[Home] [Help]
PACKAGE BODY: APPS.IGS_PS_UNIT_OFR_PAT_PKG
Source
1 package body IGS_PS_UNIT_OFR_PAT_PKG as
2 /* $Header: IGSPI87B.pls 120.1 2005/06/29 05:05:25 appldev ship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_PS_UNIT_OFR_PAT_ALL%RowType;
6 new_references IGS_PS_UNIT_OFR_PAT_ALL%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_ci_start_dt IN DATE DEFAULT NULL,
16 x_ci_end_dt IN DATE DEFAULT NULL,
17 x_waitlist_allowed IN VARCHAR2 DEFAULT NULL,
18 x_max_students_per_waitlist IN NUMBER DEFAULT NULL,
19 x_creation_date IN DATE DEFAULT NULL,
20 x_created_by IN NUMBER DEFAULT NULL,
21 x_last_update_date IN DATE DEFAULT NULL,
22 x_last_updated_by IN NUMBER DEFAULT NULL,
23 x_last_update_login IN NUMBER DEFAULT NULL,
24 x_org_id IN NUMBER DEFAULT NULL,
25 X_DELETE_FLAG IN VARCHAR2 ,
26 x_abort_flag IN VARCHAR2
27 ) AS
28
29 CURSOR cur_old_ref_values IS
30 SELECT *
31 FROM IGS_PS_UNIT_OFR_PAT_ALL
32 WHERE rowid = x_rowid;
33
34 BEGIN
35
36 l_rowid := x_rowid;
37
38 -- Code for setting the Old and New Reference Values.
39 -- Populate Old Values.
40 Open cur_old_ref_values;
41 Fetch cur_old_ref_values INTO old_references;
42 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
43 Close cur_old_ref_values;
44 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
45 IGS_GE_MSG_STACK.ADD;
46 App_Exception.Raise_Exception;
47 Return;
48 END IF;
49 Close cur_old_ref_values;
50
51 -- Populate New Values.
52 new_references.unit_cd := x_unit_cd;
53 new_references.version_number := x_version_number;
54 new_references.cal_type := x_cal_type;
55 new_references.ci_sequence_number := x_ci_sequence_number;
56 new_references.ci_start_dt := x_ci_start_dt;
57 new_references.ci_end_dt := x_ci_end_dt;
58 new_references.waitlist_allowed := x_waitlist_allowed;
59 new_references.max_students_per_waitlist := x_max_students_per_waitlist;
60 IF (p_action = 'UPDATE') THEN
61 new_references.creation_date := old_references.creation_date;
62 new_references.created_by := old_references.created_by;
63 ELSE
64 new_references.creation_date := x_creation_date;
65 new_references.created_by := x_created_by;
66 END IF;
67 new_references.last_update_date := x_last_update_date;
68 new_references.last_updated_by := x_last_updated_by;
69 new_references.last_update_login := x_last_update_login;
70 new_references.org_id := x_org_id;
71 new_references.delete_flag := x_delete_flag;
72 new_references.abort_flag := x_abort_flag;
73
74 END Set_Column_Values;
75
76 PROCEDURE BeforeRowInsertUpdateDelete1(
77 p_inserting IN BOOLEAN DEFAULT FALSE,
78 p_updating IN BOOLEAN DEFAULT FALSE,
79 p_deleting IN BOOLEAN DEFAULT FALSE
80 ) AS
81 v_unit_cd IGS_PS_UNIT_OFR_PAT_ALL.unit_cd%TYPE;
82 v_version_number IGS_PS_UNIT_OFR_PAT_ALL.version_number%TYPE;
83 v_message_name VARCHAR2(30);
84 BEGIN
85 -- Set variables.
86 IF p_deleting THEN
87 v_unit_cd := old_references.unit_cd;
88 v_version_number := old_references.version_number;
89 ELSE -- p_inserting or p_updating
90 v_unit_cd := new_references.unit_cd;
91 v_version_number := new_references.version_number;
92 END IF;
93 -- Validate the insert/update/delete.
94 IF IGS_PS_VAL_UNIT.crsp_val_iud_uv_dtl (
95 v_unit_cd,
96 v_version_number,
97 v_message_name) = FALSE THEN
98 Fnd_Message.Set_Name('IGS',v_message_name);
99 IGS_GE_MSG_STACK.ADD;
100 App_Exception.Raise_Exception;
101 END IF;
102 IF p_inserting THEN
103 -- Validate the calendar instance status.
104 IF IGS_aS_VAL_uai.crsp_val_crs_ci (
105 new_references.cal_type,
106 new_references.ci_sequence_number,
107 v_message_name) = FALSE THEN
108 Fnd_Message.Set_Name('IGS',v_message_name);
109 IGS_GE_MSG_STACK.ADD;
110 App_Exception.Raise_Exception;
111 END IF;
112 -- Validate calendar type.
113 -- As part of the bug# 1956374 changed to the below call from IGS_PS_VAL_UOp.crsp_val_uo_cal_type
114 IF IGS_AS_VAL_UAI.crsp_val_uo_cal_type (
115 new_references.cal_type,
116 v_message_name) = FALSE THEN
117 Fnd_Message.Set_Name('IGS',v_message_name);
118 IGS_GE_MSG_STACK.ADD;
119 App_Exception.Raise_Exception;
120 END IF;
121 END IF;
122
123
124 END BeforeRowInsertUpdateDelete1;
125
126 PROCEDURE Check_Constraints(
127 Column_Name IN VARCHAR2 DEFAULT NULL,
128 Column_Value IN VARCHAR2 DEFAULT NULL)
129 AS
130 BEGIN
131
132 IF Column_Name IS NULL Then
133 NULL;
134 ELSIF UPPER(column_name)='CAL_TYPE' Then
135 New_References.Cal_Type := Column_Value;
136 ELSIF UPPER(column_name)='UNIT_CD' Then
137 New_References.Unit_Cd := Column_Value;
138 ELSIF UPPER(column_name)='WAITLIST_ALLOWED' Then
139 New_References.waitlist_allowed := Column_Value;
140 ELSIF UPPER(column_name)='MAX_STUDENTS_PER_WAITLIST' Then
141 New_References.max_students_per_waitlist := Column_Value;
142 ELSIF UPPER(column_name)='DELETE_FLAG' THEN
143 New_References.delete_flag := Column_Value;
144 END IF;
145
146 IF UPPER(column_name)='CAL_TYPE' OR Column_Name IS NULL Then
147 IF New_References.Cal_Type <> UPPER(New_References.Cal_Type) Then
148 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
149 IGS_GE_MSG_STACK.ADD;
150 App_Exception.Raise_Exception;
151 END IF;
152 END IF;
153
154 IF UPPER(column_name)='UNIT_CD' OR Column_Name IS NULL Then
155 IF New_References.Unit_Cd <> UPPER(New_References.Unit_CD) 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
162 IF UPPER(column_name)='WAITLIST_ALLOWED' OR Column_Name IS NULL Then
163 IF New_References.waitlist_allowed NOT IN ( 'Y' , 'N' ) Then
164 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
165 IGS_GE_MSG_STACK.ADD;
166 App_Exception.Raise_Exception;
167 END IF;
168 END IF;
169
170 IF UPPER(column_name)='DELETE_FLAG' OR Column_Name IS NULL THEN
171 IF New_References.delete_flag NOT IN ( 'Y' , 'N' ) THEN
172 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
173 IGS_GE_MSG_STACK.ADD;
174 App_Exception.Raise_Exception;
175 END IF;
176 END IF;
177
178 IF UPPER(column_name)='ABORT_FLAG' OR Column_Name IS NULL THEN
179 IF New_References.abort_flag NOT IN ( 'Y' , 'N' ) THEN
180 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
181 IGS_GE_MSG_STACK.ADD;
182 App_Exception.Raise_Exception;
183 END IF;
184 END IF;
185
186
187
188 IF UPPER(column_name)='MAX_STUDENTS_PER_WAITLIST' OR Column_Name IS NULL Then
189 IF New_References.MAX_STUDENTS_PER_WAITLIST < 0 OR New_References.MAX_STUDENTS_PER_WAITLIST > 999999 Then
190 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
191 IGS_GE_MSG_STACK.ADD;
192 App_Exception.Raise_Exception;
193 END IF;
194 END IF;
195
196 IF Column_name is NULL THEN
197 /* check for NOT NULL constraint in two new columns added in 115 */
198 IF (new_references.waitlist_allowed is NULL)THEN
199 Fnd_Message.Set_Name ('IGS', 'IGS_PS_MAND_WLST_ALLOW');
200 IGS_GE_MSG_STACK.ADD;
201 App_Exception.Raise_Exception;
202 END IF;
203
204 IF (new_references.max_students_per_waitlist is NULL)THEN
205 Fnd_Message.Set_Name ('IGS', 'IGS_PS_MAND_MAX_STDNT_WLST');
206 IGS_GE_MSG_STACK.ADD;
207 App_Exception.Raise_Exception;
208 END IF;
209 END IF;
210
211 END Check_Constraints;
212
213 PROCEDURE Check_Parent_Existance AS
214 BEGIN
215
216 IF (((old_references.cal_type = new_references.cal_type) AND
217 (old_references.ci_sequence_number = new_references.ci_sequence_number) AND
218 (old_references.ci_start_dt = new_references.ci_start_dt) AND
219 (old_references.ci_end_dt = new_references.ci_end_dt)) OR
220 ((new_references.cal_type IS NULL) OR
221 (new_references.ci_sequence_number IS NULL) OR
222 (new_references.ci_start_dt IS NULL) OR
223 (new_references.ci_end_dt IS NULL))) THEN
224 NULL;
225 ELSE
226 IF NOT IGS_CA_INST_PKG.Get_UK_For_Validation (
227 new_references.cal_type,
228 new_references.ci_sequence_number,
229 new_references.ci_start_dt,
230 new_references.ci_end_dt) THEN
231 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
232 IGS_GE_MSG_STACK.ADD;
233 App_Exception.Raise_Exception;
234 END IF;
235
236 END IF;
237
238 IF (((old_references.unit_cd = new_references.unit_cd) AND
239 (old_references.version_number = new_references.version_number) AND
240 (old_references.cal_type = new_references.cal_type)) OR
241 ((new_references.unit_cd IS NULL) OR
242 (new_references.version_number IS NULL) OR
243 (new_references.cal_type IS NULL))) THEN
244 NULL;
245 ELSE
246 IF NOT IGS_PS_UNIT_OFR_PKG.Get_PK_For_Validation (
247 new_references.unit_cd,
248 new_references.version_number,
249 new_references.cal_type) THEN
250 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
251 IGS_GE_MSG_STACK.ADD;
252 App_Exception.Raise_Exception;
253 END IF;
254
255
256 END IF;
257
258 END Check_Parent_Existance;
259
260 PROCEDURE Check_Child_Existance AS
261 BEGIN
262
263 IGS_AD_PS_APLINSTUNT_PKG.GET_FK_IGS_PS_UNIT_OFR_PAT (
264 old_references.unit_cd,
265 old_references.version_number,
266 old_references.cal_type,
267 old_references.ci_sequence_number
268 );
269
270 IGS_AS_MARK_SHEET_PKG.GET_FK_IGS_PS_UNIT_OFR_PAT (
271 old_references.unit_cd,
272 old_references.version_number,
273 old_references.cal_type,
274 old_references.ci_sequence_number
275 );
276
277 IGS_AS_NON_ENR_STDOT_PKG.GET_FK_IGS_PS_UNIT_OFR_PAT (
278 old_references.unit_cd,
279 old_references.version_number,
280 old_references.cal_type,
281 old_references.ci_sequence_number
282 );
283
284 IGS_AS_UNITASS_ITEM_PKG.GET_FK_IGS_PS_UNIT_OFR_PAT (
285 old_references.unit_cd,
286 old_references.version_number,
287 old_references.cal_type,
288 old_references.ci_sequence_number
289 );
290
291
292 IGS_PS_UNIT_OFR_OPT_PKG.GET_FK_IGS_PS_UNIT_OFR_PAT (
293 old_references.unit_cd,
294 old_references.version_number,
295 old_references.cal_type,
296 old_references.ci_sequence_number
297 );
298
299 IGS_PS_UNT_OFR_PAT_N_PKG.GET_FK_IGS_PS_UNIT_OFR_PAT (
300 old_references.unit_cd,
301 old_references.version_number,
302 old_references.cal_type,
303 old_references.ci_sequence_number
304 );
305
306 IGS_PS_UOFR_WLST_PRI_PKG.GET_FK_IGS_PS_UNIT_OFR_PAT (
307 old_references.unit_cd,
308 old_references.version_number,
309 old_references.cal_type,
310 old_references.ci_sequence_number
311 );
312 IGS_PS_RSV_UOP_PRI_PKG.GET_FK_IGS_PS_UNIT_OFR_PAT(
313 old_references.unit_cd,
314 old_references.version_number,
315 old_references.cal_type,
316 old_references.ci_sequence_number);
317
318
319 END Check_Child_Existance;
320
321 FUNCTION Get_PK_For_Validation (
322 x_unit_cd IN VARCHAR2,
323 x_version_number IN NUMBER,
324 x_cal_type IN VARCHAR2,
325 x_ci_sequence_number IN NUMBER
326 ) RETURN BOOLEAN AS
327
328 CURSOR cur_rowid IS
329 SELECT rowid
330 FROM IGS_PS_UNIT_OFR_PAT_ALL
331 WHERE unit_cd = x_unit_cd
332 AND version_number = x_version_number
333 AND cal_type = x_cal_type
334 AND ci_sequence_number = x_ci_sequence_number
335 AND delete_flag = 'N' ;
336 lv_rowid cur_rowid%RowType;
337
338 BEGIN
339
340 Open cur_rowid;
341 Fetch cur_rowid INTO lv_rowid;
342 IF (cur_rowid%FOUND) THEN
343 Close cur_rowid;
344 Return(TRUE);
345 ELSE
346 Close cur_rowid;
347 Return(FALSE);
348 END IF;
349
350 END Get_PK_For_Validation;
351
352 PROCEDURE GET_UFK_IGS_CA_INST (
353 x_cal_type IN VARCHAR2,
354 x_sequence_number IN NUMBER,
355 x_start_dt IN DATE,
356 x_end_dt IN DATE
357 ) AS
358
359 CURSOR cur_rowid IS
360 SELECT rowid
361 FROM IGS_PS_UNIT_OFR_PAT_ALL
362 WHERE cal_type = x_cal_type
363 AND ci_sequence_number = x_sequence_number
364 AND ci_start_dt = x_start_dt
365 AND ci_end_dt = x_end_dt
366 AND delete_flag = 'N';
367
368 lv_rowid cur_rowid%RowType;
369
370 BEGIN
371
372 Open cur_rowid;
373 Fetch cur_rowid INTO lv_rowid;
374 IF (cur_rowid%FOUND) THEN
375 Close cur_rowid;
376 Fnd_Message.Set_Name ('IGS', 'IGS_PS_UOP_CI_UFK');
377 IGS_GE_MSG_STACK.ADD;
378 App_Exception.Raise_Exception;
379 Return;
380 END IF;
381 Close cur_rowid;
382
383 END GET_UFK_IGS_CA_INST;
384
385 PROCEDURE GET_FK_IGS_PS_UNIT_OFR (
386 x_unit_cd IN VARCHAR2,
387 x_version_number IN NUMBER,
388 x_cal_type IN VARCHAR2
389 ) AS
390
391 CURSOR cur_rowid IS
392 SELECT rowid
393 FROM IGS_PS_UNIT_OFR_PAT_ALL
394 WHERE unit_cd = x_unit_cd
395 AND version_number = x_version_number
396 AND cal_type = x_cal_type
397 AND delete_flag = 'N';
398
399 lv_rowid cur_rowid%RowType;
400
401 BEGIN
402
403 Open cur_rowid;
404 Fetch cur_rowid INTO lv_rowid;
405 IF (cur_rowid%FOUND) THEN
406 Close cur_rowid;
407 Fnd_Message.Set_Name ('IGS', 'IGS_PS_UOP_UO_FK');
408 IGS_GE_MSG_STACK.ADD;
409 App_Exception.Raise_Exception;
410 Return;
411 END IF;
412 Close cur_rowid;
413
414 END GET_FK_IGS_PS_UNIT_OFR;
415
416 PROCEDURE Before_DML (
417 p_action IN VARCHAR2,
418 x_rowid IN VARCHAR2 DEFAULT NULL,
419 x_unit_cd IN VARCHAR2 DEFAULT NULL,
420 x_version_number IN NUMBER DEFAULT NULL,
421 x_cal_type IN VARCHAR2 DEFAULT NULL,
422 x_ci_sequence_number IN NUMBER DEFAULT NULL,
423 x_ci_start_dt IN DATE DEFAULT NULL,
424 x_ci_end_dt IN DATE DEFAULT NULL,
425 x_waitlist_allowed IN VARCHAR2 DEFAULT NULL,
426 x_max_students_per_waitlist IN NUMBER DEFAULT NULL,
427 x_creation_date IN DATE DEFAULT NULL,
428 x_created_by IN NUMBER DEFAULT NULL,
429 x_last_update_date IN DATE DEFAULT NULL,
430 x_last_updated_by IN NUMBER DEFAULT NULL,
431 x_last_update_login IN NUMBER DEFAULT NULL,
432 x_org_id IN NUMBER DEFAULT NULL,
433 X_DELETE_FLAG IN VARCHAR2 ,
434 x_abort_flag IN VARCHAR2
435 ) AS
436 BEGIN
437
438 Set_Column_Values (
439 p_action,
440 x_rowid,
441 x_unit_cd,
442 x_version_number,
443 x_cal_type,
444 x_ci_sequence_number,
445 x_ci_start_dt,
446 x_ci_end_dt,
447 x_waitlist_allowed,
448 x_max_students_per_waitlist,
449 x_creation_date,
450 x_created_by,
451 x_last_update_date,
452 x_last_updated_by,
453 x_last_update_login ,
454 x_org_id,
455 x_delete_flag,
456 x_abort_flag
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.cal_type,
465 New_References.ci_sequence_number ) THEN
466 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
467 IGS_GE_MSG_STACK.ADD;
468 App_Exception.Raise_Exception;
469 END IF;
470 Check_Constraints;
471 Check_Parent_Existance;
472 ELSIF (p_action = 'UPDATE') THEN
473 -- Call all the procedures related to Before Update.
474 BeforeRowInsertUpdateDelete1 ( p_updating => TRUE );
475 Check_Constraints;
476 Check_Parent_Existance;
477 ELSIF (p_action = 'DELETE') THEN
478 -- Call all the procedures related to Before Delete.
479 BeforeRowInsertUpdateDelete1 ( p_deleting => TRUE );
480 Check_Child_Existance;
481 ELSIF (p_action = 'VALIDATE_INSERT') THEN
482 IF Get_PK_For_Validation (New_References.unit_cd,
483 New_References.version_number,
484 New_References.cal_type,
485 New_References.ci_sequence_number ) THEN
486 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
487 IGS_GE_MSG_STACK.ADD;
488 App_Exception.Raise_Exception;
489 END IF;
490 Check_Constraints;
491 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
492 Check_Constraints;
493 ELSIF (p_action = 'VALIDATE_DELETE') THEN
494 Check_Child_Existance;
495 END IF;
496
497 END Before_DML;
498
499 PROCEDURE After_DML (
500 p_action IN VARCHAR2,
501 x_rowid IN VARCHAR2
502 ) AS
503 BEGIN
504
505 l_rowid := x_rowid;
506
507
508
509 END After_DML;
510
511
512 procedure INSERT_ROW (
513 X_ROWID in out NOCOPY VARCHAR2,
514 X_UNIT_CD in VARCHAR2,
515 X_VERSION_NUMBER in NUMBER,
516 X_CI_SEQUENCE_NUMBER in NUMBER,
517 X_CAL_TYPE in VARCHAR2,
518 X_CI_START_DT in DATE,
519 X_CI_END_DT in DATE,
520 X_WAITLIST_ALLOWED in VARCHAR2,
521 X_MAX_STUDENTS_PER_WAITLIST in NUMBER,
522 X_MODE in VARCHAR2 default 'R',
523 X_ORG_ID in NUMBER,
524 X_DELETE_FLAG IN VARCHAR2 ,
525 x_abort_flag IN VARCHAR2
526 ) AS
527 cursor C is select ROWID from IGS_PS_UNIT_OFR_PAT_ALL
528 where UNIT_CD = X_UNIT_CD
529 and VERSION_NUMBER = X_VERSION_NUMBER
530 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
531 and CAL_TYPE = X_CAL_TYPE;
532 X_LAST_UPDATE_DATE DATE;
533 X_LAST_UPDATED_BY NUMBER;
534 X_LAST_UPDATE_LOGIN NUMBER;
535 X_REQUEST_ID NUMBER;
536 X_PROGRAM_ID NUMBER;
537 X_PROGRAM_APPLICATION_ID NUMBER;
538 X_PROGRAM_UPDATE_DATE DATE;
539 l_c_rowid ROWID;
540
541 begin
542 X_LAST_UPDATE_DATE := SYSDATE;
543 if(X_MODE = 'I') then
544 X_LAST_UPDATED_BY := 1;
545 X_LAST_UPDATE_LOGIN := 0;
546 elsif (X_MODE = 'R') then
547 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
548 if X_LAST_UPDATED_BY is NULL then
549 X_LAST_UPDATED_BY := -1;
550 end if;
551 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
552 if X_LAST_UPDATE_LOGIN is NULL then
553 X_LAST_UPDATE_LOGIN := -1;
554 end if;
555 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
556 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
557
558 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
559 if (X_REQUEST_ID = -1) then
560 X_REQUEST_ID := NULL;
561 X_PROGRAM_ID := NULL;
562 X_PROGRAM_APPLICATION_ID := NULL;
563 X_PROGRAM_UPDATE_DATE := NULL;
564 else
565 X_PROGRAM_UPDATE_DATE := SYSDATE;
566 end if;
567 else
568 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
569 IGS_GE_MSG_STACK.ADD;
570 app_exception.raise_exception;
571 end if;
572
573
574 Before_DML(
575 p_action => 'INSERT',
576 x_rowid => X_ROWID,
577 x_unit_cd => X_UNIT_CD,
578 x_version_number => X_VERSION_NUMBER,
579 x_cal_type => X_CAL_TYPE,
580 x_ci_sequence_number => X_CI_SEQUENCE_NUMBER,
581 x_ci_start_dt => X_CI_START_DT,
582 x_ci_end_dt => X_CI_END_DT,
583 x_waitlist_allowed => X_WAITLIST_ALLOWED,
584 x_max_students_per_waitlist => X_MAX_STUDENTS_PER_WAITLIST,
585 x_creation_date => X_LAST_UPDATE_DATE,
586 x_created_by => X_LAST_UPDATED_BY,
587 x_last_update_date => X_LAST_UPDATE_DATE,
588 x_last_updated_by => X_LAST_UPDATED_BY,
589 x_last_update_login => X_LAST_UPDATE_LOGIN,
590 x_org_id => igs_ge_gen_003.get_org_id,
591 x_delete_flag => x_delete_flag,
592 x_abort_flag => x_abort_flag
593 );
594
595 OPEN C;
596 FETCH C INTO l_c_rowid;
597 IF C%NOTFOUND THEN
598 CLOSE C;
599 INSERT INTO IGS_PS_UNIT_OFR_PAT_ALL (
600 UNIT_CD,
601 VERSION_NUMBER,
602 CAL_TYPE,
603 CI_SEQUENCE_NUMBER,
604 CI_START_DT,
605 CI_END_DT,
606 WAITLIST_ALLOWED,
607 MAX_STUDENTS_PER_WAITLIST,
608 CREATION_DATE,
609 CREATED_BY,
610 LAST_UPDATE_DATE,
611 LAST_UPDATED_BY,
612 LAST_UPDATE_LOGIN,
613 REQUEST_ID,
614 PROGRAM_ID,
615 PROGRAM_APPLICATION_ID,
616 PROGRAM_UPDATE_DATE,
617 ORG_ID,
618 DELETE_FLAG,
619 ABORT_FLAG
620 ) VALUES (
621 NEW_REFERENCES.UNIT_CD,
622 NEW_REFERENCES.VERSION_NUMBER,
623 NEW_REFERENCES.CAL_TYPE,
624 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
625 NEW_REFERENCES.CI_START_DT,
626 NEW_REFERENCES.CI_END_DT,
627 NEW_REFERENCES.WAITLIST_ALLOWED,
628 NEW_REFERENCES.MAX_STUDENTS_PER_WAITLIST,
629 X_LAST_UPDATE_DATE,
630 X_LAST_UPDATED_BY,
631 X_LAST_UPDATE_DATE,
632 X_LAST_UPDATED_BY,
633 X_LAST_UPDATE_LOGIN,
634 X_REQUEST_ID,
635 X_PROGRAM_ID,
636 X_PROGRAM_APPLICATION_ID,
637 X_PROGRAM_UPDATE_DATE,
638 NEW_REFERENCES.ORG_ID,
639 NEW_REFERENCES.DELETE_FLAG,
640 NEW_REFERENCES.ABORT_FLAG
641 );
642 ELSE
643 CLOSE C;
644 UPDATE IGS_PS_UNIT_OFR_PAT_ALL SET
645 WAITLIST_ALLOWED=NEW_REFERENCES.WAITLIST_ALLOWED,
646 MAX_STUDENTS_PER_WAITLIST=NEW_REFERENCES.MAX_STUDENTS_PER_WAITLIST,
647 CREATION_DATE=X_LAST_UPDATE_DATE,
648 CREATED_BY=X_LAST_UPDATED_BY,
649 LAST_UPDATE_DATE=X_LAST_UPDATE_DATE,
650 LAST_UPDATED_BY=X_LAST_UPDATED_BY,
651 LAST_UPDATE_LOGIN=X_LAST_UPDATE_LOGIN,
652 REQUEST_ID=X_REQUEST_ID,
653 PROGRAM_ID=X_PROGRAM_ID,
654 PROGRAM_APPLICATION_ID=X_PROGRAM_APPLICATION_ID,
655 PROGRAM_UPDATE_DATE=X_PROGRAM_UPDATE_DATE,
656 DELETE_FLAG = 'N',
657 abort_flag = new_references.abort_flag
658 WHERE ROWID=l_c_rowid;
659 END IF;
660
661
662 open c;
663 fetch c into X_ROWID;
664 if (c%notfound) then
665 close c;
666 raise no_data_found;
667 end if;
668 close c;
669 After_DML (
670 p_action => 'INSERT',
671 x_rowid => X_ROWID
672 );
673 end INSERT_ROW;
674
675 procedure LOCK_ROW (
676 X_ROWID in VARCHAR2,
677 X_UNIT_CD in VARCHAR2,
678 X_VERSION_NUMBER in NUMBER,
679 X_CI_SEQUENCE_NUMBER in NUMBER,
680 X_CAL_TYPE in VARCHAR2,
681 X_CI_START_DT in DATE,
682 X_CI_END_DT in DATE,
683 X_WAITLIST_ALLOWED in VARCHAR2,
684 X_MAX_STUDENTS_PER_WAITLIST in NUMBER,
685 X_DELETE_FLAG IN VARCHAR2 ,
686 x_abort_flag IN VARCHAR2
687 ) AS
688 cursor c1 is select
689 CI_START_DT,
690 CI_END_DT,
691 WAITLIST_ALLOWED,
692 MAX_STUDENTS_PER_WAITLIST,
693 DELETE_FLAG,
694 abort_flag
695 from IGS_PS_UNIT_OFR_PAT_ALL
696 where ROWID = X_ROWID for update nowait;
697 tlinfo c1%rowtype;
698
699 begin
700 open c1;
701 fetch c1 into tlinfo;
702 if (c1%notfound) then
703 close c1;
704 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
705 IGS_GE_MSG_STACK.ADD;
706 app_exception.raise_exception;
707 return;
708 end if;
709 close c1;
710
711 if ( (tlinfo.CI_START_DT = X_CI_START_DT)
712 AND (tlinfo.CI_END_DT = X_CI_END_DT)
713 AND ((tlinfo.WAITLIST_ALLOWED = X_WAITLIST_ALLOWED)
714 OR ((tlinfo.WAITLIST_ALLOWED IS NULL) AND (X_WAITLIST_ALLOWED IS NULL)))
715 AND ((tlinfo.DELETE_FLAG = X_DELETE_FLAG)
716 OR ((tlinfo.DELETE_FLAG IS NULL) AND (X_DELETE_FLAG IS NULL)))
717 AND ((tlinfo.MAX_STUDENTS_PER_WAITLIST = X_MAX_STUDENTS_PER_WAITLIST)
718 OR ((tlinfo.MAX_STUDENTS_PER_WAITLIST IS NULL) AND (X_MAX_STUDENTS_PER_WAITLIST IS NULL)))
719 AND (tlinfo.ABORT_FLAG = X_ABORT_FLAG)
720 ) then
721 null;
722 else
723 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
724 IGS_GE_MSG_STACK.ADD;
725 app_exception.raise_exception;
726 end if;
727 return;
728 end LOCK_ROW;
729
730 procedure UPDATE_ROW (
731 X_ROWID in VARCHAR2,
732 X_UNIT_CD in VARCHAR2,
733 X_VERSION_NUMBER in NUMBER,
734 X_CI_SEQUENCE_NUMBER in NUMBER,
735 X_CAL_TYPE in VARCHAR2,
736 X_CI_START_DT in DATE,
737 X_CI_END_DT in DATE,
738 X_WAITLIST_ALLOWED in VARCHAR2,
739 X_MAX_STUDENTS_PER_WAITLIST in NUMBER,
740 X_MODE in VARCHAR2 default 'R',
741 X_DELETE_FLAG IN VARCHAR2 ,
742 x_abort_flag IN VARCHAR2
743 ) AS
744 X_LAST_UPDATE_DATE DATE;
745 X_LAST_UPDATED_BY NUMBER;
746 X_LAST_UPDATE_LOGIN NUMBER;
747 X_REQUEST_ID NUMBER;
748 X_PROGRAM_ID NUMBER;
749 X_PROGRAM_APPLICATION_ID NUMBER;
750 X_PROGRAM_UPDATE_DATE DATE;
751 begin
752 X_LAST_UPDATE_DATE := SYSDATE;
753 if(X_MODE = 'I') then
754 X_LAST_UPDATED_BY := 1;
755 X_LAST_UPDATE_LOGIN := 0;
756 elsif (X_MODE = 'R') then
757 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
758 if X_LAST_UPDATED_BY is NULL then
759 X_LAST_UPDATED_BY := -1;
760 end if;
761 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
762 if X_LAST_UPDATE_LOGIN is NULL then
763 X_LAST_UPDATE_LOGIN := -1;
764 end if;
765 else
766 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
767 IGS_GE_MSG_STACK.ADD;
768 app_exception.raise_exception;
769 end if;
770
771 Before_DML(
772 p_action => 'UPDATE',
773 x_rowid => X_ROWID,
774 x_unit_cd => X_UNIT_CD,
775 x_version_number => X_VERSION_NUMBER,
776 x_cal_type => X_CAL_TYPE,
777 x_ci_sequence_number => X_CI_SEQUENCE_NUMBER,
778 x_ci_start_dt => X_CI_START_DT,
779 x_ci_end_dt => X_CI_END_DT,
780 x_waitlist_allowed => X_WAITLIST_ALLOWED,
781 x_max_students_per_waitlist => X_MAX_STUDENTS_PER_WAITLIST,
782 x_creation_date => X_LAST_UPDATE_DATE,
783 x_created_by => X_LAST_UPDATED_BY,
784 x_last_update_date => X_LAST_UPDATE_DATE,
785 x_last_updated_by => X_LAST_UPDATED_BY,
786 x_last_update_login => X_LAST_UPDATE_LOGIN,
787 x_delete_flag => X_DELETE_FLAG,
788 x_abort_flag => x_abort_flag
789 );
790
791 if (X_MODE = 'R') then
792 X_REQUEST_ID := FND_GLOBAL.CONC_REQUEST_ID;
793 X_PROGRAM_ID := FND_GLOBAL.CONC_PROGRAM_ID;
794 X_PROGRAM_APPLICATION_ID := FND_GLOBAL.PROG_APPL_ID;
795 if (X_REQUEST_ID = -1) then
796 X_REQUEST_ID := OLD_REFERENCES.REQUEST_ID;
797 X_PROGRAM_ID := OLD_REFERENCES. PROGRAM_ID;
798 X_PROGRAM_APPLICATION_ID := OLD_REFERENCES.PROGRAM_APPLICATION_ID;
799 X_PROGRAM_UPDATE_DATE := OLD_REFERENCES.PROGRAM_UPDATE_DATE;
800 else
801 X_PROGRAM_UPDATE_DATE := SYSDATE;
802 end if;
803 end if;
804
805 IF x_delete_flag = 'Y' THEN
806 Check_Child_Existance;
807 END IF;
808
809 update IGS_PS_UNIT_OFR_PAT_ALL set
810 CI_START_DT = NEW_REFERENCES.CI_START_DT,
811 CI_END_DT = NEW_REFERENCES.CI_END_DT,
812 WAITLIST_ALLOWED = NEW_REFERENCES.WAITLIST_ALLOWED,
813 MAX_STUDENTS_PER_WAITLIST = NEW_REFERENCES.MAX_STUDENTS_PER_WAITLIST,
814 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
815 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
816 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
817 REQUEST_ID = X_REQUEST_ID,
818 PROGRAM_ID = X_PROGRAM_ID,
819 PROGRAM_APPLICATION_ID = X_PROGRAM_APPLICATION_ID,
820 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
821 DELETE_FLAG = NEW_REFERENCES.DELETE_flag,
822 ABORT_FLAG = NEW_REFERENCES.ABORT_FLAG
823 where ROWID = X_ROWID
824 ;
825 if (sql%notfound) then
826 raise no_data_found;
827 end if;
828 After_DML (
829 p_action => 'UPDATE',
830 x_rowid => X_ROWID
831 );
832 end UPDATE_ROW;
833
834 procedure ADD_ROW (
835 X_ROWID in out NOCOPY VARCHAR2,
836 X_UNIT_CD in VARCHAR2,
837 X_VERSION_NUMBER in NUMBER,
838 X_CI_SEQUENCE_NUMBER in NUMBER,
839 X_CAL_TYPE in VARCHAR2,
840 X_CI_START_DT in DATE,
841 X_CI_END_DT in DATE,
842 X_WAITLIST_ALLOWED in VARCHAR2,
843 X_MAX_STUDENTS_PER_WAITLIST in NUMBER,
844 X_MODE in VARCHAR2 default 'R',
845 X_ORG_ID in NUMBER,
846 X_DELETE_FLAG IN VARCHAR2 ,
847 x_abort_flag IN VARCHAR2
848 ) AS
849 cursor c1 is select rowid from IGS_PS_UNIT_OFR_PAT_ALL
850 where UNIT_CD = X_UNIT_CD
851 and VERSION_NUMBER = X_VERSION_NUMBER
852 and CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER
853 and CAL_TYPE = X_CAL_TYPE
854 ;
855 begin
856 open c1;
857 fetch c1 into X_ROWID;
858 if (c1%notfound) then
859 close c1;
860 INSERT_ROW (
861 X_ROWID,
862 X_UNIT_CD,
863 X_VERSION_NUMBER,
864 X_CI_SEQUENCE_NUMBER,
865 X_CAL_TYPE,
866 X_CI_START_DT,
867 X_CI_END_DT,
868 X_WAITLIST_ALLOWED,
869 X_MAX_STUDENTS_PER_WAITLIST,
870 X_MODE,
871 X_ORG_ID,
872 X_DELETE_FLAG,
873 x_abort_flag);
874 return;
875 end if;
876 close c1;
877 UPDATE_ROW (
878 X_ROWID,
879 X_UNIT_CD,
880 X_VERSION_NUMBER,
881 X_CI_SEQUENCE_NUMBER,
882 X_CAL_TYPE,
883 X_CI_START_DT,
884 X_CI_END_DT,
885 X_WAITLIST_ALLOWED,
886 X_MAX_STUDENTS_PER_WAITLIST,
887 X_MODE,
888 X_DELETE_FLAG,
889 x_abort_flag
890 );
891 end ADD_ROW;
892
893 procedure DELETE_ROW (
894 X_ROWID in VARCHAR2
895 ) AS
896 begin
897 Before_DML (
898 p_action => 'DELETE',
899 x_rowid => X_ROWID
900 );
901 delete from IGS_PS_UNIT_OFR_PAT_ALL
902 where ROWID = X_ROWID
903 ;
904 if (sql%notfound) then
905 raise no_data_found;
906 end if;
907 After_DML (
908 p_action => 'DELETE',
909 x_rowid => X_ROWID
910 );
911
912 end DELETE_ROW;
913
914 end IGS_PS_UNIT_OFR_PAT_PKG;