[Home] [Help]
8: g_basis_end date;
9: g_basis_start date;
10: g_basis_year varchar2(4);
11: g_legal_entity_id varchar2(20);
12: g_person_id per_all_people_f.person_id%type;
13: g_assignment_set_id hr_assignment_sets.assignment_set_id%type;
14: g_setup_action_id pay_payroll_actions.payroll_action_id%type;
15: g_report_type varchar2(30);
16: g_previous_person_id per_all_people_f.person_id%type;
12: g_person_id per_all_people_f.person_id%type;
13: g_assignment_set_id hr_assignment_sets.assignment_set_id%type;
14: g_setup_action_id pay_payroll_actions.payroll_action_id%type;
15: g_report_type varchar2(30);
16: g_previous_person_id per_all_people_f.person_id%type;
17: g_moa_369_date ff_archive_items.value%type;
18: -- Added for bug 5435088 org cursor only need to run once
19: g_name_of_bank ff_archive_items.value%type;
20: g_org_run char(1);
18: -- Added for bug 5435088 org cursor only need to run once
19: g_name_of_bank ff_archive_items.value%type;
20: g_org_run char(1);
21: g_org_a8a_run char(1);
22: g_national_identifier per_all_people_f.national_identifier%type;
23: g_legal_entity_name hr_organization_information.org_information1%type;
24: g_er_income_tax_number hr_organization_information.org_information4%type;
25: g_er_auth_person hr_organization_information.org_information10%type;
26: g_er_auth_person_desig hr_organization_information.org_information11%type;
77: -- Assignment actions are created for all assignments processed by pre-processor
78: ----------------------------------------------------------------------------
79: procedure assignment_action_code
80: ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
81: p_start_person_id in per_all_people_f.person_id%type,
82: p_end_person_id in per_all_people_f.person_id%type,
83: p_chunk in number )
84: is
85: v_next_action_id pay_assignment_actions.assignment_action_id%type;
78: ----------------------------------------------------------------------------
79: procedure assignment_action_code
80: ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
81: p_start_person_id in per_all_people_f.person_id%type,
82: p_end_person_id in per_all_people_f.person_id%type,
83: p_chunk in number )
84: is
85: v_next_action_id pay_assignment_actions.assignment_action_id%type;
86: v_setup_action_id pay_payroll_actions.payroll_action_id%type;
204: -- Bug: 3118540 - This function is called from SRS 'IR8S Ad Hoc Printed Archive'
205: --------------------------------------------------------------------------------
206: procedure assignment_action_code_adhoc
207: ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
208: p_start_person_id in per_all_people_f.person_id%type,
209: p_end_person_id in per_all_people_f.person_id%type,
210: p_chunk in number )
211: is
212: v_next_action_id pay_assignment_actions.assignment_action_id%type;
205: --------------------------------------------------------------------------------
206: procedure assignment_action_code_adhoc
207: ( p_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
208: p_start_person_id in per_all_people_f.person_id%type,
209: p_end_person_id in per_all_people_f.person_id%type,
210: p_chunk in number )
211: is
212: v_next_action_id pay_assignment_actions.assignment_action_id%type;
213: v_person_id per_all_people_f.person_id%type;
209: p_end_person_id in per_all_people_f.person_id%type,
210: p_chunk in number )
211: is
212: v_next_action_id pay_assignment_actions.assignment_action_id%type;
213: v_person_id per_all_people_f.person_id%type;
214: v_assignment_set_id hr_assignment_sets.assignment_set_id%type;
215: v_business_group_id number;
216: v_basis_start date;
217: v_basis_end date;
243: -- Bug#3614563 Removed the Business Group id check from inner query to imporove the performence.
244: ----------------------------------------------------------------------------
245: cursor process_assignments
246: ( c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
247: c_start_person_id in per_all_people_f.person_id%type,
248: c_end_person_id in per_all_people_f.person_id%type,
249: c_person_id in per_all_people_f.person_id%type,
250: c_assignment_set_id in hr_assignment_sets.assignment_set_id%type,
251: c_business_group_id in number,
244: ----------------------------------------------------------------------------
245: cursor process_assignments
246: ( c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
247: c_start_person_id in per_all_people_f.person_id%type,
248: c_end_person_id in per_all_people_f.person_id%type,
249: c_person_id in per_all_people_f.person_id%type,
250: c_assignment_set_id in hr_assignment_sets.assignment_set_id%type,
251: c_business_group_id in number,
252: c_legal_entity_id in number,
245: cursor process_assignments
246: ( c_payroll_action_id in pay_payroll_actions.payroll_action_id%type,
247: c_start_person_id in per_all_people_f.person_id%type,
248: c_end_person_id in per_all_people_f.person_id%type,
249: c_person_id in per_all_people_f.person_id%type,
250: c_assignment_set_id in hr_assignment_sets.assignment_set_id%type,
251: c_business_group_id in number,
252: c_legal_entity_id in number,
253: c_basis_start in date,
422: procedure archive_code
423: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
424: p_effective_date in date )
425: is
426: v_person_id per_all_people_f.person_id%type;
427: v_assignment_id per_all_assignments_f.assignment_id%type;
428: v_national_identifier varchar2(50);
429: v_archive_date pay_payroll_actions.effective_date%type;
430: l_person_id per_all_people_f.person_id%type;
426: v_person_id per_all_people_f.person_id%type;
427: v_assignment_id per_all_assignments_f.assignment_id%type;
428: v_national_identifier varchar2(50);
429: v_archive_date pay_payroll_actions.effective_date%type;
430: l_person_id per_all_people_f.person_id%type;
431: l_archived_person_id binary_integer;
432:
433: ------------------------------------------------------------------------
434: -- Bug 2920732 - Modified the cursor to use secured views per_people_f, per_assignments_f
1058: -- earnings by month.
1059: ---------------------------------------------------------------------------
1060: procedure archive_balances
1061: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
1062: p_person_id in per_all_people_f.person_id%type,
1063: p_business_group_id in hr_organization_units.business_group_id%type,
1064: p_tax_unit_id in ff_archive_item_contexts.context%type,
1065: p_basis_year in varchar2 )
1066: is
1126: -- Bug# 2920732 - Modified the cursor to use secured view per_assignments_f
1127: -- Cursor month_year_action_sequence
1128: ---------------------------------------------------------------------------------------------------
1129: cursor month_year_action_sequence
1130: ( c_person_id per_all_people_f.person_id%type,
1131: c_business_group_id hr_organization_units.business_group_id%type,
1132: c_legal_entity_id pay_assignment_actions.tax_unit_id%type,
1133: c_basis_year varchar2 )
1134: is
1151: ---------------------------------------------------------------------------------------------------
1152: -- cursor month_year_action
1153: ---------------------------------------------------------------------------------------------------
1154: cursor month_year_action
1155: ( c_person_id per_all_people_f.person_id%type,
1156: c_business_group_id hr_organization_units.business_group_id%type,
1157: c_legal_entity_id pay_assignment_actions.tax_unit_id%type,
1158: c_basis_year varchar2,
1159: c_action_sequence pay_assignment_actions.action_sequence%type )
1242: ---------------------------------------------------------------------------------------------------
1243: -- Type to store the person ids with same national_identifier (Bug 2649107)
1244: ---------------------------------------------------------------------------------------------------
1245: type person_id_store_rec is record
1246: ( person_id per_all_people_f.person_id%type );
1247: type person_id_tab is table of person_id_store_rec index by binary_integer;
1248: person_id_rec person_id_tab;
1249: ---------------------------------------------------------------------------------------------------
1250: -- Type to store the months on which payroll is run for a perticular person id
1263: month_year_action_sequence_rec month_year_action_sequence%rowtype;
1264: month_year_action_rec month_year_action%rowtype;
1265: per_le_ytd_bal number;
1266: per_le_mtd_bal number;
1267: l_person_id per_all_people_f.person_id%type;
1268: l_ytd_counter number;
1269: l_mon_counter number;
1270: counter number;
1271: icounter number;
1750: -- Bug#2833530
1751: -- bug 2724020
1752: ---------------------------------------------------------------------------
1753: procedure archive_balance_dates
1754: ( p_person_id in per_all_people_f.person_id%TYPE,
1755: p_basis_year in varchar2,
1756: p_business_group_id in hr_organization_units.business_group_id%type,
1757: p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
1758: p_run_ass_action_id in pay_assignment_actions.assignment_action_id%type,
1775: l_prev_ass_id per_all_assignments_f.assignment_id%TYPE;
1776: l_new_ass_id per_all_assignments_f.assignment_id%TYPE;
1777: l_run_ass_action_id pay_assignment_actions.assignment_action_id%TYPE;
1778: l_term_max_assact_id pay_assignment_actions.assignment_action_id%TYPE;
1779: l_person_id per_all_people_f.person_id%type;
1780: v_person_id per_all_people_f.person_id%type;
1781:
1782: ---------------------------------------------------------------------------
1783: -- Bug# 2833530
1776: l_new_ass_id per_all_assignments_f.assignment_id%TYPE;
1777: l_run_ass_action_id pay_assignment_actions.assignment_action_id%TYPE;
1778: l_term_max_assact_id pay_assignment_actions.assignment_action_id%TYPE;
1779: l_person_id per_all_people_f.person_id%type;
1780: v_person_id per_all_people_f.person_id%type;
1781:
1782: ---------------------------------------------------------------------------
1783: -- Bug# 2833530
1784: -- Added check_termination and get_max_assactid cursors
1783: -- Bug# 2833530
1784: -- Added check_termination and get_max_assactid cursors
1785: -- Bug# 2920732 - Modified the cursor to use secured views per_people_f, per_assignments_f
1786: ---------------------------------------------------------------------------
1787: cursor check_termination( c_person_id per_all_people_f.person_id%TYPE,
1788: c_basis_year varchar2 )
1789: is
1790: select 'Y',
1791: oldpaaf.assignment_id,
2485: procedure archive_org_info
2486: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2487: p_business_group_id in hr_organization_units.business_group_id%type,
2488: p_legal_entity_id in hr_organization_units.organization_id%type,
2489: p_person_id in per_all_people_f.person_id%type,
2490: p_basis_start in date,
2491: p_basis_end in date)
2492: is
2493: --
2722: procedure archive_payroll_date
2723: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2724: p_business_group_id in hr_organization_units.business_group_id%type,
2725: p_legal_entity_id in hr_organization_units.organization_id%type,
2726: p_person_id in per_all_people_f.person_id%type,
2727: p_basis_year in varchar2) is
2728:
2729: v_payroll_date varchar2(30);
2730: cursor payroll_date
2729: v_payroll_date varchar2(30);
2730: cursor payroll_date
2731: ( c_business_group_id hr_organization_units.business_group_id%type,
2732: c_legal_entity_id hr_organization_units.organization_id%type,
2733: c_person_id per_all_people_f.person_id%type,
2734: c_basis_year varchar2)
2735: is
2736: select fnd_date.date_to_canonical(max(ppamax.effective_date))
2737: from per_assignments_f paamax,
2769: -- Copies the standard Person information route code.
2770: ---------------------------------------------------------------------------
2771: procedure archive_person_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_basis_start in date,
2775: p_basis_end in date )
2776: is
2777: --
2774: p_basis_start in date,
2775: p_basis_end in date )
2776: is
2777: --
2778: v_national_identifier per_all_people_f.national_identifier%type;
2779: v_sex hr_lookups.meaning%type;
2780: v_date_of_birth varchar2(30);
2781: ---------------------------------------------------------------------------
2782: -- Bug# 2920732 - Modified the cursor to use secured view per_people_f
2782: -- Bug# 2920732 - Modified the cursor to use secured view per_people_f
2783: -- Bug 2645599
2784: ---------------------------------------------------------------------------
2785: cursor person_details
2786: ( c_person_id per_all_people_f.person_id%type,
2787: c_basis_start date,
2788: c_basis_end date )
2789: is
2790: select people.national_identifier,
2829: -- Copies the standard Person Address information route code.
2830: ---------------------------------------------------------------------------
2831: procedure archive_person_addresses
2832: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2833: p_person_id in per_all_people_f.person_id%type,
2834: p_basis_start in date,
2835: p_basis_end in date )
2836: is
2837: v_style per_addresses.style%type;
2847: -- Bug2647074
2848: -- Modified for bug 5435088, added style, type and country
2849: ---------------------------------------------------------------------------
2850: cursor person_address
2851: (c_person_id per_all_people_f.person_id%type,
2852: c_basis_start date,
2853: c_basis_end date) is
2854: select addr.style,
2855: addr.address_type,
2914: -- Bug 4688761, to separate from the above procedure
2915: ---------------------------------------------------------------------------
2916: procedure archive_person_cq_addresses
2917: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2918: p_person_id in per_all_people_f.person_id%type,
2919: p_basis_start in date,
2920: p_basis_end in date )
2921: is
2922: v_address_line_1 per_addresses.address_line1%type;
2926: v_date_to varchar2(30);
2927:
2928: -- Added for bug 2373475
2929: cursor person_cq_address
2930: (c_person_id per_all_people_f.person_id%type,
2931: c_basis_start date,
2932: c_basis_end date) is
2933: select addr.address_line1,
2934: addr.address_line2,
2979: -- Copies the standard Employee information route code.
2980: ---------------------------------------------------------------------------
2981: procedure archive_emp_details
2982: (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
2983: p_person_id in per_all_people_f.person_id%type,
2984: p_basis_start in date,
2985: p_basis_end in date) is
2986:
2987: v_date_start varchar2(30);
2987: v_date_start varchar2(30);
2988: v_termination_date varchar2(30);
2989:
2990: cursor emp_hire_details
2991: (c_person_id per_all_people_f.person_id%type,
2992: c_basis_end date) is
2993: select fnd_date.date_to_canonical(min(service.date_start))
2994: from per_periods_of_service service
2995: where service.person_id = c_person_id
2996: and service.date_start <= c_basis_end; /*Bug 2668599*/
2997:
2998: /* Bug# 2920732 - Modified the cursor to use secured view per_assignments_f */
2999: cursor emp_termination_details
3000: (c_person_id per_all_people_f.person_id%type,
3001: c_basis_start date,
3002: c_basis_end date) is
3003: select fnd_date.date_to_canonical(service.actual_termination_date)
3004: from per_assignments_f assign,
3042: -- Copies the standard Person Developer DF route code.
3043: ---------------------------------------------------------------------------
3044: procedure archive_people_flex
3045: (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3046: p_person_id in per_all_people_f.person_id%type,
3047: p_basis_start in date,
3048: p_basis_end in date)
3049: is
3050: --
3047: p_basis_start in date,
3048: p_basis_end in date)
3049: is
3050: --
3051: v_sg_legal_name per_all_people_f.per_information1%type;
3052: v_pp_country per_all_people_f.per_information3%type;
3053: v_permit_type per_all_people_f.per_information6%type;
3054: v_permit_date varchar2(30);
3055: v_income_tax_no per_all_people_f.per_information12%type;
3048: p_basis_end in date)
3049: is
3050: --
3051: v_sg_legal_name per_all_people_f.per_information1%type;
3052: v_pp_country per_all_people_f.per_information3%type;
3053: v_permit_type per_all_people_f.per_information6%type;
3054: v_permit_date varchar2(30);
3055: v_income_tax_no per_all_people_f.per_information12%type;
3056: v_payee_id_type per_all_people_f.per_information23%type;
3049: is
3050: --
3051: v_sg_legal_name per_all_people_f.per_information1%type;
3052: v_pp_country per_all_people_f.per_information3%type;
3053: v_permit_type per_all_people_f.per_information6%type;
3054: v_permit_date varchar2(30);
3055: v_income_tax_no per_all_people_f.per_information12%type;
3056: v_payee_id_type per_all_people_f.per_information23%type;
3057: l_payee_id_check char(1);
3051: v_sg_legal_name per_all_people_f.per_information1%type;
3052: v_pp_country per_all_people_f.per_information3%type;
3053: v_permit_type per_all_people_f.per_information6%type;
3054: v_permit_date varchar2(30);
3055: v_income_tax_no per_all_people_f.per_information12%type;
3056: v_payee_id_type per_all_people_f.per_information23%type;
3057: l_payee_id_check char(1);
3058: l_nationality_code varchar2(3);
3059: ---------------------------------------------------------------------------
3052: v_pp_country per_all_people_f.per_information3%type;
3053: v_permit_type per_all_people_f.per_information6%type;
3054: v_permit_date varchar2(30);
3055: v_income_tax_no per_all_people_f.per_information12%type;
3056: v_payee_id_type per_all_people_f.per_information23%type;
3057: l_payee_id_check char(1);
3058: l_nationality_code varchar2(3);
3059: ---------------------------------------------------------------------------
3060: -- Bug# 2920732 - Modified the cursor to use secured view per_people_f
3061: -- Bug 2645599
3062: -- Bug 5435088, Added Payee ID Type and permit date
3063: ---------------------------------------------------------------------------
3064: cursor emp_details
3065: ( c_person_id per_all_people_f.person_id%type,
3066: c_basis_start date,
3067: c_basis_end date )
3068: is
3069: select people.per_information1,
3144: -- Copies the standard Extra Person Information DF route code.
3145: ---------------------------------------------------------------------------
3146: procedure archive_person_eits
3147: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3148: p_person_id in per_all_people_f.person_id%type,
3149: p_basis_start in date,
3150: p_basis_end in date )
3151: is
3152: --
3253: -- Added p_assignment_id as parameter
3254: ---------------------------------------------------------------------------
3255: procedure archive_assignment_eits
3256: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3257: p_person_id in per_all_people_f.person_id%type,
3258: p_assignment_id in per_all_assignments_f.assignment_id%type,
3259: p_legal_entity_id in hr_organization_units.organization_id%type,
3260: p_basis_start in date,
3261: p_basis_end in date )
3272: -- we pass the assignment_id as a parameter
3273: -- Bug 5435088 - Added field Approval from CPF to make full
3274: ---------------------------------------------------------------------------
3275: cursor assignment_eits
3276: ( c_person_id per_all_people_f.person_id%type,
3277: c_assignment_id per_assignments_f.assignment_id%type,
3278: c_legal_entity_id hr_organization_units.organization_id%type,
3279: c_basis_start date,
3280: c_basis_end date )
3321: -- value g_moa_369_date
3322: ---------------------------------------------------------------------------
3323: procedure archive_ass_bonus_date_eits
3324: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3325: p_person_id in per_all_people_f.person_id%type,
3326: p_assignment_id in per_all_assignments_f.assignment_id%type,
3327: p_legal_entity_id in hr_organization_units.organization_id%type,
3328: p_basis_start in date,
3329: p_basis_end in date )
3332: v_ass_bonus_date varchar2(10);
3333: v_assignment_id per_assignments_f.assignment_id%type;
3334:
3335: cursor ass_bonus_date_eits
3336: ( c_person_id per_all_people_f.person_id%type,
3337: c_assignment_id per_assignments_f.assignment_id%type,
3338: c_legal_entity_id hr_organization_units.organization_id%type,
3339: c_basis_start date,
3340: c_basis_end date )
3386: -- payment method
3387: ---------------------------------------------------------------------------
3388: procedure archive_ass_payment_method
3389: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3390: p_person_id in per_all_people_f.person_id%type,
3391: p_assignment_id in per_all_assignments_f.assignment_id%type,
3392: p_legal_entity_id in hr_organization_units.organization_id%type,
3393: p_basis_start in date,
3394: p_basis_end in date)
3397: v_ass_bank_name varchar2(10);
3398: v_assignment_id per_assignments_f.assignment_id%type;
3399:
3400: cursor ass_payment_method
3401: ( c_person_id per_all_people_f.person_id%type,
3402: c_assignment_id per_assignments_f.assignment_id%type,
3403: c_legal_entity_id hr_organization_units.organization_id%type,
3404: c_basis_start in date,
3405: c_basis_end in date)
3463: -- Bug 4890964, added p_assignment_id as parameter
3464: ---------------------------------------------------------------------------
3465: procedure archive_job_designation
3466: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3467: p_person_id in per_all_people_f.person_id%type,
3468: p_assignment_id in per_all_assignments_f.assignment_id%type,
3469: p_legal_entity_id in hr_organization_units.organization_id%type,
3470: p_basis_start in date,
3471: p_basis_end in date,
3483: -- Bug 5868910 - Added effective_start_date sub-query for the date tracked
3484: -- assignment
3485: ---------------------------------------------------------------------------
3486: cursor grade
3487: ( c_person_id per_all_people_f.person_id%type,
3488: c_assignment_id per_assignments_f.assignment_id%type,
3489: c_legal_entity_id hr_organization_units.organization_id%type,
3490: c_basis_start date,
3491: c_basis_end date) is
3517: -- Bug 5868910 - Added effective_start_date sub-query for the date tracked
3518: -- assignment
3519: ---------------------------------------------------------------------------
3520: cursor job
3521: ( c_person_id per_all_people_f.person_id%type,
3522: c_assignment_id per_all_assignments_f.assignment_id%type,
3523: c_legal_entity_id hr_organization_units.organization_id%type,
3524: c_basis_start date,
3525: c_basis_end date) is
3552: -- Bug 5868910 - Added effective_start_date sub-query for the date tracked
3553: -- assignment
3554: ---------------------------------------------------------------------------
3555: cursor position
3556: ( c_person_id per_all_people_f.person_id%type,
3557: c_assignment_id per_all_assignments_f.assignment_id%type,
3558: c_legal_entity_id hr_organization_units.organization_id%type,
3559: c_basis_start date,
3560: c_basis_end date) is
3590: -- assignment
3591: ---------------------------------------------------------------------------
3592:
3593: cursor position_seg
3594: ( c_person_id per_all_people_f.person_id%type,
3595: c_assignment_id per_all_assignments_f.assignment_id%type,
3596: c_basis_start date,
3597: c_basis_end date,
3598: c_legal_entity_id hr_organization_units.organization_id%type,
3679: -- we pass the assignment_id as a parameter
3680: ---------------------------------------------------------------------------
3681:
3682: cursor other
3683: ( c_person_id per_all_people_f.person_id%type,
3684: c_assignment_id per_all_assignments_f.assignment_id%type,
3685: c_legal_entity_id hr_organization_units.organization_id%type,
3686: c_basis_start date,
3687: c_basis_end date) is
3768: -- assignment whose duration is within Basis Year.
3769: ---------------------------------------------------------------------------
3770: procedure archive_os_assignment
3771: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3772: p_person_id in per_all_people_f.person_id%type,
3773: p_legal_entity_id in hr_organization_units.organization_id%type,
3774: p_basis_start in date,
3775: p_basis_end in date )
3776: is
3786: --
3787: -- Bug# 4688761 - Modified cursor to check the legal entity
3788: ---------------------------------------------------------------------------
3789: cursor os_assignment
3790: ( c_person_id per_all_people_f.person_id%type,
3791: c_legal_entity_id hr_organization_units.organization_id%type,
3792: c_basis_start date,
3793: c_basis_end date )
3794: is
3836: -- extra information screen, bug 2475287
3837: ---------------------------------------------------------------------------
3838: procedure archive_shares_details
3839: ( p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3840: p_person_id in per_all_people_f.person_id%type,
3841: p_tax_unit_id in ff_archive_item_contexts.context%type,
3842: p_basis_start in date,
3843: p_basis_end in date )
3844: is
3860: -- Bug 4314453 - Modified the cursor to use the table instead of view
3861: -- Bug 5435088 - Added grant type
3862: ---------------------------------------------------------------------------
3863: cursor shares_details
3864: ( c_person_id per_all_people_f.person_id%type,
3865: c_basis_start date,
3866: c_basis_end date )
3867: is
3868: select distinct pei.person_extra_info_id,
3879: pei2.pei_information5 grant_type,
3880: decode(pei2.pei_information3, null, null, to_char(fnd_date.canonical_to_date(pei2.pei_information3),'YYYY')||'/'||
3881: to_char(fnd_date.canonical_to_date(pei2.pei_information3),'MM')||'/'||to_char(fnd_date.canonical_to_date(pei2.pei_information3),'DD')) grant_date,
3882: pei2.pei_information4 shares_granted
3883: from per_all_people_f pap,
3884: per_people_extra_info pei,
3885: per_people_extra_info pei2,
3886: hr_all_organization_units hou,
3887: hr_organization_information hoi2,
3973: -- which is entered via assignment extra information screen, bug 3027801
3974: ---------------------------------------------------------------------------
3975: procedure archive_ir8s_c_details
3976: (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
3977: p_person_id in per_all_people_f.person_id%type,
3978: p_tax_unit_id in ff_archive_item_contexts.context%type,
3979: p_business_group_id in per_assignments_f.business_group_id%type,
3980: p_basis_start in date,
3981: p_basis_end in date) is
3982:
3983: /* Type to store the person ids with same national_identifier */
3984:
3985: type person_id_store_rec is record
3986: (person_id per_all_people_f.person_id%type);
3987:
3988: type person_id_tab is table of person_id_store_rec index by binary_integer;
3989: person_id_rec person_id_tab;
3990:
3988: type person_id_tab is table of person_id_store_rec index by binary_integer;
3989: person_id_rec person_id_tab;
3990:
3991: cursor ir8s_c_invalid_records
3992: (c_person_id per_all_people_f.person_id%type,
3993: c_tax_unit_id ff_archive_item_contexts.context%type,
3994: c_business_group_id per_assignments_f.business_group_id%type,
3995: c_basis_start date,
3996: c_basis_end date) is
4009: and paei.aei_information1 = to_char(c_basis_end,'YYYY')
4010: and (paa.effective_start_date <= c_basis_end
4011: and paa.effective_end_date >= c_basis_start);
4012:
4013: l_person_id per_all_people_f.person_id%type;
4014: l_temp_person_id per_all_people_f.person_id%type;
4015: l_archive_person_id per_all_people_f.person_id%type;
4016: counter number;
4017: l_counter number;
4010: and (paa.effective_start_date <= c_basis_end
4011: and paa.effective_end_date >= c_basis_start);
4012:
4013: l_person_id per_all_people_f.person_id%type;
4014: l_temp_person_id per_all_people_f.person_id%type;
4015: l_archive_person_id per_all_people_f.person_id%type;
4016: counter number;
4017: l_counter number;
4018: duplicate_exists varchar2(1);
4011: and paa.effective_end_date >= c_basis_start);
4012:
4013: l_person_id per_all_people_f.person_id%type;
4014: l_temp_person_id per_all_people_f.person_id%type;
4015: l_archive_person_id per_all_people_f.person_id%type;
4016: counter number;
4017: l_counter number;
4018: duplicate_exists varchar2(1);
4019: l_total_counts number;
4099:
4100:
4101: procedure archive_ir8s_c_detail_moas
4102: (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
4103: p_1_person_id in per_all_people_f.person_id%type,
4104: p_person_id in per_all_people_f.person_id%type,
4105: p_tax_unit_id in ff_archive_item_contexts.context%type,
4106: p_business_group_id in per_assignments_f.business_group_id%type,
4107: p_basis_start in date,
4100:
4101: procedure archive_ir8s_c_detail_moas
4102: (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
4103: p_1_person_id in per_all_people_f.person_id%type,
4104: p_person_id in per_all_people_f.person_id%type,
4105: p_tax_unit_id in ff_archive_item_contexts.context%type,
4106: p_business_group_id in per_assignments_f.business_group_id%type,
4107: p_basis_start in date,
4108: p_basis_end in date) is
4420: -- system Or if current employee is latest in case duplicates exist in the system
4421: -- For second case it also populates global table with all its previous employement records
4422: ----------------------------------------------------------------------
4423: function employee_if_latest ( p_national_identifier in varchar2,
4424: p_person_id in per_all_people_f.person_id%type,
4425: p_setup_action_id in pay_payroll_actions.payroll_action_id%type,
4426: p_report_type in varchar2 ) return boolean
4427: is
4428: type t_person_start_date_tab is table of per_all_people_f.start_date%type;
4424: p_person_id in per_all_people_f.person_id%type,
4425: p_setup_action_id in pay_payroll_actions.payroll_action_id%type,
4426: p_report_type in varchar2 ) return boolean
4427: is
4428: type t_person_start_date_tab is table of per_all_people_f.start_date%type;
4429: g_person_start_date_tab t_person_start_date_tab;
4430: begin
4431: g_person_id_tab.delete;
4432: --
4460: -------------------------------------------------------------------------
4461: -- Bug 4688761, this function checks the same person_id has been archived
4462: -------------------------------------------------------------------------
4463:
4464: function person_if_archived (p_person_id in per_all_people_f.person_id%type) return boolean
4465: is
4466: l_archived_person_id binary_integer;
4467: begin
4468: if g_debug then
4489: -- primary assignment, it needs for share details.
4490: -------------------------------------------------------------------------
4491:
4492: function pri_if_latest
4493: ( p_person_id in per_all_people_f.person_id%type
4494: , p_tax_unit_id in ff_archive_item_contexts.context%type
4495: , p_basis_start in date
4496: , p_basis_end in date) return boolean
4497: is
4496: , p_basis_end in date) return boolean
4497: is
4498: v_dummy varchar2(1);
4499: cursor pri_latest
4500: ( c_person_id per_all_people_f.person_id%type,
4501: c_tax_unit_id pay_assignment_actions.tax_unit_id%type,
4502: c_basis_start date,
4503: c_basis_end date )
4504: is
4552: -- effective_start_date with the primary defined
4553: -------------------------------------------------------------------------
4554:
4555: function pri_LE_if_latest
4556: ( p_person_id in per_all_people_f.person_id%type
4557: , p_tax_unit_id in ff_archive_item_contexts.context%type
4558: , p_basis_start in date
4559: , p_basis_end in date) return number
4560: is
4559: , p_basis_end in date) return number
4560: is
4561: v_assignment_id number(10);
4562: cursor pri_latest_LE
4563: ( c_person_id per_all_people_f.person_id%type,
4564: c_tax_unit_id pay_assignment_actions.tax_unit_id%type,
4565: c_basis_start date,
4566: c_basis_end date )
4567: is
4621: -- not return an assignment_id.
4622: -------------------------------------------------------------------------
4623:
4624: function id_LE_if_latest
4625: ( p_person_id in per_all_people_f.person_id%type
4626: , p_tax_unit_id in ff_archive_item_contexts.context%type
4627: , p_basis_start in date
4628: , p_basis_end in date) return number
4629: is
4628: , p_basis_end in date) return number
4629: is
4630: v_assignment_id number(10);
4631: cursor id_latest_LE
4632: ( c_person_id per_all_people_f.person_id%type,
4633: c_tax_unit_id pay_assignment_actions.tax_unit_id%type,
4634: c_basis_start date,
4635: c_basis_end date )
4636: is