[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT d.assignment_type, o.co_code
FROM sy_docs_seq d, sy_orgn_mst o
WHERE o.orgn_code = Vorgn_code
AND o.delete_mark = 0
AND d.orgn_code = o.orgn_code
AND d.doc_type = 'DXFR'
AND d.delete_mark = 0;
SELECT p.co_code, p.sob_id, w.orgn_code, p.base_currency_code
FROM gl_plcy_mst p,
ic_whse_mst w,
sy_orgn_mst o
WHERE w.whse_code = Vwhse_code
AND o.orgn_code = w.orgn_code
AND p.co_code = o.co_code;
SELECT o.organization_code, o.set_of_books_id sob_id
FROM org_organization_definitions o,
hr_all_organization_units h
WHERE o.organization_id = Vorganization_id
AND h.organization_id = o.organization_id
AND sysdate between nvl(h.date_from, sysdate) and nvl(h.date_to,sysdate);
SELECT count(1)
FROM gmi_discrete_transfers
WHERE transfer_number = Vtransfer_number
AND orgn_code = Vorgn_code;
SELECT uom_code
FROM sy_uoms_mst
WHERE um_code = Vum_code;
SELECT iim.gl_class
FROM ic_item_mst_b iim, ic_gled_cls igc
WHERE iim.item_id = p_item_id
AND iim.gl_class = igc.icgl_class;
SELECT gic.item_id, gcs.opm_class, gic.category_id, kfv.CONCATENATED_SEGMENTS,
mcv.description
FROM mtl_categories_vl mcv, mtl_categories_b_kfv kfv, gmi_category_sets gcs, gmi_item_categories gic
WHERE gcs.category_set_id IS NOT NULL
AND gic.item_id = p_item
AND gcs.opm_class IN ('GL_BUSINESS_CLASS', 'GL_PRODUCT_LINE')
AND gcs.category_set_id = gic.category_set_id
AND kfv.category_id = gic.category_id
AND mcv.category_id = gic.category_id
ORDER BY gic.item_id, gcs.opm_class ;
IF ( GMIVDBL.ic_item_mst_select(x_ic_item_mst_row, x_ic_item_mst_row) ) THEN
IF (x_ic_item_mst_row.noninv_ind = 1) THEN
FND_MESSAGE.SET_NAME('GMI','IC_API_INVALID_ITEM_NO');
SELECT count(1)
INTO l_count
FROM mtl_item_revisions
WHERE inventory_item_id = l_item.inventory_item_id
AND organization_id = l_org.organization_id
AND revision = p_line_rec_tbl(i).odm_item_revision
AND implementation_date IS NOT NULL;
IF ( GMIVDBL.ic_whse_mst_select(x_ic_whse_mst_row, x_ic_whse_mst_row) ) THEN
NULL;
IF GMIVDBL.sy_reas_cds_select(x_sy_reas_cds_row, x_sy_reas_cds_row) THEN
IF (x_sy_reas_cds_row.reason_type = 1 AND p_hdr_rec.transfer_type = 0) THEN
FND_MESSAGE.SET_NAME('GMI','IC_REASONTYPEINCREASE');
SELECT count(1) INTO l_count
FROM ic_loct_mst
WHERE whse_code = p_line_rec_tbl(i).opm_whse_code
AND location = p_line_rec_tbl(i).opm_location
AND location <> GMIGUTL.IC$DEFAULT_LOCT;
SELECT count(1) INTO l_count
FROM ic_loct_inv
WHERE whse_code = p_line_rec_tbl(i).opm_whse_code
AND location = p_line_rec_tbl(i).opm_location
AND location <> GMIGUTL.IC$DEFAULT_LOCT;
SELECT * INTO x_ic_lots_mst_row
FROM ic_lots_mst
WHERE item_id = p_line_rec_tbl(i).opm_item_id
AND lot_id = p_lot_rec_tbl(j).opm_lot_id;
IF GMIVDBL.ic_loct_inv_select(x_ic_loct_inv_row, x_ic_loct_inv_row) THEN
--store quantities in opm item UOM and ODM item UOM
p_lot_rec_tbl(j).opm_lot_status := x_ic_loct_inv_row.lot_status;
SELECT *
INTO x_ic_lots_mst_row
FROM ic_lots_mst
WHERE ITEM_ID = x_ic_item_mst_row.item_id
AND lot_id = p_lot_rec_tbl(j).opm_lot_id;
IF GMIVDBL.ic_loct_inv_select(x_ic_loct_inv_row, x_ic_loct_inv_row) THEN
p_lot_rec_tbl(j).opm_lot_status := x_ic_loct_inv_row.lot_status;
log_msg('Failed call to GMIVDBL.ic_loct_inv_select when transfer type is 1. Not an error.');
p_lot_rec_tbl.DELETE(l_lot_rec_count);
| It would created transactions and update balances in OPM inventory |
| and Oracle Inventory |
| |
| |
| RETURNS |
| Via x_ OUT parameters |
| |
| HISTORY |
| Created Jalaj Srivastava |
| RLNAGARA Material Status Migration ME - Updating Status in MOQD |
| |
+==========================================================================+ */
PROCEDURE construct_post_records
( 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_rec IN OUT NOCOPY hdr_type
, p_line_rec_tbl IN OUT NOCOPY line_type_tbl
, p_lot_rec_tbl IN OUT NOCOPY lot_type_tbl
, x_hdr_row OUT NOCOPY gmi_discrete_transfers%ROWTYPE
, x_line_row_tbl OUT NOCOPY line_row_tbl
, x_lot_row_tbl OUT NOCOPY lot_row_tbl
) IS
l_api_name CONSTANT VARCHAR2(30) := 'construct_post_records' ;
GMIVDBX.header_insert
(
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_rec => p_hdr_rec
, x_hdr_row => x_hdr_row
);
log_msg('After calling GMIVDBX.header_insert.return status is '||x_return_status);
GMIVDBX.line_insert
(
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 => x_hdr_row
, p_line_rec => p_line_rec_tbl(i)
, x_line_row => x_line_row_tbl(i)
);
log_msg('After call to procedure GMIVDBX.line_insert return status is '||x_return_status);
GMIVDBX.lot_insert
(
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_line_row => x_line_row_tbl(i)
, p_lot_rec => p_lot_rec_tbl(j)
, x_lot_row => x_lot_row_tbl(j)
);
log_msg('After call to procedure GMIVDBX.lot_insert return status is '||x_return_status);
log_msg('Calling GMIVTDX.create_txn_update_balances for posting line no '||to_char(i));
GMIVTDX.create_txn_update_balances
(
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_transfer_id => x_hdr_row.transfer_id
, p_line_id => x_line_row_tbl(i).line_id
, x_transaction_header_id => p_hdr_rec.transaction_header_id
);
log_msg('After call to procedure GMIVTDX.create_txn_update_balances.return status is '||x_return_status);
update mtl_material_transactions
set costed_flag = 'Y',
opm_costed_flag = 'Y'
where transaction_set_id = p_hdr_rec.transaction_header_id;
select lot_control_code, lot_status_enabled
into l_lot_ctl, l_lot_sts
from mtl_system_items_b
where inventory_item_id = (select inventory_item_id from mtl_material_transactions where transaction_set_id = p_hdr_rec.transaction_header_id)
and organization_id = (select organization_id from mtl_material_transactions where transaction_set_id = p_hdr_rec.transaction_header_id);
update mtl_onhand_quantities_detail
set status_id = (select status_id from mtl_material_statuses
where status_code = (select opm_lot_status from gmi_discrete_transfer_lots
where transfer_id = (select transaction_source_id from mtl_material_transactions
where transaction_set_id = p_hdr_rec.transaction_header_id)) )
where create_transaction_id = (select transaction_id from mtl_material_transactions
where transaction_set_id = p_hdr_rec.transaction_header_id) ;
log_msg('After update to mmt.costed_flag');