[Home] [Help]
PACKAGE BODY: APPS.IGR_I_APPL_PKG
Source
1 package body IGR_I_APPL_PKG as
2 /* $Header: IGSRH16B.pls 120.0 2005/06/01 21:08:31 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGR_I_APPL_ALL%RowType;
6 new_references IGR_I_APPL_ALL%RowType;
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_person_id IN NUMBER DEFAULT NULL,
11 x_sales_lead_id IN NUMBER DEFAULT NULL,
12 x_enquiry_appl_number IN NUMBER DEFAULT NULL,
13 x_acad_cal_type IN VARCHAR2 DEFAULT NULL,
14 x_acad_ci_sequence_number IN NUMBER DEFAULT NULL,
15 x_adm_cal_type IN VARCHAR2 DEFAULT NULL,
16 x_adm_ci_sequence_number IN NUMBER DEFAULT NULL,
17 x_enquiry_dt IN DATE DEFAULT NULL,
18 x_registering_person_id IN NUMBER DEFAULT NULL,
19 x_override_process_ind IN VARCHAR2 DEFAULT NULL,
20 x_indicated_mailing_dt IN DATE DEFAULT NULL,
21 x_comments IN VARCHAR2 DEFAULT NULL,
22 x_inq_entry_level_id IN NUMBER DEFAULT NULL,
23 x_edu_goal_id IN NUMBER DEFAULT NULL,
24 x_party_id IN NUMBER DEFAULT NULL,
25 x_how_knowus_id IN NUMBER DEFAULT NULL,
26 x_who_influenced_id IN NUMBER DEFAULT NULL,
27 x_attribute_category IN VARCHAR2 DEFAULT NULL,
28 x_attribute1 IN VARCHAR2 DEFAULT NULL,
29 x_attribute2 IN VARCHAR2 DEFAULT NULL,
30 x_attribute3 IN VARCHAR2 DEFAULT NULL,
31 x_attribute4 IN VARCHAR2 DEFAULT NULL,
32 x_attribute5 IN VARCHAR2 DEFAULT NULL,
33 x_attribute6 IN VARCHAR2 DEFAULT NULL,
34 x_attribute7 IN VARCHAR2 DEFAULT NULL,
35 x_attribute8 IN VARCHAR2 DEFAULT NULL,
36 x_attribute9 IN VARCHAR2 DEFAULT NULL,
37 x_attribute10 IN VARCHAR2 DEFAULT NULL,
38 x_attribute11 IN VARCHAR2 DEFAULT NULL,
39 x_attribute12 IN VARCHAR2 DEFAULT NULL,
40 x_attribute13 IN VARCHAR2 DEFAULT NULL,
41 x_attribute14 IN VARCHAR2 DEFAULT NULL,
42 x_attribute15 IN VARCHAR2 DEFAULT NULL,
43 x_attribute16 IN VARCHAR2 DEFAULT NULL,
44 x_attribute17 IN VARCHAR2 DEFAULT NULL,
45 x_attribute18 IN VARCHAR2 DEFAULT NULL,
46 x_attribute19 IN VARCHAR2 DEFAULT NULL,
47 x_attribute20 IN VARCHAR2 DEFAULT NULL,
48 x_last_process_dt IN DATE DEFAULT NULL,
49 x_org_id IN NUMBER DEFAULT NULL,
50 x_creation_date IN DATE DEFAULT NULL,
51 x_created_by IN NUMBER DEFAULT NULL,
52 x_last_update_date IN DATE DEFAULT NULL,
53 x_last_updated_by IN NUMBER DEFAULT NULL,
54 x_last_update_login IN NUMBER DEFAULT NULL,
55 x_pkg_reduct_ind IN VARCHAR2 DEFAULT NULL
56 ) as
57 CURSOR cur_old_ref_values IS
58 SELECT *
59 FROM IGR_I_APPL_ALL
60 WHERE rowid = x_rowid;
61 BEGIN
62 l_rowid := x_rowid;
63 -- Code for setting the Old and New Reference Values.
64 -- Populate Old Values.
65 Open cur_old_ref_values;
66 Fetch cur_old_ref_values INTO old_references;
67 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
68 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
69 IGS_GE_MSG_STACK.ADD;
70 Close cur_old_ref_values;
71 App_Exception.Raise_Exception;
72 Return;
73 END IF;
74 Close cur_old_ref_values;
75 -- Populate New Values.
76 new_references.person_id := x_person_id;
77 new_references.sales_lead_id := x_sales_lead_id;
78 new_references.enquiry_appl_number := x_enquiry_appl_number;
79 new_references.acad_cal_type := x_acad_cal_type;
80 new_references.acad_ci_sequence_number := x_acad_ci_sequence_number;
81 new_references.adm_cal_type := x_adm_cal_type;
82 new_references.adm_ci_sequence_number := x_adm_ci_sequence_number;
83 new_references.enquiry_dt := TRUNC(x_enquiry_dt);
84 new_references.registering_person_id := x_registering_person_id;
85 new_references.override_process_ind := x_override_process_ind;
86 new_references.indicated_mailing_dt := TRUNC(x_indicated_mailing_dt);
87 new_references.comments := x_comments;
88 new_references.inq_entry_level_id := x_inq_entry_level_id;
89 new_references.edu_goal_id := x_edu_goal_id;
90 new_references.party_id := x_party_id;
91 new_references.how_knowus_id := x_how_knowus_id ;
92 new_references.who_influenced_id := x_who_influenced_id;
93 new_references.attribute_category := x_attribute_category;
94 new_references.attribute1 := x_attribute1;
95 new_references.attribute2 := x_attribute2;
96 new_references.attribute3 := x_attribute3;
97 new_references.attribute4 := x_attribute4;
98 new_references.attribute5 := x_attribute5;
99 new_references.attribute6 := x_attribute6;
100 new_references.attribute7 := x_attribute7;
101 new_references.attribute8 := x_attribute8;
102 new_references.attribute9 := x_attribute9;
103 new_references.attribute10 := x_attribute10;
104 new_references.attribute11 := x_attribute11;
105 new_references.attribute12 := x_attribute12;
106 new_references.attribute13 := x_attribute13;
107 new_references.attribute14 := x_attribute14;
108 new_references.attribute15 := x_attribute15;
109 new_references.attribute16 := x_attribute16;
110 new_references.attribute17 := x_attribute17;
111 new_references.attribute18 := x_attribute18;
112 new_references.attribute19 := x_attribute19;
113 new_references.attribute20 := x_attribute20;
114 new_references.org_id := x_org_id;
115 new_references.last_process_dt := TRUNC(x_last_process_dt);
116 new_references.pkg_reduct_ind := NVL(x_pkg_reduct_ind,'N');
117 IF (p_action = 'UPDATE') THEN
118 new_references.creation_date := old_references.creation_date;
119 new_references.created_by := old_references.created_by;
120 ELSE
121 new_references.creation_date := x_creation_date;
122 new_references.created_by := x_created_by;
123 END IF;
124 new_references.last_update_date := x_last_update_date;
125 new_references.last_updated_by := x_last_updated_by;
126 new_references.last_update_login := x_last_update_login;
127 END Set_Column_Values;
128 -- Trigger description :-
129 -- "OSS_TST".trg_eap_br_iu
130 -- BEFORE INSERT OR UPDATE
131 -- ON IGR_I_APPL_ALL
132 -- FOR EACH ROW
133 PROCEDURE BeforeRowInsertUpdate1(
134 p_inserting IN BOOLEAN DEFAULT FALSE,
135 p_updating IN BOOLEAN DEFAULT FALSE,
136 p_deleting IN BOOLEAN DEFAULT FALSE
137 ) as
138 CURSOR c_birth_date(p_person_id igs_pe_person_base_v.person_id%TYPE) IS
139 SELECT birth_date
140 FROM igs_pe_person_base_v
141 WHERE person_id =p_person_id ;
142
143 CURSOR c_deceased(cp_party_id igs_pe_hz_parties.party_id%TYPE) IS
144 SELECT deceased_ind
145 FROM igs_pe_hz_parties
146 WHERE party_id = cp_party_id;
147 v_deceased_ind igs_pe_hz_parties.deceased_ind%TYPE;
148 v_message_name varchar2(30);
149 l_birth_date igs_pe_person_base_v.birth_date%TYPE;
150
151 BEGIN
152 -- Fetch the Deceased Indicator
153 IF p_inserting OR p_updating THEN
154 OPEN c_deceased(new_references.person_id);
155 FETCH c_deceased INTO v_deceased_ind;
156 CLOSE c_deceased;
157
158 OPEN c_birth_date(new_references.person_id);
159 FETCH c_birth_date INTO l_birth_date;
160 CLOSE c_birth_date;
161
162 IF ((l_birth_date IS NOT NULL) AND (l_birth_date > new_references.enquiry_dt)) THEN
163 FND_MESSAGE.SET_NAME('IGS','IGS_AD_DOB_ERROR');
164 FND_MESSAGE.SET_TOKEN ('NAME',fnd_message.get_string('IGS','IGS_AD_INQ_DT'));
165 IGS_GE_MSG_STACK.ADD;
166 APP_EXCEPTION.RAISE_EXCEPTION;
167 END IF;
168
169 ELSIF p_deleting THEN
170 OPEN c_deceased(old_references.person_id);
171 FETCH c_deceased INTO v_deceased_ind;
172 CLOSE c_deceased;
173 END IF;
174 -- Validate that inserts/updates/deletes are allowed if a person is deceased
175 -- Validate that the person is not deceased
176 IF v_deceased_ind = 'Y' THEN
177 Fnd_Message.Set_Name('IGS', 'IGS_IN_DEC_NO_INQ');
178 IGS_GE_MSG_STACK.ADD;
179 App_Exception.Raise_Exception;
180 END IF;
181
182 -- Validate that the indicated mailing date is not prior to the enquiry date.
183 IF p_inserting OR
184 (p_updating AND
185 (new_references.enquiry_dt <> TRUNC(old_references.enquiry_dt)) OR
186 (new_references.indicated_mailing_dt <> TRUNC(old_references.indicated_mailing_dt))) THEN
187 IF IGR_VAL_EAP.admp_val_eap_ind_dt(new_references.enquiry_dt,
188 new_references.indicated_mailing_dt,
189 v_message_name) = FALSE THEN
190 Fnd_Message.Set_Name('IGS', v_message_name);
191 IGS_GE_MSG_STACK.ADD;
192 App_Exception.Raise_Exception;
193 END IF;
194 END IF;
195 END BeforeRowInsertUpdate1;
196
197 PROCEDURE Check_Parent_Existance as
198
199 BEGIN
200
201 IF (((old_references.person_id = new_references.person_id)) OR
202 ((new_references.person_id IS NULL))) THEN
203 NULL;
204 ELSE
205 IF NOT(IGS_PE_PERSON_PKG.Get_PK_For_Validation (
206 new_references.person_id
207 ))THEN
208 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
209 IGS_GE_MSG_STACK.ADD;
210 App_Exception.Raise_Exception;
211 END IF ;
212 END IF;
213 IF (((old_references.registering_person_id = new_references.registering_person_id)) OR
214 ((new_references.registering_person_id IS NULL))) THEN
215 NULL;
216 ELSE
217 IF NOT(IGS_PE_PERSON_PKG.Get_PK_For_Validation (
218 new_references.registering_person_id
219 ))THEN
220 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
221 IGS_GE_MSG_STACK.ADD;
222 App_Exception.Raise_Exception;
223 END IF ;
224 END IF;
225 IF (((old_references.adm_cal_type = new_references.adm_cal_type) AND
226 (old_references.adm_ci_sequence_number = new_references.adm_ci_sequence_number) AND
227 (old_references.acad_cal_type = new_references.acad_cal_type) AND
228 (old_references.acad_ci_sequence_number = new_references.acad_ci_sequence_number)) OR
229 ((new_references.adm_cal_type IS NULL) OR
230 (new_references.adm_ci_sequence_number IS NULL) OR
231 (new_references.acad_cal_type IS NULL) OR
232 (new_references.acad_ci_sequence_number IS NULL))) THEN
233 NULL;
234 ELSE
235 IF NOT(IGS_CA_INST_REL_PKG.Get_PK_For_Validation (
236 new_references.adm_cal_type,
237 new_references.adm_ci_sequence_number,
238 new_references.acad_cal_type,
239 new_references.acad_ci_sequence_number
240 ))THEN
241 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
242 IGS_GE_MSG_STACK.ADD;
243 App_Exception.Raise_Exception;
244 END IF ;
245 END IF;
246 IF (((old_references.inq_entry_level_id = new_references.inq_entry_level_id)) OR
247 ((new_references.inq_entry_level_id IS NULL))) THEN
248 NULL;
249 ELSE
250 IF NOT(IGR_I_ENTRY_LVLS_PKG.Get_PK_For_Validation (
251 new_references.inq_entry_level_id ,
252 'N'
253 ))THEN
254 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
255 IGS_GE_MSG_STACK.ADD;
256 App_Exception.Raise_Exception;
257 END IF ;
258 END IF;
259 IF (((old_references.edu_goal_id = new_references.edu_goal_id)) OR
260 ((new_references.edu_goal_id IS NULL))) THEN
261 NULL;
262 ELSE
263 IF NOT(IGS_AD_CODE_CLASSES_PKG.Get_UK2_For_Validation (
264 new_references.edu_goal_id,
265 'EDU_GOALS',
266 'N'
267 ))THEN
268 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
269 IGS_GE_MSG_STACK.ADD;
270 App_Exception.Raise_Exception;
271 END IF ;
272 END IF;
273 IF (((old_references.how_knowus_id = new_references.how_knowus_id)) OR
274 ((new_references.how_knowus_id IS NULL))) THEN
275 NULL;
276 ELSE
277 IF NOT(IGS_AD_CODE_CLASSES_PKG.Get_UK2_For_Validation (
278 new_references.how_knowus_id ,
279 'INQ_HOW_KNOWUS',
280 'N'
281 ))THEN
282 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
283 IGS_GE_MSG_STACK.ADD;
284 App_Exception.Raise_Exception;
285 END IF ;
286 END IF;
287 IF (((old_references.who_influenced_id = new_references.who_influenced_id)) OR
288 ((new_references.who_influenced_id IS NULL))) THEN
289 NULL;
290 ELSE
291 IF NOT(IGS_AD_CODE_CLASSES_PKG.Get_UK2_For_Validation (
292 new_references.who_influenced_id,
293 'INQ_WHO_INFLUENCED',
294 'N'
295 ))THEN
296 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
297 IGS_GE_MSG_STACK.ADD;
298 App_Exception.Raise_Exception;
299 END IF ;
300 END IF;
301
302 END Check_Parent_Existance;
303
304
305 PROCEDURE Check_Constraints (
306 Column_Name IN VARCHAR2 DEFAULT NULL,
307 Column_Value IN VARCHAR2 DEFAULT NULL
308 ) as
309 BEGIN
310 IF column_name is null then
311 NULL;
312 ELSIF upper(Column_name) = 'ACAD_CAL_TYPE' then
313 new_references.acad_cal_type:= column_value;
314 ELSIF upper(Column_name) = 'ADM_CAL_TYPE' then
315 new_references.adm_cal_type:= column_value;
316 ELSIF upper(Column_name) = 'OVERRIDE_PROCESS_IND' then
317 new_references.override_process_ind:= column_value;
318 ELSIF upper(Column_name) = 'ACAD_CI_SEQUENCE_NUMBER' then
319 new_references.acad_ci_sequence_number:= IGS_GE_NUMBER.to_num(column_value);
320 ELSIF upper(Column_name) = 'ADM_CI_SEQUENCE_NUMBER' then
321 new_references.adm_ci_sequence_number:= IGS_GE_NUMBER.to_num(column_value);
322
323
324 END IF;
325 IF upper(column_name) = 'ACAD_CAL_TYPE' OR
326 column_name is null Then
327 IF new_references.acad_cal_type <> UPPER(new_references.acad_cal_type) Then
328 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
329 IGS_GE_MSG_STACK.ADD;
330 App_Exception.Raise_Exception;
331 END IF;
332 END IF;
333
334 IF upper(column_name) = 'ADM_CAL_TYPE' OR
335 column_name is null Then
336 IF new_references.adm_cal_type <> UPPER(new_references.adm_cal_type) Then
337 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
338 IGS_GE_MSG_STACK.ADD;
339 App_Exception.Raise_Exception;
340 END IF;
341 END IF;
342 IF upper(column_name) = 'OVERRIDE_PROCESS_IND' OR
343 column_name is null Then
344 IF new_references.override_process_ind NOT IN ( 'Y' , 'N' ) Then
345 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
346 IGS_GE_MSG_STACK.ADD;
347 App_Exception.Raise_Exception;
348 END IF;
349 END IF;
350 IF upper(column_name) = 'ACAD_CI_SEQUENCE_NUMBER' OR
351 column_name is null Then
352 IF new_references.acad_ci_sequence_number < 1 OR new_references.acad_ci_sequence_number > 999999 Then
353 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
354 IGS_GE_MSG_STACK.ADD;
355 App_Exception.Raise_Exception;
356 END IF;
357 END IF;
358 IF upper(column_name) = 'ADM_CI_SEQUENCE_NUMBER' OR
359 column_name is null Then
360 IF new_references.adm_ci_sequence_number < 1 AND new_references.adm_ci_sequence_number > 999999 Then
361 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
362 IGS_GE_MSG_STACK.ADD;
363 App_Exception.Raise_Exception;
364 END IF;
365 END IF;
366
367
368 END Check_Constraints;
369
370 PROCEDURE Check_Child_Existance as
371 BEGIN
372 IGR_I_A_CHARTYP_PKG.GET_FK_IGR_I_APPL (
373 old_references.person_id,
374 old_references.enquiry_appl_number
375 );
376 IGR_I_A_ITYPE_PKG.GET_FK_IGR_I_APPL (
377 old_references.person_id,
378 old_references.enquiry_appl_number
379 );
380 IGR_I_A_PKGITM_PKG.GET_FK_IGR_I_APPL (
381 old_references.person_id,
382 old_references.enquiry_appl_number
383 );
384 IGR_I_A_LINES_PKG.GET_FK_IGR_I_APPL (
385 old_references.person_id,
386 old_references.enquiry_appl_number
387 );
388
389 END Check_Child_Existance;
390 FUNCTION Get_PK_For_Validation (
391 x_person_id IN NUMBER,
392 x_enquiry_appl_number IN NUMBER
393 ) RETURN BOOLEAN AS
394 CURSOR cur_rowid IS
395 SELECT rowid
396 FROM IGR_I_APPL_ALL
397 WHERE person_id = x_person_id
398 AND enquiry_appl_number = x_enquiry_appl_number
399 FOR UPDATE NOWAIT;
400 lv_rowid cur_rowid%RowType;
401 BEGIN
402 Open cur_rowid;
403 Fetch cur_rowid INTO lv_rowid;
404 IF (cur_rowid%FOUND) THEN
405 Close cur_rowid;
406 Return (TRUE);
407 ELSE
408 Close cur_rowid;
409 Return (FALSE);
410 END IF;
411 END Get_PK_For_Validation;
412
413 PROCEDURE GET_FK_IGS_PE_PERSON (
414 x_person_id IN NUMBER
415 ) as
416 CURSOR cur_rowid IS
417 SELECT rowid
418 FROM IGR_I_APPL_ALL
419 WHERE person_id = x_person_id OR
420 registering_person_id = x_person_id ;
421 lv_rowid cur_rowid%RowType;
422 BEGIN
423 Open cur_rowid;
424 Fetch cur_rowid INTO lv_rowid;
425 IF (cur_rowid%FOUND) THEN
426 Fnd_Message.Set_Name ('IGS', 'IGS_IN_EAP_PE_FK');
427 IGS_GE_MSG_STACK.ADD;
428 Close cur_rowid;
429 App_Exception.Raise_Exception;
430 Return;
431 END IF;
432 Close cur_rowid;
433 END GET_FK_IGS_PE_PERSON;
434 PROCEDURE GET_FK_IGS_CA_INST_REL (
435 x_sub_cal_type IN VARCHAR2,
436 x_sub_ci_sequence_number IN NUMBER,
437 x_sup_cal_type IN VARCHAR2,
438 x_sup_ci_sequence_number IN NUMBER
439 ) as
440 CURSOR cur_rowid IS
441 SELECT rowid
442 FROM IGR_I_APPL_ALL
443 WHERE adm_cal_type = x_sub_cal_type
444 AND adm_ci_sequence_number = x_sub_ci_sequence_number
445 AND acad_cal_type = x_sup_cal_type
446 AND acad_ci_sequence_number = x_sup_ci_sequence_number;
447
448 lv_rowid cur_rowid%RowType;
449 BEGIN
450 Open cur_rowid;
451 Fetch cur_rowid INTO lv_rowid;
452 IF (cur_rowid%FOUND) THEN
453 Fnd_Message.Set_Name ('IGS', 'IGS_IN_EAP_CIR_FK');
454 IGS_GE_MSG_STACK.ADD;
455 Close cur_rowid;
456 App_Exception.Raise_Exception;
457 Return;
458 END IF;
459 Close cur_rowid;
460 END GET_FK_IGS_CA_INST_REL;
461 /*
462 Code modified as part of Recruitment Build Bug #2664699
463 Renamed GET_FK_IGS_AD_I_ENTRY_LVLS to GET_FK_IGR_I_ENTRY_LVLS
464 */
465 PROCEDURE GET_FK_IGR_I_ENTRY_LVLS (
466 x_inq_entry_level_id IN NUMBER
467 ) as
468 CURSOR cur_rowid IS
469 SELECT rowid
470 FROM IGR_I_APPL_ALL
471 WHERE inq_entry_level_id = x_inq_entry_level_id ;
472 lv_rowid cur_rowid%RowType;
473 BEGIN
474 Open cur_rowid;
475 Fetch cur_rowid INTO lv_rowid;
476 IF (cur_rowid%FOUND) THEN
477 Fnd_Message.Set_Name ('IGS', 'IGS_IN_EAP_AIELV_FK');
478 IGS_GE_MSG_STACK.ADD;
479 Close cur_rowid;
480 App_Exception.Raise_Exception;
481 Return;
482 END IF;
483 Close cur_rowid;
484 END GET_FK_IGR_I_ENTRY_LVLS;
485
486 PROCEDURE GET_FK_IGS_AD_CODE_CLASSES (
487 x_code_id IN NUMBER
488 ) as
489 CURSOR cur_rowid IS
490 SELECT rowid
491 FROM IGR_I_APPL_ALL
492 WHERE edu_goal_id = x_code_id
493 AND how_knowus_id = x_code_id
494 AND who_influenced_id = x_code_id;
495 lv_rowid cur_rowid%RowType;
496 BEGIN
497 Open cur_rowid;
498 Fetch cur_rowid INTO lv_rowid;
499 IF (cur_rowid%FOUND) THEN
500 Fnd_Message.Set_Name ('IGS', 'IGS_IN_EAP_ADCC_FK');
501 IGS_GE_MSG_STACK.ADD;
502 Close cur_rowid;
503 App_Exception.Raise_Exception;
504 Return;
505 END IF;
506 Close cur_rowid;
507 END GET_FK_IGS_AD_CODE_CLASSES;
508
509 PROCEDURE Before_DML (
510 p_action IN VARCHAR2,
511 x_rowid IN VARCHAR2 DEFAULT NULL,
512 x_person_id IN NUMBER DEFAULT NULL,
513 X_sales_lead_id IN NUMBER DEFAULT NULL,
514 x_enquiry_appl_number IN NUMBER DEFAULT NULL,
515 x_acad_cal_type IN VARCHAR2 DEFAULT NULL,
516 x_acad_ci_sequence_number IN NUMBER DEFAULT NULL,
517 x_adm_cal_type IN VARCHAR2 DEFAULT NULL,
518 x_adm_ci_sequence_number IN NUMBER DEFAULT NULL,
519 x_enquiry_dt IN DATE DEFAULT NULL,
520 x_registering_person_id IN NUMBER DEFAULT NULL,
521 x_override_process_ind IN VARCHAR2 DEFAULT NULL,
522 x_indicated_mailing_dt IN DATE DEFAULT NULL,
523 x_comments IN VARCHAR2 DEFAULT NULL,
524 x_inq_entry_level_id IN NUMBER DEFAULT NULL,
525 x_edu_goal_id IN NUMBER DEFAULT NULL,
526 x_party_id IN NUMBER DEFAULT NULL,
527 x_how_knowus_id IN NUMBER DEFAULT NULL,
528 x_who_influenced_id IN NUMBER DEFAULT NULL,
529 x_attribute_category IN VARCHAR2 DEFAULT NULL,
530 x_attribute1 IN VARCHAR2 DEFAULT NULL,
531 x_attribute2 IN VARCHAR2 DEFAULT NULL,
532 x_attribute3 IN VARCHAR2 DEFAULT NULL,
533 x_attribute4 IN VARCHAR2 DEFAULT NULL,
534 x_attribute5 IN VARCHAR2 DEFAULT NULL,
535 x_attribute6 IN VARCHAR2 DEFAULT NULL,
536 x_attribute7 IN VARCHAR2 DEFAULT NULL,
537 x_attribute8 IN VARCHAR2 DEFAULT NULL,
538 x_attribute9 IN VARCHAR2 DEFAULT NULL,
539 x_attribute10 IN VARCHAR2 DEFAULT NULL,
540 x_attribute11 IN VARCHAR2 DEFAULT NULL,
541 x_attribute12 IN VARCHAR2 DEFAULT NULL,
542 x_attribute13 IN VARCHAR2 DEFAULT NULL,
543 x_attribute14 IN VARCHAR2 DEFAULT NULL,
544 x_attribute15 IN VARCHAR2 DEFAULT NULL,
545 x_attribute16 IN VARCHAR2 DEFAULT NULL,
546 x_attribute17 IN VARCHAR2 DEFAULT NULL,
547 x_attribute18 IN VARCHAR2 DEFAULT NULL,
548 x_attribute19 IN VARCHAR2 DEFAULT NULL,
549 x_attribute20 IN VARCHAR2 DEFAULT NULL,
550 x_last_process_dt IN DATE DEFAULT NULL,
551 X_org_id IN NUMBER DEFAULT NULL,
552 x_creation_date IN DATE DEFAULT NULL,
553 x_created_by IN NUMBER DEFAULT NULL,
554 x_last_update_date IN DATE DEFAULT NULL,
555 x_last_updated_by IN NUMBER DEFAULT NULL,
556 x_last_update_login IN NUMBER DEFAULT NULL,
557 x_pkg_reduct_ind IN VARCHAR2 DEFAULT NULL
558 ) as
559
560 BEGIN
561
562 Set_Column_Values (
563 p_action,
564 x_rowid,
565 x_person_id,
566 X_sales_lead_id,
567 x_enquiry_appl_number,
568 x_acad_cal_type,
569 x_acad_ci_sequence_number,
570 x_adm_cal_type,
571 x_adm_ci_sequence_number,
572 x_enquiry_dt,
573 x_registering_person_id,
574 x_override_process_ind,
575 x_indicated_mailing_dt,
576 x_comments,
577 x_inq_entry_level_id ,
578 x_edu_goal_id ,
579 x_party_id ,
580 x_how_knowus_id ,
581 x_who_influenced_id ,
582 x_attribute_category ,
583 x_attribute1 ,
584 x_attribute2 ,
585 x_attribute3 ,
586 x_attribute4 ,
587 x_attribute5 ,
588 x_attribute6 ,
589 x_attribute7 ,
590 x_attribute8 ,
591 x_attribute9 ,
592 x_attribute10 ,
593 x_attribute11 ,
594 x_attribute12 ,
595 x_attribute13 ,
596 x_attribute14 ,
597 x_attribute15 ,
598 x_attribute16 ,
599 x_attribute17 ,
600 x_attribute18 ,
601 x_attribute19 ,
602 x_attribute20 ,
603 x_last_process_dt,
604 x_org_id,
605 x_creation_date,
606 x_created_by,
607 x_last_update_date,
608 x_last_updated_by,
609 x_last_update_login,
610 x_pkg_reduct_ind
611 );
612 IF (p_action = 'INSERT') THEN
613 -- Call all the procedures related to Before Insert.
614 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
615 IF Get_PK_For_Validation (
616 new_references.person_id ,
617 new_references.enquiry_appl_number
618 ) THEN
619 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
620 IGS_GE_MSG_STACK.ADD;
621 App_Exception.Raise_Exception;
622 END IF;
623 Check_Constraints;
624 Check_Parent_Existance;
625 ELSIF (p_action = 'UPDATE') THEN
626 -- Call all the procedures related to Before Update.
627 BeforeRowInsertUpdate1 ( p_updating => TRUE );
628 Check_Constraints;
629 Check_Parent_Existance;
630 ELSIF (p_action = 'DELETE') THEN
631 Check_Child_Existance;
632 ELSIF (p_action = 'VALIDATE_INSERT') THEN
633 IF Get_PK_For_Validation (
634 new_references.person_id ,
635 new_references.enquiry_appl_number
636 ) THEN
637 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
638 IGS_GE_MSG_STACK.ADD;
639 App_Exception.Raise_Exception;
640 END IF;
641 Check_Constraints;
642 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
643 Check_Constraints;
644 ELSIF (p_action = 'VALIDATE_DELETE') THEN
645 -- Call all the procedures related to Before Delete.
646 BeforeRowInsertUpdate1 ( p_deleting => TRUE );
647 Check_Child_Existance;
648 END IF;
649 END Before_DML;
650 procedure INSERT_ROW (
651 X_ROWID in out NOCOPY VARCHAR2,
652 X_PERSON_ID in NUMBER,
653 X_SALES_LEAD_ID IN NUMBER,
654 X_ENQUIRY_APPL_NUMBER OUT NOCOPY NUMBER,
655 X_ACAD_CAL_TYPE in VARCHAR2,
656 X_ACAD_CI_SEQUENCE_NUMBER in NUMBER,
657 X_ADM_CAL_TYPE in VARCHAR2,
658 X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
659 X_ENQUIRY_DT in DATE,
660 X_REGISTERING_PERSON_ID in NUMBER,
661 X_OVERRIDE_PROCESS_IND in VARCHAR2,
662 X_INDICATED_MAILING_DT in DATE,
663 X_LAST_PROCESS_DT in DATE,
664 X_COMMENTS in VARCHAR2,
665 X_INQ_ENTRY_LEVEL_ID in NUMBER DEFAULT NULL,
666 X_EDU_GOAL_ID in NUMBER DEFAULT NULL,
667 X_PARTY_ID in NUMBER DEFAULT NULL,
668 X_HOW_KNOWUS_ID in NUMBER DEFAULT NULL,
669 X_WHO_INFLUENCED_ID in NUMBER DEFAULT NULL,
670 X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
671 X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
672 X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
673 X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
674 X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
675 X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
676 X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
677 X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
678 X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
679 X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
680 X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
681 X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
682 X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
683 X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
684 X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
685 X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
686 X_ATTRIBUTE16 in VARCHAR2 DEFAULT NULL,
687 X_ATTRIBUTE17 in VARCHAR2 DEFAULT NULL,
688 X_ATTRIBUTE18 in VARCHAR2 DEFAULT NULL,
689 X_ATTRIBUTE19 in VARCHAR2 DEFAULT NULL,
690 X_ATTRIBUTE20 in VARCHAR2 DEFAULT NULL,
691 X_MODE in VARCHAR2 default 'R',
692 X_ORG_ID in NUMBER,
693 X_PKG_REDUCT_IND IN VARCHAR2 DEFAULT NULL
694 ) as
695 cursor C is select ROWID from IGR_I_APPL_ALL
696 where PERSON_ID = X_PERSON_ID
697 and ENQUIRY_APPL_NUMBER = X_ENQUIRY_APPL_NUMBER;
698 X_LAST_UPDATE_DATE DATE;
699 X_LAST_UPDATED_BY NUMBER;
700 X_LAST_UPDATE_LOGIN NUMBER;
701 X_REQUEST_ID NUMBER;
702 X_PROGRAM_ID NUMBER;
703 X_PROGRAM_APPLICATION_ID NUMBER;
704 X_PROGRAM_UPDATE_DATE DATE;
705 begin
706 X_LAST_UPDATE_DATE := SYSDATE;
707 if(X_MODE = 'I') then
708 X_LAST_UPDATED_BY := 1;
709 X_LAST_UPDATE_LOGIN := 0;
710 elsif (X_MODE = 'R') then
711 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
712 if X_LAST_UPDATED_BY is NULL then
713 X_LAST_UPDATED_BY := -1;
714 end if;
715 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
716 if X_LAST_UPDATE_LOGIN is NULL then
717 X_LAST_UPDATE_LOGIN := -1;
718 end if;
719 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
720 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
721 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
722 if (X_REQUEST_ID = -1) then
723 X_REQUEST_ID := NULL;
724 X_PROGRAM_ID := NULL;
725 X_PROGRAM_APPLICATION_ID := NULL;
726 X_PROGRAM_UPDATE_DATE := NULL;
727 else
728 X_PROGRAM_UPDATE_DATE := SYSDATE;
729 end if;
730 else
731 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
732 IGS_GE_MSG_STACK.ADD;
733 app_exception.raise_exception;
734 end if;
735 X_ENQUIRY_APPL_NUMBER := NULL;
736 Before_DML(
737 p_action=>'INSERT',
738 x_rowid=>X_ROWID,
739 x_acad_cal_type=>X_ACAD_CAL_TYPE,
740 x_acad_ci_sequence_number=>X_ACAD_CI_SEQUENCE_NUMBER,
741 x_adm_cal_type=>X_ADM_CAL_TYPE,
742 x_adm_ci_sequence_number=>X_ADM_CI_SEQUENCE_NUMBER,
743 x_comments=>X_COMMENTS,
744 x_inq_entry_level_id=>X_INQ_ENTRY_LEVEL_ID,
745 x_edu_goal_id=>X_EDU_GOAL_ID,
746 x_party_id=>X_PARTY_ID,
747 x_how_knowus_id=>X_HOW_KNOWUS_ID,
748 x_who_influenced_id=>X_WHO_INFLUENCED_ID,
749 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
750 x_attribute1=>X_ATTRIBUTE1,
751 x_attribute2=>X_ATTRIBUTE2,
752 x_attribute3=>X_ATTRIBUTE3,
753 x_attribute4=>X_ATTRIBUTE4,
754 x_attribute5=>X_ATTRIBUTE5,
755 x_attribute6=> X_ATTRIBUTE6,
756 x_attribute7=>X_ATTRIBUTE7,
757 x_attribute8=>X_ATTRIBUTE8,
758 x_attribute9=>X_ATTRIBUTE9,
759 x_attribute10=>X_ATTRIBUTE10,
760 x_attribute11=>X_ATTRIBUTE11,
761 x_attribute12=>X_ATTRIBUTE12,
762 x_attribute13=>X_ATTRIBUTE13,
763 x_attribute14=>X_ATTRIBUTE14,
764 x_attribute15=>X_ATTRIBUTE15,
765 x_attribute16=>X_ATTRIBUTE16,
766 x_attribute17=>X_ATTRIBUTE17,
767 x_attribute18=>X_ATTRIBUTE18,
768 x_attribute19=>X_ATTRIBUTE19,
769 x_attribute20=>X_ATTRIBUTE20,
770 x_enquiry_appl_number=>X_ENQUIRY_APPL_NUMBER,
771 x_enquiry_dt=>NVL(X_ENQUIRY_DT, SYSDATE),
772 x_indicated_mailing_dt=>X_INDICATED_MAILING_DT,
773 x_last_process_dt=>X_LAST_PROCESS_DT,
774 x_override_process_ind=>NVL(X_OVERRIDE_PROCESS_IND,'N'),
775 x_person_id=>X_PERSON_ID,
776 x_sales_lead_id => X_sales_lead_id,
777 x_registering_person_id=>X_REGISTERING_PERSON_ID,
778 x_org_id => igs_ge_gen_003.get_org_id,
779 x_creation_date=>X_LAST_UPDATE_DATE,
780 x_created_by=>X_LAST_UPDATED_BY,
781 x_last_update_date=>X_LAST_UPDATE_DATE,
782 x_last_updated_by=>X_LAST_UPDATED_BY,
783 x_last_update_login=>X_LAST_UPDATE_LOGIN,
784 x_pkg_reduct_ind => X_PKG_REDUCT_IND
785 );
786 insert into IGR_I_APPL_ALL (
787 PERSON_ID,
788 ENQUIRY_APPL_NUMBER,
789 SALES_LEAD_ID,
790 ACAD_CAL_TYPE,
791 ACAD_CI_SEQUENCE_NUMBER,
792 ADM_CAL_TYPE,
793 ADM_CI_SEQUENCE_NUMBER,
794 ENQUIRY_DT,
795 REGISTERING_PERSON_ID,
796 OVERRIDE_PROCESS_IND,
797 INDICATED_MAILING_DT,
798 LAST_PROCESS_DT,
799 COMMENTS,
800 INQ_ENTRY_LEVEL_ID,
801 EDU_GOAL_ID,
802 PARTY_ID,
803 HOW_KNOWUS_ID,
804 WHO_INFLUENCED_ID,
805 ATTRIBUTE_CATEGORY,
806 ATTRIBUTE1,
807 ATTRIBUTE2,
808 ATTRIBUTE3,
809 ATTRIBUTE4,
810 ATTRIBUTE5,
811 ATTRIBUTE6,
812 ATTRIBUTE7,
813 ATTRIBUTE8,
814 ATTRIBUTE9,
815 ATTRIBUTE10,
816 ATTRIBUTE11,
817 ATTRIBUTE12,
818 ATTRIBUTE13,
819 ATTRIBUTE14,
820 ATTRIBUTE15,
821 ATTRIBUTE16,
822 ATTRIBUTE17,
823 ATTRIBUTE18,
824 ATTRIBUTE19,
825 ATTRIBUTE20,
826 ORG_ID,
827 CREATION_DATE,
828 CREATED_BY,
829 LAST_UPDATE_DATE,
830 LAST_UPDATED_BY,
831 LAST_UPDATE_LOGIN,
832 REQUEST_ID,
833 PROGRAM_ID,
834 PROGRAM_APPLICATION_ID,
835 PROGRAM_UPDATE_DATE,
836 PKG_REDUCT_IND
837 ) values (
838 NEW_REFERENCES.PERSON_ID,
839 IGR_I_APPL_S.NEXTVAL,
840 NEW_REFERENCES.SALES_LEAD_ID,
841 NEW_REFERENCES.ACAD_CAL_TYPE,
842 NEW_REFERENCES.ACAD_CI_SEQUENCE_NUMBER,
843 NEW_REFERENCES.ADM_CAL_TYPE,
844 NEW_REFERENCES.ADM_CI_SEQUENCE_NUMBER,
845 NEW_REFERENCES.ENQUIRY_DT,
846 NEW_REFERENCES.REGISTERING_PERSON_ID,
847 NEW_REFERENCES.OVERRIDE_PROCESS_IND,
848 NEW_REFERENCES.INDICATED_MAILING_DT,
849 NEW_REFERENCES.LAST_PROCESS_DT,
850 NEW_REFERENCES.COMMENTS,
851 NEW_REFERENCES.INQ_ENTRY_LEVEL_ID,
852 NEW_REFERENCES.EDU_GOAL_ID,
853 NEW_REFERENCES.PARTY_ID,
854 NEW_REFERENCES.HOW_KNOWUS_ID,
855 NEW_REFERENCES.WHO_INFLUENCED_ID,
856 NEW_REFERENCES.ATTRIBUTE_CATEGORY,
857 NEW_REFERENCES.ATTRIBUTE1,
858 NEW_REFERENCES.ATTRIBUTE2,
859 NEW_REFERENCES.ATTRIBUTE3,
860 NEW_REFERENCES.ATTRIBUTE4,
861 NEW_REFERENCES.ATTRIBUTE5,
862 NEW_REFERENCES.ATTRIBUTE6,
863 NEW_REFERENCES.ATTRIBUTE7,
864 NEW_REFERENCES.ATTRIBUTE8,
865 NEW_REFERENCES.ATTRIBUTE9,
866 NEW_REFERENCES.ATTRIBUTE10,
867 NEW_REFERENCES.ATTRIBUTE11,
868 NEW_REFERENCES.ATTRIBUTE12,
869 NEW_REFERENCES.ATTRIBUTE13,
870 NEW_REFERENCES.ATTRIBUTE14,
871 NEW_REFERENCES.ATTRIBUTE15,
872 NEW_REFERENCES.ATTRIBUTE16,
873 NEW_REFERENCES.ATTRIBUTE17,
874 NEW_REFERENCES.ATTRIBUTE18,
875 NEW_REFERENCES.ATTRIBUTE19,
876 NEW_REFERENCES.ATTRIBUTE20,
877 NEW_REFERENCES.ORG_ID,
878 X_LAST_UPDATE_DATE,
879 X_LAST_UPDATED_BY,
880 X_LAST_UPDATE_DATE,
881 X_LAST_UPDATED_BY,
882 X_LAST_UPDATE_LOGIN,
883 X_REQUEST_ID,
884 X_PROGRAM_ID,
885 X_PROGRAM_APPLICATION_ID,
886 X_PROGRAM_UPDATE_DATE,
887 NEW_REFERENCES.PKG_REDUCT_IND
888 )RETURNING ENQUIRY_APPL_NUMBER INTO X_ENQUIRY_APPL_NUMBER;
889 open c;
890 fetch c into X_ROWID;
891 if (c%notfound) then
892 close c;
893 raise no_data_found;
894 end if;
895 close c;
896 end INSERT_ROW;
897 procedure LOCK_ROW (
898 X_ROWID in VARCHAR2,
899 X_PERSON_ID in NUMBER,
900 X_SALES_LEAD_ID IN NUMBER,
901 X_ENQUIRY_APPL_NUMBER in NUMBER,
902 X_ACAD_CAL_TYPE in VARCHAR2,
903 X_ACAD_CI_SEQUENCE_NUMBER in NUMBER,
904 X_ADM_CAL_TYPE in VARCHAR2,
905 X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
906 X_ENQUIRY_DT in DATE,
907 X_REGISTERING_PERSON_ID in NUMBER,
908 X_OVERRIDE_PROCESS_IND in VARCHAR2,
909 X_INDICATED_MAILING_DT in DATE,
910 X_LAST_PROCESS_DT in DATE,
911 X_COMMENTS in VARCHAR2,
912 X_INQ_ENTRY_LEVEL_ID in NUMBER DEFAULT NULL,
913 X_EDU_GOAL_ID in NUMBER DEFAULT NULL,
914 X_PARTY_ID in NUMBER DEFAULT NULL,
915 X_HOW_KNOWUS_ID in NUMBER DEFAULT NULL,
916 X_WHO_INFLUENCED_ID in NUMBER DEFAULT NULL,
917 X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
918 X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
919 X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
920 X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
921 X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
922 X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
923 X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
924 X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
925 X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
926 X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
927 X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
928 X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
929 X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
930 X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
931 X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
932 X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
933 X_ATTRIBUTE16 in VARCHAR2 DEFAULT NULL,
934 X_ATTRIBUTE17 in VARCHAR2 DEFAULT NULL,
935 X_ATTRIBUTE18 in VARCHAR2 DEFAULT NULL,
936 X_ATTRIBUTE19 in VARCHAR2 DEFAULT NULL,
937 X_ATTRIBUTE20 in VARCHAR2 DEFAULT NULL,
938 X_PKG_REDUCT_IND IN VARCHAR2 DEFAULT NULL
939 ) as
940 cursor c1 is select
941 ACAD_CAL_TYPE,
942 ACAD_CI_SEQUENCE_NUMBER,
943 ADM_CAL_TYPE,
944 ADM_CI_SEQUENCE_NUMBER,
945 ENQUIRY_DT,
946 REGISTERING_PERSON_ID,
947 OVERRIDE_PROCESS_IND,
948 INDICATED_MAILING_DT,
949 LAST_PROCESS_DT,
950 COMMENTS,
951 INQ_ENTRY_LEVEL_ID,
952 EDU_GOAL_ID,
953 PARTY_ID,
954 HOW_KNOWUS_ID,
955 WHO_INFLUENCED_ID,
956 ATTRIBUTE_CATEGORY,
957 ATTRIBUTE1,
958 ATTRIBUTE2,
959 ATTRIBUTE3,
960 ATTRIBUTE4,
961 ATTRIBUTE5,
962 ATTRIBUTE6,
963 ATTRIBUTE7,
964 ATTRIBUTE8,
965 ATTRIBUTE9,
966 ATTRIBUTE10,
967 ATTRIBUTE11,
968 ATTRIBUTE12,
969 ATTRIBUTE13,
970 ATTRIBUTE14,
971 ATTRIBUTE15,
972 ATTRIBUTE16,
973 ATTRIBUTE17,
974 ATTRIBUTE18,
975 ATTRIBUTE19,
976 ATTRIBUTE20,
977 PKG_REDUCT_IND
978 from IGR_I_APPL_ALL
979 where ROWID = X_ROWID
980 for update nowait;
981 tlinfo c1%rowtype;
982 begin
983 open c1;
984 fetch c1 into tlinfo;
985 if (c1%notfound) then
986 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
987 IGS_GE_MSG_STACK.ADD;
988 close c1;
989 app_exception.raise_exception;
990 return;
991 end if;
992 close c1;
993
994 if (
995 ((tlinfo.ACAD_CAL_TYPE = X_ACAD_CAL_TYPE) OR ((tlinfo.ACAD_CAL_TYPE is null) AND (X_ACAD_CAL_TYPE is null)))
996 AND ((tlinfo.ACAD_CI_SEQUENCE_NUMBER = X_ACAD_CI_SEQUENCE_NUMBER) OR ((tlinfo.ACAD_CI_SEQUENCE_NUMBER is null) AND (X_ACAD_CI_SEQUENCE_NUMBER is null)))
997 AND ((tlinfo.ADM_CAL_TYPE = X_ADM_CAL_TYPE) OR ((tlinfo.ADM_CAL_TYPE is null) AND (X_ADM_CAL_TYPE is null)))
998 AND ((tlinfo.ADM_CI_SEQUENCE_NUMBER = X_ADM_CI_SEQUENCE_NUMBER) OR ((tlinfo.ADM_CI_SEQUENCE_NUMBER is null) AND (X_ADM_CI_SEQUENCE_NUMBER is null)))
999 AND (TRUNC(tlinfo.ENQUIRY_DT) = TRUNC(X_ENQUIRY_DT))
1000 AND ((tlinfo.REGISTERING_PERSON_ID = X_REGISTERING_PERSON_ID) OR ((tlinfo.REGISTERING_PERSON_ID is null) AND (X_REGISTERING_PERSON_ID is null)))
1001 AND (tlinfo.OVERRIDE_PROCESS_IND = X_OVERRIDE_PROCESS_IND)
1002 AND ((TRUNC(tlinfo.INDICATED_MAILING_DT) = TRUNC(X_INDICATED_MAILING_DT)) OR ((tlinfo.INDICATED_MAILING_DT is null) AND (X_INDICATED_MAILING_DT is null)))
1003 AND ((TRUNC(tlinfo.LAST_PROCESS_DT) = TRUNC(X_LAST_PROCESS_DT)) OR ((tlinfo.LAST_PROCESS_DT is null) AND (X_LAST_PROCESS_DT is null)))
1004 AND ((tlinfo.COMMENTS = X_COMMENTS) OR ((tlinfo.COMMENTS is null) AND (X_COMMENTS is null)))
1005 AND ((tlinfo.INQ_ENTRY_LEVEL_ID = X_INQ_ENTRY_LEVEL_ID) OR ((tlinfo.INQ_ENTRY_LEVEL_ID is null) AND (X_INQ_ENTRY_LEVEL_ID is null)))
1006 AND ((tlinfo.EDU_GOAL_ID = X_EDU_GOAL_ID) OR ((tlinfo.EDU_GOAL_ID is null) AND (X_EDU_GOAL_ID is null)))
1007 AND ((tlinfo.PARTY_ID = X_PARTY_ID) OR ((tlinfo.PARTY_ID is null) AND (X_PARTY_ID is null)))
1008 AND ((tlinfo.HOW_KNOWUS_ID = X_HOW_KNOWUS_ID) OR ((tlinfo.HOW_KNOWUS_ID is null) AND (X_HOW_KNOWUS_ID is null)))
1009 AND ((tlinfo.WHO_INFLUENCED_ID = X_WHO_INFLUENCED_ID) OR ((tlinfo.WHO_INFLUENCED_ID is null) AND (X_WHO_INFLUENCED_ID is null)))
1010 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY) OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
1011 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1) OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
1012 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2) OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
1013 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3) OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
1014 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4) OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
1015 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5) OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
1016 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6) OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
1017 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7) OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
1018 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8) OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
1019 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9) OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
1020 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10) OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
1021 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11) OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
1022 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12) OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
1023 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13) OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
1024 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14) OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
1025 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15) OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
1026 AND ((tlinfo.ATTRIBUTE16 = X_ATTRIBUTE16) OR ((tlinfo.ATTRIBUTE16 is null) AND (X_ATTRIBUTE16 is null)))
1027 AND ((tlinfo.ATTRIBUTE17 = X_ATTRIBUTE17) OR ((tlinfo.ATTRIBUTE17 is null) AND (X_ATTRIBUTE17 is null)))
1028 AND ((tlinfo.ATTRIBUTE18 = X_ATTRIBUTE18) OR ((tlinfo.ATTRIBUTE18 is null) AND (X_ATTRIBUTE18 is null)))
1029 AND ((tlinfo.ATTRIBUTE19 = X_ATTRIBUTE19) OR ((tlinfo.ATTRIBUTE19 is null) AND (X_ATTRIBUTE19 is null)))
1030 AND ((tlinfo.ATTRIBUTE20 = X_ATTRIBUTE20) OR ((tlinfo.ATTRIBUTE20 is null) AND (X_ATTRIBUTE20 is null)))
1031 AND ((tlinfo.PKG_REDUCT_IND = X_PKG_REDUCT_IND) OR ((tlinfo.PKG_REDUCT_IND is null) AND (X_PKG_REDUCT_IND is null)))
1032 ) then
1033 null;
1034 else
1035 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1036 IGS_GE_MSG_STACK.ADD;
1037 app_exception.raise_exception;
1038 end if;
1039 return;
1040 end LOCK_ROW;
1041 procedure UPDATE_ROW (
1042 X_ROWID in VARCHAR2,
1043 X_PERSON_ID in NUMBER,
1044 X_SALES_LEAD_ID in NUMBER,
1045 X_ENQUIRY_APPL_NUMBER in NUMBER,
1046 X_ACAD_CAL_TYPE in VARCHAR2,
1047 X_ACAD_CI_SEQUENCE_NUMBER in NUMBER,
1048 X_ADM_CAL_TYPE in VARCHAR2,
1049 X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
1050 X_ENQUIRY_DT in DATE,
1051 X_REGISTERING_PERSON_ID in NUMBER,
1052 X_OVERRIDE_PROCESS_IND in VARCHAR2,
1053 X_INDICATED_MAILING_DT in DATE,
1054 X_LAST_PROCESS_DT in DATE,
1055 X_COMMENTS in VARCHAR2,
1056 X_INQ_ENTRY_LEVEL_ID in NUMBER DEFAULT NULL,
1057 X_EDU_GOAL_ID in NUMBER DEFAULT NULL,
1058 X_PARTY_ID in NUMBER DEFAULT NULL,
1059 X_HOW_KNOWUS_ID in NUMBER DEFAULT NULL,
1060 X_WHO_INFLUENCED_ID in NUMBER DEFAULT NULL,
1061 X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
1062 X_ATTRIBUTE1 in VARCHAR2 DEFAULT NULL,
1063 X_ATTRIBUTE2 in VARCHAR2 DEFAULT NULL,
1064 X_ATTRIBUTE3 in VARCHAR2 DEFAULT NULL,
1065 X_ATTRIBUTE4 in VARCHAR2 DEFAULT NULL,
1066 X_ATTRIBUTE5 in VARCHAR2 DEFAULT NULL,
1067 X_ATTRIBUTE6 in VARCHAR2 DEFAULT NULL,
1068 X_ATTRIBUTE7 in VARCHAR2 DEFAULT NULL,
1069 X_ATTRIBUTE8 in VARCHAR2 DEFAULT NULL,
1070 X_ATTRIBUTE9 in VARCHAR2 DEFAULT NULL,
1071 X_ATTRIBUTE10 in VARCHAR2 DEFAULT NULL,
1072 X_ATTRIBUTE11 in VARCHAR2 DEFAULT NULL,
1073 X_ATTRIBUTE12 in VARCHAR2 DEFAULT NULL,
1074 X_ATTRIBUTE13 in VARCHAR2 DEFAULT NULL,
1075 X_ATTRIBUTE14 in VARCHAR2 DEFAULT NULL,
1076 X_ATTRIBUTE15 in VARCHAR2 DEFAULT NULL,
1077 X_ATTRIBUTE16 in VARCHAR2 DEFAULT NULL,
1078 X_ATTRIBUTE17 in VARCHAR2 DEFAULT NULL,
1079 X_ATTRIBUTE18 in VARCHAR2 DEFAULT NULL,
1080 X_ATTRIBUTE19 in VARCHAR2 DEFAULT NULL,
1081 X_ATTRIBUTE20 in VARCHAR2 DEFAULT NULL,
1082 X_MODE in VARCHAR2 default 'R',
1083 X_PKG_REDUCT_IND IN VARCHAR2 DEFAULT NULL
1084 ) as
1085 X_LAST_UPDATE_DATE DATE;
1086 X_LAST_UPDATED_BY NUMBER;
1087 X_LAST_UPDATE_LOGIN NUMBER;
1088 X_REQUEST_ID NUMBER;
1089 X_PROGRAM_ID NUMBER;
1090 X_PROGRAM_APPLICATION_ID NUMBER;
1091 X_PROGRAM_UPDATE_DATE DATE;
1092 begin
1093 X_LAST_UPDATE_DATE := SYSDATE;
1094 if(X_MODE = 'I') then
1095 X_LAST_UPDATED_BY := 1;
1096 X_LAST_UPDATE_LOGIN := 0;
1097 elsif (X_MODE = 'R') then
1098 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1099 if X_LAST_UPDATED_BY is NULL then
1100 X_LAST_UPDATED_BY := -1;
1101 end if;
1102 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1103 if X_LAST_UPDATE_LOGIN is NULL then
1104 X_LAST_UPDATE_LOGIN := -1;
1105 end if;
1106 else
1107 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1108 IGS_GE_MSG_STACK.ADD;
1109 app_exception.raise_exception;
1110 end if;
1111 Before_DML(
1112 p_action=>'UPDATE',
1113 x_rowid=>X_ROWID,
1114 x_acad_cal_type=>X_ACAD_CAL_TYPE,
1115 x_acad_ci_sequence_number=>X_ACAD_CI_SEQUENCE_NUMBER,
1116 x_adm_cal_type=>X_ADM_CAL_TYPE,
1117 x_adm_ci_sequence_number=>X_ADM_CI_SEQUENCE_NUMBER,
1118 x_comments=>X_COMMENTS,
1119 x_inq_entry_level_id=>X_INQ_ENTRY_LEVEL_ID,
1120 x_edu_goal_id=>X_EDU_GOAL_ID,
1121 x_party_id=>X_PARTY_ID,
1122 x_how_knowus_id=>X_HOW_KNOWUS_ID,
1123 x_who_influenced_id=>X_WHO_INFLUENCED_ID,
1124 x_attribute_category=>X_ATTRIBUTE_CATEGORY,
1125 x_attribute1=>X_ATTRIBUTE1,
1126 x_attribute2=>X_ATTRIBUTE2,
1127 x_attribute3=>X_ATTRIBUTE3,
1128 x_attribute4=>X_ATTRIBUTE4,
1129 x_attribute5=>X_ATTRIBUTE5,
1130 x_attribute6=>X_ATTRIBUTE6,
1131 x_attribute7=>X_ATTRIBUTE7,
1132 x_attribute8=>X_ATTRIBUTE8,
1133 x_attribute9=>X_ATTRIBUTE9,
1134 x_attribute10=>X_ATTRIBUTE10,
1135 x_attribute11=>X_ATTRIBUTE11,
1136 x_attribute12=>X_ATTRIBUTE12,
1137 x_attribute13=>X_ATTRIBUTE13,
1138 x_attribute14=>X_ATTRIBUTE14,
1139 x_attribute15=>X_ATTRIBUTE15,
1140 x_attribute16=>X_ATTRIBUTE16,
1141 x_attribute17=>X_ATTRIBUTE17,
1142 x_attribute18=>X_ATTRIBUTE18,
1143 x_attribute19=>X_ATTRIBUTE19,
1144 x_attribute20=>X_ATTRIBUTE20,
1145 x_enquiry_appl_number=>X_ENQUIRY_APPL_NUMBER,
1146 x_enquiry_dt=>X_ENQUIRY_DT,
1147 x_indicated_mailing_dt=>X_INDICATED_MAILING_DT,
1148 x_last_process_dt=>X_LAST_PROCESS_DT,
1149 x_override_process_ind=>X_OVERRIDE_PROCESS_IND,
1150 x_person_id=>X_PERSON_ID,
1151 X_sales_lead_id => X_sales_lead_id,
1152 x_registering_person_id=>X_REGISTERING_PERSON_ID,
1153 x_creation_date=>X_LAST_UPDATE_DATE,
1154 x_created_by=>X_LAST_UPDATED_BY,
1155 x_last_update_date=>X_LAST_UPDATE_DATE,
1156 x_last_updated_by=>X_LAST_UPDATED_BY,
1157 x_last_update_login=>X_LAST_UPDATE_LOGIN,
1158 x_pkg_reduct_ind => X_PKG_REDUCT_IND
1159 );
1160 if (X_MODE = 'R') then
1161 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
1162 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
1163 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
1164 if (X_REQUEST_ID = -1) then
1165 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
1166 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
1167 X_PROGRAM_APPLICATION_ID :=
1168 OLD_REFERENCES.PROGRAM_APPLICATION_ID;
1169 X_PROGRAM_UPDATE_DATE :=
1170 OLD_REFERENCES.PROGRAM_UPDATE_DATE;
1171 else
1172 X_PROGRAM_UPDATE_DATE := SYSDATE;
1173 end if;
1174 end if;
1175 update IGR_I_APPL_ALL set
1176 ACAD_CAL_TYPE = NEW_REFERENCES.ACAD_CAL_TYPE,
1177 ACAD_CI_SEQUENCE_NUMBER = NEW_REFERENCES.ACAD_CI_SEQUENCE_NUMBER,
1178 ADM_CAL_TYPE = NEW_REFERENCES.ADM_CAL_TYPE,
1179 ADM_CI_SEQUENCE_NUMBER = NEW_REFERENCES.ADM_CI_SEQUENCE_NUMBER,
1180 ENQUIRY_DT = NEW_REFERENCES.ENQUIRY_DT,
1181 REGISTERING_PERSON_ID = NEW_REFERENCES.REGISTERING_PERSON_ID,
1182 OVERRIDE_PROCESS_IND = NEW_REFERENCES.OVERRIDE_PROCESS_IND,
1183 INDICATED_MAILING_DT = NEW_REFERENCES.INDICATED_MAILING_DT,
1184 LAST_PROCESS_DT = NEW_REFERENCES.LAST_PROCESS_DT,
1185 COMMENTS = NEW_REFERENCES.COMMENTS,
1186 INQ_ENTRY_LEVEL_ID=NEW_REFERENCES.INQ_ENTRY_LEVEL_ID,
1187 EDU_GOAL_ID=NEW_REFERENCES.EDU_GOAL_ID,
1188 PARTY_ID=NEW_REFERENCES.PARTY_ID,
1189 HOW_KNOWUS_ID=NEW_REFERENCES.HOW_KNOWUS_ID,
1190 WHO_INFLUENCED_ID=NEW_REFERENCES.WHO_INFLUENCED_ID,
1191 ATTRIBUTE_CATEGORY=NEW_REFERENCES.ATTRIBUTE_CATEGORY,
1192 ATTRIBUTE1=NEW_REFERENCES.ATTRIBUTE1,
1193 ATTRIBUTE2=NEW_REFERENCES.ATTRIBUTE2,
1194 ATTRIBUTE3=NEW_REFERENCES.ATTRIBUTE3,
1195 ATTRIBUTE4=NEW_REFERENCES.ATTRIBUTE4,
1196 ATTRIBUTE5=NEW_REFERENCES.ATTRIBUTE5,
1197 ATTRIBUTE6=NEW_REFERENCES.ATTRIBUTE6,
1198 ATTRIBUTE7=NEW_REFERENCES.ATTRIBUTE7,
1199 ATTRIBUTE8=NEW_REFERENCES.ATTRIBUTE8,
1200 ATTRIBUTE9=NEW_REFERENCES.ATTRIBUTE9,
1201 ATTRIBUTE10=NEW_REFERENCES.ATTRIBUTE10,
1202 ATTRIBUTE11=NEW_REFERENCES.ATTRIBUTE11,
1203 ATTRIBUTE12=NEW_REFERENCES.ATTRIBUTE12,
1204 ATTRIBUTE13=NEW_REFERENCES.ATTRIBUTE13,
1205 ATTRIBUTE14=NEW_REFERENCES.ATTRIBUTE14,
1206 ATTRIBUTE15=NEW_REFERENCES.ATTRIBUTE15,
1207 ATTRIBUTE16=NEW_REFERENCES.ATTRIBUTE16,
1208 ATTRIBUTE17=NEW_REFERENCES.ATTRIBUTE17,
1209 ATTRIBUTE18=NEW_REFERENCES.ATTRIBUTE18,
1210 ATTRIBUTE19=NEW_REFERENCES.ATTRIBUTE19,
1211 ATTRIBUTE20=NEW_REFERENCES.ATTRIBUTE20,
1212 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1213 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1214 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1215 REQUEST_ID = X_REQUEST_ID,
1216 PROGRAM_ID = X_PROGRAM_ID,
1217 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
1218 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
1219 PKG_REDUCT_IND = X_PKG_REDUCT_IND
1220 where ROWID = X_ROWID
1221 ;
1222 if (sql%notfound) then
1223 raise no_data_found;
1224 end if;
1225 end UPDATE_ROW;
1226 procedure ADD_ROW (
1227 X_ROWID in out NOCOPY VARCHAR2,
1228 X_PERSON_ID in NUMBER,
1229 X_ENQUIRY_APPL_NUMBER in NUMBER,
1230 X_SALES_LEAD_ID in NUMBER,
1231 X_ACAD_CAL_TYPE in VARCHAR2,
1232 X_ACAD_CI_SEQUENCE_NUMBER in NUMBER,
1233 X_ADM_CAL_TYPE in VARCHAR2,
1234 X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
1235 X_ENQUIRY_DT in DATE,
1236 X_REGISTERING_PERSON_ID in NUMBER,
1237 X_OVERRIDE_PROCESS_IND in VARCHAR2,
1238 X_INDICATED_MAILING_DT in DATE,
1239 X_LAST_PROCESS_DT in DATE,
1240 X_COMMENTS in VARCHAR2,
1241 X_INQ_ENTRY_LEVEL_ID in NUMBER DEFAULT NULL,
1242 X_EDU_GOAL_ID in NUMBER DEFAULT NULL,
1243 X_PARTY_ID in NUMBER DEFAULT NULL,
1244 X_HOW_KNOWUS_ID in NUMBER DEFAULT NULL,
1245 X_WHO_INFLUENCED_ID in NUMBER DEFAULT NULL,
1246 X_ATTRIBUTE_CATEGORY in VARCHAR2 DEFAULT NULL,
1247 X_ATTRIBUTE1 IN VARCHAR2 DEFAULT NULL,
1248 X_ATTRIBUTE2 IN VARCHAR2 DEFAULT NULL,
1249 X_ATTRIBUTE3 IN VARCHAR2 DEFAULT NULL,
1250 X_ATTRIBUTE4 IN VARCHAR2 DEFAULT NULL,
1251 X_ATTRIBUTE5 IN VARCHAR2 DEFAULT NULL,
1252 X_ATTRIBUTE6 IN VARCHAR2 DEFAULT NULL,
1253 X_ATTRIBUTE7 IN VARCHAR2 DEFAULT NULL,
1254 X_ATTRIBUTE8 IN VARCHAR2 DEFAULT NULL,
1255 X_ATTRIBUTE9 IN VARCHAR2 DEFAULT NULL,
1256 X_ATTRIBUTE10 IN VARCHAR2 DEFAULT NULL,
1257 X_ATTRIBUTE11 IN VARCHAR2 DEFAULT NULL,
1258 X_ATTRIBUTE12 IN VARCHAR2 DEFAULT NULL,
1259 X_ATTRIBUTE13 IN VARCHAR2 DEFAULT NULL,
1260 X_ATTRIBUTE14 IN VARCHAR2 DEFAULT NULL,
1261 X_ATTRIBUTE15 IN VARCHAR2 DEFAULT NULL,
1262 X_ATTRIBUTE16 IN VARCHAR2 DEFAULT NULL,
1263 X_ATTRIBUTE17 IN VARCHAR2 DEFAULT NULL,
1264 X_ATTRIBUTE18 IN VARCHAR2 DEFAULT NULL,
1265 X_ATTRIBUTE19 IN VARCHAR2 DEFAULT NULL,
1266 X_ATTRIBUTE20 IN VARCHAR2 DEFAULT NULL,
1267 X_MODE in VARCHAR2 default 'R',
1268 X_ORG_ID in NUMBER,
1269 X_PKG_REDUCT_IND IN VARCHAR2 DEFAULT NULL
1270 ) as
1271 cursor c1 is select rowid from IGR_I_APPL_ALL
1272 where PERSON_ID = X_PERSON_ID
1273 and ENQUIRY_APPL_NUMBER = X_ENQUIRY_APPL_NUMBER ;
1274 l_sales_lead_id IGR_I_APPL_ALL.sales_lead_id%TYPE;
1275 begin
1276 open c1;
1277 fetch c1 into X_ROWID;
1278 if (c1%notfound) then
1279 close c1;
1280 INSERT_ROW (
1281 X_ROWID,
1282 X_PERSON_ID,
1283 X_ENQUIRY_APPL_NUMBER,
1284 l_sales_lead_id,
1285 X_ACAD_CAL_TYPE,
1286 X_ACAD_CI_SEQUENCE_NUMBER,
1287 X_ADM_CAL_TYPE,
1288 X_ADM_CI_SEQUENCE_NUMBER,
1289 X_ENQUIRY_DT,
1290 X_REGISTERING_PERSON_ID,
1291 X_OVERRIDE_PROCESS_IND,
1292 X_INDICATED_MAILING_DT,
1293 X_LAST_PROCESS_DT,
1294 X_COMMENTS,
1295 X_INQ_ENTRY_LEVEL_ID,
1296 X_EDU_GOAL_ID,
1297 X_PARTY_ID,
1298 X_HOW_KNOWUS_ID,
1299 X_WHO_INFLUENCED_ID,
1300 X_ATTRIBUTE_CATEGORY,
1301 X_ATTRIBUTE1,
1302 X_ATTRIBUTE2,
1303 X_ATTRIBUTE3,
1304 X_ATTRIBUTE4,
1305 X_ATTRIBUTE5,
1306 X_ATTRIBUTE6,
1307 X_ATTRIBUTE7,
1308 X_ATTRIBUTE8,
1309 X_ATTRIBUTE9,
1310 X_ATTRIBUTE10,
1311 X_ATTRIBUTE11,
1312 X_ATTRIBUTE12,
1313 X_ATTRIBUTE13,
1314 X_ATTRIBUTE14,
1315 X_ATTRIBUTE15,
1316 X_ATTRIBUTE16,
1317 X_ATTRIBUTE17,
1318 X_ATTRIBUTE18,
1319 X_ATTRIBUTE19,
1320 X_ATTRIBUTE20,
1321 X_MODE,
1322 X_ORG_ID,
1323 X_PKG_REDUCT_IND);
1324 return;
1325 end if;
1326 close c1;
1327 UPDATE_ROW (
1328 X_ROWID,
1329 X_PERSON_ID,
1330 X_ENQUIRY_APPL_NUMBER,
1331 X_SALES_LEAD_ID,
1332 X_ACAD_CAL_TYPE,
1333 X_ACAD_CI_SEQUENCE_NUMBER,
1334 X_ADM_CAL_TYPE,
1335 X_ADM_CI_SEQUENCE_NUMBER,
1336 X_ENQUIRY_DT,
1337 X_REGISTERING_PERSON_ID,
1338 X_OVERRIDE_PROCESS_IND,
1339 X_INDICATED_MAILING_DT,
1340 X_LAST_PROCESS_DT,
1341 X_COMMENTS,
1342 X_INQ_ENTRY_LEVEL_ID,
1343 X_EDU_GOAL_ID,
1344 X_PARTY_ID,
1345 X_HOW_KNOWUS_ID,
1346 X_WHO_INFLUENCED_ID,
1347 X_ATTRIBUTE_CATEGORY,
1348 X_ATTRIBUTE1,
1349 X_ATTRIBUTE2,
1350 X_ATTRIBUTE3,
1351 X_ATTRIBUTE4,
1352 X_ATTRIBUTE5,
1353 X_ATTRIBUTE6,
1354 X_ATTRIBUTE7,
1355 X_ATTRIBUTE8,
1356 X_ATTRIBUTE9,
1357 X_ATTRIBUTE10,
1358 X_ATTRIBUTE11,
1359 X_ATTRIBUTE12,
1360 X_ATTRIBUTE13,
1361 X_ATTRIBUTE14,
1362 X_ATTRIBUTE15,
1363 X_ATTRIBUTE16,
1364 X_ATTRIBUTE17,
1365 X_ATTRIBUTE18,
1366 X_ATTRIBUTE19,
1367 X_ATTRIBUTE20,
1368 X_MODE,
1369 X_PKG_REDUCT_IND);
1370 end ADD_ROW;
1371 procedure DELETE_ROW (
1372 X_ROWID in VARCHAR2
1373 ) as
1374 begin
1375 Before_DML(
1376 p_action => 'DELETE',
1377 x_rowid => X_ROWID
1378 );
1379 delete from IGR_I_APPL_ALL
1380 where ROWID = X_ROWID;
1381 if (sql%notfound) then
1382 raise no_data_found;
1383 end if;
1384 end DELETE_ROW;
1385 end IGR_I_APPL_PKG;