[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
723: -----and inv.cancelled_date is null -- Bug 2228008 )
724: AND dist.parent_reversal_id is null
725: -- bug 8496890
726: /*AND not exists ( select 1
727: from ap_invoice_distributions dist1, gl_period_statuses gl
728: where gl.application_id = 101
729: and dist1.invoice_id = inv.invoice_id
730: and dist1.parent_reversal_id = dist.invoice_distribution_id
731: and gl.ledger_id = dist1.set_of_books_id
732: and dist.accounting_date between gl.start_date and gl.end_date
733: and dist1.accounting_date <= gl.end_date )
734: */
735: AND not exists ( select 1
736: from ap_invoice_distributions dist1
737: where dist1.invoice_id = inv.invoice_id
738: and dist1.parent_reversal_id = dist.invoice_distribution_id
739: and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
740: and fnd_date.canonical_to_date(P_Date_To)
745: AND fnd_date.canonical_to_date(P_Date_To)
746: AND ((dist.line_type_lookup_code = 'AWT')
747: OR
748: (dist.awt_group_id is not NULL))
749: AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
750: dist.ACCRUAL_POSTED_FLAG,
751: dist.CASH_POSTED_FLAG,
752: dist.POSTED_FLAG, inv.org_id) in ('Y','P')
753: -- bug 5207771 Added legal_entity id as 4th parameter, above line
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
846: AND dist.parent_reversal_id is not null
847: -- bug 8496890
848: /*
849: AND dist.accounting_date > (select distinct gl.end_date
850: from ap_invoice_distributions dist1, gl_period_statuses gl
851: where gl.application_id = 101
852: and dist1.invoice_id = inv.invoice_id
853: and dist.parent_reversal_id = dist1.invoice_distribution_id
854: and gl.ledger_id = dist1.set_of_books_id
853: and dist.parent_reversal_id = dist1.invoice_distribution_id
854: and gl.ledger_id = dist1.set_of_books_id
855: and dist1.accounting_date between gl.start_date and gl.end_date)
856: */
857: AND not exists (select 1 from ap_invoice_distributions dist1
858: where dist1.invoice_id = inv.invoice_id
859: and dist.parent_reversal_id = dist1.invoice_distribution_id
860: and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
861: and fnd_date.canonical_to_date(P_Date_To))
865: AND fnd_date.canonical_to_date(P_Date_To)
866: AND ((dist.line_type_lookup_code = 'AWT')
867: OR
868: (dist.awt_group_id is not NULL))
869: AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
870: dist.ACCRUAL_POSTED_FLAG,
871: dist.CASH_POSTED_FLAG,
872: dist.POSTED_FLAG, inv.org_id) in ('Y','P')
873: -- bug 5207771 Added legal_entity id as 4th parameter, above line
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(+)
976: AND dist.parent_reversal_id is null
977: -- bug 8496890
978: /*
979: AND not exists ( select 1
980: from ap_invoice_distributions dist1, gl_period_statuses gl
981: where gl.application_id = 101
982: and dist1.invoice_id = inv.invoice_id
983: and dist1.parent_reversal_id = dist.invoice_distribution_id
984: and gl.ledger_id = dist1.set_of_books_id
985: and dist.accounting_date between gl.start_date and gl.end_date
986: and dist1.accounting_date <= gl.end_date )
987: */
988: AND not exists ( select 1
989: from ap_invoice_distributions dist1
990: where dist1.invoice_id = inv.invoice_id
991: and dist1.parent_reversal_id = dist.invoice_distribution_id
992: and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
993: and fnd_date.canonical_to_date(P_Date_To)
1004: between
1005: nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
1006: and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
1007: AND (dist.line_type_lookup_code = 'AWT')
1008: AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
1009: DIST.ACCRUAL_POSTED_FLAG,
1010: DIST.CASH_POSTED_FLAG,
1011: dist.POSTED_FLAG, inv.org_id) in ('Y','P')
1012: AND dist.withholding_tax_code_id = atc.tax_id(+)
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
1175: AND dist.parent_reversal_id is not null
1176: -- bug 8496890
1177: /*
1178: AND dist.accounting_date > (select distinct gl.end_date
1179: from ap_invoice_distributions dist1, gl_period_statuses gl
1180: where gl.application_id = 101
1181: and dist1.invoice_id = inv.invoice_id
1182: and dist.parent_reversal_id = dist1.invoice_distribution_id
1183: and gl.ledger_id = dist1.set_of_books_id
1182: and dist.parent_reversal_id = dist1.invoice_distribution_id
1183: and gl.ledger_id = dist1.set_of_books_id
1184: and dist1.accounting_date between gl.start_date and gl.end_date)
1185: */
1186: AND not exists (select 1 from ap_invoice_distributions dist1
1187: where dist1.invoice_id = inv.invoice_id
1188: and dist.parent_reversal_id = dist1.invoice_distribution_id
1189: and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
1190: and fnd_date.canonical_to_date(P_Date_To))
1199: between
1200: nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
1201: and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
1202: AND (dist.line_type_lookup_code = 'AWT')
1203: AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
1204: DIST.ACCRUAL_POSTED_FLAG,
1205: DIST.CASH_POSTED_FLAG,
1206: dist.POSTED_FLAG, inv.org_id) in ('Y','P')
1207: AND dist.withholding_tax_code_id = atc.tax_id(+)
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(+)
1346: AND dist.parent_reversal_id is null
1347: -- bug 8496890
1348: /*
1349: AND not exists ( select 1
1350: from ap_invoice_distributions dist1, gl_period_statuses gl
1351: where gl.application_id = 101
1352: and dist1.invoice_id = inv.invoice_id
1353: and dist1.parent_reversal_id = dist.invoice_distribution_id
1354: and gl.ledger_id = dist1.set_of_books_id
1355: and dist.accounting_date between gl.start_date and gl.end_date
1356: and dist1.accounting_date <= gl.end_date )
1357: */
1358: AND not exists ( select 1
1359: from ap_invoice_distributions dist1
1360: where dist1.invoice_id = inv.invoice_id
1361: and dist1.parent_reversal_id = dist.invoice_distribution_id
1362: and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
1363: and fnd_date.canonical_to_date(P_Date_To)
1374: nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
1375: and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
1376: AND dist.awt_invoice_payment_id = invpay.invoice_payment_id
1377: AND (dist.line_type_lookup_code = 'AWT')
1378: AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
1379: DIST.ACCRUAL_POSTED_FLAG,
1380: DIST.CASH_POSTED_FLAG,
1381: dist.POSTED_FLAG, inv.org_id) in ('Y','P')
1382: AND dist.withholding_tax_code_id = atc.tax_id(+)
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
1543: AND dist.parent_reversal_id is not null
1544: -- bug 8496890
1545: /*
1546: AND dist.accounting_date > (select distinct gl.end_date
1547: from ap_invoice_distributions dist1, gl_period_statuses gl
1548: where gl.application_id = 101
1549: and dist1.invoice_id = inv.invoice_id
1550: and dist.parent_reversal_id = dist1.invoice_distribution_id
1551: and gl.ledger_id = dist1.set_of_books_id
1550: and dist.parent_reversal_id = dist1.invoice_distribution_id
1551: and gl.ledger_id = dist1.set_of_books_id
1552: and dist1.accounting_date between gl.start_date and gl.end_date)
1553: */
1554: AND not exists (select 1 from ap_invoice_distributions dist1
1555: where dist1.invoice_id = inv.invoice_id
1556: and dist.parent_reversal_id = dist1.invoice_distribution_id
1557: and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
1558: and fnd_date.canonical_to_date(P_Date_To))
1568: nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
1569: and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
1570: AND dist.awt_invoice_payment_id = invpay.invoice_payment_id
1571: AND (dist.line_type_lookup_code = 'AWT')
1572: AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
1573: DIST.ACCRUAL_POSTED_FLAG,
1574: DIST.CASH_POSTED_FLAG,
1575: dist.POSTED_FLAG, inv.org_id) in ('Y','P')
1576: AND dist.withholding_tax_code_id = atc.tax_id(+)
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(+)
1711: AND dist.parent_reversal_id is null
1712: -- bug 8496890
1713: /*
1714: AND not exists ( select 1
1715: from ap_invoice_distributions dist1, gl_period_statuses gl
1716: where gl.application_id = 101
1717: and dist1.invoice_id = inv.invoice_id
1718: and dist1.parent_reversal_id = dist.invoice_distribution_id
1719: and gl.ledger_id = dist1.set_of_books_id
1720: and dist.accounting_date between gl.start_date and gl.end_date
1721: and dist1.accounting_date <= gl.end_date )
1722: */
1723: AND not exists ( select 1
1724: from ap_invoice_distributions dist1
1725: where dist1.invoice_id = inv.invoice_id
1726: and dist1.parent_reversal_id = dist.invoice_distribution_id
1727: and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
1728: and fnd_date.canonical_to_date(P_Date_To)
1738: between
1739: nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
1740: and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
1741: AND (dist.line_type_lookup_code = 'AWT')
1742: AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
1743: DIST.ACCRUAL_POSTED_FLAG,
1744: DIST.CASH_POSTED_FLAG,
1745: dist.POSTED_FLAG, inv.org_id) in ('Y','P')
1746: AND dist.withholding_tax_code_id = atc.tax_id(+)
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
1906: AND dist.parent_reversal_id is not null
1907: -- bug 8496890
1908: /*
1909: AND dist.accounting_date > (select distinct gl.end_date
1910: from ap_invoice_distributions dist1, gl_period_statuses gl
1911: where gl.application_id = 101
1912: and dist1.invoice_id = inv.invoice_id
1913: and dist.parent_reversal_id = dist1.invoice_distribution_id
1914: and gl.ledger_id = dist1.set_of_books_id
1913: and dist.parent_reversal_id = dist1.invoice_distribution_id
1914: and gl.ledger_id = dist1.set_of_books_id
1915: and dist1.accounting_date between gl.start_date and gl.end_date)
1916: */
1917: AND not exists (select 1 from ap_invoice_distributions dist1
1918: where dist1.invoice_id = inv.invoice_id
1919: and dist.parent_reversal_id = dist1.invoice_distribution_id
1920: and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
1921: and fnd_date.canonical_to_date(P_Date_To))
1930: between
1931: nvl(fnd_date.canonical_to_date(P_Date_From),invpay.accounting_date)
1932: and nvl(fnd_date.canonical_to_date(P_Date_To),invpay.accounting_date)
1933: AND (dist.line_type_lookup_code = 'AWT')
1934: AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
1935: DIST.ACCRUAL_POSTED_FLAG,
1936: DIST.CASH_POSTED_FLAG,
1937: dist.POSTED_FLAG, inv.org_id) in ('Y','P')
1938: AND dist.withholding_tax_code_id = atc.tax_id(+)
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(+)
2063: AND dist.parent_reversal_id is null
2064: -- bug 8496890
2065: /*
2066: AND not exists ( select 1
2067: from ap_invoice_distributions dist1, gl_period_statuses gl
2068: where gl.application_id = 101
2069: and dist1.invoice_id = inv.invoice_id
2070: and dist1.parent_reversal_id = dist.invoice_distribution_id
2071: and gl.ledger_id = dist1.set_of_books_id
2072: and dist.accounting_date between gl.start_date and gl.end_date
2073: and dist1.accounting_date <= gl.end_date )
2074: */
2075: AND not exists ( select 1
2076: from ap_invoice_distributions dist1
2077: where dist1.invoice_id = inv.invoice_id
2078: and dist1.parent_reversal_id = dist.invoice_distribution_id
2079: and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
2080: and fnd_date.canonical_to_date(P_Date_To)
2083: AND trunc(dist.accounting_date,'DD')
2084: between fnd_date.canonical_to_date(P_Date_From)
2085: and fnd_date.canonical_to_date(P_Date_To)
2086: AND dist.line_type_lookup_code = 'AWT'
2087: AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
2088: DIST.ACCRUAL_POSTED_FLAG,
2089: DIST.CASH_POSTED_FLAG,
2090: dist.POSTED_FLAG, inv.org_id) in ('Y','P')
2091: AND dist.withholding_tax_code_id = atc.tax_id(+)
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
2219: AND dist.parent_reversal_id is not null
2220: -- bug 8496890
2221: /*
2222: AND dist.accounting_date > (select distinct gl.end_date
2223: from ap_invoice_distributions dist1, gl_period_statuses gl
2224: where gl.application_id = 101
2225: and dist1.invoice_id = inv.invoice_id
2226: and dist.parent_reversal_id = dist1.invoice_distribution_id
2227: and gl.ledger_id = dist1.set_of_books_id
2226: and dist.parent_reversal_id = dist1.invoice_distribution_id
2227: and gl.ledger_id = dist1.set_of_books_id
2228: and dist1.accounting_date between gl.start_date and gl.end_date)
2229: */
2230: AND not exists (select 1 from ap_invoice_distributions dist1
2231: where dist1.invoice_id = inv.invoice_id
2232: and dist.parent_reversal_id = dist1.invoice_distribution_id
2233: and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
2234: and fnd_date.canonical_to_date(P_Date_To))
2236: AND trunc(dist.accounting_date,'DD')
2237: between fnd_date.canonical_to_date(P_Date_From)
2238: and fnd_date.canonical_to_date(P_Date_To)
2239: AND dist.line_type_lookup_code = 'AWT'
2240: AND AP_INVOICE_DISTRIBUTIONS_PKG.GET_POSTED_STATUS(
2241: DIST.ACCRUAL_POSTED_FLAG,
2242: DIST.CASH_POSTED_FLAG,
2243: dist.POSTED_FLAG, inv.org_id) in ('Y','P')
2244: AND dist.withholding_tax_code_id = atc.tax_id(+)
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)
2320: and dist.line_type_lookup_code = 'AWT'
2321: and ((p_parent_reversal_flag = 'N' and dist.parent_reversal_id is null)
2322: OR (p_parent_reversal_flag = 'Y' and dist.parent_reversal_id is not null))
2323: and not exists ( select 1 -- Bug 12616975
2324: from ap_invoice_distributions dist1
2325: where dist1.invoice_id = p_fetch_invoice_id
2326: and dist1.parent_reversal_id = dist.invoice_distribution_id
2327: and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
2328: and fnd_date.canonical_to_date(P_Date_To)
2335:
2336: select min(dist.withholding_tax_code_id), max(invpay.accounting_date)
2337: into l_tax_code_id, l_accounting_date
2338: from ap_invoice_payments_all invpay,
2339: ap_invoice_distributions_all dist
2340: where dist.invoice_id = p_fetch_invoice_id
2341: and invpay.invoice_id = dist.invoice_id
2342: and invpay.INVOICE_PAYMENT_ID = dist.AWT_INVOICE_PAYMENT_ID -- Bug 12616975
2343: and dist.line_type_lookup_code = 'AWT'
2344: and ((p_parent_reversal_flag = 'N' and dist.parent_reversal_id is null)
2345: OR (p_parent_reversal_flag = 'Y' and dist.parent_reversal_id is not null))
2346: and dist.awt_invoice_payment_id = p_awt_invoice_payment_id -- Bug 12616975
2347: and not exists ( select 1
2348: from ap_invoice_distributions dist1
2349: where dist1.invoice_id = p_fetch_invoice_id
2350: and dist1.parent_reversal_id = dist.invoice_distribution_id
2351: and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
2352: and fnd_date.canonical_to_date(P_Date_To)
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))
2365: and dist.line_type_lookup_code = 'AWT'
2366: and ((p_parent_reversal_flag = 'N' and dist.parent_reversal_id is null)
2367: OR (p_parent_reversal_flag = 'Y' and dist.parent_reversal_id is not null))
2368: and not exists ( select 1 -- Bug 12616975
2369: from ap_invoice_distributions dist1
2370: where dist1.invoice_id = p_fetch_invoice_id
2371: and dist1.parent_reversal_id = dist.invoice_distribution_id
2372: and dist1.accounting_date between fnd_date.canonical_to_date(P_Date_From)
2373: and fnd_date.canonical_to_date(P_Date_To)