[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
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
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
nvl(l.lot_number,'X')= nvl(p_tran_rec.lot_number,'X') 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 /*+ LEADING(r) */ v.rowid, v.*, t.organization_id, l.layer_date
FROM gmf_batch_requirements r,
gmf_batch_vib_details v,
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 commented out for Bug 8472152
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(nvl(consume_ib_doc_qty,0)), 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);
INSERT INTO gme_temp_exceptions (
table_name,
procedure_name,
parameters,
message,
error_type,
script_date
) VALUES (
p_table_name,
p_procedure_name,
p_parameters,
TO_CHAR (SYSDATE, g_date_format) || ': ' || p_message,
p_error_type,
SYSDATE);
PROCEDURE Delete_old_layers (p_batch_id IN NUMBER) IS
err_num NUMBER;
DELETE
FROM gmf_layer_cost_details c
WHERE
c.layer_id IN
(SELECT il.layer_id
FROM gme_batch_header h, mtl_material_transactions t, gmf_incoming_material_layers il
WHERE h.batch_id = p_batch_id
AND h.batch_id = t.transaction_source_id
AND t.transaction_source_type_id = 5
AND il.mmt_transaction_id = t.transaction_id
AND il.mmt_organization_id = t.organization_id
);
DELETE
FROM gmf_incoming_material_layers il
WHERE il.PSEUDO_LAYER_ID IS NOT NULL
AND EXISTS
(SELECT 1
FROM gme_batch_header h, mtl_material_transactions t,
gmf_incoming_material_layers im
WHERE h.batch_id = p_batch_id
AND h.batch_id = t.transaction_source_id
AND t.transaction_source_type_id = 5
AND im.mmt_transaction_id = t.transaction_id
AND im.mmt_organization_id = t.organization_id
AND im.layer_id = il.PSEUDO_LAYER_ID
);
DELETE
FROM gmf_incoming_material_layers il
WHERE (il.mmt_organization_id, il.mmt_transaction_id) IN
(SELECT distinct t.organization_id, t.transaction_id
FROM gme_batch_header h, mtl_material_transactions t
WHERE h.batch_id = p_batch_id
AND h.batch_id = t.transaction_source_id
AND t.transaction_source_type_id = 5
);
DELETE
FROM gmf_outgoing_material_layers ol
WHERE (ol.mmt_organization_id, ol.mmt_transaction_id) IN
(SELECT distinct t.organization_id, t.transaction_id
FROM gme_batch_header h, mtl_material_transactions t
WHERE h.batch_id = p_batch_id
AND h.batch_id = t.transaction_source_id
AND t.transaction_source_type_id = 5
);
DELETE
FROM gmf_resource_layers il
WHERE il.poc_trans_id IN
(SELECT t.poc_trans_id
FROM gme_batch_header h, gme_resource_txns t
WHERE h.batch_id = p_batch_id
AND h.batch_id = t.doc_id
AND t.doc_type = 'PROD'
);
DELETE
FROM gmf_batch_vib_details bvd
WHERE bvd.requirement_id IN
(SELECT br.requirement_id
FROM gmf_batch_requirements br, gme_batch_header h
WHERE h.batch_id = p_batch_id
AND h.batch_id = br.batch_id
);
DELETE
FROM gmf_batch_requirements br
WHERE br.batch_id IN
(SELECT batch_id
FROM gme_batch_header
WHERE batch_id = p_batch_id
) ;
p_procedure_name => 'Delete_old_layers',
p_parameters => err_num,
p_message => 'Error deleting Old layer data for batch_id = '||p_batch_id||' '||err_msg,
p_error_type => 'I');
END Delete_old_layers;
PROCEDURE Delete_period_layers (p_batch_id IN NUMBER, p_period_id IN NUMBER) IS
err_num NUMBER;
select count(*)
FROM gme_batch_header h, mtl_material_transactions t, gmf_incoming_material_layers il
WHERE h.batch_id = p_batch_id
AND h.batch_id = t.transaction_source_id
AND t.transaction_source_type_id = 5
AND il.mmt_transaction_id = t.transaction_id
AND il.mmt_organization_id = t.organization_id;
SELECT CONSUME_LAYER_ID, CONSUME_IB_DOC_QTY, CONSUME_IB_PRI_QTY, LINE_TYPE
FROM gmf_batch_vib_details bvd
WHERE bvd.CONSUME_LAYER_DATE < l_start_date
AND bvd.prod_layer_id IN
(SELECT il.layer_id
FROM gme_batch_header h, mtl_material_transactions t, gmf_incoming_material_layers il
WHERE h.batch_id = p_batch_id
AND h.batch_id = t.transaction_source_id
AND t.transaction_source_type_id = 5
AND il.mmt_transaction_id = t.transaction_id
AND il.mmt_organization_id = t.organization_id
AND il.layer_date >= l_start_date
);
select start_date, end_date INTO l_start_date, l_end_date
from gmf_period_statuses
where period_id = p_period_id;
DELETE
FROM gmf_layer_cost_details c
WHERE c.layer_id IN
(SELECT il.layer_id
FROM gme_batch_header h, mtl_material_transactions t, gmf_incoming_material_layers il
WHERE h.batch_id = p_batch_id
AND h.batch_id = t.transaction_source_id
AND t.transaction_source_type_id = 5
AND il.mmt_transaction_id = t.transaction_id
AND il.mmt_organization_id = t.organization_id
AND t.transaction_date >= l_start_date
);
Select all VIB details to be deleted. Vib details has these columns of interest.
PROD_LAYER_ID (These will be deleted.) CONSUME_LAYER_ID ( Quantity needs to be added to this layer)
CONSUME_LAYER_DATE ( Add quantity if CONSUME_LAYER_DATE is in a past period.
That is CONSUME_LAYER_DATE < (select start_date from gmf_period_statuses where period_id = p_period_id)
CONSUME_IB_DOC_QTY, CONSUME_IB_PRI_QTY.
Quanity CONSUME_IB_DOC_QTY needs to be added to REMAINING_IB_DOC_QTY of gmf_outgoing_material_layers or gmf_resource_layers.
Note that CONSUME_LAYER_ID could either belong to gmf_outgoing_material_layers or gmf_resource_layers.
LINE_TYPE will decide whether it is outgoing layer or resource layer.
*/
FOR vib IN CUR_VIB_DETAILS LOOP
IF vib.LINE_TYPE IN (-1, 2) THEN
Update gmf_outgoing_material_layers
set REMAINING_IB_DOC_QTY = REMAINING_IB_DOC_QTY + vib.CONSUME_IB_DOC_QTY
where layer_id = vib.CONSUME_LAYER_ID;
Update gmf_resource_layers
set REMAINING_IB_DOC_QTY = REMAINING_IB_DOC_QTY + vib.CONSUME_IB_DOC_QTY
where layer_id = vib.CONSUME_LAYER_ID;
DELETE
FROM gmf_batch_vib_details bvd
WHERE bvd.prod_layer_id IN
(SELECT il.layer_id
FROM gme_batch_header h, mtl_material_transactions t, gmf_incoming_material_layers il
WHERE h.batch_id = p_batch_id
AND h.batch_id = t.transaction_source_id
AND t.transaction_source_type_id = 5
AND il.mmt_transaction_id = t.transaction_id
AND il.mmt_organization_id = t.organization_id
AND il.layer_date >= l_start_date
);
DELETE
FROM gmf_outgoing_material_layers ol
WHERE (ol.mmt_organization_id, ol.mmt_transaction_id) IN
(SELECT distinct t.organization_id, t.transaction_id
FROM gme_batch_header h, mtl_material_transactions t
WHERE h.batch_id = p_batch_id
AND h.batch_id = t.transaction_source_id
AND t.transaction_source_type_id = 5
AND t.transaction_date >= l_start_date
);
DELETE
FROM gmf_resource_layers il
WHERE il.poc_trans_id IN
(SELECT t.poc_trans_id
FROM gme_batch_header h, gme_resource_txns t
WHERE h.batch_id = p_batch_id
AND h.batch_id = t.doc_id
AND t.doc_type = 'PROD'
AND t.trans_date >= l_start_date
);
DELETE
FROM gmf_incoming_material_layers il
WHERE il.layer_id IN
(SELECT il1.layer_id
FROM gme_batch_header h, mtl_material_transactions t, gmf_incoming_material_layers il1
WHERE h.batch_id = p_batch_id
AND h.batch_id = t.transaction_source_id
AND t.transaction_source_type_id = 5
AND il1.mmt_transaction_id = t.transaction_id
AND il1.mmt_organization_id = t.organization_id
AND il1.layer_date >= l_start_date
);
DELETE
FROM gmf_batch_requirements br
WHERE br.batch_id IN
(SELECT batch_id
FROM gme_batch_header
WHERE batch_id = p_batch_id
);
p_procedure_name => 'Delete_period_layers',
p_parameters => err_num,
p_message => 'Error deleting Old layer data for batch_id = '||p_batch_id||' Period id '||p_period_id||
' '||err_msg,p_error_type => 'I');
END Delete_period_layers;
select *
from
(
SELECT mmt.rowid r_id, mmt.transaction_id as trans_id, mmt.transaction_date as trans_date,
md.line_type as line_type, md.dtl_um as doc_um, 'M' as type, mmt.primary_quantity as trans_qty,
tp.transaction_id2 as reverse_id
from mtl_material_transactions mmt,
gme_transaction_pairs tp,
gme_material_details md,
gme_batch_header b
where md.batch_id = b.batch_id
and b.batch_id = l_batch_id
and mmt.transaction_source_type_id = 5
and mmt.transaction_source_id = b.batch_id
and mmt.trx_source_line_id = md.material_detail_id
and mmt.inventory_item_id = md.inventory_item_id
and mmt.organization_id = md.organization_id
and md.line_type IN (-1, 2)
and tp.transaction_id1(+) = mmt.transaction_id
AND mmt.transaction_date >= l_start_date
AND mmt.transaction_date <= l_end_date
and tp.pair_type(+) = 1
)
ORDER BY trans_date, line_type,
DECODE (line_type,
1, DECODE (( ABS (DECODE (trans_qty, 0, 1, trans_qty))
/ DECODE (trans_qty, 0, 1, trans_qty)
),
1, trans_id,
DECODE (reverse_id,
NULL, trans_id,
reverse_id + .5
)
),
trans_id
);
select start_date, end_date INTO l_start_date, l_end_date
from gmf_period_statuses
where period_id = p_period_id;
SELECT
mmt.transaction_id
, mmt.transaction_source_type_id
, mmt.transaction_action_id
, mmt.transaction_type_id
, mmt.inventory_item_id
, mmt.organization_id
, NULL as lot_number /* B9442981 */
, mmt.transaction_date
, mmt.primary_quantity as primary_quantity /* B9442981 Doc Qty */
, msi.primary_uom_code
, mmt.transaction_source_id -- batch_id
, mmt.trx_source_line_id -- line_id
, mmt.last_updated_by
, mmt.created_by
, mmt.last_update_login
FROM mtl_material_transactions mmt, -- mtl_transaction_lot_numbers mtln,
mtl_system_items_b msi
WHERE
mmt.ROWID = t.r_id
-- AND mtln.transaction_id (+) = mmt.transaction_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND msi.organization_id = mmt.organization_id
)
LOOP
l_trans_rec.transaction_id := trans_rec.transaction_id;
l_trans_rec.last_updated_by := trans_rec.last_updated_by;
l_trans_rec.last_update_login := trans_rec.last_update_login;
SELECT count(*)
INTO l_count
FROM gmf_outgoing_material_layers
WHERE
mmt_transaction_id = trans_rec.transaction_id
AND ((lot_number is not null and lot_number = trans_rec.lot_number)
OR
(lot_number is null))
;
select *
from
( SELECT rt.rowid r_id, rt.poc_trans_id as trans_id, rt.trans_date, rt.line_type as line_type, rt.trans_qty_um as doc_um, 'R' as type,
rt.resource_usage as trans_qty, rt.reverse_id
FROM gme_resource_txns rt, gme_batch_header b
WHERE rt.doc_type = 'PROD'
AND rt.doc_id = b.batch_id
AND rt.completed_ind = 1
AND rt.delete_mark = 0
AND rt.doc_id = l_batch_id
AND rt.trans_date >= l_start_date
AND rt.trans_date <= l_end_date
)
ORDER BY trans_date, line_type,
DECODE (line_type,
1, DECODE (( ABS (DECODE (trans_qty, 0, 1, trans_qty))
/ DECODE (trans_qty, 0, 1, trans_qty)
),
1, trans_id,
DECODE (reverse_id,
NULL, trans_id,
reverse_id + .5
)
),
trans_id
);
select start_date, end_date INTO l_start_date, l_end_date
from gmf_period_statuses
where period_id = p_period_id;
SELECT * INTO rt
FROM gme_resource_txns
WHERE
ROWID = t.r_id;
SELECT count(*)
INTO l_count
FROM gmf_resource_layers
WHERE
poc_trans_id = rt.poc_trans_id;
select *
from
(
SELECT mmt.rowid r_id, mmt.transaction_id as trans_id, mmt.transaction_date as trans_date,
md.line_type as line_type, md.dtl_um as doc_um, 'M' as type, mmt.primary_quantity as trans_qty,
tp.transaction_id2 as reverse_id, mmt.trx_source_line_id AS mat_det_id
from mtl_material_transactions mmt,
gme_transaction_pairs tp,
gme_material_details md,
gme_batch_header b
where md.batch_id = b.batch_id
and b.batch_id = l_batch_id
and mmt.transaction_source_type_id = 5
and mmt.transaction_source_id = b.batch_id
and mmt.trx_source_line_id = md.material_detail_id
and mmt.inventory_item_id = md.inventory_item_id
and mmt.organization_id = md.organization_id
and md.line_type = 1
and tp.transaction_id1(+) = mmt.transaction_id
AND mmt.transaction_date >= l_start_date
AND mmt.transaction_date <= l_end_date
and tp.pair_type(+) = 1
)
ORDER BY mat_det_id, trans_date, line_type,
DECODE (line_type,
1, DECODE (( ABS (DECODE (trans_qty, 0, 1, trans_qty))
/ DECODE (trans_qty, 0, 1, trans_qty)
),
1, trans_id,
DECODE (reverse_id,
NULL, trans_id,
reverse_id + .5
)
),
trans_id
);
select start_date, end_date INTO l_start_date, l_end_date
from gmf_period_statuses
where period_id = p_period_id;
SELECT
mmt.transaction_id
, mmt.transaction_source_type_id
, mmt.transaction_action_id
, mmt.transaction_type_id
, mmt.inventory_item_id
, mmt.organization_id
, NULL as lot_number /* B9442981 */
, mmt.transaction_date
, mmt.primary_quantity as primary_quantity /* B9442981 Doc Qty */
, msi.primary_uom_code
, mmt.transaction_source_id -- batch_id
, mmt.trx_source_line_id -- line_id
, mmt.last_updated_by
, mmt.created_by
, mmt.last_update_login
FROM mtl_material_transactions mmt, -- mtl_transaction_lot_numbers mtln,
mtl_system_items_b msi
WHERE
mmt.ROWID = t.r_id
-- AND mtln.transaction_id (+) = mmt.transaction_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND msi.organization_id = mmt.organization_id
)
LOOP
l_trans_rec.transaction_id := trans_rec.transaction_id;
l_trans_rec.last_updated_by := trans_rec.last_updated_by;
l_trans_rec.last_update_login := trans_rec.last_update_login;
SELECT count(*)
INTO l_count
FROM gmf_incoming_material_layers
WHERE
mmt_transaction_id = trans_rec.transaction_id
AND ((lot_number is not null and lot_number = trans_rec.lot_number)
OR
(lot_number is null))
;
SELECT count(*)
INTO l_count
FROM gmf_outgoing_material_layers
WHERE
mmt_transaction_id = trans_rec.transaction_id
AND ((lot_number is not null and lot_number = trans_rec.lot_number)
OR
(lot_number is null))
;
SELECT batch_id
FROM gme_batch_header b
WHERE b.batch_status = 4
AND b.batch_id = l_batch_id
AND b.batch_close_date >= l_start_date
AND b.batch_close_date <= l_end_date;
select start_date, end_date INTO l_start_date, l_end_date
from gmf_period_statuses
where period_id = p_period_id;
SELECT gps.period_id,
gps.start_date,
gps.end_date
FROM gmf_period_statuses gps
WHERE gps.cost_type_id = l_cost_type
AND gps.calendar_code = l_calendar
AND gps.period_code = l_period
AND gps.legal_entity_id = l_legal_entity_id;
SELECT cmm.cost_type
FROM gmf_fiscal_policies gfp, cm_mthd_mst cmm
WHERE gfp.cost_type_id = cmm.cost_type_id
AND gfp.legal_entity_id = l_legal_entity_id
AND cmm.cost_type <> 6
UNION
SELECT cmm2.cost_type
FROM gmf_fiscal_policies gfp, cm_mthd_mst cmm1,cm_mthd_mst cmm2
WHERE gfp.cost_type_id = cmm1.cost_type_id
AND gfp.legal_entity_id = l_legal_entity_id
AND cmm1.cost_type = 6
AND cmm1.default_lot_cost_type_id = cmm2.cost_type_id;
select Count(*) from mtl_material_transactions
where transaction_date >= l_startdate
and transaction_date <= l_enddate
and transaction_action_id NOT IN (2, 28)
and opm_costed_flag IS NULL
and organization_id IN (SELECT organization_id
FROM gmf_organization_definitions
WHERE legal_entity_id = l_legal_entity_id);
SELECT batch_id, batch_no, batch_status , asd, batch_close_date
FROM (
SELECT h.batch_id, h.batch_no, h.batch_status , h.actual_start_date asd,
h.batch_close_date, h.organization_id
FROM gme_batch_header h,
mtl_material_transactions t
WHERE h.batch_status in (2,3,4) -- B9441550
AND h.actual_start_date <= l_enddate
AND h.batch_id = t.transaction_source_id
AND t.transaction_source_type_id = 5
AND t.transaction_date >= l_startdate
AND t.transaction_date <= l_enddate
UNION
SELECT h.batch_id, h.batch_no, h.batch_status , h.actual_start_date asd,
h.batch_close_date, h.organization_id
FROM gme_batch_header h,
gme_resource_txns r
WHERE h.batch_status in (2,3,4) -- B9441550
AND h.actual_start_date <= l_enddate
AND h.batch_id = r.doc_id
AND r.trans_date >= l_startdate
AND r.trans_date <= l_enddate
AND r.completed_ind = 1
AND r.delete_mark = 0
UNION
SELECT h.batch_id, h.batch_no, h.batch_status , h.actual_start_date asd,
h.batch_close_date, h.organization_id
FROM gme_batch_header h
WHERE h.batch_status = 4
AND h.actual_start_date <= l_enddate
AND h.batch_close_date >= l_startdate
AND h.batch_close_date <= l_enddate
UNION -- B9441550
SELECT h.batch_id, h.batch_no, h.batch_status , h.plan_start_date asd,
h.batch_close_date, h.organization_id
FROM gme_batch_header h
WHERE h.batch_status = -1
AND h.batch_id = l_batch_id
) batches
WHERE batches.batch_id = l_batch_id
AND batches.organization_id = l_org_id
AND batches.organization_id IN
(SELECT organization_id
FROM gmf_organization_definitions
WHERE legal_entity_id = l_legal_entity_id);
SELECT batch_id, batch_no, batch_status , asd, batch_close_date
FROM (
SELECT h.batch_id, h.batch_no, h.batch_status , h.actual_start_date asd,
h.batch_close_date, h.organization_id
FROM gme_batch_header h,
mtl_material_transactions t
WHERE h.batch_status in (2,3,4) -- B9441550
AND h.actual_start_date <= l_enddate
AND h.batch_id = t.transaction_source_id
AND t.transaction_source_type_id = 5
AND t.transaction_date >= l_startdate
AND t.transaction_date <= l_enddate
UNION
SELECT h.batch_id, h.batch_no, h.batch_status , h.actual_start_date asd,
h.batch_close_date, h.organization_id
FROM gme_batch_header h,
gme_resource_txns r
WHERE h.batch_status in (2,3,4) -- B9441550
AND h.actual_start_date <= l_enddate
AND h.batch_id = r.doc_id
AND r.trans_date >= l_startdate
AND r.trans_date <= l_enddate
AND r.completed_ind = 1
AND r.delete_mark = 0
UNION
SELECT h.batch_id, h.batch_no, h.batch_status , h.actual_start_date asd,
h.batch_close_date, h.organization_id
FROM gme_batch_header h
WHERE h.batch_status = 4
AND h.actual_start_date <= l_enddate
AND h.batch_close_date >= l_startdate
AND h.batch_close_date <= l_enddate
UNION -- B9441550
SELECT h.batch_id, h.batch_no, h.batch_status , h.plan_start_date asd,
h.batch_close_date, h.organization_id
FROM gme_batch_header h
WHERE h.batch_status = -1
AND h.organization_id = l_org_id
AND h.plan_start_date <= l_enddate
AND h.plan_start_date >= l_startdate
) batches
WHERE batches.organization_id = l_org_id
AND batches.organization_id IN
(SELECT organization_id
FROM gmf_organization_definitions
WHERE legal_entity_id = l_legal_entity_id);
SELECT batch_id, batch_no, batch_status , asd, batch_close_date
FROM (
SELECT h.batch_id, h.batch_no, h.batch_status , h.actual_start_date asd,
h.batch_close_date, h.organization_id
FROM gme_batch_header h,
mtl_material_transactions t
WHERE h.batch_status in (2,3,4) -- B9441550
AND h.actual_start_date <= l_enddate
AND h.batch_id = t.transaction_source_id
AND t.transaction_source_type_id = 5
AND t.transaction_date >= l_startdate
AND t.transaction_date <= l_enddate
UNION
SELECT h.batch_id, h.batch_no, h.batch_status , h.actual_start_date asd,
h.batch_close_date, h.organization_id
FROM gme_batch_header h,
gme_resource_txns r
WHERE h.batch_status in (2,3,4) -- B9441550
AND h.actual_start_date <= l_enddate
AND h.batch_id = r.doc_id
AND r.trans_date >= l_startdate
AND r.trans_date <= l_enddate
AND r.completed_ind = 1
AND r.delete_mark = 0
UNION
SELECT h.batch_id, h.batch_no, h.batch_status , h.actual_start_date asd,
h.batch_close_date, h.organization_id
FROM gme_batch_header h
WHERE h.batch_status = 4
AND h.actual_start_date <= l_enddate
AND h.batch_close_date >= l_startdate
AND h.batch_close_date <= l_enddate
UNION -- B9441550
SELECT h.batch_id, h.batch_no, h.batch_status , h.plan_start_date asd,
h.batch_close_date, h.organization_id
FROM gme_batch_header h
WHERE h.batch_status = -1
AND h.plan_start_date <= l_enddate
AND h.plan_start_date >= l_startdate
) batches
WHERE batches.organization_id IN
(SELECT organization_id
FROM gmf_organization_definitions
WHERE legal_entity_id = l_legal_entity_id);
GMF_LAYERS.Delete_old_layers(rec.batch_id);
GMF_LAYERS.Delete_period_layers(rec.batch_id, l_periodid);
SELECT count(*) INTO l_count
FROM gmf_incoming_material_layers il
WHERE il.PSEUDO_LAYER_ID IS NOT NULL
AND EXISTS
(SELECT 1
FROM gme_batch_header h, mtl_material_transactions t,
gmf_incoming_material_layers im
WHERE h.batch_id = rec.batch_id
AND h.batch_id = t.transaction_source_id
AND t.transaction_source_type_id = 5
AND im.mmt_transaction_id = t.transaction_id
AND im.mmt_organization_id = t.organization_id
AND im.layer_id = il.PSEUDO_LAYER_ID
);
SELECT count(*) INTO l_count
FROM gmf_incoming_material_layers il
WHERE (il.mmt_organization_id, il.mmt_transaction_id) IN
(SELECT DISTINCT t.organization_id, t.transaction_id
FROM gme_batch_header h, mtl_material_transactions t
WHERE h.batch_id = rec.batch_id
AND h.batch_id = t.transaction_source_id
AND t.transaction_source_type_id = 5
);
SELECT count(*) INTO l_count
FROM gmf_outgoing_material_layers ol
WHERE (ol.mmt_organization_id, ol.mmt_transaction_id) IN
(SELECT DISTINCT t.organization_id, t.transaction_id
FROM gme_batch_header h, mtl_material_transactions t
WHERE h.batch_id = rec.batch_id
AND h.batch_id = t.transaction_source_id
AND t.transaction_source_type_id = 5
);
SELECT count(*) INTO l_count
FROM gmf_batch_vib_details bvd
WHERE bvd.requirement_id IN
(SELECT br.requirement_id
FROM gmf_batch_requirements br, gme_batch_header h
WHERE h.batch_id = rec.batch_id
AND h.batch_id = br.batch_id
);
SELECT count(*) INTO l_count
FROM gmf_batch_requirements br
WHERE br.batch_id IN
(SELECT batch_id
FROM gme_batch_header
WHERE batch_id = rec.batch_id
);