[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
# This procedure inserts the data into temp tables and will
# be fetched in the form.
###############################################################*/
PROCEDURE load_optimizer_details (V_entity_id IN NUMBER,V_maintain_type IN NUMBER,
X_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Cur_get_prod IS
SELECT *
FROM gmd_material_details_gtmp
WHERE line_type = 1;
SELECT a.*,b.value
FROM gmd_technical_parameter_gtmp a, gmd_technical_data_gtmp b
WHERE a.tech_parm_id = b.tech_parm_id
AND a.entity_id = b.entity_id
AND b.line_id = V_line_id
AND b.entity_id = V_entity_id
AND a.data_type IN (5,6,9,10);
SELECT *
FROM gmd_material_details_gtmp
WHERE line_type IN (-1,3)
ORDER BY line_no;
SELECT a.*
FROM gmd_technical_data_gtmp a, gmd_optimizer_prm_gtmp b
WHERE a.entity_id = b.entity_id
AND a.tech_parm_id = b.tech_parm_id
AND a.line_id = V_line_id;
/* Inserting the product data to optimize temp tables */
DELETE FROM gmd_optimizer_hdr_gtmp;
DELETE FROM gmd_optimizer_prm_gtmp;
DELETE FROM gmd_optimizer_line_gtmp;
DELETE FROM gmd_optimizer_value_gtmp;
INSERT INTO GMD_OPTIMIZER_HDR_GTMP
(ENTITY_ID,MAINTAIN_TYPE,YIELD,INVENTORY_ITEM_ID,PRODUCT_QTY,PRODUCT_UOM)
VALUES (V_entity_id,NVL(V_maintain_type,0),100,l_prod_rec.inventory_item_id,l_prod_rec.qty,l_prod_rec.detail_uom);
INSERT INTO GMD_OPTIMIZER_PRM_GTMP
(ENTITY_ID,OPTIMIZE_TYPE,TECH_PARM_ID,TECH_PARM_NAME,VALUE,MIN_VALUE,MAX_VALUE,PRECISION,LM_UNIT_CODE)
VALUES (V_entity_id,NVL(l_prod_param_rec.optimize_type,0),l_prod_param_rec.tech_parm_id,l_prod_param_rec.tech_parm_name,
l_prod_param_rec.value,l_prod_param_rec.lowerbound_num,l_prod_param_rec.upperbound_num,
l_prod_param_rec.signif_figures,l_prod_param_rec.lm_unit_code);
INSERT INTO GMD_OPTIMIZER_LINE_GTMP
(ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,INVENTORY_ITEM_ID,DESCRIPTION,
LOT_NUMBER,QTY,DETAIL_UOM,BUFFER_IND,PARENT_LINE_ID,PRIMARY_QTY,PRIMARY_UOM,
SECONDARY_QTY,SECONDARY_UOM,QTY_MASS,MASS_UOM,QTY_VOL,VOL_UOM,ROLLUP_IND)
VALUES (V_entity_id,l_ingred_rec.line_id,l_ingred_rec.line_type,l_ingred_rec.line_no,l_ingred_rec.inventory_item_id,
l_ingred_rec.description,l_ingred_rec.lot_number,
l_ingred_rec.qty,l_ingred_rec.detail_uom,NVL(l_ingred_rec.buffer_ind,0),l_ingred_rec.parent_line_id,
l_ingred_rec.primary_qty,l_ingred_rec.primary_uom,l_ingred_rec.secondary_qty,l_ingred_rec.secondary_uom,
l_ingred_rec.qty_mass,l_ingred_rec.mass_uom,l_ingred_rec.qty_vol,l_ingred_rec.vol_uom,l_ingred_rec.rollup_ind);
INSERT INTO GMD_OPTIMIZER_VALUE_GTMP
(ENTITY_ID,LINE_ID,TECH_PARM_ID,TECH_PARM_VALUE)
VALUES (l_value_rec.entity_id,l_value_rec.line_id,l_value_rec.tech_parm_id,l_value_rec.value);
SELECT a.tech_parm_name,a.lm_unit_code,a.tech_parm_id,b.data_type
FROM gmd_optimizer_prm_gtmp a, gmd_technical_parameter_gtmp b
WHERE a.entity_id = V_entity_id
AND a.entity_id = b.entity_id
AND a.tech_parm_id = b.tech_parm_id;
rollup_update (V_entity_id => V_entity_id,
V_parm_name => l_rec.tech_parm_name,
V_parm_id => l_rec.tech_parm_id,
X_return_status => X_return_status);
SELECT NVL(SUM(weight), 0), NVL(SUM(weightpct), 0)
FROM
(SELECT qty_mass weight, qty_mass * tech_parm_value weightpct
FROM gmd_optimizer_line_gtmp d, gmd_optimizer_value_gtmp t
WHERE d.line_id = t.line_id (+)
AND d.entity_id = t.entity_id (+)
AND d.entity_id = V_entity_id
AND t.tech_parm_id (+) = V_parm_id
AND rollup_ind = 1);
SELECT NVL(SUM(weight), 0), NVL(SUM(weightpct), 0)
FROM
(SELECT qty_mass weight, qty_mass * tech_parm_value weightpct
FROM gmd_material_details_gtmp d, gmd_optimizer_value_gtmp t
WHERE d.line_id = t.line_id (+)
AND (line_type = 2 OR line_type = 3)
AND d.entity_id = t.entity_id (+)
AND d.entity_id = V_entity_id
AND t.tech_parm_id (+) = V_parm_id
AND rollup_ind = 1
AND EXISTS (SELECT 1
FROM gmd_material_details_gtmp d1
WHERE line_type = 2
AND d1.parent_line_id = d.parent_line_id));
UPDATE gmd_optimizer_prm_gtmp
SET value = X_rollup
WHERE tech_parm_id = V_parm_id;
INSERT INTO GMD_OPTIMIZER_PRM_GTMP
(ENTITY_ID,
TECH_PARM_ID,
TECH_PARM_NAME,
VALUE)
VALUES
(V_entity_id,
V_parm_id,
V_parm_name,
X_rollup);
SELECT SUM(volume), SUM(volumepct)
FROM
(SELECT qty_vol volume, qty_vol * tech_parm_value volumepct
FROM gmd_optimizer_line_gtmp d, gmd_optimizer_value_gtmp t
WHERE d.line_id = t.line_id (+)
AND d.entity_id = t.entity_id (+)
AND d.entity_id = V_entity_id
AND t.tech_parm_id (+) = V_parm_id
AND rollup_ind = 1);
SELECT SUM(volume), SUM(volumepct)
FROM
(SELECT qty_vol volume, qty_vol * tech_parm_value volumepct
FROM gmd_material_details_gtmp d, gmd_optimizer_value_gtmp t
WHERE d.line_id = t.line_id (+)
AND (line_type = 2 OR line_type = 3)
AND d.entity_id = t.entity_id (+)
AND d.entity_id = V_entity_id
AND t.tech_parm_id (+) = V_parm_id
AND rollup_ind = 1
AND EXISTS (SELECT 1
FROM gmd_material_details_gtmp d1
WHERE line_type = 2
AND d1.parent_line_id = d.parent_line_id));
UPDATE gmd_optimizer_prm_gtmp
SET value = X_rollup
WHERE tech_parm_id = V_parm_id;
INSERT INTO GMD_OPTIMIZER_PRM_GTMP
(ENTITY_ID,
TECH_PARM_ID,
TECH_PARM_NAME,
VALUE)
VALUES
(V_entity_id,
V_parm_id,
V_parm_name,
X_rollup);
# rollup_update
# SYNOPSIS
# proc rollup_update
# DESCRIPTION
# This procedure gets the values for the products for the
# by performing the cost units rollup and updates the same.
###############################################################*/
PROCEDURE rollup_update (V_entity_id IN NUMBER,
V_parm_name IN VARCHAR2,
V_parm_id IN NUMBER,
X_return_status OUT NOCOPY VARCHAR2) IS
X_rollup_cost NUMBER;
UPDATE gmd_optimizer_prm_gtmp
SET value = X_rollup_cost
WHERE tech_parm_id = V_parm_id;
INSERT INTO GMD_OPTIMIZER_PRM_GTMP
(ENTITY_ID,
TECH_PARM_ID,
TECH_PARM_NAME,
VALUE)
VALUES
(V_entity_id,
V_parm_id,
V_parm_name,
X_rollup_cost);
END rollup_update;
SELECT NVL(SUM(volumepct), 0)
FROM
(SELECT primary_qty * tech_parm_value volumepct
FROM gmd_optimizer_line_gtmp d, gmd_optimizer_value_gtmp t
WHERE d.line_id = t.line_id (+)
AND d.entity_id = t.entity_id (+)
AND d.entity_id = V_entity_id
AND t.tech_parm_id (+) = V_parm_id
AND rollup_ind = 1);
SELECT NVL(SUM(volumepct), 0)
FROM
(SELECT primary_qty * tech_parm_value volumepct
FROM gmd_material_details_gtmp d, gmd_optimizer_value_gtmp t
WHERE d.line_id = t.line_id (+)
AND (line_type = 2 OR line_type = 3)
AND d.entity_id = t.entity_id (+)
AND d.entity_id = V_entity_id
AND t.tech_parm_id (+) = V_parm_id
AND rollup_ind = 1
AND EXISTS (SELECT 1
FROM gmd_material_details_gtmp d1
WHERE line_type = 2
AND d1.parent_line_id = d.parent_line_id));
SELECT d.qty,d.detail_uom,d.lot_number,t.tech_parm_value,d.inventory_item_id,p.tpformula_id
FROM gmd_optimizer_line_gtmp d, gmd_optimizer_value_gtmp t, gmd_material_details_gtmp p
WHERE d.line_id = t.line_id (+)
AND d.entity_id = t.entity_id (+)
AND d.entity_id = p.entity_id
AND d.line_id = p.line_id
AND d.entity_id = V_entity_id
AND t.tech_parm_id (+) = V_parm_id
AND t.tech_parm_value IS NOT NULL
AND d.rollup_ind = 1;
SELECT qty,detail_uom,lot_number,tech_parm_value,inventory_item_id,d.tpformula_id
FROM gmd_material_details_gtmp d, gmd_optimizer_value_gtmp t
WHERE d.line_id = t.line_id (+)
AND (line_type = 2 OR line_type = 3)
AND d.entity_id = t.entity_id (+)
AND d.entity_id = V_entity_id
AND t.tech_parm_id (+) = V_parm_id
AND t.tech_parm_value IS NOT NULL
AND rollup_ind = 1
AND EXISTS (SELECT 1
FROM gmd_material_details_gtmp d1
WHERE line_type = 2
AND d1.parent_line_id = d.parent_line_id);
UPDATE gmd_optimizer_prm_gtmp
SET value = NULL
WHERE tech_parm_id = V_parm_id;
UPDATE gmd_optimizer_prm_gtmp
SET value = X_rollup
WHERE tech_parm_id = V_parm_id;
INSERT INTO GMD_OPTIMIZER_PRM_GTMP
(ENTITY_ID,
TECH_PARM_ID,
TECH_PARM_NAME,
VALUE)
VALUES
(V_entity_id,
V_parm_id,
V_parm_name,
X_rollup);
# is_lot_selected
# SYNOPSIS
# proc is_lot_selected
# DESCRIPTION
# This function will check if nay lots are selected for optimzation.
###############################################################*/
FUNCTION is_lot_selected(V_parentline_id IN NUMBER) RETURN VARCHAR2 IS
CURSOR Cur_get_select IS
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT 1
FROM gmd_optimizer_line_gtmp
WHERE buffer_ind = 1
AND line_type = 3
AND parent_line_id = V_parentline_id);
OPEN Cur_get_select;
FETCH Cur_get_select INTO l_exist;
IF (Cur_get_select%FOUND) THEN
CLOSE Cur_get_select;
CLOSE Cur_get_select;
END is_lot_selected;
# This function will retunr the T or F based on the lot selected
# for that item.
###############################################################*/
FUNCTION consider_line(V_line_id IN NUMBER) RETURN VARCHAR2 IS
CURSOR Cur_get_linetype IS
SELECT line_type,parent_line_id
FROM gmd_optimizer_line_gtmp
WHERE line_id = V_line_id;
IF (is_lot_selected(l_parent_line_id) = 'T') THEN
IF (l_line_type = 3) THEN
RETURN('T');
SELECT value
FROM gmd_technical_data_gtmp
WHERE line_id = V_line_id
AND tech_parm_name = V_density_parameter;
# update_line_mass_vol_qty
# SYNOPSIS
# proc update_line_mass_vol_qty
# DESCRIPTION
# This procedure calculates the qtys to mass and volume.
###############################################################*/
PROCEDURE update_line_mass_vol_qty (V_orgn_id IN NUMBER,
V_line_id IN NUMBER,
V_density_parameter IN VARCHAR2,
V_mass_uom IN VARCHAR2,
V_vol_uom IN VARCHAR2,
X_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Cur_line_qty IS
SELECT inventory_item_id, lot_number, qty,
detail_uom,primary_uom,secondary_uom
FROM gmd_optimizer_line_gtmp
WHERE line_id = V_line_id;
SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = V_inventory_item_id;
UPDATE gmd_optimizer_line_gtmp
SET qty_mass = l_mass_qty,
mass_uom = V_mass_uom,
qty_vol = l_vol_qty,
vol_uom = V_vol_uom,
primary_qty = l_primary_qty,
primary_uom = l_rec.primary_uom
WHERE line_id = V_line_id;
fnd_msg_pub.add_exc_msg ('GMD_OPTIMIZE_FETCH_PKG', 'Update_Line_Mass_Vol_Qty');
END update_line_mass_vol_qty;
# update_line_mass_qty
# SYNOPSIS
# proc update_line_mass_qty
# DESCRIPTION
# This procedure calculates the qtys to mass and volume.
###############################################################*/
PROCEDURE update_mass_vol_qty (V_orgn_id IN NUMBER,
V_entity_id IN NUMBER,
V_density_parameter IN VARCHAR2,
V_mass_uom IN VARCHAR2,
V_vol_uom IN VARCHAR2,
X_return_status OUT NOCOPY VARCHAR2) IS
CURSOR Cur_get_lines IS
SELECT line_id
FROM gmd_optimizer_line_gtmp
WHERE rollup_ind = 1
AND entity_id = V_entity_id;
update_line_mass_vol_qty (V_orgn_id => V_orgn_id,
V_line_id => l_rec.line_id,
V_density_parameter => V_density_parameter,
V_mass_uom => V_mass_uom,
V_vol_uom => V_vol_uom,
X_return_status => l_return_status);
END update_mass_vol_qty;