[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select 1 from fm_matl_dtl fm
Where exists (Select 1
From gmd_recipes_b rc
Where fm.formula_id = rc.formula_id
And fm.line_type IN (1,2)
And fm.inventory_item_id = vItem_id -- NPD Conv.
And rc.recipe_id = vRecipe_id);
SELECT std_qty, inventory_item_id, detail_uom, orgn_code
FROM gmd_recipe_validity_rules
WHERE recipe_validity_rule_id = V_vr_id;
# This is used as the duplicate check should check that the updated record is not going
# to create a duplicate record, i.e. the same details should not match any
# other record(corresponding to p_recipe_validity_rule_id other than this one)
#####################################################*/
PROCEDURE check_for_duplicate(p_recipe_validity_rule_id NUMBER -- 4134275 Added the validity_rule_id condition for duplicate check
,pRecipe_id NUMBER
,pitem_id NUMBER
,pOrgn_code VARCHAR2 DEFAULT NULL
-- NPD Conv.
,pOrganization_id NUMBER
,pRecipe_Use NUMBER
,pPreference NUMBER
,pstd_qty NUMBER
,pmin_qty NUMBER
,pmax_qty NUMBER
,pinv_max_qty NUMBER
,pinv_min_qty NUMBER
,pitem_um VARCHAR2
,pValidity_Rule_Status VARCHAR2
,pstart_date DATE
,pend_date DATE DEFAULT NULL
,pPlanned_process_loss NUMBER DEFAULT NULL
,x_return_status OUT NOCOPY VARCHAR2
) IS
CURSOR Cur_check_dup_upd IS
SELECT recipe_validity_rule_id
FROM gmd_recipe_validity_rules
WHERE recipe_id = pRecipe_id
AND inventory_item_id = pitem_id -- NPD Conv.
AND ((orgn_code = pOrgn_code) OR
(orgn_code IS NULL AND pOrgn_code IS NULL))
-- NPD Conv.
AND ((organization_id = pOrganization_id) OR
(organization_id IS NULL AND pOrganization_id IS NULL))
AND recipe_use = pRecipe_Use
AND preference = pPreference
AND std_qty = pstd_qty
AND min_qty = pmin_qty
AND max_qty = pmax_qty
AND inv_max_qty = pinv_max_qty
AND inv_min_qty = pinv_min_qty
AND detail_uom = pitem_um
AND validity_rule_status = pValidity_Rule_status
AND ((pPlanned_process_loss IS NULL AND Planned_process_loss IS NULL) OR
(planned_process_loss = pPlanned_process_loss))
AND start_date = pstart_date
AND ((end_date = pend_date) OR (end_date is NULL and pend_date is NULL))
AND recipe_validity_rule_id <> p_recipe_validity_rule_id;
/* update_validity_rules */
/* */
/* DESCRIPTION: */
/* */
/* */
/* History : */
/* Shyam 07/29/2002 Initial implementation */
/* Kapil M 18-NOV-2005 Bug # 4134275 */
/* Changed the call from fnd_date.CHARDATE_TO_DATE to */
/* fnd_date.CANONICAL_TO_DATE
/* =============================================================== */
PROCEDURE update_validity_rules
( p_validity_rule_id IN gmd_recipe_validity_rules.recipe_validity_rule_id%TYPE
, p_update_table IN gmd_validity_rules_pvt.update_tbl_type
, x_message_count OUT NOCOPY NUMBER
, x_message_list OUT NOCOPY VARCHAR2
, x_return_status OUT NOCOPY VARCHAR2
) IS
/* Local variable section */
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_VALIDITY_RULES';
VR_update_failure EXCEPTION;
last_update_date_failure EXCEPTION;
Select *
From gmd_recipe_validity_rules
Where recipe_validity_rule_id = vValidity_rule_id;
CURSOR Get_db_last_update_date(vValidity_rule_id
gmd_recipe_validity_rules.recipe_validity_rule_id%TYPE) IS
Select last_update_date
From gmd_recipe_validity_rules
Where recipe_validity_rule_id = vValidity_rule_id;
Select rt.Effective_Start_Date,
rt.Effective_End_Date
From gmd_routings_b rt, gmd_recipes_b rc,
gmd_recipe_validity_rules vr
Where vr.recipe_id = rc.recipe_id AND
rc.routing_id = rt.routing_id AND
vr.recipe_validity_rule_id = vValidity_rule_id AND
rt.delete_mark = 0;
SELECT 1
FROM sys.dual
WHERE EXISTS (Select d.formula_id
From fm_matl_dtl d, gmd_recipes_b r
WHERE r.formula_id = d.formula_id AND
r.recipe_id = vRecipe_id AND
d.line_type = 1 AND
d.inventory_item_id = vItem_id AND -- NPD Conv.
d.scale_type = 0);
SELECT 1
FROM sys.dual
WHERE EXISTS (Select h.formula_id
From fm_form_mst h, gmd_recipes_b r
WHERE r.formula_id = h.formula_id AND
r.recipe_id = vRecipe_id AND
h.scale_type = 0);
SELECT r.formula_id, v.recipe_use
FROM gmd_recipes_b r, gmd_recipe_validity_rules v
WHERE v.recipe_validity_rule_id = p_validity_rule_id
AND v.recipe_id = r.recipe_id;
RAISE vr_update_failure;
/* Loop thro' every column in p_update_table table and for each column name
assign or replace the old value with the table value */
FOR i IN 1 .. p_update_table.count LOOP
IF (l_debug = 'Y') THEN
gmd_debug.put_line(m_pkg_name||'.'||l_api_name||' : The column to be updated = '
||p_update_table(i).p_col_to_update||' and value = '
||p_update_table(i).p_value);
IF (UPPER(p_update_table(i).p_col_to_update) = 'ORGN_CODE') THEN
l_old_vr_rec.ORGN_CODE := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ORGANIZATION_ID') THEN
l_old_vr_rec.ORGANIZATION_ID := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'INVENTORY_ITEM_ID') THEN
l_old_vr_rec.INVENTORY_ITEM_ID := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'REVISION') THEN
l_old_vr_rec.REVISION := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'DETAIL_UOM') THEN
l_old_vr_rec.DETAIL_UOM := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'TEXT_CODE') THEN
l_old_vr_rec.TEXT_CODE := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'RECIPE_USE') THEN
l_old_vr_rec.RECIPE_USE := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'PREFERENCE') THEN
l_old_vr_rec.PREFERENCE := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'START_DATE') THEN
IF (l_debug = 'Y') THEN
gmd_debug.put_line(m_pkg_name||'.'||l_api_name||
' : Before conversion of Start date - '||
' CharDT to Date Format ');
l_old_vr_rec.START_DATE := FND_DATE.canonical_to_date(p_update_table(i).p_value);
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'END_DATE') THEN
IF (l_debug = 'Y') THEN
gmd_debug.put_line(m_pkg_name||'.'||l_api_name
||' : Before conversion of end date - '
||' CharDT to Date Format '||p_update_table(i).p_value);
l_old_vr_rec.END_DATE := FND_DATE.canonical_to_date(p_update_table(i).p_value);
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'PLANNED_PROCESS_LOSS') THEN
l_old_vr_rec.PLANNED_PROCESS_LOSS := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'FIXED_PROCESS_LOSS') THEN
l_old_vr_rec.FIXED_PROCESS_LOSS := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'FIXED_PROCESS_LOSS_UOM') THEN
l_old_vr_rec.FIXED_PROCESS_LOSS_UOM := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'STD_QTY') THEN
l_old_vr_rec.STD_QTY := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'MIN_QTY') THEN
l_old_vr_rec.MIN_QTY := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'MAX_QTY') THEN
l_old_vr_rec.MAX_QTY := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'DELETE_MARK') THEN
l_old_vr_rec.DELETE_MARK := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'LAST_UPDATED_BY') THEN
l_old_vr_rec.LAST_UPDATED_BY := NVL(p_update_table(i).p_value, fnd_global.user_id);
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'LAST_UPDATE_DATE') THEN
l_old_vr_rec.LAST_UPDATE_DATE := FND_DATE.canonical_to_date(p_update_table(i).p_value);
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'LAST_UPDATE_LOGIN') THEN
l_old_vr_rec.LAST_UPDATE_LOGIN := NVL(p_update_table(i).p_value,gmd_api_grp.login_id);
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE1') THEN
l_old_vr_rec.ATTRIBUTE1 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE2') THEN
l_old_vr_rec.ATTRIBUTE2 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE3') THEN
l_old_vr_rec.ATTRIBUTE3 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE4') THEN
l_old_vr_rec.ATTRIBUTE4 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE5') THEN
l_old_vr_rec.ATTRIBUTE5 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE6') THEN
l_old_vr_rec.ATTRIBUTE6 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE7') THEN
l_old_vr_rec.ATTRIBUTE7 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE8') THEN
l_old_vr_rec.ATTRIBUTE8 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE9') THEN
l_old_vr_rec.ATTRIBUTE9 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE10') THEN
l_old_vr_rec.ATTRIBUTE10 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE11') THEN
l_old_vr_rec.ATTRIBUTE11 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE12') THEN
l_old_vr_rec.ATTRIBUTE12 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE13') THEN
l_old_vr_rec.ATTRIBUTE13 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE14') THEN
l_old_vr_rec.ATTRIBUTE14 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE15') THEN
l_old_vr_rec.ATTRIBUTE15 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE16') THEN
l_old_vr_rec.ATTRIBUTE16 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE17') THEN
l_old_vr_rec.ATTRIBUTE17 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE18') THEN
l_old_vr_rec.ATTRIBUTE18 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE19') THEN
l_old_vr_rec.ATTRIBUTE19 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE20') THEN
l_old_vr_rec.ATTRIBUTE20 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE21') THEN
l_old_vr_rec.ATTRIBUTE21 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE22') THEN
l_old_vr_rec.ATTRIBUTE22 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE23') THEN
l_old_vr_rec.ATTRIBUTE23 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE24') THEN
l_old_vr_rec.ATTRIBUTE24 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE25') THEN
l_old_vr_rec.ATTRIBUTE25 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE26') THEN
l_old_vr_rec.ATTRIBUTE26 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE27') THEN
l_old_vr_rec.ATTRIBUTE27 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE28') THEN
l_old_vr_rec.ATTRIBUTE28 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE29') THEN
l_old_vr_rec.ATTRIBUTE29 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE30') THEN
l_old_vr_rec.ATTRIBUTE30 := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'ATTRIBUTE_CATEGORY') THEN
l_old_vr_rec.ATTRIBUTE_CATEGORY := p_update_table(i).p_value;
ELSIF (UPPER(p_update_table(i).p_col_to_update) = 'VALIDITY_RULE_STATUS') THEN
-- Users should be prompted to use change status API
-- Since Change Status API commits work it cannot be
-- called from here directly
FND_MESSAGE.set_name('GMD','GMD_NOT_USE_API_UPD_STATUS');
RAISE vr_update_failure;
/* Chcek if update is allowed */
IF NOT GMD_COMMON_VAL.update_allowed('VALIDITY'
,p_Validity_rule_id
,UPPER(p_update_table(i).p_col_to_update) ) THEN
FND_MESSAGE.SET_NAME('GMD', 'GMD_VR_CANNOT_UPD');
RAISE vr_update_failure;
/* Compare Dates - if the last update date passed in via the API is less than
the last update in the db - it indicates someelse has updated this row after this
row was selected */
IF (l_debug = 'Y') THEN
gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 1 '||
'Comparing last updates to check if there any locking issues ');
OPEN Get_db_last_update_date(p_Validity_rule_id);
FETCH Get_db_last_update_date INTO l_db_date;
IF Get_db_last_update_date%NOTFOUND THEN
CLOSE Get_db_last_update_date;
RAISE vr_update_failure;
CLOSE Get_db_last_update_date;
IF l_old_vr_rec.LAST_UPDATE_DATE < l_db_date THEN
RAISE last_update_date_failure;
IF (UPPER(p_update_table(i).p_col_to_update) = 'ITEM_ID') THEN
IF NOT Determine_Product(l_old_vr_rec.RECIPE_ID, l_old_vr_rec.INVENTORY_ITEM_ID) THEN -- NPD Conv.
FND_MESSAGE.SET_NAME('GMD', 'GMD_ITEM_IS_PRODUCT');
RAISE vr_update_failure;
IF (UPPER(p_update_table(i).p_col_to_update) IN ('START_DATE','END_DATE')) THEN
-- Validity rule start and end date validation
IF (l_debug = 'Y') THEN
gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 2c '||
'Checking if end date ( '||l_old_vr_rec.end_date||' ) '||
' > '||' start date ( '||l_old_vr_rec.start_date||' ) ');
RAISE vr_update_failure;
RAISE vr_update_failure;
RAISE vr_update_failure;
END IF; -- When start or end dates are updated
IF (UPPER(p_update_table(i).p_col_to_update)
IN ('STD_QTY','MIN_QTY','MAX_QTY')) THEN
-- Check if scale type at formula header is fixed, if yes then
-- the qty's fields cannot be updated
IF (l_debug = 'Y') THEN
gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': Val 3 '||
'Checking if formula hdr is fixed scaled ');
RAISE vr_update_failure;
RAISE vr_update_failure;
IF (UPPER(p_update_table(i).p_col_to_update) IN ('MIN_QTY','MAX_QTY')) THEN
SELECT UNIQUE primary_uom_code
INTO l_inv_item_um
FROM mtl_system_items
WHERE inventory_item_id = l_old_vr_rec.inventory_item_id;
RAISE vr_update_failure;
IF ((UPPER(p_update_table(i).p_col_to_update) = 'STD_QTY') AND
(UPPER(p_update_table(i).p_col_to_update) <> 'PLANNED_PROCESS_LOSS')) THEN
calculate_process_loss( V_assign => 1
,P_vr_id => p_validity_rule_id
,X_TPL => l_tpl
,X_PPL => l_old_vr_rec.planned_process_loss
,x_return_status => x_return_status);
RAISE vr_update_failure;
||': Before Final update : About to Update Val Rules '
||'The min qty, max qty and std qty = '
||l_old_vr_rec.min_qty||' - '||l_old_vr_rec.max_qty
||' - '||l_old_vr_rec.std_qty);
/* Check if for the updated organization - formula items remain valid */
IF (UPPER(p_update_table(i).p_col_to_update) IN ('ORGN_CODE','ORGANIZATION_ID')) THEN
-- Get the formula_id and recipe_use for the VR
OPEN get_recp_dets;
RAISE vr_update_failure;
RAISE vr_update_failure;
RAISE vr_update_failure;
/* Number of times this routine is equal to number of rows in the p_update_table */
UPDATE GMD_RECIPE_VALIDITY_RULES
SET
recipe_id = l_old_vr_rec.recipe_id
, orgn_code = l_old_vr_rec.orgn_code
-- NPD Conv.
, organization_id = l_old_vr_rec.organization_id
, inventory_item_id = l_old_vr_rec.inventory_item_id
, revision = l_old_vr_rec.revision
, detail_uom = l_old_vr_rec.detail_uom
-- End NPD Conv.
, recipe_use = l_old_vr_rec.recipe_use
, preference = l_old_vr_rec.preference
, start_date = l_old_vr_rec.start_date
, end_date = l_old_vr_rec.end_date
, min_qty = l_old_vr_rec.min_qty
, max_qty = l_old_vr_rec.max_qty
, std_qty = l_old_vr_rec.std_qty
, inv_min_qty = l_old_vr_rec.inv_min_qty
, inv_max_qty = l_old_vr_rec.inv_max_qty
, text_code = l_old_vr_rec.text_code
, attribute_category = l_old_vr_rec.attribute_category
, attribute1 = l_old_vr_rec.attribute1
, attribute2 = l_old_vr_rec.attribute2
, attribute3 = l_old_vr_rec.attribute3
, attribute4 = l_old_vr_rec.attribute4
, attribute5 = l_old_vr_rec.attribute5
, attribute6 = l_old_vr_rec.attribute6
, attribute7 = l_old_vr_rec.attribute7
, attribute8 = l_old_vr_rec.attribute8
, attribute9 = l_old_vr_rec.attribute9
, attribute10 = l_old_vr_rec.attribute10
, attribute11 = l_old_vr_rec.attribute11
, attribute12 = l_old_vr_rec.attribute12
, attribute13 = l_old_vr_rec.attribute13
, attribute14 = l_old_vr_rec.attribute14
, attribute15 = l_old_vr_rec.attribute15
, attribute16 = l_old_vr_rec.attribute16
, attribute17 = l_old_vr_rec.attribute17
, attribute18 = l_old_vr_rec.attribute18
, attribute19 = l_old_vr_rec.attribute19
, attribute20 = l_old_vr_rec.attribute20
, attribute21 = l_old_vr_rec.attribute21
, attribute23 = l_old_vr_rec.attribute23
, attribute22 = l_old_vr_rec.attribute22
, attribute24 = l_old_vr_rec.attribute24
, attribute25 = l_old_vr_rec.attribute25
, attribute26 = l_old_vr_rec.attribute26
, attribute27 = l_old_vr_rec.attribute27
, attribute28 = l_old_vr_rec.attribute28
, attribute29 = l_old_vr_rec.attribute29
, attribute30 = l_old_vr_rec.attribute30
, created_by = l_old_vr_rec.created_by
, creation_date = l_old_vr_rec.creation_date
, last_updated_by = l_old_vr_rec.last_updated_by
, last_update_date = l_old_vr_rec.last_update_date
, last_update_login = l_old_vr_rec.last_update_login
, delete_mark = l_old_vr_rec.delete_mark
, validity_rule_status = l_old_vr_rec.validity_rule_status
, lab_type = l_old_vr_rec.lab_type
, planned_process_loss = l_old_vr_rec.planned_process_loss
, fixed_process_loss = l_old_vr_rec.fixed_process_loss /* RLNAGARA Bug6997624 */
, fixed_process_loss_uom = l_old_vr_rec.fixed_process_loss_uom /* RLNAGARA Bug6997624 */
where recipe_validity_rule_id = p_validity_rule_id;
RAISE vr_update_failure;
gmd_debug.put_line(m_pkg_name||'.'||l_api_name||': After Update of Val Rules ');
RAISE vr_update_failure;
WHEN vr_update_failure OR invalid_version THEN
IF (l_debug = 'Y') THEN
gmd_debug.put_line (m_pkg_name||'.'||l_api_name||':'||'API not complete');
WHEN last_update_date_failure THEN
x_return_status := FND_API.G_RET_STS_ERROR;
END update_validity_rules;