1 PACKAGE BODY igs_ps_usec_x_usec_pkg AS
2 /* $Header: IGSPI1CB.pls 115.7 2002/11/29 02:05:09 nsidana ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ps_usec_x_usec%RowType;
5 new_references igs_ps_usec_x_usec%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_unit_sec_cross_unit_sec_id IN NUMBER DEFAULT NULL,
11 x_parent_uoo_id IN NUMBER DEFAULT NULL,
12 x_child_uoo_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 : aiyer
22 Date Created By : 12/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_PS_USEC_X_USEC
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.unit_sec_cross_unit_sec_id := x_unit_sec_cross_unit_sec_id;
55 new_references.parent_uoo_id := x_parent_uoo_id;
56 new_references.child_uoo_id := x_child_uoo_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 : aiyer
75 Date Created By : 12/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
92
93
94 END Check_Constraints;
95
96 PROCEDURE Check_Uniqueness AS
97 /*************************************************************
98 Created By : aiyer
99 Date Created By : 12/May/2000
100 Purpose :
101 Know limitations, enhancements or remarks
102 Change History
103 Who When What
104
105 (reverse chronological order - newest change first)
106 ***************************************************************/
107
108 begin
109 IF Get_Uk_For_Validation (
110 new_references.child_uoo_id
111 ,new_references.parent_uoo_id
112 ) THEN
113 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
114 IGS_GE_MSG_STACK.ADD;
115 app_exception.raise_exception;
116 END IF;
117 END Check_Uniqueness ;
118 PROCEDURE Check_Parent_Existance AS
119 /*************************************************************
120 Created By : aiyer
121 Date Created By : 12/May/2000
122 Purpose :
123 Know limitations, enhancements or remarks
124 Change History
125 Who When What
126
127 (reverse chronological order - newest change first)
128 ***************************************************************/
129
130 BEGIN
131
132 IF (((old_references.child_uoo_id = new_references.child_uoo_id)) OR
133 ((new_references.child_uoo_id IS NULL))) THEN
134 NULL;
135 ELSIF NOT Igs_Ps_Unit_Ofr_Opt_Pkg.Get_UK_For_Validation (
136 new_references.child_uoo_id
137 ) THEN
138 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
139 IGS_GE_MSG_STACK.ADD;
140 App_Exception.Raise_Exception;
141 END IF;
142
143 IF (((old_references.parent_uoo_id = new_references.parent_uoo_id)) OR
144 ((new_references.parent_uoo_id IS NULL))) THEN
145 NULL;
146 ELSIF NOT Igs_Ps_Unit_Ofr_Opt_Pkg.Get_UK_For_Validation (
147 new_references.parent_uoo_id
148 ) THEN
149 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
150 IGS_GE_MSG_STACK.ADD;
151 App_Exception.Raise_Exception;
152 END IF;
153
154 END Check_Parent_Existance;
155
156 FUNCTION Get_PK_For_Validation (
157 x_unit_sec_cross_unit_sec_id IN NUMBER
158 ) RETURN BOOLEAN AS
159
160 /*************************************************************
161 Created By : aiyer
162 Date Created By : 12/May/2000
163 Purpose :
164 Know limitations, enhancements or remarks
165 Change History
166 Who When What
167
168 (reverse chronological order - newest change first)
169 ***************************************************************/
170
171 CURSOR cur_rowid IS
172 SELECT rowid
173 FROM igs_ps_usec_x_usec
174 WHERE unit_sec_cross_unit_sec_id = x_unit_sec_cross_unit_sec_id
175 FOR UPDATE NOWAIT;
176
177 lv_rowid cur_rowid%RowType;
178
179 BEGIN
180
181 Open cur_rowid;
182 Fetch cur_rowid INTO lv_rowid;
183 IF (cur_rowid%FOUND) THEN
184 Close cur_rowid;
185 Return(TRUE);
186 ELSE
187 Close cur_rowid;
188 Return(FALSE);
189 END IF;
190 END Get_PK_For_Validation;
191
192 FUNCTION Get_UK_For_Validation (
193 x_child_uoo_id IN NUMBER,
194 x_parent_uoo_id IN NUMBER
195 ) RETURN BOOLEAN AS
196
197 /*************************************************************
198 Created By : aiyer
199 Date Created By : 12/May/2000
200 Purpose :
201 Know limitations, enhancements or remarks
202 Change History
203 Who When What
204
205 (reverse chronological order - newest change first)
206 ***************************************************************/
207
208 CURSOR cur_rowid IS
209 SELECT rowid
210 FROM igs_ps_usec_x_usec
211 WHERE child_uoo_id = x_child_uoo_id
212 AND parent_uoo_id = x_parent_uoo_id and ((l_rowid is null) or (rowid <> l_rowid))
213
214 ;
215 lv_rowid cur_rowid%RowType;
216
217 BEGIN
218
219 Open cur_rowid;
220 Fetch cur_rowid INTO lv_rowid;
221 IF (cur_rowid%FOUND) THEN
222 Close cur_rowid;
223 return (true);
224 ELSE
225 close cur_rowid;
226 return(false);
227 END IF;
228 END Get_UK_For_Validation ;
229
230 PROCEDURE Get_UFK_Igs_Ps_Unit_Ofr_Opt (
231 x_uoo_id IN NUMBER
232 ) AS
233
234 /*************************************************************
235 Created By : aiyer
236 Date Created By : 12/May/2000
237 Purpose :
238 Know limitations, enhancements or remarks
239 Change History
240 Who When What
241
242 (reverse chronological order - newest change first)
243 ***************************************************************/
244
245 CURSOR cur_rowid IS
246 SELECT rowid
247 FROM igs_ps_usec_x_usec
248 WHERE ( child_uoo_id = x_uoo_id
249 OR parent_uoo_id = x_uoo_id) ;
250
251 /************************************************
252 The above select statement has been modified
253 due to the presence of two foreign keys which
254 refer to the same column in a master table.
255 *************************************************/
256
257 lv_rowid cur_rowid%RowType;
258
259 BEGIN
260
261 Open cur_rowid;
262 Fetch cur_rowid INTO lv_rowid;
263 IF (cur_rowid%FOUND) THEN
264 Close cur_rowid;
265 Fnd_Message.Set_Name ('IGS', 'IGS_PS_USXUS_UNOO_UFK');
266 IGS_GE_MSG_STACK.ADD;
267 App_Exception.Raise_Exception;
268 Return;
269 END IF;
270 Close cur_rowid;
271
272 END Get_UFK_Igs_Ps_Unit_Ofr_Opt;
273
274 PROCEDURE Before_DML (
275 p_action IN VARCHAR2,
276 x_rowid IN VARCHAR2 DEFAULT NULL,
277 x_unit_sec_cross_unit_sec_id IN NUMBER DEFAULT NULL,
278 x_parent_uoo_id IN NUMBER DEFAULT NULL,
279 x_child_uoo_id IN NUMBER DEFAULT NULL,
280 x_creation_date IN DATE DEFAULT NULL,
281 x_created_by IN NUMBER DEFAULT NULL,
282 x_last_update_date IN DATE DEFAULT NULL,
283 x_last_updated_by IN NUMBER DEFAULT NULL,
284 x_last_update_login IN NUMBER DEFAULT NULL
285 ) AS
286 /*************************************************************
287 Created By : aiyer
288 Date Created By : 12/May/2000
289 Purpose :
290 Know limitations, enhancements or remarks
291 Change History
292 Who When What
293
294 (reverse chronological order - newest change first)
295 ***************************************************************/
296
297 BEGIN
298
299 Set_Column_Values (
300 p_action,
301 x_rowid,
302 x_unit_sec_cross_unit_sec_id,
303 x_parent_uoo_id,
304 x_child_uoo_id,
305 x_creation_date,
306 x_created_by,
307 x_last_update_date,
308 x_last_updated_by,
309 x_last_update_login
310 );
311
312 IF (p_action = 'INSERT') THEN
313 -- Call all the procedures related to Before Insert.
314 Null;
315 IF Get_Pk_For_Validation(
316 new_references.unit_sec_cross_unit_sec_id) THEN
317 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
318 IGS_GE_MSG_STACK.ADD;
319 App_Exception.Raise_Exception;
320 END IF;
321 Check_Uniqueness;
322 Check_Constraints;
323 Check_Parent_Existance;
324 ELSIF (p_action = 'UPDATE') THEN
325 -- Call all the procedures related to Before Update.
326 Null;
327 Check_Uniqueness;
328 Check_Constraints;
329 Check_Parent_Existance;
330 ELSIF (p_action = 'DELETE') THEN
331 -- Call all the procedures related to Before Delete.
332 Null;
333 ELSIF (p_action = 'VALIDATE_INSERT') THEN
334 -- Call all the procedures related to Before Insert.
335 IF Get_PK_For_Validation (
336 new_references.unit_sec_cross_unit_sec_id) THEN
337 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
338 IGS_GE_MSG_STACK.ADD;
339 App_Exception.Raise_Exception;
340 END IF;
341 Check_Uniqueness;
342 Check_Constraints;
343 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
344 Check_Uniqueness;
345 Check_Constraints;
346 ELSIF (p_action = 'VALIDATE_DELETE') THEN
347 Null;
348 END IF;
349
350 END Before_DML;
351
352 PROCEDURE After_DML (
353 p_action IN VARCHAR2,
354 x_rowid IN VARCHAR2
355 ) IS
356 /*************************************************************
357 Created By : aiyer
358 Date Created By : 12/May/2000
359 Purpose :
360 Know limitations, enhancements or remarks
361 Change History
362 Who When What
363
364 (reverse chronological order - newest change first)
365 ***************************************************************/
366
367 BEGIN
368
369 l_rowid := x_rowid;
370
371 IF (p_action = 'INSERT') THEN
372 -- Call all the procedures related to After Insert.
373 Null;
374 ELSIF (p_action = 'UPDATE') THEN
375 -- Call all the procedures related to After Update.
376 Null;
377 ELSIF (p_action = 'DELETE') THEN
378 -- Call all the procedures related to After Delete.
379 Null;
380 END IF;
381
382 l_rowid := null;
383 END After_DML;
384
385 procedure INSERT_ROW (
386 X_ROWID in out NOCOPY VARCHAR2,
387 x_UNIT_SEC_CROSS_UNIT_SEC_ID IN OUT NOCOPY NUMBER,
388 x_PARENT_UOO_ID IN NUMBER,
389 x_CHILD_UOO_ID IN NUMBER,
390 X_MODE in VARCHAR2 default 'R'
391 ) AS
392 /*************************************************************
393 Created By : aiyer
394 Date Created By : 12/May/2000
395 Purpose :
396 Know limitations, enhancements or remarks
397 Change History
398 Who When What
399
400 (reverse chronological order - newest change first)
401 ***************************************************************/
402
403 cursor C is select ROWID from IGS_PS_USEC_X_USEC
404 where UNIT_SEC_CROSS_UNIT_SEC_ID= X_UNIT_SEC_CROSS_UNIT_SEC_ID
405 ;
406 X_LAST_UPDATE_DATE DATE ;
407 X_LAST_UPDATED_BY NUMBER ;
408 X_LAST_UPDATE_LOGIN NUMBER ;
409 begin
410 X_LAST_UPDATE_DATE := SYSDATE;
411 if(X_MODE = 'I') then
412 X_LAST_UPDATED_BY := 1;
413 X_LAST_UPDATE_LOGIN := 0;
414 elsif (X_MODE = 'R') then
415 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
416 if X_LAST_UPDATED_BY is NULL then
417 X_LAST_UPDATED_BY := -1;
418 end if;
419 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
420 if X_LAST_UPDATE_LOGIN is NULL then
421 X_LAST_UPDATE_LOGIN := -1;
422 end if;
423 else
424 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
425 IGS_GE_MSG_STACK.ADD;
426 app_exception.raise_exception;
427 end if;
428
429
430 SELECT
431 IGS_PS_USEC_X_USEC_S.NEXTVAL
432 INTO
433 x_UNIT_SEC_CROSS_UNIT_SEC_ID
434 FROM
435 Dual;
436
437 Before_DML(
438 p_action=>'INSERT',
439 x_rowid=>X_ROWID,
440 x_unit_sec_cross_unit_sec_id=>X_UNIT_SEC_CROSS_UNIT_SEC_ID,
441 x_parent_uoo_id=>X_PARENT_UOO_ID,
442 x_child_uoo_id=>X_CHILD_UOO_ID,
443 x_creation_date=>X_LAST_UPDATE_DATE,
444 x_created_by=>X_LAST_UPDATED_BY,
445 x_last_update_date=>X_LAST_UPDATE_DATE,
446 x_last_updated_by=>X_LAST_UPDATED_BY,
447 x_last_update_login=>X_LAST_UPDATE_LOGIN);
448 insert into IGS_PS_USEC_X_USEC (
449 UNIT_SEC_CROSS_UNIT_SEC_ID
450 ,PARENT_UOO_ID
451 ,CHILD_UOO_ID
452 ,CREATION_DATE
453 ,CREATED_BY
454 ,LAST_UPDATE_DATE
455 ,LAST_UPDATED_BY
456 ,LAST_UPDATE_LOGIN
457 ) values (
458 NEW_REFERENCES.UNIT_SEC_CROSS_UNIT_SEC_ID
459 ,NEW_REFERENCES.PARENT_UOO_ID
460 ,NEW_REFERENCES.CHILD_UOO_ID
461 ,X_LAST_UPDATE_DATE
462 ,X_LAST_UPDATED_BY
463 ,X_LAST_UPDATE_DATE
464 ,X_LAST_UPDATED_BY
465 ,X_LAST_UPDATE_LOGIN
466 );
467 open c;
468 fetch c into X_ROWID;
469 if (c%notfound) then
470 close c;
471 raise no_data_found;
472 end if;
473 close c;
474 After_DML (
475 p_action => 'INSERT' ,
476 x_rowid => X_ROWID );
477 end INSERT_ROW;
478 procedure LOCK_ROW (
479 X_ROWID in VARCHAR2,
480 x_UNIT_SEC_CROSS_UNIT_SEC_ID IN NUMBER,
481 x_PARENT_UOO_ID IN NUMBER,
482 x_CHILD_UOO_ID IN NUMBER ) AS
483 /*************************************************************
484 Created By : aiyer
485 Date Created By : 12/May/2000
486 Purpose :
487 Know limitations, enhancements or remarks
488 Change History
489 Who When What
490
491 (reverse chronological order - newest change first)
492 ***************************************************************/
493
494 cursor c1 is select
495 PARENT_UOO_ID
496 , CHILD_UOO_ID
497 from IGS_PS_USEC_X_USEC
498 where ROWID = X_ROWID
499 for update nowait;
500 tlinfo c1%rowtype;
501 begin
502 open c1;
503 fetch c1 into tlinfo;
504 if (c1%notfound) then
505 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
506 IGS_GE_MSG_STACK.ADD;
507 close c1;
508 app_exception.raise_exception;
509 return;
510 end if;
511 close c1;
512 if ( ( tlinfo.PARENT_UOO_ID = X_PARENT_UOO_ID)
513 AND (tlinfo.CHILD_UOO_ID = X_CHILD_UOO_ID)
514 ) then
515 null;
516 else
517 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
518 IGS_GE_MSG_STACK.ADD;
519 app_exception.raise_exception;
520 end if;
521 return;
522 end LOCK_ROW;
523 Procedure UPDATE_ROW (
524 X_ROWID in VARCHAR2,
525 x_UNIT_SEC_CROSS_UNIT_SEC_ID IN NUMBER,
526 x_PARENT_UOO_ID IN NUMBER,
527 x_CHILD_UOO_ID IN NUMBER,
528 X_MODE in VARCHAR2 default 'R'
529 ) AS
530 /*************************************************************
531 Created By : aiyer
532 Date Created By : 12/May/2000
533 Purpose :
534 Know limitations, enhancements or remarks
535 Change History
536 Who When What
537
538 (reverse chronological order - newest change first)
539 ***************************************************************/
540
541 X_LAST_UPDATE_DATE DATE ;
542 X_LAST_UPDATED_BY NUMBER ;
543 X_LAST_UPDATE_LOGIN NUMBER ;
544 begin
545 X_LAST_UPDATE_DATE := SYSDATE;
546 if(X_MODE = 'I') then
547 X_LAST_UPDATED_BY := 1;
548 X_LAST_UPDATE_LOGIN := 0;
549 elsif (X_MODE = 'R') then
550 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
551 if X_LAST_UPDATED_BY is NULL then
552 X_LAST_UPDATED_BY := -1;
553 end if;
554 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
555 if X_LAST_UPDATE_LOGIN is NULL then
556 X_LAST_UPDATE_LOGIN := -1;
557 end if;
558 else
559 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
560 IGS_GE_MSG_STACK.ADD;
561 app_exception.raise_exception;
562 end if;
563 Before_DML(
564 p_action=>'UPDATE',
565 x_rowid=>X_ROWID,
566 x_unit_sec_cross_unit_sec_id=>X_UNIT_SEC_CROSS_UNIT_SEC_ID,
567 x_parent_uoo_id=>X_PARENT_UOO_ID,
568 x_child_uoo_id=>X_CHILD_UOO_ID,
569 x_creation_date=>X_LAST_UPDATE_DATE,
570 x_created_by=>X_LAST_UPDATED_BY,
571 x_last_update_date=>X_LAST_UPDATE_DATE,
572 x_last_updated_by=>X_LAST_UPDATED_BY,
573 x_last_update_login=>X_LAST_UPDATE_LOGIN);
574 update IGS_PS_USEC_X_USEC set
575 PARENT_UOO_ID = NEW_REFERENCES.PARENT_UOO_ID,
576 CHILD_UOO_ID = NEW_REFERENCES.CHILD_UOO_ID,
577 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
578 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
579 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
580 where ROWID = X_ROWID;
581 if (sql%notfound) then
582 raise no_data_found;
583 end if;
584
585 After_DML (
586 p_action => 'UPDATE' ,
587 x_rowid => X_ROWID
588 );
589 end UPDATE_ROW;
590 procedure ADD_ROW (
591 X_ROWID in out NOCOPY VARCHAR2,
592 x_UNIT_SEC_CROSS_UNIT_SEC_ID IN OUT NOCOPY NUMBER,
593 x_PARENT_UOO_ID IN NUMBER,
594 x_CHILD_UOO_ID IN NUMBER,
595 X_MODE in VARCHAR2 default 'R'
596 ) AS
597 /*************************************************************
598 Created By : aiyer
599 Date Created By : 12/May/2000
600 Purpose :
601 Know limitations, enhancements or remarks
602 Change History
603 Who When What
604
605 (reverse chronological order - newest change first)
606 ***************************************************************/
607
608 cursor c1 is select ROWID from IGS_PS_USEC_X_USEC
609 where UNIT_SEC_CROSS_UNIT_SEC_ID= X_UNIT_SEC_CROSS_UNIT_SEC_ID
610 ;
611 begin
612 open c1;
613 fetch c1 into X_ROWID;
614 if (c1%notfound) then
615 close c1;
616 INSERT_ROW (
617 X_ROWID,
618 X_UNIT_SEC_CROSS_UNIT_SEC_ID,
619 X_PARENT_UOO_ID,
620 X_CHILD_UOO_ID,
621 X_MODE );
622 return;
623 end if;
624 close c1;
625 UPDATE_ROW (
626 X_ROWID,
627 X_UNIT_SEC_CROSS_UNIT_SEC_ID,
628 X_PARENT_UOO_ID,
629 X_CHILD_UOO_ID,
630 X_MODE );
631 end ADD_ROW;
632 procedure DELETE_ROW (
633 X_ROWID in VARCHAR2
634 ) AS
635 /*************************************************************
636 Created By : aiyer
637 Date Created By : 12/May/2000
638 Purpose :
639 Know limitations, enhancements or remarks
640 Change History
641 Who When What
642
643 (reverse chronological order - newest change first)
644 ***************************************************************/
645
646 begin
647 Before_DML (
648 p_action => 'DELETE',
649 x_rowid => X_ROWID
650 );
651 delete from IGS_PS_USEC_X_USEC
652 where ROWID = X_ROWID;
653 if (sql%notfound) then
654 raise no_data_found;
655 end if;
656 After_DML (
657 p_action => 'DELETE',
658 x_rowid => X_ROWID
659 );
660 end DELETE_ROW;
661 END igs_ps_usec_x_usec_pkg;
[Home] [Help]