1 package body IGS_GE_REF_CD_TYPE_PKG as
2 /* $Header: IGSMI04B.pls 120.1 2006/01/25 09:19:54 skpandey noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_GE_REF_CD_TYPE_ALL%RowType;
6 new_references IGS_GE_REF_CD_TYPE_ALL%RowType;
7
8 PROCEDURE Set_Column_Values (
9 p_action IN VARCHAR2,
10 x_rowid IN VARCHAR2 DEFAULT NULL,
11 x_self_service_flag IN VARCHAR2 DEFAULT NULL,
12 x_reference_cd_type IN VARCHAR2 DEFAULT NULL,
13 x_description IN VARCHAR2 DEFAULT NULL,
14 x_s_reference_cd_type IN VARCHAR2 DEFAULT NULL,
15 x_closed_ind IN VARCHAR2 DEFAULT NULL,
16 x_program_flag IN VARCHAR2 DEFAULT NULL,
17 x_program_offering_option_flag IN VARCHAR2 DEFAULT NULL,
18 x_unit_flag IN VARCHAR2 DEFAULT NULL,
19 x_unit_section_flag IN VARCHAR2 DEFAULT NULL,
20 x_unit_section_occurrence_flag IN VARCHAR2 DEFAULT NULL,
21
22 x_creation_date IN DATE DEFAULT NULL,
23 x_created_by IN NUMBER DEFAULT NULL,
24 x_last_update_date IN DATE DEFAULT NULL,
25 x_last_updated_by IN NUMBER DEFAULT NULL,
26 x_last_update_login IN NUMBER DEFAULT NULL,
27 x_org_id IN NUMBER DEFAULT NULL,
28 x_mandatory_flag IN VARCHAR2 DEFAULT NULL,
29 x_restricted_flag IN VARCHAR2
30 ) as
31
32 /*************************************************************
33 Created By :
34 Date Created By :
35 Purpose :
36 Know limitations, enhancements or remarks
37 Change History
38 Who When What
39 sbeerell 09-MAY-2000 Changed according to DLD version 2
40 (reverse chronological order - newest change first)
41 ***************************************************************/
42
43 CURSOR cur_old_ref_values IS
44 SELECT *
45 FROM IGS_GE_REF_CD_TYPE_ALL
46 WHERE rowid = x_rowid;
47
48 BEGIN
49
50 l_rowid := x_rowid;
51
52 -- Code for setting the Old and New Reference Values.
53 -- Populate Old Values.
54 Open cur_old_ref_values;
55 Fetch cur_old_ref_values INTO old_references;
56 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
57 Close cur_old_ref_values;
58 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
59 IGS_GE_MSG_STACK.ADD;
60 App_Exception.Raise_Exception;
61 Return;
62 END IF;
63 Close cur_old_ref_values;
64
65 -- Populate New Values.
66 new_references.self_service_flag := x_self_service_flag;
67 new_references.reference_cd_type := x_reference_cd_type;
68 new_references.description := x_description;
69 new_references.s_reference_cd_type := x_s_reference_cd_type;
70 new_references.closed_ind := x_closed_ind;
71 new_references.program_flag := x_program_flag;
72 new_references.program_offering_option_flag := x_program_offering_option_flag;
73 new_references.unit_flag := x_unit_flag;
74 new_references.unit_section_flag := x_unit_section_flag;
75 new_references.unit_section_occurrence_flag := x_unit_section_occurrence_flag;
76 new_references.mandatory_flag := x_mandatory_flag;
77 new_references.restricted_flag:= x_restricted_flag;
78 IF (p_action = 'UPDATE') THEN
79 new_references.creation_date := old_references.creation_date;
80 new_references.created_by := old_references.created_by;
81 ELSE
82 new_references.creation_date := x_creation_date;
83 new_references.created_by := x_created_by;
84 END IF;
85 new_references.last_update_date := x_last_update_date;
86 new_references.last_updated_by := x_last_updated_by;
87 new_references.last_update_login := x_last_update_login;
88 new_references.org_id := x_org_id;
89
90 END Set_Column_Values;
91
92 PROCEDURE BeforeRowInsertUpdate1(
93 p_inserting IN BOOLEAN DEFAULT FALSE,
94 p_updating IN BOOLEAN DEFAULT FALSE,
95 p_deleting IN BOOLEAN DEFAULT FALSE
96 ) as
97 /*************************************************************
98 Created By :
99 Date Created By :
100 Purpose :
101 Know limitations, enhancements or remarks
102 Change History
103 Who When What
104 (reverse chronological order - newest change first)
105 ***************************************************************/
106 v_message_name NUMBER(5);
107 BEGIN
108 -- Validate system reference code type.
109 IF p_inserting OR
110 (p_updating AND
111 ((old_references.s_reference_cd_type <> new_references.s_reference_cd_type) OR
112 (old_references.closed_ind = 'Y' AND new_references.closed_ind = 'N'))) THEN
113 IF IGS_PS_VAL_RCT.crsp_val_rct_srct (
114 new_references.s_reference_cd_type,
115 v_message_name) = FALSE THEN
116 Fnd_Message.Set_Name('IGS' , v_message_name);
117 IGS_GE_MSG_STACK.ADD;
118 App_Exception.Raise_Exception;
119 END IF;
120 END IF;
121 END BeforeRowInsertUpdate1;
122
123 PROCEDURE Check_Constraints(
124 Column_Name IN VARCHAR2 DEFAULT NULL,
125 Column_Value IN VARCHAR2 DEFAULT NULL
126 ) as
127 /*************************************************************
128 Created By :
129 Date Created By :
130 Purpose :
131 Know limitations, enhancements or remarks
132 Change History
133 Who When What
134 (reverse chronological order - newest change first)
135 ***************************************************************/
136 BEGIN
137 IF column_name is null then
138 NULL;
139 ELSIF upper(Column_name) = 'CLOSED_IND' then
140 new_references.closed_ind := COLUMN_VALUE;
141 ELSIF upper(Column_name) = 'REFERENCE_CD_TYPE' then
142 new_references.reference_cd_type := COLUMN_VALUE;
143 ELSIF upper(Column_name) = 'S_REFERENCE_CD_TYPE' then
144 new_references.s_reference_cd_type := COLUMN_VALUE;
145 END IF;
146 IF upper(Column_name) = 'CLOSED_IND' OR column_name is null then
147 IF new_references.closed_ind <> UPPER(new_references.closed_ind ) OR
148 new_references.closed_ind NOT IN ( 'Y' , 'N' ) then
149 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
150 IGS_GE_MSG_STACK.ADD;
151 App_Exception.Raise_Exception;
152 END IF;
153 END IF;
154 IF upper(Column_name) = 'REFERENCE_CD_TYPE' OR column_name is null then
155 IF new_references.reference_cd_type <> UPPER(new_references.reference_cd_type ) then
156 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
157 IGS_GE_MSG_STACK.ADD;
158 App_Exception.Raise_Exception;
159 END IF;
160 END IF;
161 IF upper(Column_name) = 'S_REFERENCE_CD_TYPE' OR column_name is null then
162 IF new_references.s_reference_cd_type <> UPPER(new_references.s_reference_cd_type ) then
163 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
164 IGS_GE_MSG_STACK.ADD;
165 App_Exception.Raise_Exception;
166 END IF;
167 END IF;
168
169
170 IF column_name IS NULL THEN
171 NULL;
172 ELSIF UPPER(column_name) = 'SELF_SERVICE_FLAG' THEN
173 new_references.self_service_flag := column_value;
174 ELSIF UPPER(column_name) = 'PROGRAM_FLAG' THEN
175 new_references.program_flag := column_value;
176 ELSIF UPPER(column_name) = 'PROGRAM_OFFERING_OPTION_FLAG' THEN
177 new_references.program_offering_option_flag := column_value;
178 ELSIF UPPER(column_name) = 'UNIT_FLAG' THEN
179 new_references.unit_flag := column_value;
180 ELSIF UPPER(column_name) = 'UNIT_SECTION_FLAG' THEN
181 new_references.unit_section_flag := column_value;
182 ELSIF UPPER(column_name) = 'UNIT_SECTION_OCCURRENCE_FLAG' THEN
183 new_references.unit_section_occurrence_flag := column_value;
184
185 NULL;
186 END IF;
187
188
189
190 -- The following code checks for check constraints on the Columns.
191 IF Upper(Column_Name) = 'SELF_SERVICE_FLAG' OR
192 Column_Name IS NULL THEN
193 IF NOT (new_references.self_service_flag IN ('Y', 'N')) THEN
194 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
195 IGS_GE_MSG_STACK.ADD;
196 App_Exception.Raise_Exception;
197 END IF;
198 END IF;
199
200 -- The following code checks for check constraints on the Columns.
201 IF Upper(Column_Name) = 'PROGRAM_FLAG' OR
202 Column_Name IS NULL THEN
203 IF NOT (new_references.program_flag IN ('Y', 'N')) THEN
204 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
205 IGS_GE_MSG_STACK.ADD;
206 App_Exception.Raise_Exception;
207 END IF;
208 END IF;
209
210 -- The following code checks for check constraints on the Columns.
211 IF Upper(Column_Name) = 'PROGRAM_OFFERING_OPTION_FLAG' OR
212 Column_Name IS NULL THEN
213 IF NOT (new_references.program_offering_option_flag IN ('Y', 'N')) THEN
214 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
215 IGS_GE_MSG_STACK.ADD;
216 App_Exception.Raise_Exception;
217 END IF;
218 END IF;
219
220 -- The following code checks for check constraints on the Columns.
221 IF Upper(Column_Name) = 'UNIT_FLAG' OR
222 Column_Name IS NULL THEN
223 IF NOT (new_references.unit_flag IN ('Y', 'N')) THEN
224 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
225 IGS_GE_MSG_STACK.ADD;
226 App_Exception.Raise_Exception;
227 END IF;
228 END IF;
229
230 -- The following code checks for check constraints on the Columns.
231 IF Upper(Column_Name) = 'UNIT_SECTION_FLAG' OR
232 Column_Name IS NULL THEN
233 IF NOT (new_references.unit_section_flag IN ('Y', 'N')) THEN
234 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
235 IGS_GE_MSG_STACK.ADD;
236 App_Exception.Raise_Exception;
237 END IF;
238 END IF;
239
240 -- The following code checks for check constraints on the Columns.
241 IF Upper(Column_Name) = 'UNIT_SECTION_OCCURRENCE_FLAG' OR
242 Column_Name IS NULL THEN
243 IF NOT (new_references.unit_section_occurrence_flag IN ('Y', 'N')) THEN
244 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
245 IGS_GE_MSG_STACK.ADD;
246 App_Exception.Raise_Exception;
247 END IF;
248 END IF;
249
250 --Added as a part of Enh#2858431
251 IF NOT (new_references.restricted_flag IN ('Y', 'N')) THEN
252 Fnd_Message.Set_Name('IGS','IGS_GE_INVALID_VALUE');
253 IGS_GE_MSG_STACK.ADD;
254 App_Exception.Raise_Exception;
255 END IF;
256
257 END Check_Constraints;
258
259
260 PROCEDURE Check_Parent_Existance as
261 /*************************************************************
262 Created By :
263 Date Created By :
264 Purpose :
265 Know limitations, enhancements or remarks
266 Change History
267 Who When What
268 (reverse chronological order - newest change first)
269 ***************************************************************/
270
271 BEGIN
272
273 IF (((old_references.s_reference_cd_type = new_references.s_reference_cd_type)) OR
274 ((new_references.s_reference_cd_type IS NULL))) THEN
275 NULL;
276 ELSE
277 IF NOT IGS_LOOKUPS_view_Pkg.Get_PK_For_Validation (
278 'REFERENCE_CD_TYPE',
279 new_references.s_reference_cd_type
280 ) THEN
281 Fnd_Message.Set_Name('FND','FORM_RECORD_DELETED');
282 IGS_GE_MSG_STACK.ADD;
283 App_Exception.Raise_Exception;
284 END IF;
285
286 END IF;
287
288 END Check_Parent_Existance;
289
290 PROCEDURE Check_Child_Existance as
291 /*************************************************************
292 Created By :
293 Date Created By :
294 Purpose :
295 Know limitations, enhancements or remarks
296 Change History
297 Who When What
298 (reverse chronological order - newest change first)
299 ***************************************************************/
300
301 BEGIN
302
303 IGS_GE_REF_CD_PKG.GET_FK_IGS_GE_REF_CD_TYPE(
304 old_references.reference_cd_type
305 );
306
307 IGS_PS_ENT_PT_REF_CD_PKG.GET_FK_IGS_GE_REF_CD_TYPE (
308 old_references.reference_cd_type
309 );
310
311 IGS_PS_REF_CD_PKG.GET_FK_IGS_GE_REF_CD_TYPE (
312 old_references.reference_cd_type
313 );
314
315 IGS_PS_REF_CD_HIST_PKG.GET_FK_IGS_GE_REF_CD_TYPE (
316 old_references.reference_cd_type
317 );
318
319 IGS_PS_UNIT_REF_CD_PKG.GET_FK_IGS_GE_REF_CD_TYPE (
320 old_references.reference_cd_type
321 );
322
323 IGS_PS_UNIT_REF_HIST_PKG.GET_FK_IGS_GE_REF_CD_TYPE (
324 old_references.reference_cd_type
325 );
326
327 igs_ps_unitreqref_cd_pkg.get_fk_igs_ge_ref_cd_type (
328 old_references.reference_cd_type
329 );
330
331 igs_ps_us_req_ref_cd_pkg.get_fk_igs_ge_ref_cd_type (
332 old_references.reference_cd_type
333 );
334
335 igs_ps_usec_ref_cd_pkg.get_fk_igs_ge_ref_cd_type (
336 old_references.reference_cd_type
337 );
338
339 igs_ps_usec_ocur_ref_pkg.get_fk_igs_ge_ref_cd_type (
340 old_references.reference_cd_type
341 );
342
343 END Check_Child_Existance;
344
345 FUNCTION GET_PK_FOR_VALIDATION (
346 x_reference_cd_type IN VARCHAR2
347 ) RETURN BOOLEAN as
348 /*************************************************************
349 Created By :
350 Date Created By :
351 Purpose :
352 Know limitations, enhancements or remarks
353 Change History
354 Who When What
355 (reverse chronological order - newest change first)
356 ***************************************************************/
357 CURSOR cur_rowid IS
358 SELECT rowid
359 FROM IGS_GE_REF_CD_TYPE_ALL
360 WHERE reference_cd_type = x_reference_cd_type
361 FOR UPDATE NOWAIT;
362
363 lv_rowid cur_rowid%RowType;
364
365 BEGIN
366
367 Open cur_rowid;
368 Fetch cur_rowid INTO lv_rowid;
369 IF (cur_rowid%FOUND) THEN
370 Close cur_rowid;
371 Return(TRUE);
372 ELSE
373 Close cur_rowid;
374 Return(FALSE);
375 END IF;
376
377 END Get_PK_For_Validation;
378
379 PROCEDURE GET_FK_IGS_LOOKUPS_VIEW (
380 x_s_reference_cd_type IN VARCHAR2
381 ) as
382 /*************************************************************
383 Created By :
384 Date Created By :
385 Purpose :
386 Know limitations, enhancements or remarks
387 Change History
388 Who When What
389 skpandey 24-JAN-2006 Bug#3686538: Stubbed as a part of query optimization
390 (reverse chronological order - newest change first)
391 ***************************************************************/
392 BEGIN
393 NULL;
394 END GET_FK_IGS_LOOKUPS_VIEW;
395
396 PROCEDURE Before_DML (
397 p_action IN VARCHAR2,
398 x_rowid IN VARCHAR2 DEFAULT NULL,
399 x_self_service_flag IN VARCHAR2 DEFAULT NULL,
400 x_reference_cd_type IN VARCHAR2 DEFAULT NULL,
401 x_description IN VARCHAR2 DEFAULT NULL,
402 x_s_reference_cd_type IN VARCHAR2 DEFAULT NULL,
403 x_closed_ind IN VARCHAR2 DEFAULT NULL,
404 x_program_flag IN VARCHAR2 DEFAULT NULL,
405 x_program_offering_option_flag IN VARCHAR2 DEFAULT NULL,
406 x_unit_flag IN VARCHAR2 DEFAULT NULL,
407 x_unit_section_flag IN VARCHAR2 DEFAULT NULL,
408 x_unit_section_occurrence_flag IN VARCHAR2 DEFAULT NULL,
409
410 x_creation_date IN DATE DEFAULT NULL,
411 x_created_by IN NUMBER DEFAULT NULL,
412 x_last_update_date IN DATE DEFAULT NULL,
413 x_last_updated_by IN NUMBER DEFAULT NULL,
414 x_last_update_login IN NUMBER DEFAULT NULL,
415 x_org_id IN NUMBER DEFAULT NULL,
416 x_mandatory_flag IN VARCHAR2 DEFAULT NULL,
417 x_restricted_flag IN VARCHAR2
418 ) as
419 /*************************************************************
420 Created By :
421 Date Created By :
422 Purpose :
423 Know limitations, enhancements or remarks
424 Change History
425 Who When What
426 sbeerell 09-MAY-2000 Changed according to DLD version 2
427 (reverse chronological order - newest change first)
428 ***************************************************************/
429 BEGIN
430
431 Set_Column_Values (
432 p_action,
433 x_rowid,
434 x_self_service_flag,
435 x_reference_cd_type,
436 x_description,
437 x_s_reference_cd_type,
438 x_closed_ind,
439 x_program_flag,
440 x_program_offering_option_flag,
441 x_unit_flag,
442 x_unit_section_flag,
443 x_unit_section_occurrence_flag,
444
445 x_creation_date,
446 x_created_by,
447 x_last_update_date,
448 x_last_updated_by,
449 x_last_update_login,
450 x_org_id,
451 x_mandatory_flag,
452 x_restricted_flag
453 );
454
455 IF (p_action = 'INSERT') THEN
456 -- Call all the procedures related to Before Insert.
457 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
458 IF GET_PK_FOR_VALIDATION ( new_references.reference_cd_type) THEN
459 Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
460 IGS_GE_MSG_STACK.ADD;
461 App_Exception.Raise_Exception;
462 END IF;
463 Check_Constraints;
464 Check_Parent_Existance;
465 ELSIF (p_action = 'UPDATE') THEN
466 -- Call all the procedures related to Before Update.
467 BeforeRowInsertUpdate1 ( p_updating => TRUE );
468 Check_Constraints;
469 Check_Parent_Existance;
470 ELSIF (p_action = 'DELETE') THEN
471 -- Call all the procedures related to Before Delete.
472 Check_Child_Existance;
473 ELSIF (p_action = 'VALIDATE_INSERT') THEN
474 IF GET_PK_FOR_VALIDATION ( new_references.reference_cd_type ) THEN
475 Fnd_Message.Set_Name('IGS','IGS_GE_MULTI_ORG_DUP_REC');
476 IGS_GE_MSG_STACK.ADD;
477 App_Exception.Raise_Exception;
478 END IF;
479 Check_Constraints;
480 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
481 Check_Constraints;
482 ELSIF (p_action = 'VALIDATE_DELETE') THEN
483 Check_Child_Existance;
484
485 END IF;
486
487 END Before_DML;
488
489 PROCEDURE After_DML (
490 p_action IN VARCHAR2,
491 x_rowid IN VARCHAR2
492 ) as
493 /*************************************************************
494 Created By :
495 Date Created By :
496 Purpose :
497 Know limitations, enhancements or remarks
498 Change History
499 Who When What
500 (reverse chronological order - newest change first)
501 ***************************************************************/
502
503 BEGIN
504
505 l_rowid := x_rowid;
506
507 IF (p_action = 'INSERT') THEN
508 -- Call all the procedures related to After Insert.
509 Null;
510 ELSIF (p_action = 'UPDATE') THEN
511 -- Call all the procedures related to After Update.
512 Null;
513 ELSIF (p_action = 'DELETE') THEN
514 -- Call all the procedures related to After Delete.
515 Null;
516 END IF;
517
518 END After_DML;
519
520 procedure INSERT_ROW (
521 X_ROWID in out NOCOPY VARCHAR2,
522 x_SELF_SERVICE_FLAG IN VARCHAR2,
523 x_REFERENCE_CD_TYPE IN VARCHAR2,
524 x_DESCRIPTION IN VARCHAR2,
525 x_S_REFERENCE_CD_TYPE IN VARCHAR2,
526 x_CLOSED_IND IN VARCHAR2,
527 x_PROGRAM_FLAG IN VARCHAR2,
528 x_PROGRAM_OFFERING_OPTION_FLAG IN VARCHAR2,
529 x_UNIT_FLAG IN VARCHAR2,
530 x_UNIT_SECTION_FLAG IN VARCHAR2,
531 x_UNIT_SECTION_OCCURRENCE_FLAG IN VARCHAR2,
532 X_MODE in VARCHAR2 default 'R',
533 X_ORG_ID in NUMBER,
534 x_mandatory_flag IN VARCHAR2 DEFAULT NULL,
535 x_restricted_flag IN VARCHAR2
536 ) as
537 /*************************************************************
538 Created By :
539 Date Created By :
540 Purpose :
541 Know limitations, enhancements or remarks
542 Change History
543 Who When What
544 sbaliga 13-feb-2002 Assigned igs_ge_gen-003.get_org_id to x-org_id in before_dml
545 as part of SWCR006 build.
546 sbeerell 09-MAY-2000 Changed according to DLD version 2
547 (reverse chronological order - newest change first)
548 ***************************************************************/
549
550 cursor C is select ROWID from IGS_GE_REF_CD_TYPE_ALL
551 where REFERENCE_CD_TYPE = X_REFERENCE_CD_TYPE;
552 X_LAST_UPDATE_DATE DATE;
553 X_LAST_UPDATED_BY NUMBER;
554 X_LAST_UPDATE_LOGIN NUMBER;
555 begin
556 X_LAST_UPDATE_DATE := SYSDATE;
557 if(X_MODE = 'I') then
558 X_LAST_UPDATED_BY := 1;
559 X_LAST_UPDATE_LOGIN := 0;
560 elsif (X_MODE = 'R') then
561 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
562 if X_LAST_UPDATED_BY is NULL then
563 X_LAST_UPDATED_BY := -1;
564 end if;
565 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
566 if X_LAST_UPDATE_LOGIN is NULL then
567 X_LAST_UPDATE_LOGIN := -1;
568 end if;
569 else
570 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
571 IGS_GE_MSG_STACK.ADD;
572 app_exception.raise_exception;
573 end if;
574
575 Before_DML (
576 p_action => 'INSERT',
577 x_rowid => X_ROWID,
578 x_org_id => igs_ge_gen_003.get_org_id,
579 x_self_service_flag=>X_SELF_SERVICE_FLAG,
580 x_reference_cd_type=>X_REFERENCE_CD_TYPE,
581 x_description=>X_DESCRIPTION,
582 x_s_reference_cd_type=>X_S_REFERENCE_CD_TYPE,
583 x_closed_ind=>NVL(X_CLOSED_IND,'N' ),
584 x_program_flag=>X_PROGRAM_FLAG,
585 x_program_offering_option_flag=>X_PROGRAM_OFFERING_OPTION_FLAG,
586 x_unit_flag=>X_UNIT_FLAG,
587 x_unit_section_flag=>X_UNIT_SECTION_FLAG,
588 x_unit_section_occurrence_flag=>X_UNIT_SECTION_OCCURRENCE_FLAG,
589 x_creation_date=>X_LAST_UPDATE_DATE,
590 x_created_by=>X_LAST_UPDATED_BY,
591 x_last_update_date=>X_LAST_UPDATE_DATE,
592 x_last_updated_by=>X_LAST_UPDATED_BY,
593 x_last_update_login=>X_LAST_UPDATE_LOGIN,
594
595 x_mandatory_flag => X_MANDATORY_FLAG,
596 x_restricted_flag =>x_restricted_flag
597 );
598 insert into IGS_GE_REF_CD_TYPE_ALL (
599 SELF_SERVICE_FLAG
600 ,REFERENCE_CD_TYPE
601 ,DESCRIPTION
602 ,S_REFERENCE_CD_TYPE
603 ,CLOSED_IND
604 ,PROGRAM_FLAG
605 ,PROGRAM_OFFERING_OPTION_FLAG
606 ,UNIT_FLAG
607 ,UNIT_SECTION_FLAG
608 ,UNIT_SECTION_OCCURRENCE_FLAG
609 ,CREATION_DATE
610 ,CREATED_BY
611 ,LAST_UPDATE_DATE
612 ,LAST_UPDATED_BY
613 ,LAST_UPDATE_LOGIN
614 ,ORG_ID
615 ,MANDATORY_FLAG
616 ,RESTRICTED_FLAG
617 ) values (
618 NEW_REFERENCES.SELF_SERVICE_FLAG
619 ,NEW_REFERENCES.REFERENCE_CD_TYPE
620 ,NEW_REFERENCES.DESCRIPTION
621 ,NEW_REFERENCES.S_REFERENCE_CD_TYPE
622 ,NEW_REFERENCES.CLOSED_IND
623 ,NEW_REFERENCES.PROGRAM_FLAG
624 ,NEW_REFERENCES.PROGRAM_OFFERING_OPTION_FLAG
625 ,NEW_REFERENCES.UNIT_FLAG
626 ,NEW_REFERENCES.UNIT_SECTION_FLAG
627 ,NEW_REFERENCES.UNIT_SECTION_OCCURRENCE_FLAG
628 ,X_LAST_UPDATE_DATE
629 ,X_LAST_UPDATED_BY
630 ,X_LAST_UPDATE_DATE
631 ,X_LAST_UPDATED_BY
632 ,X_LAST_UPDATE_LOGIN
633 ,NEW_REFERENCES.ORG_ID
634 ,NEW_REFERENCES.MANDATORY_FLAG
635 ,NEW_REFERENCES.RESTRICTED_FLAG
636 );
637
638 open c;
639 fetch c into X_ROWID;
640 if (c%notfound) then
641 close c;
642 raise no_data_found;
643 end if;
644 close c;
645
646 After_DML (
647 p_action => 'INSERT',
648 x_rowid => X_ROWID
649 );
650 end INSERT_ROW;
651
652 procedure LOCK_ROW (
653 X_ROWID in VARCHAR2,
654 x_SELF_SERVICE_FLAG IN VARCHAR2,
655 x_REFERENCE_CD_TYPE IN VARCHAR2,
656 x_DESCRIPTION IN VARCHAR2,
657 x_S_REFERENCE_CD_TYPE IN VARCHAR2,
658 x_CLOSED_IND IN VARCHAR2,
659 x_PROGRAM_FLAG IN VARCHAR2,
660 x_PROGRAM_OFFERING_OPTION_FLAG IN VARCHAR2,
661 x_UNIT_FLAG IN VARCHAR2,
662 x_UNIT_SECTION_FLAG IN VARCHAR2,
663 x_UNIT_SECTION_OCCURRENCE_FLAG IN VARCHAR2,
664 x_mandatory_flag IN VARCHAR2 DEFAULT NULL ,
665 x_restricted_flag IN VARCHAR2
666 ) as
667 /*************************************************************
668 Created By :
669 Date Created By :
670 Purpose :
671 Know limitations, enhancements or remarks
672 Change History
673 Who When What
674 sbeerell 09-MAY-2000 Changed according to DLD version 2
675 (reverse chronological order - newest change first)
676 ***************************************************************/
677
678 cursor c1 is select
679 SELF_SERVICE_FLAG,
680 DESCRIPTION,
681 S_REFERENCE_CD_TYPE,
682 CLOSED_IND,
683 PROGRAM_FLAG,
684 PROGRAM_OFFERING_OPTION_FLAG,
685 UNIT_FLAG,
686 UNIT_SECTION_FLAG,
687 UNIT_SECTION_OCCURRENCE_FLAG,
688 MANDATORY_FLAG,
689 RESTRICTED_FLAG
690 from IGS_GE_REF_CD_TYPE_ALL
691 where ROWID = X_ROWID
692 for update nowait;
693 tlinfo c1%rowtype;
694
695 begin
696 open c1;
697 fetch c1 into tlinfo;
698 if (c1%notfound) then
699 close c1;
700 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
701 IGS_GE_MSG_STACK.ADD;
702 app_exception.raise_exception;
703 return;
704 end if;
705 close c1;
706
707 if((tlinfo.SELF_SERVICE_FLAG = X_SELF_SERVICE_FLAG)
708 AND (tlinfo.DESCRIPTION = X_DESCRIPTION)
709 AND (tlinfo.S_REFERENCE_CD_TYPE = X_S_REFERENCE_CD_TYPE)
710 AND (tlinfo.CLOSED_IND = X_CLOSED_IND)
711 AND (tlinfo.PROGRAM_FLAG = X_PROGRAM_FLAG)
712 AND (tlinfo.PROGRAM_OFFERING_OPTION_FLAG = X_PROGRAM_OFFERING_OPTION_FLAG)
713 AND (tlinfo.UNIT_FLAG = X_UNIT_FLAG)
714 AND (tlinfo.UNIT_SECTION_FLAG = X_UNIT_SECTION_FLAG)
715 AND (tlinfo.UNIT_SECTION_OCCURRENCE_FLAG = X_UNIT_SECTION_OCCURRENCE_FLAG)
716 AND ((tlinfo.mandatory_flag = x_mandatory_flag)
717 OR ((tlinfo.mandatory_flag IS NULL)
718 AND (X_mandatory_flag IS NULL)))
719 AND ((tlinfo.restricted_flag = x_restricted_flag)
720 OR ((tlinfo.restricted_flag IS NULL)
721 AND (X_restricted_flag IS NULL)))
722 ) then
723 null;
724 else
725 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
726 IGS_GE_MSG_STACK.ADD;
727 app_exception.raise_exception;
728 end if;
729 return;
730 end LOCK_ROW;
731
732 procedure UPDATE_ROW (
733 X_ROWID in VARCHAR2,
734 x_SELF_SERVICE_FLAG IN VARCHAR2,
735 x_REFERENCE_CD_TYPE IN VARCHAR2,
736 x_DESCRIPTION IN VARCHAR2,
737 x_S_REFERENCE_CD_TYPE IN VARCHAR2,
738 x_CLOSED_IND IN VARCHAR2,
739 x_PROGRAM_FLAG IN VARCHAR2,
740 x_PROGRAM_OFFERING_OPTION_FLAG IN VARCHAR2,
741 x_UNIT_FLAG IN VARCHAR2,
742 x_UNIT_SECTION_FLAG IN VARCHAR2,
743 x_UNIT_SECTION_OCCURRENCE_FLAG IN VARCHAR2,
744 X_MODE in VARCHAR2 default 'R',
745 x_mandatory_flag IN VARCHAR2 DEFAULT NULL,
746 x_restricted_flag IN VARCHAR2
747 ) as
748 /*************************************************************
749 Created By :
750 Date Created By :
751 Purpose :
752 Know limitations, enhancements or remarks
753 Change History
754 Who When What
755 sbeerell 09-MAY-2000 Changed according to DLD version 2
756 (reverse chronological order - newest change first)
757 ***************************************************************/
758
759 X_LAST_UPDATE_DATE DATE;
760 X_LAST_UPDATED_BY NUMBER;
761 X_LAST_UPDATE_LOGIN NUMBER;
762 begin
763 X_LAST_UPDATE_DATE := SYSDATE;
764 if(X_MODE = 'I') then
765 X_LAST_UPDATED_BY := 1;
766 X_LAST_UPDATE_LOGIN := 0;
767 elsif (X_MODE = 'R') then
768 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
769 if X_LAST_UPDATED_BY is NULL then
770 X_LAST_UPDATED_BY := -1;
771 end if;
772 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
773 if X_LAST_UPDATE_LOGIN is NULL then
774 X_LAST_UPDATE_LOGIN := -1;
775 end if;
776 else
777 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
778 IGS_GE_MSG_STACK.ADD;
779 app_exception.raise_exception;
780 end if;
781
782 Before_DML (
783 p_action => 'UPDATE',
784 x_rowid => X_ROWID,
785 x_self_service_flag=>X_SELF_SERVICE_FLAG,
786 x_reference_cd_type=>X_REFERENCE_CD_TYPE,
787 x_description=>X_DESCRIPTION,
788 x_s_reference_cd_type=>X_S_REFERENCE_CD_TYPE,
789 x_closed_ind=>NVL(X_CLOSED_IND,'N' ),
790 x_program_flag=>X_PROGRAM_FLAG,
791 x_program_offering_option_flag=>X_PROGRAM_OFFERING_OPTION_FLAG,
792 x_unit_flag=>X_UNIT_FLAG,
793 x_unit_section_flag=>X_UNIT_SECTION_FLAG,
794 x_unit_section_occurrence_flag=>X_UNIT_SECTION_OCCURRENCE_FLAG,
795 x_creation_date=>X_LAST_UPDATE_DATE,
796 x_created_by=>X_LAST_UPDATED_BY,
797 x_last_update_date=>X_LAST_UPDATE_DATE,
798 x_last_updated_by=>X_LAST_UPDATED_BY,
799 x_last_update_login=>X_LAST_UPDATE_LOGIN,
800
801 x_mandatory_flag =>X_MANDATORY_FLAG,
802 x_restricted_flag => x_restricted_flag
803 );
804
805 update IGS_GE_REF_CD_TYPE_ALL set
806 SELF_SERVICE_FLAG = NEW_REFERENCES.SELF_SERVICE_FLAG,
807 DESCRIPTION = NEW_REFERENCES.DESCRIPTION,
808 S_REFERENCE_CD_TYPE = NEW_REFERENCES.S_REFERENCE_CD_TYPE,
809 CLOSED_IND = NEW_REFERENCES.CLOSED_IND,
810 PROGRAM_FLAG = NEW_REFERENCES.PROGRAM_FLAG,
811 PROGRAM_OFFERING_OPTION_FLAG = NEW_REFERENCES.PROGRAM_OFFERING_OPTION_FLAG,
812 UNIT_FLAG = NEW_REFERENCES.UNIT_FLAG,
813 UNIT_SECTION_FLAG = NEW_REFERENCES.UNIT_SECTION_FLAG,
814 UNIT_SECTION_OCCURRENCE_FLAG = NEW_REFERENCES.UNIT_SECTION_OCCURRENCE_FLAG,
815 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
816 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
817 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
818 MANDATORY_FLAG = NEW_REFERENCES.MANDATORY_FLAG,
819 RESTRICTED_FLAG = NEW_REFERENCES.RESTRICTED_FLAG
820 where ROWID = X_ROWID;
821 if (sql%notfound) then
822 raise no_data_found;
823 end if;
824 After_DML (
825 p_action => 'UPDATE',
826 x_rowid => X_ROWID
827 );
828 end UPDATE_ROW;
829
830 procedure ADD_ROW (
831 X_ROWID in out NOCOPY VARCHAR2,
832 x_SELF_SERVICE_FLAG IN VARCHAR2,
833 x_REFERENCE_CD_TYPE IN VARCHAR2,
834 x_DESCRIPTION IN VARCHAR2,
835 x_S_REFERENCE_CD_TYPE IN VARCHAR2,
836 x_CLOSED_IND IN VARCHAR2,
837 x_PROGRAM_FLAG IN VARCHAR2,
838 x_PROGRAM_OFFERING_OPTION_FLAG IN VARCHAR2,
839 x_UNIT_FLAG IN VARCHAR2,
840 x_UNIT_SECTION_FLAG IN VARCHAR2,
841 x_UNIT_SECTION_OCCURRENCE_FLAG IN VARCHAR2,
842 X_MODE in VARCHAR2 default 'R',
843 X_ORG_ID in NUMBER,
844 X_mandatory_flag IN VARCHAR2 DEFAULT NULL,
845 x_restricted_flag IN VARCHAR2
846 ) as
847 /*************************************************************
848 Created By :
849 Date Created By :
850 Purpose :
851 Know limitations, enhancements or remarks
852 Change History
853 Who When What
854 sbeerell 09-MAY-2000 Changed according to DLD version 2
855 (reverse chronological order - newest change first)
856 ***************************************************************/
857
858 cursor c1 is select rowid from IGS_GE_REF_CD_TYPE_ALL
859 where ROWID = X_ROWID
860 ;
861
862 begin
863 open c1;
864 fetch c1 into X_ROWID;
865 if (c1%notfound) then
866 close c1;
867 INSERT_ROW (
868 X_ROWID,
869 X_SELF_SERVICE_FLAG,
870 X_REFERENCE_CD_TYPE,
871 X_DESCRIPTION,
872 X_S_REFERENCE_CD_TYPE,
873 X_CLOSED_IND,
874 X_PROGRAM_FLAG,
875 X_PROGRAM_OFFERING_OPTION_FLAG,
876 X_UNIT_FLAG,
877 X_UNIT_SECTION_FLAG,
878 X_UNIT_SECTION_OCCURRENCE_FLAG,
879 X_MODE,
880 X_ORG_ID,
881 x_MANDATORY_FLAG,
882 x_RESTRICTED_FLAG);
883
884 return;
885 end if;
886 close c1;
887 UPDATE_ROW (
888 X_ROWID,
889 X_SELF_SERVICE_FLAG,
890 X_REFERENCE_CD_TYPE,
891 X_DESCRIPTION,
892 X_S_REFERENCE_CD_TYPE,
893 X_CLOSED_IND,
894 X_PROGRAM_FLAG,
895 X_PROGRAM_OFFERING_OPTION_FLAG,
896 X_UNIT_FLAG,
897 X_UNIT_SECTION_FLAG,
898 X_UNIT_SECTION_OCCURRENCE_FLAG,
899 X_MODE,
900 x_MANDATORY_FLAG ,
901 x_RESTRICTED_FLAG);
902
903 end ADD_ROW;
904
905 procedure DELETE_ROW (
906 X_ROWID in VARCHAR2
907 ) as
908 begin
909 Before_DML (
910 p_action => 'DELETE',
911 x_rowid => X_ROWID
912 );
913
914 delete from IGS_GE_REF_CD_TYPE_ALL
915 where ROWID = X_ROWID;
916 if (sql%notfound) then
917 raise no_data_found;
918 end if;
919 After_DML (
920 p_action => 'DELETE',
921 x_rowid => X_ROWID
922 );
923
924 end DELETE_ROW;
925
926 end IGS_GE_REF_CD_TYPE_PKG;
[Home] [Help]