View: POA_PURCHASE_SALES_V

View Text

SELECT DECODE(RT.TRANSACTION_TYPE
, 'RECEIVE'
, 1
, 'RETURN TO VENDOR'
, -1
, 'CORRECT'
, DECODE(RTP.TRANSACTION_TYPE
, 'RECEIVE'
, 1
, 'RETURN TO VENDOR'
, -1
, NULL)
, NULL) * PLL.PRICE_OVERRIDE * INV_CONVERT.INV_UM_CONVERT(POL.ITEM_ID
, 5
, RT.PRIMARY_QUANTITY
, NULL
, NULL
, RT.UNIT_OF_MEASURE
, POL.UNIT_MEAS_LOOKUP_CODE) PURCHASE_AMOUNT
, TO_NUMBER(NULL) SALES_AMOUNT
, TO_CHAR(RT.TRANSACTION_DATE
, 'MONTH YYYY') MONTH_BUCKET
, RT.TRANSACTION_DATE TRANSACTION_DATE
, PLL.ORG_ID OU_ID
, POL.ITEM_ID ITEM_ID
, POH.CURRENCY_CODE CURRENCY
FROM RCV_TRANSACTIONS RTP
, PO_LINE_LOCATIONS_ALL PLL
, PO_LINES_ALL POL
, PO_HEADERS_ALL POH
, RCV_TRANSACTIONS RT
WHERE RT.TRANSACTION_TYPE IN ('RECEIVE'
, 'RETURN TO VENDOR'
, 'CORRECT')
AND RT.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND PLL.RECEIPT_REQUIRED_FLAG = 'Y'
AND RT.PO_HEADER_ID = POH.PO_HEADER_ID
AND RT.PO_LINE_ID = POL.PO_LINE_ID
AND RT.PARENT_TRANSACTION_ID = RTP.TRANSACTION_ID (+)
AND NVL(RTP.TRANSACTION_TYPE
, 'RECEIVE') IN ('RECEIVE'
, 'RETURN TO VENDOR') AND( PLL.CONSIGNED_FLAG = 'N' OR PLL.CONSIGNED_FLAG IS NULL)
AND NVL(PLL.DROP_SHIP_FLAG
, 'N')='N' UNION ALL SELECT DECODE( POL.MATCHING_BASIS
, 'AMOUNT'
, (PLL.AMOUNT - PLL.AMOUNT_CANCELLED)
, (PLL.QUANTITY - PLL.QUANTITY_CANCELLED) * PLL.PRICE_OVERRIDE) PURCHASE_AMOUNT
, TO_NUMBER(NULL) SALES_AMOUNT
, TO_CHAR(PLL.CREATION_DATE
, 'MONTH YYYY') MONTH_BUCKET
, PLL.CREATION_DATE TRANSACTION_DATE
, PLL. ORG_ID OU_ID
, POL.ITEM_ID ITEM_ID
, POH.CURRENCY_CODE CURRENCY
FROM PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL PLL
WHERE POH.PO_HEADER_ID = PLL.PO_HEADER_ID
AND POL.PO_LINE_ID = PLL.PO_LINE_ID
AND NVL(PLL.RECEIPT_REQUIRED_FLAG
, 'N') = 'N'
AND PLL.APPROVED_FLAG = 'Y'
AND ((POH.TYPE_LOOKUP_CODE = 'STANDARD') OR (POH.TYPE_LOOKUP_CODE IN ('BLANKET'
, 'PLANNED')
AND PLL.SHIPMENT_TYPE IN ('BLANKET'
, 'SCHEDULED')))
AND ( PLL.CONSIGNED_FLAG ='N' OR PLL.CONSIGNED_FLAG IS NULL)
AND NVL(PLL.DROP_SHIP_FLAG
, 'N')='N' UNION ALL SELECT TO_NUMBER(NULL) PURCHASE_AMOUNT
, DECODE(LINES.ITEM_TYPE_CODE
, 'INCLUDED'
, 0
, INV_CONVERT.INV_UM_CONVERT(DETAILS.INVENTORY_ITEM_ID
, 5
, NVL(DETAILS.SHIPPED_QUANTITY
, 0)
, DETAILS.REQUESTED_QUANTITY_UOM
, DETAILS.SRC_REQUESTED_QUANTITY_UOM
, NULL
, NULL)* LINES.UNIT_SELLING_PRICE) SALES_AMOUNT
, TO_CHAR(DELIVERIES.INITIAL_PICKUP_DATE
, 'MONTH YYYY') MONTH_BUCKET
, DELIVERIES.INITIAL_PICKUP_DATE TRANSACTION_DATE
, LINES.ORG_ID OU_ID
, TO_NUMBER(NULL) ITEM_ID
, NVL(DELIVERIES.CURRENCY_CODE
, DETAILS.CURRENCY_CODE) CURRENCY
FROM WSH_NEW_DELIVERIES_OB_GRP_V DELIVERIES
, WSH_DELIVERY_DETAILS_OB_GRP_V DETAILS
, OE_ORDER_LINES_ALL LINES
, WSH_DELIVERY_ASSIGNMENTS ASSIGNMENTS
WHERE DETAILS.SOURCE_CODE='OE'
AND DETAILS.SOURCE_LINE_ID=LINES.LINE_ID
AND DELIVERIES.DELIVERY_ID(+)= ASSIGNMENTS.DELIVERY_ID
AND ASSIGNMENTS.DELIVERY_DETAIL_ID(+)=DETAILS.DELIVERY_DETAIL_ID
AND DETAILS.RELEASED_STATUS IN ('S'
, 'Y'
, 'C')
AND NVL(LINES.SOURCE_DOCUMENT_TYPE_ID
, -99) <> 10

Columns