The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
ID,
OBJECT_VERSION_NUMBER,
ICA_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
TAS_TYPE,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
TSU_CODE,
TRY_ID,
DATE_TRANS_OCCURRED
,TRANS_NUMBER
,COMMENTS
FROM OKL_TRX_ASSETS
WHERE OKL_TRX_ASSETS.id = p_id;
l_thpv_tbl(1).LAST_UPDATED_BY,
l_thpv_tbl(1).LAST_UPDATE_DATE,
l_thpv_tbl(1).LAST_UPDATE_LOGIN,
l_thpv_tbl(1).TSU_CODE,
l_thpv_tbl(1).TRY_ID,
l_thpv_tbl(1).DATE_TRANS_OCCURRED,
l_thpv_tbl(1).TRANS_NUMBER,
l_thpv_tbl(1).COMMENTS;
SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_TYPE='OKL_TRANSACTION_STATUS'
AND LOOKUP_CODE=p_st_Code;
SELECT instance_id
FROM okx_install_items_v
where instance_number = l_instance_number;
SELECT ID,
OBJECT_VERSION_NUMBER,
SFWT_FLAG,
TAS_ID,
ILO_ID,
ILO_ID_OLD,
IAY_ID,
IAY_ID_NEW,
KLE_ID,
DNZ_KHR_ID,
LINE_NUMBER,
ORG_ID,
TAL_TYPE,
ASSET_NUMBER,
DESCRIPTION,
FA_LOCATION_ID,
ORIGINAL_COST,
CURRENT_UNITS,
MANUFACTURER_NAME,
YEAR_MANUFACTURED,
SUPPLIER_ID,
USED_ASSET_YN,
TAG_NUMBER,
MODEL_NUMBER,
CORPORATE_BOOK,
DATE_PURCHASED,
DATE_DELIVERY,
IN_SERVICE_DATE,
LIFE_IN_MONTHS,
DEPRECIATION_ID,
DEPRECIATION_COST,
DEPRN_METHOD,
DEPRN_RATE,
SALVAGE_VALUE,
PERCENT_SALVAGE_VALUE,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
DEPRECIATE_YN,
HOLD_PERIOD_DAYS,
OLD_SALVAGE_VALUE,
NEW_RESIDUAL_VALUE,
OLD_RESIDUAL_VALUE,
UNITS_RETIRED,
COST_RETIRED,
SALE_PROCEEDS,
REMOVAL_COST,
DNZ_ASSET_ID
,DATE_DUE
FROM Okl_Txl_Assets_V
WHERE okl_txl_assets_v.tas_id = p_tas_id
AND okl_txl_assets_v.tal_type = p_tal_type;
l_talv_tbl(1).LAST_UPDATED_BY,
l_talv_tbl(1).LAST_UPDATE_DATE,
l_talv_tbl(1).LAST_UPDATE_LOGIN,
l_talv_tbl(1).DEPRECIATE_YN,
l_talv_tbl(1).HOLD_PERIOD_DAYS,
l_talv_tbl(1).OLD_SALVAGE_VALUE,
l_talv_tbl(1).NEW_RESIDUAL_VALUE,
l_talv_tbl(1).OLD_RESIDUAL_VALUE,
l_talv_tbl(1).UNITS_RETIRED,
l_talv_tbl(1).COST_RETIRED,
l_talv_tbl(1).SALE_PROCEEDS,
l_talv_tbl(1).REMOVAL_COST,
l_talv_tbl(1).DNZ_ASSET_ID,
l_talv_tbl(1).DATE_DUE;
SELECT NAME
FROM OKX_VENDORS_V
WHERE ID1=p_vendor_id;
SELECT ID,
OBJECT_VERSION_NUMBER,
TAS_ID,
TAL_ID,
KLE_ID,
TAL_TYPE,
LINE_NUMBER,
INSTANCE_NUMBER_IB,
OBJECT_ID1_NEW,
OBJECT_ID2_NEW,
JTOT_OBJECT_CODE_NEW,
OBJECT_ID1_OLD,
OBJECT_ID2_OLD,
JTOT_OBJECT_CODE_OLD,
INVENTORY_ORG_ID,
SERIAL_NUMBER,
MFG_SERIAL_NUMBER_YN,
INVENTORY_ITEM_ID,
INV_MASTER_ORG_ID,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
CREATED_BY,
CREATION_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN
FROM OKL_TXL_ITM_INSTS iti
WHERE iti.tas_id = p_id
AND iti.tal_type = p_tal_type;
l_itiv_tbl(1).LAST_UPDATED_BY,
l_itiv_tbl(1).LAST_UPDATE_DATE,
l_itiv_tbl(1).LAST_UPDATE_LOGIN;
SELECT TAS_TYPE
FROM OKL_TRX_ASSETS
WHERE ID=p_tas_id;
select transaction_type_id
from CS_TRANSACTION_TYPES_V
where Name = p_transaction_type;
SELECT tt.transaction_type_id
FROM cs_transaction_types_b tt,
cs_transaction_types_tl ttl
WHERE tt.transaction_type_id = ttl.transaction_type_id
AND ttl.language = 'US'
AND ttl.NAME = p_transaction_type;
PROCEDURE update_serial_number(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
p_instance_id IN NUMBER,
p_instance_name IN VARCHAR2,
p_serial_number IN VARCHAR2,
p_inventory_item_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2)
AS
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_SERIAL_NUMBER';
select object_version_number into l_object_version_number from csi_item_instances
where instance_id = p_instance_id;
csi_item_instance_pub.update_item_instance(p_api_version => p_api_version,
p_commit => fnd_api.g_false,
p_init_msg_list => p_init_msg_list,
p_instance_rec => l_instance_rec,
p_ext_attrib_values_tbl => l_ext_attrib_values_tbl,
p_party_tbl => l_party_tbl,
p_account_tbl => l_account_tbl,
p_pricing_attrib_tbl => l_pricing_attrib_tbl,
p_org_assignments_tbl => l_org_assignments_tbl,
p_asset_assignment_tbl => l_asset_assignment_tbl,
p_txn_rec => l_txn_rec,
x_instance_id_lst => l_instance_id_lst,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
END update_serial_number;
PROCEDURE Update_item_description(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_dnz_chr_id IN NUMBER,
p_parent_line_id IN NUMBER,
p_item_description IN VARCHAR2) AS
subtype klev_rec_type is okl_CONTRACT_PVT.klev_rec_type;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_ITEM_DESCRIPTION';
SELECT parent_line_id
FROM OKX_ASSET_LINES_V
WHERE id1=c_line_id;
OKL_CONTRACT_PUB.update_contract_line(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_clev_rec,
p_klev_rec => l_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec);
OKL_CONTRACT_PUB.update_contract_line(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_clev_rec,
p_klev_rec => l_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec);
END Update_item_description;
SELECT currency_conversion_type,
currency_conversion_rate
FROM OKL_K_HEADERS
WHERE id = cp_khr_id;
SELECT okl_trn_seq.nextval
FROM dual;
SELECT A.ID
FROM OKC_K_LINES_V A,
OKC_LINE_STYLES_B B
WHERE A.CLE_ID = c_parent_line_id
AND A.LSE_ID = B.ID
AND B.LTY_CODE = 'ITEM';
SELECT B.ID
FROM AP_SUPPLIERS A,
OKC_K_PARTY_ROLES_B B
WHERE A.VENDOR_ID = B.OBJECT1_ID1
AND B.RLE_CODE = 'OKL_VENDOR'
AND B.CLE_ID = c_model_line_id;
SELECT ID2
FROM OKX_VENDORS_V
where id1=c_id;
SELECT CLE.ID ID1
FROM OKC_K_LINES_B CLE,
OKC_K_ITEMS CIM
WHERE CLE.DNZ_CHR_ID=c_khr_id
AND CIM.CLE_ID = CLE.ID
AND CIM.JTOT_OBJECT1_CODE = 'OKX_ASSET'
AND CIM.DNZ_CHR_ID = CLE.DNZ_CHR_ID
AND CIM.Object1_id1 = c_asset_id
AND CLE.STS_CODE <> 'ABANDONED';
-- use this info to update the FA tables
-- update all the asset details
p_talv_tbl(1).tas_id := p_tas_id;
okl_asset_details_pub.update_asset(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_asset_id => to_number(x_talv_tbl(1).dnz_asset_id),
p_asset_number => x_talv_tbl(1).asset_number,
px_asset_desc => l_asset_desc,
px_model_no => x_talv_tbl(1).model_number,
px_manufacturer => x_talv_tbl(1).manufacturer_name);
okl_asset_details_pub.update_year(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_dnz_chr_id => x_talv_tbl(1).dnz_khr_id,
p_parent_line_id => l_id1_okx_asset_lines,
p_year => x_talv_tbl(1).year_manufactured,
x_year => x_year);
update_item_description(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_dnz_chr_id => x_talv_tbl(1).dnz_khr_id,
--p_parent_line_id => x_itiv_tbl(1).kle_id,
p_parent_line_id => l_id1_okx_asset_lines,
--akrangan bug 5362977 start
--p_item_description => l_item_desc);
update_serial_number(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_instance_id => l_instance_id,
p_instance_name => x_itiv_tbl(1).instance_number_ib,
p_serial_number => x_itiv_tbl(1).serial_number,
p_inventory_item_id => x_itiv_tbl(1).inventory_item_id,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
okl_create_kle_pub.Update_party_roles_rec(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_cplv_rec => p_cplv_rec,
x_cplv_rec => x_cplv_rec);
-- update the transaction table and set the status to processed or whatever
--If the trx failed then i have to record the reason for failure and send message to the
--concerned person abt this. How do we do this?
IF x_return_status = OKL_API.G_RET_STS_SUCCESS THEN
p_thpv_tbl(1).id := p_tas_id;
okl_trx_assets_pub.update_trx_ass_h_def(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_thpv_tbl => p_thpv_tbl,
x_thpv_tbl => x_thpv_tbl);