ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDECA79D2E0530100007F1FF2 INV Category Set Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name US 53860F993F697167E0630100007F1025 OPM Item Number Y Y select distinct msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv, mtl_parameters mp where msiv.organization_id=mp.organization_id and mp.process_enabled_flag='Y' and (xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' or (:$flex$.organization_code is null and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id))) order by msiv.concatenated_segments US Process-manufacturing-enabled items in the selected (or accessible) organizations 53860F993F687167E0630100007F1025 OPM Organization Code Y select ood.organization_code value, ood.organization_name description from org_organization_definitions ood, mtl_parameters mp where ood.organization_id=mp.organization_id and mp.process_enabled_flag='Y' and mp.organization_id<>mp.master_organization_id and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and nvl(ood.disable_date,sysdate)>=sysdate order by ood.organization_code US Process-manufacturing-enabled inventory organizations the responsibility can access (org_access_view secured) 1A5739C5DE270375E0630100007F5142 OPM Status Code Y select gsv.status_code value, gsv.description description from gmd_status_vl gsv order by to_number(gsv.status_code) US F7DCF5975D094268E0538638C80A5DC3Y select ood.organization_code, msiv.segment1, msiv.inventory_item_status_code, msiv.description, msiv.weight_uom_code, cat.ca_code, grv.recipe_no, grv.recipe_description, grv.recipe_version, gsv_r.meaning recipe_status, frh.routing_no, grvr.preference validity_rule_preference, grvr.start_date validity_rule_from_date, gsv_v.meaning validity_rule_status, grvr.std_qty validity_rule_qty, grvr.detail_uom validity_rule_uom, ff.product_qty, ff.product_uom, aa.step_no, aa.step_qty, aa.step_qty_uom, aa.resource_code, aa.proc_qty, aa.proc_uom, aa.res_usage, aa.res_uom from gmd_recipes_vl grv, org_organization_definitions ood, gmd_recipe_validity_rules grvr, mtl_system_items_vl msiv, gmd_status_vl gsv_r, gmd_status_vl gsv_v, fm_rout_hdr frh, ( select mic.organization_id, mic.inventory_item_id, mcbk.concatenated_segments ca_code from mtl_item_categories mic, mtl_categories_b_kfv mcbk, mtl_category_sets_vl mcsv where mic.category_id=mcbk.category_id and mic.category_set_id=mcsv.category_set_id and mcsv.category_set_name=:p_category_set ) cat, ( select fmd.formula_id, fmd.qty product_qty, fmd.detail_uom product_uom from fm_matl_dtl fmd where fmd.line_type=1 group by fmd.formula_id, fmd.qty, fmd.detail_uom ) ff, ( select frd.routing_id, frd.routingstep_no step_no, frd.step_qty, gov.process_qty_uom step_qty_uom, gor.resources resource_code, gor.process_qty proc_qty, gor.resource_process_uom proc_uom, gor.resource_usage res_usage, gor.resource_usage_uom res_uom from gmd_operation_resources gor, gmd_operation_activities goa, gmd_operations_vl gov, fm_rout_dtl frd where gov.oprn_id=frd.oprn_id and gov.oprn_id=goa.oprn_id and gor.oprn_line_id=goa.oprn_line_id ) aa where 1=1 and ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and grv.owner_organization_id=ood.organization_id and grv.recipe_id=grvr.recipe_id and grvr.organization_id=ood.organization_id and msiv.inventory_item_id=grvr.inventory_item_id and msiv.organization_id=grvr.organization_id and gsv_r.status_code(+)=grv.recipe_status and gsv_v.status_code(+)=grvr.validity_rule_status and grv.routing_id=frh.routing_id(+) and grv.formula_id=ff.formula_id(+) and grv.routing_id=aa.routing_id(+) and grvr.organization_id=cat.organization_id(+) and grvr.inventory_item_id=cat.inventory_item_id(+) and grv.recipe_status<>'1000' order by ood.organization_code, grv.recipe_no Generalised Nufarm recipe data-quality report for the standard library: lowercase/per-line SQL formatting and qualified table aliases, mtl_system_items_vl (msiv) and _vl views, org_access_view inventory-org security, optional Organization Code / Recipe / Item / Recipe Status / Category Set parameters, the Nufarm-specific Formulation Code category replaced by an optional outer-joined Category Set parameter, and a Resource Code column added so each operation resource is a distinct row (eliminating the duplicate rows that previously required a group by / distinct). US OPM Recipe Details OPM (Process Manufacturing) recipe details combining recipe status, validity rules, preferences, formula product quantity and routing step / resource quantities. Supports data cleaning before cost roll up. Optionally shows an item category code via the Category Set parameter. Enginatics 1=1 :p_category_set 1 10 1=1 ood.organization_code=:p_org_code LOV OPM Organization Code 53860F993F687167E0630100007F1025 Y select ood.organization_code value, ood.organization_name description from org_organization_definitions ood, mtl_parameters mp where ood.organization_id=mp.organization_id and mp.process_enabled_flag='Y' and mp.organization_id<>mp.master_organization_id and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) and nvl(ood.disable_date,sysdate)>=sysdate order by ood.organization_code US Organization Code 2 20 1=1 grv.recipe_no=:p_recipe_no LOV custom Y select distinct grv.recipe_no value, grv.recipe_description description from gmd_recipes_vl grv, org_organization_definitions ood where grv.owner_organization_id=ood.organization_id and grv.recipe_status<>'1000' and (:$flex$.organization_code is null or xxen_util.contains(:$flex$.organization_code,ood.organization_code)='Y') and ood.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id) order by grv.recipe_no US Recipe 3 30 1=1 msiv.concatenated_segments=:p_item LOV OPM Item Number 53860F993F697167E0630100007F1025 Y Y select distinct msiv.concatenated_segments value, msiv.description from mtl_system_items_vl msiv, mtl_parameters mp where msiv.organization_id=mp.organization_id and mp.process_enabled_flag='Y' and (xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' or (:$flex$.organization_code is null and mp.organization_id in (select oav.organization_id from org_access_view oav where oav.resp_application_id=fnd_global.resp_appl_id and oav.responsibility_id=fnd_global.resp_id))) order by msiv.concatenated_segments US Item 4 40 1=1 grv.recipe_status=:p_recipe_status LOV OPM Status Code 1A5739C5DE270375E0630100007F5142 Y select gsv.status_code value, gsv.description description from gmd_status_vl gsv order by to_number(gsv.status_code) US Recipe Status 5 50 :p_category_set LOV INV Category Set 8E2FF36EDECA79D2E0530100007F1FF2 Y select distinct mcsv.category_set_name value, mcsv.description from mtl_category_sets_vl mcsv where :$flex$.organization_code is null or mcsv.category_set_id in (select mic.category_set_id from mtl_parameters mp, mtl_item_categories mic where xxen_util.contains(:$flex$.organization_code,mp.organization_code)='Y' and mp.organization_id=mic.organization_id) order by mcsv.category_set_name US Category Set