VIEW: APPS.POA_PURCHASE_SALES_V

Source

View Text - Preformatted

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, nvl(details.shipped_quantity,0)* 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

View Text - HTML Formatted

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
, NVL(DETAILS.SHIPPED_QUANTITY
, 0)* 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