[Home] [Help]
PACKAGE BODY: APPS.PQP_US_SRS_EXTRACTS
Source
1 Package Body PQP_US_SRS_Extracts AS
2 -- /* $Header: pqpussrs.pkb 120.1 2005/06/09 15:12:38 rpinjala noship $ */
3 --
4 Cursor csr_rslt_dtl(c_person_id In Number
5 ,c_ext_rslt_id In Number
6 ,c_ext_dtl_rcd_id in Number ) Is
7 Select *
8 From ben_ext_rslt_dtl dtl
9 Where dtl.ext_rslt_id = c_ext_rslt_id
10 And dtl.person_id = c_person_id
11 And dtl.ext_rcd_id = c_ext_dtl_rcd_id;
12
13 -- =============================================================================
14 -- Get the Legislation Code and Curreny Code
15 -- =============================================================================
16 CURSOR csr_leg_code (c_business_group_id IN Number) IS
17 SELECT pbg.legislation_code
18 FROM per_business_groups_perf pbg
19 WHERE pbg.business_group_id = c_business_group_id;
20
21 -- =============================================================================
22 -- ~ Get_Balance_Value : Get the Balance Total for the given dimension as of
23 -- ~ an effective date for an assignment_id
24 -- =============================================================================
25 Function Get_Balance_Value
26 (p_business_group_id In per_all_assignments_f.business_group_id%TYPE -- Context
27 ,p_assignment_id In per_all_assignments_f.assignment_id%TYPE -- Context
28 ,p_effective_date In date -- Context
29 ,p_balance_name In varchar2
30 ,p_dimension_name In varchar2) Return Number As
31
32 l_balance_amount Number;
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;
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 :'||
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,
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;
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 -- =============================================================================
70 -- ~ Get_Header_Information : Common function for Header and Footer data-elements
71 -- =============================================================================
72 Function Get_Header_Information(p_header_type In varchar2
73 ,p_header_name In out nocopy Varchar2) Return Number Is
74
75 l_proc_name varchar2(150) := g_proc_name ||'.Get_Header_Information';
76 l_header_name varchar2(1000);
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;
86
87 If p_header_type = 'EXTRACT_NAME' Then
88 l_header_name := 'Plan Name(s) : ';
89 For i In 1..g_extract_plan_names.COUNT
90 Loop
91 l_header_name := l_header_name ||g_extract_plan_names(i).plan_name||' ';
92 End Loop;
93 Elsif p_header_type = 'PAYROLL_NAME' Then
94 l_header_name := 'Payroll Name(s) :';
95 For i In 1..g_extract_payroll_names.COUNT
96 Loop
97 l_header_name := l_header_name||g_extract_payroll_names(i).payroll_name ||' ';
98 End Loop;
99 ElsIf p_header_type = 'PERIOD_STARTDT' Then
100 l_header_name := To_Char(g_extract_start_date,'MM/DD/YYYY');
101 ElsIf p_header_type = 'PERIOD_ENDDT' Then
102 l_header_name := To_Char(g_extract_end_date,'MM/DD/YYYY');
103 Elsif p_header_type = 'PERIOD_PAYDT' Then
104 l_header_name := To_Char(g_extract_pay_date,'MM/DD/YYYY');
105 Elsif p_header_type = 'PAYROLL_FREQ' Then
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;
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;
122
123 -- ===============================================================================
124 -- ~ Get_Payroll_Name : Get the Payroll Name for an Assignment ~
125 -- ===============================================================================
126 Function Get_Payroll_Name (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
127 ,p_payroll_name In out nocopy varchar2) Return Number Is
128
129 Cursor csr_payroll_name (c_payroll_id In per_all_assignments_f.payroll_id%TYPE
130 ,c_effective_date In date ) Is
131 Select payroll_name, period_type
132 from pay_payrolls_f
133 Where c_effective_date Between effective_start_date
134 and effective_end_date
135 and payroll_id = c_payroll_id;
136
137 l_payroll_id per_all_assignments_f.payroll_id%TYPE;
138 l_start_date date;
139 l_end_date date;
140 l_payroll_name pay_payrolls_f.payroll_name%TYPE;
141 l_period_type pay_payrolls_f.period_type%TYPE;
142 l_proc_name varchar2(150) := g_proc_name ||'.Get_Payroll_Name';
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;
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
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
165 Open csr_time_period (c_payroll_id => l_payroll_id
166 ,c_effective_date => g_effective_date);
167 Fetch csr_time_period Into l_start_date, l_end_date;
168 Close csr_time_period;
169
170 Open csr_payroll_name (c_payroll_id => l_payroll_id
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;
180 g_payroll_names(p_assignment_id).payroll_start_date := l_start_date;
181 g_payroll_names(p_assignment_id).payroll_end_date := l_end_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
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;
199
200 -- ===============================================================================
201 -- ~ Get_Payroll_Start_Date :
202 -- ===============================================================================
203 Function Get_Payroll_Start_Date( p_assignment_id In per_all_assignments_f.assignment_id%TYPE
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);
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
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;
225
226 -- ===============================================================================
227 -- ~ Get_Payroll_End_Date :
228 -- ===============================================================================
229 Function Get_Payroll_End_Date (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
230 ,p_end_date In out nocopy Varchar2) Return Number Is
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);
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
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;
252
253 -- ===============================================================================
254 -- ~ Get_Actual_Pay_Date :
255 -- ===============================================================================
256 Function Get_Actual_Pay_Date ( p_assignment_id In per_all_assignments_f.assignment_id%TYPE
257 ,p_pay_date In out nocopy Varchar2) Return Number Is
258 l_proc_name varchar2(150) := g_proc_name ||'.Get_Actual_Pay_Date';
259 -- nocopy changes tmehra
260 l_date_nc varchar2(20);
261
262 Cursor csr_pay_date ( c_assignment_id In per_all_assignments_f.assignment_id%TYPE
263 ,c_effective_date In date) Is
264 Select max(ppa.effective_date)
265 from pay_payroll_actions ppa,
266 per_time_periods ptp
267 where c_effective_date between ptp.start_date
268 and ptp.end_date
269 and ptp.time_period_id= ppa.time_period_id
270 and ppa.action_type in ('Q','R')
271 and ppa.payroll_action_id In ( Select payroll_action_id
272 from pay_assignment_actions
273 where assignment_id= c_assignment_id
274 and action_status = 'C');
275
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;
285 If g_ext_dfn_type = 'SRS_PTD' Then
286 If g_primary_asg.EXISTS(p_assignment_id) Then
287 l_effective_date := Least(g_primary_asg(p_assignment_id).effective_end_date,g_effective_date);
288 ElsIf g_all_sec_asgs.EXISTS(p_assignment_id) Then
289 l_effective_date := Least(g_all_sec_asgs(p_assignment_id).effective_end_date,g_effective_date);
290 End If;
291
292 Open csr_pay_date ( c_assignment_id => p_assignment_id
293 ,c_effective_date => l_effective_date);
294 Fetch csr_pay_date Into l_pay_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;
304
305 hr_utility.set_location('Leaving: '||l_proc_name, 15);
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;
315
316 -- ===============================================================================
317 -- ~ Get_SRS_Plan_Name :
318 -- ===============================================================================
319 Function Get_SRS_Plan_Name (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
320 ,p_SRS_Plan_Name In out nocopy Varchar2) Return Number Is
321 l_proc_name varchar2(150) := g_proc_name ||'.Get_SRS_Plan_Name';
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
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,
340 p_srs_plan_name := l_srs_plan_name_nc;
341 Return -1;
342 End Get_SRS_Plan_Name;
343 -- ===============================================================================
344 -- ~ Get_Separation_Date : which occurs first Plan end date in Assig Extra Info ~
345 -- ~ or the assignment end date i.e. terminated or retired etc.
346 -- ===============================================================================
347 Function Get_Separation_Date (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
348 ,p_Separation_Date In out nocopy Varchar2) Return Number Is
349
350 l_proc_name varchar2(150) := g_proc_name ||'.Get_Separation_Date';
351 l_assig_end_date date;
352 l_separation_date date;
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
362 If g_primary_asg.EXISTS(p_assignment_id) Then
363 l_assig_end_date := g_primary_asg(p_assignment_id).effective_end_date;
364 Elsif g_all_sec_asgs.EXISTS(p_assignment_id) Then
365 l_assig_end_date := g_all_sec_asgs(p_assignment_id).effective_end_date;
366 End If;
367
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
377 Return 0;
378 Exception
379 When Others Then
380 -- nocopy changes
381 p_separation_date := l_separation_date_nc;
382 Return -1;
383
384 End Get_Separation_Date;
385
386 -- ===============================================================================
387 -- ~ Get_Assig_Status : Fetchs the Assignment status for a given assignment id ~
388 -- ===============================================================================
389 Function Get_Assig_Status (p_assignment_id In per_all_assignments_f.assignment_id%TYPE
390 ,p_status_code In out nocopy Varchar2) Return Number Is
391 Cursor cur_status_name
392 (c_assignment_status_type_id In per_all_assignments_f.assignment_status_type_id%TYPE) Is
393 select user_status
394 from per_assignment_status_types
395 where assignment_status_type_id = c_assignment_status_type_id;
396
397 l_assig_status per_assignment_status_types.user_status%TYPE;
398 l_assig_type_id per_all_assignments_f.assignment_status_type_id%TYPE;
399 l_proc_name varchar2(150) := g_proc_name ||'.Get_Assig_Status';
400 l_error varchar2(500);
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
410 l_assig_type_id := g_primary_asg(p_assignment_id).assignment_status_type_id;
411 Open cur_status_name(c_assignment_status_type_id => l_assig_type_id);
412 Fetch cur_status_name Into l_assig_status;
413 Close cur_status_name;
414 p_status_code := l_assig_status;
415 Elsif g_all_sec_asgs.EXISTS(p_assignment_id) Then
416 l_assig_type_id := g_all_sec_asgs(p_assignment_id).assignment_status_type_id;
417 Open cur_status_name(c_assignment_status_type_id => l_assig_type_id);
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
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;
435
436 -- ===============================================================================
437 -- ~ Get_Person_Indentifier : Return the Plan Id for the current plan being ~
438 -- ~ processed. This is used for both primary and secondary assignments ~
439 -- ===============================================================================
440 Function Get_Person_Indentifier( p_assignment_id In per_all_assignments_f.assignment_id%TYPE -- context
441 ,p_person_identifier In out nocopy Varchar2 ) Return Number Is
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);
451 Return 0;
452 Exception
453 When Others Then
454 p_person_identifier := l_person_identifier_nc;
455 Return -1;
456 End Get_Person_Indentifier;
457
458 -- =================================================================================
459 -- ~ Get_SRS_Deduction_Balances : Returns the balance amount for an assignment for ~
460 -- ~ any given balance and the dimension is based the extract running i.e. YTD,QTD ~
461 -- ~ or MTD and is set in the main criteria function Pay_US_SRS_Main_Criteria ~
462 -- =================================================================================
463 Function Get_SRS_Deduction_Balances (p_assignment_id In per_all_assignments_f.assignment_id%TYPE -- context
464 ,p_balance_name In pay_balance_types.balance_name%TYPE
465 ,p_balance_amount In out nocopy Number
466 ) Return Number Is
467
468 Cursor c_tax_id ( c_assignment_id In per_all_assignments_f.assignment_id%Type
469 ,c_effective_date In Date) Is
470 Select to_number(sft.segment1),
471 asg.business_group_id
472 From hr_soft_coding_keyflex sft,
473 per_assignments_f asg
474 Where sft.soft_coding_keyflex_id = asg.soft_coding_keyflex_id
475 And asg.assignment_id = c_assignment_id
476 And c_effective_date between asg.effective_start_date
477 and asg.effective_end_date;
478
479 l_dimension_name pay_balance_dimensions.dimension_name%TYPE;
480 l_defined_balance_id pay_defined_balances.defined_balance_id%TYPE;
481 l_balance_name pay_balance_types.balance_name%TYPE;
482 l_effective_date date;
483 l_balance_amount Number := 0;
484 l_return_value Number := 0;
485 l_proc_name varchar2(150) := g_proc_name ||'.Get_SRS_Deduction_Balances';
486 l_error varchar2(500);
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
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
499 l_effective_date := Least(g_all_sec_asgs(p_assignment_id).effective_end_date,g_effective_date);
500 End If;
501
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);
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;
516 Elsif Trim(Upper(p_balance_name)) = Trim(Upper(g_ER_balance_C)) Then
517 -- ER Balance
518 l_balance_name := g_ER_balance;
519 Elsif Trim(Upper(p_balance_name)) = Trim(Upper(g_AT_Contribution_C)) Then
520 -- AT Contribution
521 l_balance_name := g_AT_Contribution;
522 Elsif Trim(Upper(p_balance_name)) = Trim(Upper(g_BuyBack_Balance_C)) Then
523 -- Buy Back Balance
524 l_balance_name := g_BuyBack_Balance;
525 Elsif Trim(Upper(p_balance_name)) = Trim(Upper(g_Additional_Balance_C)) Then
526 -- Additional Balance
527 l_balance_name := g_Additional_Balance;
528 Elsif Trim(Upper(p_balance_name)) = Trim(Upper(g_ER_Additional_C)) Then
529 -- ER Additional Balance
530 l_balance_name := g_ER_Additional;
531 Elsif Trim(Upper(p_balance_name)) = Trim(Upper(g_SRS_balance_C)) Then
532 l_balance_name := g_extract_plan_name;
533 Else
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);
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
550 Close csr_defined_bal;
551 l_return_value := 0;
552 pay_balance_pkg.set_context('tax_unit_id', l_tax_id);
553 pay_balance_pkg.set_context('date_earned', l_effective_date);
554 l_balance_amount := pay_balance_pkg.get_value(l_defined_balance_id,
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);
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 -- ===============================================================================
572 -- ~ Get_PTD_Start_End_Date :
573 -- ===============================================================================
574 Function Get_PTD_Start_End_Date(p_assignment_id In per_all_assignments_f.assignment_id%TYPE
575 ,p_effective_date In Date
576 ) Return Varchar2 Is
577
578 l_assig_rec csr_get_payroll_id%ROWTYPE;
579 l_time_period_rec csr_time_period%ROWTYPE;
580 l_assig_time_period csr_time_period%ROWTYPE;
581 l_return_value varchar2(5) :='T';
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
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
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
604 --
605 Open csr_time_period ( c_payroll_id => l_assig_rec.payroll_id
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
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;
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);
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;
637
638 End Get_PTD_Start_End_Date;
639 -- ======================================================================================
640 -- ~ Get_Plan_Names : Gets the Plan Names from Global Values table into global variable ~
641 -- ======================================================================================
642 Function Get_Plan_Names ( p_effective_date In Date
643 ,p_extract_name in Varchar2 ) Return Varchar2 Is
644
645 l_extract_names varchar2(240) := null;
646 l_count number :=1;
647 l_position number;
648 l_plan_name varchar2(150);
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
658 End If;
659
660 If Trim(l_extract_names) Is null or
661 Trim(l_extract_names) = '' Then
662 l_return_value := 'P1'; -- Global Value For Extract is Null/Blank in
663 End If;
664 If l_return_value ='T' Then
665 For i in 1..length(l_extract_names )
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));
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;
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);
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
694 -- ============================================================================================
695 -- ~ Get_Payroll_Names : Gets the Payroll Names from Global Values table into global variable ~
696 -- ============================================================================================
697 Function Get_Payroll_Names ( p_effective_date In Date
698 ,p_payroll_name In varchar2) Return Varchar2 Is
699
700 l_return_value varchar2(5) := 'T';
701 l_position number(5);
702 l_count number(5) := 1;
703 l_payroll_names varchar2(140);
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
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;
719 l_return_value := 'N'; -- Variable Is set to blank
720 End If;
721
722 If l_return_value ='T' Then
723 For i in 1..length(l_payroll_names)
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));
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;
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);
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;
753
754 -- ===============================================================================
755 -- ~ Check_Valid_Payroll : For the given assignment return the payroll name ~
756 -- ===============================================================================
757 Function Check_Valid_Payroll ( p_assignment_id In per_all_assignments_f.assignment_id%TYPE
758 ,p_effective_date In Date) Return Varchar2 Is
759
760 l_payroll_id pay_payrolls_f.payroll_id%TYPE;
761 l_payroll_name pay_payrolls_f.payroll_name%TYPE;
762 l_period_type pay_payrolls_f.period_type%TYPE;
763 l_return_value pay_payrolls_f.payroll_name%TYPE;
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;
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
781 ,c_effective_date => p_effective_date);
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);
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 ~
798 -- ===============================================================================
799 Function Check_Assig_Extra_Info ( p_assignment_id In per_all_assignments_f.assignment_id%TYPE
800 ,p_extract_plan_name In varchar2
801 ,p_extract_start_date In date
802 ,p_extract_end_date In date
803 ) Return Varchar2 Is
804
805 l_assig_extra_info csr_assig_extra_info%ROWTYPE;
806 l_return_value Varchar2(5) :='N';
807 l_start_date Date;
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
817 l_end_date := to_date('31/12/4712','DD/MM/YYYY');
818 Else
819 l_end_date := fnd_date.canonical_to_date(l_assig_extra_info.aei_information2);
820 End If;
821 -- If the SRS Plan present in
822 If l_assig_extra_info.aei_information4 Is Not Null and
823 l_assig_extra_info.aei_information4 = p_extract_plan_name and
824 ( (g_effective_date between l_start_date
825 and l_end_date)
826 Or
827 (l_start_date between g_extract_start_date
828 and g_extract_end_date)
829 Or
830 (l_end_date between g_extract_start_date
831 and g_extract_end_date )
832 )Then
833 g_plan_start_date := l_start_date;
834 g_plan_end_date := l_end_date;
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
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;
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
860 -- ===============================================================================
861 -- ~ Pay_US_SRS_Main_Criteria : The Main Extract Criteria for the SRS Extracts ~
862 -- ===============================================================================
863 Function Pay_US_SRS_Main_Criteria ( p_assignment_id In per_all_assignments_f.assignment_id%TYPE
864 ,p_effective_date In date
865 ,p_business_group_id In per_all_assignments_f.business_group_id%TYPE
866 ,p_extract_plan_name In varchar2
867 ,p_extract_payroll_name In varchar2
868 ) Return Varchar2 Is
869
870 Cursor csr_extract_attributes Is
871 Select eat.ext_dfn_type
872 ,eat.ext_dfn_id
873 From pqp_extract_attributes eat
874 Where eat.ext_dfn_id = ben_ext_thread.g_ext_dfn_id;
875
876 Cursor csr_chk_person ( c_person_id In per_all_people_f.person_id%TYPE
877 ,c_effective_date In Date ) Is
878 select *
879 from per_all_people_f
880 where person_id = c_person_id
881 and c_effective_date between effective_start_date
882 and effective_end_date;
883
884 l_error_value Number;
885 l_return_value Varchar2(5) :='N';
886 l_primary_asg csr_assig_rec%ROWTYPE;
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 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
896 If g_business_group_id Is Null Then
897 g_effective_date := p_effective_date;
898 g_business_group_id := p_business_group_id;
899
900 open csr_leg_code(p_business_group_id);
901 Fetch csr_leg_code Into g_legislation_code;
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;
911 Else
912 Close csr_extract_attributes;
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;
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
930 g_extract_start_date := trunc(p_effective_date,'MONTH');
931 g_extract_end_date := p_effective_date;
932 g_dimension_name :='Assignment within Government Reporting Entity Month';
933 Elsif g_ext_dfn_type = 'SRS_QTD' Then
934 g_extract_start_date := trunc(p_effective_date,'Q');
935 g_extract_end_date := p_effective_date;
936 g_dimension_name := 'Assignment within Government Reporting Entity Quarter to Date';
937 Elsif g_ext_dfn_type = 'SRS_YTD' Then
938 g_extract_start_date := trunc(p_effective_date,'YEAR');
939 g_extract_end_date := p_effective_date;
940 g_dimension_name := 'Assignment within Government Reporting Entity Year to Date';
941 End If;
942 End If; --If g_business_group_id Is Null
943
944 If g_ext_dfn_type = 'SRS_PTD' Then
945 g_dimension_name := 'Assignment within Government Reporting Entity Period to Date';
946 -- The procedure sets the g_extract_start_date, g_extract_end_date globals
947 -- based on the per_time_periods of the assig. payroll_id
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
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
966 ,c_effective_date => p_effective_date
967 ,c_extract_start_date => g_extract_start_date
968 ,c_extract_end_date => g_extract_end_date);
969 Fetch csr_assig_rec Into l_primary_asg;
970
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 =
980 Check_Valid_Payroll ( p_assignment_id => p_assignment_id
981 ,p_effective_date => g_effective_date) Then
982 l_return_value := 'Y'; Exit;
983 End If;
984 End Loop;
985 Else
986 l_return_value := 'Y';
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
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;
1011 g_ER_balance := g_extract_plan_name||g_ER_balance_C;
1012 g_AT_Contribution := g_extract_plan_name||g_AT_Contribution_C;
1013 g_BuyBack_Balance := g_extract_plan_name||g_BuyBack_Balance_C;
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';
1023 End If;
1024 End Loop; -- i In 1..g_extract_plan_names.COUNT
1025 Elsif l_return_value ='B' Then
1026 -- This means that the assignment is a Benefits Assignment (OAB) which is created
1027 -- when a person is terminated.
1028 g_plan_start_date := Null;
1029 g_plan_end_date := Null;
1030 g_plan_person_identifier := Null;
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
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
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';
1058 Return l_return_value;
1059 End Pay_US_SRS_Main_Criteria;
1060
1061 -- ===============================================================================
1062 -- ~ Get_Secondary_Assignments : Fetchs all the Secondary Assignments ~
1063 -- ===============================================================================
1064 Function Get_Secondary_Assignments
1065 ( p_primary_assignment_id in per_all_assignments_f.assignment_id%TYPE
1066 ,p_person_id in per_all_people_f.person_id%TYPE
1067 ,p_effective_date in date
1068 ,p_extract_start_date in date
1069 ,p_extract_end_date in date ) Return Varchar2 Is
1070
1071
1072 l_sec_asgs csr_sec_assignments%ROWTYPE;
1073 l_curr_asg_id per_all_assignments_f.assignment_id%TYPE;
1074 l_prev_asg_id per_all_assignments_f.assignment_id%TYPE;
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
1084 ,c_extract_end_date => p_extract_end_date )
1085 Loop
1086 g_all_sec_asgs(l_sec_asgs.assignment_id) := l_sec_asgs;
1087 End Loop; -- l_sec_asgs IN csr_sec_assignments
1088
1089 If g_all_sec_asgs.COUNT > 0 Then
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
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 -- ===============================================================================
1106 -- ~ Create_Secondary_Lines : This function is called by the hidden record to ~
1107 -- ~ check if any secondary assignments exits for the employee. If found then ~
1108 -- ~ check the assignment extra info for the plan details. ~
1109 -- ===============================================================================
1110 Function Create_Secondary_Assig_Lines ( p_assignment_id in per_all_assignments_f.assignment_id%TYPE
1111 ) Return Varchar2 Is
1112 l_primary_assig_id per_all_assignments_f.assignment_id%TYPE;
1113 l_curr_sec_asg_id per_all_assignments_f.assignment_id%TYPE;
1114 l_prev_sec_asg_id per_all_assignments_f.assignment_id%TYPE;
1115
1116 l_return_value varchar2(40);
1117 l_valid_payroll varchar2(40);
1118 l_error_value varchar2(5);
1119 l_secondary_no number(5) :=1;
1120 l_primary_no number(5) :=1;
1121 l_assign_type varchar2(150);
1122 e_ext_criteria Exception;
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 -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
1132 -- We don't want the benefits primary assignment, hence we will delete this after
1133 -- checking for the secondary and terminated active assignment within a given
1134 -- date-range.
1135 If g_primary_asg(p_assignment_id).assignment_type <> 'E' Then
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
1145 );
1146 Fetch csr_rslt_dtl INTO l_main_rec;
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);
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
1161 ,p_extract_plan_name => g_extract_plan_names(i).plan_name
1162 ,p_extract_start_date => g_extract_start_date
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;
1172 g_ER_balance := g_extract_plan_name||g_ER_balance_C;
1173 g_AT_Contribution := g_extract_plan_name||g_AT_Contribution_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 );
1183 l_secondary_no := l_secondary_no +1;
1184
1185 End If; --g_extract_plan_names(i).plan_name <> g_extract_plan_name
1186 End Loop; -- For i In 1..g_extract_plan_names
1187 End If; --g_extract_plan_name.COUNT > 1
1188
1189
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
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
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;
1210
1211 If l_return_value = 'SEC_ASSIGN_FOUND' and
1212 g_all_sec_asgs.COUNT > 0 Then
1213 l_secondary_no := 1;
1214 l_primary_no := 1;
1215 l_curr_sec_asg_id := g_all_sec_asgs.FIRST;
1216 While l_curr_sec_asg_id Is Not Null
1217 Loop
1218 If g_extract_payroll_names.COUNT > 0 Then
1219 l_valid_payroll := 'N';
1220 For j In 1..g_extract_payroll_names.COUNT
1221 Loop
1222 If g_extract_payroll_names(j).payroll_name =
1223 Check_Valid_Payroll ( p_assignment_id => l_curr_sec_asg_id
1224 ,p_effective_date => g_effective_date) Then
1225 l_valid_payroll := 'Y'; Exit;
1226 End If;
1227 End Loop;
1228 Else
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
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
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
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
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
1261 ) ='Y'
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;
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;
1281 Else
1282 l_assign_type := 'Secondary['||l_secondary_no||'] :'||g_extract_plan_name;
1283 l_secondary_no := l_secondary_no +1;
1284 End If;
1285
1286 Create_New_Lines( p_pri_assignment_id => g_primary_asg(p_assignment_id).assignment_id
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;
1296 l_curr_sec_asg_id := g_all_sec_asgs.NEXT(l_prev_sec_asg_id);
1297
1298 End Loop; -- While l_curr_sec_asg_id Is Not Null
1299 g_all_sec_asgs.DELETE;
1300 End If;
1301 If g_primary_asg(p_assignment_id).assignment_type <> 'E' Then
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);
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;
1320 End Create_Secondary_Assig_Lines;
1321
1322
1323 --=========================================================
1324 ---Added new function to get EE DCP contribution Limit
1325 --This function is for Over limit report.
1326 --============================================================
1327 FUNCTION get_dcp_limit (p_effective_date DATE)
1328 RETURN NUMBER IS
1329
1330 CURSOR c_dcp_limit (cp_effective_date DATE)
1331 IS
1332 SELECT fed_information5 dcp_limit -- DCP EE Contribution Limit
1333 ,effective_start_date
1334 ,effective_end_date
1335 FROM pay_us_federal_tax_info_f
1336 WHERE fed_information_category = 'SRS LIMITS'
1337 AND cp_effective_date BETWEEN effective_start_date
1338 AND effective_end_date;
1339 l_dcp_limit c_dcp_limit%ROWTYPE;
1340 BEGIN
1341 OPEN c_dcp_limit (p_effective_date);
1342 FETCH c_dcp_limit INTO l_dcp_limit;
1343 CLOSE c_dcp_limit;
1344 RETURN (l_dcp_limit.dcp_limit);
1345
1346 END;
1347
1348
1349 --------------End Function get_dcp_limit-----------------------------------+
1350
1351 -- ================================================================================
1352 -- ~ Create_New_Lines : This procedure creates a new line in the results detail ~
1353 -- ~ table for each Retirement Plan of an assignment. It re-computes all the rule ~
1354 -- ~ based person level fast-formulas for the secondary assignment. ~
1355 -- ================================================================================
1356 Procedure Create_New_Lines
1357 (p_pri_assignment_id In per_all_assignments_f.assignment_id%TYPE
1358 ,p_sec_assignment_id In per_all_assignments_f.assignment_id%TYPE
1359 ,p_person_id In per_all_people_f.person_id%TYPE
1360 ,p_record_name In Varchar2
1361 ) Is
1362
1363 --
1364 -- Cursor to get all the rule based data-elements for the detail record
1365 --
1366 Cursor csr_rule_ele
1367 (c_ext_rcd_id In ben_ext_data_elmt_in_rcd.ext_rcd_id%TYPE) Is
1368 Select a.ext_data_elmt_in_rcd_id
1369 ,a.seq_num
1370 ,a.sprs_cd
1371 ,a.strt_pos
1372 ,a.dlmtr_val
1373 ,a.rqd_flag
1374 ,b.ext_data_elmt_id
1375 ,b.data_elmt_typ_cd
1376 ,b.data_elmt_rl
1377 ,b.name
1378 ,hr_general.decode_lookup('BEN_EXT_FRMT_MASK', b.frmt_mask_cd) frmt_mask_cd
1379 , b.frmt_mask_cd frmt_mask_lookup_cd
1380 ,b.string_val
1381 ,b.dflt_val
1382 ,b.max_length_num
1383 ,b.just_cd
1384 from ben_ext_data_elmt b,
1385 ben_ext_data_elmt_in_rcd a
1386 where a.ext_data_elmt_id = b.ext_data_elmt_id
1387 and b.data_elmt_typ_cd = 'R'
1388 and a.ext_rcd_id = c_ext_rcd_id
1389 order by a.seq_num;
1390
1391 Cursor csr_ff_type ( c_formula_type_id in ff_formulas_f.formula_id%TYPE
1392 ,c_effective_date in date) Is
1393 Select formula_type_id
1394 from ff_formulas_f
1395 where formula_id = c_formula_type_id
1396 and c_effective_date between effective_start_date
1397 and effective_end_date;
1398
1399 -- Variable Declaration
1400 l_rec_serial_num NUMBER(3);
1401 l_itr NUMBER(3);
1402 l_next_itr NUMBER(3);
1403 l_foumula_type_id ff_formulas_f.formula_id%TYPE;
1404 l_assignment_id per_all_assignments_f.assignment_id%TYPE;
1405 l_organization_id per_all_assignments_f.organization_id%TYPE;
1406 l_business_group_id per_all_assignments_f.business_group_id%TYPE;
1407 l_ff_value ben_ext_rslt_dtl.val_01%TYPE;
1408 l_ff_value_fmt ben_ext_rslt_dtl.val_01%TYPE;
1409 l_effective_date date;
1410 l_outputs ff_exec.outputs_t;
1411 -- Rowtype Variable Declaration
1412 l_main_rec csr_rslt_dtl%ROWTYPE;
1413 l_new_rec csr_rslt_dtl%ROWTYPE;
1414 l_prev_new_rec csr_rslt_dtl%ROWTYPE;
1415 l_balance_amount Number;
1416
1417 l_proc_name Varchar2(150):= g_proc_name||'Create_New_Lines';
1418 l_return_value Number;
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
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
1436 );
1437 Fetch csr_rslt_dtl INTO l_main_rec;
1438 Close csr_rslt_dtl;
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
1448 If g_primary_asg.EXISTS(p_sec_assignment_id) Then
1449 l_assignment_id := p_sec_assignment_id;
1450 l_organization_id := g_primary_asg(l_assignment_id).organization_id;
1451 l_business_group_id := g_primary_asg(l_assignment_id).business_group_id;
1452 l_effective_date := Least(g_effective_date, g_primary_asg(l_assignment_id).effective_end_date);
1453 ElsIf g_all_sec_asgs.EXISTS(p_sec_assignment_id) Then
1454 l_assignment_id := p_sec_assignment_id;
1455 l_organization_id := g_all_sec_asgs(l_assignment_id).organization_id;
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
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
1468 ,c_effective_date => l_effective_date);
1469 Fetch csr_ff_type Into l_foumula_type_id;
1470 Close csr_ff_type;
1471 If l_foumula_type_id = -413 Then -- person level rule
1472 l_outputs := benutils.formula
1473 (p_formula_id => i.data_elmt_rl
1474 ,p_effective_date => l_effective_date
1475 ,p_assignment_id => l_assignment_id
1476 ,p_organization_id => l_organization_id
1477 ,p_business_group_id => l_business_group_id
1478 ,p_jurisdiction_code => Null
1479 ,p_param1 => 'EXT_DFN_ID'
1480 ,p_param1_value => to_char(nvl(ben_ext_thread.g_ext_dfn_id, -1))
1481 ,p_param2 => 'EXT_RSLT_ID'
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);
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
1508 ,p_ext_dtl_rec => l_new_rec);
1509 End If;
1510 End Loop; --For i in csr_rule_ele
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
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
1537 -- ================================================================================
1538 -- ~ Update_Record_Values :
1539 -- ================================================================================
1540 Procedure Update_Record_Values ( p_ext_rcd_id In ben_ext_rcd.ext_rcd_id%TYPE
1541 ,p_ext_data_element_name In ben_ext_data_elmt.name%TYPE
1542 ,p_data_element_value In ben_ext_rslt_dtl.val_01%TYPE
1543 ,p_data_ele_seqnum In Number
1544 ,p_ext_dtl_rec In out nocopy ben_ext_rslt_dtl%ROWTYPE
1545 ) Is
1546 Cursor csr_seqnum ( c_ext_rcd_id In ben_ext_rcd.ext_rcd_id%TYPE
1547 ,c_ext_data_element_name In ben_ext_data_elmt.name%TYPE
1548 ) Is
1549 select der.ext_data_elmt_id,
1550 der.seq_num,
1551 ede.name
1552 from ben_ext_data_elmt_in_rcd der
1553 ,ben_ext_data_elmt ede
1554 where der.ext_rcd_id = c_ext_rcd_id
1555 and ede.ext_data_elmt_id = der.ext_data_elmt_id
1556 and ede.name like '%'|| c_ext_data_element_name
1557 order by seq_num;
1558
1559 l_seqnum_rec csr_seqnum%ROWTYPE;
1560 l_proc_name varchar2(150):= g_proc_name||'Update_Record_Values';
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;
1579 End If;
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
1589 p_ext_dtl_rec.val_02 := p_data_element_value;
1590 Elsif l_seqnum_rec.seq_num = 3 Then
1591 p_ext_dtl_rec.val_03 := p_data_element_value;
1592 Elsif l_seqnum_rec.seq_num = 4 Then
1593 p_ext_dtl_rec.val_04 := p_data_element_value;
1594 Elsif l_seqnum_rec.seq_num = 5 Then
1595 p_ext_dtl_rec.val_05 := p_data_element_value;
1596 Elsif l_seqnum_rec.seq_num = 6 Then
1597 p_ext_dtl_rec.val_06 := p_data_element_value;
1598 Elsif l_seqnum_rec.seq_num = 7 Then
1599 p_ext_dtl_rec.val_07 := p_data_element_value;
1600 Elsif l_seqnum_rec.seq_num = 8 Then
1601 p_ext_dtl_rec.val_08 := p_data_element_value;
1602 Elsif l_seqnum_rec.seq_num = 9 Then
1603 p_ext_dtl_rec.val_09 := p_data_element_value;
1604 Elsif l_seqnum_rec.seq_num = 10 Then
1605 p_ext_dtl_rec.val_10 := p_data_element_value;
1606 Elsif l_seqnum_rec.seq_num = 11 Then
1607 p_ext_dtl_rec.val_11 := p_data_element_value;
1608 Elsif l_seqnum_rec.seq_num = 12 Then
1609 p_ext_dtl_rec.val_12 := p_data_element_value;
1610 Elsif l_seqnum_rec.seq_num = 13 Then
1611 p_ext_dtl_rec.val_13 := p_data_element_value;
1612 Elsif l_seqnum_rec.seq_num = 14 Then
1613 p_ext_dtl_rec.val_14 := p_data_element_value;
1614 Elsif l_seqnum_rec.seq_num = 15 Then
1615 p_ext_dtl_rec.val_15 := p_data_element_value;
1616 Elsif l_seqnum_rec.seq_num = 16 Then
1617 p_ext_dtl_rec.val_16 := p_data_element_value;
1618 Elsif l_seqnum_rec.seq_num = 17 Then
1619 p_ext_dtl_rec.val_17 := p_data_element_value;
1620 Elsif l_seqnum_rec.seq_num = 18 Then
1621 p_ext_dtl_rec.val_18 := p_data_element_value;
1622 Elsif l_seqnum_rec.seq_num = 19 Then
1623 p_ext_dtl_rec.val_19 := p_data_element_value;
1624 Elsif l_seqnum_rec.seq_num = 20 Then
1625 p_ext_dtl_rec.val_20 := p_data_element_value;
1626 Elsif l_seqnum_rec.seq_num = 21 Then
1627 p_ext_dtl_rec.val_21 := p_data_element_value;
1628 Elsif l_seqnum_rec.seq_num = 22 Then
1629 p_ext_dtl_rec.val_22 := p_data_element_value;
1630 Elsif l_seqnum_rec.seq_num = 23Then
1631 p_ext_dtl_rec.val_23 := p_data_element_value;
1632 Elsif l_seqnum_rec.seq_num = 24 Then
1633 p_ext_dtl_rec.val_24 := p_data_element_value;
1634 Elsif l_seqnum_rec.seq_num = 25 Then
1635 p_ext_dtl_rec.val_25 := p_data_element_value;
1636 Elsif l_seqnum_rec.seq_num = 26 Then
1637 p_ext_dtl_rec.val_26 := p_data_element_value;
1638 Elsif l_seqnum_rec.seq_num = 27 Then
1639 p_ext_dtl_rec.val_27 := p_data_element_value;
1640 Elsif l_seqnum_rec.seq_num = 28 Then
1641 p_ext_dtl_rec.val_28 := p_data_element_value;
1642 Elsif l_seqnum_rec.seq_num = 29 Then
1643 p_ext_dtl_rec.val_29 := p_data_element_value;
1644 Elsif l_seqnum_rec.seq_num = 30 Then
1645 p_ext_dtl_rec.val_30 := p_data_element_value;
1646 Elsif l_seqnum_rec.seq_num = 31 Then
1647 p_ext_dtl_rec.val_31 := p_data_element_value;
1648 Elsif l_seqnum_rec.seq_num = 32 Then
1649 p_ext_dtl_rec.val_32 := p_data_element_value;
1650 Elsif l_seqnum_rec.seq_num = 33 Then
1651 p_ext_dtl_rec.val_33 := p_data_element_value;
1652 Elsif l_seqnum_rec.seq_num = 34 Then
1653 p_ext_dtl_rec.val_34 := p_data_element_value;
1654 Elsif l_seqnum_rec.seq_num = 35 Then
1655 p_ext_dtl_rec.val_35 := p_data_element_value;
1656 Elsif l_seqnum_rec.seq_num = 36 Then
1657 p_ext_dtl_rec.val_36 := p_data_element_value;
1658 Elsif l_seqnum_rec.seq_num = 37 Then
1659 p_ext_dtl_rec.val_37 := p_data_element_value;
1660 Elsif l_seqnum_rec.seq_num = 38 Then
1661 p_ext_dtl_rec.val_38 := p_data_element_value;
1662 Elsif l_seqnum_rec.seq_num = 39 Then
1663 p_ext_dtl_rec.val_39 := p_data_element_value;
1664 Elsif l_seqnum_rec.seq_num = 40 Then
1665 p_ext_dtl_rec.val_40 := p_data_element_value;
1666 Elsif l_seqnum_rec.seq_num = 41 Then
1667 p_ext_dtl_rec.val_41 := p_data_element_value;
1668 Elsif l_seqnum_rec.seq_num = 42 Then
1669 p_ext_dtl_rec.val_42 := p_data_element_value;
1670 Elsif l_seqnum_rec.seq_num = 43 Then
1671 p_ext_dtl_rec.val_43 := p_data_element_value;
1672 Elsif l_seqnum_rec.seq_num = 44 Then
1673 p_ext_dtl_rec.val_44 := p_data_element_value;
1674 Elsif l_seqnum_rec.seq_num = 45 Then
1675 p_ext_dtl_rec.val_45 := p_data_element_value;
1676 Elsif l_seqnum_rec.seq_num = 46 Then
1677 p_ext_dtl_rec.val_46 := p_data_element_value;
1678 Elsif l_seqnum_rec.seq_num = 47 Then
1679 p_ext_dtl_rec.val_47 := p_data_element_value;
1680 Elsif l_seqnum_rec.seq_num = 48 Then
1681 p_ext_dtl_rec.val_48 := p_data_element_value;
1682 Elsif l_seqnum_rec.seq_num = 49 Then
1683 p_ext_dtl_rec.val_49 := p_data_element_value;
1684 Elsif l_seqnum_rec.seq_num = 50 Then
1685 p_ext_dtl_rec.val_50 := p_data_element_value;
1686 Elsif l_seqnum_rec.seq_num = 51 Then
1687 p_ext_dtl_rec.val_51 := p_data_element_value;
1688 Elsif l_seqnum_rec.seq_num = 52 Then
1689 p_ext_dtl_rec.val_52 := p_data_element_value;
1690 Elsif l_seqnum_rec.seq_num = 53 Then
1691 p_ext_dtl_rec.val_53 := p_data_element_value;
1692 Elsif l_seqnum_rec.seq_num = 54 Then
1693 p_ext_dtl_rec.val_54 := p_data_element_value;
1694 Elsif l_seqnum_rec.seq_num = 55 Then
1695 p_ext_dtl_rec.val_55 := p_data_element_value;
1696 Elsif l_seqnum_rec.seq_num = 56 Then
1697 p_ext_dtl_rec.val_56 := p_data_element_value;
1698 Elsif l_seqnum_rec.seq_num = 57 Then
1699 p_ext_dtl_rec.val_57 := p_data_element_value;
1700 Elsif l_seqnum_rec.seq_num = 58 Then
1701 p_ext_dtl_rec.val_58 := p_data_element_value;
1702 Elsif l_seqnum_rec.seq_num = 58 Then
1703 p_ext_dtl_rec.val_58 := p_data_element_value;
1704 Elsif l_seqnum_rec.seq_num = 59 Then
1705 p_ext_dtl_rec.val_59 := p_data_element_value;
1706 Elsif l_seqnum_rec.seq_num = 60 Then
1707 p_ext_dtl_rec.val_60 := p_data_element_value;
1708 Elsif l_seqnum_rec.seq_num = 61 Then
1709 p_ext_dtl_rec.val_61 := p_data_element_value;
1710 Elsif l_seqnum_rec.seq_num = 62 Then
1711 p_ext_dtl_rec.val_62 := p_data_element_value;
1712 Elsif l_seqnum_rec.seq_num = 63 Then
1713 p_ext_dtl_rec.val_63 := p_data_element_value;
1714 Elsif l_seqnum_rec.seq_num = 64 Then
1715 p_ext_dtl_rec.val_64 := p_data_element_value;
1716 Elsif l_seqnum_rec.seq_num = 65 Then
1717 p_ext_dtl_rec.val_65 := p_data_element_value;
1718 Elsif l_seqnum_rec.seq_num = 66 Then
1719 p_ext_dtl_rec.val_66 := p_data_element_value;
1720 Elsif l_seqnum_rec.seq_num = 67 Then
1721 p_ext_dtl_rec.val_67 := p_data_element_value;
1722 Elsif l_seqnum_rec.seq_num = 68 Then
1723 p_ext_dtl_rec.val_68 := p_data_element_value;
1724 Elsif l_seqnum_rec.seq_num = 69 Then
1725 p_ext_dtl_rec.val_69 := p_data_element_value;
1726 Elsif l_seqnum_rec.seq_num = 70 Then
1727 p_ext_dtl_rec.val_70 := p_data_element_value;
1728 Elsif l_seqnum_rec.seq_num = 71 Then
1729 p_ext_dtl_rec.val_71 := p_data_element_value;
1730 Elsif l_seqnum_rec.seq_num = 72 Then
1731 p_ext_dtl_rec.val_72 := p_data_element_value;
1732 Elsif l_seqnum_rec.seq_num = 73 Then
1733 p_ext_dtl_rec.val_73 := p_data_element_value;
1734 Elsif l_seqnum_rec.seq_num = 74 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);
1744 -- nocopy changes
1745 p_ext_dtl_rec := l_ext_dtl_rec_nc;
1746 raise;
1747
1748 End Update_Record_Values;
1749
1750 -- ================================================================================
1751 -- ~ Ins_Rslt_Dtl : Inserts a record into the results detail record.
1752 -- ================================================================================
1753 Procedure Ins_Rslt_Dtl(p_dtl_rec IN OUT NOCOPY ben_ext_rslt_dtl%ROWTYPE) Is
1754
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
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
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
1774 ,BUSINESS_GROUP_ID
1775 ,EXT_RCD_ID
1776 ,PERSON_ID
1777 ,VAL_01
1778 ,VAL_02
1779 ,VAL_03
1780 ,VAL_04
1781 ,VAL_05
1782 ,VAL_06
1783 ,VAL_07
1784 ,VAL_08
1785 ,VAL_09
1786 ,VAL_10
1787 ,VAL_11
1788 ,VAL_12
1789 ,VAL_13
1790 ,VAL_14
1791 ,VAL_15
1792 ,VAL_16
1793 ,VAL_17
1794 ,VAL_19
1795 ,VAL_18
1796 ,VAL_20
1797 ,VAL_21
1798 ,VAL_22
1799 ,VAL_23
1800 ,VAL_24
1801 ,VAL_25
1802 ,VAL_26
1803 ,VAL_27
1804 ,VAL_28
1805 ,VAL_29
1806 ,VAL_30
1807 ,VAL_31
1808 ,VAL_32
1809 ,VAL_33
1810 ,VAL_34
1811 ,VAL_35
1812 ,VAL_36
1813 ,VAL_37
1814 ,VAL_38
1815 ,VAL_39
1816 ,VAL_40
1817 ,VAL_41
1818 ,VAL_42
1819 ,VAL_43
1820 ,VAL_44
1821 ,VAL_45
1822 ,VAL_46
1823 ,VAL_47
1824 ,VAL_48
1825 ,VAL_49
1826 ,VAL_50
1827 ,VAL_51
1828 ,VAL_52
1829 ,VAL_53
1830 ,VAL_54
1831 ,VAL_55
1832 ,VAL_56
1833 ,VAL_57
1834 ,VAL_58
1835 ,VAL_59
1836 ,VAL_60
1837 ,VAL_61
1838 ,VAL_62
1839 ,VAL_63
1840 ,VAL_64
1841 ,VAL_65
1842 ,VAL_66
1843 ,VAL_67
1844 ,VAL_68
1845 ,VAL_69
1846 ,VAL_70
1847 ,VAL_71
1848 ,VAL_72
1849 ,VAL_73
1850 ,VAL_74
1851 ,VAL_75
1852 ,CREATED_BY
1853 ,CREATION_DATE
1854 ,LAST_UPDATE_DATE
1855 ,LAST_UPDATED_BY
1856 ,LAST_UPDATE_LOGIN
1857 ,PROGRAM_APPLICATION_ID
1858 ,PROGRAM_ID
1859 ,PROGRAM_UPDATE_DATE
1860 ,REQUEST_ID
1861 ,OBJECT_VERSION_NUMBER
1862 ,PRMY_SORT_VAL
1863 ,SCND_SORT_VAL
1864 ,THRD_SORT_VAL
1865 ,TRANS_SEQ_NUM
1866 ,RCRD_SEQ_NUM
1867 )
1868 VALUES
1869 (p_dtl_rec.EXT_RSLT_DTL_ID
1870 ,p_dtl_rec.EXT_RSLT_ID
1871 ,p_dtl_rec.BUSINESS_GROUP_ID
1872 ,p_dtl_rec.EXT_RCD_ID
1873 ,p_dtl_rec.PERSON_ID
1874 ,p_dtl_rec.VAL_01
1875 ,p_dtl_rec.VAL_02
1876 ,p_dtl_rec.VAL_03
1877 ,p_dtl_rec.VAL_04
1878 ,p_dtl_rec.VAL_05
1879 ,p_dtl_rec.VAL_06
1880 ,p_dtl_rec.VAL_07
1881 ,p_dtl_rec.VAL_08
1882 ,p_dtl_rec.VAL_09
1883 ,p_dtl_rec.VAL_10
1884 ,p_dtl_rec.VAL_11
1885 ,p_dtl_rec.VAL_12
1886 ,p_dtl_rec.VAL_13
1887 ,p_dtl_rec.VAL_14
1888 ,p_dtl_rec.VAL_15
1889 ,p_dtl_rec.VAL_16
1890 ,p_dtl_rec.VAL_17
1891 ,p_dtl_rec.VAL_19
1892 ,p_dtl_rec.VAL_18
1893 ,p_dtl_rec.VAL_20
1894 ,p_dtl_rec.VAL_21
1895 ,p_dtl_rec.VAL_22
1896 ,p_dtl_rec.VAL_23
1897 ,p_dtl_rec.VAL_24
1898 ,p_dtl_rec.VAL_25
1899 ,p_dtl_rec.VAL_26
1900 ,p_dtl_rec.VAL_27
1901 ,p_dtl_rec.VAL_28
1902 ,p_dtl_rec.VAL_29
1903 ,p_dtl_rec.VAL_30
1904 ,p_dtl_rec.VAL_31
1905 ,p_dtl_rec.VAL_32
1906 ,p_dtl_rec.VAL_33
1907 ,p_dtl_rec.VAL_34
1908 ,p_dtl_rec.VAL_35
1909 ,p_dtl_rec.VAL_36
1910 ,p_dtl_rec.VAL_37
1911 ,p_dtl_rec.VAL_38
1912 ,p_dtl_rec.VAL_39
1913 ,p_dtl_rec.VAL_40
1914 ,p_dtl_rec.VAL_41
1915 ,p_dtl_rec.VAL_42
1916 ,p_dtl_rec.VAL_43
1917 ,p_dtl_rec.VAL_44
1918 ,p_dtl_rec.VAL_45
1919 ,p_dtl_rec.VAL_46
1920 ,p_dtl_rec.VAL_47
1921 ,p_dtl_rec.VAL_48
1922 ,p_dtl_rec.VAL_49
1923 ,p_dtl_rec.VAL_50
1924 ,p_dtl_rec.VAL_51
1925 ,p_dtl_rec.VAL_52
1926 ,p_dtl_rec.VAL_53
1927 ,p_dtl_rec.VAL_54
1928 ,p_dtl_rec.VAL_55
1929 ,p_dtl_rec.VAL_56
1930 ,p_dtl_rec.VAL_57
1931 ,p_dtl_rec.VAL_58
1932 ,p_dtl_rec.VAL_59
1933 ,p_dtl_rec.VAL_60
1934 ,p_dtl_rec.VAL_61
1935 ,p_dtl_rec.VAL_62
1936 ,p_dtl_rec.VAL_63
1937 ,p_dtl_rec.VAL_64
1938 ,p_dtl_rec.VAL_65
1939 ,p_dtl_rec.VAL_66
1940 ,p_dtl_rec.VAL_67
1941 ,p_dtl_rec.VAL_68
1942 ,p_dtl_rec.VAL_69
1943 ,p_dtl_rec.VAL_70
1944 ,p_dtl_rec.VAL_71
1945 ,p_dtl_rec.VAL_72
1946 ,p_dtl_rec.VAL_73
1947 ,p_dtl_rec.VAL_74
1948 ,p_dtl_rec.VAL_75
1949 ,p_dtl_rec.CREATED_BY
1950 ,p_dtl_rec.CREATION_DATE
1951 ,p_dtl_rec.LAST_UPDATE_DATE
1952 ,p_dtl_rec.LAST_UPDATED_BY
1953 ,p_dtl_rec.LAST_UPDATE_LOGIN
1954 ,p_dtl_rec.PROGRAM_APPLICATION_ID
1955 ,p_dtl_rec.PROGRAM_ID
1956 ,p_dtl_rec.PROGRAM_UPDATE_DATE
1957 ,p_dtl_rec.REQUEST_ID
1958 ,p_dtl_rec.OBJECT_VERSION_NUMBER
1959 ,p_dtl_rec.PRMY_SORT_VAL
1960 ,p_dtl_rec.SCND_SORT_VAL
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
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
1976 -- ================================================================================
1977 -- ~ Upd_Rslt_Dtl : Updates the primary assignment record in results detail table
1978 -- ================================================================================
1979 Procedure Upd_Rslt_Dtl(p_dtl_rec IN ben_ext_rslt_dtl%ROWTYPE ) Is
1980
1981 l_proc_name varchar2(150):= g_proc_name||'upd_rslt_dtl';
1982
1983 Begin -- Upd_Rslt_Dtl
1984 UPDATE ben_ext_rslt_dtl
1985 SET VAL_01 = p_dtl_rec.VAL_01
1986 ,VAL_02 = p_dtl_rec.VAL_02
1987 ,VAL_03 = p_dtl_rec.VAL_03
1988 ,VAL_04 = p_dtl_rec.VAL_04
1989 ,VAL_05 = p_dtl_rec.VAL_05
1990 ,VAL_06 = p_dtl_rec.VAL_06
1991 ,VAL_07 = p_dtl_rec.VAL_07
1992 ,VAL_08 = p_dtl_rec.VAL_08
1993 ,VAL_09 = p_dtl_rec.VAL_09
1994 ,VAL_10 = p_dtl_rec.VAL_10
1995 ,VAL_11 = p_dtl_rec.VAL_11
1996 ,VAL_12 = p_dtl_rec.VAL_12
1997 ,VAL_13 = p_dtl_rec.VAL_13
1998 ,VAL_14 = p_dtl_rec.VAL_14
1999 ,VAL_15 = p_dtl_rec.VAL_15
2000 ,VAL_16 = p_dtl_rec.VAL_16
2001 ,VAL_17 = p_dtl_rec.VAL_17
2002 ,VAL_19 = p_dtl_rec.VAL_19
2003 ,VAL_18 = p_dtl_rec.VAL_18
2004 ,VAL_20 = p_dtl_rec.VAL_20
2005 ,VAL_21 = p_dtl_rec.VAL_21
2006 ,VAL_22 = p_dtl_rec.VAL_22
2007 ,VAL_23 = p_dtl_rec.VAL_23
2008 ,VAL_24 = p_dtl_rec.VAL_24
2009 ,VAL_25 = p_dtl_rec.VAL_25
2010 ,VAL_26 = p_dtl_rec.VAL_26
2011 ,VAL_27 = p_dtl_rec.VAL_27
2012 ,VAL_28 = p_dtl_rec.VAL_28
2013 ,VAL_29 = p_dtl_rec.VAL_29
2014 ,VAL_30 = p_dtl_rec.VAL_30
2015 ,VAL_31 = p_dtl_rec.VAL_31
2016 ,VAL_32 = p_dtl_rec.VAL_32
2017 ,VAL_33 = p_dtl_rec.VAL_33
2018 ,VAL_34 = p_dtl_rec.VAL_34
2019 ,VAL_35 = p_dtl_rec.VAL_35
2020 ,VAL_36 = p_dtl_rec.VAL_36
2021 ,VAL_37 = p_dtl_rec.VAL_37
2022 ,VAL_38 = p_dtl_rec.VAL_38
2023 ,VAL_39 = p_dtl_rec.VAL_39
2024 ,VAL_40 = p_dtl_rec.VAL_40
2025 ,VAL_41 = p_dtl_rec.VAL_41
2026 ,VAL_42 = p_dtl_rec.VAL_42
2027 ,VAL_43 = p_dtl_rec.VAL_43
2028 ,VAL_44 = p_dtl_rec.VAL_44
2029 ,VAL_45 = p_dtl_rec.VAL_45
2030 ,VAL_46 = p_dtl_rec.VAL_46
2031 ,VAL_47 = p_dtl_rec.VAL_47
2032 ,VAL_48 = p_dtl_rec.VAL_48
2033 ,VAL_49 = p_dtl_rec.VAL_49
2034 ,VAL_50 = p_dtl_rec.VAL_50
2035 ,VAL_51 = p_dtl_rec.VAL_51
2036 ,VAL_52 = p_dtl_rec.VAL_52
2037 ,VAL_53 = p_dtl_rec.VAL_53
2038 ,VAL_54 = p_dtl_rec.VAL_54
2039 ,VAL_55 = p_dtl_rec.VAL_55
2040 ,VAL_56 = p_dtl_rec.VAL_56
2041 ,VAL_57 = p_dtl_rec.VAL_57
2042 ,VAL_58 = p_dtl_rec.VAL_58
2043 ,VAL_59 = p_dtl_rec.VAL_59
2044 ,VAL_60 = p_dtl_rec.VAL_60
2045 ,VAL_61 = p_dtl_rec.VAL_61
2046 ,VAL_62 = p_dtl_rec.VAL_62
2047 ,VAL_63 = p_dtl_rec.VAL_63
2048 ,VAL_64 = p_dtl_rec.VAL_64
2049 ,VAL_65 = p_dtl_rec.VAL_65
2050 ,VAL_66 = p_dtl_rec.VAL_66
2051 ,VAL_67 = p_dtl_rec.VAL_67
2052 ,VAL_68 = p_dtl_rec.VAL_68
2053 ,VAL_69 = p_dtl_rec.VAL_69
2054 ,VAL_70 = p_dtl_rec.VAL_70
2055 ,VAL_71 = p_dtl_rec.VAL_71
2056 ,VAL_72 = p_dtl_rec.VAL_72
2057 ,VAL_73 = p_dtl_rec.VAL_73
2058 ,VAL_74 = p_dtl_rec.VAL_74
2059 ,VAL_75 = p_dtl_rec.VAL_75
2060 ,OBJECT_VERSION_NUMBER = p_dtl_rec.OBJECT_VERSION_NUMBER
2061 ,THRD_SORT_VAL = p_dtl_rec.THRD_SORT_VAL
2062 WHERE ext_rslt_dtl_id = p_dtl_rec.ext_rslt_dtl_id;
2063
2064 Return;
2065
2066 Exception
2067 When Others Then
2068 Raise;
2069 END Upd_Rslt_Dtl;
2070
2071 -- ====================================================================
2072 -- ~ Del_Service_Detail_Recs : Delete all the records created as part
2073 -- ~ of hidden record as they are not required.
2074 -- ====================================================================
2075 Function Del_Service_Detail_Recs (p_business_group_id ben_ext_rslt_dtl.business_group_id%TYPE
2076 )Return Number Is
2077
2078 l_ext_dtl_rcd_id ben_ext_rcd.ext_rcd_id%TYPE;
2079 l_ext_main_rcd_id ben_ext_rcd.ext_rcd_id%TYPE;
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
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
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
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
2112 End PQP_US_SRS_Extracts; -- End Of Package Body