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