INL Unaccounted Landed Costs for Full Returns

Description
Categories: BI Publisher
Application: Oracle Landed Cost Management
Source: Unaccounted Landed Costs for Full Returns (XML)
Short Name: INLRTSCP_XML
DB package: INL_INLRTSCP_XMLP_PKG
SELECT distinct to_parent_table_name
						   ,to_parent_table_id
               ,to_organization_code
						   ,decode (to_parent_table_name
								   ,'INL_SHIP_LINES'
								   ,
									(
									SELECT  ish.ship_num
									FROM    inl_ship_headers_all ish
										   ,inl_ship_lines_all isl
									WHERE   ish.ship_header_id = isl.ship_header_id
									AND     rownum = 1
									AND     isl.ship_line_id = ica.to_parent_table_id
									)
								   ,'INL_SHIP_LINE_ID'
								   ,
									(
									SELECT  ish.ship_num
									FROM    inl_ship_headers_all ish
										   ,inl_ship_lines_all isl
									WHERE   ish.ship_header_id = isl.ship_header_id
									AND     rownum = 1
									AND     isl.ship_line_id = ica.to_parent_table_id
									)
								   ,'INL_SHIP_LINE_GROUPS'
								   ,
									(
									SELECT  ish.ship_num
									FROM    inl_ship_headers_all ish
										   ,inl_ship_line_groups isg
									WHERE   ish.ship_header_id = isg.ship_header_id
									AND     rownum = 1
									AND     isg.ship_line_group_id = ica.to_parent_table_id
									)
								   ,'INL_SHIP_HEADERS'
								   ,
									(
									SELECT  ish.ship_num
									FROM    inl_ship_headers_all ish
									WHERE   ish.ship_header_id = ica.to_parent_table_id
									)
								   ,NULL) associated_to_header
						   ,decode (to_parent_table_name
								   ,'INL_SHIP_LINES'
								   ,
									(
									SELECT  isg.ship_line_group_num
									FROM    inl_ship_line_groups isg
										   ,inl_ship_lines_all isl
									WHERE   isg.ship_line_group_id = isl.ship_line_group_id
									AND     isl.ship_line_id = ica.to_parent_table_id
									)
								   ,'INL_SHIP_LINE_ID'
								   ,
									(
									SELECT  isg.ship_line_group_num
									FROM    inl_ship_line_groups isg
										   ,inl_ship_lines_all isl
									WHERE   isg.ship_line_group_id = isl.ship_line_group_id
									AND     isl.ship_line_id = ica.to_parent_table_id
									)
								   ,'INL_SHIP_LINE_GROUPS'
								   ,
									(
									SELECT  ship_line_group_num
									FROM    inl_ship_line_groups
									WHERE   ship_line_group_id = ica.to_parent_table_id
									)
								   ,'INL_SHIP_HEADERS'
								   ,NULL
								   ,NULL) associated_to_group
						   ,decode (to_parent_table_name
								   ,'INL_SHIP_LINES'
								   ,
									(
									SELECT  isl.ship_line_num
									FROM    inl_ship_lines_all isl
									WHERE   isl.ship_line_id = ica.to_parent_table_id
									)
								   ,'INL_SHIP_LINE_ID'
								   ,
									(
									SELECT  isl.ship_line_num
									FROM    inl_ship_lines_all isl
									WHERE   isl.ship_line_id = ica.to_parent_table_id
									)
								   ,'INL_SHIP_LINE_GROUPS'
								   ,NULL
								   ,'INL_SHIP_HEADERS'
								   ,NULL
								   ,NULL) associated_to_line
						   ,decode (to_parent_table_name
								   ,'INL_SHIP_LINES'
								   ,
									(
									SELECT  rsh.receipt_num
									FROM    rcv_shipment_headers rsh
									       ,rcv_shipment_lines rsl
									       ,inl_ship_lines_all isl
									WHERE   nvl (isl.parent_ship_line_id,isl.ship_line_id) = rsl.lcm_shipment_line_id
									AND     rsl.shipment_header_id = rsh.shipment_header_id
									and     isl.ship_line_id =ica.to_parent_table_id
																		)
								   ,'INL_SHIP_LINE_ID'
								   ,
									(
									SELECT  rsh.receipt_num
									FROM    rcv_shipment_headers rsh
									       ,rcv_shipment_lines rsl
									       ,inl_ship_lines_all isl
									WHERE   nvl (isl.parent_ship_line_id,isl.ship_line_id) = rsl.lcm_shipment_line_id
									AND     rsl.shipment_header_id = rsh.shipment_header_id
									and     isl.ship_line_id =ica.to_parent_table_id
									)
								   ,'INL_SHIP_LINE_GROUPS'
								   ,NULL
								   ,'INL_SHIP_HEADERS'
								   ,NULL
								   ,NULL) associated_to_receipt
						   ,decode (to_parent_table_name
								   ,'INL_SHIP_LINES'
								   ,
									(
									SELECT  rsl.line_num
									FROM    rcv_shipment_headers rsh
									       ,rcv_shipment_lines rsl
									       ,inl_ship_lines_all isl
									WHERE   nvl (isl.parent_ship_line_id,isl.ship_line_id) = rsl.lcm_shipment_line_id
									AND     rsl.shipment_header_id = rsh.shipment_header_id
									and     isl.ship_line_id =ica.to_parent_table_id
									)
								   ,'INL_SHIP_LINE_ID'
								   ,
									(
									SELECT  rsl.line_num
									FROM    rcv_shipment_headers rsh
									       ,rcv_shipment_lines rsl
									       ,inl_ship_lines_all isl
									WHERE   nvl (isl.parent_ship_line_id,isl.ship_line_id) = rsl.lcm_shipment_line_id
									AND     rsl.shipment_header_id = rsh.shipment_header_id
									and     isl.ship_line_id =ica.to_parent_table_id
									)
								   ,'INL_SHIP_LINE_GROUPS'
								   ,NULL
								   ,'INL_SHIP_HEADERS'
								   ,NULL
								   ,NULL) associated_to_receipt_line								   
					FROM    inl_component_amounts_v ica
					WHERE   component_type = :component_type
					AND     component_id = :component_id
					AND     component_code = :component_code
					AND     component_amt = :component_amt
					AND     abs (ica.component_amt) > 0.000001
					AND     ica.component_allocated_amt = 0
					order by associated_to_header,associated_to_group,associated_to_line,associated_to_receipt,associated_to_receipt_line
Parameter NameSQL textValidation
Third Party Site To
 
LOV Oracle
Third Party Site From
 
LOV Oracle
Third Party
 
LOV Oracle
Shipment Date To
 
Date
Shipment Date From
 
Date
Shipment To
 
LOV Oracle
Shipment From
 
LOV Oracle
Inventory Organization
 
LOV Oracle