GME Material Usage Variance

select
item,
--item_cost,
--formula_id,
--batch_id,
formula_no,
formula_version,
plan_qty,
actual_qty,
std_qty,
actual_qty-std_qty diff_qty,
item_cost *(actual_qty-std_qty) variance_value,
wip_plan_qty,
dtl_um,
material_requirement_date,
subinventory,
locator_id,
contribute_yield_ind,
scale_type,
scrap_factor,
line_type,
contribute_step_qty_ind,
batch_no,
batch_status,
actual_start_date,
actual_completion_date,
batch_close_date,
plan_start_date,
plan_cmplt_date,
transaction_date,
mmt_transaction_type
from (
select distinct (select segment1 from mtl_system_items_b msib where msib.inventory_item_id=gmd.inventory_item_id
and rownum<2) item,
a.item_cost,
ffm.formula_id,
gbh.batch_id,
ffm.formula_no formula_no,
ffm.formula_vers formula_version,
  gmd.plan_qty plan_qty,
  gmd.actual_qty actual_qty,
  round(nvl((select (select gmd.actual_qty/fmd.qty from fm_matl_dtl fmd, gme_material_details gmd where fmd.formulaline_id=gmd.formulaline_id and fmd.formula_id=ffm.formula_id and fmd.line_type=1 and gmd.batch_id=gbh.batch_id) * qty std_qty from fm_matl_dtl where formula_id=ffm.formula_id and formulaline_id=gmd.formulaline_id),0),4) std_qty,
--  gmd.actual_qty-std_qty diff_qty,
--  round(gmd.actual_qty-(select (select gmd.actual_qty/fmd.qty from fm_matl_dtl fmd, gme_material_details gmd where fmd.formulaline_id=gmd.formulaline_id and fmd.formula_id=ffm.formula_id and fmd.line_type=1 and gmd.batch_id=gbh.batch_id) * qty std_qty from fm_matl_dtl where formula_id=ffm.formula_id and formulaline_id=gmd.formulaline_id),4) diff_qty,
--  round(a.item_cost*(gmd.actual_qty-(select (select gmd.actual_qty/fmd.qty from fm_matl_dtl fmd, gme_material_details gmd where fmd.formulaline_id=gmd.formulaline_id and fmd.formula_id=ffm.formula_id and fmd.line_type=1 and gmd.batch_id=gbh.batch_id) * qty std_qty from fm_matl_dtl where formula_id=ffm.formula_id and formulaline_id=gmd.formulaline_id)),4) variance_value,
  gmd.wip_plan_qty wip_plan_qty,
  gmd.dtl_um dtl_um,
  gmd.material_requirement_date material_requirement_date,
  gmd.subinventory subinventory,
  gmd.locator_id locator_id,
  gmd.contribute_yield_ind contribute_yield_ind,
  gmd.scale_type scale_type,
  gmd.scrap_factor scrap_factor,
  gmd.line_type line_type,
  gmd.contribute_step_qty_ind contribute_step_qty_ind,
  gbh.batch_no batch_no,
  gbh.batch_status batch_status,
  gbh.actual_start_date actual_start_date,
  gbh.actual_cmplt_date actual_completion_date,
  gbh.batch_close_date,
  gbh.plan_start_date plan_start_date,
  gbh.plan_cmplt_date plan_cmplt_date,
  mmt.transaction_date transaction_date,
  (select transaction_type_name from mtl_transaction_types
where transaction_type_id=mmt.transaction_type_id) mmt_transaction_type
    --mmt.*
  --a.item_cost
from gme_batch_header gbh,
  gme_material_details gmd,
  fm_form_mst ffm,
  mtl_material_transactions mmt,
(select sum(cmpnt_cost) item_cost,cmpnt.inventory_item_id,cmpnt.organization_id
from  cm_cmpt_dtl cmpnt, gmf_period_statuses gmf
where 1=1
--and mmt.inventory_item_id=cmpnt.inventory_item_id
--and mmt.organization_id=cmpnt.organization_id
and cmpnt.cost_type_id=1001
and gmf.cost_type_id=cmpnt.cost_type_id
and gmf.period_id=cmpnt.period_id
and gmf.calendar_code='NUF_CAL'
--and gmf.legal_entity_id = (select legal_entity from org_organization_definitions where organization_id=89)
group by cmpnt.inventory_item_id,cmpnt.organization_id ) a
where 2=2
and gbh.batch_id=gmd.batch_id
--and ffm.formula_no='518-110004043'
and gmd.inventory_item_id=a.inventory_item_id
and gmd.organization_id=a.organization_id
and gbh.organization_id=gmd.organization_id
and gbh.formula_id=ffm.formula_id
and mmt.transaction_source_id (+)= gbh.batch_id
and mmt.trx_source_line_id (+)= gmd.material_detail_id
--and gbh.batch_no='518010138'
and mmt.inventory_item_id=a.inventory_item_id
union
select distinct (select segment1 from mtl_system_items_b msib where msib.inventory_item_id=gmd.inventory_item_id
and rownum<2) item,
a.item_cost,
ffm.formula_id,
gbh.batch_id,
ffm.formula_no formula_no,
ffm.formula_vers formula_version,
  gmd.plan_qty plan_qty,
  gmd.actual_qty actual_qty,
  round(nvl((select (select gmd.actual_qty/fmd.qty from fm_matl_dtl fmd, gme_material_details gmd where fmd.formulaline_id=gmd.formulaline_id and fmd.formula_id=ffm.formula_id and fmd.line_type=1 and gmd.batch_id=gbh.batch_id) * qty std_qty from fm_matl_dtl where formula_id=ffm.formula_id and formulaline_id=gmd.formulaline_id),0),4) std_qty,
--  gmd.actual_qty-std_qty diff_qty,
--  round(gmd.actual_qty-(select (select gmd.actual_qty/fmd.qty from fm_matl_dtl fmd, gme_material_details gmd where fmd.formulaline_id=gmd.formulaline_id and fmd.formula_id=ffm.formula_id and fmd.line_type=1 and gmd.batch_id=gbh.batch_id) * qty std_qty from fm_matl_dtl where formula_id=ffm.formula_id and formulaline_id=gmd.formulaline_id),4) diff_qty,
--  round(a.item_cost*(gmd.actual_qty-(select (select gmd.actual_qty/fmd.qty from fm_matl_dtl fmd, gme_material_details gmd where fmd.formulaline_id=gmd.formulaline_id and fmd.formula_id=ffm.formula_id and fmd.line_type=1 and gmd.batch_id=gbh.batch_id) * qty std_qty from fm_matl_dtl where formula_id=ffm.formula_id and formulaline_id=gmd.formulaline_id)),4) variance_value,
  gmd.wip_plan_qty wip_plan_qty,
  gmd.dtl_um dtl_um,
  gmd.material_requirement_date material_requirement_date,
  gmd.subinventory subinventory,
  gmd.locator_id locator_id,
  gmd.contribute_yield_ind contribute_yield_ind,
  gmd.scale_type scale_type,
  gmd.scrap_factor scrap_factor,
  gmd.line_type line_type,
  gmd.contribute_step_qty_ind contribute_step_qty_ind,
  gbh.batch_no batch_no,
  gbh.batch_status batch_status,
  gbh.actual_start_date actual_start_date,
  gbh.actual_cmplt_date actual_completion_date,
  gbh.batch_close_date,
  gbh.plan_start_date plan_start_date,
  gbh.plan_cmplt_date plan_cmplt_date,
  null transaction_date,
  null mmt_transaction_type
-- gmd.*--mmt.*
  --a.item_cost
from gme_batch_header gbh,
gme_material_details gmd,
fm_form_mst ffm,
    --  mtl_material_transactions mmt,
(select sum(cmpnt_cost) item_cost,cmpnt.inventory_item_id,cmpnt.organization_id
from  cm_cmpt_dtl cmpnt, gmf_period_statuses gmf
where 1=1
--and mmt.inventory_item_id=cmpnt.inventory_item_id
--and mmt.organization_id=cmpnt.organization_id
and cmpnt.cost_type_id=1001
and gmf.cost_type_id=cmpnt.cost_type_id
and gmf.period_id=cmpnt.period_id
and gmf.calendar_code='NUF_CAL'
--and gmd.actual_qty=0
--and gmf.legal_entity_id = (select legal_entity from org_organization_definitions where organization_id=89)
group by cmpnt.inventory_item_id,cmpnt.organization_id ) a
where gbh.batch_id     =gmd.batch_id
--and ffm.formula_no='518-110004043'
and gmd.inventory_item_id=a.inventory_item_id
and gmd.organization_id=a.organization_id
and gbh.organization_id=gmd.organization_id
and gbh.formula_id     =ffm.formula_id
and gbh.batch_id in (select distinct gbh.batch_id
from gme_batch_header gbh,
  gme_material_details gmd,
  fm_form_mst ffm,
  mtl_material_transactions mmt,
(select sum(cmpnt_cost) item_cost,cmpnt.inventory_item_id,cmpnt.organization_id
from  cm_cmpt_dtl cmpnt, gmf_period_statuses gmf
where 1=1
and cmpnt.cost_type_id=1001
and gmf.cost_type_id=cmpnt.cost_type_id
and gmf.period_id=cmpnt.period_id
and gmf.calendar_code='NUF_CAL'
and gmf.legal_entity_id = (select ood.legal_entity from org_organization_definitions ood where 3=3)
group by cmpnt.inventory_item_id,cmpnt.organization_id ) a
where 2=2
and gbh.batch_id=gmd.batch_id
and gbh.organization_id=gmd.organization_id
and gbh.formula_id=ffm.formula_id
and mmt.transaction_source_id(+)= gbh.batch_id
and mmt.trx_source_line_id(+)= gmd.material_detail_id
and mmt.inventory_item_id=a.inventory_item_id)
--and gbh.batch_no='518010138'
--and mmt.inventory_item_id=a.inventory_item_id
and not exists(
   select 1 from mtl_material_transactions mmt
   where mmt.inventory_item_id=gmd.inventory_item_id
   and mmt.organization_id=gmd.organization_id
and mmt.transaction_source_id= gbh.batch_id
and mmt.trx_source_line_id= gmd.material_detail_id
  --and ((trunc(nvl(mmt.transaction_date, to_date('01/01/1951','dd/mm/yyyy'))) between to_date('01/06/2020','dd/mm/yyyy')  and to_date('30/06/2020','dd/mm/yyyy')))
  )) b
  order by batch_no asc