VIEW: APPS.INL_DET_LANDED_COSTS_V

Source

View Text - Preformatted

SELECT cas.ship_header_id, cas.adjustment_num, cas.ship_line_group_id, cas.ship_line_id, cas.organization_id, cas.inventory_item_id, msi.concatenated_segments AS inv_item, cas.primary_qty, muo.unit_of_measure, SUM(cas.allocation_amt) AS allocated_amt, SUM((SELECT (cas.allocation_amt*100)/SUM(NVL(a.allocation_amt,0))  FROM INL_ALLOCATION_SUMMARY_V A WHERE a.ship_line_id = cas.ship_line_id AND a.inventory_item_id = cas.inventory_item_id AND a.organization_id = cas.organization_id AND a.ship_header_id = cas.ship_header_id AND a.adjustment_num = cas.adjustment_num AND a.landed_cost_flag = 'Y')) AS allocation_percent, DECODE(cas.assoc_from_parent_table_name,'INL_CHARGE_LINES', 'CHARGE', 'INL_TAX_LINES', 'TAX', 'ITEM PRICE') AS component_type, DECODE(cas.assoc_from_parent_table_name,'INL_SHIP_HEADERS', 'OTHERS', 'INL_SHIP_LINES', 'OTHERS', 'INL_CHARGE_LINES', clt.charge_line_type_name, 'INL_TAX_LINES', cta.tax_code, msi.concatenated_segments) AS component_name FROM inl_allocation_summary_v cas, inl_ship_landed_costs_v cuc, inl_tax_lines cta, inl_charge_line_types_vl clt, inl_charge_lines cch, mtl_system_items_vl msi, mtl_units_of_measure_vl muo WHERE cas.ship_header_id = cuc.ship_header_id AND cas.adjustment_num = cuc.adjustment_num AND cas.inventory_item_id = cuc.inventory_item_id AND msi.inventory_item_id = cas.inventory_item_id AND msi.organization_id = cas.organization_id AND muo.uom_code = cuc.primary_uom_code AND cta.tax_line_id (+) = DECODE(cas.assoc_from_parent_table_name,'INL_TAX_LINES',cas.assoc_from_parent_table_id,NULL) AND clt.charge_line_type_id (+) = cch.charge_line_type_id AND cch.charge_line_id (+) = DECODE(cas.assoc_from_parent_table_name,'INL_CHARGE_LINES',cas.assoc_from_parent_table_id,NULL) AND cas.landed_cost_flag = 'Y' GROUP BY cas.ship_header_id, cas.adjustment_num, cas.ship_line_group_id, cas.ship_line_id, cas.primary_qty, cas.organization_id, cas.inventory_item_id, msi.concatenated_segments, cas.organization_id, cuc.primary_uom_code, msi.segment1, muo.unit_of_measure, cch.charge_line_type_id, cta.tax_code, DECODE(cas.assoc_from_parent_table_name,'INL_CHARGE_LINES', 'CHARGE', 'INL_TAX_LINES', 'TAX', 'ITEM PRICE'), DECODE(cas.assoc_from_parent_table_name,'INL_SHIP_HEADERS', 'OTHERS', 'INL_SHIP_LINES', 'OTHERS', 'INL_CHARGE_LINES', clt.charge_line_type_name, 'INL_TAX_LINES', cta.tax_code, msi.concatenated_segments)

View Text - HTML Formatted

SELECT CAS.SHIP_HEADER_ID
, CAS.ADJUSTMENT_NUM
, CAS.SHIP_LINE_GROUP_ID
, CAS.SHIP_LINE_ID
, CAS.ORGANIZATION_ID
, CAS.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS AS INV_ITEM
, CAS.PRIMARY_QTY
, MUO.UNIT_OF_MEASURE
, SUM(CAS.ALLOCATION_AMT) AS ALLOCATED_AMT
, SUM((SELECT (CAS.ALLOCATION_AMT*100)/SUM(NVL(A.ALLOCATION_AMT
, 0))
FROM INL_ALLOCATION_SUMMARY_V A
WHERE A.SHIP_LINE_ID = CAS.SHIP_LINE_ID
AND A.INVENTORY_ITEM_ID = CAS.INVENTORY_ITEM_ID
AND A.ORGANIZATION_ID = CAS.ORGANIZATION_ID
AND A.SHIP_HEADER_ID = CAS.SHIP_HEADER_ID
AND A.ADJUSTMENT_NUM = CAS.ADJUSTMENT_NUM
AND A.LANDED_COST_FLAG = 'Y')) AS ALLOCATION_PERCENT
, DECODE(CAS.ASSOC_FROM_PARENT_TABLE_NAME
, 'INL_CHARGE_LINES'
, 'CHARGE'
, 'INL_TAX_LINES'
, 'TAX'
, 'ITEM PRICE') AS COMPONENT_TYPE
, DECODE(CAS.ASSOC_FROM_PARENT_TABLE_NAME
, 'INL_SHIP_HEADERS'
, 'OTHERS'
, 'INL_SHIP_LINES'
, 'OTHERS'
, 'INL_CHARGE_LINES'
, CLT.CHARGE_LINE_TYPE_NAME
, 'INL_TAX_LINES'
, CTA.TAX_CODE
, MSI.CONCATENATED_SEGMENTS) AS COMPONENT_NAME
FROM INL_ALLOCATION_SUMMARY_V CAS
, INL_SHIP_LANDED_COSTS_V CUC
, INL_TAX_LINES CTA
, INL_CHARGE_LINE_TYPES_VL CLT
, INL_CHARGE_LINES CCH
, MTL_SYSTEM_ITEMS_VL MSI
, MTL_UNITS_OF_MEASURE_VL MUO
WHERE CAS.SHIP_HEADER_ID = CUC.SHIP_HEADER_ID
AND CAS.ADJUSTMENT_NUM = CUC.ADJUSTMENT_NUM
AND CAS.INVENTORY_ITEM_ID = CUC.INVENTORY_ITEM_ID
AND MSI.INVENTORY_ITEM_ID = CAS.INVENTORY_ITEM_ID
AND MSI.ORGANIZATION_ID = CAS.ORGANIZATION_ID
AND MUO.UOM_CODE = CUC.PRIMARY_UOM_CODE
AND CTA.TAX_LINE_ID (+) = DECODE(CAS.ASSOC_FROM_PARENT_TABLE_NAME
, 'INL_TAX_LINES'
, CAS.ASSOC_FROM_PARENT_TABLE_ID
, NULL)
AND CLT.CHARGE_LINE_TYPE_ID (+) = CCH.CHARGE_LINE_TYPE_ID
AND CCH.CHARGE_LINE_ID (+) = DECODE(CAS.ASSOC_FROM_PARENT_TABLE_NAME
, 'INL_CHARGE_LINES'
, CAS.ASSOC_FROM_PARENT_TABLE_ID
, NULL)
AND CAS.LANDED_COST_FLAG = 'Y' GROUP BY CAS.SHIP_HEADER_ID
, CAS.ADJUSTMENT_NUM
, CAS.SHIP_LINE_GROUP_ID
, CAS.SHIP_LINE_ID
, CAS.PRIMARY_QTY
, CAS.ORGANIZATION_ID
, CAS.INVENTORY_ITEM_ID
, MSI.CONCATENATED_SEGMENTS
, CAS.ORGANIZATION_ID
, CUC.PRIMARY_UOM_CODE
, MSI.SEGMENT1
, MUO.UNIT_OF_MEASURE
, CCH.CHARGE_LINE_TYPE_ID
, CTA.TAX_CODE
, DECODE(CAS.ASSOC_FROM_PARENT_TABLE_NAME
, 'INL_CHARGE_LINES'
, 'CHARGE'
, 'INL_TAX_LINES'
, 'TAX'
, 'ITEM PRICE')
, DECODE(CAS.ASSOC_FROM_PARENT_TABLE_NAME
, 'INL_SHIP_HEADERS'
, 'OTHERS'
, 'INL_SHIP_LINES'
, 'OTHERS'
, 'INL_CHARGE_LINES'
, CLT.CHARGE_LINE_TYPE_NAME
, 'INL_TAX_LINES'
, CTA.TAX_CODE
, MSI.CONCATENATED_SEGMENTS)