[Home] [Help]
1176: is
1177: select /*+ ORDERED */
1178: pec.classification_name
1179: from pay_assignment_actions paa,
1180: pay_payroll_actions ppa,
1181: pay_run_results prr,
1182: pay_element_types_f pet,
1183: pay_element_classifications pec
1184: where paa.assignment_action_id = p_assignment_action_id
1262: is
1263: select /*+ ORDERED */
1264: paa_yea.assignment_action_id
1265: from pay_assignment_actions paa_sal,
1266: pay_payroll_actions ppa_sal,
1267: pay_action_interlocks pai_sal,
1268: pay_pre_payments ppp_pp,
1269: pay_assignment_actions paa_pp,
1270: pay_payroll_actions ppa_pp,
1266: pay_payroll_actions ppa_sal,
1267: pay_action_interlocks pai_sal,
1268: pay_pre_payments ppp_pp,
1269: pay_assignment_actions paa_pp,
1270: pay_payroll_actions ppa_pp,
1271: pay_action_interlocks pai_yea,
1272: pay_assignment_actions paa_yea,
1273: pay_payroll_actions ppa_yea
1274: where paa_sal.assignment_action_id = p_assignment_action_id
1269: pay_assignment_actions paa_pp,
1270: pay_payroll_actions ppa_pp,
1271: pay_action_interlocks pai_yea,
1272: pay_assignment_actions paa_yea,
1273: pay_payroll_actions ppa_yea
1274: where paa_sal.assignment_action_id = p_assignment_action_id
1275: and ppa_sal.payroll_action_id = paa_sal.payroll_action_id
1276: and pai_sal.locked_action_id = paa_sal.assignment_action_id
1277: and ppp_pp.assignment_action_id = pai_sal.locking_action_id
1298: select /*+ ORDERED */
1299: null
1300: from pay_action_interlocks pai_v,
1301: pay_assignment_actions paa_v,
1302: pay_payroll_actions ppa_v
1303: where pai_v.locked_action_id = paa_yea.assignment_action_id
1304: and paa_v.assignment_action_id = pai_v.locking_action_id
1305: and ppa_v.payroll_action_id = paa_v.payroll_action_id
1306: and ppa_v.action_type = 'V')
1472: is
1473: select /*+ ORDERED */
1474: paa_yea.assignment_action_id
1475: from pay_assignment_actions paa_bon,
1476: pay_payroll_actions ppa_bon,
1477: pay_action_interlocks pai_bon,
1478: pay_pre_payments ppp_pp,
1479: pay_assignment_actions paa_pp,
1480: pay_payroll_actions ppa_pp,
1476: pay_payroll_actions ppa_bon,
1477: pay_action_interlocks pai_bon,
1478: pay_pre_payments ppp_pp,
1479: pay_assignment_actions paa_pp,
1480: pay_payroll_actions ppa_pp,
1481: pay_action_interlocks pai_yea,
1482: pay_assignment_actions paa_yea,
1483: pay_payroll_actions ppa_yea
1484: where paa_bon.assignment_action_id = p_assignment_action_id
1479: pay_assignment_actions paa_pp,
1480: pay_payroll_actions ppa_pp,
1481: pay_action_interlocks pai_yea,
1482: pay_assignment_actions paa_yea,
1483: pay_payroll_actions ppa_yea
1484: where paa_bon.assignment_action_id = p_assignment_action_id
1485: and ppa_bon.payroll_action_id = paa_bon.payroll_action_id
1486: and pai_bon.locked_action_id = paa_bon.assignment_action_id
1487: and ppp_pp.assignment_action_id = pai_bon.locking_action_id
1511: select /*+ ORDERED */
1512: null
1513: from pay_action_interlocks pai_v,
1514: pay_assignment_actions paa_v,
1515: pay_payroll_actions ppa_v
1516: where pai_v.locked_action_id = paa_yea.assignment_action_id
1517: and paa_v.assignment_action_id = pai_v.locking_action_id
1518: and ppa_v.payroll_action_id = paa_v.payroll_action_id
1519: and ppa_v.action_type = 'V')
1521: select /*+ ORDERED */
1522: null
1523: from pay_action_interlocks pai_sal,
1524: pay_assignment_actions paa_sal,
1525: pay_payroll_actions ppa_sal
1526: where pai_sal.locking_action_id = ppp_pp.assignment_action_id
1527: and paa_sal.assignment_action_id = pai_sal.locked_action_id
1528: and paa_sal.action_status = 'C'
1529: and ppa_sal.payroll_action_id = paa_sal.payroll_action_id
1760: and hou.date_from <= g_effective_eoy
1761: and nvl(hou.date_to,hr_api.g_eot) >= g_effective_soy
1762: and not exists(
1763: select null
1764: from pay_payroll_actions ppa,
1765: pay_jp_itax_pact_v2 pjip
1766: where ppa.business_group_id + 0 = g_business_group_id
1767: and ppa.effective_date
1768: between g_effective_soy and g_effective_eoy
1890: --fnd_number.canonical_to_number(pjit.housing_residence_day) housing_residence_day
1891: from pay_assignment_actions paa_yea,
1892: pay_assignment_actions paa_yea_all,
1893: pay_assignment_actions paa_iarch,
1894: pay_payroll_actions ppa_iarch,
1895: pay_jp_itax_person_v2 pjip,
1896: pay_jp_itax_arch_v2 pjia,
1897: pay_jp_itax_tax_v pjit,
1898: pay_jp_itax_other_v pjio
1952: and pjwa.itax_yea_category = '0'
1953: and not exists(
1954: select null
1955: from pay_assignment_actions paa_iarch,
1956: pay_payroll_actions ppa_iarch,
1957: pay_jp_itax_person_v2 pjip,
1958: pay_jp_itax_arch_v2 pjia
1959: where paa_iarch.assignment_id = paa_yea.assignment_id
1960: and ppa_iarch.payroll_action_id = paa_iarch.payroll_action_id
5369: ppa.business_group_id,
5370: ppa.effective_date,
5371: ppa.legislative_parameters,
5372: pbg.legislation_code
5373: from pay_payroll_actions ppa,
5374: per_business_groups_perf pbg
5375: where ppa.payroll_action_id = p_payroll_action_id
5376: and pbg.business_group_id = ppa.business_group_id;
5377: --
5379: is
5380: select /*+ ORDERED */
5381: pjip.payroll_action_id,
5382: pjip.archive_sequence
5383: from pay_payroll_actions ppa, -- proc pact
5384: pay_jp_itwb_pact_v pjip -- source pact
5385: where ppa.business_group_id + 0 = g_business_group_id
5386: and ppa.effective_date
5387: between g_effective_soy and decode(g_arch_option,'ADD',g_effective_som - 1,g_effective_eoy)
5401: and pjia.assignment_action_id = paa.assignment_action_id)
5402: and not exists(
5403: select /*+ ORDERED */
5404: null
5405: from pay_payroll_actions ppa2,
5406: pay_jp_itwb_pact_v pjip2
5407: where ppa2.business_group_id + 0 = g_business_group_id
5408: and ppa2.effective_date
5409: between g_effective_soy and decode(g_arch_option,'ADD',g_effective_som - 1,g_effective_eoy)
5429: cursor csr_arch_seq
5430: is
5431: select /*+ ORDERED */
5432: pjip.archive_sequence
5433: from pay_payroll_actions ppa, -- proc pact
5434: pay_jp_itwb_pact_v pjip -- source pact
5435: where ppa.business_group_id + 0 = g_business_group_id
5436: and ppa.effective_date
5437: between g_effective_soy and decode(g_arch_option,'ADD',g_effective_som - 1,g_effective_eoy)
5445: and pjip.archive_sequence >= l_archive_sequence_o
5446: and not exists(
5447: select /*+ ORDERED */
5448: null
5449: from pay_payroll_actions ppa2,
5450: pay_jp_itwb_pact_v pjip2
5451: where ppa2.business_group_id + 0 = g_business_group_id
5452: and ppa2.effective_date
5453: between g_effective_soy and decode(g_arch_option,'ADD',g_effective_som - 1,g_effective_eoy)
6029: end if;
6030: --
6031: p_sqlstr :=
6032: 'select distinct ppos.person_id
6033: from pay_payroll_actions ppa,
6034: per_all_people_f pp,
6035: per_periods_of_service ppos
6036: where ppa.payroll_action_id = :payroll_action_id
6037: and pp.business_group_id = ppa.business_group_id + 0
6088: is
6089: select /*+ ORDERED */
6090: pa.person_id,
6091: paa.assignment_id
6092: from pay_payroll_actions ppa,
6093: pay_assignment_actions paa,
6094: per_all_assignments_f pa,
6095: pay_link_input_values_f pliv_itax_org,
6096: pay_link_input_values_f pliv_itax_type,
6128: select /*+ ORDERED */
6129: null
6130: from pay_action_interlocks pai,
6131: pay_assignment_actions paav,
6132: pay_payroll_actions ppav
6133: where pai.locked_action_id = paa.assignment_action_id
6134: and paav.assignment_action_id = pai.locking_action_id
6135: and ppav.payroll_action_id = paav.payroll_action_id
6136: and ppav.action_type = 'V')
6160: and peev_itax_type.screen_entry_value <> 'E'
6161: and not exists(
6162: select /*+ ORDERED */
6163: null
6164: from pay_payroll_actions ppa2,
6165: pay_assignment_actions paa2,
6166: pay_link_input_values_f pliv_itax_org2,
6167: pay_link_input_values_f pliv_itax_type2,
6168: pay_element_entries_f pee_itax_org2,
6196: select /*+ ORDERED */
6197: null
6198: from pay_action_interlocks pai2,
6199: pay_assignment_actions paav2,
6200: pay_payroll_actions ppav2
6201: where pai2.locked_action_id = paa2.assignment_action_id
6202: and paav2.assignment_action_id = pai2.locking_action_id
6203: and ppav2.payroll_action_id = paav2.payroll_action_id
6204: and ppav2.action_type = 'V')
6238: from (select /*+ ORDERED */
6239: pa.assignment_id,
6240: pa.person_id,
6241: ppa.effective_date
6242: from pay_payroll_actions ppa,
6243: pay_assignment_actions paa,
6244: per_all_assignments_f pa,
6245: pay_link_input_values_f pliv_itax_org,
6246: pay_link_input_values_f pliv_itax_type,
6275: select /*+ ORDERED */
6276: null
6277: from pay_action_interlocks pai,
6278: pay_assignment_actions paav,
6279: pay_payroll_actions ppav
6280: where pai.locked_action_id = paa.assignment_action_id
6281: and paav.assignment_action_id = pai.locking_action_id
6282: and ppav.payroll_action_id = paav.payroll_action_id
6283: and ppav.action_type = 'V')
6310: and peev_itax_type.screen_entry_value <> 'E'
6311: and not exists(
6312: select /*+ ORDERED */
6313: null
6314: from pay_payroll_actions ppa2,
6315: pay_assignment_actions paa2,
6316: pay_link_input_values_f pliv_itax_org2,
6317: pay_link_input_values_f pliv_itax_type2,
6318: pay_element_entries_f pee_itax_org2,
6346: select /*+ ORDERED */
6347: null
6348: from pay_action_interlocks pai2,
6349: pay_assignment_actions paav2,
6350: pay_payroll_actions ppav2
6351: where pai2.locked_action_id = paa2.assignment_action_id
6352: and paav2.assignment_action_id = pai2.locking_action_id
6353: and ppav2.payroll_action_id = paav2.payroll_action_id
6354: and ppav2.action_type = 'V')
6380: and paa2.action_sequence > paa.action_sequence)) larch_v,
6381: pay_jp_itwb_assact_v pjia,
6382: pay_assignment_actions paa_o,
6383: pay_assignment_actions lpaa,
6384: pay_payroll_actions lppa
6385: where pjia.assignment_id (+) = larch_v.assignment_id
6386: and pjia.effective_date (+)
6387: between g_effective_soy and g_effective_som - 1
6388: and paa_o.assignment_action_id (+) = pjia.assignment_action_id
6411: from (select /*+ ORDERED */
6412: pa.assignment_id,
6413: pa.person_id,
6414: ppa.effective_date
6415: from pay_payroll_actions ppa,
6416: pay_assignment_actions paa,
6417: per_all_assignments_f pa,
6418: pay_link_input_values_f pliv_itax_org,
6419: pay_link_input_values_f pliv_itax_type,
6448: select /*+ ORDERED */
6449: null
6450: from pay_action_interlocks pai,
6451: pay_assignment_actions paav,
6452: pay_payroll_actions ppav
6453: where pai.locked_action_id = paa.assignment_action_id
6454: and paav.assignment_action_id = pai.locking_action_id
6455: and ppav.payroll_action_id = paav.payroll_action_id
6456: and ppav.action_type = 'V')
6484: and peev_itax_type.screen_entry_value <> 'E'
6485: and not exists(
6486: select /*+ ORDERED */
6487: null
6488: from pay_payroll_actions ppa2,
6489: pay_assignment_actions paa2,
6490: per_all_assignments_f pa2,
6491: pay_link_input_values_f pliv_itax_org2,
6492: pay_link_input_values_f pliv_itax_type2,
6521: select /*+ ORDERED */
6522: null
6523: from pay_action_interlocks pai2,
6524: pay_assignment_actions paav2,
6525: pay_payroll_actions ppav2
6526: where pai2.locked_action_id = paa2.assignment_action_id
6527: and paav2.assignment_action_id = pai2.locking_action_id
6528: and ppav2.payroll_action_id = paav2.payroll_action_id
6529: and ppav2.action_type = 'V')
6585: from (select /*+ ORDERED */
6586: pa.assignment_id,
6587: pa.person_id,
6588: ppa.effective_date
6589: from pay_payroll_actions ppa,
6590: pay_assignment_actions paa,
6591: per_all_assignments_f pa,
6592: pay_link_input_values_f pliv_itax_org,
6593: pay_link_input_values_f pliv_itax_type,
6622: select /*+ ORDERED */
6623: null
6624: from pay_action_interlocks pai,
6625: pay_assignment_actions paav,
6626: pay_payroll_actions ppav
6627: where pai.locked_action_id = paa.assignment_action_id
6628: and paav.assignment_action_id = pai.locking_action_id
6629: and ppav.payroll_action_id = paav.payroll_action_id
6630: and ppav.action_type = 'V')
6658: and peev_itax_type.screen_entry_value <> 'E'
6659: and not exists(
6660: select /*+ ORDERED */
6661: null
6662: from pay_payroll_actions ppa2,
6663: pay_assignment_actions paa2,
6664: per_all_assignments_f pa2,
6665: pay_link_input_values_f pliv_itax_org2,
6666: pay_link_input_values_f pliv_itax_type2,
6695: select /*+ ORDERED */
6696: null
6697: from pay_action_interlocks pai2,
6698: pay_assignment_actions paav2,
6699: pay_payroll_actions ppav2
6700: where pai2.locked_action_id = paa2.assignment_action_id
6701: and paav2.assignment_action_id = pai2.locking_action_id
6702: and ppav2.payroll_action_id = paav2.payroll_action_id
6703: and ppav2.action_type = 'V')
7457: ppy.payroll_name
7458: from (select /*+ ORDERED
7459: USE_NL(PAA, PPA)
7460: INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N1)
7461: INDEX(PPA PAY_PAYROLL_ACTIONS_PK) */
7462: paa_arch.assignment_action_id arch_assignment_action_id,
7463: paa.assignment_id,
7464: paa.assignment_action_id proc_assignment_action_id,
7465: paa.action_sequence,
7481: c_yea_cust_ele_set_id,'YEA',
7482: c_rey_cust_ele_set_id,'REY',
7483: -1,pay_jp_itwb_archive_pkg.get_fuzzy_proc_type(paa.assignment_action_id,ppa.action_type),null),null) proc_type,
7484: ppa.element_set_id
7485: from pay_payroll_actions ppa_arch,
7486: pay_assignment_actions paa_arch,
7487: pay_assignment_actions paa,
7488: pay_payroll_actions ppa,
7489: pay_link_input_values_f pliv_itax_org,
7484: ppa.element_set_id
7485: from pay_payroll_actions ppa_arch,
7486: pay_assignment_actions paa_arch,
7487: pay_assignment_actions paa,
7488: pay_payroll_actions ppa,
7489: pay_link_input_values_f pliv_itax_org,
7490: pay_link_input_values_f pliv_itax_type,
7491: pay_element_entries_f pee_itax_org,
7492: pay_element_entries_f pee_itax_type,
7521: select /*+ ORDERED */
7522: null
7523: from pay_action_interlocks pai,
7524: pay_assignment_actions paav,
7525: pay_payroll_actions ppav
7526: where pai.locked_action_id = paa.assignment_action_id
7527: and paav.assignment_action_id = pai.locking_action_id
7528: and ppav.payroll_action_id = paav.payroll_action_id
7529: and ppav.action_type = 'V')
7554: /* --this part was removed for redundant validation
7555: --and not exists(
7556: -- select /*+ ORDERED */
7557: -- null
7558: -- from pay_payroll_actions ppa2,
7559: -- pay_assignment_actions paa2,
7560: -- pay_link_input_values_f pliv_itax_org2,
7561: -- pay_link_input_values_f pliv_itax_type2,
7562: -- pay_element_entries_f pee_itax_org2,
7582: -- select /*+ ORDERED */
7583: -- null
7584: -- from pay_action_interlocks pai2,
7585: -- pay_assignment_actions paav2,
7586: -- pay_payroll_actions ppav2
7587: -- where pai2.locked_action_id = paa2.assignment_action_id
7588: -- and paav2.assignment_action_id = pai2.locking_action_id
7589: -- and ppav2.payroll_action_id = paav2.payroll_action_id
7590: -- and ppav2.action_type = 'V')
7668: proc_v.larch_assignment_action_id
7669: from (select /*+ ORDERED
7670: USE_NL(PAA, PPA)
7671: INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N1)
7672: INDEX(PPA PAY_PAYROLL_ACTIONS_PK) */
7673: larch_v.arch_assignment_action_id,
7674: paa.assignment_id,
7675: ppa.payroll_id,
7676: paa.assignment_action_id proc_assignment_action_id,
7698: paa_arch.assignment_action_id arch_assignment_action_id,
7699: paa_arch.assignment_id,
7700: pjia.assignment_action_id larch_assignment_action_id,
7701: lppa.effective_date lproc_effective_date
7702: from pay_payroll_actions ppa_arch,
7703: pay_assignment_actions paa_arch,
7704: pay_jp_itwb_assact_v pjia,
7705: pay_assignment_actions paa_o,
7706: pay_assignment_actions lpaa,
7703: pay_assignment_actions paa_arch,
7704: pay_jp_itwb_assact_v pjia,
7705: pay_assignment_actions paa_o,
7706: pay_assignment_actions lpaa,
7707: pay_payroll_actions lppa
7708: where ppa_arch.payroll_action_id = p_payroll_action_id
7709: and paa_arch.payroll_action_id = ppa_arch.payroll_action_id
7710: and pjia.assignment_id (+) = paa_arch.assignment_id
7711: and pjia.effective_date (+)
7723: between g_effective_soy and g_effective_som - 1
7724: and paa_o2.assignment_action_id = pjia2.assignment_action_id
7725: and paa_o2.action_sequence > paa_o.action_sequence)) larch_v,
7726: pay_assignment_actions paa,
7727: pay_payroll_actions ppa,
7728: pay_link_input_values_f pliv_itax_org,
7729: pay_link_input_values_f pliv_itax_type,
7730: pay_element_entries_f pee_itax_org,
7731: pay_element_entries_f pee_itax_type,
7760: select /*+ ORDERED */
7761: null
7762: from pay_action_interlocks pai,
7763: pay_assignment_actions paav,
7764: pay_payroll_actions ppav
7765: where pai.locked_action_id = paa.assignment_action_id
7766: and paav.assignment_action_id = pai.locking_action_id
7767: and ppav.payroll_action_id = paav.payroll_action_id
7768: and ppav.action_type = 'V')
7794: --and not exists(
7795: -- select /*+ ORDERED */
7796: -- null
7797: -- from pay_assignment_actions paa2,
7798: -- pay_payroll_actions ppa2,
7799: -- pay_link_input_values_f pliv_itax_org2,
7800: -- pay_link_input_values_f pliv_itax_type2,
7801: -- pay_element_entries_f pee_itax_org2,
7802: -- pay_element_entries_f pee_itax_type2,
7821: -- select /*+ ORDERED */
7822: -- null
7823: -- from pay_action_interlocks pai2,
7824: -- pay_assignment_actions paav2,
7825: -- pay_payroll_actions ppav2
7826: -- where pai2.locked_action_id = paa2.assignment_action_id
7827: -- and paav2.assignment_action_id = pai2.locking_action_id
7828: -- and ppav2.payroll_action_id = paav2.payroll_action_id
7829: -- and ppav2.action_type = 'V')
7941: ppy.payroll_name
7942: from (select /*+ ORDERED
7943: USE_NL(PAA, PPA)
7944: INDEX(PAA PAY_ASSIGNMENT_ACTIONS_N1)
7945: INDEX(PPA PAY_PAYROLL_ACTIONS_PK) */
7946: larch_v.arch_assignment_action_id arch_assignment_action_id,
7947: paa.assignment_id,
7948: paa.assignment_action_id proc_assignment_action_id,
7949: paa.action_sequence,
7974: pjia.assignment_action_id larch_assignment_action_id,
7975: decode(nvl(ppa_proc.element_set_id,-1),
7976: c_yea_ele_set_id,ppa_proc.date_earned,
7977: c_rey_ele_set_id,ppa_proc.date_earned,ppa_proc.effective_date) lproc_assignment_action_date
7978: from pay_payroll_actions ppa_arch,
7979: pay_assignment_actions paa_arch,
7980: pay_jp_itwb_assact_v pjia,
7981: pay_assignment_actions paa_o,
7982: pay_assignment_actions paa_proc,
7979: pay_assignment_actions paa_arch,
7980: pay_jp_itwb_assact_v pjia,
7981: pay_assignment_actions paa_o,
7982: pay_assignment_actions paa_proc,
7983: pay_payroll_actions ppa_proc
7984: where ppa_arch.payroll_action_id = p_payroll_action_id
7985: and paa_arch.payroll_action_id = ppa_arch.payroll_action_id
7986: and pjia.assignment_id (+) = paa_arch.assignment_id
7987: and pjia.effective_date (+)
7999: and pjia2.assignment_id = paa_o.assignment_id
8000: and paa_o2.assignment_action_id = pjia2.assignment_action_id
8001: and paa_o2.action_sequence > paa_o.action_sequence)) larch_v,
8002: pay_assignment_actions paa,
8003: pay_payroll_actions ppa,
8004: pay_link_input_values_f pliv_itax_org,
8005: pay_link_input_values_f pliv_itax_type,
8006: pay_element_entries_f pee_itax_org,
8007: pay_element_entries_f pee_itax_type,
8034: select /*+ ORDERED */
8035: null
8036: from pay_action_interlocks pai,
8037: pay_assignment_actions paav,
8038: pay_payroll_actions ppav
8039: where pai.locked_action_id = paa.assignment_action_id
8040: and paav.assignment_action_id = pai.locking_action_id
8041: and ppav.payroll_action_id = paav.payroll_action_id
8042: and ppav.action_type = 'V')
8068: --and not exists(
8069: -- select /*+ ORDERED */
8070: -- null
8071: -- from pay_assignment_actions paa2,
8072: -- pay_payroll_actions ppa2,
8073: -- pay_link_input_values_f pliv_itax_org2,
8074: -- pay_link_input_values_f pliv_itax_type2,
8075: -- pay_element_entries_f pee_itax_org2,
8076: -- pay_element_entries_f pee_itax_type2,
8095: -- select /*+ ORDERED */
8096: -- null
8097: -- from pay_action_interlocks pai2,
8098: -- pay_assignment_actions paav2,
8099: -- pay_payroll_actions ppav2
8100: -- where pai2.locked_action_id = paa2.assignment_action_id
8101: -- and paav2.assignment_action_id = pai2.locking_action_id
8102: -- and ppav2.payroll_action_id = paav2.payroll_action_id
8103: -- and ppav2.action_type = 'V')