INL Landed Cost Detail

Description
Categories: BI Publisher
Application: Oracle Landed Cost Management
Source: Landed Cost Detail Report (XML)
Short Name: INLLCCRP_XML
DB package: INL_INLLCCRP_XMLP_PKG
SELECT ship
						   ,ipr.ship_header_id
						   ,to_char(ship_date,'DD-MON-YYYY') ship_date
						   ,organization_id
						   ,organization_code
						   ,q1.organization_name
						   ,slg_num
						   ,ship_line_group_reference
						   ,ship_line_src_type_code
						   ,line
						   ,alloc
						   ,(SELECT  distribution_num
                             FROM    po_distributions_all
                             WHERE   po_distribution_id = ipr.po_dist) po_dist
						   ,type
						   ,(
						        SELECT  meaning
								FROM    fnd_lookup_values_vl flv
								WHERE   flv.lookup_type = 'INL_COMPONENT_TYPES'
								AND     flv.lookup_code = decode(ipr.type,'ITEM PRICE', 'ITEM',ipr.type)
						     ) component_type
						   ,ref
						   ,item
						   ,to_char (elc,q1.get_format_mask) elc_f
						   ,to_number(to_char (elc,q1.get_format_mask),q1.get_format_mask) elc						   
						   ,to_char (billed,q1.get_format_mask) billed
						   ,to_char (decode(show_alc,0,null,alc),q1.get_format_mask) alc_f
						   ,to_number(to_char (decode(show_alc,0,null,alc),q1.get_format_mask),q1.get_format_mask) alc						   
						   ,to_number(nvl(to_char (to_number(to_char (nvl(decode(show_alc,0,null,alc),0),q1.get_format_mask),q1.get_format_mask)-to_number(to_char (nvl(elc,0),q1.get_format_mask),q1.get_format_mask),q1.get_format_mask),0),q1.get_format_mask) lc_variance
						   ,rtrim(to_char(prim_quantity,'FM9999999999.9999999999') ,'.') prim_quantity
						   ,line_primary_quantity
						   ,rtrim(to_char(line_primary_quantity,'FM9999999999.9999999999') ,'.')  line_primary_quantity_f						   						   
						   ,uom
						   ,project_id
						   ,project_number
						   ,task_id
						   ,task_number
						   ,trx
						   ,party_name
				           ,q1.currency_code
						   ,q1.get_format_mask amt_format_mask
						   ,q1.get_extprecformatmask ulc_amt_format_mask
						   ,show_alc
						   ,countrts
						   ,ship_type_code
						   ,ship_line_type_code
					FROM    inl_po_dist_landed_costs_v ipr,
					 (
							SELECT  sh.ship_header_id
								   ,sh.adjustment_num
								   ,sh.ship_num
								   ,gsb.currency_code
								   ,ood.organization_name
								   ,fnd_currency_cache.get_format_mask (gsb.currency_code
																	   ,30) get_format_mask
								   ,INL_INTEGRATION_GRP.GET_EXTPRECFORMATMASK(gsb.currency_code,30) get_extprecformatmask								   
							FROM    inl_ship_headers_all sh
								   ,gl_sets_of_books gsb
								   ,org_organization_definitions ood
							WHERE   gsb.set_of_books_id = ood.set_of_books_id
							AND     ood.organization_id = sh.organization_id
							) q1
					WHERE   q1.ship_header_id = ipr.ship_header_id
					 AND     NOT (ipr.trx = 'RECEIVE' and ipr.prim_quantity = 0)
					&LP_ORGANIZATION_ID
					&LP_SHIP_HEADER_ID
					&LP_SHIP_DATE
					&LP_SOURCE_TYPE
					&LP_ITEM_RANGE
					&LP_PROJECT_NUMBER
					&LP_TASK_NUMBER				
					ORDER BY organization_name,ship,slg_num,line,alloc ASC
Parameter Name SQL text Validation
Task
 
LOV Oracle
P_TO_PROJECT_NUMBER
 
LOV Oracle
P_FROM_PROJECT_NUMBER
 
LOV Oracle
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
Ask a question