VIEW: APPS.OE_BIS_BILLINGS_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, (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)) line_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.invoiced_quantity invoiced_quantity, l.s5_date invoicing_date, l.list_price * l.invoiced_quantity invoiced_list_price, l.selling_price * l.invoiced_quantity invoiced_selling_price, ((l.list_price * l.invoiced_quantity) - (l.selling_price * l.invoiced_quantity)) invoiced_discounts, (l.selling_price * l.invoiced_quantity) - ((NVL(l_rate.conversion_rate,1) / NVL(base_rate.conversion_rate,1)) * NVL(cost.item_cost,0) * l.invoiced_quantity) invoiced_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 /* receivables interface cycle */ 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
, (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)) LINE_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.INVOICED_QUANTITY INVOICED_QUANTITY
, L.S5_DATE INVOICING_DATE
, L.LIST_PRICE * L.INVOICED_QUANTITY INVOICED_LIST_PRICE
, L.SELLING_PRICE * L.INVOICED_QUANTITY INVOICED_SELLING_PRICE
, ((L.LIST_PRICE * L.INVOICED_QUANTITY) - (L.SELLING_PRICE * L.INVOICED_QUANTITY)) INVOICED_DISCOUNTS
, (L.SELLING_PRICE * L.INVOICED_QUANTITY) - ((NVL(L_RATE.CONVERSION_RATE
, 1) / NVL(BASE_RATE.CONVERSION_RATE
, 1)) * NVL(COST.ITEM_COST
, 0) * L.INVOICED_QUANTITY) INVOICED_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 /* RECEIVABLES INTERFACE CYCLE */
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