SELECT ALLSUMM.SHIP_HEADER_ID , ALLSUMM.ADJUSTMENT_NUM , ALLSUMM.SHIP_LINE_ID , ALLSUMM.SHIP_LINE_NUM , ALLSUMM.ORGANIZATION_ID , ALLSUMM.INVENTORY_ITEM_ID , (SELECT SUM(NVL(ALLSUMM2.ALLOCATION_AMT , 0)) FROM INL_ALLOCATION_SUMMARY_V ALLSUMM2 FROM INL_ALLOCATION_SUMMARY_V ALLSUMM2 WHERE ALLSUMM2.SHIP_HEADER_ID = ALLSUMM.SHIP_HEADER_ID AND ALLSUMM2.ADJUSTMENT_NUM = ALLSUMM.ADJUSTMENT_NUM AND ALLSUMM2.SHIP_LINE_ID = ALLSUMM.SHIP_LINE_ID AND ALLSUMM2.LANDED_COST_FLAG = 'Y' AND ALLSUMM2.ORGANIZATION_ID = ALLSUMM.ORGANIZATION_ID AND ALLSUMM2.INVENTORY_ITEM_ID = ALLSUMM.INVENTORY_ITEM_ID AND (ALLSUMM2.ASSOC_FROM_PARENT_TABLE_NAME IS NULL OR (ALLSUMM2.ASSOC_FROM_PARENT_TABLE_NAME NOT LIKE 'INL_CHARGE%' AND ALLSUMM2.ASSOC_FROM_PARENT_TABLE_NAME NOT LIKE 'INL_TAX%'))) ITEM_PRICE , (SELECT SUM(NVL(ALLSUMM2.ALLOCATION_AMT , 0)) FROM INL_ALLOCATION_SUMMARY_V ALLSUMM2 WHERE ALLSUMM2.SHIP_HEADER_ID = ALLSUMM.SHIP_HEADER_ID AND ALLSUMM2.ADJUSTMENT_NUM = ALLSUMM.ADJUSTMENT_NUM AND ALLSUMM2.SHIP_LINE_ID = ALLSUMM.SHIP_LINE_ID AND ALLSUMM2.LANDED_COST_FLAG = 'Y' AND ALLSUMM2.ORGANIZATION_ID = ALLSUMM.ORGANIZATION_ID AND ALLSUMM2.INVENTORY_ITEM_ID = ALLSUMM.INVENTORY_ITEM_ID AND ALLSUMM2.ASSOC_FROM_PARENT_TABLE_NAME LIKE 'INL_CHARGE%') CHARGES , (SELECT SUM(NVL(ALLSUMM2.ALLOCATION_AMT , 0)) FROM INL_ALLOCATION_SUMMARY_V ALLSUMM2 WHERE ALLSUMM2.SHIP_HEADER_ID = ALLSUMM.SHIP_HEADER_ID AND ALLSUMM2.ADJUSTMENT_NUM = ALLSUMM.ADJUSTMENT_NUM AND ALLSUMM2.SHIP_LINE_ID = ALLSUMM.SHIP_LINE_ID AND ALLSUMM2.LANDED_COST_FLAG = 'Y' AND ALLSUMM2.ORGANIZATION_ID = ALLSUMM.ORGANIZATION_ID AND ALLSUMM2.INVENTORY_ITEM_ID = ALLSUMM.INVENTORY_ITEM_ID AND ALLSUMM2.ASSOC_FROM_PARENT_TABLE_NAME LIKE 'INL_TAX%') TAXES , SUM(NVL(ALLSUMM.PRIMARY_QTY , 0)) PRIMARY_QTY , ALLSUMM.PRIMARY_UOM_CODE , (SELECT SUM(NVL(ALLSUMM2.ALLOCATION_AMT , 0)) FROM INL_ALLOCATION_SUMMARY_V ALLSUMM2 WHERE ALLSUMM2.SHIP_HEADER_ID = ALLSUMM.SHIP_HEADER_ID AND ALLSUMM2.ADJUSTMENT_NUM = ALLSUMM.ADJUSTMENT_NUM AND ALLSUMM2.SHIP_LINE_ID = ALLSUMM.SHIP_LINE_ID AND ALLSUMM2.LANDED_COST_FLAG = 'Y' AND ALLSUMM2.ORGANIZATION_ID = ALLSUMM.ORGANIZATION_ID AND ALLSUMM2.INVENTORY_ITEM_ID = ALLSUMM.INVENTORY_ITEM_ID) /SUM(NVL(ALLSUMM.PRIMARY_QTY , 0)) UNIT_LANDED_COST FROM INL_ALLOCATION_SUMMARY_V ALLSUMM WHERE ALLSUMM.LANDED_COST_FLAG = 'Y' AND ALLSUMM.ASSOCIATION_ID IS NULL GROUP BY ALLSUMM.SHIP_HEADER_ID , ALLSUMM.ADJUSTMENT_NUM , ALLSUMM.SHIP_LINE_ID , ALLSUMM.SHIP_LINE_NUM , ALLSUMM.ORGANIZATION_ID , ALLSUMM.INVENTORY_ITEM_ID , ALLSUMM.PRIMARY_UOM_CODE