[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*)
INTO l_count
FROM gmf_batch_requirements
WHERE batch_id = p_batch_id AND
delete_mark = 0;
gme_debug.put_line ('inserting into req table from gtmp table');
INSERT INTO gmf_batch_requirements(
vib_id,
batch_id,
product_item_id,
prod_material_detail_id,
ingredient_item_id,
ing_material_detail_id,
resources,
batchstep_resource_id,
derived_cost_alloc,
required_doc_qty,
delete_mark,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
requirement_id,
organization_id,
vib_profile_value)
SELECT
NULL,
batch_id,
product_item_id,
prod_material_detail_id,
ingredient_item_id,
ing_material_detail_id,
resources,
batchstep_resource_id,
derived_cost_alloc,
required_doc_qty,
delete_mark,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
requirement_id,
organization_id,
vib_profile_value
FROM gmf_batch_requirements_gtmp
WHERE batch_id = p_batch_id;
gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
PROCEDURE Update_Batch_Requirements
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_batch_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_count PLS_INTEGER;
l_api_name VARCHAR2(30) := 'Update_Batch_Requirements';
UPDATE gmf_batch_requirements
SET delete_mark = 1
WHERE
batch_id = p_batch_id;
gme_debug.put_line ('inserting into req table from gtmp');
INSERT INTO gmf_batch_requirements(
vib_id,
batch_id,
product_item_id,
prod_material_detail_id,
ingredient_item_id,
ing_material_detail_id,
resources,
batchstep_resource_id,
derived_cost_alloc,
required_doc_qty,
delete_mark,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
requirement_id,
organization_id,
vib_profile_value)
SELECT
NULL,
batch_id,
product_item_id,
prod_material_detail_id,
ingredient_item_id,
ing_material_detail_id,
resources,
batchstep_resource_id,
derived_cost_alloc,
required_doc_qty,
delete_mark,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
requirement_id,
organization_id,
vib_profile_value
FROM gmf_batch_requirements_gtmp;
gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
END Update_Batch_Requirements;
PROCEDURE Delete_Batch_Requirements
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
p_batch_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
l_batch_status gme_batch_header.batch_status%TYPE;
l_api_name VARCHAR2(30) := 'Delete_Batch_Requirements';
SELECT batch_status
INTO l_batch_status
FROM gme_batch_header
WHERE batch_id = p_batch_id;
UPDATE gmf_batch_requirements
SET delete_mark = 1
WHERE
batch_id = p_batch_id AND
delete_mark = 0;
gme_debug.put_line (sql%ROWCOUNT || ' rows deleted');
SELECT m.material_detail_id, m.inventory_item_id as item_id, m.organization_id,
decode(m.plan_qty, 0, nvl(m.wip_plan_qty,0), m.plan_qty) prod_plan_qty,
m.cost_alloc, s.batchstep_id
FROM gme_material_details m, gme_batch_step_items s
WHERE m.batch_id = c_batch_id AND
m.line_type = 1 AND
m.material_detail_id = s.material_detail_id and
decode(m.plan_qty, 0, m.wip_plan_qty, m.plan_qty) <> 0 and
nvl(m.cost_alloc,0) <> 0;
SELECT dep_step_id
FROM GME_BATCH_STEP_DEPENDENCIES
WHERE batch_id = c_batch_id
AND l_gmf_asg_cons_yld_step_lvl = 'N'
START WITH batchstep_id = c_batchstep_id
CONNECT BY PRIOR dep_step_id = batchstep_id
UNION
SELECT c_batchstep_id dep_step_id FROM DUAL;
SELECT ing_material_detail_id, batchstep_resource_id,
SUM(derived_cost_alloc) total_prod_alloc
FROM gmf_batch_requirements_gtmp
WHERE batch_id = c_batch_id
GROUP BY ing_material_detail_id, batchstep_resource_id;
SELECT batch_status
INTO l_batch_status
FROM gme_batch_header
WHERE batch_id = p_batch_id;
SELECT count(*)
INTO l_count
FROM gme_material_details m
WHERE m.batch_id = p_batch_id AND
m.line_type = 1 AND
decode(m.plan_qty, 0, m.wip_plan_qty, m.plan_qty) <> 0 AND
nvl(m.cost_alloc,0) <> 0;
-- Delete the temp table first
DELETE from gmf_batch_requirements_gtmp;
-- insert records into the batch requirements table
INSERT INTO gmf_batch_requirements_gtmp(
vib_id,
batch_id,
product_item_id,
prod_material_detail_id,
ingredient_item_id,
ing_material_detail_id,
resources,
batchstep_resource_id,
derived_cost_alloc,
required_doc_qty,
delete_mark,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
requirement_id,
organization_id,
vib_profile_value)
SELECT
NULL,
p_batch_id,
p.item_id,
p.material_detail_id,
m.inventory_item_id,
m.material_detail_id,
NULL,
NULL,
p.cost_alloc,
p.cost_alloc * ( decode(m.plan_qty, 0, nvl(m.wip_plan_qty,0), m.plan_qty) /
p.prod_plan_qty),
0,
-1,
sysdate,
-1,
sysdate,
NULL,
gmf_vib_id_s.nextval,
p.organization_id,
l_vib_profile_value
FROM gme_batch_step_items s,
gme_material_details m
WHERE batchstep_id = ds.dep_step_id AND
s.material_detail_id = m.material_detail_id AND
m.line_type <> 1;
INSERT INTO gmf_batch_requirements_gtmp(
vib_id,
batch_id,
product_item_id,
prod_material_detail_id,
ingredient_item_id,
ing_material_detail_id,
resources,
batchstep_resource_id,
derived_cost_alloc,
required_doc_qty,
delete_mark,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
requirement_id,
organization_id,
vib_profile_value)
SELECT
NULL,
p_batch_id,
p.item_id,
p.material_detail_id,
NULL,
NULL,
m.resources,
m.batchstep_resource_id,
p.cost_alloc,
p.cost_alloc * ( nvl(m.plan_rsrc_usage,0) / p.prod_plan_qty),
0,
-1,
sysdate,
-1,
sysdate,
NULL,
gmf_vib_id_s.nextval,
p.organization_id,
l_vib_profile_value
FROM gme_batch_step_resources m
WHERE batchstep_id = ds.dep_step_id;
-- Now insert any remaining ing/res which was not used for any product
IF g_debug <= gme_debug.g_log_statement THEN
gme_debug.put_line ('Now insert any remaining ingredients which was not used for any product...');
INSERT INTO gmf_batch_requirements_gtmp(
vib_id,
batch_id,
product_item_id,
prod_material_detail_id,
ingredient_item_id,
ing_material_detail_id,
resources,
batchstep_resource_id,
derived_cost_alloc,
required_doc_qty,
delete_mark,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
requirement_id,
organization_id,
vib_profile_value)
SELECT
NULL,
p_batch_id,
p.inventory_item_id,
p.material_detail_id,
i.inventory_item_id,
i.material_detail_id,
NULL,
NULL,
p.cost_alloc,
p.cost_alloc * ( decode(i.plan_qty, 0, nvl(i.wip_plan_qty,0), i.plan_qty) /
decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty)),
0,
-1,
sysdate,
-1,
sysdate,
NULL,
gmf_vib_id_s.nextval,
p.organization_id,
l_vib_profile_value
FROM gme_material_details p, gme_material_details i
WHERE
p.batch_id = p_batch_id AND
i.batch_id = p_batch_id AND
p.line_type = 1 AND
decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty) <> 0 AND
nvl(p.cost_alloc,0) <> 0 AND
i.line_type <> 1 AND
i.material_detail_id NOT IN (
SELECT nvl(ing_material_detail_id, -99)
FROM gmf_batch_requirements_gtmp f
WHERE
batch_id = p_batch_id );
gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
gme_debug.put_line ('Now insert any remaining resources which was not used for any product...');
INSERT INTO gmf_batch_requirements_gtmp(
vib_id,
batch_id,
product_item_id,
prod_material_detail_id,
ingredient_item_id,
ing_material_detail_id,
resources,
batchstep_resource_id,
derived_cost_alloc,
required_doc_qty,
delete_mark,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
requirement_id,
organization_id,
vib_profile_value)
SELECT
NULL,
p_batch_id,
p.inventory_item_id,
p.material_detail_id,
NULL,
NULL,
r.resources,
r.batchstep_resource_id,
p.cost_alloc,
p.cost_alloc * ( nvl(r.plan_rsrc_usage,0) /
decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty)),
0,
-1,
sysdate,
-1,
sysdate,
NULL,
gmf_vib_id_s.nextval,
p.organization_id,
l_vib_profile_value
FROM gme_material_details p, gme_batch_step_resources r, gme_batch_steps s
WHERE
p.batch_id = p_batch_id AND
s.batch_id = p_batch_id AND
s.batchstep_id = r.batchstep_id AND
p.line_type = 1 AND
decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty) <> 0 AND
nvl(p.cost_alloc,0) <> 0 AND
r.batchstep_resource_id NOT IN (
SELECT nvl(batchstep_resource_id, -99)
FROM gmf_batch_requirements_gtmp f
WHERE
batch_id = p_batch_id );
gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
-- Now insert any product that may have been missed out
IF g_debug <= gme_debug.g_log_statement THEN
gme_debug.put_line ('Now insert any product that may have been missed out...');
INSERT INTO gmf_batch_requirements_gtmp(
vib_id,
batch_id,
product_item_id,
prod_material_detail_id,
ingredient_item_id,
ing_material_detail_id,
resources,
batchstep_resource_id,
derived_cost_alloc,
required_doc_qty,
delete_mark,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
requirement_id,
organization_id,
vib_profile_value)
SELECT
NULL,
p_batch_id,
p.inventory_item_id,
p.material_detail_id,
i.inventory_item_id,
i.material_detail_id,
NULL,
NULL,
p.cost_alloc,
p.cost_alloc * ( decode(i.plan_qty, 0, nvl(i.wip_plan_qty,0), i.plan_qty) /
decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty)),
0,
-1,
sysdate,
-1,
sysdate,
NULL,
gmf_vib_id_s.nextval,
p.organization_id,
l_vib_profile_value
FROM gme_material_details p, gme_material_details i
WHERE
p.batch_id = p_batch_id AND
i.batch_id = p_batch_id AND
p.line_type = 1 AND
decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty) <> 0 AND
nvl(p.cost_alloc,0) <> 0 AND
i.line_type <> 1 AND
p.material_detail_id NOT IN (
SELECT prod_material_detail_id
FROM gmf_batch_requirements_gtmp f
WHERE
batch_id = p_batch_id );
gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
gme_debug.put_line ('inserting remaining resources');
INSERT INTO gmf_batch_requirements_gtmp(
vib_id,
batch_id,
product_item_id,
prod_material_detail_id,
ingredient_item_id,
ing_material_detail_id,
resources,
batchstep_resource_id,
derived_cost_alloc,
required_doc_qty,
delete_mark,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
requirement_id,
organization_id,
vib_profile_value)
SELECT
NULL,
p_batch_id,
p.inventory_item_id,
p.material_detail_id,
NULL,
NULL,
r.resources,
r.batchstep_resource_id,
p.cost_alloc,
p.cost_alloc * ( nvl(r.plan_rsrc_usage,0) /
decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty)),
0,
-1,
sysdate,
-1,
sysdate,
NULL,
gmf_vib_id_s.nextval,
p.organization_id,
l_vib_profile_value
FROM gme_material_details p, gme_batch_step_resources r, gme_batch_steps s
WHERE
p.batch_id = p_batch_id AND
s.batch_id = p_batch_id AND
s.batchstep_id = r.batchstep_id AND
p.line_type = 1 AND
decode(p.plan_qty, 0, p.wip_plan_qty, p.plan_qty) <> 0 AND
nvl(p.cost_alloc,0) <> 0 AND
p.material_detail_id NOT IN (
SELECT prod_material_detail_id
FROM gmf_batch_requirements_gtmp f
WHERE
batch_id = p_batch_id );
gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
-- Now update the derived cost alloc and required doc qty
IF g_debug <= gme_debug.g_log_statement THEN
gme_debug.put_line ('Now updating the derived cost alloc and required doc qty ...');
UPDATE gmf_batch_requirements_gtmp
SET derived_cost_alloc = derived_cost_alloc/i.total_prod_alloc,
required_doc_qty = required_doc_qty/i.total_prod_alloc
WHERE
batch_id = p_batch_id AND
nvl(ing_material_detail_id, -1) = nvl(i.ing_material_detail_id,-1) AND
nvl(batchstep_resource_id, -1) = nvl(i.batchstep_resource_id,-1) AND
delete_mark = 0;
dbms_output.put_line( sql%rowcount || ' rows inserted');
SELECT *
FROM gmf_batch_requirements
WHERE
batch_id = p_tran_rec.transaction_source_id AND
prod_material_detail_id = p_tran_rec.trx_source_line_id AND
delete_mark = 0;
SELECT v.consume_layer_id, v.consume_layer_date, v.line_type, v.requirement_id, v.consume_ib_doc_qty,
v.consume_ib_pri_qty,
decode (tp.transaction_id2, NULL, ol.layer_id, ol2.layer_id) c_layer_id,
decode (tp.transaction_id2, NULL, v.consume_layer_date, t2.transaction_date) c_trans_date,
decode (tp.transaction_id2, NULL, ol.remaining_ib_doc_qty, ol2.remaining_ib_doc_qty) remaining_ib_doc_qty,
decode (tp.transaction_id2, NULL, ol.layer_doc_qty, ol2.layer_doc_qty) layer_doc_qty,
decode (tp.transaction_id2, NULL, 'N', 'Y') c_rev_layer,
decode (tp.transaction_id2, NULL, ol.ROWID, ol2.ROWID) c_rowid
FROM gmf_batch_vib_details v,
gmf_incoming_material_layers il,
gmf_outgoing_material_layers ol,
mtl_material_transactions t,
gme_transaction_pairs tp,
gmf_outgoing_material_layers ol2,
mtl_material_transactions t2
WHERE
il.mmt_transaction_id = p_tran_rec.reverse_id AND -- incoming layer of reversed prod yield
-- Bug 6312166. il.mmt_transaction_id = p_tran_rec.transaction_id AND -- incoming layer of reversed prod yield
nvl(il.lot_number, 'x') = nvl(p_tran_rec.lot_number, 'x') AND
-- Bug 6312166. il.lot_number (+) = p_tran_rec.lot_number AND
v.prod_layer_id = il.layer_id AND -- VIB details of above reversed layer
v.line_type <> 0 AND -- material only
ol.layer_id (+) = v.consume_layer_id AND -- getting consumption layer for above reversed prod yield
t.transaction_id (+) = ol.mmt_transaction_id AND -- getting txn for above consumption layer
--
-- below 4 lines, get the above ingredient reversals, if any
--
tp.transaction_id1 (+) = t.transaction_id AND
tp.pair_type(+) = 1 AND
ol2.mmt_transaction_id (+) = tp.transaction_id2 AND
t2.transaction_id (+) = ol2.mmt_transaction_id
;
SELECT v.consume_layer_id, v.consume_layer_date, v.line_type, v.requirement_id, v.consume_ib_doc_qty,
v.consume_ib_pri_qty, decode (t.reverse_id, NULL, ol.layer_id, ol2.layer_id) c_layer_id,
decode (t.reverse_id, NULL, v.consume_layer_date, t2.trans_date) c_trans_date,
decode (t.reverse_id, NULL, ol.remaining_ib_doc_qty, ol2.remaining_ib_doc_qty) remaining_ib_doc_qty,
decode (t.reverse_id, NULL, ol.layer_doc_qty, ol2.layer_doc_qty) layer_doc_qty,
decode (t.reverse_id, NULL, 'N', 'Y') c_rev_layer,
decode (t.reverse_id, NULL, ol.ROWID, ol2.ROWID) c_rowid
FROM gmf_batch_vib_details v,
gmf_incoming_material_layers il,
gmf_resource_layers ol,
gme_resource_txns t,
gmf_resource_layers ol2,
gme_resource_txns t2
WHERE
il.mmt_transaction_id = p_tran_rec.reverse_id AND
nvl(il.lot_number, '@@@') = nvl(p_tran_rec.lot_number, '@@@') AND
v.prod_layer_id = il.layer_id AND
v.line_type = 0 AND -- resource only
v.consume_layer_id = ol.layer_id (+) AND
ol.poc_trans_id = t.poc_trans_id (+) AND
t.reverse_id = ol2.poc_trans_id (+) AND
ol2.poc_trans_id = t2.poc_trans_id (+);
SELECT mmt.inventory_item_id, mmt.organization_id, /* mtln.lot_number, */ mmt.primary_quantity, msi.primary_uom_code,
mmt.transaction_date, md.line_type, tp.transaction_id2 as reverse_id, l.ROWID, l.*
FROM gmf_outgoing_material_layers l,
mtl_material_transactions mmt,
mtl_system_items_b msi,
gme_material_details md,
gme_transaction_pairs tp
WHERE
mmt.transaction_source_type_id = 5 AND
mmt.transaction_source_id = p_tran_rec.transaction_source_id AND
mmt.trx_source_line_id = p_ing_material_detail_id AND
l.mmt_transaction_id = mmt.transaction_id AND
l.delete_mark = 0 AND
l.remaining_ib_doc_qty <> 0 AND
msi.inventory_item_id = mmt.inventory_item_id AND
msi.organization_id = mmt.organization_id AND
md.material_detail_id = p_ing_material_detail_id AND
tp.transaction_id1(+) = mmt.transaction_id AND
tp.pair_type(+) = 1
ORDER BY mmt.transaction_date;
SELECT count(*)
FROM gmf_outgoing_material_layers l,
mtl_material_transactions mmt,
mtl_system_items_b msi,
gme_material_details md,
gme_transaction_pairs tp
WHERE
mmt.transaction_source_type_id = 5 AND
mmt.transaction_source_id = p_tran_rec.transaction_source_id AND
mmt.trx_source_line_id = p_ing_material_detail_id AND
l.mmt_transaction_id = mmt.transaction_id AND
l.delete_mark = 0 AND
l.remaining_ib_doc_qty <> 0 AND
msi.inventory_item_id = mmt.inventory_item_id AND
msi.organization_id = mmt.organization_id AND
md.material_detail_id = p_ing_material_detail_id AND
tp.transaction_id1(+) = mmt.transaction_id AND
tp.pair_type(+) = 1
ORDER BY mmt.transaction_date;
SELECT p.resource_usage, p.trans_qty_um as trans_um, p.trans_date, p.line_type, p.reverse_id, p.organization_id, l.ROWID, l.*
FROM gmf_resource_layers l, gme_resource_txns p
WHERE
p.doc_type = 'PROD' AND
p.doc_id = p_tran_rec.transaction_source_id AND
p.line_id = p_batchstep_resource_id AND
p.completed_ind = 1 AND
p.delete_mark = 0 AND
l.poc_trans_id = p.poc_trans_id and
l.delete_mark = 0 and
l.remaining_ib_doc_qty <> 0
ORDER BY p.trans_date;
SELECT count(*)
INTO l_count
FROM gmf_batch_vib_details
WHERE
prod_layer_id = p_layer_rec.layer_id;
SELECT count (1)
INTO l_count
FROM gmf_batch_vib_details vib,
gmf_batch_requirements r
WHERE
r.batch_id = p_tran_rec.transaction_source_id AND
r.prod_material_detail_id = p_tran_rec.trx_source_line_id AND
vib.requirement_id = r.requirement_id AND
vib.consume_layer_id = v.c_layer_id;
-- Insert VIB reversals
INSERT INTO gmf_batch_vib_details(
prod_layer_id,
prod_layer_pri_qty,
consume_layer_id,
consume_layer_date,
line_type,
vib_id,
finalize_ind,
consume_ib_doc_qty,
consume_ib_pri_qty,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
requirement_id)
VALUES(
p_layer_rec.layer_id,
p_tran_rec.primary_quantity,
v.c_layer_id,
v.c_trans_date,
v.line_type,
NULL,
0,
-v.consume_ib_doc_qty,
-v.consume_ib_pri_qty,
p_tran_rec.created_by,
sysdate,
p_tran_rec.last_updated_by,
sysdate,
p_tran_rec.last_update_login,
v.requirement_id);
UPDATE gmf_outgoing_material_layers
SET remaining_ib_doc_qty = remaining_ib_doc_qty + v.consume_ib_doc_qty
WHERE
ROWID = v.c_rowid;
SELECT count (1)
INTO l_count
FROM gmf_batch_vib_details vib,
gmf_batch_requirements r
WHERE
r.batch_id = p_tran_rec.transaction_source_id AND
r.prod_material_detail_id = p_tran_rec.trx_source_line_id AND
vib.requirement_id = r.requirement_id AND
vib.consume_layer_id = v.c_layer_id;
-- Insert VIB reversals
INSERT INTO gmf_batch_vib_details(
prod_layer_id,
prod_layer_pri_qty,
consume_layer_id,
consume_layer_date,
line_type,
vib_id,
finalize_ind,
consume_ib_doc_qty,
consume_ib_pri_qty,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
requirement_id)
VALUES(
p_layer_rec.layer_id,
p_tran_rec.primary_quantity,
v.c_layer_id,
v.c_trans_date,
v.line_type,
NULL,
0,
-v.consume_ib_doc_qty,
-v.consume_ib_pri_qty,
p_tran_rec.created_by,
sysdate,
p_tran_rec.last_updated_by,
sysdate,
p_tran_rec.last_update_login,
v.requirement_id);
UPDATE gmf_resource_layers
SET remaining_ib_doc_qty = remaining_ib_doc_qty + v.consume_ib_doc_qty
WHERE
ROWID = v.c_rowid;
-- Go through the batch requirement rows for this product and insert
-- the VIB details in the vib table.
FOR req IN c_batch_req LOOP
BEGIN
l_required_ib_doc_qty := p_layer_rec.layer_doc_qty * req.required_doc_qty;
-- select ingredient layers that can be consumed for the
-- IB qty
IF g_debug <= gme_debug.g_log_statement THEN
gme_debug.put_line ('processing ingredient or by-product to create vib details');
SELECT nvl(sum (consume_ib_doc_qty), 0)
INTO l_prev_consume_ib_doc_qty
FROM gmf_batch_vib_details v,
gmf_batch_requirements r
WHERE
r.batch_id = p_tran_rec.transaction_source_id AND
r.prod_material_detail_id = p_tran_rec.trx_source_line_id AND
v.requirement_id = r.requirement_id AND
v.consume_layer_id = ing.layer_id;
SELECT nvl(sum (consume_ib_doc_qty), 0)
INTO l_orig_layer_consumption_qty
FROM gmf_outgoing_material_layers ol,
gmf_batch_vib_details v,
gmf_batch_requirements r
WHERE
ol.mmt_transaction_id = ing.reverse_id AND
v.consume_layer_id = ol.layer_id AND
r.batch_id = p_tran_rec.transaction_source_id AND
r.prod_material_detail_id = p_tran_rec.trx_source_line_id AND
v.requirement_id = r.requirement_id ;
INSERT INTO gmf_batch_vib_details(
prod_layer_id,
prod_layer_pri_qty,
consume_layer_id,
consume_layer_date,
line_type,
vib_id,
finalize_ind,
consume_ib_doc_qty,
consume_ib_pri_qty,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
requirement_id)
VALUES(
p_layer_rec.layer_id,
p_tran_rec.primary_quantity,
ing.layer_id,
ing.transaction_date,
ing.line_type,
NULL,
0,
l_consume_ib_doc_qty,
l_consume_ib_pri_qty,
p_tran_rec.created_by,
sysdate,
p_tran_rec.last_updated_by,
sysdate,
p_tran_rec.last_update_login,
req.requirement_id);
UPDATE gmf_outgoing_material_layers
SET remaining_ib_doc_qty = l_remaining_ib_doc_qty
WHERE
ROWID = ing.ROWID;
-- select ingredient layers that can be consumed for the
-- IB qty
IF g_debug <= gme_debug.g_log_statement THEN
gme_debug.put_line ('processing ingredient or by-product to create vib details');
-- insert a row in the VIB detail table
IF l_remaining_ib_doc_qty = 0 THEN
RAISE e_rsrc_invalid_consumption;
SELECT nvl(sum (consume_ib_doc_qty), 0)
INTO l_prev_consume_ib_doc_qty
FROM gmf_batch_vib_details v,
gmf_batch_requirements r
WHERE
r.batch_id = p_tran_rec.transaction_source_id AND
r.prod_material_detail_id = p_tran_rec.trx_source_line_id AND
v.requirement_id = r.requirement_id AND
v.consume_layer_id = rsrc.layer_id;
SELECT nvl(sum (consume_ib_doc_qty), 0)
INTO l_orig_layer_consumption_qty
FROM gmf_resource_layers rl,
gmf_batch_vib_details v,
gmf_batch_requirements r
WHERE
rl.poc_trans_id = rsrc.reverse_id AND
v.consume_layer_id = rl.layer_id AND
r.batch_id = p_tran_rec.transaction_source_id AND
r.prod_material_detail_id = p_tran_rec.trx_source_line_id AND
v.requirement_id = r.requirement_id ;
INSERT INTO gmf_batch_vib_details(
prod_layer_id,
prod_layer_pri_qty,
consume_layer_id,
consume_layer_date,
line_type,
vib_id,
finalize_ind,
consume_ib_doc_qty,
consume_ib_pri_qty,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
requirement_id)
VALUES(
p_layer_rec.layer_id,
p_tran_rec.primary_quantity,
rsrc.layer_id,
rsrc.trans_date,
rsrc.line_type,
NULL,
0,
l_consume_ib_doc_qty,
l_consume_ib_pri_qty,
p_tran_rec.created_by,
sysdate,
p_tran_rec.last_updated_by,
sysdate,
p_tran_rec.last_update_login,
req.requirement_id );
UPDATE gmf_resource_layers
SET remaining_ib_doc_qty = l_remaining_ib_doc_qty
WHERE
ROWID = rsrc.ROWID;
-- There is still some IB quantity not consumed, insert a NULL layer consumption.
IF l_use_vib = 'Y' and l_required_ib_doc_qty <> 0 THEN
IF g_debug <= gme_debug.g_log_statement THEN
gme_debug.put_line ('inserting NULL consumption layer for matl_dtl_id: ' || req.ing_material_detail_id);
SELECT m.dtl_um, m.line_type, i.primary_uom_code
INTO l_doc_um, l_line_type, l_item_um
FROM gme_material_details m, mtl_system_items_b i
WHERE
m.batch_id = req.batch_id AND
m.material_detail_id = req.ing_material_detail_id AND
i.inventory_item_id = m.inventory_item_id AND
i.organization_id = m.organization_id;
SELECT m.usage_um, 0, r.std_usage_uom -- Bug 8472152 changed from usage_uom, std_usage_uom
INTO l_doc_um, l_line_type, l_item_um
FROM gme_batch_step_resources m, cr_rsrc_mst_b r
WHERE
m.batch_id = req.batch_id AND
m.batchstep_resource_id = req.batchstep_resource_id AND
m.resources = r.resources;
INSERT INTO gmf_batch_vib_details(
prod_layer_id,
prod_layer_pri_qty,
consume_layer_id,
consume_layer_date,
line_type,
vib_id,
finalize_ind,
consume_ib_doc_qty,
consume_ib_pri_qty,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
requirement_id)
VALUES(
p_layer_rec.layer_id,
p_tran_rec.primary_quantity,
NULL,
p_tran_rec.transaction_date,
l_line_type,
NULL,
0,
l_required_ib_doc_qty,
l_consume_ib_pri_qty,
p_tran_rec.created_by,
sysdate,
p_tran_rec.last_updated_by,
sysdate,
p_tran_rec.last_update_login,
req.requirement_id);
SELECT *
FROM gmf_batch_requirements
WHERE
batch_id = p_batch_id AND
delete_mark = 0
ORDER BY prod_material_detail_id;
SELECT v.*, l.layer_doc_qty, l.layer_doc_um, l.mmt_transaction_id, l.lot_number, l.mmt_organization_id
FROM gmf_batch_vib_details v,
gmf_batch_requirements r,
gmf_incoming_material_layers l
WHERE
r.batch_id = p_batch_id AND
v.requirement_id = r.requirement_id AND
l.layer_id = v.prod_layer_id AND
v.finalize_ind = 0 AND
v.consume_layer_id IS NULL
ORDER BY v.prod_layer_id
;
SELECT l.*, t.primary_quantity,
t.inventory_item_id -- Bug 5607069
FROM gmf_incoming_material_layers l,
mtl_material_transactions t
WHERE
t.trx_source_line_id = p_prod_material_detail_id AND
t.transaction_source_id = p_batch_id AND
t.transaction_source_type_id = 5 AND
l.mmt_transaction_id = t.transaction_id AND
l.pseudo_layer_id IS NULL AND
not exists (select 'x' from gme_transaction_pairs tp
where transaction_id1 = t.transaction_id and tp.pair_type = 1)
ORDER BY l.creation_date DESC;
SELECT l.layer_id, l.layer_doc_um, l.remaining_ib_doc_qty, t.inventory_item_id, t.transaction_date,
NULL lot_number, md.line_type, t.primary_quantity, msi.primary_uom_code, l.ROWID, tp.transaction_id2 as reverse_id,
l.layer_doc_qty
FROM gmf_outgoing_material_layers l, mtl_material_transactions t,
gme_material_details md,
mtl_system_items_b msi, gme_transaction_pairs tp
WHERE
t.trx_source_line_id = p_ing_material_detail_id AND
t.transaction_source_id = p_batch_id AND
t.transaction_source_type_id = 5 AND
l.mmt_transaction_id = t.transaction_id AND
l.remaining_ib_doc_qty <> 0 AND
l.delete_mark = 0 AND
md.material_detail_id = p_ing_material_detail_id AND
msi.inventory_item_id = t.inventory_item_id AND
msi.organization_id = t.organization_id AND
tp.transaction_id1(+) = t.transaction_id
;
SELECT l.layer_id, l.layer_doc_um, l.remaining_ib_doc_qty, t.line_type, t.trans_date,
t.resource_usage, t.trans_qty_um trans_um, l.ROWID, t.reverse_id, l.layer_doc_qty
FROM gmf_resource_layers l,
gme_resource_txns t
WHERE
t.doc_type = 'PROD' AND
t.doc_id = p_batch_id AND
l.poc_trans_id = t.poc_trans_id AND
t.line_id = p_batchstep_resource_id AND
l.remaining_ib_doc_qty <> 0 and
l.delete_mark = 0;
SELECT v.consume_layer_id, v.line_type, sum(v.consume_ib_doc_qty) consume_ib_doc_qty
FROM
gmf_batch_vib_details v,
gmf_batch_requirements r
WHERE
r.batch_id = p_batch_id AND
v.requirement_id = r.requirement_id AND
v.finalize_ind = 1 AND
v.consume_layer_id IS NOT NULL
GROUP BY v.consume_layer_id, v.line_type;
SELECT batch_status, last_updated_by, batch_close_date
INTO l_batch_status, l_user_id, l_batch_close_date
FROM gme_batch_header
WHERE batch_id = p_batch_id;
SELECT count(*)
INTO l_count
FROM gmf_batch_vib_details v,
gmf_batch_requirements r
WHERE
r.batch_id = p_batch_id AND
r.requirement_id = v.requirement_id and
v.finalize_ind = 1;
SELECT gmf_layer_id_s.nextval INTO l_pseudo_prod_layer_id FROM DUAL;
INSERT INTO gmf_incoming_material_layers(
layer_id,
mmt_transaction_id,
mmt_organization_id,
lot_number,
layer_doc_qty,
layer_doc_um,
layer_date,
pseudo_layer_id,
final_cost_ind,
gl_posted_ind,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
accounted_flag)
VALUES(
l_pseudo_prod_layer_id,
n.mmt_transaction_id,
n.mmt_organization_id,
n.lot_number,
n.layer_doc_qty,
n.layer_doc_um,
l_batch_close_date,
n.prod_layer_id,
0,
0,
l_user_id,
sysdate,
l_user_id,
sysdate,
NULL,
'N');
INSERT INTO gmf_batch_vib_details(
prod_layer_id,
prod_layer_pri_qty,
consume_layer_id,
consume_layer_date,
line_type,
vib_id,
finalize_ind,
consume_ib_doc_qty,
consume_ib_pri_qty,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
requirement_id)
VALUES(
l_pseudo_prod_layer_id,
n.prod_layer_pri_qty,
NULL,
n.consume_layer_date,
n.line_type,
NULL,
1,
-n.consume_ib_doc_qty,
-n.consume_ib_pri_qty,
l_user_id,
sysdate,
l_user_id,
sysdate,
NULL,
n.requirement_id);
SELECT nvl(sum (consume_ib_doc_qty), 0)
INTO l_prev_consume_ib_doc_qty
FROM gmf_batch_vib_details v,
gmf_incoming_material_layers il,
mtl_material_transactions mmt
WHERE
v.requirement_id = req.requirement_id AND
v.consume_layer_id = ing.layer_id AND
il.layer_id = v.prod_layer_id AND
mmt.transaction_id = il.mmt_transaction_id AND
mmt.transaction_source_type_id = 5 AND
mmt.inventory_item_id = req.product_item_id AND
mmt.organization_id = req.organization_id
;
SELECT nvl(sum (consume_ib_doc_qty), 0)
INTO l_orig_layer_consumption_qty
FROM gmf_outgoing_material_layers ol,
gmf_batch_vib_details v,
gmf_incoming_material_layers il,
mtl_material_transactions mmt
WHERE
ol.mmt_transaction_id = ing.reverse_id AND
ol.lot_number = ing.lot_number AND
v.consume_layer_id = ol.layer_id AND
v.requirement_id = req.requirement_id AND
il.layer_id = v.prod_layer_id AND
mmt.transaction_id = il.mmt_transaction_id AND
mmt.transaction_source_type_id = 5 AND
mmt.inventory_item_id = req.product_item_id AND
mmt.organization_id = req.organization_id
;
SELECT gmf_layer_id_s.nextval INTO l_pseudo_prod_layer_id FROM DUAL;
INSERT INTO gmf_incoming_material_layers(
layer_id,
mmt_transaction_id,
mmt_organization_id,
lot_number,
layer_doc_qty,
layer_doc_um,
layer_date,
pseudo_layer_id,
final_cost_ind,
gl_posted_ind,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
accounted_flag)
VALUES(
l_pseudo_prod_layer_id,
l_last_prod_layer.mmt_transaction_id,
l_last_prod_layer.mmt_organization_id,
l_last_prod_layer.lot_number,
l_last_prod_layer.layer_doc_qty,
l_last_prod_layer.layer_doc_um,
l_batch_close_date,
l_last_prod_layer.layer_id,
0,
0,
l_user_id,
sysdate,
l_user_id,
sysdate,
NULL,
'N');
INSERT INTO gmf_batch_vib_details(
prod_layer_id,
prod_layer_pri_qty,
consume_layer_id,
consume_layer_date,
line_type,
vib_id,
finalize_ind,
consume_ib_doc_qty,
consume_ib_pri_qty,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
requirement_id)
VALUES(
l_pseudo_prod_layer_id,
l_last_prod_layer.primary_quantity,
ing.layer_id,
ing.transaction_date,
ing.line_type,
NULL,
1,
l_consume_ib_doc_qty,
l_consume_ib_pri_qty,
l_user_id,
sysdate,
l_user_id,
sysdate,
NULL,
req.requirement_id);
SELECT nvl(sum (consume_ib_doc_qty), 0)
INTO l_prev_consume_ib_doc_qty
FROM gmf_batch_vib_details v,
gmf_incoming_material_layers il,
mtl_material_transactions mmt
WHERE
v.requirement_id = req.requirement_id AND
v.consume_layer_id = rsrc.layer_id AND
il.layer_id = v.prod_layer_id AND
mmt.transaction_id = il.mmt_transaction_id AND
mmt.inventory_item_id = req.product_item_id AND
mmt.organization_id = req.organization_id AND
mmt.transaction_source_type_id = 5
;
SELECT nvl(sum (consume_ib_doc_qty), 0)
INTO l_orig_layer_consumption_qty
FROM gmf_resource_layers rl,
gmf_batch_vib_details v,
gmf_incoming_material_layers il,
mtl_material_transactions mmt
WHERE
rl.poc_trans_id = rsrc.reverse_id AND
v.consume_layer_id = rl.layer_id AND
v.requirement_id = req.requirement_id AND
il.layer_id = v.prod_layer_id AND
mmt.transaction_id = il.mmt_transaction_id AND
mmt.inventory_item_id = req.product_item_id AND
mmt.organization_id = req.organization_id AND
mmt.transaction_source_type_id = 5
;
SELECT gmf_layer_id_s.nextval INTO l_pseudo_prod_layer_id FROM DUAL;
INSERT INTO gmf_incoming_material_layers(
layer_id,
mmt_transaction_id,
mmt_organization_id, -- B6887598
lot_number,
layer_doc_qty,
layer_doc_um,
layer_date,
pseudo_layer_id,
final_cost_ind,
gl_posted_ind,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
accounted_flag)
VALUES(
l_pseudo_prod_layer_id,
l_last_prod_layer.mmt_transaction_id,
l_last_prod_layer.mmt_organization_id, -- B6887598
l_last_prod_layer.lot_number,
l_last_prod_layer.layer_doc_qty,
l_last_prod_layer.layer_doc_um,
l_batch_close_date,
l_last_prod_layer.layer_id,
0,
0,
l_user_id,
sysdate,
l_user_id,
sysdate,
NULL,
'N');
INSERT INTO gmf_batch_vib_details(
prod_layer_id,
prod_layer_pri_qty,
consume_layer_id,
consume_layer_date,
line_type,
vib_id,
finalize_ind,
consume_ib_doc_qty,
consume_ib_pri_qty,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login,
requirement_id)
VALUES(
l_pseudo_prod_layer_id,
l_last_prod_layer.primary_quantity,
rsrc.layer_id,
rsrc.trans_date,
rsrc.line_type,
NULL,
1,
l_consume_ib_doc_qty,
l_consume_ib_pri_qty,
l_user_id,
sysdate,
l_user_id,
sysdate,
NULL,
req.requirement_id);
-- Now update the remaining_ib_doc_qty based upon the finalized layers.
IF g_debug <= gme_debug.g_log_statement THEN
gme_debug.put_line ('Now update the remaining_ib_doc_qty based upon the finalized layers');
UPDATE gmf_resource_layers
SET remaining_ib_doc_qty = remaining_ib_doc_qty - c.consume_ib_doc_qty
WHERE
layer_id = c.consume_layer_id;
UPDATE gmf_outgoing_material_layers
SET remaining_ib_doc_qty = remaining_ib_doc_qty - c.consume_ib_doc_qty
WHERE
layer_id = c.consume_layer_id;
SELECT v.ROWID, v.consume_ib_doc_qty, v.consume_layer_id, v.line_type
FROM gmf_batch_vib_details v, gmf_batch_requirements r
WHERE
r.batch_id = p_batch_id and
r.requirement_id = v.requirement_id and
v.finalize_ind = 1 and
v.consume_layer_id IS NOT NULL;
SELECT count(*) FROM gmf_xla_extract_headers
WHERE entity_code = 'PRODUCTION'
AND event_class_code = 'BATCH_CLOSE'
AND transaction_id = p_batch_id
AND accounted_flag IS NULL;
SELECT event_id, ledger_id FROM gmf_xla_extract_headers
WHERE entity_code = 'PRODUCTION'
AND event_class_code = 'BATCH_CLOSE'
AND transaction_id = p_batch_id;
SELECT batch_status
INTO l_batch_status
FROM gme_batch_header
WHERE batch_id = p_batch_id;
INSERT INTO xla_events_int_gt
(entity_id
,application_id
,ledger_id
,entity_code
,event_status_code
,event_id
)
SELECT
xe.entity_id
,xe.application_id
,l_event.ledger_id
,'PRODUCTION'
,xe.event_status_code
,xe.event_id
FROM xla_events xe
WHERE xe.application_id = 555
AND xe.event_id = l_event.event_id
AND xe.event_type_code = 'CLOS';
DELETE FROM gmf_xla_extract_lines
WHERE Header_id = (SELECT header_id FROM gmf_xla_extract_headers
WHERE entity_code = 'PRODUCTION'
AND event_class_code = 'BATCH_CLOSE'
AND transaction_id = p_batch_id
AND event_id = l_event.event_id);
DELETE FROM gmf_xla_extract_headers
WHERE entity_code = 'PRODUCTION'
AND event_class_code = 'BATCH_CLOSE'
AND transaction_id = p_batch_id
AND event_id = l_event.event_id;
xla_events_pkg.delete_bulk_events( p_application_id => 555);
delete from xla_events_int_gt ;
-- Delete all rows from gmf_incoming_material_layers which are used in
-- gmf_batch_vib_details with finalize_ind = 1
IF g_debug <= gme_debug.g_log_statement THEN
gme_debug.put_line ('deleting pseudo layers...');
DELETE from gmf_incoming_material_layers
WHERE
pseudo_layer_id IS NOT NULL AND
layer_id in (
SELECT prod_layer_id
FROM gmf_batch_vib_details v,
gmf_batch_requirements r
WHERE
r.batch_id = p_batch_id AND
r.requirement_id = v.requirement_id AND
v.finalize_ind = 1);
gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
-- Delete all rows from the gmf_batch_vib_details with finalize_ind = 1
-- and the comsume_layer_id is NULL.
IF g_debug <= gme_debug.g_log_statement THEN
gme_debug.put_line ('now deleting NULL finalized consumption layers');
DELETE from gmf_batch_vib_details
WHERE
finalize_ind = 1 and
consume_layer_id IS NULL and
requirement_id in (
SELECT requirement_id
FROM gmf_batch_requirements
WHERE
Batch_id = p_batch_id);
gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
-- Update the layers material, resource remaining_doc_qty
IF f.line_type = 0 THEN
UPDATE gmf_resource_layers
SET remaining_ib_doc_qty = remaining_ib_doc_qty + f.consume_ib_doc_qty
WHERE
layer_id = f.consume_layer_id;
UPDATE gmf_outgoing_material_layers
SET remaining_ib_doc_qty = remaining_ib_doc_qty + f.consume_ib_doc_qty
WHERE
layer_id = f.consume_layer_id;
-- Delete the row from the gmf_batch_vib_detail table.
DELETE from gmf_batch_vib_details
WHERE
ROWID = f.ROWID;
SELECT layer_id, trans_id, layer_doc_qty, layer_doc_um, layer_date, pseudo_layer_id,
final_cost_ind, gl_posted_ind
FROM (
SELECT
il.layer_id, il.trans_id, il.layer_doc_qty, il.layer_doc_um, il.layer_date, il.pseudo_layer_id,
il.final_cost_ind, il.gl_posted_ind,
hdr.batch_id,
RANK() OVER(partition by hdr.batch_id ORDER BY hdr.batch_id, il.layer_date desc, il.layer_id desc) layer_rank
FROM
gme_batch_header hdr,
sy_orgn_mst orgn,
gmf_incoming_material_layers il,
ic_tran_pnd pnd
WHERE
il.layer_date >= p_start_date
AND il.layer_date <= p_end_date
AND il.trans_id IS NOT NULL
AND pnd.trans_id = il.trans_id
AND orgn.co_code = p_co_code
AND hdr.plant_code = orgn.orgn_code
AND hdr.batch_id = pnd.doc_id
AND hdr.batch_status <> 4
) a
WHERE a.layer_rank = 1
and a.layer_id = 9
ORDER BY batch_id, layer_date desc
;
SELECT hdr.co_code, dtl.start_date, dtl.end_date
INTO l_co_code, l_start_date, l_end_date
FROM cm_acpr_ctl acpr, cm_cldr_dtl dtl, cm_cldr_hdr hdr
WHERE acpr.acproc_id = p_ac_proc_id
AND hdr.calendar_code = acpr.calendar_code
AND hdr.cost_mthd_code = acpr.cost_mthd_code
AND hdr.calendar_code = dtl.calendar_code
AND dtl.period_code = acpr.period_code
;
SELECT pnd.*
INTO l_trans_rec
FROM mtl_material_transactions pnd
WHERE transaction_id = l_layer_rec.trans_id
;