[Home] [Help]
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)
528: prepaid_amount NUMBER := 0;
529: BEGIN
530: select (0 - sum(nvl(dist.base_amount,nvl(dist.amount,0))))
531: into prepaid_amount
532: from ap_invoice_distributions_all dist,
533: ap_invoice_lines_all line,
534: ap_invoices_all inv
535: where dist.invoice_id = l_invoice_id
536: and inv.legal_entity_id = nvl(l_legal_entity_id, inv.legal_entity_id)
557: l_awt_net_total NUMBER := 0;
558: BEGIN
559: SELECT NVL(SUM(nvl(dist.base_amount,NVL(dist.amount,0))),0)
560: INTO l_awt_net_total
561: FROM ap_invoice_distributions_all dist,
562: ap_invoice_lines_all line,
563: ap_invoices_all inv
564: WHERE dist.invoice_id = l_invoice_id
565: and inv.legal_entity_id = nvl(l_legal_entity_id, inv.legal_entity_id)
635: invoice_amount JE_ES_MODELO_190_ALL.net_amount%TYPE;
636: inv_payment_status_flag ap_invoices.payment_status_flag%TYPE;
637: wht_mode ap_invoices.payment_status_flag%TYPE;
638: inv_awt_flag ap_invoices.awt_flag%TYPE;
639: dist_awt_flag ap_invoice_distributions_all.awt_flag%type; -- bug 8709676
640: paid_amount JE_ES_MODELO_190_ALL.net_amount%TYPE;
641: invoice_prepaid_amount JE_ES_MODELO_190_ALL.net_amount%TYPE;
642: invoice_withheld_amount JE_ES_MODELO_190_ALL.net_amount%TYPE;
643: inv_dist_net_amount JE_ES_MODELO_190_ALL.net_amount%TYPE;
692: po_vendor_sites_all vs,
693: fnd_lookups fl,
694: ap_invoices_all inv,
695: ap_invoice_lines_all line,
696: ap_invoice_distributions_all dist,
697: ap_tax_codes_all atc,
698: ap_awt_tax_rates_all awt,
699: (SELECT distinct person_id
700: ,national_identifier
758: AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
759: OR (dist.awt_tax_rate_id is NULL) )
760: -- Ignore any invoices which do not have 'AWT' distribution lines
761: AND EXISTS ( select dist2.invoice_id
762: from ap_invoice_distributions_all dist2
763: where inv.invoice_id = dist2.invoice_id
764: and dist2.line_type_lookup_code = 'AWT'
765: and dist2.withholding_tax_code_id in
766: -- Bug 2019586: Column name should be tax_id.
793: ap_tax_codes_all atc,
794: ap_awt_tax_rates_all awt,
795: ap_invoices_all inv,
796: ap_invoice_lines_all line,
797: ap_invoice_distributions_all dist,
798: (SELECT distinct person_id
799: ,national_identifier
800: FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
801: WHERE v.vendor_id = vs.vendor_id
825: (dist.cancellation_flag is null
826: AND dist.accounting_date < (select distinct gl.start_date
827: from gl_period_statuses gl
828: where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
829: from ap_invoice_distributions_all dist1
830: where dist1.invoice_id = inv.invoice_id
831: and dist1.cancellation_flag = 'Y' )))
832: OR
833: (dist.cancellation_flag = 'Y'
833: (dist.cancellation_flag = 'Y'
834: AND dist.accounting_date > (select distinct gl.end_date
835: from gl_period_statuses gl
836: where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
837: from ap_invoice_distributions_all dist1
838: where dist1.invoice_id = inv.invoice_id
839: and dist1.cancellation_flag is null )))
840: )
841: -- END
878: AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
879: OR (dist.awt_tax_rate_id is NULL) )
880: -- Ignore any invoices which do not have 'AWT' distribution lines
881: AND EXISTS ( select dist2.invoice_id
882: from ap_invoice_distributions_all dist2
883: where inv.invoice_id = dist2.invoice_id
884: and dist2.line_type_lookup_code = 'AWT'
885: and dist2.withholding_tax_code_id in
886: -- Bug 2019586: Column name should be tax_id.
944: ap_awt_tax_rates_all awt,
945: fnd_document_sequences seq,
946: ap_invoices_all inv,
947: ap_invoice_lines_all line,
948: ap_invoice_distributions_all dist,
949: (SELECT distinct person_id
950: ,national_identifier
951: FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
952: WHERE vs.country = fl.lookup_code(+)
1020: AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
1021: OR (dist.awt_tax_rate_id is NULL) )
1022: -- Ignore any invoices which do not have 'AWT' distribution lines
1023: AND EXISTS (select dist2.invoice_id
1024: from ap_invoice_distributions_all dist2
1025: where inv.invoice_id = dist2.invoice_id
1026: and dist2.line_type_lookup_code = 'AWT'
1027: and dist2.withholding_tax_code_id in
1028: -- Bug 2019586: Column name should be tax_id.
1030: (select tax_id
1031: from ap_tax_codes_all
1032: where vat_transaction_type = p_wht_tax_type))
1033: AND NOT EXISTS ( select dist2.invoice_id
1034: from ap_invoice_distributions_all dist2
1035: where inv.invoice_id = dist2.invoice_id
1036: and dist2.line_type_lookup_code = 'AWT'
1037: and dist2.awt_flag <> 'A')
1038: GROUP BY 'A',
1123: ap_awt_tax_rates_all awt,
1124: fnd_document_sequences seq,
1125: ap_invoices_all inv,
1126: ap_invoice_lines_all line,
1127: ap_invoice_distributions_all dist,
1128: (SELECT distinct person_id
1129: ,national_identifier
1130: FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
1131: WHERE vs.country = fl.lookup_code(+)
1157: (dist.cancellation_flag is null
1158: AND dist.accounting_date < (select distinct gl.start_date
1159: from gl_period_statuses gl
1160: where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
1161: from ap_invoice_distributions_all dist1
1162: where dist1.invoice_id = inv.invoice_id
1163: and dist1.cancellation_flag = 'Y' )))
1164: OR
1165: (dist.cancellation_flag = 'Y'
1165: (dist.cancellation_flag = 'Y'
1166: AND dist.accounting_date > (select distinct gl.end_date
1167: from gl_period_statuses gl
1168: where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
1169: from ap_invoice_distributions_all dist1
1170: where dist1.invoice_id = inv.invoice_id
1171: and dist1.cancellation_flag is null )))
1172: )
1173: -- END
1215: and nvl(awt.end_date, invpay.accounting_date)
1216: AND inv.doc_sequence_id = seq.doc_sequence_id(+)
1217: -- Ignore any invoices which do not have 'AWT' distribution lines
1218: AND EXISTS (select dist2.invoice_id
1219: from ap_invoice_distributions_all dist2
1220: where inv.invoice_id = dist2.invoice_id
1221: and dist2.line_type_lookup_code = 'AWT'
1222: and dist2.withholding_tax_code_id in
1223: -- Bug 2019586: Column name should be tax_id.
1225: (select tax_id
1226: from ap_tax_codes_all
1227: where vat_transaction_type = p_wht_tax_type))
1228: AND NOT EXISTS ( select dist2.invoice_id
1229: from ap_invoice_distributions_all dist2
1230: where inv.invoice_id = dist2.invoice_id
1231: and dist2.line_type_lookup_code = 'AWT'
1232: and dist2.awt_flag <> 'A')
1233: GROUP BY 'A',
1315: ap_awt_tax_rates_all awt,
1316: fnd_document_sequences seq,
1317: ap_invoices_all inv,
1318: ap_invoice_lines_all line,
1319: ap_invoice_distributions_all dist,
1320: (SELECT distinct person_id
1321: ,national_identifier
1322: FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
1323: WHERE vs.country = fl.lookup_code(+)
1388: -- Bug 5207771 : Added to remove the duplicates WH lines
1389: AND( (dist.awt_tax_rate_id = awt.tax_rate_id)
1390: OR (dist.awt_tax_rate_id is NULL) )
1391: AND EXISTS (select dist2.invoice_id
1392: from ap_invoice_distributions_all dist2
1393: where inv.invoice_id = dist2.invoice_id
1394: and dist2.line_type_lookup_code = 'AWT'
1395: and dist2.withholding_tax_code_id in
1396: -- Bug 2019586: Column name should be tax_id.
1398: (select tax_id
1399: from ap_tax_codes_all
1400: where vat_transaction_type = p_wht_tax_type))
1401: AND NOT EXISTS ( select dist2.invoice_id
1402: from ap_invoice_distributions_all dist2
1403: where inv.invoice_id = dist2.invoice_id
1404: and dist2.line_type_lookup_code = 'AWT'
1405: and dist2.awt_flag <> 'A')
1406: -- BUG 3930123 : Adding one more select clause with certain modifications, to fetch
1491: ap_awt_tax_rates_all awt,
1492: fnd_document_sequences seq,
1493: ap_invoices_all inv,
1494: ap_invoice_lines_all line,
1495: ap_invoice_distributions_all dist,
1496: (SELECT distinct person_id
1497: ,national_identifier
1498: FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
1499: WHERE vs.country = fl.lookup_code(+)
1525: (dist.cancellation_flag is null
1526: AND dist.accounting_date < (select distinct gl.start_date
1527: from gl_period_statuses gl
1528: where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
1529: from ap_invoice_distributions_all dist1
1530: where dist1.invoice_id = inv.invoice_id
1531: and dist1.cancellation_flag = 'Y' )))
1532: OR
1533: (dist.cancellation_flag = 'Y'
1533: (dist.cancellation_flag = 'Y'
1534: AND dist.accounting_date > (select distinct gl.end_date
1535: from gl_period_statuses gl
1536: where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
1537: from ap_invoice_distributions_all dist1
1538: where dist1.invoice_id = inv.invoice_id
1539: and dist1.cancellation_flag is null )))
1540: )
1541: -- END
1582: AND invpay.accounting_date between nvl(awt.start_date, invpay.accounting_date)
1583: AND nvl(awt.end_date, invpay.accounting_date)
1584: AND inv.doc_sequence_id = seq.doc_sequence_id(+)
1585: AND EXISTS (select dist2.invoice_id
1586: from ap_invoice_distributions_all dist2
1587: where inv.invoice_id = dist2.invoice_id
1588: and dist2.line_type_lookup_code = 'AWT'
1589: and dist2.withholding_tax_code_id in
1590: -- Bug 2019586: Column name should be tax_id.
1592: (select tax_id
1593: from ap_tax_codes_all
1594: where vat_transaction_type = p_wht_tax_type))
1595: AND NOT EXISTS ( select dist2.invoice_id
1596: from ap_invoice_distributions_all dist2
1597: where inv.invoice_id = dist2.invoice_id
1598: and dist2.line_type_lookup_code = 'AWT'
1599: and dist2.awt_flag <> 'A')
1600: ----------bug 12420102 Added below group by
1681: ap_awt_tax_rates_all awt,
1682: fnd_document_sequences seq,
1683: ap_invoices_all inv,
1684: ap_invoice_lines_all line,
1685: ap_invoice_distributions_all dist,
1686: (SELECT distinct person_id
1687: ,national_identifier
1688: FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
1689: WHERE vs.country = fl.lookup_code(+)
1752: AND invpay.accounting_date between nvl(awt.start_date, invpay.accounting_date)
1753: AND nvl(awt.end_date, invpay.accounting_date)
1754: AND inv.doc_sequence_id = seq.doc_sequence_id(+)
1755: AND EXISTS (select dist2.invoice_id
1756: from ap_invoice_distributions_all dist2
1757: where inv.invoice_id = dist2.invoice_id
1758: and dist2.line_type_lookup_code = 'AWT'
1759: and dist2.withholding_tax_code_id in
1760: -- Bug 2019586: Column name should be tax_id.
1762: (select tax_id
1763: from ap_tax_codes_all
1764: where vat_transaction_type = p_wht_tax_type))
1765: AND EXISTS ( select dist2.invoice_id
1766: from ap_invoice_distributions_all dist2
1767: where inv.invoice_id = dist2.invoice_id
1768: and dist2.line_type_lookup_code = 'AWT'
1769: and dist2.awt_flag <> 'A')
1770: GROUP BY 'M',
1855: ap_awt_tax_rates_all awt,
1856: fnd_document_sequences seq,
1857: ap_invoices_all inv,
1858: ap_invoice_lines_all line,
1859: ap_invoice_distributions_all dist,
1860: (SELECT distinct person_id
1861: ,national_identifier
1862: FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
1863: WHERE vs.country = fl.lookup_code(+)
1888: (dist.cancellation_flag is null
1889: AND dist.accounting_date < (select distinct gl.start_date
1890: from gl_period_statuses gl
1891: where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
1892: from ap_invoice_distributions_all dist1
1893: where dist1.invoice_id = inv.invoice_id
1894: and dist1.cancellation_flag = 'Y' )))
1895: OR
1896: (dist.cancellation_flag = 'Y'
1896: (dist.cancellation_flag = 'Y'
1897: AND dist.accounting_date > (select distinct gl.end_date
1898: from gl_period_statuses gl
1899: where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
1900: from ap_invoice_distributions_all dist1
1901: where dist1.invoice_id = inv.invoice_id
1902: and dist1.cancellation_flag is null )))
1903: )
1904: -- END
1944: AND invpay.accounting_date between nvl(awt.start_date, invpay.accounting_date)
1945: AND nvl(awt.end_date, invpay.accounting_date)
1946: AND inv.doc_sequence_id = seq.doc_sequence_id(+)
1947: AND EXISTS (select dist2.invoice_id
1948: from ap_invoice_distributions_all dist2
1949: where inv.invoice_id = dist2.invoice_id
1950: and dist2.line_type_lookup_code = 'AWT'
1951: and dist2.withholding_tax_code_id in
1952: -- Bug 2019586: Column name should be tax_id.
1954: (select tax_id
1955: from ap_tax_codes_all
1956: where vat_transaction_type = p_wht_tax_type))
1957: AND EXISTS ( select dist2.invoice_id
1958: from ap_invoice_distributions_all dist2
1959: where inv.invoice_id = dist2.invoice_id
1960: and dist2.line_type_lookup_code = 'AWT'
1961: and dist2.awt_flag <> 'A')
1962: GROUP BY 'M',
2033: ap_awt_tax_rates_all awt,
2034: fnd_document_sequences seq,
2035: ap_invoices_all inv,
2036: ap_invoice_lines_all line,
2037: ap_invoice_distributions_all dist,
2038: (SELECT distinct person_id
2039: ,national_identifier
2040: FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
2041: WHERE vs.country = fl.lookup_code(+)
2099: OR (dist.awt_tax_rate_id is NULL) )
2100: AND inv.doc_sequence_id = seq.doc_sequence_id(+)
2101: -- Ignore any invoices which do not have 'AWT' distribution lines
2102: AND EXISTS ( select dist2.invoice_id
2103: from ap_invoice_distributions_all dist2
2104: where inv.invoice_id = dist2.invoice_id
2105: and dist2.line_type_lookup_code = 'AWT'
2106: and dist2.withholding_tax_code_id in
2107: -- Bug 2019586: Column name should be tax_id.
2168: ap_awt_tax_rates_all awt,
2169: fnd_document_sequences seq,
2170: ap_invoices_all inv,
2171: ap_invoice_lines_all line,
2172: ap_invoice_distributions_all dist,
2173: (SELECT distinct person_id
2174: ,national_identifier
2175: FROM PER_ALL_PEOPLE_F WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
2176: WHERE vs.country = fl.lookup_code(+)
2201: (dist.cancellation_flag is null
2202: AND dist.accounting_date < (select distinct gl.start_date
2203: from gl_period_statuses gl
2204: where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
2205: from ap_invoice_distributions_all dist1
2206: where dist1.invoice_id = inv.invoice_id
2207: and dist1.cancellation_flag = 'Y' )))
2208: OR
2209: (dist.cancellation_flag = 'Y'
2209: (dist.cancellation_flag = 'Y'
2210: AND dist.accounting_date > (select distinct gl.end_date
2211: from gl_period_statuses gl
2212: where gl.period_name IN (select distinct to_char(dist1.accounting_date,'MM-YY')
2213: from ap_invoice_distributions_all dist1
2214: where dist1.invoice_id = inv.invoice_id
2215: and dist1.cancellation_flag is null )))
2216: )
2217: -- END
2252: OR (dist.awt_tax_rate_id is NULL) )
2253: AND inv.doc_sequence_id = seq.doc_sequence_id(+)
2254: -- Ignore any invoices which do not have 'AWT' distribution lines
2255: AND EXISTS ( select dist2.invoice_id
2256: from ap_invoice_distributions_all dist2
2257: where inv.invoice_id = dist2.invoice_id
2258: and dist2.line_type_lookup_code = 'AWT'
2259: and dist2.withholding_tax_code_id in
2260: -- Bug 2019586: Column name should be tax_id.
2301: l_tax_code_id ap_tax_codes.tax_id%TYPE;
2302: l_invoice_num ap_invoices.invoice_num%TYPE;
2303: l_tax_name JE_ES_MODELO_190_ALL.tax_name%TYPE;
2304: l_tax_rate JE_ES_MODELO_190_ALL.tax_rate%TYPE;
2305: l_accounting_date ap_invoice_distributions_all.accounting_date%TYPE;
2306: begin
2307: begin
2308: if p_fetch_pi_flag = 'P' then
2309:
2313: -- select min(dist.withholding_tax_code_id), max(invpay.accounting_date) -- Bug 12616975
2314: select min(dist.withholding_tax_code_id),(select max(accounting_date) from ap_invoice_payments_all where invoice_id=p_fetch_invoice_id)
2315: into l_tax_code_id, l_accounting_date
2316: from -- ap_invoice_payments_all invpay,
2317: ap_invoice_distributions_all dist
2318: where dist.invoice_id = p_fetch_invoice_id
2319: --and invpay.invoice_id = dist.invoice_id -- Bug 12616975 takes cross product
2320: and dist.line_type_lookup_code = 'AWT'
2321: and ((p_parent_reversal_flag = 'N' and dist.parent_reversal_id is null)
2335:
2336: select min(dist.withholding_tax_code_id), max(invpay.accounting_date)
2337: into l_tax_code_id, l_accounting_date
2338: from ap_invoice_payments_all invpay,
2339: ap_invoice_distributions_all dist
2340: where dist.invoice_id = p_fetch_invoice_id
2341: and invpay.invoice_id = dist.invoice_id
2342: and invpay.INVOICE_PAYMENT_ID = dist.AWT_INVOICE_PAYMENT_ID -- Bug 12616975
2343: and dist.line_type_lookup_code = 'AWT'
2359: else -- p_fetch_pi_flag <> 'P'
2360:
2361: select min(withholding_tax_code_id), max(accounting_date)
2362: into l_tax_code_id, l_accounting_date
2363: from ap_invoice_distributions_all dist
2364: where dist.invoice_id = p_fetch_invoice_id
2365: and dist.line_type_lookup_code = 'AWT'
2366: and ((p_parent_reversal_flag = 'N' and dist.parent_reversal_id is null)
2367: OR (p_parent_reversal_flag = 'Y' and dist.parent_reversal_id is not null))