[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
** deletes all rows for the item in the tables GR_ITEM_CONCENTRATIONS and
** GR_ITEM_CONC_DETAILS.
**
** The formula effectivity table is read using the item code passed in. The
** first search is for an effective regulatory formula. If this does not exist an
** effective production formula is looked for. If this does not exist, the program exits
** and reports an effectivity error. NOTE: Planning and costing formulas are ignored.
**
** The top level of ingredients for the effective formula are read, converted to the
** system unit of measure based on the primary unit of measure in for the UOM class
** defined in the profile FM_YIELD_TYPE and accumulated to give a theoretical yield for
** the formula.
**
** Once the theoretical yield is calculated an iterative process is started.
** The formula is read again, calculating the % of the ingredient against the
** theoretical yield. If the ingredient is an intermediate, or is an ingredient with a
** stand alone explosion, the item is stored for later processing.
** As the % concentration of each ingredient is calculated, the % is accumulated in the
** table GR_ITEM_CONCENTRATIONS and a separate row is written to GR_ITEM_CONC_DETAILS
** for each individual formula detail line that adds into GR_ITEM_CONCENTRATIONS.
**
*/
PROCEDURE OPM_410_MSDS_Formula
(p_commit IN VARCHAR2,
p_init_msg_list IN VARCHAR2,
p_validation_level IN NUMBER,
p_api_version IN NUMBER,
p_organization_id IN NUMBER,
p_inventory_item_id IN NUMBER,
p_session_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
IS
/*
** Datastructures
*/
/*L_EXPLOSION_LIST GR_EXPLOSIONS.t_explosion_list;*/
CONCENTRATION_DELETE_ERROR EXCEPTION;
SELECT rvr.recipe_use,
rvr.inventory_item_id,
mst.formula_id,
mst.formula_no,
mst.formula_vers,
rcp.recipe_no,
rcp.recipe_version,
dtl.qty,
dtl.detail_uom item_um, --dtl.item_um
dtl.SCALE_ROUNDING_VARIANCE precision
FROM fm_form_mst_b mst,
gmd_recipe_validity_rules rvr,
gmd_status_b sts,
gmd_recipes_b rcp,
fm_matl_dtl dtl
WHERE rvr.validity_rule_status = sts.status_code
AND sts.status_type in ('400','700','900')
AND rvr.recipe_id = rcp.recipe_id
AND mst.formula_id = rcp.formula_id
AND dtl.formula_id = rcp.formula_id
AND (rvr.end_date IS NULL OR rvr.end_date >= g_current_date)
AND rvr.start_date <= g_current_date
AND rvr.recipe_use = l_formula_use
AND (rvr.organization_id IS NULL OR rvr.organization_id = l_organization_id) --rvr.orgn_code = l_organization_id)
AND dtl.line_type = 1
AND dtl.inventory_item_id = l_inventory_item_id
AND rvr.delete_mark = 0
AND mst.delete_mark = 0
AND rvr.inventory_item_id = l_inventory_item_id
ORDER BY rvr.organization_id asc, rvr.preference asc, sts.status_type desc;
SELECT dtl.inventory_item_id,
dtl.qty,
dtl.detail_uom item_um,
mtl.concatenated_segments item_no,
dtl.SCALE_ROUNDING_VARIANCE precision,
dtl.contribute_yield_ind
FROM mtl_system_items_kfv mtl,
fm_matl_dtl dtl
WHERE dtl.formula_id = EffectiveFormulaRecord.formula_id
AND dtl.line_type = -1
--AND dtl.organization_id = mtl.organization_id - Bug 5229785 do not join with detail organization since
AND mtl.organization_id = l_organization_id
AND dtl.inventory_item_id = mtl.inventory_item_id;
SELECT ig1.inventory_item_id,
ig1.ingredient_flag,
ig1.explode_ingredient_flag,
ig1.actual_hazard
FROM gr_item_explosion_properties ig1
WHERE ig1.inventory_item_id = l_inventory_item_id
AND ig1.organization_id = l_organization_id;
SELECT ut.uom_code
FROM mtl_uom_classes_vl uc, mtl_units_of_measure_tl ut
WHERE ut.uom_class = l_system_uom_type
AND ut.uom_class = uc.uom_class;
SELECT product_item_id, ingredient_item_id, concentration_percentage
FROM gr_ingredient_concentrations
WHERE organization_id = l_organization_id
AND product_item_id = l_inventory_item_id;
l_ingredient_list.delete;
GR_INGRED_CONC_DETAILS_PKG.Delete_Rows
(p_commit,
l_called_by_form,
p_organization_id,
p_inventory_item_id,
l_return_status,
l_oracle_error,
l_msg_data);
RAISE Concentration_Delete_Error;
GR_INGRED_CONCENTRATIONS_PKG.Delete_Rows
(p_commit,
l_called_by_form,
p_organization_id,
p_inventory_item_id,
l_return_status,
l_oracle_error,
l_msg_data);
RAISE Concentration_Delete_Error;
** add to the work array, otherwise update the item concentration
** tables.
*/
/* M. Grosser 07-Mar-2002 BUG 1323951 - During testing found that code went into a loop if item is
not marked as an ingredient yet formula source says no formula. Modified
code to treat this item as an ingredient.
*/
IF ((ItemSafetyRecord.ingredient_flag = 'N') OR
(ItemSafetyRecord.ingredient_flag = 'Y' AND
ItemSafetyRecord.explode_ingredient_flag = 'Y')) AND
(NOT check_circular_reference(p_organization_id, l_inventory_item_id, EffectiveFormulaRecord.formula_id, l_maximum_record)) THEN
l_code_block := ' Updating explosion plsql table';
WHEN Concentration_Delete_Error THEN
x_return_status := FND_API.G_RET_STS_ERROR;
SELECT mst.formula_id
FROM fm_form_mst_b mst,
gmd_recipe_validity_rules rvr,
gmd_status_b sts,
gmd_recipes_b rcp,
fm_matl_dtl dtl,
mtl_system_items mtl
WHERE rvr.inventory_item_id = mtl.inventory_item_id
AND rvr.validity_rule_status = sts.status_code
AND sts.status_type in ('400','700','900')
AND rvr.recipe_id = rcp.recipe_id
AND mst.formula_id = rcp.formula_id
AND dtl.formula_id = rcp.formula_id
AND (rvr.end_date IS NULL OR rvr.end_date >= g_current_date)
AND rvr.start_date <= g_current_date
AND rvr.recipe_use = l_formula_use
AND (rvr.organization_id IS NULL OR rvr.organization_id = p_organization_id)
AND dtl.line_type = 1
AND dtl.inventory_item_id = mtl.inventory_item_id
AND mtl.inventory_item_id = p_inventory_item_id
AND rvr.delete_mark = 0
AND mst.delete_mark = 0
ORDER BY rvr.organization_id asc, rvr.preference asc, sts.status_type desc;
** This procedure is used to insert or update the
** rows in the concentration tables.
**
*/
PROCEDURE process_concentrations
(p_organization_id NUMBER,
p_inventory_item_id IN NUMBER,
p_explosion_item_id IN NUMBER,
p_source_item_id IN NUMBER,
p_item_percent IN NUMBER,
p_current_record IN NUMBER,
p_item_um IN VARCHAR2,
x_msg_count IN OUT NOCOPY NUMBER,
x_msg_data IN OUT NOCOPY VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2)
IS
/*
** Alpha Variables
*/
L_ROWID VARCHAR2(18);
CONCENTRATION_INSERT_ERROR EXCEPTION;
CONCENTRATION_DELETE_ERROR EXCEPTION;
SELECT ic.concentration_percentage
FROM gr_ingredient_concentrations ic
WHERE ic.rowid = l_rowid;
SELECT ic.work_concentration
FROM gr_ingredient_conc_details ic
WHERE ic.rowid = l_rowid;
GR_INGRED_CONCENTRATIONS_PKG.Update_Row
(l_commit,
l_called_by_form,
l_rowid,
p_organization_id,
p_inventory_item_id,
p_source_item_id,
l_item_percent,
g_current_date,
g_user_id,
g_user_id,
g_current_date,
g_user_id,
l_return_status,
l_oracle_error,
l_msg_data);
RAISE Concentration_Insert_Error;
GR_INGRED_CONCENTRATIONS_PKG.Insert_Row
(l_commit,
l_called_by_form,
p_organization_id,
p_inventory_item_id,
p_source_item_id,
l_item_percent,
g_current_date,
g_user_id,
g_user_id,
g_current_date,
g_user_id,
l_rowid,
l_return_status,
l_oracle_error,
l_msg_data);
RAISE Concentration_Insert_Error;
GR_INGRED_CONC_DETAILS_PKG.Update_Row
(l_commit,
l_called_by_form,
l_rowid,
p_organization_id,
p_inventory_item_id,
p_source_item_id,
p_explosion_item_id,
p_current_record,
l_item_percent,
p_item_um,
0,
l_return_status,
l_oracle_error,
l_msg_data);
RAISE Concentration_Insert_Error;
GR_INGRED_CONC_DETAILS_PKG.Insert_Row
(l_commit,
l_called_by_form,
p_organization_id,
p_inventory_item_id,
p_source_item_id,
p_explosion_item_id,
p_current_record,
l_item_percent,
p_item_um,
0,
l_rowid,
l_return_status,
l_oracle_error,
l_msg_data);
RAISE Concentration_Insert_Error;
WHEN Concentration_Insert_Error THEN
ROLLBACK TO SAVEPOINT process_concentrations;