[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT a.tech_parm_name,a.lm_unit_code,b.tech_parm_id,a.data_type
FROM gmd_tech_parameters_b a, gmd_technical_reqs b
WHERE a.tech_parm_id = b.tech_parm_id
AND b.formulation_spec_id = V_formulation_spec_id
AND a.data_type IN (5,6,12)
UNION
SELECT a.tech_parm_name,a.lm_unit_code,b.tech_parm_id,a.data_type
FROM gmd_tech_parameters_b a, gmd_formulation_specs b
WHERE a.tech_parm_id = b.tech_parm_id
AND b.formulation_spec_id = V_formulation_spec_id
AND a.data_type IN (5,6,12);
SELECT NVL(SUM(weight), 0), NVL(SUM(weightpct), 0)
FROM
(SELECT qty_mass weight, qty_mass * value weightpct
FROM gmd_lcf_details_gtmp d, gmd_lcf_tech_data_gtmp t
WHERE d.line_id = t.line_id (+)
AND t.tech_parm_id (+) = V_parm_id
AND line_type = -1);
UPDATE gmd_lcf_tech_data_gtmp
SET value = X_rollup
WHERE tech_parm_id = V_parm_id
AND line_id = V_line_id;
INSERT INTO GMD_LCF_TECH_DATA_GTMP
(TECH_PARM_ID,
VALUE,
NUM_DATA,
LINE_ID)
VALUES
(V_parm_id,
X_rollup,
X_rollup,
V_line_id);
SELECT SUM(volume), SUM(volumepct)
FROM
(SELECT qty_vol volume, qty_vol * value volumepct
FROM gmd_lcf_details_gtmp d, gmd_lcf_tech_data_gtmp t
WHERE d.line_id = t.line_id (+)
AND t.tech_parm_id (+) = V_parm_id
AND line_type = -1);
UPDATE gmd_lcf_tech_data_gtmp
SET value = X_rollup
WHERE tech_parm_id = V_parm_id
AND line_id = V_line_id;
INSERT INTO GMD_LCF_TECH_DATA_GTMP
(TECH_PARM_ID,
NUM_DATA,
VALUE,
LINE_ID)
VALUES
(V_parm_id,
X_rollup,
X_rollup,
V_line_id);
SELECT value
FROM gmd_lcf_tech_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_lcf_details_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_lcf_details_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_LCF_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_lcf_details_gtmp
WHERE line_type <> 1;
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;
# This procedure inserts the data into temp tables and will
# be fetched in the form.
###############################################################*/
PROCEDURE load_cost_values (V_orgn_id IN NUMBER, V_inv_item_id IN NUMBER, V_cost_type IN VARCHAR2,
V_date IN DATE, V_cost_orgn IN VARCHAR2, V_source IN NUMBER, X_value OUT NOCOPY NUMBER) IS
l_msg_data VARCHAR2(2000);
# This procedure inserts the data into temp tables and will
# be fetched in the form.
###############################################################*/
PROCEDURE load_tech_values (V_orgn_id IN NUMBER, V_formulation_spec_id IN NUMBER, V_date IN DATE) IS
CURSOR Cur_get_type IS
SELECT a.*, b.line_id, b.tech_parm_id tech, c.inventory_item_id
FROM gmd_tech_parameters_b a, gmd_lcf_tech_data_gtmp b, gmd_lcf_details_gtmp c
WHERE a.tech_parm_id = b.tech_parm_id
AND b.line_id = c.line_id;
SELECT a.num_data
FROM gmd_technical_data_vl a, gmd_lcf_tech_data_gtmp b, gmd_lcf_details_gtmp c
WHERE a.tech_parm_id = b.tech_parm_id
AND a.tech_parm_id = V_tech_parm_id
AND a.inventory_item_id = c.inventory_item_id
AND a.inventory_item_id = V_inventory_item_id
AND a.organization_id = V_orgn_id;
SELECT Cost_Type, cost_source
FROM gmd_tech_parameters_b
WHERE organization_id = P_orgn_id
AND Default_cost_parameter = 1;
/* Inserting the technical parameter data of item and lot to temp tables*/
IF (V_orgn_id IS NOT NULL) THEN
INSERT INTO GMD_LCF_TECH_DATA_GTMP
(LINE_ID,TECH_PARM_ID,TECH_PARM_NAME,QCASSY_TYP_ID)
SELECT c.line_id,b.tech_parm_id,d.tech_parm_name,d.qcassy_typ_id
FROM gmd_technical_reqs b,gmd_lcf_details_gtmp c, gmd_tech_parameters_b d
WHERE b.tech_parm_id = d.tech_parm_id
AND b.formulation_spec_id = V_formulation_spec_id;
INSERT INTO GMD_LCF_TECH_DATA_GTMP
(LINE_ID,TECH_PARM_ID,TECH_PARM_NAME,QCASSY_TYP_ID)
SELECT c.line_id,b.tech_parm_id,b.tech_parm_name,b.qcassy_typ_id
FROM gmd_tech_parameters_b b, gmd_lcf_details_gtmp c,
gmd_formulation_specs e
WHERE b.tech_parm_id = e.tech_parm_id
AND e.formulation_spec_id = V_formulation_spec_id;
UPDATE GMD_LCF_TECH_DATA_GTMP
SET value = l_value,
num_data = l_value
WHERE tech_parm_id = l_rec.tech
AND line_id = l_rec.line_id;
UPDATE GMD_LCF_TECH_DATA_GTMP
SET value = X_value,
num_data = X_value
WHERE tech_parm_id = l_rec.tech
AND line_id = l_rec.line_id;
INSERT INTO GMD_LCF_TECH_DATA_GTMP
(LINE_ID,TECH_PARM_ID,TECH_PARM_NAME,VALUE,NUM_DATA,QCASSY_TYP_ID)
SELECT c.line_id,a.tech_parm_id,d.tech_parm_name,
a.num_data,a.num_data,d.qcassy_typ_id
FROM gmd_technical_data_vl a,
gmd_lcf_details_gtmp c, gmd_tech_parameters_b d
WHERE a.tech_parm_id = d.tech_parm_id
AND d.tech_parm_name = l_density_parameter
AND a.organization_id = V_orgn_id
AND a.inventory_item_id = c.inventory_item_id;
# This procedure inserts the data into temp tables and will
# be fetched in the form.
###############################################################*/
PROCEDURE load_items (V_formulation_spec_id IN NUMBER, V_organization_id IN NUMBER,V_ingred_pick_base IN VARCHAR2,
V_formula_no IN VARCHAR2, V_batch_no IN VARCHAR2,V_date IN DATE) IS
CURSOR Cur_get_sim_material IS
SELECT a.*, b.concatenated_segments item
FROM gmd_material_details_gtmp a, mtl_system_items_kfv b
WHERE line_type = -1
AND a.inventory_item_id = b.inventory_item_id
AND b.organization_id = V_organization_id;
SELECT std_uom
FROM gmd_formulation_specs
WHERE formulation_spec_id = V_formulation_spec_id;
SELECT a.*, b.concatenated_segments, b.description, b.primary_uom_code
FROM gmd_material_reqs a, mtl_system_items_kfv b
WHERE formulation_spec_id = V_formulation_spec_id
AND a.inventory_item_id = b.inventory_item_id
AND b.organization_id = V_organization_id
ORDER BY b.concatenated_segments;
SELECT b.concatenated_segments, b.inventory_item_id,
b.description, b.primary_uom_code
FROM mtl_system_items_kfv b
WHERE EXISTS (SELECT 1
FROM gmd_lcf_category_hdr_gtmp a, mtl_item_categories c
WHERE a.category_set_id = c.category_set_id
AND a.category_id = c.category_id
AND b.organization_id = c.organization_id
AND c.organization_id = V_organization_id
AND b.inventory_item_id = c.inventory_item_id)
ORDER BY b.concatenated_segments;
SELECT num_data
FROM gmd_technical_data_vl
WHERE organization_id = V_organization_id
AND inventory_item_id = V_inv_item_id
AND tech_parm_name = V_density_parameter;
INSERT INTO GMD_LCF_DETAILS_GTMP
(ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,INVENTORY_ITEM_ID,CONCATENATED_SEGMENTS,
CONV_FACTOR,DESCRIPTION,DETAIL_UOM,PRIMARY_UOM,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE)
VALUES
(1,l_simulation_rec.line_id,l_simulation_rec.line_type,l_simulation_rec.line_no,0,
l_simulation_rec.inventory_item_id,l_simulation_rec.item,l_new_qty,
l_simulation_rec.description,l_simulation_rec.detail_uom,l_simulation_rec.detail_uom,
l_simulation_rec.created_by,l_simulation_rec.creation_date,
l_simulation_rec.last_updated_by,l_simulation_rec.last_update_date);
INSERT INTO GMD_LCF_DETAILS_GTMP
(ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,INVENTORY_ITEM_ID,CONCATENATED_SEGMENTS,
CONV_FACTOR,DESCRIPTION,DETAIL_UOM,PRIMARY_UOM,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE)
VALUES
(1,l_line_id,-1,l_formula_rec.line_no,0,l_formula_rec.inventory_item_id,l_formula_rec.concatenated_segments,
l_new_qty,l_formula_rec.description,l_formula_rec.primary_uom_code,l_formula_rec.primary_uom_code,
l_formula_rec.created_by,l_formula_rec.creation_date,
l_formula_rec.last_updated_by,l_formula_rec.last_update_date);
INSERT INTO GMD_LCF_DETAILS_GTMP
(ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,INVENTORY_ITEM_ID,CONCATENATED_SEGMENTS,
CONV_FACTOR,DESCRIPTION,DETAIL_UOM,PRIMARY_UOM)
VALUES
(1,l_line_id,-1,l_line_no,0,l_comp_rec.inventory_item_id,l_comp_rec.concatenated_segments,
l_new_qty,l_comp_rec.description,l_comp_rec.primary_uom_code,l_comp_rec.primary_uom_code);
# This procedure inserts the data into temp tables and will
# be fetched in the form.
###############################################################*/
PROCEDURE load_categories (V_formulation_spec_id IN NUMBER) IS
BEGIN
IF (V_formulation_spec_id IS NOT NULL) THEN
INSERT INTO GMD_LCF_CATEGORY_HDR_GTMP
(CATEGORY_ID,CATEGORY_NAME,CATEGORY_SET_ID,
CATEGORY_SET_NAME,MIN_PCT,MAX_PCT)
SELECT gcr.category_id,mc.concatenated_segments,
gcr.category_set_id,mcs.category_set_name,
gcr.min_pct,gcr.max_pct
FROM mtl_category_sets mcs, mtl_categories_kfv mc, gmd_compositional_reqs gcr
WHERE mcs.category_set_id = gcr.category_set_id
AND mc.category_id = gcr.category_id
AND gcr.formulation_spec_id = V_formulation_spec_id
ORDER BY order_no;
# and based on that insert the date into temp table.
###############################################################*/
PROCEDURE get_category_value (V_inventory_item_id IN NUMBER, V_organization_id IN NUMBER,
V_line_id IN NUMBER) IS
CURSOR Cur_check_hdr_category IS
SELECT category_id
FROM gmd_lcf_category_hdr_gtmp;
SELECT 1
FROM mtl_item_categories
WHERE category_id = V_category_id
AND inventory_item_id = V_inventory_item_id
AND organization_id = V_organization_id;
INSERT INTO GMD_LCF_CATEGORY_DTL_GTMP (LINE_ID,VALUE_IND,CATEGORY_ID)
VALUES (V_line_id,l_value_ind,l_rec.category_id);
# This procedure inserts the data into temp tables from quality
# tables.
###############################################################*/
PROCEDURE load_quality_data (V_line_id IN NUMBER, V_orgn_id IN NUMBER,
V_qcassy_typ_id IN NUMBER,V_tech_parm_id IN NUMBER) IS
CURSOR Cur_get_data IS
SELECT *
FROM gmd_lcf_details_gtmp
WHERE line_id = V_line_id;
UPDATE gmd_lcf_tech_data_gtmp
SET value = l_inv_val_out_rec_type.entity_value,
num_data = l_inv_val_out_rec_type.entity_value
WHERE line_id = V_line_id
AND tech_parm_id = V_tech_parm_id;
# This procedure inserts the data into rows and columns then
# call the lcf engine.
###############################################################*/
PROCEDURE generate_lcf_data (V_formulation_spec_id IN NUMBER, V_organization_id IN NUMBER,
V_formula_no IN VARCHAR2, V_batch_no IN VARCHAR2,V_date IN DATE,
X_return_code OUT NOCOPY NUMBER) IS
--Formulation specification details.
CURSOR Cur_get_formulation IS
SELECT *
FROM gmd_formulation_specs
WHERE formulation_spec_id = V_formulation_spec_id;
SELECT line_id, inventory_item_id, concatenated_segments, conv_factor
FROM gmd_lcf_details_gtmp
ORDER BY line_id;
SELECT COUNT(*)
FROM gmd_lcf_details_gtmp;
SELECT value
FROM gmd_lcf_tech_data_gtmp
WHERE tech_parm_id = V_tech_parm_id
AND line_id = V_line_id;
SELECT a.inventory_item_id, a.min_qty, a.max_qty,
a.item_uom, a.range_type, b.concatenated_segments
FROM gmd_material_reqs a, gmd_lcf_details_gtmp b
WHERE (a.min_qty IS NOT NULL OR a.max_qty IS NOT NULL)
AND a.formulation_spec_id = V_formulation_spec_id
AND a.inventory_item_id = b.inventory_item_id
ORDER BY b.line_id;
SELECT line_id, concatenated_segments, conv_factor
FROM gmd_lcf_details_gtmp
WHERE inventory_item_id = V_inventory_item_id
ORDER BY line_id;
SELECT a.category_id, a.min_pct, a.max_pct, b.category_name
FROM gmd_compositional_reqs a, gmd_lcf_category_hdr_gtmp b
WHERE a.category_id = b.category_id
AND (a.min_pct IS NOT NULL OR a.max_pct IS NOT NULL)
AND a.formulation_spec_id = V_formulation_spec_id
ORDER BY order_no;
SELECT line_id
FROM gmd_lcf_category_dtl_gtmp
WHERE category_id = V_category_id
AND value_ind = 1;
SELECT a.tech_parm_id,a.min_value, a.max_value, b.tech_parm_name
FROM gmd_technical_reqs a, gmd_tech_parameters_b b
WHERE a.tech_parm_id = b.tech_parm_id
AND (a.min_value IS NOT NULL OR a.max_value IS NOT NULL)
AND a.formulation_spec_id = V_formulation_spec_id;
SELECT line_id,value
FROM gmd_lcf_tech_data_gtmp
WHERE tech_parm_id = V_tech_parm_id
ORDER BY line_id;
SELECT line_id, inventory_item_id, detail_uom, conv_factor
FROM gmd_lcf_details_gtmp
WHERE concatenated_segments = V_item;
SELECT conv_factor
FROM gmd_lcf_details_gtmp
WHERE line_id = V_line_id;
UPDATE gmd_lcf_category_dtl_gtmp
SET qty = l_solved_tab(i).qty
WHERE line_id = l_dtl_lineid;
UPDATE gmd_lcf_details_gtmp
SET qty = l_new_qty
WHERE concatenated_segments = l_solved_tab(i).item;
DELETE
FROM gmd_lcf_details_gtmp
WHERE qty IS NULL OR qty = 0;
select *
from gmd_lcf_details_gtmp;
select a.*,b.concatenated_segments
from gmd_lcf_tech_data_gtmp a, gmd_lcf_details_gtmp b
where a.line_id= b.line_id;
select *
from gmd_lcf_category_dtl_gtmp;