[Home] [Help]
1175: cursor csr_classification
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
1261: cursor csr_pay_yea_data
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,
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,
1271: pay_action_interlocks pai_yea,
1272: pay_assignment_actions paa_yea,
1273: pay_payroll_actions ppa_yea
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
1275: and ppa_sal.payroll_action_id = paa_sal.payroll_action_id
1276: and pai_sal.locked_action_id = paa_sal.assignment_action_id
1297: and not exists(
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
1471: cursor csr_pay_yea_data
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,
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,
1481: pay_action_interlocks pai_yea,
1482: pay_assignment_actions paa_yea,
1483: pay_payroll_actions ppa_yea
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
1485: and ppa_bon.payroll_action_id = paa_bon.payroll_action_id
1486: and pai_bon.locked_action_id = paa_bon.assignment_action_id
1510: and not exists(
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
1520: and not exists(
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'
1775: and pjip.effective_date
1776: between g_effective_soy and g_effective_eoy
1777: and exists(
1778: select null
1779: from pay_assignment_actions paa,
1780: pay_jp_itax_person_v2 pjips
1781: where paa.payroll_action_id = ppa.payroll_action_id
1782: and pjips.action_context_id = paa.assignment_action_id
1783: and pjips.itax_organization_id = hoi.organization_id)));
1801: pjwa.leaving_reason,
1802: pp.per_information18 last_name,
1803: pp.last_name last_name_kana,
1804: pjwa.employment_category
1805: from pay_assignment_actions paa_yea,
1806: pay_jp_wic_assacts_v pjwa,
1807: pay_all_payrolls_f pap,
1808: per_all_people_f pp
1809: where paa_yea.assignment_action_id = p_assignment_action_id
1887: --null housing_residence_date,
1888: --fnd_number.canonical_to_number(pjit.housing_residence_year) housing_residence_year,
1889: --fnd_number.canonical_to_number(pjit.housing_residence_month) housing_residence_month,
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,
1888: --fnd_number.canonical_to_number(pjit.housing_residence_year) housing_residence_year,
1889: --fnd_number.canonical_to_number(pjit.housing_residence_month) housing_residence_month,
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,
1889: --fnd_number.canonical_to_number(pjit.housing_residence_month) housing_residence_month,
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,
1936: pjwa.leaving_reason,
1937: pp.per_information18 last_name,
1938: pp.last_name last_name_kana,
1939: pjwa.employment_category
1940: from pay_assignment_actions paa_yea,
1941: pay_jp_wic_assacts_v pjwa,
1942: pay_all_payrolls_f pap,
1943: per_all_people_f pp
1944: where paa_yea.assignment_action_id = p_assignment_action_id
1951: between pp.effective_start_date and pp.effective_end_date
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
3449: is
3450: select /*+ ORDERED */
3451: pjia.payroll_id
3452: from pay_jp_itwb_assact_v pjia,
3453: pay_assignment_actions paa_arch_o
3454: where pjia.assignment_id = p_assignment_id
3455: and pjia.effective_date
3456: between g_effective_soy and g_effective_eoy
3457: and paa_arch_o.assignment_action_id = pjia.assignment_action_id
3583: is
3584: select /*+ ORDERED */
3585: pjia.payroll_id
3586: from pay_jp_itwb_assact_v pjia,
3587: pay_assignment_actions paa_arch_o
3588: where pjia.assignment_id = p_assignment_id
3589: and pjia.effective_date
3590: between g_effective_soy and g_effective_eoy
3591: and paa_arch_o.assignment_action_id = pjia.assignment_action_id
5394: and pjip.itax_org_id = g_itax_organization_id
5395: and pjip.status <> 'UPDATE'
5396: and exists(
5397: select null
5398: from pay_assignment_actions paa,
5399: pay_jp_itwb_assact_v pjia
5400: where paa.payroll_action_id = pjip.payroll_action_id
5401: and pjia.assignment_action_id = paa.assignment_action_id)
5402: and not exists(
5417: and pjip2.status <> 'UPDATE'
5418: and pjip2.archive_sequence > pjip.archive_sequence
5419: and exists(
5420: select null
5421: from pay_assignment_actions paa2,
5422: pay_jp_itwb_assact_v pjia2
5423: where paa2.payroll_action_id = pjip2.payroll_action_id
5424: and pjia2.assignment_action_id = paa2.assignment_action_id));
5425: --
5690: -- fetch all status, even no archive
5691: cursor csr_rollback_assact
5692: is
5693: select paa.assignment_action_id
5694: from pay_assignment_actions paa
5695: where paa.payroll_action_id = p_payroll_action_id;
5696: --
5697: begin
5698: --
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,
6097: pay_element_entries_f pee_itax_org,
6127: and not exists(
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
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,
6169: pay_element_entries_f pee_itax_type2,
6195: and not exists(
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
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,
6247: pay_element_entries_f pee_itax_org,
6274: and not exists(
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
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,
6319: pay_element_entries_f pee_itax_type2,
6345: and not exists(
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
6378: and peev_itax_type2.screen_entry_value <> 'E'
6379: and paa2.assignment_id = paa.assignment_id
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 (+)
6379: and paa2.assignment_id = paa.assignment_id
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
6393: and not exists(
6394: select /*+ ORDERED */
6395: null
6396: from pay_jp_itwb_assact_v pjia2,
6397: pay_assignment_actions paa_o2
6398: where pjia2.assignment_id = larch_v.assignment_id
6399: and pjia2.effective_date
6400: between g_effective_soy and g_effective_som - 1
6401: and paa_o2.assignment_action_id = pjia2.assignment_action_id
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,
6420: pay_element_entries_f pee_itax_org,
6447: and not exists(
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
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,
6493: pay_element_entries_f pee_itax_org2,
6520: and not exists(
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
6557: and peev_itax_type2.screen_entry_value <> 'E'
6558: and paa2.assignment_id = paa.assignment_id
6559: and paa2.action_sequence > paa.action_sequence)) larch_v,
6560: pay_jp_itwb_assact_v pjia,
6561: pay_assignment_actions paa_o
6562: where pjia.assignment_id (+) = larch_v.assignment_id
6563: and pjia.effective_date (+)
6564: between g_effective_soy and g_effective_eoy
6565: and paa_o.assignment_action_id (+) = pjia.assignment_action_id
6566: and not exists(
6567: select /*+ ORDERED */
6568: null
6569: from pay_jp_itwb_assact_v pjia2,
6570: pay_assignment_actions paa_o2
6571: where pjia2.assignment_id = larch_v.assignment_id
6572: and pjia2.effective_date
6573: between g_effective_soy and g_effective_eoy
6574: and paa_o2.assignment_action_id = pjia2.assignment_action_id
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,
6594: pay_element_entries_f pee_itax_org,
6621: and not exists(
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
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,
6667: pay_element_entries_f pee_itax_org2,
6694: and not exists(
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
6732: and paa2.assignment_id = paa.assignment_id
6733: and paa2.action_sequence > paa.action_sequence)) larch_v,
6734: hr_assignment_set_amendments hasa,
6735: pay_jp_itwb_assact_v pjia,
6736: pay_assignment_actions paa_o
6737: where to_number(decode(nvl(to_char(g_ass_set_formula_id),'-1'),'-1',
6738: decode(nvl(g_ass_set_amendment_type,'X'),'I',hasa.assignment_id,larch_v.assignment_id),
6739: larch_v.assignment_id)) = larch_v.assignment_id
6740: and hasa.assignment_set_id (+) = g_assignment_set_id
6747: and not exists(
6748: select /*+ ORDERED */
6749: null
6750: from pay_jp_itwb_assact_v pjia2,
6751: pay_assignment_actions paa_o2
6752: where pjia2.assignment_id = larch_v.assignment_id
6753: and pjia2.effective_date
6754: between g_effective_soy and g_effective_eoy
6755: and paa_o2.assignment_action_id = pjia2.assignment_action_id
7193: and g_detail_debug = 'Y' then
7194: l_assact_step := 28;
7195: end if;
7196: --
7197: select pay_assignment_actions_s.nextval
7198: into l_assignment_action_id
7199: from dual;
7200: --
7201: if g_debug
7456: pp.date_of_birth birth_date,
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,
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,
7490: pay_link_input_values_f pliv_itax_type,
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,
7490: pay_link_input_values_f pliv_itax_type,
7491: pay_element_entries_f pee_itax_org,
7520: and not exists(
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
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,
7563: -- pay_element_entries_f pee_itax_type2,
7581: -- and not exists(
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
7667: nvl(nvl(ppy.prl_information1,g_bg_itax_dpnt_ref_type),'CTR_EE') dpnt_ref_type,
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,
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,
7707: pay_payroll_actions lppa
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,
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
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,
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
7716: and not exists(
7717: select /*+ ORDERED */
7718: null
7719: from pay_jp_itwb_assact_v pjia2,
7720: pay_assignment_actions paa_o2
7721: where pjia2.assignment_id = pjia.assignment_id
7722: and pjia2.effective_date
7723: between g_effective_soy and g_effective_som - 1
7724: and paa_o2.assignment_action_id = pjia2.assignment_action_id
7722: and pjia2.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,
7759: and not exists(
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
7793: /* --this part was removed for redundant validation
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,
7820: -- and not exists(
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
7940: pp.date_of_birth birth_date,
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,
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,
7983: pay_payroll_actions ppa_proc
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,
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
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,
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
7992: and not exists(
7993: select /*+ ORDERED */
7994: null
7995: from pay_jp_itwb_assact_v pjia2,
7996: pay_assignment_actions paa_o2
7997: where pjia2.effective_date
7998: between g_effective_soy and g_effective_eoy
7999: and pjia2.assignment_id = paa_o.assignment_id
8000: and paa_o2.assignment_action_id = pjia2.assignment_action_id
7998: between g_effective_soy and g_effective_eoy
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,
8033: and not exists(
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
8067: /* --this part was removed for redundant validation
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,
8094: -- and not exists(
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
19667: end if;
19668: --
19669: select assignment_id
19670: into l_assignment_id
19671: from pay_assignment_actions
19672: where assignment_action_id = p_assignment_action_id;
19673: --
19674: if g_debug then
19675: hr_utility.set_location(l_proc,10);