[Home] [Help]
33: l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
34: l_proc_name varchar2(150) := g_proc_name ||'Get_Balance_Value';
35: l_error varchar2(500);
36: Begin
37: hr_utility.set_location('Entering: '||l_proc_name, 5);
38: Open csr_defined_bal ( c_balance_name => p_balance_name
39: ,c_dimension_name => p_dimension_name
40: ,c_business_group_id => p_business_group_id);
41: Fetch csr_defined_bal Into l_defined_balance_id;
40: ,c_business_group_id => p_business_group_id);
41: Fetch csr_defined_bal Into l_defined_balance_id;
42:
43: If csr_defined_bal%NOTFOUND Then
44: hr_utility.set_location('.......Could not find the defined balance for :'||p_balance_name, 10);
45: Close csr_defined_bal;
46: l_balance_amount := 0;
47: Else
48: hr_utility.set_location('.......Balance :'||p_balance_name||' and dimension :'||
44: hr_utility.set_location('.......Could not find the defined balance for :'||p_balance_name, 10);
45: Close csr_defined_bal;
46: l_balance_amount := 0;
47: Else
48: hr_utility.set_location('.......Balance :'||p_balance_name||' and dimension :'||
49: p_dimension_name||' valid..',15);
50: Close csr_defined_bal;
51: hr_utility.set_location('.......Before Calling pay_balance_pkg.get_value function..',20);
52: pay_balance_pkg.set_context('tax_unit_id', p_business_group_id);
47: Else
48: hr_utility.set_location('.......Balance :'||p_balance_name||' and dimension :'||
49: p_dimension_name||' valid..',15);
50: Close csr_defined_bal;
51: hr_utility.set_location('.......Before Calling pay_balance_pkg.get_value function..',20);
52: pay_balance_pkg.set_context('tax_unit_id', p_business_group_id);
53: pay_balance_pkg.set_context('date_earned', p_effective_date);
54: l_balance_amount := pay_balance_pkg.get_value(l_defined_balance_id,
55: p_assignment_id,
54: l_balance_amount := pay_balance_pkg.get_value(l_defined_balance_id,
55: p_assignment_id,
56: p_effective_date);
57: End If;
58: hr_utility.set_location('.......Leaving: '||l_proc_name, 25);
59: Return nvl(l_balance_amount,0);
60: Exception
61: When Others Then
62: l_error := SQLERRM;
59: Return nvl(l_balance_amount,0);
60: Exception
61: When Others Then
62: l_error := SQLERRM;
63: hr_utility.set_location('.......When Others error raised at Get_Balance_Value', 25);
64: hr_utility.set_location('.......SQL-ERRM :'||l_error, 27);
65: hr_utility.set_location('Leaving: '||l_proc_name, 30);
66: Raise;
67: End Get_Balance_Value;
60: Exception
61: When Others Then
62: l_error := SQLERRM;
63: hr_utility.set_location('.......When Others error raised at Get_Balance_Value', 25);
64: hr_utility.set_location('.......SQL-ERRM :'||l_error, 27);
65: hr_utility.set_location('Leaving: '||l_proc_name, 30);
66: Raise;
67: End Get_Balance_Value;
68:
61: When Others Then
62: l_error := SQLERRM;
63: hr_utility.set_location('.......When Others error raised at Get_Balance_Value', 25);
64: hr_utility.set_location('.......SQL-ERRM :'||l_error, 27);
65: hr_utility.set_location('Leaving: '||l_proc_name, 30);
66: Raise;
67: End Get_Balance_Value;
68:
69: -- =============================================================================
77: l_error varchar2(500);
78: -- nocopy changes
79: l_header_name_nc varchar2(1000);
80: Begin
81: hr_utility.set_location('Entering: '||l_proc_name, 5);
82:
83: -- nocopy changes tmehra
84:
85: l_header_name_nc := p_header_name;
106: l_header_name := g_payroll_frequency;
107: End If;
108:
109: p_header_name := l_header_name;
110: hr_utility.set_location('Leaving: '||l_proc_name, 45);
111: Return 0;
112: Exception
113: When Others Then
114: l_error := SQLERRM;
111: Return 0;
112: Exception
113: When Others Then
114: l_error := SQLERRM;
115: hr_utility.set_location('.......Exception Others Raised at Get_Header_Information',40);
116: hr_utility.set_location('.......SQL-ERRM :'||l_error, 42);
117: hr_utility.set_location('Leaving: '||l_proc_name, 45);
118: -- nocopy changes tmehra
119: p_header_name := l_header_name_nc;
112: Exception
113: When Others Then
114: l_error := SQLERRM;
115: hr_utility.set_location('.......Exception Others Raised at Get_Header_Information',40);
116: hr_utility.set_location('.......SQL-ERRM :'||l_error, 42);
117: hr_utility.set_location('Leaving: '||l_proc_name, 45);
118: -- nocopy changes tmehra
119: p_header_name := l_header_name_nc;
120: Return -1;
113: When Others Then
114: l_error := SQLERRM;
115: hr_utility.set_location('.......Exception Others Raised at Get_Header_Information',40);
116: hr_utility.set_location('.......SQL-ERRM :'||l_error, 42);
117: hr_utility.set_location('Leaving: '||l_proc_name, 45);
118: -- nocopy changes tmehra
119: p_header_name := l_header_name_nc;
120: Return -1;
121: End Get_Header_Information;
143: l_error varchar2(500);
144: -- nocopy changes tmehra
145: l_payroll_name_nc pay_payrolls_f.payroll_name%TYPE;
146: Begin
147: hr_utility.set_location('Entering: '||l_proc_name, 5);
148:
149: -- nocopy changes tmehra
150:
151: l_payroll_name_nc := p_payroll_name;
151: l_payroll_name_nc := p_payroll_name;
152:
153: If g_primary_asg.EXISTS(p_assignment_id) Then
154:
155: hr_utility.set_location('.......Found the pri. assign in g_primary_asg PL/SQL table..',10);
156: l_payroll_id := g_primary_asg(p_assignment_id).payroll_id;
157:
158: Elsif g_all_sec_asgs.EXISTS(p_assignment_id) Then
159:
156: l_payroll_id := g_primary_asg(p_assignment_id).payroll_id;
157:
158: Elsif g_all_sec_asgs.EXISTS(p_assignment_id) Then
159:
160: hr_utility.set_location('.......Found the secondary assign in g_all_sec_asgs PL/SQL table..',15);
161: l_payroll_id := g_all_sec_asgs(p_assignment_id).payroll_id;
162:
163: End If;
164:
171: ,c_effective_date => g_effective_date);
172: Fetch csr_payroll_name Into l_payroll_name,l_period_type;
173: close csr_payroll_name;
174:
175: hr_utility.set_location('.......Payroll Name :'||l_payroll_name,20);
176: hr_utility.set_location('.......Payroll Frequency :'||l_period_type,25);
177:
178: g_payroll_names(p_assignment_id).payroll_name := l_payroll_name;
179: g_payroll_names(p_assignment_id).period_type := l_period_type;
172: Fetch csr_payroll_name Into l_payroll_name,l_period_type;
173: close csr_payroll_name;
174:
175: hr_utility.set_location('.......Payroll Name :'||l_payroll_name,20);
176: hr_utility.set_location('.......Payroll Frequency :'||l_period_type,25);
177:
178: g_payroll_names(p_assignment_id).payroll_name := l_payroll_name;
179: g_payroll_names(p_assignment_id).period_type := l_period_type;
180: g_payroll_names(p_assignment_id).payroll_start_date := l_start_date;
182: g_payroll_names(p_assignment_id).actual_pay_date := Null;
183: p_payroll_name := l_period_type;
184: g_payroll_frequency := l_period_type;
185:
186: hr_utility.set_location('Leaving: '||l_proc_name, 35);
187: Return 0;
188:
189: Exception
190: When Others Then
188:
189: Exception
190: When Others Then
191: l_error := SQLERRM;
192: hr_utility.set_location('.......Exception when others raised at Get_Payroll_Name', 30);
193: hr_utility.set_location('.......SQL-ERRM :'||l_error, 33);
194: hr_utility.set_location('Leaving: '||l_proc_name, 35);
195: -- nocopy changes tmehra
196: p_payroll_name := l_payroll_name_nc;
189: Exception
190: When Others Then
191: l_error := SQLERRM;
192: hr_utility.set_location('.......Exception when others raised at Get_Payroll_Name', 30);
193: hr_utility.set_location('.......SQL-ERRM :'||l_error, 33);
194: hr_utility.set_location('Leaving: '||l_proc_name, 35);
195: -- nocopy changes tmehra
196: p_payroll_name := l_payroll_name_nc;
197: Return -1;
190: When Others Then
191: l_error := SQLERRM;
192: hr_utility.set_location('.......Exception when others raised at Get_Payroll_Name', 30);
193: hr_utility.set_location('.......SQL-ERRM :'||l_error, 33);
194: hr_utility.set_location('Leaving: '||l_proc_name, 35);
195: -- nocopy changes tmehra
196: p_payroll_name := l_payroll_name_nc;
197: Return -1;
198: End Get_Payroll_Name;
204: ,p_start_date In out nocopy Varchar2) Return Number Is
205: l_proc_name varchar2(150) := g_proc_name ||'.Get_Payroll_Start_Date';
206: l_date_nc varchar2(20);
207: Begin
208: hr_utility.set_location('Entering: '||l_proc_name, 5);
209: -- nocopy changes tmehra
210: l_date_nc := p_start_date;
211: If g_extract_start_date Is not Null Then
212: p_start_date := fnd_date.date_to_canonical(g_extract_start_date);
209: -- nocopy changes tmehra
210: l_date_nc := p_start_date;
211: If g_extract_start_date Is not Null Then
212: p_start_date := fnd_date.date_to_canonical(g_extract_start_date);
213: hr_utility.set_location('.......Period Start Date :'||p_start_date,10);
214: End If;
215: hr_utility.set_location('Leaving: '||l_proc_name, 15);
216: Return 0;
217: -- nocopy changes tmehra
211: If g_extract_start_date Is not Null Then
212: p_start_date := fnd_date.date_to_canonical(g_extract_start_date);
213: hr_utility.set_location('.......Period Start Date :'||p_start_date,10);
214: End If;
215: hr_utility.set_location('Leaving: '||l_proc_name, 15);
216: Return 0;
217: -- nocopy changes tmehra
218: EXCEPTION
219: WHEN OTHERS THEN
216: Return 0;
217: -- nocopy changes tmehra
218: EXCEPTION
219: WHEN OTHERS THEN
220: hr_utility.set_location('EXCEPTION :'||l_proc_name,20);
221: p_start_date := l_date_nc;
222: RETURN -1;
223:
224: End Get_Payroll_Start_Date;
231: l_proc_name varchar2(150) := g_proc_name ||'.Get_Payroll_End_Date';
232: -- nocopy changes tmehra
233: l_date_nc varchar2(20);
234: Begin
235: hr_utility.set_location('Entering: '||l_proc_name, 5);
236: -- nocopy changes tmehra
237: l_date_nc := p_end_date;
238: If g_extract_end_date Is not Null Then
239: p_end_date := fnd_date.date_to_canonical(g_extract_end_date);
236: -- nocopy changes tmehra
237: l_date_nc := p_end_date;
238: If g_extract_end_date Is not Null Then
239: p_end_date := fnd_date.date_to_canonical(g_extract_end_date);
240: hr_utility.set_location('.......Period End Date :'||p_end_date,10);
241: End If;
242: hr_utility.set_location('Leaving: '||l_proc_name, 15);
243: Return 0;
244: -- nocopy changes tmehra
238: If g_extract_end_date Is not Null Then
239: p_end_date := fnd_date.date_to_canonical(g_extract_end_date);
240: hr_utility.set_location('.......Period End Date :'||p_end_date,10);
241: End If;
242: hr_utility.set_location('Leaving: '||l_proc_name, 15);
243: Return 0;
244: -- nocopy changes tmehra
245: EXCEPTION
246: WHEN OTHERS THEN
243: Return 0;
244: -- nocopy changes tmehra
245: EXCEPTION
246: WHEN OTHERS THEN
247: hr_utility.set_location('EXCEPTION :'||l_proc_name,20);
248: p_end_date := l_date_nc;
249: RETURN -1;
250:
251: End Get_Payroll_End_Date;
276: l_pay_date date;
277: l_effective_date date;
278: Begin
279:
280: hr_utility.set_location('Entering: '||l_proc_name, 5);
281: -- nocopy changes tmehra
282: l_date_nc := p_pay_date;
283:
284: l_effective_date := g_effective_date;
295: If csr_pay_date%FOUND Then
296: close csr_pay_date;
297: g_extract_pay_date := l_pay_date;
298: p_pay_date := l_pay_date;
299: hr_utility.set_location('.......Period Actual Pay Date :'||p_pay_date,10);
300: Else
301: close csr_pay_date;
302: End If;
303: End If;
301: close csr_pay_date;
302: End If;
303: End If;
304:
305: hr_utility.set_location('Leaving: '||l_proc_name, 15);
306:
307: Return 0;
308: Exception
309: When Others Then
306:
307: Return 0;
308: Exception
309: When Others Then
310: hr_utility.set_location('.....Exception When Others raised at Get_Actual_Pay_Date',10);
311: hr_utility.set_location('Leaving: '||l_proc_name, 15);
312: p_pay_date := l_date_nc;
313: Return -1;
314: End Get_Actual_Pay_Date;
307: Return 0;
308: Exception
309: When Others Then
310: hr_utility.set_location('.....Exception When Others raised at Get_Actual_Pay_Date',10);
311: hr_utility.set_location('Leaving: '||l_proc_name, 15);
312: p_pay_date := l_date_nc;
313: Return -1;
314: End Get_Actual_Pay_Date;
315:
322: --Nocopy changes
323: l_srs_plan_name_nc varchar2(200);
324:
325: Begin
326: hr_utility.set_location('Entering: '||l_proc_name, 5);
327:
328: -- nocopy changes
329: l_srs_plan_name_nc := p_srs_plan_name;
330:
329: l_srs_plan_name_nc := p_srs_plan_name;
330:
331: If g_extract_plan_name Is Not Null Then
332: p_SRS_Plan_Name := g_extract_plan_name;
333: hr_utility.set_location('.......Extract Plan Name :'||p_SRS_Plan_Name,10);
334: End If;
335: hr_utility.set_location('Leaving: '||l_proc_name, 15);
336: Return 0;
337: Exception
331: If g_extract_plan_name Is Not Null Then
332: p_SRS_Plan_Name := g_extract_plan_name;
333: hr_utility.set_location('.......Extract Plan Name :'||p_SRS_Plan_Name,10);
334: End If;
335: hr_utility.set_location('Leaving: '||l_proc_name, 15);
336: Return 0;
337: Exception
338: When Others Then
339: -- nocopy changes,
353: -- nocopy changes
354: l_separation_date_nc varchar2(20);
355: Begin
356:
357: hr_utility.set_location('Entering: '||l_proc_name, 5);
358:
359: -- nocopy changes
360: l_separation_date_nc := p_Separation_Date;
361:
368: l_separation_date := Least(l_assig_end_date,g_plan_end_date);
369:
370: If l_separation_date < to_date('31/12/4712','DD/MM/YYYY') Then
371: p_Separation_Date := l_separation_date;
372: hr_utility.set_location('.......Separation date :'||p_Separation_date,10);
373: End If;
374:
375: hr_utility.set_location('Leaving: '||l_proc_name, 15);
376:
371: p_Separation_Date := l_separation_date;
372: hr_utility.set_location('.......Separation date :'||p_Separation_date,10);
373: End If;
374:
375: hr_utility.set_location('Leaving: '||l_proc_name, 15);
376:
377: Return 0;
378: Exception
379: When Others Then
401: -- nocopy changes
402: l_status_code_nc varchar2(100);
403:
404: Begin
405: hr_utility.set_location('Entering: '||l_proc_name, 5);
406: -- nocopy changes
407: l_status_code_nc := p_status_code;
408:
409: If g_primary_asg.EXISTS(p_assignment_id) Then
418: Fetch cur_status_name Into l_assig_status;
419: Close cur_status_name;
420: p_status_code := l_assig_status;
421: End If;
422: hr_utility.set_location('.......Assign Status :'|| p_status_code, 10);
423: hr_utility.set_location('Leaving: '||l_proc_name, 15);
424: Return 0;
425: Exception
426: When Others Then
419: Close cur_status_name;
420: p_status_code := l_assig_status;
421: End If;
422: hr_utility.set_location('.......Assign Status :'|| p_status_code, 10);
423: hr_utility.set_location('Leaving: '||l_proc_name, 15);
424: Return 0;
425: Exception
426: When Others Then
427: l_error := SQLERRM;
424: Return 0;
425: Exception
426: When Others Then
427: l_error := SQLERRM;
428: hr_utility.set_location('.......Exception When Others Raised In Get_Assig_Status',13);
429: hr_utility.set_location('.......SQL-ERRM :'||l_error, 14);
430: hr_utility.set_location('Leaving: '||l_proc_name, 15);
431: p_status_code := l_status_code_nc;
432: Return -1;
425: Exception
426: When Others Then
427: l_error := SQLERRM;
428: hr_utility.set_location('.......Exception When Others Raised In Get_Assig_Status',13);
429: hr_utility.set_location('.......SQL-ERRM :'||l_error, 14);
430: hr_utility.set_location('Leaving: '||l_proc_name, 15);
431: p_status_code := l_status_code_nc;
432: Return -1;
433:
426: When Others Then
427: l_error := SQLERRM;
428: hr_utility.set_location('.......Exception When Others Raised In Get_Assig_Status',13);
429: hr_utility.set_location('.......SQL-ERRM :'||l_error, 14);
430: hr_utility.set_location('Leaving: '||l_proc_name, 15);
431: p_status_code := l_status_code_nc;
432: Return -1;
433:
434: End Get_Assig_Status;
442: l_proc_name varchar2(150) := g_proc_name ||'.Get_Person_Indentifier';
443: -- nocopy changes
444: l_person_identifier_nc varchar2(100);
445: Begin
446: hr_utility.set_location('Entering: '||l_proc_name, 5);
447: l_person_identifier_nc := p_person_identifier;
448: p_person_identifier := g_plan_person_identifier;
449: hr_utility.set_location('.......Person Plan Id :'||p_person_identifier,10);
450: hr_utility.set_location('Leaving: '||l_proc_name, 15);
445: Begin
446: hr_utility.set_location('Entering: '||l_proc_name, 5);
447: l_person_identifier_nc := p_person_identifier;
448: p_person_identifier := g_plan_person_identifier;
449: hr_utility.set_location('.......Person Plan Id :'||p_person_identifier,10);
450: hr_utility.set_location('Leaving: '||l_proc_name, 15);
451: Return 0;
452: Exception
453: When Others Then
446: hr_utility.set_location('Entering: '||l_proc_name, 5);
447: l_person_identifier_nc := p_person_identifier;
448: p_person_identifier := g_plan_person_identifier;
449: hr_utility.set_location('.......Person Plan Id :'||p_person_identifier,10);
450: hr_utility.set_location('Leaving: '||l_proc_name, 15);
451: Return 0;
452: Exception
453: When Others Then
454: p_person_identifier := l_person_identifier_nc;
487: l_tax_id per_all_assignments_f.organization_id%TYPE;
488: l_business_group_id per_all_assignments_f.business_group_id%TYPE;
489:
490: Begin
491: hr_utility.set_location('Entering: '||l_proc_name, 5);
492:
493: l_dimension_name := g_dimension_name;
494: hr_utility.set_location('.....Dimension Name:' ||l_dimension_name,10);
495:
490: Begin
491: hr_utility.set_location('Entering: '||l_proc_name, 5);
492:
493: l_dimension_name := g_dimension_name;
494: hr_utility.set_location('.....Dimension Name:' ||l_dimension_name,10);
495:
496: If g_primary_asg.EXISTS(p_assignment_id) Then
497: l_effective_date := Least(g_primary_asg(p_assignment_id).effective_end_date,g_effective_date);
498: ElsIf g_all_sec_asgs.EXISTS(p_assignment_id) Then
502: Open c_tax_id ( c_assignment_id => p_assignment_id
503: ,c_effective_date => l_effective_date);
504: Fetch c_tax_id Into l_tax_id, l_business_group_id;
505: If c_tax_id%NOTFOUND Then
506: hr_utility.set_location('.....Gre Tax Unit Id Notfound',6);
507: End If;
508: Close c_tax_id;
509:
510: hr_utility.set_location('.....Effective date :'||l_effective_date,15);
506: hr_utility.set_location('.....Gre Tax Unit Id Notfound',6);
507: End If;
508: Close c_tax_id;
509:
510: hr_utility.set_location('.....Effective date :'||l_effective_date,15);
511: hr_utility.set_location('.....GRE l_tax_id :'||l_tax_id,20);
512:
513: If Trim(Upper(p_balance_name)) = Trim(Upper(g_eligible_comp_balance_C)) Then
514: -- Eligible Comp Balance
507: End If;
508: Close c_tax_id;
509:
510: hr_utility.set_location('.....Effective date :'||l_effective_date,15);
511: hr_utility.set_location('.....GRE l_tax_id :'||l_tax_id,20);
512:
513: If Trim(Upper(p_balance_name)) = Trim(Upper(g_eligible_comp_balance_C)) Then
514: -- Eligible Comp Balance
515: l_balance_name := g_eligible_comp_balance;
534: -- Balance Name other than the SRS Balances
535: l_balance_name := Trim(p_balance_name);
536: End If;
537:
538: hr_utility.set_location('.....Balance Name :'|| l_balance_name,15);
539:
540: Open csr_defined_bal ( c_balance_name => l_balance_name
541: ,c_dimension_name => l_dimension_name
542: ,c_business_group_id => l_business_group_id);
541: ,c_dimension_name => l_dimension_name
542: ,c_business_group_id => l_business_group_id);
543: Fetch csr_defined_bal Into l_defined_balance_id;
544: If csr_defined_bal%NOTFOUND Then
545: hr_utility.set_location('.....Defined Balance Id NOT found for :'||l_balance_name,20);
546: Close csr_defined_bal;
547: l_return_value := 0;
548: --l_return_value := -1;
549: Else
555: p_assignment_id,
556: l_effective_date);
557: End If;
558: p_balance_amount := nvl(l_balance_amount,0);
559: hr_utility.set_location('Leaving: '||l_proc_name, 45);
560: Return l_return_value;
561: Exception
562: When Others Then
563: p_balance_amount := nvl(l_balance_amount,0);
561: Exception
562: When Others Then
563: p_balance_amount := nvl(l_balance_amount,0);
564: l_error := sqlerrm;
565: hr_utility.set_location('.....Exception Occured at Get_SRS_Deduction_Balances',30);
566: hr_utility.set_location('.....SQL-ERRM :'||l_error, 35);
567: hr_utility.set_location('Leaving: '||l_proc_name, 45);
568: Return nvl(l_return_value,0);
569: End Get_SRS_Deduction_Balances;
562: When Others Then
563: p_balance_amount := nvl(l_balance_amount,0);
564: l_error := sqlerrm;
565: hr_utility.set_location('.....Exception Occured at Get_SRS_Deduction_Balances',30);
566: hr_utility.set_location('.....SQL-ERRM :'||l_error, 35);
567: hr_utility.set_location('Leaving: '||l_proc_name, 45);
568: Return nvl(l_return_value,0);
569: End Get_SRS_Deduction_Balances;
570:
563: p_balance_amount := nvl(l_balance_amount,0);
564: l_error := sqlerrm;
565: hr_utility.set_location('.....Exception Occured at Get_SRS_Deduction_Balances',30);
566: hr_utility.set_location('.....SQL-ERRM :'||l_error, 35);
567: hr_utility.set_location('Leaving: '||l_proc_name, 45);
568: Return nvl(l_return_value,0);
569: End Get_SRS_Deduction_Balances;
570:
571: -- ===============================================================================
582: e_ptd_dates_notfound Exception;
583: l_proc_name varchar2(150) := g_proc_name ||'.Get_PTD_Start_End_Date';
584: l_error varchar2(500);
585: Begin
586: hr_utility.set_location('Entering: '||l_proc_name, 5);
587: --
588: -- Get the current payroll id for the assignment
589: --
590: Open csr_get_payroll_id ( c_assignment_id => p_assignment_id
590: Open csr_get_payroll_id ( c_assignment_id => p_assignment_id
591: ,c_effective_date => p_effective_date);
592: Fetch csr_get_payroll_id Into l_assig_rec;
593: If csr_get_payroll_id%NOTFOUND Then
594: hr_utility.set_location('.......Payroll Id not found for assignment id :'||p_assignment_id,10);
595: Close csr_get_payroll_id;
596: l_return_value := 'S0'; -- Failed to get the payroll id for the assignment
597: Raise e_ptd_dates_notfound;
598: Else
595: Close csr_get_payroll_id;
596: l_return_value := 'S0'; -- Failed to get the payroll id for the assignment
597: Raise e_ptd_dates_notfound;
598: Else
599: hr_utility.set_location('.......Payroll Id found in assignment id :'||p_assignment_id,10);
600: Close csr_get_payroll_id;
601: End If;
602: --
603: -- Get the time-period start and end date based on the effective date
606: ,c_effective_date => p_effective_date
607: );
608: Fetch csr_time_period Into l_time_period_rec;
609: If csr_time_period%NOTFOUND Then
610: hr_utility.set_location('.......Time period not found for payroll id :'||l_assig_rec.payroll_id,15);
611: Close csr_time_period;
612: l_return_value := 'S1'; -- Failed to get time-period as of the effective date;
613: Raise e_ptd_dates_notfound;
614: Else
611: Close csr_time_period;
612: l_return_value := 'S1'; -- Failed to get time-period as of the effective date;
613: Raise e_ptd_dates_notfound;
614: Else
615: hr_utility.set_location('.......Time period found for payroll id :'||l_assig_rec.payroll_id,15);
616: Close csr_time_period;
617: End If;
618:
619: g_extract_start_date := l_time_period_rec.start_date;
617: End If;
618:
619: g_extract_start_date := l_time_period_rec.start_date;
620: g_extract_end_date := l_time_period_rec.end_date;
621: hr_utility.set_location('.......PTD Start date :'|| g_extract_start_date,20);
622: hr_utility.set_location('.......PTD End date :'|| g_extract_start_date,25);
623: hr_utility.set_location('Leaving: '||l_proc_name, 45);
624: Return l_return_value;
625: Exception
618:
619: g_extract_start_date := l_time_period_rec.start_date;
620: g_extract_end_date := l_time_period_rec.end_date;
621: hr_utility.set_location('.......PTD Start date :'|| g_extract_start_date,20);
622: hr_utility.set_location('.......PTD End date :'|| g_extract_start_date,25);
623: hr_utility.set_location('Leaving: '||l_proc_name, 45);
624: Return l_return_value;
625: Exception
626: When e_ptd_dates_notfound Then
619: g_extract_start_date := l_time_period_rec.start_date;
620: g_extract_end_date := l_time_period_rec.end_date;
621: hr_utility.set_location('.......PTD Start date :'|| g_extract_start_date,20);
622: hr_utility.set_location('.......PTD End date :'|| g_extract_start_date,25);
623: hr_utility.set_location('Leaving: '||l_proc_name, 45);
624: Return l_return_value;
625: Exception
626: When e_ptd_dates_notfound Then
627: hr_utility.set_location('.......Exception e_ptd_dates_notfound raised in Get_PTD_Start_End_Date',20);
623: hr_utility.set_location('Leaving: '||l_proc_name, 45);
624: Return l_return_value;
625: Exception
626: When e_ptd_dates_notfound Then
627: hr_utility.set_location('.......Exception e_ptd_dates_notfound raised in Get_PTD_Start_End_Date',20);
628: hr_utility.set_location('Leaving: '||l_proc_name, 45);
629: Return l_return_value;
630: When Others Then
631: l_error := SQLERRM;
624: Return l_return_value;
625: Exception
626: When e_ptd_dates_notfound Then
627: hr_utility.set_location('.......Exception e_ptd_dates_notfound raised in Get_PTD_Start_End_Date',20);
628: hr_utility.set_location('Leaving: '||l_proc_name, 45);
629: Return l_return_value;
630: When Others Then
631: l_error := SQLERRM;
632: hr_utility.set_location('.......Exception Others raised in Get_PTD_Start_End_Date',20);
628: hr_utility.set_location('Leaving: '||l_proc_name, 45);
629: Return l_return_value;
630: When Others Then
631: l_error := SQLERRM;
632: hr_utility.set_location('.......Exception Others raised in Get_PTD_Start_End_Date',20);
633: hr_utility.set_location('.......SQL-ERRM :'||l_error, 35);
634: hr_utility.set_location('Leaving: '||l_proc_name, 45);
635: l_return_value :='S2';
636: Return l_return_value;
629: Return l_return_value;
630: When Others Then
631: l_error := SQLERRM;
632: hr_utility.set_location('.......Exception Others raised in Get_PTD_Start_End_Date',20);
633: hr_utility.set_location('.......SQL-ERRM :'||l_error, 35);
634: hr_utility.set_location('Leaving: '||l_proc_name, 45);
635: l_return_value :='S2';
636: Return l_return_value;
637:
630: When Others Then
631: l_error := SQLERRM;
632: hr_utility.set_location('.......Exception Others raised in Get_PTD_Start_End_Date',20);
633: hr_utility.set_location('.......SQL-ERRM :'||l_error, 35);
634: hr_utility.set_location('Leaving: '||l_proc_name, 45);
635: l_return_value :='S2';
636: Return l_return_value;
637:
638: End Get_PTD_Start_End_Date;
649: l_return_value varchar2(5) :='T';
650: l_proc_name varchar2(150) := g_proc_name ||'Get_Plan_Names';
651: l_error varchar2(500);
652: Begin
653: hr_utility.set_location('Entering: '||l_proc_name, 5);
654: If p_extract_name is Not Null Then
655: l_extract_names := p_extract_name;
656: Else
657: l_return_value := 'P0'; --Global Value For Extract not defined for: SRS_PLAN_NAME
666: Loop
667: l_position := instr(l_extract_names,',');
668: If l_position = 0 Then
669: g_extract_plan_names(l_count).plan_name := Trim(l_extract_names);
670: hr_utility.set_location('......Extract Name Found in Global Value :'||g_extract_plan_names(l_count).plan_name,20);
671: l_return_value := 'T';
672: Exit;
673: Else
674: l_plan_name := Trim(substr(l_extract_names,1,l_position-1));
672: Exit;
673: Else
674: l_plan_name := Trim(substr(l_extract_names,1,l_position-1));
675: g_extract_plan_names(l_count).plan_name := l_plan_name;
676: hr_utility.set_location('.......Extract Name Found in Global Value :'||g_extract_plan_names(l_count).plan_name,20);
677: l_extract_names := substr(l_extract_names,l_position+1);
678: l_count := l_count + 1;
679: End If;
680: End loop;
678: l_count := l_count + 1;
679: End If;
680: End loop;
681: End If;
682: hr_utility.set_location('Leaving: '||l_proc_name, 45);
683: Return l_return_value;
684: Exception
685: When Others Then
686: hr_utility.set_location('.......When Others error in Get_Plan_Names',25);
682: hr_utility.set_location('Leaving: '||l_proc_name, 45);
683: Return l_return_value;
684: Exception
685: When Others Then
686: hr_utility.set_location('.......When Others error in Get_Plan_Names',25);
687: l_error := sqlerrm;
688: hr_utility.set_location('.......SQL-ERRM :'||l_error,40);
689: hr_utility.set_location('Leaving: '||l_proc_name, 45);
690: l_return_value := 'P2';
684: Exception
685: When Others Then
686: hr_utility.set_location('.......When Others error in Get_Plan_Names',25);
687: l_error := sqlerrm;
688: hr_utility.set_location('.......SQL-ERRM :'||l_error,40);
689: hr_utility.set_location('Leaving: '||l_proc_name, 45);
690: l_return_value := 'P2';
691: Return l_return_value;
692: End Get_Plan_Names;
685: When Others Then
686: hr_utility.set_location('.......When Others error in Get_Plan_Names',25);
687: l_error := sqlerrm;
688: hr_utility.set_location('.......SQL-ERRM :'||l_error,40);
689: hr_utility.set_location('Leaving: '||l_proc_name, 45);
690: l_return_value := 'P2';
691: Return l_return_value;
692: End Get_Plan_Names;
693:
704: l_payroll_name varchar2(140);
705: l_proc_name varchar2(150) := g_proc_name||'Get_Payroll_Name';
706: l_error varchar2(500);
707: Begin
708: hr_utility.set_location('Entering: '||l_proc_name, 5);
709: If p_payroll_name is Not Null Then
710: l_payroll_names := p_payroll_name;
711: Else
712: l_return_value := 'P0'; --Variable l_extract_plan_name in the FF is not set
710: l_payroll_names := p_payroll_name;
711: Else
712: l_return_value := 'P0'; --Variable l_extract_plan_name in the FF is not set
713: End If;
714: hr_utility.set_location('Payroll Name(s) :'||l_payroll_names,15);
715:
716: If Trim(l_payroll_names) Is null or
717: Trim(l_payroll_names) = '' Then
718: l_payroll_names := Null;
724: Loop
725: l_position := instr(l_payroll_names,',');
726: If l_position = 0 Then
727: g_extract_payroll_names(l_count).payroll_name := Trim(l_payroll_names);
728: hr_utility.set_location('.......Payroll Name Found :'||g_extract_payroll_names(l_count).payroll_name,20);
729: l_return_value := 'T';
730: Exit;
731: Else
732: l_payroll_name := Trim(substr(l_payroll_names,1,l_position-1));
730: Exit;
731: Else
732: l_payroll_name := Trim(substr(l_payroll_names,1,l_position-1));
733: g_extract_payroll_names(l_count).payroll_name := l_payroll_name;
734: hr_utility.set_location('.......Payroll Name Found :'||g_extract_payroll_names(l_count).payroll_name,20);
735: l_payroll_names := substr(l_payroll_names,l_position+1);
736: l_count := l_count + 1;
737: End If;
738: End loop;
737: End If;
738: End loop;
739: End If;
740:
741: hr_utility.set_location('Leaving: '||l_proc_name, 45);
742: Return l_return_value;
743: Exception
744: When Others Then
745: hr_utility.set_location('.......When Others error in Get_Payroll_Name..',25);
741: hr_utility.set_location('Leaving: '||l_proc_name, 45);
742: Return l_return_value;
743: Exception
744: When Others Then
745: hr_utility.set_location('.......When Others error in Get_Payroll_Name..',25);
746: l_error := sqlerrm;
747: hr_utility.set_location('.......SQL-ERRM :'||l_error,40);
748: hr_utility.set_location('Leaving: '||l_proc_name, 45);
749: l_return_value := 'P2';
743: Exception
744: When Others Then
745: hr_utility.set_location('.......When Others error in Get_Payroll_Name..',25);
746: l_error := sqlerrm;
747: hr_utility.set_location('.......SQL-ERRM :'||l_error,40);
748: hr_utility.set_location('Leaving: '||l_proc_name, 45);
749: l_return_value := 'P2';
750: Return l_return_value;
751:
744: When Others Then
745: hr_utility.set_location('.......When Others error in Get_Payroll_Name..',25);
746: l_error := sqlerrm;
747: hr_utility.set_location('.......SQL-ERRM :'||l_error,40);
748: hr_utility.set_location('Leaving: '||l_proc_name, 45);
749: l_return_value := 'P2';
750: Return l_return_value;
751:
752: End Get_Payroll_Names;
764: l_proc_name varchar2(150) := g_proc_name||'Check_Valid_Payroll';
765: l_error varchar2(500);
766:
767: Begin
768: hr_utility.set_location('Entering :'|| l_proc_name,5);
769: If g_primary_asg.EXISTS(p_assignment_id) Then
770:
771: hr_utility.set_location('.......Found the pri. assign in g_primary_asg PL/SQL table..',10);
772: l_payroll_id := g_primary_asg(p_assignment_id).payroll_id;
767: Begin
768: hr_utility.set_location('Entering :'|| l_proc_name,5);
769: If g_primary_asg.EXISTS(p_assignment_id) Then
770:
771: hr_utility.set_location('.......Found the pri. assign in g_primary_asg PL/SQL table..',10);
772: l_payroll_id := g_primary_asg(p_assignment_id).payroll_id;
773:
774: Elsif g_all_sec_asgs.EXISTS(p_assignment_id) Then
775:
772: l_payroll_id := g_primary_asg(p_assignment_id).payroll_id;
773:
774: Elsif g_all_sec_asgs.EXISTS(p_assignment_id) Then
775:
776: hr_utility.set_location('.......Found the secondary assign in g_all_sec_asgs PL/SQL table..',15);
777: l_payroll_id := g_all_sec_asgs(p_assignment_id).payroll_id;
778:
779: End If;
780: Open csr_payroll_name (c_payroll_id => l_payroll_id
782: Fetch csr_payroll_name Into l_payroll_name,l_period_type;
783: close csr_payroll_name;
784: l_return_value := l_payroll_name;
785:
786: hr_utility.set_location('Leaving :'|| l_proc_name,30);
787: Return l_return_value;
788: Exception
789: When Others Then
790: hr_utility.set_location('.......Exception When Others Raised In Check_Valid_Payroll..',25);
786: hr_utility.set_location('Leaving :'|| l_proc_name,30);
787: Return l_return_value;
788: Exception
789: When Others Then
790: hr_utility.set_location('.......Exception When Others Raised In Check_Valid_Payroll..',25);
791: l_error := sqlerrm;
792: hr_utility.set_location('.......SQL-ERRM :'||l_error,30);
793: hr_utility.set_location('Leaving :'|| l_proc_name,40);
794: End Check_Valid_Payroll;
788: Exception
789: When Others Then
790: hr_utility.set_location('.......Exception When Others Raised In Check_Valid_Payroll..',25);
791: l_error := sqlerrm;
792: hr_utility.set_location('.......SQL-ERRM :'||l_error,30);
793: hr_utility.set_location('Leaving :'|| l_proc_name,40);
794: End Check_Valid_Payroll;
795:
796: -- ===============================================================================
789: When Others Then
790: hr_utility.set_location('.......Exception When Others Raised In Check_Valid_Payroll..',25);
791: l_error := sqlerrm;
792: hr_utility.set_location('.......SQL-ERRM :'||l_error,30);
793: hr_utility.set_location('Leaving :'|| l_proc_name,40);
794: End Check_Valid_Payroll;
795:
796: -- ===============================================================================
797: -- ~ Check_Assig_Extra_Info : Check the SRS Plan details in the Assig Extra Info ~
808: l_end_date Date;
809: l_proc_name varchar2(150) := g_proc_name ||'.Check_Assig_Extra_Info';
810: l_error varchar2(500);
811: Begin
812: hr_utility.set_location('Entering: '||l_proc_name, 5);
813: For l_assig_extra_info In csr_assig_extra_info( c_assignment_id => p_assignment_id)
814: Loop
815: l_start_date := fnd_date.canonical_to_date(l_assig_extra_info.aei_information1);
816: If l_assig_extra_info.aei_information2 Is Null Then
835: g_plan_person_identifier := l_assig_extra_info.aei_information3;
836: g_extract_plan_name := l_assig_extra_info.aei_information4;
837: g_qualifies_10yr_rule := l_assig_extra_info.aei_information5;
838: g_qualifies_GrdFathering := l_assig_extra_info.aei_information6;
839: hr_utility.set_location('.......Plan Name :'|| g_extract_plan_name, 20);
840: hr_utility.set_location('.......Person Plan Id :'|| g_plan_person_identifier, 25);
841: hr_utility.set_location('.......Plan Start Date :'|| g_plan_start_date, 30);
842: hr_utility.set_location('.......Plan End Date :'|| g_plan_end_date, 35);
843:
836: g_extract_plan_name := l_assig_extra_info.aei_information4;
837: g_qualifies_10yr_rule := l_assig_extra_info.aei_information5;
838: g_qualifies_GrdFathering := l_assig_extra_info.aei_information6;
839: hr_utility.set_location('.......Plan Name :'|| g_extract_plan_name, 20);
840: hr_utility.set_location('.......Person Plan Id :'|| g_plan_person_identifier, 25);
841: hr_utility.set_location('.......Plan Start Date :'|| g_plan_start_date, 30);
842: hr_utility.set_location('.......Plan End Date :'|| g_plan_end_date, 35);
843:
844: l_return_value := 'Y'; Exit;
837: g_qualifies_10yr_rule := l_assig_extra_info.aei_information5;
838: g_qualifies_GrdFathering := l_assig_extra_info.aei_information6;
839: hr_utility.set_location('.......Plan Name :'|| g_extract_plan_name, 20);
840: hr_utility.set_location('.......Person Plan Id :'|| g_plan_person_identifier, 25);
841: hr_utility.set_location('.......Plan Start Date :'|| g_plan_start_date, 30);
842: hr_utility.set_location('.......Plan End Date :'|| g_plan_end_date, 35);
843:
844: l_return_value := 'Y'; Exit;
845:
838: g_qualifies_GrdFathering := l_assig_extra_info.aei_information6;
839: hr_utility.set_location('.......Plan Name :'|| g_extract_plan_name, 20);
840: hr_utility.set_location('.......Person Plan Id :'|| g_plan_person_identifier, 25);
841: hr_utility.set_location('.......Plan Start Date :'|| g_plan_start_date, 30);
842: hr_utility.set_location('.......Plan End Date :'|| g_plan_end_date, 35);
843:
844: l_return_value := 'Y'; Exit;
845:
846: End If;
844: l_return_value := 'Y'; Exit;
845:
846: End If;
847: End Loop; -- l_assig_extra_info
848: hr_utility.set_location('Leaving: '||l_proc_name, 45);
849: Return l_return_value;
850: Exception
851: When Others Then
852: l_error := sqlerrm;
849: Return l_return_value;
850: Exception
851: When Others Then
852: l_error := sqlerrm;
853: hr_utility.set_location('.......Exception Others Raised at Check_Assig_Extra_Info',30);
854: hr_utility.set_location('.......SQL-ERRM :'||l_error,40);
855: hr_utility.set_location('Leaving: '||l_proc_name, 45);
856: l_return_value := 'A0';
857: Return l_return_value;
850: Exception
851: When Others Then
852: l_error := sqlerrm;
853: hr_utility.set_location('.......Exception Others Raised at Check_Assig_Extra_Info',30);
854: hr_utility.set_location('.......SQL-ERRM :'||l_error,40);
855: hr_utility.set_location('Leaving: '||l_proc_name, 45);
856: l_return_value := 'A0';
857: Return l_return_value;
858: End Check_Assig_Extra_Info;
851: When Others Then
852: l_error := sqlerrm;
853: hr_utility.set_location('.......Exception Others Raised at Check_Assig_Extra_Info',30);
854: hr_utility.set_location('.......SQL-ERRM :'||l_error,40);
855: hr_utility.set_location('Leaving: '||l_proc_name, 45);
856: l_return_value := 'A0';
857: Return l_return_value;
858: End Check_Assig_Extra_Info;
859:
887: e_ext_criteria Exception;
888: l_proc_name varchar2(150) := g_proc_name ||'Pay_US_SRS_Main_Criteria';
889: l_error varchar2(500);
890: Begin
891: hr_utility.set_location('Entering: '||l_proc_name, 5);
892: -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
893: -- ~ Select the extract period type i.e Month, Year Quarter etc.
894: -- ~ only required the first time the criteria is called
895: -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
902: Close csr_leg_code;
903:
904: Open csr_extract_attributes;
905: Fetch csr_extract_attributes Into g_ext_dfn_type, g_ext_dfn_id;
906: hr_utility.set_location('.......Extract Type :'||g_ext_dfn_type, 10);
907: If csr_extract_attributes%NOTFOUND Then
908: Close csr_extract_attributes;
909: l_return_value := 'E0';
910: Raise e_ext_criteria;
913: End If;
914:
915: -- Get the Extract Plan Names from ff_globals_f for SRS_PLAN_NAME
916: -- into g_extract_plan_names table
917: hr_utility.set_location('.......Get Extract Plan Names ', 15);
918: l_return_value := Get_Plan_Names( p_effective_date => p_effective_date
919: ,p_extract_name => p_extract_plan_name);
920: If l_return_value Like 'P%' Then
921: Raise e_ext_criteria;
921: Raise e_ext_criteria;
922: End If;
923: -- Get the Extract Payroll Names from ff_globals_f for SRS_PAYROLL_NAME
924: -- into g_extract_plan_names table
925: hr_utility.set_location('.......Get Extract Payroll Names ', 15);
926: l_return_value := Get_Payroll_Names( p_effective_date => p_effective_date
927: ,p_payroll_name => p_extract_payroll_name);
928:
929: If g_ext_dfn_type = 'SRS_MTD' Then
948: l_return_value := Get_PTD_Start_End_Date(p_assignment_id => p_assignment_id
949: ,p_effective_date => p_effective_date
950: );
951: If l_return_value Like 'S%' Then
952: hr_utility.set_location('.......Error In Function Get_PTD_Start_End_Date',15);
953: l_return_value := 'N';
954: Raise e_ext_criteria;
955: End If;
956:
955: End If;
956:
957: End If;
958:
959: hr_utility.set_location('.......Extracts Dimension :'||g_dimension_name, 20);
960: hr_utility.set_location('.......Extract Start Date :'||g_extract_start_date,25);
961: hr_utility.set_location('.......Extract End Date :'||g_extract_end_date,30);
962: hr_utility.set_location('.......Checking if assignment_id :'||p_assignment_id||' valid for date range..',35);
963:
956:
957: End If;
958:
959: hr_utility.set_location('.......Extracts Dimension :'||g_dimension_name, 20);
960: hr_utility.set_location('.......Extract Start Date :'||g_extract_start_date,25);
961: hr_utility.set_location('.......Extract End Date :'||g_extract_end_date,30);
962: hr_utility.set_location('.......Checking if assignment_id :'||p_assignment_id||' valid for date range..',35);
963:
964: Open csr_assig_rec ( c_assignment_id => p_assignment_id
957: End If;
958:
959: hr_utility.set_location('.......Extracts Dimension :'||g_dimension_name, 20);
960: hr_utility.set_location('.......Extract Start Date :'||g_extract_start_date,25);
961: hr_utility.set_location('.......Extract End Date :'||g_extract_end_date,30);
962: hr_utility.set_location('.......Checking if assignment_id :'||p_assignment_id||' valid for date range..',35);
963:
964: Open csr_assig_rec ( c_assignment_id => p_assignment_id
965: ,c_business_group_id => p_business_group_id
958:
959: hr_utility.set_location('.......Extracts Dimension :'||g_dimension_name, 20);
960: hr_utility.set_location('.......Extract Start Date :'||g_extract_start_date,25);
961: hr_utility.set_location('.......Extract End Date :'||g_extract_end_date,30);
962: hr_utility.set_location('.......Checking if assignment_id :'||p_assignment_id||' valid for date range..',35);
963:
964: Open csr_assig_rec ( c_assignment_id => p_assignment_id
965: ,c_business_group_id => p_business_group_id
966: ,c_effective_date => p_effective_date
971: If csr_assig_rec%FOUND Then
972: If l_primary_asg.assignment_type ='E' Then
973: l_return_value := 'N';
974: g_primary_asg(l_primary_asg.assignment_id) := l_primary_asg;
975: hr_utility.set_location('......Assignment Id :'||p_assignment_id||' found..',40);
976: If g_extract_payroll_names.COUNT > 0 Then
977: For j In 1..g_extract_payroll_names.COUNT
978: Loop
979: If g_extract_payroll_names(j).payroll_name =
987: End If; -- g_extract_payroll_names.COUNT > 0
988: Elsif l_primary_asg.assignment_type ='B' Then
989: g_primary_asg(l_primary_asg.assignment_id) := l_primary_asg;
990: l_return_value := 'B';
991: hr_utility.set_location('......Assignment Type :'||l_return_value,41);
992: End If;
993:
994: If l_return_value = 'Y' Then
995: For i In 1..g_extract_plan_names.COUNT
993:
994: If l_return_value = 'Y' Then
995: For i In 1..g_extract_plan_names.COUNT
996: Loop
997: hr_utility.set_location('.......Checking for Extract Plan Name :'||g_extract_plan_names(i).plan_name,45);
998: l_return_value:= Check_Assig_Extra_Info
999: ( p_assignment_id => p_assignment_id
1000: ,p_extract_plan_name => g_extract_plan_names(i).plan_name
1001: ,p_extract_start_date => g_extract_start_date
1002: ,p_extract_end_date => g_extract_end_date
1003: );
1004: If l_return_value = 'Y' Then
1005: -- SRS Plan found, set the global balances names and exit loop
1006: hr_utility.set_location('.......Found Assig Extra Info for :'||g_extract_plan_names(i).plan_name,50);
1007: g_extract_plan_name := g_extract_plan_names(i).plan_name;
1008: g_extract_plan_names(i).assignment_id := p_assignment_id;
1009: g_eligible_comp_balance := g_extract_plan_name||g_eligible_comp_balance_C;
1010: g_srs_balance := g_extract_plan_name;
1014: g_Additional_Balance := g_extract_plan_name||g_Additional_Balance_C;
1015: g_ER_Additional := g_extract_plan_name||g_ER_Additional_C;
1016: Exit;
1017: Elsif l_return_value Like 'A%' Then
1018: hr_utility.set_location('.......Error in function Check_Assig_Extra_Info',50);
1019: Raise e_ext_criteria;
1020: Else
1021: hr_utility.set_location('.......Extract Plan :'|| g_extract_plan_name||' not found..',50);
1022: l_return_value :='N';
1017: Elsif l_return_value Like 'A%' Then
1018: hr_utility.set_location('.......Error in function Check_Assig_Extra_Info',50);
1019: Raise e_ext_criteria;
1020: Else
1021: hr_utility.set_location('.......Extract Plan :'|| g_extract_plan_name||' not found..',50);
1022: l_return_value :='N';
1023: End If;
1024: End Loop; -- i In 1..g_extract_plan_names.COUNT
1025: Elsif l_return_value ='B' Then
1031: g_extract_plan_name := Null;
1032: g_qualifies_10yr_rule := Null;
1033: g_qualifies_GrdFathering := Null;
1034: g_extract_plan_name := 'BEN_ASSIGN';
1035: hr_utility.set_location('......Before Assignment Type :'||l_return_value,51);
1036: l_return_value :='Y';
1037: hr_utility.set_location('......After Assignment Type :'||l_return_value,52);
1038: End If; -- l_return_value = 'Y'
1039: Else
1033: g_qualifies_GrdFathering := Null;
1034: g_extract_plan_name := 'BEN_ASSIGN';
1035: hr_utility.set_location('......Before Assignment Type :'||l_return_value,51);
1036: l_return_value :='Y';
1037: hr_utility.set_location('......After Assignment Type :'||l_return_value,52);
1038: End If; -- l_return_value = 'Y'
1039: Else
1040: hr_utility.set_location('.......Assignment_id : '||p_assignment_id||' not valid for date range..',40);
1041: l_return_value :='N';
1036: l_return_value :='Y';
1037: hr_utility.set_location('......After Assignment Type :'||l_return_value,52);
1038: End If; -- l_return_value = 'Y'
1039: Else
1040: hr_utility.set_location('.......Assignment_id : '||p_assignment_id||' not valid for date range..',40);
1041: l_return_value :='N';
1042: End If;
1043: Close csr_assig_rec;
1044: hr_utility.set_location('......Return Value :'||l_return_value,89);
1040: hr_utility.set_location('.......Assignment_id : '||p_assignment_id||' not valid for date range..',40);
1041: l_return_value :='N';
1042: End If;
1043: Close csr_assig_rec;
1044: hr_utility.set_location('......Return Value :'||l_return_value,89);
1045: hr_utility.set_location('Leaving: '||l_proc_name, 90);
1046: Return l_return_value;
1047: Exception
1048: When e_ext_criteria Then
1041: l_return_value :='N';
1042: End If;
1043: Close csr_assig_rec;
1044: hr_utility.set_location('......Return Value :'||l_return_value,89);
1045: hr_utility.set_location('Leaving: '||l_proc_name, 90);
1046: Return l_return_value;
1047: Exception
1048: When e_ext_criteria Then
1049: hr_utility.set_location('.......Exception e_ext_criteria raised in Pay_US_SRS_Main_Criteria..',55);
1045: hr_utility.set_location('Leaving: '||l_proc_name, 90);
1046: Return l_return_value;
1047: Exception
1048: When e_ext_criteria Then
1049: hr_utility.set_location('.......Exception e_ext_criteria raised in Pay_US_SRS_Main_Criteria..',55);
1050: hr_utility.set_location('Leaving: '||l_proc_name, 90);
1051: Return l_return_value;
1052: When Others Then
1053: hr_utility.set_location('.......When Others Error Raise in Pay_US_SRS_Main_Criteria..',55);
1046: Return l_return_value;
1047: Exception
1048: When e_ext_criteria Then
1049: hr_utility.set_location('.......Exception e_ext_criteria raised in Pay_US_SRS_Main_Criteria..',55);
1050: hr_utility.set_location('Leaving: '||l_proc_name, 90);
1051: Return l_return_value;
1052: When Others Then
1053: hr_utility.set_location('.......When Others Error Raise in Pay_US_SRS_Main_Criteria..',55);
1054: l_error := sqlerrm;
1049: hr_utility.set_location('.......Exception e_ext_criteria raised in Pay_US_SRS_Main_Criteria..',55);
1050: hr_utility.set_location('Leaving: '||l_proc_name, 90);
1051: Return l_return_value;
1052: When Others Then
1053: hr_utility.set_location('.......When Others Error Raise in Pay_US_SRS_Main_Criteria..',55);
1054: l_error := sqlerrm;
1055: hr_utility.set_location('.......SQL-ERRM :'||l_error,85);
1056: hr_utility.set_location('Leaving: '||l_proc_name, 90);
1057: l_return_value :='M0';
1051: Return l_return_value;
1052: When Others Then
1053: hr_utility.set_location('.......When Others Error Raise in Pay_US_SRS_Main_Criteria..',55);
1054: l_error := sqlerrm;
1055: hr_utility.set_location('.......SQL-ERRM :'||l_error,85);
1056: hr_utility.set_location('Leaving: '||l_proc_name, 90);
1057: l_return_value :='M0';
1058: Return l_return_value;
1059: End Pay_US_SRS_Main_Criteria;
1052: When Others Then
1053: hr_utility.set_location('.......When Others Error Raise in Pay_US_SRS_Main_Criteria..',55);
1054: l_error := sqlerrm;
1055: hr_utility.set_location('.......SQL-ERRM :'||l_error,85);
1056: hr_utility.set_location('Leaving: '||l_proc_name, 90);
1057: l_return_value :='M0';
1058: Return l_return_value;
1059: End Pay_US_SRS_Main_Criteria;
1060:
1075: l_return_value varchar2(1);
1076: l_proc_name varchar2(150) := g_proc_name ||'.Get_Secondary_Assignments';
1077: l_error varchar2(1000);
1078: Begin
1079: hr_utility.set_location('Entering: '||l_proc_name, 5);
1080: FOR l_sec_asgs IN csr_sec_assignments(c_primary_assignment_id => p_primary_assignment_id
1081: ,c_person_id => p_person_id
1082: ,c_effective_date => p_effective_date
1083: ,c_extract_start_date => p_extract_start_date
1090: l_return_value := 'Y';
1091: Else
1092: l_return_value := 'N';
1093: End If; --l_all_sec_asgs.COUNT > 0
1094: hr_utility.set_location('Leaving: '||l_proc_name, 5);
1095: Return l_return_value;
1096:
1097: Exception
1098: When Others Then
1096:
1097: Exception
1098: When Others Then
1099: l_error := substr(SQLERRM,1,999);
1100: hr_utility.set_location('....SQLERRM :'||l_error, 50);
1101: hr_utility.set_location('Leaving: '||l_proc_name, 55);
1102: Raise;
1103: End Get_Secondary_Assignments;
1104:
1097: Exception
1098: When Others Then
1099: l_error := substr(SQLERRM,1,999);
1100: hr_utility.set_location('....SQLERRM :'||l_error, 50);
1101: hr_utility.set_location('Leaving: '||l_proc_name, 55);
1102: Raise;
1103: End Get_Secondary_Assignments;
1104:
1105: -- ===============================================================================
1123: l_proc_name varchar2(150) := g_proc_name ||'.Create_Secondary_Assig_Lines';
1124: l_error varchar2(1000);
1125: l_main_rec csr_rslt_dtl%ROWTYPE;
1126: Begin
1127: hr_utility.set_location('Entering: '||l_proc_name, 5);
1128: -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1129: -- ~ Section(A): Create a new record for each pri. assignment which have more
1130: -- ~ than one Plan Name in the Assign Extra Info.
1131: -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1136: Open csr_ext_rcd_id(c_hide_flag => 'N' -- N=No record is not hidden one
1137: ,c_rcd_type_cd => 'D' ); -- D=Detail, T=Total, H-Header Record types
1138: Fetch csr_ext_rcd_id INTO g_ext_dtl_rcd_id;
1139: Close csr_ext_rcd_id;
1140: hr_utility.set_location('.....Get the results record for person id :'||g_primary_asg(p_assignment_id).person_id,15);
1141: Open csr_rslt_dtl
1142: (c_person_id => g_primary_asg(p_assignment_id).person_id
1143: ,c_ext_rslt_id => ben_ext_thread.g_ext_rslt_id
1144: ,c_ext_dtl_rcd_id => g_ext_dtl_rcd_id
1147: Close csr_rslt_dtl;
1148:
1149: End If; -- g_primary_asg(p_assignment_id).assignment_type <> 'E'
1150:
1151: hr_utility.set_location('.....Checking if more SRS Plans exists for pri. assign',10);
1152: If g_extract_plan_names.COUNT > 1 Then
1153: For i In 1..g_extract_plan_names.COUNT
1154: Loop
1155: hr_utility.set_location('.....Previous SRS Plan :'||g_extract_plan_name||'..',15);
1151: hr_utility.set_location('.....Checking if more SRS Plans exists for pri. assign',10);
1152: If g_extract_plan_names.COUNT > 1 Then
1153: For i In 1..g_extract_plan_names.COUNT
1154: Loop
1155: hr_utility.set_location('.....Previous SRS Plan :'||g_extract_plan_name||'..',15);
1156: hr_utility.set_location('.....Current SRS Plan :'||g_extract_plan_names(i).plan_name||'..',20);
1157:
1158: If (g_extract_plan_names(i).assignment_id Is Null
1159: Or g_extract_plan_names(i).assignment_id <> p_assignment_id) and
1152: If g_extract_plan_names.COUNT > 1 Then
1153: For i In 1..g_extract_plan_names.COUNT
1154: Loop
1155: hr_utility.set_location('.....Previous SRS Plan :'||g_extract_plan_name||'..',15);
1156: hr_utility.set_location('.....Current SRS Plan :'||g_extract_plan_names(i).plan_name||'..',20);
1157:
1158: If (g_extract_plan_names(i).assignment_id Is Null
1159: Or g_extract_plan_names(i).assignment_id <> p_assignment_id) and
1160: Check_Assig_Extra_Info( p_assignment_id => p_assignment_id
1163: ,p_extract_end_date => g_extract_end_date
1164: ) ='Y' Then
1165: g_extract_plan_name := g_extract_plan_names(i).plan_name;
1166: g_extract_plan_names(i).assignment_id := p_assignment_id;
1167: hr_utility.set_location('.....Found Plan :'|| g_extract_plan_names(i).plan_name||'..',25);
1168: hr_utility.set_location('.....Person Identifier :'|| g_plan_person_identifier||'..',30);
1169:
1170: g_eligible_comp_balance := g_extract_plan_name||g_eligible_comp_balance_C;
1171: g_srs_balance := g_extract_plan_name;
1164: ) ='Y' Then
1165: g_extract_plan_name := g_extract_plan_names(i).plan_name;
1166: g_extract_plan_names(i).assignment_id := p_assignment_id;
1167: hr_utility.set_location('.....Found Plan :'|| g_extract_plan_names(i).plan_name||'..',25);
1168: hr_utility.set_location('.....Person Identifier :'|| g_plan_person_identifier||'..',30);
1169:
1170: g_eligible_comp_balance := g_extract_plan_name||g_eligible_comp_balance_C;
1171: g_srs_balance := g_extract_plan_name;
1172: g_ER_balance := g_extract_plan_name||g_ER_balance_C;
1174: g_BuyBack_Balance := g_extract_plan_name||g_BuyBack_Balance_C;
1175: g_Additional_Balance := g_extract_plan_name||g_Additional_Balance_C;
1176: g_ER_Additional := g_extract_plan_name||g_ER_Additional_C;
1177:
1178: hr_utility.set_location('.....Call Create_New_Lines procedure',35);
1179: Create_New_Lines( p_pri_assignment_id => p_assignment_id
1180: ,p_sec_assignment_id => p_assignment_id
1181: ,p_person_id => g_primary_asg(p_assignment_id).person_id
1182: ,p_record_name => 'Primary['||l_secondary_no||'] :'||g_extract_plan_name );
1190: -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1191: -- ~ Section(B): Create a new record for each sec. assignment which have the
1192: -- ~ Plan Name in the Assign Extra Info.
1193: -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1194: hr_utility.set_location('.....Checking if secondary assignments present for id :'||p_assignment_id,40);
1195: hr_utility.set_location('.....g_extract_start_date :'||g_extract_start_date,41);
1196: hr_utility.set_location('.....g_extract_end_date :'||g_extract_end_date,41);
1197: hr_utility.set_location('.....p_assignment_id :'||p_assignment_id,41);
1198: If Get_Secondary_Assignments
1191: -- ~ Section(B): Create a new record for each sec. assignment which have the
1192: -- ~ Plan Name in the Assign Extra Info.
1193: -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1194: hr_utility.set_location('.....Checking if secondary assignments present for id :'||p_assignment_id,40);
1195: hr_utility.set_location('.....g_extract_start_date :'||g_extract_start_date,41);
1196: hr_utility.set_location('.....g_extract_end_date :'||g_extract_end_date,41);
1197: hr_utility.set_location('.....p_assignment_id :'||p_assignment_id,41);
1198: If Get_Secondary_Assignments
1199: ( p_primary_assignment_id => p_assignment_id
1192: -- ~ Plan Name in the Assign Extra Info.
1193: -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1194: hr_utility.set_location('.....Checking if secondary assignments present for id :'||p_assignment_id,40);
1195: hr_utility.set_location('.....g_extract_start_date :'||g_extract_start_date,41);
1196: hr_utility.set_location('.....g_extract_end_date :'||g_extract_end_date,41);
1197: hr_utility.set_location('.....p_assignment_id :'||p_assignment_id,41);
1198: If Get_Secondary_Assignments
1199: ( p_primary_assignment_id => p_assignment_id
1200: ,p_person_id => g_primary_asg(p_assignment_id).person_id
1193: -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1194: hr_utility.set_location('.....Checking if secondary assignments present for id :'||p_assignment_id,40);
1195: hr_utility.set_location('.....g_extract_start_date :'||g_extract_start_date,41);
1196: hr_utility.set_location('.....g_extract_end_date :'||g_extract_end_date,41);
1197: hr_utility.set_location('.....p_assignment_id :'||p_assignment_id,41);
1198: If Get_Secondary_Assignments
1199: ( p_primary_assignment_id => p_assignment_id
1200: ,p_person_id => g_primary_asg(p_assignment_id).person_id
1201: ,p_effective_date => g_effective_date
1201: ,p_effective_date => g_effective_date
1202: ,p_extract_start_date => g_extract_start_date
1203: ,p_extract_end_date => g_extract_end_date ) = 'Y' Then
1204: l_return_value :='SEC_ASSIGN_FOUND';
1205: hr_utility.set_location('.....Secondary assignment found for id :'|| p_assignment_id,45);
1206: Else
1207: l_return_value :='SEC_ASSIGN_NOTFOUND';
1208: hr_utility.set_location('.....Secondary assignment NOT found for id :'|| p_assignment_id,45);
1209: End If;
1204: l_return_value :='SEC_ASSIGN_FOUND';
1205: hr_utility.set_location('.....Secondary assignment found for id :'|| p_assignment_id,45);
1206: Else
1207: l_return_value :='SEC_ASSIGN_NOTFOUND';
1208: hr_utility.set_location('.....Secondary assignment NOT found for id :'|| p_assignment_id,45);
1209: End If;
1210:
1211: If l_return_value = 'SEC_ASSIGN_FOUND' and
1212: g_all_sec_asgs.COUNT > 0 Then
1229: l_valid_payroll := 'N';
1230: End If; -- g_extract_payroll_names.COUNT > 0
1231:
1232: If l_valid_payroll = 'Y' Then
1233: hr_utility.set_location('.....Processing for Secondary assignment id :'|| l_curr_sec_asg_id,50);
1234: If g_ext_dfn_type = 'SRS_PTD' Then
1235: hr_utility.set_location('Extract type Is PTD, so get the PTD start end dates based on payroll id..',55);
1236: g_dimension_name := 'Assignment within Government Reporting Entity Period to Date';
1237: -- The procedure sets the g_extract_start_date, g_extract_end_date globals
1231:
1232: If l_valid_payroll = 'Y' Then
1233: hr_utility.set_location('.....Processing for Secondary assignment id :'|| l_curr_sec_asg_id,50);
1234: If g_ext_dfn_type = 'SRS_PTD' Then
1235: hr_utility.set_location('Extract type Is PTD, so get the PTD start end dates based on payroll id..',55);
1236: g_dimension_name := 'Assignment within Government Reporting Entity Period to Date';
1237: -- The procedure sets the g_extract_start_date, g_extract_end_date globals
1238: -- based on the per_time_periods of the assig. payroll_id
1239: hr_utility.set_location('.....Calling Get_PTD_Start_End_Date in Create_Secondary_Assig_Lines..',60);
1235: hr_utility.set_location('Extract type Is PTD, so get the PTD start end dates based on payroll id..',55);
1236: g_dimension_name := 'Assignment within Government Reporting Entity Period to Date';
1237: -- The procedure sets the g_extract_start_date, g_extract_end_date globals
1238: -- based on the per_time_periods of the assig. payroll_id
1239: hr_utility.set_location('.....Calling Get_PTD_Start_End_Date in Create_Secondary_Assig_Lines..',60);
1240: l_error_value := Get_PTD_Start_End_Date(p_assignment_id => l_curr_sec_asg_id
1241: ,p_effective_date => g_effective_date
1242: );
1243: If l_error_value Like 'S%' Then
1240: l_error_value := Get_PTD_Start_End_Date(p_assignment_id => l_curr_sec_asg_id
1241: ,p_effective_date => g_effective_date
1242: );
1243: If l_error_value Like 'S%' Then
1244: hr_utility.set_location('.....Error In Get_PTD_Start_End_Date',65);
1245: Raise e_ext_criteria;
1246: End If;
1247: End If; --If g_ext_dfn_type = 'SRS_PTD'
1248:
1245: Raise e_ext_criteria;
1246: End If;
1247: End If; --If g_ext_dfn_type = 'SRS_PTD'
1248:
1249: hr_utility.set_location('.....Checking sec. assign for each plan',65);
1250:
1251: For i In 1..g_extract_plan_names.COUNT
1252: Loop
1253: -- The sec. assign may be on a different payroll and the PTD start and end
1252: Loop
1253: -- The sec. assign may be on a different payroll and the PTD start and end
1254: -- dates should be calcualted based on the payroll period of the assignment
1255: -- Check first if the person has the Plan details in Assig Extra Info
1256: hr_utility.set_location('Checking for Plan :'||g_extract_plan_names(i).plan_name,70);
1257: If Check_Assig_Extra_Info( p_assignment_id => l_curr_sec_asg_id
1258: ,p_extract_plan_name => g_extract_plan_names(i).plan_name
1259: ,p_extract_start_date => g_extract_start_date
1260: ,p_extract_end_date => g_extract_end_date
1262: and ( g_extract_plan_names(i).assignment_id Is Null
1263: Or g_extract_plan_names(i).assignment_id <> l_curr_sec_asg_id) Then
1264: g_extract_plan_name := g_extract_plan_names(i).plan_name;
1265: g_extract_plan_names(i).assignment_id := l_curr_sec_asg_id;
1266: hr_utility.set_location('.....Found Plan :'||g_extract_plan_name,75);
1267: hr_utility.set_location('.....Person Identifier :'||g_plan_person_identifier,80);
1268:
1269: g_eligible_comp_balance := g_extract_plan_name||g_eligible_comp_balance_C;
1270: g_srs_balance := g_extract_plan_name;
1263: Or g_extract_plan_names(i).assignment_id <> l_curr_sec_asg_id) Then
1264: g_extract_plan_name := g_extract_plan_names(i).plan_name;
1265: g_extract_plan_names(i).assignment_id := l_curr_sec_asg_id;
1266: hr_utility.set_location('.....Found Plan :'||g_extract_plan_name,75);
1267: hr_utility.set_location('.....Person Identifier :'||g_plan_person_identifier,80);
1268:
1269: g_eligible_comp_balance := g_extract_plan_name||g_eligible_comp_balance_C;
1270: g_srs_balance := g_extract_plan_name;
1271: g_ER_balance := g_extract_plan_name||g_ER_balance_C;
1272: g_AT_Contribution := g_extract_plan_name||g_AT_Contribution_C;
1273: g_BuyBack_Balance := g_extract_plan_name||g_BuyBack_Balance_C;
1274: g_Additional_Balance := g_extract_plan_name||g_Additional_Balance_C;
1275: g_ER_Additional := g_extract_plan_name||g_ER_Additional_C;
1276: hr_utility.set_location('.....Before Create_New_Lines for sec. assignment id: '||l_curr_sec_asg_id,85);
1277:
1278: If g_all_sec_asgs(l_curr_sec_asg_id).primary_flag = 'Y' Then
1279: l_assign_type := 'Primary['||l_primary_no||'] :'|| g_extract_plan_name;
1280: l_primary_no := l_primary_no +1;
1287: ,p_sec_assignment_id => l_curr_sec_asg_id
1288: ,p_person_id => g_primary_asg(p_assignment_id).person_id
1289: ,p_record_name => l_assign_type);
1290:
1291: hr_utility.set_location('.....After Create_New_Lines for sec. assignment id: '||l_curr_sec_asg_id,90);
1292: End If;
1293: End Loop; --For i In 1..g_extract_plan_names.COUNT
1294: End If; --If l_valid_payroll := 'Y'
1295: l_prev_sec_asg_id := l_curr_sec_asg_id;
1302: Delete from ben_ext_rslt_dtl where ext_rslt_dtl_id = l_main_rec.ext_rslt_dtl_id;
1303: End If;
1304: g_primary_asg.DELETE;
1305: g_payroll_names.DELETE;
1306: hr_utility.set_location('Leaving: '||l_proc_name, 130);
1307: Return l_return_value;
1308: Exception
1309: When e_ext_criteria Then
1310: hr_utility.set_location('.....Error e_ext_criteria raised ...',70);
1306: hr_utility.set_location('Leaving: '||l_proc_name, 130);
1307: Return l_return_value;
1308: Exception
1309: When e_ext_criteria Then
1310: hr_utility.set_location('.....Error e_ext_criteria raised ...',70);
1311: hr_utility.set_location('Leaving: '||l_proc_name, 130);
1312: Return l_return_value;
1313: When Others Then
1314: l_error := substr(SQLERRM,1,999);
1307: Return l_return_value;
1308: Exception
1309: When e_ext_criteria Then
1310: hr_utility.set_location('.....Error e_ext_criteria raised ...',70);
1311: hr_utility.set_location('Leaving: '||l_proc_name, 130);
1312: Return l_return_value;
1313: When Others Then
1314: l_error := substr(SQLERRM,1,999);
1315: hr_utility.set_location('.....When Others Error Raised...',120);
1311: hr_utility.set_location('Leaving: '||l_proc_name, 130);
1312: Return l_return_value;
1313: When Others Then
1314: l_error := substr(SQLERRM,1,999);
1315: hr_utility.set_location('.....When Others Error Raised...',120);
1316: hr_utility.set_location('.....SQLERRM :'||l_error,121);
1317: hr_utility.set_location('Leaving: '||l_proc_name, 130);
1318:
1319: Raise;
1312: Return l_return_value;
1313: When Others Then
1314: l_error := substr(SQLERRM,1,999);
1315: hr_utility.set_location('.....When Others Error Raised...',120);
1316: hr_utility.set_location('.....SQLERRM :'||l_error,121);
1317: hr_utility.set_location('Leaving: '||l_proc_name, 130);
1318:
1319: Raise;
1320: End Create_Secondary_Assig_Lines;
1313: When Others Then
1314: l_error := substr(SQLERRM,1,999);
1315: hr_utility.set_location('.....When Others Error Raised...',120);
1316: hr_utility.set_location('.....SQLERRM :'||l_error,121);
1317: hr_utility.set_location('Leaving: '||l_proc_name, 130);
1318:
1319: Raise;
1320: End Create_Secondary_Assig_Lines;
1321:
1419: l_ret_value_char varchar2(150);
1420: l_error_value varchar2(500);
1421: Begin -- Create_New_Lines
1422: -- Get the main detail record
1423: hr_utility.set_location('Entering: '||l_proc_name, 5);
1424: hr_utility.set_location('.....Get the no-hidden record id for the extract..',10);
1425: Open csr_ext_rcd_id(c_hide_flag => 'N' -- N=No record is not hidden one
1426: ,c_rcd_type_cd => 'D' ); -- D=Detail, T=Total, H-Header Record types
1427:
1420: l_error_value varchar2(500);
1421: Begin -- Create_New_Lines
1422: -- Get the main detail record
1423: hr_utility.set_location('Entering: '||l_proc_name, 5);
1424: hr_utility.set_location('.....Get the no-hidden record id for the extract..',10);
1425: Open csr_ext_rcd_id(c_hide_flag => 'N' -- N=No record is not hidden one
1426: ,c_rcd_type_cd => 'D' ); -- D=Detail, T=Total, H-Header Record types
1427:
1428: Fetch csr_ext_rcd_id INTO g_ext_dtl_rcd_id;
1427:
1428: Fetch csr_ext_rcd_id INTO g_ext_dtl_rcd_id;
1429: Close csr_ext_rcd_id;
1430: --
1431: hr_utility.set_location('.....Get the results record for person id :'||p_person_id,15);
1432: Open csr_rslt_dtl
1433: (c_person_id => p_person_id
1434: ,c_ext_rslt_id => ben_ext_thread.g_ext_rslt_id
1435: ,c_ext_dtl_rcd_id => g_ext_dtl_rcd_id
1439:
1440: -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1441: -- ~ Increment the object version number of the record ~
1442: -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1443: hr_utility.set_location('.....Increment the object version of the main record...',20);
1444: l_main_rec.object_version_number := nvl(l_main_rec.object_version_number,0) + 1;
1445: l_new_rec := l_main_rec;
1446: If g_all_sec_asgs.EXISTS(p_sec_assignment_id) Or
1447: g_primary_asg.EXISTS(p_sec_assignment_id) Then
1456: l_business_group_id := g_all_sec_asgs(l_assignment_id).business_group_id;
1457: l_effective_date := Least(g_effective_date, g_all_sec_asgs(l_assignment_id).effective_end_date);
1458: End If;
1459:
1460: hr_utility.set_location('.....l_assignment_id :'||l_assignment_id,25);
1461: hr_utility.set_location('.....l_organization_id :'||l_organization_id,30);
1462: hr_utility.set_location('.....l_business_group_id :'||l_business_group_id,35);
1463: hr_utility.set_location('.....l_effective_date :'||l_effective_date,40);
1464:
1457: l_effective_date := Least(g_effective_date, g_all_sec_asgs(l_assignment_id).effective_end_date);
1458: End If;
1459:
1460: hr_utility.set_location('.....l_assignment_id :'||l_assignment_id,25);
1461: hr_utility.set_location('.....l_organization_id :'||l_organization_id,30);
1462: hr_utility.set_location('.....l_business_group_id :'||l_business_group_id,35);
1463: hr_utility.set_location('.....l_effective_date :'||l_effective_date,40);
1464:
1465: For i in csr_rule_ele( c_ext_rcd_id => g_ext_dtl_rcd_id)
1458: End If;
1459:
1460: hr_utility.set_location('.....l_assignment_id :'||l_assignment_id,25);
1461: hr_utility.set_location('.....l_organization_id :'||l_organization_id,30);
1462: hr_utility.set_location('.....l_business_group_id :'||l_business_group_id,35);
1463: hr_utility.set_location('.....l_effective_date :'||l_effective_date,40);
1464:
1465: For i in csr_rule_ele( c_ext_rcd_id => g_ext_dtl_rcd_id)
1466: Loop
1459:
1460: hr_utility.set_location('.....l_assignment_id :'||l_assignment_id,25);
1461: hr_utility.set_location('.....l_organization_id :'||l_organization_id,30);
1462: hr_utility.set_location('.....l_business_group_id :'||l_business_group_id,35);
1463: hr_utility.set_location('.....l_effective_date :'||l_effective_date,40);
1464:
1465: For i in csr_rule_ele( c_ext_rcd_id => g_ext_dtl_rcd_id)
1466: Loop
1467: Open csr_ff_type(c_formula_type_id => i.data_elmt_rl
1482: ,p_param2_value => to_char(nvl(ben_ext_thread.g_ext_rslt_id, -1))
1483: );
1484: l_ff_value := l_outputs(l_outputs.first).value;
1485: Begin
1486: hr_utility.set_location('.....Applying the format mask',45);
1487: If i.frmt_mask_lookup_cd Is Not Null And
1488: l_ff_value Is Not Null Then
1489: If substr(i.frmt_mask_lookup_cd,1,1) = 'N' then
1490: hr_utility.set_location('.....Applying number format mask :ben_ext_fmt.apply_format_mask',50);
1486: hr_utility.set_location('.....Applying the format mask',45);
1487: If i.frmt_mask_lookup_cd Is Not Null And
1488: l_ff_value Is Not Null Then
1489: If substr(i.frmt_mask_lookup_cd,1,1) = 'N' then
1490: hr_utility.set_location('.....Applying number format mask :ben_ext_fmt.apply_format_mask',50);
1491: l_ff_value_fmt := ben_ext_fmt.apply_format_mask(to_number(l_ff_value), i.frmt_mask_cd);
1492: l_ff_value := l_ff_value_fmt;
1493: Elsif substr(i.frmt_mask_lookup_cd,1,1) = 'D' then
1494: hr_utility.set_location('.....Applying Date format mask :ben_ext_fmt.apply_format_mask',55);
1490: hr_utility.set_location('.....Applying number format mask :ben_ext_fmt.apply_format_mask',50);
1491: l_ff_value_fmt := ben_ext_fmt.apply_format_mask(to_number(l_ff_value), i.frmt_mask_cd);
1492: l_ff_value := l_ff_value_fmt;
1493: Elsif substr(i.frmt_mask_lookup_cd,1,1) = 'D' then
1494: hr_utility.set_location('.....Applying Date format mask :ben_ext_fmt.apply_format_mask',55);
1495: l_ff_value_fmt := ben_ext_fmt.apply_format_mask(FND_DATE.canonical_to_date(l_ff_value), i.frmt_mask_cd);
1496: l_ff_value := l_ff_value_fmt;
1497: End If;
1498: End If;
1499: Exception -- incase l_ff_value is not valid for formatting, just don't format it.
1500: when others then
1501: l_error_value := sqlerrm;
1502: End;
1503: hr_utility.set_location('.....Before Calling procedure Update_Record_Values',60);
1504: Update_Record_Values ( p_ext_rcd_id => g_ext_dtl_rcd_id
1505: ,p_ext_data_element_name => Null
1506: ,p_data_element_value => l_ff_value
1507: ,p_data_ele_seqnum => i.seq_num
1511: End If;
1512: -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1513: -- ~ Assignment Type Primary, Secondary etc. ~
1514: -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1515: hr_utility.set_location('.....Set the Assignment Type :'||p_sec_assignment_id, 95);
1516: Update_Record_Values ( p_ext_rcd_id => g_ext_dtl_rcd_id
1517: ,p_ext_data_element_name => 'Pay US SRS - Detail Assignment Type'
1518: ,p_data_element_value => p_record_name
1519: ,p_ext_dtl_rec => l_new_rec);
1520:
1521: -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1522: -- ~ Insert another record into the results detail table
1523: -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1524: hr_utility.set_location('.....Calling Ins_Rslt_Dtl to create a record...',100);
1525: Ins_Rslt_Dtl(p_dtl_rec => l_new_rec );
1526: hr_utility.set_location('Leaving :'||l_proc_name,150);
1527: Exception
1528: When Others Then
1522: -- ~ Insert another record into the results detail table
1523: -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1524: hr_utility.set_location('.....Calling Ins_Rslt_Dtl to create a record...',100);
1525: Ins_Rslt_Dtl(p_dtl_rec => l_new_rec );
1526: hr_utility.set_location('Leaving :'||l_proc_name,150);
1527: Exception
1528: When Others Then
1529: l_error_value := sqlerrm;
1530: hr_utility.set_location('......Exception Others raised',145);
1526: hr_utility.set_location('Leaving :'||l_proc_name,150);
1527: Exception
1528: When Others Then
1529: l_error_value := sqlerrm;
1530: hr_utility.set_location('......Exception Others raised',145);
1531: hr_utility.set_location('......SQL-ERRM :'||l_error_value,146);
1532: hr_utility.set_location('Leaving :'||l_proc_name,150);
1533:
1534: Raise;
1527: Exception
1528: When Others Then
1529: l_error_value := sqlerrm;
1530: hr_utility.set_location('......Exception Others raised',145);
1531: hr_utility.set_location('......SQL-ERRM :'||l_error_value,146);
1532: hr_utility.set_location('Leaving :'||l_proc_name,150);
1533:
1534: Raise;
1535: End Create_New_Lines;
1528: When Others Then
1529: l_error_value := sqlerrm;
1530: hr_utility.set_location('......Exception Others raised',145);
1531: hr_utility.set_location('......SQL-ERRM :'||l_error_value,146);
1532: hr_utility.set_location('Leaving :'||l_proc_name,150);
1533:
1534: Raise;
1535: End Create_New_Lines;
1536:
1561: -- nocopy changes
1562: l_ext_dtl_rec_nc ben_ext_rslt_dtl%ROWTYPE;
1563: Begin
1564:
1565: hr_utility.set_location('Entering :'||l_proc_name, 5);
1566: -- nocopy changes
1567: l_ext_dtl_rec_nc := p_ext_dtl_rec;
1568:
1569: If p_data_ele_seqnum Is Null Then
1570: Open csr_seqnum ( c_ext_rcd_id => p_ext_rcd_id
1571: ,c_ext_data_element_name => p_ext_data_element_name);
1572: Fetch csr_seqnum Into l_seqnum_rec;
1573: If csr_seqnum%NOTFOUND Then
1574: hr_utility.set_location('.....Data element :'||p_ext_data_element_name||' not found..',10);
1575: Close csr_seqnum;
1576: Else
1577: hr_utility.set_location('.....Data element :'||p_ext_data_element_name||' found..',10);
1578: Close csr_seqnum;
1573: If csr_seqnum%NOTFOUND Then
1574: hr_utility.set_location('.....Data element :'||p_ext_data_element_name||' not found..',10);
1575: Close csr_seqnum;
1576: Else
1577: hr_utility.set_location('.....Data element :'||p_ext_data_element_name||' found..',10);
1578: Close csr_seqnum;
1579: End If;
1580: Else
1581: l_seqnum_rec.seq_num := p_data_ele_seqnum;
1580: Else
1581: l_seqnum_rec.seq_num := p_data_ele_seqnum;
1582: End If;
1583:
1584: hr_utility.set_location('.....Seq. Num :'||l_seqnum_rec.seq_num,15);
1585:
1586: If l_seqnum_rec.seq_num = 1 Then
1587: p_ext_dtl_rec.val_01 := p_data_element_value;
1588: Elsif l_seqnum_rec.seq_num = 2 Then
1735: p_ext_dtl_rec.val_74 := p_data_element_value;
1736: Elsif l_seqnum_rec.seq_num = 75 Then
1737: p_ext_dtl_rec.val_75 := p_data_element_value;
1738: End If;
1739: hr_utility.set_location('Leaving :'||l_proc_name, 25);
1740: Return;
1741: Exception
1742: When Others Then
1743: hr_utility.set_location('.....Exception when others '||l_proc_name,30);
1739: hr_utility.set_location('Leaving :'||l_proc_name, 25);
1740: Return;
1741: Exception
1742: When Others Then
1743: hr_utility.set_location('.....Exception when others '||l_proc_name,30);
1744: -- nocopy changes
1745: p_ext_dtl_rec := l_ext_dtl_rec_nc;
1746: raise;
1747:
1755: l_proc_name Varchar2(150) := g_proc_name||'Ins_Rslt_Dtl';
1756: l_dtl_rec_nc ben_ext_rslt_dtl%ROWTYPE;
1757:
1758: BEGIN -- ins_rslt_dtl
1759: hr_utility.set_location('Entering :'||l_proc_name, 5);
1760:
1761: -- nocopy changes
1762: l_dtl_rec_nc := p_dtl_rec;
1763:
1760:
1761: -- nocopy changes
1762: l_dtl_rec_nc := p_dtl_rec;
1763:
1764: hr_utility.set_location('.....Fetching seq. ben_ext_rslt_dtl_s.NEXTVAL' , 10);
1765:
1766: -- Get the next sequence number to insert a record into the table
1767: SELECT ben_ext_rslt_dtl_s.NEXTVAL INTO p_dtl_rec.ext_rslt_dtl_id FROM dual;
1768:
1765:
1766: -- Get the next sequence number to insert a record into the table
1767: SELECT ben_ext_rslt_dtl_s.NEXTVAL INTO p_dtl_rec.ext_rslt_dtl_id FROM dual;
1768:
1769: hr_utility.set_location('.....Inserting into ben_ext_rslt_dtl table...' , 15);
1770:
1771: INSERT INTO ben_ext_rslt_dtl
1772: (EXT_RSLT_DTL_ID
1773: ,EXT_RSLT_ID
1961: ,p_dtl_rec.THRD_SORT_VAL
1962: ,p_dtl_rec.TRANS_SEQ_NUM
1963: ,p_dtl_rec.RCRD_SEQ_NUM
1964: );
1965: hr_utility.set_location('Leaving :'||l_proc_name, 25);
1966: Return;
1967:
1968: Exception
1969: When Others Then
1966: Return;
1967:
1968: Exception
1969: When Others Then
1970: hr_utility.set_location('.....Exception when others raised',20);
1971: hr_utility.set_location('Leaving :'||l_proc_name, 25);
1972: p_dtl_rec := l_dtl_rec_nc;
1973: Raise;
1974: End Ins_Rslt_Dtl;
1967:
1968: Exception
1969: When Others Then
1970: hr_utility.set_location('.....Exception when others raised',20);
1971: hr_utility.set_location('Leaving :'||l_proc_name, 25);
1972: p_dtl_rec := l_dtl_rec_nc;
1973: Raise;
1974: End Ins_Rslt_Dtl;
1975:
2080: l_proc_name Varchar2(150):= g_proc_name||'Del_Service_Detail_Recs';
2081: l_return_value Number := 0; --0= Sucess, -1=Error
2082:
2083: Begin
2084: hr_utility.set_location('Entering :'||l_proc_name, 5);
2085: -- Get the record id for the Hidden Detail record
2086: hr_utility.set_location('.....Get the hidden record for extract running..',10);
2087: Open csr_ext_rcd_id(c_hide_flag => 'Y' -- Y=Record is hidden one
2088: ,c_rcd_type_cd => 'D' ); -- D=Detail, T=Total, H-Header Record types
2082:
2083: Begin
2084: hr_utility.set_location('Entering :'||l_proc_name, 5);
2085: -- Get the record id for the Hidden Detail record
2086: hr_utility.set_location('.....Get the hidden record for extract running..',10);
2087: Open csr_ext_rcd_id(c_hide_flag => 'Y' -- Y=Record is hidden one
2088: ,c_rcd_type_cd => 'D' ); -- D=Detail, T=Total, H-Header Record types
2089:
2090: Fetch csr_ext_rcd_id INTO l_ext_dtl_rcd_id;
2089:
2090: Fetch csr_ext_rcd_id INTO l_ext_dtl_rcd_id;
2091: Close csr_ext_rcd_id;
2092:
2093: hr_utility.set_location('.....Deleting temp records from ben_ext_rslt_dtl...',15);
2094:
2095: Delete
2096: From ben_ext_rslt_dtl dtl
2097: Where dtl.ext_rslt_id = ben_ext_thread.g_ext_rslt_id
2097: Where dtl.ext_rslt_id = ben_ext_thread.g_ext_rslt_id
2098: And dtl.ext_rcd_id = l_ext_dtl_rcd_id
2099: And business_group_id = p_business_group_id
2100: And dtl.val_01 In ( 'SEC_ASSIGN_FOUND','SEC_ASSIGN_NOTFOUND' );
2101: hr_utility.set_location('Leaving :'||l_proc_name, 25);
2102: Return l_return_value;
2103:
2104: Exception
2105: When Others Then
2102: Return l_return_value;
2103:
2104: Exception
2105: When Others Then
2106: hr_utility.set_location('.....Exception when others raised..', 20);
2107: hr_utility.set_location('Leaving :'||l_proc_name, 25);
2108: Return -1;
2109: End Del_Service_Detail_Recs;
2110:
2103:
2104: Exception
2105: When Others Then
2106: hr_utility.set_location('.....Exception when others raised..', 20);
2107: hr_utility.set_location('Leaving :'||l_proc_name, 25);
2108: Return -1;
2109: End Del_Service_Detail_Recs;
2110:
2111: