[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
REM * Modified insert_txn_inter_hdr to insert lpn_id , transfer_lpn_id into
REM * mtl_transactions_interface table
REM * Modiied delete_material_txn to populate lpn_id or transfer_lpn_id
REM * into mtl_transactions_interface based on the transaction type
REM * Swapna K Bug 7226474
REM * Modified build_txn_inter_lot to insert attribute columns into
REM * mtl_transactions_interface table
REM * Archana Mundhe Bug 7385309
REM * Modified procedure update_material_txn to create the transaction
REM * first and then delete.
REM * G. Muratore 24-Dec-2008 Bug 7626742/7423041
REM * Backout one piece of fix from 7385309 - Do not clear the cache.
REM * Procedure: query_quantities
REM * G. Muratore 29-Dec-2008 Bug 7623144
REM * Add 'C_', 'D_' and 'N_' lot attribute columns plus lot_attribute_category.
REM * Procedure: build_txn_inter_lot
REM * Kbanddyo 21-Jan-2009 Bug 7720970
REM * Procedure : process_transactions
REM * Swapna k 18-MAR-09 Bug 8300015
REM * Added p_phantom_line_id parameter to the get_mat_txns procedure.
REM * G. Muratore 26-MAY-2009 Bug 8453485
REM * Added dynamically derived column rev_order_column to help us in order by clause.
REM * This will aid in handling Product Yield reversals first for layer sequencing for GMF.
REM * Procedure: process_transactions
REM * Apeksha Mishra 21-Sep-2009 Bug 8605909
REM * Added the call to function gme_common_pvt.check_close_period to check whether
REM * the period is closed or not.
REM * Procedure: delete_material_txn
REM * G. Muratore 05-AUG-2009 Bug 8639523 (rework of 7385309 for ingreds)
REM * Resequence calls for transaction reversals depending on line_type.
REM * PROCEDURE: update_material_txn
REM * G. Muratore 01-Dec-2009 Bug 9170460
REM * Pass in subinventory and locator id to applicable function.
REM * PROCEDURE: build_txn_inter_lot
REM * G. Muratore 15-FEB-2010 Bug 9301755 (extension of 8639523/7385309)
rem * update_material_txn is an overloaded function so we need to make same fix again.
REM * Resequence calls for transaction reversals depending on line_type.
REM * PROCEDURE: update_material_txn
REM * G. Muratore 19-MAR-2010 Bug 8751983
REM * Added p_order_by parameter to allow fetching of transactions in reverse trans order.
REM * PROCEDURE: get_mat_trans
REM * G. Muratore 10-JUN-2010 Bug 9770408 / 9626176
REM * Evaluate the non divisible flag for a product yield being modified. This will be used to sequence
REM * the calls for deleting/creating transactions. Also to set the force the INV trans engine
REM * to process data in a desired order we now set transaction_batch_seq column in the temp table.
REM * PROCEDURE: build_txn_inter_hdr and update_material_txn
REM * Change for 9626176 will now allow yielding an auto rel prod in a wip batch to match the form.
REM * PROCEDURE: pre_process_val and gmo_pre_process_val
REM * S. Kommineni 10-JUN-2010 Bug 9717803 (Included with patch 9770408)
REM * Insert into transaction pairs table prior to creating new transaction.
REM * This is to facilitate reversing yields for non divisible items.
REM * PROCEDURE: delete_material_txn
REM * G. Muratore 06-MAY-2011 Bug 12391271
REM * Initialize primary_quantity when necessary. Also pass lpn_id value to build lot inter function.
REM * lpn_id will now be passed into status_applicable function for a more accurate evaluation.
REM * PROCEDURE: build_txn_inter and build_txn_inter_lot
REM * G. Muratore 06-MAY-2011 Bug 12881196
REM * Add missing attribute columns so that they get saved.
REM * PROCEDURE: insert_txn_inter_hdr
REM * G. Muratore 06-MAY-2011 Bug 12836004
REM * Per recommendation from INV team, pass in p_validation_level as g_valid_level_none
REM * when calling inv_txn_manager_grp.validate_transactions
REM * PROCEDURE: create_material_txn
REM * G. Muratore 12-APR-2012 Bug 13925279
REM * Assign the user entered expiration date.
REM * PROCEDURE: build_txn_inter
REM * A. Mishra 26-APR-2012 Bug 13835011
REM * Update the grade code in MTLT OR MTLI.
REM * Procedure: process_transactions
REM * G. Muratore 15-MAY-2012 Bug 14065291
REM * Initialize secondary_quantity when necessary.
REM * PROCEDURE: build_txn_inter.
REM * A. Mishra 10-Jul-2012 Bug 14297117
REM * Update the supplier lot in MTLT OR MTLI.
REM * Procedure: process_transactions
REM * G. Muratore 06-MAY-2011 Bug 14461780 - Back out 12836004
REM * Per recommendation from INV team, pass in p_validation_level as g_valid_level_full
REM * when calling inv_txn_manager_grp.validate_transactions. We need this so that
REM * expiration and origination dates are calculated centrally by INV code.
REM * PROCEDURE: create_material_txn
REM * Abhay Satpute 15 Nov 2012 Bug 15879394 Removed check requiring lot
REM * to exist to be used in ingredient issue transaction
REM * G. Muratore 06-DEC-2012 Bug 14685438
REM * Limited message size to size of database field.
REM * PROCEDURE: gme_txn_message
REM * G. Muratore 04-FEB-2013 Bug 16079842 - Rework 13835011 and 14297117.
REM * Use grade_code and supplier lot number from the passed in record.
REM * PROCEDURE: build_txn_inter_lot and process_transactions
REM **********************************************************************
*/
/* +==========================================================================+
| PROCEDURE NAME
| create_material_txn
|
| USAGE
| 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);
SELECT SUBINVENTORY_CODE,
LOCATOR_ID,
LPN_CONTEXT,
LICENSE_PLATE_NUMBER
INTO x_out_subinv,
x_out_locId,
x_context,
x_out_lpnno
FROM WMS_LICENSE_PLATE_NUMBERS
WHERE lpn_id = p_lpn_id;
| 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
| Bug 7385309 Archana Mundhe
| Create a new transaction before deleting existing one.
|
| G. Muratore 15-FEB-2010 Bug 9301755
| Resequence calls for reversals depending on line_type. This is an
| extension of 8639523/7385309 which dealt with product yields only.
| update_material_txn is an overloaded function so we need to make same fix here.
+==========================================================================+ */
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;
SELECT d.line_type, NVL( lot_divisible_flag, 'Y')
FROM mtl_material_transactions t, gme_material_details d, mtl_system_items_b i
WHERE t.transaction_source_type_id = 5
AND t.transaction_id = v_transaction_id
AND d.batch_id = t.transaction_source_id
AND d.material_detail_id = t.trx_source_line_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id;
l_delete_done NUMBER;
l_delete_done := 0;
|| 'calling delete transaction for transaction id'
|| p_transaction_id);
delete_material_txn (p_transaction_id => p_transaction_id
-- 8605909 updated the delete material transaction with the trans date parameter
,p_trans_date => l_mmti_rec.transaction_date
,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;
l_delete_done := 1;
|| 'calling create in update with :'
|| l_mmti_rec.transaction_interface_id);
IF l_delete_done = 1 THEN
l_mmti_rec.transaction_batch_seq := 101;
IF l_delete_done = 0 THEN
-- call to delete all the transactions for this transaction_id
IF (g_debug <= gme_debug.g_log_statement) THEN
gme_debug.put_line
( g_pkg_name
|| '.'
|| l_api_name
|| ':'
|| 'calling delete transaction for transaction id'
|| p_transaction_id);
delete_material_txn (p_transaction_id => p_transaction_id
-- 8605909 updated the delete material transaction with the trans date parameter
,p_trans_date => l_mmti_rec.transaction_date
,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;
| 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
|
| Bug 7385309 Archana Mundhe
| Create a new transaction before deleting existing one.
|
| G. Muratore 05-AUG-2009 Bug 8639523
| Resequence calls for reversals depending on line_type. This is a rework
| of 7385309 which dealt with product yields only. This keeps that fix
| in place for products and byproducts.
+==========================================================================+ */
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;
SELECT d.line_type, NVL( lot_divisible_flag, 'Y')
FROM mtl_material_transactions t, gme_material_details d, mtl_system_items_b i
WHERE t.transaction_source_type_id = 5
AND t.transaction_id = v_transaction_id
AND d.batch_id = t.transaction_source_id
AND d.material_detail_id = t.trx_source_line_id
AND d.inventory_item_id = i.inventory_item_id
AND d.organization_id = i.organization_id;
l_delete_done NUMBER;
l_delete_done := 0;
|| 'calling delete transaction for transaction id'
|| l_transaction_id);
delete_material_txn (p_transaction_id => l_transaction_id
--8605909 updated the delete material transaction with the trans date parameter
,p_trans_date => l_mmt_rec.transaction_date
,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;
l_delete_done := 1;
IF l_delete_done = 1 THEN
l_mmti_rec.transaction_batch_seq := 101;
IF l_delete_done = 0 THEN
-- call to delete all the transactions for this transaction_id
IF (g_debug <= gme_debug.g_log_statement) THEN
gme_debug.put_line
( g_pkg_name
|| '.'
|| l_api_name
|| ':'
|| 'calling delete transaction for transaction id'
|| l_transaction_id);
delete_material_txn (p_transaction_id => l_transaction_id
--8605909 updated the delete material transaction with the trans date parameter
,p_trans_date => l_mmt_rec.transaction_date
,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
|
| A. Mishra 03-Sep-2009 Bug 8605909
| Added p_trans_date parameter to be potentially be used on reversals
| where original transaction is now in a closed period.
+==========================================================================+ */
PROCEDURE delete_material_txn (
p_transaction_id IN NUMBER
,p_txns_pair IN NUMBER DEFAULT NULL
,p_trans_date IN DATE 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;
/*Bug#8453427 Added the delete call for the phantom transactions for the product return transactions, as the
corresponsing phantom transctions would be of the production completion types and always +ve sign transactions should be
created first */
IF (l_mmti_rec.transaction_type_id = gme_common_pvt.g_prod_return OR
l_mmti_rec.transaction_type_id = gme_common_pvt.g_byprod_return ) THEN
IF l_mat_dtl_rec.phantom_line_id IS NOT NULL AND p_txns_pair IS NULL THEN
IF (g_debug <= gme_debug.g_log_statement) THEN
gme_debug.put_line ( g_pkg_name
|| '.'
|| l_api_name
|| ':'
|| 'deleting for phantom:'
|| l_mat_dtl_rec.phantom_line_id);
|| 'calling delete txns for phantom:'
|| m_transaction_id);
delete_material_txn (p_transaction_id => m_transaction_id
,p_txns_pair => 1
,p_trans_date => l_trans_date
,x_return_status => l_return_status);
RAISE delete_material_txn_err;
|| '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
,p_trans_date => l_trans_date
,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
|
| G. Muratore 06-MAY-2011 Bug 12391271
| Initialize primary_quantity when necessary.
| Also pass lpn_id value to build lot inter function.
|
| G. Muratore 12-APR-2012 Bug 13925279
| Assign the user entered expiration date.
|
| G. Muratore 15-MAY-2012 Bug 14065291
| Initialize secondary_quantity when necessary.
+==========================================================================+ */
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 ;
SELECT i.primary_uom_code, k.concatenated_segments, i.secondary_uom_code
FROM mtl_system_items_b i, mtl_system_items_kfv k
WHERE v_inventory_item_id = i.inventory_item_id
AND v_organization_id = i.organization_id
AND v_inventory_item_id = k.inventory_item_id
AND v_organization_id = k.organization_id;
l_insert_hdr := FALSE;
l_insert_hdr := TRUE;
,p_insert_hdr => l_insert_hdr);
|| 'after header- inserting lot');
UPDATE mtl_transaction_lots_interface
SET lot_expiration_date = l_mmli_tbl(i).lot_expiration_date
WHERE transaction_interface_id = x_mmti_rec.transaction_interface_id;
/* 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
|
| G. Muratore 29-Dec-08 Bug 7623144 - add all missing lot attribute columns
| 'C_', 'D_' and 'N_' attribute columns plus lot_attribute_category.
|
| G. Muratore 01-Dec-09 Bug 9170460
| Pass in subinventory and locator id to applicable function.
|
| G. Muratore 06-MAY-2011 Bug 12391271
| Added lpn_id parameter so we can pass it to status_applicable call.
|
| G. Muratore 04-FEB-2013 Bug 16079842 - Rework 13835011 and 14297117.
| Use grade_code and supplier lot number from the passed in record.
+==========================================================================+ */
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_lpn_id IN NUMBER DEFAULT NULL
,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,parent_lot_number
,lot_number, transaction_quantity
,primary_quantity
,secondary_transaction_quantity
,grade_code -- Bug 16079842
,supplier_lot_number -- Bug 16079842
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute_category -- ); -- Bug 7623144 Added additional missing columns here for lot attributes.
,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.parent_lot_number --parent lot_number
/*Bug#7372673*/
,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
,l_mmli_rec.grade_code -- Bug 16079842
,l_mmli_rec.supplier_lot_number -- Bug 16079842
,l_mmli_rec.attribute1
,l_mmli_rec.attribute2
,l_mmli_rec.attribute3
,l_mmli_rec.attribute4
,l_mmli_rec.attribute5
,l_mmli_rec.attribute6
,l_mmli_rec.attribute7
,l_mmli_rec.attribute8
,l_mmli_rec.attribute9
,l_mmli_rec.attribute10
,l_mmli_rec.attribute11
,l_mmli_rec.attribute12
,l_mmli_rec.attribute13
,l_mmli_rec.attribute14
,l_mmli_rec.attribute15
,l_mmli_rec.attribute_category -- ); -- Bug 7623144 Added aditional missing columns here for lot attributes.
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 CASE p_order_by
when 1 then Row_Number() over(order by transaction_quantity)
when 2 then Row_Number() over(order by transaction_id DESC)
END;
SELECT * FROM
( 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)
UNION ALL
SELECT *
FROM mtl_material_transactions mmt
WHERE trx_source_line_id = v_phantom_line_id
AND transaction_source_id = v_phantom_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 CASE p_order_by
when 1 then Row_Number() over(order by transaction_quantity)
when 2 then Row_Number() over(order by transaction_id DESC)
END;
SELECT batch_id INTO p_phantom_batch_id
FROM gme_material_details
WHERE material_detail_id = p_phantom_line_id;
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,
v_lot_number,
v_organization_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 = SUBSTR(NVL (x_message_list, l_errm), 1, 240)
,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 8300015 Changed the logic of updating the phantom transactions.
| Bug back port 6997483 Srinivasulu Puri 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 ,t.transaction_quantity
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);
/* Added the below loop to update the transaction only if it matches with the existing transactions
and with the qty and opposite sign */
FOR l_gme_pairs_rec_upd in (select * from gme_transaction_pairs where transaction_id2 = l_transaction_reference)
LOOP
SELECT transaction_quantity INTO l_transaction_quantity_upd
FROM mtl_material_transactions t
WHERE t.transaction_id = l_gme_pairs_rec_upd.transaction_id1;
UPDATE gme_transaction_pairs
SET transaction_id2 = l_transaction_id
WHERE transaction_id1 = l_gme_pairs_rec_upd.transaction_id1
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;
| Update the grade code in MTLT OR MTLI
|
| 04-FEB-2013 G. Muratore Bug 16079842
| Backout fixes done for 13835011 and 14297117.
+==========================================================================+ */
/* Bug 5255959 added p_clear_qty_cache parameter */
PROCEDURE process_transactions (
p_api_version IN NUMBER := 1
,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
,p_commit IN VARCHAR2 := fnd_api.g_false
,p_validation_level IN NUMBER
:= fnd_api.g_valid_level_full
,p_table IN NUMBER := 2
,p_header_id IN NUMBER
:= gme_common_pvt.get_txn_header_id
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_trans_count OUT NOCOPY NUMBER
--Bug#5584699 Changed variable from boolean to varchar2
,p_clear_qty_cache IN VARCHAR2 := fnd_api.g_true)
--,p_clear_qty_cache IN BOOLEAN DEFAULT TRUE)
IS
--bug 7720970 kbanddyo added join error_explanation IS NOT NULL for both the cursors below
CURSOR get_error_int
IS
SELECT ERROR_CODE, error_explanation
FROM mtl_transactions_interface
WHERE transaction_header_id =gme_common_pvt.g_transaction_header_id
AND error_explanation IS NOT NULL;
SELECT ERROR_CODE, error_explanation
FROM mtl_material_transactions_temp
WHERE transaction_header_id =gme_common_pvt.g_transaction_header_id
AND error_explanation IS NOT NULL;
Select sum(cnt)
INTO int_rec_count
FROM (
SELECT COUNT (*) cnt
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_header_id
AND process_flag = 'Y'
UNION ALL
SELECT COUNT (*) cnt
FROM mtl_transactions_interface
WHERE transaction_header_id = p_header_id
AND process_flag = 1
);
(SELECT transaction_temp_id, inventory_item_id, organization_id
FROM mtl_material_transactions_temp
WHERE transaction_header_id = p_header_id
AND process_flag = 'Y') LOOP
UPDATE mtl_transaction_lots_temp mtlt
SET (grade_code, supplier_lot_number) =
(SELECT grade_code, supplier_lot_number
FROM mtl_lot_numbers mln
WHERE mln.lot_number = mtlt.lot_number
AND mln.organization_id = mmtt_rec_cur.organization_id
AND inventory_item_id = mmtt_rec_cur.inventory_item_id)
WHERE transaction_temp_id = mmtt_rec_cur.transaction_temp_id
AND (grade_code is null OR supplier_lot_number IS NULL);
(SELECT transaction_interface_id, inventory_item_id, organization_id
FROM mtl_Transactions_interface
WHERE transaction_header_id = p_header_id
AND process_flag = 1) LOOP
UPDATE mtl_transaction_lots_interface mti
SET (grade_code, supplier_lot_number) =
(SELECT grade_code, supplier_lot_number
FROM mtl_lot_numbers mln
WHERE mln.lot_number = mti.lot_number
AND mln.organization_id = mti_rec_cur.organization_id
AND inventory_item_id = mti_rec_cur.inventory_item_id)
WHERE transaction_interface_id = mti_rec_cur.transaction_interface_id
AND (grade_code is null OR supplier_lot_number IS NULL);
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
, mmt.transaction_date
, mmt.primary_quantity as primary_quantity /* Changed for Bug 8347011 base bug 8219507 */
--nvl(mtln.primary_quantity, mmt.primary_quantity) as primary_quantity
, msi.primary_uom_code
,mmt.transaction_quantity as transaction_quantity /* Changed for Bug 8347011 base bug 8219507 */
--, 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
, decode(NVL(gtp.transaction_id2, 0), 0, mmt.transaction_id + 999, mmt.transaction_id) as rev_order_column
, 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_material_transactions mmt, /* Removed mtln for Bug 8347011 base bug 8219507 */
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 /*Commented for Bug 8347011*/
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*/
AND gtp.pair_type (+) = 1 /*BUG 6335682 */
ORDER BY mmt.transaction_date,
case md.line_type
when -1 then 0
when 2 then 1
when 1 then 2
end,
md.material_detail_id,
case md.line_type
when -1 then mmt.transaction_id
when 2 then rev_order_column
when 1 then rev_order_column
--mmt.transaction_id, mtln.lot_number) LOOP
end) LOOP
-- mmt.transaction_id, 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
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,attribute_category -- Bug 12881196 Add missing attribute columns so that they get saved
,transfer_lpn_id
,lpn_id) -- Bug 6437252 LPN support
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.attribute1
,p_mmti_rec.attribute2
,p_mmti_rec.attribute3
,p_mmti_rec.attribute4
,p_mmti_rec.attribute5
,p_mmti_rec.attribute6
,p_mmti_rec.attribute7
,p_mmti_rec.attribute8
,p_mmti_rec.attribute9
,p_mmti_rec.attribute10
,p_mmti_rec.attribute11
,p_mmti_rec.attribute12
,p_mmti_rec.attribute13
,p_mmti_rec.attribute14
,p_mmti_rec.attribute15
,p_mmti_rec.attribute_category -- Bug 12881196 Add missing attribute columns so that they get saved.
,p_mmti_rec.transfer_lpn_id
,p_mmti_rec.lpn_id); -- Bug 6437252 LPN support
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' */