[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
-- Insert the data into the layers table
IF (p_tran_rec.transaction_source_type_id <> 5) THEN
-- Bug 5586577. Return with Success.
-- x_return_status := FND_API.G_RET_STS_ERROR ;
SELECT -l.layer_doc_qty, l.layer_doc_um
INTO l_doc_qty, l_doc_um
FROM gmf_incoming_material_layers l, mtl_material_transactions mmt,
mtl_transaction_lot_numbers mtln
WHERE
mmt.transaction_id = p_tran_rec.reverse_id AND
mtln.transaction_id (+) = p_tran_rec.reverse_id AND
mtln.lot_number (+) = p_tran_rec.lot_number AND
l.mmt_transaction_id = mmt.transaction_id AND
l.lot_number(+) = mtln.lot_number ;
SELECT gmf_layer_id_s.nextval INTO l_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_layer_id,
p_tran_rec.transaction_id,
p_tran_rec.organization_id,
p_tran_rec.lot_number,
l_doc_qty,
l_doc_um,
p_tran_rec.transaction_date,
NULL,
0,
0,
p_tran_rec.created_by,
sysdate,
p_tran_rec.last_updated_by,
sysdate,
p_tran_rec.last_update_login,
'N');
SELECT count(*)
INTO l_req_count
FROM gmf_batch_requirements
WHERE batch_id = p_tran_rec.transaction_source_id
AND delete_mark = 0;
SELECT v.rowid, v.*, mmt.organization_id, l.layer_date
FROM gmf_batch_vib_details v,
gmf_batch_requirements r,
gmf_incoming_material_layers l,
mtl_material_transactions mmt,
mtl_transaction_lot_numbers mtln
WHERE
r.batch_id = p_tran_rec.transaction_source_id AND
r.ing_material_detail_id = p_tran_rec.trx_source_line_id AND
r.delete_mark = 0 AND
v.requirement_id = r.requirement_id AND
v.consume_layer_id is NULL AND
v.finalize_ind = 0 AND
l.layer_id = v.prod_layer_id AND
l.final_cost_ind = 0 AND
l.mmt_transaction_id = mmt.transaction_id AND
mtln.transaction_id(+) = l.mmt_transaction_id AND
mtln.lot_number(+) = l.lot_number AND
-- mmt.source_line_id = -99 AND
mmt.opm_costed_flag IS NOT NULL AND
not exists (select 'x' from gme_transaction_pairs tp
where transaction_id1 = mmt.transaction_id and tp.pair_type = 1)
ORDER by v.prod_layer_id DESC;
l_delete_mark NUMBER;
-- Insert the data into the layers table
IF (p_tran_rec.transaction_source_type_id <> 5) THEN
-- Bug 5586577. Return with Success.
-- x_return_status := FND_API.G_RET_STS_ERROR ;
SELECT -l.layer_doc_qty, l.layer_doc_um
INTO l_doc_qty, l_doc_um
FROM gmf_outgoing_material_layers l, mtl_material_transactions mmt,
mtl_transaction_lot_numbers mtln
WHERE
mmt.transaction_id = p_tran_rec.reverse_id AND
mtln.transaction_id (+) = p_tran_rec.reverse_id AND
mtln.lot_number (+) = p_tran_rec.lot_number AND
l.mmt_transaction_id = mmt.transaction_id AND
l.lot_number(+) = mtln.lot_number ;
SELECT gmf_layer_id_s.nextval INTO l_layer_id FROM DUAL;
l_delete_mark := 0;
SELECT -sum(nvl(consume_ib_doc_qty,0)), l.ROWID
INTO l_rev_consume_ib_doc_qty, l_rowid
FROM gmf_outgoing_material_layers l,
gmf_batch_vib_details v
WHERE l.mmt_transaction_id = p_tran_rec.reverse_id and
l.lot_number = p_tran_rec.lot_number and
l.layer_id = v.consume_layer_id (+)
GROUP BY l.ROWID;
l_delete_mark := 1;
UPDATE gmf_outgoing_material_layers
SET remaining_ib_doc_qty = 0,
delete_mark = l_delete_mark
WHERE
ROWID = l_rowid;
-- Update them to consume from this layer. This is done only if the product
-- Layer is not posted to subledgerand the cost is not frozen.
IF g_debug <= gme_debug.g_log_statement THEN
gme_debug.put_line ('getting null consumption layers from VIB details, if any');
SELECT COUNT(*)
INTO l_period_count
FROM gmf_organization_definitions god,
gmf_period_statuses gps,
gmf_fiscal_policies gfp,
cm_mthd_mst mthd
WHERE god.organization_id = n.organization_id
AND gfp.legal_entity_id = god.legal_entity_id
AND mthd.cost_type_id = gfp.cost_type_id
AND mthd.cost_type = 1 -- Actual costing method
AND mthd.prodcalc_type = 1 -- PMAC product cost
AND gps.legal_entity_id = gfp.legal_entity_id
AND gps.cost_type_id = gfp.cost_type_id
AND n.layer_date BETWEEN gps.start_date AND gps.end_date
AND p_tran_rec.transaction_date BETWEEN gps.start_date AND gps.end_date;
UPDATE gmf_batch_vib_details
SET consume_layer_id = l_layer_id,
consume_layer_date = p_tran_rec.transaction_date
WHERE ROWID = n.rowid;
INSERT INTO gmf_batch_vib_details(
prod_layer_id,
prod_layer_pri_qty,
consume_layer_id,
consume_layer_date,
line_type,
requirement_id,
finalize_ind,
consume_ib_doc_qty,
consume_ib_pri_qty,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES(
n.prod_layer_id,
n.prod_layer_pri_qty,
NULL,
n.consume_layer_date,
p_tran_rec.line_type, -- ???? inserting NULL
n.requirement_id,
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);
UPDATE gmf_batch_vib_details
SET consume_layer_id = l_layer_id,
consume_ib_doc_qty = l_remaining_ib_doc_qty,
consume_ib_pri_qty = l_consume_ib_pri_qty,
consume_layer_date = p_tran_rec.transaction_date
WHERE ROWID = n.rowid;
INSERT INTO gmf_outgoing_material_layers(
layer_id,
mmt_transaction_id,
mmt_organization_id,
lot_number,
layer_doc_qty,
layer_doc_um,
remaining_ib_doc_qty,
delete_mark,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES(
l_layer_id,
p_tran_rec.transaction_id,
p_tran_rec.organization_id,
p_tran_rec.lot_number,
l_doc_qty,
l_doc_um,
l_remaining_ib_doc_qty,
l_delete_mark,
p_tran_rec.created_by,
sysdate,
p_tran_rec.last_updated_by,
sysdate,
p_tran_rec.last_update_login);
gme_debug.put_line (sql%ROWCOUNT || ' rows inserted');
SELECT v.rowid, v.*, t.organization_id, l.layer_date
FROM gmf_batch_vib_details v,
gmf_batch_requirements r,
gmf_incoming_material_layers l,
mtl_material_transactions t
WHERE
r.batch_id = p_rsrc_rec.doc_id AND
r.batchstep_resource_id = p_rsrc_rec.line_id AND
r.delete_mark = 0 AND
v.requirement_id = r.requirement_id AND
v.consume_layer_id is NULL AND
l.layer_id = v.prod_layer_id AND
l.final_cost_ind = 0 AND
l.mmt_transaction_id = t.transaction_id AND
t.source_line_id = -99 AND
t.opm_costed_flag IS NOT NULL
-- ORDER by v.consume_layer_id;
l_delete_mark NUMBER;
-- Insert the data into the layers table
IF (p_rsrc_rec.doc_type <> 'PROD') THEN
-- Bug 5586577. Return with Success.
-- x_return_status := FND_API.G_RET_STS_ERROR ;
SELECT gmf_layer_id_s.nextval INTO l_layer_id FROM DUAL;
l_delete_mark := 0;
SELECT -sum(consume_ib_doc_qty), l.ROWID
INTO l_rev_consume_ib_doc_qty, l_rowid
FROM gmf_resource_layers l,
gmf_batch_vib_details v
WHERE l.poc_trans_id = p_rsrc_rec.reverse_id and
l.layer_id = v.consume_layer_id
GROUP BY l.ROWID;
l_delete_mark := 1;
UPDATE gmf_resource_layers
SET remaining_ib_doc_qty = 0,
delete_mark = l_delete_mark
WHERE
ROWID = l_rowid;
-- Update them to consume from this layer.
FOR n IN c_null_vib_rows LOOP
BEGIN
IF l_remaining_ib_doc_qty = 0 THEN
RAISE e_invalid_consumption;
SELECT COUNT(*)
INTO l_period_count
FROM gmf_organization_definitions god,
gmf_period_statuses gps,
gmf_fiscal_policies gfp,
cm_mthd_mst mthd
WHERE god.organization_id = n.organization_id
AND gfp.legal_entity_id = god.legal_entity_id
AND mthd.cost_type_id = gfp.cost_type_id
AND mthd.cost_type = 1 -- Actual costing method
AND mthd.prodcalc_type = 1 -- PMAC product cost
AND gps.legal_entity_id = gfp.legal_entity_id
AND gps.cost_type_id = gfp.cost_type_id
AND n.layer_date BETWEEN gps.start_date AND gps.end_date
AND p_rsrc_rec.trans_date BETWEEN gps.start_date AND gps.end_date;
UPDATE gmf_batch_vib_details
SET consume_layer_id = l_layer_id
WHERE ROWID = n.rowid;
INSERT INTO gmf_batch_vib_details(
prod_layer_id,
prod_layer_pri_qty,
consume_layer_id,
consume_layer_date,
line_type,
requirement_id,
finalize_ind,
consume_ib_doc_qty,
consume_ib_pri_qty,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES(
n.prod_layer_id,
n.prod_layer_pri_qty,
NULL,
n.consume_layer_date,
p_rsrc_rec.line_type, -- ???? inserting NULL
n.requirement_id,
0,
l_consume_ib_doc_qty,
l_consume_ib_pri_qty,
p_rsrc_rec.created_by,
sysdate,
p_rsrc_rec.last_updated_by,
sysdate,
p_rsrc_rec.last_update_login);
UPDATE gmf_batch_vib_details
SET consume_layer_id = l_layer_id,
consume_ib_pri_qty = l_consume_ib_pri_qty,
consume_ib_doc_qty = l_remaining_ib_doc_qty
WHERE ROWID = n.rowid;
INSERT INTO gmf_resource_layers(
layer_id,
poc_trans_id,
layer_doc_qty,
layer_doc_um,
remaining_ib_doc_qty,
delete_mark,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login)
VALUES(
l_layer_id,
p_rsrc_rec.poc_trans_id,
p_doc_qty,
p_doc_um,
l_remaining_ib_doc_qty,
l_delete_mark,
p_rsrc_rec.created_by,
sysdate,
p_rsrc_rec.last_updated_by,
sysdate,
p_rsrc_rec.last_update_login);