GMF MUV Detail
Description
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 |
|
LOV | |
Period |
|
Char |