[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DISTINCT l.asset_number, l.kle_id, b.authoring_org_id
FROM OKL_TRX_ASSETS h, OKL_TXL_ASSETS_B l -- SECHAWLA 19-FEB-04 3439647 : Use base tables instead of views
,okc_k_headers_all_b b /* Bug 6459571 */
WHERE h.id = l.tas_id
AND h.tsu_code in('ENTERED','ERROR') -- SMODUGA 07-FEB-05 3578894 : process trx in entered and error status only
AND h.date_trans_occurred <= cp_date
-- SECHAWLA 06-MAY-04 3578894 : Removing 'AED' as evergreen transactions are not created
AND ( (p_salvage_writedown_yn = 'N' AND h.tas_type in ('AMT','AUD','AUS')) OR
(p_salvage_writedown_yn = 'Y' AND h.tas_type in ('AMT','AUD','AUS','FSC'))
)-- SECHAWLA 06-MAY-04 3578894 : Added new tas types 'AUD','AUS' to above 2 conditions
AND (
-- all 3 parameter values are provided
( p_contract_id IS NOT NULL AND p_asset_id IS NOT NULL AND p_kle_id IS NOT NULL AND
l.dnz_khr_id = p_contract_id AND l.dnz_asset_id= p_asset_id AND l.kle_id = p_kle_id)
OR
-- none of the parameter values are provided
( p_contract_id IS NULL AND p_asset_id IS NULL AND p_kle_id IS NULL)
OR
-- contract Id is provided, asset_id and kle_id not provided
(p_contract_id IS NOT NULL AND l.dnz_khr_id = p_contract_id AND p_asset_id IS NULL AND p_kle_id IS NULL)
OR
-- contract Id and asset Id are provided, kle_id not provided
(p_contract_id IS NOT NULL AND l.dnz_khr_id = p_contract_id AND p_asset_id IS NOT NULL AND l.dnz_asset_id= p_asset_id AND p_kle_id IS NULL)
OR
-- contarct Id and kle_id are provided, asset Id not provided
(p_contract_id IS NOT NULL AND l.dnz_khr_id = p_contract_id AND p_asset_id IS NULL AND p_kle_id IS NOT NULL AND l.kle_id = p_kle_id)
OR
-- asset Id is provided, cntract ID and kle Id not provided
(p_contract_id IS NULL AND p_asset_id IS NOT NULL AND l.dnz_asset_id= p_asset_id AND p_kle_id IS NULL)
OR
-- asset Id and kle Id are provided, contract Id not provided
(p_contract_id IS NULL AND p_asset_id IS NOT NULL AND l.dnz_asset_id= p_asset_id AND p_kle_id IS NOT NULL AND l.kle_id = p_kle_id)
OR
-- kle Id is provided, contarct Id and asset Id not provided
(p_contract_id IS NULL AND p_asset_id IS NULL AND p_kle_id IS NOT NULL AND l.kle_id = p_kle_id)
)
AND l.dnz_khr_id = b.id(+)
AND NVL(b.AUTHORING_ORG_ID, P_ORG_ID ) = P_ORG_ID
ORDER BY asset_number;
SELECT h.id, l.id line_id, -- 17-DEC-04 SECHAWLA 4028371 : Added l.id
h.tas_type, FL.MEANING TAS_TYPE_MEANING, h.date_trans_occurred, l.depreciate_yn, l.dnz_asset_id,
decode(d.tax_book,NULL,l.CORPORATE_BOOK,d.tax_book ) ASSET_BOOK, fbc.book_class ASSET_BOOK_TYPE,
l.in_service_date, l.deprn_method, l.life_in_months, l.deprn_rate, --SECHAWLA 28-MAY-04 3645574 : Added deprn_rate
--nvl(l.depreciation_cost,0) depreciation_cost ,
l.depreciation_cost,
l.asset_number, nvl(l.salvage_value,0) salvage_value, nvl(l.old_salvage_value,0) old_salvage_value, l.kle_id,
to_char(h.trans_number) trans_number -- SECHAWLA 17-DEC-04 4028371 : added for stamping
--,l.currency_code func_currency_code -- SECHAWLA 29-JUL-05 4456005 : added
--,l.DNZ_KHR_ID -- SECHAWLA 29-JUL-05 4456005 : added
-- SGORANTL 22-MAR-06 5097643: changes made by kbbhavsa for bug 4717511 has been reversed
,h.try_id --akrangan added for sla populate sources cr
FROM OKL_TRX_ASSETS h, OKL_TXL_ASSETS_B l, OKL_TXD_ASSETS_B d, fa_book_controls fbc, FND_LOOKUPS fl -- SECHAWLA 19-FEB-04 3439647 : Use base tables instead of views
WHERE h.id = l.tas_id
AND l.id = d.tal_id(+)
AND FL.LOOKUP_CODE = h.TAS_TYPE AND FL.LOOKUP_TYPE = 'OKL_TRANS_HEADER_TYPE'
AND decode(d.tax_book,NULL, l.CORPORATE_BOOK,d.tax_book ) = fbc.book_type_code
AND h.tsu_code IN ('ENTERED','ERROR') -- SMODUGA 07-FEB-05 4144322 : process trx in entered and error status only
AND h.date_trans_occurred <= cp_date
-- SECHAWLA 06-MAY-04 3578894 : Separate SVW transactions from Amortization transactions
AND h.tas_type in ('AMT','AUD','AUS')
-- SECHAWLA 06-MAY-04 3578894 : Added new tas types 'AUD','AUS' to above 2 conditions
AND l.asset_number = cp_asset_number
ORDER BY asset_book_type , date_trans_occurred, tas_type;
SELECT h.id, h.tas_type, FL.MEANING TAS_TYPE_MEANING, h.date_trans_occurred, l.depreciate_yn, l.dnz_asset_id,
decode(d.tax_book,NULL,l.CORPORATE_BOOK,d.tax_book ) ASSET_BOOK, fbc.book_class ASSET_BOOK_TYPE,
l.in_service_date, l.deprn_method, l.life_in_months, l.deprn_rate, --SECHAWLA 28-MAY-04 3645574 : Added deprn_rate
--nvl(l.depreciation_cost,0) depreciation_cost ,
l.depreciation_cost,
l.asset_number, nvl(l.salvage_value,0) salvage_value, nvl(l.old_salvage_value,0) old_salvage_value, l.kle_id
--,l.currency_code func_currency_code -- SECHAWLA 29-JUL-05 4456005 : added
--,l.DNZ_KHR_ID -- SECHAWLA 29-JUL-05 4456005 : added
-- SGORANTL 22-MAR-06 5097643: changes made by kbbhavsa for bug 4717511 has been reversed
,h.try_id --akrangan added for sla populate sources cr
,l.id line_id --akrangan added for sla populate sources cr
FROM OKL_TRX_ASSETS h, OKL_TXL_ASSETS_B l, OKL_TXD_ASSETS_B d, fa_book_controls fbc, FND_LOOKUPS fl -- SECHAWLA 19-FEB-04 3439647 : Use base tables instead of views
WHERE h.id = l.tas_id
AND l.id = d.tal_id(+)
AND FL.LOOKUP_CODE = h.TAS_TYPE AND FL.LOOKUP_TYPE = 'OKL_TRANS_HEADER_TYPE'
AND decode(d.tax_book,NULL, l.CORPORATE_BOOK,d.tax_book ) = fbc.book_type_code
AND h.tsu_code IN ('ENTERED','ERROR') -- SMODUGA 07-FEB-05 4144322 : process trx in entered and error status only
AND h.date_trans_occurred <= cp_date
AND h.tas_type = 'FSC'
AND l.asset_number = cp_asset_number
ORDER BY asset_book_type;
SELECT fb.cost, fb.salvage_value, fb.depreciate_flag, fb.deprn_method_code, fb.life_in_months, fb.adjusted_rate
FROM fa_books fb, fa_additions_b fab
WHERE fb.transaction_header_id_out is null
AND fb.book_type_code = cp_book_type_code
AND fab.asset_id = fb.asset_id
AND fab.asset_number = cp_asset_number;
SELECT l.asset_number
FROM okl_trx_quotes_b qh, okl_txl_quote_lines_b ql, okx_asset_lines_v l
WHERE qh.id = ql.qte_id
AND qh.qst_code = 'ACCEPTED'
AND ql.qlt_code = 'AMCFIA'
AND ql.kle_id = l.parent_line_id
AND ql.kle_id = p_kle_id;
SELECT a.book_type_code
FROM fa_books a, fa_book_controls b
WHERE a.asset_id = p_asset_id
AND a.book_type_code = b.book_type_code
AND b.book_class = 'TAX'
AND a.date_ineffective IS NULL
AND a.transaction_header_id_out IS NULL;
SELECT lkhr.id, lkhr.deal_type, khr.contract_number
FROM okl_k_headers lkhr, okc_k_lines_b cle, okc_k_headers_b khr
WHERE khr.id = cle.chr_id
AND lkhr.id = khr.id
AND cle.id = p_financial_asset_id;
SELECT salvage_type
FROM fa_books
WHERE asset_id = cp_asset_id
AND book_type_code = cp_booktype_code
AND transaction_header_id_out IS NULL
AND date_ineffective IS NULL;
l_update_status VARCHAR2(1);
SELECT SYSDATE INTO l_sysdate FROM DUAL;
SAVEPOINT asset_updates;
IF l_fa_depreciate_flag <> 'NO' THEN -- SECHAWLA 10-MAY-04 3578894 : FA update fails
-- if we try to update the flag to the same value
-- Operating Lease hold period, DF Lease hold period : 1st trx - stop depreciation
-- asset header information
l_asset_hdr_rec.asset_id := l_assettrx_rec.dnz_asset_id;
/*IF l_fa_cost <> l_assettrx_rec.depreciation_cost THEN -- SECHAWLA 10-MAY-04 3578894 : FA update fails
-- if we try to update the cost with the same value
*/
-- SECHAWLA 19-FEB-04 3439647 : Write asset cost up to NIV when we stop the depreciation, for DF/Sales lease
IF l_deal_type IN ('LEASEDF','LEASEST') THEN
-- 5097643 27-mar-06 5029064: changes made by kbbhavsa for bug 4717511 has been reversed
--SECHAWLA 21-DEC-05 4899337 : end
l_dep_cost := l_assettrx_rec.depreciation_cost; -- sgorantl 27-mar-06 5097643
(l_fa_salvage_value <> l_sal_value) THEN -- SECHAWLA 10-MAY-04 3578894 : FA update fails
-- asset header information
l_asset_hdr_rec.asset_id := l_assettrx_rec.dnz_asset_id;
--SECHAWLA 28-MAY-04 3645574 : update life or rate and nullify the other
IF l_assettrx_rec.life_in_months IS NOT NULL THEN
l_asset_fin_rec_adj.life_in_months := l_assettrx_rec.life_in_months;
OKL_TXL_ASSETS_PUB.update_txl_asset_Def(
p_api_version => p_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_tlpv_rec => lp_tlpv_rec,
x_tlpv_rec => lx_tlpv_rec);
l_update_status := OKC_API.G_RET_STS_SUCCESS;
OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
p_api_version => p_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_thpv_rec => lp_thpv_rec,
x_thpv_rec => lx_thpv_rec);
l_update_status := OKC_API.G_RET_STS_ERROR;
IF l_update_status = OKC_API.G_RET_STS_SUCCESS THEN -- trx status updated successfully for all the transactions for this asset
COMMIT; -- it will commit changes in FA and also in OKL (trx status update)
ROLLBACK TO asset_updates; -- This will rollback FA changes and also OKL changes (trx status update, incase status was chnaged
ROLLBACK TO asset_updates;
SAVEPOINT trx_status_update;
OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
p_api_version => p_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_thpv_rec => lp_thpv_rec,
x_thpv_rec => lx_thpv_rec);
l_update_status := OKC_API.G_RET_STS_ERROR;
IF l_update_status = OKC_API.G_RET_STS_SUCCESS THEN -- trx status updated succesfully to 'ERROR' for all trx for this asset
COMMIT; -- commit the trx status upadte to ERROR
ELSE -- trx status could not be updated to ERROR for one or more transactions
ROLLBACK to trx_status_update; -- trx status will remain ENTERED (fa trx have already been rolled back)
OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
p_api_version => p_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_thpv_rec => lp_thpv_rec,
x_thpv_rec => lx_thpv_rec);
OKL_TRX_ASSETS_PUB.update_trx_ass_h_def(
p_api_version => p_api_version,
p_init_msg_list => OKC_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_thpv_rec => lp_thpv_rec,
x_thpv_rec => lx_thpv_rec);