View: PO_TAX_CODES_SUMMARY_V

View Text

SELECT 'PO'
, POH.PO_HEADER_ID
, POH.SEGMENT1
, POH.CURRENCY_CODE
, PLL.TAX_CODE_ID
, APT.NAME
, SUM(PO_TAX_SUMMARY_PKG.GET_RECOVERABLE_TAX(NULL
, NULL
, PLL.LINE_LOCATION_ID
, 'PO'
, 'SHIPMENT'))
, SUM(PO_TAX_SUMMARY_PKG.GET_NONRECOVERABLE_TAX(NULL
, NULL
, PLL.LINE_LOCATION_ID
, 'PO'
, 'SHIPMENT'))
, SUM(DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'RATE'
, (PLL.AMOUNT-NVL(PLL.AMOUNT_CANCELLED
, 0))
, 'FIXED PRICE'
, (PLL.AMOUNT-NVL(PLL.AMOUNT_CANCELLED
, 0))
, (PLL.QUANTITY-NVL(PLL.QUANTITY_CANCELLED
, 0))*POL.UNIT_PRICE) )
, POH.ORG_ID
FROM PO_HEADERS POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL PLL
, AP_TAX_CODES_ALL APT
WHERE PLL.PO_LINE_ID = POL.PO_LINE_ID
AND POL.PO_HEADER_ID = POH.PO_HEADER_ID
AND PLL.PO_RELEASE_ID IS NULL
AND PLL.TAX_CODE_ID = APT.TAX_ID
AND ( (NVL(PLL.QUANTITY
, 0)-NVL(PLL.QUANTITY_CANCELLED
, 0)) <> 0 OR (NVL(PLL.AMOUNT
, 0)-NVL(PLL.AMOUNT_CANCELLED
, 0) <> 0) )
AND NVL(PLL.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED' GROUP BY PLL.TAX_CODE_ID
, APT.NAME
, POH.PO_HEADER_ID
, POH.CURRENCY_CODE
, POH.SEGMENT1
, POH.ORG_ID UNION SELECT 'REL'
, POR.PO_RELEASE_ID
, POH.SEGMENT1
, POH.CURRENCY_CODE
, PLL.TAX_CODE_ID
, APT.NAME
, SUM(PO_TAX_SUMMARY_PKG.GET_RECOVERABLE_TAX(NULL
, NULL
, PLL.LINE_LOCATION_ID
, 'REL'
, 'SHIPMENT'))
, SUM(PO_TAX_SUMMARY_PKG.GET_NONRECOVERABLE_TAX(NULL
, NULL
, PLL.LINE_LOCATION_ID
, 'REL'
, 'SHIPMENT'))
, SUM(DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'FIXED PRICE'
, (PLL.AMOUNT-NVL(PLL.AMOUNT_CANCELLED
, 0))
, (PLL.QUANTITY-NVL(PLL.QUANTITY_CANCELLED
, 0))*PLL.PRICE_OVERRIDE) )
, POR.ORG_ID
FROM PO_RELEASES POR
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_RELEASE_V PLL
, AP_TAX_CODES_ALL APT
WHERE POH.PO_HEADER_ID = POR.PO_HEADER_ID
AND PLL.PO_RELEASE_ID = POR.PO_RELEASE_ID
AND PLL.PO_LINE_ID = POL.PO_LINE_ID
AND PLL.TAX_CODE_ID = APT.TAX_ID
AND ( (NVL(PLL.QUANTITY
, 0)-NVL(PLL.QUANTITY_CANCELLED
, 0)) <> 0 OR (NVL(PLL.AMOUNT
, 0)-NVL(PLL.AMOUNT_CANCELLED
, 0) <> 0) )
AND NVL(PLL.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED' GROUP BY PLL.TAX_CODE_ID
, APT.NAME
, POR.PO_RELEASE_ID
, POH.CURRENCY_CODE
, POH.SEGMENT1
, POR.ORG_ID UNION SELECT 'REQ'
, PRH.REQUISITION_HEADER_ID
, PRH.SEGMENT1
, PRH.CURRENCY_CODE
, PRL.TAX_CODE_ID
, APT.NAME
, SUM(PO_TAX_SUMMARY_PKG.GET_RECOVERABLE_TAX(NULL
, PRL.REQUISITION_LINE_ID
, NULL
, 'REQ'
, 'LINE'))
, SUM(PO_TAX_SUMMARY_PKG.GET_NONRECOVERABLE_TAX(NULL
, PRL.REQUISITION_LINE_ID
, NULL
, 'REQ'
, 'LINE'))
, SUM(DECODE(PRL.ORDER_TYPE_LOOKUP_CODE
, 'RATE'
, PRL.AMOUNT
, 'FIXED PRICE'
, PRL.AMOUNT
, PRL.QUANTITY*PRL.UNIT_PRICE))
, PRH.ORG_ID
FROM PO_REQUISITION_HEADERS_V PRH
, PO_REQUISITION_LINES_V PRL
, AP_TAX_CODES_ALL APT
WHERE PRL.REQUISITION_HEADER_ID = PRH.REQUISITION_HEADER_ID
AND PRL.TAX_CODE_ID = APT.TAX_ID
AND NVL(PRL.CANCEL_FLAG
, 'N') != 'Y'
AND NVL(PRL.CLOSED_CODE
, 'OPEN') != 'FINALLY CLOSED' GROUP BY PRL.TAX_CODE_ID
, APT.NAME
, PRH.REQUISITION_HEADER_ID
, PRH.CURRENCY_CODE
, PRH.SEGMENT1
, PRH.ORG_ID

Columns