View: OE_BIS_BACKLOG_V

Product:	OE - Order Entry
Description:
Implementation/DBA Data:	APPS.OE_BIS_BACKLOG_V

View Text

SELECT H.HEADER_ID HEADER_ID
, H.CUSTOMER_ID CUSTOMER_ID
, L.LINE_ID LINE_ID
, H.CURRENCY_CODE LINE_CURRENCY
, L.LIST_PRICE UNIT_LIST_PRICE
, L.SELLING_PRICE UNIT_SELLING_PRICE
, L.S1_DATE BOOKED_DATE
, DECODE(L.S5
, 18
, 'N'
, NULL
, 'N'
, 'Y') INVOICED_FLAG
, L.S5_DATE INVOICED_DATE
, (NVL(L_RATE.CONVERSION_RATE
, 1) / NVL(BASE_RATE.CONVERSION_RATE
, 1)) * NVL(COST.ITEM_COST
, 0) * (L.ORDERED_QUANTITY - NVL(L.INVOICED_QUANTITY
, 0)) BL_COST
, SOB.CURRENCY_CODE LINE_COST_CURRENCY
, L.INVENTORY_ITEM_ID INVENTORY_ITEM_ID
, MP.ORGANIZATION_ID WAREHOUSE_ID
, H.ORG_ID OU_ID
, L.ORDERED_QUANTITY - NVL(L.INVOICED_QUANTITY
, 0) BACKLOG_QUANTITY
, L.ORDERED_QUANTITY ORDERED_QUANTITY
, L.INVOICED_QUANTITY INVOICED_QUANTITY
, L.LIST_PRICE * (L.ORDERED_QUANTITY - NVL(L.INVOICED_QUANTITY
, 0)) BL_LIST_PRICE
, L.SELLING_PRICE * (L.ORDERED_QUANTITY - NVL(L.INVOICED_QUANTITY
, 0)) BL_SELLING_PRICE
, ((L.LIST_PRICE * (L.ORDERED_QUANTITY - NVL(L.INVOICED_QUANTITY
, 0))) - (L.SELLING_PRICE * L.INVOICED_QUANTITY)) BL_DISCOUNTS
, (L.SELLING_PRICE * (L.ORDERED_QUANTITY - NVL(L.INVOICED_QUANTITY
, 0))) - ((NVL(L_RATE.CONVERSION_RATE
, 1) / NVL(BASE_RATE.CONVERSION_RATE
, 1)) * NVL(COST.ITEM_COST
, 0) * (L.ORDERED_QUANTITY - NVL(L.INVOICED_QUANTITY
, 0))) BL_MARGIN
, NVL(MC.DESCRIPTION
, O.MEANING) PRODUCT_GROUP
, NVL(LK.MEANING
, O.MEANING) SALES_CHANNEL
FROM SO_HEADERS_ALL H
, SO_ORDER_TYPES_ALL OT
, SO_CYCLE_ACTIONS CA
, MTL_SYSTEM_ITEMS MTL
, MTL_PARAMETERS MP
, MTL_UOM_CONVERSIONS L_RATE
, MTL_UOM_CONVERSIONS BASE_RATE
, CST_ITEM_COSTS COST
, MTL_CATEGORIES MC
, MTL_DEFAULT_CATEGORY_SETS MDC
, MTL_ITEM_CATEGORIES MIC
, SO_LOOKUPS LK
, SO_LINES_ALL L
, SO_LOOKUPS O
, HR_ORGANIZATION_INFORMATION OI
, GL_SETS_OF_BOOKS SOB
WHERE L.HEADER_ID = H.HEADER_ID
AND L.LINE_TYPE_CODE IN ('REGULAR'
, 'DETAIL')
AND L.S1+0 = 1
AND H.ORDER_CATEGORY = 'R'
AND OT.ORDER_TYPE_ID = H.ORDER_TYPE_ID
AND CA.CYCLE_ID = OT.CYCLE_ID
AND CA.ACTION_ID = 7
AND MP.ORGANIZATION_ID = L.WAREHOUSE_ID
AND MTL.INVENTORY_ITEM_ID = L.INVENTORY_ITEM_ID
AND MTL.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
AND L_RATE.INVENTORY_ITEM_ID(+) = 0
AND L_RATE.UOM_CODE(+) = L.UNIT_CODE
AND TRUNC(SYSDATE) < NVL(L_RATE.DISABLE_DATE(+)
, SYSDATE+1)
AND BASE_RATE.INVENTORY_ITEM_ID(+) = 0
AND BASE_RATE.UOM_CODE(+) = MTL.PRIMARY_UOM_CODE
AND TRUNC(SYSDATE) < NVL(BASE_RATE.DISABLE_DATE(+)
, SYSDATE+1)
AND COST.INVENTORY_ITEM_ID(+) = L.INVENTORY_ITEM_ID
AND COST.ORGANIZATION_ID(+) = L.WAREHOUSE_ID
AND COST.COST_TYPE_ID(+) = 1
AND L.INVENTORY_ITEM_ID = MIC.INVENTORY_ITEM_ID
AND L.WAREHOUSE_ID = MIC.ORGANIZATION_ID
AND MDC.FUNCTIONAL_AREA_ID+0 = 7
AND MIC.CATEGORY_SET_ID = MDC.CATEGORY_SET_ID
AND MC.CATEGORY_ID = MIC.CATEGORY_ID
AND LK.LOOKUP_TYPE(+) = 'SALES_CHANNEL'
AND LK.LOOKUP_CODE(+) = H.SALES_CHANNEL_CODE
AND O.LOOKUP_TYPE = 'UNSPECIFIED'
AND O.LOOKUP_CODE = 'UNSPECIFIED'
AND OI.ORG_INFORMATION_CONTEXT = 'ACCOUNTING INFORMATION'
AND OI.ORGANIZATION_ID = MP.MASTER_ORGANIZATION_ID
AND TO_NUMBER(OI.ORG_INFORMATION1) = SOB.SET_OF_BOOKS_ID

Columns