[Home] [Help]
PACKAGE BODY: APPS.IGS_AD_GEN_015
Source
1 PACKAGE BODY igs_ad_gen_015 AS
2 /* $Header: IGSADC4B.pls 120.1 2005/09/30 04:52:44 appldev ship $ */
3 /******************************************************************
4 Created By: Navin Sinha
5 Date Created By: 07-Oct-2002
6 Purpose: BUG NO : 2602077 : HQ: release of build for td sf integrarion.
7 New Package to Insert record in the table IGS_AD_APP_REQ
8 (Application Fee ) whenever an Enrollment Deposit is
9 Recorded in the Student Finance .
10 Known limitations,enhancements,remarks:
11 Change History
12 Who When What
13 Navin.sinha 10/1/2003 BUG NO : 3160036 : OSSTST15: enrollment deposit fee error in sf gl interface process
14 Added New procedure to updare record in the table IGS_AD_APP_REQ.
15 pathipat 14-Jun-2003 Enh 2831587 - FI210 Credit Card Fund Transfer build
16 Modified create_enrollment_deposit() - igs_ad_app_req_pkg.insert_row() call
17 pathipat 06-Jan-2003 Bug: 2728620 and 2734574 - Removed exception section
18 in create_enrollment_deposit() and check_ad_code_classes_setup()
19 vvutukur 27-Nov-2002 Enh#2584986.Modified procedure create_enrollment_deposit.
20 ******************************************************************/
21 --Fwd Declarations
22 PROCEDURE check_ad_code_classes_setup (
23 p_class_type IN igs_ad_code_classes.class%TYPE -- APPLICATION_FEES
24 , p_class IN igs_ad_code_classes.class%TYPE -- FEE_PAYMENT_METHODS, FEE_STATUS, FEE_TYPES
25 , p_std_sys_stat IN igs_ad_code_classes.system_status%TYPE -- SYS_FEE_STATUS, SYS_FEE_TYPE, SYS_FEE_PAY_METHOD
26 , p_sys_stat IN igs_ad_code_classes.system_status%TYPE -- PAID, ENROLL_DEPOSIT, CHECK
27 , p_code_id OUT NOCOPY igs_ad_code_classes.code_id%TYPE -- Return value: CODE_ID
28 );
29
30 --Procedure Definitions
31 PROCEDURE create_enrollment_deposit(
32 p_person_id IN NUMBER,
33 p_admission_appl_number IN NUMBER,
34 p_enrollment_deposit_amount IN NUMBER,
35 p_payment_date IN DATE,
36 p_fee_payment_method IN VARCHAR2,
37 p_reference_number IN VARCHAR2 ) IS
38
39 /******************************************************************
40 Created By: Navin Sinha
41 Date Created By: 07-Oct-2002
42 Purpose: BUG NO : 2602077 : HQ: release of build for td sf integrarion.
43 New Procedure to Insert record in the table IGS_AD_APP_REQ
44 (Application Fee ) whenever an Enrollment Deposit is
45 Recorded in the Student Finance .
46 Known limitations,enhancements,remarks:
47 Change History
48 Who When What
49 pathipat 14-Jun-2003 Enh 2831587 - Credit Card Fund Transfer build
50 Modified call to igs_ad_app_req_pkg.insert_row - added 3 new parameters
51 pathipat 06-Jan-2003 Bug: 2728620 - Removed exception section
52 vvutukur 27-Nov-2002 Enh#2584986.Modified the tbh call to igs_ad_app_req.insert_row to include 11 new
53 columns. NULL is being passed to all the 11 columns.
54 ******************************************************************/
55
56 cst_paid CONSTANT VARCHAR2(5) := 'PAID';
57 cst_partial CONSTANT VARCHAR2(10) := 'PARTIAL';
58 cst_application_fees CONSTANT VARCHAR2(20) := 'APPL_FEES';
59 cst_fee_status CONSTANT VARCHAR2(20) := 'SYS_FEE_STATUS';
60 cst_sys_fee_status CONSTANT VARCHAR2(20) := 'SYS_FEE_STATUS';
61 cst_fee_types CONSTANT VARCHAR2(20) := 'SYS_FEE_TYPE';
62 cst_sys_fee_type CONSTANT VARCHAR2(20) := 'SYS_FEE_TYPE';
63 cst_enroll_deposit CONSTANT VARCHAR2(20) := 'ENROLL_DEPOSIT';
64 cst_fee_payment_methods CONSTANT VARCHAR2(20) := 'SYS_FEE_PAY_METHOD';
65 cst_sys_fee_pay_method CONSTANT VARCHAR2(20) := 'SYS_FEE_PAY_METHOD';
66
67 -- Cursor to Check if an enrollment deposit record(with system fee status as PARTIAL, PAID) already exists for this application.
68 -- SUM (fee_amount) > 0 will indicate that above condition is TRUE.
69 CURSOR c_paid_partial IS
70 SELECT NVL(SUM (fee_amount),0) total_fee_amount
71 FROM igs_ad_app_req
72 WHERE person_id = p_person_id
73 AND admission_appl_number = p_admission_appl_number
74 AND applicant_fee_status IN (SELECT code_id FROM igs_ad_code_classes WHERE class = cst_fee_status AND system_status IN (cst_paid,cst_partial) AND closed_ind = 'N' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES')
75 AND applicant_fee_type IN (SELECT code_id FROM igs_ad_code_classes WHERE class = cst_fee_types AND system_status = cst_enroll_deposit AND closed_ind = 'N' AND CLASS_TYPE_CODE='ADM_CODE_CLASSES');
76
77 c_paid_partial_rec c_paid_partial%ROWTYPE;
78
79 -- Cursor to get the enroll_deposit_amount for the application type
80 CURSOR c_en_deposit_amt IS
81 SELECT sat.enroll_deposit_amount enroll_deposit_amount
82 FROM igs_ad_ss_appl_typ sat,
83 igs_ad_appl aa
84 WHERE aa.person_id = p_person_id
85 AND aa.admission_appl_number = p_admission_appl_number
86 AND sat.admission_application_type = aa.application_type;
87
88 c_en_deposit_amt_rec c_en_deposit_amt%ROWTYPE;
89
90 l_fee_payment_method igs_lookups_view.lookup_code%TYPE;
91 l_rowid VARCHAR2(25);
92 l_app_req_id NUMBER;
93 l_paid_record_created BOOLEAN;
94 l_request_id NUMBER;
95 l_paid_fee_stat_code_id igs_ad_code_classes.code_id%TYPE;
96 l_partial_fee_stat_code_id igs_ad_code_classes.code_id%TYPE;
97 l_final_sys_dflt_fee_stat igs_ad_code_classes.code_id%TYPE;
98 l_fee_type_code_id igs_ad_code_classes.code_id%TYPE;
99 l_pay_mthd_code_id igs_ad_code_classes.code_id%TYPE;
100
101 BEGIN
102 -- Performing mandatory Admissions system setup check...
103 -- Check if set-up information exists for system defaulted user defined Fee Status with System Status: Paid.
104 check_ad_code_classes_setup (
105 cst_application_fees -- APPLICATION_FEES
106 , cst_fee_status -- FEE_STATUS
107 , cst_sys_fee_status -- SYS_FEE_STATUS
108 , cst_paid -- PAID
109 , l_paid_fee_stat_code_id -- Return value: CODE_ID
110 );
111
112 -- Check if set-up information exists for system defaulted user defined Fee Status with System Status: Partial Paid.
113 check_ad_code_classes_setup (
114 cst_application_fees -- APPLICATION_FEES
115 , cst_fee_status -- FEE_STATUS
116 , cst_sys_fee_status -- SYS_FEE_STATUS
117 , cst_partial -- PARTIAL
118 , l_partial_fee_stat_code_id -- Return value: CODE_ID
119 );
120
121 -- Check if set-up information exists for system defaulted user defined Fee Type with System Status: Enrollment Deposit.
122 check_ad_code_classes_setup (
123 cst_application_fees -- APPLICATION_FEES
124 , cst_fee_types -- FEE_TYPES
125 , cst_sys_fee_type -- SYS_FEE_TYPE
126 , cst_enroll_deposit -- ENROLL_DEPOSIT
127 , l_fee_type_code_id -- Return value: CODE_ID
128 );
129
130 -- Start Processing for creation of enrollment deposit record...
131 -- Check if an enrollment deposit record(with system fee status as PARTIAL, PAID) already exists for this application.
132 OPEN c_paid_partial;
133 FETCH c_paid_partial INTO c_paid_partial_rec;
134 CLOSE c_paid_partial;
135
136 -- Get the enroll_deposit_amount for the application type
137 OPEN c_en_deposit_amt;
138 FETCH c_en_deposit_amt INTO c_en_deposit_amt_rec;
139 IF (c_en_deposit_amt_rec.enroll_deposit_amount IS NOT NULL
140 AND ((p_enrollment_deposit_amount + c_paid_partial_rec.total_fee_amount) < c_en_deposit_amt_rec.enroll_deposit_amount)) THEN
141 -- Set status = system default for PARTIAL PAYMENT
142 l_final_sys_dflt_fee_stat := l_partial_fee_stat_code_id;
143 l_paid_record_created := FALSE;
144 ELSE
145 -- Set status = system default for PAID
146 l_final_sys_dflt_fee_stat := l_paid_fee_stat_code_id;
147 l_paid_record_created := TRUE;
148 END IF;
149 CLOSE c_en_deposit_amt;
150
151 -- Get the system defaulted user defined fee payment method.
152 IF p_fee_payment_method = 'CC' THEN
153 l_fee_payment_method := 'CREDIT_CARD';
154 ELSIF p_fee_payment_method IN ('CASH', 'CHECK') THEN
155 l_fee_payment_method := p_fee_payment_method;
156 ELSE
157 l_fee_payment_method := 'OTHER';
158 END IF;
159
160 -- Check if set-up information exists for system defaulted user defined Fee Payment Methods with System Status: l_fee_payment_method.
161 check_ad_code_classes_setup (
162 cst_application_fees -- APPLICATION_FEES
163 , cst_fee_payment_methods -- FEE_PAYMENT_METHODS
164 , cst_sys_fee_pay_method -- SYS_FEE_PAY_METHOD
165 , l_fee_payment_method -- CHECK
166 , l_pay_mthd_code_id -- Return value: CODE_ID
167 );
168
169 igs_ad_gen_015.g_chk_ad_app_req := 'Y'; -- Based on the value of this variable , some of the validation wont be perform in igs_ad_app_req_pkg(bug#2901627 -- rghosh)
170
171 -- Create an fee payment record in the AD table
172 igs_ad_app_req_pkg.insert_row (
173 X_Mode => 'R',
174 X_RowId => l_rowid,
175 X_App_Req_Id => l_app_req_id,
176 X_Person_Id => p_person_id,
177 X_Admission_Appl_Number => p_admission_appl_number,
178 X_Applicant_Fee_Type => l_fee_type_code_id,
179 X_Applicant_Fee_Status => l_final_sys_dflt_fee_stat,
180 X_Fee_Date => p_payment_date,
181 X_Fee_Payment_Method => l_pay_mthd_code_id,
182 X_Fee_Amount => p_enrollment_deposit_amount,
183 X_Reference_Num => p_reference_number,
184 x_credit_card_code => NULL,
185 x_credit_card_holder_name => NULL,
186 x_credit_card_number => NULL,
187 x_credit_card_expiration_date => NULL,
188 x_rev_gl_ccid => NULL,
189 x_cash_gl_ccid => NULL,
190 x_rev_account_cd => NULL,
191 x_cash_account_cd => NULL,
192 x_gl_date => NULL,
193 x_gl_posted_date => NULL,
194 x_posting_control_id => NULL,
195 x_credit_card_tangible_cd => NULL,
196 x_credit_card_payee_cd => NULL,
197 x_credit_card_status_code => NULL
198 );
199
200 igs_ad_gen_015.g_chk_ad_app_req := 'N';
201
202 -- Call the Admissions Tracking Item Completion job if a record with "Paid" Fee status is successfully created.
203 IF l_paid_record_created AND l_rowid IS NOT NULL THEN
204 -- Call Job Igs_ad_ti_comp.upd_trk_itm_st (Admissions Tracking Item Completion job)
205 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
206 APPLICATION => 'IGS',
207 PROGRAM => 'IGSADJ14',
208 DESCRIPTION => 'Admission Tracking Item Completion',
209 START_TIME => NULL,
210 SUB_REQUEST => FALSE,
211 ARGUMENT1 => p_person_id,
212 ARGUMENT2 => NULL,
213 ARGUMENT3 => p_admission_appl_number,
214 ARGUMENT4 => NULL,
215 ARGUMENT5 => NULL,
216 ARGUMENT6 => NULL,
217 ARGUMENT7 => NULL,
218 ARGUMENT8 => FND_PROFILE.VALUE('ORG_ID'),
219 ARGUMENT9 => CHR(0),
220 ARGUMENT10 => NULL,
221 ARGUMENT11 => NULL,
222 ARGUMENT12 => NULL,
223 ARGUMENT13 => NULL,
224 ARGUMENT14 => NULL,
225 ARGUMENT15 => NULL,
226 ARGUMENT16 => NULL,
227 ARGUMENT17 => NULL,
228 ARGUMENT18 => NULL,
229 ARGUMENT19 => NULL,
230 ARGUMENT20 => NULL,
231 ARGUMENT21 => NULL,
232 ARGUMENT22 => NULL,
233 ARGUMENT23 => NULL,
234 ARGUMENT24 => NULL,
235 ARGUMENT25 => NULL,
236 ARGUMENT26 => NULL,
237 ARGUMENT27 => NULL,
238 ARGUMENT28 => NULL,
239 ARGUMENT29 => NULL,
240 ARGUMENT30 => NULL,
241 ARGUMENT31 => NULL,
242 ARGUMENT32 => NULL,
243 ARGUMENT33 => NULL,
244 ARGUMENT34 => NULL,
245 ARGUMENT35 => NULL,
246 ARGUMENT36 => NULL,
247 ARGUMENT37 => NULL,
248 ARGUMENT38 => NULL,
249 ARGUMENT39 => NULL,
250 ARGUMENT40 => NULL,
251 ARGUMENT41 => NULL,
252 ARGUMENT42 => NULL,
253 ARGUMENT43 => NULL,
254 ARGUMENT44 => NULL,
255 ARGUMENT45 => NULL,
256 ARGUMENT46 => NULL,
257 ARGUMENT47 => NULL,
258 ARGUMENT48 => NULL,
259 ARGUMENT49 => NULL,
260 ARGUMENT50 => NULL,
261 ARGUMENT51 => NULL,
262 ARGUMENT52 => NULL,
263 ARGUMENT53 => NULL,
264 ARGUMENT54 => NULL,
265 ARGUMENT55 => NULL,
266 ARGUMENT56 => NULL,
267 ARGUMENT57 => NULL,
268 ARGUMENT58 => NULL,
269 ARGUMENT59 => NULL,
270 ARGUMENT60 => NULL,
271 ARGUMENT61 => NULL,
272 ARGUMENT62 => NULL,
273 ARGUMENT63 => NULL,
274 ARGUMENT64 => NULL,
275 ARGUMENT65 => NULL,
276 ARGUMENT66 => NULL,
277 ARGUMENT67 => NULL,
278 ARGUMENT68 => NULL,
279 ARGUMENT69 => NULL,
280 ARGUMENT70 => NULL,
281 ARGUMENT71 => NULL,
282 ARGUMENT72 => NULL,
283 ARGUMENT73 => NULL,
284 ARGUMENT74 => NULL,
285 ARGUMENT75 => NULL,
286 ARGUMENT76 => NULL,
287 ARGUMENT77 => NULL,
288 ARGUMENT78 => NULL,
289 ARGUMENT79 => NULL,
290 ARGUMENT80 => NULL,
291 ARGUMENT81 => NULL,
292 ARGUMENT82 => NULL,
293 ARGUMENT83 => NULL,
294 ARGUMENT84 => NULL,
295 ARGUMENT85 => NULL,
296 ARGUMENT86 => NULL,
297 ARGUMENT87 => NULL,
298 ARGUMENT88 => NULL,
299 ARGUMENT89 => NULL,
300 ARGUMENT90 => NULL,
301 ARGUMENT91 => NULL,
302 ARGUMENT92 => NULL,
303 ARGUMENT93 => NULL,
304 ARGUMENT94 => NULL,
305 ARGUMENT95 => NULL,
306 ARGUMENT96 => NULL,
307 ARGUMENT97 => NULL,
308 ARGUMENT98 => NULL,
309 ARGUMENT99 => NULL,
310 ARGUMENT100 => NULL
311 );
312
313 IF l_request_id = 0 THEN
314 fnd_message.set_name('FND','CONC-REQUEST SUBMISSION FAILED');
315 igs_ge_msg_stack.add;
316 app_exception.raise_exception;
317 ELSE
318 fnd_message.set_name('FND','CONC-SUBMITTED REQUEST');
319 fnd_message.set_token('REQUEST_ID',IGS_GE_NUMBER.TO_CANN(l_request_id), FALSE);
320 igs_ge_msg_stack.add;
321 END IF;
322 END IF;
323
324 END create_enrollment_deposit;
325
326 PROCEDURE check_ad_code_classes_setup (
327 p_class_type IN igs_ad_code_classes.class%TYPE -- APPLICATION_FEES
328 , p_class IN igs_ad_code_classes.class%TYPE -- FEE_PAYMENT_METHODS, FEE_STATUS, FEE_TYPES
329 , p_std_sys_stat IN igs_ad_code_classes.system_status%TYPE -- SYS_FEE_STATUS, SYS_FEE_TYPE, SYS_FEE_PAY_METHOD
330 , p_sys_stat IN igs_ad_code_classes.system_status%TYPE -- PAID, ENROLL_DEPOSIT, CHECK
331 , p_code_id OUT NOCOPY igs_ad_code_classes.code_id%TYPE -- Return value: CODE_ID
332 ) IS
333
334 /******************************************************************
335 Created By: Navin Sinha
336 Date Created By: 08-Oct-2002
337 Purpose: BUG NO : 2602077 : HQ: release of build for td sf integrarion.
338 Procedure for Performing mandatory Admissions system setup check.
339 Known limitations,enhancements,remarks:
340 Change History
341 Who When What
342 pathipat 06-Jan-2003 Bug: 2734574 - Removed exception section
343 ******************************************************************/
344
345 -- Cursor to check set for igs_ad_code_classes are done on client site.
346 CURSOR c_ad_cd_class IS
347 SELECT code_id
348 FROM igs_ad_code_classes
349 WHERE class = p_class
350 AND system_status = p_sys_stat
351 AND NVL(system_default, 'N') = 'Y'
352 AND closed_ind = 'N'
353 AND CLASS_TYPE_CODE='ADM_CODE_CLASSES';
354
355 -- Cursor to get the meaning for lookup code
356 CURSOR c_lkup_cd_mean(cp_lookup_type igs_lookups_view.lookup_type%TYPE, cp_lookup_code igs_lookups_view.lookup_code%TYPE) IS
357 SELECT meaning
358 FROM igs_lookups_view
359 WHERE lookup_type = cp_lookup_type
360 AND lookup_code = cp_lookup_code;
361
362 l_class_meaning igs_lookups_view.meaning%TYPE;
363 l_sys_stat_meaning igs_lookups_view.meaning%TYPE;
364 BEGIN
365 OPEN c_ad_cd_class;
366 FETCH c_ad_cd_class INTO p_code_id;
367 IF c_ad_cd_class%NOTFOUND THEN
368 CLOSE c_ad_cd_class;
369
370 -- Get the value for message token CLASS_MEANING
371 OPEN c_lkup_cd_mean(p_class_type, p_class);
372 FETCH c_lkup_cd_mean INTO l_class_meaning;
373 CLOSE c_lkup_cd_mean;
374
375 -- Get the value for message token SYS_STAT_MEANING
376 OPEN c_lkup_cd_mean(p_std_sys_stat, p_sys_stat);
377 FETCH c_lkup_cd_mean INTO l_sys_stat_meaning;
378 CLOSE c_lkup_cd_mean;
379
380 fnd_message.set_name('IGS','IGS_AD_INCOMP_SETUP_CD_CLASS');
381 fnd_message.set_token('CLASS_MEANING', l_class_meaning);
382 fnd_message.set_token('SYS_STAT_MEANING', l_sys_stat_meaning);
383 igs_ge_msg_stack.add;
384 app_exception.raise_exception;
385 END IF;
386 CLOSE c_ad_cd_class;
387
388 END check_ad_code_classes_setup;
389
390 PROCEDURE update_igs_ad_app_req (
391 p_rowid IN VARCHAR2,
392 p_app_req_id IN NUMBER,
393 p_person_id IN NUMBER,
394 p_admission_appl_number IN NUMBER,
395 p_applicant_fee_type IN NUMBER,
396 p_applicant_fee_status IN NUMBER,
397 p_fee_date IN DATE,
398 p_fee_payment_method IN NUMBER,
399 p_fee_amount IN NUMBER,
400 p_reference_num IN VARCHAR2,
401 p_credit_card_code IN VARCHAR2,
402 p_credit_card_holder_name IN VARCHAR2,
403 p_credit_card_number IN VARCHAR2,
404 p_credit_card_expiration_date IN DATE,
405 p_rev_gl_ccid IN NUMBER,
406 p_cash_gl_ccid IN NUMBER,
407 p_rev_account_cd IN VARCHAR2,
408 p_cash_account_cd IN VARCHAR2,
409 p_posting_control_id IN NUMBER,
410 p_gl_date IN DATE,
411 p_gl_posted_date IN DATE,
412 p_credit_card_tangible_cd IN VARCHAR2,
413 p_credit_card_payee_cd IN VARCHAR2,
414 p_credit_card_status_code IN VARCHAR2,
415 p_mode IN VARCHAR2
416 ) AS
417 /*************************************************************
418 Created By: Navin Sinha
419 Date Created By: 01-Oct-2003
420 Purpose: BUG NO : 3160036 : OSSTST15: enrollment deposit fee error in sf gl interface process
421 New procedure to updare record in the table IGS_AD_APP_REQ.
422 Know limitations, enhancements or remarks
423 Change History
424 Who When What
425 (reverse chronological order - newest change first)
426 ***************************************************************/
427
428 BEGIN
429 igs_ad_gen_015.g_chk_ad_app_req := 'Y'; -- Based on the value of this variable , some of the validation wont be perform in igs_ad_app_req_pkg(bug#2901627 -- rghosh)
430
431 -- Update the fee payment record in the AD table
432 igs_ad_app_req_pkg.update_row (
433 x_rowid => p_rowid,
434 x_app_req_id => p_app_req_id,
435 x_person_id => p_person_id,
436 x_admission_appl_number => p_admission_appl_number,
437 x_applicant_fee_type => p_applicant_fee_type,
438 x_applicant_fee_status => p_applicant_fee_status,
439 x_fee_date => p_fee_date,
440 x_fee_payment_method => p_fee_payment_method,
441 x_fee_amount => p_fee_amount,
442 x_reference_num => p_reference_num,
443 x_credit_card_code => p_credit_card_code,
444 x_credit_card_holder_name => p_credit_card_holder_name,
445 x_credit_card_number => p_credit_card_number,
446 x_credit_card_expiration_date => p_credit_card_expiration_date,
447 x_rev_gl_ccid => p_rev_gl_ccid,
448 x_cash_gl_ccid => p_cash_gl_ccid,
449 x_rev_account_cd => p_rev_account_cd,
450 x_cash_account_cd => p_cash_account_cd,
451 x_posting_control_id => p_posting_control_id,
452 x_gl_date => p_gl_date,
453 x_gl_posted_date => p_gl_posted_date,
454 x_credit_card_tangible_cd => p_credit_card_tangible_cd,
455 x_credit_card_payee_cd => p_credit_card_payee_cd,
456 x_credit_card_status_code => p_credit_card_status_code,
457 x_mode => p_mode
458 );
459
460 igs_ad_gen_015.g_chk_ad_app_req := 'N';
461 END update_igs_ad_app_req;
462
463 END igs_ad_gen_015;