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_NL_WAGE_REPORT_PKG dependencies on FND_DATE

Line 1770: SELECT fnd_date.canonical_to_date(org_information1) start_date

1766: ,p_collXMLTable OUT NOCOPY tXMLTable) IS
1767: --
1768: CURSOR csr_get_org_tax_data(c_org_id NUMBER
1769: ,c_payroll_type VARCHAR2) IS
1770: SELECT fnd_date.canonical_to_date(org_information1) start_date
1771: ,fnd_date.canonical_to_date(org_information2) end_date
1772: ,org_information3
1773: ,fnd_number.canonical_to_number(org_information4) amount
1774: ,org_information5 frequency

Line 1771: ,fnd_date.canonical_to_date(org_information2) end_date

1767: --
1768: CURSOR csr_get_org_tax_data(c_org_id NUMBER
1769: ,c_payroll_type VARCHAR2) IS
1770: SELECT fnd_date.canonical_to_date(org_information1) start_date
1771: ,fnd_date.canonical_to_date(org_information2) end_date
1772: ,org_information3
1773: ,fnd_number.canonical_to_number(org_information4) amount
1774: ,org_information5 frequency
1775: ,org_information6

Line 1781: ORDER BY fnd_date.canonical_to_date(org_information1);

1777: WHERE organization_id = c_org_id
1778: AND org_information_context = 'NL_ORG_FLAT_RATE_TAXATION'
1779: -- AND org_information3 = '1' -- Bug# 5754707
1780: AND org_information6 = c_payroll_type
1781: ORDER BY fnd_date.canonical_to_date(org_information1);
1782: --
1783: CURSOR csr_get_no_pay_period(c_payroll_type VARCHAR2) IS
1784: SELECT number_per_fiscal_year
1785: FROM per_time_period_types

Line 1810: IF csr_rec.org_information3 = '1' AND p_effective_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN

1806: --
1807: l_flat_rate_taxation := 0;
1808: -- Bug# 5754707, For Loop modified for all types of Look-up codes.
1809: FOR csr_rec in csr_get_org_tax_data(p_tax_unit_id,p_payroll_type) LOOP
1810: IF csr_rec.org_information3 = '1' AND p_effective_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
1811: IF csr_rec.frequency = 'P' THEN
1812: l_flat_rate_taxation := csr_rec.amount;
1813: ELSIF csr_rec.frequency = 'A' THEN
1814: l_flat_rate_taxation := csr_rec.amount/l_no_pay_period;

Line 1818: IF csr_rec.org_information3 = '2' AND p_effective_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN

1814: l_flat_rate_taxation := csr_rec.amount/l_no_pay_period;
1815: END IF;
1816: END IF;
1817:
1818: IF csr_rec.org_information3 = '2' AND p_effective_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
1819: IF csr_rec.frequency = 'P' THEN
1820: l_major_issue_flat_rate := csr_rec.amount;
1821: ELSIF csr_rec.frequency = 'A' THEN
1822: l_major_issue_flat_rate := csr_rec.amount/l_no_pay_period;

Line 1826: IF csr_rec.org_information3 = '3' AND p_effective_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN

1822: l_major_issue_flat_rate := csr_rec.amount/l_no_pay_period;
1823: END IF;
1824: END IF;
1825:
1826: IF csr_rec.org_information3 = '3' AND p_effective_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
1827: IF csr_rec.frequency = 'P' THEN
1828: l_single_rate_exp_pay := csr_rec.amount;
1829: ELSIF csr_rec.frequency = 'A' THEN
1830: l_single_rate_exp_pay := csr_rec.amount/l_no_pay_period;

Line 1834: IF csr_rec.org_information3 = '4' AND p_effective_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN

1830: l_single_rate_exp_pay := csr_rec.amount/l_no_pay_period;
1831: END IF;
1832: END IF;
1833:
1834: IF csr_rec.org_information3 = '4' AND p_effective_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
1835: IF csr_rec.frequency = 'P' THEN
1836: l_wage_tax_subsidy := csr_rec.amount;
1837: ELSIF csr_rec.frequency = 'A' THEN
1838: l_wage_tax_subsidy := csr_rec.amount/l_no_pay_period;

Line 1843: IF csr_rec.org_information3 = '5' AND p_effective_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN

1839: END IF;
1840: END IF;
1841:
1842: /**** Bug 6610259 ****/
1843: IF csr_rec.org_information3 = '5' AND p_effective_date BETWEEN csr_rec.start_date AND NVL(csr_rec.end_date,fnd_date.canonical_to_date('4712/12/31')) THEN
1844: IF csr_rec.frequency = 'P' THEN
1845: l_flat_tax_holidays := csr_rec.amount;
1846: ELSIF csr_rec.frequency = 'A' THEN
1847: l_flat_tax_holidays := csr_rec.amount/l_no_pay_period;

Line 2506: select fnd_date.canonical_to_date(org_information5)

2502: AND hou.organization_id = hoi.organization_id
2503: AND hou.organization_id = c_employer_id;
2504: --
2505: CURSOR csr_retro_date(c_business_group_id NUMBER) IS
2506: select fnd_date.canonical_to_date(org_information5)
2507: from hr_organization_information
2508: where organization_id = c_business_group_id
2509: AND org_information_context = 'NL_BG_INFO';
2510: --

Line 2725: , p_action_information3 => fnd_date.date_to_canonical(sysdate)

2721: , p_tax_unit_id => p_tax_unit_id
2722: , p_action_information_category => 'NL_WR_EMPLOYER_INFO'
2723: , p_action_information1 => p_tax_unit_id
2724: , p_action_information2 => substr(l_empr_contact.sender_id||l_tax_details.tax_reg_num,1,32)
2725: , p_action_information3 => fnd_date.date_to_canonical(sysdate)
2726: , p_action_information4 => substr(l_empr_contact.contact_name,1,35)
2727: , p_action_information5 => substr(l_seq_no,1,6)
2728: , p_action_information6 => substr(l_empr_contact.contact_num,1,14)
2729: , p_action_information7 => 'SWO00361ORACLE' --'BEL00361ORACLE' --Bug#: 7338209

Line 2732: , p_action_information10 => fnd_date.date_to_canonical(GREATEST(p_start_date,trunc(p_end_date,'Y')))

2728: , p_action_information6 => substr(l_empr_contact.contact_num,1,14)
2729: , p_action_information7 => 'SWO00361ORACLE' --'BEL00361ORACLE' --Bug#: 7338209
2730: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12)
2731: , p_action_information9 => substr(l_tax_details.tax_rep_name,1,200)
2732: , p_action_information10 => fnd_date.date_to_canonical(GREATEST(p_start_date,trunc(p_end_date,'Y')))
2733: , p_action_information11 => fnd_date.date_to_canonical(p_end_date)
2734: , p_action_information12 => g_contract_code_mapping);
2735: --Fnd_file.put_line(FND_FILE.LOG,' Archived NL_WR_EMPLOYER_INFO deinit code ');
2736: --

Line 2733: , p_action_information11 => fnd_date.date_to_canonical(p_end_date)

2729: , p_action_information7 => 'SWO00361ORACLE' --'BEL00361ORACLE' --Bug#: 7338209
2730: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12)
2731: , p_action_information9 => substr(l_tax_details.tax_rep_name,1,200)
2732: , p_action_information10 => fnd_date.date_to_canonical(GREATEST(p_start_date,trunc(p_end_date,'Y')))
2733: , p_action_information11 => fnd_date.date_to_canonical(p_end_date)
2734: , p_action_information12 => g_contract_code_mapping);
2735: --Fnd_file.put_line(FND_FILE.LOG,' Archived NL_WR_EMPLOYER_INFO deinit code ');
2736: --
2737:

Line 2822: AND c_effective_date BETWEEN fnd_date.canonical_to_date(hoi2.org_information1)

2818: AND hr_nl_org_info.Get_Tax_Org_Id(posv.ORG_STRUCTURE_VERSION_ID ,hoi2.organization_id) = c_employer_id
2819: AND posv.ORGANIZATION_STRUCTURE_ID = TO_NUMBER(hoi3.org_information1)
2820: AND c_effective_date BETWEEN posv.date_from
2821: AND nvl(posv.date_to,to_date('31-12-4712','dd-mm-yyyy'))
2822: AND c_effective_date BETWEEN fnd_date.canonical_to_date(hoi2.org_information1)
2823: AND nvl(fnd_date.canonical_to_date(hoi2.org_information2),to_date('31-12-4712','dd-mm-yyyy'))
2824: AND EXISTS (SELECT 1
2825: FROM hr_organization_information hoi4
2826: WHERE hoi4.organization_id = hoi1.organization_id

Line 2823: AND nvl(fnd_date.canonical_to_date(hoi2.org_information2),to_date('31-12-4712','dd-mm-yyyy'))

2819: AND posv.ORGANIZATION_STRUCTURE_ID = TO_NUMBER(hoi3.org_information1)
2820: AND c_effective_date BETWEEN posv.date_from
2821: AND nvl(posv.date_to,to_date('31-12-4712','dd-mm-yyyy'))
2822: AND c_effective_date BETWEEN fnd_date.canonical_to_date(hoi2.org_information1)
2823: AND nvl(fnd_date.canonical_to_date(hoi2.org_information2),to_date('31-12-4712','dd-mm-yyyy'))
2824: AND EXISTS (SELECT 1
2825: FROM hr_organization_information hoi4
2826: WHERE hoi4.organization_id = hoi1.organization_id
2827: AND hoi4.org_information_context = 'NL_SIT'

Line 2833: SELECT DISTINCT fnd_date.canonical_to_date(pai.Action_Information2) Start_date

2829: ORDER BY 1,2;
2830: --
2831: -- Correction balance total periods
2832: CURSOR csr_ret_cor_period(c_pact_id NUMBER) IS
2833: SELECT DISTINCT fnd_date.canonical_to_date(pai.Action_Information2) Start_date
2834: ,fnd_date.canonical_to_date(pai.action_information3) End_date
2835: FROM pay_assignment_actions paa
2836: ,pay_action_information pai
2837: WHERE paa.payroll_action_id = c_pact_id

Line 2834: ,fnd_date.canonical_to_date(pai.action_information3) End_date

2830: --
2831: -- Correction balance total periods
2832: CURSOR csr_ret_cor_period(c_pact_id NUMBER) IS
2833: SELECT DISTINCT fnd_date.canonical_to_date(pai.Action_Information2) Start_date
2834: ,fnd_date.canonical_to_date(pai.action_information3) End_date
2835: FROM pay_assignment_actions paa
2836: ,pay_action_information pai
2837: WHERE paa.payroll_action_id = c_pact_id
2838: AND paa.assignment_action_id = pai.action_context_id

Line 2887: ,fnd_date.date_to_displaydate(fnd_date.canonical_to_date(pai_p.action_information5)) Dt

2883: --
2884: --#
2885: CURSOR csr_get_PA_exception_info(p_payroll_action_id IN NUMBER) IS
2886: SELECT pai_p.action_information4 Message
2887: ,fnd_date.date_to_displaydate(fnd_date.canonical_to_date(pai_p.action_information5)) Dt
2888: ,pai_p.action_information6 Description
2889: ,substr(pai_p.action_information7,1,30) E_Name
2890: ,substr(pai_p.action_information8,1,30) E_Number
2891: ,pai_p.action_context_type cxt

Line 2900: ,fnd_date.date_to_displaydate(fnd_date.canonical_to_date(pai_p.action_information5)) Dt

2896: ORDER BY pai_p.action_information8 asc;
2897: --
2898: CURSOR csr_get_AAP_exception_info(p_payroll_action_id IN NUMBER) IS
2899: SELECT pai_p.action_information4 Message
2900: ,fnd_date.date_to_displaydate(fnd_date.canonical_to_date(pai_p.action_information5)) Dt
2901: ,pai_p.action_information6 Description
2902: ,substr(pai_p.action_information7,1,30) E_Name
2903: ,substr(pai_p.action_information8,1,30) E_Number
2904: ,pai_p.action_context_type cxt

Line 3121: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)

3117: , p_source_text => NULL
3118: , p_tax_unit_id => l_tax_unit_id
3119: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
3120: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_SENDER_ID')
3121: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
3122: , p_action_information6 => 'Sender ID is null'
3123: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
3124: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
3125: END IF;

Line 3141: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)

3137: , p_source_text => NULL
3138: , p_tax_unit_id => l_tax_unit_id
3139: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
3140: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_PERSON_NM')
3141: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
3142: , p_action_information6 => 'Contact Person is null'
3143: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
3144: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
3145: END IF;

Line 3161: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)

3157: , p_source_text => NULL
3158: , p_tax_unit_id => l_tax_unit_id
3159: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
3160: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_CONTACT_NO')
3161: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
3162: , p_action_information6 => 'Contact Number is null'
3163: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
3164: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
3165: END IF;

Line 3181: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)

3177: , p_source_text => NULL
3178: , p_tax_unit_id => l_tax_unit_id
3179: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
3180: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_TAX_NO')
3181: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
3182: , p_action_information6 => 'Tax Registration Number is null'
3183: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
3184: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
3185: END IF;

Line 3202: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)

3198: , p_source_text => NULL
3199: , p_tax_unit_id => l_tax_unit_id
3200: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
3201: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_TAX_FORMAT')
3202: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
3203: , p_action_information6 => 'Tax Registration Number is not in the format 111111111L11'
3204: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
3205: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
3206: END IF;

Line 3222: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)

3218: , p_source_text => NULL
3219: , p_tax_unit_id => l_tax_unit_id
3220: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
3221: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_TAX_NAME')
3222: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
3223: , p_action_information6 => 'Tax Reporting Name is null'
3224: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
3225: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
3226: END IF;

Line 3244: , p_action_information3 => fnd_date.date_to_canonical(sysdate)

3240: , p_tax_unit_id => l_tax_unit_id
3241: , p_action_information_category => 'NL_WR_EMPLOYER_INFO'
3242: , p_action_information1 => l_tax_unit_id
3243: , p_action_information2 => substr(l_empr_contact.sender_id||l_tax_details.tax_reg_num,1,32)
3244: , p_action_information3 => fnd_date.date_to_canonical(sysdate)
3245: , p_action_information4 => substr(l_empr_contact.contact_name,1,35)
3246: , p_action_information5 => substr(l_seq_no,1,6)
3247: , p_action_information6 => substr(l_empr_contact.contact_num,1,14)
3248: , p_action_information7 => 'SWO00361ORACLE' --'BEL00361ORACLE' --Bug#: 7338209

Line 3251: , p_action_information10 => fnd_date.date_to_canonical(l_start_date)

3247: , p_action_information6 => substr(l_empr_contact.contact_num,1,14)
3248: , p_action_information7 => 'SWO00361ORACLE' --'BEL00361ORACLE' --Bug#: 7338209
3249: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12)
3250: , p_action_information9 => substr(l_tax_details.tax_rep_name,1,200)
3251: , p_action_information10 => fnd_date.date_to_canonical(l_start_date)
3252: , p_action_information11 => fnd_date.date_to_canonical(l_end_date)
3253: , p_action_information12 => g_contract_code_mapping);
3254: --Fnd_file.put_line(FND_FILE.LOG,' Archived NL_WR_EMPLOYER_INFO deinit code ');
3255: --

Line 3252: , p_action_information11 => fnd_date.date_to_canonical(l_end_date)

3248: , p_action_information7 => 'SWO00361ORACLE' --'BEL00361ORACLE' --Bug#: 7338209
3249: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12)
3250: , p_action_information9 => substr(l_tax_details.tax_rep_name,1,200)
3251: , p_action_information10 => fnd_date.date_to_canonical(l_start_date)
3252: , p_action_information11 => fnd_date.date_to_canonical(l_end_date)
3253: , p_action_information12 => g_contract_code_mapping);
3254: --Fnd_file.put_line(FND_FILE.LOG,' Archived NL_WR_EMPLOYER_INFO deinit code ');
3255: --
3256: FOR l_period_dates_rec IN csr_period_dates(p_actid) LOOP

Line 3258: l_date := fnd_date.canonical_to_date(l_period_dates_rec.End_date);

3254: --Fnd_file.put_line(FND_FILE.LOG,' Archived NL_WR_EMPLOYER_INFO deinit code ');
3255: --
3256: FOR l_period_dates_rec IN csr_period_dates(p_actid) LOOP
3257: --Fnd_file.put_line(FND_FILE.LOG,' In csr_period_dates '||l_period_dates_rec.End_date);
3258: l_date := fnd_date.canonical_to_date(l_period_dates_rec.End_date);
3259: IF l_date >= l_start_date AND l_date <= l_end_date THEN -- COMPLETE RECORD
3260: --Fnd_file.put_line(FND_FILE.LOG,' If Current Period -- COMPLETE ');
3261: --Fnd_file.put_line(FND_FILE.LOG,' Calling populate_coll_bal_table for COMPLETE ');
3262: populate_coll_bal_table(p_actid => p_actid

Line 3409: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)

3405: , p_source_text => NULL
3406: , p_tax_unit_id => l_tax_unit_id
3407: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
3408: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_SECTOR')
3409: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
3410: , p_action_information6 => 'Mandatory check on Sector'
3411: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
3412: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
3413: --

Line 3427: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)

3423: , p_source_text => NULL
3424: , p_tax_unit_id => l_tax_unit_id
3425: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
3426: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_RISK_GROUP')
3427: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
3428: , p_action_information6 => 'No Risk Group'
3429: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
3430: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
3431: END IF;

Line 3447: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)

3443: , p_source_text => NULL
3444: , p_tax_unit_id => l_tax_unit_id
3445: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
3446: , p_action_information4 => fnd_message.get_string('PER','HR_NL_ZERO_BASE_WEWE') -- Message Code should have been '%_WEWA'
3447: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
3448: , p_action_information6 => 'Waiting Money Fund contribution base is zero'
3449: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
3450: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
3451: END IF;

Line 3467: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)

3463: , p_source_text => NULL
3464: , p_tax_unit_id => l_tax_unit_id
3465: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
3466: , p_action_information4 => fnd_message.get_string('PER','HR_NL_ZERO_AWF')
3467: , p_action_information5 => fnd_date.date_to_canonical(l_end_date)
3468: , p_action_information6 => 'AWF contribution base is zero'
3469: , p_action_information7 => substr(l_tax_details.tax_rep_name,1,200)
3470: , p_action_information8 => substr(l_tax_details.tax_reg_num,1,12));
3471: END IF;

Line 3480: --Fnd_file.put_line(FND_FILE.LOG,'Prev period End Date -- CORR_BALANCE '||fnd_date.date_to_canonical(l_prev_end_date));

3476: --Fnd_file.put_line(FND_FILE.LOG,'Fetching prev period act_id -- CORR_BALANCE ');
3477: OPEN csr_get_prev_period(l_curr_ass_action_id);
3478: FETCH csr_get_prev_period INTO l_prev_ass_act_id, l_prev_end_date;
3479: CLOSE csr_get_prev_period;
3480: --Fnd_file.put_line(FND_FILE.LOG,'Prev period End Date -- CORR_BALANCE '||fnd_date.date_to_canonical(l_prev_end_date));
3481: --Fnd_file.put_line(FND_FILE.LOG,'Prev period act_id -- CORR_BALANCE '||fnd_number.number_to_canonical(l_prev_ass_act_id));
3482: FOR l_ret_cor_period IN csr_ret_cor_period(p_actid) LOOP
3483: --Fnd_file.put_line(FND_FILE.LOG,'Calling populate_coll_bal_table -- CORR_BALANCE '||fnd_date.date_to_canonical(l_ret_cor_period.End_date));
3484: populate_coll_bal_table(p_actid => p_actid

Line 3483: --Fnd_file.put_line(FND_FILE.LOG,'Calling populate_coll_bal_table -- CORR_BALANCE '||fnd_date.date_to_canonical(l_ret_cor_period.End_date));

3479: CLOSE csr_get_prev_period;
3480: --Fnd_file.put_line(FND_FILE.LOG,'Prev period End Date -- CORR_BALANCE '||fnd_date.date_to_canonical(l_prev_end_date));
3481: --Fnd_file.put_line(FND_FILE.LOG,'Prev period act_id -- CORR_BALANCE '||fnd_number.number_to_canonical(l_prev_ass_act_id));
3482: FOR l_ret_cor_period IN csr_ret_cor_period(p_actid) LOOP
3483: --Fnd_file.put_line(FND_FILE.LOG,'Calling populate_coll_bal_table -- CORR_BALANCE '||fnd_date.date_to_canonical(l_ret_cor_period.End_date));
3484: populate_coll_bal_table(p_actid => p_actid
3485: ,p_tax_unit_id => l_tax_unit_id
3486: ,p_effective_date=> l_end_date
3487: ,p_balance_date => l_ret_cor_period.End_date

Line 3544: , p_action_information3 => fnd_date.date_to_canonical(l_ret_cor_period.start_date)

3540: , p_source_text => NULL
3541: , p_action_information_category => 'NL_WR_COLLECTIVE_REPORT'
3542: , p_action_information1 => 'CORR_BALANCE'
3543: , p_action_information2 => 'Saldo'
3544: , p_action_information3 => fnd_date.date_to_canonical(l_ret_cor_period.start_date)
3545: , p_action_information4 => fnd_date.date_to_canonical(l_ret_cor_period.End_date)
3546: , p_action_information5 => HR_GENERAL.decode_lookup('NL_FORM_LABELS',UPPER('Saldo'))
3547: , p_action_information6 => fnd_number.number_to_canonical(ROUND(l_corr_bal)));
3548: END LOOP;

Line 3545: , p_action_information4 => fnd_date.date_to_canonical(l_ret_cor_period.End_date)

3541: , p_action_information_category => 'NL_WR_COLLECTIVE_REPORT'
3542: , p_action_information1 => 'CORR_BALANCE'
3543: , p_action_information2 => 'Saldo'
3544: , p_action_information3 => fnd_date.date_to_canonical(l_ret_cor_period.start_date)
3545: , p_action_information4 => fnd_date.date_to_canonical(l_ret_cor_period.End_date)
3546: , p_action_information5 => HR_GENERAL.decode_lookup('NL_FORM_LABELS',UPPER('Saldo'))
3547: , p_action_information6 => fnd_number.number_to_canonical(ROUND(l_corr_bal)));
3548: END LOOP;
3549: -- Employer general total

Line 3571: ,fnd_date.canonical_to_date(l_period_dates_rec.start_date)

3567: ,p_action_information6 => fnd_number.number_to_canonical(ROUND(l_emp_total)));
3568: ELSE
3569: --Fnd_file.put_line(FND_FILE.LOG,' ELSE (Correction Period) -- CORRECTION ');
3570: OPEN csr_payroll_get_action_id2(p_actid
3571: ,fnd_date.canonical_to_date(l_period_dates_rec.start_date)
3572: ,fnd_date.canonical_to_date(l_period_dates_rec.End_date));
3573: FETCH csr_payroll_get_action_id2 INTO l_period_ass_action_id;
3574: CLOSE csr_payroll_get_action_id2;
3575: --Fnd_file.put_line(FND_FILE.LOG,'Calling populate_coll_bal_table -- CORRECTION ');

Line 3572: ,fnd_date.canonical_to_date(l_period_dates_rec.End_date));

3568: ELSE
3569: --Fnd_file.put_line(FND_FILE.LOG,' ELSE (Correction Period) -- CORRECTION ');
3570: OPEN csr_payroll_get_action_id2(p_actid
3571: ,fnd_date.canonical_to_date(l_period_dates_rec.start_date)
3572: ,fnd_date.canonical_to_date(l_period_dates_rec.End_date));
3573: FETCH csr_payroll_get_action_id2 INTO l_period_ass_action_id;
3574: CLOSE csr_payroll_get_action_id2;
3575: --Fnd_file.put_line(FND_FILE.LOG,'Calling populate_coll_bal_table -- CORRECTION ');
3576: populate_coll_bal_table(p_actid => p_actid

Line 3822: , p_action_information3 => fnd_date.date_to_canonical(sysdate)

3818: , p_tax_unit_id => l_tax_unit_id
3819: , p_action_information_category => 'NL_WR_EMPLOYER_INFO'
3820: , p_action_information1 => csr_employer_info_rec.action_information1
3821: , p_action_information2 => csr_employer_info_rec.action_information2
3822: , p_action_information3 => fnd_date.date_to_canonical(sysdate)
3823: , p_action_information4 => csr_employer_info_rec.action_information4
3824: , p_action_information5 => substr(l_seq_no,1,6)
3825: , p_action_information6 => csr_employer_info_rec.action_information6
3826: , p_action_information7 => csr_employer_info_rec.action_information7

Line 3829: , p_action_information10 => fnd_date.date_to_canonical(l_start_date)

3825: , p_action_information6 => csr_employer_info_rec.action_information6
3826: , p_action_information7 => csr_employer_info_rec.action_information7
3827: , p_action_information8 => csr_employer_info_rec.action_information8
3828: , p_action_information9 => csr_employer_info_rec.action_information9
3829: , p_action_information10 => fnd_date.date_to_canonical(l_start_date)
3830: , p_action_information11 => fnd_date.date_to_canonical(l_end_date)
3831: , p_action_information12 => csr_employer_info_rec.action_information12);
3832: --
3833: CLOSE csr_employer_info;

Line 3830: , p_action_information11 => fnd_date.date_to_canonical(l_end_date)

3826: , p_action_information7 => csr_employer_info_rec.action_information7
3827: , p_action_information8 => csr_employer_info_rec.action_information8
3828: , p_action_information9 => csr_employer_info_rec.action_information9
3829: , p_action_information10 => fnd_date.date_to_canonical(l_start_date)
3830: , p_action_information11 => fnd_date.date_to_canonical(l_end_date)
3831: , p_action_information12 => csr_employer_info_rec.action_information12);
3832: --
3833: CLOSE csr_employer_info;
3834: --

Line 4179: ,p_action_information5 => fnd_date.date_to_canonical(l_start_date)

4175: ,p_tax_unit_id => l_tax_unit_id
4176: ,p_action_information_category => 'NL_WR_SWMF_SECTOR_RISK_GROUP'
4177: ,p_action_information1 => 'SWMF'
4178: ,p_action_information2 => NULL
4179: ,p_action_information5 => fnd_date.date_to_canonical(l_start_date)
4180: ,p_action_information6 => fnd_date.date_to_canonical(l_end_date)
4181: ,p_action_information7 => l_sector
4182: ,p_action_information8 => l_risk_group
4183: ,p_action_information9 => fnd_number.number_to_canonical(ROUND(l_c_base_mon_fd))

Line 4180: ,p_action_information6 => fnd_date.date_to_canonical(l_end_date)

4176: ,p_action_information_category => 'NL_WR_SWMF_SECTOR_RISK_GROUP'
4177: ,p_action_information1 => 'SWMF'
4178: ,p_action_information2 => NULL
4179: ,p_action_information5 => fnd_date.date_to_canonical(l_start_date)
4180: ,p_action_information6 => fnd_date.date_to_canonical(l_end_date)
4181: ,p_action_information7 => l_sector
4182: ,p_action_information8 => l_risk_group
4183: ,p_action_information9 => fnd_number.number_to_canonical(ROUND(l_c_base_mon_fd))
4184: ,p_action_information10 => fnd_number.number_to_canonical(ROUND(l_con_mon_fd )));

Line 4202: , p_action_information3 => fnd_date.date_to_canonical(l_start_date)

4198: , p_source_text => NULL
4199: , p_action_information_category => 'NL_WR_COLLECTIVE_REPORT'
4200: , p_action_information1 => 'COMPLETE'
4201: , p_action_information2 => collXMLTable(i).TagName
4202: , p_action_information3 => fnd_date.date_to_canonical(l_start_date)
4203: , p_action_information4 => fnd_date.date_to_canonical(l_end_date)
4204: , p_action_information5 => collXMLTable(i).TagDesc
4205: , p_action_information6 => fnd_number.number_to_canonical(ROUND(collXMLTable(i).TagValue)));
4206: END IF;

Line 4203: , p_action_information4 => fnd_date.date_to_canonical(l_end_date)

4199: , p_action_information_category => 'NL_WR_COLLECTIVE_REPORT'
4200: , p_action_information1 => 'COMPLETE'
4201: , p_action_information2 => collXMLTable(i).TagName
4202: , p_action_information3 => fnd_date.date_to_canonical(l_start_date)
4203: , p_action_information4 => fnd_date.date_to_canonical(l_end_date)
4204: , p_action_information5 => collXMLTable(i).TagDesc
4205: , p_action_information6 => fnd_number.number_to_canonical(ROUND(collXMLTable(i).TagValue)));
4206: END IF;
4207: END LOOP;

Line 4222: ,p_action_information3 => fnd_date.date_to_canonical(l_start_date)

4218: ,p_source_text => NULL
4219: ,p_action_information_category => 'NL_WR_COLLECTIVE_REPORT'
4220: ,p_action_information1 => 'TOTAL'
4221: ,p_action_information2 => 'TotGen' -- TAG NAME
4222: ,p_action_information3 => fnd_date.date_to_canonical(l_start_date)
4223: ,p_action_information4 => fnd_date.date_to_canonical(l_end_date)
4224: ,p_action_information5 => HR_GENERAL.decode_lookup('NL_FORM_LABELS',UPPER('TotGen')) -- TAG DESCRIPTION
4225: ,p_action_information6 => fnd_number.number_to_canonical(ROUND(l_emp_total)));
4226: --

Line 4223: ,p_action_information4 => fnd_date.date_to_canonical(l_end_date)

4219: ,p_action_information_category => 'NL_WR_COLLECTIVE_REPORT'
4220: ,p_action_information1 => 'TOTAL'
4221: ,p_action_information2 => 'TotGen' -- TAG NAME
4222: ,p_action_information3 => fnd_date.date_to_canonical(l_start_date)
4223: ,p_action_information4 => fnd_date.date_to_canonical(l_end_date)
4224: ,p_action_information5 => HR_GENERAL.decode_lookup('NL_FORM_LABELS',UPPER('TotGen')) -- TAG DESCRIPTION
4225: ,p_action_information6 => fnd_number.number_to_canonical(ROUND(l_emp_total)));
4226: --
4227: END IF;

Line 4759: nvl(addr.date_to,fnd_date.canonical_to_date('4712/12/31'))

4755: FROM per_addresses addr
4756: WHERE addr.person_id = c_person_id
4757: AND addr.primary_flag = 'Y'
4758: AND c_effective_date BETWEEN addr.date_from AND
4759: nvl(addr.date_to,fnd_date.canonical_to_date('4712/12/31'))
4760: ORDER BY 1 DESC;
4761: --
4762: l_addr csr_get_emp_address%ROWTYPE;
4763: l_ovn pay_action_information.object_version_number%type;

Line 4797: , p_action_information5 => fnd_date.date_to_canonical(p_eff_date)

4793: , p_source_text => NULL
4794: , p_tax_unit_id => p_tax_unit_id
4795: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
4796: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_STREET')
4797: , p_action_information5 => fnd_date.date_to_canonical(p_eff_date)
4798: , p_action_information6 => 'Street name missing in the address'
4799: , p_action_information7 => p_name
4800: , p_action_information8 => p_assignment_number);
4801: END IF;

Line 4817: , p_action_information5 => fnd_date.date_to_canonical(p_eff_date)

4813: , p_source_text => NULL
4814: , p_tax_unit_id => p_tax_unit_id
4815: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
4816: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_CITY')
4817: , p_action_information5 => fnd_date.date_to_canonical(p_eff_date)
4818: , p_action_information6 => 'City name missing in the address'
4819: , p_action_information7 => p_name
4820: , p_action_information8 => p_assignment_number);
4821: END IF;

Line 4837: , p_action_information5 => fnd_date.date_to_canonical(p_eff_date)

4833: , p_source_text => NULL
4834: , p_tax_unit_id => p_tax_unit_id
4835: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
4836: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_PO_CODE')
4837: , p_action_information5 => fnd_date.date_to_canonical(p_eff_date)
4838: , p_action_information6 => 'Postal Code missing in address'
4839: , p_action_information7 => p_name
4840: , p_action_information8 => p_assignment_number);
4841: END IF;

Line 4857: , p_action_information5 => fnd_date.date_to_canonical(p_eff_date)

4853: , p_source_text => NULL
4854: , p_tax_unit_id => p_tax_unit_id
4855: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
4856: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_COUNTRY')
4857: , p_action_information5 => fnd_date.date_to_canonical(p_eff_date)
4858: , p_action_information6 => 'Country is missing in address'
4859: , p_action_information7 => p_name
4860: , p_action_information8 => p_assignment_number);
4861: END IF;

Line 4886: , p_action_information5 => fnd_date.date_to_canonical(p_eff_date)

4882: , p_source_text => NULL
4883: , p_tax_unit_id => p_tax_unit_id
4884: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
4885: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_PO_FORMAT')
4886: , p_action_information5 => fnd_date.date_to_canonical(p_eff_date)
4887: , p_action_information6 => 'Postal Code is not in the format 9999XX'
4888: , p_action_information7 => p_name
4889: , p_action_information8 => p_assignment_number);
4890: END IF;

Line 4959: , p_action_information5 => fnd_date.date_to_canonical(p_eff_date)

4955: , p_source_text => NULL
4956: , p_tax_unit_id => p_tax_unit_id
4957: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
4958: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_ADDRESS')
4959: , p_action_information5 => fnd_date.date_to_canonical(p_eff_date)
4960: , p_action_information6 => 'Address details are null'
4961: , p_action_information7 => p_name
4962: , p_action_information8 => p_assignment_number);
4963: END IF;

Line 4985: ,fnd_date.canonical_to_date(aei.AEI_INFORMATION1) start_date

4981: CURSOR csr_get_emp_risk_grp(c_assignment_id NUMBER) IS
4982: SELECT DISTINCT hoi.organization_id
4983: ,hoi.org_information5 sector
4984: ,hoi.org_information6 risk_group
4985: ,fnd_date.canonical_to_date(aei.AEI_INFORMATION1) start_date
4986: ,fnd_date.canonical_to_date(aei.AEI_INFORMATION2) end_date
4987: FROM hr_organization_information hoi
4988: ,per_assignment_extra_info aei
4989: WHERE hoi.organization_id = nvl( aei.aei_information8,HR_NL_ORG_INFO.Get_SI_Provider_Info(aei.assignment_id,aei.AEI_INFORMATION3))

Line 4986: ,fnd_date.canonical_to_date(aei.AEI_INFORMATION2) end_date

4982: SELECT DISTINCT hoi.organization_id
4983: ,hoi.org_information5 sector
4984: ,hoi.org_information6 risk_group
4985: ,fnd_date.canonical_to_date(aei.AEI_INFORMATION1) start_date
4986: ,fnd_date.canonical_to_date(aei.AEI_INFORMATION2) end_date
4987: FROM hr_organization_information hoi
4988: ,per_assignment_extra_info aei
4989: WHERE hoi.organization_id = nvl( aei.aei_information8,HR_NL_ORG_INFO.Get_SI_Provider_Info(aei.assignment_id,aei.AEI_INFORMATION3))
4990: AND aei.assignment_id = c_assignment_id

Line 5065: , p_action_information5 => fnd_date.date_to_canonical(l_start_date)

5061: ,p_tax_unit_id => p_tax_unit_id
5062: , p_action_information_category => 'NL_WR_SWMF_SECTOR_RISK_GROUP'
5063: , p_action_information1 => 'SECTOR_RISK_GROUP'
5064: , p_action_information2 => p_mas_act_info_id
5065: , p_action_information5 => fnd_date.date_to_canonical(l_start_date)
5066: , p_action_information6 => fnd_date.date_to_canonical(LEAST(l_end_date,NVL(l_rec.end_date,l_end_date)))
5067: , p_action_information7 => l_rec.sector
5068: , p_action_information8 => l_rec.risk_group
5069: , p_action_information9 => NULL

Line 5066: , p_action_information6 => fnd_date.date_to_canonical(LEAST(l_end_date,NVL(l_rec.end_date,l_end_date)))

5062: , p_action_information_category => 'NL_WR_SWMF_SECTOR_RISK_GROUP'
5063: , p_action_information1 => 'SECTOR_RISK_GROUP'
5064: , p_action_information2 => p_mas_act_info_id
5065: , p_action_information5 => fnd_date.date_to_canonical(l_start_date)
5066: , p_action_information6 => fnd_date.date_to_canonical(LEAST(l_end_date,NVL(l_rec.end_date,l_end_date)))
5067: , p_action_information7 => l_rec.sector
5068: , p_action_information8 => l_rec.risk_group
5069: , p_action_information9 => NULL
5070: , p_action_information10 => NULL);

Line 5099: , p_action_information5 => fnd_date.date_to_canonical(p_start_date)

5095: ,p_tax_unit_id => p_tax_unit_id
5096: , p_action_information_category => 'NL_WR_SWMF_SECTOR_RISK_GROUP'
5097: , p_action_information1 => 'SECTOR_RISK_GROUP'
5098: , p_action_information2 => p_mas_act_info_id
5099: , p_action_information5 => fnd_date.date_to_canonical(p_start_date)
5100: , p_action_information6 => fnd_date.date_to_canonical(p_end_date)
5101: , p_action_information7 => l_sector_rec.sector
5102: , p_action_information8 => l_sector_rec.risk_group
5103: , p_action_information9 => NULL

Line 5100: , p_action_information6 => fnd_date.date_to_canonical(p_end_date)

5096: , p_action_information_category => 'NL_WR_SWMF_SECTOR_RISK_GROUP'
5097: , p_action_information1 => 'SECTOR_RISK_GROUP'
5098: , p_action_information2 => p_mas_act_info_id
5099: , p_action_information5 => fnd_date.date_to_canonical(p_start_date)
5100: , p_action_information6 => fnd_date.date_to_canonical(p_end_date)
5101: , p_action_information7 => l_sector_rec.sector
5102: , p_action_information8 => l_sector_rec.risk_group
5103: , p_action_information9 => NULL
5104: , p_action_information10 => NULL);

Line 5298: , p_action_information5 => fnd_date.date_to_canonical(p_date)

5294: , p_source_text => NULL
5295: , p_tax_unit_id => p_tax_unit_id
5296: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
5297: , p_action_information4 => fnd_message.get_string('PER','HR_NL_UFO_AND_AWF')
5298: , p_action_information5 => fnd_date.date_to_canonical(p_date)
5299: , p_action_information6 => 'UFO Contributions and AWF Contributions exist'
5300: , p_action_information7 => p_name
5301: , p_action_information8 => p_assignment_id);
5302: END IF;

Line 5318: , p_action_information5 => fnd_date.date_to_canonical(p_date)

5314: , p_source_text => NULL
5315: , p_tax_unit_id => p_tax_unit_id
5316: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
5317: , p_action_information4 => fnd_message.get_string('PER','HR_NL_ZERO_WEWA')
5318: , p_action_information5 => fnd_date.date_to_canonical(p_date)
5319: , p_action_information6 => 'Waiting Money Fund contribution is zero'
5320: , p_action_information7 => p_name
5321: , p_action_information8 => p_assignment_id);
5322: END IF;

Line 5338: , p_action_information5 => fnd_date.date_to_canonical(p_date)

5334: , p_source_text => NULL
5335: , p_tax_unit_id => p_tax_unit_id
5336: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
5337: , p_action_information4 => fnd_message.get_string('PER','HR_NL_ZERO_WEWE')
5338: , p_action_information5 => fnd_date.date_to_canonical(p_date)
5339: , p_action_information6 => 'AWF contribution is zero'
5340: , p_action_information7 => p_name
5341: , p_action_information8 => p_assignment_id);
5342: END IF;

Line 5451: SELECT fnd_date.canonical_to_date(aei_information1) start_date

5447: ,p_zvw_situation IN OUT NOCOPY PER_ASSIGNMENT_EXTRA_INFO.AEI_INFORMATION1%type
5448: ,p_chk IN OUT NOCOPY VARCHAR2) IS
5449: --
5450: CURSOR csr_get_ass_extra_info(c_a_extra_info_id NUMBER) IS
5451: SELECT fnd_date.canonical_to_date(aei_information1) start_date
5452: ,fnd_date.canonical_to_date(aei_information2) end_date
5453: ,aei_information3 info1
5454: ,aei_information15 info2
5455: ,aei_information_category

Line 5452: ,fnd_date.canonical_to_date(aei_information2) end_date

5448: ,p_chk IN OUT NOCOPY VARCHAR2) IS
5449: --
5450: CURSOR csr_get_ass_extra_info(c_a_extra_info_id NUMBER) IS
5451: SELECT fnd_date.canonical_to_date(aei_information1) start_date
5452: ,fnd_date.canonical_to_date(aei_information2) end_date
5453: ,aei_information3 info1
5454: ,aei_information15 info2
5455: ,aei_information_category
5456: FROM per_assignment_extra_info

Line 5461: SELECT fnd_date.canonical_to_date(aei_information1) start_date

5457: WHERE assignment_extra_info_id = c_a_extra_info_id
5458: AND aei_information_category IN ('NL_LBR','NL_INF','NL_TML','NL_LHI','NL_SII');
5459: --
5460: CURSOR csr_get_all_ass_extra_info(c_assignment_id NUMBER) IS
5461: SELECT fnd_date.canonical_to_date(aei_information1) start_date
5462: ,fnd_date.canonical_to_date(aei_information2) end_date
5463: ,aei_information3 info1
5464: ,aei_information15 info2
5465: ,aei_information_category

Line 5462: ,fnd_date.canonical_to_date(aei_information2) end_date

5458: AND aei_information_category IN ('NL_LBR','NL_INF','NL_TML','NL_LHI','NL_SII');
5459: --
5460: CURSOR csr_get_all_ass_extra_info(c_assignment_id NUMBER) IS
5461: SELECT fnd_date.canonical_to_date(aei_information1) start_date
5462: ,fnd_date.canonical_to_date(aei_information2) end_date
5463: ,aei_information3 info1
5464: ,aei_information15 info2
5465: ,aei_information_category
5466: FROM per_assignment_extra_info

Line 5512: IF p_eff_date BETWEEN i.start_date AND NVL(i.end_date,fnd_date.canonical_to_date('4712/12/31'))

5508: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ Inside get_assignment_extra_info'||p_assignment_id);
5509: IF p_surrogate_key IS NULL THEN
5510: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_surrogate_key is null');
5511: FOR i in csr_get_all_ass_extra_info(p_assignment_id) LOOP
5512: IF p_eff_date BETWEEN i.start_date AND NVL(i.end_date,fnd_date.canonical_to_date('4712/12/31'))
5513: OR (i.start_date <= p_end_date AND NVL(i.end_date,p_end_date) >= p_start_date AND p_eff_date < fnd_date.canonical_to_date('2006/01/01')) THEN -- SR 5531106.992
5514: --
5515: IF i.aei_information_category = 'NL_LBR' AND l_labour_rel_code IS NULL THEN
5516: l_labour_rel_code := i.info1;

Line 5513: OR (i.start_date <= p_end_date AND NVL(i.end_date,p_end_date) >= p_start_date AND p_eff_date < fnd_date.canonical_to_date('2006/01/01')) THEN -- SR 5531106.992

5509: IF p_surrogate_key IS NULL THEN
5510: --##--Fnd_file.put_line(FND_FILE.LOG,'#######~~ p_surrogate_key is null');
5511: FOR i in csr_get_all_ass_extra_info(p_assignment_id) LOOP
5512: IF p_eff_date BETWEEN i.start_date AND NVL(i.end_date,fnd_date.canonical_to_date('4712/12/31'))
5513: OR (i.start_date <= p_end_date AND NVL(i.end_date,p_end_date) >= p_start_date AND p_eff_date < fnd_date.canonical_to_date('2006/01/01')) THEN -- SR 5531106.992
5514: --
5515: IF i.aei_information_category = 'NL_LBR' AND l_labour_rel_code IS NULL THEN
5516: l_labour_rel_code := i.info1;
5517: ELSIF i.aei_information_category = 'NL_INF' AND l_ins_duty_code IS NULL THEN

Line 5698: SELECT fnd_date.canonical_to_date(aei_information1) start_date

5694: FROM pay_dated_tables
5695: WHERE TABLE_NAME = c_table_name;
5696: --
5697: CURSOR csr_get_eit_effective_date(c_a_extra_info_id NUMBER) IS
5698: SELECT fnd_date.canonical_to_date(aei_information1) start_date
5699: FROM per_assignment_extra_info
5700: WHERE assignment_extra_info_id = c_a_extra_info_id
5701: AND aei_information_category IN ('NL_LBR','NL_INF','NL_TML','NL_LHI','NL_SII');
5702: --

Line 6456: ele_end_date := fnd_date.canonical_to_date(l_element_rec.period);

6452: l_retro_done := 'N';
6453: IF l_master_assignment_action_id IS NOT NULL THEN
6454: FOR l_element_rec in csr_get_element_details(l_master_assignment_action_id,p_chk_end_date) LOOP
6455: l_retro_type := 'HR';
6456: ele_end_date := fnd_date.canonical_to_date(l_element_rec.period);
6457: --
6458: IF l_element_rec.Retro_type = 'WITHDRAWAL' AND l_element_rec.period IS NOT NULL THEN
6459: OPEN csr_get_period(l_emp_rec.payroll_id, ele_end_date);
6460: FETCH csr_get_period INTO l_period_start_date,l_period_end_date;

Line 6813: --Fnd_file.put_line(FND_FILE.LOG,' g_effective_date :'||fnd_date.date_to_canonical(g_effective_date));

6809: ELSE
6810: l_type := 'CORRECT';
6811: END IF;
6812: --Fnd_file.put_line(FND_FILE.LOG,' l_type :'||l_type);
6813: --Fnd_file.put_line(FND_FILE.LOG,' g_effective_date :'||fnd_date.date_to_canonical(g_effective_date));
6814: --
6815: pay_action_information_api.create_action_information (
6816: p_action_information_id => l_master_action_info_id
6817: ,p_action_context_id => p_assactid

Line 6827: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(l_rec_changes(i).period_start_date,trunc(l_rec_changes(i).period_end_date,'Y')))

6823: ,p_source_text => NULL
6824: ,p_tax_unit_id => l_tax_unit_id
6825: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
6826: ,p_action_information1 => 'WITHDRAWAL'
6827: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(l_rec_changes(i).period_start_date,trunc(l_rec_changes(i).period_end_date,'Y')))
6828: ,p_action_information3 => fnd_date.date_to_canonical(l_rec_changes(i).period_end_date)
6829: ,p_action_information4 => l_emp_rec.assignment_number
6830: ,p_action_information5 => l_emp_rec.employee_number
6831: ,p_action_information6 => l_emp_rec.change_reason

Line 6828: ,p_action_information3 => fnd_date.date_to_canonical(l_rec_changes(i).period_end_date)

6824: ,p_tax_unit_id => l_tax_unit_id
6825: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
6826: ,p_action_information1 => 'WITHDRAWAL'
6827: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(l_rec_changes(i).period_start_date,trunc(l_rec_changes(i).period_end_date,'Y')))
6828: ,p_action_information3 => fnd_date.date_to_canonical(l_rec_changes(i).period_end_date)
6829: ,p_action_information4 => l_emp_rec.assignment_number
6830: ,p_action_information5 => l_emp_rec.employee_number
6831: ,p_action_information6 => l_emp_rec.change_reason
6832: ,p_action_information8 => l_emp_rec.sofi_number

Line 6837: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)

6833: ,p_action_information7 => l_emp_rec.person_id
6834: ,p_action_information9 => l_emp_rec.Initials
6835: ,p_action_information10 => l_emp_rec.prefix
6836: ,p_action_information11 => l_emp_rec.last_name
6837: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
6838: ,p_action_information13 => l_nationality
6839: ,p_action_information14 => l_emp_rec.gender
6840: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
6841: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)

Line 6840: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date

6836: ,p_action_information11 => l_emp_rec.last_name
6837: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
6838: ,p_action_information13 => l_nationality
6839: ,p_action_information14 => l_emp_rec.gender
6840: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
6841: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
6842: ,p_action_information17 => l_rec_changes(i).retro
6843: ,p_action_information18 => l_emp_seq);
6844: ELSE

Line 6841: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)

6837: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
6838: ,p_action_information13 => l_nationality
6839: ,p_action_information14 => l_emp_rec.gender
6840: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
6841: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
6842: ,p_action_information17 => l_rec_changes(i).retro
6843: ,p_action_information18 => l_emp_seq);
6844: ELSE
6845: --

Line 6878: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(l_rec_changes(i).period_start_date,trunc(l_rec_changes(i).period_end_date,'Y')))

6874: ,p_source_text => NULL
6875: ,p_tax_unit_id => l_tax_unit_id
6876: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
6877: ,p_action_information1 => 'INITIAL'
6878: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(l_rec_changes(i).period_start_date,trunc(l_rec_changes(i).period_end_date,'Y')))
6879: ,p_action_information3 => fnd_date.date_to_canonical(l_rec_changes(i).period_end_date)
6880: ,p_action_information4 => l_emp_rec.assignment_number
6881: ,p_action_information5 => l_emp_rec.employee_number
6882: ,p_action_information6 => l_emp_rec.change_reason

Line 6879: ,p_action_information3 => fnd_date.date_to_canonical(l_rec_changes(i).period_end_date)

6875: ,p_tax_unit_id => l_tax_unit_id
6876: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
6877: ,p_action_information1 => 'INITIAL'
6878: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(l_rec_changes(i).period_start_date,trunc(l_rec_changes(i).period_end_date,'Y')))
6879: ,p_action_information3 => fnd_date.date_to_canonical(l_rec_changes(i).period_end_date)
6880: ,p_action_information4 => l_emp_rec.assignment_number
6881: ,p_action_information5 => l_emp_rec.employee_number
6882: ,p_action_information6 => l_emp_rec.change_reason
6883: ,p_action_information8 => l_emp_rec.sofi_number

Line 6888: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)

6884: ,p_action_information7 => l_emp_rec.person_id
6885: ,p_action_information9 => l_emp_rec.Initials
6886: ,p_action_information10 => l_emp_rec.prefix
6887: ,p_action_information11 => l_emp_rec.last_name
6888: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
6889: ,p_action_information13 => l_nationality
6890: ,p_action_information14 => l_emp_rec.gender
6891: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)
6892: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)

Line 6891: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)

6887: ,p_action_information11 => l_emp_rec.last_name
6888: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
6889: ,p_action_information13 => l_nationality
6890: ,p_action_information14 => l_emp_rec.gender
6891: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)
6892: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
6893: ,p_action_information17 => l_rec_changes(i).retro
6894: ,p_action_information18 => l_emp_seq);
6895: --

Line 6892: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)

6888: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
6889: ,p_action_information13 => l_nationality
6890: ,p_action_information14 => l_emp_rec.gender
6891: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)
6892: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
6893: ,p_action_information17 => l_rec_changes(i).retro
6894: ,p_action_information18 => l_emp_seq);
6895: --
6896: l_archive_emp_info := 'N';

Line 6981: , p_action_information5 => fnd_date.date_to_canonical(GREATEST(GREATEST(l_rec_changes(i).period_start_date,l_ass_start_date),trunc(l_rec_changes(i).period_end_date,'Y'))) /* EOY 0708 ...Start Date Income Peiod */

6977: , p_action_information_category => 'NL_WR_INCOME_PERIOD'
6978: , p_tax_unit_id => l_tax_unit_id
6979: , p_action_information1 => 'INITIAL'
6980: , p_action_information2 => l_master_action_info_id
6981: , p_action_information5 => fnd_date.date_to_canonical(GREATEST(GREATEST(l_rec_changes(i).period_start_date,l_ass_start_date),trunc(l_rec_changes(i).period_end_date,'Y'))) /*** EOY 0708 ...Start Date Income Peiod ***/
6982: , p_action_information6 => l_income_code
6983: , p_action_information7 => l_labour_rel_code
6984: , p_action_information8 => l_ins_duty_code
6985: , p_action_information9 => l_assignment_catg

Line 7287: --Fnd_file.put_line(FND_FILE.LOG,' g_effective_date :'||fnd_date.date_to_canonical(g_effective_date));

7283: ELSE
7284: l_type := 'CORRECT';
7285: END IF;
7286: --Fnd_file.put_line(FND_FILE.LOG,' l_type :'||l_type);
7287: --Fnd_file.put_line(FND_FILE.LOG,' g_effective_date :'||fnd_date.date_to_canonical(g_effective_date));
7288: --
7289: l_emp_end_date := NULL;
7290: IF l_ass_end_date <= l_period_end_date THEN
7291: l_emp_end_date := l_ass_end_date;

Line 7322: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(l_period_start_date,trunc(l_period_end_date,'Y')))

7318: ,p_source_text => NULL
7319: ,p_tax_unit_id => l_tax_unit_id
7320: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
7321: ,p_action_information1 => l_type
7322: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(l_period_start_date,trunc(l_period_end_date,'Y')))
7323: ,p_action_information3 => fnd_date.date_to_canonical(l_period_end_date)
7324: ,p_action_information4 => l_emp_rec.assignment_number
7325: ,p_action_information5 => l_emp_rec.employee_number
7326: ,p_action_information6 => l_emp_rec.change_reason

Line 7323: ,p_action_information3 => fnd_date.date_to_canonical(l_period_end_date)

7319: ,p_tax_unit_id => l_tax_unit_id
7320: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
7321: ,p_action_information1 => l_type
7322: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(l_period_start_date,trunc(l_period_end_date,'Y')))
7323: ,p_action_information3 => fnd_date.date_to_canonical(l_period_end_date)
7324: ,p_action_information4 => l_emp_rec.assignment_number
7325: ,p_action_information5 => l_emp_rec.employee_number
7326: ,p_action_information6 => l_emp_rec.change_reason
7327: ,p_action_information8 => l_emp_rec.sofi_number

Line 7332: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)

7328: ,p_action_information7 => l_emp_rec.person_id
7329: ,p_action_information9 => l_emp_rec.Initials
7330: ,p_action_information10 => l_emp_rec.prefix
7331: ,p_action_information11 => l_emp_rec.last_name
7332: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
7333: ,p_action_information13 => l_nationality
7334: ,p_action_information14 => l_emp_rec.gender
7335: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
7336: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)

Line 7335: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date

7331: ,p_action_information11 => l_emp_rec.last_name
7332: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
7333: ,p_action_information13 => l_nationality
7334: ,p_action_information14 => l_emp_rec.gender
7335: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
7336: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
7337: ,p_action_information17 => l_rec_changes(i).retro
7338: ,p_action_information18 => l_emp_seq);
7339: END IF;

Line 7336: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)

7332: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
7333: ,p_action_information13 => l_nationality
7334: ,p_action_information14 => l_emp_rec.gender
7335: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
7336: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
7337: ,p_action_information17 => l_rec_changes(i).retro
7338: ,p_action_information18 => l_emp_seq);
7339: END IF;
7340: l_archive_emp_info := 'Y';

Line 7396: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)

7392: , p_source_text => NULL
7393: , p_tax_unit_id => l_tax_unit_id
7394: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7395: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_ASG_RISK_GRP')
7396: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)
7397: , p_action_information6 => 'Assignment has no Sector or Risk Group'
7398: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
7399: , p_action_information8 => l_emp_rec.assignment_number);
7400: --

Line 7459: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)

7455: , p_source_text => NULL
7456: , p_tax_unit_id => l_tax_unit_id
7457: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7458: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_ZVW_CODE_INSURED')
7459: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)
7460: , p_action_information6 => 'Code ZVW is defaulted to - A'
7461: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
7462: , p_action_information8 => l_emp_rec.assignment_number);
7463: END IF;

Line 7479: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)

7475: , p_source_text => NULL
7476: , p_tax_unit_id => l_tax_unit_id
7477: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7478: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_LABOR_CODE')
7479: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)
7480: , p_action_information6 => 'Labor Relation code is null'
7481: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
7482: , p_action_information8 => l_emp_rec.assignment_number);
7483: END IF;

Line 7499: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)

7495: , p_source_text => NULL
7496: , p_tax_unit_id => l_tax_unit_id
7497: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7498: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_CONTRACT_CODE')
7499: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)
7500: , p_action_information6 => 'Code contract for limited or unlimited time is null'
7501: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
7502: , p_action_information8 => l_emp_rec.assignment_number);
7503: END IF;

Line 7519: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)

7515: , p_source_text => NULL
7516: , p_tax_unit_id => l_tax_unit_id
7517: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7518: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_WORK_PATTERN')
7519: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)
7520: , p_action_information6 => 'Indication regular work pattern is null'
7521: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
7522: , p_action_information8 => l_emp_rec.assignment_number);
7523: END IF;

Line 7539: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)

7535: , p_source_text => NULL
7536: , p_tax_unit_id => l_tax_unit_id
7537: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7538: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_TEMP_LABOR_CODE')
7539: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)
7540: , p_action_information6 => 'Temp Labor Code is null'
7541: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
7542: , p_action_information8 => l_emp_rec.assignment_number);
7543: END IF;

Line 7559: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)

7555: , p_source_text => NULL
7556: , p_tax_unit_id => l_tax_unit_id
7557: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7558: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_COMPANY_CAR_USE')
7559: , p_action_information5 => fnd_date.date_to_canonical(l_rec_changes(i).effective_date)
7560: , p_action_information6 => 'Company Car Usage code invalid'
7561: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
7562: , p_action_information8 => l_emp_rec.assignment_number);
7563: END IF;

Line 7577: , p_action_information5 => fnd_date.date_to_canonical(GREATEST(GREATEST(l_rec_changes(i).effective_date,l_ass_start_date),trunc(l_rec_changes(i).period_end_date,'Y'))) /* EOY 0708 ...Start Date Income Peiod */

7573: , p_action_information_category => 'NL_WR_INCOME_PERIOD'
7574: , p_tax_unit_id => l_tax_unit_id
7575: , p_action_information1 => l_type
7576: , p_action_information2 => l_master_action_info_id
7577: , p_action_information5 => fnd_date.date_to_canonical(GREATEST(GREATEST(l_rec_changes(i).effective_date,l_ass_start_date),trunc(l_rec_changes(i).period_end_date,'Y'))) /*** EOY 0708 ...Start Date Income Peiod ***/
7578: , p_action_information6 => l_income_code
7579: , p_action_information7 => l_labour_rel_code
7580: , p_action_information8 => l_ins_duty_code
7581: , p_action_information9 => l_assignment_catg

Line 7631: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(p_chk_start_date,trunc(p_chk_end_date,'Y')))

7627: ,p_source_text => NULL
7628: ,p_tax_unit_id => l_tax_unit_id
7629: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
7630: ,p_action_information1 => l_type
7631: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(p_chk_start_date,trunc(p_chk_end_date,'Y')))
7632: ,p_action_information3 => fnd_date.date_to_canonical(p_chk_end_date)
7633: ,p_action_information4 => l_emp_rec.assignment_number
7634: ,p_action_information5 => l_emp_rec.employee_number
7635: ,p_action_information8 => l_emp_rec.sofi_number

Line 7632: ,p_action_information3 => fnd_date.date_to_canonical(p_chk_end_date)

7628: ,p_tax_unit_id => l_tax_unit_id
7629: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
7630: ,p_action_information1 => l_type
7631: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(p_chk_start_date,trunc(p_chk_end_date,'Y')))
7632: ,p_action_information3 => fnd_date.date_to_canonical(p_chk_end_date)
7633: ,p_action_information4 => l_emp_rec.assignment_number
7634: ,p_action_information5 => l_emp_rec.employee_number
7635: ,p_action_information8 => l_emp_rec.sofi_number
7636: ,p_action_information6 => l_emp_rec.change_reason

Line 7641: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)

7637: ,p_action_information7 => l_emp_rec.person_id
7638: ,p_action_information9 => l_emp_rec.Initials
7639: ,p_action_information10 => l_emp_rec.prefix
7640: ,p_action_information11 => l_emp_rec.last_name
7641: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
7642: ,p_action_information13 => l_nationality
7643: ,p_action_information14 => l_emp_rec.gender
7644: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
7645: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)

Line 7644: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date

7640: ,p_action_information11 => l_emp_rec.last_name
7641: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
7642: ,p_action_information13 => l_nationality
7643: ,p_action_information14 => l_emp_rec.gender
7644: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
7645: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
7646: ,p_action_information17 => NULL
7647: ,p_action_information18 => l_emp_seq);
7648: END IF;

Line 7645: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)

7641: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
7642: ,p_action_information13 => l_nationality
7643: ,p_action_information14 => l_emp_rec.gender
7644: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
7645: ,p_action_information16 => fnd_date.date_to_canonical(l_emp_end_date)
7646: ,p_action_information17 => NULL
7647: ,p_action_information18 => l_emp_seq);
7648: END IF;
7649: -- archive address information

Line 7689: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)

7685: , p_source_text => NULL
7686: , p_tax_unit_id => l_tax_unit_id
7687: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7688: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_ASG_RISK_GRP')
7689: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
7690: , p_action_information6 => 'Assignment has no Sector or Risk Group'
7691: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
7692: , p_action_information8 => l_emp_rec.assignment_number);
7693: --

Line 7711: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)

7707: , p_source_text => NULL
7708: , p_tax_unit_id => l_tax_unit_id
7709: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7710: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_ZVW_CODE_INSURED')
7711: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
7712: , p_action_information6 => 'Code ZVW is defaulted to - A'
7713: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
7714: , p_action_information8 => l_emp_rec.assignment_number);
7715: END IF;

Line 7731: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)

7727: , p_source_text => NULL
7728: , p_tax_unit_id => l_tax_unit_id
7729: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7730: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_LABOR_CODE')
7731: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
7732: , p_action_information6 => 'Labor Relation code is null'
7733: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
7734: , p_action_information8 => l_emp_rec.assignment_number);
7735: END IF;

Line 7751: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)

7747: , p_source_text => NULL
7748: , p_tax_unit_id => l_tax_unit_id
7749: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7750: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_CONTRACT_CODE')
7751: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
7752: , p_action_information6 => 'Code contract for limited or unlimited time is null'
7753: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
7754: , p_action_information8 => l_emp_rec.assignment_number);
7755: END IF;

Line 7771: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)

7767: , p_source_text => NULL
7768: , p_tax_unit_id => l_tax_unit_id
7769: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7770: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_WORK_PATTERN')
7771: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
7772: , p_action_information6 => 'Indication regular work pattern is null'
7773: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
7774: , p_action_information8 => l_emp_rec.assignment_number);
7775: END IF;

Line 7791: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)

7787: , p_source_text => NULL
7788: , p_tax_unit_id => l_tax_unit_id
7789: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7790: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_TEMP_LABOR_CODE')
7791: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
7792: , p_action_information6 => 'Temp Labor Code is null'
7793: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
7794: , p_action_information8 => l_emp_rec.assignment_number);
7795: END IF;

Line 7811: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)

7807: , p_source_text => NULL
7808: , p_tax_unit_id => l_tax_unit_id
7809: , p_action_information_category => 'NL_WR_EXCEPTION_REPORT'
7810: , p_action_information4 => fnd_message.get_string('PER','HR_NL_INVALID_COMPANY_CAR_USE')
7811: , p_action_information5 => fnd_date.date_to_canonical(p_effective_date)
7812: , p_action_information6 => 'Company Car Usage code invalid'
7813: , p_action_information7 => l_emp_rec.prefix || l_emp_rec.last_name || l_emp_rec.Initials
7814: , p_action_information8 => l_emp_rec.assignment_number);
7815: END IF;

Line 7831: , p_action_information5 => fnd_date.date_to_canonical(GREATEST(GREATEST(p_chk_start_date,l_ass_start_date),trunc(p_chk_end_date,'Y'))) /* EOY 0708 ...Start Date Income Peiod */

7827: , p_action_information_category => 'NL_WR_INCOME_PERIOD'
7828: , p_tax_unit_id => l_tax_unit_id
7829: , p_action_information1 => l_type
7830: , p_action_information2 => l_master_action_info_id
7831: , p_action_information5 => fnd_date.date_to_canonical(GREATEST(GREATEST(p_chk_start_date,l_ass_start_date),trunc(p_chk_end_date,'Y'))) /*** EOY 0708 ...Start Date Income Peiod ***/
7832: , p_action_information6 => l_income_code
7833: , p_action_information7 => l_labour_rel_code
7834: , p_action_information8 => l_ins_duty_code
7835: , p_action_information9 => l_assignment_catg

Line 7919: SELECT MIN(fnd_date.canonical_to_date(action_information5))

7915: ORDER BY pai.effective_date DESC;
7916: --
7917: CURSOR csr_get_income_start_date(c_assactid NUMBER
7918: ,c_assignment_id NUMBER) IS
7919: SELECT MIN(fnd_date.canonical_to_date(action_information5))
7920: FROM pay_action_interlocks pal
7921: ,pay_action_information pai
7922: WHERE pal.locking_action_id = c_assactid
7923: AND pal.locked_action_id = pai.action_context_id

Line 7933: SELECT MIN(fnd_date.canonical_to_date(action_information5))

7929: CURSOR csr_get_sector_start_date(c_assactid NUMBER
7930: ,c_assignment_id NUMBER
7931: ,c_sector VARCHAR2
7932: ,c_risk_grp VARCHAR2) IS
7933: SELECT MIN(fnd_date.canonical_to_date(action_information5))
7934: FROM pay_action_interlocks pal
7935: ,pay_action_information pai
7936: WHERE pal.locking_action_id = c_assactid
7937: AND pal.locked_action_id = pai.action_context_id

Line 8039: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(p_start_date,trunc(p_end_date,'Y')))

8035: ,p_source_text => NULL
8036: ,p_tax_unit_id => l_tax_unit_id
8037: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
8038: ,p_action_information1 => 'INITIAL'
8039: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(p_start_date,trunc(p_end_date,'Y')))
8040: ,p_action_information3 => fnd_date.date_to_canonical(p_end_date)
8041: ,p_action_information4 => csr_action_info_rec.action_information4
8042: ,p_action_information5 => csr_action_info_rec.action_information5
8043: ,p_action_information6 => csr_action_info_rec.action_information6

Line 8040: ,p_action_information3 => fnd_date.date_to_canonical(p_end_date)

8036: ,p_tax_unit_id => l_tax_unit_id
8037: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
8038: ,p_action_information1 => 'INITIAL'
8039: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(p_start_date,trunc(p_end_date,'Y')))
8040: ,p_action_information3 => fnd_date.date_to_canonical(p_end_date)
8041: ,p_action_information4 => csr_action_info_rec.action_information4
8042: ,p_action_information5 => csr_action_info_rec.action_information5
8043: ,p_action_information6 => csr_action_info_rec.action_information6
8044: ,p_action_information7 => csr_action_info_rec.action_information7

Line 8103: ,p_action_information5 => fnd_date.date_to_canonical(l_date) --fnd_date.date_to_canonical(p_start_date)

8099: ,p_action_information1 => 'INITIAL'
8100: ,p_action_information2 => fnd_number.number_to_canonical(l_master_action_info_id)
8101: ,p_action_information3 => csr_ip_info_rec.action_information3
8102: ,p_action_information4 => csr_ip_info_rec.action_information4
8103: ,p_action_information5 => fnd_date.date_to_canonical(l_date) --fnd_date.date_to_canonical(p_start_date)
8104: ,p_action_information6 => csr_ip_info_rec.action_information6
8105: ,p_action_information7 => csr_ip_info_rec.action_information7
8106: ,p_action_information8 => csr_ip_info_rec.action_information8
8107: ,p_action_information9 => csr_ip_info_rec.action_information9

Line 8148: ,p_action_information5 => fnd_date.date_to_canonical(l_date)--fnd_date.date_to_canonical(p_start_date)

8144: ,p_tax_unit_id => l_tax_unit_id
8145: ,p_action_information_category => 'NL_WR_SWMF_SECTOR_RISK_GROUP'
8146: ,p_action_information1 => 'SECTOR_RISK_GROUP'
8147: ,p_action_information2 => fnd_number.number_to_canonical(l_master_action_info_id)
8148: ,p_action_information5 => fnd_date.date_to_canonical(l_date)--fnd_date.date_to_canonical(p_start_date)
8149: ,p_action_information6 => csr_srg_info_rec.action_information6--fnd_date.date_to_canonical(p_end_date)
8150: ,p_action_information7 => csr_srg_info_rec.action_information7
8151: ,p_action_information8 => csr_srg_info_rec.action_information8
8152: ,p_action_information9 => csr_srg_info_rec.action_information9

Line 8149: ,p_action_information6 => csr_srg_info_rec.action_information6--fnd_date.date_to_canonical(p_end_date)

8145: ,p_action_information_category => 'NL_WR_SWMF_SECTOR_RISK_GROUP'
8146: ,p_action_information1 => 'SECTOR_RISK_GROUP'
8147: ,p_action_information2 => fnd_number.number_to_canonical(l_master_action_info_id)
8148: ,p_action_information5 => fnd_date.date_to_canonical(l_date)--fnd_date.date_to_canonical(p_start_date)
8149: ,p_action_information6 => csr_srg_info_rec.action_information6--fnd_date.date_to_canonical(p_end_date)
8150: ,p_action_information7 => csr_srg_info_rec.action_information7
8151: ,p_action_information8 => csr_srg_info_rec.action_information8
8152: ,p_action_information9 => csr_srg_info_rec.action_information9
8153: ,p_action_information10 => csr_srg_info_rec.action_information10);

Line 8706: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(p_chk_start_date,trunc(p_chk_end_date,'Y')))

8702: ,p_source_text => NULL
8703: ,p_tax_unit_id => p_tax_unit_id
8704: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
8705: ,p_action_information1 => l_type
8706: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(p_chk_start_date,trunc(p_chk_end_date,'Y')))
8707: ,p_action_information3 => fnd_date.date_to_canonical(p_chk_end_date)
8708: ,p_action_information4 => l_emp_rec.assignment_number
8709: ,p_action_information5 => l_emp_rec.employee_number
8710: ,p_action_information6 => l_emp_rec.change_reason

Line 8707: ,p_action_information3 => fnd_date.date_to_canonical(p_chk_end_date)

8703: ,p_tax_unit_id => p_tax_unit_id
8704: ,p_action_information_category => 'NL_WR_EMPLOYMENT_INFO'
8705: ,p_action_information1 => l_type
8706: ,p_action_information2 => fnd_date.date_to_canonical(GREATEST(p_chk_start_date,trunc(p_chk_end_date,'Y')))
8707: ,p_action_information3 => fnd_date.date_to_canonical(p_chk_end_date)
8708: ,p_action_information4 => l_emp_rec.assignment_number
8709: ,p_action_information5 => l_emp_rec.employee_number
8710: ,p_action_information6 => l_emp_rec.change_reason
8711: ,p_action_information8 => l_emp_rec.sofi_number

Line 8716: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)

8712: ,p_action_information7 => l_emp_rec.person_id
8713: ,p_action_information9 => NULL -- l_emp_rec.Initials
8714: ,p_action_information10 => NULL -- l_emp_rec.prefix
8715: ,p_action_information11 => l_emp_rec.last_name
8716: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
8717: ,p_action_information13 => NULL -- l_nationality
8718: ,p_action_information14 => NULL -- l_emp_rec.gender
8719: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
8720: ,p_action_information16 => fnd_date.date_to_canonical(l_ass_end_date)

Line 8719: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date

8715: ,p_action_information11 => l_emp_rec.last_name
8716: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
8717: ,p_action_information13 => NULL -- l_nationality
8718: ,p_action_information14 => NULL -- l_emp_rec.gender
8719: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
8720: ,p_action_information16 => fnd_date.date_to_canonical(l_ass_end_date)
8721: ,p_action_information17 => NULL
8722: ,p_action_information18 => l_emp_seq);
8723:

Line 8720: ,p_action_information16 => fnd_date.date_to_canonical(l_ass_end_date)

8716: ,p_action_information12 => fnd_date.date_to_canonical(l_emp_rec.dob)
8717: ,p_action_information13 => NULL -- l_nationality
8718: ,p_action_information14 => NULL -- l_emp_rec.gender
8719: ,p_action_information15 => fnd_date.date_to_canonical(l_ass_start_date)-- assignment_start_date
8720: ,p_action_information16 => fnd_date.date_to_canonical(l_ass_end_date)
8721: ,p_action_information17 => NULL
8722: ,p_action_information18 => l_emp_seq);
8723:
8724: IF g_contract_code_mapping = 'NL_EMPLOYMENT_CATG' THEN

Line 8826: , p_action_information5 => fnd_date.date_to_canonical(GREATEST(GREATEST(p_chk_start_date,l_ass_start_date),trunc(p_chk_end_date,'Y'))) /* EOY 0708 ...Start Date Income Peiod */

8822: , p_action_information_category => 'NL_WR_INCOME_PERIOD'
8823: , p_tax_unit_id => p_tax_unit_id
8824: , p_action_information1 => l_type
8825: , p_action_information2 => l_master_action_info_id
8826: , p_action_information5 => fnd_date.date_to_canonical(GREATEST(GREATEST(p_chk_start_date,l_ass_start_date),trunc(p_chk_end_date,'Y'))) /*** EOY 0708 ...Start Date Income Peiod ***/
8827: , p_action_information6 => l_income_code
8828: , p_action_information7 => l_labour_rel_code
8829: , p_action_information8 => l_ins_duty_code
8830: , p_action_information9 => l_assignment_catg

Line 9013: l_return_string := rpad(fnd_date.date_to_displaydate(l_start_date),15)||'- '||rpad(fnd_date.date_to_displaydate(l_end_date),15);

9009: OPEN csr_chk_exception(p_actid);
9010: FETCH csr_chk_exception INTO l_exception;
9011: CLOSE csr_chk_exception;
9012: --
9013: l_return_string := rpad(fnd_date.date_to_displaydate(l_start_date),15)||'- '||rpad(fnd_date.date_to_displaydate(l_end_date),15);
9014: l_return_string := l_return_string ||'- '||rpad(HR_GENERAL.decode_lookup('NL_WR_PERIOD_TYPE',l_payroll_type),40);
9015: l_return_string := l_return_string ||'- '||rpad(l_tax_rep_name,40);
9016: l_return_string := l_return_string ||'- '||rpad(HR_GENERAL.decode_lookup('NL_FORM_LABELS',l_exception),25);
9017: --