VIEW: APPS.PMI_REP_SALES_DETAILS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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