VIEW: APPS.IBE_ORDER_DETAIL_V

Source

View Text - Preformatted

SELECT ol.line_id, ol.header_id, ol.line_number, ol.order_quantity_uom, ol.ordered_quantity, msi.concatenated_segments item_number, msi.description item_description, msi.bom_item_type bom_item_type, msi.attribute7 product_description, msi.segment6 licensing_unit, ol.pricing_attribute10, ol.unit_selling_price, ol.unit_list_price, ol.unit_list_price*ol.ordered_quantity total_list_price, oe_totals_grp.Get_Order_Total(ol.header_id,ol.line_id,'ALL') extended_price, oe_totals_grp.Get_Order_Total(ol.header_id,ol.line_id,'LINES') lines_total, decode(ol.ordered_quantity, 0 , oe_totals_grp.Get_Order_Total (ol.header_id,ol.line_id,'LINES'),oe_totals_grp.Get_Order_Total (ol.header_id,ol.line_id,'LINES') / ol.ordered_quantity ) unit_net_price, oe_totals_grp.Get_Order_Total(ol.header_id,ol.line_id,'TAXES') taxes_total, oe_totals_grp.Get_Order_Total(ol.header_id,ol.line_id,'CHARGES') charges_total, ol.schedule_ship_date, ol.shipped_quantity, ol.cancelled_quantity, ol.request_date, null, ol.item_type_code, ol.top_model_line_id, ol.link_to_line_id, ol.ato_line_id, ol.promise_date, ol.flow_status_code, oel.meaning line_status, msit.unit_of_measure_tl UOM_NAME, ol.SHIPMENT_NUMBER, ol.OPTION_NUMBER, ol.COMPONENT_NUMBER, ol.SERVICE_NUMBER, ol.shippable_flag, decode(msi.config_model_type,'N','N', decode(ol.top_model_line_id,ol.line_id,'Y', decode(NVL(msi.returnable_flag,'Y'),'N','N', decode(ol.item_type_code,'SERVICE','N', decode(ol.line_category_code,'RETURN','N', decode(oh.flow_status_code, 'ENTERED','N','CANCELLED','N','Y') ) ) ) ) ) line_returnable_flag, ol.line_category_code, linetyp.name LINE_TYPE, oh.transactional_curr_code, ol.SERVICE_REFERENCE_TYPE_CODE, ol.SERVICE_REFERENCE_LINE_ID, ol.SERVICE_REFERENCE_SYSTEM_ID, ol.charge_periodicity_code, msit1.UNIT_OF_MEASURE charge_periodicity_meaning, 'N', cfgdtl.name, msi.container_item_flag container_item_flag, decode(ol.top_model_line_id, ol.line_id, decode(link_to_line_id, NULL, decode(msi.config_model_type,'N','Y','N'), 'N'), 'N') model_container, cfgdtl.config_delta, cfgdtl.ext_activated_flag, oh.org_id org_id FROM oe_order_lines_all ol, oe_order_headers_all oh, mtl_system_items_vl msi, oe_lookups oel, mtl_units_of_measure_tl msit, oe_transaction_types_tl linetyp, cz_config_details_v cfgdtl, mtl_units_of_measure_tl msit1 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 oel.lookup_code = ol.flow_status_code AND oel.lookup_type = 'LINE_FLOW_STATUS' AND ol.order_quantity_uom = msit.uom_code AND msit.language = userenv('LANG') AND ol.line_type_id = linetyp.transaction_type_id AND linetyp.language = userenv('LANG') AND ol.config_header_id = cfgdtl.config_hdr_id(+) AND ol.config_rev_nbr = cfgdtl.config_rev_nbr (+) AND ol.configuration_id = cfgdtl.config_item_id(+) AND ol.charge_periodicity_code = msit1.uom_code(+) AND msit1.language(+) = userenv('LANG') AND msit1.uom_class(+) =fnd_profile.Value('ONT_UOM_CLASS_CHARGE_PERIODICITY')

View Text - HTML Formatted

SELECT OL.LINE_ID
, OL.HEADER_ID
, OL.LINE_NUMBER
, OL.ORDER_QUANTITY_UOM
, OL.ORDERED_QUANTITY
, MSI.CONCATENATED_SEGMENTS ITEM_NUMBER
, MSI.DESCRIPTION ITEM_DESCRIPTION
, MSI.BOM_ITEM_TYPE BOM_ITEM_TYPE
, MSI.ATTRIBUTE7 PRODUCT_DESCRIPTION
, MSI.SEGMENT6 LICENSING_UNIT
, OL.PRICING_ATTRIBUTE10
, OL.UNIT_SELLING_PRICE
, OL.UNIT_LIST_PRICE
, OL.UNIT_LIST_PRICE*OL.ORDERED_QUANTITY TOTAL_LIST_PRICE
, OE_TOTALS_GRP.GET_ORDER_TOTAL(OL.HEADER_ID
, OL.LINE_ID
, 'ALL') EXTENDED_PRICE
, OE_TOTALS_GRP.GET_ORDER_TOTAL(OL.HEADER_ID
, OL.LINE_ID
, 'LINES') LINES_TOTAL
, DECODE(OL.ORDERED_QUANTITY
, 0
, OE_TOTALS_GRP.GET_ORDER_TOTAL (OL.HEADER_ID
, OL.LINE_ID
, 'LINES')
, OE_TOTALS_GRP.GET_ORDER_TOTAL (OL.HEADER_ID
, OL.LINE_ID
, 'LINES') / OL.ORDERED_QUANTITY ) UNIT_NET_PRICE
, OE_TOTALS_GRP.GET_ORDER_TOTAL(OL.HEADER_ID
, OL.LINE_ID
, 'TAXES') TAXES_TOTAL
, OE_TOTALS_GRP.GET_ORDER_TOTAL(OL.HEADER_ID
, OL.LINE_ID
, 'CHARGES') CHARGES_TOTAL
, OL.SCHEDULE_SHIP_DATE
, OL.SHIPPED_QUANTITY
, OL.CANCELLED_QUANTITY
, OL.REQUEST_DATE
, NULL
, OL.ITEM_TYPE_CODE
, OL.TOP_MODEL_LINE_ID
, OL.LINK_TO_LINE_ID
, OL.ATO_LINE_ID
, OL.PROMISE_DATE
, OL.FLOW_STATUS_CODE
, OEL.MEANING LINE_STATUS
, MSIT.UNIT_OF_MEASURE_TL UOM_NAME
, OL.SHIPMENT_NUMBER
, OL.OPTION_NUMBER
, OL.COMPONENT_NUMBER
, OL.SERVICE_NUMBER
, OL.SHIPPABLE_FLAG
, DECODE(MSI.CONFIG_MODEL_TYPE
, 'N'
, 'N'
, DECODE(OL.TOP_MODEL_LINE_ID
, OL.LINE_ID
, 'Y'
, DECODE(NVL(MSI.RETURNABLE_FLAG
, 'Y')
, 'N'
, 'N'
, DECODE(OL.ITEM_TYPE_CODE
, 'SERVICE'
, 'N'
, DECODE(OL.LINE_CATEGORY_CODE
, 'RETURN'
, 'N'
, DECODE(OH.FLOW_STATUS_CODE
, 'ENTERED'
, 'N'
, 'CANCELLED'
, 'N'
, 'Y') ) ) ) ) ) LINE_RETURNABLE_FLAG
, OL.LINE_CATEGORY_CODE
, LINETYP.NAME LINE_TYPE
, OH.TRANSACTIONAL_CURR_CODE
, OL.SERVICE_REFERENCE_TYPE_CODE
, OL.SERVICE_REFERENCE_LINE_ID
, OL.SERVICE_REFERENCE_SYSTEM_ID
, OL.CHARGE_PERIODICITY_CODE
, MSIT1.UNIT_OF_MEASURE CHARGE_PERIODICITY_MEANING
, 'N'
, CFGDTL.NAME
, MSI.CONTAINER_ITEM_FLAG CONTAINER_ITEM_FLAG
, DECODE(OL.TOP_MODEL_LINE_ID
, OL.LINE_ID
, DECODE(LINK_TO_LINE_ID
, NULL
, DECODE(MSI.CONFIG_MODEL_TYPE
, 'N'
, 'Y'
, 'N')
, 'N')
, 'N') MODEL_CONTAINER
, CFGDTL.CONFIG_DELTA
, CFGDTL.EXT_ACTIVATED_FLAG
, OH.ORG_ID ORG_ID
FROM OE_ORDER_LINES_ALL OL
, OE_ORDER_HEADERS_ALL OH
, MTL_SYSTEM_ITEMS_VL MSI
, OE_LOOKUPS OEL
, MTL_UNITS_OF_MEASURE_TL MSIT
, OE_TRANSACTION_TYPES_TL LINETYP
, CZ_CONFIG_DETAILS_V CFGDTL
, MTL_UNITS_OF_MEASURE_TL MSIT1
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 OEL.LOOKUP_CODE = OL.FLOW_STATUS_CODE
AND OEL.LOOKUP_TYPE = 'LINE_FLOW_STATUS'
AND OL.ORDER_QUANTITY_UOM = MSIT.UOM_CODE
AND MSIT.LANGUAGE = USERENV('LANG')
AND OL.LINE_TYPE_ID = LINETYP.TRANSACTION_TYPE_ID
AND LINETYP.LANGUAGE = USERENV('LANG')
AND OL.CONFIG_HEADER_ID = CFGDTL.CONFIG_HDR_ID(+)
AND OL.CONFIG_REV_NBR = CFGDTL.CONFIG_REV_NBR (+)
AND OL.CONFIGURATION_ID = CFGDTL.CONFIG_ITEM_ID(+)
AND OL.CHARGE_PERIODICITY_CODE = MSIT1.UOM_CODE(+)
AND MSIT1.LANGUAGE(+) = USERENV('LANG')
AND MSIT1.UOM_CLASS(+) =FND_PROFILE.VALUE('ONT_UOM_CLASS_CHARGE_PERIODICITY')