GME FIFO Report

select distinct
org_code,
inv_org_name,
item,
item_description,
on_hand_qty,
uom,
fifo_value,
std_cost,
fifo_value - std_cost fifo_diff
from
(select s.org_code,
s.inv_org_name,
msib.segment1 item,
msib.description item_description,
round((
case
when s.transaction_quantity <= s.primary_quantity
then s.transaction_quantity
else s.primary_quantity
end),4) on_hand_qty,
msib.primary_uom_code uom,
round(xxnuf_get_fifo_value(s.organization_id,s.inventory_item_id,(
case
when s.transaction_quantity <= s.primary_quantity
then s.transaction_quantity
else s.primary_quantity
end)),4) fifo_value,
round(
case
when s.transaction_quantity <= s.primary_quantity
then s.transaction_quantity
else s.primary_quantity
end * xxnuf_mat_var_rep_pkg.get_std_cost (:period_name, 'NUF_CAL', s.organization_id, s.inventory_item_id, :cost_mthd_code), 4) std_cost
from mtl_system_items_b msib,
mtl_material_transactions mmt6,
(select gpb.organization_id,
gpb.inventory_item_id,
ood.organization_code org_code,
ood.organization_name inv_org_name,
nvl(sum(gpb.primary_quantity),0) transaction_quantity,
(select sum(mmt.primary_quantity)
from mtl_material_transactions mmt
where gpb.inventory_item_id  =mmt.inventory_item_id
and mmt.organization_id      =gpb.organization_id
and mmt.transaction_type_id in(18,61)
) primary_quantity
from
xle_entity_profiles xep,
org_organization_definitions ood,
mtl_parameters mp,
gmf_period_balances gpb,
org_acct_periods oap,
cm_whse_asc cwa,
cm_whse_src cws
where
1=1 and
xep.name =:legal_entity and
xep.legal_entity_id=ood.legal_entity and
ood.organization_id=mp.organization_id and
nvl(mp.eam_enabled_flag,'N')='N' and
ood.organization_id  =gpb.organization_id and
gpb.acct_period_id=oap.acct_period_id and
oap.organization_id=gpb.organization_id and
oap.period_name=:period_name and
oap.period_start_date >= cwa.eff_start_date (+) and
oap.period_start_date <= cwa.eff_end_date (+) and
gpb.organization_id = cwa.organization_id (+) and
gpb.inventory_item_id = cws.inventory_item_id (+) and
gpb.organization_id = cws.organization_id (+) and
exists
(select 1
from mtl_material_transactions mmt1
where gpb.inventory_item_id   =mmt1.inventory_item_id
and mmt1.organization_id      =gpb.organization_id
and mmt1.transaction_type_id in(18,61)
)
group by
gpb.organization_id,
gpb.inventory_item_id,
ood.organization_code,
ood.organization_name
) s
where msib.inventory_item_id  =s.inventory_item_id
and msib.organization_id      =s.organization_id
and msib.inventory_item_id    =mmt6.inventory_item_id
and msib.organization_id      =mmt6.organization_id
and mmt6.transaction_type_id in(18,61)
)