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 |