VIEW: APPS.PMIFV_SALES_DETAILS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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