[Home] [Help]
PACKAGE BODY: APPS.JE_ES_WHTAX
Source
1 PACKAGE BODY JE_ES_WHTAX AS
2 /* $Header: jeeswhtb.pls 120.15.12010000.3 2008/08/04 12:27:05 vgadde 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
516 and dist.line_type_lookup_code = 'AWT';
517 return(amount_withheld);
518 END get_amount_withheld;
519 -----------------------------------------------------------------------
520 -- Function get_prepaid_amount returns the prepayment amount on
521 -- an invoice.
522 --
523 FUNCTION get_prepaid_amount( l_invoice_id IN NUMBER,
524 l_org_id IN NUMBER,
525 l_legal_entity_id IN NUMBER)
526 RETURN NUMBER IS
527 prepaid_amount NUMBER := 0;
528 BEGIN
529 select (0 - sum(nvl(dist.base_amount,nvl(dist.amount,0))))
530 into prepaid_amount
531 from ap_invoice_distributions_all dist,
532 ap_invoice_lines_all line,
533 ap_invoices_all inv
534 where dist.invoice_id = l_invoice_id
535 and inv.legal_entity_id = nvl(l_legal_entity_id, inv.legal_entity_id)
536 -- Bug 5207771 : Org_id is removed
537 -- and inv.org_id = nvl(l_org_id, inv.org_id)
538 and inv.invoice_id = line.invoice_id
539 and dist.invoice_id = line.invoice_id
540 and dist.distribution_line_number = line.line_number
541 and dist.line_type_lookup_code = 'PREPAY';
542 return(prepaid_amount);
543 END get_prepaid_amount;
544 ----------------------------------------------------------------------
545 -- Function get_awt_net_total returns the total distribution
546 -- amount for the invoice associated with withholding group.
547 -- BUG 3930123 : The net amount should be calculated only for the requested accounting period
548 -- spanugan 17/12/2004
549 FUNCTION get_awt_net_total(l_invoice_id IN NUMBER,
550 l_legal_entity_id IN NUMBER,
551 l_org_id IN NUMBER,
552 l_accounting_date IN DATE)
553 RETURN NUMBER IS
554 l_awt_net_total NUMBER := 0;
555 BEGIN
556 SELECT NVL(SUM(nvl(dist.base_amount,NVL(dist.amount,0))),0)
557 INTO l_awt_net_total
558 FROM ap_invoice_distributions_all dist,
559 ap_invoice_lines_all line,
560 ap_invoices_all inv
561 WHERE dist.invoice_id = l_invoice_id
562 and inv.legal_entity_id = nvl(l_legal_entity_id, inv.legal_entity_id)
563 -- Bug 5207771 : Org_id is removed
564 -- and inv.org_id = nvl(l_org_id, inv.org_id)
565 and inv.invoice_id = line.invoice_id
566 and dist.invoice_id = line.invoice_id
567 and dist.distribution_line_number = line.line_number
568 and dist.awt_group_id IS NOT NULL
569 and dist.line_type_lookup_code NOT IN ('AWT')
570 and dist.accounting_date = l_accounting_date; -- Bug 3930123
571 RETURN(l_awt_net_total);
572 END get_awt_net_total;
573 ----------------------------------------------------------------------
574 -- Function get_payments_count returns the total number of
575 -- accounted payments for the invoice.
576 --
577 FUNCTION get_payments_count( l_invoice_id IN NUMBER,
578 l_legal_entity_id IN NUMBER,
579 l_org_id IN NUMBER)
580 RETURN NUMBER IS
581 l_payments_count NUMBER := 0;
582 BEGIN
583 SELECT COUNT(aip.invoice_payment_id)
584 INTO l_payments_count
585 FROM ap_invoice_payments_all aip,
586 ap_checks_all ac
587 WHERE aip.invoice_id = l_invoice_id
588 AND ac.legal_entity_id = nvl(l_legal_entity_id, ac.legal_entity_id)
589 -- bug 5207771: Removed org_id condition
590 --and ac.org_id = nvl(l_org_id,ac.org_id)
591 AND aip.check_id = ac.check_id
592 AND ac.void_date is null;
593 RETURN(l_payments_count);
594 END get_payments_count;
595 ----------------------------------------------------------------------
596 -- Main Procedure Called by concurrent program.
597 --
598 PROCEDURE get_data ( ERRBUF OUT NOCOPY VARCHAR2,
599 RETCODE OUT NOCOPY NUMBER,
600 p_pay_inv_sel IN VARCHAR2,
601 p_summary IN VARCHAR2,
602 p_date_from IN VARCHAR2,
603 p_date_to IN VARCHAR2,
604 p_vendor_id IN NUMBER ,
605 p_conc_req_id IN NUMBER ,
606 p_hard_copy IN VARCHAR2 ,
607 p_wht_tax_type IN VARCHAR2,
608 p_legal_entity_id IN NUMBER,
609 p_org_id IN NUMBER
610 ) IS
611 bad_parameters EXCEPTION;
612 bad_awt_lines EXCEPTION; -- Bug 1271489
613 countrecs NUMBER;
614 first_record NUMBER := 0;
615 conc_req_id1 JE_ES_MODELO_190_ALL.conc_req_id%TYPE;
616 fin_ind1 JE_ES_MODELO_190_ALL.fin_ind%TYPE;
617 remun_type1 JE_ES_MODELO_190_ALL.remun_type%TYPE;
618 sub_remun_type1 JE_ES_MODELO_190_ALL.sub_remun_type%TYPE;
619 vendor_nif1 JE_ES_MODELO_190_ALL.vendor_nif%TYPE;
620 vendor_name1 JE_ES_MODELO_190_ALL.vendor_name%TYPE;
621 invoice_id1 JE_ES_MODELO_190_ALL.invoice_id%TYPE;
622 invoice_num1 JE_ES_MODELO_190_ALL.invoice_num%TYPE;
623 inv_doc_seq_num1 JE_ES_MODELO_190_ALL.inv_doc_seq_num%TYPE;
624 invoice_date1 JE_ES_MODELO_190_ALL.invoice_date%TYPE;
625 gl_date1 JE_ES_MODELO_190_ALL.gl_date%TYPE;
626 invoice_payment_id1 JE_ES_MODELO_190_ALL.invoice_payment_id%TYPE;
627 awt_invoice_payment_id JE_ES_MODELO_190_ALL.invoice_payment_id%TYPE;
628 date_paid1 JE_ES_MODELO_190_ALL.date_paid%TYPE;
629 invoice_amount JE_ES_MODELO_190_ALL.net_amount%TYPE;
630 inv_payment_status_flag ap_invoices.payment_status_flag%TYPE;
631 wht_mode ap_invoices.payment_status_flag%TYPE;
632 inv_awt_flag ap_invoices.awt_flag%TYPE;
633 paid_amount JE_ES_MODELO_190_ALL.net_amount%TYPE;
634 invoice_prepaid_amount JE_ES_MODELO_190_ALL.net_amount%TYPE;
635 invoice_withheld_amount JE_ES_MODELO_190_ALL.net_amount%TYPE;
636 inv_dist_net_amount JE_ES_MODELO_190_ALL.net_amount%TYPE;
637 discount_amount JE_ES_MODELO_190_ALL.net_amount%TYPE;
638 net_amount1 JE_ES_MODELO_190_ALL.net_amount%TYPE;
639 wht_net_amount1 JE_ES_MODELO_190_ALL.net_amount%TYPE;
640 inv_net_amount1 JE_ES_MODELO_190_ALL.net_amount%TYPE;
641 withholding_tax_amount1 JE_ES_MODELO_190_ALL.withholding_tax_amount%TYPE;
642 inv_wht_amount1 JE_ES_MODELO_190_ALL.withholding_tax_amount%TYPE;
643 zip_electronic1 JE_ES_MODELO_190_ALL.zip_electronic%TYPE;
644 zip_legal1 JE_ES_MODELO_190_ALL.zip_legal%TYPE;
645 city_legal1 JE_ES_MODELO_190_ALL.city_legal%TYPE;
646 num_children1 JE_ES_MODELO_190_ALL.num_children%TYPE;
647 sign1 JE_ES_MODELO_190_ALL.sign%TYPE;
648 tax_rate1 JE_ES_MODELO_190_ALL.tax_rate%TYPE;
649 tax_name1 JE_ES_MODELO_190_ALL.tax_name%TYPE;
650 year_due1 JE_ES_MODELO_190_ALL.year_due%TYPE;
651 invoice_payments_count number := 0;
652 func_curr fnd_currencies_vl.currency_code%TYPE;
653 func_curr_precision fnd_currencies_vl.precision%TYPE;
654 old_remun_type JE_ES_MODELO_190_ALL.remun_type%TYPE;
655 old_sub_remun_type JE_ES_MODELO_190_ALL.sub_remun_type%TYPE;
656 old_vendor_nif JE_ES_MODELO_190_ALL.vendor_nif%TYPE;
657 old_vendor_name JE_ES_MODELO_190_ALL.vendor_name%TYPE;
658 old_city_legal JE_ES_MODELO_190_ALL.city_legal%TYPE;
659 old_zip_electronic JE_ES_MODELO_190_ALL.zip_electronic%TYPE;
660 old_zip_legal JE_ES_MODELO_190_ALL.zip_legal%TYPE;
661 old_tax_rate JE_ES_MODELO_190_ALL.tax_rate%TYPE;
662 old_tax_name JE_ES_MODELO_190_ALL.tax_name%TYPE;
663 l_le_id_count NUMBER;
664 l_le_id_message VARCHAR2(500);
665 l_ledger_id number;
666 --
667 -- Summary APPROVED transactions Magnetic Report
668 -- Tax Code and Tax Rate are not used in Magnetic Format(Bug 998053).
669 --
670 CURSOR sum_approve_mag IS
671 SELECT decode(nvl(v.employee_id,-1),-1,'G','A'),
672 decode(nvl(v.employee_id,-1),-1,'01','00'),
673 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
674 substr(v.vendor_name,1,80) , -- AP UTF8 Changes 2398166
675 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3)),
676 sum(decode(dist.awt_group_id,NULL,0,
677 decode(dist.line_type_lookup_code,'AWT',0,
678 nvl(dist.base_amount,dist.amount)))) net_amount,
679 sum(decode(dist.line_type_lookup_code,'AWT',
680 nvl(dist.base_amount,dist.amount),0)) withholding_tax_amount
681 FROM po_vendors v,
682 po_vendor_sites_all vs,
683 fnd_lookups fl,
684 ap_invoices_all inv,
685 ap_invoice_lines_all line,
686 ap_invoice_distributions_all dist,
687 ap_tax_codes_all atc,
688 ap_awt_tax_rates_all awt,
689 (SELECT distinct person_id
690 ,national_identifier
691 FROM PER_ALL_PEOPLE_F) papf
692 WHERE v.vendor_id = vs.vendor_id
693 AND nvl(v.employee_id,-99) = papf.person_id (+)
694 AND vs.tax_reporting_site_flag = 'Y'
695 AND vs.country = fl.lookup_code(+)
696 AND fl.lookup_type = 'JEES_EURO_COUNTRY_CODES'
697 AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
698 AND inv.vendor_id = v.vendor_id
699 and vs.vendor_site_id = inv.vendor_site_id
700 and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
701 -- bug 5207771: Removed org_id condition
702 --and inv.org_id = nvl(p_org_id,inv.org_id)
703 and inv.invoice_id = line.invoice_id
704 and dist.invoice_id = line.invoice_id
705 and dist.invoice_line_number = line.line_number
706 -----and inv.cancelled_date is null -- Bug 2228008 )
707 AND dist.parent_reversal_id is null
708 AND not exists ( select 1
709 from ap_invoice_distributions dist1, gl_period_statuses gl
710 where gl.application_id = 101
711 and dist1.invoice_id = inv.invoice_id
712 and dist1.parent_reversal_id = dist.invoice_distribution_id
713 and gl.ledger_id = dist1.set_of_books_id
714 and dist.accounting_date between gl.start_date and gl.end_date
715 and dist1.accounting_date <= gl.end_date )
716 AND trunc(dist.accounting_date,'DD')
717 between fnd_date.canonical_to_date(P_Date_From)
718 AND fnd_date.canonical_to_date(P_Date_To)
719 AND ((dist.line_type_lookup_code = 'AWT')
720 OR
721 (dist.awt_group_id is not NULL))
722 AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
723 dist.ACCRUAL_POSTED_FLAG,
724 dist.CASH_POSTED_FLAG,
725 dist.POSTED_FLAG, inv.org_id) in ('Y','P')
726 -- bug 5207771 Added legal_entity id as 4th parameter, above line
727 AND dist.withholding_tax_code_id = atc.tax_id (+) -- bug 5102299
728 AND atc.name = awt.tax_name(+)
729 AND awt.vendor_id is null /* Ignore any Vendor Lines */
730 -- Bug 5207771 : Added to remove the duplicates WH lines
731 AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
732 OR (dist.awt_tax_rate_id is NULL) )
733 -- Ignore any invoices which do not have 'AWT' distribution lines
734 AND EXISTS ( select dist2.invoice_id
735 from ap_invoice_distributions_all dist2
736 where inv.invoice_id = dist2.invoice_id
737 and dist2.line_type_lookup_code = 'AWT'
738 and dist2.withholding_tax_code_id in
739 -- Bug 2019586: Column name should be tax_id.
740 -- (select tax_code_id from ap_tax_codes
741 (select tax_id from ap_tax_codes_all
742 where vat_transaction_type = p_wht_tax_type))
743 GROUP BY decode(nvl(v.employee_id,-1),-1,'G','A'),
744 decode(nvl(v.employee_id,-1),-1,'01','00'),
745 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
746 substr(v.vendor_name,1,80) ,
747 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3))
748 HAVING sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0
749 -- BUG 3930123 : Adding one more select clause with certain modifications, to fetch
750 -- the invoices that are cancelled in different accounting period.
751 -- spanugan 17/12/2004
752 UNION
753 SELECT decode(nvl(v.employee_id,-1),-1,'G','A'),
754 decode(nvl(v.employee_id,-1),-1,'01','00'),
755 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
756 substr(v.vendor_name,1,80) , -- AP UTF8 Changes 2398166
757 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3)),
758 sum(decode(dist.awt_group_id,NULL,0,
759 decode(dist.line_type_lookup_code,'AWT',0,
760 nvl(dist.base_amount,dist.amount)))) net_amount,
761 sum(decode(dist.line_type_lookup_code,'AWT',
762 nvl(dist.base_amount,dist.amount),0)) withholding_tax_amount
763 FROM fnd_lookups fl,
764 po_vendors v,
765 po_vendor_sites_all vs,
766 ap_tax_codes_all atc,
767 ap_awt_tax_rates_all awt,
768 ap_invoices_all inv,
769 ap_invoice_lines_all line,
770 ap_invoice_distributions_all dist,
771 (SELECT distinct person_id
772 ,national_identifier
773 FROM PER_ALL_PEOPLE_F) papf
774 WHERE v.vendor_id = vs.vendor_id
775 AND nvl(v.employee_id,-99) = papf.person_id (+)
776 AND vs.tax_reporting_site_flag = 'Y'
777 AND vs.country = fl.lookup_code(+)
778 AND fl.lookup_type = 'JEES_EURO_COUNTRY_CODES'
779 AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
780 AND inv.vendor_id = v.vendor_id
781 and vs.vendor_site_id = inv.vendor_site_id
782 and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
783 -- bug 5207771: Removed org_id condition
784 --and inv.org_id = nvl(p_org_id,inv.org_id)
785 and inv.invoice_id = line.invoice_id
786 and dist.invoice_id = line.invoice_id
787 and dist.invoice_line_number = line.line_number
788 -- BUG 3930123 : spanugan
789 /*AND inv.cancelled_date is not null
790 AND (
791 (dist.cancellation_flag is null
792 AND dist.accounting_date < (select distinct gl.start_date
793 from gl_period_statuses gl
794 where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
795 from ap_invoice_distributions_all dist1
796 where dist1.invoice_id = inv.invoice_id
797 and dist1.cancellation_flag = 'Y' )))
798 OR
799 (dist.cancellation_flag = 'Y'
800 AND dist.accounting_date > (select distinct gl.end_date
801 from gl_period_statuses gl
802 where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
803 from ap_invoice_distributions_all dist1
804 where dist1.invoice_id = inv.invoice_id
805 and dist1.cancellation_flag is null )))
806 )
807 -- END
808 -- Bug 3930123 JCHALL . Changed the subquery above from
809 -- a single row returned to accept mutiple rows.
810 --
811 */
812 AND dist.parent_reversal_id is not null
813 AND dist.accounting_date > (select distinct gl.end_date
814 from ap_invoice_distributions dist1, gl_period_statuses gl
815 where gl.application_id = 101
816 and dist1.invoice_id = inv.invoice_id
817 and dist.parent_reversal_id = dist1.invoice_distribution_id
818 and gl.ledger_id = dist1.set_of_books_id
819 and dist1.accounting_date between gl.start_date and gl.end_date)
820 AND trunc(dist.accounting_date,'DD')
821 between fnd_date.canonical_to_date(P_Date_From)
822 AND fnd_date.canonical_to_date(P_Date_To)
823 AND ((dist.line_type_lookup_code = 'AWT')
824 OR
825 (dist.awt_group_id is not NULL))
826 AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
827 dist.ACCRUAL_POSTED_FLAG,
828 dist.CASH_POSTED_FLAG,
829 dist.POSTED_FLAG, inv.org_id) in ('Y','P')
830 -- bug 5207771 Added legal_entity id as 4th parameter, above line
831 AND dist.withholding_tax_code_id = atc.tax_id(+)
832 AND atc.name = awt.tax_name(+)
833 AND awt.vendor_id is null /* Ignore any Vendor Lines */
834 -- Bug 5207771 : Added to remove the duplicates WH lines
835 AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
836 OR (dist.awt_tax_rate_id is NULL) )
837 -- Ignore any invoices which do not have 'AWT' distribution lines
838 AND EXISTS ( select dist2.invoice_id
839 from ap_invoice_distributions_all dist2
840 where inv.invoice_id = dist2.invoice_id
841 and dist2.line_type_lookup_code = 'AWT'
842 and dist2.withholding_tax_code_id in
843 -- Bug 2019586: Column name should be tax_id.
844 -- (select tax_code_id from ap_tax_codes
845 (select tax_id from ap_tax_codes_all
846 where vat_transaction_type = p_wht_tax_type))
847 GROUP BY decode(nvl(v.employee_id,-1),-1,'G','A'),
848 decode(nvl(v.employee_id,-1),-1,'01','00'),
849 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
850 substr(v.vendor_name,1,80) ,
851 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3))
852 HAVING sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0;
853 --
854 -- Detailed PAID transactions cursor.
855 -- This is used for Detail, Summary, Summary Magnetic format Transactions
856 -- extract purpose. It handles automatic witholding, manual witholding
857 --
858 CURSOR detail_paid IS
859 SELECT 'A',
860 decode(nvl(v.employee_id,-1),-1,'G','A'),
861 decode(nvl(v.employee_id,-1),-1,'01','00'),
862 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
863 substr(v.vendor_name,1,80) , -- AP UTF8 Changes 2398166
864 inv.awt_flag,
865 inv.payment_status_flag,
866 inv.invoice_id,
867 inv.invoice_num,
868 nvl(inv.base_amount,inv.invoice_amount) invoice_amount,
869 -- nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID),0) net_amount,
870 nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
871 nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) prepaid_amount,
872 nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) invoice_withheld_amount,
873 decode(seq.name || '-' ||
874 to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
875 to_char(inv.doc_sequence_value)),
876 trunc(inv.invoice_date,'DD'),
877 invpay.invoice_payment_id,
878 nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) payments_count,
879 nvl(invpay.payment_base_amount,invpay.amount),
880 nvl(invpay.discount_taken,0),
881 trunc(invpay.accounting_date,'DD'),
882 dist.awt_invoice_payment_id,
883 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'|| substr(fl.description,1,3)),
884 vs.city,
885 0,
886 sum(nvl(dist.base_amount,dist.amount)),
887 awt.tax_rate,
888 awt.tax_name
889 FROM fnd_lookups fl,
890 po_vendors v,
891 po_vendor_sites_all vs,
892 ap_invoice_payments_all invpay,
893 ap_checks_all checks,
894 ap_tax_codes_all atc,
895 ap_awt_tax_rates_all awt,
896 fnd_document_sequences seq,
897 ap_invoices_all inv,
898 ap_invoice_lines_all line,
899 ap_invoice_distributions_all dist,
900 (SELECT distinct person_id
901 ,national_identifier
902 FROM PER_ALL_PEOPLE_F) papf
903 WHERE vs.country = fl.lookup_code(+)
904 AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
905 ---AND v.vendor_id = nvl(p_vendor_id,v.vendor_id)
906 AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
907 AND nvl(v.employee_id,-99) = papf.person_id (+)
908 AND inv.vendor_id = v.vendor_id
909 AND v.vendor_id = vs.vendor_id
910 AND vs.tax_reporting_site_flag = 'Y'
911 and vs.vendor_site_id = inv.vendor_site_id
912 AND nvl(inv.awt_flag,'N') = 'Y'
913 and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
914 -- bug 5207771: Removed org_id condition
915 --and inv.org_id = nvl(p_org_id,inv.org_id)
916 and inv.invoice_id = line.invoice_id
917 and dist.invoice_id = line.invoice_id
918 and dist.invoice_line_number = line.line_number
919 ---and inv.cancelled_date is null -- Bug 2228008
920 AND dist.parent_reversal_id is null
921 AND not exists ( select 1
922 from ap_invoice_distributions dist1, gl_period_statuses gl
923 where gl.application_id = 101
924 and dist1.invoice_id = inv.invoice_id
925 and dist1.parent_reversal_id = dist.invoice_distribution_id
926 and gl.ledger_id = dist1.set_of_books_id
927 and dist.accounting_date between gl.start_date and gl.end_date
928 and dist1.accounting_date <= gl.end_date )
929 AND inv.invoice_id = invpay.invoice_id
930 AND ( invpay.posted_flag in ('Y','P')
931 or invpay.cash_posted_flag in ('Y','P')
932 or invpay.accrual_posted_flag in ('Y','P'))
933 AND invpay.check_id = checks.check_id
934 AND checks.void_date is null
935 AND trunc(invpay.accounting_date,'DD')
936 between
937 nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
938 and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
939 AND (dist.line_type_lookup_code = 'AWT')
940 AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
941 DIST.ACCRUAL_POSTED_FLAG,
942 DIST.CASH_POSTED_FLAG,
943 dist.POSTED_FLAG, inv.org_id) in ('Y','P')
944 AND dist.withholding_tax_code_id = atc.tax_id(+)
945 AND atc.name = awt.tax_name(+)
946 AND awt.vendor_id is null /* Ignore any Vendor Lines */
947 AND invpay.accounting_date
948 between nvl(awt.start_date,invpay.accounting_date)
949 and nvl(awt.end_date, invpay.accounting_date)
950 AND inv.doc_sequence_id = seq.doc_sequence_id(+)
951 -- Bug 5207771 : Added to remove the duplicates WH lines
952 AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
953 OR (dist.awt_tax_rate_id is NULL) )
954 -- Ignore any invoices which do not have 'AWT' distribution lines
955 AND EXISTS (select dist2.invoice_id
956 from ap_invoice_distributions_all dist2
957 where inv.invoice_id = dist2.invoice_id
958 and dist2.line_type_lookup_code = 'AWT'
959 and dist2.withholding_tax_code_id in
960 -- Bug 2019586: Column name should be tax_id.
961 -- (select tax_code_id from ap_tax_codes
962 (select tax_id
963 from ap_tax_codes_all
964 where vat_transaction_type = p_wht_tax_type))
965 AND NOT EXISTS ( select dist2.invoice_id
966 from ap_invoice_distributions_all dist2
967 where inv.invoice_id = dist2.invoice_id
968 and dist2.line_type_lookup_code = 'AWT'
969 and dist2.awt_flag <> 'A')
970 GROUP BY 'A',
971 decode(nvl(v.employee_id,-1),-1,'G','A'),
972 decode(nvl(v.employee_id,-1),-1,'01','00'),
973 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
974 substr(v.vendor_name,1,80) ,
975 inv.awt_flag,
976 inv.payment_status_flag,
977 inv.invoice_id,
978 inv.invoice_num,
979 nvl(inv.base_amount,inv.invoice_amount) ,
980 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0),
981 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0),
982 nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) ,
983 nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0),
984 decode(seq.name || '-' ||
985 to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
986 to_char(inv.doc_sequence_value)),
987 trunc(inv.invoice_date,'DD'),
988 invpay.invoice_payment_id,
989 nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) ,
990 nvl(invpay.payment_base_amount,invpay.amount),
991 nvl(invpay.discount_taken,0),
992 trunc(invpay.accounting_date,'DD'),
993 dist.awt_invoice_payment_id,
994 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
995 substr(fl.description,1,3)),
996 vs.city,
997 0,
998 awt.tax_rate,
999 awt.tax_name
1000 HAVING ((sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0) or (min(awt.tax_rate) = 0))
1001 -- Bug 1212074
1002 -- BUG 3930123 : Adding one more select clause with certain modifications, to fetch
1003 -- the invoices that are cancelled in different accounting period.
1004 -- spanugan 17/12/2004
1005 UNION
1006 SELECT 'A',
1007 decode(nvl(v.employee_id,-1),-1,'G','A'),
1008 decode(nvl(v.employee_id,-1),-1,'01','00'),
1009 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1010 substr(v.vendor_name,1,80) , -- AP UTF8 Changes 2398166
1011 inv.awt_flag,
1012 inv.payment_status_flag,
1013 inv.invoice_id,
1014 inv.invoice_num,
1015 nvl(inv.base_amount,inv.invoice_amount) invoice_amount,
1016 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) net_amount,
1017 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
1018 nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) prepaid_amount,
1019 nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) invoice_withheld_amount,
1020 decode(seq.name || '-' ||
1021 to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
1022 to_char(inv.doc_sequence_value)),
1023 trunc(inv.invoice_date,'DD'),
1024 invpay.invoice_payment_id,
1025 nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) payments_count,
1026 nvl(invpay.payment_base_amount,invpay.amount),
1027 nvl(invpay.discount_taken,0),
1028 trunc(invpay.accounting_date,'DD'),
1029 dist.awt_invoice_payment_id,
1030 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
1031 substr(fl.description,1,3)),
1032 vs.city,
1033 0,
1034 sum(nvl(dist.base_amount,dist.amount)),
1035 awt.tax_rate,
1036 awt.tax_name
1037 FROM fnd_lookups fl,
1038 po_vendors v,
1039 po_vendor_sites_all vs,
1040 ap_invoice_payments_all invpay,
1041 ap_checks_all checks,
1042 ap_tax_codes_all atc,
1043 ap_awt_tax_rates_all awt,
1044 fnd_document_sequences seq,
1045 ap_invoices_all inv,
1046 ap_invoice_lines_all line,
1047 ap_invoice_distributions_all dist,
1048 (SELECT distinct person_id
1049 ,national_identifier
1050 FROM PER_ALL_PEOPLE_F) papf
1051 WHERE vs.country = fl.lookup_code(+)
1052 AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
1053 ---AND v.vendor_id = nvl(p_vendor_id,v.vendor_id)
1054 AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
1055 AND nvl(v.employee_id,-99) = papf.person_id (+)
1056 AND inv.vendor_id = v.vendor_id
1057 AND v.vendor_id = vs.vendor_id
1058 AND vs.tax_reporting_site_flag = 'Y'
1059 and vs.vendor_site_id = inv.vendor_site_id
1060 AND nvl(inv.awt_flag,'N') = 'Y'
1061 and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
1062 -- bug 5207771: Removed org_id condition
1063 --and inv.org_id = nvl(p_org_id,inv.org_id)
1064 and inv.invoice_id = line.invoice_id
1065 and dist.invoice_id = line.invoice_id
1066 and dist.invoice_line_number = line.line_number
1067 -- BUG 3930123 : spanugan
1068 /*AND inv.cancelled_date is not null
1069 AND (
1070 (dist.cancellation_flag is null
1071 AND dist.accounting_date < (select distinct gl.start_date
1072 from gl_period_statuses gl
1073 where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
1074 from ap_invoice_distributions_all dist1
1075 where dist1.invoice_id = inv.invoice_id
1076 and dist1.cancellation_flag = 'Y' )))
1077 OR
1078 (dist.cancellation_flag = 'Y'
1079 AND dist.accounting_date > (select distinct gl.end_date
1080 from gl_period_statuses gl
1081 where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
1082 from ap_invoice_distributions_all dist1
1083 where dist1.invoice_id = inv.invoice_id
1084 and dist1.cancellation_flag is null )))
1085 )
1086 -- END
1087 */
1088 AND dist.parent_reversal_id is not null
1089 AND dist.accounting_date > (select distinct gl.end_date
1090 from ap_invoice_distributions dist1, gl_period_statuses gl
1091 where gl.application_id = 101
1092 and dist1.invoice_id = inv.invoice_id
1093 and dist.parent_reversal_id = dist1.invoice_distribution_id
1094 and gl.ledger_id = dist1.set_of_books_id
1095 and dist1.accounting_date between gl.start_date and gl.end_date)
1096 AND inv.invoice_id = invpay.invoice_id
1097 AND ( invpay.posted_flag in ('Y','P')
1098 or invpay.cash_posted_flag in ('Y','P')
1099 or invpay.accrual_posted_flag in ('Y','P'))
1100 AND invpay.check_id = checks.check_id
1101 AND checks.void_date is null
1102 AND trunc(invpay.accounting_date,'DD')
1103 between
1104 nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
1105 and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
1106 AND (dist.line_type_lookup_code = 'AWT')
1107 AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
1108 DIST.ACCRUAL_POSTED_FLAG,
1109 DIST.CASH_POSTED_FLAG,
1110 dist.POSTED_FLAG, inv.org_id) in ('Y','P')
1111 AND dist.withholding_tax_code_id = atc.tax_id(+)
1112 AND atc.name = awt.tax_name(+)
1113 AND awt.vendor_id is null /* Ignore any Vendor Lines */
1114 -- Bug 5207771 : Added to remove the duplicates WH lines
1115 AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
1116 OR (dist.awt_tax_rate_id is NULL) )
1117 AND invpay.accounting_date
1118 between nvl(awt.start_date,invpay.accounting_date)
1119 and nvl(awt.end_date, invpay.accounting_date)
1120 AND inv.doc_sequence_id = seq.doc_sequence_id(+)
1121 -- Ignore any invoices which do not have 'AWT' distribution lines
1122 AND EXISTS (select dist2.invoice_id
1123 from ap_invoice_distributions_all dist2
1124 where inv.invoice_id = dist2.invoice_id
1125 and dist2.line_type_lookup_code = 'AWT'
1126 and dist2.withholding_tax_code_id in
1127 -- Bug 2019586: Column name should be tax_id.
1128 -- (select tax_code_id from ap_tax_codes
1129 (select tax_id
1130 from ap_tax_codes_all
1131 where vat_transaction_type = p_wht_tax_type))
1132 AND NOT EXISTS ( select dist2.invoice_id
1133 from ap_invoice_distributions_all dist2
1134 where inv.invoice_id = dist2.invoice_id
1135 and dist2.line_type_lookup_code = 'AWT'
1136 and dist2.awt_flag <> 'A')
1137 GROUP BY 'A',
1138 decode(nvl(v.employee_id,-1),-1,'G','A'),
1139 decode(nvl(v.employee_id,-1),-1,'01','00'),
1140 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1141 substr(v.vendor_name,1,80) ,
1142 inv.awt_flag,
1143 inv.payment_status_flag,
1144 inv.invoice_id,
1145 inv.invoice_num,
1146 nvl(inv.base_amount,inv.invoice_amount) ,
1147 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0),
1148 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0),
1149 nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) ,
1150 nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0),
1151 decode(seq.name || '-' ||
1152 to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
1153 to_char(inv.doc_sequence_value)),
1154 trunc(inv.invoice_date,'DD'),
1155 invpay.invoice_payment_id,
1156 nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) ,
1157 nvl(invpay.payment_base_amount,invpay.amount),
1158 nvl(invpay.discount_taken,0),
1159 trunc(invpay.accounting_date,'DD'),
1160 dist.awt_invoice_payment_id,
1161 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
1162 substr(fl.description,1,3)),
1163 vs.city,
1164 0,
1165 awt.tax_rate,
1166 awt.tax_name
1167 HAVING ((sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0) or (min(awt.tax_rate) = 0))
1168 -- Bug 1212074
1169 UNION
1170 SELECT 'A',
1171 decode(nvl(v.employee_id,-1),-1,'G','A'),
1172 decode(nvl(v.employee_id,-1),-1,'01','00'),
1173 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1174 v.vendor_name,
1175 inv.awt_flag,
1176 inv.payment_status_flag,
1177 inv.invoice_id,
1178 inv.invoice_num,
1179 nvl(inv.base_amount,inv.invoice_amount) invoice_amount,
1180 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) net_amount,
1181 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
1182 nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) prepaid_amount,
1183 nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) invoice_withheld_amount,
1184 decode(seq.name || '-' || to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
1185 to_char(inv.doc_sequence_value)),
1186 trunc(inv.invoice_date,'DD'),
1187 invpay.invoice_payment_id,
1188 nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) payments_count,
1189 nvl(invpay.payment_base_amount,invpay.amount),
1190 nvl(invpay.discount_taken,0),
1191 trunc(invpay.accounting_date,'DD'),
1192 dist.awt_invoice_payment_id,
1193 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'|| substr(fl.description,1,3)),
1194 vs.city,
1195 (nvl(dist.awt_gross_amount,0)) wht_net_amount,
1196 (nvl(dist.base_amount,dist.amount)),
1197 awt.tax_rate,
1198 awt.tax_name
1199 FROM fnd_lookups fl,
1200 po_vendors v,
1201 po_vendor_sites_all vs,
1202 ap_invoice_payments_all invpay,
1203 ap_checks_all checks,
1204 ap_tax_codes_all atc,
1205 ap_awt_tax_rates_all awt,
1206 fnd_document_sequences seq,
1207 ap_invoices_all inv,
1208 ap_invoice_lines_all line,
1209 ap_invoice_distributions_all dist,
1210 (SELECT distinct person_id
1211 ,national_identifier
1212 FROM PER_ALL_PEOPLE_F) papf
1213 WHERE vs.country = fl.lookup_code(+)
1214 AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
1215 AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
1216 AND nvl(v.employee_id,-99) = papf.person_id (+)
1217 AND inv.vendor_id = v.vendor_id
1218 AND v.vendor_id = vs.vendor_id
1219 and vs.vendor_site_id = inv.vendor_site_id
1220 AND vs.tax_reporting_site_flag = 'Y'
1221 AND nvl(inv.awt_flag,'N') = 'N'
1222 and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
1223 -- bug 5207771: Removed org_id condition
1224 --and inv.org_id = nvl(p_org_id,inv.org_id)
1225 and inv.invoice_id = line.invoice_id
1226 and dist.invoice_id = line.invoice_id
1227 and dist.invoice_line_number = line.line_number
1228 ---and inv.cancelled_date is null -- Bug 2228008
1229 AND dist.parent_reversal_id is null
1230 AND not exists ( select 1
1231 from ap_invoice_distributions dist1, gl_period_statuses gl
1232 where gl.application_id = 101
1233 and dist1.invoice_id = inv.invoice_id
1234 and dist1.parent_reversal_id = dist.invoice_distribution_id
1235 and gl.ledger_id = dist1.set_of_books_id
1236 and dist.accounting_date between gl.start_date and gl.end_date
1237 and dist1.accounting_date <= gl.end_date )
1238 AND inv.invoice_id = invpay.invoice_id
1239 AND ( invpay.posted_flag in ('Y','P')
1240 or invpay.cash_posted_flag in ('Y','P')
1241 or invpay.accrual_posted_flag in ('Y','P'))
1242 AND invpay.check_id = checks.check_id
1243 AND checks.void_date is null
1244 AND trunc(invpay.accounting_date,'DD')
1245 between
1246 nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
1247 and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
1248 AND dist.awt_invoice_payment_id = invpay.invoice_payment_id
1249 AND (dist.line_type_lookup_code = 'AWT')
1250 AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
1251 DIST.ACCRUAL_POSTED_FLAG,
1252 DIST.CASH_POSTED_FLAG,
1253 dist.POSTED_FLAG, inv.org_id) in ('Y','P')
1254 AND dist.withholding_tax_code_id = atc.tax_id(+)
1255 AND atc.name = awt.tax_name(+)
1256 AND awt.vendor_id is null
1257 AND invpay.accounting_date between nvl(awt.start_date, invpay.accounting_date)
1258 AND nvl(awt.end_date, invpay.accounting_date)
1259 AND inv.doc_sequence_id = seq.doc_sequence_id(+)
1260 -- Bug 5207771 : Added to remove the duplicates WH lines
1261 AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
1262 OR (dist.awt_tax_rate_id is NULL) )
1263 AND EXISTS (select dist2.invoice_id
1264 from ap_invoice_distributions_all dist2
1265 where inv.invoice_id = dist2.invoice_id
1266 and dist2.line_type_lookup_code = 'AWT'
1267 and dist2.withholding_tax_code_id in
1268 -- Bug 2019586: Column name should be tax_id.
1269 -- (select tax_code_id from ap_tax_codes
1270 (select tax_id
1271 from ap_tax_codes_all
1272 where vat_transaction_type = p_wht_tax_type))
1273 AND NOT EXISTS ( select dist2.invoice_id
1274 from ap_invoice_distributions_all dist2
1275 where inv.invoice_id = dist2.invoice_id
1276 and dist2.line_type_lookup_code = 'AWT'
1277 and dist2.awt_flag <> 'A')
1278 -- BUG 3930123 : Adding one more select clause with certain modifications, to fetch
1279 -- the invoices that are cancelled in different accounting period.
1280 -- spanugan 17/12/2004
1281 UNION
1282 SELECT 'A',
1283 decode(nvl(v.employee_id,-1),-1,'G','A'),
1284 decode(nvl(v.employee_id,-1),-1,'01','00'),
1285 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1286 v.vendor_name,
1287 inv.awt_flag,
1288 inv.payment_status_flag,
1289 inv.invoice_id,
1290 inv.invoice_num,
1291 nvl(inv.base_amount,inv.invoice_amount) invoice_amount,
1292 -- nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID),0) net_amount,
1293 nvl(je_es_whtax.get_awt_net_total(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
1294 nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) prepaid_amount,
1295 nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) invoice_withheld_amount,
1296 decode(seq.name || '-' ||
1297 to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
1298 to_char(inv.doc_sequence_value)),
1299 trunc(inv.invoice_date,'DD'),
1300 invpay.invoice_payment_id,
1301 nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) payments_count,
1302 nvl(invpay.payment_base_amount,invpay.amount),
1303 nvl(invpay.discount_taken,0),
1304 trunc(invpay.accounting_date,'DD'),
1305 dist.awt_invoice_payment_id,
1306 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
1307 substr(fl.description,1,3)),
1308 vs.city,
1309 (nvl(dist.awt_gross_amount,0)) wht_net_amount,
1310 (nvl(dist.base_amount,dist.amount)),
1311 awt.tax_rate,
1312 awt.tax_name
1313 FROM fnd_lookups fl,
1314 po_vendors v,
1315 po_vendor_sites_all vs,
1316 ap_invoice_payments_all invpay,
1317 ap_checks_all checks,
1318 ap_tax_codes_all atc,
1319 ap_awt_tax_rates_all awt,
1320 fnd_document_sequences seq,
1321 ap_invoices_all inv,
1322 ap_invoice_lines_all line,
1323 ap_invoice_distributions_all dist,
1324 (SELECT distinct person_id
1325 ,national_identifier
1326 FROM PER_ALL_PEOPLE_F) papf
1327 WHERE vs.country = fl.lookup_code(+)
1328 AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
1329 AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
1330 AND nvl(v.employee_id,-99) = papf.person_id (+)
1331 AND inv.vendor_id = v.vendor_id
1332 AND v.vendor_id = vs.vendor_id
1333 and vs.vendor_site_id = inv.vendor_site_id
1334 AND vs.tax_reporting_site_flag = 'Y'
1335 AND nvl(inv.awt_flag,'N') = 'N'
1336 and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
1337 -- bug 5207771: Removed org_id condition
1338 --and inv.org_id = nvl(p_org_id,inv.org_id)
1339 and inv.invoice_id = line.invoice_id
1340 and dist.invoice_id = line.invoice_id
1341 and dist.invoice_line_number = line.line_number
1342 -- BUG 3930123 : spanugan
1343 /*AND inv.cancelled_date is not null
1344 AND
1345 (
1346 (dist.cancellation_flag is null
1347 AND dist.accounting_date < (select distinct gl.start_date
1348 from gl_period_statuses gl
1349 where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
1350 from ap_invoice_distributions_all dist1
1351 where dist1.invoice_id = inv.invoice_id
1352 and dist1.cancellation_flag = 'Y' )))
1353 OR
1354 (dist.cancellation_flag = 'Y'
1355 AND dist.accounting_date > (select distinct gl.end_date
1356 from gl_period_statuses gl
1357 where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
1358 from ap_invoice_distributions_all dist1
1359 where dist1.invoice_id = inv.invoice_id
1360 and dist1.cancellation_flag is null )))
1361 )
1362 -- END
1363 */
1364 AND dist.parent_reversal_id is not null
1365 AND dist.accounting_date > (select distinct gl.end_date
1366 from ap_invoice_distributions dist1, gl_period_statuses gl
1367 where gl.application_id = 101
1368 and dist1.invoice_id = inv.invoice_id
1369 and dist.parent_reversal_id = dist1.invoice_distribution_id
1370 and gl.ledger_id = dist1.set_of_books_id
1371 and dist1.accounting_date between gl.start_date and gl.end_date)
1372 AND inv.invoice_id = invpay.invoice_id
1373 AND ( invpay.posted_flag in ('Y','P')
1374 or invpay.cash_posted_flag in ('Y','P')
1375 or invpay.accrual_posted_flag in ('Y','P'))
1376 AND invpay.check_id = checks.check_id
1377 AND checks.void_date is null
1378 AND trunc(invpay.accounting_date,'DD')
1379 between
1380 nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
1381 and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
1382 AND dist.awt_invoice_payment_id = invpay.invoice_payment_id
1383 AND (dist.line_type_lookup_code = 'AWT')
1384 AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
1385 DIST.ACCRUAL_POSTED_FLAG,
1386 DIST.CASH_POSTED_FLAG,
1387 dist.POSTED_FLAG, inv.org_id) in ('Y','P')
1388 AND dist.withholding_tax_code_id = atc.tax_id(+)
1389 AND atc.name = awt.tax_name(+)
1390 AND awt.vendor_id is null
1391 -- Bug 5207771 : Added to remove the duplicates WH lines
1392 AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
1393 OR (dist.awt_tax_rate_id is NULL) )
1394 AND invpay.accounting_date between nvl(awt.start_date, invpay.accounting_date)
1395 AND nvl(awt.end_date, invpay.accounting_date)
1396 AND inv.doc_sequence_id = seq.doc_sequence_id(+)
1397 AND EXISTS (select dist2.invoice_id
1398 from ap_invoice_distributions_all dist2
1399 where inv.invoice_id = dist2.invoice_id
1400 and dist2.line_type_lookup_code = 'AWT'
1401 and dist2.withholding_tax_code_id in
1402 -- Bug 2019586: Column name should be tax_id.
1403 -- (select tax_code_id from ap_tax_codes
1404 (select tax_id
1405 from ap_tax_codes_all
1406 where vat_transaction_type = p_wht_tax_type))
1407 AND NOT EXISTS ( select dist2.invoice_id
1408 from ap_invoice_distributions_all dist2
1409 where inv.invoice_id = dist2.invoice_id
1410 and dist2.line_type_lookup_code = 'AWT'
1411 and dist2.awt_flag <> 'A')
1412 UNION
1413 SELECT 'M',
1414 decode(nvl(v.employee_id,-1),-1,'G','A'),
1415 decode(nvl(v.employee_id,-1),-1,'01','00'),
1416 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1417 v.vendor_name,
1418 inv.awt_flag,
1419 inv.payment_status_flag,
1420 inv.invoice_id,
1421 inv.invoice_num,
1422 nvl(inv.base_amount,inv.invoice_amount) invoice_amount,
1423 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) net_amount,
1424 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
1425 nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) prepaid_amount,
1426 nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) invoice_withheld_amount,
1427 decode(seq.name || '-' ||
1428 to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
1429 to_char(inv.doc_sequence_value)),
1430 trunc(inv.invoice_date,'DD'),
1431 invpay.invoice_payment_id,
1432 nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) payments_count,
1433 nvl(invpay.payment_base_amount,invpay.amount),
1434 nvl(invpay.discount_taken,0),
1435 trunc(invpay.accounting_date,'DD'),
1436 0,
1437 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
1438 substr(fl.description,1,3)),
1439 vs.city,
1440 0,
1441 sum(nvl(dist.base_amount,dist.amount)),
1442 awt.tax_rate,
1443 awt.tax_name
1444 FROM fnd_lookups fl,
1445 po_vendors v,
1446 po_vendor_sites_all vs,
1447 ap_invoice_payments_all invpay,
1448 ap_checks_all checks,
1449 ap_tax_codes_all atc,
1450 ap_awt_tax_rates_all awt,
1451 fnd_document_sequences seq,
1452 ap_invoices_all inv,
1453 ap_invoice_lines_all line,
1454 ap_invoice_distributions_all dist,
1455 (SELECT distinct person_id
1456 ,national_identifier
1457 FROM PER_ALL_PEOPLE_F) papf
1458 WHERE vs.country = fl.lookup_code(+)
1459 AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
1460 AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
1461 AND nvl(v.employee_id,-99) = papf.person_id (+)
1462 AND inv.vendor_id = v.vendor_id
1463 AND v.vendor_id = vs.vendor_id
1464 and vs.vendor_site_id = inv.vendor_site_id
1465 AND vs.tax_reporting_site_flag = 'Y'
1466 and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
1467 -- bug 5207771: Removed org_id condition
1468 --and inv.org_id = nvl(p_org_id,inv.org_id)
1469 and inv.invoice_id = line.invoice_id
1470 and dist.invoice_id = line.invoice_id
1471 and dist.invoice_line_number = line.line_number
1472 ---and inv.cancelled_date is null -- Bug 2228008
1473 AND dist.parent_reversal_id is null
1474 AND not exists ( select 1
1475 from ap_invoice_distributions dist1, gl_period_statuses gl
1476 where gl.application_id = 101
1477 and dist1.invoice_id = inv.invoice_id
1478 and dist1.parent_reversal_id = dist.invoice_distribution_id
1479 and gl.ledger_id = dist1.set_of_books_id
1480 and dist.accounting_date between gl.start_date and gl.end_date
1481 and dist1.accounting_date <= gl.end_date )
1482 AND inv.invoice_id = invpay.invoice_id
1483 AND ( invpay.posted_flag in ('Y','P')
1484 or invpay.cash_posted_flag in ('Y','P')
1485 or invpay.accrual_posted_flag in ('Y','P'))
1486 AND invpay.check_id = checks.check_id
1487 AND checks.void_date is null
1488 AND trunc(invpay.accounting_date,'DD')
1489 between
1490 nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
1491 and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
1492 AND (dist.line_type_lookup_code = 'AWT')
1493 AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
1494 DIST.ACCRUAL_POSTED_FLAG,
1495 DIST.CASH_POSTED_FLAG,
1496 dist.POSTED_FLAG, inv.org_id) in ('Y','P')
1497 AND dist.withholding_tax_code_id = atc.tax_id(+)
1498 AND atc.name = awt.tax_name(+)
1499 AND awt.vendor_id is null
1500 -- Bug 5207771 : Added to remove the duplicates WH lines
1501 AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
1502 OR (dist.awt_tax_rate_id is NULL) )
1503 AND invpay.accounting_date between nvl(awt.start_date, invpay.accounting_date)
1504 AND nvl(awt.end_date, invpay.accounting_date)
1505 AND inv.doc_sequence_id = seq.doc_sequence_id(+)
1506 AND EXISTS (select dist2.invoice_id
1507 from ap_invoice_distributions_all dist2
1508 where inv.invoice_id = dist2.invoice_id
1509 and dist2.line_type_lookup_code = 'AWT'
1510 and dist2.withholding_tax_code_id in
1511 -- Bug 2019586: Column name should be tax_id.
1512 -- (select tax_code_id from ap_tax_codes
1513 (select tax_id
1514 from ap_tax_codes_all
1515 where vat_transaction_type = p_wht_tax_type))
1516 AND EXISTS ( select dist2.invoice_id
1517 from ap_invoice_distributions_all dist2
1518 where inv.invoice_id = dist2.invoice_id
1519 and dist2.line_type_lookup_code = 'AWT'
1520 and dist2.awt_flag <> 'A')
1521 GROUP BY 'M',
1522 decode(nvl(v.employee_id,-1),-1,'G','A'),
1523 decode(nvl(v.employee_id,-1),-1,'01','00'),
1524 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1525 v.vendor_name,
1526 inv.awt_flag,
1527 inv.payment_status_flag,
1528 inv.invoice_id,
1529 inv.invoice_num,
1530 nvl(inv.base_amount,inv.invoice_amount),
1531 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) ,
1532 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) ,
1533 nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) ,
1534 nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) ,
1535 decode(seq.name || '-' ||
1536 to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
1537 to_char(inv.doc_sequence_value)),
1538 trunc(inv.invoice_date,'DD'),
1539 invpay.invoice_payment_id,
1540 nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0),
1541 nvl(invpay.payment_base_amount,invpay.amount),
1542 nvl(invpay.discount_taken,0),
1543 trunc(invpay.accounting_date,'DD'),
1544 0,
1545 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
1546 substr(fl.description,1,3)),
1547 vs.city,
1548 0,
1549 awt.tax_rate,
1550 awt.tax_name
1551 HAVING ((sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0) or (min(awt.tax_rate) = 0))
1552 -- Bug 1212074
1553 -- BUG 3930123 : Adding one more select clause with certain modifications, to fetch
1554 -- the invoices that are cancelled in different accounting period.
1555 -- spanugan 17/12/2004
1556 UNION
1557 SELECT 'M',
1558 decode(nvl(v.employee_id,-1),-1,'G','A'),
1559 decode(nvl(v.employee_id,-1),-1,'01','00'),
1560 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1561 v.vendor_name,
1562 inv.awt_flag,
1563 inv.payment_status_flag,
1564 inv.invoice_id,
1565 inv.invoice_num,
1566 nvl(inv.base_amount,inv.invoice_amount) invoice_amount,
1567 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) net_amount,
1568 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
1569 nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) prepaid_amount,
1570 nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) invoice_withheld_amount,
1571 decode(seq.name || '-' ||
1572 to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
1573 to_char(inv.doc_sequence_value)),
1574 trunc(inv.invoice_date,'DD'),
1575 invpay.invoice_payment_id,
1576 nvl(je_es_whtax.get_payments_count( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0) payments_count,
1577 nvl(invpay.payment_base_amount,invpay.amount),
1578 nvl(invpay.discount_taken,0),
1579 trunc(invpay.accounting_date,'DD'),
1580 0,
1581 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
1582 substr(fl.description,1,3)),
1583 vs.city,
1584 0,
1585 sum(nvl(dist.base_amount,dist.amount)),
1586 awt.tax_rate,
1587 awt.tax_name
1588 FROM fnd_lookups fl,
1589 po_vendors v,
1590 po_vendor_sites_all vs,
1591 ap_invoice_payments_all invpay,
1592 ap_checks_all checks,
1593 ap_tax_codes_all atc,
1594 ap_awt_tax_rates_all awt,
1595 fnd_document_sequences seq,
1596 ap_invoices_all inv,
1597 ap_invoice_lines_all line,
1598 ap_invoice_distributions_all dist,
1599 (SELECT distinct person_id
1600 ,national_identifier
1601 FROM PER_ALL_PEOPLE_F) papf
1602 WHERE vs.country = fl.lookup_code(+)
1603 AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
1604 AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
1605 AND nvl(v.employee_id,-99) = papf.person_id (+)
1606 AND inv.vendor_id = v.vendor_id
1607 AND v.vendor_id = vs.vendor_id
1608 and vs.vendor_site_id = inv.vendor_site_id
1609 AND vs.tax_reporting_site_flag = 'Y'
1610 and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
1611 -- bug 5207771: Removed org_id condition
1612 --and inv.org_id = nvl(p_org_id,inv.org_id)
1613 and inv.invoice_id = line.invoice_id
1614 and dist.invoice_id = line.invoice_id
1615 and dist.invoice_line_number = line.line_number
1616 -- BUG 3930123 : spanugan
1617 /*AND inv.cancelled_date is not null
1618 AND
1619 (
1620 (dist.cancellation_flag is null
1621 AND dist.accounting_date < (select distinct gl.start_date
1622 from gl_period_statuses gl
1623 where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
1624 from ap_invoice_distributions_all dist1
1625 where dist1.invoice_id = inv.invoice_id
1626 and dist1.cancellation_flag = 'Y' )))
1627 OR
1628 (dist.cancellation_flag = 'Y'
1629 AND dist.accounting_date > (select distinct gl.end_date
1630 from gl_period_statuses gl
1631 where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
1632 from ap_invoice_distributions_all dist1
1633 where dist1.invoice_id = inv.invoice_id
1634 and dist1.cancellation_flag is null )))
1635 )
1636 -- END
1637 */
1638 AND dist.parent_reversal_id is not null
1639 AND dist.accounting_date > (select distinct gl.end_date
1640 from ap_invoice_distributions dist1, gl_period_statuses gl
1641 where gl.application_id = 101
1642 and dist1.invoice_id = inv.invoice_id
1643 and dist.parent_reversal_id = dist1.invoice_distribution_id
1644 and gl.ledger_id = dist1.set_of_books_id
1645 and dist1.accounting_date between gl.start_date and gl.end_date)
1646 AND inv.invoice_id = invpay.invoice_id
1647 AND ( invpay.posted_flag in ('Y','P')
1648 or invpay.cash_posted_flag in ('Y','P')
1649 or invpay.accrual_posted_flag in ('Y','P'))
1650 AND invpay.check_id = checks.check_id
1651 AND checks.void_date is null
1652 AND trunc(invpay.accounting_date,'DD')
1653 between
1654 nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
1655 and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
1656 AND (dist.line_type_lookup_code = 'AWT')
1657 AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
1658 DIST.ACCRUAL_POSTED_FLAG,
1659 DIST.CASH_POSTED_FLAG,
1660 dist.POSTED_FLAG, inv.org_id) in ('Y','P')
1661 AND dist.withholding_tax_code_id = atc.tax_id(+)
1662 AND atc.name = awt.tax_name(+)
1663 AND awt.vendor_id is null
1664 -- Bug 5207771 : Added to remove the duplicates WH lines
1665 AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
1666 OR (dist.awt_tax_rate_id is NULL) )
1667 AND invpay.accounting_date between nvl(awt.start_date, invpay.accounting_date)
1668 AND nvl(awt.end_date, invpay.accounting_date)
1669 AND inv.doc_sequence_id = seq.doc_sequence_id(+)
1670 AND EXISTS (select dist2.invoice_id
1671 from ap_invoice_distributions_all dist2
1672 where inv.invoice_id = dist2.invoice_id
1673 and dist2.line_type_lookup_code = 'AWT'
1674 and dist2.withholding_tax_code_id in
1675 -- Bug 2019586: Column name should be tax_id.
1676 -- (select tax_code_id from ap_tax_codes
1677 (select tax_id
1678 from ap_tax_codes_all
1679 where vat_transaction_type = p_wht_tax_type))
1680 AND EXISTS ( select dist2.invoice_id
1681 from ap_invoice_distributions_all dist2
1682 where inv.invoice_id = dist2.invoice_id
1683 and dist2.line_type_lookup_code = 'AWT'
1684 and dist2.awt_flag <> 'A')
1685 GROUP BY 'M',
1686 decode(nvl(v.employee_id,-1),-1,'G','A'),
1687 decode(nvl(v.employee_id,-1),-1,'01','00'),
1688 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1689 v.vendor_name,
1690 inv.awt_flag,
1691 inv.payment_status_flag,
1692 inv.invoice_id,
1693 inv.invoice_num,
1694 nvl(inv.base_amount,inv.invoice_amount),
1695 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0),
1696 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0),
1697 nvl(je_es_whtax.GET_PREPAID_AMOUNT( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) ,
1698 nvl(je_es_whtax.GET_AMOUNT_WITHHELD( inv.INVOICE_ID,inv.org_id,inv.legal_entity_id),0) ,
1699 decode(seq.name || '-' ||
1700 to_char(inv.doc_sequence_value),'-',null,seq.name || '-' ||
1701 to_char(inv.doc_sequence_value)),
1702 trunc(inv.invoice_date,'DD'),
1703 invpay.invoice_payment_id,
1704 nvl(je_es_whtax.get_payments_count(inv.INVOICE_ID,inv.legal_entity_id,inv.org_id),0),
1705 nvl(invpay.payment_base_amount,invpay.amount),
1706 nvl(invpay.discount_taken,0),
1707 trunc(invpay.accounting_date,'DD'),
1708 0,
1709 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||
1710 substr(fl.description,1,3)),
1711 vs.city,
1712 0,
1713 awt.tax_rate,
1714 awt.tax_name
1715 HAVING ((sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0) or (min(awt.tax_rate) = 0));
1716 -- Bug 1212074
1717 --
1718 -- Detailed APPROVED transactions. This is used for Detail and Summary
1719 -- transactions extract Hard Copy Report.
1720 --
1721 CURSOR detail_approve IS
1722 SELECT decode(nvl(v.employee_id,-1),-1,'G','A'),
1723 decode(nvl(v.employee_id,-1),-1,'01','00'),
1724 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1725 substr(v.vendor_name,1,80), -- AP UTF8 Changes 2398166
1726 nvl(inv.base_amount,inv.invoice_amount),
1727 decode(seq.name || '-' || to_char(inv.doc_sequence_value),'-',null,
1728 seq.name || '-' || to_char(inv.doc_sequence_value)),
1729 inv.invoice_id,
1730 inv.invoice_num,
1731 trunc(inv.invoice_date,'DD'),
1732 trunc(dist.accounting_date,'DD'),
1733 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) net_amount,
1734 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
1735 sum(nvl(dist.base_amount,dist.amount)) withholding_tax_amount,
1736 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3)),
1737 vs.city,
1738 awt.tax_rate,
1739 awt.tax_name
1740 FROM fnd_lookups fl,
1741 po_vendors v,
1742 po_vendor_sites_all vs,
1743 ap_tax_codes_all atc,
1744 ap_awt_tax_rates_all awt,
1745 fnd_document_sequences seq,
1746 ap_invoices_all inv,
1747 ap_invoice_lines_all line,
1748 ap_invoice_distributions_all dist,
1749 (SELECT distinct person_id
1750 ,national_identifier
1751 FROM PER_ALL_PEOPLE_F) papf
1752 WHERE vs.country = fl.lookup_code(+)
1753 AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
1754 AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
1755 AND nvl(v.employee_id,-99) = papf.person_id (+)
1756 AND inv.vendor_id = v.vendor_id
1757 AND v.vendor_id = vs.vendor_id
1758 and vs.vendor_site_id = inv.vendor_site_id
1759 AND vs.tax_reporting_site_flag = 'Y'
1760 and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
1761 -- bug 5207771: Removed org_id condition
1762 --and inv.org_id = nvl(p_org_id,inv.org_id)
1763 and inv.invoice_id = line.invoice_id
1764 and dist.invoice_id = line.invoice_id
1765 and dist.invoice_line_number = line.line_number
1766 ---and inv.cancelled_date is null -- Bug 2228008
1767 AND dist.parent_reversal_id is null
1768 AND not exists ( select 1
1769 from ap_invoice_distributions dist1, gl_period_statuses gl
1770 where gl.application_id = 101
1771 and dist1.invoice_id = inv.invoice_id
1772 and dist1.parent_reversal_id = dist.invoice_distribution_id
1773 and gl.ledger_id = dist1.set_of_books_id
1774 and dist.accounting_date between gl.start_date and gl.end_date
1775 and dist1.accounting_date <= gl.end_date )
1776 AND trunc(dist.accounting_date,'DD')
1777 between fnd_date.canonical_to_date(P_Date_From)
1778 and fnd_date.canonical_to_date(P_Date_To)
1779 AND dist.line_type_lookup_code = 'AWT'
1780 AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
1781 DIST.ACCRUAL_POSTED_FLAG,
1782 DIST.CASH_POSTED_FLAG,
1783 dist.POSTED_FLAG, inv.org_id) in ('Y','P')
1784 AND dist.withholding_tax_code_id = atc.tax_id(+)
1785 AND atc.name = awt.tax_name(+)
1786 AND dist.accounting_date
1787 between nvl(awt.start_date, dist.accounting_date)
1788 and nvl(awt.end_date, dist.accounting_date)
1789 AND awt.vendor_id is null /* Ignore any Vendor Lines */
1790 -- Bug 5207771 : Added to remove the duplicates WH lines
1791 AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
1792 OR (dist.awt_tax_rate_id is NULL) )
1793 AND inv.doc_sequence_id = seq.doc_sequence_id(+)
1794 -- Ignore any invoices which do not have 'AWT' distribution lines
1795 AND EXISTS ( select dist2.invoice_id
1796 from ap_invoice_distributions_all dist2
1797 where inv.invoice_id = dist2.invoice_id
1798 and dist2.line_type_lookup_code = 'AWT'
1799 and dist2.withholding_tax_code_id in
1800 -- Bug 2019586: Column name should be tax_id.
1801 -- (select tax_code_id from ap_tax_codes
1802 (select tax_id from ap_tax_codes_all
1803 where vat_transaction_type = p_wht_tax_type))
1804 GROUP BY decode(nvl(v.employee_id,-1),-1,'G','A'),
1805 decode(nvl(v.employee_id,-1),-1,'01','00'),
1806 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1807 substr(v.vendor_name,1,80),
1808 nvl(inv.base_amount,inv.invoice_amount),
1809 decode(seq.name || '-' || to_char(inv.doc_sequence_value),'-', null,
1810 seq.name || '-' || to_char(inv.doc_sequence_value)),
1811 inv.invoice_id,
1812 inv.invoice_num,
1813 trunc(inv.invoice_date,'DD'),
1814 trunc(dist.accounting_date,'DD'),
1815 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) ,
1816 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) ,
1817 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3)),
1818 vs.city,
1819 awt.tax_rate,
1820 awt.tax_name
1821 HAVING ((sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0) or (min(awt.tax_rate) = 0))
1822 -- Bug 1212074
1823 -- BUG 3930123 : Adding one more select clause with certain modifications, to fetch
1824 -- the invoices that are cancelled in different accounting period.
1825 -- spanugan 17/12/2004
1826 UNION
1827 SELECT decode(nvl(v.employee_id,-1),-1,'G','A'),
1828 decode(nvl(v.employee_id,-1),-1,'01','00'),
1829 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1830 substr(v.vendor_name,1,80), -- AP UTF8 Changes 2398166
1831 nvl(inv.base_amount,inv.invoice_amount),
1832 decode(seq.name || '-' || to_char(inv.doc_sequence_value),'-',null,
1833 seq.name || '-' || to_char(inv.doc_sequence_value)),
1834 inv.invoice_id,
1835 inv.invoice_num,
1836 trunc(inv.invoice_date,'DD'),
1837 trunc(dist.accounting_date,'DD'),
1838 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) net_amount,
1839 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) net_amount,
1840 sum(nvl(dist.base_amount,dist.amount)) withholding_tax_amount,
1841 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3)),
1842 vs.city,
1843 awt.tax_rate,
1844 awt.tax_name
1845 FROM fnd_lookups fl,
1846 po_vendors v,
1847 po_vendor_sites_all vs,
1848 ap_tax_codes_all atc,
1849 ap_awt_tax_rates_all awt,
1850 fnd_document_sequences seq,
1851 ap_invoices_all inv,
1852 ap_invoice_lines_all line,
1853 ap_invoice_distributions_all dist,
1854 (SELECT distinct person_id
1855 ,national_identifier
1856 FROM PER_ALL_PEOPLE_F) papf
1857 WHERE vs.country = fl.lookup_code(+)
1858 AND 'JEES_EURO_COUNTRY_CODES' = fl.lookup_type
1859 AND (( p_vendor_id is null and v.vendor_id = v.vendor_id) or (v.vendor_id = p_vendor_id))
1860 AND nvl(v.employee_id,-99) = papf.person_id (+)
1861 AND inv.vendor_id = v.vendor_id
1862 AND v.vendor_id = vs.vendor_id
1863 and vs.vendor_site_id = inv.vendor_site_id
1864 AND vs.tax_reporting_site_flag = 'Y'
1865 and inv.legal_entity_id = nvl(p_legal_entity_id,inv.legal_entity_id)
1866 -- bug 5207771: Removed org_id condition
1867 --and inv.org_id = nvl(p_org_id,inv.org_id)
1868 and inv.invoice_id = line.invoice_id
1869 and dist.invoice_id = line.invoice_id
1870 and dist.invoice_line_number = line.line_number
1871 -- BUG 3930123 : spanugan
1872 /*AND inv.cancelled_date is not null
1873 AND
1874 (
1875 (dist.cancellation_flag is null
1876 AND dist.accounting_date < (select distinct gl.start_date
1877 from gl_period_statuses gl
1878 where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
1879 from ap_invoice_distributions_all dist1
1880 where dist1.invoice_id = inv.invoice_id
1881 and dist1.cancellation_flag = 'Y' )))
1882 OR
1883 (dist.cancellation_flag = 'Y'
1884 AND dist.accounting_date > (select distinct gl.end_date
1885 from gl_period_statuses gl
1886 where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
1887 from ap_invoice_distributions_all dist1
1888 where dist1.invoice_id = inv.invoice_id
1889 and dist1.cancellation_flag is null )))
1890 )
1891 -- END
1892 */
1893 AND dist.parent_reversal_id is not null
1894 AND dist.accounting_date > (select distinct gl.end_date
1895 from ap_invoice_distributions dist1, gl_period_statuses gl
1896 where gl.application_id = 101
1897 and dist1.invoice_id = inv.invoice_id
1898 and dist.parent_reversal_id = dist1.invoice_distribution_id
1899 and gl.ledger_id = dist1.set_of_books_id
1900 and dist1.accounting_date between gl.start_date and gl.end_date)
1901 AND trunc(dist.accounting_date,'DD')
1902 between fnd_date.canonical_to_date(P_Date_From)
1903 and fnd_date.canonical_to_date(P_Date_To)
1904 AND dist.line_type_lookup_code = 'AWT'
1905 AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
1906 DIST.ACCRUAL_POSTED_FLAG,
1907 DIST.CASH_POSTED_FLAG,
1908 dist.POSTED_FLAG, inv.org_id) in ('Y','P')
1909 AND dist.withholding_tax_code_id = atc.tax_id(+)
1910 AND atc.name = awt.tax_name(+)
1911 AND dist.accounting_date
1912 between nvl(awt.start_date, dist.accounting_date)
1913 and nvl(awt.end_date, dist.accounting_date)
1914 AND awt.vendor_id is null /* Ignore any Vendor Lines */
1915 -- Bug 5207771 : Added to remove the duplicates WH lines
1916 AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
1917 OR (dist.awt_tax_rate_id is NULL) )
1918 AND inv.doc_sequence_id = seq.doc_sequence_id(+)
1919 -- Ignore any invoices which do not have 'AWT' distribution lines
1920 AND EXISTS ( select dist2.invoice_id
1921 from ap_invoice_distributions_all dist2
1922 where inv.invoice_id = dist2.invoice_id
1923 and dist2.line_type_lookup_code = 'AWT'
1924 and dist2.withholding_tax_code_id in
1925 -- Bug 2019586: Column name should be tax_id.
1926 -- (select tax_code_id from ap_tax_codes
1927 (select tax_id from ap_tax_codes_all
1928 where vat_transaction_type = p_wht_tax_type))
1929 GROUP BY decode(nvl(v.employee_id,-1),-1,'G','A'),
1930 decode(nvl(v.employee_id,-1),-1,'01','00'),
1931 nvl(substr(nvl(papf.national_identifier,nvl(v.individual_1099,v.num_1099)),1,9),' '),
1932 substr(v.vendor_name,1,80),
1933 nvl(inv.base_amount,inv.invoice_amount),
1934 decode(seq.name || '-' || to_char(inv.doc_sequence_value),'-', null,
1935 seq.name || '-' || to_char(inv.doc_sequence_value)),
1936 inv.invoice_id,
1937 inv.invoice_num,
1938 trunc(inv.invoice_date,'DD'),
1939 trunc(dist.accounting_date,'DD'),
1940 -- nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID),0) ,
1941 nvl(je_es_whtax.get_awt_net_total( inv.INVOICE_ID,inv.legal_entity_id,inv.org_id,dist.accounting_date),0) ,
1942 decode(vs.country,'ES',substr(vs.zip,1,2)||'000','98'||substr(fl.description,1,3)),
1943 vs.city,
1944 awt.tax_rate,
1945 awt.tax_name
1946 HAVING ((sum(decode(dist.line_type_lookup_code,'AWT',nvl(dist.base_amount,dist.amount),0)) <> 0) or (min(awt.tax_rate) = 0));
1947
1948 -- Bug 1212074
1949 -- Bug 1271489: Fetch correct awt rate and name for a given invoice.
1950 PROCEDURE fetch_awt_line( p_fetch_pi_flag IN varchar2,
1951 p_fetch_invoice_id IN number,
1952 p_fetch_wht_amount IN number,
1953 date_paid1 IN DATE, -- Bug 3930123 : Spanugan 23/12/2004
1954 p_fetch_tax_rate IN OUT NOCOPY number,
1955 p_fetch_tax_name IN OUT NOCOPY varchar2,
1956 p_legal_entity_id IN number,
1957 p_org_id IN number) is
1958 l_tax_code_id ap_tax_codes.tax_id%TYPE;
1959 l_invoice_num ap_invoices.invoice_num%TYPE;
1960 l_tax_name JE_ES_MODELO_190_ALL.tax_name%TYPE;
1961 l_tax_rate JE_ES_MODELO_190_ALL.tax_rate%TYPE;
1962 l_accounting_date ap_invoice_distributions_all.accounting_date%TYPE;
1963 begin
1964 begin
1965 if p_fetch_pi_flag = 'P' then
1966 select min(dist.withholding_tax_code_id), max(invpay.accounting_date)
1967 into l_tax_code_id, l_accounting_date
1968 from ap_invoice_payments_all invpay,
1969 ap_invoice_distributions_all dist
1970 where dist.invoice_id = p_fetch_invoice_id
1971 and invpay.invoice_id = dist.invoice_id
1972 and dist.line_type_lookup_code = 'AWT'
1973 -- Bug 5207771
1974 -- and dist.accounting_date = date_paid1 -- Bug 3930217 : Spanugan 23/12/2004
1975 group by withholding_tax_code_id
1976 having /*((mod(count(dist.withholding_tax_code_id),2) = 1) and*/ --Bug 3930217
1977 (sum(decode(dist.line_type_lookup_code,'AWT',
1978 nvl(dist.base_amount,dist.amount),0)) = p_fetch_wht_amount);
1979 else -- p_fetch_pi_flag <> 'P
1980 select min(withholding_tax_code_id), max(accounting_date)
1981 into l_tax_code_id, l_accounting_date
1982 from ap_invoice_distributions_all
1983 where invoice_id = p_fetch_invoice_id
1984 and line_type_lookup_code = 'AWT'
1985 -- Bug 5207771
1986 -- and accounting_date = date_paid1 -- Bug 3930217 : Spanugan 23/12/2004
1987 group by withholding_tax_code_id
1988 having /*((mod(count(withholding_tax_code_id),2) = 1) and*/ --Bug 3930217
1989 (sum(decode(line_type_lookup_code,'AWT',
1990 nvl(base_amount,amount),0)) = p_fetch_wht_amount)
1991 ;
1992 end if;
1993 exception
1994 when OTHERS then
1995 select invoice_num into l_invoice_num
1996 from ap_invoices_all
1997 where invoice_id = p_fetch_invoice_id
1998 and legal_entity_id = nvl(p_legal_entity_id, legal_entity_id);
1999 -- bug 5207771: Removed org_id condition
2000 --and inv.org_id = nvl(p_org_id,inv.org_id);
2001 dbmsmsg('Wrong number of withholding tax lines in invoice '||l_invoice_num||'.');
2002 raise bad_awt_lines;
2003 end;
2004 begin
2005 select awt.tax_rate, awt.tax_name
2006 into l_tax_rate, l_tax_name
2007 from ap_tax_codes_all atc, ap_awt_tax_rates_all awt
2008 where atc.name = awt.tax_name(+)
2009 and atc.tax_id = l_tax_code_id
2010 and l_accounting_date between nvl(awt.start_date,l_accounting_date)
2011 and nvl(awt.end_date,l_accounting_date);
2012 exception
2013 when OTHERS then
2014 select invoice_num into l_invoice_num
2015 from ap_invoices_all
2016 where invoice_id = p_fetch_invoice_id
2017 and legal_entity_id = nvl(p_legal_entity_id,legal_entity_id);
2018 -- bug 5207771: Removed org_id condition
2019 --and inv.org_id = nvl(p_org_id,inv.org_id);
2020 dbmsmsg('The tax name for withholding tax line of invoice '||l_invoice_num|| ' is an incorrect one.');
2021 raise bad_awt_lines;
2022 end;
2023 p_fetch_tax_rate := nvl(l_tax_rate,p_fetch_tax_rate);
2024 p_fetch_tax_name := nvl(l_tax_name,p_fetch_tax_name);
2025 end;
2026 BEGIN
2027 fnd_file.put_line( fnd_file.log,'Parameters :');
2028 fnd_file.put_line( fnd_file.log,'Selection Criteria : ' || p_pay_inv_sel );
2029 fnd_file.put_line( fnd_file.log,'Summary Report : ' || p_summary );
2030 fnd_file.put_line( fnd_file.log,'Date From : ' || p_date_from );
2031 fnd_file.put_line( fnd_file.log,'Date To : ' || p_date_to );
2032 fnd_file.put_line( fnd_file.log,'Tax Type : ' || p_wht_tax_type );
2033 fnd_file.put_line( fnd_file.log,'Legal Entity id : ' || p_legal_entity_id );
2034 fnd_file.put_line( fnd_file.log,'Organization id : ' || p_org_id );
2035 fnd_file.put_line( fnd_file.log,' ');
2036 -- Added for bug 5277700.
2037 SELECT COUNT(*)
2038 INTO l_le_id_count
2039 FROM je_es_modelo_190_all
2040 WHERE legal_entity_id IS NULL;
2041 IF l_le_id_count > 0 THEN
2042 je_es_mod_le_update.update_main;
2043 /* fnd_message.set_name('JE', 'JE_WHT_LEGAL_ENTITY_ID_UPG');
2044 fnd_message.set_token('TABLE', 'JE_ES_MODELO_190_ALL');
2045 l_le_id_message := fnd_message.get;
2046 errbuf := l_le_id_message;
2047 retcode := -1;
2048 RETURN;
2049 */
2050 END IF;
2051 /* Get the functional currency and precision */
2052 -- l_ledger_id :=FND_PROFILE.value('gl_set_of_bks_id');
2053
2054 SELECT p.currency_code,
2055 c.precision
2056 INTO func_curr,
2057 func_curr_precision
2058 FROM gl_ledgers p,
2059 fnd_currencies_vl c
2060 WHERE p.currency_code = c.currency_code
2061 AND p.ledger_id = (select distinct primary_ledger_id
2062 from gl_ledger_le_v
2063 where legal_entity_id = p_legal_entity_id);
2064
2065 if p_hard_copy = 'N' then
2066 /* Deal with ELECTRONIC transactions */
2067 plsqlmsg('WITHHOLDING TAX MAGNETIC REPORT - Transfer Data');
2068 del_trans_m(p_legal_entity_id => p_legal_entity_id,
2069 p_org_id => p_org_id);
2070 plsqlmsg('Deleted Existing Rows');
2071 if p_summary = 'Y' then
2072 /* Deal with SUMMARY transactions */
2073 if p_pay_inv_sel = 'P' then
2074 /* Deal with PAID transactions */
2075 countrecs := 0;
2076 plsqlmsg('Opened CURSOR detail_paid for summary paid electronic');
2077 OPEN detail_paid;
2078 LOOP
2079 FETCH detail_paid
2080 INTO wht_mode,
2081 remun_type1,
2082 sub_remun_type1,
2083 vendor_nif1,
2084 vendor_name1,
2085 inv_awt_flag,
2086 inv_payment_status_flag,
2087 invoice_id1,
2088 invoice_num1,
2089 invoice_amount,
2090 net_amount1,
2091 invoice_prepaid_amount,
2092 invoice_withheld_amount,
2093 inv_doc_seq_num1,
2094 invoice_date1,
2095 invoice_payment_id1,
2096 invoice_payments_count,
2097 paid_amount,
2098 discount_amount,
2099 date_paid1,
2100 awt_invoice_payment_id,
2101 zip_legal1,
2102 city_legal1,
2103 wht_net_amount1,
2104 withholding_tax_amount1,
2105 tax_rate1,
2106 tax_name1;
2107 EXIT WHEN detail_paid%NOTFOUND;
2108 first_record := first_record + 1;
2109 -- fnd_file.put_line(fnd_file.log,'In Magnetic');
2110 -- Retain Old data
2111 if ( first_record = 1 ) then
2112 old_remun_type := remun_type1;
2113 old_sub_remun_type := sub_remun_type1;
2114 old_vendor_nif := vendor_nif1;
2115 old_vendor_name := vendor_name1;
2116 old_zip_electronic := zip_electronic1;
2117 old_zip_legal := zip_legal1 ;
2118 end if;
2119 -- Automatic Withholding
2120 -- Withholding calculated at invoice payment time.
2121 if ( wht_mode = 'A' ) then
2122 if(nvl(inv_awt_flag,'N') = 'N') then
2123 net_amount1 := round(wht_net_amount1,func_curr_precision);
2124 withholding_tax_amount1 := round(withholding_tax_amount1,
2125 func_curr_precision);
2126 else -- if (nvl(inv_awt_flag,'N') = 'Y')
2127 if (nvl(inv_payment_status_flag,'N') = 'Y') then
2128 if (invoice_payments_count = 1 ) then
2129 net_amount1 := round(net_amount1,func_curr_precision);
2130 withholding_tax_amount1 := round(withholding_tax_amount1, func_curr_precision);
2131 elsif ( invoice_payments_count > 1 ) then
2132 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2133 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);
2134 end if;
2135 else -- if nvl(inv_payment_status_flag,'N') = 'N'
2136 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2137 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);
2138 end if;
2139 end if;
2140 end if;
2141 -- Manuali+Automatic Withholding
2142 -- Withholding calculated at invoice payment time.
2143 -- Withholding calculated at approval time.
2144 if ( wht_mode = 'M' ) then
2145 if (nvl(inv_payment_status_flag,'N') = 'Y') then
2146 if (invoice_payments_count = 1 ) then
2147 net_amount1 := round(net_amount1,func_curr_precision);
2148 withholding_tax_amount1 := round(withholding_tax_amount1, func_curr_precision);
2149 elsif ( invoice_payments_count > 1 ) then
2150 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2151 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);
2152 end if; -- if invoice_payments_count =1 or >1
2153 else -- if nvl(inv_payment_status_flag,'N') = 'N'
2154 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2155 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);
2156 end if; -- if nvl(inv_payment_status_flag,'N') = 'Y' or 'N'
2157 end if; -- if wht_mode = 'M'
2158 if ( (nvl(remun_type1,'X') = nvl(old_remun_type,'X')) AND
2159 (nvl(sub_remun_type1,'X') = nvl(old_sub_remun_type,'X')) AND
2160 (nvl(vendor_nif1,'X') = nvl(old_vendor_nif,'X')) AND
2161 (nvl(vendor_name1,'X') = nvl(old_vendor_name,'X')) AND
2162 (nvl(zip_electronic1,'X') = nvl(old_zip_electronic,'X')) AND
2163 (nvl(zip_legal1,'X') = nvl(old_zip_legal,'X')) ) then
2164 inv_net_amount1 := nvl(inv_net_amount1,0) + net_amount1;
2165 inv_wht_amount1 := nvl(inv_wht_amount1,0) + withholding_tax_amount1;
2166 else
2167 old_remun_type := remun_type1;
2168 old_sub_remun_type := sub_remun_type1;
2169 old_vendor_nif := vendor_nif1;
2170 old_vendor_name := vendor_name1;
2171 old_zip_electronic := zip_electronic1;
2172 old_zip_legal := zip_legal1 ;
2173 if sign(inv_net_amount1) = -1 then
2174 sign1 := 'N';
2175 else
2176 sign1:= '';
2177 end if;
2178 inv_wht_amount1 := abs(inv_wht_amount1);
2179 inv_net_amount1 := abs(inv_net_amount1);
2180 if ( inv_wht_amount1 <>0 ) then
2181 -- Bug 1212074: Magnetic form does not care about 0 awt.
2182 countrecs := countrecs + 1;
2183 ins_trans(
2184 legal_entity_id => p_legal_entity_id,
2185 org_id => p_org_id,
2186 conc_req_id => p_conc_req_id,
2187 remun_type => remun_type1,
2188 sub_remun_type => sub_remun_type1,
2189 vendor_nif => vendor_nif1,
2190 vendor_name => vendor_name1,
2191 invoice_id => NULL,
2192 invoice_num => NULL,
2193 inv_doc_seq_num => NULL,
2194 invoice_date => NULL,
2195 gl_date => NULL,
2196 invoice_payment_id => NULL,
2197 date_paid => NULL,
2198 net_amount => inv_net_amount1,
2199 withholding_tax_amount => inv_wht_amount1,
2200 zip_electronic => zip_electronic1,
2201 zip_legal => zip_legal1,
2202 city_legal => NULL,
2203 num_children => NULL,
2204 sign => sign1,
2205 tax_rate => NULL,
2206 tax_name => NULL,
2207 year_due => NULL);
2208 end if;
2209 inv_net_amount1 := net_amount1;
2210 inv_wht_amount1 := withholding_tax_amount1;
2211 end if;
2212 END LOOP;
2213 if ( inv_wht_amount1 <>0 AND inv_net_amount1 <> 0 ) then
2214 -- Bug 1212074: Magnetic form does not care about 0 awt.
2215 countrecs := countrecs + 1;
2216 ins_trans( legal_entity_id => p_legal_entity_id,
2217 org_id => p_org_id,
2218 conc_req_id => p_conc_req_id,
2219 remun_type => remun_type1,
2220 sub_remun_type => sub_remun_type1,
2221 vendor_nif => vendor_nif1,
2222 vendor_name => vendor_name1,
2223 invoice_id => NULL,
2224 invoice_num => NULL,
2225 inv_doc_seq_num => NULL,
2226 invoice_date => NULL,
2227 gl_date => NULL,
2228 invoice_payment_id => NULL,
2229 date_paid => NULL,
2230 net_amount => inv_net_amount1,
2231 withholding_tax_amount => inv_wht_amount1,
2232 zip_electronic => zip_electronic1,
2233 zip_legal => zip_legal1,
2234 city_legal => NULL,
2235 num_children => NULL,
2236 sign => sign1,
2237 tax_rate => NULL,
2238 tax_name => NULL,
2239 year_due => NULL);
2240 end if;
2241 plsqlmsg('Data inserted into table JE_ES_MODELO_190_ALL');
2242 CLOSE detail_paid;
2243 plsqlmsg('CURSOR Closed');
2244 plsqlmsg('Routine Successfully completed - ' || to_char(countrecs) || ' rows inserted');
2245 ELSE -- p_pay_inv_sel <> 'P'
2246 /* Deal with APPROVED transactions */
2247 countrecs := 0;
2248 OPEN sum_approve_mag;
2249 plsqlmsg('Opened CURSOR sum_approve_mag');
2250 LOOP
2251 FETCH sum_approve_mag
2252 INTO remun_type1,
2253 sub_remun_type1,
2254 vendor_nif1,
2255 vendor_name1,
2256 zip_electronic1,
2257 net_amount1,
2258 withholding_tax_amount1;
2259 EXIT WHEN sum_approve_mag%NOTFOUND;
2260 net_amount1 := round(net_amount1,func_curr_precision);
2261 withholding_tax_amount1 := round(withholding_tax_amount1,func_curr_precision);
2262 if sign(net_amount1) = -1 then
2263 sign1 := 'N';
2264 else
2265 sign1:= '';
2266 end if;
2267 withholding_tax_amount1 := abs(withholding_tax_amount1);
2268 net_amount1 := abs(net_amount1);
2269 if ( withholding_tax_amount1 <>0 ) then
2270 -- Bug 1212074: Magnetic form does not care about 0 awt.
2271 countrecs := countrecs + 1;
2272 ins_trans( legal_entity_id => p_legal_entity_id,
2273 org_id => p_org_id,
2274 conc_req_id => NULL,
2275 remun_type => remun_type1,
2276 sub_remun_type => sub_remun_type1,
2277 vendor_nif => vendor_nif1,
2278 vendor_name => vendor_name1,
2279 invoice_id => invoice_id1,
2280 invoice_num => invoice_num1,
2281 inv_doc_seq_num => inv_doc_seq_num1,
2282 invoice_date => invoice_date1,
2283 gl_date => gl_date1,
2284 invoice_payment_id => invoice_payment_id1,
2285 date_paid => date_paid1,
2286 net_amount => net_amount1,
2287 withholding_tax_amount => withholding_tax_amount1,
2288 zip_electronic => zip_electronic1,
2289 zip_legal => zip_legal1,
2290 city_legal => city_legal1,
2291 num_children => num_children1,
2292 sign => sign1,
2293 tax_rate => tax_rate1,
2294 tax_name => tax_name1,
2295 year_due => year_due1);
2296 end if;
2297 END LOOP;
2298 plsqlmsg('Data inserted into table JE_ES_MODELO_190_ALL');
2299 CLOSE sum_approve_mag;
2300 plsqlmsg('CURSOR Closed');
2301 plsqlmsg('Routine Successfully completed - ' || to_char(countrecs) || ' rows inserted');
2302 end if; -- p_pay_inv_sel = 'P' or <> 'P'
2303 else -- p_summary <> 'Y'
2304 /* We should NEVER have any DETAIL transactions */
2305 RAISE bad_parameters;
2306 end if; -- if p_summary = 'Y' or <> 'Y'
2307 else -- p_hard_copy = 'Y'
2308 /* Deal with HARD COPY transactions */
2309 plsqlmsg('WITHHOLDING TAX REPORT - Transfer Data for Hard Copy Summary');
2310 del_trans_s(p_conc_req_id => p_conc_req_id,
2311 p_legal_entity_id => p_legal_entity_id,
2312 p_org_id => p_org_id);
2313 plsqlmsg('Deleted Existing Rows');
2314 if p_summary = 'Y' then
2315 /* Deal with SUMMARY transactions */
2316 if p_pay_inv_sel = 'P' then
2317 /* Deal with PAID transactions */
2318 countrecs := 0;
2319 OPEN detail_paid;
2320 plsqlmsg('Opened CURSOR detail_paid');
2321 LOOP
2322 FETCH detail_paid
2323 INTO wht_mode,
2324 remun_type1,
2325 sub_remun_type1,
2326 vendor_nif1,
2327 vendor_name1,
2328 inv_awt_flag,
2329 inv_payment_status_flag,
2330 invoice_id1,
2331 invoice_num1,
2332 invoice_amount,
2333 net_amount1,
2334 invoice_prepaid_amount,
2335 invoice_withheld_amount,
2336 inv_doc_seq_num1,
2337 invoice_date1,
2338 invoice_payment_id1,
2339 invoice_payments_count,
2340 paid_amount,
2341 discount_amount,
2342 date_paid1,
2343 awt_invoice_payment_id,
2344 zip_legal1,
2345 city_legal1,
2346 wht_net_amount1,
2347 withholding_tax_amount1,
2348 tax_rate1,
2349 tax_name1;
2350 EXIT WHEN detail_paid%NOTFOUND;
2351 -- Automatic Withholding
2352 -- Withholding calculated at invoice payment time.
2353 if ( wht_mode = 'A' ) then
2354 if (nvl(inv_awt_flag,'N') = 'N') then
2355 net_amount1 := round(wht_net_amount1,func_curr_precision);
2356 withholding_tax_amount1 := round(withholding_tax_amount1, func_curr_precision);
2357 else -- if (nvl(inv_awt_flag,'N') = 'Y')
2358 if (nvl(inv_payment_status_flag,'N') = 'Y') then
2359 if (invoice_payments_count = 1 ) then
2360 net_amount1 := round(net_amount1,func_curr_precision);
2361 withholding_tax_amount1 := round(withholding_tax_amount1, func_curr_precision);
2362 elsif ( invoice_payments_count > 1 ) then
2363 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2364 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);
2365 end if; -- if invoice_payments_count = 1 or > 1
2366 else -- if nvl(inv_payment_status_flag,'N') = 'N'
2367 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2368 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);
2369 end if; -- if nvl(inv_payment_status_flag,'N') = 'Y' or 'N'
2370 end if; -- if (nvl(inv_awt_flag,'N') = 'N' or 'Y'
2371 end if; -- if wht_mode = 'A'
2372 -- Manuali+Automatic Withholding
2373 -- Withholding calculated at invoice payment time.
2374 -- Withholding calculated at approval time.
2375 if ( wht_mode = 'M' ) then
2376 if (nvl(inv_payment_status_flag,'N') = 'Y') then
2377 if (invoice_payments_count = 1 ) then
2378 net_amount1 := round(net_amount1,func_curr_precision);
2379 withholding_tax_amount1 := round(withholding_tax_amount1, func_curr_precision);
2380 elsif ( invoice_payments_count > 1 ) then
2381 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2382 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);
2383 end if; -- if invoice_payments_count = 1 or > 1
2384 else -- if nvl(inv_payment_status_flag,'N') <> 'Y'
2385 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2386 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);
2387 end if; -- if nvl(inv_payment_status_flag,'N') ='Y' or <> 'Y'
2388 end if; -- if wht_mode = 'M'
2389 -- fnd_file.put_line(fnd_file.log,'Net Amount: '||to_char(net_amount1));
2390 -- fnd_file.put_line(fnd_file.log,'WHT Amount: '||to_char(withholding_tax_amount1));
2391 if ( withholding_tax_amount1 <>0 )
2392 or (tax_rate1 = 0) then -- Bug 1212074
2393 countrecs := countrecs + 1;
2394 ins_trans( legal_entity_id => p_legal_entity_id,
2395 org_id => p_org_id,
2396 conc_req_id => p_conc_req_id,
2397 remun_type => remun_type1,
2398 sub_remun_type => sub_remun_type1,
2399 vendor_nif => vendor_nif1,
2400 vendor_name => vendor_name1,
2401 invoice_id => invoice_id1,
2402 invoice_num => invoice_num1,
2403 inv_doc_seq_num => inv_doc_seq_num1,
2404 invoice_date => invoice_date1,
2405 gl_date => gl_date1,
2406 invoice_payment_id => invoice_payment_id1,
2407 date_paid => date_paid1,
2408 net_amount => net_amount1,
2409 withholding_tax_amount => withholding_tax_amount1,
2410 zip_electronic => zip_electronic1,
2411 zip_legal => zip_legal1,
2412 city_legal => city_legal1,
2413 num_children => num_children1,
2414 sign => sign1,
2415 tax_rate => tax_rate1,
2416 tax_name => tax_name1,
2417 year_due => year_due1);
2418 end if;
2419 END LOOP;
2420 plsqlmsg('Data inserted into table JE_ES_MODELO_190_ALL');
2421 CLOSE detail_paid;
2422 plsqlmsg('CURSOR Closed');
2423 plsqlmsg('Routine Successfully completed - ' || to_char(countrecs) || ' rows inserted');
2424 else -- p_pay_inv_sel <> 'P'
2425 /* Deal with APPROVED transactions */
2426 countrecs := 0;
2427 OPEN detail_approve;
2428 plsqlmsg('Opened CURSOR detail_approve');
2429 LOOP
2430 FETCH detail_approve
2431 INTO remun_type1,
2432 sub_remun_type1,
2433 vendor_nif1,
2434 vendor_name1,
2435 invoice_amount,
2436 inv_doc_seq_num1,
2437 invoice_id1,
2438 invoice_num1,
2439 invoice_date1,
2440 gl_date1,
2441 net_amount1,
2442 withholding_tax_amount1,
2443 zip_legal1,
2444 city_legal1,
2445 tax_rate1,
2446 tax_name1;
2447 EXIT WHEN detail_approve%NOTFOUND;
2448 net_amount1 := round(net_amount1,func_curr_precision);
2449 withholding_tax_amount1 := round(withholding_tax_amount1,func_curr_precision);
2450 if ( withholding_tax_amount1 <>0 )
2451 or (tax_rate1 = 0) then -- Bug 1212074
2452 countrecs := countrecs + 1;
2453 ins_trans( legal_entity_id => p_legal_entity_id,
2454 org_id => p_org_id,
2455 conc_req_id => p_conc_req_id,
2456 remun_type => remun_type1,
2457 sub_remun_type => sub_remun_type1,
2458 vendor_nif => vendor_nif1,
2459 vendor_name => vendor_name1,
2460 invoice_id => invoice_id1,
2461 invoice_num => invoice_num1,
2462 inv_doc_seq_num => inv_doc_seq_num1,
2463 invoice_date => invoice_date1,
2464 gl_date => gl_date1,
2465 invoice_payment_id => NULL,
2466 date_paid => NULL,
2467 net_amount => net_amount1,
2468 withholding_tax_amount => withholding_tax_amount1,
2469 zip_electronic => NULL,
2470 zip_legal => zip_legal1,
2471 city_legal => city_legal1,
2472 num_children => NULL,
2473 sign => NULL,
2474 tax_rate => tax_rate1,
2475 tax_name => tax_name1,
2476 year_due => NULL);
2477 end if;
2478 END LOOP;
2479 plsqlmsg('Data inserted into table JE_ES_MODELO_190_ALL');
2480 CLOSE detail_approve;
2481 plsqlmsg('CURSOR Closed');
2482 plsqlmsg('Routine Successfully completed - ' || to_char(countrecs) || ' rows inserted');
2483 end if; -- p_pay_inv_sel = 'P' or <> 'P'
2484 else -- p_summary <> 'Y'
2485 /* Deal with DETAIL transactions */
2486 if p_pay_inv_sel = 'P' then
2487 /* Deal with PAID transactions */
2488 countrecs := 0;
2489 OPEN detail_paid;
2490 plsqlmsg('Opened CURSOR detail_paid for Hard Copy');
2491 LOOP
2492 FETCH detail_paid
2493 INTO wht_mode,
2494 remun_type1,
2495 sub_remun_type1,
2496 vendor_nif1,
2497 vendor_name1,
2498 inv_awt_flag,
2499 inv_payment_status_flag,
2500 invoice_id1,
2501 invoice_num1,
2502 invoice_amount,
2503 net_amount1,
2504 invoice_prepaid_amount,
2505 invoice_withheld_amount,
2506 inv_doc_seq_num1,
2507 invoice_date1,
2508 invoice_payment_id1,
2509 invoice_payments_count,
2510 paid_amount,
2511 discount_amount,
2512 date_paid1,
2513 awt_invoice_payment_id,
2514 zip_legal1,
2515 city_legal1,
2516 wht_net_amount1,
2517 withholding_tax_amount1,
2518 tax_rate1,
2519 tax_name1;
2520 EXIT WHEN detail_paid%NOTFOUND;
2521 -- Bug 1271489: Get the correct tax name and tax rate.
2522 fetch_awt_line( p_pay_inv_sel,
2523 invoice_id1,
2524 withholding_tax_amount1,
2525 date_paid1, -- Bug 3930123 : Spanugan 23/12/2004
2526 tax_rate1,
2527 tax_name1,
2528 p_legal_entity_id,
2529 p_org_id);
2530 -- fnd_file.put_line(fnd_file.log,'Before No data');
2531 -- Automatic Withholding
2532 -- Withholding calculated at invoice payment time.
2533 if ( wht_mode = 'A' ) then
2534 if (nvl(inv_awt_flag,'N') = 'N') then
2535 net_amount1 := round(wht_net_amount1,func_curr_precision);
2536 withholding_tax_amount1 := round(withholding_tax_amount1, func_curr_precision);
2537 elsif (nvl(inv_awt_flag,'N') = 'Y') then
2538 if (nvl(inv_payment_status_flag,'N') = 'Y') then
2539 if (invoice_payments_count = 1 ) then
2540 net_amount1 := round(net_amount1,func_curr_precision);
2541 withholding_tax_amount1 := round(withholding_tax_amount1, func_curr_precision);
2542 elsif ( invoice_payments_count > 1 ) then
2543 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2544 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);
2545 end if; -- if invoice_payments_count = 1 or > 1
2546 else -- if nvl(inv_payment_status_flag,'N') = 'N'
2547 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2548 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);
2549 end if; -- if nvl(inv_payment_status_flag,'N') = 'N' or 'Y'
2550 end if; -- if nvl(inv_awt_flag,'N') = 'N' or 'Y'
2551 end if; -- if wht_mode = 'A'
2552 -- Manuali+Automatic Withholding
2553 -- Withholding calculated at invoice payment time.
2554 -- Withholding calculated at approval time.
2555 if ( wht_mode = 'M' ) then
2556 if (nvl(inv_payment_status_flag,'N') = 'Y') then
2557 if (invoice_payments_count = 1 ) then
2558 net_amount1 := round(net_amount1,func_curr_precision);
2559 withholding_tax_amount1 := round(withholding_tax_amount1, func_curr_precision);
2560 elsif ( invoice_payments_count > 1 ) then
2561 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2562 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);
2563 end if; -- if invoice_payments_count = 1 or > 1
2564 else -- if nvl(inv_payment_status_flag,'N') = 'N'
2565 net_amount1 := round((((paid_amount + nvl(discount_amount,0))/ (invoice_amount - invoice_prepaid_amount - invoice_withheld_amount))* net_amount1),func_curr_precision);
2566 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);
2567 end if; -- if nvl(inv_payment_status_flag,'N') = 'Y' or 'N'
2568 end if; -- if wht_mode = 'M'
2569 -- fnd_file.put_line(fnd_file.log,'Net Amount:' ||to_char(net_amount1));
2570 -- fnd_file.put_line(fnd_file.log,'WHT Amount:' ||to_char(withholding_tax_amount1));
2571 if ( withholding_tax_amount1 <>0 )
2572 or (tax_rate1 = 0) then -- Bug 1212074
2573 countrecs := countrecs + 1;
2574 ins_trans( legal_entity_id => p_legal_entity_id,
2575 org_id => p_org_id,
2576 conc_req_id => p_conc_req_id,
2577 remun_type => remun_type1,
2578 sub_remun_type => sub_remun_type1,
2579 vendor_nif => vendor_nif1,
2580 vendor_name => vendor_name1,
2581 invoice_id => invoice_id1,
2582 invoice_num => invoice_num1,
2583 inv_doc_seq_num => inv_doc_seq_num1,
2584 invoice_date => invoice_date1,
2585 gl_date => gl_date1,
2586 invoice_payment_id => invoice_payment_id1,
2587 date_paid => date_paid1,
2588 net_amount => net_amount1,
2589 withholding_tax_amount => withholding_tax_amount1,
2590 zip_electronic => zip_electronic1,
2591 zip_legal => zip_legal1,
2592 city_legal => city_legal1,
2593 num_children => num_children1,
2594 sign => sign1,
2595 tax_rate => tax_rate1,
2596 tax_name => tax_name1,
2597 year_due => year_due1);
2598 end if;
2599 END LOOP;
2600 plsqlmsg('Data inserted into table JE_ES_MODELO_190_ALL');
2601 CLOSE detail_paid;
2602 plsqlmsg('CURSOR Closed');
2603 plsqlmsg('Routine Successfully completed - ' || to_char(countrecs) || ' rows inserted');
2604 else -- p_pay_inv_sel <> 'P'
2605 /* Deal with APPOVED transactions */
2606 countrecs := 0;
2607 OPEN detail_approve;
2608 plsqlmsg('Opened CURSOR detail_approve');
2609 LOOP
2610 FETCH detail_approve
2611 INTO remun_type1,
2612 sub_remun_type1,
2613 vendor_nif1,
2614 vendor_name1,
2615 invoice_amount,
2616 inv_doc_seq_num1,
2617 invoice_id1,
2618 invoice_num1,
2619 invoice_date1,
2620 gl_date1,
2621 net_amount1,
2622 withholding_tax_amount1,
2623 zip_legal1,
2624 city_legal1,
2625 tax_rate1,
2626 tax_name1;
2627 EXIT WHEN detail_approve%NOTFOUND;
2628 -- Bug 1271489: Get the correct tax name and tax rate.
2629 fetch_awt_line( p_pay_inv_sel,
2630 invoice_id1,
2631 withholding_tax_amount1,
2632 gl_date1, -- Bug 3930123 : Spanugan 23/12/2004
2633 tax_rate1,
2634 tax_name1,
2635 p_legal_entity_id,
2636 p_org_id);
2637 net_amount1 := round(net_amount1,func_curr_precision);
2638 withholding_tax_amount1 := round(withholding_tax_amount1,func_curr_precision);
2639 if ( withholding_tax_amount1 <>0 )
2640 or (tax_rate1 = 0) then -- Bug 1212074
2641 countrecs := countrecs + 1;
2642 ins_trans( legal_entity_id => p_legal_entity_id,
2643 org_id => p_org_id,
2644 conc_req_id => p_conc_req_id,
2645 remun_type => remun_type1,
2646 sub_remun_type => sub_remun_type1,
2647 vendor_nif => vendor_nif1,
2648 vendor_name => vendor_name1,
2649 invoice_id => invoice_id1,
2650 invoice_num => invoice_num1,
2651 inv_doc_seq_num => inv_doc_seq_num1,
2652 invoice_date => invoice_date1,
2653 gl_date => gl_date1,
2654 invoice_payment_id => invoice_payment_id1,
2655 date_paid => date_paid1,
2656 net_amount => net_amount1,
2657 withholding_tax_amount => withholding_tax_amount1,
2658 zip_electronic => zip_electronic1,
2659 zip_legal => zip_legal1,
2660 city_legal => city_legal1,
2661 num_children => num_children1,
2662 sign => sign1,
2663 tax_rate => tax_rate1,
2664 tax_name => tax_name1,
2665 year_due => year_due1);
2666 end if;
2667 END LOOP;
2668 plsqlmsg('Data inserted into table JE_ES_MODELO_190_all');
2669 CLOSE detail_approve;
2670 plsqlmsg('CURSOR Closed');
2671 plsqlmsg('Routine Successfully completed - ' || to_char(countrecs) || ' rows inserted');
2672 RETCODE := 0;
2673 end if; -- p_pay_inv_sel = 'P' or <> 'P'
2674 end if; -- p_summary = 'Y' or <> 'Y'
2675 end if; -- p_hard_copy = 'N' or <> 'N'
2676 EXCEPTION
2677 WHEN bad_parameters THEN
2678 dbmsmsg('Error: Magnetic Report does not require DETAILED transactions');
2679 dbmsmsg('Error: Please Request SUMMARY transactions');
2680 RETCODE := 2;
2681 -- Bug 1271489: exception handling of wrong number of awt lines.
2682 WHEN bad_awt_lines THEN
2683 RETCODE := 2;
2684 WHEN others THEN
2685 dbmsmsg('Error: '|| substr(SQLERRM(SQLCODE),1,255));
2686 RETCODE := 2;
2687 ERRBUF := 'Error: '|| substr(SQLERRM(SQLCODE),1,255);
2688 end get_data;
2689 END je_es_whtax;