[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 not 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.
G. Muratore 18-NOV-2008 Bug 7565054 - Rework of 7284242
This fix was to correct the algorithm for deriving the scale factor when creating a batch by
'PRODUCT'. When a batch is created from APS, the Quantity being passed is in reference to the
primary product line only. Create batch did not treat it the same way if the same item existed in
multiple product lines. Added parameter p_sum_all_prod_lines to the procedure create_batch.
Parameter values: "A" means sum all the lines i.e. original functionality. "S" means single product summation.
The original fix dealt with fixed scale type only which we now know was not the real issue. The
New paramater will allow APS a way to tell this code to consider only the primary product line.
In the future this could be extended to the forms since the parameter now exists.
G. Muratore 25-NOV-2008 Bug 7578476 - Some Rework of 4917631 also done.
This fix was to correct the algorithm for deriving the original_primary_qty. The user reported
that it was not considering scrap_factor. That in fact was an issue but also it was not calculated
properly if the dtl uom was not the primary, in some cases. Changed the code to correct all areas where
original_primary_qty is derived. Note: Removed some redundant cursors and code. Also, made use of
l_item_masters table array properly to get the correct conversions.
G. Muratore 05-FEB-2009 Bug 7830838
Use the same value of fixed_process_loss_applied from the parent batch so that FPL
gets properly applied, or not applied, to the phantom batch based on user settings.
Also includes fix for Bug 7656415. This rounds the plan_qty to make sure it adheres to 5 decimal places.
G. Muratore 09-FEB-2009 Bug 8226667
Changed select from inline calculation to using variables and making sure that
denominator cannot be zero. Procedure: update_step_mtq
G. Muratore 26-FEB-2009 Bug 7710435
Changed call to gme_material_details_dbl.update_row which now accepts a p_called_by parameter. This allows
us to disregard the timestamp used for record locking. It is not required to lock records, during batch create,
which are not committed to the database yet. Sometimes, the timestamp in the database record did not match
the one in the memory table and therefore updates were failing. Also refetch the material records after the
last call to update_row to update the memory tables. PROCEDURE: create_batch.
G. Muratore 09-SEP-2010 Bug 10086349
Use precision of 4 instead of 5 to make comparison against batch size requested.
This is necessary because of potential precision loss.
G. Muratore 21-DEC-2010 Bug 10379034 - REWORK OF 10086349
Change the comparison against batch size requested so we do not rely on potential rounding issues.
G. Muratore 24-JAN-2011 Bug 10624995
Rearrange logic as to handle integer scale items for non scaled batches during batch creation.
G. Muratore 04-OCT-2011 Bug 12909216 - TWEEK OF 10379034
Avoid divide by zero error when qty is zero. This will allow zero phantom batches to be created.
A. Mishra 09-DEC-2011 Bug 13256866 -
Adding the who columns for every update statement.
G. Muratore 09-DEC-2011 Bug 11815699
Check to make sure the item is not inactive for process execution based on item status code.
G. Muratore 28-FEB-2012 Bug 13785754
Changed logic to try 3 times if necessary to get a unique batch number for auto doc numbering.
This change was needed for customers firing off creation of many batches by many users. Periodically
there was a key constraint issue on the wip entities table.
G. Muratore 20-MAR-2012 Bug 13811289
Make sure all items returned are valid for the specific organization.
G. Muratore 23-MAR-2012 Bug 13785754/13815190
Changed logic to try 20 times if necessary to get a unique batch number for auto doc numbering.
QZENG 12-Mar-2013 Bug 16457668
No need to check batch no for verified in bulk validation when used from batch open interface
G. Muratore 21-MAR-2013 Bug 16474091
Changed change precision from 5 to 32 for totals that are used in deriving routing scale factor.
************************************************************************************************************ */
/************************************************************
* *
* 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,
primary_uom_code, inventory_item_status_code
FROM mtl_system_items_kfv
WHERE inventory_item_id = v_inventory_item_id
AND organization_id = v_org_id;
SELECT DISTINCT concatenated_segments
FROM mtl_system_items_kfv
WHERE inventory_item_id = v_inventory_item_id
AND rownum = 1;
SELECT NVL(st.attribute_value, 'N')
FROM mtl_stat_attrib_values_all_v st
WHERE st.inventory_item_status_code = v_inventory_item_status_code
AND attribute_name = 'MTL_SYSTEM_ITEMS.PROCESS_EXECUTION_ENABLED_FLAG';
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) ), 1 as line_no
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
AND v_sum_all_prod_lines = 'A'
UNION
SELECT inv_convert.inv_um_convert (v_inventory_item_id
,5
,plan_qty
,dtl_um
,p_batch_size_uom
,NULL
,NULL), line_no
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
AND v_sum_all_prod_lines <> 'A'
ORDER BY line_no;
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)
-- Bug 13256866
,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 the batch_no with the actual value */
UPDATE gme_batch_header
SET batch_no = x_batch_header_rec.batch_no
-- Bug 13256866
,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;
/*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 (p_material_detail => l_material_details (l_row_count),
p_called_by =>'C' );
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
-- Bug 13256866
,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 material details table we have computed material requirement date and move_order_line_id */
FOR i IN 1 .. l_material_details.COUNT LOOP
-- Bug 7710435 - Added p_called_by parameter.
l_return :=
gme_material_details_dbl.update_row (p_material_detail => l_material_details (i),
p_called_by =>'C' );
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)
*/
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 ;