View: INL_DET_LANDED_COSTS_V

Product:	INL - Oracle Landed Cost Management
Description:	This view shows detailed landed cost information for a Shipment.
Implementation/DBA Data:	APPS.INL_DET_LANDED_COSTS_V

View Text

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

Columns