DBA Data[Home] [Help]

PACKAGE BODY: APPS.IGS_AD_IMP_010

Source


1 PACKAGE BODY Igs_Ad_Imp_010 AS
2 /* $Header: IGSAD88B.pls 120.0 2005/06/02 03:46:30 appldev noship $ */
3 /******************************************************************
4 Created By:
5 Date Created By:
6 Purpose:
7 Known limitations,enhancements,remarks:
8 Change History
9 Who        When          What
10 samaresh   02-FEB-2002	 Removed the procedure crt_appcln, as this happens
11                          through igsad82b.pls.
12 			 The procedures admp_val_import_us,admp_ins_import_program,
13 			 admp_ins_import_acai are removed, as these are called
14 			 from crt_appcln
15 			 bug # 2191058
16 vchappid   29-Aug-2001   Added new parameters into function calls, Enh Bug#1964478
17 ******************************************************************/
18 
19 /***************************Status,Discrepancy Rule, Match Indicators, Error Codes********************/
20 cst_rule_val_I  CONSTANT VARCHAR2(1) := 'I';
21 cst_rule_val_E  CONSTANT VARCHAR2(1) := 'E';
22 cst_rule_val_R  CONSTANT VARCHAR2(1) := 'R';
23 
24 cst_mi_val_11  CONSTANT VARCHAR2(2) := '11';
25 cst_mi_val_12  CONSTANT VARCHAR2(2) := '12';
26 cst_mi_val_13  CONSTANT VARCHAR2(2) := '13';
27 cst_mi_val_14  CONSTANT VARCHAR2(2) := '14';
28 cst_mi_val_15  CONSTANT VARCHAR2(2) := '15';
29 cst_mi_val_16  CONSTANT VARCHAR2(2) := '16';
30 cst_mi_val_17  CONSTANT VARCHAR2(2) := '17';
31 cst_mi_val_18  CONSTANT VARCHAR2(2) := '18';
32 cst_mi_val_19  CONSTANT VARCHAR2(2) := '19';
33 cst_mi_val_20  CONSTANT VARCHAR2(2) := '20';
34 cst_mi_val_21  CONSTANT VARCHAR2(2) := '21';
35 cst_mi_val_22  CONSTANT VARCHAR2(2) := '22';
36 cst_mi_val_23  CONSTANT VARCHAR2(2) := '23';
37 cst_mi_val_24  CONSTANT VARCHAR2(2) := '24';
38 cst_mi_val_25  CONSTANT VARCHAR2(2) := '25';
39 cst_mi_val_27  CONSTANT VARCHAR2(2) := '27';
40 
41 cst_s_val_1    CONSTANT VARCHAR2(1) := '1';
42 cst_s_val_2    CONSTANT VARCHAR2(1) := '2';
43 cst_s_val_3    CONSTANT VARCHAR2(1) := '3';
44 cst_s_val_4    CONSTANT VARCHAR2(1) := '4';
45 
46 cst_ec_val_E322 CONSTANT VARCHAR2(4) := 'E322';
47 cst_ec_val_E014 CONSTANT VARCHAR2(4) := 'E014';
48 
49 cst_insert     CONSTANT VARCHAR2(6) :=  'INSERT';
50 cst_update     CONSTANT VARCHAR2(6) :=  'UPDATE';
51 cst_unique_record   CONSTANT NUMBER :=  1;
52 
53 cst_et_val_E700 CONSTANT VARCHAR2(100) := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E700', 8405);
54 cst_et_val_E701 CONSTANT VARCHAR2(100) := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E701', 8405);
55 cst_et_val_E678 CONSTANT VARCHAR2(100) := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E678', 8405);
56 
57 cst_ec_val_E700 CONSTANT VARCHAR2(4) := 'E700';
58 cst_ec_val_E701 CONSTANT VARCHAR2(4) := 'E701';
59 cst_ec_val_E678 CONSTANT VARCHAR2(4) := 'E678';
60 
61 /***************************Status,Discrepancy Rule, Match Indicators, Error Codes********************/
62 
63   -- Process the Applicant Notes
64 PROCEDURE admp_val_pappl_nots(p_interface_run_id  IN NUMBER,
65                               p_enable_log        IN VARCHAR2,
66                               p_category_meaning  IN VARCHAR2,
67                               p_rule              IN VARCHAR2 )
68 AS
69         l_prog_label  VARCHAR2(100);
70         l_label  VARCHAR2(100);
71         l_debug_str VARCHAR2(2000);
72         l_request_id NUMBER;
73         l_error_code  igs_ad_notes_int.error_code%TYPE;
74 	l_records_processed NUMBER := 0;
75 
76 	-- local procedure
77   PROCEDURE crt_apcnt_notes(
78                         p_interface_run_id  IN NUMBER ) IS
79 
80 
81     CURSOR c_igs_ad_notes_int IS
82       SELECT  cst_insert dmlmode, rowid, a.*
83       FROM igs_ad_notes_int a
84       WHERE interface_run_id = p_interface_run_id
85       AND status = cst_s_val_2;
86 
87     l_Appl_Notes_Id 	NUMBER;
88     l_Rowid		VARCHAR2(100);
89     l_Rowid2            VARCHAR2(25);
90     l_Ref_Notes_Id	NUMBER;
91     l_msg_at_index   NUMBER := 0;
92     l_error_text    VARCHAR2(2000);
93     l_return_status   VARCHAR2(1);
94     l_msg_count      NUMBER ;
95     l_msg_data       VARCHAR2(2000);
96     l_hash_msg_name_text_type_tab   igs_ad_gen_016.g_msg_name_text_type_table;
97 
98     l_admission_cat igs_ad_appl.admission_cat%TYPE;
99     l_s_admission_process_type igs_ad_appl.s_admission_process_type%TYPE;
100 
101   BEGIN
102     IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
103 
104 
105       l_label := 'igs.plsql.igs_ad_imp_010.admp_val_pappl_nots.crt_apcnt_notes';
106       l_debug_str :=  'Interface Run ID' || p_interface_run_id;
107 
108       fnd_log.string_with_context( fnd_log.level_procedure,
109   	                           l_label,
110                                    l_debug_str, NULL,
111                                    NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
112     END IF;
113 
114     l_records_processed := 0;
115 
116     FOR notes_rec IN c_igs_ad_notes_int
117     LOOP
118       IF igs_ad_gen_016.get_appl_type_apc (p_application_type         => notes_rec.admission_application_type,
119                                            p_admission_cat            => l_admission_cat,
120                                            p_s_admission_process_type => l_s_admission_process_type) = 'TRUE' THEN
121 
122         IF igs_ad_gen_016.get_apcs (p_admission_cat            => l_admission_cat,
123                                     p_s_admission_process_type => l_s_admission_process_type,
124                                     p_s_admission_step_type    => 'APPL-NOTES') = 'FALSE' THEN
125           FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NOT_APC_STEP');
126           FND_MESSAGE.SET_TOKEN ('CATEGORY', p_category_meaning);
127           FND_MESSAGE.SET_TOKEN ('APPLTYPE', notes_rec.admission_application_type);
128           l_error_text := FND_MESSAGE.GET;
129           UPDATE igs_ad_notes_int
130           SET
131                  status = cst_s_val_3
132                  , error_code = cst_ec_val_E701
133                  , error_text = l_error_text
134           WHERE rowid = notes_rec.rowid;
135           l_records_processed := l_records_processed + 1;
136 
137         ELSIF NOT igs_ad_note_types_pkg.Get_UK2_For_Validation(
138  	          x_notes_type_id => notes_rec.Note_Type_Id,
139 	          x_notes_category => 'APPLICATION',
140 	          x_closed_ind =>  'N') THEN
141           FND_MESSAGE.SET_NAME ('IGS', 'IGS_GE_PK_UK_NOT_FOUND_CLOSED');
142           FND_MESSAGE.SET_TOKEN ('ATTRIBUTE', FND_MESSAGE.GET_STRING('IGS','IGS_AD_NOTE_TYPE'));
143           l_error_text := FND_MESSAGE.GET;
144           UPDATE igs_ad_notes_int
145           SET
146                  status = cst_s_val_3
147                  , error_code = cst_ec_val_E701
148                  , error_text = l_error_text
149           WHERE rowid = notes_rec.rowid;
150           l_records_processed := l_records_processed + 1;
151 
152 	ELSE
153           BEGIN
154             SAVEPOINT before_create;
155             l_msg_at_index := igs_ge_msg_stack.count_msg;
156 
157             igs_ad_appl_notes_pkg.INSERT_ROW(
158                                     X_ROWID => l_Rowid,
159                                     X_APPL_NOTES_ID => l_Appl_Notes_Id,
160                                     x_Person_Id => notes_rec.person_id,
161                                     X_Admission_Appl_Number => notes_rec.Admission_Appl_Number,
162                                     x_Nominated_Course_Cd =>  notes_rec.Nominated_Course_Cd,
163                                     x_Sequence_Number => notes_rec.Sequence_Number,
164                                     x_Note_Type_Id => notes_rec.Note_Type_Id,
165                                     x_Ref_Notes_Id => l_Ref_Notes_Id,
166                                     x_Mode  => 'R');
167             igs_ge_note_pkg.INSERT_ROW(
168                                     X_ROWID => l_Rowid2,
169                                     X_REFERENCE_NUMBER =>  l_Ref_Notes_Id,
170                                     X_S_NOTE_FORMAT_TYPE => 'TEXT',
171                                     X_NOTE_TEXT => notes_rec.notes,
172                                     X_MODE => 'R');
173             UPDATE igs_ad_notes_int
174             SET
175                    status = cst_s_val_1
176             WHERE rowid = notes_rec.rowid;
177             l_records_processed := l_records_processed + 1;
178             IF l_records_processed = 100 THEN
179              COMMIT;
180             l_records_processed := 0;
181             END IF;
182           EXCEPTION
183             WHEN OTHERS THEN
184               ROLLBACK TO before_create;
185               l_error_code := 'E322';
186               l_msg_data := SQLERRM;
187 
188               igs_ad_gen_016.extract_msg_from_stack (
189                       p_msg_at_index                => l_msg_at_index,
190                       p_return_status               => l_return_status,
191                       p_msg_count                   => l_msg_count,
192                       p_msg_data                    => l_msg_data,
193                       p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
194               l_error_text := NVL(l_msg_data,igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405));
195 
196               IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <>  'ORA'  THEN
197                 IF p_enable_log = 'Y' THEN
198                   igs_ad_imp_001.logerrormessage(notes_rec.interface_notes_id,l_msg_data,'IGS_AD_NOTES_INT');
199                 END IF;
200               ELSE
201                 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
202 
203                   l_label := 'igs.plsql.igs_ad_imp_010.admp_val_pappl_nots.crt_apcnt_notes.for_loop.execption'||l_error_code;
204 
205                   fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
206                   fnd_message.set_token('INTERFACE_ID',notes_rec.interface_notes_id);
207                   fnd_message.set_token('ERROR_CD',l_error_code);
208 
209                   l_debug_str :=  fnd_message.get;
210                   fnd_log.string_with_context( fnd_log.level_exception,
211                                                      l_label,
212                                                      l_debug_str, NULL,
213                                                      NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
214                 END IF;
215               END IF;
216 
217 
218 
219               UPDATE igs_ad_notes_int
220               SET    status = cst_s_val_3
221                      , error_code = l_error_code
222                      , error_text = l_error_text
223               WHERE rowid = notes_rec.rowid;
224               l_records_processed := l_records_processed + 1;
225           END;
226         END IF;
227 
228       ELSE
229         FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NOT_APC_STEP');
230         FND_MESSAGE.SET_TOKEN ('CATEGORY', p_category_meaning);
231         FND_MESSAGE.SET_TOKEN ('APPLTYPE', notes_rec.admission_application_type);
232         l_error_text := FND_MESSAGE.GET;
233 
234         UPDATE igs_ad_notes_int
235         SET    status = cst_s_val_3
236                , error_code = cst_ec_val_E701
237                , error_text = l_error_text
238         WHERE rowid = notes_rec.rowid;
239         l_records_processed := l_records_processed + 1;
240       END IF;
241       IF l_records_processed = 100 THEN
242         COMMIT;
243         l_records_processed := 0;
244       END IF;
245     END LOOP;
246 
247     IF l_records_processed < 100 AND l_records_processed > 0 THEN
248       COMMIT;
249     END IF;
250   END crt_apcnt_notes;
251 	-- Local Procedure crt_apcnt_notes end here
252 BEGIN
253 
254   l_prog_label := 'igs.plsql.igs_ad_imp_010.admp_val_pappl_nots';
255   l_label := 'igs.plsql.igs_ad_imp_010.admp_val_pappl_nots.';
256   l_request_id := fnd_global.conc_request_id;
257 
258   IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
259     l_label := 'igs.plsql.igs_ad_imp_010.admp_val_pappl_nots.begin';
260     l_debug_str :=  'igs_ad_imp_010.admp_val_pappl_nots';
261     fnd_log.string_with_context(fnd_log.level_procedure,
262                                 l_label,
263 			        l_debug_str, NULL,
264 			        NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
265   END IF;
266 
267   -- Set STATUS to 3 when duplicate record is found
268 
269   UPDATE igs_ad_notes_int in_rec
270   SET
271          status = cst_s_val_3
272          , error_code = cst_ec_val_E678
273          , error_text = cst_et_val_E678
274   WHERE interface_run_id = p_interface_run_id
275   AND status = cst_s_val_2
276   AND EXISTS ( SELECT 1
277                FROM igs_ad_appl_notes mn_rec
278                WHERE mn_rec.person_id = in_rec.person_id
279                AND  mn_rec.admission_appl_number = in_rec.admission_appl_number
280                AND  mn_rec.nominated_course_cd = in_rec.nominated_course_cd
281                AND  mn_rec.note_type_id = in_rec.note_type_id
282                AND  mn_rec.sequence_number = in_rec.sequence_number);
283   COMMIT;
284 
285   -- Create  the OSS record after validating successfully the interface record
286   crt_apcnt_notes( p_interface_run_id);
287 
288 END admp_val_pappl_nots ;
289 
290 PROCEDURE  prcs_applnt_edu_goal_dtls(p_interface_run_id  IN NUMBER,
291                                      p_enable_log        IN VARCHAR2,
292                                      p_category_meaning  IN VARCHAR2,
293                                      p_rule              IN VARCHAR2 ) AS
294 
295   l_prog_label  VARCHAR2(100);
296   l_label  VARCHAR2(100);
297   l_debug_str VARCHAR2(2000);
298   l_request_id NUMBER;
299   l_error_code  igs_ad_edugoal_int.error_code%TYPE;
300   l_records_processed NUMBER := 0;
301 
302   --
303   -- Start of Local Procedure create_applicant_edu_goals
304   --
305   PROCEDURE create_applicant_edu_goals(
306                        p_interface_run_id  IN NUMBER ) IS
307 
308     CURSOR c_igs_ad_edugoal_int IS
309     SELECT  cst_insert dmlmode, rowid, a.*
310     FROM igs_ad_edugoal_int a
311     WHERE interface_run_id = p_interface_run_id
312     AND status = cst_s_val_2;
313 
314     l_rowid ROWID;
315     l_post_edugoal_id igs_ad_edugoal.post_edugoal_id%TYPE;
316     l_msg_at_index    NUMBER := 0;
317     l_return_status   VARCHAR2(1);
318     l_error_text    VARCHAR2(2000);
319     l_msg_count       NUMBER ;
320     l_msg_data        VARCHAR2(2000);
321     l_hash_msg_name_text_type_tab   igs_ad_gen_016.g_msg_name_text_type_table;
322 
323     l_admission_cat igs_ad_appl.admission_cat%TYPE;
324     l_s_admission_process_type igs_ad_appl.s_admission_process_type%TYPE;
325 
326   BEGIN
327 
328     IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
329      l_label := 'igs.plsql.igs_ad_imp_010.prcs_applnt_edu_goal_dtls.create_applicant_edu_goals';
330      l_debug_str :=  'Interface Run ID' || p_interface_run_id;
331      fnd_log.string_with_context( fnd_log.level_procedure,
332   		       l_label,
333 		       l_debug_str, NULL,
334 		       NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
335     END IF;
336 
337     l_records_processed := 0;
338 
339     FOR edugoal_rec IN c_igs_ad_edugoal_int
340     LOOP
341       IF igs_ad_gen_016.get_appl_type_apc (p_application_type         => edugoal_rec.admission_application_type,
342                                            p_admission_cat            => l_admission_cat,
343                                            p_s_admission_process_type => l_s_admission_process_type) = 'TRUE' THEN
344 
345         IF igs_ad_gen_016.get_apcs (p_admission_cat            => l_admission_cat,
346                                     p_s_admission_process_type => l_s_admission_process_type,
347                                     p_s_admission_step_type    => 'EDU-GOALS') = 'FALSE' THEN
348           FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NOT_APC_STEP');
349           FND_MESSAGE.SET_TOKEN ('CATEGORY', p_category_meaning);
350           FND_MESSAGE.SET_TOKEN ('APPLTYPE', edugoal_rec.admission_application_type);
351           l_error_text := FND_MESSAGE.GET;
352           UPDATE igs_ad_edugoal_int
353           SET
354             status = cst_s_val_3
355             , error_code = cst_ec_val_E701
356             , error_text = l_error_text
357             WHERE rowid = edugoal_rec.rowid;
358             l_records_processed := l_records_processed + 1;
359         ELSE
360           BEGIN
361             SAVEPOINT before_create;
362             l_msg_at_index := igs_ge_msg_stack.count_msg;
363 
364             igs_ad_edugoal_pkg.insert_row
365                        (
366                                X_ROWID                        => l_rowid,
367                                X_POST_EDUGOAL_ID              => l_post_edugoal_id,
368                                X_PERSON_ID                    => edugoal_rec.person_id,
369                                X_ADMISSION_APPL_NUMBER        => edugoal_rec.admission_appl_number,
370                                X_NOMINATED_COURSE_CD          => edugoal_rec.nominated_course_cd,
371                                X_SEQUENCE_NUMBER              => edugoal_rec.sequence_number,
372                                X_EDU_GOAL_ID                  => edugoal_rec.edu_goal_id,
373                                X_MODE                         => 'R'
374                        );
375             UPDATE igs_ad_edugoal_int
376             SET    status = cst_s_val_1
377             WHERE rowid = edugoal_rec.rowid;
378 
379             l_records_processed := l_records_processed + 1;
380 
381             IF l_records_processed = 100 THEN
382               COMMIT;
383               l_records_processed := 0;
384             END IF;
385 
386           EXCEPTION
387             WHEN OTHERS THEN
388               ROLLBACK TO before_create;
389               l_error_code := 'E322';
390               l_msg_data := SQLERRM;
391               igs_ad_gen_016.extract_msg_from_stack (
392                       p_msg_at_index                => l_msg_at_index,
393                       p_return_status               => l_return_status,
394                       p_msg_count                   => l_msg_count,
395                       p_msg_data                    => l_msg_data,
396                       p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
397 
398               l_error_text := NVL(l_msg_data,igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405));
399 
400               IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <>  'ORA'  THEN
401                 IF p_enable_log = 'Y' THEN
402                   igs_ad_imp_001.logerrormessage(edugoal_rec.interface_edugoal_id,l_msg_data,'IGS_AD_EDUGOAL_INT');
403                 END IF;
404               ELSE
405                 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
406                   l_label := 'igs.plsql.igs_ad_imp_010.prcs_applnt_edu_goal_dtls.create_applicant_edu_goals.for_loop.execption'||l_error_code;
407                   fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
408                   fnd_message.set_token('INTERFACE_ID',edugoal_rec.interface_edugoal_id);
409         	  fnd_message.set_token('ERROR_CD',l_error_code);
410                   l_debug_str :=  fnd_message.get;
411                   fnd_log.string_with_context( fnd_log.level_exception,
412                                                l_label,
413                                                l_debug_str, NULL,
414                                                NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
415                 END IF;
416               END IF;
417 
418               UPDATE igs_ad_edugoal_int
419               SET    status = cst_s_val_3
420                      ,error_code = l_error_code
421                      ,error_text = l_error_text
422               WHERE rowid = edugoal_rec.rowid;
423               l_records_processed := l_records_processed + 1;
424           END;
425         END IF;
426       ELSE
427         FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NOT_APC_STEP');
428         FND_MESSAGE.SET_TOKEN ('CATEGORY', p_category_meaning);
429         FND_MESSAGE.SET_TOKEN ('APPLTYPE', edugoal_rec.admission_application_type);
430         l_error_text := FND_MESSAGE.GET;
431         UPDATE igs_ad_edugoal_int
432         SET
433           status = cst_s_val_3
434           , error_code = cst_ec_val_E701
435           , error_text = l_error_text
436         WHERE rowid = edugoal_rec.rowid;
437         l_records_processed := l_records_processed + 1;
438       END IF;
439 
440       IF l_records_processed = 100 THEN
441         COMMIT;
442         l_records_processed := 0;
443       END IF;
444     END LOOP;
445     IF l_records_processed < 100 AND l_records_processed > 0 THEN
446       COMMIT;
447     END IF;
448   END create_applicant_edu_goals;
449 
450 BEGIN
451 
452   l_prog_label := 'igs.plsql.igs_ad_imp_010.prcs_applnt_edu_goal_dtls';
453   l_label := 'igs.plsql.igs_ad_imp_010.prcs_applnt_edu_goal_dtls.';
454   l_request_id := fnd_global.conc_request_id;
455 
456   IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
457     l_label := 'igs.plsql.igs_ad_imp_010.prcs_applnt_edu_goal_dtls.begin';
458     l_debug_str :=  'igs_ad_imp_010.prcs_applnt_edu_goal_dtls';
459     fnd_log.string_with_context( fnd_log.level_procedure,
460                                  l_label,
461 			         l_debug_str, NULL,
462 			         NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
463   END IF;
464 
465   -- Set STATUS to 3 when duplicate record is found
466 
467   UPDATE igs_ad_edugoal_int in_rec
468   SET    status = cst_s_val_3
469          ,error_code = cst_ec_val_E678
470          ,error_text = cst_et_val_E678
471   WHERE interface_run_id = p_interface_run_id
472   AND status = cst_s_val_2
473   AND EXISTS ( SELECT 1
474                FROM igs_ad_edugoal mn_rec
475                WHERE mn_rec.person_id = in_rec.person_id
476                AND  mn_rec.admission_appl_number = in_rec.admission_appl_number
477                AND  mn_rec.nominated_course_cd = in_rec.nominated_course_cd
478                AND  mn_rec.edu_goal_id = in_rec.edu_goal_id
479                AND  mn_rec.sequence_number = in_rec.sequence_number);
480   COMMIT;
481 
482   -- Create  the OSS record after validating successfully the interface record
483   create_applicant_edu_goals( p_interface_run_id);
484 
485 END prcs_applnt_edu_goal_dtls;
486 
487 PROCEDURE prc_apcnt_uset_apl( p_interface_run_id  IN NUMBER,
488                               p_enable_log        IN VARCHAR2,
489                               p_category_meaning  IN VARCHAR2,
490                               p_rule              IN VARCHAR2 ) AS
491 
492   l_prog_label  VARCHAR2(100);
493   l_label  VARCHAR2(100);
494   l_debug_str VARCHAR2(2000);
495   l_request_id NUMBER;
496   l_error_code  igs_ad_unitsets_int.error_code%TYPE;
497 
498 
499   PROCEDURE crt_upd_apcnt_uset_apl(p_interface_run_id NUMBER) AS
500 
501     CURSOR c_igs_ad_unitsets_int IS
502     SELECT  cst_insert dmlmode, rowid, in_rec.*
503     FROM igs_ad_unitsets_int in_rec
504     WHERE interface_run_id = p_interface_run_id
505     AND status = cst_s_val_2
506     AND ( ( NVL(match_ind,'15') = '15'
507             AND NOT EXISTS ( SELECT 1
508                              FROM igs_ad_unit_sets mn_rec
509                              WHERE mn_rec.person_id = in_rec.person_id
510                              AND   mn_rec.sequence_number = in_rec.sequence_number
511                              AND   mn_rec.unit_set_cd = in_rec.unit_set_cd
512                              AND   mn_rec.version_number = in_rec.version_number
513                              AND   mn_rec.admission_appl_number = in_rec.admission_appl_number
514                              AND   mn_rec.nominated_course_cd = in_rec.nominated_course_cd))
515          OR (p_rule = cst_rule_val_R
516              AND match_ind IN (cst_mi_val_16, cst_mi_val_25)))
517     UNION ALL
518     SELECT  cst_update dmlmode, rowid, in_rec.*
519     FROM igs_ad_unitsets_int in_rec
520     WHERE interface_run_id = p_interface_run_id
521     AND status = cst_s_val_2
522     AND (   (p_rule = cst_rule_val_I)
523          OR (p_rule = cst_rule_val_R AND match_ind = cst_mi_val_21))
524     AND EXISTS ( SELECT 1
525                  FROM igs_ad_unit_sets mn_rec
526                  WHERE mn_rec.person_id = in_rec.person_id
527                  AND   mn_rec.sequence_number = in_rec.sequence_number
528                  AND   mn_rec.unit_set_cd = in_rec.unit_set_cd
529                  AND   mn_rec.version_number = in_rec.version_number
530                  AND   mn_rec.admission_appl_number = in_rec.admission_appl_number
531                  AND   mn_rec.nominated_course_cd = in_rec.nominated_course_cd);
532 
533    CURSOR c_null_hdlg_unitsets_cur_rec(cp_unit_set_cur c_igs_ad_unitsets_int%ROWTYPE) IS
534    SELECT ROWID, mn_rec.*
535    FROM igs_ad_unit_sets mn_rec
536    WHERE mn_rec.person_id = cp_unit_set_cur.person_id
537    AND   mn_rec.sequence_number = cp_unit_set_cur.sequence_number
538    AND   mn_rec.unit_set_cd = cp_unit_set_cur.unit_set_cd
539    AND   mn_rec.version_number = cp_unit_set_cur.version_number
540    AND   mn_rec.admission_appl_number = cp_unit_set_cur.admission_appl_number
541    AND   mn_rec.nominated_course_cd = cp_unit_set_cur.nominated_course_cd;
542 
543    c_null_hdlg_unitsets_rec c_null_hdlg_unitsets_cur_rec%ROWTYPE;
544 
545    l_error_code       VARCHAR2(30);
546    l_msg_at_index   NUMBER := 0;
547    l_return_status   VARCHAR2(1);
548    l_error_text    VARCHAR2(2000);
549    l_msg_count      NUMBER ;
550    l_msg_data       VARCHAR2(2000);
551    l_hash_msg_name_text_type_tab   igs_ad_gen_016.g_msg_name_text_type_table;
552 
553    l_records_processed  NUMBER;
554    l_rowid VARCHAR2(30);
555    l_unit_set_id igs_ad_unit_sets.unit_set_id%TYPE;
556 
557    l_admission_cat igs_ad_appl.admission_cat%TYPE;
558    l_s_admission_process_type igs_ad_appl.s_admission_process_type%TYPE;
559 
560  BEGIN
561 
562    IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
563      l_label := 'igs.plsql.igs_ad_imp_014.prc_apcnt_uset_apl.crt_upd_apcnt_uset_apl';
564      l_debug_str :=  'Interface Run ID' || p_interface_run_id;
565      fnd_log.string_with_context( fnd_log.level_procedure,
566                                   l_label,
567 			          l_debug_str, NULL,
568 			          NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
569    END IF;
570    l_records_processed := 0;
571 
572    FOR unitsets_rec IN c_igs_ad_unitsets_int
573    LOOP
574      IF igs_ad_gen_016.get_appl_type_apc (p_application_type         => unitsets_rec.admission_application_type,
575                                         p_admission_cat            => l_admission_cat,
576                                         p_s_admission_process_type => l_s_admission_process_type) = 'TRUE' THEN
577      IF igs_ad_gen_016.get_apcs (p_admission_cat            => l_admission_cat,
578                                  p_s_admission_process_type => l_s_admission_process_type,
579                                  p_s_admission_step_type    => 'DES-UNITSETS') = 'FALSE' THEN
580 
581        FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NOT_APC_STEP');
582        FND_MESSAGE.SET_TOKEN ('CATEGORY', p_category_meaning);
583        FND_MESSAGE.SET_TOKEN ('APPLTYPE', unitsets_rec.admission_application_type);
584        l_error_text := FND_MESSAGE.GET;
585 
586        UPDATE igs_ad_unitsets_int
587        SET    status = cst_s_val_3
588               ,match_ind = DECODE (unitsets_rec.dmlmode,  cst_update, DECODE (match_ind,NULL, cst_mi_val_12,match_ind),
589                                      cst_insert, DECODE (match_ind,NULL, cst_mi_val_11,match_ind))
590               ,error_code = cst_ec_val_E701
591               , error_text = l_error_text
592        WHERE rowid = unitsets_rec.rowid;
593 
594        l_records_processed := l_records_processed + 1;
595      ELSE
596        BEGIN
597          SAVEPOINT before_creatupdate;
598            l_msg_at_index := igs_ge_msg_stack.count_msg;
599 
600            IF unitsets_rec.dmlmode = cst_insert THEN
601              igs_ad_unit_sets_pkg.INSERT_ROW (
602 	       x_rowid => l_rowid,
603 	       x_unit_set_id => l_unit_set_id,
604 	       x_person_id => unitsets_rec.person_id,
605 	       x_admission_appl_number => unitsets_rec.admission_appl_number,
606 	       x_nominated_course_cd => unitsets_rec.nominated_course_cd,
607 	       x_sequence_number => unitsets_rec.sequence_number,
608 	       x_unit_set_cd => unitsets_rec.unit_set_cd,
609 	       x_version_number => unitsets_rec.version_number,
610 	       x_rank => unitsets_rec.rank,
611 	       x_mode =>  'R'
612 	     );
613            ELSIF unitsets_rec.dmlmode = cst_update THEN
614              OPEN   c_null_hdlg_unitsets_cur_rec(unitsets_rec);
615              FETCH c_null_hdlg_unitsets_cur_rec INTO c_null_hdlg_unitsets_rec;
616              CLOSE c_null_hdlg_unitsets_cur_rec;
617 
618              igs_ad_unit_sets_pkg.update_row(
619                 x_rowid => c_null_hdlg_unitsets_rec.rowid,
620                 x_unit_set_id => c_null_hdlg_unitsets_rec.unit_set_id,
621                 x_person_id => NVL(unitsets_rec.person_id,c_null_hdlg_unitsets_rec.person_id),
622                 x_admission_appl_number=> NVL(unitsets_rec.admission_appl_number,c_null_hdlg_unitsets_rec.admission_appl_number),
623                 x_nominated_course_cd => NVL(unitsets_rec.nominated_course_cd, c_null_hdlg_unitsets_rec.nominated_course_cd),
624                 x_sequence_number => NVL(unitsets_rec.sequence_number,c_null_hdlg_unitsets_rec.sequence_number),
625                 x_unit_set_cd => NVL(unitsets_rec.unit_set_cd,c_null_hdlg_unitsets_rec.unit_set_cd),
626                 x_version_number => NVL(unitsets_rec.version_number,c_null_hdlg_unitsets_rec.version_number),
627                 x_rank   => NVL(unitsets_rec.rank,c_null_hdlg_unitsets_rec.rank),
628                 x_mode =>'R');
629            END IF;
630 
631            UPDATE igs_ad_unitsets_int
632            SET
633            status = cst_s_val_1
634            , match_ind = DECODE (unitsets_rec.dmlmode,cst_update, cst_mi_val_18,cst_insert, cst_mi_val_11)
635            WHERE rowid = unitsets_rec.rowid;
636 
637            l_records_processed := l_records_processed + 1;
638 
639            IF l_records_processed = 100 THEN
640             COMMIT;
641             l_records_processed := 0;
642            END IF;
643 
644            EXCEPTION
645              WHEN OTHERS THEN
646                ROLLBACK TO before_creatupdate;
647                l_msg_data := SQLERRM;
648 
649                IF unitsets_rec.dmlmode = cst_insert THEN
650                  l_error_code := 'E322'; -- Insertion Failed
651                ELSIF unitsets_rec.dmlmode = cst_update THEN
652                  l_error_code := 'E014'; -- Update Failed
653                END IF;
654 
655                igs_ad_gen_016.extract_msg_from_stack (p_msg_at_index                => l_msg_at_index,
656                                                       p_return_status               => l_return_status,
657                                                       p_msg_count                   => l_msg_count,
658                                                       p_msg_data                    => l_msg_data,
659                                                       p_hash_msg_name_text_type_tab => l_hash_msg_name_text_type_tab);
660                l_error_text := NVL(l_msg_data,igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', l_error_code, 8405));
661 
662                IF l_hash_msg_name_text_type_tab(l_msg_count-1).name <>  'ORA'  THEN
663                  IF p_enable_log = 'Y' THEN
664                    igs_ad_imp_001.logerrormessage(unitsets_rec.interface_unitsets_id,l_msg_data,'IGS_AD_UNITSETS_INT');
665                  END IF;
666                ELSE
667                  IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
668 
669                    l_label := 'igs.plsql.igs_ad_imp_014.prc_apcnt_uset_apl.crt_upd_apcnt_uset_apl.for_loop.execption'||l_error_code;
670 
671                    fnd_message.set_name('IGS','IGS_PE_IMP_ERROR');
672         	   fnd_message.set_token('INTERFACE_ID',unitsets_rec.interface_unitsets_id);
673         	   fnd_message.set_token('ERROR_CD',l_error_code);
674 
675                    l_debug_str :=  fnd_message.get;
676                    fnd_log.string_with_context( fnd_log.level_exception,
677 		                                l_label,
678 						l_debug_str, NULL,
679 						NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
680                  END IF;
681 
682                END IF;
683 
684                UPDATE igs_ad_unitsets_int
685                SET    status = cst_s_val_3
686                       , match_ind = DECODE ( unitsets_rec.dmlmode
687                                              ,cst_update, DECODE ( match_ind, NULL, cst_mi_val_12, match_ind)
688                                              ,cst_insert, DECODE ( p_rule
689                                                                    ,cst_rule_val_R, DECODE ( match_ind, NULL, cst_mi_val_11, match_ind)
690                                                                    ,cst_mi_val_11))
691                       , error_code = l_error_code
692                       , error_text = l_error_text
693                WHERE rowid = unitsets_rec.rowid;
694                l_records_processed := l_records_processed + 1;
695            END;
696          END IF;
697 
698        ELSE
699          FND_MESSAGE.SET_NAME ('IGS', 'IGS_AD_NOT_APC_STEP');
700          FND_MESSAGE.SET_TOKEN ('CATEGORY', p_category_meaning);
701          FND_MESSAGE.SET_TOKEN ('APPLTYPE', unitsets_rec.admission_application_type);
702          l_error_text := FND_MESSAGE.GET;
703 
704          UPDATE igs_ad_unitsets_int
705          SET    status = cst_s_val_3
706                 , match_ind = DECODE (unitsets_rec.dmlmode,  cst_update, DECODE (match_ind,NULL, cst_mi_val_12,match_ind),
707                                                 cst_insert, DECODE (match_ind,NULL, cst_mi_val_11,match_ind))
708                 , error_code = cst_ec_val_E701
709                 , error_text = l_error_text
710          WHERE rowid = unitsets_rec.rowid;
711 
712          l_records_processed := l_records_processed + 1;
713        END IF;
714 
715        IF l_records_processed = 100 THEN
716          COMMIT;
717          l_records_processed := 0;
718        END IF;
719 
720      END LOOP;
721 
722      IF l_records_processed < 100 AND l_records_processed > 0 THEN
723        COMMIT;
724      END IF;
725 
726 END crt_upd_apcnt_uset_apl; -- End of local procedure crt_upd_apcnt_uset_apl.
727 
728 -- begin of main process prc_apcnt_uset_apl
729 BEGIN
730 
731   l_prog_label := 'igs.plsql.igs_ad_imp_014.prc_apcnt_uset_apl';
732   l_label := 'igs.plsql.igs_ad_imp_014.prc_apcnt_uset_apl.';
733   l_request_id := fnd_global.conc_request_id;
734 
735   IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
736     l_label := 'igs.plsql.igs_ad_imp_014.prc_apcnt_uset_apl.begin';
737     l_debug_str :=  'igs_ad_imp_014.prc_apcnt_uset_apl';
738     fnd_log.string_with_context( fnd_log.level_procedure,
739                                  l_label,
740 			         l_debug_str, NULL,
741 			         NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
742   END IF;
743 
744   -- Set STATUS to 3 for interface records with RULE = E or I and MATCH IND
745   IF p_rule IN ('E','I') THEN
746     UPDATE igs_ad_unitsets_int
747     SET    status = cst_s_val_3
748            , error_code = cst_ec_val_E700
749            , error_text = cst_et_val_E700
750     WHERE interface_run_id = p_interface_run_id
751     AND status = cst_s_val_2
752     AND NVL (match_ind, cst_mi_val_15) <> cst_mi_val_15;
753     COMMIT;
754   END IF;
755 
756   -- Set STATUS to 1 for interface records with RULE = R and
757   -- MATCH IND = 17,18,19,22,23,24,27
758   IF p_rule IN ('R') THEN
759     UPDATE igs_ad_unitsets_int
760     SET    status = cst_s_val_1
761     WHERE interface_run_id = p_interface_run_id
762     AND status = cst_s_val_2
763     AND match_ind IN (cst_mi_val_17, cst_mi_val_18, cst_mi_val_19,
764                       cst_mi_val_22, cst_mi_val_23, cst_mi_val_24, cst_mi_val_27);
765     COMMIT;
766   END IF;
767 
768   -- Set STATUS to 1 and MATCH IND to 19 for interface records with RULE =
769   -- E matching OSS record(s)
770   IF p_rule IN ('E') THEN
771     UPDATE igs_ad_unitsets_int in_rec
772     SET    status = cst_s_val_1
773            , match_ind = cst_mi_val_19
774     WHERE interface_run_id = p_interface_run_id
775     AND status = cst_s_val_2
776     AND EXISTS ( SELECT 1
777                  FROM igs_ad_unit_sets mn_rec
778                  WHERE mn_rec.person_id = in_rec.person_id
779                  AND   mn_rec.sequence_number = in_rec.sequence_number
780                  AND   mn_rec.unit_set_cd = in_rec.unit_set_cd
781                  AND   mn_rec.version_number = in_rec.version_number
782                  AND   mn_rec.admission_appl_number = in_rec.admission_appl_number
783                  AND   mn_rec.nominated_course_cd = in_rec.nominated_course_cd);
784     COMMIT;
785   END IF;
786 
787   -- Create / Update the OSS record after validating successfully the interface record
788   -- Create
789   -- If RULE E/I/R (match indicator will be 15 or NULL by now no need to check) and
790   -- matching system record not found OR RULE = R and MATCH IND = 16, 25
791   -- Update
792   -- If RULE = I (match indicator will be 15 or NULL by now no need to check) OR
793   -- RULE = R and MATCH IND = 21
794 
795   -- Selecting together the interface records for INSERT / UPDATE with DMLMODE identifying
796   -- the DML operation. This is done to have one code section for record validation, exception
797   -- handling and interface table update. This avoids call to separate PLSQL blocks, tuning
798   -- performance on stack maintenance during the process.
799 
800   crt_upd_apcnt_uset_apl(p_interface_run_id);
801 
802   -- Set STATUS to 1 and MATCH IND to 23 for interface records with RULE = R matching
803   -- OSS record(s) in ALL updateable column values, if column nullification is not
804   -- allowed then the 2 DECODE should be replaced by a single NVL
805   IF p_rule IN ('R') THEN
806     UPDATE igs_ad_unitsets_int in_rec
807     SET    status = cst_s_val_1
808            , match_ind = cst_mi_val_23
809     WHERE interface_run_id = p_interface_run_id
810     AND status = cst_s_val_2
811     AND NVL (match_ind, cst_mi_val_15) = cst_mi_val_15
812     AND EXISTS ( SELECT 1
813                  FROM igs_ad_unit_sets mn_rec
814                  WHERE NVL(mn_rec.person_id, -99)            = NVL(in_rec.person_id,NVL(mn_rec.person_id, -99) )
815                  AND  NVL(mn_rec.admission_appl_number, -99) = NVL(in_rec.admission_appl_number,NVL(mn_rec.admission_appl_number, -99) )
816                  AND  NVL(mn_rec.nominated_course_cd,'~')    = NVL(in_rec.nominated_course_cd, NVL(mn_rec.nominated_course_cd,'~') )
817                  AND  NVL(mn_rec.sequence_number, -99)       = NVL(in_rec.sequence_number, NVL(mn_rec.sequence_number, -99))
818                  AND  NVL(mn_rec.unit_set_cd, '~')           = NVL(in_rec.unit_set_cd,  NVL(mn_rec.unit_set_cd, '~'))
819                  AND  NVL(mn_rec.version_number, -99)        = NVL(in_rec.version_number,NVL(mn_rec.version_number, -99) )
820                  AND  NVL(mn_rec.rank, -99)                  = NVL(in_rec.rank, NVL(mn_rec.rank, -99))
821                 );
822     COMMIT;
823   END IF;
824 
825   -- Set STATUS to 3 and MATCH IND = 20 for interface records with RULE = R and MATCH IND
826   -- <> 21, 25, ones failed discrepancy check
827   IF p_rule IN ('R') THEN
828     UPDATE igs_ad_unitsets_int in_rec
829     SET
830     status = cst_s_val_3
831     , match_ind = cst_mi_val_20
832     WHERE interface_run_id = p_interface_run_id
833     AND status = cst_s_val_2
834     AND NVL (match_ind, cst_mi_val_15) = cst_mi_val_15
835     AND EXISTS ( SELECT rowid
836                  FROM igs_ad_unit_sets mn_rec
837                  WHERE mn_rec.person_id = in_rec.person_id
838                  AND   mn_rec.sequence_number = in_rec.sequence_number
839                  AND   mn_rec.unit_set_cd = in_rec.unit_set_cd
840                  AND   mn_rec.version_number = in_rec.version_number
841                  AND   mn_rec.admission_appl_number = in_rec.admission_appl_number
842                  AND   mn_rec.nominated_course_cd = in_rec.nominated_course_cd);
843     COMMIT;
844   END IF;
845 
846   -- Set STATUS to 3 for interface records with RULE = R and invalid MATCH IND
847   IF p_rule IN ('R') THEN
848     UPDATE igs_ad_unitsets_int
849     SET    status = cst_s_val_3
850            , error_code = cst_ec_val_E700
851            , error_text = cst_et_val_E700
852     WHERE interface_run_id = p_interface_run_id
853     AND status = cst_s_val_2
854     AND match_ind IS NOT NULL;
855     COMMIT;
856   END IF;
857 
858 END prc_apcnt_uset_apl;
859 
860 END Igs_Ad_Imp_010;