[Home] [Help]
PACKAGE BODY: APPS.JAI_AP_RPT_PRRG_PKG
Source
1 PACKAGE BODY jai_ap_rpt_prrg_pkg AS
2 /* $Header: jai_ap_rpt_prrg.plb 120.7.12010000.6 2009/01/08 05:34:21 nprashar ship $ */
3
4 /* --------------------------------------------------------------------------------------
5 Filename:
6
7 Change History:
8
9 Date Bug Remarks
10 --------- ---------- -------------------------------------------------------------
11 08-Jun-2005 Version 116.1 jai_ap_rpt_prrg -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
12 as required for CASE COMPLAINCE.
13
14 14-Jun-2005 rchandan for bug#4428980, Version 116.2
15 Modified the object to remove literals from DML statements and CURSORS.
16 As part OF R12 Initiative Inventory conversion the OPM code is commented
17
18 09-Dec-2005 4866533 Added by Lakshmi Gopalsami Version 120.3
19 Added WHO columns in insert to jai_po_rep_prrg_t
20
21
22 01/11/2006 SACSETHI for bug 5228046, File version 120.4
23 Forward porting the change in 11i bug 5365523 (Additional CVD Enhancement).
24 This bug has datamodel and spec changes.
25
26 01/17/2008 Kevin Cheng for Inclusive Tax calculation
27 Add query criteria to eliminate Inclusive taxes from results.
28
29 25-FEB-2008 Changes done by nprashar for bug # 6803557.
30 Added a column invoice_distribution-id in cursor definition c_get_tax_from_ap also
31 changed the cursor c_get_tax_type.Added a parameter to cursor c_get_misc_tax_line_amt , p_invoice_distribution_id.
32
33 8-july-2008 Changes by nprashar for bug # 7225946. Changes in defintion of cursor c_inv_select_cursor,c_inv_item_lines.
34
35 05-Nov-2008 Modified by JMEENA for bug#7621541
36 Removed the input parameter 'ITEM' from cursor c_inv_select_cursor,c_inv_item_lines.
37 */
38
39 PROCEDURE process_report
40 (
41 p_invoice_date_from IN date,
42 p_invoice_date_to IN date,
43 p_vendor_id IN number,
44 p_vendor_site_id IN number,
45 p_org_id IN NUMBER,
46 p_run_no OUT NOCOPY number,
47 p_error_message OUT NOCOPY varchar2
48 ) IS
49
50 cursor c_get_run_no is
51 select JAI_PO_REP_PRRG_T_RUNNO_S.nextval
52 from dual;
53
54 cursor c_inv_select_cursor is /*Signature change of cursor by nprahsar for bug # 7225946*/ --rchandan for bug#4428980
55 select invoice_id, invoice_num, org_id, vendor_id, vendor_site_id, invoice_date,
56 invoice_currency_code, nvl(exchange_rate,1) exchange_rate, voucher_num
57 from ap_invoices_all aia
58 where cancelled_date is null
59 and (p_vendor_id is null or vendor_id = p_vendor_id)
60 and (p_vendor_site_id is null or vendor_site_id = p_vendor_site_id)
61 and (p_org_id is null or org_id = p_org_id)
62 and exists
63 (select '1'
64 from ap_invoice_distributions_all
65 where invoice_id = aia.invoice_id
66 and line_type_lookup_code in ('ITEM','ACCRUAL')--nprahsar for bug # 7225946*/
67 and po_distribution_id is not null
68 and nvl(reversal_flag, 'N') <> 'Y'
69 and accounting_date >= p_invoice_date_from /* Modified by Ramananda for bug:4071409 */
70 and accounting_date <= p_invoice_date_to /* Modified by Ramananda for bug:4071409 */
71 );
72
73 cursor c_inv_item_lines(p_invoice_id number) is /*Signature change of cursor by nprashar for bug # 7225946*/ --rchandan for bug#4428980
74 select
75 distribution_line_number,
76 po_distribution_id,
77 rcv_transaction_id,
78 amount,
79 invoice_distribution_id,
80 invoice_line_number
81 /*
82 In the above cursor added invoice_line_number by Brathod, for Bug#4510143 to pass invoice_line_number
83 as parameter to jai_ap_utils_pkg.get_apportion_factor
84 */
85 from ap_invoice_distributions_all
86 where invoice_id = p_invoice_id
87 and line_type_lookup_code in ('ITEM','ACCRUAL')
88 and po_distribution_id is not null
89 and nvl(reversal_flag, 'N') <> 'Y'
90 and accounting_date >= p_invoice_date_from /* Modified by Ramananda for bug:4071409 */
91 and accounting_date <= p_invoice_date_to; /* Modified by Ramananda for bug:4071409 */
92
93
94
95
96 cursor c_get_po_details(p_po_distribution_id number) is
97 select
98 po_header_id,
99 segment1,
100 trunc(creation_date) po_date
101 from po_headers_all
102 where po_header_id =
103 ( select po_header_id
104 from po_distributions_all
105 where po_distribution_id = p_po_distribution_id);
106
107 cursor c_get_po_release (p_po_distribution_id number) is
108 select release_num, release_date
109 from po_releases_all
110 where po_release_id in
111 (
112 select po_release_id
113 from po_line_locations_all
114 where (po_header_id, po_line_id, line_location_id ) in
115 (
116 select po_header_id, po_line_id, line_location_id
117 from po_distributions_all
118 where po_distribution_id = p_po_distribution_id
119 )
120 );
121
122
123
124 cursor c_get_receipt_num(p_transaction_id number) is
125 select receipt_num, trunc(creation_date) receipt_date
126 from rcv_shipment_headers
127 where shipment_header_id =
128 ( select shipment_header_id
129 from rcv_transactions
130 where transaction_id = p_transaction_id);
131
132 cursor c_get_tax_from_ap (
133 p_invoice_id number,
134 p_parent_distribution_id number,
135 p_po_distribution_id number) is
136 select distribution_line_number, tax_id,invoice_distribution_id /*Changed by nprashar for bug # 6803557 */
137 from JAI_AP_MATCH_INV_TAXES
138 where invoice_id = p_invoice_id
139 and parent_invoice_distribution_id = p_parent_distribution_id
140 and po_distribution_id = p_po_distribution_id
141 union
142 select distribution_line_number, tax_id,invoice_distribution_id /*Changed by nprashar for bug # 6803557 */
143 from JAI_AP_MATCH_INV_TAXES
144 where invoice_id = p_invoice_id
145 and parent_invoice_distribution_id is null
146 and po_distribution_id is null
147 and (po_header_id, po_line_id, line_location_id)
148 in
149 (
150 select po_header_id, po_line_id, line_location_id
151 from po_distributions_all
152 where po_distribution_id = p_po_distribution_id
153 );
154
155 cursor c_get_tax_type(p_tax_id number) is
156 select UPPER(tax_type) tax_type /*Changes by nprashar for bug 7678389, replaced initcap by Upper function Changes by nprashar for bug # 6803557 */
157 from JAI_CMN_TAXES_ALL
158 where tax_id = p_tax_id;
159
160 cursor c_get_misc_tax_line_amt (p_invoice_id number, p_distribution_line_number number, p_invoice_distribution_id number ) is
161 select amount
162 from ap_invoice_distributions_all
163 where invoice_id = p_invoice_id
164 and distribution_line_number = p_distribution_line_number
165 and invoice_distribution_id = p_invoice_distribution_id /*Added by nprashar for Bug # 6803557*/
166 and accounting_date >= p_invoice_date_from /* Modified by Ramananda for bug:4071409 */
167 and accounting_date <= p_invoice_date_to; /* Modified by Ramananda for bug:4071409 */
168
169
170 cursor c_get_tax_from_receipt
171 (
172 p_invoice_id number,
173 p_parent_distribution_id number,
174 p_po_distribution_id number,
175 p_rcv_transaction_id number
176 ) is
177 select A.tax_id, upper(A.tax_type) tax_type, A.currency, A.tax_amount
178 from JAI_RCV_LINE_TAXES A, JAI_CMN_TAXES_ALL B -- Added by Kevin Cheng for Inclusive Tax
179 where (A.shipment_header_id, A.shipment_line_id)
180 in
181 (select shipment_header_id, shipment_line_id
182 from rcv_transactions
183 where transaction_id = p_rcv_transaction_id)
184 and A.tax_id not in
185 (
186 select tax_id
187 from JAI_AP_MATCH_INV_TAXES
188 where invoice_id = p_invoice_id
189 and parent_invoice_distribution_id = p_parent_distribution_id
190 and po_distribution_id = p_po_distribution_id
191 union
192 select tax_id
193 from JAI_AP_MATCH_INV_TAXES
194 where invoice_id = p_invoice_id
195 and parent_invoice_distribution_id is null
196 and po_distribution_id is null
197 and (po_header_id, po_line_id, line_location_id)
198 in
199 (
200 select po_header_id, po_line_id, line_location_id
201 from po_distributions_all
202 where po_distribution_id = p_po_distribution_id
203 )
204 )
205 AND A.tax_id = B.tax_id -- Added by Kevin Cheng for Inclusive Tax
206 AND nvl(B.inclusive_tax_flag, 'N') = 'N' -- Added by Kevin Cheng for Inclusive Tax
207 ;
208
209
210
211 cursor c_get_tax_from_po
212 (
213 p_invoice_id number,
214 p_parent_distribution_id number,
215 p_po_distribution_id number,
216 p_rcv_transaction_id number
217 ) is
218 select A.tax_id, upper(A.tax_type) tax_type, A.currency, A.tax_amount
219 from JAI_PO_TAXES A, JAI_CMN_TAXES_ALL B -- Added by Kevin Cheng for Inclusive Tax
220 where (A.po_header_id, A.po_line_id, A.line_location_id)
221 in
222 (select po_header_id, po_line_id, line_location_id
223 from po_distributions_all
224 where po_distribution_id = p_po_distribution_id)
225 and A.tax_id not in
226 (
227 select tax_id
228 from JAI_AP_MATCH_INV_TAXES
229 where invoice_id = p_invoice_id
230 and parent_invoice_distribution_id = p_parent_distribution_id
231 and po_distribution_id = p_po_distribution_id
232 union
233 select tax_id
234 from JAI_AP_MATCH_INV_TAXES
235 where invoice_id = p_invoice_id
236 and parent_invoice_distribution_id is null
237 and po_distribution_id is null
238 and (po_header_id, po_line_id, line_location_id)
239 in
240 (
241 select po_header_id, po_line_id, line_location_id
242 from po_distributions_all
243 where po_distribution_id = p_po_distribution_id
244 )
245 )
246 AND A.tax_id = B.tax_id -- Added by Kevin Cheng for Inclusive Tax
247 AND nvl(B.inclusive_tax_flag, 'N') = 'N'; -- Added by Kevin Cheng for Inclusive Tax
248
249
250
251 v_run_no number;
252 v_po_header_id po_headers_all.po_header_id%type;
253 v_po_number po_headers_all.segment1%type;
254 v_po_date date;
255 v_receipt_num rcv_shipment_headers.receipt_num%type;
256 v_receipt_date date;
257 v_tax_type JAI_CMN_TAXES_ALL.tax_type%type;
258 v_po_release_num po_releases_all.release_num%type;
259 v_po_release_date date;
260
261 v_excise_ap number;
262 v_customs_ap number;
263 v_cvd_ap number;
264 v_cst_ap number;
265 v_lst_ap number;
266 v_freight_ap number;
267 v_octroi_ap number;
268 v_others_ap number;
269
270 v_excise_po number;
271 v_customs_po number;
272 v_cvd_po number;
273 v_cst_po number;
274 v_lst_po number;
275 v_freight_po number;
276 v_octroi_po number;
277 v_others_po number;
278
279 v_tax_amt number;
280
281 v_conversion_factor number;
282
283 v_statement_id number:=0;
284
285 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI
286 -- START BUG 5228046
287
288 v_addcvd_ap NUMBER;
289 v_addcvd_po NUMBER;
290
291 -- END BUG 5228046
292
293 -- Date 24-Nov-2006 forward porting Bug 5671126 added by Balaji
294 --start
295 v_vat_ap NUMBER;
296 v_turnover_ap NUMBER;
297 v_entry_ap NUMBER;
298 v_pur_ap NUMBER;
299 v_vat_po NUMBER;
300 v_turnover_po NUMBER;
301 v_entry_po NUMBER;
302 v_pur_po NUMBER;
303 v_service_ap NUMBER;
304 v_service_po NUMBER;
305 --end
306
307
308 BEGIN
309
310 /* -----------------------------------------------------------------------------
311 FILENAME: process_report_p.sql
312 CHANGE HISTORY:
313
314 S.No Date Author and Details
315 1 14/06/2004 Created by Aparajita for bug#3633078. Version#115.0.
316
317 This procedure populates temporary table JAI_PO_REP_PRRG_T,
318 to be used by the purchase register report.
319
320 Depending on the input parameter, all invoices are selected.
321 Taxes that have been already brought over to payable invoice
322 as 'miscellaneous' distribution lines are considered by their tax
323 type.
324
325 For each line the taxes from the corresponding Receipt / PO are
326 again considered for any tax that is not brought over to AP. This is
327 possible as third party taxes and taxes like cvd and customs are not brought
328 over to AP. These taxes are also grouped by their tax type. These taxes
329 from purchasing side are checked for apportion factor for changes in Quantity,
330 Price and UOM for each line. Each tax line's currency is also compared against
331 invoice currency and is converted to invoice currency if required.
332
333 Taxes are grouped as follows,
334
335 excise
336 customs
337 cvd
338 cst
339 lst
340 freight
341 octroi
342 others
343
344 2 31/12/2004 Created by Ramananda for bug#4071409. Version#115.1
345
346 Issue:-
347 The report JAINPRRG.rdf calls this procedure process_report.
348 A set of from and to dates are being passed to this report.Currently the report
349 picks up the invoices based on these parameters and the details of these
350 picked up invoices are displayed in the report
351 Reason:-
352 Invoice date is checked against the input date parameters to pick the invoices
353 Fix:-
354 Accounting date is used against the input date parameters to pick the invoices
355 Dependency due to this bug:-
356 None
357
358
362 like,spec change/ A new call to a object/A datamodel change.
359 Future Dependencies For the release Of this Object:-
360 ==================================================
361 Please add a row in the section below only if your bug introduces a dependency
363
364 --------------------------------------------------------------------------------
365 Version Bug Dependencies (including other objects like files if any)
366 --------------------------------------------------------------------------------
367 115.0 3633078 Datamodel dependencies
368
369 --------------------------------------------------------------------------------- */
370
371 -- get the run_no
372 v_statement_id:= 1;
373 open c_get_run_no;
374 fetch c_get_run_no into v_run_no;
375 close c_get_run_no;
376
377 v_statement_id:= 2;
378 --JMEENA for bug#7621541, Removed Input parameter 'ITEM' from c_inv_select_cursor
379 for c_inv_select_rec in c_inv_select_cursor LOOP --rchandan for bug#4428980
380
381 v_statement_id:= 3;
382
383 -- check and loop through all the eligible item lines and populate the temp table
384 --JMEENA for bug#7621541, Removed Input parameter 'ITEM' from c_inv_item_lines
385 for c_item_lines_rec in c_inv_item_lines(c_inv_select_rec.invoice_id) loop
386
387 v_statement_id:= 4;
388
389 v_po_header_id := null;
390 v_po_number := null;
391 v_receipt_num := null;
392 v_receipt_date := null;
393 v_po_date := null;
394 v_po_release_num := null;
395 v_po_release_date := null;
396
397
398 v_excise_ap := 0;
399 v_customs_ap := 0;
400 v_cvd_ap := 0;
401 v_cst_ap := 0;
402 v_lst_ap := 0;
403 v_freight_ap := 0;
404 v_octroi_ap := 0;
405 v_others_ap := 0;
406
407 v_excise_po := 0;
408 v_customs_po := 0;
409 v_cvd_po := 0;
410 v_cst_po := 0;
411 v_lst_po := 0;
412 v_freight_po := 0;
413 v_octroi_po := 0;
414 v_others_po := 0;
415 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI
416 -- START BUG 5228046
417 v_addcvd_ap := 0;
418 v_addcvd_po := 0;
419 -- END BUG 5228046
420 -- End , Added by Girish w.r.t BUG#5143906( for Additional CVD)
421
422 -- Date 24-Nov-2006 forward porting Bug 5671126 added by Balaji
423 --start
424 v_vat_ap := 0;
425 v_turnover_ap:= 0;
426 v_entry_ap := 0;
427 v_pur_ap := 0;
428 v_vat_po := 0;
429 v_turnover_po:= 0;
430 v_entry_po := 0;
431 v_pur_po := 0;
432 v_service_ap := 0;
433 v_service_po := 0;
434 --end
435 v_conversion_factor := 1;
436
437 v_statement_id:= 5;
438 -- get the PO reference for the item line
439 open c_get_po_details(c_item_lines_rec.po_distribution_id);
440 fetch c_get_po_details into v_po_header_id, v_po_number, v_po_date;
441 close c_get_po_details;
442
443 v_statement_id:= 6;
444 open c_get_po_release(c_item_lines_rec.po_distribution_id);
445 fetch c_get_po_release into v_po_release_num, v_po_release_date;
446 close c_get_po_release;
447
448
449 -- get the receipt reference
450 if c_item_lines_rec.rcv_transaction_id is not null then
451 v_statement_id:= 7;
452 open c_get_receipt_num(c_item_lines_rec.rcv_transaction_id);
453 fetch c_get_receipt_num into v_receipt_num, v_receipt_date;
454 close c_get_receipt_num;
455 end if;
456
457
458 -- get tax from payables side
459 for c_get_tax_from_ap_rec in
460 c_get_tax_from_ap
461 (
462 c_inv_select_rec.invoice_id,
463 c_item_lines_rec.invoice_distribution_id,
464 c_item_lines_rec.po_distribution_id)
465 loop
466
467 v_statement_id:= 8;
468
469 v_tax_type := null;
470 v_tax_amt := 0;
471
472 open c_get_tax_type(c_get_tax_from_ap_rec.tax_id);
473 fetch c_get_tax_type into v_tax_type;
474 close c_get_tax_type;
475
476 v_statement_id:= 9;
477
478 open c_get_misc_tax_line_amt
479 (c_inv_select_rec.invoice_id, c_get_tax_from_ap_rec.distribution_line_number,
480 c_get_tax_from_ap_rec.invoice_distribution_id); /*Added by nprashar for bug # 6803557 */
481 fetch c_get_misc_tax_line_amt into v_tax_amt;
482 close c_get_misc_tax_line_amt;
483
484 v_statement_id:= 10;
485
486 if v_tax_type in (UPPER(jai_constants.tax_type_exc_additional), UPPER(jai_constants.tax_type_excise),UPPER(jai_constants.tax_type_exc_other)) then
487 v_excise_ap := v_excise_ap + v_tax_amt;
488 elsif v_tax_type = UPPER(jai_constants. tax_type_cst) then
489 v_cst_ap := v_cst_ap + v_tax_amt;
490 elsif v_tax_type = UPPER(jai_constants.tax_type_sales) then
491 v_lst_ap := v_lst_ap + v_tax_amt;
492 elsif v_tax_type= UPPER(jai_constants.tax_type_customs) then
496 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI
493 v_customs_ap := v_customs_ap + v_tax_amt;
494 elsif v_tax_type = UPPER(jai_constants.tax_type_cvd) then
495 v_cvd_ap := v_cvd_ap + v_tax_amt;
497 -- START BUG 5228046
498 elsif v_tax_type = UPPER(jai_constants.tax_type_add_cvd) then
499 v_addcvd_ap := v_addcvd_ap + v_tax_amt;
500 -- END BUG 5228046
501 -- Date 24-Nov-2006 Forward porting Bug 5671126 added by Balaji
502 --start
503 elsif v_tax_type = UPPER(jai_constants.tax_type_value_added) then
504 v_vat_ap := v_vat_ap + v_tax_amt;
505 elsif v_tax_type = UPPER(jai_constants.tax_type_purchase) then
506 v_pur_ap := v_pur_ap + v_tax_amt;
507 elsif v_tax_type = UPPER(jai_constants.tax_type_turnover) then
508 v_turnover_ap := v_turnover_ap + v_tax_amt;
509 elsif v_tax_type= UPPER(jai_constants.tax_type_entry) then
510 v_entry_ap := v_entry_ap + v_tax_amt;
511 elsif v_tax_type= UPPER(jai_constants.tax_type_service) then
512 v_service_ap := v_service_ap + v_tax_amt;
513 --end
514 elsif v_tax_type = UPPER(jai_constants.tax_type_freight) then
515 v_freight_ap := v_freight_ap + v_tax_amt;
516 elsif v_tax_type= UPPER(jai_constants.tax_type_octroi) then
517 v_octroi_ap := v_octroi_ap + v_tax_amt;
518 else
519 v_others_ap := v_others_ap + v_tax_amt;
520 end if;
521
522 end loop; --c_get_tax_from_ap_rec
523
524 -- Get taxes from source doc PO / Receipt that are not brought over to AP
525
526 -- get the conversion factor considering UOM, Quantity and Price change
527 v_statement_id:= 11;
528 v_conversion_factor := jai_ap_utils_pkg.get_apportion_factor(c_inv_select_rec.invoice_id, c_item_lines_rec.invoice_line_number);
529
530 if nvl(v_conversion_factor, 0) = 0 then
531 v_conversion_factor := 1;
532 end if;
533
534
535 -- If invoice currency and tax currency are different then conversion is required.
536
537 if c_item_lines_rec.rcv_transaction_id is not null then
538
539 v_statement_id:= 12;
540 -- get from receipt.
541
542 for c_receipt_tax_rec in c_get_tax_from_receipt
543 (
544 c_inv_select_rec.invoice_id,
545 c_item_lines_rec.invoice_distribution_id,
546 c_item_lines_rec.po_distribution_id,
547 c_item_lines_rec.rcv_transaction_id
548 )
549 loop
550
551 v_statement_id:= 13;
552 v_tax_type := c_receipt_tax_rec.tax_type;
553 v_tax_amt := c_receipt_tax_rec.tax_amount;
554
555
556 v_tax_amt := v_tax_amt * v_conversion_factor;
557
558 v_statement_id:= 14;
559 if c_inv_select_rec.invoice_currency_code <> c_receipt_tax_rec.currency then
560 v_tax_amt := v_tax_amt / c_inv_select_rec.exchange_rate;
561 end if;
562
563
564 if v_tax_type in (UPPER(jai_constants.tax_type_exc_additional), UPPER(jai_constants.tax_type_excise) ,UPPER(jai_constants.tax_type_exc_other)) then
565 v_excise_po := v_excise_po + v_tax_amt;
566 elsif v_tax_type= UPPER(jai_constants. tax_type_cst) then
567 v_cst_po := v_cst_po + v_tax_amt;
568 elsif v_tax_type = UPPER(jai_constants.tax_type_sales) then
569 v_lst_po := v_lst_po + v_tax_amt;
570 elsif v_tax_type = UPPER(jai_constants.tax_type_customs) then
571 v_customs_po := v_customs_po + v_tax_amt;
572 elsif v_tax_type = UPPER(jai_constants.tax_type_cvd) then
573 v_cvd_po := v_cvd_po + v_tax_amt;
574 -- Date 01-NOV-2006 Bug 5228046 added by SACSETHI
575 -- START BUG 5228046
576 elsif v_tax_type = UPPER(jai_constants.tax_type_add_cvd) then
577 v_addcvd_po :=v_addcvd_po + v_tax_amt;
578 -- END BUG 5228046
579 -- Date 24-Nov-2006 Forward porting Bug 5671126 added by Balaji
580 --start
581 elsif v_tax_type = UPPER(jai_constants.tax_type_value_added) then
582 v_vat_po := v_vat_po + v_tax_amt;
583 elsif v_tax_type = UPPER(jai_constants.tax_type_purchase) then
584 v_pur_po := v_pur_po + v_tax_amt;
585 elsif v_tax_type = UPPER(jai_constants.tax_type_turnover) then
586 v_turnover_po := v_turnover_po + v_tax_amt;
587 elsif v_tax_type= UPPER(jai_constants.tax_type_entry) then
588 v_entry_po := v_entry_po + v_tax_amt;
589 elsif v_tax_type= UPPER(jai_constants.tax_type_service) then
590 v_service_po := v_service_po + v_tax_amt;
591 --end
592 elsif v_tax_type= UPPER(jai_constants.tax_type_freight) then
593 v_freight_po := v_freight_po + v_tax_amt;
594 elsif v_tax_type = UPPER(jai_constants.tax_type_octroi) then
595 v_octroi_po := v_octroi_po + v_tax_amt;
596 else
597 v_others_po := v_others_po + v_tax_amt;
598 end if;
599
600 v_statement_id:= 15;
601
602 end loop; -- c_receipt_tax_rec
603
604 else
605 -- get from po
606
607 for c_get_tax_from_po_rec in c_get_tax_from_po
608 (
609 c_inv_select_rec.invoice_id,
610 c_item_lines_rec.invoice_distribution_id,
614
611 c_item_lines_rec.po_distribution_id,
612 c_item_lines_rec.rcv_transaction_id
613 )
615 loop
616
617 v_statement_id:= 16;
618
619 v_tax_type := c_get_tax_from_po_rec.tax_type;
620 v_tax_amt := c_get_tax_from_po_rec.tax_amount;
621
622 v_tax_amt := v_tax_amt * v_conversion_factor;
623
624 v_statement_id:= 17;
625
626 if c_inv_select_rec.invoice_currency_code <> c_get_tax_from_po_rec.currency then
627 v_tax_amt := v_tax_amt / c_inv_select_rec.exchange_rate;
628 end if;
629
630 if v_tax_type in (UPPER(jai_constants.tax_type_exc_additional), UPPER(jai_constants.tax_type_excise) ,UPPER(jai_constants.tax_type_exc_other)) then
631 v_excise_po := v_excise_po + v_tax_amt;
632 elsif v_tax_type = UPPER(jai_constants. tax_type_cst) then
633 v_cst_po := v_cst_po + v_tax_amt;
634 elsif v_tax_type=UPPER(jai_constants.tax_type_sales) then
635 v_lst_po := v_lst_po + v_tax_amt;
636 elsif v_tax_type= UPPER(jai_constants.tax_type_customs) then
637 v_customs_po := v_customs_po + v_tax_amt;
638 elsif v_tax_type = UPPER(jai_constants.tax_type_cvd) then
639 v_cvd_po := v_cvd_po + v_tax_amt;
640 elsif v_tax_type = UPPER(jai_constants.tax_type_add_cvd) then
641 v_addcvd_po := v_addcvd_po + v_tax_amt ;
642 elsif v_tax_type = UPPER(jai_constants.tax_type_freight) then
643 v_freight_po := v_freight_po + v_tax_amt;
644 elsif v_tax_type= UPPER(jai_constants.tax_type_octroi) then
645 v_octroi_po := v_octroi_po + v_tax_amt;
646
647 -- Date 24-Nov-2006 Forward porting Bug 5671126 added by Balaji
648 --start
649
650 elsif v_tax_type = upper(jai_constants.tax_type_value_added) then
651 v_vat_po := v_vat_po + v_tax_amt;
652 elsif v_tax_type= upper(jai_constants.tax_type_purchase) then
653 v_pur_po := v_pur_po + v_tax_amt;
654 elsif v_tax_type = upper(jai_constants.tax_type_turnover) then
655 v_turnover_po := v_turnover_po + v_tax_amt;
656 elsif v_tax_type = upper(jai_constants.tax_type_entry) then
657 v_entry_po := v_entry_po + v_tax_amt;
658 elsif v_tax_type = upper(jai_constants.tax_type_service) then
659 v_service_po := v_service_po + v_tax_amt;
660 --end
661 else
662 v_others_po := v_others_po + v_tax_amt;
663 end if;
664
665 v_statement_id:= 18;
666
667 end loop; -- c_get_tax_from_po_rec
668
669 end if;
670
671 v_statement_id:= 19;
672 /* Modified the following insert statement to insert VAT amounts for bug#5096880, Ramesh.B.K, 23/03/2006 */
673 -- Date 24-Nov-2006 Forward porting Bug 5671126 added by Balaji
674 -- insert into the temp table with all the values.
675
676 insert into JAI_PO_REP_PRRG_T
677 (
678 run_no,
679 org_id,
680 vendor_id,
681 vendor_site_id,
682 invoice_id,
683 invoice_num,
684 invoice_date,
685 invoice_currency_code,
686 exchange_rate,
687 voucher_num,
688 distribution_line_number,
689 po_number,
690 po_header_id,
691 po_creation_date,
692 po_distribution_id,
693 po_release_num,
694 receipt_number,
695 receipt_date,
696 rcv_transaction_id,
697 line_amount,
698 excise,
699 customs,
700 cvd,
701 additional_cvd , -- Date 01/11/2006 Bug 5228046 added by SACSETHI
702 cst,
703 lst,
704 freight,
705 octroi,
706 -- Date 24-Nov-2006 Forward porting Bug 5671126 added by Balaji
707 --start
708 vat,
709 service_tax,
710 --end
711 others,
712 /* Bug 4866533. Added by Lakshmi gopalsami
713 Added WHO columns */
714 CREATED_BY,
715 CREATION_DATE,
716 LAST_UPDATED_BY,
717 LAST_UPDATE_DATE
718 )
719 values
720 (
721 v_run_no,
722 c_inv_select_rec.org_id ,
723 c_inv_select_rec.vendor_id,
724 c_inv_select_rec.vendor_site_id,
725 c_inv_select_rec.invoice_id,
726 c_inv_select_rec.invoice_num,
727 c_inv_select_rec.invoice_date,
728 c_inv_select_rec.invoice_currency_code,
729 c_inv_select_rec.exchange_rate,
730 c_inv_select_rec.voucher_num,
731 c_item_lines_rec.distribution_line_number,
732 v_po_number,
733 v_po_header_id,
734 nvl(v_po_release_date, v_po_date),
735 c_item_lines_rec.po_distribution_id,
736 nvl(v_po_release_num, 0),
737 v_receipt_num,
738 v_receipt_date,
739 c_item_lines_rec.rcv_transaction_id,
740 c_item_lines_rec.amount,
741 v_excise_ap + v_excise_po,
742 v_customs_ap + v_customs_po,
743 v_cvd_ap + v_cvd_po,
744 v_addcvd_ap + v_addcvd_po , -- Date 01/11/2006 Bug 5228046 added by SACSETHI
745 v_cst_ap + v_cst_po,
746 v_lst_ap + v_lst_po,
747 v_freight_ap + v_freight_po,
748 v_octroi_ap + v_octroi_po,
749 -- Date 24-Nov-2006 Forward porting Bug 5671126 added by Balaji
750 --start
751 (NVL(v_vat_ap,0) + NVL(v_vat_po,0) +
752 NVL(v_turnover_ap,0) +
753 NVL(v_turnover_po,0) +
754 NVL(v_pur_ap,0) +
755 NVL(v_pur_po,0) +
756 NVL(v_entry_ap,0) +
757 NVL(v_entry_po,0)),
758 v_service_ap + v_service_po,
759 --end
760 v_others_ap + v_others_po,
761 /* Bug 4866533. Added by Lakshmi Gopalsami
762 Added WHO columns
763 */
764 fnd_global.user_id,
765 sysdate,
766 fnd_global.user_id,
767 sysdate
768 );
769
770
771 v_statement_id:= 19;
772
773 end loop; -- c_item_lines_rec
774
775 v_statement_id:= 20;
776
777 end loop;-- c_inv_select_cursor
778
779 p_run_no := v_run_no;
780
781 EXCEPTION
782 when others then
783 p_error_message := 'Error from Proc process_report(Statement id):'
784 || '(' || v_statement_id || ')' || sqlerrm;
785
786 END process_report;
787 END jai_ap_rpt_prrg_pkg;