1 PACKAGE BODY igr_gen_001 AS
2 /* $Header: IGSRT01B.pls 120.2 2006/01/31 23:48:59 rghosh noship $ */
3
4 /****************************************************************************
5 Created By : RBODDU
6 Date Created On : 12-FEB-2003
7 Purpose : 2664699
8
9 Change History
10 Who When What
11 sjlaport 07-Mar-2005 Modified for APC - bug #3799487
12 sjlaport 17-Feb-2005 Added Function Admp_Del_Eap_Eitpi and Admp_Ins_Eap_Eitpi
13 for IGR Migration
14 hreddych 26-may-2003 Capture Event Campaign
15 Added the x_source_promotion_id in the call to
16 igr_inquiry_pkg
17 rghosh 10-May-2005 Removed the procedure update_mailed_dt_inq_info and
18 replaced the calls to the proceure update_mailed_dt_inq_info
19 with update_mailed_dt. This is the new functionality
20 from the bug 4354270
21
22 (reverse chronological order - newest change first)
23 *****************************************************************************/
24
25 PROCEDURE admp_upd_eap_avail(
26 errbuf OUT NOCOPY VARCHAR2,
27 retcode OUT NOCOPY NUMBER,
28 p_process_status IN VARCHAR2 ,
29 p_package_unavailable IN VARCHAR2 ,
30 p_package_incomplete IN VARCHAR2 ,
31 p_responsible_user IN NUMBER,
32 p_default_user IN NUMBER,
33 p_inq_src_type IN VARCHAR2,
34 p_product_category_id IN NUMBER ,
35 p_inq_date_low IN VARCHAR2 ,
36 p_inq_date_high IN VARCHAR2 ,
37 p_inq_info_type IN igr_i_info_types_v.info_type_id%TYPE ) -- New param added as part of IDOPA2
38 IS
39 /****************************************************************************
40 Created By :
41 Date Created On :
42 Purpose :
43
44 Change History
45 Who When What
46 pkpatel 04-JUN-2001 Erased the parameter p_inquiry_date and added parameters to
47 select person records as per the date range and according to
48 Inquiry source type and Inquiry Entry Status
49 pkpatel 13-JUN-2001 Modified the parameter p_inq_entry_stat_id from Mandatory to Default
50 nshee 13-JAN-2003 As part of fix for bug 2645948, Inquiry source type is not to be a mandatory parameter.
51 Changed definition of cursor c_eap_es
52 sjlaport 07-MAR-2005 Changed definition of cursor c_eap_es for APC - bug #3799487
53 *****************************************************************************/
54
55 --declared variable to convert from VARCHAR2 to DATE
56 p_enquiry_dt_high DATE;
57 p_enquiry_dt_low DATE;
58
59 BEGIN -- admp_upd_eap_avail
60 -- This module processes all enquiry appl which do not have a system status
61 -- of COMPLETE. Depending on the availability of package items, it may update
62 -- the IGS_IN_ENQUIRY_APPL.enquiry_status to one of the following system statuses.
63 -- * Registered to Complete If all package items are available.
64 -- * Registered to Acknowledge If none or some of the package items are
65 -- available.
66 -- * Acknowledge to Complete If all unsent package items are available.
67 -- * Acknowledge (no change) If none or some of the unsent package items
68 -- are available.
69
70 -- The following code is added for disabling of OSS in R12.IGS.A - Bug 4955201
71 igs_ge_gen_003.set_org_id(null);
72
73 retcode := 0;
74 --Converted the variables from VARCHAR2 to DATE
75 p_enquiry_dt_high := IGS_GE_DATE.IGSDATE(p_inq_date_high);
76 p_enquiry_dt_low := IGS_GE_DATE.IGSDATE(p_inq_date_low);
77
78 -- To check whether the Entered High date is smaller than Entered Low Date
79 -- And if yes stop the Processing.
80 IF p_enquiry_dt_high < p_enquiry_dt_low THEN
81 FND_MESSAGE.SET_NAME('IGS','IGS_AD_DATE_VALIDATION');
82 IGS_GE_MSG_STACK.ADD;
83 APP_EXCEPTION.RAISE_EXCEPTION;
84 END IF;
85 DECLARE
86 cst_acknowlege CONSTANT VARCHAR2(30) := 'OSS_ACKNOWLEGE';
87 cst_complete CONSTANT VARCHAR2(30) := 'OSS_COMPLETE';
88 cst_registered CONSTANT VARCHAR2(30) := 'OSS_REGISTERED';
89 cst_both CONSTANT VARCHAR2(5) := 'BOTH';
90 v_person_id igr_i_appl_all.person_id%TYPE;
91 v_enquiry_appl_number igr_i_appl_all.enquiry_appl_number%TYPE;
92 v_process_ind igr_i_appl_all.override_process_ind%TYPE;
93 v_complete_status igr_i_status_v.enquiry_status%TYPE;
94 v_acknowledge_status igr_i_status_v.enquiry_status%TYPE;
95 v_group_id igs_pe_persid_group.group_id%TYPE;
96 v_person_id_group_created VARCHAR2(1);
97 v_process_dt DATE;
98 v_enquiries_processed NUMBER DEFAULT 0;
99 v_enquiries_completed NUMBER DEFAULT 0;
100 v_enquiries_acknowledged NUMBER DEFAULT 0;
101 v_dummy VARCHAR2(1);
102 e_resource_busy EXCEPTION;
103 l_ret_status VARCHAR2(3);
104 l_msg_data VARCHAR2(3000);
105 l_msg_count NUMBER;
106 l_s_enq_stat igr_i_appl_v.s_enquiry_status%TYPE;
107 PRAGMA EXCEPTION_INIT (e_resource_busy, -54);
108
109 v_group_cd igs_pe_persid_group_all.group_cd%TYPE;
110
111 -- Modified the following cursor as part of build of CRM recruitment changes. Bug: 2664699
112 CURSOR c_es (cp_enquiry_status as_statuses_vl.status_code%TYPE) IS
113 SELECT es.enquiry_status
114 FROM igr_i_status_v es
115 WHERE es.s_enquiry_status = cp_enquiry_status AND
116 es.enabled_flag = 'Y';
117
118 --Changed the Cursor to check the Enquiry_date between low and high date AND Enquiry source type
119 --and Enquiry Entry status passed are valid for the Persons. Modified the following cursor as part of CRM recruitment
120 --changes. Bug: 2664699
121 CURSOR c_eap_es (cp_s_enquiry_status as_statuses_vl.status_code%TYPE) IS
122 SELECT eap.*
123 FROM igr_i_appl eap,
124 igr_i_status_v es
125 WHERE ((eap.enquiry_dt >= p_enquiry_dt_low OR p_enquiry_dt_low IS NULL) AND
126 (eap.enquiry_dt <= p_enquiry_dt_high OR p_enquiry_dt_high IS NULL)) AND
127 eap.enquiry_status = es.enquiry_status AND
128 es.s_enquiry_status = cp_s_enquiry_status AND
129 (eap.inquiry_method_code = p_inq_src_type OR p_inq_src_type IS NULL) AND
130 (p_product_category_id IS NULL OR EXISTS (SELECT 'X' FROM igr_i_a_lines_v alin
131 WHERE alin.person_id = eap.person_id AND
132 alin.enquiry_appl_number = eap.enquiry_appl_number AND
133 alin.product_category_id = p_product_category_id )) AND
134 (p_inq_info_type IS NULL OR EXISTS (SELECT 'X' FROM igr_i_a_itype ityp
135 WHERE ityp.person_id = eap.person_id AND
136 ityp.enquiry_appl_number = eap.enquiry_appl_number AND
137 ityp.info_type_id = p_inq_info_type))
138 FOR UPDATE OF eap.enquiry_status, eap.last_process_dt NOWAIT;
139
140
141 --Cursor to check if there are any unsent (Mailed Date Null) available for the current Inquiry Instance.
142 --Modified the cursor to fetch the Inquiry Instances having at least Package Item's Mailed Date as NULL. Bug:2664699
143 CURSOR c_eapmpi ( cp_person_id igr_i_a_pkgitm.person_id%TYPE,
144 cp_enquiry_appl_number igr_i_a_pkgitm.enquiry_appl_number%TYPE) IS
145 SELECT 'X'
146 FROM igr_i_a_pkgitm eapmpi,
147 igr_i_pkg_items_v epi
148 WHERE eapmpi.person_id = cp_person_id AND
149 eapmpi.enquiry_appl_number = cp_enquiry_appl_number AND
150 eapmpi.mailed_dt IS NULL AND
151 eapmpi.package_item_id = epi.package_item_id ;
152
153
154 -- Modified the following cursor as part of build of CRM recruitment changes. Bug: 2664699
155 -- Modified to verify the Non-Availability of package item depending on the available date range.
156 CURSOR c_eapmpi_epi ( cp_person_id igr_i_a_pkgitm.person_id%TYPE,
157 cp_enquiry_appl_number igr_i_a_pkgitm.enquiry_appl_number%TYPE) IS
158 SELECT 'X'
159 FROM igr_i_a_pkgitm eapmpi,
160 igr_i_pkg_items_v epi
161 WHERE eapmpi.person_id = cp_person_id AND
162 eapmpi.enquiry_appl_number = cp_enquiry_appl_number AND
163 eapmpi.mailed_dt IS NULL AND
164 eapmpi.package_item_id = epi.package_item_id AND
165 (epi.actual_avail_from_date > SYSDATE OR
166 epi.actual_avail_to_date < SYSDATE);
167
168 -- Modified the following cursor as part of build of CRM recruitment changes. Bug: 2664699
169 -- Modified to verify the Availability of package item depending on the available date range.
170 CURSOR c_eapmpi_get_epi ( cp_person_id igr_i_a_pkgitm.person_id%TYPE,
171 cp_enquiry_appl_number igr_i_a_pkgitm.enquiry_appl_number%TYPE) IS
172 SELECT 'X'
173 FROM igr_i_a_pkgitm eapmpi,
174 igr_i_pkg_items_v epi
175 WHERE eapmpi.person_id = cp_person_id AND
176 eapmpi.enquiry_appl_number = cp_enquiry_appl_number AND
177 eapmpi.mailed_dt IS NULL AND
178 eapmpi.package_item_id = epi.package_item_id AND
179 (epi.actual_avail_from_date <= SYSDATE) AND
180 (epi.actual_avail_to_date >= SYSDATE) AND
181 NVL(eapmpi.donot_mail_ind,'N') = 'N';
182
183
184 /** IDOPA2 New cursors added to take care of package items if p_inq_info_type is not null **/
185
186 -- Modified the following cursor as part of build of CRM recruitment changes. Bug: 2664699
187 CURSOR c_pkgitems_exist (
188 cp_person_id igr_i_a_pkgitm.person_id%TYPE,
189 cp_enquiry_appl_number igr_i_a_pkgitm.enquiry_appl_number%TYPE) IS
190 SELECT 'X'
191 FROM igr_i_a_pkgitm eapmpi, --Transaction table holding Package items of the inquiry instance
192 igr_i_pkg_items_v epi --Setup (joined with CRM setup to hold all the attributes of Packge Items)
193 WHERE eapmpi.person_id = cp_person_id AND
194 eapmpi.enquiry_appl_number = cp_enquiry_appl_number AND
195 eapmpi.mailed_dt IS NULL AND
196 eapmpi.package_item_id = epi.package_item_id AND
197 NVL(eapmpi.donot_mail_ind,'N') = 'N' ;
198
199 --Cursor to fetch the inquiry instances having the UNSENT (Mailed Date as NULL) package items for the
200 --given enquiry instance. Added as part of Bug: 2664699
201 CURSOR c_pkgitems_exist_more (
202 cp_person_id igr_i_a_pkgitm.person_id%TYPE,
203 cp_enquiry_appl_number igr_i_a_pkgitm.enquiry_appl_number%TYPE) IS
204 SELECT 'X'
205 FROM igr_i_a_pkgitm eapmpi, --Transaction table (Inquiry Instance table holding Package items)
206 igr_i_pkg_items_v epi
207 WHERE eapmpi.person_id = cp_person_id AND
208 eapmpi.enquiry_appl_number = cp_enquiry_appl_number AND
209 eapmpi.mailed_dt IS NULL AND
210 eapmpi.package_item_id = epi.package_item_id AND
211 (epi.actual_avail_from_date <= SYSDATE) AND
212 (epi.actual_avail_to_date >= SYSDATE) AND
213 NVL(eapmpi.donot_mail_ind,'N') = 'N' ;
214
215 -- Modified the following cursor as part of build of CRM recruitment changes. Bug: 2664699
216 -- The following cursor if returns Row(s) then it means that the corresponding Package Item(s) are Available.
217 CURSOR c_pkgitems_avail (
218 cp_person_id igr_i_a_pkgitm.person_id%TYPE,
219 cp_enquiry_appl_number igr_i_a_pkgitm.enquiry_appl_number%TYPE) IS
220 SELECT 'X'
221 FROM igr_i_a_pkgitm eapmpi,
222 igr_i_pkg_items_v epi
223 WHERE eapmpi.person_id = cp_person_id AND
224 eapmpi.enquiry_appl_number = cp_enquiry_appl_number AND
225 eapmpi.mailed_dt IS NULL AND
226 eapmpi.package_item_id = epi.package_item_id AND
227 (epi.actual_avail_from_date > SYSDATE OR --Checks for UNAVAILABLE condition.
228 epi.actual_avail_to_date < SYSDATE);
229
230 --Cursor to get the AVAILABLE package Items for the given Inquiry Instance.Bug: 2664699
231 CURSOR c_pkgitems_get_avail (
232 cp_person_id igr_i_a_pkgitm.person_id%TYPE,
233 cp_enquiry_appl_number igr_i_a_pkgitm.enquiry_appl_number%TYPE) IS
234 SELECT 'X'
235 FROM igr_i_a_pkgitm eapmpi,
236 igr_i_pkg_items_v epi
237 WHERE eapmpi.person_id = cp_person_id AND
238 eapmpi.enquiry_appl_number = cp_enquiry_appl_number AND
239 eapmpi.mailed_dt IS NULL AND
240 eapmpi.package_item_id = epi.package_item_id AND
241 (epi.actual_avail_from_date <= SYSDATE) AND --Checks for UNAVAILABLE condition.
242 (epi.actual_avail_to_date >= SYSDATE) AND
243 NVL(eapmpi.donot_mail_ind,'N') = 'N';
244
245 --Cursor to get the group code of the Person ID Group for a given group id (rghosh, bug#3973942: APC Integration Build)
246 CURSOR c_get_group_cd(cp_group_id IGS_PE_PERSID_GROUP_ALL.GROUP_ID%TYPE) IS
247 SELECT group_cd
248 FROM igs_pe_persid_group_all
249 WHERE group_id = cp_group_id;
250
251
252 /** IDOPA2 New cursors added to take care of package items if p_inq_info_type is not null **/
253
254 PROCEDURE update_mailed_dt (
255 p_person_id igr_i_a_pkgitm.person_id%TYPE,
256 p_enquiry_appl_number igr_i_a_pkgitm.enquiry_appl_number%TYPE,
257 p_mailed_dt igr_i_a_pkgitm.mailed_dt%TYPE)
258 IS
259 /****************************************************************************
260 Created By :
261 Date Created On :
262 Purpose :
263
264 Change History
265 Who When What
266 (reverse chronological order - newest change first)
267 *****************************************************************************/
268 BEGIN -- update_mailed_dt
269 -- This procedure updates the mailed date of available packaged items.
270 DECLARE
271
272 -- Modified the following cursor as part of build of CRM recruitment changes. Bug: 2664699
273 CURSOR c_eapmpi_epi IS
274 SELECT eapmpi.ROWID, eapmpi.*
275 FROM igr_i_a_pkgitm eapmpi,
276 igr_i_pkg_items_v epi -- Package Items setup view
277 WHERE eapmpi.person_id = p_person_id AND
278 eapmpi.enquiry_appl_number = p_enquiry_appl_number AND
279 eapmpi.mailed_dt IS NULL AND
280 eapmpi.package_item_id = epi.package_item_id AND
281 (epi.actual_avail_from_date <= SYSDATE) AND
282 (epi.actual_avail_to_date >= SYSDATE) AND
283 NVL(eapmpi.donot_mail_ind,'N') = 'N'
284 FOR UPDATE OF eapmpi.mailed_dt NOWAIT;
285
286 l_ret_status VARCHAR2(3);
287 l_msg_data VARCHAR2(2000);
288 l_msg_count NUMBER;
289 l_action VARCHAR2(255);
290 BEGIN
291 FOR rec_igs_in_applml_pkgitm IN c_eapmpi_epi LOOP
292
293 igr_i_a_pkgitm_pkg.update_row (
294 X_Mode => 'R',
295 X_RowId => rec_igs_in_applml_pkgitm.rowid,
296 X_Person_Id => rec_igs_in_applml_pkgitm.person_id,
297 X_Enquiry_Appl_Number => rec_igs_in_applml_pkgitm.enquiry_appl_number,
298 X_Package_Item_id => rec_igs_in_applml_pkgitm.package_item_id,
299 X_Mailed_Dt => p_mailed_dt,
300 X_donot_mail_ind => 'Y',
301 x_action => l_action,
302 x_ret_status => l_ret_status,
303 x_msg_data => l_msg_data,
304 x_msg_count => l_msg_count
305 );
306
307 END LOOP;
308 EXCEPTION
309 WHEN e_resource_busy THEN
310 IF c_eapmpi_epi%ISOPEN THEN
311 CLOSE c_eapmpi_epi;
312 END IF;
313 FND_MESSAGE.SET_NAME('IGS','IGS_AD_CANPRC_CURUPD_USER');
314 IGS_GE_MSG_STACK.ADD;
315 APP_EXCEPTION.RAISE_EXCEPTION;
316 WHEN OTHERS THEN
317 IF c_eapmpi_epi%ISOPEN THEN
318 CLOSE c_eapmpi_epi;
319 END IF;
320 app_exception.raise_exception;
321 END;
322 EXCEPTION
323 WHEN OTHERS THEN
324 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
325 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_012.update_mailed_dt');
326 IGS_GE_MSG_STACK.ADD;
327 App_Exception.Raise_Exception;
328 END update_mailed_dt;
329
330 /***********New local procedure added as part of IDOPA2 to update mailed_dt in case of inq_info_type not null ******/
331 PROCEDURE create_person_id_group_secur (
332 pl_group_id IGS_PE_PRSID_GRP_SEC.group_id%TYPE)
333 IS
334 /****************************************************************************
335 Created By :
336 Date Created On :
337 Purpose :
338
339 Change History
340 Who When What
341 (reverse chronological order - newest change first)
342 *****************************************************************************/
343
344 BEGIN -- create_person_id_group_secur
345 -- After creating the IGS_PE_PERSID_GROUP record, create a security record giving
346 -- select access on it, to the default user specified in the parameters..
347 DECLARE
348 lv_rowid VARCHAR2(25);
349 BEGIN
350 IF p_default_user IS NOT NULL THEN
351 igs_pe_prsid_grp_sec_pkg.insert_row (
352 X_Mode => 'R',
353 X_RowId => lv_rowid,
354 X_Group_Id => pl_group_id,
355 X_Person_Id => p_default_user,
356 X_Insert_Ind => 'N',
357 X_Update_Ind => 'N',
358 X_Delete_Ind => 'N'
359 );
360
361 END IF;
362 END;
363 EXCEPTION
364 WHEN OTHERS THEN
365 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
366 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_012.create_person_id_group_secur');
367 IGS_GE_MSG_STACK.ADD;
368 App_Exception.Raise_Exception;
369 END create_person_id_group_secur;
370
371 FUNCTION create_person_id_group(p_responsible_user IN NUMBER)
372 RETURN NUMBER
373 IS
374 /****************************************************************************
375 Created By :
376 Date Created On :
377 Purpose :
378
379 Change History
380 Who When What
381 (reverse chronological order - newest change first)
382 *****************************************************************************/
383
384 BEGIN -- create_person_id_group
385 DECLARE
386 l_group_cd igs_pe_persid_group.group_cd%TYPE;
387 l_req_id NUMBER;
388
389 -- cst_adm_enquiry CONSTANT VARCHAR2(10) := 'ADM_ENQY';
390 -- kamohan Bug 2382418
391 -- Changed from ADMISSION ENQUIRY to ADMISSION INQUIRY
392
393 cst_admission_enquiry CONSTANT VARCHAR2(20) := 'ADMISSION INQUIRY';
394 v_user_id IGS_PE_PERSON.person_id%TYPE;
395 v_group_id IGS_PE_PERSID_GROUP.group_id%TYPE;
396 lv_rowid VARCHAR2(25);
397 l_org_id NUMBER(15);
398 CURSOR c_get_next_seq IS
399 SELECT IGS_PE_PERSID_GROUP_GP_ID_S.NEXTVAL
400 FROM DUAL;
401 BEGIN
402 l_req_id := FND_GLOBAL.CONC_REQUEST_ID();
403 l_group_cd := igs_ad_gen_012.ret_group_cd();
404 -- Get the current user ID
405 v_user_id := p_responsible_user;
406 -- Get the next sequence number for the primary key
407 OPEN c_get_next_seq;
408 FETCH c_get_next_seq INTO v_group_id;
409 CLOSE c_get_next_seq;
410 l_org_id := igs_ge_gen_003.get_org_id;
411 igs_pe_persid_group_pkg.Insert_Row (
412 X_Mode => 'R',
413 X_RowId => lv_rowid,
414 X_Group_Id => v_group_id,
415 X_Group_Cd => l_group_cd,
416 X_Creator_Person_Id => v_user_id,
417 X_Description => cst_admission_enquiry || '-' ||IGS_GE_NUMBER.TO_CANN ( l_req_id),
418 X_Create_Dt => SYSDATE,
419 X_Closed_Ind => 'N',
420 X_Comments => Null ,
421 X_Org_Id => l_org_id
422 );
423
424 -- Since create IGS_PE_PERSID_GROUP is called several times and
425 -- create_person_id_group is used in each case. It made sense to have the
426 -- call actually in the create IGS_PE_PERSID_GROUP procedure.
427 -- After creating the IGS_PE_PERSID_GROUP record, create a security record giving
428 -- select access on it, to the default user specified in the parameters..
429 create_person_id_group_secur(v_group_id);
430 -- Note : p_group_id is an output parameter which returns the group_id so
431 -- that it can be used to trigger other jobs. This parameter has been
432 -- removed for now, as it was causing problems in the parameter form.
433 -- To be re-instated if functionality evolves in the future which allows
434 -- output parameters to be used as input to other jobs in JBS.
435 -- p_group_id := v_group_id;
436 RETURN v_group_id;
437 EXCEPTION
438 WHEN OTHERS THEN
439 IF c_get_next_seq%ISOPEN THEN
440 CLOSE c_get_next_seq;
441 END IF;
442 app_exception.raise_exception;
443 END;
444 EXCEPTION
445 WHEN OTHERS THEN
446 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
447 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_012.create_person_id_group');
448 IGS_GE_MSG_STACK.ADD;
449 App_Exception.Raise_Exception;
450 END create_person_id_group;
451
452 PROCEDURE create_person_id_group_member (
453 p_group_id igs_pe_prsid_grp_mem.group_id%TYPE,
454 p_person_id igs_pe_prsid_grp_mem.person_id%TYPE) IS
455 /****************************************************************************
456 Created By :
457 Date Created On :
458 Purpose :
459
460 Change History
461 Who When What
462 (reverse chronological order - newest change first)
463 *****************************************************************************/
464 BEGIN
465 DECLARE
466 v_user_id igs_pe_person.person_id%TYPE;
467 v_group_id igs_pe_persid_group.group_id%TYPE;
468 lv_rowid VARCHAR2(25);
469 l_org_id NUMBER(15);
470 CURSOR c_pigm (
471 cp_group_id igs_pe_persid_group.group_id%TYPE,
472 cp_person_id igs_pe_person.person_id%TYPE) IS
473 SELECT 'X'
474 FROM igs_pe_prsid_grp_mem pigm
475 WHERE pigm.group_id = cp_group_id AND
476 pigm.person_id = cp_person_id AND
477 NVL(TRUNC(start_date),TRUNC(SYSDATE)) <= TRUNC(SYSDATE) AND
478 NVL(TRUNC(end_date),TRUNC(SYSDATE)) >= TRUNC(SYSDATE);
479 BEGIN -- create_person_id_group_member
480 OPEN c_pigm(
481 p_group_id,
482 p_person_id);
483 FETCH c_pigm INTO v_dummy;
484 IF c_pigm%NOTFOUND THEN -- IGS_PE_PERSON not already in group
485 CLOSE c_pigm;
486 l_org_id := igs_ge_gen_003.get_org_id;
487 IGS_PE_PRSID_GRP_MEM_Pkg.Insert_Row (
488 X_Mode => 'R',
489 X_RowId => lv_rowid,
490 X_Group_Id => p_group_id,
491 X_Person_Id => p_person_id,
492 X_START_DATE => TRUNC(SYSDATE),
493 X_END_DATE => null,
494 X_org_Id => l_org_id
495 );
496
497 ELSE -- person already in group
498 CLOSE c_pigm;
499 END IF;
500 EXCEPTION
501 WHEN OTHERS THEN
502 IF c_pigm%ISOPEN THEN
503 CLOSE c_pigm;
504 END IF;
505 APP_EXCEPTION.RAISE_EXCEPTION;
506 END;
507 EXCEPTION
508 WHEN OTHERS THEN
509 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
510 Fnd_Message.Set_Token('NAME','IGS_AD_GEN_012.create_person_id_group_member');
511 IGS_GE_MSG_STACK.ADD;
512 App_Exception.Raise_Exception;
513 END create_person_id_group_member;
514 BEGIN
515 -- 1. Set default values for later processing
516 -- Get default status values for system enquiry statuses 'OSS_COMPLETE' and
517 -- 'OSS_ACKNOWLEGE'. There may be more than one status mapped to the system
518 -- status, so select the first found in each case.
519 OPEN c_es(cst_complete);
520 FETCH c_es INTO v_complete_status;
521 CLOSE c_es;
522
523 OPEN c_es(cst_acknowlege);
524 FETCH c_es INTO v_acknowledge_status;
525 CLOSE c_es;
526
527 v_person_id_group_created := 'N';
528 -- Set the process date to sysdate to ensure all records processed have the
529 -- same date placed into the last_process_dt field and the mailed_dt
530 -- fields of the package items. This assists in the subsequent reporting
531 -- process, as well as ensuring some records don't have a different date
532 -- component if the day changes while the process is running.
533 v_process_dt := TRUNC(SYSDATE);
534 -- 2. Firstly process enquiries with a system status of 'OSS_ACKNOWLEGE'
535 -- or 'BOTH'
536
537
538 IF p_process_status IN (cst_acknowlege, cst_both) THEN
539 FOR v_eap_es_rec IN c_eap_es(cst_acknowlege) LOOP
540 v_person_id := v_eap_es_rec.person_id;
541 v_enquiry_appl_number := v_eap_es_rec.enquiry_appl_number;
542 v_process_ind := v_eap_es_rec.override_process_ind;
543
544 IF p_inq_info_type IS NOT NULL THEN
545
546 --c_pkgitems_exist returns the Package Items defined for current Enquiry Instance,
547 --which have Mailed Date as NULL.
548 OPEN c_pkgitems_exist(
549 v_person_id,
550 v_enquiry_appl_number);
551 FETCH c_pkgitems_exist INTO v_dummy;
552
553
554 IF c_pkgitems_exist%NOTFOUND THEN -- No items defined, no action required.
555 CLOSE c_pkgitems_exist;
556 ELSE -- Package Items for the given Inquiry Instance and Information Type Exist.
557 CLOSE c_pkgitems_exist;
558
559
560 -- c_pkgitems_avail returns ROW(S) if there are any UNAVAILABLE package items
561 -- are there which are unsent (Mailed Date NULL) for the current Inquiry Instance
562 OPEN c_pkgitems_avail(
563 v_person_id,
564 v_enquiry_appl_number);
565
566 FETCH c_pkgitems_avail INTO v_dummy;
567
568 -- First, check if atleast one UNSENT package Item is UNAVAILABLE
569 IF c_pkgitems_avail%NOTFOUND THEN --NO package items found which are UNAVAILABLE. This means items are available
570
571 CLOSE c_pkgitems_avail;
572
573 --Update the Mailed Date of all the avaikable package items, to process date
574 update_mailed_dt(
575 v_person_id,
576 v_enquiry_appl_number,
577 v_process_dt);
578
579 IF v_person_id_group_created = 'N' THEN
580 v_group_id := create_person_id_group(p_responsible_user);
581 v_person_id_group_created := 'Y';
582 END IF;
583 create_person_id_group_member(
584 v_group_id,
585 v_person_id);
586
587 --Check if there are any additional package items available for current Inquiry Instance
588 --which are unsent. Update the Inquiry Status accordingly.
589 OPEN c_pkgitems_exist_more( v_person_id,
590 v_enquiry_appl_number);
591 FETCH c_pkgitems_avail INTO v_dummy;
592 IF c_pkgitems_exist_more%FOUND THEN
593 l_s_enq_stat := cst_acknowlege;
594 ELSE
595 l_s_enq_stat := cst_complete;
596 END IF;
597 CLOSE c_pkgitems_exist_more;
598 v_enquiries_processed := v_enquiries_processed + 1;
599 /* Update only the last process date and enquiry status as complete*/
600 igr_inquiry_pkg.update_row (
601 x_rowid => v_eap_es_rec.Row_Id,
602 x_person_id => v_eap_es_rec.Person_Id,
603 x_enquiry_appl_number => v_eap_es_rec.Enquiry_Appl_Number,
604 x_sales_lead_id => v_eap_es_rec.sales_lead_id,
605 x_acad_cal_type => v_eap_es_rec.Acad_Cal_Type,
606 x_acad_ci_sequence_number => v_eap_es_rec.Acad_Ci_Sequence_Number,
607 x_adm_cal_type => v_eap_es_rec.Adm_Cal_Type,
608 x_adm_ci_sequence_number => v_eap_es_rec.Adm_Ci_Sequence_Number,
609 x_enquiry_dt => v_eap_es_rec.Enquiry_Dt,
610 x_registering_person_id => v_eap_es_rec.Registering_Person_Id,
611 x_override_process_ind => v_eap_es_rec.Override_Process_Ind,
612 x_indicated_mailing_dt => v_eap_es_rec.Indicated_Mailing_Dt,
613 x_last_process_dt => v_process_dt,
614 x_comments => v_eap_es_rec.Comments,
615 x_org_id => v_eap_es_rec.org_id,
616 x_inq_entry_level_id => v_eap_es_rec.Inq_Entry_Level_Id,
617 x_edu_goal_id => v_eap_es_rec.Edu_Goal_Id,
618 x_party_id => v_eap_es_rec.Party_Id,
619 x_how_knowus_id => v_eap_es_rec.How_Knowus_Id,
620 x_who_influenced_id => v_eap_es_rec.Who_Influenced_Id,
621 x_attribute_category => v_eap_es_rec.Attribute_Category,
622 x_attribute1 => v_eap_es_rec.Attribute1,
623 x_attribute2 => v_eap_es_rec.Attribute2,
624 x_attribute3 => v_eap_es_rec.Attribute3,
625 x_attribute4 => v_eap_es_rec.Attribute4,
626 x_attribute5 => v_eap_es_rec.Attribute5,
627 x_attribute6 => v_eap_es_rec.Attribute6,
628 x_attribute7 => v_eap_es_rec.Attribute7,
629 x_attribute8 => v_eap_es_rec.Attribute8,
630 x_attribute9 => v_eap_es_rec.Attribute9,
631 x_attribute10 => v_eap_es_rec.Attribute10,
632 x_attribute11 => v_eap_es_rec.Attribute11,
633 x_attribute12 => v_eap_es_rec.Attribute12,
634 x_attribute13 => v_eap_es_rec.Attribute13,
635 x_attribute14 => v_eap_es_rec.Attribute14,
636 x_attribute15 => v_eap_es_rec.Attribute15,
637 x_attribute16 => v_eap_es_rec.Attribute16,
638 x_attribute17 => v_eap_es_rec.Attribute17,
639 x_attribute18 => v_eap_es_rec.Attribute18,
640 x_attribute19 => v_eap_es_rec.Attribute19,
641 x_attribute20 => v_eap_es_rec.Attribute20,
642 x_s_enquiry_status => l_s_enq_stat,
643 x_enabled_flag => v_eap_es_rec.enabled_flag,
644 x_inquiry_method_code => v_eap_es_rec.inquiry_method_code,
645 x_source_promotion_id => v_eap_es_rec.source_promotion_id,
646 x_mode => 'R',
647 x_action => 'Upd',
648 x_ret_status => l_ret_status,
649 x_msg_data => l_msg_data,
650 x_msg_count => l_msg_count,
651 x_pkg_reduct_ind => v_eap_es_rec.pkg_reduct_ind
652 );
653 IF l_ret_status IN ('E','U') THEN
654 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data);
655 ELSE
656 v_enquiries_completed := v_enquiries_completed + 1;
657 END IF;
658
659 ELSE ---c_pkgitems_avail%FOUND. i.e atleast one Package Item is there which is UNAVAILABLE
660 CLOSE c_pkgitems_avail;
661
662 ---c_pkgitems_get_avail fetches the Package Items associated with current
663 -- Inquiry Instance, which are AVAILABLE.
664 OPEN c_pkgitems_get_avail(
665 v_person_id,
666 v_enquiry_appl_number);
667
668 FETCH c_pkgitems_get_avail INTO v_dummy;
669 -- Check if none of the package items are available
670 IF c_pkgitems_get_avail%NOTFOUND THEN -- No items are available
671 CLOSE c_pkgitems_get_avail; -- Do nothing as in IDOPA2
672 ELSE -- Some items are available
673 CLOSE c_pkgitems_get_avail;
674 IF NOT (p_package_incomplete = 'N' AND
675 v_process_ind = 'N') THEN
676
677 update_mailed_dt(
678 v_person_id,
679 v_enquiry_appl_number,
680 v_process_dt);
681
682 IF v_person_id_group_created = 'N' THEN
683 v_group_id := create_person_id_group(p_responsible_user);
684 v_person_id_group_created := 'Y';
685 END IF;
686 create_person_id_group_member(
687 v_group_id,
688 v_person_id);
689 /* Update The Enquiry Status obtained above and last process date of inquiry instance */
690 igr_inquiry_pkg.update_row (
691 x_rowid => v_eap_es_rec.Row_Id,
692 x_person_id => v_eap_es_rec.Person_Id,
693 x_enquiry_appl_number => v_eap_es_rec.Enquiry_Appl_Number,
694 x_sales_lead_id => v_eap_es_rec.sales_lead_id,
695 x_acad_cal_type => v_eap_es_rec.Acad_Cal_Type,
696 x_acad_ci_sequence_number => v_eap_es_rec.Acad_Ci_Sequence_Number,
697 x_adm_cal_type => v_eap_es_rec.Adm_Cal_Type,
698 x_adm_ci_sequence_number => v_eap_es_rec.Adm_Ci_Sequence_Number,
699 x_enquiry_dt => v_eap_es_rec.Enquiry_Dt,
700 x_registering_person_id => v_eap_es_rec.Registering_Person_Id,
701 x_override_process_ind => v_eap_es_rec.Override_Process_Ind,
702 x_indicated_mailing_dt => v_eap_es_rec.Indicated_Mailing_Dt,
703 x_last_process_dt => v_process_dt,
704 x_comments => v_eap_es_rec.Comments,
705 x_org_id => v_eap_es_rec.org_id,
706 x_inq_entry_level_id => v_eap_es_rec.Inq_Entry_Level_Id,
707 x_edu_goal_id => v_eap_es_rec.Edu_Goal_Id,
708 x_party_id => v_eap_es_rec.Party_Id,
709 x_how_knowus_id => v_eap_es_rec.How_Knowus_Id,
710 x_who_influenced_id => v_eap_es_rec.Who_Influenced_Id,
711 x_attribute_category => v_eap_es_rec.Attribute_Category,
712 x_attribute1 => v_eap_es_rec.Attribute1,
713 x_attribute2 => v_eap_es_rec.Attribute2,
714 x_attribute3 => v_eap_es_rec.Attribute3,
715 x_attribute4 => v_eap_es_rec.Attribute4,
716 x_attribute5 => v_eap_es_rec.Attribute5,
717 x_attribute6 => v_eap_es_rec.Attribute6,
718 x_attribute7 => v_eap_es_rec.Attribute7,
719 x_attribute8 => v_eap_es_rec.Attribute8,
720 x_attribute9 => v_eap_es_rec.Attribute9,
721 x_attribute10 => v_eap_es_rec.Attribute10,
722 x_attribute11 => v_eap_es_rec.Attribute11,
723 x_attribute12 => v_eap_es_rec.Attribute12,
724 x_attribute13 => v_eap_es_rec.Attribute13,
725 x_attribute14 => v_eap_es_rec.Attribute14,
726 x_attribute15 => v_eap_es_rec.Attribute15,
727 x_attribute16 => v_eap_es_rec.Attribute16,
728 x_attribute17 => v_eap_es_rec.Attribute17,
729 x_attribute18 => v_eap_es_rec.Attribute18,
730 x_attribute19 => v_eap_es_rec.Attribute19,
731 x_attribute20 => v_eap_es_rec.Attribute20,
732 x_s_enquiry_status => v_eap_es_rec.s_enquiry_status,
733 x_enabled_flag => v_eap_es_rec.enabled_flag,
734 x_inquiry_method_code => v_eap_es_rec.inquiry_method_code,
735 x_source_promotion_id => v_eap_es_rec.source_promotion_id,
736 x_mode => 'R',
737 x_action => 'Upd',
738 x_ret_status => l_ret_status,
739 x_msg_data => l_msg_data,
740 x_msg_count => l_msg_count,
741 x_pkg_reduct_ind => v_eap_es_rec.pkg_reduct_ind
742 );
743 IF l_ret_status IN ('E','U') THEN
744 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data);
745 END IF;
746 END IF;
747 END IF;
748 END IF;
749 END IF;
750
751
752 ELSIF p_inq_info_type IS NULL THEN
753 /** This IF section has Original code -only change :: update if donot_mail_ind= 'N'- see cursor in update_mailed_dt*/
754
755 -- Check if enquiry has any package items defined.
756 -- If not, no processing is required.
757 OPEN c_eapmpi(
758 v_person_id,
759 v_enquiry_appl_number);
760 FETCH c_eapmpi INTO v_dummy;
761
762 IF c_eapmpi%NOTFOUND THEN -- No Package items defined, no action required.
763 CLOSE c_eapmpi;
764 ELSE
765 CLOSE c_eapmpi;
766
767 -- For each record found check the availability of unsent package items
768 -- First, check if ALL required package items are available
769
770 OPEN c_eapmpi_epi(
771 v_person_id,
772 v_enquiry_appl_number);
773
774 FETCH c_eapmpi_epi INTO v_dummy;
775 IF c_eapmpi_epi%NOTFOUND THEN -- No items found which are NOT in the Available Dates range.
776 -- means that All items are available (checks on available From/To dates)
777 CLOSE c_eapmpi_epi;
778 update_mailed_dt(
779 v_person_id,
780 v_enquiry_appl_number,
781 v_process_dt);
782
783 IF v_person_id_group_created = 'N' THEN
784 v_group_id := create_person_id_group(p_responsible_user);
785 v_person_id_group_created := 'Y';
786 END IF;
787 create_person_id_group_member(
788 v_group_id,
789 v_person_id);
790 v_enquiries_processed := v_enquiries_processed + 1;
791 /* Update only the last process date and enquiry status as complete*/
792 igr_inquiry_pkg.update_row (
793 x_rowid => v_eap_es_rec.Row_Id,
794 x_person_id => v_eap_es_rec.Person_Id,
795 x_enquiry_appl_number => v_eap_es_rec.Enquiry_Appl_Number,
796 x_sales_lead_id => v_eap_es_rec.sales_lead_id,
797 x_acad_cal_type => v_eap_es_rec.Acad_Cal_Type,
798 x_acad_ci_sequence_number => v_eap_es_rec.Acad_Ci_Sequence_Number,
799 x_adm_cal_type => v_eap_es_rec.Adm_Cal_Type,
800 x_adm_ci_sequence_number => v_eap_es_rec.Adm_Ci_Sequence_Number,
801 x_enquiry_dt => v_eap_es_rec.Enquiry_Dt,
802 x_registering_person_id => v_eap_es_rec.Registering_Person_Id,
803 x_override_process_ind => v_eap_es_rec.Override_Process_Ind,
804 x_indicated_mailing_dt => v_eap_es_rec.Indicated_Mailing_Dt,
805 x_last_process_dt => v_process_dt,
806 x_comments => v_eap_es_rec.Comments,
807 x_org_id => v_eap_es_rec.org_id,
808 x_inq_entry_level_id => v_eap_es_rec.Inq_Entry_Level_Id,
809 x_edu_goal_id => v_eap_es_rec.Edu_Goal_Id,
810 x_party_id => v_eap_es_rec.Party_Id,
811 x_how_knowus_id => v_eap_es_rec.How_Knowus_Id,
812 x_who_influenced_id => v_eap_es_rec.Who_Influenced_Id,
813 x_attribute_category => v_eap_es_rec.Attribute_Category,
814 x_attribute1 => v_eap_es_rec.Attribute1,
815 x_attribute2 => v_eap_es_rec.Attribute2,
816 x_attribute3 => v_eap_es_rec.Attribute3,
817 x_attribute4 => v_eap_es_rec.Attribute4,
818 x_attribute5 => v_eap_es_rec.Attribute5,
819 x_attribute6 => v_eap_es_rec.Attribute6,
820 x_attribute7 => v_eap_es_rec.Attribute7,
821 x_attribute8 => v_eap_es_rec.Attribute8,
822 x_attribute9 => v_eap_es_rec.Attribute9,
823 x_attribute10 => v_eap_es_rec.Attribute10,
824 x_attribute11 => v_eap_es_rec.Attribute11,
825 x_attribute12 => v_eap_es_rec.Attribute12,
826 x_attribute13 => v_eap_es_rec.Attribute13,
827 x_attribute14 => v_eap_es_rec.Attribute14,
828 x_attribute15 => v_eap_es_rec.Attribute15,
829 x_attribute16 => v_eap_es_rec.Attribute16,
830 x_attribute17 => v_eap_es_rec.Attribute17,
831 x_attribute18 => v_eap_es_rec.Attribute18,
832 x_attribute19 => v_eap_es_rec.Attribute19,
833 x_attribute20 => v_eap_es_rec.Attribute20,
834 x_s_enquiry_status => cst_complete,
835 x_enabled_flag => v_eap_es_rec.enabled_flag,
836 x_inquiry_method_code => v_eap_es_rec.inquiry_method_code,
837 x_source_promotion_id => v_eap_es_rec.source_promotion_id,
838 x_mode => 'R',
839 x_action => 'Upd',
840 x_ret_status => l_ret_status,
841 x_msg_data => l_msg_data,
842 x_msg_count => l_msg_count,
843 x_pkg_reduct_ind => v_eap_es_rec.pkg_reduct_ind
844 );
845 IF l_ret_status IN ('E','U') THEN
846 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data);
847 ELSE
848 v_enquiries_completed := v_enquiries_completed + 1;
849 END IF;
850 ELSE --c_eapmpi_epi%FOUND
851 CLOSE c_eapmpi_epi;
852
853 -- c_eapmpi_get_epi returns Package Items defined for current Inquiry Instance which are AVAILABLE.
854 OPEN c_eapmpi_get_epi(
855 v_person_id,
856 v_enquiry_appl_number);
857
858 FETCH c_eapmpi_get_epi INTO v_dummy;
859
860 -- Check if none of the package items are available
861 IF c_eapmpi_get_epi%NOTFOUND THEN -- No items are available
862 CLOSE c_eapmpi_get_epi; -- Do nothing as in IDOPA2 (also before)
863
864 ELSE -- Some items are available
865
866 CLOSE c_eapmpi_get_epi;
867 IF NOT (p_package_incomplete = 'N' AND
868 v_process_ind = 'N') THEN
869
870 update_mailed_dt(
871 v_person_id,
872 v_enquiry_appl_number,
873 v_process_dt);
874
875 IF v_person_id_group_created = 'N' THEN
876 v_group_id := create_person_id_group(p_responsible_user);
877 v_person_id_group_created := 'Y';
878 END IF;
879 create_person_id_group_member(
880 v_group_id,
881 v_person_id);
882
883 /* Update only the last process date */
884 igr_inquiry_pkg.update_row (
885 x_rowid => v_eap_es_rec.Row_Id,
886 x_person_id => v_eap_es_rec.Person_Id,
887 x_enquiry_appl_number => v_eap_es_rec.Enquiry_Appl_Number,
888 x_sales_lead_id => v_eap_es_rec.sales_lead_id,
889 x_acad_cal_type => v_eap_es_rec.Acad_Cal_Type,
890 x_acad_ci_sequence_number => v_eap_es_rec.Acad_Ci_Sequence_Number,
891 x_adm_cal_type => v_eap_es_rec.Adm_Cal_Type,
892 x_adm_ci_sequence_number => v_eap_es_rec.Adm_Ci_Sequence_Number,
893 x_enquiry_dt => v_eap_es_rec.Enquiry_Dt,
894 x_registering_person_id => v_eap_es_rec.Registering_Person_Id,
895 x_override_process_ind => v_eap_es_rec.Override_Process_Ind,
896 x_indicated_mailing_dt => v_eap_es_rec.Indicated_Mailing_Dt,
897 x_last_process_dt => v_process_dt,
898 x_comments => v_eap_es_rec.Comments,
899 x_org_id => v_eap_es_rec.org_id,
900 x_inq_entry_level_id => v_eap_es_rec.Inq_Entry_Level_Id,
901 x_edu_goal_id => v_eap_es_rec.Edu_Goal_Id,
902 x_party_id => v_eap_es_rec.Party_Id,
903 x_how_knowus_id => v_eap_es_rec.How_Knowus_Id,
904 x_who_influenced_id => v_eap_es_rec.Who_Influenced_Id,
905 x_attribute_category => v_eap_es_rec.Attribute_Category,
906 x_attribute1 => v_eap_es_rec.Attribute1,
907 x_attribute2 => v_eap_es_rec.Attribute2,
908 x_attribute3 => v_eap_es_rec.Attribute3,
909 x_attribute4 => v_eap_es_rec.Attribute4,
910 x_attribute5 => v_eap_es_rec.Attribute5,
911 x_attribute6 => v_eap_es_rec.Attribute6,
912 x_attribute7 => v_eap_es_rec.Attribute7,
913 x_attribute8 => v_eap_es_rec.Attribute8,
914 x_attribute9 => v_eap_es_rec.Attribute9,
915 x_attribute10 => v_eap_es_rec.Attribute10,
916 x_attribute11 => v_eap_es_rec.Attribute11,
917 x_attribute12 => v_eap_es_rec.Attribute12,
918 x_attribute13 => v_eap_es_rec.Attribute13,
919 x_attribute14 => v_eap_es_rec.Attribute14,
920 x_attribute15 => v_eap_es_rec.Attribute15,
921 x_attribute16 => v_eap_es_rec.Attribute16,
922 x_attribute17 => v_eap_es_rec.Attribute17,
923 x_attribute18 => v_eap_es_rec.Attribute18,
924 x_attribute19 => v_eap_es_rec.Attribute19,
925 x_attribute20 => v_eap_es_rec.Attribute20,
926 x_s_enquiry_status => v_eap_es_rec.s_enquiry_status,
927 x_enabled_flag => v_eap_es_rec.enabled_flag,
928 x_inquiry_method_code => v_eap_es_rec.inquiry_method_code,
929 x_source_promotion_id => v_eap_es_rec.source_promotion_id,
930 x_mode => 'R',
931 x_action => 'Upd',
932 x_ret_status => l_ret_status,
933 x_msg_data => l_msg_data,
934 x_msg_count => l_msg_count,
935 x_pkg_reduct_ind => v_eap_es_rec.pkg_reduct_ind
936 );
937 IF l_ret_status IN ('E','U') THEN
938 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data);
939 END IF;
940 END IF;
941 END IF;
942 END IF;
943 END IF;
944
945 END IF; -- p_inq_info_type IS NULL / NOT NULL --Newly added for IDOPA2
946 END LOOP; -- For loopin in c_eap_es
947 END IF; -- For Acknowledge or both
948
949
950 -- 3. Now, process enquiries with a system status of 'REGISTERED'
951 -- or 'BOTH'
952 -- 3. Now, process enquiries with a system status of 'REGISTERED'
953 -- or 'BOTH'
954
955
956 IF p_process_status IN (cst_registered,cst_both) THEN --(1)
957
958 FOR v_eap_es_rec IN c_eap_es(cst_registered) LOOP --Loop 1
959 v_person_id := v_eap_es_rec.person_id;
960 v_enquiry_appl_number := v_eap_es_rec.enquiry_appl_number;
961 v_process_ind := v_eap_es_rec.override_process_ind;
962
963 IF p_inq_info_type IS NOT NULL THEN /** All new code as part of IDOOPA2 */ --(2)
964 -- Check if enquiry has any package items defined. If not, no
965 -- processing is required.
966 OPEN c_pkgitems_exist(
967 v_person_id,
968 v_enquiry_appl_number);
969 FETCH c_pkgitems_exist INTO v_dummy;
970 IF c_pkgitems_exist%NOTFOUND THEN -- No items defined, no action required. --(3)
971 CLOSE c_pkgitems_exist;
972 ELSE -- Package Items defined for the Inquiry Instance
973 CLOSE c_pkgitems_exist;
974 -- For each record found check the availability of unsent package items
975 -- First, check if ALL required package items are available
976
977 OPEN c_pkgitems_avail(
978 v_person_id,
979 v_enquiry_appl_number);
980 FETCH c_pkgitems_avail INTO v_dummy;
981
982 IF c_pkgitems_avail%NOTFOUND THEN -- All items are available --(4)
983 CLOSE c_pkgitems_avail;
984 update_mailed_dt(
985 v_person_id,
986 v_enquiry_appl_number,
987 v_process_dt);
988
989 IF v_person_id_group_created = 'N' THEN --(5)
990
991 v_group_id := create_person_id_group(p_responsible_user);
992 v_person_id_group_created := 'Y';
993 END IF; --(5)
994
995
996 create_person_id_group_member(
997 v_group_id,
998 v_person_id);
999 OPEN c_pkgitems_exist_more( v_person_id,
1000 v_enquiry_appl_number);
1001 FETCH c_pkgitems_exist_more INTO v_dummy;
1002 IF c_pkgitems_exist_more%FOUND THEN
1003 l_s_enq_stat := cst_acknowlege;
1004 ELSE
1005 l_s_enq_stat := cst_complete;
1006 END IF;
1007 CLOSE c_pkgitems_exist_more;
1008
1009 v_enquiries_processed := v_enquiries_processed + 1;
1010 igr_inquiry_pkg.update_row (
1011 x_rowid => v_eap_es_rec.Row_Id,
1012 x_person_id => v_eap_es_rec.Person_Id,
1013 x_enquiry_appl_number => v_eap_es_rec.Enquiry_Appl_Number,
1014 x_sales_lead_id => v_eap_es_rec.sales_lead_id,
1015 x_acad_cal_type => v_eap_es_rec.Acad_Cal_Type,
1016 x_acad_ci_sequence_number => v_eap_es_rec.Acad_Ci_Sequence_Number,
1017 x_adm_cal_type => v_eap_es_rec.Adm_Cal_Type,
1018 x_adm_ci_sequence_number => v_eap_es_rec.Adm_Ci_Sequence_Number,
1019 x_enquiry_dt => v_eap_es_rec.Enquiry_Dt,
1020 x_registering_person_id => v_eap_es_rec.Registering_Person_Id,
1021 x_override_process_ind => v_eap_es_rec.Override_Process_Ind,
1022 x_indicated_mailing_dt => v_eap_es_rec.Indicated_Mailing_Dt,
1023 x_last_process_dt => v_process_dt,
1024 x_comments => v_eap_es_rec.Comments,
1025 x_org_id => v_eap_es_rec.org_id,
1026 x_inq_entry_level_id => v_eap_es_rec.Inq_Entry_Level_Id,
1027 x_edu_goal_id => v_eap_es_rec.Edu_Goal_Id,
1028 x_party_id => v_eap_es_rec.Party_Id,
1029 x_how_knowus_id => v_eap_es_rec.How_Knowus_Id,
1030 x_who_influenced_id => v_eap_es_rec.Who_Influenced_Id,
1031 x_attribute_category => v_eap_es_rec.Attribute_Category,
1032 x_attribute1 => v_eap_es_rec.Attribute1,
1033 x_attribute2 => v_eap_es_rec.Attribute2,
1034 x_attribute3 => v_eap_es_rec.Attribute3,
1035 x_attribute4 => v_eap_es_rec.Attribute4,
1036 x_attribute5 => v_eap_es_rec.Attribute5,
1037 x_attribute6 => v_eap_es_rec.Attribute6,
1038 x_attribute7 => v_eap_es_rec.Attribute7,
1039 x_attribute8 => v_eap_es_rec.Attribute8,
1040 x_attribute9 => v_eap_es_rec.Attribute9,
1041 x_attribute10 => v_eap_es_rec.Attribute10,
1042 x_attribute11 => v_eap_es_rec.Attribute11,
1043 x_attribute12 => v_eap_es_rec.Attribute12,
1044 x_attribute13 => v_eap_es_rec.Attribute13,
1045 x_attribute14 => v_eap_es_rec.Attribute14,
1046 x_attribute15 => v_eap_es_rec.Attribute15,
1047 x_attribute16 => v_eap_es_rec.Attribute16,
1048 x_attribute17 => v_eap_es_rec.Attribute17,
1049 x_attribute18 => v_eap_es_rec.Attribute18,
1050 x_attribute19 => v_eap_es_rec.Attribute19,
1051 x_attribute20 => v_eap_es_rec.Attribute20,
1052 x_s_enquiry_status => l_s_enq_stat,
1053 x_enabled_flag => v_eap_es_rec.enabled_flag,
1054 x_inquiry_method_code => v_eap_es_rec.inquiry_method_code,
1055 x_source_promotion_id => v_eap_es_rec.source_promotion_id,
1056 x_mode => 'R',
1057 x_action => 'Upd',
1058 x_ret_status => l_ret_status,
1059 x_msg_data => l_msg_data,
1060 x_msg_count => l_msg_count,
1061 x_pkg_reduct_ind => v_eap_es_rec.pkg_reduct_ind
1062 );
1063
1064 IF l_ret_status IN ('E','U') THEN
1065 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data);
1066 ELSE
1067 IF l_s_enq_stat = cst_complete THEN
1068 v_enquiries_completed := v_enquiries_completed + 1;
1069 ELSIF l_s_enq_stat = cst_acknowlege THEN
1070 v_enquiries_acknowledged := v_enquiries_acknowledged +1;
1071 END IF;
1072 END IF;
1073
1074 ELSE --(4) All items available y/n (At least one Package Item is UNAVAILABLE.
1075 CLOSE c_pkgitems_avail;
1076 -- Check if none of the package items are available
1077 OPEN c_pkgitems_get_avail(
1078 v_person_id,
1079 v_enquiry_appl_number);
1080
1081 FETCH c_pkgitems_get_avail INTO v_dummy;
1082 IF c_pkgitems_get_avail%NOTFOUND THEN -- No items are available
1083 CLOSE c_pkgitems_get_avail;
1084 IF p_package_unavailable = 'Y' THEN -- Process anyway
1085 IF v_person_id_group_created = 'N' THEN
1086 v_group_id := create_person_id_group(p_responsible_user);
1087 v_person_id_group_created := 'Y';
1088 END IF;
1089 create_person_id_group_member(
1090 v_group_id,
1091 v_person_id);
1092 --Update Process Date and Inquiry Status to 'Acknowledged'.
1093 v_enquiries_processed := v_enquiries_processed + 1;
1094 igr_inquiry_pkg.update_row (
1095 x_rowid => v_eap_es_rec.Row_Id,
1096 x_person_id => v_eap_es_rec.Person_Id,
1097 x_enquiry_appl_number => v_eap_es_rec.Enquiry_Appl_Number,
1098 x_sales_lead_id => v_eap_es_rec.sales_lead_id,
1099 x_acad_cal_type => v_eap_es_rec.Acad_Cal_Type,
1100 x_acad_ci_sequence_number => v_eap_es_rec.Acad_Ci_Sequence_Number,
1101 x_adm_cal_type => v_eap_es_rec.Adm_Cal_Type,
1102 x_adm_ci_sequence_number => v_eap_es_rec.Adm_Ci_Sequence_Number,
1103 x_enquiry_dt => v_eap_es_rec.Enquiry_Dt,
1104 x_registering_person_id => v_eap_es_rec.Registering_Person_Id,
1105 x_override_process_ind => v_eap_es_rec.Override_Process_Ind,
1106 x_indicated_mailing_dt => v_eap_es_rec.Indicated_Mailing_Dt,
1107 x_last_process_dt => v_process_dt,
1108 x_comments => v_eap_es_rec.Comments,
1109 x_org_id => v_eap_es_rec.org_id,
1110 x_inq_entry_level_id => v_eap_es_rec.Inq_Entry_Level_Id,
1111 x_edu_goal_id => v_eap_es_rec.Edu_Goal_Id,
1112 x_party_id => v_eap_es_rec.Party_Id,
1113 x_how_knowus_id => v_eap_es_rec.How_Knowus_Id,
1114 x_who_influenced_id => v_eap_es_rec.Who_Influenced_Id,
1115 x_attribute_category => v_eap_es_rec.Attribute_Category,
1116 x_attribute1 => v_eap_es_rec.Attribute1,
1117 x_attribute2 => v_eap_es_rec.Attribute2,
1118 x_attribute3 => v_eap_es_rec.Attribute3,
1119 x_attribute4 => v_eap_es_rec.Attribute4,
1120 x_attribute5 => v_eap_es_rec.Attribute5,
1121 x_attribute6 => v_eap_es_rec.Attribute6,
1122 x_attribute7 => v_eap_es_rec.Attribute7,
1123 x_attribute8 => v_eap_es_rec.Attribute8,
1124 x_attribute9 => v_eap_es_rec.Attribute9,
1125 x_attribute10 => v_eap_es_rec.Attribute10,
1126 x_attribute11 => v_eap_es_rec.Attribute11,
1127 x_attribute12 => v_eap_es_rec.Attribute12,
1128 x_attribute13 => v_eap_es_rec.Attribute13,
1129 x_attribute14 => v_eap_es_rec.Attribute14,
1130 x_attribute15 => v_eap_es_rec.Attribute15,
1131 x_attribute16 => v_eap_es_rec.Attribute16,
1132 x_attribute17 => v_eap_es_rec.Attribute17,
1133 x_attribute18 => v_eap_es_rec.Attribute18,
1134 x_attribute19 => v_eap_es_rec.Attribute19,
1135 x_attribute20 => v_eap_es_rec.Attribute20,
1136 x_s_enquiry_status => cst_acknowlege,
1137 x_enabled_flag => v_eap_es_rec.enabled_flag,
1138 x_inquiry_method_code => v_eap_es_rec.inquiry_method_code,
1139 x_source_promotion_id => v_eap_es_rec.source_promotion_id,
1140 x_mode => 'R',
1141 x_action => 'Upd',
1142 x_ret_status => l_ret_status,
1143 x_msg_data => l_msg_data,
1144 x_msg_count => l_msg_count,
1145 x_pkg_reduct_ind => v_eap_es_rec.pkg_reduct_ind
1146 );
1147 IF l_ret_status IN ('E','U') THEN
1148 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data);
1149 ELSE
1150 v_enquiries_acknowledged := v_enquiries_acknowledged + 1;
1151 END IF;
1152 END IF;
1153 ELSE --c_pkgitems_get_avail%FOUND THEN
1154 CLOSE c_pkgitems_get_avail;
1155 IF NOT (p_package_incomplete = 'N' AND
1156 v_process_ind = 'N') THEN
1157
1158 update_mailed_dt(
1159 v_person_id,
1160 v_enquiry_appl_number,
1161 v_process_dt);
1162
1163 IF v_person_id_group_created = 'N' THEN
1164 v_group_id := create_person_id_group(p_responsible_user);
1165 v_person_id_group_created := 'Y';
1166 END IF;
1167
1168 create_person_id_group_member(
1169 v_group_id,
1170 v_person_id);
1171
1172 v_enquiries_processed := v_enquiries_processed + 1;
1173 -- Update the Last Process Date to current date and Inquiry Status to Acknowledged.
1174 igr_inquiry_pkg.update_row (
1175 x_rowid => v_eap_es_rec.Row_Id,
1176 x_person_id => v_eap_es_rec.Person_Id,
1177 x_enquiry_appl_number => v_eap_es_rec.Enquiry_Appl_Number,
1178 x_sales_lead_id => v_eap_es_rec.sales_lead_id,
1179 x_acad_cal_type => v_eap_es_rec.Acad_Cal_Type,
1180 x_acad_ci_sequence_number => v_eap_es_rec.Acad_Ci_Sequence_Number,
1181 x_adm_cal_type => v_eap_es_rec.Adm_Cal_Type,
1182 x_adm_ci_sequence_number => v_eap_es_rec.Adm_Ci_Sequence_Number,
1183 x_enquiry_dt => v_eap_es_rec.Enquiry_Dt,
1184 x_registering_person_id => v_eap_es_rec.Registering_Person_Id,
1185 x_override_process_ind => v_eap_es_rec.Override_Process_Ind,
1186 x_indicated_mailing_dt => v_eap_es_rec.Indicated_Mailing_Dt,
1187 x_last_process_dt => v_process_dt,
1188 x_comments => v_eap_es_rec.Comments,
1189 x_org_id => v_eap_es_rec.org_id,
1190 x_inq_entry_level_id => v_eap_es_rec.Inq_Entry_Level_Id,
1191 x_edu_goal_id => v_eap_es_rec.Edu_Goal_Id,
1192 x_party_id => v_eap_es_rec.Party_Id,
1193 x_how_knowus_id => v_eap_es_rec.How_Knowus_Id,
1194 x_who_influenced_id => v_eap_es_rec.Who_Influenced_Id,
1195 x_attribute_category => v_eap_es_rec.Attribute_Category,
1196 x_attribute1 => v_eap_es_rec.Attribute1,
1197 x_attribute2 => v_eap_es_rec.Attribute2,
1198 x_attribute3 => v_eap_es_rec.Attribute3,
1199 x_attribute4 => v_eap_es_rec.Attribute4,
1200 x_attribute5 => v_eap_es_rec.Attribute5,
1201 x_attribute6 => v_eap_es_rec.Attribute6,
1202 x_attribute7 => v_eap_es_rec.Attribute7,
1203 x_attribute8 => v_eap_es_rec.Attribute8,
1204 x_attribute9 => v_eap_es_rec.Attribute9,
1205 x_attribute10 => v_eap_es_rec.Attribute10,
1206 x_attribute11 => v_eap_es_rec.Attribute11,
1207 x_attribute12 => v_eap_es_rec.Attribute12,
1208 x_attribute13 => v_eap_es_rec.Attribute13,
1209 x_attribute14 => v_eap_es_rec.Attribute14,
1210 x_attribute15 => v_eap_es_rec.Attribute15,
1211 x_attribute16 => v_eap_es_rec.Attribute16,
1212 x_attribute17 => v_eap_es_rec.Attribute17,
1213 x_attribute18 => v_eap_es_rec.Attribute18,
1214 x_attribute19 => v_eap_es_rec.Attribute19,
1215 x_attribute20 => v_eap_es_rec.Attribute20,
1216 x_s_enquiry_status => cst_acknowlege,
1217 x_enabled_flag => v_eap_es_rec.enabled_flag,
1218 x_inquiry_method_code => v_eap_es_rec.inquiry_method_code,
1219 x_source_promotion_id => v_eap_es_rec.source_promotion_id,
1220 x_mode => 'R',
1221 x_action => 'Upd',
1222 x_ret_status => l_ret_status,
1223 x_msg_data => l_msg_data,
1224 x_msg_count => l_msg_count,
1225 x_pkg_reduct_ind => v_eap_es_rec.pkg_reduct_ind
1226 );
1227 IF l_ret_status IN ('E','U') THEN
1228 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data);
1229 ELSE
1230 v_enquiries_acknowledged := v_enquiries_acknowledged + 1;
1231 END IF;
1232 END IF; --Package incomplete
1233 END IF; --No or some items
1234 END IF; --(4) All items available y/n
1235 END IF; --(3) -- For package items exists or not
1236
1237
1238 ELSIF p_inq_info_type IS NULL THEN /** Original code */
1239
1240 -- Check if enquiry has any package items defined. If not, no
1241 -- processing is required.
1242 OPEN c_eapmpi(
1243 v_person_id,
1244 v_enquiry_appl_number);
1245 FETCH c_eapmpi INTO v_dummy;
1246 IF c_eapmpi%NOTFOUND THEN -- No items defined, no action required.
1247 CLOSE c_eapmpi;
1248 ELSE -- Package Items are defined for enquiry instance
1249
1250 CLOSE c_eapmpi;
1251 -- For each record found check the availability of unsent package items
1252 -- First, check if ALL required package items are available
1253 OPEN c_eapmpi_epi(
1254 v_person_id,
1255 v_enquiry_appl_number);
1256 FETCH c_eapmpi_epi INTO v_dummy;
1257 IF c_eapmpi_epi%NOTFOUND THEN -- All items are available
1258
1259 CLOSE c_eapmpi_epi;
1260 update_mailed_dt(
1261 v_person_id,
1262 v_enquiry_appl_number,
1263 v_process_dt);
1264 IF v_person_id_group_created = 'N' THEN
1265 v_group_id := create_person_id_group(p_responsible_user);
1266 v_person_id_group_created := 'Y';
1267 END IF;
1268 create_person_id_group_member(
1269 v_group_id,
1270 v_person_id);
1271
1272
1273 v_enquiries_processed := v_enquiries_processed + 1;
1274 /* Update only the last process date and enquiry status as complete*/
1275 igr_inquiry_pkg.update_row (
1276 x_rowid => v_eap_es_rec.Row_Id,
1277 x_person_id => v_eap_es_rec.Person_Id,
1278 x_enquiry_appl_number => v_eap_es_rec.Enquiry_Appl_Number,
1279 x_sales_lead_id => v_eap_es_rec.sales_lead_id,
1280 x_acad_cal_type => v_eap_es_rec.Acad_Cal_Type,
1281 x_acad_ci_sequence_number => v_eap_es_rec.Acad_Ci_Sequence_Number,
1282 x_adm_cal_type => v_eap_es_rec.Adm_Cal_Type,
1283 x_adm_ci_sequence_number => v_eap_es_rec.Adm_Ci_Sequence_Number,
1284 x_enquiry_dt => v_eap_es_rec.Enquiry_Dt,
1285 x_registering_person_id => v_eap_es_rec.Registering_Person_Id,
1286 x_override_process_ind => v_eap_es_rec.Override_Process_Ind,
1287 x_indicated_mailing_dt => v_eap_es_rec.Indicated_Mailing_Dt,
1288 x_last_process_dt => v_process_dt,
1289 x_comments => v_eap_es_rec.Comments,
1290 x_org_id => v_eap_es_rec.org_id,
1291 x_inq_entry_level_id => v_eap_es_rec.Inq_Entry_Level_Id,
1292 x_edu_goal_id => v_eap_es_rec.Edu_Goal_Id,
1293 x_party_id => v_eap_es_rec.Party_Id,
1294 x_how_knowus_id => v_eap_es_rec.How_Knowus_Id,
1295 x_who_influenced_id => v_eap_es_rec.Who_Influenced_Id,
1296 x_attribute_category => v_eap_es_rec.Attribute_Category,
1297 x_attribute1 => v_eap_es_rec.Attribute1,
1298 x_attribute2 => v_eap_es_rec.Attribute2,
1299 x_attribute3 => v_eap_es_rec.Attribute3,
1300 x_attribute4 => v_eap_es_rec.Attribute4,
1301 x_attribute5 => v_eap_es_rec.Attribute5,
1302 x_attribute6 => v_eap_es_rec.Attribute6,
1303 x_attribute7 => v_eap_es_rec.Attribute7,
1304 x_attribute8 => v_eap_es_rec.Attribute8,
1305 x_attribute9 => v_eap_es_rec.Attribute9,
1306 x_attribute10 => v_eap_es_rec.Attribute10,
1307 x_attribute11 => v_eap_es_rec.Attribute11,
1308 x_attribute12 => v_eap_es_rec.Attribute12,
1309 x_attribute13 => v_eap_es_rec.Attribute13,
1310 x_attribute14 => v_eap_es_rec.Attribute14,
1311 x_attribute15 => v_eap_es_rec.Attribute15,
1312 x_attribute16 => v_eap_es_rec.Attribute16,
1313 x_attribute17 => v_eap_es_rec.Attribute17,
1314 x_attribute18 => v_eap_es_rec.Attribute18,
1315 x_attribute19 => v_eap_es_rec.Attribute19,
1316 x_attribute20 => v_eap_es_rec.Attribute20,
1317 x_s_enquiry_status => cst_complete,
1318 x_enabled_flag => v_eap_es_rec.enabled_flag,
1319 x_inquiry_method_code => v_eap_es_rec.inquiry_method_code,
1320 x_source_promotion_id => v_eap_es_rec.source_promotion_id,
1321 x_mode => 'R',
1322 x_action => 'Upd',
1323 x_ret_status => l_ret_status,
1324 x_msg_data => l_msg_data,
1325 x_msg_count => l_msg_count,
1326 x_pkg_reduct_ind => v_eap_es_rec.pkg_reduct_ind
1327 );
1328 IF l_ret_status IN ('E','U') THEN
1329 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data);
1330 ELSE
1331 v_enquiries_completed := v_enquiries_completed + 1;
1332 END IF;
1333
1334 ELSE --c_eapmpi_epi%FOUND
1335
1336 CLOSE c_eapmpi_epi;
1337 -- Check if none of the package items are available
1338 OPEN c_eapmpi_get_epi(
1339 v_person_id,
1340 v_enquiry_appl_number);
1341 FETCH c_eapmpi_get_epi INTO v_dummy;
1342 IF c_eapmpi_get_epi%NOTFOUND THEN -- No items are available
1343
1344 CLOSE c_eapmpi_get_epi;
1345 IF p_package_unavailable = 'Y' THEN -- Process anyway
1346 IF v_person_id_group_created = 'N' THEN
1347 v_group_id := create_person_id_group(p_responsible_user);
1348 v_person_id_group_created := 'Y';
1349 END IF;
1350 create_person_id_group_member(
1351 v_group_id,
1352 v_person_id);
1353
1354 v_enquiries_processed := v_enquiries_processed + 1;
1355 /* Update only the last process date */
1356 igr_inquiry_pkg.update_row (
1357 x_rowid => v_eap_es_rec.Row_Id,
1358 x_person_id => v_eap_es_rec.Person_Id,
1359 x_enquiry_appl_number => v_eap_es_rec.Enquiry_Appl_Number,
1360 x_sales_lead_id => v_eap_es_rec.sales_lead_id,
1361 x_acad_cal_type => v_eap_es_rec.Acad_Cal_Type,
1362 x_acad_ci_sequence_number => v_eap_es_rec.Acad_Ci_Sequence_Number,
1363 x_adm_cal_type => v_eap_es_rec.Adm_Cal_Type,
1364 x_adm_ci_sequence_number => v_eap_es_rec.Adm_Ci_Sequence_Number,
1365 x_enquiry_dt => v_eap_es_rec.Enquiry_Dt,
1366 x_registering_person_id => v_eap_es_rec.Registering_Person_Id,
1367 x_override_process_ind => v_eap_es_rec.Override_Process_Ind,
1368 x_indicated_mailing_dt => v_eap_es_rec.Indicated_Mailing_Dt,
1369 x_last_process_dt => v_process_dt,
1370 x_comments => v_eap_es_rec.Comments,
1371 x_org_id => v_eap_es_rec.org_id,
1372 x_inq_entry_level_id => v_eap_es_rec.Inq_Entry_Level_Id,
1373 x_edu_goal_id => v_eap_es_rec.Edu_Goal_Id,
1374 x_party_id => v_eap_es_rec.Party_Id,
1375 x_how_knowus_id => v_eap_es_rec.How_Knowus_Id,
1376 x_who_influenced_id => v_eap_es_rec.Who_Influenced_Id,
1377 x_attribute_category => v_eap_es_rec.Attribute_Category,
1378 x_attribute1 => v_eap_es_rec.Attribute1,
1379 x_attribute2 => v_eap_es_rec.Attribute2,
1380 x_attribute3 => v_eap_es_rec.Attribute3,
1381 x_attribute4 => v_eap_es_rec.Attribute4,
1382 x_attribute5 => v_eap_es_rec.Attribute5,
1383 x_attribute6 => v_eap_es_rec.Attribute6,
1384 x_attribute7 => v_eap_es_rec.Attribute7,
1385 x_attribute8 => v_eap_es_rec.Attribute8,
1386 x_attribute9 => v_eap_es_rec.Attribute9,
1387 x_attribute10 => v_eap_es_rec.Attribute10,
1388 x_attribute11 => v_eap_es_rec.Attribute11,
1389 x_attribute12 => v_eap_es_rec.Attribute12,
1390 x_attribute13 => v_eap_es_rec.Attribute13,
1391 x_attribute14 => v_eap_es_rec.Attribute14,
1392 x_attribute15 => v_eap_es_rec.Attribute15,
1393 x_attribute16 => v_eap_es_rec.Attribute16,
1394 x_attribute17 => v_eap_es_rec.Attribute17,
1395 x_attribute18 => v_eap_es_rec.Attribute18,
1396 x_attribute19 => v_eap_es_rec.Attribute19,
1397 x_attribute20 => v_eap_es_rec.Attribute20,
1398 x_s_enquiry_status => cst_acknowlege,
1399 x_enabled_flag => v_eap_es_rec.enabled_flag,
1400 x_inquiry_method_code => v_eap_es_rec.inquiry_method_code,
1401 x_source_promotion_id => v_eap_es_rec.source_promotion_id,
1402 x_mode => 'R',
1403 x_action => 'Upd',
1404 x_ret_status => l_ret_status,
1405 x_msg_data => l_msg_data,
1406 x_msg_count => l_msg_count,
1407 x_pkg_reduct_ind => v_eap_es_rec.pkg_reduct_ind
1408 );
1409 IF l_ret_status IN ('E','U') THEN
1410 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data);
1411 ELSE
1412 v_enquiries_acknowledged := v_enquiries_acknowledged + 1;
1413 END IF;
1414 END IF;
1415 ELSE --c_eapmpi_get_epi%FOUND
1416 CLOSE c_eapmpi_get_epi;
1417 IF NOT (p_package_incomplete = 'N' AND
1418 v_process_ind = 'N') THEN
1419 update_mailed_dt(
1420 v_person_id,
1421 v_enquiry_appl_number,
1422 v_process_dt);
1423
1424 IF v_person_id_group_created = 'N' THEN
1425 v_group_id := create_person_id_group(p_responsible_user);
1426 v_person_id_group_created := 'Y';
1427 END IF;
1428
1429 create_person_id_group_member(
1430 v_group_id,
1431 v_person_id);
1432
1433 v_enquiries_processed := v_enquiries_processed + 1;
1434 /* Update only the last process date and enquiry status as acknowlege*/
1435 igr_inquiry_pkg.update_row (
1436 x_rowid => v_eap_es_rec.Row_Id,
1437 x_person_id => v_eap_es_rec.Person_Id,
1438 x_enquiry_appl_number => v_eap_es_rec.Enquiry_Appl_Number,
1439 x_sales_lead_id => v_eap_es_rec.sales_lead_id,
1440 x_acad_cal_type => v_eap_es_rec.Acad_Cal_Type,
1441 x_acad_ci_sequence_number => v_eap_es_rec.Acad_Ci_Sequence_Number,
1442 x_adm_cal_type => v_eap_es_rec.Adm_Cal_Type,
1443 x_adm_ci_sequence_number => v_eap_es_rec.Adm_Ci_Sequence_Number,
1444 x_enquiry_dt => v_eap_es_rec.Enquiry_Dt,
1445 x_registering_person_id => v_eap_es_rec.Registering_Person_Id,
1446 x_override_process_ind => v_eap_es_rec.Override_Process_Ind,
1447 x_indicated_mailing_dt => v_eap_es_rec.Indicated_Mailing_Dt,
1448 x_last_process_dt => v_process_dt,
1449 x_comments => v_eap_es_rec.Comments,
1450 x_org_id => v_eap_es_rec.org_id,
1451 x_inq_entry_level_id => v_eap_es_rec.Inq_Entry_Level_Id,
1452 x_edu_goal_id => v_eap_es_rec.Edu_Goal_Id,
1453 x_party_id => v_eap_es_rec.Party_Id,
1454 x_how_knowus_id => v_eap_es_rec.How_Knowus_Id,
1455 x_who_influenced_id => v_eap_es_rec.Who_Influenced_Id,
1456 x_attribute_category => v_eap_es_rec.Attribute_Category,
1457 x_attribute1 => v_eap_es_rec.Attribute1,
1458 x_attribute2 => v_eap_es_rec.Attribute2,
1459 x_attribute3 => v_eap_es_rec.Attribute3,
1460 x_attribute4 => v_eap_es_rec.Attribute4,
1461 x_attribute5 => v_eap_es_rec.Attribute5,
1462 x_attribute6 => v_eap_es_rec.Attribute6,
1463 x_attribute7 => v_eap_es_rec.Attribute7,
1464 x_attribute8 => v_eap_es_rec.Attribute8,
1465 x_attribute9 => v_eap_es_rec.Attribute9,
1466 x_attribute10 => v_eap_es_rec.Attribute10,
1467 x_attribute11 => v_eap_es_rec.Attribute11,
1468 x_attribute12 => v_eap_es_rec.Attribute12,
1469 x_attribute13 => v_eap_es_rec.Attribute13,
1470 x_attribute14 => v_eap_es_rec.Attribute14,
1471 x_attribute15 => v_eap_es_rec.Attribute15,
1472 x_attribute16 => v_eap_es_rec.Attribute16,
1473 x_attribute17 => v_eap_es_rec.Attribute17,
1474 x_attribute18 => v_eap_es_rec.Attribute18,
1475 x_attribute19 => v_eap_es_rec.Attribute19,
1476 x_attribute20 => v_eap_es_rec.Attribute20,
1477 x_s_enquiry_status => cst_acknowlege,
1478 x_enabled_flag => v_eap_es_rec.enabled_flag,
1479 x_inquiry_method_code => v_eap_es_rec.inquiry_method_code,
1480 x_source_promotion_id => v_eap_es_rec.source_promotion_id,
1481 x_mode => 'R',
1482 x_action => 'Upd',
1483 x_ret_status => l_ret_status,
1484 x_msg_data => l_msg_data,
1485 x_msg_count => l_msg_count,
1486 x_pkg_reduct_ind => v_eap_es_rec.pkg_reduct_ind
1487 );
1488 IF l_ret_status IN ('E','U') THEN
1489 FND_FILE.PUT_LINE(FND_FILE.LOG,l_msg_data);
1490 ELSE
1491 v_enquiries_acknowledged := v_enquiries_acknowledged + 1;
1492 END IF;
1493 END IF;
1494 END IF;
1495 END IF;
1496 END IF;
1497 END IF; -- p_inq_info_type IS NULL / NOT NULL --Newly added for IDOPA2 --(2)
1498 END LOOP; -- For loopin in c_eap_es -Loop 1
1499 END IF;-- For Registered or both --(1)
1500
1501 -- Write summary totals to the job scheduler runlog
1502
1503 OPEN c_get_group_cd(v_group_id);
1504 FETCH c_get_group_cd INTO v_group_cd;
1505 CLOSE c_get_group_cd;
1506
1507 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_GE_TOTAL_REC_PROCESSED')||IGS_GE_NUMBER.TO_CANN(v_enquiries_processed));
1508 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_GE_TOTAL_REC_COMPLETED')||IGS_GE_NUMBER.TO_CANN(v_enquiries_completed));
1509 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGS_GE_TOTAL_REC_ACKNOWLEGED')||IGS_GE_NUMBER.TO_CANN(v_enquiries_acknowledged));
1510 FND_FILE.PUT_LINE(FND_FILE.LOG,FND_MESSAGE.GET_STRING('IGS','IGR_PRSN_ID_GRP_CD')|| v_group_cd);
1511 COMMIT;
1512 RETURN;
1513 EXCEPTION
1514 WHEN e_resource_busy THEN
1515 IF c_eap_es%ISOPEN THEN
1516 CLOSE c_eap_es;
1517 END IF;
1518 IF c_eapmpi_epi%ISOPEN THEN
1519 CLOSE c_eapmpi_epi;
1520 END IF;
1521
1522 IF c_pkgitems_exist%ISOPEN THEN
1523 CLOSE c_pkgitems_exist;
1524 END IF;
1525
1526 IF c_pkgitems_avail%ISOPEN THEN
1527 CLOSE c_pkgitems_avail;
1528 END IF;
1529
1530
1531 FND_MESSAGE.SET_NAME('IGS','IGS_AD_CANPRC_CURUPD_USER');
1532 IGS_GE_MSG_STACK.ADD;
1533 APP_EXCEPTION.RAISE_EXCEPTION;
1534 WHEN OTHERS THEN
1535 IF c_es%ISOPEN THEN
1536 CLOSE c_es;
1537 END IF;
1538 IF c_eap_es%ISOPEN THEN
1539 CLOSE c_eap_es;
1540 END IF;
1541 IF c_eapmpi_epi%ISOPEN THEN
1542 CLOSE c_eapmpi_epi;
1543 END IF;
1544
1545 IF c_pkgitems_exist%ISOPEN THEN
1546 CLOSE c_pkgitems_exist;
1547 END IF;
1548
1549 IF c_pkgitems_avail%ISOPEN THEN
1550 CLOSE c_pkgitems_avail;
1551 END IF;
1552
1553 IF c_eapmpi%ISOPEN THEN
1554 CLOSE c_eapmpi;
1555 END IF;
1556
1557 IF c_eapmpi_get_epi%ISOPEN THEN
1558 CLOSE c_eapmpi_get_epi;
1559 END IF;
1560
1561 IF c_pkgitems_exist_more%ISOPEN THEN
1562 CLOSE c_pkgitems_exist_more;
1563 END IF;
1564
1565 IF c_pkgitems_get_avail%ISOPEN THEN
1566 CLOSE c_pkgitems_get_avail;
1567 END IF;
1568
1569 APP_EXCEPTION.RAISE_EXCEPTION;
1570 END;
1571 EXCEPTION
1572 WHEN OTHERS THEN
1573 retcode := 2;
1574 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION');
1575 IGS_GE_MSG_STACK.CONC_EXCEPTION_HNDL;
1576 END admp_upd_eap_avail;
1577
1578
1579 Function Admp_Del_Eap_Eitpi(
1580 p_person_id IN NUMBER ,
1581 p_enquiry_appl_number IN NUMBER ,
1582 p_enquiry_information_type IN VARCHAR2 ,
1583 p_message_name OUT NOCOPY VARCHAR2 )
1584 RETURN BOOLEAN IS
1585 /****************************************************************************
1586 Created By :
1587 Date Created On :
1588 Purpose :
1589
1590 Change History
1591 Who When What
1592 sjlaport 17-FEB-2005 Moved from IGS_AD_GEN_001 (IGSAD01B.pls)
1593 pkpatel 04-JUN-2001 Erased the parameter p_inquiry_date and added parameters to
1594 select person records as per the date range and according to
1595 Inquiry source type and Inquiry Entry Status
1596 pkpatel 13-JUN-2001 Modified the parameter p_inq_entry_stat_id from Mandatory to Default
1597 nshee 13-JAN-2003 As part of fix for bug 2645948, Inquiry source type is not to be a mandatory parameter.
1598 Changed definition of cursor c_eap_es
1599 (reverse chronological order - newest change first)
1600 *****************************************************************************/
1601
1602
1603 BEGIN -- admp_del_eap_eitpi
1604 -- Description: This routine will delete all IGR_I_A_PKGITM records
1605 -- associated with an IGR_I_A_ITYPE record which is being deleted.
1606 DECLARE
1607 v_message_name VARCHAR2(30);
1608 e_resource_busy_exception EXCEPTION;
1609 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
1610 v_eapc_eltpi VARCHAR2(1);
1611 v_eapc_cepi VARCHAR2(1);
1612 v_eapit_eitpi VARCHAR2(1);
1613 cst_course CONSTANT VARCHAR2(7) := 'COURSE';
1614
1615 CURSOR c_eitpi IS
1616 SELECT akit.deliverable_kit_part_id
1617 FROM AMS_DELIV_KIT_ITEMS akit,
1618 AMS_P_DELIVERABLES_V apd
1619 WHERE apd.deliverable_name = p_enquiry_information_type
1620 AND apd.deliverable_id = akit.deliverable_kit_id;
1621
1622
1623
1624 --Check INFORMATION type packages.(Other than the parameter type)
1625 CURSOR c_eapit_eitpi(
1626 cp_enquiry_package_item_id AMS_DELIV_KIT_ITEMS.deliverable_kit_part_id%TYPE) IS
1627 SELECT 'X' FROM IGR_I_A_ITYPE eapit,
1628 AMS_DELIV_KIT_ITEMS akit,
1629 AMS_P_DELIVERABLES_V apd
1630 WHERE eapit.person_id = p_person_id AND
1631 eapit.enquiry_appl_number = p_enquiry_appl_number AND
1632 apd.deliverable_name<> p_enquiry_information_type AND
1633 apd.deliverable_id = akit.deliverable_kit_id AND
1634 eapit.info_type_id = akit.deliverable_kit_id AND
1635 akit.deliverable_kit_part_id = cp_enquiry_package_item_id;
1636
1637 CURSOR c_eapmpi_del(
1638 cp_package_item_id AMS_DELIV_KIT_ITEMS.deliverable_kit_part_id%TYPE) IS
1639 SELECT ROWID, eapmpi.*
1640 FROM IGR_I_A_PKGITM eapmpi
1641 WHERE eapmpi.person_id = p_person_id AND
1642 eapmpi.enquiry_appl_number = p_enquiry_appl_number AND
1643 eapmpi.package_item_id = cp_package_item_id
1644 FOR UPDATE OF eapmpi.person_id NOWAIT;
1645 v_eapmpi_del c_eapmpi_del%ROWTYPE;
1646
1647 BEGIN
1648 p_message_name := null;
1649 IF p_person_id IS NULL OR
1650 p_enquiry_appl_number IS NULL OR
1651 p_enquiry_information_type IS NULL THEN
1652 RETURN TRUE;
1653 END IF;
1654 -- 2.Check that the enquiry has not been completed. If so,
1655 --the delete is not permitted
1656 IF IGR_VAL_EAP.admp_val_eap_comp (p_person_id,
1657 p_enquiry_appl_number,
1658 p_message_name) = FALSE THEN
1659 RETURN FALSE;
1660 END IF;
1661 -- 3.Issue a save point for the module so that if locks exist,
1662 -- a rollback can be performed.
1663 SAVEPOINT sp_save_point;
1664 -- 4.Loop through to select all package items defined for the nominated
1665 -- enquiry information
1666 FOR v_eitpi_rec IN c_eitpi LOOP
1667 --Check INFORMATION type packages.(Other than the parameter type)
1668 OPEN c_eapit_eitpi(
1669 v_eitpi_rec.deliverable_kit_part_id);
1670 FETCH c_eapit_eitpi INTO v_eapit_eitpi;
1671 IF (c_eapit_eitpi%FOUND) THEN
1672 CLOSE c_eapit_eitpi;
1673 EXIT;
1674 END IF;
1675 CLOSE c_eapit_eitpi;
1676 BEGIN
1677 --No matches found so record can be deleted.
1678 FOR v_eapmpi_del IN c_eapmpi_del(
1679 v_eitpi_rec.deliverable_kit_part_id) LOOP
1680 IGR_I_A_PKGITM_PKG.DELETE_ROW (
1681 X_ROWID => V_EAPMPI_DEL.ROWID );
1682 END LOOP;
1683 EXCEPTION
1684 WHEN e_resource_busy_exception THEN
1685 ROLLBACK TO sp_save_point;
1686 p_message_name := 'IGS_AD_NOTDEL_ENQUIRYPACKAGE';
1687 RETURN FALSE;
1688 WHEN OTHERS THEN
1689 App_Exception.Raise_Exception;
1690 END;
1691 END LOOP;
1692
1693 RETURN TRUE;
1694 EXCEPTION
1695 WHEN OTHERS THEN
1696 IF c_eitpi%ISOPEN THEN
1697 CLOSE c_eitpi;
1698 END IF;
1699 IF c_eapit_eitpi%ISOPEN THEN
1700 CLOSE c_eapit_eitpi;
1701 END IF;
1702 IF c_eapmpi_del%ISOPEN THEN
1703 CLOSE c_eapmpi_del;
1704 END IF;
1705 App_Exception.Raise_Exception;
1706 END;
1707 EXCEPTION
1708 WHEN OTHERS THEN
1709 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1710 Fnd_Message.Set_Token('NAME','IGR_GEN_001.admp_del_eap_eitpi');
1711 IGS_GE_MSG_STACK.ADD;
1712 App_Exception.Raise_Exception;
1713 END admp_del_eap_eitpi;
1714
1715 Function Admp_Ins_Eap_Eitpi(
1716 p_person_id IN NUMBER ,
1717 p_enquiry_appl_number IN NUMBER ,
1718 p_enquiry_information_type IN VARCHAR2 ,
1719 p_message_name OUT NOCOPY VARCHAR2 )
1720 RETURN BOOLEAN IS
1721 gv_other_detail VARCHAR2(255);
1722 lv_return_status VARCHAR2(10);
1723 lv_msg_data VARCHAR2(2000);
1724 lv_msg_count NUMBER;
1725 l_action VARCHAR2(10);
1726 BEGIN -- admp_ins_eap_eitpi
1727 -- Insert records into the IGR_I_A_PKGITMtable for a nominated
1728 -- IGS_IN_ENQ_INFO_TYPE.
1729 DECLARE
1730 v_message_name varchar2(30);
1731 v_dummy VARCHAR2(1);
1732 --(This cursor is changed as a part of the SQL tuning bug 4991561)
1733 CURSOR c_eitpi IS
1734 SELECT akit.deliverable_kit_part_id
1735 FROM AMS_P_DELIVERABLES_V apd,
1736 AMS_P_DELIVERABLES_V apd1,
1737 AMS_DELIV_KIT_ITEMS akit
1738 WHERE apd.deliverable_id = akit.deliverable_kit_part_id
1739 AND apd.actual_avail_from_date <= SYSDATE
1740 AND apd.actual_avail_to_date >= SYSDATE
1741 AND akit.deliverable_kit_id = apd1.deliverable_id
1742 AND apd1.deliverable_name = p_enquiry_information_type;
1743
1744
1745 CURSOR c_eapmpi_exists (
1746 cp_package_item_id
1747 IGR_I_A_PKGITM.package_item_id%TYPE) IS
1748 SELECT 'x'
1749 FROM IGR_I_A_PKGITM eapmpi
1750 WHERE eapmpi.person_id = p_person_id AND
1751 eapmpi.enquiry_appl_number = p_enquiry_appl_number AND
1752 eapmpi.package_item_id = cp_package_item_id;
1753 lv_rowid VARCHAR2(25);
1754 BEGIN
1755 -- set default value
1756 p_message_name := null;
1757 l_action := 'Add';
1758 -- 1. Validate parameters.
1759 IF p_person_id IS NULL OR
1760 p_enquiry_appl_number IS NULL OR
1761 p_enquiry_information_type IS NULL THEN
1762 RETURN TRUE;
1763 END IF;
1764 -- 2. Check that the enquiry has not been completed.
1765 -- If so, the insert is not permitted.
1766 IF IGR_VAL_EAP.admp_val_eap_comp ( p_person_id,
1767 p_enquiry_appl_number,
1768 v_message_name) = FALSE THEN
1769 p_message_name := v_message_name;
1770 RETURN FALSE;
1771 END IF;
1772 -- 3. Use a loop to select all package items defined for the nominated
1773 -- enquiry information type. Join to the IGS_IN_ENQ_PKG_ITEM table to
1774 -- ensure that the package item is not closed.
1775 FOR v_eitpi_rec IN c_eitpi LOOP
1776 -- Check that the package item is not already defined in the
1777 -- IGR_I_A_PKGITMtable.
1778 OPEN c_eapmpi_exists (
1779 v_eitpi_rec.deliverable_kit_part_id);
1780 FETCH c_eapmpi_exists INTO v_dummy;
1781 IF c_eapmpi_exists%NOTFOUND THEN
1782 CLOSE c_eapmpi_exists;
1783 IGR_I_A_PKGITM_Pkg.Insert_Row (
1784 X_Mode => 'R',
1785 X_RowId => lv_rowid,
1786 X_Person_Id => p_person_id,
1787 X_Enquiry_Appl_Number => p_enquiry_appl_number,
1788 X_Package_Item_id => v_eitpi_rec.deliverable_kit_part_id,
1789 X_Mailed_Dt => Null ,
1790 X_donot_mail_ind => 'N' ,--ADDED as part of Impact of IDOPA2--sykrishn
1791 x_ret_status => lv_return_status,
1792 x_msg_data => lv_msg_data,
1793 x_msg_count => lv_msg_count,
1794 X_action => l_action );
1795
1796 p_message_name := lv_msg_data;
1797
1798 ELSE
1799 CLOSE c_eapmpi_exists;
1800 END IF;
1801 END LOOP;
1802 RETURN TRUE;
1803 EXCEPTION
1804 WHEN OTHERS THEN
1805 IF c_eitpi%ISOPEN THEN
1806 CLOSE c_eitpi;
1807 END IF;
1808 IF c_eapmpi_exists%ISOPEN THEN
1809 CLOSE c_eapmpi_exists;
1810 END IF;
1811 APP_EXCEPTION.RAISE_EXCEPTION;
1812 END;
1813 EXCEPTION
1814 WHEN OTHERS THEN
1815 Fnd_Message.Set_Name('IGS','IGS_GE_UNHANDLED_EXP');
1816 Fnd_Message.Set_Token('NAME','IGR_GEN_001.admp_ins_eap_eitpi');
1817 IGS_GE_MSG_STACK.ADD;
1818 App_Exception.Raise_Exception;
1819 END; -- admp_ins_eap_eitpi
1820
1821
1822 END igr_gen_001;
[Home] [Help]