eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PAY_IN_REPORTS_PKG dependencies on PAY_ACTION_INFORMATION

Line 412: FROM pay_action_information

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)

Line 429: FROM pay_action_information pai

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

Line 437: FROM pay_action_information

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

Line 444: FROM pay_action_information

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

Line 464: FROM pay_action_information

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;

Line 490: FROM pay_action_information pai

486: ,p_month NUMBER)
487: IS
488: SELECT COUNT(*) count
489: ,fnd_number.canonical_to_number(pai.action_information5) rate
490: FROM pay_action_information pai
491: WHERE pai.jurisdiction_code = 'MH'
492: AND pai.source_id = p_source_id
493: AND pai.action_information_category = 'IN_PT_ASG'
494: AND pai.action_information1 = p_year

Line 499: FROM pay_action_information

495: AND TO_NUMBER(pai.action_information2) = p_month
496: AND pai.action_context_type = 'AAP'
497: AND pai.action_information6 <> 'Yes'
498: AND pai.action_information_id = (SELECT MAX(action_information_id)
499: FROM pay_action_information
500: WHERE action_information1 = p_year
501: AND TO_NUMBER(action_information2) = p_month
502: AND assignment_id = pai.assignment_id
503: AND action_context_type = 'AAP'

Line 1052: FROM pay_action_information pai

1048:
1049: CURSOR csr_org_max_action_context_id
1050: IS
1051: SELECT MAX(pai.action_context_id)
1052: FROM pay_action_information pai
1053: WHERE pai.action_information_category = 'IN_EOY_ORG'
1054: AND pai.Action_information1 = p_gre_org_id
1055: AND pai.action_information3 = p_assess_year
1056: AND pai.action_context_type = 'PA';

Line 1084: FROM pay_action_information pai

1080: , pay_in_reports_pkg.get_location_details(pai.action_information16,'POSTAL_CODE')
1081: , pai.action_information17
1082: , pai.action_information18
1083: , pai.action_information13
1084: FROM pay_action_information pai
1085: , pay_payroll_actions ppa
1086: WHERE pai.action_information_category = 'IN_EOY_ORG'
1087: AND pai.action_context_type = 'PA'
1088: AND pai.action_information1 = p_gre_org_id

Line 1100: FROM pay_action_information

1096:
1097: CURSOR csr_tot_emp_cnt
1098: IS
1099: SELECT COUNT(*)
1100: FROM pay_action_information
1101: WHERE action_information_category = 'IN_EOY_PERSON'
1102: AND action_context_type = 'AAP'
1103: AND action_information2 = p_assess_year
1104: AND action_information3 = p_gre_org_id

Line 1106: FROM pay_action_information pai

1102: AND action_context_type = 'AAP'
1103: AND action_information2 = p_assess_year
1104: AND action_information3 = p_gre_org_id
1105: AND action_context_id IN ( SELECT MAX(action_context_id)
1106: FROM pay_action_information pai
1107: ,pay_assignment_actions paa
1108: ,per_assignments_f asg
1109: WHERE pai.action_information_category = 'IN_EOY_PERSON'
1110: AND pai.action_context_type = 'AAP'

Line 1122: FROM pay_action_information

1118:
1119: CURSOR csr_tax_details(p_balance VARCHAR2,p_action_context_id NUMBER,p_source_id IN NUMBER)
1120: IS
1121: SELECT NVL(SUM(fnd_number.canonical_to_number(action_information2)),0)
1122: FROM pay_action_information
1123: WHERE action_information_category = 'IN_EOY_ASG_SAL'
1124: AND action_context_type = 'AAP'
1125: AND action_information1 = p_balance
1126: AND action_context_id = p_action_context_id

Line 1132: FROM pay_action_information pai

1128:
1129: CURSOR csr_get_max_cont_id IS
1130: SELECT MAX(pai.action_context_id) action_cont_id
1131: ,source_id sour_id
1132: FROM pay_action_information pai
1133: ,pay_assignment_actions paa
1134: ,per_assignments_f asg
1135: WHERE pai.action_information_category = 'IN_EOY_PERSON'
1136: AND pai.action_information3 = p_gre_org_id

Line 1495: SELECT DISTINCT paa_pay.action_information2 --PF Org Id

1491: ,p_effective_start_date DATE
1492: ,p_effective_end_date DATE
1493: )
1494: IS
1495: SELECT DISTINCT paa_pay.action_information2 --PF Org Id
1496: ,paa_pay.action_information3 --PF Org Reg Name
1497: ,paa_pay.action_information5 --Address
1498: ,paa_pay.action_information6 --Code
1499: ,paa_pay.action_information8 --PF Org Name

Line 1496: ,paa_pay.action_information3 --PF Org Reg Name

1492: ,p_effective_end_date DATE
1493: )
1494: IS
1495: SELECT DISTINCT paa_pay.action_information2 --PF Org Id
1496: ,paa_pay.action_information3 --PF Org Reg Name
1497: ,paa_pay.action_information5 --Address
1498: ,paa_pay.action_information6 --Code
1499: ,paa_pay.action_information8 --PF Org Name
1500: FROM pay_action_information paa_asg

Line 1497: ,paa_pay.action_information5 --Address

1493: )
1494: IS
1495: SELECT DISTINCT paa_pay.action_information2 --PF Org Id
1496: ,paa_pay.action_information3 --PF Org Reg Name
1497: ,paa_pay.action_information5 --Address
1498: ,paa_pay.action_information6 --Code
1499: ,paa_pay.action_information8 --PF Org Name
1500: FROM pay_action_information paa_asg
1501: ,pay_action_information paa_pay

Line 1498: ,paa_pay.action_information6 --Code

1494: IS
1495: SELECT DISTINCT paa_pay.action_information2 --PF Org Id
1496: ,paa_pay.action_information3 --PF Org Reg Name
1497: ,paa_pay.action_information5 --Address
1498: ,paa_pay.action_information6 --Code
1499: ,paa_pay.action_information8 --PF Org Name
1500: FROM pay_action_information paa_asg
1501: ,pay_action_information paa_pay
1502: ,pay_assignment_actions paa

Line 1499: ,paa_pay.action_information8 --PF Org Name

1495: SELECT DISTINCT paa_pay.action_information2 --PF Org Id
1496: ,paa_pay.action_information3 --PF Org Reg Name
1497: ,paa_pay.action_information5 --Address
1498: ,paa_pay.action_information6 --Code
1499: ,paa_pay.action_information8 --PF Org Name
1500: FROM pay_action_information paa_asg
1501: ,pay_action_information paa_pay
1502: ,pay_assignment_actions paa
1503: ,hr_organization_units hou

Line 1500: FROM pay_action_information paa_asg

1496: ,paa_pay.action_information3 --PF Org Reg Name
1497: ,paa_pay.action_information5 --Address
1498: ,paa_pay.action_information6 --Code
1499: ,paa_pay.action_information8 --PF Org Name
1500: FROM pay_action_information paa_asg
1501: ,pay_action_information paa_pay
1502: ,pay_assignment_actions paa
1503: ,hr_organization_units hou
1504: WHERE paa_asg.action_information_category = 'IN_PF_ASG'

Line 1501: ,pay_action_information paa_pay

1497: ,paa_pay.action_information5 --Address
1498: ,paa_pay.action_information6 --Code
1499: ,paa_pay.action_information8 --PF Org Name
1500: FROM pay_action_information paa_asg
1501: ,pay_action_information paa_pay
1502: ,pay_assignment_actions paa
1503: ,hr_organization_units hou
1504: WHERE paa_asg.action_information_category = 'IN_PF_ASG'
1505: AND paa_pay.action_information_category = 'IN_PF_PAY'

Line 1505: AND paa_pay.action_information_category = 'IN_PF_PAY'

1501: ,pay_action_information paa_pay
1502: ,pay_assignment_actions paa
1503: ,hr_organization_units hou
1504: WHERE paa_asg.action_information_category = 'IN_PF_ASG'
1505: AND paa_pay.action_information_category = 'IN_PF_PAY'
1506: AND paa_asg.ACTION_CONTEXT_TYPE = 'AAP'
1507: AND paa_pay.ACTION_CONTEXT_TYPE = 'PA'
1508: AND paa.assignment_action_id = paa_asg.action_context_id
1509: AND paa.payroll_action_id = paa_pay.action_context_id

Line 1511: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'EXEM','%') --PF Org Class

1507: AND paa_pay.ACTION_CONTEXT_TYPE = 'PA'
1508: AND paa.assignment_action_id = paa_asg.action_context_id
1509: AND paa.payroll_action_id = paa_pay.action_context_id
1510: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
1511: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'EXEM','%') --PF Org Class
1512: AND paa_asg.action_information2 = paa_pay.action_information2
1513: AND paa_asg.action_information15 IS NOT NULL
1514: AND paa_asg.action_information1 = p_contribution_period
1515: AND paa_pay.action_information1 = p_contribution_period

Line 1512: AND paa_asg.action_information2 = paa_pay.action_information2

1508: AND paa.assignment_action_id = paa_asg.action_context_id
1509: AND paa.payroll_action_id = paa_pay.action_context_id
1510: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
1511: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'EXEM','%') --PF Org Class
1512: AND paa_asg.action_information2 = paa_pay.action_information2
1513: AND paa_asg.action_information15 IS NOT NULL
1514: AND paa_asg.action_information1 = p_contribution_period
1515: AND paa_pay.action_information1 = p_contribution_period
1516: AND hou.organization_id=paa_pay.action_information2

Line 1515: AND paa_pay.action_information1 = p_contribution_period

1511: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'EXEM','%') --PF Org Class
1512: AND paa_asg.action_information2 = paa_pay.action_information2
1513: AND paa_asg.action_information15 IS NOT NULL
1514: AND paa_asg.action_information1 = p_contribution_period
1515: AND paa_pay.action_information1 = p_contribution_period
1516: AND hou.organization_id=paa_pay.action_information2
1517: AND hou.organization_id=paa_asg.action_information2
1518: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
1519: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date

Line 1516: AND hou.organization_id=paa_pay.action_information2

1512: AND paa_asg.action_information2 = paa_pay.action_information2
1513: AND paa_asg.action_information15 IS NOT NULL
1514: AND paa_asg.action_information1 = p_contribution_period
1515: AND paa_pay.action_information1 = p_contribution_period
1516: AND hou.organization_id=paa_pay.action_information2
1517: AND hou.organization_id=paa_asg.action_information2
1518: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
1519: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
1520: ORDER BY paa_pay.action_information8 ASC;

Line 1520: ORDER BY paa_pay.action_information8 ASC;

1516: AND hou.organization_id=paa_pay.action_information2
1517: AND hou.organization_id=paa_asg.action_information2
1518: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
1519: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
1520: ORDER BY paa_pay.action_information8 ASC;
1521:
1522: CURSOR c_assignment_id(p_pf_org_id VARCHAR2
1523: ,p_contribution_period VARCHAR2
1524: ,p_effective_start_date DATE

Line 1528: FROM pay_action_information

1524: ,p_effective_start_date DATE
1525: ,p_effective_end_date DATE)
1526: IS
1527: SELECT DISTINCT action_information15,assignment_id
1528: FROM pay_action_information
1529: WHERE action_information_category = 'IN_PF_ASG'
1530: AND action_information2 = p_pf_org_id --PF Organization ID
1531: AND action_information1 = p_contribution_period
1532: AND action_information13 BETWEEN p_effective_start_date AND p_effective_end_date

Line 1552: FROM pay_action_information pai

1548: ,p_contribution_period VARCHAR2
1549: ,p_pension_number VARCHAR2)
1550: IS
1551: SELECT '1' status
1552: FROM pay_action_information pai
1553: WHERE pai.action_information_category ='IN_PF_ASG'
1554: AND pai.action_information1=p_contribution_period
1555: AND pai.action_information2 = p_pf_org_id
1556: AND pai.action_information15 = p_pension_number

Line 1569: FROM pay_action_information pai_mas

1565: ,p_pf_salary_ceiling NUMBER)
1566: IS
1567: SELECT
1568: SUM(fnd_number.canonical_to_number(pai_mas.action_information10)) pension
1569: FROM pay_action_information pai_mas
1570: where pai_mas.action_information_category ='IN_PF_ASG'
1571: and pai_mas.action_information1 = p_contribution_period
1572: and pai_mas.action_information2 = p_pf_org_id
1573: and pai_mas.action_information15 = p_pension_number

Line 1575: FROM pay_action_information pai1

1571: and pai_mas.action_information1 = p_contribution_period
1572: and pai_mas.action_information2 = p_pf_org_id
1573: and pai_mas.action_information15 = p_pension_number
1574: and pai_mas.action_information_id in (SELECT MAX(pai1.action_information_id)
1575: FROM pay_action_information pai1
1576: WHERE pai1.action_information1 = p_contribution_period
1577: AND pai1.action_information2 = p_pf_org_id
1578: AND pai1.action_information15 = p_pension_number
1579: GROUP BY TRUNC(TO_DATE(pai1.action_information13,'DD-MM-YY'),'MM')

Line 1589: FROM pay_action_information

1585: ,p_effective_end_date DATE
1586: )
1587: IS
1588: SELECT DISTINCT TO_DATE(action_information13,'DD-MM-YYYY') mon
1589: FROM pay_action_information
1590: WHERE action_information_category='IN_PF_ASG'
1591: AND action_information3 =p_pf_number
1592: AND action_information2 =p_pf_org_id --PF Organization ID
1593: AND action_information1 = p_contribution_period -- Bug 5231500

Line 1618: FROM pay_action_information

1614: ,p_effective_end_date DATE
1615: )
1616: IS
1617: SELECT action_information4 --Full Name
1618: FROM pay_action_information
1619: WHERE action_information_category='IN_PF_ASG'
1620: AND action_information1 = p_contribution_period -- Bug 5231500
1621: AND action_information2 = p_pf_org_id
1622: AND action_information15 = p_pension_number

Line 1632: SELECT paa_pay.action_information4 rep_name

1628: ,p_effective_start_date DATE
1629: ,p_effective_end_date DATE
1630: )
1631: IS
1632: SELECT paa_pay.action_information4 rep_name
1633: FROM pay_action_information paa_asg
1634: ,pay_action_information paa_pay
1635: ,pay_assignment_actions paa
1636: WHERE paa_asg.action_information_category='IN_PF_ASG'

Line 1633: FROM pay_action_information paa_asg

1629: ,p_effective_end_date DATE
1630: )
1631: IS
1632: SELECT paa_pay.action_information4 rep_name
1633: FROM pay_action_information paa_asg
1634: ,pay_action_information paa_pay
1635: ,pay_assignment_actions paa
1636: WHERE paa_asg.action_information_category='IN_PF_ASG'
1637: AND paa_pay.action_information_category='IN_PF_PAY'

Line 1634: ,pay_action_information paa_pay

1630: )
1631: IS
1632: SELECT paa_pay.action_information4 rep_name
1633: FROM pay_action_information paa_asg
1634: ,pay_action_information paa_pay
1635: ,pay_assignment_actions paa
1636: WHERE paa_asg.action_information_category='IN_PF_ASG'
1637: AND paa_pay.action_information_category='IN_PF_PAY'
1638: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'

Line 1637: AND paa_pay.action_information_category='IN_PF_PAY'

1633: FROM pay_action_information paa_asg
1634: ,pay_action_information paa_pay
1635: ,pay_assignment_actions paa
1636: WHERE paa_asg.action_information_category='IN_PF_ASG'
1637: AND paa_pay.action_information_category='IN_PF_PAY'
1638: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
1639: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
1640: AND paa.assignment_action_id=paa_asg.action_context_id
1641: AND paa.payroll_action_id=paa_pay.action_context_id

Line 1642: AND paa_pay.action_information7 = 'EXEM'

1638: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
1639: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
1640: AND paa.assignment_action_id=paa_asg.action_context_id
1641: AND paa.payroll_action_id=paa_pay.action_context_id
1642: AND paa_pay.action_information7 = 'EXEM'
1643: AND paa_asg.action_information1=p_contribution_period
1644: AND paa_pay.action_information1=p_contribution_period
1645: AND paa_pay.action_information2=p_pf_org_id
1646: AND paa_asg.action_information2=p_pf_org_id

Line 1644: AND paa_pay.action_information1=p_contribution_period

1640: AND paa.assignment_action_id=paa_asg.action_context_id
1641: AND paa.payroll_action_id=paa_pay.action_context_id
1642: AND paa_pay.action_information7 = 'EXEM'
1643: AND paa_asg.action_information1=p_contribution_period
1644: AND paa_pay.action_information1=p_contribution_period
1645: AND paa_pay.action_information2=p_pf_org_id
1646: AND paa_asg.action_information2=p_pf_org_id
1647: AND paa_asg.action_information1=paa_pay.action_information1
1648: AND paa_asg.action_information2=paa_pay.action_information2

Line 1645: AND paa_pay.action_information2=p_pf_org_id

1641: AND paa.payroll_action_id=paa_pay.action_context_id
1642: AND paa_pay.action_information7 = 'EXEM'
1643: AND paa_asg.action_information1=p_contribution_period
1644: AND paa_pay.action_information1=p_contribution_period
1645: AND paa_pay.action_information2=p_pf_org_id
1646: AND paa_asg.action_information2=p_pf_org_id
1647: AND paa_asg.action_information1=paa_pay.action_information1
1648: AND paa_asg.action_information2=paa_pay.action_information2
1649: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date

Line 1647: AND paa_asg.action_information1=paa_pay.action_information1

1643: AND paa_asg.action_information1=p_contribution_period
1644: AND paa_pay.action_information1=p_contribution_period
1645: AND paa_pay.action_information2=p_pf_org_id
1646: AND paa_asg.action_information2=p_pf_org_id
1647: AND paa_asg.action_information1=paa_pay.action_information1
1648: AND paa_asg.action_information2=paa_pay.action_information2
1649: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
1650: ORDER BY TO_DATE(paa_asg.action_information13,'DD-MM-YYYY') DESC;
1651:

Line 1648: AND paa_asg.action_information2=paa_pay.action_information2

1644: AND paa_pay.action_information1=p_contribution_period
1645: AND paa_pay.action_information2=p_pf_org_id
1646: AND paa_asg.action_information2=p_pf_org_id
1647: AND paa_asg.action_information1=paa_pay.action_information1
1648: AND paa_asg.action_information2=paa_pay.action_information2
1649: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
1650: ORDER BY TO_DATE(paa_asg.action_information13,'DD-MM-YYYY') DESC;
1651:
1652: /*Added for Bug 5647738*/

Line 1658: FROM pay_action_information pai

1654: , p_pension_no varchar2)
1655: IS
1656: SELECT fnd_number.canonical_to_number(pai.action_information7) pf_wages
1657: ,pai.action_information13 date_earned
1658: FROM pay_action_information pai
1659: WHERE pai.action_information_category ='IN_PF_ASG'
1660: AND pai.action_information1 = p_contribution_period
1661: AND pai.action_information2 = p_pf_org_id
1662: AND pai.action_information15 = p_pension_no

Line 1673: FROM pay_Action_information pai1

1669: ,pai.action_information18
1670: ,pai.action_information_id
1671: ,pai.assignment_id
1672: HAVING pai.action_information_id = (SELECT MAX(pai1.action_information_id)
1673: FROM pay_Action_information pai1
1674: WHERE pai1.action_information_category ='IN_PF_ASG'
1675: AND pai1.action_information1 = p_contribution_period
1676: AND pai1.action_information2 = p_pf_org_id
1677: AND TRUNC(TO_DATE(pai.action_information13,'DD-MM-YY'),'MM') = TRUNC(TO_DATE(pai1.action_information13,'DD-MM-YY'),'MM')

Line 2168: FROM pay_action_information paa

2164:
2165: CURSOR c_transfer_check
2166: IS
2167: SELECT action_information2
2168: FROM pay_action_information paa
2169: WHERE paa.action_information_category='IN_PF_ASG'
2170: AND paa.action_context_type='AAP'
2171: AND paa.action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
2172: AND paa.action_information3=p_pf_number

Line 2178: FROM pay_action_information paa

2174:
2175: CURSOR c_transfer_status(pf_org_id VARCHAR2)
2176: IS
2177: SELECT 1
2178: FROM pay_action_information paa
2179: WHERE paa.action_information_category='IN_PF_ASG'
2180: AND paa.action_context_type='AAP'
2181: AND paa.action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
2182: AND paa.action_information3=p_pf_number

Line 2187: FROM pay_action_information paa

2183: and paa.action_information2=p_pf_org_id
2184: and TO_DATE(paa.action_information13,'DD-MM-YY')=
2185: (
2186: SELECT MAX(to_date(action_information13,'DD-MM-YY'))
2187: FROM pay_action_information paa
2188: WHERE paa.action_information_category='IN_PF_ASG'
2189: AND paa.action_context_type='AAP'
2190: AND paa.action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
2191: AND paa.action_information3=p_pf_number

Line 2216: FROM pay_action_information

2212:
2213: CURSOR c_last_pay_count
2214: IS
2215: SELECT COUNT(action_information2)
2216: FROM pay_action_information
2217: WHERE action_information_category ='IN_PF_ASG'
2218: AND action_information3=p_pf_number
2219: AND action_context_type='AAP'
2220: AND action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')

Line 2224: FROM pay_action_information paa

2220: AND action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
2221: AND TO_DATE(action_information13,'DD-MM-YY')=
2222: (
2223: SELECT MAX(TO_DATE(action_information13,'DD-MM-YY'))
2224: FROM pay_action_information paa
2225: WHERE paa.action_information_category='IN_PF_ASG'
2226: AND paa.action_context_type='AAP'
2227: AND paa.action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
2228: AND paa.action_information3=p_pf_number

Line 2234: FROM pay_action_information paa

2230:
2231: CURSOR c_last_pay_date
2232: IS
2233: SELECT MAX(TO_DATE(action_information13,'DD-MM-YY'))
2234: FROM pay_action_information paa
2235: WHERE paa.action_information_category='IN_PF_ASG'
2236: AND paa.action_context_type='AAP'
2237: AND paa.action_information1=to_char(p_effective_start_date,'YYYY')||'-'||to_char(p_effective_end_date,'YYYY')
2238: AND paa.action_information3=p_pf_number;

Line 2545: SELECT DISTINCT paa_pay.action_information2 --PF Org Id

2541: ,p_effective_end_date DATE
2542: ,p_contribution_period VARCHAR2
2543: )
2544: IS
2545: SELECT DISTINCT paa_pay.action_information2 --PF Org Id
2546: ,paa_pay.action_information3 --PF Org Reg Name
2547: ,paa_pay.action_information5 --Address
2548: ,paa_pay.action_information6 --Code
2549: ,paa_pay.action_information8 --PF Org Name

Line 2546: ,paa_pay.action_information3 --PF Org Reg Name

2542: ,p_contribution_period VARCHAR2
2543: )
2544: IS
2545: SELECT DISTINCT paa_pay.action_information2 --PF Org Id
2546: ,paa_pay.action_information3 --PF Org Reg Name
2547: ,paa_pay.action_information5 --Address
2548: ,paa_pay.action_information6 --Code
2549: ,paa_pay.action_information8 --PF Org Name
2550: FROM pay_action_information paa_asg

Line 2547: ,paa_pay.action_information5 --Address

2543: )
2544: IS
2545: SELECT DISTINCT paa_pay.action_information2 --PF Org Id
2546: ,paa_pay.action_information3 --PF Org Reg Name
2547: ,paa_pay.action_information5 --Address
2548: ,paa_pay.action_information6 --Code
2549: ,paa_pay.action_information8 --PF Org Name
2550: FROM pay_action_information paa_asg
2551: ,pay_action_information paa_pay

Line 2548: ,paa_pay.action_information6 --Code

2544: IS
2545: SELECT DISTINCT paa_pay.action_information2 --PF Org Id
2546: ,paa_pay.action_information3 --PF Org Reg Name
2547: ,paa_pay.action_information5 --Address
2548: ,paa_pay.action_information6 --Code
2549: ,paa_pay.action_information8 --PF Org Name
2550: FROM pay_action_information paa_asg
2551: ,pay_action_information paa_pay
2552: ,pay_assignment_actions paa

Line 2549: ,paa_pay.action_information8 --PF Org Name

2545: SELECT DISTINCT paa_pay.action_information2 --PF Org Id
2546: ,paa_pay.action_information3 --PF Org Reg Name
2547: ,paa_pay.action_information5 --Address
2548: ,paa_pay.action_information6 --Code
2549: ,paa_pay.action_information8 --PF Org Name
2550: FROM pay_action_information paa_asg
2551: ,pay_action_information paa_pay
2552: ,pay_assignment_actions paa
2553: ,hr_organization_units hou

Line 2550: FROM pay_action_information paa_asg

2546: ,paa_pay.action_information3 --PF Org Reg Name
2547: ,paa_pay.action_information5 --Address
2548: ,paa_pay.action_information6 --Code
2549: ,paa_pay.action_information8 --PF Org Name
2550: FROM pay_action_information paa_asg
2551: ,pay_action_information paa_pay
2552: ,pay_assignment_actions paa
2553: ,hr_organization_units hou
2554: WHERE paa_asg.action_information_category='IN_PF_ASG'

Line 2551: ,pay_action_information paa_pay

2547: ,paa_pay.action_information5 --Address
2548: ,paa_pay.action_information6 --Code
2549: ,paa_pay.action_information8 --PF Org Name
2550: FROM pay_action_information paa_asg
2551: ,pay_action_information paa_pay
2552: ,pay_assignment_actions paa
2553: ,hr_organization_units hou
2554: WHERE paa_asg.action_information_category='IN_PF_ASG'
2555: AND paa_pay.action_information_category='IN_PF_PAY'

Line 2555: AND paa_pay.action_information_category='IN_PF_PAY'

2551: ,pay_action_information paa_pay
2552: ,pay_assignment_actions paa
2553: ,hr_organization_units hou
2554: WHERE paa_asg.action_information_category='IN_PF_ASG'
2555: AND paa_pay.action_information_category='IN_PF_PAY'
2556: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
2557: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
2558: AND paa.assignment_action_id=paa_asg.action_context_id
2559: AND paa.payroll_action_id=paa_pay.action_context_id

Line 2561: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,null,'UEX','%') --PF Org Class

2557: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
2558: AND paa.assignment_action_id=paa_asg.action_context_id
2559: AND paa.payroll_action_id=paa_pay.action_context_id
2560: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
2561: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,null,'UEX','%') --PF Org Class
2562: AND paa_asg.action_information2 = paa_pay.action_information2
2563: AND paa_pay.action_information7 NOT IN ('EXEM')
2564: AND paa_asg.action_information3 IS NOT NULL
2565: AND paa_asg.action_information1=p_contribution_period

Line 2562: AND paa_asg.action_information2 = paa_pay.action_information2

2558: AND paa.assignment_action_id=paa_asg.action_context_id
2559: AND paa.payroll_action_id=paa_pay.action_context_id
2560: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
2561: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,null,'UEX','%') --PF Org Class
2562: AND paa_asg.action_information2 = paa_pay.action_information2
2563: AND paa_pay.action_information7 NOT IN ('EXEM')
2564: AND paa_asg.action_information3 IS NOT NULL
2565: AND paa_asg.action_information1=p_contribution_period
2566: AND paa_pay.action_information1=p_contribution_period

Line 2563: AND paa_pay.action_information7 NOT IN ('EXEM')

2559: AND paa.payroll_action_id=paa_pay.action_context_id
2560: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
2561: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,null,'UEX','%') --PF Org Class
2562: AND paa_asg.action_information2 = paa_pay.action_information2
2563: AND paa_pay.action_information7 NOT IN ('EXEM')
2564: AND paa_asg.action_information3 IS NOT NULL
2565: AND paa_asg.action_information1=p_contribution_period
2566: AND paa_pay.action_information1=p_contribution_period
2567: AND hou.organization_id=paa_pay.action_information2

Line 2566: AND paa_pay.action_information1=p_contribution_period

2562: AND paa_asg.action_information2 = paa_pay.action_information2
2563: AND paa_pay.action_information7 NOT IN ('EXEM')
2564: AND paa_asg.action_information3 IS NOT NULL
2565: AND paa_asg.action_information1=p_contribution_period
2566: AND paa_pay.action_information1=p_contribution_period
2567: AND hou.organization_id=paa_pay.action_information2
2568: AND hou.organization_id=paa_asg.action_information2
2569: AND hou.business_group_id = fnd_profile.VALUE('PER_BUSINESS_GROUP_ID')
2570: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date

Line 2567: AND hou.organization_id=paa_pay.action_information2

2563: AND paa_pay.action_information7 NOT IN ('EXEM')
2564: AND paa_asg.action_information3 IS NOT NULL
2565: AND paa_asg.action_information1=p_contribution_period
2566: AND paa_pay.action_information1=p_contribution_period
2567: AND hou.organization_id=paa_pay.action_information2
2568: AND hou.organization_id=paa_asg.action_information2
2569: AND hou.business_group_id = fnd_profile.VALUE('PER_BUSINESS_GROUP_ID')
2570: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
2571: ORDER BY paa_pay.action_information8 ASC;

Line 2571: ORDER BY paa_pay.action_information8 ASC;

2567: AND hou.organization_id=paa_pay.action_information2
2568: AND hou.organization_id=paa_asg.action_information2
2569: AND hou.business_group_id = fnd_profile.VALUE('PER_BUSINESS_GROUP_ID')
2570: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
2571: ORDER BY paa_pay.action_information8 ASC;
2572:
2573: CURSOR c_assignment_id(p_pf_org_id VARCHAR2
2574: ,p_effective_start_date DATE
2575: ,p_effective_end_date DATE

Line 2580: FROM pay_action_information

2576: ,p_contribution_period VARCHAR2
2577: )
2578: IS
2579: SELECT DISTINCT action_information3
2580: FROM pay_action_information
2581: WHERE action_information_category='IN_PF_ASG'
2582: AND action_information2 =p_pf_org_id --PF Organization ID
2583: AND action_information1 =p_contribution_period
2584: AND action_information13 BETWEEN p_effective_start_date --Payroll Date

Line 2599: FROM pay_action_information

2595: SELECT SUM(fnd_number.canonical_to_number(action_information7)) pf_ytd --PF Salary
2596: ,SUM(fnd_number.canonical_to_number(action_information8)) employee --Total Employee Contr
2597: ,SUM(fnd_number.canonical_to_number(action_information9)) employer --Employer Contr towards PF
2598: ,SUM(fnd_number.canonical_to_number(action_information10)) pension --Employer Contr towards Pension
2599: FROM pay_action_information
2600: WHERE action_information2 = p_pf_org_id
2601: AND action_information3 = p_pf_number
2602: AND action_information1 = p_contribution_period
2603: AND action_information_id IN(

Line 2605: FROM pay_action_information

2601: AND action_information3 = p_pf_number
2602: AND action_information1 = p_contribution_period
2603: AND action_information_id IN(
2604: SELECT MAX(action_information_id)
2605: FROM pay_action_information
2606: WHERE action_information2 = p_pf_org_id
2607: AND action_information3 = p_pf_number
2608: AND action_information1 = p_contribution_period
2609: GROUP BY TO_DATE('01'||substr(action_information13,3),'DD-MM-YYYY'))

Line 2620: FROM pay_action_information

2616: ,p_effective_end_date DATE
2617: )
2618: IS
2619: SELECT DISTINCT TO_DATE(action_information13,'DD-MM-YYYY') mon
2620: FROM pay_action_information
2621: WHERE action_information_category='IN_PF_ASG'
2622: AND action_information3 =p_pf_number
2623: AND action_information2 =p_pf_org_id --PF Organization ID
2624: AND action_information1 = p_contribution_period -- Bug 5231500

Line 2637: FROM pay_action_information

2633: )
2634: IS
2635: SELECT action_information6 --Voluntary Contribution Rate
2636: ,TO_DATE(action_information13,'DD-MM-YYYY')
2637: FROM pay_action_information
2638: WHERE action_information_category='IN_PF_ASG'
2639: AND action_information3 = p_pf_number
2640: AND action_information2 = p_pf_org_id --PF Organization ID
2641: AND action_information1 = p_contribution_period -- Bug 5231500

Line 2652: FROM pay_action_information

2648: ,p_effective_end_date DATE
2649: )
2650: IS
2651: SELECT count(DISTINCT action_information3)--assignment_id)
2652: FROM pay_action_information
2653: WHERE action_information_category='IN_PF_ASG'
2654: AND to_number(action_information6)>0
2655: AND action_information13 BETWEEN p_effective_start_date
2656: AND p_effective_end_date

Line 2691: FROM pay_action_information

2687: )
2688: IS
2689: SELECT action_information4 --Full Name
2690: ,assignment_id
2691: FROM pay_action_information
2692: WHERE action_information_category='IN_PF_ASG'
2693: AND action_information2 =p_pf_org_id --PF Organization ID
2694: AND action_information3 =p_pf_number --PF Number
2695: AND action_information1 = p_contribution_period -- Bug 5231500

Line 2706: SELECT paa_pay.action_information4 rep_name

2702: ,p_effective_start_date DATE
2703: ,p_effective_end_date DATE
2704: )
2705: IS
2706: SELECT paa_pay.action_information4 rep_name
2707: FROM pay_action_information paa_asg
2708: ,pay_action_information paa_pay
2709: ,pay_assignment_actions paa
2710: WHERE paa_asg.action_information_category ='IN_PF_ASG'

Line 2707: FROM pay_action_information paa_asg

2703: ,p_effective_end_date DATE
2704: )
2705: IS
2706: SELECT paa_pay.action_information4 rep_name
2707: FROM pay_action_information paa_asg
2708: ,pay_action_information paa_pay
2709: ,pay_assignment_actions paa
2710: WHERE paa_asg.action_information_category ='IN_PF_ASG'
2711: AND paa_pay.action_information_category ='IN_PF_PAY'

Line 2708: ,pay_action_information paa_pay

2704: )
2705: IS
2706: SELECT paa_pay.action_information4 rep_name
2707: FROM pay_action_information paa_asg
2708: ,pay_action_information paa_pay
2709: ,pay_assignment_actions paa
2710: WHERE paa_asg.action_information_category ='IN_PF_ASG'
2711: AND paa_pay.action_information_category ='IN_PF_PAY'
2712: AND paa_asg.ACTION_CONTEXT_TYPE ='AAP'

Line 2711: AND paa_pay.action_information_category ='IN_PF_PAY'

2707: FROM pay_action_information paa_asg
2708: ,pay_action_information paa_pay
2709: ,pay_assignment_actions paa
2710: WHERE paa_asg.action_information_category ='IN_PF_ASG'
2711: AND paa_pay.action_information_category ='IN_PF_PAY'
2712: AND paa_asg.ACTION_CONTEXT_TYPE ='AAP'
2713: AND paa_pay.ACTION_CONTEXT_TYPE ='PA'
2714: AND paa.assignment_action_id = paa_asg.action_context_id
2715: AND paa.payroll_action_id = paa_pay.action_context_id

Line 2716: AND paa_pay.action_information7 NOT IN ('EXEM')

2712: AND paa_asg.ACTION_CONTEXT_TYPE ='AAP'
2713: AND paa_pay.ACTION_CONTEXT_TYPE ='PA'
2714: AND paa.assignment_action_id = paa_asg.action_context_id
2715: AND paa.payroll_action_id = paa_pay.action_context_id
2716: AND paa_pay.action_information7 NOT IN ('EXEM')
2717: AND paa_asg.action_information3 IS NOT NULL
2718: AND paa_asg.action_information1 = p_contribution_period
2719: AND paa_pay.action_information1 = p_contribution_period
2720: AND paa_pay.action_information2 = p_pf_org_id

Line 2719: AND paa_pay.action_information1 = p_contribution_period

2715: AND paa.payroll_action_id = paa_pay.action_context_id
2716: AND paa_pay.action_information7 NOT IN ('EXEM')
2717: AND paa_asg.action_information3 IS NOT NULL
2718: AND paa_asg.action_information1 = p_contribution_period
2719: AND paa_pay.action_information1 = p_contribution_period
2720: AND paa_pay.action_information2 = p_pf_org_id
2721: AND paa_asg.action_information2 = p_pf_org_id
2722: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
2723: ORDER BY TO_DATE(paa_asg.action_information13,'DD-MM-YYYY') DESC;

Line 2720: AND paa_pay.action_information2 = p_pf_org_id

2716: AND paa_pay.action_information7 NOT IN ('EXEM')
2717: AND paa_asg.action_information3 IS NOT NULL
2718: AND paa_asg.action_information1 = p_contribution_period
2719: AND paa_pay.action_information1 = p_contribution_period
2720: AND paa_pay.action_information2 = p_pf_org_id
2721: AND paa_asg.action_information2 = p_pf_org_id
2722: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
2723: ORDER BY TO_DATE(paa_asg.action_information13,'DD-MM-YYYY') DESC;
2724:

Line 3084: FROM pay_action_information

3080: pay_in_utils.set_location(g_debug,l_procedure, 230);
3081:
3082: SELECT COUNT(DISTINCT action_information3)
3083: INTO pay_in_xml_utils.gXMLTable(l_count).Value
3084: FROM pay_action_information
3085: WHERE action_information_category='IN_PF_ASG'
3086: AND action_information1 = p_contribution_period -- Bug 5231500
3087: AND action_information2 = c_rec.action_information2 --PF Organization ID
3088: AND action_information3 IS NOT NULL

Line 3159: FROM pay_action_information paa_asg

3155: IS
3156: SELECT DISTINCT paa_asg.action_information14 --PF Org Name
3157: ,paa_asg.action_information3 pf_num --PF Number
3158: ,paa_asg.action_information2 pf_org_id --PF Org ID
3159: FROM pay_action_information paa_asg
3160: ,pay_action_information paa_pay
3161: ,pay_assignment_actions paa
3162: ,hr_organization_units hou
3163: WHERE paa_asg.action_information_category='IN_PF_ASG'

Line 3160: ,pay_action_information paa_pay

3156: SELECT DISTINCT paa_asg.action_information14 --PF Org Name
3157: ,paa_asg.action_information3 pf_num --PF Number
3158: ,paa_asg.action_information2 pf_org_id --PF Org ID
3159: FROM pay_action_information paa_asg
3160: ,pay_action_information paa_pay
3161: ,pay_assignment_actions paa
3162: ,hr_organization_units hou
3163: WHERE paa_asg.action_information_category='IN_PF_ASG'
3164: AND paa_pay.action_information_category='IN_PF_PAY'

Line 3164: AND paa_pay.action_information_category='IN_PF_PAY'

3160: ,pay_action_information paa_pay
3161: ,pay_assignment_actions paa
3162: ,hr_organization_units hou
3163: WHERE paa_asg.action_information_category='IN_PF_ASG'
3164: AND paa_pay.action_information_category='IN_PF_PAY'
3165: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
3166: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
3167: AND paa.assignment_action_id=paa_asg.action_context_id
3168: AND paa.payroll_action_id=paa_pay.action_context_id

Line 3169: AND paa_pay.action_information7 NOT IN ('EXEM')

3165: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
3166: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
3167: AND paa.assignment_action_id=paa_asg.action_context_id
3168: AND paa.payroll_action_id=paa_pay.action_context_id
3169: AND paa_pay.action_information7 NOT IN ('EXEM')
3170: AND paa_asg.action_information3 IS NOT NULL
3171: AND paa_asg.action_information1=p_contribution_period
3172: AND paa_pay.action_information1=p_contribution_period
3173: AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID

Line 3172: AND paa_pay.action_information1=p_contribution_period

3168: AND paa.payroll_action_id=paa_pay.action_context_id
3169: AND paa_pay.action_information7 NOT IN ('EXEM')
3170: AND paa_asg.action_information3 IS NOT NULL
3171: AND paa_asg.action_information1=p_contribution_period
3172: AND paa_pay.action_information1=p_contribution_period
3173: AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3174: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3175: AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
3176: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification

Line 3173: AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID

3169: AND paa_pay.action_information7 NOT IN ('EXEM')
3170: AND paa_asg.action_information3 IS NOT NULL
3171: AND paa_asg.action_information1=p_contribution_period
3172: AND paa_pay.action_information1=p_contribution_period
3173: AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3174: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3175: AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
3176: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
3177: AND paa_asg.action_information2 = paa_pay.action_information2

Line 3176: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification

3172: AND paa_pay.action_information1=p_contribution_period
3173: AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3174: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3175: AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
3176: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
3177: AND paa_asg.action_information2 = paa_pay.action_information2
3178: AND hou.organization_id=paa_pay.action_information2
3179: AND hou.organization_id=paa_asg.action_information2
3180: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')

Line 3177: AND paa_asg.action_information2 = paa_pay.action_information2

3173: AND paa_pay.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3174: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3175: AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
3176: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
3177: AND paa_asg.action_information2 = paa_pay.action_information2
3178: AND hou.organization_id=paa_pay.action_information2
3179: AND hou.organization_id=paa_asg.action_information2
3180: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
3181: ORDER BY paa_asg.action_information14,paa_asg.action_information3 asc;

Line 3178: AND hou.organization_id=paa_pay.action_information2

3174: AND paa_asg.action_information2 LIKE NVL(p_pf_org_id,'%') --PF Organization ID
3175: AND paa_asg.action_information3 LIKE DECODE(p_employee_type,'SPECIFIC',p_pf_number,'%') --PF Number
3176: AND paa_pay.action_information7 LIKE DECODE(p_pf_org_id,NULL,'UEX','%') --PF Org Classification
3177: AND paa_asg.action_information2 = paa_pay.action_information2
3178: AND hou.organization_id=paa_pay.action_information2
3179: AND hou.organization_id=paa_asg.action_information2
3180: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
3181: ORDER BY paa_asg.action_information14,paa_asg.action_information3 asc;
3182:

Line 3193: SELECT DISTINCT paa_pay.action_information5 --Address

3189: ,p_cp_pf_org_id VARCHAR2
3190: ,p_status VARCHAR2
3191: )
3192: IS
3193: SELECT DISTINCT paa_pay.action_information5 --Address
3194: ,paa_pay.action_information3 reg --Registered Name
3195: FROM pay_action_information paa_asg
3196: ,pay_action_information paa_pay
3197: ,pay_assignment_actions paa

Line 3194: ,paa_pay.action_information3 reg --Registered Name

3190: ,p_status VARCHAR2
3191: )
3192: IS
3193: SELECT DISTINCT paa_pay.action_information5 --Address
3194: ,paa_pay.action_information3 reg --Registered Name
3195: FROM pay_action_information paa_asg
3196: ,pay_action_information paa_pay
3197: ,pay_assignment_actions paa
3198: WHERE paa_asg.action_information_category='IN_PF_ASG'

Line 3195: FROM pay_action_information paa_asg

3191: )
3192: IS
3193: SELECT DISTINCT paa_pay.action_information5 --Address
3194: ,paa_pay.action_information3 reg --Registered Name
3195: FROM pay_action_information paa_asg
3196: ,pay_action_information paa_pay
3197: ,pay_assignment_actions paa
3198: WHERE paa_asg.action_information_category='IN_PF_ASG'
3199: AND paa_pay.action_information_category='IN_PF_PAY'

Line 3196: ,pay_action_information paa_pay

3192: IS
3193: SELECT DISTINCT paa_pay.action_information5 --Address
3194: ,paa_pay.action_information3 reg --Registered Name
3195: FROM pay_action_information paa_asg
3196: ,pay_action_information paa_pay
3197: ,pay_assignment_actions paa
3198: WHERE paa_asg.action_information_category='IN_PF_ASG'
3199: AND paa_pay.action_information_category='IN_PF_PAY'
3200: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'

Line 3199: AND paa_pay.action_information_category='IN_PF_PAY'

3195: FROM pay_action_information paa_asg
3196: ,pay_action_information paa_pay
3197: ,pay_assignment_actions paa
3198: WHERE paa_asg.action_information_category='IN_PF_ASG'
3199: AND paa_pay.action_information_category='IN_PF_PAY'
3200: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
3201: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
3202: AND paa.assignment_action_id=paa_asg.action_context_id
3203: AND paa.payroll_action_id=paa_pay.action_context_id

Line 3204: AND paa_pay.action_information7 NOT IN ('EXEM')

3200: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
3201: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
3202: AND paa.assignment_action_id=paa_asg.action_context_id
3203: AND paa.payroll_action_id=paa_pay.action_context_id
3204: AND paa_pay.action_information7 NOT IN ('EXEM')
3205: AND paa_asg.action_information3 IS NOT NULL
3206: AND paa_asg.action_information1 = p_contribution_period
3207: AND paa_pay.action_information1 = p_contribution_period
3208: AND paa_pay.action_information2 = p_pf_org_id --PF Organization ID

Line 3207: AND paa_pay.action_information1 = p_contribution_period

3203: AND paa.payroll_action_id=paa_pay.action_context_id
3204: AND paa_pay.action_information7 NOT IN ('EXEM')
3205: AND paa_asg.action_information3 IS NOT NULL
3206: AND paa_asg.action_information1 = p_contribution_period
3207: AND paa_pay.action_information1 = p_contribution_period
3208: AND paa_pay.action_information2 = p_pf_org_id --PF Organization ID
3209: AND paa_asg.action_information2 = p_pf_org_id --PF Organization ID
3210: AND paa_asg.action_information3 = p_pf_number --PF Number
3211: AND paa_asg.action_information2 LIKE DECODE(p_employee_type,'CURRENT',nvl(p_cp_pf_org_id,'%'),'%')

Line 3208: AND paa_pay.action_information2 = p_pf_org_id --PF Organization ID

3204: AND paa_pay.action_information7 NOT IN ('EXEM')
3205: AND paa_asg.action_information3 IS NOT NULL
3206: AND paa_asg.action_information1 = p_contribution_period
3207: AND paa_pay.action_information1 = p_contribution_period
3208: AND paa_pay.action_information2 = p_pf_org_id --PF Organization ID
3209: AND paa_asg.action_information2 = p_pf_org_id --PF Organization ID
3210: AND paa_asg.action_information3 = p_pf_number --PF Number
3211: AND paa_asg.action_information2 LIKE DECODE(p_employee_type,'CURRENT',nvl(p_cp_pf_org_id,'%'),'%')
3212: AND paa_pay.action_information2 LIKE DECODE(p_employee_type,'CURRENT',nvl(p_cp_pf_org_id,'%'),'%')

Line 3212: AND paa_pay.action_information2 LIKE DECODE(p_employee_type,'CURRENT',nvl(p_cp_pf_org_id,'%'),'%')

3208: AND paa_pay.action_information2 = p_pf_org_id --PF Organization ID
3209: AND paa_asg.action_information2 = p_pf_org_id --PF Organization ID
3210: AND paa_asg.action_information3 = p_pf_number --PF Number
3211: AND paa_asg.action_information2 LIKE DECODE(p_employee_type,'CURRENT',nvl(p_cp_pf_org_id,'%'),'%')
3212: AND paa_pay.action_information2 LIKE DECODE(p_employee_type,'CURRENT',nvl(p_cp_pf_org_id,'%'),'%')
3213: AND nvl(p_status,-1) LIKE DECODE(p_employee_type,'CURRENT','CURRENT','TRANSFERRED','TRANSFERRED',-1)
3214: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date;
3215:
3216: CURSOR c_name_fath_hus_name(p_pf_org_id VARCHAR2

Line 3227: FROM pay_action_information

3223: SELECT action_information4 --Full Name
3224: ,action_information5 --Father/Husband Name
3225: ,action_information13 --Payroll Date
3226: ,action_information6 --Voluntary Contribution Rate
3227: FROM pay_action_information
3228: WHERE action_information_category='IN_PF_ASG'
3229: AND action_information1 =p_contribution_period --PF Contribution Period
3230: AND action_information2 =p_pf_org_id --PF Organization ID
3231: AND action_information3 =p_pf_number --PF Number

Line 3250: ,paa_pay.action_information4 --PF Rep Name

3246: ,paa_asg.action_information9 --Employer Contr towards PF
3247: ,paa_asg.action_information10 --Employer Contr towards Pension
3248: ,paa_asg.action_information11 --Absence
3249: -- ,paa_asg.action_information12 --Remarks
3250: ,paa_pay.action_information4 --PF Rep Name
3251: ,paa_asg.action_information13 --Payroll Month
3252: ,paa_asg.assignment_id --Assignment ID
3253: FROM pay_action_information paa_asg
3254: ,pay_action_information paa_pay

Line 3253: FROM pay_action_information paa_asg

3249: -- ,paa_asg.action_information12 --Remarks
3250: ,paa_pay.action_information4 --PF Rep Name
3251: ,paa_asg.action_information13 --Payroll Month
3252: ,paa_asg.assignment_id --Assignment ID
3253: FROM pay_action_information paa_asg
3254: ,pay_action_information paa_pay
3255: ,pay_assignment_actions paa
3256: WHERE paa_asg.action_information_category='IN_PF_ASG'
3257: AND paa_pay.action_information_category='IN_PF_PAY'

Line 3254: ,pay_action_information paa_pay

3250: ,paa_pay.action_information4 --PF Rep Name
3251: ,paa_asg.action_information13 --Payroll Month
3252: ,paa_asg.assignment_id --Assignment ID
3253: FROM pay_action_information paa_asg
3254: ,pay_action_information paa_pay
3255: ,pay_assignment_actions paa
3256: WHERE paa_asg.action_information_category='IN_PF_ASG'
3257: AND paa_pay.action_information_category='IN_PF_PAY'
3258: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'

Line 3257: AND paa_pay.action_information_category='IN_PF_PAY'

3253: FROM pay_action_information paa_asg
3254: ,pay_action_information paa_pay
3255: ,pay_assignment_actions paa
3256: WHERE paa_asg.action_information_category='IN_PF_ASG'
3257: AND paa_pay.action_information_category='IN_PF_PAY'
3258: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
3259: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
3260: AND paa.assignment_action_id=paa_asg.action_context_id
3261: AND paa.payroll_action_id=paa_pay.action_context_id

Line 3262: AND paa_pay.action_information7 NOT IN ('EXEM')

3258: AND paa_asg.ACTION_CONTEXT_TYPE='AAP'
3259: AND paa_pay.ACTION_CONTEXT_TYPE='PA'
3260: AND paa.assignment_action_id=paa_asg.action_context_id
3261: AND paa.payroll_action_id=paa_pay.action_context_id
3262: AND paa_pay.action_information7 NOT IN ('EXEM')
3263: AND paa_asg.action_information3 IS NOT NULL
3264: AND paa_asg.action_information1=p_contribution_period
3265: AND paa_pay.action_information1=p_contribution_period
3266: AND paa_asg.action_information2=p_pf_org_id

Line 3265: AND paa_pay.action_information1=p_contribution_period

3261: AND paa.payroll_action_id=paa_pay.action_context_id
3262: AND paa_pay.action_information7 NOT IN ('EXEM')
3263: AND paa_asg.action_information3 IS NOT NULL
3264: AND paa_asg.action_information1=p_contribution_period
3265: AND paa_pay.action_information1=p_contribution_period
3266: AND paa_asg.action_information2=p_pf_org_id
3267: AND paa_asg.action_information3=p_pf_number
3268: AND paa_pay.action_information2=p_pf_org_id
3269: AND to_number(to_char(to_date(paa_asg.action_information13,'DD-MM-YY'),'MM'))=p_mon_number

Line 3268: AND paa_pay.action_information2=p_pf_org_id

3264: AND paa_asg.action_information1=p_contribution_period
3265: AND paa_pay.action_information1=p_contribution_period
3266: AND paa_asg.action_information2=p_pf_org_id
3267: AND paa_asg.action_information3=p_pf_number
3268: AND paa_pay.action_information2=p_pf_org_id
3269: AND to_number(to_char(to_date(paa_asg.action_information13,'DD-MM-YY'),'MM'))=p_mon_number
3270: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
3271: ORDER BY to_date(paa_asg.action_information13,'DD-MM-YYYY'), fnd_number.canonical_to_number(paa_asg.action_information7) ASC;
3272:

Line 3285: FROM pay_action_information

3281: ,SUM(fnd_number.canonical_to_number(action_information8)) employee --Total Employee Contr
3282: ,SUM(fnd_number.canonical_to_number(action_information9)) employer --Employer Contr towards PF
3283: ,SUM(fnd_number.canonical_to_number(action_information10)) pension --Employer Contr towards Pension
3284: ,SUM(fnd_number.canonical_to_number(action_information8) + fnd_number.canonical_to_number(action_information9)) total --Total Employee Employer Contr
3285: FROM pay_action_information
3286: WHERE action_information2 = p_pf_org_id
3287: AND action_information3 = p_pf_number
3288: AND action_information1 = p_contribution_period
3289: AND action_information_id IN(

Line 3291: FROM pay_action_information

3287: AND action_information3 = p_pf_number
3288: AND action_information1 = p_contribution_period
3289: AND action_information_id IN(
3290: SELECT MAX(action_information_id)
3291: FROM pay_action_information
3292: WHERE action_information2 = p_pf_org_id
3293: AND action_information3 = p_pf_number
3294: AND action_information1 = p_contribution_period
3295: GROUP BY to_date('01'||substr(action_information13,3),'DD-MM-YYYY'))

Line 3641: FROM pay_action_information pai

3637: AND hoi.org_information_context ='PER_IN_PF_DF'
3638: AND hou.organization_id like nvl(p_pf_org_id,'%')
3639: AND hou.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
3640: AND EXISTS (SELECT 1
3641: FROM pay_action_information pai
3642: WHERE pai.action_information_category ='IN_PF_PAY'
3643: AND pai.action_information1 = p_contribution_period --Contribution period
3644: AND pai.action_information2 = hou.organization_id -- Org ID
3645: AND pai.action_information7 = 'EXEM' -- PF Org Classification

Line 3652: SELECT paa_pay.action_information3 Registered_Name

3648:
3649: /* 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*/
3650: CURSOR csr_pf_org_details (p_pf_org_id number)
3651: IS
3652: SELECT paa_pay.action_information3 Registered_Name
3653: ,paa_pay.action_information4 Rep_name
3654: ,paa_pay.action_information5 Address
3655: ,paa_pay.action_information6 Code
3656: FROM pay_action_information paa_asg

Line 3653: ,paa_pay.action_information4 Rep_name

3649: /* 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*/
3650: CURSOR csr_pf_org_details (p_pf_org_id number)
3651: IS
3652: SELECT paa_pay.action_information3 Registered_Name
3653: ,paa_pay.action_information4 Rep_name
3654: ,paa_pay.action_information5 Address
3655: ,paa_pay.action_information6 Code
3656: FROM pay_action_information paa_asg
3657: ,pay_action_information paa_pay

Line 3654: ,paa_pay.action_information5 Address

3650: CURSOR csr_pf_org_details (p_pf_org_id number)
3651: IS
3652: SELECT paa_pay.action_information3 Registered_Name
3653: ,paa_pay.action_information4 Rep_name
3654: ,paa_pay.action_information5 Address
3655: ,paa_pay.action_information6 Code
3656: FROM pay_action_information paa_asg
3657: ,pay_action_information paa_pay
3658: ,pay_assignment_actions paa

Line 3655: ,paa_pay.action_information6 Code

3651: IS
3652: SELECT paa_pay.action_information3 Registered_Name
3653: ,paa_pay.action_information4 Rep_name
3654: ,paa_pay.action_information5 Address
3655: ,paa_pay.action_information6 Code
3656: FROM pay_action_information paa_asg
3657: ,pay_action_information paa_pay
3658: ,pay_assignment_actions paa
3659: WHERE paa_asg.action_information_category='IN_PF_ASG'

Line 3656: FROM pay_action_information paa_asg

3652: SELECT paa_pay.action_information3 Registered_Name
3653: ,paa_pay.action_information4 Rep_name
3654: ,paa_pay.action_information5 Address
3655: ,paa_pay.action_information6 Code
3656: FROM pay_action_information paa_asg
3657: ,pay_action_information paa_pay
3658: ,pay_assignment_actions paa
3659: WHERE paa_asg.action_information_category='IN_PF_ASG'
3660: AND paa_pay.action_information_category='IN_PF_PAY'

Line 3657: ,pay_action_information paa_pay

3653: ,paa_pay.action_information4 Rep_name
3654: ,paa_pay.action_information5 Address
3655: ,paa_pay.action_information6 Code
3656: FROM pay_action_information paa_asg
3657: ,pay_action_information paa_pay
3658: ,pay_assignment_actions paa
3659: WHERE paa_asg.action_information_category='IN_PF_ASG'
3660: AND paa_pay.action_information_category='IN_PF_PAY'
3661: AND paa_asg.action_context_type='AAP'

Line 3660: AND paa_pay.action_information_category='IN_PF_PAY'

3656: FROM pay_action_information paa_asg
3657: ,pay_action_information paa_pay
3658: ,pay_assignment_actions paa
3659: WHERE paa_asg.action_information_category='IN_PF_ASG'
3660: AND paa_pay.action_information_category='IN_PF_PAY'
3661: AND paa_asg.action_context_type='AAP'
3662: AND paa_pay.action_context_type='PA'
3663: AND paa.assignment_action_id=paa_asg.action_context_id
3664: AND paa.payroll_action_id=paa_pay.action_context_id

Line 3665: AND paa_pay.action_information7 = 'EXEM'

3661: AND paa_asg.action_context_type='AAP'
3662: AND paa_pay.action_context_type='PA'
3663: AND paa.assignment_action_id=paa_asg.action_context_id
3664: AND paa.payroll_action_id=paa_pay.action_context_id
3665: AND paa_pay.action_information7 = 'EXEM'
3666: AND paa_asg.action_information1=p_contribution_period
3667: AND paa_pay.action_information1=p_contribution_period
3668: AND paa_pay.action_information2=p_pf_org_id
3669: AND paa_asg.action_information2=p_pf_org_id

Line 3667: AND paa_pay.action_information1=p_contribution_period

3663: AND paa.assignment_action_id=paa_asg.action_context_id
3664: AND paa.payroll_action_id=paa_pay.action_context_id
3665: AND paa_pay.action_information7 = 'EXEM'
3666: AND paa_asg.action_information1=p_contribution_period
3667: AND paa_pay.action_information1=p_contribution_period
3668: AND paa_pay.action_information2=p_pf_org_id
3669: AND paa_asg.action_information2=p_pf_org_id
3670: AND paa_asg.action_information1=paa_pay.action_information1
3671: AND paa_asg.action_information2=paa_pay.action_information2

Line 3668: AND paa_pay.action_information2=p_pf_org_id

3664: AND paa.payroll_action_id=paa_pay.action_context_id
3665: AND paa_pay.action_information7 = 'EXEM'
3666: AND paa_asg.action_information1=p_contribution_period
3667: AND paa_pay.action_information1=p_contribution_period
3668: AND paa_pay.action_information2=p_pf_org_id
3669: AND paa_asg.action_information2=p_pf_org_id
3670: AND paa_asg.action_information1=paa_pay.action_information1
3671: AND paa_asg.action_information2=paa_pay.action_information2
3672: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date

Line 3670: AND paa_asg.action_information1=paa_pay.action_information1

3666: AND paa_asg.action_information1=p_contribution_period
3667: AND paa_pay.action_information1=p_contribution_period
3668: AND paa_pay.action_information2=p_pf_org_id
3669: AND paa_asg.action_information2=p_pf_org_id
3670: AND paa_asg.action_information1=paa_pay.action_information1
3671: AND paa_asg.action_information2=paa_pay.action_information2
3672: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
3673: ORDER BY TO_DATE(paa_asg.action_information13,'DD-MM-YYYY') DESC;
3674:

Line 3671: AND paa_asg.action_information2=paa_pay.action_information2

3667: AND paa_pay.action_information1=p_contribution_period
3668: AND paa_pay.action_information2=p_pf_org_id
3669: AND paa_asg.action_information2=p_pf_org_id
3670: AND paa_asg.action_information1=paa_pay.action_information1
3671: AND paa_asg.action_information2=paa_pay.action_information2
3672: AND paa_asg.action_information13 BETWEEN p_effective_start_date AND p_effective_end_date
3673: ORDER BY TO_DATE(paa_asg.action_information13,'DD-MM-YYYY') DESC;
3674:
3675:

Line 3681: FROM pay_action_information pai

3677: /* 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 */
3678: CURSOR csr_pension_number(l_pf_org_id NUMBER)
3679: IS
3680: SELECT DISTINCT pai.action_information15 pension_number
3681: FROM pay_action_information pai
3682: WHERE pai.action_information_category ='IN_PF_ASG'
3683: AND pai.action_information1 = p_contribution_period
3684: AND pai.action_information2 = l_pf_org_id
3685: AND pai.action_information15 IS NOT NULL

Line 3696: FROM pay_action_information pai

3692: ,p_contribution_period VARCHAR2
3693: ,p_pension_number VARCHAR2)
3694: IS
3695: SELECT 'X' status
3696: FROM pay_action_information pai
3697: WHERE pai.action_information_category ='IN_PF_ASG'
3698: AND pai.action_information1=p_contribution_period
3699: AND pai.action_information2 = p_pf_org_id
3700: AND pai.action_information15 = p_pension_number

Line 3715: FROM pay_action_information pai

3711: IS
3712: SELECT pai.action_information4 Emp_name
3713: ,pai.action_information5 Fath_husb_name
3714: ,pai.action_information16 Hire_date
3715: FROM pay_action_information pai
3716: WHERE pai.action_information_category ='IN_PF_ASG'
3717: AND pai.action_information1 = p_contribution_period
3718: AND pai.action_information2 = p_pf_org_id
3719: AND pai.action_information15 = l_pension_number

Line 3736: FROM pay_action_information pai

3732: ,pai.action_information17 Contribution_sal
3733: ,pai.action_information18 Excluded_employee_status
3734: ,pai.action_information_id action_information_id
3735: ,pai.assignment_id assignment_id
3736: FROM pay_action_information pai
3737: WHERE pai.action_information_category ='IN_PF_ASG'
3738: AND pai.action_information1 = p_contribution_period
3739: AND pai.action_information2 = p_pf_org_id
3740: AND pai.action_information15 = p_pension_number

Line 3751: FROM pay_Action_information pai1

3747: ,pai.action_information18
3748: ,pai.action_information_id
3749: ,pai.assignment_id
3750: HAVING pai.action_information_id = (SELECT MAX(pai1.action_information_id)
3751: FROM pay_Action_information pai1
3752: WHERE pai1.action_information_category ='IN_PF_ASG'
3753: AND pai1.action_information1 = p_contribution_period
3754: AND pai1.action_information2 = p_pf_org_id
3755: AND TRUNC(TO_DATE(pai.action_information13,'DD-MM-YY'),'MM') = TRUNC(TO_DATE(pai1.action_information13,'DD-MM-YY'),'MM')

Line 3775: l_org_address pay_action_information.action_information1%TYPE;

3771:
3772:
3773: l_reg_name hr_organization_information.org_information1%TYPE;
3774: l_rep_name per_all_people_f.full_name%TYPE;
3775: l_org_address pay_action_information.action_information1%TYPE;
3776: --In the Rarest case of location adddress exceeding 240 characters nothing can be done
3777: l_est_code hr_organization_information.org_information1%TYPE;
3778:
3779: g_org_XMLTable pay_in_xml_utils.tXMLTable;

Line 4161: FROM pay_action_information paa

4157: IS
4158: CURSOR c_transfer_check
4159: IS
4160: SELECT action_information2
4161: FROM pay_action_information paa
4162: WHERE paa.action_information_category='IN_ESI_ASG'
4163: AND paa.action_context_type='AAP'
4164: AND paa.action_information1=p_contribution_period
4165: AND paa.action_information3=p_insurance_no

Line 4171: FROM pay_action_information paa

4167:
4168: CURSOR c_transfer_status(p_esi_org_id VARCHAR2)
4169: IS
4170: SELECT 1
4171: FROM pay_action_information paa
4172: WHERE paa.action_information_category='IN_ESI_ASG'
4173: AND paa.action_context_type='AAP'
4174: AND paa.action_information1=p_contribution_period
4175: AND paa.action_information3=p_insurance_no

Line 4180: FROM pay_action_information paa

4176: and paa.action_information2=p_esi_org_id
4177: and to_date(paa.action_information11,'DD-MM-YY')=
4178: (
4179: SELECT MAX(to_date(action_information11,'DD-MM-YY'))
4180: FROM pay_action_information paa
4181: WHERE paa.action_information_category='IN_ESI_ASG'
4182: AND paa.action_context_type='AAP'
4183: AND paa.action_information1=p_contribution_period
4184: AND paa.action_information3=p_insurance_no

Line 4190: FROM pay_action_information

4186:
4187: CURSOR c_last_pay_count
4188: IS
4189: SELECT count(action_information2)
4190: FROM pay_action_information
4191: WHERE action_information_category ='IN_ESI_ASG'
4192: AND action_information3=p_insurance_no
4193: AND action_context_type='AAP'
4194: AND action_information1=p_contribution_period

Line 4198: FROM pay_action_information paa

4194: AND action_information1=p_contribution_period
4195: AND to_date(action_information11,'DD-MM-YY')=
4196: (
4197: SELECT MAX(to_date(action_information11,'DD-MM-YY'))
4198: FROM pay_action_information paa
4199: WHERE paa.action_information_category='IN_ESI_ASG'
4200: AND paa.action_context_type='AAP'
4201: AND paa.action_information1=p_contribution_period
4202: AND paa.action_information3=p_insurance_no

Line 4208: FROM pay_action_information paa

4204:
4205: CURSOR c_last_pay_date
4206: IS
4207: SELECT MAX(to_date(action_information11,'DD-MM-YY'))
4208: FROM pay_action_information paa
4209: WHERE paa.action_information_category='IN_ESI_ASG'
4210: AND paa.action_context_type='AAP'
4211: AND paa.action_information1=p_contribution_period
4212: AND paa.action_information3=p_insurance_no;

Line 4267: FROM pay_action_information

4263: --Find Exemption status
4264: CURSOR c_esi_exemption(p_payroll_date DATE)
4265: IS
4266: SELECT SUBSTR(action_information9,1,1)
4267: FROM pay_action_information
4268: WHERE action_information_category='IN_ESI_ASG'
4269: AND action_information3=p_insurance_no
4270: AND action_information2=p_org_id
4271: AND action_information1=p_contribution_period -- Bug 5231500

Line 4537: FROM pay_action_information

4533: CURSOR c_distinct_org
4534: IS
4535: SELECT DISTINCT action_information2 org_id
4536: ,action_information8 org_name
4537: FROM pay_action_information
4538: ,hr_organization_units hou
4539: WHERE action_information_category='IN_ESI_PAY'
4540: AND action_context_type='PA'
4541: AND action_information1=p_contribution_period

Line 4551: FROM pay_action_information pai

4547: --Select the highest payroll action id for an Org
4548: CURSOR c_max_pa_action_id(p_esi_org_id VARCHAR2)
4549: IS
4550: SELECT max(pai.action_context_id)
4551: FROM pay_action_information pai
4552: ,pay_assignment_actions pac
4553: WHERE pai.action_information_category='IN_ESI_PAY'
4554: AND pai.action_context_type='PA'
4555: AND pai.action_information1=p_contribution_period

Line 4560: FROM pay_action_information

4556: AND pai.action_information2=p_esi_org_id
4557: AND pac.payroll_action_id=pai.action_context_id
4558: AND pac.assignment_action_id in
4559: ( SELECT action_context_id
4560: FROM pay_action_information
4561: WHERE action_information_category='IN_ESI_ASG'
4562: AND action_context_type='AAP'
4563: AND action_information1=p_contribution_period
4564: AND action_information2=p_esi_org_id

Line 4567: FROM pay_action_information

4563: AND action_information1=p_contribution_period
4564: AND action_information2=p_esi_org_id
4565: AND action_information11=
4566: ( SELECT max(to_date(action_information11,'DD-MM-YY'))
4567: FROM pay_action_information
4568: WHERE action_information_category='IN_ESI_ASG'
4569: AND action_context_type='AAP'
4570: AND action_information1=p_contribution_period
4571: AND action_information2=p_esi_org_id

Line 4586: FROM pay_action_information

4582: ,action_information4 rep_name
4583: ,action_information9 rep_desg
4584: ,action_information7 rep_addr
4585: ,action_information8 org_name
4586: FROM pay_action_information
4587: WHERE action_information_category='IN_ESI_PAY'
4588: AND action_context_type = 'PA'
4589: AND action_information1 = p_contribution_period
4590: AND action_information2 = p_esi_org_id

Line 4597: FROM pay_action_information

4593: --DISTINCT Assignment Ids --Later to be changed
4594: CURSOR c_asg_id(p_esi_org_id VARCHAR2)
4595: IS
4596: SELECT DISTINCT action_information3 insurance_no
4597: FROM pay_action_information
4598: WHERE action_information_category='IN_ESI_ASG'
4599: AND action_context_type='AAP'
4600: AND action_information1=p_contribution_period
4601: AND action_information2=p_esi_org_id

Line 4610: FROM pay_action_information

4606: ,p_insurance_no VARCHAR2
4607: )
4608: IS
4609: SELECT max(to_date(action_information11,'DD-MM-YY')) maxdate
4610: FROM pay_action_information
4611: WHERE action_information_category='IN_ESI_ASG'
4612: AND action_context_type='AAP'
4613: AND action_information1=p_contribution_period
4614: AND action_information2=p_org_id

Line 4630: FROM pay_action_information

4626: ,fnd_number.canonical_to_number(action_information7) employee_contr
4627: ,fnd_number.canonical_to_number(action_information8) employer_contr
4628: ,action_information9 esi_coverage
4629: ,action_information11 payroll_date
4630: FROM pay_action_information
4631: WHERE action_information_category='IN_ESI_ASG'
4632: AND action_context_type='AAP'
4633: AND action_information1=p_contribution_period
4634: AND action_information2=p_esi_org_id

Line 4644: FROM pay_action_information

4640: CURSOR c_absence(p_insurance_no VARCHAR2
4641: ,p_esi_org_id VARCHAR2)
4642: IS
4643: SELECT sum(nvl(action_information5,0)) absence
4644: FROM pay_action_information
4645: WHERE action_information_category='IN_ESI_ASG'
4646: AND action_context_type='AAP'
4647: AND action_information1=p_contribution_period
4648: AND action_information2=p_esi_org_id

Line 4689: FROM pay_action_information

4685: CURSOR c_remarks(p_insurance_no VARCHAR2
4686: ,p_esi_org_id VARCHAR2)
4687: IS
4688: SELECT DISTINCT TO_DATE(action_information11,'DD-MM-YYYY') mon
4689: FROM pay_action_information
4690: WHERE action_information_category='IN_ESI_ASG'
4691: AND action_context_type='AAP'
4692: AND action_information1=p_contribution_period
4693: AND action_information2=p_esi_org_id