1 package body PQP_US_Student_BEE as
2 /* $Header: pqusstbe.pkb 120.0 2005/05/29 02:14:39 appldev noship $
3 +============================================================================+
4 | Copyright (c) Oracle Corporation 1991,1992,1993. All rights reserved |
5 | |
6 | Description : Package and procedures to support Batch Element Entry |
7 | process for Student Eearnings. |
8 | |
9 | Change List |
10 +=============+========+=======+========+====================================+
11 | Date |Name |Ver |Bug No |Description |
12 +=============+========+=======+========+====================================+
13 | 23-SEP-2004 |tmehra |115.0 | |Created |
14 | 10-DEC-2004 |hgattu |115.5 |4094250 | |
15 | 14-FEB-2005 |hgattu |115.6 |4180797 | |
16 | | | |4181127 | |
17 | 18-FEB-2005 |hgattu |115.7 | | Aligned |
18 | 07-MAR-2005 |rpinjala|115.9 |4219848 | |
19 | 25-APR-2005 |rpinjala|115.11 | |Added loop for processing multiple |
20 | | | | |awards for a student. |
21 | 26-APR-2005 |rpinjala|115.12 | |removed the close stu_cur cursor. |
22 | 26-APR-2005 |rpinjala|115.12 |4350673 |Default values for the input values |
23 | | | | |Ded. Processing and separate check |
24 | | | | | |
25 | | | | | |
26 +=============+========+=======+========+====================================+
27 */
28
29 -- =============================================================================
30 -- Package body Global Variables
31 -- =============================================================================
32 g_debug boolean;
33 g_pkg constant varchar2(150) :='PQP_US_Student_BEE.';
34 -- =============================================================================
35 -- Package body Cursors
36 -- =============================================================================
37
38 -- Cursor to get lookup meaning.
39 cursor csr_lookup_meaning
40 (p_lookup_type in varchar2
41 ,p_lookup_code in varchar2)is
42 select meaning
43 from fnd_lookup_values_vl
44 where lookup_type = p_lookup_type
45 and upper(lookup_code) = upper(p_lookup_code);
46
47 -- Cursor to get the input value id(s) for an element.
48 cursor csr_ipv_id (c_ele_type_id in number
49 ,c_effective_date in date) is
50 select piv.input_value_id
51 ,piv.name
52 ,piv.display_sequence
53 ,piv.lookup_type
54 ,piv.max_value
55 ,piv.min_value
56 ,piv.default_value
57 ,piv.warning_or_error
58 from pay_input_values_f piv
59 where piv.element_type_id = c_ele_type_id
60 and c_effective_date between piv.effective_start_date
61 and piv.effective_end_date
62 order by piv.display_sequence;
63 -- Cursor to get all Student Earnings Element in a given date range.
64 Cursor csr_chk_ele (c_assignment_id in number
65 ,c_bg_id in number
66 ,c_start_date in date
67 ,c_end_date in date ) is
68 Select distinct
69 pel.element_type_id
70 ,pee.element_entry_id
71 ,pel.element_link_id
72 From pay_element_entries_f pee
73 ,pay_element_links_f pel
74 ,per_all_assignments_f paf
75 Where (c_end_date between pee.effective_start_date
76 and pee.effective_end_date
77 or
78 pee.effective_end_date between c_start_date
79 and c_end_date
80 )
81 and pee.assignment_id = c_assignment_id
82 and (c_end_date between pel.effective_start_date
83 and pel.effective_end_date
84 or
85 pel.effective_end_date between c_start_date
86 and c_end_date
87 )
88 and pee.element_link_id = pel.element_link_id
89 and pel.element_type_id in
90 (Select pet.element_type_id
91 From pay_element_types_f pet
92 Where pet.element_information_category = 'US_EARNINGS'
93 and pet.business_group_id = c_bg_id
94 and pet.element_information1 ='SE'
95 and c_end_date between pet.effective_start_date
96 and pet.effective_end_date)
97 and paf.assignment_id = pee.assignment_id
98 and paf.business_group_id = c_bg_id
99 and pel.business_group_id = c_bg_id
100 and c_end_date between paf.effective_start_date
101 and paf.effective_end_date
102 order by pee.element_entry_id desc;
103
104 -- Cursor to get the Screen entry value for the input values
105 Cursor csr_entry_val (c_element_entry_id in Number
106 ,c_start_date in Date
107 ,c_end_date in Date
108 ,c_input_value_id in Number
109 ) Is
110 Select pev.screen_entry_value
111 from pay_element_entry_values_f pev
112 where pev.input_value_id = c_input_value_id
113 and pev.element_entry_id = c_element_entry_id
114 and (c_end_date between pev.effective_start_date
115 and pev.effective_end_date
116 or
117 (pev.effective_end_date >= c_start_date and
118 pev.effective_start_date <= c_end_date)
119 )
120 order by pev.effective_start_date desc;
121
122 -- Get the ipv id for an ipv name of an element
123 cursor ipv_id (c_ele_type_id in number
124 ,c_ipv_name in varchar2) is
125 select piv.input_value_id
126 ,piv.name
127 from pay_input_values_f piv
128 where piv.element_type_id = c_ele_type_id
129 and piv.name = c_ipv_name
130 order by piv.display_sequence;
131 -- Get the latest start and end date for the element entry id.
132 Cursor entry_date (c_element_entry_id in Number
133 ,c_assignment_id in Number) is
134 select max(pee.effective_start_date)
135 ,max(pee.effective_end_date)
136 from pay_element_entries_f pee
137 where pee.assignment_id = c_assignment_id
138 and pee.element_entry_id = c_element_entry_id;
139
140 -- Define the record structure for holding the elements input names.
141 type input_values_rec is record
142 (input_value_id number(10)
143 ,name pay_input_values_f.name%type
144 ,screen_entry_value pay_element_entry_values_f.screen_entry_value%type
145 ,display_sequence pay_input_values_f.display_sequence%type
146 ,lookup_type pay_input_values_f.lookup_type%type
147 ,default_value pay_input_values_f.default_value%type
148 ,max_value pay_input_values_f.max_value%type
149 ,min_value pay_input_values_f.min_value%type
150 ,warning_or_error pay_input_values_f.warning_or_error%type
151 );
152 -- Record type declaration
153 type t_input_values is table of input_values_rec
154 index by binary_integer;
155
156 -- =============================================================================
157 -- Get_Lookup_Meaning: function returns the values of the look up meaning
158 -- =============================================================================
159 function Get_Lookup_Meaning
160 (p_lookup_type in varchar2
161 ,p_lookup_code in varchar2
162 ) return varchar2 is
163 l_lookup_meaning fnd_lookup_values_vl.meaning%type;
164 l_proc_name varchar2(150) := g_pkg ||'Get_Lookup_Meaning';
165 begin
166 hr_utility.set_location('Entering: '||l_proc_name, 5);
167 open csr_lookup_meaning
168 (p_lookup_type => p_lookup_type
169 ,p_lookup_code => p_lookup_code);
170 fetch csr_lookup_meaning into l_lookup_meaning;
171 close csr_lookup_meaning;
172 hr_utility.set_location('Leaving: '||l_proc_name, 80);
173 return l_lookup_meaning;
174
175 end Get_Lookup_Meaning;
176 -- =============================================================================
177 -- Check_Input_Values:
178 -- =============================================================================
179 Procedure Check_Input_Values(p_ipv_rec_new In Out NoCOpy t_input_values
180 ,p_ipv_rec_old In Out NoCOpy t_input_values
181 ) Is
182
183 l_proc_name varchar2(150) := g_pkg ||'Check_Input_Values';
184
185 Begin
186 hr_utility.set_location('Entering: '||l_proc_name, 5);
187 For i in 1..15
188 Loop
189 For j in 1..15
190 Loop
191 If Upper(p_ipv_rec_old(i).name) = Upper(p_ipv_rec_new(j).name) Then
192 If p_ipv_rec_new(j).screen_entry_value is Null Then
193 hr_utility.set_location('Old IPV: '||p_ipv_rec_old(i).screen_entry_value, 5);
194 hr_utility.set_location('New IPV: '||p_ipv_rec_new(j).screen_entry_value, 5);
195 p_ipv_rec_new(j).screen_entry_value
196 := p_ipv_rec_old(i).screen_entry_value;
197 Exit;
198 End If;
199 End If;
200 End Loop;
201 End Loop;
202 hr_utility.set_location('Leaving: '||l_proc_name, 60);
203 End Check_Input_Values;
204 -- =============================================================================
205 -- ~ Init_Ipv_Rec: Used to re-set the values of the input names, before using it
206 -- ~ another element type id.
207 -- =============================================================================
208 Procedure Init_Ipv_Rec
209 (p_ipv_rec_old in out nocopy t_input_values) is
210
211 Begin
212 for i in 1..15
213 loop
214 p_ipv_rec_old(i).screen_entry_value := null;
215 p_ipv_rec_old(i).input_value_id := null;
216 p_ipv_rec_old(i).name := null;
217 p_ipv_rec_old(i).display_sequence := null;
218 p_ipv_rec_old(i).lookup_type := null;
219 p_ipv_rec_old(i).default_value := null;
220 p_ipv_rec_old(i).max_value := null;
221 p_ipv_rec_old(i).min_value := null;
222 end loop;
223
224 End Init_Ipv_Rec;
225 -- =============================================================================
226 -- ~ Chk_If_Entry_Exists: Check if entry exist for the award ID already.
227 -- =============================================================================
228 procedure Chk_If_Entry_Exists
229 (p_assignment_id in number
230 ,p_business_group_id in number
231 ,p_effective_date in date
232 ,p_element_type_id in number
233 ,p_auth_id in varchar2
234 ,p_award_max_amt in number
235 ,p_ipv_val_tab in out nocopy t_input_values
236 ,p_award_paid in out nocopy boolean
237 ,p_award_amt_adj in out nocopy boolean
238 ) is
239
240 l_ipv_rec_old t_input_values;
241
242 l_proc_name constant varchar2(150) := g_pkg||'Chk_If_Entry_Exists';
243 l_start_date Date;
244 l_end_date Date;
245 l_ele_entry_st_date Date;
246 l_ele_entry_ed_date Date;
247 l_new_Maxdiff_amt Number;
248 l_authId_ipv_id pay_input_values_f.input_value_id%TYPE;
249 l_awdId_name pay_input_values_f.name%TYPE;
250 l_authId_ipv_value pay_element_entry_values_f.screen_entry_value%TYPE;
251
252 l_maxAmt_ipvId pay_input_values_f.input_value_id%TYPE;
253 l_maxAmt_name pay_input_values_f.name%TYPE;
254 l_maxAmt_ipv_value pay_element_entry_values_f.screen_entry_value%TYPE;
255 l_count number;
256
257 Begin
258
259 hr_utility.set_location('Entering: '||l_proc_name, 5);
260 p_award_paid := false;
261 p_award_amt_adj := false;
262 -- We have to back one year for the assignment to see if the same auth id
263 -- was already given to the student and end dated already.
264 l_start_date := add_months(p_effective_date,-12);
265 l_end_date := p_effective_date;
266 l_count := 0;
267
268 Init_Ipv_Rec(l_ipv_rec_old);
269
270 hr_utility.set_location(' l_start_date: '|| l_start_date,6);
271 hr_utility.set_location(' l_end_date: '|| l_end_date,6);
272
273 -- Check for all the past one years Student Earnings to see if the student
274 -- has been paid for this work authorization already.
275 For prv_awds in csr_chk_ele
276 (c_assignment_id => p_assignment_id
277 ,c_bg_id => p_business_group_id
278 ,c_start_date => l_start_date
279 ,c_end_date => l_end_date )
280 Loop
281 -- Check if the element has an input call Authorization ID
282 If g_debug Then
283 hr_utility.set_location(' Element Type Id: '|| prv_awds.element_type_id,6);
284 hr_utility.set_location(' Element Entry Id: '|| prv_awds.element_entry_id,6);
285 hr_utility.set_location(' Element Link Id: '|| prv_awds.element_link_id,6);
286 End If;
287 Open ipv_id (prv_awds.element_type_id
288 ,'Authorization ID');
289 Fetch ipv_id Into l_authId_ipv_id, l_awdId_name;
290 Close ipv_id;
291
292 Open ipv_id (prv_awds.element_type_id
293 ,'Maximum Amount');
294 Fetch ipv_id Into l_maxAmt_ipvId,l_maxAmt_name;
295 Close ipv_id;
296 If g_debug Then
297 hr_utility.set_location(' l_maxAmt_name: '|| l_maxAmt_name,9);
298 hr_utility.set_location(' l_maxAmt_ipvId: '|| l_maxAmt_ipvId,9);
299 hr_utility.set_location(' l_awdId_name: '|| l_awdId_name,7);
300 hr_utility.set_location(' l_authId_ipv_id: '|| l_authId_ipv_id,7);
301 End If;
302 If l_maxAmt_ipvId Is Null or l_authId_ipv_id Is Null Then
303 Goto Next_Element;
304 End If;
305 -- Get the Auth Id screen entry value
306 Open csr_entry_val (c_element_entry_id => prv_awds.element_entry_id
307 ,c_start_date => l_start_date
308 ,c_end_date => l_end_date
309 ,c_input_value_id => l_authId_ipv_id
310 );
311 Fetch csr_entry_val Into l_authId_ipv_value;
312 Close csr_entry_val;
313 hr_utility.set_location(' l_authId_ipv_value: '|| l_authId_ipv_value,8);
314
315 -- Get the Max. Auth Amount screen entry value
316 Open csr_entry_val(c_element_entry_id => prv_awds.element_entry_id
317 ,c_start_date => l_start_date
318 ,c_end_date => l_end_date
319 ,c_input_value_id => l_maxAmt_ipvId);
320 Fetch csr_entry_val Into l_maxAmt_ipv_value;
321 Close csr_entry_val;
322 hr_utility.set_location(' l_maxAmt_ipv_value: '|| l_maxAmt_ipv_value,10);
323
324 -- Check if the Student had already gotten the authorization paid in the
325 -- past one year.
326 If l_authId_ipv_value is not null and
327 l_authId_ipv_value <> p_auth_id Then
328 Goto Next_Element;
329 End If;
330
331 -- Get the start and end date for the element entry id.
335 Close entry_date;
332 Open entry_date(prv_awds.element_entry_id
333 ,p_assignment_id);
334 Fetch entry_date Into l_ele_entry_st_date, l_ele_entry_ed_date;
336 If g_debug Then
337 hr_utility.set_location(' l_ele_entry_st_date: '|| l_ele_entry_st_date,10);
338 hr_utility.set_location(' l_ele_entry_ed_date: '|| l_ele_entry_ed_date,10);
339 End If;
340 -- Auth Id are same, now check the amounts.
341 If p_award_max_amt > to_number(l_maxAmt_ipv_value) and
342 l_ele_entry_ed_date < l_end_date Then
343 l_new_Maxdiff_amt := (p_award_max_amt - to_number(l_maxAmt_ipv_value));
344 p_award_amt_adj := True;
345 hr_utility.set_location(' p_award_amt_adj: TRUE', 11);
346 For i in 1..15
347 Loop
348 if p_ipv_val_tab(i).name ='Maximum Amount' Then
349 p_ipv_val_tab(i).screen_entry_value := l_new_Maxdiff_amt;
350 exit;
351 End if;
352 End Loop;
353 Elsif p_award_max_amt = to_number(l_maxAmt_ipv_value) and
354 l_ele_entry_ed_date < l_end_date Then
355 p_award_paid := True;
356 hr_utility.set_location(' p_award_paid: TRUE', 11);
357 Exit;
358 Elsif p_award_max_amt = to_number(l_maxAmt_ipv_value) and
359 p_effective_date Between l_ele_entry_st_date and
360 l_ele_entry_ed_date Then
361 hr_utility.set_location(' Entry Already Exists', 11);
362 p_award_paid := True;
363 Exit;
364 End If;
365
366 If p_element_type_id = prv_awds.element_type_id Then
367 If g_debug Then
368 hr_utility.set_location(' p_element_type_id: '||p_element_type_id, 12);
369 hr_utility.set_location(' prv_awds.element_type_id: '||prv_awds.element_type_id, 12);
370 End If;
371 l_ipv_rec_old := p_ipv_val_tab;
372 Else
373 -- Means the element type is diff. even though the both the
374 -- the same name for auth id and max amount.
375 For ipv_rec in csr_ipv_id
376 (c_ele_type_id => prv_awds.element_type_id
377 ,c_effective_date => p_effective_date)
378 Loop
379 l_count := l_count + 1;
380 l_ipv_rec_old(l_count).input_value_id := ipv_rec.input_value_id;
381 l_ipv_rec_old(l_count).name := ipv_rec.name;
382 l_ipv_rec_old(l_count).display_sequence := ipv_rec.display_sequence;
383 l_ipv_rec_old(l_count).lookup_type := ipv_rec.lookup_type;
384 l_ipv_rec_old(l_count).default_value := ipv_rec.default_value;
385 l_ipv_rec_old(l_count).max_value := ipv_rec.max_value;
386 l_ipv_rec_old(l_count).min_value := ipv_rec.min_value;
387 l_ipv_rec_old(l_count).warning_or_error := ipv_rec.warning_or_error;
388 End Loop;
389 l_count := 0;
390 End If; -- If p_element_type_id
391 --
392 For i in 1..15
393 Loop
394 Open csr_entry_val
395 (c_element_entry_id => prv_awds.element_entry_id
396 ,c_start_date => l_ele_entry_st_date
397 ,c_end_date => l_ele_entry_ed_date
398 ,c_input_value_id => l_ipv_rec_old(i).input_value_id);
399 Fetch csr_entry_val Into l_ipv_rec_old(i).screen_entry_value;
400 If g_debug Then
401 hr_utility.set_location(' IPV Name: '||l_ipv_rec_old(i).name, 13);
402 hr_utility.set_location(' IPV Value: '||l_ipv_rec_old(i).screen_entry_value, 13);
403 end if;
404 Close csr_entry_val;
405 End loop;
406 Check_Input_Values(p_ipv_val_tab,l_ipv_rec_old);
407 Exit;
408 <<Next_Element>>
409
410 l_count := 0; l_new_Maxdiff_amt:= 0;
411 Init_Ipv_Rec(l_ipv_rec_old);
412 l_authId_ipv_id := Null; l_awdId_name := Null;
413 l_maxAmt_ipvId := Null; l_maxAmt_name := Null;
414 l_authId_ipv_value := Null; l_maxAmt_ipv_value := Null;
415 l_ele_entry_st_date:= Null; l_ele_entry_ed_date:= Null;
416
417 End Loop; --For prv_awds
418 hr_utility.set_location('Leaving: '||l_proc_name, 80);
419 exception
420 when others then
421 hr_utility.set_location('Leaving: '||l_proc_name, 90);
422
423 End Chk_If_Entry_Exists;
424
425 -- =============================================================================
426 -- ~ Create_Student_Batch_Entry: create student earnings batch header
427 -- =============================================================================
428 procedure Create_Student_Batch_Entry
429 (errbuf out nocopy varchar2
430 ,retcode out nocopy number
431 ,p_effective_date in varchar2
432 ,p_earnings_type in varchar2
433 ,p_selection_criteria in varchar2
434 ,p_business_group_id in varchar2
435 ,p_is_asg_set in varchar2
436 ,p_assignment_set in varchar2
437 ,p_is_ssn in varchar2
438 ,p_ssn in varchar2
439 ,p_is_person_group in varchar2
440 ,p_person_group_id in varchar2
441 ,p_element_type_id in varchar2
442 ) is
443
444 -- ===========================================================================
445 -- Cursor to get the Assignment ID and Assignment Number
446 -- if the selection criteria is Person Group
447 -- ===========================================================================
448 cursor get_stu_details (c_ssn in varchar2
449 ,c_business_group_id in number
450 ,c_effective_date in date) is
451 select paf.assignment_id,
452 paf.assignment_number
453 from per_people_f per,
454 per_assignments_f paf
455 where per.national_identifier = c_ssn
459 and per.effective_end_date
456 and paf.assignment_type = 'E'
457 and paf.primary_flag = 'Y'
458 and c_effective_date between per.effective_start_date
460 and paf.person_id = per.person_id
461 and per.business_group_id = c_business_group_id
462 and c_effective_date between paf.effective_start_date
463 and paf.effective_end_date;
464
465 -- ===========================================================================
466 -- Cursor to get the element name for element_type_id
467 -- ===========================================================================
468 cursor get_element_name (p_element_type_id number,
469 p_effective_date date) is
470 select element_name
471 from pay_element_types_f
472 where element_type_id = p_element_type_id
473 and p_effective_date between effective_start_date
474 and effective_end_date;
475 -- Type declarations
476 type empcurtyp is ref cursor;
477 pri_cur empcurtyp;
478 stu_cur empcurtyp;
479 l_ipv t_input_values;
480
481 -- Boolean variables
482 l_batch_header_created boolean;
483 l_award_paid boolean;
484 l_award_amt_adj boolean;
485 -- Varchar variables
486 l_fund_code varchar2(30);
487 l_stusys_ssn varchar2(30);
488 l_earnings_type varchar2(20);
489 l_sqlstmt varchar2(1500);
490 l_selcrs varchar2(1500);
491 l_error_msg varchar2(2000);
492
493 -- Number variables
494 l_ct number;
495 l_new_batch number;
496 l_object_version_number number;
497 l_batch_line_id number;
498 l_auth_id number;
499 l_auth_amt number;
500 l_fund_id number;
501 l_count number;
502 l_st_date date;
503 l_end_date date;
504 l_effective_date date;
505
506 -- Type casted variables
507 l_assignment_id per_assignments_f.assignment_id%type;
508 l_assignment_number per_assignments_f.assignment_number%type;
509 l_element_name pay_element_types_f.element_name%type;
510 l_person_id per_people_f.person_id%type;
511 l_ssn per_people_f.national_identifier%type;
512 l_party_id per_people_f.party_id%type;
513 l_proc_name constant varchar2(150) :=
514 'PQP_US_Student_BEE.Create_Student_Batch_Entry';
515
516 -- =========================================================================
517 -- Function to dynamically generate the the sql to get the person ids for
518 -- the given person group id. Please note that these person ids are not same
519 -- as per_people_f person_id instead these belong to student system.
520 -- =========================================================================
521 function Get_Person_ID
522 (p_group_id in number
523 ) return varchar2 is
524
525 plsql_block varchar2(2000);
526 partyids varchar2(2000);
527 l_status varchar2(20);
528
529 begin
530 plsql_block :=
531 'declare
532 l_sql varchar2(2000);
533 begin
534 l_sql :=
535 IGS_PE_Dynamic_Persid_Group.IGS_Get_Dynamic_Sql
536 (p_groupid => :1
537 ,p_status => :2
538 );
539 :3 := l_sql;
540 end;' ;
541 execute immediate plsql_block
542 using p_group_id
543 ,out l_status
544 ,out partyids;
545
546 return partyids;
547
548 end Get_Person_ID;
549
550 begin -- Main
551
552 hr_utility.set_location('Entering: '||l_proc_name, 5);
553 if hr_utility.debug_enabled then
554 g_debug := true;
555 end if;
556
557 hr_utility.set_location('p_effective_date: '||p_effective_date, 5);
558 hr_utility.set_location('p_selection_criteria: '||p_selection_criteria, 5);
559 hr_utility.set_location('p_business_group_id: '||p_business_group_id, 5);
560 hr_utility.set_location('p_assignment_set: '||p_assignment_set, 5);
561 hr_utility.set_location('p_ssn: '||p_ssn, 5);
562 hr_utility.set_location('p_person_group_id: '||p_person_group_id, 5);
563 hr_utility.set_location('p_element_type_id: '||p_element_type_id, 5);
564
565 -- ===========================================================================
566 -- ~ Assign default values to the local variables.
567 -- ===========================================================================
568 hr_utility.set_location('Assign default value to local variables ', 5);
569 l_ct := 0; l_person_id := null;
570 l_new_batch := 0; l_ssn := null;
571 l_object_version_number:= 0; l_fund_code := null;
572 l_batch_line_id := 0; l_auth_id := null;
573 l_auth_amt := null; l_party_id := null;
574 l_batch_header_created := false;
575
576 l_effective_date := fnd_date.canonical_to_date(p_effective_date);
577
578 hr_utility.set_location('p_earnings_type: '||p_earnings_type, 10);
579 -- Translating the earnings type code as used in by the Student Financial Aid
580 -- Module. For HRMS its PQP_US_STUDENT_EARNINGS_TYPE and for OSS Fin. Aid its
581 -- IGF_AW_FUND_SOURCE.
582
583 if p_earnings_type = 'IWS' then
584 l_earnings_type := 'INSTITUTIONAL';
585
586 elsif p_earnings_type = 'FWS' then
590 l_earnings_type := 'STATE';
587 l_earnings_type := 'FEDERAL';
588
589 elsif p_earnings_type = 'SWS' then
591
592 elsif p_earnings_type = 'GSS' then
593 l_earnings_type := 'ENDOWMENT';
594
595 elsif p_earnings_type = 'ESE' then
596 l_earnings_type := 'OUTSIDE';
597
598 else
599 l_earnings_type := 'FEDERAL';
600
601 end if;
602
603 hr_utility.set_location('l_earnings_type: '||l_earnings_type, 10);
604
605 if p_selection_criteria = 'Assignment Set' then
606
607 hr_utility.set_location('p_selection_criteria: '||p_selection_criteria, 11);
608 -- If the selection criteria is an Assignment set then get all the employee
609 -- assignments of those who are student employees within the assignment set.
610 l_selcrs :=
611 'select per.national_identifier,
612 paf.assignment_id,
613 paf.assignment_number,
614 per.party_id
615 from per_assignments_f paf,
616 per_people_f per
617 where paf.person_id = per.person_id
618 and paf.assignment_type =''E''
619 and paf.primary_flag=''Y''
620 and :1 between paf.effective_start_date
621 and paf.effective_end_date
622 and :2 between per.effective_start_date
623 and per.effective_end_date
624 and per.business_group_id=:3
625 and exists
626 (select 1
627 from hr_assignment_set_amendments hasa
628 where hasa.assignment_set_id = :4
629 and hasa.assignment_id = paf.assignment_id
630 and upper(hasa.include_or_exclude) = ''I'')
631 and exists
632 (select 1
633 from per_people_extra_info pei
634 where pei.person_id = per.person_id
635 and pei.information_type = ''PQP_OSS_PERSON_DETAILS'')';
636
637 open pri_cur for l_selcrs
638 using l_effective_date
639 ,l_effective_date
640 ,p_business_group_id
641 ,to_number(p_assignment_set);
642
643 elsif p_selection_criteria = 'OSS Student Person Group' then
644
645 hr_utility.set_location('p_selection_criteria: '||p_selection_criteria, 12);
646 -- If the selection criteris is an OSS Person Group, then call the dynamic
647 -- sql to get the list of student party ids and match those with the
648 -- party id in per_all_people_f.
649 l_selcrs := get_person_id(to_number(p_person_group_id));
650 open pri_cur for l_selcrs;
651
652 elsif p_selection_criteria = 'ALL' then
653
654 hr_utility.set_location('p_selection_criteria: '||p_selection_criteria, 13);
655 -- To get the awards details from OSS for all the student employees
656 -- within the business group id.
657 l_selcrs :=
658 'select per.national_identifier,
659 paf.assignment_id,
660 paf.assignment_number,
661 per.party_id
662 from per_assignments_f paf,
663 per_people_f per
664 where paf.person_id = per.person_id
665 and :1 between paf.effective_start_date
666 and paf.effective_end_date
667 and :2 between per.effective_start_date
668 and per.effective_end_date
669 and per.business_group_id=:3
670 and paf.assignment_type =''E''
671 and paf.primary_flag=''Y''
672 and exists
673 (select 1
674 from per_people_extra_info pei
675 where pei.person_id = per.person_id
676 and pei.information_type = ''PQP_OSS_PERSON_DETAILS'')';
677
678 open pri_cur for l_selcrs
679 using l_effective_date
680 ,l_effective_date
681 ,p_business_group_id;
682
683 elsif p_selection_criteria = 'Social Security Number' then
684
685 hr_utility.set_location('p_selection_criteria: '||p_selection_criteria, 14);
686 -- Get the Student award details for the given social security number.
687 l_selcrs :=
688 'select per.national_identifier,
689 paf.assignment_id,
690 paf.assignment_number,
691 per.party_id
692 from per_people_f per,
693 per_assignments_f paf
694 where per.national_identifier = :1
695 and :2 between per.effective_start_date
696 and per.effective_end_date
697 and paf.person_id = per.person_id
698 and :3 between paf.effective_start_date
699 and paf.effective_end_date
700 and per.business_group_id=:4
701 and paf.assignment_type =''E''
702 and paf.primary_flag=''Y''
703 and exists (select 1
704 from per_people_extra_info pei
705 where pei.person_id = per.person_id
706 and pei.information_type = ''PQP_OSS_PERSON_DETAILS'')';
707
708 open pri_cur for l_selcrs
709 using p_ssn
710 ,l_effective_date
711 ,l_effective_date
712 ,p_business_group_id;
713 end if;
714
715 l_batch_header_created := false;
716 hr_utility.set_location('Delete the PL/SQL table and create null records ', 15);
717 l_ipv.delete;
718 for i in 1..15
719 loop
720 l_ipv(i).screen_entry_value := null;
721 l_ipv(i).input_value_id := null;
722 l_ipv(i).name := null;
726 l_ipv(i).max_value := null;
723 l_ipv(i).display_sequence := null;
724 l_ipv(i).lookup_type := null;
725 l_ipv(i).default_value := null;
727 l_ipv(i).min_value := null;
728 end loop;
729 -- Get the input value names for the earnings element
730 l_count := 0;
731 hr_utility.set_location('Assign the input names to the PL/SQL table ', 15);
732 for ipv_rec in csr_ipv_id(c_ele_type_id => p_element_type_id
733 ,c_effective_date => l_effective_date)
734 loop
735 l_count := l_count + 1;
736 l_ipv(l_count).input_value_id := ipv_rec.input_value_id;
737 l_ipv(l_count).name := ipv_rec.name;
738 l_ipv(l_count).display_sequence := ipv_rec.display_sequence;
739 l_ipv(l_count).lookup_type := ipv_rec.lookup_type;
740 l_ipv(l_count).default_value := ipv_rec.default_value;
741 l_ipv(l_count).max_value := ipv_rec.max_value;
742 l_ipv(l_count).min_value := ipv_rec.min_value;
743 l_ipv(l_count).warning_or_error := ipv_rec.warning_or_error;
744 end loop;
745
746 -- The Main Cursor Loop starts here.
747 loop
748 if p_selection_criteria = 'OSS Student Person Group' then
749 fetch pri_cur into l_party_id;
750 else
751 fetch pri_cur into l_ssn,
752 l_assignment_id,
753 l_assignment_number,
754 l_party_id;
755 end if;
756 --
757 if g_debug then
758 hr_utility.set_location('Assignment Set ID'||p_assignment_set, 15);
759 hr_utility.set_location('SSN :'||l_ssn, 15);
760 hr_utility.set_location('Assignment ID :'||l_assignment_id, 15);
761 hr_utility.set_location('Assignment Number: '||l_assignment_number, 15);
762 hr_utility.set_location('Party ID: '||l_party_id, 15);
763 hr_utility.set_location('Person ID: '||l_person_id, 15);
764 hr_utility.set_location('Element Name: '||l_element_name, 15);
765 end if;
766 -- If no more students left then, exit.
767 exit when pri_cur%notfound;
768
769 -- Create the BEE header and set the flag value to true.
770 if not l_batch_header_created then
771
772 hr_utility.set_location('Create the BEE header', 16);
773 -- Create the Batch Element Entry Header need execute this code only once.
774 pay_batch_element_entry_api.create_batch_header
775 (p_session_date => l_effective_date
776 ,p_batch_name => 'OSS Batch '||rtrim(fnd_global.conc_request_id)
777 ,p_business_group_id => to_number(p_business_group_id)
778 ,p_action_if_exists => 'R'
779 ,p_batch_reference => 'OSS Batch '||rtrim(fnd_global.conc_request_id)
780 ,p_batch_source => 'Student Systems Fin Aid'
781 ,p_batch_id => l_new_batch
782 ,p_object_version_number=> l_object_version_number
783 );
784 for cele in get_element_name (to_number(p_element_type_id)
785 ,l_effective_date)
786 loop
787 l_element_name := cele.element_name;
788 end loop;
789 l_batch_header_created := true;
790
791 end if;
792 --
793 if stu_cur%ISOPEN then
794 close stu_cur;
795 end if;
796 if p_selection_criteria = 'OSS Student Person Group' then
797 -- If selection criteria is OSS Person Group ,then we have only party_id
798 -- now so we need to have different query for this criteria and get
799 -- the same financial aid details
800 l_sqlstmt :=
801 'select authorization_id,
802 authorized_amt,
803 fund_id,
804 authorization_start_date,
805 authorization_end_date,
806 social_security_number
807 from igf_se_authorization_v
808 where person_id = nvl(:1, person_id)
809 and sys_fund_source_code = nvl(:2, sys_fund_source_code)';
810
811 open stu_cur for l_sqlstmt
812 using l_party_id
813 ,l_earnings_type;
814 else
815 l_sqlstmt :=
816 'select authorization_id,
817 authorized_amt,
818 fund_id,
819 authorization_start_date,
820 authorization_end_date,
821 social_security_number
822 from igf_se_authorization_v
823 where social_security_number = nvl(:1, social_security_number)
824 and person_id = nvl(:2, person_id)
825 and sys_fund_source_code = nvl(:3, sys_fund_source_code)';
826
827 open stu_cur for l_sqlstmt
828 using l_ssn
829 ,l_party_id
830 ,l_earnings_type;
831 end if;
832 --
833
834 loop -- loop thru all the work study awards
835 fetch stu_cur into l_auth_id,
836 l_auth_amt,
837 l_fund_id,
838 l_st_date,
839 l_end_date,
840 l_stusys_ssn;
841 --
842 exit when stu_cur%notfound;
843 --
844 if g_debug then
845 hr_utility.set_location('Authorization ID: '||l_auth_id, 20);
846 hr_utility.set_location('Amount: '||l_auth_amt, 20);
847 hr_utility.set_location('FundId: '||l_fund_id, 20);
848 hr_utility.set_location('Auth Start Date: '||l_st_date, 20);
849 hr_utility.set_location('SSN: '||l_stusys_ssn, 20);
850 hr_utility.set_location('Auth End Date: '||l_end_date, 20);
851 end if;
852 --
853 if stu_cur%found then
857 for c1 in get_stu_details ( l_stusys_ssn
854 -- Get assignment details in case if the selection criteria
855 -- is Person Group.
856 if p_selection_criteria = 'OSS Student Person Group' then
858 ,p_business_group_id
859 ,l_effective_date
860 )
861 loop
862 l_assignment_id := c1.assignment_id;
863 l_assignment_number := c1.assignment_number;
864 end loop;
865 end if;
866 hr_utility.set_location('Assign input values from OSS: ', 25);
867 -- Assign the proper values to each input value of the element entry
868 for i in 1..15
869 loop
870 if l_ipv(i).name = 'Amount' then
871 l_ipv(i).screen_entry_value := null;
872
873 elsif l_ipv(i).name = 'Jurisdiction' then
874 l_ipv(i).screen_entry_value := null;
875
876 elsif l_ipv(i).name = 'Deduction Processing' then
877 l_ipv(i).screen_entry_value := get_lookup_meaning
878 ('US_DEDUCTION_PROCESSING'
879 ,l_ipv(i).default_value);
880 elsif l_ipv(i).name = 'Separate Check' then
881 l_ipv(i).screen_entry_value := get_lookup_meaning
882 ('YES_NO'
883 ,l_ipv(i).default_value);
884
885 elsif l_ipv(i).name = 'Authorization ID' then
886 l_ipv(i).screen_entry_value := l_auth_id;
887
888 elsif l_ipv(i).name = 'Student Earnings Type' then
889 l_ipv(i).screen_entry_value
890 := get_lookup_meaning('PQP_US_STUDENT_EARNINGS_TYPE'
891 ,p_earnings_type);
892 elsif l_ipv(i).name = 'Authorization Start Date' then
893 l_ipv(i).screen_entry_value := l_st_date;
894
895 elsif l_ipv(i).name = 'Authorization End Date' then
896 l_ipv(i).screen_entry_value := l_end_date;
897
898 elsif l_ipv(i).name = 'Fund ID' then
899 l_ipv(i).screen_entry_value := l_fund_id;
900
901 elsif l_ipv(i).name = 'Maximum Amount' then
902 l_ipv(i).screen_entry_value := l_auth_amt;
903 end if;
904 end loop;
905 -- Check if the Auth. Id is already created.
906 Chk_If_Entry_Exists
907 (p_assignment_id => l_assignment_id
908 ,p_business_group_id => p_business_group_id
909 ,p_effective_date => l_effective_date
910 ,p_element_type_id => p_element_type_id
911 ,p_ipv_val_tab => l_ipv
912 ,p_auth_id => l_auth_id
913 ,p_award_max_amt => l_auth_amt
914 ,p_award_paid => l_award_paid
915 ,p_award_amt_adj => l_award_amt_adj
916 );
917 If not l_award_paid Then
918 -- Increment the batch sequence
919 l_ct := l_ct + 1;
920 hr_utility.set_location('Calling : PAY_Batch_Element_Entry_API.Create_Batch_Line', 26);
921 PAY_Batch_Element_Entry_API.Create_Batch_Line
922 (p_session_date => l_effective_date
923 ,p_batch_id => l_new_batch
924 ,p_assignment_id => l_assignment_id
925 ,p_assignment_number => l_assignment_number
926 ,p_batch_sequence => l_ct
927 ,p_effective_date => l_effective_date
928 ,p_effective_start_date => l_effective_date
929 ,p_element_name => l_element_name
930 ,p_element_type_id => to_number(p_element_type_id)
931 ,p_value_1 => l_ipv(1).screen_entry_value
932 ,p_value_2 => l_ipv(2).screen_entry_value
933 ,p_value_3 => l_ipv(3).screen_entry_value
934 ,p_value_4 => l_ipv(4).screen_entry_value
935 ,p_value_5 => l_ipv(5).screen_entry_value
936 ,p_value_6 => l_ipv(6).screen_entry_value
937 ,p_value_7 => l_ipv(7).screen_entry_value
938 ,p_value_8 => l_ipv(8).screen_entry_value
939 ,p_value_9 => l_ipv(9).screen_entry_value
940 ,p_value_10 => l_ipv(10).screen_entry_value
941 ,p_value_11 => l_ipv(11).screen_entry_value
942 ,p_value_12 => l_ipv(12).screen_entry_value
943 ,p_value_13 => l_ipv(13).screen_entry_value
944 ,p_value_14 => l_ipv(14).screen_entry_value
945 ,p_value_15 => l_ipv(15).screen_entry_value
946 ,p_batch_line_id => l_batch_line_id
947 ,p_object_version_number=> l_object_version_number
948 );
949 End If;
950 end if; --if stu_cur%found
951 hr_utility.set_location('Re-set the entry values for next record ', 27);
952 for i in 1..15
953 loop
954 l_ipv(i).screen_entry_value := null;
955 end loop;
956
957 end loop; -- loop for each auth id for a student
958
959 end loop;
960 close pri_cur;
961
962 hr_utility.set_location('leaving: '||l_proc_name, 80);
963 -- Commit the records in the BEE tables.
964 commit;
965
966 exception
967 when others then
968 l_error_msg := sqlerrm;
969 Hr_Utility.set_location('SQLCODE :'||SQLCODE,90);
970 Hr_Utility.set_message(8303, 'PQP_230500_HROSS_GENERIC_ERR');
971 Hr_Utility.set_message_token('GENERIC_TOKEN',l_error_msg );
972 Hr_Utility.set_location('Leaving: '||l_proc_name, 90);
973 Hr_Utility.raise_error;
974
975
976 end Create_Student_Batch_Entry;
977
978 end;
[Home] [Help]