1 PACKAGE BODY igs_fi_prc_impclc_anc_chgs AS
2 /* $Header: IGSFI52B.pls 120.4 2006/05/15 07:44:51 sapanigr ship $ */
3
4 /**********************************************************************************************************
5 Who When What
6 sapanigr 03-May-2006 Enh#3924836 Precision Issue. Modified finp_imp_calc_anc_charges
7 sapanigr 15-Feb-2006 Bug#5018036. Cursor cur_person_id in function finp_validate_input_data replaced by function call.
8 sapanigr 14-Feb-2006 Bug#5018036. Cursor Cursor cur_person_id in function finp_validate_input_data modified for R12 Repository tuning.
9 svuppala
10 svuppala 04-AUG-2005 Enh 3392095 - Tution Waivers build
11 Impact of Charges API version Number change
12 Modified igs_fi_charges_api_pvt.create_charge - version 2.0 and x_waiver_amount
13 pathipat 30-Sep-2003 Bug 3166888 - Modified finp_imp_calc_anc_charges() - modified cursor cur_inv_int
14 vvutukur 20-Jun-2003 Enh#2777404.Modified finp_validate_input_data,finp_imp_calc_anc_charges.
15 shtatiko 29-APR-2003 Enh# 2831569, Modified check_person_id, finp_imp_calc_anc_charges and finp_validate_input_data
16 vvutukur 23-Jan-20033 Bug#2750566.Modifications done in finp_imp_calc_anc_charges.
17 vvutukur 07-Jan-2003 Bug#2737714.Modified procedure finp_imp_calc_anc_charges.
18 pathipat 20-Nov-2002 Enh bug 2584986 - GL Interface build
19 1. Modified proc finp_imp_calc_anc_charges
20 2. Removed lookup_desc, instead used generic func to get lookup description
21 3. Modified finp_validate_input_data - added check for status in cur_ftci
22 vvutukur 24-Jul-02 Bug#2425767.Procedure finp_imp_calc_anc_charges modified for removing references to
23 obsoleted columns chg_rate and chg_elements.
24 sykrishn 03-JUL-02 Bug 2442163 - Procedure finp_imp_calc_anc_charges modified to insert sysdate to
25 transaction_dt in the table igs_fi_impchgs_lines table
26 Reference to anc_int table.transaction_dt is removed as it will never be present.
27 Removed transaction_dt from igs_fi_anc_int_pkg.update_row
28 Call to charges api changed to pass sysdate for invoice creation date instead of transaction_dt
29 sykrishn 10-JUL-02 Bug 2454128 - Procedure finp_imp_calc_anc_charges
30 Call to charges api changed to pass Fee Type Description for invoice description
31 (l_header_rec.p_invoice_desc) instead of null.
32 *************************************************************************************************************/
33
34 g_ancillary CONSTANT VARCHAR2(30) := 'ANCILLARY';
35 g_todo CONSTANT VARCHAR2(30) := 'TODO';
36 g_success CONSTANT VARCHAR2(30) := 'SUCCESS';
37 g_error CONSTANT VARCHAR2(30) := 'ERROR';
38 g_active CONSTANT VARCHAR2(30) := 'ACTIVE';
39 g_v_cleared CONSTANT VARCHAR2(30) := 'CLEARED';
40 g_v_chgadj CONSTANT VARCHAR2(30) := 'CHGADJ';
41
42 l_rowid VARCHAR2(25) := NULL;
43
44 -- Added new parameter p_person_id as part of Enh# 2831569.
45 FUNCTION Check_Person_Id(p_person_id IN igs_pe_person.person_id%TYPE,
46 p_person_id_type IN igs_pe_person.Person_Id_Type%TYPE,
47 p_api_person_id IN igs_pe_person.Api_Person_Id%TYPE) RETURN BOOLEAN;
48
49 FUNCTION Get_Person_Number(p_person_id IN igs_pe_person.Person_Id%TYPE) RETURN IGS_PE_PERSON.Person_Number%TYPE;
50
51 FUNCTION finp_get_anc_rate(p_fee_cal_type IN igs_fi_anc_rates.Fee_Cal_Type%TYPE,
52 p_fee_ci_sequence_number IN igs_fi_anc_rates.Fee_Ci_Sequence_Number%TYPE,
53 p_fee_type IN igs_fi_anc_rates.Fee_Type%TYPE,
54 p_ancillary_attribute1 IN igs_fi_anc_rates.ancillary_attribute1%TYPE ,
55 p_ancillary_attribute2 IN igs_fi_anc_rates.ancillary_attribute2%TYPE ,
56 p_ancillary_attribute3 IN igs_fi_anc_rates.ancillary_attribute3%TYPE ,
57 p_ancillary_attribute4 IN igs_fi_anc_rates.ancillary_attribute4%TYPE ,
58 p_ancillary_attribute5 IN igs_fi_anc_rates.ancillary_attribute5%TYPE ,
59 p_ancillary_attribute6 IN igs_fi_anc_rates.ancillary_attribute6%TYPE ,
60 p_ancillary_attribute7 IN igs_fi_anc_rates.ancillary_attribute7%TYPE ,
61 p_ancillary_attribute8 IN igs_fi_anc_rates.ancillary_attribute8%TYPE ,
62 p_ancillary_attribute9 IN igs_fi_anc_rates.ancillary_attribute9%TYPE ,
63 p_ancillary_attribute10 IN igs_fi_anc_rates.ancillary_attribute10%TYPE ,
64 p_ancillary_attribute11 IN igs_fi_anc_rates.ancillary_attribute11%TYPE ,
65 p_ancillary_attribute12 IN igs_fi_anc_rates.ancillary_attribute12%TYPE ,
66 p_ancillary_attribute13 IN igs_fi_anc_rates.ancillary_attribute13%TYPE ,
67 p_ancillary_attribute14 IN igs_fi_anc_rates.ancillary_attribute14%TYPE ,
68 p_ancillary_attribute15 IN igs_fi_anc_rates.ancillary_attribute15%TYPE ,
69 p_ancillary_chg_rate OUT NOCOPY igs_fi_anc_rates.Ancillary_Chg_Rate%TYPE) RETURN BOOLEAN AS
70
71 /***********************************************************************************************
72
73 Created By: Amit Gairola
74
75 Date Created By: 12-04-2001
76
77 Purpose: This function gets the Ancillary Rate based on the Ancillary Segments
78 and the fee type calendar instance passed
79
80 Known limitations,enhancements,remarks:
81
82 Change History
83
84 Who When What
85
86 ********************************************************************************************** */
87 l_anc_rate igs_fi_anc_rates.Ancillary_Chg_Rate%TYPE := 0;
88 l_bool BOOLEAN := FALSE;
89
90 -- Cursor for fetching the Ancillary Rate based on the Fee Type, Fee Calendar
91 -- and the Ancillary Attributes Passed
92 CURSOR cur_anc_rates(cp_fee_cal_type igs_fi_anc_rates.Fee_Cal_Type%TYPE,
93 cp_fee_ci_sequence_number igs_fi_anc_rates.Fee_Ci_Sequence_Number%TYPE,
94 cp_fee_type igs_fi_anc_rates.Fee_Type%TYPE,
95 cp_ancillary_attribute1 igs_fi_anc_rates.Ancillary_Attribute1%TYPE ,
96 cp_ancillary_attribute2 igs_fi_anc_rates.Ancillary_Attribute2%TYPE ,
97 cp_ancillary_attribute3 igs_fi_anc_rates.Ancillary_Attribute3%TYPE ,
98 cp_ancillary_attribute4 igs_fi_anc_rates.Ancillary_Attribute4%TYPE ,
99 cp_ancillary_attribute5 igs_fi_anc_rates.Ancillary_Attribute5%TYPE ,
100 cp_ancillary_attribute6 igs_fi_anc_rates.Ancillary_Attribute6%TYPE ,
101 cp_ancillary_attribute7 igs_fi_anc_rates.Ancillary_Attribute7%TYPE ,
102 cp_ancillary_attribute8 igs_fi_anc_rates.Ancillary_Attribute8%TYPE ,
103 cp_ancillary_attribute9 igs_fi_anc_rates.Ancillary_Attribute9%TYPE ,
104 cp_ancillary_attribute10 igs_fi_anc_rates.Ancillary_Attribute10%TYPE ,
105 cp_ancillary_attribute11 igs_fi_anc_rates.Ancillary_Attribute11%TYPE ,
106 cp_ancillary_attribute12 igs_fi_anc_rates.Ancillary_Attribute12%TYPE ,
107 cp_ancillary_attribute13 igs_fi_anc_rates.Ancillary_Attribute13%TYPE ,
108 cp_ancillary_attribute14 igs_fi_anc_rates.Ancillary_Attribute14%TYPE ,
109 cp_ancillary_attribute15 igs_fi_anc_rates.Ancillary_Attribute15%TYPE ) IS
110 SELECT ancillary_chg_rate
111 FROM igs_fi_anc_rates
112 WHERE fee_cal_type = cp_fee_cal_type
113 AND fee_ci_sequence_number = cp_fee_ci_sequence_number
114 AND fee_type = cp_fee_type
115 AND NVL(enabled_flag,'N') = 'Y'
116 AND ((ancillary_attribute1 = cp_ancillary_attribute1)
117 OR ((cp_ancillary_attribute1 IS NULL) AND (ancillary_attribute1 IS NULL)))
118 AND ((ancillary_attribute2 = cp_ancillary_attribute2)
119 OR ((cp_ancillary_attribute2 IS NULL)AND (ancillary_attribute2 IS NULL)))
120 AND ((ancillary_attribute3 = cp_ancillary_attribute3)
121 OR ((cp_ancillary_attribute3 IS NULL) AND (ancillary_attribute3 IS NULL)))
122 AND ((ancillary_attribute4 = cp_ancillary_attribute4)
123 OR ((cp_ancillary_attribute4 IS NULL) AND (ancillary_attribute4 IS NULL)))
124 AND ((ancillary_attribute5 = cp_ancillary_attribute5)
125 OR ((cp_ancillary_attribute5 IS NULL) AND (ancillary_attribute5 IS NULL)))
126 AND ((ancillary_attribute6 = cp_ancillary_attribute6)
127 OR ((cp_ancillary_attribute6 IS NULL) AND (ancillary_attribute6 IS NULL)))
128 AND ((ancillary_attribute7 = cp_ancillary_attribute7)
129 OR ((cp_ancillary_attribute7 IS NULL) AND (ancillary_attribute7 IS NULL)))
130 AND ((ancillary_attribute8 = cp_ancillary_attribute8)
131 OR ((cp_ancillary_attribute8 IS NULL) AND (ancillary_attribute8 IS NULL)))
132 AND ((ancillary_attribute9 = cp_ancillary_attribute9)
133 OR ((cp_ancillary_attribute9 IS NULL) AND (ancillary_attribute9 IS NULL)))
134 AND ((ancillary_attribute10 = cp_ancillary_attribute10)
135 OR ((cp_ancillary_attribute10 IS NULL) AND (ancillary_attribute10 IS NULL)))
136 AND ((ancillary_attribute11 = cp_ancillary_attribute11)
137 OR ((cp_ancillary_attribute11 IS NULL) AND (ancillary_attribute11 IS NULL)))
138 AND ((ancillary_attribute12 = cp_ancillary_attribute12)
139 OR ((cp_ancillary_attribute12 IS NULL) AND (ancillary_attribute12 IS NULL)))
140 AND ((ancillary_attribute13 = cp_ancillary_attribute13)
141 OR ((cp_ancillary_attribute13 IS NULL) AND (ancillary_attribute13 IS NULL)))
142 AND ((ancillary_attribute14 = cp_ancillary_attribute14)
143 OR ((cp_ancillary_attribute14 IS NULL) AND (ancillary_attribute14 IS NULL)))
144 AND ((ancillary_attribute15 = cp_ancillary_attribute15)
145 OR ((cp_ancillary_attribute15 IS NULL) AND (ancillary_attribute15 IS NULL)));
146 BEGIN
147
148 -- Fetch the Ancillary Rate from the Ancillary Rates table
149 OPEN cur_anc_rates(p_fee_cal_type,
150 p_fee_ci_sequence_number,
151 p_fee_type,
152 p_ancillary_attribute1,
153 p_ancillary_attribute2,
154 p_ancillary_attribute3,
155 p_ancillary_attribute4,
156 p_ancillary_attribute5,
157 p_ancillary_attribute6,
158 p_ancillary_attribute7,
159 p_ancillary_attribute8,
160 p_ancillary_attribute9,
161 p_ancillary_attribute10,
162 p_ancillary_attribute11,
163 p_ancillary_attribute12,
164 p_ancillary_attribute13,
165 p_ancillary_attribute14,
166 p_ancillary_attribute15);
167 FETCH cur_anc_rates INTO l_anc_rate;
168
169 -- If the Ancillary Rate is not found, then
170 IF cur_anc_rates%NOTFOUND THEN
171
172 -- Set the Ancillary Rate local variable to 0 and the boolean variable to False
173 l_anc_rate := 0;
174 l_bool := FALSE;
175 ELSE
176
177 -- Else set the Boolean Variable to TRUE
178 l_bool := TRUE;
179 END IF;
180 CLOSE cur_anc_rates;
181
182 -- Assign the Ancillary Rate fetched to the Out NOCOPY variable of the function
183 -- and return the boolean variable
184 p_ancillary_chg_rate := l_anc_rate;
185 RETURN l_bool;
186 END finp_get_anc_rate;
187
188
189 FUNCTION finp_validate_input_data(p_fee_cal_type IN igs_fi_anc_rates.Fee_Cal_Type%TYPE,
190 p_fee_ci_sequence_number IN igs_fi_anc_rates.Fee_Ci_Sequence_Number%TYPE,
191 p_fee_type IN igs_fi_anc_rates.Fee_Type%TYPE,
192 p_person_id IN igs_pe_person.Person_Id%TYPE ,
193 p_person_id_type IN igs_pe_person.Person_Id_Type%TYPE ,
194 p_api_person_id IN igs_pe_person.Api_Person_Id%TYPE ,
195 p_err_msg_name OUT NOCOPY VARCHAR2) RETURN BOOLEAN AS
196 /***********************************************************************************************
197
198 Created By: Amit Gairola
199
200 Date Created By: 12-04-2001
201
202 Purpose: This function validates the Input Data basde on the fee type calendar instance
203 and the Person details passed
204
205 Known limitations,enhancements,remarks:
206
207 Change History
208
209 Who When What
210 sapanigr 15-Feb-2006 Bug#5018036. Cursor cur_person_id replaced by call to function igs_en_gen_007.enrp_get_student_ind
211 sapanigr 14-Feb-2006 Bug#5018036. Cursor cur_person_id modified to take values from base tables directly.
212 vvutukur 20-Jun-2003 Enh#2777404.Modified cursor cur_fee_type to exclude closed fee types.
213 shtatiko 29-APR-2003 Enh# 2831569, Removed cursor cur_api_pers_id as its not used anywhere.
214 Added p_person_id in call to check_person_id
215 pathipat 20-Nov-2002 Enh bug 2584986 - Modified cursor cur_ftci - added check for active status
216 of the ftci.
217 ********************************************************************************************** */
218
219 l_temp VARCHAR2(1);
220
221 -- Cursor for validating whether the Fee Calendar is a valid Calendar
222 CURSOR cur_cal_type(cp_cal_type igs_fi_anc_rates.fee_cal_type%TYPE) IS
223 SELECT 'x'
224 FROM igs_ca_type
225 WHERE cal_type = cp_cal_type;
226
227 -- Cursor for validating the Calendar Instance
228 CURSOR cur_cal_inst(cp_cal_type igs_fi_anc_rates.Fee_Cal_Type%TYPE,
229 cp_ci_sequence_number igs_fi_anc_rates.Fee_Ci_Sequence_Number%TYPE) IS
230 SELECT 'x'
231 FROM igs_ca_inst
232 WHERE cal_type = cp_cal_type
233 AND sequence_number = cp_ci_sequence_number;
234
235 -- Cursor for validating whether the Fee Type is an active fee type
236 -- and the System fee Type is Ancillary
237 CURSOR cur_fee_type(cp_fee_type igs_fi_anc_rates.fee_type%TYPE) IS
238 SELECT 'x'
239 FROM igs_fi_fee_type
240 WHERE fee_type = cp_fee_type
241 AND s_fee_type = g_ancillary
242 AND NVL(closed_ind,'N') = 'N';
243
244 -- Cursor for validating the Fee Type Calendar Instance
245 CURSOR cur_ftci(cp_cal_type igs_fi_anc_rates.Fee_Cal_Type%TYPE,
246 cp_ci_sequence_number igs_fi_anc_rates.Fee_Ci_Sequence_Number%TYPE,
247 cp_fee_type igs_fi_anc_rates.Fee_Type%TYPE) IS
248 SELECT 'x'
249 FROM igs_fi_f_typ_ca_inst ftci, igs_fi_fee_str_stat fss
250 WHERE ftci.fee_cal_type = cp_cal_type
251 AND ftci.fee_ci_sequence_number = cp_ci_sequence_number
252 AND ftci.fee_type = cp_fee_type
253 AND fss.fee_structure_status = ftci.fee_type_ci_status
254 AND fss.s_fee_structure_status = g_active;
255
256 BEGIN
257
258 -- Validate the Calendar
259 OPEN cur_cal_type(p_fee_cal_type);
260 FETCH cur_cal_type INTO l_temp;
261
262 -- If the Calendar is not valid then
263 IF cur_cal_type%NOTFOUND THEN
264
265 -- Return the Error Message and the Function returns False
266 CLOSE cur_cal_type;
267 p_err_msg_name := 'IGS_FI_FEE_CAL_NOTFOUND';
268 RETURN FALSE;
269 END IF;
270 CLOSE cur_cal_type;
271
272 -- Validate the Calendar instance
273 OPEN cur_cal_inst(p_fee_cal_type,
274 p_fee_ci_sequence_number);
275 FETCH cur_cal_inst INTO l_temp;
276
277 -- If the Calendar Instance is not valid then
278 IF cur_cal_inst%NOTFOUND THEN
279 -- Return the Error Message and the Function returns False
280 CLOSE cur_cal_inst;
281 p_err_msg_name := 'IGS_FI_FEE_CAL_INST_NOTFOUND';
282 RETURN FALSE;
283 END IF;
284 CLOSE cur_cal_inst;
285
286 -- Validate the Fee Type
287 OPEN cur_fee_type(p_fee_type);
288 FETCH cur_fee_type INTO l_temp;
289
290 -- If the Fee Type is not valid, then
291 IF cur_fee_type%NOTFOUND THEN
292 -- Return the Error Message and the Function returns False
293 CLOSE cur_fee_type;
294 p_err_msg_name := 'IGS_FI_NO_FEE_TYPE';
295 RETURN FALSE;
296 END IF;
297 CLOSE cur_fee_type;
298
299 -- Validate the Fee Type Calendar Instance
300 OPEN cur_ftci(p_fee_cal_type,
301 p_fee_ci_sequence_number,
302 p_fee_type);
303 FETCH cur_ftci INTO l_temp;
304
305 -- If the Fee Type Calendar Instance is Not Valid then
306 IF cur_ftci%NOTFOUND THEN
307
308 -- Return the Error Message and the Function returns False
309 CLOSE cur_ftci;
310 p_err_msg_name := 'IGS_FI_FTCI_NOTFOUND';
311 RETURN FALSE;
312 END IF;
313 CLOSE cur_ftci;
314
315 -- If the Person Id is not null then
316 IF (p_person_id IS NOT NULL) THEN
317
318 -- Validate if the Person is a Student.
319 IF NVL(igs_en_gen_007.enrp_get_student_ind(p_person_id),'N')<>'Y' THEN
320 -- If not a valid Student, then return the Error Message and the Function returns False.
321 p_err_msg_name := 'IGS_FI_PERSON_NOTFOUND';
322 RETURN FALSE;
323 END IF;
324 END IF;
325
326 -- If the Person Id type and the Alternate Person Id are not valid
327 IF (( p_person_id_type IS NOT NULL) AND (p_api_person_id IS NOT NULL)) THEN
328
329 -- Validate the Person Id Type and the Alternate Person Id
330 IF NOT Check_person_Id(p_person_id,
331 p_person_id_type,
332 p_api_person_id) THEN
333 p_err_msg_name := 'IGS_FI_ALT_PRS_NOTFOUND';
334 RETURN FALSE;
335 END IF;
336 END IF;
337
338 p_err_msg_name := NULL;
339
340 RETURN TRUE;
341 END finp_validate_input_data;
342
343 PROCEDURE finp_imp_calc_anc_charges(errbuf OUT NOCOPY VARCHAR2,
344 retcode OUT NOCOPY NUMBER,
345 p_person_id IN igs_pe_person.Person_Id%TYPE ,
346 p_person_id_type IN igs_pe_person.Person_Id_Type%TYPE ,
347 p_api_person_id IN igs_pe_person.Api_Person_Id%TYPE ,
348 p_fee_cal_type IN igs_fi_anc_rates.Fee_Cal_Type%TYPE,
349 p_fee_ci_sequence_number IN igs_fi_anc_rates.Fee_Ci_Sequence_Number%TYPE,
350 p_fee_type IN igs_fi_anc_rates.Fee_Type%TYPE ,
351 p_org_id IN NUMBER) AS
352 /***********************************************************************************************
353
354 Created By: Amit Gairola
355
356 Date Created By: 12-04-2001
357
358 Purpose: This procedure imports the ancillary charges from the Ancillary Interface tables
359 based on the fee type calendar instance and the Person details passed
360
361 Known limitations,enhancements,remarks:
362
363 Change History
364
365 Who When What
366 sapanigr 03-May-2006 Enh#3924836 Precision Issue. Amount values being inserted into igs_fi_impchgs_lines
367 are now rounded off to currency precision
368 svuppala 04-AUG-2005 Enh 3392095 - Tution Waivers build
369 Impact of Charges API version Number change
370 Modified igs_fi_charges_api_pvt.create_charge - version 2.0 and x_waiver_amount
371 pathipat 30-Sep-2003 Bug 3166888 - Modified cur_inv_int to sum invoice_amount instead of invoice_amount_due
372 and added 'not exists' clause
373 vvutukur 20-Jun-2003 Enh#2777404.Modified the logic such that all the error messages pertaining to an ancillary record
374 being processed from interface table are logged in the log file and error_msg field in the interface table
375 is updated with the concatenated error message texts.
376 shtatiko 29-APR-2003 Enh# 2831569, Added check for Manage Accounts System Option. If its value is NULL then
377 this process cannot be run.
378 vvutukur 23-Jan-20033 Bug#2750566.Modified the code to update the error_msg column in Interface record with appropriate message text.
379 vvutukur 06-Jan-2003 Bug#2737714.Used fnd_message.set_encoded to encode the message after charges api call,if charges api returns an error.
380 Also shown the no. of successfully imported records instead of showing no. of records processsed from interface table.
381 pathipat 20-NOV-2002 Enh#2584986 - GL Interface build
382 1. Removed override account columns from calls to insert_row of IGS_FI_IMP_CHGS and
383 IGS_FI_IMPCHGS_LINES and update_row of IGS_FI_ANC_INT.
384 Also removed the corresponding validations and local variables.
385 2. Removed ext_attribute columns from calls to insert_row of IGS_FI_IMPCHGS_LINES
386 3. Removed cursor cur_currency and its usage. Derived local currency using generic
387 function igs_fi_gen_gl.finp_get_cur()
388 4. Passed sysdate to p_d_gl_date in call to charges_api. Passed null to override account
389 codes before calling charges_api.
390 saraskhi 13-sep-2002 Enh#2564643, removed the reference of subaccount
391 vvutukur 24-Jul-2002 Bug#2425767.Removed references to obsoleted columns chg_rate,chg_elements from
392 call to igs_fi_impchgs_lines.insert_row procedure.
393 sykrishn 10-JUL-02 Bug 2454128 - Procedure finp_imp_calc_anc_charges
394 Call to charges api changed to pass Fee Type Description for invoice description
395 (l_header_rec.p_invoice_desc) instead of null.
396 jbegum 12-Jun-02 Bug#2400189 - Added a local valriable l_rec_cntr to count the number of
397 records processed and log that in the log file
398 agairola 04-Jun-2002 Bug 2395663 - Modified the TBH call for the IGS_FI_IMPCHGS_LINES_PKG
399 SYKRISHN 19-APR-2002 Bug 2324088 - Introduced Desc Flex Field Validations and CCID validations.
400 smadathi 27-Feb-2002 Bug 2238413. Reduced selection list for
401 rec installed flag to 'Y' and 'N'.Removed
402 reference of rec installed flag = 'E'.
403 sarakshi 16-jan-2002 Modified the logic of fetching subaccount_id as a part of bug:2175865
404 ********************************************************************************************** */
405
406 -- Variables of type VARCHAR2
407 l_error_msg VARCHAR2(2000);
408 l_person_id igs_pe_alt_pers_id.pe_person_id%TYPE;
409 l_rec_installed igs_fi_control.rec_installed%TYPE;
410 l_exception_flag VARCHAR2(1);
411 l_message VARCHAR2(2000);
412 l_person_number igs_pe_person.person_number%TYPE;
413
414 -- Variables of type NUMBER
415 l_diff_amount igs_fi_inv_int.invoice_amount_due%TYPE;
416 l_import_charges_id igs_fi_imp_chgs.import_charges_id%TYPE;
417 l_impchgs_lines_id igs_fi_impchgs_lines.impchg_lines_id%TYPE;
418 l_ancillary_chg_rate igs_fi_anc_rates.ancillary_chg_rate%TYPE;
419 l_rec_cntr NUMBER(10);
420
421 -- Variables of type BOOLEAN
422 l_validate BOOLEAN;
423 l_anc_rate_out BOOLEAN;
424
425 -- New variable defined as part of 2324088
426 l_dff_validate BOOLEAN;
427
428 -- Variables of type DATE
429 l_effective_dt igs_fi_anc_int.Effective_Dt%TYPE;
430
431 -- Variables for Charges API Integration
432 l_header_rec igs_fi_charges_api_pvt.header_rec_type;
433 l_line_tbl igs_fi_charges_api_pvt.line_tbl_type;
434 l_line_tbl_dummy igs_fi_charges_api_pvt.line_tbl_type;
435 l_line_id_tbl igs_fi_charges_api_pvt.line_id_tbl_type;
436 l_invoice_id igs_fi_inv_int.Invoice_Id%TYPE;
437 l_msg_count NUMBER(3);
438 l_msg_data VARCHAR2(2000);
439 l_return_status VARCHAR2(1);
440 l_prev_amount igs_fi_inv_int.invoice_amount%TYPE;
441 l_var NUMBER(3);
442 l_fee_desc igs_fi_fee_type.description%TYPE;
443
444 l_v_local_currency igs_fi_control.currency_cd%TYPE;
445 l_n_curr_cd igs_fi_control.currency_cd%TYPE;
446 l_v_curr_desc fnd_currencies_tl.name%TYPE;
447
448 l_appl_name VARCHAR2(5);
449 l_msg_name fnd_new_messages.message_name%TYPE;
450
451 l_v_message_name fnd_new_messages.message_name%TYPE;
452 l_msg fnd_new_messages.message_text%TYPE;
453 l_v_manage_accounts igs_fi_control_all.manage_accounts%TYPE;
454 l_exception EXCEPTION;
455
456 l_n_waiver_amount NUMBER;
457 -- Cursor for getting the todo records from the Ancillary Interface table
458 CURSOR cur_anc_int(cp_person_id IGS_PE_PERSON.Person_Id%TYPE,
459 cp_person_id_type IGS_PE_PERSON.Person_Id_Type%TYPE,
460 cp_api_person_id IGS_PE_PERSON.Api_Person_Id%TYPE,
461 cp_fee_cal_type IGS_FI_ANC_RATES.Fee_Cal_Type%TYPE,
462 cp_fee_ci_sequence_number IGS_FI_ANC_RATES.Fee_Ci_Sequence_Number%TYPE,
463 cp_fee_type IGS_FI_ANC_RATES.Fee_Type%TYPE) IS
464 SELECT rowid,
465 IGS_FI_ANC_INT.*
466 FROM IGS_FI_ANC_INT
467 WHERE ((fee_cal_type = cp_fee_cal_type)
468 OR (cp_fee_cal_type IS NULL))
469 AND ((fee_ci_sequence_number = cp_fee_ci_sequence_number)
470 OR (cp_fee_ci_sequence_number IS NULL))
471 AND status = g_todo
472 AND ((person_id = cp_person_id)
473 OR (cp_person_id IS NULL))
474 AND ((person_id_type = cp_person_id_type)
475 OR (cp_person_id_type IS NULL))
476 AND ((api_person_id = cp_api_person_id)
477 OR (cp_api_person_id IS NULL))
478 AND ((fee_type = cp_fee_type)
479 OR (cp_fee_type IS NULL));
480
481 -- Cursor for getting the Person Id from Alternate Person Id table based on the
482 -- Person Id Type and Alternate Person Id
483 CURSOR cur_person(cp_person_id_type IGS_PE_PERSON.Person_Id_Type%TYPE,
484 cp_api_person_id IGS_PE_PERSON.Api_Person_Id%TYPE) IS
485 SELECT pe_person_id
486 FROM igs_pe_alt_pers_id
487 WHERE person_id_type = cp_person_id_type
488 AND api_person_id = cp_api_person_id;
489
490 -- Cursor for checking whether the record exists in the Import Charges table
491 CURSOR cur_impchgs(cp_person_id IGS_PE_PERSON.Person_Id%TYPE,
492 cp_fee_cal_type IGS_FI_F_TYP_CA_INST.Fee_Cal_Type%TYPE,
493 cp_fee_ci_sequence_number IGS_FI_F_TYP_CA_INST.Fee_Ci_Sequence_Number%TYPE,
494 cp_fee_type IGS_FI_F_TYP_CA_INST.Fee_Type%TYPE,
495 cp_transaction_type VARCHAR2) IS
496 SELECT import_charges_id
497 FROM igs_fi_imp_chgs
498 WHERE person_id = cp_person_id
499 AND fee_cal_type = cp_fee_cal_type
500 AND fee_ci_sequence_number = cp_fee_ci_sequence_number
501 AND fee_type = cp_fee_type
502 AND transaction_type = cp_transaction_type;
503
504 -- Cursor for summing up the values from the Invoice Interface table
505 -- Charges that were reversed or declined are not to be
506 -- considered while summing the invoice_amount values.
507 CURSOR cur_inv_int(cp_person_id IGS_PE_PERSON.Person_Id%TYPE,
508 cp_fee_cal_type IGS_FI_F_TYP_CA_INST.Fee_Cal_Type%TYPE,
509 cp_fee_ci_sequence_number IGS_FI_F_TYP_CA_INST.Fee_Ci_Sequence_Number%TYPE,
510 cp_fee_type IGS_FI_F_TYP_CA_INST.Fee_Type%TYPE,
511 cp_transaction_type VARCHAR2) IS
512 SELECT SUM(invoice_amount)
513 FROM igs_fi_inv_int inv
514 WHERE inv.person_id = cp_person_id
515 AND inv.fee_cal_type = cp_fee_cal_type
516 AND inv.fee_ci_sequence_number = cp_fee_ci_sequence_number
517 AND inv.fee_type = cp_fee_type
518 AND inv.transaction_type = cp_transaction_type
519 AND NOT EXISTS( SELECT 'X'
520 FROM igs_fi_credits fc,
521 igs_fi_cr_types crt,
522 igs_fi_applications app
523 WHERE app.invoice_id = inv.invoice_id
524 AND app.credit_id = fc.credit_id
525 AND fc.status = g_v_cleared
526 AND fc.credit_type_id = crt.credit_type_id
527 AND crt.credit_class = g_v_chgadj
528 AND app.amount_applied = inv.invoice_amount);
529
530 -- Cursor for fetching the Fee Type description
531 CURSOR cur_fee(cp_fee_type IGS_FI_FEE_TYPE.Fee_Type%TYPE) IS
532 SELECT description
533 FROM igs_fi_fee_type
534 WHERE fee_type = cp_fee_type;
535
536 BEGIN
537
538 -- Set the Org Id
539 IGS_GE_GEN_003.Set_Org_Id(p_org_id);
540
541 l_rec_installed := IGS_FI_GEN_005.finp_get_receivables_inst;
542 -- Logging the Paramteres in the Log File
543
544 FND_MESSAGE.Set_Name('IGS','IGS_FI_ANC_LOG_PARM');
545 FND_FILE.Put_Line(FND_FILE.Log,FND_MESSAGE.Get);
546
547 IF p_person_id IS NOT NULL THEN
548 l_person_number := Get_Person_Number(p_person_id);
549 END IF;
550
551 -- Removed usage of lookup_desc, instead used generic function to get the lookup description
552 fnd_message.set_name('IGS', 'IGS_FI_IMP_CHGS_PARAMETER');
553 fnd_message.set_token('PARM_TYPE',igs_fi_gen_gl.get_lkp_meaning (p_v_lookup_type => 'IGS_FI_LOCKBOX',
554 p_v_lookup_code => 'PERSON')
555 );
556 fnd_message.set_token('PARM_CODE',l_person_number);
557 fnd_file.put_line(fnd_file.log,fnd_message.get);
558
559 fnd_message.set_name('IGS','IGS_FI_IMP_CHGS_PARAMETER');
560 fnd_message.set_token('PARM_TYPE',igs_fi_gen_gl.get_lkp_meaning (p_v_lookup_type => 'IGS_FI_LOCKBOX',
561 p_v_lookup_code => 'PERSON_ID_TYPE')
562 );
563 fnd_message.set_token('PARM_CODE',p_person_id_type);
564 fnd_file.put_line(fnd_file.log,fnd_message.get);
565
566 fnd_message.set_name('IGS','IGS_FI_IMP_CHGS_PARAMETER');
567 fnd_message.set_token('PARM_TYPE',igs_fi_gen_gl.get_lkp_meaning (p_v_lookup_type => 'IGS_FI_LOCKBOX',
568 p_v_lookup_code => 'ALTERNATE_PERSON_ID')
569 );
570 fnd_message.set_token('PARM_CODE',p_api_person_id);
571 fnd_file.put_line(fnd_file.log,fnd_message.get);
572
573 fnd_message.set_name('IGS','IGS_FI_IMP_CHGS_PARAMETER');
574 fnd_message.set_token('PARM_TYPE',igs_fi_gen_gl.get_lkp_meaning (p_v_lookup_type => 'IGS_FI_LOCKBOX',
575 p_v_lookup_code => 'FEE_TYPE')
576 );
577 fnd_message.set_token('PARM_CODE',p_fee_type);
578 fnd_file.put_line(fnd_file.log,fnd_message.get);
579
580 fnd_message.set_name('IGS','IGS_FI_IMP_CHGS_PARAMETER');
581 fnd_message.set_token('PARM_TYPE',igs_fi_gen_gl.get_lkp_meaning (p_v_lookup_type => 'IGS_FI_LOCKBOX',
582 p_v_lookup_code => 'FEE_CAL_TYPE')
583 );
584 fnd_message.set_token('PARM_CODE',p_fee_cal_type);
585 fnd_file.put_line(fnd_file.log,fnd_message.get);
586
587 -- Get the value of "Manage Accounts" System Option value.
588 -- If this value is NULL then this process should error out.
589 igs_fi_com_rec_interface.chk_manage_account ( p_v_manage_acc => l_v_manage_accounts,
590 p_v_message_name => l_v_message_name );
591 IF l_v_manage_accounts IS NULL THEN
592 fnd_message.set_name ( 'IGS', l_v_message_name );
593 fnd_file.put_line(fnd_file.log,fnd_message.get);
594 RAISE l_exception;
595 END IF;
596
597 -- If the Person Id is not null then
598 IF p_person_id IS NOT NULL THEN
599
600 -- Person Id Type and Alternate Person Id cannot be specified
601 -- If the Person Id Type and the Alternate Person Id are input to the process
602 -- then raise an error
603 IF ((p_person_id_type IS NOT NULL) OR (p_api_person_id IS NOT NULL)) THEN
604 FND_MESSAGE.Set_Name('IGS', 'IGS_FI_PERS_INFO_NOT_NULL');
605 FND_MESSAGE.Set_Token('PERSON_ID', l_person_number);
606 FND_MESSAGE.Set_Token('PERS_ID_TYPE', p_person_id_type);
607 FND_MESSAGE.Set_Token('API_PERS_ID', p_api_person_id);
608 l_message := FND_MESSAGE.Get;
609 FND_FILE.Put_Line(FND_FILE.Log, l_message);
610 APP_EXCEPTION.Raise_Exception;
611 END IF;
612 ELSE
613
614 -- If the Person Id is null
615 -- And the Person Id Type is NOT NULL and Api Person Id IS NULL
616 -- then log an error message and exit
617 IF ((p_person_id_type IS NOT NULL) AND (p_api_person_id IS NULL)) THEN
618 FND_MESSAGE.Set_Name('IGS','IGS_FI_API_PERS_ID_NULL');
619 FND_MESSAGE.Set_Token('PERS_ID_TYPE', p_person_id_type);
620 l_message := FND_MESSAGE.Get;
621 FND_FILE.Put_Line(FND_FILE.Log, l_message);
622 APP_EXCEPTION.Raise_Exception;
623
624 -- Else if the Person Id Type is NULL and the API Person Id is NOT NULL then
625 -- Log this as an error
626 ELSIF ((p_person_id_type IS NULL) AND (p_api_person_id IS NOT NULL)) THEN
627 FND_MESSAGE.Set_Name('IGS', 'IGS_FI_PERS_ID_TYPE_NULL');
628 FND_MESSAGE.Set_Token('API_PERS_ID', p_api_person_id);
629 l_message := FND_MESSAGE.Get;
630 FND_FILE.Put_Line(FND_FILE.Log, l_message);
631 APP_EXCEPTION.Raise_Exception;
632 END IF;
633 END IF;
634
635 -- Obtain the currency_cd setup in the system. (Added as part of bug 2584986)
636 igs_fi_gen_gl.finp_get_cur( p_v_currency_cd => l_n_curr_cd,
637 p_v_curr_desc => l_v_curr_desc,
638 p_v_message_name => l_v_message_name
639 );
640 IF l_v_message_name IS NOT NULL THEN
641 fnd_message.set_name('IGS',l_v_message_name);
642 fnd_file.put_line(fnd_file.Log,fnd_message.get);
643 app_exception.raise_exception;
644 ELSE
645 l_v_local_currency := l_n_curr_cd;
646 END IF;
647
648 l_rec_cntr := 0;
649 -- Loop through the Ancillary Interface records which have a status of TODO
650 FOR ancrec IN cur_anc_int(p_person_id,
651 p_person_id_type,
652 p_api_person_id,
653 p_fee_cal_type,
654 p_fee_ci_sequence_number,
655 p_fee_type) LOOP
656
657 -- IF the Person Id is not null then this person id should be used for
658 -- creating the records in the Import Charges table
659 -- Else the Person Id should be derived from the Person Id Type and
660 -- Alternate Person ID
661 IF ancrec.person_id IS NOT NULL THEN
662 l_person_id := ancrec.person_id;
663 ELSE
664
665 -- Get the Person Id based on the Person Id Type and Api Person Id
666 OPEN cur_person(ancrec.person_id_type,
667 ancrec.api_person_id);
668 FETCH cur_person INTO l_person_id;
669 CLOSE cur_person;
670 END IF;
671
672 ancrec.error_msg := NULL;
673
674 l_validate := TRUE;
675
676 l_person_number := Get_Person_Number(l_person_id);
677
678 -- Log the record Details
679 fnd_file.new_line(fnd_file.log);
680 FND_MESSAGE.Set_name('IGS','IGS_FI_ANC_REC_DTLS');
681 FND_FILE.Put_Line(FND_FILE.Log, FND_MESSAGE.Get);
682
683 FND_MESSAGE.Set_Name('IGS','IGS_FI_ANC_CHG_REC1');
684 FND_MESSAGE.Set_Token('TOKEN',l_person_number);
685 FND_FILE.Put_Line(FND_FILE.Log, FND_MESSAGE.Get);
686
687 FND_MESSAGE.Set_Name('IGS','IGS_FI_ANC_CHG_REC2');
688 FND_MESSAGE.Set_Token('TOKEN', ancrec.fee_cal_type);
689 FND_FILE.Put_Line(FND_FILE.Log, FND_MESSAGE.Get);
690
691 FND_MESSAGE.Set_Name('IGS','IGS_FI_ANC_CHG_REC3');
692 FND_MESSAGE.Set_Token('TOKEN', ancrec.fee_type);
693 FND_FILE.Put_Line(FND_FILE.Log, FND_MESSAGE.Get);
694
695 -- If the validation flag is set to N then
696 IF NVL(ancrec.validation_flag,'N') = 'N' THEN
697
698 -- Make a call to finp_validate_input_data for validating the input data
699 l_validate := finp_validate_input_data(p_fee_cal_type => ancrec.fee_cal_type,
700 p_fee_ci_sequence_number => ancrec.fee_ci_sequence_number,
701 p_fee_type => ancrec.fee_type,
702 p_person_id => l_person_id, -- Passed l_person_id instead of ancrec.person_id (Enh# 2831569)
703 p_person_id_type => ancrec.person_id_type,
704 p_api_person_id => ancrec.api_person_id,
705 p_err_msg_name => l_error_msg);
706 IF NOT l_validate THEN
707 fnd_message.set_name('IGS',l_error_msg);
708 l_msg := fnd_message.get;
709 fnd_file.put_line(fnd_file.log,l_msg);
710 ancrec.error_msg := l_msg;
711 ancrec.status := g_error;
712 END IF;
713
714 -- sykrishn Modifications due to 2324088
715 -- If the above validations are successful we want to check if the DFF is valid
716 -- If DFF is invalid then sets l_validate to FALSE so that no further processing happens
717 l_dff_validate := igs_ad_imp_018.validate_desc_flex (
718 p_attribute_category => ancrec.attribute_category,
719 p_attribute1 => ancrec.attribute1,
720 p_attribute2 => ancrec.attribute2,
721 p_attribute3 => ancrec.attribute3,
722 p_attribute4 => ancrec.attribute4,
723 p_attribute5 => ancrec.attribute5,
724 p_attribute6 => ancrec.attribute6,
725 p_attribute7 => ancrec.attribute7,
726 p_attribute8 => ancrec.attribute8,
727 p_attribute9 => ancrec.attribute9,
728 p_attribute10 => ancrec.attribute10,
729 p_attribute11 => ancrec.attribute11,
730 p_attribute12 => ancrec.attribute12,
731 p_attribute13 => ancrec.attribute13,
732 p_attribute14 => ancrec.attribute14,
733 p_attribute15 => ancrec.attribute15,
734 p_attribute16 => ancrec.attribute16,
735 p_attribute17 => ancrec.attribute17,
736 p_attribute18 => ancrec.attribute18,
737 p_attribute19 => ancrec.attribute19,
738 p_attribute20 => ancrec.attribute20,
739 p_desc_flex_name => 'IGS_FI_IMPCHGS_FLEX');
740
741 IF NOT l_dff_validate THEN
742 l_validate := FALSE;
743 ancrec.status := g_error;
744 fnd_message.set_name('IGS','IGS_AD_INVALID_DESC_FLEX');
745 l_msg := fnd_message.get;
746 fnd_file.put_line(fnd_file.log,l_msg);
747 ancrec.error_msg := ancrec.error_msg||'.'||l_msg;
748 END IF;
749 END IF;
750
751
752 -- make a call to the finp_get_anc_rate procedure
753 -- for getting the Ancillary Rate
754 l_anc_rate_out := finp_get_anc_rate( p_fee_cal_type => ancrec.fee_cal_type,
755 p_fee_ci_sequence_number => ancrec.fee_ci_sequence_number,
756 p_fee_type => ancrec.fee_type,
757 p_ancillary_attribute1 => ancrec.ancillary_attribute1,
758 p_ancillary_attribute2 => ancrec.ancillary_attribute2,
759 p_ancillary_attribute3 => ancrec.ancillary_attribute3,
760 p_ancillary_attribute4 => ancrec.ancillary_attribute4,
761 p_ancillary_attribute5 => ancrec.ancillary_attribute5,
762 p_ancillary_attribute6 => ancrec.ancillary_attribute6,
763 p_ancillary_attribute7 => ancrec.ancillary_attribute7,
764 p_ancillary_attribute8 => ancrec.ancillary_attribute8,
765 p_ancillary_attribute9 => ancrec.ancillary_attribute9,
766 p_ancillary_attribute10 => ancrec.ancillary_attribute10,
767 p_ancillary_attribute11 => ancrec.ancillary_attribute11,
768 p_ancillary_attribute12 => ancrec.ancillary_attribute12,
769 p_ancillary_attribute13 => ancrec.ancillary_attribute13,
770 p_ancillary_attribute14 => ancrec.ancillary_attribute14,
771 p_ancillary_attribute15 => ancrec.ancillary_attribute15,
772 p_ancillary_chg_rate => l_ancillary_chg_rate);
773
774 -- If the rate is not found then
775 IF NOT l_anc_rate_out THEN
776 -- The record should be updated to error with the error message
777 -- and the concurrent manager's log file should be updated
778 l_validate := FALSE;
779 ancrec.status := g_error;
780 fnd_message.set_name('IGS','IGS_FI_ANCRATE_NOT_FOUND');
781 l_msg := fnd_message.get;
782 fnd_file.put_line(fnd_file.log,l_msg);
783 ancrec.error_msg := ancrec.error_msg||'.'||l_msg;
784 END IF;
785
786 IF l_validate THEN
787
788 -- The Ancillary Rates have been found and all the validations are done
789 BEGIN
790
791 -- Set the Effective Date
792 -- If the values in the record are null, then they are defaulted to
793 -- SYSDATE
794 l_effective_dt := NVL(ancrec.effective_dt,SYSDATE);
795
796 -- Check if the record already exists in the Import Charges table based
797 -- on the Person_Id, Fee Type Calendar Instance and of transaction type
798 -- as ancillary
799 OPEN cur_impchgs(l_person_id,
800 ancrec.fee_cal_type,
801 ancrec.fee_ci_sequence_number,
802 ancrec.fee_type,
803 g_ancillary);
804 FETCH cur_impchgs INTO l_import_charges_id;
805
806 IF cur_impchgs%NOTFOUND THEN
807 -- if the records are not found in the Import Charges table,
808 -- then insert has to be done in both header and detail table
809 l_import_charges_id := NULL;
810 l_rowid := NULL;
811
812 -- Call the TBH for creating a record in the Import Charges Table
813 igs_fi_imp_chgs_pkg.Insert_Row( x_rowid => l_rowid,
814 x_import_charges_id => l_import_charges_id,
815 x_person_id => l_person_id,
816 x_fee_type => ancrec.fee_type,
817 x_fee_cal_type => ancrec.fee_cal_type,
818 x_fee_ci_sequence_number => ancrec.fee_ci_sequence_number,
819 x_transaction_type => g_ancillary);
820
821
822 l_rowid := NULL;
823 l_impchgs_lines_id := NULL;
824
825 FND_MESSAGE.Set_Name('IGS','IGS_FI_ANC_CHG_REC4');
826 FND_MESSAGE.Set_Token('TOKEN',to_char(l_ancillary_chg_rate));
827 FND_FILE.Put_Line(FND_FILE.Log, FND_MESSAGE.Get);
828
829 -- Call the TBH for creating a record in the Import Charges Lines table
830
831 -- Passed l_v_local_currency derived to the currency_cd field (part of enh bug 2584986)
832 -- Obsoleted columns override account codes and ext_attributes
833 -- Call to igs_fi_gen_gl.get_formatted_amount formats l_ancillary_chg_rate by rounding off to currency precision
834 igs_fi_impchgs_lines_pkg.Insert_Row( x_rowid => l_rowid,
835 x_impchg_lines_id => l_impchgs_lines_id,
836 x_import_charges_id => l_import_charges_id,
837 x_transaction_dt => SYSDATE,
838 x_effective_dt => l_effective_dt,
839 x_transaction_amount => igs_fi_gen_gl.get_formatted_amount(l_ancillary_chg_rate),
840 x_currency_cd => l_v_local_currency,
841 x_exchange_rate => NULL,
842 x_comments => NULL,
843 x_ancillary_attribute1 => ancrec.ancillary_attribute1,
844 x_ancillary_attribute2 => ancrec.ancillary_attribute2,
845 x_ancillary_attribute3 => ancrec.ancillary_attribute3,
846 x_ancillary_attribute4 => ancrec.ancillary_attribute4,
847 x_ancillary_attribute5 => ancrec.ancillary_attribute5,
848 x_ancillary_attribute6 => ancrec.ancillary_attribute6,
849 x_ancillary_attribute7 => ancrec.ancillary_attribute7,
850 x_ancillary_attribute8 => ancrec.ancillary_attribute8,
851 x_ancillary_attribute9 => ancrec.ancillary_attribute9,
852 x_ancillary_attribute10 => ancrec.ancillary_attribute10,
853 x_ancillary_attribute11 => ancrec.ancillary_attribute11,
854 x_ancillary_attribute12 => ancrec.ancillary_attribute12,
855 x_ancillary_attribute13 => ancrec.ancillary_attribute13,
856 x_ancillary_attribute14 => ancrec.ancillary_attribute14,
857 x_ancillary_attribute15 => ancrec.ancillary_attribute15,
858 x_attribute_category => ancrec.attribute_category,
859 x_attribute1 => ancrec.attribute1,
860 x_attribute2 => ancrec.attribute2,
861 x_attribute3 => ancrec.attribute3,
862 x_attribute4 => ancrec.attribute4,
863 x_attribute5 => ancrec.attribute5,
864 x_attribute6 => ancrec.attribute6,
865 x_attribute7 => ancrec.attribute7,
866 x_attribute8 => ancrec.attribute8,
867 x_attribute9 => ancrec.attribute9,
868 x_attribute10 => ancrec.attribute10,
869 x_attribute11 => ancrec.attribute11,
870 x_attribute12 => ancrec.attribute12,
871 x_attribute13 => ancrec.attribute13,
872 x_attribute14 => ancrec.attribute14,
873 x_attribute15 => ancrec.attribute15,
874 x_attribute16 => ancrec.attribute16,
875 x_attribute17 => ancrec.attribute17,
876 x_attribute18 => ancrec.attribute18,
877 x_attribute19 => ancrec.attribute19,
878 x_attribute20 => ancrec.attribute20
879 );
880
881 ELSE
882
883 -- The records need to be created in the Import Charges Lines table
884 l_rowid := NULL;
885 l_impchgs_lines_id := NULL;
886
887 FND_MESSAGE.Set_Name('IGS', 'IGS_FI_ANC_CHG_REC4');
888 FND_MESSAGE.Set_Token('TOKEN', to_char(l_ancillary_chg_rate));
889 FND_FILE.Put_Line(FND_FILE.Log, FND_MESSAGE.Get);
890
891
892 -- Call the TBH for creating a record in the Import Charges Lines table
893
894 -- Passed l_v_local_currency derived to the currency_cd field (part of enh bug 2584986)
895 -- Obsoleted columns override account codes and ext_attributes
896 -- Call to igs_fi_gen_gl.get_formatted_amount formats l_ancillary_chg_rate by rounding off to currency precision
897 igs_fi_impchgs_lines_pkg.Insert_Row( x_rowid => l_rowid,
898 x_impchg_lines_id => l_impchgs_lines_id,
899 x_import_charges_id => l_import_charges_id,
900 x_transaction_dt => SYSDATE,
901 x_effective_dt => l_effective_dt,
902 x_transaction_amount => igs_fi_gen_gl.get_formatted_amount(l_ancillary_chg_rate),
903 x_currency_cd => l_v_local_currency,
904 x_exchange_rate => NULL,
905 x_comments => NULL,
906 x_ancillary_attribute1 => ancrec.ancillary_attribute1,
907 x_ancillary_attribute2 => ancrec.ancillary_attribute2,
908 x_ancillary_attribute3 => ancrec.ancillary_attribute3,
909 x_ancillary_attribute4 => ancrec.ancillary_attribute4,
910 x_ancillary_attribute5 => ancrec.ancillary_attribute5,
911 x_ancillary_attribute6 => ancrec.ancillary_attribute6,
912 x_ancillary_attribute7 => ancrec.ancillary_attribute7,
913 x_ancillary_attribute8 => ancrec.ancillary_attribute8,
914 x_ancillary_attribute9 => ancrec.ancillary_attribute9,
915 x_ancillary_attribute10 => ancrec.ancillary_attribute10,
916 x_ancillary_attribute11 => ancrec.ancillary_attribute11,
917 x_ancillary_attribute12 => ancrec.ancillary_attribute12,
918 x_ancillary_attribute13 => ancrec.ancillary_attribute13,
919 x_ancillary_attribute14 => ancrec.ancillary_attribute14,
920 x_ancillary_attribute15 => ancrec.ancillary_attribute15,
921 x_attribute_category => ancrec.attribute_category,
922 x_attribute1 => ancrec.attribute1,
923 x_attribute2 => ancrec.attribute2,
924 x_attribute3 => ancrec.attribute3,
925 x_attribute4 => ancrec.attribute4,
926 x_attribute5 => ancrec.attribute5,
927 x_attribute6 => ancrec.attribute6,
928 x_attribute7 => ancrec.attribute7,
929 x_attribute8 => ancrec.attribute8,
930 x_attribute9 => ancrec.attribute9,
931 x_attribute10 => ancrec.attribute10,
932 x_attribute11 => ancrec.attribute11,
933 x_attribute12 => ancrec.attribute12,
934 x_attribute13 => ancrec.attribute13,
935 x_attribute14 => ancrec.attribute14,
936 x_attribute15 => ancrec.attribute15,
937 x_attribute16 => ancrec.attribute16,
938 x_attribute17 => ancrec.attribute17,
939 x_attribute18 => ancrec.attribute18,
940 x_attribute19 => ancrec.attribute19,
941 x_attribute20 => ancrec.attribute20
942 );
943 END IF;
944 CLOSE cur_impchgs;
945
946 -- Code for the Charges API
947
948 -- Get the Summed Up Invoice Amount from the Invoice table
949 -- for the Ancillary Type of Charges
950 OPEN cur_inv_int(l_person_id,
951 ancrec.fee_cal_type,
952 ancrec.fee_ci_sequence_number,
953 ancrec.fee_type,
954 g_ancillary);
955 FETCH cur_inv_int INTO l_prev_amount;
956 CLOSE cur_inv_int;
957
958
959 -- Get the Fee Description
960 OPEN cur_fee(ancrec.fee_type);
961 FETCH cur_fee INTO l_fee_desc;
962 CLOSE cur_fee;
963
964 -- Initialize the Header Record and the PL/SQL table
965 l_header_rec := NULL;
966 l_line_tbl := l_line_tbl_dummy;
967 l_line_tbl.DELETE;
968
969 -- Calculate the Charge Amount. This will be the difference of the Amount already
970 -- posted to the charges table and the current amount
971 l_diff_amount := NVL(l_ancillary_chg_rate,0) - NVL(l_prev_amount,0);
972
973 l_header_rec.p_person_id := l_person_id;
974 l_header_rec.p_fee_type := ancrec.fee_type;
975 l_header_rec.p_fee_cat := NULL;
976 l_header_rec.p_fee_cal_type := ancrec.fee_cal_type;
977 l_header_rec.p_fee_ci_sequence_number := ancrec.fee_ci_sequence_number;
978 l_header_rec.p_invoice_amount := l_diff_amount;
979 l_header_rec.p_invoice_creation_date := SYSDATE;
980 l_header_rec.p_invoice_desc := l_fee_desc;
981 l_header_rec.p_transaction_type := g_ancillary;
982 l_header_rec.p_currency_cd := l_v_local_currency;
983 l_header_rec.p_exchange_rate := 1;
984 l_header_rec.p_effective_date := l_effective_dt;
985
986 l_line_tbl(1).p_description := l_fee_desc;
987 l_line_tbl(1).p_amount := l_diff_amount;
988
989 -- This code has been added by aiyer as a part of the code fix for the bug #1954101
990 -- Values are being assigned to the l_line_tbl variable which gets passed to the
991 -- igs_fi_charges_api_pvt.create_charge procedure.
992
993 -- Start of additions as part of bug 2584986
994 -- gl_date always passed as sysdate, without any validations
995
996 l_line_tbl(1).p_d_gl_date := TRUNC(SYSDATE);
997
998 -- override account codes are passed as null and derived in charges api
999 l_line_tbl(1).p_override_dr_rec_ccid := NULL;
1000 l_line_tbl(1).p_override_cr_rev_ccid := NULL;
1001 l_line_tbl(1).p_override_dr_rec_account_cd := NULL;
1002 l_line_tbl(1).p_override_cr_rev_account_cd := NULL;
1003
1004 -- End of modifications
1005
1006 l_line_tbl(1).p_attribute_category := ancrec.attribute_category;
1007 l_line_tbl(1).p_attribute1 := ancrec.attribute1;
1008 l_line_tbl(1).p_attribute2 := ancrec.attribute2;
1009 l_line_tbl(1).p_attribute3 := ancrec.attribute3;
1010 l_line_tbl(1).p_attribute4 := ancrec.attribute4;
1011 l_line_tbl(1).p_attribute5 := ancrec.attribute5;
1012 l_line_tbl(1).p_attribute6 := ancrec.attribute6;
1013 l_line_tbl(1).p_attribute7 := ancrec.attribute7;
1014 l_line_tbl(1).p_attribute8 := ancrec.attribute8;
1015 l_line_tbl(1).p_attribute9 := ancrec.attribute9;
1016 l_line_tbl(1).p_attribute10 := ancrec.attribute10;
1017 l_line_tbl(1).p_attribute11 := ancrec.attribute11;
1018 l_line_tbl(1).p_attribute12 := ancrec.attribute12;
1019 l_line_tbl(1).p_attribute13 := ancrec.attribute13;
1020 l_line_tbl(1).p_attribute14 := ancrec.attribute14;
1021 l_line_tbl(1).p_attribute15 := ancrec.attribute15;
1022 l_line_tbl(1).p_attribute16 := ancrec.attribute16;
1023 l_line_tbl(1).p_attribute17 := ancrec.attribute17;
1024 l_line_tbl(1).p_attribute18 := ancrec.attribute18;
1025 l_line_tbl(1).p_attribute19 := ancrec.attribute19;
1026 l_line_tbl(1).p_attribute20 := ancrec.attribute20;
1027
1028 -- If there is a difference in amount, then
1029 IF l_diff_amount <> 0 THEN
1030 -- Call the Charges API
1031 igs_fi_charges_api_pvt.Create_Charge(p_api_version => 2.0,
1032 p_init_msg_list => 'T',
1033 p_commit => 'F',
1034 p_header_rec => l_header_rec,
1035 p_line_tbl => l_line_tbl,
1036 x_invoice_id => l_invoice_id,
1037 x_line_id_tbl => l_line_id_tbl,
1038 x_return_status => l_return_status,
1039 x_msg_count => l_msg_count,
1040 x_msg_data => l_msg_data,
1041 x_waiver_amount => l_n_waiver_amount);
1042
1043 -- If the Charges API returns a Status other than S
1044 -- this means that the Charges API has resulted in an
1045 -- Error and this must be logged.
1046 IF l_return_status <> 'S' THEN
1047 l_exception_flag := 'Y';
1048 IF l_msg_count = 1 THEN
1049 fnd_message.set_encoded(l_msg_data);
1050 ancrec.error_msg := fnd_message.get;
1051 ELSE
1052 FOR l_var IN 1..l_msg_count LOOP
1053 fnd_message.parse_encoded(fnd_msg_pub.get, l_appl_name, l_msg_name);
1054 fnd_message.set_name(l_appl_name, l_msg_name);
1055 ancrec.error_msg := ancrec.error_msg||'.'||fnd_message.get;
1056 END LOOP;
1057 END IF;
1058 END IF;
1059
1060 END IF;
1061 -- Ends here
1062 -- l_diff_amount is needed to be passed to the Charges API
1063 EXCEPTION
1064 WHEN OTHERS THEN
1065 l_exception_flag := 'Y';
1066 ROLLBACK;
1067 END;
1068 -- If an error happened while creating the records, then
1069 -- Set the Ancillary Record Status to ERROR and update the Error Message
1070 -- Field to the proper error message
1071 IF l_exception_flag = 'Y' THEN
1072 l_exception_flag := 'N';
1073 ancrec.status := g_error;
1074 IF ancrec.error_msg IS NULL THEN
1075 l_msg := fnd_message.get;
1076 IF l_msg IS NULL THEN
1077 fnd_message.set_name('IGS','IGS_FI_RECORD_IN_ERROR');
1078 ancrec.error_msg := fnd_message.get;
1079 ELSE
1080 ancrec.error_msg := l_msg;
1081 END IF;
1082 l_msg := NULL;
1083 END IF;
1084 fnd_file.put_line(fnd_file.log,ancrec.error_msg);
1085 END IF;
1086 END IF;
1087
1088 -- If the Ancillary Record Status is TODO then no error has occured
1089 -- and should be updated to SUCCESS
1090 IF ancrec.status <> g_error THEN
1091 fnd_message.set_name('IGS','IGS_FI_ANC_REC_SUCCESS');
1092 fnd_file.put_line(fnd_file.log,fnd_message.get);
1093 ancrec.status := g_success;
1094 ancrec.error_msg:= null;
1095 l_rec_cntr := NVL(l_rec_cntr,0) + 1;
1096 ELSE
1097 fnd_message.set_name('IGS', 'IGS_FI_ANC_REC_IN_ERROR');
1098 fnd_file.put_line(fnd_file.log,fnd_message.get);
1099 END IF;
1100
1101
1102 -- The Record in the Ancillary Interface table to be updated with
1103 -- the status and the error message
1104 -- Call the TBH for updating the record
1105 igs_fi_anc_int_pkg.update_row( x_rowid => ancrec.rowid,
1106 x_ancillary_int_id => ancrec.ancillary_int_id,
1107 x_person_id => ancrec.person_id,
1108 x_person_id_type => ancrec.person_id_type,
1109 x_api_person_id => ancrec.api_person_id,
1110 x_status => ancrec.status,
1111 x_fee_type => ancrec.fee_type,
1112 x_fee_cal_type => ancrec.fee_cal_type,
1113 x_fee_ci_sequence_number => ancrec.fee_ci_sequence_number,
1114 x_ancillary_attribute1 => ancrec.ancillary_attribute1,
1115 x_ancillary_attribute2 => ancrec.ancillary_attribute2,
1116 x_ancillary_attribute3 => ancrec.ancillary_attribute3,
1117 x_ancillary_attribute4 => ancrec.ancillary_attribute4,
1118 x_ancillary_attribute5 => ancrec.ancillary_attribute5,
1119 x_ancillary_attribute6 => ancrec.ancillary_attribute6,
1120 x_ancillary_attribute7 => ancrec.ancillary_attribute7,
1121 x_ancillary_attribute8 => ancrec.ancillary_attribute8,
1122 x_ancillary_attribute9 => ancrec.ancillary_attribute9,
1123 x_ancillary_attribute10 => ancrec.ancillary_attribute10,
1124 x_ancillary_attribute11 => ancrec.ancillary_attribute11,
1125 x_ancillary_attribute12 => ancrec.ancillary_attribute12,
1126 x_ancillary_attribute13 => ancrec.ancillary_attribute13,
1127 x_ancillary_attribute14 => ancrec.ancillary_attribute14,
1128 x_ancillary_attribute15 => ancrec.ancillary_attribute15,
1129 x_attribute_category => ancrec.attribute_category,
1130 x_attribute1 => ancrec.attribute1,
1131 x_attribute2 => ancrec.attribute2,
1132 x_attribute3 => ancrec.attribute3,
1133 x_attribute4 => ancrec.attribute4,
1134 x_attribute5 => ancrec.attribute5,
1135 x_attribute6 => ancrec.attribute6,
1136 x_attribute7 => ancrec.attribute7,
1137 x_attribute8 => ancrec.attribute8,
1138 x_attribute9 => ancrec.attribute9,
1139 x_attribute10 => ancrec.attribute10,
1140 x_attribute11 => ancrec.attribute11,
1141 x_attribute12 => ancrec.attribute12,
1142 x_attribute13 => ancrec.attribute13,
1143 x_attribute14 => ancrec.attribute14,
1144 x_attribute15 => ancrec.attribute15,
1145 x_attribute16 => ancrec.attribute16,
1146 x_attribute17 => ancrec.attribute17,
1147 x_attribute18 => ancrec.attribute18,
1148 x_attribute19 => ancrec.attribute19,
1149 x_attribute20 => ancrec.attribute20,
1150 x_effective_dt => ancrec.effective_dt,
1151 x_error_msg => SUBSTR(LTRIM(ancrec.error_msg,'.'),1,2000),
1152 x_validation_flag => ancrec.validation_flag,
1153 x_mode => 'R');
1154 COMMIT;
1155 END LOOP;
1156 fnd_file.put_line(fnd_file.log,fnd_message.get_string ('IGS','IGS_GE_TOTAL_REC_PROCESSED')||TO_CHAR(l_rec_cntr));
1157 retcode :=0;
1158
1159 EXCEPTION
1160 WHEN l_exception THEN
1161 retcode := 2;
1162 WHEN OTHERS THEN
1163 retcode := 2;
1164 fnd_message.set_name('IGS','IGS_FI_ANC_REC_IN_ERROR');
1165 errbuf := fnd_message.Get;
1166 igs_ge_msg_stack.conc_exception_hndl;
1167 END finp_imp_calc_anc_charges;
1168
1169 FUNCTION Check_Person_Id(p_person_id IN igs_pe_person.person_id%TYPE,
1170 p_person_id_type IN igs_pe_person.person_id_type%TYPE,
1171 p_api_person_id IN igs_pe_person.api_person_id%TYPE) RETURN BOOLEAN AS
1172 /***********************************************************************************************
1173
1174 Created By: Amit Gairola
1175
1176 Date Created By: 12-04-2001
1177
1178 Purpose: This function checks whether the Person Id specified by the Person Id Type and
1179 the Alternate Person Id is present in the Person table.
1180
1181 Known limitations,enhancements,remarks:
1182
1183 Change History
1184
1185 Who When What
1186 shtatiko 29-APR-2003 Enh# 2831569, Added new parameter p_person_id so that validation
1187 of person_id_type and api_person_id combination is done against
1188 given person_id i.e., now this function validates whether
1189 person_id_type and api_person_id combination is exists for a given
1190 person_id.
1191 ********************************************************************************************** */
1192
1193 l_temp VARCHAR2(1);
1194 l_ret BOOLEAN;
1195
1196 -- Cursor for checking the Person Id in IGS_PE_ALT_PERS_ID table for the
1197 -- Person Id Type and the Alternate Person Id exists in the Person table
1198 CURSOR cur_person(cp_person_id igs_pe_person.person_id%TYPE,
1199 cp_person_id_type IGS_PE_PERSON.Person_Id_Type%TYPE,
1200 cp_api_person_id IGS_PE_PERSON.Api_Person_Id%TYPE) IS
1201 SELECT 'x'
1202 FROM igs_pe_person_base_v ppv,
1203 igs_pe_alt_pers_id api
1204 WHERE ppv.person_id = api.pe_person_id
1205 AND ppv.person_id = cp_person_id
1206 AND api.person_id_type = cp_person_id_type
1207 AND api.api_person_id = cp_api_person_id;
1208 BEGIN
1209
1210 -- Open the cursor
1211 OPEN cur_person(p_person_id,
1212 p_person_id_type,
1213 p_api_person_id);
1214 FETCH cur_person INTO l_temp;
1215
1216 -- If the Person is not found then
1217 IF cur_person%NOTFOUND THEN
1218
1219 -- Return False
1220 l_ret := FALSE;
1221 ELSE
1222
1223 -- Else return true
1224 l_ret := TRUE;
1225 END IF;
1226 CLOSE cur_person;
1227
1228 RETURN l_ret;
1229 END check_person_id;
1230
1231 FUNCTION get_person_number(p_person_id IN igs_pe_person.person_id%TYPE) RETURN igs_pe_person.person_number%TYPE AS
1232
1233 /***********************************************************************************************
1234
1235 Created By: Amit Gairola
1236
1237 Date Created By: 12-04-2001
1238
1239 Purpose: This function returns the Person Number based on the Person Id passed
1240
1241 Known limitations,enhancements,remarks:
1242
1243 Change History
1244
1245 Who When What
1246
1247 ********************************************************************************************** */
1248
1249 l_person_number igs_pe_person.person_number%TYPE;
1250
1251 -- Cursor for geeting the Person Number from the Person Id
1252 CURSOR cur_person(cp_person_id igs_pe_person.person_id%TYPE) IS
1253 SELECT person_number
1254 FROM igs_pe_person_base_v
1255 WHERE person_id = cp_person_id;
1256 BEGIN
1257
1258 -- Fetch the Person Number based on the Person Id passed
1259 OPEN cur_person(p_person_id);
1260 FETCH cur_person INTO l_person_number;
1261 CLOSE cur_person;
1262 RETURN l_person_number;
1263 END get_person_number;
1264
1265 END igs_fi_prc_impclc_anc_chgs;
[Home] [Help]