[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_IMP_015
Source
1 PACKAGE BODY igs_ad_imp_015 AS
2 /* $Header: IGSAD93B.pls 120.2 2006/04/13 05:52:53 stammine ship $ */
3
4 /******************************************************************
5 Created By : knag
6 Date Created By : 05-NOV-2003
7 Purpose:
8 Known limitations,enhancements,remarks:
9 Change History
10 Who When What
11 rbezawad 27-Feb-05 Added code to procedures prc_ad_category(), del_cmpld_ad_records() to execute a Dynamic Code block
12 when IGR functionality is enabled. In store_ad_stats() procedure, changed Recruitment table
13 names to IGR_% naming convention. Also deleted package variables related to inquiry.
14 ******************************************************************/
15 -- These are the package variables to hold the value of whether the particular category is included or not.
16 g_application_inc BOOLEAN := FALSE;
17 g_person_qual_inc BOOLEAN := FALSE;
18 g_person_recruit_dtls_inc BOOLEAN := FALSE;
19 g_test_result_inc BOOLEAN := FALSE;
20 g_transcript_dtls_inc BOOLEAN := FALSE;
21 g_applicant_oth_inst_appl_inc BOOLEAN := FALSE;
22 g_applicant_acad_int_inc BOOLEAN := FALSE;
23 g_applicant_appl_intent_inc BOOLEAN := FALSE;
24 g_applicant_spl_int_inc BOOLEAN := FALSE;
25 g_applicant_spl_tal_inc BOOLEAN := FALSE;
26 g_applicant_per_stat_inc BOOLEAN := FALSE;
27 g_applicant_fee_dtls_inc BOOLEAN := FALSE;
28 g_applicant_notes_inc BOOLEAN := FALSE;
29 g_applicant_des_unit_sets_inc BOOLEAN := FALSE;
30 g_applicant_edu_goal_inc BOOLEAN := FALSE;
31 g_applicant_hist_inc BOOLEAN := FALSE;
32
33 PROCEDURE sel_ad_src_cat_imp (p_source_type_id IN NUMBER,
34 p_batch_id IN NUMBER,
35 p_enable_log IN VARCHAR2,
36 p_legacy_ind IN VARCHAR2
37 ) AS
38 /*************************************************************
39 Created By : knag
40 Date Created By : 05-NOV-2003
41 Purpose : This procedure gets called at the beginning of import process.
42 The package variables are initialized here as per the categories included and then used further.
43 Know limitations, enhancements or remarks
44 Change History
45 Who When What
46 (reverse chronological order - newest change first)
47 ***************************************************************/
48 l_prog_label VARCHAR2(4000);
49 l_label VARCHAR2(4000);
50 l_request_id NUMBER;
51 l_debug_str VARCHAR2(4000);
52 BEGIN
53 l_prog_label := 'igs.plsql.igs_ad_imp_015.sel_ad_src_cat_imp';
54 l_label := 'igs.plsql.igs_ad_imp_015.sel_ad_src_cat_imp.';
55
56 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
57
58 IF (l_request_id IS NULL) THEN
59 l_request_id := fnd_global.conc_request_id;
60 END IF;
61
62 l_label := 'igs.plsql.igs_ad_imp_015.sel_ad_src_cat_imp.begin';
63 l_debug_str := 'Source Type Id : ' || p_source_type_id || ' Batch ID : ' || p_batch_id;
64
65 fnd_log.string_with_context (fnd_log.level_procedure,
66 l_label,
67 l_debug_str,
68 NULL,NULL,NULL,NULL,NULL,
69 TO_CHAR(l_request_id));
70 END IF;
71
72 g_person_qual_inc := igs_ad_gen_016.chk_src_cat (p_source_type_id, 'PERSON_QUAL');
73 IF g_person_qual_inc AND p_legacy_ind = 'N' THEN
74 g_person_qual_inc := FALSE;
75 END IF;
76 g_person_recruit_dtls_inc := igs_ad_gen_016.chk_src_cat (p_source_type_id, 'PERSON_RECRUITMENT_DETAILS');
77
78 g_test_result_inc := igs_ad_gen_016.chk_src_cat (p_source_type_id, 'TEST_RESULTS');
79 g_transcript_dtls_inc := igs_ad_gen_016.chk_src_cat (p_source_type_id, 'TRANSCRIPT_DETAILS');
80
81 g_application_inc := igs_ad_gen_016.chk_src_cat (p_source_type_id, 'APPLICATION');
82 g_applicant_oth_inst_appl_inc := igs_ad_gen_016.chk_src_cat (p_source_type_id, 'APPLICANT_OTHERINSTS_APPLIED');
83 g_applicant_acad_int_inc := igs_ad_gen_016.chk_src_cat (p_source_type_id, 'APPLICANT_ACADEMIC_INTERESTS');
84 g_applicant_appl_intent_inc := igs_ad_gen_016.chk_src_cat (p_source_type_id, 'APPLICANT_INTENT');
85 g_applicant_spl_int_inc := igs_ad_gen_016.chk_src_cat (p_source_type_id, 'APPLICANT_SPECIAL_INTERESTS');
86 g_applicant_spl_tal_inc := igs_ad_gen_016.chk_src_cat (p_source_type_id, 'APPLICANT_SPECIAL_TALENTS');
87 g_applicant_per_stat_inc := igs_ad_gen_016.chk_src_cat (p_source_type_id, 'APPLICANT_PERSONAL_STATEMENTS');
88 g_applicant_fee_dtls_inc := igs_ad_gen_016.chk_src_cat (p_source_type_id, 'APPLICANT_FEE_DTLS');
89 g_applicant_notes_inc := igs_ad_gen_016.chk_src_cat (p_source_type_id, 'APPLICANT_NOTES');
90 g_applicant_des_unit_sets_inc := igs_ad_gen_016.chk_src_cat (p_source_type_id, 'APPLICANT_UNITSETS_APPLIED');
91 g_applicant_edu_goal_inc := igs_ad_gen_016.chk_src_cat (p_source_type_id, 'APPLICANT_EDU_GOALS' );
92 g_applicant_hist_inc := igs_ad_gen_016.chk_src_cat (p_source_type_id, 'APPLICANT_HISTORY');
93 IF g_applicant_hist_inc AND p_legacy_ind = 'N' THEN
94 g_applicant_hist_inc := FALSE;
95 END IF;
96
97 END sel_ad_src_cat_imp;
98
99 PROCEDURE prc_ad_category (p_source_type_id IN NUMBER,
100 p_batch_id IN NUMBER,
101 p_interface_run_id IN NUMBER,
102 p_enable_log IN VARCHAR2,
103 p_legacy_ind IN VARCHAR2
104 ) AS
105 /*************************************************************
106 Created By : knag
107 Date Created By : 05-NOV-2003
108 Purpose : This procedure will call all the procedures for admission and inquiry related categories
109 Know limitations, enhancements or remarks
110 Change History
111 Who When What
112 rbezawad 27-Feb-05 Added code to procedure prc_ad_category() to execute a Dynamic Code block
113 when IGR functionality is enabled
114 (reverse chronological order - newest change first)
115 ***************************************************************/
116 l_prog_label VARCHAR2(4000);
117 l_label VARCHAR2(4000);
118 l_request_id NUMBER;
119 l_debug_str VARCHAR2(4000);
120
121 l_return BOOLEAN;
122 l_status VARCHAR2(5);
123 l_industry VARCHAR2(5);
124 l_schema VARCHAR2(30);
125
126 l_meaning igs_lookup_values.meaning%TYPE;
127 l_stmt VARCHAR2(2000);
128 l_system_source_type igs_pe_src_types_all.system_source_type%TYPE;
129
130 CURSOR c_system_source_type IS
131 SELECT system_source_type
132 FROM igs_pe_src_types_all
133 WHERE source_type_id = p_source_type_id
134 AND NVL(closed_ind,'N') = 'N';
135
136 BEGIN
137
138 -- Select categories for import
139 igs_ad_imp_015.sel_ad_src_cat_imp (p_source_type_id => p_source_type_id,
140 p_batch_id => p_batch_id,
141 p_enable_log => p_enable_log,
142 p_legacy_ind => p_legacy_ind);
143
144 l_prog_label := 'igs.plsql.igs_ad_imp_015.prc_ad_category';
145 l_label := 'igs.plsql.igs_ad_imp_015.prc_ad_category.';
146
147 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
148
149 IF (l_request_id IS NULL) THEN
150 l_request_id := fnd_global.conc_request_id;
151 END IF;
152
153 l_label := 'igs.plsql.igs_ad_imp_015.prc_ad_category.begin';
154 l_debug_str := 'Source Type Id : ' || p_source_type_id || ' Batch ID : ' || p_batch_id;
155
156 fnd_log.string_with_context (fnd_log.level_procedure,
157 l_label,
158 l_debug_str,
159 NULL,NULL,NULL,NULL,NULL,
160 TO_CHAR(l_request_id));
161 END IF;
162
163 -- To fetch table schema name for gather statistics
164 l_return := fnd_installation.get_app_info('IGS', l_status, l_industry, l_schema);
165
166 IF g_person_qual_inc THEN
167 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'PERSON_QUAL', 8405);
168
169 IF p_enable_log = 'Y' THEN
170 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
171 p_token_name => 'TYPE_NAME',
172 p_token_value => l_meaning);
173 END IF;
174
175 -- Populating the interface table with the interface_run_id value
176 UPDATE igs_uc_qual_ints a
177 SET interface_run_id = p_interface_run_id,
178 person_id = (SELECT person_id
179 FROM igs_ad_interface
180 WHERE interface_id = a.interface_id)
181 WHERE interface_id IN (SELECT interface_id
182 FROM igs_ad_interface
183 WHERE interface_run_id = p_interface_run_id
184 AND status IN ('1','4'));
185
186 -- Gather statistics of the table
187 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
188 tabname => 'IGS_UC_QUAL_INTS',
189 cascade => TRUE);
190
191 -- Call category entity import procedure
192 igs_ad_imp_028.prc_pe_qual_details (p_interface_run_id => p_interface_run_id,
193 p_enable_log => p_enable_log,
194 p_rule => 'N'); -- Update not yet supported
195
196 END IF; -- g_person_qual_inc
197
198 IF g_person_recruit_dtls_inc THEN
199 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'PERSON_RECRUITMENT_DETAILS', 8405);
200
201 IF p_enable_log = 'Y' THEN
202 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
203 p_token_name => 'TYPE_NAME',
204 p_token_value => l_meaning);
205 END IF;
206
207 -- Populating the interface table with the interface_run_id value
208 UPDATE igs_ad_recruit_int a
209 SET interface_run_id = p_interface_run_id,
210 person_id = (SELECT person_id
211 FROM igs_ad_interface
212 WHERE interface_id = a.interface_id)
213 WHERE interface_id IN (SELECT interface_id
214 FROM igs_ad_interface
215 WHERE interface_run_id = p_interface_run_id
216 AND status IN ('1','4'));
217
218 -- Gather statistics of the table
219 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
220 tabname => 'IGS_AD_RECRUIT_INT',
221 cascade => TRUE);
222
223 -- Call category entity import procedure
224 igs_ad_imp_014.prc_pe_recruitments_dtl (p_interface_run_id => p_interface_run_id,
225 p_enable_log => p_enable_log,
226 p_rule => igs_ad_gen_016.find_source_cat_rule (p_source_type_id, 'PERSON_RECRUITMENT_DETAILS'));
227
228 END IF; -- g_person_recruit_dtls_inc
229
230 --Dynamic Code block to be executed when IGR functionality is enabled.
231 IF (fnd_profile.value('IGS_RECRUITING_ENABLED') IS NULL OR fnd_profile.value('IGS_RECRUITING_ENABLED') = 'N') THEN
232
233 IF igs_ad_gen_016.chk_src_cat (p_source_type_id, 'INQUIRY_INSTANCE') = TRUE THEN
234 --Log error "Inquiry Instance related information is not Processed as Oracle Student Recruiting functionality is not enabled for the user".
235 fnd_file.put_line(fnd_file.log,FND_MESSAGE.GET_STRING('IGS','IGS_AD_INQ_NOT_PRCSD'));
236 ELSE
237 OPEN c_system_source_type;
238 FETCH c_system_source_type INTO l_system_source_type;
239 CLOSE c_system_source_type;
240
241 IF l_system_source_type = 'PROSPECT_SS_WEB_INQUIRY' OR l_system_source_type IS NULL THEN
242 --Log error "Inquiry Instance related information is not Processed as Oracle Student Recruiting functionality is not enabled for the user".
243 fnd_file.put_line(fnd_file.log,FND_MESSAGE.GET_STRING('IGS','IGS_AD_INQ_NOT_PRCSD'));
244 END IF;
245
246 END IF;
247 ELSIF fnd_profile.value('IGS_RECRUITING_ENABLED') = 'Y' THEN
248
249 BEGIN
250 l_stmt := ' BEGIN
251 igr_imp_002.prc_ad_category(:1,:2,:3,:4);
252 END; ';
253 EXECUTE IMMEDIATE l_stmt USING p_source_type_id, p_interface_run_id, p_enable_log, l_schema;
254 EXCEPTION
255 WHEN OTHERS THEN
256 fnd_file.put_line(fnd_file.log,'Error occurred while calling IGR_IMP_002.PRC_AD_CATEGORY() : '||SQLERRM);
257 END;
258
259 END IF;
260
261 IF g_test_result_inc THEN
262 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'TEST_RESULTS', 8405);
263
264 IF p_enable_log = 'Y' THEN
265 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
266 p_token_name => 'TYPE_NAME',
267 p_token_value => l_meaning);
268 END IF;
269
270 -- Populating the interface table with the interface_run_id value
271 UPDATE igs_ad_test_int a
272 SET interface_run_id = p_interface_run_id,
273 person_id = (SELECT person_id
274 FROM igs_ad_interface
275 WHERE interface_id = a.interface_id)
276 WHERE interface_id IN (SELECT interface_id
277 FROM igs_ad_interface
278 WHERE interface_run_id = p_interface_run_id
279 AND status IN ('1','4'));
280
281 -- If record failed only due to child record failure
282 -- then set status back to 1 and nullify error code/text
283 UPDATE igs_ad_test_int
284 SET error_code = NULL,
285 error_text = NULL,
286 status = '1'
287 WHERE interface_run_id = p_interface_run_id
288 AND error_code = 'E347'
289 AND status = '4';
290
291 -- Gather statistics of the table
292 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
293 tabname => 'IGS_AD_TEST_INT',
294 cascade => TRUE);
295
296 UPDATE igs_ad_test_segs_int
297 SET interface_run_id = p_interface_run_id
298 WHERE interface_test_id IN (SELECT interface_test_id
299 FROM igs_ad_test_int
300 WHERE interface_run_id = p_interface_run_id
301 AND status IN ('1','2','4'));
302
303 -- Gather statistics of the table
304 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
305 tabname => 'IGS_AD_TEST_SEGS_INT',
306 cascade => TRUE);
307
308 -- Call category entity import procedure
309 igs_ad_imp_016.prc_tst_rslts (p_interface_run_id => p_interface_run_id,
310 p_enable_log => p_enable_log,
311 p_rule => igs_ad_gen_016.find_source_cat_rule (p_source_type_id, 'TEST_RESULTS'));
312
313 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
314
315 IF (l_request_id IS NULL) THEN
316 l_request_id := fnd_global.conc_request_id;
317 END IF;
318
319 l_label := 'igs.plsql.igs_ad_imp_015.prc_ad_category.after_prc_tst_rslt';
320 l_debug_str := 'Test Results Processed Succesfully';
321
322 fnd_log.string_with_context (fnd_log.level_procedure,
323 l_label,
324 l_debug_str,
325 NULL,NULL,NULL,NULL,NULL,
326 TO_CHAR(l_request_id));
327 END IF;
328
329 END IF; -- g_test_result_inc
330
331 IF g_transcript_dtls_inc THEN
332 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'TRANSCRIPT_DETAILS', 8405);
333
334 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
335
336 IF (l_request_id IS NULL) THEN
337 l_request_id := fnd_global.conc_request_id;
338 END IF;
339
340 l_label := 'igs.plsql.igs_ad_imp_015.prc_ad_category.before_prc_transcript_dtls';
341 l_debug_str := 'Befoer Processing Transcript Details';
342
343 fnd_log.string_with_context (fnd_log.level_procedure,
344 l_label,
345 l_debug_str,
346 NULL,NULL,NULL,NULL,NULL,
347 TO_CHAR(l_request_id));
348 END IF;
349
350 IF p_enable_log = 'Y' THEN
351 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
352 p_token_name => 'TYPE_NAME',
353 p_token_value => l_meaning);
354 END IF;
355
356 -- If record failed only due to child record failure
357 -- then set status back to 1 and nullify error code/text
358 UPDATE igs_ad_acadhis_int_all
359 SET error_code = NULL,
360 error_text = NULL,
361 status = '1'
362 WHERE interface_run_id = p_interface_run_id
363 AND error_code = 'E347'
364 AND status = '4';
365
366 -- Populating the interface table with the interface_run_id value
367 UPDATE igs_ad_txcpt_int a
368 SET interface_run_id = p_interface_run_id,
369 (person_id,education_id)
370 = (SELECT person_id,NVL(education_id,update_education_id)
371 FROM igs_ad_acadhis_int_all
372 WHERE interface_acadhis_id = a.interface_acadhis_id)
373 WHERE interface_acadhis_id IN (SELECT interface_acadhis_id
374 FROM igs_ad_acadhis_int_all
375 WHERE interface_run_id = p_interface_run_id
376 AND status IN ('1','4'));
377
378 -- If record failed only due to child record failure
379 -- then set status back to 1 and nullify error code/text
380 UPDATE igs_ad_txcpt_int
381 SET error_code = NULL,
382 error_text = NULL,
383 status = '1'
384 WHERE interface_run_id = p_interface_run_id
385 AND error_code = 'E347'
386 AND status = '4';
387
388 -- Gather statistics of the table
389 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
390 tabname => 'IGS_AD_TXCPT_INT',
391 cascade => TRUE);
392
393 -- Call category entity import procedure
394 igs_ad_imp_024.prc_trscrpt (p_interface_run_id => p_interface_run_id,
395 p_enable_log => p_enable_log,
396 p_rule => igs_ad_gen_016.find_source_cat_rule (p_source_type_id, 'TRANSCRIPT_DETAILS'));
397
398 END IF; -- g_transcript_dtls_inc
399
400 IF g_application_inc THEN
401 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'APPLICATION', 8405);
402
403 IF p_enable_log = 'Y' THEN
404 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
405 p_token_name => 'TYPE_NAME',
406 p_token_value => l_meaning);
407 END IF;
408
409 -- Populating the interface table with the interface_run_id value
410 UPDATE igs_ad_apl_int a
411 SET interface_run_id = p_interface_run_id,
412 person_id = (SELECT person_id
413 FROM igs_ad_interface
414 WHERE interface_id = a.interface_id)
415 WHERE interface_id IN (SELECT interface_id
416 FROM igs_ad_interface
417 WHERE interface_run_id = p_interface_run_id
418 AND status IN ('1','4'));
419
420 -- If record failed only due to child record failure
421 -- then set status back to 1 and nullify error code/text
422 UPDATE igs_ad_apl_int
423 SET error_code = NULL,
424 error_text = NULL,
425 status = '1'
426 WHERE interface_run_id = p_interface_run_id
427 AND error_code = 'E347'
428 AND status = '4';
429
430 -- Gather statistics of the table
431 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
432 tabname => 'IGS_AD_APL_INT',
433 cascade => TRUE);
434
435 UPDATE igs_ad_ps_appl_inst_int a
436 SET interface_run_id = p_interface_run_id
437 WHERE interface_appl_id IN (SELECT interface_appl_id
438 FROM igs_ad_apl_int
439 WHERE interface_run_id = p_interface_run_id
440 AND update_adm_appl_number IS NULL
441 AND status IN ('1','2','4'));
442
443 -- If record failed only due to child record failure
444 -- then set status back to 1 and nullify error code/text
445 UPDATE igs_ad_ps_appl_inst_int
446 SET error_code = NULL,
447 error_text = NULL,
448 status = '1'
449 WHERE interface_run_id = p_interface_run_id
450 AND error_code = 'E347'
451 AND status = '4';
452
453 -- Gather statistics of the table
454 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
455 tabname => 'IGS_AD_PS_APPL_INST_INT',
456 cascade => TRUE);
457
458 -- ONLY required to identify presence of history records for which application/instance is being updated
459 -- Populating the application history interface table with the application context value
460 UPDATE igs_ad_apphist_int a
461 SET (person_id,admission_appl_number)
462 = (SELECT person_id,update_adm_appl_number
463 FROM igs_ad_apl_int
464 WHERE interface_appl_id = a.interface_appl_id)
465 WHERE status IN ('1','2','4')
466 AND interface_appl_id IN (SELECT interface_appl_id
467 FROM igs_ad_apl_int
468 WHERE interface_run_id = p_interface_run_id
469 AND status IN ('1','2','4'));
470
471 -- Populating the application instance history interface table with the application instance context value
472 UPDATE igs_ad_insthist_int a
473 SET (person_id,admission_appl_number,nominated_course_cd,sequence_number)
474 = (SELECT person_id,admission_appl_number,nominated_course_cd,update_adm_seq_number
475 FROM igs_ad_ps_appl_inst_int
476 WHERE interface_ps_appl_inst_id = a.interface_ps_appl_inst_id)
477 WHERE status IN ('1','2','4')
478 AND interface_ps_appl_inst_id IN (SELECT interface_ps_appl_inst_id
479 FROM igs_ad_ps_appl_inst_int
480 WHERE interface_run_id = p_interface_run_id
481 AND status IN ('1','2','4'));
482
483 -- Call category entity import procedure
484 igs_ad_imp_004.prc_appcln (p_interface_run_id => p_interface_run_id,
485 p_enable_log => p_enable_log,
486 p_rule => igs_ad_gen_016.find_source_cat_rule (p_source_type_id, 'APPLICATION'),
487 p_legacy_ind => p_legacy_ind);
488
489 UPDATE igs_ad_ps_appl_inst_int a
490 SET (person_id,admission_appl_number,admission_application_type)
491 = (SELECT person_id,NVL(admission_appl_number,update_adm_appl_number),admission_application_type
492 FROM igs_ad_apl_int
493 WHERE interface_appl_id = a.interface_appl_id)
494 WHERE status IN ('1','4')
495 AND interface_run_id = p_interface_run_id;
496
497 END IF; -- g_application_inc
498
499 IF g_applicant_hist_inc THEN
500 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'APPLICANT_HISTORY', 8405);
501
502 IF p_enable_log = 'Y' THEN
503 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
504 p_token_name => 'TYPE_NAME',
505 p_token_value => l_meaning);
506 END IF;
507
508 -- Populating the interface table with the interface_run_id value
509 UPDATE igs_ad_apphist_int a
510 SET interface_run_id = p_interface_run_id,
511 (person_id,admission_appl_number)
512 = (SELECT person_id,NVL(admission_appl_number,update_adm_appl_number)
513 FROM igs_ad_apl_int
514 WHERE interface_appl_id = a.interface_appl_id)
515 WHERE interface_appl_id IN (SELECT interface_appl_id
516 FROM igs_ad_apl_int
517 WHERE interface_run_id = p_interface_run_id
518 AND status IN ('1','4'));
519
520 -- Gather statistics of the table
521 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
522 tabname => 'IGS_AD_APPHIST_INT',
523 cascade => TRUE);
524
525 UPDATE igs_ad_insthist_int a
526 SET interface_run_id = p_interface_run_id,
527 (person_id,admission_appl_number,nominated_course_cd,sequence_number)
528 = (SELECT person_id,admission_appl_number,nominated_course_cd,NVL(sequence_number,update_adm_seq_number)
529 FROM igs_ad_ps_appl_inst_int
530 WHERE interface_ps_appl_inst_id = a.interface_ps_appl_inst_id)
531 WHERE interface_ps_appl_inst_id IN (SELECT interface_ps_appl_inst_id
532 FROM igs_ad_ps_appl_inst_int
533 WHERE interface_run_id = p_interface_run_id
534 AND status IN ('1','4'));
535
536 -- Gather statistics of the table
537 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
538 tabname => 'IGS_AD_INSTHIST_INT',
539 cascade => TRUE);
540
541 -- Call category entity import procedure
542 igs_ad_imp_027.prc_appl_hist (p_interface_run_id => p_interface_run_id,
543 p_enable_log => p_enable_log,
544 p_rule => 'N'); -- Update not yet supported
545
546 igs_ad_imp_027.prc_appl_inst_hist (p_interface_run_id => p_interface_run_id,
547 p_enable_log => p_enable_log,
548 p_rule => 'N'); -- Update not yet supported
549
550 END IF; -- g_applicant_hist_inc
551
552 IF g_applicant_oth_inst_appl_inc THEN
553 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'APPLICANT_OTHERINSTS_APPLIED', 8405);
554
555 IF p_enable_log = 'Y' THEN
556 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
557 p_token_name => 'TYPE_NAME',
558 p_token_value => l_meaning);
559 END IF;
560
561 -- Populating the interface table with the interface_run_id value
562 UPDATE igs_ad_othinst_int a
563 SET interface_run_id = p_interface_run_id,
564 (person_id,admission_appl_number,admission_Application_type)
565 = (SELECT person_id,NVL(admission_appl_number,update_adm_appl_number), admission_Application_type
566 FROM igs_ad_apl_int
567 WHERE interface_appl_id = a.interface_appl_id)
568 WHERE interface_appl_id IN (SELECT interface_appl_id
569 FROM igs_ad_apl_int
570 WHERE interface_run_id = p_interface_run_id
571 AND status IN ('1','4'));
572
573 -- Gather statistics of the table
574 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
575 tabname => 'IGS_AD_OTHINST_INT',
576 cascade => TRUE);
577
578 -- Call category entity import procedure
579 igs_ad_imp_003.prc_apcnt_oth_inst_apld (p_interface_run_id => p_interface_run_id,
580 p_enable_log => p_enable_log,
581 p_category_meaning => l_meaning,
582 p_rule => 'N'); -- Update not yet supported
583
584 END IF; -- g_applicant_oth_inst_appl_inc
585
586 IF g_applicant_acad_int_inc THEN
587 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'APPLICANT_ACADEMIC_INTERESTS', 8405);
588
589 IF p_enable_log = 'Y' THEN
590 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
591 p_token_name => 'TYPE_NAME',
592 p_token_value => l_meaning);
593 END IF;
594
595 -- Populating the interface table with the interface_run_id value
596 UPDATE igs_ad_acadint_int a
597 SET interface_run_id = p_interface_run_id,
598 (person_id,admission_appl_number, admission_Application_type)
599 = (SELECT person_id,NVL(admission_appl_number,update_adm_appl_number),admission_Application_type
600 FROM igs_ad_apl_int
601 WHERE interface_appl_id = a.interface_appl_id)
602 WHERE interface_appl_id IN (SELECT interface_appl_id
603 FROM igs_ad_apl_int
604 WHERE interface_run_id = p_interface_run_id
605 AND status IN ('1','4'));
606
607 -- Gather statistics of the table
608 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
609 tabname => 'IGS_AD_ACADINT_INT',
610 cascade => TRUE);
611
612 -- Call category entity import procedure
613 igs_ad_imp_003.prc_acad_int (p_interface_run_id => p_interface_run_id,
614 p_enable_log => p_enable_log,
615 p_category_meaning => l_meaning,
616 p_rule => 'N'); -- Update not yet supported
617
618 END IF; -- g_applicant_acad_int_inc
619
620 IF g_applicant_appl_intent_inc THEN
621 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'APPLICANT_INTENT', 8405);
622
623 IF p_enable_log = 'Y' THEN
624 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
625 p_token_name => 'TYPE_NAME',
626 p_token_value => l_meaning);
627 END IF;
628
629 -- Populating the interface table with the interface_run_id value
630 UPDATE igs_ad_appint_int a
631 SET interface_run_id = p_interface_run_id,
632 (person_id,admission_appl_number,admission_Application_type)
633 = (SELECT person_id,NVL(admission_appl_number,update_adm_appl_number),admission_Application_type
634 FROM igs_ad_apl_int
635 WHERE interface_appl_id = a.interface_appl_id)
636 WHERE interface_appl_id IN (SELECT interface_appl_id
637 FROM igs_ad_apl_int
638 WHERE interface_run_id = p_interface_run_id
639 AND status IN ('1','4'));
640
641 -- Gather statistics of the table
642 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
643 tabname => 'IGS_AD_APPINT_INT',
644 cascade => TRUE);
645
646 -- Call category entity import procedure
647 igs_ad_imp_003.prc_apcnt_indt (p_interface_run_id => p_interface_run_id,
648 p_enable_log => p_enable_log,
649 p_category_meaning => l_meaning,
650 p_rule => 'N'); -- Update not yet supported
651
652 END IF; -- g_applicant_appl_intent_inc
653
654 IF g_applicant_spl_int_inc THEN
655 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'APPLICANT_SPECIAL_INTERESTS', 8405);
656
657 IF p_enable_log = 'Y' THEN
658 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
659 p_token_name => 'TYPE_NAME',
660 p_token_value => l_meaning);
661 END IF;
662
663 -- Populating the interface table with the interface_run_id value
664 UPDATE igs_ad_splint_int a
665 SET interface_run_id = p_interface_run_id,
666 (person_id,admission_appl_number,admission_Application_type)
667 = (SELECT person_id,NVL(admission_appl_number,update_adm_appl_number),admission_Application_type
668 FROM igs_ad_apl_int
669 WHERE interface_appl_id = a.interface_appl_id)
670 WHERE interface_appl_id IN (SELECT interface_appl_id
671 FROM igs_ad_apl_int
672 WHERE interface_run_id = p_interface_run_id
673 AND status IN ('1','4'));
674
675 -- Gather statistics of the table
676 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
677 tabname => 'IGS_AD_SPLINT_INT',
678 cascade => TRUE);
679
680 -- Call category entity import procedure
681 igs_ad_imp_003.prc_apcnt_spl_intrst (p_interface_run_id => p_interface_run_id,
682 p_enable_log => p_enable_log,
683 p_category_meaning => l_meaning,
684 p_rule => 'N'); -- Update not yet supported
685
686 END IF; -- g_applicant_spl_int_inc
687
688 IF g_applicant_spl_tal_inc THEN
689 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'APPLICANT_SPECIAL_TALENTS', 8405);
690
691 IF p_enable_log = 'Y' THEN
692 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
693 p_token_name => 'TYPE_NAME',
694 p_token_value => l_meaning);
695 END IF;
696
697 -- Populating the interface table with the interface_run_id value
698 UPDATE igs_ad_spltal_int a
699 SET interface_run_id = p_interface_run_id,
700 (person_id,admission_appl_number,admission_Application_type)
701 = (SELECT person_id,NVL(admission_appl_number,update_adm_appl_number),admission_Application_type
702 FROM igs_ad_apl_int
703 WHERE interface_appl_id = a.interface_appl_id)
704 WHERE interface_appl_id IN (SELECT interface_appl_id
705 FROM igs_ad_apl_int
706 WHERE interface_run_id = p_interface_run_id
707 AND status IN ('1','4'));
708
709 -- Gather statistics of the table
710 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
711 tabname => 'IGS_AD_SPLTAL_INT',
712 cascade => TRUE);
713
714 -- Call category entity import procedure
715 igs_ad_imp_003.prc_apcnt_spl_tal (p_interface_run_id => p_interface_run_id,
716 p_enable_log => p_enable_log,
717 p_category_meaning => l_meaning,
718 p_rule => 'N'); -- Update not yet supported
719
720 END IF; -- g_applicant_spl_tal_inc
721
722 IF g_applicant_per_stat_inc THEN
723 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'APPLICANT_PERSONAL_STATEMENTS', 8405);
724
725 IF p_enable_log = 'Y' THEN
726 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
727 p_token_name => 'TYPE_NAME',
728 p_token_value => l_meaning);
729 END IF;
730
731 -- Populating the interface table with the interface_run_id value
732 UPDATE igs_ad_perstmt_int a
733 SET interface_run_id = p_interface_run_id,
734 (person_id,admission_appl_number)
735 = (SELECT person_id,NVL(admission_appl_number,update_adm_appl_number)
736 FROM igs_ad_apl_int
737 WHERE interface_appl_id = a.interface_appl_id)
738 WHERE interface_appl_id IN (SELECT interface_appl_id
739 FROM igs_ad_apl_int
740 WHERE interface_run_id = p_interface_run_id
741 AND status IN ('1','4'));
742
743 -- Gather statistics of the table
744 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
745 tabname => 'IGS_AD_PERSTMT_INT',
746 cascade => TRUE);
747
748 -- Call category entity import procedure
749 igs_ad_imp_003.prc_pe_persstat_details (p_interface_run_id => p_interface_run_id,
750 p_enable_log => p_enable_log,
751 p_rule => 'N'); -- Update not yet supported
752
753 END IF; -- g_applicant_per_stat_inc
754
755 IF g_applicant_fee_dtls_inc THEN
756 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'APPLICANT_FEE_DTLS', 8405);
757
758 IF p_enable_log = 'Y' THEN
759 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
760 p_token_name => 'TYPE_NAME',
761 p_token_value => l_meaning);
762 END IF;
763
764 -- Populating the interface table with the interface_run_id value
765 UPDATE igs_ad_fee_int a
766 SET interface_run_id = p_interface_run_id,
767 (person_id,admission_appl_number)
768 = (SELECT person_id,NVL(admission_appl_number,update_adm_appl_number)
769 FROM igs_ad_apl_int
770 WHERE interface_appl_id = a.interface_appl_id)
771 WHERE interface_appl_id IN (SELECT interface_appl_id
772 FROM igs_ad_apl_int
773 WHERE interface_run_id = p_interface_run_id
774 AND status IN ('1','4'));
775
776 -- Gather statistics of the table
777 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
778 tabname => 'IGS_AD_FEE_INT',
779 cascade => TRUE);
780
781 -- Call category entity import procedure
782 igs_ad_imp_003.prc_appl_fees (p_interface_run_id => p_interface_run_id,
783 p_enable_log => p_enable_log,
784 p_rule => igs_ad_gen_016.find_source_cat_rule (p_source_type_id, 'APPLICANT_FEE_DTLS'));
785
786 END IF; -- g_applicant_fee_dtls_inc
787
788 IF g_applicant_notes_inc THEN
789 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'APPLICANT_NOTES', 8405);
790
791 IF p_enable_log = 'Y' THEN
792 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
793 p_token_name => 'TYPE_NAME',
794 p_token_value => l_meaning);
795 END IF;
796
797 -- Populating the interface table with the interface_run_id value
798 UPDATE igs_ad_notes_int a
799 SET interface_run_id = p_interface_run_id,
800 (person_id,admission_appl_number,nominated_course_cd,sequence_number,admission_Application_type)
801 = (SELECT person_id,admission_appl_number,nominated_course_cd,
802 NVL(sequence_number,update_adm_seq_number),admission_Application_type
803 FROM igs_ad_ps_appl_inst_int
804 WHERE interface_ps_appl_inst_id = a.interface_ps_appl_inst_id)
805 WHERE interface_ps_appl_inst_id IN (SELECT interface_ps_appl_inst_id
806 FROM igs_ad_ps_appl_inst_int
807 WHERE interface_run_id = p_interface_run_id
808 AND status IN ('1','4'));
809
810 -- Gather statistics of the table
811 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
812 tabname => 'IGS_AD_NOTES_INT',
813 cascade => TRUE);
814
815 -- Call category entity import procedure
816 igs_ad_imp_010.admp_val_pappl_nots (p_interface_run_id => p_interface_run_id,
817 p_enable_log => p_enable_log,
818 p_category_meaning => l_meaning,
819 p_rule => 'N'); -- Update not yet supported
820
821 END IF; -- g_applicant_notes_inc
822
823 IF g_applicant_des_unit_sets_inc THEN
824 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'APPLICANT_UNITSETS_APPLIED', 8405);
825
826 IF p_enable_log = 'Y' THEN
827 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
828 p_token_name => 'TYPE_NAME',
829 p_token_value => l_meaning);
830 END IF;
831
832 -- Populating the interface table with the interface_run_id value
833 UPDATE igs_ad_unitsets_int a
834 SET interface_run_id = p_interface_run_id,
835 (person_id,admission_appl_number,nominated_course_cd,sequence_number,admission_Application_type )
836 = (SELECT person_id,admission_appl_number,nominated_course_cd,NVL(sequence_number,update_adm_seq_number),admission_Application_type
837 FROM igs_ad_ps_appl_inst_int
838 WHERE interface_ps_appl_inst_id = a.interface_ps_appl_inst_id)
839 WHERE interface_ps_appl_inst_id IN (SELECT interface_ps_appl_inst_id
840 FROM igs_ad_ps_appl_inst_int
841 WHERE interface_run_id = p_interface_run_id
842 AND status IN ('1','4'));
843
844 -- Gather statistics of the table
845 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
846 tabname => 'IGS_AD_UNITSETS_INT',
847 cascade => TRUE);
848
849 -- Call category entity import procedure
850 igs_ad_imp_010.prc_apcnt_uset_apl (p_interface_run_id => p_interface_run_id,
851 p_enable_log => p_enable_log,
852 p_category_meaning => l_meaning,
853 p_rule => igs_ad_gen_016.find_source_cat_rule (p_source_type_id, 'APPLICANT_UNITSETS_APPLIED'));
854
855 END IF; -- g_applicant_des_unit_sets_inc
856
857 IF g_applicant_edu_goal_inc THEN
858 l_meaning := igs_ad_gen_016.get_lkup_meaning ('IMP_CATEGORIES', 'APPLICANT_EDU_GOALS', 8405);
859
860 IF p_enable_log = 'Y' THEN
861 igs_ad_imp_001.set_message (p_name => 'IGS_PE_BEG_IMP',
862 p_token_name => 'TYPE_NAME',
863 p_token_value => l_meaning);
864 END IF;
865
866 -- Populating the interface table with the interface_run_id value
867 UPDATE igs_ad_edugoal_int a
868 SET interface_run_id = p_interface_run_id,
869 (person_id,admission_appl_number,nominated_course_cd,sequence_number,admission_Application_type )
870 = (SELECT person_id,admission_appl_number,nominated_course_cd,NVL(sequence_number,update_adm_seq_number),admission_Application_type
871 FROM igs_ad_ps_appl_inst_int
872 WHERE interface_ps_appl_inst_id = a.interface_ps_appl_inst_id)
873 WHERE interface_ps_appl_inst_id IN (SELECT interface_ps_appl_inst_id
874 FROM igs_ad_ps_appl_inst_int
875 WHERE interface_run_id = p_interface_run_id
876 AND status IN ('1','4'));
877
878 -- Gather statistics of the table
879 FND_STATS.GATHER_TABLE_STATS(ownname => l_schema,
880 tabname => 'IGS_AD_EDUGOAL_INT',
881 cascade => TRUE);
882
883 -- Call category entity import procedure
884 igs_ad_imp_010.prcs_applnt_edu_goal_dtls (p_interface_run_id => p_interface_run_id,
885 p_enable_log => p_enable_log,
886 p_category_meaning => l_meaning,
887 p_rule => 'N'); -- Update not yet supported
888
889 END IF; -- g_applicant_edu_goal_inc
890
891 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
892
893 IF (l_request_id IS NULL) THEN
894 l_request_id := fnd_global.conc_request_id;
895 END IF;
896
897 l_label := 'igs.plsql.igs_ad_imp_015.prc_ad_category.Leaving';
898 l_debug_str := 'Leaving prc_ad_category';
899
900 fnd_log.string_with_context (fnd_log.level_procedure,
901 l_label,
902 l_debug_str,
903 NULL,NULL,NULL,NULL,NULL,
904 TO_CHAR(l_request_id));
905 END IF;
906 END prc_ad_category;
907
908 PROCEDURE store_ad_stats (p_source_type_id IN NUMBER,
909 p_batch_id IN NUMBER,
910 p_interface_run_id IN NUMBER
911 ) AS
912 /*************************************************************
913 Created By : knag
914 Date Created By : 05-NOV-2003
915 Purpose : This procedure will call all the procedures for admission and inquiry related categories
916 Know limitations, enhancements or remarks
917 Change History
918 Who When What
919 rbezawad 27-Feb-05 In store_ad_stats() procedure, changed Recruitment table names to IGR_% naming convention.
920 (reverse chronological order - newest change first)
921 ***************************************************************/
922 l_category_entity_ad_table igs_ad_imp_001.g_category_entity_type_table;
923
924 BEGIN
925
926 -- Define category - entity mapping
927 l_category_entity_ad_table(01).category_name := 'PERSON_QUAL'; l_category_entity_ad_table(01).entity_name := 'IGS_UC_QUAL_INTS';
928 l_category_entity_ad_table(02).category_name := 'PERSON_RECRUITMENT_DETAILS'; l_category_entity_ad_table(02).entity_name := 'IGS_AD_RECRUIT_INT';
929 l_category_entity_ad_table(03).category_name := 'INQUIRY_DETAILS'; l_category_entity_ad_table(03).entity_name := 'IGR_I_APPL_INT';
930 l_category_entity_ad_table(04).category_name := 'INQUIRY_ACADEMIC_INTEREST'; l_category_entity_ad_table(04).entity_name := 'IGR_I_LINES_INT';
931 l_category_entity_ad_table(05).category_name := 'INQUIRY_PACKAGE_ITEMS'; l_category_entity_ad_table(05).entity_name := 'IGR_I_PKG_INT';
932 l_category_entity_ad_table(06).category_name := 'INQUIRY_INFORMATION_TYPES'; l_category_entity_ad_table(06).entity_name := 'IGR_I_INFO_INT';
933 l_category_entity_ad_table(07).category_name := 'INQUIRY_CHARACTERISTICS'; l_category_entity_ad_table(07).entity_name := 'IGR_I_CHAR_INT';
934 l_category_entity_ad_table(08).category_name := 'TEST_RESULTS'; l_category_entity_ad_table(08).entity_name := 'IGS_AD_TEST_INT';
935 l_category_entity_ad_table(09).category_name := 'TEST_RESULTS'; l_category_entity_ad_table(09).entity_name := 'IGS_AD_TEST_SEGS_INT';
936 l_category_entity_ad_table(10).category_name := 'TRANSCRIPT_DETAILS'; l_category_entity_ad_table(10).entity_name := 'IGS_AD_TXCPT_INT';
937 l_category_entity_ad_table(11).category_name := 'TRANSCRIPT_DETAILS'; l_category_entity_ad_table(11).entity_name := 'IGS_AD_TRMDT_INT';
938 l_category_entity_ad_table(12).category_name := 'TRANSCRIPT_DETAILS'; l_category_entity_ad_table(12).entity_name := 'IGS_AD_TUNDT_INT';
939 l_category_entity_ad_table(13).category_name := 'APPLICATION'; l_category_entity_ad_table(13).entity_name := 'IGS_AD_APL_INT';
940 l_category_entity_ad_table(14).category_name := 'APPLICATION'; l_category_entity_ad_table(14).entity_name := 'IGS_AD_PS_APPL_INST_INT';
941 l_category_entity_ad_table(15).category_name := 'APPLICANT_HISTORY'; l_category_entity_ad_table(15).entity_name := 'IGS_AD_APPHIST_INT';
942 l_category_entity_ad_table(16).category_name := 'APPLICANT_HISTORY'; l_category_entity_ad_table(16).entity_name := 'IGS_AD_INSTHIST_INT';
943 l_category_entity_ad_table(17).category_name := 'APPLICANT_OTHERINSTS_APPLIED'; l_category_entity_ad_table(17).entity_name := 'IGS_AD_OTHINST_INT';
944 l_category_entity_ad_table(18).category_name := 'APPLICANT_ACADEMIC_INTERESTS'; l_category_entity_ad_table(18).entity_name := 'IGS_AD_ACADINT_INT';
945 l_category_entity_ad_table(19).category_name := 'APPLICANT_INTENT'; l_category_entity_ad_table(19).entity_name := 'IGS_AD_APPINT_INT';
946 l_category_entity_ad_table(20).category_name := 'APPLICANT_SPECIAL_INTERESTS'; l_category_entity_ad_table(20).entity_name := 'IGS_AD_SPLINT_INT';
947 l_category_entity_ad_table(21).category_name := 'APPLICANT_SPECIAL_TALENTS'; l_category_entity_ad_table(21).entity_name := 'IGS_AD_SPLTAL_INT';
948 l_category_entity_ad_table(22).category_name := 'APPLICANT_PERSONAL_STATEMENTS'; l_category_entity_ad_table(22).entity_name := 'IGS_AD_PERSTMT_INT';
949 l_category_entity_ad_table(23).category_name := 'APPLICANT_FEE_DTLS'; l_category_entity_ad_table(23).entity_name := 'IGS_AD_FEE_INT';
950 l_category_entity_ad_table(24).category_name := 'APPLICANT_NOTES'; l_category_entity_ad_table(24).entity_name := 'IGS_AD_NOTES_INT';
951 l_category_entity_ad_table(25).category_name := 'APPLICANT_UNITSETS_APPLIED'; l_category_entity_ad_table(25).entity_name := 'IGS_AD_UNITSETS_INT';
952 l_category_entity_ad_table(26).category_name := 'APPLICANT_EDU_GOALS'; l_category_entity_ad_table(26).entity_name := 'IGS_AD_EDUGOAL_INT';
953
954 IF fnd_profile.value('IGS_RECRUITING_ENABLED') = 'Y' THEN
955 l_category_entity_ad_table(27).category_name := 'INQUIRY_INSTANCE'; l_category_entity_ad_table(27).entity_name := 'IGR_I_APPL_INT';
956 l_category_entity_ad_table(28).category_name := 'INQUIRY_INSTANCE'; l_category_entity_ad_table(28).entity_name := 'IGR_I_LINES_INT';
957 l_category_entity_ad_table(29).category_name := 'INQUIRY_INSTANCE'; l_category_entity_ad_table(29).entity_name := 'IGR_I_PKG_INT';
958 l_category_entity_ad_table(30).category_name := 'INQUIRY_INSTANCE'; l_category_entity_ad_table(30).entity_name := 'IGR_I_INFO_INT';
959 l_category_entity_ad_table(31).category_name := 'INQUIRY_INSTANCE'; l_category_entity_ad_table(31).entity_name := 'IGR_I_CHAR_INT';
960 END IF;
961
962 igs_ad_imp_001.store_stats (p_source_type_id => p_source_type_id,
963 p_batch_id => p_batch_id,
964 p_interface_run_id => p_interface_run_id,
965 p_category_entity_table => l_category_entity_ad_table);
966
967 END store_ad_stats;
968
969 PROCEDURE del_cmpld_ad_records (p_source_type_id IN NUMBER,
970 p_batch_id IN NUMBER,
971 p_interface_run_id IN NUMBER
972 ) AS
973 /*************************************************************
974 Created By : knag
975 Date Created By : 05-NOV-2003
976 Purpose : This procedure will call all the procedures for admission and inquiry related categories
977 Know limitations, enhancements or remarks
978 Change History
979 Who When What
980 rbezawad 27-Feb-05 Added code to procedure del_cmpld_ad_records() to execute a Dynamic Code block
981 when IGR functionality is enabled
982 (reverse chronological order - newest change first)
983 ***************************************************************/
984 l_prog_label VARCHAR2(4000);
985 l_label VARCHAR2(4000);
986 l_request_id NUMBER;
987 l_debug_str VARCHAR2(4000);
988 l_stmt VARCHAR2(2000);
989
990 BEGIN
991
992 l_prog_label := 'igs.plsql.igs_ad_imp_015.del_cmpld_ad_records';
993 l_label := 'igs.plsql.igs_ad_imp_015.del_cmpld_ad_records.';
994
995 IF fnd_log.test(fnd_log.level_procedure,l_prog_label) THEN
996
997 IF (l_request_id IS NULL) THEN
998 l_request_id := fnd_global.conc_request_id;
999 END IF;
1000
1001 l_label := 'igs.plsql.igs_ad_imp_015.del_cmpld_ad_records.begin';
1002 l_debug_str := 'Source Type Id : ' || p_source_type_id || ' Batch ID : ' || p_batch_id;
1003
1004 fnd_log.string_with_context (fnd_log.level_procedure,
1005 l_label,
1006 l_debug_str,
1007 NULL,NULL,NULL,NULL,NULL,
1008 TO_CHAR(l_request_id));
1009 END IF;
1010
1011 IF g_applicant_hist_inc THEN
1012 DELETE FROM igs_ad_insthist_int
1013 WHERE status = '1'
1014 AND interface_run_id = p_interface_run_id;
1015 COMMIT;
1016 END IF; -- g_applicant_hist_inc
1017
1018 IF g_applicant_notes_inc THEN
1019 DELETE FROM igs_ad_notes_int
1020 WHERE status = '1'
1021 AND interface_run_id = p_interface_run_id;
1022 COMMIT;
1023 END IF; -- g_applicant_notes_inc
1024
1025 IF g_applicant_des_unit_sets_inc THEN
1026 DELETE FROM igs_ad_unitsets_int
1027 WHERE status = '1'
1028 AND interface_run_id = p_interface_run_id;
1029 COMMIT;
1030 END IF; -- g_applicant_des_unit_sets_inc
1031
1032 IF g_applicant_edu_goal_inc THEN
1033 DELETE FROM igs_ad_edugoal_int
1034 WHERE status = '1'
1035 AND interface_run_id = p_interface_run_id;
1036 COMMIT;
1037 END IF; -- g_applicant_edu_goal_inc
1038
1039 IF g_application_inc THEN
1040 DELETE FROM igs_ad_ps_appl_inst_int
1041 WHERE status = '1'
1042 AND interface_run_id = p_interface_run_id;
1043 COMMIT;
1044 END IF; -- g_application_inc
1045
1046 IF g_applicant_hist_inc THEN
1047 DELETE FROM igs_ad_apphist_int
1048 WHERE status = '1'
1049 AND interface_run_id = p_interface_run_id;
1050 COMMIT;
1051 END IF; -- g_applicant_hist_inc
1052
1053 IF g_applicant_oth_inst_appl_inc THEN
1054 DELETE FROM igs_ad_othinst_int
1055 WHERE status = '1'
1056 AND interface_run_id = p_interface_run_id;
1057 COMMIT;
1058 END IF; -- g_applicant_oth_inst_appl_inc
1059
1060 IF g_applicant_acad_int_inc THEN
1061 DELETE FROM igs_ad_acadint_int
1062 WHERE status = '1'
1063 AND interface_run_id = p_interface_run_id;
1064 COMMIT;
1065 END IF; -- g_applicant_acad_int_inc
1066
1067 IF g_applicant_appl_intent_inc THEN
1068 DELETE FROM igs_ad_appint_int
1069 WHERE status = '1'
1070 AND interface_run_id = p_interface_run_id;
1071 COMMIT;
1072 END IF; -- g_applicant_appl_intent_inc
1073
1074 IF g_applicant_spl_int_inc THEN
1075 DELETE FROM igs_ad_splint_int
1076 WHERE status = '1'
1077 AND interface_run_id = p_interface_run_id;
1078 COMMIT;
1079 END IF; -- g_applicant_spl_int_inc
1080
1081 IF g_applicant_spl_tal_inc THEN
1082 DELETE FROM igs_ad_spltal_int
1083 WHERE status = '1'
1084 AND interface_run_id = p_interface_run_id;
1085 COMMIT;
1086 END IF; -- g_applicant_spl_tal_inc
1087
1088 IF g_applicant_per_stat_inc THEN
1089 DELETE FROM igs_ad_perstmt_int
1090 WHERE status = '1'
1091 AND interface_run_id = p_interface_run_id;
1092 COMMIT;
1093 END IF; -- g_applicant_per_stat_inc
1094
1095 IF g_applicant_fee_dtls_inc THEN
1096 DELETE FROM igs_ad_fee_int
1097 WHERE status = '1'
1098 AND interface_run_id = p_interface_run_id;
1099 COMMIT;
1100 END IF; -- g_applicant_fee_dtls_inc
1101
1102 IF g_application_inc THEN
1103 DELETE FROM igs_ad_apl_int
1104 WHERE status = '1'
1105 AND interface_run_id = p_interface_run_id;
1106 COMMIT;
1107 END IF; -- g_application_inc
1108
1109 IF g_transcript_dtls_inc THEN
1110 DELETE FROM igs_ad_tundt_int
1111 WHERE status = '1'
1112 AND interface_run_id = p_interface_run_id;
1113 COMMIT;
1114
1115 DELETE FROM igs_ad_trmdt_int
1116 WHERE status = '1'
1117 AND interface_run_id = p_interface_run_id;
1118 COMMIT;
1119
1120 DELETE FROM igs_ad_txcpt_int
1121 WHERE status = '1'
1122 AND interface_run_id = p_interface_run_id;
1123 COMMIT;
1124 END IF; -- g_transcript_dtls_inc
1125
1126 IF g_test_result_inc THEN
1127 DELETE FROM igs_ad_test_segs_int
1128 WHERE status = '1'
1129 AND interface_run_id = p_interface_run_id;
1130 COMMIT;
1131
1132 DELETE FROM igs_ad_test_int
1133 WHERE status = '1'
1134 AND interface_run_id = p_interface_run_id;
1135 COMMIT;
1136 END IF; -- g_test_result_inc
1137
1138 --Dynamic Code block to be executed when IGR functionality is enabled.
1139 IF fnd_profile.value('IGS_RECRUITING_ENABLED') = 'Y' THEN
1140 BEGIN
1141 l_stmt := ' BEGIN
1142 igr_imp_002.del_cmpld_rct_records(:1,:2);
1143 END; ';
1144 EXECUTE IMMEDIATE l_stmt USING p_source_type_id,p_interface_run_id;
1145 EXCEPTION
1146 WHEN OTHERS THEN
1147 fnd_file.put_line(fnd_file.log,'Error occurred while calling IGR_IMP_002.DEL_CMPLD_RCT_RECORDS() : '||SQLERRM);
1148 END;
1149 END IF;
1150
1151 IF g_person_recruit_dtls_inc THEN
1152 DELETE FROM igs_ad_recruit_int
1153 WHERE status = '1'
1154 AND interface_run_id = p_interface_run_id;
1155 COMMIT;
1156 END IF; -- g_person_recruit_dtls_inc
1157
1158 IF g_person_qual_inc THEN
1159 DELETE FROM igs_uc_qual_ints
1160 WHERE status = '1'
1161 AND interface_run_id = p_interface_run_id;
1162 COMMIT;
1163 END IF; -- g_person_qual_inc
1164
1165 END del_cmpld_ad_records;
1166
1167 END igs_ad_imp_015;