[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
error_insert_batch_step EXCEPTION;
error_insert_b_step_act EXCEPTION;
error_insert_b_step_res EXCEPTION;
error_insert_b_res_param EXCEPTION;
error_insert_res_txns EXCEPTION;
error_insert_b_step_items EXCEPTION;
error_insert_b_step_depend EXCEPTION;
SELECT batchstep_id, plan_step_qty, plan_charges
FROM gme_batch_steps
WHERE batch_id = v_batch_id
AND ( v_count <> 1
OR ( v_count = 1
AND batchstep_id = (SELECT MAX (batchstep_id)
FROM gme_batch_steps
WHERE batch_id = v_batch_id) ) );
SELECT batchstep_activity_id, plan_activity_factor
FROM gme_batch_step_activities
WHERE batch_id = v_batch_id;
SELECT batchstep_activity_id, plan_activity_factor
FROM gme_batch_step_activities
WHERE batchstep_id = x_step_id AND batch_id = x_batch_id;
SELECT batchstep_resource_id, plan_rsrc_usage, plan_rsrc_count
FROM gme_batch_step_resources
WHERE batchstep_activity_id = v_batchstep_activity_id;
SELECT recipe_id
FROM gmd_recipe_validity_rules
WHERE recipe_validity_rule_id = v_recipe_validity_rule_id;
SELECT plan_start_date, plan_cmplt_date
FROM gme_batch_steps
WHERE batch_id = v_batch_id AND batchstep_id = v_batchstep_id;
SELECT plan_cmplt_date
FROM gme_batch_steps
WHERE batch_id = v_batch_id AND batchstep_id = v_batchstep_id;
SELECT resources
FROM gme_batch_step_charges
WHERE batch_id = v_batch_id
AND batchstep_id = v_batchstep_id
AND ROWNUM = 1;
SELECT plan_start_date, plan_cmplt_date
FROM gme_batch_step_resources
WHERE resources = v_resources
AND batch_id = v_batch_id
AND batchstep_id = v_batchstep_id;
SELECT batchstep_id
FROM gme_batch_steps
WHERE batch_id = v_batch_id;
l_step_charge_rsrc_tab.DELETE ();
l_gme_batch_steps (j).delete_mark := 0;
/* call create flex procedure to insert the default values of the BATCH_STEPS_DTL_FLEX
DFF's segments if they are enabled */
l_return_status := NULL;
IF (gme_batch_steps_dbl.insert_row
(p_batch_step => l_gme_batch_steps
(j)
,x_batch_step => l_gme_batch_steps
(j) ) =
TRUE) THEN
-- Keep the last batchstep_id produced... this is required for single step insert... since we don't
-- have the routingstep_id, we need this when linking the activities and resources back to the step.
l_last_batchstep_id := l_gme_batch_steps (j).batchstep_id;
RAISE error_insert_batch_step;
l_gme_batch_step_activities (j).delete_mark := 0;
SELECT batchstep_id
,plan_start_date
,plan_cmplt_date
INTO l_gme_batch_step_activities (j).batchstep_id
,l_gme_batch_step_activities (j).plan_start_date
,l_gme_batch_step_activities (j).plan_cmplt_date
FROM gme_batch_steps
WHERE batch_id = l_batch_id
AND routingstep_id = p_recipe_rout_act_tbl (i).routingstep_id;
SELECT batchstep_id
,plan_start_date
,plan_cmplt_date
INTO l_gme_batch_step_activities (j).batchstep_id
,l_gme_batch_step_activities (j).plan_start_date
,l_gme_batch_step_activities (j).plan_cmplt_date
FROM gme_batch_steps
WHERE batch_id = l_batch_id
AND batchstep_id = l_last_batchstep_id;
/* call create flex procedure to insert the default values of the GME_BATCH_STEP_ACTIVITIES_FLEX
DFF's segments if they are enabled */
l_return_status:=NULL;
IF (gme_batch_step_activities_dbl.insert_row
(p_batch_step_activities => l_gme_batch_step_activities
(j)
,x_batch_step_activities => l_gme_batch_step_activities
(j) ) ) THEN
NULL;
RAISE error_insert_b_step_act;
SELECT batchstep_id
,plan_start_date
,plan_cmplt_date
INTO l_gme_batch_step_resources (j).batchstep_id
,l_gme_batch_step_resources (j).plan_start_date
,l_gme_batch_step_resources (j).plan_cmplt_date
FROM gme_batch_steps
WHERE batch_id = l_batch_id
AND routingstep_id = p_recipe_rout_resc_tbl (i).routingstep_id;
SELECT batchstep_id
,plan_start_date
,plan_cmplt_date
INTO l_gme_batch_step_resources (j).batchstep_id
,l_gme_batch_step_resources (j).plan_start_date
,l_gme_batch_step_resources (j).plan_cmplt_date
FROM gme_batch_steps
WHERE batch_id = l_batch_id
AND batchstep_id = l_last_batchstep_id;
SELECT batchstep_activity_id
INTO l_gme_batch_step_resources (j).batchstep_activity_id
FROM gme_batch_step_activities
WHERE batch_id = l_batch_id
AND batchstep_id = l_gme_batch_step_resources (j).batchstep_id
AND oprn_line_id = p_recipe_rout_resc_tbl (i).oprn_line_id;
/* call create flex procedure to insert the default values of the GME_BATCH_STEP_RESOURCES_FLEX
DFF's segments if they are enabled */
l_return_status:=NULL;
IF (gme_batch_step_resources_dbl.insert_row
(p_batch_step_resources => l_gme_batch_step_resources
(j)
,x_batch_step_resources => l_gme_batch_step_resources
(j) ) ) THEN
NULL;
RAISE error_insert_b_step_res;
SELECT batchstep_id
INTO l_gme_rsrc_parameters (m).batchstep_id
FROM gme_batch_steps
WHERE batch_id = l_batch_id
AND routingstep_id = p_resc_parameters_tbl (m).routingstep_id;
SELECT batchstep_id
INTO l_gme_rsrc_parameters (m).batchstep_id
FROM gme_batch_steps
WHERE batch_id = l_batch_id
AND batchstep_id = l_last_batchstep_id;
SELECT batchstep_activity_id
INTO l_gme_rsrc_parameters (m).batchstep_activity_id
FROM gme_batch_step_activities
WHERE batch_id = l_batch_id
AND batchstep_id = l_gme_rsrc_parameters (m).batchstep_id
AND oprn_line_id = p_resc_parameters_tbl (m).oprn_line_id;
SELECT batchstep_resource_id
INTO l_gme_rsrc_parameters (m).batchstep_resource_id
FROM gme_batch_step_resources
WHERE batch_id = l_batch_id
AND batchstep_id = l_gme_rsrc_parameters (m).batchstep_id
AND batchstep_activity_id =
l_gme_rsrc_parameters (m).batchstep_activity_id
AND resources = p_resc_parameters_tbl (m).resources;
/* call create flex procedure to insert the default values of the GME_BATCH_PROC_PARAM_FLEX
DFF's segments if they are enabled */
l_return_status:=NULL;
IF NOT (gme_process_parameters_dbl.insert_row
(p_process_parameters => l_gme_rsrc_parameters
(m)
,x_process_parameters => l_gme_rsrc_parameters
(m) ) ) THEN
RAISE error_insert_b_res_param;
SELECT material_detail_id
INTO l_gme_batch_step_items (j).material_detail_id
FROM gme_material_details
WHERE batch_id = l_batch_id
AND formulaline_id = p_recipe_rout_matl_tbl (i).formulaline_id;
SELECT batchstep_id
INTO l_gme_batch_step_items (j).batchstep_id
FROM gme_batch_steps
WHERE batch_id = l_batch_id
AND routingstep_id = p_recipe_rout_matl_tbl (i).routingstep_id;
IF (gme_batch_step_items_dbl.insert_row
(p_batch_step_items => l_gme_batch_step_items
(j)
,x_batch_step_items => l_gme_batch_step_items
(j) ) ) THEN
NULL;
RAISE error_insert_b_step_items;
SELECT batchstep_id
INTO l_gme_batch_step_dep (j).batchstep_id
FROM gme_batch_steps
WHERE batch_id = l_batch_id
AND batchstep_no = p_routing_depd_tbl (i).routingstep_no;
SELECT batchstep_id
INTO l_gme_batch_step_dep (j).dep_step_id
FROM gme_batch_steps
WHERE batch_id = l_batch_id
AND batchstep_no = p_routing_depd_tbl (i).dep_routingstep_no;
IF (gme_batch_step_depend_dbl.insert_row (l_gme_batch_step_dep (j)
,l_gme_batch_step_dep (j) ) ) THEN
NULL;
RAISE error_insert_b_step_depend;
UPDATE gme_batch_steps
SET plan_step_qty = ROUND (l_step_tbl (i).step_qty, 32)
,plan_mass_qty = ROUND (l_step_tbl (i).step_mass_qty, 32)
,mass_ref_um = l_step_tbl (i).step_mass_uom
,plan_volume_qty = ROUND (l_step_tbl (i).step_vol_qty, 32)
,volume_ref_um = l_step_tbl (i).step_vol_uom
WHERE batch_id = l_batch_id
AND batchstep_no = l_step_tbl (i).step_no;
SELECT uom_class, conversion_rate
INTO l_uom_class, l_std_factor
FROM mtl_uom_conversions
WHERE uom_code = p_recipe_rout_step_tbl (1).process_qty_uom
AND inventory_item_id = 0;
|| 'resources name passed in case of single step insertion is'
|| p_recipe_rout_step_tbl (1).resources);
gme_update_step_qty_pvt.calc_charge
(p_step_id => l_last_batchstep_id
,p_resources => p_recipe_rout_step_tbl (1).resources
,p_mass_qty => l_mass_qty
,p_vol_qty => l_volume_qty
,x_charge => l_charge
,x_return_status => l_return_status);
UPDATE gme_batch_steps
SET plan_charges = l_charge
WHERE batchstep_id = l_last_batchstep_id;
update_charges (p_batch_id => l_batch_id
,p_step_charge_rsrc_tab => l_step_charge_rsrc_tab
,x_return_status => l_return_status);
UPDATE gme_batch_step_resources
SET plan_rsrc_usage =
ROUND ( (l_qty / plan_rsrc_qty * plan_rsrc_usage), 32)
WHERE batchstep_id = l_batchstep_id
AND scale_type = 1
AND -- scale_type = 1 denotes linear scale...
plan_rsrc_qty <> 0;
SELECT plan_step_qty, plan_charges
INTO l_qty, l_plan_charges
FROM gme_batch_steps
WHERE batchstep_id = l_last_batchstep_id;
UPDATE gme_batch_step_resources
SET plan_rsrc_qty = ROUND (l_qty, 32)
WHERE batchstep_id = l_last_batchstep_id
AND scale_type <>
0
-- if scale_type = 0, then let's leave the plan_rsrc_qty to that passed in by GMD... scale_type = 0 is fixed
AND plan_rsrc_qty <> 0;
UPDATE gme_batch_step_resources
SET plan_rsrc_usage =
ROUND (plan_rsrc_usage * l_plan_charges, 32)
WHERE batchstep_id = l_last_batchstep_id AND scale_type = 2;
SELECT COUNT (1)
INTO l_count_scale_by_charge
FROM gme_batch_step_resources
WHERE batchstep_id = l_last_batchstep_id AND scale_type = 2;
UPDATE gme_batch_step_resources
SET plan_rsrc_usage =
ROUND (plan_rsrc_usage * l_activity_factor, 32)
,plan_rsrc_qty =
ROUND (plan_rsrc_qty * l_activity_factor, 32)
WHERE batchstep_activity_id = l_batchstep_activity_id;
IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN
FOR l_rec IN cur_get_resources (l_batchstep_activity_id) LOOP
UPDATE gme_resource_txns
SET resource_usage =
ROUND ( l_rec.plan_rsrc_usage
/ l_rec.plan_rsrc_count
,32)
WHERE doc_id = l_batch_id
AND doc_type = l_doc_type
AND line_id = l_rec.batchstep_resource_id;
/* IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN */
FETCH cur_get_step_activities
INTO l_batchstep_activity_id, l_activity_factor;
UPDATE gme_batch_step_resources
SET plan_rsrc_qty = ROUND (l_qty, 32)
WHERE batchstep_id = l_batchstep_id
AND scale_type <> 0
AND plan_rsrc_qty <> 0;
UPDATE gme_batch_step_resources
SET plan_rsrc_usage =
ROUND (plan_rsrc_usage * l_plan_charges, 32)
WHERE batchstep_id = l_batchstep_id AND scale_type = 2;
SELECT COUNT (1)
INTO l_count_scale_by_charge
FROM gme_batch_step_resources
WHERE batchstep_id = l_batchstep_id AND scale_type = 2;
UPDATE gme_batch_step_resources
SET plan_rsrc_usage =
ROUND (plan_rsrc_usage * l_activity_factor, 32)
,plan_rsrc_qty =
ROUND (plan_rsrc_qty * l_activity_factor, 32)
WHERE batchstep_activity_id = l_batchstep_activity_id;
IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN
FOR l_rec IN cur_get_resources (l_batchstep_activity_id) LOOP
UPDATE gme_resource_txns
SET resource_usage =
ROUND ( l_rec.plan_rsrc_usage
/ l_rec.plan_rsrc_count
,32)
WHERE doc_id = l_batch_id
AND doc_type = l_doc_type
AND line_id = l_rec.batchstep_resource_id;
/* IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN */
FETCH cur_get_activities
INTO l_batchstep_activity_id, l_activity_factor;
UPDATE gme_batch_steps
SET plan_cmplt_date = p_step_cmplt_date
,last_updated_by = gme_common_pvt.g_user_ident
,last_update_date = gme_common_pvt.g_timestamp
,last_update_login = gme_common_pvt.g_login_id
WHERE batch_id = p_gme_batch_header_rec.batch_id
AND batchstep_id = l_last_batchstep_id;
UPDATE gme_batch_step_charges
SET plan_start_date = l_rsrc_start_date
,plan_cmplt_date = l_rsrc_cmplt_date
WHERE batch_id = p_gme_batch_header_rec.batch_id
AND batchstep_id = l_batchstep_ids_tab (i);
WHEN error_insert_batch_step THEN
IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
gme_debug.put_line ('insert batch step error');
WHEN error_insert_b_step_act THEN
IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
gme_debug.put_line ('insert batch step activity error');
WHEN error_insert_b_step_res THEN
IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
gme_debug.put_line ('insert batch step resource error');
WHEN error_insert_res_txns THEN
IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
gme_debug.put_line ('insert resource txns error');
WHEN error_insert_b_res_param THEN
IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
gme_debug.put_line ('insert resource param error');
WHEN error_insert_b_step_items THEN
IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
gme_debug.put_line ('insert batch step items error');
WHEN error_insert_b_step_depend THEN
IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
gme_debug.put_line ('insert batch step depend error');
PROCEDURE update_charges (
p_batch_id IN NUMBER
,p_step_charge_rsrc_tab IN gme_create_step_pvt.step_charge_rsrc_tab
,x_return_status OUT NOCOPY VARCHAR2)
IS
x_charge_tab charge_tab;
l_api_name CONSTANT VARCHAR2 (30) := 'update charges';
SELECT batchstep_id, plan_mass_qty, mass_ref_um, plan_volume_qty
,volume_ref_um
FROM gme_batch_steps
WHERE batch_id = v_batch_id;
gme_update_step_qty_pvt.calc_charge
(p_step_id => x_cur_step_rec.batchstep_id
,p_resources => p_step_charge_rsrc_tab
(x_cur_step_rec.batchstep_id).resources
,p_mass_qty => x_cur_step_rec.plan_mass_qty
,p_vol_qty => x_cur_step_rec.plan_volume_qty
,x_charge => x_charge
,x_return_status => l_return_status);
( ' update charges >calc charges > return charge '
|| TO_CHAR (x_charge) );
( ' update charges >calc charges >batchstep_id '
|| TO_CHAR (x_cur_step_rec.batchstep_id) );
UPDATE gme_batch_steps
SET plan_charges = x_charge_tab (i).charge
WHERE batchstep_id = x_charge_tab (i).step_id;
END update_charges;
SELECT dep_type, r.plan_start_date, r.plan_cmplt_date
,standard_delay
FROM gme_batch_step_dependencies d, gme_batch_steps r
WHERE d.batch_id = r.batch_id
AND d.batch_id = v_batch_id
AND r.batchstep_id = d.dep_step_id
AND d.batchstep_id = v_step_id;
SELECT MAX (DECODE (dep_type
,1, r.plan_start_date + standard_delay / 24
,0, r.plan_cmplt_date + standard_delay / 24) )
FROM gme_batch_step_dependencies d, gme_batch_steps r
WHERE d.batch_id = r.batch_id
AND d.batch_id = v_batch_id
AND r.batchstep_id = d.dep_step_id
AND d.batchstep_id = v_step_id;
SELECT *
FROM gme_batch_step_activities
WHERE batch_id = v_batch_id AND batchstep_id = v_step_id;
SELECT *
FROM gme_batch_step_resources
WHERE batchstep_activity_id = v_batchstep_activity_id;
SELECT batchstep_id
FROM gme_batch_steps
WHERE batch_id = v_batch_id AND batchstep_no = v_step;
SELECT batchstep_no
FROM gme_batch_steps
WHERE batchstep_id = v_batchstep_id;
SELECT activity
FROM gme_batch_step_activities
WHERE batchstep_activity_id = v_batchstep_activity_id;
SELECT inventory_item_id
FROM gmd_recipe_validity_rules
WHERE recipe_validity_rule_id = p_recipe_validity_rule_id;
error_insert_res_txns EXCEPTION;
/* it is not NULL, then we only want to calc the dates for that step, i.e. insert_step... */
IF (p_step_id IS NULL) THEN
gmd_auto_step_calc.load_steps (l_batch_id
,1
,NULL
,x_gmd_step_tbl
,x_routing_id
,l_return_status);
SELECT batchstep_no
INTO x_gmd_step_tbl (1).step_no
FROM gme_batch_steps
WHERE batchstep_id = p_step_id;
SELECT batchstep_no
INTO x_step_no
FROM gme_batch_steps
WHERE batchstep_id = p_step_id;
SELECT *
INTO x_step_row
FROM gme_batch_steps
WHERE batchstep_id = x_step_tbl (i);
x_act_tab.DELETE;
x_rsrc_tab.DELETE;
IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN
x_rsrc_txns_tab.DELETE;
/* IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN */
FOR k IN x_rsrc_tab.FIRST .. x_rsrc_tab.LAST LOOP
IF l_use_workday_cal = fnd_api.g_true THEN
x_rsrc_tab (k).plan_start_date :=
get_working_start_time
(p_start_date => x_act_tab (j).plan_start_date
,p_offset => x_rsrc_tab (k).offset_interval
,p_calendar_code => l_calendar_code);
IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN
FOR m IN 1 .. l_cal_count LOOP
x_rsrc_tab (k).plan_start_date :=
l_contig_period_tbl (m).start_date;
insert_resource_txns (p_gme_batch_header_rec
,l_doc_type
,x_rsrc_tab (k)
,l_return_status);
RAISE error_insert_res_txns;
/* IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN */
IF k = x_rsrc_tab.FIRST
OR x_rsrc_tab (k).plan_cmplt_date > x_max_rsrc_date THEN
x_max_rsrc_date := x_rsrc_tab (k).plan_cmplt_date;
UPDATE gme_batch_step_resources
SET plan_start_date = x_rsrc_tab (k).plan_start_date
,plan_cmplt_date = x_rsrc_tab (k).plan_cmplt_date
WHERE batchstep_resource_id =
x_rsrc_tab (k).batchstep_resource_id;
UPDATE gme_batch_step_activities
SET plan_start_date = x_act_tab (k).plan_start_date
,plan_cmplt_date = x_act_tab (k).plan_cmplt_date
WHERE batchstep_activity_id = x_act_tab (k).batchstep_activity_id;
UPDATE gme_batch_steps
SET plan_start_date = x_step_row.plan_start_date
,plan_cmplt_date = x_step_row.plan_cmplt_date
,due_date = x_step_row.due_date
WHERE batchstep_id = x_step_row.batchstep_id;
SELECT MAX (plan_cmplt_date)
INTO max_cmplt_date
FROM gme_batch_steps
WHERE batch_id = l_batch_id;
SELECT MIN (plan_start_date)
INTO min_start_date
FROM gme_batch_steps
WHERE batch_id = l_batch_id;
UPDATE gme_batch_header
SET plan_cmplt_date = max_cmplt_date
,last_updated_by = gme_common_pvt.g_user_ident
,last_update_date = gme_common_pvt.g_timestamp
,last_update_login = gme_common_pvt.g_login_id
WHERE batch_id = l_batch_id;
UPDATE gme_batch_header
SET plan_start_date = min_start_date
,plan_cmplt_date = max_cmplt_date
,last_updated_by = gme_common_pvt.g_user_ident
,last_update_date = gme_common_pvt.g_timestamp
,last_update_login = gme_common_pvt.g_login_id
WHERE batch_id = l_batch_id;
/* UPDATE gme_batch_header
SET plan_start_date = min_start_date
,plan_cmplt_date = max_cmplt_date
,last_updated_by = gme_common_pvt.g_user_ident
,last_update_date = gme_common_pvt.g_timestamp
,last_update_login = gme_common_pvt.g_login_id
WHERE batch_id = l_batch_id;*/
UPDATE gme_batch_header
SET due_date = max_cmplt_date
,last_updated_by = gme_common_pvt.g_user_ident
,last_update_date = gme_common_pvt.g_timestamp
,last_update_login = gme_common_pvt.g_login_id
WHERE batch_id = l_batch_id
AND due_date = gme_common_pvt.g_timestamp;
SELECT batchstep_id, batchstep_no
FROM gme_batch_steps
WHERE batch_id = v_batch_id
AND batchstep_id NOT IN (SELECT dep_step_id
FROM gme_batch_step_dependencies
WHERE batch_id = v_batch_id);
SELECT branch, LENGTH(branch) sz
FROM (SELECT REPLACE(sys_connect_by_path(LPAD(s.batchstep_no, 5, 0)||LPAD(p.batchstep_no, 5, 0 )||dep_type||LPAD(standard_delay, 3,0),' '), ' ', NULL) branch
FROM (SELECT * FROM gme_batch_step_dependencies WHERE batch_id = v_batch_id) d,
(SELECT * FROM gme_batch_steps WHERE batch_id = v_batch_id) s,
(SELECT * FROM gme_batch_steps WHERE batch_id = v_batch_id) p
WHERE s.batchstep_id = d.batchstep_id
AND p.batchstep_id = d.dep_step_id
AND connect_by_isleaf = 1
START WITH d.batchstep_id = v_batchstep_id
CONNECT BY d.batchstep_id = PRIOR d.dep_step_id) x;
SELECT a.conversion_rate, b.uom_class
FROM mtl_uom_conversions a, mtl_units_of_measure b
WHERE a.uom_code = b.uom_code
AND a.inventory_item_id = 0
AND b.uom_code = v_uom_code;
SELECT uom_code
FROM mtl_units_of_measure
WHERE uom_class = v_uom_class AND base_uom_flag = 'Y';
SELECT plan_step_qty
INTO x_step_qty
FROM gme_batch_steps
WHERE batchstep_no = p_step_tbl (i).step_no
AND batch_id = p_parent_id;
SELECT *
FROM fm_text_tbl
WHERE text_code = p_text_code
ORDER BY line_no;
l_text_table.DELETE;
gme_text_dbl.insert_header_row (l_text_header, l_text_header);
gme_text_dbl.insert_text_row (l_text_table (l_row_count)
,l_text_table (l_row_count) );
gme_debug.put_line ( 'trouble inserting text for '
|| p_text_string);
gme_debug.put_line ('inserted text for ' || p_text_string);
('ERROR - unexpected error in copy and create text while inserting header or dtl');
SELECT b.uom_class, a.conversion_rate
FROM mtl_uom_conversions a, mtl_units_of_measure b
WHERE a.uom_code = b.uom_code
AND a.inventory_item_id = 0
AND b.uom_code = v_uom_code;
SELECT batchstep_activity_id, offset_interval, batchstep_id
,batch_id
FROM gme_batch_step_activities
WHERE batchstep_id = v_step_id AND batch_id = v_batch_id;
SELECT batchstep_resource_id, offset_interval, plan_rsrc_usage
,plan_rsrc_count, usage_um
FROM gme_batch_step_resources
WHERE batchstep_id = v_step_id
AND batch_id = v_batch_id
AND batchstep_activity_id = v_activity_id;
PROCEDURE insert_resource_txns (
p_gme_batch_header_rec IN gme_batch_header%ROWTYPE
,p_doc_type IN VARCHAR2
,p_batch_step_resources_rec IN gme_batch_step_resources%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_gme_resource_txns gme_resource_txns%ROWTYPE;
l_api_name CONSTANT VARCHAR2 (30) := 'insert_resource_txns';
error_insert_res_txns EXCEPTION;
IF p_gme_batch_header_rec.update_inventory_ind = 'Y' THEN
l_gme_resource_txns.doc_id := p_batch_step_resources_rec.batch_id;
l_gme_resource_txns.delete_mark := 0;
/*call create flex procedure to insert the default values of the GME_RSRC_TXN_FLEX
DFF's segments if they are enabled */
l_return_status:=NULL;
IF (gme_resource_txns_dbl.insert_row
(p_resource_txns => l_gme_resource_txns
,x_resource_txns => l_gme_resource_txns) ) THEN
NULL;
RAISE error_insert_res_txns;
WHEN error_insert_res_txns THEN
IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
gme_debug.put_line ('insert resource txns error');
END insert_resource_txns;