1 PACKAGE BODY IGR_IMP_003 AS
2 /* $Header: IGSRT14B.pls 120.0 2005/06/01 16:09:32 appldev noship $ */
3 /* ------------------------------------------------------------------------------------------------------------------------
4 || Created By : rbezawad
5 || Created On : 28-Feb-05
6 || Purpose : Extract of IGR related references from Admissions Import process packages (IGSAD97B.pls)
7 || to get rid of probable compilation errors for non-IGR customers.
8 || Known limitations, enhancements or remarks :
9 || Change History :
10 || WHO WHEN WHAT
11 9-Mar-05 rbezawad Modified logic to Validated Inquiry Lines for APC Integration Build. Bug: 3973942.
12 Also obsoelted usage of Entry Status/Program/Unit Set code columns.
13 ---------------------------------------------------------------------------------------------------------------------------*/
14
15 /** Main Cursor to iterate in Inquiry details records **/
16
17 CURSOR c_inquiry_dtls (cp_interface_run_id igr_i_appl_int.interface_run_id%TYPE)
18 IS
19 SELECT a.rowid,a.*
20 FROM igr_i_appl_int a
21 WHERE a.interface_run_id = cp_interface_run_id
22 AND a.status = '2';
23
24 CURSOR c_inquiry_lines (cp_interface_run_id igr_i_lines_int.interface_run_id%TYPE)
25 IS
26 SELECT a.rowid,a.*
27 FROM igr_i_lines_int a
28 WHERE a.interface_run_id = cp_interface_run_id
29 AND a.status = '2';
30
31 /***************************Get Meaning for error code******************************/
32
33 FUNCTION Get_Meaning(
34 p_lookup_code igs_lookups_view.lookup_code%TYPE,
35 p_lookup_type igs_lookups_view.lookup_type%TYPE
36 )
37 RETURN VARCHAR2 AS
38 /*******************************************************************************
39 Created By: Syam Krishnan
40 Date Created By: 06-12-2001 (MM-DD-YYYY)
41 Purpose: This fucntion is used to return the meaning for
42 a particular lookup_type and lookup_code combination.
43 Known limitations,enhancements,remarks:
44 Change History
45 Who When What
46
47 *******************************************************************************/
48 lv_meaning igs_lookups_view.meaning%TYPE;
49
50 -- Cursor c_lkup is used to select the record (if any) that matches the
51 -- criteria passed via the parameters to the fucntion
52 CURSOR c_lkup (cp_lookup_type igs_lookups_view.lookup_type%TYPE,
53 cp_lookup_code igs_lookups_view.lookup_code%TYPE) IS
54 SELECT
55 meaning
56 FROM
57 igs_lookups_view
58 WHERE
59 lookup_type = cp_lookup_type
60 AND lookup_code = cp_lookup_code;
61
62 BEGIN
63 OPEN c_lkup (p_lookup_type ,p_lookup_code);
64 FETCH c_lkup INTO lv_meaning;
65
66 IF c_lkup%NOTFOUND THEN
67 -- No records are found that match the criteria passed via the parameters
68 -- hence closing the cursor and returning null value.
69 CLOSE c_lkup;
70 RETURN NULL;
71 END IF;
72
73 -- The control will come to this point only if a matching record is found
74 -- Hence closing the cursor and returning the value.
75 CLOSE c_lkup;
76 RETURN lv_meaning;
77 END get_meaning;
78 /***************************Get Meaning for error code******************************/
79
80
81 /***********************Start Create person inquiry *********************************/
82 PROCEDURE create_person_inquiry ( p_inquiry_dtls_rec IN c_inquiry_dtls%ROWTYPE,
83 p_source_type_id igs_ad_interface.source_type_id%TYPE,
84 p_status OUT NOCOPY VARCHAR2
85 ) AS
86 /*************************************************************
87 Created By :Syam.Krishnan
88 Date Created By :12-JUN-2001
89 Purpose : To create Person Inquiries
90 Know limitations, enhancements or remarks
91 Change History
92 Who When What
93 rrengara 14-Feb-2003 Changes for RCT Build
94 (reverse chronological order - newest change first)
95 ***************************************************************/
96
97 ----------------Variable Declarations-------------------------------------
98 lv_enq_appl_rowid ROWID;
99 lv_enquiry_appl_number igr_i_appl.enquiry_appl_number%TYPE;
100 ln_org_id igr_i_appl.org_id%TYPE ;
101 l_sales_lead_id igr_i_appl_v.sales_lead_id%TYPE;
102
103 lv_msg_data VARCHAR2(4000);
104 lv_return_status VARCHAR2(1);
105 lv_msg_count NUMBER;
106
107 lv_new_person_type_code igs_pe_typ_instances.person_type_code%TYPE;
108 lv_new_funnel_status igs_pe_typ_instances.funnel_status%TYPE;
109
110 cst_PROSPECT CONSTANT varchar2(50) DEFAULT 'PROSPECT';
111 cst_IDENTIFIED CONSTANT varchar2(50) DEFAULT '100-IDENTIFIED';
112 cst_INQUIRED CONSTANT varchar2(50) DEFAULT '300-INQUIRED';
113 cst_CONTACTED CONSTANT varchar2(50) DEFAULT '200-CONTACTED';
114
115 l_prog_label VARCHAR2(100);
116 l_error_code VARCHAR2(30);
117 l_request_id NUMBER;
118 l_label VARCHAR2(100);
119 l_debug_str VARCHAR2(2000);
120 l_enable_log VARCHAR2(1);
121 l_rowid VARCHAR2(25);
122 l_error_text VARCHAR2(2000);
123 l_type VARCHAR2(1);
124 l_status VARCHAR2(1);
125 l_sqlerrm VARCHAR2(2000);
126
127 --------------------End variable Declarations-------------------------------
128
129 ----------------Cursor Declarations-----------------------------------------
130 CURSOR
131 c_prospect_exist (cp_person_id igs_pe_typ_instances.person_id%TYPE) IS
132 SELECT
133 pti.rowid,pti.*
134 FROM
135 igs_pe_typ_instances_all pti,
136 igs_pe_person_types pt
137 WHERE
138 pti.person_id = cp_person_id
139 AND pti.person_type_code = pt.person_type_code
140 AND pt.system_type = cst_PROSPECT
141 AND funnel_status IN (cst_IDENTIFIED,cst_INQUIRED,cst_CONTACTED);
142 ----------------End Cursor Declarations-----------------------------------------
143
144
145 --------------------Local Procedure for Finding Funnel Status and Person Type------------------
146 PROCEDURE find_ptype_funnel_status (p_inquiry_dtls_rec c_inquiry_dtls%ROWTYPE ,
147 p_source_type_id igs_ad_interface.source_type_id%TYPE,
148 p_old_person_type_code igs_pe_typ_instances.person_type_code%TYPE,
149 p_new_person_type_code OUT NOCOPY igs_pe_typ_instances.person_type_code%TYPE,
150 p_new_funnel_status OUT NOCOPY igs_pe_typ_instances.funnel_status%TYPE) AS
151 /*************************************************************
152 Created By :Sykrishn
153 Date Created By :06-SEP-2001
154 Purpose : To find new person type and the new funnel status
155 Know limitations, enhancements or remarks
156 Change History
157 Who When What
158
159 (reverse chronological order - newest change first)
160 Change History
161 Who When What
162 rboddu 09-OCT-2002 Removed the logic of fetching person_type_code and funnel_status
163 from IGS_AD_INTERFACE_CTL as part of Enh Bug: 2604395
164 ***************************************************************/
165
166 ----------------------Variable Declarations-----------------------------------------
167 lv_person_type_code igs_pe_typ_instances.person_type_code%TYPE ;
168 lv_funnel_status igs_pe_typ_instances.funnel_status%TYPE ;
169 lb_int_found BOOLEAN ;
170 lb_int_ctl_found BOOLEAN ;
171
172 cst_PROSPECT CONSTANT varchar2(50) DEFAULT 'PROSPECT';
173 cst_other CONSTANT varchar2(50) DEFAULT 'OTHER';
174 cst_applicant CONSTANT VARCHAR2(50) DEFAULT 'APPLICANT';
175 ----------------------End Variable Declarations-----------------------------------------
176
177 -----------------Cursor Declarations----------------------------------------------------
178 /* Cursor to get the person_type and funnel_status from igs_ad_interface */
179 CURSOR c_interface (cp_interface_id igs_ad_interface.interface_id%TYPE) IS
180 SELECT
181 person_type_code ,
182 funnel_status
183 FROM
184 igs_ad_interface
185 WHERE
186 interface_id = cp_interface_id;
187
188 CURSOR c_pe_src_types (cp_source_type_id igs_ad_interface.source_type_id%TYPE) IS
189 SELECT
190 person_type_code ,
191 funnel_status
192 FROM
193 igs_pe_src_types
194 WHERE
195 source_type_id = cp_source_type_id;
196
197 CURSOR c_sys_person_type (p_person_type_code igs_pe_person_types.person_type_code%TYPE) IS
198 SELECT
199 system_type
200 FROM
201 igs_pe_person_types
202 WHERE
203 person_type_code = p_person_type_code ;
204
205 l_sys_person_type igs_pe_person_types.system_type%TYPE;
206
207 -----------------End Cursor Declarations----------------------------------------------------
208 BEGIN
209
210 lv_person_type_code := NULL;
211 lv_funnel_status := NULL;
212 lb_int_found := FALSE;
213 lb_int_ctl_found := FALSE;
214
215
216 OPEN c_interface(p_inquiry_dtls_rec.interface_id);
217 FETCH c_interface INTO lv_person_type_code,lv_funnel_status;
218 IF c_interface%FOUND THEN
219 CLOSE c_interface;
220 IF (lv_person_type_code IS NOT NULL ) AND (lv_funnel_status IS NOT NULL) THEN
221 lb_int_found := TRUE;
222 END IF;
223 ELSE
224 CLOSE c_interface;
225 END IF;
226
227 IF NOT lb_int_found THEN -- if not in ad_interface >> next level interface_ctl
228 lv_person_type_code := NULL;
229 lv_funnel_status := NULL;
230 OPEN c_pe_src_types(p_source_type_id);
231 FETCH c_pe_src_types INTO lv_person_type_code,lv_funnel_status;
232 CLOSE c_pe_src_types;
233 END IF;
234
235 IF (lv_person_type_code IS NULL AND p_old_person_type_code = cst_OTHER) THEN
236 BEGIN
237 UPDATE igr_i_appl_int
238 SET status = '3',
239 error_code = 'E731'
240 WHERE interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
241 END;
242 END IF;
243
244 OPEN c_sys_person_type(lv_person_type_code);
245 FETCH c_sys_person_type INTO l_sys_person_type;
246 CLOSE c_sys_person_type;
247
248 IF (lv_funnel_status IS NOT NULL AND l_sys_person_type <> cst_PROSPECT) THEN
249 BEGIN
250 UPDATE igr_i_appl_int
251 SET status = '3',
252 error_code = 'E732' /* Invalid Person Type */
253 WHERE interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
254 END;
255 END IF;
256
257 /* Anyway return the out parameters */
258 p_new_person_type_code := lv_person_type_code;
259 p_new_funnel_status := lv_funnel_status;
260 END find_ptype_funnel_status;
261 /*************************************Local Procedure for Finding Funnel Status and Person Type****************************/
262
263 BEGIN
264 ln_org_id := igs_ge_gen_003.get_org_id;
265 FOR rec_prospect_exist IN c_prospect_exist (p_inquiry_dtls_rec.person_id) LOOP
266 find_ptype_funnel_status (p_inquiry_dtls_rec => p_inquiry_dtls_rec ,
267 p_source_type_id => p_source_type_id,
268 p_old_person_type_code => rec_prospect_exist.person_type_code,
269 p_new_person_type_code => lv_new_person_type_code,
270 p_new_funnel_status => lv_new_funnel_status );
271 END LOOP;
272
273 igr_inquiry_pkg.insert_row (
274 x_mode => 'R',
275 x_rowid => lv_enq_appl_rowid,
276 x_person_id => p_inquiry_dtls_rec.person_id,
277 x_enquiry_appl_number => lv_enquiry_appl_number,
278 x_sales_lead_id => l_sales_lead_id,
279 x_acad_cal_type => p_inquiry_dtls_rec.acad_cal_type,
280 x_acad_ci_sequence_number => p_inquiry_dtls_rec.acad_ci_sequence_number,
281 x_adm_cal_type => p_inquiry_dtls_rec.adm_cal_type,
282 x_adm_ci_sequence_number => p_inquiry_dtls_rec.adm_ci_sequence_number,
283 x_enquiry_dt => p_inquiry_dtls_rec.inquiry_dt,
284 x_registering_person_id => p_inquiry_dtls_rec.registering_person_id,
285 x_override_process_ind => p_inquiry_dtls_rec.override_process_ind,
286 x_indicated_mailing_dt => p_inquiry_dtls_rec.indicated_mailing_dt,
287 x_last_process_dt => p_inquiry_dtls_rec.last_process_dt,
288 x_comments => p_inquiry_dtls_rec.comments,
289 x_org_id => igs_ge_gen_003.get_org_id,
293 x_how_knowus_id => p_inquiry_dtls_rec.learn_source_id,
290 x_inq_entry_level_id => p_inquiry_dtls_rec.inquiry_entry_level_id,
291 x_edu_goal_id => p_inquiry_dtls_rec.edu_goal_id,
292 x_party_id => p_inquiry_dtls_rec.inquiry_school_of_interest_id,
294 x_who_influenced_id => p_inquiry_dtls_rec.influence_source_id,
295 x_attribute_category => p_inquiry_dtls_rec.attribute_category,
296 x_attribute1 => p_inquiry_dtls_rec.attribute1,
297 x_attribute2 => p_inquiry_dtls_rec.attribute2,
298 x_attribute3 => p_inquiry_dtls_rec.attribute3,
299 x_attribute4 => p_inquiry_dtls_rec.attribute4,
300 x_attribute5 => p_inquiry_dtls_rec.attribute5,
301 x_attribute6 => p_inquiry_dtls_rec.attribute6,
302 x_attribute7 => p_inquiry_dtls_rec.attribute7,
303 x_attribute8 => p_inquiry_dtls_rec.attribute8,
304 x_attribute9 => p_inquiry_dtls_rec.attribute9,
305 x_attribute10 => p_inquiry_dtls_rec.attribute10,
306 x_attribute11 => p_inquiry_dtls_rec.attribute11,
307 x_attribute12 => p_inquiry_dtls_rec.attribute12,
308 x_attribute13 => p_inquiry_dtls_rec.attribute13,
309 x_attribute14 => p_inquiry_dtls_rec.attribute14,
310 x_attribute15 => p_inquiry_dtls_rec.attribute15,
311 x_attribute16 => p_inquiry_dtls_rec.attribute16,
312 x_attribute17 => p_inquiry_dtls_rec.attribute17,
313 x_attribute18 => p_inquiry_dtls_rec.attribute18,
314 x_attribute19 => p_inquiry_dtls_rec.attribute19,
315 x_attribute20 => p_inquiry_dtls_rec.attribute20,
316 x_s_enquiry_status => p_inquiry_dtls_rec.inquiry_status,
317 x_source_promotion_id => p_inquiry_dtls_rec.source_promotion_id,
318 x_person_type_code => lv_new_person_type_code,
319 x_funnel_status => lv_new_funnel_status,
320 x_ret_status => lv_return_status,
321 x_msg_data => lv_msg_data,
322 x_msg_count => lv_msg_count,
323 x_inquiry_method_code => p_inquiry_dtls_rec.inquiry_source_type,
324 x_action => 'Import',
325 x_pkg_reduct_ind => NVL(p_inquiry_dtls_rec.pkg_reduct_ind,'N')
326 );
327
328 IF lv_return_status <>'U' AND lv_msg_data IS NOT NULL THEN
329 IF l_enable_log = 'Y' THEN
330 igs_ad_imp_001.logerrormessage(p_inquiry_dtls_rec.interface_inq_appl_id,lv_msg_data);
331 END IF;
332 ELSIF lv_return_status = 'U' THEN
333 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
334 l_label := 'igs.plsql.igr_imp_003.process_person_inquiry.exception '||'E322';
335
336 fnd_message.set_name('IGS','IGS_PE_IMP_DET_ERROR');
337 fnd_message.set_token('CONTEXT',p_inquiry_dtls_rec.interface_inq_appl_id);
338 fnd_message.set_token('ERROR', l_error_text);
339
340 l_debug_str := fnd_message.get;
341
342 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
343 END IF;
344 END IF;
345
346 IF lv_msg_data IS NOT NULL THEN
347 /* Return status of Null should be treated as Success */
348 IF NVL(lv_return_status,'S') = 'S' THEN
349 UPDATE igr_i_appl_int
350 SET status = '4',
351 error_code = 'E702',
352 error_text = lv_msg_data
353 WHERE rowid = p_inquiry_dtls_rec.rowid;
354 ELSE
355 ROLLBACK TO perinq_save;
356 UPDATE igr_i_appl_int
357 SET status = '3',
358 error_code = 'E322',
359 error_text = lv_msg_data
360 WHERE rowid = p_inquiry_dtls_rec.rowid;
361 END IF;
362 ELSE
363 /* Return status of Null should be treated as Success */
364 IF NVL(lv_return_status,'S') = 'S' THEN
365 UPDATE igr_i_appl_int
366 SET status = '1',
367 error_code = NULL,
368 error_text = NULL,
369 enquiry_appl_number= lv_enquiry_appl_number,
370 sales_lead_id = l_sales_lead_id
371 WHERE rowid = p_inquiry_dtls_rec.rowid;
372 ELSE
373 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E322', 8405);
374 ROLLBACK TO perinq_save;
375 UPDATE igr_i_appl_int
376 SET status = '3',
377 error_code = 'E322',
378 error_text = l_error_text
379 WHERE rowid = p_inquiry_dtls_rec.rowid;
380 END IF;
381 END IF;
382
383
384 EXCEPTION
385 WHEN OTHERS THEN
386 l_sqlerrm := SQLERRM;
387 /* If insert is NOT successful update status to 3 in table igr_i_appl_int */
388 p_status := '3';
389
390 ROLLBACK TO perinq_save;
391
392 UPDATE
393 igr_i_appl_int
394 SET
395 status = '3',
396 error_code = 'E322' ,
397 error_text = NVL(lv_msg_data,l_sqlerrm)
398 WHERE
399 interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
400 END create_person_inquiry ;
401 /***********************End Create person inquiry *********************************/
402
403 PROCEDURE validate_person_inquiry ( p_inquiry_dtls_rec IN c_inquiry_dtls%ROWTYPE,
404 p_validation OUT NOCOPY BOOLEAN )
405 AS
406 /*************************************************************
407 Created By :Syam.Krishnan
411 Change History
408 Date Created By :12-JUN-2001
409 Purpose : Validate data elements for person inquiry
410 Know limitations, enhancements or remarks
412 Who When What
413
414 (reverse chronological order - newest change first)
415 -- kamohan 5/23/02 Changed the condition to check
416 -- the user defined status and not the system defined
417 --pbondugu 19-Mar-2003 Added the validation for inquiry date. New error code is added
418 for the same.
419 ***************************************************************/
420
421 -------------------------Variable Declarations-------------------------------------------------------
422 lv_deceased_ind igs_pe_hz_parties.deceased_ind%TYPE;
423
424 lv_exist varchar2(2000);
425 l_birth_date igs_pe_person_base_v.birth_date%TYPE;
426
427 l_error_text VARCHAR2(2000);
428 -------------------------End Variable Declarations-------------------------------------------------------
429
430 ------------------------------------Cursor Declarations----------------------------------------------
431 /*cursor to select Birth Date*/
432 CURSOR c_birth_date(p_person_id igs_pe_person_base_v.person_id%TYPE) IS
433 SELECT birth_date
434 FROM igs_pe_person_base_v
435 WHERE person_id =p_person_id ;
436
437 /*cursor to select inquiry status*/
438 CURSOR c_inquiry_status (cp_inquiry_status igr_i_appl_int.inquiry_status%TYPE) IS
439 SELECT
440 'X'
441 FROM
442 igr_i_status_v
443 WHERE
444 s_enquiry_status = cp_inquiry_status
445 AND dsp_closed_ind = 'N';
446
447 /*cursor to select inquiry source type */
448 CURSOR c_inquiry_source_type (cp_inquiry_source_type igr_i_appl_int.inquiry_source_type%TYPE) IS
449 SELECT
450 'X'
451 FROM
452 fnd_lookup_values
453 WHERE
454 LOOKUP_TYPE ='VEHICLE_RESPONSE_CODE'
455 AND lookup_code = cp_inquiry_source_type
456 AND enabled_flag ='Y'
457 AND LANGUAGE = USERENV('LANG')
458 AND VIEW_APPLICATION_ID = 279
459 AND SECURITY_GROUP_ID = 0;
460
461 /*cursor to select inquiry entry status id */
462 CURSOR c_inquiry_type_id (cp_inquiry_type_id igr_i_appl_int.inquiry_type_id%TYPE) IS
463 SELECT
464 'X'
465 FROM
466 igr_i_inquiry_types
467 WHERE
468 enabled_flag ='Y' AND
469 inquiry_type_id = cp_inquiry_type_id;
470
471 /*cursor to select inquiry entry level id */
472 CURSOR c_inquiry_entry_level_id (cp_inquiry_entry_level_id igr_i_appl_int.inquiry_entry_level_id%TYPE) IS
473 SELECT
474 inq_entry_level_id
475 FROM
476 igr_i_entry_lvls_v
477 WHERE
478 closed_ind = 'N'
479 AND inq_entry_level_id = cp_inquiry_entry_level_id;
480
481
482 /*cursor to select registering person id */
483 CURSOR c_registering_person_id (cp_registering_person_id igr_i_appl_int.registering_person_id%TYPE) IS
484 SELECT
485 'X'
486 FROM
487 igs_pe_person_base_v
488 WHERE
489 person_id = cp_registering_person_id;
490
491 /*cursor to select education goal */
492 CURSOR c_edu_goal_id (cp_edu_goal_id igr_i_appl_int.edu_goal_id%TYPE) IS
493 SELECT
494 'X'
495 FROM
496 igs_ad_code_classes cc
497 WHERE
498 cc.class = 'EDU_GOALS'
499 AND NVL(cc.closed_ind,'N') = 'N'
500 AND cc.code_id = cp_edu_goal_id;
501
502 /*cursor to select inquiry school of interest id */
503 CURSOR c_inq_school_of_interest_id (cp_inq_school_of_interest_id igr_i_appl_int.inquiry_school_of_interest_id%TYPE) IS
504 SELECT
505 'X'
506 FROM
507 igs_ad_schl_aply_to
508 WHERE
509 closed_ind = 'N'
510 AND sch_apl_to_id = cp_inq_school_of_interest_id;
511
512 /*cursor to select learn source id */
513 CURSOR c_learn_source_id (cp_learn_source_id igr_i_appl_int.learn_source_id%TYPE) IS
514 SELECT
515 'X'
516 FROM
517 igs_ad_code_classes cc
518 WHERE
519 cc.class = 'INQ_HOW_KNOWUS'
520 AND NVL(cc.closed_ind,'N') = 'N'
521 AND cc.code_id = cp_learn_source_id;
522
523
524 /*cursor to select influence source id */
525 CURSOR c_influence_source_id (cp_influence_source_id igr_i_appl_int.influence_source_id%TYPE) IS
526 SELECT
527 'X'
528 FROM
529 igs_ad_code_classes cc
530 WHERE
531 cc.class = 'INQ_WHO_INFLUENCED'
532 AND NVL(cc.closed_ind,'N') = 'N'
533 AND cc.code_id = cp_influence_source_id;
534
535
536 /*cursor to select academic cal type and ci sequence number */
537 CURSOR c_acad_cal_type_ci (p_acad_cal_type igr_i_appl_int.acad_cal_type%TYPE,
538 p_acad_ci_sequence_number igr_i_appl_int.acad_ci_sequence_number%TYPE) IS
539
540 SELECT
541 'X'
542 FROM
543 igs_ca_inst_alt_v ciav,
544 igs_lookups_view lkupv,
545 igs_lookups_view lkupv1
546 WHERE
547 (ciav.s_cal_cat = 'ACADEMIC' AND ciav.s_cal_status IN ('ACTIVE')
548 AND (p_acad_cal_type, p_acad_ci_sequence_number) IN
549 (SELECT
550 cir.sup_cal_type,
551 cir.sup_ci_sequence_number
552 FROM
553 igs_ca_inst_rel cir
554 WHERE
555 cir.sub_cal_type IN
556 (SELECT
557 ct.cal_type
558 FROM
559 igs_ca_type ct
560 WHERE
564 AND lkupv1.lookup_type = 'CALENDAR_STATUS';
561 ct.s_cal_cat = 'ADMISSION')))
562 AND lkupv.lookup_type='CAL_CAT' and lkupv.lookup_code='ACADEMIC'
563 AND lkupv1.lookup_code = ciav.s_cal_status
565
566
567 /*cursor to select admission cal type and ci sequence number */
568 CURSOR c_adm_cal_type_ci (p_adm_cal_type igr_i_appl_int.adm_cal_type%TYPE,
569 p_adm_ci_sequence_number igr_i_appl_int.adm_ci_sequence_number%TYPE) IS
570 SELECT
571 'X'
572 FROM
573 igs_ca_inst_alt_v ciav1,
574 igs_lookups_view lkupv,
575 igs_lookups_view lkupv1
576 WHERE
577 (ciav1.s_cal_cat = 'ADMISSION' and ciav1.s_cal_status in ('ACTIVE')
578 AND (p_adm_cal_type, p_adm_ci_sequence_number)
579 IN
580 (SELECT
581 cir.sub_cal_type,
582 cir.sub_ci_sequence_number
583 FROM
584 igs_ca_inst_rel cir
585 WHERE
586 cir.sup_cal_type = P_INQUIRY_DTLS_REC.ACAD_CAL_TYPE
587 AND cir.sup_ci_sequence_number = P_INQUIRY_DTLS_REC.ACAD_CI_SEQUENCE_NUMBER ))
588 AND lkupv.lookup_type='CAL_CAT' and lkupv.lookup_code = 'ADMISSION'
589 AND lkupv1.lookup_type = 'CALENDAR_STATUS' and lkupv1.lookup_code = ciav1.s_cal_status;
590
591 -- Validation for the deceased person is added as a part of the bug #2028066
592 CURSOR c_deceased(cp_party_id igs_pe_hz_parties.party_id%TYPE) IS
593 SELECT
594 deceased_ind
595 FROM
596 igs_pe_hz_parties
597 WHERE
598 party_id = cp_party_id;
599
600 -- Validation for the source_promotion_id added as a part of the Capture Campaign event
601
602 CURSOR c_source_promotion (cp_source_promotion_id igr_i_appl_int.source_promotion_id%TYPE) IS
603 SELECT
604 'X'
605 FROM
606 ams_p_source_codes_v sc,
607 ams_lookups lkup
608 WHERE sc.source_code_id = cp_source_promotion_id
609 AND sc.status IN ('ACTIVE','COMPLETED')
610 AND sc.source_type IN ('EVEH','CAMP')
611 AND sc.source_type = lkup.lookup_code(+)
612 AND lkup.lookup_type = 'AMS_SYS_ARC_QUALIFIER'
613 AND start_date < TRUNC (SYSDATE) ;
614
615
616 ------------------------------------End Cursor Declarations----------------------------------------------
617 BEGIN
618 /* setting the validation flag to true by default */
619 p_validation := TRUE;
620 /* Validation for inquiry date */
621 IF( p_inquiry_dtls_rec.inquiry_dt> SYSDATE) THEN
622 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E349', 8405);
623 UPDATE
624 igr_i_appl_int
625 SET
626 status = '3',
627 error_code = 'E349' , /* Error code for validation to check if system date is greater than system date */
628 error_text = l_error_text
629 WHERE
630 interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
631
632 p_validation := FALSE;
633 RETURN;
634 END IF;
635
636 /* Validation to check for Birth Date */
637 OPEN c_birth_date(p_inquiry_dtls_rec.person_id);
638 FETCH c_birth_date INTO l_birth_date;
639 CLOSE c_birth_date;
640
641 IF ((l_birth_date IS NOT NULL) AND (l_birth_date > p_inquiry_dtls_rec.inquiry_dt)) THEN
642
643 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E583', 8405);
644 UPDATE
645 igr_i_appl_int
646 SET
647 status = '3',
648 error_code = 'E583' , /* Error code for validation to check if the person is deceased */
649 error_text = l_error_text
650 WHERE
651 interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
652 p_validation := FALSE;
653 RETURN;
654 END IF;
655
656 /* Validation to check for deceased person */
657 OPEN c_deceased (p_inquiry_dtls_rec.person_id);
658 FETCH c_deceased INTO lv_deceased_ind;
659 CLOSE c_deceased;
660 IF lv_deceased_ind = 'Y' THEN
661 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E528', 8405);
662 UPDATE
663 igr_i_appl_int
664 SET
665 status = '3',
666 error_code = 'E528' , /* Error code for validation to check if the person is deceased */
667 error_text = l_error_text
668 WHERE
669 interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
670
671 p_validation := FALSE;
672 RETURN;
673 END IF;
674
675 /* validation for inquiry status */
676 OPEN c_inquiry_status (p_inquiry_dtls_rec.inquiry_status);
677 FETCH c_inquiry_status INTO lv_exist;
678 IF c_inquiry_status%NOTFOUND THEN
679 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E302', 8405);
680 UPDATE
681 igr_i_appl_int
682 SET
683 status = '3',
684 error_code = 'E302' , /* Error code for validation for field Inquiry Status */
685 error_text = l_error_text
686 WHERE
687 interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
688
689 p_validation := FALSE;
690 CLOSE c_inquiry_status;
691 RETURN;
692 END IF;
693
694 IF c_inquiry_status%ISOPEN THEN
695 CLOSE c_inquiry_status;
696 END IF;
697
698
699 /* validation for inquiry source type */
700 OPEN c_inquiry_source_type (p_inquiry_dtls_rec.inquiry_source_type);
701 FETCH c_inquiry_source_type INTO lv_exist;
702 IF c_inquiry_source_type%NOTFOUND THEN
703 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E303', 8405);
707 status = '3',
704 UPDATE
705 igr_i_appl_int
706 SET
708 error_code = 'E303', /*Error code for validation for field Inquiry Source Type*/
709 error_text = l_error_text
710 WHERE
711 interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
712
713 p_validation := FALSE;
714 CLOSE c_inquiry_source_type;
715 RETURN;
716 END IF;
717
718 IF c_inquiry_source_type%ISOPEN THEN
719 CLOSE c_inquiry_source_type;
720 END IF;
721
722 /*validation for inquiry_type_id */
723 OPEN c_inquiry_type_id (p_inquiry_dtls_rec.inquiry_type_id);
724 FETCH c_inquiry_type_id INTO lv_exist;
725 IF c_inquiry_type_id%NOTFOUND THEN
726 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E313', 8405);
727 UPDATE
728 igr_i_appl_int
729 SET
730 status = '3',
731 error_code = 'E313', --Error code for validation for field Inquiry Type Id
732 error_text = l_error_text
733 WHERE
734 interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
735
736 p_validation := FALSE;
737
738 CLOSE c_inquiry_type_id;
739 RETURN;
740 END IF;
741
742 IF c_inquiry_type_id%ISOPEN THEN
743 CLOSE c_inquiry_type_id;
744 END IF;
745
746 /* validation for Package items Reduction indicator */
747 IF p_inquiry_dtls_rec.pkg_reduct_ind IS NOT NULL THEN
748 IF p_inquiry_dtls_rec.pkg_reduct_ind NOT IN ('Y','N') THEN
749 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E360', 8405);
750 UPDATE igr_i_appl_int
751 SET status = '3',
752 error_code = 'E360' ,/*Error code for validation for field Package items Reduction Ind */
753 error_text = l_error_text
754 WHERE
755 interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
756 p_validation := FALSE;
757 RETURN;
758 END IF;
759 END IF;
760
761 IF p_inquiry_dtls_rec.inquiry_entry_level_id IS NOT NULL THEN
762 /* validation for inquiry entry level id */
763 OPEN c_inquiry_entry_level_id (p_inquiry_dtls_rec.inquiry_entry_level_id);
764 FETCH c_inquiry_entry_level_id INTO lv_exist;
765 IF c_inquiry_entry_level_id%NOTFOUND THEN
766 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E305', 8405);
767 UPDATE
768 igr_i_appl_int
769 SET
770 status = '3',
771 error_code = 'E305' ,/*Error code for validation for field Inquiry Entry Level Id*/
772 error_text = l_error_text
773 WHERE
774 interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
775
776 p_validation := FALSE;
777 CLOSE c_inquiry_entry_level_id;
778 RETURN;
779 END IF;
780
781 IF c_inquiry_entry_level_id%ISOPEN THEN
782 CLOSE c_inquiry_entry_level_id;
783 END IF;
784 END IF;
785
786 /* validation for registering person id */
787 IF p_inquiry_dtls_rec.registering_person_id IS NOT NULL THEN
788 OPEN c_registering_person_id (p_inquiry_dtls_rec.registering_person_id);
789 FETCH c_registering_person_id INTO lv_exist;
790
791 IF c_registering_person_id%NOTFOUND THEN
792 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E306', 8405);
793
794 UPDATE igr_i_appl_int
795 SET status = '3',
796 error_code = 'E306' , /*Error code for validation for field Registering person Id*/
797 error_text = l_error_text
798 WHERE
799 interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
800 p_validation := FALSE;
801 CLOSE c_registering_person_id;
802 RETURN;
803 END IF;
804
805 IF c_registering_person_id%ISOPEN THEN
806 CLOSE c_registering_person_id;
807 END IF;
808 END IF;
809
810 /* validation for override process indicator */
811 IF p_inquiry_dtls_rec.override_process_ind IS NOT NULL THEN
812 IF p_inquiry_dtls_rec.override_process_ind NOT IN ('Y','N') THEN
813 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E307', 8405);
814 UPDATE igr_i_appl_int
815 SET status = '3',
816 error_code = 'E307' ,/*Error code for validation for field Override Process Ind */
817 error_text = l_error_text
818 WHERE
819 interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
820 p_validation := FALSE;
821 RETURN;
822 END IF;
823 END IF;
824
825 /* validation for education goal id */
826 IF p_inquiry_dtls_rec.edu_goal_id IS NOT NULL THEN
827 OPEN c_edu_goal_id (p_inquiry_dtls_rec.edu_goal_id);
828 FETCH c_edu_goal_id INTO lv_exist;
829
830 IF c_edu_goal_id%NOTFOUND THEN
831 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E308', 8405);
832 UPDATE igr_i_appl_int
833 SET status = '3',
834 error_code = 'E308', /*Error code for validation for field Education Goal Id*/
835 error_text = l_error_text
836 WHERE
837 interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
838
839 p_validation := FALSE;
840 CLOSE c_edu_goal_id;
841 RETURN;
842 END IF;
843 IF c_edu_goal_id%ISOPEN THEN
844 CLOSE c_edu_goal_id;
845 END IF;
846 END IF;
847
848 /* validation for inquiry school of interest */
852 IF c_inq_school_of_interest_id%NOTFOUND THEN
849 IF p_inquiry_dtls_rec.inquiry_school_of_interest_id IS NOT NULL THEN
850 OPEN c_inq_school_of_interest_id (p_inquiry_dtls_rec.inquiry_school_of_interest_id);
851 FETCH c_inq_school_of_interest_id INTO lv_exist;
853 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E309', 8405);
854 UPDATE igr_i_appl_int
855 SET status = '3',
856 error_code = 'E309' ,/*Error code for validation for field Inquiry School of Interest Id*/
857 error_text = l_error_text
858 WHERE
859 interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
860
861 p_validation := FALSE;
862 CLOSE c_inq_school_of_interest_id;
863 RETURN;
864 END IF;
865
866 IF c_inq_school_of_interest_id%ISOPEN THEN
867 CLOSE c_inq_school_of_interest_id;
868 END IF;
869 END IF;
870
871
872 /* validation for learn source id */
873 IF p_inquiry_dtls_rec.learn_source_id IS NOT NULL THEN
874 OPEN c_learn_source_id (p_inquiry_dtls_rec.learn_source_id);
875 FETCH c_learn_source_id INTO lv_exist;
876 IF c_learn_source_id%NOTFOUND THEN
877 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E310', 8405);
878 UPDATE igr_i_appl_int
879 SET status = '3',
880 error_code = 'E310' ,/*Error code for validation for field Learn Source Id*/
881 error_text = l_error_text
882 WHERE
883 interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
884
885 p_validation := FALSE;
886 CLOSE c_learn_source_id;
887 RETURN;
888 END IF;
889 IF c_learn_source_id%ISOPEN THEN
890 CLOSE c_learn_source_id;
891 END IF;
892 END IF;
893
894 /* validation for influence source id */
895 IF p_inquiry_dtls_rec.influence_source_id IS NOT NULL THEN
896 OPEN c_influence_source_id (p_inquiry_dtls_rec.influence_source_id);
897 FETCH c_influence_source_id INTO lv_exist;
898 IF c_influence_source_id%NOTFOUND THEN
899 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E311', 8405);
900 UPDATE igr_i_appl_int
901 SET status = '3',
902 error_code = 'E311' ,/*Error code for validation for field Influence Source Id*/
903 error_text = l_error_text
904 WHERE
905 interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
906
907 p_validation := FALSE;
908 CLOSE c_influence_source_id;
909 RETURN;
910 END IF;
911 IF c_influence_source_id%ISOPEN THEN
912 CLOSE c_influence_source_id;
913 END IF;
914 END IF;
915
916 /* validation for the decriptive flex field attributes */
917 IF p_inquiry_dtls_rec.attribute_category IS NOT NULL THEN
918 IF NOT igs_ad_imp_018.validate_desc_flex
919 ( P_ATTRIBUTE_CATEGORY => p_inquiry_dtls_rec.attribute_category,
920 P_ATTRIBUTE1 => p_inquiry_dtls_rec.attribute1,
921 P_ATTRIBUTE2 => p_inquiry_dtls_rec.attribute2,
922 P_ATTRIBUTE3 => p_inquiry_dtls_rec.attribute3,
923 P_ATTRIBUTE4 => p_inquiry_dtls_rec.attribute4,
924 P_ATTRIBUTE5 => p_inquiry_dtls_rec.attribute5,
925 P_ATTRIBUTE6 => p_inquiry_dtls_rec.attribute6,
926 P_ATTRIBUTE7 => p_inquiry_dtls_rec.attribute7,
927 P_ATTRIBUTE8 => p_inquiry_dtls_rec.attribute8,
928 P_ATTRIBUTE9 => p_inquiry_dtls_rec.attribute9,
929 P_ATTRIBUTE10 => p_inquiry_dtls_rec.attribute10,
930 P_ATTRIBUTE11 => p_inquiry_dtls_rec.attribute11,
931 P_ATTRIBUTE12 => p_inquiry_dtls_rec.attribute12,
932 P_ATTRIBUTE13 => p_inquiry_dtls_rec.attribute13,
933 P_ATTRIBUTE14 => p_inquiry_dtls_rec.attribute14,
934 P_ATTRIBUTE15 => p_inquiry_dtls_rec.attribute15,
935 P_ATTRIBUTE16 => p_inquiry_dtls_rec.attribute16,
936 P_ATTRIBUTE17 => p_inquiry_dtls_rec.attribute17,
937 P_ATTRIBUTE18 => p_inquiry_dtls_rec.attribute18,
938 P_ATTRIBUTE19 => p_inquiry_dtls_rec.attribute19,
939 P_ATTRIBUTE20 => p_inquiry_dtls_rec.attribute20,
940 P_DESC_FLEX_NAME => 'IGR_S_INQUIRY_FLEX'
941 ) THEN
942
943 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E008', 8405);
944 UPDATE igr_i_appl_int
945 SET status = '3',
946 error_code = 'E008' ,/*Using this Error code for validation for field Flex Field as no error code specified for this*/
947 error_text = l_error_text
948 WHERE
949 interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
950
951 p_validation := FALSE;
952 RETURN;
953 END IF;
954 END IF;
955
956 /* validation for academic cal type and ci sequence number */
957 IF p_inquiry_dtls_rec.acad_cal_type IS NOT NULL THEN
958 OPEN c_acad_cal_type_ci (p_inquiry_dtls_rec.acad_cal_type,
959 p_inquiry_dtls_rec.acad_ci_sequence_number);
960
961
962 FETCH c_acad_cal_type_ci INTO lv_exist;
963 IF c_acad_cal_type_ci%NOTFOUND THEN
964 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E301', 8405);
965 UPDATE igr_i_appl_int
966 SET status = '3',
967 error_code = 'E301' ,/*Using this Error code for validation for field Acad Cal type ci seq number as no specific error code specified for this*/
968 error_text = l_error_text
969 WHERE interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
970
974 END IF;
971 p_validation := FALSE;
972 CLOSE c_acad_cal_type_ci;
973 RETURN;
975
976 IF c_acad_cal_type_ci%ISOPEN THEN
977 CLOSE c_acad_cal_type_ci;
978 END IF;
979 END IF;
980
981 /* validation for admission cal type and ci sequence number */
982 IF p_inquiry_dtls_rec.adm_cal_type IS NOT NULL THEN
983 OPEN c_adm_cal_type_ci (p_inquiry_dtls_rec.adm_cal_type,
984 p_inquiry_dtls_rec.adm_ci_sequence_number);
985
986
987 FETCH c_adm_cal_type_ci INTO lv_exist;
988 IF c_adm_cal_type_ci%NOTFOUND THEN
989 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E301', 8405);
990 UPDATE igr_i_appl_int
991 SET status = '3',
992 error_code = 'E301' ,/* Using this Error code for validation for field Adm Cal type ci seq number as no specific error code specified*/
993 error_text = l_error_text
994 WHERE
995 interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
996 p_validation := FALSE;
997 CLOSE c_adm_cal_type_ci;
998 RETURN;
999 END IF;
1000
1001 IF c_adm_cal_type_ci%ISOPEN THEN
1002 CLOSE c_adm_cal_type_ci;
1003 END IF;
1004 END IF;
1005
1006 /* Validation to check for Source Promotion */
1007 IF p_inquiry_dtls_rec.source_promotion_id IS NOT NULL THEN
1008 OPEN c_source_promotion(p_inquiry_dtls_rec.source_promotion_id);
1009 FETCH c_source_promotion INTO lv_exist;
1010
1011 IF c_source_promotion%NOTFOUND THEN
1012 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E584', 8405);
1013 UPDATE
1014 igr_i_appl_int
1015 SET
1016 status = '3',
1017 error_code = 'E584' , /* Error code if source promotion ID is not correct */
1018 error_text = l_error_text
1019 WHERE
1020 interface_inq_appl_id = p_inquiry_dtls_rec.interface_inq_appl_id ;
1021 p_validation := FALSE;
1022 CLOSE c_source_promotion;
1023 RETURN;
1024 END IF;
1025
1026 IF c_source_promotion%ISOPEN THEN
1027 CLOSE c_source_promotion;
1028 END IF;
1029 END IF ;
1030
1031
1032 /* Setting the validation flag to TRUE in case there are no validation errors*/
1033 p_validation := TRUE;
1034 END validate_person_inquiry;
1035
1036
1037 PROCEDURE process_person_inquiry (
1038 p_interface_run_id IN NUMBER,
1039 p_source_type_id IN NUMBER,
1040 p_enable_log IN VARCHAR2,
1041 p_rule IN VARCHAR2) AS
1042 /*************************************************************
1043 Created By :Syam.Krishnan
1044 Date Created By :12-JUN-2001
1045 Purpose : To process person inquiries
1046 Know limitations, enhancements or remarks
1047 Change History
1048 Who When What
1049 rrengara 11-FEB-2003 As a part of Build RCT. All the funnel status logic has been moved to TBH
1050 mesriniv 19-FEB-2002 Funnel Status Values for IDENTIFIED,CONTACTED,INQUIRED
1051 were changed as below as per the SWCR001 Person Change Build
1052 Bug :2203778
1053 sykrishn 05/09 IDOPA2 Changes
1054 (reverse chronological order - newest change first)
1055 ***************************************************************/
1056 --------------Variable Declaration-----------------------------------------
1057 lb_validation boolean ;
1058 v_pr_inq_status varchar2(1);
1059 l_records_processed NUMBER;
1060
1061 l_request_id NUMBER;
1062 l_error_text VARCHAR2(2000);
1063
1064 --------------End Variable Declaration-----------------------------------------
1065 BEGIN
1066 l_records_processed := 0;
1067 lb_validation := FALSE;
1068
1069 IF (l_request_id IS NULL) THEN
1070 l_request_id := fnd_global.conc_request_id;
1071 END IF;
1072
1073 /* loop across the inquiry details interface records */
1074 FOR v_inquiry_dtls_rec IN c_inquiry_dtls (p_interface_run_id) LOOP
1075 l_records_processed := l_records_processed + 1;
1076 SAVEPOINT perinq_save;
1077 /* procedure to validate the data elements in inquiry details records*/
1078 validate_person_inquiry (v_inquiry_dtls_rec,lb_validation);
1079 /* If validation is passed then create the person inquiry */
1080 IF lb_validation THEN
1081 /* procedure to create person inquiry */
1082 create_person_inquiry (v_inquiry_dtls_rec,p_source_type_id,v_pr_inq_status);
1083 END IF;
1084 IF l_records_processed = 100 THEN
1085 COMMIT;
1086 l_records_processed := 0;
1087 END IF;
1088 END LOOP;
1089 IF l_records_processed < 100 AND l_records_processed > 0 THEN
1090 COMMIT;
1091 END IF;
1092 END process_person_inquiry;
1093
1094 PROCEDURE validate_inquiry_lines ( p_inquiry_lines_rec IN c_inquiry_lines%ROWTYPE,
1095 p_validation OUT NOCOPY BOOLEAN )
1096 AS
1097 /*************************************************************
1098 Created By :Ramesh.Rengarajan
1099 Date Created By :5-FEB-2003
1100 Purpose : To validate inquiry lines
1101 Know limitations, enhancements or remarks
1102 Change History
1103 Who When What
1104
1105 (reverse chronological order - newest change first)
1106 ***************************************************************/
1107
1111 l_error_text VARCHAR2(2000);
1108 --------------------Variable Declaration-----------------------------------------------
1109 lv_exists VARCHAR2(2000);
1110
1112 --------------------End Variable Declaration-----------------------------------------------
1113
1114 ---------------------Cursor Declaration------------------------------------------------------------
1115
1116 /*Cursor to validate the Product Category Id and Product Category Set ID *****/
1117 CURSOR c_val_acad_int(cp_product_category_id igr_i_lines_int.product_category_id%TYPE,
1118 cp_product_category_set_id igr_i_lines_int.product_category_set_id%TYPE) IS
1119 SELECT 'X'
1120 FROM ENI_PROD_DENORM_HRCHY_V EPDHV,
1121 ENI_PROD_DEN_HRCHY_PARENTS_V P,
1122 MTL_CATEGORIES_V C
1123 WHERE P.CATEGORY_ID = EPDHV.CHILD_ID
1124 AND EPDHV.PARENT_ID = C.CATEGORY_ID
1125 AND C.DESCRIPTION = 'OSS Academic Interest'
1126 AND EPDHV.PARENT_ID <> P.CATEGORY_ID
1127 AND P.PURCHASE_INTEREST = 'Y'
1128 AND ( P.DISABLE_DATE IS NULL OR P.DISABLE_DATE > SYSDATE )
1129 AND P.category_id = cp_product_category_id
1130 AND P.category_set_id = cp_product_category_set_id;
1131
1132
1133 ---------------------End Cursor Declaration------------------------------------------------------------
1134 BEGIN
1135 /* setting the validation flag to true by default */
1136 p_validation := TRUE;
1137
1138 OPEN c_val_acad_int(p_inquiry_lines_rec.product_category_id, p_inquiry_lines_rec.product_category_set_id );
1139 FETCH c_val_acad_int INTO lv_exists;
1140 IF c_val_acad_int%NOTFOUND THEN
1141
1142 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E314', 8405);
1143
1144 UPDATE
1145 igr_i_lines_int
1146 SET
1147 status = 3,
1148 error_code = 'E314',
1149 error_text = l_error_text
1150 WHERE
1151 interface_lines_id = p_inquiry_lines_rec.interface_lines_id;
1152
1153 p_validation := FALSE;
1154 CLOSE c_val_acad_int;
1155 RETURN;
1156 END IF;
1157 CLOSE c_val_acad_int;
1158
1159 /**if all validations passed set it to true **/
1160 p_validation := TRUE;
1161 END validate_inquiry_lines;
1162
1163 PROCEDURE process_inquiry_lines (
1164 p_interface_run_id IN NUMBER,
1165 p_enable_log IN VARCHAR2,
1166 p_rule IN VARCHAR2) AS
1167
1168 /*************************************************************
1169 Created By :Ramesh.Rengarajan
1170 Date Created By :5-FEB-2003
1171 Purpose : To Process Inquiry Programs
1172 Know limitations, enhancements or remarks
1173 Change History
1174 Who When What
1175
1176 (reverse chronological order - newest change first)
1177 ***************************************************************/
1178 lv_msg_count NUMBER ;
1179 lv_msg_data VARCHAR2(9000);
1180 lv_return_status VARCHAR2(1);
1181 lb_validation boolean ;
1182 l_sales_lead_line_id igr_i_a_lines.sales_lead_line_id%TYPE;
1183 lv_rowid VARCHAR2(100);
1184 l_records_processed NUMBER ;
1185
1186 l_prog_label VARCHAR2(100);
1187 l_error_code VARCHAR2(30);
1188 l_request_id NUMBER;
1189 l_label VARCHAR2(100);
1190 l_debug_str VARCHAR2(2000);
1191 l_enable_log VARCHAR2(1);
1192 l_rowid VARCHAR2(25);
1193 l_error_text VARCHAR2(2000);
1194 l_type VARCHAR2(1);
1195 l_status VARCHAR2(1);
1196
1197
1198 BEGIN
1199 l_records_processed := 0;
1200 lb_validation := FALSE;
1201 IF (l_request_id IS NULL) THEN
1202 l_request_id := fnd_global.conc_request_id;
1203 END IF;
1204
1205 /** Iterate in Cursor for Inquiry lines **/
1206 FOR v_inquiry_lines_rec IN c_inquiry_lines (p_interface_run_id) LOOP
1207
1208 l_records_processed := l_records_processed + 1;
1209 SAVEPOINT perinqlin_save;
1210
1211 validate_inquiry_lines (v_inquiry_lines_rec,lb_validation);
1212
1213 IF lb_validation THEN
1214
1215 BEGIN
1216 igr_inquiry_lines_pkg.insert_row (
1217 x_mode => 'R',
1218 x_rowid => lv_rowid,
1219 x_sales_lead_line_id => l_sales_lead_line_id,
1220 x_person_id => v_inquiry_lines_rec.person_id,
1221 x_enquiry_appl_number => v_inquiry_lines_rec.enquiry_appl_number,
1222 x_enquiry_dt => v_inquiry_lines_rec.inquiry_date,
1223 x_inquiry_method_code => v_inquiry_lines_rec.inquiry_source_type,
1224 x_preference => v_inquiry_lines_rec.preference,
1225 x_ret_status => lv_return_status,
1226 x_msg_data => lv_msg_data,
1227 x_msg_count => lv_msg_count,
1228 x_product_category_id => v_inquiry_lines_rec.product_category_id,
1229 x_product_category_set_id => v_inquiry_lines_rec.product_category_set_id
1230 );
1231
1232 IF lv_return_status <>'U' AND lv_msg_data IS NOT NULL THEN
1233
1234 IF l_enable_log = 'Y' THEN
1235 igs_ad_imp_001.logerrormessage(v_inquiry_lines_rec.interface_inq_appl_id,lv_msg_data);
1236 END IF;
1237
1238 ELSIF lv_return_status = 'U' THEN
1239
1240 IF fnd_log.test(fnd_log.level_exception,l_prog_label) THEN
1241 l_label := 'igs.plsql.igr_imp_003.process_inquiry_lines.exception '||'E322';
1242 fnd_message.set_name('IGS','IGS_PE_IMP_DET_ERROR');
1243 fnd_message.set_token('CONTEXT',v_inquiry_lines_rec.interface_lines_id);
1244 fnd_message.set_token('ERROR', l_error_text);
1245 l_debug_str := fnd_message.get;
1246 fnd_log.string_with_context( fnd_log.level_exception,l_label,l_debug_str, NULL,NULL,NULL,NULL,NULL,TO_CHAR(l_request_id));
1247 END IF;
1248
1249 END IF;
1250
1251 IF lv_msg_data IS NOT NULL THEN
1252
1253 /* Return status of Null should be treated as Success */
1254 IF NVL(lv_return_status,'S') = 'S' THEN
1255 UPDATE igr_i_lines_int
1256 SET status = '4',
1257 error_code = 'E702',
1258 error_text = lv_msg_data
1259 WHERE rowid = v_inquiry_lines_rec.rowid;
1260 ELSE
1261 ROLLBACK TO perinqlin_save;
1262 UPDATE igr_i_lines_int
1263 SET status = '3',
1264 error_code = 'E322',
1265 error_text = lv_msg_data
1266 WHERE rowid = v_inquiry_lines_rec.rowid;
1267 END IF;
1268
1269 ELSE
1270
1271 /* Return status of Null should be treated as Success */
1272 IF NVL(lv_return_status,'S') = 'S' THEN
1273 UPDATE igr_i_lines_int
1274 SET status = '1',
1275 error_code = NULL,
1276 error_text = NULL
1277 WHERE rowid = v_inquiry_lines_rec.rowid;
1278 ELSE
1279
1280 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E322', 8405);
1281 ROLLBACK TO perinqlin_save;
1282 UPDATE igr_i_lines_int
1283 SET status = '3',
1284 error_code = 'E322',
1285 error_text = l_error_text
1286 WHERE rowid = v_inquiry_lines_rec.rowid;
1287 END IF;
1288
1289 END IF;
1290
1291 EXCEPTION
1292 WHEN OTHERS THEN
1293 ROLLBACK TO perinqlin_save;
1294 l_error_text := igs_ad_gen_016.get_lkup_meaning ('IMPORT_ERROR_CODE', 'E322', 8405);
1295 UPDATE igr_i_lines_int
1296 SET status = '3',
1297 error_code = 'E322' ,
1298 error_text = l_error_text
1299 WHERE interface_lines_id = v_inquiry_lines_rec.interface_lines_id;
1300 END;
1301
1302 END IF;
1303
1304 IF l_records_processed = 100 THEN
1305 COMMIT;
1306 l_records_processed := 0;
1307 END IF;
1308
1309 END LOOP;
1310
1311 IF l_records_processed < 100 AND l_records_processed > 0 THEN
1312 COMMIT;
1313 END IF;
1314
1315 END process_inquiry_lines;
1316
1317 END IGR_IMP_003;
[Home] [Help]