Inventory Valuation Report

Opened the new report builder. and have taken inventory_item_id,

segment1,

description,

item_type,

creation_date columns

from mtl_system_items_b

and for transacations i have taken mtl_material_transactions....

common column for these two tables is inventory_item_id.

by using this I have built a query in data model.

And I also used some formula columns in this report for from_date{select trunc(add_months( sysdate , - 12)) into l_date},

to_date{ select trunc(sysdate) into l_date},

beginning onhand_quantity{SELECT NVL(SUM (transaction_quantity),0) INTO  lv_beg_on_hand_qty from mtl_material_transactions }

sales_quantity{select NVL(sum(transaction_quantity),0)* -1 into lv_sales_qty from mtl_material_transactions},

average_cost{SELECT nvl(round(item_cost,2),0)

                        INTO lv_item_cost

                        FROM cst_item_costs}

,onhand_quantity{select NVL(sum(PRIMARY_TRANSACTION_QUANTITY),0) INTO lv_trans_qty

from mtl_onhand_quantities_detail},

purchase_quantity{select NVL(sum(transaction_quantity),0) into l_purchase_qty

   from mtl_material_transactions.}

inventory_valuation{ v_inv_valuation := NVL(:CF_ON_HAND_QTY,0) * (NVL(:CF_AVG_COST,0) )}

and used to summary column for inventory_valuation formula column.

and in the layout model, taken one main frame, one repeating frame and assigned source to that, and fields I have placed in that repeating frame and assigned source to that repeating frames respectively., and text fields also added in the main frame based on the requirement..

after doing all these changes in the data model as well as in layout model, saved that report and compiled it and have run the report.