Reports2017-11-18T12:27:27+00:00

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
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
Adjustment Date To
 
Date
Adjustment Date From
 
Date
Adjustments
 
LOV Oracle
Document/Purchase Order To
 
Document/Purchase Order From
 
Item To
 
LOV Oracle
Item From
 
LOV Oracle
Source Type
 
LOV Oracle
Shipment Date To
 
Date
Shipment Date From
 
Date
Shipment To
 
LOV Oracle
Shipment From
 
LOV Oracle
Inventory Organization
 
LOV Oracle
By continuing to use the site, you agree to the use of cookies. Accept