View: PMI_REP_SALES_DETAILS_V

Product:	PMI - Process Manufacturing Intelligence (Obsolete)
Description:	PMI Sales Details Report view
Implementation/DBA Data:	APPS.PMI_REP_SALES_DETAILS_V

View Text

SELECT ITEM_ID
, SUM(ORDER_QUANTITY) ORDER_QUANTITY
, WAREHOUSE_SHIPPED_FROM
, SUM(EXTENDED_PRICE_BASE_CURRENCY) EXTENDED_PRICE_BASE_CURRENCY
, SUM(CHARGES_BASE_CURRENCY) CHARGES_BASE_CURRENCY
, SALES_COMPANY
, SALES_ORGANIZATION
, PERIOD_START_DATE
, OPERATING_UNIT_ID
, YEAR_ID
, QUARTER_ID
, PERIOD_ID
FROM ( SELECT ORDERDETAIL.ITEM_ID ITEM_ID
, GMICUOM.I2UOM_CV(ORDERDETAIL.ITEM_ID
, 0
, ORDERDETAIL.ORDER_UM1
, ORDERDETAIL.ORDER_QTY1
, ITEMMASTER.ITEM_UM) ORDER_QUANTITY
, ORDERDETAIL.FROM_WHSE WAREHOUSE_SHIPPED_FROM
, 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)) EXTENDED_PRICE_BASE_CURRENCY
, 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) CHARGES_BASE_CURRENCY
, SALESORG.CO_CODE SALES_COMPANY
, ORDERHDR.ORGN_CODE SALES_ORGANIZATION
, GLCLDR.PERIOD_START_DATE PERIOD_START_DATE
, OU.ORGANIZATION_ID OPERATING_UNIT_ID
, GLCLDR.YEAR_ID
, GLCLDR.QUARTER_ID
, GLCLDR.PERIOD_ID
FROM PMI_GL_TIME_V GLCLDR
, GL_PLCY_MST GLPLCY
, OP_ORDR_HDR ORDERHDR
, OP_ORDR_DTL ORDERDETAIL
, IC_ITEM_MST_B ITEMMASTER
, IC_WHSE_MST SHIPPINGWAREHOUSE
, SY_ORGN_MST SALESORG
, 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 GLPLCY.CO_CODE = SALESORG.CO_CODE
AND GLCLDR.SET_OF_BOOKS_NAME=GLPLCY.SET_OF_BOOKS_NAME
AND TRUNC(ORDERDETAIL.ACTUAL_SHIPDATE) BETWEEN GLCLDR.PERIOD_START_DATE
AND GLCLDR.PERIOD_END_DATE
AND ORDERDETAIL.LINE_STATUS>=20
AND OU.ORGANIZATION_ID = GLPLCY.ORG_ID
AND ITEMMASTER.ITEM_ID = ORDERDETAIL.ITEM_ID
AND 'TRUE' = PMI_SECURITY_PKG.SHOW_RECORD(SALESORG.ORGN_CODE) UNION ALL SELECT ITEMMASTER.ITEM_ID ITEM_ID
, GMICUOM.I2UOM_CV(ITEMMASTER.ITEM_ID
, 0
, ORDERDETAIL.ORDER_QUANTITY_UOM
, ORDERDETAIL.ORDERED_QUANTITY
, ITEMMASTER.ITEM_UM) ORDER_QUANTITY
, SHIPPINGWAREHOUSE.WHSE_CODE WAREHOUSE_SHIPPED_FROM
, 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
, NULL SALES_COMPANY
, NULL SALES_ORGANIZATION
, GLCLDR.PERIOD_START_DATE PERIOD_START_DATE
, OU.ORGANIZATION_ID OPERATING_UNIT_ID
, GLCLDR.YEAR_ID
, GLCLDR.QUARTER_ID
, GLCLDR.PERIOD_ID
FROM OE_ORDER_HEADERS_ALL ORDERHDR
, OE_ORDER_LINES_ALL ORDERDETAIL
, OE_SYSTEM_PARAMETERS_ALL PARAMS
, MTL_SYSTEM_ITEMS_B MSI
, IC_ITEM_MST_B ITEMMASTER
, HR_OPERATING_UNITS OU
, GL_SETS_OF_BOOKS SOB
, IC_WHSE_MST SHIPPINGWAREHOUSE
, PMI_GL_TIME_V 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 TRUNC(ORDERDETAIL.ACTUAL_SHIPMENT_DATE) BETWEEN GLCLDR.PERIOD_START_DATE
AND GLCLDR.PERIOD_END_DATE
AND SOB.PERIOD_SET_NAME = GLCLDR.PERIOD_SET_NAME
AND SOB.NAME = GLCLDR.SET_OF_BOOKS_NAME
AND HR_SECURITY.SHOW_BIS_RECORD( ORDERHDR.ORG_ID ) = 'TRUE' ) GROUP BY ITEM_ID
, WAREHOUSE_SHIPPED_FROM
, SALES_COMPANY
, SALES_ORGANIZATION
, PERIOD_START_DATE
, OPERATING_UNIT_ID
, YEAR_ID
, QUARTER_ID
, PERIOD_ID WITH READ ONLY

Columns