1 PACKAGE BODY IGR_IMP_004 AS
2 /* $Header: IGSRT15B.pls 120.2 2006/06/27 12:07:06 rghosh noship $ */
3
4 /* ------------------------------------------------------------------------------------------------------------------------
5 || Created By : rbezawad
6 || Created On : 28-Feb-05
7 || Purpose : Extract of IGR related references from Admissions Import process packages (IGSAD98B.pls)
8 || to get rid of probable compilation errors for non-IGR customers.
9 || Known limitations, enhancements or remarks :
10 || Change History :
11 || WHO WHEN WHAT
12 9-Mar-05 rbezawad Modified logic to Validated Pakcage items for APC Integration Build. Bug: 3973942.
13 Also obsoelted usage of Entry Status/Program/Unit Set code columns.
14 ---------------------------------------------------------------------------------------------------------------------------*/
15
16 -- lb_validation is set to true if the particular record in the interface
17 -- table passes all the validation meant for it, othewise it is set to false
18 lb_validation BOOLEAN;
19
20 cst_s_val_1 CONSTANT VARCHAR2(1) := '1';
21 cst_s_val_2 CONSTANT VARCHAR2(1) := '2';
22 cst_s_val_3 CONSTANT VARCHAR2(1) := '3';
23 cst_s_val_4 CONSTANT VARCHAR2(1) := '4';
24
25 cst_ec_val_E322 CONSTANT VARCHAR2(4) := 'E322';
26 cst_ec_val_E014 CONSTANT VARCHAR2(4) := 'E014';
27 cst_ec_val_E702 CONSTANT VARCHAR2(4) := 'E702';
28 cst_ec_val_e700 CONSTANT VARCHAR2(4) := 'E700';
29
30 -- The cursor c_inq_info is used, to select all the records from the Inquiry Information
31 -- Interface table that are pending for processing and the parent
32 -- Inquiry Application record status is completed ('1')and the parent
33 -- Interface Record has a status of Completed ('1') or Warning ('4')
34
35 CURSOR c_inq_info (cp_interface_run_id igr_i_info_int.interface_run_id%TYPE) IS
36 SELECT inq.rowid,inq.*
37 FROM igr_i_info_int inq
38 WHERE inq.interface_run_id = cp_interface_run_id
39 AND inq.status = '2';
40
41
42 -- The cursor c_inq_char is used, to select all the records from the Inquiry Characteristics
43 -- Interface table that are pending for processing and the parent
44 -- Inquiry Application record's status is completed ('1')and the parent
45 -- Interface Record has a status of Completed ('1') or Warning ('4')
46
47 CURSOR c_inq_char (cp_interface_run_id igr_i_char_int.interface_run_id%TYPE) IS
48 SELECT chi.rowid,chi.*
49 FROM igr_i_char_int chi
50 WHERE chi.interface_run_id = cp_interface_run_id
51 AND chi.status = '2';
52
53
54 -- The cursor c_inq_pkg is used, to select all the records from the Inquiry Packages
55 -- Interface table that are pending for processing and the parent
56 -- Inquiry Application record's status is completed ('1')and the parent
57 -- Interface Record has status of Completed ('1') or Warning ('4')
58
59 CURSOR c_inq_pkg (cp_interface_run_id igr_i_pkg_int.interface_run_id%TYPE)IS
60 SELECT pkg.rowid,pkg.*
61 FROM igr_i_pkg_int pkg
62 WHERE pkg.interface_run_id = cp_interface_run_id
63 AND pkg.status = '2';
64
65 -------------Local Procedure Get_Meaning-----------------------------------------------
66 FUNCTION get_meaning(
67 p_lookup_code VARCHAR2,
68 p_lookup_type VARCHAR2
69 )
70 RETURN VARCHAR2 AS
71 /*******************************************************************************
72 Created By: Annamalai Muthu
73 Date Created By: 06-12-2001 (MM-DD-YYYY)
74 Purpose: This fucntion is used to return the meaning for
75 a particular lookup_type and lookup_code combination.
76 Known limitations,enhancements,remarks:
77 Change History
78 Who When What
79
80 *******************************************************************************/
81 -------------------------Variable Declaration------------------------------------
82 lv_meaning igs_lookups_view.meaning%TYPE;
83 -------------------------End Variable Declaration------------------------------------
84
85 ---------------------------Cursor Declarations----------------------------------------
86 -- Cursor c_lkup is used to select the record (if any) that matches the
87 -- criteria passed via the parameters to the fucntion
88 CURSOR c_lkup (cp_lookup_type IGS_LOOKUPS_VIEW.LOOKUP_TYPE%TYPE,
89 cp_lookup_code IGS_LOOKUPS_VIEW.LOOKUP_CODE%TYPE) IS
90 SELECT meaning
91 FROM igs_lookups_view
92 WHERE lookup_type = cp_lookup_type
93 AND lookup_code = cp_lookup_code;
94 ---------------------------End Cursor Declarations----------------------------------------
95 BEGIN
96 OPEN c_lkup (P_lookup_code, p_lookup_type);
97 FETCH c_lkup INTO lv_meaning;
98
99 IF c_lkup%NOTFOUND THEN
100 -- No records are found that match the criteria passed via the parameters
101 -- hence closing the cursor and returning null value.
102 CLOSE c_lkup;
103 RETURN NULL;
104 END IF;
105
106 -- The control will come to this point only if a matching record is found
107 -- Hence closing the cursor and returning the value.
108 CLOSE c_lkup;
109 RETURN lv_meaning;
110 END get_meaning;
111 -------------------------------------------------------------------------------
112
113
114 PROCEDURE validate_inq_info(
115 p_inq_info_rec IN c_inq_info%ROWTYPE,
116 p_validation OUT NOCOPY BOOLEAN
117 ) AS
118 /*******************************************************************************
119 Created By: Annamalai Muthu
120 Date Created By: 06-12-2001 (MM-DD-YYYY)
121 Purpose: To Validate the Inquiry Information record being processed
122 Known limitations,enhancements,remarks:
123 Change History
124 Who When What
125
126 *******************************************************************************/
127
128 -------------------------Variable Delcaration---------------------------------
129 lv_var VARCHAR2(1);
130
131 l_error_text VARCHAR2(2000);
132 -------------------------End Variable Delcaration---------------------------------
133
134
135 ----------------------------Cursor Declaration--------------------------------------
136 -- The cursor c_inq_info_typ is used to validate the column INQUIRY_INFORMATION_TYPE
137 -- in the interface table. This cursor checks to see if the value is being
138 -- properly referenced from the appropriate parent table.
139 CURSOR c_inq_info_typ (cp_info_type_id igr_i_info_int.info_type_id%TYPE) IS
140 SELECT
141 'X'
142 FROM
143 igr_i_info_types_v
144 WHERE
145 TRUNC(actual_avail_from_date) <= TRUNC(SYSDATE)
146 AND TRUNC(actual_avail_to_date) >= TRUNC(SYSDATE)
147 AND info_type_id = cp_info_type_id;
148 ----------------------------End Cursor Declaration--------------------------------------
149 BEGIN
150
151 OPEN c_inq_info_typ(p_inq_info_rec.info_type_id);
152 FETCH c_inq_info_typ INTO lv_var;
153
154 IF c_inq_info_typ%NOTFOUND THEN
155 -- The control will come to this point only when there are is no referrence
156 -- to this value in the parent, hence this is an error and validation must fail.
157 p_validation := FALSE;
158
159 -- Setting the status of the interface record to failed and the appropriate
160 -- error code meant for the particular column.
161 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E318', 8405);
162 UPDATE igr_i_info_int
163 SET
164 ERROR_CODE = 'E318',
165 STATUS = '3',
166 error_text = l_error_text
167 WHERE INTERFACE_INQ_INFO_ID = p_inq_info_rec.INTERFACE_INQ_INFO_ID ;
168
169 ELSE
170 -- if the validation succeeds then continue processing normally
171 p_validation := TRUE;
172 END IF;
173 -- closing the cursor that was opened before the IF block
174 CLOSE c_inq_info_typ;
175 END validate_inq_info;
176
177 -------------------------------------------------------------------------------
178 PROCEDURE create_inq_info(
179 p_inq_info_rec IN c_inq_info%ROWTYPE
180 ) AS
181 /*******************************************************************************
182 Created By: Annamalai Muthu
183 Date Created By: 06-12-2001 (MM-DD-YYYY)
184 Purpose: To Insert the Inquiry Information record
185 in to the system tables using a TBH call
186 Known limitations,enhancements,remarks:
187 Change History
188 Who When What
189 *******************************************************************************/
190
191 -----------------------------Variable Declaration-----------------------------------------
192 lv_rowid VARCHAR2(25);
193
194 l_prog_label VARCHAR2(100);
195 p_error_code VARCHAR2(30);
196 p_status VARCHAR2(1);
197 l_error_code VARCHAR2(30);
198 l_request_id NUMBER;
199 l_label VARCHAR2(100);
200 l_debug_str VARCHAR2(2000);
201 l_enable_log VARCHAR2(1);
202 l_rowid VARCHAR2(25);
203 l_error_text VARCHAR2(2000);
204 l_type VARCHAR2(1);
205 l_status VARCHAR2(1);
206 l_acad_int_id NUMBER;
207
208 l_msg_at_index NUMBER := 0;
209 l_return_status VARCHAR2(1);
210 l_msg_count NUMBER;
211 l_msg_data VARCHAR2(2000);
212 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
213
214 l_records_processed NUMBER := 0;
215
216 -----------------------------End Variable Declaration-----------------------------------------
217
218 BEGIN -- procedure create_inq_info
219
220 -- Validate the record using the procedure validate_inq_info.
221 validate_inq_info(p_inq_info_rec, lb_validation);
222
223 IF lb_validation THEN
224 -- the validation succeeds then
225 -- call the TBH to insert the values from the
226 -- Interface table igr_i_info_int.
227 igr_i_a_itype_pkg.insert_row(
228 x_mode => 'R',
229 x_rowid => lv_rowid,
230 x_person_id => p_inq_info_rec.person_id,
231 x_enquiry_appl_number => p_inq_info_rec.enquiry_appl_number,
232 x_info_type_id => p_inq_info_rec.info_type_id);
233
234 igs_ad_gen_016.extract_msg_from_stack (
235 p_msg_at_index => l_msg_at_index,
236 p_return_status => l_return_status,
237 p_msg_count => l_msg_count,
238 p_msg_data => l_msg_data,
239 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
240
241 IF l_msg_count > 0 THEN
242 l_error_text := l_msg_data;
243 l_type := l_hash_msg_name_text_type_tab(l_msg_count-1).type;
244 END IF;
245
246 IF l_type = 'E' THEN
247 ROLLBACK TO inqinfo_save;
248 UPDATE igr_i_info_int
249 SET status = cst_s_val_3,
250 error_code = cst_ec_val_E322,
251 error_text = l_error_text
252 WHERE rowid = p_inq_info_rec.rowid;
253
254 IF l_enable_log = 'Y' THEN
255 igs_ad_imp_001.logerrormessage(p_inq_info_rec.interface_inq_info_id,l_msg_data);
256 END IF;
257
258 ELSIF l_type = 'S' THEN
259 UPDATE igr_i_info_int
260 SET status = cst_s_val_4,
261 error_code = cst_ec_val_E702,
262 error_text = l_error_text
263 WHERE rowid = p_inq_info_rec.rowid;
264
265 IF l_enable_log = 'Y' THEN
266 igs_ad_imp_001.logerrormessage(p_inq_info_rec.interface_inq_info_id,l_msg_data);
267 END IF;
268
269 ELSIF l_type IS NULL THEN
270 UPDATE igr_i_info_int
271 SET status = cst_s_val_1,
272 error_code = NULL,
273 error_text = NULL
274 WHERE rowid = p_inq_info_rec.rowid;
275
276 END IF;
277
278 l_records_processed := l_records_processed +1;
279
280 END IF; -- lb_validation
281
282 EXCEPTION
283 WHEN OTHERS THEN
284
285 l_status := '3';
286 l_error_code := 'E322';
287
288 igs_ad_gen_016.extract_msg_from_stack (
289 p_msg_at_index => l_msg_at_index,
290 p_return_status => l_return_status,
291 p_msg_count => l_msg_count,
292 p_msg_data => l_msg_data,
293 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
294
295 l_error_text := l_msg_data;
296
297 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
298 IF l_enable_log = 'Y' THEN
299 igs_ad_imp_001.logerrormessage(p_inq_info_rec.interface_inq_info_id,l_msg_data);
300 END IF;
301 ELSE
302
303 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
304 l_label := 'igs.plsql.igr_imp_004.create_inq_info.exception '||'E322';
305
306 fnd_message.set_name('IGS','IGS_PE_IMP_DET_ERROR');
307 fnd_message.set_token('CONTEXT',p_inq_info_rec.interface_inq_info_id);
308 fnd_message.set_token('ERROR', l_error_text);
309
310 l_debug_str := fnd_message.get;
311
312 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str, NULL,
313 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
314 END IF;
315 END IF;
316
317 ROLLBACK TO inqinfo_save;
318
319 UPDATE igr_i_info_int
320 SET status = cst_s_val_3,
321 error_code = l_error_code ,
322 error_text = l_error_text
323 WHERE rowid = p_inq_info_rec.rowid;
324
325 l_records_processed := l_records_processed + 1;
326
327 END create_inq_info; -- procedure create_inq_info.
328
329 PROCEDURE prc_inq_info (
330 p_interface_run_id IN NUMBER,
331 p_enable_log IN VARCHAR2,
335 Date Created By: 06-12-2001 (MM-DD-YYYY)
332 p_rule IN VARCHAR2) AS
333 /*******************************************************************************
334 Created By: Annamalai Muthu
336 Purpose: To Process the Inquiry Information records in the Interface table
337 Known limitations,enhancements,remarks:
338 Change History
339 Who When What
340
341 *******************************************************************************/
342 l_records_processed NUMBER := 0;
343
344 l_request_id NUMBER;
345 l_error_text VARCHAR2(2000);
346 l_error_code VARCHAR2(30);
347
348 BEGIN -- Procedure PRC_INQ_INFO.
349
350 /**********************************************************************************
351 This procedure is used to import the data from the interface table
352 igr_i_info_int to the System table
353
354 1. This procedure will loop through all the records in the table igr_i_info_int
355 with the STATUS = 2 -'Pending AND the parent IGS_AD_INTERFACE RECORD has
356 been successfully imported (Status = 1 - 'Completed')
357 ***********************************************************************************/
358 IF (l_request_id IS NULL) THEN
359 l_request_id := fnd_global.conc_request_id;
360 END IF;
361
362 FOR lr_inq_info_rec IN c_inq_info (p_interface_run_id)
363 LOOP
364 l_records_processed := l_records_processed + 1;
365 SAVEPOINT inqinfo_save;
366 create_inq_info(lr_inq_info_rec);
367 IF l_records_processed = 100 THEN
368 COMMIT;
369 l_records_processed := 0;
370 END IF;
371 l_error_text := NULL;
372 l_error_code := NULL;
373 END LOOP;
374 IF l_records_processed < 100 AND l_records_processed > 0 THEN
375 COMMIT;
376 END IF;
377 END prc_inq_info;
378
379
380 PROCEDURE validate_inq_char(
381 p_inq_char_rec IN c_inq_char%ROWTYPE,
382 p_validation OUT NOCOPY BOOLEAN
383 ) AS
384 /*******************************************************************************
385 Created By: Annamalai Muthu
386 Date Created By: 06-12-2001 (MM-DD-YYYY)
387 Purpose: To Validate the Inquiry Characteristics being porcessed
388 Known limitations,enhancements,remarks:
389 Change History
390 Who When What
391
392 *******************************************************************************/
393 ---------------------------Variable Delcation----------------------------------------
394 lv_var VARCHAR2(1);
395
396 l_error_text VARCHAR2(2000);
397 ---------------------------End Variable Delcation----------------------------------------
398
399 ---------------------------------Cursor Declaration--------------------------------------
400 -- The cursor c_inq_char_typ is used to validate the column INQUIRY_CHARACTERISTIC_TYPE
401 -- in the interface table. This cursor checks to see if the value is being
402 -- properly referenced from the appropriate parent table.
403
404 CURSOR c_inq_char_typ (cp_INQUIRY_CHARACTERISTIC_TYPE igr_i_char_int.inquiry_characteristic_type%TYPE)IS
405 SELECT
406 'X'
407 FROM
408 igr_i_e_chartyp
409 WHERE
410 closed_ind ='N'
411 AND enquiry_characteristic_type = cp_inquiry_characteristic_type;
412 ---------------------------------End Cursor Declaration--------------------------------------
413 BEGIN -- Validate_inq_char
414
415 OPEN c_inq_char_typ(p_inq_char_rec.INQUIRY_CHARACTERISTIC_TYPE);
416 FETCH c_inq_char_typ INTO lv_var;
417
418 IF c_inq_char_typ%NOTFOUND THEN
419 -- The control will come to this point only when there are is no referrence
420 -- to this value in the parent, hence this is an error and validation must fail.
421 p_validation := FALSE;
422
423 -- Setting the status of the interface record to failed and the appropriate
424 -- error code meant for the particular column.
425 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E319', 8405);
426 UPDATE igr_i_char_int
427 SET
428 error_code = 'E319',
429 status = '3',
430 error_text = l_error_text
431 WHERE interface_inq_char_id = p_inq_char_rec.interface_inq_char_id;
432
433 ELSE
434 -- if the validation succeeds then continue processing normally
435 p_validation := TRUE;
436 END IF;
437 -- closing the cursor that was opened before the if block.
438 CLOSE c_inq_char_typ;
439 END validate_inq_char;
440
441 PROCEDURE create_inq_char(
442 p_inq_char_rec IN c_inq_char%ROWTYPE
443 ) AS
444 /*******************************************************************************
445 Created By: Annamalai Muthu
446 Date Created By: 06-12-2001 (MM-DD-YYYY)
447 Purpose: To Insert the Inquiry Characteristics record being processed
448 in to the system tables using a TBH call
449 Known limitations,enhancements,remarks:
450 Change History
451 Who When What
452
453 *******************************************************************************/
454 lv_rowid VARCHAR2(25);
455
456 l_prog_label VARCHAR2(100);
457 p_error_code VARCHAR2(30);
458 p_status VARCHAR2(1);
459 l_error_code VARCHAR2(30);
460 l_request_id NUMBER;
461 l_label VARCHAR2(100);
462 l_debug_str VARCHAR2(2000);
463 l_enable_log VARCHAR2(1);
464 l_rowid VARCHAR2(25);
465 l_error_text VARCHAR2(2000);
466 l_type VARCHAR2(1);
467 l_status VARCHAR2(1);
468 l_acad_int_id NUMBER;
469
470 l_msg_at_index NUMBER := 0;
471 l_return_status VARCHAR2(1);
475
472 l_msg_count NUMBER;
473 l_msg_data VARCHAR2(2000);
474 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
476 l_records_processed NUMBER := 0;
477
478 BEGIN -- create_inq_char
479
480 -- Validate the record using the procedure validate_inq_char.
481 Validate_inq_char(p_inq_char_rec, lb_validation);
482
483 IF lb_validation THEN
484 -- call the TBH to insert the values from the
485 -- Interface table igr_i_char_int.
486
487 igr_i_a_chartyp_pkg.insert_row(
488 x_rowid => lv_rowid,
489 x_person_id => p_inq_char_rec.person_id,
490 x_enquiry_appl_number => p_inq_char_rec.enquiry_appl_number,
491 x_enquiry_characteristic_type => p_inq_char_rec.inquiry_characteristic_type,
492 x_mode => 'R');
493
494 -- If the Insert succeeds then update the Interface records as completed.
495 igs_ad_gen_016.extract_msg_from_stack (
496 p_msg_at_index => l_msg_at_index,
497 p_return_status => l_return_status,
498 p_msg_count => l_msg_count,
499 p_msg_data => l_msg_data,
500 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
501
502 IF l_msg_count > 0 THEN
503 l_error_text := l_msg_data;
504 l_type := l_hash_msg_name_text_type_tab(l_msg_count-1).type;
505 END IF;
506
507 IF l_type = 'E' THEN
508 ROLLBACK TO inqchar_save;
509 UPDATE igr_i_char_int
510 SET status = cst_s_val_3,
511 error_code = cst_ec_val_E322,
512 error_text = l_error_text
513 WHERE rowid = p_inq_char_rec.rowid;
514
515 IF l_enable_log = 'Y' THEN
516 igs_ad_imp_001.logerrormessage(p_inq_char_rec.interface_inq_char_id,l_msg_data);
517 END IF;
518
519 ELSIF l_type = 'S' THEN
520 UPDATE igr_i_char_int
521 SET status = cst_s_val_4,
522 error_code = cst_ec_val_E702,
523 error_text = l_error_text
524 WHERE rowid = p_inq_char_rec.rowid;
525
526 IF l_enable_log = 'Y' THEN
527 igs_ad_imp_001.logerrormessage(p_inq_char_rec.interface_inq_char_id,l_msg_data);
528 END IF;
529
530 ELSIF l_type IS NULL THEN
531 UPDATE igr_i_char_int
532 SET status = cst_s_val_1,
533 error_code = NULL,
534 error_text = NULL
535 WHERE rowid = p_inq_char_rec.rowid;
536
537 END IF;
538
539 l_records_processed := l_records_processed +1;
540
541 END IF; -- lb_validation
542
543 EXCEPTION
544 WHEN OTHERS THEN
545
546 l_status := '3';
547 l_error_code := 'E322';
548
549 igs_ad_gen_016.extract_msg_from_stack (
550 p_msg_at_index => l_msg_at_index,
551 p_return_status => l_return_status,
552 p_msg_count => l_msg_count,
553 p_msg_data => l_msg_data,
554 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
555
556 l_error_text := l_msg_data;
557
558 IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <> 'ORA' THEN
559 IF l_enable_log = 'Y' THEN
560 igs_ad_imp_001.logerrormessage(p_inq_char_rec.interface_inq_char_id,l_msg_data);
561 END IF;
562 ELSE
563
564 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
565 l_label := 'igs.plsql.igr_imp_004.create_inq_char.exception '||'E322';
566
567 fnd_message.set_name('IGS','IGS_PE_IMP_DET_ERROR');
568 fnd_message.set_token('CONTEXT',p_inq_char_rec.interface_inq_char_id);
569 fnd_message.set_token('ERROR', l_error_text);
570
571 l_debug_str := fnd_message.get;
572
573 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str, NULL,
574 NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
575 END IF;
576 END IF;
577
578 ROLLBACK TO inqchar_save;
579
580 UPDATE igr_i_char_int
581 SET status = cst_s_val_3,
582 error_code = l_error_code ,
583 error_text = l_error_text
584 WHERE rowid = p_inq_char_rec.rowid;
585
586 l_records_processed := l_records_processed + 1;
587 END create_inq_char;
588
589 PROCEDURE prc_inq_char (
590 p_interface_run_id IN NUMBER,
591 p_enable_log IN VARCHAR2,
592 p_rule IN VARCHAR2) AS
593 /*******************************************************************************
594 Created By: Annamalai Muthu
595 Date Created By: 06-12-2001 (MM-DD-YYYY)
596 Purpose: To Process Inquiry Characteristics in the Interface Table
597 Known limitations,enhancements,remarks:
598 Change History
599 Who When What
600
601 *******************************************************************************/
602 l_records_processed NUMBER := 0;
603
604 l_request_id NUMBER;
605 l_error_text VARCHAR2(2000);
609 /*-----------------------------------------------------------------------------
606 l_error_code VARCHAR2(30);
607
608 BEGIN -- prc_inq_char
610 This procedure is used to import the data from the interface table
611 igr_i_char_int to the System table
612
613 1. This procedure will loop through all the records in the table igr_i_char_int
614 with the STATUS = 2 -'Pending AND the parent IGS_AD_INTERFACE RECORD has
615 been successfully imported (Status = 1 - 'Completed')
616 *******************************************************************/
617 IF (l_request_id IS NULL) THEN
618 l_request_id := fnd_global.conc_request_id;
619 END IF;
620
621 FOR lr_inq_char_rec IN c_inq_char (p_interface_run_id)
622 LOOP
623 l_records_processed := l_records_processed + 1;
624 SAVEPOINT inqchar_save;
625 create_inq_char(lr_inq_char_rec);
626 IF l_records_processed = 100 THEN
627 COMMIT;
628 l_records_processed := 0;
629 END IF;
630 l_error_text := NULL;
631 l_error_code := NULL;
632 END LOOP;
633 IF l_records_processed < 100 AND l_records_processed > 0 THEN
634 COMMIT;
635 END IF;
636 END prc_inq_char;
637
638 PROCEDURE validate_inq_pkg(
639 p_inq_pkg_rec IN c_inq_pkg%ROWTYPE,
640 p_validation OUT NOCOPY BOOLEAN
641 ) AS
642 /*******************************************************************************
643 Created By: Annamalai Muthu
644 Date Created By: 06-12-2001 (MM-DD-YYYY)
645 Purpose: To Validate the Inquiry Packages Record being processed
646 Known limitations,enhancements,remarks:
647 Change History
648 Who When What
649
650 *******************************************************************************/
651 -----------------------Variable declaration-------------------------------------
652 l_exists VARCHAR2(1);
653 l_request_id NUMBER;
654 l_error_text VARCHAR2(2000);
655 l_error_code VARCHAR2(30);
656
657 -------------------------End variable declaration-------------------------------
658
659 ------------------------Cursor declaration--------------------------------------
660 -- Cursor to validate the package items (This cursor is changed as a part of the SQL tuning
661 -- bug 4991561)
662 CURSOR c_package_item(cp_sales_lead_id as_sales_lead_lines.sales_lead_id%TYPE,
663 cp_inquiry_type_id igr_i_appl_int.inquiry_type_id%TYPE,
664 cp_package_reduct_ind igr_i_appl_int.pkg_reduct_ind%TYPE) IS
665 SELECT 'X'
666 FROM IGR_I_PKG_ITEM PKGITM,
667 AMS_P_DELIVERABLES_V DELIV
668 WHERE pkgitm.package_item_id = p_inq_pkg_rec.package_item_id
669 AND PKGITM.PACKAGE_ITEM_ID = DELIV.DELIVERABLE_ID
670 AND DELIV.KIT_FLAG = 'N'
671 AND (TRUNC(DELIV.actual_avail_from_date) <= TRUNC(SYSDATE) AND
672 TRUNC(DELIV.actual_avail_to_date) >= TRUNC(SYSDATE) )
673 AND (
674 -- Validate Package item against Inquiry Type => Associated with Information Type/Deliverable Kit => With Package items in it.
675 EXISTS ( SELECT 1
676 FROM ams_p_deliv_kit_items_v kitems,igr_i_inquiry_types inq
677 WHERE kitems.deliverable_kit_part_id = pkgitm.package_item_id
678 AND kitems.deliverable_kit_id = inq.info_type_id
679 AND inq.inquiry_type_id = cp_inquiry_type_id
680 )
681 -- Don't Validate package items against Academic Interest Category when Package Reduction Indicator is not set('N' or NULL).
682 OR NVL(cp_package_reduct_ind,'N') = 'N'
683 -- Don't Validate package items when there are no Academic Interest Categories associated.
684 OR NOT EXISTS ( SELECT 1
685 FROM as_sales_lead_lines
686 WHERE sales_lead_id = cp_sales_lead_id
687 AND category_id IS NOT NULL
688 AND category_set_id IS NOT NULL
689 )
690 -- Validate package items against Academic Interest Category when Package Reduction Indicator is set to 'Y'.
691 OR ( cp_package_reduct_ind = 'Y' AND
692 EXISTS ( SELECT 1
693 FROM igr_i_pkgitm_assign pia, as_sales_lead_lines lines
694 WHERE pia.package_item_id = pkgitm.package_item_id
695 AND pia.product_category_id = lines.category_id
696 AND pia.product_category_set_id = lines.category_set_id
697 AND lines.sales_lead_id = cp_sales_lead_id
698 AND pia.enabled_flag = 'Y'
699 )
700 )
701 );
702
703 --Cursor to get the inquiry_type_id and package_reduct_ind from IGR_I_APPL_INT table.
704 CURSOR c_appl_int (cp_interface_inq_appl_id igr_i_pkg_int.interface_inq_appl_id%TYPE) IS
705 SELECT inquiry_type_id, pkg_reduct_ind
706 FROM igr_i_appl_int
707 WHERE interface_inq_appl_id = cp_interface_inq_appl_id;
708
709 l_appl_int_rec c_appl_int%ROWTYPE;
710
711 ------------------------End Cursor declaration--------------------------------------
712 BEGIN -- Validate_inq_pkg
713
714 l_appl_int_rec := NULL;
715 OPEN c_appl_int (p_inq_pkg_rec.interface_inq_appl_id);
716 FETCH c_appl_int INTO l_appl_int_rec;
717 CLOSE c_appl_int;
718
719 OPEN c_package_item(p_inq_pkg_rec.sales_lead_id,l_appl_int_rec.inquiry_type_id,l_appl_int_rec.pkg_reduct_ind);
720 FETCH c_package_item INTO l_exists;
721 IF c_package_item%NOTFOUND THEN
722 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E321', 8405);
723 UPDATE
724 igr_i_pkg_int
725 SET
726 status = '3', -- 'Error'
730 INTERFACE_INQ_PKG_ID = p_inq_pkg_rec.INTERFACE_INQ_PKG_ID;
727 error_code = 'E321',
728 error_text = l_error_text
729 WHERE
731
732 p_validation := FALSE;
733 RETURN;
734 END IF;
735 p_validation := TRUE;
736 END validate_inq_pkg;
737
738 PROCEDURE create_inq_pkg(
739 p_inq_pkg_rec IN c_inq_pkg%ROWTYPE
740 ) AS
741 /*******************************************************************************
742 Created By: Annamalai Muthu
743 Date Created By: 06-12-2001 (MM-DD-YYYY)
744 Purpose: To Insert the Inquiry Packages record
745 in to the system tables using a TBH call
746 Known limitations,enhancements,remarks:
747 Change History
748 Who When What
749
750 *******************************************************************************/
751
752 ----------------Variable delcarations-------------------------------------------
753 lv_rowid VARCHAR2(25);
754 lb_validation BOOLEAN;
755 lv_msg_count NUMBER ;
756 lv_msg_data VARCHAR2(2000);
757 lv_return_status VARCHAR2(1);
758
759 l_prog_label VARCHAR2(100);
760 l_error_code VARCHAR2(30);
761 l_request_id NUMBER;
762 l_label VARCHAR2(100);
763 l_debug_str VARCHAR2(2000);
764 l_enable_log VARCHAR2(1);
765 l_rowid VARCHAR2(25);
766 l_error_text VARCHAR2(2000);
767 l_type VARCHAR2(1);
768 l_status VARCHAR2(1);
769 l_sqlerrm VARCHAR2(2000);
770
771 l_msg_at_index NUMBER := 0;
772 l_return_status VARCHAR2(1);
773 l_msg_count NUMBER;
774 l_msg_data VARCHAR2(2000);
775 l_hash_msg_name_text_type_tab igs_ad_gen_016.g_msg_name_text_type_table;
776
777 -------------------End variable declarations-----------------------------------
778 BEGIN -- create_inq_pkg
779
780 l_msg_at_index := igs_ge_msg_stack.count_msg;
781
782 Validate_inq_pkg(p_inq_pkg_rec, lb_validation);
783
784 IF lb_validation THEN
785
786 -- call the TBH to insert the values from the
787 -- Interface table igr_i_pkg_int.
788
789 igr_i_a_pkgitm_pkg.insert_row(
790 x_rowid => lv_rowid,
791 x_person_id => p_inq_pkg_rec.person_id,
792 x_enquiry_appl_number => p_inq_pkg_rec.enquiry_appl_number,
793 x_package_item_id => p_inq_pkg_rec.package_item_id,
794 x_mailed_dt => NULL,
795 x_donot_mail_ind => p_inq_pkg_rec.donot_mail_ind, -- added as part of idopa2
796 x_mode => 'R',
797 x_ret_status => lv_return_status,
798 x_msg_data => lv_msg_data,
799 x_msg_count => lv_msg_count,
800 x_action => 'Import'
801 );
802
803 IF lv_return_status <>'U' AND lv_msg_data IS NOT NULL THEN
804 IF l_enable_log = 'Y' THEN
805 igs_ad_imp_001.logerrormessage(p_inq_pkg_rec.interface_inq_pkg_id,lv_msg_data);
806 END IF;
807
808 ELSIF lv_return_status = 'U' THEN
809 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
810 l_label := 'igs.plsql.igr_imp_004.create_inq_pkg.exception '||'E322';
811
812 fnd_message.set_name('IGS','IGS_PE_IMP_DET_ERROR');
813 fnd_message.set_token('CONTEXT',p_inq_pkg_rec.interface_inq_pkg_id);
814 fnd_message.set_token('ERROR', l_error_text);
815
816 l_debug_str := fnd_message.get;
817
818 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
819 END IF;
820 END IF;
821
822 IF lv_msg_data IS NOT NULL THEN
823 /* Return status of Null should be treated as Success */
824 IF NVL(lv_return_status,'S') = 'S' THEN
825 UPDATE igr_i_pkg_int
826 SET status = '4',
827 error_code = 'E702',
828 error_text = lv_msg_data
829 WHERE rowid = p_inq_pkg_rec.rowid;
830 ELSE
831 ROLLBACK TO inqpkg_save;
832 UPDATE igr_i_pkg_int
833 SET status = '3',
834 error_code = 'E322',
835 error_text = lv_msg_data
836 WHERE rowid = p_inq_pkg_rec.rowid;
837 END IF;
838 ELSE
839 /* Return status of Null should be treated as Success */
840 IF NVL(lv_return_status,'S') = 'S' THEN
841 UPDATE igr_i_pkg_int
842 SET status = '1',
843 error_code = NULL,
844 error_text = NULL
845 WHERE rowid = p_inq_pkg_rec.rowid;
846 ELSE
847 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E322', 8405);
848 ROLLBACK TO inqpkg_save;
849 UPDATE igr_i_pkg_int
850 SET status = '3',
851 error_code = 'E322',
852 error_text = l_error_text
853 WHERE rowid = p_inq_pkg_rec.rowid;
854 END IF;
855 END IF;
856
857 END IF; -- lb_validation
858 EXCEPTION
859 WHEN OTHERS THEN
860
861 igs_ad_gen_016.extract_msg_from_stack (
862 p_msg_at_index => l_msg_at_index,
863 p_return_status => l_return_status,
864 p_msg_count => l_msg_count,
865 p_msg_data => l_msg_data,
866 p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
867
868 IF l_msg_data IS NOT NULL THEN
869 l_error_text := l_msg_data;
870 ELSE
871 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E322', 8405);
872 END IF;
873
874 ROLLBACK TO inqpkg_save;
875 UPDATE igr_i_pkg_int
876 SET error_code = 'E322',
877 status = '3',
878 error_text = l_error_text
879 WHERE interface_inq_pkg_id = p_inq_pkg_rec.interface_inq_pkg_id;
880
881 END create_inq_pkg;
882
883 PROCEDURE prc_inq_pkg (
884 p_interface_run_id IN NUMBER,
885 p_enable_log IN VARCHAR2,
886 p_rule IN VARCHAR2) AS
887 /*******************************************************************************
888 Created By: Annamalai Muthu
889 Date Created By: 06-12-2001 (MM-DD-YYYY)
890 Purpose: To Process the Inquiry Packages record in the Interface table
891 Known limitations,enhancements,remarks:
892 Change History
893 Who When What
894 *******************************************************************************/
895 l_records_processed NUMBER := 0;
896
897 l_request_id NUMBER;
898 l_error_text VARCHAR2(2000);
899 l_error_code VARCHAR2(30);
900
901 BEGIN -- prc_inq_pkg
902 /*-----------------------------------------------------------------------------
903 This procedure is used to import the data from the interface table
904 igr_i_pkg_int to the System table
905
906 1. This procedure will loop through all the records in the table igr_i_pkg_int
907 with the STATUS = 2 -'Pending AND the parent IGS_AD_INTERFACE RECORD has
908 been successfully imported (Status = 1 - 'Completed')
909 -------------------------------------------------------------------------------*/
910 IF (l_request_id IS NULL) THEN
911 l_request_id := fnd_global.conc_request_id;
912 END IF;
913
914 FOR lr_inq_pkg_rec IN c_inq_pkg (p_interface_run_id)
915 LOOP
916 l_records_processed := l_records_processed + 1;
917 SAVEPOINT inqpkg_save;
918
919 create_inq_pkg(lr_inq_pkg_rec);
920
921 IF l_records_processed = 100 THEN
922 COMMIT;
923 l_records_processed := 0;
924 END IF;
925 l_error_text := NULL;
926 l_error_code := NULL;
927 END LOOP;
928 IF l_records_processed < 100 AND l_records_processed > 0 THEN
929 COMMIT;
930 END IF;
931 END prc_inq_pkg;
932
933 END IGR_IMP_004;
[Home] [Help]