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