VIEW: APPS.IBE_ORDER_ITEMS_ADV_V

Source

View Text - Preformatted

SELECT ol.line_id, ol.header_id, oh.order_number, ol.cust_po_number , oh.order_category_code, ol.line_category_code, ol.line_number, ol.SHIPMENT_NUMBER, ol.OPTION_NUMBER, ol.COMPONENT_NUMBER, ol.SERVICE_NUMBER, msi.inventory_item_id, msi.concatenated_segments item_number, msi.description item_description, ol.ordered_quantity, ol.shipped_quantity, ol.cancelled_quantity, ol.order_quantity_uom, msit.unit_of_measure_tl uom_name, msi.segment6 licensing_unit, ol.pricing_attribute10, ol.unit_selling_price, ol.unit_list_price, oh.transactional_curr_code, oh.ordered_date , oh.booked_date , ol.schedule_ship_date, ol.request_date, ol.promise_date, ol.item_type_code, ol.top_model_line_id, ol.link_to_line_id, ol.ato_line_id, ol.flow_status_code, oel.meaning line_status, ol.service_reference_type_code, ol.service_reference_line_id, ol.service_reference_system_id, ol.shippable_flag, ol.AGREEMENT_ID, oh.sold_to_org_id, hdr_inv_acct_site.cust_account_id, hdr_inv_site_use.cust_acct_site_id, inv_acct_site.cust_account_id, shp_acct_site.cust_account_id , ol.end_customer_id, oh.created_by, oh.org_id org_id FROM oe_order_lines_all ol, oe_order_headers_all oh, mtl_system_items_vl msi, mtl_units_of_measure_tl msit, hz_cust_site_uses_all hdr_inv_site_use, hz_cust_acct_sites_all hdr_inv_acct_site, hz_cust_site_uses_all inv_site_use, hz_cust_acct_sites_all inv_acct_site, hz_cust_site_uses_all shp_site_use, hz_cust_acct_sites_all shp_acct_site, oe_lookups oel WHERE ol.inventory_item_id = msi.inventory_item_Id AND msi.organization_id = oe_profile.value('OE_ORGANIZATION_ID', ol.org_id) AND ol.header_id = oh.header_id AND ol.order_quantity_uom = msit.uom_code AND msit.language = userenv('LANG') AND oh.invoice_to_org_id = hdr_inv_site_use.site_use_id AND hdr_inv_site_use.cust_acct_site_id = hdr_inv_acct_site.cust_acct_site_id AND ol.invoice_to_org_id = inv_site_use.site_use_id(+) AND inv_site_use.cust_acct_site_id = inv_acct_site.cust_acct_site_id(+) AND ol.ship_to_org_id = shp_site_use.site_use_id AND shp_site_use.cust_acct_site_id = shp_acct_site.cust_acct_site_id(+) AND oel.lookup_code = ol.flow_status_code AND oel.lookup_type = 'LINE_FLOW_STATUS'

View Text - HTML Formatted

SELECT OL.LINE_ID
, OL.HEADER_ID
, OH.ORDER_NUMBER
, OL.CUST_PO_NUMBER
, OH.ORDER_CATEGORY_CODE
, OL.LINE_CATEGORY_CODE
, OL.LINE_NUMBER
, OL.SHIPMENT_NUMBER
, OL.OPTION_NUMBER
, OL.COMPONENT_NUMBER
, OL.SERVICE_NUMBER
, MSI.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, OL.ORDERED_QUANTITY
, OL.SHIPPED_QUANTITY
, OL.CANCELLED_QUANTITY
, OL.ORDER_QUANTITY_UOM
, MSIT.UNIT_OF_MEASURE_TL UOM_NAME
, MSI.SEGMENT6 LICENSING_UNIT
, OL.PRICING_ATTRIBUTE10
, OL.UNIT_SELLING_PRICE
, OL.UNIT_LIST_PRICE
, OH.TRANSACTIONAL_CURR_CODE
, OH.ORDERED_DATE
, OH.BOOKED_DATE
, OL.SCHEDULE_SHIP_DATE
, OL.REQUEST_DATE
, OL.PROMISE_DATE
, OL.ITEM_TYPE_CODE
, OL.TOP_MODEL_LINE_ID
, OL.LINK_TO_LINE_ID
, OL.ATO_LINE_ID
, OL.FLOW_STATUS_CODE
, OEL.MEANING LINE_STATUS
, OL.SERVICE_REFERENCE_TYPE_CODE
, OL.SERVICE_REFERENCE_LINE_ID
, OL.SERVICE_REFERENCE_SYSTEM_ID
, OL.SHIPPABLE_FLAG
, OL.AGREEMENT_ID
, OH.SOLD_TO_ORG_ID
, HDR_INV_ACCT_SITE.CUST_ACCOUNT_ID
, HDR_INV_SITE_USE.CUST_ACCT_SITE_ID
, INV_ACCT_SITE.CUST_ACCOUNT_ID
, SHP_ACCT_SITE.CUST_ACCOUNT_ID
, OL.END_CUSTOMER_ID
, OH.CREATED_BY
, OH.ORG_ID ORG_ID
FROM OE_ORDER_LINES_ALL OL
, OE_ORDER_HEADERS_ALL OH
, MTL_SYSTEM_ITEMS_VL MSI
, MTL_UNITS_OF_MEASURE_TL MSIT
, HZ_CUST_SITE_USES_ALL HDR_INV_SITE_USE
, HZ_CUST_ACCT_SITES_ALL HDR_INV_ACCT_SITE
, HZ_CUST_SITE_USES_ALL INV_SITE_USE
, HZ_CUST_ACCT_SITES_ALL INV_ACCT_SITE
, HZ_CUST_SITE_USES_ALL SHP_SITE_USE
, HZ_CUST_ACCT_SITES_ALL SHP_ACCT_SITE
, OE_LOOKUPS OEL
WHERE OL.INVENTORY_ITEM_ID = MSI.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = OE_PROFILE.VALUE('OE_ORGANIZATION_ID'
, OL.ORG_ID)
AND OL.HEADER_ID = OH.HEADER_ID
AND OL.ORDER_QUANTITY_UOM = MSIT.UOM_CODE
AND MSIT.LANGUAGE = USERENV('LANG')
AND OH.INVOICE_TO_ORG_ID = HDR_INV_SITE_USE.SITE_USE_ID
AND HDR_INV_SITE_USE.CUST_ACCT_SITE_ID = HDR_INV_ACCT_SITE.CUST_ACCT_SITE_ID
AND OL.INVOICE_TO_ORG_ID = INV_SITE_USE.SITE_USE_ID(+)
AND INV_SITE_USE.CUST_ACCT_SITE_ID = INV_ACCT_SITE.CUST_ACCT_SITE_ID(+)
AND OL.SHIP_TO_ORG_ID = SHP_SITE_USE.SITE_USE_ID
AND SHP_SITE_USE.CUST_ACCT_SITE_ID = SHP_ACCT_SITE.CUST_ACCT_SITE_ID(+)
AND OEL.LOOKUP_CODE = OL.FLOW_STATUS_CODE
AND OEL.LOOKUP_TYPE = 'LINE_FLOW_STATUS'