View: PMIFV_SALES_DETAILS_V

Product:	PMI - Process Manufacturing Intelligence
Description:	Details of all shipped sales orders which are associated to corresponding GL periods.
Implementation/DBA Data:	APPS.PMIFV_SALES_DETAILS_V

View Text

SELECT 'OPSO' ORDER_SOURCE_SYSTEM
, ORDERDETAIL.PRICE_ID
, ORDERDETAIL.PRICE_SELECTED_IND
, ORDERDETAIL.ORDER_ID
, ORDERHDR.ORDER_NO
, ORDERHDR.ORDER_DATE
, ORDERHDR.ORDER_STATUS
, ORDERDETAIL.LINE_ID
, ORDERDETAIL.LINE_NO
, ORDERDETAIL.LINE_STATUS
, ORDERDETAIL.ITEM_ID
, ITEMMASTER.ITEM_NO
, ITEMMASTER.ITEM_DESC1
, ITEMMASTER.ITEM_UM
, ITEMMASTER.SALES_CLASS
, ORDERDETAIL.ORDER_QTY1
, GMICUOM.I2UOM_CV(ORDERDETAIL.ITEM_ID
, 0
, ORDERDETAIL.ORDER_UM1
, ORDERDETAIL.ORDER_QTY1
, ITEMMASTER.ITEM_UM)
, ORDERDETAIL.ORDER_UM1
, BILLINGCUSTOMER.CUST_CLASS
, ORDERHDR.BILLCUST_ID
, BILLINGCUSTOMER.CUST_NO
, BILLINGCUSTOMER.CUST_NAME
, BILLINGCUSTOMER.ADDR_ID
, BILLINGADDRESS.ADDR4
, BILLINGADDRESS.STATE_CODE
, BILLINGADDRESS.COUNTRY_CODE
, SHIPPINGCUSTOMER.CUST_CLASS
, ORDERDETAIL.SHIPCUST_ID
, SHIPPINGCUSTOMER.CUST_NO
, SHIPPINGCUSTOMER.CUST_NAME
, ORDERHDR.SHIPADDR_ID
, SHIPPINGADDRESS.ADDR4
, SHIPPINGADDRESS.STATE_CODE
, SHIPPINGADDRESS.COUNTRY_CODE
, ORDERDETAIL.SHIPPER_CODE
, ORDERDETAIL.SHIP_MTHD
, ORDERDETAIL.TO_WHSE
, ORDERDETAIL.FROM_WHSE
, SHIPORG.ORGN_CODE
, SHIPORG.ORGN_NAME
, SHIPORG.CO_CODE
, ORDERDETAIL.SHIP_STATUS
, ORDERDETAIL.BASE_PRICE
, ORDERDETAIL.NET_PRICE
, ORDERDETAIL.EXTENDED_PRICE
, DECODE(ORDERDETAIL.BASE_CURRENCY
, ORDERDETAIL.BILLING_CURRENCY
, ORDERDETAIL.EXTENDED_PRICE
, DECODE(ORDERDETAIL.MUL_DIV_SIGN
, 0
, ORDERDETAIL.EXTENDED_PRICE*ORDERDETAIL.EXCHANGE_RATE
, ORDERDETAIL.EXTENDED_PRICE/ORDERDETAIL.EXCHANGE_RATE))
, PMI_SALES_PKG.PMISA_GET_CHARGE(ORDERDETAIL.ORDER_ID
, ORDERDETAIL.LINE_ID
, ORDERDETAIL.EXTENDED_PRICE
, ORDERDETAIL.BILLING_CURRENCY
, ORDERDETAIL.BASE_CURRENCY
, ORDERDETAIL.EXCHANGE_RATE
, ORDERDETAIL.MUL_DIV_SIGN)
, ORDERDETAIL.PRICE_UM
, ORDERDETAIL.BILLING_CURRENCY
, ORDERDETAIL.BASE_CURRENCY
, ORDERDETAIL.EXCHANGE_RATE
, ORDERDETAIL.REQUESTED_SHIPDATE
, ORDERDETAIL.PROMISED_SHIPDATE
, ORDERDETAIL.LIST_PRICE
, ORDERDETAIL.SCHED_SHIPDATE
, ORDERDETAIL.ACTUAL_SHIPDATE
, ORDERDETAIL.REQUIRED_DLVDATE
, ORDERDETAIL.ACTUAL_DLVDATE
, ORDERDETAIL.QC_GRADE_WANTED
, ORDERDETAIL.SLSREP_CODE
, SALESREP.SLSREP_NAME
, ORDERDETAIL.COMMISSION_CODE
, ORDERDETAIL.NET_COMMISSION
, ORDERDETAIL.NET_TAX
, ORDERDETAIL.CUSTPO_NO
, SALESORG.CO_CODE
, ORDERHDR.ORGN_CODE
, SALESORG.ORGN_NAME
, GLCLDR.PERIOD_YEAR
, GLCLDR.PERIOD_NUM
, GLCLDR.PERIOD_NAME
, GLCLDR.START_DATE
, GLCLDR.END_DATE
, ORDERDETAIL.CREATION_DATE
, ORDERDETAIL.LAST_UPDATE_DATE
, ORDERDETAIL.CREATED_BY
, ORDERDETAIL.LAST_UPDATED_BY
, OU.ORGANIZATION_ID
, OU.NAME
FROM PMI_GL_CALENDAR_V GLCLDR
, GL_PLCY_MST GLPLCY
, OP_ORDR_HDR ORDERHDR
, OP_ORDR_DTL ORDERDETAIL
, OP_CUST_MST BILLINGCUSTOMER
, OP_CUST_MST SHIPPINGCUSTOMER
, OP_SLSR_MST SALESREP
, IC_ITEM_MST ITEMMASTER
, IC_WHSE_MST SHIPPINGWAREHOUSE
, SY_ORGN_MST SALESORG
, SY_ORGN_MST SHIPORG
, SY_ADDR_MST BILLINGADDRESS
, SY_ADDR_MST SHIPPINGADDRESS
, HR_OPERATING_UNITS OU
WHERE ORDERHDR.ORDER_ID = ORDERDETAIL.ORDER_ID
AND ORDERHDR.ORGN_CODE = SALESORG.ORGN_CODE
AND ORDERDETAIL.FROM_WHSE=SHIPPINGWAREHOUSE.WHSE_CODE
AND SHIPPINGWAREHOUSE.ORGN_CODE=SHIPORG.ORGN_CODE
AND ORDERDETAIL.ITEM_ID = ITEMMASTER.ITEM_ID
AND ORDERDETAIL.SHIPCUST_ID=SHIPPINGCUSTOMER.CUST_ID
AND ORDERHDR.BILLCUST_ID=BILLINGCUSTOMER.CUST_ID
AND ORDERHDR.SHIPADDR_ID=SHIPPINGADDRESS.ADDR_ID(+)
AND BILLINGCUSTOMER.ADDR_ID=BILLINGADDRESS.ADDR_ID(+)
AND ORDERDETAIL.SLSREP_CODE=SALESREP.SLSREP_CODE(+)
AND GLPLCY.CO_CODE = SALESORG.CO_CODE
AND OU.ORGANIZATION_ID = GLPLCY.ORG_ID
AND GLCLDR.CO_CODE = SALESORG.CO_CODE
AND TRUNC(ORDERDETAIL.ACTUAL_SHIPDATE) BETWEEN GLCLDR.START_DATE
AND GLCLDR.END_DATE
AND ORDERDETAIL.LINE_STATUS>=20
AND (SALESORG.ORGN_CODE IS NULL OR SALESORG.ORGN_CODE IN (SELECT SECORG.ORGN_CODE
FROM SY_ORGN_MST SECORG
WHERE PMI_SECURITY_PKG.SHOW_RECORD(SECORG.ORGN_CODE) = 'TRUE')) UNION ALL SELECT 'OMSO' ORDER_SOURCE_SYSTEM
, TO_NUMBER(NULL) PRICE_ID
, TO_NUMBER(NULL) PRICE_SELECTED_IND
, ORDERDETAIL.HEADER_ID ORDER_ID
, TO_CHAR(ORDERHDR.ORDER_NUMBER) ORDER_NO
, ORDERHDR.ORDERED_DATE ORDER_DATE
, DECODE(ORDERHDR.CANCELLED_FLAG
, 'Y'
, -1
, DECODE(ORDERHDR.FLOW_STATUS_CODE
, 'ENTERED'
, 0
, 'CANCELLED'
, -1
, 'CLOSED'
, 25
, 'BOOKED'
, 0) ) ORDER_STATUS
, ORDERDETAIL.LINE_ID LINE_ID
, ORDERDETAIL.LINE_NUMBER LINE_NO
, DECODE(ORDERDETAIL.CANCELLED_FLAG
, 'Y'
, -1
, DECODE(ORDERDETAIL.FLOW_STATUS_CODE
, 'AWAITING_FULFILLMENT'
, 0
, 'ENTERED'
, 0
, 'AWAITING_SHIPPING'
, 10
, 'PICKED'
, 10
, 'SCHEDULED'
, 0
, 'SHIPPED'
, 20
, 'CANCELLED'
, -1
, 'PICKED_PARTIAL'
, 0
, 'AWAITING_RETURN'
, 27
, 'BOOKED'
, 0
, 'RETURNED'
, 29
, 'CLOSED'
, 25
, 'INVOICED_PARTIAL'
, 22
, 'INVOICED'
, 23
, 'FULFILLED'
, 20
, 'PRODUCTION_COMPLETE'
, 0
, 'PRODUCTION_PARTIAL'
, 0
, 'PRODUCTION_OPEN'
, 0
, 'PRODUCTION_ELIGABLE'
, 0
, 'BOM_AND_RTG_CREATED'
, 0
, 'ITEM_CREATED'
, 0
, 'AWAITING_RECEIPT'
, 28 ) ) LINE_STATUS
, ITEMMASTER.ITEM_ID ITEM_ID
, ITEMMASTER.ITEM_NO ITEM_NO
, ITEMMASTER.ITEM_DESC1 ITEM_DESCRIPTION
, ITEMMASTER.ITEM_UM ITEM_UOM
, ITEMMASTER.SALES_CLASS SALES_CLASS
, ORDERDETAIL.ORDERED_QUANTITY ORDER_QUANTITY
, GMICUOM.I2UOM_CV(ITEMMASTER.ITEM_ID
, 0
, ORDERDETAIL.ORDER_QUANTITY_UOM
, ORDERDETAIL.ORDERED_QUANTITY
, ITEMMASTER.ITEM_UM)
, ORDERDETAIL.ORDER_QUANTITY_UOM ORDER_UOM
, NULL BILLING_CUSTOMER_CLASS
, BILLINGCUSTOMER.PARTY_ID BILLING_CUSTOMER_ID
, BILLINGCUSTOMER.PARTY_NUMBER BILLING_CUSTOMER_NO
, BILLINGCUSTOMER.PARTY_NAME BILLING_CUSTOMER_NAME
, BILLINGCUSTOMER.LOCATION_ID BILLING_CUSTOMER_ADDID
, BILLINGCUSTOMER.PARTY_CITY BILLING_CUSTOMER_CITY
, BILLINGCUSTOMER.PARTY_STATE BILLING_CUSTOMER_STATE
, BILLINGCUSTOMER.PARTY_COUNTRY BILLING_CUSTOMER_COUNTRY
, NULL SHIPPING_CUSTOMER_CLASS
, SHIPPINGCUSTOMER.PARTY_ID SHIPPING_CUSTOMER_ID
, SHIPPINGCUSTOMER.PARTY_NUMBER SHIPPING_CUSTOMER_NO
, SHIPPINGCUSTOMER.PARTY_NAME SHIPPING_CUSTOMER_NAME
, SHIPPINGCUSTOMER.LOCATION_ID SHIPPING_CUSTOMER_ADDID
, SHIPPINGCUSTOMER.PARTY_CITY SHIPPING_CUSTOMER_CITY
, SHIPPINGCUSTOMER.PARTY_STATE SHIPPING_CUSTOMER_STATE
, SHIPPINGCUSTOMER.PARTY_COUNTRY SHIPPING_CUSTOMER_COUNTRY
, ORDERDETAIL.FREIGHT_CARRIER_CODE SHIPPER_CODE
, ORDERDETAIL.SHIPPING_METHOD_CODE SHIPPING_METHOD
, TO_CHAR(ORDERDETAIL.SHIP_TO_ORG_ID) WAREHOUSE_SHIPPED_TO
, SHIPPINGWAREHOUSE.WHSE_CODE WAREHOUSE_SHIPPED_FROM
, SHIPORG.ORGN_CODE SHIPPING_ORGANIZATION
, SHIPORG.ORGN_NAME SHIPPING_ORGANIZATION_NAME
, SHIPORG.CO_CODE SHIPPING_COMPANY
, DECODE(ORDERDETAIL.FLOW_STATUS_CODE
, 'PICKED'
, 10
, 'AWAITING_SHIPPING'
, 10
, 'SHIPPED'
, 20
, 0) SHIPPING_STATUS
, ORDERDETAIL.UNIT_LIST_PRICE BASE_PRICE
, ORDERDETAIL.UNIT_SELLING_PRICE NET_PRICE
, ORDERDETAIL.UNIT_LIST_PRICE * ORDERDETAIL.ORDERED_QUANTITY EXTENDED_PRICE
, DECODE(SOB.CURRENCY_CODE
, ORDERHDR.TRANSACTIONAL_CURR_CODE
, ORDERDETAIL.UNIT_LIST_PRICE * ORDERED_QUANTITY
, ORDERDETAIL.UNIT_LIST_PRICE * NVL(ORDERHDR.CONVERSION_RATE
, PMI_COMMON_PKG.PMICO_GET_MULCURR_AMT(ORDERHDR.TRANSACTIONAL_CURR_CODE
, SOB.CURRENCY_CODE
, ORDERDETAIL.ACTUAL_SHIPMENT_DATE
, 1)) * ORDERED_QUANTITY) EXTENDED_PRICE_BASE_CURRENCY
, (ORDERDETAIL.UNIT_LIST_PRICE - ORDERDETAIL.UNIT_SELLING_PRICE) * ORDERDETAIL.ORDERED_QUANTITY CHARGES_BASE_CURRENCY
, ORDERDETAIL.PRICING_QUANTITY_UOM PRICING_UOM
, ORDERHDR.TRANSACTIONAL_CURR_CODE BILLING_CURRENCY
, SOB.CURRENCY_CODE BASE_CURRENCY
, NVL(ORDERHDR.CONVERSION_RATE
, PMI_COMMON_PKG.PMICO_GET_MULCURR_AMT(ORDERHDR.TRANSACTIONAL_CURR_CODE
, SOB.CURRENCY_CODE
, ORDERDETAIL.ACTUAL_SHIPMENT_DATE
, 1)) EXCHANGE_RATE
, ORDERDETAIL.REQUEST_DATE REQUESTED_SHIPDATE
, ORDERDETAIL.PROMISE_DATE PROMISED_SHIPDATE
, ORDERDETAIL.UNIT_LIST_PRICE LIST_PRICE
, ORDERDETAIL.SCHEDULE_SHIP_DATE SCHEDULED_SHIPDATE
, ORDERDETAIL.ACTUAL_SHIPMENT_DATE ACTUAL_SHIPDATE
, ORDERDETAIL.LATEST_ACCEPTABLE_DATE REQUIRED_DELIVERY_DATE
, ORDERDETAIL.ACTUAL_ARRIVAL_DATE ACTUAL_DELIVERY_DATE
, ORDERDETAIL.PREFERRED_GRADE QC_GRADE_WANTED
, TO_CHAR(ORDERDETAIL.SALESREP_ID) SALES_REPRESENTATIVE
, SALESREP.NAME SALES_REP_NAME
, TO_CHAR(NULL) COMMISSION_CODE
, TO_NUMBER(NULL) NET_COMMISSION
, ORDERDETAIL.TAX_VALUE NET_TAX
, ORDERDETAIL.CUST_PO_NUMBER CUSTOMER_PO_NO
, NULL SALES_COMPANY
, NULL SALES_ORGANIZATION
, OU.NAME SALES_ORGANIZATION_NAME
, GLCLDR.PERIOD_YEAR
, GLCLDR.PERIOD_NUM
, GLCLDR.PERIOD_NAME
, GLCLDR.START_DATE
, GLCLDR.END_DATE
, ORDERDETAIL.CREATION_DATE CREATION_DATE
, ORDERDETAIL.LAST_UPDATE_DATE LAST_UPDATE_DATE
, ORDERDETAIL.CREATED_BY CREATED_BY
, ORDERDETAIL.LAST_UPDATED_BY LAST_UPDATED_BY
, OU.ORGANIZATION_ID OPERATING_UNIT_ID
, OU.NAME OPERATING_UNIT_NAME
FROM OE_ORDER_HEADERS_ALL ORDERHDR
, OE_ORDER_LINES_ALL ORDERDETAIL
, OE_SYSTEM_PARAMETERS_ALL PARAMS
, MTL_SYSTEM_ITEMS MSI
, IC_ITEM_MST ITEMMASTER
, HR_OPERATING_UNITS OU
, GL_SETS_OF_BOOKS SOB
, IC_WHSE_MST SHIPPINGWAREHOUSE
, SY_ORGN_MST SHIPORG
, PMI_HZ_PARTY_V BILLINGCUSTOMER
, PMI_HZ_PARTY_V SHIPPINGCUSTOMER
, RA_SALESREPS_ALL SALESREP
, GL_PERIODS GLCLDR
WHERE ORDERDETAIL.HEADER_ID = ORDERHDR.HEADER_ID
AND PARAMS.ORG_ID = ORDERHDR.ORG_ID
AND MSI.ORGANIZATION_ID = PARAMS.MASTER_ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = ORDERDETAIL.INVENTORY_ITEM_ID
AND ITEMMASTER.ITEM_NO(+) = MSI.SEGMENT1
AND OU.ORGANIZATION_ID = ORDERHDR.ORG_ID
AND OU.ORGANIZATION_ID = ORDERDETAIL.ORG_ID
AND SOB.SET_OF_BOOKS_ID = OU.SET_OF_BOOKS_ID
AND SHIPPINGWAREHOUSE.MTL_ORGANIZATION_ID = ORDERDETAIL.SHIP_FROM_ORG_ID
AND SHIPORG.ORGN_CODE = SHIPPINGWAREHOUSE.ORGN_CODE
AND BILLINGCUSTOMER.SITE_USE_ID(+) = ORDERHDR.INVOICE_TO_ORG_ID
AND SHIPPINGCUSTOMER.SITE_USE_ID(+) = ORDERDETAIL.SHIP_TO_ORG_ID
AND SALESREP.SALESREP_ID(+) = ORDERDETAIL.SALESREP_ID
AND SALESREP.ORG_ID(+) = ORDERDETAIL.ORG_ID
AND TRUNC(ORDERDETAIL.ACTUAL_SHIPMENT_DATE) BETWEEN GLCLDR.START_DATE
AND GLCLDR.END_DATE
AND SOB.PERIOD_SET_NAME = GLCLDR.PERIOD_SET_NAME
AND SOB.ACCOUNTED_PERIOD_TYPE = GLCLDR.PERIOD_TYPE
AND GLCLDR.ADJUSTMENT_PERIOD_FLAG <>'Y'
AND '_SEC:ORDERHDR.ORG_ID' IS NOT NULL WITH READ ONLY

Columns