[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| inserting transactions, creating lots in ODM and updating balances |
| in OPM inventory and Oracle Inventory. |
| |
| Contents |
| create_txn_update_balances |
| create_txn_update_bal_in_opm |
| complete_transaction_in_opm |
| create_txn_update_bal_in_odm |
| |
| HISTORY |
| Created - Jalaj Srivastava |
| |
| |
+==========================================================================+
*/
PROCEDURE log_msg(p_msg_text IN VARCHAR2);
| create_txn_update_balances |
| |
| TYPE |
| Private |
| |
| USAGE |
| Sets up and posts transactions and updates balances in OPM inventory |
| and Oracle Inventory. | |
| |
| RETURNS |
| Via x_ OUT parameters |
| |
| HISTORY |
| Created Jalaj Srivastava |
| |
+==========================================================================+ */
PROCEDURE create_txn_update_balances
( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
, p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
, p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_transfer_id IN NUMBER
, p_line_id IN NUMBER
, x_transaction_header_id IN OUT NOCOPY NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'create_txn_update_balances' ;
SELECT *
FROM gmi_discrete_transfer_lots
WHERE transfer_id = Vtransfer_id
AND line_id = Vline_id;
log_msg('Beginning of procedure create_txn_update_balances');
SELECT *
INTO l_hdr_row
FROM gmi_discrete_transfers
WHERE transfer_id = p_transfer_id;
log_msg('After selecting header row from database. transfer id is '||to_char(l_hdr_row.transfer_id));
SELECT *
INTO l_line_row
FROM gmi_discrete_transfer_lines
WHERE transfer_id = p_transfer_id
AND line_id = p_line_id;
log_msg('After selecting line row from database. line id is '||to_char(l_line_row.line_id));
log_msg('After selecting lot rows from database. no of lots is '||to_char(l_lot_row_tbl.count));
log_msg('Failed call to inv_validate.transaction_type in procedure create_txn_update_balances');
SELECT item_um,item_um2,
lot_ctl,lot_indivisible
INTO l_txn_vars_rec.opm_item_um, l_txn_vars_rec.opm_item_um2,
l_txn_vars_rec.lot_control, l_txn_vars_rec.opm_lot_indivisible
FROM ic_item_mst_b
WHERE item_id = l_line_row.opm_item_id;
log_msg('before calling create_txn_update_bal_in_opm');
create_txn_update_bal_in_opm
(
p_api_version => p_api_version
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_validation_level => p_validation_level
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_hdr_row => l_hdr_row
, p_line_row => l_line_row
, p_lot_row_tbl => l_lot_row_tbl
, p_txn_vars_rec => l_txn_vars_rec
);
log_msg('After call to procedure create_txn_update_balances_in_opm. return status is '||x_return_status);
log_msg('before calling create_txn_update_bal_in_odm');
create_txn_update_bal_in_odm
(
p_api_version => p_api_version
, p_init_msg_list => FND_API.G_FALSE
, p_commit => FND_API.G_FALSE
, p_validation_level => p_validation_level
, x_return_status => x_return_status
, x_msg_count => x_msg_count
, x_msg_data => x_msg_data
, p_hdr_row => l_hdr_row
, p_line_row => l_line_row
, p_lot_row_tbl => l_lot_row_tbl
, p_txn_vars_rec => l_txn_vars_rec
, p_odm_txn_type_rec => l_odm_txn_type_rec
, x_transaction_header_id => x_transaction_header_id
);
log_msg('After call to procedure create_txn_update_balances_in_odm. return status is '||x_return_status||' transaction header id is '||to_char(x_transaction_header_id));
END create_txn_update_balances;
| create_txn_update_bal_in_opm |
| |
| TYPE |
| Private |
| |
| USAGE |
| Sets up and posts transactions and updates balances in OPM inventory |
| Does a final check to prevent OPM inventory balances from going |
| negative and calls the OPM inventory engine to post the transaction |
| |
| RETURNS |
| Via x_ OUT parameters |
| |
| HISTORY |
| Created Jalaj Srivastava |
| |
+==========================================================================+ */
PROCEDURE create_txn_update_bal_in_opm
( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
, p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
, p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_hdr_row IN gmi_discrete_transfers%ROWTYPE
, p_line_row IN gmi_discrete_transfer_lines%ROWTYPE
, p_lot_row_tbl IN GMIVDX.lot_row_tbl
, p_txn_vars_rec IN txn_vars_type
)IS
l_api_name CONSTANT VARCHAR2(30) := 'create_txn_update_bal_in_opm' ;
IF GMIVDBL.ic_loct_inv_select(l_ic_loct_inv_row, l_ic_loct_inv_row) THEN
IF ( (p_txn_vars_rec.opm_lot_indivisible = 1)
AND (l_ic_loct_inv_row.loct_onhand <> abs(l_tran_rec_tbl(i).trans_qty))
) THEN
FND_MESSAGE.SET_NAME('GMI','GMI_DXFR_INDIVISIBLE_LOT');
END create_txn_update_bal_in_opm;
| create_txn_update_bal_in_odm |
| |
| TYPE |
| Private |
| |
| USAGE |
| Sets up and posts transactions and updates balances in OPM inventory |
| |
| RETURNS |
| Via x_ OUT parameters |
| |
| HISTORY |
| Created Jalaj Srivastava |
| |
+==========================================================================+ */
PROCEDURE create_txn_update_bal_in_odm
( p_api_version IN NUMBER
, p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE
, p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
, p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL
, x_return_status OUT NOCOPY VARCHAR2
, x_msg_count OUT NOCOPY NUMBER
, x_msg_data OUT NOCOPY VARCHAR2
, p_hdr_row IN gmi_discrete_transfers%ROWTYPE
, p_line_row IN gmi_discrete_transfer_lines%ROWTYPE
, p_lot_row_tbl IN GMIVDX.lot_row_tbl
, p_txn_vars_rec IN txn_vars_type
, p_odm_txn_type_rec IN inv_validate.transaction
, x_transaction_header_id IN OUT NOCOPY NUMBER
) IS
l_api_name CONSTANT VARCHAR2(30) := 'create_txn_update_bal_in_odm' ;
SELECT uom_code
FROM sy_uoms_mst
WHERE um_code = Vum_code;
SELECT mtl_material_transactions_s.NEXTVAL
INTO x_transaction_header_id
FROM DUAL;
SELECT mtl_material_transactions_s.NEXTVAL
INTO l_mmtt_row.transaction_temp_id
FROM DUAL;
SELECT secondary_uom_code
INTO l_mmtt_row.secondary_uom_code
FROM mtl_system_items_b
WHERE inventory_item_id = l_mmtt_row.inventory_item_id
AND organization_id = l_mmtt_row.organization_id;
l_mmtt_row.last_update_date := SYSDATE;
l_mmtt_row.last_updated_by := FND_GLOBAL.USER_ID;
l_mmtt_row.last_update_login := FND_GLOBAL.LOGIN_ID;
INSERT INTO mtl_material_transactions_temp
(
TRANSACTION_HEADER_ID,
TRANSACTION_TEMP_ID,
TRANSACTION_MODE,
LOCK_FLAG,
INVENTORY_ITEM_ID,
REVISION,
ORGANIZATION_ID,
SUBINVENTORY_CODE,
LOCATOR_ID,
TRANSACTION_QUANTITY,
PRIMARY_QUANTITY,
SECONDARY_TRANSACTION_QUANTITY,
TRANSACTION_UOM,
SECONDARY_UOM_CODE,
TRANSACTION_TYPE_ID,
TRANSACTION_ACTION_ID,
TRANSACTION_SOURCE_TYPE_ID,
TRANSACTION_SOURCE_NAME,
TRANSACTION_DATE,
ACCT_PERIOD_ID,
DISTRIBUTION_ACCOUNT_ID,
TRANSACTION_REFERENCE,
POSTING_FLAG,
PROCESS_FLAG,
FINAL_COMPLETION_FLAG,
TRANSACTION_LINE_NUMBER,
REASON_ID,
TRANSACTION_COST,
SOURCE_CODE,
SOURCE_LINE_ID,
TRANSACTION_SOURCE_ID,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN
)
VALUES
(
l_mmtt_row.transaction_header_id,
l_mmtt_row.transaction_temp_id,
l_mmtt_row.transaction_mode,
l_mmtt_row.lock_flag,
l_mmtt_row.inventory_item_id,
l_mmtt_row.revision,
l_mmtt_row.organization_id,
l_mmtt_row.subinventory_code,
l_mmtt_row.locator_id,
l_mmtt_row.transaction_quantity,
l_mmtt_row.primary_quantity,
l_mmtt_row.secondary_transaction_quantity,
l_mmtt_row.transaction_uom,
l_mmtt_row.secondary_uom_code,
l_mmtt_row.transaction_type_id,
l_mmtt_row.transaction_action_id,
l_mmtt_row.transaction_source_type_id,
l_mmtt_row.transaction_source_name,
l_mmtt_row.transaction_date,
l_mmtt_row.acct_period_id,
l_mmtt_row.distribution_account_id,
l_mmtt_row.transaction_reference,
l_mmtt_row.posting_flag,
l_mmtt_row.process_flag,
l_mmtt_row.final_completion_flag,
l_mmtt_row.transaction_line_number,
l_mmtt_row.reason_id,
l_mmtt_row.transaction_cost,
l_mmtt_row.source_code,
l_mmtt_row.source_line_id,
l_mmtt_row.transaction_source_id,
l_mmtt_row.creation_date,
l_mmtt_row.created_by,
l_mmtt_row.last_update_date,
l_mmtt_row.last_updated_by,
l_mmtt_row.last_update_login
);
log_msg('Inserted 1 record in mtl_material_transactions_temp');
l_mtlt_row_tbl(i).last_update_date := SYSDATE;
l_mtlt_row_tbl(i).last_updated_by := FND_GLOBAL.USER_ID;
l_mtlt_row_tbl(i).last_update_login := FND_GLOBAL.LOGIN_ID;
INSERT INTO MTL_TRANSACTION_LOTS_TEMP
(
transaction_temp_id
,group_header_id
,transaction_quantity
,primary_quantity
,secondary_quantity
,lot_number
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login
,lot_expiration_date
,status_id
,lot_attribute_category
,attribute_category
,attribute1
,attribute2
,attribute3
,attribute4
,attribute5
,attribute6
,attribute7
,attribute8
,attribute9
,attribute10
,attribute11
,attribute12
,attribute13
,attribute14
,attribute15
,c_attribute1
,c_attribute2
,c_attribute3
,c_attribute4
,c_attribute5
,c_attribute6
,c_attribute7
,c_attribute8
,c_attribute9
,c_attribute10
,c_attribute11
,c_attribute12
,c_attribute13
,c_attribute14
,c_attribute15
,c_attribute16
,c_attribute17
,c_attribute18
,c_attribute19
,c_attribute20
,n_attribute1
,n_attribute2
,n_attribute3
,n_attribute4
,n_attribute5
,n_attribute6
,n_attribute7
,n_attribute8
,n_attribute9
,n_attribute10
,d_attribute1
,d_attribute2
,d_attribute3
,d_attribute4
,d_attribute5
,d_attribute6
,d_attribute7
,d_attribute8
,d_attribute9
,d_attribute10
,grade_code
,origination_date
,date_code
,change_date
,age
,retest_date
,maturity_date
,item_size
,color
,volume
,volume_uom
,place_of_origin
,best_by_date
,length
,length_uom
,recycled_content
,thickness
,thickness_uom
,width
,width_uom
,territory_code
,supplier_lot_number
,vendor_name
,parent_lot_number
,origination_type
,expiration_action_code
,expiration_action_date
,hold_date
,reason_id
)
SELECT
l_mtlt_row_tbl(i).transaction_temp_id
,l_mtlt_row_tbl(i).group_header_id
,l_mtlt_row_tbl(i).transaction_quantity
,l_mtlt_row_tbl(i).primary_quantity
,l_mtlt_row_tbl(i).secondary_quantity
,l_mtlt_row_tbl(i).lot_number
,l_mtlt_row_tbl(i).creation_date
,l_mtlt_row_tbl(i).created_by
,l_mtlt_row_tbl(i).last_update_date
,l_mtlt_row_tbl(i).last_updated_by
,l_mtlt_row_tbl(i).last_update_login
,mln.expiration_date
,mln.status_id
,mln.lot_attribute_category
,mln.attribute_category
,mln.attribute1
,mln.attribute2
,mln.attribute3
,mln.attribute4
,mln.attribute5
,mln.attribute6
,mln.attribute7
,mln.attribute8
,mln.attribute9
,mln.attribute10
,mln.attribute11
,mln.attribute12
,mln.attribute13
,mln.attribute14
,mln.attribute15
,mln.c_attribute1
,mln.c_attribute2
,mln.c_attribute3
,mln.c_attribute4
,mln.c_attribute5
,mln.c_attribute6
,mln.c_attribute7
,mln.c_attribute8
,mln.c_attribute9
,mln.c_attribute10
,mln.c_attribute11
,mln.c_attribute12
,mln.c_attribute13
,mln.c_attribute14
,mln.c_attribute15
,mln.c_attribute16
,mln.c_attribute17
,mln.c_attribute18
,mln.c_attribute19
,mln.c_attribute20
,mln.n_attribute1
,mln.n_attribute2
,mln.n_attribute3
,mln.n_attribute4
,mln.n_attribute5
,mln.n_attribute6
,mln.n_attribute7
,mln.n_attribute8
,mln.n_attribute9
,mln.n_attribute10
,mln.d_attribute1
,mln.d_attribute2
,mln.d_attribute3
,mln.d_attribute4
,mln.d_attribute5
,mln.d_attribute6
,mln.d_attribute7
,mln.d_attribute8
,mln.d_attribute9
,mln.d_attribute10
,mln.grade_code
,mln.origination_date
,mln.date_code
,mln.change_date
,mln.age
,mln.retest_date
,mln.maturity_date
,mln.item_size
,mln.color
,mln.volume
,mln.volume_uom
,mln.place_of_origin
,mln.best_by_date
,mln.length
,mln.length_uom
,mln.recycled_content
,mln.thickness
,mln.thickness_uom
,mln.width
,mln.width_uom
,mln.territory_code
,mln.supplier_lot_number
,mln.vendor_name
,mln.parent_lot_number
,mln.origination_type
,mln.expiration_action_code
,mln.expiration_action_date
,mln.hold_date
,l_mmtt_row.reason_id
FROM
mtl_lot_numbers mln
WHERE
mln.inventory_item_id = l_mmtt_row.inventory_item_id
and mln.organization_id = l_mmtt_row.organization_id
and mln.lot_number = l_mtlt_row_tbl(i).lot_number;
log_msg('Inserted '||to_char(l_mtlt_row_tbl.count)||' lot records for the line in mtl_transactions_lots_temp');
END create_txn_update_bal_in_odm;