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