eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PAY_SG_IRAS_ARCHIVE dependencies on FND_DATE

Line 1163: decode(ppa.action_type,'V',fnd_date.date_to_canonical(ppa.effective_date),fnd_date.date_to_canonical(ppa.date_earned)) date_earned,

1159: c_action_sequence pay_assignment_actions.action_sequence%type )
1160: is
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

Line 1379: month_recs(l_payroll_mon_counter).month := to_number(to_char(fnd_date.canonical_to_date(month_year_action_rec.date_earned),'MM'));

1375: ----------------------------------------------------------------------------------
1376: -- Start Bug 3038605 - Store the months which have payroll runs.
1377: -- Bug: 3205321 - Store Month in MM format in month_recs
1378: ----------------------------------------------------------------------------------
1379: month_recs(l_payroll_mon_counter).month := to_number(to_char(fnd_date.canonical_to_date(month_year_action_rec.date_earned),'MM'));
1380: l_payroll_mon_counter := l_payroll_mon_counter+1;
1381: ----------------------------------------------------------------------------------
1382: -- Bulk Balance Fetch for Bug 3064282
1383: ----------------------------------------------------------------------------------

Line 1423: mtd_balance_rec(l_mtd_counter).date_earned := to_char(fnd_date.canonical_to_date(month_year_action_rec.date_earned),'MMYYYY');

1419: for counter in 1..t_month_balanceid_store.count
1420: loop
1421: mtd_balance_rec(l_mtd_counter).balance_id := t_month_balanceid_store(counter).user_entity_id;
1422: mtd_balance_rec(l_mtd_counter).balance_value := nvl(g_detailed_bal_out_tab(counter).balance_value,0);
1423: mtd_balance_rec(l_mtd_counter).date_earned := to_char(fnd_date.canonical_to_date(month_year_action_rec.date_earned),'MMYYYY');
1424: mtd_balance_rec(l_mtd_counter).date_earned_archive := month_year_action_rec.date_earned;
1425: mtd_balance_rec(l_mtd_counter).person_id := person_id_rec(l_person_counter).person_id;
1426: mtd_balance_rec(l_mtd_counter).archive_status := 'Y';
1427: l_mtd_counter := l_mtd_counter + 1;

Line 1857: select fnd_date.date_to_canonical(min(pact.date_earned)) date_from,

1853:
1854: --
1855: cursor balance_dates_rr
1856: is
1857: select fnd_date.date_to_canonical(min(pact.date_earned)) date_from,
1858: fnd_date.date_to_canonical(max(pact.date_earned)) date_to,
1859: sum(decode(pact.action_type,'V',-1,1)) no_of_times
1860: from pay_run_result_values target,
1861: pay_balance_feeds_f feed,

Line 1858: fnd_date.date_to_canonical(max(pact.date_earned)) date_to,

1854: --
1855: cursor balance_dates_rr
1856: is
1857: select fnd_date.date_to_canonical(min(pact.date_earned)) date_from,
1858: fnd_date.date_to_canonical(max(pact.date_earned)) date_to,
1859: sum(decode(pact.action_type,'V',-1,1)) no_of_times
1860: from pay_run_result_values target,
1861: pay_balance_feeds_f feed,
1862: pay_balance_types pbt,

Line 1894: select fnd_date.date_to_canonical(min(prb.effective_date)) date_from,

1890: and pact.effective_date >= trunc(bact.effective_date,'Y');
1891: --
1892: cursor balance_dates_rb
1893: is
1894: select fnd_date.date_to_canonical(min(prb.effective_date)) date_from,
1895: fnd_date.date_to_canonical(max(prb.effective_date)) date_to,
1896: sum(decode(ppa.action_type,'V',-1,1)) no_of_times
1897: from pay_run_balances prb,
1898: pay_assignment_actions ASSACT,

Line 1895: fnd_date.date_to_canonical(max(prb.effective_date)) date_to,

1891: --
1892: cursor balance_dates_rb
1893: is
1894: select fnd_date.date_to_canonical(min(prb.effective_date)) date_from,
1895: fnd_date.date_to_canonical(max(prb.effective_date)) date_to,
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,

Line 2360: if fnd_date.canonical_to_date(v_date_to) > fnd_date.canonical_to_date(v_moa_411_date) then

2356: ------------------------------------------------------------------------------
2357: end if;
2358: --
2359: if v_date_to is not null and v_moa_411_date is not null then
2360: if fnd_date.canonical_to_date(v_date_to) > fnd_date.canonical_to_date(v_moa_411_date) then
2361: v_moa_411_date := v_date_to;
2362: else
2363: v_no_of_times := v_no_of_times_411;
2364: end if;

Line 2460: if fnd_date.canonical_to_date(v_date_to) > fnd_date.canonical_to_date(v_moa_413_date) then

2456: v_date_to ,
2457: v_no_of_times);
2458: ------------------------------------------------------------------------------
2459: if v_date_to is not null and v_moa_413_date is not null then
2460: if fnd_date.canonical_to_date(v_date_to) > fnd_date.canonical_to_date(v_moa_413_date) then
2461: v_moa_413_date := v_date_to;
2462: else
2463: v_no_of_times := v_no_of_times_413;
2464: end if;

Line 2625: if to_char(fnd_date.canonical_to_date(g_er_bonus_date),'YYYY') = to_char(g_basis_end,'YYYY') then

2621:
2622: -- Bug 5078454, if moa369 balance is not zero, then store the date
2623: -- from LE to the global value g_moa_369_date if it is not blank
2624: if g_moa_369_date is not null then
2625: if to_char(fnd_date.canonical_to_date(g_er_bonus_date),'YYYY') = to_char(g_basis_end,'YYYY') then
2626: 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');
2627: end if;
2628: end if;
2629:

Line 2626: 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');

2622: -- Bug 5078454, if moa369 balance is not zero, then store the date
2623: -- from LE to the global value g_moa_369_date if it is not blank
2624: if g_moa_369_date is not null then
2625: if to_char(fnd_date.canonical_to_date(g_er_bonus_date),'YYYY') = to_char(g_basis_end,'YYYY') then
2626: 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');
2627: end if;
2628: end if;
2629:
2630: -- Added for bug 4890964, the info. based on the assignment should

Line 2736: select fnd_date.date_to_canonical(max(ppamax.effective_date))

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,
2738: pay_assignment_actions pacmax,
2739: pay_payroll_actions ppamax
2740: where ppamax.business_group_id = c_business_group_id

Line 2792: fnd_date.date_to_canonical(people.date_of_birth)

2788: c_basis_end date )
2789: is
2790: select people.national_identifier,
2791: h.meaning,
2792: fnd_date.date_to_canonical(people.date_of_birth)
2793: from per_people_f people,
2794: hr_lookups h
2795: where people.person_id = c_person_id
2796: and people.effective_start_date = (

Line 2936: fnd_date.date_to_canonical(addr.date_from),

2932: c_basis_end date) is
2933: select addr.address_line1,
2934: addr.address_line2,
2935: addr.address_line3,
2936: fnd_date.date_to_canonical(addr.date_from),
2937: fnd_date.date_to_canonical(nvl(addr.date_to,c_basis_end))/* if its not blank, return the real end date, bug 2654499 */
2938: from per_addresses addr,
2939: fnd_territories_tl a
2940: where addr.person_id (+) = c_person_id

Line 2937: fnd_date.date_to_canonical(nvl(addr.date_to,c_basis_end))/* if its not blank, return the real end date, bug 2654499 */

2933: select addr.address_line1,
2934: addr.address_line2,
2935: addr.address_line3,
2936: fnd_date.date_to_canonical(addr.date_from),
2937: fnd_date.date_to_canonical(nvl(addr.date_to,c_basis_end))/* if its not blank, return the real end date, bug 2654499 */
2938: from per_addresses addr,
2939: fnd_territories_tl a
2940: where addr.person_id (+) = c_person_id
2941: and a.territory_code (+)= addr.country

Line 2993: select fnd_date.date_to_canonical(min(service.date_start))

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:

Line 3003: select fnd_date.date_to_canonical(service.actual_termination_date)

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,
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

Line 3072: 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

3068: is
3069: select people.per_information1,
3070: people.per_information3,
3071: people.per_information6,
3072: 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
3073: -- fnd_date.canonical_to_date(people.per_information9), -- Bug 5435088
3074: people.per_information12,
3075: people.per_information23
3076: from per_people_f people

Line 3073: -- fnd_date.canonical_to_date(people.per_information9), -- Bug 5435088

3069: select people.per_information1,
3070: people.per_information3,
3071: people.per_information6,
3072: 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
3073: -- fnd_date.canonical_to_date(people.per_information9), -- Bug 5435088
3074: people.per_information12,
3075: people.per_information23
3076: from per_people_f people
3077: where people.person_id = c_person_id

Line 3180: 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

3176: indicators.pei_information2,
3177: indicators.pei_information3,
3178: indicators.pei_information4, -- Exempt Remission
3179: indicators.pei_information5, -- Approval from IRAS
3180: 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
3181: details.pei_information3,
3182: details.pei_information4,
3183: details.pei_information6, -- Name of bank
3184: info.pei_information1

Line 3343: to_char(fnd_date.canonical_to_date(aei.aei_information1),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(aei.aei_information1),'MM')||'/'||

3339: c_basis_start date,
3340: c_basis_end date )
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

Line 3344: to_char(fnd_date.canonical_to_date(aei.aei_information1),'DD')

3340: c_basis_end date )
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

Line 3356: and to_char(fnd_date.canonical_to_date(aei.aei_information1),'YYYY') = to_char(c_basis_end,'YYYY');

3352: and hsc.segment1 = c_legal_entity_id
3353: and aei.information_type = 'HR_NON_CONT_BONUS_INFO_SG'
3354: and aei.aei_information1 is not NULL
3355: and assign.assignment_type = 'E'
3356: and to_char(fnd_date.canonical_to_date(aei.aei_information1),'YYYY') = to_char(c_basis_end,'YYYY');
3357:
3358: --
3359: begin
3360:

Line 3796: min(fnd_date.date_to_canonical(assign.effective_start_date)),

3792: c_basis_start date,
3793: c_basis_end date )
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

Line 3797: max(fnd_date.date_to_canonical(nvl(assign.effective_end_date,c_basis_end)))

3793: c_basis_end date )
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

Line 3872: to_char(fnd_date.canonical_to_date(pei.pei_information5),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(pei.pei_information5),'MM')||'/'||

3868: select distinct pei.person_extra_info_id,
3869: pei.pei_information1 stock_option,
3870: pei.pei_information3 exercise_price,
3871: pei.pei_information4 market_exercise_value,
3872: to_char(fnd_date.canonical_to_date(pei.pei_information5),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(pei.pei_information5),'MM')||'/'||
3873: to_char(fnd_date.canonical_to_date(pei.pei_information5),'DD') exercise_date,
3874: pei.pei_information6 shares_acquired,
3875: hoi1.org_information1 name_of_company,
3876: hoi1.org_information4 RCB,

Line 3873: to_char(fnd_date.canonical_to_date(pei.pei_information5),'DD') exercise_date,

3869: pei.pei_information1 stock_option,
3870: pei.pei_information3 exercise_price,
3871: pei.pei_information4 market_exercise_value,
3872: to_char(fnd_date.canonical_to_date(pei.pei_information5),'YYYY')||'/'||to_char(fnd_date.canonical_to_date(pei.pei_information5),'MM')||'/'||
3873: to_char(fnd_date.canonical_to_date(pei.pei_information5),'DD') exercise_date,
3874: pei.pei_information6 shares_acquired,
3875: hoi1.org_information1 name_of_company,
3876: hoi1.org_information4 RCB,
3877: hoi1.org_information15 company_type,

Line 3880: decode(pei2.pei_information3, null, null, to_char(fnd_date.canonical_to_date(pei2.pei_information3),'YYYY')||'/'||

3876: hoi1.org_information4 RCB,
3877: hoi1.org_information15 company_type,
3878: pei2.pei_information2 market_grant_value,
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,

Line 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,

3877: hoi1.org_information15 company_type,
3878: pei2.pei_information2 market_grant_value,
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,

Line 3902: and to_char(fnd_date.canonical_to_date(pei.pei_information5),'YYYY') = to_char(c_basis_end,'YYYY') /* Bug#2684645 */

3898: and hoi1.org_information_context||'' = 'SG_LEGAL_ENTITY'
3899: and hoi2.org_information_context||'' = 'CLASS'
3900: and hoi2.org_information1 = 'HR_LEGAL'
3901: and hoi2.org_information2 = 'Y'
3902: and to_char(fnd_date.canonical_to_date(pei.pei_information5),'YYYY') = to_char(c_basis_end,'YYYY') /* Bug#2684645 */
3903: and (pap.effective_start_date <= c_basis_end and pap.effective_end_date >= c_basis_start);
3904: --
3905: begin
3906: v_moa_305 := 0;

Line 4139: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information3),'YYYY'), aei.aei_information1) = aei.aei_information1

4135: and ass.business_group_id = c_business_group_id
4136: and ass.assignment_type = 'E' /* Bug 5033609 */
4137: and aei.information_type = 'HR_CPF_CLAIMED_SG'
4138: and aei.aei_information1 = to_char(c_basis_end,'YYYY')
4139: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information3),'YYYY'), aei.aei_information1) = aei.aei_information1
4140: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information4),'YYYY'), aei.aei_information1) = aei.aei_information1
4141: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information5),'YYYY'), aei.aei_information1) = aei.aei_information1
4142: and (ass.effective_start_date <= c_basis_end
4143: and ass.effective_end_date >= c_basis_start);

Line 4140: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information4),'YYYY'), aei.aei_information1) = aei.aei_information1

4136: and ass.assignment_type = 'E' /* Bug 5033609 */
4137: and aei.information_type = 'HR_CPF_CLAIMED_SG'
4138: and aei.aei_information1 = to_char(c_basis_end,'YYYY')
4139: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information3),'YYYY'), aei.aei_information1) = aei.aei_information1
4140: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information4),'YYYY'), aei.aei_information1) = aei.aei_information1
4141: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information5),'YYYY'), aei.aei_information1) = aei.aei_information1
4142: and (ass.effective_start_date <= c_basis_end
4143: and ass.effective_end_date >= c_basis_start);
4144: /* Bug 6020961, removed date in year check for er_cpf_date and ee_cpf_date */

Line 4141: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information5),'YYYY'), aei.aei_information1) = aei.aei_information1

4137: and aei.information_type = 'HR_CPF_CLAIMED_SG'
4138: and aei.aei_information1 = to_char(c_basis_end,'YYYY')
4139: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information3),'YYYY'), aei.aei_information1) = aei.aei_information1
4140: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information4),'YYYY'), aei.aei_information1) = aei.aei_information1
4141: and nvl(to_char(fnd_date.canonical_to_date(aei.aei_information5),'YYYY'), aei.aei_information1) = aei.aei_information1
4142: and (ass.effective_start_date <= c_basis_end
4143: and ass.effective_end_date >= c_basis_start);
4144: /* Bug 6020961, removed date in year check for er_cpf_date and ee_cpf_date */
4145:

Line 4435: select distinct pai.action_information2 , fnd_date.canonical_to_date(pai.action_information3)

4431: g_person_id_tab.delete;
4432: --
4433: if p_national_identifier is not null and p_report_type <> 'SG_IR8S_ADHOC_REPORT' then
4434: begin
4435: select distinct pai.action_information2 , fnd_date.canonical_to_date(pai.action_information3)
4436: bulk collect into g_person_id_tab , g_person_start_date_tab
4437: from pay_action_information pai
4438: where pai.action_information1 = p_national_identifier
4439: and pai.action_context_id = p_setup_action_id

Line 4442: order by fnd_date.canonical_to_date(pai.action_information3) desc;

4438: where pai.action_information1 = p_national_identifier
4439: and pai.action_context_id = p_setup_action_id
4440: and pai.action_context_type = 'AAP'
4441: and pai.action_information_category = 'SG_IRAS_SETUP'
4442: order by fnd_date.canonical_to_date(pai.action_information3) desc;
4443: end;
4444: --
4445: if g_person_id_tab.count > 1 then
4446: if g_person_id_tab(1) = p_person_id then