[Home] [Help]
PACKAGE BODY: APPS.PQP_US_STUDENT_EARNINGS
Source
1 package body PQP_US_Student_Earnings as
2 /* $Header: pqustrfe.pkb 120.0 2005/05/29 02:15:16 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 | 03-FEB-2004 |hgattu |115.5 |4158766 |record type changes |
15 | 10-FEB-2005 |hgattu |115.6 |4094250 | |
16 | 14-FEB-2005 |hgattu |115.7 |4180797 | |
17 | | | |4181127 | |
18 | 16-FEB-2005 |hgattu |115.8 |4187138 | |
19 | 18-FEB-2005 |hgattu |115.9 |4192747 | |
20 | 25-FEB-2005 |rpinjala |115.10 | |Retro-pay actions added. |
21 | | | | |removed entry_type (E,V,B) |
22 | 25-FEB-2005 |rpinjala |115.11 | |Retro-pay changes. |
23 | 21-MAR-2005 |rpinjala |115.12 | |Added comments to the header |
24 | | | | | |
25 | | | | | |
26 +=============+=========+=======+========+===================================+
27 */
28 -- =============================================================================
29 -- Package body Global Variables
30 -- =============================================================================
31 g_debug boolean;
32 g_pkg Varchar2(150) := 'PQP_US_Student_Earnings.';
33 -- =============================================================================
34 -- ~ Transfer_Student_Earnings
35 -- =============================================================================
36 procedure Transfer_Student_Earnings
37 (errbuf out nocopy varchar2
38 ,retcode out nocopy number
39 ,p_begin_date_paid in varchar2
40 ,p_end_date_paid in varchar2
41 ,p_earnings_type in varchar2
42 ,p_selection_criteria in varchar2
43 ,p_business_group_id in varchar2
44 ,p_is_asg_set in varchar2
45 ,p_assignment_set in varchar2
46 ,p_is_ssn in varchar2
47 ,p_ssn in varchar2
48 ,p_is_person_group in varchar2
49 ,p_person_group_id in varchar2
50 ,p_element_selection in varchar2
51 ,p_is_element_name in varchar2
52 ,p_element_type_id in varchar2
53 ,p_is_element_set in varchar2
54 ,p_element_set_id in varchar2
55 ) is
56
57 -- ==========================================================================
58 -- Cursor to get the run result value for given input_value_id and
59 -- element_type_id along with assignment_action_id for (P)rocessed actions.
60 -- ==========================================================================
61 cursor c_get_run_value (p_asg_action_id in number
62 ,p_element_type_id in number
63 ,p_effective_date in date
64 ,p_input_value_name in varchar2) is
65 select prv.result_value
66 from pay_run_results prr
67 ,pay_run_result_values prv
68 where prr.assignment_action_id = p_asg_action_id
69 and prr.element_type_id = p_element_type_id
70 and prv.input_value_id in
71 (select distinct input_value_id
72 from pay_input_values_f
73 where element_type_id = p_element_type_id
74 and p_effective_date between effective_start_date
75 and effective_end_date
76 and name = p_input_value_name)
77 and prv.run_result_id = prr.run_result_id;
78
79 -- To Get the party id of the person as of the date or award earnings
80 cursor c_party_id (c_asssignment_id in number
81 ,c_effective_date in date) Is
82 select per.party_id
83 from per_people_f per,
84 per_assignments_f paf
85 where per.person_id = paf.person_id
86 and paf.assignment_id = c_asssignment_id
87 and c_effective_date between per.effective_start_date
88 and per.effective_end_date
89 and c_effective_date between paf.effective_start_date
90 and paf.effective_end_date;
91 -- Ref. Cursors
92 type prigrpcur is ref cursor;
93 pri_grp_cur prigrpcur;
94 type empcurtyp is ref cursor;
95 pri_cur empcurtyp;
96
97 -- Booleans
98 l_batch_header_created boolean := false;
99 -- Numbers Variables
100 l_ct number :=0;
101 l_new_batch number :=0;
102 l_object_version_number number :=0;
103 l_batch_line_id number :=0;
104 l_transaction_id number;
105 l_msg_count number;
106 l_auth_id number := null;
107 l_auth_amt number := null;
108 l_fund_id number;
109 l_authorization_number number;
110 l_paid_amount number;
111 l_person_id number := null;
112 l_intial_value number :=1;
113 -- Varchar2 Variables
114 l_earnings_type varchar2(20);
115 l_sqlstmt varchar2(2500);
116 l_selcrs varchar2(4500);
117 l_grp_selcrs varchar2(4500);
118 l_return_status varchar2(200);
119 l_msg_data varchar2(200);
120 plsql_block varchar2(2500);
121 l_fund_code varchar2(30):= null;
122 l_stusys_ssn varchar2(30);
123 l_chk_earnings_type varchar2(20);
124 l_grp_party_list_id varchar2(9000);
125 -- Dates
126 l_effective_date date;
127 l_st_date date;
128 l_end_date date;
129 l_date_earned date;
130 -- Type declarations variables
131 l_grp_party_id per_people_f.party_id%type := null;
132 l_chk_grp_party_id per_people_f.party_id%type := null;
133 l_ssn per_people_f.national_identifier%type := null;
134 l_assignment_id per_assignments_f.assignment_id%type;
135 l_assignment_action_id pay_assignment_actions.assignment_action_id%type;
136 l_element_type_id pay_element_types_f.element_type_id%type;
137 l_tax_unit_id pay_assignment_actions.tax_unit_id%type;
138 l_payroll_id pay_payroll_actions.payroll_id%type;
139
140 l_proc_name constant varchar2(150):= g_pkg||'Transfer_Student_Earnings';
141
142 -- =======================================================================
143 -- Function to dynamically generate the the sql to get the person ids for
144 -- the given person group id. Please note that these person ids are not
145 -- same as per_people_f person_id instead these belong to student system.
146 -- =======================================================================
147 function get_person_id
148 (p_group_id in number) return varchar2 is
149 plsql_block varchar2(2000);
150 partyids varchar2(2000);
151 l_status varchar2(20);
152 l_proc_name constant varchar2(150) := g_pkg||'get_person_id';
153 begin
154 hr_utility.set_location('Entering: '||l_proc_name, 5);
155 plsql_block :=
156 ' declare
157 l_sql varchar2(2000);
158 begin
159 l_sql :=
160 igs_pe_dynamic_persid_group.igs_get_dynamic_sql
161 (p_groupid => :1
162 ,p_status => :2
163 );
164 :3 := l_sql;
165 end;' ;
166 execute immediate plsql_block
167 using p_group_id
168 ,out l_status
169 ,out partyids;
170 hr_utility.set_location('Leaving: '||l_proc_name, 80);
171 return partyids;
172 end get_person_id;
173
174 begin
175
176 hr_utility.set_location('Entering: '||l_proc_name, 5);
177 if hr_utility.debug_enabled then
178 g_debug := true;
179 end if;
180
181 -- Translating the earnings type code as used in by the Student Financial Aid
182 -- Module. For HRMS its PQP_US_STUDENT_EARNINGS_TYPE and for OSS Fin. Aid its
183 -- IGF_AW_FUND_SOURCE.
184
185 if p_earnings_type = 'IWS' then
186 l_earnings_type := 'INSTITUTIONAL';
187
188 elsif p_earnings_type = 'FWS' then
189 l_earnings_type := 'FEDERAL';
190
191 elsif p_earnings_type = 'SWS' then
192 l_earnings_type := 'STATE';
193
194 elsif p_earnings_type = 'GSS' then
195 l_earnings_type := 'ENDOWMENT';
196
197 elsif p_earnings_type = 'ESE' then
198 l_earnings_type := 'OUTSIDE';
199
200 else
201 l_earnings_type := p_earnings_type;
202 end if;
203
204 if g_debug then
205 hr_utility.set_location('Selection Criteria : '||p_selection_criteria, 15);
206 hr_utility.set_location('Element Selection : '||p_element_selection, 15);
207 hr_utility.set_location('Business Group Id : '||p_business_group_id, 15);
208 hr_utility.set_location('Element Type Id : '||p_element_type_id, 15);
209 hr_utility.set_location('SSN : '||p_ssn, 15);
210 hr_utility.set_location('l_earnings_type : '||l_earnings_type, 15);
211 end if;
212
213 if p_selection_criteria = 'Assignment Set' then
214
215 if p_element_selection = 'Element Name' then
216 l_selcrs :=
217 'select paa.assignment_id
218 ,paa.assignment_action_id
219 ,paa.tax_unit_id
220 ,ppa.date_earned
221 ,ppa.payroll_id
222 ,prr.element_type_id
223
224 from pay_assignment_actions paa
225 ,pay_payroll_actions ppa
226 ,pay_run_results prr
227 where ppa.action_status = ''C''
228 and paa.action_status = ''C''
229 and paa.payroll_action_id = ppa.payroll_action_id
230 and ppa.business_group_id = :1
231 and ppa.action_type in
232 (''Q'',''B'',''V'',''R'',''O'',''G'',''L'')
233 and ppa.date_earned between :2
234 and :3
235 and prr.assignment_action_id = paa.assignment_action_id
236 and prr.status in (''P'',''PA'')
237 and prr.element_type_id = :4
238 and exists
239 (select 1
240 from hr_assignment_set_amendments hasa
241 where hasa.assignment_set_id = :5
242 and hasa.assignment_id = paa.assignment_id
243 and upper(hasa.include_or_exclude) = ''I'')';
244
245 open pri_cur for l_selcrs
246 using to_number(p_business_group_id)
247 ,fnd_date.canonical_to_date(p_begin_date_paid)
248 ,fnd_date.canonical_to_date(p_end_date_paid)
249 ,to_number(p_element_type_id)
250 ,to_number(p_assignment_set);
251 else
252 l_selcrs :=
253 'select paa.assignment_id
254 ,paa.assignment_action_id
255 ,paa.tax_unit_id
256 ,ppa.date_earned
257 ,ppa.payroll_id
258 ,prr.element_type_id
259
260 from pay_assignment_actions paa
261 ,pay_payroll_actions ppa
262 ,pay_run_results prr
263 where ppa.action_status = ''C''
264 and paa.action_status = ''C''
265 and paa.payroll_action_id = ppa.payroll_action_id
266 and ppa.business_group_id = :1
267 and ppa.action_type in
268 (''Q'',''B'',''V'',''R'',''O'',''G'',''L'')
269 and ppa.date_earned between :2
270 and :3
271 and prr.assignment_action_id = paa.assignment_action_id
272 and prr.status in (''P'',''PA'')
273 and prr.element_type_id in
274 (select distinct petr.element_type_id
275 from pay_element_type_rules petr
276 where petr.element_set_id = :4
277 and petr.include_or_exclude = ''I''
278 union all
279 select distinct pet1.element_type_id
280 from pay_element_types_f pet1
281 where pet1.classification_id in
282 (select classification_id
283 from pay_ele_classification_rules
284 where element_set_id = :5)
285 minus
286 select distinct petr.element_type_id
287 from pay_element_type_rules petr
288 where petr.element_set_id = :6
289 and petr.include_or_exclude = ''E''
290 )
291 and exists
292 (select 1
293 from hr_assignment_set_amendments hasa
294 where hasa.assignment_set_id = :7
295 and hasa.assignment_id = paa.assignment_id
296 and upper(hasa.include_or_exclude) = ''I'')';
297
298 open pri_cur for l_selcrs
299 using to_number(p_business_group_id)
300 ,fnd_date.canonical_to_date(p_begin_date_paid)
301 ,fnd_date.canonical_to_date(p_end_date_paid)
302 ,to_number(p_element_set_id)
303 ,to_number(p_element_set_id)
304 ,to_number(p_element_set_id)
305 ,to_number(p_assignment_set);
306
307 end if;-- if p_element_selection
308
309 elsif p_selection_criteria = 'OSS Student Person Group' then
310
311 -- Call OSS Dynamic SQL to get the party_ids for groupid
312 l_grp_selcrs := get_person_id(to_number(p_person_group_id));
313 open pri_grp_cur for l_grp_selcrs;
314 loop
315 fetch pri_grp_cur into l_grp_party_id;
316 exit when pri_grp_cur%notfound;
317 -- Exit,if previous and current partyId is same
318 if nvl(l_grp_party_id, l_chk_grp_party_id) = l_chk_grp_party_id then
319 exit;
320 end if;
321 -- if l_intial_value is 1 then adding the ( to form the in statement
322 if l_intial_value = 1 then
323 l_grp_party_list_id := '(' ||l_grp_party_id ;
324 else
325 l_grp_party_list_id := l_grp_party_list_id||','||l_grp_party_id;
326 end if;
327 l_intial_value := l_intial_value+1;
328 l_chk_grp_party_id := l_grp_party_id;
329 end loop;
330
331 close pri_grp_cur;
332
333 if l_grp_party_list_id is not null then
334
335 l_grp_party_list_id := l_grp_party_list_id ||')';
336 hr_utility.set_location('Group PartyId List: '||l_grp_party_list_id, 15);
337
338 if p_element_selection = 'Element Name' then
339
340 l_selcrs :=
341 'select paa.assignment_id
342 ,paa.assignment_action_id
343 ,paa.tax_unit_id
344 ,ppa.date_earned
345 ,ppa.payroll_id
346 ,prr.element_type_id
347
348 from pay_assignment_actions paa
349 ,pay_payroll_actions ppa
350 ,pay_run_results prr
351 where ppa.action_status = ''C''
352 and paa.action_status = ''C''
353 and paa.payroll_action_id = ppa.payroll_action_id
354 and ppa.business_group_id = ' || p_business_group_id || '
355 and ppa.action_type in
356 (''Q'',''B'',''V'',''R'',''O'',''G'',''L'')
357 and ppa.date_earned
358 between '||''''|| fnd_date.canonical_to_date(p_begin_date_paid) ||''''|| '
359 and '||''''|| fnd_date.canonical_to_date(p_end_date_paid) ||''''|| '
360 and prr.assignment_action_id = paa.assignment_action_id
361 and prr.status in (''P'',''PA'')
362 and prr.element_type_id = '|| p_element_type_id ||'
363 and exists
364 (select 1
365 from per_people_extra_info pei,
366 per_people_f per ,
367 per_assignments_f paf
368 where pei.person_id = per.person_id
369 and paf.person_id = per.person_id
370 and paf.assignment_id = paa.assignment_id
371 and ppa.date_earned between per.effective_start_date
372 and per.effective_end_date
373 and ppa.date_earned between paf.effective_start_date
374 and paf.effective_end_date
375 and paf.assignment_type =''E''
376 and paf.primary_flag=''Y''
377 and pei.information_type = ''PQP_OSS_PERSON_DETAILS''
378 and per.party_id in ' || l_grp_party_list_id || '
379 and per.business_group_id= ' || p_business_group_id || '
380 )';
381 open pri_cur for l_selcrs;
382
383 else
384 l_selcrs :=
385 'select paa.assignment_id
386 ,paa.assignment_action_id
387 ,paa.tax_unit_id
388 ,ppa.date_earned
389 ,ppa.payroll_id
390 ,prr.element_type_id
391
392 from pay_assignment_actions paa
393 ,pay_payroll_actions ppa
394 ,pay_run_results prr
395 where ppa.action_status = ''C''
396 and paa.action_status = ''C''
397 and paa.payroll_action_id = ppa.payroll_action_id
398 and ppa.business_group_id = ' || p_business_group_id || '
399 and ppa.action_type in
400 (''Q'',''B'',''V'',''R'',''O'',''G'',''L'')
401 and ppa.date_earned
402 between '||''''|| fnd_date.canonical_to_date(p_begin_date_paid) ||''''|| '
403 and '||''''|| fnd_date.canonical_to_date(p_end_date_paid) ||''''|| '
404 and prr.assignment_action_id = paa.assignment_action_id
405 and prr.status IN (''P'',''PA'')
406 and prr.element_type_id in
407 (select distinct petr.element_type_id
408 from pay_element_type_rules petr
409 where petr.element_set_id = ' || p_element_set_id || '
410 and petr.include_or_exclude = ''I''
411 union all
412 select distinct pet1.element_type_id
413 from pay_element_types_f pet1
414 where pet1.classification_id in
415 (select classification_id
416 from pay_ele_classification_rules
417 where element_set_id = ' || p_element_set_id || ')
418 minus
419 select distinct petr.element_type_id
420 from pay_element_type_rules petr
421 where petr.element_set_id = ' || p_element_set_id || '
422 and petr.include_or_exclude = ''E''
423 )
424 and exists
425 (select 1
426 from per_people_extra_info pei,
427 per_people_f per ,
428 per_assignments_f paf
429 where pei.person_id = per.person_id
430 and paf.person_id = per.person_id
431 and paf.assignment_id = paa.assignment_id
432 and ppa.date_earned between per.effective_start_date
433 and per.effective_end_date
434 and ppa.date_earned between paf.effective_start_date
435 and paf.effective_end_date
436 and paf.assignment_type =''E''
437 and paf.primary_flag =''Y''
438 and pei.information_type = ''PQP_OSS_PERSON_DETAILS''
439 and per.party_id in ' || l_grp_party_list_id || '
440 and per.business_group_id=' || p_business_group_id || ' )';
441
442 open pri_cur for l_selcrs ;
443
444 end if;--if p_element_selection
445 end if;-- if l_grp_party_list_id
446
447 elsif p_selection_criteria = 'ALL' then
448
449 if p_element_selection = 'Element Name' then
450 l_selcrs :=
451 'select paa.assignment_id
452 ,paa.assignment_action_id
453 ,paa.tax_unit_id
454 ,ppa.date_earned
455 ,ppa.payroll_id
456 ,prr.element_type_id
457
458 from pay_assignment_actions paa
459 ,pay_payroll_actions ppa
460 ,pay_run_results prr
461 where ppa.action_status = ''C''
462 and paa.action_status = ''C''
463 and paa.payroll_action_id = ppa.payroll_action_id
464 and ppa.business_group_id = :1
465 and ppa.action_type in
466 (''Q'',''B'',''V'',''R'',''O'',''G'',''L'')
467 and ppa.date_earned between :2
468 and :3
469 and prr.assignment_action_id = paa.assignment_action_id
470 and prr.status IN (''P'',''PA'')
471 and prr.element_type_id = :4
472 and exists
473 (select 1
474 from per_people_extra_info pei,
475 per_assignments_f paf
476 where pei.person_id = paf.person_id
477 and pei.information_type = ''PQP_OSS_PERSON_DETAILS''
478 and paf.assignment_id = paa.assignment_id)';
479
480 open pri_cur for l_selcrs
481 using to_number(p_business_group_id)
482 ,fnd_date.canonical_to_date(p_begin_date_paid)
483 ,fnd_date.canonical_to_date(p_end_date_paid)
484 ,to_number(p_element_type_id);
485 else
486 l_selcrs :=
487 'select paa.assignment_id
488 ,paa.assignment_action_id
489 ,paa.tax_unit_id
490 ,ppa.date_earned
491 ,ppa.payroll_id
492 ,prr.element_type_id
493
494 from pay_assignment_actions paa
495 ,pay_payroll_actions ppa
496 ,pay_run_results prr
497 where ppa.action_status = ''C''
498 and paa.action_status = ''C''
499 and paa.payroll_action_id = ppa.payroll_action_id
500 and ppa.business_group_id = :1
501 and ppa.action_type IN
502 (''Q'',''B'',''V'',''R'',''O'',''G'',''L'')
503 and ppa.date_earned between :2
504 and :3
505 and prr.assignment_action_id = paa.assignment_action_id
506 and prr.status in (''P'',''PA'')
507 and prr.element_type_id in
508 (select distinct petr.element_type_id
509 from pay_element_type_rules petr
510 where petr.element_set_id = :4
511 and petr.include_or_exclude = ''I''
512 union all
513 select distinct pet1.element_type_id
514 from pay_element_types_f pet1
515 where pet1.classification_id in
516 (select classification_id
517 from pay_ele_classification_rules
518 where element_set_id = :5)
519 minus
520 select distinct petr.element_type_id
521 from pay_element_type_rules petr
522 where petr.element_set_id = :6
523 and petr.include_or_exclude = ''E''
524 )
525 and exists
526 (select 1
527 from per_people_extra_info pei,
528 per_assignments_f paf
529 where pei.person_id = paf.person_id
530 and pei.information_type = ''PQP_OSS_PERSON_DETAILS''
531 and paf.assignment_id = paa.assignment_id)';
532 open pri_cur for l_selcrs
533 using to_number(p_business_group_id)
534 ,fnd_date.canonical_to_date(p_begin_date_paid)
535 ,fnd_date.canonical_to_date(p_end_date_paid)
536 ,to_number(p_element_set_id)
537 ,to_number(p_element_set_id)
538 ,to_number(p_element_set_id);
539 end if;--if p_element_selection
540
541 elsif p_selection_criteria = 'Social Security Number' then
542
543 if p_element_selection = 'Element Name' then
544 l_selcrs :=
545 'select paa.assignment_id
546 ,paa.assignment_action_id
547 ,paa.tax_unit_id
548 ,ppa.date_earned
549 ,ppa.payroll_id
550 ,prr.element_type_id
551
552 from pay_assignment_actions paa
553 ,pay_payroll_actions ppa
554 ,pay_run_results prr
555 where ppa.action_status = ''C''
556 and paa.action_status = ''C''
557 and paa.payroll_action_id = ppa.payroll_action_id
558 and ppa.business_group_id = :1
559 and ppa.action_type in
560 (''Q'',''B'',''V'',''R'',''O'',''G'',''L'')
561 and ppa.date_earned between :2
562 and :3
563 and prr.assignment_action_id = paa.assignment_action_id
564 and prr.status IN (''P'',''PA'')
565 and prr.element_type_id = :4
566 and exists
567 (select 1
568 from per_people_extra_info pei,
569 per_people_f per ,
570 per_assignments_f paf
571 where pei.person_id = per.person_id
572 and paf.person_id = per.person_id
573 and paf.assignment_id = paa.assignment_id
574 and ppa.date_earned between per.effective_start_date
575 and per.effective_end_date
576 and ppa.date_earned between paf.effective_start_date
577 and paf.effective_end_date
578 and paf.assignment_type = ''E''
579 and paf.primary_flag = ''Y''
580 and pei.information_type = ''PQP_OSS_PERSON_DETAILS''
581 and per.national_identifier = :5
582 and per.business_group_id = :6 )';
583
584 open pri_cur for l_selcrs
585 using to_number(p_business_group_id)
586 ,fnd_date.canonical_to_date(p_begin_date_paid)
587 ,fnd_date.canonical_to_date(p_end_date_paid)
588 ,to_number(p_element_type_id)
589 ,p_ssn
590 ,to_number(p_business_group_id);
591 else
592 l_selcrs :=
593 'select paa.assignment_id
594 ,paa.assignment_action_id
595 ,paa.tax_unit_id
596 ,ppa.date_earned
597 ,ppa.payroll_id
598 ,prr.element_type_id
599
600 from pay_assignment_actions paa
601 ,pay_payroll_actions ppa
602 ,pay_run_results prr
603 where ppa.action_status = ''C''
604 and paa.action_status = ''C''
605 and paa.payroll_action_id = ppa.payroll_action_id
606 and ppa.business_group_id = :1
607 and ppa.action_type in
608 (''Q'',''B'',''V'',''R'',''O'',''G'',''L'')
609 and ppa.date_earned between :2
610 and :3
611 and prr.assignment_action_id = paa.assignment_action_id
612 and prr.status IN (''P'',''PA'')
613 and prr.element_type_id in
614 (select distinct petr.element_type_id
615 from pay_element_type_rules petr
616 where petr.element_set_id = :4
617 and petr.include_or_exclude = ''I''
618 union all
619 select distinct pet1.element_type_id
620 from pay_element_types_f pet1
621 where pet1.classification_id in
622 (select classification_id
623 from pay_ele_classification_rules
624 where element_set_id = :5)
625 minus
626 select distinct petr.element_type_id
627 from pay_element_type_rules petr
628 where petr.element_set_id = :6
629 and petr.include_or_exclude = ''E''
630 )
631 and exists
632 (select 1
633 from per_people_extra_info pei,
634 per_people_f per ,
635 per_assignments_f paf
636 where pei.person_id = per.person_id
637 and paf.person_id = per.person_id
638 and paf.assignment_id = paa.assignment_id
639 and ppa.date_earned between per.effective_start_date
640 and per.effective_end_date
641 and ppa.date_earned between paf.effective_start_date
642 and paf.effective_end_date
643 and paf.assignment_type =''E''
644 and paf.primary_flag = ''Y''
645 and pei.information_type = ''PQP_OSS_PERSON_DETAILS''
646 and per.national_identifier = :7
647 and per.business_group_id = :8
648 )';
649
650 open pri_cur for l_selcrs
651 using to_number(p_business_group_id)
652 ,fnd_date.canonical_to_date(p_begin_date_paid)
653 ,fnd_date.canonical_to_date(p_end_date_paid)
654 ,to_number(p_element_set_id)
655 ,to_number(p_element_set_id)
656 ,to_number(p_element_set_id)
657 ,p_ssn
658 ,to_number(p_business_group_id);
659
660 end if; --if p_element_selection
661
662 end if; --if p_selection_criteria
663
664 if g_debug then
665 hr_utility.set_location(' Start of Main Loop ', 20);
666
667 end if;
668
669 loop -- Start: Main Cursor
670 fetch pri_cur into l_assignment_id,
671 l_assignment_action_id,
672 l_tax_unit_id,
673 l_date_earned,
674 l_payroll_id,
675 l_element_type_id;
676 exit when pri_cur%notfound;
677 --
678 if g_debug then
679 hr_utility.set_location('Assignment Id: '||l_assignment_id, 21);
680 hr_utility.set_location('Assignment Action Id: '||l_assignment_action_id, 21);
681 hr_utility.set_location('Tax Unit Id: '||l_tax_unit_id, 21);
682 hr_utility.set_location('Payroll ID: '||l_payroll_id, 21);
683 hr_utility.set_location('Element Type Id: '||l_element_type_id, 21);
684 end if;
685 -- Process the record if the Earnings Type matches
686 for c_rec in c_get_run_value (l_assignment_action_id
687 ,l_element_type_id
688 ,l_date_earned
689 ,'Student Earnings Type')
690 loop
691 l_chk_earnings_type := c_rec.result_value;
692 end loop;
693 if nvl(p_earnings_type, l_chk_earnings_type) <> l_chk_earnings_type then
694 -- raise error;
695 hr_utility.set_location('l_chk_earnings_type: '||l_chk_earnings_type, 21);
696 hr_utility.set_location('p_earnings_type: '||p_earnings_type, 21);
697 exit;
698 end if;
699 -- Get Authorization Number
700 l_authorization_number := null;
701 for c_rec in c_get_run_value (l_assignment_action_id
702 ,l_element_type_id
703 ,l_date_earned
704 ,'Authorization ID')
705 loop
706 l_authorization_number := c_rec.result_value;
707 end loop;
708 -- Get Paid Amount
709 l_paid_amount := 0;
710 for c_rec in c_get_run_value (l_assignment_action_id
711 ,l_element_type_id
712 ,l_date_earned
713 ,'Pay Value')
714 loop
715 l_paid_amount := l_paid_amount + c_rec.result_value;
716 end loop;
717 -- Get Person Id from Student System, i.e. party_id
718 open c_party_id (c_asssignment_id => l_assignment_id
719 ,c_effective_date => l_date_earned);
720 fetch c_party_id into l_person_id;
721 hr_utility.set_location('Stu Party Id: '||l_person_id, 22);
722
723 if c_party_id%found then
724 if g_debug then
725 hr_utility.set_location('Payroll Id: '||l_payroll_id, 22);
726 hr_utility.set_location('Date Earned: '||l_date_earned, 22);
727 hr_utility.set_location('Authorization Number: '||l_authorization_number, 22);
728 hr_utility.set_location('Party Id: '||l_person_id, 22);
729 hr_utility.set_location('Paid Amount: '||l_paid_amount, 22);
730 hr_utility.set_location('Tax Unit Id: '||l_tax_unit_id, 22);
731 end if;
732 --
733 plsql_block :=
734 'declare
735 pay_rec igf_se_payment_pub.payment_rec_type;
736 begin
737 pay_rec.transaction_id := :1;
738 pay_rec.payroll_id := :2;
739 pay_rec.payroll_date := :3;
740 pay_rec.authorization_id := :4;
741 pay_rec.person_id := :5;
742 pay_rec.paid_amount := :6;
743 pay_rec.organization_unit_name := :7;
744 pay_rec.source := :8;
745
746 igf_se_payment_pub.create_payment(
747 p_init_msg_list => Fnd_Api.G_TRUE
748 ,p_payment_rec => pay_rec
749 ,x_transaction_id => :9
750 ,x_return_status => :10
751 ,x_msg_count => :11
752 ,x_msg_data => :12
753 );
754 end;';
755 execute immediate plsql_block
756 using
757 l_transaction_id
758 ,l_assignment_action_id
759 ,l_date_earned
760 ,l_authorization_number
761 ,l_person_id
762 ,l_paid_amount
763 ,l_tax_unit_id
764 ,'ORACLE_HRMS'
765 ,out l_transaction_id
766 ,out l_return_status
767 ,out l_msg_count
768 ,out l_msg_data;
769
770 end if;
771 Close c_party_id;
772
773 if g_debug then
774 hr_utility.set_location(' l_transaction_id: '||l_transaction_id, 70);
775 hr_utility.set_location(' l_return_status : '||l_return_status, 70);
776 end if;
777 l_transaction_id := null; l_msg_data := null;
778 l_return_status := null;
779 end loop; -- end: main cursor
780 close pri_cur;
781 hr_utility.set_location('leaving: '||l_proc_name, 80);
782 commit;
783
784 end Transfer_Student_Earnings;
785
786 end;