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