INL Landed Cost Summary
Description
Categories: BI Publisher
Application: Oracle Landed Cost Management
Source: Landed Cost Summary Report (XML)
Short Name: INLSLCRP_XML
DB package: INL_INLSLCRP_XMLP_PKG
Source: Landed Cost Summary Report (XML)
Short Name: INLSLCRP_XML
DB package: INL_INLSLCRP_XMLP_PKG
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 |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Inventory Organization |
|
LOV Oracle | |
Shipment From |
|
LOV Oracle | |
Shipment To |
|
LOV Oracle | |
Shipment Date From |
|
Date | |
Shipment Date To |
|
Date | |
Source Type |
|
LOV Oracle | |
Item From |
|
LOV Oracle | |
Item To |
|
LOV Oracle | |
Document/Purchase Order From |
|
Char | |
Document/Purchase Order To |
|
Char | |
Adjustments |
|
LOV Oracle | |
Adjustment Date From |
|
Date | |
Adjustment Date To |
|
Date |