ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN B24CBF5AD89C6343E0530100007FDCC2 INV Organization Code (no access org restrictions and avoiding master and disabled orgs) Y select mp.organization_code value, haou.name description from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou where hoi.org_information_context = 'Accounting Information' and hoi.organization_id = mp.organization_id and hoi.organization_id = haou.organization_id and sysdate < nvl(haou.date_to, sysdate +1) and mp.organization_id <> mp.master_organization_id order by 1 US ACC10939A5B2565FE0530100007F728FY 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 US GME Material Usage Variance 1=1 2=2 3=3 1 10 1=1 gmf.period_code=:period_code LOV custom Y select gps.period_code value, gps.start_date||' - '||trunc(gps.end_date) description, gps.* from gmf_period_statuses gps where gps.calendar_code='NUF_CAL' and gps.legal_entity_id in (select ood.legal_entity from org_organization_definitions ood where ood.organization_code=:$flex$.organization_code) order by gps.start_date desc US Period 2 20 2=2 mmt.organization_id in (select mp.organization_id from mtl_parameters mp where mp.organization_code=:organization_code) LOV INV Organization Code (no access org restrictions and avoiding master and disabled orgs) B24CBF5AD89C6343E0530100007FDCC2 Y select mp.organization_code value, haou.name description from mtl_parameters mp, hr_organization_information hoi, hr_all_organization_units haou where hoi.org_information_context = 'Accounting Information' and hoi.organization_id = mp.organization_id and hoi.organization_id = haou.organization_id and sysdate < nvl(haou.date_to, sysdate +1) and mp.organization_id <> mp.master_organization_id order by 1 Y US Organization Code 3 3=3 ood.organization_code=:organization_code US Organization Code 4 30 2=2 mmt.transaction_date>=:trx_date_from Date US Transaction Date From 5 40 2=2 mmt.transaction_date<:trx_date_to+1 Date US Transaction Date To 6 50 2=2 batch_close_date>=:batch_close_date_from Date trunc(:$flex$.transaction_date_to,'month') US Batch Close Date From 7 60 2=2 batch_close_date<:batch_close_date_to Date :$flex$.transaction_date_to US Batch Close Date To