[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_UNIT_REF_CD_PKG
Source
1 package body IGS_PS_UNIT_REF_CD_PKG as
2 /* $Header: IGSPI88B.pls 115.10 2003/05/09 06:51:56 sarakshi ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PS_UNIT_REF_CD%RowType;
6 new_references IGS_PS_UNIT_REF_CD%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_unit_cd IN VARCHAR2 DEFAULT NULL,
12 x_version_number IN NUMBER DEFAULT NULL,
13 x_reference_cd_type IN VARCHAR2 DEFAULT NULL,
14 x_reference_cd IN VARCHAR2 DEFAULT NULL,
15 x_description IN VARCHAR2 DEFAULT NULL,
16 x_creation_date IN DATE DEFAULT NULL,
17 x_created_by IN NUMBER DEFAULT NULL,
18 x_last_update_date IN DATE DEFAULT NULL,
19 x_last_updated_by IN NUMBER DEFAULT NULL,
20 x_last_update_login IN NUMBER DEFAULT NULL
21 ) AS
22
23 CURSOR cur_old_ref_values IS
24 SELECT *
25 FROM IGS_PS_UNIT_REF_CD
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 Close cur_old_ref_values;
38 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
39 IGS_GE_MSG_STACK.ADD;
40 App_Exception.Raise_Exception;
41 Return;
42 END IF;
43 Close cur_old_ref_values;
44
45 -- Populate New Values.
46 new_references.unit_cd := x_unit_cd;
47 new_references.version_number := x_version_number;
48 new_references.reference_cd_type := x_reference_cd_type;
49 new_references.reference_cd := x_reference_cd;
50 new_references.description := x_description;
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 v_unit_cd IGS_PS_UNIT_REF_CD.unit_cd%TYPE;
70 v_version_number IGS_PS_UNIT_REF_CD.version_number%TYPE;
71 v_description IGS_PS_UNIT_REF_CD.description%TYPE;
72 v_message_name Varchar2(20);
73 BEGIN
74 -- Set variables.
75 IF p_deleting THEN
76 v_unit_cd := old_references.unit_cd;
77 v_version_number := old_references.version_number;
78 ELSE -- p_inserting or p_updating
79 v_unit_cd := new_references.unit_cd;
80 v_version_number := new_references.version_number;
81 END IF;
82 -- Validate the insert/update/delete.
83 IF IGS_PS_VAL_UNIT.crsp_val_iud_uv_dtl (
84 v_unit_cd,
85 v_version_number,
86 v_message_name) = FALSE THEN
87 Fnd_Message.Set_Name('IGS',v_message_name);
88 IGS_GE_MSG_STACK.ADD;
89 App_Exception.Raise_Exception;
90 END IF;
91 -- Validate reference code type. Referenece code type is not updateable.
92 IF p_inserting THEN
93 -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_URC.crsp_val_ref_cd_type
94 IF IGS_PS_VAL_CRFC.crsp_val_ref_cd_type(
95 new_references.reference_cd_type,
96 v_message_name) = FALSE THEN
97 Fnd_Message.Set_Name('IGS',v_message_name);
98 IGS_GE_MSG_STACK.ADD;
99 App_Exception.Raise_Exception;
100 END IF;
101 END IF;
102 -- Create history record.
103 IF p_updating THEN
104 IF NVL(old_references.description,'null') <> NVL(new_references.description,'null') THEN
105 SELECT DECODE(NVL(old_references.description,'NULL'),NVL(new_references.description,'NULL'),
106 NULL,old_references.description)
107 INTO v_description
108 FROM dual;
109 IGS_PS_GEN_005.CRSP_INS_URC_HIST(
110 old_references.unit_cd,
111 old_references.version_number,
112 old_references.reference_cd_type,
113 old_references.reference_cd,
114 old_references.last_update_date,
115 new_references.last_update_date,
116 old_references.last_updated_by,
117 v_description);
118 END IF;
119 END IF;
120 -- Create history record on delete
121 IF p_deleting THEN
122 IF igs_ps_val_atl.chk_mandatory_ref_cd(old_references.reference_cd_type) THEN
123 Fnd_Message.Set_Name ('IGS', 'IGS_PS_REF_CD_MANDATORY');
124 IGS_GE_MSG_STACK.ADD;
125 App_Exception.Raise_Exception;
126 END IF;
127 IGS_PS_GEN_005.CRSP_INS_URC_HIST(
128 old_references.unit_cd,
129 old_references.version_number,
130 old_references.reference_cd_type,
131 old_references.reference_cd,
132 old_references.last_update_date,
133 SYSDATE,
134 old_references.last_updated_by,
135 old_references.description);
136 END IF;
137
138
139 END BeforeRowInsertUpdateDelete1;
140
141 PROCEDURE Check_Constraints(
142 Column_Name IN VARCHAR2 DEFAULT NULL,
143 Column_Value IN VARCHAR2 DEFAULT NULL)
144 AS
145 BEGIN
146
147 IF Column_Name IS NULL Then
148 NULL;
149 ELSIF Upper(Column_Name)='REFERENCE_CD' Then
150 New_References.Reference_Cd := Column_Value;
151 ELSIF Upper(Column_Name)='REFERENCE_CD_TYPE' Then
152 New_References.Reference_Cd_Type := Column_Value;
153 ELSIF Upper(Column_Name)='UNIT_CD' Then
154 New_References.Unit_Cd := Column_Value;
155 END IF;
156
157 IF Upper(Column_Name)='REFERENCE_CD' OR Column_Name IS NULL Then
158 IF New_References.Reference_Cd <> UPPER(New_References.Reference_Cd) Then
159 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
160 IGS_GE_MSG_STACK.ADD;
161 App_Exception.Raise_Exception;
162 END IF;
163 END IF;
164
165 IF Upper(Column_Name)='REFERENCE_CD_TYPE' OR Column_Name IS NULL Then
166 IF New_References.Reference_Cd_Type <> UPPER(New_References.Reference_Cd_Type) Then
167 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
168 IGS_GE_MSG_STACK.ADD;
169 App_Exception.Raise_Exception;
170 END IF;
171 END IF;
172
173
174 IF Upper(Column_Name)='UNIT_CD' OR Column_Name IS NULL Then
175 IF New_References.Unit_Cd <> UPPER(New_References.Unit_CD) Then
176 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
177 IGS_GE_MSG_STACK.ADD;
178 App_Exception.Raise_Exception;
179 END IF;
180 END IF;
181
182 END Check_Constraints;
183
184 PROCEDURE Check_Uniqueness AS
185 BEGIN
186 IF Get_UK_For_Validation (
187 New_References.unit_cd,
188 New_References.version_number,
189 New_References.reference_cd_type) THEN
190 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
191 IGS_GE_MSG_STACK.ADD;
192 App_Exception.Raise_Exception;
193 END IF;
194 END Check_Uniqueness;
195
196
197 FUNCTION Get_UK_For_Validation (
198 x_unit_cd IN VARCHAR2,
199 x_version_number IN NUMBER,
200 x_reference_cd_type IN VARCHAR2
201 )RETURN BOOLEAN AS
202
203 CURSOR cur_rowid IS
204 SELECT rowid
205 FROM IGS_PS_UNIT_REF_CD
206 WHERE unit_cd = x_unit_cd
207 AND version_number = x_version_number
208 AND reference_cd_type = x_reference_cd_type
209 AND (l_rowid IS NULL OR rowid <> l_rowid)
210 FOR UPDATE NOWAIT;
211
212 lv_rowid cur_rowid%RowType;
213
214 BEGIN
215
216 Open cur_rowid;
217 Fetch cur_rowid INTO lv_rowid;
218 IF (cur_rowid%FOUND) THEN
219 Close cur_rowid;
220 Return(TRUE);
221 ELSE
222 Close cur_rowid;
223 Return(FALSE);
224 END IF;
225
226 END Get_UK_For_Validation;
227
228
229 PROCEDURE Check_Parent_Existance AS
230 /*************************************************************
231 Created By :
232 Date Created By :
233 Purpose :
234 Know limitations, enhancements or remarks
235 Change History
236 Who When What
237 smvk 31-Jan-2003 Bug # 2532094. Added the foreign key checking with igs_ge_ref_cd.
238 (reverse chronological order - newest change first)
239 ***************************************************************/
240
241 CURSOR cur_reference_cd_chk(cp_reference_cd_type igs_ge_ref_cd_type_all.reference_cd_type%TYPE) IS
242 SELECT 'X'
243 FROM igs_ge_ref_cd_type_all
244 WHERE restricted_flag='Y'
245 AND reference_cd_type=cp_reference_cd_type;
246 l_var VARCHAR2(1);
247
248 BEGIN
249
250 IF (((old_references.reference_cd_type = new_references.reference_cd_type)) OR
251 ((new_references.reference_cd_type IS NULL))) THEN
252 NULL;
253 ELSE
254 IF NOT IGS_GE_REF_CD_TYPE_PKG.Get_PK_For_Validation (
255 new_references.reference_cd_type) THEN
256 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
257 IGS_GE_MSG_STACK.ADD;
258 App_Exception.Raise_Exception;
259 END IF;
260
261
262 END IF;
263
264 IF (((old_references.unit_cd = new_references.unit_cd) AND
265 (old_references.version_number = new_references.version_number)) OR
266 ((new_references.unit_cd IS NULL) OR
267 (new_references.version_number IS NULL))) THEN
268 NULL;
269 ELSE
270 IF NOT IGS_PS_UNIT_VER_PKG.Get_PK_For_Validation (
271 new_references.unit_cd,
272 new_references.version_number) THEN
273 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
274 IGS_GE_MSG_STACK.ADD;
275 App_Exception.Raise_Exception;
276 END IF;
277
278
279 END IF;
280
281 OPEN cur_reference_cd_chk(new_references.reference_cd_type);
282 FETCH cur_reference_cd_chk INTO l_var;
283 IF cur_reference_cd_chk%FOUND THEN
284 IF (((old_references.reference_cd_type = new_references.reference_cd_type) AND
285 (old_references.reference_cd = new_references.reference_cd)) OR
286 ((new_references.reference_cd_type IS NULL) OR
287 (new_references.reference_cd IS NULL))) THEN
288 NULL;
289 ELSIF NOT igs_ge_ref_cd_pkg.get_uk_for_validation (
290 new_references.reference_cd_type,
291 new_references.reference_cd
292 ) THEN
293 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
294 IGS_GE_MSG_STACK.ADD;
295 App_Exception.Raise_Exception;
296 END IF;
297 END IF;
298 CLOSE cur_reference_cd_chk;
299
300 END Check_Parent_Existance;
301
302 FUNCTION Get_PK_For_Validation (
303 x_unit_cd IN VARCHAR2,
304 x_version_number IN NUMBER,
305 x_reference_cd_type IN VARCHAR2,
306 x_reference_cd IN VARCHAR2
307 ) RETURN BOOLEAN AS
308
309 CURSOR cur_rowid IS
310 SELECT rowid
311 FROM IGS_PS_UNIT_REF_CD
312 WHERE unit_cd = x_unit_cd
313 AND version_number = x_version_number
314 AND reference_cd_type = x_reference_cd_type
315 AND reference_cd = x_reference_cd
316 FOR UPDATE NOWAIT;
317
318 lv_rowid cur_rowid%RowType;
319
320 BEGIN
321
322 Open cur_rowid;
323 Fetch cur_rowid INTO lv_rowid;
324 IF (cur_rowid%FOUND) THEN
325 Close cur_rowid;
326 Return(TRUE);
327 ELSE
328 Close cur_rowid;
329 Return(FALSE);
330 END IF;
331
332 END Get_PK_For_Validation;
333
334 PROCEDURE GET_FK_IGS_GE_REF_CD_TYPE (
335 x_reference_cd_type IN VARCHAR2
336 ) AS
337
338 CURSOR cur_rowid IS
339 SELECT rowid
340 FROM IGS_PS_UNIT_REF_CD
341 WHERE reference_cd_type = x_reference_cd_type ;
342
343 lv_rowid cur_rowid%RowType;
344
345 BEGIN
346
347 Open cur_rowid;
348 Fetch cur_rowid INTO lv_rowid;
349 IF (cur_rowid%FOUND) THEN
350 Close cur_rowid;
351 Fnd_Message.Set_Name ('IGS', 'IGS_PS_URC_RCT_FK');
352 IGS_GE_MSG_STACK.ADD;
353 App_Exception.Raise_Exception;
354 Return;
355 END IF;
356 Close cur_rowid;
357
358 END GET_FK_IGS_GE_REF_CD_TYPE;
359
360 PROCEDURE get_ufk_igs_ge_ref_cd (
361 x_reference_cd_type IN VARCHAR2,
362 x_reference_cd IN VARCHAR2
363 ) AS
364
365 /*************************************************************
366 Created By :sarakshi
367 Date Created By :8-May-2003
368 Purpose :
369 Know limitations, enhancements or remarks
370 Change History
371 Who When What
372
373 (reverse chronological order - newest change first)
374 ***************************************************************/
375
376 CURSOR cur_rowid IS
377 SELECT ROWID
378 FROM igs_ps_unit_ref_cd
379 WHERE reference_cd_type = x_reference_cd_type
380 AND reference_cd = x_reference_cd ;
381
382 lv_rowid cur_rowid%ROWTYPE;
383
384 BEGIN
385
386 OPEN cur_rowid;
387 FETCH cur_rowid INTO lv_rowid;
388 IF (cur_rowid%FOUND) THEN
389 CLOSE cur_rowid;
390 fnd_message.set_name ('IGS', 'IGS_PS_URC_RC_FK');
391 igs_ge_msg_stack.add;
392 app_exception.raise_exception;
393 RETURN;
394 END IF;
395 CLOSE cur_rowid;
396
397 END get_ufk_igs_ge_ref_cd;
398
399
400 PROCEDURE GET_FK_IGS_PS_UNIT_VER (
401 x_unit_cd IN VARCHAR2,
402 x_version_number IN NUMBER
403 ) AS
404
405 CURSOR cur_rowid IS
406 SELECT rowid
407 FROM IGS_PS_UNIT_REF_CD
408 WHERE unit_cd = x_unit_cd
409 AND version_number = x_version_number ;
410
411 lv_rowid cur_rowid%RowType;
412
413 BEGIN
414
415 Open cur_rowid;
416 Fetch cur_rowid INTO lv_rowid;
417 IF (cur_rowid%FOUND) THEN
418 Close cur_rowid;
419 Fnd_Message.Set_Name ('IGS', 'IGS_PS_URC_UV_FK');
420 IGS_GE_MSG_STACK.ADD;
421 App_Exception.Raise_Exception;
422 Return;
423 END IF;
424 Close cur_rowid;
425
426 END GET_FK_IGS_PS_UNIT_VER;
427
428 PROCEDURE Before_DML (
429 p_action IN VARCHAR2,
430 x_rowid IN VARCHAR2 DEFAULT NULL,
431 x_unit_cd IN VARCHAR2 DEFAULT NULL,
432 x_version_number IN NUMBER DEFAULT NULL,
433 x_reference_cd_type IN VARCHAR2 DEFAULT NULL,
434 x_reference_cd IN VARCHAR2 DEFAULT NULL,
435 x_description IN VARCHAR2 DEFAULT NULL,
436 x_creation_date IN DATE DEFAULT NULL,
437 x_created_by IN NUMBER DEFAULT NULL,
438 x_last_update_date IN DATE DEFAULT NULL,
439 x_last_updated_by IN NUMBER DEFAULT NULL,
440 x_last_update_login IN NUMBER DEFAULT NULL
441 ) AS
442 BEGIN
443
444 Set_Column_Values (
445 p_action,
446 x_rowid,
447 x_unit_cd,
448 x_version_number,
449 x_reference_cd_type,
450 x_reference_cd,
451 x_description,
452 x_creation_date,
453 x_created_by,
454 x_last_update_date,
455 x_last_updated_by,
456 x_last_update_login
457 );
458
459 IF (p_action = 'INSERT') THEN
460 -- Call all the procedures related to Before Insert.
461 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
462 IF Get_PK_For_Validation (New_References.unit_cd,
463 New_References.version_number,
464 New_References.reference_cd_type,
465 New_References.reference_cd) THEN
466 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
467 IGS_GE_MSG_STACK.ADD;
468 App_Exception.Raise_Exception;
469 END IF;
470 Check_Constraints;
471 Check_Uniqueness;
472 Check_Parent_Existance;
473 ELSIF (p_action = 'UPDATE') THEN
474 -- Call all the procedures related to Before Update.
475 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
476 Check_Constraints;
477 Check_Uniqueness;
478 Check_Parent_Existance;
479 ELSIF (p_action = 'DELETE') THEN
480 -- Call all the procedures related to Before Delete.
481 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
482 ELSIF (p_action = 'VALIDATE_INSERT') THEN
483 IF Get_PK_For_Validation (New_References.unit_cd,
484 New_References.version_number,
485 New_References.reference_cd_type,
486 New_References.reference_cd) THEN
487 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
488 IGS_GE_MSG_STACK.ADD;
489 App_Exception.Raise_Exception;
490 END IF;
491 Check_Constraints;
492 Check_Uniqueness;
493 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
494 Check_Constraints;
495 Check_Uniqueness;
496
497 END IF;
498
499 l_rowid := NULL;
500 END Before_DML;
501
502 PROCEDURE After_DML (
503 p_action IN VARCHAR2,
504 x_rowid IN VARCHAR2
505 ) AS
506 BEGIN
507
508 l_rowid := x_rowid;
509
510 l_rowid:=NULL;
511 END After_DML;
512
513 procedure INSERT_ROW (
514 X_ROWID in out NOCOPY VARCHAR2,
515 X_UNIT_CD in VARCHAR2,
516 X_REFERENCE_CD_TYPE in VARCHAR2,
517 X_VERSION_NUMBER in NUMBER,
518 X_REFERENCE_CD in VARCHAR2,
519 X_DESCRIPTION in VARCHAR2,
520 X_MODE in VARCHAR2 default 'R'
521 ) AS
522 cursor C is select ROWID from IGS_PS_UNIT_REF_CD
523 where UNIT_CD = X_UNIT_CD
524 and REFERENCE_CD_TYPE = X_REFERENCE_CD_TYPE
525 and VERSION_NUMBER = X_VERSION_NUMBER
526 and REFERENCE_CD = X_REFERENCE_CD;
527 X_LAST_UPDATE_DATE DATE;
528 X_LAST_UPDATED_BY NUMBER;
529 X_LAST_UPDATE_LOGIN NUMBER;
530 begin
531 X_LAST_UPDATE_DATE := SYSDATE;
532 if(X_MODE = 'I') then
533 X_LAST_UPDATED_BY := 1;
534 X_LAST_UPDATE_LOGIN := 0;
535 elsif (X_MODE = 'R') then
536 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
537 if X_LAST_UPDATED_BY is NULL then
538 X_LAST_UPDATED_BY := -1;
539 end if;
540 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
541 if X_LAST_UPDATE_LOGIN is NULL then
542 X_LAST_UPDATE_LOGIN := -1;
543 end if;
544 else
545 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
546 IGS_GE_MSG_STACK.ADD;
547 app_exception.raise_exception;
548 end if;
549
550 Before_DML(
551 p_action => 'INSERT',
552 x_rowid => X_ROWID,
553 x_unit_cd => X_UNIT_CD,
554 x_version_number => X_VERSION_NUMBER,
555 x_reference_cd_type => X_REFERENCE_CD_TYPE,
556 x_reference_cd => X_REFERENCE_CD,
557 x_description => X_DESCRIPTION,
558 x_creation_date => X_LAST_UPDATE_DATE,
559 x_created_by => X_LAST_UPDATED_BY,
560 x_last_update_date => X_LAST_UPDATE_DATE,
561 x_last_updated_by => X_LAST_UPDATED_BY,
562 x_last_update_login => X_LAST_UPDATE_LOGIN
563 );
564
565 insert into IGS_PS_UNIT_REF_CD (
566 UNIT_CD,
567 VERSION_NUMBER,
568 REFERENCE_CD_TYPE,
569 REFERENCE_CD,
570 DESCRIPTION,
571 CREATION_DATE,
572 CREATED_BY,
573 LAST_UPDATE_DATE,
574 LAST_UPDATED_BY,
575 LAST_UPDATE_LOGIN
576 ) values (
577 NEW_REFERENCES.UNIT_CD,
578 NEW_REFERENCES.VERSION_NUMBER,
579 NEW_REFERENCES.REFERENCE_CD_TYPE,
580 NEW_REFERENCES.REFERENCE_CD,
581 NEW_REFERENCES.DESCRIPTION,
582 X_LAST_UPDATE_DATE,
583 X_LAST_UPDATED_BY,
584 X_LAST_UPDATE_DATE,
585 X_LAST_UPDATED_BY,
586 X_LAST_UPDATE_LOGIN
587 );
588
589 open c;
590 fetch c into X_ROWID;
591 if (c%notfound) then
592 close c;
593 raise no_data_found;
594 end if;
595 close c;
596 After_DML (
597 p_action => 'INSERT',
598 x_rowid => X_ROWID
599 );
600
601
602 end INSERT_ROW;
603
604 procedure LOCK_ROW (
605 X_ROWID in VARCHAR2,
606 X_UNIT_CD in VARCHAR2,
607 X_REFERENCE_CD_TYPE in VARCHAR2,
608 X_VERSION_NUMBER in NUMBER,
609 X_REFERENCE_CD in VARCHAR2,
610 X_DESCRIPTION in VARCHAR2
611 ) AS
612 cursor c1 is select
613 DESCRIPTION
614 from IGS_PS_UNIT_REF_CD
615 where ROWID = X_ROWID for update nowait;
616 tlinfo c1%rowtype;
617
618 begin
619 open c1;
620 fetch c1 into tlinfo;
621 if (c1%notfound) then
622 close c1;
623 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
624 IGS_GE_MSG_STACK.ADD;
625 app_exception.raise_exception;
626 return;
627 end if;
628 close c1;
629
630 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
631 OR ((tlinfo.DESCRIPTION is null)
632 AND (X_DESCRIPTION is null)))
633 ) then
634 null;
635 else
636 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
637 IGS_GE_MSG_STACK.ADD;
638 app_exception.raise_exception;
639 end if;
640 return;
641 end LOCK_ROW;
642
643 procedure UPDATE_ROW (
644 X_ROWID in VARCHAR2,
645 X_UNIT_CD in VARCHAR2,
646 X_REFERENCE_CD_TYPE in VARCHAR2,
647 X_VERSION_NUMBER in NUMBER,
648 X_REFERENCE_CD in VARCHAR2,
649 X_DESCRIPTION in VARCHAR2,
650 X_MODE in VARCHAR2 default 'R'
651 ) AS
652 X_LAST_UPDATE_DATE DATE;
653 X_LAST_UPDATED_BY NUMBER;
654 X_LAST_UPDATE_LOGIN NUMBER;
655 begin
656 X_LAST_UPDATE_DATE := SYSDATE;
657 if(X_MODE = 'I') then
658 X_LAST_UPDATED_BY := 1;
659 X_LAST_UPDATE_LOGIN := 0;
660 elsif (X_MODE = 'R') then
661 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
662 if X_LAST_UPDATED_BY is NULL then
663 X_LAST_UPDATED_BY := -1;
664 end if;
665 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
666 if X_LAST_UPDATE_LOGIN is NULL then
667 X_LAST_UPDATE_LOGIN := -1;
668 end if;
669 else
670 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
671 IGS_GE_MSG_STACK.ADD;
672 app_exception.raise_exception;
673 end if;
674
675 Before_DML(
676 p_action => 'UPDATE',
677 x_rowid => X_ROWID,
678 x_unit_cd => X_UNIT_CD,
679 x_version_number => X_VERSION_NUMBER,
680 x_reference_cd_type => X_REFERENCE_CD_TYPE,
681 x_reference_cd => X_REFERENCE_CD,
682 x_description => X_DESCRIPTION,
683 x_creation_date => X_LAST_UPDATE_DATE,
684 x_created_by => X_LAST_UPDATED_BY,
685 x_last_update_date => X_LAST_UPDATE_DATE,
686 x_last_updated_by => X_LAST_UPDATED_BY,
687 x_last_update_login => X_LAST_UPDATE_LOGIN
688 );
689
690 update IGS_PS_UNIT_REF_CD set
691 UNIT_CD = NEW_REFERENCES.UNIT_CD,
692 VERSION_NUMBER = NEW_REFERENCES.VERSION_NUMBER,
693 REFERENCE_CD_TYPE = NEW_REFERENCES.REFERENCE_CD_TYPE,
694 REFERENCE_CD= NEW_REFERENCES.REFERENCE_CD,
695 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
696 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
697 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
698 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
699 where ROWID = X_ROWID
700 ;
701 if (sql%notfound) then
702 raise no_data_found;
703 end if;
704 After_DML (
705 p_action => 'UPDATE',
706 x_rowid => X_ROWID
707 );
708 end UPDATE_ROW;
709
710 procedure ADD_ROW (
711 X_ROWID in out NOCOPY VARCHAR2,
712 X_UNIT_CD in VARCHAR2,
713 X_REFERENCE_CD_TYPE in VARCHAR2,
714 X_VERSION_NUMBER in NUMBER,
715 X_REFERENCE_CD in VARCHAR2,
716 X_DESCRIPTION in VARCHAR2,
717 X_MODE in VARCHAR2 default 'R'
718 ) AS
719 cursor c1 is select rowid from IGS_PS_UNIT_REF_CD
720 where UNIT_CD = X_UNIT_CD
721 and REFERENCE_CD_TYPE = X_REFERENCE_CD_TYPE
722 and VERSION_NUMBER = X_VERSION_NUMBER
723 and REFERENCE_CD = X_REFERENCE_CD
724 ;
725 begin
726 open c1;
727 fetch c1 into X_ROWID;
728 if (c1%notfound) then
729 close c1;
730 INSERT_ROW (
731 X_ROWID,
732 X_UNIT_CD,
733 X_REFERENCE_CD_TYPE,
734 X_VERSION_NUMBER,
735 X_REFERENCE_CD,
736 X_DESCRIPTION,
737 X_MODE);
738 return;
739 end if;
740 close c1;
741 UPDATE_ROW (
742 X_ROWID,
743 X_UNIT_CD,
744 X_REFERENCE_CD_TYPE,
745 X_VERSION_NUMBER,
746 X_REFERENCE_CD,
747 X_DESCRIPTION,
748 X_MODE);
749 end ADD_ROW;
750
751 procedure DELETE_ROW (
752 X_ROWID in VARCHAR2
753 ) AS
754 begin
755 Before_DML (
756 p_action => 'DELETE',
757 x_rowid => X_ROWID
758 );
759 delete from IGS_PS_UNIT_REF_CD
760 where ROWID = X_ROWID
761 ;
762 if (sql%notfound) then
763 raise no_data_found;
764 end if;
765 After_DML (
766 p_action => 'DELETE',
767 x_rowid => X_ROWID
768 );
769
770 end DELETE_ROW;
771
772 end IGS_PS_UNIT_REF_CD_PKG;