INL Landed Cost Summary

➜ Back to Library
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

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

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
Download
 
Related Blitz Reports
INL Landed Cost Detail XXAMZ OTIF Delivered Orders(XXONT1253) QP Customer Pricing Engine Request GL Account Analysis (Distributions) CAC Open Internal ISO-IRO INV Intercompany Invoice Reconciliation RK: AR Transactions and Lines Avalara VAT LinesData Avalara VAT listing
Blitz Report™

Blitz Report™ provides multiple benefits: