[Home] [Help]
PACKAGE BODY: APPS.IGS_CA_INST_PKG
Source
1 PACKAGE BODY IGS_CA_INST_PKG AS
2 /* $Header: IGSCI12B.pls 120.0 2005/06/01 22:19:16 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_CA_INST_ALL%ROWTYPE;
6 new_references IGS_CA_INST_ALL%ROWTYPE;
7
8 -- Forward declaring the procedure beforerowdelete, beforerowupdate
9 PROCEDURE beforerowdelete;
10 PROCEDURE beforerowupdate;
11
12 PROCEDURE Set_Column_Values (
13 p_action IN VARCHAR2,
14 x_rowid IN VARCHAR2 ,
15 x_cal_type IN VARCHAR2 ,
16 x_sequence_number IN NUMBER ,
17 x_start_dt IN DATE ,
18 x_end_dt IN DATE ,
19 x_cal_status IN VARCHAR2 ,
20 x_alternate_code IN VARCHAR2 ,
21 x_sup_cal_status_differ_ind IN VARCHAR2 ,
22 x_prior_ci_sequence_number IN NUMBER ,
23 x_org_id IN NUMBER ,
24 x_creation_date IN DATE ,
25 x_created_by IN NUMBER ,
26 x_last_update_date IN DATE ,
27 x_last_updated_by IN NUMBER ,
28 x_last_update_login IN NUMBER ,
29 x_ss_displayed IN VARCHAR2 ,
30 x_description IN VARCHAR2 ,
31 x_ivr_display_ind IN VARCHAR2,
32 x_term_instruction_time IN NUMBER ,
33 X_PLANNING_FLAG in VARCHAR2 ,
34 X_SCHEDULE_FLAG in VARCHAR2 ,
35 X_ADMIN_FLAG in VARCHAR2
36 ) AS
37
38 CURSOR cur_old_ref_values IS
39 SELECT *
40 FROM IGS_CA_INST_ALL
41 WHERE ROWID = x_rowid;
42
43 BEGIN
44
45 l_rowid := x_rowid;
46
47 -- Code for setting the Old and New Reference Values.
48 -- Populate Old Values.
49 OPEN cur_old_ref_values;
50 FETCH cur_old_ref_values INTO old_references;
51 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT', 'VALIDATE_INSERT')) THEN
52 CLOSE cur_old_ref_values;
53 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
54 IGS_GE_MSG_STACK.ADD;
55 App_Exception.Raise_Exception;
56 RETURN;
57 END IF;
58 CLOSE cur_old_ref_values;
59
60 -- Populate New Values.
61 new_references.cal_type := x_cal_type;
62 new_references.sequence_number := x_sequence_number;
63 new_references.start_dt := x_start_dt;
64 new_references.end_dt := x_end_dt;
65 new_references.cal_status := x_cal_status;
66 new_references.alternate_code := x_alternate_code;
67 new_references.sup_cal_status_differ_ind := x_sup_cal_status_differ_ind;
68 new_references.prior_ci_sequence_number := x_prior_ci_sequence_number;
69 IF (p_action = 'UPDATE') THEN
70 new_references.creation_date := old_references.creation_date;
71 new_references.created_by := old_references.created_by;
72 ELSE
73 new_references.creation_date := x_creation_date;
74 new_references.created_by := x_created_by;
75 END IF;
76 new_references.org_id := x_org_id ;
77 new_references.last_update_date := x_last_update_date;
78 new_references.last_updated_by := x_last_updated_by;
79 new_references.last_update_login := x_last_update_login;
80 new_references.ss_displayed := x_ss_displayed;
81 new_references.ivr_display_ind := NVL(x_ivr_display_ind,'N');
82 new_references.term_instruction_time := x_term_instruction_time;
83 new_references.PLANNING_FLAG := NVL(X_PLANNING_FLAG,'N') ; --default N
84 new_references.SCHEDULE_FLAG := NVL(X_SCHEDULE_FLAG,'N') ; --default N
85 new_references.ADMIN_FLAG := NVL(X_ADMIN_FLAG,'N') ; --default N
86
87 --SINCE WE NEED TO COMMUNICATE TO THE USER THAT DESCRIPTION HAS TO BE SPECIFIED.
88 --WITH RESPECT TO THE SWCR003 CALENDAR DESCRIPTION -- CHANGE REQUEST
89 --Enh No :- 2138560 Change Request for Calendar Instance
90 --Add a Description Column
91
92 IF LTRIM(RTRIM(x_description)) IS NULL THEN
93
94 fnd_message.set_name('IGS','IGS_CA_CALDESC_NOT_AVAILABLE');
95 new_references.description :=fnd_message.get;
96
97 ELSE
98 new_references.description :=LTRIM(RTRIM(x_description));
99
100 END IF;
101 END Set_Column_Values;
102
103
104
105 PROCEDURE BeforeRowInsertUpdate1(
106 p_inserting IN BOOLEAN ,
107 p_updating IN BOOLEAN ,
108 p_deleting IN BOOLEAN
109 ) AS
110 -- BUG - 2563531
111 -- CURSOR added to check uniqueness for alternate code for calendar categories
112 -- load , academic and teaching
113 CURSOR alt_code_unique IS
114 SELECT count(*)
115 FROM IGS_CA_INST CI , IGS_CA_TYPE CAT
116 WHERE CAT.CAL_TYPE = CI.CAL_TYPE
117 AND CAT.S_CAL_CAT IN ('LOAD','TEACHING','ACADEMIC')
118 AND NEW_REFERENCES.ALTERNATE_CODE = CI.ALTERNATE_CODE
119 AND ((l_rowid IS NULL) OR (CI.ROW_ID <> l_rowid)) ;
120 l_count NUMBER(3);
121 v_message_name VARCHAR2(30);
122 BEGIN
123 -- Validate alternate code
124 IF IGS_CA_VAL_CI.calp_val_ci_alt_cd(
125 new_references.cal_type,
126 new_references.alternate_code,
127 v_message_name) = FALSE THEN
128 Fnd_Message.Set_Name('IGS',v_message_name);
129 IGS_GE_MSG_STACK.ADD;
130 APP_EXCEPTION.RAISE_EXCEPTION;
131
132 END IF;
133 IF p_inserting OR
134 (new_references.cal_status <> old_references.cal_status) THEN
135 -- Validate calendar status
136 IF IGS_CA_VAL_CI.calp_val_cs_closed(
137 new_references.cal_status,
138 v_message_name) = FALSE THEN
139 Fnd_Message.Set_Name('IGS',v_message_name);
140 IGS_GE_MSG_STACK.ADD;
141 APP_EXCEPTION.RAISE_EXCEPTION;
142 END IF;
143 END IF;
144 -- code to check uniqueness for alternate code for calendar categories
145 -- load ,teaching and academic
146 IF p_inserting OR p_updating THEN
147 OPEN alt_code_unique;
148 FETCH alt_code_unique INTO l_count;
149 IF l_count > 0 THEN
150 Fnd_Message.Set_Name('IGS','IGS_CA_UNIQUE_ALT_CODE');
151 IGS_GE_MSG_STACK.ADD;
152 APP_EXCEPTION.RAISE_EXCEPTION;
153 END IF;
154 CLOSE alt_code_unique;
155 END IF;
156
157 END BeforeRowInsertUpdate1;
158
159
160 PROCEDURE AfterRowInsertUpdate2(
161 p_inserting IN BOOLEAN ,
162 p_updating IN BOOLEAN ,
163 p_deleting IN BOOLEAN
164 ) AS
165 v_message_name VARCHAR2(30);
166 v_rowid_saved BOOLEAN := FALSE;
167 BEGIN
168 -- Validate calendar instance status
169 IF (new_references.cal_status <> old_references.cal_status)
170 THEN
171 -- partial call to calp_val_ci_status
172 IF IGS_CA_VAL_CI.calp_val_ci_status(p_cal_type => '',
173 p_sequence_number => NULL,
174 p_old_cal_status => old_references.cal_status,
175 p_new_cal_status => new_references.cal_status,
176 p_message_name => v_message_name) = FALSE
177 THEN
178 Fnd_Message.Set_Name('IGS',v_message_name);
179 IGS_GE_MSG_STACK.ADD;
180 APP_EXCEPTION.RAISE_EXCEPTION;
181 END IF;
182 END IF;
183 -- Check that the calendar type is not closed.
184 IF IGS_CA_GEN_001.CALP_GET_CAT_CLOSED (new_references.cal_type,
185 v_message_name) = TRUE
186 THEN
187 Fnd_Message.Set_Name('IGS',v_message_name);
188 IGS_GE_MSG_STACK.ADD;
189 APP_EXCEPTION.RAISE_EXCEPTION;
190 END IF;
191 END AfterRowInsertUpdate2;
192
193
194
195 PROCEDURE AfterStmtInsertUpdateDelete3(
196 p_inserting IN BOOLEAN ,
197 p_updating IN BOOLEAN ,
198 p_deleting IN BOOLEAN
199 ) AS
200 v_message_name VARCHAR2(30);
201 BEGIN
202 -- Validation routine calls.
203 -- Validate calendar instance status
204 IF p_inserting OR p_updating THEN
205 -- Validate calendar instance status
206 -- not all parameters are included in the call to calp_val_ci_status
207 IF IGS_CA_VAL_CI.calp_val_ci_status (p_cal_type => NVL (new_references.cal_type, old_references.cal_type),
208 p_sequence_number => NVL (new_references.sequence_number, old_references.sequence_number),
209 p_old_cal_status => '',
210 p_new_cal_status => NVL (new_references.cal_status, old_references.cal_status),
211 p_message_name => v_message_name) = FALSE
212 THEN
213 Fnd_Message.Set_Name('IGS',v_message_name);
214 IGS_GE_MSG_STACK.ADD;
215 APP_EXCEPTION.RAISE_EXCEPTION;
216 END IF;
217 END IF;
218 END AfterStmtInsertUpdateDelete3;
219
220 PROCEDURE Check_Constraints (
221 Column_Name IN VARCHAR2 ,
222 Column_Value IN VARCHAR2
223 ) AS
224 BEGIN
225 IF Column_Name IS NULL THEN
226 NULL;
227 ELSIF UPPER(Column_Name) = 'ALTERNATE_CODE' THEN
228 new_references.alternate_code := Column_Value;
229 ELSIF UPPER(Column_Name) = 'CAL_STATUS' THEN
230 new_references.cal_status := Column_Value;
231 ELSIF UPPER(Column_Name) = 'CAL_TYPE' THEN
232 new_references.cal_type:= Column_Value;
233 ELSIF UPPER(Column_Name) = 'SUP_CAL_STATUS_DIFFER_IND' THEN
234 new_references.sup_cal_status_differ_ind := Column_Value;
235 ELSIF UPPER(Column_Name) = 'PRIOR_CI_SEQUENCE_NUMBER' THEN
236 new_references.prior_ci_sequence_number := igs_ge_number.to_num(Column_Value);
237
238
239 END IF;
240 IF column_name IS NULL THEN
241 IF (new_references.start_dt > new_references.end_dt) THEN
242 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
243 IGS_GE_MSG_STACK.ADD;
244 App_Exception.Raise_Exception;
245 END IF;
246 END IF;
247 IF UPPER(Column_Name) = 'PRIOR_CI_SEQUENCE_NUMBER' OR
248 column_name IS NULL THEN
249 IF (new_references.prior_ci_sequence_number < 1 OR new_references.prior_ci_sequence_number > 999999) AND new_references.prior_ci_sequence_number IS NOT NULL THEN
250 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
251 IGS_GE_MSG_STACK.ADD;
252 App_Exception.Raise_Exception;
253 END IF;
254 END IF;
255 IF UPPER(Column_Name) = 'SUP_CAL_STATUS_DIFFER_IND' OR
256 column_name IS NULL THEN
257 IF new_references.sup_cal_status_differ_ind NOT IN ('Y', 'N') AND new_references.sup_cal_status_differ_ind IS NOT NULL THEN
258 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
259 IGS_GE_MSG_STACK.ADD;
260 App_Exception.Raise_Exception;
261 END IF;
262 END IF;
263 IF UPPER(Column_Name) = 'ALTERNATE_CODE' OR
264 column_name IS NULL THEN
265 IF new_references.alternate_code <> UPPER(new_references.alternate_code) AND new_references.alternate_code IS NOT NULL THEN
266 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
267 IGS_GE_MSG_STACK.ADD;
268 App_Exception.Raise_Exception;
269 END IF;
270 END IF;
271 IF UPPER(Column_Name) = 'CAL_STATUS' OR
272 column_name IS NULL THEN
273 IF (new_references.cal_status <> UPPER(new_references.cal_status)) AND new_references.cal_status IS NOT NULL THEN
274 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
275 IGS_GE_MSG_STACK.ADD;
276 App_Exception.Raise_Exception;
277 END IF;
278 END IF;
279 IF UPPER(Column_Name) = 'CAL_TYPE' OR
280 column_name IS NULL THEN
281 IF new_references.cal_type <> UPPER(new_references.cal_type) AND new_references.cal_type IS NOT NULL THEN
282 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
283 IGS_GE_MSG_STACK.ADD;
284 App_Exception.Raise_Exception;
285 END IF;
286 END IF;
287
288
289
290 END Check_Constraints;
291
292 PROCEDURE Check_Uniqueness
293 IS
294 BEGIN
295 IF Get_UK_For_Validation (
296 new_references.cal_type ,
297 new_references.sequence_number ,
298 new_references.start_dt ,
299 new_references.end_dt )
300 THEN
301 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
302 IGS_GE_MSG_STACK.ADD;
303 App_Exception.Raise_Exception;
304 END IF;
305 IF Get_UK2_For_Validation (
306 new_references.cal_type ,
307 new_references.start_dt ,
308 new_references.end_dt )
309 THEN
310 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
311 IGS_GE_MSG_STACK.ADD;
312 App_Exception.Raise_Exception;
313 END IF;
314
315 END Check_Uniqueness;
316
317
318 PROCEDURE Check_Parent_Existance AS
319 BEGIN
320
321 IF (((old_references.cal_type = new_references.cal_type)) OR
322 ((new_references.cal_type IS NULL))) THEN
323 NULL;
324 ELSIF NOT IGS_CA_TYPE_PKG.Get_PK_For_Validation (
325 new_references.cal_type
326 ) THEN
327 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
328 IGS_GE_MSG_STACK.ADD;
329 App_Exception.Raise_Exception;
330 END IF;
331
332 IF (((old_references.cal_status = new_references.cal_status)) OR
333 ((new_references.cal_status IS NULL))) THEN
334 NULL;
335 ELSIF NOT IGS_CA_STAT_PKG.Get_PK_For_Validation (
336 new_references.cal_status
337 ) THEN
338 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
339 IGS_GE_MSG_STACK.ADD;
340 App_Exception.Raise_Exception;
341 END IF;
342
343 IF (((old_references.cal_type = new_references.cal_type) AND
344 (old_references.prior_ci_sequence_number = new_references.prior_ci_sequence_number)) OR
345 ((new_references.cal_type IS NULL) OR
346 (new_references.prior_ci_sequence_number IS NULL))) THEN
347 NULL;
348 ELSIF NOT IGS_CA_INST_PKG.Get_PK_For_Validation (
349 new_references.cal_type,
350 new_references.prior_ci_sequence_number
351 ) THEN
352 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
353 IGS_GE_MSG_STACK.ADD;
354 App_Exception.Raise_Exception;
355 END IF;
356
357 END Check_Parent_Existance;
358
359 PROCEDURE Check_Child_Existance AS
360 ------------------------------------------------------------------
361 --Change History:
362 --Who When What
363 --rmaddipa 14-sep-2004 Enh # 3316063 Reverted back the changes made in the earlier version of the file.
364 --rmaddipa 07-Sep-2004 Enh # 3316063 Added call to igs_fi_tp_ret_schd_pkg.get_fk_igs_ca_inst
365 --smvk 26-Aug-03 Enh # 3045007, Added igs_fi_pp_std_attrs_pkg
366 --shtatiko 10-JUN-2003 Enh# 2831582, Added call to igs_fi_lb_fcis_pkg
367 --sbaliga 18-Apr-2002 Bug 2278825, modified check child
368 --vchappid 02-Apr-2002 Enh# bug2293676, modified check child
369 --schodava 06-FEB-2002 Enh # 2187247
370 -- SFCR021 : FCI-LCI Relation
371 -- Removed the references to igs_fi_chg_mth_app_pkg
372 --smvk 04-feb-2002 added igs_fi_credits_pkg.get_fk_igs_ca_inst_1
373 -- added igs_fi_credits_pkg.get_fk_igs_ca_inst_2
374 -- call
375 --smadathi 04-feb-2002 added igf_sp_stdnt_rel_pkg.get_fk_igs_ca_inst
376 -- call
377
378 --ckasu 04-Dec-2003 Added IGS_EN_SPA_TERMS_PKG.GET_FK_IGS_CA_INST
379 -- for Term Records Build
380 -------------------------------------------------------------------
381
382 BEGIN
383
384
385
386 IGS_AD_PERD_AD_CAT_PKG.GET_UFK_IGS_CA_INST (
387 old_references.cal_type,
388 old_references.sequence_number,
389 old_references.start_dt,
390 old_references.end_dt
391 );
392
393 IGS_EN_SU_ATTEMPT_PKG.GET_UFK_IGS_CA_INST (
394 old_references.cal_type,
395 old_references.sequence_number,
396 old_references.start_dt,
397 old_references.end_dt
398 );
399
400 IGS_AS_SU_STMPTOUT_PKG.GET_UFK_IGS_CA_INST (
401 old_references.cal_type,
402 old_references.sequence_number,
403 old_references.start_dt,
404 old_references.end_dt
405 );
406
407 IGS_PS_OFR_INST_PKG.GET_UFK_IGS_CA_INST (
408 old_references.cal_type,
409 old_references.sequence_number,
410 old_references.start_dt,
411 old_references.end_dt
412 );
413
414 IGS_PS_UNIT_OFR_PAT_PKG.GET_UFK_IGS_CA_INST (
415 old_references.cal_type,
416 old_references.sequence_number,
417 old_references.start_dt,
418 old_references.end_dt
419 );
420
421 IGS_AD_PS_APPL_INST_PKG.GET_FK_IGS_CA_INST (
422 old_references.cal_type,
423 old_references.sequence_number
424 );
425
426 IGS_CA_INST_PKG.GET_FK_IGS_CA_INST (
427 old_references.cal_type,
428 old_references.sequence_number
429 );
430
431 IGS_CA_INST_REL_PKG.GET_FK_IGS_CA_INST (
432 old_references.cal_type,
433 old_references.sequence_number
434 );
435
436
437 IGS_GR_CRMN_ROUND_PKG.GET_FK_IGS_CA_INST (
438 old_references.cal_type,
439 old_references.sequence_number
440 );
441
442 IGS_CO_ITM_PKG.GET_FK_IGS_CA_INST (
443 old_references.cal_type,
444 old_references.sequence_number
445 );
446
447 IGS_ST_DFT_LOAD_APPO_PKG.GET_FK_IGS_CA_INST (
448 old_references.cal_type,
449 old_references.sequence_number
450 );
451
452 IGS_CA_DA_INST_PKG.GET_FK_IGS_CA_INST (
453 old_references.cal_type,
454 old_references.sequence_number
455 );
456
457 IGS_AS_EXAM_SESSION_PKG.GET_FK_IGS_CA_INST (
458 old_references.cal_type,
459 old_references.sequence_number
460 );
461
462 IGS_FI_F_CAT_CA_INST_PKG.GET_FK_IGS_CA_INST (
463 old_references.cal_type,
464 old_references.sequence_number
465 );
466
467 IGS_FI_F_TYP_CA_INST_PKG.GET_FK_IGS_CA_INST (
468 old_references.cal_type,
469 old_references.sequence_number
470 );
471
472 IGS_ST_GVTSEMLOAD_CA_PKG.GET_FK_IGS_CA_INST (
473 old_references.cal_type,
474 old_references.sequence_number
475 );
476
477 IGS_EN_SPA_TERMS_PKG.GET_FK_IGS_CA_INST (
478 old_references.cal_type,
479 old_references.sequence_number
480 );
481
482 /*IGS_CO_OU_CO_REF_PKG.GET_FK_IGS_CA_INST (
483 old_references.cal_type,
484 old_references.sequence_number
485 ); */
486
487 IGS_PS_PAT_OF_STUDY_PKG.GET_FK_IGS_CA_INST (
488 old_references.cal_type,
489 old_references.sequence_number
490 );
491
492 IGS_PR_RU_CA_TYPE_PKG.GET_FK_IGS_CA_INST (
493 old_references.cal_type,
494 old_references.sequence_number
495 );
496
497 IGS_AS_SC_ATMPT_ENR_PKG.GET_FK_IGS_CA_INST (
498 old_references.cal_type,
499 old_references.sequence_number
500 );
501
502 IGS_PR_SDT_PS_PR_MSR_PKG.GET_FK_IGS_CA_INST (
503 old_references.cal_type,
504 old_references.sequence_number
505 );
506
507 IGS_PR_STDNT_PR_CK_PKG.GET_FK_IGS_CA_INST (
508 old_references.cal_type,
509 old_references.sequence_number
510 );
511
512 IGS_AS_UNITASS_ITEM_PKG.GET_FK_IGS_CA_INST (
513 old_references.cal_type,
514 old_references.sequence_number
515 );
516
517 IGS_FI_UNIT_FEE_TRG_PKG.GET_FK_IGS_CA_INST (
518 old_references.cal_type,
519 old_references.sequence_number
520 );
521
522 IGS_EN_INST_WLST_OPT_PKG.GET_FK_IGS_CA_INST (
523 old_references.cal_type,
524 old_references.sequence_number
525 );
526
527 igs_ps_uso_cm_grp_pkg.get_fk_igs_ca_inst (
528 old_references.cal_type,
529 old_references.sequence_number
530 );
531
532 igs_ps_us_em_grp_pkg.get_fk_igs_ca_inst (
533 old_references.cal_type,
534 old_references.sequence_number
535 );
536
537 igs_ps_usec_x_grp_pkg.get_fk_igs_ca_inst (
538 old_references.cal_type,
539 old_references.sequence_number
540 );
541 igs_ps_unit_ver_pkg.get_fk_igs_ca_inst_all(
542 old_references.cal_type,
543 old_references.sequence_number
544 );
545 igs_ps_unit_ver_pkg.get_fk_igs_ca_inst_all1(
546 old_references.cal_type,
547 old_references.sequence_number
548 );
549 igs_en_elgb_ovr_pkg.get_fk_igs_ca_inst(
550 old_references.cal_type,
551 old_references.sequence_number
552 );
553 -- Start of addition for Bug no. 1960126
554 igs_av_stnd_unit_pkg.get_fk_igs_ca_inst(
555 old_references.cal_type,
556 old_references.sequence_number
557 );
558
559 igs_av_stnd_unit_lvl_pkg.get_fk_igs_ca_inst(
560 old_references.cal_type,
561 old_references.sequence_number
562 );
563 -- End of addition for Bug no. 1960126
564
565 igs_pe_stat_details_pkg.get_fk_igs_ca_inst(
566 old_references.cal_type,
567 old_references.sequence_number
568 );
569
570 IGS_PE_PERS_ENCUMB_PKG.get_fk_igs_ca_inst(
571 old_references.cal_type,
572 old_references.sequence_number
573 );
574
575 IGS_EN_STDNT_PS_ATT_PKG.get_fk_igs_ca_inst(
576 old_references.cal_type,
577 old_references.sequence_number
578 );
579
580 IGS_AS_SU_SETATMPT_PKG.get_fk_igs_ca_inst(
581 old_references.cal_type,
582 old_references.sequence_number
583 );
584
585 --added by nalkumar, Bug:2126091
586 IGS_FI_PERSON_HOLDS_PKG.get_fk_igs_ca_inst(
587 old_references.cal_type,
588 old_references.sequence_number
589 );
590
591 IGS_AS_ANON_ID_US_PKG.get_fk_igs_ca_inst(
592 old_references.cal_type,
593 old_references.sequence_number
594 );
595
596 IGS_AS_ANON_ID_ASS_PKG.get_fk_igs_ca_inst(
597 old_references.cal_type,
598 old_references.sequence_number
599 );
600
601 IGS_PS_FAC_WL_PKG.get_fk_igs_ca_inst(
602 old_references.cal_type,
603 old_references.sequence_number
604 );
605
606 igf_sp_stdnt_rel_pkg.get_fk_igs_ca_inst(
607 old_references.cal_type,
608 old_references.sequence_number
609 );
610 -- ADDED FOR 2191470 STARTS HERE
611 igs_fi_credits_pkg.get_fk_igs_ca_inst_1(
612 old_references.cal_type,
613 old_references.sequence_number
614 );
615
616 igs_fi_credits_pkg.get_fk_igs_ca_inst_2(
617 old_references.cal_type,
618 old_references.sequence_number
619 );
620 -- ADDED FOR 2191470 ENDS HERE
621
622 igs_fi_bill_pln_crd_pkg.get_fk_igs_ca_inst(
623 old_references.cal_type,
624 old_references.sequence_number
625 );
626
627 igs_he_fte_cal_prd_pkg.get_fk_igs_ca_inst1(
628 old_references.cal_type,
629 old_references.sequence_number
630 );
631
632 igs_he_fte_cal_prd_pkg.get_fk_igs_ca_inst2(
633 old_references.cal_type,
634 old_references.sequence_number
635 );
636
637 igs_pr_stu_acad_stat_pkg.get_fk_igs_ca_inst(
638 old_references.cal_type,
639 old_references.sequence_number
640 );
641
642 igs_pr_cohort_inst_pkg.get_fk_igs_ca_inst(
643 old_references.cal_type,
644 old_references.sequence_number
645 );
646 -- DA UI 2829285 addition
647 -- commented code for resolving depndency in the bug 2981279
648
649 igs_da_cnfg_req_typ_pkg.get_fk_igs_ca_inst (
650 x_cal_type => old_references.cal_type,
651 x_sequence_number => old_references.sequence_number
652 );
653 igs_da_req_wif_pkg.get_fk_igs_ca_inst (
654 x_cal_type => old_references.cal_type,
655 x_sequence_number => old_references.sequence_number
656 );
657 -- DA UI 2829285 addition ends
658
659 -- Enh# 2831582, Added the following call.
660 igs_fi_lb_fcis_pkg.get_fk_igs_ca_inst(
661 old_references.cal_type,
662 old_references.sequence_number
663 );
664
665 -- Enh # 3045007
666 igs_fi_pp_std_attrs_pkg.get_fk_igs_ca_inst (
667 x_cal_type => old_references.cal_type,
668 x_sequence_number => old_references.sequence_number
669 );
670
671 igs_en_psv_term_it_pkg.get_fk_igs_ca_inst (
672 x_cal_type => old_references.cal_type,
673 x_sequence_number => old_references.sequence_number
674 );
675
676 END Check_Child_Existance;
677
678 PROCEDURE Check_UK_Child_Existance AS
679 BEGIN
680 IF(((old_references.CAL_TYPE = new_references.CAL_TYPE)AND
681 (old_references.SEQUENCE_NUMBER = new_references.SEQUENCE_NUMBER)AND
682 (old_references.START_DT = new_references.START_DT)AND
683 (old_references.END_DT = new_references.END_DT)) OR
684 ((old_references.CAL_TYPE IS NULL)AND
685 (old_references.SEQUENCE_NUMBER IS NULL)AND
686 (old_references.START_DT IS NULL)AND
687 (old_references.END_DT IS NULL))) THEN
688 NULL;
689 ELSE
690 IGS_AD_PERD_AD_CAT_PKG.GET_UFK_IGS_CA_INST(
691 old_references.CAL_TYPE,
692 old_references.SEQUENCE_NUMBER,
693 old_references.START_DT,
694 old_references.END_DT);
695 IGS_PS_OFR_INST_PKG.GET_UFK_IGS_CA_INST(
696 old_references.CAL_TYPE,
697 old_references.SEQUENCE_NUMBER,
698 old_references.START_DT,
699 old_references.END_DT);
700 IGS_AS_SU_STMPTOUT_PKG.GET_UFK_IGS_CA_INST(
701 old_references.CAL_TYPE,
702 old_references.SEQUENCE_NUMBER,
703 old_references.START_DT,
704 old_references.END_DT);
705 IGS_EN_SU_ATTEMPT_PKG.GET_UFK_IGS_CA_INST(
706 old_references.CAL_TYPE,
707 old_references.SEQUENCE_NUMBER,
708 old_references.START_DT,
709 old_references.END_DT);
710 IGS_PS_UNIT_OFR_PAT_PKG.GET_UFK_IGS_CA_INST(
711 old_references.CAL_TYPE,
712 old_references.SEQUENCE_NUMBER,
713 old_references.START_DT,
714 old_references.END_DT);
715 END IF;
716 END Check_UK_Child_Existance;
717
718 FUNCTION Get_PK_For_Validation (
719 x_cal_type IN VARCHAR2,
720 x_sequence_number IN NUMBER
721 ) RETURN BOOLEAN AS
722
723
724 -- Bug#2409299, Depending on the calendar status lock on the table is acquired
725 -- lock is required when the cal status is Planned since it is allowed to delete from the Calendar Instance Form
726 -- lock is not required when the cal status is either Active or Inactive so an explicit lock is not required
727 -- opening different cursors depending on the System calendar status
728 CURSOR cur_get_status
729 IS
730 SELECT s.s_cal_status
731 FROM igs_ca_stat s,
732 igs_ca_inst_all ci
733 WHERE ci.cal_status = s.cal_status
734 AND ci.cal_type = x_cal_type
735 AND ci.sequence_number = x_sequence_number;
736 l_s_cal_status igs_ca_stat.s_cal_status%TYPE;
737
738 CURSOR cur_rowid_planned IS
739 SELECT ROWID
740 FROM igs_ca_inst_all
741 WHERE cal_type = x_cal_type
742 AND sequence_number = x_sequence_number
743 FOR UPDATE NOWAIT;
744
745 CURSOR cur_rowid_act_inact IS
746 SELECT ROWID
747 FROM igs_ca_inst_all
748 WHERE cal_type = x_cal_type
749 AND sequence_number = x_sequence_number;
750
751 lv_rowid cur_rowid_planned%ROWTYPE;
752
753 BEGIN
754
755 OPEN cur_get_status;
756 FETCH cur_get_status INTO l_s_cal_status;
757 IF cur_get_status%NOTFOUND THEN
758 CLOSE cur_get_status;
759 RETURN(FALSE);
760 ELSE
761 CLOSE cur_get_status;
762 IF l_s_cal_status = 'PLANNED' THEN
763 OPEN cur_rowid_planned;
764 FETCH cur_rowid_planned INTO lv_rowid;
765 IF cur_rowid_planned%FOUND THEN
766 CLOSE cur_rowid_planned;
767 RETURN (TRUE);
768 ELSE
769 CLOSE cur_rowid_planned;
770 RETURN (FALSE);
771 END IF;
772 ELSE
773 OPEN cur_rowid_act_inact;
774 FETCH cur_rowid_act_inact INTO lv_rowid;
775 IF cur_rowid_act_inact%FOUND THEN
776 CLOSE cur_rowid_act_inact;
777 RETURN (TRUE);
778 ELSE
779 CLOSE cur_rowid_act_inact;
780 RETURN (FALSE);
781 END IF;
782 END IF;
783 END IF;
784
785 END Get_PK_For_Validation;
786
787 FUNCTION Get_UK_For_Validation (
788 x_cal_type IN VARCHAR2,
789 x_sequence_number IN NUMBER,
790 x_start_dt IN DATE,
791 x_end_dt IN DATE
792 )RETURN BOOLEAN AS
793
794 CURSOR cur_rowid IS
795 SELECT ROWID
796 FROM IGS_CA_INST_ALL
797 WHERE cal_type = x_cal_type
798 AND sequence_number = x_sequence_number
799 AND start_dt = x_start_dt
800 AND end_dt = x_end_dt
801 AND ((l_rowid IS NULL) OR (ROWID <> l_rowid)) ;
802
803 lv_rowid cur_rowid%ROWTYPE;
804
805 BEGIN
806
807 OPEN cur_rowid;
808 FETCH cur_rowid INTO lv_rowid;
809 IF (cur_rowid%FOUND) THEN
810 CLOSE cur_rowid;
811 RETURN (TRUE);
812 ELSE
813 CLOSE cur_rowid;
814 RETURN (FALSE);
815 END IF;
816
817 END Get_UK_For_Validation;
818
819 FUNCTION Get_UK2_For_Validation (
820 x_cal_type IN VARCHAR2,
821 x_start_dt IN DATE,
822 x_end_dt IN DATE
823 )RETURN BOOLEAN AS
824
825 CURSOR cur_rowid IS
826 SELECT ROWID
827 FROM IGS_CA_INST_ALL
828 WHERE cal_type = x_cal_type
829 AND start_dt = x_start_dt
830 AND end_dt = x_end_dt
831 AND ((l_rowid IS NULL) OR (ROWID <> l_rowid));
832
833 lv_rowid cur_rowid%ROWTYPE;
834
835 BEGIN
836
837 OPEN cur_rowid;
838 FETCH cur_rowid INTO lv_rowid;
839 IF (cur_rowid%FOUND) THEN
840 CLOSE cur_rowid;
841 RETURN (TRUE);
842 ELSE
843 CLOSE cur_rowid;
844 RETURN (FALSE);
845 END IF;
846
847 END Get_UK2_For_Validation;
848
849 PROCEDURE GET_FK_IGS_CA_TYPE (
850 x_cal_type IN VARCHAR2
851 ) AS
852
853 CURSOR cur_rowid IS
854 SELECT ROWID
855 FROM IGS_CA_INST_ALL
856 WHERE cal_type = x_cal_type ;
857
858 lv_rowid cur_rowid%ROWTYPE;
859
860 BEGIN
861
862 OPEN cur_rowid;
863 FETCH cur_rowid INTO lv_rowid;
864 IF (cur_rowid%FOUND) THEN
865 CLOSE cur_rowid;
866 Fnd_Message.Set_Name ('IGS', 'IGS_CA_CI_CAT_FK');
867 IGS_GE_MSG_STACK.ADD;
868 App_Exception.Raise_Exception;
869 RETURN;
870 END IF;
871 CLOSE cur_rowid;
872
873 END GET_FK_IGS_CA_TYPE;
874
875 PROCEDURE GET_FK_IGS_CA_STAT (
876 x_cal_status IN VARCHAR2
877 ) AS
878
879 CURSOR cur_rowid IS
880 SELECT ROWID
881 FROM IGS_CA_INST_ALL
882 WHERE cal_status = x_cal_status ;
883
884 lv_rowid cur_rowid%ROWTYPE;
885
886 BEGIN
887
888 OPEN cur_rowid;
889 FETCH cur_rowid INTO lv_rowid;
890 IF (cur_rowid%FOUND) THEN
891 CLOSE cur_rowid;
892 Fnd_Message.Set_Name ('IGS', 'IGS_CA_CI_CS_FK');
893 IGS_GE_MSG_STACK.ADD;
894 App_Exception.Raise_Exception;
895 RETURN;
896 END IF;
897 CLOSE cur_rowid;
898
899 END GET_FK_IGS_CA_STAT;
900
901 PROCEDURE GET_FK_IGS_CA_INST (
902 x_cal_type IN VARCHAR2,
903 x_sequence_number IN NUMBER
904 ) AS
905
906 CURSOR cur_rowid IS
907 SELECT ROWID
908 FROM IGS_CA_INST_ALL
909 WHERE cal_type = x_cal_type
910 AND prior_ci_sequence_number = x_sequence_number ;
911
912 lv_rowid cur_rowid%ROWTYPE;
913
914 BEGIN
915
916 OPEN cur_rowid;
917 FETCH cur_rowid INTO lv_rowid;
918 IF (cur_rowid%FOUND) THEN
919 CLOSE cur_rowid;
920 Fnd_Message.Set_Name ('IGS', 'IGS_CA_CI_PRIOR_CI_FK');
921 IGS_GE_MSG_STACK.ADD;
922 App_Exception.Raise_Exception;
923 RETURN;
924 END IF;
925 CLOSE cur_rowid;
926
927 END GET_FK_IGS_CA_INST;
928
929 PROCEDURE Before_DML (
930 p_action IN VARCHAR2,
931 x_rowid IN VARCHAR2 ,
932 x_cal_type IN VARCHAR2 ,
933 x_sequence_number IN NUMBER ,
934 x_start_dt IN DATE ,
935 x_end_dt IN DATE ,
936 x_cal_status IN VARCHAR2 ,
937 x_alternate_code IN VARCHAR2 ,
938 x_sup_cal_status_differ_ind IN VARCHAR2 ,
939 x_prior_ci_sequence_number IN NUMBER ,
940 x_org_id IN NUMBER ,
941 x_creation_date IN DATE ,
942 x_created_by IN NUMBER ,
943 x_last_update_date IN DATE ,
944 x_last_updated_by IN NUMBER ,
945 x_last_update_login IN NUMBER ,
946 x_ss_displayed IN VARCHAR2 ,
947 x_description IN VARCHAR2,
948 x_ivr_display_ind IN VARCHAR2,
949 x_term_instruction_time IN NUMBER ,
950 X_PLANNING_FLAG IN VARCHAR2,
951 X_SCHEDULE_FLAG IN VARCHAR2,
952 X_ADMIN_FLAG IN VARCHAR2
953 ) AS
954 BEGIN
955 Set_Column_Values (
956 p_action,
957 x_rowid,
958 x_cal_type,
959 x_sequence_number,
960 x_start_dt,
961 x_end_dt,
962 x_cal_status,
963 x_alternate_code,
964 x_sup_cal_status_differ_ind,
965 x_prior_ci_sequence_number,
966 x_org_id,
967 x_creation_date,
968 x_created_by,
969 x_last_update_date,
970 x_last_updated_by,
971 x_last_update_login,
972 x_ss_displayed ,
973 x_description,
974 x_ivr_display_ind,
975 x_term_instruction_time,
976 X_PLANNING_FLAG,
977 X_SCHEDULE_FLAG,
978 X_ADMIN_FLAG
979
980 );
981 IF (p_action = 'INSERT') THEN
982 -- Call all the procedures related to Before Insert.
983 BeforeRowInsertUpdate1 ( p_inserting => TRUE ,p_updating => FALSE , p_deleting => FALSE);
984 IF Get_PK_For_Validation (
985 new_references.cal_type,
986 new_references.sequence_number ) THEN
987 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
988 IGS_GE_MSG_STACK.ADD;
989 App_Exception.Raise_Exception;
990 END IF;
991 Check_Uniqueness;
992 Check_Constraints;
993 Check_Parent_Existance;
994 ELSIF (p_action = 'UPDATE') THEN
995 -- Call all the procedures related to Before Update.
996 BeforeRowInsertUpdate1 ( p_inserting => FALSE , p_updating => TRUE , p_deleting => FALSE );
997 beforerowupdate;
998 Check_Uniqueness;
999 Check_Constraints;
1000 Check_Parent_Existance;
1001 Check_UK_Child_Existance;
1002 ELSIF (p_action = 'DELETE') THEN
1003 -- Call all the procedures related to Before Delete.
1004 beforerowdelete;
1005 Check_Child_Existance;
1006 ELSIF (p_action = 'VALIDATE_INSERT') THEN
1007 -- Call all the procedures related to Before Insert.
1008 IF Get_PK_For_Validation (
1009 new_references.cal_type,
1010 new_references.sequence_number ) THEN
1011 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
1012 IGS_GE_MSG_STACK.ADD;
1013 App_Exception.Raise_Exception;
1014 END IF;
1015 Check_Uniqueness;
1016 Check_Constraints;
1017 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
1018 Check_Uniqueness;
1019 Check_Constraints;
1020 Check_UK_Child_Existance;
1021 ELSIF (p_action = 'VALIDATE_DELETE') THEN
1022 Check_Child_Existance;
1023 END IF;
1024 END Before_DML;
1025
1026 PROCEDURE After_DML (
1027 p_action IN VARCHAR2,
1028 x_rowid IN VARCHAR2
1029 ) AS
1030 BEGIN
1031
1032 l_rowid := x_rowid;
1033
1034 IF (p_action = 'INSERT') THEN
1035 -- Call all the procedures related to After Insert.
1036 AfterRowInsertUpdate2 ( p_inserting => TRUE , p_updating => FALSE , p_deleting => FALSE);
1037 AfterStmtInsertUpdateDelete3 ( p_inserting => TRUE , p_updating => FALSE , p_deleting => FALSE);
1038 ELSIF (p_action = 'UPDATE') THEN
1039 -- Call all the procedures related to After Update.
1040 AfterRowInsertUpdate2 ( p_inserting => FALSE , p_updating => TRUE , p_deleting => FALSE );
1041 AfterStmtInsertUpdateDelete3 ( p_inserting => FALSE , p_updating => TRUE , p_deleting => FALSE );
1042 ELSIF (p_action = 'DELETE') THEN
1043 -- Call all the procedures related to After Delete.
1044 AfterStmtInsertUpdateDelete3 (p_inserting => FALSE , p_updating => FALSE , p_deleting => TRUE );
1045 END IF;
1046
1047 l_rowid := NULL;
1048
1049 END After_DML;
1050
1051 PROCEDURE INSERT_ROW (
1052 X_ROWID IN OUT NOCOPY VARCHAR2,
1053 X_CAL_TYPE IN VARCHAR2,
1054 X_SEQUENCE_NUMBER IN NUMBER,
1055 X_START_DT IN DATE,
1056 X_END_DT IN DATE,
1057 X_CAL_STATUS IN VARCHAR2,
1058 X_ALTERNATE_CODE IN VARCHAR2,
1059 X_SUP_CAL_STATUS_DIFFER_IND IN VARCHAR2,
1060 X_PRIOR_CI_SEQUENCE_NUMBER IN NUMBER,
1061 X_ORG_ID IN NUMBER ,
1062 X_MODE IN VARCHAR2 ,
1063 X_SS_DISPLAYED IN VARCHAR2 ,
1064 X_DESCRIPTION IN VARCHAR2,
1065 X_IVR_DISPLAY_IND IN VARCHAR2,
1066 X_TERM_INSTRUCTION_TIME IN NUMBER,
1067 X_PLANNING_FLAG IN VARCHAR2,
1068 X_SCHEDULE_FLAG IN VARCHAR2,
1069 X_ADMIN_FLAG IN VARCHAR2
1070 ) AS
1071 CURSOR C IS SELECT ROWID FROM IGS_CA_INST_ALL
1072 WHERE CAL_TYPE = X_CAL_TYPE
1073 AND SEQUENCE_NUMBER = X_SEQUENCE_NUMBER;
1074 X_LAST_UPDATE_DATE DATE;
1075 X_LAST_UPDATED_BY NUMBER;
1076 X_LAST_UPDATE_LOGIN NUMBER;
1077 BEGIN
1078 X_LAST_UPDATE_DATE := SYSDATE;
1079 IF(X_MODE = 'I') THEN
1080 X_LAST_UPDATED_BY := 1;
1081 X_LAST_UPDATE_LOGIN := 0;
1082 ELSIF (X_MODE = 'R') THEN
1083 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1084 IF X_LAST_UPDATED_BY IS NULL THEN
1085 X_LAST_UPDATED_BY := -1;
1086 END IF;
1087 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1088 IF X_LAST_UPDATE_LOGIN IS NULL THEN
1089 X_LAST_UPDATE_LOGIN := -1;
1090 END IF;
1091 ELSE
1092 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1093 IGS_GE_MSG_STACK.ADD;
1094 app_exception.raise_exception;
1095 END IF;
1096 Before_DML (
1097 p_action =>'INSERT',
1098 x_rowid =>X_ROWID,
1099 x_cal_type =>X_CAL_TYPE,
1100 x_sequence_number =>X_SEQUENCE_NUMBER,
1101 x_start_dt =>X_START_DT,
1102 x_end_dt =>X_END_DT,
1103 x_cal_status =>X_CAL_STATUS,
1104 x_alternate_code =>X_ALTERNATE_CODE,
1105 x_sup_cal_status_differ_ind =>NVL(X_SUP_CAL_STATUS_DIFFER_IND,'N'),
1106 x_prior_ci_sequence_number =>X_PRIOR_CI_SEQUENCE_NUMBER,
1107 x_org_id => igs_ge_gen_003.get_org_id,
1108 x_creation_date =>X_LAST_UPDATE_DATE,
1109 x_created_by =>X_LAST_UPDATED_BY,
1110 x_last_update_date =>X_LAST_UPDATE_DATE,
1111 x_last_updated_by =>X_LAST_UPDATED_BY,
1112 x_last_update_login =>X_LAST_UPDATE_LOGIN,
1113 x_ss_displayed => X_SS_DISPLAYED,
1114 x_description => X_DESCRIPTION,
1115 x_ivr_display_ind => X_IVR_DISPLAY_IND,
1116 x_term_instruction_time => X_TERM_INSTRUCTION_TIME,
1117 X_PLANNING_FLAG => X_PLANNING_FLAG ,
1118 X_SCHEDULE_FLAG => X_SCHEDULE_FLAG,
1119 X_ADMIN_FLAG => X_ADMIN_FLAG
1120
1121 );
1122 INSERT INTO IGS_CA_INST_ALL (
1123 CAL_TYPE,
1124 SEQUENCE_NUMBER,
1125 START_DT,
1126 END_DT,
1127 CAL_STATUS,
1128 ALTERNATE_CODE,
1129 SUP_CAL_STATUS_DIFFER_IND,
1130 PRIOR_CI_SEQUENCE_NUMBER,
1131 ORG_ID,
1132 CREATION_DATE,
1133 CREATED_BY,
1134 LAST_UPDATE_DATE,
1135 LAST_UPDATED_BY,
1136 LAST_UPDATE_LOGIN,
1137 SS_DISPLAYED,
1138 DESCRIPTION,
1139 IVR_DISPLAY_IND,
1140 TERM_INSTRUCTION_TIME,
1141 PLANNING_FLAG,
1142 SCHEDULE_FLAG,
1143 ADMIN_FLAG
1144 ) VALUES (
1145 NEW_REFERENCES.CAL_TYPE,
1146 NEW_REFERENCES.SEQUENCE_NUMBER,
1147 NEW_REFERENCES.START_DT,
1148 NEW_REFERENCES.END_DT,
1149 NEW_REFERENCES.CAL_STATUS,
1150 NEW_REFERENCES.ALTERNATE_CODE,
1151 NEW_REFERENCES.SUP_CAL_STATUS_DIFFER_IND,
1152 NEW_REFERENCES.PRIOR_CI_SEQUENCE_NUMBER,
1153 NEW_REFERENCES.ORG_ID,
1154 X_LAST_UPDATE_DATE,
1155 X_LAST_UPDATED_BY,
1156 X_LAST_UPDATE_DATE,
1157 X_LAST_UPDATED_BY,
1158 X_LAST_UPDATE_LOGIN,
1159 X_SS_DISPLAYED,
1160 NEW_REFERENCES.DESCRIPTION,
1161 NEW_REFERENCES.IVR_DISPLAY_IND,
1162 NEW_REFERENCES.TERM_INSTRUCTION_TIME ,
1163 NEW_REFERENCES.PLANNING_FLAG,
1164 NEW_REFERENCES.SCHEDULE_FLAG,
1165 NEW_REFERENCES.ADMIN_FLAG
1166 );
1167 OPEN c;
1168 FETCH c INTO X_ROWID;
1169 IF (c%NOTFOUND) THEN
1170 CLOSE c;
1171 RAISE NO_DATA_FOUND;
1172 END IF;
1173 CLOSE c;
1174 After_DML (
1175 p_action =>'INSERT',
1176 x_rowid =>X_ROWID
1177 );
1178 END INSERT_ROW;
1179
1180 PROCEDURE LOCK_ROW (
1181 X_ROWID IN VARCHAR2,
1182 X_CAL_TYPE IN VARCHAR2,
1183 X_SEQUENCE_NUMBER IN NUMBER,
1184 X_START_DT IN DATE,
1185 X_END_DT IN DATE,
1186 X_CAL_STATUS IN VARCHAR2,
1187 X_ALTERNATE_CODE IN VARCHAR2,
1188 X_SUP_CAL_STATUS_DIFFER_IND IN VARCHAR2,
1189 X_PRIOR_CI_SEQUENCE_NUMBER IN NUMBER,
1190 X_SS_DISPLAYED IN VARCHAR2 ,
1191 X_DESCRIPTION IN VARCHAR2,
1192 X_IVR_DISPLAY_IND IN VARCHAR2,
1193 X_TERM_INSTRUCTION_TIME IN NUMBER ,
1194 X_PLANNING_FLAG IN VARCHAR2,
1195 X_SCHEDULE_FLAG IN VARCHAR2,
1196 X_ADMIN_FLAG IN VARCHAR2
1197
1198 ) AS
1199 CURSOR c1 IS SELECT
1200 START_DT,
1201 END_DT,
1202 CAL_STATUS,
1203 ALTERNATE_CODE,
1204 SUP_CAL_STATUS_DIFFER_IND,
1205 PRIOR_CI_SEQUENCE_NUMBER,
1206 DESCRIPTION,
1207 IVR_DISPLAY_IND,
1208 TERM_INSTRUCTION_TIME,
1209 PLANNING_FLAG,
1210 SCHEDULE_FLAG,
1211 ADMIN_FLAG
1212 FROM IGS_CA_INST_ALL
1213 WHERE ROWID=X_ROWID
1214 FOR UPDATE NOWAIT;
1215 tlinfo c1%ROWTYPE;
1216
1217 BEGIN
1218 OPEN c1;
1219 FETCH c1 INTO tlinfo;
1220 IF (c1%NOTFOUND) THEN
1221 CLOSE c1;
1222 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1223 IGS_GE_MSG_STACK.ADD;
1224 app_exception.raise_exception;
1225 RETURN;
1226 END IF;
1227 CLOSE c1;
1228
1229 IF ( (tlinfo.START_DT = X_START_DT)
1230 AND (tlinfo.END_DT = X_END_DT)
1231 AND (tlinfo.CAL_STATUS = X_CAL_STATUS)
1232 AND ((tlinfo.ALTERNATE_CODE = X_ALTERNATE_CODE)
1233 OR ((tlinfo.ALTERNATE_CODE IS NULL)
1234 AND (X_ALTERNATE_CODE IS NULL)))
1235 AND (tlinfo.SUP_CAL_STATUS_DIFFER_IND = X_SUP_CAL_STATUS_DIFFER_IND)
1236 AND ((tlinfo.PRIOR_CI_SEQUENCE_NUMBER = X_PRIOR_CI_SEQUENCE_NUMBER)
1237 OR ((tlinfo.PRIOR_CI_SEQUENCE_NUMBER IS NULL)
1238 AND (X_PRIOR_CI_SEQUENCE_NUMBER IS NULL)))
1239 AND ((tlinfo.DESCRIPTION=X_DESCRIPTION)
1240 OR ((tlinfo.DESCRIPTION IS NULL)
1241 AND (X_DESCRIPTION IS NULL )))
1242 AND ((tlinfo.IVR_DISPLAY_IND=X_IVR_DISPLAY_IND)
1243 OR ((tlinfo.IVR_DISPLAY_IND IS NULL)
1244 AND (X_IVR_DISPLAY_IND IS NULL )))
1245 AND ((tlinfo.TERM_INSTRUCTION_TIME = X_TERM_INSTRUCTION_TIME)
1246 OR ((tlinfo.TERM_INSTRUCTION_TIME IS NULL)
1247 AND (X_TERM_INSTRUCTION_TIME IS NULL)))
1248 AND ((tlinfo.PLANNING_FLAG = X_PLANNING_FLAG)
1249 OR ((tlinfo.PLANNING_FLAG IS NULL)
1250 AND (X_PLANNING_FLAG IS NULL)))
1251 AND ((tlinfo.SCHEDULE_FLAG = X_SCHEDULE_FLAG)
1252 OR ((tlinfo.SCHEDULE_FLAG IS NULL)
1253 AND (X_SCHEDULE_FLAG IS NULL)))
1254 AND ((tlinfo.ADMIN_FLAG = X_ADMIN_FLAG)
1255 OR ((tlinfo.ADMIN_FLAG IS NULL)
1256 AND (X_ADMIN_FLAG IS NULL)))
1257
1258 ) THEN
1259 NULL;
1260 ELSE
1261 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1262 IGS_GE_MSG_STACK.ADD;
1263 app_exception.raise_exception;
1264 END IF;
1265 RETURN;
1266 END LOCK_ROW;
1267
1268 PROCEDURE UPDATE_ROW (
1269 X_ROWID IN VARCHAR2,
1270 X_CAL_TYPE IN VARCHAR2,
1271 X_SEQUENCE_NUMBER IN NUMBER,
1272 X_START_DT IN DATE,
1273 X_END_DT IN DATE,
1274 X_CAL_STATUS IN VARCHAR2,
1275 X_ALTERNATE_CODE IN VARCHAR2,
1276 X_SUP_CAL_STATUS_DIFFER_IND IN VARCHAR2,
1277 X_PRIOR_CI_SEQUENCE_NUMBER IN NUMBER,
1278 X_MODE IN VARCHAR2 ,
1279 X_SS_DISPLAYED IN VARCHAR2 ,
1280 X_DESCRIPTION IN VARCHAR2,
1281 X_IVR_DISPLAY_IND IN VARCHAR2,
1282 X_TERM_INSTRUCTION_TIME IN NUMBER ,
1283 X_PLANNING_FLAG IN VARCHAR2,
1284 X_SCHEDULE_FLAG IN VARCHAR2,
1285 X_ADMIN_FLAG IN VARCHAR2
1286 ) AS
1287 X_LAST_UPDATE_DATE DATE;
1288 X_LAST_UPDATED_BY NUMBER;
1289 X_LAST_UPDATE_LOGIN NUMBER;
1290 X_SS_DISPLAYED_V VARCHAR2(1) := 'N';
1291 l_msg VARCHAR2(30);
1292
1293 BEGIN
1294 X_LAST_UPDATE_DATE := SYSDATE;
1295 IF(X_MODE = 'I') THEN
1296 X_LAST_UPDATED_BY := 1;
1297 X_LAST_UPDATE_LOGIN := 0;
1298 ELSIF (X_MODE = 'R') THEN
1299 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1300 IF X_LAST_UPDATED_BY IS NULL THEN
1301 X_LAST_UPDATED_BY := -1;
1302 END IF;
1303 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1304 IF X_LAST_UPDATE_LOGIN IS NULL THEN
1305 X_LAST_UPDATE_LOGIN := -1;
1306 END IF;
1307 ELSE
1308 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1309 IGS_GE_MSG_STACK.ADD;
1310 app_exception.raise_exception;
1311 END IF;
1312
1313 --**
1314 IF X_SS_DISPLAYED IS NULL AND
1315 old_references.ss_displayed IS NOT NULL THEN
1316 X_SS_DISPLAYED_V := old_references.ss_displayed;
1317 ELSE
1318 X_SS_DISPLAYED_V := X_SS_DISPLAYED;
1319 END IF;
1320 --**
1321
1322 Before_DML (
1323 p_action =>'UPDATE',
1324 x_rowid =>X_ROWID,
1325 x_cal_type =>X_CAL_TYPE,
1326 x_sequence_number =>X_SEQUENCE_NUMBER,
1327 x_start_dt =>X_START_DT,
1328 x_end_dt =>X_END_DT,
1329 x_cal_status =>X_CAL_STATUS,
1330 x_alternate_code =>X_ALTERNATE_CODE,
1331 x_sup_cal_status_differ_ind =>X_SUP_CAL_STATUS_DIFFER_IND,
1332 x_prior_ci_sequence_number =>X_PRIOR_CI_SEQUENCE_NUMBER,
1333 x_creation_date =>X_LAST_UPDATE_DATE,
1334 x_created_by =>X_LAST_UPDATED_BY,
1335 x_last_update_date =>X_LAST_UPDATE_DATE,
1336 x_last_updated_by =>X_LAST_UPDATED_BY,
1337 x_last_update_login =>X_LAST_UPDATE_LOGIN,
1338 x_ss_displayed => X_SS_DISPLAYED_V,
1339 x_description => X_DESCRIPTION,
1340 x_ivr_display_ind => X_IVR_DISPLAY_IND,
1341 x_term_instruction_time => X_TERM_INSTRUCTION_TIME,
1342 X_PLANNING_FLAG => X_PLANNING_FLAG ,
1343 X_SCHEDULE_FLAG => X_SCHEDULE_FLAG,
1344 X_ADMIN_FLAG => X_ADMIN_FLAG
1345 );
1346
1347 --SINCE WE NEED TO COMMUNICATE TO THE USER THAT DESCRIPTION HAS TO BE SPECIFIED.
1348 --WITH RESPECT TO THE SWCR003 CALENDAR DESCRIPTION -- CHANGE REQUEST
1349 --Enh No :- 2138560 Change Request for Calendar Instance
1350 --Add a Description Column
1351
1352 IF LTRIM(RTRIM(x_description)) IS NULL THEN
1353
1354 fnd_message.set_name('IGS','IGS_CA_CALDESC_NOT_AVAILABLE');
1355 l_msg :=fnd_message.get;
1356
1357 ELSE
1358 l_msg :=LTRIM(RTRIM(x_description));
1359
1360 END IF;
1361
1362 UPDATE IGS_CA_INST_ALL SET
1363 START_DT = NEW_REFERENCES.START_DT,
1364 END_DT = NEW_REFERENCES.END_DT,
1365 CAL_STATUS = NEW_REFERENCES.CAL_STATUS,
1366 ALTERNATE_CODE = NEW_REFERENCES.ALTERNATE_CODE,
1367 SUP_CAL_STATUS_DIFFER_IND = NEW_REFERENCES.SUP_CAL_STATUS_DIFFER_IND,
1368 PRIOR_CI_SEQUENCE_NUMBER = NEW_REFERENCES.PRIOR_CI_SEQUENCE_NUMBER,
1369 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1370 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1371 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1372 SS_DISPLAYED = X_SS_DISPLAYED_V,
1373 IVR_DISPLAY_IND = X_IVR_DISPLAY_IND,
1374 TERM_INSTRUCTION_TIME = X_TERM_INSTRUCTION_TIME,
1375 DESCRIPTION = l_msg,
1376 PLANNING_FLAG = x_PLANNING_FLAG,
1377 SCHEDULE_FLAG = x_SCHEDULE_FLAG,
1378 ADMIN_FLAG = x_ADMIN_FLAG
1379 WHERE ROWID=X_ROWID
1380 ;
1381 IF (SQL%NOTFOUND) THEN
1382 RAISE NO_DATA_FOUND;
1383 END IF;
1384 After_DML (
1385 p_action =>'UPDATE',
1386 x_rowid =>X_ROWID
1387 );
1388 END UPDATE_ROW;
1389
1390 PROCEDURE ADD_ROW (
1391 X_ROWID IN OUT NOCOPY VARCHAR2,
1392 X_CAL_TYPE IN VARCHAR2,
1393 X_SEQUENCE_NUMBER IN NUMBER,
1394 X_START_DT IN DATE,
1395 X_END_DT IN DATE,
1396 X_CAL_STATUS IN VARCHAR2,
1397 X_ALTERNATE_CODE IN VARCHAR2,
1398 X_SUP_CAL_STATUS_DIFFER_IND IN VARCHAR2,
1399 X_PRIOR_CI_SEQUENCE_NUMBER IN NUMBER,
1400 X_ORG_ID IN NUMBER ,
1401 X_MODE IN VARCHAR2 ,
1402 X_SS_DISPLAYED IN VARCHAR2 ,
1403 X_DESCRIPTION IN VARCHAR2,
1404 X_IVR_DISPLAY_IND IN VARCHAR2,
1405 X_TERM_INSTRUCTION_TIME IN NUMBER,
1406 X_PLANNING_FLAG IN VARCHAR2,
1407 X_SCHEDULE_FLAG IN VARCHAR2,
1408 X_ADMIN_FLAG IN VARCHAR2
1409 ) AS
1410 CURSOR c1 IS SELECT ROWID FROM IGS_CA_INST_ALL
1411 WHERE CAL_TYPE = X_CAL_TYPE
1412 AND SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
1413 ;
1414 BEGIN
1415 OPEN c1;
1416 FETCH c1 INTO X_ROWID;
1417 IF (c1%NOTFOUND) THEN
1418 CLOSE c1;
1419 INSERT_ROW (
1420 X_ROWID,
1421 X_CAL_TYPE,
1422 X_SEQUENCE_NUMBER,
1423 X_START_DT,
1424 X_END_DT,
1425 X_CAL_STATUS,
1426 X_ALTERNATE_CODE,
1427 X_SUP_CAL_STATUS_DIFFER_IND,
1428 X_PRIOR_CI_SEQUENCE_NUMBER,
1429 X_ORG_ID,
1430 X_MODE,
1431 X_SS_DISPLAYED,
1432 X_DESCRIPTION,
1433 X_IVR_DISPLAY_IND,
1434 X_TERM_INSTRUCTION_TIME ,
1435 X_PLANNING_FLAG,
1436 X_SCHEDULE_FLAG ,
1437 X_ADMIN_FLAG
1438 );
1439 RETURN;
1440 END IF;
1441 CLOSE c1;
1442 UPDATE_ROW (
1443 X_ROWID,
1444 X_CAL_TYPE,
1445 X_SEQUENCE_NUMBER,
1446 X_START_DT,
1447 X_END_DT,
1448 X_CAL_STATUS,
1449 X_ALTERNATE_CODE,
1450 X_SUP_CAL_STATUS_DIFFER_IND,
1451 X_PRIOR_CI_SEQUENCE_NUMBER,
1452 X_MODE,
1453 X_SS_DISPLAYED,
1454 X_DESCRIPTION,
1455 X_IVR_DISPLAY_IND,
1456 X_TERM_INSTRUCTION_TIME ,
1457 X_PLANNING_FLAG,
1458 X_SCHEDULE_FLAG ,
1459 X_ADMIN_FLAG
1460 );
1461 END ADD_ROW;
1462
1463 PROCEDURE DELETE_ROW (
1464 X_ROWID IN VARCHAR2
1465 ) AS
1466 BEGIN
1467 Before_DML(
1468 p_action =>'DELETE',
1469 x_rowid =>X_ROWID
1470 );
1471 DELETE FROM IGS_CA_INST_ALL
1472 WHERE ROWID=X_ROWID;
1473 IF (SQL%NOTFOUND) THEN
1474 RAISE NO_DATA_FOUND;
1475 END IF;
1476 After_DML (
1477 p_action =>'DELETE',
1478 x_rowid =>X_ROWID
1479 );
1480 END DELETE_ROW;
1481
1482 PROCEDURE beforerowdelete AS
1483 ------------------------------------------------------------------
1484 --Created by : vchappid, Oracle India
1485 --Date created: 12-Jun-2002
1486 --
1487 --Purpose: Only planned Calendar Instances are allowed for deletion
1488 --
1489 --
1490 --Known limitations/enhancements and/or remarks:
1491 --
1492 --Change History:
1493 --Who When What
1494 -------------------------------------------------------------------
1495
1496 CURSOR cur_delete (cp_cal_type igs_ca_inst.cal_type%TYPE, cp_seq_number igs_ca_inst.sequence_number%TYPE)
1497 IS
1498 SELECT 'x'
1499 FROM igs_ca_inst i, igs_ca_stat s
1500 WHERE i.cal_status = s.cal_status
1501 AND s.s_cal_status = 'PLANNED'
1502 AND i.cal_type = cp_cal_type
1503 AND i.sequence_number = cp_seq_number;
1504 l_check VARCHAR2(1);
1505
1506 BEGIN
1507 -- Only planned Calendar Instances are allowed for deletion
1508 OPEN cur_delete (old_references.cal_type,old_references.sequence_number );
1509 FETCH cur_delete INTO l_check;
1510 IF cur_delete%NOTFOUND THEN
1511 close cur_delete;
1512 fnd_message.set_name('IGS','IGS_CA_NO_DELETE_ALLOWED');
1513 igs_ge_msg_stack.add;
1514 app_exception.raise_exception;
1515 END IF;
1516 close cur_delete;
1517 END beforerowdelete;
1518
1519 PROCEDURE beforerowupdate AS
1520 ------------------------------------------------------------------
1521 --Created by : vchappid, Oracle India
1522 --Date created: 12-Jun-2002
1523 --
1524 --Purpose: Active Calendar Status calendar instance can not be changed to Planned Status
1525 --
1526 --
1527 --Known limitations/enhancements and/or remarks:
1528 --
1529 --Change History:
1530 --Who When What
1531 --kpadiyar 06-May-2003 Added the validations for bug 2885873
1532 -------------------------------------------------------------------
1533 CURSOR cur_get_status (cp_cal_status igs_ca_inst.cal_status%TYPE)
1534 IS
1535 SELECT s_cal_status
1536 FROM igs_ca_stat
1537 WHERE cal_status = cp_cal_status;
1538 l_s_cal_status igs_ca_stat.s_cal_status%TYPE;
1539
1540 CURSOR cur_check_update (cp_cal_type igs_ca_inst.cal_type%TYPE, cp_seq_number igs_ca_inst.sequence_number%TYPE)
1541 IS
1542 SELECT 'x'
1543 FROM igs_ca_inst i, igs_ca_stat s
1544 WHERE i.cal_status = s.cal_status
1545 AND s.s_cal_status = 'ACTIVE'
1546 AND i.cal_type = cp_cal_type
1547 AND i.sequence_number = cp_seq_number;
1548 l_check VARCHAR2(1);
1549 BEGIN
1550 -- get the system calendar status for the user defined cal status
1551 -- if the calendar status is changed and the in the form the system cal status is PLANNED and the
1552 -- old value of the system cal status is ACTIVE then the updation should be aborted
1553 OPEN cur_get_status(new_references.cal_status);
1554 FETCH cur_get_status INTO l_s_cal_status;
1555 IF cur_get_status%FOUND THEN
1556 CLOSE cur_get_status;
1557 IF (l_s_cal_status = 'PLANNED') THEN
1558 OPEN cur_check_update(old_references.cal_type, old_references.sequence_number);
1559 FETCH cur_check_update INTO l_check;
1560 IF cur_check_update%FOUND THEN
1561 CLOSE cur_check_update;
1562 fnd_message.set_name('IGS','IGS_CA_INACTIVE_NOTCHG_PLANN');
1563 igs_ge_msg_stack.add;
1564 app_exception.raise_exception;
1565 END IF;
1566 CLOSE cur_check_update;
1567 END IF;
1568 ELSE
1569 -- If the calendar status is not found then the record might have been deleted
1570 CLOSE cur_get_status;
1571 fnd_message.set_name('FND','FORM_RECORD_DELETED');
1572 igs_ge_msg_stack.add;
1573 app_exception.raise_exception;
1574 END IF;
1575
1576 DECLARE
1577 l_ret_status boolean;
1578 l_message_name fnd_new_messages.message_name%TYPE;
1579
1580 CURSOR c_old_status (p_cal_status IN VARCHAR2) IS
1581 SELECT s_cal_status
1582 FROM igs_ca_stat
1583 WHERE cal_status = p_cal_status
1584 AND closed_ind = 'N';
1585
1586 l_old_status igs_ca_stat.s_cal_status%TYPE;
1587
1588 CURSOR c_new_status (p_cal_status IN VARCHAR2) IS
1589 SELECT s_cal_status
1590 FROM igs_ca_stat
1591 WHERE cal_status = p_cal_status
1592 AND closed_ind = 'N';
1593
1594 l_new_status igs_ca_stat.s_cal_status%TYPE;
1595
1596 CURSOR c_cal_type (p_cal_type IN VARCHAR2) IS
1597 SELECT s_cal_cat
1598 FROM igs_ca_type
1599 WHERE cal_type = p_cal_type
1600 AND closed_ind = 'N';
1601
1602 l_cal_type igs_ca_type.s_cal_cat%TYPE;
1603
1604 BEGIN
1605 OPEN c_old_status(old_references.cal_status);
1606 FETCH c_old_status INTO l_old_status;
1607 CLOSE c_old_status;
1608
1609 OPEN c_new_status(new_references.cal_status);
1610 FETCH c_new_status INTO l_new_status;
1611 CLOSE c_new_status;
1612
1613 OPEN c_cal_type(new_references.cal_type);
1614 FETCH c_cal_type INTO l_cal_type;
1615 CLOSE c_cal_type;
1616
1617 IF (
1618 (l_cal_type = 'TEACHING') AND
1619 (l_old_status <> l_new_status) AND
1620 (l_old_status IN ('ACTIVE','INACTIVE')) AND
1621 (l_new_status IN ('ACTIVE','INACTIVE'))
1622 )THEN
1623 igs_ps_gen_001.Change_Unit_Section_Status(
1624 l_old_status,
1625 l_new_status,
1626 new_references.cal_type,
1627 new_references.sequence_number,
1628 l_ret_status,
1629 l_message_name);
1630 IF NOT l_ret_status THEN
1631 Fnd_Message.Set_Name ('IGS', l_message_name);
1632 IGS_GE_MSG_STACK.ADD;
1633 App_Exception.Raise_Exception;
1634 END IF;
1635 END IF;
1636
1637 EXCEPTION
1638 WHEN OTHERS THEN
1639
1640 IF c_old_status%ISOPEN THEN
1641 CLOSE c_old_status;
1642 END IF;
1643
1644 IF c_new_status%ISOPEN THEN
1645 CLOSE c_new_status;
1646 END IF;
1647
1648 IF c_cal_type%ISOPEN THEN
1649 CLOSE c_cal_type;
1650 END IF;
1651 --kumma, 2986872, Added the following line to raise the exception
1652 App_Exception.Raise_Exception;
1653 END;
1654
1655 END beforerowupdate;
1656
1657 END igs_ca_inst_pkg;