VIEW: APPS.OE_BIS_BACKLOG_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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