[Home] [Help]
PACKAGE BODY: APPS.IGR_I_A_PKGITM_PKG
Source
1 package body IGR_I_A_PKGITM_PKG as
2 /* $Header: IGSRH18B.pls 120.0 2005/06/01 13:25:52 appldev noship $ */
3
4
5 l_rowid VARCHAR2(25);
6 old_references IGR_I_A_PKGITM%RowType;
7 new_references IGR_I_A_PKGITM%RowType;
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_person_id IN NUMBER DEFAULT NULL,
12 x_enquiry_appl_number IN NUMBER DEFAULT NULL,
13 x_PACKAGE_ITEM_ID IN NUMBER DEFAULT NULL,
14 x_mailed_dt IN DATE DEFAULT NULL,
15 x_creation_date IN DATE DEFAULT NULL,
16 x_created_by IN NUMBER DEFAULT NULL,
17 x_last_update_date IN DATE DEFAULT NULL,
18 x_last_updated_by IN NUMBER DEFAULT NULL,
19 x_last_update_login IN NUMBER DEFAULT NULL,
20 x_donot_mail_ind IN VARCHAR2 DEFAULT NULL
21 ) as
22
23 CURSOR cur_old_ref_values IS
24 SELECT *
25 FROM IGR_I_A_PKGITM
26 WHERE rowid = x_rowid;
27
28 BEGIN
29
30 l_rowid := x_rowid;
31
32 -- Code for setting the Old and New Reference Values.
33 -- Populate Old Values.
34 Open cur_old_ref_values;
35 Fetch cur_old_ref_values INTO old_references;
36 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
37 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
38 IGS_GE_MSG_STACK.ADD;
39 Close cur_old_ref_values;
40 App_Exception.Raise_Exception;
41 Return;
42 END IF;
43 Close cur_old_ref_values;
44
45 -- Populate New Values.
46 new_references.person_id := x_person_id;
47 new_references.enquiry_appl_number := x_enquiry_appl_number;
48 new_references.PACKAGE_ITEM_ID:= x_PACKAGE_ITEM_ID;
49 new_references.mailed_dt := TRUNC(x_mailed_dt);
50 new_references.donot_mail_ind := x_donot_mail_ind;
51 IF (p_action = 'UPDATE') THEN
52 new_references.creation_date := old_references.creation_date;
53 new_references.created_by := old_references.created_by;
54 ELSE
55 new_references.creation_date := x_creation_date;
56 new_references.created_by := x_created_by;
57 END IF;
58 new_references.last_update_date := x_last_update_date;
59 new_references.last_updated_by := x_last_updated_by;
60 new_references.last_update_login := x_last_update_login;
61
62 END Set_Column_Values;
63
64 PROCEDURE BeforeRowInsertUpdateDelete1(
65 p_inserting IN BOOLEAN DEFAULT FALSE,
66 p_updating IN BOOLEAN DEFAULT FALSE,
67 p_deleting IN BOOLEAN DEFAULT FALSE
68 ) as
69 CURSOR c_deceased(cp_party_id igs_pe_hz_parties.party_id%TYPE) IS
70 SELECT deceased_ind
71 FROM igs_pe_hz_parties
72 WHERE party_id = cp_party_id;
73 v_deceased_ind igs_pe_hz_parties.deceased_ind%TYPE;
74 v_message_name varchar2(30);
75 BEGIN
76 -- Fetch the Deceased Indicator
77 IF p_inserting OR p_updating THEN
78 OPEN c_deceased(new_references.person_id);
79 FETCH c_deceased INTO v_deceased_ind;
80 CLOSE c_deceased;
81 ELSIF p_deleting THEN
82 OPEN c_deceased(old_references.person_id);
83 FETCH c_deceased INTO v_deceased_ind;
84 CLOSE c_deceased;
85 END IF;
86 -- Validate that inserts/updates/deletes are allowed if a person is deceased
87 -- Validate that the person is not deceased
88 IF v_deceased_ind = 'Y' THEN
89 Fnd_Message.Set_Name('IGS', 'IGS_IN_DEC_NO_INQ');
90 IGS_GE_MSG_STACK.ADD;
91 App_Exception.Raise_Exception;
92 END IF;
93 -- Validate that the item mailed date is not prior to the enquiry date.
94 IF p_inserting OR
95 (p_updating AND
96 (new_references.mailed_dt <> NVL(TRUNC(old_references.mailed_dt), new_references.mailed_dt - 1 ))) THEN
97 IF IGR_VAL_EAPMPI.admp_val_eapmpi_dt(new_references.person_id,
98 new_references.enquiry_appl_number,
99 new_references.mailed_dt,
100 v_message_name) = FALSE THEN
101 Fnd_Message.Set_Name('IGS', v_message_name);
102 IGS_GE_MSG_STACK.ADD;
103 App_Exception.Raise_Exception;
104 END IF;
105 END IF;
106
107
108 END BeforeRowInsertUpdateDelete1;
109
110 PROCEDURE Check_Parent_Existance as
111 BEGIN
112
113 IF (((old_references.person_id = new_references.person_id) AND
114 (old_references.enquiry_appl_number = new_references.enquiry_appl_number)) OR
115 ((new_references.person_id IS NULL) OR
116 (new_references.enquiry_appl_number IS NULL))) THEN
117 NULL;
118 ELSE
119 IF NOT(IGR_I_APPL_PKG.Get_PK_For_Validation (
120 new_references.person_id,
121 new_references.enquiry_appl_number
122 ))THEN
123 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
124 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ENQ_APPL'));
125 IGS_GE_MSG_STACK.ADD;
126 App_Exception.Raise_Exception;
127 END IF;
128 END IF;
129
130 IF (((old_references.PACKAGE_ITEM_ID = new_references.PACKAGE_ITEM_ID)) OR
131 ((new_references.PACKAGE_ITEM_ID IS NULL))) THEN
132 NULL;
133 ELSE
134 IF NOT(IGR_I_PKG_ITEM_PKG.Get_PK_For_Validation (
135 new_references.PACKAGE_ITEM_ID
136 ))THEN
137 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
138 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ENQ_PACKAGE_ITEM'));
139 IGS_GE_MSG_STACK.ADD;
140 App_Exception.Raise_Exception;
141 END IF;
142 END IF;
143
144 END Check_Parent_Existance;
145
146 PROCEDURE Check_Constraints (
147 Column_Name IN VARCHAR2 DEFAULT NULL,
148 Column_Value IN VARCHAR2 DEFAULT NULL
149 ) as
150 BEGIN
151 IF column_name is null then
152 NULL;
153 ELSIF upper(Column_name) = 'PACKAGE_ITEM_ID' then
154 new_references.PACKAGE_ITEM_ID:= column_value;
155 ELSIF upper(Column_name) = 'DONOT_MAIL_IND' then
156 new_references.PACKAGE_ITEM_ID:= column_value;
157 END IF;
158 IF upper(column_name) = 'PACKAGE_ITEM_ID' OR
159 column_name is null Then
160 IF new_references.PACKAGE_ITEM_ID <> UPPER(new_references.PACKAGE_ITEM_ID) Then
161 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
162 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ENQ_PACKAGE_ITEM'));
163 IGS_GE_MSG_STACK.ADD;
164 App_Exception.Raise_Exception;
165 END IF;
166 END IF;
167 IF upper(column_name) = 'DONOT_MAIL_IND' OR
168 column_name is null Then
169 IF new_references.donot_mail_ind IS NOT NULL THEN
170 IF new_references.donot_mail_ind NOT IN ('Y','N') THEN
171 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
172 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_MAIL_INF'));
173 IGS_GE_MSG_STACK.ADD;
174 App_Exception.Raise_Exception;
175 END IF;
176 END IF;
177 END IF;
178
179 END Check_Constraints;
180
181
182 FUNCTION Get_PK_For_Validation (
183 x_person_id IN NUMBER,
184 x_enquiry_appl_number IN NUMBER,
185 x_PACKAGE_ITEM_ID IN NUMBER
186 ) RETURN BOOLEAN AS
187 CURSOR cur_rowid IS
188 SELECT rowid
189 FROM IGR_I_A_PKGITM
190 WHERE person_id = x_person_id
191 AND enquiry_appl_number = x_enquiry_appl_number
192 AND PACKAGE_ITEM_ID = x_PACKAGE_ITEM_ID
193 FOR UPDATE NOWAIT;
194
195 lv_rowid cur_rowid%RowType;
196
197 BEGIN
198
199 Open cur_rowid;
200 Fetch cur_rowid INTO lv_rowid;
201 IF (cur_rowid%FOUND) THEN
202 Close cur_rowid;
203 Return (TRUE);
204 ELSE
205 Close cur_rowid;
206 Return (FALSE);
207 END IF;
208
209 END Get_PK_For_Validation;
210
211 PROCEDURE GET_FK_IGR_I_APPL (
212 x_person_id IN NUMBER,
213 x_enquiry_appl_number IN NUMBER
214 ) as
215
216 CURSOR cur_rowid IS
217 SELECT rowid
218 FROM IGR_I_A_PKGITM
219 WHERE person_id = x_person_id
220 AND enquiry_appl_number = x_enquiry_appl_number ;
221
222 lv_rowid cur_rowid%RowType;
223
224 BEGIN
225
226 Open cur_rowid;
227 Fetch cur_rowid INTO lv_rowid;
228 IF (cur_rowid%FOUND) THEN
229 Fnd_Message.Set_Name ('IGS', 'IGS_IN_EAPMPI_EAP_FK');
230 IGS_GE_MSG_STACK.ADD;
231 Close cur_rowid;
232 App_Exception.Raise_Exception;
233 Return;
234 END IF;
235 Close cur_rowid;
236
237 END GET_FK_IGR_I_APPL;
238
239
240 PROCEDURE Before_DML (
241 p_action IN VARCHAR2,
242 x_rowid IN VARCHAR2 DEFAULT NULL,
243 x_person_id IN NUMBER DEFAULT NULL,
244 x_enquiry_appl_number IN NUMBER DEFAULT NULL,
245 x_PACKAGE_ITEM_ID IN NUMBER DEFAULT NULL,
246 x_mailed_dt IN DATE DEFAULT NULL,
247 x_creation_date IN DATE DEFAULT NULL,
248 x_created_by IN NUMBER DEFAULT NULL,
249 x_last_update_date IN DATE DEFAULT NULL,
250 x_last_updated_by IN NUMBER DEFAULT NULL,
251 x_last_update_login IN NUMBER DEFAULT NULL,
252 x_donot_mail_ind IN VARCHAR2 DEFAULT NULL
253 ) as
254 BEGIN
255
256 Set_Column_Values (
257 p_action,
258 x_rowid,
259 x_person_id,
260 x_enquiry_appl_number,
261 x_PACKAGE_ITEM_ID,
262 x_mailed_dt,
263 x_creation_date,
264 x_created_by,
265 x_last_update_date,
266 x_last_updated_by,
267 x_last_update_login,
268 x_donot_mail_ind
269 );
270
271 IF (p_action = 'INSERT') THEN
272 -- Call all the procedures related to Before Insert.
273 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
274 IF Get_PK_For_Validation (
275 new_references.person_id ,
276 new_references.enquiry_appl_number,
277 new_references.PACKAGE_ITEM_ID
278
279 ) THEN
280 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
281 IGS_GE_MSG_STACK.ADD;
282 App_Exception.Raise_Exception;
283 END IF;
284
285 Check_Constraints;
286 Check_Parent_Existance;
287 ELSIF (p_action = 'UPDATE') THEN
288 -- Call all the procedures related to Before Update.
289 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
290 Check_Constraints;
291 Check_Parent_Existance;
292 ELSIF (p_action = 'DELETE') THEN
293 null;
294 ELSIF (p_action = 'VALIDATE_INSERT') THEN
295 IF Get_PK_For_Validation (
296 new_references.person_id ,
297 new_references.enquiry_appl_number,
298 new_references.PACKAGE_ITEM_ID
299
300 ) THEN
301 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
302 IGS_GE_MSG_STACK.ADD;
303 App_Exception.Raise_Exception;
304 END IF;
305 Check_Constraints;
306 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
307 Check_Constraints;
308 ELSIF (p_action = 'VALIDATE_DELETE') THEN
309 -- Call all the procedures related to Before Delete.
310 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
311 END IF;
312 END Before_DML;
313
314
315 procedure INSERT_ROW (
316 X_ROWID in out NOCOPY VARCHAR2,
317 X_PERSON_ID in NUMBER,
318 X_ENQUIRY_APPL_NUMBER in NUMBER,
319 X_PACKAGE_ITEM_ID in NUMBER,
320 X_MAILED_DT in DATE,
321 X_MODE in VARCHAR2 default 'R',
322 X_DONOT_MAIL_IND IN VARCHAR2 DEFAULT NULL,
323 X_ACTION IN VARCHAR2,
324 X_ret_status OUT NOCOPY VARCHAR2,
325 X_msg_data OUT NOCOPY VARCHAR2,
326 X_msg_count OUT NOCOPY NUMBER
327 ) as
328 cursor C is select ROWID from IGR_I_A_PKGITM
329 where PERSON_ID = X_PERSON_ID
330 and ENQUIRY_APPL_NUMBER = X_ENQUIRY_APPL_NUMBER
331 and PACKAGE_ITEM_ID = X_PACKAGE_ITEM_ID;
332 X_LAST_UPDATE_DATE DATE;
333 X_LAST_UPDATED_BY NUMBER;
334 X_LAST_UPDATE_LOGIN NUMBER;
335 X_REQUEST_ID NUMBER;
336 X_PROGRAM_ID NUMBER;
337 X_PROGRAM_APPLICATION_ID NUMBER;
338 X_PROGRAM_UPDATE_DATE DATE;
339
340 CURSOR cur_sales_lead_id (p_person_id igr_i_appl_v.person_id%TYPE,
341 p_enquiry_appl_number igr_i_appl_v.enquiry_appl_number%TYPE ) IS
342 SELECT sales_lead_id
343 FROM igr_i_appl_v
344 WHERE person_id = p_person_id
345 AND enquiry_appl_number = p_enquiry_appl_number ;
346
347 p_sales_lead_id igr_i_appl_v.sales_lead_id%TYPE;
348
349 begin
350 X_LAST_UPDATE_DATE := SYSDATE;
351 if(X_MODE = 'I') then
352 X_LAST_UPDATED_BY := 1;
353 X_LAST_UPDATE_LOGIN := 0;
354 elsif (X_MODE = 'R') then
355 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
356 if X_LAST_UPDATED_BY is NULL then
357 X_LAST_UPDATED_BY := -1;
358 end if;
359 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
360 if X_LAST_UPDATE_LOGIN is NULL then
361 X_LAST_UPDATE_LOGIN := -1;
362 end if;
363 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
364 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
365
366 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
367 if (X_REQUEST_ID = -1) then
368 X_REQUEST_ID := NULL;
369 X_PROGRAM_ID := NULL;
370 X_PROGRAM_APPLICATION_ID := NULL;
371 X_PROGRAM_UPDATE_DATE := NULL;
372 else
373 X_PROGRAM_UPDATE_DATE := SYSDATE;
374 end if;
375 else
376 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
377 IGS_GE_MSG_STACK.ADD;
378 app_exception.raise_exception;
379 end if;
380
381 Before_DML(
382 p_action=>'INSERT',
383 x_rowid=>X_ROWID,
384 x_enquiry_appl_number=>X_ENQUIRY_APPL_NUMBER,
385 x_PACKAGE_ITEM_ID=>X_PACKAGE_ITEM_ID,
386 x_mailed_dt=>X_MAILED_DT,
387 x_person_id=>X_PERSON_ID,
388 x_creation_date=>X_LAST_UPDATE_DATE,
389 x_created_by=>X_LAST_UPDATED_BY,
390 x_last_update_date=>X_LAST_UPDATE_DATE,
391 x_last_updated_by=>X_LAST_UPDATED_BY,
392 x_last_update_login=>X_LAST_UPDATE_LOGIN,
393 x_donot_mail_ind=>X_DONOT_MAIL_IND
394 );
395 insert into IGR_I_A_PKGITM (
396 PERSON_ID,
397 ENQUIRY_APPL_NUMBER,
398 PACKAGE_ITEM_ID,
399 MAILED_DT,
400 DONOT_MAIL_IND,
401 CREATION_DATE,
402 CREATED_BY,
403 LAST_UPDATE_DATE,
404 LAST_UPDATED_BY,
405 LAST_UPDATE_LOGIN,
406 REQUEST_ID,
407 PROGRAM_ID,
408 PROGRAM_APPLICATION_ID,
409 PROGRAM_UPDATE_DATE
410 ) values (
411 NEW_REFERENCES.PERSON_ID,
412 NEW_REFERENCES.ENQUIRY_APPL_NUMBER,
413 NEW_REFERENCES.PACKAGE_ITEM_ID,
414 NEW_REFERENCES.MAILED_DT,
415 NEW_REFERENCES.DONOT_MAIL_IND,
416 X_LAST_UPDATE_DATE,
417 X_LAST_UPDATED_BY,
418 X_LAST_UPDATE_DATE,
419 X_LAST_UPDATED_BY,
420 X_LAST_UPDATE_LOGIN,
421 X_REQUEST_ID,
422 X_PROGRAM_ID,
423 X_PROGRAM_APPLICATION_ID,
424 X_PROGRAM_UPDATE_DATE
425 );
426
427 open c;
428 fetch c into X_ROWID;
429 if (c%notfound) then
430 close c;
431 raise no_data_found;
432 end if;
433 close c;
434
435 OPEN cur_sales_lead_id(NEW_REFERENCES.PERSON_ID, NEW_REFERENCES.ENQUIRY_APPL_NUMBER);
436 FETCH cur_sales_lead_id INTO p_sales_lead_id ;
437 CLOSE cur_sales_lead_id;
438
439 Igr_in_jtf_interactions_pkg.start_int_and_act ( p_doc_ref => 'AMS_DELV',
440 p_person_id => NEW_REFERENCES.PERSON_ID,
441 p_sales_lead_id => p_sales_lead_id,
442 p_item_id => NEW_REFERENCES.PACKAGE_ITEM_ID,
443 p_doc_id => NEW_REFERENCES.PACKAGE_ITEM_ID,
444 p_action => X_ACTION ,
445 p_action_item => 'PACKAGE_ITEM',
446 p_ret_status => x_ret_status,
447 p_msg_data => x_msg_count,
448 p_msg_count => x_msg_count);
449
450
451 end INSERT_ROW;
452
453 procedure LOCK_ROW (
454 X_ROWID in VARCHAR2,
455 X_PERSON_ID in NUMBER,
456 X_ENQUIRY_APPL_NUMBER in NUMBER,
457 X_PACKAGE_ITEM_ID in NUMBER,
458 X_MAILED_DT in DATE,
459 X_DONOT_MAIL_IND IN VARCHAR2 DEFAULT NULL
460 ) as
461 cursor c1 is select
462 MAILED_DT,DONOT_MAIL_IND
463 from IGR_I_A_PKGITM
464 where ROWID = X_ROWID
465 for update nowait;
466 tlinfo c1%rowtype;
467
468 begin
469 open c1;
470 fetch c1 into tlinfo;
471 if (c1%notfound) then
472 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
473 IGS_GE_MSG_STACK.ADD;
474 close c1;
475 app_exception.raise_exception;
476 return;
477 end if;
478 close c1;
479
480 if ( ((TRUNC(tlinfo.MAILED_DT) = TRUNC(X_MAILED_DT)) OR ((tlinfo.MAILED_DT is null) AND (X_MAILED_DT is null))) AND
481 ((tlinfo.DONOT_MAIL_IND = X_DONOT_MAIL_IND) OR ((tlinfo.DONOT_MAIL_IND is null) AND (X_DONOT_MAIL_IND is null)))
482 ) then
483 null;
484 else
485 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
486 IGS_GE_MSG_STACK.ADD;
487 app_exception.raise_exception;
488 end if;
489 return;
490 end LOCK_ROW;
491
492 procedure UPDATE_ROW (
493 X_ROWID in VARCHAR2,
494 X_PERSON_ID in NUMBER,
495 X_ENQUIRY_APPL_NUMBER in NUMBER,
496 X_PACKAGE_ITEM_ID in NUMBER,
497 X_MAILED_DT in DATE,
498 X_MODE in VARCHAR2 default 'R',
499 X_DONOT_MAIL_IND IN VARCHAR2 DEFAULT NULL,
500 X_ACTION IN VARCHAR2,
501 X_ret_status OUT NOCOPY VARCHAR2,
502 X_msg_data OUT NOCOPY VARCHAR2,
503 X_msg_count OUT NOCOPY NUMBER
504
505 ) as
506 X_LAST_UPDATE_DATE DATE;
507 X_LAST_UPDATED_BY NUMBER;
508 X_LAST_UPDATE_LOGIN NUMBER;
509 X_REQUEST_ID NUMBER;
510 X_PROGRAM_ID NUMBER;
511 X_PROGRAM_APPLICATION_ID NUMBER;
512 X_PROGRAM_UPDATE_DATE DATE;
513 CURSOR cur_sales_lead_id (p_person_id igr_i_appl_v.person_id%TYPE,
514 p_enquiry_appl_number igr_i_appl_v.enquiry_appl_number%TYPE ) IS
515 SELECT sales_lead_id
516 FROM igr_i_appl_v
517 WHERE person_id = p_person_id
518 AND enquiry_appl_number = p_enquiry_appl_number ;
519
520 p_sales_lead_id igr_i_appl_v.sales_lead_id%TYPE;
521
522 begin
523 X_LAST_UPDATE_DATE := SYSDATE;
524 if(X_MODE = 'I') then
525 X_LAST_UPDATED_BY := 1;
526 X_LAST_UPDATE_LOGIN := 0;
527 elsif (X_MODE = 'R') then
528 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
529 if X_LAST_UPDATED_BY is NULL then
530 X_LAST_UPDATED_BY := -1;
531 end if;
532 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
533 if X_LAST_UPDATE_LOGIN is NULL then
534 X_LAST_UPDATE_LOGIN := -1;
535 end if;
536 else
537 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
538 IGS_GE_MSG_STACK.ADD;
539 app_exception.raise_exception;
540 end if;
541 if (X_MODE = 'R') then
542 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
543 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
544 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
545 if (X_REQUEST_ID = -1) then
546 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
547 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
548 X_PROGRAM_APPLICATION_ID :=
549 OLD_REFERENCES.PROGRAM_APPLICATION_ID;
550 X_PROGRAM_UPDATE_DATE :=
551 OLD_REFERENCES.PROGRAM_UPDATE_DATE;
552 else
553 X_PROGRAM_UPDATE_DATE := SYSDATE;
554 end if;
555 end if;
556
557 Before_DML(
558 p_action=>'UPDATE',
559 x_rowid=>X_ROWID,
560 x_enquiry_appl_number=>X_ENQUIRY_APPL_NUMBER,
561 x_PACKAGE_ITEM_ID=>X_PACKAGE_ITEM_ID,
562 x_mailed_dt=>X_MAILED_DT,
563 x_person_id=>X_PERSON_ID,
564 x_creation_date=>X_LAST_UPDATE_DATE,
565 x_created_by=>X_LAST_UPDATED_BY,
566 x_last_update_date=>X_LAST_UPDATE_DATE,
567 x_last_updated_by=>X_LAST_UPDATED_BY,
568 x_last_update_login=>X_LAST_UPDATE_LOGIN,
569 x_donot_mail_ind=>X_DONOT_MAIL_IND
570 );
571
572 update IGR_I_A_PKGITM set
573 MAILED_DT = NEW_REFERENCES.MAILED_DT,
574 DONOT_MAIL_IND = NEW_REFERENCES.DONOT_MAIL_IND,
575 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
576 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
577 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
578 REQUEST_ID = X_REQUEST_ID,
579 PROGRAM_ID = X_PROGRAM_ID,
580 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
581 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE
582 where ROWID = X_ROWID
583 ;
584 if (sql%notfound) then
585 raise no_data_found;
586 end if;
587 OPEN cur_sales_lead_id(NEW_REFERENCES.PERSON_ID, NEW_REFERENCES.ENQUIRY_APPL_NUMBER);
588 FETCH cur_sales_lead_id INTO p_sales_lead_id ;
589 CLOSE cur_sales_lead_id;
590
591 Igr_in_jtf_interactions_pkg.start_int_and_act ( p_doc_ref => 'AMS_DELV',
592 p_person_id => NEW_REFERENCES.PERSON_ID,
593 p_sales_lead_id => p_sales_lead_id,
594 p_item_id => NEW_REFERENCES.PACKAGE_ITEM_ID,
595 p_doc_id => NEW_REFERENCES.PACKAGE_ITEM_ID,
596 p_action => X_ACTION ,
597 p_action_item => 'PACKAGE_ITEM',
598 p_ret_status => x_ret_status,
599 p_msg_data => x_msg_count,
600 p_msg_count => x_msg_count);
601
602 end UPDATE_ROW;
603
604 procedure ADD_ROW (
605 X_ROWID in out NOCOPY VARCHAR2,
606 X_PERSON_ID in NUMBER,
607 X_ENQUIRY_APPL_NUMBER in NUMBER,
608 X_PACKAGE_ITEM_ID in NUMBER,
609 X_MAILED_DT in DATE,
610 X_MODE in VARCHAR2 default 'R',
611 X_DONOT_MAIL_IND IN VARCHAR2 DEFAULT NULL,
612 X_ACTION IN VARCHAR2,
613 X_ret_status OUT NOCOPY VARCHAR2,
614 X_msg_data OUT NOCOPY VARCHAR2,
615 X_msg_count OUT NOCOPY NUMBER
616
617 ) as
618 cursor c1 is select rowid from IGR_I_A_PKGITM
619 where PERSON_ID = X_PERSON_ID
620 and ENQUIRY_APPL_NUMBER = X_ENQUIRY_APPL_NUMBER
621 and PACKAGE_ITEM_ID = X_PACKAGE_ITEM_ID
622 ;
623
624 begin
625 open c1;
626 fetch c1 into X_ROWID;
627 if (c1%notfound) then
628 close c1;
629 INSERT_ROW (
630 X_ROWID,
631 X_PERSON_ID,
632 X_ENQUIRY_APPL_NUMBER,
633 X_PACKAGE_ITEM_ID,
634 X_MAILED_DT,
635 X_MODE,
636 X_DONOT_MAIL_IND,
637 X_ACTION ,
638 X_ret_status ,
639 X_msg_data ,
640 X_msg_count );
641 return;
642 end if;
643 close c1;
644 UPDATE_ROW (
645 X_ROWID,
646 X_PERSON_ID,
647 X_ENQUIRY_APPL_NUMBER,
648 X_PACKAGE_ITEM_ID,
649 X_MAILED_DT,
650 X_MODE,
651 X_DONOT_MAIL_IND,
652 X_ACTION,
653 X_ret_status ,
654 X_msg_data ,
655 X_msg_count );
656 end ADD_ROW;
657
658 procedure DELETE_ROW (
659 X_ROWID in VARCHAR2
660 ) as
661 begin
662 Before_DML(
663 p_action => 'DELETE',
664 x_rowid => X_ROWID
665 );
666 delete from IGR_I_A_PKGITM
667 where ROWID = X_ROWID;
668 if (sql%notfound) then
669 raise no_data_found;
670 end if;
671 end DELETE_ROW;
672
673 end IGR_I_A_PKGITM_PKG;