[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Insert_FormulaEffectivity */
/* DESCRIPTION: */
/* This PL/SQL procedure is responsible for */
/* inserting a formula effectivity. */
/* HISTORY */
/* Mohit Kapoor 10-May-2002 Bug 2186284 */
/* Modified the insert statement such that the start_date */
/* and end_date are inserted without timestamp. */
/* ============================================= */
PROCEDURE Insert_FormulaEffectivity
( p_api_version IN NUMBER ,
p_init_msg_list IN varchar2 ,
p_commit IN varchar2 ,
x_return_status OUT NOCOPY varchar2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_formula_effectivity_rec IN fm_form_eff%ROWTYPE
)
IS
/* Local Variables definitions */
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_FORMULAEFFECTIVITY';
SAVEPOINT Insert_FormulaEffectivity_PVT;
/* Later on this insert should be changed to */
/* make insert on business view as opposed to tables directly. */
/* Bug 2186284 Mohit Kapoor */
/* Modified p_formula_effectivity_rec.end_date and */
/* p_formula_effectivity_rec.start_date to use */
/* trunc(p_formula_effectivity_rec.end_date), */
/* trunc(p_formula_effectivity_rec.start_date) */
INSERT INTO fm_form_eff
(fmeff_id, orgn_code,
item_id, formula_use,
end_date, start_date,
inv_min_qty, inv_max_qty,
min_qty, max_qty,
std_qty, item_um,
preference, routing_id,
formula_id, cust_id,
trans_cnt, text_code,
delete_mark, created_by,
creation_date, last_update_date,
last_updated_by, last_update_login)
VALUES
(p_formula_effectivity_rec.fmeff_id, p_formula_effectivity_rec.orgn_code,
p_formula_effectivity_rec.item_id, p_formula_effectivity_rec.formula_use,
TRUNC(p_formula_effectivity_rec.end_date), TRUNC(p_formula_effectivity_rec.start_date),
p_formula_effectivity_rec.inv_min_qty, p_formula_effectivity_rec.inv_max_qty,
p_formula_effectivity_rec.min_qty, p_formula_effectivity_rec.max_qty,
p_formula_effectivity_rec.std_qty, p_formula_effectivity_rec.item_um,
p_formula_effectivity_rec.preference, p_formula_effectivity_rec.routing_id,
p_formula_effectivity_rec.formula_id, p_formula_effectivity_rec.cust_id,
p_formula_effectivity_rec.trans_cnt, p_formula_effectivity_rec.text_code,
p_formula_effectivity_rec.delete_mark, p_formula_effectivity_rec.created_by,
p_formula_effectivity_rec.creation_date, p_formula_effectivity_rec.last_update_date,
p_formula_effectivity_rec.last_updated_by, p_formula_effectivity_rec.last_update_login);
ROLLBACK to Insert_FormulaEffectivity_PVT;
ROLLBACK to Insert_FormulaEffectivity_PVT;
ROLLBACK to Insert_FormulaEffectivity_PVT;
END Insert_FormulaEffectivity;
/* Update_FormulaEffectivity */
/* DESCRIPTION: */
/* This PL/SQL procedure is responsible for */
/* updating a formula effectivity. */
/* HISTORY: */
/* RajaSekhar 03/02/2002 BUG#2202559 */
/* Modified the code to update the effective */
/* END_DATE of the frozen formula */
/* Mohit Kapoor 10-May-2002 Bug 2186284 */
/* Modified the update statement such that the start_date */
/* and end_dates are updated without timestamp. */
/* K. RajaSekhar Reddy 10/04/2002 BUG#2583665 */
/* Modified IF statement to update the formula effectivity record */
/* with the existing orgn_code, if it's value is not passed or passed as null. */
/* ============================================= */
PROCEDURE Update_FormulaEffectivity
( p_api_version IN NUMBER ,
p_init_msg_list IN VARCHAR2 ,
p_commit IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2 ,
x_msg_count OUT NOCOPY NUMBER ,
x_msg_data OUT NOCOPY VARCHAR2 ,
p_formula_effectivity_rec IN fm_form_eff%ROWTYPE
)
IS
/* Local Variables definitions */
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_FORMULAEFFECTIVITY';
SELECT *
FROM fm_form_eff
WHERE fmeff_id = vfmeff_id;
SAVEPOINT Update_FormulaEffectivity_PVT;
/* Later on to be changed to update a business view */
/* and not a table. */
/* Certain vaildation to be performed. */
OPEN get_record(p_formula_effectivity_rec.fmeff_id);
UPDATE fm_form_eff SET
orgn_code = l_orgn_code,
formula_use = l_formula_use,
end_date = TRUNC(l_end_date),
start_date = TRUNC(l_start_date),
inv_min_qty = l_inv_min_qty,
inv_max_qty = l_inv_max_qty,
min_qty = l_min_qty,
max_qty = l_max_qty,
std_qty = l_std_qty,
item_um = l_item_um,
preference = l_preference,
--BEGIN BUG#2202559 RajaSekhar
--The columns creation_date and created _by are commented as update
--statement should not overwrite them. DECODE is used to update the column
--with the database value if it is not passed in PL/SQL record.
routing_id = DECODE(p_formula_effectivity_rec.routing_id, NULL, routing_id, p_formula_effectivity_rec.routing_id),
cust_id = DECODE(p_formula_effectivity_rec.cust_id, NULL, cust_id, p_formula_effectivity_rec.cust_id),
--creation_date = p_formula_effectivity_rec.creation_date,
--created_by = p_formula_effectivity_rec.created_by,
last_update_date = p_formula_effectivity_rec.last_update_date,
last_updated_by = p_formula_effectivity_rec.last_updated_by,
delete_mark = DECODE(p_formula_effectivity_rec.delete_mark, NULL, delete_mark, p_formula_effectivity_rec.delete_mark),
text_code = DECODE(p_formula_effectivity_rec.text_code, NULL, text_code, p_formula_effectivity_rec.text_code),
trans_cnt = DECODE(p_formula_effectivity_rec.trans_cnt, NULL, trans_cnt, p_formula_effectivity_rec.trans_cnt)
--END BUG#2202559
WHERE
fmeff_id = p_formula_effectivity_rec.fmeff_id;
ROLLBACK to Update_FormulaEffectivity_PVT;
ROLLBACK to Update_FormulaEffectivity_PVT;
ROLLBACK to Update_FormulaEffectivity_PVT;
END Update_FormulaEffectivity;