[Home] [Help]
76: p_start_person_id in per_all_people_f.person_id%type,
77: p_end_person_id in per_all_people_f.person_id%type,
78: p_chunk in number )
79: is
80: v_next_action_id pay_assignment_actions.assignment_action_id%type;
81: v_setup_action_id pay_payroll_actions.payroll_action_id%type;
82: v_assignment_id per_all_assignments_f.assignment_id%type;
83: v_person_id per_all_people_f.person_id%type;
84: v_assignment_set_id hr_assignment_sets.assignment_set_id%type;
90: from pay_payroll_actions
91: where payroll_action_id = c_payroll_Action_id;
92: --
93: cursor next_action_id is
94: select pay_assignment_actions_s.nextval
95: from dual;
96: --
97: cursor process_assignments
98: (c_setup_action_id in pay_payroll_actions.payroll_action_id%type,
116: cursor csr_archive_action_id(p_assignment_id NUMBER)
117: is
118: select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
119: from pay_payroll_actions ppa,
120: pay_assignment_actions paa
121: where ppa.payroll_action_id in (
122: SELECT org_information2
123: FROM hr_organization_information
124: WHERE org_information_context = 'SG_IRAS_DETAILS'
133: where intl.locked_action_id = p_archive_action_id
134: and exists
135: ( select null
136: from per_assignments_f paf,
137: pay_assignment_actions mcl,
138: pay_payroll_actions mpl
139: where paf.assignment_id = mcl.assignment_id
140: and mpl.payroll_action_id = mcl.payroll_action_id
141: and mcl.assignment_Action_id = intl.locking_action_id
273: -- to archive the data in groups because depending on the data,
274: -- different parameters are required.
275: ------------------------------------------------------------------------
276: procedure archive_code
277: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
278: p_effective_date in date )
279: is
280: v_person_id per_all_people_f.person_id%type;
281: v_assignment_id per_all_assignments_f.assignment_id%type;
288: -- Bug 2920732 - Modified the cursor to use secured views per_people_f, per_assignments_f
289: -- Bug 3260855 - Modified the cusor to fetch only person_id, asg_id instead of
290: -- legislative parameters as global values can be used, which are initialized in Init_code.
291: ------------------------------------------------------------------------
292: cursor get_details( c_assignment_action_id pay_assignment_actions.assignment_action_id%type ) is
293: select pap.person_id,
294: nvl(pap.national_identifier,pap.per_information12),
295: pac.assignment_id
296: from pay_assignment_actions pac,
292: cursor get_details( c_assignment_action_id pay_assignment_actions.assignment_action_id%type ) is
293: select pap.person_id,
294: nvl(pap.national_identifier,pap.per_information12),
295: pac.assignment_id
296: from pay_assignment_actions pac,
297: per_assignments_f paa,
298: per_people_f pap
299: where pac.assignment_action_id = c_assignment_action_id
300: and paa.assignment_id = pac.assignment_id
390: -- Do not include Obsoleted balances
391: ---------------------------------------------------------------------------------------
392: procedure a8a_balances_value
393: ( p_person_id in per_people_f.person_id%type,
394: p_assct_id in pay_assignment_actions.assignment_action_id%type,
395: p_tax_uid in pay_assignment_actions.tax_unit_id%type,
396: p_person_counter in number )
397: is
398: l_balance_value_tab pay_balance_pkg.t_balance_value_tab;
391: ---------------------------------------------------------------------------------------
392: procedure a8a_balances_value
393: ( p_person_id in per_people_f.person_id%type,
394: p_assct_id in pay_assignment_actions.assignment_action_id%type,
395: p_tax_uid in pay_assignment_actions.tax_unit_id%type,
396: p_person_counter in number )
397: is
398: l_balance_value_tab pay_balance_pkg.t_balance_value_tab;
399: l_context_tab pay_balance_pkg.t_context_tab;
858: -- Month Balances: Specific balances required for IR8S as this breaks down
859: -- earnings by month.
860: ---------------------------------------------------------------------------
861: procedure archive_balances
862: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
863: p_person_id in per_all_people_f.person_id%type,
864: p_business_group_id in hr_organization_units.business_group_id%type,
865: p_tax_unit_id in ff_archive_item_contexts.context%type,
866: p_basis_year in varchar2 )
864: p_business_group_id in hr_organization_units.business_group_id%type,
865: p_tax_unit_id in ff_archive_item_contexts.context%type,
866: p_basis_year in varchar2 )
867: is
868: v_run_ass_action_id pay_assignment_actions.assignment_action_id%type;
869: v_date_earned ff_archive_item_contexts.context%type;
870: v_balance_value ff_archive_items.value%type;
871: v_archive_item_id ff_archive_items.archive_item_id%type;
872: v_object_version_number ff_archive_items.object_version_number%type;
969: ---------------------------------------------------------------------------------------------------
970: cursor month_year_action_sequence
971: ( c_person_id per_all_people_f.person_id%type,
972: c_business_group_id hr_organization_units.business_group_id%type,
973: c_legal_entity_id pay_assignment_actions.tax_unit_id%type,
974: c_basis_year varchar2 )
975: is
976: select /*+ ORDERED USE_NL(pacmax) */
977: max(pacmax.action_sequence) act_seq,
976: select /*+ ORDERED USE_NL(pacmax) */
977: max(pacmax.action_sequence) act_seq,
978: to_char(ppamax.effective_date,'MM')
979: from per_assignments_f paamax,
980: pay_assignment_actions pacmax,
981: pay_payroll_actions ppamax
982: where ppamax.business_group_id = c_business_group_id
983: and pacmax.tax_unit_id = c_legal_entity_id
984: and paamax.person_id = c_person_id
994: ---------------------------------------------------------------------------------------------------
995: cursor month_year_action
996: ( c_person_id per_all_people_f.person_id%type,
997: c_business_group_id hr_organization_units.business_group_id%type,
998: c_legal_entity_id pay_assignment_actions.tax_unit_id%type,
999: c_basis_year varchar2,
1000: c_action_sequence pay_assignment_actions.action_sequence%type )
1001: is
1002: select /*+ ORDERED USE_NL(pac) */
996: ( c_person_id per_all_people_f.person_id%type,
997: c_business_group_id hr_organization_units.business_group_id%type,
998: c_legal_entity_id pay_assignment_actions.tax_unit_id%type,
999: c_basis_year varchar2,
1000: c_action_sequence pay_assignment_actions.action_sequence%type )
1001: is
1002: select /*+ ORDERED USE_NL(pac) */
1003: pac.assignment_action_id assact_id,
1004: decode(ppa.action_type,'V',fnd_date.date_to_canonical(ppa.effective_date),fnd_date.date_to_canonical(ppa.date_earned)) date_earned,
1003: pac.assignment_action_id assact_id,
1004: decode(ppa.action_type,'V',fnd_date.date_to_canonical(ppa.effective_date),fnd_date.date_to_canonical(ppa.date_earned)) date_earned,
1005: pac.tax_unit_id tax_uid
1006: from per_assignments_f paa,
1007: pay_assignment_actions pac,
1008: pay_payroll_actions ppa
1009: where ppa.business_group_id = c_business_group_id
1010: and pac.tax_unit_id = c_legal_entity_id
1011: and paa.person_id = c_person_id
1537: from ff_user_entities where user_entity_id = t_user_entity_id(i);
1538:
1539: SELECT assignment_id
1540: INTO l_assignment_id
1541: FROM pay_assignment_Actions paa
1542: WHERE paa.assignment_action_id = p_assignment_action_id;
1543:
1544: /* past original and amendment */
1545:
1553: FROM ff_archive_items arch
1554: WHERE arch.user_entity_id = t_user_entity_id(i)
1555: AND arch.context1 IN( select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
1556: from pay_payroll_actions ppa,
1557: pay_assignment_actions paa
1558: where ppa.payroll_action_id in (SELECT org_information2
1559: FROM hr_organization_information
1560: WHERE org_information_context = 'SG_IRAS_DETAILS'
1561: AND organization_id = g_legal_entity_id
1657: from ff_archive_items arch
1658: where arch.user_entity_id = t_user_entity_id_ir8a(counter)
1659: and arch.context1 IN( select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
1660: from pay_payroll_actions ppa,
1661: pay_assignment_actions paa
1662: where ppa.payroll_action_id in (SELECT org_information2
1663: FROM hr_organization_information
1664: WHERE org_information_context = 'SG_IRAS_DETAILS'
1665: AND organization_id = g_legal_entity_id
1714: from ff_archive_items arch
1715: where arch.user_entity_id = t_user_entity_id_ir8s(counter)
1716: and arch.context1 IN( select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
1717: from pay_payroll_actions ppa,
1718: pay_assignment_actions paa
1719: where ppa.payroll_action_id in (SELECT org_information2
1720: FROM hr_organization_information
1721: WHERE org_information_context = 'SG_IRAS_DETAILS'
1722: AND organization_id = g_legal_entity_id
1787: where arch.user_entity_id = t_user_entity_id_a8a(i)
1788: and arch.context1 IN( select paa.assignment_action_id
1789: -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
1790: from pay_payroll_actions ppa,
1791: pay_assignment_actions paa
1792: where ppa.payroll_action_id in (SELECT org_information2
1793: FROM hr_organization_information
1794: WHERE org_information_context = 'SG_IRAS_DETAILS'
1795: AND organization_id = g_legal_entity_id
2136: and ue.user_entity_id = arch.user_entity_id
2137: AND ARCH.CONTEXT1 in ( select paa.assignment_action_id
2138: -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
2139: from pay_payroll_actions ppa,
2140: pay_assignment_actions paa
2141: where ppa.payroll_action_id in (SELECT org_information2
2142: FROM hr_organization_information
2143: WHERE org_information_context = 'SG_IRAS_DETAILS'
2144: AND organization_id = g_legal_entity_id
2210:
2211:
2212: ---------------------------------------------------------------------------
2213: procedure archive_shares_details
2214: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2215: p_person_id in per_all_people_f.person_id%type,
2216: p_tax_unit_id in ff_archive_item_contexts.context%type,
2217: p_basis_start in date,
2218: p_basis_end in date )
2349: where arch.user_entity_id = c_user_entity_id
2350: and arch.context1 IN(
2351: select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
2352: from pay_payroll_actions ppa,
2353: pay_assignment_actions paa
2354: where ppa.payroll_action_id in (
2355: SELECT org_information2
2356: FROM hr_organization_information
2357: WHERE org_information_context = 'SG_IRAS_DETAILS'
2401: end if;
2402:
2403: SELECT assignment_id
2404: INTO l_assignment_id
2405: FROM pay_assignment_Actions paa
2406: WHERE paa.assignment_action_id = p_assignment_action_id;
2407:
2408: select arch.value
2409: into v_er_incorp_date
2413: and ue.user_entity_id = arch.user_entity_id
2414: AND ARCH.CONTEXT1 in ( select paa.assignment_action_id
2415: -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
2416: from pay_payroll_actions ppa,
2417: pay_assignment_actions paa
2418: where ppa.payroll_action_id in (
2419: SELECT org_information2
2420: FROM hr_organization_information
2421: WHERE org_information_context = 'SG_IRAS_DETAILS'
2461: where arch.user_entity_id = t_user_entity_id_a8b(1)
2462: and arch.context1 IN(
2463: select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
2464: from pay_payroll_actions ppa,
2465: pay_assignment_actions paa
2466: where ppa.payroll_action_id in (
2467: SELECT org_information2
2468: FROM hr_organization_information
2469: WHERE org_information_context = 'SG_IRAS_DETAILS'
2507: and ue.user_entity_id = arch.user_entity_id
2508: AND ARCH.CONTEXT1 in ( select paa.assignment_action_id
2509: -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
2510: from pay_payroll_actions ppa,
2511: pay_assignment_actions paa
2512: where ppa.payroll_action_id in (SELECT org_information2
2513: FROM hr_organization_information
2514: WHERE org_information_context = 'SG_IRAS_DETAILS'
2515: AND organization_id = g_legal_entity_id
2561: where arch.user_entity_id = t_user_entity_id_a8b(1)
2562: and arch.context1 IN(
2563: select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
2564: from pay_payroll_actions ppa,
2565: pay_assignment_actions paa
2566: where ppa.payroll_action_id in (
2567: SELECT org_information2
2568: FROM hr_organization_information
2569: WHERE org_information_context = 'SG_IRAS_DETAILS'
2768: -- Selects information for IR8S C claimed/to be claimed details information,
2769: -- which is entered via assignment extra information screen, bug 3027801
2770: ---------------------------------------------------------------------------
2771: procedure archive_ir8s_c_details
2772: (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2773: p_person_id in per_all_people_f.person_id%type,
2774: p_tax_unit_id in ff_archive_item_contexts.context%type,
2775: p_business_group_id in per_assignments_f.business_group_id%type,
2776: p_basis_start in date,
2843: end archive_ir8s_c_details;
2844:
2845:
2846: procedure archive_ir8s_c_detail_moas
2847: (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2848: p_1_person_id in per_all_people_f.person_id%type,
2849: p_person_id in per_all_people_f.person_id%type,
2850: p_tax_unit_id in ff_archive_item_contexts.context%type,
2851: p_business_group_id in per_assignments_f.business_group_id%type,
2929: where arch.user_entity_id = c_user_entity_id
2930: and arch.context1 IN(
2931: select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
2932: from pay_payroll_actions ppa,
2933: pay_assignment_actions paa
2934: where ppa.payroll_action_id in (
2935: SELECT org_information2
2936: FROM hr_organization_information
2937: WHERE org_information_context = 'SG_IRAS_DETAILS'
2976: order by ue.user_entity_name;
2977:
2978: SELECT assignment_id
2979: INTO l_assignment_id
2980: FROM pay_assignment_Actions paa
2981: WHERE paa.assignment_action_id = p_assignment_action_id;
2982:
2983: amend_ir8s_c_flag := 'N';
2984:
2998: where arch.user_entity_id = t_user_entity_id_ir8s_c(counter)
2999: and arch.context1 IN(
3000: select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
3001: from pay_payroll_actions ppa,
3002: pay_assignment_actions paa
3003: where ppa.payroll_action_id in (
3004: SELECT org_information2
3005: FROM hr_organization_information
3006: WHERE org_information_context = 'SG_IRAS_DETAILS'
3054: and ue.user_entity_id = arch.user_entity_id
3055: AND ARCH.CONTEXT1 in ( select paa.assignment_action_id
3056: -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
3057: from pay_payroll_actions ppa,
3058: pay_assignment_actions paa
3059: where ppa.payroll_action_id in (SELECT org_information2
3060: FROM hr_organization_information
3061: WHERE org_information_context = 'SG_IRAS_DETAILS'
3062: AND organization_id = g_legal_entity_id
3132: where arch.user_entity_id = t_user_entity_id_ir8s_c(counter)
3133: and arch.context1 IN(
3134: select paa.assignment_action_id -- ALL PREV ORIGINAL AND AMENDMENT ARCHIVES SUBMITTED B4
3135: from pay_payroll_actions ppa,
3136: pay_assignment_actions paa
3137: where ppa.payroll_action_id in (
3138: SELECT org_information2
3139: FROM hr_organization_information
3140: WHERE org_information_context = 'SG_IRAS_DETAILS'
3230: -- Calls the archive utility to actually perform the archive of the item.
3231: ---------------------------------------------------------------------------
3232: procedure archive_item
3233: ( p_user_entity_name in ff_user_entities.user_entity_name%type,
3234: p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3235: p_archive_value in ff_archive_items.value%type )
3236: is
3237: --
3238: v_user_entity_id ff_user_entities.user_entity_id%type;
3281: -- one another context
3282: -----------------------------------------------------------------------------
3283: procedure archive_item_2
3284: ( p_user_entity_name in ff_user_entities.user_entity_name%type,
3285: p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3286: p_archive_value in ff_archive_items.value%type,
3287: p_context_value2 in ff_archive_item_contexts.context%type )
3288: is
3289: --
3335: -- one another context
3336: -----------------------------------------------------------------------------
3337: procedure archive_item_3
3338: ( p_user_entity_name in ff_user_entities.user_entity_name%type,
3339: p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3340: p_archive_value in ff_archive_items.value%type,
3341: p_context_value2 in ff_archive_item_contexts.context%type,
3342: p_context_value3 in ff_archive_item_contexts.context%type )
3343: is
3514: is
3515: v_dummy varchar2(1);
3516: cursor pri_latest
3517: ( c_person_id per_all_people_f.person_id%type,
3518: c_tax_unit_id pay_assignment_actions.tax_unit_id%type,
3519: c_basis_start date,
3520: c_basis_end date )
3521: is
3522: select 'X'
3577: is
3578: v_assignment_id number(10);
3579: cursor pri_latest_LE
3580: ( c_person_id per_all_people_f.person_id%type,
3581: c_tax_unit_id pay_assignment_actions.tax_unit_id%type,
3582: c_basis_start date,
3583: c_basis_end date )
3584: is
3585: select paf.assignment_id
3646: is
3647: v_assignment_id number(10);
3648: cursor id_latest_LE
3649: ( c_person_id per_all_people_f.person_id%type,
3650: c_tax_unit_id pay_assignment_actions.tax_unit_id%type,
3651: c_basis_start date,
3652: c_basis_end date )
3653: is
3654: select max(paf.assignment_id)