[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
batch_hist_insert_err EXCEPTION;
SELECT phantom_id
FROM gme_material_details d, gme_batch_header h
WHERE d.batch_id = x_batch_header_rec.batch_id
AND phantom_id IS NOT NULL
AND h.batch_id = d.phantom_id
AND h.batch_status <> 4;
SELECT material_detail_id
FROM gme_material_details
WHERE batch_id = v_batch_id
FOR UPDATE OF actual_qty NOWAIT;
SELECT COUNT (*)
FROM gme_batch_steps
WHERE step_status < 4 AND batch_id = x_batch_header_rec.batch_id;
SELECT *
FROM gme_material_details
WHERE batch_id = v_batch_id
AND line_type = 1;
IF p_batch_header_rec.delete_mark = 1 THEN
RAISE marked_for_deletion;
gme_debug.put_line(g_pkg_name||'.'||l_api_name||' Invoking delete_prod_supply_resv for batch header' );
gme_supply_res_pvt.delete_batch_prod_supply_resv (
p_batch_header_rec => l_batch_header_rec
,x_msg_count => l_message_count
,x_msg_data => l_message_list
,x_return_status => x_return_status);
DELETE FROM mtl_txn_request_lines l
WHERE organization_id = x_batch_header_rec.organization_id
AND transaction_source_type_id = gme_common_pvt.g_txn_source_type
AND txn_source_id = x_batch_header_rec.batch_id
AND txn_source_line_id = l_material_details_tab(i).material_detail_id
AND line_status = 7
AND NOT EXISTS (SELECT 1
FROM mtl_txn_request_headers mtrh
WHERE mtrh.header_id = l.header_id)
AND NOT EXISTS (SELECT 1
FROM mtl_material_transactions_temp mmtt
where l.line_id = mmtt.move_order_line_id);
IF x_batch_header_rec.update_inventory_ind = 'Y' THEN
IF (g_debug <= gme_debug.g_log_procedure) THEN
gme_debug.put_line ( g_pkg_name
|| '.'
|| l_api_name
|| ':'
|| 'calling purge exceptions for batch_id='
|| x_batch_header_rec.batch_id);
,p_delete_reservations => 'T'
,x_return_status => x_return_status);
UPDATE MTL_TXN_REQUEST_Lines
SET line_status = 5
WHERE line_id in
(SELECT mtrl.line_id
FROM MTL_TXN_REQUEST_Lines mtrl, mtl_txn_request_headers mtrh
WHERE mtrl.TRANSACTION_SOURCE_TYPE_ID = 5
AND mtrl.LINE_STATUS = 7
AND mtrl.LPN_ID IS NOT NULL
AND mtrh.header_id = mtrl.header_id
AND mtrh.ORGANIZATION_ID = mtrl.organization_id
AND mtrl.txn_source_id = x_batch_header_rec.batch_id
AND mtrl.ORGANIZATION_ID = x_batch_header_rec.organization_id
AND mtrh.MOVE_ORDER_TYPE = 6);
gme_debug.put_line ('Calling Batch Header DBL.Update Row ');
/* Update the batch step to the database */
IF NOT (gme_batch_header_dbl.update_row (x_batch_header_rec) ) THEN
RAISE batch_header_upd_err;
/* Insert the event into the batch history table */
IF x_batch_header_rec.update_inventory_ind = 'Y' THEN
IF g_debug <= gme_debug.g_log_procedure THEN
gme_debug.put_line ('Calling Create History.');
RAISE batch_hist_insert_err;
RAISE BATCH_HIST_INSERT_ERR;
/* Now update the batch step status to close */
gme_close_batch_pvt.fetch_batch_steps (x_batch_header_rec.batch_id
,NULL
,l_batch_steps_tab
,l_return_status);
/* 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;
IF NOT gme_material_details_dbl.update_row(l_product_rec) THEN
RAISE MATERIAL_DETAIL_UPD_ERR;
WHEN batch_hist_insert_err THEN
x_return_status := fnd_api.g_ret_sts_error;
gme_debug.put_line ('Terminating due to error deleteing reservations against this batch supply.');
IF NOT (gme_batch_history_dbl.insert_row (l_ins_history, l_ins_history) ) THEN
RETURN FALSE;
SELECT step_close_date
FROM gme_batch_steps
WHERE batch_id IN (
SELECT DISTINCT batch_id
FROM gme_material_details
START WITH batch_id =
p_batch_header_rec.batch_id
CONNECT BY batch_id = PRIOR phantom_id)
AND NVL (step_close_date, p_batch_header_rec.batch_close_date) >
p_batch_header_rec.batch_close_date;
SELECT DISTINCT batch_id
FROM gme_material_details
START WITH batch_id = v_batch_id
CONNECT BY batch_id = PRIOR phantom_id;
SELECT trans_date
FROM gme_resource_txns_gtmp
WHERE doc_type = 'PROD'
AND doc_id = v_batch_id
AND completed_ind = 1
AND delete_mark = 0
AND action_code NOT IN ('DEL', 'REVS', 'REVL')
AND NVL (trans_date, p_batch_header_rec.batch_close_date) >
p_batch_header_rec.batch_close_date
AND ROWNUM = 1;
SELECT trans_date
FROM gme_inventory_txns_gtmp
WHERE doc_type = 'PROD'
AND doc_id = v_batch_id
AND completed_ind = 1
AND transaction_no <> 2
AND action_code NOT IN ('DELP', 'DELC')
AND NVL (trans_date, p_batch_header_rec.batch_close_date) >
p_batch_header_rec.batch_close_date; */
SELECT material_detail_id
from gme_material_details
WHERE batch_id = v_batch_id;
SELECT transaction_date
FROM mtl_material_transactions mmt
WHERE trx_source_line_id = v_mat_det_id
AND transaction_source_id = v_batch_id
AND transaction_source_type_id = v_txn_source_type
AND NOT EXISTS ( SELECT /*+ no_unnest */
transaction_id1
FROM gme_transaction_pairs
WHERE transaction_id1 = mmt.transaction_id
AND pair_type = v_pairs_reversal_type)
AND NVL (transaction_date, p_batch_header_rec.batch_close_date) >
p_batch_header_rec.batch_close_date
AND ROWNUM = 1;
SELECT actual_cmplt_date
FROM gme_batch_header
WHERE batch_id IN (
SELECT DISTINCT batch_id
FROM gme_material_details
START WITH batch_id =
p_batch_header_rec.batch_id
CONNECT BY batch_id = PRIOR phantom_id)
AND NVL (actual_cmplt_date, p_batch_header_rec.batch_close_date) >
p_batch_header_rec.batch_close_date;
IF (p_batch_header_rec.update_inventory_ind = 'Y') THEN
OPEN cur_get_batches (p_batch_header_rec.batch_id);
SELECT routing_id
FROM gme_batch_header
WHERE batch_id = v_batch_id;
SELECT d.dep_step_id
FROM gme_batch_step_dependencies d
WHERE d.batch_id = v_batch_id
START WITH ( (d.batch_id = v_batch_id)
AND ( (v_batchstep_id IS NULL)
OR (batchstep_id = v_batchstep_id) ) )
CONNECT BY d.batch_id = PRIOR d.batch_id
AND d.batchstep_id = PRIOR d.dep_step_id
GROUP BY d.dep_step_id
ORDER BY MAX (LEVEL) DESC;
SELECT *
FROM gme_batch_steps
WHERE batch_id = v_batch_id AND batchstep_id = v_step_id;
SELECT *
FROM gme_batch_steps s
WHERE s.batch_id = v_batch_id
AND s.batchstep_id NOT IN (SELECT dep_step_id
FROM gme_batch_step_dependencies
WHERE batch_id = v_batch_id)
ORDER BY batchstep_no;