[Home] [Help]
PACKAGE BODY: APPS.JE_ITWHYE_AP_PKG
Source
1 PACKAGE BODY je_itwhye_ap_pkg AS
2 -- $Header: JEITWHYLB.pls 120.16.12020000.3 2012/09/21 13:29:39 rshergil ship $
3 -- ****************************************************************************************
4 -- Copyright (c) 2000 Oracle Solution Services (India) Product Development
5 -- All rights reserved
6 -- ****************************************************************************************
7 --
8 -- PROGRAM NAME
9 -- JEITWHYLB.pls
10 --
11 -- DESCRIPTION
12 -- This script creates the package body of je_itwhye_ap_pkg Package
13 -- This package is used to generate Italian Withholding Yearly extract Report
14 --
15 -- USAGE
16 -- To install How to Install
17 -- To execute How to Execute
18 --
19 -- DEPENDENCIES
20 -- None.
21 --
22 --
23 -- LAST UPDATE DATE 23-SEP-2009
24 -- Date the program has been modified for the last time
25 --
26 -- HISTORY
27 -- =======
28 --
29 -- VERSION DATE AUTHOR(S) DESCRIPTION
30 -- ------- ----------- --------------- ------------------------------------
31 -- 1.0 12-NOV-2008 SURESH SINGH M Creation
32 --1.1 28-JAN-2009 SURESH SINGH Removed all the functions that where being referred by the insert statement into the Global temp table.
33 --1.2 23-SEP-2009 SURESH SINGH Updated code for PREPAYMENT Invoices
34 --1.3 12-JAN-2012 JSAJJA Updated code to correct the year start date and year end date in beforereport function. as ber bug#13442170
35 --1.4 29-JAN-2012 KHANSEN added code that missed out (bug 13783211)
36 --1.5 15-MAR-2012 KHANSEN updated PROCEDURE je_withholding modified (bug 13829746)
37 -- 08-JUN-2012 FHOLST Added rep_registration_number, table and join for zx_party_tax_profile Bug 14136822
38 --****************************************************************************************
39 -----****************************************************************************************
40 -----------------------------------------------------BEFORE REPORT LOGIC---------------------------------------------------------------
41 ------****************************************************************************************
42 FUNCTION beforereport
43 RETURN BOOLEAN
44 IS
45 BEGIN
46 DECLARE
47 ln_coaid NUMBER;
48 lc_sobname VARCHAR2 (30);
49 lc_functcurr VARCHAR2 (15);
50 lc_errbuf VARCHAR2 (132);
51 ln_sob_id NUMBER;
52 BEGIN
53
54 -----------------------------------------------------------------------------------------------------------------------------------------------------
55 -----------Getting the Ledger Id Information from the Input Parameter of Legal Entity Id
56 -----------------------------------------------------------------------------------------------------------------------------------------------------
57 BEGIN
58 SELECT glev.ledger_id
59 --, glp.year_start_date, glp.end_date --Commented as per bug#13442170
60 ,glev.currency_code
61 INTO cp_set_of_books_id
62 -- ,cp_year_start_date --Commented as per bug#13442170
63 -- ,cp_year_end_date --Commented as per bug#13442170
64 ,cp_currencycode
65 FROM gl_ledger_le_v glev
66 -- , gl_periods glp --Commented as per bug#13442170
67 WHERE glev.legal_entity_id = p_legal_entity_id
68 -- AND glev.period_set_name = glp.period_set_name --Commented as per bug#13442170
69 -- AND glev.accounted_period_type = glp.period_type --Commented as per bug#13442170
70 -- AND glp.period_year = p_year --Commented as per bug#13442170
71 -- AND glp.adjustment_period_flag = 'Y' --Commented as per bug#13442170
72 AND glev.relationship_enabled_flag = 'Y'
73 AND glev.ledger_category_code = 'PRIMARY';
74
75 -- Updated for bug 13783211
76 cp_year_start_date := to_date(P_YEAR||'/01/01','YYYY/MM/DD');
77 cp_year_end_date := to_date(P_YEAR||'/12/31','YYYY/MM/DD');
78
79
80 EXCEPTION
81 WHEN NO_DATA_FOUND
82 THEN
83 fnd_file.put_line (fnd_file.LOG, SQLERRM);
84 END;
85
86 /*------------------------------------------------------------------------------------------------------------------------------------------
87 -----------Getting the Commercial Number for the Input Parameter of Legal Entity Id
88 ----------------------------------------------------------------------------------------------------------------------------------------------------- */
89 BEGIN
90 SELECT NVL (xler.registration_number, '') commercial_number
91 INTO cp_comm_num
92 FROM xle_registrations xler,
93 xle_jurisdictions_b xlej,
94 xle_entity_profiles xlee
95 WHERE xlej.jurisdiction_id = xler.jurisdiction_id
96 AND xlej.legislative_cat_code = 'COMMERCIAL_LAW'
97 AND xler.source_id = xlee.legal_entity_id
98 AND xler.source_table = 'XLE_ENTITY_PROFILES'
99 AND xlee.legal_entity_id = p_legal_entity_id;
100 EXCEPTION
101 WHEN NO_DATA_FOUND THEN
102 cp_comm_num:=NULL;
103 END;
104
105
106 /*------------------------------------------------------------------------------------------------------------------------------------------
107 -----------Getting the Ledger Functional Currency Information from the Input Parameter of Legal Entity Id
108 ----------------------------------------------------------------------------------------------------------------------------------------------------- */
109
110 GL_INFO.GL_GET_LEDGER_INFO (cp_set_of_books_id
111 ,ln_coaid
112 ,lc_sobname
113 ,lc_functcurr
114 ,lc_errbuf
115 );
116 IF (lc_errbuf IS NOT NULL)
117 THEN
118
119 NULL;
120
121 END IF;
122
123
124
125 -----------------------------------------------------------------------------------------------------------------------------------------------------
126 -----------Fetching the Precision from fnd_curriencies for the Corresponding Functional Currency Code
127 -----------------------------------------------------------------------------------------------------------------------------------------------------
128 BEGIN
129 SELECT PRECISION
130 INTO cp_precision
131 FROM fnd_currencies
132 WHERE currency_code = lc_functcurr;
133 EXCEPTION
134 WHEN NO_DATA_FOUND
135 THEN
136 fnd_file.put_line (fnd_file.LOG, SQLERRM);
137 END;
138
139
140 -----------------------------------------------------------------------------------------------------------------------------------------------------
141 -----------Assigning the IRPEF , INPS and Currency code to the Global Variables
142 -----------------------------------------------------------------------------------------------------------------------------------------------------
143
144 BEGIN
145 g_irpef:='IRPEF';
146 g_inps :='INPS';
147 -----------------------------------------------------------------------------------------------------------------------------------------------------
148 -----------Assigning the Functional Currency Code to the global Variable
149 -----------------------------------------------------------------------------------------------------------------------------------------------------
150 cp_currency_code := lc_functcurr;
151 END;
152
153 -----------------------------------------------------------------------------------------------------------------------------------------------------
154 -----------Passing Lexical Parameters for the Order By Clause
155 -----------------------------------------------------------------------------------------------------------------------------------------------------
156
157 IF(P_ORDER_BY ='S') THEN
158 lp_order_by :='aps.vendor_name';
159 ELSIF(P_ORDER_BY ='V') THEN
160 lp_order_by :='vat_registration_num'; --Bug 14136822
161 ELSIF(P_ORDER_BY ='T') THEN
162 lp_order_by :='taxpayer_id';
163 END IF;
164
165 -----------------------------------------------------------------------------------------------------------------------------------------------------
166 -----------Calling the Procedure to insert data into the Gloab Temp table jg_zz_vat_trx_gt
167 -----------------------------------------------------------------------------------------------------------------------------------------------------
168
169
170 JE_ITWHYE_AP_PKG.JE_WITHHOLDING(errbuf
171 ,errcode
172 ,p_legal_entity_id
173 ,cp_year_start_date
174 ,cp_year_end_date
175 ,lp_order_by
176 );
177
178 EXCEPTION
179 WHEN OTHERS
180 THEN
181 NULL;
182 END;
183
184
185
186
187 RETURN (TRUE);
188 END;
189
190 -----****************************************************************************************
191 -----------------------------------------------------AFTER REPORT---------------------------------------------------------------
192 ------****************************************************************************************
193
194 FUNCTION afterreport
195 RETURN BOOLEAN
196 IS
197 BEGIN
198
199 RETURN (TRUE);
200 END;
201
202 -----------------------------------------------------------------------------------------------------------------------------------------------------
203 -----------Converting the P_Year into Year Start Date
204 -----------------------------------------------------------------------------------------------------------------------------------------------------
205 FUNCTION cp_start_date
206 RETURN VARCHAR2
207 IS
208
209 BEGIN
210 RETURN cp_year_start_date;
211 END;
212 -----------------------------------------------------------------------------------------------------------------------------------------------------
213 -----------Converting the P_Year into Year End Date
214 -----------------------------------------------------------------------------------------------------------------------------------------------------
215 FUNCTION cp_end_date
216 RETURN VARCHAR2
217 IS
218 BEGIN
219
220 RETURN cp_year_end_date;
221
222 END;
223 -----------------------------------------------------------------------------------------------------------------------------------------------------
224 --Inserting the rows in the JG_ZZ_VAT_TRX_GT Global Table
225 -----------------------------------------------------------------------------------------------------------------------------------------------------
226
227 PROCEDURE je_withholding(errbuf OUT NOCOPY VARCHAR2
228 ,errcode OUT NOCOPY NUMBER
229 ,p_legal_entity_id NUMBER
230 ,cp_year_start_date VARCHAR2
231 ,cp_year_end_date VARCHAR2
232 ,p_order_by VARCHAR2
233 )
234 IS
235
236
237 CURSOR cur_withholding_extract(p_legal_entity_id NUMBER, cp_year_start_date VARCHAR2,cp_year_end_date VARCHAR2,p_order_by VARCHAR2)
238 IS
239 SELECT aps.vendor_name
240 , aps.vendor_id ,
241 NVL (papf.national_identifier,
242 NVL (aps.individual_1099, aps.num_1099)
243 ) taxpayer_id,
244 aps.segment1
245 , apss.vendor_site_code ,
246 apss.vendor_site_id ,
247 nvl(apss.vat_registration_num,zpt.rep_registration_number) vat_registration_num, --Bug 14136822
248 apss.address_line1 ,
249 apss.address_line2 ,
250 apss.address_line3 ,
251 apss.address_line1
252 || DECODE (apss.address_line2,
253 NULL, NULL,
254 ', ' || apss.address_line2
255 )
256 || DECODE (apss.address_line3,
257 NULL, NULL,
258 ', ' || apss.address_line3
259 ) supplier_address,
260 apss.city
261 , apss.zip ,
262 apss.province
263 , apss.country ,
264 inv.invoice_id ,
265 NVL (inv.base_amount, inv.invoice_amount) invoice_amount,
266 NVL (aip.payment_base_amount, aip.amount) amount_paid,
267 inv.invoice_num
268 ,dist.exempt_amount
269 ,inv.invoice_type_lookup_code
270 FROM ap_supplier_sites_all apss,
271 ap_suppliers aps,
272 (SELECT DISTINCT person_id, national_identifier
273 FROM per_all_people_f) papf
274 ,(SELECT SUM (DECODE (dist1.line_type_lookup_code,
275 'ITEM', NVL (SIGN (dist1.awt_group_id) - 1, 1)
276 * NVL (dist1.base_amount, dist1.amount),
277 'PREPAY', NVL (SIGN (dist1.awt_group_id) - 1, 1)
278 * NVL (dist1.base_amount, dist1.amount),
279 0
280 )
281 ) exempt_amount
282 ,dist1.invoice_id
283 FROM ap_invoice_distributions_all dist1,ap_invoices_all inv1
284 WHERE dist1.invoice_id = inv1.invoice_id
285 AND dist1.line_type_lookup_code = 'ITEM'
286 -- AND dist1.awt_group_id IS NULL
287 group by dist1.invoice_id)dist
288 ,ap_invoices_all inv,
289 ap_invoice_payments_all aip,
290 ap_checks_all checks,
291 zx_party_tax_profile zpt --Bug 14136822
292 WHERE inv.legal_entity_id = p_legal_entity_id
293 AND inv.invoice_id = dist.invoice_id
294 AND inv.invoice_id = aip.invoice_id
295 AND ( aip.posted_flag IN ('Y', 'P')
296 OR aip.cash_posted_flag IN ('Y', 'P')
297 OR aip.accrual_posted_flag IN ('Y', 'P')
298 )
299 AND inv.vendor_id = aps.vendor_id
300 AND inv.vendor_site_id = apss.vendor_site_id
301 AND aps.vendor_id = apss.vendor_id
302 AND apss.party_site_id = zpt.party_id --Bug 14136822
303 AND zpt.party_type_code = 'THIRD_PARTY_SITE' --Bug 14136822
304 AND NVL (aps.employee_id, -99) = papf.person_id(+)
305 AND aip.check_id = checks.check_id
306 AND checks.void_date IS NULL
307 AND aip.accounting_date BETWEEN cp_year_start_date AND cp_year_end_date
308 AND aip.invoice_payment_id =
309 (SELECT MAX (aip_sub.invoice_payment_id)
310 FROM ap_invoice_payments_all aip_sub
311 WHERE aip_sub.invoice_id = inv.invoice_id
312 AND aip_sub.accounting_date BETWEEN cp_year_start_date
313 AND cp_year_end_date
314 GROUP BY aip_sub.invoice_id)
315 AND EXISTS (
316 SELECT 1
317 FROM ap_invoice_payments_all aip_sub2,
318 ap_invoice_distributions_all dist
319 WHERE aip_sub2.invoice_payment_id =
320 DECODE (dist.line_type_lookup_code,
321 'AWT', dist.awt_invoice_payment_id,
322 aip_sub2.invoice_payment_id
323 )
324 AND aip_sub2.accounting_date BETWEEN cp_year_start_date
325 AND cp_year_end_date
326 )
327 GROUP BY aps.vendor_name,
328 aps.vendor_id,
329 NVL (papf.national_identifier,
330 NVL (aps.individual_1099, aps.num_1099)
331 ),
332 aps.segment1,
333 apss.vendor_site_code,
334 apss.vendor_site_id,
335 nvl(apss.vat_registration_num,zpt.rep_registration_number), --Bug 14136822
336 apss.address_line1,
337 apss.address_line2,
338 apss.address_line3,
339 apss.address_line1
340 || DECODE (apss.address_line2,
341 NULL, NULL,
342 ', ' || apss.address_line2
343 )
344 || DECODE (apss.address_line3,
345 NULL, NULL,
346 ', ' || apss.address_line3
347 ),
348 apss.city,
349 apss.zip,
350 apss.province,
351 apss.country,
352 inv.invoice_id,
353 NVL (inv.base_amount, inv.invoice_amount),
354 NVL (aip.payment_base_amount, aip.amount),
355 inv.invoice_num
356 ,dist.exempt_amount
357 ,inv.invoice_type_lookup_code
358 ORDER BY case p_order_by --- bug 14189687
359 when 'aps.vendor_name' then aps.vendor_name
360 when 'vat_registration_num' then vat_registration_num
361 when 'taxpayer_id' then taxpayer_id
362 end ;
363
364 CURSOR cur_withholding_invoice(p_invoice_id NUMBER)
365 IS
366 SELECT count(1)rec_count
367 FROM ap_awt_group_taxes_all awt
368 WHERE awt.GROUP_ID IN (
369 SELECT DISTINCT dist.awt_origin_group_id
370 FROM ap_invoice_distributions_all dist,
371 ap_invoices_all inv
372 WHERE dist.invoice_id = inv.invoice_id
373 AND dist.line_type_lookup_code ='AWT'
374 AND inv.invoice_id = p_invoice_id
375 );
376
377 ---Declaring Variable
378 ln_inps_wthamount NUMBER;
379 ln_irpef_wthamount NUMBER;
380 ln_inps_rate NUMBER;
381 ln_irpef_rate NUMBER;
382 lc_inps_taxname VARCHAR2(100);
383 lc_irpef_taxname VARCHAR2(100);
384 ln_taxable_amount NUMBER;
385 lc_inps_vendor_name VARCHAR2(100);
386 lc_irpef_vendor_name VARCHAR2(100);
387 ln_tax_amount_one NUMBER;
388 ln_tax_amount_two NUMBER;
389 ln_tax_amount NUMBER;
390 ln_ex_amount1 NUMBER;
391 ln_ex_amount2 NUMBER;
392 ln_ex_amount NUMBER;
393 --bug14369763 - start
394 ln_net_amount NUMBER;
395 ln_gross_amount NUMBER;
396 ln_ratio_paid NUMBER;
397 l_inv_gctot_amt number;
398 l_inv_whtot_amt number;
399 l_inv_gctot_base number;
400 l_inv_gctot_etrd number;
401 l_inv_whtot_base number;
402 l_inv_whtot_etrd number;
403 l_pay_amount number;
404 l_amount_etrd number;
405 l_amount_paid number;
406 ln_awt_group_id number;
407 --bug14369763 - end
408 ln_inv_num VARCHAR2(100);
409
410
411
412 --Declaring Cursors
413 cur_withholding_extract_rec cur_withholding_extract%ROWTYPE;
414 cur_withholding_invoice_rec cur_withholding_invoice%ROWTYPE;
415
416
417 BEGIN
418
419 fnd_file.put_line(fnd_file.log,'cp_year_start_date:'||cp_year_start_date);
420 fnd_file.put_line(fnd_file.log,'cp_year_end_date:'||cp_year_end_date);
421 fnd_file.put_line(fnd_file.log,'p_legal_entity_id:'||p_legal_entity_id);
422 fnd_file.put_line(fnd_file.log,'lp_order_by:'||lp_order_by);
423
424
425 OPEN cur_withholding_extract(p_legal_entity_id,cp_year_start_date,cp_year_end_date,lp_order_by);
426 LOOP
427 FETCH cur_withholding_extract INTO cur_withholding_extract_rec;
428 EXIT when cur_withholding_extract%NOTFOUND;
429
430 OPEN cur_withholding_invoice(cur_withholding_extract_rec.invoice_id);
431 LOOP
432 FETCH cur_withholding_invoice INTO cur_withholding_invoice_rec;
433 EXIT when cur_withholding_invoice%NOTFOUND;
434
435
436
437 -- bug 13829746
438 -- IF(cur_withholding_invoice_rec.rec_count > 1) THEN
439
440 IF(cur_withholding_invoice_rec.rec_count >= 1) THEN
441
442 fnd_file.put_line(fnd_file.log,'Starting Invoice Id..'||cur_withholding_extract_rec.invoice_id );
443
444 BEGIN
445
446 /* bug14369763
447 SELECT SUM (NVL (dist.base_amount, dist.amount))
448 INTO ln_ex_amount1
449 FROM ap_invoice_distributions_all dist
450 WHERE dist.invoice_id = cur_withholding_extract_rec.invoice_id
451 AND dist.line_type_lookup_code in ('ITEM','ACCRUAL')
452 AND dist.pay_awt_group_id IS NULL;
453
454 */
455
456 SELECT SUM (NVL (dist.base_amount, dist.amount))
457 INTO ln_ex_amount2
458 FROM ap_invoice_distributions_all dist
459 WHERE dist.invoice_id = cur_withholding_extract_rec.invoice_id
460 AND dist.line_type_lookup_code in ('ITEM', 'ACCRUAL') --bug14369763
461 AND nvl(dist.pay_awt_group_id, nvl(dist.awt_group_id,dist.awt_origin_group_id)) IN ( --bug14369763
462 SELECT awgt_sub.GROUP_ID
463 FROM ap_tax_codes_all atc_sub,
464 ap_awt_tax_rates_all awt_sub,
465 ap_awt_group_taxes_all awgt_sub
466 WHERE awgt_sub.tax_name = awt_sub.tax_name
467 AND atc_sub.NAME = awt_sub.tax_name
468 AND awt_sub.tax_rate = 0);
469
470
471 --bug14369763 - start
472 --ln_ex_amount := nvl(ln_ex_amount1,0) + nvl(ln_ex_amount2,0);
473 ln_ex_amount := nvl(ln_ex_amount2,0);
474 --bug14369763 - end
475
476 fnd_file.put_line(fnd_file.log,'ln_ex_amount:'||ln_ex_amount);
477
478 EXCEPTION
479 WHEN NO_DATA_FOUND
480 THEN
481 ln_ex_amount := 0;
482 fnd_file.put_line(fnd_file.log,'no data found for ln_ex_amount'||ln_ex_amount);
483
484 END;
485
486 BEGIN
487
488 /* bug14369763
489 SELECT SUM (NVL (dist.base_amount, dist.amount))
490 INTO ln_tax_amount_one
491 FROM ap_invoice_distributions_all dist
492 WHERE dist.invoice_id = cur_withholding_extract_rec.invoice_id
493 AND dist.line_type_lookup_code = 'ITEM'
494 AND dist.awt_group_id IS NULL;
495
496
497
498 */
499
500 SELECT SUM (NVL (dist.base_amount, dist.amount))
501 INTO ln_tax_amount_two
502 FROM ap_invoice_distributions_all dist
503 , ap_awt_group_taxes_all awt
504 WHERE dist.invoice_id = invoice_id
505 AND NVL(dist.pay_awt_group_id,nvl(dist.awt_group_id, dist.awt_origin_group_id)) = awt.group_id --bug14369763
506 AND dist.org_id = awt.org_id
507 AND dist.invoice_id = cur_withholding_extract_rec.invoice_id
508 AND dist.line_type_lookup_code in ('ITEM','ACCRUAL') --bug14369763
509 AND NVL(dist.pay_awt_group_id, nvl(dist.awt_group_id, dist.awt_origin_group_id)) NOT IN ( --bug14369763
510 SELECT awgt_sub.GROUP_ID
511 FROM ap_tax_codes_all atc_sub,
512 ap_awt_tax_rates_all awt_sub,
513 ap_awt_group_taxes_all awgt_sub
514 WHERE awgt_sub.tax_name = awt_sub.tax_name
515 AND atc_sub.NAME = awt_sub.tax_name
516 AND awt_sub.tax_rate = 0);
517
518
519 --bug14369763 - start
520 --ln_tax_amount := NVL (ln_tax_amount_one, 0) + NVL (ln_tax_amount_two, 0);
521 ln_tax_amount := NVL (ln_tax_amount_two, 0);
522 --bug14369763 - end
523
524 fnd_file.put_line(fnd_file.log,'ln_tax_amount:'||ln_tax_amount);
525
526 EXCEPTION
527 WHEN NO_DATA_FOUND
528 THEN
529 ln_tax_amount := 0;
530 fnd_file.put_line(fnd_file.log,'no data found for ln_tax_amount'||ln_tax_amount);
531
532 END;
533
534
535
536 --to get amount paid --bug14369763
537 BEGIN
538
539
540 select sum(amount),
541 sum(nvl(payment_base_amount, amount))
542 into l_amount_etrd,
543 l_amount_paid
544 from ap_invoice_payments
545 where invoice_id = cur_withholding_extract_rec.invoice_id
546 and accounting_date between cp_year_start_date AND cp_year_end_date
547 group by invoice_id;
548
549 fnd_file.put_line(fnd_file.log,'l_amount_etrd:'||l_amount_etrd);
550 fnd_file.put_line(fnd_file.log,'l_amount_paid:'||l_amount_paid);
551
552 EXCEPTION
553 WHEN NO_DATA_FOUND
554 THEN
555 null;
556 fnd_file.put_line(fnd_file.log,'exception and no data found for l_amount_etrd:'||l_amount_etrd);
557 fnd_file.put_line(fnd_file.log,'exception and no data found for l_amount_paid:'||l_amount_paid);
558 END;
559
560 -- to get ratio paid --bug14369763
561 BEGIN
562
563
564 select sum(decode(dist.line_type_lookup_code,'AWT',0,nvl(dist.base_amount,dist.amount))),
565 sum(decode(dist.line_type_lookup_code,'AWT',0,dist.amount)),
566 sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)),
567 sum(decode(dist.line_type_lookup_code,'AWT',dist.amount,0))
568 into l_inv_gctot_base,
569 l_inv_gctot_etrd,
570 l_inv_whtot_base,
571 l_inv_whtot_etrd
572 from ap_invoice_distributions dist
573 where dist.invoice_id = cur_withholding_extract_rec.invoice_id
574 and exists (select 1
575 from ap_invoice_payments pay
576 where pay.invoice_payment_id = decode(dist.line_type_lookup_code,'AWT',
577 dist.awt_invoice_payment_id,pay.invoice_payment_id)
578 and pay.accounting_date between cp_year_start_date AND cp_year_end_date);
579
580 fnd_file.put_line(fnd_file.log,'l_inv_gctot_base:'||l_inv_gctot_base);
581 fnd_file.put_line(fnd_file.log,'l_inv_gctot_etrd:'||l_inv_gctot_etrd);
582 fnd_file.put_line(fnd_file.log,'l_inv_whtot_base:'||l_inv_whtot_base);
583 fnd_file.put_line(fnd_file.log,'l_inv_whtot_etrd:'||l_inv_whtot_etrd);
584
585
586 select decode(inv.invoice_currency_code,inv.payment_currency_code,l_inv_gctot_etrd,l_inv_gctot_base),
587 decode(inv.invoice_currency_code,inv.payment_currency_code,l_inv_whtot_etrd,l_inv_whtot_base),
588 decode(inv.invoice_currency_code,inv.payment_currency_code, l_amount_etrd, l_amount_paid)
589 into l_inv_gctot_amt,
590 l_inv_whtot_amt,
591 l_pay_amount
592 from ap_invoices inv
593 where inv.invoice_id = cur_withholding_extract_rec.invoice_id;
594
595 fnd_file.put_line(fnd_file.log,'l_inv_gctot_amt:'||l_inv_gctot_amt);
596 fnd_file.put_line(fnd_file.log,'l_inv_whtot_amt:'||l_inv_whtot_amt);
597 fnd_file.put_line(fnd_file.log,'l_pay_amount:'||l_pay_amount);
598
599
600 if nvl(l_inv_gctot_amt,0) <> 0 then
601 ln_ratio_paid := round((l_pay_amount + (-1) * l_inv_whtot_amt)/l_inv_gctot_amt,10);
602 end if;
603
604 fnd_file.put_line(fnd_file.log,'ln_ratio_paid:'||ln_ratio_paid);
605
606 EXCEPTION
607 WHEN NO_DATA_FOUND
608 THEN
609 ln_ratio_paid := 1;
610 fnd_file.put_line(fnd_file.log,'exception and no data found for ratio paid'||ln_ratio_paid);
611
612 END;
613
614
615 -- to get group_id --bug14369763
616
617 BEGIN
618 SELECT awt.group_id
619 into ln_awt_group_id
620 FROM ap_awt_group_taxes_all awt
621 WHERE awt.GROUP_ID IN (
622 SELECT DISTINCT dist.awt_origin_group_id
623 FROM ap_invoice_distributions_all dist,
624 ap_invoices_all inv
625 WHERE dist.invoice_id = inv.invoice_id
626 AND dist.line_type_lookup_code ='AWT'
627 AND inv.invoice_id = cur_withholding_extract_rec.invoice_id
628 );
629
630 fnd_file.put_line(fnd_file.log,'ln_awt_group_id:'||ln_awt_group_id);
631
632 EXCEPTION
633 WHEN OTHERS THEN
634 null;
635 fnd_file.put_line(fnd_file.log,'EXCEPTION in getting group_id in ln_awt_group_id:'||ln_awt_group_id);
636 END;
637
638 -- to get gross amount --bug14369763
639
640 BEGIN
641
642 select sum(nvl(amount,0))
643 into ln_gross_amount
644 from
645 ( select sum(nvl(dist.base_amount,nvl(dist.amount,0))) amount
646 from ap_invoice_distributions dist
647 where dist.invoice_id = cur_withholding_extract_rec.invoice_id
648 and dist.line_type_lookup_code <> 'AWT'
649 and nvl(dist.pay_awt_group_id,nvl(dist.awt_group_id,dist.awt_origin_group_id)) = ln_awt_group_id
650
651 UNION
652
653 select sum(nvl(c.base_amount,nvl(c.amount,0))) amount
654 from ap_invoice_distributions dist,
655 ap_invoice_distributions c
656 where dist.invoice_id = cur_withholding_extract_rec.invoice_id
657 and c.invoice_id = cur_withholding_extract_rec.invoice_id
658 and dist.line_type_lookup_code <> 'AWT'
659 and c.CHARGE_APPLICABLE_TO_DIST_ID = dist.invoice_distribution_id
660 and nvl(dist.pay_awt_group_id,nvl(dist.awt_group_id,dist.awt_origin_group_id)) = ln_awt_group_id
661
662 ) chrg;
663
664
665 ln_gross_amount := round(ln_ratio_paid * ln_gross_amount, cp_precision);
666
667 fnd_file.put_line(fnd_file.log,'ln_gross_amount:'||ln_gross_amount);
668
669 EXCEPTION
670 WHEN NO_DATA_FOUND
671 THEN
672 ln_gross_amount := 0;
673 fnd_file.put_line(fnd_file.log,'exception and no data found for gross amount'||ln_gross_amount);
674 END;
675
676
677
678
679
680 BEGIN
681
682 fnd_file.put_line(fnd_file.log,'g_irpef:'||g_irpef);
683
684 SELECT awt.tax_rate, awt.tax_name,
685 ROUND (SUM (NVL (dist.base_amount, dist.amount)) * (-1),
686 cp_precision
687 ) wth_amount,
688 aps.vendor_name
689 INTO ln_irpef_rate, lc_irpef_taxname,
690 ln_irpef_wthamount,
691 lc_irpef_vendor_name
692 FROM ap_invoice_distributions dist,
693 ap_tax_codes atc,
694 ap_invoices inv1,
695 ap_awt_tax_rates awt,
696 ap_suppliers aps
697 WHERE dist.invoice_id = inv1.invoice_id
698 AND inv1.invoice_id = cur_withholding_extract_rec.invoice_id
699 AND dist.line_type_lookup_code = atc.tax_type
700 AND dist.line_type_lookup_code = 'AWT'
701 AND atc.NAME = awt.tax_name
702 AND awt.tax_rate_id = dist.awt_tax_rate_id
703 AND awt.vendor_id IS NULL
704 AND atc.awt_vendor_id = aps.vendor_id
705 AND aps.vendor_name = g_irpef
706 AND aps.vendor_type_lookup_code = 'TAX AUTHORITY'
707 GROUP BY awt.tax_rate, awt.tax_name, aps.vendor_name;
708
709 fnd_file.put_line(fnd_file.log,'ln_irpef_rate:'||ln_irpef_rate);
710 fnd_file.put_line(fnd_file.log,'lc_irpef_taxname:'||lc_irpef_taxname);
711 fnd_file.put_line(fnd_file.log,'ln_irpef_wthamount:'||ln_irpef_wthamount);
712 fnd_file.put_line(fnd_file.log,'lc_irpef_vendor_name:'||lc_irpef_vendor_name);
713
714 EXCEPTION
715 WHEN NO_DATA_FOUND
716 THEN
717 ln_irpef_rate := 0;
718 lc_irpef_taxname := NULL;
719 ln_irpef_wthamount := 0;
720 lc_irpef_vendor_name := NULL;
721
722 fnd_file.put_line(fnd_file.log,'no data found');
723 fnd_file.put_line(fnd_file.log,'ln_irpef_rate2:'||ln_irpef_rate);
724 fnd_file.put_line(fnd_file.log,'lc_irpef_taxname2:'||lc_irpef_taxname);
725 fnd_file.put_line(fnd_file.log,'ln_irpef_wthamount2:'||ln_irpef_wthamount);
726 fnd_file.put_line(fnd_file.log,'lc_irpef_vendor_name2:'||lc_irpef_vendor_name);
727
728
729 END;
730
731 BEGIN
732
733 fnd_file.put_line(fnd_file.log,'g_inps:'||g_inps);
734
735
736 SELECT awt.tax_rate, awt.tax_name,
737 ROUND (SUM (NVL (dist.base_amount, dist.amount)) * (-1),
738 cp_precision
739 ) wth_amount,
740 aps.vendor_name
741 INTO ln_inps_rate, lc_inps_taxname,
742 ln_inps_wthamount,
743 lc_inps_vendor_name
744 FROM ap_invoice_distributions dist,
745 ap_tax_codes atc,
746 ap_invoices inv1,
747 ap_awt_tax_rates awt,
748 ap_suppliers aps
749 WHERE dist.invoice_id = inv1.invoice_id
750 AND inv1.invoice_id = cur_withholding_extract_rec.invoice_id
751 AND dist.line_type_lookup_code = atc.tax_type
752 AND dist.line_type_lookup_code = 'AWT'
753 AND atc.NAME = awt.tax_name
754 AND awt.tax_rate_id = dist.awt_tax_rate_id
755 AND awt.vendor_id IS NULL
756 AND atc.awt_vendor_id = aps.vendor_id
757 AND aps.vendor_name = g_inps
758 AND aps.vendor_type_lookup_code = 'TAX AUTHORITY'
759 GROUP BY awt.tax_rate, awt.tax_name, aps.vendor_name;
760
761 fnd_file.put_line(fnd_file.log,'ln_inps_rate:'||ln_inps_rate);
762 fnd_file.put_line(fnd_file.log,'lc_inps_taxname:'||lc_inps_taxname);
763 fnd_file.put_line(fnd_file.log,'ln_inps_wthamount:'||ln_inps_wthamount);
764 fnd_file.put_line(fnd_file.log,'lc_inps_vendor_name:'||lc_inps_vendor_name);
765
766 EXCEPTION
767 WHEN NO_DATA_FOUND
768 THEN
769 ln_inps_rate := 0;
770 lc_inps_taxname := NULL;
771 ln_inps_wthamount := 0;
772 lc_inps_vendor_name := NULL;
773
774 fnd_file.put_line(fnd_file.log,'no data found');
775 fnd_file.put_line(fnd_file.log,'ln_inps_rate2:'||ln_inps_rate);
776 fnd_file.put_line(fnd_file.log,'lc_inps_taxname2:'||lc_inps_taxname);
777 fnd_file.put_line(fnd_file.log,'ln_inps_wthamount2:'||ln_inps_wthamount);
778 fnd_file.put_line(fnd_file.log,'lc_inps_vendor_name2:'||lc_inps_vendor_name);
779
780 END;
781
782
783 -- to get net amount -- bug14369763
784 BEGIN
785 ln_net_amount := round(ln_gross_amount - ln_irpef_wthamount - ln_inps_wthamount, cp_precision);
786
787 fnd_file.put_line(fnd_file.log,'ln_net_amount:'||ln_net_amount);
788
789 EXCEPTION
790 WHEN NO_DATA_FOUND
791 THEN
792 ln_net_amount := 0;
793 fnd_file.put_line(fnd_file.log,'EXCEPTION in getting ln_net_amount:'||ln_net_amount);
794 END;
795
796 ----Prepayment
797 fnd_file.put_line(fnd_file.log,'Invoice Type Lookup Code...'||cur_withholding_extract_rec.invoice_type_lookup_code );
798
799 IF(cur_withholding_extract_rec.invoice_type_lookup_code = 'PREPAYMENT' ) THEN
800
801 BEGIN
802 SELECT DISTINCT apinv_sub.invoice_num
803 INTO ln_inv_num
804 FROM ap_invoices_all apinv_sub,
805 ap_invoice_distributions_all apdist1_sub,
806 ap_invoice_distributions_all apdist2_sub
807 WHERE apdist2_sub.invoice_id = cur_withholding_extract_rec.invoice_id
808 AND apdist2_sub.invoice_distribution_id =
809 apdist1_sub.prepay_distribution_id
810 AND apdist1_sub.invoice_id = apinv_sub.invoice_id
811 AND apdist1_sub.reversal_flag <> 'Y';
812 EXCEPTION
813 WHEN NO_DATA_FOUND THEN
814 fnd_file.put_line(fnd_file.log,'No Data Found.'||SQLERRM);
815 ln_inv_num:=cur_withholding_extract_rec.invoice_num;
816
817 WHEN TOO_MANY_ROWS THEN
818 fnd_file.put_line(fnd_file.log,'More Than One Invoice Record Found.'||SQLERRM);
819 ln_inv_num:= cur_withholding_extract_rec.invoice_num;
820 END;
821 ELSE
822 fnd_file.put_line(fnd_file.log,'Not a prepayment Invoice Type Lookup Code...'||cur_withholding_extract_rec.invoice_type_lookup_code );
823 ln_inv_num:= cur_withholding_extract_rec.invoice_num;
824 fnd_file.put_line(fnd_file.log,'ln_inv_num:'||ln_inv_num);
825 END IF;
826
827 --Checking for the Taxable amount in accordance with the Exempt Amount
828 --If Exempt Amount exits then Taxable amount should be 0
829
830 --bug14369763
831 -- IF(ln_ex_amount > 0 ) THEN
832 -- ln_tax_amount := 0;
833 --fnd_file.put_line(fnd_file.log,'I am in the > then condition so ln_tax_amount is now zero');
834 -- END IF;
835
836
837
838 fnd_file.put_line(fnd_file.log,'cur_withholding_extract_rec.vendor_name'||cur_withholding_extract_rec.vendor_name);
839 fnd_file.put_line(fnd_file.log,'cur_withholding_extract_rec.vendor_id'||cur_withholding_extract_rec.vendor_id);
840 fnd_file.put_line(fnd_file.log,'cur_withholding_extract_rec.taxpayer_id'||cur_withholding_extract_rec.taxpayer_id);
841 fnd_file.put_line(fnd_file.log,'cur_withholding_extract_rec.segment1'||cur_withholding_extract_rec.segment1);
842 fnd_file.put_line(fnd_file.log,'cur_withholding_extract_rec.vendor_site_code'||cur_withholding_extract_rec.vendor_site_code);
843 fnd_file.put_line(fnd_file.log,'cur_withholding_extract_rec.vendor_site_id'||cur_withholding_extract_rec.vendor_site_id);
844 fnd_file.put_line(fnd_file.log,'cur_withholding_extract_rec.vat_registration_num'||cur_withholding_extract_rec.vat_registration_num);
845 fnd_file.put_line(fnd_file.log,'cur_withholding_extract_rec.address_line1'||cur_withholding_extract_rec.address_line1);
846 fnd_file.put_line(fnd_file.log,'cur_withholding_extract_rec.address_line2'||cur_withholding_extract_rec.address_line2);
847 fnd_file.put_line(fnd_file.log,'cur_withholding_extract_rec.address_line3'||cur_withholding_extract_rec.address_line3);
848 fnd_file.put_line(fnd_file.log,'cur_withholding_extract_rec.supplier_address'||cur_withholding_extract_rec.supplier_address);
849 fnd_file.put_line(fnd_file.log,'cur_withholding_extract_rec.city'||cur_withholding_extract_rec.city);
850 fnd_file.put_line(fnd_file.log,'cur_withholding_extract_rec.zip'||cur_withholding_extract_rec.zip);
851 fnd_file.put_line(fnd_file.log,'cur_withholding_extract_rec.province'||cur_withholding_extract_rec.province);
852 fnd_file.put_line(fnd_file.log,'cur_withholding_extract_rec.country'||cur_withholding_extract_rec.country);
853 fnd_file.put_line(fnd_file.log,'cur_withholding_extract_rec.invoice_id'||cur_withholding_extract_rec.invoice_id);
854 fnd_file.put_line(fnd_file.log,'cur_withholding_extract_rec.invoice_amount'||cur_withholding_extract_rec.invoice_amount);
855 fnd_file.put_line(fnd_file.log,'cur_withholding_extract_rec.amount_paid'||cur_withholding_extract_rec.amount_paid);
856 fnd_file.put_line(fnd_file.log,'ln_inv_num'||ln_inv_num);
857 fnd_file.put_line(fnd_file.log,'ln_inps_wthamount'||ln_inps_wthamount);
858 fnd_file.put_line(fnd_file.log,'ln_irpef_wthamount'||ln_irpef_wthamount);
859 fnd_file.put_line(fnd_file.log,'cur_withholding_extract_rec.amount_paid'||cur_withholding_extract_rec.amount_paid);
860 fnd_file.put_line(fnd_file.log,'cur_withholding_extract_rec.exempt_amount'||cur_withholding_extract_rec.exempt_amount);
861 fnd_file.put_line(fnd_file.log,'ln_ex_amount'||ln_ex_amount);
862 fnd_file.put_line(fnd_file.log,'ln_tax_amount'||ln_tax_amount);
863 fnd_file.put_line(fnd_file.log,'p_legal_entity_id'||p_legal_entity_id);
864 fnd_file.put_line(fnd_file.log,'lc_inps_vendor_name'||lc_inps_vendor_name);
865 fnd_file.put_line(fnd_file.log,'lc_irpef_vendor_name'||lc_irpef_vendor_name);
866
867
868
869 BEGIN
870 INSERT INTO JG_ZZ_VAT_TRX_GT
871 (jg_info_v1, jg_info_n1, jg_info_v2, jg_info_v3, jg_info_v4,
872 jg_info_n2, jg_info_v5, jg_info_v6, jg_info_v7, jg_info_v8,
873 jg_info_v9, jg_info_v10, jg_info_v11, jg_info_v12, jg_info_v13,
874 jg_info_n3, jg_info_n4, jg_info_n5, jg_info_v14, jg_info_n6,
875 jg_info_n7, jg_info_n8, jg_info_n9, jg_info_v15, jg_info_v16,
876 jg_info_n10, jg_info_n11, jg_info_n12, jg_info_n13, jg_info_n14
877 ,jg_info_n15,jg_info_v17,jg_info_v18)
878 VALUES(
879 cur_withholding_extract_rec.vendor_name
880 ,cur_withholding_extract_rec.vendor_id
881 ,cur_withholding_extract_rec.taxpayer_id
882 ,cur_withholding_extract_rec.segment1
883 ,cur_withholding_extract_rec.vendor_site_code
884 ,cur_withholding_extract_rec.vendor_site_id
885 ,cur_withholding_extract_rec.vat_registration_num
886 ,cur_withholding_extract_rec.address_line1
887 ,cur_withholding_extract_rec.address_line2
888 ,cur_withholding_extract_rec.address_line3
889 ,cur_withholding_extract_rec.supplier_address
890 ,cur_withholding_extract_rec.city
891 ,cur_withholding_extract_rec.zip
892 ,cur_withholding_extract_rec.province
893 ,cur_withholding_extract_rec.country
894 ,cur_withholding_extract_rec.invoice_id
895 ,cur_withholding_extract_rec.invoice_amount
896 ,cur_withholding_extract_rec.amount_paid
897 ,ln_inv_num
898 ,ln_inps_wthamount
899 ,ln_irpef_wthamount
900 ,ln_inps_rate
901 ,ln_irpef_rate
902 ,lc_inps_taxname
903 ,lc_irpef_taxname
904 ,ln_net_amount
905 ,ln_ratio_paid
906 ,ln_gross_amount
907 -- ,cur_withholding_extract_rec.amount_paid
908 -- ,ROUND( (cur_withholding_extract_rec.amount_paid +ln_inps_wthamount+ln_irpef_wthamount )
909 -- /DECODE(ln_ex_amount,0,1,decode(cur_withholding_extract_rec.exempt_amount,0,1,cur_withholding_extract_rec.exempt_amount)),10)
910 -- ,ROUND (ROUND( (cur_withholding_extract_rec.amount_paid +ln_inps_wthamount+ln_irpef_wthamount )
911 -- /DECODE(ln_ex_amount,0,1,decode(cur_withholding_extract_rec.exempt_amount,0,1,cur_withholding_extract_rec.exempt_amount)),10)
912 --*DECODE(ln_ex_amount,0,1,decode(cur_withholding_extract_rec.exempt_amount,0,1,cur_withholding_extract_rec.exempt_amount)), cp_precision)
913 ,ln_ex_amount
914 ,ln_tax_amount
915 ,p_legal_entity_id
916 ,lc_inps_vendor_name
917 ,lc_irpef_vendor_name
918 );
919
920 EXCEPTION
921 WHEN OTHERS THEN
922 fnd_file.put_line(fnd_file.log,'Unable to insert Record.'||SQLERRM);
923 END;
924 END IF;
925
926 END LOOP;
927 CLOSE cur_withholding_invoice;
928
929 END LOOP;
930 CLOSE cur_withholding_extract;
931
932 EXCEPTION
933 WHEN OTHERS THEN
934 ROLLBACK;
935
936 END je_withholding;
937
938
939 -----------------------------------------------------------------------------------------------------------------------------------------------------
940 ------------Functions to refer Oracle report placeholders------------------------------------------------------------------------------
941 -----------------------------------------------------------------------------------------------------------------------------------------------------
942 --Precision
943 FUNCTION cp_precision_p
944 RETURN NUMBER
945 IS
946 BEGIN
947
948 RETURN cp_precision;
949 END;
950
951 FUNCTION cp_irpef_p
952 RETURN VARCHAR2
953 IS
954 BEGIN
955 RETURN g_irpef;
956 END;
957
958 FUNCTION cp_inps_p
959 RETURN VARCHAR2
960 IS
961 BEGIN
962 RETURN g_inps;
963 END;
964
965 END je_itwhye_ap_pkg ;