[Home] [Help]
PACKAGE BODY: APPS.IGS_AS_SPL_CONS_APPL_PKG
Source
1 package body IGS_AS_SPL_CONS_APPL_PKG AS
2 /* $Header: IGSDI10B.pls 120.0 2005/07/05 12:02:13 appldev noship $ */
3
4 l_rowid VARCHAR2(25);
5 old_references IGS_AS_SPL_CONS_APPL%RowType;
6 new_references IGS_AS_SPL_CONS_APPL%RowType;
7 PROCEDURE Set_Column_Values (
8 p_action IN VARCHAR2,
9 x_rowid IN VARCHAR2 DEFAULT NULL,
10 x_person_id IN NUMBER DEFAULT NULL,
11 x_course_cd IN VARCHAR2 DEFAULT NULL,
12 x_unit_cd IN VARCHAR2 DEFAULT NULL,
13 x_cal_type IN VARCHAR2 DEFAULT NULL,
14 x_ci_sequence_number IN NUMBER DEFAULT NULL,
15 x_ass_id IN NUMBER DEFAULT NULL,
16 x_creation_dt IN DATE DEFAULT NULL,
17 x_received_dt IN DATE DEFAULT NULL,
18 x_spcl_consideration_cat IN VARCHAR2 DEFAULT NULL,
19 x_sought_outcome IN VARCHAR2 DEFAULT NULL,
20 x_spcl_consideration_outcome IN VARCHAR2 DEFAULT NULL,
21 x_estimated_processing_days IN NUMBER DEFAULT NULL,
22 x_tracking_id IN NUMBER DEFAULT NULL,
23 x_comments IN VARCHAR2 DEFAULT NULL,
24 x_creation_date IN DATE DEFAULT NULL,
25 x_created_by IN NUMBER DEFAULT NULL,
26 x_last_update_date IN DATE DEFAULT NULL,
27 x_last_updated_by IN NUMBER DEFAULT NULL,
28 x_last_update_login IN NUMBER DEFAULT NULL,
29 x_uoo_id IN NUMBER DEFAULT NULL,
30 x_notified_date IN DATE DEFAULT NULL
31 ) IS
32 CURSOR cur_old_ref_values IS
33 SELECT *
34 FROM IGS_AS_SPL_CONS_APPL
35 WHERE rowid = x_rowid;
36 BEGIN
37 l_rowid := x_rowid;
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 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
44 IGS_GE_MSG_STACK.ADD;
45 Close cur_old_ref_values;
46 APP_EXCEPTION.RAISE_EXCEPTION;
47
48 Return;
49 END IF;
50 Close cur_old_ref_values;
51 -- Populate New Values.
52 new_references.person_id := x_person_id;
53 new_references.course_cd := x_course_cd;
54 new_references.unit_cd := x_unit_cd;
55 new_references.cal_type := x_cal_type;
56 new_references.ci_sequence_number := x_ci_sequence_number;
57 new_references.ass_id := x_ass_id;
58 new_references.creation_dt := x_creation_dt;
59 new_references.received_dt := x_received_dt;
60 new_references.spcl_consideration_cat := x_spcl_consideration_cat;
61 new_references.sought_outcome := x_sought_outcome;
62 new_references.spcl_consideration_outcome := x_spcl_consideration_outcome;
63 new_references.estimated_processing_days := x_estimated_processing_days;
64 new_references.tracking_id := x_tracking_id;
65 new_references.comments := x_comments;
66 new_references.uoo_id := x_uoo_id;
67 IF (p_action = 'UPDATE') THEN
68 new_references.creation_date := old_references.creation_date;
69 new_references.created_by := old_references.created_by;
70 ELSE
71 new_references.creation_date := x_creation_date;
72 new_references.created_by := x_created_by;
73 END IF;
74 new_references.last_update_date := x_last_update_date;
75 new_references.last_updated_by := x_last_updated_by;
76 new_references.last_update_login := x_last_update_login;
77 END Set_Column_Values;
78
79 -------------------------------------------------------------------------------------------
80 --Change History:
81 --Who When What
82 --svanukur 29-APR-03 Passed uoo_id to IGS_AS_VAL_SCAP.assp_val_suaai_ins , IGS_AS_VAL_SCAP.assp_val_suaai_delet
83 -- as part of MUS build, # 2829262
84 -------------------------------------------------------------------------------------------
85 PROCEDURE BeforeRowInsertUpdate1(
86 p_inserting IN BOOLEAN DEFAULT FALSE,
87 p_updating IN BOOLEAN DEFAULT FALSE,
88 p_deleting IN BOOLEAN DEFAULT FALSE
89 ) AS
90 v_message_name VARCHAR2(30);
91 BEGIN
92 -- Validate that inserts/updates are allowed
93 IF p_inserting OR p_updating THEN
94 -- <scap1>
95 -- Validate IGS_AS_SPCL_CONS_CAT closed indicator
96 IF IGS_AS_VAL_SCAP.assp_val_spcc_closed ( new_references.spcl_consideration_cat,
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 -- <scap2>
103 -- Validate IGS_AS_SPCL_CONS_OUT closed indicator for
104 -- the sought_outcome field
105 IF IGS_AS_VAL_SCAP.assp_val_spco_closed( new_references.sought_outcome,
106 v_message_name) = FALSE THEN
107 FND_MESSAGE.SET_NAME('IGS',v_message_name);
108 IGS_GE_MSG_STACK.ADD;
109 APP_EXCEPTION.RAISE_EXCEPTION;
110 END IF;
111 -- <scap3>
112 -- Validate IGS_AS_SPCL_CONS_OUT closed indicator for
113 -- the IGS_AS_SPCL_CONS_OUT field
114 IF IGS_AS_VAL_SCAP.assp_val_spco_closed( new_references.spcl_consideration_outcome,
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 --<scap4>
121 -- Validate SUA is correct status and has valid links
122 -- This uses the same valid'n as that for creation of SUAAI,
123 -- the latter being slightly different in that they can not be
124 -- added for SUA status = 'COMPLETED'. That's why this code
125 -- traps for that error and allows valid'n to succeed if it is
126 -- encountered
127 IF (IGS_AS_VAL_SCAP.assp_val_suaai_ins ( new_references.person_id,
128 new_references.course_cd,
129 new_references.unit_cd,
130 new_references.cal_type,
131 new_references.ci_sequence_number,
132 new_references.ass_id,
133 v_message_name,
134 new_references.uoo_id) = FALSE AND
135 v_message_name <> 'IGS_CA_AA_CIR_FK') THEN
136 IF v_message_name ='IGS_CA_AA_CIR_FK' THEN
137
138 FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE');
139 IGS_GE_MSG_STACK.ADD;
140 APP_EXCEPTION.RAISE_EXCEPTION;
141 ELSE
142 FND_MESSAGE.SET_NAME('IGS',v_message_name);
143 IGS_GE_MSG_STACK.ADD;
144 APP_EXCEPTION.RAISE_EXCEPTION;
145 END IF;
146 END IF;
147 END IF;
148 IF p_inserting THEN
149 --<scap6>
150 IF IGS_AS_VAL_SCAP.assp_val_suaai_delet(
151 new_references.person_id,
152 new_references.course_cd,
153 new_references.unit_cd,
154 new_references.cal_type,
155 new_references.ci_sequence_number,
156 new_references.ass_id,
157 new_references.creation_dt,
158 v_message_name,
159 new_references.uoo_id) = FALSE THEN
160
161 FND_MESSAGE.SET_NAME('IGS','IGS_PS_POSU_POSP_FK');
162 IGS_GE_MSG_STACK.ADD;
163 APP_EXCEPTION.RAISE_EXCEPTION;
164 END IF;
165 END IF;
166 IF p_updating THEN
167 --<scap7>
168 IF new_references.spcl_consideration_outcome IS NOT NULL AND
169 IGS_AS_VAL_SCAP.assp_val_suaai_delet(
170 new_references.person_id,
171 new_references.course_cd,
172 new_references.unit_cd,
173 new_references.cal_type,
174 new_references.ci_sequence_number,
175 new_references.ass_id,
176 new_references.creation_dt,
177 v_message_name,
178 new_references.uoo_id) = FALSE THEN
179 FND_MESSAGE.SET_NAME('IGS','IGS_PE_PIG_PE_FK');
180 IGS_GE_MSG_STACK.ADD;
181 APP_EXCEPTION.RAISE_EXCEPTION;
182 END IF;
183 END IF;
184 END BeforeRowInsertUpdate1;
185
186 PROCEDURE Check_Parent_Existance AS
187 BEGIN
188 IF (((old_references.sought_outcome = new_references.sought_outcome)) OR
189 ((new_references.sought_outcome IS NULL))) THEN
190 NULL;
191 ELSIF NOT IGS_AS_SPCL_CONS_OUT_PKG.Get_PK_For_Validation (
192 new_references.sought_outcome
193 ) THEN
194 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
195 IGS_GE_MSG_STACK.ADD;
196 APP_EXCEPTION.RAISE_EXCEPTION;
197
198 END IF;
199 IF (((old_references.spcl_consideration_outcome= new_references.spcl_consideration_outcome)) OR
200 ((new_references.spcl_consideration_outcome IS NULL))) THEN
201 NULL;
202 ELSIF NOT IGS_AS_SPCL_CONS_OUT_PKG.Get_PK_For_Validation (
203 new_references.spcl_consideration_outcome
204 ) THEN
205 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
206 IGS_GE_MSG_STACK.ADD;
207 APP_EXCEPTION.RAISE_EXCEPTION;
208
209 END IF;
210 IF ((
211 (old_references.course_cd = new_references.course_cd) OR
212 (old_references.person_id = new_references.person_id) OR
213 (old_references.ass_id = new_references.ass_id) OR
214 (old_references.creation_dt = new_references.creation_dt)OR
215 (old_references.uoo_id = new_references.uoo_id)) OR
216 (
217 (new_references.course_cd IS NULL) OR
218 (new_references.person_id IS NULL) OR
219 (new_references.ass_id IS NULL) OR
220 (new_references.creation_dt IS NULL) OR
221 (new_references.uoo_id IS NULL) )) THEN
222 NULL;
223 ELSIF NOT IGS_AS_SU_ATMPT_ITM_PKG.Get_PK_For_Validation (
224 new_references.course_cd,
225 new_references.person_id,
226 new_references.ass_id,
227 new_references.creation_dt,
228 new_references.uoo_id)THEN
229 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
230 IGS_GE_MSG_STACK.ADD;
231 APP_EXCEPTION.RAISE_EXCEPTION;
232
233 END IF;
234 IF (((old_references.spcl_consideration_cat= new_references.spcl_consideration_cat)) OR
235 ((new_references.spcl_consideration_cat IS NULL))) THEN
236 NULL;
237 ELSIF NOT IGS_AS_SPCL_CONS_CAT_PKG.Get_PK_For_Validation (
238 new_references.spcl_consideration_cat
239 ) THEN
240 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
241 IGS_GE_MSG_STACK.ADD;
242 APP_EXCEPTION.RAISE_EXCEPTION;
243
244 END IF;
245 END Check_Parent_Existance;
246
247 -------------------------------------------------------------------------------------------
248 --Change History:
249 --Who When What
250 --svanukur 29-APR-03 changed the PK columns as part of MUS build, # 2829262
251 -------------------------------------------------------------------------------------------
252 FUNCTION Get_PK_For_Validation (
253 x_person_id IN NUMBER,
254 x_course_cd IN VARCHAR2,
255 x_ass_id IN NUMBER,
256 x_creation_dt IN DATE,
257 x_received_dt IN DATE,
258 x_uoo_id IN NUMBER
259 ) RETURN BOOLEAN AS
260 CURSOR cur_rowid IS
261 SELECT rowid
262 FROM IGS_AS_SPL_CONS_APPL
263 WHERE person_id = x_person_id
264 AND course_cd = x_course_cd
265 AND ass_id = x_ass_id
266 AND creation_dt = x_creation_dt
267 AND received_dt = x_received_dt
268 AND uoo_id = x_uoo_id
269 FOR UPDATE NOWAIT;
270 lv_rowid cur_rowid%RowType;
271 BEGIN
272 Open cur_rowid;
273 Fetch cur_rowid INTO lv_rowid;
274 IF (cur_rowid%FOUND) THEN
275 Close cur_rowid;
276 Return (TRUE);
277 ELSE
278 Close cur_rowid;
279 Return (FALSE);
280 END IF;
281
282
283 END Get_PK_For_Validation;
284
285 PROCEDURE GET_FK_IGS_AS_SPCL_CONS_OUT (
286 x_spcl_consideration_outcome IN VARCHAR2
287 ) AS
288 CURSOR cur_rowid IS
289 SELECT rowid
290 FROM IGS_AS_SPL_CONS_APPL
291 WHERE sought_outcome = x_spcl_consideration_outcome OR
292 spcl_consideration_outcome= x_spcl_consideration_outcome ;
293 lv_rowid cur_rowid%RowType;
294 BEGIN
295 Open cur_rowid;
296 Fetch cur_rowid INTO lv_rowid;
297 IF (cur_rowid%FOUND) THEN
298 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SCAP_SPCO_FK');
299 IGS_GE_MSG_STACK.ADD;
300 Close cur_rowid;
301 APP_EXCEPTION.RAISE_EXCEPTION;
302
303 Return;
304 END IF;
305 Close cur_rowid;
306 END GET_FK_IGS_AS_SPCL_CONS_OUT;
307 -------------------------------------------------------------------------------------------
308 --Change History:
309 --Who When What
310 --svanukur 29-APR-03 Added uoo_id as part of MUS build, # 2829262
311 -------------------------------------------------------------------------------------------
312 PROCEDURE GET_FK_IGS_AS_SU_ATMPT_ITM (
313 x_course_cd IN VARCHAR2,
314 x_person_id IN NUMBER,
315 x_ass_id IN NUMBER,
316 x_creation_dt IN DATE,
317 x_uoo_id IN NUMBER
318 ) AS
319 CURSOR cur_rowid IS
320 SELECT rowid
321 FROM IGS_AS_SPL_CONS_APPL
322 WHERE person_id = x_person_id
323 AND course_cd = x_course_cd
324 AND ass_id = x_ass_id
325 AND creation_dt = x_creation_dt
326 AND uoo_id = x_uoo_id;
327 lv_rowid cur_rowid%RowType;
328 BEGIN
329 Open cur_rowid;
330 Fetch cur_rowid INTO lv_rowid;
331 IF (cur_rowid%FOUND) THEN
332 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SCAP_SUAAI_FK');
333 IGS_GE_MSG_STACK.ADD;
334 Close cur_rowid;
335 APP_EXCEPTION.RAISE_EXCEPTION;
336
337 Return;
338 END IF;
339 Close cur_rowid;
340 END GET_FK_IGS_AS_SU_ATMPT_ITM;
341 PROCEDURE GET_FK_IGS_AS_SPCL_CONS_CAT (
342 x_spcl_consideration_cat IN VARCHAR2
343 ) AS
344 CURSOR cur_rowid IS
345 SELECT rowid
346 FROM IGS_AS_SPL_CONS_APPL
347 WHERE spcl_consideration_cat= x_spcl_consideration_cat ;
348 lv_rowid cur_rowid%RowType;
349 BEGIN
350 Open cur_rowid;
351 Fetch cur_rowid INTO lv_rowid;
352 IF (cur_rowid%FOUND) THEN
353 Fnd_Message.Set_Name ('IGS', 'IGS_AS_SCAP_SPCC_FK');
354 IGS_GE_MSG_STACK.ADD;
355 Close cur_rowid;
356 APP_EXCEPTION.RAISE_EXCEPTION;
357
358 Return;
359 END IF;
360 Close cur_rowid;
361 END GET_FK_IGS_AS_SPCL_CONS_CAT;
362
363 PROCEDURE Before_DML (
364 p_action IN VARCHAR2,
365 x_rowid IN VARCHAR2 DEFAULT NULL,
366 x_person_id IN NUMBER DEFAULT NULL,
367 x_course_cd IN VARCHAR2 DEFAULT NULL,
368 x_unit_cd IN VARCHAR2 DEFAULT NULL,
369 x_cal_type IN VARCHAR2 DEFAULT NULL,
370 x_ci_sequence_number IN NUMBER DEFAULT NULL,
371 x_ass_id IN NUMBER DEFAULT NULL,
372 x_creation_dt IN DATE DEFAULT NULL,
373 x_received_dt IN DATE DEFAULT NULL,
374 x_spcl_consideration_cat IN VARCHAR2 DEFAULT NULL,
375 x_sought_outcome IN VARCHAR2 DEFAULT NULL,
376 x_spcl_consideration_outcome IN VARCHAR2 DEFAULT NULL,
377 x_estimated_processing_days IN NUMBER DEFAULT NULL,
378 x_tracking_id IN NUMBER DEFAULT NULL,
379 x_comments IN VARCHAR2 DEFAULT NULL,
380 x_creation_date IN DATE DEFAULT NULL,
381 x_created_by IN NUMBER DEFAULT NULL,
382 x_last_update_date IN DATE DEFAULT NULL,
383 x_last_updated_by IN NUMBER DEFAULT NULL,
384 x_last_update_login IN NUMBER DEFAULT NULL,
385 x_uoo_id IN NUMBER DEFAULT NULL,
386 x_notified_date IN DATE DEFAULT NULL
387 ) AS
388 BEGIN
389 Set_Column_Values (
390 p_action,
391 x_rowid,
392 x_person_id,
393 x_course_cd,
394 x_unit_cd,
395 x_cal_type,
396 x_ci_sequence_number,
397 x_ass_id,
398 x_creation_dt,
399 x_received_dt,
400 x_spcl_consideration_cat,
401 x_sought_outcome,
402 x_spcl_consideration_outcome,
403 x_estimated_processing_days,
404 x_tracking_id,
405 x_comments,
406 x_creation_date,
407 x_created_by,
408 x_last_update_date,
409 x_last_updated_by,
410 x_last_update_login,
411 x_uoo_id
412 );
413 IF (p_action = 'INSERT') THEN
414 -- Call all the procedures related to Before Insert.
415 BeforeRowInsertUpdate1 ( p_inserting => TRUE );
416 IF Get_PK_For_Validation (
417 NEW_REFERENCES.person_id ,
418 NEW_REFERENCES.course_cd ,
419 NEW_REFERENCES.ass_id ,
420 NEW_REFERENCES.creation_dt ,
421 NEW_REFERENCES.received_dt ,
422 NEW_REFERENCES.uoo_id
423 ) THEN
424 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
425 IGS_GE_MSG_STACK.ADD;
426 APP_EXCEPTION.RAISE_EXCEPTION;
427 END IF;
428
429 Check_Constraints;
430
431 Check_Parent_Existance;
432 ELSIF (p_action = 'UPDATE') THEN
433 -- Call all the procedures related to Before Update.
434 BeforeRowInsertUpdate1 ( p_updating => TRUE );
435
436 Check_Constraints;
437
438 Check_Parent_Existance;
439
440 ELSIF (p_action = 'VALIDATE_INSERT') THEN
441 IF Get_PK_For_Validation (
442 NEW_REFERENCES.person_id ,
443 NEW_REFERENCES.course_cd ,
444 NEW_REFERENCES.ass_id ,
445 NEW_REFERENCES.creation_dt ,
446 NEW_REFERENCES.received_dt,
447 NEW_REFERENCES.uoo_id ) THEN
448 Fnd_Message.Set_Name ('IGS', 'IGS_GE_RECORD_ALREADY_EXISTS');
449 IGS_GE_MSG_STACK.ADD;
450 APP_EXCEPTION.RAISE_EXCEPTION;
451 END IF;
452 Check_Constraints;
453 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
454 Check_Constraints;
455
456
457 END IF;
458 END Before_DML;
459
460 procedure INSERT_ROW (
461 X_ROWID in out NOCOPY VARCHAR2,
462 X_PERSON_ID in NUMBER,
463 X_COURSE_CD in VARCHAR2,
464 X_UNIT_CD in VARCHAR2,
465 X_CAL_TYPE in VARCHAR2,
466 X_CI_SEQUENCE_NUMBER in NUMBER,
467 X_ASS_ID in NUMBER,
468 X_CREATION_DT in DATE,
469 X_RECEIVED_DT in DATE,
470 X_SPCL_CONSIDERATION_CAT in VARCHAR2,
471 X_SOUGHT_OUTCOME in VARCHAR2,
472 X_SPCL_CONSIDERATION_OUTCOME in VARCHAR2,
473 X_ESTIMATED_PROCESSING_DAYS in NUMBER,
474 X_TRACKING_ID in NUMBER,
475 X_COMMENTS in VARCHAR2,
476 X_MODE in VARCHAR2 default 'R',
477 X_UOO_ID in NUMBER,
478 X_NOTIFIED_DATE DATE
479 ) AS
480 cursor C is select ROWID from IGS_AS_SPL_CONS_APPL
481 where PERSON_ID = X_PERSON_ID
482 and COURSE_CD = X_COURSE_CD
483 and ASS_ID = X_ASS_ID
484 and CREATION_DT = X_CREATION_DT
485 and RECEIVED_DT = X_RECEIVED_DT
486 and UOO_ID = X_UOO_ID;
487 X_LAST_UPDATE_DATE DATE;
488 X_LAST_UPDATED_BY NUMBER;
489 X_LAST_UPDATE_LOGIN NUMBER;
490 begin
491 X_LAST_UPDATE_DATE := SYSDATE;
492 if(X_MODE = 'I') then
493 X_LAST_UPDATED_BY := 1;
494 X_LAST_UPDATE_LOGIN := 0;
495 elsif (X_MODE IN ('R', 'S')) then
496 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
497 if X_LAST_UPDATED_BY is NULL then
498 X_LAST_UPDATED_BY := -1;
499 end if;
500 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
501 if X_LAST_UPDATE_LOGIN is NULL then
502 X_LAST_UPDATE_LOGIN := -1;
503 end if;
504 else
505 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
506 IGS_GE_MSG_STACK.ADD;
507 APP_EXCEPTION.RAISE_EXCEPTION;
508 end if;
509 Before_DML(
510 p_action=>'INSERT',
511 x_rowid=>X_ROWID,
512 x_ass_id=>X_ASS_ID,
513 x_cal_type=>X_CAL_TYPE,
514 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
515 x_comments=>X_COMMENTS,
516 x_course_cd=>X_COURSE_CD,
517 x_creation_dt=>X_CREATION_DT,
518 x_estimated_processing_days=>X_ESTIMATED_PROCESSING_DAYS,
519 x_person_id=>X_PERSON_ID,
520 x_received_dt=>X_RECEIVED_DT,
521 x_sought_outcome=>X_SOUGHT_OUTCOME,
522 x_spcl_consideration_cat=>X_SPCL_CONSIDERATION_CAT,
523 x_spcl_consideration_outcome=>X_SPCL_CONSIDERATION_OUTCOME,
524 x_tracking_id=>X_TRACKING_ID,
525 x_unit_cd=>X_UNIT_CD,
526 x_creation_date=>X_LAST_UPDATE_DATE,
527 x_created_by=>X_LAST_UPDATED_BY,
528 x_last_update_date=>X_LAST_UPDATE_DATE,
529 x_last_updated_by=>X_LAST_UPDATED_BY,
530 x_last_update_login=>X_LAST_UPDATE_LOGIN,
531 x_uoo_id => X_UOO_ID,
532 x_notified_date => X_NOTIFIED_DATE
533 );
534 IF (x_mode = 'S') THEN
535 igs_sc_gen_001.set_ctx('R');
536 END IF;
537 insert into IGS_AS_SPL_CONS_APPL (
538 PERSON_ID,
539 COURSE_CD,
540 UNIT_CD,
541 CAL_TYPE,
542 CI_SEQUENCE_NUMBER,
543 ASS_ID,
544 CREATION_DT,
545 RECEIVED_DT,
546 SPCL_CONSIDERATION_CAT,
547 SOUGHT_OUTCOME,
548 SPCL_CONSIDERATION_OUTCOME,
549 ESTIMATED_PROCESSING_DAYS,
550 TRACKING_ID,
551 COMMENTS,
552 CREATION_DATE,
553 CREATED_BY,
554 LAST_UPDATE_DATE,
555 LAST_UPDATED_BY,
556 LAST_UPDATE_LOGIN,
557 UOO_ID,
558 NOTIFIED_DATE
559 ) values (
560 NEW_REFERENCES.PERSON_ID,
561 NEW_REFERENCES.COURSE_CD,
562 NEW_REFERENCES.UNIT_CD,
563 NEW_REFERENCES.CAL_TYPE,
564 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
565 NEW_REFERENCES.ASS_ID,
566 NEW_REFERENCES.CREATION_DT,
567 NEW_REFERENCES.RECEIVED_DT,
568 NEW_REFERENCES.SPCL_CONSIDERATION_CAT,
569 NEW_REFERENCES.SOUGHT_OUTCOME,
570 NEW_REFERENCES.SPCL_CONSIDERATION_OUTCOME,
571 NEW_REFERENCES.ESTIMATED_PROCESSING_DAYS,
572 NEW_REFERENCES.TRACKING_ID,
573 NEW_REFERENCES.COMMENTS,
574 X_LAST_UPDATE_DATE,
575 X_LAST_UPDATED_BY,
576 X_LAST_UPDATE_DATE,
577 X_LAST_UPDATED_BY,
578 X_LAST_UPDATE_LOGIN,
579 NEW_REFERENCES.UOO_ID,
580 X_NOTIFIED_DATE
581 );
582 IF (x_mode = 'S') THEN
583 igs_sc_gen_001.unset_ctx('R');
584 END IF;
585
586 open c;
587 fetch c into X_ROWID;
588 if (c%notfound) then
589 close c;
590 raise no_data_found;
591 end if;
592 close c;
593
594 EXCEPTION
595 WHEN OTHERS THEN
596 IF (SQLCODE IN (-28115, -28113, -28111)) THEN
597 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_EXCEPTION');
598 fnd_message.set_token ('ERR_CD', SQLCODE);
599 igs_ge_msg_stack.add;
600 igs_sc_gen_001.unset_ctx('R');
601 app_exception.raise_exception;
602 ELSE
603 igs_sc_gen_001.unset_ctx('R');
604 RAISE;
605 END IF;
606
607 end INSERT_ROW;
608 procedure LOCK_ROW (
609 X_ROWID in VARCHAR2,
610 X_PERSON_ID in NUMBER,
611 X_COURSE_CD in VARCHAR2,
612 X_UNIT_CD in VARCHAR2,
613 X_CAL_TYPE in VARCHAR2,
614 X_CI_SEQUENCE_NUMBER in NUMBER,
615 X_ASS_ID in NUMBER,
616 X_CREATION_DT in DATE,
617 X_RECEIVED_DT in DATE,
618 X_SPCL_CONSIDERATION_CAT in VARCHAR2,
619 X_SOUGHT_OUTCOME in VARCHAR2,
620 X_SPCL_CONSIDERATION_OUTCOME in VARCHAR2,
621 X_ESTIMATED_PROCESSING_DAYS in NUMBER,
622 X_TRACKING_ID in NUMBER,
623 X_COMMENTS in VARCHAR2,
624 X_UOO_ID in NUMBER,
625 X_NOTIFIED_DATE in DATE
626 ) AS
627 cursor c1 is select
628 SPCL_CONSIDERATION_CAT,
629 SOUGHT_OUTCOME,
630 SPCL_CONSIDERATION_OUTCOME,
631 ESTIMATED_PROCESSING_DAYS,
632 TRACKING_ID,
633 COMMENTS
634 from IGS_AS_SPL_CONS_APPL
635 where ROWID = X_ROWID for update nowait;
636 tlinfo c1%rowtype;
637 begin
638 open c1;
639 fetch c1 into tlinfo;
640 if (c1%notfound) then
641 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
642 IGS_GE_MSG_STACK.ADD;
643 APP_EXCEPTION.RAISE_EXCEPTION;
644 close c1;
645 return;
646 end if;
647 close c1;
648 if ( (tlinfo.SPCL_CONSIDERATION_CAT = X_SPCL_CONSIDERATION_CAT)
649 AND ((tlinfo.SOUGHT_OUTCOME = X_SOUGHT_OUTCOME)
650 OR ((tlinfo.SOUGHT_OUTCOME is null)
651 AND (X_SOUGHT_OUTCOME is null)))
652 AND ((tlinfo.SPCL_CONSIDERATION_OUTCOME = X_SPCL_CONSIDERATION_OUTCOME)
653 OR ((tlinfo.SPCL_CONSIDERATION_OUTCOME is null)
654 AND (X_SPCL_CONSIDERATION_OUTCOME is null)))
655 AND ((tlinfo.ESTIMATED_PROCESSING_DAYS = X_ESTIMATED_PROCESSING_DAYS)
656 OR ((tlinfo.ESTIMATED_PROCESSING_DAYS is null)
657 AND (X_ESTIMATED_PROCESSING_DAYS is null)))
658 AND ((tlinfo.TRACKING_ID = X_TRACKING_ID)
659 OR ((tlinfo.TRACKING_ID is null)
660 AND (X_TRACKING_ID is null)))
661 AND ((tlinfo.COMMENTS = X_COMMENTS)
662 OR ((tlinfo.COMMENTS is null)
663 AND (X_COMMENTS is null)))
664 ) then
665 null;
666 else
667 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
668 IGS_GE_MSG_STACK.ADD;
669 APP_EXCEPTION.RAISE_EXCEPTION;
670 end if;
671 return;
672 end LOCK_ROW;
673 procedure UPDATE_ROW (
674 X_ROWID in VARCHAR2,
675 X_PERSON_ID in NUMBER,
676 X_COURSE_CD in VARCHAR2,
677 X_UNIT_CD in VARCHAR2,
678 X_CAL_TYPE in VARCHAR2,
679 X_CI_SEQUENCE_NUMBER in NUMBER,
680 X_ASS_ID in NUMBER,
681 X_CREATION_DT in DATE,
682 X_RECEIVED_DT in DATE,
683 X_SPCL_CONSIDERATION_CAT in VARCHAR2,
684 X_SOUGHT_OUTCOME in VARCHAR2,
685 X_SPCL_CONSIDERATION_OUTCOME in VARCHAR2,
686 X_ESTIMATED_PROCESSING_DAYS in NUMBER,
687 X_TRACKING_ID in NUMBER,
688 X_COMMENTS in VARCHAR2,
689 X_MODE in VARCHAR2 default 'R',
690 X_UOO_ID in NUMBER,
691 X_NOTIFIED_DATE in DATE
692 ) AS
693 X_LAST_UPDATE_DATE DATE;
694 X_LAST_UPDATED_BY NUMBER;
695 X_LAST_UPDATE_LOGIN NUMBER;
696 begin
697 X_LAST_UPDATE_DATE := SYSDATE;
698 if(X_MODE = 'I') then
699 X_LAST_UPDATED_BY := 1;
700 X_LAST_UPDATE_LOGIN := 0;
701 elsif (X_MODE IN ('R', 'S')) then
702 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
703 if X_LAST_UPDATED_BY is NULL then
704 X_LAST_UPDATED_BY := -1;
705 end if;
706 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
707 if X_LAST_UPDATE_LOGIN is NULL then
708 X_LAST_UPDATE_LOGIN := -1;
709 end if;
710 else
711 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
712 IGS_GE_MSG_STACK.ADD;
713 APP_EXCEPTION.RAISE_EXCEPTION;
714 end if;
715 Before_DML(
716 p_action=>'UPDATE',
717 x_rowid=>X_ROWID,
718 x_ass_id=>X_ASS_ID,
719 x_cal_type=>X_CAL_TYPE,
720 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
721 x_comments=>X_COMMENTS,
722 x_course_cd=>X_COURSE_CD,
723 x_creation_dt=>X_CREATION_DT,
724 x_estimated_processing_days=>X_ESTIMATED_PROCESSING_DAYS,
725 x_person_id=>X_PERSON_ID,
726 x_received_dt=>X_RECEIVED_DT,
727 x_sought_outcome=>X_SOUGHT_OUTCOME,
728 x_spcl_consideration_cat=>X_SPCL_CONSIDERATION_CAT,
729 x_spcl_consideration_outcome=>X_SPCL_CONSIDERATION_OUTCOME,
730 x_tracking_id=>X_TRACKING_ID,
731 x_unit_cd=>X_UNIT_CD,
732 x_creation_date=>X_LAST_UPDATE_DATE,
733 x_created_by=>X_LAST_UPDATED_BY,
734 x_last_update_date=>X_LAST_UPDATE_DATE,
735 x_last_updated_by=>X_LAST_UPDATED_BY,
736 x_last_update_login=>X_LAST_UPDATE_LOGIN,
737 x_uoo_id=>X_UOO_ID,
738 x_notified_date => X_NOTIFIED_DATE
739 );
740 IF (x_mode = 'S') THEN
741 igs_sc_gen_001.set_ctx('R');
742 END IF;
743 update IGS_AS_SPL_CONS_APPL set
744 SPCL_CONSIDERATION_CAT = NEW_REFERENCES.SPCL_CONSIDERATION_CAT,
745 SOUGHT_OUTCOME = NEW_REFERENCES.SOUGHT_OUTCOME,
746 SPCL_CONSIDERATION_OUTCOME = NEW_REFERENCES.SPCL_CONSIDERATION_OUTCOME,
747 ESTIMATED_PROCESSING_DAYS = NEW_REFERENCES.ESTIMATED_PROCESSING_DAYS,
748 TRACKING_ID = NEW_REFERENCES.TRACKING_ID,
749 COMMENTS = NEW_REFERENCES.COMMENTS,
750 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
751 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
752 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
753 NOTIFIED_DATE = X_NOTIFIED_DATE
754 where ROWID = X_ROWID;
755 if (sql%notfound) then
756 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
757 igs_ge_msg_stack.add;
758 igs_sc_gen_001.unset_ctx('R');
759 app_exception.raise_exception;
760 end if;
761 IF (x_mode = 'S') THEN
762 igs_sc_gen_001.unset_ctx('R');
763 END IF;
764
765
766 EXCEPTION
767 WHEN OTHERS THEN
768 IF (SQLCODE = (-28115)) THEN
769 fnd_message.set_name ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
770 fnd_message.set_token ('ERR_CD', SQLCODE);
771 igs_ge_msg_stack.add;
772 igs_sc_gen_001.unset_ctx('R');
773 app_exception.raise_exception;
774 ELSE
775 igs_sc_gen_001.unset_ctx('R');
776 RAISE;
777 END IF;
778
779 end UPDATE_ROW;
780 procedure ADD_ROW (
781 X_ROWID in out NOCOPY VARCHAR2,
782 X_PERSON_ID in NUMBER,
783 X_COURSE_CD in VARCHAR2,
784 X_UNIT_CD in VARCHAR2,
785 X_CAL_TYPE in VARCHAR2,
786 X_CI_SEQUENCE_NUMBER in NUMBER,
787 X_ASS_ID in NUMBER,
788 X_CREATION_DT in DATE,
789 X_RECEIVED_DT in DATE,
790 X_SPCL_CONSIDERATION_CAT in VARCHAR2,
791 X_SOUGHT_OUTCOME in VARCHAR2,
792 X_SPCL_CONSIDERATION_OUTCOME in VARCHAR2,
793 X_ESTIMATED_PROCESSING_DAYS in NUMBER,
794 X_TRACKING_ID in NUMBER,
795 X_COMMENTS in VARCHAR2,
796 X_MODE in VARCHAR2 default 'R',
797 X_UOO_ID in NUMBER,
798 X_NOTIFIED_DATE in DATE
799 ) AS
800 cursor c1 is select rowid from IGS_AS_SPL_CONS_APPL
801 where PERSON_ID = X_PERSON_ID
802 and COURSE_CD = X_COURSE_CD
803 and ASS_ID = X_ASS_ID
804 and CREATION_DT = X_CREATION_DT
805 and RECEIVED_DT = X_RECEIVED_DT
806 and UOO_ID = X_UOO_ID;
807 begin
808 open c1;
809 fetch c1 into X_ROWID;
810 if (c1%notfound) then
811 close c1;
812 INSERT_ROW (
813 X_ROWID,
814 X_PERSON_ID,
815 X_COURSE_CD,
816 X_UNIT_CD,
817 X_CAL_TYPE,
818 X_CI_SEQUENCE_NUMBER,
819 X_ASS_ID,
820 X_CREATION_DT,
821 X_RECEIVED_DT,
822 X_SPCL_CONSIDERATION_CAT,
823 X_SOUGHT_OUTCOME,
824 X_SPCL_CONSIDERATION_OUTCOME,
825 X_ESTIMATED_PROCESSING_DAYS,
826 X_TRACKING_ID,
827 X_COMMENTS,
828 X_MODE,
829 X_UOO_ID,
830 X_NOTIFIED_DATE );
831 return;
832 end if;
833 close c1;
834 UPDATE_ROW (
835 X_ROWID,
836 X_PERSON_ID,
837 X_COURSE_CD,
838 X_UNIT_CD,
839 X_CAL_TYPE,
840 X_CI_SEQUENCE_NUMBER,
841 X_ASS_ID,
842 X_CREATION_DT,
843 X_RECEIVED_DT,
844 X_SPCL_CONSIDERATION_CAT,
845 X_SOUGHT_OUTCOME,
846 X_SPCL_CONSIDERATION_OUTCOME,
847 X_ESTIMATED_PROCESSING_DAYS,
848 X_TRACKING_ID,
849 X_COMMENTS,
850 X_MODE,
851 X_UOO_ID,
852 X_NOTIFIED_DATE );
853 end ADD_ROW;
854 procedure DELETE_ROW (
855 X_ROWID in VARCHAR2,
856 x_mode IN VARCHAR2) AS
857 begin
858 Before_DML(
859 p_action => 'DELETE',
860 x_rowid => X_ROWID
861 );
862 IF (x_mode = 'S') THEN
863 igs_sc_gen_001.set_ctx('R');
864 END IF;
865 delete from IGS_AS_SPL_CONS_APPL
866 where ROWID = X_ROWID;
867 if (sql%notfound) then
868 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
869 igs_ge_msg_stack.add;
870 igs_sc_gen_001.unset_ctx('R');
871 app_exception.raise_exception;
872 end if;
873 IF (x_mode = 'S') THEN
874 igs_sc_gen_001.unset_ctx('R');
875 END IF;
876
877
878 end DELETE_ROW;
879
880 PROCEDURE Check_Constraints (
881 Column_Name IN VARCHAR2 DEFAULT NULL,
882 Column_Value IN VARCHAR2 DEFAULT NULL
883 )
884 AS
885 BEGIN
886
887 IF column_name is null then
888 NULL;
889 ELSIF upper(Column_name) = 'CI_SEQUENCE_NUMBER' then
890 new_references.CI_SEQUENCE_NUMBER := IGS_GE_NUMBER.TO_NUM(column_value);
891
892 ELSIF upper(Column_name) = 'ESTIMATED_PROCESSING_DAYS' then
893 new_references.ESTIMATED_PROCESSING_DAYS := IGS_GE_NUMBER.TO_NUM(column_value);
894
895 ELSIF upper(Column_name) = 'CAL_TYPE' then
896 new_references.CAL_TYPE := column_value;
897
898 ELSIF upper(Column_name) = 'SPCL_CONSIDERATION_CAT' then
899 new_references.SPCL_CONSIDERATION_CAT := column_value;
900
901 ELSIF upper(Column_name) = 'SPCL_CONSIDERATION_OUTCOME' then
902 new_references.SPCL_CONSIDERATION_OUTCOME := column_value;
903
904 ELSIF upper(Column_name) = 'COURSE_CD' then
905 new_references.COURSE_CD := column_value;
906
907 ELSIF upper(Column_name) = 'UNIT_CD' then
908 new_references.UNIT_CD := column_value;
909 end if;
910
911 IF upper(column_name) = 'CI_SEQUENCE_NUMBER ' OR
912 column_name is null Then
913 IF new_references.ci_sequence_number < 1 OR new_references.ci_sequence_number > 999999 Then
914 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
915 IGS_GE_MSG_STACK.ADD;
916 APP_EXCEPTION.RAISE_EXCEPTION;
917 END IF;
918 END IF;
919 IF upper(column_name) = 'ESTIMATED_PROCESSING_DAYS' OR
920 column_name is null Then
921 IF new_references.ESTIMATED_PROCESSING_DAYS < 0 OR new_references.ESTIMATED_PROCESSING_DAYS > 99 Then
922 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
923 IGS_GE_MSG_STACK.ADD;
924 APP_EXCEPTION.RAISE_EXCEPTION;
925 END IF;
926 END IF;
927
928
929 IF upper(column_name) = 'CAL_TYPE' OR
930 column_name is null Then
931 IF new_references.CAL_TYPE <> UPPER(new_references.CAL_TYPE) Then
932 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
933 IGS_GE_MSG_STACK.ADD;
934 APP_EXCEPTION.RAISE_EXCEPTION;
935 END IF;
936 END IF;
937 IF upper(column_name) = 'CAL_TYPE' OR
938 column_name is null Then
939 IF new_references.CAL_TYPE<> UPPER(new_references.CAL_TYPE) Then
940 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
941 IGS_GE_MSG_STACK.ADD;
942 APP_EXCEPTION.RAISE_EXCEPTION;
943 END IF;
944 END IF;
945 IF upper(column_name) = 'COURSE_CD' OR
946 column_name is null Then
947 IF new_references.COURSE_CD <> UPPER(new_references.COURSE_CD) Then
948 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
949 IGS_GE_MSG_STACK.ADD;
950 APP_EXCEPTION.RAISE_EXCEPTION;
951 END IF;
952 END IF;
953 IF upper(column_name) = 'SPCL_CONSIDERATION_CAT' OR
954 column_name is null Then
955 IF new_references.SPCL_CONSIDERATION_CAT <> UPPER(new_references.SPCL_CONSIDERATION_CAT) Then
956 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
957 IGS_GE_MSG_STACK.ADD;
958 APP_EXCEPTION.RAISE_EXCEPTION;
959 END IF;
960 END IF;
961 IF upper(column_name) = 'SPCL_CONSIDERATION_OUTCOME' OR
962 column_name is null Then
963 IF new_references.SPCL_CONSIDERATION_OUTCOME <> UPPER(new_references.SPCL_CONSIDERATION_OUTCOME) Then
964 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
965 IGS_GE_MSG_STACK.ADD;
966 APP_EXCEPTION.RAISE_EXCEPTION;
967 END IF;
968 END IF;
969 IF upper(column_name) = 'UNIT_CD' OR
970 column_name is null Then
971 IF new_references.UNIT_CD <> UPPER(new_references.UNIT_CD) Then
972 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
973 IGS_GE_MSG_STACK.ADD;
974 APP_EXCEPTION.RAISE_EXCEPTION;
975 END IF;
976 END IF;
977 END Check_Constraints;
978
979 end IGS_AS_SPL_CONS_APPL_PKG;