[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT status_type
FROM gmd_status gs, gmd_recipe_validity_rules grvr
WHERE grvr.recipe_validity_rule_id = v_validity_rule_id
AND status_code = grvr.validity_rule_status;
SELECT start_date, end_date
FROM gmd_recipe_validity_rules
WHERE recipe_validity_rule_id = v_validity_rule_id;
SELECT material_detail_id, line_type
FROM gme_material_details
WHERE batch_id = v_batch_id;
SELECT phantom_id, m.material_detail_id, release_type, line_type
,i.batchstep_id
FROM gme_material_details m, gme_batch_step_items i
WHERE m.batch_id = v_batch_id
AND NVL (phantom_id, 0) > 0
AND m.batch_id = i.batch_id(+)
AND m.material_detail_id = i.material_detail_id(+);
SELECT plan_start_date
FROM gme_batch_steps
WHERE batch_id = v_batch_id AND batchstep_id = v_batchstep_id;
SELECT batchstep_no, plan_cmplt_date
FROM gme_batch_steps
WHERE batch_id = v_batch_id
AND (plan_cmplt_date + l_diff) > v_plan_cmplt_date;
SELECT *
FROM gmd_recipe_validity_rules
WHERE recipe_validity_rule_id = NVL (p_recipe_validity_rule_id, -1);
SELECT primary_item_id
FROM wip_entities
WHERE wip_entity_id = v_batch_id;
SELECT plan_qty, dtl_um
FROM gme_material_details
WHERE batch_id = v_batch_id
AND inventory_item_id = v_item_id
AND line_type = 1
ORDER BY line_no ASC;
SELECT segment1
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_item_id;
SELECT 1
FROM DUAL
WHERE EXISTS (SELECT 1
FROM gme_batch_step_charges
WHERE batch_id = v_batch_id);
gme_debug.put_line ('Delete all the pending resource transactions');
IF x_batch_header_rec.update_inventory_ind = 'Y' THEN
DELETE FROM gme_resource_txns
WHERE doc_id = x_batch_header_rec.batch_id
AND doc_type = l_doc_type
AND line_id IN (
SELECT batchstep_resource_id
FROM gme_batch_step_resources
WHERE batch_id = x_batch_header_rec.batch_id
AND batchstep_id IN (
SELECT batchstep_id
FROM gme_batch_steps
WHERE batch_id =
x_batch_header_rec.batch_id
AND step_status = 1) );
DELETE FROM gme_resource_txns_gtmp
WHERE doc_id = x_batch_header_rec.batch_id
AND doc_type = l_doc_type
AND line_id IN (
SELECT batchstep_resource_id
FROM gme_batch_step_resources
WHERE batch_id = x_batch_header_rec.batch_id
AND batchstep_id IN (
SELECT batchstep_id
FROM gme_batch_steps
WHERE batch_id =
x_batch_header_rec.batch_id
AND step_status = 1) );
SELECT primary_uom_code
INTO l_prim_item_um
FROM mtl_system_items
WHERE inventory_item_id =
l_recipe_validity_rule_rec.inventory_item_id
AND organization_id = l_batch_header_rec.organization_id;
gme_debug.put_line ('Update gme_batch_header.');
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)
,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;
( ' After update gme_batch_header. Final plan_start_date '
|| TO_CHAR (x_batch_header_rec.plan_start_date
,'yyyy/mon/dd hh24:mi:ss')
|| ' Final plan_cmplt_date '
|| TO_CHAR (x_batch_header_rec.plan_cmplt_date
,'yyyy/mon/dd hh24:mi:ss') );
SELECT MIN (plan_start_date)
INTO min_start_date
FROM gme_batch_steps
WHERE batch_id = p_batch_header_rec.batch_id;
SELECT MAX (plan_cmplt_date)
INTO max_cmplt_date
FROM gme_batch_steps
WHERE batch_id = p_batch_header_rec.batch_id;
UPDATE gme_batch_header
SET plan_cmplt_date = p_batch_header_rec.plan_cmplt_date
WHERE batch_id = x_batch_header_rec.batch_id;
/* for the current batch if the update inventory ind is set for the batch */
IF x_batch_header_rec.update_inventory_ind = 'Y' THEN
IF (NVL (g_debug, 0) IN
(gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
gme_debug.put_line
('Load all the transactions and resources to the temporary table.');
END IF; /* IF x_batch_header_rec.update_inventory_ind = 'Y' */
/* Now we have to update the transaction dates of pending transactions */
/* for the material lines which are not of step release type */
OPEN cur_get_material (x_batch_header_rec.batch_id);
SELECT plan_start_date, plan_cmplt_date
INTO l_start_date, l_cmplt_date
FROM gme_batch_steps
WHERE batch_id = x_batch_header_rec.batch_id
AND batchstep_id = (SELECT batchstep_id
FROM gme_batch_step_items
WHERE batch_id = x_batch_header_rec.batch_id
AND material_detail_id = l_material_detail_ids(i));
R: When reservations are deleted for a material line
M: When MO Allocations are deleted for a material line
B: When Both reservations and material lines are deleted for a material line
*/
IF x_return_status = 'R' THEN
l_R_count := l_R_count + 1;
gme_common_pvt.log_message('GME_EXPIRED_RESERV_MO_DELETED');
gme_common_pvt.log_message('GME_EXPIRED_RESERV_DELETED');
gme_common_pvt.log_message('GME_EXPIRED_MO_DELETED');
UPDATE gme_batch_header
SET FINITE_SCHEDULED_IND = 0
WHERE batch_id = x_batch_header_rec.batch_id;
/* Update the row who columns */
x_batch_header_rec.last_update_date := gme_common_pvt.g_timestamp;
x_batch_header_rec.last_updated_by := gme_common_pvt.g_user_ident;
x_batch_header_rec.last_update_login := gme_common_pvt.g_login_id;
This particular procedure is used to update the pending transactions of the material detail
line passed in to the trans date to the plan_start or completion dates of the batch/
Parameters
P_batch_header Batch Header Row.
p_date number possible values = 0 for start date and 1 for end date
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected error
Revision History
Rishi Varma bug # 3446787.
Added the p_batchstep_id parameter for updating only the steps,actvities,
resources,resource transactions.
For this modified the sql query to include the condition
"AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id)" for
steps,activities,resources and resource transactions.
***********************************************************************************/
PROCEDURE truncate_date (
p_batch_header_rec IN gme_batch_header%ROWTYPE
,p_date IN NUMBER
,p_batchstep_id IN gme_batch_steps.batchstep_id%TYPE
DEFAULT NULL
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_TRANSACTION';
UPDATE gme_batch_header
SET plan_start_date = p_batch_header_rec.plan_start_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_cmplt_date = p_batch_header_rec.plan_start_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 plan_cmplt_date < p_batch_header_rec.plan_start_date;
UPDATE gme_batch_header
SET due_date = p_batch_header_rec.plan_start_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 < p_batch_header_rec.plan_start_date;
UPDATE gme_batch_steps
SET plan_start_date = p_batch_header_rec.plan_start_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 plan_start_date < p_batch_header_rec.plan_start_date
AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
UPDATE gme_batch_steps
SET plan_cmplt_date = p_batch_header_rec.plan_start_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 plan_cmplt_date < p_batch_header_rec.plan_start_date
AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
UPDATE gme_batch_steps
SET due_date = p_batch_header_rec.plan_start_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 < p_batch_header_rec.plan_start_date
AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
UPDATE gme_batch_step_activities
SET plan_start_date = p_batch_header_rec.plan_start_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 plan_start_date < p_batch_header_rec.plan_start_date
AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
UPDATE gme_batch_step_activities
SET plan_cmplt_date = p_batch_header_rec.plan_start_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 plan_cmplt_date < p_batch_header_rec.plan_start_date
AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
UPDATE gme_batch_step_resources
SET plan_start_date = p_batch_header_rec.plan_start_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 plan_start_date < p_batch_header_rec.plan_start_date
AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
UPDATE gme_batch_step_resources
SET plan_cmplt_date = p_batch_header_rec.plan_start_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 plan_cmplt_date < p_batch_header_rec.plan_start_date
AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
UPDATE gme_resource_txns
SET start_date = p_batch_header_rec.plan_start_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 doc_id = l_batch_id
AND doc_type = l_doc_type
AND start_date < p_batch_header_rec.plan_start_date;
UPDATE gme_resource_txns
SET end_date = p_batch_header_rec.plan_start_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 doc_id = l_batch_id
AND doc_type = l_doc_type
AND end_date < p_batch_header_rec.plan_start_date;
UPDATE gme_resource_txns
SET trans_date = p_batch_header_rec.plan_start_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 doc_id = l_batch_id
AND doc_type = l_doc_type
AND trans_date < p_batch_header_rec.plan_start_date;
UPDATE gme_batch_header
SET plan_cmplt_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 = l_batch_id;
UPDATE gme_batch_header
SET plan_start_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 = l_batch_id
AND plan_start_date > p_batch_header_rec.plan_cmplt_date;
UPDATE gme_batch_header
SET due_date = p_batch_header_rec.due_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 > p_batch_header_rec.plan_cmplt_date;
UPDATE gme_batch_steps
SET plan_start_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 = l_batch_id
AND plan_start_date > p_batch_header_rec.plan_cmplt_date
AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
UPDATE gme_batch_steps
SET plan_cmplt_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 = l_batch_id
AND plan_cmplt_date > p_batch_header_rec.plan_cmplt_date
AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
UPDATE gme_batch_steps
SET 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 = l_batch_id
AND due_date > p_batch_header_rec.plan_cmplt_date
AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
UPDATE gme_batch_step_activities
SET plan_start_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 = l_batch_id
AND plan_start_date > p_batch_header_rec.plan_cmplt_date
AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
UPDATE gme_batch_step_activities
SET plan_cmplt_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 = l_batch_id
AND plan_cmplt_date > p_batch_header_rec.plan_cmplt_date
AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
UPDATE gme_batch_step_resources
SET plan_start_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 = l_batch_id
AND plan_start_date > p_batch_header_rec.plan_cmplt_date
AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
UPDATE gme_batch_step_resources
SET plan_cmplt_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 = l_batch_id
AND plan_cmplt_date > p_batch_header_rec.plan_cmplt_date
AND (p_batchstep_id IS NULL OR batchstep_id = p_batchstep_id);
UPDATE gme_resource_txns
SET start_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 doc_id = l_batch_id
AND doc_type = l_doc_type
AND start_date > p_batch_header_rec.plan_cmplt_date;
UPDATE gme_resource_txns
SET end_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 doc_id = l_batch_id
AND doc_type = l_doc_type
AND end_date > p_batch_header_rec.plan_cmplt_date;
UPDATE gme_resource_txns
SET trans_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 doc_id = l_batch_id
AND doc_type = l_doc_type
AND trans_date > p_batch_header_rec.plan_cmplt_date;