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