Product: | IBE - iStore |
---|---|
Description: | Stores information about details of an order line |
Implementation/DBA Data: | APPS.IBE_ORDER_DETAIL_V |
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.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(OL.TOP_MODEL_LINE_ID
, OL.LINE_ID
, 'Y'
, DECODE(OL.SHIPPABLE_FLAG
, 'N'
, 'N'
, 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
, 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')