eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.IGS_AD_PS_APLINSTHST_PKG

Source


1 PACKAGE BODY IGS_AD_PS_APLINSTHST_PKG AS
2 /* $Header: IGSAI19B.pls 120.2 2005/09/21 00:32:55 appldev ship $*/
3 l_rowid VARCHAR2(25);
4 old_references IGS_AD_PS_APLINSTHST_ALL%RowType;
5 new_references IGS_AD_PS_APLINSTHST_ALL%RowType;
6 
7   PROCEDURE Set_Column_Values (
8     p_action IN VARCHAR2,
9     x_rowid IN VARCHAR2 DEFAULT NULL,
10                 x_org_id IN NUMBER DEFAULT NULL,
11     x_course_cd IN VARCHAR2 DEFAULT NULL,
12     x_crv_version_number IN NUMBER DEFAULT NULL,
13     x_location_cd IN VARCHAR2 DEFAULT NULL,
14     x_attendance_mode IN VARCHAR2 DEFAULT NULL,
15     x_attendance_type IN VARCHAR2 DEFAULT NULL,
16     x_unit_set_cd IN VARCHAR2 DEFAULT NULL,
17     x_us_version_number IN NUMBER DEFAULT NULL,
18     x_preference_number IN NUMBER DEFAULT NULL,
19     x_adm_doc_status IN VARCHAR2 DEFAULT NULL,
20     x_adm_entry_qual_status IN VARCHAR2 DEFAULT NULL,
21     x_late_adm_fee_status IN VARCHAR2 DEFAULT NULL,
22     x_adm_outcome_status IN VARCHAR2 DEFAULT NULL,
23     x_adm_otcm_status_auth_per_id IN NUMBER DEFAULT NULL,
24     x_adm_outcome_status_auth_dt IN DATE DEFAULT NULL,
25     x_adm_outcome_status_reason IN VARCHAR2 DEFAULT NULL,
26     x_offer_dt IN DATE DEFAULT NULL,
27     x_offer_response_dt IN DATE DEFAULT NULL,
28     x_prpsd_commencement_dt IN DATE DEFAULT NULL,
29     x_adm_cndtnl_offer_status IN VARCHAR2 DEFAULT NULL,
30     x_cndtnl_offer_satisfied_dt IN DATE DEFAULT NULL,
31     x_cndtnl_ofr_must_be_stsfd_ind IN VARCHAR2 DEFAULT NULL,
32     x_adm_offer_resp_status IN VARCHAR2 DEFAULT NULL,
33     x_actual_response_dt IN DATE DEFAULT NULL,
34     x_adm_offer_dfrmnt_status IN VARCHAR2 DEFAULT NULL,
35     x_deferred_adm_cal_type IN VARCHAR2 DEFAULT NULL,
36     x_deferred_adm_ci_sequence_num IN NUMBER DEFAULT NULL,
37     x_deferred_tracking_id IN NUMBER DEFAULT NULL,
38     x_ass_rank IN NUMBER DEFAULT NULL,
39     x_secondary_ass_rank IN NUMBER DEFAULT NULL,
40     x_intrntnl_accept_advice_num IN NUMBER DEFAULT NULL,
41     x_ass_tracking_id IN NUMBER DEFAULT NULL,
42     x_fee_cat IN VARCHAR2 DEFAULT NULL,
43     x_hecs_payment_option IN VARCHAR2 DEFAULT NULL,
44     x_expected_completion_yr IN NUMBER DEFAULT NULL,
45     x_expected_completion_perd IN VARCHAR2 DEFAULT NULL,
46     x_correspondence_cat IN VARCHAR2 DEFAULT NULL,
47     x_enrolment_cat IN VARCHAR2 DEFAULT NULL,
48     x_funding_source IN VARCHAR2 DEFAULT NULL,
49     x_cndtnl_offer_cndtn IN VARCHAR2 DEFAULT NULL,
50     x_applicant_acptnce_cndtn IN VARCHAR2 DEFAULT NULL,
51     x_person_id IN NUMBER DEFAULT NULL,
52     x_admission_appl_number IN NUMBER DEFAULT NULL,
53     x_nominated_course_cd IN VARCHAR2 DEFAULT NULL,
54     x_sequence_number IN NUMBER DEFAULT NULL,
55     x_hist_start_dt IN DATE DEFAULT NULL,
56     x_hist_end_dt IN DATE DEFAULT NULL,
57     x_hist_who IN NUMBER DEFAULT NULL,
58     x_hist_offer_round_number IN NUMBER DEFAULT NULL,
59     x_adm_cal_type IN VARCHAR2 DEFAULT NULL,
60     x_adm_ci_sequence_number IN NUMBER DEFAULT NULL,
61     x_creation_date IN DATE DEFAULT NULL,
62     x_created_by IN NUMBER DEFAULT NULL,
63     x_last_update_date IN DATE DEFAULT NULL,
64     x_last_updated_by IN NUMBER DEFAULT NULL,
65     x_last_update_login IN NUMBER DEFAULT NULL ,
66     x_appl_inst_status IN VARCHAR2 DEFAULT NULL,					--arvsrini igsm
67     X_DECISION_DATE                            DATE     DEFAULT NULL,	-- begin APADEGAL adtd001 igs.m
68     X_DECISION_MAKE_ID                         NUMBER   DEFAULT NULL,
69     X_DECISION_REASON_ID                       NUMBER   DEFAULT NULL,
70     X_PENDING_REASON_ID                        NUMBER   DEFAULT NULL,
71     X_WAITLIST_STATUS                          VARCHAR2 DEFAULT NULL,
72     X_WAITLIST_RANK                            VARCHAR2 DEFAULT NULL,
73     X_FUTURE_ACAD_CAL_TYPE                     VARCHAR2 DEFAULT NULL,
74     X_FUTURE_ACAD_CI_SEQUENCE_NUM              NUMBER   DEFAULT NULL,
75     X_FUTURE_ADM_CAL_TYPE                      VARCHAR2 DEFAULT NULL,
76     X_FUTURE_ADM_CI_SEQUENCE_NUM               NUMBER   DEFAULT NULL,
77     X_DEF_ACAD_CAL_TYPE                        VARCHAR2 DEFAULT NULL,
78     X_DEF_ACAD_CI_SEQUENCE_NUM                 NUMBER   DEFAULT NULL,
79     X_RECONSIDER_FLAG                          VARCHAR2 DEFAULT NULL,
80     X_DECLINE_OFR_REASON                       VARCHAR2 DEFAULT NULL    -- end APADEGAL adtd001 igs.m
81  ) AS
82 
83     CURSOR cur_old_ref_values IS
84       SELECT   *
85       FROM     IGS_AD_PS_APLINSTHST_ALL
86       WHERE    rowid = x_rowid;
87 
88   BEGIN
89 
90     l_rowid := x_rowid;
91 
92     -- Code for setting the Old and New Reference Values.
93     -- Populate Old Values.
94     Open cur_old_ref_values;
95     Fetch cur_old_ref_values INTO old_references;
96     IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
97       Close cur_old_ref_values;
98       Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
99           IGS_GE_MSG_STACK.ADD;
100       App_Exception.Raise_Exception;
101       Return;
102     END IF;
103     Close cur_old_ref_values;
104 
105     -- Populate New Values.
106     new_references.org_id := x_org_id;
107     new_references.course_cd := x_course_cd;
108     new_references.crv_version_number := x_crv_version_number;
109     new_references.location_cd := x_location_cd;
110     new_references.attendance_mode := x_attendance_mode;
111     new_references.attendance_type := x_attendance_type;
112     new_references.unit_set_cd := x_unit_set_cd;
113     new_references.us_version_number := x_us_version_number;
114     new_references.preference_number := x_preference_number;
115     new_references.adm_doc_status := x_adm_doc_status;
116     new_references.adm_entry_qual_status := x_adm_entry_qual_status;
117     new_references.late_adm_fee_status := x_late_adm_fee_status;
118     new_references.adm_outcome_status := x_adm_outcome_status;
119     new_references.adm_otcm_status_auth_person_id := x_adm_otcm_status_auth_per_id;
120     new_references.adm_outcome_status_auth_dt := TRUNC(x_adm_outcome_status_auth_dt);
121     new_references.adm_outcome_status_reason := x_adm_outcome_status_reason;
122     new_references.offer_dt := TRUNC(x_offer_dt);
123     new_references.offer_response_dt := TRUNC(x_offer_response_dt);
124     new_references.prpsd_commencement_dt := TRUNC(x_prpsd_commencement_dt);
125     new_references.adm_cndtnl_offer_status := x_adm_cndtnl_offer_status;
126     new_references.cndtnl_offer_satisfied_dt := TRUNC(x_cndtnl_offer_satisfied_dt);
127     new_references.cndtnl_offer_must_be_stsfd_ind := x_cndtnl_ofr_must_be_stsfd_ind;
128     new_references.adm_offer_resp_status := x_adm_offer_resp_status;
129     new_references.actual_response_dt := TRUNC(x_actual_response_dt);
130     new_references.adm_offer_dfrmnt_status := x_adm_offer_dfrmnt_status;
131     new_references.deferred_adm_cal_type := x_deferred_adm_cal_type;
132     new_references.deferred_adm_ci_sequence_num := x_deferred_adm_ci_sequence_num;
133     new_references.deferred_tracking_id := x_deferred_tracking_id;
134     new_references.ass_rank := x_ass_rank;
135     new_references.secondary_ass_rank := x_secondary_ass_rank;
136     new_references.intrntnl_acceptance_advice_num := x_intrntnl_accept_advice_num;
137     new_references.ass_tracking_id := x_ass_tracking_id;
138     new_references.fee_cat := x_fee_cat;
139     new_references.hecs_payment_option := x_hecs_payment_option;
140     new_references.expected_completion_yr := x_expected_completion_yr;
141     new_references.expected_completion_perd := x_expected_completion_perd;
142     new_references.correspondence_cat := x_correspondence_cat;
143     new_references.enrolment_cat := x_enrolment_cat;
144     new_references.funding_source := x_funding_source;
145     new_references.cndtnl_offer_cndtn := x_cndtnl_offer_cndtn;
146     new_references.applicant_acptnce_cndtn := x_applicant_acptnce_cndtn;
147     new_references.person_id := x_person_id;
148     new_references.admission_appl_number := x_admission_appl_number;
149     new_references.nominated_course_cd := x_nominated_course_cd;
150     new_references.sequence_number := x_sequence_number;
151     new_references.hist_start_dt := x_hist_start_dt;
152     new_references.hist_end_dt := x_hist_end_dt;
153     new_references.hist_who := x_hist_who;
154     new_references.hist_offer_round_number := x_hist_offer_round_number;
155     new_references.adm_cal_type := x_adm_cal_type;
156     new_references.adm_ci_sequence_number := x_adm_ci_sequence_number;
157     IF (p_action = 'UPDATE') THEN
158       new_references.creation_date := old_references.creation_date;
159       new_references.created_by := old_references.created_by;
160     ELSE
161       new_references.creation_date := x_creation_date;
162       new_references.created_by := x_created_by;
163     END IF;
164     new_references.last_update_date  := x_last_update_date;
165     new_references.last_updated_by   := x_last_updated_by;
166     new_references.last_update_login := x_last_update_login;
167     new_references.appl_inst_status  := x_appl_inst_status;				--arvsrini igsm
168 
169  -- begin apadegal td001 igsm
170     new_references.decision_date                :=   X_DECISION_DATE;
171     new_references.decision_make_id             :=   X_DECISION_MAKE_ID;
172     new_references.decision_reason_id           :=   X_DECISION_REASON_ID;
173     new_references.pending_reason_id            :=   X_PENDING_REASON_ID;
174     new_references.waitlist_status              :=   X_WAITLIST_STATUS;
175     new_references.waitlist_rank                :=   X_WAITLIST_RANK;
176     new_references.future_acad_cal_type         :=   X_FUTURE_ACAD_CAL_TYPE;
177     new_references.future_acad_ci_sequence_num  :=   X_FUTURE_ACAD_CI_SEQUENCE_NUM;
178     new_references.future_adm_cal_type          :=   X_FUTURE_ADM_CAL_TYPE;
179     new_references.future_adm_ci_sequence_num   :=   X_FUTURE_ADM_CI_SEQUENCE_NUM;
180     new_references.def_acad_cal_type            :=   X_DEF_ACAD_CAL_TYPE;
181     new_references.def_acad_ci_sequence_num    	:=   X_DEF_ACAD_CI_SEQUENCE_NUM;
182     new_references.reconsider_flag              :=   X_RECONSIDER_FLAG;
183     new_references.decline_ofr_reason           :=   X_DECLINE_OFR_REASON;
184 -- end apadegal td001 igsm
185 
186 
187   END Set_Column_Values;
188 
189 PROCEDURE Check_Constraints (
190          Column_Name    IN      VARCHAR2        DEFAULT NULL,
191          Column_Value   IN      VARCHAR2        DEFAULT NULL
192 ) AS
193 /*----------------------------------------------------------------------------
194   ||  Created By :
195   ||  Created On :
196   ||  Purpose :
197   ||  Known limitations, enhancements or remarks :
198   ||  Change History :
199   ||  Who             When            What
200   ||  (reverse chronological order - newest change first)
201   ||  vvutukur        19-May-2002   removed upper check constraint on fee_cat column.bug#2344826.
202   ----------------------------------------------------------------------------*/
203 BEGIN
204  IF  column_name is null then
205      NULL;
206  ELSIF upper(Column_name) = 'CNDTNL_OFFER_MUST_BE_STSFD_IND' then
207      new_references.cndtnl_offer_must_be_stsfd_ind := column_value;
208  ELSIF upper(Column_name) = 'ASS_RANK' then
209      new_references.ass_rank := igs_ge_number.to_num(column_value);
210  ELSIF upper(Column_name) = 'INTRNTNL_ACCEPTANCE_ADVICE_NUM' then
211      new_references.intrntnl_acceptance_advice_num := igs_ge_number.to_num(column_value);
212  ELSIF upper(Column_name) = 'SECONDARY_ASS_RANK' then
213      new_references.secondary_ass_rank := igs_ge_number.to_num(column_value);
214  ELSIF upper(Column_name) = 'PREFERENCE_NUMBER' then
215      new_references.preference_number := igs_ge_number.to_num(column_value);
216  ELSIF upper(Column_name) = 'SEQUENCE_NUMBER' then
217      new_references.sequence_number := igs_ge_number.to_num(column_value);
218  ELSIF upper(Column_name) = 'DEFERRED_ADM_CI_SEQUENCE_NUM' then
219      new_references.deferred_adm_ci_sequence_num := igs_ge_number.to_num(column_value);
220  ELSIF upper(Column_name) = 'ADM_OUTCOME_STATUS_REASON' then
221      new_references.adm_outcome_status_reason := column_value;
222  ELSIF upper(Column_name) = 'CORRESPONDENCE_CAT' then
223      new_references.correspondence_cat := column_value;
224  ELSIF upper(Column_name) = 'EXPECTED_COMPLETION_PERD' then
225      new_references.expected_completion_perd := column_value;
226  ELSIF upper(column_name) = 'APPL_INST_STATUS' THEN					--arvsrini igsm
227       new_references.appl_inst_status := column_value;
228 END IF;
229 
230 IF upper(column_name) = 'CNDTNL_OFFER_MUST_BE_STSFD_IND' OR
231      column_name is null Then
232      IF new_references.cndtnl_offer_must_be_stsfd_ind NOT IN ('Y','N') Then
233          FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
234          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_CND_OFR_STSFD_IND'));
235          IGS_GE_MSG_STACK.ADD;
236          App_Exception.Raise_Exception;
237       END IF;
238 END IF;
239 
240 IF upper(column_name) = 'ASS_RANK' OR
241      column_name is null Then
242      IF new_references.ass_rank  < 1 OR
243         new_references.ass_rank > 999 Then
244          FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
245          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ASSESSMENT_RANK'));
246          IGS_GE_MSG_STACK.ADD;
247          App_Exception.Raise_Exception;
248         END IF;
249 END IF;
250 
251 IF upper(column_name) = 'INTRNTNL_ACCEPTANCE_ADVICE_NUM' OR
252      column_name is null Then
253      IF new_references.intrntnl_acceptance_advice_num  < 1 OR
254         new_references.intrntnl_acceptance_advice_num > 999999999999999 Then
255          FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
256          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_INTRL_ACCPT_ADV_NUM'));
257          IGS_GE_MSG_STACK.ADD;
258          App_Exception.Raise_Exception;
259         END IF;
260 END IF;
261 
262 IF upper(column_name) = 'SECONDARY_ASS_RANK' OR
263      column_name is null Then
264      IF new_references.secondary_ass_rank  < 1 OR
265         new_references.secondary_ass_rank > 999 Then
266          FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
267          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_SEC_ASS_RANK'));
268          IGS_GE_MSG_STACK.ADD;
269          App_Exception.Raise_Exception;
270         END IF;
271 END IF;
272 
273 IF upper(column_name) = 'PREFERENCE_NUMBER' OR
274      column_name is null Then
275      IF new_references.preference_number  < 1 OR
276         new_references.preference_number > 99 Then
277          FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
278          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PREFERANCE_NUM'));
279          IGS_GE_MSG_STACK.ADD;
280         App_Exception.Raise_Exception;
281         END IF;
282 END IF;
283 
284 
285 IF upper(column_name) = 'SEQUENCE_NUMBER' OR
286      column_name is null Then
287      IF new_references.sequence_number  < 1 OR
288         new_references.sequence_number > 999999 Then
289          FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
290          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_SEQUENCE_NUM'));
291          IGS_GE_MSG_STACK.ADD;
292          App_Exception.Raise_Exception;
293      END IF;
294 END IF;
295 
296 
297 IF upper(column_name) = 'CORRESPONDENCE_CAT' OR
298         column_name is null Then
299         IF new_references.correspondence_cat <> UPPER(new_references.correspondence_cat) Then
300          FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
301          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_CRSPOND_CAT'));
302          IGS_GE_MSG_STACK.ADD;
303          App_Exception.Raise_Exception;
304         END IF;
305 END IF;
306 
307 IF upper(column_name) = 'EXPECTED_COMPLETION_PERD' OR
308         column_name is null Then
309         IF new_references.expected_completion_perd <> UPPER(new_references.expected_completion_perd) Then
310          FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
311          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_EXPCT_COMP_PRD'));
312          IGS_GE_MSG_STACK.ADD;
313          App_Exception.Raise_Exception;
314         END IF;
315 END IF;
316 
317 IF upper(column_name) = 'APPL_INST_STATUS' OR								--arvsrini igsm
318         column_name IS NULL THEN
319         IF new_references.appl_inst_status <> UPPER(new_references.appl_inst_status) THEN
320          FND_MESSAGE.SET_NAME('IGS','IGS_GE_INVALID_VALUE_WITH_CTXT');
321          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_APPL_INST_STAT'));
322          IGS_GE_MSG_STACK.ADD;
323          App_Exception.Raise_Exception;
324         END IF;
325 END IF;
326 
327 
328 
329 END Check_Constraints;
330 
331 PROCEDURE Check_Parent_Existance IS
332   /*************************************************************
333   Created By :
334   Date Created By :
335   Purpose :
336   Know limitations, enhancements or remarks
337   Change History
338   Who             When            What
339   smadathi       13-Feb-2002      Bug 2217104. Added foreign key references
340                                   to IGS_CA_INST_ALL table
341   (reverse chronological order - newest change first)
342   ***************************************************************/
343   BEGIN
344     IF (((old_references.attendance_mode = new_references.attendance_mode)) OR
345         ((new_references.attendance_mode IS NULL))) THEN
346       NULL;
347     ELSE
348       IF NOT IGS_EN_ATD_MODE_PKG.Get_PK_For_Validation (
349         new_references.attendance_mode
350         )THEN
351          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
352          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ATTENDANCE_MODE'));
353          IGS_GE_MSG_STACK.ADD;
354          APP_EXCEPTION.RAISE_EXCEPTION;
355       END IF;
356     END IF;
357 
358     IF (((old_references.adm_offer_dfrmnt_status = new_references.adm_offer_dfrmnt_status)) OR
359         ((new_references.adm_offer_dfrmnt_status IS NULL))) THEN
360       NULL;
361     ELSE
362       IF NOT IGS_AD_OFRDFRMT_STAT_PKG.Get_PK_For_Validation (
363         new_references.adm_offer_dfrmnt_status
364         )THEN
365          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
366          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_OFFER_DFRMNT_STATUS'));
367          IGS_GE_MSG_STACK.ADD;
368          APP_EXCEPTION.RAISE_EXCEPTION;
369       END IF;
370     END IF;
371 
372     IF (((old_references.adm_offer_resp_status = new_references.adm_offer_resp_status)) OR
373         ((new_references.adm_offer_resp_status IS NULL))) THEN
374       NULL;
375     ELSE
376       IF NOT IGS_AD_OFR_RESP_STAT_PKG.Get_PK_For_Validation (
377         new_references.adm_offer_resp_status
378         )THEN
379          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
380          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_OFFER_RESP_STATUS'));
381          IGS_GE_MSG_STACK.ADD;
382          APP_EXCEPTION.RAISE_EXCEPTION;
383       END IF;
384     END IF;
385 
386     IF (((old_references.adm_outcome_status = new_references.adm_outcome_status)) OR
387         ((new_references.adm_outcome_status IS NULL))) THEN
388       NULL;
389     ELSE
390       IF NOT IGS_AD_OU_STAT_PKG.Get_PK_For_Validation (
391         new_references.adm_outcome_status
392         )THEN
393          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
394          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_OUTCOME_STATUS'));
395          IGS_GE_MSG_STACK.ADD;
396          APP_EXCEPTION.RAISE_EXCEPTION;
397       END IF;
398     END IF;
399 
400     IF (((old_references.attendance_type = new_references.attendance_type)) OR
401         ((new_references.attendance_type IS NULL))) THEN
402       NULL;
403     ELSE
404       IF NOT IGS_EN_ATD_TYPE_PKG.Get_PK_For_Validation (
405         new_references.attendance_type
406         )THEN
407          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
408          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ATTENDANCE_TYPE'));
409          IGS_GE_MSG_STACK.ADD;
410          APP_EXCEPTION.RAISE_EXCEPTION;
411       END IF;
412     END IF;
413 
414     IF (((old_references.deferred_adm_cal_type = new_references.deferred_adm_cal_type) AND
415          (old_references.deferred_adm_ci_sequence_num = new_references.deferred_adm_ci_sequence_num)) OR
416         ((new_references.deferred_adm_cal_type IS NULL) OR
417          (new_references.deferred_adm_ci_sequence_num IS NULL))) THEN
418       NULL;
419     ELSE
420       IF NOT IGS_CA_INST_PKG.Get_PK_For_Validation (
421         new_references.deferred_adm_cal_type,
422         new_references.deferred_adm_ci_sequence_num
423         )THEN
424          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
425          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_DEFERRED_ADM_CAL_TYPE'));
426          IGS_GE_MSG_STACK.ADD;
427          APP_EXCEPTION.RAISE_EXCEPTION;
428       END IF;
429     END IF;
430 
431     IF (((old_references.adm_cal_type = new_references.adm_cal_type) AND
432          (old_references.adm_ci_sequence_number = new_references.adm_ci_sequence_number)) OR
433         ((new_references.adm_cal_type IS NULL) OR
434          (new_references.adm_ci_sequence_number IS NULL))) THEN
435       NULL;
436     ELSE
437       IF NOT IGS_CA_INST_PKG.Get_PK_For_Validation (
438         new_references.adm_cal_type,
439         new_references.adm_ci_sequence_number
440         )THEN
441          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
442          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_CAL'));
443          IGS_GE_MSG_STACK.ADD;
444          APP_EXCEPTION.RAISE_EXCEPTION;
445       END IF;
446     END IF;
447 
448     IF (((old_references.course_cd = new_references.course_cd) AND
449          (old_references.crv_version_number = new_references.crv_version_number)) OR
450         ((new_references.course_cd IS NULL) OR
451          (new_references.crv_version_number IS NULL))) THEN
452       NULL;
453     ELSE
454       IF NOT IGS_PS_VER_PKG.Get_PK_For_Validation (
455         new_references.course_cd,
456         new_references.crv_version_number
457         )THEN
458          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
459          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PROGRAM'));
460          IGS_GE_MSG_STACK.ADD;
461          APP_EXCEPTION.RAISE_EXCEPTION;
462       END IF;
463     END IF;
464 
465     IF (((old_references.enrolment_cat = new_references.enrolment_cat)) OR
466         ((new_references.enrolment_cat IS NULL))) THEN
467       NULL;
468     ELSE
469       IF NOT IGS_EN_ENROLMENT_CAT_PKG.Get_PK_For_Validation (
470         new_references.enrolment_cat
471         )THEN
472          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
473          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ENROLMENT_CAT'));
474          IGS_GE_MSG_STACK.ADD;
475         APP_EXCEPTION.RAISE_EXCEPTION;
476       END IF;
477     END IF;
478 
479     IF (((old_references.fee_cat = new_references.fee_cat)) OR
480         ((new_references.fee_cat IS NULL))) THEN
481       NULL;
482     ELSE
483       IF NOT IGS_FI_FEE_CAT_PKG.Get_PK_For_Validation (
484         new_references.fee_cat
485         )THEN
486          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
487          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_FEE_CAT'));
488          IGS_GE_MSG_STACK.ADD;
489          APP_EXCEPTION.RAISE_EXCEPTION;
490       END IF;
491     END IF;
492 
493     IF (((old_references.funding_source = new_references.funding_source)) OR
494         ((new_references.funding_source IS NULL))) THEN
495       NULL;
496     ELSE
497       IF NOT IGS_FI_FUND_SRC_PKG.Get_PK_For_Validation (
498         new_references.funding_source
499         ) THEN
500          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
501          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_FUNDING_SOURCE'));
502          IGS_GE_MSG_STACK.ADD;
503          APP_EXCEPTION.RAISE_EXCEPTION;
504       END IF;
505     END IF;
506 
507     IF (((old_references.hecs_payment_option = new_references.hecs_payment_option)) OR
508         ((new_references.hecs_payment_option IS NULL))) THEN
509       NULL;
510     ELSE
511       IF NOT IGS_FI_HECS_PAY_OPTN_PKG.Get_PK_For_Validation (
512         new_references.hecs_payment_option
513         )THEN
514          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
515          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_HECS_PAY_OPT'));
516          IGS_GE_MSG_STACK.ADD;
517          APP_EXCEPTION.RAISE_EXCEPTION;
518       END IF;
519     END IF;
520 
521     IF (((old_references.location_cd = new_references.location_cd)) OR
522         ((new_references.location_cd IS NULL))) THEN
523       NULL;
524     ELSE
525       IF NOT IGS_AD_LOCATION_PKG.Get_PK_For_Validation (
526         new_references.location_cd
527         )THEN
528          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
529          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_EN_LOCATION'));
530          IGS_GE_MSG_STACK.ADD;
531          APP_EXCEPTION.RAISE_EXCEPTION;
532       END IF;
533     END IF;
534 
535     IF (((old_references.ass_tracking_id = new_references.ass_tracking_id)) OR
536         ((new_references.ass_tracking_id IS NULL))) THEN
537       NULL;
538     ELSE
539       IF NOT IGS_TR_ITEM_PKG.Get_PK_For_Validation (
540         new_references.ass_tracking_id
541         )THEN
542          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
543          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ASS_TRACKING_ID'));
544          IGS_GE_MSG_STACK.ADD;
545         APP_EXCEPTION.RAISE_EXCEPTION;
546       END IF;
547     END IF;
548 
549     IF (((old_references.deferred_tracking_id = new_references.deferred_tracking_id)) OR
550         ((new_references.deferred_tracking_id IS NULL))) THEN
551       NULL;
552     ELSE
553       IF NOT IGS_TR_ITEM_PKG.Get_PK_For_Validation (
554         new_references.deferred_tracking_id
555         )THEN
556          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
557          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ASS_TRACKING_ID'));
558          IGS_GE_MSG_STACK.ADD;
559         APP_EXCEPTION.RAISE_EXCEPTION;
560       END IF;
561     END IF;
562 
563     IF (((old_references.unit_set_cd = new_references.unit_set_cd) AND
564          (old_references.us_version_number = new_references.us_version_number)) OR
565         ((new_references.unit_set_cd IS NULL) OR
566          (new_references.us_version_number IS NULL))) THEN
567       NULL;
568     ELSE
569       IF NOT IGS_EN_UNIT_SET_PKG.Get_PK_For_Validation (
570         new_references.unit_set_cd,
571         new_references.us_version_number
572         )THEN
573          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
574          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_PS_UNIT_SET'));
575          IGS_GE_MSG_STACK.ADD;
576          APP_EXCEPTION.RAISE_EXCEPTION;
577       END IF;
578     END IF;
579 
580     IF (((old_references.person_id = new_references.person_id) AND
581          (old_references.admission_appl_number = new_references.admission_appl_number) AND
582          (old_references.nominated_course_cd = new_references.nominated_course_cd)) OR
583         ((new_references.person_id IS NULL) OR
584          (new_references.admission_appl_number IS NULL) OR
585          (new_references.nominated_course_cd IS NULL))) THEN
586       NULL;
587     ELSE
588       IF NOT IGS_AD_PS_APPL_PKG.Get_PK_For_Validation (
589         new_references.person_id,
590         new_references.admission_appl_number,
591         new_references.nominated_course_cd
592         )THEN
593          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
594          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PROGRAM_APPL'));
595          IGS_GE_MSG_STACK.ADD;
596          APP_EXCEPTION.RAISE_EXCEPTION;
597       END IF;
598     END IF;
599 
600     IF (((old_references.adm_cndtnl_offer_status = new_references.adm_cndtnl_offer_status)) OR
601         ((new_references.adm_cndtnl_offer_status IS NULL))) THEN
602       NULL;
603     ELSE
604       IF NOT IGS_AD_CNDNL_OFRSTAT_PKG.Get_PK_For_Validation (
605         new_references.adm_cndtnl_offer_status
606         )THEN
607          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
608          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_COND_OFR_STATUS'));
609          IGS_GE_MSG_STACK.ADD;
610          APP_EXCEPTION.RAISE_EXCEPTION;
611       END IF;
612     END IF;
613 
614     IF (((old_references.adm_doc_status = new_references.adm_doc_status)) OR
615         ((new_references.adm_doc_status IS NULL))) THEN
616       NULL;
617     ELSE
618       IF NOT IGS_AD_DOC_STAT_PKG.Get_PK_For_Validation (
619         new_references.adm_doc_status
620         )THEN
621          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
622          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_DOC_STAT'));
623          IGS_GE_MSG_STACK.ADD;
624          APP_EXCEPTION.RAISE_EXCEPTION;
625       END IF;
626     END IF;
627 
628     IF (((old_references.adm_entry_qual_status = new_references.adm_entry_qual_status)) OR
629         ((new_references.adm_entry_qual_status IS NULL))) THEN
630       NULL;
631     ELSE
632       IF NOT IGS_AD_ENT_QF_STAT_PKG.Get_PK_For_Validation (
633         new_references.adm_entry_qual_status
634         )THEN
635          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
636          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_ADM_ENTRY_QUAL_STATUS'));
637          IGS_GE_MSG_STACK.ADD;
638          APP_EXCEPTION.RAISE_EXCEPTION;
639       END IF;
640     END IF;
641 
642     IF (((old_references.late_adm_fee_status = new_references.late_adm_fee_status)) OR
643         ((new_references.late_adm_fee_status IS NULL))) THEN
644       NULL;
645     ELSE
646       IF NOT IGS_AD_FEE_STAT_PKG.Get_PK_For_Validation (
647         new_references.late_adm_fee_status
648         )THEN
649          FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
650          FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_LATE_ADM_FEE_STATUS'));
651          IGS_GE_MSG_STACK.ADD;
652          APP_EXCEPTION.RAISE_EXCEPTION;
653       END IF;
654     END IF;
655 
656 
657     IF (((old_references.appl_inst_status = new_references.appl_inst_status)) OR		--arvsrini igsm
658         ((new_references.appl_inst_status IS NULL))) THEN
659       NULL;
660     ELSE
661       IF NOT IGS_AD_APPL_STAT_PKG.Get_PK_For_Validation (
662         new_references.appl_inst_status ,'N'
663         )THEN
664         FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
665         FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_APPL_INST_STAT'));
666         IGS_GE_MSG_STACK.ADD;
667         APP_EXCEPTION.RAISE_EXCEPTION;
668       END IF;
669     END IF;
670 
671 ---************************************************* begin apadegal adtd001 IGS.M **********************************
672 
673      IF (((old_references.decision_make_id = new_references.decision_make_id)) OR
674         ((new_references.decision_make_id IS NULL))) THEN
675       NULL;
676     ELSIF NOT Igs_Pe_Person_Pkg.Get_PK_For_Validation (
677                         new_references.decision_make_id
678         )  THEN
679         FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
680         FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_DEC_MAKE'));
681       IGS_GE_MSG_STACK.ADD;
682          App_Exception.Raise_Exception;
683     END IF;
684     IF (((old_references.decision_reason_id = new_references.decision_reason_id)) OR
685         ((new_references.decision_reason_id IS NULL))) THEN
686       NULL;
687     ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
688                         new_references.decision_reason_id,
689                        'DECISION_REASON', 'N'
690         )  THEN
691         FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
692         FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_DEC_REASON'));
693       IGS_GE_MSG_STACK.ADD;
694          App_Exception.Raise_Exception;
695     END IF;
696     IF (((old_references.pending_reason_id = new_references.pending_reason_id)) OR
697         ((new_references.pending_reason_id IS NULL))) THEN
698       NULL;
699     ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
700                         new_references.pending_reason_id,
701                         'PENDING_REASON','N'
702         )  THEN
703         FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
704         FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_PEND_REASON'));
705       IGS_GE_MSG_STACK.ADD;
706          App_Exception.Raise_Exception;
707     END IF;
708 
709     IF (((old_references.future_acad_cal_type = new_references.future_acad_cal_type) AND
710          (old_references.future_acad_ci_sequence_num = new_references.future_acad_ci_sequence_num)) OR
711         ((new_references.future_acad_cal_type IS NULL) OR
712          (new_references.future_acad_ci_sequence_num IS NULL))) THEN
713       NULL;
714     ELSE
715       IF NOT IGS_CA_INST_PKG.Get_PK_For_Validation (
716         new_references.future_acad_cal_type,
717         new_references.future_acad_ci_sequence_num
718         )THEN
719         FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
720         FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_FUT_ACAD_CAL'));
721            IGS_GE_MSG_STACK.ADD;
722            APP_EXCEPTION.RAISE_EXCEPTION;
723       END IF;
724     END IF;
725 
726     IF (((old_references.future_adm_cal_type = new_references.future_adm_cal_type) AND
727          (old_references.future_adm_ci_sequence_num = new_references.future_adm_ci_sequence_num)) OR
728         ((new_references.future_adm_cal_type IS NULL) OR
729          (new_references.future_adm_ci_sequence_num IS NULL))) THEN
730       NULL;
731     ELSE
732       IF NOT IGS_CA_INST_PKG.Get_PK_For_Validation (
733         new_references.future_adm_cal_type,
734         new_references.future_adm_ci_sequence_num
735         )THEN
736         FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
737         FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_FUT_ADM_CAL'));
738            IGS_GE_MSG_STACK.ADD;
739            APP_EXCEPTION.RAISE_EXCEPTION;
740       END IF;
741     END IF;
742 ---************************************************* end apadegal adtd001 IGS.M **********************************
743 
744 
745 
746 END Check_Parent_Existance;
747 
748 
749  FUNCTION Get_PK_For_Validation (
750     x_person_id IN NUMBER,
751     x_admission_appl_number IN NUMBER,
752     x_nominated_course_cd IN VARCHAR2,
753     x_sequence_number IN NUMBER,
754     x_hist_start_dt IN DATE
755     )
756 RETURN BOOLEAN
757 AS
758     CURSOR cur_rowid IS
759       SELECT   rowid
760       FROM     IGS_AD_PS_APLINSTHST_ALL
761       WHERE    person_id = x_person_id
762       AND      admission_appl_number = x_admission_appl_number
763       AND      nominated_course_cd = x_nominated_course_cd
764       AND      sequence_number = x_sequence_number
765       AND      hist_start_dt = x_hist_start_dt
766       FOR UPDATE NOWAIT;
767 
768     lv_rowid cur_rowid%RowType;
769 
770   BEGIN
771 
772     Open cur_rowid;
773     Fetch cur_rowid INTO lv_rowid;
774  IF (cur_rowid%FOUND) THEN
775        Close cur_rowid;
776        Return (TRUE);
777  ELSE
778        Close cur_rowid;
779        Return (FALSE);
780  END IF;
781   END Get_PK_For_Validation;
782 
783   PROCEDURE Before_DML (
784     p_action IN VARCHAR2,
785     x_rowid IN VARCHAR2 DEFAULT NULL,
786                 x_org_id IN NUMBER DEFAULT NULL,
787     x_course_cd IN VARCHAR2 DEFAULT NULL,
788     x_crv_version_number IN NUMBER DEFAULT NULL,
789     x_location_cd IN VARCHAR2 DEFAULT NULL,
790     x_attendance_mode IN VARCHAR2 DEFAULT NULL,
791     x_attendance_type IN VARCHAR2 DEFAULT NULL,
792     x_unit_set_cd IN VARCHAR2 DEFAULT NULL,
793     x_us_version_number IN NUMBER DEFAULT NULL,
794     x_preference_number IN NUMBER DEFAULT NULL,
795     x_adm_doc_status IN VARCHAR2 DEFAULT NULL,
796     x_adm_entry_qual_status IN VARCHAR2 DEFAULT NULL,
797     x_late_adm_fee_status IN VARCHAR2 DEFAULT NULL,
798     x_adm_outcome_status IN VARCHAR2 DEFAULT NULL,
799     x_adm_otcm_status_auth_per_id IN NUMBER DEFAULT NULL,
800     x_adm_outcome_status_auth_dt IN DATE DEFAULT NULL,
801     x_adm_outcome_status_reason IN VARCHAR2 DEFAULT NULL,
802     x_offer_dt IN DATE DEFAULT NULL,
803     x_offer_response_dt IN DATE DEFAULT NULL,
804     x_prpsd_commencement_dt IN DATE DEFAULT NULL,
805     x_adm_cndtnl_offer_status IN VARCHAR2 DEFAULT NULL,
806     x_cndtnl_offer_satisfied_dt IN DATE DEFAULT NULL,
807     x_cndtnl_ofr_must_be_stsfd_ind IN VARCHAR2 DEFAULT NULL,
808     x_adm_offer_resp_status IN VARCHAR2 DEFAULT NULL,
809     x_actual_response_dt IN DATE DEFAULT NULL,
810     x_adm_offer_dfrmnt_status IN VARCHAR2 DEFAULT NULL,
811     x_deferred_adm_cal_type IN VARCHAR2 DEFAULT NULL,
812     x_deferred_adm_ci_sequence_num IN NUMBER DEFAULT NULL,
813     x_deferred_tracking_id IN NUMBER DEFAULT NULL,
814     x_ass_rank IN NUMBER DEFAULT NULL,
815     x_secondary_ass_rank IN NUMBER DEFAULT NULL,
816     x_intrntnl_accept_advice_num IN NUMBER DEFAULT NULL,
817     x_ass_tracking_id IN NUMBER DEFAULT NULL,
818     x_fee_cat IN VARCHAR2 DEFAULT NULL,
819     x_hecs_payment_option IN VARCHAR2 DEFAULT NULL,
820     x_expected_completion_yr IN NUMBER DEFAULT NULL,
821     x_expected_completion_perd IN VARCHAR2 DEFAULT NULL,
822     x_correspondence_cat IN VARCHAR2 DEFAULT NULL,
823     x_enrolment_cat IN VARCHAR2 DEFAULT NULL,
824     x_funding_source IN VARCHAR2 DEFAULT NULL,
825     x_cndtnl_offer_cndtn IN VARCHAR2 DEFAULT NULL,
826     x_applicant_acptnce_cndtn IN VARCHAR2 DEFAULT NULL,
827     x_person_id IN NUMBER DEFAULT NULL,
828     x_admission_appl_number IN NUMBER DEFAULT NULL,
829     x_nominated_course_cd IN VARCHAR2 DEFAULT NULL,
830     x_sequence_number IN NUMBER DEFAULT NULL,
831     x_hist_start_dt IN DATE DEFAULT NULL,
832     x_hist_end_dt IN DATE DEFAULT NULL,
833     x_hist_who IN NUMBER DEFAULT NULL,
834     x_hist_offer_round_number IN NUMBER DEFAULT NULL,
835     x_adm_cal_type IN VARCHAR2 DEFAULT NULL,
836     x_adm_ci_sequence_number IN NUMBER DEFAULT NULL,
837     x_creation_date IN DATE DEFAULT NULL,
838     x_created_by IN NUMBER DEFAULT NULL,
839     x_last_update_date IN DATE DEFAULT NULL,
840     x_last_updated_by IN NUMBER DEFAULT NULL,
841     x_last_update_login IN NUMBER DEFAULT NULL,
842     x_appl_inst_status IN VARCHAR2 DEFAULT NULL,							--arvsrini igsm
843     X_DECISION_DATE                            DATE     DEFAULT NULL,	-- begin APADEGAL adtd001 igs.m
844     X_DECISION_MAKE_ID                         NUMBER   DEFAULT NULL,
845     X_DECISION_REASON_ID                       NUMBER   DEFAULT NULL,
846     X_PENDING_REASON_ID                        NUMBER   DEFAULT NULL,
847     X_WAITLIST_STATUS                          VARCHAR2 DEFAULT NULL,
848     X_WAITLIST_RANK                            VARCHAR2 DEFAULT NULL,
849     X_FUTURE_ACAD_CAL_TYPE                     VARCHAR2 DEFAULT NULL,
850     X_FUTURE_ACAD_CI_SEQUENCE_NUM              NUMBER   DEFAULT NULL,
851     X_FUTURE_ADM_CAL_TYPE                      VARCHAR2 DEFAULT NULL,
852     X_FUTURE_ADM_CI_SEQUENCE_NUM               NUMBER   DEFAULT NULL,
853     X_DEF_ACAD_CAL_TYPE                        VARCHAR2 DEFAULT NULL,
854     X_DEF_ACAD_CI_SEQUENCE_NUM                 NUMBER   DEFAULT NULL,
855     X_RECONSIDER_FLAG                          VARCHAR2 DEFAULT NULL,
856     X_DECLINE_OFR_REASON                       VARCHAR2 DEFAULT NULL    -- end APADEGAL adtd001 igs.m
857   ) AS
858   BEGIN
859 
860     Set_Column_Values (
861       p_action,
862       x_rowid,
863                         x_org_id,
864       x_course_cd,
865       x_crv_version_number,
866       x_location_cd,
867       x_attendance_mode,
868       x_attendance_type,
869       x_unit_set_cd,
870       x_us_version_number,
871       x_preference_number,
872       x_adm_doc_status,
873       x_adm_entry_qual_status,
874       x_late_adm_fee_status,
875       x_adm_outcome_status,
876       x_adm_otcm_status_auth_per_id,
877       x_adm_outcome_status_auth_dt,
878       x_adm_outcome_status_reason,
879       x_offer_dt,
880       x_offer_response_dt,
881       x_prpsd_commencement_dt,
882       x_adm_cndtnl_offer_status,
883       x_cndtnl_offer_satisfied_dt,
884       x_cndtnl_ofr_must_be_stsfd_ind,
885       x_adm_offer_resp_status,
886       x_actual_response_dt,
887       x_adm_offer_dfrmnt_status,
888       x_deferred_adm_cal_type,
889       x_deferred_adm_ci_sequence_num,
890       x_deferred_tracking_id,
891       x_ass_rank,
892       x_secondary_ass_rank,
893       x_intrntnl_accept_advice_num,
894       x_ass_tracking_id,
895       x_fee_cat,
896       x_hecs_payment_option,
897       x_expected_completion_yr,
898       x_expected_completion_perd,
899       x_correspondence_cat,
900       x_enrolment_cat,
901       x_funding_source,
902       x_cndtnl_offer_cndtn,
903       x_applicant_acptnce_cndtn,
904       x_person_id,
905       x_admission_appl_number,
906       x_nominated_course_cd,
907       x_sequence_number,
908       x_hist_start_dt,
909       x_hist_end_dt,
910       x_hist_who,
911       x_hist_offer_round_number,
912       x_adm_cal_type,
913       x_adm_ci_sequence_number,
914       x_creation_date,
915       x_created_by,
916       x_last_update_date,
917       x_last_updated_by,
918       x_last_update_login,
919       x_appl_inst_status,								--arvsrini igsm
920       x_decision_date               ,	-- begin APADEGAL adtd001 igs.m
921       x_decision_make_id            ,
922       x_decision_reason_id          ,
923       x_pending_reason_id           ,
924       x_waitlist_status             ,
925       x_waitlist_rank               ,
926       x_future_acad_cal_type        ,
927       x_future_acad_ci_sequence_num ,
928       x_future_adm_cal_type         ,
929       x_future_adm_ci_sequence_num  ,
930       x_def_acad_cal_type           ,
931       x_def_acad_ci_sequence_num    ,
932       x_reconsider_flag             ,
933       x_decline_ofr_reason              -- end APADEGAL adtd001 igs.m
934     );
935 
936     IF (p_action = 'INSERT') THEN
937       IF  Get_PK_For_Validation (
938                 new_references.person_id,
939         new_references.admission_appl_number,
940               new_references.nominated_course_cd,
941         new_references.sequence_number,
942               new_references.hist_start_dt
943         ) THEN
944          Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
945                  IGS_GE_MSG_STACK.ADD;
946           App_Exception.Raise_Exception;
947       END IF;
948       Check_Constraints;
949       Check_Parent_Existance;
950     ELSIF (p_action = 'UPDATE') THEN
951       Check_Constraints;
952       Check_Parent_Existance;
953     ELSIF (p_action = 'VALIDATE_INSERT') THEN
954       IF  Get_PK_For_Validation (
955        new_references.person_id,
956        new_references.admission_appl_number,
957        new_references.nominated_course_cd,
958        new_references.sequence_number,
959        new_references.hist_start_dt
960         ) THEN
961          Fnd_Message.Set_Name ('IGS', 'IGS_GE_MULTI_ORG_DUP_REC');
962                  IGS_GE_MSG_STACK.ADD;
963           App_Exception.Raise_Exception;
964       END IF;
965       Check_Constraints;
966     ELSIF (p_action = 'VALIDATE_UPDATE') THEN
967        Check_Constraints;
968     END IF;
969   END Before_DML;
970 
971   PROCEDURE After_DML (
972     p_action IN VARCHAR2,
973     x_rowid IN VARCHAR2
974   ) AS
975   BEGIN
976 
977     l_rowid := x_rowid;
978   END After_DML;
979 
980 procedure INSERT_ROW (
981   X_ROWID in out NOCOPY VARCHAR2,
982         X_ORG_ID in NUMBER,
983   X_PERSON_ID in NUMBER,
984   X_ADMISSION_APPL_NUMBER in NUMBER,
985   X_NOMINATED_COURSE_CD in VARCHAR2,
986   X_SEQUENCE_NUMBER in NUMBER,
987   X_HIST_START_DT in DATE,
988   X_APPLICANT_ACPTNCE_CNDTN in VARCHAR2,
989   X_CNDTNL_OFFER_CNDTN in VARCHAR2,
990   X_HIST_END_DT in DATE,
991   X_HIST_WHO in NUMBER,
992   X_HIST_OFFER_ROUND_NUMBER in NUMBER,
993   X_ADM_CAL_TYPE in VARCHAR2,
994   X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
995   X_COURSE_CD in VARCHAR2,
996   X_CRV_VERSION_NUMBER in NUMBER,
997   X_LOCATION_CD in VARCHAR2,
998   X_ATTENDANCE_MODE in VARCHAR2,
999   X_ATTENDANCE_TYPE in VARCHAR2,
1000   X_UNIT_SET_CD in VARCHAR2,
1001   X_US_VERSION_NUMBER in NUMBER,
1002   X_PREFERENCE_NUMBER in NUMBER,
1003   X_ADM_DOC_STATUS in VARCHAR2,
1004   X_ADM_ENTRY_QUAL_STATUS in VARCHAR2,
1005   X_LATE_ADM_FEE_STATUS in VARCHAR2,
1006   X_ADM_OUTCOME_STATUS in VARCHAR2,
1007   X_ADM_OTCM_STATUS_AUTH_PER_ID in NUMBER,
1008   X_ADM_OUTCOME_STATUS_AUTH_DT in DATE,
1009   X_ADM_OUTCOME_STATUS_REASON in VARCHAR2,
1010   X_OFFER_DT in DATE,
1011   X_OFFER_RESPONSE_DT in DATE,
1012   X_PRPSD_COMMENCEMENT_DT in DATE,
1013   X_ADM_CNDTNL_OFFER_STATUS in VARCHAR2,
1014   X_CNDTNL_OFFER_SATISFIED_DT in DATE,
1015   X_CNDTNL_OFR_MUST_BE_STSFD_IND in VARCHAR2,
1016   X_ADM_OFFER_RESP_STATUS in VARCHAR2,
1017   X_ACTUAL_RESPONSE_DT in DATE,
1018   X_ADM_OFFER_DFRMNT_STATUS in VARCHAR2,
1019   X_DEFERRED_ADM_CAL_TYPE in VARCHAR2,
1020   X_DEFERRED_ADM_CI_SEQUENCE_NUM in NUMBER,
1021   X_DEFERRED_TRACKING_ID in NUMBER,
1022   X_ASS_RANK in NUMBER,
1023   X_SECONDARY_ASS_RANK in NUMBER,
1024   X_INTRNTNL_ACCEPT_ADVICE_NUM in NUMBER,
1025   X_ASS_TRACKING_ID in NUMBER,
1026   X_FEE_CAT in VARCHAR2,
1027   X_HECS_PAYMENT_OPTION in VARCHAR2,
1028   X_EXPECTED_COMPLETION_YR in NUMBER,
1029   X_EXPECTED_COMPLETION_PERD in VARCHAR2,
1030   X_CORRESPONDENCE_CAT in VARCHAR2,
1031   X_ENROLMENT_CAT in VARCHAR2,
1032   X_FUNDING_SOURCE in VARCHAR2,
1033   X_MODE in VARCHAR2,
1034   X_APPL_INST_STATUS IN VARCHAR2,							--arvsrini igsm
1035   X_DECISION_DATE                            DATE     DEFAULT NULL,	-- begin APADEGAL adtd001 igs.m
1036   X_DECISION_MAKE_ID                         NUMBER   DEFAULT NULL,
1037   X_DECISION_REASON_ID                       NUMBER   DEFAULT NULL,
1038   X_PENDING_REASON_ID                        NUMBER   DEFAULT NULL,
1039   X_WAITLIST_STATUS                          VARCHAR2 DEFAULT NULL,
1040   X_WAITLIST_RANK                            VARCHAR2 DEFAULT NULL,
1041   X_FUTURE_ACAD_CAL_TYPE                     VARCHAR2 DEFAULT NULL,
1042   X_FUTURE_ACAD_CI_SEQUENCE_NUM              NUMBER   DEFAULT NULL,
1043   X_FUTURE_ADM_CAL_TYPE                      VARCHAR2 DEFAULT NULL,
1044   X_FUTURE_ADM_CI_SEQUENCE_NUM               NUMBER   DEFAULT NULL,
1045   X_DEF_ACAD_CAL_TYPE                        VARCHAR2 DEFAULT NULL,
1046   X_DEF_ACAD_CI_SEQUENCE_NUM                 NUMBER   DEFAULT NULL,
1047   X_RECONSIDER_FLAG                          VARCHAR2 DEFAULT NULL,
1048   X_DECLINE_OFR_REASON                       VARCHAR2 DEFAULT NULL    -- end APADEGAL adtd001 igs.m
1049   ) as
1050     cursor C is select ROWID from IGS_AD_PS_APLINSTHST_ALL
1051       where PERSON_ID = X_PERSON_ID
1052       and ADMISSION_APPL_NUMBER = X_ADMISSION_APPL_NUMBER
1053       and NOMINATED_COURSE_CD = X_NOMINATED_COURSE_CD
1054       and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
1055       and HIST_START_DT = X_HIST_START_DT;
1056     X_LAST_UPDATE_DATE DATE;
1057     X_LAST_UPDATED_BY NUMBER;
1058     X_LAST_UPDATE_LOGIN NUMBER;
1059 begin
1060   X_LAST_UPDATE_DATE := SYSDATE;
1061   if(X_MODE = 'I') then
1062     X_LAST_UPDATED_BY := 1;
1063     X_LAST_UPDATE_LOGIN := 0;
1064   elsif (X_MODE = 'R') then
1065     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1066     if X_LAST_UPDATED_BY is NULL then
1067       X_LAST_UPDATED_BY := -1;
1068     end if;
1069     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1070     if X_LAST_UPDATE_LOGIN is NULL then
1071       X_LAST_UPDATE_LOGIN := -1;
1072     end if;
1073   else
1074     FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1075         IGS_GE_MSG_STACK.ADD;
1076     app_exception.raise_exception;
1077   end if;
1078 
1079 
1080   Before_DML(
1081   p_action =>'INSERT',
1082   x_rowid =>X_ROWID,
1083         x_org_id => igs_ge_gen_003.get_org_id,
1084   x_course_cd => X_COURSE_CD,
1085   x_crv_version_number => X_CRV_VERSION_NUMBER,
1086   x_location_cd =>  X_LOCATION_CD,
1087   x_attendance_mode=>X_ATTENDANCE_MODE,
1088   x_attendance_type=>X_ATTENDANCE_TYPE,
1089   x_unit_set_cd=>X_UNIT_SET_CD,
1090   x_us_version_number =>X_US_VERSION_NUMBER,
1091   x_preference_number =>X_PREFERENCE_NUMBER,
1092   x_adm_doc_status =>X_ADM_DOC_STATUS,
1093   x_adm_entry_qual_status =>X_ADM_ENTRY_QUAL_STATUS,
1094   x_late_adm_fee_status =>X_LATE_ADM_FEE_STATUS,
1095   x_adm_outcome_status =>X_ADM_OUTCOME_STATUS,
1096   x_adm_otcm_status_auth_per_id =>X_ADM_OTCM_STATUS_AUTH_PER_ID,
1097   x_adm_outcome_status_auth_dt =>X_ADM_OUTCOME_STATUS_AUTH_DT,
1098   x_adm_outcome_status_reason =>X_ADM_OUTCOME_STATUS_REASON,
1099   x_offer_dt =>X_OFFER_DT,
1100   x_offer_response_dt =>X_OFFER_RESPONSE_DT,
1101   x_prpsd_commencement_dt =>X_PRPSD_COMMENCEMENT_DT,
1102   x_adm_cndtnl_offer_status =>X_ADM_CNDTNL_OFFER_STATUS,
1103   x_cndtnl_offer_satisfied_dt =>X_CNDTNL_OFFER_SATISFIED_DT,
1104   x_cndtnl_ofr_must_be_stsfd_ind => NVL(X_CNDTNL_OFR_MUST_BE_STSFD_IND,'N'),
1105   x_adm_offer_resp_status => X_ADM_OFFER_RESP_STATUS,
1106   x_actual_response_dt => X_ACTUAL_RESPONSE_DT,
1107   x_adm_offer_dfrmnt_status =>X_ADM_OFFER_DFRMNT_STATUS,
1108   x_deferred_adm_cal_type =>X_DEFERRED_ADM_CAL_TYPE,
1109   x_deferred_adm_ci_sequence_num =>X_DEFERRED_ADM_CI_SEQUENCE_NUM,
1110   x_deferred_tracking_id =>X_DEFERRED_TRACKING_ID,
1111   x_ass_rank =>X_ASS_RANK,
1112   x_secondary_ass_rank =>X_SECONDARY_ASS_RANK,
1113   x_intrntnl_accept_advice_num =>X_INTRNTNL_ACCEPT_ADVICE_NUM,
1114   x_ass_tracking_id =>X_ASS_TRACKING_ID,
1115   x_fee_cat =>X_FEE_CAT,
1116   x_hecs_payment_option =>X_HECS_PAYMENT_OPTION,
1117   x_expected_completion_yr =>X_EXPECTED_COMPLETION_YR,
1118   x_expected_completion_perd =>X_EXPECTED_COMPLETION_PERD,
1119   x_correspondence_cat =>X_CORRESPONDENCE_CAT,
1120   x_enrolment_cat =>X_ENROLMENT_CAT,
1121   x_funding_source =>X_FUNDING_SOURCE,
1122   x_cndtnl_offer_cndtn =>X_CNDTNL_OFFER_CNDTN,
1123   x_applicant_acptnce_cndtn =>X_APPLICANT_ACPTNCE_CNDTN,
1124   x_person_id =>X_PERSON_ID,
1125   x_admission_appl_number =>X_ADMISSION_APPL_NUMBER,
1126   x_nominated_course_cd =>X_NOMINATED_COURSE_CD,
1127   x_sequence_number =>X_SEQUENCE_NUMBER,
1128   x_hist_start_dt =>X_HIST_START_DT,
1129   x_hist_end_dt =>X_HIST_END_DT,
1130   x_hist_who =>X_HIST_WHO,
1131   x_hist_offer_round_number=>X_HIST_OFFER_ROUND_NUMBER,
1132   x_adm_cal_type =>X_ADM_CAL_TYPE,
1133   x_adm_ci_sequence_number =>X_ADM_CI_SEQUENCE_NUMBER,
1134   x_creation_date => X_LAST_UPDATE_DATE,
1135   x_created_by => X_LAST_UPDATED_BY,
1136   x_last_update_date => X_LAST_UPDATE_DATE,
1137   x_last_updated_by => X_LAST_UPDATED_BY,
1138   x_last_update_login => X_LAST_UPDATE_LOGIN,
1139   x_appl_inst_status => X_APPL_INST_STATUS,						--arvsrini igsm
1140   x_decision_date                 =>	x_decision_date                ,    	        -- begin apadegal td001 igsm
1141   x_decision_make_id              =>	x_decision_make_id             ,
1142   x_decision_reason_id            =>	x_decision_reason_id           ,
1143   x_pending_reason_id             =>	x_pending_reason_id            ,
1144   x_waitlist_status               =>	x_waitlist_status              ,
1145   x_waitlist_rank                 =>	x_waitlist_rank                ,
1146   x_future_acad_cal_type          =>	x_future_acad_cal_type         ,
1147   x_future_acad_ci_sequence_num   =>	x_future_acad_ci_sequence_num  ,
1148   x_future_adm_cal_type           =>	x_future_adm_cal_type          ,
1149   x_future_adm_ci_sequence_num    =>	x_future_adm_ci_sequence_num   ,
1150   x_def_acad_cal_type             =>	x_def_acad_cal_type            ,
1151   x_def_acad_ci_sequence_num      =>	x_def_acad_ci_sequence_num     ,
1152   x_reconsider_flag               =>	x_reconsider_flag              ,
1153   x_decline_ofr_reason            => 	x_decline_ofr_reason              		-- end apadegal td001 igsm
1154 );
1155 
1156   insert into IGS_AD_PS_APLINSTHST_ALL (
1157                 ORG_ID,
1158     APPLICANT_ACPTNCE_CNDTN,
1159     CNDTNL_OFFER_CNDTN,
1160     PERSON_ID,
1161     ADMISSION_APPL_NUMBER,
1162     NOMINATED_COURSE_CD,
1163     SEQUENCE_NUMBER,
1164     HIST_START_DT,
1165     HIST_END_DT,
1166     HIST_WHO,
1167     HIST_OFFER_ROUND_NUMBER,
1168     ADM_CAL_TYPE,
1169     ADM_CI_SEQUENCE_NUMBER,
1170     COURSE_CD,
1171     CRV_VERSION_NUMBER,
1172     LOCATION_CD,
1173     ATTENDANCE_MODE,
1174     ATTENDANCE_TYPE,
1175     UNIT_SET_CD,
1176     US_VERSION_NUMBER,
1177     PREFERENCE_NUMBER,
1178     ADM_DOC_STATUS,
1179     ADM_ENTRY_QUAL_STATUS,
1180     LATE_ADM_FEE_STATUS,
1181     ADM_OUTCOME_STATUS,
1182     ADM_OTCM_STATUS_AUTH_PERSON_ID,
1183     ADM_OUTCOME_STATUS_AUTH_DT,
1184     ADM_OUTCOME_STATUS_REASON,
1185     OFFER_DT,
1186     OFFER_RESPONSE_DT,
1187     PRPSD_COMMENCEMENT_DT,
1188     ADM_CNDTNL_OFFER_STATUS,
1189     CNDTNL_OFFER_SATISFIED_DT,
1190     CNDTNL_OFFER_MUST_BE_STSFD_IND,
1191     ADM_OFFER_RESP_STATUS,
1192     ACTUAL_RESPONSE_DT,
1193     ADM_OFFER_DFRMNT_STATUS,
1194     DEFERRED_ADM_CAL_TYPE,
1195     DEFERRED_ADM_CI_SEQUENCE_NUM,
1196     DEFERRED_TRACKING_ID,
1197     ASS_RANK,
1198     SECONDARY_ASS_RANK,
1199     INTRNTNL_ACCEPTANCE_ADVICE_NUM,
1200     ASS_TRACKING_ID,
1201     FEE_CAT,
1202     HECS_PAYMENT_OPTION,
1203     EXPECTED_COMPLETION_YR,
1204     EXPECTED_COMPLETION_PERD,
1205     CORRESPONDENCE_CAT,
1206     ENROLMENT_CAT,
1207     FUNDING_SOURCE,
1208     CREATION_DATE,
1209     CREATED_BY,
1210     LAST_UPDATE_DATE,
1211     LAST_UPDATED_BY,
1212     LAST_UPDATE_LOGIN,
1213     APPL_INST_STATUS,								--arvsrini igsm
1214     DECISION_DATE                ,      -- BEGIN APADEGAL TD001 IGSM
1215     DECISION_MAKE_ID             ,
1216     DECISION_REASON_ID           ,
1217     PENDING_REASON_ID            ,
1218     WAITLIST_STATUS              ,
1219     WAITLIST_RANK                ,
1220     FUTURE_ACAD_CAL_TYPE         ,
1221     FUTURE_ACAD_CI_SEQUENCE_NUM  ,
1222     FUTURE_ADM_CAL_TYPE          ,
1223     FUTURE_ADM_CI_SEQUENCE_NUM   ,
1224     DEF_ACAD_CAL_TYPE            ,
1225     DEF_ACAD_CI_SEQUENCE_NUM     ,
1226     RECONSIDER_FLAG              ,
1227     DECLINE_OFR_REASON                -- end APADEGAL TD001 IGSM
1228   ) values (
1229     NEW_REFERENCES.ORG_ID,
1230     NEW_REFERENCES.APPLICANT_ACPTNCE_CNDTN,
1231     NEW_REFERENCES.CNDTNL_OFFER_CNDTN,
1232     NEW_REFERENCES.PERSON_ID,
1233     NEW_REFERENCES.ADMISSION_APPL_NUMBER,
1234     NEW_REFERENCES.NOMINATED_COURSE_CD,
1235     NEW_REFERENCES.SEQUENCE_NUMBER,
1236     NEW_REFERENCES.HIST_START_DT,
1237     NEW_REFERENCES.HIST_END_DT,
1238     NEW_REFERENCES.HIST_WHO,
1239     NEW_REFERENCES.HIST_OFFER_ROUND_NUMBER,
1240     NEW_REFERENCES.ADM_CAL_TYPE,
1241     NEW_REFERENCES.ADM_CI_SEQUENCE_NUMBER,
1242     NEW_REFERENCES.COURSE_CD,
1243     NEW_REFERENCES.CRV_VERSION_NUMBER,
1244     NEW_REFERENCES.LOCATION_CD,
1245     NEW_REFERENCES.ATTENDANCE_MODE,
1246     NEW_REFERENCES.ATTENDANCE_TYPE,
1247     NEW_REFERENCES.UNIT_SET_CD,
1248     NEW_REFERENCES.US_VERSION_NUMBER,
1249     NEW_REFERENCES.PREFERENCE_NUMBER,
1250     NEW_REFERENCES.ADM_DOC_STATUS,
1251     NEW_REFERENCES.ADM_ENTRY_QUAL_STATUS,
1252     NEW_REFERENCES.LATE_ADM_FEE_STATUS,
1253     NEW_REFERENCES.ADM_OUTCOME_STATUS,
1254     NEW_REFERENCES.ADM_OTCM_STATUS_AUTH_PERSON_ID,
1255     NEW_REFERENCES.ADM_OUTCOME_STATUS_AUTH_DT,
1256     NEW_REFERENCES.ADM_OUTCOME_STATUS_REASON,
1257     NEW_REFERENCES.OFFER_DT,
1258     NEW_REFERENCES.OFFER_RESPONSE_DT,
1259     NEW_REFERENCES.PRPSD_COMMENCEMENT_DT,
1260     NEW_REFERENCES.ADM_CNDTNL_OFFER_STATUS,
1261     NEW_REFERENCES.CNDTNL_OFFER_SATISFIED_DT,
1262     NEW_REFERENCES.CNDTNL_OFFER_MUST_BE_STSFD_IND,
1263     NEW_REFERENCES.ADM_OFFER_RESP_STATUS,
1264     NEW_REFERENCES.ACTUAL_RESPONSE_DT,
1265     NEW_REFERENCES.ADM_OFFER_DFRMNT_STATUS,
1266     NEW_REFERENCES.DEFERRED_ADM_CAL_TYPE,
1267     NEW_REFERENCES.DEFERRED_ADM_CI_SEQUENCE_NUM,
1268     NEW_REFERENCES.DEFERRED_TRACKING_ID,
1269     NEW_REFERENCES.ASS_RANK,
1270     NEW_REFERENCES.SECONDARY_ASS_RANK,
1271     NEW_REFERENCES.INTRNTNL_ACCEPTANCE_ADVICE_NUM,
1272     NEW_REFERENCES.ASS_TRACKING_ID,
1273     NEW_REFERENCES.FEE_CAT,
1274     NEW_REFERENCES.HECS_PAYMENT_OPTION,
1275     NEW_REFERENCES.EXPECTED_COMPLETION_YR,
1276     NEW_REFERENCES.EXPECTED_COMPLETION_PERD,
1277     NEW_REFERENCES.CORRESPONDENCE_CAT,
1278     NEW_REFERENCES.ENROLMENT_CAT,
1279     NEW_REFERENCES.FUNDING_SOURCE,
1280     X_LAST_UPDATE_DATE,
1281     X_LAST_UPDATED_BY,
1282     X_LAST_UPDATE_DATE,
1283     X_LAST_UPDATED_BY,
1284     X_LAST_UPDATE_LOGIN,
1285     NEW_REFERENCES.APPL_INST_STATUS,							--arvsrini igsm
1286     NEW_REFERENCES.DECISION_DATE                ,      -- BEGIN APADEGAL TD001 IGSM
1287     NEW_REFERENCES.DECISION_MAKE_ID             ,
1288     NEW_REFERENCES.DECISION_REASON_ID           ,
1289     NEW_REFERENCES.PENDING_REASON_ID            ,
1290     NEW_REFERENCES.WAITLIST_STATUS              ,
1291     NEW_REFERENCES.WAITLIST_RANK                ,
1292     NEW_REFERENCES.FUTURE_ACAD_CAL_TYPE         ,
1293     NEW_REFERENCES.FUTURE_ACAD_CI_SEQUENCE_NUM  ,
1294     NEW_REFERENCES.FUTURE_ADM_CAL_TYPE          ,
1295     NEW_REFERENCES.FUTURE_ADM_CI_SEQUENCE_NUM   ,
1296     NEW_REFERENCES.DEF_ACAD_CAL_TYPE            ,
1297     NEW_REFERENCES.DEF_ACAD_CI_SEQUENCE_NUM     ,
1298     NEW_REFERENCES.RECONSIDER_FLAG              ,
1299     NEW_REFERENCES.DECLINE_OFR_REASON                -- end APADEGAL TD001 IGSM
1300   );
1301 
1302   open c;
1303   fetch c into X_ROWID;
1304   if (c%notfound) then
1305     close c;
1306     raise no_data_found;
1307   end if;
1308   close c;
1309 
1310 After_DML(
1311  p_action =>'INSERT',
1312  x_rowid => X_ROWID
1313 );
1314 EXCEPTION
1315   WHEN OTHERS THEN
1316     IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1317       -- Code to handle Security Policy error raised
1318       -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1319       -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1320       -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1321       --    that the ownerof policy function does not have privilege to access.
1322       FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1323       FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1324       IGS_GE_MSG_STACK.ADD;
1325       app_exception.raise_exception;
1326     ELSE
1327       RAISE;
1328     END IF;
1329 
1330 end INSERT_ROW;
1331 
1332 procedure LOCK_ROW (
1333   X_ROWID in VARCHAR2,
1334   X_PERSON_ID in NUMBER,
1335   X_ADMISSION_APPL_NUMBER in NUMBER,
1336   X_NOMINATED_COURSE_CD in VARCHAR2,
1337   X_SEQUENCE_NUMBER in NUMBER,
1338   X_HIST_START_DT in DATE,
1339   X_APPLICANT_ACPTNCE_CNDTN in VARCHAR2,
1340   X_CNDTNL_OFFER_CNDTN in VARCHAR2,
1341   X_HIST_END_DT in DATE,
1342   X_HIST_WHO in NUMBER,
1343   X_HIST_OFFER_ROUND_NUMBER in NUMBER,
1344   X_ADM_CAL_TYPE in VARCHAR2,
1345   X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
1346   X_COURSE_CD in VARCHAR2,
1347   X_CRV_VERSION_NUMBER in NUMBER,
1348   X_LOCATION_CD in VARCHAR2,
1349   X_ATTENDANCE_MODE in VARCHAR2,
1350   X_ATTENDANCE_TYPE in VARCHAR2,
1351   X_UNIT_SET_CD in VARCHAR2,
1352   X_US_VERSION_NUMBER in NUMBER,
1353   X_PREFERENCE_NUMBER in NUMBER,
1354   X_ADM_DOC_STATUS in VARCHAR2,
1355   X_ADM_ENTRY_QUAL_STATUS in VARCHAR2,
1356   X_LATE_ADM_FEE_STATUS in VARCHAR2,
1357   X_ADM_OUTCOME_STATUS in VARCHAR2,
1358   X_ADM_OTCM_STATUS_AUTH_PER_ID in NUMBER,
1359   X_ADM_OUTCOME_STATUS_AUTH_DT in DATE,
1360   X_ADM_OUTCOME_STATUS_REASON in VARCHAR2,
1361   X_OFFER_DT in DATE,
1362   X_OFFER_RESPONSE_DT in DATE,
1363   X_PRPSD_COMMENCEMENT_DT in DATE,
1364   X_ADM_CNDTNL_OFFER_STATUS in VARCHAR2,
1365   X_CNDTNL_OFFER_SATISFIED_DT in DATE,
1366   X_CNDTNL_OFR_MUST_BE_STSFD_IND in VARCHAR2,
1367   X_ADM_OFFER_RESP_STATUS in VARCHAR2,
1368   X_ACTUAL_RESPONSE_DT in DATE,
1369   X_ADM_OFFER_DFRMNT_STATUS in VARCHAR2,
1370   X_DEFERRED_ADM_CAL_TYPE in VARCHAR2,
1371   X_DEFERRED_ADM_CI_SEQUENCE_NUM in NUMBER,
1372   X_DEFERRED_TRACKING_ID in NUMBER,
1373   X_ASS_RANK in NUMBER,
1374   X_SECONDARY_ASS_RANK in NUMBER,
1375   X_INTRNTNL_ACCEPT_ADVICE_NUM in NUMBER,
1376   X_ASS_TRACKING_ID in NUMBER,
1377   X_FEE_CAT in VARCHAR2,
1378   X_HECS_PAYMENT_OPTION in VARCHAR2,
1379   X_EXPECTED_COMPLETION_YR in NUMBER,
1380   X_EXPECTED_COMPLETION_PERD in VARCHAR2,
1381   X_CORRESPONDENCE_CAT in VARCHAR2,
1382   X_ENROLMENT_CAT in VARCHAR2,
1383   X_FUNDING_SOURCE in VARCHAR2,
1384   X_APPL_INST_STATUS IN VARCHAR2,								--arvsrini igsm
1385   X_DECISION_DATE                   DATE     DEFAULT NULL,	-- begin APADEGAL adtd001 igs.m
1386   X_DECISION_MAKE_ID                NUMBER   DEFAULT NULL,
1387   X_DECISION_REASON_ID              NUMBER   DEFAULT NULL,
1388   X_PENDING_REASON_ID               NUMBER   DEFAULT NULL,
1389   X_WAITLIST_STATUS                 VARCHAR2 DEFAULT NULL,
1390   X_WAITLIST_RANK                   VARCHAR2 DEFAULT NULL,
1391   X_FUTURE_ACAD_CAL_TYPE            VARCHAR2 DEFAULT NULL,
1392   X_FUTURE_ACAD_CI_SEQUENCE_NUM     NUMBER   DEFAULT NULL,
1393   X_FUTURE_ADM_CAL_TYPE             VARCHAR2 DEFAULT NULL,
1394   X_FUTURE_ADM_CI_SEQUENCE_NUM      NUMBER   DEFAULT NULL,
1395   X_DEF_ACAD_CAL_TYPE               VARCHAR2 DEFAULT NULL,
1396   X_DEF_ACAD_CI_SEQUENCE_NUM        NUMBER   DEFAULT NULL,
1397   X_RECONSIDER_FLAG                 VARCHAR2 DEFAULT NULL,
1398   X_DECLINE_OFR_REASON              VARCHAR2 DEFAULT NULL    -- end APADEGAL adtd001 igs.m
1399 ) as
1400   cursor c1 is select
1401       APPLICANT_ACPTNCE_CNDTN,
1402       CNDTNL_OFFER_CNDTN,
1403       HIST_END_DT,
1404       HIST_WHO,
1405       HIST_OFFER_ROUND_NUMBER,
1406       ADM_CAL_TYPE,
1407       ADM_CI_SEQUENCE_NUMBER,
1408       COURSE_CD,
1409       CRV_VERSION_NUMBER,
1410       LOCATION_CD,
1411       ATTENDANCE_MODE,
1412       ATTENDANCE_TYPE,
1413       UNIT_SET_CD,
1414       US_VERSION_NUMBER,
1415       PREFERENCE_NUMBER,
1416       ADM_DOC_STATUS,
1417       ADM_ENTRY_QUAL_STATUS,
1418       LATE_ADM_FEE_STATUS,
1419       ADM_OUTCOME_STATUS,
1420       ADM_OTCM_STATUS_AUTH_PERSON_ID,
1421       ADM_OUTCOME_STATUS_AUTH_DT,
1422       ADM_OUTCOME_STATUS_REASON,
1423       OFFER_DT,
1424       OFFER_RESPONSE_DT,
1425       PRPSD_COMMENCEMENT_DT,
1426       ADM_CNDTNL_OFFER_STATUS,
1427       CNDTNL_OFFER_SATISFIED_DT,
1428       CNDTNL_OFFER_MUST_BE_STSFD_IND,
1429       ADM_OFFER_RESP_STATUS,
1430       ACTUAL_RESPONSE_DT,
1431       ADM_OFFER_DFRMNT_STATUS,
1432       DEFERRED_ADM_CAL_TYPE,
1433       DEFERRED_ADM_CI_SEQUENCE_NUM,
1434       DEFERRED_TRACKING_ID,
1435       ASS_RANK,
1436       SECONDARY_ASS_RANK,
1437       INTRNTNL_ACCEPTANCE_ADVICE_NUM,
1438       ASS_TRACKING_ID,
1439       FEE_CAT,
1440       HECS_PAYMENT_OPTION,
1441       EXPECTED_COMPLETION_YR,
1442       EXPECTED_COMPLETION_PERD,
1443       CORRESPONDENCE_CAT,
1444       ENROLMENT_CAT,
1445       FUNDING_SOURCE,
1446       APPL_INST_STATUS,										--arvsrini igsm
1447       DECISION_DATE                 ,	-- begin APADEGAL adtd001 igs.m
1448       DECISION_MAKE_ID              ,
1449       DECISION_REASON_ID            ,
1450       PENDING_REASON_ID             ,
1451       WAITLIST_STATUS               ,
1452       WAITLIST_RANK                 ,
1453       FUTURE_ACAD_CAL_TYPE          ,
1454       FUTURE_ACAD_CI_SEQUENCE_NUM   ,
1455       FUTURE_ADM_CAL_TYPE           ,
1456       FUTURE_ADM_CI_SEQUENCE_NUM    ,
1457       DEF_ACAD_CAL_TYPE             ,
1458       DEF_ACAD_CI_SEQUENCE_NUM      ,
1459       RECONSIDER_FLAG               ,
1460       DECLINE_OFR_REASON                -- end APADEGAL adtd001 igs.m
1461     from IGS_AD_PS_APLINSTHST_ALL
1462     where ROWID = X_ROWID for update nowait;
1463   tlinfo c1%rowtype;
1464 
1465 begin
1466   open c1;
1467   fetch c1 into tlinfo;
1468   if (c1%notfound) then
1469     close c1;
1470     fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1471         IGS_GE_MSG_STACK.ADD;
1472     app_exception.raise_exception;
1473     return;
1474   end if;
1475   close c1;
1476 
1477       if ( ((tlinfo.APPLICANT_ACPTNCE_CNDTN = X_APPLICANT_ACPTNCE_CNDTN)
1478            OR ((tlinfo.APPLICANT_ACPTNCE_CNDTN is null)
1479                AND (X_APPLICANT_ACPTNCE_CNDTN is null)))
1480       AND ((tlinfo.CNDTNL_OFFER_CNDTN = X_CNDTNL_OFFER_CNDTN)
1481            OR ((tlinfo.CNDTNL_OFFER_CNDTN is null)
1482                AND (X_CNDTNL_OFFER_CNDTN is null)))
1483       AND (tlinfo.HIST_END_DT = X_HIST_END_DT)
1484       AND (tlinfo.HIST_WHO = X_HIST_WHO)
1485       AND ((tlinfo.HIST_OFFER_ROUND_NUMBER = X_HIST_OFFER_ROUND_NUMBER)
1486            OR ((tlinfo.HIST_OFFER_ROUND_NUMBER is null)
1487                AND (X_HIST_OFFER_ROUND_NUMBER is null)))
1488       AND ((tlinfo.ADM_CAL_TYPE = X_ADM_CAL_TYPE)
1489            OR ((tlinfo.ADM_CAL_TYPE is null)
1490                AND (X_ADM_CAL_TYPE is null)))
1491       AND ((tlinfo.ADM_CI_SEQUENCE_NUMBER = X_ADM_CI_SEQUENCE_NUMBER)
1492            OR ((tlinfo.ADM_CI_SEQUENCE_NUMBER is null)
1493                AND (X_ADM_CI_SEQUENCE_NUMBER is null)))
1494       AND ((tlinfo.COURSE_CD = X_COURSE_CD)
1495            OR ((tlinfo.COURSE_CD is null)
1496                AND (X_COURSE_CD is null)))
1497       AND ((tlinfo.CRV_VERSION_NUMBER = X_CRV_VERSION_NUMBER)
1498            OR ((tlinfo.CRV_VERSION_NUMBER is null)
1499                AND (X_CRV_VERSION_NUMBER is null)))
1500       AND ((tlinfo.LOCATION_CD = X_LOCATION_CD)
1501            OR ((tlinfo.LOCATION_CD is null)
1502                AND (X_LOCATION_CD is null)))
1503       AND ((tlinfo.ATTENDANCE_MODE = X_ATTENDANCE_MODE)
1504            OR ((tlinfo.ATTENDANCE_MODE is null)
1505                AND (X_ATTENDANCE_MODE is null)))
1506       AND ((tlinfo.ATTENDANCE_TYPE = X_ATTENDANCE_TYPE)
1507            OR ((tlinfo.ATTENDANCE_TYPE is null)
1508                AND (X_ATTENDANCE_TYPE is null)))
1509       AND ((tlinfo.UNIT_SET_CD = X_UNIT_SET_CD)
1510            OR ((tlinfo.UNIT_SET_CD is null)
1511                AND (X_UNIT_SET_CD is null)))
1512       AND ((tlinfo.US_VERSION_NUMBER = X_US_VERSION_NUMBER)
1513            OR ((tlinfo.US_VERSION_NUMBER is null)
1514                AND (X_US_VERSION_NUMBER is null)))
1515       AND ((tlinfo.PREFERENCE_NUMBER = X_PREFERENCE_NUMBER)
1516            OR ((tlinfo.PREFERENCE_NUMBER is null)
1517                AND (X_PREFERENCE_NUMBER is null)))
1518       AND ((tlinfo.ADM_DOC_STATUS = X_ADM_DOC_STATUS)
1519            OR ((tlinfo.ADM_DOC_STATUS is null)
1520                AND (X_ADM_DOC_STATUS is null)))
1521       AND ((tlinfo.ADM_ENTRY_QUAL_STATUS = X_ADM_ENTRY_QUAL_STATUS)
1522            OR ((tlinfo.ADM_ENTRY_QUAL_STATUS is null)
1523                AND (X_ADM_ENTRY_QUAL_STATUS is null)))
1524       AND ((tlinfo.LATE_ADM_FEE_STATUS = X_LATE_ADM_FEE_STATUS)
1525            OR ((tlinfo.LATE_ADM_FEE_STATUS is null)
1526                AND (X_LATE_ADM_FEE_STATUS is null)))
1527       AND ((tlinfo.ADM_OUTCOME_STATUS = X_ADM_OUTCOME_STATUS)
1528            OR ((tlinfo.ADM_OUTCOME_STATUS is null)
1529                AND (X_ADM_OUTCOME_STATUS is null)))
1530       AND ((tlinfo.ADM_OTCM_STATUS_AUTH_PERSON_ID = X_ADM_OTCM_STATUS_AUTH_PER_ID)
1531            OR ((tlinfo.ADM_OTCM_STATUS_AUTH_PERSON_ID is null)
1532                AND (X_ADM_OTCM_STATUS_AUTH_PER_ID is null)))
1533       AND ((TRUNC(tlinfo.ADM_OUTCOME_STATUS_AUTH_DT) = TRUNC(X_ADM_OUTCOME_STATUS_AUTH_DT))
1534            OR ((tlinfo.ADM_OUTCOME_STATUS_AUTH_DT is null)
1535                AND (X_ADM_OUTCOME_STATUS_AUTH_DT is null)))
1536       AND ((tlinfo.ADM_OUTCOME_STATUS_REASON = X_ADM_OUTCOME_STATUS_REASON)
1537            OR ((tlinfo.ADM_OUTCOME_STATUS_REASON is null)
1538                AND (X_ADM_OUTCOME_STATUS_REASON is null)))
1539       AND ((TRUNC(tlinfo.OFFER_DT) = TRUNC(X_OFFER_DT))
1540            OR ((tlinfo.OFFER_DT is null)
1541                AND (X_OFFER_DT is null)))
1542       AND ((TRUNC(tlinfo.OFFER_RESPONSE_DT) = TRUNC(X_OFFER_RESPONSE_DT))
1543            OR ((tlinfo.OFFER_RESPONSE_DT is null)
1544                AND (X_OFFER_RESPONSE_DT is null)))
1545       AND ((TRUNC(tlinfo.PRPSD_COMMENCEMENT_DT) = TRUNC(X_PRPSD_COMMENCEMENT_DT))
1546            OR ((tlinfo.PRPSD_COMMENCEMENT_DT is null)
1547                AND (X_PRPSD_COMMENCEMENT_DT is null)))
1548       AND ((tlinfo.ADM_CNDTNL_OFFER_STATUS = X_ADM_CNDTNL_OFFER_STATUS)
1549            OR ((tlinfo.ADM_CNDTNL_OFFER_STATUS is null)
1550                AND (X_ADM_CNDTNL_OFFER_STATUS is null)))
1551       AND ((TRUNC(tlinfo.CNDTNL_OFFER_SATISFIED_DT) = TRUNC(X_CNDTNL_OFFER_SATISFIED_DT))
1552            OR ((tlinfo.CNDTNL_OFFER_SATISFIED_DT is null)
1553                AND (X_CNDTNL_OFFER_SATISFIED_DT is null)))
1554       AND ((tlinfo.CNDTNL_OFFER_MUST_BE_STSFD_IND = X_CNDTNL_OFR_MUST_BE_STSFD_IND)
1555            OR ((tlinfo.CNDTNL_OFFER_MUST_BE_STSFD_IND is null)
1556                AND (X_CNDTNL_OFR_MUST_BE_STSFD_IND is null)))
1557       AND ((tlinfo.ADM_OFFER_RESP_STATUS = X_ADM_OFFER_RESP_STATUS)
1558            OR ((tlinfo.ADM_OFFER_RESP_STATUS is null)
1559                AND (X_ADM_OFFER_RESP_STATUS is null)))
1560       AND ((TRUNC(tlinfo.ACTUAL_RESPONSE_DT) = TRUNC(X_ACTUAL_RESPONSE_DT))
1561            OR ((tlinfo.ACTUAL_RESPONSE_DT is null)
1562                AND (X_ACTUAL_RESPONSE_DT is null)))
1563       AND ((tlinfo.ADM_OFFER_DFRMNT_STATUS = X_ADM_OFFER_DFRMNT_STATUS)
1564            OR ((tlinfo.ADM_OFFER_DFRMNT_STATUS is null)
1565                AND (X_ADM_OFFER_DFRMNT_STATUS is null)))
1566       AND ((tlinfo.DEFERRED_ADM_CAL_TYPE = X_DEFERRED_ADM_CAL_TYPE)
1567            OR ((tlinfo.DEFERRED_ADM_CAL_TYPE is null)
1568                AND (X_DEFERRED_ADM_CAL_TYPE is null)))
1569       AND ((tlinfo.DEFERRED_ADM_CI_SEQUENCE_NUM = X_DEFERRED_ADM_CI_SEQUENCE_NUM)
1570            OR ((tlinfo.DEFERRED_ADM_CI_SEQUENCE_NUM is null)
1571                AND (X_DEFERRED_ADM_CI_SEQUENCE_NUM is null)))
1572       AND ((tlinfo.DEFERRED_TRACKING_ID = X_DEFERRED_TRACKING_ID)
1573            OR ((tlinfo.DEFERRED_TRACKING_ID is null)
1574                AND (X_DEFERRED_TRACKING_ID is null)))
1575       AND ((tlinfo.ASS_RANK = X_ASS_RANK)
1576            OR ((tlinfo.ASS_RANK is null)
1577                AND (X_ASS_RANK is null)))
1578       AND ((tlinfo.SECONDARY_ASS_RANK = X_SECONDARY_ASS_RANK)
1579            OR ((tlinfo.SECONDARY_ASS_RANK is null)
1580                AND (X_SECONDARY_ASS_RANK is null)))
1581       AND ((tlinfo.INTRNTNL_ACCEPTANCE_ADVICE_NUM = X_INTRNTNL_ACCEPT_ADVICE_NUM)
1582            OR ((tlinfo.INTRNTNL_ACCEPTANCE_ADVICE_NUM is null)
1583                AND (X_INTRNTNL_ACCEPT_ADVICE_NUM is null)))
1584       AND ((tlinfo.ASS_TRACKING_ID = X_ASS_TRACKING_ID)
1585            OR ((tlinfo.ASS_TRACKING_ID is null)
1586                AND (X_ASS_TRACKING_ID is null)))
1587       AND ((tlinfo.FEE_CAT = X_FEE_CAT)
1588            OR ((tlinfo.FEE_CAT is null)
1589                AND (X_FEE_CAT is null)))
1590       AND ((tlinfo.HECS_PAYMENT_OPTION = X_HECS_PAYMENT_OPTION)
1591            OR ((tlinfo.HECS_PAYMENT_OPTION is null)
1592                AND (X_HECS_PAYMENT_OPTION is null)))
1593       AND ((tlinfo.EXPECTED_COMPLETION_YR = X_EXPECTED_COMPLETION_YR)
1594            OR ((tlinfo.EXPECTED_COMPLETION_YR is null)
1595                AND (X_EXPECTED_COMPLETION_YR is null)))
1596       AND ((tlinfo.EXPECTED_COMPLETION_PERD = X_EXPECTED_COMPLETION_PERD)
1597            OR ((tlinfo.EXPECTED_COMPLETION_PERD is null)
1598                AND (X_EXPECTED_COMPLETION_PERD is null)))
1599       AND ((tlinfo.CORRESPONDENCE_CAT = X_CORRESPONDENCE_CAT)
1600            OR ((tlinfo.CORRESPONDENCE_CAT is null)
1601                AND (X_CORRESPONDENCE_CAT is null)))
1602       AND ((tlinfo.ENROLMENT_CAT = X_ENROLMENT_CAT)
1603            OR ((tlinfo.ENROLMENT_CAT is null)
1604                AND (X_ENROLMENT_CAT is null)))
1605       AND ((tlinfo.FUNDING_SOURCE = X_FUNDING_SOURCE)
1606            OR ((tlinfo.FUNDING_SOURCE is null)
1607                AND (X_FUNDING_SOURCE is null)))
1608       AND ((tlinfo.APPL_INST_STATUS = X_APPL_INST_STATUS)					--arvsrini igsm
1609            OR ((tlinfo.APPL_INST_STATUS is null)
1610                AND (X_APPL_INST_STATUS is null)))
1611 
1612       AND ((tlinfo.decision_Make_Id = X_decision_Make_Id)         -- BEGIN APADEGAL TD001 IGSM
1613                  OR ((tlinfo.decision_Make_Id is null)
1614                      AND (X_decision_Make_Id is null)))
1615 
1616       AND ((tlinfo.decision_Date = X_decision_Date)
1617                  OR ((tlinfo.decision_Date is null)
1618                      AND (X_decision_Date is null)))
1619 
1620       AND ((tlinfo.decision_reason_id = X_decision_reason_id)
1621                  OR ((tlinfo.decision_reason_id is null)
1622                      AND (X_decision_reason_id is null)))
1623 
1624       AND ((tlinfo.pending_reason_id = X_pending_reason_id)
1625                  OR ((tlinfo.pending_reason_id is null)
1626                      AND (X_pending_reason_id is null)))
1627 
1628       AND ((tlinfo.waitlist_status = X_waitlist_status)
1629                  OR ((tlinfo.waitlist_status is null)
1630                      AND (X_waitlist_status is null)))
1631 
1632       AND ((tlinfo.waitlist_rank = X_waitlist_rank)
1633                  OR ((tlinfo.waitlist_rank is null)
1634                      AND (X_waitlist_rank is null)))
1635 
1636       AND ((tlinfo.Future_Acad_Cal_Type = X_Future_Acad_Cal_Type)
1637                  OR ((tlinfo.Future_Acad_Cal_Type is null)
1638                      AND (X_Future_Acad_Cal_Type is null)))
1639 
1640       AND ((tlinfo.Future_Acad_Ci_Sequence_Num = X_Future_Acad_Ci_Sequence_Num)
1641                  OR ((tlinfo.Future_Acad_Ci_Sequence_Num is null)
1642                      AND (X_Future_Acad_Ci_Sequence_Num is null)))
1643 
1644       AND ((tlinfo.Future_Adm_Cal_Type = X_Future_Adm_Cal_Type)
1645                  OR ((tlinfo.Future_Adm_Cal_Type is null)
1646                      AND (X_Future_Adm_Cal_Type is null)))
1647 
1648       AND ((tlinfo.Future_Adm_Ci_Sequence_Num = X_Future_Adm_Ci_Sequence_Num)
1649                  OR ((tlinfo.Future_Adm_Ci_Sequence_Num is null)
1650                      AND (X_Future_Adm_Ci_Sequence_Num is null)))
1651 
1652       AND ((tlinfo.Def_acad_cal_type = X_def_acad_cal_type)
1653                  OR ((tlinfo.Def_acad_cal_type is null)
1654                      AND (X_def_acad_cal_type is null)))
1655 
1656       AND ((tlinfo.def_Acad_Ci_Sequence_Num = X_Def_acad_Ci_Sequence_Num)
1657                  OR ((tlinfo.def_Acad_Ci_Sequence_Num is null)
1658                      AND (X_Def_acad_Ci_Sequence_Num is null)))
1659 
1660       AND ((tlinfo.RECONSIDER_FLAG = X_RECONSIDER_FLAG)
1661                  OR ((tlinfo.RECONSIDER_FLAG is null)
1662                      AND (X_RECONSIDER_FLAG is null)))
1663 
1664       AND ((tlinfo.DECLINE_OFR_REASON = X_DECLINE_OFR_REASON)
1665            OR ((tlinfo.DECLINE_OFR_REASON is null)
1666                AND (X_DECLINE_OFR_REASON is null)))          		     -- end APADEGAL TD001 IGSM
1667 
1668 
1669   ) then
1670     null;
1671   else
1672     fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1673         IGS_GE_MSG_STACK.ADD;
1674     app_exception.raise_exception;
1675   end if;
1676   return;
1677 end LOCK_ROW;
1678 
1679 procedure UPDATE_ROW (
1680   X_ROWID in VARCHAR2,
1681   X_PERSON_ID in NUMBER,
1682   X_ADMISSION_APPL_NUMBER in NUMBER,
1683   X_NOMINATED_COURSE_CD in VARCHAR2,
1684   X_SEQUENCE_NUMBER in NUMBER,
1685   X_HIST_START_DT in DATE,
1686   X_APPLICANT_ACPTNCE_CNDTN in VARCHAR2,
1687   X_CNDTNL_OFFER_CNDTN in VARCHAR2,
1688   X_HIST_END_DT in DATE,
1689   X_HIST_WHO in NUMBER,
1690   X_HIST_OFFER_ROUND_NUMBER in NUMBER,
1691   X_ADM_CAL_TYPE in VARCHAR2,
1692   X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
1693   X_COURSE_CD in VARCHAR2,
1694   X_CRV_VERSION_NUMBER in NUMBER,
1695   X_LOCATION_CD in VARCHAR2,
1696   X_ATTENDANCE_MODE in VARCHAR2,
1697   X_ATTENDANCE_TYPE in VARCHAR2,
1698   X_UNIT_SET_CD in VARCHAR2,
1699   X_US_VERSION_NUMBER in NUMBER,
1700   X_PREFERENCE_NUMBER in NUMBER,
1701   X_ADM_DOC_STATUS in VARCHAR2,
1702   X_ADM_ENTRY_QUAL_STATUS in VARCHAR2,
1703   X_LATE_ADM_FEE_STATUS in VARCHAR2,
1704   X_ADM_OUTCOME_STATUS in VARCHAR2,
1705   X_ADM_OTCM_STATUS_AUTH_PER_ID in NUMBER,
1706   X_ADM_OUTCOME_STATUS_AUTH_DT in DATE,
1707   X_ADM_OUTCOME_STATUS_REASON in VARCHAR2,
1708   X_OFFER_DT in DATE,
1709   X_OFFER_RESPONSE_DT in DATE,
1710   X_PRPSD_COMMENCEMENT_DT in DATE,
1711   X_ADM_CNDTNL_OFFER_STATUS in VARCHAR2,
1712   X_CNDTNL_OFFER_SATISFIED_DT in DATE,
1713   X_CNDTNL_OFR_MUST_BE_STSFD_IND in VARCHAR2,
1714   X_ADM_OFFER_RESP_STATUS in VARCHAR2,
1715   X_ACTUAL_RESPONSE_DT in DATE,
1716   X_ADM_OFFER_DFRMNT_STATUS in VARCHAR2,
1717   X_DEFERRED_ADM_CAL_TYPE in VARCHAR2,
1718   X_DEFERRED_ADM_CI_SEQUENCE_NUM in NUMBER,
1719   X_DEFERRED_TRACKING_ID in NUMBER,
1720   X_ASS_RANK in NUMBER,
1721   X_SECONDARY_ASS_RANK in NUMBER,
1722   X_INTRNTNL_ACCEPT_ADVICE_NUM in NUMBER,
1723   X_ASS_TRACKING_ID in NUMBER,
1724   X_FEE_CAT in VARCHAR2,
1725   X_HECS_PAYMENT_OPTION in VARCHAR2,
1726   X_EXPECTED_COMPLETION_YR in NUMBER,
1727   X_EXPECTED_COMPLETION_PERD in VARCHAR2,
1728   X_CORRESPONDENCE_CAT in VARCHAR2,
1729   X_ENROLMENT_CAT in VARCHAR2,
1730   X_FUNDING_SOURCE in VARCHAR2,
1731   X_MODE in VARCHAR2,									--arvsrini igsm
1732   X_APPL_INST_STATUS IN VARCHAR2,
1733   X_DECISION_DATE                            DATE     DEFAULT NULL,	-- begin APADEGAL adtd001 igs.m
1734   X_DECISION_MAKE_ID                         NUMBER   DEFAULT NULL,
1735   X_DECISION_REASON_ID                       NUMBER   DEFAULT NULL,
1736   X_PENDING_REASON_ID                        NUMBER   DEFAULT NULL,
1737   X_WAITLIST_STATUS                          VARCHAR2 DEFAULT NULL,
1738   X_WAITLIST_RANK                            VARCHAR2 DEFAULT NULL,
1739   X_FUTURE_ACAD_CAL_TYPE                     VARCHAR2 DEFAULT NULL,
1740   X_FUTURE_ACAD_CI_SEQUENCE_NUM              NUMBER   DEFAULT NULL,
1741   X_FUTURE_ADM_CAL_TYPE                      VARCHAR2 DEFAULT NULL,
1742   X_FUTURE_ADM_CI_SEQUENCE_NUM               NUMBER   DEFAULT NULL,
1743   X_DEF_ACAD_CAL_TYPE                        VARCHAR2 DEFAULT NULL,
1744   X_DEF_ACAD_CI_SEQUENCE_NUM                 NUMBER   DEFAULT NULL,
1745   X_RECONSIDER_FLAG                          VARCHAR2 DEFAULT NULL,
1746   X_DECLINE_OFR_REASON                       VARCHAR2 DEFAULT NULL    -- end APADEGAL adtd001 igs.m
1747   ) as
1748     X_LAST_UPDATE_DATE DATE;
1749     X_LAST_UPDATED_BY NUMBER;
1750     X_LAST_UPDATE_LOGIN NUMBER;
1751 begin
1752   X_LAST_UPDATE_DATE := SYSDATE;
1753   if(X_MODE = 'I') then
1754     X_LAST_UPDATED_BY := 1;
1755     X_LAST_UPDATE_LOGIN := 0;
1756   elsif (X_MODE = 'R') then
1757     X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1758     if X_LAST_UPDATED_BY is NULL then
1759       X_LAST_UPDATED_BY := -1;
1760     end if;
1761     X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1762     if X_LAST_UPDATE_LOGIN is NULL then
1763       X_LAST_UPDATE_LOGIN := -1;
1764     end if;
1765   else
1766     FND_MESSAGE.SET_NAME('FND', 'SYSTEM-INVALID ARGS');
1767         IGS_GE_MSG_STACK.ADD;
1768     app_exception.raise_exception;
1769   end if;
1770 
1771   Before_DML(
1772   p_action =>'UPDATE',
1773   x_rowid =>X_ROWID,
1774   x_course_cd => X_COURSE_CD,
1775   x_crv_version_number => X_CRV_VERSION_NUMBER,
1776   x_location_cd =>  X_LOCATION_CD,
1777   x_attendance_mode=>X_ATTENDANCE_MODE,
1778   x_attendance_type=>X_ATTENDANCE_TYPE,
1779   x_unit_set_cd=>X_UNIT_SET_CD,
1780   x_us_version_number =>X_US_VERSION_NUMBER,
1781   x_preference_number =>X_PREFERENCE_NUMBER,
1782   x_adm_doc_status =>X_ADM_DOC_STATUS,
1783   x_adm_entry_qual_status =>X_ADM_ENTRY_QUAL_STATUS,
1784   x_late_adm_fee_status =>X_LATE_ADM_FEE_STATUS,
1785   x_adm_outcome_status =>X_ADM_OUTCOME_STATUS,
1786   x_adm_otcm_status_auth_per_id =>X_ADM_OTCM_STATUS_AUTH_PER_ID,
1787   x_adm_outcome_status_auth_dt =>X_ADM_OUTCOME_STATUS_AUTH_DT,
1788   x_adm_outcome_status_reason =>X_ADM_OUTCOME_STATUS_REASON,
1789   x_offer_dt =>X_OFFER_DT,
1790   x_offer_response_dt =>X_OFFER_RESPONSE_DT,
1791   x_prpsd_commencement_dt =>X_PRPSD_COMMENCEMENT_DT,
1792   x_adm_cndtnl_offer_status =>X_ADM_CNDTNL_OFFER_STATUS,
1793   x_cndtnl_offer_satisfied_dt =>X_CNDTNL_OFFER_SATISFIED_DT,
1794   x_cndtnl_ofr_must_be_stsfd_ind => X_CNDTNL_OFR_MUST_BE_STSFD_IND,
1795   x_adm_offer_resp_status => X_ADM_OFFER_RESP_STATUS,
1796   x_actual_response_dt => X_ACTUAL_RESPONSE_DT,
1797   x_adm_offer_dfrmnt_status =>X_ADM_OFFER_DFRMNT_STATUS,
1798   x_deferred_adm_cal_type =>X_DEFERRED_ADM_CAL_TYPE,
1799   x_deferred_adm_ci_sequence_num =>X_DEFERRED_ADM_CI_SEQUENCE_NUM,
1800   x_deferred_tracking_id =>X_DEFERRED_TRACKING_ID,
1801   x_ass_rank =>X_ASS_RANK,
1802   x_secondary_ass_rank =>X_SECONDARY_ASS_RANK,
1803   x_intrntnl_accept_advice_num =>X_INTRNTNL_ACCEPT_ADVICE_NUM,
1804   x_ass_tracking_id =>X_ASS_TRACKING_ID,
1805   x_fee_cat =>X_FEE_CAT,
1806   x_hecs_payment_option =>X_HECS_PAYMENT_OPTION,
1807   x_expected_completion_yr =>X_EXPECTED_COMPLETION_YR,
1808   x_expected_completion_perd =>X_EXPECTED_COMPLETION_PERD,
1809   x_correspondence_cat =>X_CORRESPONDENCE_CAT,
1810   x_enrolment_cat =>X_ENROLMENT_CAT,
1811   x_funding_source =>X_FUNDING_SOURCE,
1812   x_cndtnl_offer_cndtn =>X_CNDTNL_OFFER_CNDTN,
1813   x_applicant_acptnce_cndtn =>X_APPLICANT_ACPTNCE_CNDTN,
1814   x_person_id =>X_PERSON_ID,
1815   x_admission_appl_number =>X_ADMISSION_APPL_NUMBER,
1816   x_nominated_course_cd =>X_NOMINATED_COURSE_CD,
1817   x_sequence_number =>X_SEQUENCE_NUMBER,
1818   x_hist_start_dt =>X_HIST_START_DT,
1819   x_hist_end_dt =>X_HIST_END_DT,
1820   x_hist_who =>X_HIST_WHO,
1821   x_hist_offer_round_number=>X_HIST_OFFER_ROUND_NUMBER,
1822   x_adm_cal_type =>X_ADM_CAL_TYPE,
1823   x_adm_ci_sequence_number =>X_ADM_CI_SEQUENCE_NUMBER,
1824   x_creation_date => X_LAST_UPDATE_DATE,
1825   x_created_by => X_LAST_UPDATED_BY,
1826   x_last_update_date => X_LAST_UPDATE_DATE,
1827   x_last_updated_by => X_LAST_UPDATED_BY,
1828   x_last_update_login => X_LAST_UPDATE_LOGIN,
1829   x_appl_inst_status => X_APPL_INST_STATUS,					--arvsrini igsm
1830   x_decision_date                 =>	x_decision_date                ,    	        -- begin apadegal td001 igsm
1831   x_decision_make_id              =>	x_decision_make_id             ,
1832   x_decision_reason_id            =>	x_decision_reason_id           ,
1833   x_pending_reason_id             =>	x_pending_reason_id            ,
1834   x_waitlist_status               =>	x_waitlist_status              ,
1835   x_waitlist_rank                 =>	x_waitlist_rank                ,
1836   x_future_acad_cal_type          =>	x_future_acad_cal_type         ,
1837   x_future_acad_ci_sequence_num   =>	x_future_acad_ci_sequence_num  ,
1838   x_future_adm_cal_type           =>	x_future_adm_cal_type          ,
1839   x_future_adm_ci_sequence_num    =>	x_future_adm_ci_sequence_num   ,
1840   x_def_acad_cal_type             =>	x_def_acad_cal_type            ,
1841   x_def_acad_ci_sequence_num      =>	x_def_acad_ci_sequence_num     ,
1842   x_reconsider_flag               =>	x_reconsider_flag              ,
1843   x_decline_ofr_reason            => 	x_decline_ofr_reason              		-- end apadegal td001 igsm
1844   );
1845 
1846   update IGS_AD_PS_APLINSTHST_ALL set
1847     APPLICANT_ACPTNCE_CNDTN = NEW_REFERENCES.APPLICANT_ACPTNCE_CNDTN,
1848     CNDTNL_OFFER_CNDTN = NEW_REFERENCES.CNDTNL_OFFER_CNDTN,
1849     HIST_END_DT = NEW_REFERENCES.HIST_END_DT,
1850     HIST_WHO = NEW_REFERENCES.HIST_WHO,
1851     HIST_OFFER_ROUND_NUMBER = NEW_REFERENCES.HIST_OFFER_ROUND_NUMBER,
1852     ADM_CAL_TYPE = NEW_REFERENCES.ADM_CAL_TYPE,
1853     ADM_CI_SEQUENCE_NUMBER = NEW_REFERENCES.ADM_CI_SEQUENCE_NUMBER,
1854     COURSE_CD = NEW_REFERENCES.COURSE_CD,
1855     CRV_VERSION_NUMBER = NEW_REFERENCES.CRV_VERSION_NUMBER,
1856     LOCATION_CD = NEW_REFERENCES.LOCATION_CD,
1857     ATTENDANCE_MODE = NEW_REFERENCES.ATTENDANCE_MODE,
1858     ATTENDANCE_TYPE = NEW_REFERENCES.ATTENDANCE_TYPE,
1859     UNIT_SET_CD = NEW_REFERENCES.UNIT_SET_CD,
1860     US_VERSION_NUMBER = NEW_REFERENCES.US_VERSION_NUMBER,
1861     PREFERENCE_NUMBER = NEW_REFERENCES.PREFERENCE_NUMBER,
1862     ADM_DOC_STATUS = NEW_REFERENCES.ADM_DOC_STATUS,
1863     ADM_ENTRY_QUAL_STATUS = NEW_REFERENCES.ADM_ENTRY_QUAL_STATUS,
1864     LATE_ADM_FEE_STATUS = NEW_REFERENCES.LATE_ADM_FEE_STATUS,
1865     ADM_OUTCOME_STATUS = NEW_REFERENCES.ADM_OUTCOME_STATUS,
1866     ADM_OTCM_STATUS_AUTH_PERSON_ID = NEW_REFERENCES.ADM_OTCM_STATUS_AUTH_PERSON_ID,
1867     ADM_OUTCOME_STATUS_AUTH_DT = NEW_REFERENCES.ADM_OUTCOME_STATUS_AUTH_DT,
1868     ADM_OUTCOME_STATUS_REASON = NEW_REFERENCES.ADM_OUTCOME_STATUS_REASON,
1869     OFFER_DT = NEW_REFERENCES.OFFER_DT,
1870     OFFER_RESPONSE_DT = NEW_REFERENCES.OFFER_RESPONSE_DT,
1871     PRPSD_COMMENCEMENT_DT = NEW_REFERENCES.PRPSD_COMMENCEMENT_DT,
1872     ADM_CNDTNL_OFFER_STATUS = NEW_REFERENCES.ADM_CNDTNL_OFFER_STATUS,
1873     CNDTNL_OFFER_SATISFIED_DT = NEW_REFERENCES.CNDTNL_OFFER_SATISFIED_DT,
1874     CNDTNL_OFFER_MUST_BE_STSFD_IND = NEW_REFERENCES.CNDTNL_OFFER_MUST_BE_STSFD_IND,
1875     ADM_OFFER_RESP_STATUS = NEW_REFERENCES.ADM_OFFER_RESP_STATUS,
1876     ACTUAL_RESPONSE_DT = NEW_REFERENCES.ACTUAL_RESPONSE_DT,
1877     ADM_OFFER_DFRMNT_STATUS = NEW_REFERENCES.ADM_OFFER_DFRMNT_STATUS,
1878     DEFERRED_ADM_CAL_TYPE = NEW_REFERENCES.DEFERRED_ADM_CAL_TYPE,
1879     DEFERRED_ADM_CI_SEQUENCE_NUM = NEW_REFERENCES.DEFERRED_ADM_CI_SEQUENCE_NUM,
1880     DEFERRED_TRACKING_ID = NEW_REFERENCES.DEFERRED_TRACKING_ID,
1881     ASS_RANK = NEW_REFERENCES.ASS_RANK,
1882     SECONDARY_ASS_RANK = NEW_REFERENCES.SECONDARY_ASS_RANK,
1883     INTRNTNL_ACCEPTANCE_ADVICE_NUM = NEW_REFERENCES.INTRNTNL_ACCEPTANCE_ADVICE_NUM,
1884     ASS_TRACKING_ID = NEW_REFERENCES.ASS_TRACKING_ID,
1885     FEE_CAT = NEW_REFERENCES.FEE_CAT,
1886     HECS_PAYMENT_OPTION = NEW_REFERENCES.HECS_PAYMENT_OPTION,
1887     EXPECTED_COMPLETION_YR = NEW_REFERENCES.EXPECTED_COMPLETION_YR,
1888     EXPECTED_COMPLETION_PERD = NEW_REFERENCES.EXPECTED_COMPLETION_PERD,
1889     CORRESPONDENCE_CAT = NEW_REFERENCES.CORRESPONDENCE_CAT,
1890     ENROLMENT_CAT = NEW_REFERENCES.ENROLMENT_CAT,
1891     FUNDING_SOURCE = NEW_REFERENCES.FUNDING_SOURCE,
1892     LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1893     LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1894     LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1895     APPL_INST_STATUS =X_APPL_INST_STATUS,				--arvsrini igsm
1896     decision_date                 = new_references.decision_date                ,    	        -- begin apadegal td001 igsm
1897     decision_make_id              = new_references.decision_make_id             ,
1898     decision_reason_id            = new_references.decision_reason_id           ,
1899     pending_reason_id             = new_references.pending_reason_id            ,
1900     waitlist_status               = new_references.waitlist_status              ,
1901     waitlist_rank                 = new_references.waitlist_rank                ,
1902     future_acad_cal_type          = new_references.future_acad_cal_type         ,
1903     future_acad_ci_sequence_num   = new_references.future_acad_ci_sequence_num  ,
1904     future_adm_cal_type           = new_references.future_adm_cal_type          ,
1905     future_adm_ci_sequence_num    = new_references.future_adm_ci_sequence_num   ,
1906     def_acad_cal_type             = new_references.def_acad_cal_type            ,
1907     def_acad_ci_sequence_num      = new_references.def_acad_ci_sequence_num     ,
1908     reconsider_flag               = new_references.reconsider_flag              ,
1909     decline_ofr_reason            = new_references.decline_ofr_reason              		-- end apadegal td001 igsm
1910   where ROWID = X_ROWID
1911   ;
1912   if (sql%notfound) then
1913           FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1914           IGS_GE_MSG_STACK.ADD;
1915           app_exception.raise_exception;
1916   end if;
1917 
1918 After_DML(
1919  p_action =>'UPDATE',
1920  x_rowid => X_ROWID
1921 );
1922 EXCEPTION
1923   WHEN OTHERS THEN
1924     IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1925       -- Code to handle Security Policy error raised
1926       -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1927       -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1928       -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1929       --    that the ownerof policy function does not have privilege to access.
1930       FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1931       FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1932       IGS_GE_MSG_STACK.ADD;
1933       app_exception.raise_exception;
1934     ELSE
1935       RAISE;
1936     END IF;
1937 
1938 end UPDATE_ROW;
1939 
1940 procedure ADD_ROW (
1941   X_ROWID in out NOCOPY VARCHAR2,
1942         X_ORG_ID in NUMBER,
1943   X_PERSON_ID in NUMBER,
1944   X_ADMISSION_APPL_NUMBER in NUMBER,
1945   X_NOMINATED_COURSE_CD in VARCHAR2,
1946   X_SEQUENCE_NUMBER in NUMBER,
1947   X_HIST_START_DT in DATE,
1948   X_APPLICANT_ACPTNCE_CNDTN in VARCHAR2,
1949   X_CNDTNL_OFFER_CNDTN in VARCHAR2,
1950   X_HIST_END_DT in DATE,
1951   X_HIST_WHO in NUMBER,
1952   X_HIST_OFFER_ROUND_NUMBER in NUMBER,
1953   X_ADM_CAL_TYPE in VARCHAR2,
1954   X_ADM_CI_SEQUENCE_NUMBER in NUMBER,
1955   X_COURSE_CD in VARCHAR2,
1956   X_CRV_VERSION_NUMBER in NUMBER,
1957   X_LOCATION_CD in VARCHAR2,
1958   X_ATTENDANCE_MODE in VARCHAR2,
1959   X_ATTENDANCE_TYPE in VARCHAR2,
1960   X_UNIT_SET_CD in VARCHAR2,
1961   X_US_VERSION_NUMBER in NUMBER,
1962   X_PREFERENCE_NUMBER in NUMBER,
1963   X_ADM_DOC_STATUS in VARCHAR2,
1964   X_ADM_ENTRY_QUAL_STATUS in VARCHAR2,
1965   X_LATE_ADM_FEE_STATUS in VARCHAR2,
1966   X_ADM_OUTCOME_STATUS in VARCHAR2,
1967   X_ADM_OTCM_STATUS_AUTH_PER_ID in NUMBER,
1968   X_ADM_OUTCOME_STATUS_AUTH_DT in DATE,
1969   X_ADM_OUTCOME_STATUS_REASON in VARCHAR2,
1970   X_OFFER_DT in DATE,
1971   X_OFFER_RESPONSE_DT in DATE,
1972   X_PRPSD_COMMENCEMENT_DT in DATE,
1973   X_ADM_CNDTNL_OFFER_STATUS in VARCHAR2,
1974   X_CNDTNL_OFFER_SATISFIED_DT in DATE,
1975   X_CNDTNL_OFR_MUST_BE_STSFD_IND in VARCHAR2,
1976   X_ADM_OFFER_RESP_STATUS in VARCHAR2,
1977   X_ACTUAL_RESPONSE_DT in DATE,
1978   X_ADM_OFFER_DFRMNT_STATUS in VARCHAR2,
1979   X_DEFERRED_ADM_CAL_TYPE in VARCHAR2,
1980   X_DEFERRED_ADM_CI_SEQUENCE_NUM in NUMBER,
1981   X_DEFERRED_TRACKING_ID in NUMBER,
1982   X_ASS_RANK in NUMBER,
1983   X_SECONDARY_ASS_RANK in NUMBER,
1984   X_INTRNTNL_ACCEPT_ADVICE_NUM in NUMBER,
1985   X_ASS_TRACKING_ID in NUMBER,
1986   X_FEE_CAT in VARCHAR2,
1987   X_HECS_PAYMENT_OPTION in VARCHAR2,
1988   X_EXPECTED_COMPLETION_YR in NUMBER,
1989   X_EXPECTED_COMPLETION_PERD in VARCHAR2,
1990   X_CORRESPONDENCE_CAT in VARCHAR2,
1991   X_ENROLMENT_CAT in VARCHAR2,
1992   X_FUNDING_SOURCE in VARCHAR2,
1993   X_MODE in VARCHAR2,
1994   X_APPL_INST_STATUS IN VARCHAR2,					--arvsrini igsm
1995   X_DECISION_DATE                            DATE     DEFAULT NULL,	-- begin APADEGAL adtd001 igs.m
1996   X_DECISION_MAKE_ID                         NUMBER   DEFAULT NULL,
1997   X_DECISION_REASON_ID                       NUMBER   DEFAULT NULL,
1998   X_PENDING_REASON_ID                        NUMBER   DEFAULT NULL,
1999   X_WAITLIST_STATUS                          VARCHAR2 DEFAULT NULL,
2000   X_WAITLIST_RANK                            VARCHAR2 DEFAULT NULL,
2001   X_FUTURE_ACAD_CAL_TYPE                     VARCHAR2 DEFAULT NULL,
2002   X_FUTURE_ACAD_CI_SEQUENCE_NUM              NUMBER   DEFAULT NULL,
2003   X_FUTURE_ADM_CAL_TYPE                      VARCHAR2 DEFAULT NULL,
2004   X_FUTURE_ADM_CI_SEQUENCE_NUM               NUMBER   DEFAULT NULL,
2005   X_DEF_ACAD_CAL_TYPE                        VARCHAR2 DEFAULT NULL,
2006   X_DEF_ACAD_CI_SEQUENCE_NUM                 NUMBER   DEFAULT NULL,
2007   X_RECONSIDER_FLAG                          VARCHAR2 DEFAULT NULL,
2008   X_DECLINE_OFR_REASON                       VARCHAR2 DEFAULT NULL    -- end APADEGAL adtd001 igs.m
2009   ) as
2010   cursor c1 is select rowid from IGS_AD_PS_APLINSTHST_ALL
2011      where PERSON_ID = X_PERSON_ID
2012      and ADMISSION_APPL_NUMBER = X_ADMISSION_APPL_NUMBER
2013      and NOMINATED_COURSE_CD = X_NOMINATED_COURSE_CD
2014      and SEQUENCE_NUMBER = X_SEQUENCE_NUMBER
2015      and HIST_START_DT = X_HIST_START_DT
2016   ;
2017 
2018 begin
2019   open c1;
2020   fetch c1 into X_ROWID;
2021   if (c1%notfound) then
2022     close c1;
2023     INSERT_ROW (
2024      X_ROWID,
2025      X_ORG_ID,
2026      X_PERSON_ID,
2027      X_ADMISSION_APPL_NUMBER,
2028      X_NOMINATED_COURSE_CD,
2029      X_SEQUENCE_NUMBER,
2030      X_HIST_START_DT,
2031      X_APPLICANT_ACPTNCE_CNDTN,
2032      X_CNDTNL_OFFER_CNDTN,
2033      X_HIST_END_DT,
2034      X_HIST_WHO,
2035      X_HIST_OFFER_ROUND_NUMBER,
2036      X_ADM_CAL_TYPE,
2037      X_ADM_CI_SEQUENCE_NUMBER,
2038      X_COURSE_CD,
2039      X_CRV_VERSION_NUMBER,
2040      X_LOCATION_CD,
2041      X_ATTENDANCE_MODE,
2042      X_ATTENDANCE_TYPE,
2043      X_UNIT_SET_CD,
2044      X_US_VERSION_NUMBER,
2045      X_PREFERENCE_NUMBER,
2046      X_ADM_DOC_STATUS,
2047      X_ADM_ENTRY_QUAL_STATUS,
2048      X_LATE_ADM_FEE_STATUS,
2049      X_ADM_OUTCOME_STATUS,
2050      X_ADM_OTCM_STATUS_AUTH_PER_ID,
2051      X_ADM_OUTCOME_STATUS_AUTH_DT,
2052      X_ADM_OUTCOME_STATUS_REASON,
2053      X_OFFER_DT,
2054      X_OFFER_RESPONSE_DT,
2055      X_PRPSD_COMMENCEMENT_DT,
2056      X_ADM_CNDTNL_OFFER_STATUS,
2057      X_CNDTNL_OFFER_SATISFIED_DT,
2058      X_CNDTNL_OFR_MUST_BE_STSFD_IND,
2059      X_ADM_OFFER_RESP_STATUS,
2060      X_ACTUAL_RESPONSE_DT,
2061      X_ADM_OFFER_DFRMNT_STATUS,
2062      X_DEFERRED_ADM_CAL_TYPE,
2063      X_DEFERRED_ADM_CI_SEQUENCE_NUM,
2064      X_DEFERRED_TRACKING_ID,
2065      X_ASS_RANK,
2066      X_SECONDARY_ASS_RANK,
2067      X_INTRNTNL_ACCEPT_ADVICE_NUM,
2068      X_ASS_TRACKING_ID,
2069      X_FEE_CAT,
2070      X_HECS_PAYMENT_OPTION,
2071      X_EXPECTED_COMPLETION_YR,
2072      X_EXPECTED_COMPLETION_PERD,
2073      X_CORRESPONDENCE_CAT,
2074      X_ENROLMENT_CAT,
2075      X_FUNDING_SOURCE,
2076      X_MODE,
2077      X_APPL_INST_STATUS, 				--arvsrini igsm
2078      X_DECISION_DATE                ,	-- begin APADEGAL adtd001 igs.m
2079      X_DECISION_MAKE_ID             ,
2080      X_DECISION_REASON_ID           ,
2081      X_PENDING_REASON_ID            ,
2082      X_WAITLIST_STATUS              ,
2083      X_WAITLIST_RANK                ,
2084      X_FUTURE_ACAD_CAL_TYPE         ,
2085      X_FUTURE_ACAD_CI_SEQUENCE_NUM  ,
2086      X_FUTURE_ADM_CAL_TYPE          ,
2087      X_FUTURE_ADM_CI_SEQUENCE_NUM   ,
2088      X_DEF_ACAD_CAL_TYPE            ,
2089      X_DEF_ACAD_CI_SEQUENCE_NUM     ,
2090      X_RECONSIDER_FLAG              ,
2091      X_DECLINE_OFR_REASON               -- end APADEGAL adtd001 igs.m
2092    );
2093     return;
2094   end if;
2095   close c1;
2096   UPDATE_ROW (
2097    X_ROWID,
2098    X_PERSON_ID,
2099    X_ADMISSION_APPL_NUMBER,
2100    X_NOMINATED_COURSE_CD,
2101    X_SEQUENCE_NUMBER,
2102    X_HIST_START_DT,
2103    X_APPLICANT_ACPTNCE_CNDTN,
2104    X_CNDTNL_OFFER_CNDTN,
2105    X_HIST_END_DT,
2106    X_HIST_WHO,
2107    X_HIST_OFFER_ROUND_NUMBER,
2108    X_ADM_CAL_TYPE,
2109    X_ADM_CI_SEQUENCE_NUMBER,
2110    X_COURSE_CD,
2111    X_CRV_VERSION_NUMBER,
2112    X_LOCATION_CD,
2113    X_ATTENDANCE_MODE,
2114    X_ATTENDANCE_TYPE,
2115    X_UNIT_SET_CD,
2116    X_US_VERSION_NUMBER,
2117    X_PREFERENCE_NUMBER,
2118    X_ADM_DOC_STATUS,
2119    X_ADM_ENTRY_QUAL_STATUS,
2120    X_LATE_ADM_FEE_STATUS,
2121    X_ADM_OUTCOME_STATUS,
2122    X_ADM_OTCM_STATUS_AUTH_PER_ID,
2123    X_ADM_OUTCOME_STATUS_AUTH_DT,
2124    X_ADM_OUTCOME_STATUS_REASON,
2125    X_OFFER_DT,
2126    X_OFFER_RESPONSE_DT,
2127    X_PRPSD_COMMENCEMENT_DT,
2128    X_ADM_CNDTNL_OFFER_STATUS,
2129    X_CNDTNL_OFFER_SATISFIED_DT,
2130    X_CNDTNL_OFR_MUST_BE_STSFD_IND,
2131    X_ADM_OFFER_RESP_STATUS,
2132    X_ACTUAL_RESPONSE_DT,
2133    X_ADM_OFFER_DFRMNT_STATUS,
2134    X_DEFERRED_ADM_CAL_TYPE,
2135    X_DEFERRED_ADM_CI_SEQUENCE_NUM,
2136    X_DEFERRED_TRACKING_ID,
2137    X_ASS_RANK,
2138    X_SECONDARY_ASS_RANK,
2139    X_INTRNTNL_ACCEPT_ADVICE_NUM,
2140    X_ASS_TRACKING_ID,
2141    X_FEE_CAT,
2142    X_HECS_PAYMENT_OPTION,
2143    X_EXPECTED_COMPLETION_YR,
2144    X_EXPECTED_COMPLETION_PERD,
2145    X_CORRESPONDENCE_CAT,
2146    X_ENROLMENT_CAT,
2147    X_FUNDING_SOURCE,
2148    X_MODE,
2149    X_APPL_INST_STATUS,
2150    X_DECISION_DATE                ,	-- begin APADEGAL adtd001 igs.m
2151    X_DECISION_MAKE_ID             ,
2152    X_DECISION_REASON_ID           ,
2153    X_PENDING_REASON_ID            ,
2154    X_WAITLIST_STATUS              ,
2155    X_WAITLIST_RANK                ,
2156    X_FUTURE_ACAD_CAL_TYPE         ,
2157    X_FUTURE_ACAD_CI_SEQUENCE_NUM  ,
2158    X_FUTURE_ADM_CAL_TYPE          ,
2159    X_FUTURE_ADM_CI_SEQUENCE_NUM   ,
2160    X_DEF_ACAD_CAL_TYPE            ,
2161    X_DEF_ACAD_CI_SEQUENCE_NUM     ,
2162    X_RECONSIDER_FLAG              ,
2163    X_DECLINE_OFR_REASON               -- end APADEGAL adtd001 igs.m
2164    );
2165 end ADD_ROW;
2166 
2167 procedure DELETE_ROW (
2168   X_ROWID in VARCHAR2
2169 ) as
2170 begin
2171 
2172 Before_DML(
2173  p_action =>'DELETE',
2174  x_rowid => X_ROWID
2175 );
2176   delete from IGS_AD_PS_APLINSTHST_ALL
2177   where ROWID = X_ROWID;
2178   if (sql%notfound) then
2179           FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
2180           IGS_GE_MSG_STACK.ADD;
2181           app_exception.raise_exception;
2182   end if;
2183 After_DML(
2184  p_action =>'DELETE',
2185  x_rowid => X_ROWID
2186 );
2187 EXCEPTION
2188   WHEN OTHERS THEN
2189     IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
2190       -- Code to handle Security Policy error raised
2191       -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
2192       -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
2193       -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
2194       --    that the ownerof policy function does not have privilege to access.
2195       FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
2196       FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
2197       IGS_GE_MSG_STACK.ADD;
2198       app_exception.raise_exception;
2199     ELSE
2200       RAISE;
2201     END IF;
2202 
2203 end DELETE_ROW;
2204 
2205 end IGS_AD_PS_APLINSTHST_PKG;