GME FIFO Report
Description
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) ) |
Parameter Name | SQL text | Validation | |
---|---|---|---|
Organization Code |
|
LOV | |
Period |
|
LOV | |
Legal Entity |
|
LOV | |
Cost Method |
|
LOV |