[Home] [Help]
252: c_legal_entity_id in number,
253: c_basis_start in date,
254: c_basis_end in date) is
255: select max(paf.assignment_id)
256: from per_assignments_f paf,
257: pay_payroll_actions ppa1
258: where ppa1.payroll_action_id = c_payroll_action_id
259: and paf.person_id between c_start_person_id and c_end_person_id
260: and ppa1.business_group_id = paf.business_group_id
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
435: -- Bug 3260855 - Modified the cusor to fetch only person_id, asg_id instead of
436: -- legislative parameters as global values can be used, which are initialized in Init_code.
437: ------------------------------------------------------------------------
438: cursor get_details( c_assignment_action_id pay_assignment_actions.assignment_action_id%type ) is
439: select pap.person_id,
440: nvl(pap.national_identifier,pap.per_information12),
441: pac.assignment_id
442: from pay_assignment_actions pac,
443: per_assignments_f paa,
444: per_people_f pap
445: where pac.assignment_action_id = c_assignment_action_id
446: and paa.assignment_id = pac.assignment_id
447: and paa.person_id = pap.person_id ;
1122: and pbd.dimension_name = '_PER_LE_YTD';
1123: ---------------------------------------------------------------------------------------------------
1124: -- Bug 2629839. Cursor month_year_action is split into two cursors month_year_action_sequence and
1125: -- month_year_action to improve the performance
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,
1134: is
1135: select /*+ ORDERED USE_NL(pacmax) */
1136: max(pacmax.action_sequence) act_seq,
1137: to_char(ppamax.effective_date,'MM')
1138: from per_assignments_f paamax,
1139: pay_assignment_actions pacmax,
1140: pay_payroll_actions ppamax
1141: where ppamax.business_group_id = c_business_group_id
1142: and pacmax.tax_unit_id = c_legal_entity_id
1161: select /*+ ORDERED USE_NL(pac) */
1162: pac.assignment_action_id assact_id,
1163: decode(ppa.action_type,'V',fnd_date.date_to_canonical(ppa.effective_date),fnd_date.date_to_canonical(ppa.date_earned)) date_earned,
1164: pac.tax_unit_id tax_uid
1165: from per_assignments_f paa,
1166: pay_assignment_actions pac,
1167: pay_payroll_actions ppa
1168: where ppa.business_group_id = c_business_group_id
1169: and pac.tax_unit_id = c_legal_entity_id
1781:
1782: ---------------------------------------------------------------------------
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,
1792: newpaaf.assignment_id
1793: from per_people_f pap,
1794: per_assignments_f oldpaaf,
1795: per_assignments_f newpaaf
1796: where pap.person_id = c_person_id
1797: and pap.person_id = oldpaaf.person_id
1798: and oldpaaf.person_id = newpaaf.person_id
1791: oldpaaf.assignment_id,
1792: newpaaf.assignment_id
1793: from per_people_f pap,
1794: per_assignments_f oldpaaf,
1795: per_assignments_f newpaaf
1796: where pap.person_id = c_person_id
1797: and pap.person_id = oldpaaf.person_id
1798: and oldpaaf.person_id = newpaaf.person_id
1799: and oldpaaf.assignment_type = 'E' /* Bug 5033609 */
1803: and newpaaf.effective_start_date
1804: and newpaaf.effective_start_date between oldpaaf.effective_end_date
1805: and to_date('31-12-'||c_basis_year,'DD-MM-YYYY');
1806: ---------------------------------------------------------------------------
1807: -- Bug# 2920732 - Modified the cursor to use secured view per_assignments_f
1808: ---------------------------------------------------------------------------
1809: cursor get_max_assactid( c_prev_ass_id per_all_assignments_f.assignment_id%TYPE ,
1810: c_basis_year varchar2,
1811: c_tax_unit_id pay_assignment_actions.tax_unit_id%type,
1813: is
1814: select assact1.assignment_action_id
1815: from pay_assignment_actions assact1,
1816: pay_payroll_actions pact1,
1817: per_assignments_f paaf1
1818: where assact1.tax_unit_id = c_tax_unit_id
1819: and paaf1.assignment_id = c_prev_ass_id
1820: and paaf1.assignment_id = assact1.assignment_id
1821: and pact1.payroll_action_id = assact1.payroll_action_id
1824: and assact1.action_sequence =
1825: ( select max(assact.action_sequence)
1826: from pay_assignment_actions assact,
1827: pay_payroll_actions pact,
1828: per_assignments_f paaf
1829: where paaf.assignment_id = paaf1.assignment_id
1830: and paaf.assignment_id = assact.assignment_id
1831: and pact.payroll_action_id = assact.payroll_action_id
1832: and paaf.business_group_id = paaf1.business_group_id
1864: pay_assignment_actions assact,
1865: pay_assignment_actions bal_assact,
1866: pay_payroll_actions pact,
1867: pay_payroll_actions bact,
1868: per_assignments_f ass
1869: where bal_assact.assignment_action_id = p_asg_action_id
1870: and bal_assact.payroll_action_id = bact.payroll_action_id
1871: and feed.balance_type_id = pbt.balance_type_id + decode(target.input_value_id,null,0,0)
1872: and pbt.legislation_code = 'SG'
1881: and assact.action_sequence <= bal_assact.action_sequence
1882: and assact.assignment_id = ass.assignment_id
1883: and bal_assact.assignment_id = assact.assignment_id /* added the join for bug#2227759 */
1884: and exists ( select null
1885: from per_assignments_f start_ass
1886: where start_ass.assignment_id = bal_assact.assignment_id
1887: and person_id = ass.person_id )
1888: and pact.effective_date between ass.effective_start_date and ass.effective_end_date
1889: and assact.tax_unit_id = p_tax_unit_id
1896: sum(decode(ppa.action_type,'V',-1,1)) no_of_times
1897: from pay_run_balances prb,
1898: pay_assignment_actions ASSACT,
1899: pay_payroll_actions PACT,
1900: per_assignments_f ass,
1901: pay_payroll_actions ppa,
1902: pay_assignment_actions paa
1903: where prb.defined_balance_id = c_def_balance_id
1904: and assact.assignment_action_id = p_asg_action_id
1908: and prb.action_sequence <= assact.action_sequence
1909: and prb.effective_date <= pact.effective_date
1910: and prb.balance_value <> 0
1911: and ASS.person_id = (select person_id
1912: from per_assignments_f START_ASS
1913: where START_ASS.assignment_id = assact.assignment_id
1914: and rownum = 1)
1915: and prb.effective_date between ASS.effective_start_date
1916: and ASS.effective_end_date
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,
2738: pay_assignment_actions pacmax,
2739: pay_payroll_actions ppamax
2740: where ppamax.business_group_id = c_business_group_id
2741: and pacmax.tax_unit_id = c_legal_entity_id
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
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,
3005: per_periods_of_service service
3006: where service.person_id = c_person_id
3007: and service.period_of_service_id (+)= assign.period_of_service_id
3008: and assign.effective_start_date = (
3006: where service.person_id = c_person_id
3007: and service.period_of_service_id (+)= assign.period_of_service_id
3008: and assign.effective_start_date = (
3009: select max(assign1.effective_start_date)
3010: from per_assignments_f assign1
3011: where assign1.person_id = service.person_id
3012: and assign1.assignment_type = 'E' /* Bug 5033609 */
3013: and (assign1.effective_start_date <= c_basis_end
3014: and assign1.effective_end_date >= c_basis_start)
3262: is
3263: --
3264: v_voluntary_cpf_obligatory per_assignment_extra_info.aei_information2%type;
3265: v_appr_cpf_full per_assignment_extra_info.aei_information3%type;
3266: v_assignment_id per_assignments_f.assignment_id%type;
3267: ---------------------------------------------------------------------------
3268: -- Bug# 2920732 - Modified the cursor to use the secured view per_assignments_f
3269: -- Bug# 4688761 - Modified cursor to check the legal entity, and need get the
3270: -- latest primary assignment
3264: v_voluntary_cpf_obligatory per_assignment_extra_info.aei_information2%type;
3265: v_appr_cpf_full per_assignment_extra_info.aei_information3%type;
3266: v_assignment_id per_assignments_f.assignment_id%type;
3267: ---------------------------------------------------------------------------
3268: -- Bug# 2920732 - Modified the cursor to use the secured view per_assignments_f
3269: -- Bug# 4688761 - Modified cursor to check the legal entity, and need get the
3270: -- latest primary assignment
3271: -- Bug 4890964 - Modified cursor to remove the latest assignment check, now
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 )
3281: is
3281: is
3282: select /*+ USE_NL(aei) */
3283: aei.aei_information2,
3284: aei.aei_information3
3285: from per_assignments_f assign,
3286: per_assignment_extra_info aei,
3287: hr_soft_coding_keyflex hsc
3288: where assign.person_id = c_person_id
3289: and assign.assignment_id = c_assignment_id
3329: p_basis_end in date )
3330: is
3331: --
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,
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 )
3341: is
3341: is
3342: select /*+ USE_NL(aei) */
3343: to_char(fnd_date.canonical_to_date(aei.aei_information1),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(aei.aei_information1),'MM')||'/'||
3344: to_char(fnd_date.canonical_to_date(aei.aei_information1),'DD')
3345: from per_assignments_f assign,
3346: per_assignment_extra_info aei,
3347: hr_soft_coding_keyflex hsc
3348: where assign.person_id = c_person_id
3349: and assign.assignment_id = c_assignment_id
3394: p_basis_end in date)
3395: is
3396: --
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,
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)
3406: is
3405: c_basis_end in date)
3406: is
3407:
3408: SELECT pea.segment4 bank_name
3409: FROM per_assignments_f assign,
3410: hr_soft_coding_keyflex hsc,
3411: pay_external_accounts pea,
3412: pay_personal_payment_methods_f ppm,
3413: hr_lookups hl
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
3492:
3490: c_basis_start date,
3491: c_basis_end date) is
3492:
3493: select grade.name
3494: from per_assignments_f assign,
3495: per_grades grade,
3496: hr_soft_coding_keyflex hsc
3497: where assign.person_id = c_person_id
3498: and assign.assignment_id = c_assignment_id
3501: and hsc.segment1 = c_legal_entity_id
3502: and assign.assignment_type = 'E'
3503: and assign.effective_start_date =
3504: (select max(paf1.effective_start_date)
3505: from per_assignments_f paf1
3506: where paf1.person_id = assign.person_id
3507: and paf1.assignment_id = assign.assignment_id
3508: and paf1.soft_coding_keyflex_id = assign.soft_coding_keyflex_id
3509: and paf1.effective_start_date <= c_basis_end
3524: c_basis_start date,
3525: c_basis_end date) is
3526:
3527: select jbt.name
3528: from per_assignments_f assign,
3529: per_jobs_tl jbt,
3530: hr_soft_coding_keyflex hsc
3531: where assign.person_id = c_person_id
3532: and assign.assignment_id = c_assignment_id
3536: and jbt.language = userenv('LANG')
3537: and assign.assignment_type = 'E'
3538: and assign.effective_start_date =
3539: (select max(paf1.effective_start_date)
3540: from per_assignments_f paf1
3541: where paf1.person_id = assign.person_id
3542: and paf1.assignment_id = assign.assignment_id
3543: and paf1.soft_coding_keyflex_id = assign.soft_coding_keyflex_id
3544: and paf1.effective_start_date <= c_basis_end
3559: c_basis_start date,
3560: c_basis_end date) is
3561:
3562: select pst.name
3563: from per_assignments_f assign,
3564: hr_all_positions_f_tl pst,
3565: hr_all_positions_f pos,
3566: hr_soft_coding_keyflex hsc
3567: where assign.person_id = c_person_id
3574: and assign.effective_start_date between NVL(pos.effective_start_date,to_date('01-01-1900','DD-MM-YYYY')) and NVL(pos.effective_end_date,to_date('31-12-4712','DD-MM-YYYY'))
3575: and assign.assignment_type = 'E'
3576: and assign.effective_start_date =
3577: (select max(paf1.effective_start_date)
3578: from per_assignments_f paf1
3579: where paf1.person_id = assign.person_id
3580: and paf1.assignment_id = assign.assignment_id
3581: and paf1.soft_coding_keyflex_id = assign.soft_coding_keyflex_id
3582: and paf1.effective_start_date <= c_basis_end
3628: 'SEGMENT27',ppd.segment27,
3629: 'SEGMENt28',ppd.segment28,
3630: 'SEGMENT29',ppd.segment29,
3631: 'SEGMENT30',ppd.segment30)
3632: from per_assignments_f assign,
3633: hr_soft_coding_keyflex hsc,
3634: hr_all_positions_f pos,
3635: hr_all_positions_f_tl pst,
3636: per_position_definitions ppd,
3664: and fifs.segment_name = c_er_position_seg_type
3665: and pos.position_definition_id = ppd.position_definition_id
3666: and assign.effective_start_date =
3667: (select max(paf1.effective_start_date)
3668: from per_assignments_f paf1
3669: where paf1.person_id = assign.person_id
3670: and paf1.assignment_id = assign.assignment_id
3671: and paf1.soft_coding_keyflex_id = assign.soft_coding_keyflex_id
3672: and paf1.effective_start_date <= c_basis_end
3687: c_basis_end date) is
3688:
3689: select /*+ USE_NL(aei) */
3690: aei.aei_information1
3691: from per_assignments_f assign,
3692: per_assignment_extra_info aei,
3693: hr_soft_coding_keyflex hsc
3694: where assign.person_id = c_person_id
3695: and assign.assignment_id = c_assignment_id
3778: v_cpf_overseas_post_obligatory per_assignment_extra_info.aei_information1%type;
3779: v_start_date varchar2(30);
3780: v_end_date varchar2(30);
3781: ---------------------------------------------------------------------------
3782: -- Bug# 2920732 - Modified the cursor to use the secured view per_assignments_f
3783: -- Bug# 3257843 -Modified the cursor now it selects minimum effective start date
3784: -- and maximum effective end date of the assignment
3785: -- previously it was selecting last assignemnt of the basis year.
3786: --
3794: is
3795: select aei.aei_information1,
3796: min(fnd_date.date_to_canonical(assign.effective_start_date)),
3797: max(fnd_date.date_to_canonical(nvl(assign.effective_end_date,c_basis_end)))
3798: from per_assignments_f assign,
3799: per_assignment_extra_info aei,
3800: hr_soft_coding_keyflex hsc
3801: where assign.person_id = c_person_id
3802: and assign.assignment_id = aei.assignment_id
3853: v_moa_602 number;
3854: v_grant_type_error char(1);
3855:
3856: ---------------------------------------------------------------------------
3857: -- Bug# 2920732 - Modified the cursor to use the secured view per_assignments_f
3858: -- bug 2691877
3859: -- bug 3501956 - Changed cursor to select information from per_people_extra_info table
3860: -- Bug 4314453 - Modified the cursor to use the table instead of view
3861: -- Bug 5435088 - Added grant type
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 */
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
3997:
3998: select count(distinct (paei.assignment_extra_info_id))
3996: c_basis_end date) is
3997:
3998: select count(distinct (paei.assignment_extra_info_id))
3999: from per_assignment_extra_info paei,
4000: per_assignments_f paa,
4001: hr_soft_coding_keyflex hsc
4002: where paa.person_id = c_person_id
4003: and paa.assignment_id = paei.assignment_id
4004: and paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
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
4109:
4110: cursor ir8s_c_details
4107: p_basis_start in date,
4108: p_basis_end in date) is
4109:
4110: cursor ir8s_c_details
4111: (c_person_id per_assignments_f.person_id%type,
4112: c_tax_unit_id ff_archive_item_contexts.context%type,
4113: c_business_group_id per_assignments_f.business_group_id%type,
4114: c_basis_start date,
4115: c_basis_end date) is
4109:
4110: cursor ir8s_c_details
4111: (c_person_id per_assignments_f.person_id%type,
4112: c_tax_unit_id ff_archive_item_contexts.context%type,
4113: c_business_group_id per_assignments_f.business_group_id%type,
4114: c_basis_start date,
4115: c_basis_end date) is
4116:
4117: select distinct aei.assignment_extra_info_id,
4124: aei.aei_information8 er_cpf_date,
4125: aei.aei_information9 ee_cpf,
4126: aei.aei_information10 ee_cpf_interest,
4127: aei.aei_information11 ee_cpf_date
4128: from per_assignments_f ass,
4129: per_assignment_extra_info aei,
4130: hr_soft_coding_keyflex hsc
4131: where ass.person_id = c_person_id
4132: and ass.assignment_id = aei.assignment_id
4502: c_basis_start date,
4503: c_basis_end date )
4504: is
4505: select 'X'
4506: from per_assignments_f paf,
4507: hr_soft_coding_keyflex hsc
4508: where paf.person_id = c_person_id
4509: and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
4510: and hsc.segment1 = c_tax_unit_id
4511: and paf.primary_flag = 'Y'
4512: and paf.assignment_type = 'E' /* Bug 5033609 */
4513: and paf.effective_start_date =
4514: (select max(paf1.effective_start_date)
4515: from per_assignments_f paf1 /* Bug 5858566 */
4516: where paf1.person_id = paf.person_id
4517: and paf1.assignment_type = 'E' /* Bug 5033609 */
4518: and paf1.effective_start_date <= c_basis_end
4519: and paf1.effective_end_date >= c_basis_start
4565: c_basis_start date,
4566: c_basis_end date )
4567: is
4568: select paf.assignment_id
4569: from per_assignments_f paf,
4570: hr_soft_coding_keyflex hsc
4571: where paf.person_id = c_person_id
4572: and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
4573: and hsc.segment1 = c_tax_unit_id
4574: and paf.primary_flag = 'Y'
4575: and paf.assignment_type = 'E' /* Bug 5033609 */
4576: and paf.effective_start_date =
4577: (select max(paf1.effective_start_date)
4578: from per_assignments_f paf1 /* Bug 5858566 */
4579: where paf1.person_id = paf.person_id
4580: and paf1.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
4581: and paf1.assignment_type = 'E' /* Bug 5033609 */
4582: and paf1.effective_start_date <= c_basis_end
4634: c_basis_start date,
4635: c_basis_end date )
4636: is
4637: select max(paf.assignment_id)
4638: from per_assignments_f paf,
4639: hr_soft_coding_keyflex hsc
4640: where paf.person_id = c_person_id
4641: and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
4642: and hsc.segment1 = c_tax_unit_id
4642: and hsc.segment1 = c_tax_unit_id
4643: and paf.assignment_type = 'E'
4644: and paf.effective_start_date = (
4645: select max(paf1.effective_start_date)
4646: from per_assignments_f paf1 /* Bug 5858566 */
4647: where paf1.person_id = paf.person_id
4648: and paf1.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
4649: and paf1.assignment_type = 'E' /*Bug5033609*/
4650: and paf1.effective_start_date <= c_basis_end