[Home] [Help]
369: PROCEDURE create_from_person_types (P_BUSINESS_GROUP_ID IN varchar2,
370: P_USER_TABLE_NAME IN varchar2,
371: P_USER_COLUMN_NAME IN varchar2)
372: IS
373: v_user_person_type per_person_types.user_person_type%TYPE;
374:
375: v_sequence number := 10;
376:
377: v_legislation_code varchar2 (10); -- holds legislation code returned
377: v_legislation_code varchar2 (10); -- holds legislation code returned
378:
379: CURSOR get_person_types_csr IS
380: SELECT user_person_type
381: FROM per_person_types_v
382: WHERE business_group_id = P_BUSINESS_GROUP_ID;
383:
384: BEGIN
385: -- Initialising the parameters
443: ,p_count_clause1 => 'sum(bal.run_amount)'
444: ,p_seeded_data => 'Y'
445: ,p_count_clause2 => ''
446: ,p_where_clause => '/*DBG p.person_id DBGCOLS1 person_id DBG*/ from per_bf_balances_v bal
447: , per_person_types_tl pt
448: , per_all_people_f p
449: , per_assignment_status_types per_ast
450: , per_all_assignments_f a
451: , P_ESTABLISHMENT_TABLE
473: (select count(*)
474: from
475: (select p2.person_id,sum(bal2.run_amount) run_amount
476: from
477: per_person_types_tl pt2
478: , per_all_people_f p2
479: , per_bf_balances_v bal2
480: , per_assignment_status_types ast2
481: , per_all_assignments_f a2
517: ,p_count_clause1 => 'sum(bal.run_amount)'
518: ,p_seeded_data => 'Y'
519: ,p_count_clause2 => ''
520: ,p_where_clause => 'from per_bf_balances_v bal
521: , per_person_types_tl pt
522: , per_all_people_f p
523: , per_assignment_status_types per_ast
524: , per_all_assignments_f a
525: , P_ESTABLISHMENT_TABLE
523: , per_assignment_status_types per_ast
524: , per_all_assignments_f a
525: , P_ESTABLISHMENT_TABLE
526: , (select count(distinct p4.person_id) population
527: from per_person_types_tl pt4
528: , per_all_people_f p4
529: , per_assignment_status_types ast4
530: , per_all_assignments_f a4
531: where a4.assignment_status_type_id = ast4.assignment_status_type_id
567: (select count(*)
568: from
569: (select p2.person_id,sum(bal2.run_amount) run_amount
570: from per_bf_balances_v bal2
571: , per_person_types_tl pt2
572: ,
573: per_all_people_f p2
574: , per_assignment_status_types ast2
575: , per_all_assignments_f a2
610: ,p_count_clause1 => 'sum(bal.run_amount)'
611: ,p_seeded_data => 'Y'
612: ,p_count_clause2 => ''
613: ,p_where_clause => 'from per_bf_balances_v bal
614: , per_person_types_tl pt
615: , per_all_people_f p
616: , per_assignment_status_types per_ast
617: , per_all_assignments_f a
618: , P_ESTABLISHMENT_TABLE
616: , per_assignment_status_types per_ast
617: , per_all_assignments_f a
618: , P_ESTABLISHMENT_TABLE
619: , (select count(distinct p4.person_id) population
620: from per_person_types_tl pt4
621: , per_all_people_f p4
622: , per_assignment_status_types ast4
623: , per_all_assignments_f a4
624: where a4.assignment_status_type_id = ast4.assignment_status_type_id
660: (select count(*)
661: from
662: (select p2.person_id,sum(bal2.run_amount) run_amount
663: from per_bf_balances_v bal2
664: , per_person_types_tl pt2
665: ,
666: per_all_people_f p2
667: , per_assignment_status_types ast2
668: , per_all_assignments_f a2
710: , per_all_assignments_f a
711: where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
712: and (p.effective_start_date,a.effective_start_date) =
713: (select max(p2.effective_start_date),max(a2.effective_start_date)
714: from per_person_types_tl pt2
715: , per_all_people_f p2
716: , per_assignment_status_types ast2
717: , per_all_assignments_f a2
718: , P_ESTABLISHMENT_TABLE
745: RESTRICTION.ABSENCE_CATEGORY[abt]
746: and abt.business_group_id = P_BUSINESS_GROUP_ID
747: and exists
748: (select null
749: from per_person_types_tl pt3
750: , per_all_people_f p3
751: , per_assignment_status_types ast3
752: , per_all_assignments_f a3
753: , P_ESTABLISHMENT_TABLE
786: where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
787: and a.cagr_grade_def_id = cagr_def.cagr_grade_def_id(+)
788: and (p.effective_start_date,a.effective_start_date) =
789: (select max(p2.effective_start_date),max(a2.effective_start_date)
790: from per_person_types_tl pt2
791: , per_all_people_f p2
792: , per_assignment_status_types ast2
793: , per_all_assignments_f a2
794: , P_ESTABLISHMENT_TABLE
817: between P_START_OF_YEAR and P_END_OF_YEAR
818: and bal.run_amount is not null
819: and exists
820: (select null
821: from per_person_types_tl pt3
822: , per_all_people_f p3
823: , per_assignment_status_types ast3
824: , per_all_assignments_f a3
825: , P_ESTABLISHMENT_TABLE
859: where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
860: and a.cagr_grade_def_id = cagr_def.cagr_grade_def_id(+)
861: and (p.effective_start_date,a.effective_start_date) =
862: (select max(p2.effective_start_date),max(a2.effective_start_date)
863: from per_person_types_tl pt2
864: , per_all_people_f p2
865: , per_assignment_status_types ast2
866: , per_all_assignments_f a2
867: , P_ESTABLISHMENT_TABLE
890: between P_START_OF_YEAR and P_END_OF_YEAR
891: and bal.run_amount is not null
892: and exists
893: (select null
894: from per_person_types_tl pt3
895: , per_all_people_f p3
896: , per_assignment_status_types ast3
897: , per_all_assignments_f a3
898: , P_ESTABLISHMENT_TABLE
932: where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
933: and a.cagr_grade_def_id = cagr_def.cagr_grade_def_id(+)
934: and (p.effective_start_date,a.effective_start_date) =
935: (select max(p2.effective_start_date),max(a2.effective_start_date)
936: from per_person_types_tl pt2
937: , per_all_people_f p2
938: , per_assignment_status_types ast2
939: , per_all_assignments_f a2
940: , P_ESTABLISHMENT_TABLE
963: between P_START_OF_YEAR and P_END_OF_YEAR
964: and bal.run_amount is not null
965: and exists
966: (select null
967: from per_person_types_tl pt3
968: , per_all_people_f p3
969: , per_assignment_status_types ast3
970: , per_all_assignments_f a3
971: , P_ESTABLISHMENT_TABLE
1004: , per_all_assignments_f a
1005: where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
1006: and (p.effective_start_date,a.effective_start_date) =
1007: (select max(p2.effective_start_date),max(a2.effective_start_date)
1008: from per_person_types_tl pt2
1009: , per_all_people_f p2
1010: , per_assignment_status_types ast2
1011: , per_all_assignments_f a2
1012: , P_ESTABLISHMENT_TABLE
1035: between P_START_OF_YEAR and P_END_OF_YEAR
1036: and bal.run_amount is not null
1037: and exists
1038: (select null
1039: from per_person_types_tl pt3
1040: , per_all_people_f p3
1041: , per_assignment_status_types ast3
1042: , per_all_assignments_f a3
1043: , P_ESTABLISHMENT_TABLE
1077: where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
1078: and a.cagr_grade_def_id = cagr_def.cagr_grade_def_id(+)
1079: and (p.effective_start_date,a.effective_start_date) =
1080: (select max(p2.effective_start_date),max(a2.effective_start_date)
1081: from per_person_types_tl pt2
1082: , per_all_people_f p2
1083: , per_assignment_status_types ast2
1084: , per_all_assignments_f a2
1085: , P_ESTABLISHMENT_TABLE
1108: between P_START_OF_YEAR and P_END_OF_YEAR
1109: and bal.run_amount is not null
1110: and exists
1111: (select null
1112: from per_person_types_tl pt3
1113: , per_all_people_f p3
1114: , per_assignment_status_types ast3
1115: , per_all_assignments_f a3
1116: , P_ESTABLISHMENT_TABLE
1145: ,p_where_clause => 'from per_medical_assessments pma
1146: , per_all_people_f p
1147: where p.effective_start_date =
1148: (select max(p2.effective_start_date)
1149: from per_person_types_tl pt2
1150: , per_all_people_f p2
1151: , per_assignment_status_types ast2
1152: , per_all_assignments_f a2
1153: , P_ESTABLISHMENT_TABLE
1186: , per_all_people_f p
1187: , per_all_assignments_f a
1188: where (p.effective_start_date,a.effective_start_date) =
1189: (select max(p2.effective_start_date),max(a2.effective_start_date)
1190: from per_person_types_tl pt2
1191: , per_all_people_f p2
1192: , per_assignment_status_types ast2
1193: , per_all_assignments_f a2
1194: , P_ESTABLISHMENT_TABLE
1220: to_date(P_YEAR||''1231'',''YYYYMMDD'')
1221: and bal.run_amount is not null
1222: and exists
1223: (select null
1224: from per_person_types_tl pt3
1225: , per_all_people_f p3
1226: , per_assignment_status_types ast3
1227: , per_all_assignments_f a3
1228: , P_ESTABLISHMENT_TABLE
1257: ,p_where_clause => 'from per_medical_assessments pma
1258: , per_all_people_f p
1259: where p.effective_start_date =
1260: (select max(p2.effective_start_date)
1261: from per_person_types_tl pt2
1262: , per_all_people_f p2
1263: , per_assignment_status_types ast2
1264: , per_all_assignments_f a2
1265: , P_ESTABLISHMENT_TABLE
1300: , per_all_assignments_f a
1301: where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
1302: and (p.effective_start_date,a.effective_start_date) =
1303: (select max(p2.effective_start_date),max(a2.effective_start_date)
1304: from per_person_types_tl pt2
1305: , per_all_people_f p2
1306: , per_assignment_status_types ast2
1307: , per_all_assignments_f a2
1308: , P_ESTABLISHMENT_TABLE
1385: ,p_where_clause => 'from per_all_people_f p
1386: , per_all_assignments_f a
1387: where (p.effective_start_date,a.effective_start_date) =
1388: (select max(p2.effective_start_date),max(a2.effective_start_date)
1389: from per_person_types_tl pt2
1390: , per_all_people_f p2
1391: , per_assignment_status_types ast2
1392: , per_all_assignments_f a2
1393: , P_ESTABLISHMENT_TABLE
1437: where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
1438: and a.cagr_grade_def_id = cagr_def.cagr_grade_def_id(+)
1439: and (p.effective_start_date,a.effective_start_date) =
1440: (select max(p2.effective_start_date),max(a2.effective_start_date)
1441: from per_person_types_tl pt2
1442: , per_all_people_f p2
1443: , per_assignment_status_types ast2
1444: , per_all_assignments_f a2
1445: , P_ESTABLISHMENT_TABLE
1467: between P_START_OF_YEAR and P_END_OF_YEAR
1468: and bal.run_amount is not null
1469: and exists
1470: (select null
1471: from per_person_types_tl pt3
1472: , per_all_people_f p3
1473: , per_assignment_status_types ast3
1474: , per_all_assignments_f a3
1475: , P_ESTABLISHMENT_TABLE
1506: , per_all_assignments_f a
1507: where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
1508: and (p.effective_start_date,a.effective_start_date) =
1509: (select max(p2.effective_start_date),max(a2.effective_start_date)
1510: from per_person_types_tl pt2
1511: , per_all_people_f p2
1512: , per_assignment_status_types ast2
1513: , per_all_assignments_f a2
1514: , P_ESTABLISHMENT_TABLE
1533: RESTRICTION.NEW_USER_PERSON_TYPE[/*parameteralias p2*/])
1534: and a.person_id = p.person_id
1535: and exists
1536: (select null
1537: from per_person_types_tl pt3
1538: , per_all_people_f p3
1539: , per_bf_balances_v bal
1540: , per_assignment_status_types ast3
1541: , per_all_assignments_f a3
1578: where a.person_id = p.person_id
1579: and a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id (+)
1580: and (p.effective_start_date,a.effective_start_date) =
1581: (select max(p2.effective_start_date),max(a2.effective_start_date)
1582: from per_person_types_tl pt2
1583: , per_all_people_f p2
1584: , per_assignment_status_types ast2
1585: , per_all_assignments_f a2
1586: , P_ESTABLISHMENT_TABLE
1620: ,p_seeded_data => 'Y'
1621: ,p_count_clause2 => ''
1622: ,p_where_clause => ' from hr_soft_coding_keyflex scl
1623: , per_contracts_f c
1624: , per_person_types_tl pt
1625: , per_all_people_f p
1626: , per_assignment_status_types per_ast
1627: , per_all_assignments_f a
1628: , P_ESTABLISHMENT_TABLE
1651: OR exists
1652: (select null
1653: from hr_soft_coding_keyflex scl2
1654: , per_contracts_f c2
1655: , per_person_types_tl pt2
1656: , per_all_people_f p2
1657: , per_assignment_status_types ast2
1658: , per_all_assignments_f a2
1659: where a2.person_id = p.person_id
1696: where a.person_id = p.person_id
1697: and a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id (+)
1698: and (p.effective_start_date,a.effective_start_date) =
1699: (select max(p2.effective_start_date),max(a2.effective_start_date)
1700: from per_person_types_tl pt2
1701: , per_all_people_f p2
1702: , per_assignment_status_types ast2
1703: , per_all_assignments_f a2
1704: , P_ESTABLISHMENT_TABLE
1739: ,p_count_clause1 => 'count(distinct(p.person_id))'
1740: ,p_seeded_data => 'Y'
1741: ,p_count_clause2 => ''
1742: ,p_where_clause => '/*DBG a.effective_start_date,a.effective_end_date DBGCOLS2 start_date end_date DBG*/ from hr_soft_coding_keyflex scl
1743: , per_person_types_tl pt
1744: , per_all_people_f p
1745: , per_assignment_status_types per_ast
1746: , per_all_assignments_f a
1747: , P_ESTABLISHMENT_TABLE
1763: and P_END_OF_YEAR <= p.effective_end_date
1764: and (least(a.effective_end_date,p.effective_end_date) > P_END_OF_YEAR
1765: OR exists (select null
1766: from hr_soft_coding_keyflex scl2
1767: , per_person_types_tl pt2
1768: ,
1769: per_all_people_f p2
1770: , per_assignment_status_types ast2
1771: , per_all_assignments_f a2
1799: ,p_count_clause1 => 'count(distinct(p.person_id))'
1800: ,p_seeded_data => 'Y'
1801: ,p_count_clause2 => ''
1802: ,p_where_clause => 'from per_contracts_f c
1803: , per_person_types_tl pt
1804: , per_all_people_f p
1805: , per_assignment_status_types per_ast
1806: , per_all_assignments_f a
1807: , P_ESTABLISHMENT_TABLE
1845: OR exists
1846: (select
1847: null
1848: from per_contracts_f c2
1849: , per_person_types_tl pt2
1850: , per_all_people_f p2
1851: , per_assignment_status_types ast2
1852: , per_all_assignments_f a2
1853: where a2.person_id = p.person_id
1886: ,p_seeded_data => 'Y'
1887: ,p_count_clause2 => ''
1888: ,p_where_clause => 'from hr_soft_coding_keyflex scl
1889: , per_contracts_f c
1890: , per_person_types_tl pt
1891: , per_all_people_f p
1892: , per_assignment_status_types per_ast
1893: , per_all_assignments_f a
1894: , P_ESTABLISHMENT_TABLE
1934: OR exists
1935: (select null
1936: from hr_soft_coding_keyflex scl2
1937: , per_contracts_f c2
1938: , per_person_types_tl pt2
1939: , per_all_people_f p2
1940: , per_assignment_status_types ast2
1941: , per_all_assignments_f a2
1942: where a2.person_id = p.person_id
1977: ,p_count_clause2 => 'count(distinct p.person_id)'
1978: ,p_where_clause => 'from hr_soft_coding_keyflex scl
1979: , per_cagr_grades_def cagr_def
1980: , per_bf_balances_v bal
1981: , per_person_types_tl pt
1982: , per_all_people_f p
1983: , per_all_assignments_f a
1984: , (select ''01'' month from sys.dual
1985: union select ''02'' month from sys.dual
1994: union select ''11'' month from sys.dual
1995: union select ''12'' month from sys.dual) m
1996: where (p.effective_start_date,a.effective_start_date) =
1997: (select max(p2.effective_start_date),max(a2.effective_start_date)
1998: from per_person_types_tl pt2
1999: , per_all_people_f p2
2000: , per_assignment_status_types ast2
2001: , per_all_assignments_f a2
2002: , P_ESTABLISHMENT_TABLE
2029: add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1)
2030: and bal.run_amount is not null
2031: and exists
2032: (select null
2033: from per_person_types_tl pt3
2034: , per_all_people_f p3
2035: , per_assignment_status_types ast3
2036: , per_all_assignments_f a3
2037: , P_ESTABLISHMENT_TABLE
2063: ,p_count_clause1 => 'count(distinct(p.person_id))'
2064: ,p_seeded_data => 'Y'
2065: ,p_count_clause2 => ''
2066: ,p_where_clause => 'from per_contracts_f c
2067: , per_person_types_tl pt
2068: , per_all_people_f p
2069: , per_assignment_status_types per_ast
2070: , per_all_assignments_f a
2071: , P_ESTABLISHMENT_TABLE
2105: add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1)
2106: OR exists
2107: (select null
2108: from per_contracts_f c2
2109: , per_person_types_tl pt2
2110: , per_all_people_f p2
2111: , per_assignment_status_types ast2
2112: , per_all_assignments_f a2
2113: where a2.person_id = p.person_id
2143: ,p_datatype => 'N'
2144: ,p_count_clause1 => 'count(distinct(p.person_id))'
2145: ,p_seeded_data => 'Y'
2146: ,p_count_clause2 => ''
2147: ,p_where_clause => 'from per_person_types_tl pt
2148: , per_all_people_f p
2149: , per_assignment_status_types per_ast
2150: , per_all_assignments_f a
2151: , P_ESTABLISHMENT_TABLE
2178: and (least(a.effective_end_date,p.effective_end_date)
2179: > add_months(to_date(P_YEAR||m.month||''01'',''YYYYMMDD'')-1,1)
2180: OR exists
2181: (select null
2182: from per_person_types_tl pt2
2183: , per_all_people_f p2
2184: , per_assignment_status_types ast2
2185: , per_all_assignments_f a2
2186: where a2.person_id = p.person_id
2217: , per_all_assignments_f a
2218: where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
2219: and (p.effective_start_date,a.effective_start_date) =
2220: (select max(p2.effective_start_date),max(a2.effective_start_date)
2221: from per_person_types_tl pt2
2222: , per_all_people_f p2
2223: , per_assignment_status_types ast2
2224: , per_all_assignments_f a2
2225: , P_ESTABLISHMENT_TABLE
2297: , per_all_assignments_f a
2298: where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
2299: and (p.effective_start_date,a.effective_start_date) =
2300: (select max(p2.effective_start_date),max(a2.effective_start_date)
2301: from per_person_types_tl pt2
2302: , per_all_people_f p2
2303: , per_assignment_status_types ast2
2304: , per_all_assignments_f a2
2305: , P_ESTABLISHMENT_TABLE
2373: ,p_count_clause1 => 'count(distinct(p.person_id))'
2374: ,p_seeded_data => 'Y'
2375: ,p_count_clause2 => ''
2376: ,p_where_clause => 'from hr_soft_coding_keyflex scl
2377: , per_person_types_tl pt
2378: , per_all_people_f p
2379: , per_assignment_status_types per_ast
2380: , per_all_assignments_f a
2381: , P_ESTABLISHMENT_TABLE
2398: and (least(a.effective_end_date,p.effective_end_date) > P_END_OF_YEAR
2399: OR exists
2400: (select null
2401: from hr_soft_coding_keyflex scl2
2402: , per_person_types_tl pt2
2403: , per_all_people_f p2
2404: , per_assignment_status_types ast2
2405: , per_all_assignments_f a2
2406: where a2.soft_coding_keyflex_id = scl2.soft_coding_keyflex_id(+)
2439: where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
2440: and a.cagr_grade_def_id = cagr_def.cagr_grade_def_id(+)
2441: and (p.effective_start_date,a.effective_start_date) =
2442: (select max(p2.effective_start_date),max(a2.effective_start_date)
2443: from per_person_types_tl pt2
2444: , per_all_people_f p2
2445: , per_assignment_status_types ast2
2446: , per_all_assignments_f a2
2447: , P_ESTABLISHMENT_TABLE
2470: between P_START_OF_YEAR and P_END_OF_YEAR
2471: and bal.run_amount is not null
2472: and exists
2473: (select null
2474: from per_person_types_tl pt3
2475: , per_all_people_f p3
2476: , per_assignment_status_types ast3
2477: , per_all_assignments_f a3
2478: , P_ESTABLISHMENT_TABLE
2510: , per_all_assignments_f a
2511: where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
2512: and (p.effective_start_date,a.effective_start_date) =
2513: (select max(p2.effective_start_date),max(a2.effective_start_date)
2514: from per_person_types_tl pt2
2515: , per_all_people_f p2
2516: , per_assignment_status_types ast2
2517: , per_all_assignments_f a2
2518: , P_ESTABLISHMENT_TABLE
2542: between P_START_OF_YEAR and P_END_OF_YEAR
2543: and bal.run_amount is not null
2544: and exists
2545: (select null
2546: from per_person_types_tl pt3
2547: , per_all_people_f p3
2548: , per_assignment_status_types ast3
2549: , per_all_assignments_f a3
2550: , P_ESTABLISHMENT_TABLE
2583: where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
2584: and a.cagr_grade_def_id = cagr_def.cagr_grade_def_id(+)
2585: and (p.effective_start_date,a.effective_start_date) =
2586: (select max(p2.effective_start_date),max(a2.effective_start_date)
2587: from per_person_types_tl pt2
2588: , per_all_people_f p2
2589: , per_assignment_status_types ast2
2590: , per_all_assignments_f a2
2591: , P_ESTABLISHMENT_TABLE
2614: between P_START_OF_YEAR and P_END_OF_YEAR
2615: and bal.run_amount is not null
2616: and exists
2617: (select null
2618: from per_person_types_tl pt3
2619: , per_all_people_f p3
2620: , per_assignment_status_types ast3
2621: , per_all_assignments_f a3
2622: , P_ESTABLISHMENT_TABLE
2655: , per_all_assignments_f a
2656: where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
2657: and (p.effective_start_date,a.effective_start_date) =
2658: (select max(p2.effective_start_date),max(a2.effective_start_date)
2659: from per_person_types_tl pt2
2660: , per_all_people_f p2
2661: , per_assignment_status_types ast2
2662: , per_all_assignments_f a2
2663: , P_ESTABLISHMENT_TABLE
2686: between P_START_OF_YEAR and P_END_OF_YEAR
2687: and bal.run_amount is not null
2688: and exists
2689: (select null
2690: from per_person_types_tl pt3
2691: , per_all_people_f p3
2692: , per_assignment_status_types ast3
2693: , per_all_assignments_f a3
2694: , P_ESTABLISHMENT_TABLE
2728: where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
2729: and a.cagr_grade_def_id = cagr_def.cagr_grade_def_id(+)
2730: and (p.effective_start_date,a.effective_start_date) =
2731: (select max(p2.effective_start_date),max(a2.effective_start_date)
2732: from per_person_types_tl pt2
2733: , per_all_people_f p2
2734: , per_assignment_status_types ast2
2735: , per_all_assignments_f a2
2736: , P_ESTABLISHMENT_TABLE
2759: between P_START_OF_YEAR and P_END_OF_YEAR
2760: and bal.run_amount is not null
2761: and exists
2762: (select null
2763: from per_person_types_tl pt3
2764: , per_all_people_f p3
2765: , per_assignment_status_types ast3
2766: , per_all_assignments_f a3
2767: , P_ESTABLISHMENT_TABLE
2793: ,p_count_clause1 => 'count(distinct(p.person_id))'
2794: ,p_seeded_data => 'Y'
2795: ,p_count_clause2 => ''
2796: ,p_where_clause => '/*DBG a.normal_hours,a.employment_category DBGCOLS2 normal_hours emp_cat DBG*/ from hr_soft_coding_keyflex scl
2797: , per_person_types_tl pt
2798: , per_all_people_f p
2799: , per_assignment_status_types per_ast
2800: , per_all_assignments_f a
2801: , P_ESTABLISHMENT_TABLE
2818: and P_END_OF_YEAR <= p.effective_end_date
2819: and (least(a.effective_end_date,p.effective_end_date) > P_END_OF_YEAR
2820: OR exists (select null
2821: from hr_soft_coding_keyflex scl2
2822: , per_person_types_tl pt2
2823: ,
2824: per_all_people_f p2
2825: , per_assignment_status_types ast2
2826: , per_all_assignments_f a2
2862: where a.person_id = p.person_id
2863: and a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id (+)
2864: and (p.effective_start_date,a.effective_start_date) =
2865: (select max(p2.effective_start_date),max(a2.effective_start_date)
2866: from per_person_types_tl pt2
2867: , per_all_people_f p2
2868: , per_assignment_status_types ast2
2869: , per_all_assignments_f a2
2870: , P_ESTABLISHMENT_TABLE
2902: ,p_seeded_data => 'Y'
2903: ,p_count_clause2 => ''
2904: ,p_where_clause => 'from hr_soft_coding_keyflex scl
2905: , per_contracts_f c
2906: , per_person_types_tl pt
2907: , per_all_people_f p
2908: , per_assignment_status_types per_ast
2909: , per_all_assignments_f a
2910: , P_ESTABLISHMENT_TABLE
2954: from per_contracts_f
2955: c3
2956: ,per_assignment_status_types ast3
2957: ,per_all_assignments_f a3
2958: ,per_person_types_tl pt3
2959: ,per_all_people_f p3
2960: where p3.person_id = p.person_id
2961: and userenv(''lang'')=pt3.language
2962: and a3.person_id = p3.person_id
3016: where a.person_id = p.person_id
3017: and a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id (+)
3018: and (p.effective_start_date,a.effective_start_date) =
3019: (select max(p2.effective_start_date),max(a2.effective_start_date)
3020: from per_person_types_tl pt2
3021: , per_all_people_f p2
3022: , per_assignment_status_types ast2
3023: , per_all_assignments_f a2
3024: , P_ESTABLISHMENT_TABLE
3063: , per_all_assignments_f a
3064: where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
3065: and (p.effective_start_date,a.effective_start_date) =
3066: (select max(p2.effective_start_date),max(a2.effective_start_date)
3067: from per_person_types_tl pt2
3068: , per_all_people_f p2
3069: , per_assignment_status_types ast2
3070: , per_all_assignments_f a2
3071: , P_ESTABLISHMENT_TABLE
3150: where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id
3151: and (p.effective_start_date,a.effective_start_date) =
3152: (select max(p2.effective_start_date),max(a2.effective_start_date)
3153: from hr_soft_coding_keyflex scl4
3154: , per_person_types_tl pt2
3155: , per_all_people_f p2
3156: , per_assignment_status_types ast2
3157: , per_all_assignments_f a2
3158: , P_ESTABLISHMENT_TABLE
3214: , per_all_assignments_f a
3215: , (select sum(bal2.run_amount) annual_remuneration
3216: , bal2.assignment_id assignment_id
3217: from per_bf_balances_v bal2
3218: , per_person_types_tl pt2
3219: , per_all_people_f p2
3220: , per_assignment_status_types ast2
3221: , per_all_assignments_f a2
3222: , P_ESTABLISHMENT_TABLE
3241: RESTRICTION.BALANCE_NAME[bal2]
3242: group by bal2.assignment_id) remuneration
3243: where (p.effective_start_date,a.effective_start_date) =
3244: (select max(p3.effective_start_date),max(a3.effective_start_date)
3245: from per_person_types_tl pt3
3246: , per_all_people_f p3
3247: , P_ESTABLISHMENT_TABLE
3248: , per_assignment_status_types ast3
3249: , per_all_assignments_f a3
3279: ,p_count_clause1 => 'count(*)'
3280: ,p_seeded_data => 'Y'
3281: ,p_count_clause2 => ''
3282: ,p_where_clause => 'from per_people_extra_info pex,
3283: per_person_types_tl pt,
3284: P_ESTABLISHMENT_TABLE ,
3285: per_all_people_f p,
3286: per_assignment_status_types per_ast,
3287: per_all_assignments_f a
3306: and P_END_OF_YEAR <= p.effective_end_date
3307: and (least(a.effective_end_date,p.effective_end_date) > P_END_OF_YEAR
3308: OR exists (select
3309: null
3310: from per_person_types_tl pt2
3311: , per_all_people_f p2
3312: , per_assignment_status_types ast2
3313: , per_all_assignments_f a2
3314: where a2.person_id = p.person_id
3337: ,p_count_clause1 => 'sum(to_number(pex.pei_information8)* decode(pex.pei_information9,''Y'',1,''M'',12,''W'',52,''D'',200,''H'',1600))'
3338: ,p_seeded_data => 'Y'
3339: ,p_count_clause2 => ''
3340: ,p_where_clause => 'from per_people_extra_info pex
3341: , per_person_types_tl pt
3342: , per_all_people_f p
3343: , per_assignment_status_types per_ast
3344: , per_all_assignments_f a
3345: , P_ESTABLISHMENT_TABLE
3365: and P_END_OF_YEAR <= p.effective_end_date
3366: and (least(a.effective_end_date,p.effective_end_date) >
3367: P_END_OF_YEAR
3368: OR exists (select null
3369: from per_person_types_tl pt2
3370: , per_all_people_f p2
3371: , per_assignment_status_types ast2
3372: , per_all_assignments_f a2
3373: where a2.person_id = p.person_id
3396: ,p_count_clause1 => 'count(distinct(p.person_id))'
3397: ,p_seeded_data => 'Y'
3398: ,p_count_clause2 => ''
3399: ,p_where_clause => 'from hr_soft_coding_keyflex scl
3400: , per_person_types_tl pt
3401: , per_all_people_f p
3402: , per_assignment_status_types per_ast
3403: , per_all_assignments_f a
3404: , P_ESTABLISHMENT_TABLE
3421: and (least(a.effective_end_date,p.effective_end_date) > P_END_OF_YEAR
3422: OR exists
3423: (select null
3424: from hr_soft_coding_keyflex scl2
3425: , per_person_types_tl pt2
3426: , per_all_people_f p2
3427: ,
3428: per_assignment_status_types ast2
3429: , per_all_assignments_f a2
3456: ,p_count_clause1 => 'count(distinct(p.person_id))'
3457: ,p_seeded_data => 'Y'
3458: ,p_count_clause2 => ''
3459: ,p_where_clause => 'from hr_soft_coding_keyflex scl
3460: , per_person_types_tl pt
3461: , per_all_people_f p
3462: , per_assignment_status_types per_ast
3463: , per_all_assignments_f a
3464: , P_ESTABLISHMENT_TABLE
3483: OR exists
3484: (select null
3485: from hr_soft_coding_keyflex scl2
3486: ,
3487: per_person_types_tl pt2
3488: , per_all_people_f p2
3489: , per_assignment_status_types ast2
3490: , per_all_assignments_f a2
3491: where a2.person_id = p.person_id
3522: , per_all_people_f p
3523: , per_all_assignments_f a
3524: where (p.effective_start_date,a.effective_start_date) =
3525: (select max(p2.effective_start_date),max(a2.effective_start_date)
3526: from per_person_types_tl pt2
3527: , per_all_people_f p2
3528: , per_assignment_status_types ast2
3529: , per_all_assignments_f a2
3530: , P_ESTABLISHMENT_TABLE
3567: (min(greatest(p5.effective_start_date,a5.effective_start_date,P_START_OF_YEAR))
3568: <= least(p.effective_end_date,a.effective_end_date,P_END_OF_YEAR) -7)
3569: and count(*) =
3570: (select count(*)
3571: from per_person_types_tl pt3
3572: , per_all_people_f p3
3573: , per_assignment_status_types ast3
3574: , per_all_assignments_f a3
3575: , P_ESTABLISHMENT_TABLE
3627: and per_pos.period_of_service_id = a.period_of_service_id
3628: and a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
3629: and (p.effective_start_date,a.effective_start_date) =
3630: (select max(p2.effective_start_date),max(a2.effective_start_date)
3631: from per_person_types_tl pt2
3632: , per_all_people_f p2
3633: , per_assignment_status_types ast2
3634: , per_all_assignments_f a2
3635: , P_ESTABLISHMENT_TABLE
3671: where a.person_id = p.person_id
3672: and a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id (+)
3673: and (p.effective_start_date,a.effective_start_date) =
3674: (select max(p2.effective_start_date),max(a2.effective_start_date)
3675: from per_person_types_tl pt2
3676: , per_all_people_f p2
3677: , per_assignment_status_types ast2
3678: , per_all_assignments_f a2
3679: , P_ESTABLISHMENT_TABLE
3720: where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
3721: and a.cagr_grade_def_id = cagr_def.cagr_grade_def_id(+)
3722: and (p.effective_start_date,a.effective_start_date) =
3723: (select max(p2.effective_start_date),max(a2.effective_start_date)
3724: from per_person_types_tl pt2
3725: , per_all_people_f p2
3726: , per_assignment_status_types ast2
3727: , per_all_assignments_f a2
3728: , P_ESTABLISHMENT_TABLE
3751: between P_START_OF_YEAR and P_END_OF_YEAR
3752: and bal.run_amount is not null
3753: and exists
3754: (select null
3755: from per_person_types_tl pt3
3756: , per_all_people_f p3
3757: , per_assignment_status_types ast3
3758: , per_all_assignments_f a3
3759: , P_ESTABLISHMENT_TABLE
3793: where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
3794: and a.cagr_grade_def_id = cagr_def.cagr_grade_def_id(+)
3795: and (p.effective_start_date,a.effective_start_date) =
3796: (select max(p2.effective_start_date),max(a2.effective_start_date)
3797: from per_person_types_tl pt2
3798: , per_all_people_f p2
3799: , per_assignment_status_types ast2
3800: , per_all_assignments_f a2
3801: , P_ESTABLISHMENT_TABLE
3824: between P_START_OF_YEAR and P_END_OF_YEAR
3825: and bal.run_amount is not null
3826: and exists
3827: (select null
3828: from per_person_types_tl pt3
3829: , per_all_people_f p3
3830: , per_assignment_status_types ast3
3831: , per_all_assignments_f a3
3832: , P_ESTABLISHMENT_TABLE
3859: ,p_seeded_data => 'Y'
3860: ,p_count_clause2 => ''
3861: ,p_where_clause => '/*DBG substr(p.full_name,1,80) DBGCOLS1 name DBG*/ from hr_soft_coding_keyflex scl
3862: ,per_cagr_grades_def cagr_def
3863: ,per_person_types_tl pt
3864: ,per_all_people_f p
3865: ,per_periods_of_service per_pos
3866: ,per_assignment_status_types per_ast
3867: ,per_all_assignments_f a
3887: OR exists
3888: (select null
3889: from hr_soft_coding_keyflex scl2
3890: ,per_cagr_grades_def cagr_def2
3891: ,per_person_types_tl pt2
3892: ,per_all_people_f p2
3893: ,per_periods_of_service pos2
3894: ,per_assignment_status_types ast2
3895: ,per_all_assignments_f a2
3929: ,per_all_assignments_f a
3930: where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
3931: and (p.effective_start_date,a.effective_start_date) =
3932: (select max(p2.effective_start_date),max(a2.effective_start_date)
3933: from per_person_types_tl pt2
3934: , per_all_people_f p2
3935: , per_assignment_status_types ast2
3936: , per_all_assignments_f a2
3937: , P_ESTABLISHMENT_TABLE
3959: between P_START_OF_YEAR and P_END_OF_YEAR
3960: and bal.run_amount is not null
3961: and exists
3962: (select null
3963: from per_person_types_tl pt3
3964: , per_all_people_f p3
3965: , per_assignment_status_types ast3
3966: , per_all_assignments_f a3
3967: , P_ESTABLISHMENT_TABLE
4004: ,per_all_assignments_f asg
4005: where asg.assignment_id = time.assignment_id
4006: and exists
4007: (select null
4008: from per_person_types_tl pt3
4009: ,per_all_people_f p3
4010: ,per_assignment_status_types ast3
4011: ,per_all_assignments_f a3
4012: ,P_ESTABLISHMENT_TABLE
4035: ,per_all_assignments_f asg
4036: where asg.assignment_id = weeks.assignment_id
4037: and exists
4038: (select null
4039: from per_person_types_tl pt3
4040: ,per_all_people_f p3
4041: ,per_assignment_status_types ast3
4042: ,per_all_assignments_f a3
4043: ,P_ESTABLISHMENT_TABLE
4059: ) balances
4060: where a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id(+)
4061: and (p.effective_start_date,a.effective_start_date) =
4062: (select max(p2.effective_start_date),max(a2.effective_start_date)
4063: from per_person_types_tl pt2
4064: ,per_all_people_f p2
4065: ,per_assignment_status_types ast2
4066: ,per_all_assignments_f a2
4067: ,P_ESTABLISHMENT_TABLE
4107: where a.person_id = p.person_id
4108: and a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id (+)
4109: and (p.effective_start_date,a.effective_start_date) =
4110: (select max(p2.effective_start_date),max(a2.effective_start_date)
4111: from per_person_types_tl pt2
4112: , per_all_people_f p2
4113: , per_assignment_status_types ast2
4114: , per_all_assignments_f a2
4115: , P_ESTABLISHMENT_TABLE
4147: ,p_count_clause1 => 'count(distinct(p.person_id))'
4148: ,p_seeded_data => 'Y'
4149: ,p_count_clause2 => ''
4150: ,p_where_clause => 'from hr_soft_coding_keyflex scl
4151: , per_person_types_tl pt
4152: , per_all_people_f p
4153: , per_assignment_status_types per_ast
4154: , per_all_assignments_f a
4155: , P_ESTABLISHMENT_TABLE
4186: <= P_START_OF_YEAR)
4187: and count(*) =
4188: (select count(*)
4189: from hr_soft_coding_keyflex scl3
4190: ,per_person_types_tl pt3
4191: ,per_all_people_f p3
4192: ,per_assignment_status_types ast3
4193: ,per_all_assignments_f a3
4194: where p3.person_id = p.person_id
4243: where a.person_id = p.person_id
4244: and a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id (+)
4245: and (p.effective_start_date,a.effective_start_date) =
4246: (select max(p2.effective_start_date),max(a2.effective_start_date)
4247: from per_person_types_tl pt2
4248: , per_all_people_f p2
4249: , per_assignment_status_types ast2
4250: , per_all_assignments_f a2
4251: , P_ESTABLISHMENT_TABLE
4289: where a.person_id = p.person_id
4290: and a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id (+)
4291: and (p.effective_start_date,a.effective_start_date) =
4292: (select max(p2.effective_start_date),max(a2.effective_start_date)
4293: from per_person_types_tl pt2
4294: , per_all_people_f p2
4295: , per_assignment_status_types ast2
4296: , per_all_assignments_f a2
4297: , P_ESTABLISHMENT_TABLE
4336: where a.person_id = p.person_id
4337: and a.soft_coding_keyflex_id = scl.soft_coding_keyflex_id (+)
4338: and (p.effective_start_date,a.effective_start_date) =
4339: (select max(p2.effective_start_date),max(a2.effective_start_date)
4340: from per_person_types_tl pt2
4341: , per_all_people_f p2
4342: , per_assignment_status_types ast2
4343: , per_all_assignments_f a2
4344: , P_ESTABLISHMENT_TABLE
4363: between P_START_OF_YEAR and P_END_OF_YEAR
4364: and bal.run_amount is not null
4365: and exists
4366: (select null
4367: from per_person_types_tl pt3
4368: , per_all_people_f p3
4369: , per_assignment_status_types ast3
4370: , per_all_assignments_f a3
4371: , P_ESTABLISHMENT_TABLE
4400: ,p_where_clause => 'from per_all_people_f p
4401: , per_all_assignments_f a
4402: where (p.effective_start_date,a.effective_start_date) =
4403: (select max(p2.effective_start_date),max(a2.effective_start_date)
4404: from per_person_types_tl pt2
4405: , per_all_people_f p2
4406: , per_assignment_status_types ast2
4407: , per_all_assignments_f a2
4408: , P_ESTABLISHMENT_TABLE
4640: ,p_seeded_data => 'Y'
4641: ,p_restriction_clause => 'hr_person_type_usage_info.get_user_person_type(aliasreplace.effective_start_date, aliasreplace.person_id)'
4642: ,p_restriction_sql => 'select t.user_person_type VALUE
4643: ,t.user_person_type MEANING
4644: from per_person_types_tl t,
4645: per_person_types b
4646: where b.business_group_id=:ctl_globals.business_group_id
4647: and b.person_type_id=t.person_type_id
4648: and userenv(''lang'')=t.language');
4641: ,p_restriction_clause => 'hr_person_type_usage_info.get_user_person_type(aliasreplace.effective_start_date, aliasreplace.person_id)'
4642: ,p_restriction_sql => 'select t.user_person_type VALUE
4643: ,t.user_person_type MEANING
4644: from per_person_types_tl t,
4645: per_person_types b
4646: where b.business_group_id=:ctl_globals.business_group_id
4647: and b.person_type_id=t.person_type_id
4648: and userenv(''lang'')=t.language');
4649: --
4839: ,p_seeded_data => 'Y'
4840: ,p_restriction_clause => 'user_person_type'
4841: ,p_restriction_sql => 'select t.user_person_type VALUE
4842: ,t.user_person_type MEANING
4843: from per_person_types_tl t,
4844: per_person_types b
4845: where b.business_group_id=:ctl_globals.business_group_id
4846: and b.person_type_id=t.person_type_id
4847: and userenv(''lang'')=t.language');
4840: ,p_restriction_clause => 'user_person_type'
4841: ,p_restriction_sql => 'select t.user_person_type VALUE
4842: ,t.user_person_type MEANING
4843: from per_person_types_tl t,
4844: per_person_types b
4845: where b.business_group_id=:ctl_globals.business_group_id
4846: and b.person_type_id=t.person_type_id
4847: and userenv(''lang'')=t.language');
4848: --