1 PACKAGE BODY igs_fi_load_ext_chg AS
2 /* $Header: IGSFI50B.pls 120.7 2006/05/16 04:17:18 akandreg ship $ */
3
4 g_external CONSTANT VARCHAR2(20) := 'EXTERNAL';
5 g_curr_desc fnd_currencies_tl.name%TYPE;
6 g_curr_cd fnd_currencies_tl.currency_code%TYPE;
7
8 /******************************************************************
9 Created By :Suraj Chakma
10 Date Created By :02-06-2000
11 Purpose :This procedure validates the data entered into
12 IGS_FI_EXT_INT_ALL Table. It is called from the
13 form IGSFI038.fmb - EXTERNAL CHARGES
14 Known limitations,
15 enhancements,
16 remarks :
17 Change History
18 Who When What
19 akandreg 16-May-2006 Bug 5105131, modified cursor c_f_cal_typ. In function igs_fi_ext_val, the system defined
20 status (igs_ca_stat.S_CAL_STATUS) displayed on the Calendar Statuses form would be used in
21 the validation test instead of IGS_CA_INST.CAL_TYPE, which is user defined status.
22 sapanigr 24-Feb-2006 Bug#5018036 - Replaced cursor cur_person in igs_fi_extto_imp procedure by function call.
23 sapanigr 12-Feb-2006 Bug#5018036 - Modified cursor cur_person in igs_fi_extto_imp procedure. (R12 SQL Repository tuning)
24 svuppala 09-AUG-2005 Enh 3392095 - Tution Waivers build
25 Impact of Charges API version Number change
26 Modified igs_fi_charges_api_pvt.create_charge - version 2.0 and x_waiver_amount
27 gurprsin 26-Jul-2005 Bug# 3392095.In IGS_FI_EXTTO_IMP procedure, Modified table habdler call igs_fi_prc_acct_pkg.build_accounts to
28 include waiver_name column parameter.
29 bannamal 03-Jun-2005 Unit Level Fee Assessment Build. Modified the call to
30 igs_fi_prc_acct_pkg.build_accounts. Added new parameters.
31 pmarada 29-Mar-2005 Bug 4270442, removed the cursor c_person instead of that calling
32 get_std_formerstd_ind function.
33 pmarada 30-nov-2004 Bug 4003908, Added get_std_formerstd_ind funcation to return whether person is
34 of student and former student person type
35 pmarada 16-Nov-2004 Bug 3902065, Added FORMER_STUDENT to the person types cursor to allow formaer students
36 vvutukur 20-Jun-2003 Bug#2777502.Modified igs_fi_ext_val,igs_fi_extto_imp.
37 vchappid 19-May-2003 Build Bug# 2831572, Financial Accounting Enhancements
38 New Parameters - Attendance Type, Attendance Mode, Residency Status Code
39 added in procedure igs_fi_extto_imp
40 shtatiko 28-APR-2003 Enh# 2831569, Modified igs_fi_extto_imp.
41 pathipat 07-Jan-2003 Bug: 2737666 - Modified igs_fi_extto_imp() - rearranged code.
42 Sykrishn 31DEC2002 Bug 2682928 -- igs_fi_extto_imp Logging of parameters introduced
43 Also changed cur_person to point to IGS_FI_PARTIES_V
44 Also removed repetative loggin og fee_cal_type,start_dt and end_dt for each record in cur_fei loop as they
45 are fixed and are once per process.
46 Used igs_ge_date.igschar(start_dt) ||' '|| igs_ge_date.igschar(end_dt)
47 SYKRISHn 30_DEC_2002 Bug 2727384 - Loggin details before validation errors are reported.
48 pathipat 16-Nov-2002 Enh Bug 2584986
49 1. Modified declaration of g_curr_desc to fnd_currencies_tl.name%TYPe from igs_fi_cur.description%TYPE
50 2. Modified function igs_fi_Ext_val() - Added validation for gl_date, modified validations of currency_cd
51 Added parameter p_d_gl_date.
52 3. Modified igs_fi_extto_imp - Removed insert_row into IGS_FI_IMP_CHGS and IGS_FI_IMGCHGS_LINES.
53 Added parameter p_d_gl_Date in call to create_charges() and charges_api call
54 Added call to igs_fi_prc_acct_pkg.build_accounts before calling charges_api
55 4. Removed function lookup_Desc(). Used generic function igs_fi_gen_gl.get_lkp_meaning() instead.
56 vvutukur 29-Jul-2002 Bug#2425767.Modified procedure igs_fi_extto_imp to remove the references to
57 chg_rate,chg_elements,transaction_type as these columns are obsolete.
58 SYKRISHN 10_JUL_2002 Bug 2438874 - Procedure igs_fi_extto_imp modified to log Transaction Amount along with the other data elemets already present
59 Also Introduced currency descrption allong with the amount. - Log File looks imporoved - Hard coded english text remived
60 sykrishn 03-JUL-220 Bug 2442163 - Procedure igs_fi_extto_imp modified to insert sysdate to
61 transaction_dt in the table igs_fi_impchgs_lines table
62 Reference to l_cur_fei.transaction_dt is removed as it will never be present.
63 Removed transaction_dt from igs_fi_ext_int_pkg.update_row
64
65 smadathi 24-Jun-2002 Bug 2404720. Procedure igs_fi_extto_imp modified.
66 jbegum 14-Jun-2002 BUG#2413574 Modified cursor cur_fei in procedure igs_fi_extto_imp.
67 jbegum 12-Jun-2002 Bug#2403209 Removed code that calculated the transaction amount
68 as a product of Charge Rate and Charge Elements columns of IGS_FI_EXT_INT
69 table and inserted that value in the transaction amount column of the
70 IGS_FI_IMPCHGS_LINES table.
71 Now the transaction amount column value of IGS_FI_EXT_INT is directly
72 imported into the transaction amount column of the IGS_FI_IMPCHGS_LINES
73 table.
74 agairola 04-Jun-2002 Bug 2395663 - Modified the TBH call for the IGS_FI_IMPCHGS_LINES_PKG
75 Added one function for validating the DFF
76 SYKRISHn 22-MAY-2002 Bug 2385001 OSSTST15: TRANSACTION AND EFFECTIVE DATE IN EXTERNAL CHARGES FORM
77 Removed the validation for Transaction and Effective Date from the function
78 igs_fi_ext_val (Keeping the signature same for future use)
79 SYKRISHN 19-APR-2002 Bug 2324088 - Introduced Desc Flex Field Validations and CCID validations.
80 vchappid 12-APR-2001 Modified input parameters as per new Ancillary,External Charges DLD
81
82 ******************************************************************/
83
84 FUNCTION igs_fi_ext_val (p_person_id igs_fi_ext_int_all.person_id%TYPE,
85 p_fee_type igs_fi_ext_int_all.fee_type%TYPE,
86 p_fee_cal_type igs_fi_ext_int_all.fee_cal_type%TYPE,
87 p_fee_ci_sequence_number igs_fi_ext_int_all.fee_ci_sequence_number%TYPE,
88 p_transaction_dt igs_fi_ext_int_all.effective_dt%TYPE ,
89 p_currency_cd igs_fi_ext_int_all.currency_cd%TYPE,
90 p_effective_dt igs_fi_ext_int_all.effective_dt%TYPE,
91 p_d_gl_date DATE,
92 p_message_name OUT NOCOPY VARCHAR2)
93 RETURN BOOLEAN AS
94 /***
95 akandreg 16-May-2006 Bug 5105131, modified cursor c_f_cal_typ. In function igs_fi_ext_val, the system defined
96 status (igs_ca_stat.S_CAL_STATUS) displayed on the Calendar Statuses form would be used in
97 the validation test instead of IGS_CA_INST.CAL_TYPE, which is user defined status.
98 pmarada 29-Mar-2005 Bug 4270442, removed the cursor c_person instead of that calling
99 get_std_formerstd_ind function.
100 vvutukur 20-Jun-2003 Bug#2777502.Modified cursor c_fee_typ to exclude closed fee types.
101 pathipat 16-Nov-2002 Enh Bug: 2584986 - Added parameter p_d_gl_Date and its validations
102 Modified validations for currency_cd
103 SYKRISHn 22-MAY-2002 Bug 2385001 OSSTST15: TRANSACTION AND EFFECTIVE DATE IN EXTERNAL CHARGES FORM
104 Removed the validation for Transaction and Effective Date from the function
105 igs_fi_ext_val (Keeping the signature same for future use)
106 SYKRISHN 03-JUL-2002 Bug 2442163
107 Definition of p_transaction_dt changed to Effective_dt%type - since transaction_dt is being made obsolete.
108
109 ***/
110 l_start_dt igs_ca_inst.start_dt%TYPE;
111 l_end_dt igs_ca_inst.end_dt%TYPE;
112 l_dummy VARCHAR2(1);
113 l_v_status gl_period_statuses.closing_status%TYPE := NULL;
114 l_message_name fnd_new_messages.message_name%TYPE;
115
116 CURSOR c_fee_typ
117 IS
118 SELECT 'x'
119 FROM igs_fi_fee_type
120 WHERE s_fee_type = 'EXTERNAL'
121 AND fee_type = p_fee_type
122 AND NVL(closed_ind,'N') = 'N';
123
124 CURSOR c_f_cal_typ
125 IS
126 SELECT ci.start_dt,
127 ci.end_dt
128 FROM igs_ca_inst ci,
129 igs_ca_type ct,
130 igs_ca_stat st
131 WHERE ci.cal_type = ct.cal_type
132 AND ct.s_cal_cat = 'FEE'
133 AND ci.cal_status = st.cal_status
134 AND st.s_cal_status = 'ACTIVE'
135 AND ci.cal_type = p_fee_cal_type
136 AND ci.sequence_number = p_fee_ci_sequence_number;
137
138 BEGIN
139
140 -- to validate person id
141 -- check whether the person type of student or former student
142 IF igs_fi_load_ext_chg.get_std_formerstd_ind(p_person_id) = 'N' THEN
143 p_message_name :='IGS_FI_INVALID_PERSON_ID';
144 RETURN FALSE;
145 END IF;
146
147 --to validate Fee Type
148 OPEN c_fee_typ ;
149 FETCH c_fee_typ INTO l_dummy;
150 IF c_fee_typ%NOTFOUND THEN
151 p_message_name :='IGS_FI_INVALID_FEE_TYPE';
152 CLOSE c_fee_typ;
153 RETURN FALSE;
154 END IF;
155 CLOSE c_fee_typ;
156
157 --to validate Fee Cal type
158 OPEN c_f_cal_typ ;
159 FETCH c_f_cal_typ INTO l_start_dt, l_end_dt;
160 IF c_f_cal_typ%NOTFOUND THEN
161 p_message_name :='IGS_FI_INVALID_FEE_CAL_TYPE';
162 CLOSE c_f_cal_typ;
163 RETURN FALSE;
164 END IF;
165 CLOSE c_f_cal_typ;
166
167
168 -- Modified as part of GL Interface build
169 -- Validate if the currency_cd passed is same as the currency_cd in the System Options form
170 -- So call function finp_get_cur which obtains the code and desc from igs_fi_control
171 igs_fi_gen_gl.finp_get_cur( p_v_currency_cd => g_curr_cd,
172 p_v_curr_desc => g_curr_desc,
173 p_v_message_name => l_message_name
174 );
175 IF l_message_name IS NULL THEN
176 IF g_curr_cd <> p_currency_cd THEN
177 p_message_name := 'IGS_FI_CUR_MISMATCH';
178 RETURN FALSE;
179 END IF;
180 ELSE
181 p_message_name := l_message_name;
182 RETURN FALSE;
183 END IF;
184
185
186 -- to validate gl_Date to be in an open or future period
187 -- added as part of Enh bug 2584986 - GL interface build
188 IF p_d_gl_date IS NULL THEN
189 p_message_name := 'IGS_FI_NO_GL_DATE';
190 RETURN FALSE;
191 END IF;
192
193 igs_fi_gen_gl.get_period_status_for_date( p_d_date => p_d_gl_date,
194 p_v_closing_status => l_v_status,
195 p_v_message_name => l_message_name
196 );
197
198 IF l_message_name IS NULL THEN
199 IF l_v_status NOT IN ('O','F') THEN
200 p_message_name := 'IGS_FI_INVALID_GL_DATE';
201 RETURN FALSE;
202 ELSE
203 p_message_name := NULL;
204 RETURN TRUE;
205 END IF;
206 ELSE
207 p_message_name := l_message_name;
208 RETURN FALSE;
209 END IF;
210
211 END igs_fi_ext_val;
212
213
214 FUNCTION validate_desc_flex
215 (
216 p_attribute_category IN VARCHAR2,
217 p_attribute1 IN VARCHAR2,
218 p_attribute2 IN VARCHAR2,
219 p_attribute3 IN VARCHAR2,
220 p_attribute4 IN VARCHAR2,
221 p_attribute5 IN VARCHAR2,
222 p_attribute6 IN VARCHAR2,
223 p_attribute7 IN VARCHAR2,
224 p_attribute8 IN VARCHAR2,
225 p_attribute9 IN VARCHAR2,
226 p_attribute10 IN VARCHAR2,
227 p_attribute11 IN VARCHAR2,
228 p_attribute12 IN VARCHAR2,
229 p_attribute13 IN VARCHAR2,
230 p_attribute14 IN VARCHAR2,
231 p_attribute15 IN VARCHAR2,
232 p_attribute16 IN VARCHAR2,
233 p_attribute17 IN VARCHAR2,
234 p_attribute18 IN VARCHAR2,
235 p_attribute19 IN VARCHAR2,
236 p_attribute20 IN VARCHAR2,
237 p_desc_flex_name IN VARCHAR2
238 ) RETURN BOOLEAN AS
239 /***********************************************************************************************
240
241 Created By : agairola
242 Date Created By: 04-Jun-2002
243 Purpose : To validate the DFF. This has been created as the column names for the Attributes
244 of the DFF have names beginning with EXT_. This has been created for bug 2395663
245
246 Known limitations,enhancements,remarks:
247 Change History
248 Who When What
249
250 ********************************************************************************************** */
251 CURSOR app_cur IS
252 SELECT
253 application_short_name
254 FROM
255 fnd_application app, fnd_descriptive_flexs des
256 WHERE
257 app.application_id = des.application_id AND
258 des.descriptive_flexfield_name = p_desc_flex_name;
259 app_rec app_cur%ROWTYPE;
260 BEGIN
261 fnd_flex_descval.clear_column_values;
262 fnd_flex_descval.set_context_value(p_attribute_category);
263 fnd_flex_descval.set_column_value('ATTRIBUTE1',p_attribute1);
264 fnd_flex_descval.set_column_value('ATTRIBUTE2',p_attribute2);
265 fnd_flex_descval.set_column_value('ATTRIBUTE3',p_attribute3);
266 fnd_flex_descval.set_column_value('ATTRIBUTE4',p_attribute4);
267 fnd_flex_descval.set_column_value('ATTRIBUTE5',p_attribute5);
268 fnd_flex_descval.set_column_value('ATTRIBUTE6',p_attribute6);
269 fnd_flex_descval.set_column_value('ATTRIBUTE7',p_attribute7);
270 fnd_flex_descval.set_column_value('ATTRIBUTE8',p_attribute8);
271 fnd_flex_descval.set_column_value('ATTRIBUTE9',p_attribute9);
272 fnd_flex_descval.set_column_value('ATTRIBUTE10',p_attribute10);
273 fnd_flex_descval.set_column_value('ATTRIBUTE11',p_attribute11);
274 fnd_flex_descval.set_column_value('ATTRIBUTE12',p_attribute12);
275 fnd_flex_descval.set_column_value('ATTRIBUTE13',p_attribute13);
276 fnd_flex_descval.set_column_value('ATTRIBUTE14',p_attribute14);
277 fnd_flex_descval.set_column_value('ATTRIBUTE15',p_attribute15);
278 fnd_flex_descval.set_column_value('ATTRIBUTE16',p_attribute16);
279 fnd_flex_descval.set_column_value('ATTRIBUTE17',p_attribute17);
280 fnd_flex_descval.set_column_value('ATTRIBUTE18',p_attribute18);
281 fnd_flex_descval.set_column_value('ATTRIBUTE19',p_attribute19);
282 fnd_flex_descval.set_column_value('ATTRIBUTE20',p_attribute20);
283 OPEN app_cur;
284 FETCH app_cur INTO app_rec;
285 CLOSE app_cur;
286 IF (fnd_flex_descval.validate_desccols( app_rec.application_short_name, p_desc_flex_name, 'I',SYSDATE)) THEN
287 RETURN TRUE;
288 ELSE
289 RETURN FALSE;
290 END IF;
291 EXCEPTION
292 WHEN OTHERS THEN
293 IF app_cur%ISOPEN THEN
294 CLOSE app_cur;
295 END IF;
296 RETURN FALSE;
297 END validate_desc_flex;
298
299
300 PROCEDURE igs_fi_extto_imp(errbuf OUT NOCOPY VARCHAR2,
301 retcode OUT NOCOPY NUMBER,
302 p_org_id NUMBER,
303 p_person_id igs_fi_ext_int_all.person_id%TYPE ,
304 p_fee_type igs_fi_ext_int_all.fee_type%TYPE ,
305 p_fee_cal_type igs_fi_ext_int_all.fee_cal_type%TYPE,
306 p_fee_ci_sequence_number igs_fi_ext_int_all.fee_ci_sequence_number%TYPE
307 ) AS
308 /******************************************************************
309 Created By :Suraj Chakma
310 Date Created By :02-06-2000
311 Purpose :This process is called by the concurrent manager
312 from concurrent program IGSFIJ21 to load data from
313 the IGS_FI_EXT_INT_ALL Table to the IGS_FI_IMP_CHGS_ALL
314 and IGS_FI_IMPCHGS_LINES Tables
315 Known limitations,
316 enhancements,
317 remarks :
318 Change History
319 Who When What
320 sapanigr 24-Feb-2006 Bug#5018036 - Cursor cur_person replaced by call to function igs_fi_gen_008.get_party_number.
321 sapanigr 12-Feb-2006 Bug#5018036 - Cursor cur_person now queries hz_parties instead of igs_fi_parties_v. (R12 SQL Repository tuning)
322 svuppala 09-AUG-2005 Enh 3392095 - Tution Waivers build
323 Impact of Charges API version Number change
324 Modified igs_fi_charges_api_pvt.create_charge - version 2.0 and x_waiver_amount
325 gurprsin 26-Jul-2005 Bug# 3392095. Modified table habdler call igs_fi_prc_acct_pkg.build_accounts to include waiver_name column parameter.
326 vvutukur 20-Jun-2003 Bug#2777502.Modified the code to concatenate all the error messages of interface record and update the newly added
327 error_msg column in interface table. Also cursor cur_fei is modified such that it picks up records with ERROR status
328 along with TODO status records.
329 vchappid 19-May-2003 Build Bug# 2831572, Financial Accounting Enhancements
330 New Parameters - Attendance Type, Attendance Mode, Residency Status Code added
331 shtatiko 28-APR-2003 Eng# 2831569, Added check for Manage Accounts System Option. If its
332 value is NULL then this process cannot be run.
333 pathipat 07-Jan-2003 Bug:2737666 - Moved code for logging parameters to before calling
334 the validations. Moved code for incrementing record count to end of
335 procedure before doing a commit.
336 Sykrishn 31DEC2002 Bug 2682928 -- igs_fi_extto_imp Logging of parameters introduced
337 Also changed cur_person to point to IGS_FI_PARTIES_V
338 Also removed repetative loggin og fee_cal_type,start_dt and end_dt for each record in cur_fei loop as they
339 are fixed and are once per process.
340 Used igs_ge_date.igschar(start_dt) ||' '|| igs_ge_date.igschar(end_dt)
341
342 SYKRISHn 30_DEC_2002 Bug 2727384 - Loggin details before validation errors are reported.
343 pathipat 15-NOV-2002 Enh# 2584986 - GL Interface build
344 1. Removed insert_row into IGS_FI_IMP_CHGS and IGS_FI_IMPCHGS_LINES tables
345 2. Added call to igs_fi_prc_acct_pkg.build_accounts before calling charges_api
346 3. Added p_d_gl_date to be passed to charges_api, in p_line_tbl
347 4. Added parameter p_d_gl_date in call to igs_fi_ext_val()
348 5. DFF changed to IGS_FI_INVLN_INT_ALL_FLEX from IGS_FI_IMPCHGS_EXT_FLEX
349 6. Removed cursor cur_imp_chgs and the associated local variables
350 sarakshi 13-sep-2002 Enh#2564643,removed the reference of subaccount id from this procedure
351 vvutukur 29-Jul-2002 Bug#2425767.removed parameters x_chg_rate,x_chg_elements from the calls to
352 igs_fi_impchgs_lines_pkg.insert_row and igs_fi_ext_int_pkg.update_row as these
353 columns are obsoleted.removed transaction_type from call to igs_fi_ext_int_pkg.update_row.
354 SYKRISHN 10_JUL_2002 Bug 2438874 - Procedure igs_fi_extto_imp modified to log Transaction Amount along with
355 the other data elemets already present
356 Also Introduced currency descrption allong with the amount.
357 sykrishn 03-JUL-220 Bug 2442163 - Procedure igs_fi_extto_imp modified to insert sysdate to
358 transaction_dt in the table igs_fi_impchgs_lines table
359 Reference to l_cur_fei.transaction_dt is removed as it will never be present.
360 Removed transaction_dt from igs_fi_ext_int_pkg.update_row
361 smadathi 24-Jun-2002 Bug 2404720. The concatenated description comprising of fee desc
362 and transaction date which was initially passed as parameter to charges
363 API was modified to pass only fee type description.
364 jbegum 14-Jun-2002 BUG#2413574 Modified the cursor cur_fei,by removing the and condition
365 "transaction_type = 'EXTERNAL'" from the where clause.
366 agairola 04-Jun-2002 Bug 2395663 - Modified the TBH call for the IGS_FI_IMPCHGS_LINES_PKG
367 SYKRISHN 19-APR-2002 Bug 2324088 - Introduced Desc Flex Field Validations and CCID validations.
368 smadathi 27-Feb-2002 Bug 2238413. Reduced selection list for
369 rec installed flag to 'Y' and 'N'.Removed
370 reference of rec installed flag = 'E'.
371 sarakshi 16-jan-2002 Remove the logic of fetching subaccount_id from igs_fi_f_typ_ca_inst,
372 now fetching using function igs_fi_gen_007.get_subaccount_id,also rectified hard coded
373 message logging in log file, bug:2175865
374 Syam 24-8-2000 Logic changes
375 Schodava 4-12-2000 Removal of parameters Course_cd and Version_number
376 vchappid 12-Apr-2001 Modified Logic as per new Ancillary,External Charges DLD
377 ******************************************************************/
378
379 l_ext_status igs_fi_ext_int_all.status%TYPE;
380 l_cst_success CONSTANT igs_fi_ext_int_all.status%TYPE := 'SUCCESS';
381 l_cst_error CONSTANT igs_fi_ext_int_all.status%TYPE := 'ERROR';
382 l_cst_todo CONSTANT igs_fi_ext_int_all.status%TYPE := 'TODO';
383 l_b_ext_val_flag BOOLEAN ;
384
385 -- New variable added by sykrishn to validate DFFs bug 2324088
386 l_b_dff_validate BOOLEAN ;
387 -- Cursor for fetching records of TODO status
388 CURSOR cur_fei (cp_person_id igs_fi_ext_int.person_id%TYPE,
389 cp_fee_type igs_fi_ext_int.fee_type%TYPE,
390 cp_fee_cal_type igs_fi_ext_int.fee_cal_type%TYPE,
391 cp_fee_ci_sequence_number igs_fi_ext_int.fee_ci_sequence_number%TYPE) IS
392 SELECT *
393 FROM igs_fi_ext_int
394 WHERE (person_id = cp_person_id OR (cp_person_id IS NULL))
395 AND (fee_type = cp_fee_type OR (cp_fee_type IS NULL))
396 AND fee_cal_type = cp_fee_cal_type
397 AND fee_ci_sequence_number = cp_fee_ci_sequence_number
398 AND status IN (l_cst_error,l_cst_todo);
399
400
401 -- Variables added for the Charges API call
402 l_header_rec igs_fi_charges_api_pvt.Header_Rec_Type;
403 l_line_tbl igs_fi_charges_api_pvt.Line_Tbl_Type;
404 l_line_id_tbl igs_fi_charges_api_pvt.Line_Id_Tbl_Type;
405 l_invoice_id igs_fi_inv_int.invoice_id%TYPE;
406 l_v_return_status VARCHAR2(1);
407 l_n_msg_count NUMBER(3);
408 l_v_msg_data VARCHAR2(2000);
409 l_v_fee_desc VARCHAR2(100);
410 l_cur_fei cur_fei%ROWTYPE; -- Interface Table Row Type Variable
411 l_v_rec_installed VARCHAR2(1); -- To find which type of Accounts receivables are installed at client side
412 l_override_dr_rec_account_cd igs_fi_ext_int.override_dr_rec_account_cd%TYPE;
413 l_override_cr_rev_account_cd igs_fi_ext_int.override_cr_rev_account_cd%TYPE;
414 l_override_dr_rec_ccid igs_fi_ext_int.override_dr_rec_ccid%TYPE;
415 l_override_cr_rev_ccid igs_fi_ext_int.override_cr_rev_ccid%TYPE;
416 l_transaction_amount igs_fi_ext_int.transaction_amount%TYPE;
417
418 l_error_message fnd_new_messages.message_name%TYPE;
419 l_n_err_type NUMBER;
420 l_v_err_string igs_fi_invln_int.error_string%TYPE;
421 l_b_return_status BOOLEAN;
422
423 CURSOR cur_fee_desc(cp_fee_type igs_fi_fee_type.fee_type%TYPE) IS
424 SELECT description
425 FROM igs_fi_fee_type
426 WHERE fee_type = cp_fee_type;
427
428 l_n_record_count PLS_INTEGER :=0;
429 l_b_flag BOOLEAN := TRUE;
430
431 l_person_number hz_parties.party_number%TYPE;
432
433 CURSOR cur_fee_period(cp_cal_type igs_ca_inst.cal_type%TYPE,cp_sequence_number igs_ca_inst.sequence_number%TYPE) IS
434 SELECT igs_ge_date.igschar(start_dt) ||' '|| igs_ge_date.igschar(end_dt) string
435 FROM igs_ca_inst
436 WHERE cal_type=cp_cal_type
437 AND sequence_number=cp_sequence_number;
438
439 l_cur_fee_period cur_fee_period%ROWTYPE;
440 l_v_message_name fnd_new_messages.message_name%TYPE;
441 l_v_manage_accounts igs_fi_control.manage_accounts%TYPE;
442 l_exception EXCEPTION;
443
444 l_v_msg fnd_new_messages.message_text%TYPE;
445 l_b_valid_ccid BOOLEAN;
446
447 l_n_waiver_amount NUMBER;
448
449 BEGIN
450
451 -- Set the Org Id
452 igs_ge_gen_003.Set_Org_Id(p_org_id);
453
454 -- SYKRISHN -- Bug 2682928 -- Logging of parameters introduced.. 31DEC2002
455 IF p_person_id IS NOT NULL THEN
456 l_person_number := igs_fi_gen_008.get_party_number(p_person_id);
457 END IF;
458
459 OPEN cur_fee_period(p_fee_cal_type,p_fee_ci_sequence_number);
460 FETCH cur_fee_period INTO l_cur_fee_period;
461 CLOSE cur_fee_period;
462
463 fnd_file.put_line(fnd_file.log,RPAD('-',77,'-'));
464 fnd_message.set_name('IGS','IGS_FI_ANC_LOG_PARM');
465 fnd_file.put_line(fnd_file.log,fnd_message.get);
466
467 fnd_message.set_name('IGS', 'IGS_FI_IMP_CHGS_PARAMETER');
468 fnd_message.set_token('PARM_TYPE',igs_fi_gen_gl.get_lkp_meaning (p_v_lookup_type => 'IGS_FI_LOCKBOX',
469 p_v_lookup_code => 'PERSON') );
470 fnd_message.set_token('PARM_CODE',l_person_number);
471 fnd_file.put_line(fnd_file.log,fnd_message.get);
472
473
474 fnd_message.set_name('IGS','IGS_FI_IMP_CHGS_PARAMETER');
475 fnd_message.set_token('PARM_TYPE',igs_fi_gen_gl.get_lkp_meaning (p_v_lookup_type => 'IGS_FI_LOCKBOX',
476 p_v_lookup_code => 'FEE_TYPE') );
477 fnd_message.set_token('PARM_CODE',p_fee_type);
478 fnd_file.put_line(fnd_file.log,fnd_message.get);
479
480 fnd_message.set_name('IGS','IGS_FI_IMP_CHGS_PARAMETER');
481 fnd_message.set_token('PARM_TYPE',igs_fi_gen_gl.get_lkp_meaning (p_v_lookup_type => 'IGS_FI_LOCKBOX',
482 p_v_lookup_code => 'FEE_CAL_TYPE') );
483 fnd_message.set_token('PARM_CODE',p_fee_cal_type);
484 fnd_file.put_line(fnd_file.log,fnd_message.get);
485
486 fnd_message.set_name('IGS','IGS_FI_IMP_CHGS_PARAMETER');
487 fnd_message.set_token('PARM_TYPE',igs_fi_gen_gl.get_lkp_meaning (p_v_lookup_type => 'IGS_FI_LOCKBOX',
488 p_v_lookup_code => 'FEE_PERIOD') );
489 fnd_message.set_token('PARM_CODE',l_cur_fee_period.string);
490 fnd_file.put_line(fnd_file.log,fnd_message.get);
491 fnd_file.put_line(fnd_file.log,RPAD('-',77,'-'));
492
493 -- Get the value of "Manage Accounts" System Option value.
494 -- If this value is NULL then this process should error out.
495 igs_fi_com_rec_interface.chk_manage_account ( p_v_manage_acc => l_v_manage_accounts,
496 p_v_message_name => l_v_message_name );
497 IF l_v_manage_accounts IS NULL THEN
498 fnd_message.set_name ( 'IGS', l_v_message_name );
499 fnd_file.put_line(fnd_file.log,fnd_message.get);
500 RAISE l_exception;
501 END IF;
502
503 OPEN cur_fei ( p_person_id,
504 p_fee_type,
505 p_fee_cal_type,
506 p_fee_ci_sequence_number);
507 LOOP
508 FETCH cur_fei INTO l_cur_fei;
509 EXIT WHEN cur_fei%NOTFOUND;
510
511 l_b_ext_val_flag := TRUE;
512 l_cur_fei.error_msg := NULL;
513
514 -- Removed incrementing of record count from here to end of loop
515 -- Increment to be done only on successful import for the particular record
516 -- (pathipat) as part of bug 2737666
517
518 l_person_number := igs_fi_gen_008.get_party_number(l_cur_fei.person_id);
519
520 fnd_file.put_line(fnd_file.log,RPAD('-',77,'-'));
521 -- Checking which Account Receivables is installed at the client side and insert into corresponding columns accordingly
522 l_v_rec_installed := igs_fi_gen_005.finp_get_receivables_inst;
523
524 IF l_v_rec_installed = 'Y' THEN
525 l_override_dr_rec_account_cd := NULL;
526 l_override_cr_rev_account_cd := NULL;
527 l_override_dr_rec_ccid := l_cur_fei.override_dr_rec_ccid;
528 l_override_cr_rev_ccid := l_cur_fei.override_cr_rev_ccid;
529 ELSE
530 l_override_dr_rec_account_cd := l_cur_fei.override_dr_rec_account_cd;
531 l_override_cr_rev_account_cd := l_cur_fei.override_cr_rev_account_cd;
532 l_override_dr_rec_ccid := NULL;
533 l_override_cr_rev_ccid := NULL;
534 END IF;
535
536 l_b_valid_ccid := TRUE;
537
538 -- resetting global variable to set before it gets assigned for every record
539 g_curr_desc := NULL;
540 g_curr_cd := NULL;
541
542
543 -- Following code for logging details moved here, before validations begin
544 -- by pathipat for bug 2737666
545
546 -- (pathipat) Removed code inserting record into IGS_FI_IMP_CHGS and IGS_FI_IMPCHGS_LINES
547 -- as part of enh bug 2584986
548
549 l_transaction_amount := l_cur_fei.transaction_amount;
550
551 -- Used generic function to get lookup description, removed lookup_desc() function
552
553 fnd_message.set_name('IGS','IGS_FI_IMP_CHGS_PARAMETER');
554 fnd_message.set_token('PARM_TYPE', igs_fi_gen_gl.get_lkp_meaning (p_v_lookup_type => 'IGS_FI_LOCKBOX',
555 p_v_lookup_code => 'PERSON'));
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 => 'FEE_TYPE'));
562 fnd_message.set_token('PARM_CODE', l_cur_fei.fee_type);
563 fnd_file.put_line(fnd_file.log, fnd_message.get);
564
565 fnd_message.set_name('IGS', 'IGS_FI_IMP_CHGS_PARAMETER');
566 fnd_message.set_token('PARM_TYPE', igs_fi_gen_gl.get_lkp_meaning (p_v_lookup_type => 'IGS_FI_LOCKBOX',
567 p_v_lookup_code => 'AMOUNT'));
568 fnd_message.set_token('PARM_CODE', l_transaction_amount);
569 fnd_file.put_line(fnd_file.log, fnd_message.get);
570
571 fnd_message.set_name('IGS', 'IGS_FI_IMP_CHGS_PARAMETER');
572 fnd_message.set_token('PARM_TYPE', igs_fi_gen_gl.get_lkp_meaning (p_v_lookup_type => 'IGS_FI_LOCKBOX',
573 p_v_lookup_code => 'CURRENCY'));
574 -- Also changed to display currency code instead of description top accomodate error currency code also.
575 fnd_message.set_token('PARM_CODE', l_cur_fei.currency_cd);
576 fnd_file.put_line(fnd_file.log, fnd_message.get);
577 fnd_file.new_line(fnd_file.log);
578
579 -- Checking all Validations returns TRUE if all validations are passed else FALSE is returned
580 l_b_ext_val_flag := igs_fi_load_ext_chg.igs_fi_ext_val( p_person_id => l_cur_fei.person_id,
581 p_fee_type => l_cur_fei.fee_type,
582 p_fee_cal_type => l_cur_fei.fee_cal_type,
583 p_fee_ci_sequence_number => l_cur_fei.fee_ci_sequence_number,
584 p_transaction_dt => SYSDATE,
585 p_currency_cd => l_cur_fei.currency_cd,
586 p_d_gl_date => l_cur_fei.gl_date,
587 p_message_name => l_error_message);
588 IF NOT l_b_ext_val_flag THEN
589 IF l_error_message = 'IGS_FI_CUR_MISMATCH' THEN
590 fnd_message.set_name('IGS', l_error_message);
591 fnd_message.set_token('CUR1',l_cur_fei.currency_cd);
592 fnd_message.set_token('CUR2',g_curr_cd);
593 ELSIF l_error_message = 'IGS_FI_INVALID_GL_DATE' THEN
594 fnd_message.set_name('IGS', l_error_message);
595 fnd_message.set_token('GL_DATE',l_cur_fei.gl_date);
596 ELSE
597 fnd_message.set_name('IGS', l_error_message);
598 END IF;
599
600 l_v_msg := fnd_message.get;
601 fnd_file.put_line(fnd_file.log,l_v_msg);
602 l_cur_fei.error_msg := l_v_msg;
603 END IF;
604
605 -- Enh bug 2584986 Changed DFF name from IGS_FI_IMPCHGS_EXT_INT to IGS_FI_INVLN_INT_ALL_FLEX
606 l_b_dff_validate := validate_desc_flex ( p_attribute_category => l_cur_fei.attribute_category,
607 p_attribute1 => l_cur_fei.attribute1,
608 p_attribute2 => l_cur_fei.attribute2,
609 p_attribute3 => l_cur_fei.attribute3,
610 p_attribute4 => l_cur_fei.attribute4,
611 p_attribute5 => l_cur_fei.attribute5,
612 p_attribute6 => l_cur_fei.attribute6,
613 p_attribute7 => l_cur_fei.attribute7,
614 p_attribute8 => l_cur_fei.attribute8,
615 p_attribute9 => l_cur_fei.attribute9,
616 p_attribute10 => l_cur_fei.attribute10,
617 p_attribute11 => l_cur_fei.attribute11,
618 p_attribute12 => l_cur_fei.attribute12,
619 p_attribute13 => l_cur_fei.attribute13,
620 p_attribute14 => l_cur_fei.attribute14,
621 p_attribute15 => l_cur_fei.attribute15,
622 p_attribute16 => l_cur_fei.attribute16,
623 p_attribute17 => l_cur_fei.attribute17,
624 p_attribute18 => l_cur_fei.attribute18,
625 p_attribute19 => l_cur_fei.attribute19,
626 p_attribute20 => l_cur_fei.attribute20,
627 p_desc_flex_name => 'IGS_FI_INVLN_INT_ALL_FLEX'
628 );
629 IF NOT l_b_dff_validate THEN
630 l_b_ext_val_flag := FALSE;
631 fnd_message.set_name('IGS','IGS_AD_INVALID_DESC_FLEX');
632 l_v_msg := fnd_message.get;
633 fnd_file.put_line(fnd_file.log,l_v_msg);
634 l_cur_fei.error_msg := l_cur_fei.error_msg||'.'||l_v_msg;
635 END IF;
636
637 -- If the above validations are successful we want to check if the Code Combination ID CCIDs are valid
638 -- If CCIDs are invalid then sets l_b_ext_val_flag to FALSE so that no further processing happens
639 IF l_override_dr_rec_ccid IS NOT NULL THEN
640 IF NOT igs_fi_gen_002.finp_validate_ccid (l_override_dr_rec_ccid) THEN
641 l_b_valid_ccid := FALSE;
642 END IF;
643 ELSIF l_override_cr_rev_ccid IS NOT NULL THEN
644 IF NOT igs_fi_gen_002.finp_validate_ccid (l_override_cr_rev_ccid) THEN
645 l_b_valid_ccid := FALSE;
646 END IF;
647 END IF;
648
649 IF NOT l_b_valid_ccid THEN
650 l_b_ext_val_flag := FALSE;
651 fnd_message.set_name('IGS','IGS_FI_INVALID_CCID');
652 l_v_msg := fnd_message.get;
653 fnd_file.put_line(fnd_file.log,l_v_msg);
654 l_cur_fei.error_msg := l_cur_fei.error_msg||'.'||l_v_msg;
655 END IF;
656
657 IF l_b_ext_val_flag THEN
658 igs_fi_prc_acct_pkg.build_accounts( p_fee_type => l_cur_fei.fee_type,
659 p_fee_cal_type => l_cur_fei.fee_cal_type,
660 p_fee_ci_sequence_number => l_cur_fei.fee_ci_sequence_number,
661 p_course_cd => NULL,
662 p_course_version_number => NULL,
663 p_org_unit_cd => NULL,
664 p_org_start_dt => NULL,
665 p_unit_cd => NULL,
666 p_unit_version_number => NULL,
667 p_uoo_id => NULL,
668 p_location_cd => NULL,
669 p_transaction_type => 'CHARGE',
670 p_credit_type_id => NULL,
671 p_source_transaction_id => NULL,
672 x_dr_gl_ccid => l_override_dr_rec_ccid,
673 x_cr_gl_ccid => l_override_cr_rev_ccid,
674 x_dr_account_cd => l_override_dr_rec_account_cd,
675 x_cr_account_cd => l_override_cr_rev_account_cd,
676 x_err_type => l_n_err_type,
677 x_err_string => l_v_err_string,
678 x_ret_status => l_b_return_status,
679 p_v_attendance_type => NULL,
680 p_v_attendance_mode => NULL,
681 p_v_residency_status_cd => NULL,
682 p_n_unit_type_id => NULL,
683 p_v_unit_class => NULL,
684 p_v_unit_mode => NULL,
685 p_v_unit_level => NULL,
686 p_v_waiver_name => NULL
687 );
688 IF (NOT l_b_return_status) OR (l_n_err_type IS NOT NULL) THEN
689 l_b_ext_val_flag := FALSE;
690
691 -- Error from build_accounts procedure
692 -- Build Accounts Process will return error message name when the error type is 1
693 -- Will return message string when the error type is greater than 1
694 IF (l_v_err_string IS NOT NULL) THEN
695 IF (l_n_err_type = 1) THEN
696 fnd_message.set_name('IGS',l_v_err_string);
697 l_v_msg := fnd_message.get;
698 fnd_file.put_line(fnd_file.log,l_v_msg);
699 l_cur_fei.error_msg := l_v_msg;
700 ELSE
701 fnd_file.put_line(fnd_file.log, l_v_err_string);
702 l_cur_fei.error_msg := l_v_err_string;
703 END IF;
704 END IF;
705 END IF;
706 END IF;
707
708 --If all the above validations are passed, then only charges api is called.
709 IF l_b_ext_val_flag THEN
710
711 OPEN cur_fee_desc(l_cur_fei.fee_type);
712 FETCH cur_fee_desc INTO l_v_fee_desc;
713 CLOSE cur_fee_desc;
714
715 l_header_rec.p_person_id := l_cur_fei.person_id;
716 l_header_rec.p_fee_cal_type := l_cur_fei.fee_cal_type;
717 l_header_rec.p_fee_ci_sequence_number := l_cur_fei.fee_ci_sequence_number;
718 l_header_rec.p_fee_type := l_cur_fei.fee_type;
719 l_header_rec.p_invoice_amount := l_transaction_amount;
720 l_header_rec.p_invoice_creation_date := SYSDATE;
721 l_header_rec.p_invoice_desc := l_v_fee_desc;
722 l_header_rec.p_transaction_type := g_external;
723 l_header_rec.p_currency_cd := l_cur_fei.currency_cd;
724 l_header_rec.p_exchange_rate := 1 ; -- Exchange rate passed as 1 in invoking charges_api
725 l_header_rec.p_effective_date := NVL(l_cur_fei.effective_dt,SYSDATE);
726
727 l_line_tbl(1).p_description := l_v_fee_desc;
728 l_line_tbl(1).p_amount := l_transaction_amount;
729
730 l_line_tbl(1).p_attribute_category := l_cur_fei.attribute_category;
731 l_line_tbl(1).p_attribute1 := l_cur_fei.attribute1;
732 l_line_tbl(1).p_attribute2 := l_cur_fei.attribute2;
733 l_line_tbl(1).p_attribute3 := l_cur_fei.attribute3;
734 l_line_tbl(1).p_attribute4 := l_cur_fei.attribute4;
735 l_line_tbl(1).p_attribute5 := l_cur_fei.attribute5;
736 l_line_tbl(1).p_attribute6 := l_cur_fei.attribute6;
737 l_line_tbl(1).p_attribute7 := l_cur_fei.attribute7;
738 l_line_tbl(1).p_attribute8 := l_cur_fei.attribute8;
739 l_line_tbl(1).p_attribute9 := l_cur_fei.attribute9;
740 l_line_tbl(1).p_attribute10 := l_cur_fei.attribute10;
741 l_line_tbl(1).p_attribute11 := l_cur_fei.attribute11;
742 l_line_tbl(1).p_attribute12 := l_cur_fei.attribute12;
743 l_line_tbl(1).p_attribute13 := l_cur_fei.attribute13;
744 l_line_tbl(1).p_attribute14 := l_cur_fei.attribute14;
745 l_line_tbl(1).p_attribute15 := l_cur_fei.attribute15;
746 l_line_tbl(1).p_attribute16 := l_cur_fei.attribute16;
747 l_line_tbl(1).p_attribute17 := l_cur_fei.attribute17;
748 l_line_tbl(1).p_attribute18 := l_cur_fei.attribute18;
749 l_line_tbl(1).p_attribute19 := l_cur_fei.attribute19;
750 l_line_tbl(1).p_attribute20 := l_cur_fei.attribute20;
751
752 -- Added as part of GL Interface build
753 -- Start of modifications
754 l_line_tbl(1).p_d_gl_date := l_cur_fei.gl_date;
755
756 l_line_tbl(1).p_override_dr_rec_ccid := l_override_dr_rec_ccid;
757 l_line_tbl(1).p_override_cr_rev_ccid := l_override_cr_rev_ccid;
758 l_line_tbl(1).p_override_dr_rec_account_cd := l_override_dr_rec_account_cd;
759 l_line_tbl(1).p_override_cr_rev_account_cd := l_override_cr_rev_account_cd;
760
761 -- End of modifications due to GL build
762
763 igs_fi_charges_api_pvt.create_charge(p_api_version => 2.0,
764 p_init_msg_list => 'T',
765 p_commit => 'F',
766 p_header_rec => l_header_rec,
767 p_line_tbl => l_line_tbl,
768 x_line_id_tbl => l_line_id_tbl,
769 x_invoice_id => l_invoice_id,
770 x_return_status => l_v_return_status,
771 x_msg_count => l_n_msg_count,
772 x_msg_data => l_v_msg_data,
773 x_waiver_amount => l_n_waiver_amount);
774 IF l_v_return_status <> 'S' THEN
775 l_b_ext_val_flag := FALSE;
776 IF l_n_msg_count = 1 THEN
777 fnd_message.set_encoded(l_v_msg_data);
778 l_v_msg := fnd_message.get;
779 fnd_file.put_line(fnd_file.log,l_v_msg);
780 l_cur_fei.error_msg := l_v_msg;
781 ELSE
782 FOR l_var IN 1..l_n_msg_count LOOP
783 fnd_message.set_encoded(fnd_msg_pub.get);
784 l_v_msg := fnd_message.get;
785 fnd_file.put_line(fnd_file.log,l_v_msg);
786 l_cur_fei.error_msg := l_cur_fei.error_msg||'.'||l_v_msg;
787 END LOOP;
788 END IF;
789 END IF;
790 END IF;
791
792 IF l_b_ext_val_flag THEN
793 l_ext_status := l_cst_success;
794 l_cur_fei.error_msg := NULL;
795 ELSE
796 l_ext_status := l_cst_error;
797 END IF;
798
799 -- Updating the TODO record status in IGS_FI_EXT_INT_ALL table to SUCCESS once the data is moved.
800 --Modified by sarakshi, bug:2175865, now updating interface table if return status is success earlier code was
801 -- reverse of this
802 -- Added gl_Date in call to update_row
803
804 BEGIN
805 igs_fi_ext_int_pkg.update_row ( x_rowid => l_cur_fei.row_id,
806 x_external_fee_id => l_cur_fei.external_fee_id,
807 x_person_id => l_cur_fei.person_id,
808 x_status => l_ext_status,
809 x_fee_type => l_cur_fei.fee_type,
810 x_fee_cal_type => l_cur_fei.fee_cal_type,
811 x_fee_ci_sequence_number => l_cur_fei.fee_ci_sequence_number,
812 x_course_cd => l_cur_fei.course_cd,
813 x_crs_version_number => l_cur_fei.crs_version_number,
814 x_transaction_amount => l_cur_fei.transaction_amount,
815 x_currency_cd => l_cur_fei.currency_cd,
816 x_exchange_rate => l_cur_fei.exchange_rate,
817 x_effective_dt => l_cur_fei.effective_dt,
818 x_comments => l_cur_fei.comments,
819 x_logical_delete_dt => l_cur_fei.logical_delete_dt,
820 x_override_dr_rec_account_cd => l_cur_fei.override_dr_rec_account_cd,
821 x_override_dr_rec_ccid => l_cur_fei.override_dr_rec_ccid,
822 x_override_cr_rev_account_cd => l_cur_fei.override_cr_rev_account_cd,
823 x_override_cr_rev_ccid => l_cur_fei.override_cr_rev_ccid,
824 x_attribute_category => l_cur_fei.attribute_category,
825 x_attribute1 => l_cur_fei.attribute1,
826 x_attribute2 => l_cur_fei.attribute2,
827 x_attribute3 => l_cur_fei.attribute3,
828 x_attribute4 => l_cur_fei.attribute4,
829 x_attribute5 => l_cur_fei.attribute5,
830 x_attribute6 => l_cur_fei.attribute6,
831 x_attribute7 => l_cur_fei.attribute7,
832 x_attribute8 => l_cur_fei.attribute8,
833 x_attribute9 => l_cur_fei.attribute9,
834 x_attribute10 => l_cur_fei.attribute10,
835 x_attribute11 => l_cur_fei.attribute11,
836 x_attribute12 => l_cur_fei.attribute12,
837 x_attribute13 => l_cur_fei.attribute13,
838 x_attribute14 => l_cur_fei.attribute14,
839 x_attribute15 => l_cur_fei.attribute15,
840 x_attribute16 => l_cur_fei.attribute16,
841 x_attribute17 => l_cur_fei.attribute17,
842 x_attribute18 => l_cur_fei.attribute18,
843 x_attribute19 => l_cur_fei.attribute19,
844 x_attribute20 => l_cur_fei.attribute20,
845 x_mode => 'R',
846 x_gl_date => l_cur_fei.gl_date,
847 x_error_msg => SUBSTR(LTRIM(l_cur_fei.error_msg,'.'),1,2000)
848 ) ;
849 EXCEPTION
850 WHEN OTHERS THEN
851 l_b_flag := FALSE;
852 l_b_ext_val_flag := FALSE;
853 END;
854
855 --Added by sarakshi, bug:2175865
856 --If Entire transaction is successful then commit else rollback
857 IF l_b_flag = FALSE THEN
858 ROLLBACK;
859 ELSE
860 COMMIT;
861 END IF;
862
863 -- Records processed count to be done only on successful passing of all validations and import
864 -- Hence moved here from beginning of loop
865 -- part of bug 2737666
866 IF l_b_ext_val_flag THEN
867 l_n_record_count := l_n_record_count + 1;
868 END IF;
869 END LOOP;
870 CLOSE cur_fei;
871
872 fnd_file.put_line(fnd_file.log,fnd_message.get_string ('IGS','IGS_GE_TOTAL_REC_PROCESSED')||TO_CHAR(l_n_record_count));
873 fnd_file.put_line(fnd_file.log,RPAD('-',77,'-'));
874
875 EXCEPTION
876 WHEN l_exception THEN
877 retcode := 2;
878 WHEN OTHERS THEN
879 ROLLBACK;
880 retcode := 2;
881 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION')||' : '||SQLERRM;
882 igs_ge_msg_stack.add;
883 igs_ge_msg_stack.conc_exception_hndl;
884 END igs_fi_extto_imp;
885
886 FUNCTION get_std_formerstd_ind(p_person_id IN igs_pe_typ_instances_all.person_id%TYPE )
887 RETURN VARCHAR2 AS
888 /******************************************************************
889 Created By :Prasad marada
890 Date Created By :30-Nov-2004
891 Purpose :This procedure is called for validating if the
892 passed Person Id has an active type instance of
893 STUDENT or FORMER Student
894 Known limitations,
895 enhancements,
896 remarks :
897 Change History
898 Who When What
899
900 ********************************************************************/
901 l_n_count NUMBER;
902 l_v_output VARCHAR2(1);
903
904 CURSOR c_person_typ (cp_person_id igs_pe_typ_instances_all.person_id%TYPE) IS
905 SELECT 1
906 FROM igs_pe_typ_instances_all pti, igs_pe_person_types pty
907 WHERE pti.person_type_code = pty.person_type_code AND
908 pty.system_type IN ('STUDENT','FORMER_STUDENT') AND
909 pti.person_id = cp_person_id;
910
911 BEGIN
912 -- This funcation returns Y if person of type student or former student
913 -- else return N
914
915 OPEN c_person_typ (p_person_id);
916 FETCH c_person_typ INTO l_n_count;
917 IF c_person_typ%FOUND THEN
918 l_v_output := 'Y';
919 ELSE
920 l_v_output := 'N';
921 END IF;
922 CLOSE c_person_typ;
923 RETURN l_v_output;
924
925 END get_std_formerstd_ind;
926
927 -- end of package body
928 END igs_fi_load_ext_chg;
[Home] [Help]