VIEW: APPS.AMS_DS_ORDERS_V

Source

View Text - Preformatted

SELECT oh.org_id, oh.SOLD_TO_ORG_ID , cust.party_id, oh.ordered_date, oh.transactional_curr_code, oh.SOLD_FROM_ORG_ID, oh.sales_channel_code, oh.BOOKED_DATE, oh.MARKETING_SOURCE_CODE_ID, OL.LINE_ID, OL.header_id, OL.LINE_CATEGORY_CODE, OL.LINE_NUMBER, OL.ORDER_QUANTITY_UOM, OL.ORDERED_QUANTITY, MSI.SEGMENT1 ITEM_NUMBER, MSI.DESCRIPTION ITEM_DESCRIPTION, OL.UNIT_SELLING_PRICE unit_selling_price, OL.UNIT_LIST_PRICE unit_list_price, NVL(OL.UNIT_SELLING_PRICE,0)*( NVL(OL.ORDERED_QUANTITY,0) - NVL(OL.CANCELLED_QUANTITY,0) ) EXTENDED_PRICE , OL.SCHEDULE_SHIP_DATE, OL.SCHEDULE_ARRIVAL_DATE, OL.ACTUAL_SHIPMENT_DATE, OL.ACTUAL_ARRIVAL_DATE, OL.SHIPPED_QUANTITY, OL.SHIP_TO_CONTACT_ID, OL.CANCELLED_FLAG, OL.OPEN_FLAG, OL.BOOKED_FLAG, OL.OPTION_NUMBER, OL.SHIPPING_QUANTITY_UOM, OL.CANCELLED_QUANTITY, OL.PROMISE_DATE, OL.REQUEST_DATE, OL.SHIPPING_METHOD_CODE, OL.SHIPMENT_PRIORITY_CODE, OL.LINE_TYPE_ID, OL.PRICING_QUANTITY, OL.PRICING_QUANTITY_UOM, OL.FULFILLED_QUANTITY, OL.SHIPPING_QUANTITY, OL.ORDER_SOURCE_ID, OL.INVOICE_TO_ORG_ID, OL.SHIP_TO_ORG_ID, OL.ATO_LINE_ID, OL.CREATION_DATE, OL.LAST_UPDATE_DATE, '  ' , MSI.CONCATENATED_SEGMENTS , msi.inventory_item_id, oh.attribute1, oh.attribute2, oh.attribute3, oh.attribute4, oh.attribute5, oh.attribute6, oh.attribute7, oh.attribute8, oh.attribute9, oh.attribute10, oh.attribute11, oh.attribute12, oh.attribute13, oh.attribute14, oh.attribute15, oh.attribute16, oh.attribute17, oh.attribute18, oh.attribute19, oh.attribute20, mic.category_id from OE_ORDER_LINES_ALL OL, OE_ORDER_HEADERS_ALL OH, MTL_SYSTEM_ITEMS_B_KFV MSI , hz_cust_accounts cust, MTL_ITEM_CATEGORIES MIC, MTL_DEFAULT_CATEGORY_SETS S WHERE OL.HEADER_ID = OH.HEADER_ID AND ol.inventory_item_id = MSI.inventory_item_Id and msi.organization_id = ol.ship_from_org_id and cust.CUST_ACCOUNT_ID = oh.SOLD_TO_ORG_ID and mic.inventory_item_id = msi.inventory_item_id and mic.organization_id = msi.organization_id and s.functional_area_id = 11 and s.category_set_id = mic.category_set_id

View Text - HTML Formatted

SELECT OH.ORG_ID
, OH.SOLD_TO_ORG_ID
, CUST.PARTY_ID
, OH.ORDERED_DATE
, OH.TRANSACTIONAL_CURR_CODE
, OH.SOLD_FROM_ORG_ID
, OH.SALES_CHANNEL_CODE
, OH.BOOKED_DATE
, OH.MARKETING_SOURCE_CODE_ID
, OL.LINE_ID
, OL.HEADER_ID
, OL.LINE_CATEGORY_CODE
, OL.LINE_NUMBER
, OL.ORDER_QUANTITY_UOM
, OL.ORDERED_QUANTITY
, MSI.SEGMENT1 ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, OL.UNIT_SELLING_PRICE UNIT_SELLING_PRICE
, OL.UNIT_LIST_PRICE UNIT_LIST_PRICE
, NVL(OL.UNIT_SELLING_PRICE
, 0)*( NVL(OL.ORDERED_QUANTITY
, 0) - NVL(OL.CANCELLED_QUANTITY
, 0) ) EXTENDED_PRICE
, OL.SCHEDULE_SHIP_DATE
, OL.SCHEDULE_ARRIVAL_DATE
, OL.ACTUAL_SHIPMENT_DATE
, OL.ACTUAL_ARRIVAL_DATE
, OL.SHIPPED_QUANTITY
, OL.SHIP_TO_CONTACT_ID
, OL.CANCELLED_FLAG
, OL.OPEN_FLAG
, OL.BOOKED_FLAG
, OL.OPTION_NUMBER
, OL.SHIPPING_QUANTITY_UOM
, OL.CANCELLED_QUANTITY
, OL.PROMISE_DATE
, OL.REQUEST_DATE
, OL.SHIPPING_METHOD_CODE
, OL.SHIPMENT_PRIORITY_CODE
, OL.LINE_TYPE_ID
, OL.PRICING_QUANTITY
, OL.PRICING_QUANTITY_UOM
, OL.FULFILLED_QUANTITY
, OL.SHIPPING_QUANTITY
, OL.ORDER_SOURCE_ID
, OL.INVOICE_TO_ORG_ID
, OL.SHIP_TO_ORG_ID
, OL.ATO_LINE_ID
, OL.CREATION_DATE
, OL.LAST_UPDATE_DATE
, ' '
, MSI.CONCATENATED_SEGMENTS
, MSI.INVENTORY_ITEM_ID
, OH.ATTRIBUTE1
, OH.ATTRIBUTE2
, OH.ATTRIBUTE3
, OH.ATTRIBUTE4
, OH.ATTRIBUTE5
, OH.ATTRIBUTE6
, OH.ATTRIBUTE7
, OH.ATTRIBUTE8
, OH.ATTRIBUTE9
, OH.ATTRIBUTE10
, OH.ATTRIBUTE11
, OH.ATTRIBUTE12
, OH.ATTRIBUTE13
, OH.ATTRIBUTE14
, OH.ATTRIBUTE15
, OH.ATTRIBUTE16
, OH.ATTRIBUTE17
, OH.ATTRIBUTE18
, OH.ATTRIBUTE19
, OH.ATTRIBUTE20
, MIC.CATEGORY_ID
FROM OE_ORDER_LINES_ALL OL
, OE_ORDER_HEADERS_ALL OH
, MTL_SYSTEM_ITEMS_B_KFV MSI
, HZ_CUST_ACCOUNTS CUST
, MTL_ITEM_CATEGORIES MIC
, MTL_DEFAULT_CATEGORY_SETS S
WHERE OL.HEADER_ID = OH.HEADER_ID
AND OL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = OL.SHIP_FROM_ORG_ID
AND CUST.CUST_ACCOUNT_ID = OH.SOLD_TO_ORG_ID
AND MIC.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MIC.ORGANIZATION_ID = MSI.ORGANIZATION_ID
AND S.FUNCTIONAL_AREA_ID = 11
AND S.CATEGORY_SET_ID = MIC.CATEGORY_SET_ID