[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT legislative_parameters,payroll_id
INTO leg_param,l_ppa_payroll_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id ;
UPDATE pay_payroll_actions
SET payroll_id = l_payroll_id
WHERE payroll_action_id = p_payroll_action_id;
SELECT SUBSTR(legislative_parameters,
INSTR(legislative_parameters,p_token)+(LENGTH(p_token)+1),
INSTR(legislative_parameters,' ',
INSTR(legislative_parameters,p_token))
- (INSTR(legislative_parameters,p_token)+LENGTH(p_token))),
business_group_id
FROM pay_payroll_actions
WHERE payroll_action_id = p_pact_id;
SELECT paa_init.assignment_id,
paa_init.assignment_action_id
FROM pay_assignment_actions paa_init,
pay_payroll_actions ppa_init,
per_all_assignments_f paf
WHERE ppa_init.payroll_action_id = paa_init.payroll_action_id
AND ppa_init.action_type='I'
AND ppa_init.business_group_id = l_bg_id
AND paf.business_group_id = l_bg_id
AND (paf.payroll_id = p_payroll_id OR p_payroll_id IS NULL)
AND ppa_init.consolidation_set_id = p_consolidation_set_id
AND paf.person_id BETWEEN
p_start_person AND p_end_person
AND paf.assignment_id = paa_init.assignment_id
AND ppa_init.effective_date BETWEEN paf.effective_start_date AND paf.effective_end_date
AND ppa_init.effective_date BETWEEN p_start_date AND p_end_date
AND paa_init.action_sequence = pay_in_utils.get_max_act_sequence(paa_init.assignment_id
,'I'
,ppa_init.effective_date
)
AND NOT EXISTS (SELECT NULL
FROM pay_assignment_actions paa_arch
,pay_payroll_actions ppa_arch
,pay_action_interlocks intk
WHERE paa_arch.payroll_action_id = ppa_arch.payroll_action_id
AND intk.locked_action_id = paa_init.assignment_action_id
AND intk.locking_action_id = paa_arch.assignment_action_id
AND paf.assignment_id = paa_arch.assignment_id
AND ppa_arch.action_type = 'X'
AND ppa_arch.report_type ='IN_PAYSLIP_ARCHIVE'
AND ppa_arch.report_qualifier='IN')
ORDER BY paa_init.assignment_id,paa_init.assignment_action_id;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_actid
FROM dual;
SELECT pap.accrual_plan_name accrual_plan_name
,hr_general_utilities.get_lookup_meaning('US_PTO_ACCRUAL',pap.accrual_category) accrual_category
,pap.accrual_units_of_measure accrual_uom
,ppa.payroll_id payroll_id
,pap.business_group_id business_group_id
,pap.accrual_plan_id accrual_plan_id
FROM pay_accrual_plans pap
,pay_element_types_f pet
,pay_element_links_f pel
,pay_element_entries_f pee
,pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE pet.element_type_id = pap.accrual_plan_element_type_id
AND pel.element_type_id = pet.element_type_id
AND pee.element_link_id = pel.element_link_id
AND paa.assignment_id = pee.assignment_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('R','Q')
AND ppa.action_status = 'C'
AND ppa.date_earned BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND ppa.date_earned BETWEEN pel.effective_start_date
AND pel.effective_end_date
AND ppa.date_earned BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND paa.assignment_id = p_assignment_id
AND paa.assignment_action_id = p_assignment_action_id;
SELECT pat.name absence_type
,pet.reporting_name reporting_name
,decode(pet.processing_type,'R',greatest(pab.date_start,PTP.START_DATE),pab.date_start) start_date
,decode(pet.processing_type,'R',least(pab.date_end,PTP.END_DATE),pab.date_end) end_date
,decode(pet.processing_type,'R',to_number(prrv.result_value),nvl(pab.absence_days,pab.absence_hours)) absence_days
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,pay_run_results prr
,pay_run_result_values prrv
,per_time_periods ptp
,pay_element_types_f pet
,pay_input_values_f piv
,pay_element_entries_f pee
,per_absence_attendance_types pat
,per_absence_attendances pab
WHERE paa.assignment_action_id = p_assg_act_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ppa.action_type IN ('Q','R')
AND ptp.time_period_id = ppa.time_period_id
AND paa.assignment_action_id = prr.assignment_action_id
AND pet.element_type_id = prr.element_type_id
AND pet.element_type_id = piv.element_type_id
AND piv.input_value_id = pat.input_value_id
AND pat.absence_attendance_type_id = pab.absence_attendance_type_id
AND pab.absence_attendance_id = pee.creator_id
AND pee.creator_type = 'A'
AND pee.assignment_id = paa.assignment_id
AND pee.element_entry_id = prr.source_id
AND piv.input_value_id = prrv.input_value_id
AND prr.run_result_id = prrv.run_result_id
AND ppa.effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND ppa.effective_date BETWEEN pee.effective_start_date
AND pee.effective_end_date
AND ppa.effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date;
SELECT element_reporting_name
,classification_name
,amount
,foreign_currency_code
,exchange_rate
FROM pay_in_asg_elements_v
WHERE assignment_action_id = p_assignment_action_id;
/*Cursor for selecting assignment actions in case Single pre payment has been done for multi payroll runs*/
CURSOR c_multi_rec_count(p_prepayment_lcking_id NUMBER
)
IS
select count(paa.assignment_action_id)
from pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_action_interlocks pal
where pal.locking_action_id=p_prepayment_lcking_id
and paa.assignment_action_id=pal.locked_action_id
and ppa.payroll_action_id=paa.payroll_action_id
and ppa.action_type in ('Q','R')
and ppa.action_status='C'
and paa.action_status='C'
and paa.source_action_id is not null;
select paa.assignment_action_id assignment_action_id
,ppa.date_earned date_earned
,ppa.effective_date effective_date
from pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_action_interlocks pal
where pal.locking_action_id=p_prepayment_lcking_id
and paa.assignment_action_id=pal.locked_action_id
and ppa.payroll_action_id=paa.payroll_action_id
and ppa.action_type in ('Q','R')
and ppa.action_status='C'
and paa.action_status='C'
and paa.source_action_id is not null
ORDER BY TO_NUMBER(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id);
/*Cursor for selecting payroll for the given payroll_action_id*/
CURSOR c_payroll_id(p_payroll_action_id NUMBER) IS
SELECT payroll.payroll_id
, payroll.payroll_name
FROM pay_payrolls_f payroll
,pay_payroll_actions ppa
WHERE ppa.payroll_action_id = p_run_payroll_action_id
AND ppa.payroll_id = payroll.payroll_id;
SELECT PRV2.RESULT_VALUE
FROM PAY_ELEMENT_TYPES_F PET,
PAY_ELEMENT_CLASSIFICATIONS PEC,
PAY_INPUT_VALUES_F PIV,
PAY_INPUT_VALUES_F PIV2,
PAY_RUN_RESULTS PRR,
PAY_RUN_RESULT_VALUES PRV,
PAY_RUN_RESULT_VALUES PRV2
WHERE PEC.LEGISLATION_CODE='IN'
AND PEC.CLASSIFICATION_NAME='Fringe Benefits'
AND PEC.CLASSIFICATION_ID=PET.CLASSIFICATION_ID
AND PET.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND PIV.NAME = 'Component Name'
AND PET.ELEMENT_TYPE_ID = PIV2.ELEMENT_TYPE_ID
AND PIV2.NAME = 'Add to Net Pay'
AND PRR.ASSIGNMENT_ACTION_ID = p_assignment_action_id
AND PRR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PRR.RUN_RESULT_ID = PRV.RUN_RESULT_ID
AND PRV.INPUT_VALUE_ID=PIV.INPUT_VALUE_ID
AND PRV.RESULT_VALUE= 'Employees Welfare Expense'
AND PRR.RUN_RESULT_ID = PRV2.RUN_RESULT_ID
AND PRV2.INPUT_VALUE_ID=PIV2.INPUT_VALUE_ID
AND P_EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE
AND P_EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE
AND P_EFFECTIVE_DATE BETWEEN PIV2.EFFECTIVE_START_DATE AND PIV2.EFFECTIVE_END_DATE;
l_bal_name_val.delete;
SELECT action_information_id
,action_information1
,action_information2
FROM pay_action_information
WHERE action_information_category = 'EMPLOYEE NET PAY DISTRIBUTION'
AND action_context_id = p_assactid
AND action_context_type = 'AAP';
SELECT action_information_id
FROM pay_action_information
WHERE action_information_category = 'EMPLOYEE DETAILS'
AND action_context_id = p_assactid
AND action_context_type = 'AAP';
SELECT paa.person_id
FROM per_assignments_f paa
WHERE paa.assignment_id = p_assignment_id
AND p_date_earned BETWEEN paa.effective_start_date
AND paa.effective_end_date;
SELECT fnd_date.date_to_canonical(pap.date_of_birth) dob
,pap.per_information8 pf_number
,pap.per_information9 esi_number
,pap.per_information4 pan
,pap.per_information10 superannuation_number
,hr_in_utility.per_in_full_name(pap.first_name,pap.middle_names,pap.last_name,pap.title)
,pap.email_address
FROM per_people_f pap
WHERE pap.person_id = p_person_id
AND p_date_earned BETWEEN pap.effective_start_date
AND pap.effective_end_date;
SELECT hoi.org_information1
FROM hr_soft_coding_keyflex hsck
,hr_organization_information hoi
,per_assignments_f paaf
,pay_assignment_actions paa
,pay_payroll_actions ppa
WHERE paa.assignment_action_id = p_assactid
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_id = paaf.assignment_id
AND hsck.soft_coding_keyflex_id = paaf.soft_coding_keyflex_id
AND hsck.segment3 = hoi.organization_id
AND hoi.org_information_context = 'PER_IN_PROF_TAX_DF'
AND ppa.effective_date BETWEEN paaf.effective_start_date
AND paaf.effective_end_date;
SELECT tax_unit_id
FROM pay_assignment_actions
WHERE assignment_action_id = p_curr_pymt_ass_act_id;
SELECT pea.segment3 bank_name
,pea.segment4 bank_branch
,pea.segment1 account_number
,ppm.percentage percentage
,pop.currency_code
FROM pay_external_accounts pea
,pay_pre_payments ppp
,pay_org_payment_methods_f pop
,pay_personal_payment_methods_f ppm
WHERE ppp.assignment_action_id = p_curr_pymt_ass_act_id
AND nvl(ppp.personal_payment_method_id,0) = nvl(p_personal_payment_method_id,0)
AND ppp.org_payment_method_id = p_org_payment_method_id
AND ppp.personal_payment_method_id = ppm.personal_payment_method_id (+)
AND ppp.org_payment_method_id = pop.org_payment_method_id
AND ppm.external_account_id = pea.external_account_id (+)
AND p_pre_effective_date BETWEEN pop.effective_start_date
AND pop.effective_end_date
AND p_pre_effective_date BETWEEN nvl(ppm.effective_start_date,p_pre_effective_date)
AND nvl(ppm.effective_end_date,p_pre_effective_date);
SELECT action_information_id
FROM pay_action_information
WHERE action_information_category = 'EMPLOYEE DETAILS'
AND action_context_id = p_assactid
AND action_context_type = 'AAP';
SELECT hou.name
FROM per_assignments_f peaf
,hr_soft_coding_keyflex hrscf
,hr_organization_information hoi
,hr_organization_units hou
WHERE peaf.assignment_id=p_assignment_id
AND peaf.soft_coding_keyflex_id=hrscf.soft_coding_keyflex_id
AND hoi.organization_id=hrscf.segment1
AND hoi.org_information_context='PER_IN_INCOME_TAX_DF'
AND hou.organization_id=hoi.org_information4
AND p_date_earned between peaf.effective_start_date and peaf.effective_end_date;
pay_action_information_api.update_action_information
(
p_action_information_id => l_act_inf_id
,p_object_version_number => l_ovn
,p_action_information18 => l_reg_name
);
pay_action_information_api.update_action_information
(
p_action_information_id => net_pay_rec.action_information_id
,p_object_version_number => l_ovn
,p_action_information5 => l_bank
,p_action_information7 => l_account_number
,p_action_information12 => l_percentage
,p_action_information13 => l_currency_code
);
pay_action_information_api.update_action_information
(
p_action_information_id => l_emp_det_act_info_id
,p_object_version_number => l_ovn
,p_action_information1 => l_full_name
,p_action_information6 => l_esi_number
,p_action_information8 => l_prof_tax_number
,p_action_information13 => l_dob
,p_action_information23 => l_month||','||l_year
,p_action_information24 => l_pf_number
,p_action_information25 => l_pan
,p_action_information27 => l_superannuation_number
);
SELECT hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title) Employee_Name
,pep.per_information8 PF_Number
,pep.per_information13 Pension_Number
,pep.person_id person_id
,con.contact_person_id contact
,con.contact_type -- added for ECR
,pep.sex -- added for ECR
,pep.date_of_birth -- added for ECR
FROM per_assignments_f asg
,per_people_f pep
,pay_assignment_actions paa
,per_contact_relationships con
WHERE asg.PERSON_ID=pep.person_id
AND asg.assignment_id=paa.assignment_id
AND paa.assignment_action_id= p_assignment_action_id
AND con.person_id(+) = pep.person_id
AND con.contact_type(+) = decode(pep.sex,'M','JP_FT','F',decode(pep.marital_status,'M','S','JP_FT'))
AND p_payroll_date between pep.effective_start_date and pep.effective_end_date
AND p_payroll_date between asg.effective_start_date and asg.effective_end_date;
SELECT hr_in_utility.per_in_full_name(pea.first_name,pea.middle_names,pea.last_name,pea.title) Father_Husbannd
FROM per_people_f pea
WHERE pea.person_id = p_contact_person_id
AND p_payroll_date between pea.effective_start_date and pea.effective_end_date;
select DISTINCT hoi.organization_id source_id
from hr_organization_units hoi
,hr_soft_coding_keyflex scf
,per_assignments_f asg
,pay_assignment_actions paa
where asg.assignment_id=paa.assignment_id
and paa.assignment_action_id=p_assignment_action_id
and asg.SOFT_CODING_KEYFLEX_ID=scf.SOFT_CODING_KEYFLEX_ID
and hoi.ORGANIZATION_ID=scf.segment2
and (to_char(asg.effective_start_date,'Month-YYYY')=to_char(p_payroll_date,'Month-YYYY')
or to_char(asg.effective_end_date,'Month-YYYY')=to_char(p_payroll_date,'Month-YYYY')
or p_payroll_date between asg.effective_start_date and asg.effective_end_date
);
select pdb.defined_balance_id
from pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
where pbt.balance_name=p_balance_name
and pbd.dimension_name=p_dimension
and pbt.legislation_code = 'IN'
and pbd.legislation_code = 'IN'
and pbt.balance_type_id = pdb.balance_type_id
and pbd.balance_dimension_id = pdb.balance_dimension_id;
SELECT hou.name
FROM hr_organization_units hou
WHERE hou.organization_id=p_organization_id
AND p_effective_date BETWEEN hou.date_from AND nvl(date_to,to_date('31-12-4712','DD-MM-YYYY'));
SELECT MAX(paf.effective_end_date)
FROM per_assignments_f paf
,hr_soft_coding_keyflex scl
WHERE paf.soft_coding_keyflex_id=scl.soft_coding_keyflex_id
AND paf.assignment_id=p_assignment_id
AND scl.segment2 = p_source_id
AND paf.effective_start_date <= p_pay_end
AND paf.effective_end_date >= p_pay_start;
SELECT ppf.per_information13 pension_num
,ppf.per_information8 PF_Number
FROM per_people_f ppf
,per_assignments_f paf
WHERE ppf.person_id = paf.person_id
AND paf.assignment_id = p_assignment_id
AND p_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND p_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
SELECT min(paf.effective_start_date)
FROM per_people_f ppf
,per_assignments_f paf
,hr_soft_coding_keyflex scl
WHERE ppf.person_id = l_person_id
AND paf.person_id =ppf.person_id
AND paf.soft_coding_keyflex_id =scl.soft_coding_keyflex_id
AND scl.segment2 = l_source_id;
SELECT '1'
FROM per_people_f ppf
,per_assignments_f paf
,hr_soft_coding_keyflex scl
WHERE ppf.person_id = l_person_id
AND paf.person_id =ppf.person_id
AND paf.soft_coding_keyflex_id =scl.soft_coding_keyflex_id
and scl.segment2 IS NOT NULL
AND scl.segment2 <> l_source_id
AND paf.effective_end_date < l_asg_start_date
AND ROWNUM <2;
SELECT ppf.per_information13
FROM per_people_f ppf
WHERE ppf.person_id =l_person_id
AND ppf.per_information13 IS NOT NULL
AND ppf.effective_start_date < l_asg_start_date
ORDER BY ppf.effective_start_date desc;
SELECT MAX(pos.date_start)
FROM per_periods_of_service pos
WHERE pos.person_id = p_person_id
AND pos.date_start <= (SELECT MIN(effective_start_date)
FROM per_people_f ppf
WHERE ppf.person_id = p_person_id
AND ppf.per_information13 = p_pension_number);
SELECT
distinct
pps.actual_termination_date term_date, -- Termination Date
pps.pds_information3 efile -- Termination Reason(EFile)
FROM per_people_f ppf,
per_assignments_f paf,
per_periods_of_service pps,
hr_soft_coding_keyflex scl
WHERE paf.person_id = ppf.person_id
AND paf.period_of_service_id = pps.period_of_service_id
AND paf.assignment_id = p_assignment_id
AND scl.segment2 = l_source_id
AND scl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
AND scl.enabled_flag = 'Y'
AND paf.effective_end_date between ppf.effective_start_date and ppf.effective_end_date;
SELECT DISTINCT hoi.organization_id source_id
FROM hr_organization_units hoi
,hr_soft_coding_keyflex scf
,per_assignments_f asg
,pay_assignment_actions paa
WHERE asg.assignment_id=paa.assignment_id
AND paa.assignment_action_id=p_assignment_action_id
AND asg.SOFT_CODING_KEYFLEX_ID=scf.SOFT_CODING_KEYFLEX_ID
AND hoi.ORGANIZATION_ID=scf.segment4
AND (to_char(asg.effective_start_date,'Month-YYYY')=to_char(p_payroll_date,'Month-YYYY')
OR to_char(asg.effective_end_date,'Month-YYYY')=to_char(p_payroll_date,'Month-YYYY')
OR p_payroll_date BETWEEN asg.effective_start_date AND asg.effective_end_date
);
SELECT SERVICE.date_start
FROM
/* Person current period of service date details */
per_all_assignments_f ASSIGN
, per_periods_of_service SERVICE
WHERE p_payroll_date BETWEEN ASSIGN.effective_start_date
AND ASSIGN.effective_end_date
AND ASSIGN.assignment_id = p_assignment_id
AND SERVICE.period_of_Service_id = ASSIGN.period_of_service_id;
SELECT hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title) Employee_Name
,pep.per_information9 ESI_Number
,pep.person_id person_id
FROM per_assignments_f asg
,per_people_f pep
,pay_assignment_actions paa
WHERE asg.PERSON_ID=pep.person_id
AND asg.assignment_id=paa.assignment_id
AND paa.assignment_action_id= p_assignment_action_id
AND p_payroll_date BETWEEN pep.effective_start_date AND pep.effective_end_date
AND p_payroll_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
SELECT pdb.defined_balance_id
FROM pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE pbt.balance_name=p_balance_name
AND pbd.dimension_name=p_dimension
AND pbt.legislation_code = 'IN'
AND pbd.legislation_code = 'IN'
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id;
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name='ESI Information'
AND legislation_code = 'IN'
AND p_payroll_date between effective_start_date and effective_end_date;
SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id = p_element_type_id
AND name = p_name
AND p_payroll_date BETWEEN effective_start_date AND effective_end_date;
SELECT run_result_id
FROM pay_run_results
WHERE assignment_action_id=p_assignment_action_id
AND element_type_id=p_element_type_id;
SELECT prr2.result_value
FROM pay_run_result_values prr1
,pay_run_result_values prr2
WHERE prr1.run_result_id = p_run_result_id
AND prr1.input_value_id = p_org_iv_id
AND prr2.run_result_id = prr1.run_result_id
AND prr2.input_value_id = p_rem_iv_id
AND prr1.result_value = p_org_id;
SELECT hou.name
FROM hr_organization_units hou
WHERE hou.organization_id=p_organization_id
AND p_effective_date BETWEEN hou.date_from AND nvl(date_to,to_date('31-12-4712','DD-MM-YYYY'));
SELECT '1'
FROM per_people_f
WHERE person_id= p_person_id
AND date_of_death <= p_effective_date ;
SELECT '1'
FROM per_periods_of_service
WHERE actual_termination_date <= p_effective_date
AND person_id = p_person_id
AND date_start = (SELECT MAX(TO_DATE(date_start,'DD-MM-YY'))
FROM per_periods_of_service
WHERE person_id = p_person_id
AND business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
);
SELECT MAX(paf.effective_end_date)
FROM per_assignments_f paf
,hr_soft_coding_keyflex scl
WHERE paf.soft_coding_keyflex_id=scl.soft_coding_keyflex_id
AND paf.assignment_id=p_assignment_id
AND scl.segment4 = p_source_id
AND paf.effective_start_date <= p_pay_end
AND paf.effective_end_date >= p_pay_start;
SELECT ppf.per_information9 ESI_Number
FROM per_people_f ppf
,per_assignments_f paf
WHERE ppf.person_id = paf.person_id
AND paf.assignment_id = p_assignment_id
AND p_date BETWEEN ppf.effective_start_date AND ppf.effective_end_date
AND p_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
SELECT global_value
FROM ff_globals_f
WHERE legislation_code='IN'
AND global_name = c_global_value
AND p_payroll_date BETWEEN effective_start_date AND effective_end_date;
SELECT target.org_information3 FROM
per_all_assignments_f assign,
hr_soft_coding_keyflex scl,
hr_organization_information target
WHERE assign.assignment_id = p_assignment_id
AND p_payroll_date BETWEEN ASSIGN.effective_start_date AND ASSIGN.effective_end_date
AND assign.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND scl.segment1 = target.organization_id
AND target.org_information_context = 'PER_IN_INCOME_TAX_DF';
SELECT effective_date
FROM pay_payroll_actions
WHERE payroll_action_id=p_payroll_action_id;
SELECT hou.name
FROM hr_organization_units hou
WHERE hou.organization_id=p_organization_id
AND p_effective_date BETWEEN hou.date_from AND nvl(date_to,to_date('31-12-4712','DD-MM-YYYY'));
SELECT hou.name
,hoi.org_information1
,hoi.org_information3
,substr(
hla.address_line_1||
decode(hla.address_line_2,null,null,','||hla.address_line_2)||
decode(hla.address_line_3,null,null,','||hla.address_line_3)||
decode(hla.loc_information14,null,null,','||hla.loc_information14)||
decode(hla.loc_information15,null,null,','||hla.loc_information15)||
decode(hr_general.decode_lookup('IN_STATES',hla.loc_information16),null,null,','||hr_general.decode_lookup('IN_STATES',hla.loc_information16))||
decode(hla.postal_code,null,null,','||hla.postal_code)
,1,240)
FROM hr_organization_information hoi
,hr_organization_units hou
,hr_organization_units hou1
,hr_locations_all hla
WHERE hoi.organization_id=p_organization_id
AND hoi.org_information_context = DECODE(p_context,'PF','PER_IN_PF_DF','ESI','PER_IN_ESI_DF')
AND hou.organization_id = DECODE (p_context,'PF',hoi.org_information8,'ESI',hoi.org_information2)
AND hla.location_id=hou1.location_id
AND hou.organization_id=hou1.organization_id
AND p_effective_date BETWEEN hou.date_from AND nvl(hou.date_to,to_date('31-12-4712','DD-MM-YYYY'));
SELECT DISTINCT hr_in_utility.per_in_full_name(peap.first_name,peap.middle_names,peap.last_name,peap.title) rep_name
,peap.person_id person_id
FROM hr_organization_information hoi
,per_people_f peap
WHERE hoi.ORGANIZATION_ID=p_pf_org_id
AND hoi.ORG_INFORMATION_CONTEXT = DECODE (p_context,'PF','PER_IN_PF_REP_DF','ESI','PER_IN_ESI_REP_DF')
AND peap.person_id=hoi.ORG_INFORMATION1
AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information2)
AND NVL(fnd_date.canonical_to_date(hoi.org_information3),TO_DATE('31-12-4712','DD-MM-YYYY'));
SELECT SUBSTR(
address_line1||
DECODE(address_line2,NULL,NULL,','||address_line2)||
DECODE(address_line3,NULL,NULL,','||address_line3)||
DECODE(add_information13,NULL,NULL,','||add_information13)||
DECODE(add_information14,NULL,NULL,','||add_information14)||
DECODE(hr_general.decode_lookup('IN_STATES',add_information15),NULL,NULL,','||hr_general.decode_lookup('IN_STATES',add_information15))||
DECODE(postal_code,NULL,NULL,','||postal_code)
,1,240) address
FROM per_addresses
WHERE person_id=p_person_id
AND address_type='HK_R'
AND p_effective_date BETWEEN date_from AND nvl(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
SELECT nvl(pos.name,job.name) name
FROM per_positions pos
,per_assignments_f asg
,per_jobs job
WHERE asg.position_id=pos.position_id(+)
AND asg.job_id=job.job_id(+)
AND asg.person_id = p_person_id
AND asg.primary_flag = 'Y'
AND p_effective_date BETWEEN pos.date_effective(+) AND NVL(pos.date_end(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
AND p_effective_date BETWEEN job.date_from(+) AND NVL(job.date_to(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
SELECT 1
FROM pay_action_information
WHERE action_context_id = p_payroll_action_id
AND action_information_category = 'IN_'||p_context||'_PAY'
AND action_information1 = p_contribution_period
AND action_information2 = p_organization_id
AND action_context_type = 'PA';
SELECT DECODE(p_context,'PF',l_class,l_rep_addr) INTO l_context
FROM dual;--case p_context when 'PF' then l_class else l_rep_addr end --PF CLass or ESI Org Rep Addr
SELECT DISTINCT pay_in_prof_tax_pkg.get_state(hoi.organization_id) jurisdiction_code
FROM hr_organization_units hoi
,hr_soft_coding_keyflex scf
,per_assignments_f asg
,pay_assignment_actions paa
WHERE asg.assignment_id = paa.assignment_id
AND paa.assignment_action_id = p_assignment_action_id
AND asg.soft_coding_keyflex_id = scf.soft_coding_keyflex_id
AND hoi.organization_id = scf.segment3
AND (TO_CHAR(asg.effective_start_date, 'Month-YYYY') = TO_CHAR(p_payroll_date, 'Month-YYYY')
OR TO_CHAR(asg.effective_end_date, 'Month-YYYY') = TO_CHAR(p_payroll_date, 'Month-YYYY')
OR p_payroll_date BETWEEN asg.effective_start_date AND asg.effective_end_date);
SELECT source_id
,name
FROM ( */SELECT hoi.organization_id source_id
,hoi.name name
,asg.effective_end_date
FROM hr_organization_units hoi
,hr_soft_coding_keyflex scf
,per_assignments_f asg
,pay_assignment_actions paa
WHERE asg.assignment_id = paa.assignment_id
AND paa.assignment_action_id = p_assignment_action_id
AND asg.soft_coding_keyflex_id = scf.soft_coding_keyflex_id
AND hoi.organization_id = scf.segment3
AND (TO_CHAR(asg.effective_start_date, 'Month-YYYY') = TO_CHAR(p_payroll_date, 'Month-YYYY')
OR TO_CHAR(asg.effective_end_date, 'Month-YYYY') = TO_CHAR(p_payroll_date, 'Month-YYYY')
OR p_payroll_date BETWEEN asg.effective_start_date AND asg.effective_end_date)
AND pay_in_prof_tax_pkg.get_state(hoi.organization_id) = p_jur_code
ORDER BY asg.effective_end_date DESC;/*)
SELECT hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title) Employee_Name
,pep.person_id person_id
FROM per_assignments_f asg
,per_people_f pep
,pay_assignment_actions paa
WHERE asg.person_id = pep.person_id
AND asg.assignment_id = paa.assignment_id
AND paa.assignment_action_id = p_assignment_action_id
AND p_payroll_date BETWEEN pep.effective_start_date AND pep.effective_end_date
AND p_payroll_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
SELECT element_type_id
FROM pay_element_types_f
WHERE element_name ='Professional Tax Information'
AND legislation_code = 'IN'
AND p_payroll_date BETWEEN effective_start_date AND effective_end_date;
SELECT input_value_id
FROM pay_input_values_f
WHERE element_type_id = p_element_type_id
AND name = p_name
AND p_payroll_date BETWEEN effective_start_date AND effective_end_date;
SELECT run_result_id
FROM pay_run_results
WHERE assignment_action_id = p_assignment_action_id
AND element_type_id = p_element_type_id;
SELECT prr2.result_value
FROM pay_run_result_values prr1
,pay_run_result_values prr2
WHERE prr1.run_result_id = p_run_result_id
AND prr1.input_value_id = p_state_iv_id
AND prr2.run_result_id = prr1.run_result_id
AND prr2.input_value_id = p_rem_iv_id
AND prr1.result_value = p_jur_code;
SELECT effective_date
FROM pay_payroll_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT hou.name
FROM hr_organization_units hou
WHERE hou.organization_id = p_organization_id
AND p_effective_date BETWEEN hou.date_from AND NVL(date_to, TO_DATE('31-12-4712', 'DD-MM-YYYY'));
SELECT hou.name
,hoi.org_information1
,hoi.org_information3
,SUBSTR(
hla.address_line_1||
DECODE(hla.address_line_2, null, null, ',' || hla.address_line_2)||
DECODE(hla.address_line_3, null, null, ',' || hla.address_line_3)||
DECODE(hla.loc_information14, null, null, ',' || hla.loc_information14)||
DECODE(hla.loc_information15, null, null, ',' || hla.loc_information15)||
DECODE(hr_general.decode_lookup('IN_STATES', hla.loc_information16)
, null, null, ',' || hr_general.decode_lookup('IN_STATES', hla.loc_information16))||
DECODE(hla.postal_code, null, null, ','||hla.postal_code)
,1,240)
FROM hr_organization_information hoi
,hr_organization_units hou
,hr_organization_units hou1
,hr_locations_all hla
WHERE hoi.organization_id = p_organization_id
AND hoi.org_information_context = 'PER_IN_PROF_TAX_DF'
AND hou.organization_id = hoi.org_information2
AND hla.location_id = hou1.location_id
AND hou.organization_id = hou1.organization_id
AND p_effective_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712', 'DD-MM-YYYY'));
SELECT DISTINCT hr_in_utility.per_in_full_name(peap.first_name,peap.middle_names,peap.last_name,peap.title) rep_name
,peap.person_id person_id
FROM hr_organization_information hoi
,per_people_f peap
WHERE hoi.organization_id = p_pf_org_id
AND hoi.org_information_context = 'PER_IN_PROF_TAX_REP_DF'
AND peap.person_id = hoi.org_information1
AND p_effective_date BETWEEN fnd_date.canonical_to_date(hoi.org_information2)
AND NVL(fnd_date.canonical_to_date(hoi.org_information3)
, TO_DATE('31-12-4712', 'DD-MM-YYYY'));
SELECT nvl(pos.name,job.name) name
FROM per_positions pos
,per_assignments_f asg
,per_jobs job
WHERE asg.position_id=pos.position_id(+)
AND asg.job_id=job.job_id(+)
AND asg.person_id = p_person_id
AND asg.primary_flag = 'Y'
AND p_effective_date BETWEEN pos.date_effective(+) AND NVL(pos.date_end(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
AND p_effective_date BETWEEN job.date_from(+) AND NVL(job.date_to(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
SELECT paa_arch.assignment_action_id arch_assignment_action_id
,paa_arch.payroll_action_id arch_payroll_action_id
,paa_init.assignment_action_id init_assignment_action_id
,ppa_init.payroll_action_id init_payroll_action_id
,ppa_init.effective_date init_effective_date
,paa_arch.assignment_id
FROM pay_assignment_actions paa_arch
,pay_action_interlocks intk
,pay_assignment_actions paa_init
,pay_payroll_actions ppa_init
WHERE paa_arch.assignment_action_id = p_init_arch_action_id
AND intk.locking_action_id = paa_arch.assignment_action_id
AND intk.locked_action_id = paa_init.assignment_action_id
AND paa_init.payroll_action_id = ppa_init.payroll_action_id
AND ppa_init.action_type ='I';
SELECT /*+ ORDERED */
paa_arch_chd.assignment_action_id chld_arc_assignment_action_id
,paa_arch_chd.payroll_action_id arc_payroll_action_id
,paa_pre.assignment_action_id pre_assignment_action_id
,paa_run.assignment_action_id run_assignment_action_id
,paa_run.payroll_action_id run_payroll_action_id
,ppa_pre.effective_date pre_effective_date
,paa_arch_chd.assignment_id
,ppa_run.effective_date run_effective_date
,ppa_run.date_earned run_date_earned
,ptp.end_date period_end_date
,ptp.time_period_id
,ptp.start_date period_start_date
,ptp.regular_payment_date
FROM pay_assignment_actions paa_arch_mst
,pay_assignment_actions paa_arch_chd
,pay_action_interlocks pai_pre
,pay_assignment_actions paa_pre
,pay_payroll_actions ppa_pre
,per_business_groups pbg
,pay_action_interlocks pai_run
,pay_assignment_actions paa_run
,pay_payroll_actions ppa_run
,per_time_periods ptp
WHERE paa_arch_mst.assignment_action_id = p_master_aa_id
AND paa_arch_chd.source_action_id = paa_arch_mst.assignment_action_id
AND paa_arch_chd.payroll_action_id = paa_arch_mst.payroll_action_id
AND ppa_pre.business_group_id = pbg.business_group_id
AND pbg.business_group_id = ppa_run.business_group_id
AND ppa_pre.payroll_id = ppa_run.payroll_id
AND paa_arch_chd.assignment_id = paa_arch_mst.assignment_id
AND pai_pre.locking_action_id = paa_arch_mst.assignment_action_id
AND pai_pre.locked_action_id = paa_pre.assignment_action_id
AND pai_run.locking_action_id = paa_arch_chd.assignment_action_id
AND pai_run.locked_action_id = paa_run.assignment_action_id
/* Asg start */
and paa_run.assignment_id = paa_arch_chd.assignment_id
and paa_pre.assignment_id = paa_arch_mst.assignment_id
/* Asg end */
AND ppa_pre.payroll_action_id = paa_pre.payroll_action_id
AND ppa_pre.action_type IN ('P','U')
AND ppa_run.payroll_action_id = paa_run.payroll_action_id
AND ppa_run.action_type IN ('R','Q')
AND ptp.payroll_id = ppa_run.payroll_id
AND ppa_run.date_earned BETWEEN ptp.start_date
AND ptp.end_date
-- Get the highest in sequence for this payslip
AND paa_run.action_sequence =
(
SELECT MAX(paa_run2.action_sequence)
FROM pay_assignment_actions paa_run2
,pay_action_interlocks pai_run2
WHERE pai_run2.locking_action_id = paa_arch_chd.assignment_action_id
AND pai_run2.locked_action_id = paa_run2.assignment_action_id
);
/*Cursor for selecting assignment actions in case Single pre payment has been done for multi payroll runs*/
CURSOR c_multi_rec_count(p_prepayment_lcking_id NUMBER
)
IS
select count(paa.assignment_action_id)
from pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_action_interlocks pal
where pal.locking_action_id=p_prepayment_lcking_id
and paa.assignment_action_id=pal.locked_action_id
and ppa.payroll_action_id=paa.payroll_action_id
and ppa.action_type in ('Q','R')
and ppa.action_status='C'
and paa.action_status='C'
and paa.source_action_id is not null;
select paa.assignment_action_id
,ppa.date_earned
from pay_payroll_actions ppa
,pay_assignment_actions paa
,pay_action_interlocks pal
where pal.locking_action_id=p_prepayment_lcking_id
and paa.assignment_action_id=pal.locked_action_id
and ppa.payroll_action_id=paa.payroll_action_id
and ppa.action_type in ('Q','R')
and ppa.action_status='C'
and paa.action_status='C'
and paa.source_action_id is not null
and to_char(ppa.date_earned,'MM-YYYY')=to_char(add_months(p_date_earned,-i),'MM-YYYY')
order by paa.assignment_action_id desc;
SELECT paa.assignment_action_id
FROM pay_payroll_actions ppa,pay_assignment_actions paa,
pay_action_interlocks pai1,pay_action_interlocks pai2
WHERE ppa.PAYROLL_ACTION_ID = paa.PAYROLL_ACTION_ID
AND paa.ASSIGNMENT_ID = c_assignment_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.action_status = 'C'
AND ACTION_TYPE = 'V'
AND ppa.action_status = 'C'
AND pai1.LOCKING_ACTION_ID = c_assignment_action_id
AND pai2.LOCKING_ACTION_ID = paa.assignment_action_id
AND pai1.locked_action_id = pai2.locked_action_id
AND TO_CHAR(ppa.date_earned ,'MM-YYYY') = to_char(c_payroll_date,'MM-YYYY');
SELECT pps.actual_termination_date
FROM pay_payroll_actions ppa,
pay_assignment_actions paa,
per_time_periods ptp,
per_all_assignments_f paf,
per_periods_of_service pps
WHERE paa.assignment_action_id = p_assignment_action_id
AND ppa.payroll_action_id = paa.payroll_action_id
AND ptp.payroll_id = ppa.payroll_id
AND paf.assignment_id = paa.assignment_id
AND pps.period_of_service_id = paf.period_of_service_id
AND ppa.date_earned between ptp.start_date AND ptp.end_date
AND pps.actual_termination_date between ptp.start_date AND ptp.end_date;