COPY OF: CST Intransit Value (1)

select 
msiv.segment1 item_number,
msiv.description description,
fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', mck.structure_id, null, mck.category_id, 'ALL', 'Y', 'VALUE') category,
msiv.primary_uom_code uom,
round(sum(ciqt.rollback_qty),:p_qty_precision) quantity,
round((nvl(cict.item_cost,0)*:p_exchange_rate1),:p_ext_prec) unit_cost,
round((sum(ciqt.rollback_qty*nvl(cict.item_cost,0) )*:p_exchange_rate1)/:round_unit)*:round_unit unit_cost_total,
case
when mp_from.process_enabled_flag <> mp_to.process_enabled_flag then 0
when ((mmt.transaction_action_id=12 and mmt.transaction_source_type_id=101) or (mmt.transaction_action_id=21 and mmt.transaction_source_type_id=7))then 0	
else 
 decode(mip.matl_interorg_transfer_code, 
  1,round((nvl(rsl.transfer_cost,0)/(gl_currency_api.get_rate(cod.set_of_books_id,:p_currency_code,sysdate,:p_curr_type))),:p_ext_prec),
  2,round((nvl(rsl.transfer_cost,0)/(gl_currency_api.get_rate(cod.set_of_books_id,:p_currency_code,sysdate,:p_curr_type))),:p_ext_prec),
  3,round((decode(mip.fob_point,2,( nvl(cict.item_cost,0 )*:p_exchange_rate1*rsl.transfer_percentage/100),1,(nvl(mta.rate_or_amount,0)*:p_exchange_rate1))/(gl_currency_api.get_rate(cod.set_of_books_id,:p_currency_code,sysdate,:p_curr_type))),:p_ext_prec),
  4,round((decode(mip.fob_point,2,( nvl(cict.item_cost,0 )*:p_exchange_rate1*rsl.transfer_percentage/100),1,(nvl(mta.rate_or_amount,0)*:p_exchange_rate1))/(gl_currency_api.get_rate(cod.set_of_books_id,:p_currency_code,sysdate,:p_curr_type))),:p_ext_prec) )
end transfer_charges,
round((nvl(rsl.transportation_cost,0)/(gl_currency_api.get_rate(cod.set_of_books_id,:p_currency_code,sysdate,:p_curr_type))),:p_ext_prec) freight_cost,
mp_from.organization_code from_org,
mp_to.organization_code to_org,
ml.meaning fob_point,
rsh.shipment_num shipment_number,
rsh.shipped_date ship_date,
rsh.expected_receipt_date+nvl(msi_to.postprocessing_lead_time,0) arrival_date,
rsh.freight_carrier_code freight_carrier,
rsh.waybill_airbill_num "Waybill/Airbill",
rsh.num_of_containers number_of_containers,
bom_cstritvr_xmlp_pkg.item_qtyformula(
msiv.primary_uom_code, 
decode(ciqt.organization_id, 
 ciqt.from_organization_id, msi_from.primary_uom_code,
 msi_to.primary_uom_code),
ciqt.inventory_item_id,
round(sum(ciqt.rollback_qty),:p_qty_precision)) item_quantity, 
bom_cstritvr_xmlp_pkg.tot_costformula(
 ciqt.organization_id, 
 mp_from.organization_id, 
 msi.primary_uom_code, 
 decode(ciqt.organization_id, 
  ciqt.from_organization_id,msi_from.primary_uom_code,
  msi_to.primary_uom_code), 
 ciqt.inventory_item_id, 
 round(sum(ciqt.rollback_qty),:p_qty_precision), 
 round((nvl(cict.item_cost,0)* :p_exchange_rate1),:p_ext_prec), 
 round((sum(ciqt.rollback_qty*nvl(cict.item_cost,0) )*:p_exchange_rate1)/:round_unit)*:round_unit ) total_cost,
bom_cstritvr_xmlp_pkg.xfer_cost_totalformula(
 case
 when mp_from.process_enabled_flag <> mp_to.process_enabled_flag then 0
 when ((mmt.transaction_action_id=12 and mmt.transaction_source_type_id=101) or (mmt.transaction_action_id=21 and mmt.transaction_source_type_id=7)) then 0
 else
 decode(mip.matl_interorg_transfer_code, 
 1,round((nvl(rsl.transfer_cost,0)/(gl_currency_api.get_rate(cod.set_of_books_id,:p_currency_code,sysdate,:p_curr_type))),:p_ext_prec),
 2,round((nvl(rsl.transfer_cost,0)/(gl_currency_api.get_rate(cod.set_of_books_id,:p_currency_code,sysdate,:p_curr_type))), :p_ext_prec),
 3,round((decode(mip.fob_point,2,( nvl(cict.item_cost,0)*:p_exchange_rate1*rsl.transfer_percentage/100),1,(nvl(mta.rate_or_amount,0)*:p_exchange_rate1))/(gl_currency_api.get_rate(cod.set_of_books_id,:p_currency_code,sysdate,:p_curr_type))),:p_ext_prec),
 4,round((decode(mip.fob_point,2,( nvl(cict.item_cost,0 )*:p_exchange_rate1*rsl.transfer_percentage/100),1,(nvl(mta.rate_or_amount,0)*:p_exchange_rate1))/(gl_currency_api.get_rate(cod.set_of_books_id,:p_currency_code,sysdate,:p_curr_type))), :p_ext_prec) 
 )
 end, 
 round(sum(ciqt.rollback_qty),:p_qty_precision)
 ) transfer_charges_total, 
bom_cstritvr_xmlp_pkg.xport_cost_totalformula(
 round((nvl(rsl.transportation_cost,0)/(gl_currency_api.get_rate(cod.set_of_books_id,:p_currency_code,sysdate,:p_curr_type))),:p_ext_prec), 
 round(sum(ciqt.rollback_qty),:p_qty_precision)) freight_cost_total
from 
cst_inv_qty_temp ciqt,
cst_inv_cost_temp cict,
rcv_shipment_headers rsh,
rcv_shipment_lines rsl,
cst_cost_groups ccg,
mfg_lookups ml,
mtl_parameters mp,
mtl_parameters mp_from,
mtl_parameters mp_to,
mtl_system_items_vl msiv,
mtl_system_items msi,
mtl_system_items msi_from,
mtl_system_items msi_to,
mtl_categories_kfv mck,
cst_organization_definitions cod,
mtl_interorg_parameters mip,
mtl_transaction_accounts mta,
mtl_material_transactions mmt
where 
1=1 and
msiv.organization_id=:p_org_id and
cict.organization_id(+)=ciqt.organization_id and
cict.inventory_item_id(+)=ciqt.inventory_item_id and
cict.cost_type_id(+)=ciqt.cost_type_id and
(mp.primary_cost_method=1 or nvl(cict.cost_group_id,ciqt.cost_group_id)=ciqt.cost_group_id) and
rsh.shipment_header_id=rsl.shipment_header_id and
rsl.shipment_line_id=ciqt.shipment_line_id and
mta.transaction_id(+)=rsl.mmt_transaction_id and
mta.accounting_line_type(+)= 11 and
mmt.transaction_id(+)=rsl.mmt_transaction_id and
ccg.cost_group_id=ciqt.cost_group_id and
ml.lookup_type='MTL_FOB_POINT' and
ml.lookup_code=decode(ciqt.organization_id,ciqt.to_organization_id,1,2) and
mp.organization_id=ciqt.organization_id and
mp_from.organization_id=ciqt.from_organization_id and
mp_to.organization_id=ciqt.to_organization_id and
msiv.inventory_item_id=ciqt.inventory_item_id and
msi.organization_id=ciqt.organization_id and
msi.inventory_item_id=ciqt.inventory_item_id and
msi_from.organization_id=ciqt.from_organization_id and
msi_from.inventory_item_id=ciqt.inventory_item_id and
msi_to.organization_id=ciqt.to_organization_id and
msi_to.inventory_item_id=ciqt.inventory_item_id and
mck.category_id=ciqt.category_id and
mip.from_organization_id(+)=ciqt.from_organization_id and
mip.to_organization_id(+)=ciqt.to_organization_id and
cod.organization_id=mp_from.organization_id 
group by
msiv.segment1,
fnd_flex_xml_publisher_apis.process_kff_combination_1('category_segment', 'INV', 'MCAT', mck.structure_id, null, mck.category_id, 'ALL', 'Y', 'VALUE'),
msiv.description,
msiv.primary_uom_code,
msi.primary_uom_code,
ciqt.organization_id,
mp_from.organization_id,
mp_from.organization_code,
mp_to.organization_code,
ml.meaning,
rsh.shipment_num,
rsh.shipped_date,
rsh.expected_receipt_date+nvl(msi_to.postprocessing_lead_time,0),
rsh.freight_carrier_code,
rsh.waybill_airbill_num,
rsh.num_of_containers,
ccg.cost_group,
decode(ciqt.organization_id, 
ciqt.from_organization_id, msi_from.primary_uom_code,
msi_to.primary_uom_code),
round((nvl(cict.item_cost,0)*:p_exchange_rate1),:p_ext_prec),
ciqt.inventory_item_id,
decode(:p_cost_type_id,cict.cost_type_id,' ','*'),
case
when mp_from.process_enabled_flag <> mp_to.process_enabled_flag then 0
when ((mmt.transaction_action_id=12 and mmt.transaction_source_type_id=101) or (mmt.transaction_action_id=21 and mmt.transaction_source_type_id=7)) then 0
else
decode( mip.matl_interorg_transfer_code, 
  1,round((nvl(rsl.transfer_cost,0)/(gl_currency_api.get_rate(cod.set_of_books_id,:p_currency_code,sysdate,:p_curr_type))), :p_ext_prec),
  2,round((nvl(rsl.transfer_cost,0)/(gl_currency_api.get_rate(cod.set_of_books_id,:p_currency_code,sysdate,:p_curr_type))), :p_ext_prec),
  3,round(
  (decode(mip.fob_point,
    2,(nvl(cict.item_cost,0)*:p_exchange_rate1*rsl.transfer_percentage/100),
    1,(nvl(mta.rate_or_amount,0)*:p_exchange_rate1)
   )/(gl_currency_api.get_rate(cod.set_of_books_id,:p_currency_code,sysdate,:p_curr_type))),:p_ext_prec),
  4,round(
    (decode(mip.fob_point,
    2,(nvl(cict.item_cost,0)*:p_exchange_rate1*rsl.transfer_percentage/100),
    1,(nvl(mta.rate_or_amount,0)*:p_exchange_rate1)
    )/(gl_currency_api.get_rate(cod.set_of_books_id,:p_currency_code,sysdate,:p_curr_type))),:p_ext_prec) 
)
end,
round((nvl(rsl.transportation_cost,0)/(gl_currency_api.get_rate(cod.set_of_books_id,:p_currency_code,sysdate,:p_curr_type))),:p_ext_prec)
having round(sum(ciqt.rollback_qty),:p_qty_precision) <> 0