[Home] [Help]
132: from pay_action_interlocks intl
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
284: l_person_id per_all_people_f.person_id%type;
285: l_archived_person_id binary_integer;
286:
287: ------------------------------------------------------------------------
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,
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
301: and paa.person_id = pap.person_id ;
963: and pbd.dimension_name = '_PER_LE_YTD';
964: ---------------------------------------------------------------------------------------------------
965: -- Bug 2629839. Cursor month_year_action is split into two cursors month_year_action_sequence and
966: -- month_year_action to improve the performance
967: -- Bug# 2920732 - Modified the cursor to use secured view per_assignments_f
968: -- Cursor month_year_action_sequence
969: ---------------------------------------------------------------------------------------------------
970: cursor month_year_action_sequence
971: ( c_person_id per_all_people_f.person_id%type,
975: is
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
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,
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
2256: l_temp_value VARCHAR2(2000);
2257: archive_value_shares_acquired VARCHAR2(2000);
2258: amend_value_shares_acquired VARCHAR2(2000);
2259: l_name_ue VARCHAR2(2000);
2260: l_assignment_id per_assignments_f.assignment_id%type;
2261:
2262: v_moa_305 number;
2263: v_moa_319 number;
2264: v_moa_339 number;
2287: v_archive char(1);
2288:
2289:
2290: ---------------------------------------------------------------------------
2291: -- Bug# 2920732 - Modified the cursor to use the secured view per_assignments_f
2292: -- bug 2691877
2293: -- bug 3501956 - Changed cursor to select information from per_people_extra_info table
2294: -- Bug 4314453 - Modified the cursor to use the table instead of view
2295: -- Bug 5435088 - Added grant type
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,
2777: p_basis_end in date) is
2778:
2779: /* Type to store the person ids with same national_identifier */
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,
2852: p_basis_start in date,
2853: p_basis_end in date) is
2854:
2855:
2883: l_name_ue VARCHAR2(2000);
2884:
2885:
2886: cursor ir8s_c_details
2887: (c_person_id per_assignments_f.person_id%type,
2888: c_tax_unit_id ff_archive_item_contexts.context%type,
2889: c_business_group_id per_assignments_f.business_group_id%type,
2890: c_basis_start date,
2891: c_basis_end date) is
2885:
2886: cursor ir8s_c_details
2887: (c_person_id per_assignments_f.person_id%type,
2888: c_tax_unit_id ff_archive_item_contexts.context%type,
2889: c_business_group_id per_assignments_f.business_group_id%type,
2890: c_basis_start date,
2891: c_basis_end date) is
2892:
2893: select distinct aei.assignment_extra_info_id,
2900: aei.aei_information8 er_cpf_date,
2901: aei.aei_information9 ee_cpf,
2902: aei.aei_information10 ee_cpf_interest,
2903: aei.aei_information11 ee_cpf_date
2904: from per_assignments_f ass,
2905: per_assignment_extra_info aei,
2906: hr_soft_coding_keyflex hsc
2907: where ass.person_id = c_person_id
2908: and ass.assignment_id = aei.assignment_id
3519: c_basis_start date,
3520: c_basis_end date )
3521: is
3522: select 'X'
3523: from per_assignments_f paf,
3524: hr_soft_coding_keyflex hsc
3525: where paf.person_id = c_person_id
3526: and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3527: and hsc.segment1 = c_tax_unit_id
3528: and paf.primary_flag = 'Y'
3529: and paf.assignment_type = 'E' /* Bug 5033609 */
3530: and paf.effective_start_date =
3531: (select max(paf1.effective_start_date)
3532: from per_assignments_f paf1 /* Bug 5858566 */
3533: where paf1.person_id = paf.person_id
3534: and paf1.assignment_type = 'E' /* Bug 5033609 */
3535: and paf1.effective_start_date <= c_basis_end
3536: and paf1.effective_end_date >= c_basis_start
3582: c_basis_start date,
3583: c_basis_end date )
3584: is
3585: select paf.assignment_id
3586: from per_assignments_f paf,
3587: hr_soft_coding_keyflex hsc
3588: where paf.person_id = c_person_id
3589: and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3590: and hsc.segment1 = c_tax_unit_id
3591: and paf.primary_flag = 'Y'
3592: and paf.assignment_type = 'E' /* Bug 5033609 */
3593: and paf.effective_start_date =
3594: (select max(paf1.effective_start_date)
3595: from per_assignments_f paf1 /* Bug 5858566 */
3596: where paf1.person_id = paf.person_id
3597: and paf1.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
3598: and paf1.assignment_type = 'E' /* Bug 5033609 */
3599: and paf1.effective_start_date <= c_basis_end
3651: c_basis_start date,
3652: c_basis_end date )
3653: is
3654: select max(paf.assignment_id)
3655: from per_assignments_f paf,
3656: hr_soft_coding_keyflex hsc
3657: where paf.person_id = c_person_id
3658: and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3659: and hsc.segment1 = c_tax_unit_id
3659: and hsc.segment1 = c_tax_unit_id
3660: and paf.assignment_type = 'E'
3661: and paf.effective_start_date = (
3662: select max(paf1.effective_start_date)
3663: from per_assignments_f paf1 /* Bug 5858566 */
3664: where paf1.person_id = paf.person_id
3665: and paf1.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
3666: and paf1.assignment_type = 'E' /*Bug5033609*/
3667: and paf1.effective_start_date <= c_basis_end