[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM gme_inventory_txns_gtmp
-- WHERE ACTION_CODE ='REVL'; -- Should this be indexed.
SELECT *
FROM gme_inventory_txns_gtmp
WHERE transaction_no <> 2 -- Should this be indexed.
AND doc_id = v_doc_id
AND line_type = v_line_type
AND item_id = v_item_id
AND material_detail_id = v_detail_id
AND whse_code = v_whse_code
AND lot_id = v_lot_id
AND LOCATION = v_location
AND completed_ind = v_completed_ind
AND trans_date = v_trans_date
AND ABS (trans_qty) = v_qty;
SELECT *
FROM gme_inventory_txns_gtmp
WHERE transaction_no <> 2 -- Should this be indexed.
AND doc_id = v_doc_id
AND line_type = v_line_type
AND item_id = v_item_id
AND material_detail_id = v_detail_id
AND whse_code = v_whse_code
AND lot_id = v_lot_id
AND LOCATION = v_location
AND completed_ind = v_completed_ind
AND ABS (trans_qty) = v_qty;
SELECT *
FROM gme_inventory_txns_gtmp
WHERE completed_ind = 1 AND trans_qty = 0
ORDER BY line_type
,item_id
,material_detail_id
,whse_code
,lot_id
,LOCATION
,completed_ind
,trans_id;
SELECT COUNT (1)
FROM gme_inventory_txns_gtmp
WHERE doc_id = p_batch_id AND doc_type = p_batch_type;
|| 'Update Inventory Ind:'
|| p_batch_row.update_inventory_ind
|| ' Batch Id:'
|| p_batch_row.batch_id);
IF (p_batch_row.update_inventory_ind <> 'Y') THEN
IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
gme_debug.put_line ( g_pkg_name
|| '.'
|| l_api_name
|| ':'
|| 'No Transactions will be loaded');
INSERT INTO gme_inventory_txns_gtmp
(trans_id,
item_id,
co_code,
orgn_code,
whse_code,
lot_id,
location,
doc_id,
doc_type,
doc_line,
line_type,
reason_code,
trans_date,
trans_qty,
trans_qty2,
qc_grade,
lot_status,
trans_stat,
trans_um,
trans_um2,
completed_ind,
staged_ind,
gl_posted_ind,
event_id,
delete_mark,
text_code,
action_code,
material_detail_id,
transaction_no,
def_trans_ind,
organization_id,
locator_id,
subinventory,
alloc_um,
alloc_qty
)
SELECT i.trans_id,
i.item_id,
i.co_code,
i.orgn_code,
i.whse_code,
i.lot_id,
i.location,
i.doc_id,
i.doc_type,
i.doc_line,
i.line_type,
i.reason_code,
i.trans_date,
i.trans_qty,
i.trans_qty2,
i.qc_grade,
i.lot_status,
i.trans_stat,
i.trans_um,
i.trans_um2,
i.completed_ind,
i.staged_ind,
i.gl_posted_ind,
i.event_id,
--Rishi Varma 25-05-2004 3476239 Serono enh.
--setting the delete_mark to 9 only if phantoms are involved
decode(g.phantom_id,NULL,i.delete_mark,9),
i.text_code,
'NONE',
i.line_id, -- I.TRANS_ID,-- Using TRANS ID for tranasction_no
1, -- This means Display the Record Use For Forms,
0, -- def_trans_ind => default it to NO
0, -- For Future Use
0, -- For Future Use
--Swapna Kommineni bug#3897220 24-SEP-2004
-- subinvenoty is inserted with the trans_id which is used to check
-- before calling the delete_pending_trans procedure in GMEVTXNB.pls
trans_id, --NULL, -- For Future Use
g.item_um,
-- B2834826 prevent uom conv if not required
decode(g.item_um,i.trans_um2,
ABS(i.trans_qty2),
gmicuom.uom_conversion (
i.item_id,
i.lot_id,
ABS (i.trans_qty),
i.trans_um,
g.item_um,
0) )
FROM ic_tran_pnd i, gme_material_details g
WHERE doc_id = p_batch_row.batch_id AND
doc_type = l_doc_type AND
line_id = g.material_detail_id AND
-- Bug 3777331 commented next condition since not needed
--doc_id = batch_id AND
delete_mark <> 1 --3187467
-- Bug 3777331 added next AND condition and commented rest of the where clause
AND reverse_id IS NULL;
UPDATE gme_inventory_txns_gtmp
-- SET ACTION_CODE = 'REVS'
SET transaction_no = 2
WHERE trans_id IN
(l_last_txn.trans_id, l_current_txn.trans_id);
/* Added the action_code = NONE and the batch_id condition as the following update */
/* was updating the rows of previous batches which have been modified */
UPDATE gme_inventory_txns_gtmp
SET transaction_no = 2
WHERE action_code = 'NONE'
AND doc_id = p_batch_row.batch_id
AND ( (line_type = -1 AND trans_qty > 0)
OR (line_type <> -1 AND trans_qty < 0) );
UPDATE gme_inventory_txns_gtmp
SET transaction_no = 2
WHERE trans_id = l_match_rev_id;
SELECT *
FROM gme_resource_txns_gtmp
WHERE action_code = 'REVL'; -- Should this be indexed.
SELECT *
FROM gme_resource_txns_gtmp
WHERE action_code NOT IN ('REVL', 'REVS')
AND line_id = v_line_id
AND completed_ind = 1
ORDER BY trans_date DESC, poc_trans_id;
SELECT COUNT (1)
FROM gme_resource_txns_gtmp
WHERE doc_id = p_batch_id;
INSERT INTO gme_resource_txns_gtmp
(poc_trans_id, orgn_code, doc_type, doc_id, line_type
,line_id, resources, resource_usage, trans_um
,trans_date, completed_ind, posted_ind, reason_code, reason_id
,start_date, end_date, text_code, transaction_no
,overrided_protected_ind, action_code, delete_mark
,instance_id, sequence_dependent_ind,organization_id
,attribute1, attribute2, attribute3, attribute4
,attribute5, attribute6, attribute7, attribute8
,attribute9, attribute10, attribute11, attribute12
,attribute13, attribute14, attribute15, attribute16
,attribute17, attribute18, attribute19, attribute20
,attribute21, attribute22, attribute23, attribute24
,attribute25, attribute26, attribute27, attribute28
,attribute29, attribute30, attribute_category)
SELECT poc_trans_id, orgn_code, doc_type, doc_id, line_type
,line_id, resources, resource_usage, trans_qty_um
,trans_date, completed_ind, posted_ind, reason_code, reason_id
,start_date, end_date, text_code, poc_trans_id
,overrided_protected_ind, 'NONE', delete_mark, instance_id
,sequence_dependent_ind,organization_id
,attribute1, attribute2, attribute3, attribute4
,attribute5, attribute6, attribute7, attribute8
,attribute9, attribute10, attribute11, attribute12
,attribute13, attribute14, attribute15, attribute16
,attribute17, attribute18, attribute19, attribute20
,attribute21, attribute22, attribute23, attribute24
,attribute25, attribute26, attribute27, attribute28
,attribute29, attribute30, attribute_category
FROM gme_resource_txns
WHERE doc_id = p_batch_row.batch_id
AND doc_type = l_doc_type
AND delete_mark = 0
--Rishi Varma B3818266/3759970 10-08-2004
/*Added the condition for elimination of reversed records*/
AND reverse_id IS NULL;
UPDATE gme_resource_txns_gtmp
SET action_code = 'REVL'
WHERE resource_usage < 0 AND completed_ind = 1;
UPDATE gme_resource_txns_gtmp
SET action_code = 'REVS'
WHERE poc_trans_id = mtch_resrc_revs.poc_trans_id;
SELECT material_detail_id
FROM gme_material_details
WHERE batch_id = v_batch_id;
UPDATE gme_inventory_txns_gtmp
SET def_trans_ind = 1
WHERE trans_id = l_def_trans_id;
SELECT whse_code
FROM ps_whse_eff
WHERE plant_code = p_orgn_code
AND (whse_item_id IS NULL OR whse_item_id = p_item_id)
AND ( (p_line_type > 0 AND replen_ind = 1)
OR (p_line_type < 0 AND consum_ind = 1) )
ORDER BY whse_item_id, whse_code;
SELECT trans_id, whse_code
FROM gme_inventory_txns_gtmp
WHERE doc_id = v_batch_id
AND doc_type = v_doc_type
AND material_detail_id = v_line_id
AND lot_id = 0
AND LOCATION = p_default_loct
AND
--Rishi Varma B3476239 Serono enh.
--delete_mark = 0 AND
transaction_no <> 2
ORDER BY line_type
,item_id
,material_detail_id
,whse_code
,lot_id
,LOCATION
,completed_ind
,trans_id;
SELECT loct_ctl
FROM ic_whse_mst
WHERE whse_code = v_whse_code;
SELECT *
INTO l_matl_dtl
FROM gme_material_details
WHERE material_detail_id = p_line_id;
SELECT *
INTO l_item_mst
FROM ic_item_mst
WHERE item_id = l_matl_dtl.item_id;
SELECT batch_type
INTO l_batch_type
FROM gme_batch_header
WHERE batch_id = l_matl_dtl.batch_id;
SELECT COUNT (1)
INTO l_cnt
FROM gme_inventory_txns_gtmp
WHERE doc_id = l_matl_dtl.batch_id
AND doc_type = l_doc_type
AND material_detail_id = p_line_id
AND transaction_no <> 2
AND trans_qty = 0;
SELECT trans_id
INTO x_def_trans_id
FROM gme_inventory_txns_gtmp
WHERE doc_id = l_matl_dtl.batch_id
AND doc_type = l_doc_type
AND material_detail_id = p_line_id
AND transaction_no <> 2
AND trans_qty = 0;
SELECT *
INTO l_ic_tran_pnd
FROM ic_tran_pnd
WHERE trans_id = get_rec.trans_id;
UPDATE gme_inventory_txns_gtmp g
SET def_trans_ind = 1
WHERE trans_id = (SELECT MIN (trans_id)
FROM gme_inventory_txns_gtmp
WHERE material_detail_id = g.material_detail_id);