[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_UNT_OFR_OPT_N_PKG
Source
1 package body IGS_PS_UNT_OFR_OPT_N_PKG as
2 /* $Header: IGSPI84B.pls 115.5 2002/11/29 02:39:42 nsidana ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PS_UNT_OFR_OPT_N%RowType;
6 new_references IGS_PS_UNT_OFR_OPT_N%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_cal_type IN VARCHAR2 DEFAULT NULL,
14 x_ci_sequence_number IN NUMBER DEFAULT NULL,
15 x_location_cd IN VARCHAR2 DEFAULT NULL,
16 x_unit_class IN VARCHAR2 DEFAULT NULL,
17 x_reference_number IN NUMBER DEFAULT NULL,
18 x_uoo_id IN NUMBER DEFAULT NULL,
19 x_crs_note_type IN VARCHAR2 DEFAULT NULL,
20 x_creation_date IN DATE DEFAULT NULL,
21 x_created_by IN NUMBER DEFAULT NULL,
22 x_last_update_date IN DATE DEFAULT NULL,
23 x_last_updated_by IN NUMBER DEFAULT NULL,
24 x_last_update_login IN NUMBER DEFAULT NULL
25 ) AS
26
27 CURSOR cur_old_ref_values IS
28 SELECT *
29 FROM IGS_PS_UNT_OFR_OPT_N
30 WHERE rowid = x_rowid;
31
32 BEGIN
33
34 l_rowid := x_rowid;
35
36 -- Code for setting the Old and New Reference Values.
37 -- Populate Old Values.
38 Open cur_old_ref_values;
39 Fetch cur_old_ref_values INTO old_references;
40 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
41 Close cur_old_ref_values;
42 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
43 IGS_GE_MSG_STACK.ADD;
44 App_Exception.Raise_Exception;
45 Return;
46 END IF;
47 Close cur_old_ref_values;
48
49 -- Populate New Values.
50 new_references.unit_cd := x_unit_cd;
51 new_references.version_number := x_version_number;
52 new_references.cal_type := x_cal_type;
53 new_references.ci_sequence_number := x_ci_sequence_number;
54 new_references.location_cd := x_location_cd;
55 new_references.unit_class := x_unit_class;
56 new_references.reference_number := x_reference_number;
57 new_references.uoo_id := x_uoo_id;
58 new_references.crs_note_type := x_crs_note_type;
59 IF (p_action = 'UPDATE') THEN
60 new_references.creation_date := old_references.creation_date;
61 new_references.created_by := old_references.created_by;
62 ELSE
63 new_references.creation_date := x_creation_date;
64 new_references.created_by := x_created_by;
65 END IF;
66 new_references.last_update_date := x_last_update_date;
67 new_references.last_updated_by := x_last_updated_by;
68 new_references.last_update_login := x_last_update_login;
69
70 END Set_Column_Values;
71
72 PROCEDURE BeforeRowInsertUpdateDelete1(
73 p_inserting IN BOOLEAN DEFAULT FALSE,
74 p_updating IN BOOLEAN DEFAULT FALSE,
75 p_deleting IN BOOLEAN DEFAULT FALSE
76 ) AS
77 v_unit_cd IGS_PS_UNT_OFR_OPT_N.unit_cd%TYPE;
78 v_version_number IGS_PS_UNT_OFR_OPT_N.version_number%TYPE;
79 v_message_name Varchar2(30);
80 BEGIN
81 -- Set IGS_PS_UNIT_OFR_OPT key.
82 IF p_inserting THEN
83 IGS_PS_GEN_006.CRSP_GET_UOO_KEY (
84 new_references.unit_cd,
85 new_references.version_number,
86 new_references.cal_type,
87 new_references.ci_sequence_number,
88 new_references.location_cd,
89 new_references.unit_class,
90 new_references.uoo_id);
91 END IF;
92 -- Set variables.
93 IF p_deleting THEN
94 v_unit_cd := old_references.unit_cd;
95 v_version_number := old_references.version_number;
96 ELSE -- p_inserting or p_updating
97 v_unit_cd := new_references.unit_cd;
98 v_version_number := new_references.version_number;
99 END IF;
100 -- Validate the insert/update/delete.
101 IF IGS_PS_VAL_UNIT.crsp_val_iud_uv_dtl (
102 v_unit_cd,
103 v_version_number,
104 v_message_name) = FALSE THEN
105 Fnd_Message.Set_Name('IGS',v_message_name);
106 IGS_GE_MSG_STACK.ADD;
107 App_Exception.Raise_Exception;
108 END IF;
109
110
111 END BeforeRowInsertUpdateDelete1;
112
113 PROCEDURE Check_Constraints(
114 Column_Name IN VARCHAR2 DEFAULT NULL,
115 Column_Value IN VARCHAR2 DEFAULT NULL)
116 AS
117 BEGIN
118
119 IF Column_Name IS NULL Then
120 NULL;
121 ELSIF Upper(Column_Name)='CAL_TYPE' Then
122 New_References.Cal_Type := Column_Value;
123 ELSIF Upper(Column_Name)='CRS_NOTE_TYPE' Then
124 New_References.Crs_Note_Type := Column_Value;
125 ELSIF Upper(Column_Name)='LOCATION_CD' Then
126 New_References.Location_Cd := Column_Value;
127 ELSIF Upper(Column_Name)='UNIT_CD' Then
128 New_References.Unit_Cd := Column_Value;
129 ELSIF Upper(Column_Name)='UNIT_CLASS' Then
130 New_References.Unit_Class := Column_Value;
131 END IF;
132
133 IF Upper(Column_Name)='CAL_TYPE' OR Column_Name IS NULL Then
134 IF New_References.Cal_Type <> UPPER(New_References.Cal_Type) Then
135 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
136 IGS_GE_MSG_STACK.ADD;
137 App_Exception.Raise_Exception;
138 END IF;
139 END IF;
140
141 IF Upper(Column_Name)='CRS_NOTE_TYPE' OR Column_Name IS NULL Then
142 IF New_References.Crs_Note_Type <> UPPER(New_References.Crs_Note_Type) Then
143 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
144 IGS_GE_MSG_STACK.ADD;
145 App_Exception.Raise_Exception;
146 END IF;
147 END IF;
148
149
150 IF Upper(Column_Name)='LOCATION_CD' OR Column_Name IS NULL Then
151 IF New_References.Location_Cd <> UPPER(New_References.Location_Cd) Then
152 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
153 IGS_GE_MSG_STACK.ADD;
154 App_Exception.Raise_Exception;
155 END IF;
156 END IF;
157
158 IF Upper(Column_Name)='UNIT_CD' OR Column_Name IS NULL Then
159 IF New_References.Unit_Cd <> UPPER(New_References.Unit_CD) Then
160 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
161 IGS_GE_MSG_STACK.ADD;
162 App_Exception.Raise_Exception;
163 END IF;
164 END IF;
165
166 IF Upper(Column_Name)='UNIT_CLASS' OR Column_Name IS NULL Then
167 IF New_References.Unit_Class <> UPPER(New_References.Unit_Class) Then
168 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
169 IGS_GE_MSG_STACK.ADD;
170 App_Exception.Raise_Exception;
171 END IF;
172 END IF;
173
174
175 END Check_Constraints;
176
177
178 PROCEDURE Check_Parent_Existance AS
179 BEGIN
180
181 IF (((old_references.crs_note_type = new_references.crs_note_type)) OR
182 ((new_references.crs_note_type IS NULL))) THEN
183 NULL;
184 ELSE
185 IF NOT IGS_PS_NOTE_TYPE_PKG.Get_PK_For_Validation (
186 new_references.crs_note_type) THEN
187 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
188 IGS_GE_MSG_STACK.ADD;
189 App_Exception.Raise_Exception;
190 END IF;
191 END IF;
192
193 IF (((old_references.reference_number = new_references.reference_number)) OR
194 ((new_references.reference_number IS NULL))) THEN
195 NULL;
196 ELSE
197 IF NOT IGS_GE_NOTE_PKG.Get_PK_For_Validation (
198 new_references.reference_number) THEN
199 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
200 IGS_GE_MSG_STACK.ADD;
201 App_Exception.Raise_Exception;
202 END IF;
203
204 END IF;
205
206 IF (((old_references.unit_cd = new_references.unit_cd) AND
207 (old_references.version_number = new_references.version_number) AND
208 (old_references.cal_type = new_references.cal_type) AND
209 (old_references.ci_sequence_number = new_references.ci_sequence_number) AND
210 (old_references.location_cd = new_references.location_cd) AND
211 (old_references.unit_class = new_references.unit_class)) OR
212 ((new_references.unit_cd IS NULL) OR
213 (new_references.version_number IS NULL) OR
214 (new_references.cal_type IS NULL) OR
215 (new_references.ci_sequence_number IS NULL) OR
216 (new_references.location_cd IS NULL) OR
217 (new_references.unit_class IS NULL))) THEN
218 NULL;
219 ELSE
220 IF NOT IGS_PS_UNIT_OFR_OPT_PKG.Get_PK_For_Validation (
221 new_references.unit_cd,
222 new_references.version_number,
223 new_references.cal_type,
224 new_references.ci_sequence_number,
225 new_references.location_cd,
226 new_references.unit_class) THEN
227 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
228 IGS_GE_MSG_STACK.ADD;
229 App_Exception.Raise_Exception;
230 END IF;
231
232 END IF;
233
234 IF (((old_references.uoo_id = new_references.uoo_id)) OR
235 ((new_references.uoo_id IS NULL))) THEN
236 NULL;
237 ELSE
238 IF NOT IGS_PS_UNIT_OFR_OPT_PKG.Get_UK_For_Validation (
239 new_references.uoo_id) THEN
240 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
241 IGS_GE_MSG_STACK.ADD;
242 App_Exception.Raise_Exception;
243 END IF;
244 END IF;
245
246 END Check_Parent_Existance;
247
248 FUNCTION Get_PK_For_Validation (
249 x_unit_cd IN VARCHAR2,
250 x_version_number IN NUMBER,
251 x_cal_type IN VARCHAR2,
252 x_ci_sequence_number IN NUMBER,
253 x_location_cd IN VARCHAR2,
254 x_unit_class IN VARCHAR2,
255 x_reference_number IN NUMBER
256 ) RETURN BOOLEAN AS
257
258 CURSOR cur_rowid IS
259 SELECT rowid
260 FROM IGS_PS_UNT_OFR_OPT_N
261 WHERE unit_cd = x_unit_cd
262 AND version_number = x_version_number
263 AND cal_type = x_cal_type
264 AND ci_sequence_number = x_ci_sequence_number
265 AND location_cd = x_location_cd
266 AND unit_class = x_unit_class
267 AND reference_number = x_reference_number
268 FOR UPDATE NOWAIT;
269
270 lv_rowid cur_rowid%RowType;
271
272 BEGIN
273
274 Open cur_rowid;
275 Fetch cur_rowid INTO lv_rowid;
276 IF (cur_rowid%FOUND) THEN
277 Close cur_rowid;
278 Return(TRUE);
279 ELSE
280 Close cur_rowid;
281 Return(FALSE);
282 END IF;
283
284 END Get_PK_For_Validation;
285
286 PROCEDURE GET_FK_IGS_PS_NOTE_TYPE (
287 x_crs_note_type IN VARCHAR2
288 ) AS
289
290 CURSOR cur_rowid IS
291 SELECT rowid
292 FROM IGS_PS_UNT_OFR_OPT_N
293 WHERE crs_note_type = x_crs_note_type ;
294
295 lv_rowid cur_rowid%RowType;
296
297 BEGIN
298
299 Open cur_rowid;
300 Fetch cur_rowid INTO lv_rowid;
301 IF (cur_rowid%FOUND) THEN
302 Close cur_rowid;
303 Fnd_Message.Set_Name ('IGS', 'IGS_PS_UOON_CNT_FK');
304 IGS_GE_MSG_STACK.ADD;
305 App_Exception.Raise_Exception;
306 Return;
307 END IF;
308 Close cur_rowid;
309
310 END GET_FK_IGS_PS_NOTE_TYPE;
311
312 PROCEDURE GET_FK_IGS_GE_NOTE (
313 x_reference_number IN NUMBER
314 ) AS
315
316 CURSOR cur_rowid IS
317 SELECT rowid
318 FROM IGS_PS_UNT_OFR_OPT_N
319 WHERE reference_number = x_reference_number ;
320
321 lv_rowid cur_rowid%RowType;
322
323 BEGIN
324
325 Open cur_rowid;
326 Fetch cur_rowid INTO lv_rowid;
327 IF (cur_rowid%FOUND) THEN
328 Close cur_rowid;
329 Fnd_Message.Set_Name ('IGS', 'IGS_PS_UOON_NOTE_FK');
330 IGS_GE_MSG_STACK.ADD;
331 App_Exception.Raise_Exception;
332 Return;
333 END IF;
334 Close cur_rowid;
335
336 END GET_FK_IGS_GE_NOTE;
337
338 PROCEDURE GET_FK_IGS_PS_UNIT_OFR_OPT (
339 x_unit_cd IN VARCHAR2,
340 x_version_number IN NUMBER,
341 x_cal_type IN VARCHAR2,
342 x_ci_sequence_number IN NUMBER,
343 x_location_cd IN VARCHAR2,
344 x_unit_class IN VARCHAR2
345 ) AS
346
347 CURSOR cur_rowid IS
348 SELECT rowid
349 FROM IGS_PS_UNT_OFR_OPT_N
350 WHERE unit_cd = x_unit_cd
351 AND version_number = x_version_number
352 AND cal_type = x_cal_type
353 AND ci_sequence_number = x_ci_sequence_number
354 AND location_cd = x_location_cd
355 AND unit_class = x_unit_class ;
356
357 lv_rowid cur_rowid%RowType;
358
359 BEGIN
360
361 Open cur_rowid;
362 Fetch cur_rowid INTO lv_rowid;
363 IF (cur_rowid%FOUND) THEN
364 Close cur_rowid;
365 Fnd_Message.Set_Name ('IGS', 'IGS_PS_UOON_UOO_FK');
366 IGS_GE_MSG_STACK.ADD;
367 App_Exception.Raise_Exception;
368 Return;
369 END IF;
370 Close cur_rowid;
371
372 END GET_FK_IGS_PS_UNIT_OFR_OPT;
373
374 PROCEDURE GET_UFK_IGS_PS_UNIT_OFR_OPT (
375 x_uoo_id IN NUMBER
376 ) AS
377
378 CURSOR cur_rowid IS
379 SELECT rowid
380 FROM IGS_PS_UNT_OFR_OPT_N
381 WHERE uoo_id = x_uoo_id ;
382
383 lv_rowid cur_rowid%RowType;
384
385 BEGIN
386
387 Open cur_rowid;
388 Fetch cur_rowid INTO lv_rowid;
389 IF (cur_rowid%FOUND) THEN
390 Close cur_rowid;
391 Fnd_Message.Set_Name ('IGS', 'IGS_PS_UOON_UOO_FK');
392 IGS_GE_MSG_STACK.ADD;
393 App_Exception.Raise_Exception;
394 Return;
395 END IF;
396 Close cur_rowid;
397
398 END GET_UFK_IGS_PS_UNIT_OFR_OPT;
399
400 PROCEDURE Before_DML (
401 p_action IN VARCHAR2,
402 x_rowid IN VARCHAR2 DEFAULT NULL,
403 x_unit_cd IN VARCHAR2 DEFAULT NULL,
404 x_version_number IN NUMBER DEFAULT NULL,
405 x_cal_type IN VARCHAR2 DEFAULT NULL,
406 x_ci_sequence_number IN NUMBER DEFAULT NULL,
407 x_location_cd IN VARCHAR2 DEFAULT NULL,
408 x_unit_class IN VARCHAR2 DEFAULT NULL,
409 x_reference_number IN NUMBER DEFAULT NULL,
410 x_uoo_id IN NUMBER DEFAULT NULL,
411 x_crs_note_type IN VARCHAR2 DEFAULT NULL,
412 x_creation_date IN DATE DEFAULT NULL,
413 x_created_by IN NUMBER DEFAULT NULL,
414 x_last_update_date IN DATE DEFAULT NULL,
415 x_last_updated_by IN NUMBER DEFAULT NULL,
416 x_last_update_login IN NUMBER DEFAULT NULL
417 ) AS
418 BEGIN
419
420 Set_Column_Values (
421 p_action,
422 x_rowid,
423 x_unit_cd,
424 x_version_number,
425 x_cal_type,
426 x_ci_sequence_number,
427 x_location_cd,
428 x_unit_class,
429 x_reference_number,
430 x_uoo_id,
431 x_crs_note_type,
432 x_creation_date,
433 x_created_by,
434 x_last_update_date,
435 x_last_updated_by,
436 x_last_update_login
437 );
438
439 IF (p_action = 'INSERT') THEN
440 -- Call all the procedures related to Before Insert.
441 BeforeRowInsertUpdateDelete1 ( p_inserting => TRUE );
442 IF Get_PK_For_Validation (New_References.unit_cd,
443 New_References.version_number,
444 New_References.cal_type,
445 New_References.ci_sequence_number,
446 New_References.location_cd,
447 New_References.unit_class,
448 New_References.reference_number) THEN
449 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
450 IGS_GE_MSG_STACK.ADD;
451 App_Exception.Raise_Exception;
452 END IF;
453 Check_Constraints;
454 Check_Parent_Existance;
455 ELSIF (p_action = 'UPDATE') THEN
456 -- Call all the procedures related to Before Update.
457 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
458 Check_Constraints;
459 Check_Parent_Existance;
460 ELSIF (p_action = 'DELETE') THEN
461 -- Call all the procedures related to Before Delete.
462 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
463 ELSIF (p_action = 'VALIDATE_INSERT') THEN
464 IF Get_PK_For_Validation (New_References.unit_cd,
465 New_References.version_number,
466 New_References.cal_type,
467 New_References.ci_sequence_number,
468 New_References.location_cd,
469 New_References.unit_class,
470 New_References.reference_number) THEN
471 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
472 IGS_GE_MSG_STACK.ADD;
473 App_Exception.Raise_Exception;
474 END IF;
475 Check_Constraints;
476 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
477 Check_Constraints;
478
479 END IF;
480
481 END Before_DML;
482
483 PROCEDURE After_DML (
484 p_action IN VARCHAR2,
485 x_rowid IN VARCHAR2
486 ) AS
487 BEGIN
488
489 l_rowid := x_rowid;
490
491
492 END After_DML;
493
494 procedure INSERT_ROW (
495 X_ROWID in out NOCOPY VARCHAR2,
496 X_UNIT_CD in VARCHAR2,
497 X_VERSION_NUMBER in NUMBER,
498 X_CI_SEQUENCE_NUMBER in NUMBER,
499 X_UNIT_CLASS in VARCHAR2,
500 X_REFERENCE_NUMBER in NUMBER,
501 X_LOCATION_CD in VARCHAR2,
502 X_CAL_TYPE in VARCHAR2,
503 X_UOO_ID in out NOCOPY NUMBER,
504 X_CRS_NOTE_TYPE in VARCHAR2,
505 X_MODE in VARCHAR2 default 'R'
506 ) AS
507 cursor C is select ROWID from IGS_PS_UNT_OFR_OPT_N
508 where UNIT_CD = X_UNIT_CD
509 and VERSION_NUMBER = X_VERSION_NUMBER
510 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
511 and UNIT_CLASS = X_UNIT_CLASS
512 and REFERENCE_NUMBER = X_REFERENCE_NUMBER
513 and LOCATION_CD = X_LOCATION_CD
514 and CAL_TYPE = X_CAL_TYPE;
515 X_LAST_UPDATE_DATE DATE;
516 X_LAST_UPDATED_BY NUMBER;
517 X_LAST_UPDATE_LOGIN NUMBER;
518 begin
519 X_LAST_UPDATE_DATE := SYSDATE;
520 if(X_MODE = 'I') then
521 X_LAST_UPDATED_BY := 1;
522 X_LAST_UPDATE_LOGIN := 0;
523 elsif (X_MODE = 'R') then
524 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
525 if X_LAST_UPDATED_BY is NULL then
526 X_LAST_UPDATED_BY := -1;
527 end if;
528 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
529 if X_LAST_UPDATE_LOGIN is NULL then
530 X_LAST_UPDATE_LOGIN := -1;
531 end if;
532 else
533 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
534 IGS_GE_MSG_STACK.ADD;
535 app_exception.raise_exception;
536 end if;
537
538 Before_DML(
539 p_action => 'INSERT',
540 x_rowid => X_ROWID,
541 x_unit_cd => X_UNIT_CD,
542 x_version_number => X_VERSION_NUMBER,
543 x_cal_type => X_CAL_TYPE,
544 x_ci_sequence_number => X_CI_SEQUENCE_NUMBER,
545 x_location_cd => X_LOCATION_CD,
546 x_unit_class => X_UNIT_CLASS,
547 x_reference_number => X_REFERENCE_NUMBER,
548 x_uoo_id => X_UOO_ID,
549 x_crs_note_type => X_CRS_NOTE_TYPE,
550 x_creation_date => X_LAST_UPDATE_DATE,
551 x_created_by => X_LAST_UPDATED_BY,
552 x_last_update_date => X_LAST_UPDATE_DATE,
553 x_last_updated_by => X_LAST_UPDATED_BY,
554 x_last_update_login => X_LAST_UPDATE_LOGIN
555 );
556
557 insert into IGS_PS_UNT_OFR_OPT_N (
558 UNIT_CD,
559 VERSION_NUMBER,
560 CAL_TYPE,
561 CI_SEQUENCE_NUMBER,
562 LOCATION_CD,
563 UNIT_CLASS,
564 REFERENCE_NUMBER,
565 UOO_ID,
566 CRS_NOTE_TYPE,
567 CREATION_DATE,
568 CREATED_BY,
569 LAST_UPDATE_DATE,
570 LAST_UPDATED_BY,
571 LAST_UPDATE_LOGIN
572 ) values (
573 NEW_REFERENCES.UNIT_CD,
574 NEW_REFERENCES.VERSION_NUMBER,
575 NEW_REFERENCES.CAL_TYPE,
576 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
577 NEW_REFERENCES.LOCATION_CD,
578 NEW_REFERENCES.UNIT_CLASS,
579 NEW_REFERENCES.REFERENCE_NUMBER,
580 NEW_REFERENCES.UOO_ID,
581 NEW_REFERENCES.CRS_NOTE_TYPE,
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 x_uoo_id := new_references.uoo_id;
601
602 end INSERT_ROW;
603
604 procedure LOCK_ROW (
605 X_ROWID in VARCHAR2,
606 X_UNIT_CD in VARCHAR2,
607 X_VERSION_NUMBER in NUMBER,
608 X_CI_SEQUENCE_NUMBER in NUMBER,
609 X_UNIT_CLASS in VARCHAR2,
610 X_REFERENCE_NUMBER in NUMBER,
611 X_LOCATION_CD in VARCHAR2,
612 X_CAL_TYPE in VARCHAR2,
613 X_UOO_ID in NUMBER,
614 X_CRS_NOTE_TYPE in VARCHAR2
615 ) AS
616 cursor c1 is select
617 UOO_ID,
618 CRS_NOTE_TYPE
619 from IGS_PS_UNT_OFR_OPT_N
620 where ROWID = X_ROWID for update nowait;
621 tlinfo c1%rowtype;
622
623 begin
624 open c1;
625 fetch c1 into tlinfo;
626 if (c1%notfound) then
627 close c1;
628 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
629 IGS_GE_MSG_STACK.ADD;
630 app_exception.raise_exception;
631 return;
632 end if;
633 close c1;
634
635 if ( (tlinfo.UOO_ID = X_UOO_ID)
636 AND (tlinfo.CRS_NOTE_TYPE = X_CRS_NOTE_TYPE)
637 ) then
638 null;
639 else
640 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
641 IGS_GE_MSG_STACK.ADD;
642 app_exception.raise_exception;
643 end if;
644 return;
645 end LOCK_ROW;
646
647 procedure UPDATE_ROW (
648 X_ROWID in VARCHAR2,
649 X_UNIT_CD in VARCHAR2,
650 X_VERSION_NUMBER in NUMBER,
651 X_CI_SEQUENCE_NUMBER in NUMBER,
652 X_UNIT_CLASS in VARCHAR2,
653 X_REFERENCE_NUMBER in NUMBER,
654 X_LOCATION_CD in VARCHAR2,
655 X_CAL_TYPE in VARCHAR2,
656 X_UOO_ID in NUMBER,
657 X_CRS_NOTE_TYPE in VARCHAR2,
658 X_MODE in VARCHAR2 default 'R'
659 ) AS
660 X_LAST_UPDATE_DATE DATE;
661 X_LAST_UPDATED_BY NUMBER;
662 X_LAST_UPDATE_LOGIN NUMBER;
663 begin
664 X_LAST_UPDATE_DATE := SYSDATE;
665 if(X_MODE = 'I') then
666 X_LAST_UPDATED_BY := 1;
667 X_LAST_UPDATE_LOGIN := 0;
668 elsif (X_MODE = 'R') then
669 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
670 if X_LAST_UPDATED_BY is NULL then
671 X_LAST_UPDATED_BY := -1;
672 end if;
673 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
674 if X_LAST_UPDATE_LOGIN is NULL then
675 X_LAST_UPDATE_LOGIN := -1;
676 end if;
677 else
678 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
679 IGS_GE_MSG_STACK.ADD;
680 app_exception.raise_exception;
681 end if;
682
683 Before_DML(
684 p_action => 'UPDATE',
685 x_rowid => X_ROWID,
686 x_unit_cd => X_UNIT_CD,
687 x_version_number => X_VERSION_NUMBER,
688 x_cal_type => X_CAL_TYPE,
689 x_ci_sequence_number => X_CI_SEQUENCE_NUMBER,
690 x_location_cd => X_LOCATION_CD,
691 x_unit_class => X_UNIT_CLASS,
692 x_reference_number => X_REFERENCE_NUMBER,
693 x_uoo_id => X_UOO_ID,
694 x_crs_note_type => X_CRS_NOTE_TYPE,
695 x_creation_date => X_LAST_UPDATE_DATE,
696 x_created_by => X_LAST_UPDATED_BY,
697 x_last_update_date => X_LAST_UPDATE_DATE,
698 x_last_updated_by => X_LAST_UPDATED_BY,
699 x_last_update_login => X_LAST_UPDATE_LOGIN
700 );
701
702 update IGS_PS_UNT_OFR_OPT_N set
703 UOO_ID = NEW_REFERENCES.UOO_ID,
704 CRS_NOTE_TYPE = NEW_REFERENCES.CRS_NOTE_TYPE,
705 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
706 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
707 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
708 where ROWID = X_ROWID
709 ;
710 if (sql%notfound) then
711 raise no_data_found;
712 end if;
713 After_DML (
714 p_action => 'UPDATE',
715 x_rowid => X_ROWID
716 );
717
718 end UPDATE_ROW;
719
720 procedure ADD_ROW (
721 X_ROWID in out NOCOPY VARCHAR2,
722 X_UNIT_CD in VARCHAR2,
723 X_VERSION_NUMBER in NUMBER,
724 X_CI_SEQUENCE_NUMBER in NUMBER,
725 X_UNIT_CLASS in VARCHAR2,
726 X_REFERENCE_NUMBER in NUMBER,
727 X_LOCATION_CD in VARCHAR2,
728 X_CAL_TYPE in VARCHAR2,
729 X_UOO_ID in out NOCOPY NUMBER,
730 X_CRS_NOTE_TYPE in VARCHAR2,
731 X_MODE in VARCHAR2 default 'R'
732 ) AS
733 cursor c1 is select rowid from IGS_PS_UNT_OFR_OPT_N
734 where UNIT_CD = X_UNIT_CD
735 and VERSION_NUMBER = X_VERSION_NUMBER
736 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
737 and UNIT_CLASS = X_UNIT_CLASS
738 and REFERENCE_NUMBER = X_REFERENCE_NUMBER
739 and LOCATION_CD = X_LOCATION_CD
740 and CAL_TYPE = X_CAL_TYPE
741 ;
742 begin
743 open c1;
744 fetch c1 into X_ROWID;
745 if (c1%notfound) then
746 close c1;
747 INSERT_ROW (
748 X_ROWID,
749 X_UNIT_CD,
750 X_VERSION_NUMBER,
751 X_CI_SEQUENCE_NUMBER,
752 X_UNIT_CLASS,
753 X_REFERENCE_NUMBER,
754 X_LOCATION_CD,
755 X_CAL_TYPE,
756 X_UOO_ID,
757 X_CRS_NOTE_TYPE,
758 X_MODE);
759 return;
760 end if;
761 close c1;
762 UPDATE_ROW (
763 X_ROWID,
764 X_UNIT_CD,
765 X_VERSION_NUMBER,
766 X_CI_SEQUENCE_NUMBER,
767 X_UNIT_CLASS,
768 X_REFERENCE_NUMBER,
769 X_LOCATION_CD,
770 X_CAL_TYPE,
771 X_UOO_ID,
772 X_CRS_NOTE_TYPE,
773 X_MODE);
774 end ADD_ROW;
775
776 procedure DELETE_ROW (
777 X_ROWID in VARCHAR2
778 ) AS
779 begin
780 Before_DML (
781 p_action => 'DELETE',
782 x_rowid => X_ROWID
783 );
784
785 delete from IGS_PS_UNT_OFR_OPT_N
786 where ROWID = X_ROWID;
787 if (sql%notfound) then
788 raise no_data_found;
789 end if;
790 After_DML (
791 p_action => 'DELETE',
792 x_rowid => X_ROWID
793 );
794
795 end DELETE_ROW;
796
797 end IGS_PS_UNT_OFR_OPT_N_PKG;