[Home] [Help]
1000: c_action_sequence pay_assignment_actions.action_sequence%type )
1001: is
1002: select /*+ ORDERED USE_NL(pac) */
1003: pac.assignment_action_id assact_id,
1004: decode(ppa.action_type,'V',fnd_date.date_to_canonical(ppa.effective_date),fnd_date.date_to_canonical(ppa.date_earned)) date_earned,
1005: pac.tax_unit_id tax_uid
1006: from per_assignments_f paa,
1007: pay_assignment_actions pac,
1008: pay_payroll_actions ppa
1237: ----------------------------------------------------------------------------------
1238: -- Start Bug 3038605 - Store the months which have payroll runs.
1239: -- Bug: 3205321 - Store Month in MM format in month_recs
1240: ----------------------------------------------------------------------------------
1241: month_recs(l_payroll_mon_counter).month := to_number(to_char(fnd_date.canonical_to_date(month_year_action_rec.date_earned),'MM'));
1242: l_payroll_mon_counter := l_payroll_mon_counter+1;
1243: ----------------------------------------------------------------------------------
1244: -- Bulk Balance Fetch for Bug 3064282
1245: ----------------------------------------------------------------------------------
1283: for counter in 1..t_month_balanceid_store.count
1284: loop
1285: mtd_balance_rec(l_mtd_counter).balance_id := t_month_balanceid_store(counter).user_entity_id;
1286: mtd_balance_rec(l_mtd_counter).balance_value := nvl(g_detailed_bal_out_tab(counter).balance_value,0);
1287: mtd_balance_rec(l_mtd_counter).date_earned := to_char(fnd_date.canonical_to_date(month_year_action_rec.date_earned),'MMYYYY');
1288: mtd_balance_rec(l_mtd_counter).date_earned_archive := month_year_action_rec.date_earned;
1289: mtd_balance_rec(l_mtd_counter).person_id := person_id_rec(l_person_counter).person_id;
1290: mtd_balance_rec(l_mtd_counter).archive_status := 'Y';
1291: l_mtd_counter := l_mtd_counter + 1;
1569: AND con1.sequence_no =2)
1570: AND EXISTS (SELECT 1
1571: FROM ff_archive_item_contexts con2
1572: WHERE con2.archive_item_id = arch.archive_item_id
1573: AND fnd_date.canonical_to_date(con2.context) = fnd_date.canonical_to_date(t_date_earned(i))
1574: AND con2.sequence_no =3);
1575: EXCEPTION
1576: WHEN NO_DATA_FOUND
1577: THEN NULL;
2302: select distinct pei.person_extra_info_id,
2303: pei.pei_information1 stock_option,
2304: pei.pei_information3 exercise_price,
2305: pei.pei_information4 market_exercise_value,
2306: to_char(fnd_date.canonical_to_date(pei.pei_information5),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(pei.pei_information5),'MM')||'/'||
2307: to_char(fnd_date.canonical_to_date(pei.pei_information5),'DD') exercise_date,
2308: pei.pei_information6 shares_acquired,
2309: hoi1.org_information1 name_of_company,
2310: hoi1.org_information4 RCB,
2303: pei.pei_information1 stock_option,
2304: pei.pei_information3 exercise_price,
2305: pei.pei_information4 market_exercise_value,
2306: to_char(fnd_date.canonical_to_date(pei.pei_information5),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(pei.pei_information5),'MM')||'/'||
2307: to_char(fnd_date.canonical_to_date(pei.pei_information5),'DD') exercise_date,
2308: pei.pei_information6 shares_acquired,
2309: hoi1.org_information1 name_of_company,
2310: hoi1.org_information4 RCB,
2311: hoi1.org_information15 company_type,
2310: hoi1.org_information4 RCB,
2311: hoi1.org_information15 company_type,
2312: pei2.pei_information2 market_grant_value,
2313: pei2.pei_information5 grant_type,
2314: decode(pei2.pei_information3, null, null, to_char(fnd_date.canonical_to_date(pei2.pei_information3),'YYYY')||'/'||
2315: to_char(fnd_date.canonical_to_date(pei2.pei_information3),'MM')||'/'||to_char(fnd_date.canonical_to_date(pei2.pei_information3),'DD')) grant_date,
2316: pei2.pei_information4 shares_granted
2317: from per_all_people_f pap,
2318: per_people_extra_info pei,
2311: hoi1.org_information15 company_type,
2312: pei2.pei_information2 market_grant_value,
2313: pei2.pei_information5 grant_type,
2314: decode(pei2.pei_information3, null, null, to_char(fnd_date.canonical_to_date(pei2.pei_information3),'YYYY')||'/'||
2315: to_char(fnd_date.canonical_to_date(pei2.pei_information3),'MM')||'/'||to_char(fnd_date.canonical_to_date(pei2.pei_information3),'DD')) grant_date,
2316: pei2.pei_information4 shares_granted
2317: from per_all_people_f pap,
2318: per_people_extra_info pei,
2319: per_people_extra_info pei2,
2332: and hoi1.org_information_context||'' = 'SG_LEGAL_ENTITY'
2333: and hoi2.org_information_context||'' = 'CLASS'
2334: and hoi2.org_information1 = 'HR_LEGAL'
2335: and hoi2.org_information2 = 'Y'
2336: and to_char(fnd_date.canonical_to_date(pei.pei_information5),'YYYY') = to_char(c_basis_end,'YYYY') /* Bug#2684645 */
2337: and (pap.effective_start_date <= c_basis_end and pap.effective_end_date >= c_basis_start);
2338: --
2339:
2340: cursor shares_removed
2425: and ppa.report_type='SG_IRAS_ARCHIVE'
2426: and paa.assignment_id = l_assignment_id);
2427:
2428: if v_er_incorp_date is not null then
2429: v_er_incorp_date_1 := to_char(fnd_date.canonical_to_date(v_er_incorp_date),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(v_er_incorp_date),'MM')||'/'||to_char(fnd_date.canonical_to_date(v_er_incorp_date),'DD');
2430: v_er_incorp_date_2 := to_char(to_number(to_char(fnd_date.canonical_to_date(v_er_incorp_date),'YYYY'))+3)||'/'||to_char(fnd_date.canonical_to_date(v_er_incorp_date),'MM')||'/'||to_char(fnd_date.canonical_to_date(v_er_incorp_date),'DD');
2431: end if;
2432:
2433: select ue.user_entity_id, ue.user_entity_name
2426: and paa.assignment_id = l_assignment_id);
2427:
2428: if v_er_incorp_date is not null then
2429: v_er_incorp_date_1 := to_char(fnd_date.canonical_to_date(v_er_incorp_date),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(v_er_incorp_date),'MM')||'/'||to_char(fnd_date.canonical_to_date(v_er_incorp_date),'DD');
2430: v_er_incorp_date_2 := to_char(to_number(to_char(fnd_date.canonical_to_date(v_er_incorp_date),'YYYY'))+3)||'/'||to_char(fnd_date.canonical_to_date(v_er_incorp_date),'MM')||'/'||to_char(fnd_date.canonical_to_date(v_er_incorp_date),'DD');
2431: end if;
2432:
2433: select ue.user_entity_id, ue.user_entity_name
2434: bulk collect into t_user_entity_id_a8b, t_user_entity_name_a8b
2693: if (to_date(t_value_shares(counter).grant_date,'YYYY/MM/DD') between
2694: to_date('2008/02/16','YYYY/MM/DD') and
2695: to_date('2013/02/15','YYYY/MM/DD')) and
2696: (to_date(t_value_shares(counter).grant_date, 'YYYY/MM/DD') between
2697: fnd_date.canonical_to_date(v_er_incorp_date_1) and
2698: fnd_date.canonical_to_date(v_er_incorp_date_2)) then
2699: null;
2700: else
2701: v_nsop_date_error := 'Y';
2694: to_date('2008/02/16','YYYY/MM/DD') and
2695: to_date('2013/02/15','YYYY/MM/DD')) and
2696: (to_date(t_value_shares(counter).grant_date, 'YYYY/MM/DD') between
2697: fnd_date.canonical_to_date(v_er_incorp_date_1) and
2698: fnd_date.canonical_to_date(v_er_incorp_date_2)) then
2699: null;
2700: else
2701: v_nsop_date_error := 'Y';
2702: end if;
2911: and ass.business_group_id = c_business_group_id
2912: and ass.assignment_type = 'E' /* Bug 5033609 */
2913: and aei.information_type = 'HR_CPF_CLAIMED_SG'
2914: and aei.aei_information1 = to_char(c_basis_end,'YYYY')
2915: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information3),'YYYY'), aei.aei_information1) = aei.aei_information1
2916: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information4),'YYYY'), aei.aei_information1) = aei.aei_information1
2917: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information5),'YYYY'), aei.aei_information1) = aei.aei_information1
2918: and (ass.effective_start_date <= c_basis_end
2919: and ass.effective_end_date >= c_basis_start);
2912: and ass.assignment_type = 'E' /* Bug 5033609 */
2913: and aei.information_type = 'HR_CPF_CLAIMED_SG'
2914: and aei.aei_information1 = to_char(c_basis_end,'YYYY')
2915: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information3),'YYYY'), aei.aei_information1) = aei.aei_information1
2916: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information4),'YYYY'), aei.aei_information1) = aei.aei_information1
2917: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information5),'YYYY'), aei.aei_information1) = aei.aei_information1
2918: and (ass.effective_start_date <= c_basis_end
2919: and ass.effective_end_date >= c_basis_start);
2920:
2913: and aei.information_type = 'HR_CPF_CLAIMED_SG'
2914: and aei.aei_information1 = to_char(c_basis_end,'YYYY')
2915: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information3),'YYYY'), aei.aei_information1) = aei.aei_information1
2916: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information4),'YYYY'), aei.aei_information1) = aei.aei_information1
2917: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information5),'YYYY'), aei.aei_information1) = aei.aei_information1
2918: and (ass.effective_start_date <= c_basis_end
2919: and ass.effective_end_date >= c_basis_start);
2920:
2921: cursor ir8s_c_removed
3448: g_person_id_tab.delete;
3449: --
3450: if p_national_identifier is not null and p_report_type <> 'SG_IR8S_ADHOC_REPORT' then
3451: begin
3452: select distinct pai.action_information2 , fnd_date.canonical_to_date(pai.action_information3)
3453: bulk collect into g_person_id_tab , g_person_start_date_tab
3454: from pay_action_information pai
3455: where pai.action_information1 = p_national_identifier
3456: and pai.action_context_id = p_setup_action_id
3455: where pai.action_information1 = p_national_identifier
3456: and pai.action_context_id = p_setup_action_id
3457: and pai.action_context_type = 'AAP'
3458: and pai.action_information_category = 'SG_IRAS_AMEND_SETUP'
3459: order by fnd_date.canonical_to_date(pai.action_information3) desc;
3460: end;
3461: --
3462: if g_person_id_tab.count > 1 then
3463: if g_person_id_tab(1) = p_person_id then