CMF_MATERIAL_PO_ORDERS_V

Details

• Schema: FUSION

• Object owner: CMF

• Object type: VIEW

Columns

Name

PO_HEADER_ID PO_NUMBER PROCUREMENT_BUSINESS_UNIT_ID DELIVER_TO_BUSINESS_UNIT_ID SOLD_TO_LEGAL_ENTITY_ID CURRENCY_CODE VENDOR_ID VENDOR_SITE_ID AGENT_ID PO_LINE_ID LINE_NUMBER INVENTORY_ITEM_ID DEFAULT_INVENTORY_ORG_ID PURCHASE_BASIS PO_LINE_LOCATION_ID SHIPMENT_NUMBER QTY_IN_PRIMARY_UOM QTY_IN_SECONDARY_UOM UOM_CODE SECONDARY_UOM_CODE PRICE PRICE_IN_FUNC_CURRENCY DELIVER_TO_INVENTORY_ORG_ID SHIP_TO_LOCATION_ID NEED_BY_DATE PROMISED_DATE DESTINATION_TYPE_CODE ACCRUE_ON_RECEIPT_FLAG EVENT_DATE

Query

SQL_Statement

SELECT CPOD.PO_HEADER_ID, CPOD.PO_NUMBER, CPOD.PROCUREMENT_BUSINESS_UNIT_ID, CPOD.DELIVER_TO_BUSINESS_UNIT_ID, CPOD.SOLD_TO_LEGAL_ENTITY_ID, CPOD.CURRENCY_CODE, CPOD.VENDOR_ID, CPOD.VENDOR_SITE_ID, CPOD.AGENT_ID, CPOD.PO_LINE_ID, CPOD.LINE_NUMBER, CPOD.INVENTORY_ITEM_ID, CPOD.DEFAULT_INVENTORY_ORG_ID, CPOD.PURCHASE_BASIS, CPOD.PO_LINE_LOCATION_ID, CPOD.SHIPMENT_NUMBER, SUM(NVL(CPOD.QUANTITY_ORDERED,0)- NVL(CPOD.QUANTITY_CANCELLED,0)) QTY_IN_PRIMARY_UOM, SUM(NVL(CPOD.SECONDARY_QUANTITY,0)- NVL(CPOD.SECONDARY_QTY_CANCELLED,0)) QTY_IN_SECONDARY_UOM, CPOD.UOM_CODE, CPOD.SECONDARY_UOM_CODE, CPOD.PRICE, CPOD.PRICE * NVL(CPOD.CURRENCY_CONVERSION_RATE,1) PRICE_IN_FUNC_CURRENCY, CPOD.DELIVER_TO_INVENTORY_ORG_ID, CPOD.SHIP_TO_LOCATION_ID, CPOD.NEED_BY_DATE, CPOD.PROMISED_DATE, CPOD.DESTINATION_TYPE_CODE, CPOD.ACCRUE_ON_RECEIPT_FLAG, ( SELECT MAX(EVENT_DATE) FROM CMR_PURCHASE_ORDER_DTLS CPOD1 WHERE CPOD1.PO_HEADER_ID = CPOD.PO_HEADER_ID AND CPOD1.PO_LINE_ID=CPOD.PO_LINE_ID AND CPOD1.PO_LINE_LOCATION_ID = CPOD.PO_LINE_LOCATION_ID) EVENT_DATE FROM CMR_PURCHASE_ORDER_DTLS CPOD WHERE CPOD.ACCRUE_ON_RECEIPT_FLAG = 'Y' AND CPOD.PURCHASE_BASIS = 'GOODS' AND CPOD.ACTIVE_FLAG = 'Y' GROUP BY CPOD.PO_HEADER_ID, CPOD.PO_NUMBER, CPOD.PROCUREMENT_BUSINESS_UNIT_ID, CPOD.DELIVER_TO_BUSINESS_UNIT_ID, CPOD.SOLD_TO_LEGAL_ENTITY_ID, CPOD.CURRENCY_CODE, CPOD.VENDOR_ID, CPOD.VENDOR_SITE_ID, CPOD.AGENT_ID, CPOD.PO_LINE_ID, CPOD.LINE_NUMBER, CPOD.INVENTORY_ITEM_ID, CPOD.DEFAULT_INVENTORY_ORG_ID, CPOD.PURCHASE_BASIS, CPOD.PO_LINE_LOCATION_ID, CPOD.SHIPMENT_NUMBER, CPOD.UOM_CODE, CPOD.SECONDARY_UOM_CODE, CPOD.PRICE, CPOD.CURRENCY_CONVERSION_RATE, CPOD.DELIVER_TO_INVENTORY_ORG_ID, CPOD.SHIP_TO_LOCATION_ID, CPOD.NEED_BY_DATE, CPOD.PROMISED_DATE, CPOD.DESTINATION_TYPE_CODE, CPOD.ACCRUE_ON_RECEIPT_FLAG