[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT a.start_date, a.contract_number, b.pdt_id
FROM okc_k_headers_b a, okl_k_headers b
WHERE a.id = b.id
AND a.id = cp_khr_id;
SELECT method_code, life_in_months
FROM fa_methods
WHERE method_id = p_method_id;
SELECT hold_period_days, method_id, deprn_rate
FROM okl_amort_hold_setups
WHERE category_id = p_category_id
AND book_type_code = p_book_type_code;
SELECT cost
FROM okx_ast_bks_v
WHERE asset_id = p_id
AND book_type_code = p_btc;
lp_thpv_rec.tas_type := 'AUD'; -- depreciate flag update trx
lp_thpv_rec.tas_type := 'AUD'; -- depreciate flag update trx
SELECT method_code, life_in_months
FROM fa_methods
WHERE method_id = p_method_id;
SELECT hold_period_days, method_id, deprn_rate
FROM okl_amort_hold_setups
WHERE category_id = p_category_id
AND book_type_code = p_book_type_code;
SELECT method_code, life_in_months
FROM fa_methods
WHERE method_id = p_method_id;
SELECT hold_period_days, method_id, deprn_rate
FROM okl_amort_hold_setups
WHERE category_id = p_category_id
AND book_type_code = p_book_type_code;
SELECT fb.book_type_code
FROM fa_books fb, fa_additions_b fab, fa_book_controls fbc
WHERE fb.asset_id = fab.asset_id
AND fb.book_type_code = fbc.book_type_code
AND nvl(fbc.date_ineffective,cp_sysdate+1) > cp_sysdate
AND fb.transaction_header_id_out IS NULL
AND fab.asset_number = cp_asset_number
AND fbc.book_class = 'TAX';
SELECT fb.book_type_code
FROM fa_books fb, fa_additions_b fab, fa_book_controls fbc
WHERE fb.asset_id = fab.asset_id
AND fb.book_type_code = fbc.book_type_code
AND nvl(fbc.date_ineffective,cp_sysdate+1) > cp_sysdate
AND fb.transaction_header_id_out IS NULL
AND fab.asset_number = cp_asset_number
AND fbc.book_class = 'CORPORATE';
SELECT dnz_chr_id, depreciation_category, corporate_book, salvage_value, deprn_method_code, life_in_months,
asset_number, item_description, asset_id,original_cost, current_units, in_service_date, deal_type
FROM okx_asset_lines_v lines, OKL_K_HEADERS_FULL_V hdr
WHERE lines.dnz_chr_id = hdr.id
AND lines.parent_line_id = p_kle_id;
SELECT cleb_fin.id parent_line_id,
cleb_fin.dnz_chr_id,
fab.asset_category_id depreciation_category,
fb.book_type_code,
fbc.book_class,
fb.salvage_value,
fb.DEPRN_METHOD_CODE,
fb.LIFE_IN_MONTHS,
-- SECHAWLA 28-MAY-04 : 3645574 :added adjusted_rate
fb.adjusted_rate deprn_rate,
fab.asset_number asset_number,
clet_fin.item_description,
fb.asset_id,
fb.original_cost,
fab.current_units,
fb.DATE_PLACED_IN_SERVICE in_service_date
FROM fa_books fb,
fa_additions_b fab,
fa_book_controls fbc,
okc_k_items cim_fa,
okc_k_lines_b cleb_fa,
okc_line_styles_b lseb_fa,
okc_k_lines_tl clet_fin,
okc_k_lines_b cleb_fin,
okc_line_styles_b lseb_fin
WHERE fb.asset_id = fab.asset_id
AND fb.transaction_header_id_out IS NULL
AND fb.book_type_code = fbc.book_type_code
AND nvl(fbc.date_ineffective,cp_sysdate+1) > cp_sysdate
AND fab.asset_id = to_number(cim_fa.object1_id1)
AND cim_fa.object1_id2 = '#'
AND cim_fa.cle_id = cleb_fa.id
AND cim_fa.dnz_chr_id = cleb_fa.dnz_chr_id
AND cleb_fa.cle_id = cleb_fin.id
AND cleb_fa.dnz_chr_id = cleb_fin.dnz_chr_id
AND cleb_fa.lse_id = lseb_fa.id
AND lseb_fa.lty_code = 'FIXED_ASSET'
AND clet_fin.id = cleb_fin.id
AND clet_fin.language = userenv('LANG')
AND cleb_fin.lse_id = lseb_fin.id
AND lseb_fin.lty_code = 'FREE_FORM1'
AND cleb_fin.sts_code <> 'ABANDONED'
AND cleb_fin.id = cp_kle_id
AND fbc.book_class = 'CORPORATE';
SELECT cleb_fin.id parent_line_id,
cleb_fin.dnz_chr_id,
fab.asset_category_id depreciation_category,
fb.book_type_code,
fbc.book_class,
fb.salvage_value,
fb.DEPRN_METHOD_CODE,
fb.LIFE_IN_MONTHS,
-- SECHAWLA 28-MAY-04 : 3645574 :added adjusted_rate
fb.adjusted_rate deprn_rate,
fab.asset_number asset_number,
clet_fin.item_description,
fb.asset_id,
fb.original_cost,
fab.current_units,
fb.DATE_PLACED_IN_SERVICE in_service_date
FROM fa_books fb,
fa_additions_b fab,
fa_book_controls fbc,
okc_k_items cim_fa,
okc_k_lines_b cleb_fa,
okc_line_styles_b lseb_fa,
okc_k_lines_tl clet_fin,
okc_k_lines_b cleb_fin,
okc_line_styles_b lseb_fin
WHERE fb.asset_id = fab.asset_id
AND fb.transaction_header_id_out is null
AND fb.book_type_code = fbc.book_type_code
AND nvl(fbc.date_ineffective,cp_sysdate+1) > cp_sysdate
AND fab.asset_id = to_number(cim_fa.object1_id1)
AND cim_fa.object1_id2 = '#'
AND cim_fa.cle_id = cleb_fa.id
AND cim_fa.dnz_chr_id = cleb_fa.dnz_chr_id
AND cleb_fa.cle_id = cleb_fin.id
AND cleb_fa.dnz_chr_id = cleb_fin.dnz_chr_id
AND cleb_fa.lse_id = lseb_fa.id
AND lseb_fa.lty_code = 'FIXED_ASSET'
AND clet_fin.id = cleb_fin.id
AND clet_fin.language = userenv('LANG')
AND cleb_fin.lse_id = lseb_fin.id
AND lseb_fin.lty_code = 'FREE_FORM1'
AND cleb_fin.sts_code <> 'ABANDONED'
AND cleb_fin.id = cp_kle_id
AND fbc.book_class IN ('CORPORATE','TAX')
ORDER BY fbc.book_class;
SELECT cleb_fin.id parent_line_id,
cleb_fin.dnz_chr_id,
fab.asset_category_id depreciation_category,
fb.book_type_code,
fbc.book_class,
fb.salvage_value,
fb.DEPRN_METHOD_CODE,
fb.LIFE_IN_MONTHS,
-- SECHAWLA 28-MAY-04 : 3645574 :added adjusted_rate
fb.adjusted_rate deprn_rate,
fab.asset_number asset_number,
clet_fin.item_description,
fb.asset_id,
fb.original_cost,
fab.current_units,
fb.DATE_PLACED_IN_SERVICE in_service_date
FROM fa_books fb,
fa_additions_b fab,
fa_book_controls fbc,
okc_k_items cim_fa,
okc_k_lines_b cleb_fa,
okc_line_styles_b lseb_fa,
okc_k_lines_tl clet_fin,
okc_k_lines_b cleb_fin,
okc_line_styles_b lseb_fin
WHERE fb.asset_id = fab.asset_id
AND fb.transaction_header_id_out is null
AND fb.book_type_code = fbc.book_type_code
AND nvl(fbc.date_ineffective,cp_sysdate+1) > cp_sysdate
AND fab.asset_id = to_number(cim_fa.object1_id1)
AND cim_fa.object1_id2 = '#'
AND cim_fa.cle_id = cleb_fa.id
AND cim_fa.dnz_chr_id = cleb_fa.dnz_chr_id
AND cleb_fa.cle_id = cleb_fin.id
AND cleb_fa.dnz_chr_id = cleb_fin.dnz_chr_id
AND cleb_fa.lse_id = lseb_fa.id
AND lseb_fa.lty_code = 'FIXED_ASSET'
AND clet_fin.id = cleb_fin.id
AND clet_fin.language = userenv('LANG')
AND cleb_fin.lse_id = lseb_fin.id
AND lseb_fin.lty_code = 'FREE_FORM1'
AND cleb_fin.sts_code <> 'ABANDONED'
AND cleb_fin.id = cp_kle_id
AND fbc.book_class IN ('CORPORATE','TAX')
AND fb.book_type_code <> cp_rep_book
ORDER BY fbc.book_class;
SELECT cleb_fin.id parent_line_id,
cleb_fin.dnz_chr_id,
fab.asset_category_id depreciation_category,
fb.book_type_code,
fbc.book_class,
fb.salvage_value,
fb.DEPRN_METHOD_CODE,
fb.LIFE_IN_MONTHS,
-- SECHAWLA 28-MAY-04 : 3645574 :added adjusted_rate
fb.adjusted_rate deprn_rate,
fab.asset_number asset_number,
clet_fin.item_description,
fb.asset_id,
fb.original_cost,
fab.current_units,
fb.DATE_PLACED_IN_SERVICE in_service_date
FROM fa_books fb,
fa_additions_b fab,
fa_book_controls fbc,
okc_k_items cim_fa,
okc_k_lines_b cleb_fa,
okc_line_styles_b lseb_fa,
okc_k_lines_tl clet_fin,
okc_k_lines_b cleb_fin,
okc_line_styles_b lseb_fin
WHERE fb.asset_id = fab.asset_id
AND fb.transaction_header_id_out is null
AND fb.book_type_code = fbc.book_type_code
AND nvl(fbc.date_ineffective,cp_sysdate+1) > cp_sysdate
AND fab.asset_id = to_number(cim_fa.object1_id1)
AND cim_fa.object1_id2 = '#'
AND cim_fa.cle_id = cleb_fa.id
AND cim_fa.dnz_chr_id = cleb_fa.dnz_chr_id
AND cleb_fa.cle_id = cleb_fin.id
AND cleb_fa.dnz_chr_id = cleb_fin.dnz_chr_id
AND cleb_fa.lse_id = lseb_fa.id
AND lseb_fa.lty_code = 'FIXED_ASSET'
AND clet_fin.id = cleb_fin.id
AND clet_fin.language = userenv('LANG')
AND cleb_fin.lse_id = lseb_fin.id
AND lseb_fin.lty_code = 'FREE_FORM1'
AND cleb_fin.sts_code <> 'ABANDONED'
AND cleb_fin.id = cp_kle_id
AND fbc.book_class = 'TAX'
AND fb.book_type_code = cp_rep_book;
SELECT a.id, a.deal_type, a.legal_entity_id
FROM okl_k_headers a, okc_k_lines_b b
WHERE a.id = b.dnz_chr_id
AND b.id = cp_kle_id;
SELECT name
FROM okl_k_lines_full_v
WHERE id = cp_kle_id;
SELECT oec, residual_value
FROM okl_k_lines_full_v
WHERE id = p_id;
SELECT original_cost
FROM okl_txl_assets_v
WHERE tal_type = 'CFA'
AND asset_number = p_asset_number
AND ROWNUM < 2;
SELECT SYSDATE INTO l_sysdate FROM DUAL;
SELECT parent_line_id
FROM okx_asset_lines_v
WHERE dnz_chr_id = p_contract_id;
SELECT sts_code
FROM okc_k_headers_b
WHERE id = cp_khr_id;
SELECT a.id
FROM okc_k_lines_b a , okc_line_styles_b b
WHERE a.chr_id = cp_khr_id
AND a.lse_id = b.id
AND b.lty_code = 'FREE_FORM1'
AND a.sts_code = cp_sts_code;
PROCEDURE update_offlease_asset_trx(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_header_rec IN thpv_rec_type,
p_lines_rec IN tlpv_rec_type
) IS
l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
l_api_name CONSTANT VARCHAR2(30) := 'update_offlease_asset_trx';
SELECT DATE_TRANS_OCCURRED
FROM okl_trx_assets -- SECHAWLA 24-FEB-05 4147143 : changed view to base table
WHERE id = p_id;
SELECT l.asset_number, l.hold_period_days, l.depreciate_yn, l.kle_id, l.tal_type,
decode(d.tax_book,NULL,l.CORPORATE_BOOK,d.tax_book ) ASSET_BOOK
FROM okl_txl_assets_b l, -- SECHAWLA 24-FEB-05 4147143 : changed view to base table
OKL_TXD_ASSETS_B d -- SECHAWLA 27-FEB-05 4147143 : added
WHERE l.id = p_id
AND l.id = d.tal_id(+); -- SECHAWLA 27-FEB-05 4147143 : added
SELECT l.id, l.tas_id
FROM okl_trx_assets h, okl_txl_assets_b l, OKL_TXD_ASSETS_B d
WHERE l.kle_id = cp_kle_id
AND l.tas_id = h.id
AND h.tsu_code IN ( 'ENTERED','ERROR')
AND l.tal_type IN ('AUF','AUT')
AND l.depreciate_yn = 'N'
AND l.id = d.tal_id(+)
AND decode(d.tax_book,NULL,l.CORPORATE_BOOK,d.tax_book ) = cp_book_type_code;
SELECT hdr.id hdr_id ,line.id line_id
FROM okl_trx_assets hdr, okl_txl_assets_b line, OKL_TXD_ASSETS_B txd
WHERE line.kle_id = cp_kle_id
AND line.tas_id = hdr.id
AND hdr.tsu_code IN ( 'ENTERED','ERROR')
AND tal_type = 'AUF'
AND depreciate_yn = 'Y'
AND line.tas_id = hdr.id
AND line.id = txd.tal_id(+)
AND decode(txd.tax_book,NULL,line.CORPORATE_BOOK,txd.tax_book ) = cp_book_type_code;
SELECT h.DATE_TRANS_OCCURRED
FROM okl_trx_assets h, okl_txl_assets_b l, OKL_TXD_ASSETS_B d
WHERE l.kle_id = cp_kle_id
AND l.tas_id = h.id
AND h.tsu_code IN ( 'ENTERED','ERROR')
AND l.tal_type = 'AML'
AND l.tas_id = h.id
AND l.id = d.tal_id(+)
AND decode(d.tax_book,NULL,l.CORPORATE_BOOK,d.tax_book ) = cp_book_type_code;
SELECT SYSDATE INTO l_sysdate FROM DUAL;
IF l_tal_type = 'AUF' AND l_depreciate_yn = 'Y' THEN -- 4th trx is being updated
-- get the trx date of 3rd trx
OPEN l_amortline_csr(l_kle_id, l_booktype_code); -- SECHAWLA 27-FEB-05 4147143 ; added book type code
OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_thpv_rec => p_header_rec,
x_thpv_rec => lx_header_rec);
OKL_TXL_ASSETS_PUB.update_txl_asset_def(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tlpv_rec => lp_lines_temp_rec,
x_tlpv_rec => lx_lines_rec);
IF l_tal_type = 'AML' THEN -- 3rd trx is updated
IF trunc(l_new_trans_date) <> trunc(l_old_trans_date) THEN -- date changed during 3rd trx updte
--If trx date = termination date then cancel 1st 2 transcations
IF l_hold_zero = 'Y' THEN
-- cancel 1st 2 transactions
FOR l_nodeplines_rec IN l_nodeplines_csr(l_kle_id, l_booktype_code) LOOP -- SECHAWLA 27-FEB-05 4147143 : added booktype_code
lp_header_rec.id := l_nodeplines_rec.tas_id;
OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_thpv_rec => lp_header_rec,
x_thpv_rec => lx_header_rec);
OKL_TXL_ASSETS_PUB.update_txl_asset_def(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tlpv_rec => lp_lines_rec,
x_tlpv_rec => lx_lines_rec);
OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_thpv_rec => lp_header_rec,
x_thpv_rec => lx_header_rec);
OKL_TXL_ASSETS_PUB.update_txl_asset_def(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tlpv_rec => lp_lines_temp_rec,
x_tlpv_rec => lx_lines_rec);
END update_offlease_asset_trx;
PROCEDURE update_offlease_asset_trx(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 ,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_header_tbl IN thpv_tbl_type,
p_lines_tbl IN tlpv_tbl_type,
x_record_status OUT NOCOPY VARCHAR2 ) IS
l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
l_api_name CONSTANT VARCHAR2(30) := 'update_offlease_asset_trx';
SELECT DATE_TRANS_OCCURRED
FROM OKL_TRX_ASSETS
WHERE id = p_id;
SELECT asset_number, hold_period_days, depreciate_yn
FROM okl_txl_assets_v
WHERE id = p_id;
With new logic of updating off-lease transctions, update to one transaction results in updat to other
transactions for an asset. This procedure needs to be modified to uptake the new logic, if required
IF p_header_tbl.count <> p_lines_tbl.count THEN
x_return_status := OKL_API.G_RET_STS_ERROR;
SELECT SYSDATE INTO l_sysdate FROM DUAL;
OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_thpv_rec => p_header_tbl(i),
x_thpv_rec => lx_header_rec);
OKL_TXL_ASSETS_PUB.update_txl_asset_def(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tlpv_rec => lp_lines_temp_rec,
x_tlpv_rec => lx_lines_rec);
END update_offlease_asset_trx;
PROCEDURE update_depreciation(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_deprn_rec IN deprn_rec_type) IS
-- Get the Trn details
CURSOR trns_csr (p_id IN NUMBER) IS
SELECT TAS.id,
TAS.tsu_code,
TAS.tas_type,
TAS.date_trans_occurred
FROM OKL_TRX_ASSETS TAS
WHERE TAS.id = p_id;
SELECT FAM.method_code
FROM FA_METHODS FAM
WHERE FAM.method_code = p_dep_method
AND FAM.life_in_months = p_life_in_months;
SELECT TXL.id
FROM OKL_TXL_ASSETS_V TXL
WHERE TXL.tas_id = p_header_id;
SELECT fnd.meaning
FROM fnd_lookups fnd
WHERE fnd.lookup_type = 'OKL_TRANS_HEADER_TYPE'
AND fnd.lookup_code = p_code;
SELECT fnd.meaning
FROM fnd_lookups fnd
WHERE fnd.lookup_type = 'OKL_TRANSACTION_STATUS'
AND fnd.lookup_code = p_code;
SELECT 'x'
FROM fa_methods famet, fa_flat_rates fart
WHERE famet.method_code = cp_deprn_method
AND famet.method_id = fart.method_id
AND fart.adjusted_rate = cp_deprn_rate_percent / 100 -- sechawla 03-jun-04 3657624 : divide by 100
AND nvl(fart.adjusting_rate,0) = 0 ;
l_api_name := 'update_depreciation';
update_offlease_asset_trx(
p_api_version => p_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_header_rec => lp_header_rec,
p_lines_rec => lp_lines_rec);
END update_depreciation;