VIEW: APPS.JAI_AP_MATCH_INV_TAX_V

Source

View Text - Preformatted

SELECT tax_line.match_type, tax_line.invoice_id, tax_line.parent_invoice_line_number, tax_line.transaction_id, tax_line.TAX_LINE_NO, tax_line.TAX_ID, tax_line.TAX_TYPE, tax_line.PRECEDENCE_1, tax_line.PRECEDENCE_2, tax_line.PRECEDENCE_3, tax_line.PRECEDENCE_4, tax_line.PRECEDENCE_5, tax_line.PRECEDENCE_6, tax_line.PRECEDENCE_7, tax_line.PRECEDENCE_8, tax_line.PRECEDENCE_9, tax_line.PRECEDENCE_10, tax_line.CURRENCY, tax_line.TAX_RATE, tax_line.QTY_RATE, tax_line.UOM, sum(nvl(tax_line.TAX_AMOUNT,0)) TAX_AMOUNT, tax_line.VENDOR_ID, tax_line.VENDOR_SITE_ID, tax_line.MODVAT_FLAG from (Select 'ITEM_TO_PO' match_type, b.invoice_id, b.parent_invoice_line_number, a.line_location_id transaction_id, a.TAX_LINE_NO, a.TAX_ID, a.TAX_TYPE, a.PRECEDENCE_1, a.PRECEDENCE_2, a.PRECEDENCE_3, a.PRECEDENCE_4, a.PRECEDENCE_5, a.PRECEDENCE_6, a.PRECEDENCE_7, a.PRECEDENCE_8, a.PRECEDENCE_9, a.PRECEDENCE_10, a.CURRENCY, a.TAX_RATE, a.QTY_RATE, a.UOM, b.TAX_AMOUNT, a.VENDOR_ID, NULL VENDOR_SITE_ID, a.MODVAT_FLAG from jai_po_taxes a, jai_ap_match_inv_taxes b WHERE a.tax_id = b.tax_id and a.line_location_id = b.line_location_id and b.rcv_transaction_id is null UNION ALL Select 'ITEM_TO_RECEIPT' match_type, b.invoice_id, b.parent_invoice_line_number, a.transaction_id, a.TAX_LINE_NO, a.TAX_ID, a.TAX_TYPE, a.PRECEDENCE_1, a.PRECEDENCE_2, a.PRECEDENCE_3, a.PRECEDENCE_4, a.PRECEDENCE_5, a.PRECEDENCE_6, a.PRECEDENCE_7, a.PRECEDENCE_8, a.PRECEDENCE_9, a.PRECEDENCE_10, a.CURRENCY, a.TAX_RATE, a.QTY_RATE, a.UOM, b.TAX_AMOUNT, a.VENDOR_ID, a.VENDOR_SITE_ID, a.MODVAT_FLAG from JAI_RCV_LINE_TAXES a, jai_ap_match_inv_taxes b, rcv_transactions c where a.tax_id = b.tax_id and b.rcv_transaction_id = c.transaction_id and b.rcv_transaction_id is not null and a.shipment_line_id = c.shipment_line_id ) tax_line group by tax_line.match_type, tax_line.invoice_id, tax_line.parent_invoice_line_number, tax_line.transaction_id, tax_line.TAX_LINE_NO, tax_line.TAX_ID, tax_line.TAX_TYPE, tax_line.PRECEDENCE_1, tax_line.PRECEDENCE_2, tax_line.PRECEDENCE_3, tax_line.PRECEDENCE_4, tax_line.PRECEDENCE_5, tax_line.PRECEDENCE_6, tax_line.PRECEDENCE_7, tax_line.PRECEDENCE_8, tax_line.PRECEDENCE_9, tax_line.PRECEDENCE_10, tax_line.CURRENCY, tax_line.TAX_RATE, tax_line.QTY_RATE, tax_line.UOM, tax_line.VENDOR_ID, tax_line.VENDOR_SITE_ID, tax_line.MODVAT_FLAG

View Text - HTML Formatted

SELECT TAX_LINE.MATCH_TYPE
, TAX_LINE.INVOICE_ID
, TAX_LINE.PARENT_INVOICE_LINE_NUMBER
, TAX_LINE.TRANSACTION_ID
, TAX_LINE.TAX_LINE_NO
, TAX_LINE.TAX_ID
, TAX_LINE.TAX_TYPE
, TAX_LINE.PRECEDENCE_1
, TAX_LINE.PRECEDENCE_2
, TAX_LINE.PRECEDENCE_3
, TAX_LINE.PRECEDENCE_4
, TAX_LINE.PRECEDENCE_5
, TAX_LINE.PRECEDENCE_6
, TAX_LINE.PRECEDENCE_7
, TAX_LINE.PRECEDENCE_8
, TAX_LINE.PRECEDENCE_9
, TAX_LINE.PRECEDENCE_10
, TAX_LINE.CURRENCY
, TAX_LINE.TAX_RATE
, TAX_LINE.QTY_RATE
, TAX_LINE.UOM
, SUM(NVL(TAX_LINE.TAX_AMOUNT
, 0)) TAX_AMOUNT
, TAX_LINE.VENDOR_ID
, TAX_LINE.VENDOR_SITE_ID
, TAX_LINE.MODVAT_FLAG
FROM (SELECT 'ITEM_TO_PO' MATCH_TYPE
, B.INVOICE_ID
, B.PARENT_INVOICE_LINE_NUMBER
, A.LINE_LOCATION_ID TRANSACTION_ID
, A.TAX_LINE_NO
, A.TAX_ID
, A.TAX_TYPE
, A.PRECEDENCE_1
, A.PRECEDENCE_2
, A.PRECEDENCE_3
, A.PRECEDENCE_4
, A.PRECEDENCE_5
, A.PRECEDENCE_6
, A.PRECEDENCE_7
, A.PRECEDENCE_8
, A.PRECEDENCE_9
, A.PRECEDENCE_10
, A.CURRENCY
, A.TAX_RATE
, A.QTY_RATE
, A.UOM
, B.TAX_AMOUNT
, A.VENDOR_ID
, NULL VENDOR_SITE_ID
, A.MODVAT_FLAG
FROM JAI_PO_TAXES A
, JAI_AP_MATCH_INV_TAXES B
WHERE A.TAX_ID = B.TAX_ID
AND A.LINE_LOCATION_ID = B.LINE_LOCATION_ID
AND B.RCV_TRANSACTION_ID IS NULL UNION ALL SELECT 'ITEM_TO_RECEIPT' MATCH_TYPE
, B.INVOICE_ID
, B.PARENT_INVOICE_LINE_NUMBER
, A.TRANSACTION_ID
, A.TAX_LINE_NO
, A.TAX_ID
, A.TAX_TYPE
, A.PRECEDENCE_1
, A.PRECEDENCE_2
, A.PRECEDENCE_3
, A.PRECEDENCE_4
, A.PRECEDENCE_5
, A.PRECEDENCE_6
, A.PRECEDENCE_7
, A.PRECEDENCE_8
, A.PRECEDENCE_9
, A.PRECEDENCE_10
, A.CURRENCY
, A.TAX_RATE
, A.QTY_RATE
, A.UOM
, B.TAX_AMOUNT
, A.VENDOR_ID
, A.VENDOR_SITE_ID
, A.MODVAT_FLAG
FROM JAI_RCV_LINE_TAXES A
, JAI_AP_MATCH_INV_TAXES B
, RCV_TRANSACTIONS C
WHERE A.TAX_ID = B.TAX_ID
AND B.RCV_TRANSACTION_ID = C.TRANSACTION_ID
AND B.RCV_TRANSACTION_ID IS NOT NULL
AND A.SHIPMENT_LINE_ID = C.SHIPMENT_LINE_ID ) TAX_LINE GROUP BY TAX_LINE.MATCH_TYPE
, TAX_LINE.INVOICE_ID
, TAX_LINE.PARENT_INVOICE_LINE_NUMBER
, TAX_LINE.TRANSACTION_ID
, TAX_LINE.TAX_LINE_NO
, TAX_LINE.TAX_ID
, TAX_LINE.TAX_TYPE
, TAX_LINE.PRECEDENCE_1
, TAX_LINE.PRECEDENCE_2
, TAX_LINE.PRECEDENCE_3
, TAX_LINE.PRECEDENCE_4
, TAX_LINE.PRECEDENCE_5
, TAX_LINE.PRECEDENCE_6
, TAX_LINE.PRECEDENCE_7
, TAX_LINE.PRECEDENCE_8
, TAX_LINE.PRECEDENCE_9
, TAX_LINE.PRECEDENCE_10
, TAX_LINE.CURRENCY
, TAX_LINE.TAX_RATE
, TAX_LINE.QTY_RATE
, TAX_LINE.UOM
, TAX_LINE.VENDOR_ID
, TAX_LINE.VENDOR_SITE_ID
, TAX_LINE.MODVAT_FLAG