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 |