[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* sunitha bug # 5484529 selecting the sum of the plan */
/* quantity without converting it to the UOM that the user entered */
/* while creating the batch into l_temp_qty .Convert the */
/* p_batch_size(user entered product plan quantity */
/* to the Routing uom and then Compare it with l_temp_qty.*/
/* Kapil M. Bug# 5458674 */
/* Changes to Support LCF Batches for GMO */
/* Archana Mundhe Bug 5763818 Modified the code to use */
/* ERES constants that are added to gme_common_pvt instead*/
/* of using the hardcoded ERES event names such as */
/* 'oracle.apps.gme...' */
/* Swapna K Bug#6398619 calls to validate_wip_entity are changed */
/* for the manual doc ordering */
/* Please use this new format for comments going forward.
G. Muratore 12/26/2007 Bug 6665602 Back out fix for 5484529. This fix was
incorrect. The select fixed in 5484529 was no correct because it did not convert
all product lines to one common uom. Therefore the summation is invalid.
Bug 5484529 will ahve to be re-addressed in a different way.
Swapna K Bug#6738476 Added parameter,p_batch_header_rec
to the procedure call,gme_phantom_pvt.create_phantom
10-JAN-2008 Rajesh Patangya Bug # 6752637
MTQ Quantity should be calculated based on product in place of just copy from
the routing, This is required by PS engine, New Function UPDATE_STEP_MTQ added
G. Muratore 07/31/2008 Bug 7265006 Correct fix for 5512352.
This fix was incorrect as to where the new condition was used when trying to optimize shortage checking.
Moved g_no_phant_short_check to shortage check condition as it was originally intended
for shortage checking only. Unfortunately it stopped all the other logic for phantom batches.
Now invisible move orders and high level reservations will get created for phantom batches.
G. Muratore 08/18/2008 Bug 7284242
This fix was to correct the algorithm for deriving the scale factor when creating a batch by
'PRODUCT'. Prior to this fix the code did not treat fixed scale products differently from
linear scale products.
K. swapna 10/21/2008 Bug 7493614
Moved the call to wf_event.raise call to the end of the create_batch
procedure as the sample can be created at the end of the batch creation.
********************************************************* */
/************************************************************
* *
* CONSTRUCT_BATCH_HEADER *
* *
************************************************************/
FUNCTION construct_batch_header (
p_batch_header_rec IN gme_batch_header%ROWTYPE
,x_batch_header_rec OUT NOCOPY gme_batch_header%ROWTYPE)
RETURN BOOLEAN
IS
l_count NUMBER;
x_batch_header_rec.delete_mark := 0;
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT 1
FROM wip_entities
WHERE organization_id = p_organization_id
AND wip_entity_name = p_batch_no);
SELECT *
FROM gmd_recipe_validity_rules
WHERE recipe_validity_rule_id = NVL (v_recipe_validity_rule_id, -1);
SELECT *
FROM gmd_recipes
WHERE recipe_id = v_recipe_id;
SELECT a.*
FROM fm_matl_dtl a, gmd_recipes_b b
WHERE a.formula_id = b.formula_id AND b.recipe_id = v_recipe_id
ORDER BY line_no;
SELECT *
FROM fm_text_tbl
WHERE text_code = NVL (v_text_code, -1)
ORDER BY line_no;
SELECT inventory_item_id, concatenated_segments,
eng_item_flag, process_execution_enabled_flag
FROM mtl_system_items_kfv
WHERE inventory_item_id = v_inventory_item_id
AND organization_id = v_org_id;
SELECT primary_uom_code, eng_item_flag, process_execution_enabled_flag
FROM mtl_system_items_kfv
WHERE inventory_item_id = v_inventory_item_id
AND organization_id = v_org_id;
SELECT std_lot_size, primary_uom_code
FROM mtl_system_items_b
WHERE inventory_item_id = v_inventory_item_id
AND organization_id = v_org_id;
SELECT DECODE (v_batch_type
,10, fpo_doc_numbering
,batch_doc_numbering) assignment_type
FROM gme_parameters
WHERE organization_id = v_org_id;
SELECT 1
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM gme_batch_header
WHERE batch_no = v_batch_no
AND organization_id = v_org_id
AND batch_type = v_batch_type);
SELECT plan_qty, dtl_um
FROM gme_material_details
WHERE batch_id = v_batch_id
AND inventory_item_id = v_inventory_item_id
AND line_type = gme_common_pvt.g_line_type_prod
ORDER BY line_no ASC;
SELECT SUM (inv_convert.inv_um_convert (v_inventory_item_id
,5
,plan_qty
,dtl_um
,p_batch_size_uom
,NULL
,NULL) )
FROM gme_material_details
WHERE batch_id = v_batch_id
AND inventory_item_id = v_inventory_item_id
AND line_type = gme_common_pvt.g_line_type_prod;
SELECT SUM (plan_qty )
FROM gme_material_details
WHERE batch_id = v_batch_id
AND inventory_item_id = v_inventory_item_id
AND line_type = gme_common_pvt.g_line_type_prod; */
SELECT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = v_inventory_item_id
AND organization_id = v_org_id;
SELECT b.*
FROM gme_batch_step_items a, gme_batch_steps b
WHERE a.batchstep_id = b.batchstep_id AND a.material_detail_id = p_material_detail_id;
update_step_mtq_failure EXCEPTION;
x_batch_header_rec.update_inventory_ind :=
p_batch_header_rec.update_inventory_ind;
x_batch_header_rec.update_inventory_ind := 'Y';
x_batch_header_rec.update_inventory_ind :=
p_batch_header_rec.update_inventory_ind;
x_batch_header_rec.update_inventory_ind := 'Y';
OR l_formula_master.delete_mark = 1
OR l_formula_master.inactive_ind = 1 THEN
gme_common_pvt.log_message ('GME_API_INVALID_FORMULA');
/*5698727 rework Begin we use timestamp initially as its unique. later we update
with actual batch_no
gme_common_pvt.create_document_no (l_in_batch_header
,x_batch_header_rec);*/
SELECT trim(TO_CHAR(systimestamp,'DD:MM:YYYY HH24:MI:SS:FF6')) INTO l_doc_timestamp FROM DUAL;
gme_text_dbl.insert_header_row (l_text_header, l_text_header);
gme_debug.put_line ('Error in inserting text code = '||l_formula_master.text_code);
/* update automatic_step_calculation */
x_batch_header_rec.automatic_step_calculation :=
l_recipe.calculate_step_quantity;
/* call create flex procedure to insert the default values of the BACTH_FLEX
DFF's segments if they are enabled */
gme_validate_flex_fld_pvt.create_flex_batch_header(x_batch_header_rec,
x_batch_header_rec,
l_return_status);
gme_batch_header_dbl.insert_row (x_batch_header_rec
,x_batch_header_rec);
gme_debug.put_line ('Inserting formula header text in batch header edit text');
gme_text_dbl.insert_header_row (l_text_header
,l_text_header);
gme_debug.put_line ('Error in inserting text header');
gme_debug.put_line ('MATERTIAL lines to be INSERTED ' || l_row_count);
gme_material_details_dbl.insert_row
(l_material_details (l_row_count)
,l_material_details (l_row_count) );
gme_debug.put_line ('MATERTIAL_INSERTED');
gme_text_dbl.insert_text_row (l_text_table (l_row_count)
,l_text_table (l_row_count) );
SELECT primary_uom_code
INTO l_prim_item_um
FROM mtl_system_items_b
WHERE inventory_item_id = l_recipe_validity_rule.inventory_item_id
AND organization_id = x_batch_header_rec.organization_id;
UPDATE gme_batch_header
SET plan_start_date = x_batch_header_rec.plan_start_date
,plan_cmplt_date = x_batch_header_rec.plan_cmplt_date
,due_date =
NVL (x_batch_header_rec.due_date
,x_batch_header_rec.plan_cmplt_date)
WHERE batch_id = x_batch_header_rec.batch_id;
/* update the batch_no with the actual value */
UPDATE gme_batch_header
SET batch_no = x_batch_header_rec.batch_no
WHERE batch_id = x_batch_header_rec.batch_id;
/*5698727 rework update the wip_entities table with actual batch no*/
UPDATE wip_entities
SET wip_entity_name = l_prefix||x_batch_header_rec.batch_no
WHERE organization_id = x_batch_header_rec.organization_id
AND wip_entity_name = l_prefix||l_doc_timestamp;
UPDATE gme_batch_header
SET plan_cmplt_date = p_batch_header_rec.plan_cmplt_date
,due_date =
NVL (x_batch_header_rec.due_date
,p_batch_header_rec.plan_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 = x_batch_header_rec.batch_id;
UPDATE gme_batch_header
SET plan_cmplt_date = p_batch_header_rec.plan_cmplt_date
,due_date =
NVL (x_batch_header_rec.due_date
,p_batch_header_rec.plan_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 = x_batch_header_rec.batch_id;
gme_material_details_dbl.update_row
(l_material_details (l_row_count) );
as this update is not allowed in above dbl procedure */
UPDATE gme_material_details
SET original_primary_qty = l_material_details (l_row_count).original_primary_qty
WHERE material_detail_id = l_material_details (l_row_count).material_detail_id;
gme_update_step_qty_pvt.update_step_qty (p_batch_step_rec => l_batchstep_rec,
x_message_count => l_message_count,
x_message_list => l_message_list,
x_return_status => x_return_status,
x_batch_step_rec => x_batchstep_rec
);
gme_debug.put_line ('After update step qty, return status is:' || x_return_status);
IF NVL (x_batch_header_rec.update_inventory_ind, 'Y') = 'Y' AND l_item_substituted = TRUE THEN
IF x_batch_header_rec.automatic_step_calculation = 1 THEN
/* Assigning the user passed dates so that batch gets rescheduled to the passed dates*/
-- Restore the dates as supplied by user. If user does not provide either start or cmplt dt, use
-- newly created batch's planned start date to re-schedule.
IF p_batch_header_rec.plan_start_date IS NULL AND p_batch_header_rec.plan_cmplt_date IS NULL THEN
x_batch_header_rec.plan_cmplt_date := p_batch_header_rec.plan_cmplt_date;
/* nsinghi bug#5674398 Added the FETCH condition. Reschedule_batch will update the
material_requirement_date, hence requery material detail records */
l_in_material_detail.batch_id := x_batch_header_rec.batch_id;
UPDATE gme_batch_header
SET due_date =
x_batch_header_rec.plan_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 = x_batch_header_rec.batch_id;
END IF; /* Update inventory*/
/* Update WIP entity table to put the primary product id */
UPDATE wip_entities
SET primary_item_id = l_recipe_validity_rule.inventory_item_id
WHERE wip_entity_id = x_batch_header_rec.batch_id;
|| 'Calling Update_step_mtq');
IF NOT update_step_mtq (x_batch_header_rec.batch_id ) THEN
RAISE update_step_mtq_failure;
fnd_msg_pub.delete_msg (p_msg_index => l_error_count_after);
IF x_batch_header_rec.batch_type = 0 AND NVL (x_batch_header_rec.update_inventory_ind, 'Y') = 'Y' THEN
/* Check inventory shortages */
IF (gme_common_pvt.g_check_shortages_ind = 1 AND g_no_phant_short_check = 0) THEN
IF g_debug <= gme_debug.g_log_statement THEN
gme_debug.put_line ( g_pkg_name || '.'|| l_api_name|| ' Calling shortages ');
/* Update batch header with move_order_header_id */
UPDATE gme_batch_header
SET move_order_header_id = x_batch_header_rec.move_order_header_id
WHERE batch_id = x_batch_header_rec.batch_id;
/* Update material details table we have computed material requirement date and move_order_line_id */
FOR i IN 1 .. l_material_details.COUNT LOOP
l_return :=
gme_material_details_dbl.update_row (l_material_details (i) );
WHEN update_step_mtq_failure THEN
x_return_status := fnd_api.g_ret_sts_error;
* UPDATE_STEP_MTQ *
* MTQ Quantity should be calculated based on product *
*************************************************************/
FUNCTION update_step_mtq (p_batch_id IN NUMBER)
RETURN BOOLEAN IS
l_batch_steps gme_batch_steps%ROWTYPE;
l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_STEP_MTQ';
SELECT *
FROM gme_batch_steps
WHERE batch_id = x_batch_id ;
SELECT
(frh.routing_qty * NVL(frd.minimum_transfer_qty,0)
* DECODE(fmd.detail_uom, iim.primary_uom_code, fmd.qty,
inv_convert.inv_um_convert
(fmd.inventory_item_id,
5,
fmd.qty,
fmd.detail_uom,
iim.primary_uom_code,
NULL,NULL)
)
)
/ (ffm.total_output_qty * frd.step_qty)
FROM fm_form_mst ffm,
fm_matl_dtl fmd,
fm_rout_hdr frh,
fm_rout_dtl frd,
mtl_system_items iim,
gme_batch_header gbh,
gmd_recipe_validity_rules ffe
WHERE gbh.batch_id = x_batch_id
AND ffm.formula_id = gbh.formula_id
AND ffm.formula_id = fmd.formula_id
AND iim.organization_id = NVL(ffe.organization_id,iim.organization_id)
AND fmd.inventory_item_id = ffe.inventory_item_id
AND iim.inventory_item_id = fmd.inventory_item_id
AND iim.organization_id = fmd.organization_id
AND frh.routing_id = gbh.routing_id
AND frh.routing_id = frd.routing_id
AND frd.routingstep_id = l_batch_steps.routingstep_id
AND ffe.recipe_validity_rule_id = gbh.recipe_validity_rule_id ;
UPDATE gme_batch_steps
SET minimum_transfer_qty = l_calculated_mtq
WHERE batch_id = l_batch_steps.batch_id
AND routingstep_id = l_batch_steps.routingstep_id
AND batchstep_id = l_batch_steps.batchstep_id ;
gme_debug.put_line (' Update Calculated MTQ = ' || l_calculated_mtq );
END update_step_mtq ;