View: POABV_EDW_RECEIVING_TXN_FCV

Product:	PO - Purchasing
Description:
Implementation/DBA Data:	APPS.POABV_EDW_RECEIVING_TXN_FCV

View Text

SELECT /*+ ALL_ROWS */ INC.SEQ_ID
, 1
, RCV.TRANSACTION_ID ||'-'|| ELI.INSTANCE_CODE
, ELI.INSTANCE_CODE
, RCV.TRANSACTION_TYPE || '-' || 'RCV TRANSACTION TYPE' || '-' || 'PO'
, EDW_ITEMS_PKG.ITEM_ORG_FK (POL.ITEM_ID
, DECODE(POL.ITEM_ID
, NULL
, POH.ORG_ID
, FSP.INVENTORY_ORGANIZATION_ID)
, POL.ITEM_DESCRIPTION
, POL.CATEGORY_ID
, ELI.INSTANCE_CODE)
, PLT.LINE_TYPE
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY'
, EDW_UTIL.GET_EDW_BASE_UOM(MTLU.UOM_CODE
, POL.ITEM_ID)
, 'NA_EDW')
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY'
, EDW_UTIL.GET_EDW_UOM(MTLU.UOM_CODE
, POL.ITEM_ID)
, 'NA_EDW')
, DECODE(RSH.RECEIPT_SOURCE_CODE
, NULL
, NULL
, RSH.RECEIPT_SOURCE_CODE || '-' || 'SHIPMENT SOURCE TYPE' || '-' || 'PO')
, EDW_TRD_PARTNER_PKG.SUPPLIER_SITE_FK (POH.VENDOR_SITE_ID
, POH.ORG_ID
, ELI.INSTANCE_CODE)
, DECODE(POH.VENDOR_SITE_ID
, NULL
, 'NA_EDW'
, PVS.CITY || '-' || PVS.ZIP || '-' || DECODE(PVS.STATE
, NULL
, PVS.PROVINCE
, PVS.STATE) || '-' || PVS.COUNTRY)
, DECODE(RCV.DESTINATION_TYPE_CODE
, NULL
, NULL
, RCV.DESTINATION_TYPE_CODE || '-'||'RCV DESTINATION TYPE'|| '-'||'PO')
, EDW_ORGANIZATION_PKG.INT_ORGANIZATION_FK (PLL.SHIP_TO_ORGANIZATION_ID
, ELI.INSTANCE_CODE)
, DECODE(RCV.LOCATION_ID
, NULL
, 'NA_EDW'
, DECODE(HRL1.LOCATION_ID
, NULL
, EDW_GEOGRAPHY_PKG.HZ_POSTCODE_CITY_FK(RCV.LOCATION_ID)
, HRL1.TOWN_OR_CITY || '-' || HRL1.POSTAL_CODE || '-' || HRL1.REGION_2 || '-' || HRL1.COUNTRY))
, DECODE(RCV.DELIVER_TO_LOCATION_ID
, NULL
, 'NA_EDW'
, DECODE(HRL2.LOCATION_ID
, NULL
, EDW_GEOGRAPHY_PKG.HZ_POSTCODE_CITY_FK(RCV.DELIVER_TO_LOCATION_ID)
, HRL2.TOWN_OR_CITY || '-' || HRL2.POSTAL_CODE || '-' || HRL2.REGION_2 || '-' || HRL2.COUNTRY))
, DECODE(MIL.PHYSICAL_LOCATION_ID
, NULL
, EDW_MTL_INVENTORY_LOC_PKG.GET_LOCATOR_FK (RCV.LOCATOR_ID
, RCV.ORGANIZATION_ID
, RCV.SUBINVENTORY)
, EDW_MTL_INVENTORY_LOC_PKG.GET_LOCATOR_FK (MIL.PHYSICAL_LOCATION_ID
, MIL.ORGANIZATION_ID
, MIL.SUBINVENTORY_CODE))
, EDW_HR_PERSON_PKG.REGULAR_EMPLOYEE_FK (POH.AGENT_ID
, ELI.INSTANCE_CODE)
, EDW_HR_PERSON_PKG.REGULAR_EMPLOYEE_FK (RCV.DELIVER_TO_PERSON_ID
, ELI.INSTANCE_CODE)
, EDW_TIME_PKG.CAL_DAY_FK (RCV.TRANSACTION_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)
, EDW_TIME_PKG.CAL_DAY_FK (RCV.CREATION_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)
, EDW_TIME_PKG.CAL_DAY_FK (RSH.SHIPPED_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)
, EDW_TIME_PKG.CAL_DAY_FK (RSH.EXPECTED_RECEIPT_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)
, EDW_TIME_PKG.CAL_DAY_FK (PLL.PROMISED_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)
, EDW_TIME_PKG.CAL_DAY_FK (PLL.NEED_BY_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)
, EDW_TIME_PKG.CAL_DAY_FK (PLL.LAST_ACCEPT_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)
, EDW_TIME_PKG.CAL_DAY_FK (PLL.CREATION_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)
, EDW_TIME_PKG.CAL_DAY_FK (RCV_PARENT.TRANSACTION_DATE
, FSP.SET_OF_BOOKS_ID
, ELI.INSTANCE_CODE)
, DECODE(RSH.PAYMENT_TERMS_ID
, NULL
, NULL
, RSH.PAYMENT_TERMS_ID || 'AP' || '-' || ELI.INSTANCE_CODE)
, DECODE(RRH.ROUTING_NAME
, NULL
, NULL
, UPPER(RRH.ROUTING_NAME) || '-' || 'RCV ROUTING'|| '-' || 'ROU')
, DECODE(RCV.SUBSTITUTE_UNORDERED_CODE
, NULL
, NULL
, RCV.SUBSTITUTE_UNORDERED_CODE || '-'||'VENDOR RECEIPT OPTION' || '-' || 'PO')
, DECODE(RCV_PARENT.TRANSACTION_TYPE
, NULL
, NULL
, RCV_PARENT.TRANSACTION_TYPE || '-' || 'RCV TRANSACTION TYPE' || '-' || 'PO')
, DECODE(RSH.FREIGHT_CARRIER_CODE
, NULL
, NULL
, UPPER(RSH.FREIGHT_CARRIER_CODE) || '-' || 'SHIP VIA TYPE' || '-' || 'ORG')
, NVL(RCV.INSPECTION_STATUS_CODE
, 'NOT INSPECTED') || '-' || 'INSPECTION STATUS' || '-' || 'PO'
, DECODE(RCV.INSPECTION_QUALITY_CODE
, NULL
, NULL
, UPPER(RCV.INSPECTION_QUALITY_CODE) || '-' || 'PO QUALITY CODE' || '-' || 'POQ')
, NVL(RCV.RECEIPT_EXCEPTION_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(RCV.USER_ENTERED_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, DECODE(RCV.REASON_ID
, NULL
, NULL
, UPPER(MTR.REASON_NAME)|| '-'|| 'TXN REASON' || '-' || 'MTR')
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE)
, EDW_LOOKUP_PKG.LOOKUP_CODE_FK( 'PO'
, 'PURCHASE CLASSIFICATION'
, POA_CUSTOMIZATION_PKG.PURCHASE_CLASSIFICATION_CODE( RCV.TRANSACTION_ID
, 'RCV_TRANSACTIONS'))
, EDW_SUPPLIER_ITEM_PKG.SUPPLIER_ITEM_FK ( POV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, POL.VENDOR_PRODUCT_NUM) /*** MEASURES ***/
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, RCV.QUANTITY * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID))
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, (RCV.QUANTITY + POA_EDW_RCV_TXNS_PKG.QTY_CORRECTED (RCV.TRANSACTION_ID)) * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID))
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, RSL.QUANTITY_RECEIVED * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID))
, DECODE(RCV.TRANSACTION_TYPE
, 'RECEIVE'
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, POA_EDW_RCV_TXNS_PKG.QTY_NET_CHILD_TXNS ( RSL.SHIPMENT_LINE_ID
, 'ACCEPT') * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID))
, NULL)
, DECODE(RCV.TRANSACTION_TYPE
, 'RECEIVE'
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, POA_EDW_RCV_TXNS_PKG.QTY_NET_CHILD_TXNS ( RSL.SHIPMENT_LINE_ID
, 'REJECT') * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID))
, NULL)
, DECODE(RCV.TRANSACTION_TYPE
, 'RECEIVE'
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, POA_EDW_RCV_TXNS_PKG.QTY_NET_CHILD_TXNS ( RSL.SHIPMENT_LINE_ID
, 'RETURN TO VENDOR') * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID))
, NULL)
, DECODE(RCV.TRANSACTION_TYPE
, 'RECEIVE'
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, POA_EDW_RCV_TXNS_PKG.QTY_NET_CHILD_TXNS ( RSL.SHIPMENT_LINE_ID
, 'RETURN TO RECEIVING') * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID))
, NULL)
, DECODE(RCV.TRANSACTION_TYPE
, 'RECEIVE'
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, POA_EDW_RCV_TXNS_PKG.QTY_NET_CHILD_TXNS ( RSL.SHIPMENT_LINE_ID
, 'DELIVER') * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID))
, NULL)
, DECODE(RCV.TRANSACTION_TYPE
, 'RECEIVE'
, DECODE(POL.ORDER_TYPE_LOOKUP_CODE
, 'AMOUNT'
, NULL
, POA_EDW_RCV_TXNS_PKG.QTY_NET_CHILD_TXNS ( RSL.SHIPMENT_LINE_ID
, 'TRANSFER') * EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID))
, NULL)
, PLL.PRICE_OVERRIDE / EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID)
, DECODE(POH.RATE_TYPE
, 'USER'
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT ( POH.RATE*PLL.PRICE_OVERRIDE
, TO_NUMBER(NULL)
, GSOB.CURRENCY_CODE
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, PLL.CREATION_DATE)
, NULL)
, EDW_CURRENCY.CONVERT_GLOBAL_AMOUNT ( PLL.PRICE_OVERRIDE
, TO_NUMBER(NULL)
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE)
, FSP.SET_OF_BOOKS_ID
, NVL(POH.RATE_DATE
, PLL.CREATION_DATE)
, POH.RATE_TYPE)) / EDW_UTIL.GET_UOM_CONV_RATE (MTLU.UOM_CODE
, POL.ITEM_ID)
, DECODE(RCV.TRANSACTION_TYPE
, 'RECEIVE'
, POA_EDW_RCV_TXNS_PKG.DATE_LAST_DELIVERY(RSL.SHIPMENT_LINE_ID) - RCV.TRANSACTION_DATE
, NULL) /*** ATTRIBUTES ***/
, RSH.SHIPMENT_NUM
, API.INVOICE_NUM
, RSH.RECEIPT_NUM || '-' || RCV.ORGANIZATION_ID
, POH.SEGMENT1
, NVL(RCV.VENDOR_LOT_NUM
, RSL.VENDOR_LOT_NUM)
, RCV.RMA_REFERENCE
, RCV.COMMENTS
, RSH.COMMENTS
, RSH.WAYBILL_AIRBILL_NUM
, RSH.BILL_OF_LADING
, NVL(RSL.PACKING_SLIP
, RSH.PACKING_SLIP) /*** OTHERS ***/
, GREATEST(NVL(RCV_PARENT.LAST_UPDATE_DATE
, TO_DATE('01/01/0001'
, 'MM/DD/YYYY'))
, RCV.LAST_UPDATE_DATE
, NVL(RRH.LAST_UPDATE_DATE
, TO_DATE('01/01/0001'
, 'MM/DD/YYYY'))
, NVL(API.LAST_UPDATE_DATE
, TO_DATE('01/01/0001'
, 'MM/DD/YYYY'))
, NVL(MTR.LAST_UPDATE_DATE
, TO_DATE('01/01/0001'
, 'MM/DD/YYYY'))
, NVL(POV.LAST_UPDATE_DATE
, TO_DATE('01/01/0001'
, 'MM/DD/YYYY'))
, GSOB.LAST_UPDATE_DATE
, MTLU.LAST_UPDATE_DATE
, RSL.LAST_UPDATE_DATE
, RSH.LAST_UPDATE_DATE
, POL.LAST_UPDATE_DATE
, POH.LAST_UPDATE_DATE
, PLT.LAST_UPDATE_DATE
, FSP.LAST_UPDATE_DATE
, PLL.LAST_UPDATE_DATE)
, RCV.CREATION_DATE
, NULL
, NULL
, NULL
, RCV.PO_DISTRIBUTION_ID
, '_DF:PO:PO_HEADERS:POH'
, '_DF:PO:PO_LINES:POL'
, '_DF:PO:PO_PDF_RETURN_FROM:RCV'
FROM POA_EDW_RCV_TXNS_INC INC
, EDW_LOCAL_INSTANCE ELI
, MTL_UNITS_OF_MEASURE MTLU
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, AP_INVOICES_ALL API
, MTL_TRANSACTION_REASONS MTR
, GL_SETS_OF_BOOKS GSOB
, MTL_ITEM_LOCATIONS MIL
, RCV_ROUTING_HEADERS RRH
, PO_VENDORS POV
, PO_LINE_TYPES PLT
, PO_HEADERS_ALL POH
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL PLL
, RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, RCV_TRANSACTIONS RCV_PARENT
, RCV_TRANSACTIONS RCV
, PO_VENDOR_SITES_ALL PVS
, HR_LOCATIONS_ALL HRL1
, HR_LOCATIONS_ALL HRL2
WHERE INC.PRIMARY_KEY = RCV.TRANSACTION_ID
AND RCV.SHIPMENT_HEADER_ID = RSH.SHIPMENT_HEADER_ID
AND RCV.SHIPMENT_LINE_ID = RSL.SHIPMENT_LINE_ID
AND RCV.PARENT_TRANSACTION_ID = RCV_PARENT.TRANSACTION_ID (+)
AND RCV.PO_LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND RCV.ROUTING_HEADER_ID = RRH.ROUTING_HEADER_ID (+)
AND RCV.LOCATOR_ID = MIL.INVENTORY_LOCATION_ID (+)
AND RCV.ORGANIZATION_ID = MIL.ORGANIZATION_ID (+)
AND RCV.INVOICE_ID = API.INVOICE_ID (+)
AND RCV.REASON_ID = MTR.REASON_ID (+)
AND RSL.UNIT_OF_MEASURE = MTLU.UNIT_OF_MEASURE
AND PLL.PO_HEADER_ID = POH.PO_HEADER_ID
AND PLL.PO_LINE_ID = POL.PO_LINE_ID
AND POH.VENDOR_ID = POV.VENDOR_ID (+)
AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND NVL(POH.ORG_ID
, -999) = NVL(FSP.ORG_ID
, -999)
AND FSP.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID
AND PVS.VENDOR_ID (+) = POH.VENDOR_ID
AND PVS.VENDOR_SITE_ID (+) = POH.VENDOR_SITE_ID
AND RCV.LOCATION_ID = HRL1.LOCATION_ID (+)
AND RCV.DELIVER_TO_LOCATION_ID = HRL2.LOCATION_ID (+)
AND POL.MATCHING_BASIS = 'QUANTITY'

Columns