[Home] [Help]
PACKAGE BODY: APPS.JE_ES_WHTAX
Source
1 PACKAGE BODY JE_ES_WHTAX AS
2 /* $Header: jeeswhtb.pls 120.35.12020000.3 2013/03/07 12:14:08 abhijsar ship $ */
3 PROCEDURE plsqlmsg ( msg IN VARCHAR2) IS
4 BEGIN
5 fnd_file.put_line(fnd_file.output, msg);
6 END plsqlmsg;
7 PROCEDURE dbmsmsg( msg IN VARCHAR2) IS
8 BEGIN
9 fnd_file.put_line(fnd_file.log,msg);
10 END dbmsmsg;
11 /* Delete EXTERNAL transactions */
12 PROCEDURE del_trans_x ( -- p_org_name IN VARCHAR2,-- Bug 5207771 org_id removed
13 p_legal_entity_name IN VARCHAR2,
14 p_fin_ind IN VARCHAR2) IS
15 bad_parameters EXCEPTION;
16 bad_legal_entity EXCEPTION;
17 bad_org_name EXCEPTION;
18 current_org_id number(15);
19 current_legal_entity_id number(15);
20 BEGIN
21 if p_fin_ind = 'S' then
22 RAISE bad_parameters;
23 end if;
24 if p_legal_entity_name is NOT NULL then
25 select legal_entity_id
26 into current_legal_entity_id
27 from XLE_FIRSTPARTY_INFORMATION_V
28 where upper(name) = upper(p_legal_entity_name);
29 If (SQL%NOTFOUND) then
30 RAISE bad_legal_entity;
31 else
32 DELETE je_es_modelo_190_all
33 WHERE legal_entity_id = current_legal_entity_id
34 and fin_ind = p_fin_ind;
35 COMMIT;
36 end if;
37 end if;
38 -- bug 5207771: Removed org_id condition
39 /*
40 if p_org_name is NOT NULL then
41 select organization_id
42 into current_org_id
43 from hr_organization_units
44 where UPPER(name) = UPPER(p_org_name);
45 If (SQL%NOTFOUND) then
46 RAISE bad_org_name;
47 else
48 DELETE je_es_modelo_190_all
49 WHERE org_id = current_org_id
50 and fin_ind = p_fin_ind;
51 COMMIT;
52 end if;
53 end if;
54 */
55 EXCEPTION
56 WHEN bad_parameters THEN
57 dbmsmsg('Error: Please call this routine with a parameter for FIN_IND <> S');
58 WHEN bad_legal_entity THEN
59 dbmsmsg('Error: Legal Entity Name ' || p_legal_entity_name || ' is not a valid Legal Entity');
60 -- WHEN bad_org_name THEN
61 -- dbmsmsg('Error: Org Name ' || p_org_name || ' is not a valid Organization');
62 END del_trans_x;
63 /* Delete Oracle Payables Hard Copy transactions */
64 PROCEDURE del_trans_s ( p_conc_req_id IN NUMBER,
65 p_legal_entity_id IN NUMBER,
66 p_org_id IN NUMBER ) IS
67 BEGIN
68 DELETE je_es_modelo_190_all
69 WHERE fin_ind = 'S'
70 and conc_req_id = p_conc_req_id
71 and legal_entity_id = p_legal_entity_id;
72 -- bug 5207771: Removed org_id condition
73 -- and org_id = p_org_id;
74 COMMIT;
75 END del_trans_s;
76 /* Delete Oracle Payables Magnetic transactions */
77 PROCEDURE del_trans_m ( p_legal_entity_id IN NUMBER,
78 p_org_id IN NUMBER) IS
79 BEGIN
80 DELETE je_es_modelo_190_all
81 WHERE fin_ind = 'S'
82 and conc_req_id is NULL
83 and legal_entity_id = p_legal_entity_id;
84 -- bug 5207771: Removed org_id condition
85 -- and org_id = p_org_id;
86 COMMIT;
87 END del_trans_m;
88 /* Insert EXTERNAL PAID transactions */
89 PROCEDURE ins_trans ( p_legal_entity_name IN VARCHAR2,
90 -- p_org_name IN VARCHAR2, -- Bug 5207771 org_id removed
91 p_fin_ind IN VARCHAR2,
92 p_remun_type IN VARCHAR2,
93 p_vendor_nif IN VARCHAR2,
94 p_vendor_name IN VARCHAR2,
95 p_date_paid IN VARCHAR2,
96 p_net_amount IN NUMBER,
97 p_withholding_tax_amount IN NUMBER,
98 p_zip_electronic IN VARCHAR2,
99 p_num_children IN NUMBER,
100 p_sign IN VARCHAR2,
101 p_tax_rate IN NUMBER,
102 p_year_due IN NUMBER,
103 p_sub_remun_type IN VARCHAR2,
104 p_withholdable_amt_in_kind IN NUMBER,
105 p_withheld_amt_in_kind IN NUMBER,
106 p_withheld_pymt_amt_in_kind IN NUMBER,
107 p_earned_amounts IN NUMBER,
108 p_contract_type IN NUMBER,
109 p_birth_year IN NUMBER,
110 p_disabled IN NUMBER,
111 p_family_situation IN NUMBER,
112 p_partner_fiscal_code IN VARCHAR2,
113 p_descendant_lt_3 IN NUMBER,
114 p_descendant_bt_3_16 IN NUMBER,
115 p_descendant_bt_16_25 IN NUMBER,
116 p_disable_desc_bt_33_65 IN NUMBER,
117 p_disable_desc_gt_65 IN NUMBER,
118 p_descendant_total IN NUMBER,
119 p_deductions IN NUMBER,
120 p_expenses IN NUMBER,
121 p_spouse_maintenance_amt IN NUMBER,
122 p_children_maintenance_amt IN NUMBER
123 ) IS
124 bad_num_children EXCEPTION;
125 bad_parameters EXCEPTION;
126 missing_parameters EXCEPTION;
127 bad_legal_name EXCEPTION;
128 bad_org_name EXCEPTION;
129 current_org_id number(15);
130 current_legal_entity_id number(15);
131 BEGIN
132 if p_fin_ind = 'S' then
133 RAISE bad_parameters;
134 end if;
135 if p_num_children is NOT NULL then
136 if (p_num_children < 0) or (p_num_children > 99) then
137 RAISE bad_num_children;
138 end if;
139 end if;
140 if (p_legal_entity_name is NOT NULL) then
141 select legal_entity_id
142 into current_legal_entity_id
143 from XLE_FIRSTPARTY_INFORMATION_V
144 where upper(name) = upper(p_legal_entity_name);
145 If (SQL%NOTFOUND) then
146 RAISE bad_legal_name;
147 end if;
148 -- bug 5207771: Removed org_id condition
149 /*
150 if (p_org_name is NOT NULL) then
151 select organization_id
152 into current_org_id
153 from hr_organization_units
154 where UPPER(name) = UPPER(p_org_name);
155 If (SQL%NOTFOUND) then
156 RAISE bad_org_name;
157 end if;
158 else
159 current_org_id := NULL;
160 end if;
161 */
162 INSERT INTO je_es_modelo_190_all(
163 legal_entity_id,
164 -- org_id, -- Bug 5207771 org_id removed
165 fin_ind,
166 remun_type,
167 vendor_nif,
168 vendor_name,
169 date_paid,
170 net_amount,
171 withholding_tax_amount,
172 zip_electronic,
173 num_children,
174 sign,
175 tax_rate,
176 year_due,
177 sub_remun_type ,
178 withholdable_amt_in_kind ,
179 withholdable_amt_in_kind_sign ,
180 withheld_amt_in_kind ,
181 withheld_pymt_amt_in_kind ,
182 earned_amounts ,
183 contract_type ,
184 birth_year ,
185 disabled ,
186 family_situation ,
187 partner_fiscal_code ,
188 descendant_lt_3 ,
189 descendant_bt_3_16 ,
190 descendant_bt_16_25 ,
191 disable_desc_bt_33_65 ,
192 disable_desc_gt_65 ,
193 descendant_total ,
194 deductions ,
195 expenses ,
196 spouse_maintenance_amt ,
197 children_maintenance_amt
198 )
199 values( current_legal_entity_id,
200 -- current_org_id, -- Bug 5207771 org_id removed
201 p_fin_ind,
202 p_remun_type,
203 p_vendor_nif,
204 substr(p_vendor_name,1,80) , -- AP UTF8 Changes 2398166
205 p_date_paid,
206 p_net_amount,
207 p_withholding_tax_amount,
208 p_zip_electronic,
209 p_num_children,
210 p_sign,
211 p_tax_rate,
212 p_year_due,
213 p_sub_remun_type ,
214 p_withholdable_amt_in_kind ,
215 decode(p_withholdable_amt_in_kind,NULL,NULL,
216 decode(sign(p_withholdable_amt_in_kind),-1,'N',' ')),
217 p_withheld_amt_in_kind ,
218 p_withheld_pymt_amt_in_kind ,
219 p_earned_amounts ,
220 p_contract_type ,
221 p_birth_year ,
222 p_disabled ,
223 p_family_situation ,
224 p_partner_fiscal_code ,
225 p_descendant_lt_3 ,
226 p_descendant_bt_3_16 ,
227 p_descendant_bt_16_25 ,
228 p_disable_desc_bt_33_65 ,
229 p_disable_desc_gt_65 ,
230 p_descendant_total ,
231 p_deductions ,
232 p_expenses ,
233 p_spouse_maintenance_amt ,
234 p_children_maintenance_amt
235 );
236 else
237 RAISE missing_parameters;
238 end if;
239 EXCEPTION
240 WHEN bad_num_children THEN
241 dbmsmsg('Error: Please enter a value between 0 and 99 for P_NUM_CHILDREN');
242 WHEN bad_parameters THEN
243 dbmsmsg('Error: Please use the correct parameters when inserting FIN_IND = S transactions');
244 WHEN bad_legal_name THEN
245 dbmsmsg('Error: Legal Entity Name ' || p_legal_entity_name || ' is not a valid Legal Entity');
246 -- WHEN bad_org_name THEN
247 -- dbmsmsg('Error: Org Name ' || p_org_name || ' is not a valid Organization');
248 WHEN missing_parameters THEN
249 dbmsmsg('Error: Legal Entity Name has to be given as a parameter');
250 END ins_trans;
251 /* Insert EXTERNAL APPROVED transactions */
252 PROCEDURE ins_trans ( p_legal_entity_name IN VARCHAR2,
253 -- p_org_name IN VARCHAR2,-- Bug 5207771 org_id removed
254 p_fin_ind IN VARCHAR2,
255 p_remun_type IN VARCHAR2,
256 p_vendor_nif IN VARCHAR2,
257 p_vendor_name IN VARCHAR2,
258 p_gl_date IN VARCHAR2,
259 p_net_amount IN NUMBER,
260 p_withholding_tax_amount IN NUMBER,
261 p_zip_electronic IN VARCHAR2,
262 p_num_children IN NUMBER,
263 p_sign IN VARCHAR2,
264 p_tax_rate IN NUMBER,
265 p_year_due IN NUMBER,
266 p_sub_remun_type IN VARCHAR2,
267 p_withholdable_amt_in_kind IN NUMBER,
268 p_withheld_amt_in_kind IN NUMBER,
269 p_withheld_pymt_amt_in_kind IN NUMBER,
270 p_earned_amounts IN NUMBER,
271 p_contract_type IN NUMBER,
272 p_birth_year IN NUMBER,
273 p_disabled IN NUMBER,
274 p_family_situation IN NUMBER,
275 p_partner_fiscal_code IN VARCHAR2,
276 p_descendant_lt_3 IN NUMBER,
277 p_descendant_bt_3_16 IN NUMBER,
278 p_descendant_bt_16_25 IN NUMBER,
279 p_disable_desc_bt_33_65 IN NUMBER,
280 p_disable_desc_gt_65 IN NUMBER,
281 p_descendant_total IN NUMBER,
282 p_deductions IN NUMBER,
283 p_expenses IN NUMBER,
284 p_spouse_maintenance_amt IN NUMBER,
285 p_children_maintenance_amt IN NUMBER
286 ) IS
287 bad_num_children EXCEPTION;
288 bad_parameters EXCEPTION;
289 missing_parameters EXCEPTION;
290 bad_legal_name EXCEPTION;
291 bad_org_name EXCEPTION;
292 current_legal_entity_id number(15);
293 current_org_id number(15);
294 BEGIN
295 if p_fin_ind = 'S' then
296 RAISE bad_parameters;
297 end if;
298 if p_num_children is NOT NULL then
299 if (p_num_children < 0) or (p_num_children > 99) then
300 RAISE bad_num_children;
301 end if;
302 end if;
303 if (p_legal_entity_name is NOT NULL) then
304 select legal_entity_id
305 into current_legal_entity_id
306 from XLE_FIRSTPARTY_INFORMATION_V
307 where upper(name) = upper(p_legal_entity_name);
308 If (SQL%NOTFOUND) then
309 RAISE bad_legal_name;
310 end if;
311 -- bug 5207771: Removed org_id condition
312 /*
313 if (p_org_name is NOT NULL) then
314 select organization_id
315 into current_org_id
316 from hr_organization_units
317 where UPPER(name) = UPPER(p_org_name);
318 If (SQL%NOTFOUND) then
319 RAISE bad_org_name;
320 end if;
321 else
322 current_org_id := NULL;
323 end if;
324 */
325 INSERT INTO je_es_modelo_190_all(
326 legal_entity_id,
327 -- org_id, -- Bug 5207771 org_id removed
328 fin_ind,
329 remun_type,
330 vendor_nif,
331 vendor_name,
332 gl_date,
333 net_amount,
334 withholding_tax_amount,
335 zip_electronic,
336 num_children,
337 sign,
338 tax_rate,
339 year_due,
340 sub_remun_type ,
341 withholdable_amt_in_kind ,
342 withholdable_amt_in_kind_sign ,
343 withheld_amt_in_kind ,
344 withheld_pymt_amt_in_kind ,
345 earned_amounts ,
346 contract_type ,
347 birth_year ,
348 disabled ,
349 family_situation ,
350 partner_fiscal_code ,
351 descendant_lt_3 ,
352 descendant_bt_3_16 ,
353 descendant_bt_16_25 ,
354 disable_desc_bt_33_65 ,
355 disable_desc_gt_65 ,
356 descendant_total ,
357 deductions ,
358 expenses ,
359 spouse_maintenance_amt ,
360 children_maintenance_amt
361 )
362 values( current_legal_entity_id,
363 -- current_org_id, -- Bug 5207771 org_id removed
364 p_fin_ind,
365 p_remun_type,
366 p_vendor_nif,
367 substr(p_vendor_name,1,80), -- AP UTF8 Changes 2398166
368 p_gl_date,
369 p_net_amount,
370 p_withholding_tax_amount,
371 p_zip_electronic,
372 p_num_children,
373 p_sign,
374 p_tax_rate,
375 p_year_due,
376 p_sub_remun_type ,
377 p_withholdable_amt_in_kind ,
378 decode(p_withholdable_amt_in_kind,NULL,NULL,
379 decode(sign(p_withholdable_amt_in_kind),-1,'N',' ')),
380 p_withheld_amt_in_kind ,
381 p_withheld_pymt_amt_in_kind ,
382 p_earned_amounts ,
383 p_contract_type ,
384 p_birth_year ,
385 p_disabled ,
386 p_family_situation ,
387 p_partner_fiscal_code ,
388 p_descendant_lt_3 ,
389 p_descendant_bt_3_16 ,
390 p_descendant_bt_16_25 ,
391 p_disable_desc_bt_33_65 ,
392 p_disable_desc_gt_65 ,
393 p_descendant_total ,
394 p_deductions ,
395 p_expenses ,
396 p_spouse_maintenance_amt ,
397 p_children_maintenance_amt
398 );
399 else
400 RAISE missing_parameters;
401 end if;
402 EXCEPTION
403 WHEN bad_parameters THEN
404 dbmsmsg('Error: Please use the correct parameters when inserting FIN_IND = S transactions');
405 WHEN bad_num_children THEN
406 dbmsmsg('Error: Please enter a value between 0 and 99 for P_NUM_CHILDREN');
407 WHEN bad_legal_name THEN
408 dbmsmsg('Error: Legal Entity Name ' || p_legal_entity_name || ' is not a valid Legal Entity');
409 -- WHEN bad_org_name THEN
410 -- dbmsmsg('Error: Org Name ' || p_org_name || ' is not a valid Organization');
411 WHEN missing_parameters THEN
412 dbmsmsg('Error: Legal Entity Name has to be given as a parameter');
413 END ins_trans;
414 /* Insert Oracle Payables transactions */
415 PROCEDURE ins_trans ( legal_entity_id NUMBER,
416 org_id NUMBER,
417 conc_req_id NUMBER,
418 remun_type VARCHAR2,
419 sub_remun_type VARCHAR2,
420 vendor_nif VARCHAR2,
421 vendor_name VARCHAR2,
422 invoice_id NUMBER,
423 invoice_num VARCHAR2,
424 inv_doc_seq_num VARCHAR2,
425 invoice_date VARCHAR2,
426 gl_date VARCHAR2,
427 invoice_payment_id NUMBER,
428 date_paid VARCHAR2,
429 net_amount NUMBER,
430 withholding_tax_amount NUMBER,
431 zip_electronic VARCHAR2,
432 zip_legal VARCHAR2,
433 city_legal VARCHAR2,
434 num_children NUMBER,
435 sign VARCHAR2,
436 tax_rate NUMBER,
437 tax_name VARCHAR2,
438 year_due NUMBER
439 ) IS
440 BEGIN
441 INSERT INTO je_es_modelo_190_all( legal_entity_id,
442 org_id,
443 conc_req_id,
444 fin_ind,
445 remun_type,
446 vendor_nif,
447 vendor_name,
448 invoice_id,
449 invoice_num,
450 inv_doc_seq_num,
451 invoice_date,
452 gl_date,
453 invoice_payment_id,
454 date_paid,
455 net_amount,
456 withholding_tax_amount,
457 zip_electronic,
458 zip_legal,
459 city_legal,
460 num_children,
461 sign,
462 tax_rate,
463 tax_name,
464 year_due,
465 sub_remun_type
466 )
467 values( legal_entity_id,
468 org_id,
469 conc_req_id,
470 'S',
471 remun_type,
472 vendor_nif,
473 substr(vendor_name,1,80) , -- AP UTF8 Changes 2398166
474 invoice_id,
475 invoice_num,
476 inv_doc_seq_num,
477 invoice_date,
478 gl_date,
479 invoice_payment_id,
480 date_paid,
481 net_amount,
482 withholding_tax_amount,
483 zip_electronic,
484 zip_legal,
485 city_legal,
486 num_children,
487 sign,
488 tax_rate,
489 tax_name,
490 year_due,
491 sub_remun_type
492 );
493 END ins_trans;
494 -----------------------------------------------------------------------
495 -- Function get_amount_withheld returns the AWT withheld amount on
496 -- an invoice.
497 --
498 FUNCTION get_amount_withheld( l_invoice_id IN NUMBER,
499 l_org_id IN NUMBER,
500 l_legal_entity_id IN NUMBER)
501 RETURN NUMBER IS
502 amount_withheld NUMBER := 0;
503 BEGIN
504 select (0 - sum(nvl(dist.base_amount,nvl(dist.amount,0))))
505 into amount_withheld
506 from ap_invoice_distributions_all dist,
507 ap_invoice_lines_all line,
508 ap_invoices_all inv
509 where dist.invoice_id = l_invoice_id
510 and inv.legal_entity_id = nvl(l_legal_entity_id, inv.legal_entity_id)
511 -- Bug 5207771 : Org_id is removed
512 -- and inv.org_id = nvl(l_org_id, inv.org_id)
513 and inv.invoice_id = line.invoice_id
514 and dist.invoice_id = line.invoice_id
515 -- and dist.distribution_line_number = line.line_number commented and added below logic for bug 7300332
516 and dist.invoice_line_number = line.line_number
517 and dist.line_type_lookup_code = 'AWT';
518 return(amount_withheld);
519 END get_amount_withheld;
520 -----------------------------------------------------------------------
521 -- Function get_prepaid_amount returns the prepayment amount on
522 -- an invoice.
523 --
524 FUNCTION get_prepaid_amount( l_invoice_id IN NUMBER,
525 l_org_id IN NUMBER,
526 l_legal_entity_id IN NUMBER)
527 RETURN NUMBER IS
528 prepaid_amount NUMBER := 0;
529 BEGIN
530 select (0 - sum(nvl(dist.base_amount,nvl(dist.amount,0))))
531 into prepaid_amount
532 from ap_invoice_distributions_all dist,
533 ap_invoice_lines_all line,
534 ap_invoices_all inv
535 where dist.invoice_id = l_invoice_id
536 and inv.legal_entity_id = nvl(l_legal_entity_id, inv.legal_entity_id)
537 -- Bug 5207771 : Org_id is removed
538 -- and inv.org_id = nvl(l_org_id, inv.org_id)
539 and inv.invoice_id = line.invoice_id
540 and dist.invoice_id = line.invoice_id
541 -- and dist.distribution_line_number = line.line_number Commented and added below logic for Bug 7300332
542 and dist.invoice_line_number = line.line_number
543 and dist.line_type_lookup_code = 'PREPAY';
544 return(prepaid_amount);
545 END get_prepaid_amount;
546 ----------------------------------------------------------------------
547 -- Function get_awt_net_total returns the total distribution
548 -- amount for the invoice associated with withholding group.
549 -- BUG 3930123 : The net amount should be calculated only for the requested accounting period
550 -- spanugan 17/12/2004
551 FUNCTION get_awt_net_total(l_invoice_id IN NUMBER,
552 l_legal_entity_id IN NUMBER,
553 l_org_id IN NUMBER,
554 l_date_from IN DATE,
555 l_date_to IN DATE)
556 RETURN NUMBER IS
557 l_awt_net_total NUMBER := 0;
558 BEGIN
559 SELECT NVL(SUM(nvl(dist.base_amount,NVL(dist.amount,0))),0)
560 INTO l_awt_net_total
561 FROM ap_invoice_distributions_all dist,
562 ap_invoice_lines_all line,
563 ap_invoices_all inv
564 WHERE dist.invoice_id = l_invoice_id
565 and inv.legal_entity_id = nvl(l_legal_entity_id, inv.legal_entity_id)
566 -- Bug 5207771 : Org_id is removed
567 -- and inv.org_id = nvl(l_org_id, inv.org_id)
568 and inv.invoice_id = line.invoice_id
569 and dist.invoice_id = line.invoice_id
570 -- and dist.distribution_line_number = line.line_number Commented and added below for bug 7300332
571 and dist.invoice_line_number = line.line_number
572 and dist.awt_group_id IS NOT NULL
573 and dist.line_type_lookup_code NOT IN ('AWT')
574 and trunc(dist.accounting_date) between trunc(l_Date_From) and trunc(l_Date_To);
575 -- and dist.accounting_date = l_accounting_date; -- Bug 3930123
576 RETURN(l_awt_net_total);
577 END get_awt_net_total;
578 ----------------------------------------------------------------------
579 -- Function get_payments_count returns the total number of
580 -- accounted payments for the invoice.
581 --
582 FUNCTION get_payments_count( l_invoice_id IN NUMBER,
583 l_legal_entity_id IN NUMBER,
584 l_org_id IN NUMBER)
585 RETURN NUMBER IS
586 l_payments_count NUMBER := 0;
587 BEGIN
588 SELECT COUNT(aip.invoice_payment_id)
589 INTO l_payments_count
590 FROM ap_invoice_payments_all aip,
591 ap_checks_all ac
592 WHERE aip.invoice_id = l_invoice_id
593 AND ac.legal_entity_id = nvl(l_legal_entity_id, ac.legal_entity_id)
594 -- bug 5207771: Removed org_id condition
595 --and ac.org_id = nvl(l_org_id,ac.org_id)
596 AND aip.check_id = ac.check_id
597 AND ac.void_date is null;
598 RETURN(l_payments_count);
599 END get_payments_count;
600 ----------------------------------------------------------------------
601 -- Main Procedure Called by concurrent program.
602 --
603 PROCEDURE get_data ( ERRBUF OUT NOCOPY VARCHAR2,
604 RETCODE OUT NOCOPY NUMBER,
605 p_pay_inv_sel IN VARCHAR2,
606 p_summary IN VARCHAR2,
607 p_date_from IN VARCHAR2,
608 p_date_to IN VARCHAR2,
609 p_vendor_id IN NUMBER ,
610 p_conc_req_id IN NUMBER ,
611 p_hard_copy IN VARCHAR2 ,
612 p_wht_tax_type IN VARCHAR2,
613 p_legal_entity_id IN NUMBER,
614 p_org_id IN NUMBER,
615 p_rep_site_ou IN NUMBER
616 ) IS
617 bad_parameters EXCEPTION;
618 bad_awt_lines EXCEPTION; -- Bug 1271489
619 countrecs NUMBER;
620 first_record NUMBER := 0;
621 conc_req_id1 JE_ES_MODELO_190_ALL.conc_req_id%TYPE;
622 fin_ind1 JE_ES_MODELO_190_ALL.fin_ind%TYPE;
623 remun_type1 JE_ES_MODELO_190_ALL.remun_type%TYPE;
624 sub_remun_type1 JE_ES_MODELO_190_ALL.sub_remun_type%TYPE;
625 vendor_nif1 JE_ES_MODELO_190_ALL.vendor_nif%TYPE;
626 vendor_name1 JE_ES_MODELO_190_ALL.vendor_name%TYPE;
627 invoice_id1 JE_ES_MODELO_190_ALL.invoice_id%TYPE;
628 invoice_num1 JE_ES_MODELO_190_ALL.invoice_num%TYPE;
629 inv_doc_seq_num1 JE_ES_MODELO_190_ALL.inv_doc_seq_num%TYPE;
630 invoice_date1 JE_ES_MODELO_190_ALL.invoice_date%TYPE;
631 gl_date1 JE_ES_MODELO_190_ALL.gl_date%TYPE;
632 invoice_payment_id1 JE_ES_MODELO_190_ALL.invoice_payment_id%TYPE;
633 awt_invoice_payment_id JE_ES_MODELO_190_ALL.invoice_payment_id%TYPE;
634 date_paid1 JE_ES_MODELO_190_ALL.date_paid%TYPE;
635 invoice_amount JE_ES_MODELO_190_ALL.net_amount%TYPE;
636 inv_payment_status_flag ap_invoices.payment_status_flag%TYPE;
637 wht_mode ap_invoices.payment_status_flag%TYPE;
638 inv_awt_flag ap_invoices.awt_flag%TYPE;
639 dist_awt_flag ap_invoice_distributions_all.awt_flag%type; -- bug 8709676
640 paid_amount JE_ES_MODELO_190_ALL.net_amount%TYPE;
641 invoice_prepaid_amount JE_ES_MODELO_190_ALL.net_amount%TYPE;
642 invoice_withheld_amount JE_ES_MODELO_190_ALL.net_amount%TYPE;
643 inv_dist_net_amount JE_ES_MODELO_190_ALL.net_amount%TYPE;
644 discount_amount JE_ES_MODELO_190_ALL.net_amount%TYPE;
645 net_amount1 JE_ES_MODELO_190_ALL.net_amount%TYPE;
646 wht_net_amount1 JE_ES_MODELO_190_ALL.net_amount%TYPE;
647 inv_net_amount1 JE_ES_MODELO_190_ALL.net_amount%TYPE;
648 withholding_tax_amount1 JE_ES_MODELO_190_ALL.withholding_tax_amount%TYPE;
649 inv_wht_amount1 JE_ES_MODELO_190_ALL.withholding_tax_amount%TYPE;
650 zip_electronic1 JE_ES_MODELO_190_ALL.zip_electronic%TYPE;
651 zip_legal1 JE_ES_MODELO_190_ALL.zip_legal%TYPE;
652 city_legal1 JE_ES_MODELO_190_ALL.city_legal%TYPE;
653 num_children1 JE_ES_MODELO_190_ALL.num_children%TYPE;
654 sign1 JE_ES_MODELO_190_ALL.sign%TYPE;
655 tax_rate1 JE_ES_MODELO_190_ALL.tax_rate%TYPE;
656 tax_name1 JE_ES_MODELO_190_ALL.tax_name%TYPE;
657 year_due1 JE_ES_MODELO_190_ALL.year_due%TYPE;
658 invoice_payments_count number := 0;
659 func_curr fnd_currencies_vl.currency_code%TYPE;
660 func_curr_precision fnd_currencies_vl.precision%TYPE;
661 old_remun_type JE_ES_MODELO_190_ALL.remun_type%TYPE;
662 old_sub_remun_type JE_ES_MODELO_190_ALL.sub_remun_type%TYPE;
663 old_vendor_nif JE_ES_MODELO_190_ALL.vendor_nif%TYPE;
664 old_vendor_name JE_ES_MODELO_190_ALL.vendor_name%TYPE;
665 old_city_legal JE_ES_MODELO_190_ALL.city_legal%TYPE;
666 old_zip_electronic JE_ES_MODELO_190_ALL.zip_electronic%TYPE;
667 old_zip_legal JE_ES_MODELO_190_ALL.zip_legal%TYPE;
668 old_tax_rate JE_ES_MODELO_190_ALL.tax_rate%TYPE;
669 old_tax_name JE_ES_MODELO_190_ALL.tax_name%TYPE;
670 l_parent_reversal_flag VARCHAR2(1); -- Bug 9759711
671 l_le_id_count NUMBER;
672 l_le_id_message VARCHAR2(500);
673 l_ledger_id number;
674 date_from date;
675 date_to date;
676 --
677 -- Summary APPROVED transactions Magnetic Report
678 -- Tax Code and Tax Rate are not used in Magnetic Format(Bug 998053).
679 --
680 CURSOR sum_approve_mag IS
681 SELECT decode(nvl(v.employee_id,-1),-1,'G','A'),
682 decode(nvl(v.employee_id,-1),-1,'01','00'),
683 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
684 substr(v.vendor_name,1,80) , -- AP UTF8 Changes 2398166
685 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3)),
686 sum(decode(dist.awt_group_id,NULL,0,
687 decode(dist.line_type_lookup_code,'AWT',0,
688 nvl(dist.base_amount,dist.amount)))) net_amount,
689 sum(decode(dist.line_type_lookup_code,'AWT',
690 nvl(dist.base_amount,dist.amount),0)) withholding_tax_amount
691 FROM po_vendors v,
692 po_vendor_sites_all vs,
693 fnd_lookups fl,
694 ap_invoices_all inv,
695 ap_invoice_lines_all line,
696 ap_invoice_distributions_all dist,
697 ap_tax_codes_all atc,
698 ap_awt_tax_rates_all awt,
699 (SELECT distinct person_id
700 ,national_identifier
701 FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
702 WHERE v.vendor_id = vs.vendor_id
703 AND nvl(v.employee_id,-99) = papf.person_id (+)
704 -- bug 8551359 - start
705 --AND vs.tax_reporting_site_flag = 'Y'
706 AND exists (select 'x'
707 from po_vendor_sites_all
708 where vendor_id = v.vendor_id
709 and tax_reporting_site_flag = 'Y'
710 and org_id = p_rep_site_ou)
711 -- bug 8551359 - end
712 AND vs.country = fl.lookup_code(+)
713 AND fl.lookup_type = 'JEES_EURO_COUNTRY_CODES'
714 AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
715 AND inv.vendor_id = v.vendor_id
716 and vs.vendor_site_id = inv.vendor_site_id
717 and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
718 -- bug 5207771: Removed org_id condition
719 --and inv.org_id = nvl(p_org_id,inv.org_id)
720 and inv.invoice_id = line.invoice_id
721 and dist.invoice_id = line.invoice_id
722 and dist.invoice_line_number = line.line_number
723 -----and inv.cancelled_date is null -- Bug 2228008 )
724 AND dist.parent_reversal_id is null
725 -- bug 8496890
726 /*AND not exists ( select 1
727 from ap_invoice_distributions dist1, gl_period_statuses gl
728 where gl.application_id = 101
729 and dist1.invoice_id = inv.invoice_id
730 and dist1.parent_reversal_id = dist.invoice_distribution_id
731 and gl.ledger_id = dist1.set_of_books_id
732 and dist.accounting_date between gl.start_date and gl.end_date
733 and dist1.accounting_date <= gl.end_date )
734 */
735 AND not exists ( select 1
736 from ap_invoice_distributions dist1
737 where dist1.invoice_id = inv.invoice_id
738 and dist1.parent_reversal_id = dist.invoice_distribution_id
739 and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
740 and fnd_date.canonical_to_date(P_Date_To)
741 )
742 -- bug 8496890
743 AND trunc(dist.accounting_date,'DD')
744 between fnd_date.canonical_to_date(P_Date_From)
745 AND fnd_date.canonical_to_date(P_Date_To)
746 AND ((dist.line_type_lookup_code = 'AWT')
747 OR
748 (dist.awt_group_id is not NULL))
749 AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
750 dist.ACCRUAL_POSTED_FLAG,
751 dist.CASH_POSTED_FLAG,
752 dist.POSTED_FLAG, inv.org_id) in ('Y','P')
753 -- bug 5207771 Added legal_entity id as 4th parameter, above line
754 AND dist.withholding_tax_code_id = atc.tax_id (+) -- bug 5102299
755 AND atc.name = awt.tax_name(+)
756 AND awt.vendor_id is null /* Ignore any Vendor Lines */
757 -- Bug 5207771 : Added to remove the duplicates WH lines
758 AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
759 OR (dist.awt_tax_rate_id is NULL) )
760 -- Ignore any invoices which do not have 'AWT' distribution lines
761 AND EXISTS ( select dist2.invoice_id
762 from ap_invoice_distributions_all dist2
763 where inv.invoice_id = dist2.invoice_id
764 and dist2.line_type_lookup_code = 'AWT'
765 and dist2.withholding_tax_code_id in
766 -- Bug 2019586: Column name should be tax_id.
767 -- (select tax_code_id from ap_tax_codes
768 (select tax_id from ap_tax_codes_all
769 where vat_transaction_type = p_wht_tax_type))
770 GROUP BY decode(nvl(v.employee_id,-1),-1,'G','A'),
771 decode(nvl(v.employee_id,-1),-1,'01','00'),
772 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
773 substr(v.vendor_name,1,80) ,
774 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3))
775 HAVING sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0
776 -- BUG 3930123 : Adding one more select clause with certain modifications, to fetch
777 -- the invoices that are cancelled in different accounting period.
778 -- spanugan 17/12/2004
779 UNION
780 SELECT decode(nvl(v.employee_id,-1),-1,'G','A'),
781 decode(nvl(v.employee_id,-1),-1,'01','00'),
782 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
783 substr(v.vendor_name,1,80) , -- AP UTF8 Changes 2398166
784 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3)),
785 sum(decode(dist.awt_group_id,NULL,0,
786 decode(dist.line_type_lookup_code,'AWT',0,
787 nvl(dist.base_amount,dist.amount)))) net_amount,
788 sum(decode(dist.line_type_lookup_code,'AWT',
789 nvl(dist.base_amount,dist.amount),0)) withholding_tax_amount
790 FROM fnd_lookups fl,
791 po_vendors v,
792 po_vendor_sites_all vs,
793 ap_tax_codes_all atc,
794 ap_awt_tax_rates_all awt,
795 ap_invoices_all inv,
796 ap_invoice_lines_all line,
797 ap_invoice_distributions_all dist,
798 (SELECT distinct person_id
799 ,national_identifier
800 FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
801 WHERE v.vendor_id = vs.vendor_id
802 AND nvl(v.employee_id,-99) = papf.person_id (+)
803 -- bug 8551359 - start
804 --AND vs.tax_reporting_site_flag = 'Y'
805 AND exists (select 'x'
806 from po_vendor_sites_all
807 where vendor_id = v.vendor_id
808 and tax_reporting_site_flag = 'Y'
809 and org_id = p_rep_site_ou)
810 -- bug 8551359 - end
811 AND vs.country = fl.lookup_code(+)
812 AND fl.lookup_type = 'JEES_EURO_COUNTRY_CODES'
813 AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
814 AND inv.vendor_id = v.vendor_id
815 and vs.vendor_site_id = inv.vendor_site_id
816 and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
817 -- bug 5207771: Removed org_id condition
818 --and inv.org_id = nvl(p_org_id,inv.org_id)
819 and inv.invoice_id = line.invoice_id
820 and dist.invoice_id = line.invoice_id
821 and dist.invoice_line_number = line.line_number
822 -- BUG 3930123 : spanugan
823 /*AND inv.cancelled_date is not null
824 AND (
825 (dist.cancellation_flag is null
826 AND dist.accounting_date < (select distinct gl.start_date
827 from gl_period_statuses gl
828 where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
829 from ap_invoice_distributions_all dist1
830 where dist1.invoice_id = inv.invoice_id
831 and dist1.cancellation_flag = 'Y' )))
832 OR
833 (dist.cancellation_flag = 'Y'
834 AND dist.accounting_date > (select distinct gl.end_date
835 from gl_period_statuses gl
836 where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
837 from ap_invoice_distributions_all dist1
838 where dist1.invoice_id = inv.invoice_id
839 and dist1.cancellation_flag is null )))
840 )
841 -- END
842 -- Bug 3930123 JCHALL . Changed the subquery above from
843 -- a single row returned to accept mutiple rows.
844 --
845 */
846 AND dist.parent_reversal_id is not null
847 -- bug 8496890
848 /*
849 AND dist.accounting_date > (select distinct gl.end_date
850 from ap_invoice_distributions dist1, gl_period_statuses gl
851 where gl.application_id = 101
852 and dist1.invoice_id = inv.invoice_id
853 and dist.parent_reversal_id = dist1.invoice_distribution_id
854 and gl.ledger_id = dist1.set_of_books_id
855 and dist1.accounting_date between gl.start_date and gl.end_date)
856 */
857 AND not exists (select 1 from ap_invoice_distributions dist1
858 where dist1.invoice_id = inv.invoice_id
859 and dist.parent_reversal_id = dist1.invoice_distribution_id
860 and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
861 and fnd_date.canonical_to_date(P_Date_To))
862 -- bug 8496890
863 AND trunc(dist.accounting_date,'DD')
864 between fnd_date.canonical_to_date(P_Date_From)
865 AND fnd_date.canonical_to_date(P_Date_To)
866 AND ((dist.line_type_lookup_code = 'AWT')
867 OR
868 (dist.awt_group_id is not NULL))
869 AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
870 dist.ACCRUAL_POSTED_FLAG,
871 dist.CASH_POSTED_FLAG,
872 dist.POSTED_FLAG, inv.org_id) in ('Y','P')
873 -- bug 5207771 Added legal_entity id as 4th parameter, above line
874 AND dist.withholding_tax_code_id = atc.tax_id(+)
875 AND atc.name = awt.tax_name(+)
876 AND awt.vendor_id is null /* Ignore any Vendor Lines */
877 -- Bug 5207771 : Added to remove the duplicates WH lines
878 AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
879 OR (dist.awt_tax_rate_id is NULL) )
880 -- Ignore any invoices which do not have 'AWT' distribution lines
881 AND EXISTS ( select dist2.invoice_id
882 from ap_invoice_distributions_all dist2
883 where inv.invoice_id = dist2.invoice_id
884 and dist2.line_type_lookup_code = 'AWT'
885 and dist2.withholding_tax_code_id in
886 -- Bug 2019586: Column name should be tax_id.
887 -- (select tax_code_id from ap_tax_codes
888 (select tax_id from ap_tax_codes_all
889 where vat_transaction_type = p_wht_tax_type))
890 GROUP BY decode(nvl(v.employee_id,-1),-1,'G','A'),
891 decode(nvl(v.employee_id,-1),-1,'01','00'),
892 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
893 substr(v.vendor_name,1,80) ,
894 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3))
895 HAVING sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0;
896 --
897 -- Detailed PAID transactions cursor.
898 -- This is used for Detail, Summary, Summary Magnetic format Transactions
899 -- extract purpose. It handles automatic witholding, manual witholding
900 --
901 CURSOR detail_paid IS
902 SELECT 'A',
903 decode(nvl(v.employee_id,-1),-1,'G','A'),
904 decode(nvl(v.employee_id,-1),-1,'01','00'),
905 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
906 substr(v.vendor_name,1,80) , -- AP UTF8 Changes 2398166
907 inv.awt_flag,
908 inv.payment_status_flag,
909 inv.invoice_id,
910 inv.invoice_num,
911 nvl(inv.base_amount,inv.invoice_amount) invoice_amount,
912 -- nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID),0) net_amount,
913 --bug10384931 nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
914 --bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount) net_amount, --bug10384931
915 -- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)) net_amount, --bug11891899
916 --nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)) net_amount,--bug14047712
917 nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)) net_amount, --bug14114547
918 nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) prepaid_amount,
919 nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) invoice_withheld_amount,
920 decode(seq.name || '-' ||
921 to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
922 to_char(inv.doc_sequence_value)),
923 trunc(inv.invoice_date,'DD'),
924 invpay.invoice_payment_id,
925 nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) payments_count,
926 nvl(invpay.payment_base_amount,invpay.amount),
927 nvl(invpay.discount_taken,0),
928 trunc(invpay.accounting_date,'DD'),
929 dist.awt_invoice_payment_id,
930 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'|| substr(fl.description,1,3)),
931 substr(vs.city,1,25), --bug12649867
932 0,
933 sum(nvl(dist.base_amount,dist.amount)),
934 dist.awt_flag dist_awt_flag, --bug 8709676
935 awt.tax_rate,
936 awt.tax_name,
937 'N'
938 FROM fnd_lookups fl,
939 po_vendors v,
940 po_vendor_sites_all vs,
941 ap_invoice_payments_all invpay,
942 ap_checks_all checks,
943 ap_tax_codes_all atc,
944 ap_awt_tax_rates_all awt,
945 fnd_document_sequences seq,
946 ap_invoices_all inv,
947 ap_invoice_lines_all line,
948 ap_invoice_distributions_all dist,
949 (SELECT distinct person_id
950 ,national_identifier
951 FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
952 WHERE vs.country = fl.lookup_code(+)
953 AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
954 ---AND v.vendor_id = nvl(p_vendor_id,v.vendor_id)
955 AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
956 AND nvl(v.employee_id,-99) = papf.person_id (+)
957 AND inv.vendor_id = v.vendor_id
958 AND v.vendor_id = vs.vendor_id
959 -- bug 8551359 - start
960 --AND vs.tax_reporting_site_flag = 'Y'
961 AND exists (select 'x'
962 from po_vendor_sites_all
963 where vendor_id = v.vendor_id
964 and tax_reporting_site_flag = 'Y'
965 and org_id = p_rep_site_ou)
966 -- bug 8551359 - end
967 and vs.vendor_site_id = inv.vendor_site_id
968 AND nvl(inv.awt_flag,'N') = 'Y'
969 and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
970 -- bug 5207771: Removed org_id condition
971 --and inv.org_id = nvl(p_org_id,inv.org_id)
972 and inv.invoice_id = line.invoice_id
973 and dist.invoice_id = line.invoice_id
974 and dist.invoice_line_number = line.line_number
975 ---and inv.cancelled_date is null -- Bug 2228008
976 AND dist.parent_reversal_id is null
977 -- bug 8496890
978 /*
979 AND not exists ( select 1
980 from ap_invoice_distributions dist1, gl_period_statuses gl
981 where gl.application_id = 101
982 and dist1.invoice_id = inv.invoice_id
983 and dist1.parent_reversal_id = dist.invoice_distribution_id
984 and gl.ledger_id = dist1.set_of_books_id
985 and dist.accounting_date between gl.start_date and gl.end_date
986 and dist1.accounting_date <= gl.end_date )
987 */
988 AND not exists ( select 1
989 from ap_invoice_distributions dist1
990 where dist1.invoice_id = inv.invoice_id
991 and dist1.parent_reversal_id = dist.invoice_distribution_id
992 and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
993 and fnd_date.canonical_to_date(P_Date_To)
994 )
995 -- bug 8496890
996
997 AND inv.invoice_id = invpay.invoice_id
998 AND ( invpay.posted_flag in ('Y','P')
999 or invpay.cash_posted_flag in ('Y','P')
1000 or invpay.accrual_posted_flag in ('Y','P'))
1001 AND invpay.check_id = checks.check_id
1002 AND checks.void_date is null
1003 AND trunc(invpay.accounting_date,'DD')
1004 between
1005 nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
1006 and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
1007 AND (dist.line_type_lookup_code = 'AWT')
1008 AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
1009 DIST.ACCRUAL_POSTED_FLAG,
1010 DIST.CASH_POSTED_FLAG,
1011 dist.POSTED_FLAG, inv.org_id) in ('Y','P')
1012 AND dist.withholding_tax_code_id = atc.tax_id(+)
1013 AND atc.name = awt.tax_name(+)
1014 AND awt.vendor_id is null /* Ignore any Vendor Lines */
1015 AND invpay.accounting_date
1016 between nvl(awt.start_date,invpay.accounting_date)
1017 and nvl(awt.end_date, invpay.accounting_date)
1018 AND inv.doc_sequence_id = seq.doc_sequence_id(+)
1019 -- Bug 5207771 : Added to remove the duplicates WH lines
1020 AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
1021 OR (dist.awt_tax_rate_id is NULL) )
1022 -- Ignore any invoices which do not have 'AWT' distribution lines
1023 AND EXISTS (select dist2.invoice_id
1024 from ap_invoice_distributions_all dist2
1025 where inv.invoice_id = dist2.invoice_id
1026 and dist2.line_type_lookup_code = 'AWT'
1027 and dist2.withholding_tax_code_id in
1028 -- Bug 2019586: Column name should be tax_id.
1029 -- (select tax_code_id from ap_tax_codes
1030 (select tax_id
1031 from ap_tax_codes_all
1032 where vat_transaction_type = p_wht_tax_type))
1033 AND NOT EXISTS ( select dist2.invoice_id
1034 from ap_invoice_distributions_all dist2
1035 where inv.invoice_id = dist2.invoice_id
1036 and dist2.line_type_lookup_code = 'AWT'
1037 and dist2.awt_flag <> 'A')
1038 GROUP BY 'A',
1039 decode(nvl(v.employee_id,-1),-1,'G','A'),
1040 decode(nvl(v.employee_id,-1),-1,'01','00'),
1041 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1042 substr(v.vendor_name,1,80) ,
1043 inv.awt_flag,
1044 inv.payment_status_flag,
1045 inv.invoice_id,
1046 inv.invoice_num,
1047 nvl(inv.base_amount,inv.invoice_amount) ,
1048 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0),
1049 --10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0),
1050 --bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount), --bug10384931
1051 -- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)), --bug11891899
1052 --nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)),--bug14047712
1053 nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)), --bug14114547
1054 nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) ,
1055 nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0),
1056 decode(seq.name || '-' ||
1057 to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
1058 to_char(inv.doc_sequence_value)),
1059 trunc(inv.invoice_date,'DD'),
1060 invpay.invoice_payment_id,
1061 nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) ,
1062 nvl(invpay.payment_base_amount,invpay.amount),
1063 nvl(invpay.discount_taken,0),
1064 trunc(invpay.accounting_date,'DD'),
1065 dist.awt_invoice_payment_id,
1066 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
1067 substr(fl.description,1,3)),
1068 substr(vs.city,1,25), --bug12649867
1069 0,
1070 dist.awt_flag, --bug 8709676
1071 awt.tax_rate,
1072 awt.tax_name,
1073 'N'
1074 HAVING ((sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0) or (min(awt.tax_rate) = 0))
1075 -- Bug 1212074
1076 -- BUG 3930123 : Adding one more select clause with certain modifications, to fetch
1077 -- the invoices that are cancelled in different accounting period.
1078 -- spanugan 17/12/2004
1079 UNION
1080 SELECT 'A',
1081 decode(nvl(v.employee_id,-1),-1,'G','A'),
1082 decode(nvl(v.employee_id,-1),-1,'01','00'),
1083 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1084 substr(v.vendor_name,1,80) , -- AP UTF8 Changes 2398166
1085 inv.awt_flag,
1086 inv.payment_status_flag,
1087 inv.invoice_id,
1088 inv.invoice_num,
1089 nvl(inv.base_amount,inv.invoice_amount) invoice_amount,
1090 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) net_amount,
1091 --bug10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
1092 --bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount) net_amount, --bug10384931
1093 -- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)) net_amount, --bug11891899
1094 --nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)) net_amount,--bug14047712
1095 nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)) net_amount, --bug14114547
1096 nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) prepaid_amount,
1097 nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) invoice_withheld_amount,
1098 decode(seq.name || '-' ||
1099 to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
1100 to_char(inv.doc_sequence_value)),
1101 trunc(inv.invoice_date,'DD'),
1102 invpay.invoice_payment_id,
1103 nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) payments_count,
1104 nvl(invpay.payment_base_amount,invpay.amount),
1105 nvl(invpay.discount_taken,0),
1106 trunc(invpay.accounting_date,'DD'),
1107 dist.awt_invoice_payment_id,
1108 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
1109 substr(fl.description,1,3)),
1110 substr(vs.city,1,25), --bug12649867
1111 0,
1112 sum(nvl(dist.base_amount,dist.amount)),
1113 dist.awt_flag dist_awt_flag, --bug 8709676
1114 awt.tax_rate,
1115 awt.tax_name,
1116 'Y'
1117 FROM fnd_lookups fl,
1118 po_vendors v,
1119 po_vendor_sites_all vs,
1120 ap_invoice_payments_all invpay,
1121 ap_checks_all checks,
1122 ap_tax_codes_all atc,
1123 ap_awt_tax_rates_all awt,
1124 fnd_document_sequences seq,
1125 ap_invoices_all inv,
1126 ap_invoice_lines_all line,
1127 ap_invoice_distributions_all dist,
1128 (SELECT distinct person_id
1129 ,national_identifier
1130 FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
1131 WHERE vs.country = fl.lookup_code(+)
1132 AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
1133 ---AND v.vendor_id = nvl(p_vendor_id,v.vendor_id)
1134 AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
1135 AND nvl(v.employee_id,-99) = papf.person_id (+)
1136 AND inv.vendor_id = v.vendor_id
1137 AND v.vendor_id = vs.vendor_id
1138 -- bug 8551359 - start
1139 --AND vs.tax_reporting_site_flag = 'Y'
1140 AND exists (select 'x'
1141 from po_vendor_sites_all
1142 where vendor_id = v.vendor_id
1143 and tax_reporting_site_flag = 'Y'
1144 and org_id = p_rep_site_ou)
1145 -- bug 8551359 - end
1146 and vs.vendor_site_id = inv.vendor_site_id
1147 AND nvl(inv.awt_flag,'N') = 'Y'
1148 and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
1149 -- bug 5207771: Removed org_id condition
1150 --and inv.org_id = nvl(p_org_id,inv.org_id)
1151 and inv.invoice_id = line.invoice_id
1152 and dist.invoice_id = line.invoice_id
1153 and dist.invoice_line_number = line.line_number
1154 -- BUG 3930123 : spanugan
1155 /*AND inv.cancelled_date is not null
1156 AND (
1157 (dist.cancellation_flag is null
1158 AND dist.accounting_date < (select distinct gl.start_date
1159 from gl_period_statuses gl
1160 where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
1161 from ap_invoice_distributions_all dist1
1162 where dist1.invoice_id = inv.invoice_id
1163 and dist1.cancellation_flag = 'Y' )))
1164 OR
1165 (dist.cancellation_flag = 'Y'
1166 AND dist.accounting_date > (select distinct gl.end_date
1167 from gl_period_statuses gl
1168 where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
1169 from ap_invoice_distributions_all dist1
1170 where dist1.invoice_id = inv.invoice_id
1171 and dist1.cancellation_flag is null )))
1172 )
1173 -- END
1174 */
1175 AND dist.parent_reversal_id is not null
1176 -- bug 8496890
1177 /*
1178 AND dist.accounting_date > (select distinct gl.end_date
1179 from ap_invoice_distributions dist1, gl_period_statuses gl
1180 where gl.application_id = 101
1181 and dist1.invoice_id = inv.invoice_id
1182 and dist.parent_reversal_id = dist1.invoice_distribution_id
1183 and gl.ledger_id = dist1.set_of_books_id
1184 and dist1.accounting_date between gl.start_date and gl.end_date)
1185 */
1186 AND not exists (select 1 from ap_invoice_distributions dist1
1187 where dist1.invoice_id = inv.invoice_id
1188 and dist.parent_reversal_id = dist1.invoice_distribution_id
1189 and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
1190 and fnd_date.canonical_to_date(P_Date_To))
1191 -- bug 8496890
1192 AND inv.invoice_id = invpay.invoice_id
1193 AND ( invpay.posted_flag in ('Y','P')
1194 or invpay.cash_posted_flag in ('Y','P')
1195 or invpay.accrual_posted_flag in ('Y','P'))
1196 AND invpay.check_id = checks.check_id
1197 AND checks.void_date is null
1198 AND trunc(invpay.accounting_date,'DD')
1199 between
1200 nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
1201 and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
1202 AND (dist.line_type_lookup_code = 'AWT')
1203 AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
1204 DIST.ACCRUAL_POSTED_FLAG,
1205 DIST.CASH_POSTED_FLAG,
1206 dist.POSTED_FLAG, inv.org_id) in ('Y','P')
1207 AND dist.withholding_tax_code_id = atc.tax_id(+)
1208 AND atc.name = awt.tax_name(+)
1209 AND awt.vendor_id is null /* Ignore any Vendor Lines */
1210 -- Bug 5207771 : Added to remove the duplicates WH lines
1211 AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
1212 OR (dist.awt_tax_rate_id is NULL) )
1213 AND invpay.accounting_date
1214 between nvl(awt.start_date,invpay.accounting_date)
1215 and nvl(awt.end_date, invpay.accounting_date)
1216 AND inv.doc_sequence_id = seq.doc_sequence_id(+)
1217 -- Ignore any invoices which do not have 'AWT' distribution lines
1218 AND EXISTS (select dist2.invoice_id
1219 from ap_invoice_distributions_all dist2
1220 where inv.invoice_id = dist2.invoice_id
1221 and dist2.line_type_lookup_code = 'AWT'
1222 and dist2.withholding_tax_code_id in
1223 -- Bug 2019586: Column name should be tax_id.
1224 -- (select tax_code_id from ap_tax_codes
1225 (select tax_id
1226 from ap_tax_codes_all
1227 where vat_transaction_type = p_wht_tax_type))
1228 AND NOT EXISTS ( select dist2.invoice_id
1229 from ap_invoice_distributions_all dist2
1230 where inv.invoice_id = dist2.invoice_id
1231 and dist2.line_type_lookup_code = 'AWT'
1232 and dist2.awt_flag <> 'A')
1233 GROUP BY 'A',
1234 decode(nvl(v.employee_id,-1),-1,'G','A'),
1235 decode(nvl(v.employee_id,-1),-1,'01','00'),
1236 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1237 substr(v.vendor_name,1,80) ,
1238 inv.awt_flag,
1239 inv.payment_status_flag,
1240 inv.invoice_id,
1241 inv.invoice_num,
1242 nvl(inv.base_amount,inv.invoice_amount) ,
1243 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0),
1244 --bug10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0),
1245 --bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount), --bug10384931
1246 -- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)), --bug11891899
1247 --nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)),--bug14047712
1248 nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)), --bug14114547
1249 nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) ,
1250 nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0),
1251 decode(seq.name || '-' ||
1252 to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
1253 to_char(inv.doc_sequence_value)),
1254 trunc(inv.invoice_date,'DD'),
1255 invpay.invoice_payment_id,
1256 nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) ,
1257 nvl(invpay.payment_base_amount,invpay.amount),
1258 nvl(invpay.discount_taken,0),
1259 trunc(invpay.accounting_date,'DD'),
1260 dist.awt_invoice_payment_id,
1261 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
1262 substr(fl.description,1,3)),
1263 substr(vs.city,1,25), --bug12649867
1264 0,
1265 dist.awt_flag, --bug 8709676
1266 awt.tax_rate,
1267 awt.tax_name,
1268 'Y'
1269 HAVING ((sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0) or (min(awt.tax_rate) = 0))
1270 -- Bug 1212074
1271 UNION
1272 SELECT 'A',
1273 decode(nvl(v.employee_id,-1),-1,'G','A'),
1274 decode(nvl(v.employee_id,-1),-1,'01','00'),
1275 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1276 v.vendor_name,
1277 inv.awt_flag,
1278 inv.payment_status_flag,
1279 inv.invoice_id,
1280 inv.invoice_num,
1281 nvl(inv.base_amount,inv.invoice_amount) invoice_amount,
1282 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) net_amount,
1283 --bug10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
1284 --bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount) net_amount, --bug10384931
1285 -- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)) net_amount, --bug11891899
1286 --nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)) net_amount,--bug14047712
1287 nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)) net_amount, --bug14114547
1288 nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) prepaid_amount,
1289 nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) invoice_withheld_amount,
1290 decode(seq.name || '-' || to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
1291 to_char(inv.doc_sequence_value)),
1292 trunc(inv.invoice_date,'DD'),
1293 invpay.invoice_payment_id,
1294 nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) payments_count,
1295 nvl(invpay.payment_base_amount,invpay.amount),
1296 nvl(invpay.discount_taken,0),
1297 trunc(invpay.accounting_date,'DD'),
1298 dist.awt_invoice_payment_id,
1299 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'|| substr(fl.description,1,3)),
1300 substr(vs.city,1,25), --bug12649867
1301 --bug11891899 (nvl(dist.awt_gross_amount,0)) wht_net_amount,
1302 (nvl(dist.awt_gross_amount,0)*nvl(dist.exchange_rate,1)) wht_net_amount, --bug11891899
1303 --bug 12420102 (nvl(dist.base_amount,dist.amount)),
1304 sum(nvl(dist.base_amount,dist.amount)), --bug 12420102
1305 dist.awt_flag dist_awt_flag, -- bug 8709676
1306 awt.tax_rate,
1307 awt.tax_name,
1308 'N'
1309 FROM fnd_lookups fl,
1310 po_vendors v,
1311 po_vendor_sites_all vs,
1312 ap_invoice_payments_all invpay,
1313 ap_checks_all checks,
1314 ap_tax_codes_all atc,
1315 ap_awt_tax_rates_all awt,
1316 fnd_document_sequences seq,
1317 ap_invoices_all inv,
1318 ap_invoice_lines_all line,
1319 ap_invoice_distributions_all dist,
1320 (SELECT distinct person_id
1321 ,national_identifier
1322 FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
1323 WHERE vs.country = fl.lookup_code(+)
1324 AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
1325 AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
1326 AND nvl(v.employee_id,-99) = papf.person_id (+)
1327 AND inv.vendor_id = v.vendor_id
1328 AND v.vendor_id = vs.vendor_id
1329 and vs.vendor_site_id = inv.vendor_site_id
1330 -- bug 8551359 - start
1331 --AND vs.tax_reporting_site_flag = 'Y'
1332 AND exists (select 'x'
1333 from po_vendor_sites_all
1334 where vendor_id = v.vendor_id
1335 and tax_reporting_site_flag = 'Y'
1336 and org_id = p_rep_site_ou)
1337 -- bug 8551359 - end
1338 AND nvl(inv.awt_flag,'N') = 'N'
1339 and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
1340 -- bug 5207771: Removed org_id condition
1341 --and inv.org_id = nvl(p_org_id,inv.org_id)
1342 and inv.invoice_id = line.invoice_id
1343 and dist.invoice_id = line.invoice_id
1344 and dist.invoice_line_number = line.line_number
1345 ---and inv.cancelled_date is null -- Bug 2228008
1346 AND dist.parent_reversal_id is null
1347 -- bug 8496890
1348 /*
1349 AND not exists ( select 1
1350 from ap_invoice_distributions dist1, gl_period_statuses gl
1351 where gl.application_id = 101
1352 and dist1.invoice_id = inv.invoice_id
1353 and dist1.parent_reversal_id = dist.invoice_distribution_id
1354 and gl.ledger_id = dist1.set_of_books_id
1355 and dist.accounting_date between gl.start_date and gl.end_date
1356 and dist1.accounting_date <= gl.end_date )
1357 */
1358 AND not exists ( select 1
1359 from ap_invoice_distributions dist1
1360 where dist1.invoice_id = inv.invoice_id
1361 and dist1.parent_reversal_id = dist.invoice_distribution_id
1362 and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
1363 and fnd_date.canonical_to_date(P_Date_To)
1364 )
1365 -- bug 8496890
1366 AND inv.invoice_id = invpay.invoice_id
1367 AND ( invpay.posted_flag in ('Y','P')
1368 or invpay.cash_posted_flag in ('Y','P')
1369 or invpay.accrual_posted_flag in ('Y','P'))
1370 AND invpay.check_id = checks.check_id
1371 AND checks.void_date is null
1372 AND trunc(invpay.accounting_date,'DD')
1373 between
1374 nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
1375 and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
1376 AND dist.awt_invoice_payment_id = invpay.invoice_payment_id
1377 AND (dist.line_type_lookup_code = 'AWT')
1378 AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
1379 DIST.ACCRUAL_POSTED_FLAG,
1380 DIST.CASH_POSTED_FLAG,
1381 dist.POSTED_FLAG, inv.org_id) in ('Y','P')
1382 AND dist.withholding_tax_code_id = atc.tax_id(+)
1383 AND atc.name = awt.tax_name(+)
1384 AND awt.vendor_id is null
1385 AND invpay.accounting_date between nvl(awt.start_date, invpay.accounting_date)
1386 AND nvl(awt.end_date, invpay.accounting_date)
1387 AND inv.doc_sequence_id = seq.doc_sequence_id(+)
1388 -- Bug 5207771 : Added to remove the duplicates WH lines
1389 AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
1390 OR (dist.awt_tax_rate_id is NULL) )
1391 AND EXISTS (select dist2.invoice_id
1392 from ap_invoice_distributions_all dist2
1393 where inv.invoice_id = dist2.invoice_id
1394 and dist2.line_type_lookup_code = 'AWT'
1395 and dist2.withholding_tax_code_id in
1396 -- Bug 2019586: Column name should be tax_id.
1397 -- (select tax_code_id from ap_tax_codes
1398 (select tax_id
1399 from ap_tax_codes_all
1400 where vat_transaction_type = p_wht_tax_type))
1401 AND NOT EXISTS ( select dist2.invoice_id
1402 from ap_invoice_distributions_all dist2
1403 where inv.invoice_id = dist2.invoice_id
1404 and dist2.line_type_lookup_code = 'AWT'
1405 and dist2.awt_flag <> 'A')
1406 -- BUG 3930123 : Adding one more select clause with certain modifications, to fetch
1407 -- the invoices that are cancelled in different accounting period.
1408 -- spanugan 17/12/2004
1409 -------Bug 12420102 Added below group by
1410 GROUP BY 'A',
1411 decode(nvl(v.employee_id,-1),-1,'G','A'),
1412 decode(nvl(v.employee_id,-1),-1,'01','00'),
1413 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1414 v.vendor_name,
1415 inv.awt_flag,
1416 inv.payment_status_flag,
1417 inv.invoice_id,
1418 inv.invoice_num,
1419 nvl(inv.base_amount,inv.invoice_amount) ,
1420 -- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)),
1421 --nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)),--bug14047712
1422 nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)), --bug14114547
1423 nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) ,
1424 nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0),
1425 decode(seq.name || '-' ||
1426 to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
1427 to_char(inv.doc_sequence_value)),
1428 trunc(inv.invoice_date,'DD'),
1429 invpay.invoice_payment_id,
1430 nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) ,
1431 nvl(invpay.payment_base_amount,invpay.amount),
1432 nvl(invpay.discount_taken,0),
1433 trunc(invpay.accounting_date,'DD'),
1434 dist.awt_invoice_payment_id,
1435 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
1436 substr(fl.description,1,3)),
1437 substr(vs.city,1,25), --bug12649867
1438 nvl(dist.awt_gross_amount,0)*nvl(dist.exchange_rate,1),
1439 dist.awt_flag,
1440 awt.tax_rate,
1441 awt.tax_name,
1442 'N'
1443 HAVING ((sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0) or (min(awt.tax_rate) = 0))
1444 --------bug 12420102
1445 UNION
1446 SELECT 'A',
1447 decode(nvl(v.employee_id,-1),-1,'G','A'),
1448 decode(nvl(v.employee_id,-1),-1,'01','00'),
1449 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1450 v.vendor_name,
1451 inv.awt_flag,
1452 inv.payment_status_flag,
1453 inv.invoice_id,
1454 inv.invoice_num,
1455 nvl(inv.base_amount,inv.invoice_amount) invoice_amount,
1456 -- nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID),0) net_amount,
1457 --bug10384931 nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
1458 --bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount) net_amount, --bug10384931
1459 -- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)) net_amount, --bug11891899
1460 --nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)) net_amount,--bug14047712
1461 nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)) net_amount, --bug14114547
1462 nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) prepaid_amount,
1463 nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) invoice_withheld_amount,
1464 decode(seq.name || '-' ||
1465 to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
1466 to_char(inv.doc_sequence_value)),
1467 trunc(inv.invoice_date,'DD'),
1468 invpay.invoice_payment_id,
1469 nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) payments_count,
1470 nvl(invpay.payment_base_amount,invpay.amount),
1471 nvl(invpay.discount_taken,0),
1472 trunc(invpay.accounting_date,'DD'),
1473 dist.awt_invoice_payment_id,
1474 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
1475 substr(fl.description,1,3)),
1476 substr(vs.city,1,25), --bug12649867
1477 --bug11891899 (nvl(dist.awt_gross_amount,0)) wht_net_amount,
1478 (nvl(dist.awt_gross_amount,0)*nvl(dist.exchange_rate,1)) wht_net_amount, --bug11891899
1479 --bug 12420102 (nvl(dist.base_amount,dist.amount)),
1480 sum(nvl(dist.base_amount,dist.amount)), --bug 12420102
1481 dist.awt_flag dist_awt_flag, -- bug 8709676
1482 awt.tax_rate,
1483 awt.tax_name,
1484 'Y'
1485 FROM fnd_lookups fl,
1486 po_vendors v,
1487 po_vendor_sites_all vs,
1488 ap_invoice_payments_all invpay,
1489 ap_checks_all checks,
1490 ap_tax_codes_all atc,
1491 ap_awt_tax_rates_all awt,
1492 fnd_document_sequences seq,
1493 ap_invoices_all inv,
1494 ap_invoice_lines_all line,
1495 ap_invoice_distributions_all dist,
1496 (SELECT distinct person_id
1497 ,national_identifier
1498 FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
1499 WHERE vs.country = fl.lookup_code(+)
1500 AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
1501 AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
1502 AND nvl(v.employee_id,-99) = papf.person_id (+)
1503 AND inv.vendor_id = v.vendor_id
1504 AND v.vendor_id = vs.vendor_id
1505 and vs.vendor_site_id = inv.vendor_site_id
1506 -- bug 8551359 - start
1507 --AND vs.tax_reporting_site_flag = 'Y'
1508 AND exists (select 'x'
1509 from po_vendor_sites_all
1510 where vendor_id = v.vendor_id
1511 and tax_reporting_site_flag = 'Y'
1512 and org_id = p_rep_site_ou)
1513 -- bug 8551359 - end
1514 AND nvl(inv.awt_flag,'N') = 'N'
1515 and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
1516 -- bug 5207771: Removed org_id condition
1517 --and inv.org_id = nvl(p_org_id,inv.org_id)
1518 and inv.invoice_id = line.invoice_id
1519 and dist.invoice_id = line.invoice_id
1520 and dist.invoice_line_number = line.line_number
1521 -- BUG 3930123 : spanugan
1522 /*AND inv.cancelled_date is not null
1523 AND
1524 (
1525 (dist.cancellation_flag is null
1526 AND dist.accounting_date < (select distinct gl.start_date
1527 from gl_period_statuses gl
1528 where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
1529 from ap_invoice_distributions_all dist1
1530 where dist1.invoice_id = inv.invoice_id
1531 and dist1.cancellation_flag = 'Y' )))
1532 OR
1533 (dist.cancellation_flag = 'Y'
1534 AND dist.accounting_date > (select distinct gl.end_date
1535 from gl_period_statuses gl
1536 where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
1537 from ap_invoice_distributions_all dist1
1538 where dist1.invoice_id = inv.invoice_id
1539 and dist1.cancellation_flag is null )))
1540 )
1541 -- END
1542 */
1543 AND dist.parent_reversal_id is not null
1544 -- bug 8496890
1545 /*
1546 AND dist.accounting_date > (select distinct gl.end_date
1547 from ap_invoice_distributions dist1, gl_period_statuses gl
1548 where gl.application_id = 101
1549 and dist1.invoice_id = inv.invoice_id
1550 and dist.parent_reversal_id = dist1.invoice_distribution_id
1551 and gl.ledger_id = dist1.set_of_books_id
1552 and dist1.accounting_date between gl.start_date and gl.end_date)
1553 */
1554 AND not exists (select 1 from ap_invoice_distributions dist1
1555 where dist1.invoice_id = inv.invoice_id
1556 and dist.parent_reversal_id = dist1.invoice_distribution_id
1557 and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
1558 and fnd_date.canonical_to_date(P_Date_To))
1559 -- bug 8496890
1560 AND inv.invoice_id = invpay.invoice_id
1561 AND ( invpay.posted_flag in ('Y','P')
1562 or invpay.cash_posted_flag in ('Y','P')
1563 or invpay.accrual_posted_flag in ('Y','P'))
1564 AND invpay.check_id = checks.check_id
1565 AND checks.void_date is null
1566 AND trunc(invpay.accounting_date,'DD')
1567 between
1568 nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
1569 and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
1570 AND dist.awt_invoice_payment_id = invpay.invoice_payment_id
1571 AND (dist.line_type_lookup_code = 'AWT')
1572 AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
1573 DIST.ACCRUAL_POSTED_FLAG,
1574 DIST.CASH_POSTED_FLAG,
1575 dist.POSTED_FLAG, inv.org_id) in ('Y','P')
1576 AND dist.withholding_tax_code_id = atc.tax_id(+)
1577 AND atc.name = awt.tax_name(+)
1578 AND awt.vendor_id is null
1579 -- Bug 5207771 : Added to remove the duplicates WH lines
1580 AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
1581 OR (dist.awt_tax_rate_id is NULL) )
1582 AND invpay.accounting_date between nvl(awt.start_date, invpay.accounting_date)
1583 AND nvl(awt.end_date, invpay.accounting_date)
1584 AND inv.doc_sequence_id = seq.doc_sequence_id(+)
1585 AND EXISTS (select dist2.invoice_id
1586 from ap_invoice_distributions_all dist2
1587 where inv.invoice_id = dist2.invoice_id
1588 and dist2.line_type_lookup_code = 'AWT'
1589 and dist2.withholding_tax_code_id in
1590 -- Bug 2019586: Column name should be tax_id.
1591 -- (select tax_code_id from ap_tax_codes
1592 (select tax_id
1593 from ap_tax_codes_all
1594 where vat_transaction_type = p_wht_tax_type))
1595 AND NOT EXISTS ( select dist2.invoice_id
1596 from ap_invoice_distributions_all dist2
1597 where inv.invoice_id = dist2.invoice_id
1598 and dist2.line_type_lookup_code = 'AWT'
1599 and dist2.awt_flag <> 'A')
1600 ----------bug 12420102 Added below group by
1601 GROUP BY 'A',
1602 decode(nvl(v.employee_id,-1),-1,'G','A'),
1603 decode(nvl(v.employee_id,-1),-1,'01','00'),
1604 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1605 v.vendor_name,
1606 inv.awt_flag,
1607 inv.payment_status_flag,
1608 inv.invoice_id,
1609 inv.invoice_num,
1610 nvl(inv.base_amount,inv.invoice_amount) ,
1611 --nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)),
1612 --nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)),--bug14047712
1613 nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)), --bug14114547
1614 nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) ,
1615 nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0),
1616 decode(seq.name || '-' ||
1617 to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
1618 to_char(inv.doc_sequence_value)),
1619 trunc(inv.invoice_date,'DD'),
1620 invpay.invoice_payment_id,
1621 nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) ,
1622 nvl(invpay.payment_base_amount,invpay.amount),
1623 nvl(invpay.discount_taken,0),
1624 trunc(invpay.accounting_date,'DD'),
1625 dist.awt_invoice_payment_id,
1626 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
1627 substr(fl.description,1,3)),
1628 substr(vs.city,1,25), --bug12649867
1629 nvl(dist.awt_gross_amount,0)*nvl(dist.exchange_rate,1),
1630 dist.awt_flag,
1631 awt.tax_rate,
1632 awt.tax_name,
1633 'Y'
1634 HAVING ((sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0) or (min(awt.tax_rate) = 0))
1635 ---------bug 12420102
1636
1637 UNION
1638 SELECT 'M',
1639 decode(nvl(v.employee_id,-1),-1,'G','A'),
1640 decode(nvl(v.employee_id,-1),-1,'01','00'),
1641 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1642 v.vendor_name,
1643 inv.awt_flag,
1644 inv.payment_status_flag,
1645 inv.invoice_id,
1646 inv.invoice_num,
1647 nvl(inv.base_amount,inv.invoice_amount) invoice_amount,
1648 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) net_amount,
1649 --bug10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
1650 --bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount) net_amount, --bug10384931
1651 -- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)) net_amount, --bug11891899
1652 --nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)) net_amount,--bug14047712
1653 nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)) net_amount, --bug14114547
1654 nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) prepaid_amount,
1655 nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) invoice_withheld_amount,
1656 decode(seq.name || '-' ||
1657 to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
1658 to_char(inv.doc_sequence_value)),
1659 trunc(inv.invoice_date,'DD'),
1660 invpay.invoice_payment_id,
1661 nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) payments_count,
1662 nvl(invpay.payment_base_amount,invpay.amount),
1663 nvl(invpay.discount_taken,0),
1664 trunc(invpay.accounting_date,'DD'),
1665 0,
1666 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
1667 substr(fl.description,1,3)),
1668 substr(vs.city,1,25), --bug12649867
1669 0,
1670 sum(nvl(dist.base_amount,dist.amount)),
1671 dist.awt_flag dist_awt_flag, --bug 8709676
1672 awt.tax_rate,
1673 awt.tax_name,
1674 'N'
1675 FROM fnd_lookups fl,
1676 po_vendors v,
1677 po_vendor_sites_all vs,
1678 ap_invoice_payments_all invpay,
1679 ap_checks_all checks,
1680 ap_tax_codes_all atc,
1681 ap_awt_tax_rates_all awt,
1682 fnd_document_sequences seq,
1683 ap_invoices_all inv,
1684 ap_invoice_lines_all line,
1685 ap_invoice_distributions_all dist,
1686 (SELECT distinct person_id
1687 ,national_identifier
1688 FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
1689 WHERE vs.country = fl.lookup_code(+)
1690 AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
1691 AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
1692 AND nvl(v.employee_id,-99) = papf.person_id (+)
1693 AND inv.vendor_id = v.vendor_id
1694 AND v.vendor_id = vs.vendor_id
1695 and vs.vendor_site_id = inv.vendor_site_id
1696 -- bug 8551359 - start
1697 --AND vs.tax_reporting_site_flag = 'Y'
1698 AND exists (select 'x'
1699 from po_vendor_sites_all
1700 where vendor_id = v.vendor_id
1701 and tax_reporting_site_flag = 'Y'
1702 and org_id = p_rep_site_ou)
1703 -- bug 8551359 - end
1704 and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
1705 -- bug 5207771: Removed org_id condition
1706 --and inv.org_id = nvl(p_org_id,inv.org_id)
1707 and inv.invoice_id = line.invoice_id
1708 and dist.invoice_id = line.invoice_id
1709 and dist.invoice_line_number = line.line_number
1710 ---and inv.cancelled_date is null -- Bug 2228008
1711 AND dist.parent_reversal_id is null
1712 -- bug 8496890
1713 /*
1714 AND not exists ( select 1
1715 from ap_invoice_distributions dist1, gl_period_statuses gl
1716 where gl.application_id = 101
1717 and dist1.invoice_id = inv.invoice_id
1718 and dist1.parent_reversal_id = dist.invoice_distribution_id
1719 and gl.ledger_id = dist1.set_of_books_id
1720 and dist.accounting_date between gl.start_date and gl.end_date
1721 and dist1.accounting_date <= gl.end_date )
1722 */
1723 AND not exists ( select 1
1724 from ap_invoice_distributions dist1
1725 where dist1.invoice_id = inv.invoice_id
1726 and dist1.parent_reversal_id = dist.invoice_distribution_id
1727 and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
1728 and fnd_date.canonical_to_date(P_Date_To)
1729 )
1730 -- bug 8496890
1731 AND inv.invoice_id = invpay.invoice_id
1732 AND ( invpay.posted_flag in ('Y','P')
1733 or invpay.cash_posted_flag in ('Y','P')
1734 or invpay.accrual_posted_flag in ('Y','P'))
1735 AND invpay.check_id = checks.check_id
1736 AND checks.void_date is null
1737 AND trunc(invpay.accounting_date,'DD')
1738 between
1739 nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
1740 and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
1741 AND (dist.line_type_lookup_code = 'AWT')
1742 AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
1743 DIST.ACCRUAL_POSTED_FLAG,
1744 DIST.CASH_POSTED_FLAG,
1745 dist.POSTED_FLAG, inv.org_id) in ('Y','P')
1746 AND dist.withholding_tax_code_id = atc.tax_id(+)
1747 AND atc.name = awt.tax_name(+)
1748 AND awt.vendor_id is null
1749 -- Bug 5207771 : Added to remove the duplicates WH lines
1750 AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
1751 OR (dist.awt_tax_rate_id is NULL) )
1752 AND invpay.accounting_date between nvl(awt.start_date, invpay.accounting_date)
1753 AND nvl(awt.end_date, invpay.accounting_date)
1754 AND inv.doc_sequence_id = seq.doc_sequence_id(+)
1755 AND EXISTS (select dist2.invoice_id
1756 from ap_invoice_distributions_all dist2
1757 where inv.invoice_id = dist2.invoice_id
1758 and dist2.line_type_lookup_code = 'AWT'
1759 and dist2.withholding_tax_code_id in
1760 -- Bug 2019586: Column name should be tax_id.
1761 -- (select tax_code_id from ap_tax_codes
1762 (select tax_id
1763 from ap_tax_codes_all
1764 where vat_transaction_type = p_wht_tax_type))
1765 AND EXISTS ( select dist2.invoice_id
1766 from ap_invoice_distributions_all dist2
1767 where inv.invoice_id = dist2.invoice_id
1768 and dist2.line_type_lookup_code = 'AWT'
1769 and dist2.awt_flag <> 'A')
1770 GROUP BY 'M',
1771 decode(nvl(v.employee_id,-1),-1,'G','A'),
1772 decode(nvl(v.employee_id,-1),-1,'01','00'),
1773 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1774 v.vendor_name,
1775 inv.awt_flag,
1776 inv.payment_status_flag,
1777 inv.invoice_id,
1778 inv.invoice_num,
1779 nvl(inv.base_amount,inv.invoice_amount),
1780 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) ,
1781 --bug10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) ,
1782 --bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount), --bug10384931
1783 -- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)), --bug11891899
1784 --nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)),--bug14047712
1785 nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)), --bug14114547
1786 nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) ,
1787 nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) ,
1788 decode(seq.name || '-' ||
1789 to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
1790 to_char(inv.doc_sequence_value)),
1791 trunc(inv.invoice_date,'DD'),
1792 invpay.invoice_payment_id,
1793 nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0),
1794 nvl(invpay.payment_base_amount,invpay.amount),
1795 nvl(invpay.discount_taken,0),
1796 trunc(invpay.accounting_date,'DD'),
1797 0,
1798 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
1799 substr(fl.description,1,3)),
1800 substr(vs.city,1,25), --bug12649867
1801 0,
1802 dist.awt_flag, --bug 8709676
1803 awt.tax_rate,
1804 awt.tax_name,
1805 'N'
1806 HAVING ((sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0) or (min(awt.tax_rate) = 0))
1807 -- Bug 1212074
1808 -- BUG 3930123 : Adding one more select clause with certain modifications, to fetch
1809 -- the invoices that are cancelled in different accounting period.
1810 -- spanugan 17/12/2004
1811 UNION
1812 SELECT 'M',
1813 decode(nvl(v.employee_id,-1),-1,'G','A'),
1814 decode(nvl(v.employee_id,-1),-1,'01','00'),
1815 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1816 v.vendor_name,
1817 inv.awt_flag,
1818 inv.payment_status_flag,
1819 inv.invoice_id,
1820 inv.invoice_num,
1821 nvl(inv.base_amount,inv.invoice_amount) invoice_amount,
1822 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) net_amount,
1823 --bug10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
1824 --bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount) net_amount, --bug10384931
1825 -- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)) net_amount, --bug11891899
1826 --nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)) net_amount,--bug14047712
1827 nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)) net_amount, --bug14114547
1828 nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) prepaid_amount,
1829 nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) invoice_withheld_amount,
1830 decode(seq.name || '-' ||
1831 to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
1832 to_char(inv.doc_sequence_value)),
1833 trunc(inv.invoice_date,'DD'),
1834 invpay.invoice_payment_id,
1835 nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) payments_count,
1836 nvl(invpay.payment_base_amount,invpay.amount),
1837 nvl(invpay.discount_taken,0),
1838 trunc(invpay.accounting_date,'DD'),
1839 0,
1840 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
1841 substr(fl.description,1,3)),
1842 substr(vs.city,1,25), --bug12649867
1843 0,
1844 sum(nvl(dist.base_amount,dist.amount)),
1845 dist.awt_flag dist_awt_flag, --bug 8709676
1846 awt.tax_rate,
1847 awt.tax_name,
1848 'Y'
1849 FROM fnd_lookups fl,
1850 po_vendors v,
1851 po_vendor_sites_all vs,
1852 ap_invoice_payments_all invpay,
1853 ap_checks_all checks,
1854 ap_tax_codes_all atc,
1855 ap_awt_tax_rates_all awt,
1856 fnd_document_sequences seq,
1857 ap_invoices_all inv,
1858 ap_invoice_lines_all line,
1859 ap_invoice_distributions_all dist,
1860 (SELECT distinct person_id
1861 ,national_identifier
1862 FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
1863 WHERE vs.country = fl.lookup_code(+)
1864 AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
1865 AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
1866 AND nvl(v.employee_id,-99) = papf.person_id (+)
1867 AND inv.vendor_id = v.vendor_id
1868 AND v.vendor_id = vs.vendor_id
1869 and vs.vendor_site_id = inv.vendor_site_id
1870 -- bug 8551359 - start
1871 --AND vs.tax_reporting_site_flag = 'Y'
1872 AND exists (select 'x'
1873 from po_vendor_sites_all
1874 where vendor_id = v.vendor_id
1875 and tax_reporting_site_flag = 'Y'
1876 and org_id = p_rep_site_ou)
1877 -- bug 8551359 - end
1878 and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
1879 -- bug 5207771: Removed org_id condition
1880 --and inv.org_id = nvl(p_org_id,inv.org_id)
1881 and inv.invoice_id = line.invoice_id
1882 and dist.invoice_id = line.invoice_id
1883 and dist.invoice_line_number = line.line_number
1884 -- BUG 3930123 : spanugan
1885 /*AND inv.cancelled_date is not null
1886 AND
1887 (
1888 (dist.cancellation_flag is null
1889 AND dist.accounting_date < (select distinct gl.start_date
1890 from gl_period_statuses gl
1891 where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
1892 from ap_invoice_distributions_all dist1
1893 where dist1.invoice_id = inv.invoice_id
1894 and dist1.cancellation_flag = 'Y' )))
1895 OR
1896 (dist.cancellation_flag = 'Y'
1897 AND dist.accounting_date > (select distinct gl.end_date
1898 from gl_period_statuses gl
1899 where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
1900 from ap_invoice_distributions_all dist1
1901 where dist1.invoice_id = inv.invoice_id
1902 and dist1.cancellation_flag is null )))
1903 )
1904 -- END
1905 */
1906 AND dist.parent_reversal_id is not null
1907 -- bug 8496890
1908 /*
1909 AND dist.accounting_date > (select distinct gl.end_date
1910 from ap_invoice_distributions dist1, gl_period_statuses gl
1911 where gl.application_id = 101
1912 and dist1.invoice_id = inv.invoice_id
1913 and dist.parent_reversal_id = dist1.invoice_distribution_id
1914 and gl.ledger_id = dist1.set_of_books_id
1915 and dist1.accounting_date between gl.start_date and gl.end_date)
1916 */
1917 AND not exists (select 1 from ap_invoice_distributions dist1
1918 where dist1.invoice_id = inv.invoice_id
1919 and dist.parent_reversal_id = dist1.invoice_distribution_id
1920 and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
1921 and fnd_date.canonical_to_date(P_Date_To))
1922 -- bug 8496890
1923 AND inv.invoice_id = invpay.invoice_id
1924 AND ( invpay.posted_flag in ('Y','P')
1925 or invpay.cash_posted_flag in ('Y','P')
1926 or invpay.accrual_posted_flag in ('Y','P'))
1927 AND invpay.check_id = checks.check_id
1928 AND checks.void_date is null
1929 AND trunc(invpay.accounting_date,'DD')
1930 between
1931 nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
1932 and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
1933 AND (dist.line_type_lookup_code = 'AWT')
1934 AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
1935 DIST.ACCRUAL_POSTED_FLAG,
1936 DIST.CASH_POSTED_FLAG,
1937 dist.POSTED_FLAG, inv.org_id) in ('Y','P')
1938 AND dist.withholding_tax_code_id = atc.tax_id(+)
1939 AND atc.name = awt.tax_name(+)
1940 AND awt.vendor_id is null
1941 -- Bug 5207771 : Added to remove the duplicates WH lines
1942 AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
1943 OR (dist.awt_tax_rate_id is NULL) )
1944 AND invpay.accounting_date between nvl(awt.start_date, invpay.accounting_date)
1945 AND nvl(awt.end_date, invpay.accounting_date)
1946 AND inv.doc_sequence_id = seq.doc_sequence_id(+)
1947 AND EXISTS (select dist2.invoice_id
1948 from ap_invoice_distributions_all dist2
1949 where inv.invoice_id = dist2.invoice_id
1950 and dist2.line_type_lookup_code = 'AWT'
1951 and dist2.withholding_tax_code_id in
1952 -- Bug 2019586: Column name should be tax_id.
1953 -- (select tax_code_id from ap_tax_codes
1954 (select tax_id
1955 from ap_tax_codes_all
1956 where vat_transaction_type = p_wht_tax_type))
1957 AND EXISTS ( select dist2.invoice_id
1958 from ap_invoice_distributions_all dist2
1959 where inv.invoice_id = dist2.invoice_id
1960 and dist2.line_type_lookup_code = 'AWT'
1961 and dist2.awt_flag <> 'A')
1962 GROUP BY 'M',
1963 decode(nvl(v.employee_id,-1),-1,'G','A'),
1964 decode(nvl(v.employee_id,-1),-1,'01','00'),
1965 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1966 v.vendor_name,
1967 inv.awt_flag,
1968 inv.payment_status_flag,
1969 inv.invoice_id,
1970 inv.invoice_num,
1971 nvl(inv.base_amount,inv.invoice_amount),
1972 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0),
1973 --bug10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0),
1974 --bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount), --bug10384931
1975 -- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)), --bug11891899
1976 --nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)),--bug14047712
1977 nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)), --bug14114547
1978 nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) ,
1979 nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) ,
1980 decode(seq.name || '-' ||
1981 to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
1982 to_char(inv.doc_sequence_value)),
1983 trunc(inv.invoice_date,'DD'),
1984 invpay.invoice_payment_id,
1985 nvl(je_es_whtax.get_payments_count(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0),
1986 nvl(invpay.payment_base_amount,invpay.amount),
1987 nvl(invpay.discount_taken,0),
1988 trunc(invpay.accounting_date,'DD'),
1989 0,
1990 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
1991 substr(fl.description,1,3)),
1992 substr(vs.city,1,25), --bug12649867
1993 0,
1994 dist.awt_flag, --bug 8709676
1995 awt.tax_rate,
1996 awt.tax_name,
1997 'Y'
1998 HAVING ((sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0) or (min(awt.tax_rate) = 0));
1999 -- Bug 1212074
2000 --
2001 -- Detailed APPROVED transactions. This is used for Detail and Summary
2002 -- transactions extract Hard Copy Report.
2003 --
2004 CURSOR detail_approve IS
2005 SELECT decode(nvl(v.employee_id,-1),-1,'G','A'),
2006 decode(nvl(v.employee_id,-1),-1,'01','00'),
2007 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
2008 substr(v.vendor_name,1,80), -- AP UTF8 Changes 2398166
2009 nvl(inv.base_amount,inv.invoice_amount),
2010 decode(seq.name || '-' || to_char(inv.doc_sequence_value),'-',null,
2011 seq.name || '-' || to_char(inv.doc_sequence_value)),
2012 inv.invoice_id,
2013 inv.invoice_num,
2014 trunc(inv.invoice_date,'DD'),
2015 trunc(dist.accounting_date,'DD'),
2016 dist.awt_flag, -- bug 8709676
2017 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) net_amount,
2018 -- bug10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
2019 -- bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount) net_amount, --bug10384931
2020 -- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)) net_amount, --bug11891899
2021 --nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)) net_amount, --bug14047712
2022 nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)) net_amount, --bug14114547
2023 sum(nvl(dist.base_amount,dist.amount)) withholding_tax_amount,
2024 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3)),
2025 substr(vs.city,1,25), --bug12649867
2026 awt.tax_rate,
2027 awt.tax_name,
2028 'N'
2029 FROM fnd_lookups fl,
2030 po_vendors v,
2031 po_vendor_sites_all vs,
2032 ap_tax_codes_all atc,
2033 ap_awt_tax_rates_all awt,
2034 fnd_document_sequences seq,
2035 ap_invoices_all inv,
2036 ap_invoice_lines_all line,
2037 ap_invoice_distributions_all dist,
2038 (SELECT distinct person_id
2039 ,national_identifier
2040 FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
2041 WHERE vs.country = fl.lookup_code(+)
2042 AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
2043 AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
2044 AND nvl(v.employee_id,-99) = papf.person_id (+)
2045 AND inv.vendor_id = v.vendor_id
2046 AND v.vendor_id = vs.vendor_id
2047 and vs.vendor_site_id = inv.vendor_site_id
2048 -- bug 8551359 - start
2049 --AND vs.tax_reporting_site_flag = 'Y'
2050 AND exists (select 'x'
2051 from po_vendor_sites_all
2052 where vendor_id = v.vendor_id
2053 and tax_reporting_site_flag = 'Y'
2054 and org_id = p_rep_site_ou)
2055 -- bug 8551359 - end
2056 and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
2057 -- bug 5207771: Removed org_id condition
2058 --and inv.org_id = nvl(p_org_id,inv.org_id)
2059 and inv.invoice_id = line.invoice_id
2060 and dist.invoice_id = line.invoice_id
2061 and dist.invoice_line_number = line.line_number
2062 ---and inv.cancelled_date is null -- Bug 2228008
2063 AND dist.parent_reversal_id is null
2064 -- bug 8496890
2065 /*
2066 AND not exists ( select 1
2067 from ap_invoice_distributions dist1, gl_period_statuses gl
2068 where gl.application_id = 101
2069 and dist1.invoice_id = inv.invoice_id
2070 and dist1.parent_reversal_id = dist.invoice_distribution_id
2071 and gl.ledger_id = dist1.set_of_books_id
2072 and dist.accounting_date between gl.start_date and gl.end_date
2073 and dist1.accounting_date <= gl.end_date )
2074 */
2075 AND not exists ( select 1
2076 from ap_invoice_distributions dist1
2077 where dist1.invoice_id = inv.invoice_id
2078 and dist1.parent_reversal_id = dist.invoice_distribution_id
2079 and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
2080 and fnd_date.canonical_to_date(P_Date_To)
2081 )
2082 -- bug 8496890
2083 AND trunc(dist.accounting_date,'DD')
2084 between fnd_date.canonical_to_date(P_Date_From)
2085 and fnd_date.canonical_to_date(P_Date_To)
2086 AND dist.line_type_lookup_code = 'AWT'
2087 AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
2088 DIST.ACCRUAL_POSTED_FLAG,
2089 DIST.CASH_POSTED_FLAG,
2090 dist.POSTED_FLAG, inv.org_id) in ('Y','P')
2091 AND dist.withholding_tax_code_id = atc.tax_id(+)
2092 AND atc.name = awt.tax_name(+)
2093 /*AND dist.accounting_date
2094 between nvl(awt.start_date, dist.accounting_date)
2095 and nvl(awt.end_date, dist.accounting_date)*/ -- bug 16343633
2096 AND awt.vendor_id is null /* Ignore any Vendor Lines */
2097 -- Bug 5207771 : Added to remove the duplicates WH lines
2098 AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
2099 OR (dist.awt_tax_rate_id is NULL) )
2100 AND inv.doc_sequence_id = seq.doc_sequence_id(+)
2101 -- Ignore any invoices which do not have 'AWT' distribution lines
2102 AND EXISTS ( select dist2.invoice_id
2103 from ap_invoice_distributions_all dist2
2104 where inv.invoice_id = dist2.invoice_id
2105 and dist2.line_type_lookup_code = 'AWT'
2106 and dist2.withholding_tax_code_id in
2107 -- Bug 2019586: Column name should be tax_id.
2108 -- (select tax_code_id from ap_tax_codes
2109 (select tax_id from ap_tax_codes_all
2110 where vat_transaction_type = p_wht_tax_type))
2111 GROUP BY decode(nvl(v.employee_id,-1),-1,'G','A'),
2112 decode(nvl(v.employee_id,-1),-1,'01','00'),
2113 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
2114 substr(v.vendor_name,1,80),
2115 nvl(inv.base_amount,inv.invoice_amount),
2116 decode(seq.name || '-' || to_char(inv.doc_sequence_value),'-', null,
2117 seq.name || '-' || to_char(inv.doc_sequence_value)),
2118 inv.invoice_id,
2119 inv.invoice_num,
2120 trunc(inv.invoice_date,'DD'),
2121 trunc(dist.accounting_date,'DD'),
2122 dist.awt_flag, -- bug 8709676
2123 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) ,
2124 --bug10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) ,
2125 --bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount), --bug10384931
2126 -- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)), --bug11891899
2127 --nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)),--bug14047712
2128 nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)), --bug14114547
2129 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3)),
2130 substr(vs.city,1,25), --bug12649867
2131 awt.tax_rate,
2132 awt.tax_name,
2133 'N'
2134 HAVING ((sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0) or (min(awt.tax_rate) = 0))
2135 -- Bug 1212074
2136 -- BUG 3930123 : Adding one more select clause with certain modifications, to fetch
2137 -- the invoices that are cancelled in different accounting period.
2138 -- spanugan 17/12/2004
2139 UNION
2140 SELECT decode(nvl(v.employee_id,-1),-1,'G','A'),
2141 decode(nvl(v.employee_id,-1),-1,'01','00'),
2142 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
2143 substr(v.vendor_name,1,80), -- AP UTF8 Changes 2398166
2144 nvl(inv.base_amount,inv.invoice_amount),
2145 decode(seq.name || '-' || to_char(inv.doc_sequence_value),'-',null,
2146 seq.name || '-' || to_char(inv.doc_sequence_value)),
2147 inv.invoice_id,
2148 inv.invoice_num,
2149 trunc(inv.invoice_date,'DD'),
2150 trunc(dist.accounting_date,'DD'),
2151 dist.awt_flag, -- bug 8709676
2152 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) net_amount,
2153 --bug10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
2154 --bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount) net_amount, --bug10384931
2155 -- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)) net_amount, --bug11891899
2156 --nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)) net_amount,--bug14047712
2157 nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id, date_from, date_to),0)) net_amount, --bug14114547
2158 sum(nvl(dist.base_amount,dist.amount)) withholding_tax_amount,
2159 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3)),
2160 substr(vs.city,1,25), --bug12649867
2161 awt.tax_rate,
2162 awt.tax_name,
2163 'Y'
2164 FROM fnd_lookups fl,
2165 po_vendors v,
2166 po_vendor_sites_all vs,
2167 ap_tax_codes_all atc,
2168 ap_awt_tax_rates_all awt,
2169 fnd_document_sequences seq,
2170 ap_invoices_all inv,
2171 ap_invoice_lines_all line,
2172 ap_invoice_distributions_all dist,
2173 (SELECT distinct person_id
2174 ,national_identifier
2175 FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
2176 WHERE vs.country = fl.lookup_code(+)
2177 AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
2178 AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
2179 AND nvl(v.employee_id,-99) = papf.person_id (+)
2180 AND inv.vendor_id = v.vendor_id
2181 AND v.vendor_id = vs.vendor_id
2182 and vs.vendor_site_id = inv.vendor_site_id
2183 -- bug 8551359 - start
2184 --AND vs.tax_reporting_site_flag = 'Y'
2185 AND exists (select 'x'
2186 from po_vendor_sites_all
2187 where vendor_id = v.vendor_id
2188 and tax_reporting_site_flag = 'Y'
2189 and org_id = p_rep_site_ou)
2190 -- bug 8551359 - end
2191 and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
2192 -- bug 5207771: Removed org_id condition
2193 --and inv.org_id = nvl(p_org_id,inv.org_id)
2194 and inv.invoice_id = line.invoice_id
2195 and dist.invoice_id = line.invoice_id
2196 and dist.invoice_line_number = line.line_number
2197 -- BUG 3930123 : spanugan
2198 /*AND inv.cancelled_date is not null
2199 AND
2200 (
2201 (dist.cancellation_flag is null
2202 AND dist.accounting_date < (select distinct gl.start_date
2203 from gl_period_statuses gl
2204 where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
2205 from ap_invoice_distributions_all dist1
2206 where dist1.invoice_id = inv.invoice_id
2207 and dist1.cancellation_flag = 'Y' )))
2208 OR
2209 (dist.cancellation_flag = 'Y'
2210 AND dist.accounting_date > (select distinct gl.end_date
2211 from gl_period_statuses gl
2212 where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
2213 from ap_invoice_distributions_all dist1
2214 where dist1.invoice_id = inv.invoice_id
2215 and dist1.cancellation_flag is null )))
2216 )
2217 -- END
2218 */
2219 AND dist.parent_reversal_id is not null
2220 -- bug 8496890
2221 /*
2222 AND dist.accounting_date > (select distinct gl.end_date
2223 from ap_invoice_distributions dist1, gl_period_statuses gl
2224 where gl.application_id = 101
2225 and dist1.invoice_id = inv.invoice_id
2226 and dist.parent_reversal_id = dist1.invoice_distribution_id
2227 and gl.ledger_id = dist1.set_of_books_id
2228 and dist1.accounting_date between gl.start_date and gl.end_date)
2229 */
2230 AND not exists (select 1 from ap_invoice_distributions dist1
2231 where dist1.invoice_id = inv.invoice_id
2232 and dist.parent_reversal_id = dist1.invoice_distribution_id
2233 and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
2234 and fnd_date.canonical_to_date(P_Date_To))
2235 -- bug 8496890
2236 AND trunc(dist.accounting_date,'DD')
2237 between fnd_date.canonical_to_date(P_Date_From)
2238 and fnd_date.canonical_to_date(P_Date_To)
2239 AND dist.line_type_lookup_code = 'AWT'
2240 AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
2241 DIST.ACCRUAL_POSTED_FLAG,
2242 DIST.CASH_POSTED_FLAG,
2243 dist.POSTED_FLAG, inv.org_id) in ('Y','P')
2244 AND dist.withholding_tax_code_id = atc.tax_id(+)
2245 AND atc.name = awt.tax_name(+)
2246 /*AND dist.accounting_date
2247 between nvl(awt.start_date, dist.accounting_date)
2248 and nvl(awt.end_date, dist.accounting_date)*/ -- bug 16343633
2249 AND awt.vendor_id is null /* Ignore any Vendor Lines */
2250 -- Bug 5207771 : Added to remove the duplicates WH lines
2251 AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
2252 OR (dist.awt_tax_rate_id is NULL) )
2253 AND inv.doc_sequence_id = seq.doc_sequence_id(+)
2254 -- Ignore any invoices which do not have 'AWT' distribution lines
2255 AND EXISTS ( select dist2.invoice_id
2256 from ap_invoice_distributions_all dist2
2257 where inv.invoice_id = dist2.invoice_id
2258 and dist2.line_type_lookup_code = 'AWT'
2259 and dist2.withholding_tax_code_id in
2260 -- Bug 2019586: Column name should be tax_id.
2261 -- (select tax_code_id from ap_tax_codes
2262 (select tax_id from ap_tax_codes_all
2263 where vat_transaction_type = p_wht_tax_type))
2264 GROUP BY decode(nvl(v.employee_id,-1),-1,'G','A'),
2265 decode(nvl(v.employee_id,-1),-1,'01','00'),
2266 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
2267 substr(v.vendor_name,1,80),
2268 nvl(inv.base_amount,inv.invoice_amount),
2269 decode(seq.name || '-' || to_char(inv.doc_sequence_value),'-', null,
2270 seq.name || '-' || to_char(inv.doc_sequence_value)),
2271 inv.invoice_id,
2272 inv.invoice_num,
2273 trunc(inv.invoice_date,'DD'),
2274 trunc(dist.accounting_date,'DD'),
2275 dist.awt_flag, -- bug 8709676
2276 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) ,
2277 --bug10384931 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) ,
2278 --bug11891899 nvl(dist.awt_gross_amount,inv.invoice_amount), --bug10384931
2279 -- nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)),nvl(inv.base_amount,inv.invoice_amount)), --bug11891899
2280 --nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0)),--bug14047712
2281 nvl((dist.awt_gross_amount * nvl(inv.exchange_rate,1)), nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,date_from,date_to),0)), --bug14114547
2282 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3)),
2283 substr(vs.city,1,25), --bug12649867
2284 awt.tax_rate,
2285 awt.tax_name,
2286 'Y'
2287 HAVING ((sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0) or (min(awt.tax_rate) = 0));
2288
2289 -- Bug 1212074
2290 -- Bug 1271489: Fetch correct awt rate and name for a given invoice.
2291 PROCEDURE fetch_awt_line( p_fetch_pi_flag IN varchar2,
2292 p_fetch_invoice_id IN number,
2293 p_fetch_wht_amount IN number,
2294 date_paid1 IN DATE, -- Bug 3930123 : Spanugan 23/12/2004
2295 p_fetch_tax_rate IN OUT NOCOPY number,
2296 p_fetch_tax_name IN OUT NOCOPY varchar2,
2297 p_legal_entity_id IN number,
2298 p_org_id IN number,
2299 p_parent_reversal_flag IN VARCHAR2,
2300 p_awt_invoice_payment_id IN NUMBER DEFAULT 0) is -- Bug 12616975
2301 l_tax_code_id ap_tax_codes.tax_id%TYPE;
2302 l_invoice_num ap_invoices.invoice_num%TYPE;
2303 l_tax_name JE_ES_MODELO_190_ALL.tax_name%TYPE;
2304 l_tax_rate JE_ES_MODELO_190_ALL.tax_rate%TYPE;
2305 l_accounting_date ap_invoice_distributions_all.accounting_date%TYPE;
2306 begin
2307 begin
2308 if p_fetch_pi_flag = 'P' then
2309
2310 if p_awt_invoice_payment_id is null then -- Bug 12616975
2311
2312
2313 -- select min(dist.withholding_tax_code_id), max(invpay.accounting_date) -- Bug 12616975
2314 select min(dist.withholding_tax_code_id),(select max(accounting_date) from ap_invoice_payments_all where invoice_id=p_fetch_invoice_id)
2315 into l_tax_code_id, l_accounting_date
2316 from -- ap_invoice_payments_all invpay,
2317 ap_invoice_distributions_all dist
2318 where dist.invoice_id = p_fetch_invoice_id
2319 --and invpay.invoice_id = dist.invoice_id -- Bug 12616975 takes cross product
2320 and dist.line_type_lookup_code = 'AWT'
2321 and ((p_parent_reversal_flag = 'N' and dist.parent_reversal_id is null)
2322 OR (p_parent_reversal_flag = 'Y' and dist.parent_reversal_id is not null))
2323 and not exists ( select 1 -- Bug 12616975
2324 from ap_invoice_distributions dist1
2325 where dist1.invoice_id = p_fetch_invoice_id
2326 and dist1.parent_reversal_id = dist.invoice_distribution_id
2327 and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
2328 and fnd_date.canonical_to_date(P_Date_To)
2329 )
2330 group by withholding_tax_code_id
2331 having (sum(decode(dist.line_type_lookup_code,'AWT',
2332 nvl(dist.base_amount,dist.amount),0)) = p_fetch_wht_amount);
2333
2334 else
2335
2336 select min(dist.withholding_tax_code_id), max(invpay.accounting_date)
2337 into l_tax_code_id, l_accounting_date
2338 from ap_invoice_payments_all invpay,
2339 ap_invoice_distributions_all dist
2340 where dist.invoice_id = p_fetch_invoice_id
2341 and invpay.invoice_id = dist.invoice_id
2342 and invpay.INVOICE_PAYMENT_ID = dist.AWT_INVOICE_PAYMENT_ID -- Bug 12616975
2343 and dist.line_type_lookup_code = 'AWT'
2344 and ((p_parent_reversal_flag = 'N' and dist.parent_reversal_id is null)
2345 OR (p_parent_reversal_flag = 'Y' and dist.parent_reversal_id is not null))
2346 and dist.awt_invoice_payment_id = p_awt_invoice_payment_id -- Bug 12616975
2347 and not exists ( select 1
2348 from ap_invoice_distributions dist1
2349 where dist1.invoice_id = p_fetch_invoice_id
2350 and dist1.parent_reversal_id = dist.invoice_distribution_id
2351 and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
2352 and fnd_date.canonical_to_date(P_Date_To)
2353 )
2354 group by withholding_tax_code_id
2355 having /*((mod(count(dist.withholding_tax_code_id),2) = 1) and*/ --Bug 3930217
2356 (sum(decode(dist.line_type_lookup_code,'AWT',
2357 nvl(dist.base_amount,dist.amount),0)) = p_fetch_wht_amount);
2358 end if;
2359 else -- p_fetch_pi_flag <> 'P'
2360
2361 select min(withholding_tax_code_id), max(accounting_date)
2362 into l_tax_code_id, l_accounting_date
2363 from ap_invoice_distributions_all dist
2364 where dist.invoice_id = p_fetch_invoice_id
2365 and dist.line_type_lookup_code = 'AWT'
2366 and ((p_parent_reversal_flag = 'N' and dist.parent_reversal_id is null)
2367 OR (p_parent_reversal_flag = 'Y' and dist.parent_reversal_id is not null))
2368 and not exists ( select 1 -- Bug 12616975
2369 from ap_invoice_distributions dist1
2370 where dist1.invoice_id = p_fetch_invoice_id
2371 and dist1.parent_reversal_id = dist.invoice_distribution_id
2372 and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
2373 and fnd_date.canonical_to_date(P_Date_To)
2374 )
2375 -- Bug 5207771
2376 -- and accounting_date = date_paid1 -- Bug 3930217 : Spanugan 23/12/2004
2377 group by withholding_tax_code_id
2378 having /*((mod(count(withholding_tax_code_id),2) = 1) and*/ --Bug 3930217
2379 (sum(decode(dist.line_type_lookup_code,'AWT',
2380 nvl(dist.base_amount,amount),0)) = p_fetch_wht_amount)
2381 ;
2382 end if;
2383 exception
2384 when OTHERS then
2385 select invoice_num into l_invoice_num
2386 from ap_invoices_all
2387 where invoice_id = p_fetch_invoice_id
2388 and legal_entity_id = nvl(p_legal_entity_id, legal_entity_id);
2389 -- bug 5207771: Removed org_id condition
2390 --and inv.org_id = nvl(p_org_id,inv.org_id);
2391 dbmsmsg('Wrong number of withholding tax lines in invoice '||l_invoice_num||'.');
2392 raise bad_awt_lines;
2393 end;
2394 begin
2395
2396
2397 select awt.tax_rate, awt.tax_name
2398 into l_tax_rate, l_tax_name
2399 from ap_tax_codes_all atc, ap_awt_tax_rates_all awt
2400 where atc.name = awt.tax_name(+)
2401 and atc.tax_id = l_tax_code_id
2402 and l_accounting_date between nvl(awt.start_date,l_accounting_date)
2403 and nvl(awt.end_date,l_accounting_date)
2404 and atc.org_id = awt.org_id; -- bug 8401560
2405
2406 exception
2407 when OTHERS then
2408 select invoice_num into l_invoice_num
2409 from ap_invoices_all
2410 where invoice_id = p_fetch_invoice_id
2411 and legal_entity_id = nvl(p_legal_entity_id,legal_entity_id);
2412 -- bug 5207771: Removed org_id condition
2413 --and inv.org_id = nvl(p_org_id,inv.org_id);
2414 dbmsmsg('The tax name for withholding tax line of invoice '||l_invoice_num|| ' is an incorrect one.');
2415 raise bad_awt_lines;
2416 end;
2417 p_fetch_tax_rate := nvl(l_tax_rate,p_fetch_tax_rate);
2418 p_fetch_tax_name := nvl(l_tax_name,p_fetch_tax_name);
2419 end;
2420 BEGIN
2421 fnd_file.put_line( fnd_file.log,'Parameters :');
2422 fnd_file.put_line( fnd_file.log,'Selection Criteria : ' || p_pay_inv_sel );
2423 fnd_file.put_line( fnd_file.log,'Summary Report : ' || p_summary );
2424 fnd_file.put_line( fnd_file.log,'Date From : ' || p_date_from );
2425 fnd_file.put_line( fnd_file.log,'Date To : ' || p_date_to );
2426 fnd_file.put_line( fnd_file.log,'Tax Type : ' || p_wht_tax_type );
2427 fnd_file.put_line( fnd_file.log,'Legal Entity id : ' || p_legal_entity_id );
2428 fnd_file.put_line( fnd_file.log,'Organization id : ' || p_org_id );
2429 fnd_file.put_line( fnd_file.log,' ');
2430
2431 date_from := fnd_date.canonical_to_date(P_DATE_FROM); --bug1411547
2432 date_to := fnd_date.canonical_to_date(P_DATE_TO); --bug1411547
2433
2434
2435 -- Added for bug 5277700.
2436 SELECT COUNT(*)
2437 INTO l_le_id_count
2438 FROM je_es_modelo_190_all
2439 WHERE legal_entity_id IS NULL;
2440 IF l_le_id_count > 0 THEN
2441 je_es_mod_le_update.update_main;
2442 /* fnd_message.set_name('JE', 'JE_WHT_LEGAL_ENTITY_ID_UPG');
2443 fnd_message.set_token('TABLE', 'JE_ES_MODELO_190_ALL');
2444 l_le_id_message := fnd_message.get;
2445 errbuf := l_le_id_message;
2446 retcode := -1;
2447 RETURN;
2448 */
2449 END IF;
2450 /* Get the functional currency and precision */
2451 -- l_ledger_id :=FND_PROFILE.value('gl_set_of_bks_id');
2452
2453 SELECT p.currency_code,
2454 c.precision
2455 INTO func_curr,
2456 func_curr_precision
2457 FROM gl_ledgers p,
2458 fnd_currencies_vl c
2459 WHERE p.currency_code = c.currency_code
2460 AND p.ledger_id = (select distinct primary_ledger_id
2461 from gl_ledger_le_v
2462 where legal_entity_id = p_legal_entity_id);
2463
2464 if p_hard_copy = 'N' then
2465 /* Deal with ELECTRONIC transactions */
2466 plsqlmsg('WITHHOLDING TAX MAGNETIC REPORT - Transfer Data');
2467 del_trans_m(p_legal_entity_id => p_legal_entity_id,
2468 p_org_id => p_org_id);
2469 plsqlmsg('Deleted Existing Rows');
2470 if p_summary = 'Y' then
2471 /* Deal with SUMMARY transactions */
2472 if p_pay_inv_sel = 'P' then
2473 /* Deal with PAID transactions */
2474 countrecs := 0;
2475 plsqlmsg('Opened CURSOR detail_paid for summary paid electronic');
2476 OPEN detail_paid;
2477 LOOP
2478 FETCH detail_paid
2479 INTO wht_mode,
2480 remun_type1,
2481 sub_remun_type1,
2482 vendor_nif1,
2483 vendor_name1,
2484 inv_awt_flag,
2485 inv_payment_status_flag,
2486 invoice_id1,
2487 invoice_num1,
2488 invoice_amount,
2489 net_amount1,
2490 invoice_prepaid_amount,
2491 invoice_withheld_amount,
2492 inv_doc_seq_num1,
2493 invoice_date1,
2494 invoice_payment_id1,
2495 invoice_payments_count,
2496 paid_amount,
2497 discount_amount,
2498 date_paid1,
2499 awt_invoice_payment_id,
2500 zip_legal1,
2501 city_legal1,
2502 wht_net_amount1,
2503 withholding_tax_amount1,
2504 dist_awt_flag, --bug 8709676
2505 tax_rate1,
2506 tax_name1,
2507 l_parent_reversal_flag;
2508 EXIT WHEN detail_paid%NOTFOUND;
2509 first_record := first_record + 1;
2510 -- fnd_file.put_line(fnd_file.log,'In Magnetic');
2511 -- Retain Old data
2512 if ( first_record = 1 ) then
2513 old_remun_type := remun_type1;
2514 old_sub_remun_type := sub_remun_type1;
2515 old_vendor_nif := vendor_nif1;
2516 old_vendor_name := vendor_name1;
2517 old_zip_electronic := zip_electronic1;
2518 old_zip_legal := zip_legal1 ;
2519 end if;
2520 -- Automatic Withholding
2521 -- Withholding calculated at invoice payment time.
2522 if ( wht_mode = 'A' ) then
2523 if(nvl(inv_awt_flag,'N') = 'N') then
2524 net_amount1 := round(wht_net_amount1,func_curr_precision);
2525 withholding_tax_amount1 := round(withholding_tax_amount1,
2526 func_curr_precision);
2527 else -- if (nvl(inv_awt_flag,'N') = 'Y')
2528 if (nvl(inv_payment_status_flag,'N') = 'Y') then
2529 if (invoice_payments_count = 1 ) then
2530 net_amount1 := round(net_amount1,func_curr_precision);
2531 withholding_tax_amount1 := round(withholding_tax_amount1, func_curr_precision);
2532 elsif ( invoice_payments_count > 1 ) then
2533 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2534 withholding_tax_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* invoice_withheld_amount * -1), func_curr_precision);
2535 end if;
2536 else -- if nvl(inv_payment_status_flag,'N') = 'N'
2537 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2538 withholding_tax_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* invoice_withheld_amount * -1), func_curr_precision);
2539 end if;
2540 end if;
2541 end if;
2542 -- Manuali+Automatic Withholding
2543 -- Withholding calculated at invoice payment time.
2544 -- Withholding calculated at approval time.
2545 if ( wht_mode = 'M' ) then
2546 if (nvl(inv_payment_status_flag,'N') = 'Y') then
2547 if (invoice_payments_count = 1 ) then
2548 net_amount1 := round(net_amount1,func_curr_precision);
2549 withholding_tax_amount1 := round(withholding_tax_amount1, func_curr_precision);
2550 elsif ( invoice_payments_count > 1 ) then
2551 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2552 withholding_tax_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* invoice_withheld_amount * -1),func_curr_precision);
2553 end if; -- if invoice_payments_count =1 or >1
2554 else -- if nvl(inv_payment_status_flag,'N') = 'N'
2555 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2556 withholding_tax_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* invoice_withheld_amount * -1),func_curr_precision);
2557 end if; -- if nvl(inv_payment_status_flag,'N') = 'Y' or 'N'
2558 end if; -- if wht_mode = 'M'
2559 if ( (nvl(remun_type1,'X') = nvl(old_remun_type,'X')) AND
2560 (nvl(sub_remun_type1,'X') = nvl(old_sub_remun_type,'X')) AND
2561 (nvl(vendor_nif1,'X') = nvl(old_vendor_nif,'X')) AND
2562 (nvl(vendor_name1,'X') = nvl(old_vendor_name,'X')) AND
2563 (nvl(zip_electronic1,'X') = nvl(old_zip_electronic,'X')) AND
2564 (nvl(zip_legal1,'X') = nvl(old_zip_legal,'X')) ) then
2565 inv_net_amount1 := nvl(inv_net_amount1,0) + net_amount1;
2566 inv_wht_amount1 := nvl(inv_wht_amount1,0) + withholding_tax_amount1;
2567 else
2568 old_remun_type := remun_type1;
2569 old_sub_remun_type := sub_remun_type1;
2570 old_vendor_nif := vendor_nif1;
2571 old_vendor_name := vendor_name1;
2572 old_zip_electronic := zip_electronic1;
2573 old_zip_legal := zip_legal1 ;
2574 if sign(inv_net_amount1) = -1 then
2575 sign1 := 'N';
2576 else
2577 sign1:= '';
2578 end if;
2579 inv_wht_amount1 := abs(inv_wht_amount1);
2580 inv_net_amount1 := abs(inv_net_amount1);
2581 if ( inv_wht_amount1 <>0 ) then
2582 -- Bug 1212074: Magnetic form does not care about 0 awt.
2583 countrecs := countrecs + 1;
2584 ins_trans(
2585 legal_entity_id => p_legal_entity_id,
2586 org_id => p_org_id,
2587 conc_req_id => p_conc_req_id,
2588 remun_type => remun_type1,
2589 sub_remun_type => sub_remun_type1,
2590 vendor_nif => vendor_nif1,
2591 vendor_name => vendor_name1,
2592 invoice_id => NULL,
2593 invoice_num => NULL,
2594 inv_doc_seq_num => NULL,
2595 invoice_date => NULL,
2596 gl_date => NULL,
2597 invoice_payment_id => NULL,
2598 date_paid => NULL,
2599 net_amount => inv_net_amount1,
2600 withholding_tax_amount => inv_wht_amount1,
2601 zip_electronic => zip_electronic1,
2602 zip_legal => zip_legal1,
2603 city_legal => NULL,
2604 num_children => NULL,
2605 sign => sign1,
2606 tax_rate => NULL,
2607 tax_name => NULL,
2608 year_due => NULL);
2609 end if;
2610 inv_net_amount1 := net_amount1;
2611 inv_wht_amount1 := withholding_tax_amount1;
2612 end if;
2613 END LOOP;
2614 if ( inv_wht_amount1 <>0 AND inv_net_amount1 <> 0 ) then
2615 -- Bug 1212074: Magnetic form does not care about 0 awt.
2616 countrecs := countrecs + 1;
2617 ins_trans( legal_entity_id => p_legal_entity_id,
2618 org_id => p_org_id,
2619 conc_req_id => p_conc_req_id,
2620 remun_type => remun_type1,
2621 sub_remun_type => sub_remun_type1,
2622 vendor_nif => vendor_nif1,
2623 vendor_name => vendor_name1,
2624 invoice_id => NULL,
2625 invoice_num => NULL,
2626 inv_doc_seq_num => NULL,
2627 invoice_date => NULL,
2628 gl_date => NULL,
2629 invoice_payment_id => NULL,
2630 date_paid => NULL,
2631 net_amount => inv_net_amount1,
2632 withholding_tax_amount => inv_wht_amount1,
2633 zip_electronic => zip_electronic1,
2634 zip_legal => zip_legal1,
2635 city_legal => NULL,
2636 num_children => NULL,
2637 sign => sign1,
2638 tax_rate => NULL,
2639 tax_name => NULL,
2640 year_due => NULL);
2641 end if;
2642 plsqlmsg('Data inserted into table JE_ES_MODELO_190_ALL');
2643 CLOSE detail_paid;
2644 plsqlmsg('CURSOR Closed');
2645 plsqlmsg('Routine Successfully completed - ' || to_char(countrecs) || ' rows inserted');
2646 ELSE -- p_pay_inv_sel <> 'P'
2647 /* Deal with APPROVED transactions */
2648 countrecs := 0;
2649 OPEN sum_approve_mag;
2650 plsqlmsg('Opened CURSOR sum_approve_mag');
2651 LOOP
2652 FETCH sum_approve_mag
2653 INTO remun_type1,
2654 sub_remun_type1,
2655 vendor_nif1,
2656 vendor_name1,
2657 zip_electronic1,
2658 net_amount1,
2659 withholding_tax_amount1;
2660 EXIT WHEN sum_approve_mag%NOTFOUND;
2661 net_amount1 := round(net_amount1,func_curr_precision);
2662 withholding_tax_amount1 := round(withholding_tax_amount1,func_curr_precision);
2663 if sign(net_amount1) = -1 then
2664 sign1 := 'N';
2665 else
2666 sign1:= '';
2667 end if;
2668 withholding_tax_amount1 := abs(withholding_tax_amount1);
2669 net_amount1 := abs(net_amount1);
2670 if ( withholding_tax_amount1 <>0 ) then
2671 -- Bug 1212074: Magnetic form does not care about 0 awt.
2672 countrecs := countrecs + 1;
2673 ins_trans( legal_entity_id => p_legal_entity_id,
2674 org_id => p_org_id,
2675 conc_req_id => NULL,
2676 remun_type => remun_type1,
2677 sub_remun_type => sub_remun_type1,
2678 vendor_nif => vendor_nif1,
2679 vendor_name => vendor_name1,
2680 invoice_id => invoice_id1,
2681 invoice_num => invoice_num1,
2682 inv_doc_seq_num => inv_doc_seq_num1,
2683 invoice_date => invoice_date1,
2684 gl_date => gl_date1,
2685 invoice_payment_id => invoice_payment_id1,
2686 date_paid => date_paid1,
2687 net_amount => net_amount1,
2688 withholding_tax_amount => withholding_tax_amount1,
2689 zip_electronic => zip_electronic1,
2690 zip_legal => zip_legal1,
2691 city_legal => city_legal1,
2692 num_children => num_children1,
2693 sign => sign1,
2694 tax_rate => tax_rate1,
2695 tax_name => tax_name1,
2696 year_due => year_due1);
2697 end if;
2698 END LOOP;
2699 plsqlmsg('Data inserted into table JE_ES_MODELO_190_ALL');
2700 CLOSE sum_approve_mag;
2701 plsqlmsg('CURSOR Closed');
2702 plsqlmsg('Routine Successfully completed - ' || to_char(countrecs) || ' rows inserted');
2703 end if; -- p_pay_inv_sel = 'P' or <> 'P'
2704 else -- p_summary <> 'Y'
2705 /* We should NEVER have any DETAIL transactions */
2706 RAISE bad_parameters;
2707 end if; -- if p_summary = 'Y' or <> 'Y'
2708 else -- p_hard_copy = 'Y'
2709 /* Deal with HARD COPY transactions */
2710 plsqlmsg('WITHHOLDING TAX REPORT - Transfer Data for Hard Copy Summary');
2711 del_trans_s(p_conc_req_id => p_conc_req_id,
2712 p_legal_entity_id => p_legal_entity_id,
2713 p_org_id => p_org_id);
2714 plsqlmsg('Deleted Existing Rows');
2715 if p_summary = 'Y' then
2716 /* Deal with SUMMARY transactions */
2717 if p_pay_inv_sel = 'P' then
2718 /* Deal with PAID transactions */
2719 countrecs := 0;
2720 OPEN detail_paid;
2721 plsqlmsg('Opened CURSOR detail_paid');
2722 LOOP
2723 FETCH detail_paid
2724 INTO wht_mode,
2725 remun_type1,
2726 sub_remun_type1,
2727 vendor_nif1,
2728 vendor_name1,
2729 inv_awt_flag,
2730 inv_payment_status_flag,
2731 invoice_id1,
2732 invoice_num1,
2733 invoice_amount,
2734 net_amount1,
2735 invoice_prepaid_amount,
2736 invoice_withheld_amount,
2737 inv_doc_seq_num1,
2738 invoice_date1,
2739 invoice_payment_id1,
2740 invoice_payments_count,
2741 paid_amount,
2742 discount_amount,
2743 date_paid1,
2744 awt_invoice_payment_id,
2745 zip_legal1,
2746 city_legal1,
2747 wht_net_amount1,
2748 withholding_tax_amount1,
2749 dist_awt_flag, --bug 8709676
2750 tax_rate1,
2751 tax_name1,
2752 l_parent_reversal_flag;
2753 EXIT WHEN detail_paid%NOTFOUND;
2754 -- Automatic Withholding
2755 -- Withholding calculated at invoice payment time.
2756 if ( wht_mode = 'A' ) then
2757 if (nvl(inv_awt_flag,'N') = 'N') then
2758 net_amount1 := round(wht_net_amount1,func_curr_precision);
2759 withholding_tax_amount1 := round(withholding_tax_amount1, func_curr_precision);
2760 else -- if (nvl(inv_awt_flag,'N') = 'Y')
2761 if (nvl(inv_payment_status_flag,'N') = 'Y') then
2762 if (invoice_payments_count = 1 ) then
2763 net_amount1 := round(net_amount1,func_curr_precision);
2764 withholding_tax_amount1 := round(withholding_tax_amount1, func_curr_precision);
2765 elsif ( invoice_payments_count > 1 ) then
2766 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2767 withholding_tax_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* invoice_withheld_amount * -1),func_curr_precision);
2768 end if; -- if invoice_payments_count = 1 or > 1
2769 else -- if nvl(inv_payment_status_flag,'N') = 'N'
2770 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2771 withholding_tax_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* invoice_withheld_amount * -1),func_curr_precision);
2772 end if; -- if nvl(inv_payment_status_flag,'N') = 'Y' or 'N'
2773 end if; -- if (nvl(inv_awt_flag,'N') = 'N' or 'Y'
2774 end if; -- if wht_mode = 'A'
2775 -- Manuali+Automatic Withholding
2776 -- Withholding calculated at invoice payment time.
2777 -- Withholding calculated at approval time.
2778 if ( wht_mode = 'M' ) then
2779 if (nvl(inv_payment_status_flag,'N') = 'Y') then
2780 if (invoice_payments_count = 1 ) then
2781 net_amount1 := round(net_amount1,func_curr_precision);
2782 withholding_tax_amount1 := round(withholding_tax_amount1, func_curr_precision);
2783 elsif ( invoice_payments_count > 1 ) then
2784 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2785 withholding_tax_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* invoice_withheld_amount * -1),func_curr_precision);
2786 end if; -- if invoice_payments_count = 1 or > 1
2787 else -- if nvl(inv_payment_status_flag,'N') <> 'Y'
2788 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2789 withholding_tax_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* invoice_withheld_amount * -1),func_curr_precision);
2790 end if; -- if nvl(inv_payment_status_flag,'N') ='Y' or <> 'Y'
2791 end if; -- if wht_mode = 'M'
2792 -- fnd_file.put_line(fnd_file.log,'Net Amount: '||to_char(net_amount1));
2793 -- fnd_file.put_line(fnd_file.log,'WHT Amount: '||to_char(withholding_tax_amount1));
2794 if ( withholding_tax_amount1 <>0 )
2795 or (tax_rate1 = 0) then -- Bug 1212074
2796 countrecs := countrecs + 1;
2797 ins_trans( legal_entity_id => p_legal_entity_id,
2798 org_id => p_org_id,
2799 conc_req_id => p_conc_req_id,
2800 remun_type => remun_type1,
2801 sub_remun_type => sub_remun_type1,
2802 vendor_nif => vendor_nif1,
2803 vendor_name => vendor_name1,
2804 invoice_id => invoice_id1,
2805 invoice_num => invoice_num1,
2806 inv_doc_seq_num => inv_doc_seq_num1,
2807 invoice_date => invoice_date1,
2808 gl_date => gl_date1,
2809 invoice_payment_id => invoice_payment_id1,
2810 date_paid => date_paid1,
2811 net_amount => net_amount1,
2812 withholding_tax_amount => withholding_tax_amount1,
2813 zip_electronic => zip_electronic1,
2814 zip_legal => zip_legal1,
2815 city_legal => city_legal1,
2816 num_children => num_children1,
2817 sign => sign1,
2818 tax_rate => tax_rate1,
2819 tax_name => tax_name1,
2820 year_due => year_due1);
2821 end if;
2822 END LOOP;
2823 plsqlmsg('Data inserted into table JE_ES_MODELO_190_ALL');
2824 CLOSE detail_paid;
2825 plsqlmsg('CURSOR Closed');
2826 plsqlmsg('Routine Successfully completed - ' || to_char(countrecs) || ' rows inserted');
2827 else -- p_pay_inv_sel <> 'P'
2828 /* Deal with APPROVED transactions */
2829 countrecs := 0;
2830 OPEN detail_approve;
2831 plsqlmsg('Opened CURSOR detail_approve');
2832 LOOP
2833 FETCH detail_approve
2834 INTO remun_type1,
2835 sub_remun_type1,
2836 vendor_nif1,
2837 vendor_name1,
2838 invoice_amount,
2839 inv_doc_seq_num1,
2840 invoice_id1,
2841 invoice_num1,
2842 invoice_date1,
2843 gl_date1,
2844 dist_awt_flag, -- bug 8709676
2845 net_amount1,
2846 withholding_tax_amount1,
2847 zip_legal1,
2848 city_legal1,
2849 tax_rate1,
2850 tax_name1,
2851 l_parent_reversal_flag;
2852 EXIT WHEN detail_approve%NOTFOUND;
2853 net_amount1 := round(net_amount1,func_curr_precision);
2854 withholding_tax_amount1 := round(withholding_tax_amount1,func_curr_precision);
2855 if ( withholding_tax_amount1 <>0 )
2856 or (tax_rate1 = 0) then -- Bug 1212074
2857 countrecs := countrecs + 1;
2858 ins_trans( legal_entity_id => p_legal_entity_id,
2859 org_id => p_org_id,
2860 conc_req_id => p_conc_req_id,
2861 remun_type => remun_type1,
2862 sub_remun_type => sub_remun_type1,
2863 vendor_nif => vendor_nif1,
2864 vendor_name => vendor_name1,
2865 invoice_id => invoice_id1,
2866 invoice_num => invoice_num1,
2867 inv_doc_seq_num => inv_doc_seq_num1,
2868 invoice_date => invoice_date1,
2869 gl_date => gl_date1,
2870 invoice_payment_id => NULL,
2871 date_paid => NULL,
2872 net_amount => net_amount1,
2873 withholding_tax_amount => withholding_tax_amount1,
2874 zip_electronic => NULL,
2875 zip_legal => zip_legal1,
2876 city_legal => city_legal1,
2877 num_children => NULL,
2878 sign => NULL,
2879 tax_rate => tax_rate1,
2880 tax_name => tax_name1,
2881 year_due => NULL);
2882 end if;
2883 END LOOP;
2884 plsqlmsg('Data inserted into table JE_ES_MODELO_190_ALL');
2885 CLOSE detail_approve;
2886 plsqlmsg('CURSOR Closed');
2887 plsqlmsg('Routine Successfully completed - ' || to_char(countrecs) || ' rows inserted');
2888 end if; -- p_pay_inv_sel = 'P' or <> 'P'
2889 else -- p_summary <> 'Y'
2890 /* Deal with DETAIL transactions */
2891 if p_pay_inv_sel = 'P' then
2892 /* Deal with PAID transactions */
2893 countrecs := 0;
2894 OPEN detail_paid;
2895 plsqlmsg('Opened CURSOR detail_paid for Hard Copy');
2896 LOOP
2897 FETCH detail_paid
2898 INTO wht_mode,
2899 remun_type1,
2900 sub_remun_type1,
2901 vendor_nif1,
2902 vendor_name1,
2903 inv_awt_flag,
2904 inv_payment_status_flag,
2905 invoice_id1,
2906 invoice_num1,
2907 invoice_amount,
2908 net_amount1,
2909 invoice_prepaid_amount,
2910 invoice_withheld_amount,
2911 inv_doc_seq_num1,
2912 invoice_date1,
2913 invoice_payment_id1,
2914 invoice_payments_count,
2915 paid_amount,
2916 discount_amount,
2917 date_paid1,
2918 awt_invoice_payment_id,
2919 zip_legal1,
2920 city_legal1,
2921 wht_net_amount1,
2922 withholding_tax_amount1,
2923 dist_awt_flag, --bug 8709676
2924 tax_rate1,
2925 tax_name1,
2926 l_parent_reversal_flag;
2927 EXIT WHEN detail_paid%NOTFOUND;
2928
2929 -- Bug 1271489: Get the correct tax name and tax rate.
2930 if dist_awt_flag <> 'M' then --bug 8709676
2931 fetch_awt_line( p_pay_inv_sel,
2932 invoice_id1,
2933 withholding_tax_amount1,
2934 date_paid1, -- Bug 3930123 : Spanugan 23/12/2004
2935 tax_rate1,
2936 tax_name1,
2937 p_legal_entity_id,
2938 p_org_id,
2939 l_parent_reversal_flag,
2940 awt_invoice_payment_id --Bug 12616975
2941 );
2942 end if;
2943 -- fnd_file.put_line(fnd_file.log,'Before No data');
2944 -- Automatic Withholding
2945 -- Withholding calculated at invoice payment time.
2946 if ( wht_mode = 'A' ) then
2947 if (nvl(inv_awt_flag,'N') = 'N') then
2948 net_amount1 := round(wht_net_amount1,func_curr_precision);
2949 withholding_tax_amount1 := round(withholding_tax_amount1, func_curr_precision);
2950 elsif (nvl(inv_awt_flag,'N') = 'Y') then
2951 if (nvl(inv_payment_status_flag,'N') = 'Y') then
2952 if (invoice_payments_count = 1 ) then
2953 net_amount1 := round(net_amount1,func_curr_precision);
2954 withholding_tax_amount1 := round(withholding_tax_amount1, func_curr_precision);
2955 elsif ( invoice_payments_count > 1 ) then
2956 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2957 withholding_tax_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* invoice_withheld_amount * -1),func_curr_precision);
2958 end if; -- if invoice_payments_count = 1 or > 1
2959 else -- if nvl(inv_payment_status_flag,'N') = 'N'
2960 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2961 withholding_tax_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* invoice_withheld_amount * -1),func_curr_precision);
2962 end if; -- if nvl(inv_payment_status_flag,'N') = 'N' or 'Y'
2963 end if; -- if nvl(inv_awt_flag,'N') = 'N' or 'Y'
2964 end if; -- if wht_mode = 'A'
2965 -- Manuali+Automatic Withholding
2966 -- Withholding calculated at invoice payment time.
2967 -- Withholding calculated at approval time.
2968 if ( wht_mode = 'M' ) then
2969 if (nvl(inv_payment_status_flag,'N') = 'Y') then
2970 if (invoice_payments_count = 1 ) then
2971 net_amount1 := round(net_amount1,func_curr_precision);
2972 withholding_tax_amount1 := round(withholding_tax_amount1, func_curr_precision);
2973 elsif ( invoice_payments_count > 1 ) then
2974 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2975 withholding_tax_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* invoice_withheld_amount * -1),func_curr_precision);
2976 end if; -- if invoice_payments_count = 1 or > 1
2977 else -- if nvl(inv_payment_status_flag,'N') = 'N'
2978 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2979 withholding_tax_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* invoice_withheld_amount * -1),func_curr_precision);
2980 end if; -- if nvl(inv_payment_status_flag,'N') = 'Y' or 'N'
2981 end if; -- if wht_mode = 'M'
2982 -- fnd_file.put_line(fnd_file.log,'Net Amount:' ||to_char(net_amount1));
2983 -- fnd_file.put_line(fnd_file.log,'WHT Amount:' ||to_char(withholding_tax_amount1));
2984 if ( withholding_tax_amount1 <>0 )
2985 or (tax_rate1 = 0) then -- Bug 1212074
2986 countrecs := countrecs + 1;
2987 ins_trans( legal_entity_id => p_legal_entity_id,
2988 org_id => p_org_id,
2989 conc_req_id => p_conc_req_id,
2990 remun_type => remun_type1,
2991 sub_remun_type => sub_remun_type1,
2992 vendor_nif => vendor_nif1,
2993 vendor_name => vendor_name1,
2994 invoice_id => invoice_id1,
2995 invoice_num => invoice_num1,
2996 inv_doc_seq_num => inv_doc_seq_num1,
2997 invoice_date => invoice_date1,
2998 gl_date => gl_date1,
2999 invoice_payment_id => invoice_payment_id1,
3000 date_paid => date_paid1,
3001 net_amount => net_amount1,
3002 withholding_tax_amount => withholding_tax_amount1,
3003 zip_electronic => zip_electronic1,
3004 zip_legal => zip_legal1,
3005 city_legal => city_legal1,
3006 num_children => num_children1,
3007 sign => sign1,
3008 tax_rate => tax_rate1,
3009 tax_name => tax_name1,
3010 year_due => year_due1);
3011 end if;
3012 END LOOP;
3013 plsqlmsg('Data inserted into table JE_ES_MODELO_190_ALL');
3014 CLOSE detail_paid;
3015 plsqlmsg('CURSOR Closed');
3016 plsqlmsg('Routine Successfully completed - ' || to_char(countrecs) || ' rows inserted');
3017 else -- p_pay_inv_sel <> 'P'
3018 /* Deal with APPOVED transactions */
3019 countrecs := 0;
3020 OPEN detail_approve;
3021 plsqlmsg('Opened CURSOR detail_approve');
3022 LOOP
3023 FETCH detail_approve
3024 INTO remun_type1,
3025 sub_remun_type1,
3026 vendor_nif1,
3027 vendor_name1,
3028 invoice_amount,
3029 inv_doc_seq_num1,
3030 invoice_id1,
3031 invoice_num1,
3032 invoice_date1,
3033 gl_date1,
3034 dist_awt_flag, -- bug 8709676
3035 net_amount1,
3036 withholding_tax_amount1,
3037 zip_legal1,
3038 city_legal1,
3039 tax_rate1,
3040 tax_name1,
3041 l_parent_reversal_flag;
3042 EXIT WHEN detail_approve%NOTFOUND;
3043 -- Bug 1271489: Get the correct tax name and tax rate.
3044
3045 if dist_awt_flag <> 'M' then --bug 8709676
3046 fetch_awt_line( p_pay_inv_sel,
3047 invoice_id1,
3048 withholding_tax_amount1,
3049 gl_date1, -- Bug 3930123 : Spanugan 23/12/2004
3050 tax_rate1,
3051 tax_name1,
3052 p_legal_entity_id,
3053 p_org_id,
3054 l_parent_reversal_flag);
3055
3056 end if; -- bug 8709676
3057
3058 net_amount1 := round(net_amount1,func_curr_precision);
3059 withholding_tax_amount1 := round(withholding_tax_amount1,func_curr_precision);
3060 if ( withholding_tax_amount1 <>0 )
3061 or (tax_rate1 = 0) then -- Bug 1212074
3062 countrecs := countrecs + 1;
3063 ins_trans( legal_entity_id => p_legal_entity_id,
3064 org_id => p_org_id,
3065 conc_req_id => p_conc_req_id,
3066 remun_type => remun_type1,
3067 sub_remun_type => sub_remun_type1,
3068 vendor_nif => vendor_nif1,
3069 vendor_name => vendor_name1,
3070 invoice_id => invoice_id1,
3071 invoice_num => invoice_num1,
3072 inv_doc_seq_num => inv_doc_seq_num1,
3073 invoice_date => invoice_date1,
3074 gl_date => gl_date1,
3075 invoice_payment_id => invoice_payment_id1,
3076 date_paid => date_paid1,
3077 net_amount => net_amount1,
3078 withholding_tax_amount => withholding_tax_amount1,
3079 zip_electronic => zip_electronic1,
3080 zip_legal => zip_legal1,
3081 city_legal => city_legal1,
3082 num_children => num_children1,
3083 sign => sign1,
3084 tax_rate => tax_rate1,
3085 tax_name => tax_name1,
3086 year_due => year_due1);
3087 end if;
3088 END LOOP;
3089 plsqlmsg('Data inserted into table JE_ES_MODELO_190_all');
3090 CLOSE detail_approve;
3091 plsqlmsg('CURSOR Closed');
3092 plsqlmsg('Routine Successfully completed - ' || to_char(countrecs) || ' rows inserted');
3093 RETCODE := 0;
3094 end if; -- p_pay_inv_sel = 'P' or <> 'P'
3095 end if; -- p_summary = 'Y' or <> 'Y'
3096 end if; -- p_hard_copy = 'N' or <> 'N'
3097 EXCEPTION
3098 WHEN bad_parameters THEN
3099 dbmsmsg('Error: Magnetic Report does not require DETAILED transactions');
3100 dbmsmsg('Error: Please Request SUMMARY transactions');
3101 RETCODE := 2;
3102 -- Bug 1271489: exception handling of wrong number of awt lines.
3103 WHEN bad_awt_lines THEN
3104 RETCODE := 2;
3105 WHEN others THEN
3106 dbmsmsg('Error: '|| substr(SQLERRM(SQLCODE),1,255));
3107 RETCODE := 2;
3108 ERRBUF := 'Error: '|| substr(SQLERRM(SQLCODE),1,255);
3109 end get_data;
3110 END je_es_whtax;