[Home] [Help]
PACKAGE BODY: APPS.IGF_AP_LG_TD_IMP
Source
1 PACKAGE BODY igf_ap_lg_td_imp AS
2 /* $Header: IGFAP39B.pls 120.9 2006/03/07 23:25:05 veramach ship $ */
3
4 g_log_tab_index NUMBER := 0;
5
6 TYPE log_record IS RECORD
7 ( person_number VARCHAR2(30),
8 message_text VARCHAR2(500));
9
10 -- The PL/SQL table for storing the log messages
11 TYPE LogTab IS TABLE OF log_record
12 index by binary_integer;
13
14 g_log_tab LogTab;
15
16 -- THIS IS THE GLOBAL CURSOR THAT IS USED TO UPDATE THE FA BASE RECORD IF THE TODO ITEMS ARE SUCCESSFULLY IMPORTED
17
18 CURSOR c_baseid_exists(cp_base_id NUMBER)
19 IS
20 SELECT ROWID row_id,
21 base_id,
22 ci_cal_type,
23 person_id,
24 ci_sequence_number,
25 org_id ,
26 bbay ,
27 current_enrolled_hrs ,
28 special_handling,
29 coa_pending,
30 sap_evaluation_date,
31 sap_selected_flag,
32 state_sap_status,
33 verification_process_run,
34 inst_verif_status_date,
35 manual_verif_flag,
36 fed_verif_status,
37 fed_verif_status_date,
38 inst_verif_status,
39 nslds_eligible,
40 ede_correction_batch_id,
41 fa_process_status_date,
42 isir_corr_status,
43 isir_corr_status_date,
44 isir_status,
45 isir_status_date,
46 profile_status,
47 profile_status_date,
48 profile_fc,
49 pell_eligible,
50 award_adjusted,
51 change_pending,
52 coa_code_f,
53 coa_fixed,
54 coa_code_i,
55 coa_f,
56 coa_i ,
57 coa_pell ,
58 disbursement_hold ,
59 enrolment_status ,
60 enrolment_status_date ,
61 fa_process_status ,
62 federal_sap_status ,
63 grade_level ,
64 grade_level_date ,
65 grade_level_type ,
66 inst_sap_status ,
67 last_packaged ,
68 notification_status ,
69 notification_status_date ,
70 packaging_hold ,
71 nslds_data_override_flg ,
72 packaging_status ,
73 prof_judgement_flg ,
74 packaging_status_date ,
75 qa_sampling ,
76 target_group ,
77 todo_code ,
78 total_package_accepted ,
79 total_package_offered ,
80 transcript_available ,
81 tolerance_amount ,
82 transfered ,
83 total_aid ,
84 admstruct_id,
85 admsegment_1 ,
86 admsegment_2 ,
87 admsegment_3 ,
88 admsegment_4 ,
89 admsegment_5 ,
90 admsegment_6,
91 admsegment_7,
92 admsegment_8,
93 admsegment_9,
94 admsegment_10,
95 admsegment_11,
96 admsegment_12,
97 admsegment_13,
98 admsegment_14,
99 admsegment_15,
100 admsegment_16,
101 admsegment_17,
102 admsegment_18,
103 admsegment_19,
104 admsegment_20,
105 packstruct_id,
106 packsegment_1,
107 packsegment_2,
108 packsegment_3,
109 packsegment_4,
110 packsegment_5,
111 packsegment_6,
112 packsegment_7,
113 packsegment_8,
114 packsegment_9,
115 packsegment_10,
116 packsegment_11,
117 packsegment_12,
118 packsegment_13,
119 packsegment_14,
120 packsegment_15,
121 packsegment_16,
122 packsegment_17,
123 packsegment_18,
124 packsegment_19,
125 packsegment_20,
126 miscstruct_id ,
127 miscsegment_1,
128 miscsegment_2 ,
129 miscsegment_3 ,
130 miscsegment_4,
131 miscsegment_5,
132 miscsegment_6,
133 miscsegment_7,
134 miscsegment_8,
135 miscsegment_9,
136 miscsegment_10,
137 miscsegment_11,
138 miscsegment_12,
139 miscsegment_13,
140 miscsegment_14,
141 miscsegment_15,
142 miscsegment_16,
143 miscsegment_17,
144 miscsegment_18,
145 miscsegment_19,
146 miscsegment_20,
147 request_id,
148 program_application_id,
149 program_id ,
150 program_update_date,
151 manual_disb_hold,
152 pell_alt_expense,
153 assoc_org_num, --Modified(added this attribute) by ugummall on 25-SEP-2003 w.r.t FA 126 - Multiple FA Offices
154 award_fmly_contribution_type,
155 isir_locked_by,
156 adnl_unsub_loan_elig_flag,
157 lock_awd_flag,
158 lock_coa_flag
159 FROM igf_ap_fa_base_rec_all FA
160 WHERE FA.base_id = cp_base_id;
161
162 g_baseid_exists c_baseid_exists%ROWTYPE;
163
164 -- museshad (FA 140). Forward declaration
165 PROCEDURE process_pref_lender(
166 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
167 p_rel_cd IN igf_ap_li_todo_ints.relationship_cd%TYPE,
168 p_clprl_id OUT NOCOPY igf_sl_cl_pref_lenders.clprl_id%TYPE
169 );
170 -- museshad (FA 140)
171
172 PROCEDURE main ( errbuf OUT NOCOPY VARCHAR2,
173 retcode OUT NOCOPY NUMBER,
174 p_award_year IN VARCHAR2,
175 p_batch_id IN NUMBER,
176 p_del_ind IN VARCHAR2 )
177 IS
178 /*
179 || Created By : bkkumar
180 || Created On : 26-MAY-2003
181 || Purpose : Main process which imports the To Do Items attached to a student in the system.
182 || Known limitations, enhancements or remarks :
183 || Change History :
184 || Who When What
185 || tsailaja 13/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
186 || museshad 25-Jul-2005 FA 140. Validation for preferred lender. After successful import,
187 || insert Pref lender details
188 || (reverse chronological order - newest change first)
189 */
190 l_proc_item_str VARCHAR2(50) := NULL;
191 l_message_str VARCHAR2(800) := NULL;
192 l_terminate_flag BOOLEAN := FALSE;
193 l_error_flag BOOLEAN := FALSE;
194 l_error VARCHAR2(10);
195 lv_row_id VARCHAR2(80) := NULL;
196 lv_person_id igs_pe_hz_parties.party_id%TYPE := NULL;
197 lv_base_id igf_ap_fa_base_rec_all.base_id%TYPE := NULL;
198 l_old_person_number igf_ap_li_todo_ints.person_number%TYPE := '******';
199 l_current_person_number igf_ap_li_todo_ints.person_number%TYPE := NULL;
200 l_person_skip_flag BOOLEAN := FALSE;
201 l_success_record_cnt NUMBER := 0;
202 l_error_record_cnt NUMBER := 0;
203 l_todo_flag BOOLEAN := FALSE;
204 l_chk_batch VARCHAR2(1) := 'Y';
205 l_chk_profile VARCHAR2(1) := 'Y';
206 l_debug_str VARCHAR2(800) := NULL;
207 l_total_record_cnt NUMBER := 0;
208 l_get_meaning igf_lookups_view.meaning%TYPE := NULL;
209 l_todo_number igf_ap_td_item_mst_all.todo_number%TYPE := NULL;
210
211 l_cal_type igf_ap_fa_base_rec_all.ci_cal_type%TYPE ;
212 l_seq_number igf_ap_fa_base_rec_all.ci_sequence_number%TYPE;
213
214 -- Cursor for getting the context award year details
215 CURSOR c_get_status(cp_cal_type VARCHAR2,
216 cp_seq_number NUMBER)
217 IS
218 SELECT sys_award_year,
219 award_year_status_code
220 FROM igf_ap_batch_aw_map
221 WHERE ci_cal_type = cp_cal_type
222 AND ci_sequence_number = cp_seq_number;
223
224 l_get_status c_get_status%ROWTYPE;
225
226 CURSOR c_get_alternate_code(cp_cal_type VARCHAR2,
227 cp_seq_number NUMBER)
228 IS
229 SELECT alternate_code
230 FROM igs_ca_inst
231 WHERE cal_type = cp_cal_type
232 AND sequence_number = cp_seq_number;
233
234 l_get_alternate_code c_get_alternate_code%ROWTYPE;
235
236 CURSOR c_get_persons(cp_alternate_code VARCHAR2,
237 cp_batch_id NUMBER)
238 IS
239 SELECT batch_num,
240 tdint_id,
241 ci_alternate_code,
242 person_number,
243 item_code,
244 item_add_date,
245 item_status_code,
246 item_status_date,
247 corsp_date,
248 corsp_count_num,
249 max_attempt_num,
250 freq_attempt_num,
251 reqd_for_application_flag,
252 inactive_flag,
253 import_status_type,
254 import_record_type,
255 relationship_cd,
256 ROWID ROW_ID
257 FROM igf_ap_li_todo_ints
258 WHERE ci_alternate_code = cp_alternate_code
259 AND batch_num = cp_batch_id
260 AND import_status_type IN ('U','R')
261
262 ORDER BY person_number;
263
264 l_get_persons c_get_persons%ROWTYPE;
265
266 CURSOR c_get_crsp_hist(cp_cal_type VARCHAR2,
267 cp_seq_number NUMBER,
268 cp_person_number VARCHAR2)
269 IS
270 SELECT hz.party_number person_id
271 FROM igs_co_interac_hist hist,
272 hz_parties hz
273 WHERE hist.cal_type = cp_cal_type
274 AND hist.ci_sequence_number = cp_seq_number
275 AND hist.student_id = hz.party_id
276 AND hz.party_number = cp_person_number
277 AND rownum = 1;
278
279 l_get_crsp_hist c_get_crsp_hist%ROWTYPE;
280
281 CURSOR c_todo_item_valid(cp_cal_type VARCHAR2,
282 cp_seq_number NUMBER,
283 cp_item_code VARCHAR2)
284 IS
285 (
286 SELECT todo_number
287 FROM igf_ap_td_item_mst_all
288 WHERE ci_cal_type = cp_cal_type
289 AND ci_sequence_number = cp_seq_number
290 AND item_code = NVL(cp_item_code,item_code)
291 AND CAREER_ITEM = 'N'
292 UNION
293 SELECT todo_number
294 FROM igf_ap_td_item_mst_all
295 WHERE item_code = NVL(cp_item_code,item_code)
296 AND CAREER_ITEM = 'Y'
297 );
298
299 l_todo_item_valid c_todo_item_valid%ROWTYPE;
300
301 CURSOR c_todo_dup(cp_base_id NUMBER,
302 cp_item_code VARCHAR2)
303 IS
304 SELECT item_sequence_number
305 FROM igf_ap_td_item_inst_v
306 WHERE base_id = cp_base_id
307 AND item_code = cp_item_code;
308
309 l_todo_dup c_todo_dup%ROWTYPE;
310
311 CURSOR c_get_rowid(cp_item_seq_number NUMBER,
312 cp_base_id NUMBER)
313 IS
314 SELECT ROWID ROW_ID
315 FROM igf_ap_td_item_inst
316 WHERE base_id = cp_base_id
317 AND item_sequence_number = cp_item_seq_number;
318
319 l_get_rowid c_get_rowid%ROWTYPE;
320
321 -- museshad (FA 140)
322 CURSOR c_item_system_todo_type_code(
323 cp_item_code VARCHAR2,
324 cp_cal_type igs_ca_inst_all.cal_type%TYPE,
325 cp_seq_number igs_ca_inst_all.sequence_number%TYPE
326 )
327 IS
328 SELECT system_todo_type_code
329 FROM igf_ap_td_item_mst_all
330 WHERE
331 item_code = cp_item_code AND
332 system_todo_type_code IS NOT NULL;
333
334 l_item_system_todo_type_code igf_ap_td_item_mst_all.system_todo_type_code%TYPE;
335
336 CURSOR c_chk_lender_rel (cp_rel_cd igf_sl_cl_recipient.relationship_cd%TYPE)
337 IS
338 SELECT 'X'
339 FROM igf_sl_cl_recipient
340 WHERE relationship_cd = cp_rel_cd AND
341 UPPER(ENABLED) = 'Y';
342
343 l_chk_lender_rel_rec VARCHAR2(1);
344 l_clprl_id igf_sl_cl_pref_lenders.clprl_id%TYPE;
345 -- museshad (FA 140)
346
347 BEGIN
348 igf_aw_gen.set_org_id(NULL);
349 errbuf := NULL;
350 retcode := 0;
351 l_cal_type := LTRIM(RTRIM(SUBSTR(p_award_year,1,10)));
352 l_seq_number := TO_NUMBER(SUBSTR(p_award_year,11));
353
354 l_error := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','ERROR');
355
356 l_chk_profile := igf_ap_gen.check_profile;
357 IF l_chk_profile = 'N' THEN
358 fnd_message.set_name('IGF','IGF_AP_LGCY_PROC_NOT_RUN');
359 fnd_file.put_line(fnd_file.log,RPAD(l_error,12) || fnd_message.get);
360 RETURN;
361 END IF;
362
363 -- this is to get the alternate code
364 l_get_alternate_code := NULL;
365 OPEN c_get_alternate_code(l_cal_type,l_seq_number);
366 FETCH c_get_alternate_code INTO l_get_alternate_code;
367 CLOSE c_get_alternate_code;
368
369 -- this is to check that the award year is valid or not
370 l_get_status := NULL;
371 OPEN c_get_status(l_cal_type,l_seq_number);
372 FETCH c_get_status INTO l_get_status;
373 CLOSE c_get_status;
374
375 IF l_get_status.sys_award_year IS NULL THEN
376 fnd_message.set_name('IGF','IGF_AP_AWD_YR_NOT_FOUND');
377 fnd_message.set_token('P_AWARD_YEAR',p_award_year);
378 add_log_table(NULL,l_error,fnd_message.get);
379 l_terminate_flag := TRUE;
380 ELSIF
381 l_get_status.award_year_status_code NOT IN('LD','O') THEN
382 fnd_message.set_name('IGF','IGF_AP_LG_INVALID_STAT');
383 fnd_message.set_token('AWARD_STATUS',l_get_status.award_year_status_code);
384 add_log_table(NULL,l_error,fnd_message.get);
385 l_terminate_flag := TRUE;
386 END IF;
387
388 l_chk_batch := igf_ap_gen.check_batch(p_batch_id,'TODO');
389 IF l_chk_batch = 'N' THEN
390 fnd_message.set_name('IGF','IGF_GR_BATCH_DOES_NOT_EXIST');
391 add_log_table(NULL,l_error,fnd_message.get);
392 l_terminate_flag := TRUE;
393 END IF;
394
395 -- this is to check that the todo item setup is valid or not
396 l_todo_item_valid := NULL;
397 OPEN c_todo_item_valid(l_cal_type,l_seq_number,NULL);
398 FETCH c_todo_item_valid INTO l_todo_item_valid;
399 CLOSE c_todo_item_valid;
400
401 IF l_terminate_flag = TRUE THEN
402 print_log_process(l_get_alternate_code.alternate_code,p_batch_id,p_del_ind);
403 RETURN;
404 END IF;
405 -- THE MAIN LOOP STARTS HERE FOR FETCHING THE RECORD FROM THE INTERFACE TABLE
406
407 OPEN c_get_persons(l_get_alternate_code.alternate_code,p_batch_id);
408 LOOP
409 BEGIN
410 SAVEPOINT sp1;
411 FETCH c_get_persons INTO l_get_persons;
412 EXIT WHEN c_get_persons%NOTFOUND OR c_get_persons%NOTFOUND IS NULL;
413 l_debug_str := 'Tdint_id is:' || l_get_persons.tdint_id;
414 IF l_old_person_number <> l_get_persons.person_number THEN -- THIS IS TO SEE IF THE CURRENT RECORD IS A NEW RECORD (HAVING DIFFERENT PERSON NUMBER)
415 l_debug_str := l_debug_str || ' Inside new person check';
416 IF l_person_skip_flag = FALSE AND l_old_person_number <> '******' THEN --INDIACTES THAT THE PERSON WAS NOT SKIPPED
417 IF l_todo_flag = TRUE THEN --- THIS MEANS THAT AT LEAST ONE TODO ITEMS OF THE PERSON HAS BEEN SUCESSFULLY IMPORTED
418 -- updation of the FA Base Record Application Processing Status
419 update_fabase_process(l_old_person_number);
420 END IF;
421 END IF;
422
423 l_person_skip_flag := FALSE;
424 l_todo_flag := FALSE;
425 --set the old person and perform all the person level validations
426 l_old_person_number := l_get_persons.person_number;
427 lv_base_id := NULL;
428 lv_person_id := NULL;
429 --HERE CALL TO THE GENERIC WRAPPER IS BEING MADE TO CHEHK THE VALIDITY OF THE PEROSN AND BASE ID
430 igf_ap_gen.check_person(l_get_persons.person_number,l_cal_type,l_seq_number,lv_person_id,lv_base_id);
431 IF lv_person_id IS NULL THEN
432 fnd_message.set_name('IGF','IGF_AP_PE_NOT_EXIST');
433 add_log_table(l_get_persons.person_number,l_error,fnd_message.get);
434 l_person_skip_flag := TRUE;
435 ELSE
436 IF lv_base_id IS NULL THEN
437 fnd_message.set_name('IGF','IGF_AP_FABASE_NOT_FOUND');
438 add_log_table(l_get_persons.person_number,l_error,fnd_message.get);
439 l_person_skip_flag := TRUE;
440 ELSE
441 g_baseid_exists := NULL;
442 OPEN c_baseid_exists(lv_base_id);
443 FETCH c_baseid_exists INTO g_baseid_exists;
444 CLOSE c_baseid_exists;
445 END IF;
446 END IF;
447 l_debug_str := l_debug_str || ' Person and Base ID check passed';
448 l_get_crsp_hist := NULL;
449 OPEN c_get_crsp_hist(l_cal_type,l_seq_number,l_get_persons.person_number);
450 FETCH c_get_crsp_hist INTO l_get_crsp_hist;
451 CLOSE c_get_crsp_hist;
452 IF l_get_crsp_hist.person_id IS NOT NULL THEN
453 fnd_message.set_name('IGF','IGF_AP_TD_CORSP_HIST_EXIST');
454 add_log_table(l_get_persons.person_number,l_error,fnd_message.get);
455 l_person_skip_flag := TRUE;
456 END IF;
457 IF l_person_skip_flag = TRUE THEN
458 UPDATE igf_ap_li_todo_ints
459 SET import_status_type = 'E'
460 WHERE ci_alternate_code = l_get_alternate_code.alternate_code
461 AND person_number = l_get_persons.person_number
462 AND batch_num = p_batch_id;
463 END IF;
464 l_debug_str := l_debug_str || ' Correspondence check passed';
465 END IF; -- HERE THE CHECK FOR DIFFERENT PERSON ENDS
466
467 IF l_person_skip_flag = FALSE THEN
468 -- the person is not to be skipped and the record level validations are to be done
469 fnd_message.set_name('IGF','IGF_AP_PROC_ITM');
470 fnd_message.set_token('ITEM',l_get_persons.item_code);
471 l_proc_item_str := fnd_message.get;
472 l_todo_item_valid := NULL;
473 OPEN c_todo_item_valid(l_cal_type,l_seq_number,l_get_persons.item_code);
474 FETCH c_todo_item_valid INTO l_todo_item_valid;
475 CLOSE c_todo_item_valid;
476 IF l_todo_item_valid.todo_number IS NULL THEN
477 fnd_message.set_name('IGF','IGF_AP_TD_INVALID_ITM');
478 fnd_message.set_token('ITEM',l_get_persons.item_code);
479 l_message_str := l_proc_item_str || ' ' || fnd_message.get;
480 add_log_table(l_get_persons.person_number,l_error,l_message_str);
481 l_message_str := NULL;
482 l_error_flag := TRUE;
483 ELSE
484 l_debug_str := l_debug_str || ' Valid to do item passed';
485 l_todo_dup := NULL;
486 OPEN c_todo_dup(g_baseid_exists.base_id,l_get_persons.item_code);
487 FETCH c_todo_dup INTO l_todo_dup;
488 CLOSE c_todo_dup;
489 IF l_todo_dup.item_sequence_number IS NOT NULL AND NVL(l_get_persons.import_record_type,'X') <> 'U' THEN
490 fnd_message.set_name('IGF','IGF_AP_TD_ITM_EXIST');
491 fnd_message.set_token('ITEM',l_get_persons.item_code);
492 l_message_str := l_proc_item_str || ' ' || fnd_message.get;
493 add_log_table(l_get_persons.person_number,l_error,l_message_str);
494 l_message_str := NULL;
495 l_error_flag := TRUE;
496 END IF;
497 ----HERE THE VALIDATION IS TO BE DONE IF NULL AND 'U'
498 IF l_todo_dup.item_sequence_number IS NULL AND NVL(l_get_persons.import_record_type,'X') = 'U' THEN
499 fnd_message.set_name('IGF','IGF_AP_ORIG_REC_NOT_FOUND');
500 l_message_str := l_proc_item_str || ' ' || fnd_message.get;
501 add_log_table(l_get_persons.person_number,l_error,l_message_str);
502 l_message_str := NULL;
503 l_error_flag := TRUE;
504 END IF;
505 -- validation for the add date
506 IF l_get_persons.item_add_date > TRUNC(SYSDATE) THEN
507 fnd_message.set_name('IGF','IGF_AP_TODO_DATE_GR_SYSDT');
508 l_message_str := l_proc_item_str || ' ' || fnd_message.get;
509 add_log_table(l_get_persons.person_number,l_error,l_message_str);
510 l_message_str := NULL;
511 l_error_flag := TRUE;
512 END IF;
513 --validation for the item status
514 l_get_meaning := igf_ap_gen.get_lookup_meaning('IGF_TD_ITEM_STATUS',l_get_persons.item_status_code);
515 IF l_get_meaning IS NULL THEN
516 fnd_message.set_name('IGF','IGF_AP_TODO_INVALID_STAT');
517 l_message_str := l_proc_item_str || ' ' || fnd_message.get;
518 add_log_table(l_get_persons.person_number,l_error,l_message_str);
519 l_message_str := NULL;
520 l_error_flag := TRUE;
521 END IF;
522 --VALIDATION FOR THE STATUS DATE
523 IF l_get_persons.item_status_date IS NULL THEN
524 l_get_persons.item_status_date := TRUNC(SYSDATE);
525 ELSIF l_get_persons.item_status_date < l_get_persons.item_add_date OR l_get_persons.item_status_date > TRUNC(SYSDATE) THEN
526 fnd_message.set_name('IGF','IGF_AP_STATUS_DATE');
527 l_message_str := l_proc_item_str || ' ' || fnd_message.get;
528 add_log_table(l_get_persons.person_number,l_error,l_message_str);
529 l_message_str := NULL;
530 l_error_flag := TRUE;
531 END IF;
532 l_debug_str := l_debug_str || ' Status Date passed';
533 --VALIDATION FOR THE CORRESPONDENCE DATE...
534 IF l_get_persons.corsp_date IS NOT NULL AND (l_get_persons.corsp_date < l_get_persons.item_add_date OR l_get_persons.corsp_date > TRUNC(SYSDATE)) THEN
535 fnd_message.set_name('IGF','IGF_AP_CRSP_INVALID_DT');
536 l_message_str := l_proc_item_str || ' ' || fnd_message.get;
537 add_log_table(l_get_persons.person_number,l_error,l_message_str);
538 l_message_str := NULL;
539 l_error_flag := TRUE;
540 END IF;
541 --VALIDATION FOR THE CORRESPONDENCE COUNT
542 --FIRST NEGATIVE CORR COUNT IS CHECKED
543 IF NVL(l_get_persons.corsp_count_num,0) < 0 THEN
544 fnd_message.set_name('IGF','IGF_AP_TD_CORR_COUNT_NEG');
545 l_message_str := l_proc_item_str || ' ' || fnd_message.get;
546 add_log_table(l_get_persons.person_number,l_error,l_message_str);
547 l_message_str := NULL;
548 l_error_flag := TRUE;
549 END IF;
550 IF l_get_persons.corsp_count_num IS NOT NULL AND l_get_persons.corsp_date IS NULL THEN
551 fnd_message.set_name('IGF','IGF_AP_TD_CORR_DATE_REQ');
552 l_message_str := l_proc_item_str || ' ' || fnd_message.get;
553 add_log_table(l_get_persons.person_number,l_error,l_message_str);
554 l_message_str := NULL;
555 l_error_flag := TRUE;
556 END IF;
557 IF NVL(l_get_persons.corsp_count_num,0) = 0 AND l_get_persons.corsp_date IS NOT NULL THEN
558 fnd_message.set_name('IGF','IGF_AP_CRSP_COUNT');
559 l_message_str := l_proc_item_str ||' ' || fnd_message.get;
560 add_log_table(l_get_persons.person_number,l_error,l_message_str);
561 l_message_str := NULL;
562 l_error_flag := TRUE;
563 END IF;
564 --VALIDATION FOR THE INACTIVE FLAG
565 IF NVL(l_get_persons.inactive_flag,'X') NOT IN ('Y','N') THEN
566 fnd_message.set_name('IGF','IGF_AP_TD_FLAG_INCORR');
567 l_message_str := l_proc_item_str || ' ' || fnd_message.get;
568 add_log_table(l_get_persons.person_number,l_error,l_message_str);
569 l_message_str := NULL;
570 l_error_flag := TRUE;
571 END IF;
572
573 -- museshad (FA 140)
574 -- VALIDATION FOR PREFERRED LENDER
575 l_item_system_todo_type_code := NULL;
576 OPEN c_item_system_todo_type_code(
577 cp_item_code => l_get_persons.item_code,
578 cp_cal_type => l_cal_type,
579 cp_seq_number => l_seq_number
580 );
581 FETCH c_item_system_todo_type_code INTO l_item_system_todo_type_code;
582
583 IF (c_item_system_todo_type_code%FOUND) THEN
584
585 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
586 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_ap_lg_td_imp.main.debug', 'l_item_system_todo_type_code: '||l_item_system_todo_type_code);
587 fnd_log.string(fnd_log.level_statement, 'igf.plsql.igf_ap_lg_td_imp.main.debug', 'l_get_persons.item_status_code: '||l_get_persons.item_status_code);
588 END IF;
589
590 IF l_item_system_todo_type_code = 'PREFLEND' THEN
591
592 IF l_get_persons.item_status_code = 'COM' THEN
593
594 IF (l_get_persons.relationship_cd IS NULL) THEN
595 -- Error
596 fnd_message.set_name('IGF', 'IGF_AP_TD_PREFL_INCOM');
597 l_message_str := l_proc_item_str || ' ' || fnd_message.get;
598 add_log_table(l_get_persons.person_number, l_error, l_message_str);
599 l_message_str := NULL;
600 l_error_flag := TRUE;
601 ELSE
602 -- Check if the relationship is valid
603 OPEN c_chk_lender_rel (cp_rel_cd => l_get_persons.relationship_cd);
604 FETCH c_chk_lender_rel INTO l_chk_lender_rel_rec;
605
606 IF (c_chk_lender_rel%NOTFOUND) THEN
607 -- Error
608 fnd_message.set_name('IGF', 'IGF_AP_TD_PREFL_NTFND');
609 fnd_message.set_token('RELCD', l_get_persons.relationship_cd);
610 l_message_str := l_proc_item_str || ' ' || fnd_message.get;
611 add_log_table(l_get_persons.person_number, l_error, l_message_str);
612 l_message_str := NULL;
613 l_error_flag := TRUE;
614 END IF;
615 CLOSE c_chk_lender_rel;
616 END IF;
617
618 ELSE
619 -- When the status code is not 'COM', then the Relationship code
620 -- must be NULL
621 IF (l_get_persons.relationship_cd IS NOT NULL) THEN
622 -- Error
623 fnd_message.set_name('IGF','IGF_AP_TD_PREFL_INCOM');
624 l_message_str := l_proc_item_str || ' ' || fnd_message.get;
625 add_log_table(l_get_persons.person_number, l_error, l_message_str);
626 l_message_str := NULL;
627 l_error_flag := TRUE;
628 END IF;
629 END IF;
630
631 ELSE
632 -- When the System todo type code of that item is not 'PREFLEND'
633 -- then Relationship code must be NULL
634 IF (l_get_persons.relationship_cd IS NOT NULL) THEN
635 -- Error
636 fnd_message.set_name('IGF','IGF_AP_TD_PREFL_INCOM');
637 l_message_str := l_proc_item_str || ' ' || fnd_message.get;
638 add_log_table(l_get_persons.person_number,l_error,l_message_str);
639 l_message_str := NULL;
640 l_error_flag := TRUE;
641 END IF;
642 END IF; -- End l_item_system_todo_type_code
643 END IF; -- End c_item_system_todo_type_code%FOUND
644
645 CLOSE c_item_system_todo_type_code;
646 -- museshad (FA 140)
647
648 l_debug_str := l_debug_str || ' Inactive flag and correspondence count item passed';
649 IF l_error_flag = FALSE THEN
650 IF l_todo_dup.item_sequence_number IS NULL AND NVL(l_get_persons.import_record_type,'X') <> 'U' THEN
651 --insert the record
652 l_todo_number := l_todo_item_valid.todo_number;
653 ELSE
654 --delete and insert the record again
655 --obtain the row_id from the item_sequence_number and the base_id from the
656 l_todo_number := l_todo_dup.item_sequence_number;
657 l_get_rowid := NULL;
658 OPEN c_get_rowid(l_todo_dup.item_sequence_number,g_baseid_exists.base_id);
659 FETCH c_get_rowid INTO l_get_rowid;
660 CLOSE c_get_rowid;
661 igf_ap_td_item_inst_pkg.delete_row(
662 x_ROWID => l_get_rowid.ROW_ID
663 );
664 END IF;
665 -- museshad FA 140
666 -- Insert Pref lender details
667 IF l_item_system_todo_type_code = 'PREFLEND' AND l_get_persons.relationship_cd IS NOT NULL THEN
668 process_pref_lender(g_baseid_exists.base_id, l_get_persons.relationship_cd, l_clprl_id);
669 END IF;
670 -- museshad FA 140
671 igf_ap_td_item_inst_pkg.insert_row(
672 x_MODE => 'R',
673 x_BASE_ID => g_baseid_exists.base_id,
674 x_ROWID => lv_row_id,
675 x_ITEM_SEQUENCE_NUMBER => l_todo_number,
676 x_STATUS => l_get_persons.item_status_code,
677 x_STATUS_DATE => l_get_persons.item_status_date,
678 x_ADD_DATE => l_get_persons.item_add_date,
679 x_CORSP_DATE => l_get_persons.corsp_date,
680 x_CORSP_COUNT => l_get_persons.corsp_count_num,
681 x_INACTIVE_FLAG => l_get_persons.inactive_flag,
682 x_FREQ_ATTEMPT => l_get_persons.freq_attempt_num,
683 x_MAX_ATTEMPT => l_get_persons.max_attempt_num,
684 x_REQUIRED_FOR_APPLICATION => l_get_persons.reqd_for_application_flag,
685 x_LEGACY_RECORD_FLAG => 'Y',
686 x_clprl_id => l_clprl_id
687 );
688 l_debug_str := l_debug_str || ' Record Insertion passed';
689 l_success_record_cnt := l_success_record_cnt + 1;
690 l_todo_flag := TRUE;
691 l_todo_number := NULL;
692 IF p_del_ind = 'Y' THEN
693 DELETE FROM igf_ap_li_todo_ints
694 WHERE ROWID = l_get_persons.ROW_ID;
695 ELSE
696 --update the legacy interface table column import_status to 'I'
697 UPDATE igf_ap_li_todo_ints
698 SET import_status_type = 'I'
699 WHERE ROWID = l_get_persons.ROW_ID;
700 END IF;
701 END IF;
702 END IF; -- for the valid todo item
703 ELSE
704 l_error_record_cnt := l_error_record_cnt + 1;
705 END IF; -- for the person skip flag
706
707 IF l_error_flag = TRUE THEN
708 l_error_flag := FALSE;
709 l_error_record_cnt := l_error_record_cnt + 1;
710 --update the legacy interface table column import_status to 'E'
711 UPDATE igf_ap_li_todo_ints
712 SET import_status_type = 'E'
713 WHERE ROWID = l_get_persons.ROW_ID;
714 END IF;
715 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
716 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_ap_lg_td_imp.main.debug',l_debug_str);
717 END IF;
718 l_proc_item_str := NULL;
719 l_get_meaning := NULL;
720 l_debug_str := NULL;
721 EXCEPTION
722 WHEN others THEN
723 l_todo_flag := FALSE;
724 l_error_flag := FALSE;
725 l_proc_item_str := NULL;
726 l_get_meaning := NULL;
727 l_debug_str := NULL;
728 fnd_message.set_name('IGS','IGS_GE_UNHANDLED_EXP');
729 fnd_message.set_token('NAME','IGF_AP_LG_TD_IMP.MAIN');
730 add_log_table(l_get_persons.person_number,l_error,fnd_message.get);
731 ROLLBACK TO sp1;
732 END;
733 COMMIT;
734
735 END LOOP;
736 IF l_person_skip_flag = FALSE AND l_todo_flag = TRUE THEN
737 update_fabase_process(l_old_person_number);
738 END IF;
739 CLOSE c_get_persons;
740
741 IF l_success_record_cnt = 0 AND l_error_record_cnt = 0 THEN
742 fnd_message.set_name('IGS','IGS_FI_NO_RECORD_AVAILABLE');
743 add_log_table(NULL,l_error,fnd_message.get);
744 END IF;
745
746 -- CALL THE PRINT LOG PROCESS
747 print_log_process(l_get_alternate_code.alternate_code,p_batch_id,p_del_ind);
748
749 l_total_record_cnt := l_success_record_cnt + l_error_record_cnt;
750 fnd_message.set_name('IGS','IGS_GE_TOTAL_REC_PROCESSED');
751 fnd_file.put_line(fnd_file.OUTPUT,fnd_message.get || ' ' || l_total_record_cnt);
752 fnd_message.set_name('IGS','IGS_AD_SUCC_IMP_OFR_RESP_REC');
753 fnd_file.put_line(fnd_file.OUTPUT,fnd_message.get || ' : ' || l_success_record_cnt);
754 fnd_message.set_name('IGS','IGS_GE_TOTAL_REC_FAILED');
755 fnd_file.put_line(fnd_file.OUTPUT,fnd_message.get || ' : ' || l_error_record_cnt);
756
757 EXCEPTION
758 WHEN others THEN
759 --CALL TO THE COMMON LOGGING FRAMEWORK FOR DEBUG MESSAGES
760 IF fnd_log.level_exception >= fnd_log.g_current_runtime_level THEN
761 fnd_log.string(fnd_log.level_exception,'igf.plsql.igf_ap_lg_td_imp.main.exception','Exception: '||SQLERRM);
762 END IF;
763 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
764 fnd_message.set_token('NAME','IGF_AP_LG_TD_IMP.MAIN');
765 errbuf := fnd_message.get;
766 igs_ge_msg_stack.conc_exception_hndl;
767
768
769 END main;
770
771 PROCEDURE update_fabase_process(p_person_number IN VARCHAR2)
772 IS
773 /*
774 || Created By : museshad
775 || Created On : 28-Jul-2005
776 || Purpose : Build FA 140
777 || Implements the new logic for deriving the FA Base record
778 || application status
779 || Known limitations, enhancements or remarks :
780 || Change History :
781 || Who When What
782 || (reverse chronological order - newest change first)
783 */
784 -- Get person_id
785 CURSOR c_person_id(cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
786 IS
787 SELECT person_id
788 FROM igf_ap_fa_base_rec_all
789 WHERE base_id = cp_base_id;
790
791 CURSOR cur_todo(
792 cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE,
793 cp_person_id igf_ap_fa_base_rec_all.person_id%TYPE
794 )
795 IS
796 SELECT 1
797 FROM igf_ap_td_item_inst_all tdinst,
798 igf_ap_td_item_mst_all tdmst
799 WHERE tdinst.base_id = cp_base_id
800 AND tdinst.status IN ('INC','REQ','REC')
801 AND tdinst.required_for_application = 'Y'
802 AND NVL(tdinst.inactive_flag,'N') <> 'Y'
803 AND tdinst.item_sequence_number = tdmst.todo_number
804 AND tdmst.career_item = 'N'
805 AND ROWNUM < 2
806 UNION
807 SELECT 1
808 FROM igf_ap_td_item_inst_all tdinst,
809 igf_ap_td_item_mst_all tdmst,
810 igf_ap_fa_base_rec_all fa
811 WHERE tdinst.base_id = fa.base_id
812 AND tdinst.status IN ('INC','REQ','REC')
813 AND tdinst.required_for_application = 'Y'
814 AND NVL(tdinst.inactive_flag,'N') <> 'Y'
815 AND tdinst.item_sequence_number = tdmst.todo_number
816 AND tdmst.career_item = 'Y'
817 AND fa.person_id = cp_person_id
818 AND ROWNUM < 2;
819
820 CURSOR cur_ver_status (cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
821 IS
822 SELECT NVL(fa_process_status,'RECEIVED') fa_process_status
823 FROM igf_ap_fa_base_rec
824 WHERE base_id = cp_base_id;
825
826 ln_count_open_items NUMBER;
827 l_person_id hz_parties.party_id%TYPE;
828 lv_fa_process_status igf_ap_fa_base_rec_all.fa_process_status%TYPE;
829 ln_auto_na_complete VARCHAR2(80);
830
831 BEGIN
832
833 -- Get Person Id
834 OPEN c_person_id(g_baseid_exists.base_id);
835 FETCH c_person_id INTO l_person_id;
836 CLOSE c_person_id;
837
838 fnd_profile.get('IGF_AP_MANUAL_REVIEW_APPL', ln_auto_na_complete);
839 ln_auto_na_complete := NVL(ln_auto_na_complete, 'N');
840
841 OPEN cur_ver_status (g_baseid_exists.base_id);
842 FETCH cur_ver_status INTO lv_fa_process_status;
843 CLOSE cur_ver_status;
844
845 OPEN cur_todo (g_baseid_exists.base_id, l_person_id);
846 FETCH cur_todo INTO ln_count_open_items;
847 IF cur_todo%NOTFOUND THEN
848 ln_count_open_items := 0;
849 ELSE
850 ln_count_open_items := 1;
851 END IF;
852 CLOSE cur_todo;
853
854 -- Update FA Base record with the right Application Status
855 IF lv_fa_process_status = 'RECEIVED' AND ln_count_open_items = 0 THEN
856 IF ln_auto_na_complete = 'Y' THEN
857 update_fabase_rec('MANUAL_REVIEW');
858 fnd_message.set_name('IGF','IGF_AP_APP_STAT_RVW');
859 add_log_table(p_person_number,' ',fnd_message.get);
860 ELSE
861 update_fabase_rec('COMPLETE');
862 fnd_message.set_name('IGF','IGF_AP_APP_STAT_COMPLETE');
863 add_log_table(p_person_number,' ',fnd_message.get);
864 END IF;
865 ELSIF ln_count_open_items > 0 THEN
866 update_fabase_rec('RECEIVED');
867 fnd_message.set_name('IGF', 'IGF_AP_APP_STAT_REC');
868 add_log_table(p_person_number,' ',fnd_message.get);
869 END IF;
870 END update_fabase_process;
871
872 PROCEDURE update_fabase_rec(
873 p_fa_process_status IN VARCHAR2
874 ) IS
875 /*
876 || Created By : bkkumar
877 || Created On : 26-MAY-2003
878 || Purpose : This process updates the FA Base Record Application Processing Status
879 || Known limitations, enhancements or remarks :
880 || Change History :
881 || Who When What
882 || (reverse chronological order - newest change first)
883 || rasahoo 17-NOV-2003 FA 128 - ISIR update 2004-05
884 || added new parameter award_fmly_contribution_type to
885 || igf_ap_fa_base_rec_pkg.update_row
886 || ugummall 25-SEP-2003 FA 126 - Multiple FA Offices
887 || added new parameter assoc_org_num to
888 || igf_ap_fa_base_rec_pkg.update_row call
889 */
890
891 BEGIN
892 igf_ap_fa_base_rec_pkg.update_row(
893 x_Mode => 'R' ,
894 x_rowid => g_baseid_exists.row_id ,
895 x_base_id => g_baseid_exists.base_id ,
896 x_ci_cal_type => g_baseid_exists.ci_cal_type ,
897 x_person_id => g_baseid_exists.person_id ,
898 x_ci_sequence_number => g_baseid_exists.ci_sequence_number ,
899 x_org_id => g_baseid_exists.org_id ,
900 x_coa_pending => g_baseid_exists.coa_pending ,
901 x_verification_process_run => g_baseid_exists.verification_process_run ,
902 x_inst_verif_status_date => g_baseid_exists.inst_verif_status_date ,
903 x_manual_verif_flag => g_baseid_exists.manual_verif_flag ,
904 x_fed_verif_status => g_baseid_exists.fed_verif_status ,
905 x_fed_verif_status_date => g_baseid_exists.fed_verif_status_date ,
906 x_inst_verif_status => g_baseid_exists.inst_verif_status ,
907 x_nslds_eligible => g_baseid_exists.nslds_eligible ,
908 x_ede_correction_batch_id => g_baseid_exists.ede_correction_batch_id ,
909 x_fa_process_status_date => TRUNC(SYSDATE) ,
910 x_isir_corr_status => g_baseid_exists.isir_corr_status ,
911 x_isir_corr_status_date => g_baseid_exists.isir_corr_status_date ,
912 x_isir_status => g_baseid_exists.isir_status ,
913 x_isir_status_date => g_baseid_exists.isir_status_date ,
914 x_coa_code_f => g_baseid_exists.coa_code_f ,
915 x_coa_code_i => g_baseid_exists.coa_code_i ,
916 x_coa_f => g_baseid_exists.coa_f ,
917 x_coa_i => g_baseid_exists.coa_i ,
918 x_disbursement_hold => g_baseid_exists.disbursement_hold ,
919 x_fa_process_status => p_fa_process_status ,
920 x_notification_status => g_baseid_exists.notification_status ,
921 x_notification_status_date => g_baseid_exists.notification_status_date ,
922 x_packaging_status => g_baseid_exists.packaging_status ,
923 x_packaging_status_date => g_baseid_exists.packaging_status_date ,
924 x_total_package_accepted => g_baseid_exists.total_package_accepted ,
925 x_total_package_offered => g_baseid_exists.total_package_offered ,
926 x_admstruct_id => g_baseid_exists.admstruct_id ,
927 x_admsegment_1 => g_baseid_exists.admsegment_1 ,
928 x_admsegment_2 => g_baseid_exists.admsegment_2 ,
929 x_admsegment_3 => g_baseid_exists.admsegment_3 ,
930 x_admsegment_4 => g_baseid_exists.admsegment_4 ,
931 x_admsegment_5 => g_baseid_exists.admsegment_5 ,
932 x_admsegment_6 => g_baseid_exists.admsegment_6 ,
933 x_admsegment_7 => g_baseid_exists.admsegment_7 ,
934 x_admsegment_8 => g_baseid_exists.admsegment_8 ,
935 x_admsegment_9 => g_baseid_exists.admsegment_9 ,
936 x_admsegment_10 => g_baseid_exists.admsegment_10 ,
937 x_admsegment_11 => g_baseid_exists.admsegment_11 ,
938 x_admsegment_12 => g_baseid_exists.admsegment_12 ,
939 x_admsegment_13 => g_baseid_exists.admsegment_13 ,
940 x_admsegment_14 => g_baseid_exists.admsegment_14 ,
941 x_admsegment_15 => g_baseid_exists.admsegment_15 ,
942 x_admsegment_16 => g_baseid_exists.admsegment_16 ,
943 x_admsegment_17 => g_baseid_exists.admsegment_17 ,
944 x_admsegment_18 => g_baseid_exists.admsegment_18 ,
945 x_admsegment_19 => g_baseid_exists.admsegment_19 ,
946 x_admsegment_20 => g_baseid_exists.admsegment_20 ,
947 x_packstruct_id => g_baseid_exists.packstruct_id ,
948 x_packsegment_1 => g_baseid_exists.packsegment_1 ,
949 x_packsegment_2 => g_baseid_exists.packsegment_2 ,
950 x_packsegment_3 => g_baseid_exists.packsegment_3 ,
951 x_packsegment_4 => g_baseid_exists.packsegment_4 ,
952 x_packsegment_5 => g_baseid_exists.packsegment_5 ,
953 x_packsegment_6 => g_baseid_exists.packsegment_6 ,
954 x_packsegment_7 => g_baseid_exists.packsegment_7 ,
955 x_packsegment_8 => g_baseid_exists.packsegment_8 ,
956 x_packsegment_9 => g_baseid_exists.packsegment_9 ,
957 x_packsegment_10 => g_baseid_exists.packsegment_10 ,
958 x_packsegment_11 => g_baseid_exists.packsegment_11 ,
959 x_packsegment_12 => g_baseid_exists.packsegment_12 ,
960 x_packsegment_13 => g_baseid_exists.packsegment_13 ,
961 x_packsegment_14 => g_baseid_exists.packsegment_14 ,
962 x_packsegment_15 => g_baseid_exists.packsegment_15 ,
963 x_packsegment_16 => g_baseid_exists.packsegment_16 ,
964 x_packsegment_17 => g_baseid_exists.packsegment_17 ,
965 x_packsegment_18 => g_baseid_exists.packsegment_18 ,
966 x_packsegment_19 => g_baseid_exists.packsegment_19 ,
967 x_packsegment_20 => g_baseid_exists.packsegment_20 ,
968 x_miscstruct_id => g_baseid_exists.miscstruct_id ,
969 x_miscsegment_1 => g_baseid_exists.miscsegment_1 ,
970 x_miscsegment_2 => g_baseid_exists.miscsegment_2 ,
971 x_miscsegment_3 => g_baseid_exists.miscsegment_3 ,
972 x_miscsegment_4 => g_baseid_exists.miscsegment_4 ,
973 x_miscsegment_5 => g_baseid_exists.miscsegment_5 ,
974 x_miscsegment_6 => g_baseid_exists.miscsegment_6 ,
975 x_miscsegment_7 => g_baseid_exists.miscsegment_7 ,
976 x_miscsegment_8 => g_baseid_exists.miscsegment_8 ,
977 x_miscsegment_9 => g_baseid_exists.miscsegment_9 ,
978 x_miscsegment_10 => g_baseid_exists.miscsegment_10 ,
979 x_miscsegment_11 => g_baseid_exists.miscsegment_11 ,
980 x_miscsegment_12 => g_baseid_exists.miscsegment_12 ,
981 x_miscsegment_13 => g_baseid_exists.miscsegment_13 ,
982 x_miscsegment_14 => g_baseid_exists.miscsegment_14 ,
983 x_miscsegment_15 => g_baseid_exists.miscsegment_15 ,
984 x_miscsegment_16 => g_baseid_exists.miscsegment_16 ,
985 x_miscsegment_17 => g_baseid_exists.miscsegment_17 ,
986 x_miscsegment_18 => g_baseid_exists.miscsegment_18 ,
987 x_miscsegment_19 => g_baseid_exists.miscsegment_19 ,
988 x_miscsegment_20 => g_baseid_exists.miscsegment_20 ,
989 x_prof_judgement_flg => g_baseid_exists.prof_judgement_flg ,
990 x_nslds_data_override_flg => g_baseid_exists.nslds_data_override_flg ,
991 x_target_group => g_baseid_exists.target_group ,
992 x_coa_fixed => g_baseid_exists.coa_fixed ,
993 x_coa_pell => g_baseid_exists.coa_pell ,
994 x_profile_status => g_baseid_exists.profile_status ,
995 x_profile_status_date => g_baseid_exists.profile_status_date ,
996 x_profile_fc => g_baseid_exists.profile_fc ,
997 x_manual_disb_hold => g_baseid_exists.manual_disb_hold ,
998 x_pell_alt_expense => g_baseid_exists.pell_alt_expense,
999 x_assoc_org_num => g_baseid_exists.assoc_org_num,
1000 x_award_fmly_contribution_type => g_baseid_exists.award_fmly_contribution_type,
1001 x_isir_locked_by => g_baseid_exists.isir_locked_by,
1002 x_adnl_unsub_loan_elig_flag => g_baseid_exists.adnl_unsub_loan_elig_flag,
1003 x_lock_awd_flag => g_baseid_exists.lock_awd_flag,
1004 x_lock_coa_flag => g_baseid_exists.lock_coa_flag
1005 );
1006
1007 END update_fabase_rec;
1008
1009 PROCEDURE process_pref_lender(
1010 p_base_id IN igf_ap_fa_base_rec_all.base_id%TYPE,
1011 p_rel_cd IN igf_ap_li_todo_ints.relationship_cd%TYPE,
1012 p_clprl_id OUT NOCOPY igf_sl_cl_pref_lenders.clprl_id%TYPE
1013 )
1014 IS
1015
1016 /*
1017 || Created By : museshad
1018 || Created On : 28-Jul-2005
1019 || Purpose : Inserts Pref. lender details. This proc. gets called
1020 || when a completed preferred lender to do item is
1021 || successfully imported.
1022 || Known limitations, enhancements or remarks :
1023 || Change History :
1024 || Who When What
1025 || (reverse chronological order - newest change first)
1026 */
1027
1028 -- Get Person Id
1029 CURSOR c_get_person_id (cp_base_id igf_ap_fa_base_rec_all.base_id%TYPE)
1030 IS
1031 SELECT person_id
1032 FROM igf_ap_fa_base_rec_all
1033 WHERE base_id = cp_base_id;
1034
1035 l_person_id igf_sl_cl_pref_lenders.person_id%TYPE;
1036
1037 -- Gets the Prferred lender details if it exists
1038 CURSOR c_chk_pref_lender (cp_person_id igf_sl_cl_pref_lenders.person_id%TYPE)
1039 IS
1040 SELECT
1041 ROWID row_id,
1042 pref_lender.clprl_id,
1043 pref_lender.relationship_cd,
1044 pref_lender.start_date,
1045 pref_lender.end_date,
1046 pref_lender.person_id
1047 FROM
1048 igf_sl_cl_pref_lenders pref_lender
1049 WHERE
1050 pref_lender.person_id = cp_person_id
1051 AND pref_lender.end_date IS NULL;
1052
1053 l_chk_pref_lender c_chk_pref_lender%ROWTYPE;
1054 l_msg_count NUMBER := NULL;
1055 l_msg_number NUMBER := NULL;
1056 l_return_status VARCHAR2(50) := NULL;
1057 l_row_id VARCHAR2(80) := NULL;
1058
1059 BEGIN
1060
1061 -- Get Person Id
1062 OPEN c_get_person_id(p_base_id);
1063 FETCH c_get_person_id INTO l_person_id;
1064 IF (c_get_person_id%NOTFOUND) THEN
1065 p_clprl_id := NULL;
1066 CLOSE c_get_person_id;
1067 RETURN;
1068 END IF;
1069 CLOSE c_get_person_id;
1070
1071 -- Get Pref lender details
1072 OPEN c_chk_pref_lender(l_person_id);
1073 FETCH c_chk_pref_lender INTO l_chk_pref_lender;
1074 CLOSE c_chk_pref_lender;
1075
1076 IF l_chk_pref_lender.start_date IS NULL THEN
1077 -- No Active Pref lender exists. So insert lender
1078 igf_sl_cl_pref_lenders_pkg.insert_row (
1079 x_mode => 'R',
1080 x_rowid => l_row_id,
1081 x_clprl_id => p_clprl_id,
1082 x_msg_count => l_msg_count,
1083 x_msg_data => l_msg_number,
1084 x_return_status => l_return_status,
1085 x_person_id => l_person_id,
1086 x_start_date => TRUNC(SYSDATE),
1087 x_relationship_cd => p_rel_cd,
1088 x_end_date => NULL
1089 );
1090 ELSE
1091 -- Active lender exists
1092 IF l_chk_pref_lender.relationship_cd <> p_rel_cd THEN
1093 -- Existing active Pref lender is different from one being imported.
1094 -- Previous relationship record has to be end dated and
1095 -- a new record has to be added
1096 igf_sl_cl_pref_lenders_pkg.update_row(
1097 x_mode => 'R',
1098 x_rowid => l_chk_pref_lender.row_id,
1099 x_clprl_id => l_chk_pref_lender.clprl_id,
1100 x_msg_count => l_msg_count,
1101 x_msg_data => l_msg_number,
1102 x_return_status => l_return_status,
1103 x_person_id => l_chk_pref_lender.person_id,
1104 x_start_date => l_chk_pref_lender.start_date,
1105 x_relationship_cd => l_chk_pref_lender.relationship_cd,
1106 x_end_date => TRUNC(SYSDATE - 1)
1107 );
1108 igf_sl_cl_pref_lenders_pkg.insert_row (
1109 x_mode => 'R',
1110 x_rowid => l_row_id,
1111 x_clprl_id => p_clprl_id,
1112 x_msg_count => l_msg_count,
1113 x_msg_data => l_msg_number,
1114 x_return_status => l_return_status,
1115 x_person_id => l_person_id,
1116 x_start_date => TRUNC(SYSDATE),
1117 x_relationship_cd => p_rel_cd,
1118 x_end_date => NULL
1119 );
1120 ELSE
1121 -- Existing Pref lender is the same as one being imported.
1122 -- Just return the existing clprl_id
1123 p_clprl_id := l_chk_pref_lender.clprl_id;
1124 END IF;
1125 END IF;
1126 END process_pref_lender;
1127
1128 PROCEDURE add_log_table(
1129 p_person_number IN VARCHAR2,
1130 p_error IN VARCHAR2,
1131 p_message_str IN VARCHAR2
1132 ) IS
1133 /*
1134 || Created By : bkkumar
1135 || Created On : 26-MAY-2003
1136 || Purpose : This process adds a record to the global pl/sql table containing log messages
1137 || Known limitations, enhancements or remarks :
1138 || Change History :
1139 || Who When What
1140 || (reverse chronological order - newest change first)
1141 */
1142
1143 BEGIN
1144
1145 g_log_tab_index := g_log_tab_index + 1;
1146 g_log_tab(g_log_tab_index).person_number := p_person_number;
1147 g_log_tab(g_log_tab_index).message_text := RPAD(p_error,12) || p_message_str;
1148
1149 END add_log_table;
1150
1151 PROCEDURE print_log_process(
1152 p_alternate_code IN VARCHAR2,
1153 p_batch_id IN NUMBER,
1154 p_del_ind IN VARCHAR2
1155 ) IS
1156 /*
1157 || Created By : bkkumar
1158 || Created On : 26-MAY-2003
1159 || Purpose : This process gets the records from the pl/sql table and print in the log file
1160 || Known limitations, enhancements or remarks :
1161 || Change History :
1162 || Who When What
1163 || (reverse chronological order - newest change first)
1164 */
1165
1166 l_count NUMBER(5) := g_log_tab.COUNT;
1167 l_old_person igf_ap_li_todo_ints.person_number%TYPE := '*******';
1168
1169 l_person_number VARCHAR2(80);
1170 l_batch_id VARCHAR2(80);
1171 l_award_yr VARCHAR2(80);
1172 l_del_message VARCHAR2(200);
1173 l_batch_desc VARCHAR2(80);
1174 l_yes_no VARCHAR2(10);
1175
1176 CURSOR c_get_batch_desc(cp_batch_num NUMBER) IS
1177 SELECT batch_desc
1178 FROM igf_ap_li_bat_ints
1179 WHERE batch_num = cp_batch_num;
1180
1181 l_get_batch_desc c_get_batch_desc%ROWTYPE;
1182
1183 BEGIN
1184
1185 l_person_number := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','PERSON_NUMBER');
1186 l_batch_id := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','BATCH_ID');
1187 l_award_yr := igf_ap_gen.get_lookup_meaning('IGF_AW_LOOKUPS_MSG','AWARD_YEAR');
1188 l_yes_no := igf_ap_gen.get_lookup_meaning('IGF_AP_YES_NO',p_del_ind);
1189
1190 OPEN c_get_batch_desc(p_batch_id);
1191 FETCH c_get_batch_desc INTO l_get_batch_desc;
1192 CLOSE c_get_batch_desc;
1193 l_batch_desc := l_get_batch_desc.batch_desc ;
1194
1195 -- HERE THE INPUT PARAMETERS ARE TO BE LOGGED TO THE LOG FILE
1196 fnd_message.set_name('IGS','IGS_DA_JOB');
1197 fnd_file.put_line(fnd_file.log,fnd_message.get);
1198 fnd_file.put_line(fnd_file.log,RPAD(l_batch_id,50) || ' : ' || p_batch_id || ' - ' || l_batch_desc);
1199 fnd_file.put_line(fnd_file.log,RPAD(l_award_yr,50) || ' : ' || p_alternate_code);
1200 fnd_message.set_name('IGS','IGS_GE_ASK_DEL_REC');
1201 fnd_file.put_line(fnd_file.log,RPAD(fnd_message.get,50) || ' : ' || l_yes_no);
1202 fnd_file.put_line(fnd_file.log,'------------------------------------------------------------------------------');
1203
1204 FOR i IN 1..l_count LOOP
1205 IF g_log_tab(i).person_number IS NOT NULL THEN
1206 IF l_old_person <> g_log_tab(i).person_number THEN
1207 fnd_file.put_line(fnd_file.log,'---------------------------------------------------------------------------------');
1208 fnd_file.put_line(fnd_file.log,l_person_number || ' : ' || g_log_tab(i).person_number);
1209 END IF;
1210 l_old_person := g_log_tab(i).person_number;
1211 END IF;
1212 fnd_file.put_line(fnd_file.log,g_log_tab(i).message_text);
1213 END LOOP;
1214
1215 END print_log_process;
1216
1217 END igf_ap_lg_td_imp;