GMD MUV Detail

➜ Back to Library
Description
Categories: Draft

Download Blitz Report™ – World’s fastest reporting and data upload for Oracle EBS

Contact us to schedule a demo or if you need help with the installation

select distinct
item,
(select mp.organization_code from mtl_parameters mp where x.organization_id=mp.organization_id) organization_code,
item_cost,
formula_no,
formula_version,
--plan_qty,
actual_qty,
std_qty,
actual_qty-std_qty diff_qty,
item_cost *(actual_qty-std_qty) variance_value,
batch_no,
xxen_util.meaning(x.line_type,'LINE_TYPE',555),
--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) + 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)
  *(gmd.scrap_factor) std_qty,
  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,
  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,
  to_char(mmt.transaction_date,'MON-YYYY') transaction_date,
  (select transaction_type_name from mtl_transaction_types where transaction_type_id=mmt.transaction_type_id) mmt_transaction_type,
  gmd.organization_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
where 1=1
group by cmpnt.inventory_item_id,cmpnt.organization_id ) a
where 2=2
and gbh.batch_id=gmd.batch_id
and gmd.inventory_item_id=a.inventory_item_id
and a.organization_id = nvl((select cost_organization_id
                  from cm_whse_asc cws
                  where cws.organization_id=gmd.organization_id
                  and trunc(sysdate) between trunc(nvl(eff_start_date,sysdate-1)) and trunc(nvl(eff_end_date,sysdate-1)) ),gmd.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 mmt.transaction_type_id not in (43)
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=fmd.inventory_item_id and rownum<2) item,
a.item_cost item_cost,
ffm.formula_id,
gbh.batch_id,
ffm.formula_no formula_no,
ffm.formula_vers formula_version,
fmd.qty  plan_qty,
 0 actual_qty,
  round(nvl((select gmd1.actual_qty*(fmd.qty/fmd_prod.qty) from gme_material_details gmd1 where gmd1.batch_id=gbh.batch_id and gmd1.inventory_item_id=fmd_prod.inventory_item_id and gmd1.line_type=1),0),4) +round(nvl((select gmd1.actual_qty*(fmd.qty/fmd_prod.qty) from gme_material_details gmd1 where gmd1.batch_id=gbh.batch_id and gmd1.inventory_item_id=fmd_prod.inventory_item_id and gmd1.line_type=1),0),4) *fmd.scrap_factor
  ,0 wip_plan_qty,
  null dtl_um,
  null material_requirement_date,
  null subinventory,
  null locator_id,
  fmd.contribute_yield_ind contribute_yield_ind,
  fmd.scale_type scale_type,
  fmd.scrap_factor scrap_factor,
  fmd.line_type line_type,
  fmd.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,
 fmd.organization_id
from
gme_batch_header gbh,
fm_form_mst ffm,
fm_matl_dtl fmd,
   fm_matl_dtl fmd_prod,
(select sum(cmpnt_cost) item_cost,cmpnt.inventory_item_id,cmpnt.organization_id 
from  cm_cmpt_dtl cmpnt
where 1=1
group by cmpnt.inventory_item_id,cmpnt.organization_id ) a
where 
a.organization_id = nvl((select cost_organization_id
          from cm_whse_asc cws
          where cws.organization_id=fmd.organization_id
  and trunc(sysdate) between trunc(nvl(eff_start_date,sysdate-1)) and trunc(nvl(eff_end_date,sysdate-1)) ),fmd.organization_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
where 1=1 
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 ffm.formula_id= fmd.formula_id 
and ffm.formula_id=gbh.formula_id
and a.inventory_item_id=fmd.inventory_item_id
and fmd_prod.formula_id=ffm.formula_id
and fmd_prod.line_type=1
and fmd.line_type =- 1
and not exists(
   select 1 from gme_material_details gmd
   where  gmd.inventory_item_id=fmd.inventory_item_id
   and gmd.batch_id=gbh.batch_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) + 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)
  *(gmd.scrap_factor) std_qty,
  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.organization_id
from
gme_batch_header gbh,
gme_material_details gmd,
fm_form_mst ffm,
(select sum(cmpnt_cost) item_cost,cmpnt.inventory_item_id,cmpnt.organization_id 
from  cm_cmpt_dtl cmpnt
where 1=1
group by cmpnt.inventory_item_id,cmpnt.organization_id ) a
where gbh.batch_id     =gmd.batch_id
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
where 1=1
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 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
  )
  ) x
order by x.batch_no asc
Parameter Name SQL text Validation
Operating Unit 
mmt.organization_id in (
select
ood.organization_id
from
hr_all_organization_units_vl haouv,
org_organization_definitions ood
where
haouv.name=:operating_unit and
haouv.organization_id=ood.operating_unit
)
 LOV
Period 
(cmpnt.cost_type_id,cmpnt.period_id) in (
select
gps.cost_type_id,
gps.period_id
from
gmf_period_statuses gps
where
gps.period_code=:period and
gps.legal_entity_id=(select ood.legal_entity from hr_all_organization_units_vl haouv, org_organization_definitions ood where haouv.name=:operating_unit and haouv.organization_id=ood.operating_unit and rownum=1) and
gps.cost_type_id=1001 and
gps.calendar_code='NUF_CAL'
)
 LOV
Download
 
Related Blitz Reports
GME Batch Ticket GMF MUV Detail GME Material Usage Variance ECC Genealogy and Trace, Process Batch ECC Process Genealogy and Trace, Process Batch OPM WIP Value - V2 OPM2 GME Batch Yield Variance GME Material Usage and Substitution Variance
Blitz Report™

Blitz Report™ provides multiple benefits: