[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT MAX (plan_cmplt_date)
FROM gme_batch_steps
WHERE batch_id = v_batch_id;
SELECT MIN (plan_start_date)
FROM gme_batch_steps
WHERE batch_id = v_batch_id;
SELECT material_detail_id, line_type
FROM gme_material_details
WHERE batch_id = v_batch_id;
SELECT s.batchstep_id, d.dep_type, d.standard_delay
,s.step_status
FROM gme_batch_step_dependencies d, gme_batch_steps s
WHERE d.batchstep_id = v_batchstep_id
AND s.batchstep_id = d.dep_step_id
AND s.batch_id = v_batch_id
AND d.batch_id = s.batch_id
ORDER BY s.plan_start_date;
SELECT d.batchstep_id, d.dep_type, d.standard_delay, s.step_status
FROM gme_batch_step_dependencies d, gme_batch_steps s
WHERE d.batchstep_id = s.batchstep_id
AND d.dep_step_id = v_batchstep_id
AND s.batch_id = v_batch_id
AND d.batch_id = s.batch_id
ORDER BY s.plan_cmplt_date;
SELECT batchstep_id, plan_start_date, plan_cmplt_date
FROM gme_batch_steps
WHERE batchstep_id = v_batchstep_id;
SELECT MAX ( DECODE (d.dep_type
,1, s.plan_start_date
,0, s.plan_cmplt_date)
+ d.standard_delay / 24) max_date
FROM gme_batch_step_dependencies d, gme_batch_steps s
WHERE d.batchstep_id = v_batchstep_id
AND s.batchstep_id = d.dep_step_id
AND s.batch_id = v_batch_id
AND d.batch_id = s.batch_id;
SELECT 1
FROM DUAL
WHERE EXISTS (
SELECT 1
FROM gme_batch_step_charges
WHERE batch_id = v_batch_id
AND batchstep_id = v_batchstep_id);
IF l_batch_header_rec.update_inventory_ind = 'Y' THEN
IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
gme_debug.put_line ( 'deleting transactions for '
|| x_batch_step_rec.batchstep_id);
DELETE FROM gme_resource_txns
WHERE doc_type = l_doc_type
AND doc_id = x_batch_step_rec.batch_id
AND line_id IN (
SELECT batchstep_resource_id
FROM gme_batch_step_resources
WHERE batch_id = x_batch_step_rec.batch_id
AND batchstep_id = x_batch_step_rec.batchstep_id);
DELETE FROM gme_resource_txns_gtmp
WHERE doc_type = l_doc_type
AND doc_id = x_batch_step_rec.batch_id
AND line_id IN (
SELECT batchstep_resource_id
FROM gme_batch_step_resources
WHERE batch_id = x_batch_step_rec.batch_id
AND batchstep_id = x_batch_step_rec.batchstep_id);
IF NOT (gme_batch_steps_dbl.update_row (x_batch_step_rec) ) THEN
RAISE save_data_error;
UPDATE gme_batch_steps
SET due_date = p_batch_step_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 = p_batch_step_rec.batch_id
AND batchstep_id = p_batch_step_rec.batchstep_id;
/* Update the batch dates etc. only when this procedure is in for the
main step and not during the recursive calls */
OPEN cur_get_max (l_batch_header_rec.batch_id);
/* Since we update the batch dates in the code else where
we want to fetch the row, so that the timestamps are most
current, which is what is used to make sure that someone
else has not updated this batch during processing of this
program execution. That is why l_batch_header_rec is introduced.*/
IF NOT (gme_batch_header_dbl.fetch_row (l_batch_header_rec
,l_batch_header2_rec) ) THEN
RAISE batch_header_fetch_error;
IF NOT (gme_batch_header_dbl.update_row (l_batch_header2_rec) ) THEN
RAISE save_data_error;
/* 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_step_rec.batch_id);
/* SELECT batchstep_id INTO l_batch_step2_rec.batchstep_id FROM GME_BATCH_STEP_ITEMS
WHERE material_detail_id = l_material_detail_id_tbl(i);
SELECT batchstep_id INTO l_batch_step2_rec.batchstep_id FROM GME_BATCH_STEP_ITEMS
WHERE material_detail_id = l_material_detail_id_tbl(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');
This particular procedure updates all necessary tables.
Parameters
p_batch_step_rec The batch step row to identify the step.
p_diff Duration used to reschedule the start date with.
p_diff_end Duration used to reschedule the end date with.
x_return_status outcome of the API call
S - Success
E - Error
U - Unexpected error
HISTORY
G.Kelly 22-Feb-2002 Bug - Rewrote the code.
A Newbury 05-Aug-2003 B3045672 Modified cursor to include manual and incremental
Pawan Kuamr 01-26-2004 For rework of bug 3010444
***************************************************************************************/
PROCEDURE save_all_data (
p_batch_step_rec IN gme_batch_steps%ROWTYPE
,p_use_workday_cal IN VARCHAR2
,p_contiguity_override IN VARCHAR2
,p_start_date IN DATE
,p_end_date IN DATE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'SAVE_ALL_DATA';
SELECT material_detail_id, line_type
FROM gme_material_details det
WHERE batch_id = v_batch_id
AND release_type IN (1, 2, 3)
AND EXISTS (
SELECT 1
FROM gme_batch_step_items
WHERE batch_id = v_batch_id
AND batchstep_id = v_batchstep_id
AND material_detail_id = det.material_detail_id);
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');