[Home] [Help]
408: ,p_period_end DATE)
409: IS
410: SELECT DISTINCT source_id org_id
411: ,action_information9 org_name
412: FROM pay_action_information
413: ,hr_organization_units hou
414: WHERE action_information_category = 'IN_PT_ASG'
415: AND action_context_type = 'AAP'
416: AND source_id = NVL(p_pt_org_id, source_id)
425: ,p_period_start DATE
426: ,p_period_end DATE)
427: IS
428: SELECT MAX(pai.action_context_id)
429: FROM pay_action_information pai
430: ,pay_assignment_actions pac
431: WHERE pai.action_information_category = 'IN_PT_PAY'
432: AND pai.action_context_type = 'PA'
433: AND pai.source_id = p_pt_org_id
433: AND pai.source_id = p_pt_org_id
434: AND pac.payroll_action_id = pai.action_context_id
435: AND pac.assignment_action_id IN
436: ( SELECT action_context_id
437: FROM pay_action_information
438: WHERE action_information_category = 'IN_PT_ASG'
439: AND action_context_type = 'AAP'
440: AND action_information1 = p_year -- Bug 5231500
441: AND source_id = p_pt_org_id
440: AND action_information1 = p_year -- Bug 5231500
441: AND source_id = p_pt_org_id
442: AND TO_DATE(action_information8,'DD-MM-YY')=
443: ( SELECT MAX(TO_DATE(action_information8,'DD-MM-YY'))
444: FROM pay_action_information
445: WHERE action_information_category = 'IN_PT_ASG'
446: AND action_context_type = 'AAP'
447: AND action_information1 = p_year -- Bug 5231500
448: AND TO_DATE(action_information8, 'DD-MM-YYYY') BETWEEN p_period_start AND p_period_end
460: ,action_information5 rep_name
461: ,action_information9 rep_desg
462: ,action_information8 org_name
463: ,action_information3 bsrtc
464: FROM pay_action_information
465: WHERE action_information_category = 'IN_PT_PAY'
466: AND action_context_type = 'PA'
467: AND source_id = p_pt_org_id
468: AND action_context_id = p_payroll_action_id;
488: ,p_max_sal NUMBER)
489: IS
490: SELECT COUNT(*) count
491: ,fnd_number.canonical_to_number(pai.action_information5) rate
492: FROM pay_action_information pai
493: WHERE pai.jurisdiction_code = 'MH'
494: AND pai.source_id = p_source_id
495: AND pai.action_information_category = 'IN_PT_ASG'
496: AND pai.action_information1 = p_year
497: AND TO_NUMBER(pai.action_information2) = p_month
498: AND pai.action_context_type = 'AAP'
499: AND pai.action_information6 <> 'Yes'
500: AND pai.action_information_id = (SELECT MAX(action_information_id)
501: FROM pay_action_information
502: WHERE action_information1 = p_year
503: AND TO_NUMBER(action_information2) = p_month
504: AND assignment_id = pai.assignment_id
505: AND action_context_type = 'AAP'
1128:
1129: CURSOR csr_org_max_action_context_id
1130: IS
1131: SELECT MAX(pai.action_context_id)
1132: FROM pay_action_information pai
1133: WHERE pai.action_information_category = 'IN_EOY_ORG'
1134: AND pai.Action_information1 = p_gre_org_id
1135: AND pai.action_information3 = p_assess_year
1136: AND pai.action_context_type = 'PA';
1160: , pay_in_reports_pkg.get_location_details(pai.action_information16,'POSTAL_CODE')
1161: , pai.action_information17
1162: , pai.action_information18
1163: , pai.action_information13
1164: FROM pay_action_information pai
1165: , pay_payroll_actions ppa
1166: WHERE pai.action_information_category = 'IN_EOY_ORG'
1167: AND pai.action_context_type = 'PA'
1168: AND pai.action_information1 = p_gre_org_id
1176:
1177: CURSOR csr_tot_emp_cnt
1178: IS
1179: SELECT COUNT(*)
1180: FROM pay_action_information
1181: WHERE action_information_category = 'IN_EOY_PERSON'
1182: AND action_context_type = 'AAP'
1183: AND action_information2 = p_assess_year
1184: AND action_information3 = p_gre_org_id
1182: AND action_context_type = 'AAP'
1183: AND action_information2 = p_assess_year
1184: AND action_information3 = p_gre_org_id
1185: AND action_context_id IN ( SELECT MAX(action_context_id)
1186: FROM pay_action_information pai
1187: ,pay_assignment_actions paa
1188: ,per_assignments_f asg
1189: WHERE pai.action_information_category = 'IN_EOY_PERSON'
1190: AND pai.action_context_type = 'AAP'
1198:
1199: CURSOR csr_tax_details(p_balance VARCHAR2,p_action_context_id NUMBER,p_source_id IN NUMBER)
1200: IS
1201: SELECT NVL(SUM(fnd_number.canonical_to_number(action_information2)),0)
1202: FROM pay_action_information
1203: WHERE action_information_category = 'IN_EOY_ASG_SAL'
1204: AND action_context_type = 'AAP'
1205: AND action_information1 = p_balance
1206: AND action_context_id = p_action_context_id
1208:
1209: CURSOR csr_get_max_cont_id IS
1210: SELECT MAX(pai.action_context_id) action_cont_id
1211: ,source_id sour_id
1212: FROM pay_action_information pai
1213: ,pay_assignment_actions paa
1214: ,per_assignments_f asg
1215: WHERE pai.action_information_category = 'IN_EOY_PERSON'
1216: AND pai.action_information3 = p_gre_org_id
1413: IS
1414: SELECT
1415: substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 ) member_id
1416: ,count(DISTINCT paa_asg.action_information4) asg_cnt
1417: FROM pay_action_information paa_asg
1418: ,pay_action_information paa_pay
1419: ,pay_assignment_actions paa
1420: WHERE paa_asg.action_information_category='IN_PF_ASG'
1421: AND paa_pay.action_information_category='IN_PF_PAY'
1414: SELECT
1415: substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 ) member_id
1416: ,count(DISTINCT paa_asg.action_information4) asg_cnt
1417: FROM pay_action_information paa_asg
1418: ,pay_action_information paa_pay
1419: ,pay_assignment_actions paa
1420: WHERE paa_asg.action_information_category='IN_PF_ASG'
1421: AND paa_pay.action_information_category='IN_PF_PAY'
1422: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
1417: FROM pay_action_information paa_asg
1418: ,pay_action_information paa_pay
1419: ,pay_assignment_actions paa
1420: WHERE paa_asg.action_information_category='IN_PF_ASG'
1421: AND paa_pay.action_information_category='IN_PF_PAY'
1422: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
1423: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
1424: AND paa.assignment_action_id=paa_asg.action_context_id
1425: AND paa.payroll_action_id=paa_pay.action_context_id
1422: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
1423: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
1424: AND paa.assignment_action_id=paa_asg.action_context_id
1425: AND paa.payroll_action_id=paa_pay.action_context_id
1426: AND paa_pay.action_information7 NOT IN ('EXEM')
1427: AND paa_asg.action_information3 IS NOT NULL
1428: AND paa_asg.action_information24=p_contribution_period
1429: AND paa_pay.action_information24=p_contribution_period
1430: AND paa_asg.action_information2=p_pf_org_id
1425: AND paa.payroll_action_id=paa_pay.action_context_id
1426: AND paa_pay.action_information7 NOT IN ('EXEM')
1427: AND paa_asg.action_information3 IS NOT NULL
1428: AND paa_asg.action_information24=p_contribution_period
1429: AND paa_pay.action_information24=p_contribution_period
1430: AND paa_asg.action_information2=p_pf_org_id
1431: AND paa_pay.action_information2=p_pf_org_id
1432: AND to_number(to_char(to_date(paa_asg.action_information13,'DD/MM/YY'),'MM'))=p_mon_number
1433: GROUP BY substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 )
1427: AND paa_asg.action_information3 IS NOT NULL
1428: AND paa_asg.action_information24=p_contribution_period
1429: AND paa_pay.action_information24=p_contribution_period
1430: AND paa_asg.action_information2=p_pf_org_id
1431: AND paa_pay.action_information2=p_pf_org_id
1432: AND to_number(to_char(to_date(paa_asg.action_information13,'DD/MM/YY'),'MM'))=p_mon_number
1433: GROUP BY substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 )
1434: HAVING count(DISTINCT paa_asg.action_information4) > 1
1435: ORDER BY substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 ) ASC;
1441: IS
1442: SELECT
1443: DISTINCT substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 ) member_id
1444: ,paa_asg.action_information4 member_name
1445: FROM pay_action_information paa_asg
1446: ,pay_action_information paa_pay
1447: ,pay_assignment_actions paa
1448: WHERE paa_asg.action_information_category='IN_PF_ASG'
1449: AND paa_pay.action_information_category='IN_PF_PAY'
1442: SELECT
1443: DISTINCT substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 ) member_id
1444: ,paa_asg.action_information4 member_name
1445: FROM pay_action_information paa_asg
1446: ,pay_action_information paa_pay
1447: ,pay_assignment_actions paa
1448: WHERE paa_asg.action_information_category='IN_PF_ASG'
1449: AND paa_pay.action_information_category='IN_PF_PAY'
1450: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
1445: FROM pay_action_information paa_asg
1446: ,pay_action_information paa_pay
1447: ,pay_assignment_actions paa
1448: WHERE paa_asg.action_information_category='IN_PF_ASG'
1449: AND paa_pay.action_information_category='IN_PF_PAY'
1450: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
1451: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
1452: AND paa.assignment_action_id=paa_asg.action_context_id
1453: AND paa.payroll_action_id=paa_pay.action_context_id
1450: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
1451: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
1452: AND paa.assignment_action_id=paa_asg.action_context_id
1453: AND paa.payroll_action_id=paa_pay.action_context_id
1454: AND paa_pay.action_information7 NOT IN ('EXEM')
1455: AND paa_asg.action_information3 IS NOT NULL
1456: AND paa_asg.action_information24=p_contribution_period
1457: AND paa_pay.action_information24=p_contribution_period
1458: AND paa_asg.action_information2=p_pf_org_id
1453: AND paa.payroll_action_id=paa_pay.action_context_id
1454: AND paa_pay.action_information7 NOT IN ('EXEM')
1455: AND paa_asg.action_information3 IS NOT NULL
1456: AND paa_asg.action_information24=p_contribution_period
1457: AND paa_pay.action_information24=p_contribution_period
1458: AND paa_asg.action_information2=p_pf_org_id
1459: AND paa_pay.action_information2=p_pf_org_id
1460: AND to_number(to_char(to_date(paa_asg.action_information13,'DD/MM/YY'),'MM'))=p_mon_number
1461: ORDER BY substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 ) ASC;
1455: AND paa_asg.action_information3 IS NOT NULL
1456: AND paa_asg.action_information24=p_contribution_period
1457: AND paa_pay.action_information24=p_contribution_period
1458: AND paa_asg.action_information2=p_pf_org_id
1459: AND paa_pay.action_information2=p_pf_org_id
1460: AND to_number(to_char(to_date(paa_asg.action_information13,'DD/MM/YY'),'MM'))=p_mon_number
1461: ORDER BY substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 ) ASC;
1462:
1463:
1484: ,to_char(to_date(paa_asg.action_information16,'DD/MM/RRRR'),'DD/MM/RRRR') hire_date
1485: ,to_char(to_date(paa_asg.action_information25,'DD/MM/RRRR'),'DD/MM/RRRR') term_date
1486: ,paa_asg.action_information26 term_reason
1487: ,to_char(to_date(paa_asg.action_information13,'DD/MM/RRRR'),'DD/MM/RRRR') payroll_date
1488: FROM pay_action_information paa_asg
1489: ,pay_action_information paa_pay
1490: ,pay_assignment_actions paa
1491: WHERE paa_asg.action_information_category='IN_PF_ASG'
1492: AND paa_pay.action_information_category='IN_PF_PAY'
1485: ,to_char(to_date(paa_asg.action_information25,'DD/MM/RRRR'),'DD/MM/RRRR') term_date
1486: ,paa_asg.action_information26 term_reason
1487: ,to_char(to_date(paa_asg.action_information13,'DD/MM/RRRR'),'DD/MM/RRRR') payroll_date
1488: FROM pay_action_information paa_asg
1489: ,pay_action_information paa_pay
1490: ,pay_assignment_actions paa
1491: WHERE paa_asg.action_information_category='IN_PF_ASG'
1492: AND paa_pay.action_information_category='IN_PF_PAY'
1493: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
1488: FROM pay_action_information paa_asg
1489: ,pay_action_information paa_pay
1490: ,pay_assignment_actions paa
1491: WHERE paa_asg.action_information_category='IN_PF_ASG'
1492: AND paa_pay.action_information_category='IN_PF_PAY'
1493: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
1494: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
1495: AND paa.assignment_action_id=paa_asg.action_context_id
1496: AND paa.payroll_action_id=paa_pay.action_context_id
1493: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
1494: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
1495: AND paa.assignment_action_id=paa_asg.action_context_id
1496: AND paa.payroll_action_id=paa_pay.action_context_id
1497: AND paa_pay.action_information7 NOT IN ('EXEM')
1498: AND paa_asg.action_information3 IS NOT NULL
1499: AND paa_asg.action_information24=p_contribution_period
1500: AND paa_pay.action_information24=p_contribution_period
1501: AND paa_asg.action_information2=p_pf_org_id
1496: AND paa.payroll_action_id=paa_pay.action_context_id
1497: AND paa_pay.action_information7 NOT IN ('EXEM')
1498: AND paa_asg.action_information3 IS NOT NULL
1499: AND paa_asg.action_information24=p_contribution_period
1500: AND paa_pay.action_information24=p_contribution_period
1501: AND paa_asg.action_information2=p_pf_org_id
1502: AND paa_pay.action_information2=p_pf_org_id
1503: AND substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 )=p_pf_number
1504: AND paa_asg.action_information4 = p_member_name
1498: AND paa_asg.action_information3 IS NOT NULL
1499: AND paa_asg.action_information24=p_contribution_period
1500: AND paa_pay.action_information24=p_contribution_period
1501: AND paa_asg.action_information2=p_pf_org_id
1502: AND paa_pay.action_information2=p_pf_org_id
1503: AND substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 )=p_pf_number
1504: AND paa_asg.action_information4 = p_member_name
1505: AND to_number(to_char(to_date(paa_asg.action_information13,'DD/MM/YY'),'MM'))=p_mon_number
1506: ORDER BY substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 ),fnd_number.canonical_to_number(paa_asg.action_information7) ASC;
2072: ,p_effective_start_date DATE
2073: ,p_effective_end_date DATE
2074: )
2075: IS
2076: SELECT DISTINCT paa_pay.action_information2 --PF Org Id
2077: ,paa_pay.action_information3 --PF Org Reg Name
2078: ,paa_pay.action_information5 --Address
2079: ,paa_pay.action_information6 --Code
2080: ,paa_pay.action_information8 --PF Org Name
2073: ,p_effective_end_date DATE
2074: )
2075: IS
2076: SELECT DISTINCT paa_pay.action_information2 --PF Org Id
2077: ,paa_pay.action_information3 --PF Org Reg Name
2078: ,paa_pay.action_information5 --Address
2079: ,paa_pay.action_information6 --Code
2080: ,paa_pay.action_information8 --PF Org Name
2081: FROM pay_action_information paa_asg
2074: )
2075: IS
2076: SELECT DISTINCT paa_pay.action_information2 --PF Org Id
2077: ,paa_pay.action_information3 --PF Org Reg Name
2078: ,paa_pay.action_information5 --Address
2079: ,paa_pay.action_information6 --Code
2080: ,paa_pay.action_information8 --PF Org Name
2081: FROM pay_action_information paa_asg
2082: ,pay_action_information paa_pay
2075: IS
2076: SELECT DISTINCT paa_pay.action_information2 --PF Org Id
2077: ,paa_pay.action_information3 --PF Org Reg Name
2078: ,paa_pay.action_information5 --Address
2079: ,paa_pay.action_information6 --Code
2080: ,paa_pay.action_information8 --PF Org Name
2081: FROM pay_action_information paa_asg
2082: ,pay_action_information paa_pay
2083: ,pay_assignment_actions paa
2076: SELECT DISTINCT paa_pay.action_information2 --PF Org Id
2077: ,paa_pay.action_information3 --PF Org Reg Name
2078: ,paa_pay.action_information5 --Address
2079: ,paa_pay.action_information6 --Code
2080: ,paa_pay.action_information8 --PF Org Name
2081: FROM pay_action_information paa_asg
2082: ,pay_action_information paa_pay
2083: ,pay_assignment_actions paa
2084: ,hr_organization_units hou
2077: ,paa_pay.action_information3 --PF Org Reg Name
2078: ,paa_pay.action_information5 --Address
2079: ,paa_pay.action_information6 --Code
2080: ,paa_pay.action_information8 --PF Org Name
2081: FROM pay_action_information paa_asg
2082: ,pay_action_information paa_pay
2083: ,pay_assignment_actions paa
2084: ,hr_organization_units hou
2085: WHERE paa_asg.action_information_category = 'IN_PF_ASG'
2078: ,paa_pay.action_information5 --Address
2079: ,paa_pay.action_information6 --Code
2080: ,paa_pay.action_information8 --PF Org Name
2081: FROM pay_action_information paa_asg
2082: ,pay_action_information paa_pay
2083: ,pay_assignment_actions paa
2084: ,hr_organization_units hou
2085: WHERE paa_asg.action_information_category = 'IN_PF_ASG'
2086: AND paa_pay.action_information_category = 'IN_PF_PAY'
2082: ,pay_action_information paa_pay
2083: ,pay_assignment_actions paa
2084: ,hr_organization_units hou
2085: WHERE paa_asg.action_information_category = 'IN_PF_ASG'
2086: AND paa_pay.action_information_category = 'IN_PF_PAY'
2087: AND paa_asg.ACTION_CONTEXT_TYPE = 'AAP'
2088: AND paa_pay.ACTION_CONTEXT_TYPE = 'PA'
2089: AND paa.assignment_action_id = paa_asg.action_context_id
2090: AND paa.payroll_action_id = paa_pay.action_context_id
2088: AND paa_pay.ACTION_CONTEXT_TYPE = 'PA'
2089: AND paa.assignment_action_id = paa_asg.action_context_id
2090: AND paa.payroll_action_id = paa_pay.action_context_id
2091: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
2092: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'EXEM','%') --PF Org Class
2093: AND paa_asg.action_information2 = paa_pay.action_information2
2094: AND paa_asg.action_information15 IS NOT NULL
2095: AND paa_asg.action_information1 = p_contribution_period
2096: AND paa_pay.action_information1 = p_contribution_period
2089: AND paa.assignment_action_id = paa_asg.action_context_id
2090: AND paa.payroll_action_id = paa_pay.action_context_id
2091: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
2092: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'EXEM','%') --PF Org Class
2093: AND paa_asg.action_information2 = paa_pay.action_information2
2094: AND paa_asg.action_information15 IS NOT NULL
2095: AND paa_asg.action_information1 = p_contribution_period
2096: AND paa_pay.action_information1 = p_contribution_period
2097: AND hou.organization_id=paa_pay.action_information2
2092: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'EXEM','%') --PF Org Class
2093: AND paa_asg.action_information2 = paa_pay.action_information2
2094: AND paa_asg.action_information15 IS NOT NULL
2095: AND paa_asg.action_information1 = p_contribution_period
2096: AND paa_pay.action_information1 = p_contribution_period
2097: AND hou.organization_id=paa_pay.action_information2
2098: AND hou.organization_id=paa_asg.action_information2
2099: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
2100: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
2093: AND paa_asg.action_information2 = paa_pay.action_information2
2094: AND paa_asg.action_information15 IS NOT NULL
2095: AND paa_asg.action_information1 = p_contribution_period
2096: AND paa_pay.action_information1 = p_contribution_period
2097: AND hou.organization_id=paa_pay.action_information2
2098: AND hou.organization_id=paa_asg.action_information2
2099: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
2100: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
2101: ORDER BY paa_pay.action_information8 ASC;
2097: AND hou.organization_id=paa_pay.action_information2
2098: AND hou.organization_id=paa_asg.action_information2
2099: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
2100: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
2101: ORDER BY paa_pay.action_information8 ASC;
2102:
2103: CURSOR c_assignment_id(p_pf_org_id VARCHAR2
2104: ,p_contribution_period VARCHAR2
2105: ,p_effective_start_date DATE
2105: ,p_effective_start_date DATE
2106: ,p_effective_end_date DATE)
2107: IS
2108: SELECT DISTINCT action_information15,assignment_id
2109: FROM pay_action_information
2110: WHERE action_information_category = 'IN_PF_ASG'
2111: AND action_information2 = p_pf_org_id --PF Organization ID
2112: AND action_information1 = p_contribution_period
2113: AND action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
2129: ,p_contribution_period VARCHAR2
2130: ,p_pension_number VARCHAR2)
2131: IS
2132: SELECT '1' status
2133: FROM pay_action_information pai
2134: WHERE pai.action_information_category ='IN_PF_ASG'
2135: AND pai.action_information1=p_contribution_period
2136: AND pai.action_information2 = p_pf_org_id
2137: AND pai.action_information15 = p_pension_number
2146: ,p_pf_salary_ceiling NUMBER)
2147: IS
2148: SELECT
2149: SUM(fnd_number.canonical_to_number(pai_mas.action_information10)) pension
2150: FROM pay_action_information pai_mas
2151: where pai_mas.action_information_category ='IN_PF_ASG'
2152: and pai_mas.action_information1 = p_contribution_period
2153: and pai_mas.action_information2 = p_pf_org_id
2154: and pai_mas.action_information15 = p_pension_number
2152: and pai_mas.action_information1 = p_contribution_period
2153: and pai_mas.action_information2 = p_pf_org_id
2154: and pai_mas.action_information15 = p_pension_number
2155: and pai_mas.action_information_id in (SELECT MAX(pai1.action_information_id)
2156: FROM pay_action_information pai1
2157: WHERE pai1.action_information1 = p_contribution_period
2158: AND pai1.action_information2 = p_pf_org_id
2159: AND pai1.action_information15 = p_pension_number
2160: GROUP BY TRUNC(TO_DATE(pai1.action_information13,'DD-MM-YY'),'MM')
2166: ,p_effective_end_date DATE
2167: )
2168: IS
2169: SELECT DISTINCT TO_DATE(action_information13,'DD-MM-YYYY') mon
2170: FROM pay_action_information
2171: WHERE action_information_category='IN_PF_ASG'
2172: AND action_information3 =p_pf_number
2173: AND action_information2 =p_pf_org_id --PF Organization ID
2174: AND action_information1 = p_contribution_period -- Bug 5231500
2195: ,p_effective_end_date DATE
2196: )
2197: IS
2198: SELECT action_information4 --Full Name
2199: FROM pay_action_information
2200: WHERE action_information_category='IN_PF_ASG'
2201: AND action_information1 = p_contribution_period -- Bug 5231500
2202: AND action_information2 = p_pf_org_id
2203: AND action_information15 = p_pension_number
2209: ,p_effective_start_date DATE
2210: ,p_effective_end_date DATE
2211: )
2212: IS
2213: SELECT paa_pay.action_information4 rep_name
2214: FROM pay_action_information paa_asg
2215: ,pay_action_information paa_pay
2216: ,pay_assignment_actions paa
2217: WHERE paa_asg.action_information_category='IN_PF_ASG'
2210: ,p_effective_end_date DATE
2211: )
2212: IS
2213: SELECT paa_pay.action_information4 rep_name
2214: FROM pay_action_information paa_asg
2215: ,pay_action_information paa_pay
2216: ,pay_assignment_actions paa
2217: WHERE paa_asg.action_information_category='IN_PF_ASG'
2218: AND paa_pay.action_information_category='IN_PF_PAY'
2211: )
2212: IS
2213: SELECT paa_pay.action_information4 rep_name
2214: FROM pay_action_information paa_asg
2215: ,pay_action_information paa_pay
2216: ,pay_assignment_actions paa
2217: WHERE paa_asg.action_information_category='IN_PF_ASG'
2218: AND paa_pay.action_information_category='IN_PF_PAY'
2219: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
2214: FROM pay_action_information paa_asg
2215: ,pay_action_information paa_pay
2216: ,pay_assignment_actions paa
2217: WHERE paa_asg.action_information_category='IN_PF_ASG'
2218: AND paa_pay.action_information_category='IN_PF_PAY'
2219: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
2220: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
2221: AND paa.assignment_action_id=paa_asg.action_context_id
2222: AND paa.payroll_action_id=paa_pay.action_context_id
2219: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
2220: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
2221: AND paa.assignment_action_id=paa_asg.action_context_id
2222: AND paa.payroll_action_id=paa_pay.action_context_id
2223: AND paa_pay.action_information7 = 'EXEM'
2224: AND paa_asg.action_information1=p_contribution_period
2225: AND paa_pay.action_information1=p_contribution_period
2226: AND paa_pay.action_information2=p_pf_org_id
2227: AND paa_asg.action_information2=p_pf_org_id
2221: AND paa.assignment_action_id=paa_asg.action_context_id
2222: AND paa.payroll_action_id=paa_pay.action_context_id
2223: AND paa_pay.action_information7 = 'EXEM'
2224: AND paa_asg.action_information1=p_contribution_period
2225: AND paa_pay.action_information1=p_contribution_period
2226: AND paa_pay.action_information2=p_pf_org_id
2227: AND paa_asg.action_information2=p_pf_org_id
2228: AND paa_asg.action_information1=paa_pay.action_information1
2229: AND paa_asg.action_information2=paa_pay.action_information2
2222: AND paa.payroll_action_id=paa_pay.action_context_id
2223: AND paa_pay.action_information7 = 'EXEM'
2224: AND paa_asg.action_information1=p_contribution_period
2225: AND paa_pay.action_information1=p_contribution_period
2226: AND paa_pay.action_information2=p_pf_org_id
2227: AND paa_asg.action_information2=p_pf_org_id
2228: AND paa_asg.action_information1=paa_pay.action_information1
2229: AND paa_asg.action_information2=paa_pay.action_information2
2230: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
2224: AND paa_asg.action_information1=p_contribution_period
2225: AND paa_pay.action_information1=p_contribution_period
2226: AND paa_pay.action_information2=p_pf_org_id
2227: AND paa_asg.action_information2=p_pf_org_id
2228: AND paa_asg.action_information1=paa_pay.action_information1
2229: AND paa_asg.action_information2=paa_pay.action_information2
2230: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
2231: ORDER BY TO_DATE(paa_asg.action_information13,'DD-MM-YYYY') DESC;
2232:
2225: AND paa_pay.action_information1=p_contribution_period
2226: AND paa_pay.action_information2=p_pf_org_id
2227: AND paa_asg.action_information2=p_pf_org_id
2228: AND paa_asg.action_information1=paa_pay.action_information1
2229: AND paa_asg.action_information2=paa_pay.action_information2
2230: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
2231: ORDER BY TO_DATE(paa_asg.action_information13,'DD-MM-YYYY') DESC;
2232:
2233: /*Added for Bug 5647738*/
2235: , p_pension_no varchar2)
2236: IS
2237: SELECT fnd_number.canonical_to_number(pai.action_information7) pf_wages
2238: ,pai.action_information13 date_earned
2239: FROM pay_action_information pai
2240: WHERE pai.action_information_category ='IN_PF_ASG'
2241: AND pai.action_information1 = p_contribution_period
2242: AND pai.action_information2 = p_pf_org_id
2243: AND pai.action_information15 = p_pension_no
2250: ,pai.action_information18
2251: ,pai.action_information_id
2252: ,pai.assignment_id
2253: HAVING pai.action_information_id = (SELECT MAX(pai1.action_information_id)
2254: FROM pay_Action_information pai1
2255: WHERE pai1.action_information_category ='IN_PF_ASG'
2256: AND pai1.action_information1 = p_contribution_period
2257: AND pai1.action_information2 = p_pf_org_id
2258: AND TRUNC(TO_DATE(pai.action_information13,'DD-MM-YY'),'MM') = TRUNC(TO_DATE(pai1.action_information13,'DD-MM-YY'),'MM')
2745:
2746: CURSOR c_transfer_check
2747: IS
2748: SELECT action_information2
2749: FROM pay_action_information paa
2750: WHERE paa.action_information_category='IN_PF_ASG'
2751: AND paa.action_context_type='AAP'
2752: AND paa.action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
2753: AND paa.action_information3=p_pf_number
2755:
2756: CURSOR c_transfer_status(pf_org_id VARCHAR2)
2757: IS
2758: SELECT 1
2759: FROM pay_action_information paa
2760: WHERE paa.action_information_category='IN_PF_ASG'
2761: AND paa.action_context_type='AAP'
2762: AND paa.action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
2763: AND paa.action_information3=p_pf_number
2764: and paa.action_information2=p_pf_org_id
2765: and TO_DATE(paa.action_information13,'DD-MM-YY')=
2766: (
2767: SELECT MAX(to_date(action_information13,'DD-MM-YY'))
2768: FROM pay_action_information paa
2769: WHERE paa.action_information_category='IN_PF_ASG'
2770: AND paa.action_context_type='AAP'
2771: AND paa.action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
2772: AND paa.action_information3=p_pf_number
2793:
2794: CURSOR c_last_pay_count
2795: IS
2796: SELECT COUNT(action_information2)
2797: FROM pay_action_information
2798: WHERE action_information_category ='IN_PF_ASG'
2799: AND action_information3=p_pf_number
2800: AND action_context_type='AAP'
2801: AND action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
2801: AND action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
2802: AND TO_DATE(action_information13,'DD-MM-YY')=
2803: (
2804: SELECT MAX(TO_DATE(action_information13,'DD-MM-YY'))
2805: FROM pay_action_information paa
2806: WHERE paa.action_information_category='IN_PF_ASG'
2807: AND paa.action_context_type='AAP'
2808: AND paa.action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
2809: AND paa.action_information3=p_pf_number
2811:
2812: CURSOR c_last_pay_date
2813: IS
2814: SELECT MAX(TO_DATE(action_information13,'DD-MM-YY'))
2815: FROM pay_action_information paa
2816: WHERE paa.action_information_category='IN_PF_ASG'
2817: AND paa.action_context_type='AAP'
2818: AND paa.action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
2819: AND paa.action_information3=p_pf_number;
3116: ,p_effective_start_date DATE
3117: ,p_effective_end_date DATE
3118: ,p_contribution_period VARCHAR2)
3119: IS
3120: TYPE c_rec IS RECORD (action_information3 pay_action_information.action_information3%TYPE,
3121: pf_num pay_action_information.action_information3%TYPE);
3122:
3123: TYPE c_asg_id_ref IS REF CURSOR RETURN c_rec;
3124: c_asg_id c_asg_id_ref;
3117: ,p_effective_end_date DATE
3118: ,p_contribution_period VARCHAR2)
3119: IS
3120: TYPE c_rec IS RECORD (action_information3 pay_action_information.action_information3%TYPE,
3121: pf_num pay_action_information.action_information3%TYPE);
3122:
3123: TYPE c_asg_id_ref IS REF CURSOR RETURN c_rec;
3124: c_asg_id c_asg_id_ref;
3125: assignment_rec c_rec;
3129: ,p_effective_end_date DATE
3130: ,p_contribution_period VARCHAR2
3131: )
3132: IS
3133: SELECT DISTINCT paa_pay.action_information2 --PF Org Id
3134: ,paa_pay.action_information3 --PF Org Reg Name
3135: ,paa_pay.action_information5 --Address
3136: ,paa_pay.action_information6 --Code
3137: ,paa_pay.action_information8 --PF Org Name
3130: ,p_contribution_period VARCHAR2
3131: )
3132: IS
3133: SELECT DISTINCT paa_pay.action_information2 --PF Org Id
3134: ,paa_pay.action_information3 --PF Org Reg Name
3135: ,paa_pay.action_information5 --Address
3136: ,paa_pay.action_information6 --Code
3137: ,paa_pay.action_information8 --PF Org Name
3138: FROM pay_action_information paa_asg
3131: )
3132: IS
3133: SELECT DISTINCT paa_pay.action_information2 --PF Org Id
3134: ,paa_pay.action_information3 --PF Org Reg Name
3135: ,paa_pay.action_information5 --Address
3136: ,paa_pay.action_information6 --Code
3137: ,paa_pay.action_information8 --PF Org Name
3138: FROM pay_action_information paa_asg
3139: ,pay_action_information paa_pay
3132: IS
3133: SELECT DISTINCT paa_pay.action_information2 --PF Org Id
3134: ,paa_pay.action_information3 --PF Org Reg Name
3135: ,paa_pay.action_information5 --Address
3136: ,paa_pay.action_information6 --Code
3137: ,paa_pay.action_information8 --PF Org Name
3138: FROM pay_action_information paa_asg
3139: ,pay_action_information paa_pay
3140: ,pay_assignment_actions paa
3133: SELECT DISTINCT paa_pay.action_information2 --PF Org Id
3134: ,paa_pay.action_information3 --PF Org Reg Name
3135: ,paa_pay.action_information5 --Address
3136: ,paa_pay.action_information6 --Code
3137: ,paa_pay.action_information8 --PF Org Name
3138: FROM pay_action_information paa_asg
3139: ,pay_action_information paa_pay
3140: ,pay_assignment_actions paa
3141: ,hr_organization_units hou
3134: ,paa_pay.action_information3 --PF Org Reg Name
3135: ,paa_pay.action_information5 --Address
3136: ,paa_pay.action_information6 --Code
3137: ,paa_pay.action_information8 --PF Org Name
3138: FROM pay_action_information paa_asg
3139: ,pay_action_information paa_pay
3140: ,pay_assignment_actions paa
3141: ,hr_organization_units hou
3142: WHERE paa_asg.action_information_category='IN_PF_ASG'
3135: ,paa_pay.action_information5 --Address
3136: ,paa_pay.action_information6 --Code
3137: ,paa_pay.action_information8 --PF Org Name
3138: FROM pay_action_information paa_asg
3139: ,pay_action_information paa_pay
3140: ,pay_assignment_actions paa
3141: ,hr_organization_units hou
3142: WHERE paa_asg.action_information_category='IN_PF_ASG'
3143: AND paa_pay.action_information_category='IN_PF_PAY'
3139: ,pay_action_information paa_pay
3140: ,pay_assignment_actions paa
3141: ,hr_organization_units hou
3142: WHERE paa_asg.action_information_category='IN_PF_ASG'
3143: AND paa_pay.action_information_category='IN_PF_PAY'
3144: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
3145: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
3146: AND paa.assignment_action_id=paa_asg.action_context_id
3147: AND paa.payroll_action_id=paa_pay.action_context_id
3145: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
3146: AND paa.assignment_action_id=paa_asg.action_context_id
3147: AND paa.payroll_action_id=paa_pay.action_context_id
3148: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3149: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,null,'UEX','%') --PF Org Class
3150: AND paa_asg.action_information2 = paa_pay.action_information2
3151: AND paa_pay.action_information7 NOT IN ('EXEM')
3152: AND paa_asg.action_information3 IS NOT NULL
3153: AND paa_asg.action_information1=p_contribution_period
3146: AND paa.assignment_action_id=paa_asg.action_context_id
3147: AND paa.payroll_action_id=paa_pay.action_context_id
3148: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3149: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,null,'UEX','%') --PF Org Class
3150: AND paa_asg.action_information2 = paa_pay.action_information2
3151: AND paa_pay.action_information7 NOT IN ('EXEM')
3152: AND paa_asg.action_information3 IS NOT NULL
3153: AND paa_asg.action_information1=p_contribution_period
3154: AND paa_pay.action_information1=p_contribution_period
3147: AND paa.payroll_action_id=paa_pay.action_context_id
3148: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3149: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,null,'UEX','%') --PF Org Class
3150: AND paa_asg.action_information2 = paa_pay.action_information2
3151: AND paa_pay.action_information7 NOT IN ('EXEM')
3152: AND paa_asg.action_information3 IS NOT NULL
3153: AND paa_asg.action_information1=p_contribution_period
3154: AND paa_pay.action_information1=p_contribution_period
3155: AND hou.organization_id=paa_pay.action_information2
3150: AND paa_asg.action_information2 = paa_pay.action_information2
3151: AND paa_pay.action_information7 NOT IN ('EXEM')
3152: AND paa_asg.action_information3 IS NOT NULL
3153: AND paa_asg.action_information1=p_contribution_period
3154: AND paa_pay.action_information1=p_contribution_period
3155: AND hou.organization_id=paa_pay.action_information2
3156: AND hou.organization_id=paa_asg.action_information2
3157: AND hou.business_group_id = fnd_profile.VALUE('PER_BUSINESS_GROUP_ID')
3158: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
3151: AND paa_pay.action_information7 NOT IN ('EXEM')
3152: AND paa_asg.action_information3 IS NOT NULL
3153: AND paa_asg.action_information1=p_contribution_period
3154: AND paa_pay.action_information1=p_contribution_period
3155: AND hou.organization_id=paa_pay.action_information2
3156: AND hou.organization_id=paa_asg.action_information2
3157: AND hou.business_group_id = fnd_profile.VALUE('PER_BUSINESS_GROUP_ID')
3158: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
3159: ORDER BY paa_pay.action_information8 ASC;
3155: AND hou.organization_id=paa_pay.action_information2
3156: AND hou.organization_id=paa_asg.action_information2
3157: AND hou.business_group_id = fnd_profile.VALUE('PER_BUSINESS_GROUP_ID')
3158: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
3159: ORDER BY paa_pay.action_information8 ASC;
3160:
3161: CURSOR c_assignment_id(p_pf_org_id VARCHAR2
3162: ,p_effective_start_date DATE
3163: ,p_effective_end_date DATE
3165: )
3166: IS
3167: SELECT DISTINCT action_information3
3168: ,substr(action_information3 ,instr(action_information3,'/',-1)+1 ) pf_acc
3169: FROM pay_action_information
3170: WHERE action_information_category='IN_PF_ASG'
3171: AND action_information2 =p_pf_org_id --PF Organization ID
3172: AND action_information1 =p_contribution_period
3173: AND action_information13 BETWEEN p_effective_start_date --Payroll Date
3184: SELECT SUM(fnd_number.canonical_to_number(action_information7)) pf_ytd --PF Salary
3185: ,SUM(fnd_number.canonical_to_number(action_information8)) employee --Total Employee Contr
3186: ,SUM(fnd_number.canonical_to_number(action_information9)) employer --Employer Contr towards PF
3187: ,SUM(fnd_number.canonical_to_number(action_information10)) pension --Employer Contr towards Pension
3188: FROM pay_action_information
3189: WHERE action_information2 = p_pf_org_id
3190: AND action_information3 = p_pf_number
3191: AND action_information1 = p_contribution_period
3192: AND action_information_id IN(
3190: AND action_information3 = p_pf_number
3191: AND action_information1 = p_contribution_period
3192: AND action_information_id IN(
3193: SELECT MAX(action_information_id)
3194: FROM pay_action_information
3195: WHERE action_information2 = p_pf_org_id
3196: AND action_information3 = p_pf_number
3197: AND action_information1 = p_contribution_period
3198: GROUP BY TO_DATE('01'||substr(action_information13,3),'DD-MM-YYYY'))
3205: ,p_effective_end_date DATE
3206: )
3207: IS
3208: SELECT DISTINCT TO_DATE(action_information13,'DD-MM-YYYY') mon
3209: FROM pay_action_information
3210: WHERE action_information_category='IN_PF_ASG'
3211: AND action_information3 =p_pf_number
3212: AND action_information2 =p_pf_org_id --PF Organization ID
3213: AND action_information1 = p_contribution_period -- Bug 5231500
3222: )
3223: IS
3224: SELECT action_information6 --Voluntary Contribution Rate
3225: ,TO_DATE(action_information13,'DD-MM-YYYY')
3226: FROM pay_action_information
3227: WHERE action_information_category='IN_PF_ASG'
3228: AND action_information3 = p_pf_number
3229: AND action_information2 = p_pf_org_id --PF Organization ID
3230: AND action_information1 = p_contribution_period -- Bug 5231500
3237: ,p_effective_end_date DATE
3238: )
3239: IS
3240: SELECT count(DISTINCT action_information3)--assignment_id)
3241: FROM pay_action_information
3242: WHERE action_information_category='IN_PF_ASG'
3243: AND to_number(action_information6)>0
3244: AND action_information13 BETWEEN p_effective_start_date
3245: AND p_effective_end_date
3276: )
3277: IS
3278: SELECT action_information4 --Full Name
3279: ,assignment_id
3280: FROM pay_action_information
3281: WHERE action_information_category='IN_PF_ASG'
3282: AND action_information2 =p_pf_org_id --PF Organization ID
3283: AND action_information3 =p_pf_number --PF Number
3284: AND action_information1 = p_contribution_period -- Bug 5231500
3291: ,p_effective_start_date DATE
3292: ,p_effective_end_date DATE
3293: )
3294: IS
3295: SELECT paa_pay.action_information4 rep_name
3296: FROM pay_action_information paa_asg
3297: ,pay_action_information paa_pay
3298: ,pay_assignment_actions paa
3299: WHERE paa_asg.action_information_category ='IN_PF_ASG'
3292: ,p_effective_end_date DATE
3293: )
3294: IS
3295: SELECT paa_pay.action_information4 rep_name
3296: FROM pay_action_information paa_asg
3297: ,pay_action_information paa_pay
3298: ,pay_assignment_actions paa
3299: WHERE paa_asg.action_information_category ='IN_PF_ASG'
3300: AND paa_pay.action_information_category ='IN_PF_PAY'
3293: )
3294: IS
3295: SELECT paa_pay.action_information4 rep_name
3296: FROM pay_action_information paa_asg
3297: ,pay_action_information paa_pay
3298: ,pay_assignment_actions paa
3299: WHERE paa_asg.action_information_category ='IN_PF_ASG'
3300: AND paa_pay.action_information_category ='IN_PF_PAY'
3301: AND paa_asg.ACTION_CONTEXT_TYPE ='AAP'
3296: FROM pay_action_information paa_asg
3297: ,pay_action_information paa_pay
3298: ,pay_assignment_actions paa
3299: WHERE paa_asg.action_information_category ='IN_PF_ASG'
3300: AND paa_pay.action_information_category ='IN_PF_PAY'
3301: AND paa_asg.ACTION_CONTEXT_TYPE ='AAP'
3302: AND paa_pay.ACTION_CONTEXT_TYPE ='PA'
3303: AND paa.assignment_action_id = paa_asg.action_context_id
3304: AND paa.payroll_action_id = paa_pay.action_context_id
3301: AND paa_asg.ACTION_CONTEXT_TYPE ='AAP'
3302: AND paa_pay.ACTION_CONTEXT_TYPE ='PA'
3303: AND paa.assignment_action_id = paa_asg.action_context_id
3304: AND paa.payroll_action_id = paa_pay.action_context_id
3305: AND paa_pay.action_information7 NOT IN ('EXEM')
3306: AND paa_asg.action_information3 IS NOT NULL
3307: AND paa_asg.action_information1 = p_contribution_period
3308: AND paa_pay.action_information1 = p_contribution_period
3309: AND paa_pay.action_information2 = p_pf_org_id
3304: AND paa.payroll_action_id = paa_pay.action_context_id
3305: AND paa_pay.action_information7 NOT IN ('EXEM')
3306: AND paa_asg.action_information3 IS NOT NULL
3307: AND paa_asg.action_information1 = p_contribution_period
3308: AND paa_pay.action_information1 = p_contribution_period
3309: AND paa_pay.action_information2 = p_pf_org_id
3310: AND paa_asg.action_information2 = p_pf_org_id
3311: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
3312: ORDER BY TO_DATE(paa_asg.action_information13,'DD-MM-YYYY') DESC;
3305: AND paa_pay.action_information7 NOT IN ('EXEM')
3306: AND paa_asg.action_information3 IS NOT NULL
3307: AND paa_asg.action_information1 = p_contribution_period
3308: AND paa_pay.action_information1 = p_contribution_period
3309: AND paa_pay.action_information2 = p_pf_org_id
3310: AND paa_asg.action_information2 = p_pf_org_id
3311: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
3312: ORDER BY TO_DATE(paa_asg.action_information13,'DD-MM-YYYY') DESC;
3313:
3451: IF c_assignment_id%FOUND THEN
3452: OPEN c_asg_id FOR
3453: SELECT DISTINCT action_information3
3454: ,substr(action_information3 ,instr(action_information3,'/',-1)+1 ) pf_acc
3455: FROM pay_action_information
3456: WHERE action_information_category='IN_PF_ASG'
3457: AND action_information2 = c_rec.action_information2 --PF Organization ID
3458: AND action_information1 = p_contribution_period
3459: AND action_information13 BETWEEN p_effective_start_date --Payroll Date
3465: EXCEPTION WHEN INVALID_NUMBER THEN
3466: OPEN c_asg_id FOR
3467: SELECT DISTINCT action_information3
3468: ,substr(action_information3 ,instr(action_information3,'/',-1)+1 ) pf_acc
3469: FROM pay_action_information
3470: WHERE action_information_category='IN_PF_ASG'
3471: AND action_information2 = c_rec.action_information2 --PF Organization ID
3472: AND action_information1 = p_contribution_period
3473: AND action_information13 BETWEEN p_effective_start_date --Payroll Date
3712: pay_in_utils.set_location(g_debug,l_procedure, 230);
3713:
3714: SELECT COUNT(DISTINCT action_information3)
3715: INTO pay_in_xml_utils.gXMLTable(l_count).Value
3716: FROM pay_action_information
3717: WHERE action_information_category='IN_PF_ASG'
3718: AND action_information1 = p_contribution_period -- Bug 5231500
3719: AND action_information2 = c_rec.action_information2 --PF Organization ID
3720: AND action_information3 IS NOT NULL
3775: ,p_employee_type VARCHAR2
3776: ,p_effective_start_date DATE
3777: ,p_effective_end_date DATE)
3778: IS
3779: TYPE c_rec IS RECORD (pf_org pay_action_information.action_information14%TYPE,
3780: pf_num pay_action_information.action_information3%TYPE,
3781: pf_org_id pay_action_information.action_information2%TYPE,
3782: pf_acc pay_action_information.action_information3%TYPE );
3783:
3776: ,p_effective_start_date DATE
3777: ,p_effective_end_date DATE)
3778: IS
3779: TYPE c_rec IS RECORD (pf_org pay_action_information.action_information14%TYPE,
3780: pf_num pay_action_information.action_information3%TYPE,
3781: pf_org_id pay_action_information.action_information2%TYPE,
3782: pf_acc pay_action_information.action_information3%TYPE );
3783:
3784: TYPE c_org_id_ref IS REF CURSOR RETURN c_rec;
3777: ,p_effective_end_date DATE)
3778: IS
3779: TYPE c_rec IS RECORD (pf_org pay_action_information.action_information14%TYPE,
3780: pf_num pay_action_information.action_information3%TYPE,
3781: pf_org_id pay_action_information.action_information2%TYPE,
3782: pf_acc pay_action_information.action_information3%TYPE );
3783:
3784: TYPE c_org_id_ref IS REF CURSOR RETURN c_rec;
3785: c_org_id c_org_id_ref;
3778: IS
3779: TYPE c_rec IS RECORD (pf_org pay_action_information.action_information14%TYPE,
3780: pf_num pay_action_information.action_information3%TYPE,
3781: pf_org_id pay_action_information.action_information2%TYPE,
3782: pf_acc pay_action_information.action_information3%TYPE );
3783:
3784: TYPE c_org_id_ref IS REF CURSOR RETURN c_rec;
3785: c_org_id c_org_id_ref;
3786: c_org c_rec;
3796: SELECT DISTINCT paa_asg.action_information14 --PF Org Name
3797: ,paa_asg.action_information3 pf_num --PF Number
3798: ,paa_asg.action_information2 pf_org_id --PF Org ID
3799: ,substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 ) pf_acc
3800: FROM pay_action_information paa_asg
3801: ,pay_action_information paa_pay
3802: ,pay_assignment_actions paa
3803: ,hr_organization_units hou
3804: WHERE paa_asg.action_information_category='IN_PF_ASG'
3797: ,paa_asg.action_information3 pf_num --PF Number
3798: ,paa_asg.action_information2 pf_org_id --PF Org ID
3799: ,substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 ) pf_acc
3800: FROM pay_action_information paa_asg
3801: ,pay_action_information paa_pay
3802: ,pay_assignment_actions paa
3803: ,hr_organization_units hou
3804: WHERE paa_asg.action_information_category='IN_PF_ASG'
3805: AND paa_pay.action_information_category='IN_PF_PAY'
3801: ,pay_action_information paa_pay
3802: ,pay_assignment_actions paa
3803: ,hr_organization_units hou
3804: WHERE paa_asg.action_information_category='IN_PF_ASG'
3805: AND paa_pay.action_information_category='IN_PF_PAY'
3806: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
3807: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
3808: AND paa.assignment_action_id=paa_asg.action_context_id
3809: AND paa.payroll_action_id=paa_pay.action_context_id
3806: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
3807: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
3808: AND paa.assignment_action_id=paa_asg.action_context_id
3809: AND paa.payroll_action_id=paa_pay.action_context_id
3810: AND paa_pay.action_information7 NOT IN ('EXEM')
3811: AND paa_asg.action_information3 IS NOT NULL
3812: AND paa_asg.action_information1=p_contribution_period
3813: AND paa_pay.action_information1=p_contribution_period
3814: AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3809: AND paa.payroll_action_id=paa_pay.action_context_id
3810: AND paa_pay.action_information7 NOT IN ('EXEM')
3811: AND paa_asg.action_information3 IS NOT NULL
3812: AND paa_asg.action_information1=p_contribution_period
3813: AND paa_pay.action_information1=p_contribution_period
3814: AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3815: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3816: AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
3817: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
3810: AND paa_pay.action_information7 NOT IN ('EXEM')
3811: AND paa_asg.action_information3 IS NOT NULL
3812: AND paa_asg.action_information1=p_contribution_period
3813: AND paa_pay.action_information1=p_contribution_period
3814: AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3815: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3816: AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
3817: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
3818: AND paa_asg.action_information2 = paa_pay.action_information2
3813: AND paa_pay.action_information1=p_contribution_period
3814: AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3815: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3816: AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
3817: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
3818: AND paa_asg.action_information2 = paa_pay.action_information2
3819: AND hou.organization_id=paa_pay.action_information2
3820: AND hou.organization_id=paa_asg.action_information2
3821: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
3814: AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3815: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3816: AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
3817: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
3818: AND paa_asg.action_information2 = paa_pay.action_information2
3819: AND hou.organization_id=paa_pay.action_information2
3820: AND hou.organization_id=paa_asg.action_information2
3821: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
3822: ORDER BY paa_asg.action_information14,to_number(pf_acc) ASC;
3815: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3816: AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
3817: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
3818: AND paa_asg.action_information2 = paa_pay.action_information2
3819: AND hou.organization_id=paa_pay.action_information2
3820: AND hou.organization_id=paa_asg.action_information2
3821: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
3822: ORDER BY paa_asg.action_information14,to_number(pf_acc) ASC;
3823:
3830: ,p_cp_pf_org_id VARCHAR2
3831: ,p_status VARCHAR2
3832: )
3833: IS
3834: SELECT DISTINCT paa_pay.action_information5 --Address
3835: ,paa_pay.action_information3 reg --Registered Name
3836: FROM pay_action_information paa_asg
3837: ,pay_action_information paa_pay
3838: ,pay_assignment_actions paa
3831: ,p_status VARCHAR2
3832: )
3833: IS
3834: SELECT DISTINCT paa_pay.action_information5 --Address
3835: ,paa_pay.action_information3 reg --Registered Name
3836: FROM pay_action_information paa_asg
3837: ,pay_action_information paa_pay
3838: ,pay_assignment_actions paa
3839: WHERE paa_asg.action_information_category='IN_PF_ASG'
3832: )
3833: IS
3834: SELECT DISTINCT paa_pay.action_information5 --Address
3835: ,paa_pay.action_information3 reg --Registered Name
3836: FROM pay_action_information paa_asg
3837: ,pay_action_information paa_pay
3838: ,pay_assignment_actions paa
3839: WHERE paa_asg.action_information_category='IN_PF_ASG'
3840: AND paa_pay.action_information_category='IN_PF_PAY'
3833: IS
3834: SELECT DISTINCT paa_pay.action_information5 --Address
3835: ,paa_pay.action_information3 reg --Registered Name
3836: FROM pay_action_information paa_asg
3837: ,pay_action_information paa_pay
3838: ,pay_assignment_actions paa
3839: WHERE paa_asg.action_information_category='IN_PF_ASG'
3840: AND paa_pay.action_information_category='IN_PF_PAY'
3841: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
3836: FROM pay_action_information paa_asg
3837: ,pay_action_information paa_pay
3838: ,pay_assignment_actions paa
3839: WHERE paa_asg.action_information_category='IN_PF_ASG'
3840: AND paa_pay.action_information_category='IN_PF_PAY'
3841: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
3842: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
3843: AND paa.assignment_action_id=paa_asg.action_context_id
3844: AND paa.payroll_action_id=paa_pay.action_context_id
3841: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
3842: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
3843: AND paa.assignment_action_id=paa_asg.action_context_id
3844: AND paa.payroll_action_id=paa_pay.action_context_id
3845: AND paa_pay.action_information7 NOT IN ('EXEM')
3846: AND paa_asg.action_information3 IS NOT NULL
3847: AND paa_asg.action_information1 = p_contribution_period
3848: AND paa_pay.action_information1 = p_contribution_period
3849: AND paa_pay.action_information2 = p_pf_org_id --PF Organization ID
3844: AND paa.payroll_action_id=paa_pay.action_context_id
3845: AND paa_pay.action_information7 NOT IN ('EXEM')
3846: AND paa_asg.action_information3 IS NOT NULL
3847: AND paa_asg.action_information1 = p_contribution_period
3848: AND paa_pay.action_information1 = p_contribution_period
3849: AND paa_pay.action_information2 = p_pf_org_id --PF Organization ID
3850: AND paa_asg.action_information2 = p_pf_org_id --PF Organization ID
3851: AND paa_asg.action_information3 = p_pf_number --PF Number
3852: AND paa_asg.action_information2 LIKE DECODE(p_employee_type,'CURRENT',nvl(p_cp_pf_org_id,'%'),'%')
3845: AND paa_pay.action_information7 NOT IN ('EXEM')
3846: AND paa_asg.action_information3 IS NOT NULL
3847: AND paa_asg.action_information1 = p_contribution_period
3848: AND paa_pay.action_information1 = p_contribution_period
3849: AND paa_pay.action_information2 = p_pf_org_id --PF Organization ID
3850: AND paa_asg.action_information2 = p_pf_org_id --PF Organization ID
3851: AND paa_asg.action_information3 = p_pf_number --PF Number
3852: AND paa_asg.action_information2 LIKE DECODE(p_employee_type,'CURRENT',nvl(p_cp_pf_org_id,'%'),'%')
3853: AND paa_pay.action_information2 LIKE DECODE(p_employee_type,'CURRENT',nvl(p_cp_pf_org_id,'%'),'%')
3849: AND paa_pay.action_information2 = p_pf_org_id --PF Organization ID
3850: AND paa_asg.action_information2 = p_pf_org_id --PF Organization ID
3851: AND paa_asg.action_information3 = p_pf_number --PF Number
3852: AND paa_asg.action_information2 LIKE DECODE(p_employee_type,'CURRENT',nvl(p_cp_pf_org_id,'%'),'%')
3853: AND paa_pay.action_information2 LIKE DECODE(p_employee_type,'CURRENT',nvl(p_cp_pf_org_id,'%'),'%')
3854: AND nvl(p_status,-1) LIKE DECODE(p_employee_type,'CURRENT','CURRENT','TRANSFERRED','TRANSFERRED',-1)
3855: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date;
3856:
3857: CURSOR c_name_fath_hus_name(p_pf_org_id VARCHAR2
3864: SELECT action_information4 --Full Name
3865: ,action_information5 --Father/Husband Name
3866: ,action_information13 --Payroll Date
3867: ,action_information6 --Voluntary Contribution Rate
3868: FROM pay_action_information
3869: WHERE action_information_category='IN_PF_ASG'
3870: AND action_information1 =p_contribution_period --PF Contribution Period
3871: AND action_information2 =p_pf_org_id --PF Organization ID
3872: AND action_information3 =p_pf_number --PF Number
3887: ,paa_asg.action_information9 --Employer Contr towards PF
3888: ,paa_asg.action_information10 --Employer Contr towards Pension
3889: ,paa_asg.action_information11 --Absence
3890: -- ,paa_asg.action_information12 --Remarks
3891: ,paa_pay.action_information4 --PF Rep Name
3892: ,paa_asg.action_information13 --Payroll Month
3893: ,paa_asg.assignment_id --Assignment ID
3894: FROM pay_action_information paa_asg
3895: ,pay_action_information paa_pay
3890: -- ,paa_asg.action_information12 --Remarks
3891: ,paa_pay.action_information4 --PF Rep Name
3892: ,paa_asg.action_information13 --Payroll Month
3893: ,paa_asg.assignment_id --Assignment ID
3894: FROM pay_action_information paa_asg
3895: ,pay_action_information paa_pay
3896: ,pay_assignment_actions paa
3897: WHERE paa_asg.action_information_category='IN_PF_ASG'
3898: AND paa_pay.action_information_category='IN_PF_PAY'
3891: ,paa_pay.action_information4 --PF Rep Name
3892: ,paa_asg.action_information13 --Payroll Month
3893: ,paa_asg.assignment_id --Assignment ID
3894: FROM pay_action_information paa_asg
3895: ,pay_action_information paa_pay
3896: ,pay_assignment_actions paa
3897: WHERE paa_asg.action_information_category='IN_PF_ASG'
3898: AND paa_pay.action_information_category='IN_PF_PAY'
3899: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
3894: FROM pay_action_information paa_asg
3895: ,pay_action_information paa_pay
3896: ,pay_assignment_actions paa
3897: WHERE paa_asg.action_information_category='IN_PF_ASG'
3898: AND paa_pay.action_information_category='IN_PF_PAY'
3899: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
3900: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
3901: AND paa.assignment_action_id=paa_asg.action_context_id
3902: AND paa.payroll_action_id=paa_pay.action_context_id
3899: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
3900: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
3901: AND paa.assignment_action_id=paa_asg.action_context_id
3902: AND paa.payroll_action_id=paa_pay.action_context_id
3903: AND paa_pay.action_information7 NOT IN ('EXEM')
3904: AND paa_asg.action_information3 IS NOT NULL
3905: AND paa_asg.action_information1=p_contribution_period
3906: AND paa_pay.action_information1=p_contribution_period
3907: AND paa_asg.action_information2=p_pf_org_id
3902: AND paa.payroll_action_id=paa_pay.action_context_id
3903: AND paa_pay.action_information7 NOT IN ('EXEM')
3904: AND paa_asg.action_information3 IS NOT NULL
3905: AND paa_asg.action_information1=p_contribution_period
3906: AND paa_pay.action_information1=p_contribution_period
3907: AND paa_asg.action_information2=p_pf_org_id
3908: AND paa_asg.action_information3=p_pf_number
3909: AND paa_pay.action_information2=p_pf_org_id
3910: AND to_number(to_char(to_date(paa_asg.action_information13,'DD-MM-YY'),'MM'))=p_mon_number
3905: AND paa_asg.action_information1=p_contribution_period
3906: AND paa_pay.action_information1=p_contribution_period
3907: AND paa_asg.action_information2=p_pf_org_id
3908: AND paa_asg.action_information3=p_pf_number
3909: AND paa_pay.action_information2=p_pf_org_id
3910: AND to_number(to_char(to_date(paa_asg.action_information13,'DD-MM-YY'),'MM'))=p_mon_number
3911: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
3912: ORDER BY to_date(paa_asg.action_information13,'DD-MM-YYYY'), fnd_number.canonical_to_number(paa_asg.action_information7) ASC;
3913:
3922: ,SUM(fnd_number.canonical_to_number(action_information8)) employee --Total Employee Contr
3923: ,SUM(fnd_number.canonical_to_number(action_information9)) employer --Employer Contr towards PF
3924: ,SUM(fnd_number.canonical_to_number(action_information10)) pension --Employer Contr towards Pension
3925: ,SUM(fnd_number.canonical_to_number(action_information8) + fnd_number.canonical_to_number(action_information9)) total --Total Employee Employer Contr
3926: FROM pay_action_information
3927: WHERE action_information2 = p_pf_org_id
3928: AND action_information3 = p_pf_number
3929: AND action_information1 = p_contribution_period
3930: AND action_information_id IN(
3928: AND action_information3 = p_pf_number
3929: AND action_information1 = p_contribution_period
3930: AND action_information_id IN(
3931: SELECT MAX(action_information_id)
3932: FROM pay_action_information
3933: WHERE action_information2 = p_pf_org_id
3934: AND action_information3 = p_pf_number
3935: AND action_information1 = p_contribution_period
3936: GROUP BY to_date('01'||substr(action_information13,3),'DD-MM-YYYY'))
4004: SELECT DISTINCT paa_asg.action_information14 pf_org --PF Org Name
4005: ,paa_asg.action_information3 pf_num --PF Number
4006: ,paa_asg.action_information2 pf_org_id --PF Org ID
4007: ,substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 ) pf_acc
4008: FROM pay_action_information paa_asg
4009: ,pay_action_information paa_pay
4010: ,pay_assignment_actions paa
4011: ,hr_organization_units hou
4012: WHERE paa_asg.action_information_category='IN_PF_ASG'
4005: ,paa_asg.action_information3 pf_num --PF Number
4006: ,paa_asg.action_information2 pf_org_id --PF Org ID
4007: ,substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 ) pf_acc
4008: FROM pay_action_information paa_asg
4009: ,pay_action_information paa_pay
4010: ,pay_assignment_actions paa
4011: ,hr_organization_units hou
4012: WHERE paa_asg.action_information_category='IN_PF_ASG'
4013: AND paa_pay.action_information_category='IN_PF_PAY'
4009: ,pay_action_information paa_pay
4010: ,pay_assignment_actions paa
4011: ,hr_organization_units hou
4012: WHERE paa_asg.action_information_category='IN_PF_ASG'
4013: AND paa_pay.action_information_category='IN_PF_PAY'
4014: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
4015: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
4016: AND paa.assignment_action_id=paa_asg.action_context_id
4017: AND paa.payroll_action_id=paa_pay.action_context_id
4014: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
4015: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
4016: AND paa.assignment_action_id=paa_asg.action_context_id
4017: AND paa.payroll_action_id=paa_pay.action_context_id
4018: AND paa_pay.action_information7 NOT IN ('EXEM')
4019: AND paa_asg.action_information3 IS NOT NULL
4020: AND paa_asg.action_information1=p_contribution_period
4021: AND paa_pay.action_information1=p_contribution_period
4022: AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
4017: AND paa.payroll_action_id=paa_pay.action_context_id
4018: AND paa_pay.action_information7 NOT IN ('EXEM')
4019: AND paa_asg.action_information3 IS NOT NULL
4020: AND paa_asg.action_information1=p_contribution_period
4021: AND paa_pay.action_information1=p_contribution_period
4022: AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
4023: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
4024: AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
4025: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
4018: AND paa_pay.action_information7 NOT IN ('EXEM')
4019: AND paa_asg.action_information3 IS NOT NULL
4020: AND paa_asg.action_information1=p_contribution_period
4021: AND paa_pay.action_information1=p_contribution_period
4022: AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
4023: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
4024: AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
4025: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
4026: AND paa_asg.action_information2 = paa_pay.action_information2
4021: AND paa_pay.action_information1=p_contribution_period
4022: AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
4023: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
4024: AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
4025: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
4026: AND paa_asg.action_information2 = paa_pay.action_information2
4027: AND hou.organization_id=paa_pay.action_information2
4028: AND hou.organization_id=paa_asg.action_information2
4029: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
4022: AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
4023: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
4024: AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
4025: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
4026: AND paa_asg.action_information2 = paa_pay.action_information2
4027: AND hou.organization_id=paa_pay.action_information2
4028: AND hou.organization_id=paa_asg.action_information2
4029: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
4030: ORDER BY paa_asg.action_information14,to_number(pf_acc) asc;
4023: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
4024: AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
4025: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
4026: AND paa_asg.action_information2 = paa_pay.action_information2
4027: AND hou.organization_id=paa_pay.action_information2
4028: AND hou.organization_id=paa_asg.action_information2
4029: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
4030: ORDER BY paa_asg.action_information14,to_number(pf_acc) asc;
4031: END IF;
4035: SELECT DISTINCT paa_asg.action_information14 pf_org --PF Org Name
4036: ,paa_asg.action_information3 pf_num --PF Number
4037: ,paa_asg.action_information2 pf_org_id --PF Org ID
4038: ,substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 ) pf_acc
4039: FROM pay_action_information paa_asg
4040: ,pay_action_information paa_pay
4041: ,pay_assignment_actions paa
4042: ,hr_organization_units hou
4043: WHERE paa_asg.action_information_category='IN_PF_ASG'
4036: ,paa_asg.action_information3 pf_num --PF Number
4037: ,paa_asg.action_information2 pf_org_id --PF Org ID
4038: ,substr(paa_asg.action_information3 ,instr(paa_asg.action_information3,'/',-1)+1 ) pf_acc
4039: FROM pay_action_information paa_asg
4040: ,pay_action_information paa_pay
4041: ,pay_assignment_actions paa
4042: ,hr_organization_units hou
4043: WHERE paa_asg.action_information_category='IN_PF_ASG'
4044: AND paa_pay.action_information_category='IN_PF_PAY'
4040: ,pay_action_information paa_pay
4041: ,pay_assignment_actions paa
4042: ,hr_organization_units hou
4043: WHERE paa_asg.action_information_category='IN_PF_ASG'
4044: AND paa_pay.action_information_category='IN_PF_PAY'
4045: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
4046: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
4047: AND paa.assignment_action_id=paa_asg.action_context_id
4048: AND paa.payroll_action_id=paa_pay.action_context_id
4045: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
4046: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
4047: AND paa.assignment_action_id=paa_asg.action_context_id
4048: AND paa.payroll_action_id=paa_pay.action_context_id
4049: AND paa_pay.action_information7 NOT IN ('EXEM')
4050: AND paa_asg.action_information3 IS NOT NULL
4051: AND paa_asg.action_information1=p_contribution_period
4052: AND paa_pay.action_information1=p_contribution_period
4053: AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
4048: AND paa.payroll_action_id=paa_pay.action_context_id
4049: AND paa_pay.action_information7 NOT IN ('EXEM')
4050: AND paa_asg.action_information3 IS NOT NULL
4051: AND paa_asg.action_information1=p_contribution_period
4052: AND paa_pay.action_information1=p_contribution_period
4053: AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
4054: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
4055: AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
4056: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
4049: AND paa_pay.action_information7 NOT IN ('EXEM')
4050: AND paa_asg.action_information3 IS NOT NULL
4051: AND paa_asg.action_information1=p_contribution_period
4052: AND paa_pay.action_information1=p_contribution_period
4053: AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
4054: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
4055: AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
4056: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
4057: AND paa_asg.action_information2 = paa_pay.action_information2
4052: AND paa_pay.action_information1=p_contribution_period
4053: AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
4054: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
4055: AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
4056: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
4057: AND paa_asg.action_information2 = paa_pay.action_information2
4058: AND hou.organization_id=paa_pay.action_information2
4059: AND hou.organization_id=paa_asg.action_information2
4060: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
4053: AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
4054: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
4055: AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
4056: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
4057: AND paa_asg.action_information2 = paa_pay.action_information2
4058: AND hou.organization_id=paa_pay.action_information2
4059: AND hou.organization_id=paa_asg.action_information2
4060: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
4061: ORDER BY paa_asg.action_information14,pf_num asc;
4054: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
4055: AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
4056: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
4057: AND paa_asg.action_information2 = paa_pay.action_information2
4058: AND hou.organization_id=paa_pay.action_information2
4059: AND hou.organization_id=paa_asg.action_information2
4060: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
4061: ORDER BY paa_asg.action_information14,pf_num asc;
4062: CLOSE c_distinct_org_id;
4356: AND hoi.org_information_context ='PER_IN_PF_DF'
4357: AND hou.organization_id like nvl(p_pf_org_id,'%')
4358: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
4359: AND EXISTS (SELECT 1
4360: FROM pay_action_information pai
4361: WHERE pai.action_information_category ='IN_PF_PAY'
4362: AND pai.action_information1 = p_contribution_period --Contribution period
4363: AND pai.action_information2 = hou.organization_id -- Org ID
4364: AND pai.action_information7 = 'EXEM' -- PF Org Classification
4367:
4368: /* This finds the latest PF Organization data,in the contribution period from the archive table.All org level details will be found here .This will fire once foR EACH row returned by csr_exempted_pf_orglist*/
4369: CURSOR csr_pf_org_details (p_pf_org_id number)
4370: IS
4371: SELECT paa_pay.action_information3 Registered_Name
4372: ,paa_pay.action_information4 Rep_name
4373: ,paa_pay.action_information5 Address
4374: ,paa_pay.action_information6 Code
4375: FROM pay_action_information paa_asg
4368: /* This finds the latest PF Organization data,in the contribution period from the archive table.All org level details will be found here .This will fire once foR EACH row returned by csr_exempted_pf_orglist*/
4369: CURSOR csr_pf_org_details (p_pf_org_id number)
4370: IS
4371: SELECT paa_pay.action_information3 Registered_Name
4372: ,paa_pay.action_information4 Rep_name
4373: ,paa_pay.action_information5 Address
4374: ,paa_pay.action_information6 Code
4375: FROM pay_action_information paa_asg
4376: ,pay_action_information paa_pay
4369: CURSOR csr_pf_org_details (p_pf_org_id number)
4370: IS
4371: SELECT paa_pay.action_information3 Registered_Name
4372: ,paa_pay.action_information4 Rep_name
4373: ,paa_pay.action_information5 Address
4374: ,paa_pay.action_information6 Code
4375: FROM pay_action_information paa_asg
4376: ,pay_action_information paa_pay
4377: ,pay_assignment_actions paa
4370: IS
4371: SELECT paa_pay.action_information3 Registered_Name
4372: ,paa_pay.action_information4 Rep_name
4373: ,paa_pay.action_information5 Address
4374: ,paa_pay.action_information6 Code
4375: FROM pay_action_information paa_asg
4376: ,pay_action_information paa_pay
4377: ,pay_assignment_actions paa
4378: WHERE paa_asg.action_information_category='IN_PF_ASG'
4371: SELECT paa_pay.action_information3 Registered_Name
4372: ,paa_pay.action_information4 Rep_name
4373: ,paa_pay.action_information5 Address
4374: ,paa_pay.action_information6 Code
4375: FROM pay_action_information paa_asg
4376: ,pay_action_information paa_pay
4377: ,pay_assignment_actions paa
4378: WHERE paa_asg.action_information_category='IN_PF_ASG'
4379: AND paa_pay.action_information_category='IN_PF_PAY'
4372: ,paa_pay.action_information4 Rep_name
4373: ,paa_pay.action_information5 Address
4374: ,paa_pay.action_information6 Code
4375: FROM pay_action_information paa_asg
4376: ,pay_action_information paa_pay
4377: ,pay_assignment_actions paa
4378: WHERE paa_asg.action_information_category='IN_PF_ASG'
4379: AND paa_pay.action_information_category='IN_PF_PAY'
4380: AND paa_asg.action_context_type='AAP'
4375: FROM pay_action_information paa_asg
4376: ,pay_action_information paa_pay
4377: ,pay_assignment_actions paa
4378: WHERE paa_asg.action_information_category='IN_PF_ASG'
4379: AND paa_pay.action_information_category='IN_PF_PAY'
4380: AND paa_asg.action_context_type='AAP'
4381: AND paa_pay.action_context_type='PA'
4382: AND paa.assignment_action_id=paa_asg.action_context_id
4383: AND paa.payroll_action_id=paa_pay.action_context_id
4380: AND paa_asg.action_context_type='AAP'
4381: AND paa_pay.action_context_type='PA'
4382: AND paa.assignment_action_id=paa_asg.action_context_id
4383: AND paa.payroll_action_id=paa_pay.action_context_id
4384: AND paa_pay.action_information7 = 'EXEM'
4385: AND paa_asg.action_information1=p_contribution_period
4386: AND paa_pay.action_information1=p_contribution_period
4387: AND paa_pay.action_information2=p_pf_org_id
4388: AND paa_asg.action_information2=p_pf_org_id
4382: AND paa.assignment_action_id=paa_asg.action_context_id
4383: AND paa.payroll_action_id=paa_pay.action_context_id
4384: AND paa_pay.action_information7 = 'EXEM'
4385: AND paa_asg.action_information1=p_contribution_period
4386: AND paa_pay.action_information1=p_contribution_period
4387: AND paa_pay.action_information2=p_pf_org_id
4388: AND paa_asg.action_information2=p_pf_org_id
4389: AND paa_asg.action_information1=paa_pay.action_information1
4390: AND paa_asg.action_information2=paa_pay.action_information2
4383: AND paa.payroll_action_id=paa_pay.action_context_id
4384: AND paa_pay.action_information7 = 'EXEM'
4385: AND paa_asg.action_information1=p_contribution_period
4386: AND paa_pay.action_information1=p_contribution_period
4387: AND paa_pay.action_information2=p_pf_org_id
4388: AND paa_asg.action_information2=p_pf_org_id
4389: AND paa_asg.action_information1=paa_pay.action_information1
4390: AND paa_asg.action_information2=paa_pay.action_information2
4391: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
4385: AND paa_asg.action_information1=p_contribution_period
4386: AND paa_pay.action_information1=p_contribution_period
4387: AND paa_pay.action_information2=p_pf_org_id
4388: AND paa_asg.action_information2=p_pf_org_id
4389: AND paa_asg.action_information1=paa_pay.action_information1
4390: AND paa_asg.action_information2=paa_pay.action_information2
4391: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
4392: ORDER BY TO_DATE(paa_asg.action_information13,'DD-MM-YYYY') DESC;
4393:
4386: AND paa_pay.action_information1=p_contribution_period
4387: AND paa_pay.action_information2=p_pf_org_id
4388: AND paa_asg.action_information2=p_pf_org_id
4389: AND paa_asg.action_information1=paa_pay.action_information1
4390: AND paa_asg.action_information2=paa_pay.action_information2
4391: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
4392: ORDER BY TO_DATE(paa_asg.action_information13,'DD-MM-YYYY') DESC;
4393:
4394:
4396: /* Find the distinct pension number,assignment id combination for the given PF Org in the contribution period.Report will be generated for each record returned by the cursor. Rehire cases will not be handled */
4397: CURSOR csr_pension_number(l_pf_org_id NUMBER)
4398: IS
4399: SELECT DISTINCT pai.action_information15 pension_number
4400: FROM pay_action_information pai
4401: WHERE pai.action_information_category ='IN_PF_ASG'
4402: AND pai.action_information1 = p_contribution_period
4403: AND pai.action_information2 = l_pf_org_id
4404: AND pai.action_information15 IS NOT NULL
4411: ,p_contribution_period VARCHAR2
4412: ,p_pension_number VARCHAR2)
4413: IS
4414: SELECT 'X' status
4415: FROM pay_action_information pai
4416: WHERE pai.action_information_category ='IN_PF_ASG'
4417: AND pai.action_information1=p_contribution_period
4418: AND pai.action_information2 = p_pf_org_id
4419: AND pai.action_information15 = p_pension_number
4430: IS
4431: SELECT pai.action_information4 Emp_name
4432: ,pai.action_information5 Fath_husb_name
4433: ,pai.action_information16 Hire_date
4434: FROM pay_action_information pai
4435: WHERE pai.action_information_category ='IN_PF_ASG'
4436: AND pai.action_information1 = p_contribution_period
4437: AND pai.action_information2 = p_pf_org_id
4438: AND pai.action_information15 = l_pension_number
4451: ,pai.action_information17 Contribution_sal
4452: ,pai.action_information18 Excluded_employee_status
4453: ,pai.action_information_id action_information_id
4454: ,pai.assignment_id assignment_id
4455: FROM pay_action_information pai
4456: WHERE pai.action_information_category ='IN_PF_ASG'
4457: AND pai.action_information1 = p_contribution_period
4458: AND pai.action_information2 = p_pf_org_id
4459: AND pai.action_information15 = p_pension_number
4466: ,pai.action_information18
4467: ,pai.action_information_id
4468: ,pai.assignment_id
4469: HAVING pai.action_information_id = (SELECT MAX(pai1.action_information_id)
4470: FROM pay_Action_information pai1
4471: WHERE pai1.action_information_category ='IN_PF_ASG'
4472: AND pai1.action_information1 = p_contribution_period
4473: AND pai1.action_information2 = p_pf_org_id
4474: AND TRUNC(TO_DATE(pai.action_information13,'DD-MM-YY'),'MM') = TRUNC(TO_DATE(pai1.action_information13,'DD-MM-YY'),'MM')
4490:
4491:
4492: l_reg_name hr_organization_information.org_information1%TYPE;
4493: l_rep_name per_all_people_f.full_name%TYPE;
4494: l_org_address pay_action_information.action_information1%TYPE;
4495: --In the Rarest case of location adddress exceeding 240 characters nothing can be done
4496: l_est_code hr_organization_information.org_information1%TYPE;
4497:
4498: g_org_XMLTable pay_in_xml_utils.tXMLTable;
4876: IS
4877: CURSOR c_transfer_check
4878: IS
4879: SELECT action_information2
4880: FROM pay_action_information paa
4881: WHERE paa.action_information_category='IN_ESI_ASG'
4882: AND paa.action_context_type='AAP'
4883: AND paa.action_information1=p_contribution_period
4884: AND paa.action_information3=p_insurance_no
4886:
4887: CURSOR c_transfer_status(p_esi_org_id VARCHAR2)
4888: IS
4889: SELECT 1
4890: FROM pay_action_information paa
4891: WHERE paa.action_information_category='IN_ESI_ASG'
4892: AND paa.action_context_type='AAP'
4893: AND paa.action_information1=p_contribution_period
4894: AND paa.action_information3=p_insurance_no
4895: and paa.action_information2=p_esi_org_id
4896: and to_date(paa.action_information11,'DD-MM-YY')=
4897: (
4898: SELECT MAX(to_date(action_information11,'DD-MM-YY'))
4899: FROM pay_action_information paa
4900: WHERE paa.action_information_category='IN_ESI_ASG'
4901: AND paa.action_context_type='AAP'
4902: AND paa.action_information1=p_contribution_period
4903: AND paa.action_information3=p_insurance_no
4905:
4906: CURSOR c_last_pay_count
4907: IS
4908: SELECT count(action_information2)
4909: FROM pay_action_information
4910: WHERE action_information_category ='IN_ESI_ASG'
4911: AND action_information3=p_insurance_no
4912: AND action_context_type='AAP'
4913: AND action_information1=p_contribution_period
4913: AND action_information1=p_contribution_period
4914: AND to_date(action_information11,'DD-MM-YY')=
4915: (
4916: SELECT MAX(to_date(action_information11,'DD-MM-YY'))
4917: FROM pay_action_information paa
4918: WHERE paa.action_information_category='IN_ESI_ASG'
4919: AND paa.action_context_type='AAP'
4920: AND paa.action_information1=p_contribution_period
4921: AND paa.action_information3=p_insurance_no
4923:
4924: CURSOR c_last_pay_date
4925: IS
4926: SELECT MAX(to_date(action_information11,'DD-MM-YY'))
4927: FROM pay_action_information paa
4928: WHERE paa.action_information_category='IN_ESI_ASG'
4929: AND paa.action_context_type='AAP'
4930: AND paa.action_information1=p_contribution_period
4931: AND paa.action_information3=p_insurance_no;
4982: --Find Exemption status
4983: CURSOR c_esi_exemption(p_payroll_date DATE)
4984: IS
4985: SELECT SUBSTR(action_information9,1,1)
4986: FROM pay_action_information
4987: WHERE action_information_category='IN_ESI_ASG'
4988: AND action_information3=p_insurance_no
4989: AND action_information2=p_org_id
4990: AND action_information1=p_contribution_period -- Bug 5231500
5252: CURSOR c_distinct_org
5253: IS
5254: SELECT DISTINCT action_information2 org_id
5255: ,action_information8 org_name
5256: FROM pay_action_information
5257: ,hr_organization_units hou
5258: WHERE action_information_category='IN_ESI_PAY'
5259: AND action_context_type='PA'
5260: AND action_information1=p_contribution_period
5266: --Select the highest payroll action id for an Org
5267: CURSOR c_max_pa_action_id(p_esi_org_id VARCHAR2)
5268: IS
5269: SELECT max(pai.action_context_id)
5270: FROM pay_action_information pai
5271: ,pay_assignment_actions pac
5272: WHERE pai.action_information_category='IN_ESI_PAY'
5273: AND pai.action_context_type='PA'
5274: AND pai.action_information1=p_contribution_period
5275: AND pai.action_information2=p_esi_org_id
5276: AND pac.payroll_action_id=pai.action_context_id
5277: AND pac.assignment_action_id in
5278: ( SELECT action_context_id
5279: FROM pay_action_information
5280: WHERE action_information_category='IN_ESI_ASG'
5281: AND action_context_type='AAP'
5282: AND action_information1=p_contribution_period
5283: AND action_information2=p_esi_org_id
5282: AND action_information1=p_contribution_period
5283: AND action_information2=p_esi_org_id
5284: AND action_information11=
5285: ( SELECT max(to_date(action_information11,'DD-MM-YY'))
5286: FROM pay_action_information
5287: WHERE action_information_category='IN_ESI_ASG'
5288: AND action_context_type='AAP'
5289: AND action_information1=p_contribution_period
5290: AND action_information2=p_esi_org_id
5301: ,action_information4 rep_name
5302: ,action_information9 rep_desg
5303: ,action_information7 rep_addr
5304: ,action_information8 org_name
5305: FROM pay_action_information
5306: WHERE action_information_category='IN_ESI_PAY'
5307: AND action_context_type = 'PA'
5308: AND action_information1 = p_contribution_period
5309: AND action_information2 = p_esi_org_id
5312: --DISTINCT Assignment Ids --Later to be changed
5313: CURSOR c_asg_id(p_esi_org_id VARCHAR2)
5314: IS
5315: SELECT DISTINCT action_information3 insurance_no
5316: FROM pay_action_information
5317: WHERE action_information_category='IN_ESI_ASG'
5318: AND action_context_type='AAP'
5319: AND action_information1=p_contribution_period
5320: AND action_information2=p_esi_org_id
5325: ,p_insurance_no VARCHAR2
5326: )
5327: IS
5328: SELECT max(to_date(action_information11,'DD-MM-YY')) maxdate
5329: FROM pay_action_information
5330: WHERE action_information_category='IN_ESI_ASG'
5331: AND action_context_type='AAP'
5332: AND action_information1=p_contribution_period
5333: AND action_information2=p_org_id
5345: ,fnd_number.canonical_to_number(action_information7) employee_contr
5346: ,fnd_number.canonical_to_number(action_information8) employer_contr
5347: ,action_information9 esi_coverage
5348: ,action_information11 payroll_date
5349: FROM pay_action_information
5350: WHERE action_information_category='IN_ESI_ASG'
5351: AND action_context_type='AAP'
5352: AND action_information1=p_contribution_period
5353: AND action_information2=p_esi_org_id
5359: CURSOR c_absence(p_insurance_no VARCHAR2
5360: ,p_esi_org_id VARCHAR2)
5361: IS
5362: SELECT sum(nvl(action_information5,0)) absence
5363: FROM pay_action_information
5364: WHERE action_information_category='IN_ESI_ASG'
5365: AND action_context_type='AAP'
5366: AND action_information1=p_contribution_period
5367: AND action_information2=p_esi_org_id
5404: CURSOR c_remarks(p_insurance_no VARCHAR2
5405: ,p_esi_org_id VARCHAR2)
5406: IS
5407: SELECT DISTINCT TO_DATE(action_information11,'DD-MM-YYYY') mon
5408: FROM pay_action_information
5409: WHERE action_information_category='IN_ESI_ASG'
5410: AND action_context_type='AAP'
5411: AND action_information1=p_contribution_period
5412: AND action_information2=p_esi_org_id