1 PACKAGE BODY igs_ad_app_req_pkg AS
2 /* $Header: IGSAIA2B.pls 120.7 2005/10/07 09:37:07 appldev ship $ */
3
4
5 l_rowid VARCHAR2(25);
6 old_references igs_ad_app_req%ROWTYPE;
7 new_references igs_ad_app_req%ROWTYPE;
8
9 PROCEDURE Set_Column_Values (
10 p_action IN VARCHAR2,
11 x_rowid IN VARCHAR2,
12 x_app_req_id IN NUMBER,
13 x_person_id IN NUMBER,
14 x_admission_appl_number IN NUMBER,
15 x_applicant_fee_type IN NUMBER,
16 x_applicant_fee_status IN NUMBER,
17 x_fee_date IN DATE,
18 x_fee_payment_method IN NUMBER,
19 x_fee_amount IN NUMBER,
20 x_creation_date IN DATE,
21 x_created_by IN NUMBER,
22 x_last_update_date IN DATE,
23 x_last_updated_by IN NUMBER,
24 x_last_update_login IN NUMBER ,
25 x_reference_num IN VARCHAR2 ,
26 x_credit_card_code IN VARCHAR2 ,
27 x_credit_card_holder_name IN VARCHAR2 ,
28 x_credit_card_number IN VARCHAR2 ,
29 x_credit_card_expiration_date IN DATE ,
30 x_rev_gl_ccid IN NUMBER ,
31 x_cash_gl_ccid IN NUMBER ,
32 x_rev_account_cd IN VARCHAR2 ,
33 x_cash_account_cd IN VARCHAR2 ,
34 x_gl_date IN DATE ,
35 x_gl_posted_date IN DATE ,
36 x_posting_control_id IN NUMBER ,
37 x_credit_card_tangible_cd IN VARCHAR2 ,
38 x_credit_card_payee_cd IN VARCHAR2 ,
39 x_credit_card_status_code IN VARCHAR2
40 ) AS
41
42 /*************************************************************
43 Created By :
44 Date Created By :
45 Purpose :
46 Know limitations, enhancements or remarks
47 Change History
48 Who When What
49 pathipat 16-Jun-2003 Enh 2831587 - FI210 Credit Card Fund Transfer build
50 Added cols - credit_card_tangible_cd, credit_card_payee_cd
51 and credit_card_status_code
52 smadathi 06-nov-2002 Enh. Bug 2584986. Added new columns as specified
53 in GL Interface CS Document
54 (reverse chronological order - newest change first)
55 ***************************************************************/
56
57 CURSOR cur_old_ref_values IS
58 SELECT *
59 FROM IGS_AD_APP_REQ
60 WHERE rowid = x_rowid;
61
62 BEGIN
63
64 l_rowid := x_rowid;
65
66 -- Code for setting the Old and New Reference Values.
67 -- Populate Old Values.
68 Open cur_old_ref_values;
69 Fetch cur_old_ref_values INTO old_references;
70 IF (cur_old_ref_values%NOTFOUND) AND (p_action NOT IN ('INSERT','VALIDATE_INSERT')) THEN
71 Close cur_old_ref_values;
72 Fnd_Message.Set_Name ('FND', 'FORM_RECORD_DELETED');
73 IGS_GE_MSG_STACK.ADD;
74 App_Exception.Raise_Exception;
75 Return;
76 END IF;
77 Close cur_old_ref_values;
78
79 -- Populate New Values.
80 new_references.app_req_id := x_app_req_id;
81 new_references.person_id := x_person_id;
82 new_references.admission_appl_number := x_admission_appl_number;
83 new_references.applicant_fee_type := x_applicant_fee_type;
84 new_references.applicant_fee_status := x_applicant_fee_status;
85 new_references.fee_date := TRUNC(x_fee_date);
86 new_references.fee_payment_method := x_fee_payment_method;
87 new_references.fee_amount := x_fee_amount;
88 new_references.reference_num := x_reference_num;
89 new_references.credit_card_code := x_credit_card_code;
90 new_references.credit_card_holder_name := x_credit_card_holder_name;
91 new_references.credit_card_number := x_credit_card_number;
92 new_references.credit_card_expiration_date := TRUNC(x_credit_card_expiration_date);
93 new_references.rev_gl_ccid := x_rev_gl_ccid;
94 new_references.cash_gl_ccid := x_cash_gl_ccid;
95 new_references.rev_account_cd := x_rev_account_cd;
96 new_references.cash_account_cd := x_cash_account_cd;
97 new_references.gl_date := TRUNC(x_gl_date);
98 new_references.gl_posted_date := TRUNC(x_gl_posted_date);
99 new_references.posting_control_id := x_posting_control_id;
100 new_references.credit_card_tangible_cd := x_credit_card_tangible_cd;
101 new_references.credit_card_payee_cd := x_credit_card_payee_cd;
102 new_references.credit_card_status_code := x_credit_card_status_code;
103
104 IF (p_action = 'UPDATE') THEN
105 new_references.creation_date := old_references.creation_date;
106 new_references.created_by := old_references.created_by;
107 ELSE
108 new_references.creation_date := x_creation_date;
109 new_references.created_by := x_created_by;
110 END IF;
111 new_references.last_update_date := x_last_update_date;
112 new_references.last_updated_by := x_last_updated_by;
113 new_references.last_update_login := x_last_update_login;
114
115 END Set_Column_Values;
116
117 PROCEDURE beforerowinsertupdate1 (
118 p_inserting BOOLEAN,
119 p_updating BOOLEAN,
120 p_deleting BOOLEAN ) IS
121
122 /*************************************************************
123 Created By : Rishi Ghosh
124 Date Created By : 17- apr-2003
125 Purpose : This procedure will perform all the validations that were earler performed
126 in the library before inserting and updating (bug#2901627)
127 Know limitations, enhancements or remarks
128 Change History
129 Who When What
130 NSINHA 05-June-03 Enh# 2860854 Enh for 'sf commercial payables integration'
131 NSINHA 01-July-03 Bug# 3017630 Ossuk15: sf commercial int: updating the existing enrollment deposit
132 NSINHA 25-July-03 Bug# 3017800 OSSUK15: ss: ua: update application information page - fee information - app fee
133 (reverse chronological order - newest change first)
134 ***************************************************************/
135
136 -- Cursor to get the application processing status for an application
137 CURSOR c_appl_status (cp_person_id igs_ad_app_req.person_id%TYPE,
138 cp_admission_appl_number igs_ad_app_req.admission_appl_number%TYPE) IS
139 SELECT s_adm_appl_status
140 FROM igs_ad_appl_stat
141 WHERE adm_appl_status = ( SELECT adm_appl_status
142 FROM igs_ad_appl
143 WHERE person_id = cp_person_id
144 AND admission_appl_number = cp_admission_appl_number );
145
146 l_appl_status igs_ad_appl.adm_appl_status%TYPE;
147
148 -- Cursor to get the fee type
149 CURSOR cur_enr_dpt(cp_applicant_fee_type igs_ad_app_req.applicant_fee_type%TYPE) IS
150 SELECT system_status
151 FROM igs_ad_code_classes
152 WHERE code_id = cp_applicant_fee_type;
153
154 -- Cursor to get the fee status
155 CURSOR cur_ent_dpt_upd(cp_applicant_fee_status igs_ad_app_req.applicant_fee_status%TYPE) IS
156 SELECT system_status
157 FROM igs_ad_code_classes
158 WHERE code_id = cp_applicant_fee_status;
159
160 l_fee_status igs_ad_code_classes.system_status%TYPE;
161 l_fee_type igs_ad_code_classes.system_status%TYPE;
162
163 -- Cursor to get the application date of the application
164 CURSOR c_appl_dt(cp_person_id igs_ad_app_req.person_id%TYPE,
165 cp_admission_appl_number igs_ad_app_req.admission_appl_number%TYPE) IS
166 SELECT appl_dt
167 FROM igs_ad_appl
168 where person_id = cp_person_id
169 and admission_appl_number = cp_admission_appl_number;
170
171 l_appl_dt igs_ad_appl.appl_dt%TYPE;
172 l_manage_acc igs_fi_control_all.manage_accounts%TYPE;
173 l_message_name fnd_new_messages.message_name%TYPE;
174
175 BEGIN
176
177 IF NVL(igs_ad_gen_015.g_chk_ad_app_req,'N') = 'N' THEN -- If this package is called from the igs_ad_gen_015.create_enrollment_deposit then
178 -- no validations will be performed.-- rghosh (bug#2901627)
179
180 OPEN cur_ent_dpt_upd(new_references.applicant_fee_status);
181 FETCH cur_ent_dpt_upd INTO l_fee_status;
182 CLOSE cur_ent_dpt_upd;
183
184 OPEN cur_enr_dpt(new_references.applicant_fee_type);
185 FETCH cur_enr_dpt INTO l_fee_type ;
186 CLOSE cur_enr_dpt;
187
188 IF NVL(p_updating,FALSE) OR NVL(p_inserting,FALSE) THEN
189 OPEN c_appl_status(new_references.person_id,new_references.admission_appl_number);
190 FETCH c_appl_status INTO l_appl_status;
191 CLOSE c_appl_status;
192 -- for an withdrawn application , or for an complete application for which the offer response status is not 'Accepted' or
193 -- the offer response status is 'Defferal' with deferment status as 'Confirmed' , the fee related information cannot be inserted.
194 /* removed the following validation for bug 3374937
195 IF l_appl_status = 'WITHDRAWN' OR
196 ( l_appl_status = 'COMPLETED' AND NOT igs_ad_gen_002.valid_ofr_resp_status(new_references.person_id,new_references.admission_appl_number)) THEN
197 fnd_message.set_name('IGS','IGS_AD_CANNOT_CHG_APPL_DTL');
198 igs_ge_msg_stack.add;
199 app_exception.raise_exception;
200 -- ADDED following check for Bug# 3017800
201 ELS*/
202 IF ( l_appl_status = 'COMPLETED' AND igs_ad_gen_002.valid_ofr_resp_status(new_references.person_id,new_references.admission_appl_number) AND l_fee_type <> 'ENROLL_DEPOSIT' ) THEN
203 fnd_message.set_name('IGS','IGS_AD_ENRDPT_UPD_CMP_APPL'); -- IGS_AD_ENRDPT_UPD_CMP_APPL: For a completed application only Enrollment Deposit fee records can be manupulated.
204 igs_ge_msg_stack.add;
205 app_exception.raise_exception;
206 END IF;
207
208 -- for an complete application for which the offer response status is 'Accepted' or the offer response status is
209 -- 'Defferal' with deferment status as 'Confirmed' , only fee type of enroll_deposit can be entered. Also manual
210 -- creation of paid or partial fee payment is not allowed.
211
212 -- NSINHA 05-June-03 correctled the 'ENROLL_DEPOSIT' with 'PAID' and 'PARTIAL' fee status.
213 IF igs_ad_gen_002.valid_ofr_resp_status(new_references.person_id,new_references.admission_appl_number) THEN
214 -- NSINHA 05-June-03 Enh# 2860854 Enh for 'sf commercial payables integration'
215 igs_fi_com_rec_interface.chk_manage_account( l_manage_acc, l_message_name);
216 -- If manage_Accounts is STUDENT_FINANCE
217 IF (l_manage_acc = 'STUDENT_FINANCE') THEN
218 -- DO NOT Allow Manual creation of Enrollment Deposit record with Application Fee Status mapped to system status of Partial or Paid.
219 IF NVL(p_inserting,FALSE) AND l_fee_type = 'ENROLL_DEPOSIT' AND l_fee_status IN ('PAID','PARTIAL') THEN
220 -- Manual creation of Enrollment Deposit record with Application Fee Status mapped to system status of Partial or Paid is not allowed.
221 fnd_message.set_name('IGS','IGS_AD_ENR_DPT_STATUS');
222 igs_ge_msg_stack.add;
223 app_exception.raise_exception;
224 END IF;
225
226 IF NVL(p_updating,FALSE) AND l_fee_type = 'ENROLL_DEPOSIT' AND l_fee_status IN ('PAID','PARTIAL') THEN
227 -- Update of an Enrollment Deposit record with Application Fee Status mapped to system status of Partial or Paid is not allowed.
228 fnd_message.set_name('IGS','IGS_AD_ENRDPT_UPD_NT_ALWD');
229 igs_ge_msg_stack.add;
230 app_exception.raise_exception;
231 END IF;
232 -- If manage_accounts is NULL or OTHER
233 ELSIF (l_manage_acc IS NULL OR l_manage_acc = 'OTHER') THEN
234 -- Allow Manual creation of Enrollment Deposit record with Application Fee Status mapped to system status of Partial or Paid.
235 NULL;
236 END IF;
237 ELSE
238 IF l_fee_type = 'ENROLL_DEPOSIT' THEN
239 fnd_message.set_name('IGS','IGS_AD_ENR_DPT_CNT_PAY');
240 igs_ge_msg_stack.add;
241 app_exception.raise_exception;
242 END IF;
243 END IF;
244
245 OPEN c_appl_dt (new_references.person_id,new_references.admission_appl_number);
246 FETCH c_appl_dt INTO l_appl_dt;
247 CLOSE c_appl_dt;
248
249 -- the fee date cannot be earlier than the application date
250 /* relaxed this validation as per bug 4027871
251 IF new_references.fee_date < l_appl_dt THEN
252 fnd_message.set_name('IGS','IGS_AD_APPL_DATE_ERROR');
253 fnd_message.set_token ('NAME',fnd_message.get_string('IGS','IGS_AD_FEE_DATE'));
254 igs_ge_msg_stack.add;
255 app_exception.raise_exception;
256 END IF;
257 */
258 -- the fee date cannot be later than the system date
259 IF new_references.fee_date > SYSDATE THEN
260 fnd_message.set_name('IGS','IGS_AD_DATE_SYSDATE');
261 fnd_message.set_token ('NAME',fnd_message.get_string('IGS','IGS_AD_FEE_DATE'));
262 igs_ge_msg_stack.add;
263 app_exception.raise_exception;
264 END IF;
265 END IF; -- p_updating, p_inserting
266 END IF; -- igs_ad_gen_015.g_chk_ad_app_req
267 END beforerowinsertupdate1;
268
269 PROCEDURE Check_Constraints (
270 Column_Name IN VARCHAR2 ,
271 Column_Value IN VARCHAR2 ) AS
272 /*************************************************************
273 Created By :
274 Date Created By :
275 Purpose :
276 Know limitations, enhancements or remarks
277 Change History
278 Who When What
279
280 (reverse chronological order - newest change first)
281 ***************************************************************/
282
283 BEGIN
284
285 IF column_name IS NULL THEN
286 NULL;
287 ELSIF UPPER(column_name) = 'FEE_AMOUNT' THEN
288 new_references.fee_amount := IGS_GE_NUMBER.TO_NUM(column_value);
289 NULL;
290 END IF;
291
292
293
294 -- The following code checks for check constraints on the Columns.
295 IF Upper(Column_Name) = 'FEE_AMOUNT' OR
296 Column_Name IS NULL THEN
297 IF NOT (new_references.fee_amount >= 0
298 OR new_references.fee_amount IS NULL) THEN
299 Fnd_Message.Set_Name('IGS','IGS_AD_FEE_AMT_NON_NEGATIVE');
300 IGS_GE_MSG_STACK.ADD;
301 App_Exception.Raise_Exception;
302 END IF;
303 END IF;
304
305 END Check_Constraints;
306
307
308
309
310 PROCEDURE Check_Parent_Existance AS
311 /*************************************************************
312 Created By :
313 Date Created By :
314 Purpose :
315 Know limitations, enhancements or remarks
316 Change History
317 Who When What
318 smadathi 06-nov-2002 Enh. Bug 2584986. Added
319 igs_fi_acc_pkg.get_pk_for_validation
320 (reverse chronological order - newest change first)
321 ***************************************************************/
322
323 BEGIN
324
325 IF (((old_references.fee_payment_method = new_references.fee_payment_method)) OR
326 ((new_references.fee_payment_method IS NULL))) THEN
327 NULL;
328 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
329 new_references.fee_payment_method ,
330 'SYS_FEE_PAY_METHOD',
331 'N'
332 ) THEN
333 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
334 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_FEE_PAY_METHOD'));
335 IGS_GE_MSG_STACK.ADD;
336 App_Exception.Raise_Exception;
337 END IF;
338
339 IF (((old_references.person_id = new_references.person_id) AND
340 (old_references.admission_appl_number = new_references.admission_appl_number)) OR
341 ((new_references.person_id IS NULL) OR
342 (new_references.admission_appl_number IS NULL))) THEN
343 NULL;
344 ELSIF NOT Igs_Ad_Appl_Pkg.Get_PK_For_Validation (
345 new_references.person_id,
346 new_references.admission_appl_number
347 ) THEN
348 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND');
349 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_APPL'));
350 IGS_GE_MSG_STACK.ADD;
351 App_Exception.Raise_Exception;
352 END IF;
353
354 IF (((old_references.applicant_fee_status = new_references.applicant_fee_status)) OR
355 ((new_references.applicant_fee_status IS NULL))) THEN
356 NULL;
357 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
358 new_references.applicant_fee_status ,
359 'SYS_FEE_STATUS',
360 'N'
361 ) THEN
362 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
363 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_APPLICANT_FEE_STAT'));
364 IGS_GE_MSG_STACK.ADD;
365 App_Exception.Raise_Exception;
366 END IF;
367
368 IF (((old_references.applicant_fee_type = new_references.applicant_fee_type)) OR
369 ((new_references.applicant_fee_type IS NULL))) THEN
370 NULL;
371 ELSIF NOT Igs_Ad_Code_Classes_Pkg.Get_UK2_For_Validation (
372 new_references.applicant_fee_type ,
373 'SYS_FEE_TYPE',
374 'N'
375 ) THEN
376 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
377 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_APPLICANT_FEE_TYPE'));
378 IGS_GE_MSG_STACK.ADD;
379 App_Exception.Raise_Exception;
380 END IF;
381
382 IF ((old_references.rev_account_cd = new_references.rev_account_cd) OR
383 (new_references.rev_account_cd IS NULL)) THEN
384 NULL;
385 ELSE
386 IF NOT igs_fi_acc_pkg.get_pk_for_validation (
387 new_references.rev_account_cd
388 ) THEN
389 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
390 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_REV_ACCT'));
391 IGS_GE_MSG_STACK.ADD;
392 App_Exception.Raise_Exception;
393 END IF;
394 END IF;
395
396 IF ((old_references.cash_account_cd = new_references.cash_account_cd) OR
397 (new_references.cash_account_cd IS NULL)) THEN
398 NULL;
399 ELSE
400 IF NOT igs_fi_acc_pkg.get_pk_for_validation (
401 new_references.cash_account_cd
402 ) THEN
403 FND_MESSAGE.SET_NAME('IGS','IGS_GE_PK_UK_NOT_FOUND_CLOSED');
404 FND_MESSAGE.SET_TOKEN('ATTRIBUTE',FND_MESSAGE.GET_STRING('IGS','IGS_AD_CASH_ACCT'));
405 IGS_GE_MSG_STACK.ADD;
406 App_Exception.Raise_Exception;
407 END IF;
408 END IF;
409
410 END Check_Parent_Existance;
411
412 FUNCTION Get_PK_For_Validation (
413 x_app_req_id IN NUMBER
414 ) RETURN BOOLEAN AS
415
416 /*************************************************************
417 Created By :
418 Date Created By :
419 Purpose :
420 Know limitations, enhancements or remarks
421 Change History
422 Who When What
423
424 (reverse chronological order - newest change first)
425 ***************************************************************/
426
427 CURSOR cur_rowid IS
428 SELECT rowid
429 FROM igs_ad_app_req
430 WHERE app_req_id = x_app_req_id
431 FOR UPDATE NOWAIT;
432
433 lv_rowid cur_rowid%RowType;
434
435 BEGIN
436
437 Open cur_rowid;
438 Fetch cur_rowid INTO lv_rowid;
439 IF (cur_rowid%FOUND) THEN
440 Close cur_rowid;
441 Return(TRUE);
442 ELSE
443 Close cur_rowid;
444 Return(FALSE);
445 END IF;
446 END Get_PK_For_Validation;
447
448
449 PROCEDURE Get_FK_Igs_Ad_Appl (
450 x_person_id IN NUMBER,
451 x_admission_appl_number IN NUMBER
452 ) AS
453
454 /*************************************************************
455 Created By :
456 Date Created By :
457 Purpose :
458 Know limitations, enhancements or remarks
459 Change History
460 Who When What
461
462 (reverse chronological order - newest change first)
463 ***************************************************************/
464
465 CURSOR cur_rowid IS
466 SELECT rowid
467 FROM igs_ad_app_req
468 WHERE person_id = x_person_id
469 AND admission_appl_number = x_admission_appl_number ;
470
471 lv_rowid cur_rowid%RowType;
472
473 BEGIN
474
475 Open cur_rowid;
476 Fetch cur_rowid INTO lv_rowid;
477 IF (cur_rowid%FOUND) THEN
478 Close cur_rowid;
479 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AAR_AA_FK');
480 IGS_GE_MSG_STACK.ADD;
481 App_Exception.Raise_Exception;
482 Return;
483 END IF;
484 Close cur_rowid;
485
486 END Get_FK_Igs_Ad_Appl;
487
488 PROCEDURE Get_FK_Igs_Ad_Code_Classes (
489 x_code_id IN NUMBER
490 ) AS
491
492 /*************************************************************
493 Created By :
494 Date Created By :
495 Purpose :
496 Know limitations, enhancements or remarks
497 Change History
498 Who When What
499
500 (reverse chronological order - newest change first)
501 ***************************************************************/
502
503 CURSOR cur_rowid IS
504 SELECT rowid
505 FROM igs_ad_app_req
506 WHERE applicant_fee_status = x_code_id ;
507
508 lv_rowid cur_rowid%RowType;
509
510 CURSOR cur_rowid2 IS
511 SELECT rowid
512 FROM igs_ad_app_req
513 WHERE applicant_fee_type = x_code_id ;
514
515 lv_rowid2 cur_rowid2%RowType;
516
517 CURSOR cur_rowid3 IS
518 SELECT rowid
519 FROM igs_ad_app_req
520 WHERE fee_payment_method = x_code_id ;
521
522 lv_rowid3 cur_rowid3%RowType;
523
524
525 BEGIN
526
527 Open cur_rowid;
528 Fetch cur_rowid INTO lv_rowid;
529 IF (cur_rowid%FOUND) THEN
530 Close cur_rowid;
531 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AAR_ACDC_FK3');
532 IGS_GE_MSG_STACK.ADD;
533 App_Exception.Raise_Exception;
534 Return;
535 END IF;
536 Close cur_rowid;
537
538 Open cur_rowid2;
539 Fetch cur_rowid2 INTO lv_rowid2;
540 IF (cur_rowid2%FOUND) THEN
541 Close cur_rowid2;
542 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AAR_ACADC_FK1');
543 IGS_GE_MSG_STACK.ADD;
544 App_Exception.Raise_Exception;
545 Return;
546 END IF;
547 Close cur_rowid2;
548
549 Open cur_rowid3;
550 Fetch cur_rowid3 INTO lv_rowid3;
551 IF (cur_rowid3%FOUND) THEN
552 Close cur_rowid3;
553 Fnd_Message.Set_Name ('IGS', 'IGS_AD_AAR_ACDC_FK2');
554 IGS_GE_MSG_STACK.ADD;
555 App_Exception.Raise_Exception;
556 Return;
557 END IF;
558 Close cur_rowid3;
559
560 END Get_FK_Igs_Ad_Code_Classes;
561
562
563 PROCEDURE Before_DML (
564 p_action IN VARCHAR2,
565 x_rowid IN VARCHAR2,
566 x_app_req_id IN NUMBER,
567 x_person_id IN NUMBER,
568 x_admission_appl_number IN NUMBER,
569 x_applicant_fee_type IN NUMBER,
570 x_applicant_fee_status IN NUMBER,
571 x_fee_date IN DATE,
572 x_fee_payment_method IN NUMBER,
573 x_fee_amount IN NUMBER,
574 x_reference_num IN VARCHAR2,
575 x_creation_date IN DATE,
576 x_created_by IN NUMBER,
577 x_last_update_date IN DATE,
578 x_last_updated_by IN NUMBER,
579 x_last_update_login IN NUMBER,
580 x_credit_card_code IN VARCHAR2 ,
581 x_credit_card_holder_name IN VARCHAR2 ,
582 x_credit_card_number IN VARCHAR2 ,
583 x_credit_card_expiration_date IN DATE ,
584 x_rev_gl_ccid IN NUMBER ,
585 x_cash_gl_ccid IN NUMBER ,
586 x_rev_account_cd IN VARCHAR2 ,
587 x_cash_account_cd IN VARCHAR2 ,
588 x_gl_date IN DATE ,
589 x_gl_posted_date IN DATE ,
590 x_posting_control_id IN NUMBER ,
591 x_credit_card_tangible_cd IN VARCHAR2 ,
592 x_credit_card_payee_cd IN VARCHAR2 ,
593 x_credit_card_status_code IN VARCHAR2
594 ) AS
595 /*************************************************************
596 Created By :
597 Date Created By :
598 Purpose :
599 Know limitations, enhancements or remarks
600 Change History
601 Who When What
602 pathipat 16-Jun-2003 Enh 2831587 - FI210 Credit Card Fund Transfer build
603 Added cols - credit_card_tangible_cd, credit_card_payee_cd
604 and credit_card_status_code
605 smadathi 06-nov-2002 Enh. Bug 2584986. Added new columns as specified
606 in GL Interface CS Document
607 (reverse chronological order - newest change first)
608 ***************************************************************/
609
610 BEGIN
611
612 Set_Column_Values (
613 p_action,
614 x_rowid,
615 x_app_req_id,
616 x_person_id,
617 x_admission_appl_number,
618 x_applicant_fee_type,
619 x_applicant_fee_status,
620 x_fee_date,
621 x_fee_payment_method,
622 x_fee_amount,
623 x_creation_date,
624 x_created_by,
625 x_last_update_date,
626 x_last_updated_by,
627 x_last_update_login ,
628 x_reference_num,
629 x_credit_card_code ,
630 x_credit_card_holder_name ,
631 x_credit_card_number ,
632 x_credit_card_expiration_date ,
633 x_rev_gl_ccid ,
634 x_cash_gl_ccid ,
635 x_rev_account_cd ,
636 x_cash_account_cd ,
637 x_gl_date ,
638 x_gl_posted_date ,
639 x_posting_control_id ,
640 x_credit_card_tangible_cd ,
641 x_credit_card_payee_cd ,
642 x_credit_card_status_code
643 );
644
645 igs_ad_app_req_pkg.g_pkg_cst_completed_chk := 'N'; -- this variable is called from the procedure igs_ad_gen_002.check_adm_appl_inst_stat (rghosh)
646
647 igs_ad_gen_002.check_adm_appl_inst_stat(
648 x_person_id,
649 x_admission_appl_number
650 );
651 igs_ad_app_req_pkg.g_pkg_cst_completed_chk := 'Y';
652
653 IF (p_action = 'INSERT') THEN
654 -- Call all the procedures related to Before Insert.
655 Null;
656 IF Get_Pk_For_Validation(
657 new_references.app_req_id) THEN
658 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
659 IGS_GE_MSG_STACK.ADD;
660 App_Exception.Raise_Exception;
661 END IF;
662 beforerowinsertupdate1( p_inserting => TRUE , p_updating => FALSE, p_deleting=> FALSE);
663 Check_Constraints;
664 Check_Parent_Existance;
665 ELSIF (p_action = 'UPDATE') THEN
666 -- Call all the procedures related to Before Update.
667 beforerowinsertupdate1( p_inserting => FALSE , p_updating =>TRUE , p_deleting=> FALSE);
668 Check_Constraints;
669 Check_Parent_Existance;
670 ELSIF (p_action = 'DELETE') THEN
671 -- Call all the procedures related to Before Delete.
672 NULL;
673 ELSIF (p_action = 'VALIDATE_INSERT') THEN
674 -- Call all the procedures related to Before Insert.
675 IF Get_PK_For_Validation (
676 new_references.app_req_id) THEN
677 Fnd_Message.Set_name('IGS','IGS_GE_RECORD_ALREADY_EXISTS');
678 IGS_GE_MSG_STACK.ADD;
679 App_Exception.Raise_Exception;
680 END IF;
681 beforerowinsertupdate1( p_inserting => TRUE , p_updating => FALSE, p_deleting=> FALSE);
682 Check_Constraints;
683 ELSIF (p_action = 'VALIDATE_UPDATE') THEN
684 beforerowinsertupdate1( p_inserting =>FALSE , p_updating => TRUE, p_deleting=> FALSE);
685 Check_Constraints;
686 ELSIF (p_action = 'VALIDATE_DELETE') THEN
687 NULL;
688 END IF;
689
690 END Before_DML;
691
692 PROCEDURE After_DML (
693 p_action IN VARCHAR2,
694 x_rowid IN VARCHAR2
695 ) IS
696 /*************************************************************
697 Created By :
698 Date Created By :
699 Purpose :
700 Know limitations, enhancements or remarks
701 Change History
702 Who When What
703
704 (reverse chronological order - newest change first)
705 ***************************************************************/
706
707 BEGIN
708
709 l_rowid := x_rowid;
710
711 IF (p_action = 'INSERT') THEN
712 -- Call all the procedures related to After Insert.
713 --Raise the Fee create Business Event
714 igs_ad_wf_001.FEE_PAYMENT_CRT_EVENT
715 (
716 P_PERSON_ID => NEW_REFERENCES.PERSON_ID,
717 P_ADMISSION_APPL_NUMBER => NEW_REFERENCES.ADMISSION_APPL_NUMBER,
718 P_APP_REQ_ID => NEW_REFERENCES.APP_REQ_ID,
719 P_APPLICANT_FEE_TYPE => NEW_REFERENCES.APPLICANT_FEE_TYPE,
720 P_APPLICANT_FEE_STATUS => NEW_REFERENCES.APPLICANT_FEE_STATUS
721 );
722
723 ELSIF (p_action = 'UPDATE') THEN
724 -- Call all the procedures related to After Update.
725 --Raise the Fee update Business Event
726 IF(NEW_REFERENCES.APPLICANT_FEE_STATUS <> OLD_REFERENCES.APPLICANT_FEE_STATUS) THEN
727 igs_ad_wf_001.FEE_PAYMENT_UPD_EVENT(
728 P_PERSON_ID => NEW_REFERENCES.PERSON_ID,
729 P_ADMISSION_APPL_NUMBER => NEW_REFERENCES.ADMISSION_APPL_NUMBER,
730 P_APP_REQ_ID => NEW_REFERENCES.APP_REQ_ID,
731 P_APPLICANT_FEE_TYPE => NEW_REFERENCES.APPLICANT_FEE_TYPE,
732 P_APPLICANT_FEE_STATUS_NEW => NEW_REFERENCES.APPLICANT_FEE_STATUS,
733 P_APPLICANT_FEE_STATUS_OLD => OLD_REFERENCES.APPLICANT_FEE_STATUS);
734 END IF;
735 ELSIF (p_action = 'DELETE') THEN
736 -- Call all the procedures related to After Delete.
737 Null;
738 END IF;
739
740 l_rowid:=NULL;
741 END After_DML;
742
743 procedure INSERT_ROW (
744 X_ROWID in out NOCOPY VARCHAR2,
745 x_APP_REQ_ID IN OUT NOCOPY NUMBER,
746 x_PERSON_ID IN NUMBER,
747 x_ADMISSION_APPL_NUMBER IN NUMBER,
748 x_APPLICANT_FEE_TYPE IN NUMBER,
749 x_APPLICANT_FEE_STATUS IN NUMBER,
750 x_FEE_DATE IN DATE,
751 x_FEE_PAYMENT_METHOD IN NUMBER,
752 x_FEE_AMOUNT IN NUMBER,
753 x_REFERENCE_NUM IN VARCHAR2,
754 X_MODE IN VARCHAR2 ,
755 x_credit_card_code IN VARCHAR2 ,
756 x_credit_card_holder_name IN VARCHAR2 ,
757 x_credit_card_number IN VARCHAR2 ,
758 x_credit_card_expiration_date IN DATE ,
759 x_rev_gl_ccid IN NUMBER ,
760 x_cash_gl_ccid IN NUMBER ,
761 x_rev_account_cd IN VARCHAR2 ,
762 x_cash_account_cd IN VARCHAR2 ,
763 x_gl_date IN DATE ,
764 x_gl_posted_date IN DATE ,
765 x_posting_control_id IN NUMBER ,
766 x_credit_card_tangible_cd IN VARCHAR2 ,
767 x_credit_card_payee_cd IN VARCHAR2 ,
768 x_credit_card_status_code IN VARCHAR2
769 ) AS
770 /*************************************************************
771 Created By :
772 Date Created By :
773 Purpose :
774 Know limitations, enhancements or remarks
775 Change History
776 Who When What
777 gurprsin 27-Sep-2005 Enh 4607540, Credit Card Enryption enhancement, Passed the encrypted value of credit_card_number to before_dml call.
778 ravishar 30-May-05 Security related changes
779 pathipat 16-Jun-2003 Enh 2831587 - FI210 Credit Card Fund Transfer build
780 Added cols - credit_card_tangible_cd, credit_card_payee_cd
781 and credit_card_status_code
782 smadathi 06-nov-2002 Enh. Bug 2584986. Added new columns as specified
783 in GL Interface CS Document
784 (reverse chronological order - newest change first)
785 ***************************************************************/
786
787 CURSOR C IS SELECT rowid FROM igs_ad_app_req
788 WHERE APP_REQ_ID= X_APP_REQ_ID
789 ;
790 X_LAST_UPDATE_DATE DATE ;
791 X_LAST_UPDATED_BY NUMBER ;
792 X_LAST_UPDATE_LOGIN NUMBER ;
793 -- l_v_cc_number igs_fi_credits_all.credit_card_number%TYPE;
794 begin
795 X_LAST_UPDATE_DATE := SYSDATE;
796 if(X_MODE = 'I') then
797 X_LAST_UPDATED_BY := 1;
798 X_LAST_UPDATE_LOGIN := 0;
799 elsif (X_MODE IN ('R', 'S')) then
800 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
801 if X_LAST_UPDATED_BY is NULL then
802 X_LAST_UPDATED_BY := -1;
803 end if;
804 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
805 if X_LAST_UPDATE_LOGIN is NULL then
806 X_LAST_UPDATE_LOGIN := -1;
807 end if;
808 else
809 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
810 IGS_GE_MSG_STACK.ADD;
811 app_exception.raise_exception;
812 end if;
813
814 X_APP_REQ_ID := -1;
815
816 --Enh 4607540 , Calling iPayment API to get the encrypted value of Credit card number.
817 --Bug 4660773 This Code logic is commented as the part of the Bug 4660773 Dont remove the commented Code
818 /* IF x_credit_card_number IS NOT NULL THEN
819 l_v_cc_number := IBY_CC_SECURITY_PUB.SECURE_CARD_NUMBER(p_commit => FND_API.G_FALSE , p_card_number => x_credit_card_number);
820 ELSE
821 l_v_cc_number := x_credit_card_number;
822 END IF;*/
823 Before_DML(
824 p_action=>'INSERT',
825 x_rowid=>X_ROWID,
826 x_app_req_id=>X_APP_REQ_ID,
827 x_person_id=>X_PERSON_ID,
828 x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
829 x_applicant_fee_type=>X_APPLICANT_FEE_TYPE,
830 x_applicant_fee_status=>X_APPLICANT_FEE_STATUS,
831 x_fee_date=>X_FEE_DATE,
832 x_fee_payment_method=>X_FEE_PAYMENT_METHOD,
833 x_fee_amount=>X_FEE_AMOUNT,
834 x_reference_num=>X_REFERENCE_NUM,
835 x_creation_date=>X_LAST_UPDATE_DATE,
836 x_created_by=>X_LAST_UPDATED_BY,
837 x_last_update_date=>X_LAST_UPDATE_DATE,
838 x_last_updated_by=>X_LAST_UPDATED_BY,
839 x_last_update_login=>X_LAST_UPDATE_LOGIN,
840 x_credit_card_code => x_credit_card_code,
841 x_credit_card_holder_name => x_credit_card_holder_name,
842 x_credit_card_number => x_credit_card_number,
843 x_credit_card_expiration_date => x_credit_card_expiration_date,
844 x_rev_gl_ccid => x_rev_gl_ccid,
845 x_cash_gl_ccid => x_cash_gl_ccid,
846 x_rev_account_cd => x_rev_account_cd,
847 x_cash_account_cd => x_cash_account_cd,
848 x_gl_date => x_gl_date,
849 x_gl_posted_date => x_gl_posted_date,
850 x_posting_control_id => x_posting_control_id,
851 x_credit_card_tangible_cd => x_credit_card_tangible_cd,
852 x_credit_card_payee_cd => x_credit_card_payee_cd,
853 x_credit_card_status_code => x_credit_card_status_code
854 );
855 IF (x_mode = 'S') THEN
856 igs_sc_gen_001.set_ctx('R');
857 END IF;
858 INSERT INTO IGS_AD_APP_REQ (
859 APP_REQ_ID
860 ,PERSON_ID
861 ,ADMISSION_APPL_NUMBER
862 ,APPLICANT_FEE_TYPE
863 ,APPLICANT_FEE_STATUS
864 ,FEE_DATE
865 ,FEE_PAYMENT_METHOD
866 ,FEE_AMOUNT
867 ,REFERENCE_NUM
868 ,CREATION_DATE
869 ,CREATED_BY
870 ,LAST_UPDATE_DATE
871 ,LAST_UPDATED_BY
872 ,LAST_UPDATE_LOGIN,
873 credit_card_code ,
874 credit_card_holder_name ,
875 credit_card_number ,
876 credit_card_expiration_date ,
877 rev_gl_ccid ,
878 cash_gl_ccid ,
879 rev_account_cd ,
880 cash_account_cd ,
881 gl_date ,
882 gl_posted_date ,
883 posting_control_id ,
884 credit_card_tangible_cd ,
885 credit_card_payee_cd ,
886 credit_card_status_code
887 ) VALUES (
888 IGS_AD_APP_REQ_S.NEXTVAL
889 ,NEW_REFERENCES.PERSON_ID
890 ,NEW_REFERENCES.ADMISSION_APPL_NUMBER
891 ,NEW_REFERENCES.APPLICANT_FEE_TYPE
892 ,NEW_REFERENCES.APPLICANT_FEE_STATUS
893 ,NEW_REFERENCES.FEE_DATE
894 ,NEW_REFERENCES.FEE_PAYMENT_METHOD
895 ,NEW_REFERENCES.FEE_AMOUNT
896 ,NEW_REFERENCES.REFERENCE_NUM
897 ,X_LAST_UPDATE_DATE
898 ,X_LAST_UPDATED_BY
899 ,X_LAST_UPDATE_DATE
900 ,X_LAST_UPDATED_BY
901 ,X_LAST_UPDATE_LOGIN,
902 new_references.credit_card_code ,
903 new_references.credit_card_holder_name ,
904 new_references.credit_card_number ,
905 new_references.credit_card_expiration_date ,
906 new_references.rev_gl_ccid ,
907 new_references.cash_gl_ccid ,
908 new_references.rev_account_cd ,
909 new_references.cash_account_cd ,
910 new_references.gl_date ,
911 new_references.gl_posted_date ,
912 new_references.posting_control_id ,
913 new_references.credit_card_tangible_cd ,
914 new_references.credit_card_payee_cd ,
915 new_references.credit_card_status_code
916
917 ) RETURNING APP_REQ_ID INTO X_APP_REQ_ID;
918 new_references.APP_REQ_ID := X_APP_REQ_ID;
919 IF (x_mode = 'S') THEN
920 igs_sc_gen_001.unset_ctx('R');
921 END IF;
922
923 open c;
924 fetch c into X_ROWID;
925 if (c%notfound) then
926 close c;
927 raise no_data_found;
928 end if;
929 close c;
930 After_DML (
931 p_action => 'INSERT' ,
932 x_rowid => X_ROWID );
933 EXCEPTION
934 WHEN OTHERS THEN
935 IF (x_mode = 'S') THEN
936 igs_sc_gen_001.unset_ctx('R');
937 END IF;
938 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
939 -- Code to handle Security Policy error raised
940 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
941 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
942 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
943 -- that the ownerof policy function does not have privilege to access.
944 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
945 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
946 IGS_GE_MSG_STACK.ADD;
947 app_exception.raise_exception;
948 ELSE
949 RAISE;
950 END IF;
951 END insert_row;
952
953 PROCEDURE lock_row (
954 X_ROWID in VARCHAR2,
955 x_APP_REQ_ID IN NUMBER,
956 x_PERSON_ID IN NUMBER,
957 x_ADMISSION_APPL_NUMBER IN NUMBER,
958 x_APPLICANT_FEE_TYPE IN NUMBER,
959 x_APPLICANT_FEE_STATUS IN NUMBER,
960 x_FEE_DATE IN DATE,
961 x_FEE_PAYMENT_METHOD IN NUMBER,
962 x_FEE_AMOUNT IN NUMBER,
963 x_REFERENCE_NUM IN VARCHAR2,
964 x_credit_card_code IN VARCHAR2 ,
965 x_credit_card_holder_name IN VARCHAR2 ,
966 x_credit_card_number IN VARCHAR2 ,
967 x_credit_card_expiration_date IN DATE ,
968 x_rev_gl_ccid IN NUMBER ,
969 x_cash_gl_ccid IN NUMBER ,
970 x_rev_account_cd IN VARCHAR2 ,
971 x_cash_account_cd IN VARCHAR2 ,
972 x_gl_date IN DATE ,
973 x_gl_posted_date IN DATE ,
974 x_posting_control_id IN NUMBER ,
975 x_credit_card_tangible_cd IN VARCHAR2 ,
976 x_credit_card_payee_cd IN VARCHAR2 ,
977 x_credit_card_status_code IN VARCHAR2
978 ) AS
979 /*************************************************************
980 Created By :
981 Date Created By :
982 Purpose :
983 Know limitations, enhancements or remarks
984 Change History
985 Who When What
986 pathipat 16-Jun-2003 Enh 2831587 - FI210 Credit Card Fund Transfer build
987 Added cols - credit_card_tangible_cd, credit_card_payee_cd
988 and credit_card_status_code
989 smadathi 06-nov-2002 Enh. Bug 2584986. Added new columns as specified
990 in GL Interface CS Document
991 (reverse chronological order - newest change first)
992 ***************************************************************/
993
994 CURSOR c1 IS SELECT
995 person_id
996 , admission_appl_number
997 , applicant_fee_type
998 , applicant_fee_status
999 , fee_date
1000 , fee_payment_method
1001 , fee_amount
1002 , reference_num,
1003 credit_card_code ,
1004 credit_card_holder_name ,
1005 credit_card_number ,
1006 credit_card_expiration_date ,
1007 rev_gl_ccid ,
1008 cash_gl_ccid ,
1009 rev_account_cd ,
1010 cash_account_cd ,
1011 gl_date ,
1012 gl_posted_date ,
1013 posting_control_id ,
1014 credit_card_tangible_cd ,
1015 credit_card_payee_cd ,
1016 credit_card_status_code
1017
1018 FROM IGS_AD_APP_REQ
1019 WHERE rowid = x_rowid
1020 FOR UPDATE NOWAIT;
1021 tlinfo c1%ROWTYPE;
1022 begin
1023 open c1;
1024 fetch c1 into tlinfo;
1025 if (c1%notfound) then
1026 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
1027 IGS_GE_MSG_STACK.ADD;
1028 close c1;
1029 app_exception.raise_exception;
1030 return;
1031 end if;
1032 close c1;
1033 IF ( ( tlinfo.PERSON_ID = X_PERSON_ID)
1034 AND (tlinfo.ADMISSION_APPL_NUMBER = X_ADMISSION_APPL_NUMBER)
1035 AND (tlinfo.APPLICANT_FEE_TYPE = X_APPLICANT_FEE_TYPE)
1036 AND (tlinfo.APPLICANT_FEE_STATUS = X_APPLICANT_FEE_STATUS)
1037 AND (TRUNC(tlinfo.FEE_DATE) = TRUNC(X_FEE_DATE))
1038 AND ((tlinfo.fee_payment_method = X_FEE_PAYMENT_METHOD) OR ((tlinfo.fee_payment_method IS NULL) AND (X_FEE_PAYMENT_METHOD IS NULL)))
1039 AND (tlinfo.FEE_AMOUNT = X_FEE_AMOUNT)
1040 AND ((tlinfo.REFERENCE_NUM = x_reference_num)
1041 OR ((tlinfo.reference_num is null)
1042 AND (x_reference_num is null)))
1043 AND ((tlinfo.credit_card_code = x_credit_card_code) OR ((tlinfo.credit_card_code IS NULL) AND (x_credit_card_code IS NULL)))
1044 AND ((tlinfo.credit_card_holder_name = x_credit_card_holder_name) OR ((tlinfo.credit_card_holder_name IS NULL) AND (x_credit_card_holder_name IS NULL)))
1045 AND ((tlinfo.credit_card_number = x_credit_card_number) OR ((tlinfo.credit_card_number IS NULL) AND (x_credit_card_number IS NULL)))
1046 AND ((TRUNC(tlinfo.credit_card_expiration_date) = TRUNC(x_credit_card_expiration_date)) OR ((tlinfo.credit_card_expiration_date IS NULL) AND (x_credit_card_expiration_date IS NULL)))
1047 AND ((tlinfo.rev_gl_ccid = x_rev_gl_ccid) OR ((tlinfo.rev_gl_ccid IS NULL) AND (x_rev_gl_ccid IS NULL)))
1048 AND ((tlinfo.cash_gl_ccid = x_cash_gl_ccid) OR ((tlinfo.cash_gl_ccid IS NULL) AND (x_cash_gl_ccid IS NULL)))
1049 AND ((tlinfo.rev_account_cd = x_rev_account_cd) OR ((tlinfo.rev_account_cd IS NULL) AND (x_rev_account_cd IS NULL)))
1050 AND ((tlinfo.cash_account_cd = x_cash_account_cd) OR ((tlinfo.cash_account_cd IS NULL) AND (x_cash_account_cd IS NULL)))
1051 AND ((TRUNC(tlinfo.gl_date) = TRUNC(x_gl_date)) OR ((tlinfo.gl_date IS NULL) AND (x_gl_date IS NULL)))
1052 AND ((TRUNC(tlinfo.gl_posted_date) = TRUNC(x_gl_posted_date)) OR ((tlinfo.gl_posted_date IS NULL) AND (x_gl_posted_date IS NULL)))
1053 AND ((tlinfo.posting_control_id = x_posting_control_id) OR ((tlinfo.posting_control_id IS NULL) AND (x_posting_control_id IS NULL)))
1054 AND ((tlinfo.credit_card_tangible_cd = x_credit_card_tangible_cd) OR ((tlinfo.credit_card_tangible_cd IS NULL) AND (x_credit_card_tangible_cd IS NULL)))
1055 AND ((tlinfo.credit_card_payee_cd = x_credit_card_payee_cd) OR ((tlinfo.credit_card_payee_cd IS NULL) AND (x_credit_card_payee_cd IS NULL)))
1056 AND ((tlinfo.credit_card_status_code = x_credit_card_status_code) OR ((tlinfo.credit_card_status_code IS NULL) AND (x_credit_card_status_code IS NULL)))
1057 ) THEN
1058 NULL;
1059 ELSE
1060 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
1061 igs_ge_msg_stack.add;
1062 app_exception.raise_exception;
1063 END IF;
1064 RETURN;
1065 END lock_row;
1066
1067 PROCEDURE update_row (
1068 X_ROWID in VARCHAR2,
1069 x_APP_REQ_ID IN NUMBER,
1070 x_PERSON_ID IN NUMBER,
1071 x_ADMISSION_APPL_NUMBER IN NUMBER,
1072 x_APPLICANT_FEE_TYPE IN NUMBER,
1073 x_APPLICANT_FEE_STATUS IN NUMBER,
1074 x_FEE_DATE IN DATE,
1075 x_FEE_PAYMENT_METHOD IN NUMBER,
1076 x_FEE_AMOUNT IN NUMBER,
1077 x_REFERENCE_NUM IN VARCHAR2,
1078 X_MODE IN VARCHAR2 ,
1079 x_credit_card_code IN VARCHAR2 ,
1080 x_credit_card_holder_name IN VARCHAR2 ,
1081 x_credit_card_number IN VARCHAR2 ,
1082 x_credit_card_expiration_date IN DATE ,
1083 x_rev_gl_ccid IN NUMBER ,
1084 x_cash_gl_ccid IN NUMBER ,
1085 x_rev_account_cd IN VARCHAR2 ,
1086 x_cash_account_cd IN VARCHAR2 ,
1087 x_gl_date IN DATE ,
1088 x_gl_posted_date IN DATE ,
1089 x_posting_control_id IN NUMBER ,
1090 x_credit_card_tangible_cd IN VARCHAR2 ,
1091 x_credit_card_payee_cd IN VARCHAR2 ,
1092 x_credit_card_status_code IN VARCHAR2
1093 ) AS
1094 /*************************************************************
1095 Created By :
1096 Date Created By :
1097 Purpose :
1098 Know limitations, enhancements or remarks
1099 Change History
1100 Who When What
1101 ravishar 30-May-200 Security related changes
1102 pathipat 16-Jun-2003 Enh 2831587 - FI210 Credit Card Fund Transfer build
1103 Added cols - credit_card_tangible_cd, credit_card_payee_cd
1104 and credit_card_status_code
1105 smadathi 06-nov-2002 Enh. Bug 2584986. Added new columns as specified
1106 in GL Interface CS Document
1107 (reverse chronological order - newest change first)
1108 ***************************************************************/
1109
1110 X_LAST_UPDATE_DATE DATE ;
1111 X_LAST_UPDATED_BY NUMBER ;
1112 X_LAST_UPDATE_LOGIN NUMBER ;
1113 begin
1114 X_LAST_UPDATE_DATE := SYSDATE;
1115 if(X_MODE = 'I') then
1116 X_LAST_UPDATED_BY := 1;
1117 X_LAST_UPDATE_LOGIN := 0;
1118 elsif (X_MODE IN ('R', 'S')) then
1119 X_LAST_UPDATED_BY := FND_GLOBAL.USER_ID;
1120 if X_LAST_UPDATED_BY is NULL then
1121 X_LAST_UPDATED_BY := -1;
1122 end if;
1123 X_LAST_UPDATE_LOGIN :=FND_GLOBAL.LOGIN_ID;
1124 if X_LAST_UPDATE_LOGIN is NULL then
1125 X_LAST_UPDATE_LOGIN := -1;
1126 end if;
1127 else
1128 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
1129 IGS_GE_MSG_STACK.ADD;
1130 app_exception.raise_exception;
1131 end if;
1132 Before_DML(
1133 p_action=>'UPDATE',
1134 x_rowid=>X_ROWID,
1135 x_app_req_id=>X_APP_REQ_ID,
1136 x_person_id=>X_PERSON_ID,
1137 x_admission_appl_number=>X_ADMISSION_APPL_NUMBER,
1138 x_applicant_fee_type=>X_APPLICANT_FEE_TYPE,
1139 x_applicant_fee_status=>X_APPLICANT_FEE_STATUS,
1140 x_fee_date=>X_FEE_DATE,
1141 x_fee_payment_method=>X_FEE_PAYMENT_METHOD,
1142 x_fee_amount=>X_FEE_AMOUNT,
1143 x_reference_num=>X_REFERENCE_NUM,
1144 x_creation_date=>X_LAST_UPDATE_DATE,
1145 x_created_by=>X_LAST_UPDATED_BY,
1146 x_last_update_date=>X_LAST_UPDATE_DATE,
1147 x_last_updated_by=>X_LAST_UPDATED_BY,
1148 x_last_update_login=>X_LAST_UPDATE_LOGIN,
1149 x_credit_card_code => x_credit_card_code,
1150 x_credit_card_holder_name => x_credit_card_holder_name,
1151 x_credit_card_number => x_credit_card_number,
1152 x_credit_card_expiration_date => x_credit_card_expiration_date,
1153 x_rev_gl_ccid => x_rev_gl_ccid,
1154 x_cash_gl_ccid => x_cash_gl_ccid,
1155 x_rev_account_cd => x_rev_account_cd,
1156 x_cash_account_cd => x_cash_account_cd,
1157 x_gl_date => x_gl_date,
1158 x_gl_posted_date => x_gl_posted_date,
1159 x_posting_control_id => x_posting_control_id,
1160 x_credit_card_tangible_cd => x_credit_card_tangible_cd,
1161 x_credit_card_payee_cd => x_credit_card_payee_cd,
1162 x_credit_card_status_code => x_credit_card_status_code
1163 );
1164 IF (x_mode = 'S') THEN
1165 igs_sc_gen_001.set_ctx('R');
1166 END IF;
1167 UPDATE igs_ad_app_req SET
1168 PERSON_ID = NEW_REFERENCES.PERSON_ID,
1169 ADMISSION_APPL_NUMBER = NEW_REFERENCES.ADMISSION_APPL_NUMBER,
1170 APPLICANT_FEE_TYPE = NEW_REFERENCES.APPLICANT_FEE_TYPE,
1171 APPLICANT_FEE_STATUS = NEW_REFERENCES.APPLICANT_FEE_STATUS,
1172 FEE_DATE = NEW_REFERENCES.FEE_DATE,
1173 FEE_PAYMENT_METHOD = NEW_REFERENCES.FEE_PAYMENT_METHOD,
1174 FEE_AMOUNT = NEW_REFERENCES.FEE_AMOUNT,
1175 REFERENCE_NUM = NEW_REFERENCES.REFERENCE_NUM,
1176 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
1177 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
1178 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN,
1179 credit_card_code = new_references.credit_card_code ,
1180 credit_card_holder_name = new_references.credit_card_holder_name,
1181 credit_card_number = new_references.credit_card_number,
1182 credit_card_expiration_date = new_references.credit_card_expiration_date,
1183 rev_gl_ccid = new_references.rev_gl_ccid,
1184 cash_gl_ccid = new_references.cash_gl_ccid,
1185 rev_account_cd = new_references.rev_account_cd,
1186 cash_account_cd = new_references.cash_account_cd,
1187 gl_date = new_references.gl_date,
1188 gl_posted_date = new_references.gl_posted_date,
1189 posting_control_id = new_references.posting_control_id,
1190 credit_card_tangible_cd = new_references.credit_card_tangible_cd,
1191 credit_card_payee_cd = new_references.credit_card_payee_cd,
1192 credit_card_status_code = new_references.credit_card_status_code
1193 WHERE rowid = x_rowid;
1194 IF (SQL%NOTFOUND) THEN
1195 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1196 igs_ge_msg_stack.add;
1197 IF (x_mode = 'S') THEN
1198 igs_sc_gen_001.unset_ctx('R');
1199 END IF;
1200 app_exception.raise_exception;
1201 END IF;
1202 IF (x_mode = 'S') THEN
1203 igs_sc_gen_001.unset_ctx('R');
1204 END IF;
1205
1206
1207 After_DML (
1208 p_action => 'UPDATE' ,
1209 x_rowid => X_ROWID
1210 );
1211 EXCEPTION
1212 WHEN OTHERS THEN
1213 IF (x_mode = 'S') THEN
1214 igs_sc_gen_001.unset_ctx('R');
1215 END IF;
1216 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1217 -- Code to handle Security Policy error raised
1218 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1219 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1220 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1221 -- that the ownerof policy function does not have privilege to access.
1222 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_UPD_POLICY_EXCP');
1223 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1224 IGS_GE_MSG_STACK.ADD;
1225 app_exception.raise_exception;
1226 ELSE
1227 RAISE;
1228 END IF;
1229 END update_row;
1230
1231 PROCEDURE add_row (
1232 X_ROWID in out NOCOPY VARCHAR2,
1233 x_APP_REQ_ID IN OUT NOCOPY NUMBER,
1234 x_PERSON_ID IN NUMBER,
1235 x_ADMISSION_APPL_NUMBER IN NUMBER,
1236 x_APPLICANT_FEE_TYPE IN NUMBER,
1237 x_APPLICANT_FEE_STATUS IN NUMBER,
1238 x_FEE_DATE IN DATE,
1239 x_FEE_PAYMENT_METHOD IN NUMBER,
1240 x_FEE_AMOUNT IN NUMBER,
1241 x_REFERENCE_NUM IN VARCHAR2,
1242 X_MODE IN VARCHAR2 ,
1243 x_credit_card_code IN VARCHAR2 ,
1244 x_credit_card_holder_name IN VARCHAR2 ,
1245 x_credit_card_number IN VARCHAR2 ,
1246 x_credit_card_expiration_date IN DATE ,
1247 x_rev_gl_ccid IN NUMBER ,
1248 x_cash_gl_ccid IN NUMBER ,
1249 x_rev_account_cd IN VARCHAR2 ,
1250 x_cash_account_cd IN VARCHAR2 ,
1251 x_gl_date IN DATE ,
1252 x_gl_posted_date IN DATE ,
1253 x_posting_control_id IN NUMBER ,
1254 x_credit_card_tangible_cd IN VARCHAR2 ,
1255 x_credit_card_payee_cd IN VARCHAR2 ,
1256 x_credit_card_status_code IN VARCHAR2
1257 ) AS
1258 /*************************************************************
1259 Created By :
1260 Date Created By :
1261 Purpose :
1262 Know limitations, enhancements or remarks
1263 Change History
1264 Who When What
1265 pathipat 16-Jun-2003 Enh 2831587 - FI210 Credit Card Fund Transfer build
1266 Added cols - credit_card_tangible_cd, credit_card_payee_cd
1267 and credit_card_status_code
1268 smadathi 06-nov-2002 Enh. Bug 2584986. Added new columns as specified
1269 in GL Interface CS Document
1270 (reverse chronological order - newest change first)
1271 ***************************************************************/
1272
1273 CURSOR c1 IS
1274 SELECT rowid
1275 FROM igs_ad_app_req
1276 WHERE app_req_id= x_app_req_id
1277 ;
1278 BEGIN
1279 OPEN c1;
1280 FETCH c1 INTO X_ROWID;
1281 IF (c1%NOTFOUND) THEN
1282 CLOSE c1;
1283 insert_row (
1284 X_ROWID,
1285 X_APP_REQ_ID,
1286 X_PERSON_ID,
1287 X_ADMISSION_APPL_NUMBER,
1288 X_APPLICANT_FEE_TYPE,
1289 X_APPLICANT_FEE_STATUS,
1290 X_FEE_DATE,
1291 X_FEE_PAYMENT_METHOD,
1292 X_FEE_AMOUNT,
1293 X_REFERENCE_NUM,
1294 X_MODE ,
1295 x_credit_card_code ,
1296 x_credit_card_holder_name ,
1297 x_credit_card_number ,
1298 x_credit_card_expiration_date ,
1299 x_rev_gl_ccid ,
1300 x_cash_gl_ccid ,
1301 x_rev_account_cd ,
1302 x_cash_account_cd ,
1303 x_gl_date ,
1304 x_gl_posted_date ,
1305 x_posting_control_id ,
1306 x_credit_card_tangible_cd ,
1307 x_credit_card_payee_cd ,
1308 x_credit_card_status_code
1309 );
1310 RETURN;
1311 END IF;
1312 CLOSE c1;
1313 update_row (
1314 X_ROWID,
1315 X_APP_REQ_ID,
1316 X_PERSON_ID,
1317 X_ADMISSION_APPL_NUMBER,
1318 X_APPLICANT_FEE_TYPE,
1319 X_APPLICANT_FEE_STATUS,
1320 X_FEE_DATE,
1321 X_FEE_PAYMENT_METHOD,
1322 X_FEE_AMOUNT,
1323 X_REFERENCE_NUM,
1324 X_MODE ,
1325 x_credit_card_code ,
1326 x_credit_card_holder_name ,
1327 x_credit_card_number ,
1328 x_credit_card_expiration_date ,
1329 x_rev_gl_ccid ,
1330 x_cash_gl_ccid ,
1331 x_rev_account_cd ,
1332 x_cash_account_cd ,
1333 x_gl_date ,
1334 x_gl_posted_date ,
1335 x_posting_control_id ,
1336 x_credit_card_tangible_cd ,
1337 x_credit_card_payee_cd ,
1338 x_credit_card_status_code
1339 );
1340 END add_row;
1341
1342 PROCEDURE delete_row (
1343 x_rowid IN VARCHAR2,
1344 x_mode IN VARCHAR2
1345 ) AS
1346 /*************************************************************
1347 Created By :
1348 Date Created By :
1349 Purpose :
1350 Know limitations, enhancements or remarks
1351 Change History
1352 Who When What
1353 ravishar 30-May-200 Security related changes
1354
1355 (reverse chronological order - newest change first)
1356 ***************************************************************/
1357
1358 BEGIN
1359 Before_DML (
1360 p_action => 'DELETE',
1361 x_rowid => X_ROWID
1362 );
1363 IF (x_mode = 'S') THEN
1364 igs_sc_gen_001.set_ctx('R');
1365 END IF;
1366 DELETE FROM IGS_AD_APP_REQ
1367 WHERE rowid = x_rowid;
1368 IF (SQL%NOTFOUND) THEN
1369 fnd_message.set_name ('IGS', 'IGS_SC_POLICY_UPD_DEL_EXCEP');
1370 igs_ge_msg_stack.add;
1371 IF (x_mode = 'S') THEN
1372 igs_sc_gen_001.set_ctx('R');
1373 END IF;
1374 app_exception.raise_exception;
1375 END IF;
1376 IF (x_mode = 'S') THEN
1377 igs_sc_gen_001.unset_ctx('R');
1378 END IF;
1379
1380 After_DML (
1381 p_action => 'DELETE',
1382 x_rowid => X_ROWID
1383 );
1384 EXCEPTION
1385 WHEN OTHERS THEN
1386 IF (x_mode = 'S') THEN
1387 igs_sc_gen_001.set_ctx('R');
1388 END IF;
1389 IF SQLCODE = '-28115' OR SQLCODE = '-28113' OR SQLCODE = '-28111' THEN
1390 -- Code to handle Security Policy error raised
1391 -- 1) ORA-28115 (policy with check option violation) which is raised when Policy predicate was evaluated to FALSE with the updated values.
1392 -- 2) ORA-28113 (policy predicate has error) which is raised when Policy function generates invalid predicate.
1393 -- 3) ORA-28111 (insufficient privilege to evaluate policy predicate) which is raised when Predicate has a subquery which contains objects
1394 -- that the ownerof policy function does not have privilege to access.
1395 FND_MESSAGE.SET_NAME ('IGS', 'IGS_SC_POLICY_EXCEPTION');
1396 FND_MESSAGE.SET_TOKEN('ERR_CD',SQLCODE);
1397 IGS_GE_MSG_STACK.ADD;
1398 app_exception.raise_exception;
1399 ELSE
1400 RAISE;
1401 END IF;
1402 END delete_row;
1403
1404 END igs_ad_app_req_pkg;
[Home] [Help]