VIEW: APPS.INL_DET_LANDED_COSTS_V

Source

View Text - Preformatted

SELECT x.ship_header_id, x.adjustment_num, x.ship_line_group_id, x.ship_line_id, x.parent_ship_line_id, x.ship_line_num, x.organization_id, x.inventory_item_id, x.inv_item, x.primary_qty, x.unit_of_measure, x.component_type, x.component_code, x.component_name, x.charge_line_type_id, SUM(x.allocated_amt) allocated_amt, RATIO_TO_REPORT(sum(x.allocated_amt)) OVER(PARTITION BY x.ship_header_id, x.ship_line_id, x.adjustment_num) * 100 allocation_percent, SUM(x.estimated_allocated_amt) estimated_allocated_amt, RATIO_TO_REPORT(sum(x.estimated_allocated_amt)) OVER(PARTITION BY x.ship_header_id, x.ship_line_id, x.adjustment_num) * 100 estimated_allocation_percent FROM (SELECT alloc.ship_header_id, alloc.adjustment_num, sl.ship_line_group_id, sl.ship_line_id, sl.parent_ship_line_id, sl.ship_line_num, sh.organization_id, sl.inventory_item_id, msi.concatenated_segments inv_item, sl.primary_qty, uom.unit_of_measure, DECODE(assoc.from_parent_table_name,'INL_CHARGE_LINES', 'CHARGE', 'INL_TAX_LINES', 'TAX', 'ITEM PRICE') component_type, DECODE(assoc.from_parent_table_name,'INL_SHIP_HEADERS', 'OTHERS', 'INL_SHIP_LINES', 'OTHERS', 'INL_CHARGE_LINES', pe.price_element_code, 'INL_TAX_LINES', tl.tax_code, msi.concatenated_segments) component_code, DECODE(assoc.from_parent_table_name,'INL_SHIP_HEADERS', 'OTHERS', 'INL_SHIP_LINES', 'OTHERS', 'INL_CHARGE_LINES', pe.name, 'INL_TAX_LINES', tl.tax_code, msi.concatenated_segments) component_name, cl.charge_line_type_id, alloc.allocation_amt allocated_amt, DECODE(sl2.adjustment_num,NULL,DECODE(cl.adjustment_num,NULL,DECODE(tl.adjustment_num,NULL,NULL,DECODE(SIGN(tl.adjustment_num), 1,FIRST_VALUE(DECODE(ABS(tl.adjustment_num), tl.adjustment_num, DECODE(tl.adjustment_num, 0,alloc.allocation_amt,null), alloc.allocation_amt)) OVER (PARTITION BY alloc.ship_header_id, NVL(sl.parent_ship_line_id,sl.ship_line_id), tl.tax_line_num ORDER BY tl.adjustment_num, alloc.allocation_id desc),alloc.allocation_amt)), DECODE(SIGN(cl.adjustment_num), 1,FIRST_VALUE(DECODE(ABS(cl.adjustment_num), cl.adjustment_num, DECODE(cl.adjustment_num, 0,alloc.allocation_amt,null), alloc.allocation_amt)) OVER (PARTITION BY alloc.ship_header_id, NVL(sl.parent_ship_line_id,sl.ship_line_id), cl.charge_line_num ORDER BY cl.adjustment_num, alloc.allocation_id desc),alloc.allocation_amt)), DECODE(SIGN(sl2.adjustment_num),1,FIRST_VALUE(DECODE(ABS(sl2.adjustment_num), sl2.adjustment_num, DECODE(sl2.adjustment_num,0,alloc.allocation_amt,null), alloc.allocation_amt)) OVER (PARTITION BY alloc.ship_header_id, NVL(sl.parent_ship_line_id,sl.ship_line_id), sl2.ship_line_num ORDER BY sl2.adjustment_num, alloc.allocation_id desc),alloc.allocation_amt)) estimated_allocated_amt FROM mtl_units_of_measure uom, pon_price_element_types_vl pe, mtl_system_items_kfv msi, inl_charge_lines cl, inl_tax_lines tl, inl_associations assoc, inl_ship_headers_all sh, inl_ship_lines_all sl2, inl_ship_lines_all sl, inl_allocations alloc WHERE uom.uom_code = sl.primary_uom_code AND pe.price_element_type_id(+) = cl.charge_line_type_id AND msi.organization_id = sh.organization_id AND msi.inventory_item_id = sl.inventory_item_id AND sl2.ship_line_id (+) = DECODE(alloc.from_parent_table_name,'INL_SHIP_LINES',alloc.from_parent_table_id,NULL) AND cl.charge_line_id (+) = DECODE(alloc.from_parent_table_name,'INL_CHARGE_LINES',alloc.from_parent_table_id,NULL) AND tl.tax_line_id (+) = DECODE(alloc.from_parent_table_name,'INL_TAX_LINES',alloc.from_parent_table_id ,NULL) AND assoc.association_id (+) = alloc.association_id AND sh.ship_header_id = sl.ship_header_id AND sl.ship_line_id = alloc.ship_line_id AND alloc.landed_cost_flag = 'Y') x GROUP BY x.ship_header_id, x.adjustment_num, x.ship_line_group_id, x.ship_line_id, x.parent_ship_line_id, x.ship_line_num, x.organization_id, x.inventory_item_id, x.inv_item, x.primary_qty, x.unit_of_measure, x.component_type, x.component_code, x.component_name, x.charge_line_type_id

View Text - HTML Formatted

SELECT X.SHIP_HEADER_ID
, X.ADJUSTMENT_NUM
, X.SHIP_LINE_GROUP_ID
, X.SHIP_LINE_ID
, X.PARENT_SHIP_LINE_ID
, X.SHIP_LINE_NUM
, X.ORGANIZATION_ID
, X.INVENTORY_ITEM_ID
, X.INV_ITEM
, X.PRIMARY_QTY
, X.UNIT_OF_MEASURE
, X.COMPONENT_TYPE
, X.COMPONENT_CODE
, X.COMPONENT_NAME
, X.CHARGE_LINE_TYPE_ID
, SUM(X.ALLOCATED_AMT) ALLOCATED_AMT
, RATIO_TO_REPORT(SUM(X.ALLOCATED_AMT)) OVER(PARTITION BY X.SHIP_HEADER_ID
, X.SHIP_LINE_ID
, X.ADJUSTMENT_NUM) * 100 ALLOCATION_PERCENT
, SUM(X.ESTIMATED_ALLOCATED_AMT) ESTIMATED_ALLOCATED_AMT
, RATIO_TO_REPORT(SUM(X.ESTIMATED_ALLOCATED_AMT)) OVER(PARTITION BY X.SHIP_HEADER_ID
, X.SHIP_LINE_ID
, X.ADJUSTMENT_NUM) * 100 ESTIMATED_ALLOCATION_PERCENT
FROM (SELECT ALLOC.SHIP_HEADER_ID
, ALLOC.ADJUSTMENT_NUM
, SL.SHIP_LINE_GROUP_ID
, SL.SHIP_LINE_ID
, SL.PARENT_SHIP_LINE_ID
, SL.SHIP_LINE_NUM
, SH.ORGANIZATION_ID
, SL.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS INV_ITEM
, SL.PRIMARY_QTY
, UOM.UNIT_OF_MEASURE
, DECODE(ASSOC.FROM_PARENT_TABLE_NAME
, 'INL_CHARGE_LINES'
, 'CHARGE'
, 'INL_TAX_LINES'
, 'TAX'
, 'ITEM PRICE') COMPONENT_TYPE
, DECODE(ASSOC.FROM_PARENT_TABLE_NAME
, 'INL_SHIP_HEADERS'
, 'OTHERS'
, 'INL_SHIP_LINES'
, 'OTHERS'
, 'INL_CHARGE_LINES'
, PE.PRICE_ELEMENT_CODE
, 'INL_TAX_LINES'
, TL.TAX_CODE
, MSI.CONCATENATED_SEGMENTS) COMPONENT_CODE
, DECODE(ASSOC.FROM_PARENT_TABLE_NAME
, 'INL_SHIP_HEADERS'
, 'OTHERS'
, 'INL_SHIP_LINES'
, 'OTHERS'
, 'INL_CHARGE_LINES'
, PE.NAME
, 'INL_TAX_LINES'
, TL.TAX_CODE
, MSI.CONCATENATED_SEGMENTS) COMPONENT_NAME
, CL.CHARGE_LINE_TYPE_ID
, ALLOC.ALLOCATION_AMT ALLOCATED_AMT
, DECODE(SL2.ADJUSTMENT_NUM
, NULL
, DECODE(CL.ADJUSTMENT_NUM
, NULL
, DECODE(TL.ADJUSTMENT_NUM
, NULL
, NULL
, DECODE(SIGN(TL.ADJUSTMENT_NUM)
, 1
, FIRST_VALUE(DECODE(ABS(TL.ADJUSTMENT_NUM)
, TL.ADJUSTMENT_NUM
, DECODE(TL.ADJUSTMENT_NUM
, 0
, ALLOC.ALLOCATION_AMT
, NULL)
, ALLOC.ALLOCATION_AMT)) OVER (PARTITION BY ALLOC.SHIP_HEADER_ID
, NVL(SL.PARENT_SHIP_LINE_ID
, SL.SHIP_LINE_ID)
, TL.TAX_LINE_NUM ORDER BY TL.ADJUSTMENT_NUM
, ALLOC.ALLOCATION_ID DESC)
, ALLOC.ALLOCATION_AMT))
, DECODE(SIGN(CL.ADJUSTMENT_NUM)
, 1
, FIRST_VALUE(DECODE(ABS(CL.ADJUSTMENT_NUM)
, CL.ADJUSTMENT_NUM
, DECODE(CL.ADJUSTMENT_NUM
, 0
, ALLOC.ALLOCATION_AMT
, NULL)
, ALLOC.ALLOCATION_AMT)) OVER (PARTITION BY ALLOC.SHIP_HEADER_ID
, NVL(SL.PARENT_SHIP_LINE_ID
, SL.SHIP_LINE_ID)
, CL.CHARGE_LINE_NUM ORDER BY CL.ADJUSTMENT_NUM
, ALLOC.ALLOCATION_ID DESC)
, ALLOC.ALLOCATION_AMT))
, DECODE(SIGN(SL2.ADJUSTMENT_NUM)
, 1
, FIRST_VALUE(DECODE(ABS(SL2.ADJUSTMENT_NUM)
, SL2.ADJUSTMENT_NUM
, DECODE(SL2.ADJUSTMENT_NUM
, 0
, ALLOC.ALLOCATION_AMT
, NULL)
, ALLOC.ALLOCATION_AMT)) OVER (PARTITION BY ALLOC.SHIP_HEADER_ID
, NVL(SL.PARENT_SHIP_LINE_ID
, SL.SHIP_LINE_ID)
, SL2.SHIP_LINE_NUM ORDER BY SL2.ADJUSTMENT_NUM
, ALLOC.ALLOCATION_ID DESC)
, ALLOC.ALLOCATION_AMT)) ESTIMATED_ALLOCATED_AMT
FROM MTL_UNITS_OF_MEASURE UOM
, PON_PRICE_ELEMENT_TYPES_VL PE
, MTL_SYSTEM_ITEMS_KFV MSI
, INL_CHARGE_LINES CL
, INL_TAX_LINES TL
, INL_ASSOCIATIONS ASSOC
, INL_SHIP_HEADERS_ALL SH
, INL_SHIP_LINES_ALL SL2
, INL_SHIP_LINES_ALL SL
, INL_ALLOCATIONS ALLOC
WHERE UOM.UOM_CODE = SL.PRIMARY_UOM_CODE
AND PE.PRICE_ELEMENT_TYPE_ID(+) = CL.CHARGE_LINE_TYPE_ID
AND MSI.ORGANIZATION_ID = SH.ORGANIZATION_ID
AND MSI.INVENTORY_ITEM_ID = SL.INVENTORY_ITEM_ID
AND SL2.SHIP_LINE_ID (+) = DECODE(ALLOC.FROM_PARENT_TABLE_NAME
, 'INL_SHIP_LINES'
, ALLOC.FROM_PARENT_TABLE_ID
, NULL)
AND CL.CHARGE_LINE_ID (+) = DECODE(ALLOC.FROM_PARENT_TABLE_NAME
, 'INL_CHARGE_LINES'
, ALLOC.FROM_PARENT_TABLE_ID
, NULL)
AND TL.TAX_LINE_ID (+) = DECODE(ALLOC.FROM_PARENT_TABLE_NAME
, 'INL_TAX_LINES'
, ALLOC.FROM_PARENT_TABLE_ID
, NULL)
AND ASSOC.ASSOCIATION_ID (+) = ALLOC.ASSOCIATION_ID
AND SH.SHIP_HEADER_ID = SL.SHIP_HEADER_ID
AND SL.SHIP_LINE_ID = ALLOC.SHIP_LINE_ID
AND ALLOC.LANDED_COST_FLAG = 'Y') X GROUP BY X.SHIP_HEADER_ID
, X.ADJUSTMENT_NUM
, X.SHIP_LINE_GROUP_ID
, X.SHIP_LINE_ID
, X.PARENT_SHIP_LINE_ID
, X.SHIP_LINE_NUM
, X.ORGANIZATION_ID
, X.INVENTORY_ITEM_ID
, X.INV_ITEM
, X.PRIMARY_QTY
, X.UNIT_OF_MEASURE
, X.COMPONENT_TYPE
, X.COMPONENT_CODE
, X.COMPONENT_NAME
, X.CHARGE_LINE_TYPE_ID