[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_STS_UPDATE_TRX_MISSING CONSTANT VARCHAR2(200) := 'OKL_LLA_STS_UPDATE_TRX_MISSING';
SELECT
cim.ID,
cim.OBJECT_VERSION_NUMBER,
cim.CLE_ID,
cim.CHR_ID,
cim.CLE_ID_FOR,
cim.DNZ_CHR_ID,
cim.OBJECT1_ID1,
cim.OBJECT1_ID2,
cim.JTOT_OBJECT1_CODE,
cim.UOM_CODE,
cim.EXCEPTION_YN,
cim.NUMBER_OF_ITEMS,
cim.UPG_ORIG_SYSTEM_REF,
cim.UPG_ORIG_SYSTEM_REF_ID,
cim.PRICED_ITEM_YN,
cim.CREATED_BY,
cim.CREATION_DATE,
cim.LAST_UPDATED_BY,
cim.LAST_UPDATE_DATE,
cim.LAST_UPDATE_LOGIN
FROM Okc_K_Items_V cim
where cle_id = p_cle_id;
l_cimv_rec.LAST_UPDATED_BY,
l_cimv_rec.LAST_UPDATE_DATE,
l_cimv_rec.LAST_UPDATE_LOGIN;
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,
--Bug# 2981308
ASSET_KEY_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,
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,
CURRENCY_CODE,
CURRENCY_CONVERSION_TYPE,
CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_DATE
FROM Okl_Txl_Assets_V
WHERE okl_txl_assets_v.kle_id = p_kle_id
and okl_txl_assets_v.tal_type = p_trx_type
and exists (select '1' from OKL_TRX_ASSETS
where OKL_TRX_ASSETS.TAS_TYPE = p_trx_type
and OKL_TRX_ASSETS.TSU_CODE = G_TSU_CODE_ENTERED
and OKL_TRX_ASSETS.ID = Okl_txl_assets_v.tas_id);
l_talv_rec.LAST_UPDATED_BY,
l_talv_rec.LAST_UPDATE_DATE,
l_talv_rec.LAST_UPDATE_LOGIN,
l_talv_rec.DEPRECIATE_YN,
l_talv_rec.HOLD_PERIOD_DAYS,
l_talv_rec.OLD_SALVAGE_VALUE,
l_talv_rec.NEW_RESIDUAL_VALUE,
l_talv_rec.OLD_RESIDUAL_VALUE,
l_talv_rec.UNITS_RETIRED,
l_talv_rec.COST_RETIRED,
l_talv_rec.SALE_PROCEEDS,
l_talv_rec.REMOVAL_COST,
l_talv_rec.DNZ_ASSET_ID,
l_talv_rec.DATE_DUE,
l_talv_rec.CURRENCY_CODE,
l_talv_rec.CURRENCY_CONVERSION_TYPE,
l_talv_rec.CURRENCY_CONVERSION_RATE,
l_talv_rec.CURRENCY_CONVERSION_DATE;
PROCEDURE update_trx_status(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_tas_id IN NUMBER,
p_tsu_code IN VARCHAR2) IS
l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
l_api_name CONSTANT VARCHAR2(30) := 'update_trx_status';
SELECT tsu_code
FROM OKL_TRX_ASSETS
WHERE id = p_tas_id;
p_msg_name => G_STS_UPDATE_TRX_MISSING,
p_token1 => G_TAS_ID_TOKEN,
p_token1_value => 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_rec => l_thpv_rec,
x_thpv_rec => l_thpv_rec_out);
END update_trx_status;
Select cost,
corporate_book,
current_units,
name,
description
from OKX_ASSETS_V
where ID1 = p_asset_id
and status = 'A'
and nvl(start_date_active,sysdate) <= sysdate
and nvl(end_date_active,sysdate + 1) > sysdate;
select sum(deprn_amount)
from OKX_AST_DPRTNS_V
where Asset_id = to_number(p_asset_id)
and book_type_code = p_book_type_code
and status = 'A'
and nvl(start_date_active,sysdate) <= sysdate
and nvl(end_date_active,sysdate + 1) > sysdate;
select period_name,
calendar_period_open_date,
calendar_period_close_date
from fa_deprn_periods
where book_type_code = p_book_type_code
and period_close_date is null;
select period_name,
calendar_period_open_date,
calendar_period_close_date
from fa_deprn_periods
where book_type_code = p_book_type_code
and period_close_date is null;
SELECT '!'
FROM okl_trx_contracts ktrx
WHERE ktrx.khr_id_old = p_chr_id
AND ktrx.tsu_code = 'ENTERED'
AND ktrx.rbr_code is NOT NULL
AND ktrx.tcn_type = 'TRBK'
--rkuttiya added for 12.1.1 Multi GAAP Project
AND ktrx.representation_type = 'PRIMARY';
Select cleb_fin.id,
kle_fin.oec,
kle_fin.capital_amount,
cleb_fin.dnz_chr_id
into l_cle_id,
l_oec,
l_capital_amount,
l_chr_id
From
okc_k_lines_b cleb_fa,
okc_k_lines_b cleb_fin,
okl_k_lines kle_fin
where
cleb_fa.id = p_fa_cle_id
and cleb_fin.id = cleb_fa.cle_id
and cleb_fin.dnz_chr_id = cleb_fa.dnz_chr_id
and kle_fin.id = cleb_fin.id;
Select orig_system_id1
From okc_k_lines_b
where id = fa_cle_id;
Select nvl(sum(cov_ast_kle.capital_amount),0) capitalized_fee
From
OKL_K_LINES fee_kle,
OKC_K_LINES_B fee_cle,
OKC_STATUSES_B fee_sts,
OKL_K_LINES cov_ast_kle,
OKC_K_LINES_B cov_ast_cle,
OKC_LINE_STYLES_B cov_ast_lse,
OKC_STATUSES_B cov_ast_sts,
OKC_K_ITEMS cov_ast_cim,
OKC_K_LINES_B fa_cle,
OKC_K_LINES_B src_cle
Where fee_kle.id = fee_cle.id
and fee_kle.fee_type = 'CAPITALIZED'
and fee_cle.id = cov_ast_cle.cle_id
and fee_cle.dnz_chr_id = cov_ast_cle.dnz_chr_id
and fee_cle.sts_code = fee_sts.code
and fee_sts.ste_code not in ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
and cov_ast_kle.id = cov_ast_cle.id
and cov_ast_cle.id = cov_ast_cim.cle_id
and cov_ast_cle.lse_id = cov_ast_lse.id
and cov_ast_lse.lty_code = 'LINK_FEE_ASSET'
and cov_ast_cle.sts_code = cov_ast_sts.code
and cov_ast_sts.ste_code not in ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
and cov_ast_cle.dnz_chr_id = cov_ast_cim.dnz_chr_id
and cov_ast_cim.object1_id1 = to_char(fa_cle.cle_id)
and cov_ast_cim.object1_id2 = '#'
and cov_ast_cim.jtot_object1_code = 'OKX_COVASST'
and fa_cle.id = fa_cle_id
and fee_cle.orig_system_id1 = src_cle.id
and src_cle.dnz_chr_id = rbk_chr_id;
SELECT msi.expense_account
FROM MTL_SYSTEM_ITEMS msi,
OKC_K_ITEMS cim,
OKC_K_LINES_B mdl,
OKC_K_LINES_B fal
WHERE cim.object1_id1 = msi.inventory_item_id
AND cim.object1_id2 = to_char(msi.organization_id)
AND cim.jtot_object1_code = 'OKX_SYSITEM'
AND cim.dnz_chr_id = mdl.dnz_chr_id
AND cim.cle_id = mdl.id
AND mdl.dnz_chr_id = fal.dnz_chr_id
AND mdl.cle_id = fal.cle_id
AND fal.id = p_kle_id;
select set_of_books_id
from OKX_ASST_BK_CONTROLS_V
where book_type_code = p_book_type_code
and status = 'A';
SELECT fcp.start_date,
fcp.end_date,
fbc.book_type_code,
fbc.deprn_calendar,
fbc.prorate_calendar,
fbc.last_period_counter,
fdp.period_name
FROM fa_book_controls fbc,
fa_deprn_periods fdp,
fa_calendar_periods fcp
WHERE fcp.period_name = fdp.period_name
AND fdp.period_counter = (fbc.last_period_counter + 1)
AND fdp.book_type_code = fbc.book_type_code
AND fcp.calendar_type = fbc.deprn_calendar
AND fbc.date_ineffective is null
AND fbc.book_type_code = p_book_type_code;
l_trans_rec.who_info.last_updated_by := FND_GLOBAL.USER_ID;
select asset_key_ccid
into l_asset_desc_rec.asset_key_ccid
from fa_additions
where asset_id = p_ast_line_rec.asset_id;
select units_assigned,
location_id,
distribution_id,
code_combination_id
from fa_distribution_history
where asset_id = p_asset_id
and book_type_code = p_corporate_book
and transaction_header_id_out is null
and retirement_id is null;
l_trans_rec.who_info.last_updated_by := FND_GLOBAL.USER_ID;
l_trans_rec.who_info.last_update_login := FND_GLOBAL.LOGIN_ID;
l_asset_dist_tbl.delete;
select units_assigned,
distribution_id
from fa_distribution_history
where asset_id = p_asset_id
and book_type_code = p_corporate_book
and transaction_header_id_out is null
and retirement_id is null;
l_trans_rec.who_info.last_updated_by := FND_GLOBAL.USER_ID;
l_trans_rec.who_info.last_update_login := FND_GLOBAL.LOGIN_ID;
PROCEDURE FIXED_ASSET_UPDATE_DESC
(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_asset_id IN NUMBER,
p_model_number IN VARCHAR2,
p_manufacturer IN VARCHAR2,
p_description IN VARCHAR2,
p_trx_date IN DATE,
p_trx_number IN VARCHAR2,
x_fa_trx_date OUT NOCOPY DATE,
p_calling_interface IN VARCHAR2) is
l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
l_api_name CONSTANT varchar2(30) := 'FIXED_ASSET_UPDATE_DESC';
l_trans_rec.who_info.last_updated_by := FND_GLOBAL.USER_ID;
l_trans_rec.who_info.last_update_login := FND_GLOBAL.LOGIN_ID;
FA_ASSET_DESC_PUB.update_desc(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_commit => FND_API.G_FALSE,
p_validation_level => FND_API.G_VALID_LEVEL_FULL,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_calling_fn => p_calling_interface,
px_trans_rec => l_trans_rec,
px_asset_hdr_rec => l_asset_hdr_rec,
px_asset_desc_rec_new => l_asset_desc_rec,
px_asset_cat_rec_new => l_asset_cat_rec);
END FIXED_ASSET_UPDATE_DESC;
SELECT residual_value
FROM OKL_K_LINES
WHERE id = p_fin_ast_line_id;
SELECT '!'
FROM OKX_AST_CAT_BKS_V
WHERE CATEGORY_ID = p_category_id
AND BOOK_TYPE_CODE = p_book_type_code
AND STATUS = 'A';
SELECT asset_number,
description,
quantity,
cost,
tax_book,
life_in_months_tax,
deprn_method_tax,
deprn_rate_tax,
salvage_value,
--bug# 6373605 start
id asd_id
--bug# 6373605 end
FROM okl_txd_Assets_v
where tal_id = p_tal_id;
SELECT '!'
FROM OKX_AST_BKS_V
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND status = 'A';
select src.jtot_object_code
from OKC_LINE_STYLE_SOURCES src,
OKC_LINE_STYLES_B lse
where src.lse_id = lse.id
and lse.lty_code = p_lty_code;
select book_type_code,
cost,
--Bug# 2657558
salvage_value,
--Bug# 2981308
percent_salvage_value
from okx_ast_bks_v
where asset_id = p_Asset_id
and book_class = p_book_class;
SELECT '!'
FROM OKX_ASST_BK_CONTROLS_V
WHERE book_type_code = p_tax_book
AND book_class = 'TAX'
AND mass_copy_source_book = p_corp_book
AND allow_mass_copy = 'YES'
AND copy_additions_flag = 'YES';
SELECT okx.acquisition_date in_service_date,
okx.life_in_months life_in_months,
okx.cost cost,
okx.depreciation_category depreciation_category,
okx.deprn_method_code deprn_method_code,
okx.adjusted_rate adjusted_rate,
okx.basic_rate basic_rate,
okx.salvage_value salvage_value,
okx.percent_salvage_value percent_salvage_value,
okx.book_type_code book_type_code,
okx.book_class book_class,
okx.asset_number asset_number,
okx.asset_id asset_id
FROM okx_ast_bks_v okx
WHERE okx.asset_id = p_asset_id
AND okx.book_type_code = p_book_type_code;
l_select Varchar2(2000);
select to_char(trans_number),
DATE_TRANS_OCCURRED,
--Bug# 6373605 start
id tas_id,
try_id
--Bug# 6373605 end
from okl_trx_assets
where id = p_tas_id;
Select rule_information1 tax_owner,
id
From okc_rules_b rul
where rul.dnz_chr_id = pchrid
and rul.rule_information_category = 'LATOWN'
and nvl(rul.STD_TEMPLATE_YN,'N') = 'N';
select id
from okl_txd_assets_b txdb
where tal_id = p_tal_id
and tax_book = p_book_type_code;
OKL_OKC_MIGRATION_PVT.update_contract_item
(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_cimv_rec => l_cimv_rec,
x_cimv_rec => l_cimv_rec_out);
x_select => l_select);
okl_contract_pub.update_contract_header(
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_chrv_rec => l_chrv_rec,
p_khrv_rec => l_khrv_rec,
p_edit_mode => 'N',
x_chrv_rec => x_chrv_rec,
x_khrv_rec => x_khrv_rec);
update_trx_status(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_tas_id => l_talv_rec.tas_id,
p_tsu_code => G_TSU_CODE_PROCESSED);
okl_tal_pvt.update_row
(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_talv_rec => l_talv_date_rec,
x_talv_rec => lx_talv_date_rec);
SELECT chr.SCS_CODE,
chr.STS_CODE,
khr.DEAL_TYPE,
--11.5.9(Multi GAAP)
khr.PDT_ID,
chr.START_DATE
From OKC_K_HEADERS_B chr,
OKL_K_HEADERS khr
WHERE khr.id = chr.id
AND chr.ID = P_CHRV_ID;
SELECT cle.id,
cle.cle_id
from okc_k_lines_b cle,
okc_statuses_b sts
where cle.lse_id = G_FA_LINE_LTY_ID
and cle.dnz_chr_id = p_chrv_id
and cle.sts_code = sts.code
and sts.ste_code not in ('HOLD','EXPIRED','TERMINATED','CANCELLED');
Select rule_information1 tax_owner,
id
From okc_rules_b rul
where rul.dnz_chr_id = pchrid
and rul.rule_information_category = 'LATOWN'
and nvl(rul.STD_TEMPLATE_YN,'N') = 'N';
l_fa_line_id_tbl.delete;
l_fin_ast_line_id_tbl.delete;
SELECT ktrx.rbr_code,
ktrx.date_transaction_occurred,
khr.deal_type,
chr.id,
chr.sts_code,
rul.rule_information1,
--Bug# : 11.5.9 Multi-GAAP
khr.pdt_id,
chr.start_date,
--Bug# 3156924
ktrx.trx_number
FROM OKC_RULES_B rul,
OKL_K_HEADERS khr,
OKC_K_HEADERS_B chr,
OKL_TRX_CONTRACTS ktrx,
OKC_K_HEADERS_B rbk_chr
WHERE rul.dnz_chr_id = chr.id
AND rul.rule_information_category = 'LATOWN'
AND khr.id = chr.id
AND chr.id = rbk_chr.orig_system_id1
AND exists (select null
from okl_trx_types_tl tl
where tl.language = 'US'
and tl.name = 'Rebook'
and tl.id = ktrx.try_id)
AND ktrx.KHR_ID = chr.id
AND ktrx.KHR_ID_NEW = rbk_chr.id
AND ktrx.tsu_code = G_TSU_CODE_ENTERED
--rkuttiya added for 12.1.1 Multi GAAP Project
AND ktrx.representation_type = 'PRIMARY'
--
AND rbk_chr.orig_system_source_code = 'OKL_REBOOK'
AND rbk_chr.id = p_rbk_chr_id;
SELECT kle.OEC,
kle.RESIDUAL_VALUE,
cle.id,
cle.name
FROM OKC_K_LINES_V cle,
OKC_LINE_STYLES_B lse,
OKL_K_LINES kle,
OKC_K_LINES_B fa_cle,
OKC_LINE_STYLES_B fa_cle_lse
WHERE kle.id = cle.id
AND cle.chr_id = p_chr_id
AND cle.dnz_chr_id = p_chr_id
AND cle.lse_id = lse.id
AND lse.lty_code = G_FIN_AST_LINE_LTY_CODE
AND cle.id = fa_cle.cle_id
AND fa_cle.id = nvl(p_fa_cle_id,fa_cle.id)
AND fa_cle.lse_id = fa_cle_lse.id
AND fa_cle_lse.lty_code = G_FA_LINE_LTY_CODE
--Bug# 2942543 : effectivity should be checked by keeping start and end date as inclusive
--AND nvl(cle.start_date,p_effective_date) <= p_effective_date
--AND nvl(cle.end_date,p_effective_date+1) > p_effective_date
And p_effective_date between cle.start_date and cle.end_date
AND not exists (select '1'
from OKC_STATUSES_B sts
Where sts.code = cle.sts_code
--Bug# 2522268
--And sts.ste_code in ('HOLD','EXPIRED','TERMINATED','CANCELED'));
SELECT txl.in_service_date in_service_date,
txl.life_in_months life_in_months,
txl.depreciation_cost depreciation_cost,
txl.depreciation_id asset_category_id,
txl.deprn_method deprn_method,
txl.deprn_rate deprn_rate,
txl.salvage_value salvage_value,
txl.corporate_book book_type_code,
txl.asset_number asset_number,
txl.kle_id kle_id,
--bug# 3548044
txl.tas_id tas_id,
txl.salvage_value corp_salvage_value,
--Bug# 4028371
txl.id tal_id,
--Bug# 3950089
txl.percent_salvage_value pct_salvage_value,
txl.percent_salvage_value corp_pct_salvage_value,
--Bug# 5207066
txl.current_units rbk_current_units,
--akrangan bug# 5362977 start
cle.cle_id rbk_fin_ast_cle_id,
txl.model_number model_number,
txl.manufacturer_name manufacturer_name,
txl.description description,
--Bug# 6373605 start
txl.id sla_source_line_id,
txl.tas_id sla_source_header_id,
'OKL_TXL_ASSETS_B' sla_source_line_table,
tas.try_id sla_source_try_id
--Bug# 6373605 end
FROM OKL_TXL_ASSETS_V txl,
--akrangan bug# 5362977 end
--Bug# 6373605 start
OKL_TRX_ASSETS tas,
--Bug# 6373605 end
OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse
WHERE txl.kle_id = cle.id
--Bug# 6373605 start
AND tas.id = txl.tas_id
--Bug# 6373605 end
AND cle.dnz_chr_id = p_rbk_chr_id
AND cle.lse_id = lse.id
--effectivity
--Bug# 2942543 : effectivity should be checked by keeping start and end date as inclusive
--And nvl(cle.start_date,p_effective_date) <= p_effective_date
--And nvl(cle.end_date,p_effective_date+1) > p_effective_date
And p_effective_date between cle.start_date and cle.end_date
AND not exists (select '1'
from OKC_STATUSES_B sts
Where sts.code = cle.sts_code
--Bug# 2522268
--And sts.ste_code in ('HOLD','EXPIRED','TERMINATED','CANCELED')
And sts.ste_code in ('HOLD','EXPIRED','TERMINATED','CANCELLED')
)
AND lse.lty_code = G_FA_LINE_LTY_CODE
UNION
SELECT txl.in_service_date in_service_date,
txd.life_in_months_tax life_in_months,
txd.cost depreciation_cost,
txl.depreciation_id asset_category_id,
txd.deprn_method_tax deprn_method,
txd.deprn_rate_tax deprn_rate,
txd.salvage_value salvage_value,
txd.tax_book book_type_code,
txd.asset_number asset_number,
txl.kle_id kle_id,
--BUG# 3548044
null tas_id,
txl.salvage_value corp_salvage_value,
--Bug# 4028371
null tal_id,
--Bug# 3950089
txl.percent_salvage_value pct_salvage_value,
txl.percent_salvage_value corp_pct_salvage_value,
--Bug# 5207066
null,
cle.cle_id rbk_fin_ast_cle_id,
-- akrangan Bug# 5362977 start
null model_number,
null manufacturer_name,
null description,
-- akrangan Bug# 5362977 end
--Bug# 6373605 start
txd.id sla_source_line_id,
txl.tas_id sla_source_header_id,
'OKL_TXD_ASSETS_B' sla_source_line_table,
tas.try_id sla_source_try_id
--Bug# 6373605 end
FROM OKL_TXD_ASSETS_B txd,
OKL_TXL_ASSETS_B txl,
--Bug# 6373605 start
OKL_TRX_ASSETS tas,
--Bug# 6373605 end
OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse
WHERE txd.tal_id = txl.id
--Bug# 6373605 start
AND tas.id = txl.tas_id
--Bug# 6373605
AND txl.kle_id = cle.id
AND cle.dnz_chr_id = p_rbk_chr_id
AND cle.lse_id = lse.id
--effectivity
--Bug# 2942543 : effectivity should be checked by keeping start and end date as inclusive
--And nvl(cle.start_date,p_effective_date) <= p_effective_date
--And nvl(cle.end_date,p_effective_date+1) > p_effective_date
And p_effective_date between cle.start_date and cle.end_date
AND not exists (select '1'
from OKC_STATUSES_B sts
Where sts.code = cle.sts_code
--Bug# 2522268
--And sts.ste_code in ('HOLD','EXPIRED','TERMINATED','CANCELED')
And sts.ste_code in ('HOLD','EXPIRED','TERMINATED','CANCELLED')
)
AND lse.lty_code = G_FA_LINE_LTY_CODE;
SELECT okx.acquisition_date in_service_date,
okx.life_in_months life_in_months,
okx.cost cost,
okx.depreciation_category depreciation_category,
okx.deprn_method_code deprn_method_code,
okx.adjusted_rate adjusted_rate,
okx.basic_rate basic_rate,
okx.salvage_value salvage_value,
okx.book_type_code book_type_code,
okx.book_class book_class,
okx.asset_number asset_number,
okx.asset_id asset_id,
--Bug# 3950089
okx.percent_salvage_value percent_salvage_value,
--Bug# 5207066
okx.current_units fa_current_units
FROM okx_ast_bks_v okx
WHERE okx.asset_number = p_asset_number
AND okx.book_type_code = nvl(p_book_type_code,okx.book_type_code);
Select '!'
from OKC_K_LINES_B cle,
OKC_STATUSES_B sts,
OKC_K_ITEMS cim
Where cle.sts_code = sts.CODE
--Bug# 2942543 : effectivity should be checked by keeping start and end date as inclusive
--And nvl(sts.start_date,p_effective_date) <= p_effective_date
--And nvl(sts.end_date,p_effective_date+1) > p_effective_date
--And nvl(cle.start_date,p_effective_date) <= p_effective_date
--And nvl(cle.end_date,p_effective_date+1) > p_effective_date
And p_effective_date between cle.start_date and cle.end_date
And cle.id = cim.cle_id
And cle.dnz_chr_id = p_chr_id
And cim.dnz_chr_id = p_chr_id
And cim.object1_id1 = p_asset_id1
And cim.object1_id2 = p_asset_id2
And cim.jtot_object1_code = 'OKX_ASSET'
--Bug# 2522268
--And sts.ste_code not in ('HOLD','EXPIRED','TERMINATED','CANCELED');
Select clev.id new_fin_cle_id,
fa_cle.id new_fa_cle_id,
clev.chr_id orig_chr_id
From okc_k_lines_v clev,
okc_line_styles_b lse,
okc_k_lines_b fa_cle,
okc_line_styles_b fa_lse,
okc_k_headers_b chr
where clev.chr_id = chr.orig_system_id1
and clev.dnz_chr_id = chr.orig_system_id1
and chr.id = p_rbk_chr_id
and chr.orig_system_source_code = 'OKL_REBOOK'
and exists (select null
from okc_k_lines_v rbk_line
where rbk_line.chr_id = chr.id
and rbk_line.dnz_chr_id = chr.id
and rbk_line.lse_id = clev.lse_id
and rbk_line.name = clev.name
and rbk_line.id = clev.orig_system_id1)
and clev.lse_id = lse.id
and lse.lty_code = G_FIN_AST_LINE_LTY_CODE
and fa_cle.cle_id = clev.id
and fa_cle.dnz_chr_id = chr.orig_system_id1
and fa_cle.lse_id = fa_lse.id
and fa_lse.lty_code = G_FA_LINE_LTY_CODE;
Select rule_information1 tax_owner,
id
From okc_rules_b rul
where rul.dnz_chr_id = pchrid
and rul.rule_information_category = 'LATOWN'
and nvl(rul.STD_TEMPLATE_YN,'N') = 'N';
okl_tal_pvt.update_row
(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_talv_rec => l_talv_date_rec,
x_talv_rec => lx_talv_date_rec);
FIXED_ASSET_UPDATE_DESC
(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 => okx_ast_rec.asset_id,
p_model_number => adj_txl_rec.model_number,
p_manufacturer => adj_txl_rec.manufacturer_name,
p_description => adj_txl_rec.description,
p_trx_date => l_date_trx_occured,
p_trx_number => l_trx_number,
p_calling_interface => l_calling_interface,
--Bug# 4028371
x_fa_trx_date => l_fa_adj_date);
update_trx_status(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_tas_id => adj_txl_rec.tas_id,
p_tsu_code => G_TSU_CODE_PROCESSED);
SELECT ktrx.rbr_code,
ktrx.date_transaction_occurred,
khr.deal_type,
chr.id,
chr.sts_code,
rul.rule_information1,
khr.pdt_id,
chr.start_date,
--Bug# 3156924
ktrx.trx_number
FROM OKC_RULES_B rul,
OKL_K_HEADERS khr,
OKC_K_HEADERS_B chr,
OKL_TRX_CONTRACTS ktrx
WHERE rul.dnz_chr_id = chr.id
AND rul.rule_information_category = 'LATOWN'
AND khr.id = chr.id
AND chr.id = p_rbk_chr_id
AND exists (select null
from okl_trx_types_tl tl
where tl.language = 'US'
and tl.name = 'Rebook'
and tl.id = ktrx.try_id)
AND ktrx.KHR_ID = chr.id
AND ktrx.KHR_ID = rul.dnz_chr_id
--rkuttiya added for 12.1.1 Multi GAAP Project
AND ktrx.representation_type = 'PRIMARY'
--
AND ktrx.KHR_ID_NEW is null
AND ktrx.tsu_code = G_TSU_CODE_ENTERED;
SELECT kle.OEC,
kle.RESIDUAL_VALUE,
cle.id,
cle.name
FROM OKC_K_LINES_V cle,
OKC_LINE_STYLES_B lse,
OKL_K_LINES kle,
OKC_K_LINES_B fa_cle,
OKC_LINE_STYLES_B fa_cle_lse
WHERE kle.id = cle.id
AND cle.chr_id = p_chr_id
AND cle.dnz_chr_id = p_chr_id
AND cle.lse_id = lse.id
AND lse.lty_code = G_FIN_AST_LINE_LTY_CODE
AND cle.id = fa_cle.cle_id
AND fa_cle.id = nvl(p_fa_cle_id,fa_cle.id)
AND fa_cle.lse_id = fa_cle_lse.id
AND fa_cle_lse.lty_code = G_FA_LINE_LTY_CODE
--Bug# 2942543 : effectivity should be checked by keeping start and end date as inclusive
--AND nvl(cle.start_date,p_effective_date) <= p_effective_date
--AND nvl(cle.end_date,p_effective_date+1) > p_effective_date
AND p_effective_date between cle.start_date and cle.end_date
AND not exists (select '1'
from OKC_STATUSES_B sts
Where sts.code = cle.sts_code
--Bug# 2522268
--And sts.ste_code in ('HOLD','EXPIRED','TERMINATED','CANCELED'));
SELECT txl.in_service_date in_service_date,
txl.life_in_months life_in_months,
txl.depreciation_cost depreciation_cost,
txl.depreciation_id asset_category_id,
txl.deprn_method deprn_method,
txl.deprn_rate deprn_rate,
txl.salvage_value salvage_value,
txl.corporate_book book_type_code,
txl.asset_number asset_number,
txl.kle_id kle_id,
--Bug# 3548044
txl.tas_id tas_id,
txl.salvage_value corp_salvage_value,
--Bug# 4028371
txl.id tal_id,
--Bug# 6373605 start
txl.id sla_source_line_id,
txl.tas_id sla_source_header_id,
'OKL_TXL_ASSETS_B' sla_source_line_table,
tas.try_id sla_source_try_id,
cle.cle_id sla_source_kle_id
--Bug# 6373605 end
FROM OKL_TXL_ASSETS_B txl,
--Bug# 6373605 start
OKL_TRX_ASSETS tas,
--Bug# 6373605 end
OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse
WHERE txl.kle_id = cle.id
AND txl.tal_type = 'CRB'
--Bug# 6373605 start
AND tas.id = txl.tas_id
--Bug# 6373605 end
AND cle.dnz_chr_id = p_rbk_chr_id
AND cle.lse_id = lse.id
--effectivity
--Bug# 2942543 : effectivity should be checked by keeping start and end date as inclusive
--And nvl(cle.start_date,p_effective_date) <= p_effective_date
--And nvl(cle.end_date,p_effective_date+1) > p_effective_date
AND p_effective_date between cle.start_date and cle.end_date
AND not exists (select '1'
from OKC_STATUSES_B sts
Where sts.code = cle.sts_code
--Bug# 2522268
--And sts.ste_code in ('HOLD','EXPIRED','TERMINATED','CANCELED')
And sts.ste_code in ('HOLD','EXPIRED','TERMINATED','CANCELLED')
)
AND lse.lty_code = G_FA_LINE_LTY_CODE
AND exists (select '1'
from OKL_TRX_ASSETS TRX
Where TRX.ID = Txl.tas_id
And TRX.tas_type = 'CRB'
And TRX.tsu_code = 'ENTERED')
UNION
SELECT txl.in_service_date in_service_date,
txd.life_in_months_tax life_in_months,
txd.cost depreciation_cost,
txl.depreciation_id asset_category_id,
txd.deprn_method_tax deprn_method,
txd.deprn_rate_tax deprn_rate,
txd.salvage_value salvage_value,
txd.tax_book book_type_code,
txd.asset_number asset_number,
txl.kle_id kle_id,
--bug# 3548044
null tas_id,
txl.salvage_value corp_salvage_value,
--Bgu# 4028371
null tal_id,
--Bug# 6373605 start
txd.id sla_source_line_id,
txl.tas_id sla_source_header_id,
'OKL_TXD_ASSETS_B' sla_source_line_table,
tas.try_id sla_source_try_id,
cle.cle_id sla_source_kle_id
--Bug# 6373605 end
FROM OKL_TXD_ASSETS_B txd,
OKL_TXL_ASSETS_B txl,
--Bug# 6373605 start
OKL_TRX_ASSETS tas,
--Bug# 6373605 end
OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse
WHERE txd.tal_id = txl.id
--Bug# 6373605 start
AND tas.id = txl.tas_id
--Bug# 6373605
AND txl.kle_id = cle.id
AND cle.dnz_chr_id = p_rbk_chr_id
AND cle.lse_id = lse.id
--effectivity
--Bug# 2942543 : effectivity should be checked by keeping start and end date as inclusive
--And nvl(cle.start_date,p_effective_date) <= p_effective_date
--And nvl(cle.end_date,p_effective_date+1) > p_effective_date
AND p_effective_date between cle.start_date and cle.end_date
AND not exists (select '1'
from OKC_STATUSES_B sts
Where sts.code = cle.sts_code
--Bug# 2522268
--And sts.ste_code in ('HOLD','EXPIRED','TERMINATED','CANCELED')
And sts.ste_code in ('HOLD','EXPIRED','TERMINATED','CANCELLED')
)
AND lse.lty_code = G_FA_LINE_LTY_CODE
AND exists (select '1'
from OKL_TRX_ASSETS TRX
Where TRX.ID = Txl.tas_id
And TRX.tas_type = 'CRB'
And TRX.tsu_code = 'ENTERED');
SELECT okx.acquisition_date in_service_date,
okx.life_in_months life_in_months,
okx.cost cost,
okx.depreciation_category depreciation_category,
okx.deprn_method_code deprn_method_code,
okx.adjusted_rate adjusted_rate,
okx.basic_rate basic_rate,
okx.salvage_value salvage_value,
okx.book_type_code book_type_code,
okx.book_class book_class,
okx.asset_number asset_number,
okx.asset_id asset_id
FROM okx_ast_bks_v okx
WHERE okx.asset_number = p_asset_number
AND okx.book_type_code = nvl(p_book_type_code,okx.book_type_code);
Select '!'
from OKC_K_LINES_B cle,
OKC_STATUSES_B sts,
OKC_K_ITEMS cim
Where cle.sts_code = sts.CODE
--Bug# 2942543 : effectivity should be checked by keeping start and end date as inclusive
--And nvl(sts.start_date,p_effective_date) <= p_effective_date
--And nvl(sts.end_date,p_effective_date+1) > p_effective_date
--And nvl(cle.start_date,p_effective_date) <= p_effective_date
--And nvl(cle.end_date,p_effective_date+1) > p_effective_date
And p_effective_date between cle.start_date and cle.end_date
And cle.id = cim.cle_id
And cle.dnz_chr_id = p_chr_id
And cim.dnz_chr_id = p_chr_id
And cim.object1_id1 = p_asset_id1
And cim.object1_id2 = p_asset_id2
And cim.jtot_object1_code = 'OKX_ASSET'
--Bug #2522268
--And sts.ste_code not in ('HOLD','EXPIRED','TERMINATED','CANCELED');
Select rule_information1 tax_owner,
id
From okc_rules_b rul
where rul.dnz_chr_id = pchrid
and rul.rule_information_category = 'LATOWN'
and nvl(rul.STD_TEMPLATE_YN,'N') = 'N';
okl_tal_pvt.update_row
(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_talv_rec => l_talv_date_rec,
x_talv_rec => lx_talv_date_rec);
update_trx_status(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_tas_id => adj_txl_rec.tas_id,
p_tsu_code => G_TSU_CODE_PROCESSED);
SELECT khr.deal_type,
chr.id,
chr.sts_code,
rul.rule_information1,
chr.orig_system_id1,
khr.pdt_id,
chr.start_date,
--Bug# 4631549
chr.orig_system_source_code
FROM OKC_RULES_B rul,
OKL_K_HEADERS khr,
OKC_K_HEADERS_B chr
WHERE rul.dnz_chr_id = chr.id
AND rul.rule_information_category = 'LATOWN'
AND rul.dnz_chr_id = khr.id
AND khr.id = chr.id
AND chr.id = p_rel_chr_id
--Bug#2522439
--AND chr.orig_system_source_code = 'OKL_RELEASE';
AND exists (SELECT '1'
FROM OKC_RULES_B rul_rel_Ast
WHERE rul_rel_ast.dnz_chr_id = chr.id
AND rul_rel_ast.rule_information_category = 'LARLES'
AND nvl(rule_information1,'N') = 'Y');
SELECT txl.in_service_date in_service_date,
txl.life_in_months life_in_months,
txl.depreciation_cost depreciation_cost,
txl.depreciation_id asset_category_id,
txl.deprn_method deprn_method,
txl.deprn_rate deprn_rate,
txl.salvage_value salvage_value,
txl.corporate_book book_type_code,
txl.asset_number asset_number,
txl.kle_id kle_id,
--Bug# 3156924
trx.trans_number,
--Bug# 3533936
txl.fa_location_id fa_location_id,
trx.id tas_id,
txl.salvage_value corp_salvage_value,
--Bug# 3631094
txl.percent_salvage_value corp_percent_sv,
txl.corporate_book corp_book,
fab.book_class book_class,
--Bug# 4028371
txl.id tal_id,
--Bug# 3950089
txl.percent_salvage_value pct_salvage_value,
txl.percent_salvage_value corp_pct_salvage_value,
--Bug# 6373605 start
trx.id sla_source_header_id,
txl.id sla_source_line_id,
'OKL_TXL_ASSETS_B' sla_source_line_table,
trx.try_id sla_source_try_id,
cle.cle_id sla_source_kle_id
--Bug# 6373605 end
FROM OKL_TRX_TYPES_TL ttyp,
OKL_TRX_ASSETS trx,
OKL_TXL_ASSETS_B txl,
OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse,
-- Bug# 3631094
FA_BOOK_CONTROLS fab
WHERE txl.kle_id = cle.id
AND cle.dnz_chr_id = p_rel_chr_id
AND cle.lse_id = lse.id
--effectivity
--And nvl(cle.start_date,p_effective_date) <= p_effective_date
--And nvl(cle.end_date,p_effective_date+1) > p_effective_date
AND not exists (select '1'
from OKC_STATUSES_B sts
Where sts.code = cle.sts_code
--Bug# 2522268
--And sts.ste_code in ('HOLD','EXPIRED','TERMINATED','CANCELED')
And sts.ste_code in ('HOLD','EXPIRED','TERMINATED','CANCELLED')
)
AND lse.lty_code = G_FA_LINE_LTY_CODE
--Bug# 3156924:
/*----------------------------------------------------
--AND exists (select null
--from OKL_TRX_ASSETS trx,
--OKL_TRX_TYPES_TL ttyp
--where trx.id = txl.tas_id
--and trx.try_id = ttyp.id
--and ttyp.name = 'Internal Asset Creation'
--and ttyp.language = 'US'
--and trx.tsu_code <> 'PROCESSED'
--Bug#2522439
----and trx.tas_type = G_TRX_HDR_TYPE_RELEASE)
--and trx.tas_type = G_TRX_HDR_TYPE_BOOK)
-------------------------------------------------------*/
AND trx.id = txl.tas_id
and trx.try_id = ttyp.id
and ttyp.name = 'Internal Asset Creation'
and ttyp.language = 'US'
and trx.tsu_code <> 'PROCESSED'
--Bug#2522439
--and trx.tas_type = G_TRX_HDR_TYPE_RELEASE)
and trx.tas_type = G_TRX_HDR_TYPE_RELEASE
AND txl.tal_type = G_TRX_LINE_TYPE_RELEASE
--Bug# 3631094
AND fab.book_type_code = txl.corporate_book
UNION
SELECT txl.in_service_date in_service_date,
txd.life_in_months_tax life_in_months,
txd.cost depreciation_cost,
txl.depreciation_id asset_category_id,
txd.deprn_method_tax deprn_method,
txd.deprn_rate_tax deprn_rate,
txd.salvage_value salvage_value,
txd.tax_book book_type_code,
txd.asset_number asset_number,
txl.kle_id kle_id,
--Bug# 3156924
trx.trans_number,
--Bug# 3533936
null fa_location_id,
null tas_id,
txl.salvage_value corp_salvage_value,
--Bug# 3631094
txl.percent_salvage_value corp_percent_sv,
txl.corporate_book corp_book,
fab.book_class book_class,
--Bug# 4028371
null tal_id,
--Bug# 3950089
txl.percent_salvage_value pct_salvage_value,
txl.percent_salvage_value corp_pct_salvage_value,
--bug# 6373605 start
trx.id sla_source_header_id,
txd.id sla_source_line_id,
'OKL_TXD_ASSETS_B' sla_source_line_table,
trx.try_id sla_source_try_id,
cle.cle_id sla_source_kle_id
--Bug# 6373605 end
FROM OKL_TRX_TYPES_TL ttyp,
OKL_TRX_ASSETS trx,
OKL_TXD_ASSETS_B txd,
OKL_TXL_ASSETS_B txl,
OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse,
-- Bug# 3631094
FA_BOOK_CONTROLS fab
WHERE txd.tal_id = txl.id
AND txl.kle_id = cle.id
AND cle.dnz_chr_id = p_rel_chr_id
AND cle.lse_id = lse.id
--effectivity
--And nvl(cle.start_date,p_effective_date) <= p_effective_date
--And nvl(cle.end_date,p_effective_date+1) > p_effective_date
AND not exists (select '1'
from OKC_STATUSES_B sts
Where sts.code = cle.sts_code
--Bug#2522268
--And sts.ste_code in ('HOLD','EXPIRED','TERMINATED','CANCELED')
And sts.ste_code in ('HOLD','EXPIRED','TERMINATED','CANCELLED')
)
AND lse.lty_code = G_FA_LINE_LTY_CODE
--bug# 3156924
/*-------------------------------------------------------------
--AND exists (select null
--from OKL_TRX_ASSETS trx,
--OKL_TRX_TYPES_TL ttyp
--where trx.id = txl.tas_id
--and trx.try_id = ttyp.id
--and ttyp.name = 'Release'
--and ttyp.language = 'US'
--and trx.tsu_code <> 'PROCESSED'
--and trx.tas_type = G_TRX_HDR_TYPE_RELEASE)
---------------------------------------------------------------*/
AND trx.id = txl.tas_id
AND trx.try_id = ttyp.id
and ttyp.name = 'Internal Asset Creation'
and ttyp.language = 'US'
and trx.tsu_code <> 'PROCESSED'
and trx.tas_type = G_TRX_HDR_TYPE_RELEASE
--bug# 3156924
AND txl.tal_type = G_TRX_LINE_TYPE_RELEASE
--Bug# 3631094
AND fab.book_type_code = txd.tax_book
ORDER BY asset_number, book_class ;
SELECT okx.acquisition_date in_service_date,
okx.life_in_months life_in_months,
okx.cost cost,
okx.depreciation_category depreciation_category,
okx.deprn_method_code deprn_method_code,
okx.adjusted_rate adjusted_rate,
okx.basic_rate basic_rate,
okx.salvage_value salvage_value,
--Bug# 3631094
okx.percent_salvage_value percent_salvage_value,
okx.book_type_code book_type_code,
okx.book_class book_class,
okx.asset_number asset_number,
okx.asset_id asset_id
FROM okx_ast_bks_v okx
WHERE okx.asset_number = p_asset_number
AND okx.book_type_code = nvl(p_book_type_code,okx.book_type_code);
Select rule_information1 tax_owner,
id
From okc_rules_b rul
where rul.dnz_chr_id = pchrid
and rul.rule_information_category = 'LATOWN'
and nvl(rul.STD_TEMPLATE_YN,'N') = 'N';
select cleb.id,
--Bug# 3783518
cleb.orig_system_id1
from okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_statuses_b stsb
where cleb.chr_id = p_chr_id
and cleb.dnz_chr_id = p_chr_id
and lseb.id = cleb.lse_id
and lseb.lty_code = G_FIN_AST_LINE_LTY_CODE
and stsb.code = cleb.sts_code
and stsb.ste_code not in ('HOLD','EXPIRED','TERMINATED','CANCELLED');
select fab.asset_id asset_id,
ast_clet.name asset_number,
fab.cost asset_cost,
fab.book_type_code book_type_code,
txl.in_service_date in_service_date,
--bug# 3156924
trx.trans_number,
--Bug# 6373605 start
trx.id sla_source_header_id,
txl.id sla_source_line_id,
trx.try_id sla_source_try_id,
fa_cleb.cle_id sla_source_kle_id,
fbc.book_class
--Bug# 6373605 end
from
fa_books fab,
--Bug# 6373605 start
fa_book_controls fbc,
--Bug# 6373605 end
okc_k_items fa_cim,
okl_trx_types_tl ttyp,
okl_trx_assets trx,
okl_txl_assets_b txl,
okc_k_lines_b fa_cleb,
okc_line_styles_b fa_lseb,
okc_k_lines_tl ast_clet
where
fab.asset_id = to_number(fa_cim.object1_id1)
and fab.transaction_header_id_out is NULL
--Bug# 6373605 start
and fab.book_type_code = fbc.book_type_code
--bug# 6373605 end
and fa_cim.object1_id2 = '#'
and fa_cim.jtot_object1_code = 'OKX_ASSET'
and fa_cim.dnz_chr_id = fa_cleb.dnz_chr_id
and fa_cim.cle_id = fa_cleb.id
and txl.kle_id = fa_cleb.id
and txl.tal_type = G_TRX_LINE_TYPE_RELEASE
and trx.id = txl.tas_id
and trx.try_id = ttyp.id
--Bug# 2981308
and ttyp.name = 'Internal Asset Creation'
--and ttyp.name = 'Release'
and ttyp.language = 'US'
and trx.tsu_code <> 'PROCESSED'
--Bug# 2981308
and trx.tas_type = G_TRX_HDR_TYPE_RELEASE
--and trx.tas_type = G_TRX_HDR_TYPE_RELEASE
and fa_cleb.cle_id = p_asset_cle_id
and fa_lseb.id = fa_cleb.lse_id
and fa_lseb.lty_code = G_FA_LINE_LTY_CODE
and ast_clet.id = p_asset_cle_id
and ast_clet.language = userenv('LANG');
select id sla_source_line_id,
tax_book
from okl_txd_Assets_b
where tal_id = p_tal_id
and tax_book = p_book_type_code;
SELECT trx.id
FROM OKL_TRX_ASSETS trx,
OKL_TXL_ASSETS_B txl,
OKC_K_LINES_B cle,
OKC_LINE_STYLES_B lse
WHERE txl.kle_id = cle.id
AND cle.dnz_chr_id = p_chr_id
AND cle.lse_id = lse.id
AND not exists (select '1'
from OKC_STATUSES_B sts
Where sts.code = cle.sts_code
And sts.ste_code in ('HOLD','EXPIRED','TERMINATED','CANCELLED')
)
AND lse.lty_code = G_FA_LINE_LTY_CODE
AND trx.id = txl.tas_id
and trx.tsu_code <> 'PROCESSED'
and trx.tas_type = G_TRX_HDR_TYPE_RELEASE
AND txl.tal_type = G_TRX_LINE_TYPE_RELEASE;
SELECT '!'
FROM OKX_AST_BKS_V
WHERE asset_id = p_asset_id
AND book_type_code = p_book_type_code
AND status = 'A';
SELECT '!'
FROM OKX_AST_CAT_BKS_V
WHERE CATEGORY_ID = p_category_id
AND BOOK_TYPE_CODE = p_book_type_code
AND STATUS = 'A';
SELECT '!'
FROM OKX_ASST_BK_CONTROLS_V
WHERE book_type_code = p_tax_book
AND book_class = 'TAX'
AND mass_copy_source_book = p_corp_book
AND allow_mass_copy = 'YES'
AND copy_additions_flag = 'YES';
select book_type_code,
cost,
salvage_value,
percent_salvage_value
from okx_ast_bks_v
where asset_id = p_Asset_id
and book_class = p_book_class;
SELECT pdt.reporting_pdt_id
FROM okc_k_lines_b cle,
okl_k_headers khr,
okl_products pdt
WHERE cle.id = p_cle_id
AND khr.id = cle.dnz_chr_id
AND pdt.id = khr.pdt_id;
Select nvl(sum(cov_ast_kle.capital_amount),0) capitalized_fee
From
OKL_K_LINES fee_kle,
OKC_K_LINES_B fee_cle,
OKC_STATUSES_B fee_sts,
OKL_K_LINES cov_ast_kle,
OKC_K_LINES_B cov_ast_cle,
OKC_LINE_STYLES_B cov_ast_lse,
OKC_STATUSES_B cov_ast_sts,
OKC_K_ITEMS cov_ast_cim,
OKC_K_LINES_B fa_cle
Where fee_kle.id = fee_cle.id
and fee_kle.fee_type = 'CAPITALIZED'
and fee_cle.id = cov_ast_cle.cle_id
and fee_cle.dnz_chr_id = cov_ast_cle.dnz_chr_id
and fee_cle.sts_code = fee_sts.code
and fee_sts.ste_code not in ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
and cov_ast_kle.id = cov_ast_cle.id
and cov_ast_cle.id = cov_ast_cim.cle_id
and cov_ast_cle.lse_id = cov_ast_lse.id
and cov_ast_lse.lty_code = 'LINK_FEE_ASSET'
and cov_ast_cle.sts_code = cov_ast_sts.code
and cov_ast_sts.ste_code not in ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
and cov_ast_cle.dnz_chr_id = cov_ast_cim.dnz_chr_id
and cov_ast_cim.object1_id1 = to_char(fa_cle.cle_id)
and cov_ast_cim.object1_id2 = '#'
and cov_ast_cim.jtot_object1_code = 'OKX_COVASST'
and fa_cle.id = fa_cle_id
and fee_cle.dnz_chr_id = rel_chr_id ;
okl_tal_pvt.update_row
(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_talv_rec => l_talv_date_rec,
x_talv_rec => lx_talv_date_rec);
okl_tal_pvt.update_row
(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_talv_rec => l_talv_date_rec,
x_talv_rec => lx_talv_date_rec);
update_trx_status(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_tas_id => adj_txl_rec.tas_id,
p_tsu_code => G_TSU_CODE_PROCESSED);
update_trx_status(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_tas_id => tas_rec.id,
p_tsu_code => G_TSU_CODE_PROCESSED);
select ttyt.name transaction_type,
trx.tas_type,
txl.tal_type,
txl.id tal_id,
trx.creation_date
from okl_trx_types_tl ttyt,
okl_trx_assets trx,
okl_txl_assets_b txl,
okc_k_lines_b cleb,
okc_line_styles_b lseb
where ttyt.id = trx.try_id
and ttyt.language = 'US'
and trx.id = txl.tas_id
and trx.tsu_code = 'ENTERED'
and txl.kle_id = cleb.id
and cleb.cle_id = p_cle_id
and cleb.dnz_chr_id = p_chr_id
and cleb.lse_id = lseb.id
and lseb.lty_code = 'FIXED_ASSET'
order by trx.creation_date desc;
SELECT 'Y'
FROM okc_k_headers_b CHR,
okl_trx_contracts ktrx
where CHR.ID = p_chr_id
AND ktrx.KHR_ID = chr.id
AND ktrx.tsu_code = 'ENTERED'
AND ktrx.rbr_code IS NOT NULL
AND ktrx.tcn_type = 'TRBK'
--rkuttiya added for 12.1.1 Multi GAAP Proect
AND ktrx.representation_type = 'PRIMARY'
--
AND EXISTS (SELECT '1'
FROM okl_rbk_selected_contract rbk_khr
WHERE rbk_khr.KHR_ID = chr.id
AND rbk_khr.STATUS <> 'PROCESSED');
select fab.*,
fa.asset_category_id,
fbc.book_class,
fa.asset_number
from fa_additions fa,
fa_books fab,
fa_book_controls fbc,
okc_k_items cim,
okc_k_lines_b cleb,
okc_line_styles_b lseb
where fab.asset_id = fa.asset_id
and fab.transaction_header_id_out is null
and fbc.book_type_code = fab.book_type_code
and fa.asset_id = cim.object1_id1
and cim.object1_id2 = '#'
and cim.jtot_object1_code = 'OKX_ASSET'
and cim.cle_id = cleb.id
and cim.dnz_chr_id = cleb.dnz_chr_id
and cleb.cle_id = p_cle_id
and cleb.lse_id = lseb.id
and cleb.dnz_chr_id = p_chr_id
and lseb.lty_code = 'FIXED_ASSET';
select khr.pdt_id,
khr.deal_type,
chrb.start_date,
rul.rule_information1
from okc_rules_b rul,
okc_rule_groups_b rgp,
okl_k_headers khr,
okc_k_headers_b chrb
where rul.rule_information_category = 'LATOWN'
and rul.rgp_id = rgp.id
and rul.dnz_chr_id = rgp.dnz_chr_id
and rgp.dnz_chr_id = chrb.id
and rgp.chr_id = chrb.id
and rgp.rgd_code = 'LATOWN'
and khr.id = chrb.id
and chrb.id = p_chr_id;
select txl.asset_number,
txl.corporate_book,
fbc.book_class,
txl.deprn_method,
txl.in_service_date,
txl.life_in_months,
txl.deprn_rate,
txl.salvage_value,
txl.percent_salvage_value,
fcbd.prorate_convention_code,
txl.depreciation_cost,
txl.depreciation_id,
txl.kle_id
from okl_txl_Assets_b txl,
fa_book_controls fbc,
fa_category_book_defaults fcbd
where fcbd.category_id = txl.depreciation_id
and fcbd.book_type_code = txl.corporate_book
and txl.in_service_date between fcbd.start_dpis and nvl(fcbd.end_dpis,txl.in_service_date)
and fbc.book_type_code = txl.corporate_book
and txl.id = p_tal_id;
select txl.asset_number,
txl.corporate_book,
fbc.book_class,
txl.deprn_method,
txl.in_service_date,
txl.life_in_months,
txl.deprn_rate,
txl.salvage_value,
txl.percent_salvage_value,
fcbd.prorate_convention_code,
txl.depreciation_cost,
txl.depreciation_id,
txl.kle_id
from okl_txl_Assets_b txl,
fa_book_controls fbc,
fa_category_book_defaults fcbd,
fa_additions fa
where fcbd.category_id = fa.asset_category_id
and fcbd.book_type_code = txl.corporate_book
and fa.asset_number = txl.asset_number
and txl.in_service_date between fcbd.start_dpis and nvl(fcbd.end_dpis,txl.in_service_date)
and fbc.book_type_code = txl.corporate_book
and txl.id = p_tal_id;
select txd.tax_book,
fbc.book_class,
txd.deprn_method_tax,
txd.life_in_months_tax,
txd.deprn_rate_tax,
fcbd.prorate_convention_code,
txd.cost
from okl_txd_Assets_b txd,
fa_book_controls fbc,
fa_category_book_defaults fcbd
where fcbd.category_id = p_category_id
and fcbd.book_type_code = txd.tax_book
and p_in_service_date between fcbd.start_dpis and nvl(fcbd.end_dpis,p_in_service_date)
and fbc.book_type_code = txd.tax_book
and txd.tal_id = p_tal_id;
select txd.tax_book,
fbc.book_class,
txd.deprn_method_tax,
txd.life_in_months_tax,
txd.deprn_rate_tax,
fcbd.prorate_convention_code,
txd.cost
from okl_txd_Assets_b txd,
fa_book_controls fbc,
fa_category_book_defaults fcbd
where fcbd.category_id = p_category_id
and fcbd.book_type_code = txd.tax_book
and p_in_service_date between fcbd.start_dpis and nvl(fcbd.end_dpis,p_in_service_date)
and fbc.book_type_code = txd.tax_book
and txd.tal_id = p_tal_id
and txd.tax_book = p_book;
select fm.method_id
from fa_methods fm
where fm.method_code = p_method_code
and fm.life_in_months = p_life
and fm.life_in_months is not null;
select fm.method_id
from fa_methods fm
where fm.method_code = p_method_code
and fm.life_in_months is null
and exists (select 1
from fa_flat_rates ffr
where ffr.method_id = fm.method_id
and ffr.basic_rate = p_basic_rate
and ffr.adjusted_rate = p_adj_rate);
select deprn_method,
life_in_months,
basic_rate,
adjusted_rate,
prorate_convention_code
from fa_category_book_defaults
where book_type_code = p_book
and category_id = p_category_id
and p_date between start_dpis and nvl(end_dpis,p_date);
select fab.*,
fa.asset_category_id
from fa_books fab,
fa_additions fa
where fab.book_type_code = p_book
and fab.asset_id = fa.asset_id
and fa.asset_number = p_asset_number
and fab.transaction_header_id_out is null;
select name
from okc_k_lines_v
where id = p_cle_id;
select id,EXPECTED_ASSET_COST
from okl_k_lines_v
where id = p_cle_id;
select id, ORIG_SYSTEM_SOURCE_CODE
from okc_k_headers_b
where id = p_chr_id;
Select nvl(sum(cov_ast_kle.capital_amount),0) capitalized_fee
From
OKL_K_LINES fee_kle,
OKC_K_LINES_B fee_cle,
OKC_STATUSES_B fee_sts,
OKL_K_LINES cov_ast_kle,
OKC_K_LINES_B cov_ast_cle,
OKC_STATUSES_B cov_ast_sts,
OKC_K_ITEMS cov_ast_cim
Where fee_kle.id = fee_cle.id
and fee_kle.fee_type = 'CAPITALIZED'
and fee_cle.dnz_chr_id = p_chr_id
and fee_cle.chr_id = p_chr_id
and fee_cle.lse_id = 52 -- FEE
and fee_cle.sts_code = fee_sts.code
and fee_sts.ste_code not in ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
and fee_cle.id = cov_ast_cle.cle_id
and fee_cle.dnz_chr_id = cov_ast_cle.dnz_chr_id
and cov_ast_kle.id = cov_ast_cle.id
and cov_ast_cle.id = cov_ast_cim.cle_id
and cov_ast_cle.lse_id = 53 --LINK_FEE_ASSET
and cov_ast_cle.sts_code = cov_ast_sts.code
and cov_ast_sts.ste_code not in ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
and cov_ast_cle.dnz_chr_id = cov_ast_cim.dnz_chr_id
and cov_ast_cim.object1_id1 = to_char(p_fin_ast_cle_id)
and cov_ast_cim.object1_id2 = '#'
and cov_ast_cim.jtot_object1_code = 'OKX_COVASST';
SELECT '!'
FROM okc_k_headers_b CHR,
okl_trx_contracts ktrx
WHERE ktrx.khr_id_new = chr.id
AND ktrx.tsu_code = 'ENTERED'
AND ktrx.rbr_code is NOT NULL
AND ktrx.tcn_type = 'TRBK'
--rkuttiya added for 12.1.1 Multi GAAP Project
AND ktrx.representation_type = 'PRIMARY'
--
AND CHR.id = p_chr_id
AND CHR.ORIG_SYSTEM_SOURCE_CODE = 'OKL_REBOOK';
select ttyt.name transaction_type,
trx.tas_type,
txl.tal_type,
txl.id tal_id,
trx.creation_date
from okl_trx_types_tl ttyt,
okl_trx_assets trx,
okl_txl_assets_b txl,
okc_k_lines_b cleb,
okc_line_styles_b lseb
where ttyt.id = trx.try_id
and ttyt.language = 'US'
and trx.id = txl.tas_id
and trx.tsu_code = 'ENTERED'
and txl.kle_id = cleb.id
and cleb.cle_id = p_cle_id
and cleb.dnz_chr_id = p_chr_id
and cleb.lse_id = lseb.id
and lseb.lty_code = 'FIXED_ASSET'
order by trx.creation_date desc;
select khr.pdt_id,
khr.deal_type,
chrb.start_date,
rul.rule_information1
from okc_rules_b rul,
okc_rule_groups_b rgp,
okl_k_headers khr,
okc_k_headers_b chrb
where rul.rule_information_category = 'LATOWN'
and rul.rgp_id = rgp.id
and rul.dnz_chr_id = rgp.dnz_chr_id
and rgp.dnz_chr_id = chrb.id
and rgp.chr_id = chrb.id
and rgp.rgd_code = 'LATOWN'
and khr.id = chrb.id
and chrb.id = p_chr_id;
select txd.id,
txd.tax_book
from okl_txd_Assets_b txd
where txd.tal_id = p_tal_id;
select kle_fin.capital_amount
from okc_k_lines_b cleb_fin,
okl_k_lines kle_fin
where cleb_fin.id = p_fin_cle_id
and kle_fin.id = cleb_fin.id;
OKL_TXL_ASSETS_PUB.update_txl_asset_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_tlpv_rec => l_talv_rec,
x_tlpv_rec => lx_talv_rec);
OKL_TXD_ASSETS_PUB.UPDATE_TXD_ASSET_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_adpv_rec => l_txdv_rec,
x_adpv_rec => lx_txdv_rec);
OKL_TXL_ASSETS_PUB.update_txl_asset_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_tlpv_rec => l_talv_rec,
x_tlpv_rec => lx_talv_rec);
OKL_TXD_ASSETS_PUB.UPDATE_TXD_ASSET_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_adpv_rec => l_txdv_rec,
x_adpv_rec => lx_txdv_rec);
OKL_TXD_ASSETS_PUB.UPDATE_TXD_ASSET_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_adpv_rec => l_txdv_rec,
x_adpv_rec => lx_txdv_rec);