OPM Recipe Upload
Description
Categories: Enginatics, Upload
Repository: Github
Repository: Github
OPM Recipe Upload
=================
Create or update Oracle Process Manufacturing (OPM / GMD) Recipes and their Validity Rules via the public API GMD_RECIPE_HEADER / GMD_RECIPE_DETAIL.
Row model
=========
One spreadsheet row per recipe VALIDITY RULE. The recipe header columns (Organization Code, Recipe, Recipe Version, Description, Formula, Routing, Recipe Type, owner, header at ... more
=================
Create or update Oracle Process Manufacturing (OPM / GMD) Recipes and their Validity Rules via the public API GMD_RECIPE_HEADER / GMD_RECIPE_DETAIL.
Row model
=========
One spreadsheet row per recipe VALIDITY RULE. The recipe header columns (Organization Code, Recipe, Recipe Version, Description, Formula, Routing, Recipe Type, owner, header at ... more
select null action_, null status_, null message_, null modified_columns_, row_number() over (order by x.recipe, x.recipe_version, x.validity_rule_id) upload_row, x.organization_code, x.creation_organization_code, x.recipe, x.recipe_version, x.recipe_description, x.formula, x.formula_version, x.routing, x.routing_version, x.recipe_type, x.recipe_status, x.planned_process_loss, x.fixed_process_loss, x.fixed_process_loss_uom, x.recipe_yield, x.contiguous, x.enhanced_process_instructions, x.calculate_step_quantity, x.owner, x.validity_rule_use, x.validity_organization_code, x.product, x.vr_revision, x.preference, x.standard_quantity, x.minimum_quantity, x.maximum_quantity, x.vr_detail_uom, x.start_date, x.end_date, x.vr_planned_process_loss, x.vr_fixed_process_loss, x.vr_fixed_process_loss_uom, x.ha_attribute_category, x.ha_attribute1, x.ha_attribute2, x.ha_attribute3, x.ha_attribute4, x.ha_attribute5, x.ha_attribute6, x.ha_attribute7, x.ha_attribute8, x.ha_attribute9, x.ha_attribute10, x.ha_attribute11, x.ha_attribute12, x.ha_attribute13, x.ha_attribute14, x.ha_attribute15, x.ha_attribute16, x.ha_attribute17, x.ha_attribute18, x.ha_attribute19, x.ha_attribute20, x.ha_attribute21, x.ha_attribute22, x.ha_attribute23, x.ha_attribute24, x.ha_attribute25, x.ha_attribute26, x.ha_attribute27, x.ha_attribute28, x.ha_attribute29, x.ha_attribute30, x.va_attribute_category, x.va_attribute1, x.va_attribute2, x.va_attribute3, x.va_attribute4, x.va_attribute5, x.va_attribute6, x.va_attribute7, x.va_attribute8, x.va_attribute9, x.va_attribute10, x.va_attribute11, x.va_attribute12, x.va_attribute13, x.va_attribute14, x.va_attribute15, x.va_attribute16, x.va_attribute17, x.va_attribute18, x.va_attribute19, x.va_attribute20, x.va_attribute21, x.va_attribute22, x.va_attribute23, x.va_attribute24, x.va_attribute25, x.va_attribute26, x.va_attribute27, x.va_attribute28, x.va_attribute29, x.va_attribute30, x.recipe_id, x.validity_rule_id from ( select (select mp.organization_code from mtl_parameters mp where mp.organization_id=grcv.owner_organization_id) organization_code, (select mp.organization_code from mtl_parameters mp where mp.organization_id=grcv.creation_organization_id) creation_organization_code, grcv.recipe_no recipe, grcv.recipe_version, grcv.recipe_description, (select ffmb.formula_no from fm_form_mst_b ffmb where ffmb.formula_id=grcv.formula_id) formula, (select ffmb.formula_vers from fm_form_mst_b ffmb where ffmb.formula_id=grcv.formula_id) formula_version, (select grb.routing_no from gmd_routings_b grb where grb.routing_id=grcv.routing_id) routing, (select grb.routing_vers from gmd_routings_b grb where grb.routing_id=grcv.routing_id) routing_version, xxen_util.meaning(to_char(grcv.recipe_type),'GMD_RECIPE_TYPE',552) recipe_type, (select gsv.meaning from gmd_status_vl gsv where gsv.status_code=grcv.recipe_status) recipe_status, grcv.planned_process_loss, grcv.fixed_process_loss, grcv.fixed_process_loss_uom, grcv.recipe_yield, xxen_util.meaning(decode(grcv.contiguous_ind,1,'Y','N'),'YES_NO',0) contiguous, xxen_util.meaning(grcv.enhanced_pi_ind,'YES_NO',552) enhanced_process_instructions, xxen_util.meaning(decode(grcv.calculate_step_quantity,1,'Y','N'),'YES_NO',0) calculate_step_quantity, (select fu.user_name from fnd_user fu where fu.user_id=grcv.owner_id) owner, xxen_util.meaning(grvr.recipe_use,'GMD_FORMULA_USE',552) validity_rule_use, (select mp.organization_code from mtl_parameters mp where mp.organization_id=grvr.organization_id) validity_organization_code, (select msiv.concatenated_segments from mtl_system_items_vl msiv where msiv.inventory_item_id=grvr.inventory_item_id and msiv.organization_id=grcv.owner_organization_id) product, grvr.revision vr_revision, grvr.preference, grvr.std_qty standard_quantity, grvr.min_qty minimum_quantity, grvr.max_qty maximum_quantity, grvr.detail_uom vr_detail_uom, grvr.start_date, grvr.end_date, grvr.planned_process_loss vr_planned_process_loss, grvr.fixed_process_loss vr_fixed_process_loss, grvr.fixed_process_loss_uom vr_fixed_process_loss_uom, xxen_util.display_flexfield_context(552,'GMD_RECIPES_FLEX',grcv.attribute_category) ha_attribute_category, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE1',grcv.rowid,grcv.attribute1) ha_attribute1, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE2',grcv.rowid,grcv.attribute2) ha_attribute2, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE3',grcv.rowid,grcv.attribute3) ha_attribute3, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE4',grcv.rowid,grcv.attribute4) ha_attribute4, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE5',grcv.rowid,grcv.attribute5) ha_attribute5, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE6',grcv.rowid,grcv.attribute6) ha_attribute6, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE7',grcv.rowid,grcv.attribute7) ha_attribute7, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE8',grcv.rowid,grcv.attribute8) ha_attribute8, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE9',grcv.rowid,grcv.attribute9) ha_attribute9, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE10',grcv.rowid,grcv.attribute10) ha_attribute10, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE11',grcv.rowid,grcv.attribute11) ha_attribute11, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE12',grcv.rowid,grcv.attribute12) ha_attribute12, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE13',grcv.rowid,grcv.attribute13) ha_attribute13, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE14',grcv.rowid,grcv.attribute14) ha_attribute14, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE15',grcv.rowid,grcv.attribute15) ha_attribute15, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE16',grcv.rowid,grcv.attribute16) ha_attribute16, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE17',grcv.rowid,grcv.attribute17) ha_attribute17, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE18',grcv.rowid,grcv.attribute18) ha_attribute18, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE19',grcv.rowid,grcv.attribute19) ha_attribute19, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE20',grcv.rowid,grcv.attribute20) ha_attribute20, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE21',grcv.rowid,grcv.attribute21) ha_attribute21, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE22',grcv.rowid,grcv.attribute22) ha_attribute22, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE23',grcv.rowid,grcv.attribute23) ha_attribute23, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE24',grcv.rowid,grcv.attribute24) ha_attribute24, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE25',grcv.rowid,grcv.attribute25) ha_attribute25, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE26',grcv.rowid,grcv.attribute26) ha_attribute26, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE27',grcv.rowid,grcv.attribute27) ha_attribute27, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE28',grcv.rowid,grcv.attribute28) ha_attribute28, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE29',grcv.rowid,grcv.attribute29) ha_attribute29, xxen_util.display_flexfield_value(552,'GMD_RECIPES_FLEX',grcv.attribute_category,'ATTRIBUTE30',grcv.rowid,grcv.attribute30) ha_attribute30, xxen_util.display_flexfield_context(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category) va_attribute_category, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE1',grvr.rowid,grvr.attribute1) va_attribute1, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE2',grvr.rowid,grvr.attribute2) va_attribute2, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE3',grvr.rowid,grvr.attribute3) va_attribute3, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE4',grvr.rowid,grvr.attribute4) va_attribute4, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE5',grvr.rowid,grvr.attribute5) va_attribute5, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE6',grvr.rowid,grvr.attribute6) va_attribute6, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE7',grvr.rowid,grvr.attribute7) va_attribute7, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE8',grvr.rowid,grvr.attribute8) va_attribute8, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE9',grvr.rowid,grvr.attribute9) va_attribute9, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE10',grvr.rowid,grvr.attribute10) va_attribute10, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE11',grvr.rowid,grvr.attribute11) va_attribute11, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE12',grvr.rowid,grvr.attribute12) va_attribute12, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE13',grvr.rowid,grvr.attribute13) va_attribute13, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE14',grvr.rowid,grvr.attribute14) va_attribute14, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE15',grvr.rowid,grvr.attribute15) va_attribute15, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE16',grvr.rowid,grvr.attribute16) va_attribute16, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE17',grvr.rowid,grvr.attribute17) va_attribute17, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE18',grvr.rowid,grvr.attribute18) va_attribute18, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE19',grvr.rowid,grvr.attribute19) va_attribute19, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE20',grvr.rowid,grvr.attribute20) va_attribute20, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE21',grvr.rowid,grvr.attribute21) va_attribute21, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE22',grvr.rowid,grvr.attribute22) va_attribute22, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE23',grvr.rowid,grvr.attribute23) va_attribute23, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE24',grvr.rowid,grvr.attribute24) va_attribute24, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE25',grvr.rowid,grvr.attribute25) va_attribute25, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE26',grvr.rowid,grvr.attribute26) va_attribute26, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE27',grvr.rowid,grvr.attribute27) va_attribute27, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE28',grvr.rowid,grvr.attribute28) va_attribute28, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE29',grvr.rowid,grvr.attribute29) va_attribute29, xxen_util.display_flexfield_value(552,'GMD_RECIPE_VALIDITY_RULES_FLEX',grvr.attribute_category,'ATTRIBUTE30',grvr.rowid,grvr.attribute30) va_attribute30, grcv.recipe_id, grvr.recipe_validity_rule_id validity_rule_id from gmd_recipes_vl grcv, gmd_recipe_validity_rules grvr, mtl_parameters mp where grcv.recipe_id=grvr.recipe_id and grcv.owner_organization_id=mp.organization_id and grcv.delete_mark=0 and nvl(grvr.delete_mark,0)=0 and grcv.owner_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 :p_upload_mode like '%'||xxen_upload.action_update ) x where 1=1 |
| Parameter Name | SQL text | Validation | |
|---|---|---|---|
| Upload Mode |
| LOV | |
| Organization Code |
| LOV | |
| Recipe |
| Char |