[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
pay_in_utils.set_location(g_debug,'Number of assignment actions deleted : '|| g_count_sal_delete ,4);
FOR i IN 1..g_count_sal_delete -1
LOOP
pay_in_utils.trace('Last Action Context ID is : ', g_sal_data_rec_del(i).last_action_context_id );
pay_in_utils.trace('Number of assignment actions updated : ', g_count_sal_update );
FOR i IN 1..g_count_sal_update -1
LOOP
pay_in_utils.trace('Last Action Context ID is : ', g_sal_data_rec_upd(i).last_action_context_id );
pay_in_utils.set_location(g_debug,'Number of element entries deleted : '|| g_count_ee_delete ,4);
FOR i IN 1..g_count_ee_delete -1
LOOP
pay_in_utils.trace('Last Action Context ID is : ', g_ee_data_rec_del(i).last_action_context_id );
pay_in_utils.trace('Number of element entries updated : ', g_count_ee_update );
FOR i IN 1..g_count_ee_update -1
LOOP
pay_in_utils.trace('Last Action Context ID is : ', g_ee_data_rec_upd(i).last_action_context_id );
pay_in_utils.trace('Number of updated challans is : ', g_count_challan_upd);
SELECT 1
FROM pay_action_information
WHERE action_information_category = 'IN_24QC_PERSON'
AND action_context_type = 'AAP'
AND source_id = p_source_id
AND action_information10 = p_mode
AND action_context_id IN
(SELECT assignment_action_id
FROM pay_assignment_actions
WHERE payroll_action_id = g_payroll_action_id
);
SELECT 1
FROM pay_action_information
WHERE action_information_category = 'IN_24QC_DEDUCTEE'
AND action_context_type = 'AAP'
AND source_id = p_source_id
AND action_information15 = p_mode
AND action_context_id IN
(SELECT assignment_action_id
FROM pay_assignment_actions
WHERE payroll_action_id = g_payroll_action_id
);
SELECT 1
FROM pay_action_information
WHERE action_information_category = 'IN_24QC_ORG'
AND action_context_type = 'PA'
AND action_context_id = g_payroll_action_id
AND action_information1 = g_gre_id;
SELECT 1
FROM pay_action_information
WHERE action_information_category = 'IN_24QC_CHALLAN'
AND action_context_type = 'PA'
AND action_context_id = g_payroll_action_id
AND action_information1 = p_act_information1
AND action_information3 = g_gre_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 1
FROM pay_action_information pai
,pay_payroll_actions ppa
WHERE pai.action_information_category = 'IN_24QC_ORG'
AND pai.action_context_type = 'PA'
AND pai.action_information1 = g_gre_id
AND pai.action_information3 = g_year||g_quarter
AND pai.action_information30 = g_24qc_reference
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;
SELECT DECODE(action_information_category,'IN_24Q_ORG',action_information21,action_information7) deductor_type
FROM pay_action_information
WHERE action_information_category IN ('IN_24Q_ORG','IN_24QC_ORG')
AND action_context_id = p_last_act_cxt_id
AND action_context_type = 'PA'
AND action_information1 = g_gre_id
AND action_information2 = p_tan_number
AND action_information3 = g_year||g_quarter
ORDER BY action_context_id DESC;
SELECT hoi.org_information1 ,hoi.org_information6 into l_tan_number,l_deductor_type
FROM hr_organization_information hoi
,hr_organization_units hou
WHERE hoi.organization_id = g_gre_id
AND hoi.org_information_context = 'PER_IN_INCOME_TAX_DF'
AND hou.organization_id = hoi.organization_id
AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND g_qr_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
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 = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_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;
FUNCTION update_challans(p_challan_number IN VARCHAR2)
RETURN VARCHAR2
IS
CURSOR c_24q_ee_sum(p_challan_number VARCHAR2
,p_24q_pay_action_id NUMBER
)
IS
SELECT SUM(NVL(pai.action_information9,0)) amount_deposited
FROM pay_action_information pai
,pay_assignment_actions paa
WHERE pai.action_information_category = 'IN_24Q_DEDUCTEE'
AND paa.assignment_action_id = pai.action_context_id
AND pai.action_information1 = p_challan_number
AND paa.payroll_action_id = p_24q_pay_action_id;
l_procedure := g_package ||'.update_challans';
pay_in_utils.set_location(g_debug,'Leaving: '|| g_package||'.update_challans',1);
END update_challans;
SELECT pai.action_information1
,MAX(pai.action_context_id)
FROM pay_action_information pai
,pay_action_interlocks locks
,pay_assignment_actions paa
,pay_assignment_actions paa_24qc
WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
AND locks.locking_action_id = pai.action_context_id
AND locks.locked_action_id = paa.assignment_action_id
AND paa.payroll_action_id = g_24q_payroll_act_id
AND paa.assignment_id = pai.assignment_id
AND pai.action_information2 = g_year||g_quarter
AND paa_24qc.assignment_id = pai.assignment_id
AND paa_24qc.assignment_action_id = pai.action_context_id
AND pai.action_information15 = 'A'
AND pai.source_id = p_element_entry_id
AND paa_24qc.payroll_action_id IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
)
GROUP BY pai.action_information1;
CURSOR c_delete_mode_entries(p_payroll_action_id NUMBER)
IS
SELECT pai.source_id element_entry_id
,pai.assignment_id assignment_id
,pai.action_context_id last_action_context_id
FROM pay_action_information pai
,pay_assignment_actions paa
WHERE pai.action_information_category LIKE 'IN_24Q%_DEDUCTEE'
AND pai.action_context_id = paa.assignment_action_id
AND(
paa.payroll_action_id IN (
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
)
)
AND pai.action_information3 = g_gre_id
AND (
pai.source_id NOT IN
(SELECT entry.element_entry_id
FROM pay_element_entries_f entry
,pay_element_types_f types
WHERE entry.assignment_id = pai.assignment_id
AND entry.element_type_id = types.element_type_id
AND types.element_name = 'Income Tax Challan Information'
AND types.legislation_code = 'IN'
AND entry.effective_start_date BETWEEN g_qr_start_date
AND g_qr_end_date
)
OR
pai.action_information1 <> pay_in_utils.get_ee_value(pai.source_id,'Challan or Voucher Number')
)
AND (
pai.source_id NOT IN
(SELECT pai.source_id
FROM pay_action_information pai
,pay_action_interlocks locks
,pay_assignment_actions paa
,pay_assignment_actions masters
WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
AND pai.action_information15 = 'D'
AND locks.locking_action_id = pai.action_context_id
AND locks.locked_action_id = paa.assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id
AND paa.assignment_id = pai.assignment_id
AND masters.assignment_id = pai.assignment_id
AND masters.assignment_action_id = pai.action_context_id
AND masters.payroll_action_id IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
)
)
OR
pay_in_utils.get_ee_value(pai.source_id,'Challan or Voucher Number') NOT IN
(
(SELECT pai.action_information1
FROM pay_action_information pai
,pay_action_interlocks locks
,pay_assignment_actions paa
,pay_assignment_actions masters
WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
AND pai.action_information15 = 'A'
AND locks.locking_action_id = pai.action_context_id
AND locks.locked_action_id = paa.assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id
AND paa.assignment_id = pai.assignment_id
AND masters.assignment_id = pai.assignment_id
AND masters.assignment_action_id = pai.action_context_id
AND masters.payroll_action_id IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
)
)
)
)
ORDER BY 3 DESC;
SELECT entry.element_entry_id element_entry_id
,entry.assignment_id assignment_id
,NULL last_action_context_id
,pay_in_utils.get_ee_value(entry.element_entry_id,'Challan or Voucher Number')
FROM pay_element_entries_f entry
,pay_element_types_f types
WHERE entry.element_type_id = types.element_type_id
AND types.element_name = 'Income Tax Challan Information'
AND types.legislation_code = 'IN'
AND entry.effective_start_date BETWEEN g_qr_start_date AND g_qr_end_date
AND (
entry.element_entry_id NOT IN
(SELECT pai.source_id
FROM pay_action_information pai
,pay_assignment_actions paa
WHERE pai.action_information_category = 'IN_24Q_DEDUCTEE'
AND pai.action_context_id = paa.assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id
)
OR
pay_in_utils.get_ee_value(entry.element_entry_id,'Challan or Voucher Number') NOT IN
(SELECT pai.action_information1
FROM pay_action_information pai
,pay_assignment_actions paa
WHERE pai.action_information_category = 'IN_24Q_DEDUCTEE'
AND pai.action_context_id = paa.assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id
AND pai.source_id = entry.element_entry_id
)
)
AND (
entry.element_entry_id NOT IN
(SELECT pai.source_id
FROM pay_action_information pai
,pay_action_interlocks locks
,pay_assignment_actions paa
,pay_assignment_actions paa_24qc
WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
AND locks.locking_action_id = pai.action_context_id
AND locks.locked_action_id = paa.assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id
AND paa.assignment_id = pai.assignment_id
AND pai.action_information2 = g_year||g_quarter
AND paa_24qc.assignment_id = pai.assignment_id
AND paa_24qc.assignment_action_id = pai.action_context_id
AND paa_24qc.payroll_action_id IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
)
)
OR
pay_in_utils.get_ee_value(entry.element_entry_id,'Challan or Voucher Number') NOT IN
(
(SELECT pai.action_information1
FROM pay_action_information pai
,pay_action_interlocks locks
,pay_assignment_actions paa
,pay_assignment_actions paa_24qc
WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
AND locks.locking_action_id = pai.action_context_id
AND locks.locked_action_id = paa.assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id
AND paa.assignment_id = pai.assignment_id
AND pai.action_information2 = g_year||g_quarter
AND paa_24qc.assignment_id = pai.assignment_id
AND paa_24qc.assignment_action_id = pai.action_context_id
AND pai.source_id = entry.element_entry_id
AND paa_24qc.payroll_action_id IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
)
)
)
)
AND pay_in_utils.get_ee_value(entry.element_entry_id,'Challan or Voucher Number') IN
(SELECT bank.org_information4||' - '||hoi.org_information3 ||' - ' ||to_char(fnd_date.canonical_to_date(hoi.org_information2),'DD-Mon-RRRR') org_information3
FROM hr_organization_units hou
,hr_organization_information hoi
,hr_organization_information bank
WHERE hoi.organization_id = hou.organization_id
AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
AND hoi.org_information1 = g_tax_year
AND bank.org_information_context = 'PER_IN_CHALLAN_BANK'
and bank.organization_id = hoi.organization_id
AND hoi.org_information5 = bank.org_information_id
AND hoi.org_information13 = g_quarter
AND hoi.organization_id = g_gre_id
UNION
SELECT 'BOOK - '||hoi.org_information3 ||' - ' ||to_char(fnd_date.canonical_to_date(hoi.org_information2),'DD-Mon-RRRR') org_information3
FROM hr_organization_units hou
,hr_organization_information hoi
WHERE hoi.organization_id = hou.organization_id
AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
AND hoi.org_information1 = g_tax_year
AND hoi.org_information13 = g_quarter
AND hoi.org_information5 IS NULL
AND hoi.organization_id = g_gre_id
)
AND (
(
g_correction_mode IN ('%','C3')
AND
pay_in_utils.get_ee_value(entry.element_entry_id,'Challan or Voucher Number') IN
(
SELECT action_information1
FROM pay_action_information
WHERE action_information_category IN('IN_24QC_CHALLAN','IN_24Q_CHALLAN')
AND (
action_information15 = p_payroll_action_id
AND action_information_category = 'IN_24QC_CHALLAN'
AND action_information2 = g_year||g_quarter
AND action_information15 IN
(
(SELECT bank.org_information4||' - '||hoi.org_information3 ||' - ' ||to_char(fnd_date.canonical_to_date(hoi.org_information2),'DD-Mon-RRRR') org_information3
FROM hr_organization_units hou
,hr_organization_information hoi
,hr_organization_information bank
WHERE hoi.organization_id = hou.organization_id
AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
AND hoi.org_information1 = g_tax_year
AND bank.org_information_context = 'PER_IN_CHALLAN_BANK'
and bank.organization_id = hoi.organization_id
AND hoi.org_information5 = bank.org_information_id
AND hoi.org_information13 = g_quarter
AND hoi.organization_id = g_gre_id
UNION
SELECT 'BOOK - '||hoi.org_information3 ||' - ' ||to_char(fnd_date.canonical_to_date(hoi.org_information2),'DD-Mon-RRRR') org_information3
FROM hr_organization_units hou
,hr_organization_information hoi
WHERE hoi.organization_id = hou.organization_id
AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
AND hoi.org_information1 = g_tax_year
AND hoi.org_information13 = g_quarter
AND hoi.org_information5 IS NULL
AND hoi.organization_id = g_gre_id
)
)
)
OR
(
action_information_category = 'IN_24Q_CHALLAN'
AND action_context_id = p_payroll_action_id
)
)
)
OR
(g_correction_mode IN ('%','C9')
AND
pay_in_utils.get_ee_value(entry.element_entry_id,'Challan or Voucher Number') NOT IN
(
SELECT action_information1
FROM pay_action_information
WHERE action_information_category IN('IN_24QC_CHALLAN','IN_24Q_CHALLAN')
AND (
action_information15 = p_payroll_action_id
AND action_information_category = 'IN_24QC_CHALLAN'
AND action_information2 = g_year||g_quarter
AND action_information15 IN
(SELECT bank.org_information4||' - '||hoi.org_information3 ||' - ' ||to_char(fnd_date.canonical_to_date(hoi.org_information2),'DD-Mon-RRRR') org_information3
FROM hr_organization_units hou
,hr_organization_information hoi
,hr_organization_information bank
WHERE hoi.organization_id = hou.organization_id
AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
AND hoi.org_information1 = g_tax_year
AND bank.org_information_context = 'PER_IN_CHALLAN_BANK'
and bank.organization_id = hoi.organization_id
AND hoi.org_information5 = bank.org_information_id
AND hoi.org_information13 = g_quarter
AND hoi.organization_id = g_gre_id
UNION
SELECT 'BOOK - '||hoi.org_information3 ||' - ' ||to_char(fnd_date.canonical_to_date(hoi.org_information2),'DD-Mon-RRRR') org_information3
FROM hr_organization_units hou
,hr_organization_information hoi
WHERE hoi.organization_id = hou.organization_id
AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND hoi.org_information_context = 'PER_IN_IT_CHALLAN_INFO'
AND hoi.org_information1 = g_tax_year
AND hoi.org_information13 = g_quarter
AND hoi.org_information5 IS NULL
AND hoi.organization_id = g_gre_id
)
)
OR
(
action_information_category = 'IN_24Q_CHALLAN'
AND action_context_id = p_payroll_action_id
)
)
)
)
ORDER BY 4;
CURSOR c_update_mode_entries(p_payroll_action_id NUMBER
)
IS
SELECT pai.source_id element_entry_id
,pai.assignment_id assignment_id
,pai.action_context_id last_action_context_id
,pep.effective_end_date
FROM pay_action_information pai
,pay_assignment_actions paa
,per_people_f pep
,per_assignments_f asg
WHERE pai.action_information_category = 'IN_24Q_DEDUCTEE'
AND pai.action_context_id = paa.assignment_action_id
AND pai.action_information3 = g_gre_id
AND paa.payroll_action_id = p_payroll_action_id
AND asg.assignment_id = pai.assignment_id
AND asg.person_id = pep.person_id
AND asg.business_group_id = pep.business_group_id
AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND (
(
(NVL(pai.action_information13,'0') <> (SELECT NVL(paei.aei_information2,'0')
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.assignment_id = asg.assignment_id
AND paei.aei_information1 = g_tax_year
AND assignment_end_date(asg.assignment_id) BETWEEN paa.effective_start_date AND paa.effective_end_date
AND ROWNUM = 1)
)
OR
(TO_NUMBER(remove_curr_format(pai.action_information6)) <> TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Income Tax Deducted'))))
OR
(TO_NUMBER(remove_curr_format(pai.action_information7)) <> TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Surcharge Deducted'))))
OR
(TO_NUMBER(remove_curr_format(pai.action_information8)) <> TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Education Cess Deducted'))))
OR
(TO_NUMBER(remove_curr_format(pai.action_information5)) <> TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Taxable Income'))))
OR
(pai.action_information4 <> pay_in_utils.get_ee_value(pai.source_id,'Payment Date'))
OR
(pai.action_information12 <> hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title))
OR
(NVL(pai.action_information11,'0') <> NVL(pep.per_information14,'0'))
OR(
(
g_correction_mode IN ('C5','%')
AND (pai.action_information10 NOT IN('APPLIEDFOR','PANNOTAVBL'))
AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))NOT IN('APPLIEDFOR','PANNOTAVBL'))
AND (pai.action_information10 <> (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
)
OR
(
g_correction_mode NOT IN ('C5')
AND (pai.action_information10 <> (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))IN('APPLIEDFOR','PANNOTAVBL'))
)
)
)
AND pay_in_utils.get_ee_value(pai.source_id,'Challan or Voucher Number') = pai.action_information1
)
AND assignment_end_date(asg.assignment_id) BETWEEN asg.effective_start_date AND asg.effective_end_date
AND assignment_end_date(asg.assignment_id) BETWEEN pep.effective_start_date AND pep.effective_end_date
ORDER BY 4 DESC;
SELECT pai_locking.action_context_id last_action_context_id
FROM pay_action_information pai_locking
,pay_action_interlocks locks
,pay_assignment_actions paa
WHERE pai_locking.action_information_category = 'IN_24QC_DEDUCTEE'
AND pai_locking.action_information3 = g_gre_id
AND pai_locking.action_information15 = p_mode
AND pai_locking.action_context_id = paa.assignment_action_id
AND paa.payroll_action_id IN
(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
)
AND locks.locking_action_id = pai_locking.action_context_id
AND locks.locked_action_id = p_24q_arc_action_context_id
AND pai_locking.assignment_id = p_assignment_id
AND pai_locking.source_id = p_element_entry_id
ORDER BY pai_locking.action_context_id DESC;
SELECT pai.action_context_id
FROM pay_action_information pai
,per_people_f pep
,per_assignments_f asg
,pay_assignment_actions paa
WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
AND paa.assignment_action_id = pai.action_context_id
AND pai.source_id = p_element_entry_id
AND paa.payroll_action_id IN
(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
)
AND pai.action_information3 = g_gre_id
AND pai.action_context_id = p_assignment_action_id
AND asg.assignment_id = pai.assignment_id
AND asg.person_id = pep.person_id
AND asg.business_group_id = pep.business_group_id
AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND (
(NVL(pai.action_information18,'0') <> (SELECT NVL(paei.aei_information2,'0')
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.assignment_id = asg.assignment_id
AND paei.aei_information1 = g_tax_year
AND assignment_end_date(asg.assignment_id) BETWEEN paa.effective_start_date AND paa.effective_end_date
AND ROWNUM = 1)
)
OR
(TO_NUMBER(remove_curr_format(pai.action_information6)) <> TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Income Tax Deducted'))))
OR
(TO_NUMBER(remove_curr_format(pai.action_information7)) <> TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Surcharge Deducted'))))
OR
(TO_NUMBER(remove_curr_format(pai.action_information8)) <> TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Education Cess Deducted'))))
OR
(TO_NUMBER(remove_curr_format(pai.action_information5)) <> TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Taxable Income'))))
OR
(pai.action_information4 <> pay_in_utils.get_ee_value(pai.source_id,'Payment Date'))
OR
(pai.action_information10 <> hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title))
OR(--Checking PAN Number
(
g_correction_mode IN ('C5','%')
AND (pai.action_information9 NOT IN('APPLIEDFOR','PANNOTAVBL'))
AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))NOT IN('APPLIEDFOR','PANNOTAVBL'))
AND (pai.action_information9 <> (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
)
OR
(
g_correction_mode NOT IN ('C5')
AND (pai.action_information9 <> (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))IN('APPLIEDFOR','PANNOTAVBL'))
)
)
OR -- Checking PAN reference Number
(NVL(pai.action_information11,'0') <> NVL(pep.per_information14,'0'))
)
AND assignment_end_date(asg.assignment_id) BETWEEN asg.effective_start_date AND asg.effective_end_date
AND assignment_end_date(asg.assignment_id) BETWEEN pep.effective_start_date AND pep.effective_end_date
AND NOT EXISTS
(
SELECT 1
FROM pay_action_information pai
,per_people_f pep
,per_assignments_f asg
WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
AND pai.source_id = p_element_entry_id
AND pai.action_information3 = g_gre_id
AND pai.action_context_id = p_assignment_action_id
AND asg.assignment_id = pai.assignment_id
AND asg.person_id = pep.person_id
AND asg.business_group_id = pep.business_group_id
AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND (
(NVL(pai.action_information18,'0') = (SELECT NVL(paei.aei_information2,'0')
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.assignment_id = asg.assignment_id
AND paei.aei_information1 = g_tax_year
AND assignment_end_date(asg.assignment_id) BETWEEN paa.effective_start_date AND paa.effective_end_date
AND ROWNUM = 1)
)
AND
(TO_NUMBER(remove_curr_format(pai.action_information6)) = TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Income Tax Deducted'))))
AND
(TO_NUMBER(remove_curr_format(pai.action_information7)) = TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Surcharge Deducted'))))
AND
(TO_NUMBER(remove_curr_format(pai.action_information8)) = TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Education Cess Deducted'))))
AND
(TO_NUMBER(remove_curr_format(pai.action_information5)) = TO_NUMBER(remove_curr_format(pay_in_utils.get_ee_value(pai.source_id,'Taxable Income'))))
AND
(pai.action_information4 = pay_in_utils.get_ee_value(pai.source_id,'Payment Date'))
AND
(pai.action_information10 = hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title))
AND(--Checking PAN Number
(
g_correction_mode IN ('C5','%')
AND (pai.action_information9 NOT IN('APPLIEDFOR','PANNOTAVBL'))
AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))NOT IN('APPLIEDFOR','PANNOTAVBL'))
AND (pai.action_information9 = (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
)
OR
(
g_correction_mode NOT IN ('C5')
AND (pai.action_information9 = (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))IN('APPLIEDFOR','PANNOTAVBL'))
)
)
AND -- Checking PAN reference Number
(NVL(pai.action_information11,'0') = NVL(pep.per_information14,'0'))
)
AND assignment_end_date(asg.assignment_id) BETWEEN asg.effective_start_date AND asg.effective_end_date
AND assignment_end_date(asg.assignment_id) BETWEEN pep.effective_start_date AND pep.effective_end_date
);
SELECT MAX(pai.action_context_id) last_action_context_id
,pai.source_id element_entry_id
,pai.assignment_id assignment_id
FROM pay_action_information pai
,pay_assignment_actions paa
WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
AND pai.action_information3 = g_gre_id
AND pai.action_information2 = g_year||g_quarter
AND pai.action_information15 = p_mode
AND pai.action_context_id = paa.assignment_action_id
AND paa.payroll_action_id IN
(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
)
AND (
p_flag = 1
OR
(
pai.source_id NOT IN(
SELECT pai.source_id
FROM pay_action_information pai
,pay_assignment_actions paa
WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
AND pai.action_information3 = g_gre_id
AND pai.action_information2 = g_year||g_quarter
AND pai.action_information15 = 'D'
AND pai.action_context_id = paa.assignment_action_id
AND paa.payroll_action_id IN
(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
)
)
AND pai.source_id IN
(SELECT entry.element_entry_id
FROM pay_element_entries_f entry
,pay_element_types_f types
WHERE entry.assignment_id = pai.assignment_id
AND entry.element_type_id = types.element_type_id
AND types.element_name = 'Income Tax Challan Information'
AND types.legislation_code = 'IN'
AND entry.effective_start_date BETWEEN g_qr_start_date
AND g_qr_end_date
)
)
)
GROUP BY pai.source_id,pai.assignment_id;
SELECT entry.element_entry_id element_entry_id
,entry.assignment_id assignment_id
,pay_in_utils.get_ee_value(entry.element_entry_id,'Challan or Voucher Number') challan
FROM pay_element_entries_f entry
,pay_element_types_f types
WHERE entry.element_type_id = types.element_type_id
AND types.element_name = 'Income Tax Challan Information'
AND types.legislation_code = 'IN'
AND entry.effective_start_date BETWEEN g_qr_start_date AND g_qr_end_date;
SELECT 1
FROM per_assignments_f
WHERE business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND assignment_id = p_assignment_id;
pay_in_utils.set_location(g_debug,'Finding element entries deleted since last statement ',2);
FOR c_rec IN c_delete_mode_entries(p_payroll_action_id)
LOOP
l_bg_check := 0;
g_ee_data_rec_del(g_count_ee_delete).last_action_context_id := c_rec.last_action_context_id;
g_ee_data_rec_del(g_count_ee_delete).assignment_id := c_rec.assignment_id;
g_ee_data_rec_del(g_count_ee_delete).element_entry_id := c_rec.element_entry_id;
g_ee_data_rec_del(g_count_ee_delete).deductee_mode := 'D';
g_count_ee_delete := g_count_ee_delete + 1;
pay_in_utils.set_location(g_debug,'Finding element entries updated since last statement ',4);
FOR csr_rec IN c_update_mode_entries(p_payroll_action_id)
LOOP
pay_in_utils.set_location(g_debug,'Diff between live data and 24Q Archival found', 1);
pay_in_utils.set_location(g_debug,'Mid way in finding updated element entries ', 5);
g_ee_data_rec_upd(g_count_ee_update).last_action_context_id := csr_rec.last_action_context_id;
g_ee_data_rec_upd(g_count_ee_update).assignment_id := csr_rec.assignment_id;
g_ee_data_rec_upd(g_count_ee_update).element_entry_id := csr_rec.element_entry_id;
g_ee_data_rec_upd(g_count_ee_update).deductee_mode := 'U';
g_count_ee_update := g_count_ee_update + 1;
g_ee_data_rec_upd(g_count_ee_update).last_action_context_id := l_dummy;
g_ee_data_rec_upd(g_count_ee_update).assignment_id := csr_rec.assignment_id;
g_ee_data_rec_upd(g_count_ee_update).element_entry_id := csr_rec.element_entry_id;
g_ee_data_rec_upd(g_count_ee_update).deductee_mode := 'U';
g_count_ee_update := g_count_ee_update + 1;
pay_in_utils.set_location(g_debug,'Additional Checking on updated element entries ', 6);
FOR i IN 1.. g_count_ee_update - 1
LOOP
IF (
(g_ee_data_rec_upd(i).last_action_context_id = c_rec.last_action_context_id)
AND
(g_ee_data_rec_upd(i).assignment_id = c_rec.assignment_id)
AND
(g_ee_data_rec_upd(i).element_entry_id = c_rec.element_entry_id)
AND
(g_ee_data_rec_upd(i).deductee_mode = 'U')
)
THEN
l_flag := FALSE;
g_ee_data_rec_upd(g_count_ee_update).last_action_context_id := c_rec.last_action_context_id;
g_ee_data_rec_upd(g_count_ee_update).assignment_id := c_rec.assignment_id;
g_ee_data_rec_upd(g_count_ee_update).element_entry_id := c_rec.element_entry_id;
g_ee_data_rec_upd(g_count_ee_update).deductee_mode := 'U';
g_count_ee_update := g_count_ee_update + 1;
pay_in_utils.set_location(g_debug,'Now Searching for those updated element entries that were 24QC+',1);
g_ee_data_rec_upd(g_count_ee_update).last_action_context_id := c_rec.last_action_context_id;
g_ee_data_rec_upd(g_count_ee_update).assignment_id := c_rec.assignment_id;
g_ee_data_rec_upd(g_count_ee_update).element_entry_id := c_rec.element_entry_id;
g_ee_data_rec_upd(g_count_ee_update).deductee_mode := 'U';
g_count_ee_update := g_count_ee_update + 1;
SELECT NVL(action_information2,0)
FROM pay_action_information
WHERE action_information_category = p_category
AND action_information1 = p_component_name
AND action_context_id = p_context_id
AND source_id = p_source_id;
CURSOR c_delete_mode_entries(p_payroll_action_id NUMBER)
IS
SELECT pai.source_id source_id
,pai.assignment_id assignment_id
,pai.action_context_id last_action_context_id
FROM pay_action_information pai
,pay_assignment_actions paa
WHERE pai.action_information_category LIKE 'IN_24Q%_PERSON'
AND fnd_number.canonical_to_number(pai.action_information1)
BETWEEN p_start_person AND p_end_person
AND pai.action_context_id = paa.assignment_action_id
AND
( pai.action_information_category = 'IN_24Q_PERSON'
OR
(pai.action_information_category = 'IN_24QC_PERSON' AND pai.action_information10 IN ('A'))
)
AND(
paa.payroll_action_id IN (
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
)
)
AND pai.action_information3 = g_gre_id
AND (
pai.source_id NOT IN
(
SELECT
paa.assignment_action_id 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 paa.assignment_id = pai.assignment_id
AND paa.tax_unit_id = pai.action_information3
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 g_start_date and g_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 ppa1.action_type in('P','U')
AND ppa.action_type in('R','Q','B')
AND ppa1.action_status ='C'
AND ppa1.effective_date BETWEEN g_start_date and g_end_date
AND ROWNUM =1 ))
)
)
AND (
pai.source_id NOT IN
(SELECT pai.source_id
FROM pay_action_information pai
,pay_action_interlocks locks
,pay_assignment_actions paa
,pay_assignment_actions masters
WHERE pai.action_information_category = 'IN_24QC_PERSON'
AND pai.action_information10 IN ('D')
AND locks.locking_action_id = pai.action_context_id
AND locks.locked_action_id = paa.assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id
AND paa.assignment_id = pai.assignment_id
AND masters.assignment_id = pai.assignment_id
AND masters.assignment_action_id = pai.action_context_id
AND masters.payroll_action_id IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
)
)
)
ORDER BY 2 DESC;
SELECT a.source_id,a.assignment_id,a.last_action_context_id
FROM
(SELECT
FND_NUMBER.CANONICAL_TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) source_id
,paf.assignment_id assignment_id
,NULL last_action_context_id
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_assignments_f paf
WHERE paf.person_id BETWEEN p_start_person AND p_end_person
AND paf.assignment_id = paa.assignment_id
AND paa.tax_unit_id = g_gre_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 g_start_date and g_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 ppa1.action_type in('P','U')
AND ppa.action_type in('R','Q','B')
AND ppa1.action_status ='C'
AND ppa1.effective_date BETWEEN g_start_date and g_end_date
AND ROWNUM =1 ))
GROUP BY paf.assignment_id) a
WHERE (
a.source_id NOT IN
(SELECT pai.source_id
FROM pay_action_information pai
,pay_assignment_actions paa
WHERE pai.action_information_category = 'IN_24Q_PERSON'
AND pai.action_context_id = paa.assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id
)
)
AND (
a.source_id NOT IN
(SELECT pai.source_id
FROM pay_action_information pai
,pay_action_interlocks locks
,pay_assignment_actions paa
,pay_assignment_actions paa_24qc
WHERE pai.action_information_category = 'IN_24QC_PERSON'
AND locks.locking_action_id = pai.action_context_id
AND locks.locked_action_id = paa.assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id
AND paa.assignment_id = pai.assignment_id
AND pai.action_information2 = g_year||g_quarter
AND paa_24qc.assignment_id = pai.assignment_id
AND paa_24qc.assignment_action_id = pai.action_context_id
AND paa_24qc.payroll_action_id IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
)
)
)
AND (
a.source_id NOT IN
(SELECT pai.source_id
FROM pay_action_information pai
,pay_action_interlocks locks
,pay_assignment_actions paa
,pay_assignment_actions paa_24qc
WHERE pai.action_information_category = 'IN_24QC_PERSON'
AND locks.locking_action_id = pai.action_context_id
AND locks.locked_action_id = paa.assignment_action_id
AND paa.payroll_action_id = p_payroll_action_id
AND paa.assignment_id = pai.assignment_id
AND pai.action_information2 = g_year||g_quarter
AND paa_24qc.assignment_id = pai.assignment_id
AND paa_24qc.assignment_action_id = pai.action_context_id
AND paa_24qc.payroll_action_id IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
)
)
)
AND (
a.source_id NOT IN
(SELECT pai.source_id
FROM pay_action_information pai
,pay_assignment_actions paa_24qc
WHERE pai.action_information_category = 'IN_24QC_PERSON'
AND pai.action_information3 = g_gre_id
AND pai.action_information2 = g_year||g_quarter
AND paa_24qc.assignment_id = pai.assignment_id
AND paa_24qc.assignment_action_id = pai.action_context_id
AND paa_24qc.payroll_action_id IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
)
)
)
ORDER BY 2 DESC;
CURSOR c_update_mode_entries(p_payroll_action_id NUMBER
)
IS
SELECT DISTINCT
pai.source_id source_id
,pai.assignment_id assignment_id
,pai.action_context_id last_action_context_id
,pep.effective_end_date
FROM pay_action_information pai
,pay_assignment_actions paa
,per_people_f pep
,per_assignments_f asg
WHERE pai.action_information_category = 'IN_24Q_PERSON'
AND fnd_number.canonical_to_number(pai.action_information1)
BETWEEN p_start_person AND p_end_person
AND pai.action_context_id = paa.assignment_action_id
AND pai.action_information3 = g_gre_id
AND paa.payroll_action_id = p_payroll_action_id
AND asg.assignment_id = pai.assignment_id
AND asg.person_id = pep.person_id
AND asg.business_group_id = pep.business_group_id
AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND pai.source_id IN
(
SELECT
paa.assignment_action_id run_asg_action_id
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_assignments_f paf
WHERE paf.person_id BETWEEN p_start_person AND p_end_person
AND paf.assignment_id = paa.assignment_id
AND paa.assignment_id = pai.assignment_id
AND paa.tax_unit_id = pai.action_information3
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 g_start_date and g_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 ppa1.action_type in('P','U')
AND ppa.action_type in('R','Q','B')
AND ppa1.action_status ='C'
AND ppa1.effective_date BETWEEN g_start_date and g_end_date
AND ROWNUM =1 ))
)
AND (
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Salary Under Section 17',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Salary Under Section 17','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Profit in lieu of Salary',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Profit in lieu of Salary','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Value of Perquisites',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Value of Perquisites','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Gross Salary less Allowances',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Gross Salary less Allowances','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Allowances Exempt',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Allowances Exempt','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Deductions under Sec 16',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Deductions under Sec 16','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Income Chargeable Under head Salaries',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Income Chargeable Under head Salaries','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Other Income',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Other Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Gross Total Income',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Gross Total Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Total Income',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Total Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Tax on Total Income',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Tax on Total Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Marginal Relief',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Marginal Relief','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Total Tax payable',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Total Tax payable','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Relief under Sec 89',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Relief under Sec 89','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Employment Tax',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Employment Tax','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Entertainment Allowance',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Entertainment Allowance','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Surcharge',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Surcharge','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Education Cess',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Education Cess','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 TDS',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 TDS','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_SALARY','F16 Total Chapter VI A Deductions',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Total Chapter VI A Deductions','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24Q_VIA','80CCE',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Deductions Sec 80CCE','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(pai.action_information6 <> hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title))
OR -- Checking PAN reference Number
(NVL(pai.action_information5,'0') <> NVL(pep.per_information14,'0'))
OR ( --Checking PAN Number
(
g_correction_mode IN ('C5','%')
AND (pai.action_information4 NOT IN('APPLIEDFOR','PANNOTAVBL'))
AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))NOT IN('APPLIEDFOR','PANNOTAVBL'))
AND (pai.action_information4 <> (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
)
OR
(
g_correction_mode NOT IN ('C5')
AND (pai.action_information4 <> (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))IN('APPLIEDFOR','PANNOTAVBL'))
)
)
)
AND assignment_end_date(asg.assignment_id) BETWEEN asg.effective_start_date AND asg.effective_end_date
AND assignment_end_date(asg.assignment_id) BETWEEN pep.effective_start_date AND pep.effective_end_date
ORDER BY 2,4 DESC;
SELECT pai_locking.action_context_id last_action_context_id
FROM pay_action_information pai_locking
,pay_action_interlocks locks
,pay_assignment_actions paa
WHERE pai_locking.action_information_category = 'IN_24QC_PERSON'
AND fnd_number.canonical_to_number(pai_locking.action_information1)
BETWEEN p_start_person AND p_end_person
AND pai_locking.action_information3 = g_gre_id
AND pai_locking.action_context_id = paa.assignment_action_id
AND paa.payroll_action_id IN
(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
)
AND locks.locking_action_id = pai_locking.action_context_id
AND locks.locked_action_id = p_24q_arc_action_context_id
AND pai_locking.assignment_id = p_assignment_id
AND pai_locking.source_id = p_source_id
ORDER BY pai_locking.action_context_id DESC;
SELECT pai.action_context_id
FROM pay_action_information pai
,per_people_f pep
,per_assignments_f asg
,pay_assignment_actions paa
WHERE pai.action_information_category = 'IN_24QC_PERSON'
AND fnd_number.canonical_to_number(pai.action_information1)
BETWEEN p_start_person AND p_end_person
AND paa.assignment_action_id = pai.action_context_id
AND pai.source_id = p_source_id
AND paa.payroll_action_id IN
(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
)
AND pai.action_information3 = g_gre_id
AND pai.action_context_id = p_assignment_action_id
AND asg.assignment_id = pai.assignment_id
AND asg.person_id = pep.person_id
AND asg.business_group_id = pep.business_group_id
AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND pai.source_id IN
(
SELECT
paa.assignment_action_id run_asg_action_id
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_assignments_f paf
WHERE paf.person_id BETWEEN p_start_person AND p_end_person
AND paf.assignment_id = paa.assignment_id
AND paa.assignment_id = pai.assignment_id
AND paa.tax_unit_id = pai.action_information3
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 g_start_date and g_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 ppa1.action_type in('P','U')
AND ppa.action_type in('R','Q','B')
AND ppa1.action_status ='C'
AND ppa1.effective_date BETWEEN g_start_date and g_end_date
AND ROWNUM =1 ))
)
AND (
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Salary Under Section 17',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Salary Under Section 17','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Profit in lieu of Salary',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Profit in lieu of Salary','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Value of Perquisites',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Value of Perquisites','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Gross Salary less Allowances',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Gross Salary less Allowances','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Allowances Exempt',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Allowances Exempt','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Deductions under Sec 16',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Deductions under Sec 16','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Income Chargeable Under head Salaries',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Income Chargeable Under head Salaries','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Other Income',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Other Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Gross Total Income',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Gross Total Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Total Income',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Total Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Tax on Total Income',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Tax on Total Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Marginal Relief',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Marginal Relief','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Total Tax payable',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Total Tax payable','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Relief under Sec 89',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Relief under Sec 89','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Employment Tax',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Employment Tax','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Entertainment Allowance',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Entertainment Allowance','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Surcharge',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Surcharge','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Education Cess',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Education Cess','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 TDS',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 TDS','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Total Chapter VI A Deductions',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Total Chapter VI A Deductions','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_VIA','80CCE',pai.action_context_id,pai.source_id,1)))
<> FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Deductions Sec 80CCE','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
OR
(pai.action_information6 <> hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title))
OR
(NVL(pai.action_information5,'0') <> NVL(pep.per_information14,'0'))
OR(
(
g_correction_mode IN ('C5','%')
AND (pai.action_information4 NOT IN('APPLIEDFOR','PANNOTAVBL'))
AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))NOT IN('APPLIEDFOR','PANNOTAVBL'))
AND (pai.action_information4 <> (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
)
OR
(
g_correction_mode NOT IN ('C5')
AND (pai.action_information4 <> (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))IN('APPLIEDFOR','PANNOTAVBL'))
)
)
)
AND assignment_end_date(asg.assignment_id) BETWEEN asg.effective_start_date AND asg.effective_end_date
AND assignment_end_date(asg.assignment_id) BETWEEN pep.effective_start_date AND pep.effective_end_date
AND NOT EXISTS
(
SELECT 1
FROM pay_action_information pai
,per_people_f pep
,per_assignments_f asg
WHERE pai.action_information_category = 'IN_24QC_PERSON'
AND fnd_number.canonical_to_number(pai.action_information1)
BETWEEN p_start_person AND p_end_person
AND pai.source_id = p_source_id
AND pai.action_information3 = g_gre_id
AND pai.action_context_id = p_assignment_action_id
AND asg.assignment_id = pai.assignment_id
AND asg.person_id = pep.person_id
AND asg.business_group_id = pep.business_group_id
AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND (
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Salary Under Section 17',pai.action_context_id,pai.source_id,1)))
= FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Salary Under Section 17','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
AND
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Profit in lieu of Salary',pai.action_context_id,pai.source_id,1)))
= FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Profit in lieu of Salary','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
AND
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Value of Perquisites',pai.action_context_id,pai.source_id,1)))
= FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Value of Perquisites','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
AND
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Gross Salary less Allowances',pai.action_context_id,pai.source_id,1)))
= FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Gross Salary less Allowances','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
AND
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Allowances Exempt',pai.action_context_id,pai.source_id,1)))
= FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Allowances Exempt','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
AND
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Deductions under Sec 16',pai.action_context_id,pai.source_id,1)))
= FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Deductions under Sec 16','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
AND
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Income Chargeable Under head Salaries',pai.action_context_id,pai.source_id,1)))
= FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Income Chargeable Under head Salaries','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
AND
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Other Income',pai.action_context_id,pai.source_id,1)))
= FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Other Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
AND
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Gross Total Income',pai.action_context_id,pai.source_id,1)))
= FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Gross Total Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
AND
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Total Income',pai.action_context_id,pai.source_id,1)))
= FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Total Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
AND
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Tax on Total Income',pai.action_context_id,pai.source_id,1)))
= FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Tax on Total Income','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
AND
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Marginal Relief',pai.action_context_id,pai.source_id,1)))
= FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Marginal Relief','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
AND
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Total Tax payable',pai.action_context_id,pai.source_id,1)))
= FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Total Tax payable','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
AND
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Relief under Sec 89',pai.action_context_id,pai.source_id,1)))
= FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Relief under Sec 89','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
AND
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Employment Tax',pai.action_context_id,pai.source_id,1)))
= FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Employment Tax','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
AND
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Entertainment Allowance',pai.action_context_id,pai.source_id,1)))
= FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Entertainment Allowance','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
AND
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Surcharge',pai.action_context_id,pai.source_id,1)))
= FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Surcharge','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
AND
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Education Cess',pai.action_context_id,pai.source_id,1)))
= FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Education Cess','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
AND
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 TDS',pai.action_context_id,pai.source_id,1)))
= FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 TDS','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
AND
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_SALARY','F16 Total Chapter VI A Deductions',pai.action_context_id,pai.source_id,1)))
= FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Total Chapter VI A Deductions','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
AND
(FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values('IN_24QC_VIA','80CCE',pai.action_context_id,pai.source_id,1)))
= FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(pay_in_tax_utils.get_balance_value(pai.source_id,'F16 Deductions Sec 80CCE','_ASG_LE_PTD','TAX_UNIT_ID',g_gre_id))))
AND
(pai.action_information6 = hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title))
AND(--Checking PAN Number
(
g_correction_mode IN ('C5','%')
AND (pai.action_information4 NOT IN('APPLIEDFOR','PANNOTAVBL'))
AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))NOT IN('APPLIEDFOR','PANNOTAVBL'))
AND (pai.action_information4 = (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
)
OR
(
g_correction_mode NOT IN ('C5')
AND (pai.action_information4 = (DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4)))
AND ((DECODE(pep.per_information4,NULL,DECODE(pep.per_information5,'Yes','APPLIEDFOR','PANNOTAVBL'),pep.per_information4))IN('APPLIEDFOR','PANNOTAVBL'))
)
)
AND -- Checking PAN reference Number
(NVL(pai.action_information5,'0') = NVL(pep.per_information14,'0'))
)
AND assignment_end_date(asg.assignment_id) BETWEEN asg.effective_start_date AND asg.effective_end_date
AND assignment_end_date(asg.assignment_id) BETWEEN pep.effective_start_date AND pep.effective_end_date
);
SELECT MAX(pai.action_context_id) last_action_context_id
,pai.source_id source_id
,pai.assignment_id assignment_id
FROM pay_action_information pai
,pay_assignment_actions paa
WHERE pai.action_information_category = 'IN_24QC_PERSON'
AND fnd_number.canonical_to_number(pai.action_information1)
BETWEEN p_start_person AND p_end_person
AND pai.action_information3 = g_gre_id
AND pai.action_information2 = g_year||g_quarter
AND
( ((p_flag = 1) AND (pai.action_information10 IN ('A','NA')))
OR ((p_flag = 2) AND (pai.action_information10 = p_mode))
)
AND pai.action_context_id = paa.assignment_action_id
AND paa.payroll_action_id IN
(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
)
AND (
p_flag = 1
OR
(
pai.source_id NOT IN(
SELECT pai.source_id
FROM pay_action_information pai
,pay_assignment_actions paa
WHERE pai.action_information_category = 'IN_24QC_PERSON'
AND fnd_number.canonical_to_number(pai.action_information1)
BETWEEN p_start_person AND p_end_person
AND pai.action_information3 = g_gre_id
AND pai.action_information2 = g_year||g_quarter
AND pai.action_information10 = 'D'
AND pai.action_context_id = paa.assignment_action_id
AND paa.payroll_action_id IN
(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
)
)
AND pai.source_id IN
(SELECT a.source_id
FROM
(SELECT
FND_NUMBER.CANONICAL_TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) source_id
,paf.assignment_id assignment_id
,NULL last_action_context_id
FROM pay_assignment_actions paa
,pay_payroll_actions ppa
,per_assignments_f paf
WHERE paf.person_id BETWEEN p_start_person AND p_end_person
AND paf.assignment_id = paa.assignment_id
AND paa.tax_unit_id = g_gre_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 g_start_date and g_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 ppa1.action_type in('P','U')
AND ppa.action_type in('R','Q','B')
AND ppa1.action_status ='C'
AND ppa1.effective_date BETWEEN g_start_date and g_end_date
AND ROWNUM =1 ))
GROUP BY paf.assignment_id) a
WHERE
a.assignment_id = pai.assignment_id)
)
)
GROUP BY pai.assignment_id,pai.source_id;
SELECT 1
FROM per_assignments_f
WHERE business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND assignment_id = p_assignment_id;
pay_in_utils.set_location(g_debug,'Finding Assignment Actions deleted since last statement ',2);
FOR c_rec IN c_delete_mode_entries(p_payroll_action_id)
LOOP
l_bg_check := 0;
g_sal_data_rec_del(g_count_sal_delete).source_id := c_rec.source_id;
g_sal_data_rec_del(g_count_sal_delete).last_action_context_id := c_rec.last_action_context_id;
g_sal_data_rec_del(g_count_sal_delete).assignment_id := c_rec.assignment_id;
g_sal_data_rec_del(g_count_sal_delete).salary_mode := 'D';
g_count_sal_delete := g_count_sal_delete + 1;
pay_in_utils.set_location(g_debug,'Finding Assignment Actions updated since last statement ',4);
FOR csr_rec IN c_update_mode_entries(p_payroll_action_id)
LOOP
pay_in_utils.set_location(g_debug,'Diff between live data and 24Q Archival found', 1);
pay_in_utils.set_location(g_debug,'Mid way in finding updated assignment actions', 3);
g_sal_data_rec_upd(g_count_sal_update).last_action_context_id := csr_rec.last_action_context_id;
g_sal_data_rec_upd(g_count_sal_update).assignment_id := csr_rec.assignment_id;
g_sal_data_rec_upd(g_count_sal_update).source_id := csr_rec.source_id;
g_sal_data_rec_upd(g_count_sal_update).salary_mode := 'U';
g_count_sal_update := g_count_sal_update + 1;
g_sal_data_rec_upd(g_count_sal_update).last_action_context_id := l_dummy;
g_sal_data_rec_upd(g_count_sal_update).assignment_id := csr_rec.assignment_id;
g_sal_data_rec_upd(g_count_sal_update).source_id := csr_rec.source_id;
g_sal_data_rec_upd(g_count_sal_update).salary_mode := 'U';
g_count_sal_update := g_count_sal_update + 1;
pay_in_utils.set_location(g_debug,'Additional Checking on updated assignment actions ', 7);
FOR i IN 1.. g_count_sal_update - 1
LOOP
IF (
(g_sal_data_rec_upd(i).last_action_context_id = c_rec.last_action_context_id)
AND
(g_sal_data_rec_upd(i).assignment_id = c_rec.assignment_id)
AND
(g_sal_data_rec_upd(i).source_id = c_rec.source_id)
AND
(g_sal_data_rec_upd(i).salary_mode = 'U')
)
THEN
l_flag := FALSE;
g_sal_data_rec_upd(g_count_sal_update).last_action_context_id := c_rec.last_action_context_id;
g_sal_data_rec_upd(g_count_sal_update).assignment_id := c_rec.assignment_id;
g_sal_data_rec_upd(g_count_sal_update).source_id := c_rec.source_id;
g_sal_data_rec_upd(g_count_sal_update).salary_mode := 'U';
g_count_sal_update := g_count_sal_update + 1;
pay_in_utils.set_location(g_debug,'Now Searching for those updated assignment actions that were 24QC+',8);
g_sal_data_rec_upd(g_count_sal_update).last_action_context_id := c_rec.last_action_context_id;
g_sal_data_rec_upd(g_count_sal_update).assignment_id := c_rec.assignment_id;
g_sal_data_rec_upd(g_count_sal_update).source_id := c_rec.source_id;
g_sal_data_rec_upd(g_count_sal_update).salary_mode := 'U';
g_count_sal_update := g_count_sal_update + 1;
SELECT hoi.org_information1 tan
,hoi.org_information3 emplr_type
,hoi.org_information6 emplr_type_24Q
,hoi.org_information4 reg_org_id
,hoi.org_information7 division
,hou.location_id location_id
,hoi.org_information9 state
,hoi.org_information10 pao_code
,hoi.org_information11 ddo_code
,hoi.org_information12 ministry_name
,hoi.org_information13 other_ministry_name
,hoi.org_information14 pao_reg_code
,hoi.org_information15 ddo_reg_code
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 = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND g_qr_end_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 = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND g_qr_end_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 = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND g_qr_end_date BETWEEN pos.date_effective(+) AND NVL(pos.date_end(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
AND g_qr_end_date BETWEEN job.date_from(+) AND NVL(job.date_to(+),TO_DATE('31-12-4712','DD-MM-YYYY'))
AND g_qr_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date;
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 = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND hou.organization_id = asg.organization_id
AND hou.business_group_id = asg.business_group_id
AND g_qr_end_date BETWEEN asg.effective_start_date AND asg.effective_end_date
AND g_qr_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
SELECT pep.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 = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND pep.person_id = hoi.org_information1
AND pep.business_group_id = hou.business_group_id
AND g_qr_end_date BETWEEN pep.effective_start_date AND pep.effective_end_date
AND g_qr_end_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 g_qr_end_date BETWEEN hou.date_from AND NVL(hou.date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
SELECT phone_number rep_phone_no
,SUBSTR(phone_number
,INSTR(phone_number,'-',1,1) + 1
,INSTR(phone_number,'-',1,2) - INSTR(phone_number,'-',1,1) -1
)STD_CODE
FROM per_phones
WHERE parent_id = p_person_id
AND phone_type = 'W1'
AND g_qr_end_date BETWEEN date_from AND NVL(date_to,TO_DATE('31-12-4712','DD-MM-YYYY'));
SELECT DISTINCT pai.action_context_id locking_id
,pai.action_information1 gre_id
FROM pay_action_information pai
WHERE pai.action_context_type = 'PA'
AND pai.action_information_category = 'IN_24QC_ORG'
AND pai.action_information1 = p_gre_id
AND pai.action_information3 = g_year||g_quarter
AND pai.source_id = p_payroll_action_id
AND pai.action_context_id IN
(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
)
ORDER BY pai.action_context_id DESC;
SELECT action_information1 gre_id
FROM pay_action_information
WHERE action_information_category = 'IN_24Q_ORG'
AND action_context_type = 'PA'
AND action_information1 = p_gre_id
AND action_information3 = g_year||g_quarter
AND action_context_id = p_payroll_action_id;
SELECT pai.action_context_id last_action_context_id
FROM pay_action_information pai
WHERE pai.action_information_category = p_act_inf_category
AND pai.action_context_type = 'PA'
AND pai.action_context_id = p_action_context_id
AND pai.action_information1 = p_gre_id
AND pai.action_information2 = p_tan_number
AND pai.action_information3 = g_year||g_quarter
AND(
(pai.action_information4 <> p_pan_number)
OR
(pai.action_information5 <> p_legal_name)
OR
(pai.action_information6 <> p_org_location)
OR
(pai.action_information7 <> p_deductor_type)
OR
(pai.action_information8 <> p_branch_or_division)
OR
(pai.action_information9 <> p_rep_name)
OR
(pai.action_information10 <> p_rep_email_id)
OR
(pai.action_information11 <> p_rep_position)
OR
(pai.action_information12 <> p_rep_location)
OR
(pai.action_information13 <> p_rep_work_phone)
OR
(SUBSTR(pai.action_information13
,INSTR(pai.action_information13,'-',1,1) + 1
,INSTR(pai.action_information13,'-',1,2) - INSTR(pai.action_information13,'-',1,1) -1
) <> p_rep_std_code
)
OR
(DECODE(p_act_inf_category,'IN_24Q_ORG',p_rep_std_code,pai.action_information14) <> p_rep_std_code)
);
SELECT pai.action_context_id last_action_context_id
FROM pay_action_information pai
WHERE pai.action_information_category = p_act_inf_category
AND pai.action_context_type = 'PA'
AND pai.action_context_id = p_action_context_id
AND pai.action_information1 = p_gre_id
AND pai.action_information2 = p_tan_number
AND pai.action_information3 = g_year||g_quarter
AND(
(pai.action_information4 <> p_pan_number)
OR
(pai.action_information5 <> p_legal_name)
OR
(pai.action_information6 <> p_org_location)
OR
(DECODE(p_act_inf_category,'IN_24Q_ORG',pai.action_information21,pai.action_information7) <> p_deductor_type)
OR
(pai.action_information8 <> p_branch_or_division)
OR
(pai.action_information9 <> p_rep_name)
OR
(pai.action_information10 <> p_rep_email_id)
OR
(pai.action_information11 <> p_rep_position)
OR
(pai.action_information12 <> p_rep_location)
OR
(pai.action_information13 <> p_rep_work_phone)
OR
(DECODE(p_act_inf_category,'IN_24Q_ORG',pai.action_information14,pai.action_information20) <> p_state)
OR
(DECODE(p_act_inf_category,'IN_24Q_ORG',pai.action_information15,pai.action_information21) <> p_pao_code)
OR
(DECODE(p_act_inf_category,'IN_24Q_ORG',pai.action_information16,pai.action_information22) <> p_ddo_code)
OR
(DECODE(p_act_inf_category,'IN_24Q_ORG',pai.action_information17,pai.action_information23) <> p_ministry_name)
OR
(DECODE(p_act_inf_category,'IN_24Q_ORG',pai.action_information18,pai.action_information24) <> p_other_ministry_name)
OR
(DECODE(p_act_inf_category,'IN_24Q_ORG',pai.action_information19,pai.action_information27) <> p_pao_reg_code)
OR
(DECODE(p_act_inf_category,'IN_24Q_ORG',pai.action_information20,pai.action_information28) <> p_ddo_reg_code)
OR
(SUBSTR(pai.action_information13
,INSTR(pai.action_information13,'-',1,1) + 1
,INSTR(pai.action_information13,'-',1,2) - INSTR(pai.action_information13,'-',1,1) -1
) <> p_rep_std_code
)
OR
(DECODE(p_act_inf_category,'IN_24Q_ORG',p_rep_std_code,pai.action_information14) <> p_rep_std_code)
);
SELECT hoi.org_information3 challan_number,
hoi.org_information2 transfer_voucher_date,
hoi.org_information4 amount,
hoi.org_information7 surcharge,
hoi.org_information8 education_cess,
hoi.org_information10 other,
hoi.org_information9 interest,
(SELECT hoi_bank.org_information4
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 = hoi.org_information5
) bank_branch_code,
hoi.org_information11 cheque_dd_num,
hoi.org_information_id org_information_id
FROM hr_organization_information hoi
WHERE hoi.org_information_id IN
(
SELECT hoi.org_information_id
FROM hr_organization_information hoi
,hr_organization_units hou
WHERE hoi.organization_id = p_gre_id
AND hoi.organization_id = hou.organization_id
AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND org_information_context = 'PER_IN_IT_CHALLAN_INFO'
)
AND hoi.org_information13 = g_quarter
AND hoi.org_information1 = p_tax_year
AND(
hoi.org_information3 LIKE p_challan_number
OR
hoi.org_information_id = p_org_info_id
);
SELECT hoi.org_information3 challan_number,
hoi.org_information2 transfer_voucher_date,
hoi.org_information4 amount,
hoi.org_information7 surcharge,
hoi.org_information8 education_cess,
hoi.org_information10 other,
hoi.org_information9 interest,
(SELECT hoi_bank.org_information4
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 = hoi.org_information5
) bank_branch_code,
hoi.org_information11 cheque_dd_num,
hoi.org_information_id org_information_id
FROM hr_organization_information hoi
WHERE hoi.org_information_id IN
(
SELECT hoi.org_information_id
FROM hr_organization_information hoi
,hr_organization_units hou
WHERE hoi.organization_id = p_gre_id
AND hoi.organization_id = hou.organization_id
AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND org_information_context = 'PER_IN_IT_CHALLAN_INFO'
)
AND hoi.org_information13 = g_quarter
AND hoi.org_information1 = p_tax_year
AND(
hoi.org_information3 LIKE SUBSTR(p_challan_number ,INSTR (p_challan_number, ' -',1,1)+3,(INSTR (p_challan_number, ' -',1,2)-(INSTR (p_challan_number, ' -',1,1)+3)))
OR
hoi.org_information_id = p_org_info_id
);
SELECT action_information_id,
action_information1 transfer_voucher_number,
action_information4 bank_branch_code,
action_information5 transfer_voucher_date,
action_information6 amount,
action_information7 surcharge,
action_information8 cess,
action_information9 interest,
action_information10 other,
action_information11 cheque_dd_num,
source_id org_information_id
FROM pay_action_information
WHERE action_information2 = g_year||g_quarter
AND action_information3 = p_gre_id
AND action_information_category = p_act_info_category
AND action_context_id IN
(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
)
AND DECODE(p_act_info_category
,'IN_24Q_CHALLAN'
,action_context_id
,p_payroll_action_id
) = p_payroll_action_id
AND (
action_information1 LIKE p_voucher_number
OR
source_id = p_org_information_id
)
AND DECODE(p_act_info_category
,'IN_24QC_CHALLAN'
,action_information15
,p_payroll_action_id
) = p_payroll_action_id
ORDER BY action_information_id DESC;
SELECT action_information_id,
action_information1 transfer_voucher_number,
action_information4 bank_branch_code,
action_information5 transfer_voucher_date,
action_information6 amount,
action_information7 surcharge,
action_information8 cess,
action_information9 interest,
action_information10 other,
action_information11 cheque_dd_num,
source_id org_information_id
FROM pay_action_information
WHERE action_information2 = g_year||g_quarter
AND action_information3 = p_gre_id
AND action_information_category = p_act_info_category
AND action_context_id IN
(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
)
AND DECODE(p_act_info_category
,'IN_24Q_CHALLAN'
,action_context_id
,p_payroll_action_id
) = p_payroll_action_id
AND (
action_information1 LIKE p_voucher_number
OR
source_id = p_org_information_id
)
AND DECODE(p_act_info_category
,'IN_24QC_CHALLAN'
,action_information15
,p_payroll_action_id
) = p_payroll_action_id
ORDER BY action_information_id DESC;
pay_in_utils.set_location(g_debug,'Starting Search for Updated Challans',1);
SELECT pay_in_utils.get_ee_value(p_element_entry_id,'Challan or Voucher Number') Challan_or_Voucher_Number,
pay_in_utils.get_ee_value(p_element_entry_id,'Payment Date') Payment_Date,
pay_in_utils.get_ee_value(p_element_entry_id,'Taxable Income') Taxable_Income,
pay_in_utils.get_ee_value(p_element_entry_id,'Income Tax Deducted') Income_Tax_Deducted,
pay_in_utils.get_ee_value(p_element_entry_id,'Surcharge Deducted') Surcharge_Deducted,
pay_in_utils.get_ee_value(p_element_entry_id,'Education Cess Deducted') Education_Cess_Deducted,
pay_in_utils.get_ee_value(p_element_entry_id,'Amount Deposited') Amount_Deposited
FROM dual;
SELECT action_information1 Challan_or_Voucher_Number,
action_information4 Payment_Date,
action_information5 Taxable_Income,
action_information6 Income_Tax_Deducted,
action_information7 Surcharge_Deducted,
action_information8 Education_Cess_Deducted,
DECODE(action_information_category,'IN_24Q_DEDUCTEE'
,action_information9,'IN_24QC_DEDUCTEE'
,action_information16) Amount_Deposited
,action_information_id
FROM pay_action_information pai
WHERE pai.action_context_id = p_action_context_id
AND pai.action_information_category IN ('IN_24Q_DEDUCTEE','IN_24QC_DEDUCTEE')
AND pai.assignment_id = p_assignment_id
AND pai.source_id = p_element_entry_id
AND pai.action_information3 = g_gre_id
ORDER BY action_information_id DESC;
SELECT action_information1 person_id
,action_information4 pan_number
,action_information5 pan_ref_number
,action_information6 full_name
,action_information_id
FROM pay_action_information
WHERE action_context_id = p_action_context_id
AND action_context_type = 'AAP'
AND action_information_category = 'IN_24Q_PERSON'
AND assignment_id = p_assignment_id
AND action_information2 = g_year||g_quarter
AND action_information3 = g_gre_id
ORDER BY action_information_id DESC;
SELECT action_information13 tax_rate
FROM pay_action_information
WHERE action_context_id = p_action_context_id
AND action_context_type = 'AAP'
AND action_information_category = 'IN_24Q_DEDUCTEE'
AND assignment_id = p_assignment_id
AND source_id = p_element_entry_id
AND action_information3 = g_gre_id;
SELECT action_information12 person_id
,action_information9 pan_number
,action_information11 pan_ref_number
,action_information18 tax_rate
,action_information10 full_name
,action_information_id
FROM pay_action_information
WHERE action_context_id = p_action_context_id
AND action_context_type = 'AAP'
AND action_information_category = 'IN_24QC_DEDUCTEE'
AND assignment_id = p_assignment_id
AND action_information2 = g_year||g_quarter
AND action_information3 = g_gre_id
ORDER BY action_information_id DESC;
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_number
,hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title) full_name
,pep.effective_end_date
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 = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND assignment_end_date(asg.assignment_id) BETWEEN asg.effective_start_date AND asg.effective_end_date
AND assignment_end_date(asg.assignment_id) BETWEEN pep.effective_start_date AND pep.effective_end_date
ORDER BY 5 DESC;
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 assignment_end_date(paa.assignment_id) BETWEEN paa.effective_start_date AND paa.effective_end_date
AND ROWNUM = 1;
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 Income'
,'F16 Tax on Total Income'
,'F16 Marginal Relief'
,'F16 Total Tax payable'
,'F16 Relief under Sec 89'
,'F16 Employment Tax'
,'F16 Entertainment Allowance'
,'F16 Surcharge'
,'F16 Education Cess'
,'F16 Sec and HE Cess'
,'F16 TDS'
--Chapter VIA Balances
,'F16 Total Chapter VI A Deductions'
,'F16 Deductions Sec 80CCE'
)
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;
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_tab.DELETE;
g_result_table.DELETE;
SELECT DISTINCT
FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values(p_category,p_balance_name,pai.action_context_id,pai.source_id,1)))
FROM pay_action_information pai
WHERE pai.action_context_id = p_action_context_id
AND pai.action_information_category = p_category
AND pai.source_id = p_24q_source_id;
SELECT DISTINCT
pai.action_information1 person_id
,pai.action_information4 pan_number
,pai.action_information5 pan_ref_number
,pai.action_information6 full_name
,fnd_date.canonical_to_date(pai.action_information9) start_date
,fnd_date.canonical_to_date(pai.action_information10) end_date
,pai.source_id
FROM pay_action_information pai
,pay_assignment_actions paa
WHERE pai.action_information_category IN('IN_24Q_PERSON')
AND pai.action_information3 = g_gre_id
AND pai.action_information2 = g_year||g_quarter
AND pai.assignment_id = p_assignment_id
AND (
(pai.source_id = p_source_id AND p_mode NOT IN ('A'))
OR (p_mode IN ('A'))
)
AND pai.action_context_id = paa.assignment_action_id
AND paa.payroll_action_id = p_24qa_pay_act_id
ORDER BY pai.source_id DESC;
SELECT DISTINCT
pai.action_information1 person_id
,pai.action_information4 pan_number
,pai.action_information5 pan_ref_number
,pai.action_information6 full_name
,fnd_date.canonical_to_date(pai.action_information8) start_date
,fnd_date.canonical_to_date(pai.action_information9) end_date
,pai.source_id
FROM pay_action_information pai
,pay_assignment_actions paa
,pay_action_interlocks locks
,pay_assignment_actions paa_master
WHERE pai.action_information_category = 'IN_24QC_PERSON'
AND pai.assignment_id = p_assignment_id
AND pai.action_information3 = g_gre_id
AND pai.action_information2 = g_year||g_quarter
AND pai.action_information10 = 'NA'
AND (
(pai.source_id = p_source_id AND p_mode NOT IN ('A'))
OR (p_mode IN ('A'))
)
AND locks.locking_action_id = pai.action_context_id
AND locks.locked_action_id = paa.assignment_action_id
AND paa.payroll_action_id = p_24qa_pay_act_id
AND paa_master.assignment_action_id = pai.action_context_id
AND paa_master.payroll_action_id IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
)
ORDER BY pai.source_id DESC;
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_number
,hr_in_utility.per_in_full_name(pep.first_name,pep.middle_names,pep.last_name,pep.title) full_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 = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND asg.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND assignment_end_date(asg.assignment_id) BETWEEN asg.effective_start_date AND asg.effective_end_date
AND assignment_end_date(asg.assignment_id) BETWEEN pep.effective_start_date AND pep.effective_end_date;
SELECT pai.action_context_id, pai.source_id
FROM pay_action_information pai
,pay_assignment_actions paa
,pay_action_interlocks locks
,pay_assignment_actions paa_master
WHERE pai.action_information_category = 'IN_24QC_PERSON'
AND pai.assignment_id = p_assignment_id
AND pai.action_information3 = g_gre_id
AND pai.action_information2 = g_year||g_quarter
AND pai.action_information10 = 'A'
AND (
(pai.source_id = p_source_id AND p_mode NOT IN ('A'))
OR (p_mode IN ('A'))
)
AND locks.locking_action_id = pai.action_context_id
AND locks.locked_action_id = paa.assignment_action_id
AND paa.payroll_action_id = p_24qa_pay_act_id
AND paa_master.assignment_action_id = pai.action_context_id
AND paa_master.payroll_action_id IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
)
ORDER BY pai.source_id DESC;
SELECT DISTINCT pai.action_context_id, pai.source_id
FROM pay_action_information pai
,pay_assignment_actions paa
WHERE pai.action_information_category IN('IN_24Q_PERSON')
AND pai.action_information3 = g_gre_id
AND pai.action_information2 = g_year||g_quarter
AND pai.assignment_id = p_assignment_id
AND (
(pai.source_id = p_source_id AND p_mode NOT IN ('A'))
OR (p_mode IN ('A'))
)
AND pai.action_context_id = paa.assignment_action_id
AND paa.payroll_action_id = p_24qa_pay_act_id
ORDER BY pai.source_id DESC;
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_action_context_id
AND asg.assignment_id = paa.assignment_id
AND paa.assignment_id = p_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 = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
ORDER BY 1 ;
SELECT FND_NUMBER.CANONICAL_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 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_action_context_id
AND paa.assignment_id = paf.assignment_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 = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_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;
SELECT MAX(FND_NUMBER.CANONICAL_TO_NUMBER(action_information11))
FROM pay_action_information pai
,pay_assignment_actions paa
WHERE pai.action_information_category = 'IN_24Q_PERSON'
AND pai.action_information3 = g_gre_id
AND pai.action_information2 = g_year||g_quarter
AND pai.action_information1 = p_person_id
AND pai.source_id = p_source_id
AND pai.action_context_id = paa.assignment_action_id
AND paa.payroll_action_id = p_24qa_pay_act_id
AND paa.payroll_action_id IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'O'
);
SELECT MAX(FND_NUMBER.CANONICAL_TO_NUMBER(action_information12))
FROM pay_action_information pai
,pay_assignment_actions paa
,pay_action_interlocks locks
,pay_assignment_actions paa_master
WHERE pai.action_information_category = 'IN_24QC_PERSON'
AND pai.action_information3 = g_gre_id
AND pai.action_information2 = g_year||g_quarter
AND pai.action_information1 = p_person_id
AND pai.source_id = p_source_id
AND locks.locking_action_id = pai.action_context_id
AND locks.locked_action_id = paa.assignment_action_id
AND paa.payroll_action_id = p_24qa_pay_act_id
AND paa_master.assignment_action_id = pai.action_context_id
AND paa_master.payroll_action_id IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
);
SELECT MAX(FND_NUMBER.CANONICAL_TO_NUMBER(action_information11))
FROM pay_action_information pai
,pay_assignment_actions paa
WHERE pai.action_information_category = 'IN_24Q_PERSON'
AND pai.action_information3 = g_gre_id
AND pai.action_information2 = g_year||g_quarter
AND pai.action_context_id = paa.assignment_action_id
AND paa.payroll_action_id = p_24qa_pay_act_id
AND paa.payroll_action_id IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'O'
);
SELECT MAX(FND_NUMBER.CANONICAL_TO_NUMBER(action_information12))
FROM pay_action_information pai
,pay_assignment_actions paa
,pay_action_interlocks locks
,pay_assignment_actions paa_master
WHERE pai.action_information_category = 'IN_24QC_PERSON'
AND pai.action_information3 = g_gre_id
AND pai.action_information2 = g_year||g_quarter
AND locks.locking_action_id = pai.action_context_id
AND locks.locked_action_id = paa.assignment_action_id
AND paa.payroll_action_id = p_24qa_pay_act_id
AND paa_master.assignment_action_id = pai.action_context_id
AND paa_master.payroll_action_id IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
);
SELECT assignment_action_id
FROM pay_assignment_actions
WHERE payroll_action_id = p_payroll_action_id
AND assignment_id = p_assignment_id;
SELECT
FND_NUMBER.CANONICAL_TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) source_id
,paf.assignment_id assignment_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 = g_gre_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 g_start_date and g_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 ppa1.action_type in('P','U')
AND ppa.action_type in('R','Q','B')
AND ppa1.action_status ='C'
AND ppa1.effective_date between g_start_date and g_end_date
AND ROWNUM =1 ))
GROUP BY paf.assignment_id;
SELECT
FND_NUMBER.CANONICAL_TO_NUMBER(SUBSTR(MAX(LPAD(paa.action_sequence,15,'0')||paa.assignment_action_id),16)) source_id
,paf.assignment_id assignment_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 = g_gre_id
AND paa.payroll_action_id = ppa.payroll_action_id
AND paa.assignment_action_id < p_max_asact_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 g_start_date and g_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 ppa1.action_type in('P','U')
AND ppa.action_type in('R','Q','B')
AND ppa1.action_status ='C'
AND ppa1.effective_date between g_start_date and g_end_date
AND ROWNUM =1 ))
GROUP BY paf.assignment_id;
SELECT DISTINCT pai.source_id,pai.action_context_id
FROM pay_action_information pai
,pay_assignment_actions paa
,pay_action_interlocks locks
,pay_assignment_actions paa_master
WHERE pai.action_information_category = 'IN_24QC_PERSON'
AND pai.assignment_id = p_assignment_id
AND pai.action_information3 = g_gre_id
AND pai.action_information2 = g_year||g_quarter
AND pai.action_information10 = 'A'
AND locks.locking_action_id = pai.action_context_id
AND locks.locked_action_id = paa.assignment_action_id
AND paa.payroll_action_id = p_24qa_pay_act_id
AND paa_master.assignment_action_id = pai.action_context_id
AND paa_master.payroll_action_id IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
)
ORDER BY pai.source_id DESC;
SELECT DISTINCT pai.source_id,pai.action_context_id
FROM pay_action_information pai
WHERE pai.action_information_category IN('IN_24Q_PERSON')
AND pai.action_information3 = g_gre_id
AND pai.assignment_id = p_assignment_id
AND pai.action_context_id = p_action_context_id
ORDER BY pai.source_id DESC;
SELECT DISTINCT
FND_NUMBER.CANONICAL_TO_NUMBER(remove_curr_format(get_24Q_values(p_category,p_balance_name,pai.action_context_id,pai.source_id,1)))
FROM pay_action_information pai
WHERE pai.action_context_id = p_action_context_id
AND pai.action_information_category = p_category
AND pai.source_id = p_24q_source_id;
l_count_delete NUMBER;
pay_in_utils.set_location(g_debug,'Value of g_count_sal_delete : '|| g_count_sal_delete, 1);
FOR i IN 1..g_count_sal_delete - 1
LOOP
pay_in_utils.set_location(g_debug,'Checking archived presence of this deleted assignment action',1);
pay_in_utils.trace('l_flag in g_count_sal_delete ',l_flag);
pay_in_utils.trace('l_flag1 in g_count_sal_delete ',l_flag1);
pay_in_utils.set_location(g_debug,'Deleted Salary Detail Record Not Archived. Hence doing '||g_sal_data_rec_del(i).source_id,1);
pay_in_utils.trace('Delete Source ID : ', g_sal_data_rec_del(i).source_id );
pay_in_utils.trace('Delete Source ID : ', g_sal_data_rec_del(i).source_id );
l_count_delete := 0;
FOR p IN 1..g_count_sal_delete - 1
LOOP
IF (g_sal_data_rec_add(i).assignment_id = g_sal_data_rec_del(p).assignment_id)
THEN
l_count_delete := l_count_delete + 1;
IF (l_count_delete = 0) THEN
l_future_pay := TRUE;
pay_in_utils.set_location(g_debug,'Value of g_count_sal_update : '|| g_count_sal_update, 1);
FOR i IN 1..g_count_sal_update - 1
LOOP
pay_in_utils.set_location(g_debug,'Checking archived presence of this updated assignment action',1);
pay_in_utils.trace('l_flag in g_count_sal_update : ', l_flag );
pay_in_utils.trace('l_flag1 in g_count_sal_update : ', l_flag1 );
pay_in_utils.trace('l_flag2 in g_count_sal_update : ', l_flag2 );
pay_in_utils.set_location(g_debug,'Updated Asg Action Not Archived. hence doing '||g_sal_data_rec_upd(i).source_id,1);
pay_in_utils.trace('Update Source_id :',g_sal_data_rec_upd(i).source_id);
pay_in_utils.trace('l_gre_count in g_count_sal_update: ',l_gre_count);
SELECT pai.action_information_category
,pai.action_context_id
FROM pay_action_information pai
,pay_assignment_actions paa
WHERE paa.assignment_action_id = p_last_arc_asg_action_id
AND pai.action_context_id = paa.payroll_action_id
AND pai.action_context_type = 'PA'
ORDER BY pai.action_context_id DESC;
SELECT action_information25
FROM pay_action_information pai
,pay_assignment_actions paa
WHERE pai.action_information_category = 'IN_24Q_DEDUCTEE'
AND pai.action_information3 = g_gre_id
AND pai.action_information1 = p_challan_number
AND pai.action_context_id = paa.assignment_action_id
AND paa.payroll_action_id = p_24qa_pay_act_id
AND pai.source_id = p_element_entry_id;
SELECT action_information25
FROM pay_action_information pai
,pay_assignment_actions paa
,pay_action_interlocks locks
WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
AND pai.action_information3 = g_gre_id
AND pai.action_information2 = g_year||g_quarter
AND pai.action_information1 = p_challan_number
AND locks.locking_action_id = pai.action_context_id
AND locks.locked_action_id = paa.assignment_action_id
AND paa.payroll_action_id = p_24qa_pay_act_id
AND pai.source_id = p_element_entry_id;
SELECT MAX(TO_NUMBER(action_information25))
FROM pay_action_information pai
,pay_assignment_actions paa
,pay_action_interlocks locks
,pay_assignment_actions paa_master
WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
AND pai.action_information3 = g_gre_id
AND pai.action_information2 = g_year||g_quarter
AND pai.action_information1 = p_challan_number
AND locks.locking_action_id = pai.action_context_id
AND locks.locked_action_id = paa.assignment_action_id
AND paa.payroll_action_id = p_24qa_pay_act_id
AND paa_master.assignment_action_id = pai.action_context_id
AND paa_master.payroll_action_id IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
);
SELECT MAX(TO_NUMBER(action_information25))
FROM pay_action_information pai
,pay_assignment_actions paa
WHERE pai.action_information_category = 'IN_24Q_DEDUCTEE'
AND pai.action_information3 = g_gre_id
AND pai.action_information1 = p_challan_number
AND pai.action_context_id = paa.assignment_action_id
AND paa.payroll_action_id = p_24qa_pay_act_id
AND paa.payroll_action_id IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'O'
);
SELECT assignment_action_id
FROM pay_assignment_actions
WHERE payroll_action_id = p_payroll_action_id
AND assignment_id = p_assignment_id;
pay_in_utils.set_location(g_debug,'Value of g_count_ee_delete : '|| g_count_ee_delete, 1);
FOR i IN 1..g_count_ee_delete - 1
LOOP
pay_in_utils.set_location(g_debug,'Checking archived presence of this deleted ee',1);
pay_in_utils.set_location(g_debug,'Deleted EE Not Archived. hence doing '||g_ee_data_rec_del(i).element_entry_id,1);
pay_in_utils.set_location(g_debug,'Value of g_count_ee_update : '|| g_count_ee_update, 1);
FOR i IN 1..g_count_ee_update - 1
LOOP
pay_in_utils.set_location(g_debug,'Checking archived presence of this updated ee',1);
pay_in_utils.set_location(g_debug,'Updated EE Not Archived. hence doing '||g_ee_data_rec_upd(i).element_entry_id,1);
SELECT action_information13
,action_information14
,action_information17
,action_information25
,action_information_id
FROM pay_action_information
WHERE action_context_type = 'PA'
AND action_information_category = 'IN_24QC_CHALLAN'
AND action_information15 = p_24qa_pay_act_id
AND NVL(action_information4,'BOOK')||' - '||action_information1||' - '||to_char(fnd_date.canonical_to_date(action_information5),'DD-Mon-RRRR') = p_transfer_voucher_number
AND action_context_id IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
)
ORDER BY action_information_id DESC;
SELECT action_information4 bank_code
,action_information5 payment_date
, NVL(action_information6,0)
+ NVL(action_information7,0)
+ NVL(action_information8,0)
+ NVL(action_information9,0)
+ NVL(action_information10,0)
,action_information25
FROM pay_action_information pai
WHERE pai.action_context_type = 'PA'
AND pai.action_information_category = 'IN_24Q_CHALLAN'
AND pai.action_context_id = p_24qa_pay_act_id
AND NVL(action_information4,'BOOK')||' - '||action_information1||' - '||to_char(fnd_date.canonical_to_date(action_information5),'DD-Mon-RRRR') = p_transfer_voucher_number
ORDER BY action_information_id DESC;
SELECT hoi.org_information3 challan_number,
hoi.org_information2 transfer_voucher_date,
hoi.org_information4 amount,
hoi.org_information7 surcharge,
hoi.org_information8 education_cess,
hoi.org_information10 other,
hoi.org_information9 interest,
(SELECT hoi_bank.org_information4
FROM hr_organization_information hoi_bank
WHERE hoi_bank.organization_id = g_gre_id
AND hoi_bank.org_information_context = 'PER_IN_CHALLAN_BANK'
AND hoi_bank.org_information_id = hoi.org_information5
) bank_branch_code,
hoi.org_information11 cheque_dd_num,
hoi.org_information12 book_entry,
hoi.org_information_id org_information_id
FROM hr_organization_information hoi
WHERE hoi.org_information_id IN
(
SELECT hoi.org_information_id
FROM hr_organization_information hoi
,hr_organization_units hou
WHERE hoi.organization_id = g_gre_id
AND hoi.organization_id = hou.organization_id
AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND org_information_context = 'PER_IN_IT_CHALLAN_INFO'
)
AND hoi.org_information13 = g_quarter
AND hoi.org_information1 = g_tax_year;
pay_in_utils.set_location(g_debug,'Checking in Updated Challans ', 1);
,p_action_information19 => update_challans(g_challan_data_noc(i).transfer_voucher_number)
,p_action_information25 => l_challan_deductee_no
,p_action_information_id => l_action_info_id
,p_object_version_number => l_ovn
);
SELECT hoi.org_information3 challan_number,
hoi.org_information2 transfer_voucher_date,
hoi.org_information4 amount,
hoi.org_information7 surcharge,
hoi.org_information8 education_cess,
hoi.org_information10 other,
hoi.org_information9 interest,
(SELECT hoi_bank.org_information4
FROM hr_organization_information hoi_bank
WHERE hoi_bank.organization_id = g_gre_id
AND hoi_bank.org_information_context = 'PER_IN_CHALLAN_BANK'
AND hoi_bank.org_information_id = hoi.org_information5
) bank_branch_code,
hoi.org_information11 cheque_dd_num,
hoi.org_information12 book_entry,
hoi.org_information_id org_information_id
FROM hr_organization_information hoi
WHERE hoi.org_information_id IN
(
SELECT hoi.org_information_id
FROM hr_organization_information hoi
,hr_organization_units hou
WHERE hoi.organization_id = g_gre_id
AND hoi.organization_id = hou.organization_id
AND hou.business_group_id = FND_PROFILE.VALUE('PER_BUSINESS_GROUP_ID')
AND org_information_context = 'PER_IN_IT_CHALLAN_INFO'
)
AND hoi.org_information13 = g_quarter
AND hoi.org_information1 = g_tax_year
AND (
(hoi.org_information3 = SUBSTR(p_challan_number ,INSTR (p_challan_number, ' -',1,1)+3,(INSTR (p_challan_number, ' -',1,2)-(INSTR (p_challan_number, ' -',1,1)+3))))
OR
(hoi.org_information_id = p_org_information_id)
);
SELECT MAX(TO_NUMBER(action_information25))
INTO j
FROM pay_action_information
WHERE action_information_category = 'IN_24Q_CHALLAN'
AND action_information3 = g_gre_id
AND action_information2 = g_year||g_quarter
AND action_context_id = p_24qa_pay_act_id
AND action_context_id IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'O'
);
SELECT MAX(TO_NUMBER(action_information25))
INTO k
FROM pay_action_information
WHERE action_information_category = 'IN_24QC_CHALLAN'
AND action_information3 = g_gre_id
AND action_information2 = g_year||g_quarter
AND action_information15 = p_24qa_pay_act_id
AND action_context_id IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
);
,p_action_information19 => update_challans(g_challan_data_add(i).transfer_voucher_number)
,p_action_information25 => l_challan_record_number + i
,p_action_information_id => l_action_info_id
,p_object_version_number => l_ovn
);
pay_in_utils.set_location(g_debug,'Fetching Updated Challan Data ' ,1);
pay_in_utils.set_location(g_debug,'Checking archived presence of this updated challan',1);
SELECT DISTINCT action_information25
INTO j
FROM pay_action_information
WHERE action_information_category = 'IN_24Q_CHALLAN'
AND action_information3 = g_gre_id
AND action_information2 = g_year||g_quarter
AND action_context_id = p_24qa_pay_act_id
AND(action_information1 = g_challan_data_upd(i).transfer_voucher_number
OR
source_id = g_challan_data_upd(i).org_information_id
)
AND p_24qa_pay_act_id IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'O'
);
SELECT DISTINCT action_information25
INTO k
FROM pay_action_information
WHERE action_information_category = 'IN_24QC_CHALLAN'
AND action_information3 = g_gre_id
AND action_information2 = g_year||g_quarter
AND action_context_id = p_24qa_pay_act_id
AND(action_information1 = g_challan_data_upd(i).transfer_voucher_number
OR
source_id = g_challan_data_upd(i).org_information_id
)
AND action_information15 IN(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
);
,p_action_information19 => update_challans(g_challan_data_upd(i).transfer_voucher_number)
,p_action_information25 => l_challan_record_number
,p_action_information_id => l_action_info_id
,p_object_version_number => l_ovn
);
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information4 = g_cancel_ref_number;
SELECT action_information2
,action_information5
,action_information7
,DECODE(action_information_category,'IN_24Q_ORG',action_information21,action_information7)
FROM pay_action_information
WHERE action_context_id = p_action_context_id
AND action_information_category LIKE 'IN_24Q%ORG'
AND action_context_type = 'PA';
SELECT action_information5 legal_name
,action_information7 emplr_type
,DECODE(action_information_category,'IN_24Q_ORG',action_information21,action_information7) emplr_type_24Q
,1 BUI
,action_context_id
FROM pay_action_information
WHERE action_information_category IN ('IN_24Q_ORG','IN_24QC_ORG')
AND action_context_id = p_last_act_cxt_id
AND action_context_type = 'PA'
AND action_information1 = g_gre_id
AND action_information2 = p_tan_number
AND action_information3 = g_year||g_quarter
ORDER BY action_context_id DESC;
SELECT action_information26
FROM pay_action_information
WHERE action_information_category = 'IN_24Q_ORG'
AND action_context_id = p_24qa_pay_act_id
AND action_context_type = 'PA'
AND action_information1 = g_gre_id
AND action_information2 = p_tan_number
AND action_information3 = g_year||g_quarter
ORDER BY action_context_id DESC;
SELECT DECODE(DECODE(g_24qc_empr_change,'N',DECODE(g_24qc_rep_adr_chg,'Y','Y','N'),'Y'),'Y',1,0)
INTO l_batch_upd_indicator
FROM dual;
SELECT 1
FROM pay_action_information
WHERE action_information_category = 'IN_24Q_PERSON'
AND action_context_id = p_assignment_act_id
AND action_information3 = g_gre_id;
SELECT 1
FROM pay_action_information
WHERE action_information_category = 'IN_24QC_DEDUCTEE'
AND action_context_id = p_assignment_act_id
AND action_information3 = g_gre_id;
SELECT assignment_action_id
,assignment_id
FROM pay_assignment_actions
WHERE payroll_action_id = p_payroll_action_id;
SELECT assignment_action_id
FROM pay_assignment_actions paa
,pay_action_interlocks pai
WHERE paa.assignment_id = p_assignment_id
AND paa.assignment_action_id = pai.locking_action_id
AND paa.payroll_action_id = p_payroll_action_id;
SELECT 1
FROM pay_action_interlocks pai
WHERE pai.locking_action_id = p_locking_act_id
AND pai.locked_action_id = p_locked_act_id;
CURSOR c_select_prev_24qc
IS
SELECT MAX(org_information3)
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
AND org_information3 <> p_locked_pay_act_id_qc;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_action_id
FROM dual;
pay_in_utils.set_location(g_debug,'Inserting Assignment Actions ', 14);
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_action_id
FROM dual;
pay_in_utils.set_location(g_debug,'Inserting Assignment Actions as it was null', 14);
OPEN c_select_prev_24qc;
FETCH c_select_prev_24qc INTO l_24qc_prv_pay_act_id;
CLOSE c_select_prev_24qc;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_action_id
FROM dual;
pay_in_utils.set_location(g_debug,'Inserting Assignment Actions as it was null', 14);
CURSOR c_select_deductee
IS
SELECT DISTINCT pai.action_information1 challan
FROM pay_action_information pai
,pay_assignment_actions paa
WHERE pai.action_information_category = 'IN_24QC_DEDUCTEE'
AND paa.assignment_action_id = pai.action_context_id
AND paa.payroll_action_id = p_payroll_action_id
AND pai.action_information19 IS NOT NULL
AND pai.action_information20 IS NOT NULL;
SELECT pai.action_information25 challan_seq
FROM pay_action_information pai
WHERE pai.action_information_category IN ('IN_24QC_CHALLAN','IN_24Q_CHALLAN')
AND pai.action_information1 = p_challan_number
AND pai.action_context_id IN
(
SELECT org_information3
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
)
AND ROWNUM = 1;
SELECT action_information2 tan_number
,action_information5 legal_name
,action_information7 emplr_type
,DECODE(action_information_category,'IN_24Q_ORG',action_information21,action_information7) emplr_type_24Q
FROM pay_action_information
WHERE action_information_category IN ('IN_24Q_ORG','IN_24QC_ORG')
AND action_context_id = p_last_act_cxt_id
AND action_context_type = 'PA'
AND action_information1 = g_gre_id
AND action_information3 = g_year||g_quarter
ORDER BY action_context_id DESC;
FOR c_rec IN c_select_deductee
LOOP
OPEN c_challan_sequence(c_rec.challan);
pay_in_utils.set_location(g_debug,'Checking Challan Data in updated Data', 1);
pay_in_utils.set_location(g_debug,'Found Challan in updated Data', 1);
,p_action_information19 => update_challans(g_challan_data_upd(i).transfer_voucher_number)
,p_action_information25 => l_challan_deductee_no
,p_action_information29 => 'C5'
,p_action_information_id => l_action_info_id
,p_object_version_number => l_ovn
);
,p_action_information19 => update_challans(g_challan_data_noc(i).transfer_voucher_number)
,p_action_information25 => l_challan_deductee_no
,p_action_information29 => 'C5'
,p_action_information_id => l_action_info_id
,p_object_version_number => l_ovn
);
SELECT org_information3 -- payroll action id of this quarter Form 24Q A/C Archival
,org_information6 -- Archival Type (Original or Correction Statement)
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
ORDER BY org_information3 DESC;
SELECT 1
FROM pay_action_interlocks
WHERE locking_action_id = p_locking_action_id
AND locked_action_id = p_locked_action_id;
SELECT assignment_action_id
FROM pay_assignment_actions
WHERE payroll_action_id = p_payroll_act_id
AND assignment_id = p_assignment_id;
SELECT max(locking_action_id)
FROM pay_action_interlocks
WHERE locked_action_id = p_24qc_arc_asg_act_id
ORDER BY locking_action_id DESC;
CURSOR c_select_prev_24qc(p_locked_pay_act_id_qc NUMBER)
IS
SELECT MAX(org_information3)
FROM hr_organization_information
WHERE org_information_context = 'PER_IN_FORM24Q_RECEIPT_DF'
AND organization_id = g_gre_id
AND org_information1 = g_year
AND org_information2 = g_quarter
AND org_information5 = 'A'
AND org_information6 = 'C'
AND org_information3 <> p_locked_pay_act_id_qc;
SELECT source_id
FROM pay_action_information
WHERE action_context_id = p_pay_act_id_last_24Q
AND action_information_category = 'IN_24QC_ORG'
AND action_context_type = 'PA'
AND action_information3 = g_year||g_quarter;
SELECT DECODE(pai.action_information_category,'IN_24Q_ORG',1,2)
FROM pay_action_information pai
WHERE pai.action_information_category IN ('IN_24QC_ORG','IN_24Q_ORG')
AND pai.action_context_id = p_pay_act_id_last_24Q
AND pai.action_context_type = 'PA'
AND pai.action_information3 = g_year||g_quarter
AND pai.action_information1 = g_gre_id;
OPEN c_select_prev_24qc(g_24qc_payroll_act_id);
FETCH c_select_prev_24qc INTO l_last_24qc_pay_act_id;
CLOSE c_select_prev_24qc;
pay_in_utils.set_location(g_debug,'Inserting assignment action ids for Salary Records',30);
pay_in_utils.set_location(g_debug,'Inserting Asg Action ID for Deleted Assignment Actions ' ,35);
pay_in_utils.set_location(g_debug,'Value of g_count_sal_delete is '|| g_count_sal_delete, 39);
IF ((g_count_sal_delete > 1) AND (g_correction_mode <> 'Y'))
THEN
FOR i IN 1..g_count_sal_delete - 1
LOOP
l_24q_asg_action_id := NULL;
pay_in_utils.trace('l_dummy in g_count_sal_delete ',l_dummy);
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_action_id
FROM dual;
pay_in_utils.set_location(g_debug,'Inserting Assignment action id ',61);
pay_in_utils.set_location(g_debug,'Inserting Asg Action ID for Added Assignment Actions ' ,60);
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_action_id
FROM dual;
pay_in_utils.set_location(g_debug,'Inserting Assignment action id ',61);
pay_in_utils.set_location(g_debug,'Inserting Asg Action ID for Updated Assignment Actions ' ,70);
pay_in_utils.set_location(g_debug,'Value of g_count_sal_update is '|| g_count_sal_update, 71);
IF ((g_count_sal_update > 1) AND (g_correction_mode <> 'Y'))
THEN
FOR i IN 1..g_count_sal_update - 1
LOOP
l_24q_asg_action_id := NULL;
pay_in_utils.trace('l_dummy in g_count_sal_update ',l_dummy);
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_action_id
FROM dual;
pay_in_utils.set_location(g_debug,'Inserting Assignment action id ',61);
pay_in_utils.set_location(g_debug,'Asg Action ID Insertion Over for Salary Records ', 80);
pay_in_utils.set_location(g_debug,'Inserting assignment action id in pay_assignment_actions ',40);
pay_in_utils.set_location(g_debug,'Inserting Asg Action ID for Deleted Element Entries ' ,50);
pay_in_utils.set_location(g_debug,'Value of g_count_ee_delete is '|| g_count_ee_delete, 51);
IF ((g_count_ee_delete > 1)AND (g_correction_mode <> 'Y'))
THEN
FOR i IN 1..g_count_ee_delete - 1
LOOP
l_24q_asg_action_id := NULL;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_action_id
FROM dual;
pay_in_utils.set_location(g_debug,'Inserting Assignment action id ',61);
pay_in_utils.set_location(g_debug,'Inserting Asg Action ID for Added Element Entries ' ,60);
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_action_id
FROM dual;
pay_in_utils.set_location(g_debug,'Inserting Assignment action id ',61);
pay_in_utils.set_location(g_debug,'Inserting Asg Action ID for Updated Element Entries ' ,70);
pay_in_utils.set_location(g_debug,'Value of g_count_ee_update is '|| g_count_ee_update, 71);
IF ((g_count_ee_update > 1)AND(g_correction_mode <> 'Y'))
THEN
FOR i IN 1..g_count_ee_update - 1
LOOP
l_24q_asg_action_id := NULL;
SELECT pay_assignment_actions_s.NEXTVAL
INTO l_action_id
FROM dual;
pay_in_utils.set_location(g_debug,'Inserting Assignment action id ',61);
pay_in_utils.set_location(g_debug,'Asg Action ID Insertion Over ', 80);
IF ((g_count_ee_delete = 1) AND (g_count_ee_addition = 1) AND (g_count_ee_update = 1))
THEN
pay_in_utils.set_location(g_debug,'Generating Locking...',2);
SELECT DECODE(action_information15, 1, 'C1')
,action_information_id
,object_version_number
,action_information29
FROM pay_action_information
WHERE action_information1 = g_gre_id
AND action_information3 = g_year||g_quarter
AND action_context_type = 'PA'
AND action_information_category = 'IN_24QC_ORG'
AND action_context_id = p_payroll_action_id;
SELECT action_information1 challan
,DECODE(action_information18, 'U', 'C2', 'A', 'C9', 'NC', null) correction_type
,action_information_id
,object_version_number
FROM pay_action_information
WHERE action_information3 = g_gre_id
AND action_information2 = g_year||g_quarter
AND action_context_type = 'PA'
AND action_information_category = 'IN_24QC_CHALLAN'
AND action_context_id = p_payroll_action_id;
SELECT 'C3' correction_type
FROM pay_action_information pai
WHERE pai.action_context_id IN (SELECT assignment_action_id
FROM pay_assignment_actions
WHERE payroll_action_id = p_payroll_action_id
AND assignment_id = pai.assignment_id
)
AND action_context_type = 'AAP'
AND action_information_category = 'IN_24QC_DEDUCTEE'
AND action_information3 = g_gre_id
AND action_information2 = g_year||g_quarter
AND action_information15 IN ('U', 'A', 'D')
AND (
( action_information19 IS NULL
AND action_information20 IS NULL
)
OR
( (INSTR(action_information19,'C3') <> 0)
)
)
AND action_information1 = p_challan_number;
SELECT 'C5' correction_type
FROM pay_action_information pai
WHERE pai.action_context_id IN ( SELECT assignment_action_id
FROM pay_assignment_actions
WHERE payroll_action_id = p_payroll_action_id
AND assignment_id = pai.assignment_id
)
AND action_context_type = 'AAP'
AND action_information_category = 'IN_24QC_DEDUCTEE'
AND action_information3 = g_gre_id
AND action_information2 = g_year||g_quarter
AND INSTR(action_information19,'C5') <> 0
AND action_information20 IS NOT NULL
AND action_information1 = p_challan_number;
SELECT 'C5' correction_type
FROM pay_action_information pai
WHERE pai.action_context_id IN ( SELECT assignment_action_id
FROM pay_assignment_actions
WHERE payroll_action_id = p_payroll_action_id
AND assignment_id = pai.assignment_id
)
AND action_context_type = 'AAP'
AND action_information_category = 'IN_24QC_PERSON'
AND action_information3 = g_gre_id
AND action_information2 = g_year||g_quarter
AND INSTR(action_information11,'C5') <> 0
AND action_information1 = p_person_id;
SELECT 'C4' correction_type
FROM pay_action_information pai
WHERE pai.action_context_id IN ( SELECT assignment_action_id
FROM pay_assignment_actions
WHERE payroll_action_id = p_payroll_action_id
AND assignment_id = pai.assignment_id
)
AND action_context_type = 'AAP'
AND action_information_category = 'IN_24QC_PERSON'
AND action_information3 = g_gre_id
AND action_information2 = g_year||g_quarter
AND INSTR(action_information11,'C4') <> 0
AND action_information1 = p_person_id;
CURSOR cur_salary_update_recs
IS
SELECT DISTINCT action_information3 gre_id
FROM pay_action_information
WHERE action_information_category = 'IN_24QC_PERSON'
AND action_context_type = 'AAP'
AND action_information10 = 'A'
AND action_information11 = 'C4'
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;
CURSOR cur_person_update_recs( p_gre_id VARCHAR2)
IS
SELECT DISTINCT action_information1 person_id
, source_id
, action_information_id
, object_version_number
FROM pay_action_information
WHERE action_information_category = 'IN_24QC_PERSON'
AND action_context_type = 'AAP'
AND action_information3 = fnd_number.canonical_to_number(p_gre_id)
AND action_information10 = 'A'
AND action_information11 = 'C4'
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;
SELECT MIN(fnd_number.canonical_to_number(action_information12)) min_salary_rec
FROM pay_action_information
WHERE action_information_category = 'IN_24QC_PERSON'
AND action_context_type = 'AAP'
AND action_information3 = fnd_number.canonical_to_number(p_gre_id)
AND action_information10 = 'A'
AND action_information11 = 'C4'
AND action_context_id IN (SELECT assignment_action_id
FROM pay_assignment_actions
WHERE payroll_action_id = p_payroll_action_id);
SELECT DISTINCT action_information1 person_id
FROM pay_action_information pai
WHERE action_information_category = 'IN_24QC_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
AND assignment_id = pai.assignment_id)
AND action_information3 = g_gre_id
AND action_information2 = g_year||g_quarter;
SELECT SUM(NVL(pai.action_information9,0)) amount_deposited
FROM pay_action_information pai
,pay_assignment_actions paa
WHERE pai.action_information_category = 'IN_24Q_DEDUCTEE'
AND paa.assignment_action_id = pai.action_context_id
AND pai.action_information1 = p_challan_number
AND paa.payroll_action_id = p_24q_pay_action_id;
l_org_update_string VARCHAR(2400);
pay_action_information_api.update_action_information
(p_validate => FALSE
,p_action_information_id => t_challan_nos(l_index).action_information_id
,p_object_version_number => t_challan_nos(l_index).object_version_number
,p_action_information29 => t_challan_nos(l_index).correction_type
);
l_org_update_string := NVL(l_org_update_string, '1') ||':'||t_challan_nos(l_index).correction_type;
FOR c_salary_rec IN cur_salary_update_recs
LOOP
OPEN cur_min_salary_record(c_salary_rec.gre_id);
FOR cur_rec IN cur_person_update_recs(c_salary_rec.gre_id)
LOOP
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_information12 => l_index
);
pay_in_utils.set_location(g_debug,'Updated SD Record Number for A mode in Salary Records : ',3);
l_org_update_string := NVL(l_org_update_string, '1') ||':'||l_correction_type;
l_org_update_string := NVL(l_org_update_string, '1') ||':'||l_correction_type;
IF INSTR(l_org_update_string, 'C2') <> 0 THEN
IF org_correction_type is null or org_correction_type = 'C1' THEN
org_correction_type := 'C2';
IF INSTR(l_org_update_string, 'C3') <> 0 THEN
IF org_correction_type IS NULL OR org_correction_type = 'C1' THEN
org_correction_type := 'C3';
IF INSTR(l_org_update_string, 'C4') <> 0 THEN
IF org_correction_type IS NULL THEN
org_correction_type := 'C4';
IF INSTR(l_org_update_string, 'C5') <> 0 THEN
IF org_correction_type IS NULL THEN
org_correction_type := 'C5';
IF INSTR(l_org_update_string, 'C9') <> 0 THEN
IF org_correction_type IS NULL THEN
org_correction_type := 'C9';
pay_action_information_api.update_action_information
(p_validate => FALSE
,p_action_information_id => l_action_info_id
,p_object_version_number => l_ovn
,p_action_information29 => org_correction_type
);