1 PACKAGE BODY igs_ps_usec_ocur_ref_pkg AS
2 /* $Header: IGSPI0XB.pls 120.1 2005/06/29 03:30:50 appldev ship $ */
3 l_rowid VARCHAR2(25);
4 old_references igs_ps_usec_ocur_ref%RowType;
5 new_references igs_ps_usec_ocur_ref%RowType;
6
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_unit_sec_occur_reference_id IN NUMBER DEFAULT NULL,
11 x_unit_section_occurrence_id IN NUMBER DEFAULT NULL,
12 x_reference_code_type IN VARCHAR2 DEFAULT NULL,
13 x_reference_code IN VARCHAR2 DEFAULT NULL,
14 x_creation_date IN DATE DEFAULT NULL,
15 x_created_by IN NUMBER DEFAULT NULL,
16 x_last_update_date IN DATE DEFAULT NULL,
17 x_last_updated_by IN NUMBER DEFAULT NULL,
18 x_last_update_login IN NUMBER DEFAULT NULL ,
19 x_reference_code_desc IN VARCHAR2 DEFAULT NULL
20 ) AS
21
22 /*************************************************************
23 Created By : venagara
24 Date Created By : 2000/05/12
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_USEC_OCUR_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_sec_occur_reference_id := x_unit_sec_occur_reference_id;
57 new_references.unit_section_occurrence_id := x_unit_section_occurrence_id;
58 new_references.reference_code_type := x_reference_code_type;
59 new_references.reference_code := x_reference_code;
60 new_references.reference_code_desc := x_reference_code_desc;
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 : venagara
79 Date Created By : 2000/05/12
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_Parent_Existance AS
101 /*************************************************************
102 Created By : venagara
103 Date Created By : 2000/05/12
104 Purpose :
105 Know limitations, enhancements or remarks
106 Change History
107 Who When What
108 smvk 31-Jan-2003 Bug # 2532094. Added the foreign key checking with igs_ge_ref_cd.
109 (reverse chronological order - newest change first)
110 ***************************************************************/
111
112 CURSOR cur_reference_cd_chk(cp_reference_cd_type igs_ge_ref_cd_type_all.reference_cd_type%TYPE) IS
113 SELECT 'X'
114 FROM igs_ge_ref_cd_type_all
115 WHERE restricted_flag='Y'
116 AND reference_cd_type=cp_reference_cd_type;
117 l_var VARCHAR2(1);
118
119 BEGIN
120
121 IF (((old_references.unit_section_occurrence_id = new_references.unit_section_occurrence_id)) OR
122 ((new_references.unit_section_occurrence_id IS NULL))) THEN
123 NULL;
124 ELSIF NOT Igs_Ps_Usec_Occurs_Pkg.Get_PK_For_Validation (
125 new_references.unit_section_occurrence_id
126 ) THEN
127 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
128 IGS_GE_MSG_STACK.ADD;
129 App_Exception.Raise_Exception;
130 END IF;
131
132 OPEN cur_reference_cd_chk(new_references.reference_code_type);
133 FETCH cur_reference_cd_chk INTO l_var;
134 IF cur_reference_cd_chk%FOUND THEN
135
136 IF (((old_references.reference_code_type = new_references.reference_code_type) AND
137 (old_references.reference_code = new_references.reference_code)) OR
138 ((new_references.reference_code_type IS NULL) OR
139 (new_references.reference_code IS NULL))) THEN
140 NULL;
141 ELSIF NOT igs_ge_ref_cd_pkg.get_uk_for_validation (
142 new_references.reference_code_type,
143 new_references.reference_code
144 ) THEN
145 Fnd_Message.Set_Name ('FND','FORM_RECORD_DELETED');
146 IGS_GE_MSG_STACK.ADD;
147 App_Exception.Raise_Exception;
148 END IF;
149 END IF;
150 CLOSE cur_reference_cd_chk;
151
152 END Check_Parent_Existance;
153
154 FUNCTION Get_PK_For_Validation (
155 x_unit_sec_occur_reference_id IN NUMBER
156 ) RETURN BOOLEAN AS
157
158 /*************************************************************
159 Created By : venagara
160 Date Created By : 2000/05/12
161 Purpose :
162 Know limitations, enhancements or remarks
163 Change History
164 Who When What
165
166 (reverse chronological order - newest change first)
167 ***************************************************************/
168
169 CURSOR cur_rowid IS
170 SELECT rowid
171 FROM igs_ps_usec_ocur_ref
172 WHERE unit_sec_occur_reference_id = x_unit_sec_occur_reference_id
173 FOR UPDATE NOWAIT;
174
175 lv_rowid cur_rowid%RowType;
176
177 BEGIN
178
179 Open cur_rowid;
180 Fetch cur_rowid INTO lv_rowid;
181 IF (cur_rowid%FOUND) THEN
182 Close cur_rowid;
183 Return(TRUE);
184 ELSE
185 Close cur_rowid;
186 Return(FALSE);
187 END IF;
188 END Get_PK_For_Validation;
189
190 PROCEDURE Get_FK_Igs_Ps_Usec_Occurs (
191 x_unit_section_occurrence_id IN NUMBER
192 ) AS
193
194 /*************************************************************
195 Created By : venagara
196 Date Created By : 2000/05/12
197 Purpose :
198 Know limitations, enhancements or remarks
199 Change History
200 Who When What
201
202 (reverse chronological order - newest change first)
203 ***************************************************************/
204
205 CURSOR cur_rowid IS
206 SELECT rowid
207 FROM igs_ps_usec_ocur_ref
208 WHERE unit_section_occurrence_id = x_unit_section_occurrence_id ;
209
210 lv_rowid cur_rowid%RowType;
211
212 BEGIN
213
214 Open cur_rowid;
215 Fetch cur_rowid INTO lv_rowid;
216 IF (cur_rowid%FOUND) THEN
217 Close cur_rowid;
218 Fnd_Message.Set_Name ('IGS', 'IGS_PS_USOR_USO_FK');
219 IGS_GE_MSG_STACK.ADD;
220 App_Exception.Raise_Exception;
221 Return;
222 END IF;
223 Close cur_rowid;
224
225 END Get_FK_Igs_Ps_Usec_Occurs;
226
227
228
229 PROCEDURE get_fk_igs_ge_ref_cd_type (
230 x_reference_code_type IN VARCHAR2
231 ) AS
232
233 /*************************************************************
234 Created By :sarakshi
235 Date Created By :8-May-2003
236 Purpose :
237 Know limitations, enhancements or remarks
238 Change History
239 Who When What
240
241 (reverse chronological order - newest change first)
242 ***************************************************************/
243
244 CURSOR cur_rowid IS
245 SELECT ROWID
246 FROM igs_ps_usec_ocur_ref
247 WHERE reference_code_type = x_reference_code_type;
248
249 lv_rowid cur_rowid%ROWTYPE;
250
251 BEGIN
252
253 OPEN cur_rowid;
254 FETCH cur_rowid INTO lv_rowid;
255 IF (cur_rowid%FOUND) THEN
256 CLOSE cur_rowid;
257 fnd_message.set_name ('IGS', 'IGS_PS_USOR_RCT_FK');
258 igs_ge_msg_stack.add;
259 app_exception.raise_exception;
260 RETURN;
261 END IF;
262 CLOSE cur_rowid;
263
264 END get_fk_igs_ge_ref_cd_type;
265
266
267 PROCEDURE get_ufk_igs_ge_ref_cd (
268 x_reference_code_type IN VARCHAR2,
269 x_reference_code IN VARCHAR2
270 ) AS
271
272 /*************************************************************
273 Created By :sarakshi
274 Date Created By :8-May-2003
275 Purpose :
276 Know limitations, enhancements or remarks
277 Change History
278 Who When What
279
280 (reverse chronological order - newest change first)
281 ***************************************************************/
282
283 CURSOR cur_rowid IS
284 SELECT ROWID
285 FROM igs_ps_usec_ocur_ref
286 WHERE reference_code_type = x_reference_code_type
287 AND reference_code = x_reference_code ;
288
289 lv_rowid cur_rowid%ROWTYPE;
290
291 BEGIN
292
293 OPEN cur_rowid;
294 FETCH cur_rowid INTO lv_rowid;
295 IF (cur_rowid%FOUND) THEN
296 CLOSE cur_rowid;
297 fnd_message.set_name ('IGS', 'IGS_PS_USOR_RC_FK');
298 igs_ge_msg_stack.add;
299 app_exception.raise_exception;
300 RETURN;
301 END IF;
302 CLOSE cur_rowid;
303
304 END get_ufk_igs_ge_ref_cd;
305
306
307 -- Function for getting the UK
308 -- If the record exists for the parameters passed, then it returns TRUE
309 -- Else it returns false
310 FUNCTION Get_Uk_For_Validation (
311 x_reference_code_type IN VARCHAR2,
312 x_reference_code IN VARCHAR2,
313 x_Unit_section_Occurrence_Id IN NUMBER
314 ) RETURN BOOLEAN AS
315 CURSOR cur_uor IS
316 SELECT ROWID
317 FROM IGS_PS_USEC_OCUR_REF
318 WHERE reference_code_type = x_reference_code_type AND
319 reference_code = x_reference_code AND
320 Unit_section_Occurrence_Id = x_Unit_section_Occurrence_Id AND
321 (l_rowid IS NULL OR rowid <> l_rowid);
322 lv_row_id cur_uor%ROWTYPE;
323 BEGIN
324
325 OPEN cur_uor;
326 FETCH cur_uor INTO lv_row_id;
327 IF cur_uor%FOUND THEN
328 CLOSE cur_uor;
329 RETURN(TRUE);
330 ELSE
331 CLOSE cur_uor;
332 RETURN(FALSE);
333 END IF;
334
335 END Get_Uk_For_Validation;
336
337 -- Procedure for checking the uniqueness
338 PROCEDURE Check_Uniqueness AS
339 BEGIN
340 IF Get_Uk_For_Validation(x_reference_code_type => new_references.reference_code_type,
341 x_reference_code => new_references.reference_code,
342 x_Unit_section_Occurrence_Id => new_references.Unit_section_Occurrence_Id) THEN
343 FND_MESSAGE.SET_NAME('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
344 IGS_GE_MSG_STACK.ADD;
345 APP_EXCEPTION.RAISE_EXCEPTION;
346 END IF;
347 END Check_Uniqueness;
348
349
350 PROCEDURE Before_DML (
351 p_action IN VARCHAR2,
352 x_rowid IN VARCHAR2 DEFAULT NULL,
353 x_unit_sec_occur_reference_id IN NUMBER DEFAULT NULL,
354 x_unit_section_occurrence_id IN NUMBER DEFAULT NULL,
355 x_reference_code_type IN VARCHAR2 DEFAULT NULL,
356 x_reference_code IN VARCHAR2 DEFAULT NULL,
357 x_creation_date IN DATE DEFAULT NULL,
358 x_created_by IN NUMBER DEFAULT NULL,
359 x_last_update_date IN DATE DEFAULT NULL,
360 x_last_updated_by IN NUMBER DEFAULT NULL,
361 x_last_update_login IN NUMBER DEFAULT NULL,
362 x_reference_code_desc IN VARCHAR2 DEFAULT NULL
363 ) AS
364 /*************************************************************
365 Created By : venagara
366 Date Created By : 2000/05/12
367 Purpose :
368 Know limitations, enhancements or remarks
369 Change History
370 Who When What
371
372 (reverse chronological order - newest change first)
373 ***************************************************************/
374
375 BEGIN
376
377 Set_Column_Values (
378 p_action,
379 x_rowid,
380 x_unit_sec_occur_reference_id,
381 x_unit_section_occurrence_id,
382 x_reference_code_type,
383 x_reference_code,
384 x_creation_date,
385 x_created_by,
386 x_last_update_date,
387 x_last_updated_by,
388 x_last_update_login,
389 x_reference_code_desc
390 );
391
392 IF (p_action = 'INSERT') THEN
393 -- Call all the procedures related to Before Insert.
394 Null;
395 IF Get_Pk_For_Validation(
396 new_references.unit_sec_occur_reference_id) THEN
397 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
398 IGS_GE_MSG_STACK.ADD;
399 App_Exception.Raise_Exception;
400 END IF;
401 Check_Constraints;
402 Check_Uniqueness;
403 Check_Parent_Existance;
404 ELSIF (p_action = 'UPDATE') THEN
405 -- Call all the procedures related to Before Update.
406 Null;
407 Check_Constraints;
408 Check_Uniqueness;
409 Check_Parent_Existance;
410 ELSIF (p_action = 'DELETE') THEN
411 -- Call all the procedures related to Before Delete.
412 Null;
413 ELSIF (p_action = 'VALIDATE_INSERT') THEN
414 -- Call all the procedures related to Before Insert.
415 IF Get_PK_For_Validation (
416 new_references.unit_sec_occur_reference_id) THEN
417 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
418 IGS_GE_MSG_STACK.ADD;
419 App_Exception.Raise_Exception;
420 END IF;
421 Check_Constraints;
422 Check_Uniqueness;
423 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
424 Check_Uniqueness;
425 Check_Constraints;
426 ELSIF (p_action = 'VALIDATE_DELETE') THEN
427 Null;
428 END IF;
429
430 END Before_DML;
431
432 PROCEDURE After_DML (
433 p_action IN VARCHAR2,
434 x_rowid IN VARCHAR2
435 ) IS
436 /*************************************************************
437 Created By : venagara
438 Date Created By : 2000/05/12
439 Purpose :
440 Know limitations, enhancements or remarks
441 Change History
442 Who When What
443
444 (reverse chronological order - newest change first)
445 ***************************************************************/
446 CURSOR c_occurs(cp_unit_section_occurrence_id igs_ps_usec_occurs_all.unit_section_occurrence_id%TYPE) IS
447 SELECT uso.unit_section_occurrence_id
448 FROM igs_ps_usec_occurs_all uso
449 WHERE (uso.schedule_status IS NOT NULL AND uso.schedule_status NOT IN ('PROCESSING','USER_UPDATE'))
450 AND uso.no_set_day_ind ='N'
451 AND uso.unit_section_occurrence_id=cp_unit_section_occurrence_id;
452
453 BEGIN
454
455 l_rowid := x_rowid;
456
457 IF (p_action = 'INSERT') THEN
458 --Update the schedule status of the occurrence to USER_UPDATE if inserting a record
459 FOR l_occurs_rec IN c_occurs(new_references.unit_section_occurrence_id) LOOP
460 igs_ps_usec_schedule.update_occurrence_status(l_occurs_rec.unit_section_occurrence_id,'USER_UPDATE','N');
461 END LOOP;
462
463 ELSIF (p_action = 'UPDATE') THEN
464 --Update the schedule status of the occurrence to USER_UPDATE if updating a record
465 FOR l_occurs_rec IN c_occurs(new_references.unit_section_occurrence_id) LOOP
466 igs_ps_usec_schedule.update_occurrence_status(l_occurs_rec.unit_section_occurrence_id,'USER_UPDATE','N');
467 END LOOP;
468
469
470 ELSIF (p_action = 'DELETE') THEN
471 --Update the schedule status of the occurrence to USER_UPDATE if updating a record
472 FOR l_occurs_rec IN c_occurs(old_references.unit_section_occurrence_id) LOOP
473 igs_ps_usec_schedule.update_occurrence_status(l_occurs_rec.unit_section_occurrence_id,'USER_UPDATE','N');
474 END LOOP;
475
476 END IF;
477
478 l_rowid:=NULL;
479 END After_DML;
480
481 procedure INSERT_ROW (
482 X_ROWID in out NOCOPY VARCHAR2,
483 x_UNIT_SEC_OCCUR_REFERENCE_ID IN OUT NOCOPY NUMBER,
484 x_UNIT_SECTION_OCCURRENCE_ID IN NUMBER,
485 x_REFERENCE_CODE_TYPE IN VARCHAR2,
486 x_REFERENCE_CODE IN VARCHAR2,
487 X_MODE in VARCHAR2 default 'R' ,
488 x_reference_code_desc IN VARCHAR2 DEFAULT NULL
489 ) AS
490 /*************************************************************
491 Created By : venagara
492 Date Created By : 2000/05/12
493 Purpose :
494 Know limitations, enhancements or remarks
495 Change History
496 Who When What
497
498 (reverse chronological order - newest change first)
499 ***************************************************************/
500
501 cursor C is select ROWID from IGS_PS_USEC_OCUR_REF
502 where UNIT_SEC_OCCUR_REFERENCE_ID= X_UNIT_SEC_OCCUR_REFERENCE_ID
503 ;
504 X_LAST_UPDATE_DATE DATE ;
505 X_LAST_UPDATED_BY NUMBER ;
506 X_LAST_UPDATE_LOGIN NUMBER ;
507 begin
508 X_LAST_UPDATE_DATE := SYSDATE;
509 if(X_MODE = 'I') then
510 X_LAST_UPDATED_BY := 1;
511 X_LAST_UPDATE_LOGIN := 0;
512 elsif (X_MODE = 'R') then
513 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
514 if X_LAST_UPDATED_BY is NULL then
515 X_LAST_UPDATED_BY := -1;
516 end if;
517 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
518 if X_LAST_UPDATE_LOGIN is NULL then
519 X_LAST_UPDATE_LOGIN := -1;
520 end if;
521 else
522 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
523 IGS_GE_MSG_STACK.ADD;
524 app_exception.raise_exception;
525 end if;
526 SELECT
527 igs_ps_usec_occur_ref_s.nextval
528 INTO
529 X_UNIT_SEC_OCCUR_REFERENCE_ID
530 FROM dual;
531 Before_DML(
532 p_action=>'INSERT',
533 x_rowid=>X_ROWID,
534 x_unit_sec_occur_reference_id=>X_UNIT_SEC_OCCUR_REFERENCE_ID,
535 x_unit_section_occurrence_id=>X_UNIT_SECTION_OCCURRENCE_ID,
536 x_reference_code_type=>X_REFERENCE_CODE_TYPE,
537 x_reference_code=>X_REFERENCE_CODE,
538 x_creation_date=>X_LAST_UPDATE_DATE,
539 x_created_by=>X_LAST_UPDATED_BY,
540 x_last_update_date=>X_LAST_UPDATE_DATE,
541 x_last_updated_by=>X_LAST_UPDATED_BY,
542 x_last_update_login=>X_LAST_UPDATE_LOGIN,
543 x_reference_code_desc=>x_reference_code_desc);
544 insert into IGS_PS_USEC_OCUR_REF (
545 UNIT_SEC_OCCUR_REFERENCE_ID
546 ,UNIT_SECTION_OCCURRENCE_ID
547 ,REFERENCE_CODE_TYPE
548 ,REFERENCE_CODE
549 ,CREATION_DATE
550 ,CREATED_BY
551 ,LAST_UPDATE_DATE
552 ,LAST_UPDATED_BY
553 ,LAST_UPDATE_LOGIN
554 ,REFERENCE_CODE_DESC
555 ) values (
556 NEW_REFERENCES.UNIT_SEC_OCCUR_REFERENCE_ID
557 ,NEW_REFERENCES.UNIT_SECTION_OCCURRENCE_ID
558 ,NEW_REFERENCES.REFERENCE_CODE_TYPE
559 ,NEW_REFERENCES.REFERENCE_CODE
560 ,X_LAST_UPDATE_DATE
561 ,X_LAST_UPDATED_BY
562 ,X_LAST_UPDATE_DATE
563 ,X_LAST_UPDATED_BY
564 ,X_LAST_UPDATE_LOGIN
565 ,X_REFERENCE_CODE_DESC
566 );
567 open c;
568 fetch c into X_ROWID;
569 if (c%notfound) then
570 close c;
571 raise no_data_found;
572 end if;
573 close c;
574 After_DML (
575 p_action => 'INSERT' ,
576 x_rowid => X_ROWID );
577 end INSERT_ROW;
578 procedure LOCK_ROW (
579 X_ROWID in VARCHAR2,
580 x_UNIT_SEC_OCCUR_REFERENCE_ID IN NUMBER,
581 x_UNIT_SECTION_OCCURRENCE_ID IN NUMBER,
582 x_REFERENCE_CODE_TYPE IN VARCHAR2,
583 x_REFERENCE_CODE IN VARCHAR2 ,
584 x_reference_code_desc IN VARCHAR2 DEFAULT NULL
585 ) AS
586 /*************************************************************
587 Created By : venagara
588 Date Created By : 2000/05/12
589 Purpose :
590 Know limitations, enhancements or remarks
591 Change History
592 Who When What
593
594 (reverse chronological order - newest change first)
595 ***************************************************************/
596
597 cursor c1 is select
598 UNIT_SECTION_OCCURRENCE_ID
599 , REFERENCE_CODE_TYPE
600 , REFERENCE_CODE
601 , REFERENCE_CODE_DESC
602 from IGS_PS_USEC_OCUR_REF
603 where ROWID = X_ROWID
604 for update nowait;
605 tlinfo c1%rowtype;
606 begin
607 open c1;
608 fetch c1 into tlinfo;
609 if (c1%notfound) then
610 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
611 IGS_GE_MSG_STACK.ADD;
612 close c1;
613 app_exception.raise_exception;
614 return;
615 end if;
616 close c1;
617 if ( ( tlinfo.UNIT_SECTION_OCCURRENCE_ID = X_UNIT_SECTION_OCCURRENCE_ID)
618 AND (tlinfo.REFERENCE_CODE_TYPE = X_REFERENCE_CODE_TYPE)
619 AND (tlinfo.REFERENCE_CODE = X_REFERENCE_CODE)
620 AND ((tlinfo.reference_code_desc= x_reference_code_desc)
621 OR ((tlinfo.reference_code_desc IS NULL)
622 AND (x_reference_code_desc IS NULL)))
623 ) then
624 null;
625 else
626 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
627 IGS_GE_MSG_STACK.ADD;
628 app_exception.raise_exception;
629 end if;
630 return;
631 end LOCK_ROW;
632
633 Procedure UPDATE_ROW (
634 X_ROWID in VARCHAR2,
635 x_UNIT_SEC_OCCUR_REFERENCE_ID IN NUMBER,
636 x_UNIT_SECTION_OCCURRENCE_ID IN NUMBER,
637 x_REFERENCE_CODE_TYPE IN VARCHAR2,
638 x_REFERENCE_CODE IN VARCHAR2,
639 X_MODE in VARCHAR2 default 'R' ,
640 x_reference_code_desc IN VARCHAR2 DEFAULT NULL
641 ) AS
642 /*************************************************************
643 Created By : venagara
644 Date Created By : 2000/05/12
645 Purpose :
646 Know limitations, enhancements or remarks
647 Change History
648 Who When What
649
650 (reverse chronological order - newest change first)
651 ***************************************************************/
652
653 X_LAST_UPDATE_DATE DATE ;
654 X_LAST_UPDATED_BY NUMBER ;
655 X_LAST_UPDATE_LOGIN NUMBER ;
656 begin
657 X_LAST_UPDATE_DATE := SYSDATE;
658 if(X_MODE = 'I') then
659 X_LAST_UPDATED_BY := 1;
660 X_LAST_UPDATE_LOGIN := 0;
661 elsif (X_MODE = 'R') then
662 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
663 if X_LAST_UPDATED_BY is NULL then
664 X_LAST_UPDATED_BY := -1;
665 end if;
666 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
667 if X_LAST_UPDATE_LOGIN is NULL then
668 X_LAST_UPDATE_LOGIN := -1;
669 end if;
670 else
671 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
672 IGS_GE_MSG_STACK.ADD;
673 app_exception.raise_exception;
674 end if;
675 Before_DML(
676 p_action=>'UPDATE',
677 x_rowid=>X_ROWID,
678 x_unit_sec_occur_reference_id=>X_UNIT_SEC_OCCUR_REFERENCE_ID,
679 x_unit_section_occurrence_id=>X_UNIT_SECTION_OCCURRENCE_ID,
680 x_reference_code_type=>X_REFERENCE_CODE_TYPE,
681 x_reference_code=>X_REFERENCE_CODE,
682 x_creation_date=>X_LAST_UPDATE_DATE,
683 x_created_by=>X_LAST_UPDATED_BY,
684 x_last_update_date=>X_LAST_UPDATE_DATE,
685 x_last_updated_by=>X_LAST_UPDATED_BY,
686 x_last_update_login=>X_LAST_UPDATE_LOGIN,
687 x_reference_code_desc=>x_reference_code_desc);
688 update IGS_PS_USEC_OCUR_REF set
689 UNIT_SECTION_OCCURRENCE_ID = NEW_REFERENCES.UNIT_SECTION_OCCURRENCE_ID,
690 REFERENCE_CODE_TYPE = NEW_REFERENCES.REFERENCE_CODE_TYPE,
691 REFERENCE_CODE = NEW_REFERENCES.REFERENCE_CODE,
692 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
693 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
694 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
695 REFERENCE_CODE_DESC = NEW_REFERENCES.REFERENCE_CODE_DESC
696 where ROWID = X_ROWID;
697 if (sql%notfound) then
698 raise no_data_found;
699 end if;
700
701 After_DML (
702 p_action => 'UPDATE' ,
703 x_rowid => X_ROWID
704 );
705 end UPDATE_ROW;
706 procedure ADD_ROW (
707 X_ROWID in out NOCOPY VARCHAR2,
708 x_UNIT_SEC_OCCUR_REFERENCE_ID IN OUT NOCOPY NUMBER,
709 x_UNIT_SECTION_OCCURRENCE_ID IN NUMBER,
710 x_REFERENCE_CODE_TYPE IN VARCHAR2,
711 x_REFERENCE_CODE IN VARCHAR2,
712 X_MODE in VARCHAR2 default 'R' ,
713 x_reference_code_desc IN VARCHAR2 DEFAULT NULL
714 ) AS
715 /*************************************************************
716 Created By : venagara
717 Date Created By : 2000/05/12
718 Purpose :
719 Know limitations, enhancements or remarks
720 Change History
721 Who When What
722
723 (reverse chronological order - newest change first)
724 ***************************************************************/
725
726 cursor c1 is select ROWID from IGS_PS_USEC_OCUR_REF
727 where UNIT_SEC_OCCUR_REFERENCE_ID= X_UNIT_SEC_OCCUR_REFERENCE_ID
728 ;
729 begin
730 open c1;
731 fetch c1 into X_ROWID;
732 if (c1%notfound) then
733 close c1;
734 INSERT_ROW (
735 X_ROWID,
736 X_UNIT_SEC_OCCUR_REFERENCE_ID,
737 X_UNIT_SECTION_OCCURRENCE_ID,
738 X_REFERENCE_CODE_TYPE,
739 X_REFERENCE_CODE,
740 X_MODE,
741 X_REFERENCE_CODE_DESC );
742 return;
743 end if;
744 close c1;
745 UPDATE_ROW (
746 X_ROWID,
747 X_UNIT_SEC_OCCUR_REFERENCE_ID,
748 X_UNIT_SECTION_OCCURRENCE_ID,
749 X_REFERENCE_CODE_TYPE,
750 X_REFERENCE_CODE,
751 X_MODE,
752 X_REFERENCE_CODE_DESC );
753 end ADD_ROW;
754 procedure DELETE_ROW (
755 X_ROWID in VARCHAR2
756 ) AS
757 /*************************************************************
758 Created By : venagara
759 Date Created By : 2000/05/12
760 Purpose :
761 Know limitations, enhancements or remarks
762 Change History
763 Who When What
764
765 (reverse chronological order - newest change first)
766 ***************************************************************/
767
768 begin
769 Before_DML (
770 p_action => 'DELETE',
771 x_rowid => X_ROWID
772 );
773 delete from IGS_PS_USEC_OCUR_REF
774 where ROWID = X_ROWID;
775 if (sql%notfound) then
776 raise no_data_found;
777 end if;
778 After_DML (
779 p_action => 'DELETE',
780 x_rowid => X_ROWID
781 );
782 end DELETE_ROW;
783 END igs_ps_usec_ocur_ref_pkg;
[Home] [Help]