[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 pet.element_type_id element_type_id
,piv.input_value_id input_value_id
,piv.display_sequence indx
FROM pay_element_types_f pet
,pay_input_values_f piv
WHERE pet.element_name ='Income Tax Challan Information'
AND pet.legislation_code='IN'
AND pet.element_type_id = piv.element_type_id
AND piv.name in('Challan or Voucher Number',
'Payment Date',
'Taxable Income',
'Income Tax Deducted',
'Surcharge Deducted',
'Education Cess Deducted',
'Amount Deposited')
AND g_session_date BETWEEN pet.effective_start_date AND pet.effective_end_date
AND g_session_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
SELECT 1
FROM pay_action_information pai
,pay_payroll_actions ppa
,hr_organization_units hou
WHERE pai.action_information_category = 'IN_24Q_ORG'
AND pai.action_context_type = 'PA'
AND pai.action_information1 like g_gre_id
AND pai.action_information3 = g_year||g_quarter
AND pai.action_information30 = g_archive_ref_no
AND pai.action_context_id = ppa.payroll_action_id
AND ppa.action_type = 'X'
AND ppa.action_status = 'C'
AND ppa.payroll_action_id <> p_payroll_action_id
AND hou.organization_id = pai.action_information1
AND hou.business_group_id = p_bg_id;
SELECT TRUNC(effective_date)
INTO g_session_date
FROM fnd_sessions
WHERE session_id = USERENV('sessionid');
SELECT paf.assignment_id assignment_id
FROM per_assignments_f paf
,pay_payroll_actions ppa
,pay_assignment_actions paa
WHERE paf.business_group_id = g_bg_id
AND paf.person_id BETWEEN p_start_person AND p_end_person
AND p_payroll_action_id IS NOT NULL
AND paa.tax_unit_id LIKE g_gre_id
AND paa.assignment_id =paf.assignment_id
AND ppa.action_type IN('P','U','I')
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_status = 'C'
AND ppa.effective_date BETWEEN g_start_date and g_end_date
AND paf.effective_start_date <= g_end_date
AND paf.effective_end_date >= g_start_date
AND ppa.business_group_id =g_bg_id
UNION
SELECT paf1.assignment_id
FROM pay_element_entries_f pee
,per_assignments_f paf1
WHERE paf1.business_group_id = g_bg_id
AND paf1.person_id BETWEEN p_start_person AND p_end_person
AND pee.element_type_id = g_chln_element_id
AND p_payroll_action_id IS NOT NULL
AND paf1.effective_start_date <= g_fin_end_date
AND paf1.effective_end_date >= g_fin_start_date
AND pee.effective_start_date <= g_fin_end_date
AND pee.effective_end_date >= g_fin_start_date
AND pee.assignment_id = paf1.assignment_id
AND EXISTS (SELECT ''
FROM pay_element_entry_values_f peev
,hr_organization_information hoi
WHERE peev.input_value_id = g_input_table_rec(1).input_value_id
AND peev.element_entry_id = pee.element_entry_id
AND peev.screen_entry_value = hoi.org_information3
AND hoi.org_information1 = g_tax_year
AND hoi.org_information13 = g_quarter
AND hoi.org_information_context ='PER_IN_IT_CHALLAN_INFO'
AND peev.effective_start_date <= g_fin_end_date
AND peev.effective_end_date >= g_fin_start_date
AND hoi.organization_id LIKE g_gre_id
AND ROWNUM =1);
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_action_id
FROM dual;
SELECT hoi_challan.org_information2 Payment_date
,hoi_challan.org_information5 Bank
,hoi_challan.org_information3 challan_number
,hoi_challan.org_information4 tax
,hoi_challan.org_information6 remarks
,hoi_challan.org_information7 surcharge
,hoi_challan.org_information8 cess
,hoi_challan.org_information9 interest
,hoi_challan.org_information10 others
,hoi_challan.org_information11 dd_cheq_num
,hoi_challan.org_information12 book_entry
,hoi_challan.org_information_id org_info_id
FROM hr_organization_information hoi_challan
WHERE hoi_challan.organization_id = p_gre_id
AND hoi_challan.org_information_context ='PER_IN_IT_CHALLAN_INFO'
AND hoi_challan.org_information1 = g_tax_year
AND hoi_challan.org_information13 = g_quarter
AND fnd_date.canonical_to_date(hoi_challan.org_information2) <= fnd_date.CHARDATE_TO_DATE(SYSDATE)
ORDER BY fnd_date.canonical_to_date(hoi_challan.org_information2);
SELECT hoi_bank.org_information4 Bank
FROM hr_organization_information hoi_bank
WHERE hoi_bank.organization_id = p_gre_id
AND hoi_bank.org_information_context = 'PER_IN_CHALLAN_BANK'
AND hoi_bank.org_information_id = p_bank_code;
t_challan_entry_asg_tab.DELETE;
SELECT hoi.org_information1 tan
,hoi.org_information3 er_class
,hoi.org_information4 reg_org_id
,hoi.org_information7 division
,hou.location_id location_id
FROM hr_organization_information hoi
,hr_organization_units hou
WHERE hoi.organization_id = p_gre_id
AND hoi.org_information_context = 'PER_IN_INCOME_TAX_DF'
AND hou.organization_id = hoi.organization_id
AND hou.business_group_id = g_bg_id
AND p_effective_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
SELECT hoi.org_information3 pan
,hoi.org_information4 legal_name
FROM hr_organization_information hoi
,hr_organization_units hou
WHERE hoi.organization_id = p_reg_org_id
AND hoi.org_information_context = 'PER_IN_COMPANY_DF'
AND hou.organization_id = hoi.organization_id
AND hou.business_group_id = g_bg_id
AND p_effective_date BETWEEN hou.date_from AND NVL(hou.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 asg.business_group_id = g_bg_id
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 hoi.org_information1 person_id
,hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title) rep_name
,pep.email_address email_id
FROM hr_organization_information hoi
,hr_organization_units hou
,per_people_f pep
WHERE hoi.org_information_context = 'PER_IN_INCOME_TAX_REP_DF'
AND hoi.organization_id = p_gre_id
AND hou.organization_id = hoi.organization_id
AND hou.business_group_id = g_bg_id
AND pep.person_id = hoi.org_information1
AND pep.business_group_id = hou.business_group_id
AND p_effective_date BETWEEN pep.effective_start_date AND pep.effective_end_date
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'))
AND p_effective_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
SELECT hou.location_id rep_location
FROM per_assignments_f asg
,hr_organization_units hou
WHERE asg.person_id = p_person_id
AND asg.primary_flag = 'Y'
AND asg.business_group_id = g_bg_id
AND hou.organization_id = asg.organization_id
AND hou.business_group_id = asg.business_group_id
AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND p_effective_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
SELECT phone_number rep_phone_no
FROM per_phones
WHERE parent_id = p_person_id
AND phone_type = 'W1'
AND p_effective_date BETWEEN date_from AND NVL(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
SELECT COUNT(*)
FROM pay_action_information
WHERE action_information_category = 'IN_24Q_CHALLAN'
AND action_context_type = 'PA'
AND action_context_id = p_arc_pay_action_id
AND action_information3 = p_gre_id
AND action_information2 = g_year||g_quarter;
SELECT asg.person_id person_id
,DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4) pan
,pep.per_information14 pan_ref_num
,hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title) name
FROM per_assignments_f asg
,per_people_f pep
WHERE asg.assignment_id = p_assignment_id
AND pep.person_id = asg.person_id
AND pep.business_group_id = g_bg_id
AND asg.business_group_id = g_bg_id
AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND p_effective_date BETWEEN pep.effective_start_date AND pep.effective_end_date ;
SELECT nvl(pos.name,job.name) name
FROM per_all_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.assignment_id = p_assignment_id
AND asg.business_group_id = g_bg_id
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 paei.aei_information2
FROM per_assignment_extra_info paei
,per_assignments_f paa
WHERE paei.information_type = 'PER_IN_TAX_EXEMPTION_DF'
AND paei.aei_information_category = 'PER_IN_TAX_EXEMPTION_DF'
AND paei.assignment_id = paa.assignment_id
AND paa.person_id = p_person_id
AND paei.aei_information1 = g_tax_year
AND p_effective_date BETWEEN paa.effective_start_date AND paa.effective_end_date
AND ROWNUM = 1;
SELECT paf.payroll_id
FROM per_all_assignments_f paf
WHERE paf.assignment_id =p_assignment_id
AND p_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
SELECT pdb.defined_balance_id balance_id
,pbt.balance_name balance_name
FROM pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE pbt.balance_name IN('F16 Deductions Sec 80E'
,'F16 Deductions Sec 80U'
,'Gross Chapter VIA Deductions'
,'Deferred Annuity'
,'Senior Citizens Savings Scheme'
,'Pension Fund'
,'F16 Employee PF Contribution'
,'F16 Total Chapter VI A Deductions'
,'Deductions under Section 80CCE'
,'F16 Deductions Sec 80GG'
,'F16 Deductions Sec 80G'
,'F16 Deductions Sec 80CCE'
,'F16 ER Pension Contribution'
)
AND pbd.dimension_name='_ASG_LE_PTD'
AND pbt.legislation_code = 'IN'
AND pbd.legislation_code = 'IN'
AND pdb.legislation_code = 'IN'
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id
ORDER BY pbt.balance_name;
g_balance_value_tab1.DELETE;
g_context_table.DELETE;
g_result_table1.DELETE;
g_balance_value_tab1.DELETE;
g_context_table.DELETE;
g_result_table1.DELETE;
g_context_table.DELETE;
g_result_table.DELETE;
g_balance_value_tab.DELETE;
g_bal_name_tab.DELETE;
g_bal_name_tab.DELETE;
g_balance_value_tab.DELETE;
g_balance_value_tab1.DELETE;
g_result_table.DELETE;
g_context_table.DELETE;
g_result_table1.DELETE;
SELECT pdb.defined_balance_id balance_id
,pbt.balance_name balance_name
FROM pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE pbt.balance_name IN('F16 Salary Under Section 17'
,'F16 Profit in lieu of Salary'
,'F16 Value of Perquisites'
,'F16 Gross Salary less Allowances'
,'F16 Allowances Exempt'
,'F16 Deductions under Sec 16'
,'F16 Income Chargeable Under head Salaries'
,'F16 Other Income'
,'F16 Gross Total Income'
,'F16 Total Chapter VI A Deductions'
,'F16 Total Income'
,'F16 Tax on Total Income'
,'F16 Marginal Relief'
,'F16 Total Tax payable'
,'F16 Relief under Sec 89'
,'F16 Employment Tax'
,'F16 Entertainment Allowance'
,'Allowances Standard Value'
,'F16 Surcharge'
,'F16 Education Cess'
,'F16 Sec and HE Cess'
,'F16 TDS'
)
AND pbd.dimension_name ='_ASG_LE_PTD'
AND pbt.legislation_code = 'IN'
AND pbd.legislation_code = 'IN'
AND pdb.legislation_code = 'IN'
AND pbt.balance_type_id = pdb.balance_type_id
AND pbd.balance_dimension_id = pdb.balance_dimension_id;
SELECT pdb.defined_balance_id balance_id
,pbt.balance_name balance_name
FROM pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE pbt.balance_name IN( 'Excess Interest Amount'
,'Excess PF Amount'
,'Allowance Amount'
)
AND pbd.dimension_name='_ASG_YTD'
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;
g_bal_name_tab.DELETE;
g_balance_value_tab.DELETE;
g_result_table.DELETE;
g_context_table.DELETE;
g_bal_name_tab.DELETE;
g_context_table.DELETE;
g_balance_value_tab1.DELETE;
g_balance_value_tab2.DELETE;
g_result_table.DELETE;
g_bal_name_tab.DELETE;
g_context_table.DELETE;
g_balance_value_tab.DELETE;
g_result_table.DELETE;
g_balance_value_tab1.DELETE;
g_balance_value_tab2.DELETE;
SELECT pdb.defined_balance_id balance_id
,DECODE(pbt.balance_name,'Monthly Furniture Cost',1,
'Furniture Perquisite',2,
'Taxable Perquisites',3) indx
,pbt.balance_name balance_name
FROM pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE pbt.balance_name IN('Monthly Furniture Cost'
,'Furniture Perquisite'
,'Taxable Perquisites'
)
AND pbd.dimension_name='_ASG_YTD'
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 pdb.defined_balance_id balance_id
,DECODE(pbt.balance_name,'Projected Furniture Cost',1,
'Projected Furniture Perquisite',2,
'Taxable Perquisites for Projection',3) indx
FROM pay_balance_types pbt
,pay_balance_dimensions pbd
,pay_defined_balances pdb
WHERE pbt.balance_name IN('Projected Furniture Cost'
,'Projected Furniture Perquisite'
,'Taxable Perquisites for Projection'
)
AND pbd.dimension_name='_ASG_PTD'
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;
g_context_table.DELETE;
g_balance_value_tab1.DELETE;
g_result_table1.DELETE;
g_balance_value_tab1.DELETE;
g_balance_value_tab2.DELETE;
g_bal_name_tab.DELETE;
g_result_table1.DELETE;
g_result_table2.DELETE;
g_balance_value_tab.DELETE;
g_balance_value_tab.DELETE;
g_context_table.DELETE;
g_result_table1.delete;
g_result_table2.DELETE;
g_balance_value_tab.DELETE;
g_balance_value_tab1.DELETE;
g_balance_value_tab2.DELETE;
g_bal_name_tab.DELETE;
g_context_table.DELETE;
g_result_table.DELETE;
g_result_table1.DELETE;
g_result_table2.DELETE;
SELECT pee.element_entry_id
FROM pay_element_entries_f pee
WHERE pee.element_type_id = g_chln_element_id
AND pee.effective_start_date <= g_fin_end_date
AND pee.effective_end_date >= g_fin_start_date
AND pee.assignment_id = p_assignment_id
AND EXISTS (SELECT ''
FROM pay_element_entry_values_f peev
,hr_organization_information hoi
WHERE peev.input_value_id = g_input_table_rec(1).input_value_id
AND peev.element_entry_id = pee.element_entry_id
AND peev.screen_entry_value = hoi.org_information3
AND hoi.organization_id = p_gre_id
AND hoi.org_information1 = g_tax_year
AND hoi.org_information13 = g_quarter
AND hoi.org_information_context ='PER_IN_IT_CHALLAN_INFO'
AND peev.effective_start_date <= g_fin_end_date
AND peev.effective_end_date >= g_fin_start_date
AND ROWNUM =1);
SELECT asg.person_id person_id
,DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4) pan
,pep.per_information14 pan_ref_num
,hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title) name
FROM per_assignments_f asg
,per_people_f pep
WHERE asg.assignment_id = p_assignment_id
AND pep.person_id = asg.person_id
AND pep.business_group_id = g_bg_id
AND asg.business_group_id = g_bg_id
AND p_effective_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND p_effective_date BETWEEN pep.effective_start_date AND pep.effective_end_date ;
SELECT nvl(pos.name,job.name) name
FROM per_all_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.assignment_id = p_assignment_id
AND asg.business_group_id = g_bg_id
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 paei.aei_information2
FROM per_assignment_extra_info paei
,per_assignments_f paa
WHERE paei.information_type = 'PER_IN_TAX_EXEMPTION_DF'
AND paei.aei_information_category = 'PER_IN_TAX_EXEMPTION_DF'
AND paei.assignment_id = paa.assignment_id
AND paa.person_id = p_person_id
AND paei.aei_information1 = g_tax_year
AND p_effective_date BETWEEN paa.effective_start_date AND paa.effective_end_date
AND ROWNUM = 1;
SELECT peev.screen_entry_value entry_value
,piv.display_sequence indx
FROM pay_element_entry_values_f peev
,pay_input_values_f piv
WHERE peev.element_entry_id = p_element_entry_id
AND peev.input_value_id IN(g_input_table_rec(1).input_value_id
,g_input_table_rec(2).input_value_id
,g_input_table_rec(3).input_value_id
,g_input_table_rec(4).input_value_id
,g_input_table_rec(5).input_value_id
,g_input_table_rec(6).input_value_id
,g_input_table_rec(7).input_value_id)
AND peev.input_value_id = piv.input_value_id
AND g_fin_end_date BETWEEN piv.effective_start_date AND piv.effective_end_date;
t_challan_entry_asg_tab.DELETE;
SELECT paa.assignment_id
,paa.payroll_action_id
,paf.person_id
FROM pay_assignment_actions paa
,per_all_assignments_f paf
WHERE paa.assignment_action_id = p_assignment_action_id
AND paa.assignment_id = paf.assignment_id
AND ROWNUM =1;
SELECT DISTINCT(hscl.segment1) gre
FROM per_all_assignments_f paf
,hr_soft_coding_keyflex hscl
WHERE hscl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
AND paf.assignment_id =paf.assignment_id
AND paf.assignment_id = p_assignment_id
AND ( paf.effective_start_date BETWEEN g_fin_start_date AND g_end_date
OR g_fin_start_date BETWEEN paf.effective_start_date AND paf.effective_end_date
)
AND hscl.segment1 LIKE g_gre_id;
SELECT GREATEST(asg.effective_start_date,g_fin_start_date) start_date
,LEAST(asg.effective_end_date,g_fin_end_date) end_date
,scl.segment1
FROM per_assignments_f asg
,hr_soft_coding_keyflex scl
,pay_assignment_actions paa
WHERE asg.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
AND paa.assignment_action_id = p_assignment_action_id
AND asg.assignment_id = paa.assignment_id
AND scl.segment1 LIKE g_gre_id
AND ( asg.effective_start_date BETWEEN g_fin_start_date AND g_end_date
OR g_fin_start_date BETWEEN asg.effective_start_date AND asg.effective_end_date
)
AND asg.business_group_id = g_bg_id
ORDER BY 1 ;
SELECT paf.payroll_id
FROM per_all_assignments_f paf
WHERE paf.assignment_id =p_assignment_id
AND p_date BETWEEN paf.effective_start_date AND paf.effective_end_date;
SELECT TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) run_asg_action_id
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_assignments_f paf
WHERE paf.assignment_id = paa.assignment_id
AND paf.assignment_id = p_assignment_id
AND paa.tax_unit_id = p_tax_unit_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type IN('R','Q','I','B')
AND ppa.payroll_id = paf.payroll_id
AND ppa.action_status ='C'
AND ppa.effective_date between p_start_date and p_end_date
AND paa.source_action_id IS NULL
AND (1 = DECODE(ppa.action_type,'I',1,0)
OR EXISTS (SELECT ''
FROM pay_action_interlocks intk,
pay_assignment_actions paa1,
pay_payroll_actions ppa1
WHERE intk.locked_action_id = paa.assignment_Action_id
AND intk.locking_action_id = paa1.assignment_action_id
AND paa1.payroll_action_id =ppa1.payroll_action_id
AND paa1.assignment_id = p_assignment_id
AND ppa1.action_type in('P','U')
AND ppa.action_type in('R','Q','B')
AND ppa1.action_status ='C'
AND ppa1.effective_date BETWEEN p_start_date and p_end_date
AND ROWNUM =1 ));
SELECT ppa.date_earned run_date
,ppa.payroll_id
FROM pay_payroll_actions ppa,
pay_assignment_actions paa
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_action_id = l_run_assact;
SELECT 1
FROM pay_action_information pai,
pay_assignment_actions paa
WHERE pai.action_information_category = 'IN_24Q_ORG'
AND pai.action_context_type = 'PA'
AND pai.action_context_id = p_payroll_action_id
AND pai.action_information1 = p_gre_id
AND ROWNUM =1;
SELECT NVL(pos.actual_termination_date,(fnd_date.string_to_date('31-12-4712','DD-MM-YYYY')))
FROM per_all_assignments_f asg
,per_periods_of_service pos
WHERE asg.person_id = pos.person_id
AND asg.assignment_id = p_assignment_id
AND asg.business_group_id = pos.business_group_id
AND asg.business_group_id = g_bg_id
AND NVL(pos.actual_termination_date,(to_date('31-12-4712','DD-MM-YYYY')))
BETWEEN asg.effective_start_date AND asg.effective_end_date
ORDER BY 1 DESC;
g_asg_tab.DELETE;
p_person_data.DELETE;
SELECT DISTINCT action_information1 challan_no
,action_information3 gre_id
FROM pay_action_information
WHERE action_information_category = 'IN_24Q_DEDUCTEE'
AND action_context_type = 'AAP'
AND action_context_id IN (SELECT assignment_action_id
FROM pay_assignment_actions
WHERE payroll_action_id = p_payroll_action_id)
ORDER BY action_information3;
SELECT DISTINCT action_information2
, action_information4
, action_information_id
, object_version_number
FROM pay_action_information
WHERE action_information_category = 'IN_24Q_DEDUCTEE'
AND action_context_type = 'AAP'
AND action_information3 = p_gre_id
AND action_information1 = p_challan
AND action_context_id IN (SELECT assignment_action_id
FROM pay_assignment_actions
WHERE payroll_action_id = p_payroll_action_id)
ORDER BY action_information2
, action_information4;
SELECT DISTINCT action_information3 gre_id
FROM pay_action_information
WHERE action_information_category = 'IN_24Q_PERSON'
AND action_context_type = 'AAP'
AND action_context_id IN (SELECT assignment_action_id
FROM pay_assignment_actions
WHERE payroll_action_id = p_payroll_action_id)
ORDER BY action_information3;
SELECT DISTINCT action_information1 person_id
, source_id
, action_information_id
, object_version_number
FROM pay_action_information
WHERE action_information_category = 'IN_24Q_PERSON'
AND action_context_type = 'AAP'
AND action_information3 = p_gre_id
AND action_context_id IN (SELECT assignment_action_id
FROM pay_assignment_actions
WHERE payroll_action_id = p_payroll_action_id)
ORDER BY LENGTH(action_information1)
,action_information1
,source_id;
pay_action_information_api.update_action_information
(p_validate => FALSE
,p_action_information_id => cur_rec.action_information_id
,p_object_version_number => cur_rec.object_version_number
,p_action_information25 => l_index
);
pay_action_information_api.update_action_information
(p_validate => FALSE
,p_action_information_id => cur_rec.action_information_id
,p_object_version_number => cur_rec.object_version_number
,p_action_information11 => l_index
);