SELECT mp.organization_code
,ood.organization_name
,hrl.location_code
,sh.ship_num
,to_char(sh.ship_date,'DD-MON-YYYY') ship_date
,lc.adjustment_num
,to_char(
(
SELECT max (a.creation_date)
FROM inl_allocations a
WHERE lc.ship_header_id = a.ship_header_id
AND lc.ship_line_id = a.ship_line_id
AND lc.adjustment_num = a.adjustment_num
),'DD-MON-YYYY') adjustment_date
,slg.ship_line_group_num
,hp.party_name
,hps.party_site_name
,lc.ship_line_num
,lc.inv_item
,msi.description item_description
,decode (sl.primary_qty
,0
,0
,sum (lc.allocated_amt) / sl.primary_qty) ulc
,to_char (decode (sl.primary_qty
,0
,0
,sum (lc.allocated_amt) / sl.primary_qty)
,fnd_currency_cache.get_format_mask (gsb.currency_code
,30)) ulc_f
,sum (lc.allocated_amt) actual_amount
,to_char (sum (lc.allocated_amt)
,fnd_currency_cache.get_format_mask (gsb.currency_code
,30)) actual_amount_f
,sl.primary_qty
,lc.unit_of_measure
,sh.organization_id
,nvl (lc.parent_ship_line_id
,lc.ship_line_id) parent_ship_line_id
,lc.ship_line_id
,lc.ship_header_id
,sh.location_id
,slg.ship_line_group_reference
,slg.party_id
,slg.party_site_id
,sl.ship_line_src_type_code
,nvl (ph.segment1
,sl.src_document_num) src_document_num
,nvl (pr.release_num
,sl.src_document_version_num) src_document_version_num
,nvl (pl.line_num
,sl.src_document_line_num) src_document_line_num
,nvl (pll.shipment_num
,sl.src_document_line_detail_num) src_document_line_detail_num
,gsb.currency_code funct_currency
,decode (lc.adjustment_num
,sh.adjustment_num
,1
,0) last_adj
,DECODE(sl.primary_qty, 0,0,(SUM(DECODE(lc.component_type, 'ITEM PRICE',lc.allocated_amt,0))/sl.primary_qty)) unit_price
,to_char (DECODE(sl.primary_qty, 0,0,(SUM(DECODE(lc.component_type, 'ITEM PRICE',lc.allocated_amt,0))/sl.primary_qty))
,fnd_currency_cache.get_format_mask (gsb.currency_code
,30)) unit_price_f
FROM inl_det_landed_costs_v lc
,inl_ship_headers_all sh
,inl_ship_line_groups slg
,inl_ship_lines_all sl
,mtl_parameters mp
,hr_locations_all hrl
,hz_parties hp
,hz_party_sites hps
,mtl_system_items_kfv msi
,po_headers_all ph
,po_releases_all pr
,po_lines_all pl
,po_line_locations_all pll
,org_organization_definitions ood
,gl_sets_of_books gsb
WHERE hrl.location_id = sh.location_id
AND lc.ship_header_id = sh.ship_header_id
AND sh.simulation_id IS NULL
AND sh.ship_status_code = 'COMPLETED'
AND nvl (sh.pending_matching_flag
,'N') = 'N'
AND nvl (sh.pending_update_flag
,'N') = 'N'
AND slg.ship_header_id = sh.ship_header_id
AND slg.ship_line_group_id = lc.ship_line_group_id
AND slg.ship_line_group_id = sl.ship_line_group_id
AND sh.organization_id = mp.organization_id
AND lc.ship_line_id = sl.ship_line_id
AND hp.party_id = slg.party_id
AND hps.party_site_id = slg.party_site_id
AND msi.organization_id = sh.organization_id
AND msi.inventory_item_id = sl.inventory_item_id
AND pll.line_location_id (+) = sl.ship_line_source_id
AND pl.po_line_id (+) = pll.po_line_id
AND pll.po_header_id = ph.po_header_id (+)
AND pll.po_release_id = pr.po_release_id (+)
AND gsb.set_of_books_id = ood.set_of_books_id
AND sh.organization_id = ood.organization_id
&LP_ORGANIZATION_ID
&LP_SHIP_HEADER_ID
&LP_SHIP_DATE
&LP_SOURCE_TYPE
&LP_ITEM_RANGE
&LP_DOC_RANGE
&LP_ADJ_INCLUDED
&LP_ADJ_DATE
GROUP BY mp.organization_code
,ood.organization_name
,hrl.location_code
,sh.ship_num
,sh.ship_date
,lc.adjustment_num
,slg.ship_line_group_num
,hp.party_name
,hps.party_site_name
,lc.ship_line_num
,lc.inv_item
,msi.description
,sl.primary_qty
,lc.unit_of_measure
,sh.organization_id
,nvl (lc.parent_ship_line_id
,lc.ship_line_id)
,lc.ship_line_id
,lc.ship_header_id
,sh.location_id
,slg.ship_line_group_reference
,slg.party_id
,slg.party_site_id
,sl.ship_line_src_type_code
,nvl (ph.segment1
,sl.src_document_num)
,nvl (pr.release_num
,sl.src_document_version_num)
,nvl (pl.line_num
,sl.src_document_line_num)
,nvl (pll.shipment_num
,sl.src_document_line_detail_num)
,gsb.currency_code
,decode (lc.adjustment_num
,sh.adjustment_num
,1
,0)
ORDER BY mp.organization_code
,sh.ship_num
,slg.ship_line_group_num
,lc.ship_line_num
,lc.adjustment_num |
