GMF MUV Detail

➜ Back to Library
Description
Categories: Draft
Run GMF MUV Detail and other Oracle EBS reports with Blitz Report™ on our demo environment
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'
)
 Char
Download
Related Blitz Reports
GMD MUV Detail GME Batch Ticket GME Material Usage Variance