eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: POABV_EDW_PO_DISTRIBUTIONS_FCV

Product:	PO - Purchasing
Description:	EDW PO Distribution Fact Collection View
Implementation/DBA Data:	APPS.POABV_EDW_PO_DISTRIBUTIONS_FCV

View Text

SELECT /* ORDERED FULL(INC) USE_NL(POD) USE_NL(PLL) USE_NL(POL) USE_NL(POH) USE_NL(POR) USE_NL(SVNGS) */ POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE) / DECODE(POH.RATE_TYPE
, 'USER'
, POH.RATE
, 1)
, INC.SEQ_ID
, 1
, TO_CHAR(POD.PO_DISTRIBUTION_ID) || '-' || ELI.INSTANCE_CODE
, DECODE(POL.VENDOR_PRODUCT_NUM
, NULL
, 'NA_EDW'
, DECODE(PVS.VENDOR_SITE_CODE
, NULL
, 'NA_EDW'
, POV.VENDOR_NAME || '-' || PVS.VENDOR_SITE_CODE || '-' || POL.VENDOR_PRODUCT_NUM))
, ELI.INSTANCE_CODE
, 'NA_EDW' /* POA_CUSTOMIZATION_PKG.PURCHASE_CLASSIFICATION_CODE( PLL.LINE_LOCATION_ID
, 'PO_LINE_LOCATIONS_ALL') || '-' || 'PURCHASE CLASSIFICATION' || '-' || 'PO' */
, DECODE(NVL(POR.AGENT_ID
, POH.AGENT_ID)
, NULL
, 'NA_EDW'
, NVL(POR.AGENT_ID
, POH.AGENT_ID) || '-' || ELI.INSTANCE_CODE || '-' || 'EMPLOYEE'|| '-' || 'PERS')
, DECODE(POD.DELIVER_TO_PERSON_ID
, NULL
, 'NA_EDW'
, POD.DELIVER_TO_PERSON_ID || '-' || ELI.INSTANCE_CODE || '-' || 'EMPLOYEE'|| '-' || 'PERS')
, DECODE(NVL(POA_EDW_VARIABLES_PKG.APPROVED_BY(POD.PO_HEADER_ID)
, POH.AGENT_ID)
, NULL
, 'NA_EDW'
, NVL(POA_EDW_VARIABLES_PKG.APPROVED_BY(POD.PO_HEADER_ID)
, POH.AGENT_ID) || '-' || ELI.INSTANCE_CODE || '-' || 'EMPLOYEE'|| '-' || 'PERS')
, EDW_ITEMS_PKG.ITEM_ORG_FK(POL.ITEM_ID
, DECODE(POL.ITEM_ID
, NULL
, POD.ORG_ID
, FSP.INVENTORY_ORGANIZATION_ID)
, POL.ITEM_DESCRIPTION
, POL.CATEGORY_ID
, ELI.INSTANCE_CODE)
, DECODE(POH.VENDOR_SITE_ID
, NULL
, 'NA_EDW'
, POH.VENDOR_SITE_ID || '-' || POH.ORG_ID || '-' || ELI.INSTANCE_CODE || '-SUPPLIER_SITE')
, DECODE(PLL.SHIP_TO_ORGANIZATION_ID
, NULL
, 'NA_EDW'
, PLL.SHIP_TO_ORGANIZATION_ID || '-' || ELI.INSTANCE_CODE)
, DECODE(POD.DESTINATION_ORGANIZATION_ID
, NULL
, 'NA_EDW'
, POD.DESTINATION_ORGANIZATION_ID || '-' || ELI.INSTANCE_CODE)
, DECODE(POD.SET_OF_BOOKS_ID
, NULL
, 'NA_EDW'
, POD.SET_OF_BOOKS_ID || '-' || ELI.INSTANCE_CODE)
, DECODE(PLL.SHIP_TO_LOCATION_ID
, NULL
, 'NA_EDW'
, DECODE(HRL1.LOCATION_ID
, NULL
, EDW_GEOGRAPHY_PKG.HZ_POSTCODE_CITY_FK(PLL.SHIP_TO_LOCATION_ID)
, HRL1.TOWN_OR_CITY || '-' || HRL1.POSTAL_CODE || '-' || HRL1.REGION_2 || '-' || HRL1.COUNTRY))
, DECODE(POH.BILL_TO_LOCATION_ID
, NULL
, 'NA_EDW'
, HRL2.TOWN_OR_CITY || '-' || HRL2.POSTAL_CODE || '-' || HRL2.REGION_2 || '-' || HRL2.COUNTRY)
, DECODE(POD.DELIVER_TO_LOCATION_ID
, NULL
, 'NA_EDW'
, DECODE(HRL3.LOCATION_ID
, NULL
, EDW_GEOGRAPHY_PKG.HZ_POSTCODE_CITY_FK(POD.DELIVER_TO_LOCATION_ID)
, HRL3.TOWN_OR_CITY || '-' || HRL3.POSTAL_CODE || '-' || HRL3.REGION_2 || '-' || HRL3.COUNTRY))
, DECODE(POH.VENDOR_SITE_ID
, NULL
, 'NA_EDW'
, PVS.CITY || '-' || PVS.ZIP || '-' || DECODE(PVS.STATE
, NULL
, PVS.PROVINCE
, PVS.STATE) || '-' || PVS.COUNTRY)
, DECODE(POD.TASK_ID
, NULL
, DECODE(POD.PROJECT_ID
, NULL
, 'NA_EDW'
, POD.PROJECT_ID || '-' || ELI.INSTANCE_CODE || '-PJ-PRJ')
, DECODE(POD.TASK_ID
, NULL
, 'NA_EDW'
, POD.TASK_ID || '-' || ELI.INSTANCE_CODE))
, DECODE(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.CREATION_DATE
, POR.CREATION_DATE)
, NULL
, 'NA_EDW'
, TO_CHAR(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.CREATION_DATE
, POR.CREATION_DATE)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POL.CREATION_DATE
, PLL.CREATION_DATE)
, NULL
, 'NA_EDW'
, TO_CHAR(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POL.CREATION_DATE
, PLL.CREATION_DATE)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(PLL.CREATION_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(PLL.CREATION_DATE
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(POD.CREATION_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(POD.CREATION_DATE
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(SVNGS.APPROVED_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(SVNGS.APPROVED_DATE
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.REVISED_DATE
, POR.REVISED_DATE)
, NULL
, 'NA_EDW'
, TO_CHAR(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.REVISED_DATE
, POR.REVISED_DATE)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.ACCEPTANCE_DUE_DATE
, POR.ACCEPTANCE_DUE_DATE)
, NULL
, 'NA_EDW'
, TO_CHAR(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.ACCEPTANCE_DUE_DATE
, POR.ACCEPTANCE_DUE_DATE)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.CREATION_DATE
, POR.RELEASE_DATE)
, NULL
, 'NA_EDW'
, TO_CHAR(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.CREATION_DATE
, POR.RELEASE_DATE)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE( NVL(POA_OLTP_GENERIC_PKG.GET_APPROVED_DATE_PLL(POD.CREATION_DATE
, PLL.LINE_LOCATION_ID)
, PLL.APPROVED_DATE)
, NULL
, 'NA_EDW'
, TO_CHAR ( NVL(POA_OLTP_GENERIC_PKG.GET_APPROVED_DATE_PLL(POD.CREATION_DATE
, PLL.LINE_LOCATION_ID)
, PLL.APPROVED_DATE)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(PLL.LAST_ACCEPT_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(PLL.LAST_ACCEPT_DATE
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(PLL.NEED_BY_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(PLL.NEED_BY_DATE
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(PLL.PROMISED_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(PLL.PROMISED_DATE
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.PRINTED_DATE
, POR.PRINTED_DATE)
, NULL
, 'NA_EDW'
, TO_CHAR(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.PRINTED_DATE
, POR.PRINTED_DATE)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, NULL
, 'NA_EDW'
, TO_CHAR(NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(PRQ.CREATION_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(PRQ.CREATION_DATE
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(POA_EDW_VARIABLES_PKG.GET_REQ_APPROVAL_DATE(POD.REQ_DISTRIBUTION_ID)
, NULL
, 'NA_EDW'
, TO_CHAR(POA_EDW_VARIABLES_PKG.GET_REQ_APPROVAL_DATE(POD.REQ_DISTRIBUTION_ID)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(DECODE(POD.PO_RELEASE_ID
, NULL
, POA_EDW_VARIABLES_PKG.GET_ACCEPTANCE_DATE (POD.PO_HEADER_ID
, 'P')
, POA_EDW_VARIABLES_PKG.GET_ACCEPTANCE_DATE (POD.PO_RELEASE_ID
, 'R'))
, NULL
, 'NA_EDW'
, TO_CHAR(DECODE(POD.PO_RELEASE_ID
, NULL
, POA_EDW_VARIABLES_PKG.GET_ACCEPTANCE_DATE (POD.PO_HEADER_ID
, 'P')
, POA_EDW_VARIABLES_PKG.GET_ACCEPTANCE_DATE (POD.PO_RELEASE_ID
, 'R'))
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(POH.TERMS_ID
, NULL
, NULL
, POH.TERMS_ID || '-' || 'AP' || '-' || ELI.INSTANCE_CODE)
, DECODE(PLL.SHIPMENT_TYPE
, NULL
, NULL
, (UPPER(PLL.SHIPMENT_TYPE) || '-' || 'SHIPMENT TYPE' || '-' || 'PO'))
, DECODE(POH.SHIP_VIA_LOOKUP_CODE
, NULL
, NULL
, (UPPER(POH.SHIP_VIA_LOOKUP_CODE) || '-' || 'SHIP VIA TYPE' || '-' || 'ORG'))
, DECODE(POH.FOB_LOOKUP_CODE
, NULL
, NULL
, (UPPER(POH.FOB_LOOKUP_CODE) || '-' || 'FOB' || '-' || 'PO'))
, DECODE(POH.FREIGHT_TERMS_LOOKUP_CODE
, NULL
, NULL
, (UPPER(POH.FREIGHT_TERMS_LOOKUP_CODE) || '-' || 'FREIGHT TERMS' || '-' || 'PO'))
, DECODE(POL.TRANSACTION_REASON_CODE
, NULL
, NULL
, (UPPER(POL.TRANSACTION_REASON_CODE) || '-' || 'TRANSACTION REASON' || '-' || 'PO'))
, DECODE(POL.PRICE_TYPE_LOOKUP_CODE
, NULL
, NULL
, (UPPER(POL.PRICE_TYPE_LOOKUP_CODE) || '-' || 'PRICE TYPE' || '-' || 'PO'))
, DECODE(POL.PRICE_BREAK_LOOKUP_CODE
, NULL
, NULL
, (UPPER(POL.PRICE_BREAK_LOOKUP_CODE) || '-' || 'PRICE BREAK TYPE' || '-' || 'PO'))
, DECODE(PLL.CLOSED_CODE
, NULL
, NULL
, (UPPER(PLL.CLOSED_CODE) || '-' || 'DOCUMENT STATE' || '-' || 'PO'))
, DECODE(POD.DESTINATION_TYPE_CODE
, NULL
, NULL
, (UPPER(POD.DESTINATION_TYPE_CODE) || '-' || 'DESTINATION TYPE' || '-' || 'PO'))
, DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, DECODE(POL.CONTRACT_ID
, NULL
, ('STANDARD' || '-' || 'AGREEMENT TYPE' || '-' || 'POD')
, ('CONTRACT' || '-' || 'AGREEMENT TYPE' || '-' || 'POD'))
, 'SCHEDULED'
, ('PLANNED' || '-' || 'AGREEMENT TYPE' || '-' || 'POD')
, 'BLANKET'
, ('BLANKET' || '-' || 'AGREEMENT TYPE' || '-' || 'POD'))
, DECODE(RRH.ROUTING_NAME
, NULL
, NULL
, UPPER(RRH.ROUTING_NAME) || '-' || 'RCV ROUTING' || '-' || 'ROU')
, NVL(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.ACCEPTANCE_REQUIRED_FLAG
, POR.ACCEPTANCE_REQUIRED_FLAG)
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.FROZEN_FLAG
, POR.FROZEN_FLAG)
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.USER_HOLD_FLAG
, POR.HOLD_FLAG)
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(POH.CONFIRMING_ORDER_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(POL.NEGOTIATED_BY_PREPARER_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(PLL.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(PLL.APPROVED_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(PLL.CANCEL_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(PLL.INSPECTION_REQUIRED_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(PLL.RECEIPT_REQUIRED_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(POD.ACCRUED_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(POD.ENCUMBERED_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, DECODE(POD.REQ_DISTRIBUTION_ID
, NULL
, 'N'
, 'Y') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(PLL.TAXABLE_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, NVL(POH.EDI_PROCESSED_FLAG
, 'N') || '-' || 'YES_NO' || '-' || 'FND'
, DECODE(POH.PCARD_ID
, NULL
, 'N'
, 'Y') || '-' || 'YES_NO' || '-' || 'FND'
, POA_EDW_VARIABLES_PKG.GET_SUPPLIER_APPROVED( POD.PO_DISTRIBUTION_ID
, POH.VENDOR_ID
, POH.VENDOR_SITE_ID
, PLL.SHIP_TO_ORGANIZATION_ID
, POL.ITEM_ID
, POL.CATEGORY_ID ) || '-' || 'YES_NO' || '-' || 'FND'
, 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(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, PLT.LINE_TYPE
, DECODE(INC.CHECK_CUT_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(INC.CHECK_CUT_DATE
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(INC.INVOICE_RECEIVED_DATE
, NULL
, 'NA_EDW'
, TO_CHAR(INC.INVOICE_RECEIVED_DATE
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(POA_EDW_VARIABLES_PKG.GET_INVOICE_CREATION_DATE (POD.PO_DISTRIBUTION_ID)
, NULL
, 'NA_EDW'
, TO_CHAR(POA_EDW_VARIABLES_PKG.GET_INVOICE_CREATION_DATE (POD.PO_DISTRIBUTION_ID)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, DECODE(POA_EDW_VARIABLES_PKG.GET_GOODS_RECEIVED_DATE (PLL.LINE_LOCATION_ID)
, NULL
, 'NA_EDW'
, TO_CHAR(POA_EDW_VARIABLES_PKG.GET_GOODS_RECEIVED_DATE (PLL.LINE_LOCATION_ID)
, 'DD-MM-YYYY') || '-'|| GSOB.PERIOD_SET_NAME || '-'|| GSOB.ACCOUNTED_PERIOD_TYPE || '-'|| ELI.INSTANCE_CODE || '-CD')
, NULL
, NULL
, NULL
, TO_NUMBER(SVNGS.APPROVED_DATE - DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POD.CREATION_DATE
, POR.CREATION_DATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, TO_NUMBER(INC.CHECK_CUT_DATE - SVNGS.APPROVED_DATE))
, CASE WHEN PLL.CONSIGNED_FLAG='Y' OR POH.CONSIGNED_CONSUMPTION_FLAG='Y' OR POR.CONSIGNED_CONSUMPTION_FLAG='Y' THEN NULL ELSE TO_NUMBER(INC.CHECK_CUT_DATE - POA_EDW_VARIABLES_PKG.GET_GOODS_RECEIVED_DATE (PLL.LINE_LOCATION_ID)) END
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, TO_NUMBER(INC.INVOICE_RECEIVED_DATE - POA_EDW_VARIABLES_PKG.GET_INVOICE_CREATION_DATE (POD.PO_DISTRIBUTION_ID)))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, TO_NUMBER(INC.CHECK_CUT_DATE - INC.INVOICE_RECEIVED_DATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, POA_EDW_VARIABLES_PKG.GET_IPV(POD.PO_DISTRIBUTION_ID)/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (POA_EDW_VARIABLES_PKG.GET_IPV(POD.PO_DISTRIBUTION_ID)/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE)) * POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, NVL(SVNGS.PURCHASE_AMOUNT/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE)
, 0))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (NVL(SVNGS.PURCHASE_AMOUNT/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE)
, 0)) * POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, NVL(SVNGS.CONTRACT_AMOUNT/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE)
, 0))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (NVL(SVNGS.CONTRACT_AMOUNT/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE)
, 0)) * POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, NVL(SVNGS.NON_CONTRACT_AMOUNT/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE)
, 0))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (NVL(SVNGS.NON_CONTRACT_AMOUNT/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE)
, 0)) * POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, NVL(SVNGS.POT_CONTRACT_AMOUNT/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE)
, 0))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (NVL(SVNGS.POT_CONTRACT_AMOUNT/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE)
, 0)) * POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, NVL(SVNGS.POTENTIAL_SAVING/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE)
, 0))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (NVL(SVNGS.POTENTIAL_SAVING/DECODE(POD.RATE
, NULL
, 1
, 0
, 1
, POD.RATE)
, 0)) * POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, POD.AMOUNT_BILLED)
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, POD.AMOUNT_BILLED * POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (CASE WHEN PLT.ORDER_TYPE_LOOKUP_CODE <> 'QUANTITY' THEN NULL ELSE (POD.QUANTITY_BILLED * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID)) END))
, CASE WHEN POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR PLT.ORDER_TYPE_LOOKUP_CODE <> 'QUANTITY' THEN NULL ELSE POD.QUANTITY_CANCELLED * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID) END
, CASE WHEN POH.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR POR.CONSIGNED_CONSUMPTION_FLAG = 'Y' OR PLT.ORDER_TYPE_LOOKUP_CODE <> 'QUANTITY' THEN NULL ELSE POD.QUANTITY_DELIVERED * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID) END
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (CASE WHEN PLT.ORDER_TYPE_LOOKUP_CODE <> 'QUANTITY' THEN NULL ELSE(POD.QUANTITY_ORDERED * POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID)) END))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, PLL.PRICE_OVERRIDE / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (PLL.PRICE_OVERRIDE / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID))* POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, POL.LIST_PRICE_PER_UNIT / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (POL.LIST_PRICE_PER_UNIT / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID)) * POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, POL.MARKET_PRICE / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (POL.MARKET_PRICE / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID)) * POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, POL.NOT_TO_EXCEED_PRICE / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID))
, DECODE(PLL.CONSIGNED_FLAG
, 'Y'
, NULL
, (POL.NOT_TO_EXCEED_PRICE / POA_EDW_VARIABLES_PKG.GET_UOM_CONV_RATE(MTLU.UOM_CODE
, POL.ITEM_ID)) * POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE (POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE))
, POL.ITEM_ID
, POL.ITEM_DESCRIPTION
, DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.NOTE_TO_VENDOR
, POR.NOTE_TO_VENDOR)
, POL.NOTE_TO_VENDOR
, POH.COMMENTS
, POH.NOTE_TO_RECEIVER
, POL.VENDOR_PRODUCT_NUM
, PLL.CANCEL_REASON
, PLL.CLOSED_REASON
, PLL.SOURCE_SHIPMENT_ID
, POD.PO_DISTRIBUTION_ID
, POD.LINE_LOCATION_ID
, POD.PO_HEADER_ID
, POD.PO_LINE_ID
, POD.PO_RELEASE_ID
, POD.SOURCE_DISTRIBUTION_ID
, DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH.REVISION_NUM
, POR.REVISION_NUM)
, POH.SEGMENT1
, DECODE(GA_POH.GLOBAL_AGREEMENT_FLAG
, 'Y'
, GA_POH.SEGMENT1
, DECODE(PLL.SHIPMENT_TYPE
, 'STANDARD'
, POH2.SEGMENT1
, POH.SEGMENT1))
, POR.RELEASE_NUM
, POD.CODE_COMBINATION_ID
, GREATEST(POH.LAST_UPDATE_DATE
, POL.LAST_UPDATE_DATE
, PLL.LAST_UPDATE_DATE
, POD.LAST_UPDATE_DATE)
, DECODE(POA_EDW_VARIABLES_PKG.GET_GLOBAL_CURRENCY_RATE(POH.RATE_TYPE
, DECODE(POH.RATE_TYPE
, 'USER'
, GSOB.CURRENCY_CODE
, NVL(POH.CURRENCY_CODE
, GSOB.CURRENCY_CODE))
, NVL(POD.RATE_DATE
, POD.CREATION_DATE)
, POH.RATE)/DECODE(POH.RATE_TYPE
, 'USER'
, POH.RATE
, 1)
, -1
, 'RATE NOT AVAILABLE'
, -2
, 'INVALID CURRENCY'
, 'LOCAL READY')
, '_DF:PO:PO_HEADERS:POH'
, '_DF:PO:PO_LINES:POL'
, '_DF:PO:PO_DISTRIBUTIONS:POD'
, '_DF:PO:PO_LINE_LOCATIONS:PLL'
FROM POA_EDW_PO_DIST_INC INC
, PO_DISTRIBUTIONS_ALL POD
, PO_LINE_LOCATIONS_ALL PLL
, PO_LINES_ALL POL
, PO_HEADERS_ALL POH
, PO_RELEASES_ALL POR
, POA_BIS_SAVINGS SVNGS
, PO_REQ_DISTRIBUTIONS_ALL PRQ
, GL_SETS_OF_BOOKS GSOB
, RCV_ROUTING_HEADERS RRH
, MTL_UNITS_OF_MEASURE MTLU
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, PO_VENDORS POV
, PO_LINE_TYPES PLT
, EDW_LOCAL_INSTANCE ELI
, PO_VENDOR_SITES_ALL PVS
, HR_LOCATIONS_ALL HRL1
, HR_LOCATIONS_ALL HRL2
, HR_LOCATIONS_ALL HRL3
, PO_HEADERS_ALL GA_POH
, PO_HEADERS_ALL POH2
WHERE INC.PRIMARY_KEY = POD.PO_DISTRIBUTION_ID
AND PLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID
AND PLL.APPROVED_FLAG = 'Y'
AND POD.PO_LINE_ID = POL.PO_LINE_ID
AND POD.PO_HEADER_ID = POH.PO_HEADER_ID
AND POD.PO_DISTRIBUTION_ID = SVNGS.DISTRIBUTION_TRANSACTION_ID (+)
AND POD.PO_RELEASE_ID = POR.PO_RELEASE_ID (+)
AND POD.SET_OF_BOOKS_ID = GSOB.SET_OF_BOOKS_ID
AND NVL(POD.ORG_ID
, -999) = NVL(FSP.ORG_ID
, -999)
AND POD.REQ_DISTRIBUTION_ID = PRQ.DISTRIBUTION_ID (+)
AND POH.VENDOR_ID = POV.VENDOR_ID (+)
AND PLL.RECEIVING_ROUTING_ID = RRH.ROUTING_HEADER_ID (+)
AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND POL.UNIT_MEAS_LOOKUP_CODE = MTLU.UNIT_OF_MEASURE(+)
AND PVS.VENDOR_ID (+)= POH.VENDOR_ID
AND PVS.VENDOR_SITE_ID (+)= POH.VENDOR_SITE_ID
AND PLL.SHIP_TO_LOCATION_ID = HRL1.LOCATION_ID (+)
AND POH.BILL_TO_LOCATION_ID = HRL2.LOCATION_ID (+)
AND POD.DELIVER_TO_LOCATION_ID = HRL3.LOCATION_ID (+)
AND POL.FROM_HEADER_ID = GA_POH.PO_HEADER_ID (+)
AND NVL(POD.DISTRIBUTION_TYPE
, '-99') <> 'AGREEMENT'
AND POL.CONTRACT_ID =POH2.PO_HEADER_ID(+)

Columns

Name
GLOBAL_CURRENCY_RATE
SEQ_ID
VIEW_ID
PO_DIST_INST_PK
SUPPLIER_ITEM_FK
INSTANCE_FK
PURCH_CLASS_FK
BUYER_FK
DELIVER_TO_FK
APPROVER_FK
ITEM_FK
SUPPLIER_SITE_FK
SHIP_TO_ORG_FK
DESTIN_ORG_FK
SOB_FK
SHIP_LOCATION_FK
BILL_LOCATION_FK
DELIV_LOCATION_FK
SUP_SITE_GEOG_FK
TASK_FK
PO_CREATE_DATE_FK
LNE_CREAT_DATE_FK
SHP_CREAT_DATE_FK
DST_CREAT_DATE_FK
PO_APP_DATE_FK
REVISED_DATE_FK
ACCPT_DUE_DATE_FK
RELEASE_DATE_FK
SHP_APP_DATE_FK
LST_ACCPT_DATE_FK
NEED_BY_DATE_FK
PROMISED_DATE_FK
PRINTED_DATE_FK
TXN_CUR_DATE_FK
REQ_CREAT_DATE_FK
REQ_APPRV_DATE_FK
PO_ACCEPT_DATE_FK
AP_TERMS_FK
SHIPMENT_TYPE_FK
SHIP_VIA_FK
FOB_FK
FREIGHT_TERMS_FK
TXN_REASON_FK
PRICE_TYPE_FK
PRICE_BREAK_FK
SHP_CLOSED_FK
DESTIN_TYPE_FK
CONTRACT_TYPE_FK
RCV_ROUTING_FK
ACCPT_REQUIRED_FK
FROZEN_FK
RELEASE_HOLD_FK
CONFIRM_ORDER_FK
NEG_BY_PREPARE_FK
SUB_RECEIPT_FK
SHP_APPROVED_FK
SHP_CANCELLED_FK
INSPECTION_REQ_FK
RECEIPT_REQ_FK
ACCRUED_FK
DST_ENCUMB_FK
ONLINE_REQ_FK
SHP_TAXABLE_FK
EDI_PROCESSED_FK
PCARD_PROCESS_FK
APPRV_SUPPLIER_FK
EDW_BASE_UOM_FK
EDW_UOM_FK
TXN_CUR_CODE_FK
PO_LINE_TYPE_FK
CHECK_CUT_DATE_FK
INV_RECEIVED_DATE_FK
INV_CREATION_DATE_FK
GOODS_RECEIVED_DATE_FK
DUNS_FK
UNSPSC_FK
SIC_CODE_FK
PO_CREATION_CYCLE_TIME
ORDER_TO_PAY_CYCLE_TIME
RECEIVE_TO_PAY_CYCL_TIME
INV_CREATION_CYCLE_TIME
INV_TO_PAY_CYCLE_TIME
IPV_T
IPV_G
AMT_PURCHASED_T
AMT_PURCHASED_G
AMT_CONTRACT_T
AMT_CONTRACT_G
AMT_NONCONTRACT_T
AMT_NONCONTRACT_G
AMT_LEAKAGE_T
AMT_LEAKAGE_G
POTENTIAL_SVG_T
POTENTIAL_SVG_G
AMT_BILLED_T
AMT_BILLED_G
QTY_BILLED_B
QTY_CANCELLED_B
QTY_DELIVERED_B
QTY_ORDERED_B
PRICE_T
PRICE_G
LIST_PRC_UNIT_T
LIST_PRC_UNIT_G
MARKET_PRICE_T
MARKET_PRICE_G
PRICE_LIMIT_T
PRICE_LIMIT_G
ITEM_ID
ITEM_DESCRIPTION
SUPPLIER_NOTE
LNE_SUPPLIER_NOTE
PO_COMMENTS
PO_RECEIVER_NOTE
SUPPLIER_PROD_NUM
SHP_CANCEL_REASON
SHP_CLOSED_REASON
SHP_SRC_SHIP_ID
DISTRIBUTION_ID
LINE_LOCATION_ID
PO_HEADER_ID
PO_LINE_ID
PO_RELEASE_ID
SOURCE_DIST_ID
REVISION_NUM
PO_NUMBER
CONTRACT_NUM
RELEASE_NUM
CCID
LAST_UPDATE_DATE
COLLECTION_STATUS
"_DF:PO_HDR:_EDW"
"_DF:PO_LINES:_EDW"
"_DF:PO_POD:_EDW"
"_DF:PO_PLL:_EDW"