[Home] [Help]
PACKAGE BODY: APPS.IGS_FI_UPG_RETENTION
Source
1 PACKAGE BODY igs_fi_upg_retention AS
2 /* $Header: IGSFI90B.pls 120.6 2006/05/04 07:53:45 abshriva noship $ */
3
4 /******************************************************************
5 Created By : Shirish Tatikonda
6 Date Created By : 11-DEC-2003
7 Purpose : Package Body for upgrade of Retention Charge Accounts.
8
9 Known limitations,enhancements,remarks:
10 Change History
11 Who When What
12 abshriva 4-May-2006 Bug 5178077: Modification done in PROCEDURE upg_accts
13 svuppala 30-MAY-2005 Enh 3442712 - Done the TBH modifications by adding
14 new columns Unit_Type_Id, Unit_Level in igs_fi_invln_int_all
15 shtatiko 11-DEC-2003 Bug# 3288973, Created this process
16 ***************************************************************** */
17
18 TYPE ret_invoice_rec IS RECORD( invoice_id igs_fi_inv_int_all.invoice_id%TYPE,
19 ftci_rec_account_cd igs_fi_f_typ_ca_inst.rec_account_cd%TYPE,
20 ftci_rec_gl_ccid igs_fi_f_typ_ca_inst.rec_gl_ccid%TYPE,
21 ftci_ret_account_cd igs_fi_f_typ_ca_inst.ret_account_cd%TYPE,
22 ftci_ret_gl_ccid igs_fi_f_typ_ca_inst.ret_gl_ccid%TYPE );
23 TYPE ret_invoice_tab_type IS TABLE OF ret_invoice_rec
24 INDEX BY BINARY_INTEGER;
25
26 FUNCTION find_invoice ( p_n_invoice_id igs_fi_inv_int_all.invoice_id%TYPE,
27 p_invoice_tab ret_invoice_tab_type) RETURN BOOLEAN IS
28
29 BEGIN
30
31 IF p_invoice_tab.COUNT > 0 THEN
32 FOR i IN p_invoice_tab.FIRST..p_invoice_tab.LAST LOOP
33 IF p_invoice_tab.EXISTS(i) THEN
34 IF p_invoice_tab(i).invoice_id = p_n_invoice_id THEN
35 RETURN TRUE;
36 END IF;
37 END IF;
38 END LOOP;
39 END IF;
40
41 RETURN FALSE;
42
43 END find_invoice;
44
45 FUNCTION get_credit_class( p_n_credit_id igs_fi_credits_all.credit_id%TYPE ) RETURN VARCHAR2 IS
46
47 -- bug 5018036 : joined IGS_FI_CREDITS_ALL , IGS_FI_CR_TYPES instead using IGS_FI_CREDITS_V
48 CURSOR c_crd_class (cp_n_credit_id igs_fi_credits_all.credit_id%TYPE) IS
49 SELECT credit_class credit_class_code
50 FROM igs_fi_credits_all crd, igs_fi_cr_types ct
51 WHERE crd.credit_id = cp_n_credit_id AND
52 ct.credit_type_id=crd.credit_type_id;
53 rec_crd_class c_crd_class%ROWTYPE;
54
55
56 BEGIN
57
58 OPEN c_crd_class ( p_n_credit_id );
59 FETCH c_crd_class INTO rec_crd_class;
60 CLOSE c_crd_class;
61
62 RETURN rec_crd_class.credit_class_code;
63
64 END get_credit_class;
65
66 FUNCTION get_credit_number( p_n_credit_id igs_fi_credits_all.credit_id%TYPE ) RETURN VARCHAR2 IS
67
68 CURSOR c_crd_number (cp_n_credit_id igs_fi_credits_all.credit_id%TYPE) IS
69 SELECT credit_number
70 FROM igs_fi_credits_all
71 WHERE credit_id = cp_n_credit_id;
72 rec_crd_number c_crd_number%ROWTYPE;
73
74 BEGIN
75
76 OPEN c_crd_number ( p_n_credit_id );
77 FETCH c_crd_number INTO rec_crd_number;
78 CLOSE c_crd_number;
79
80 RETURN rec_crd_number.credit_number;
81
82 END get_credit_number;
83
84 PROCEDURE upg_accts(errbuf OUT NOCOPY VARCHAR2,
85 retcode OUT NOCOPY NUMBER ) AS
86 /******************************************************************
87 Created By : Shirish Tatikonda
88 Date Created By : 11-DEC-2003
89 Purpose : Main procedure for upgrade of Retention Charge Accounts.
90
91 Known limitations,enhancements,remarks:
92 Change History
93 Who When What
94 abshriva 4-May-2006 Bug 5178077: Introduced igs_ge_gen_003.set_org_id
95 skharida 13-Feb-2006 APPSPERF: bug 5018036, Replaced igs_fi_f_typ_ca_inst_lkp_v by a join of igs_fi_f_typ_ca_inst and igs_ca_inst
96 svuppala 30-MAY-2005 Enh 3442712 - Done the TBH modifications by adding
97 new columns Unit_Type_Id, Unit_Level in igs_fi_invln_int_all
98
99 shtatiko 11-DEC-2003 Bug# 3288973, Created this process
100 ***************************************************************** */
101
102 -- Fetch Receivables Account Information from System Options Level
103 CURSOR c_sys_opt_rec_acct IS
104 SELECT rec_installed, rec_gl_ccid, rec_account_cd, accounting_method
105 FROM igs_fi_control_all;
106
107 -- Bug 5018036, SQL ID 14794917: replaced igs_fi_f_typ_ca_inst_lkp_v by a join of igs_fi_f_typ_ca_inst and igs_ca_inst
108 CURSOR c_upd_charges IS
109 SELECT a.invoice_id,
110 a.fee_type,
111 a.fee_cal_type,
112 a.fee_ci_sequence_number,
113 ci.start_dt,
114 ci.end_dt,
115 b.rec_gl_ccid inv_rec_gl_ccid,
116 b.rec_account_cd inv_rec_account_cd,
117 b.rev_gl_ccid inv_rev_gl_ccid,
118 b.rev_account_cd inv_rev_account_cd,
119 c.rec_gl_ccid ftci_rec_gl_ccid,
120 c.rec_account_cd ftci_rec_account_cd,
121 c.ret_gl_ccid ftci_ret_gl_ccid,
122 c.ret_account_cd ftci_ret_account_cd
123 FROM igs_fi_inv_int_all a,
124 igs_fi_invln_int_all b,
125 igs_fi_f_typ_ca_inst c,
126 igs_ca_inst ci
127 WHERE a.invoice_id = b.invoice_id
128 AND a.transaction_type = 'RETENTION'
129 AND b.error_account = 'Y'
130 AND a.fee_type = c.fee_type
131 AND a.fee_cal_type = c.fee_cal_type
132 AND a.fee_ci_sequence_number = c.fee_ci_sequence_number
133 And ci.cal_type=c.fee_cal_type
134 And ci.sequence_number = c.fee_ci_sequence_number
135 ORDER BY a.fee_type, a.fee_cal_type, a.fee_ci_sequence_number;
136
137 -- Bug 5018036: Instead using igs_fi_f_typ_ca_inst_lkp_v joined igs_fi_f_typ_ca_inst and igs_ca_inst
138 CURSOR c_upd_applications IS
139 SELECT app.invoice_id ,
140 app.application_id ,
141 inv.fee_type ,
142 inv.fee_cal_type ,
143 inv.fee_ci_sequence_number ,
144 app.dr_gl_code_ccid app_dr_gl_code_ccid,
145 app.dr_account_cd app_dr_account_cd,
146 app.cr_gl_code_ccid app_cr_gl_code_ccid,
147 app.cr_account_cd app_cr_account_cd,
148 ftci.rec_gl_ccid ftci_rec_gl_ccid,
149 ftci.rec_account_cd ftci_rec_account_cd,
150 ftci.ret_gl_ccid ftci_ret_gl_ccid,
151 ftci.ret_account_cd ftci_ret_account_cd,
152 ci.start_dt,
153 ci.end_dt
154 FROM igs_fi_applications app,
155 igs_fi_inv_int_all inv,
156 igs_fi_f_typ_ca_inst ftci,
157 igs_ca_inst ci
158 WHERE app.invoice_id = inv.invoice_id
159 AND inv.transaction_type = 'RETENTION'
160 AND inv.fee_type = ftci.fee_type
161 AND inv.fee_cal_type = ftci.fee_cal_type
162 AND inv.fee_ci_sequence_number = ftci.fee_ci_sequence_number
163 AND ((app.dr_gl_code_ccid IS NULL AND app.dr_account_cd IS NULL)
164 OR (app.cr_gl_code_ccid IS NULL AND app.cr_account_cd IS NULL)
165 )
166 And ci.cal_type=ftci.fee_cal_type
167 And ci.sequence_number = ftci.fee_ci_sequence_number
168 ORDER BY inv.fee_type, inv.fee_cal_type, inv.fee_ci_sequence_number;
169
170
171 CURSOR c_invoice_details ( cp_n_invoice_id igs_fi_inv_int_all.invoice_id%TYPE ) IS
172 SELECT ROWID, inv.*
173 FROM igs_fi_invln_int_all inv
174 WHERE invoice_id = cp_n_invoice_id;
175 rec_invoice_details c_invoice_details%ROWTYPE;
176
177 CURSOR c_application_details ( cp_n_invoice_id igs_fi_inv_int_all.invoice_id%TYPE ) IS
178 SELECT ROWID, app.*
179 FROM igs_fi_applications app
180 WHERE invoice_id = cp_n_invoice_id;
181
182 CURSOR c_cr_act_details ( cp_n_credit_id igs_fi_credits_all.credit_id%TYPE ) IS
183 SELECT ROWID, crd.*
184 FROM igs_fi_cr_activities crd
185 WHERE credit_id = cp_n_credit_id;
186 rec_cr_act_details c_cr_act_details%ROWTYPE;
187
188 CURSOR c_gl_interface ( cp_v_ref_23 VARCHAR2, cp_v_ref_30 VARCHAR2) IS
189 SELECT rowid, accounted_cr, accounted_dr, code_combination_id
190 FROM gl_interface
191 WHERE reference23 = cp_v_ref_23
192 AND reference30 = cp_v_ref_30;
193
194 CURSOR c_posting_int ( cp_n_source_tran_id NUMBER, cp_v_source_tran_type VARCHAR2) IS
195 SELECT ROWID, post.*
196 FROM igs_fi_posting_int_all post
197 WHERE source_transaction_id = cp_n_source_tran_id
198 AND source_transaction_type = cp_v_source_tran_type;
199
200 l_v_rec_installed igs_fi_control_all.rec_installed%TYPE;
201 l_v_accounting_method igs_fi_control_all.accounting_method%TYPE;
202
203 l_v_fee_type igs_fi_inv_int_all.fee_type%TYPE;
204 l_v_fee_cal_type igs_fi_inv_int_all.fee_cal_type%TYPE;
205 l_n_fee_ci_sequence_number igs_fi_inv_int_all.fee_ci_sequence_number%TYPE;
206
207 l_n_rec_gl_ccid_sys igs_fi_control_all.rec_gl_ccid%TYPE;
208 l_n_upd_rec_gl_ccid igs_fi_control_all.rec_gl_ccid%TYPE;
209 l_n_upd_rev_gl_ccid igs_fi_control_all.rec_gl_ccid%TYPE;
210 l_n_upd_dr_gl_ccid igs_fi_control_all.rec_gl_ccid%TYPE;
211 l_n_upd_cr_gl_code_ccid igs_fi_control_all.rec_gl_ccid%TYPE;
212
213 l_v_rec_account_cd_sys igs_fi_control_all.rec_account_cd%TYPE;
214 l_v_upd_rec_account_cd igs_fi_control_all.rec_account_cd%TYPE;
215 l_v_upd_rev_account_cd igs_fi_control_all.rec_account_cd%TYPE;
216 l_v_upd_dr_account_cd igs_fi_control_all.rec_account_cd%TYPE;
217 l_v_upd_cr_account_cd igs_fi_control_all.rec_account_cd%TYPE;
218
219 l_b_sys_opt_defined BOOLEAN := FALSE;
220 l_b_invoice_found BOOLEAN := FALSE;
221 l_b_no_ftci_setup BOOLEAN := FALSE;
222 l_b_upd BOOLEAN := FALSE;
223 l_b_rec_setup BOOLEAN := FALSE;
224 l_b_ret_setup BOOLEAN := FALSE;
225 l_b_upgrade_done BOOLEAN := FALSE;
226 l_org_id VARCHAR2(15);
227 l_n_cntr NUMBER;
228
229 l_v_lkp_fee_type igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'FEE_TYPE');
230 l_v_lkp_fee_cal_type igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'FEE_CAL_TYPE');
231 l_v_lkp_start_dt igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'START_DT');
232 l_v_lkp_end_dt igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'END_DT');
233
234 l_v_lkp_dr_account igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'DR_ACCOUNT');
235 l_v_lkp_cr_account igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'CR_ACCOUNT');
236
237 l_v_lkp_charge_number igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'CHARGE_NUMBER');
238 l_v_lkp_credit_number igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'CREDIT_NUMBER');
239 l_v_lkp_application_id igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'APPLICATION_ID');
240
241 l_v_lkp_status igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('IGS_FI_LOCKBOX', 'STATUS');
242 l_v_lkp_error igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('STATUS', 'ERROR');
243 l_v_lkp_success igs_lookup_values.meaning%TYPE := igs_fi_gen_gl.get_lkp_meaning('STATUS', 'SUCCESS');
244
245 ret_invoice_tab ret_invoice_tab_type;
246
247 BEGIN
248 BEGIN
249 l_org_id := NULL;
250 igs_ge_gen_003.set_org_id(l_org_id);
251 EXCEPTION
252 WHEN OTHERS THEN
253 fnd_file.put_line (fnd_file.log, fnd_message.get);
254 retcode:=2;
255 RETURN;
256 END;
257 l_b_no_ftci_setup := FALSE;
258 ret_invoice_tab.DELETE;
259
260 OPEN c_sys_opt_rec_acct;
261 FETCH c_sys_opt_rec_acct INTO l_v_rec_installed,
262 l_n_rec_gl_ccid_sys,
263 l_v_rec_account_cd_sys,
264 l_v_accounting_method;
265 CLOSE c_sys_opt_rec_acct;
266
267 IF l_n_rec_gl_ccid_sys IS NULL AND l_v_rec_account_cd_sys IS NULL THEN
268 l_b_sys_opt_defined := FALSE;
269 ELSE
270 l_b_sys_opt_defined := TRUE;
271 END IF;
272
273 ----------------------------------------------------------------------------------------------
274 -- En-list all FTCIs, which does not have Receivables or Retention or both account information.
275 ----------------------------------------------------------------------------------------------
276
277 -- Process Charge Records to en-list
278
279 l_n_cntr := 0;
280 l_v_fee_type := 'NULL';
281 l_v_fee_cal_type := 'NULL';
282 l_n_fee_ci_sequence_number := 0;
283 l_b_ret_setup := FALSE;
284 l_b_rec_setup := FALSE;
285
286 FOR rec_upd_charges IN c_upd_charges LOOP
287 IF (l_v_fee_type <> rec_upd_charges.fee_type OR
288 l_v_fee_cal_type <> rec_upd_charges.fee_cal_type OR
289 l_n_fee_ci_sequence_number <> rec_upd_charges.fee_ci_sequence_number) THEN
290
291 -- Check whether Receivables setup is there
292 IF (l_b_sys_opt_defined = FALSE AND rec_upd_charges.ftci_rec_gl_ccid IS NULL AND rec_upd_charges.ftci_rec_account_cd IS NULL) THEN
293 l_b_rec_setup := TRUE;
294 END IF;
295
296 -- Check if Retention account setup is there at FTCI
297 IF (rec_upd_charges.ftci_ret_gl_ccid IS NULL AND rec_upd_charges.ftci_ret_account_cd IS NULL ) THEN
298 l_b_ret_setup := TRUE;
299 END IF;
300
301 IF l_b_rec_setup OR l_b_ret_setup THEN
302 l_b_no_ftci_setup := TRUE;
303
304 fnd_file.new_line(fnd_file.LOG );
305 fnd_file.put_line(fnd_file.LOG, l_v_lkp_fee_type || ' : ' || rec_upd_charges.fee_type );
306 fnd_file.put_line(fnd_file.LOG, l_v_lkp_fee_cal_type || ' : ' || rec_upd_charges.fee_cal_type );
307 fnd_file.put_line(fnd_file.LOG, l_v_lkp_start_dt || ' : ' || rec_upd_charges.start_dt );
308 fnd_file.put_line(fnd_file.LOG, l_v_lkp_end_dt || ' : ' || rec_upd_charges.end_dt );
309
310 IF l_b_rec_setup THEN
311 fnd_file.put_line( fnd_file.LOG, fnd_message.get_string('IGS', 'IGS_FI_NO_REC_ACCT_CD_FTCI') );
312 END IF;
313
314 IF l_b_ret_setup THEN
315 fnd_file.put_line( fnd_file.LOG, fnd_message.get_string('IGS', 'IGS_FI_NO_RETENTION_ACC') );
316 END IF;
317 END IF;
318 l_b_ret_setup := FALSE;
319 l_b_rec_setup := FALSE;
320 END IF;
321 l_v_fee_type := rec_upd_charges.fee_type;
322 l_v_fee_cal_type := rec_upd_charges.fee_cal_type;
323 l_n_fee_ci_sequence_number := rec_upd_charges.fee_ci_sequence_number;
324
325 ret_invoice_tab(l_n_cntr).invoice_id := rec_upd_charges.invoice_id;
326 ret_invoice_tab(l_n_cntr).ftci_rec_gl_ccid := rec_upd_charges.ftci_rec_gl_ccid;
327 ret_invoice_tab(l_n_cntr).ftci_rec_account_cd := rec_upd_charges.ftci_rec_account_cd;
328 ret_invoice_tab(l_n_cntr).ftci_ret_gl_ccid := rec_upd_charges.ftci_ret_gl_ccid;
329 ret_invoice_tab(l_n_cntr).ftci_ret_account_cd := rec_upd_charges.ftci_ret_account_cd;
330 l_n_cntr := l_n_cntr + 1;
331 END LOOP;
332
333 -- Process Application records to en-list
334
335 l_v_fee_type := 'NULL';
336 l_v_fee_cal_type := 'NULL';
337 l_n_fee_ci_sequence_number := 0;
338 l_b_ret_setup := FALSE;
339 l_b_rec_setup := FALSE;
340
341 FOR rec_upd_applications IN c_upd_applications LOOP
342
343 l_b_invoice_found := find_invoice(rec_upd_applications.invoice_id, ret_invoice_tab);
344
345 -- If the invoice is already there in the table, proceed with the next application record.
346 IF l_b_invoice_found = FALSE THEN
347
348 IF (l_v_fee_type <> rec_upd_applications.fee_type
349 OR l_v_fee_cal_type <> rec_upd_applications.fee_cal_type
350 OR l_n_fee_ci_sequence_number <> rec_upd_applications.fee_ci_sequence_number) THEN
351
352 -- Check whether Receivables setup is there
353 IF (l_b_sys_opt_defined = FALSE AND rec_upd_applications.ftci_rec_gl_ccid IS NULL AND rec_upd_applications.ftci_rec_account_cd IS NULL) THEN
354 l_b_rec_setup := TRUE;
355 END IF;
356
357 -- Check if Retention account setup is there at FTCI
358 IF (rec_upd_applications.ftci_ret_gl_ccid IS NULL AND rec_upd_applications.ftci_ret_account_cd IS NULL ) THEN
359 l_b_ret_setup := TRUE;
360 END IF;
361
362 IF l_b_rec_setup OR l_b_ret_setup THEN
363 l_b_no_ftci_setup := TRUE;
364
365 fnd_file.new_line(fnd_file.LOG );
366 fnd_file.put_line(fnd_file.LOG, l_v_lkp_fee_type || ' : ' || rec_upd_applications.fee_type );
367 fnd_file.put_line(fnd_file.LOG, l_v_lkp_fee_cal_type || ' : ' || rec_upd_applications.fee_cal_type );
368 fnd_file.put_line(fnd_file.LOG, l_v_lkp_start_dt || ' : ' || rec_upd_applications.start_dt );
369 fnd_file.put_line(fnd_file.LOG, l_v_lkp_end_dt || ' : ' || rec_upd_applications.end_dt );
370
371 IF l_b_rec_setup THEN
372 fnd_file.put_line( fnd_file.LOG, fnd_message.get_string('IGS', 'IGS_FI_NO_REC_ACCT_CD_FTCI') );
373 END IF;
374
375 IF l_b_ret_setup THEN
376 fnd_file.put_line( fnd_file.LOG, fnd_message.get_string('IGS', 'IGS_FI_NO_RETENTION_ACC') );
377 END IF;
378 END IF;
379 l_b_ret_setup := FALSE;
380 l_b_rec_setup := FALSE;
381 END IF;
382
383 l_v_fee_type := rec_upd_applications.fee_type;
384 l_v_fee_cal_type := rec_upd_applications.fee_cal_type;
385 l_n_fee_ci_sequence_number := rec_upd_applications.fee_ci_sequence_number;
386
387 ret_invoice_tab(l_n_cntr).invoice_id := rec_upd_applications.invoice_id;
388 ret_invoice_tab(l_n_cntr).ftci_rec_gl_ccid := rec_upd_applications.ftci_rec_gl_ccid;
389 ret_invoice_tab(l_n_cntr).ftci_rec_account_cd := rec_upd_applications.ftci_rec_account_cd;
390 ret_invoice_tab(l_n_cntr).ftci_ret_gl_ccid := rec_upd_applications.ftci_ret_gl_ccid;
391 ret_invoice_tab(l_n_cntr).ftci_ret_account_cd := rec_upd_applications.ftci_ret_account_cd;
392 l_n_cntr := l_n_cntr + 1;
393 END IF; /* l_b_invoice_found */
394 END LOOP;
395
396 -- See whether you have any records to Upgrade
397 IF ret_invoice_tab.COUNT = 0 THEN
398 l_b_upgrade_done := TRUE;
399 fnd_file.new_line(fnd_file.LOG );
400 fnd_file.put_line( fnd_file.LOG, fnd_message.get_string('IGS', 'IGS_GE_NO_DATA_FOUND'));
401 fnd_file.new_line(fnd_file.LOG );
402 END IF;
403
404 -----------------------
405 -- Actual Upgrade Logic
406 -----------------------
407
408 -- Upgrade needs to be done only if we don't find any FTCIs with missing information.
409 IF l_b_no_ftci_setup = FALSE AND ret_invoice_tab.COUNT > 0 THEN
410 FOR l_n_cntr IN ret_invoice_tab.FIRST..ret_invoice_tab.LAST LOOP
411 IF ret_invoice_tab.EXISTS(l_n_cntr) THEN
412
413 -- ====== UPDATE Invoice Line Record. ====== --
414
415 OPEN c_invoice_details( ret_invoice_tab(l_n_cntr).invoice_id );
416 FETCH c_invoice_details INTO rec_invoice_details;
417 CLOSE c_invoice_details;
418
419 l_v_upd_rec_account_cd := NVL(NVL(rec_invoice_details.rec_account_cd, ret_invoice_tab(l_n_cntr).ftci_rec_account_cd), l_v_rec_account_cd_sys);
420 l_n_upd_rec_gl_ccid := NVL( NVL(rec_invoice_details.rec_gl_ccid, ret_invoice_tab(l_n_cntr).ftci_rec_gl_ccid), l_n_rec_gl_ccid_sys);
421 l_v_upd_rev_account_cd := NVL(rec_invoice_details.rev_account_cd, ret_invoice_tab(l_n_cntr).ftci_ret_account_cd);
422 l_n_upd_rev_gl_ccid := NVL(rec_invoice_details.rev_gl_ccid, ret_invoice_tab(l_n_cntr).ftci_ret_gl_ccid);
423
424 l_b_upd := FALSE;
425
426 IF l_v_rec_installed = 'Y' THEN
427 IF (((rec_invoice_details.rev_gl_ccid <> l_n_upd_rev_gl_ccid) OR (rec_invoice_details.rev_gl_ccid IS NULL)) OR
428 ((rec_invoice_details.rec_gl_ccid <> l_n_upd_rec_gl_ccid) OR (rec_invoice_details.rec_gl_ccid IS NULL))) THEN
429 l_b_upd := TRUE;
430 END IF;
431 ELSE
432 IF (((rec_invoice_details.rev_account_cd <> l_v_upd_rev_account_cd) OR (rec_invoice_details.rev_account_cd IS NULL)) OR
433 ((rec_invoice_details.rec_account_cd <> l_v_upd_rec_account_cd) OR (rec_invoice_details.rec_account_cd IS NULL))) THEN
434 l_b_upd := TRUE;
435 END IF;
436 END IF;
437
438 IF l_b_upd THEN
439 l_b_upgrade_done := TRUE;
440 igs_fi_invln_int_pkg.update_row( x_rowid => rec_invoice_details.rowid,
441 x_invoice_id => rec_invoice_details.invoice_id,
442 x_line_number => rec_invoice_details.line_number,
443 x_invoice_lines_id => rec_invoice_details.invoice_lines_id,
444 x_attribute2 => rec_invoice_details.attribute2,
445 x_chg_elements => rec_invoice_details.chg_elements,
446 x_amount => rec_invoice_details.amount,
447 x_unit_attempt_status => rec_invoice_details.unit_attempt_status,
448 x_eftsu => rec_invoice_details.eftsu,
449 x_credit_points => rec_invoice_details.credit_points,
450 x_attribute_category => rec_invoice_details.attribute_category,
451 x_attribute1 => rec_invoice_details.attribute1,
452 x_s_chg_method_type => rec_invoice_details.s_chg_method_type,
453 x_description => rec_invoice_details.description,
454 x_attribute3 => rec_invoice_details.attribute3,
455 x_attribute4 => rec_invoice_details.attribute4,
456 x_attribute5 => rec_invoice_details.attribute5,
457 x_attribute6 => rec_invoice_details.attribute6,
458 x_attribute7 => rec_invoice_details.attribute7,
459 x_attribute8 => rec_invoice_details.attribute8,
460 x_attribute9 => rec_invoice_details.attribute9,
461 x_attribute10 => rec_invoice_details.attribute10,
462 x_rec_account_cd => l_v_upd_rec_account_cd,
463 x_rev_account_cd => l_v_upd_rev_account_cd,
464 x_rec_gl_ccid => l_n_upd_rec_gl_ccid,
465 x_rev_gl_ccid => l_n_upd_rev_gl_ccid,
466 x_org_unit_cd => rec_invoice_details.org_unit_cd,
467 x_posting_id => rec_invoice_details.posting_id,
468 x_attribute11 => rec_invoice_details.attribute11,
469 x_attribute12 => rec_invoice_details.attribute12,
470 x_attribute13 => rec_invoice_details.attribute13,
471 x_attribute14 => rec_invoice_details.attribute14,
472 x_attribute15 => rec_invoice_details.attribute15,
473 x_attribute16 => rec_invoice_details.attribute16,
474 x_attribute17 => rec_invoice_details.attribute17,
475 x_attribute18 => rec_invoice_details.attribute18,
476 x_attribute19 => rec_invoice_details.attribute19,
477 x_attribute20 => rec_invoice_details.attribute20,
478 x_error_string => rec_invoice_details.error_string,
479 x_error_account => 'N',
480 x_location_cd => rec_invoice_details.location_cd,
481 x_uoo_id => rec_invoice_details.uoo_id,
482 x_gl_date => rec_invoice_details.gl_date,
483 x_gl_posted_date => rec_invoice_details.gl_posted_date,
484 x_posting_control_id => rec_invoice_details.posting_control_id,
485 x_mode => 'R',
486 x_unit_type_id => rec_invoice_details.unit_type_id,
487 x_unit_level => rec_invoice_details.unit_level
488 );
489
490 fnd_file.new_line(fnd_file.LOG );
491 fnd_file.put_line(fnd_file.LOG, l_v_lkp_charge_number || ': ' || igs_fi_gen_008.get_invoice_number(rec_invoice_details.invoice_id) );
492 fnd_file.put_line(fnd_file.LOG, l_v_lkp_dr_account || ': ' || NVL(igs_fi_gen_007.get_ccid_concat(l_n_upd_rec_gl_ccid), l_v_upd_rec_account_cd) );
493 fnd_file.put_line(fnd_file.LOG, l_v_lkp_cr_account || ': ' || NVL(igs_fi_gen_007.get_ccid_concat(l_n_upd_rev_gl_ccid), l_v_upd_rev_account_cd) );
494 fnd_file.put_line(fnd_file.LOG, l_v_lkp_status || ' : ' || l_v_lkp_success );
495
496 -- Check if this charge has been posted to GL Interface or Posting Interface
497 IF rec_invoice_details.posting_control_id IS NOT NULL THEN
498 IF l_v_rec_installed = 'Y' THEN
499 FOR rec_gl_interface IN c_gl_interface( rec_invoice_details.invoice_lines_id, 'IGS_FI_INVLN_INT' ) LOOP
500
501 -- Update GL Interface with above derived accounting information
502 IF rec_gl_interface.accounted_dr IS NOT NULL
503 AND rec_gl_interface.code_combination_id IS NULL THEN
504
505 UPDATE gl_interface
506 SET code_combination_id = NVL(code_combination_id, l_n_upd_rec_gl_ccid)
507 WHERE rowid = rec_gl_interface.rowid;
508 l_b_upgrade_done := TRUE;
509
510 ELSIF rec_gl_interface.accounted_cr IS NOT NULL
511 AND rec_gl_interface.code_combination_id IS NULL THEN
512
513 UPDATE gl_interface
514 SET code_combination_id = NVL(code_combination_id, l_n_upd_rev_gl_ccid)
515 WHERE rowid = rec_gl_interface.rowid;
516 l_b_upgrade_done := TRUE;
517
518 END IF;
519
520 END LOOP;
521
522 ELSE -- GL is not Installed, so Check Posting Interface Table
523 FOR rec_posting_int IN c_posting_int( rec_invoice_details.invoice_lines_id, 'CHARGE' ) LOOP
524 IF rec_posting_int.dr_account_cd IS NULL
525 OR rec_posting_int.cr_account_cd IS NULL THEN
526 l_b_upgrade_done := TRUE;
527 igs_fi_posting_int_pkg.update_row (
528 x_rowid => rec_posting_int.ROWID,
529 x_posting_control_id => rec_posting_int.posting_control_id,
530 x_posting_id => rec_posting_int.posting_id,
531 x_batch_name => rec_posting_int.batch_name,
532 x_accounting_date => rec_posting_int.accounting_date,
533 x_transaction_date => rec_posting_int.transaction_date,
534 x_currency_cd => rec_posting_int.currency_cd,
535 x_dr_account_cd => NVL(rec_posting_int.dr_account_cd, l_v_upd_rec_account_cd),
536 x_cr_account_cd => NVL(rec_posting_int.cr_account_cd, l_v_upd_rev_account_cd),
537 x_dr_gl_code_ccid => rec_posting_int.dr_gl_code_ccid,
538 x_cr_gl_code_ccid => rec_posting_int.cr_gl_code_ccid,
539 x_amount => rec_posting_int.amount,
540 x_source_transaction_id => rec_posting_int.source_transaction_id,
541 x_source_transaction_type => rec_posting_int.source_transaction_type,
542 x_status => rec_posting_int.status,
543 x_orig_appl_fee_ref => rec_posting_int.orig_appl_fee_ref,
544 x_mode => 'R');
545
546 END IF;
547 END LOOP;
548 END IF; /* GL Installed */
549 END IF; /* Posting Control ID */
550 END IF; /* End If for l_b_upd */
551
552 -- ====== UPDATE Applications Records. ====== --
553
554 FOR rec_application_details IN c_application_details ( ret_invoice_tab(l_n_cntr).invoice_id ) LOOP
555
556 -- === Regular Payments/Credits applied to Rentention Charges - Type 1
557 -- === Targeted Applications of the Negative Charge Adjustments - Type 2
558
559 IF l_v_accounting_method = 'ACCRUAL' THEN
560
561 /*
562 === Debit side: Unapplied ( From Credit ) -- Need not be upgraded as it comes from Credit Types
563 === Credit side: Receivables ( From Charge )
564 */
565 l_v_upd_cr_account_cd := NVL(NVL(rec_application_details.cr_account_cd, ret_invoice_tab(l_n_cntr).ftci_rec_account_cd), l_v_rec_account_cd_sys);
566 l_n_upd_cr_gl_code_ccid := NVL( NVL(rec_application_details.cr_gl_code_ccid, ret_invoice_tab(l_n_cntr).ftci_rec_gl_ccid), l_n_rec_gl_ccid_sys);
567
568 ELSIF l_v_accounting_method = 'CASH' THEN
569
570 /*
571 === Debit side: Unapplied ( From Credit ) -- Need not be upgraded as it comes from Credit Types
572 === Credit side: Revenue ( From Charge )
573 */
574 l_v_upd_cr_account_cd := NVL(rec_application_details.cr_account_cd, ret_invoice_tab(l_n_cntr).ftci_ret_account_cd);
575 l_n_upd_cr_gl_code_ccid := NVL(rec_application_details.cr_gl_code_ccid, ret_invoice_tab(l_n_cntr).ftci_ret_gl_ccid);
576
577 END IF; /* Accounting Method */
578
579 l_b_upd := FALSE;
580
581 IF l_v_rec_installed = 'Y' THEN
582 IF ((rec_application_details.cr_gl_code_ccid <> l_n_upd_cr_gl_code_ccid) OR (rec_application_details.cr_gl_code_ccid IS NULL)) THEN
583 l_b_upd := TRUE;
584 END IF;
585 ELSE
586 IF ((rec_application_details.cr_account_cd <> l_v_upd_cr_account_cd) OR (rec_application_details.cr_account_cd IS NULL)) THEN
587 l_b_upd := TRUE;
588 END IF;
589 END IF;
590
591 IF l_b_upd THEN
592
593 l_b_upgrade_done := TRUE;
594 igs_fi_applications_pkg.update_row(x_rowid => rec_application_details.rowid,
595 x_application_id => rec_application_details.application_id,
596 x_application_type => rec_application_details.application_type,
597 x_invoice_id => rec_application_details.invoice_id,
598 x_credit_id => rec_application_details.credit_id,
599 x_credit_activity_id => rec_application_details.credit_activity_id,
600 x_amount_applied => rec_application_details.amount_applied,
601 x_apply_date => rec_application_details.apply_date,
602 x_link_application_id => rec_application_details.link_application_id,
603 x_dr_account_cd => rec_application_details.dr_account_cd,
604 x_cr_account_cd => l_v_upd_cr_account_cd,
605 x_dr_gl_code_ccid => rec_application_details.dr_gl_code_ccid,
606 x_cr_gl_code_ccid => l_n_upd_cr_gl_code_ccid,
607 x_applied_invoice_lines_id => rec_application_details.applied_invoice_lines_id,
608 x_appl_hierarchy_id => rec_application_details.appl_hierarchy_id,
609 x_posting_id => rec_application_details.posting_id,
610 x_gl_date => rec_application_details.gl_date,
611 x_gl_posted_date => rec_application_details.gl_posted_date,
612 x_posting_control_id => rec_application_details.posting_control_id);
613
614 fnd_file.new_line(fnd_file.LOG );
615 fnd_file.put_line(fnd_file.LOG, l_v_lkp_application_id || ': ' || rec_application_details.application_id );
616 fnd_file.put_line(fnd_file.LOG, l_v_lkp_dr_account || ': ' || NVL(igs_fi_gen_007.get_ccid_concat(rec_application_details.dr_gl_code_ccid), rec_application_details.dr_account_cd) );
617 fnd_file.put_line(fnd_file.LOG, l_v_lkp_cr_account || ': ' || NVL(igs_fi_gen_007.get_ccid_concat(l_n_upd_cr_gl_code_ccid), l_v_upd_cr_account_cd) );
618 fnd_file.put_line(fnd_file.LOG, l_v_lkp_status || ' : ' || l_v_lkp_success );
619
620 -- Check if this Application Record has been posted to GL Interface or Posting Interface
621 IF rec_application_details.posting_control_id IS NOT NULL THEN
622 IF l_v_rec_installed = 'Y' THEN
623 FOR rec_gl_interface IN c_gl_interface( rec_application_details.application_id, 'IGS_FI_APPLICATIONS' ) LOOP
624 IF rec_application_details.application_type = 'APP'
625 AND rec_gl_interface.accounted_cr IS NOT NULL
626 AND rec_gl_interface.code_combination_id IS NULL THEN
627 UPDATE gl_interface
628 SET code_combination_id = NVL(code_combination_id, l_n_upd_cr_gl_code_ccid)
629 WHERE rowid = rec_gl_interface.rowid;
630 l_b_upgrade_done := TRUE;
631
632 -- This code is executed for UNAPP Record transfered to GL.
633 ELSIF rec_application_details.application_type = 'UNAPP'
634 AND rec_gl_interface.accounted_dr IS NOT NULL
635 AND rec_gl_interface.code_combination_id IS NULL THEN
636 UPDATE gl_interface
637 SET code_combination_id = NVL(code_combination_id, l_n_upd_cr_gl_code_ccid)
638 WHERE rowid = rec_gl_interface.rowid;
639 l_b_upgrade_done := TRUE;
640 END IF;
641 END LOOP;
642
643 ELSE -- GL is not Installed, so Check Posting Interface Table
644 FOR rec_posting_int IN c_posting_int( rec_application_details.application_id, 'APPLICATION' ) LOOP
645 IF rec_posting_int.dr_account_cd IS NULL
646 OR rec_posting_int.cr_account_cd IS NULL THEN
647 IF rec_application_details.application_type = 'UNAPP' THEN
648 l_v_upd_dr_account_cd := NVL(rec_posting_int.dr_account_cd, l_v_upd_cr_account_cd); -- For UNAPP, rec_posting_int.dr_account_cd will be NULL
649 l_v_upd_cr_account_cd := rec_posting_int.cr_account_cd;
650 ELSE
651 l_v_upd_dr_account_cd := rec_posting_int.dr_account_cd;
652 l_v_upd_cr_account_cd := NVL(rec_posting_int.cr_account_cd, l_v_upd_cr_account_cd); -- For App, rec_posting_int.cr_account_cd will be NULL
653 END IF;
654
655 l_b_upgrade_done := TRUE;
656 igs_fi_posting_int_pkg.update_row (
657 x_rowid => rec_posting_int.ROWID,
658 x_posting_control_id => rec_posting_int.posting_control_id,
659 x_posting_id => rec_posting_int.posting_id,
660 x_batch_name => rec_posting_int.batch_name,
661 x_accounting_date => rec_posting_int.accounting_date,
662 x_transaction_date => rec_posting_int.transaction_date,
663 x_currency_cd => rec_posting_int.currency_cd,
664 x_dr_account_cd => l_v_upd_dr_account_cd,
665 x_cr_account_cd => l_v_upd_cr_account_cd,
666 x_dr_gl_code_ccid => rec_posting_int.dr_gl_code_ccid,
667 x_cr_gl_code_ccid => rec_posting_int.cr_gl_code_ccid,
668 x_amount => rec_posting_int.amount,
669 x_source_transaction_id => rec_posting_int.source_transaction_id,
670 x_source_transaction_type => rec_posting_int.source_transaction_type,
671 x_status => rec_posting_int.status,
672 x_orig_appl_fee_ref => rec_posting_int.orig_appl_fee_ref,
673 x_mode => 'R');
674 END IF;
675 END LOOP;
676 END IF; /* GL Installed */
677 END IF; /* Posting Control ID */
678
679 -- ====== UPDATE Credit Activity Records. ====== --
680
681 IF l_v_accounting_method = 'ACCRUAL' AND get_credit_class( rec_application_details.credit_id ) = 'CHGADJ' THEN
682 OPEN c_cr_act_details( rec_application_details.credit_id );
683 FETCH c_cr_act_details INTO rec_cr_act_details;
684 CLOSE c_cr_act_details;
685
686 l_v_upd_dr_account_cd := NVL( rec_cr_act_details.dr_account_cd, ret_invoice_tab(l_n_cntr).ftci_ret_account_cd );
687 l_n_upd_dr_gl_ccid := NVL( rec_cr_act_details.dr_gl_ccid, ret_invoice_tab(l_n_cntr).ftci_ret_gl_ccid );
688
689 l_b_upd := FALSE;
690 IF l_v_rec_installed = 'Y' THEN
691 IF ((l_n_upd_dr_gl_ccid <> rec_cr_act_details.dr_gl_ccid) OR (rec_cr_act_details.dr_gl_ccid IS NULL)) THEN
692 l_b_upd := TRUE;
693 END IF;
694 ELSE
695 IF ((l_v_upd_dr_account_cd <> rec_cr_act_details.dr_account_cd) OR (rec_cr_act_details.dr_account_cd IS NULL)) THEN
696 l_b_upd := TRUE;
697 END IF;
698 END IF;
699
700 IF l_b_upd = TRUE THEN
701 l_b_upgrade_done := TRUE;
702 igs_fi_cr_activities_pkg.update_row(x_rowid => rec_cr_act_details.rowid,
703 x_credit_activity_id => rec_cr_act_details.credit_activity_id,
704 x_credit_id => rec_cr_act_details.credit_id,
705 x_status => rec_cr_act_details.status,
706 x_transaction_date => rec_cr_act_details.transaction_date,
707 x_amount => rec_cr_act_details.amount,
708 x_dr_account_cd => l_v_upd_dr_account_cd,
709 x_cr_account_cd => rec_cr_act_details.cr_account_cd,
710 x_dr_gl_ccid => l_n_upd_dr_gl_ccid,
711 x_cr_gl_ccid => rec_cr_act_details.cr_gl_ccid,
712 x_bill_id => rec_cr_act_details.bill_id,
713 x_bill_number => rec_cr_act_details.bill_number,
714 x_bill_date => rec_cr_act_details.bill_date,
715 x_posting_id => rec_cr_act_details.posting_id,
716 x_posting_control_id => rec_cr_act_details.posting_control_id,
717 x_gl_date => rec_cr_act_details.gl_date,
718 x_gl_posted_date => rec_cr_act_details.gl_posted_date);
719
720
721 fnd_file.new_line(fnd_file.LOG );
722 fnd_file.put_line(fnd_file.LOG, l_v_lkp_credit_number || ': ' || get_credit_number(rec_cr_act_details.credit_id) );
723 fnd_file.put_line(fnd_file.LOG, l_v_lkp_dr_account || ': ' || NVL(igs_fi_gen_007.get_ccid_concat(l_n_upd_dr_gl_ccid), l_v_upd_dr_account_cd) );
724 fnd_file.put_line(fnd_file.LOG, l_v_lkp_cr_account || ': ' || NVL(igs_fi_gen_007.get_ccid_concat(rec_cr_act_details.cr_gl_ccid), rec_cr_act_details.cr_account_cd) );
725 fnd_file.put_line(fnd_file.LOG, l_v_lkp_status || ' : ' || l_v_lkp_success );
726
727 -- Check if this Credit Activity Record has been posted to GL Interface or Posting Interface
728 IF rec_cr_act_details.posting_control_id IS NOT NULL THEN
729 IF l_v_rec_installed = 'Y' THEN
730
731 FOR rec_gl_interface IN c_gl_interface( rec_cr_act_details.credit_activity_id, 'IGS_FI_CR_ACTIVITIES' ) LOOP
732 IF rec_gl_interface.accounted_dr IS NOT NULL
733 AND rec_gl_interface.code_combination_id IS NULL THEN
734 UPDATE gl_interface
735 SET code_combination_id = NVL(code_combination_id, l_n_upd_dr_gl_ccid)
736 WHERE rowid = rec_gl_interface.rowid;
737 l_b_upgrade_done := TRUE;
738 END IF;
739 END LOOP;
740
741 ELSE -- GL is not Installed, so Check Posting Interface Table
742 FOR rec_posting_int IN c_posting_int( rec_cr_act_details.credit_activity_id, 'CREDIT' ) LOOP
743 IF rec_posting_int.dr_account_cd IS NULL THEN
744 l_b_upgrade_done := TRUE;
745 igs_fi_posting_int_pkg.update_row (
746 x_rowid => rec_posting_int.ROWID,
747 x_posting_control_id => rec_posting_int.posting_control_id,
748 x_posting_id => rec_posting_int.posting_id,
749 x_batch_name => rec_posting_int.batch_name,
750 x_accounting_date => rec_posting_int.accounting_date,
751 x_transaction_date => rec_posting_int.transaction_date,
752 x_currency_cd => rec_posting_int.currency_cd,
753 x_dr_account_cd => NVL(rec_posting_int.dr_account_cd, l_v_upd_dr_account_cd),
754 x_cr_account_cd => rec_posting_int.cr_account_cd,
755 x_dr_gl_code_ccid => rec_posting_int.dr_gl_code_ccid,
756 x_cr_gl_code_ccid => rec_posting_int.cr_gl_code_ccid,
757 x_amount => rec_posting_int.amount,
758 x_source_transaction_id => rec_posting_int.source_transaction_id,
759 x_source_transaction_type => rec_posting_int.source_transaction_type,
760 x_status => rec_posting_int.status,
761 x_orig_appl_fee_ref => rec_posting_int.orig_appl_fee_ref,
762 x_mode => 'R');
763
764 END IF;
765 END LOOP;
766 END IF; /* GL Installed */
767 END IF; /* Posting Control ID */
768 END IF; /* l_b_upd */
769 END IF; /* Accounting Method and Credit Class */
770
771 END IF; /* l_b_upd */
772 END LOOP; /* Updation of Application Records */
773 END IF; /* ret_invoice_tab.EXISTS(l_n_cntr) */
774 END LOOP;
775
776 COMMIT;
777
778 ELSIF l_b_no_ftci_setup = TRUE THEN
779 retcode := 1;
780 RETURN;
781 END IF; /* l_b_no_ftci_setup = FALSE AND ret_invoice_tab.COUNT > 0 */
782
783 IF l_b_upgrade_done = FALSE THEN
784 fnd_file.put_line( fnd_file.LOG, l_v_lkp_status || ' : ' || l_v_lkp_error );
785 END IF;
786
787 EXCEPTION
788
789 WHEN OTHERS THEN
790 ROLLBACK;
791 retcode := 2;
792 errbuf := fnd_message.get_string('IGS','IGS_GE_UNHANDLED_EXCEPTION')||sqlerrm;
793 igs_ge_msg_stack.conc_exception_hndl;
794
795 END upg_accts;
796
797 END igs_fi_upg_retention;