[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
V_orgn_id IN NUMBER,V_update_inv_ind IN VARCHAR2,
V_plant_id IN NUMBER) IS
BEGIN
DELETE FROM gmd_material_details_gtmp;
DELETE FROM gmd_technical_data_gtmp;
gmd_spread_fetch_pkg.load_batch_details(V_entity_id,V_batch_id,V_orgn_id,V_update_inv_ind,V_plant_id);
# This procedure inserts the data into temp tables and will
# be fetched in the form.
###############################################################*/
PROCEDURE load_spread_details (V_entity_id IN NUMBER, V_sprd_id IN NUMBER,V_orgn_id IN NUMBER) IS
CURSOR Cur_get_spread IS
SELECT a.*,b.description,b.lot_control_code,b.secondary_default_ind,
b.grade_control_flag,b.location_control_code,b.tracking_quantity_ind,c.expiration_date expiry_date,
b.primary_uom_code PRIMARY, c.lot_number lot, d.batchstep_no
FROM lm_sprd_dtl a, mtl_system_items_b b, mtl_lot_numbers c,
gme_batch_steps d, gme_batch_step_items e
WHERE a.inventory_item_id = b.inventory_item_id
AND a.organization_id = b.organization_id
AND a.inventory_item_id = c.inventory_item_id (+)
AND a.organization_id = c.organization_id (+)
AND a.lot_number = c.lot_number (+)
AND a.sprd_id = V_sprd_id
AND a.material_detail_id = e.material_detail_id (+)
AND d.batchstep_id(+) = e.batchstep_id
AND (a.line_type <> 1 OR a.line_no = 1)
ORDER BY a.line_type,a.line_no;
SELECT parent_line_id
FROM gmd_material_details_gtmp
WHERE entity_id = V_entity_id
AND line_type <> 3 ;
SELECT text_code
FROM gme_material_details
WHERE material_detail_id = V_matldetlid;
SELECT text_code
FROM fm_matl_dtl
WHERE formulaline_id = V_formlineid;
/* Inserting the item and lot data from spread tables to temp tables*/
IF (V_sprd_id IS NOT NULL) THEN
OPEN Cur_get_spread;
INSERT INTO GMD_MATERIAL_DETAILS_GTMP
(ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,TRACKING_QUANTITY_IND,LOCATION_CONTROL_CODE,
INVENTORY_ITEM_ID,DESCRIPTION,QTY,SECONDARY_QTY,DETAIL_UOM,ORGANIZATION_ID,
GRADE_CODE,PRIMARY_UOM,SECONDARY_UOM,LOT_CONTROL_CODE,REVISION,
GRADE_CONTROL_FLAG,LOT_NUMBER,TEXT_CODE,ORGINAL_TEXT_CODE,SPRD_LINE_ID,ACTION_CODE,
FORMULALINE_ID,EXPAND_IND,EXPIRATION_DATE,MATERIAL_DETAIL_ID,PARENT_LINE_ID,TPFORMULA_ID,
SUBINVENTORY_CODE,LOCATION,TRANSACTION_ID,RESERVATION_ID,BATCHSTEP_NO,BUFFER_IND, PLANT_ORGANIZATION_ID,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,REVISION_QTY_CONTROL_CODE,
MOVE_ORDER_LINE_ID,SECONDARY_DEFAULT_IND,LOCATOR_ID,PROD_PERCENT)
VALUES
(l_spread_rec.sprd_id,l_line_id,l_spread_rec.line_type,l_spread_rec.line_no,
l_spread_rec.rollup_ind,l_spread_rec.tracking_quantity_ind,l_spread_rec.location_control_code,
l_spread_rec.inventory_item_id,l_spread_rec.description,
l_spread_rec.qty,l_spread_rec.secondary_qty,l_spread_rec.detail_uom,l_spread_rec.organization_id,l_spread_rec.grade_code,
l_spread_rec.primary,l_spread_rec.secondary_uom,l_spread_rec.lot_control_code,
l_spread_rec.revision,l_spread_rec.grade_control_flag,l_spread_rec.lot_number,
l_spread_rec.text_code,l_text_code,l_spread_rec.line_id,'NONE',l_spread_rec.formulaline_id,
DECODE(l_spread_rec.lot_number,NULL,1,0),l_spread_rec.expiry_date,l_spread_rec.material_detail_id,
l_parent_line_id,l_spread_rec.tpformula_id,l_spread_rec.subinventory_code,l_spread_rec.location,
l_spread_rec.transaction_id,l_spread_rec.reservation_id,l_spread_rec.batchstep_no,l_spread_rec.buffer_ind,l_spread_rec.plant_organization_id,
l_spread_rec.created_by,l_spread_rec.creation_date,l_spread_rec.last_updated_by,
l_spread_rec.last_update_date,l_spread_rec.revision_qty_control_code,
l_spread_rec.move_order_line_id,l_spread_rec.secondary_default_ind,l_spread_rec.locator_id,l_spread_rec.prod_percent);
# This procedure inserts the data into temp tables and will
# be fetched in the form.
###############################################################*/
PROCEDURE load_batch_details (V_entity_id IN NUMBER, V_batch_id IN NUMBER,
V_orgn_id IN NUMBER,V_update_inv_ind IN VARCHAR2,
V_plant_id IN NUMBER) IS
CURSOR Cur_get_batch IS
SELECT a.*,b.description,b.lot_control_code,
b.grade_control_flag,b.tracking_quantity_ind,b.location_control_code,
b.default_grade,b.primary_uom_code primary,b.secondary_uom_code secondary,
c.batchstep_no,e.batch_status, b.revision_qty_control_code,b.secondary_default_ind
FROM gme_material_details a, mtl_system_items_b b, gme_batch_steps c,
gme_batch_step_items d, gme_batch_header e
WHERE a.inventory_item_id = b.inventory_item_id
AND b.organization_id = a.organization_id
AND a.batch_id = V_entity_id
AND e.batch_id = V_batch_id
AND a.material_detail_id = d.material_detail_id (+)
AND c.batchstep_id(+) = d.batchstep_id
AND (a.line_type <> 1 OR a.line_no = 1)
ORDER BY a.line_type, a.line_no;
SELECT a.*, c.expiration_date, b.inventory_item_id,
b.detail_uom, b.primary_uom, b.secondary_uom,
b.tracking_quantity_ind,b.lot_control_code,b.secondary_default_ind,
b.grade_control_flag,b.location_control_code, b.organization_id,b.locator_id
FROM gme_pending_product_lots a, gmd_material_details_gtmp b,
mtl_lot_numbers c
WHERE a.material_detail_id = b.material_detail_id
AND a.material_detail_id = V_matl_detl_id
AND a.batch_id = V_batch_id
AND b.organization_id = c.organization_id
AND b.inventory_item_id = c.inventory_item_id
AND a.lot_number = c.lot_number
AND b.lot_control_code = 2
AND b.line_type NOT IN (1,3);
SELECT b.tracking_quantity_ind,b.lot_control_code,
b.grade_control_flag,b.location_control_code,
b.default_grade,b.secondary_default_ind,c.expiration_date,c.organization_id
FROM mtl_system_items b,mtl_lot_numbers c
WHERE b.inventory_item_id = c.inventory_item_id
AND b.organization_id = c.organization_id
AND b.inventory_item_id = V_inventory_item_id
AND b.organization_id = V_organization_id
AND c.lot_number = V_lot_number;
SELECT material_detail_id
FROM gmd_material_details_gtmp
WHERE entity_id = V_entity_id
AND line_type <> 3 ;
SELECT formula_id
FROM gmd_recipes r, gmd_recipe_validity_rules v
WHERE recipe_validity_rule_id = V_validity_rule_id
AND r.recipe_id = v.recipe_id;
/* Inserting the item and lot data from batch material tables to temp tables*/
l_user_id := TO_NUMBER (fnd_profile.VALUE ('USER_ID'));
INSERT INTO GMD_MATERIAL_DETAILS_GTMP
(ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,TRACKING_QUANTITY_IND,LOCATION_CONTROL_CODE,
INVENTORY_ITEM_ID,DESCRIPTION,EXPAND_IND,QTY,SECONDARY_QTY,DETAIL_UOM,
GRADE_CODE,PRIMARY_UOM,SECONDARY_UOM,LOT_CONTROL_CODE,ORGANIZATION_ID,
GRADE_CONTROL_FLAG,REVISION,TEXT_CODE,ORGINAL_TEXT_CODE,MATERIAL_DETAIL_ID,FORMULALINE_ID,
PARENT_LINE_ID,ACTION_CODE,TPFORMULA_ID,BATCHSTEP_NO,MOVE_ORDER_LINE_ID,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,REVISION_QTY_CONTROL_CODE,
SECONDARY_DEFAULT_IND)
VALUES
(l_batch_rec.batch_id,l_line_id,l_batch_rec.line_type,l_batch_rec.line_no,1,
l_batch_rec.tracking_quantity_ind,l_batch_rec.location_control_code,
l_batch_rec.inventory_item_id,l_batch_rec.description,
1,l_qty,l_secondary_qty,l_batch_rec.dtl_um,l_batch_rec.default_grade,l_batch_rec.primary,l_batch_rec.secondary,
l_batch_rec.lot_control_code,l_batch_rec.organization_id,l_batch_rec.grade_control_flag,
l_batch_rec.revision,l_batch_rec.text_code,l_batch_rec.text_code,l_batch_rec.material_detail_id,
l_batch_rec.formulaline_id,l_batch_rec.material_detail_id,'NONE',l_tpformula_id,
l_batch_rec.batchstep_no,l_batch_rec.move_order_line_id,l_batch_rec.created_by,l_batch_rec.creation_date,
l_batch_rec.last_updated_by,l_batch_rec.last_update_date,
l_batch_rec.revision_qty_control_code, l_batch_rec.secondary_default_ind);
INSERT INTO GMD_MATERIAL_DETAILS_GTMP
(ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,INVENTORY_ITEM_ID,EXPAND_IND,
EXPIRATION_DATE,LOT_NUMBER,QTY,PRIMARY_QTY,PRIMARY_UOM,SECONDARY_QTY,SECONDARY_UOM,
DETAIL_UOM,GRADE_CODE,TRACKING_QUANTITY_IND,LOCATION_CONTROL_CODE,LOT_CONTROL_CODE,
GRADE_CONTROL_FLAG,SUBINVENTORY_CODE,MATERIAL_DETAIL_ID,TRANSACTION_ID,
PARENT_LINE_ID,ACTION_CODE,CREATED_BY,CREATION_DATE,LOCATOR_ID,
LAST_UPDATED_BY,LAST_UPDATE_DATE,SECONDARY_DEFAULT_IND,ORGANIZATION_ID)
VALUES
(V_batch_id,l_line_id,3,l_line_no,0,l_mmln_tbl(j).inventory_item_id,0,
l_control.expiration_date,l_mmln_tbl(j).lot_number,
ABS(l_mmln_tbl(j).transaction_quantity),ABS(l_mmln_tbl(j).primary_quantity),
l_mmt_tbl(i).transaction_uom,ABS(l_mmln_tbl(j).secondary_transaction_quantity),
l_mmt_tbl(i).secondary_uom_code,l_batch_rec.dtl_um,l_mmln_tbl(j).grade_code,
l_control.tracking_quantity_ind,l_control.location_control_code,
l_control.lot_control_code,l_control.grade_control_flag,
l_mmt_tbl(i).subinventory_code,l_batch_rec.material_detail_id,
l_mmt_tbl(i).transaction_id,l_batch_rec.material_detail_id,
'NONE',l_user_id,sysdate,l_mmt_tbl(i).locator_id,
l_user_id,sysdate,l_control.secondary_default_ind,l_mmln_tbl(j).organization_id);
/* based on update_inventory_ind data will be loaded either from reservations table
or pending lots table*/
IF (V_update_inv_ind = 'Y') THEN
--Load Reservations
gme_reservations_pvt.get_material_reservations (p_organization_id => l_batch_rec.organization_id
,p_batch_id => V_batch_id
,p_material_detail_id => l_batch_rec.material_detail_id
,x_return_status => l_status
,x_reservations_tbl => l_reservations_tbl);
INSERT INTO GMD_MATERIAL_DETAILS_GTMP
(ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,INVENTORY_ITEM_ID,EXPAND_IND,
EXPIRATION_DATE,LOT_NUMBER,QTY,PRIMARY_QTY,PRIMARY_UOM,SECONDARY_QTY,SECONDARY_UOM,
DETAIL_UOM,GRADE_CODE,TRACKING_QUANTITY_IND,LOCATION_CONTROL_CODE,LOT_CONTROL_CODE,
GRADE_CONTROL_FLAG,SUBINVENTORY_CODE,MATERIAL_DETAIL_ID,RESERVATION_ID,
PARENT_LINE_ID,ACTION_CODE,CREATED_BY,CREATION_DATE,LOCATOR_ID,
LAST_UPDATED_BY,LAST_UPDATE_DATE,SECONDARY_DEFAULT_IND,ORGANIZATION_ID)
VALUES
(V_batch_id,l_line_id,3,l_line_no,0,l_reservations_tbl(i).inventory_item_id,0,
l_control.expiration_date,l_reservations_tbl(i).lot_number,
l_rsv_qty,l_reservations_tbl(i).primary_reservation_quantity,
l_reservations_tbl(i).primary_uom_code,l_reservations_tbl(i).secondary_reservation_quantity,
l_reservations_tbl(i).secondary_uom_code,l_batch_rec.dtl_um,l_control.default_grade,
l_control.tracking_quantity_ind,l_control.location_control_code,
l_control.lot_control_code,l_control.grade_control_flag,
l_reservations_tbl(i).subinventory_code,l_batch_rec.material_detail_id,
l_reservations_tbl(i).reservation_id,l_batch_rec.material_detail_id,
'NONE',l_user_id,sysdate,l_reservations_tbl(i).locator_id,l_user_id,sysdate,l_control.secondary_default_ind,l_control.organization_id);
INSERT INTO GMD_MATERIAL_DETAILS_GTMP
(ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,INVENTORY_ITEM_ID,EXPAND_IND,
EXPIRATION_DATE,SECONDARY_UOM,LOT_NUMBER,QTY,PRIMARY_QTY,PRIMARY_UOM,SECONDARY_QTY,
DETAIL_UOM,TRACKING_QUANTITY_IND,LOCATION_CONTROL_CODE,LOT_CONTROL_CODE,
GRADE_CONTROL_FLAG,MATERIAL_DETAIL_ID,PARENT_LINE_ID,ACTION_CODE,CREATED_BY,
CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,SECONDARY_DEFAULT_IND,
ORGANIZATION_ID,LOCATOR_ID,TRANS_ID)
VALUES
(V_batch_id,l_line_id,3,l_line_no,0,l_labrec.inventory_item_id,0,l_labrec.expiration_date,
l_labrec.secondary_uom,l_labrec.lot_number,l_labrec.quantity,l_primary_qty,l_labrec.primary_uom,
l_labrec.secondary_quantity,l_labrec.detail_uom,l_labrec.tracking_quantity_ind,
l_labrec.location_control_code,l_labrec.lot_control_code,l_labrec.grade_control_flag,
l_labrec.material_detail_id,l_labrec.material_detail_id,'NONE',
l_user_id,sysdate,l_user_id,sysdate,l_labrec.secondary_default_ind,
l_labrec.organization_id,l_labrec.locator_id,l_labrec.pending_product_lot_id);
# This procedure inserts the data into temp tables and will
# be fetched in the form.
# HISTORY
# Kapil M 12-FEB-2007 Bug# 5716318 : Auto-Prod Calcualtion ME
# Added the newly added column - prod_percent to insert into temp tables
###############################################################*/
PROCEDURE load_formula_details (V_entity_id IN NUMBER, V_formula_id IN NUMBER,
V_orgn_id IN NUMBER, V_plant_id IN NUMBER) IS
CURSOR Cur_get_formula IS
SELECT a.*,b.description,b.default_grade,
b.primary_uom_code primary,b.secondary_uom_code secondary,
b.lot_control_code,b.revision_qty_control_code,b.secondary_default_ind,
b.grade_control_flag,b.tracking_quantity_ind,b.location_control_code
FROM fm_matl_dtl a, mtl_system_items_b b
WHERE a.inventory_item_id = b.inventory_item_id
AND b.organization_id = a.organization_id
AND a.formula_id = V_entity_id
AND (a.line_type <> 1 OR a.line_no = 1)
ORDER BY a.line_type, a.line_no;
SELECT a.*, b.lot_number lot,b.expiration_date expire, b.inventory_item_id itemid
FROM gmd_material_details_gtmp a, mtl_lot_numbers b, (select inventory_item_id,lot_number, lot_organization_id
from gmd_technical_data_vl group by inventory_item_id,lot_number,lot_organization_id) c
WHERE a.entity_id = V_formula_id
AND a.inventory_item_id = b.inventory_item_id
AND a.inventory_item_id = c.inventory_item_id
AND b.organization_id = c.lot_organization_id
AND b.lot_number = c.lot_number --Added this condition for Bug10633233
AND a.lot_control_code = 2
AND (a.line_type <> 1)
ORDER BY a.formulaline_id,b.lot_number;
SELECT formulaline_id
FROM gmd_material_details_gtmp
WHERE entity_id = V_entity_id
AND line_type <> 3 ;
SELECT formula_id
FROM gmd_recipes r, gmd_recipe_validity_rules v
WHERE recipe_validity_rule_id = V_validity_rule_id
AND r.recipe_id = v.recipe_id;
/* Inserting the item and lot data from formula detail tables to temp tables*/
IF (V_formula_id IS NOT NULL) THEN
OPEN Cur_get_formula;
SELECT a.formula_id INTO l_tpformula_id
FROM
(SELECT a.formula_id
FROM Fm_form_mst a, fm_matl_dtl b, gmd_technical_data_hdr g
WHERE b.item_id = l_formula_rec.item_id
AND a.formula_id = b.formula_id
AND g.item_id = b.item_id
AND g.formula_id = a.formula_id
AND g.organization_id = V_orgn_id
AND a.formula_id <>0
AND b.line_type = 1
AND a.delete_mark =0
AND g.delete_mark =0
ORDER BY a.formula_id) a
WHERE rownum < 2;
INSERT INTO GMD_MATERIAL_DETAILS_GTMP
(ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,EXPAND_IND,TRACKING_QUANTITY_IND,
LOCATION_CONTROL_CODE,INVENTORY_ITEM_ID,DESCRIPTION,TPFORMULA_ID,
QTY,SECONDARY_QTY,DETAIL_UOM,GRADE_CODE,PRIMARY_UOM,SECONDARY_UOM,LOT_CONTROL_CODE,
GRADE_CONTROL_FLAG,REVISION,TEXT_CODE,ORGINAL_TEXT_CODE,FORMULALINE_ID,PARENT_LINE_ID,
ACTION_CODE,BUFFER_IND,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,
REVISION_QTY_CONTROL_CODE,ORGANIZATION_ID,SECONDARY_DEFAULT_IND,PROD_PERCENT)
VALUES
(l_formula_rec.formula_id,l_line_id,l_formula_rec.line_type,l_formula_rec.line_no,1,1,
l_formula_rec.tracking_quantity_ind,l_formula_rec.location_control_code,
l_formula_rec.inventory_item_id,l_formula_rec.description,
l_tpformula_id,l_formula_rec.qty,l_secondary_qty,l_formula_rec.detail_uom,
l_formula_rec.default_grade,l_formula_rec.primary,l_formula_rec.secondary,
l_formula_rec.lot_control_code,l_formula_rec.grade_control_flag,
l_formula_rec.revision,l_formula_rec.text_code,l_formula_rec.text_code,l_formula_rec.formulaline_id,
l_formula_rec.formulaline_id,'NONE',l_formula_rec.buffer_ind,l_formula_rec.created_by,
l_formula_rec.creation_date,l_formula_rec.last_updated_by,
l_formula_rec.last_update_date,l_formula_rec.revision_qty_control_code,
l_formula_rec.organization_id, l_formula_rec.secondary_default_ind,l_formula_rec.prod_percent);
INSERT INTO GMD_MATERIAL_DETAILS_GTMP
(ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,TRACKING_QUANTITY_IND,LOCATION_CONTROL_CODE,
INVENTORY_ITEM_ID,EXPAND_IND,EXPIRATION_DATE,LOT_NUMBER,QTY,SECONDARY_QTY,
DETAIL_UOM,GRADE_CODE,FORMULALINE_ID,PARENT_LINE_ID,LOT_CONTROL_CODE,
GRADE_CONTROL_FLAG,ACTION_CODE,SECONDARY_UOM,SECONDARY_DEFAULT_IND,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,ORGANIZATION_ID,PROD_PERCENT)
VALUES
(l_matl_rec.entity_id,l_line_id,3,l_line_no,0,l_matl_rec.tracking_quantity_ind,
l_matl_rec.location_control_code,l_matl_rec.inventory_item_id,0,l_matl_rec.expiration_date,
l_matl_rec.lot,0,l_secondary_qty,l_matl_rec.detail_uom,l_matl_rec.grade_code,
l_matl_rec.formulaline_id,l_matl_rec.formulaline_id,l_matl_rec.lot_control_code,
l_matl_rec.grade_control_flag,'NONE',l_matl_rec.secondary_uom,l_matl_rec.secondary_default_ind,
l_matl_rec.created_by,l_matl_rec.creation_date,
l_matl_rec.last_updated_by,l_matl_rec.last_update_date,l_matl_rec.organization_id,l_formula_rec.prod_percent);
# This procedure inserts the data into temp tables for tech
# parameters .
###############################################################*/
PROCEDURE load_tech_params (V_entity_id IN NUMBER,V_sprd_id IN NUMBER,V_batch_id IN NUMBER,
V_orgn_id IN NUMBER,V_folder_name IN VARCHAR2,
V_inv_item_id IN NUMBER,V_formula_id IN NUMBER) IS
CURSOR Cur_get_prod IS
SELECT inventory_item_id
FROM fm_matl_dtl
WHERE formula_id = V_entity_id
AND line_type = 1
AND line_no = 1;
SELECT inventory_item_id
FROM gme_material_details
WHERE batch_id = V_entity_id
AND line_type = 1
AND line_no = 1;
SELECT inventory_item_id
FROM gmd_lcf_details_gtmp
WHERE entity_id = V_entity_id
AND line_type = 1
AND line_no = 1;
SELECT category_id
FROM mtl_item_categories
WHERE category_set_id = l_category_set_id
AND inventory_item_id = l_item_id;
SELECT count(*)
FROM gmd_technical_sequence_vl
WHERE organization_id = V_orgn_id
AND inventory_item_id = l_item_id
AND delete_mark = 0;
SELECT count(*)
FROM gmd_technical_sequence_vl
WHERE category_id = l_category_id
AND organization_id = V_orgn_id
AND delete_mark = 0;
SELECT c.*, b.sequence
FROM fnd_folders a, fnd_folder_columns b, lm_tech_hdr c
WHERE a.folder_id = b.folder_id
AND b.item_prompt = c.tech_parm_name
AND a.name = V_folder_name
AND a.OBJECT = 'SPREAD_DTL_SUB'
AND c.organization_id = V_orgn_id;*/
SELECT c.*, b.sequence
FROM fnd_folders a,
fnd_folder_columns b, lm_tech_hdr c, gmd_technical_sequence_vl s
WHERE a.folder_id(+) = b.folder_id
AND c.tech_parm_name = b.item_prompt(+)
AND a.name(+) = V_folder_name --- Bug 4254315 ....
AND a.OBJECT(+) = 'SPREAD_DTL_SUB'
AND c.organization_id = V_orgn_id
AND c.tech_parm_id = s.tech_parm_id
AND c.organization_id = s.organization_id
AND NVL(l_item_id, 1) = NVL(s.inventory_item_id,1)
AND NVL(s.category_id, 1) = NVL(l_category_id ,1)
UNION
SELECT c.*, b.sequence
FROM fnd_folders a,
fnd_folder_columns b, lm_tech_hdr c, gmd_technical_sequence_vl s
WHERE a.folder_id(+) = b.folder_id
AND c.tech_parm_name = b.item_prompt(+)
AND a.name(+) = V_folder_name --- Bug 4254315 ....
AND a.OBJECT(+) = 'SPREAD_DTL_SUB'
AND c.organization_id = V_orgn_id
AND c.tech_parm_id = s.tech_parm_id
AND c.organization_id = s.organization_id
AND s.inventory_item_id is NULL
AND NVL(s.category_id, 1) = NVL(l_category_id ,1)
ORDER BY sequence ;
DELETE FROM gmd_technical_parameter_gtmp;
INSERT INTO GMD_TECHNICAL_PARAMETER_GTMP
(ENTITY_ID,TECH_PARM_NAME,TECH_PARM_ID,PARM_DESCRIPTION,SORT_SEQ,QCASSY_TYP_ID,
DATA_TYPE,EXPRESSION_CHAR,LM_UNIT_CODE,SIGNIF_FIGURES,LOWERBOUND_NUM,
UPPERBOUND_NUM,LOWERBOUND_CHAR,UPPERBOUND_CHAR,MAX_LENGTH)
VALUES (V_entity_id,l_rec.tech_parm_name,l_rec.tech_parm_id,l_rec.parm_description,l_rec.sequence,l_rec.qcassy_typ_id,
l_rec.data_type,l_rec.expression_char,l_rec.lm_unit_code,
DECODE(l_rec.data_type, 4, NVL(l_rec.signif_figures, 0 ), 11, NVL(l_rec.signif_figures, 0 ),l_rec.signif_figures ),
l_rec.lowerbound_num,l_rec.upperbound_num,l_rec.lowerbound_char,l_rec.upperbound_char,l_rec.max_length);
INSERT INTO GMD_TECHNICAL_PARAMETER_GTMP
(ENTITY_ID,TECH_PARM_NAME,TECH_PARM_ID,PARM_DESCRIPTION,SORT_SEQ,QCASSY_TYP_ID,DATA_TYPE,EXPRESSION_CHAR,
LM_UNIT_CODE,SIGNIF_FIGURES,LOWERBOUND_NUM,UPPERBOUND_NUM,OPTIMIZE_TYPE,LOWERBOUND_CHAR,UPPERBOUND_CHAR,MAX_LENGTH)
SELECT a.sprd_id,a.tech_parm_name,b.tech_parm_id,b.parm_description,a.sort_seq,b.qcassy_typ_id,
b.data_type,b.expression_char,b.lm_unit_code,
DECODE(b.data_type, 4, NVL(b.signif_figures, 0 ), 11, NVL(b.signif_figures, 0 ),b.signif_figures ),
a.min_value,a.max_value,a.optimize_type,
b.lowerbound_char,b.upperbound_char,b.max_length
FROM lm_sprd_prm a, lm_tech_hdr b
WHERE a.tech_parm_id = b.tech_parm_id
AND a.sprd_id = V_entity_id
AND a.organization_id = V_orgn_id;
INSERT INTO GMD_TECHNICAL_PARAMETER_GTMP
(ENTITY_ID,TECH_PARM_NAME,TECH_PARM_ID,PARM_DESCRIPTION,SORT_SEQ,QCASSY_TYP_ID,DATA_TYPE,EXPRESSION_CHAR,
LM_UNIT_CODE,SIGNIF_FIGURES,LOWERBOUND_NUM,UPPERBOUND_NUM,LOWERBOUND_CHAR,UPPERBOUND_CHAR,MAX_LENGTH)
SELECT V_entity_id,a.tech_parm_name,b.tech_parm_id,b.parm_description,a.sort_seq,
b.qcassy_typ_id,b.data_type,b.expression_char,b.lm_unit_code,
DECODE(b.data_type, 4, NVL(b.signif_figures, 0 ), 11, NVL(b.signif_figures, 0 ),b.signif_figures ),
b.lowerbound_num,b.upperbound_num,
b.lowerbound_char,b.upperbound_char,b.max_length
FROM gmd_technical_sequence_vl a, lm_tech_hdr b
WHERE a.tech_parm_id = b.tech_parm_id
AND a.inventory_item_id = l_item_id
AND a.organization_id = V_orgn_id;
INSERT INTO GMD_TECHNICAL_PARAMETER_GTMP
(ENTITY_ID,TECH_PARM_NAME,TECH_PARM_ID,PARM_DESCRIPTION,SORT_SEQ,QCASSY_TYP_ID,DATA_TYPE,EXPRESSION_CHAR,
LM_UNIT_CODE,SIGNIF_FIGURES,LOWERBOUND_NUM,UPPERBOUND_NUM,LOWERBOUND_CHAR,UPPERBOUND_CHAR,MAX_LENGTH)
SELECT V_entity_id,a.tech_parm_name,b.tech_parm_id,b.parm_description,a.sort_seq,
b.qcassy_typ_id,b.data_type,b.expression_char,b.lm_unit_code,
DECODE(b.data_type, 4, NVL(b.signif_figures, 0 ), 11, NVL(b.signif_figures, 0 ),b.signif_figures ),
b.lowerbound_num,b.upperbound_num,
b.lowerbound_char,b.upperbound_char,b.max_length
FROM gmd_technical_sequence_vl a, lm_tech_hdr b
WHERE a.tech_parm_id = b.tech_parm_id
AND a.category_id = l_category_id
AND a.organization_id = V_orgn_id;
INSERT INTO GMD_TECHNICAL_PARAMETER_GTMP
(ENTITY_ID,TECH_PARM_NAME,TECH_PARM_ID,PARM_DESCRIPTION,SORT_SEQ,QCASSY_TYP_ID,DATA_TYPE,EXPRESSION_CHAR,
LM_UNIT_CODE,SIGNIF_FIGURES,LOWERBOUND_NUM,UPPERBOUND_NUM,LOWERBOUND_CHAR,UPPERBOUND_CHAR,MAX_LENGTH)
SELECT V_entity_id,a.tech_parm_name,b.tech_parm_id,b.parm_description,a.sort_seq,
b.qcassy_typ_id,b.data_type,b.expression_char,b.lm_unit_code,
DECODE(b.data_type, 4, NVL(b.signif_figures, 0 ), 11, NVL(b.signif_figures, 0 ),b.signif_figures ),
b.lowerbound_num,b.upperbound_num,
b.lowerbound_char,b.upperbound_char,b.max_length
FROM gmd_technical_sequence_vl a, lm_tech_hdr b
WHERE a.tech_parm_id = b.tech_parm_id
AND a.organization_id = V_orgn_id
AND a.inventory_item_id IS NULL
AND a.category_id IS NULL;
# This procedure inserts the data into temp tables and will
# be fetched in the form.
###############################################################*/
PROCEDURE add_new_line (V_entity_id IN NUMBER, V_inv_item_id IN NUMBER, V_line_type IN NUMBER,
V_line_no IN NUMBER, V_source_ind IN NUMBER,V_formula_id IN NUMBER,V_move_order_header_id IN NUMBER,
V_orgn_id IN NUMBER, X_line_id OUT NOCOPY NUMBER,
X_parent_line_id OUT NOCOPY NUMBER,X_move_order_line_id OUT NOCOPY NUMBER,
V_plant_id IN NUMBER) IS
CURSOR Cur_get_item IS
SELECT b.description,b.default_grade,b.secondary_default_ind,
b.primary_uom_code,b.secondary_uom_code,b.tracking_quantity_ind,
b.lot_control_code,b.grade_control_flag,b.location_control_code,
b.revision_qty_control_code,b.mtl_transactions_enabled_flag
FROM mtl_system_items_b b
WHERE b.inventory_item_id = V_inv_item_id
AND b.organization_id = V_orgn_id;
SELECT MAX(line_id)
FROM gmd_material_details_gtmp
WHERE entity_id = V_entity_id;
SELECT gem5_formulaline_id_s.NEXTVAL
FROM dual;
SELECT gem5_sprd_line_id_s.nextval
FROM dual;
SELECT *
FROM gmd_material_details_gtmp
WHERE line_id = Pline_id;
SELECT *
FROM gme_batch_header
WHERE batch_id = V_entity_id;
/* Inserting the item and lot data for the item entered in the form*/
OPEN Cur_line_id;
INSERT INTO GMD_MATERIAL_DETAILS_GTMP
(ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,INVENTORY_ITEM_ID,
DESCRIPTION,EXPAND_IND,QTY,DETAIL_UOM,GRADE_CODE,PRIMARY_UOM,SECONDARY_UOM,TRACKING_QUANTITY_IND,SECONDARY_DEFAULT_IND,
LOT_CONTROL_CODE,GRADE_CONTROL_FLAG,LOCATION_CONTROL_CODE,TEXT_CODE,ORGINAL_TEXT_CODE,FORMULALINE_ID,
MATERIAL_DETAIL_ID,PARENT_LINE_ID,ACTION_CODE,CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,
LAST_UPDATE_DATE,REVISION_QTY_CONTROL_CODE,ORGANIZATION_ID)
VALUES
(V_entity_id,l_line_id,V_line_type,V_line_no,1,V_inv_item_id,
l_item_rec.description,1,0,l_item_rec.primary_uom_code,l_item_rec.default_grade,l_item_rec.primary_uom_code,
l_item_rec.secondary_uom_code,l_item_rec.tracking_quantity_ind,l_item_rec.secondary_default_ind,
l_item_rec.lot_control_code,l_item_rec.grade_control_flag,l_item_rec.location_control_code,
NULL,NULL,l_parentline_id,l_parentline_id,l_parentline_id,'NONE',l_user_id,SYSDATE,
l_user_id,SYSDATE,l_item_rec.revision_qty_control_code,V_orgn_id);
IF (V_line_type = -1 AND l_batch.update_inventory_ind = 'Y'
AND l_item_rec.mtl_transactions_enabled_flag = 'Y') THEN
IF (V_source_ind = 1) THEN
OPEN Cur_get_materials(l_line_id);
l_materials(1).last_update_date := gme_common_pvt.g_timestamp;
l_materials(1).last_updated_by := gme_common_pvt.g_user_ident;
# This procedure inserts the data into temp tables and will
# be fetched in the form.
###############################################################*/
PROCEDURE load_spread_values (V_entity_id IN NUMBER,V_sprd_id IN NUMBER,
V_orgn_id IN NUMBER,V_parent_line_id IN NUMBER) IS
CURSOR Cur_get_line IS
SELECT line_id
FROM gmd_material_details_gtmp
WHERE parent_line_id = V_parent_line_id
ORDER BY line_type;
/* Inserting the technical parameter data of item and lot to temp tables*/
IF (V_sprd_id IS NOT NULL) THEN
INSERT INTO GMD_TECHNICAL_DATA_GTMP
(ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,
VALUE,SORT_SEQ,NUM_DATA,TEXT_DATA,BOOLEAN_DATA)
SELECT a.sprd_id,c.line_id,a.tech_parm_name,a.tech_parm_id,
DECODE(B.DATA_TYPE,0,TEXT_DATA,2,TEXT_DATA,3,BOOLEAN_DATA,NUM_DATA) VALUE,
b.sort_seq,a.num_data,a.text_data,a.boolean_data
FROM lm_sprd_tec a, gmd_technical_parameter_gtmp b, gmd_material_details_gtmp c
WHERE a.tech_parm_id = b.tech_parm_id
AND a.sprd_id = V_entity_id
AND a.line_id = c.sprd_line_id
AND a.organization_id = V_orgn_id
AND c.parent_line_id = V_parent_line_id;
# This procedure inserts the data into temp tables and will
# be fetched in the form.
###############################################################*/
PROCEDURE load_batch_values (V_entity_id IN NUMBER,V_batch_id IN NUMBER,
V_orgn_id IN NUMBER,V_matl_detl_id IN NUMBER,
V_line_id IN NUMBER,V_plant_id IN NUMBER) IS
CURSOR Cur_get_line IS
SELECT line_id
FROM gmd_material_details_gtmp
WHERE parent_line_id = V_matl_detl_id
ORDER BY line_type;
/* Inserting the technical parameter data of item and lot to temp tables*/
IF (V_batch_id IS NOT NULL) THEN
INSERT INTO GMD_TECHNICAL_DATA_GTMP
(ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,
VALUE,SORT_SEQ,NUM_DATA,TEXT_DATA,BOOLEAN_DATA)
SELECT V_entity_id,c.line_id,a.tech_parm_name, a.tech_parm_id,
DECODE(B.DATA_TYPE,0,TEXT_DATA,2,TEXT_DATA,3,BOOLEAN_DATA,NUM_DATA) VALUE,
b.sort_seq,a.num_data,a.text_data,a.boolean_data
FROM gmd_technical_data_vl a, gmd_technical_parameter_gtmp b, gmd_material_details_gtmp c
WHERE a.tech_parm_id = b.tech_parm_id
AND a.organization_id = V_orgn_id
AND a.inventory_item_id = c.inventory_item_id
AND (V_matl_detl_id IS NULL OR c.parent_line_id = V_matl_detl_id)
AND c.entity_id = V_entity_id
AND (V_line_id IS NULL OR c.line_id = V_line_id)
AND (a.batch_id = V_entity_id OR
(a.batch_id IS NULL AND NOT EXISTS ( SELECT 1
FROM GMD_TECHNICAL_DATA_VL e
WHERE e.inventory_item_id = c.inventory_item_id
AND nvl(e.lot_number, '-1') = nvl(c.lot_number, '-1')
AND nvl(e.lot_organization_id, c.organization_id) = c.organization_id
AND e.formula_id IS NULL
AND e.batch_id = V_entity_id
AND e.organization_id = V_orgn_id)))
AND a.formula_id IS NULL
AND a.delete_mark = 0
AND NVL(c.organization_id, -1) = NVL(a.lot_organization_id, c.organization_id)
AND NVL(c.lot_number, '-1') = NVL(a.lot_number, '-1');
# This procedure inserts the data into temp tables and will
# be fetched in the form.
###############################################################*/
PROCEDURE load_formula_values (V_entity_id IN NUMBER,V_formula_id IN NUMBER,
V_orgn_id IN NUMBER,V_formulaline_id IN NUMBER,
V_line_id IN NUMBER,V_plant_id IN NUMBER) IS
CURSOR Cur_get_line IS
SELECT line_id
FROM gmd_material_details_gtmp
WHERE (V_formulaline_id IS NULL OR parent_line_id = V_formulaline_id)
ORDER BY line_type;
/* Inserting the technical parameter data of item and lot to temp tables*/
IF (V_formula_id IS NOT NULL) THEN
INSERT INTO GMD_TECHNICAL_DATA_GTMP
(ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,
VALUE,SORT_SEQ,NUM_DATA,TEXT_DATA,BOOLEAN_DATA)
SELECT V_entity_id,c.line_id,a.tech_parm_name,a.tech_parm_id,
DECODE(B.DATA_TYPE,0,TEXT_DATA,2,TEXT_DATA,3,BOOLEAN_DATA,NUM_DATA) VALUE,
b.sort_seq,a.num_data,a.text_data,a.boolean_data
FROM gmd_technical_data_vl a, gmd_technical_parameter_gtmp b, gmd_material_details_gtmp c
WHERE a.tech_parm_id = b.tech_parm_id
AND a.organization_id = V_orgn_id
AND a.inventory_item_id = c.inventory_item_id
AND (V_formulaline_id IS NULL OR c.parent_line_id = V_formulaline_id)
AND c.entity_id = V_entity_id
AND (V_line_id IS NULL OR c.line_id = V_line_id)
AND (a.formula_id = c.tpformula_id OR
(a.formula_id IS NULL AND NOT EXISTS (SELECT 1
FROM GMD_TECHNICAL_DATA_VL e
WHERE e.inventory_item_id = c.inventory_item_id
AND NVL(e.lot_number, '-1') = NVL(c.lot_number, '-1')
AND NVL(e.lot_organization_id, c.organization_id) = c.organization_id
AND e.batch_id IS NULL
AND e.formula_id = c.tpformula_id
AND e.organization_id = V_orgn_id)))
AND a.batch_id IS NULL
AND a.delete_mark = 0
AND NVL(c.organization_id, -1) = NVL(a.lot_organization_id, c.organization_id)
AND NVL(c.lot_number, '-1') = NVL(a.lot_number, '-1');
# This procedure inserts the data into spreadsheet tables.
###############################################################*/
PROCEDURE save_spreadsheet (V_entity_id IN NUMBER,V_sprd_id IN NUMBER,
V_formula_id IN NUMBER,V_batch_id IN NUMBER,
V_orgn_id IN NUMBER,V_spread_name IN VARCHAR2,
V_maintain_type IN NUMBER,V_text_code IN NUMBER,
V_last_update_date IN DATE,V_move_order_header_id IN NUMBER) IS
CURSOR Cur_sprd_id IS
SELECT gem5_sprd_id_s.nextval
FROM fnd_dual;
SELECT gem5_sprd_line_id_s.nextval
FROM fnd_dual;
CURSOR Cur_sprd_insert IS
SELECT line_id,V_orgn_id,move_order_line_id,line_type,formulaline_id,
material_detail_id,line_no,rollup_ind,inventory_item_id,qty,detail_uom,
text_code,subinventory_code,location,locator_id,lot_number,expiration_date,grade_code,
transaction_id,reservation_id,secondary_qty,secondary_uom,buffer_ind,revision,
revision_qty_control_code,plant_organization_id,organization_id,
tpformula_id,last_updated_by,last_update_date,created_by,creation_date,prod_percent--added tpformula_id for Bug#7254259
FROM gmd_material_details_gtmp
WHERE entity_id = V_entity_id;
UPDATE lm_sprd_fls
SET lab_organization_id = V_orgn_id,
formula_id = V_formula_id,
batch_id = V_batch_id,
move_order_header_id = V_move_order_header_id,
maintain_type = V_maintain_type,
last_update_date = V_last_update_date,
last_updated_by = l_user_id,
text_code = V_text_code
WHERE sprd_name = V_spread_name;
DELETE FROM lm_sprd_dtl
WHERE sprd_id = V_sprd_id;
DELETE FROM lm_sprd_tec
WHERE sprd_id = V_sprd_id;
DELETE FROM lm_sprd_prm
WHERE sprd_id = V_sprd_id;
INSERT INTO lm_sprd_fls (sprd_id, sprd_name, formula_id,batch_id, lab_organization_id, maintain_type,
active_ind, delete_mark, creation_date,
last_update_date, created_by,
last_updated_by, text_code, in_use,move_order_header_id)
VALUES (X_sprd_id, V_spread_name,
V_formula_id,V_batch_id,
V_orgn_id,V_maintain_type,1,0,V_last_update_date,
V_last_update_date,l_user_id,
l_user_id, V_text_code, 0, V_move_order_header_id);
INSERT INTO lm_sprd_prm (sprd_id,organization_id,tech_parm_name,tech_parm_id,sort_seq,data_type,
expression_char,min_value,max_value,precision,optimize_type,
last_updated_by,last_update_date,created_by,creation_date)
SELECT X_sprd_id, V_orgn_id,tech_parm_name,tech_parm_id,sort_seq,data_type,
expression_char,lowerbound_num,upperbound_num,signif_figures,optimize_type,
l_user_id,sysdate,l_user_id,sysdate
FROM gmd_technical_parameter_gtmp
WHERE entity_id = V_entity_id;
FOR l_rec IN Cur_sprd_insert LOOP
OPEN Cur_line_id;
INSERT INTO lm_sprd_dtl (line_id,sprd_id,move_order_line_id,line_type,formulaline_id,material_detail_id,line_no,
rollup_ind,inventory_item_id,qty,detail_uom,revision,revision_qty_control_code,text_code,subinventory_code,
location,lot_number,expiration_date,grade_code,transaction_id,reservation_id,secondary_qty,secondary_uom,
buffer_ind, plant_organization_id,organization_id,tpformula_id, last_updated_by,last_update_date,--added tpformula_id for Bug#7254259
created_by,creation_date,locator_id,prod_percent)
VALUES
(X_line_id,x_sprd_id,l_rec.move_order_line_id,l_rec.line_type,l_rec.formulaline_id,
l_rec.material_detail_id,l_rec.line_no,l_rec.rollup_ind,l_rec.inventory_item_id,
l_rec.qty,l_rec.detail_uom,l_rec.revision,l_rec.revision_qty_control_code,l_rec.text_code,
l_rec.subinventory_code,l_rec.location,l_rec.lot_number,l_rec.expiration_date,
l_rec.grade_code,l_rec.transaction_id,l_rec.reservation_id,l_rec.secondary_qty,
l_rec.secondary_uom,l_rec.buffer_ind,l_rec.plant_organization_id,
l_rec.organization_id,l_rec.tpformula_id,l_rec.last_updated_by,l_rec.last_update_date,--added l_rec.tpformula_id for Bug#7254259
l_rec.created_by,l_rec.creation_date,l_rec.locator_id,l_rec.prod_percent);
INSERT INTO lm_sprd_tec (line_id,organization_id,tech_parm_name,tech_parm_id,sprd_id,sort_seq,num_data,text_data,
boolean_data,last_updated_by,last_update_date,created_by,creation_date)
SELECT X_line_id, V_orgn_id, tech_parm_name,tech_parm_id,
X_sprd_id,sort_seq,num_data,text_data,boolean_data,
l_user_id,sysdate,l_user_id,sysdate
FROM gmd_technical_data_gtmp
WHERE entity_id = V_entity_id
AND line_id = l_rec.line_id;
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_material_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_material_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_SPREAD_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_material_details_gtmp
WHERE rollup_ind = 1
AND line_type <> 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;
# 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_plant_id IN NUMBER) IS
CURSOR Cur_get_qmdata IS
SELECT *
FROM gmd_technical_parameter_gtmp
WHERE qcassy_typ_id IS NOT NULL;
SELECT *
FROM gmd_material_details_gtmp
WHERE line_id = V_line_id;
SELECT value
FROM gmd_technical_data_gtmp
WHERE line_id = Pline_id
AND tech_parm_id = Pparm_id;
UPDATE gmd_technical_data_gtmp
SET value = l_inv_val_out_rec_type.entity_value,
num_data = l_num_value,
qm_entity_id = l_inv_val_out_rec_type.entity_id,
qm_level = l_inv_val_out_rec_type.level,
text_data = l_char_value
WHERE line_id = V_line_id
AND tech_parm_id = l_qmrec.tech_parm_id;
INSERT INTO GMD_TECHNICAL_DATA_GTMP
(ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,VALUE,SORT_SEQ,NUM_DATA,
TEXT_DATA,QM_ENTITY_ID,QM_LEVEL,COMP_IND,MIN_VALUE,MAX_VALUE,SPEC_ID)
VALUES (l_rec.entity_id,V_line_id,l_qmrec.tech_parm_name,l_qmrec.tech_parm_id,l_inv_val_out_rec_type.entity_value,
l_qmrec.sort_seq,l_num_value,l_char_value,
l_inv_val_out_rec_type.entity_id,l_inv_val_out_rec_type.level,l_inv_val_out_rec_type.composite_ind,
l_inv_val_out_rec_type.entity_min_value,l_inv_val_out_rec_type.entity_max_value,l_inv_val_out_rec_type.spec_id);
UPDATE gmd_technical_data_gtmp
SET value = l_inv_val_out_rec_type.entity_value,
num_data = l_num_value,
qm_entity_id = l_inv_val_out_rec_type.entity_id,
qm_level = l_inv_val_out_rec_type.level,
text_data = l_char_value
WHERE line_id = V_line_id
AND tech_parm_id = l_qmrec.tech_parm_id;
SELECT tech_parm_id
FROM gmd_tech_parameters_b
WHERE tech_parm_name = v_density
AND organization_id = P_orgn_id;
SELECT a.value
FROM gmd_technical_data_gtmp a, gmd_material_details_gtmp c
WHERE a.tech_parm_id = v_tech_parm_id
AND c.parent_line_id = P_parent_detl_id
AND a.line_id = c.line_id
AND c.line_type <> 3;
SELECT line_id
FROM gmd_material_details_gtmp p
WHERE parent_line_id = P_parent_detl_id
AND line_type = 3
AND NOT EXISTS ( SELECT 1
FROM gmd_technical_data_gtmp g
WHERE p.line_id = g.line_id
AND g.tech_parm_id = v_tech_parm_id);
INSERT INTO gmd_technical_data_gtmp
(entity_id, line_id, tech_parm_name, tech_parm_id,
Value, sort_seq, num_data, TEXT_DATA, BOOLEAN_DATA)
VALUES
(P_entity_id, l_rec.line_id, l_density, l_tech_parm_id,
l_value, 1, l_value, NULL, NULL);
# This procedure inserts the data into temp tables and will
# be fetched in the form.
###############################################################*/
PROCEDURE load_lcf_details (V_entity_id IN NUMBER,
V_orgn_id IN NUMBER,
V_plant_id IN NUMBER) IS
CURSOR Cur_get_lcf IS
SELECT a.*,b.description descrip, b.default_grade,
b.primary_uom_code primary,b.secondary_uom_code secondary,
b.lot_control_code,b.secondary_default_ind,
b.grade_control_flag,b.tracking_quantity_ind,b.location_control_code
FROM gmd_lcf_details_gtmp a, mtl_system_items_b b
WHERE a.inventory_item_id = b.inventory_item_id
AND b.organization_id = V_orgn_id
ORDER BY a.line_type, a.line_no;
SELECT formulaline_id
FROM gmd_material_details_gtmp
WHERE entity_id = V_entity_id;
/* Inserting the item and lot data from formula detail tables to temp tables*/
IF (V_orgn_id IS NOT NULL) THEN
OPEN Cur_get_lcf;
INSERT INTO GMD_MATERIAL_DETAILS_GTMP
(ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,EXPAND_IND,TRACKING_QUANTITY_IND,
LOCATION_CONTROL_CODE,INVENTORY_ITEM_ID,DESCRIPTION,
QTY,SECONDARY_QTY,DETAIL_UOM,GRADE_CODE,PRIMARY_UOM,SECONDARY_UOM,LOT_CONTROL_CODE,
GRADE_CONTROL_FLAG,FORMULALINE_ID,PARENT_LINE_ID,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,
ORGANIZATION_ID,SECONDARY_DEFAULT_IND)
VALUES
(V_entity_id,l_formula_rec.line_id,l_formula_rec.line_type,l_formula_rec.line_no,1,1,
l_formula_rec.tracking_quantity_ind,l_formula_rec.location_control_code,
l_formula_rec.inventory_item_id,l_formula_rec.descrip,
l_formula_rec.qty,l_secondary_qty,l_formula_rec.detail_uom,
l_formula_rec.default_grade,l_formula_rec.primary,l_formula_rec.secondary,
l_formula_rec.lot_control_code,l_formula_rec.grade_control_flag,
l_formula_rec.formulaline_id,l_formula_rec.line_id,l_formula_rec.created_by,
l_formula_rec.creation_date,l_formula_rec.last_updated_by,
l_formula_rec.last_update_date,V_orgn_id,l_formula_rec.secondary_default_ind);
# This procedure inserts the data into temp tables and will
# be fetched in the form.
###############################################################*/
PROCEDURE load_lcf_values (V_entity_id IN NUMBER,V_orgn_id IN NUMBER,
V_formulaline_id IN NUMBER,V_plant_id IN NUMBER,
V_line_id IN NUMBER) IS
CURSOR Cur_get_line IS
SELECT line_id
FROM gmd_material_details_gtmp
WHERE (V_formulaline_id IS NULL OR parent_line_id = V_formulaline_id)
ORDER BY line_type;
/* Inserting the technical parameter data of item and lot to temp tables*/
IF (V_entity_id IS NOT NULL) THEN
INSERT INTO GMD_TECHNICAL_DATA_GTMP
(ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,
VALUE,SORT_SEQ,NUM_DATA,TEXT_DATA,BOOLEAN_DATA)
SELECT V_entity_id,c.line_id,b.tech_parm_name,a.tech_parm_id,
DECODE(B.DATA_TYPE,0,TEXT_DATA,2,TEXT_DATA,3,BOOLEAN_DATA,NUM_DATA) VALUE,
b.sort_seq,a.num_data,a.text_data,a.boolean_data
FROM gmd_technical_data_vl a, gmd_technical_parameter_gtmp b, gmd_material_details_gtmp c
WHERE a.tech_parm_id = b.tech_parm_id
AND a.organization_id = V_orgn_id
AND a.inventory_item_id = c.inventory_item_id
AND (V_formulaline_id IS NULL OR c.parent_line_id = V_formulaline_id)
AND c.entity_id = V_entity_id
AND (V_line_id IS NULL OR c.line_id = V_line_id)
AND NVL(c.organization_id, -1) = NVL(a.lot_organization_id, c.organization_id)
AND NVL(c.lot_number, '-1') = NVL(a.lot_number, '-1');
# This procedure inserts the data into temp tables and will
# be fetched in the form.
###############################################################*/
PROCEDURE load_derived_cost (V_entity_id IN NUMBER,V_orgn_id IN NUMBER,V_line_id IN NUMBER) IS
CURSOR Cur_get_type IS
SELECT a.*, c.line_id line, b.tech_parm_id tech, b.tech_parm_name name,b.sort_seq,
c.inventory_item_id,c.detail_uom
FROM gmd_tech_parameters_b a, gmd_technical_parameter_gtmp b, gmd_material_details_gtmp c
WHERE a.tech_parm_id = b.tech_parm_id
AND c.line_id = V_line_id;
SELECT primary_uom_code
FROM mtl_system_items_b
WHERE inventory_item_id = V_inv_item_id
AND organization_id = V_orgn_id;
INSERT INTO GMD_TECHNICAL_DATA_GTMP (ENTITY_ID,LINE_ID,TECH_PARM_NAME,
TECH_PARM_ID,VALUE,NUM_DATA,SORT_SEQ)
VALUES (V_entity_id,l_rec.line,l_rec.name,l_rec.tech,l_value,l_value,l_rec.sort_seq);
/* Bug#7254259 (add procedure ,update_formula_details ,Update_Formula_Values ) begin */
PROCEDURE update_formula_details (V_entity_id IN NUMBER, V_line_id IN NUMBER, V_formula_id IN NUMBER,
V_line_type IN NUMBER, V_line_no IN NUMBER,V_orgn_id IN number,V_plant_id IN number) IS
/* CURSOR Cur_get_formula IS
SELECT a.*,b.item_no,b.item_desc1,b.qc_grade,b.item_um primary,b.item_um2 secondary,
b.lot_ctl,b.sublot_ctl,b.grade_ctl,b.loct_ctl,b.dualum_ind
FROM fm_matl_dtl a, ic_item_mst b
WHERE a.item_id = b.item_id
AND a.formula_id = V_formula_id
AND a.line_type = 1
AND a.line_no = V_line_no; */
SELECT a.*,b.segment1 original_item_id, b.primary_uom_code primary,b.secondary_uom_code secondary,b.dual_uom_control dualum_ind
FROM fm_matl_dtl a, mtl_system_items_b b
WHERE a.inventory_item_id = b.inventory_item_id
and b.organization_id = V_orgn_id
AND a.formula_id = V_formula_id
AND a.line_type = 1
AND a.line_no = V_line_no;
SELECT a.*,b.lot_no lot, b.sublot_no sublot, b.lot_id item_lotid, b.expire_date expire, b.item_id itemid
FROM gmd_material_details_gtmp a, ic_lots_mst b, (select item_id,lot_id from gmd_technical_data_vl group by item_id,lot_id) c
WHERE a.entity_id = V_formula_id
AND a.item_id = b.item_id
AND a.item_id = c.item_id
AND b.lot_id = c.lot_id
AND a.lot_ctl = 1
AND b.lot_id <> 0
AND (a.line_type <> 1)
ORDER BY a.formulaline_id,b.lot_no; */
SELECT a.*,b.PARENT_LOT_NUMBER lot, b.LOT_NUMBER sublot, NULL item_lotid, b.EXPIRATION_DATE expire, b.inventory_item_id itemid
FROM gmd_material_details_gtmp a, MTL_LOT_NUMBERS b,
(select inventory_item_id,lot_NUMBER from gmd_technical_data_vl group by inventory_item_id,lot_NUMBER) c
WHERE a.entity_id = V_formula_id
AND a.inventory_item_id = b.inventory_item_id
AND a.inventory_item_id = c.inventory_item_id
and b.organization_id = V_orgn_id
AND b.PARENT_LOT_NUMBER = c.LOT_NUMBER
AND a.lot_ctl = 1
AND (a.line_type <> 1)
ORDER BY a.formulaline_id,b.PARENT_LOT_NUMBER;
SELECT formulaline_id
FROM gmd_material_details_gtmp
WHERE entity_id = V_entity_id
AND line_type <> 3 ;
SELECT formula_id
FROM gmd_recipes r, gmd_recipe_validity_rules v
WHERE recipe_validity_rule_id = V_validity_rule_id
AND r.recipe_id = v.recipe_id;
UPDATE GMD_MATERIAL_DETAILS_GTMP
SET TPFORMULA_ID = l_tpformula_id,
FORMULALINE_ID = l_formula_rec.formulaline_id,
PARENT_LINE_ID = l_formula_rec.formulaline_id
WHERE ENTITY_ID = V_entity_id
AND LINE_ID = V_line_id;
DELETE FROM GMD_MATERIAL_DETAILS_GTMP
WHERE ENTITY_ID = V_entity_id
AND LINE_ID = V_line_id;
INSERT INTO GMD_MATERIAL_DETAILS_GTMP
(ENTITY_ID,LINE_ID,LINE_TYPE,LINE_NO,ROLLUP_IND,DUALUM_IND,LOCT_CTL,ITEM_ID,EXPAND_IND,EXPIRE_DATE,
LOT_ID,LOT_NO,SUBLOT_NO,QTY,SECONDARY_QTY,ITEM_UM,QC_GRADE,FORMULALINE_ID,PARENT_LINE_ID,
LOT_CTL,SUBLOT_CTL,GRADE_CTL,ACTION_CODE,SECONDARY_UM,
CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,
SECONDARY_DEFAULT_IND,TRACKING_QUANTITY_IND)
VALUES(l_matl_rec.entity_id,l_line_id,3,l_line_no,0,l_matl_rec.dualum_ind,l_matl_rec.loct_ctl,
l_matl_rec.item_id,0,l_matl_rec.expire,l_matl_rec.item_lotid,l_matl_rec.lot,l_matl_rec.sublot,0,
l_secondary_qty,l_matl_rec.item_um,l_matl_rec.qc_grade,l_matl_rec.formulaline_id,l_matl_rec.formulaline_id,
l_matl_rec.lot_ctl,l_matl_rec.sublot_ctl,l_matl_rec.grade_ctl,'NONE',l_matl_rec.secondary_um,
l_matl_rec.created_by,l_matl_rec.creation_date,l_matl_rec.last_updated_by,l_matl_rec.last_update_date,
l_matl_rec.SECONDARY_DEFAULT_IND,l_matl_rec.TRACKING_QUANTITY_IND);
gmd_spread_fetch_pkg.Update_formula_values(V_entity_id => V_entity_id,
V_formula_id => V_formula_id,
V_orgn_id => V_orgn_id,
V_formulaline_id => fmid,
V_line_id => V_line_id,
V_item_id => null,
V_plant_id => V_plant_id);
fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Update_Formula_Details');
END update_formula_details;
PROCEDURE Update_Formula_Values (V_entity_id IN NUMBER,V_formula_id IN NUMBER,
V_orgn_id IN number,V_formulaline_id IN NUMBER,
V_line_id IN NUMBER,V_item_id IN NUMBER,V_plant_id IN number) IS
CURSOR Cur_get_line IS
SELECT line_id
FROM gmd_material_details_gtmp
WHERE (V_formulaline_id IS NULL OR parent_line_id = V_formulaline_id)
ORDER BY line_type;
DELETE FROM GMD_TECHNICAL_DATA_GTMP
WHERE ENTITY_ID = V_entity_id
AND LINE_ID = V_line_id;
/* INSERT INTO GMD_TECHNICAL_DATA_GTMP
(ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,
VALUE,SORT_SEQ,NUM_DATA,TEXT_DATA,BOOLEAN_DATA)
SELECT V_entity_id,c.line_id,a.tech_parm_name,a.tech_parm_id,
DECODE(B.DATA_TYPE,0,TEXT_DATA,2,TEXT_DATA,3,BOOLEAN_DATA,NUM_DATA) VALUE,
b.sort_seq,a.num_data,a.text_data,a.boolean_data
FROM gmd_technical_data_vl a, gmd_technical_parameter_gtmp b, gmd_material_details_gtmp c
WHERE a.tech_parm_id = b.tech_parm_id
AND a.organization_id = V_orgn_id
AND a.item_id = c.item_id
AND (V_formulaline_id IS NULL OR c.parent_line_id = V_formulaline_id)
AND c.entity_id = V_entity_id
AND (V_line_id IS NULL OR c.line_id = V_line_id)
AND (a.formula_id = c.tpformula_id OR
(a.formula_id IS NULL AND NOT EXISTS ( SELECT 1
FROM GMD_TECHNICAL_DATA_VL e
WHERE e.item_id = c.item_id
AND nvl(e.lot_id, -1) = nvl(c.lot_id, -1)
AND e.batch_id IS NULL
AND e.formula_id = c.tpformula_id
AND e.organization_id = V_orgn_id)))
AND a.batch_id IS NULL
AND NVL(c.lot_id,-1) = NVL(a.lot_id,-1); */
INSERT INTO GMD_TECHNICAL_DATA_GTMP
(ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,
VALUE,SORT_SEQ,NUM_DATA,TEXT_DATA,BOOLEAN_DATA)
SELECT V_entity_id,c.line_id,a.tech_parm_name,a.tech_parm_id,
DECODE(B.DATA_TYPE,0,TEXT_DATA,2,TEXT_DATA,3,BOOLEAN_DATA,NUM_DATA) VALUE,
b.sort_seq,a.num_data,a.text_data,a.boolean_data
FROM gmd_technical_data_vl a, gmd_technical_parameter_gtmp b, gmd_material_details_gtmp c
WHERE a.tech_parm_id = b.tech_parm_id
AND a.organization_id = V_orgn_id
AND a.inventory_item_id = c.inventory_item_id
and c.organization_id = V_orgn_id
AND (V_formulaline_id IS NULL OR c.parent_line_id = V_formulaline_id)
AND c.entity_id = V_entity_id
AND (V_line_id IS NULL OR c.line_id = V_line_id)
AND (a.formula_id = c.tpformula_id OR
(a.formula_id IS NULL AND NOT EXISTS ( SELECT 1
FROM GMD_TECHNICAL_DATA_VL e
WHERE e.inventory_item_id = c.inventory_item_id
AND nvl(e.lot_id, -1) = nvl(c.lot_id, -1)
AND e.batch_id IS NULL
AND e.formula_id = c.tpformula_id
AND e.organization_id = V_orgn_id)))
AND a.batch_id IS NULL
AND NVL(c.lot_id,-1) = NVL(a.lot_id,-1);
INSERT INTO GMD_TECHNICAL_DATA_GTMP
(ENTITY_ID,LINE_ID,TECH_PARM_NAME,TECH_PARM_ID,
VALUE,SORT_SEQ,NUM_DATA,TEXT_DATA,BOOLEAN_DATA)
SELECT V_entity_id,V_line_id,a.tech_parm_name,a.tech_parm_id,
DECODE(B.DATA_TYPE,0,TEXT_DATA,2,TEXT_DATA,3,BOOLEAN_DATA,NUM_DATA) VALUE,
b.sort_seq,a.num_data,a.text_data,a.boolean_data
FROM gmd_technical_data_vl a, gmd_technical_parameter_gtmp b--, gmd_material_details_gtmp c
WHERE a.tech_parm_id = b.tech_parm_id
AND a.organization_id = V_orgn_id ---1
AND a.inventory_item_id = V_item_id
AND a.formula_id is null
AND a.batch_id IS NULL;
fnd_msg_pub.add_exc_msg ('GMD_SPREAD_FETCH_PKG', 'Update_Formula_Values');
END Update_Formula_Values;
/* Bug#7254259 (add procedure ,update_formula_details ,Update_Formula_Values ) End */
END GMD_SPREAD_FETCH_PKG;