[Home] [Help]
PACKAGE BODY: APPS.IGF_SL_UPLOAD_XML
Source
1 PACKAGE BODY igf_sl_upload_xml AS
2 /* $Header: IGFSL26B.pls 120.13 2006/04/20 00:57:44 ugummall noship $ */
3
4 /*----------PROCESS FLOW--------------------------------------
5
6 main()
7 The main() process is called from the concurrent request (Upload XML File).
8 It will take the file path as parameter and it will raise the business event
9 which will then upload this file. It also passes the file path as ECX_PARAMETER1
10
11 upload_xml()
12 This process is called from the workflow to convert the file passed to a CLOB
13 parameter and will set the ECX_EVENT_MESSAGE attribute of the workflow.
14 It will also find the <DocumentID> and set it as the parameter ECX_PARAMETER2.
15 It will also insert the document in the igf_sl_cod_doc_dtls table.
16
17 set_nls_fmt()
18 This process is called from the IGF_SL_INBOUND.xgm for setting the NLS Format
19
20 get_datetime()
21 This process is called from the IGF_SL_INBOUND.xgm for converting the datetime
22 fields into Gateway compatible.
23
24 get_date()
25 This process is called from the IGF_SL_INBOUND.xgm for converting the date field
26 into XML Gateway compatible.
27
28 launch_request()
29 This process is called from the workflow and is teh last step of the workflow. It will
30 launch the sub-process which will upload launch a concurrent request which will process
31 the response records uploaded by XML Gateway in the previous step.
32
33 main_response()
34 This process is the sub-process (also a concurrent program) which will take the records in the
35 response tables and updates teh system tables accordingly. It takes the document ID as a
36 parameter. It will internally call process_pell_records and process_dl_records which process
37 the Pell and DL Records respectively.
38
39 -----------------------------------------------------------*/
40
41 CURSOR chk_doc ( cp_doc_id VARCHAR2) IS
42 SELECT ROWID row_id, a.*
43 FROM IGF_SL_COD_DOC_DTLS a
44 WHERE document_id_txt = cp_doc_id;
45
46 g_doc_id VARCHAR2(30);
47 g_process_date DATE;
48
49 PROCEDURE update_xml_document(l_chk_doc chk_doc%ROWTYPE, p_doc_status VARCHAR2)
50 IS
51 PRAGMA AUTONOMOUS_TRANSACTION;
52 BEGIN
53 igf_sl_cod_doc_dtls_pkg.update_row (
54 x_rowid => l_chk_doc.row_id,
55 x_document_id_txt => l_chk_doc.document_id_txt,
56 x_outbound_doc => l_chk_doc.outbound_doc ,
57 x_inbound_doc => l_chk_doc.inbound_doc ,
58 x_send_date => l_chk_doc.send_date ,
59 x_ack_date => l_chk_doc.ack_date ,
60 x_doc_status => p_doc_status ,
61 x_doc_type => l_chk_doc.doc_type ,
62 x_full_resp_code => l_chk_doc.full_resp_code ,
63 x_mode => 'R'
64 );
65 COMMIT;
66 END update_xml_document;
67
68 PROCEDURE delete_temp_table_data
69 IS
70 PRAGMA AUTONOMOUS_TRANSACTION;
71 BEGIN
72 DELETE IGF_SL_COD_TEMP;
73 COMMIT;
74 null;
75 END delete_temp_table_data;
76
77 PROCEDURE rollback_resp_tables
78 IS
79 PRAGMA AUTONOMOUS_TRANSACTION;
80
81 CURSOR get_cod_temp IS
82 SELECT *
83 FROM IGF_SL_COD_TEMP;
84 l_temp get_cod_temp%ROWTYPE;
85 BEGIN
86 -- Remove data from resp tables because of any of the following reasons
87 -- 1. Destination entity id is not correct.
88 -- 2. It is a receipt document
89 -- 3. Invalid Full_Resp_code value
90 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
91 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.rollback_resp_tables.debug','Removing data from resp tables');
92 END IF;
93
94 FOR l_temp IN get_cod_temp
95 LOOP
96 IF l_temp.LEVEL_CODE = 'CR' THEN
97 DELETE IGF_SL_CR_RESP_DTLS WHERE DOCUMENT_ID_TXT = l_temp.REC_ID;
98 ELSIF l_temp.LEVEL_CODE = 'RS' THEN
99 DELETE IGF_SL_RS_RESP_DTLS WHERE REP_SCHL_RESP_ID = l_temp.REC_ID;
100 ELSIF l_temp.LEVEL_CODE = 'AS' THEN
101 DELETE IGF_SL_AS_RESP_DTLS WHERE ATD_SCHL_RESP_ID = l_temp.REC_ID;
102 ELSIF l_temp.LEVEL_CODE = 'ST' THEN
103 DELETE IGF_SL_ST_RESP_DTLS WHERE STDNT_RESP_ID = l_temp.REC_ID;
104 ELSIF l_temp.LEVEL_CODE = 'AWD' THEN
105 DELETE IGF_SL_DL_RESP_DTLS WHERE DL_LOAN_RESP_ID = l_temp.REC_ID;
106 ELSIF l_temp.LEVEL_CODE = 'DL_DB' THEN
107 DELETE IGF_SL_DLDB_RSP_DTL WHERE DISB_RESP_ID = l_temp.REC_ID;
108 ELSIF l_temp.LEVEL_CODE = 'PELL' THEN
109 DELETE IGF_GR_RESP_DTLS WHERE PELL_RESP_ID = l_temp.REC_ID;
110 ELSIF l_temp.LEVEL_CODE = 'PELL_DB' THEN
111 DELETE IGF_GR_DB_RESP_DTLS WHERE DISB_RESP_ID = l_temp.REC_ID;
112 ELSIF l_temp.LEVEL_CODE = 'DL_INFO' THEN
113 DELETE IGF_SL_DI_RESP_DTLS WHERE DL_INFO_ID = l_temp.REC_ID;
114 END IF;
115 END LOOP;
116
117 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
118 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.rollback_resp_tables.debug','Removing data from IGF_SL_COD_TEMP');
119 END IF;
120 DELETE IGF_SL_COD_TEMP;
121
122 COMMIT;
123 END rollback_resp_tables;
124
125 FUNCTION check_entityid(
126 p_entity_id VARCHAR2
127 ) RETURN BOOLEAN
128 AS
129 -----------------------------------------------------------------------------------
130 --
131 -- Created By : ugummall
132 -- Date Created On : 2004/09/21
133 -- Purpose : Check if a particular entity ID is configured as a SWS Entity ID
134 -- into the OSS System
135 -- Know limitations, enhancements or remarks
136 -- Change History:
137 -----------------------------------------------------------------------------------
138 -- Who When What
139 -----------------------------------------------------------------------------------
140 CURSOR chk_id (cp_entity_id VARCHAR2) IS
141 SELECT REP.ORG_ALTERNATE_ID ENTITY_ID
142 FROM IGS_OR_ORG_ALT_IDS REP,
143 IGS_OR_ORG_ALT_IDTYP_V REPID
144 WHERE REP.ORG_ALTERNATE_ID_TYPE = REPID.ORG_ALTERNATE_ID_TYPE
145 AND REPID.SYSTEM_ID_TYPE = 'ENTITY_ID'
146 AND SYSDATE BETWEEN REP.START_DATE AND NVL(REP.END_DATE, SYSDATE)
147 AND REP.ORG_ALTERNATE_ID = cp_entity_id;
148 l_entity_id IGF_GR_REPORT_PELL.REP_ENTITY_ID_TXT%TYPE;
149
150 BEGIN
151 l_entity_id := NULL;
152 OPEN chk_id(p_entity_id);
153 FETCH chk_id INTO l_entity_id;
154 CLOSE chk_id;
155
156 IF l_entity_id IS NULL THEN
157 RETURN FALSE;
158 ELSE
159 RETURN TRUE;
160 END IF;
161
162 EXCEPTION
163 WHEN others THEN
164 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
165 fnd_message.set_token('NAME','igf_sl_upload_xml.check_entityid');
166 igs_ge_msg_stack.add;
167 app_exception.raise_exception;
168 END check_entityid;
169 /* -----------------------------------------------------------------------------------
170 Know limitations, enhancements or remarks
171 Change History:
172 -----------------------------------------------------------------------------------
173 Who When What
174 tsailaja 15/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
175 -----------------------------------------------------------------------------------
176 */
177 PROCEDURE main ( errbuf OUT NOCOPY VARCHAR2,
178 retcode OUT NOCOPY NUMBER,
179 p_file_path IN VARCHAR2
180 )
181 AS
182 CURSOR get_doc ( cp_doc_id VARCHAR2) IS
183 SELECT ROWID ROW_ID, docdtls.*
184 FROM IGF_SL_COD_DOC_DTLS docdtls
185 WHERE DOCUMENT_ID_TXT = cp_doc_id;
186 l_get_doc get_doc%ROWTYPE;
187
188 CURSOR get_event_key IS
189 SELECT IGF_SL_LOAD_XML_S.NEXTVAL
190 FROM DUAL;
191
192 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
193
194 l_event_name VARCHAR2(255);
195 l_event_key VARCHAR2(240);
196 l_map_code VARCHAR2(255);
197
198 l_temp VARCHAR2(30);
199 l_sql_stmt VARCHAR2(200);
200 l_start_pos NUMBER;
201 l_file_path VARCHAR2(1000);
202 srcFile BFILE ;
203 intLen INT;
204 tmpClob1 CLOB;
205 tmpClob CLOB;
206 MYCLOB_TEXT VARCHAR2(11000);
207 l_doc_id VARCHAR2(30);
208 lv_file UTL_FILE.FILE_TYPE;
209 ln_start_pos INTEGER;
210 ln_end_pos INTEGER;
211 l_endofdir NUMBER;
212 l_temp_endofdir NUMBER;
213 lv_directory VARCHAR2(300);
214 lv_filename VARCHAR2(300);
215 ln_file_line_num NUMBER;
216 BEGIN
217 igf_aw_gen.set_org_id(NULL);
218 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
219 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main.debug','main started');
220 END IF;
221
222 -- Step 1. Print the log parameters
223 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
224 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main.debug','file path is: ' || p_file_path);
225 END IF;
226 fnd_file.put_line(fnd_file.log,'File Path : '||p_file_path);
227
228 -- Step 2. Get the XML file from the file path and store it in COD_DOC_DTLS table.
229 -- Seperate directory and filename
230 l_file_path := p_file_path;
231 l_endofdir := 0;
232 l_temp_endofdir := 0;
233 LOOP
234 l_temp_endofdir := INSTR(l_file_path, '/', l_temp_endofdir+1, 1);
235 IF l_temp_endofdir = 0 THEN
236 EXIT;
237 END IF;
238 l_endofdir := l_temp_endofdir;
239 END LOOP;
240
241 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
242 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main.debug','Final value of l_endofdire is: ' || l_endofdir);
243 END IF;
244
245 lv_directory := SUBSTR(l_file_path, 1, l_endofdir-1);
246 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
247 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main.debug','lv_directory: ' || lv_directory);
248 END IF;
249 lv_filename := SUBSTR(l_file_path, l_endofdir+1);
250 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
251 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main.debug','lv_filename: ' || lv_filename);
252 END IF;
253
254 -- Open the file
255 BEGIN
256 lv_file := utl_file.fopen(lv_directory, lv_filename, 'r', 32767);
257 EXCEPTION
258 WHEN UTL_FILE.INVALID_PATH THEN
259 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
260 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main.debug','UTL_FILE.INVALID_PATH Exception occurred');
261 END IF;
262 RAISE;
263 RETURN;
264 WHEN UTL_FILE.INVALID_MODE THEN
265 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
266 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main.debug','UTL_FILE.INVALID_MODE Exception occurred');
267 END IF;
268 RAISE;
269 RETURN;
270 WHEN UTL_FILE.INVALID_OPERATION THEN
271 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
272 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main.debug','UTL_FILE.INVALID_OPERATION Exception occurred');
273 END IF;
274 RAISE;
275 RETURN;
276 END;
277
278 -- Here tmpClob is the final CLOB output, not tmpClob1
279 -- Open tmpClob1.
280 tmpClob1 := EMPTY_CLOB;
281 DBMS_LOB.CREATETEMPORARY(tmpClob1,TRUE,DBMS_LOB.SESSION);
282 DBMS_LOB.OPEN(tmpClob1,DBMS_LOB.LOB_READWRITE);
283 IF DBMS_LOB.ISOPEN(tmpClob1) = 1 THEN
284 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
285 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main.debug','tmpClob1 IS opened successfully');
286 END IF;
287 ELSE
288 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
289 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main.debug','tmpClob1 is NOT opened successfully');
290 END IF;
291 RETURN;
292 END IF;
293
294 -- Create tmpClob1 from the file.
295 ln_file_line_num := 0;
296 BEGIN
297 LOOP
298 -- When EOF reaches, GET_LINE raises NO DATA FOUND Exception.
299 ln_file_line_num := ln_file_line_num + 1;
300 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
301 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main.debug', 'Before reading line number: ' || ln_file_line_num);
302 END IF;
303
304 UTL_FILE.GET_LINE(lv_file, myclob_text);
305
306 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
307 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main.debug', 'After reading line number: ' || ln_file_line_num || 'Length of the text is: ' || LENGTH(myclob_text) || 'Start of the text is: ' || SUBSTR(myclob_text, 1, 32));
308 END IF;
309
310 IF myclob_text IS NOT NULL AND LENGTH(myclob_text) <> 0 THEN
311 DBMS_LOB.WRITEAPPEND(tmpClob1, LENGTH(myclob_text), myclob_text);
312 END IF;
313
314 END LOOP;
315 EXCEPTION
316 WHEN NO_DATA_FOUND THEN
317 -- This will Auto-Close the file when EOF Reaches
318 UTL_FILE.FCLOSE(lv_file);
319 WHEN OTHERS THEN
320 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
321 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main.debug','Exception occurred while creating tmpClob1');
322 END IF;
323 RAISE;
324 RETURN;
325 END;
326
327 -- Open tmpClob
328 tmpClob := EMPTY_CLOB;
329 DBMS_LOB.CREATETEMPORARY(tmpClob,TRUE,DBMS_LOB.SESSION);
330 DBMS_LOB.OPEN(tmpClob,DBMS_LOB.LOB_READWRITE);
331 IF DBMS_LOB.ISOPEN(tmpClob1) = 1 THEN
332 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
333 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main.debug','tmpClob IS opened successfully');
334 END IF;
335 ELSE
336 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
337 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main.debug','tmpClob is NOT opened successfully');
338 END IF;
339 RETURN;
340 END IF;
341
342 -- Copy from tmpClob1 to tmpClob
343 ln_start_pos := DBMS_LOB.INSTR(tmpClob1,'<CommonRecord',1,1);
344 ln_end_pos := DBMS_LOB.INSTR(tmpClob1,'</CommonRecord>',1,1);
345 ln_end_pos := ln_end_pos + LENGTH('</CommonRecord>');
346 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
347 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main.debug', 'Start of <CommonRecord ie ln_start_pos is: ' || ln_start_pos || 'End of </CommonRecord> ie ln_end_pos is: ' || ln_end_pos);
348 END IF;
349 DBMS_LOB.COPY(tmpClob, tmpClob1, ln_end_pos-ln_start_pos, 1, ln_start_pos);
350
351 -- Try to find out the document id from the file
352 MYCLOB_TEXT := TRIM(DBMS_LOB.SUBSTR(tmpClob,10000,1));
353 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
354 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main.debug','MYCLOB_TEXT is read successfully: '||SUBSTR(MYCLOB_TEXT, 1, 1000));
355 END IF;
356
357 l_start_pos := INSTR(MYCLOB_TEXT,'<DocumentID>',1,1);
358 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
359 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main.debug','l_start_pos is: ' || l_start_pos);
360 END IF;
361
362 IF l_start_pos = 0 THEN
363 fnd_message.set_name('IGF','IGF_SL_COD_IB_NO_XML_DOC_ID');
364 fnd_file.put_line(fnd_file.log, fnd_message.get);
365 RETURN;
366 ELSE
367 l_doc_id := SUBSTR(MYCLOB_TEXT,(l_start_pos+12),30);
368 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
369 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main.debug','Document ID read from XML is: ' || l_doc_id);
370 END IF;
371 END IF;
372
373 -- Insert/Update the IGF_SL_COD_DOC_DTLS TABLE
374 l_get_doc := NULL;
375 OPEN get_doc(l_doc_id);
376 FETCH get_doc INTO l_get_doc;
377 CLOSE get_doc;
378
379 IF l_get_doc.ROW_ID IS NULL THEN
380 l_get_doc.doc_type := 'COD';
381 END IF;
382 igf_sl_cod_doc_dtls_pkg.add_row (
383 x_mode => 'R',
384 x_rowid => l_get_doc.ROW_ID,
385 x_document_id_txt => l_doc_id,
386 x_outbound_doc => l_get_doc.outbound_doc,
387 x_inbound_doc => tmpClob,
388 x_send_date => l_get_doc.send_date,
389 x_ack_date => SYSDATE,
390 x_doc_status => 'R',
391 x_doc_type => l_get_doc.doc_type,
392 x_full_resp_code => l_get_doc.full_resp_code
393 );
394 COMMIT;
395
396 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
397 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main.debug','Document successfully loaded in table');
398 END IF;
399
400 -- Step 3. Raise the business event.
401 l_event_name := 'oracle.apps.igf.sl.loadxml';
402 l_map_code := 'IGF_SL_INBOUND';
403 l_event_key := NULL;
404 OPEN get_event_key;
405 FETCH get_event_key INTO l_event_key;
406 CLOSE get_event_key;
407
408 -- Now add the parameters to the list to be passed to the workflow
409 wf_event.addparametertolist(
410 p_name => 'EVENT_NAME',
411 p_value => l_event_name,
412 p_parameterlist => l_parameter_list
413 );
414 wf_event.addparametertolist(
415 p_name => 'EVENT_KEY',
416 p_value => l_event_key,
417 p_parameterlist => l_parameter_list
418 );
419 wf_event.addparametertolist(
420 p_name => 'ECX_MAP_CODE',
421 p_value => l_map_code,
422 p_parameterlist => l_parameter_list
423 );
424 wf_event.addparametertolist(
425 p_name => 'ECX_PARAMETER1',
426 p_value => l_doc_id,
427 p_parameterlist => l_parameter_list
428 );
429
430 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
431 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main.debug','Raising business event with key: ' || l_event_key);
432 END IF;
433 -- raise the business event
434 wf_event.RAISE (
435 p_event_name => l_event_name,
436 p_event_key => l_event_key,
437 p_parameters => l_parameter_list
438 );
439
440 fnd_message.set_name('IGF','IGF_SL_COD_INBOUND_EVENT');
441 fnd_message.set_token('EVENT_KEY_VALUE',l_event_key);
442 fnd_file.put_line(fnd_file.log,fnd_message.get);
443
444 EXCEPTION
445 WHEN OTHERS THEN
446 ROLLBACK;
447 retcode := 2;
448 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
449 fnd_file.put_line(fnd_file.log, SQLERRM);
450 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
451 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main.debug','sqlerrm ' || SQLERRM);
452 END IF;
453 igs_ge_msg_stack.conc_exception_hndl;
454 END main;
455
456 PROCEDURE upload_xml ( itemtype IN VARCHAR2,
457 itemkey IN VARCHAR2,
458 actid IN NUMBER,
459 funcmode IN VARCHAR2,
460 resultout OUT NOCOPY VARCHAR2
461 )
462 AS
463 l_doc_id VARCHAR2(30);
464 l_wf_event wf_event_t;
465 newxmldoc CLOB;
466 buffer VARCHAR2(32767);
467
468 BEGIN
469 -- This is called by workflow. Read XML File from the table, then add <CR> Tag
470 -- and initialize the workflow event data with two this new xml as parameter.
471
472 DECLARE
473 xmldoc CLOB;
474 buffer VARCHAR2(32767);
475 amount BINARY_INTEGER;
476 l_start_pos INTEGER;
477 new_l_start_pos INTEGER;
478 new_end_pos INTEGER;
479 new_xml_len INTEGER;
480 flag INTEGER;
481 nth_occur INTEGER;
482 new_l_start_pos_buffer INTEGER;
483 end_tag INTEGER;
484
485 BEGIN
486 l_doc_id := wf_engine.getitemattrtext ( itemtype, itemkey, 'ECX_PARAMETER1');
487 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
488 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.upload_xml.debug','Document Id from the ECX_PARAMETER1' || l_doc_id);
489 END IF;
490
491 SELECT INBOUND_DOC INTO xmldoc FROM igf_sl_cod_doc_dtls WHERE DOCUMENT_ID_TXT = l_doc_id;
492 SELECT INBOUND_DOC INTO newxmldoc FROM igf_sl_cod_doc_dtls WHERE DOCUMENT_ID_TXT = l_doc_id FOR UPDATE;
493
494 new_xml_len := DBMS_LOB.GETLENGTH(newxmldoc);
495 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
496 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.upload_xml.debug','Before erasing the newxmldoc. Its length new_xml_len is '||new_xml_len);
497 END IF;
498 DBMS_LOB.ERASE(newxmldoc, new_xml_len, 1);
499
500 buffer := '<CR>
501 <CommonRecord>';
502 amount := LENGTH(buffer);
503 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
504 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.upload_xml.debug','Writing root tags into newxmldoc');
505 END IF;
506 DBMS_LOB.WRITE(newxmldoc, amount, 1, buffer);
507 --akomurav one off 4873868
508 -- Remove the xsi:nil tags from the inbound file
509 new_l_start_pos := DBMS_LOB.INSTR(xmldoc,'<DocumentID>',1,1);
510 new_l_start_pos_buffer :=new_xml_len;
511 flag := 1;
512 nth_occur := 1;
513 while (flag =1) loop
514 new_end_pos := DBMS_LOB.INSTR(xmldoc,'xsi:nil',1,nth_occur);
515 if new_end_pos > 0 then
516 DBMS_LOB.COPY(newxmldoc,xmldoc,new_end_pos-new_l_start_pos,amount+1,new_l_start_pos);
517 nth_occur := nth_occur + 1;
518 amount := amount+(new_end_pos-new_l_start_pos);
519 end_tag := DBMS_LOB.INSTR(xmldoc,'>',new_end_pos+1,1);
520 new_l_start_pos := end_tag;
521 end if;
522 if new_end_pos = 0 then
523 flag:=0;
524 end if;
525 end loop;
526
527 DBMS_LOB.COPY(newxmldoc,xmldoc,new_l_start_pos_buffer-new_l_start_pos+1,amount+1,new_l_start_pos);
528 buffer := '</CR>';
529 amount := LENGTH(buffer);
530 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
531 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.upload_xml.debug','Appending </CR> tag');
532 END IF;
533 DBMS_LOB.WRITEAPPEND(newxmldoc, amount, buffer);
534 END;
535
536 -- set the workflow attributes
537 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
538 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.upload_xml.debug','Before setting workflow attributes');
539 END IF;
540 wf_event_t.Initialize(l_wf_event);
541 l_wf_event.setEventData(newxmldoc);
542 wf_engine.SetItemAttrEvent(itemtype,itemkey,'ECX_EVENT_MESSAGE',l_wf_event);
543 resultout := 'P';
544
545 EXCEPTION
546 WHEN OTHERS THEN
547 resultout := 'F';
548 wf_core.context ('IGF_SL_UPLOAD_XML',
549 'UPLOAD_XML', itemtype,
550 itemkey,to_char(actid), funcmode);
551 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
552 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.upload_xml.debug','sqlerrm ' || SQLERRM);
553 END IF;
554 END upload_xml;
555
556 PROCEDURE print_edits ( p_id IN VARCHAR2,
557 p_level IN VARCHAR2
558 )
559 AS
560
561 CURSOR get_records ( cp_id VARCHAR2,
562 cp_level VARCHAR2) IS
563 SELECT *
564 FROM IGF_SL_REJ_EDIT_V
565 WHERE EDIT_ID = cp_id
566 AND LEVEL_CODE = cp_level;
567 rec_get_records get_records%ROWTYPE;
568 lv_lookup_code VARCHAR2(30);
569 BEGIN
570 -- Print the level for which the edit results are pritned.
571 OPEN get_records(p_id, p_level);
572 FETCH get_records INTO rec_get_records;
573 IF get_records%FOUND THEN
574 lv_lookup_code := p_level || '_EDIT_RSLTS';
575 fnd_file.put_line(fnd_file.log, igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS',lv_lookup_code));
576 END IF;
577 CLOSE get_records;
578
579 FOR l_record IN get_records(p_id, p_level)
580 LOOP
581 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','EDIT_CD')|| ':'||l_record.EDIT_CODE);
582 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','EDIT_CD_TYP')|| ':'||l_record.EDIT_CODE_TYPE);
583 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','ERR_FLD')|| ':'||l_record.RESP_ERR_FIELD);
584 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','ERR_VAL')|| ':'||l_record.RESP_ERR_VALUE);
585 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','REPT_VAL')|| ':'||l_record.REPORTED_VALUE);
586 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','EDIT_MSG')|| ':'||l_record.EDIT_MESSAGE_TXT);
587 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','EDIT_COND')|| ':'||l_record.EDIT_CONDITION_TXT);
588 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','EDIT_FIX')|| ':'||l_record.EDIT_FIX_TXT);
589 END LOOP;
590
591 EXCEPTION
592 WHEN OTHERS THEN
593 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
594 fnd_message.set_token('NAME','igf_sl_upload_xml.print_edits');
595 igs_ge_msg_stack.add;
596 app_exception.raise_exception;
597 END print_edits;
598
599 PROCEDURE process_dl_records ( p_st_id IN NUMBER,
600 p_rej_flg IN BOOLEAN,
601 p_type IN VARCHAR2
602 )
603 AS
604
605 CURSOR get_dl_resp ( cp_st_id NUMBER) IS
606 SELECT *
607 FROM IGF_SL_DL_RESP_DTLS
608 WHERE STDNT_RESP_ID = cp_st_id
609 AND NVL(STATUS_CODE, '*') <> 'P';
610
611 CURSOR get_dl_db_resp ( cp_dl_id NUMBER) IS
612 SELECT *
613 FROM IGF_SL_DLDB_RSP_DTL
614 WHERE DL_LOAN_RESP_ID = cp_dl_id
615 AND NVL(STATUS_CODE, '*') <> 'P';
616
617 CURSOR get_dl_rec ( cp_dl_num VARCHAR2,
618 cp_fin_awd_yr VARCHAR2
619 ) IS
620 SELECT *
621 FROM IGF_SL_LOR_LOC_ALL
622 WHERE LOAN_NUMBER = cp_dl_num
623 AND FIN_AWARD_YEAR = cp_fin_awd_yr;
624 l_dl_rec get_dl_rec%ROWTYPE;
625
626 CURSOR get_dl_db_rec ( cp_awd_id NUMBER,
627 cp_disb_seq_num NUMBER,
628 cp_disb_num NUMBER
629 ) IS
630 SELECT *
631 FROM IGF_AW_DB_CHG_DTLS
632 WHERE AWARD_ID = cp_awd_id
633 AND DISB_NUM = cp_disb_num
634 AND DISB_SEQ_NUM = cp_disb_seq_num;
635 l_dl_db_rec get_dl_db_rec%ROWTYPE;
636
637 -- Curosrs for the loan records updation
638 CURSOR c_tbh_cur ( cp_loan_id NUMBER) IS
639 SELECT igf_sl_lor.*
640 FROM IGF_SL_LOR
641 WHERE loan_id = cp_loan_id
642 FOR UPDATE NOWAIT;
643 l_tbh_cur c_tbh_cur%ROWTYPE;
644
645 CURSOR c_tbh_cur1 ( cp_loan_id NUMBER) IS
646 SELECT igf_sl_lor_loc.*
647 FROM IGF_SL_LOR_LOC
648 WHERE loan_id = cp_loan_id
649 FOR UPDATE NOWAIT;
650 l_tbh_cur1 c_tbh_cur1%ROWTYPE;
651
652 CURSOR c_tbh_cur2 ( cp_loan_id NUMBER) IS
653 SELECT igf_sl_loans.*
654 FROM IGF_SL_LOANS
655 WHERE loan_id = cp_loan_id
656 FOR UPDATE NOWAIT;
657 l_tbh_cur2 c_tbh_cur2%ROWTYPE;
658
659 -- Cursors from the disbursements updation
660 CURSOR c_tbh_disb ( cp_awd_id NUMBER,
661 cp_disb_seq_num NUMBER,
662 cp_disb_num NUMBER
663 ) IS
664 SELECT *
665 FROM IGF_AW_DB_CHG_DTLS_V
666 WHERE AWARD_ID = cp_awd_id
667 AND DISB_NUM = cp_disb_num
668 AND DISB_SEQ_NUM = cp_disb_seq_num;
669 l_tbh_disb c_tbh_disb%ROWTYPE;
670
671 CURSOR c_tbh_disb1 ( cp_awd_id NUMBER,
672 cp_disb_seq_num NUMBER,
673 cp_disb_num NUMBER
674 ) IS
675 SELECT *
676 FROM IGF_AW_DB_COD_DTLS_V
677 WHERE AWARD_ID = cp_awd_id
678 AND DISB_NUM = cp_disb_num
679 AND DISB_SEQ_NUM = cp_disb_seq_num;
680 l_tbh_disb1 c_tbh_disb1%ROWTYPE;
681 update_flag BOOLEAN;
682
683
684
685 BEGIN
686
687 FOR l_dl_resp IN get_dl_resp(p_st_id)
688 LOOP
689 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','PROC_DL_NUM')||':' ||l_dl_resp.LOAN_NUMBER_TXT);
690 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_ST')||':' ||l_dl_resp.RESP_CODE );
691
692 l_dl_rec := NULL;
693 OPEN get_dl_rec(l_dl_resp.LOAN_NUMBER_TXT,l_dl_resp.FIN_AWD_YR);
694 FETCH get_dl_rec INTO l_dl_rec;
695 CLOSE get_dl_rec;
696
697 IF l_dl_rec.loan_id IS NULL THEN
698 fnd_message.set_name('IGF','IGF_SL_COD_SKIP');
699 fnd_file.put_line(fnd_file.log,fnd_message.get);
700
701 -- update the status to 'N' - Processed, Not found in the System
702 UPDATE IGF_SL_DL_RESP_DTLS
703 SET STATUS_CODE = 'N'
704 WHERE DL_LOAN_RESP_ID = l_dl_resp.DL_LOAN_RESP_ID;
705 ELSE
706
707
708 l_tbh_cur := NULL;
709 l_tbh_cur1 := NULL;
710 l_tbh_cur2 := NULL;
711
712 -- igf_sl_lor_all
713 OPEN c_tbh_cur(l_dl_rec.loan_id);
714 FETCH c_tbh_cur INTO l_tbh_cur;
715 CLOSE c_tbh_cur;
716
717 -- igf_sl_lor_loc_all
718 OPEN c_tbh_cur1(l_dl_rec.loan_id);
719 FETCH c_tbh_cur1 INTO l_tbh_cur1;
720 CLOSE c_tbh_cur1;
721
722 -- igf_sl_loans
723 OPEN c_tbh_cur2(l_dl_rec.loan_id);
724 FETCH c_tbh_cur2 INTO l_tbh_cur2;
725 CLOSE c_tbh_cur2;
726
727 print_edits(l_dl_resp.DL_LOAN_RESP_ID,'DL');
728
729 IF p_rej_flg = TRUE OR l_dl_resp.RESP_CODE = 'R' THEN
730 -- update the IGF_SL_LOR_ALL with ORIG_STATUS_FLAG = 'R'
731 l_tbh_cur.ORIG_STATUS_FLAG := 'R';
732
733 -- update the IGF_SL_LOR_LOC_ALL with ORIG_STATUS_FLAG = 'R'
734 l_tbh_cur1.ORIG_STATUS_FLAG := 'R';
735 l_tbh_cur1.document_id_txt := g_doc_id;
736
737 -- update IGF_SL_LOANS_ALL with LOAN_STATUS or LOAN_CHG_STATUS = 'R'
738 IF l_tbh_cur2.LOAN_STATUS = 'S' THEN
739 l_tbh_cur2.LOAN_STATUS := 'R';
740 l_tbh_cur2.LOAN_STATUS_DATE := SYSDATE;
741 ELSIF l_tbh_cur2.LOAN_CHG_STATUS = 'S' THEN -- change down
742 l_tbh_cur2.LOAN_CHG_STATUS := 'R';
743 l_tbh_cur2.LOAN_CHG_STATUS_DATE := SYSDATE;
744 END IF;
745 ELSIF l_dl_resp.RESP_CODE ='A' THEN
746 l_tbh_cur.ORIG_STATUS_FLAG := l_dl_resp.RESP_CODE;
747 l_tbh_cur1.ORIG_STATUS_FLAG := l_dl_resp.RESP_CODE;
748 l_tbh_cur1.document_id_txt := g_doc_id;
749
750 IF l_tbh_cur2.LOAN_STATUS = 'S' THEN
751 l_tbh_cur2.LOAN_STATUS := l_dl_resp.RESP_CODE;
752 ELSIF l_tbh_cur2.LOAN_CHG_STATUS = 'S' THEN
753 l_tbh_cur2.LOAN_CHG_STATUS := l_dl_resp.RESP_CODE;
754 END IF;
755
756 -- compare the values in the resp record with the system record
757 IF l_dl_resp.AWARD_AMT <> l_dl_rec.LOAN_AMT_ACCEPTED THEN
758 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','SYS_VAL_DL_AMT')||':' ||l_dl_rec.LOAN_AMT_ACCEPTED );
759 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_VAL_DL_AMT')||':' ||l_dl_resp.AWARD_AMT );
760 END IF;
761
762 -- 4582675. If the response document is of type RS, then update all the fields
763 -- that would get updated if it had been CO, PN, PS or BN.
764
765 IF p_type = 'CO' OR p_type = 'RS' THEN
766
767 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','END_AMT')||':' ||l_dl_resp.ENDORSER_AMT);
768 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','CRDT_DEC_ST')||':' ||l_dl_resp.CRDT_DECISION_STATUS);
769 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','CRDT_DEC_DT')||':' ||l_dl_resp.CRDT_DECISION_DATE);
770 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','CRDT_DEC_OVD')||':' ||l_dl_resp.CRDT_DECISION_OVRD_CODE);
771
772 -- update these 4 cols in the tables IGF_SL_LOR_ALL, IGF_SL_LOR_LOC_ALL
773 l_tbh_cur.CREDIT_OVERRIDE := NVL(l_dl_resp.CRDT_DECISION_OVRD_CODE, l_tbh_cur.CREDIT_OVERRIDE);
774 l_tbh_cur.CREDIT_DECISION_DATE := NVL(l_dl_resp.CRDT_DECISION_DATE, l_tbh_cur.CREDIT_DECISION_DATE);
775 l_tbh_cur.crdt_decision_status := NVL(l_dl_resp.CRDT_DECISION_STATUS, l_tbh_cur.crdt_decision_status);
776 l_tbh_cur.PNOTE_ACCEPT_AMT := NVL(l_dl_resp.ENDORSER_AMT, l_tbh_cur.PNOTE_ACCEPT_AMT);
777 l_tbh_cur.PNOTE_BATCH_ID := g_doc_id;
778
779 l_tbh_cur1.CREDIT_OVERRIDE := NVL(l_dl_resp.CRDT_DECISION_OVRD_CODE, l_tbh_cur1.CREDIT_OVERRIDE);
780 l_tbh_cur1.CREDIT_DECISION_DATE := NVL(l_dl_resp.CRDT_DECISION_DATE, l_tbh_cur1.CREDIT_DECISION_DATE);
781 l_tbh_cur1.crdt_decision_status := NVL(l_dl_resp.CRDT_DECISION_STATUS, l_tbh_cur1.crdt_decision_status);
782 l_tbh_cur1.PNOTE_ACCEPT_AMT := NVL(l_dl_resp.ENDORSER_AMT, l_tbh_cur1.PNOTE_ACCEPT_AMT);
783 l_tbh_cur1.DOCUMENT_ID_TXT := g_doc_id;
784
785 END IF;
786
787 IF p_type = 'PN' OR p_type = 'RS' OR p_type = 'CO' THEN
788
789 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','ELEC_MPN_FLAG')||':' ||l_dl_resp.ELEC_MPN_FLAG);
790 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','PNOTE_MPN_ID')||':' ||l_dl_resp.PNOTE_MPN_ID);
791 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','MPN_STATUS_CODE')||':' ||l_dl_resp.MPN_STATUS_CODE);
792 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','MPN_LINK_FLAG')||':' ||l_dl_resp.MPN_LINK_FLAG);
793
794 -- update these 4 cols in the tables IGF_SL_LOR_ALL, IGF_SL_LOR_LOC_ALL
795 l_tbh_cur.PNOTE_ID := NVL(l_dl_resp.PNOTE_MPN_ID, l_tbh_cur.PNOTE_ID);
796 l_tbh_cur.PNOTE_STATUS := NVL(l_dl_resp.MPN_STATUS_CODE, l_tbh_cur.PNOTE_STATUS);
797 l_tbh_cur.PNOTE_ACK_DATE := NVL(g_process_date, l_tbh_cur.PNOTE_ACK_DATE);
798 l_tbh_cur.PNOTE_STATUS_DATE := SYSDATE;
799
800 IF (l_dl_resp.ELEC_MPN_FLAG IS NOT NULL) THEN
801 IF (l_dl_resp.ELEC_MPN_FLAG = 'true') THEN
802 l_tbh_cur.ELEC_MPN_IND := 'E';
803 ELSIF (l_dl_resp.ELEC_MPN_FLAG = 'false') THEN
804 l_tbh_cur.ELEC_MPN_IND := 'P';
805 END IF;
806 END IF;
807
808 IF (l_dl_resp.MPN_LINK_FLAG IS NOT NULL) THEN
809 IF (l_dl_resp.MPN_LINK_FLAG = 'true') THEN
810 l_tbh_cur.PNOTE_MPN_IND := 'Y';
811 ELSIF (l_dl_resp.MPN_LINK_FLAG = 'false') THEN
812 l_tbh_cur.PNOTE_MPN_IND := 'N';
813 END IF;
814 END IF;
815 l_tbh_cur.PNOTE_BATCH_ID := g_doc_id;
816
817 l_tbh_cur1.PNOTE_ID := NVL(l_dl_resp.PNOTE_MPN_ID, l_tbh_cur1.PNOTE_ID);
818 l_tbh_cur1.PNOTE_STATUS := NVL(l_dl_resp.MPN_STATUS_CODE, l_tbh_cur1.PNOTE_STATUS);
819 l_tbh_cur1.PNOTE_PRINT_IND := NVL(l_tbh_cur.ELEC_MPN_IND, l_tbh_cur1.PNOTE_PRINT_IND);
820 l_tbh_cur1.PNOTE_MPN_IND := NVL(l_tbh_cur.PNOTE_MPN_IND, l_tbh_cur1.PNOTE_MPN_IND);
821 l_tbh_cur1.DOCUMENT_ID_TXT := g_doc_id;
822
823 END IF;
824
825 IF p_type = 'PS' OR p_type = 'RS' THEN
826
827 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','PYMT_SERVICER_AMT')||':' ||l_dl_resp.PYMT_SERVICER_AMT);
828 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','PYMT_SERVICER_DATE')||':' ||l_dl_resp.PYMT_SERVICER_DATE);
829
830 -- update these 2 cols in the tables IGF_SL_LOR_ALL, IGF_SL_LOR_LOC_ALL
831 -- IN LOR_ALL OLD+NEW PYMT AMOUNT
832 l_tbh_cur1.pymt_servicer_amt := NVL(l_tbh_cur1.pymt_servicer_amt,0) + NVL(l_dl_resp.PYMT_SERVICER_AMT,0);
833 l_tbh_cur1.pymt_servicer_date := NVL(l_dl_resp.PYMT_SERVICER_DATE, l_tbh_cur1.pymt_servicer_date);
834 l_tbh_cur1.DOCUMENT_ID_TXT := g_doc_id;
835
836 END IF;
837
838 IF p_type = 'BN' OR p_type = 'RS' THEN
839
840 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','BOOK_LOAN_AMT')||':' ||l_dl_resp.BOOK_LOAN_AMT);
841 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','BOOK_LOAN_AMT_DATE')||':' ||l_dl_resp.BOOK_LOAN_AMT_DATE);
842
843 -- update these 2 cols in the tables IGF_SL_LOR_ALL, IGF_SL_LOR_LOC_ALL and the document_id also
844 l_tbh_cur.BOOK_LOAN_AMT := NVL(l_dl_resp.BOOK_LOAN_AMT, l_tbh_cur.BOOK_LOAN_AMT);
845 l_tbh_cur.BOOK_LOAN_AMT_DATE := NVL(l_dl_resp.BOOK_LOAN_AMT_DATE, l_tbh_cur.BOOK_LOAN_AMT_DATE);
846 l_tbh_cur.PNOTE_BATCH_ID := g_doc_id;
847
848 l_tbh_cur1.BOOK_LOAN_AMT := NVL(l_dl_resp.BOOK_LOAN_AMT, l_tbh_cur1.BOOK_LOAN_AMT);
849 l_tbh_cur1.BOOK_LOAN_AMT_DATE := NVL(l_dl_resp.BOOK_LOAN_AMT_DATE, l_tbh_cur1.BOOK_LOAN_AMT_DATE);
850 l_tbh_cur1.DOCUMENT_ID_TXT := g_doc_id;
851
852 END IF;
853 END IF;
854
855 igf_sl_lor_pkg.update_row (
856 X_Mode => 'R',
857 x_rowid => l_tbh_cur.row_id,
858 x_origination_id => l_tbh_cur.origination_id,
859 x_loan_id => l_tbh_cur.loan_id,
860 x_sch_cert_date => l_tbh_cur.sch_cert_date,
861 x_orig_status_flag => l_tbh_cur.orig_status_flag,
862 x_orig_batch_id => l_tbh_cur.orig_batch_id,
863 x_orig_batch_date => l_tbh_cur.orig_batch_date,
864 x_chg_batch_id => l_tbh_cur.chg_batch_id,
865 x_orig_ack_date => l_tbh_cur.orig_ack_date,
866 x_credit_override => l_tbh_cur.credit_override,
867 x_credit_decision_date => l_tbh_cur.credit_decision_date,
868 x_req_serial_loan_code => l_tbh_cur.req_serial_loan_code,
869 x_act_serial_loan_code => l_tbh_cur.act_serial_loan_code,
870 x_pnote_delivery_code => l_tbh_cur.pnote_delivery_code,
871 x_pnote_status => l_tbh_cur.pnote_status,
872 x_pnote_status_date => l_tbh_cur.pnote_status_date,
873 x_pnote_id => l_tbh_cur.pnote_id,
874 x_pnote_print_ind => l_tbh_cur.pnote_print_ind,
875 x_pnote_accept_amt => l_tbh_cur.pnote_accept_amt,
876 x_pnote_accept_date => l_tbh_cur.pnote_accept_date,
877 x_unsub_elig_for_heal => l_tbh_cur.unsub_elig_for_heal,
878 x_disclosure_print_ind => l_tbh_cur.disclosure_print_ind,
879 x_orig_fee_perct => l_tbh_cur.orig_fee_perct,
880 x_borw_confirm_ind => l_tbh_cur.borw_confirm_ind,
881 x_borw_interest_ind => l_tbh_cur.borw_interest_ind,
882 x_borw_outstd_loan_code => l_tbh_cur.borw_outstd_loan_code,
883 x_unsub_elig_for_depnt => l_tbh_cur.unsub_elig_for_depnt,
884 x_guarantee_amt => l_tbh_cur.guarantee_amt,
885 x_guarantee_date => l_tbh_cur.guarantee_date,
886 x_guarnt_amt_redn_code => l_tbh_cur.guarnt_amt_redn_code,
887 x_guarnt_status_code => l_tbh_cur.guarnt_status_code,
888 x_guarnt_status_date => l_tbh_cur.guarnt_status_date,
889 x_lend_apprv_denied_code => NULL,
890 x_lend_apprv_denied_date => NULL,
891 x_lend_status_code => l_tbh_cur.lend_status_code,
892 x_lend_status_date => l_tbh_cur.lend_status_date,
893 x_guarnt_adj_ind => l_tbh_cur.guarnt_adj_ind,
894 x_grade_level_code => l_tbh_cur.grade_level_code,
895 x_enrollment_code => l_tbh_cur.enrollment_code,
896 x_anticip_compl_date => l_tbh_cur.anticip_compl_date,
897 x_borw_lender_id => NULL,
898 x_duns_borw_lender_id => NULL,
899 x_guarantor_id => NULL,
900 x_duns_guarnt_id => NULL,
901 x_prc_type_code => l_tbh_cur.prc_type_code,
902 x_cl_seq_number => l_tbh_cur.cl_seq_number,
903 x_last_resort_lender => l_tbh_cur.last_resort_lender,
904 x_lender_id => NULL,
905 x_duns_lender_id => NULL,
906 x_lend_non_ed_brc_id => NULL,
907 x_recipient_id => NULL,
908 x_recipient_type => NULL,
909 x_duns_recip_id => NULL,
910 x_recip_non_ed_brc_id => NULL,
911 x_rec_type_ind => l_tbh_cur.rec_type_ind,
912 x_cl_loan_type => l_tbh_cur.cl_loan_type,
913 x_cl_rec_status => NULL,
914 x_cl_rec_status_last_update => NULL,
915 x_alt_prog_type_code => l_tbh_cur.alt_prog_type_code,
916 x_alt_appl_ver_code => l_tbh_cur.alt_appl_ver_code,
917 x_mpn_confirm_code => NULL,
918 x_resp_to_orig_code => l_tbh_cur.resp_to_orig_code,
919 x_appl_loan_phase_code => NULL,
920 x_appl_loan_phase_code_chg => NULL,
921 x_appl_send_error_codes => NULL,
922 x_tot_outstd_stafford => l_tbh_cur.tot_outstd_stafford,
923 x_tot_outstd_plus => l_tbh_cur.tot_outstd_plus,
924 x_alt_borw_tot_debt => l_tbh_cur.alt_borw_tot_debt,
925 x_act_interest_rate => l_tbh_cur.act_interest_rate,
926 x_service_type_code => l_tbh_cur.service_type_code,
927 x_rev_notice_of_guarnt => l_tbh_cur.rev_notice_of_guarnt,
928 x_sch_refund_amt => l_tbh_cur.sch_refund_amt,
929 x_sch_refund_date => l_tbh_cur.sch_refund_date,
930 x_uniq_layout_vend_code => l_tbh_cur.uniq_layout_vend_code,
931 x_uniq_layout_ident_code => l_tbh_cur.uniq_layout_ident_code,
932 x_p_person_id => l_tbh_cur.p_person_id,
933 x_p_ssn_chg_date => NULL,
934 x_p_dob_chg_date => NULL,
935 x_p_permt_addr_chg_date => NULL,
936 x_p_default_status => l_tbh_cur.p_default_status,
937 x_p_signature_code => l_tbh_cur.p_signature_code,
938 x_p_signature_date => l_tbh_cur.p_signature_date,
939 x_s_ssn_chg_date => NULL,
940 x_s_dob_chg_date => NULL,
941 x_s_permt_addr_chg_date => NULL,
942 x_s_local_addr_chg_date => NULL,
943 x_s_default_status => l_tbh_cur.s_default_status,
944 x_s_signature_code => l_tbh_cur.s_signature_code,
945 x_pnote_batch_id => l_tbh_cur.pnote_batch_id,
946 x_pnote_ack_date => l_tbh_cur.pnote_ack_date,
947 x_pnote_mpn_ind => l_tbh_cur.pnote_mpn_ind ,
948 x_elec_mpn_ind => l_tbh_cur.elec_mpn_ind ,
949 x_borr_sign_ind => l_tbh_cur.borr_sign_ind ,
950 x_stud_sign_ind => l_tbh_cur.stud_sign_ind ,
951 x_borr_credit_auth_code => l_tbh_cur.borr_credit_auth_code ,
952 x_relationship_cd => l_tbh_cur.relationship_cd,
953 x_interest_rebate_percent_num => l_tbh_cur.interest_rebate_percent_num,
954 x_cps_trans_num => l_tbh_cur.cps_trans_num ,
955 x_atd_entity_id_txt => l_tbh_cur.atd_entity_id_txt,
956 x_rep_entity_id_txt => l_tbh_cur.rep_entity_id_txt,
957 x_crdt_decision_status => l_tbh_cur.crdt_decision_status,
958 x_note_message => l_tbh_cur.note_message ,
959 x_book_loan_amt => l_tbh_cur.book_loan_amt ,
960 x_book_loan_amt_date => l_tbh_cur.book_loan_amt_date,
961 x_actual_record_type_code => l_tbh_cur.actual_record_type_code,
962 x_alt_approved_amt => l_tbh_cur.alt_approved_amt,
963 x_deferment_request_code => l_tbh_cur.deferment_request_code,
964 x_eft_authorization_code => l_tbh_cur.eft_authorization_code,
965 x_external_loan_id_txt => l_tbh_cur.external_loan_id_txt,
966 x_flp_approved_amt => l_tbh_cur.flp_approved_amt,
967 x_fls_approved_amt => l_tbh_cur.fls_approved_amt,
968 x_flu_approved_amt => l_tbh_cur.flu_approved_amt,
969 x_guarantor_use_txt => l_tbh_cur.guarantor_use_txt,
970 x_lender_use_txt => l_tbh_cur.lender_use_txt,
971 x_loan_app_form_code => l_tbh_cur.loan_app_form_code,
972 x_override_grade_level_code => l_tbh_cur.override_grade_level_code,
973 x_pymt_servicer_amt => l_tbh_cur.pymt_servicer_amt,
974 x_pymt_servicer_date => l_tbh_cur.pymt_servicer_date,
975 x_reinstatement_amt => l_tbh_cur.reinstatement_amt,
976 x_requested_loan_amt => l_tbh_cur.requested_loan_amt,
977 x_school_use_txt => l_tbh_cur.school_use_txt,
978 x_b_alien_reg_num_txt => l_tbh_cur.b_alien_reg_num_txt,
979 x_esign_src_typ_cd => l_tbh_cur.esign_src_typ_cd,
980 x_acad_begin_date => l_tbh_cur.acad_begin_date,
981 x_acad_end_date => l_tbh_cur.acad_end_date);
982
983 igf_sl_lor_loc_pkg.update_row (
984 x_mode => 'R',
985 x_rowid => l_tbh_cur1.row_id,
986 x_loan_id => l_tbh_cur1.loan_id,
987 x_origination_id => l_tbh_cur1.origination_id,
988 x_loan_number => l_tbh_cur1.loan_number,
989 x_loan_type => l_tbh_cur1.loan_type,
990 x_loan_amt_offered => l_tbh_cur1.loan_amt_offered,
991 x_loan_amt_accepted => l_tbh_cur1.loan_amt_accepted,
992 x_loan_per_begin_date => l_tbh_cur1.loan_per_begin_date,
993 x_loan_per_end_date => l_tbh_cur1.loan_per_end_date,
994 x_acad_yr_begin_date => l_tbh_cur1.acad_yr_begin_date,
995 x_acad_yr_end_date => l_tbh_cur1.acad_yr_end_date,
996 x_loan_status => l_tbh_cur1.loan_status,
997 x_loan_status_date => l_tbh_cur1.loan_status_date,
998 x_loan_chg_status => l_tbh_cur1.loan_chg_status,
999 x_loan_chg_status_date => l_tbh_cur1.loan_chg_status_date,
1000 x_req_serial_loan_code => l_tbh_cur1.req_serial_loan_code,
1001 x_act_serial_loan_code => l_tbh_cur1.act_serial_loan_code,
1002 x_active => l_tbh_cur1.active,
1003 x_active_date => l_tbh_cur1.active_date,
1004 x_sch_cert_date => l_tbh_cur1.sch_cert_date,
1005 x_orig_status_flag => l_tbh_cur1.orig_status_flag,
1006 x_orig_batch_id => l_tbh_cur1.orig_batch_id,
1007 x_orig_batch_date => l_tbh_cur1.orig_batch_date,
1008 x_chg_batch_id => NULL,
1009 x_orig_ack_date => l_tbh_cur1.orig_ack_date,
1010 x_credit_override => l_tbh_cur1.credit_override,
1011 x_credit_decision_date => l_tbh_cur1.credit_decision_date,
1012 x_pnote_delivery_code => l_tbh_cur1.pnote_delivery_code,
1013 x_pnote_status => l_tbh_cur1.pnote_status,
1014 x_pnote_status_date => l_tbh_cur1.pnote_status_date,
1015 x_pnote_id => l_tbh_cur1.pnote_id,
1016 x_pnote_print_ind => l_tbh_cur1.pnote_print_ind,
1017 x_pnote_accept_amt => l_tbh_cur1.pnote_accept_amt,
1018 x_pnote_accept_date => l_tbh_cur1.pnote_accept_date,
1019 x_p_signature_code => l_tbh_cur1.p_signature_code,
1020 x_p_signature_date => l_tbh_cur1.p_signature_date,
1021 x_s_signature_code => l_tbh_cur1.s_signature_code,
1022 x_unsub_elig_for_heal => l_tbh_cur1.unsub_elig_for_heal,
1023 x_disclosure_print_ind => l_tbh_cur1.disclosure_print_ind,
1024 x_orig_fee_perct => l_tbh_cur1.orig_fee_perct,
1025 x_borw_confirm_ind => l_tbh_cur1.borw_confirm_ind,
1026 x_borw_interest_ind => l_tbh_cur1.borw_interest_ind,
1027 x_unsub_elig_for_depnt => l_tbh_cur1.unsub_elig_for_depnt,
1028 x_guarantee_amt => l_tbh_cur1.guarantee_amt,
1029 x_guarantee_date => l_tbh_cur1.guarantee_date,
1030 x_guarnt_adj_ind => l_tbh_cur1.guarnt_adj_ind,
1031 x_guarnt_amt_redn_code => l_tbh_cur1.guarnt_amt_redn_code,
1032 x_guarnt_status_code => l_tbh_cur1.guarnt_status_code,
1033 x_guarnt_status_date => l_tbh_cur1.guarnt_status_date,
1034 x_lend_apprv_denied_code => NULL,
1035 x_lend_apprv_denied_date => NULL,
1036 x_lend_status_code => l_tbh_cur1.lend_status_code,
1037 x_lend_status_date => l_tbh_cur1.lend_status_date,
1038 x_grade_level_code => l_tbh_cur1.grade_level_code,
1039 x_enrollment_code => l_tbh_cur1.enrollment_code,
1040 x_anticip_compl_date => l_tbh_cur1.anticip_compl_date,
1041 x_borw_lender_id => l_tbh_cur1.borw_lender_id,
1042 x_duns_borw_lender_id => NULL,
1043 x_guarantor_id => l_tbh_cur1.guarantor_id,
1044 x_duns_guarnt_id => NULL,
1045 x_prc_type_code => l_tbh_cur1.prc_type_code,
1046 x_rec_type_ind => l_tbh_cur1.rec_type_ind,
1047 x_cl_loan_type => l_tbh_cur1.cl_loan_type,
1048 x_cl_seq_number => l_tbh_cur1.cl_seq_number,
1049 x_last_resort_lender => l_tbh_cur1.last_resort_lender,
1050 x_lender_id => l_tbh_cur1.lender_id,
1051 x_duns_lender_id => NULL,
1052 x_lend_non_ed_brc_id => l_tbh_cur1.lend_non_ed_brc_id,
1053 x_recipient_id => l_tbh_cur1.recipient_id,
1054 x_recipient_type => l_tbh_cur1.recipient_type,
1055 x_duns_recip_id => NULL,
1056 x_recip_non_ed_brc_id => l_tbh_cur1.recip_non_ed_brc_id,
1057 x_cl_rec_status => NULL,
1058 x_cl_rec_status_last_update => NULL,
1059 x_alt_prog_type_code => l_tbh_cur1.alt_prog_type_code,
1060 x_alt_appl_ver_code => l_tbh_cur1.alt_appl_ver_code,
1061 x_borw_outstd_loan_code => l_tbh_cur1.borw_outstd_loan_code,
1062 x_mpn_confirm_code => NULL,
1063 x_resp_to_orig_code => l_tbh_cur1.resp_to_orig_code,
1064 x_appl_loan_phase_code => NULL,
1065 x_appl_loan_phase_code_chg => NULL,
1066 x_tot_outstd_stafford => l_tbh_cur1.tot_outstd_stafford,
1067 x_tot_outstd_plus => l_tbh_cur1.tot_outstd_plus,
1068 x_alt_borw_tot_debt => l_tbh_cur1.alt_borw_tot_debt,
1069 x_act_interest_rate => l_tbh_cur1.act_interest_rate,
1070 x_service_type_code => l_tbh_cur1.service_type_code,
1071 x_rev_notice_of_guarnt => l_tbh_cur1.rev_notice_of_guarnt,
1072 x_sch_refund_amt => l_tbh_cur1.sch_refund_amt,
1073 x_sch_refund_date => l_tbh_cur1.sch_refund_date,
1074 x_uniq_layout_vend_code => l_tbh_cur1.uniq_layout_vend_code,
1075 x_uniq_layout_ident_code => l_tbh_cur1.uniq_layout_ident_code,
1076 x_p_person_id => l_tbh_cur1.p_person_id,
1077 x_p_ssn => l_tbh_cur1.p_ssn,
1078 x_p_ssn_chg_date => NULL,
1079 x_p_last_name => l_tbh_cur1.p_last_name,
1080 x_p_first_name => l_tbh_cur1.p_first_name,
1081 x_p_middle_name => l_tbh_cur1.p_middle_name,
1082 x_p_permt_addr1 => l_tbh_cur1.p_permt_addr1,
1083 x_p_permt_addr2 => l_tbh_cur1.p_permt_addr2,
1084 x_p_permt_city => l_tbh_cur1.p_permt_city,
1085 x_p_permt_state => l_tbh_cur1.p_permt_state,
1086 x_p_permt_zip => l_tbh_cur1.p_permt_zip,
1087 x_p_permt_addr_chg_date => l_tbh_cur1.p_permt_addr_chg_date,
1088 x_p_permt_phone => l_tbh_cur1.p_permt_phone,
1089 x_p_email_addr => l_tbh_cur1.p_email_addr,
1090 x_p_date_of_birth => l_tbh_cur1.p_date_of_birth,
1091 x_p_dob_chg_date => NULL,
1092 x_p_license_num => l_tbh_cur1.p_license_num,
1093 x_p_license_state => l_tbh_cur1.p_license_state,
1094 x_p_citizenship_status => l_tbh_cur1.p_citizenship_status,
1095 x_p_alien_reg_num => l_tbh_cur1.p_alien_reg_num,
1096 x_p_default_status => l_tbh_cur1.p_default_status,
1097 x_p_foreign_postal_code => l_tbh_cur1.p_foreign_postal_code,
1098 x_p_state_of_legal_res => l_tbh_cur1.p_state_of_legal_res,
1099 x_p_legal_res_date => l_tbh_cur1.p_legal_res_date,
1100 x_s_ssn => l_tbh_cur1.s_ssn,
1101 x_s_ssn_chg_date => NULL,
1102 x_s_last_name => l_tbh_cur1.s_last_name,
1103 x_s_first_name => l_tbh_cur1.s_first_name,
1104 x_s_middle_name => l_tbh_cur1.s_middle_name,
1105 x_s_permt_addr1 => l_tbh_cur1.s_permt_addr1,
1106 x_s_permt_addr2 => l_tbh_cur1.s_permt_addr2,
1107 x_s_permt_city => l_tbh_cur1.s_permt_city,
1108 x_s_permt_state => l_tbh_cur1.s_permt_state,
1109 x_s_permt_zip => l_tbh_cur1.s_permt_zip,
1110 x_s_permt_addr_chg_date => l_tbh_cur1.s_permt_addr_chg_date,
1111 x_s_permt_phone => l_tbh_cur1.s_permt_phone,
1112 x_s_local_addr1 => l_tbh_cur1.s_local_addr1,
1113 x_s_local_addr2 => l_tbh_cur1.s_local_addr2,
1114 x_s_local_city => l_tbh_cur1.s_local_city,
1115 x_s_local_state => l_tbh_cur1.s_local_state,
1116 x_s_local_zip => l_tbh_cur1.s_local_zip,
1117 x_s_local_addr_chg_date => NULL,
1118 x_s_email_addr => l_tbh_cur1.s_email_addr,
1119 x_s_date_of_birth => l_tbh_cur1.s_date_of_birth,
1120 x_s_dob_chg_date => NULL,
1121 x_s_license_num => l_tbh_cur1.s_license_num,
1122 x_s_license_state => l_tbh_cur1.s_license_state,
1123 x_s_depncy_status => l_tbh_cur1.s_depncy_status,
1124 x_s_default_status => l_tbh_cur1.s_default_status,
1125 x_s_citizenship_status => l_tbh_cur1.s_citizenship_status,
1126 x_s_alien_reg_num => l_tbh_cur1.s_alien_reg_num,
1127 x_s_foreign_postal_code => l_tbh_cur1.s_foreign_postal_code,
1128 x_pnote_batch_id => l_tbh_cur1.pnote_batch_id,
1129 x_pnote_ack_date => l_tbh_cur1.pnote_ack_date,
1130 x_pnote_mpn_ind => l_tbh_cur1.pnote_mpn_ind,
1131 x_award_id => l_tbh_cur1.award_id ,
1132 x_base_id => l_tbh_cur1.base_id ,
1133 x_document_id_txt => l_tbh_cur1.document_id_txt ,
1134 x_loan_key_num => l_tbh_cur1.loan_key_num ,
1135 x_INTEREST_REBATE_PERCENT_NUM => l_tbh_cur1.INTEREST_REBATE_PERCENT_NUM,
1136 x_fin_award_year => l_tbh_cur1.fin_award_year ,
1137 x_cps_trans_num => l_tbh_cur1.cps_trans_num ,
1138 x_ATD_ENTITY_ID_TXT => l_tbh_cur1.ATD_ENTITY_ID_TXT,
1139 x_REP_ENTITY_ID_TXT => l_tbh_cur1.REP_ENTITY_ID_TXT,
1140 x_SOURCE_ENTITY_ID_TXT => l_tbh_cur1.SOURCE_ENTITY_ID_TXT,
1141 x_pymt_servicer_amt => l_tbh_cur1.pymt_servicer_amt ,
1142 x_pymt_servicer_date => l_tbh_cur1.pymt_servicer_date ,
1143 x_book_loan_amt => l_tbh_cur1.book_loan_amt ,
1144 x_book_loan_amt_date => l_tbh_cur1.book_loan_amt_date ,
1145 x_s_chg_birth_date => l_tbh_cur1.s_chg_birth_date ,
1146 x_s_chg_ssn => l_tbh_cur1.s_chg_ssn ,
1147 x_s_chg_last_name => l_tbh_cur1.s_chg_last_name ,
1148 x_b_chg_birth_date => l_tbh_cur1.b_chg_birth_date ,
1149 x_b_chg_ssn => l_tbh_cur1.b_chg_ssn ,
1150 x_b_chg_last_name => l_tbh_cur1.b_chg_last_name ,
1151 x_note_message => l_tbh_cur1.note_message ,
1152 x_full_resp_code => l_tbh_cur1.full_resp_code ,
1153 x_s_permt_county => l_tbh_cur1.s_permt_county ,
1154 x_b_permt_county => l_tbh_cur1.b_permt_county ,
1155 x_s_permt_country => l_tbh_cur1.s_permt_country ,
1156 x_b_permt_country => l_tbh_cur1.b_permt_country ,
1157 x_crdt_decision_status => l_tbh_cur1.crdt_decision_status,
1158 x_actual_record_type_code => l_tbh_cur1.actual_record_type_code,
1159 x_alt_approved_amt => l_tbh_cur1.alt_approved_amt,
1160 x_alt_borrower_ind_flag => l_tbh_cur1.alt_borrower_ind_flag,
1161 x_borower_credit_authoriz_flag => l_tbh_cur1.borower_credit_authoriz_flag,
1162 x_borower_electronic_sign_flag => l_tbh_cur1.borower_electronic_sign_flag,
1163 x_cost_of_attendance_amt => l_tbh_cur1.cost_of_attendance_amt,
1164 x_deferment_request_code => l_tbh_cur1.deferment_request_code,
1165 x_eft_authorization_code => l_tbh_cur1.eft_authorization_code,
1166 x_established_fin_aid_amount => l_tbh_cur1.established_fin_aid_amount,
1167 x_expect_family_contribute_amt => l_tbh_cur1.expect_family_contribute_amt,
1168 x_external_loan_id_txt => l_tbh_cur1.external_loan_id_txt,
1169 x_flp_approved_amt => l_tbh_cur1.flp_approved_amt,
1170 x_fls_approved_amt => l_tbh_cur1.fls_approved_amt,
1171 x_flu_approved_amt => l_tbh_cur1.flu_approved_amt,
1172 x_guarantor_use_txt => l_tbh_cur1.guarantor_use_txt,
1173 x_lender_use_txt => l_tbh_cur1.lender_use_txt,
1174 x_loan_app_form_code => l_tbh_cur1.loan_app_form_code,
1175 x_mpn_type_flag => l_tbh_cur1.mpn_type_flag,
1176 x_reinstatement_amt => l_tbh_cur1.reinstatement_amt,
1177 x_requested_loan_amt => l_tbh_cur1.requested_loan_amt,
1178 x_school_id_txt => l_tbh_cur1.school_id_txt,
1179 x_school_use_txt => l_tbh_cur1.school_use_txt,
1180 x_student_electronic_sign_flag => l_tbh_cur1.student_electronic_sign_flag,
1181 x_esign_src_typ_cd => l_tbh_cur1.esign_src_typ_cd
1182 );
1183
1184 igf_sl_loans_pkg.update_row (
1185 x_mode => 'R',
1186 x_rowid => l_tbh_cur2.row_id,
1187 x_loan_id => l_tbh_cur2.loan_id,
1188 x_award_id => l_tbh_cur2.award_id,
1189 x_seq_num => l_tbh_cur2.seq_num,
1190 x_loan_number => l_tbh_cur2.loan_number,
1191 x_loan_per_begin_date => l_tbh_cur2.loan_per_begin_date,
1192 x_loan_per_end_date => l_tbh_cur2.loan_per_end_date,
1193 x_loan_status => l_tbh_cur2.loan_status,
1194 x_loan_status_date => l_tbh_cur2.loan_status_date,
1195 x_loan_chg_status => l_tbh_cur2.loan_chg_status,
1196 x_loan_chg_status_date => l_tbh_cur2.loan_chg_status_date,
1197 x_active => l_tbh_cur2.active,
1198 x_active_date => l_tbh_cur2.active_date,
1199 x_borw_detrm_code => l_tbh_cur2.borw_detrm_code,
1200 x_legacy_record_flag => NULL,
1201 x_external_loan_id_txt => l_tbh_cur2.external_loan_id_txt);
1202
1203 UPDATE IGF_SL_DL_RESP_DTLS
1204 SET STATUS_CODE = 'P'
1205 WHERE DL_LOAN_RESP_ID = l_dl_resp.DL_LOAN_RESP_ID;
1206
1207 -- start the processing of Direct Loan Disbursements
1208 FOR l_dl_db_resp IN get_dl_db_resp(l_dl_resp.DL_LOAN_RESP_ID)
1209 LOOP
1210 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','DISB_NUM')||':'||l_dl_db_resp.disb_num);
1211 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','DISB_SEQ_NUM')||':' ||l_dl_db_resp.disb_seq_num);
1212 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_ST')||':' ||l_dl_db_resp.RESP_CODE);
1213
1214 l_dl_db_rec := NULL;
1215 OPEN get_dl_db_rec(l_dl_rec.AWARD_ID,l_dl_db_resp.disb_seq_num,l_dl_db_resp.disb_num);
1216 FETCH get_dl_db_rec INTO l_dl_db_rec;
1217 CLOSE get_dl_db_rec;
1218
1219 l_tbh_disb := NULL;
1220 OPEN c_tbh_disb(l_dl_rec.AWARD_ID,l_dl_db_resp.disb_seq_num,l_dl_db_resp.disb_num);
1221 FETCH c_tbh_disb INTO l_tbh_disb;
1222 CLOSE c_tbh_disb;
1223
1224 l_tbh_disb1 := NULL;
1225 OPEN c_tbh_disb1(l_dl_rec.AWARD_ID,l_dl_db_resp.disb_seq_num,l_dl_db_resp.disb_num);
1226 FETCH c_tbh_disb1 INTO l_tbh_disb1;
1227 CLOSE c_tbh_disb1;
1228
1229 update_flag := FALSE;
1230 IF p_rej_flg = TRUE OR l_dl_db_resp.RESP_CODE = 'R' THEN
1231 -- update the table IGF_AW_DB_COD_DTLS with DISB_STATUS = 'R' , IGF_AW_DB_CHG_DTLS DISB_STATUS = 'R'
1232 IF l_dl_db_resp.disb_seq_num < 66 THEN
1233 IF l_tbh_disb.disb_seq_num IS NULL THEN
1234 -- disbursement record not found in the system. Log a mesg.
1235 fnd_message.set_name('IGF','IGF_SL_COD_SKIP');
1236 fnd_file.put_line(fnd_file.log,fnd_message.get);
1237
1238 UPDATE IGF_SL_DLDB_RSP_DTL
1239 SET status_code = 'N'
1240 WHERE DISB_RESP_ID = l_dl_db_resp.DISB_RESP_ID;
1241 update_flag := FALSE;
1242 ELSE
1243 l_tbh_disb.DISB_STATUS := 'R';
1244 --l_tbh_disb1.DISB_STATUS := 'R';
1245
1246 UPDATE IGF_SL_DLDB_RSP_DTL
1247 SET status_code = 'P'
1248 WHERE DISB_RESP_ID = l_dl_db_resp.DISB_RESP_ID;
1249 update_flag := TRUE;
1250 END IF;
1251 END IF;
1252 ELSE
1253 print_edits(l_dl_db_resp.DISB_RESP_ID,'DL_DB');
1254 IF l_dl_db_resp.disb_seq_num < 66 THEN
1255 IF l_tbh_disb.disb_seq_num IS NULL THEN
1256 -- disbursement record not found in the system. Log a mesg.
1257 fnd_message.set_name('IGF','IGF_SL_COD_SKIP');
1258 fnd_file.put_line(fnd_file.log,fnd_message.get);
1259
1260 -- update the status to 'N' - Processed, Not found in the System
1261 UPDATE IGF_SL_DLDB_RSP_DTL
1262 SET STATUS_CODE = 'N'
1263 WHERE DISB_RESP_ID = l_dl_db_resp.DISB_RESP_ID;
1264 update_flag := FALSE;
1265 ELSE
1266 IF l_dl_db_rec.DISB_ACCEPTED_AMT <> l_dl_db_resp.disb_amt THEN
1267 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','SYS_VAL_DB_AMT')||':' ||l_dl_db_rec.DISB_ACCEPTED_AMT);
1268 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_VAL_DB_AMT')||':' ||l_dl_db_resp.disb_amt);
1269 igf_gr_gen.insert_sys_holds(l_dl_rec.award_id,l_dl_db_rec.disb_num,'DL');
1270 END IF;
1271 IF l_dl_db_rec.disb_date <> l_dl_db_resp.disb_date THEN
1272 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','SYS_VAL_DB_DT')||':' ||l_dl_db_rec.disb_date);
1273 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_VAL_DB_DT')||':' ||l_dl_db_resp.disb_date);
1274 END IF;
1275
1276 -- update in the table IGF_AW_DB_COD_DTLS , IGF_AW_DB_CHG_DTLS RESP_CODE,PREV_SEQ_NUM,STATUS
1277 l_tbh_disb.DISB_STATUS := l_dl_db_resp.RESP_CODE;
1278 --l_tbh_disb1.DISB_STATUS := l_dl_db_resp.RESP_CODE;
1279 --l_tbh_disb.PREV_SEQ_NUM := l_dl_db_resp.PREV_SEQ_NUM;
1280 --l_tbh_disb1.PREV_SEQ_NUM := l_dl_db_resp.PREV_SEQ_NUM;
1281
1282 UPDATE IGF_SL_DLDB_RSP_DTL
1283 SET STATUS_CODE = 'P'
1284 WHERE DISB_RESP_ID = l_dl_db_resp.DISB_RESP_ID;
1285 update_flag := TRUE;
1286 END IF;
1287 ELSE -- implies disb_seq_num > 65
1288 IF l_dl_db_resp.DISB_SEQ_NUM < 91 THEN
1289 fnd_message.set_name('IGF','IGF_SL_COD_SCHL_ADJ');
1290 fnd_file.put_line(fnd_file.log,fnd_message.get);
1291 ELSIF l_dl_db_resp.DISB_SEQ_NUM < 100 THEN
1292 fnd_message.set_name('IGF','IGF_SL_COD_GEN_DISB');
1293 fnd_file.put_line(fnd_file.log,fnd_message.get);
1294 END IF;
1295
1296 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_VAL_DB_AMT')||':' ||l_dl_db_resp.DISB_AMT);
1297 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_VAL_DB_DT')||':' ||l_dl_db_resp.DISB_DATE);
1298 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_VAL_PRV_SEQ_NUM')||':' ||l_dl_db_resp.PREV_SEQ_NUM);
1299
1300 UPDATE IGF_SL_DLDB_RSP_DTL
1301 SET STATUS_CODE = 'P'
1302 WHERE DISB_RESP_ID = l_dl_db_resp.DISB_RESP_ID;
1303 update_flag := FALSE;
1304 END IF;
1305 END IF; -- for the rejected flag check
1306
1307 IF (update_flag) THEN
1308 igf_aw_db_chg_dtls_pkg.update_row(
1309 x_rowid => l_tbh_disb.row_id ,
1310 x_award_id => l_tbh_disb.award_id,
1311 x_disb_num => l_tbh_disb.disb_num ,
1312 x_disb_seq_num => l_tbh_disb.disb_seq_num,
1313 x_DISB_ACCEPTED_AMT => l_tbh_disb.DISB_ACCEPTED_AMT,
1314 x_orig_fee_amt => l_tbh_disb.orig_fee_amt ,
1315 x_disb_net_amt => l_tbh_disb.disb_net_amt ,
1316 x_disb_date => l_tbh_disb.disb_date ,
1317 x_disb_activity => l_tbh_disb.disb_activity ,
1318 x_disb_status => l_tbh_disb.disb_status ,
1319 x_disb_status_date => l_tbh_disb.disb_status_date,
1320 x_disb_rel_flag => l_tbh_disb.disb_rel_flag ,
1321 x_first_disb_flag => l_tbh_disb.first_disb_flag ,
1322 x_INTEREST_REBATE_AMT => l_tbh_disb.INTEREST_REBATE_AMT,
1323 x_disb_conf_flag => l_tbh_disb.disb_conf_flag ,
1324 x_pymnt_prd_start_date => l_tbh_disb.pymnt_prd_start_date ,
1325 x_note_message => l_tbh_disb.note_message ,
1326 x_batch_id_txt => l_tbh_disb.batch_id_txt ,
1327 x_ack_date => l_tbh_disb.ack_date ,
1328 x_booking_id_txt => l_tbh_disb.booking_id_txt ,
1329 x_booking_date => l_tbh_disb.booking_date ,
1330 x_mode => 'R'
1331 );
1332 igf_aw_db_cod_dtls_pkg.update_row(
1333 x_rowid => l_tbh_disb1.row_id ,
1334 x_award_id => l_tbh_disb1.award_id ,
1335 x_document_id_txt => l_tbh_disb1.document_id_txt ,
1336 x_disb_num => l_tbh_disb1.disb_num ,
1337 x_disb_seq_num => l_tbh_disb1.disb_seq_num ,
1338 x_DISB_ACCEPTED_AMT => l_tbh_disb1.DISB_ACCEPTED_AMT,
1339 x_orig_fee_amt => l_tbh_disb1.orig_fee_amt ,
1340 x_disb_net_amt => l_tbh_disb1.disb_net_amt ,
1341 x_disb_date => l_tbh_disb1.disb_date ,
1342 x_disb_rel_flag => l_tbh_disb1.disb_rel_flag ,
1343 x_first_disb_flag => l_tbh_disb1.first_disb_flag ,
1344 x_INTEREST_REBATE_AMT => l_tbh_disb1.INTEREST_REBATE_AMT,
1345 x_disb_conf_flag => l_tbh_disb1.disb_conf_flag ,
1346 x_pymnt_per_start_date => l_tbh_disb1.pymnt_per_start_date,
1347 x_note_message => l_tbh_disb1.note_message ,
1348 x_rep_entity_id_txt => l_tbh_disb1.rep_entity_id_txt,
1349 x_atd_entity_id_txt => l_tbh_disb1.atd_entity_id_txt,
1350 x_mode => 'R'
1351 );
1352 END IF;
1353 END LOOP; -- DL DISBURSEMENT LOOP
1354 END IF; -- FOR THE RECORD EXISTS CHECK
1355 END LOOP; -- DL AWARDS LOOP
1356
1357 EXCEPTION
1358 WHEN OTHERS THEN
1359 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1360 fnd_message.set_token('NAME','igf_sl_upload_xml.process_dl_records');
1361 igs_ge_msg_stack.add;
1362 app_exception.raise_exception;
1363 END process_dl_records;
1364
1365 PROCEDURE process_pell_records ( p_st_id IN NUMBER,
1366 p_ssn IN VARCHAR2,
1367 p_lname IN VARCHAR2,
1368 p_dob IN DATE,
1369 p_rej_flg IN BOOLEAN
1370 )
1371 AS
1372
1373 CURSOR get_pell_resp ( cp_st_id NUMBER) IS
1374 SELECT *
1375 FROM IGF_GR_RESP_DTLS
1376 WHERE STDNT_RESP_ID = cp_st_id
1377 AND NVL(STATUS_CODE, '*') <> 'P';
1378
1379 CURSOR get_pell_db_resp (cp_pell_id NUMBER) IS
1380 SELECT *
1381 FROM IGF_GR_DB_RESP_DTLS
1382 WHERE PELL_RESP_ID = cp_pell_id
1383 AND NVL(STATUS_CODE, '*') <> 'P';
1384
1385 CURSOR get_pell_rec ( cp_ssn VARCHAR2,
1386 cp_lname VARCHAR2,
1387 cp_dob DATE,
1388 cp_fin_awd_yr VARCHAR2
1389 ) IS
1390 SELECT *
1391 FROM IGF_GR_COD_DTLS
1392 WHERE S_SSN = cp_ssn
1393 AND S_DATE_OF_BIRTH = cp_dob
1394 AND S_LAST_NAME = cp_lname
1395 AND FIN_AWARD_YEAR = cp_fin_awd_yr;
1396 l_pell_rec get_pell_rec%ROWTYPE;
1397
1398 CURSOR get_pell_db_rec ( cp_awd_id NUMBER,
1399 cp_disb_seq_num NUMBER,
1400 cp_disb_num NUMBER
1401 ) IS
1402 SELECT *
1403 FROM IGF_AW_DB_CHG_DTLS
1404 WHERE AWARD_ID = cp_awd_id
1405 AND DISB_NUM = cp_disb_num
1406 AND DISB_SEQ_NUM = cp_disb_seq_num;
1407 l_pell_db_rec get_pell_db_rec%ROWTYPE;
1408
1409 CURSOR c_tbh_pell ( orig_id IGF_GR_RFMS.ORIGINATION_ID%TYPE) IS
1410 SELECT *
1411 FROM IGF_GR_RFMS
1412 WHERE ORIGINATION_ID = orig_id
1413 FOR UPDATE;
1414 l_tbh_pell c_tbh_pell%ROWTYPE;
1415
1416 -- Cursors from the disbursements updation
1417 CURSOR c_tbh_disb ( cp_awd_id NUMBER,
1418 cp_disb_seq_num NUMBER,
1419 cp_disb_num NUMBER
1420 ) IS
1421 SELECT *
1422 FROM IGF_AW_DB_CHG_DTLS_V
1423 WHERE AWARD_ID = cp_awd_id
1424 AND DISB_NUM = cp_disb_num
1425 AND DISB_SEQ_NUM = cp_disb_seq_num;
1426 l_tbh_disb c_tbh_disb%ROWTYPE;
1427
1428 CURSOR c_tbh_disb1 ( cp_awd_id NUMBER,
1429 cp_disb_seq_num NUMBER,
1430 cp_disb_num NUMBER
1431 ) IS
1432 SELECT *
1433 FROM IGF_AW_DB_COD_DTLS_V
1434 WHERE AWARD_ID = cp_awd_id
1435 AND DISB_NUM = cp_disb_num
1436 AND DISB_SEQ_NUM = cp_disb_seq_num;
1437 l_tbh_disb1 c_tbh_disb1%ROWTYPE;
1438
1439 update_flag BOOLEAN;
1440
1441
1442 BEGIN
1443
1444
1445 FOR l_pell_resp IN get_pell_resp(p_st_id)
1446 LOOP
1447 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','PROC_PELL'));
1448 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_ST')||':' ||l_pell_resp.RESP_CODE );
1449
1450 l_pell_rec := NULL;
1451 OPEN get_pell_rec(p_ssn,p_lname,p_dob,l_pell_resp.FIN_AWD_YR);
1452 FETCH get_pell_rec INTO l_pell_rec;
1453 CLOSE get_pell_rec;
1454
1455
1456
1457
1458 IF l_pell_rec.origination_id IS NULL THEN
1459 fnd_message.set_name('IGF','IGF_SL_COD_SKIP');
1460 fnd_file.put_line(fnd_file.log,fnd_message.get);
1461
1462 -- Update the status to 'N' - Processed, Not found in the System
1463 UPDATE IGF_GR_RESP_DTLS
1464 SET STATUS_CODE = 'N'
1465 WHERE PELL_RESP_ID = l_pell_resp.PELL_RESP_ID;
1466 ELSE
1467
1468
1469 l_tbh_pell := NULL;
1470 OPEN c_tbh_pell(l_pell_rec.ORIGINATION_ID);
1471 FETCH c_tbh_pell INTO l_tbh_pell;
1472 CLOSE c_tbh_pell;
1473
1474 print_edits(l_pell_resp.PELL_RESP_ID,'PELL');
1475
1476 IF p_rej_flg = TRUE OR l_pell_resp.RESP_CODE = 'R' THEN
1477 -- In response file 'R' means Rejected.
1478 -- In the system, Pell Orig Status 'E' means Rejected and 'R' means 'Ready to Send'
1479 l_tbh_pell.ORIG_ACTION_CODE := 'E';
1480 ELSIF l_pell_resp.RESP_CODE IN ('A','C') THEN
1481 -- compare the values in the resp record with the system record
1482 IF l_pell_resp.AWARD_AMT <> l_pell_rec.AWARD_AMT THEN
1483 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','SYS_VAL_PELL')||':' ||l_pell_rec.AWARD_AMT );
1484 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_VAL_PELL')||':' ||l_pell_resp.AWARD_AMT );
1485 END IF;
1486 IF l_pell_resp.COA_AMT <> l_pell_rec.COA_AMT THEN
1487 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','SYS_VAL_COA')||':' ||l_pell_rec.COA_AMT);
1488 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_VAL_COA')||':' ||l_pell_resp.COA_AMT);
1489 END IF;
1490 IF l_pell_resp.SCHD_PELL_AMT <> l_pell_rec.SCHD_PELL_AMT THEN
1491 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','SYS_VAL_FT')||':' ||l_pell_rec.SCHD_PELL_AMT);
1492 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_VAL_FT')||':' ||l_pell_resp.SCHD_PELL_AMT);
1493 END IF;
1494 IF l_pell_resp.VER_STATUS_CODE <> l_pell_rec.VER_STATUS_CODE THEN
1495 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','SYS_VAL_VER')||':' ||l_pell_rec.VER_STATUS_CODE);
1496 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_VAL_VER')||':' ||l_pell_resp.VER_STATUS_CODE);
1497 END IF;
1498
1499 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','YTD_DISB_AMT')||':' ||l_pell_resp.YTD_DISB_AMT);
1500 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_ELIG_USED')||':' ||l_pell_resp.TOT_ELIG_USED);
1501 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','NEG_PEND_AMT')||':' ||l_pell_resp.NEG_PEND_AMT);
1502
1503 /*SELECT THE COLUMNS YTD_DISB_AMT, TOT_ELIG_USED, SCHD_PELL_AMT, NEG_PEND_AMT, FSA_CODE_1,FSA_CODE_2,FSA_CODE_3,PELL_STATUS
1504 CPS_VERIF_FLAG, HIGH_CPS_TRANS_NUM FROM IGF_GR_RESP_DTLS
1505 Also update the fields YTD_DISB_AMT, TOT_ELIG_USED, PENDING_AMOUNT,ORIG_aCTION_CODE in the table IGF_GR_RFMS_ALL
1506 */--dont
1507 l_tbh_pell.ORIG_ACTION_CODE := l_pell_resp.RESP_CODE;
1508 END IF;
1509
1510 igf_gr_rfms_pkg.update_row(
1511 x_rowid => l_tbh_pell.row_id,
1512 x_origination_id => l_tbh_pell.origination_id,
1513 x_ci_cal_type => l_tbh_pell.ci_cal_type ,
1514 x_ci_sequence_number => l_tbh_pell.ci_sequence_number ,
1515 x_base_id => l_tbh_pell.base_id ,
1516 x_award_id => l_tbh_pell.award_id ,
1517 x_rfmb_id => l_tbh_pell.rfmb_id ,
1518 x_sys_orig_ssn => l_tbh_pell.sys_orig_ssn ,
1519 x_sys_orig_name_cd => l_tbh_pell.sys_orig_name_cd ,
1520 x_transaction_num => l_tbh_pell.transaction_num ,
1521 x_efc => l_tbh_pell.efc ,
1522 x_ver_status_code => l_tbh_pell.ver_status_code ,
1523 x_secondary_efc => l_tbh_pell.secondary_efc ,
1524 x_secondary_efc_cd => l_tbh_pell.secondary_efc_cd ,
1525 x_pell_amount => l_tbh_pell.pell_amount ,
1526 x_pell_profile => l_tbh_pell.pell_profile ,
1527 x_enrollment_status => l_tbh_pell.enrollment_status ,
1528 x_enrollment_dt => l_tbh_pell.enrollment_dt ,
1529 x_coa_amount => l_tbh_pell.coa_amount ,
1530 x_academic_calendar => l_tbh_pell.academic_calendar ,
1531 x_payment_method => l_tbh_pell.payment_method ,
1532 x_total_pymt_prds => l_tbh_pell.total_pymt_prds ,
1533 x_incrcd_fed_pell_rcp_cd => l_tbh_pell.incrcd_fed_pell_rcp_cd,
1534 x_attending_campus_id => l_tbh_pell.attending_campus_id ,
1535 x_est_disb_dt1 => l_tbh_pell.est_disb_dt1 ,
1536 x_orig_action_code => l_tbh_pell.orig_action_code ,
1537 x_orig_status_dt => l_tbh_pell.orig_status_dt ,
1538 x_orig_ed_use_flags => l_tbh_pell.orig_ed_use_flags ,
1539 x_ft_pell_amount => l_tbh_pell.ft_pell_amount ,
1540 x_prev_accpt_efc => l_tbh_pell.prev_accpt_efc ,
1541 x_prev_accpt_tran_no => l_tbh_pell.prev_accpt_tran_no ,
1542 x_prev_accpt_sec_efc_cd => l_tbh_pell.prev_accpt_sec_efc_cd ,
1543 x_prev_accpt_coa => l_tbh_pell.prev_accpt_coa ,
1544 x_orig_reject_code => l_tbh_pell.orig_reject_code ,
1545 x_wk_inst_time_calc_pymt => l_tbh_pell.wk_inst_time_calc_pymt,
1546 x_wk_int_time_prg_def_yr => l_tbh_pell.wk_int_time_prg_def_yr,
1547 x_cr_clk_hrs_prds_sch_yr => l_tbh_pell.cr_clk_hrs_prds_sch_yr,
1548 x_cr_clk_hrs_acad_yr => l_tbh_pell.cr_clk_hrs_acad_yr ,
1549 x_inst_cross_ref_cd => l_tbh_pell.inst_cross_ref_cd ,
1550 x_low_tution_fee => l_tbh_pell.low_tution_fee ,
1551 x_rec_source => l_tbh_pell.rec_source ,
1552 x_pending_amount => l_tbh_pell.pending_amount ,
1553 x_mode => 'R',
1554 x_birth_dt => l_tbh_pell.birth_dt ,
1555 x_last_name => l_tbh_pell.last_name ,
1556 x_first_name => l_tbh_pell.first_name ,
1557 x_middle_name => l_tbh_pell.middle_name ,
1558 x_current_ssn => l_tbh_pell.current_ssn ,
1559 x_legacy_record_flag => NULL,
1560 x_reporting_pell_cd => NULL,
1561 x_rep_entity_id_txt => l_tbh_pell.rep_entity_id_txt ,
1562 x_atd_entity_id_txt => l_tbh_pell.atd_entity_id_txt ,
1563 x_note_message => l_tbh_pell.note_message ,
1564 x_full_resp_code => l_tbh_pell.full_resp_code ,
1565 x_document_id_txt => l_tbh_pell.document_id_txt
1566 );
1567
1568 UPDATE IGF_GR_RESP_DTLS
1569 SET STATUS_CODE = 'P'
1570 WHERE PELL_RESP_ID = l_pell_resp.PELL_RESP_ID;
1571
1572 -- start the processing of pell disbursements
1573 FOR l_pell_db_resp IN get_pell_db_resp(l_pell_resp.PELL_RESP_ID)
1574 LOOP
1575 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','DISB_NUM')||':'||l_pell_db_resp.disb_num);
1576 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','DISB_SEQ_NUM')||':' ||l_pell_db_resp.disb_seq_num);
1577 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_ST')||':' ||l_pell_db_resp.RESP_CODE);
1578
1579 l_pell_db_rec := NULL;
1580 OPEN get_pell_db_rec(l_pell_rec.AWARD_ID,l_pell_db_resp.disb_seq_num,l_pell_db_resp.disb_num);
1581 FETCH get_pell_db_rec INTO l_pell_db_rec;
1582 CLOSE get_pell_db_rec;
1583
1584 l_tbh_disb := NULL;
1585 OPEN c_tbh_disb(l_pell_rec.AWARD_ID,l_pell_db_resp.disb_seq_num,l_pell_db_resp.disb_num);
1586 FETCH c_tbh_disb INTO l_tbh_disb;
1587 CLOSE c_tbh_disb;
1588
1589 l_tbh_disb1 := NULL;
1590 OPEN c_tbh_disb1(l_pell_rec.AWARD_ID,l_pell_db_resp.disb_seq_num,l_pell_db_resp.disb_num);
1591 FETCH c_tbh_disb1 INTO l_tbh_disb1;
1592 CLOSE c_tbh_disb1;
1593
1594 update_flag := FALSE;
1595 IF p_rej_flg = TRUE OR l_pell_db_resp.RESP_CODE = 'R' THEN
1596 -- update the table IGF_AW_DB_COD_DTLS with DISB_STATUS = 'R' , IGF_AW_DB_CHG_DTLS DISB_STATUS = 'R'
1597 -- update the status_code = 'P' in IGF_GR_DB_RESP_DTLS
1598 IF l_pell_db_resp.disb_seq_num < 66 THEN
1599 IF l_tbh_disb.disb_seq_num IS NULL THEN
1600 -- disbursement record not found in the system. Log a mesg.
1601 fnd_message.set_name('IGF','IGF_SL_COD_SKIP');
1602 fnd_file.put_line(fnd_file.log,fnd_message.get);
1603
1604 UPDATE IGF_GR_DB_RESP_DTLS
1605 SET status_code = 'N'
1606 WHERE DISB_RESP_ID = l_pell_db_resp.DISB_RESP_ID;
1607 update_flag := FALSE;
1608 ELSE
1609 l_tbh_disb.DISB_STATUS := 'R';
1610 --l_tbh_disb1.DISB_STATUS := 'R';
1611 UPDATE IGF_GR_DB_RESP_DTLS
1612 SET status_code = 'P'
1613 WHERE DISB_RESP_ID = l_pell_db_resp.DISB_RESP_ID;
1614 update_flag := TRUE;
1615 END IF;
1616 END IF;
1617 ELSE
1618 print_edits(l_pell_db_resp.DISB_RESP_ID,'PELL_DB');
1619 IF l_pell_db_resp.disb_seq_num < 66 THEN
1620 IF l_tbh_disb.disb_seq_num IS NULL THEN
1621 -- disbursement record not found in the system. Log a mesg.
1622 fnd_message.set_name('IGF','IGF_SL_COD_SKIP');
1623 fnd_file.put_line(fnd_file.log,fnd_message.get);
1624
1625 -- update the status to 'N' - Processed, Not found in the System
1626 UPDATE IGF_GR_DB_RESP_DTLS
1627 SET STATUS_CODE = 'N'
1628 WHERE DISB_RESP_ID = l_pell_db_resp.DISB_RESP_ID;
1629 update_flag := FALSE;
1630 ELSE
1631 /* Compare the system disbursement amount and the response disbursement amount, and disbursement date,
1632 if these are different then print into the log file and insert system hold on the disbursement as per
1633 existing logic. Call the wrapper igf_gr_gen.insert_sys_holds(rec_award.award_id,rec_disb_orig.disb_ref_num,'PELL');
1634 Update the disb_status = resp_code in table IGF_AW_DB_CHG_DTLS
1635 Also print in the log file, Payment Period Start Date if present and update it in the table
1636 IGF_AW_DISB_COD_DTLS and IGF_AW_DB_CHG_DTLS.
1637 Update the status_code = 'P' in IGF_GR_DB_RESP_DTLS*/ --dont
1638
1639 IF l_pell_db_rec.DISB_ACCEPTED_AMT <> l_pell_db_resp.disb_amt THEN
1640 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','SYS_VAL_DB_AMT')||':' ||l_pell_db_rec.DISB_ACCEPTED_AMT);
1641 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_VAL_DB_AMT')||':' ||l_pell_db_resp.disb_amt);
1642 igf_gr_gen.insert_sys_holds(l_pell_rec.award_id,l_pell_db_rec.disb_num,'PELL');
1643 END IF;
1644 IF l_pell_db_rec.disb_date <> l_pell_db_resp.disb_date THEN
1645 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','SYS_VAL_DB_DT')||':' ||l_pell_db_rec.disb_date);
1646 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_VAL_DB_DT')||':' ||l_pell_db_resp.disb_date);
1647 END IF;
1648 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_VAL_PYMT_PER_DT')||':' ||l_pell_db_resp.PYMNT_PER_START_DATE);
1649
1650 -- UPDATE IN THE TABLE IGF_AW_DB_COD_DTLS , IGF_AW_DB_CHG_DTLS RESP_CODE,PYMNT_PER_START_DATE, PREV_SEQ_NUM
1651 l_tbh_disb.DISB_STATUS := l_pell_db_resp.RESP_CODE;
1652 l_tbh_disb.PYMNT_PRD_START_DATE := l_pell_db_resp.PYMNT_PER_START_DATE;
1653 --l_tbh_disb.PREV_SEQ_NUM := l_pell_db_resp.PREV_SEQ_NUM;
1654 --l_tbh_disb1.DISB_STATUS := l_pell_db_resp.RESP_CODE;
1655 l_tbh_disb1.PYMNT_PER_START_DATE := l_pell_db_resp.PYMNT_PER_START_DATE;
1656 --l_tbh_disb1.PREV_SEQ_NUM := l_pell_db_resp.PREV_SEQ_NUM;
1657
1658 UPDATE IGF_GR_DB_RESP_DTLS
1659 SET STATUS_CODE = 'P'
1660 WHERE DISB_RESP_ID = l_pell_db_resp.DISB_RESP_ID;
1661 update_flag := TRUE;
1662 END IF;
1663 ELSE -- implies disb_seq_num > 65
1664 /* Print Disbursement Amount, Disbursement Date, Previous Disbursement Sequence Number
1665 and print a message -(School would have to adjust the disbursement amount as per the COD generated adjustment)
1666 Update the status_code = 'P' in IGF_GR_DB_RESP_DTLS. */ --dont
1667 fnd_message.set_name('IGF','IGF_SL_COD_SCHL_ADJ');
1668 fnd_file.put_line(fnd_file.log,fnd_message.get);
1669 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_VAL_DB_AMT')||':' ||l_pell_db_resp.DISB_AMT);
1670 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_VAL_DB_DT')||':' ||l_pell_db_resp.DISB_DATE);
1671 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_VAL_PRV_SEQ_NUM')||':' ||l_pell_db_resp.PREV_SEQ_NUM);
1672
1673 UPDATE IGF_GR_DB_RESP_DTLS
1674 SET STATUS_CODE = 'P'
1675 WHERE DISB_RESP_ID = l_pell_db_resp.DISB_RESP_ID;
1676 update_flag := FALSE;
1677 END IF;
1678 END IF; -- for the rejected flag check
1679
1680 IF (update_flag) THEN
1681 igf_aw_db_chg_dtls_pkg.update_row(
1682 x_rowid => l_tbh_disb.row_id,
1683 x_award_id => l_tbh_disb.award_id,
1684 x_disb_num => l_tbh_disb.disb_num,
1685 x_disb_seq_num => l_tbh_disb.disb_seq_num,
1686 x_disb_accepted_amt => l_tbh_disb.disb_accepted_amt,
1687 x_orig_fee_amt => l_tbh_disb.orig_fee_amt,
1688 x_disb_net_amt => l_tbh_disb.disb_net_amt,
1689 x_disb_date => l_tbh_disb.disb_date,
1690 x_disb_activity => l_tbh_disb.disb_activity,
1691 x_disb_status => l_tbh_disb.disb_status,
1692 x_disb_status_date => l_tbh_disb.disb_status_date,
1693 x_disb_rel_flag => l_tbh_disb.disb_rel_flag,
1694 x_first_disb_flag => l_tbh_disb.first_disb_flag,
1695 x_interest_rebate_amt => l_tbh_disb.interest_rebate_amt,
1696 x_disb_conf_flag => l_tbh_disb.disb_conf_flag,
1697 x_pymnt_prd_start_date => l_tbh_disb.pymnt_prd_start_date,
1698 x_note_message => l_tbh_disb.note_message,
1699 x_batch_id_txt => l_tbh_disb.batch_id_txt,
1700 x_ack_date => l_tbh_disb.ack_date,
1701 x_booking_id_txt => l_tbh_disb.booking_id_txt,
1702 x_booking_date => l_tbh_disb.booking_date,
1703 x_mode => 'R'
1704 );
1705 igf_aw_db_cod_dtls_pkg.update_row(
1706 x_rowid => l_tbh_disb1.row_id ,
1707 x_award_id => l_tbh_disb1.award_id ,
1708 x_document_id_txt => l_tbh_disb1.document_id_txt ,
1709 x_disb_num => l_tbh_disb1.disb_num ,
1710 x_disb_seq_num => l_tbh_disb1.disb_seq_num ,
1711 x_disb_accepted_amt => l_tbh_disb1.disb_accepted_amt ,
1712 x_orig_fee_amt => l_tbh_disb1.orig_fee_amt ,
1713 x_disb_net_amt => l_tbh_disb1.disb_net_amt ,
1714 x_disb_date => l_tbh_disb1.disb_date ,
1715 x_disb_rel_flag => l_tbh_disb1.disb_rel_flag ,
1716 x_first_disb_flag => l_tbh_disb1.first_disb_flag ,
1717 x_interest_rebate_amt => l_tbh_disb1.interest_rebate_amt ,
1718 x_disb_conf_flag => l_tbh_disb1.disb_conf_flag ,
1719 x_pymnt_per_start_date => l_tbh_disb1.pymnt_per_start_date,
1720 x_note_message => l_tbh_disb1.note_message ,
1721 x_rep_entity_id_txt => l_tbh_disb1.rep_entity_id_txt ,
1722 x_atd_entity_id_txt => l_tbh_disb1.atd_entity_id_txt ,
1723 x_mode => 'R'
1724 );
1725 END IF;
1726 END LOOP; -- PELL DISBURSEMENT LOOP
1727 END IF; -- FOR THE RECORD EXISTS CHECK
1728 END LOOP; -- PELL AWARDS LOOP
1729
1730 EXCEPTION
1731 WHEN OTHERS THEN
1732 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
1733 fnd_message.set_token('NAME','igf_sl_upload_xml.process_pell_records');
1734 igs_ge_msg_stack.add;
1735 app_exception.raise_exception;
1736 END process_pell_records;
1737
1738 PROCEDURE main_response ( errbuf OUT NOCOPY VARCHAR2,
1739 retcode OUT NOCOPY NUMBER,
1740 p_document_id IN VARCHAR2
1741 )
1742 AS
1743 -----------------------------------------------------------------------------------
1744 --
1745 -- Created By : ugummall
1746 -- Date Created On : 2004/09/21
1747 -- Purpose : It uploads the data from the response tables and updates the system
1748 -- tables with the latest informtion on the COD
1749 -- Know limitations, enhancements or remarks
1750 -- Change History:
1751 -----------------------------------------------------------------------------------
1752 -- Who When What
1753 -- tsailaja 15/Jan/2006 Bug 4947880 Added invocation of igf_aw_gen.set_org_id(NULL);
1754 -----------------------------------------------------------------------------------
1755 l_chk_doc chk_doc%ROWTYPE;
1756
1757 CURSOR get_cr_resp ( cp_doc_id VARCHAR2) IS
1758 SELECT DOC_CREATED_DATE,
1759 SOURCE_ENTITY_ID_TXT,
1760 SOFT_PROVIDER,
1761 SOFT_VERSION,
1762 FULL_RESP_CODE,
1763 RECEIPT_DATE,
1764 DEST_ENTITY_ID_TXT,
1765 DOC_TYPE_CODE,
1766 DOC_STATUS_CODE,
1767 PROCESS_DATE
1768 FROM IGF_SL_CR_RESP_DTLS
1769 WHERE DOCUMENT_ID_TXT = cp_doc_id;
1770 l_get_cr_resp get_cr_resp%ROWTYPE;
1771
1772 CURSOR get_rs_resp ( cp_doc_id VARCHAR2) IS
1773 SELECT *
1774 FROM IGF_SL_RS_RESP_DTLS
1775 WHERE document_id_txt = cp_doc_id;
1776
1777 CURSOR get_fin_smry ( cp_rep_id NUMBER) IS
1778 SELECT *
1779 FROM IGF_SL_RESP_F_SMRY
1780 WHERE REP_SCHL_RESP_ID = cp_rep_id
1781 ORDER BY FIN_AWARD_YEAR,
1782 FIN_AWARD_TYPE;
1783
1784 CURSOR get_as_resp ( cp_rep_id NUMBER) IS
1785 SELECT *
1786 FROM IGF_SL_AS_RESP_DTLS
1787 WHERE REP_SCHL_RESP_ID = cp_rep_id;
1788
1789 CURSOR get_st_resp ( cp_atd_id NUMBER) IS
1790 SELECT *
1791 FROM IGF_SL_ST_RESP_DTLS
1792 WHERE ATD_SCHL_RESP_ID = cp_atd_id;
1793
1794 CURSOR get_cod_temp IS
1795 SELECT *
1796 FROM IGF_SL_COD_TEMP;
1797
1798 CURSOR get_cods_for_student ( cp_ssn VARCHAR2, cp_lname VARCHAR2, cp_dob DATE) IS
1799 SELECT codpell.ROWID row_id, codpell.*
1800 FROM IGF_GR_COD_DTLS codpell
1801 WHERE codpell.s_ssn = cp_ssn
1802 AND codpell.s_last_name = cp_lname
1803 AND codpell.s_date_of_birth = cp_dob;
1804
1805 CURSOR get_loans_for_student ( cp_ssn VARCHAR2, cp_lname VARCHAR2, cp_dob DATE) IS
1806 SELECT coddl.*
1807 FROM IGF_SL_LOR_LOC coddl
1808 WHERE coddl.s_ssn = cp_ssn
1809 AND coddl.s_last_name = cp_lname
1810 AND coddl.s_date_of_birth = cp_dob;
1811
1812 l_doc_id VARCHAR2(30);
1813 l_doc_rej_flg BOOLEAN;
1814 l_rs_rej_flg BOOLEAN;
1815 l_as_rej_flg BOOLEAN;
1816 l_st_rej_flg BOOLEAN;
1817 p_doc_id VARCHAR2(30);
1818
1819 changed_p_ssn VARCHAR2(30);
1820 changed_p_lname VARCHAR2(30);
1821 changed_p_dob DATE;
1822
1823 BEGIN
1824 igf_aw_gen.set_org_id(NULL);
1825 p_doc_id := p_document_id;
1826 l_doc_rej_flg := FALSE;
1827 l_rs_rej_flg := FALSE;
1828 l_as_rej_flg := FALSE;
1829 l_st_rej_flg := FALSE;
1830
1831 -- Print the parameters passed to the procedure
1832 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','DOC_ID')|| ':'||p_doc_id);
1833 fnd_file.put_line(fnd_file.log,'');
1834
1835 -- Check if the document is present or not.
1836 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1837 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main_response.debug','Checking document(' || p_doc_id || ') is present or not');
1838 END IF;
1839 l_doc_id := NULL;
1840 OPEN chk_doc(p_doc_id);
1841 FETCH chk_doc INTO l_chk_doc;
1842 CLOSE chk_doc;
1843 IF l_chk_doc.document_id_txt IS NULL THEN
1844 fnd_message.set_name('IGF','IGF_SL_COD_INV_DOCID');
1845 fnd_file.put_line(fnd_file.log,fnd_message.get);
1846 RETURN;
1847 END IF;
1848
1849 -- set the global doc id
1850 g_doc_id := p_doc_id;
1851
1852 -- take the data from the temp tables into the response tables
1853 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1854 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main_response.debug','Updating Response details from COD_TEMP table to RESP Tables');
1855 END IF;
1856 FOR l_temp IN get_cod_temp
1857 LOOP
1858 IF l_temp.LEVEL_CODE = 'CR' THEN
1859 UPDATE IGF_SL_CR_RESP_DTLS
1860 SET DOC_TYPE_CODE = l_temp.DOC_TYPE_CODE,
1861 DOC_STATUS_CODE = l_temp.DOC_STATUS_CODE,
1862 PROCESS_DATE = l_temp.PROCESS_DATE
1863 WHERE DOCUMENT_ID_TXT = l_temp.REC_ID;
1864 ELSIF l_temp.LEVEL_CODE = 'RS' THEN
1865 UPDATE IGF_SL_RS_RESP_DTLS
1866 SET RESP_CODE = l_temp.RESP_CODE
1867 WHERE REP_SCHL_RESP_ID = l_temp.REC_ID;
1868 ELSIF l_temp.LEVEL_CODE = 'AS' THEN
1869 UPDATE IGF_SL_AS_RESP_DTLS
1870 SET RESP_CODE = l_temp.RESP_CODE
1871 WHERE ATD_SCHL_RESP_ID = l_temp.REC_ID;
1872 ELSIF l_temp.LEVEL_CODE = 'ST' THEN
1873 UPDATE IGF_SL_ST_RESP_DTLS
1874 SET RESP_CODE = l_temp.RESP_CODE
1875 WHERE STDNT_RESP_ID = l_temp.REC_ID;
1876 ELSIF l_temp.LEVEL_CODE = 'AWD' THEN
1877 UPDATE IGF_SL_DL_RESP_DTLS
1878 SET RESP_CODE = l_temp.RESP_CODE,
1879 ELEC_MPN_FLAG = l_temp.ELEC_MPN_FLAG,
1880 PNOTE_MPN_ID = l_temp.PNOTE_MPN_ID,
1881 MPN_STATUS_CODE = l_temp.MPN_STATUS_CODE,
1882 MPN_LINK_FLAG = l_temp.MPN_LINK_FLAG,
1883 PYMT_SERVICER_AMT = l_temp.PYMT_SERVICER_AMT,
1884 PYMT_SERVICER_DATE = l_temp.PYMT_SERVICER_DATE,
1885 BOOK_LOAN_AMT = l_temp.BOOK_LOAN_AMT,
1886 BOOK_LOAN_AMT_DATE = l_temp.BOOK_LOAN_AMT_DATE,
1887 ENDORSER_AMT = l_temp.ENDORSER_AMT,
1888 CRDT_DECISION_STATUS = l_temp.CRDT_DECISION_STATUS,
1889 CRDT_DECISION_DATE = l_temp.CRDT_DECISION_DATE,
1890 CRDT_DECISION_OVRD_CODE = l_temp.CRDT_DECISION_OVRD_CODE
1891 WHERE DL_LOAN_RESP_ID = l_temp.REC_ID;
1892 ELSIF l_temp.LEVEL_CODE = 'BORR' THEN
1893 UPDATE IGF_SL_DL_RESP_DTLS
1894 SET B_RESP_CODE = l_temp.RESP_CODE
1895 WHERE DL_LOAN_RESP_ID = l_temp.REC_ID;
1896 ELSIF l_temp.LEVEL_CODE = 'DL_DB' THEN
1897 UPDATE IGF_SL_DLDB_RSP_DTL
1898 SET RESP_CODE = l_temp.RESP_CODE,
1899 PREV_SEQ_NUM = l_temp.PREV_SEQ_NUM
1900 WHERE DISB_RESP_ID = l_temp.REC_ID;
1901 ELSIF l_temp.LEVEL_CODE = 'PELL' THEN
1902 UPDATE IGF_GR_RESP_DTLS
1903 SET RESP_CODE = l_temp.RESP_CODE,
1904 YTD_DISB_AMT = l_temp.YTD_DISB_AMT,
1905 TOT_ELIG_USED = l_temp.TOT_ELIG_USED,
1906 SCHD_PELL_AMT = l_temp.SCHD_PELL_AMT,
1907 NEG_PEND_AMT = l_temp.NEG_PEND_AMT,
1908 FSA_CODE_1 = l_temp.FSA_CODE_1,
1909 FSA_CODE_2 = l_temp.FSA_CODE_2,
1910 FSA_CODE_3 = l_temp.FSA_CODE_3,
1911 CPS_VERIF_FLAG = l_temp.CPS_VERIF_FLAG,
1912 HIGH_CPS_TRANS_NUM = l_temp.HIGH_CPS_TRANS_NUM
1913 WHERE PELL_RESP_ID = l_temp.REC_ID;
1914 ELSIF l_temp.LEVEL_CODE = 'PELL_DB' THEN
1915 UPDATE IGF_GR_DB_RESP_DTLS
1916 SET RESP_CODE = l_temp.RESP_CODE,
1917 PREV_SEQ_NUM = l_temp.PREV_SEQ_NUM
1918 WHERE DISB_RESP_ID = l_temp.REC_ID;
1919 ELSIF l_temp.LEVEL_CODE = 'DL_INFO' THEN
1920 UPDATE IGF_SL_DI_RESP_DTLS
1921 SET RESP_CODE = l_temp.RESP_CODE
1922 WHERE DL_INFO_ID = l_temp.REC_ID;
1923 END IF;
1924 END LOOP;
1925
1926 -- Commit above changes.
1927 -- Here commit is needed as we may need to remove data from resp tables in seperate transaction
1928 -- at which locks on above updated rows should be available.
1929 COMMIT;
1930
1931 -- Open the cursor for the main CommonRecord Details
1932 l_get_cr_resp := NULL;
1933 OPEN get_cr_resp(p_doc_id);
1934 FETCH get_cr_resp INTO l_get_cr_resp;
1935 CLOSE get_cr_resp;
1936
1937 -- print CommonRecord values in log file
1938 g_process_date := l_get_cr_resp.PROCESS_DATE;
1939 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','CR_RESP_DTLS'));
1940 fnd_file.put_line(fnd_file.log,'');
1941 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','DOC_CR_DT')|| ':'||l_get_cr_resp.DOC_CREATED_DATE);
1942 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','SRC_ENT_ID')|| ':'||l_get_cr_resp.SOURCE_ENTITY_ID_TXT);
1943 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','DST_ENT_ID')|| ':'||l_get_cr_resp.DEST_ENTITY_ID_TXT);
1944 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','SFT_PRVD')|| ':'||l_get_cr_resp.SOFT_PROVIDER);
1945 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','SFT_VER')|| ':'||l_get_cr_resp.SOFT_VERSION);
1946 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','FULL_RESP_CD')|| ':'||l_get_cr_resp.FULL_RESP_CODE);
1947 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RECP_DATE')|| ':'||l_get_cr_resp.RECEIPT_DATE);
1948 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','DOC_TYPE')|| ':'||l_get_cr_resp.DOC_TYPE_CODE);
1949 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','DOC_STATUS')|| ':'||l_get_cr_resp.DOC_STATUS_CODE);
1950 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','PROC_DATE')|| ':'||l_get_cr_resp.PROCESS_DATE);
1951
1952 -- Check if the document is receipt document or not. If it is, then
1953 -- log message and return. Do not process if it is receipt document.
1954 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1955 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main_response.debug','RECEIPT_DATE is ' || l_get_cr_resp.RECEIPT_DATE);
1956 END IF;
1957 IF l_get_cr_resp.RECEIPT_DATE IS NOT NULL THEN
1958 fnd_file.put_line(fnd_file.log,'receipt date is not null');
1959 fnd_message.set_name('IGF','IGF_SL_NOT_PRC_RECEIPT_DOC');
1960 fnd_file.put_line(fnd_file.log,fnd_message.get);
1961 -- delete data from resp tables as well as from temp table(IGF_SL_COD_TEMP)
1962 rollback_resp_tables();
1963 RETURN;
1964 END IF;
1965
1966 -- Check if destination school in XML file is present in our system or not.
1967 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
1968 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main_response.debug','Checking destination entity id(' || l_get_cr_resp.dest_entity_id_txt || ')');
1969 END IF;
1970 IF NOT check_entityid(l_get_cr_resp.DEST_ENTITY_ID_TXT) THEN
1971 fnd_message.set_name('IGF','IGF_SL_COD_INV_DEST_ID');
1972 fnd_message.set_token('DEST_ENTITY_ID',l_get_cr_resp.DEST_ENTITY_ID_TXT);
1973 fnd_file.put_line(fnd_file.log,fnd_message.get);
1974
1975 -- update the IGF_SL_COD_DOC_DTLS table with the status = 'E'
1976 update_xml_document(l_chk_doc, 'E');
1977 -- delete data from resp tables as well as from temp table(IGF_SL_COD_TEMP)
1978 rollback_resp_tables();
1979 RETURN;
1980 END IF;
1981
1982 -- Rest of the processing is to be done if this is not a receipt document.
1983 IF l_get_cr_resp.RECEIPT_DATE IS NULL THEN
1984 IF l_get_cr_resp.FULL_RESP_CODE NOT IN ('F','S','M','N') THEN
1985 fnd_message.set_name('IGF','IGF_SL_COD_INV_RES_CODE');
1986 fnd_file.put_line(fnd_file.log,fnd_message.get);
1987
1988 -- update the IGF_SL_COD_DOC_DTLS table with the status = 'F';
1989 update_xml_document(l_chk_doc, 'F');
1990 -- delete data from resp tables as well as from temp table(IGF_SL_COD_TEMP)
1991 rollback_resp_tables();
1992 RETURN;
1993 END IF;
1994
1995 -- Here onwards, Data from response tables should not be deleted.
1996 -- Hence removing data from temp table(IGF_SL_COD_TEMP) as temp table data
1997 -- is not needed to remove data from resp tables
1998 delete_temp_table_data();
1999
2000 -- print the edit results at the CommonRecord Level
2001 print_edits(p_doc_id,'CR');
2002
2003 l_doc_rej_flg := FALSE;
2004 IF l_get_cr_resp.DOC_STATUS_CODE = 'R' THEN
2005 l_doc_rej_flg := TRUE;
2006
2007 -- update the IGF_SL_COD_DOC_DTLS table with the status = 'J';
2008 -- do not return from here as there would be child level rejects
2009 igf_sl_cod_doc_dtls_pkg.update_row (
2010 x_rowid => l_chk_doc.row_id,
2011 x_document_id_txt => l_chk_doc.document_id_txt,
2012 x_outbound_doc => l_chk_doc.outbound_doc ,
2013 x_inbound_doc => l_chk_doc.inbound_doc ,
2014 x_send_date => l_chk_doc.send_date ,
2015 x_ack_date => l_chk_doc.ack_date ,
2016 x_doc_status => 'J' ,
2017 x_doc_type => l_chk_doc.doc_type ,
2018 x_full_resp_code => l_chk_doc.full_resp_code ,
2019 x_mode => 'R'
2020 );
2021 END IF;
2022
2023 -- start the processing of the Reporting School Tags
2024 FOR l_rs_resp IN get_rs_resp(p_doc_id)
2025 LOOP
2026 l_rs_rej_flg := FALSE;
2027 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RS_RESP_DTLS'));
2028 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RS_ENT_ID')|| ':'|| l_rs_resp.REP_ENTITY_ID_TXT);
2029 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_ST')|| ':'|| l_rs_resp.RESP_CODE);
2030 fnd_file.put_line(fnd_file.OUTPUT,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RS_RESP_DTLS'));
2031 fnd_file.put_line(fnd_file.OUTPUT,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RS_ENT_ID')|| ':'|| l_rs_resp.REP_ENTITY_ID_TXT);
2032 fnd_file.put_line(fnd_file.OUTPUT,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_ST')|| ':'|| l_rs_resp.RESP_CODE);
2033
2034 IF NOT check_entityid(l_rs_resp.REP_ENTITY_ID_TXT) THEN
2035 fnd_message.set_name('IGF','IGF_SL_COD_INV_REPENTITY_ID');
2036 fnd_message.set_token('REPT_ENTITY_ID',l_rs_resp.REP_ENTITY_ID_TXT);
2037 fnd_file.put_line(fnd_file.log,fnd_message.get);
2038 -- skip the processing for this reporting school
2039 ELSE
2040 print_edits(l_rs_resp.REP_SCHL_RESP_ID,'RS');
2041
2042 IF l_doc_rej_flg = TRUE OR l_rs_resp.RESP_CODE = 'R' THEN
2043 l_rs_rej_flg := TRUE;
2044 END IF;
2045
2046 -- print the fin summary data from the response file
2047 FOR l_fin_smry IN get_fin_smry(l_rs_resp.REP_SCHL_RESP_ID)
2048 LOOP
2049 fnd_file.put_line(fnd_file.OUTPUT,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','FIN_AWD_YR')|| ':'|| l_fin_smry.FIN_AWARD_YEAR);
2050 fnd_file.put_line(fnd_file.OUTPUT,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','FIN_AWD_TYP')|| ':'|| l_fin_smry.FIN_AWARD_TYPE);
2051 fnd_file.put_line(fnd_file.OUTPUT,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_ST_TAG')|| ':'|| l_fin_smry.TOT_CNT_NUM);
2052 fnd_file.put_line(fnd_file.OUTPUT,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_ACPT_AWD')|| ':'|| l_fin_smry.TOT_CNT_ACPT_NUM);
2053 fnd_file.put_line(fnd_file.OUTPUT,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_REJ_AWD')|| ':'|| l_fin_smry.TOT_CNT_REJ_NUM);
2054 fnd_file.put_line(fnd_file.OUTPUT,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_DUP_AWD')|| ':'|| l_fin_smry.TOT_CNT_DUP_NUM);
2055 fnd_file.put_line(fnd_file.OUTPUT,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_COR_AWD')|| ':'|| l_fin_smry.TOT_CNT_CORR_NUM);
2056 fnd_file.put_line(fnd_file.OUTPUT,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_SEL_VERIF')|| ':'|| l_fin_smry.TOT_CNT_VER_SLCTD_NUM);
2057 fnd_file.put_line(fnd_file.OUTPUT,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_SSADMIN')|| ':'|| l_fin_smry.TOT_CNT_SSADMIN_NUM);
2058 fnd_file.put_line(fnd_file.OUTPUT,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_REP_AWD')|| ':'|| l_fin_smry.TOT_REP_AWD_AMT);
2059 fnd_file.put_line(fnd_file.OUTPUT,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_REP_DB_AMT')|| ':'|| l_fin_smry.TOT_REP_DISB_AMT);
2060 fnd_file.put_line(fnd_file.OUTPUT,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_ACPT_AMT')|| ':'|| l_fin_smry.TOT_FIN_AWD_ACPT_AMT);
2061 fnd_file.put_line(fnd_file.OUTPUT,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_DB_ACPT_AMT')|| ':'|| l_fin_smry.TOT_FIN_DISB_ACPT_AMT);
2062 fnd_file.put_line(fnd_file.OUTPUT,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_FUND_DB_AMT')|| ':'|| l_fin_smry.TOT_FUND_DISB_ACPT_AMT);
2063 fnd_file.put_line(fnd_file.OUTPUT,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_NONFUND_DB_AMT')|| ':'|| l_fin_smry.TOT_NONFUND_DISB_ACPT_AMT);
2064 fnd_file.put_line(fnd_file.OUTPUT,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_AWD_CORR_AMT')|| ':'|| l_fin_smry.TOT_FIN_AWD_CORR_AMT);
2065
2066 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','FIN_AWD_YR')|| ':'|| l_fin_smry.FIN_AWARD_YEAR);
2067 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','FIN_AWD_TYP')|| ':'|| l_fin_smry.FIN_AWARD_TYPE);
2068 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_ST_TAG')|| ':'|| l_fin_smry.TOT_CNT_NUM);
2069 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_ACPT_AWD')|| ':'|| l_fin_smry.TOT_CNT_ACPT_NUM);
2070 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_REJ_AWD')|| ':'|| l_fin_smry.TOT_CNT_REJ_NUM);
2071 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_DUP_AWD')|| ':'|| l_fin_smry.TOT_CNT_DUP_NUM);
2072 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_COR_AWD')|| ':'|| l_fin_smry.TOT_CNT_CORR_NUM);
2073 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_SEL_VERIF')|| ':'|| l_fin_smry.TOT_CNT_VER_SLCTD_NUM);
2074 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_SSADMIN')|| ':'|| l_fin_smry.TOT_CNT_SSADMIN_NUM);
2075 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_REP_AWD')|| ':'|| l_fin_smry.TOT_REP_AWD_AMT);
2076 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_REP_DB_AMT')|| ':'|| l_fin_smry.TOT_REP_DISB_AMT);
2077 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_ACPT_AMT')|| ':'|| l_fin_smry.TOT_FIN_AWD_ACPT_AMT);
2078 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_DB_ACPT_AMT')|| ':'|| l_fin_smry.TOT_FIN_DISB_ACPT_AMT);
2079 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_FUND_DB_AMT')|| ':'|| l_fin_smry.TOT_FUND_DISB_ACPT_AMT);
2080 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_NONFUND_DB_AMT')|| ':'|| l_fin_smry.TOT_NONFUND_DISB_ACPT_AMT);
2081 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','TOT_AWD_CORR_AMT')|| ':'|| l_fin_smry.TOT_FIN_AWD_CORR_AMT);
2082 END LOOP;
2083
2084 -- start the processing of the Attending School Tags
2085 FOR l_as_resp IN get_as_resp(l_rs_resp.REP_SCHL_RESP_ID)
2086 LOOP
2087 l_as_rej_flg := FALSE;
2088 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','AS_RESP_DTLS'));
2089 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','AS_ENT_ID')|| ':'|| l_as_resp.ATD_ENTITY_ID_TXT);
2090 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_ST')|| ':'|| l_as_resp.RESP_CODE);
2091 fnd_file.put_line(fnd_file.OUTPUT,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','AS_RESP_DTLS'));
2092 fnd_file.put_line(fnd_file.OUTPUT,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','AS_ENT_ID')|| ':'|| l_as_resp.ATD_ENTITY_ID_TXT);
2093 fnd_file.put_line(fnd_file.OUTPUT,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_ST')|| ':'|| l_as_resp.RESP_CODE);
2094
2095 IF NOT check_entityid(l_as_resp.ATD_ENTITY_ID_TXT) THEN
2096 fnd_message.set_name('IGF','IGF_SL_COD_INV_ATDENTITY_ID');
2097 fnd_message.set_token('ATTD_ENTITY_ID',l_as_resp.ATD_ENTITY_ID_TXT);
2098 fnd_file.put_line(fnd_file.log,fnd_message.get);
2099 -- skip the processing for this attending school
2100 ELSE
2101 print_edits(l_as_resp.ATD_SCHL_RESP_ID,'AS');
2102
2103 IF l_rs_rej_flg = TRUE OR l_as_resp.RESP_CODE = 'R' THEN
2104 l_as_rej_flg := TRUE;
2105 END IF;
2106
2107 -- start the processing of the Student Tags
2108 FOR l_st_resp IN get_st_resp(l_as_resp.ATD_SCHL_RESP_ID)
2109 LOOP
2110 l_st_rej_flg := FALSE;
2111 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','ST_RESP_DTLS'));
2112 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','ST_SSN')|| ':'|| l_st_resp.S_SSN);
2113 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','ST_LNAME')|| ':'|| l_st_resp.S_LAST_NAME);
2114 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','ST_DOB')|| ':'|| l_st_resp.S_DATE_OF_BIRTH);
2115 fnd_file.put_line(fnd_file.log,igf_aw_gen.lookup_desc('IGF_SL_COD_XML_TAGS','RESP_ST')|| ':'|| l_st_resp.RESP_CODE);
2116
2117 print_edits(l_st_resp.STDNT_RESP_ID,'ST');
2118
2119 IF l_as_rej_flg = TRUE OR l_st_resp.RESP_CODE = 'R' THEN
2120 l_st_rej_flg := TRUE;
2121 END IF;
2122
2123 changed_p_dob := l_st_resp.S_DATE_OF_BIRTH;
2124 changed_p_ssn := l_st_resp.S_SSN;
2125 changed_p_lname := l_st_resp.S_LAST_NAME;
2126
2127 IF l_st_resp.RESP_CODE = 'A' THEN
2128 -- Get changed(new) student identifier information, if present.
2129 changed_p_dob := NVL(l_st_resp.S_CHG_DATE_OF_BIRTH, l_st_resp.S_DATE_OF_BIRTH);
2130 changed_p_ssn := NVL(l_st_resp.S_CHG_SSN, l_st_resp.S_SSN);
2131 changed_p_lname := NVL(l_st_resp.S_CHG_LAST_NAME, l_st_resp.S_LAST_NAME);
2132
2133 -- If new student identifier info is different from old student identifier info then
2134 -- local COD tables should be reflected with COD info.
2135 -- That is for all records in local COD tables for that student, update with new student identifier info.
2136 IF changed_p_dob <> l_st_resp.S_DATE_OF_BIRTH OR changed_p_ssn <> l_st_resp.S_SSN OR changed_p_lname <> l_st_resp.S_LAST_NAME THEN
2137 -- Update student pell records with new student identifier info.
2138 -- Student will have only one Pell award, but still used For loop for consistency purpose.
2139 FOR rec IN get_cods_for_student(l_st_resp.s_ssn,l_st_resp.s_last_name,l_st_resp.s_date_of_birth)
2140 LOOP
2141 igf_gr_cod_dtls_pkg.update_row(
2142 x_rowid => rec.row_id,
2143 x_origination_id => rec.origination_id,
2144 x_award_id => rec.award_id,
2145 x_document_id_txt => rec.document_id_txt,
2146 x_base_id => rec.base_id,
2147 x_fin_award_year => rec.fin_award_year,
2148 x_cps_trans_num => rec.cps_trans_num,
2149 x_award_amt => rec.award_amt,
2150 x_coa_amt => rec.coa_amt,
2151 x_low_tution_fee => rec.low_tution_fee,
2152 x_incarc_flag => rec.incarc_flag,
2153 x_ver_status_code => rec.ver_status_code,
2154 x_enrollment_date => rec.enrollment_date,
2155 x_sec_efc_code => rec.sec_efc_code,
2156 x_ytd_disb_amt => rec.ytd_disb_amt,
2157 x_tot_elig_used => rec.tot_elig_used,
2158 x_schd_pell_amt => rec.schd_pell_amt,
2159 x_neg_pend_amt => rec.neg_pend_amt,
2160 x_cps_verif_flag => rec.cps_verif_flag,
2161 x_high_cps_trans_num => rec.high_cps_trans_num,
2162 x_note_message => rec.note_message,
2163 x_full_resp_code => rec.full_resp_code,
2164 x_atd_entity_id_txt => rec.atd_entity_id_txt,
2165 x_rep_entity_id_txt => rec.rep_entity_id_txt,
2166 x_source_entity_id_txt => rec.source_entity_id_txt,
2167 x_pell_status => rec.pell_status,
2168 x_pell_status_date => rec.pell_status_date,
2169 x_s_ssn => changed_p_ssn,
2170 x_driver_lic_state => rec.driver_lic_state,
2171 x_driver_lic_number => rec.driver_lic_number,
2172 x_s_date_of_birth => changed_p_dob,
2173 x_first_name => UPPER(rec.first_name),
2174 x_middle_name => UPPER(rec.middle_name),
2175 x_s_last_name => changed_p_lname,
2176 x_s_chg_date_of_birth => NULL,
2177 x_s_chg_ssn => NULL,
2178 x_s_chg_last_name => NULL,
2179 x_permt_addr_foreign_flag => NULL,
2180 x_addr_type_code => NULL,
2181 x_permt_addr_line_1 => UPPER(rec.permt_addr_line_1),
2182 x_permt_addr_line_2 => UPPER(rec.permt_addr_line_2),
2183 x_permt_addr_line_3 => UPPER(rec.permt_addr_line_3),
2184 x_permt_addr_city => UPPER(rec.permt_addr_city),
2185 x_permt_addr_state_code => UPPER(rec.permt_addr_state_code),
2186 x_permt_addr_post_code => UPPER(rec.permt_addr_post_code),
2187 x_permt_addr_county => UPPER(rec.permt_addr_county),
2188 x_permt_addr_country => UPPER(rec.permt_addr_country),
2189 x_phone_number_1 => rec.phone_number_1,
2190 x_phone_number_2 => NULL,
2191 x_phone_number_3 => NULL,
2192 x_email_address => UPPER(rec.email_address),
2193 x_citzn_status_code => rec.citzn_status_code,
2194 x_mode => 'R'
2195 );
2196 END LOOP;
2197
2198 -- Update student loan records with new student identifier info.
2199 FOR rec IN get_loans_for_student(l_st_resp.s_ssn,l_st_resp.s_last_name,l_st_resp.s_date_of_birth)
2200 LOOP
2201 igf_sl_lor_loc_pkg.update_row (
2202 x_mode => 'R',
2203 x_rowid => rec.row_id,
2204 x_loan_id => rec.loan_id,
2205 x_origination_id => rec.origination_id,
2206 x_loan_number => rec.loan_number,
2207 x_loan_type => rec.loan_type,
2208 x_loan_amt_offered => rec.loan_amt_offered,
2209 x_loan_amt_accepted => rec.loan_amt_accepted,
2210 x_loan_per_begin_date => rec.loan_per_begin_date,
2211 x_loan_per_end_date => rec.loan_per_end_date,
2212 x_acad_yr_begin_date => rec.acad_yr_begin_date,
2213 x_acad_yr_end_date => rec.acad_yr_end_date,
2214 x_loan_status => rec.loan_status,
2215 x_loan_status_date => rec.loan_status_date,
2216 x_loan_chg_status => rec.loan_chg_status,
2217 x_loan_chg_status_date => rec.loan_chg_status_date,
2218 x_req_serial_loan_code => rec.req_serial_loan_code,
2219 x_act_serial_loan_code => rec.act_serial_loan_code,
2220 x_active => rec.active,
2221 x_active_date => rec.active_date,
2222 x_sch_cert_date => rec.sch_cert_date,
2223 x_orig_status_flag => rec.orig_status_flag,
2224 x_orig_batch_id => rec.orig_batch_id,
2225 x_orig_batch_date => rec.orig_batch_date,
2226 x_chg_batch_id => NULL,
2227 x_orig_ack_date => rec.orig_ack_date,
2228 x_credit_override => rec.credit_override,
2229 x_credit_decision_date => rec.credit_decision_date,
2230 x_pnote_delivery_code => rec.pnote_delivery_code,
2231 x_pnote_status => rec.pnote_status,
2232 x_pnote_status_date => rec.pnote_status_date,
2233 x_pnote_id => rec.pnote_id,
2234 x_pnote_print_ind => rec.pnote_print_ind,
2235 x_pnote_accept_amt => rec.pnote_accept_amt,
2236 x_pnote_accept_date => rec.pnote_accept_date,
2237 x_p_signature_code => rec.p_signature_code,
2238 x_p_signature_date => rec.p_signature_date,
2239 x_s_signature_code => rec.s_signature_code,
2240 x_unsub_elig_for_heal => rec.unsub_elig_for_heal,
2241 x_disclosure_print_ind => rec.disclosure_print_ind,
2242 x_orig_fee_perct => rec.orig_fee_perct,
2243 x_borw_confirm_ind => rec.borw_confirm_ind,
2244 x_borw_interest_ind => rec.borw_interest_ind,
2245 x_unsub_elig_for_depnt => rec.unsub_elig_for_depnt,
2246 x_guarantee_amt => rec.guarantee_amt,
2247 x_guarantee_date => rec.guarantee_date,
2248 x_guarnt_adj_ind => rec.guarnt_adj_ind,
2249 x_guarnt_amt_redn_code => rec.guarnt_amt_redn_code,
2250 x_guarnt_status_code => rec.guarnt_status_code,
2251 x_guarnt_status_date => rec.guarnt_status_date,
2252 x_lend_apprv_denied_code => NULL,
2253 x_lend_apprv_denied_date => NULL,
2254 x_lend_status_code => rec.lend_status_code,
2255 x_lend_status_date => rec.lend_status_date,
2256 x_grade_level_code => rec.grade_level_code,
2257 x_enrollment_code => rec.enrollment_code,
2258 x_anticip_compl_date => rec.anticip_compl_date,
2259 x_borw_lender_id => rec.borw_lender_id,
2260 x_duns_borw_lender_id => NULL,
2261 x_guarantor_id => rec.guarantor_id,
2262 x_duns_guarnt_id => NULL,
2263 x_prc_type_code => rec.prc_type_code,
2264 x_rec_type_ind => rec.rec_type_ind,
2265 x_cl_loan_type => rec.cl_loan_type,
2266 x_cl_seq_number => rec.cl_seq_number,
2267 x_last_resort_lender => rec.last_resort_lender,
2268 x_lender_id => rec.lender_id,
2269 x_duns_lender_id => NULL,
2270 x_lend_non_ed_brc_id => rec.lend_non_ed_brc_id,
2271 x_recipient_id => rec.recipient_id,
2272 x_recipient_type => rec.recipient_type,
2273 x_duns_recip_id => NULL,
2274 x_recip_non_ed_brc_id => rec.recip_non_ed_brc_id,
2275 x_cl_rec_status => NULL,
2276 x_cl_rec_status_last_update => NULL,
2277 x_alt_prog_type_code => rec.alt_prog_type_code,
2278 x_alt_appl_ver_code => rec.alt_appl_ver_code,
2279 x_borw_outstd_loan_code => rec.borw_outstd_loan_code,
2280 x_mpn_confirm_code => NULL,
2281 x_resp_to_orig_code => rec.resp_to_orig_code,
2282 x_appl_loan_phase_code => NULL,
2283 x_appl_loan_phase_code_chg => NULL,
2284 x_tot_outstd_stafford => rec.tot_outstd_stafford,
2285 x_tot_outstd_plus => rec.tot_outstd_plus,
2286 x_alt_borw_tot_debt => rec.alt_borw_tot_debt,
2287 x_act_interest_rate => rec.act_interest_rate,
2288 x_service_type_code => rec.service_type_code,
2289 x_rev_notice_of_guarnt => rec.rev_notice_of_guarnt,
2290 x_sch_refund_amt => rec.sch_refund_amt,
2291 x_sch_refund_date => rec.sch_refund_date,
2292 x_uniq_layout_vend_code => rec.uniq_layout_vend_code,
2293 x_uniq_layout_ident_code => rec.uniq_layout_ident_code,
2294 x_p_person_id => rec.p_person_id,
2295 x_p_ssn => rec.p_ssn,
2296 x_p_ssn_chg_date => NULL,
2297 x_p_last_name => rec.p_last_name,
2298 x_p_first_name => rec.p_first_name,
2299 x_p_middle_name => rec.p_middle_name,
2300 x_p_permt_addr1 => rec.p_permt_addr1,
2301 x_p_permt_addr2 => rec.p_permt_addr2,
2302 x_p_permt_city => rec.p_permt_city,
2303 x_p_permt_state => rec.p_permt_state,
2304 x_p_permt_zip => rec.p_permt_zip,
2305 x_p_permt_addr_chg_date => rec.p_permt_addr_chg_date,
2306 x_p_permt_phone => rec.p_permt_phone,
2307 x_p_email_addr => rec.p_email_addr,
2308 x_p_date_of_birth => rec.p_date_of_birth,
2309 x_p_dob_chg_date => NULL,
2310 x_p_license_num => rec.p_license_num,
2311 x_p_license_state => rec.p_license_state,
2312 x_p_citizenship_status => rec.p_citizenship_status,
2313 x_p_alien_reg_num => rec.p_alien_reg_num,
2314 x_p_default_status => rec.p_default_status,
2315 x_p_foreign_postal_code => rec.p_foreign_postal_code,
2316 x_p_state_of_legal_res => rec.p_state_of_legal_res,
2317 x_p_legal_res_date => rec.p_legal_res_date,
2318 x_s_ssn => changed_p_ssn,
2319 x_s_ssn_chg_date => NULL,
2320 x_s_last_name => changed_p_lname,
2321 x_s_first_name => rec.s_first_name,
2322 x_s_middle_name => rec.s_middle_name,
2323 x_s_permt_addr1 => rec.s_permt_addr1,
2324 x_s_permt_addr2 => rec.s_permt_addr2,
2325 x_s_permt_city => rec.s_permt_city,
2326 x_s_permt_state => rec.s_permt_state,
2327 x_s_permt_zip => rec.s_permt_zip,
2328 x_s_permt_addr_chg_date => rec.s_permt_addr_chg_date,
2329 x_s_permt_phone => rec.s_permt_phone,
2330 x_s_local_addr1 => rec.s_local_addr1,
2331 x_s_local_addr2 => rec.s_local_addr2,
2332 x_s_local_city => rec.s_local_city,
2333 x_s_local_state => rec.s_local_state,
2334 x_s_local_zip => rec.s_local_zip,
2335 x_s_local_addr_chg_date => NULL,
2336 x_s_email_addr => rec.s_email_addr,
2337 x_s_date_of_birth => changed_p_dob,
2338 x_s_dob_chg_date => NULL,
2339 x_s_license_num => rec.s_license_num,
2340 x_s_license_state => rec.s_license_state,
2341 x_s_depncy_status => rec.s_depncy_status,
2342 x_s_default_status => rec.s_default_status,
2343 x_s_citizenship_status => rec.s_citizenship_status,
2344 x_s_alien_reg_num => rec.s_alien_reg_num,
2345 x_s_foreign_postal_code => rec.s_foreign_postal_code,
2346 x_pnote_batch_id => rec.pnote_batch_id,
2347 x_pnote_ack_date => rec.pnote_ack_date,
2348 x_pnote_mpn_ind => rec.pnote_mpn_ind,
2349 x_award_id => rec.award_id ,
2350 x_base_id => rec.base_id ,
2351 x_document_id_txt => rec.document_id_txt ,
2352 x_loan_key_num => rec.loan_key_num ,
2353 x_INTEREST_REBATE_PERCENT_NUM => rec.INTEREST_REBATE_PERCENT_NUM,
2354 x_fin_award_year => rec.fin_award_year ,
2355 x_cps_trans_num => rec.cps_trans_num ,
2356 x_ATD_ENTITY_ID_TXT => rec.ATD_ENTITY_ID_TXT,
2357 x_REP_ENTITY_ID_TXT => rec.REP_ENTITY_ID_TXT,
2358 x_SOURCE_ENTITY_ID_TXT => rec.SOURCE_ENTITY_ID_TXT,
2359 x_pymt_servicer_amt => rec.pymt_servicer_amt ,
2360 x_pymt_servicer_date => rec.pymt_servicer_date ,
2361 x_book_loan_amt => rec.book_loan_amt ,
2362 x_book_loan_amt_date => rec.book_loan_amt_date ,
2363 x_s_chg_birth_date => NULL,
2364 x_s_chg_ssn => NULL,
2365 x_s_chg_last_name => NULL,
2366 x_b_chg_birth_date => rec.b_chg_birth_date ,
2367 x_b_chg_ssn => rec.b_chg_ssn ,
2368 x_b_chg_last_name => rec.b_chg_last_name ,
2369 x_note_message => rec.note_message ,
2370 x_full_resp_code => rec.full_resp_code ,
2371 x_s_permt_county => rec.s_permt_county ,
2372 x_b_permt_county => rec.b_permt_county ,
2373 x_s_permt_country => rec.s_permt_country ,
2374 x_b_permt_country => rec.b_permt_country ,
2375 x_crdt_decision_status => rec.crdt_decision_status,
2376 x_actual_record_type_code => rec.actual_record_type_code,
2377 x_alt_approved_amt => rec.alt_approved_amt,
2378 x_alt_borrower_ind_flag => rec.alt_borrower_ind_flag,
2379 x_borower_credit_authoriz_flag => rec.borower_credit_authoriz_flag,
2380 x_borower_electronic_sign_flag => rec.borower_electronic_sign_flag,
2381 x_cost_of_attendance_amt => rec.cost_of_attendance_amt,
2382 x_deferment_request_code => rec.deferment_request_code,
2383 x_eft_authorization_code => rec.eft_authorization_code,
2384 x_established_fin_aid_amount => rec.established_fin_aid_amount,
2385 x_expect_family_contribute_amt => rec.expect_family_contribute_amt,
2386 x_external_loan_id_txt => rec.external_loan_id_txt,
2387 x_flp_approved_amt => rec.flp_approved_amt,
2388 x_fls_approved_amt => rec.fls_approved_amt,
2389 x_flu_approved_amt => rec.flu_approved_amt,
2390 x_guarantor_use_txt => rec.guarantor_use_txt,
2391 x_lender_use_txt => rec.lender_use_txt,
2392 x_loan_app_form_code => rec.loan_app_form_code,
2393 x_mpn_type_flag => rec.mpn_type_flag,
2394 x_reinstatement_amt => rec.reinstatement_amt,
2395 x_requested_loan_amt => rec.requested_loan_amt,
2396 x_school_id_txt => rec.school_id_txt,
2397 x_school_use_txt => rec.school_use_txt,
2398 x_student_electronic_sign_flag => rec.student_electronic_sign_flag,
2399 x_esign_src_typ_cd => rec.esign_src_typ_cd
2400 );
2401 END LOOP;
2402 END IF; -- student identifier differ
2403 END IF; -- student is accepted.
2404
2405 -- start the processing based on the document type
2406 IF l_chk_doc.doc_type IN ('COD','DL') THEN
2407 IF l_get_cr_resp.DOC_TYPE_CODE = 'ND' THEN
2408 process_pell_records(l_st_resp.STDNT_RESP_ID, changed_p_ssn, changed_p_lname, changed_p_dob, FALSE);
2409 ELSE
2410 process_dl_records(l_st_resp.STDNT_RESP_ID,l_st_rej_flg,l_get_cr_resp.DOC_TYPE_CODE);
2411 END IF;
2412 END IF;
2413 IF l_chk_doc.doc_type = 'PELL' THEN
2414 process_pell_records(l_st_resp.STDNT_RESP_ID, changed_p_ssn, changed_p_lname, changed_p_dob, l_st_rej_flg);
2415 END IF;
2416 END LOOP; -- FOR THE STUDENT CURSOR
2417 END IF; -- FOR THE ATTENDING SCHOOL ENTITY ID
2418 END LOOP; -- FOR THE ATTENDING SCHOOL CURSOR
2419 END IF; -- REPORTING SCHOOL ENTITY ID
2420 END LOOP; -- FOR THE REPORTING SCHOOL CURSOR
2421 END IF;
2422
2423 COMMIT;
2424 EXCEPTION
2425 WHEN OTHERS THEN
2426 ROLLBACK;
2427 retcode := 2;
2428 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
2429 fnd_file.put_line(fnd_file.log, SQLERRM);
2430 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2431 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.main_response.debug','sqlerrm ' || SQLERRM);
2432 END IF;
2433 igs_ge_msg_stack.conc_exception_hndl;
2434 END main_response;
2435
2436 PROCEDURE set_nls_fmt ( PARAM IN VARCHAR2)
2437 AS
2438 l_temp varchar2(10);
2439 l_sql_stmt varchar2(100);
2440 BEGIN
2441 l_temp := '.,';
2442 l_sql_stmt := 'ALTER SESSION SET NLS_NUMERIC_CHARACTERS = ''' || l_temp || '''';
2443 EXECute IMMEDIATE l_sql_stmt;
2444 END set_nls_fmt;
2445
2446 FUNCTION get_created_by
2447 RETURN NUMBER AS
2448 BEGIN
2449 RETURN -1;
2450 END get_created_by;
2451
2452 FUNCTION get_creation_date
2453 RETURN DATE AS
2454 BEGIN
2455 RETURN TO_DATE('01062004', 'DDMMYYYY');
2456 END get_creation_date ;
2457
2458 FUNCTION get_last_updated_by
2459 RETURN NUMBER AS
2460 BEGIN
2461 RETURN -1;
2462 END get_last_updated_by ;
2463
2464 FUNCTION get_last_update_date
2465 RETURN DATE AS
2466 BEGIN
2467 RETURN TO_DATE('01062004', 'DDMMYYYY');
2468 END get_last_update_date ;
2469
2470 FUNCTION get_last_update_login
2471 RETURN NUMBER AS
2472 BEGIN
2473 RETURN -1;
2474 END get_last_update_login ;
2475
2476
2477 PROCEDURE get_datetime ( PARAM IN VARCHAR2,
2478 OUTPARAM OUT NOCOPY VARCHAR2
2479 )
2480 AS
2481 BEGIN
2482 OUTPARAM := SUBSTR(REPLACE(REPLACE(REPLACE(PARAM,'-'),':'),'T'),1,14);
2483 END get_datetime;
2484
2485 PROCEDURE get_date ( PARAM IN VARCHAR2,
2486 OUTPARAM OUT NOCOPY VARCHAR2
2487 )
2488 AS
2489 BEGIN
2490 OUTPARAM := REPLACE(PARAM,'-');
2491 END get_date;
2492
2493 PROCEDURE launch_request ( itemtype IN VARCHAR2,
2494 itemkey IN VARCHAR2,
2495 actid IN NUMBER,
2496 funcmode IN VARCHAR2,
2497 resultout OUT NOCOPY VARCHAR2
2498 )
2499 IS
2500 ln_request_id NUMBER;
2501 l_doc_id VARCHAR2(30);
2502 BEGIN
2503 l_doc_id := wf_engine.GetItemAttrText(itemtype, itemkey, 'ECX_PARAMETER1');
2504 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2505 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.launch_request.debug','Document ID from ECX_PARAMETER1 is: ' || l_doc_id);
2506 END IF;
2507
2508 ln_request_id := fnd_request.submit_request(
2509 'IGF','IGFSLJ18','','', FALSE,
2510 l_doc_id,CHR(0),
2511 '','','','','','','','',
2512 '','','','','','','','','','',
2513 '','','','','','','','','','',
2514 '','','','','','','','','','',
2515 '','','','','','','','','','',
2516 '','','','','','','','','','',
2517 '','','','','','','','','','',
2518 '','','','','','','','','','',
2519 '','','','','','','','','','',
2520 '','','','','','','','','','');
2521 resultout := 'SUCCESS';
2522 COMMIT;
2523 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2524 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.launch_request.debug','sub process launched with id ' || ln_request_id);
2525 END IF;
2526 EXCEPTION
2527 WHEN OTHERS THEN
2528 resultout := 'F';
2529 wf_core.context ('IGF_SL_UPLOAD_XML',
2530 'LAUNCH_REQUEST', itemtype,
2531 itemkey,to_char(actid), funcmode);
2532 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2533 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.launch_request.debug','sqlerrm ' || SQLERRM);
2534 END IF;
2535 RETURN;
2536 END launch_request;
2537
2538 PROCEDURE update_rs_respcode(p_rec_id IN VARCHAR2, p_resp_code IN VARCHAR2)
2539 AS
2540 CURSOR cur_cod_temp(cp_rec_id IN VARCHAR2) IS
2541 SELECT rec_id, level_code, resp_code
2542 FROM IGF_SL_COD_TEMP
2543 WHERE REC_ID = cp_rec_id
2544 AND LEVEL_CODE = 'RS';
2545 rec_cod_temp cur_cod_temp%ROWTYPE;
2546 IGFSL26_XMLGW_RS_REC_NOT_FOUND EXCEPTION;
2547 BEGIN
2548 -- write parameters in debug messages
2549 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2550 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'p_rec_id = ' || p_rec_id);
2551 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'p_resp_code = ' || p_resp_code);
2552 END IF;
2553
2554 -- check wether record with p_rec_id and LEVEL_CODE as RS exists or not.
2555 OPEN cur_cod_temp(p_rec_id);
2556 FETCH cur_cod_temp INTO rec_cod_temp;
2557 IF cur_cod_temp%NOTFOUND THEN
2558 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2559 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'Record NOT found with p_rec_id ' || p_rec_id || ' and with level_code RS');
2560 END IF;
2561 RAISE IGFSL26_XMLGW_RS_REC_NOT_FOUND;
2562 ELSE
2563 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2564 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'Record has been found with p_rec_id ' || p_rec_id);
2565 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'REC_ID = ' || rec_cod_temp.REC_ID);
2566 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'LEVEL_CODE = ' || rec_cod_temp.LEVEL_CODE);
2567 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'RESP_CODE = ' || rec_cod_temp.RESP_CODE);
2568 END IF;
2569 END IF;
2570 CLOSE cur_cod_temp;
2571
2572 -- update the record with RESP_CODE as p_resp_code
2573 -- we are not commiting after the update as the transaction in which this update executes
2574 -- and the transaction in which XML Gateway engine executes its insertions is same.
2575 UPDATE IGF_SL_COD_TEMP
2576 SET RESP_CODE = p_resp_code
2577 WHERE REC_ID = p_rec_id
2578 AND LEVEL_CODE = 'RS';
2579
2580 -- print the record in debug messages.
2581 OPEN cur_cod_temp(p_rec_id);
2582 FETCH cur_cod_temp INTO rec_cod_temp;
2583 IF cur_cod_temp%NOTFOUND THEN
2584 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2585 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'After update, Record NOT found with p_rec_id ' || p_rec_id || ' and with level_code RS');
2586 END IF;
2587 RAISE IGFSL26_XMLGW_RS_REC_NOT_FOUND;
2588 ELSE
2589 IF fnd_log.level_statement >= fnd_log.g_current_runtime_level THEN
2590 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'After update, Record has been found with p_rec_id ' || p_rec_id);
2591 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'After update, REC_ID = ' || rec_cod_temp.REC_ID);
2592 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'After update, LEVEL_CODE = ' || rec_cod_temp.LEVEL_CODE);
2593 fnd_log.string(fnd_log.level_statement,'igf.plsql.igf_sl_upload_xml.update_rs_respcode.debug', 'After update, RESP_CODE = ' || rec_cod_temp.RESP_CODE);
2594 END IF;
2595 END IF;
2596 CLOSE cur_cod_temp;
2597 EXCEPTION
2598 WHEN IGFSL26_XMLGW_RS_REC_NOT_FOUND THEN
2599 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2600 fnd_message.set_token('NAME','igf_sl_upload_xml.update_rs_respcode');
2601 igs_ge_msg_stack.add;
2602 app_exception.raise_exception;
2603 WHEN OTHERS THEN
2604 fnd_message.set_name('IGF','IGF_GE_UNHANDLED_EXP');
2605 fnd_message.set_token('NAME','igf_sl_upload_xml.update_rs_respcode');
2606 igs_ge_msg_stack.add;
2607 app_exception.raise_exception;
2608 END update_rs_respcode;
2609
2610 PROCEDURE update_rcptdate_respcode(p_doc_id IN VARCHAR2, p_receipt_date IN VARCHAR2)
2611 AS
2612 ld_receipt_date DATE;
2613 lv_receipt_date VARCHAR2(100);
2614 BEGIN
2615 get_datetime(p_receipt_date, lv_receipt_date);
2616 ld_receipt_date := TO_DATE(SUBSTR(lv_receipt_date, 1, 8), 'YYYY/MM/DD');
2617
2618 UPDATE IGF_SL_CR_RESP_DTLS
2619 SET RECEIPT_DATE = ld_receipt_date
2620 WHERE DOCUMENT_ID_TXT = p_doc_id;
2621
2622 END;
2623
2624 END igf_sl_upload_xml;