1 PACKAGE BODY jai_ap_rpt_apcr_pkg
2 /* $Header: jai_ap_rpt_apcr.plb 120.13 2012/05/23 09:40:57 zxin ship $ */
3 AS
4
5 /* --------------------------------------------------------------------------------------
6 Filename:
7
8 Change History:
9
10 Date Bug Remarks
11 --------- ---------- -------------------------------------------------------------
12 08-Jun-2005 Version 116.1 jai_ap_rpt_apcr -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
13 as required for CASE COMPLAINCE.
14 */
15 FUNCTION compute_credit_balance
16 (
17 p_bal_date DATE,
18 p_vendor_id NUMBER,
19 p_set_of_books_id NUMBER,
20 p_vendor_site_code VARCHAR2,
21 p_org_id NUMBER ,-- added by Aparajita on 26-sep-2002 for bug # 2574262
22 p_currency_code VARCHAR2 DEFAULT NULL, /* added by vumaasha for bug 8310720 */
23 p_accts ap_invoices_all.accts_pay_code_combination_id%TYPE DEFAULT NULL
24 )
25 RETURN NUMBER
26 IS
27 /*------------------------------------------------------------------------------
28 FILENAME: jai_ap_rpt_apcr_pkg.compute_credit_balance.sql
29 CHANGE HISTORY:
30
31 S.No Date Author AND Details
32
33 1. 16/07/2002 Aparajita Das, revamped this procedure the older code is
34 commented below for bug # 2459399.version# 615.1.
35
36 This function is called from 'Creditor's ledger Report' for c
37 alculating opening and closing balances. The logic for the detail
38 report line was correct, but balances were getting calculated
39 wrongly in many scenarios, to tackel this issue, this procedure
40 has been redefined based on the queries of the report.
41
42 2. 26/09/2002 Aparajita for bug # 2574262. version# 615.2
43 Added the concept of org_id, as org_id was being considered at
44 line level but not balance level.
45 Added the additional parameter p_org_id.
46
47
48 3. 17/12/2002 Aparajita for bug # 2668999. version# 615.3
49
50 For debit memos the entries should come as follows.
51 - -ve entry in credit side when it has been created and approved and if paid
52 it should not be a refund type of payment.
53 - -ve entry in debit side when it has been paid.
54 Changed the query1 of the report query to add a subquery condition to check
55 for payment existance of refund type of transactions.
56
57 4. 16/01/2003 Aparajita for bug # 2545466. Version# 615.4
58
59 Broke the GAIN/LOSS query in the cursor into 4 queries to take care of the
60 4 different source from which the gain loss record gets populated into
61 ap_ae_lines_all table.
62
63 Also removed the old code that was earlier commented at the end of this code.
64
65 There was inconsistancy between the logic followed in the report and this
66 function for following four fields, changed to follow
67 the report as that was correct.
68
69 - exchange_rate
70 - exchange_rate_type
71 - invoice_currency_code
72 - exchange_date
73 All the modifications are done in cursor C_invoices.
74
75 5. 15/07/2003 RBASKER for Bug #2911835. Version #616.1
76
77 Performance Issue: The report 'India Creditors Ledger' is running indefinitely ,
78 when it is run for all the vendors.
79
80 Fix: i)The TRUNC function around the date fields suppress the use of index.
81 Removed the TRUNC function from all SELECT statments.
82
83 ii)In query4 Gain or Loss source AP_INVOICE_DISTRIBUTIONS,
84 the join condition between ap_invoices_all and
85 ap_invoice_distributions_all was missing. Corrected the same.
86
87 6. 22/01/2004 Aparajita for Bug#3392495. Version#618.1
88 Added code to reduce the invoice amount by discount amount already given.
89 Changes in cursor C_invoices Query 1.
90
91 7. 04/06/2004 Added by vchallur for bug#3663549 version 115.1
92
93 added code so that it can free from invoice entry need not start
94 with disribution_line_number=1
95
96 8. 03/08/2004 Aparajita for bug#2839228. Version#115.2
97
98 Modified cursor C_vendor_site_id to select vendor site id from po_vendor_sites_all
99 considering the operating unit also. This was needed as vendor site code for the
100 same vendor may be same accross operating units..
101
102 9. 05/11/2004 Sanjikum for bug # 3896940, Version 115.3
103 Following changes are made in query 1 of cursor C_invoices
104 a) In the query's column Credit_val, changed the column from api.Invoice_amt to z.amt_val
105 b) In the From clause, added the inline view Z
106 c) In the where clause, added the join "z.invoice_id = api.invoice_id"
107
108 10. 05/11/2004 Sanjikum for bug # 4030311, Version 115.4
109 Following changes are made in query 1 of cursor C_invoices
110 a) In the inline view Z, added a condition - ap_invoice_distributions_all.Line_type_lookup_code <> 'PREPAY'
111
112 11. 12/01/2005 For bug4035943. Added by LGOPALSA
113 Modified the calculation for balances as per the query
114 change.
115 Added the accounted_dr and accounted_cr for GAIN/LOSS
116 lines as they will have the correct values with respect
117 to functional currency. Thus we can avoid any
118 rounding errors on further calculation.
119 (1) Added invoice_type_lookup_code in all the query
120 (2) Changed the column selection for exchange rate in query 2
121 (3) Selected zero for acct_dr except for LOSS lines
122 (4) Selected accounted_dr from core accounting tables which will make the
123 calculation easier and also avoids the rounding problems.
124 (5) Added the validation for 'LOSS' in calculating the credit balance.
125 (6) Rounded the value to 2 decimal places as this is showing .01 while
126 displaying closing balance. We need to round it before sending the
127 values for displaying credit and debite values
128
129 12. 14-Jun-2005 rchandan for bug#4428980, Version 116.2
130 Modified the object to remove literals from DML statements and CURSORS.
131 As part OF R12 Initiative Inventory conversion the OPM code is commented
132
133 13 07/12/2005 Hjujjuru for the bug 4866533 File version 120.3
134 added the who columns in the insert into table JAI_PO_REP_PRRG_T
135 Dependencies Due to this bug:-
136 None
137 14. 31-MAR-2009 JMEENA for bug#7689858
138 Removed discount_amount_taken from invoice amount and added with payment amount.
139 ----------------------------------------------------------------------------------------------------------------------- */
140
141 v_exchange_rate NUMBER;
142 v_credit_bal NUMBER;
143 v_invoice_amount NUMBER;
144 v_vendor_site_id NUMBER;
145 lv_prepayment CONSTANT varchar2(30) := 'PREPAYMENT'; --rchandan for bug#4428980
146 lv_prepay CONSTANT varchar2(30) := 'PREPAY'; --rchandan for bug#4428980
147 lv_debit CONSTANT varchar2(30) := 'DEBIT' ; --rchandan for bug#4428980
148 lv_cleared CONSTANT varchar2(30) := 'CLEARED';--rchandan for bug#4428980
149 lv_negotiable CONSTANT varchar2(30) := 'NEGOTIABLE';--rchandan for bug#4428980
150 lv_voided CONSTANT varchar2(30) := 'VOIDED';--rchandan for bug#4428980
151 lv_rec_unacc CONSTANT varchar2(30) := 'RECONCILED UNACCOUNTED';--rchandan for bug#4428980
152 lv_reconciled CONSTANT varchar2(30) := 'RECONCILED';--rchandan for bug#4428980
153 lv_cleared_unacc CONSTANT varchar2(30) := 'CLEARED BUT UNACCOUNTED';--rchandan for bug#4428980
154 lv_ap_checks CONSTANT varchar2(30) := 'AP_CHECKS'; --rchandan for bug#4428980
155 lv_ap_invoices CONSTANT varchar2(30) := 'AP_INVOICES'; --rchandan for bug#4428980
156 lv_ap_invoice_distributions CONSTANT varchar2(30) := 'AP_INVOICE_DISTRIBUTIONS'; --rchandan for bug#4428980
157 lv_ap_invoice_payments CONSTANT varchar2(30) := 'AP_INVOICE_PAYMENTS'; --rchandan for bug#4428980
158
159 -- Bug 4997569. Added by Lakshmi Gopalsami
160 lv_inv_entity_code CONSTANT varchar2(30) := 'AP_INVOICES';
161 lv_pay_entity_code CONSTANT varchar2(30) := 'AP_PAYMENTS';
162
163 CURSOR C_invoices( p_gain ap_ae_lines_all.ae_line_type_code%TYPE,p_loss ap_ae_lines_all.ae_line_type_code%TYPE ) IS
164 -- query 1
165 --Removed discount_amount_taken from amount for the bug#7689858
166 SELECT api.invoice_type_lookup_code,
167 DECODE(api.invoice_type_lookup_code,
168 'CREDIT',
169 0,
170 z.amt_val
171 ) credit_val,
172 0 acct_cr,
173 api.exchange_rate exchange_rate,
174 api.exchange_rate_type exchange_rate_type,
175 api.invoice_currency_code invoice_currency_code,
176 api.exchange_date exchange_date
177 FROM ap_invoices_all api,
178 ap_invoice_distributions_all apd,
179 (SELECT NVL(SUM(apd.amount),0) amt_val,
180 api.invoice_id
181 FROM ap_invoices_all api,
182 ap_invoice_distributions_all apd
183 WHERE api.invoice_id = apd.invoice_id
184 AND api.invoice_type_lookup_code <> lv_prepayment --rchandan for bug#4428980
185 AND apd.match_status_flag = 'A'
186 AND api.vendor_id = p_vendor_id
187 AND api.vendor_site_id = v_vendor_site_id
188 AND apd.accounting_date < p_bal_date
189 AND (api.org_id = p_org_id or api.org_id is null)
190 AND apd.line_type_lookup_code <> lv_prepay
191 GROUP BY api.invoice_id) z
192 WHERE z.invoice_id = api.invoice_id
193 AND api.invoice_id = apd.invoice_id
194 AND apd.rowid = (select rowid
195 from ap_invoice_distributions_all
196 where rownum=1
197 and invoice_id=apd.invoice_id
198 AND match_status_flag = 'A'
199 AND accounting_date < p_bal_date)
200 AND api.invoice_type_lookup_code <> lv_prepayment --rchandan for bug#4428980
201 AND apd.match_status_flag = 'A'
202 AND api.vendor_id = p_vendor_id
203 AND api.vendor_site_id = v_vendor_site_id
204 AND apd.accounting_date < p_bal_date
205 AND (api.org_id = p_org_id or api.org_id is null)
206 AND ((api.invoice_type_lookup_code <> lv_debit)--rchandan for bug#4428980
207 or
208 (
209 (api.invoice_type_lookup_code = lv_debit) --rchandan for bug#4428980
210 and
211 ( not exists
212 (Select '1'
213 from ap_invoice_payments_all app,
214 ap_checks_all apc
215 where app.check_id = apc.check_id
216 and app.invoice_id = api.invoice_id
217 and apc.payment_type_flag = 'R'
218 )
219 )
220 )
221 )
222 AND api.invoice_currency_code =nvl( p_currency_code,api.invoice_currency_code) /* Added by vumaasha for bug 8310720 */
223 AND api.accts_pay_code_combination_id = nvl(p_accts,api.accts_pay_code_combination_id) /* Added by vumaasha for bug 8310720 */
224
225 UNION ALL
226 -- query 2
227 --Added discount_amount_taken in the amount for the bug#7689858
228 SELECT api.invoice_type_lookup_code,
229 DECODE(api.invoice_type_lookup_code,'CREDIT',
230 DECODE(status_lookup_code,'VOIDED',
231 app.amount+ nvl(discount_amount_taken, 0) , ABS(app.amount)+ abs(nvl(discount_amount_taken, 0)) ), 0) credit_val,
232 0 acct_cr,
233 apc.exchange_rate exchange_rate,
234 apc.exchange_rate_type exchange_rate_type,
235 api.payment_currency_code invoice_currency_code,
236 apc.exchange_date exchange_date
237 FROM ap_invoices_all api,
238 --ap_invoice_distributions_all apd,Commented by nprashar for bug 8307469
239 ap_invoice_payments_all app,
240 ap_checks_all apc
241 WHERE /*api.invoice_id = apd.invoice_id
242 AND apd.distribution_line_number = (select distribution_line_number from ap_invoice_distributions_all
243 where rownum=1
244 and invoice_id=apd.invoice_id
245 AND apd.match_status_flag='A') Commented by nprashar for bug 8307469
246
247 AND */ app.invoice_id = api.invoice_id
248 AND app.check_id = apc.check_id
249 AND apc.status_lookup_code IN (lv_cleared,lv_negotiable,lv_voided,lv_rec_unacc,lv_reconciled,lv_cleared_unacc ) --rchandan for bug#4428980
250 --AND apd.match_status_flag='A' Commented by nprashar for bug 8307469
251 AND api.vendor_id = p_vendor_id
252 AND api.vendor_site_id = v_vendor_site_id
253 AND app.accounting_date < trunc(p_bal_date)
254 AND ( api.org_id = p_org_id or api.org_id is null )
255 AND Exists ( Select '1' from ap_invoice_distributions_all apd , ap_invoice_lines_all apil where apd.invoice_id = api.invoice_id
256 and apil.invoice_id = apd.invoice_id
257 and apd.match_status_flag ='A'
258 and apil.line_number = apd.invoice_line_number) /*Added by nprashar for bug 12651044 */
259 AND api.invoice_currency_code =nvl( p_currency_code,api.invoice_currency_code) /* Added by vumaasha for bug 8310720 */
260 AND api.accts_pay_code_combination_id = nvl(p_accts,api.accts_pay_code_combination_id) /* Added by vumaasha for bug 8310720 */
261 UNION ALL
262 -- query 3
263 /* Gain or Loss source AP_INVOICES */
264 /* Bug 4997569. Added by Lakshmi Gopalsami
265 (1) Changed the reference to xla_ae_headers instead of ap_ae_headers_all
266 and xla_ae_lines instead of xla_ae_lines.
267 (2) Changed ae_line_type_code to accounting_class_code
268 (3) Also added xla_transaction_entities to get the entity_id and
269 source_int_id_1 so that it can be joined with transaction tables.
270 */
271 select 'LOSS' invoice_type_lookup_code,
272 nvl(accounted_cr,0) credit_val, /*Added by nprashar for bug # 12843559*/
273 nvl(accounted_dr,0) acct_cr, /*Added by nprashar for bug # 12843559*/
274 xal.currency_conversion_rate exchange_rate ,
275 xal.currency_conversion_type exchange_rate_type,
276 xal.currency_code invoice_currency_code,
277 xal.currency_conversion_date exchange_date
278 FROM xla_ae_lines xal,
279 xla_ae_headers xah,
280 xla_transaction_entities xte,
281 ap_invoices_all api
282 WHERE xal.application_id = 200 AND
283 xal.ae_header_id = xah.ae_header_id AND
284 xal.accounting_class_code in ( p_gain,p_loss) AND --rchandan for bug#4428980
285 xah.application_id = 200 AND
286 xah.entity_id = xte.entity_id AND
287 xte.application_id = 200 AND
288 xte.entity_code =lv_inv_entity_code AND --'AP_INVOICES'
289 xte.source_id_int_1 = api.invoice_id AND
290 api.vendor_id = p_vendor_id AND
291 api.vendor_site_id = v_vendor_site_id AND
292 xah.ACCOUNTING_DATE < p_bal_date AND
293 (api.org_id = p_org_id or api.org_id is null )
294 AND api.invoice_currency_code =nvl( p_currency_code,api.invoice_currency_code) /* Added by vumaasha for bug 8310720 */
295 AND api.accts_pay_code_combination_id = nvl(p_accts,api.accts_pay_code_combination_id) /* Added by vumaasha for bug 8310720 */
296 union all
297 -- Query 4
298 /* Gain or Loss source AP_INVOICE_DISTRIBUTIONS */
299 /* Bug 4997569. Added by Lakshmi Gopalsami
300 (1) Changed the reference to xla_ae_headers instead of ap_ae_headers_all
301 and xla_ae_lines instead of xla_ae_lines.
302 (2) Changed ae_line_type_code to accounting_class_code
303 (3) Also added xla_transaction_entities to get the entity_id and
304 source_int_id_1 so that it can be joined with transaction tables.
305 */
306 /* Commented query 4 by nprashar for bug 8307469 as query 3 and 4 are identical
307 select 'LOSS' invoice_type_lookup_code,
308 0 credit_val,
309 DECODE(xal.accounting_class_code,'LOSS', accounted_dr,0) acct_cr,
310 xal.currency_conversion_rate exchange_rate ,
311 xal.currency_conversion_type exchange_rate_type,
312 xal.currency_code invoice_currency_code,
313 xal.currency_conversion_date exchange_date
314 from xla_ae_lines xal,
315 xla_ae_headers xah,
316 xla_transaction_entities xte,
317 ap_invoices_all api,
318 ap_invoice_distributions_all apd --Commented by nprashar for bug 8307469
319 where xal.application_id = 200 AND
320 xal.ae_header_id = xah.ae_header_id AND
321 xal.accounting_class_code in ( p_gain,p_loss) AND --rchandan for bug#4428980
322 xah.application_id = 200 AND
323 xah.entity_id = xte.entity_id AND
324 xte.application_id = 200 AND
325 xte.entity_code =lv_inv_entity_code AND --'AP_INVOICES'
326 xte.source_id_int_1 = api.invoice_id AND
327 /*api.invoice_id = apd.invoice_id AND
328 apd.accounting_event_id = xah.event_id AND --Commented by nprashar for bug 8307469
329 api.vendor_id = p_vendor_id AND
330 api.vendor_site_id = v_vendor_site_id AND
331 xah.ACCOUNTING_DATE < p_bal_date AND
332 ( api.org_id = p_org_id or api.org_id is null ) AND
333 Exists( select '1' from ap_invoice_distributions_all apd
334 where api.invoice_id = apd.invoice_id
335 AND apd.accounting_event_id = xah.event_id) --Added by nprashar for bug 8307469
336 union all Commenting ends */
337 -- Query 5
338 /* Gain or Loss source AP_CHECKS */
339 /* Bug 4997569. Added by Lakshmi Gopalsami
340 (1) Changed the reference to xla_ae_headers instead of ap_ae_headers_all
341 and xla_ae_lines instead of xla_ae_lines.
342 (2) Changed ae_line_type_code to accounting_class_code
343 (3) Also added xla_transaction_entities to get the entity_id and
344 source_int_id_1 so that it can be joined with transaction tables.
345 */
346 select 'LOSS' invoice_type_lookup_code,
347 nvl(accounted_cr,0) credit_val, /*Added by nprashar for bug # 12843559*/
348 nvl(accounted_dr,0) acct_cr, /*Added by nprashar for bug # 12843559*/
349 xal.currency_conversion_rate exchange_rate ,
350 xal.currency_conversion_type exchange_rate_type,
351 xal.currency_code invoice_currency_code,
352 xal.currency_conversion_date exchange_date
353 from xla_ae_lines xal,
354 xla_ae_headers xah,
355 xla_transaction_entities xte,
356 ap_invoices_all api,
357 ap_checks_all ac ,
358 ap_invoice_payments_all app
359 where xal.application_id = 200 AND
360 xal.ae_header_id = xah.ae_header_id AND
361 xal.accounting_class_code in ( p_gain,p_loss) AND --rchandan for bug#4428980
362 xah.application_id = 200 AND
363 xah.entity_id = xte.entity_id AND
364 xte.application_id = 200 AND
365 xte.entity_code = lv_pay_entity_code AND --'AP_PAYMENTS'
366 xte.source_id_int_1 = ac.check_id AND
367 xah.event_id = app.accounting_event_id AND
368 api.invoice_id = app.invoice_id AND
369 app.check_id = ac.check_id AND
370 ac.status_lookup_code IN (lv_cleared,lv_negotiable,lv_voided,
371 lv_rec_unacc,lv_reconciled,lv_cleared_unacc) AND
372 api.vendor_id = p_vendor_id AND
373 api.vendor_site_id = v_vendor_site_id AND
374 xah.ACCOUNTING_DATE < p_bal_date AND
375 (api.org_id = p_org_id or api.org_id is null )
376 AND api.invoice_currency_code =nvl( p_currency_code,api.invoice_currency_code) /* Added by vumaasha for bug 8310720 */
377 AND api.accts_pay_code_combination_id = nvl(p_accts,api.accts_pay_code_combination_id) /* Added by vumaasha for bug 8310720 */;
378
379 /* Commented query 6 as , Query 5 and query 6 are identical
380 union all
381 -- Query 6
382 Gain or Loss source AP_INVOICE_PAYMENTS
383 Bug 4997569. Added by Lakshmi Gopalsami
384 (1) Changed the reference to xla_ae_headers instead of ap_ae_headers_all
385 and xla_ae_lines instead of xla_ae_lines.
386 (2) Changed ae_line_type_code to accounting_class_code
387 (3) Also added xla_transaction_entities to get the entity_id and
388 source_int_id_1 so that it can be joined with transaction tables.
389
390 select 'LOSS' invoice_type_lookup_code,
391 0 credit_val,
392 DECODE(xal.accounting_class_code,'LOSS', accounted_dr,0) acct_cr,
393 xal.currency_conversion_rate exchange_rate ,
394 xal.currency_conversion_type exchange_rate_type,
395 xal.currency_code invoice_currency_code,
396 xal.currency_conversion_date exchange_date
397 from xla_ae_lines xal,
398 xla_ae_headers xah,
399 xla_transaction_entities xte,
400 ap_invoices_all api,
401 ap_checks_all ac ,
402 ap_invoice_payments_all app
403 where xal.application_id = 200 AND
404 xal.ae_header_id = xah.ae_header_id AND
405 xal.accounting_class_code in ( p_gain,p_loss) AND --rchandan for bug#4428980
406 xah.application_id = 200 AND
407 xah.entity_id = xte.entity_id AND
408 xte.application_id = 200 AND
409 xte.entity_code = lv_pay_entity_code AND --'AP_PAYMENTS'
410 xte.source_id_int_1 = ac.check_id AND
411 xah.event_id = app.accounting_event_id AND
412 api.invoice_id = app.invoice_id AND
413 app.check_id = ac.check_id AND
414 ac.status_lookup_code IN (lv_cleared,lv_negotiable,lv_voided,lv_rec_unacc,lv_reconciled,lv_cleared_unacc ) AND--rchandan for bug#4428980
415 api.vendor_id = p_vendor_id AND
416 api.vendor_site_id = v_vendor_site_id AND
417 xah.ACCOUNTING_DATE < p_bal_date AND
418 (api.org_id = p_org_id or api.org_id is null )
419 ; Commented by nprashar for bug 8307469*/
420
421
422 CURSOR C_vendor_site_id IS
423 SELECT vendor_site_id
424 FROM po_vendor_sites_all
425 WHERE vendor_id = p_vendor_id
426 AND org_id = p_org_id /* Added for Bug 2839228 */
427 AND vendor_site_code = p_vendor_site_code ;
428
429 BEGIN
430
431 -- get the vendor site id
432 OPEN C_vendor_site_id;
433 FETCH C_vendor_site_id INTO v_vendor_site_id;
434 CLOSE C_vendor_site_id;
435
436
437 v_credit_bal := 0;
438
439 FOR inv_rec IN C_invoices('GAIN','LOSS') LOOP --rchandan for bug#4428980
440
441 v_invoice_amount := 0;
442 v_exchange_rate := 1;
443
444 /* For bug 4035943. Added by LGOPALSA
445 Added the logic for LOSS Lines */
446 If inv_rec.invoice_type_lookup_code ='LOSS' Then
447
448 v_invoice_amount := nvl(inv_rec.acct_cr,0) - nvl(inv_rec.credit_val,0); /*Added by nprashar for bug # 12843559*/
449 fnd_file.put_line(FND_FILE.LOG, 'invoice amt- LOSS : '|| v_invoice_amount);
450
451 Else
452
453
454 IF inv_rec.exchange_rate_type IS NOT NULL THEN
455
456 v_exchange_rate := jai_cmn_utils_pkg.currency_conversion( p_set_of_books_id,
457 inv_rec.invoice_currency_code,
458 inv_rec.exchange_date,
459 inv_rec.exchange_rate_type,
460 inv_rec.exchange_rate );
461
462 v_invoice_amount := NVL(inv_rec.credit_val, 0) * NVL(v_exchange_rate, 1);
463 fnd_file.put_line(FND_FILE.LOG, 'invoice amt'|| v_invoice_amount);
464 ELSE
465 v_invoice_amount := NVL(inv_rec.credit_val, 0) ;
466 END IF;
467
468 End if;
469
470 v_credit_bal := v_credit_bal + NVL(v_invoice_amount, 0);
471
472 END LOOP ;
473
474 RETURN (round( NVL(v_credit_bal, 0),2));
475
476 EXCEPTION
477 WHEN OTHERS THEN
478 RAISE_APPLICATION_ERROR(-20009,'EXCEPTION IN FUNCTION ja_in_cledger_credit_bal : ' || SQLERRM);
479
480 END compute_credit_balance;
481
482 FUNCTION compute_debit_balance
483 (
484 p_bal_date DATE,
485 p_vendor_id NUMBER,
486 p_set_of_books_id NUMBER,
487 p_vendor_site_code VARCHAR2,
488 p_org_id NUMBER, -- added by Aparajita on 26-sep-2002 for bug # 2574262
489 p_currency_code VARCHAR2 DEFAULT NULL, /* added by vumaasha for bug 8310720 */
490 p_accts ap_invoices_all.accts_pay_code_combination_id%TYPE DEFAULT NULL
491 )
492 RETURN NUMBER
493 IS
494 /*------------------------------------------------------------------------------
495 FILENAME: jai_ap_rpt_apcr_pkg.compute_credit_balance.sql
496 CHANGE HISTORY:
497
498 S.No Date Author AND Details
499
500 1. 16/07/2002 Aparajita Das, revamped this procedure the older code is
501 commented below for bug # 2459399. Version # 615.1
502
503 This function is called from 'Creditor's ledger Report' for
504 calculating opening and closing balances. The logic for the
505 detail report line was correct, but balances were getting
506 calculated wrongly in many scenarios, to tackel this issue,
507 this procedure has been redefined based on the queries of
508 the report.
509
510 2. 26/09/2002 Aparajita for bug # 2574262. Version # 615.2
511
512 Added the concept of org_id, as org_id was being considered at
513 line level but not balance level.
514 Added the additional parameter p_org_id.
515
516 3. 17/12/2002 Aparajita for bug # 2668999. Version # 615.3
517
518 For debit memos the entries should come as follows.
519 - -ve entry in credit side when it has been created and approved
520 and if paid it should not be a refund type of payment.
521
522 - -ve entry in debit side when it has been paid.
523
524 Changed the query1 of the report query to add a subquery condition
525 to check for paymentexistance of refund type of transactions.
526
527
528 4. 16/01/2003 Aparajita for bug # 2545466. Version # 615.4
529
530 Broke the GAIN/LOSS query in the cursor into 4 queries to take
531 care of the 4 different source from which the gain loss record
532 gets populated into ap_ae_lines_all table.
533
534 Also removed the old code that was earlier commented at the end
535 of this code.
536
537 There was inconsistancy between the logic followed in the report
538 and this function for following four fields,
539 changed to follow the report as that was correct.
540 - exchange_rate
541 - exchange_rate_type
542 - invoice_currency_code
543 - exchange_date
544 All the modifications are done in cursor C_invoices.
545
546 5. 15/07/2003 RBASKER for Bug #2911835. Version #616.1
547
548 Performance Issue: The report 'India Creditors Ledger' is running
549 indefinitely ,when it is run for all the vendors.
550
551 Fix:
552 i)The TRUNC function around the date fields suppress the use of index.
553 Removed the TRUNC function from all SELECT statments.
554
555 ii)In query4 Gain or Loss source AP_INVOICE_DISTRIBUTIONS, the join condition
556 between ap_invoices_all and ap_invoice_distributions_all was missing.
557 Corrected the same.
558
559 6. 22/01/2004 Aparajita for Bug#3392495. Version#115.1
560 Added code to reduce the invoice amount by discount amount already given.
561 Changes in cursor C_invoices Query 1.
562
563 7. 04/06/2004 Added by vchallur for bug#3663549 version 115.1
564 added code so that it can free from invoice entry need not start with
565 disribution_line_number=1
566
567 8. 03/08/2004 Aparajita for bug#2839228. Version#115.2
568
569 Modified cursor C_vendor_site_id to select vendor site id from po_vendor_sites_all
570 considering the operating unit also. This was needed as vendor site code for the
571 same vendor may be same accross operating units..
572
573 9. 05/11/2004 Sanjikum for bug # 3896940, Version 115.3
574 Following changes are made in query 1 of cursor C_invoices
575 a) In the query's column Credit_val, changed the column from api.Invoice_amt to z.amt_val
576 b) In the From clause, added the inline view Z
577 c) In the where clause, added the join "z.invoice_id = api.invoice_id"
578
579 10. 05/11/2004 Sanjikum for bug # 4030311, Version 115.4
580 Following changes are made in query 1 of cursor C_invoices
581 a) In the inline view Z, added a condition - ap_invoice_distributions_all.Line_type_lookup_code <> 'PREPAY'
582
583 11. 12/01/2005 For bug4035943. Added by LGOPALSA
584 Modified the calculation for balances as per the
585 query change.
586 Added the accounted_dr and accounted_cr for GAIN/LOSS
587 lines as they will have the correct values with respect
588 to functional currency. Thus, we can avoid any rounding
589 errors on further calculation.
590 (1) Added invoice_type_lookup_code in all the query
591 (2) Changed the column selection for exchange rate in
592 query 2
593 (3) Selected zero for acct_cr except for GAIN lines
594 (4) Selected accounted_cr from core accounting tables
595 which will make the calculation easier and also
596 avoids the rounding problems.
597 (5) Added the validation for 'GAIN' in calculating the debit balance.
598 (6) Rounded the value to 2 decimal places as this is showing .01 while
599 displaying closing balance. We need to round it before sending the
600 values for displaying credit and debite values
601
602 12. 31-MAR-2009 JMEENA for bug#7689858
603 Removed discount_amount_taken from invoice amount and added with payment amount.
604 ----------------------------------------------------------------------------------------------*/
605
606
607 v_exchange_rate NUMBER;
608 v_debit_bal NUMBER;
609 v_invoice_amount NUMBER;
610 v_vendor_site_id NUMBER;
611 lv_prepayment CONSTANT varchar2(30) := 'PREPAYMENT'; --rchandan for bug#4428980
612 lv_prepay CONSTANT varchar2(30) := 'PREPAY'; --rchandan for bug#4428980
613 lv_debit CONSTANT varchar2(30) := 'DEBIT' ; --rchandan for bug#4428980
614 lv_cleared CONSTANT varchar2(30) := 'CLEARED';--rchandan for bug#4428980
615 lv_negotiable CONSTANT varchar2(30) := 'NEGOTIABLE';--rchandan for bug#4428980
616 lv_voided CONSTANT varchar2(30) := 'VOIDED';--rchandan for bug#4428980
617 lv_rec_unacc CONSTANT varchar2(30) := 'RECONCILED UNACCOUNTED';--rchandan for bug#4428980
618 lv_reconciled CONSTANT varchar2(30) := 'RECONCILED';--rchandan for bug#4428980
619 lv_cleared_unacc CONSTANT varchar2(30) := 'CLEARED BUT UNACCOUNTED';--rchandan for bug#4428980
620 lv_ap_checks CONSTANT varchar2(30) := 'AP_CHECKS'; --rchandan for bug#4428980
621 lv_ap_invoices CONSTANT varchar2(30) := 'AP_INVOICES'; --rchandan for bug#4428980
622 lv_ap_invoice_distributions CONSTANT varchar2(30) := 'AP_INVOICE_DISTRIBUTIONS'; --rchandan for bug#4428980
623 lv_ap_invoice_payments CONSTANT varchar2(30) := 'AP_INVOICE_PAYMENTS'; --rchandan for bug#4428980
624
625 -- Bug 4997569. Added by Lakshmi Gopalsami
626 lv_inv_entity_code CONSTANT varchar2(30) := 'AP_INVOICES';
627 lv_pay_entity_code CONSTANT varchar2(30) := 'AP_PAYMENTS';
628
629
630 CURSOR C_invoices( p_gain ap_ae_lines_all.ae_line_type_code%TYPE,p_loss ap_ae_lines_all.ae_line_type_code%TYPE ) IS --rchandan for bug#4428980
631 -- query 1
632 --Removed discount_amount_taken from debit_val for the bug#7689858
633 SELECT api.invoice_type_lookup_code,
634 DECODE(api.invoice_type_lookup_code,
635 'CREDIT',
636 ABS(z.amt_val) , -- Changed from api.Invoice_amt to z.amt_val for Bug#3896940
637 0
638 ) debit_val,
639 /* Bug4035943. Added by LGOPALSA */
640 0 acct_dr,
641 api.exchange_rate exchange_rate,
642 api.exchange_rate_type exchange_rate_type,
643 api.invoice_currency_code invoice_currency_code,
644 api.exchange_date exchange_date
645 FROM ap_invoices_all api,
646 ap_invoice_distributions_all apd,
647 (SELECT NVL(SUM(apd.amount),0) amt_val, /* Bug#3390665*/
648 api.invoice_id
649 FROM ap_invoices_all api,
650 ap_invoice_distributions_all apd
651 WHERE api.invoice_id = apd.invoice_id
652 AND api.invoice_type_lookup_code <> lv_prepayment --rchandan for bug#4428980
653 AND apd.match_status_flag = 'A'
654 AND api.vendor_id = p_vendor_id
655 AND api.vendor_site_id = v_vendor_site_id
656 AND apd.accounting_date < p_bal_date
657 AND (api.org_id = p_org_id or api.org_id is null)
658 AND apd.line_type_lookup_code <> lv_prepay --Added by Sanjikum for Bug # 4030311 --rchandan for bug#4428980
659 GROUP BY api.invoice_id) z -- Added the Inline view for Bug # 3896940, as sum of amount was required in place of invoice amount
660 WHERE z.invoice_id = api.invoice_id --Added the condition for Bug # 3896940
661 AND api.invoice_id = apd.invoice_id
662 AND apd.rowid = (select rowid
663 from ap_invoice_distributions_all
664 where rownum=1
665 and invoice_id=apd.invoice_id
666 AND match_status_flag = 'A'
667 AND accounting_date < p_bal_date)
668 AND api.invoice_type_lookup_code <> lv_prepayment --rchandan for bug#4428980
669 AND apd.match_status_flag = 'A'
670 AND api.vendor_id = p_vendor_id
671 AND api.vendor_site_id = v_vendor_site_id
672 AND apd.accounting_date < p_bal_date
673 AND (api.org_id = p_org_id or api.org_id is null) -- added by Aparajita on 26-sep-2002 for bug # 2574262
674 /* Following and clause added by Aparajita on 17/12/2002 for bug # 2668999 */
675 AND ((api.invoice_type_lookup_code <> lv_debit)
676 or
677 (
678 (api.invoice_type_lookup_code = lv_debit)
679 and
680 ( not exists
681 (Select '1'
682 from ap_invoice_payments_all app,
683 ap_checks_all apc
684 where app.check_id = apc.check_id
685 and app.invoice_id = api.invoice_id
686 and apc.payment_type_flag = 'R'
687 )
688 )
689 )
690 )
691 AND api.invoice_currency_code =nvl( p_currency_code,api.invoice_currency_code) /* Added by vumaasha for bug 8310720 */
692 AND api.accts_pay_code_combination_id = nvl(p_accts,api.accts_pay_code_combination_id) /* Added by vumaasha for bug 8310720 */
693
694 UNION ALL
695 -- query 2
696 --Added discount_amount_taken in the amount for the bug#7689858
697 SELECT api.invoice_type_lookup_code,
698 DECODE(api.invoice_type_lookup_code,'CREDIT', 0,
699 decode(app.discount_taken ,0, app.amount, app.amount + nvl(discount_amount_taken, 0))) debit_val, --Added Decode clause for bug # 12651044
700 /* Bug4035943. Added by LGOPALSA */
701 0 acct_dr,
702 /* Bug 4035943. Also need to select the exchange rate details from
703 checks rather that invoices for payments */
704 apc.exchange_rate exchange_rate,
705 apc.exchange_rate_type exchange_rate_type,
706 api.payment_currency_code invoice_currency_code,
707 apc.exchange_date exchange_date
708 FROM ap_invoices_all api,
709 --ap_invoice_distributions_all apd, Commented by nprashar for bug # 8307469
710 ap_invoice_payments_all app,
711 ap_checks_all apc
712 WHERE /*api.invoice_id = apd.invoice_id
713 AND apd.distribution_line_number = (select distribution_line_number from ap_invoice_distributions_all
714 where rownum=1
715 and invoice_id=apd.invoice_id
716 AND apd.match_status_flag='A')
717 added by vchallur for bug#3663549 Commented by nprashar for bug # 8307469
718 AND */ app.invoice_id = api.invoice_id
719 AND app.check_id = apc.check_id
720 AND apc.status_lookup_code IN (lv_cleared,lv_negotiable,lv_voided,lv_rec_unacc,lv_reconciled,lv_cleared_unacc ) --rchandan for bug#4428980
721 -- AND apd.match_status_flag='A' Commented by nprashar for bug # 8307469
722 AND api.vendor_id = p_vendor_id
723 AND api.vendor_site_id = v_vendor_site_id
724 AND app.accounting_date < p_bal_date
725 AND ( api.org_id = p_org_id or api.org_id is null ) -- added by Aparajita on 26-sep-2002 for bug # 2574262
726 AND Exists ( Select '1' from ap_invoice_distributions_all apd , ap_invoice_lines_all apil where apd.invoice_id = api.invoice_id
727 and apil.invoice_id = apd.invoice_id
728 and apd.match_status_flag ='A'
729 and apil.line_number = apd.invoice_line_number) --Added by nprashar for bug # 12651044
730 AND api.invoice_currency_code =nvl( p_currency_code,api.invoice_currency_code) /* Added by vumaasha for bug 8310720 */
731 AND api.accts_pay_code_combination_id = nvl(p_accts,api.accts_pay_code_combination_id) /* Added by vumaasha for bug 8310720 */
732 UNION ALL
733 -- query 3
734 /* Gain or Loss source AP_INVOICES */
735 /* Bug 4997569. Added by Lakshmi Gopalsami
736 (1) Changed the reference to xla_ae_headers instead of ap_ae_headers_all
737 and xla_ae_lines instead of xla_ae_lines.
738 (2) Changed ae_line_type_code to accounting_class_code
739 (3) Also added xla_transaction_entities to get the entity_id and
740 source_int_id_1 so that it can be joined with transaction tables.
741 */
742 select 'GAIN' invoice_type_lookup_code,
743 0 debit_val,
744 DECODE(xal.accounting_class_code,'GAIN', accounted_cr,0) acct_dr,
745 xal.currency_conversion_rate exchange_rate ,
746 xal.currency_conversion_type exchange_rate_type,
747 xal.currency_code invoice_currency_code,
748 xal.currency_conversion_date exchange_date
749 from xla_ae_lines xal,
750 xla_ae_headers xah,
751 xla_transaction_entities xte,
752 ap_invoices_all api
753 where xal.application_id = 200 AND
754 xal.ae_header_id = xah.ae_header_id AND
755 xal.accounting_class_code in ( p_gain,p_loss) AND --rchandan for bug#4428980
756 xah.application_id = 200 AND
757 xah.entity_id = xte.entity_id AND
758 xte.application_id = 200 AND
759 xte.entity_code =lv_inv_entity_code AND --'AP_INVOICES'
760 xte.source_id_int_1 = api.invoice_id AND /*Added for Bug 8262193*/
761 api.vendor_id = p_vendor_id AND
762 api.vendor_site_id = v_vendor_site_id AND
763 xah.ACCOUNTING_DATE < p_bal_date AND
764 (api.org_id = p_org_id or api.org_id is null )
765 AND api.invoice_currency_code =nvl( p_currency_code,api.invoice_currency_code) /* Added by vumaasha for bug 8310720 */
766 AND api.accts_pay_code_combination_id = nvl(p_accts,api.accts_pay_code_combination_id) /* Added by vumaasha for bug 8310720 */
767 union all
768 -- Query 4
769 /* Gain or Loss source AP_INVOICE_DISTRIBUTIONS */
770 /* Bug 4997569. Added by Lakshmi Gopalsami
771 (1) Changed the reference to xla_ae_headers instead of ap_ae_headers_all
772 and xla_ae_lines instead of xla_ae_lines.
773 (2) Changed ae_line_type_code to accounting_class_code
774 (3) Also added xla_transaction_entities to get the entity_id and
775 source_int_id_1 so that it can be joined with transaction tables.
776 */
777 /*Commented Query 4 by nprashar for bug 8307469 as query3 and 4 identical
778 select 'GAIN' invoice_type_lookup_code,
779 0 debit_val,
780 DECODE(xal.accounting_class_code,'GAIN', accounted_cr,0) acct_dr,
781 xal.currency_conversion_rate exchange_rate ,
782 xal.currency_conversion_type exchange_rate_type,
783 xal.currency_code invoice_currency_code,
784 xal.currency_conversion_date exchange_date
785 from xla_ae_lines xal,
786 xla_ae_headers xah,
787 xla_transaction_entities xte,
788 ap_invoices_all api/*,
789 ap_invoice_distributions_all apd --Commented by nprashar for bug # 8307469
790 WHERE xal.application_id = 200 AND
791 xal.ae_header_id = xah.ae_header_id AND
792 xal.accounting_class_code in ( p_gain,p_loss) AND --rchandan for bug#4428980
793 xah.application_id = 200 AND
794 xah.entity_id = xte.entity_id AND
795 xte.application_id = 200 AND
796 xte.entity_code =lv_inv_entity_code AND --'AP_INVOICES'
797 xte.source_id_int_1 = api.invoice_id AND
798 /*apd.accounting_event_id = xah.event_id AND
799 api.invoice_id = apd.invoice_id AND -- Commented by nprashar for bug # 8307469
800 api.vendor_id = p_vendor_id AND
801 api.vendor_site_id = v_vendor_site_id AND
802 xah.ACCOUNTING_DATE < p_bal_date AND
803 ( api.org_id = p_org_id or api.org_id is null )
804 AND Exists (Select '1' from ap_invoice_distributions_all apd
805 Where apd.accounting_event_id = xah.event_id
806 AND api.invoice_id = apd.invoice_id ) -- Added by nprashar for bug # 8307469
807 union all Commenting Ends*/
808 -- Query 5
809 /* Gain or Loss source AP_CHECKS */
810 /* Bug 4997569. Added by Lakshmi Gopalsami
811 (1) Changed the reference to xla_ae_headers instead of ap_ae_headers_all
812 and xla_ae_lines instead of xla_ae_lines.
813 (2) Changed ae_line_type_code to accounting_class_code
814 (3) Also added xla_transaction_entities to get the entity_id and
815 source_int_id_1 so that it can be joined with transaction tables.
816 */
817 select 'GAIN' invoice_type_lookup_code,
818 0 debit_val,
819 DECODE(xal.accounting_class_code,'GAIN', accounted_cr,0) acct_dr,
820 xal.currency_conversion_rate exchange_rate ,
821 xal.currency_conversion_type exchange_rate_type,
822 xal.currency_code invoice_currency_code,
823 xal.currency_conversion_date exchange_date
824 from xla_ae_lines xal,
825 xla_ae_headers xah,
826 xla_transaction_entities xte,
827 ap_invoices_all api,
828 ap_checks_all ac ,
829 ap_invoice_payments_all app
830 where xal.application_id = 200 AND
831 xal.ae_header_id = xah.ae_header_id AND
832 xal.accounting_class_code in ( p_gain,p_loss) AND --rchandan for bug#4428980
833 xah.application_id = 200 AND
834 xah.entity_id = xte.entity_id AND
835 xte.application_id = 200 AND
836 xte.entity_code = lv_pay_entity_code AND --'AP_PAYMENTS'
837 xte.source_id_int_1 = ac.check_id AND
838 xah.event_id = app.accounting_event_id AND
839 api.invoice_id = app.invoice_id AND
840 app.check_id = ac.check_id AND
841 ac.status_lookup_code IN (lv_cleared,lv_negotiable,lv_voided,
842 lv_rec_unacc,lv_reconciled,
843 lv_cleared_unacc ) AND --rchandan for bug#4428980
844 api.vendor_id = p_vendor_id AND
845 api.vendor_site_id = v_vendor_site_id AND
846 xah.ACCOUNTING_DATE < p_bal_date AND
847 (api.org_id = p_org_id or api.org_id is null )
848 AND api.invoice_currency_code =nvl( p_currency_code,api.invoice_currency_code) /* Added by vumaasha for bug 8310720 */
849 AND api.accts_pay_code_combination_id = nvl(p_accts,api.accts_pay_code_combination_id) /* Added by vumaasha for bug 8310720 */;
850
851 /*Commented Query 6 , as query 5 and 6 are identical
852 union all
853 -- Query 6
854 Gain or Loss source AP_INVOICE_PAYMENTS
855 Bug 4997569. Added by Lakshmi Gopalsami
856 (1) Changed the reference to xla_ae_headers instead of ap_ae_headers_all
857 and xla_ae_lines instead of xla_ae_lines.
858 (2) Changed ae_line_type_code to accounting_class_code
859 (3) Also added xla_transaction_entities to get the entity_id and
860 source_int_id_1 so that it can be joined with transaction tables.
861
862 select 'GAIN' invoice_type_lookup_code,
863 0 debit_val,
864 DECODE(xal.accounting_class_code,'GAIN', accounted_cr,0) acct_dr,
865 xal.currency_conversion_rate exchange_rate ,
866 xal.currency_conversion_type exchange_rate_type,
867 xal.currency_code invoice_currency_code,
868 xal.currency_conversion_date exchange_date
869 from xla_ae_lines xal,
870 xla_ae_headers xah,
871 xla_transaction_entities xte,
872 ap_invoices_all api,
873 ap_checks_all ac ,
874 ap_invoice_payments_all app
875 where xal.application_id = 200 AND
876 xal.ae_header_id = xah.ae_header_id AND
877 xal.accounting_class_code in ( p_gain,p_loss) AND --rchandan for bug#4428980
878 xah.application_id = 200 AND
879 xah.entity_id = xte.entity_id AND
880 xte.application_id = 200 AND
881 xte.entity_code = lv_pay_entity_code AND --'AP_PAYMENTS'
882 xte.source_id_int_1 = ac.check_id AND
883 xah.event_id = app.accounting_event_id AND
884 api.invoice_id = app.invoice_id AND
885 app.check_id = ac.check_id AND
886 ac.status_lookup_code IN (lv_cleared,lv_negotiable,lv_voided,
887 lv_rec_unacc,lv_reconciled,
888 lv_cleared_unacc ) AND --rchandan for bug#4428980
889 api.vendor_id = p_vendor_id AND
890 api.vendor_site_id = v_vendor_site_id AND
891 xah.ACCOUNTING_DATE < p_bal_date AND
892 (api.org_id = p_org_id or api.org_id is null )
893 ; Commented by nprashar for bug 8307469*/
894
895 CURSOR C_vendor_site_id IS
896 SELECT vendor_site_id
897 FROM po_vendor_sites_all
898 WHERE vendor_id = p_vendor_id
899 AND org_id = p_org_id /* Added for Bug 2839228 */
900 AND vendor_site_code = p_vendor_site_code ;
901
902 BEGIN
903
904 -- get the vendor site id
905 OPEN C_vendor_site_id;
906 FETCH C_vendor_site_id INTO v_vendor_site_id;
907 CLOSE C_vendor_site_id;
908
909
910 v_debit_bal := 0;
911
912 FOR inv_rec IN C_invoices('GAIN','LOSS') LOOP --rchandan for bug#4428980
913
914 v_invoice_amount := 0;
915 v_exchange_rate := 1;
916
917 /* For bug 4035943. Added by LGOPALSA
918 Added the logic for GAIN Lines */
919 If inv_rec.invoice_type_lookup_code ='GAIN' Then
920
921 v_invoice_amount := nvl(inv_rec.acct_dr,0);
922
923 Else
924
925 IF inv_rec.exchange_rate_type IS NOT NULL THEN
926
927 v_exchange_rate := jai_cmn_utils_pkg.currency_conversion( p_set_of_books_id,
928 inv_rec.invoice_currency_code,
929 inv_rec.exchange_date,
930 inv_rec.exchange_rate_type,
931 inv_rec.exchange_rate );
932
933 v_invoice_amount := NVL(inv_rec.debit_val, 0) * NVL(v_exchange_rate, 1);
934
935 ELSE
936 v_invoice_amount := NVL(inv_rec.debit_val, 0) ;
937 END IF;
938
939 End if;
940
941 v_debit_bal := v_debit_bal + v_invoice_amount;
942
943 END LOOP ;
944
945 RETURN (round( NVL(v_debit_bal, 0),2));
946
947 EXCEPTION
948 WHEN OTHERS THEN
949 RAISE_APPLICATION_ERROR(-20009,'Exception in function Ja_In_Cledger_debit_Bal : ' || SQLERRM);
950
951 END compute_debit_balance;
952
953
954 PROCEDURE process_report
955 (
956 p_invoice_date_from IN date,
957 p_invoice_date_to IN date,
958 p_vendor_id IN number,
959 p_vendor_site_id IN number,
960 p_org_id IN NUMBER,
961 p_run_no OUT NOCOPY number,
962 p_error_message OUT NOCOPY varchar2
963 )
964 IS
965
966 cursor c_get_run_no IS
967 select JAI_PO_REP_PRRG_T_RUNNO_S.nextval
968 from dual;
969
970 cursor c_inv_select_cursor( c_line_type_lookup_code ap_invoice_distributions_all.line_type_lookup_code%TYPE ) IS --rchandan for bug#4428980
971 select invoice_id, invoice_num, org_id, vendor_id, vendor_site_id, invoice_date,
972 invoice_currency_code, nvl(exchange_rate,1) exchange_rate, voucher_num
973 from ap_invoices_all aia
974 where cancelled_date is null
975 and (p_vendor_id is null or vendor_id = p_vendor_id)
976 and (p_vendor_site_id is null or vendor_site_id = p_vendor_site_id)
977 and (p_org_id is null or org_id = p_org_id)
978 and exists
979 (select '1'
980 from ap_invoice_distributions_all
981 where invoice_id = aia.invoice_id
982 and line_type_lookup_code = c_line_type_lookup_code
983 and po_distribution_id is not null
984 and nvl(reversal_flag, 'N') <> 'Y'
985 and accounting_date >= p_invoice_date_from /* Modified by Ramananda for bug:4071409 */
986 and accounting_date <= p_invoice_date_to /* Modified by Ramananda for bug:4071409 */
987 );
988
989 cursor c_inv_item_lines(p_invoice_id number,c_line_type_lookup_code ap_invoice_distributions_all.line_type_lookup_code%TYPE) is
990 select
991 distribution_line_number,
992 po_distribution_id,
993 rcv_transaction_id,
994 amount,
995 invoice_distribution_id,
996 invoice_line_number
997 from ap_invoice_distributions_all
998 where invoice_id = p_invoice_id
999 and line_type_lookup_code = c_line_type_lookup_code
1000 and po_distribution_id is not null
1001 and nvl(reversal_flag, 'N') <> 'Y'
1002 and accounting_date >= p_invoice_date_from /* Modified by Ramananda for bug:4071409 */
1003 and accounting_date <= p_invoice_date_to; /* Modified by Ramananda for bug:4071409 */
1004
1005
1006
1007
1008 cursor c_get_po_details(p_po_distribution_id number) is
1009 select
1010 po_header_id,
1011 segment1,
1012 trunc(creation_date) po_date
1013 from po_headers_all
1014 where po_header_id =
1015 ( select po_header_id
1016 from po_distributions_all
1017 where po_distribution_id = p_po_distribution_id);
1018
1019 cursor c_get_po_release (p_po_distribution_id number) is
1020 select release_num, release_date
1021 from po_releases_all
1022 where po_release_id in
1023 (
1024 select po_release_id
1025 from po_line_locations_all
1026 where (po_header_id, po_line_id, line_location_id ) in
1027 (
1028 select po_header_id, po_line_id, line_location_id
1029 from po_distributions_all
1030 where po_distribution_id = p_po_distribution_id
1031 )
1032 );
1033
1034
1035
1036 cursor c_get_receipt_num(p_transaction_id number) is
1037 select receipt_num, trunc(creation_date) receipt_date
1038 from rcv_shipment_headers
1039 where shipment_header_id =
1040 ( select shipment_header_id
1041 from rcv_transactions
1042 where transaction_id = p_transaction_id);
1043
1044
1045 cursor c_get_tax_from_ap (
1046 p_invoice_id number,
1047 p_parent_distribution_id number,
1048 p_po_distribution_id number) is
1049 select distribution_line_number, tax_id
1050 from JAI_AP_MATCH_INV_TAXES
1051 where invoice_id = p_invoice_id
1052 and parent_invoice_distribution_id = p_parent_distribution_id
1053 and po_distribution_id = p_po_distribution_id
1054 union
1055 select distribution_line_number, tax_id
1056 from JAI_AP_MATCH_INV_TAXES
1057 where invoice_id = p_invoice_id
1058 and parent_invoice_distribution_id is null
1059 and po_distribution_id is null
1060 and (po_header_id, po_line_id, line_location_id)
1061 in
1062 (
1063 select po_header_id, po_line_id, line_location_id
1064 from po_distributions_all
1065 where po_distribution_id = p_po_distribution_id
1066 );
1067
1068 cursor c_get_tax_type(p_tax_id number) is
1069 select upper(tax_type) tax_type
1070 from JAI_CMN_TAXES_ALL
1071 where tax_id = p_tax_id;
1072
1073 cursor c_get_misc_tax_line_amt (p_invoice_id number, p_distribution_line_number number) is
1074 select amount
1075 from ap_invoice_distributions_all
1076 where invoice_id = p_invoice_id
1077 and distribution_line_number = p_distribution_line_number
1078 and accounting_date >= p_invoice_date_from /* Modified by Ramananda for bug:4071409 */
1079 and accounting_date <= p_invoice_date_to; /* Modified by Ramananda for bug:4071409 */
1080
1081
1082 cursor c_get_tax_from_receipt
1083 (
1084 p_invoice_id number,
1085 p_parent_distribution_id number,
1086 p_po_distribution_id number,
1087 p_rcv_transaction_id number
1088 ) is
1089 select tax_id, upper(tax_type) tax_type, currency, tax_amount
1090 from JAI_RCV_LINE_TAXES
1091 where (shipment_header_id, shipment_line_id)
1092 in
1093 (select shipment_header_id, shipment_line_id
1094 from rcv_transactions
1095 where transaction_id = p_rcv_transaction_id)
1096 and tax_id not in
1097 (
1098 select tax_id
1099 from JAI_AP_MATCH_INV_TAXES
1100 where invoice_id = p_invoice_id
1101 and parent_invoice_distribution_id = p_parent_distribution_id
1102 and po_distribution_id = p_po_distribution_id
1103 union
1104 select tax_id
1105 from JAI_AP_MATCH_INV_TAXES
1106 where invoice_id = p_invoice_id
1107 and parent_invoice_distribution_id is null
1108 and po_distribution_id is null
1109 and (po_header_id, po_line_id, line_location_id)
1110 in
1111 (
1112 select po_header_id, po_line_id, line_location_id
1113 from po_distributions_all
1114 where po_distribution_id = p_po_distribution_id
1115 )
1116 )
1117 ;
1118
1119
1120
1121 cursor c_get_tax_from_po
1122 (
1123 p_invoice_id number,
1124 p_parent_distribution_id number,
1125 p_po_distribution_id number,
1126 p_rcv_transaction_id number
1127 ) is
1128 select tax_id, upper(tax_type) tax_type, currency, tax_amount
1129 from JAI_PO_TAXES
1130 where (po_header_id, po_line_id, line_location_id)
1131 in
1132 (select po_header_id, po_line_id, line_location_id
1133 from po_distributions_all
1134 where po_distribution_id = p_po_distribution_id)
1135 and tax_id not in
1136 (
1137 select tax_id
1138 from JAI_AP_MATCH_INV_TAXES
1139 where invoice_id = p_invoice_id
1140 and parent_invoice_distribution_id = p_parent_distribution_id
1141 and po_distribution_id = p_po_distribution_id
1142 union
1143 select tax_id
1144 from JAI_AP_MATCH_INV_TAXES
1145 where invoice_id = p_invoice_id
1146 and parent_invoice_distribution_id is null
1147 and po_distribution_id is null
1148 and (po_header_id, po_line_id, line_location_id)
1149 in
1150 (
1151 select po_header_id, po_line_id, line_location_id
1152 from po_distributions_all
1153 where po_distribution_id = p_po_distribution_id
1154 )
1155 );
1156
1157
1158
1159 v_run_no number;
1160 v_po_header_id po_headers_all.po_header_id%type;
1161 v_po_number po_headers_all.segment1%type;
1162 v_po_date date;
1163 v_receipt_num rcv_shipment_headers.receipt_num%type;
1164 v_receipt_date date;
1165 v_tax_type JAI_CMN_TAXES_ALL.tax_type%type;
1166 v_po_release_num po_releases_all.release_num%type;
1167 v_po_release_date date;
1168
1169 v_excise_ap number;
1170 v_customs_ap number;
1171 v_cvd_ap number;
1172 v_cst_ap number;
1173 v_lst_ap number;
1174 v_freight_ap number;
1175 v_octroi_ap number;
1176 v_others_ap number;
1177
1178 v_excise_po number;
1179 v_customs_po number;
1180 v_cvd_po number;
1181 v_cst_po number;
1182 v_lst_po number;
1183 v_freight_po number;
1184 v_octroi_po number;
1185 v_others_po number;
1186
1187 v_tax_amt number;
1188
1189 v_conversion_factor number;
1190
1191 v_statement_id number:=0;
1192
1193
1194 BEGIN
1195
1196 /* -----------------------------------------------------------------------------
1197 FILENAME: ja_in_prrg_report_temp_proc_p.sql
1198 CHANGE HISTORY:
1199
1200 S.No Date Author and Details
1201 1 14/06/2004 Created by Aparajita for bug#3633078. Version#115.0.
1202
1203 This procedure populates temporary table JAI_PO_REP_PRRG_T,
1204 to be used by the purchase register report.
1205
1206 Depending on the input parameter, all invoices are selected.
1207 Taxes that have been already brought over to payable invoice
1208 as 'miscellaneous' distribution lines are considered by their tax
1209 type.
1210
1211 For each line the taxes from the corresponding Receipt / PO are
1212 again considered for any tax that is not brought over to AP. This is
1213 possible as third party taxes and taxes like cvd and customs are not brought
1214 over to AP. These taxes are also grouped by their tax type. These taxes
1215 from purchasing side are checked for apportion factor for changes in Quantity,
1216 Price and UOM for each line. Each tax line's currency is also compared against
1217 invoice currency and is converted to invoice currency if required.
1218
1219 Taxes are grouped as follows,
1220
1221 excise
1222 customs
1223 cvd
1224 cst
1225 lst
1226 freight
1227 octroi
1228 others
1229
1230 2 31/12/2004 Created by Ramananda for bug#4071409. Version#115.1
1231
1232 Issue:-
1233 The report JAINPRRG.rdf calls this procedure jai_ap_rpt_prrg_pkg.process_report.
1234 A set of from and to dates are being passed to this report.Currently the report
1235 picks up the invoices based on these parameters and the details of these
1236 picked up invoices are displayed in the report
1237 Reason:-
1238 Invoice date is checked against the input date parameters to pick the invoices
1239 Fix:-
1240 Accounting date is used against the input date parameters to pick the invoices
1241 Dependency due to this bug:-
1242 None
1243
1244 Future Dependencies For the release Of this Object:-
1245 ==================================================
1246 Please add a row in the section below only if your bug introduces a dependency
1247 like,spec change/ A new call to a object/A datamodel change.
1248
1249 --------------------------------------------------------------------------------
1250 Version Bug Dependencies (including other objects like files if any)
1251 --------------------------------------------------------------------------------
1252 115.0 3633078 Datamodel dependencies
1253
1254 --------------------------------------------------------------------------------- */
1255
1256 -- get the run_no
1257 v_statement_id:= 1;
1258 open c_get_run_no;
1259 fetch c_get_run_no into v_run_no;
1260 close c_get_run_no;
1261
1262 v_statement_id:= 2;
1263 for c_inv_select_rec in c_inv_select_cursor('ITEM') loop--rchandan for bug#4428980
1264
1265 v_statement_id:= 3;
1266
1267 -- check and loop through all the eligible item lines and populate the temp table
1268 for c_item_lines_rec in c_inv_item_lines(c_inv_select_rec.invoice_id,'ITEM') loop
1269
1270 v_statement_id:= 4;
1271
1272 v_po_header_id := null;
1273 v_po_number := null;
1274 v_receipt_num := null;
1275 v_receipt_date := null;
1276 v_po_date := null;
1277 v_po_release_num := null;
1278 v_po_release_date := null;
1279
1280
1281 v_excise_ap := 0;
1282 v_customs_ap := 0;
1283 v_cvd_ap := 0;
1284 v_cst_ap := 0;
1285 v_lst_ap := 0;
1286 v_freight_ap := 0;
1287 v_octroi_ap := 0;
1288 v_others_ap := 0;
1289
1290 v_excise_po := 0;
1291 v_customs_po := 0;
1292 v_cvd_po := 0;
1293 v_cst_po := 0;
1294 v_lst_po := 0;
1295 v_freight_po := 0;
1296 v_octroi_po := 0;
1297 v_others_po := 0;
1298
1299 v_conversion_factor := 1;
1300
1301 v_statement_id:= 5;
1302 -- get the PO reference for the item line
1303 open c_get_po_details(c_item_lines_rec.po_distribution_id);
1304 fetch c_get_po_details into v_po_header_id, v_po_number, v_po_date;
1305 close c_get_po_details;
1306
1307 v_statement_id:= 6;
1308 open c_get_po_release(c_item_lines_rec.po_distribution_id);
1309 fetch c_get_po_release into v_po_release_num, v_po_release_date;
1310 close c_get_po_release;
1311
1312
1313 -- get the receipt reference
1314 if c_item_lines_rec.rcv_transaction_id is not null then
1315 v_statement_id:= 7;
1316 open c_get_receipt_num(c_item_lines_rec.rcv_transaction_id);
1317 fetch c_get_receipt_num into v_receipt_num, v_receipt_date;
1318 close c_get_receipt_num;
1319 end if;
1320
1321
1322 -- get tax from payables side
1323 for c_get_tax_from_ap_rec in
1324 c_get_tax_from_ap
1325 (
1326 c_inv_select_rec.invoice_id,
1327 c_item_lines_rec.invoice_distribution_id,
1328 c_item_lines_rec.po_distribution_id)
1329 loop
1330
1331 v_statement_id:= 8;
1332
1333 v_tax_type := null;
1334 v_tax_amt := 0;
1335
1336 open c_get_tax_type(c_get_tax_from_ap_rec.tax_id);
1337 fetch c_get_tax_type into v_tax_type;
1338 close c_get_tax_type;
1339
1340 v_statement_id:= 9;
1341
1342 open c_get_misc_tax_line_amt
1343 (c_inv_select_rec.invoice_id, c_get_tax_from_ap_rec.distribution_line_number);
1344 fetch c_get_misc_tax_line_amt into v_tax_amt;
1345 close c_get_misc_tax_line_amt;
1346
1347 v_statement_id:= 10;
1348
1349 if v_tax_type in ('ADDL. EXCISE', 'EXCISE', 'OTHER EXCISE') then
1350 v_excise_ap := v_excise_ap + v_tax_amt;
1351 elsif v_tax_type = 'CST' then
1352 v_cst_ap := v_cst_ap + v_tax_amt;
1353 elsif v_tax_type = 'SALES TAX' then
1354 v_lst_ap := v_lst_ap + v_tax_amt;
1355 elsif v_tax_type = 'CUSTOMS' then
1356 v_customs_ap := v_customs_ap + v_tax_amt;
1357 elsif v_tax_type = 'CVD' then
1358 v_cvd_ap := v_cvd_ap + v_tax_amt;
1359 elsif v_tax_type = 'FREIGHT' then
1360 v_freight_ap := v_freight_ap + v_tax_amt;
1361 elsif v_tax_type = 'OCTRAI' then
1362 v_octroi_ap := v_octroi_ap + v_tax_amt;
1363 else
1364 v_others_ap := v_others_ap + v_tax_amt;
1365 end if;
1366
1367 end loop; --c_get_tax_from_ap_rec
1368
1369 -- Get taxes from source doc PO / Receipt that are not brought over to AP
1370
1371 -- get the conversion factor considering UOM, Quantity and Price change
1372 v_statement_id:= 11;
1373 v_conversion_factor := jai_ap_utils_pkg.get_apportion_factor(c_inv_select_rec.invoice_id,c_item_lines_rec.invoice_line_number);
1374
1375
1376 if nvl(v_conversion_factor, 0) = 0 then
1377 v_conversion_factor := 1;
1378 end if;
1379
1380
1381 -- If invoice currency and tax currency are different then conversion is required.
1382
1383 if c_item_lines_rec.rcv_transaction_id is not null then
1384
1385 v_statement_id:= 12;
1386 -- get from receipt.
1387
1388 for c_receipt_tax_rec in c_get_tax_from_receipt
1389 (
1390 c_inv_select_rec.invoice_id,
1391 c_item_lines_rec.invoice_distribution_id,
1392 c_item_lines_rec.po_distribution_id,
1393 c_item_lines_rec.rcv_transaction_id
1394 )
1395 loop
1396
1397 v_statement_id:= 13;
1398 v_tax_type := c_receipt_tax_rec.tax_type;
1399 v_tax_amt := c_receipt_tax_rec.tax_amount;
1400
1401
1402 v_tax_amt := v_tax_amt * v_conversion_factor;
1403
1404 v_statement_id:= 14;
1405 if c_inv_select_rec.invoice_currency_code <> c_receipt_tax_rec.currency then
1406 v_tax_amt := v_tax_amt / c_inv_select_rec.exchange_rate;
1407 end if;
1408
1409
1410 if v_tax_type in ('ADDL. EXCISE', 'EXCISE', 'OTHER EXCISE') then
1411 v_excise_po := v_excise_po + v_tax_amt;
1412 elsif v_tax_type = 'CST' then
1413 v_cst_po := v_cst_po + v_tax_amt;
1414 elsif v_tax_type = 'SALES TAX' then
1415 v_lst_po := v_lst_po + v_tax_amt;
1416 elsif v_tax_type = 'CUSTOMS' then
1417 v_customs_po := v_customs_po + v_tax_amt;
1418 elsif v_tax_type = 'CVD' then
1419 v_cvd_po := v_cvd_po + v_tax_amt;
1420 elsif v_tax_type = 'FREIGHT' then
1421 v_freight_po := v_freight_po + v_tax_amt;
1422 elsif v_tax_type = 'OCTRAI' then
1423 v_octroi_po := v_octroi_po + v_tax_amt;
1424 else
1425 v_others_po := v_others_po + v_tax_amt;
1426 end if;
1427
1428 v_statement_id:= 15;
1429
1430 end loop; -- c_receipt_tax_rec
1431
1432 else
1433 -- get from po
1434
1435 for c_get_tax_from_po_rec in c_get_tax_from_po
1436 (
1437 c_inv_select_rec.invoice_id,
1438 c_item_lines_rec.invoice_distribution_id,
1439 c_item_lines_rec.po_distribution_id,
1440 c_item_lines_rec.rcv_transaction_id
1441 )
1442
1443 loop
1444
1445 v_statement_id:= 16;
1446
1447 v_tax_type := c_get_tax_from_po_rec.tax_type;
1448 v_tax_amt := c_get_tax_from_po_rec.tax_amount;
1449
1450 v_tax_amt := v_tax_amt * v_conversion_factor;
1451
1452 v_statement_id:= 17;
1453
1454 if c_inv_select_rec.invoice_currency_code <> c_get_tax_from_po_rec.currency then
1455 v_tax_amt := v_tax_amt / c_inv_select_rec.exchange_rate;
1456 end if;
1457
1458 if v_tax_type in ('ADDL. EXCISE', 'EXCISE', 'OTHER EXCISE') then
1459 v_excise_po := v_excise_po + v_tax_amt;
1460 elsif v_tax_type = 'CST' then
1461 v_cst_po := v_cst_po + v_tax_amt;
1462 elsif v_tax_type = 'SALES TAX' then
1463 v_lst_po := v_lst_po + v_tax_amt;
1464 elsif v_tax_type = 'CUSTOMS' then
1465 v_customs_po := v_customs_po + v_tax_amt;
1466 elsif v_tax_type = 'CVD' then
1467 v_cvd_po := v_cvd_po + v_tax_amt;
1468 elsif v_tax_type = 'FREIGHT' then
1469 v_freight_po := v_freight_po + v_tax_amt;
1470 elsif v_tax_type = 'OCTRAI' then
1471 v_octroi_po := v_octroi_po + v_tax_amt;
1472 else
1473 v_others_po := v_others_po + v_tax_amt;
1474 end if;
1475
1476 v_statement_id:= 18;
1477
1478 end loop; -- c_get_tax_from_po_rec
1479
1480 end if;
1481
1482 v_statement_id:= 19;
1483 -- insert into the temp table with all the values.
1484 insert into JAI_PO_REP_PRRG_T
1485 (
1486 run_no,
1487 org_id,
1488 vendor_id,
1489 vendor_site_id,
1490 invoice_id,
1491 invoice_num,
1492 invoice_date,
1493 invoice_currency_code,
1494 exchange_rate,
1495 voucher_num,
1496 distribution_line_number,
1497 po_number,
1498 po_header_id,
1499 po_creation_date,
1500 po_distribution_id,
1501 po_release_num,
1502 receipt_number,
1503 receipt_date,
1504 rcv_transaction_id,
1505 line_amount,
1506 excise,
1507 customs,
1508 cvd,
1509 cst,
1510 lst,
1511 freight,
1512 octroi,
1513 others,
1514 -- added, Harshita for Bug 4866533
1515 created_by,
1516 creation_date,
1517 last_updated_by,
1518 last_update_date
1519 )
1520 values
1521 (
1522 v_run_no,
1523 c_inv_select_rec.org_id ,
1524 c_inv_select_rec.vendor_id,
1525 c_inv_select_rec.vendor_site_id,
1526 c_inv_select_rec.invoice_id,
1527 c_inv_select_rec.invoice_num,
1528 c_inv_select_rec.invoice_date,
1529 c_inv_select_rec.invoice_currency_code,
1530 c_inv_select_rec.exchange_rate,
1531 c_inv_select_rec.voucher_num,
1532 c_item_lines_rec.distribution_line_number,
1533 v_po_number,
1534 v_po_header_id,
1535 nvl(v_po_release_date, v_po_date),
1536 c_item_lines_rec.po_distribution_id,
1537 nvl(v_po_release_num, 0),
1538 v_receipt_num,
1539 v_receipt_date,
1540 c_item_lines_rec.rcv_transaction_id,
1541 c_item_lines_rec.amount,
1542 v_excise_ap + v_excise_po,
1543 v_customs_ap + v_customs_po,
1544 v_cvd_ap + v_cvd_po,
1545 v_cst_ap + v_cst_po,
1546 v_lst_ap + v_lst_po,
1547 v_freight_ap + v_freight_po,
1548 v_octroi_ap + v_octroi_po,
1549 v_others_ap + v_others_po,
1550 -- added, Harshita for Bug 4866533
1551 fnd_global.user_id,
1552 sysdate,
1553 fnd_global.user_id,
1554 sysdate
1555 );
1556
1557
1558 v_statement_id:= 19;
1559
1560 end loop; -- c_item_lines_rec
1561
1562 v_statement_id:= 20;
1563
1564 end loop;-- c_inv_select_cursor
1565
1566 p_run_no := v_run_no;
1567
1568 EXCEPTION
1569 when others then
1570 p_error_message := 'Error from Proc jai_ap_rpt_prrg_pkg.process_report(Statement id):'
1571 || '(' || v_statement_id || ')' || sqlerrm;
1572
1573 END process_report;
1574
1575 END jai_ap_rpt_apcr_pkg;
1576
[Home] [Help]