[Home] [Help]
255: c_legal_entity_id in number,
256: c_basis_start in date,
257: c_basis_end in date) is
258: select max(paf.assignment_id)
259: from per_assignments_f paf,
260: pay_payroll_actions ppa1
261: where ppa1.payroll_action_id = c_payroll_action_id
262: and paf.person_id between c_start_person_id and c_end_person_id
263: and ppa1.business_group_id = paf.business_group_id
433: l_person_id per_all_people_f.person_id%type;
434: l_archived_person_id binary_integer;
435:
436: ------------------------------------------------------------------------
437: -- Bug 2920732 - Modified the cursor to use secured views per_people_f, per_assignments_f
438: -- Bug 3260855 - Modified the cusor to fetch only person_id, asg_id instead of
439: -- legislative parameters as global values can be used, which are initialized in Init_code.
440: ------------------------------------------------------------------------
441: cursor get_details( c_assignment_action_id pay_assignment_actions.assignment_action_id%type ) is
442: select pap.person_id,
443: nvl(pap.national_identifier,pap.per_information12),
444: pac.assignment_id
445: from pay_assignment_actions pac,
446: per_assignments_f paa,
447: per_people_f pap
448: where pac.assignment_action_id = c_assignment_action_id
449: and paa.assignment_id = pac.assignment_id
450: and paa.person_id = pap.person_id;
1133: and pbd.dimension_name = '_PER_LE_YTD';
1134: ---------------------------------------------------------------------------------------------------
1135: -- Bug 2629839. Cursor month_year_action is split into two cursors month_year_action_sequence and
1136: -- month_year_action to improve the performance
1137: -- Bug# 2920732 - Modified the cursor to use secured view per_assignments_f
1138: -- Cursor month_year_action_sequence
1139: ---------------------------------------------------------------------------------------------------
1140: cursor month_year_action_sequence
1141: ( c_person_id per_all_people_f.person_id%type,
1145: is
1146: select /*+ ORDERED USE_NL(pacmax) */
1147: max(pacmax.action_sequence) act_seq,
1148: to_char(ppamax.effective_date,'MM')
1149: from per_assignments_f paamax,
1150: pay_assignment_actions pacmax,
1151: pay_payroll_actions ppamax
1152: where ppamax.business_group_id = c_business_group_id
1153: and pacmax.tax_unit_id = c_legal_entity_id
1172: select /*+ ORDERED USE_NL(pac) */
1173: pac.assignment_action_id assact_id,
1174: decode(ppa.action_type,'V',fnd_date.date_to_canonical(ppa.effective_date),fnd_date.date_to_canonical(ppa.date_earned)) date_earned,
1175: pac.tax_unit_id tax_uid
1176: from per_assignments_f paa,
1177: pay_assignment_actions pac,
1178: pay_payroll_actions ppa
1179: where ppa.business_group_id = c_business_group_id
1180: and pac.tax_unit_id = c_legal_entity_id
1792:
1793: ---------------------------------------------------------------------------
1794: -- Bug# 2833530
1795: -- Added check_termination and get_max_assactid cursors
1796: -- Bug# 2920732 - Modified the cursor to use secured views per_people_f, per_assignments_f
1797: ---------------------------------------------------------------------------
1798: cursor check_termination( c_person_id per_all_people_f.person_id%TYPE,
1799: c_basis_year varchar2 )
1800: is
1801: select 'Y',
1802: oldpaaf.assignment_id,
1803: newpaaf.assignment_id
1804: from per_people_f pap,
1805: per_assignments_f oldpaaf,
1806: per_assignments_f newpaaf
1807: where pap.person_id = c_person_id
1808: and pap.person_id = oldpaaf.person_id
1809: and oldpaaf.person_id = newpaaf.person_id
1802: oldpaaf.assignment_id,
1803: newpaaf.assignment_id
1804: from per_people_f pap,
1805: per_assignments_f oldpaaf,
1806: per_assignments_f newpaaf
1807: where pap.person_id = c_person_id
1808: and pap.person_id = oldpaaf.person_id
1809: and oldpaaf.person_id = newpaaf.person_id
1810: and oldpaaf.assignment_type = 'E' /* Bug 5033609 */
1814: and newpaaf.effective_start_date
1815: and newpaaf.effective_start_date between oldpaaf.effective_end_date
1816: and to_date('31-12-'||c_basis_year,'DD-MM-YYYY');
1817: ---------------------------------------------------------------------------
1818: -- Bug# 2920732 - Modified the cursor to use secured view per_assignments_f
1819: ---------------------------------------------------------------------------
1820: cursor get_max_assactid( c_prev_ass_id per_all_assignments_f.assignment_id%TYPE ,
1821: c_basis_year varchar2,
1822: c_tax_unit_id pay_assignment_actions.tax_unit_id%type,
1824: is
1825: select assact1.assignment_action_id
1826: from pay_assignment_actions assact1,
1827: pay_payroll_actions pact1,
1828: per_assignments_f paaf1
1829: where assact1.tax_unit_id = c_tax_unit_id
1830: and paaf1.assignment_id = c_prev_ass_id
1831: and paaf1.assignment_id = assact1.assignment_id
1832: and pact1.payroll_action_id = assact1.payroll_action_id
1835: and assact1.action_sequence =
1836: ( select max(assact.action_sequence)
1837: from pay_assignment_actions assact,
1838: pay_payroll_actions pact,
1839: per_assignments_f paaf
1840: where paaf.assignment_id = paaf1.assignment_id
1841: and paaf.assignment_id = assact.assignment_id
1842: and pact.payroll_action_id = assact.payroll_action_id
1843: and paaf.business_group_id = paaf1.business_group_id
1875: pay_assignment_actions assact,
1876: pay_assignment_actions bal_assact,
1877: pay_payroll_actions pact,
1878: pay_payroll_actions bact,
1879: per_assignments_f ass
1880: where bal_assact.assignment_action_id = p_asg_action_id
1881: and bal_assact.payroll_action_id = bact.payroll_action_id
1882: and feed.balance_type_id = pbt.balance_type_id + decode(target.input_value_id,null,0,0)
1883: and pbt.legislation_code = 'SG'
1892: and assact.action_sequence <= bal_assact.action_sequence
1893: and assact.assignment_id = ass.assignment_id
1894: and bal_assact.assignment_id = assact.assignment_id /* added the join for bug#2227759 */
1895: and exists ( select null
1896: from per_assignments_f start_ass
1897: where start_ass.assignment_id = bal_assact.assignment_id
1898: and person_id = ass.person_id )
1899: and pact.effective_date between ass.effective_start_date and ass.effective_end_date
1900: and assact.tax_unit_id = p_tax_unit_id
1907: sum(decode(ppa.action_type,'V',-1,1)) no_of_times
1908: from pay_run_balances prb,
1909: pay_assignment_actions ASSACT,
1910: pay_payroll_actions PACT,
1911: per_assignments_f ass,
1912: pay_payroll_actions ppa,
1913: pay_assignment_actions paa
1914: where prb.defined_balance_id = c_def_balance_id
1915: and assact.assignment_action_id = p_asg_action_id
1919: and prb.action_sequence <= assact.action_sequence
1920: and prb.effective_date <= pact.effective_date
1921: and prb.balance_value <> 0
1922: and ASS.person_id = (select person_id
1923: from per_assignments_f START_ASS
1924: where START_ASS.assignment_id = assact.assignment_id
1925: and rownum = 1)
1926: and prb.effective_date between ASS.effective_start_date
1927: and ASS.effective_end_date
2573: select
2574: count(distinct pei.person_extra_info_id)
2575: from per_all_people_f pap,
2576: per_people_extra_info pei,
2577: per_assignments_f assign,
2578: hr_soft_coding_keyflex hsc
2579: where pap.person_id = pei.person_id
2580: and pei.information_type = 'HR_STOCK_EXERCISE_SG'
2581: and pap.person_id = pei.person_id
2785: c_person_id per_all_people_f.person_id%type,
2786: c_basis_year varchar2)
2787: is
2788: select fnd_date.date_to_canonical(max(ppamax.effective_date))
2789: from per_assignments_f paamax,
2790: pay_assignment_actions pacmax,
2791: pay_payroll_actions ppamax
2792: where ppamax.business_group_id = c_business_group_id
2793: and pacmax.tax_unit_id = c_legal_entity_id
3058: c_basis_end date )
3059: is
3060: select min(assign.effective_start_date),
3061: max(assign.effective_start_date)
3062: from per_assignments_f assign,
3063: hr_soft_coding_keyflex hsc,
3064: per_assignment_status_types sta
3065: where assign.person_id = c_person_id
3066: and assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3077: c_legal_entity_id hr_organization_units.organization_id%type,
3078: c_date date )
3079: is
3080: select 'Y'
3081: from per_assignments_f assign,
3082: hr_soft_coding_keyflex hsc,
3083: per_assignment_status_types sta
3084: where assign.person_id = c_person_id
3085: and assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3096: c_basis_end date )
3097: is
3098: select min(assign.effective_end_date),
3099: max(assign.effective_end_date)
3100: from per_assignments_f assign,
3101: hr_soft_coding_keyflex hsc,
3102: per_assignment_status_types sta
3103: where assign.person_id = c_person_id
3104: and assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
3391: is
3392: --
3393: v_voluntary_cpf_obligatory per_assignment_extra_info.aei_information2%type;
3394: v_appr_cpf_full per_assignment_extra_info.aei_information3%type;
3395: v_assignment_id per_assignments_f.assignment_id%type;
3396: ---------------------------------------------------------------------------
3397: -- Bug# 2920732 - Modified the cursor to use the secured view per_assignments_f
3398: -- Bug# 4688761 - Modified cursor to check the legal entity, and need get the
3399: -- latest primary assignment
3393: v_voluntary_cpf_obligatory per_assignment_extra_info.aei_information2%type;
3394: v_appr_cpf_full per_assignment_extra_info.aei_information3%type;
3395: v_assignment_id per_assignments_f.assignment_id%type;
3396: ---------------------------------------------------------------------------
3397: -- Bug# 2920732 - Modified the cursor to use the secured view per_assignments_f
3398: -- Bug# 4688761 - Modified cursor to check the legal entity, and need get the
3399: -- latest primary assignment
3400: -- Bug 4890964 - Modified cursor to remove the latest assignment check, now
3401: -- we pass the assignment_id as a parameter
3402: -- Bug 5435088 - Added field Approval from CPF to make full
3403: ---------------------------------------------------------------------------
3404: cursor assignment_eits
3405: ( c_person_id per_all_people_f.person_id%type,
3406: c_assignment_id per_assignments_f.assignment_id%type,
3407: c_legal_entity_id hr_organization_units.organization_id%type,
3408: c_basis_start date,
3409: c_basis_end date )
3410: is
3410: is
3411: select /*+ USE_NL(aei) */
3412: aei.aei_information2,
3413: aei.aei_information3
3414: from per_assignments_f assign,
3415: per_assignment_extra_info aei,
3416: hr_soft_coding_keyflex hsc
3417: where assign.person_id = c_person_id
3418: and assign.assignment_id = c_assignment_id
3458: p_basis_end in date )
3459: is
3460: --
3461: v_ass_bonus_date varchar2(10);
3462: v_assignment_id per_assignments_f.assignment_id%type;
3463:
3464: cursor ass_bonus_date_eits
3465: ( c_person_id per_all_people_f.person_id%type,
3466: c_assignment_id per_assignments_f.assignment_id%type,
3462: v_assignment_id per_assignments_f.assignment_id%type;
3463:
3464: cursor ass_bonus_date_eits
3465: ( c_person_id per_all_people_f.person_id%type,
3466: c_assignment_id per_assignments_f.assignment_id%type,
3467: c_legal_entity_id hr_organization_units.organization_id%type,
3468: c_basis_start date,
3469: c_basis_end date )
3470: is
3470: is
3471: select /*+ USE_NL(aei) */
3472: to_char(fnd_date.canonical_to_date(aei.aei_information1),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(aei.aei_information1),'MM')||'/'||
3473: to_char(fnd_date.canonical_to_date(aei.aei_information1),'DD')
3474: from per_assignments_f assign,
3475: per_assignment_extra_info aei,
3476: hr_soft_coding_keyflex hsc
3477: where assign.person_id = c_person_id
3478: and assign.assignment_id = c_assignment_id
3523: p_basis_end in date)
3524: is
3525: --
3526: v_ass_bank_name varchar2(10);
3527: v_assignment_id per_assignments_f.assignment_id%type;
3528:
3529: cursor ass_payment_method
3530: ( c_person_id per_all_people_f.person_id%type,
3531: c_assignment_id per_assignments_f.assignment_id%type,
3527: v_assignment_id per_assignments_f.assignment_id%type;
3528:
3529: cursor ass_payment_method
3530: ( c_person_id per_all_people_f.person_id%type,
3531: c_assignment_id per_assignments_f.assignment_id%type,
3532: c_legal_entity_id hr_organization_units.organization_id%type,
3533: c_basis_start in date,
3534: c_basis_end in date)
3535: is
3534: c_basis_end in date)
3535: is
3536:
3537: SELECT pea.segment4 bank_name
3538: FROM per_assignments_f assign,
3539: hr_soft_coding_keyflex hsc,
3540: pay_external_accounts pea,
3541: pay_personal_payment_methods_f ppm,
3542: hr_lookups hl
3614: -- assignment
3615: ---------------------------------------------------------------------------
3616: cursor grade
3617: ( c_person_id per_all_people_f.person_id%type,
3618: c_assignment_id per_assignments_f.assignment_id%type,
3619: c_legal_entity_id hr_organization_units.organization_id%type,
3620: c_basis_start date,
3621: c_basis_end date) is
3622:
3620: c_basis_start date,
3621: c_basis_end date) is
3622:
3623: select grade.name
3624: from per_assignments_f assign,
3625: per_grades grade,
3626: hr_soft_coding_keyflex hsc
3627: where assign.person_id = c_person_id
3628: and assign.assignment_id = c_assignment_id
3631: and hsc.segment1 = c_legal_entity_id
3632: and assign.assignment_type = 'E'
3633: and assign.effective_start_date =
3634: (select max(paf1.effective_start_date)
3635: from per_assignments_f paf1
3636: where paf1.person_id = assign.person_id
3637: and paf1.assignment_id = assign.assignment_id
3638: and paf1.soft_coding_keyflex_id = assign.soft_coding_keyflex_id
3639: and paf1.effective_start_date <= c_basis_end
3654: c_basis_start date,
3655: c_basis_end date) is
3656:
3657: select jbt.name
3658: from per_assignments_f assign,
3659: per_jobs_tl jbt,
3660: hr_soft_coding_keyflex hsc
3661: where assign.person_id = c_person_id
3662: and assign.assignment_id = c_assignment_id
3666: and jbt.language = userenv('LANG')
3667: and assign.assignment_type = 'E'
3668: and assign.effective_start_date =
3669: (select max(paf1.effective_start_date)
3670: from per_assignments_f paf1
3671: where paf1.person_id = assign.person_id
3672: and paf1.assignment_id = assign.assignment_id
3673: and paf1.soft_coding_keyflex_id = assign.soft_coding_keyflex_id
3674: and paf1.effective_start_date <= c_basis_end
3689: c_basis_start date,
3690: c_basis_end date) is
3691:
3692: select pst.name
3693: from per_assignments_f assign,
3694: hr_all_positions_f_tl pst,
3695: hr_all_positions_f pos,
3696: hr_soft_coding_keyflex hsc
3697: where assign.person_id = c_person_id
3704: 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'))
3705: and assign.assignment_type = 'E'
3706: and assign.effective_start_date =
3707: (select max(paf1.effective_start_date)
3708: from per_assignments_f paf1
3709: where paf1.person_id = assign.person_id
3710: and paf1.assignment_id = assign.assignment_id
3711: and paf1.soft_coding_keyflex_id = assign.soft_coding_keyflex_id
3712: and paf1.effective_start_date <= c_basis_end
3758: 'SEGMENT27',ppd.segment27,
3759: 'SEGMENt28',ppd.segment28,
3760: 'SEGMENT29',ppd.segment29,
3761: 'SEGMENT30',ppd.segment30)
3762: from per_assignments_f assign,
3763: hr_soft_coding_keyflex hsc,
3764: hr_all_positions_f pos,
3765: hr_all_positions_f_tl pst,
3766: per_position_definitions ppd,
3794: and fifs.segment_name = c_er_position_seg_type
3795: and pos.position_definition_id = ppd.position_definition_id
3796: and assign.effective_start_date =
3797: (select max(paf1.effective_start_date)
3798: from per_assignments_f paf1
3799: where paf1.person_id = assign.person_id
3800: and paf1.assignment_id = assign.assignment_id
3801: and paf1.soft_coding_keyflex_id = assign.soft_coding_keyflex_id
3802: and paf1.effective_start_date <= c_basis_end
3817: c_basis_end date) is
3818:
3819: select /*+ USE_NL(aei) */
3820: aei.aei_information1
3821: from per_assignments_f assign,
3822: per_assignment_extra_info aei,
3823: hr_soft_coding_keyflex hsc
3824: where assign.person_id = c_person_id
3825: and assign.assignment_id = c_assignment_id
3908: v_cpf_overseas_post_obligatory per_assignment_extra_info.aei_information1%type;
3909: v_start_date varchar2(30);
3910: v_end_date varchar2(30);
3911: ---------------------------------------------------------------------------
3912: -- Bug# 2920732 - Modified the cursor to use the secured view per_assignments_f
3913: -- Bug# 3257843 -Modified the cursor now it selects minimum effective start date
3914: -- and maximum effective end date of the assignment
3915: -- previously it was selecting last assignemnt of the basis year.
3916: --
3924: is
3925: select aei.aei_information1,
3926: min(fnd_date.date_to_canonical(assign.effective_start_date)),
3927: max(fnd_date.date_to_canonical(nvl(assign.effective_end_date,c_basis_end)))
3928: from per_assignments_f assign,
3929: per_assignment_extra_info aei,
3930: hr_soft_coding_keyflex hsc
3931: where assign.person_id = c_person_id
3932: and assign.assignment_id = aei.assignment_id
3997: v_archive char(1);
3998:
3999:
4000: ---------------------------------------------------------------------------
4001: -- Bug# 2920732 - Modified the cursor to use the secured view per_assignments_f
4002: -- bug 2691877
4003: -- bug 3501956 - Changed cursor to select information from per_people_extra_info table
4004: -- Bug 4314453 - Modified the cursor to use the table instead of view
4005: -- Bug 5435088 - Added grant type
4235: procedure archive_ir8s_c_details
4236: (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
4237: p_person_id in per_all_people_f.person_id%type,
4238: p_tax_unit_id in ff_archive_item_contexts.context%type,
4239: p_business_group_id in per_assignments_f.business_group_id%type,
4240: p_basis_start in date,
4241: p_basis_end in date) is
4242:
4243: /* Type to store the person ids with same national_identifier */
4250:
4251: cursor ir8s_c_invalid_records
4252: (c_person_id per_all_people_f.person_id%type,
4253: c_tax_unit_id ff_archive_item_contexts.context%type,
4254: c_business_group_id per_assignments_f.business_group_id%type,
4255: c_basis_start date,
4256: c_basis_end date) is
4257:
4258: select count(distinct (paei.assignment_extra_info_id))
4256: c_basis_end date) is
4257:
4258: select count(distinct (paei.assignment_extra_info_id))
4259: from per_assignment_extra_info paei,
4260: per_assignments_f paa,
4261: hr_soft_coding_keyflex hsc
4262: where paa.person_id = c_person_id
4263: and paa.assignment_id = paei.assignment_id
4264: and paa.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
4362: (p_assignment_action_id in pay_assignment_actions.assignment_action_id%type,
4363: p_1_person_id in per_all_people_f.person_id%type,
4364: p_person_id in per_all_people_f.person_id%type,
4365: p_tax_unit_id in ff_archive_item_contexts.context%type,
4366: p_business_group_id in per_assignments_f.business_group_id%type,
4367: p_basis_start in date,
4368: p_basis_end in date) is
4369:
4370: cursor ir8s_c_details
4367: p_basis_start in date,
4368: p_basis_end in date) is
4369:
4370: cursor ir8s_c_details
4371: (c_person_id per_assignments_f.person_id%type,
4372: c_tax_unit_id ff_archive_item_contexts.context%type,
4373: c_business_group_id per_assignments_f.business_group_id%type,
4374: c_basis_start date,
4375: c_basis_end date) is
4369:
4370: cursor ir8s_c_details
4371: (c_person_id per_assignments_f.person_id%type,
4372: c_tax_unit_id ff_archive_item_contexts.context%type,
4373: c_business_group_id per_assignments_f.business_group_id%type,
4374: c_basis_start date,
4375: c_basis_end date) is
4376:
4377: select distinct aei.assignment_extra_info_id,
4384: aei.aei_information8 er_cpf_date,
4385: aei.aei_information9 ee_cpf,
4386: aei.aei_information10 ee_cpf_interest,
4387: aei.aei_information11 ee_cpf_date
4388: from per_assignments_f ass,
4389: per_assignment_extra_info aei,
4390: hr_soft_coding_keyflex hsc
4391: where ass.person_id = c_person_id
4392: and ass.assignment_id = aei.assignment_id
4762: c_basis_start date,
4763: c_basis_end date )
4764: is
4765: select 'X'
4766: from per_assignments_f paf,
4767: hr_soft_coding_keyflex hsc
4768: where paf.person_id = c_person_id
4769: and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
4770: and hsc.segment1 = c_tax_unit_id
4771: and paf.primary_flag = 'Y'
4772: and paf.assignment_type = 'E' /* Bug 5033609 */
4773: and paf.effective_start_date =
4774: (select max(paf1.effective_start_date)
4775: from per_assignments_f paf1 /* Bug 5858566 */
4776: where paf1.person_id = paf.person_id
4777: and paf1.assignment_type = 'E' /* Bug 5033609 */
4778: and paf1.effective_start_date <= c_basis_end
4779: and paf1.effective_end_date >= c_basis_start
4825: c_basis_start date,
4826: c_basis_end date )
4827: is
4828: select paf.assignment_id
4829: from per_assignments_f paf,
4830: hr_soft_coding_keyflex hsc
4831: where paf.person_id = c_person_id
4832: and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
4833: and hsc.segment1 = c_tax_unit_id
4834: and paf.primary_flag = 'Y'
4835: and paf.assignment_type = 'E' /* Bug 5033609 */
4836: and paf.effective_start_date =
4837: (select max(paf1.effective_start_date)
4838: from per_assignments_f paf1 /* Bug 5858566 */
4839: where paf1.person_id = paf.person_id
4840: and paf1.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
4841: and paf1.assignment_type = 'E' /* Bug 5033609 */
4842: and paf1.effective_start_date <= c_basis_end
4894: c_basis_start date,
4895: c_basis_end date )
4896: is
4897: select max(paf.assignment_id)
4898: from per_assignments_f paf,
4899: hr_soft_coding_keyflex hsc
4900: where paf.person_id = c_person_id
4901: and paf.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
4902: and hsc.segment1 = c_tax_unit_id
4902: and hsc.segment1 = c_tax_unit_id
4903: and paf.assignment_type = 'E'
4904: and paf.effective_start_date = (
4905: select max(paf1.effective_start_date)
4906: from per_assignments_f paf1 /* Bug 5858566 */
4907: where paf1.person_id = paf.person_id
4908: and paf1.soft_coding_keyflex_id = paf.soft_coding_keyflex_id
4909: and paf1.assignment_type = 'E' /*Bug5033609*/
4910: and paf1.effective_start_date <= c_basis_end