[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_PS_APINTUNTHS_PKG
Source
1 package body IGS_AD_PS_APINTUNTHS_PKG as
2 /* $Header: IGSAI22B.pls 120.0 2005/06/01 17:43:36 appldev noship $*/
3 l_rowid VARCHAR2(25);
4 old_references IGS_AD_PS_APINTUNTHS_ALL%RowType;
5 new_references IGS_AD_PS_APINTUNTHS_ALL%RowType;
6 PROCEDURE Set_Column_Values (
7 p_action IN VARCHAR2,
8 x_rowid IN VARCHAR2 DEFAULT NULL,
9 x_org_id IN NUMBER,
10 x_person_id IN NUMBER DEFAULT NULL,
11 x_admission_appl_number IN NUMBER DEFAULT NULL,
12 x_nominated_course_cd IN VARCHAR2 DEFAULT NULL,
13 x_acai_sequence_number IN NUMBER DEFAULT NULL,
14 x_unit_cd IN VARCHAR2 DEFAULT NULL,
15 x_hist_start_dt IN DATE DEFAULT NULL,
16 x_hist_end_dt IN DATE DEFAULT NULL,
17 x_hist_who IN NUMBER DEFAULT NULL,
18 x_uv_version_number IN NUMBER DEFAULT NULL,
19 x_cal_type IN VARCHAR2 DEFAULT NULL,
20 x_ci_sequence_number IN NUMBER DEFAULT NULL,
21 x_location_cd IN VARCHAR2 DEFAULT NULL,
22 x_unit_class IN VARCHAR2 DEFAULT NULL,
23 x_unit_mode IN VARCHAR2 DEFAULT NULL,
24 x_adm_unit_outcome_status IN VARCHAR2 DEFAULT NULL,
25 x_ass_tracking_id IN NUMBER DEFAULT NULL,
26 x_rule_waived_dt IN DATE DEFAULT NULL,
27 x_rule_waived_person_id IN NUMBER DEFAULT NULL,
28 x_sup_unit_cd IN VARCHAR2 DEFAULT NULL,
29 x_sup_uv_version_number IN NUMBER DEFAULT NULL,
30 x_creation_date IN DATE DEFAULT NULL,
31 x_created_by IN NUMBER DEFAULT NULL,
32 x_last_update_date IN DATE DEFAULT NULL,
33 x_last_updated_by IN NUMBER DEFAULT NULL,
34 x_last_update_login IN NUMBER DEFAULT NULL,
35 x_adm_ps_appl_inst_unit_id IN NUMBER DEFAULT NULL,
36 x_adm_ps_appl_inst_unithist_id IN NUMBER DEFAULT NULL
37 ) AS
38
39 CURSOR cur_old_ref_values IS
40 SELECT *
41 FROM IGS_AD_PS_APINTUNTHS_ALL
42 WHERE rowid = x_rowid;
43
44 BEGIN
45
46 l_rowid := x_rowid;
47
48 -- Code for setting the Old and New Reference Values.
49 -- Populate Old Values.
50 Open cur_old_ref_values;
51 Fetch cur_old_ref_values INTO old_references;
52 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
53 Close cur_old_ref_values;
54 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
55 IGS_GE_MSG_STACK.ADD;
56 App_Exception.Raise_Exception;
57 Return;
58 END IF;
59 Close cur_old_ref_values;
60
61 -- Populate New Values.
62 new_references.org_id := x_org_id;
63 new_references.person_id := x_person_id;
64 new_references.admission_appl_number := x_admission_appl_number;
65 new_references.nominated_course_cd := x_nominated_course_cd;
66 new_references.acai_sequence_number := x_acai_sequence_number;
67 new_references.unit_cd := x_unit_cd;
68 new_references.hist_start_dt := x_hist_start_dt;
69 new_references.hist_end_dt := x_hist_end_dt;
70 new_references.hist_who := x_hist_who;
71 new_references.uv_version_number := x_uv_version_number;
72 new_references.cal_type := x_cal_type;
73 new_references.ci_sequence_number := x_ci_sequence_number;
74 new_references.location_cd := x_location_cd;
75 new_references.unit_class := x_unit_class;
76 new_references.unit_mode := x_unit_mode;
77 new_references.adm_unit_outcome_status := x_adm_unit_outcome_status;
78 new_references.ass_tracking_id := x_ass_tracking_id;
79 new_references.rule_waived_dt := TRUNC(x_rule_waived_dt);
80 new_references.rule_waived_person_id := x_rule_waived_person_id;
81 new_references.sup_unit_cd := x_sup_unit_cd;
82 new_references.sup_uv_version_number := x_sup_uv_version_number;
83 new_references.adm_ps_appl_inst_unit_id := x_adm_ps_appl_inst_unit_id;
84 new_references.adm_ps_appl_inst_unit_hist_id := x_adm_ps_appl_inst_unithist_id;
85 IF (p_action = 'UPDATE') THEN
86 new_references.creation_date := old_references.creation_date;
87 new_references.created_by := old_references.created_by;
88 ELSE
89 new_references.creation_date := x_creation_date;
90 new_references.created_by := x_created_by;
91 END IF;
92 new_references.last_update_date := x_last_update_date;
93 new_references.last_updated_by := x_last_updated_by;
94 new_references.last_update_login := x_last_update_login;
95
96 END Set_Column_Values;
97
98 PROCEDURE Check_Constraints (
99 Column_Name IN VARCHAR2 DEFAULT NULL,
100 Column_Value IN VARCHAR2 DEFAULT NULL
101 )
102 AS
103 BEGIN
104 IF column_name is null then
105 NULL;
106 ELSIF upper(Column_name) = 'ADM_UNIT_OUTCOME_STATUS' then
107 new_references.adm_unit_outcome_status := column_value;
108 ELSIF upper(Column_name) = 'CAL_TYPE' then
109 new_references.cal_type := column_value;
110 ELSIF upper(Column_name) = 'LOCATION_CD' then
111 new_references.location_cd := column_value;
112 ELSIF upper(Column_name) = 'SUP_UNIT_CD' then
113 new_references.sup_unit_cd := column_value;
114 ELSIF upper(Column_name) = 'UNIT_CD' then
115 new_references.unit_cd := column_value;
116 ELSIF upper(Column_name) = 'UNIT_CLASS' then
117 new_references.unit_class := column_value;
118 ELSIF upper(Column_name) = 'UNIT_MODE' then
119 new_references.unit_mode := column_value;
120 ELSIF upper(Column_name) = 'CI_SEQUENCE_NUMBER' then
121 new_references.ci_sequence_number := igs_ge_number.to_num(column_value);
122 END IF;
123
124 IF upper(column_name) = 'ADM_UNIT_OUTCOME_STATUS' OR column_name is null Then
125 IF new_references.adm_unit_outcome_status <> UPPER(new_references.adm_unit_outcome_status) Then
126 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
127 IGS_GE_MSG_STACK.ADD;
128 App_Exception.Raise_Exception;
129 END IF;
130 END IF;
131
132 IF upper(column_name) = 'CAL_TYPE' OR column_name is null Then
133 IF new_references.cal_type <> UPPER(new_references.cal_type) Then
134 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
135 IGS_GE_MSG_STACK.ADD;
136 App_Exception.Raise_Exception;
137 END IF;
138 END IF;
139
140 IF upper(column_name) = 'LOCATION_CD' OR column_name is null Then
141 IF new_references.location_cd <> UPPER(new_references.location_cd) Then
142 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
143 IGS_GE_MSG_STACK.ADD;
144 App_Exception.Raise_Exception;
145 END IF;
146 END IF;
147
148 IF upper(column_name) = 'SUP_UNIT_CD' OR column_name is null Then
149 IF new_references.sup_unit_cd <> UPPER(new_references.sup_unit_cd) Then
150 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
151 IGS_GE_MSG_STACK.ADD;
152 App_Exception.Raise_Exception;
153 END IF;
154 END IF;
155
156 IF upper(column_name) = 'UNIT_CD' OR column_name is null Then
157 IF new_references.unit_cd <> UPPER(new_references.unit_cd) Then
158 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
159 IGS_GE_MSG_STACK.ADD;
160 App_Exception.Raise_Exception;
161 END IF;
162 END IF;
163
164 IF upper(column_name) = 'UNIT_CLASS' OR column_name is null Then
165 IF new_references.unit_class <> UPPER(new_references.unit_class) Then
166 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
167 IGS_GE_MSG_STACK.ADD;
168 App_Exception.Raise_Exception;
169 END IF;
170 END IF;
171
172 IF upper(column_name) = 'UNIT_MODE' OR column_name is null Then
173 IF new_references.unit_mode <> UPPER(new_references.unit_mode) Then
174 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
175 IGS_GE_MSG_STACK.ADD;
176 App_Exception.Raise_Exception;
177 END IF;
178 END IF;
179
180 IF upper(column_name) = 'CI_SEQUENCE_NUMBER' OR column_name is null Then
181 IF new_references.ci_sequence_number < 1 OR new_references.ci_sequence_number > 999999 Then
182 Fnd_Message.Set_Name ('IGS', 'IGS_GE_INVALID_VALUE');
183 IGS_GE_MSG_STACK.ADD;
184 App_Exception.Raise_Exception;
185 END IF;
186 END IF;
187 END Check_Constraints;
188
189 FUNCTION Get_PK_For_Validation (
190 x_adm_ps_appl_inst_unithist_id IN NUMBER
191 )
192 RETURN BOOLEAN
193 AS
194 CURSOR cur_rowid IS
195 SELECT rowid
196 FROM IGS_AD_PS_APINTUNTHS_ALL
197 WHERE adm_ps_appl_inst_unit_hist_id = x_adm_ps_appl_inst_unithist_id
198 FOR UPDATE NOWAIT;
199
200 lv_rowid cur_rowid%RowType;
201
202 BEGIN
203
204 Open cur_rowid;
205 Fetch cur_rowid INTO lv_rowid;
206 IF (cur_rowid%FOUND) THEN
207 Close cur_rowid;
208 Return (TRUE);
209 ELSE
210 Close cur_rowid;
211 Return (FALSE);
212 END IF;
213 END Get_PK_For_Validation;
214
215
216 FUNCTION Get_UK_For_Validation (
217 x_adm_ps_appl_inst_unit_id IN NUMBER,
218 x_hist_start_dt IN DATE
219 )
220 RETURN BOOLEAN
221 AS
222 CURSOR cur_rowid IS
223 SELECT rowid
224 FROM IGS_AD_PS_APINTUNTHS_ALL
225 WHERE adm_ps_appl_inst_unit_id = x_adm_ps_appl_inst_unit_id
226 AND hist_start_dt = x_hist_start_dt
227 AND (l_rowid IS NULL OR rowid <> l_rowid)
228 FOR UPDATE NOWAIT;
229
230 lv_rowid cur_rowid%RowType;
231
232 BEGIN
233
234 Open cur_rowid;
235 Fetch cur_rowid INTO lv_rowid;
236 IF (cur_rowid%FOUND) THEN
237 Close cur_rowid;
238 Return (TRUE);
239 ELSE
240 Close cur_rowid;
241 Return (FALSE);
242 END IF;
243 END Get_UK_For_Validation;
244
245 PROCEDURE Before_DML (
246 p_action IN VARCHAR2,
247 x_rowid IN VARCHAR2 DEFAULT NULL,
248 x_org_id IN NUMBER DEFAULT NULL,
249 x_person_id IN NUMBER DEFAULT NULL,
250 x_admission_appl_number IN NUMBER DEFAULT NULL,
251 x_nominated_course_cd IN VARCHAR2 DEFAULT NULL,
252 x_acai_sequence_number IN NUMBER DEFAULT NULL,
253 x_unit_cd IN VARCHAR2 DEFAULT NULL,
254 x_hist_start_dt IN DATE DEFAULT NULL,
255 x_hist_end_dt IN DATE DEFAULT NULL,
256 x_hist_who IN NUMBER DEFAULT NULL,
257 x_uv_version_number IN NUMBER DEFAULT NULL,
258 x_cal_type IN VARCHAR2 DEFAULT NULL,
259 x_ci_sequence_number IN NUMBER DEFAULT NULL,
260 x_location_cd IN VARCHAR2 DEFAULT NULL,
261 x_unit_class IN VARCHAR2 DEFAULT NULL,
262 x_unit_mode IN VARCHAR2 DEFAULT NULL,
263 x_adm_unit_outcome_status IN VARCHAR2 DEFAULT NULL,
264 x_ass_tracking_id IN NUMBER DEFAULT NULL,
265 x_rule_waived_dt IN DATE DEFAULT NULL,
266 x_rule_waived_person_id IN NUMBER DEFAULT NULL,
267 x_sup_unit_cd IN VARCHAR2 DEFAULT NULL,
268 x_sup_uv_version_number IN NUMBER DEFAULT NULL,
269 x_creation_date IN DATE DEFAULT NULL,
270 x_created_by IN NUMBER DEFAULT NULL,
271 x_last_update_date IN DATE DEFAULT NULL,
272 x_last_updated_by IN NUMBER DEFAULT NULL,
273 x_last_update_login IN NUMBER DEFAULT NULL,
274 x_adm_ps_appl_inst_unit_id IN NUMBER DEFAULT NULL,
275 x_adm_ps_appl_inst_unithist_id IN NUMBER DEFAULT NULL
276 ) AS
277 BEGIN
278
279 Set_Column_Values (
280 p_action,
281 x_rowid,
282 x_org_id,
283 x_person_id,
284 x_admission_appl_number,
285 x_nominated_course_cd,
286 x_acai_sequence_number,
287 x_unit_cd,
288 x_hist_start_dt,
289 x_hist_end_dt,
290 x_hist_who,
291 x_uv_version_number,
292 x_cal_type,
293 x_ci_sequence_number,
294 x_location_cd,
295 x_unit_class,
296 x_unit_mode,
297 x_adm_unit_outcome_status,
298 x_ass_tracking_id,
299 x_rule_waived_dt,
300 x_rule_waived_person_id,
301 x_sup_unit_cd,
302 x_sup_uv_version_number,
303 x_creation_date,
304 x_created_by,
305 x_last_update_date,
306 x_last_updated_by,
307 x_last_update_login,
308 x_adm_ps_appl_inst_unit_id,
309 x_adm_ps_appl_inst_unithist_id
310 );
311
312 IF (p_action = 'INSERT') THEN
313 -- Call all the procedures related to Before Insert.
314 IF Get_PK_For_Validation (new_references.adm_ps_appl_inst_unit_hist_id) OR
315 Get_UK_For_Validation (
316 new_references.adm_ps_appl_inst_unit_id,
317 new_references.hist_start_dt
318 ) THEN
319 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
320 IGS_GE_MSG_STACK.ADD;
321 App_Exception.Raise_Exception;
322 END IF;
323 Check_Constraints;
324 ELSIF (p_action = 'UPDATE') THEN
325 new_references.adm_ps_appl_inst_unit_hist_id := old_references.adm_ps_appl_inst_unit_hist_id;
326 IF Get_UK_For_Validation (
327 new_references.adm_ps_appl_inst_unit_id,
328 new_references.hist_start_dt
329 ) THEN
330 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
331 IGS_GE_MSG_STACK.ADD;
332 App_Exception.Raise_Exception;
333 END IF;
334 Check_Constraints;
335 ELSIF (p_action = 'VALIDATE_INSERT') THEN
336 IF Get_PK_For_Validation (new_references.adm_ps_appl_inst_unit_hist_id) OR
337 Get_UK_For_Validation (
338 new_references.adm_ps_appl_inst_unit_id,
339 new_references.hist_start_dt
340 ) THEN
341 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
342 IGS_GE_MSG_STACK.ADD;
343 App_Exception.Raise_Exception;
344 END IF;
345 Check_Constraints;
346 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
347 new_references.adm_ps_appl_inst_unit_hist_id := old_references.adm_ps_appl_inst_unit_hist_id;
348 IF Get_UK_For_Validation (
349 new_references.adm_ps_appl_inst_unit_id,
350 new_references.hist_start_dt
351 ) THEN
352 Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
353 IGS_GE_MSG_STACK.ADD;
354 App_Exception.Raise_Exception;
355 END IF;
356 Check_Constraints;
357 END IF;
358 END Before_DML;
359
360 PROCEDURE After_DML (
361 p_action IN VARCHAR2,
362 x_rowid IN VARCHAR2
363 ) AS
364 BEGIN
365
366 l_rowid := x_rowid;
367
368 END After_DML;
369
370 procedure INSERT_ROW (
371 X_ROWID in out NOCOPY VARCHAR2,
372 x_ORG_ID in NUMBER,
373 X_PERSON_ID in NUMBER,
374 X_ADMISSION_APPL_NUMBER in NUMBER,
375 X_NOMINATED_COURSE_CD in VARCHAR2,
376 X_ACAI_SEQUENCE_NUMBER in NUMBER,
377 X_UNIT_CD in VARCHAR2,
378 X_HIST_START_DT in DATE,
379 X_HIST_END_DT in DATE,
380 X_HIST_WHO in NUMBER,
381 X_UV_VERSION_NUMBER in NUMBER,
382 X_CAL_TYPE in VARCHAR2,
383 X_CI_SEQUENCE_NUMBER in NUMBER,
384 X_LOCATION_CD in VARCHAR2,
385 X_UNIT_CLASS in VARCHAR2,
386 X_UNIT_MODE in VARCHAR2,
387 X_ADM_UNIT_OUTCOME_STATUS in VARCHAR2,
388 X_ASS_TRACKING_ID in NUMBER,
389 X_RULE_WAIVED_DT in DATE,
390 X_RULE_WAIVED_PERSON_ID in NUMBER,
391 X_SUP_UNIT_CD in VARCHAR2,
392 X_SUP_UV_VERSION_NUMBER in NUMBER,
393 X_MODE in VARCHAR2,
394 x_adm_ps_appl_inst_unit_id IN NUMBER DEFAULT NULL,
395 x_adm_ps_appl_inst_unithist_id IN OUT NOCOPY NUMBER
396 ) AS
397 cursor C is select ROWID, ADM_PS_APPL_INST_UNIT_HIST_ID from IGS_AD_PS_APINTUNTHS_ALL
398 where ADM_PS_APPL_INST_UNIT_ID = X_ADM_PS_APPL_INST_UNIT_ID
399 and HIST_START_DT = X_HIST_START_DT;
400 X_LAST_UPDATE_DATE DATE;
401 X_LAST_UPDATED_BY NUMBER;
405 if(X_MODE = 'I') then
402 X_LAST_UPDATE_LOGIN NUMBER;
403 begin
404 X_LAST_UPDATE_DATE := SYSDATE;
406 X_LAST_UPDATED_BY := 1;
407 X_LAST_UPDATE_LOGIN := 0;
408 elsif (X_MODE = 'R') then
409 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
410 if X_LAST_UPDATED_BY is NULL then
411 X_LAST_UPDATED_BY := -1;
412 end if;
413 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
414 if X_LAST_UPDATE_LOGIN is NULL then
415 X_LAST_UPDATE_LOGIN := -1;
416 end if;
417 else
418 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
419 IGS_GE_MSG_STACK.ADD;
420 app_exception.raise_exception;
421 end if;
422 Before_DML(p_action =>'INSERT',
423 x_rowid =>X_ROWID,
424 x_org_id => igs_ge_gen_003.get_org_id,
425 x_person_id=>X_PERSON_ID ,
426 x_admission_appl_number=>X_ADMISSION_APPL_NUMBER ,
427 x_nominated_course_cd=>X_NOMINATED_COURSE_CD,
428 x_acai_sequence_number=>X_ACAI_SEQUENCE_NUMBER,
429 x_unit_cd=>X_UNIT_CD,
430 x_hist_start_dt=>X_HIST_START_DT,
431 x_hist_end_dt=>X_HIST_END_DT,
432 x_hist_who=>X_HIST_WHO,
433 x_uv_version_number=>X_UV_VERSION_NUMBER,
434 x_cal_type=>X_CAL_TYPE,
435 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
436 x_location_cd=>X_LOCATION_CD,
437 x_unit_class=>X_UNIT_CLASS,
438 x_unit_mode=>X_UNIT_MODE,
439 x_adm_unit_outcome_status=>X_ADM_UNIT_OUTCOME_STATUS,
440 x_ass_tracking_id=>X_ASS_TRACKING_ID,
441 x_rule_waived_dt=>X_RULE_WAIVED_DT,
442 x_rule_waived_person_id=>X_RULE_WAIVED_PERSON_ID,
443 x_sup_unit_cd=>X_SUP_UNIT_CD,
444 x_sup_uv_version_number=>X_SUP_UV_VERSION_NUMBER,
445 x_creation_date => X_LAST_UPDATE_DATE,
446 x_created_by => X_LAST_UPDATED_BY,
447 x_last_update_date => X_LAST_UPDATE_DATE,
448 x_last_updated_by => X_LAST_UPDATED_BY,
449 x_last_update_login => X_LAST_UPDATE_LOGIN,
450 x_adm_ps_appl_inst_unit_id => X_ADM_PS_APPL_INST_UNIT_ID,
451 x_adm_ps_appl_inst_unithist_id => X_ADM_PS_APPL_INST_UNITHIST_ID
452 );
453
454 insert into IGS_AD_PS_APINTUNTHS_ALL (
455 ORG_ID,
456 PERSON_ID,
457 ADMISSION_APPL_NUMBER,
458 NOMINATED_COURSE_CD,
459 ACAI_SEQUENCE_NUMBER,
460 UNIT_CD,
461 HIST_START_DT,
462 HIST_END_DT,
463 HIST_WHO,
464 UV_VERSION_NUMBER,
465 CAL_TYPE,
466 CI_SEQUENCE_NUMBER,
467 LOCATION_CD,
468 UNIT_CLASS,
469 UNIT_MODE,
470 ADM_UNIT_OUTCOME_STATUS,
471 ASS_TRACKING_ID,
472 RULE_WAIVED_DT,
473 RULE_WAIVED_PERSON_ID,
474 SUP_UNIT_CD,
475 SUP_UV_VERSION_NUMBER,
476 CREATION_DATE,
477 CREATED_BY,
478 LAST_UPDATE_DATE,
479 LAST_UPDATED_BY,
480 LAST_UPDATE_LOGIN,
481 ADM_PS_APPL_INST_UNIT_ID,
482 ADM_PS_APPL_INST_UNIT_HIST_ID
483 ) values (
484 NEW_REFERENCES.ORG_ID,
485 NEW_REFERENCES.PERSON_ID,
486 NEW_REFERENCES.ADMISSION_APPL_NUMBER,
487 NEW_REFERENCES.NOMINATED_COURSE_CD,
488 NEW_REFERENCES.ACAI_SEQUENCE_NUMBER,
489 NEW_REFERENCES.UNIT_CD,
490 NEW_REFERENCES.HIST_START_DT,
491 NEW_REFERENCES.HIST_END_DT,
492 NEW_REFERENCES.HIST_WHO,
493 NEW_REFERENCES.UV_VERSION_NUMBER,
494 NEW_REFERENCES.CAL_TYPE,
495 NEW_REFERENCES.CI_SEQUENCE_NUMBER,
496 NEW_REFERENCES.LOCATION_CD,
497 NEW_REFERENCES.UNIT_CLASS,
498 NEW_REFERENCES.UNIT_MODE,
499 NEW_REFERENCES.ADM_UNIT_OUTCOME_STATUS,
500 NEW_REFERENCES.ASS_TRACKING_ID,
501 NEW_REFERENCES.RULE_WAIVED_DT,
502 NEW_REFERENCES.RULE_WAIVED_PERSON_ID,
503 NEW_REFERENCES.SUP_UNIT_CD,
504 NEW_REFERENCES.SUP_UV_VERSION_NUMBER,
505 X_LAST_UPDATE_DATE,
506 X_LAST_UPDATED_BY,
507 X_LAST_UPDATE_DATE,
508 X_LAST_UPDATED_BY,
509 X_LAST_UPDATE_LOGIN,
510 NEW_REFERENCES.ADM_PS_APPL_INST_UNIT_ID,
511 IGS_AD_PS_APINTUNTHS_S.NEXTVAL
512 );
513
514 open c;
515 fetch c into X_ROWID, X_ADM_PS_APPL_INST_UNITHIST_ID;
516 if (c%notfound) then
517 close c;
518 raise no_data_found;
519 end if;
520 close c;
521 After_DML(
522 p_action =>'INSERT',
523 x_rowid => X_ROWID
524 );
525 EXCEPTION
526 WHEN OTHERS THEN
527 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
528 -- Code to handle Security Policy error raised
529 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
530 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
531 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
532 -- that the ownerof policy function does not have privilege to access.
533 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
534 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
535 IGS_GE_MSG_STACK.ADD;
536 app_exception.raise_exception;
537 ELSE
538 RAISE;
539 END IF;
540 end INSERT_ROW;
541
542 procedure LOCK_ROW (
543 X_ROWID in VARCHAR2,
544 X_PERSON_ID in NUMBER,
548 X_UNIT_CD in VARCHAR2,
545 X_ADMISSION_APPL_NUMBER in NUMBER,
546 X_NOMINATED_COURSE_CD in VARCHAR2,
547 X_ACAI_SEQUENCE_NUMBER in NUMBER,
549 X_HIST_START_DT in DATE,
550 X_HIST_END_DT in DATE,
551 X_HIST_WHO in NUMBER,
552 X_UV_VERSION_NUMBER in NUMBER,
553 X_CAL_TYPE in VARCHAR2,
554 X_CI_SEQUENCE_NUMBER in NUMBER,
555 X_LOCATION_CD in VARCHAR2,
556 X_UNIT_CLASS in VARCHAR2,
557 X_UNIT_MODE in VARCHAR2,
558 X_ADM_UNIT_OUTCOME_STATUS in VARCHAR2,
559 X_ASS_TRACKING_ID in NUMBER,
560 X_RULE_WAIVED_DT in DATE,
561 X_RULE_WAIVED_PERSON_ID in NUMBER,
562 X_SUP_UNIT_CD in VARCHAR2,
563 X_SUP_UV_VERSION_NUMBER in NUMBER,
564 x_adm_ps_appl_inst_unit_id IN NUMBER DEFAULT NULL,
565 x_adm_ps_appl_inst_unithist_id IN NUMBER DEFAULT NULL
566 ) AS
567 cursor c1 is select
568 HIST_END_DT,
569 HIST_WHO,
570 UV_VERSION_NUMBER,
571 CAL_TYPE,
572 CI_SEQUENCE_NUMBER,
573 LOCATION_CD,
574 UNIT_CLASS,
575 UNIT_MODE,
576 ADM_UNIT_OUTCOME_STATUS,
577 ASS_TRACKING_ID,
578 RULE_WAIVED_DT,
579 RULE_WAIVED_PERSON_ID,
580 SUP_UNIT_CD,
581 SUP_UV_VERSION_NUMBER
582 from IGS_AD_PS_APINTUNTHS_ALL
583 where ROWID = X_ROWID for update nowait;
584 tlinfo c1%rowtype;
585
586 begin
587 open c1;
588 fetch c1 into tlinfo;
589 if (c1%notfound) then
590 close c1;
591 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
592 IGS_GE_MSG_STACK.ADD;
593 app_exception.raise_exception;
594 return;
595 end if;
596 close c1;
597
598 if ( (tlinfo.HIST_END_DT = X_HIST_END_DT)
599 AND (tlinfo.HIST_WHO = X_HIST_WHO)
600 AND ((tlinfo.UV_VERSION_NUMBER = X_UV_VERSION_NUMBER)
601 OR ((tlinfo.UV_VERSION_NUMBER is null)
602 AND (X_UV_VERSION_NUMBER is null)))
603 AND ((tlinfo.CAL_TYPE = X_CAL_TYPE)
604 OR ((tlinfo.CAL_TYPE is null)
605 AND (X_CAL_TYPE is null)))
606 AND ((tlinfo.CI_SEQUENCE_NUMBER = X_CI_SEQUENCE_NUMBER)
607 OR ((tlinfo.CI_SEQUENCE_NUMBER is null)
608 AND (X_CI_SEQUENCE_NUMBER is null)))
609 AND ((tlinfo.LOCATION_CD = X_LOCATION_CD)
610 OR ((tlinfo.LOCATION_CD is null)
611 AND (X_LOCATION_CD is null)))
612 AND ((tlinfo.UNIT_CLASS = X_UNIT_CLASS)
613 OR ((tlinfo.UNIT_CLASS is null)
614 AND (X_UNIT_CLASS is null)))
615 AND ((tlinfo.UNIT_MODE = X_UNIT_MODE)
616 OR ((tlinfo.UNIT_MODE is null)
617 AND (X_UNIT_MODE is null)))
618 AND ((tlinfo.ADM_UNIT_OUTCOME_STATUS = X_ADM_UNIT_OUTCOME_STATUS)
619 OR ((tlinfo.ADM_UNIT_OUTCOME_STATUS is null)
620 AND (X_ADM_UNIT_OUTCOME_STATUS is null)))
621 AND ((tlinfo.ASS_TRACKING_ID = X_ASS_TRACKING_ID)
622 OR ((tlinfo.ASS_TRACKING_ID is null)
623 AND (X_ASS_TRACKING_ID is null)))
624 AND ((TRUNC(tlinfo.RULE_WAIVED_DT) = TRUNC(X_RULE_WAIVED_DT))
625 OR ((tlinfo.RULE_WAIVED_DT is null)
626 AND (X_RULE_WAIVED_DT is null)))
627 AND ((tlinfo.RULE_WAIVED_PERSON_ID = X_RULE_WAIVED_PERSON_ID)
628 OR ((tlinfo.RULE_WAIVED_PERSON_ID is null)
629 AND (X_RULE_WAIVED_PERSON_ID is null)))
630 AND ((tlinfo.SUP_UNIT_CD = X_SUP_UNIT_CD)
631 OR ((tlinfo.SUP_UNIT_CD is null)
632 AND (X_SUP_UNIT_CD is null)))
633 AND ((tlinfo.SUP_UV_VERSION_NUMBER = X_SUP_UV_VERSION_NUMBER)
634 OR ((tlinfo.SUP_UV_VERSION_NUMBER is null)
635 AND (X_SUP_UV_VERSION_NUMBER is null)))
636 ) then
637 null;
638 else
639 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
640 IGS_GE_MSG_STACK.ADD;
641 app_exception.raise_exception;
642 end if;
643 return;
644 end LOCK_ROW;
645
646 procedure UPDATE_ROW (
647 X_ROWID in VARCHAR2,
648 X_PERSON_ID in NUMBER,
649 X_ADMISSION_APPL_NUMBER in NUMBER,
650 X_NOMINATED_COURSE_CD in VARCHAR2,
651 X_ACAI_SEQUENCE_NUMBER in NUMBER,
652 X_UNIT_CD in VARCHAR2,
653 X_HIST_START_DT in DATE,
654 X_HIST_END_DT in DATE,
655 X_HIST_WHO in NUMBER,
656 X_UV_VERSION_NUMBER in NUMBER,
657 X_CAL_TYPE in VARCHAR2,
658 X_CI_SEQUENCE_NUMBER in NUMBER,
659 X_LOCATION_CD in VARCHAR2,
660 X_UNIT_CLASS in VARCHAR2,
661 X_UNIT_MODE in VARCHAR2,
662 X_ADM_UNIT_OUTCOME_STATUS in VARCHAR2,
663 X_ASS_TRACKING_ID in NUMBER,
664 X_RULE_WAIVED_DT in DATE,
665 X_RULE_WAIVED_PERSON_ID in NUMBER,
666 X_SUP_UNIT_CD in VARCHAR2,
667 X_SUP_UV_VERSION_NUMBER in NUMBER,
668 X_MODE in VARCHAR2,
669 x_adm_ps_appl_inst_unit_id IN NUMBER DEFAULT NULL,
670 x_adm_ps_appl_inst_unithist_id IN NUMBER DEFAULT NULL
671 ) AS
672 X_LAST_UPDATE_DATE DATE;
673 X_LAST_UPDATED_BY NUMBER;
674 X_LAST_UPDATE_LOGIN NUMBER;
675 begin
676 X_LAST_UPDATE_DATE := SYSDATE;
677 if(X_MODE = 'I') then
678 X_LAST_UPDATED_BY := 1;
679 X_LAST_UPDATE_LOGIN := 0;
680 elsif (X_MODE = 'R') then
681 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
685 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
682 if X_LAST_UPDATED_BY is NULL then
683 X_LAST_UPDATED_BY := -1;
684 end if;
686 if X_LAST_UPDATE_LOGIN is NULL then
687 X_LAST_UPDATE_LOGIN := -1;
688 end if;
689 else
690 FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
691 IGS_GE_MSG_STACK.ADD;
692 app_exception.raise_exception;
693 end if;
694 Before_DML(p_action =>'UPDATE',
695 x_rowid =>X_ROWID,
696 x_person_id=>X_PERSON_ID ,
697 x_admission_appl_number=>X_ADMISSION_APPL_NUMBER ,
698 x_nominated_course_cd=>X_NOMINATED_COURSE_CD,
699 x_acai_sequence_number=>X_ACAI_SEQUENCE_NUMBER,
700 x_unit_cd=>X_UNIT_CD,
701 x_hist_start_dt=>X_HIST_START_DT,
702 x_hist_end_dt=>X_HIST_END_DT,
703 x_hist_who=>X_HIST_WHO,
704 x_uv_version_number=>X_UV_VERSION_NUMBER,
705 x_cal_type=>X_CAL_TYPE,
706 x_ci_sequence_number=>X_CI_SEQUENCE_NUMBER,
707 x_location_cd=>X_LOCATION_CD,
708 x_unit_class=>X_UNIT_CLASS,
709 x_unit_mode=>X_UNIT_MODE,
710 x_adm_unit_outcome_status=>X_ADM_UNIT_OUTCOME_STATUS,
711 x_ass_tracking_id=>X_ASS_TRACKING_ID,
712 x_rule_waived_dt=>X_RULE_WAIVED_DT,
713 x_rule_waived_person_id=>X_RULE_WAIVED_PERSON_ID,
714 x_sup_unit_cd=>X_SUP_UNIT_CD,
715 x_sup_uv_version_number=>X_SUP_UV_VERSION_NUMBER,
716 x_creation_date => X_LAST_UPDATE_DATE,
717 x_created_by => X_LAST_UPDATED_BY,
718 x_last_update_date => X_LAST_UPDATE_DATE,
719 x_last_updated_by => X_LAST_UPDATED_BY,
720 x_last_update_login => X_LAST_UPDATE_LOGIN,
721 x_adm_ps_appl_inst_unit_id => X_ADM_PS_APPL_INST_UNIT_ID,
722 x_adm_ps_appl_inst_unithist_id => X_ADM_PS_APPL_INST_UNITHIST_ID
723 );
724
725 update IGS_AD_PS_APINTUNTHS_ALL set
726 HIST_END_DT = NEW_REFERENCES.HIST_END_DT,
727 HIST_WHO = NEW_REFERENCES.HIST_WHO,
728 UV_VERSION_NUMBER = NEW_REFERENCES.UV_VERSION_NUMBER,
729 CAL_TYPE = NEW_REFERENCES.CAL_TYPE,
730 CI_SEQUENCE_NUMBER = NEW_REFERENCES.CI_SEQUENCE_NUMBER,
731 LOCATION_CD = NEW_REFERENCES.LOCATION_CD,
732 UNIT_CLASS = NEW_REFERENCES.UNIT_CLASS,
733 UNIT_MODE = NEW_REFERENCES.UNIT_MODE,
734 ADM_UNIT_OUTCOME_STATUS = NEW_REFERENCES.ADM_UNIT_OUTCOME_STATUS,
735 ASS_TRACKING_ID = NEW_REFERENCES.ASS_TRACKING_ID,
736 RULE_WAIVED_DT = NEW_REFERENCES.RULE_WAIVED_DT,
737 RULE_WAIVED_PERSON_ID = NEW_REFERENCES.RULE_WAIVED_PERSON_ID,
738 SUP_UNIT_CD = NEW_REFERENCES.SUP_UNIT_CD,
739 SUP_UV_VERSION_NUMBER = NEW_REFERENCES.SUP_UV_VERSION_NUMBER,
740 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
741 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
742 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
743 where ROWID = X_ROWID
744 ;
745 if (sql%notfound) then
746 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
747 IGS_GE_MSG_STACK.ADD;
748 app_exception.raise_exception;
749 end if;
750 After_DML(
751 p_action =>'UPDATE',
752 x_rowid => X_ROWID
753 );
754
755 EXCEPTION
756 WHEN OTHERS THEN
757 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
758 -- Code to handle Security Policy error raised
759 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
760 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
761 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
762 -- that the ownerof policy function does not have privilege to access.
763 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
764 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
765 IGS_GE_MSG_STACK.ADD;
766 app_exception.raise_exception;
767 ELSE
768 RAISE;
769 END IF;
770 end UPDATE_ROW;
771
772 procedure ADD_ROW (
773 X_ROWID in out NOCOPY VARCHAR2,
774 X_ORG_ID in NUMBER,
775 X_PERSON_ID in NUMBER,
776 X_ADMISSION_APPL_NUMBER in NUMBER,
777 X_NOMINATED_COURSE_CD in VARCHAR2,
778 X_ACAI_SEQUENCE_NUMBER in NUMBER,
779 X_UNIT_CD in VARCHAR2,
780 X_HIST_START_DT in DATE,
781 X_HIST_END_DT in DATE,
782 X_HIST_WHO in NUMBER,
783 X_UV_VERSION_NUMBER in NUMBER,
784 X_CAL_TYPE in VARCHAR2,
785 X_CI_SEQUENCE_NUMBER in NUMBER,
786 X_LOCATION_CD in VARCHAR2,
787 X_UNIT_CLASS in VARCHAR2,
788 X_UNIT_MODE in VARCHAR2,
789 X_ADM_UNIT_OUTCOME_STATUS in VARCHAR2,
790 X_ASS_TRACKING_ID in NUMBER,
791 X_RULE_WAIVED_DT in DATE,
792 X_RULE_WAIVED_PERSON_ID in NUMBER,
793 X_SUP_UNIT_CD in VARCHAR2,
794 X_SUP_UV_VERSION_NUMBER in NUMBER,
795 X_MODE in VARCHAR2,
796 x_adm_ps_appl_inst_unit_id IN NUMBER DEFAULT NULL,
797 x_adm_ps_appl_inst_unithist_id IN OUT NOCOPY NUMBER
798 ) AS
799 cursor c1 is select rowid from IGS_AD_PS_APINTUNTHS_ALL
800 where adm_ps_appl_inst_unit_hist_id = x_adm_ps_appl_inst_unithist_id
801 ;
802
803 begin
804 open c1;
805 fetch c1 into X_ROWID;
806 if (c1%notfound) then
807 close c1;
808 INSERT_ROW (
809 X_ROWID,
810 X_ORG_ID,
811 X_PERSON_ID,
812 X_ADMISSION_APPL_NUMBER,
813 X_NOMINATED_COURSE_CD,
814 X_ACAI_SEQUENCE_NUMBER,
815 X_UNIT_CD,
816 X_HIST_START_DT,
817 X_HIST_END_DT,
818 X_HIST_WHO,
819 X_UV_VERSION_NUMBER,
820 X_CAL_TYPE,
821 X_CI_SEQUENCE_NUMBER,
822 X_LOCATION_CD,
823 X_UNIT_CLASS,
824 X_UNIT_MODE,
825 X_ADM_UNIT_OUTCOME_STATUS,
826 X_ASS_TRACKING_ID,
827 X_RULE_WAIVED_DT,
828 X_RULE_WAIVED_PERSON_ID,
829 X_SUP_UNIT_CD,
830 X_SUP_UV_VERSION_NUMBER,
831 X_MODE,
832 X_ADM_PS_APPL_INST_UNIT_ID,
833 X_ADM_PS_APPL_INST_UNITHIST_ID);
834 return;
835 end if;
836 close c1;
837 UPDATE_ROW (
838 X_ROWID,
839 X_PERSON_ID,
840 X_ADMISSION_APPL_NUMBER,
841 X_NOMINATED_COURSE_CD,
842 X_ACAI_SEQUENCE_NUMBER,
843 X_UNIT_CD,
844 X_HIST_START_DT,
845 X_HIST_END_DT,
846 X_HIST_WHO,
847 X_UV_VERSION_NUMBER,
848 X_CAL_TYPE,
849 X_CI_SEQUENCE_NUMBER,
850 X_LOCATION_CD,
851 X_UNIT_CLASS,
852 X_UNIT_MODE,
853 X_ADM_UNIT_OUTCOME_STATUS,
854 X_ASS_TRACKING_ID,
855 X_RULE_WAIVED_DT,
856 X_RULE_WAIVED_PERSON_ID,
857 X_SUP_UNIT_CD,
858 X_SUP_UV_VERSION_NUMBER,
859 X_MODE,
860 X_ADM_PS_APPL_INST_UNIT_ID,
861 X_ADM_PS_APPL_INST_UNITHIST_ID);
862 end ADD_ROW;
863
864 procedure DELETE_ROW (
865 X_ROWID in VARCHAR2
866 ) AS
867
868 e_resource_busy_exception EXCEPTION;
869 PRAGMA EXCEPTION_INIT(e_resource_busy_exception, -54);
870 v_message_name varchar2(30);
871
872 begin
873
874 Before_DML(
875 p_action =>'DELETE',
876 x_rowid => X_ROWID
877 );
878 -- set default value
879 v_message_name := null;
880 delete from IGS_AD_PS_APINTUNTHS_ALL
881 where ROWID = X_ROWID;
882 if (sql%notfound) then
883 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
884 IGS_GE_MSG_STACK.ADD;
885 app_exception.raise_exception;
886 end if;
887 After_DML(
888 p_action =>'DELETE',
889 x_rowid => X_ROWID
890 );
891 EXCEPTION
892 WHEN e_resource_busy_exception THEN
893 -- Set error message number
894 v_message_name := 'IGS_AD_UNABLE_TO_DELETE';
895 Fnd_Message.Set_Name('IGS',v_message_name);
896 IGS_GE_MSG_STACK.ADD;
897 App_Exception.Raise_Exception;
898 WHEN OTHERS THEN
899 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
900 -- Code to handle Security Policy error raised
901 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
902 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
903 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
904 -- that the ownerof policy function does not have privilege to access.
905 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
906 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
907 IGS_GE_MSG_STACK.ADD;
908 app_exception.raise_exception;
909 ELSE
910 RAISE;
911 END IF;
912 end DELETE_ROW;
913 end IGS_AD_PS_APINTUNTHS_PKG;