[Home] [Help]
1170: c_action_sequence pay_assignment_actions.action_sequence%type )
1171: is
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
1386: ----------------------------------------------------------------------------------
1387: -- Start Bug 3038605 - Store the months which have payroll runs.
1388: -- Bug: 3205321 - Store Month in MM format in month_recs
1389: ----------------------------------------------------------------------------------
1390: month_recs(l_payroll_mon_counter).month := to_number(to_char(fnd_date.canonical_to_date(month_year_action_rec.date_earned),'MM'));
1391: l_payroll_mon_counter := l_payroll_mon_counter+1;
1392: ----------------------------------------------------------------------------------
1393: -- Bulk Balance Fetch for Bug 3064282
1394: ----------------------------------------------------------------------------------
1430: for counter in 1..t_month_balanceid_store.count
1431: loop
1432: mtd_balance_rec(l_mtd_counter).balance_id := t_month_balanceid_store(counter).user_entity_id;
1433: mtd_balance_rec(l_mtd_counter).balance_value := nvl(g_detailed_bal_out_tab(counter).balance_value,0);
1434: mtd_balance_rec(l_mtd_counter).date_earned := to_char(fnd_date.canonical_to_date(month_year_action_rec.date_earned),'MMYYYY');
1435: mtd_balance_rec(l_mtd_counter).date_earned_archive := month_year_action_rec.date_earned;
1436: mtd_balance_rec(l_mtd_counter).person_id := person_id_rec(l_person_counter).person_id;
1437: mtd_balance_rec(l_mtd_counter).archive_status := 'Y';
1438: l_mtd_counter := l_mtd_counter + 1;
1864:
1865: --
1866: cursor balance_dates_rr
1867: is
1868: select fnd_date.date_to_canonical(min(pact.date_earned)) date_from,
1869: fnd_date.date_to_canonical(max(pact.date_earned)) date_to,
1870: sum(decode(pact.action_type,'V',-1,1)) no_of_times
1871: from pay_run_result_values target,
1872: pay_balance_feeds_f feed,
1865: --
1866: cursor balance_dates_rr
1867: is
1868: select fnd_date.date_to_canonical(min(pact.date_earned)) date_from,
1869: fnd_date.date_to_canonical(max(pact.date_earned)) date_to,
1870: sum(decode(pact.action_type,'V',-1,1)) no_of_times
1871: from pay_run_result_values target,
1872: pay_balance_feeds_f feed,
1873: pay_balance_types pbt,
1901: and pact.effective_date >= trunc(bact.effective_date,'Y');
1902: --
1903: cursor balance_dates_rb
1904: is
1905: select fnd_date.date_to_canonical(min(prb.effective_date)) date_from,
1906: fnd_date.date_to_canonical(max(prb.effective_date)) date_to,
1907: sum(decode(ppa.action_type,'V',-1,1)) no_of_times
1908: from pay_run_balances prb,
1909: pay_assignment_actions ASSACT,
1902: --
1903: cursor balance_dates_rb
1904: is
1905: select fnd_date.date_to_canonical(min(prb.effective_date)) date_from,
1906: fnd_date.date_to_canonical(max(prb.effective_date)) date_to,
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,
2367: ------------------------------------------------------------------------------
2368: end if;
2369: --
2370: if v_date_to is not null and v_moa_411_date is not null then
2371: if fnd_date.canonical_to_date(v_date_to) > fnd_date.canonical_to_date(v_moa_411_date) then
2372: v_moa_411_date := v_date_to;
2373: else
2374: v_no_of_times := v_no_of_times_411;
2375: end if;
2467: v_date_to ,
2468: v_no_of_times);
2469: ------------------------------------------------------------------------------
2470: if v_date_to is not null and v_moa_413_date is not null then
2471: if fnd_date.canonical_to_date(v_date_to) > fnd_date.canonical_to_date(v_moa_413_date) then
2472: v_moa_413_date := v_date_to;
2473: else
2474: v_no_of_times := v_no_of_times_413;
2475: end if;
2583: and assign.soft_coding_keyflex_id = hsc.soft_coding_keyflex_id
2584: and hsc.segment1 = to_char(c_legal_entity_id)
2585: and assign.business_group_id = c_business_group_id
2586: and pei.pei_information1 = 'N'
2587: and to_char(fnd_date.canonical_to_date(pei.pei_information5),'YYYY') = to_char(c_basis_end,'YYYY')
2588: and (pap.effective_start_date <= c_basis_end and pap.effective_end_date >= c_basis_start);
2589:
2590:
2591: begin
2663: archive_item ('X_SG_LEGAL_ENTITY_SG_A8B_INCORP_DATE', p_assignment_action_id, g_er_incorp_date);
2664: archive_item ('X_IRAS_METHOD', p_assignment_action_id, g_iras_method);
2665: -- Bug 7415444, this is for A8B NSOP
2666: if g_er_incorp_date is not null then
2667: g_er_incorp_date_1 := to_char(fnd_date.canonical_to_date(g_er_incorp_date),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(g_er_incorp_date),'MM')||'/'||to_char(fnd_date.canonical_to_date(g_er_incorp_date),'DD');
2668: g_er_incorp_date_2 := to_char(to_number(to_char(fnd_date.canonical_to_date(g_er_incorp_date),'YYYY'))+3)||'/'||to_char(fnd_date.canonical_to_date(g_er_incorp_date),'MM')||'/'||to_char(fnd_date.canonical_to_date(g_er_incorp_date),'DD');
2669: if v_shares_nsop_count = 0 then
2670: archive_item ('X_SG_LE_A8B_INCORP_DATE_ERROR', p_assignment_action_id, 'Y');
2671: end if;
2664: archive_item ('X_IRAS_METHOD', p_assignment_action_id, g_iras_method);
2665: -- Bug 7415444, this is for A8B NSOP
2666: if g_er_incorp_date is not null then
2667: g_er_incorp_date_1 := to_char(fnd_date.canonical_to_date(g_er_incorp_date),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(g_er_incorp_date),'MM')||'/'||to_char(fnd_date.canonical_to_date(g_er_incorp_date),'DD');
2668: g_er_incorp_date_2 := to_char(to_number(to_char(fnd_date.canonical_to_date(g_er_incorp_date),'YYYY'))+3)||'/'||to_char(fnd_date.canonical_to_date(g_er_incorp_date),'MM')||'/'||to_char(fnd_date.canonical_to_date(g_er_incorp_date),'DD');
2669: if v_shares_nsop_count = 0 then
2670: archive_item ('X_SG_LE_A8B_INCORP_DATE_ERROR', p_assignment_action_id, 'Y');
2671: end if;
2672: end if;
2673:
2674: -- Bug 5078454, if moa369 balance is not zero, then store the date
2675: -- from LE to the global value g_moa_369_date if it is not blank
2676: if g_moa_369_date is not null then
2677: if to_char(fnd_date.canonical_to_date(g_er_bonus_date),'YYYY') = to_char(g_basis_end,'YYYY') then
2678: g_moa_369_date := to_char(fnd_date.canonical_to_date(g_er_bonus_date),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(g_er_bonus_date),'MM')||'/'||to_char(fnd_date.canonical_to_date(g_er_bonus_date),'DD');
2679: end if;
2680: end if;
2681:
2674: -- Bug 5078454, if moa369 balance is not zero, then store the date
2675: -- from LE to the global value g_moa_369_date if it is not blank
2676: if g_moa_369_date is not null then
2677: if to_char(fnd_date.canonical_to_date(g_er_bonus_date),'YYYY') = to_char(g_basis_end,'YYYY') then
2678: g_moa_369_date := to_char(fnd_date.canonical_to_date(g_er_bonus_date),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(g_er_bonus_date),'MM')||'/'||to_char(fnd_date.canonical_to_date(g_er_bonus_date),'DD');
2679: end if;
2680: end if;
2681:
2682: -- Added for bug 4890964, the info. based on the assignment should
2784: c_legal_entity_id hr_organization_units.organization_id%type,
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
2840: c_basis_end date )
2841: is
2842: select people.national_identifier,
2843: h.meaning,
2844: fnd_date.date_to_canonical(people.date_of_birth)
2845: from per_people_f people,
2846: hr_lookups h
2847: where people.person_id = c_person_id
2848: and people.effective_start_date = (
2988: c_basis_end date) is
2989: select addr.address_line1,
2990: addr.address_line2,
2991: addr.address_line3,
2992: fnd_date.date_to_canonical(addr.date_from),
2993: fnd_date.date_to_canonical(nvl(addr.date_to,c_basis_end))/* if its not blank, return the real end date, bug 2654499 */
2994: from per_addresses addr,
2995: fnd_territories_tl a
2996: where addr.person_id (+) = c_person_id
2989: select addr.address_line1,
2990: addr.address_line2,
2991: addr.address_line3,
2992: fnd_date.date_to_canonical(addr.date_from),
2993: fnd_date.date_to_canonical(nvl(addr.date_to,c_basis_end))/* if its not blank, return the real end date, bug 2654499 */
2994: from per_addresses addr,
2995: fnd_territories_tl a
2996: where addr.person_id (+) = c_person_id
2997: and a.territory_code (+)= addr.country
3152: end if;
3153:
3154:
3155: if v_ass_start_date is not null then
3156: archive_item ('X_EMP_HIRE_DATE', p_assignment_action_id, fnd_date.date_to_canonical(v_ass_start_date));
3157: end if;
3158:
3159: if v_ass_end_date is not null then
3160: archive_item ('X_EMP_TERM_DATE', p_assignment_action_id, fnd_date.date_to_canonical(v_ass_end_date));
3156: archive_item ('X_EMP_HIRE_DATE', p_assignment_action_id, fnd_date.date_to_canonical(v_ass_start_date));
3157: end if;
3158:
3159: if v_ass_end_date is not null then
3160: archive_item ('X_EMP_TERM_DATE', p_assignment_action_id, fnd_date.date_to_canonical(v_ass_end_date));
3161: end if;
3162: hr_utility.trace('X_EMP_HIRE_DATE:'||fnd_date.date_to_canonical(v_ass_start_date));
3163: hr_utility.trace('X_EMP_TERM_DATE:'||fnd_date.date_to_canonical(v_ass_end_date));
3164:
3158:
3159: if v_ass_end_date is not null then
3160: archive_item ('X_EMP_TERM_DATE', p_assignment_action_id, fnd_date.date_to_canonical(v_ass_end_date));
3161: end if;
3162: hr_utility.trace('X_EMP_HIRE_DATE:'||fnd_date.date_to_canonical(v_ass_start_date));
3163: hr_utility.trace('X_EMP_TERM_DATE:'||fnd_date.date_to_canonical(v_ass_end_date));
3164:
3165: --
3166: if g_debug then
3159: if v_ass_end_date is not null then
3160: archive_item ('X_EMP_TERM_DATE', p_assignment_action_id, fnd_date.date_to_canonical(v_ass_end_date));
3161: end if;
3162: hr_utility.trace('X_EMP_HIRE_DATE:'||fnd_date.date_to_canonical(v_ass_start_date));
3163: hr_utility.trace('X_EMP_TERM_DATE:'||fnd_date.date_to_canonical(v_ass_end_date));
3164:
3165: --
3166: if g_debug then
3167: hr_utility.set_location('pysgirar: End of archive_emp_details',20);
3197: is
3198: select people.per_information1,
3199: people.per_information3,
3200: people.per_information6,
3201: to_char(fnd_date.canonical_to_date(people.per_information9),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(people.per_information9),'MM')||'/'||to_char(fnd_date.canonical_to_date(people.per_information9),'DD'), -- Bug 5435088
3202: -- fnd_date.canonical_to_date(people.per_information9), -- Bug 5435088
3203: people.per_information12,
3204: people.per_information23
3205: from per_people_f people
3198: select people.per_information1,
3199: people.per_information3,
3200: people.per_information6,
3201: to_char(fnd_date.canonical_to_date(people.per_information9),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(people.per_information9),'MM')||'/'||to_char(fnd_date.canonical_to_date(people.per_information9),'DD'), -- Bug 5435088
3202: -- fnd_date.canonical_to_date(people.per_information9), -- Bug 5435088
3203: people.per_information12,
3204: people.per_information23
3205: from per_people_f people
3206: where people.person_id = c_person_id
3308: indicators.pei_information2,
3309: indicators.pei_information3,
3310: indicators.pei_information4, -- Exempt Remission
3311: indicators.pei_information5, -- Approval from IRAS
3312: to_char(fnd_date.canonical_to_date(indicators.pei_information6),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(indicators.pei_information6),'MM')||'/'||to_char(fnd_date.canonical_to_date(indicators.pei_information6),'DD'), -- Date of Approval
3313: details.pei_information3,
3314: details.pei_information4,
3315: details.pei_information6, -- Name of bank
3316: info.pei_information1
3468: c_basis_start date,
3469: c_basis_end date )
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
3469: c_basis_end date )
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
3481: and hsc.segment1 = c_legal_entity_id
3482: and aei.information_type = 'HR_NON_CONT_BONUS_INFO_SG'
3483: and aei.aei_information1 is not NULL
3484: and assign.assignment_type = 'E'
3485: and to_char(fnd_date.canonical_to_date(aei.aei_information1),'YYYY') = to_char(c_basis_end,'YYYY');
3486:
3487: --
3488: begin
3489:
3922: c_basis_start date,
3923: c_basis_end date )
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
3923: c_basis_end date )
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
4013: select distinct pei.person_extra_info_id,
4014: pei.pei_information1 stock_option,
4015: fnd_number.canonical_to_number(pei.pei_information3) exercise_price,
4016: fnd_number.canonical_to_number(pei.pei_information4) market_exercise_value,
4017: to_char(fnd_date.canonical_to_date(pei.pei_information5),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(pei.pei_information5),'MM')||'/'||
4018: to_char(fnd_date.canonical_to_date(pei.pei_information5),'DD') exercise_date,
4019: fnd_number.canonical_to_number(pei.pei_information6) shares_acquired,
4020: hoi1.org_information1 name_of_company,
4021: hoi1.org_information4 RCB,
4014: pei.pei_information1 stock_option,
4015: fnd_number.canonical_to_number(pei.pei_information3) exercise_price,
4016: fnd_number.canonical_to_number(pei.pei_information4) market_exercise_value,
4017: to_char(fnd_date.canonical_to_date(pei.pei_information5),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(pei.pei_information5),'MM')||'/'||
4018: to_char(fnd_date.canonical_to_date(pei.pei_information5),'DD') exercise_date,
4019: fnd_number.canonical_to_number(pei.pei_information6) shares_acquired,
4020: hoi1.org_information1 name_of_company,
4021: hoi1.org_information4 RCB,
4022: hoi1.org_information15 company_type,
4021: hoi1.org_information4 RCB,
4022: hoi1.org_information15 company_type,
4023: fnd_number.canonical_to_number(pei2.pei_information2) market_grant_value,
4024: pei2.pei_information5 grant_type,
4025: decode(pei2.pei_information3, null, null, to_char(fnd_date.canonical_to_date(pei2.pei_information3),'YYYY')||'/'||
4026: to_char(fnd_date.canonical_to_date(pei2.pei_information3),'MM')||'/'||to_char(fnd_date.canonical_to_date(pei2.pei_information3),'DD')) grant_date,
4027: fnd_number.canonical_to_number(pei2.pei_information4) shares_granted
4028: from per_all_people_f pap,
4029: per_people_extra_info pei,
4022: hoi1.org_information15 company_type,
4023: fnd_number.canonical_to_number(pei2.pei_information2) market_grant_value,
4024: pei2.pei_information5 grant_type,
4025: decode(pei2.pei_information3, null, null, to_char(fnd_date.canonical_to_date(pei2.pei_information3),'YYYY')||'/'||
4026: to_char(fnd_date.canonical_to_date(pei2.pei_information3),'MM')||'/'||to_char(fnd_date.canonical_to_date(pei2.pei_information3),'DD')) grant_date,
4027: fnd_number.canonical_to_number(pei2.pei_information4) shares_granted
4028: from per_all_people_f pap,
4029: per_people_extra_info pei,
4030: per_people_extra_info pei2,
4043: and hoi1.org_information_context||'' = 'SG_LEGAL_ENTITY'
4044: and hoi2.org_information_context||'' = 'CLASS'
4045: and hoi2.org_information1 = 'HR_LEGAL'
4046: and hoi2.org_information2 = 'Y'
4047: and to_char(fnd_date.canonical_to_date(pei.pei_information5),'YYYY') = to_char(c_basis_end,'YYYY') /* Bug#2684645 */
4048: and (pap.effective_start_date <= c_basis_end and pap.effective_end_date >= c_basis_start);
4049: --
4050: begin
4051: v_moa_305 := 0;
4173: if (to_date(share_rec.grant_date, 'YYYY/MM/DD') between
4174: to_date('2008/02/16','YYYY/MM/DD') and
4175: to_date('2013/02/15','YYYY/MM/DD')) and
4176: (to_date(share_rec.grant_date, 'YYYY/MM/DD') between
4177: fnd_date.canonical_to_date(g_er_incorp_date_1) and
4178: fnd_date.canonical_to_date(g_er_incorp_date_2)) then
4179: null;
4180: else
4181: v_nsop_date_error := 'Y';
4174: to_date('2008/02/16','YYYY/MM/DD') and
4175: to_date('2013/02/15','YYYY/MM/DD')) and
4176: (to_date(share_rec.grant_date, 'YYYY/MM/DD') between
4177: fnd_date.canonical_to_date(g_er_incorp_date_1) and
4178: fnd_date.canonical_to_date(g_er_incorp_date_2)) then
4179: null;
4180: else
4181: v_nsop_date_error := 'Y';
4182: end if;
4395: and ass.business_group_id = c_business_group_id
4396: and ass.assignment_type = 'E' /* Bug 5033609 */
4397: and aei.information_type = 'HR_CPF_CLAIMED_SG'
4398: and aei.aei_information1 = to_char(c_basis_end,'YYYY')
4399: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information3),'YYYY'), aei.aei_information1) = aei.aei_information1
4400: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information4),'YYYY'), aei.aei_information1) = aei.aei_information1
4401: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information5),'YYYY'), aei.aei_information1) = aei.aei_information1
4402: and (ass.effective_start_date <= c_basis_end
4403: and ass.effective_end_date >= c_basis_start);
4396: and ass.assignment_type = 'E' /* Bug 5033609 */
4397: and aei.information_type = 'HR_CPF_CLAIMED_SG'
4398: and aei.aei_information1 = to_char(c_basis_end,'YYYY')
4399: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information3),'YYYY'), aei.aei_information1) = aei.aei_information1
4400: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information4),'YYYY'), aei.aei_information1) = aei.aei_information1
4401: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information5),'YYYY'), aei.aei_information1) = aei.aei_information1
4402: and (ass.effective_start_date <= c_basis_end
4403: and ass.effective_end_date >= c_basis_start);
4404: /* Bug 6020961, removed date in year check for er_cpf_date and ee_cpf_date */
4397: and aei.information_type = 'HR_CPF_CLAIMED_SG'
4398: and aei.aei_information1 = to_char(c_basis_end,'YYYY')
4399: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information3),'YYYY'), aei.aei_information1) = aei.aei_information1
4400: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information4),'YYYY'), aei.aei_information1) = aei.aei_information1
4401: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information5),'YYYY'), aei.aei_information1) = aei.aei_information1
4402: and (ass.effective_start_date <= c_basis_end
4403: and ass.effective_end_date >= c_basis_start);
4404: /* Bug 6020961, removed date in year check for er_cpf_date and ee_cpf_date */
4405:
4691: g_person_id_tab.delete;
4692: --
4693: if p_national_identifier is not null and p_report_type <> 'SG_IR8S_ADHOC_REPORT' then
4694: begin
4695: select distinct pai.action_information2 , fnd_date.canonical_to_date(pai.action_information3)
4696: bulk collect into g_person_id_tab , g_person_start_date_tab
4697: from pay_action_information pai
4698: where pai.action_information1 = p_national_identifier
4699: and pai.action_context_id = p_setup_action_id
4698: where pai.action_information1 = p_national_identifier
4699: and pai.action_context_id = p_setup_action_id
4700: and pai.action_context_type = 'AAP'
4701: and pai.action_information_category = 'SG_IRAS_SETUP'
4702: order by fnd_date.canonical_to_date(pai.action_information3) desc;
4703: end;
4704: --
4705: if g_person_id_tab.count > 1 then
4706: if g_person_id_tab(1) = p_person_id then