AVG COST Calculation Report

Period Code	Item Code	Prior Period Balance	Prior Period Cost	Current Period TRX Qty	Current Period Value	Avg Cost

Hi All ,

I am trying to design a report like this , I want to show how the avg cost is driven during the period with these basic information , for just one line for each Item.

This is what i wrote so far .

/* Formatted on 05/Mar/17 1:43:37 PM (QP5 v5.215.12089.38647) */  SELECT COST_Details.calendar_code,         COST_Details.period_code,         COST_Details.period_id,         COST_Details.item_no,         COST_Details.orgn_code,         COST_Details.item_desc,         COST_Details.uom,         COST_Details.inventory_item_id,         COST_Details.cost_cmpntcls_id,         cost_details.cost_cmpntcls_code,         SUM (COST_Details.trx_Value) trx_value,         /*   -- to get details         COST_Details.trx_Qty trx_qty,             CASE                WHEN SUM (COST_Details.trx_Qty) != 0                THEN                   NVL (                      (SUM (COST_Details.trx_Value) / (COST_Details.trx_Qty)),                      0)                ELSE                   0             END                unit_cost ,*/         cost_details.period_trans_qty,         cost_details.period_perp_qty,         GMF_CMCOMMON.get_cmpt_cost (COST_Details.inventory_item_id,                                     COST_Details.organization_id,                                     cost_details.end_date,                                     1000,                                     0)            AVG_cost_in_period,         COST_Details.Cost_Origin    FROM (SELECT gps.calendar_code,                 gps.period_code,                 cmd.period_id,                 iim.item_number item_no,                 iim.inventory_item_id,                 mp.organization_code orgn_code,                 iim.organization_id,                 iim.description item_desc,                 DECODE (cmd.cost_level,                         0, 'This Level Cost',                         1, 'Lower Level',                         'None')                    Cost_level,                 cmm.cost_cmpntcls_id,                 cmm.cost_cmpntcls_code,                 cmm.cost_cmpntcls_desc component_desc,                 cmd.cost_analysis_code analysis_code,                 cmd.cmpnt_cost component_cost,                 iim.primary_uom_code uom,                 cmm.sort_sequence sequence,                 ccd.source_ind,                 DECODE (ccd.SOURCE_IND,                         0, 'Batch