VIEW: APPS.INL_SHIPLN_LANDED_COSTS_V

Source

View Text - Preformatted

SELECT ish.ship_header_id, isl.ship_line_group_id, ial.adjustment_num, isl.ship_line_id, isl.ship_line_num, ish.organization_id, isl.inventory_item_id, (select sum(nvl(a.allocation_amt,0)) from inl_allocations a, inl_associations b WHERE a.ship_header_id = ish.ship_header_id and a.adjustment_num = ial.adjustment_num and a.ship_line_id = isl.ship_line_id and a.landed_cost_flag = 'Y' and a.association_id = b.association_id (+) and ((b.from_parent_table_name is null) or (b.from_parent_table_name = 'INL_SHIP_LINES'))) ITEM_PRICE, (select sum(nvl(a.allocation_amt,0)) from inl_allocations a, inl_associations b where a.ship_header_id = ish.ship_header_id and a.adjustment_num = ial.adjustment_num and a.ship_line_id = isl.ship_line_id and a.landed_cost_flag = 'Y' and a.association_id = b.association_id (+) and b.from_parent_table_name = 'INL_CHARGE_LINES') CHARGES, (select sum(nvl(a.allocation_amt,0)) from inl_allocations a, inl_associations b where a.ship_header_id = ish.ship_header_id and a.adjustment_num = ial.adjustment_num and a.ship_line_id = isl.ship_line_id and a.landed_cost_flag = 'Y' and a.association_id = b.association_id (+) and b.from_parent_table_name = 'INL_TAX_LINES') TAXES, SUM(NVL(isl.primary_qty,0)) primary_qty, isl.primary_uom_code, (select sum(nvl(a.allocation_amt,0)) from inl_allocations a, inl_associations b where a.ship_header_id = ish.ship_header_id and a.adjustment_num = ial.adjustment_num and a.ship_line_id = isl.ship_line_id and a.landed_cost_flag = 'Y' and a.association_id = b.association_id (+)) / SUM(NVL(isl.primary_qty,0)) UNIT_LANDED_COST FROM inl_ship_headers_all ish, inl_ship_lines_all isl, inl_allocations ial, inl_associations ias WHERE ish.ship_header_id = isl.ship_header_id AND ish.ship_header_id = ial.ship_header_id AND isl.ship_line_id (+) = ial.ship_line_id AND ias.association_id (+) = ial.association_id AND ial.landed_cost_flag = 'Y' AND ial.association_id IS NULL GROUP BY ish.ship_header_id, isl.ship_line_group_id, ial.adjustment_num, isl.ship_line_id, isl.ship_line_num, ish.organization_id, isl.inventory_item_id, isl.primary_uom_code, ias.from_parent_table_name

View Text - HTML Formatted

SELECT ISH.SHIP_HEADER_ID
, ISL.SHIP_LINE_GROUP_ID
, IAL.ADJUSTMENT_NUM
, ISL.SHIP_LINE_ID
, ISL.SHIP_LINE_NUM
, ISH.ORGANIZATION_ID
, ISL.INVENTORY_ITEM_ID
, (SELECT SUM(NVL(A.ALLOCATION_AMT
, 0))
FROM INL_ALLOCATIONS A
, INL_ASSOCIATIONS B
WHERE A.SHIP_HEADER_ID = ISH.SHIP_HEADER_ID
AND A.ADJUSTMENT_NUM = IAL.ADJUSTMENT_NUM
AND A.SHIP_LINE_ID = ISL.SHIP_LINE_ID
AND A.LANDED_COST_FLAG = 'Y'
AND A.ASSOCIATION_ID = B.ASSOCIATION_ID (+)
AND ((B.FROM_PARENT_TABLE_NAME IS NULL) OR (B.FROM_PARENT_TABLE_NAME = 'INL_SHIP_LINES'))) ITEM_PRICE
, (SELECT SUM(NVL(A.ALLOCATION_AMT
, 0))
FROM INL_ALLOCATIONS A
, INL_ASSOCIATIONS B
WHERE A.SHIP_HEADER_ID = ISH.SHIP_HEADER_ID
AND A.ADJUSTMENT_NUM = IAL.ADJUSTMENT_NUM
AND A.SHIP_LINE_ID = ISL.SHIP_LINE_ID
AND A.LANDED_COST_FLAG = 'Y'
AND A.ASSOCIATION_ID = B.ASSOCIATION_ID (+)
AND B.FROM_PARENT_TABLE_NAME = 'INL_CHARGE_LINES') CHARGES
, (SELECT SUM(NVL(A.ALLOCATION_AMT
, 0))
FROM INL_ALLOCATIONS A
, INL_ASSOCIATIONS B
WHERE A.SHIP_HEADER_ID = ISH.SHIP_HEADER_ID
AND A.ADJUSTMENT_NUM = IAL.ADJUSTMENT_NUM
AND A.SHIP_LINE_ID = ISL.SHIP_LINE_ID
AND A.LANDED_COST_FLAG = 'Y'
AND A.ASSOCIATION_ID = B.ASSOCIATION_ID (+)
AND B.FROM_PARENT_TABLE_NAME = 'INL_TAX_LINES') TAXES
, SUM(NVL(ISL.PRIMARY_QTY
, 0)) PRIMARY_QTY
, ISL.PRIMARY_UOM_CODE
, (SELECT SUM(NVL(A.ALLOCATION_AMT
, 0))
FROM INL_ALLOCATIONS A
, INL_ASSOCIATIONS B
WHERE A.SHIP_HEADER_ID = ISH.SHIP_HEADER_ID
AND A.ADJUSTMENT_NUM = IAL.ADJUSTMENT_NUM
AND A.SHIP_LINE_ID = ISL.SHIP_LINE_ID
AND A.LANDED_COST_FLAG = 'Y'
AND A.ASSOCIATION_ID = B.ASSOCIATION_ID (+)) / SUM(NVL(ISL.PRIMARY_QTY
, 0)) UNIT_LANDED_COST
FROM INL_SHIP_HEADERS_ALL ISH
, INL_SHIP_LINES_ALL ISL
, INL_ALLOCATIONS IAL
, INL_ASSOCIATIONS IAS
WHERE ISH.SHIP_HEADER_ID = ISL.SHIP_HEADER_ID
AND ISH.SHIP_HEADER_ID = IAL.SHIP_HEADER_ID
AND ISL.SHIP_LINE_ID (+) = IAL.SHIP_LINE_ID
AND IAS.ASSOCIATION_ID (+) = IAL.ASSOCIATION_ID
AND IAL.LANDED_COST_FLAG = 'Y'
AND IAL.ASSOCIATION_ID IS NULL GROUP BY ISH.SHIP_HEADER_ID
, ISL.SHIP_LINE_GROUP_ID
, IAL.ADJUSTMENT_NUM
, ISL.SHIP_LINE_ID
, ISL.SHIP_LINE_NUM
, ISH.ORGANIZATION_ID
, ISL.INVENTORY_ITEM_ID
, ISL.PRIMARY_UOM_CODE
, IAS.FROM_PARENT_TABLE_NAME