[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| Inserts the transaction to interface table
|
| ARGUMENTS
| p_mmti_rec -- mtl_transaction_interface rowtype
| p_mmli_tbl -- table of mtl_trans_lots_inter_tbl as input
|
| RETURNS
| returns via x_status OUT parameters
|
| HISTORY
| Created 02-Feb-05 Pawan Kumar
|
+==========================================================================+ */
PROCEDURE create_material_txn (
p_mmti_rec IN mtl_transactions_interface%ROWTYPE
,p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl
,p_phantom_trans IN NUMBER DEFAULT 0
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_MATERIAL_TXN';
select count(*)
into l_cnt_int
from mtl_transactions_interface
where transaction_header_id= gme_common_pvt.g_transaction_header_id;
select count(*) into l_cnt_temp
from mtl_material_transactions_temp
where transaction_header_id= gme_common_pvt.g_transaction_header_id;
FOR get_msgs IN (SELECT error_explanation FROM mtl_transactions_interface
WHERE transaction_header_id = gme_common_pvt.g_transaction_header_id
AND error_explanation IS NOT NULL) LOOP
IF (g_debug <= gme_debug.g_log_statement) THEN
gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'error_explanation is: '||get_msgs.error_explanation);
| update_material_txn
|
| USAGE
| update the transaction in interface table - it deletes all transactions
| of transaction_id passed. Creates new transactions as passed.
|
| ARGUMENTS
| p_transaction_id - transaction_id from mmt for deletion
| p_mmti_rec -- mtl_transaction_interface rowtype
| p_mmli_tbl -- table of mtl_transaction_lots_inumber_tbl as input
|
| RETURNS
| returns via x_status OUT parameters
|
| HISTORY
| Created 02-Feb-05 Pawan Kumar
|
+==========================================================================+ */
PROCEDURE update_material_txn (
p_transaction_id IN NUMBER
,p_mmti_rec IN mtl_transactions_interface%ROWTYPE
,p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_MATERIAL_TXN';
delete_material_txn_err EXCEPTION;
|| 'calling delete with :'
|| p_transaction_id);
delete_material_txn (p_transaction_id => p_transaction_id
,x_return_status => l_return_status);
gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
RAISE delete_material_txn_err;
|| 'calling create in update with :'
|| l_mmti_rec.transaction_interface_id);
WHEN delete_material_txn_err THEN
gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
END update_material_txn;
| update_material_txn
|
| USAGE
| update the transaction in interface table - it deletes all transactions
| by getting transaction_id from the mmt record passed. Creates new transactions
| in interface by converting the mmt to mmti.
|
| ARGUMENTS
| p_mmt_rec -- mtl_material_transaction rowtype
| p_mmln_tbl -- table of mtl_transaction_lots_inumber_tbl as input
|
| RETURNS
| returns via x_status OUT parameters
|
| HISTORY
| Created 02-Feb-05 Pawan Kumar
|
+==========================================================================+ */
PROCEDURE update_material_txn (
p_mmt_rec IN mtl_material_transactions%ROWTYPE
,p_mmln_tbl IN gme_common_pvt.mtl_trans_lots_num_tbl
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_MATERIAL_TXN-2';
delete_material_txn_err EXCEPTION;
|| 'calling delete transaction for transaction id'
|| l_transaction_id);
delete_material_txn (p_transaction_id => l_transaction_id
,x_return_status => l_return_status);
gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
RAISE delete_material_txn_err;
WHEN delete_material_txn_err THEN
gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
END update_material_txn;
| delete_material_txn
|
| USAGE
| delete all transactions of transaction_id passed by creating reverse transaction.
|
| ARGUMENTS
| p_transaction_id -- transaction_id from mmt for deletion
|
|
| RETURNS
| returns via x_status OUT parameters
|
| HISTORY
| Created 02-Feb-05 Pawan Kumar
|
+==========================================================================+ */
PROCEDURE delete_material_txn (
p_transaction_id IN NUMBER
,p_txns_pair IN NUMBER DEFAULT NULL
,x_return_status OUT NOCOPY VARCHAR2)
IS
CURSOR cur_get_ph_txns (v_transaction_id NUMBER)
IS
SELECT transaction_id2
FROM gme_transaction_pairs
WHERE transaction_id1 = v_transaction_id
AND pair_type = gme_common_pvt.g_pairs_phantom_type;
l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_MATERIAL_TXN';
delete_material_txn_err EXCEPTION;
|| 'inserting into pairs table transaction_id:'
|| l_transaction_id);
|| 'inserting into pairs table batch_id:'
|| l_mat_dtl_rec.batch_id);
|| 'inserting into pairs table material_detail_id:'
|| l_mat_dtl_rec.material_detail_id);
|| 'inserting into pairs table pair_type:'
|| gme_common_pvt.g_pairs_reversal_type);
INSERT INTO gme_transaction_pairs
(batch_id, material_detail_id
,transaction_id1, transaction_id2
,pair_type)
VALUES (l_mat_dtl_rec.batch_id, l_mat_dtl_rec.material_detail_id
,l_mmt_rec.transaction_id, NULL
,gme_common_pvt.g_pairs_reversal_type);
|| 'calling delete txns for phantom:'
|| m_transaction_id);
delete_material_txn (p_transaction_id => m_transaction_id
,p_txns_pair => 1
,x_return_status => l_return_status);
RAISE delete_material_txn_err;
WHEN delete_material_txn_err THEN
gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
END delete_material_txn;
| Inserts the transaction to interface table
|
| ARGUMENTS
| p_mmti_rec -- mtl_transaction_interface rowtype
| p_mmli_tbl -- table of mtl_trans_lots_inter_tbl as input
|
| RETURNS
| returns via x_status OUT parameters
|
| HISTORY
| Created 02-Feb-05 Pawan Kumar
|
+==========================================================================+ */
PROCEDURE build_txn_inter (
p_mmti_rec IN mtl_transactions_interface%ROWTYPE
,p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl
,p_assign_phantom IN NUMBER DEFAULT 0
,x_mmti_rec OUT NOCOPY mtl_transactions_interface%ROWTYPE
,x_mmli_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_inter_tbl
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_mmti_rec mtl_transactions_interface%ROWTYPE;
l_insert_hdr BOOLEAN;
insert_hdr_err EXCEPTION ;
l_insert_hdr := FALSE;
l_insert_hdr := TRUE;
,p_insert_hdr => l_insert_hdr);
|| 'after header- inserting lot');
/* Bug 4929610 Added code to insert if not inserted originally */
IF NOT(l_insert_hdr) THEN
insert_txn_inter_hdr(p_mmti_rec => x_mmti_rec,
x_return_status => l_return_status);
RAISE insert_hdr_err;
WHEN insert_hdr_err THEN
gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'insert_hdr_err');
| Inserts the transaction to interface table
|
| ARGUMENTS
| p_mmti_rec -- mtl_transaction_interface rowtype
|
|
| RETURNS
| returns via x_status OUT parameters
|
| HISTORY
| Created 02-Feb-05 Pawan Kumar
| 13-Sep-05 Namit Singhi - Modified to include insert into transfer_lpn_id.
|
+==========================================================================+ */
PROCEDURE build_txn_inter_hdr (
p_mmti_rec IN mtl_transactions_interface%ROWTYPE
,p_assign_phantom IN NUMBER DEFAULT 0
,x_mmti_rec OUT NOCOPY mtl_transactions_interface%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2
,p_insert_hdr IN BOOLEAN DEFAULT TRUE)
IS
CURSOR get_location (v_org_id IN NUMBER
,v_sub_inv IN VARCHAR2
,v_loc_id IN NUMBER) IS
SELECT substr(concatenated_segments,1,100)
FROM wms_item_locations_kfv
WHERE organization_id = v_org_id
AND subinventory_code = v_sub_inv
AND inventory_location_id (+) = v_loc_id;
insert_hdr_err EXCEPTION ;
SELECT mtl_material_transactions_s.NEXTVAL
INTO gme_common_pvt.g_transaction_header_id
FROM DUAL;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_mmti_rec.transaction_interface_id
FROM DUAL;
|| 'last_updated_by: '
|| gme_common_pvt.g_user_ident);
IF (p_insert_hdr) THEN
insert_txn_inter_hdr(p_mmti_rec => l_mmti_rec,
x_return_status => l_return_status);
RAISE insert_hdr_err;
|| 'after inserting header with status:'||x_return_status);
WHEN insert_hdr_err THEN
x_return_status := l_return_status;
SELECT substr(concatenated_segments,1,100)
INTO l_item
FROM mtl_system_items_kfv
WHERE organization_id = l_mmti_rec.organization_id
AND inventory_item_id = l_mmti_rec.inventory_item_id;
SELECT transaction_type_name
INTO l_type
FROM mtl_transaction_types
WHERE transaction_type_id = l_mmti_rec.transaction_type_id;
| Inserts the transaction to interface table
|
| ARGUMENTS
|
| p_mmli_rec -- table of mtl_trans_lots_inter_tbl as input
|
| RETURNS
| returns via x_status OUT parameters
|
| HISTORY
| Created 02-Feb-05 Pawan Kumar
| Bug 6925025 11-Apr-2008 Archana Mundhe
| Added parameters subinventory_code and locator_id.
+==========================================================================+ */
PROCEDURE build_txn_inter_lot (
p_trans_inter_id IN NUMBER
,p_transaction_type_id IN NUMBER
,p_inventory_item_id IN NUMBER
,p_subinventory_code IN VARCHAR2
,p_locator_id IN NUMBER
,p_mmli_rec IN mtl_transaction_lots_interface%ROWTYPE
,x_mmli_rec OUT NOCOPY mtl_transaction_lots_interface%ROWTYPE
,x_return_status OUT NOCOPY VARCHAR2)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'BUILD_TXN_INTER_LOT';
INSERT INTO mtl_transaction_lots_interface
(transaction_interface_id, last_update_date
,last_updated_by, last_update_login
,creation_date, created_by
,lot_number, transaction_quantity
,primary_quantity
,secondary_transaction_quantity)
VALUES ( p_trans_inter_id --transaction_interface_id
,gme_common_pvt.g_timestamp --last_update_date
,gme_common_pvt.g_user_ident --last_updated_by
,gme_common_pvt.g_user_ident --last_update_login
,gme_common_pvt.g_timestamp --creation_date
,gme_common_pvt.g_user_ident --created_by
,l_mmli_rec.lot_number --lot_number
,l_mmli_rec.transaction_quantity --lot_quantity
,l_mmli_rec.primary_quantity
,l_mmli_rec.secondary_transaction_quantity);
SELECT substr(concatenated_segments,1,100)
INTO l_item
FROM mtl_system_items_kfv
WHERE organization_id = gme_common_pvt.g_organization_id
AND inventory_item_id = l_inventory_item_id;
SELECT transaction_type_name
INTO l_type
FROM mtl_transaction_types
WHERE transaction_type_id = p_transaction_type_id;
SELECT *
FROM mtl_material_transactions mmt
WHERE transaction_id = v_transaction_id
AND NOT EXISTS ( SELECT /*+ no_unnest */
transaction_id1
FROM gme_transaction_pairs
WHERE transaction_id1 = mmt.transaction_id
AND pair_type = v_reversal_type)
ORDER BY mmt.transaction_quantity;
SELECT *
FROM mtl_transaction_lot_numbers
WHERE transaction_id = v_transaction_id;
x_mmti_rec.last_updated_by := l_mmt_rec.last_updated_by;
x_mmti_rec.last_update_login := l_mmt_rec.last_update_login;
x_mmti_rec.last_update_date := l_mmt_rec.last_update_date;
x_mmli_tbl (i).last_update_date :=
l_mmln_tbl (i).last_update_date;
x_mmli_tbl (i).last_updated_by := l_mmln_tbl (i).last_updated_by;
SELECT *
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)
ORDER BY mmt.transaction_quantity;
SELECT *
FROM mtl_transaction_lot_numbers
WHERE transaction_id = v_transaction_id;
SELECT transaction_interface_id
FROM mtl_transactions_interface
WHERE transaction_header_id = v_hdr_id
AND transaction_source_type_id = gme_common_pvt.g_txn_source_type
AND wip_entity_type = gme_common_pvt.g_wip_entity_type_batch;
/* update mtl_transactions_interface
set error_code = 'wip_mtlInterfaceProc_pub.processInterface()',
error_explanation = l_errMessage,
process_flag = wip_constants.mti_error
where transaction_header_id = p_txnHdrID; */
SELECT SUM (DECODE (v_trans_uom,
t.transaction_uom, transaction_quantity,
inv_convert.inv_um_convert (d.inventory_item_id,
gme_common_pvt.g_precision,
t.transaction_quantity,
t.transaction_uom,
v_trans_uom,
NULL,
NULL
)
)
)
FROM mtl_material_transactions t, gme_material_details d
WHERE t.organization_id = v_organization_id
AND t.inventory_item_id = v_item_id
AND t.transaction_source_id = v_batch_id
AND t.trx_source_line_id = v_mat_det_id
AND t.transaction_source_type_id = gme_common_pvt.g_txn_source_type
AND t.trx_source_line_id = d.material_detail_id
AND (t.revision IS NULL OR t.revision = v_revision)
GROUP BY t.revision, t.inventory_item_id;
SELECT lot_number,
SUM (DECODE (v_trans_uom,
m.transaction_uom, m.transaction_quantity,
inv_convert.inv_um_convert (d.inventory_item_id,
gme_common_pvt.g_precision,
m.transaction_quantity,
m.transaction_uom,
v_trans_uom,
NULL,
NULL
)
)
)
FROM mtl_material_transactions m, mtl_transaction_lot_numbers l, gme_material_details d
WHERE l.transaction_id = m.transaction_id
AND m.trx_source_line_id = d.material_detail_id
AND l.lot_number = v_lot_number
AND l.inventory_item_id = v_item_id
AND l.organization_id = v_organization_id
AND l.transaction_source_id = v_batch_id
AND m.trx_source_line_id = v_mat_det_id
AND m.transaction_source_type_id = gme_common_pvt.g_txn_source_type
-- Pawan Kumar added for checking of revision bug 5451006- 5493370
AND (m.revision IS NULL OR m.revision = v_revision)
GROUP BY l.lot_number, l.inventory_item_id;
SELECT *
FROM mtl_transactions_interface
WHERE transaction_interface_id = v_trans_inter_id;
SELECT *
FROM mtl_system_items_b
WHERE inventory_item_id = v_item_id AND organization_id = v_org_id;
SELECT step_status
FROM gme_batch_steps s, gme_batch_step_items i
WHERE s.batchstep_id = i.batchstep_id
AND i.material_detail_id = v_matl_dtl_id;
SELECT lot_number, SUM (transaction_quantity) l_mtli_lot_qty
FROM mtl_transaction_lots_interface
WHERE transaction_interface_id = v_trans_inter_id
GROUP BY lot_number;
IF l_batch_hdr_rec.update_inventory_ind = 'Y' THEN
IF l_mmti_rec.trx_source_line_id IS NOT NULL THEN
l_mat_dtl_rec.material_detail_id := l_mmti_rec.trx_source_line_id;
FOR get_lots IN (SELECT DISTINCT lot_number FROM mtl_transaction_lots_interface WHERE transaction_interface_id = p_transaction_interface_id) LOOP
gme_transactions_pvt.validate_lot_for_ing(p_organization_id => l_mmti_rec.organization_id,
p_inventory_item_id => l_mmti_rec.inventory_item_id,
p_lot_number => get_lots.lot_number,
x_return_status => l_return_status);
END IF; /* update_inventory_ind = 'Y' */
UPDATE mtl_transactions_interface
SET ERROR_CODE = g_pkg_name || '.' || p_api_name
,error_explanation = NVL (x_message_list, l_errm)
,process_flag = 3 -- we can make it a constant in gme common
WHERE transaction_interface_id = l_transaction_interface_id;
| Bug 5763818 28-Feb-2007 Archana Mundhe Do not update actual qty if
| the material detail line has been deleted.
| Bug 6997483 01-May-2005 Archana Mundhe Added parameter transaction_id
| to gme_unrelease_batch_pvt.create_matl_resv_pplot.
+==========================================================================+ */
PROCEDURE gme_post_process (
p_transaction_id IN NUMBER
,x_return_status OUT NOCOPY VARCHAR2)
IS
CURSOR cur_get_trans (v_transaction_id NUMBER)
IS
SELECT t.transaction_id, t.transaction_source_id, l.lot_number
,t.trx_source_line_id, t.source_line_id, t.transaction_type_id,
t.transaction_reference, t.inventory_item_id
, t.organization_id
FROM mtl_material_transactions t, mtl_transaction_lot_numbers l
WHERE t.transaction_id = l.transaction_id(+)
AND t.transaction_id = v_transaction_id;
SELECT a.transaction_id, a.lot_number, a.doc_qty, SUM(a.doc_qty) over() mtl_qty
FROM (SELECT t.transaction_id, tl.lot_number,
DECODE(d.dtl_um,t.transaction_uom, NVL(tl.transaction_quantity,t.transaction_quantity),
Inv_Convert.inv_um_convert(d.inventory_item_id,tl.lot_number,t.organization_id, 5
,NVL(tl.transaction_quantity,t.transaction_quantity), t.transaction_uom
,d.dtl_um, NULL, NULL)) doc_qty
FROM mtl_material_transactions t , gme_material_details d, mtl_transaction_lot_numbers tl
WHERE t.organization_id = v_organization_id
AND t.transaction_source_id = v_batch_id
AND t.trx_source_line_id = v_mat_det_id
AND t.transaction_source_type_id = gme_common_pvt.g_txn_source_type
AND t.trx_source_line_id = d.material_detail_id
AND tl.transaction_id(+) = t.transaction_id) a;
SELECT lot_number, SUM (l.transaction_quantity)
FROM mtl_material_transactions m, mtl_transaction_lot_numbers l
WHERE l.transaction_id = m.transaction_id
AND l.lot_number = v_lot_number
AND l.inventory_item_id = v_item_id
AND l.organization_id = v_organization_id
AND l.transaction_source_id = v_batch_id
AND m.trx_source_line_id = v_mat_det_id
AND m.transaction_source_type_id =
gme_common_pvt.g_txn_source_type
GROUP BY l.lot_number, l.inventory_item_id;
select count(1)
from GME_ERES_GTMP
where event_name = 'oracle.apps.gme.batchmtl.removed'
and event_key = v_transaction_source_id||'-'||v_trx_source_line_id;
|| 'for inserting reverse transaction_id: '
|| l_transaction_id);
|| 'for inserting reverse source_line_id: '
|| l_source_line_id);
UPDATE gme_transaction_pairs
SET transaction_id2 = l_transaction_id
WHERE batch_id = l_transaction_source_id
AND material_detail_id = l_trx_source_line_id
AND transaction_id1 = l_source_line_id
AND pair_type = gme_common_pvt.g_pairs_reversal_type;
INSERT INTO gme_transaction_pairs
(batch_id, material_detail_id
,transaction_id1, transaction_id2
,pair_type)
VALUES (l_transaction_source_id, l_trx_source_line_id
,l_transaction_id, l_source_line_id
,gme_common_pvt.g_pairs_reversal_type);
|| 'after inserting reverse transaction_id: '
|| l_transaction_id);
|| 'after inserting reverse source_line_id: '
|| l_source_line_id);
|| 'for inserting phantom l_transaction_reference: '
|| l_transaction_reference);
|| 'update row -phantom in pairs having l_trans_ref: '
|| l_transaction_reference);
|| 'update row-phantom in pairs with l_trans_ID: '
|| l_transaction_id);
UPDATE gme_transaction_pairs
SET transaction_id2 = l_transaction_id
WHERE transaction_id2 = l_transaction_reference
AND pair_type = gme_common_pvt.g_pairs_phantom_type;
SELECT *
INTO l_gme_pairs_rec
FROM gme_transaction_pairs
WHERE transaction_id2 = l_transaction_id
AND pair_type = gme_common_pvt.g_pairs_phantom_type;
|| 'after update row -phantom l_trans_id1: '
|| l_gme_pairs_rec.transaction_id1);
|| 'after update row-phantom l_trans_ID2: '
|| l_gme_pairs_rec.transaction_id1);
|| 'insert row- after update -transaction_id1: '
|| l_gme_pairs_rec.transaction_id1);
|| 'insert row- after update -transaction_id2: '
|| l_gme_pairs_rec.transaction_id2);
|| 'insert row- after update -batch_id:'
|| l_transaction_source_id);
|| 'insert row- after update -mat_det_id: '
|| l_trx_source_line_id);
INSERT INTO gme_transaction_pairs
(batch_id, material_detail_id
,transaction_id1, transaction_id2
,pair_type)
VALUES (l_transaction_source_id, l_trx_source_line_id
,l_transaction_id, l_gme_pairs_rec.transaction_id1
,gme_common_pvt.g_pairs_phantom_type);
|| 'insert row-transaction_id1: '
|| l_transaction_id);
|| 'insert row-transaction_id2: '
|| l_transaction_reference);
|| 'insert row-batch_id: '
|| l_transaction_source_id);
|| 'insert row-material_detail_id: '
|| l_trx_source_line_id);
INSERT INTO gme_transaction_pairs
(batch_id, material_detail_id
,transaction_id1, transaction_id2
,pair_type)
VALUES (l_transaction_source_id, l_trx_source_line_id
,l_transaction_id, l_transaction_reference
,gme_common_pvt.g_pairs_phantom_type);
IF NOT gme_material_details_dbl.update_row
(p_material_detail => l_mat_dtl_rec) THEN
RAISE fnd_api.g_exc_error;
DELETE FROM gme_transaction_pairs
WHERE batch_id = l_batch_id;
DELETE FROM gme_transaction_pairs
WHERE material_detail_id = l_material_detail_id;
DELETE FROM gme_transaction_pairs
WHERE batch_id = l_batch_id
AND material_detail_id = l_material_detail_id;
SELECT ERROR_CODE, error_explanation
FROM mtl_transactions_interface
WHERE transaction_header_id =
gme_common_pvt.g_transaction_header_id;
SELECT ERROR_CODE, error_explanation
FROM mtl_material_transactions_temp
WHERE transaction_header_id =
gme_common_pvt.g_transaction_header_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
, mtln.lot_number
, mmt.transaction_date
, nvl(mtln.primary_quantity, mmt.primary_quantity) as primary_quantity
, msi.primary_uom_code
, nvl(mtln.transaction_quantity, mmt.transaction_quantity) as transaction_quantity /* Doc Qty */
, md.dtl_um as doc_uom
, mmt.transaction_source_id -- batch_id
, mmt.trx_source_line_id -- line_id
, gtp.transaction_id2 AS reverse_id
, md.line_type
, 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,
gme_material_details md,
gme_transaction_pairs gtp
WHERE
mmt.transaction_set_id = gme_common_pvt.g_transaction_header_id
AND mtln.transaction_id(+) = mmt.transaction_id
AND msi.organization_id = mmt.organization_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND md.material_detail_id = mmt.trx_source_line_id
AND gtp.transaction_id1(+) = mmt.transaction_id
AND gtp.batch_id(+) = mmt.transaction_source_id
AND gtp.material_detail_id(+) = mmt.trx_source_line_id
AND mmt.transaction_source_type_id = gme_common_pvt.g_txn_source_type /*Bug#6266714*/
ORDER BY mmt.transaction_date,
case md.line_type
when -1 then 0
when 2 then 1
when 1 then 2
end,
mmt.transaction_id, mtln.lot_number) 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;
p_qty_tbl.delete();
| update_quantities
|
| USAGE
| Update quantity at the level specified by the input and
| return the quantities at the level after the update
|
| ARGUMENTS
| p_api_version API Version of this procedure. Current version is 1.0
| p_init_msg_list fnd_api.g_false or fnd_api.g_true is passed as input to determine whether to Initialize message list or not |
| x_return_status Returns the status to indicate success or failure of execution
| x_msg_count Returns number of error message in the error message stack in case of failure
| x_msg_data Returns the error message in case of failure
|
| RETURNS
| returns via x_ OUT parameters
|
| HISTORY
| Created 07-Mar-05 Jalaj Srivastava
|
+==========================================================================+ */
PROCEDURE update_quantities (
p_api_version_number IN NUMBER := 1
,p_init_msg_lst IN VARCHAR2
DEFAULT fnd_api.g_false
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_organization_id IN NUMBER
,p_inventory_item_id IN NUMBER
,p_tree_mode IN INTEGER
,p_is_serial_control IN BOOLEAN := FALSE
,p_demand_source_type_id IN NUMBER
DEFAULT gme_common_pvt.g_txn_source_type
,p_demand_source_header_id IN NUMBER DEFAULT -9999
,p_demand_source_line_id IN NUMBER DEFAULT -9999
,p_demand_source_name IN VARCHAR2 DEFAULT NULL
,p_lot_expiration_date IN DATE DEFAULT NULL
,p_revision IN VARCHAR2 DEFAULT NULL
,p_lot_number IN VARCHAR2 DEFAULT NULL
,p_subinventory_code IN VARCHAR2 DEFAULT NULL
,p_locator_id IN NUMBER DEFAULT NULL
,p_grade_code IN VARCHAR2 DEFAULT NULL
,p_primary_quantity IN NUMBER
,p_quantity_type IN INTEGER
,p_secondary_quantity IN NUMBER
,p_onhand_source IN NUMBER
DEFAULT inv_quantity_tree_pvt.g_all_subs
,x_qoh OUT NOCOPY NUMBER
,x_rqoh OUT NOCOPY NUMBER
,x_qr OUT NOCOPY NUMBER
,x_qs OUT NOCOPY NUMBER
,x_att OUT NOCOPY NUMBER
,x_atr OUT NOCOPY NUMBER
,x_sqoh OUT NOCOPY NUMBER
,x_srqoh OUT NOCOPY NUMBER
,x_sqr OUT NOCOPY NUMBER
,x_sqs OUT NOCOPY NUMBER
,x_satt OUT NOCOPY NUMBER
,x_satr OUT NOCOPY NUMBER
,p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
,p_cost_group_id IN NUMBER DEFAULT NULL
,p_containerized IN NUMBER
DEFAULT inv_quantity_tree_pvt.g_containerized_false
,p_lpn_id IN NUMBER DEFAULT NULL
,p_transfer_locator_id IN NUMBER DEFAULT NULL)
IS
l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_QUANTITIES';
|| 'Calling Inv_Quantity_Tree_Pub.Update_Quantities');
inv_quantity_tree_pub.update_quantities
(p_api_version_number => p_api_version_number
,p_init_msg_lst => p_init_msg_lst
,x_return_status => x_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_organization_id => p_organization_id
,p_inventory_item_id => p_inventory_item_id
,p_tree_mode => p_tree_mode
,p_is_revision_control => l_is_revision_control
,p_is_lot_control => l_is_lot_control
,p_is_serial_control => p_is_serial_control
,p_grade_code => p_grade_code
,p_demand_source_type_id => p_demand_source_type_id
,p_demand_source_header_id => p_demand_source_header_id
,p_demand_source_line_id => p_demand_source_line_id
,p_demand_source_name => p_demand_source_name
,p_lot_expiration_date => p_lot_expiration_date
,p_revision => p_revision
,p_lot_number => p_lot_number
,p_subinventory_code => p_subinventory_code
,p_locator_id => p_locator_id
,p_onhand_source => p_onhand_source
,p_primary_quantity => p_primary_quantity
,p_quantity_type => p_quantity_type
,p_secondary_quantity => p_secondary_quantity
,x_qoh => x_qoh
,x_rqoh => x_rqoh
,x_qr => x_qr
,x_qs => x_qs
,x_att => x_att
,x_atr => x_atr
,x_sqoh => x_sqoh
,x_srqoh => x_srqoh
,x_sqr => x_sqr
,x_sqs => x_sqs
,x_satt => x_satt
,x_satr => x_satr
,p_transfer_subinventory_code => p_transfer_subinventory_code
,p_cost_group_id => p_cost_group_id
,p_lpn_id => p_lpn_id
,p_transfer_locator_id => p_transfer_locator_id
,p_containerized => p_containerized);
END update_quantities;
SELECT expiration_date
FROM mtl_lot_numbers
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND lot_number = p_lot_number;
PROCEDURE insert_txn_inter_hdr(p_mmti_rec IN mtl_transactions_interface%ROWTYPE,
x_return_status OUT NOCOPY VARCHAR2) IS
l_api_name CONSTANT VARCHAR2 (30) := 'insert_txn_inter_hdr';
INSERT INTO mtl_transactions_interface
(transaction_interface_id
,transaction_header_id
,source_code
,source_header_id
,lock_flag
,transaction_mode
,process_flag
,validation_required
,source_line_id
,transaction_source_id
,trx_source_line_id
,last_updated_by
,last_update_login
,last_update_date
,creation_date
,created_by
,inventory_item_id
,revision
,organization_id
,transaction_date
,transaction_type_id
,transaction_action_id
,transaction_quantity
,primary_quantity
,secondary_transaction_quantity
,secondary_uom_code
,transaction_uom
,subinventory_code
,locator_id
,transaction_source_type_id
,wip_entity_type
,transaction_source_name
,transaction_reference
,reason_id
,transaction_batch_id
,transaction_batch_seq
,reservation_quantity
,transaction_sequence_id
,transfer_lpn_id)
VALUES (p_mmti_rec.transaction_interface_id
,gme_common_pvt.g_transaction_header_id
,'OPM' -- source_code
,p_mmti_rec.transaction_source_id --source_header_id
,1 -- lock_flag
,2 -- transaction_mode
,1 -- (Yes) process_flag
,2 -- validation_required
, NVL (p_mmti_rec.source_line_id, -99)-- transaction_id for reversal
,p_mmti_rec.transaction_source_id -- batch id
,p_mmti_rec.trx_source_line_id -- material detail id
,gme_common_pvt.g_user_ident --last_updated_by
,gme_common_pvt.g_user_ident -- last_update_login
,gme_common_pvt.g_timestamp --last_update_date
,gme_common_pvt.g_timestamp --creation_date
,gme_common_pvt.g_user_ident --created_by
,p_mmti_rec.inventory_item_id -- inventory_item_id
,p_mmti_rec.revision
,p_mmti_rec.organization_id --organization_id
/* FPBug#4543872 rework
removed defaulting the transaction date
*/
,p_mmti_rec.transaction_date
,p_mmti_rec.transaction_type_id
, --(Batch Issue)transaction_type_id
p_mmti_rec.transaction_action_id
, --transaction_action_id
p_mmti_rec.transaction_quantity
, --transaction_quantity
p_mmti_rec.primary_quantity
, --primary_quantity
p_mmti_rec.secondary_transaction_quantity -- secondary_quantity
,p_mmti_rec.secondary_uom_code -- secondary_uom_code
, -- secondary_quantity
p_mmti_rec.transaction_uom, --transaction_uom
p_mmti_rec.subinventory_code
, --subinventory_code
p_mmti_rec.locator_id, --locator_id
gme_common_pvt.g_txn_source_type
, -- (Batch) transaction_source_type_id
gme_common_pvt.g_wip_entity_type_batch -- (for batch) wip_entity_type
,p_mmti_rec.transaction_source_name -- transaction_source_name
,p_mmti_rec.transaction_reference
,p_mmti_rec.reason_id
,p_mmti_rec.transaction_batch_id -- must populate for seq
,p_mmti_rec.transaction_batch_seq
,p_mmti_rec.reservation_quantity
,p_mmti_rec.transaction_sequence_id
,p_mmti_rec.transfer_lpn_id);
END insert_txn_inter_hdr;
SELECT *
FROM mtl_material_transactions mmt
WHERE transaction_id = v_transaction_id;
SELECT *
FROM mtl_transaction_lot_numbers
WHERE transaction_id = v_transaction_id;
SELECT expiration_date
FROM mtl_lot_numbers
WHERE organization_id = p_organization_id
AND inventory_item_id = p_inventory_item_id
AND lot_number = p_lot_number;
SELECT *
FROM mtl_system_items_b
WHERE inventory_item_id = v_item_id AND organization_id = v_org_id;
SELECT step_status
FROM gme_batch_steps s, gme_batch_step_items i
WHERE s.batchstep_id = i.batchstep_id
AND i.material_detail_id = v_matl_dtl_id;
IF l_batch_hdr_rec.update_inventory_ind = 'Y' THEN
IF p_mmti_rec.trx_source_line_id IS NOT NULL THEN
l_mat_dtl_rec.material_detail_id := p_mmti_rec.trx_source_line_id;
END IF; /* update_inventory_ind = 'Y' */