[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT nvl(tax_upfront_yn,'N')
FROM okl_system_params;
SELECT khr.pdt_id product_id
,NULL product_name
,khr.sts_code contract_status
,khr.start_date start_date
,khr.currency_code currency_code
,khr.authoring_org_id authoring_org_id
,khr.currency_conversion_rate currency_conversion_rate
,khr.currency_conversion_type currency_conversion_type
,khr.currency_conversion_date currency_conversion_date
,khr.scs_code scs_code
FROM okl_k_headers_full_v khr
WHERE khr.id = p_contract_id;
SELECT name product_name,
deal_type book_classification_code,
tax_owner tax_owner_code,
description description,
revenue_recognition_method revenue_recognition_method,
interest_calculation_basis interest_calculation_basis
FROM okl_product_parameters_v
WHERE id = p_product_id;
select description, lookup_code
from fnd_lookup_values
where language = 'US'
AND lookup_type = lkp_type
AND meaning = mng;
SELECT NVL(rule_information11,'BILLED') feetype
FROM okc_rules_b rl,
okc_rule_groups_b rgp,
okc_k_lines_b cle
WHERE rl.dnz_chr_id = p_contract_id
AND rl.rule_information_category = 'LAASTX'
AND NVL(rule_information11,'BILLED') <> 'BILLED'
AND rgp.id = rl.rgp_id
AND cle.id = rgp.cle_id
AND cle.sts_code <> 'ABANDONED';
SELECT rule_information1,
rule_information2,
rule_information3,
rule_information4,
rule_information5
FROM okc_rules_b rl
WHERE rl.dnz_chr_id = p_contract_id
AND rl.rule_information_category = 'LASTPR';
SELECT count(NVL(rule_information11, l_fee_type))
FROM okc_rules_b rl,
okc_rule_groups_b rgp,
okc_k_lines_b cle
WHERE rl.dnz_chr_id = p_contract_id
AND rl.rule_information_category = 'LAASTX'
AND NVL(rule_information11, l_fee_type) = l_fee_type
AND rgp.id = rl.rgp_id
AND cle.id = rgp.cle_id
AND cle.sts_code <> 'ABANDONED';
SELECT NVL(SUM(NVL(TOTAL_TAX,0)),0)
FROM okl_tax_sources txs,
okc_rule_groups_b rg1,
okc_rules_b rl1,
okc_rule_groups_b rg2,
okc_rules_b rl2,
okc_k_lines_b cle
WHERE txs.KHR_ID = p_contract_id
AND txs.khr_id = rg1.dnz_chr_id
AND txs.kle_id = rg1.cle_id
AND txs.khr_id = rl1.dnz_chr_id
AND rg1.rgd_code = 'LAASTX'
AND rg1.id = rl1.rgp_id
AND rl1.rule_information_category = 'LAASTX'
AND (rl1.rule_information11 = p_feetype
OR
rl1.rule_information11 IS NULL)
AND txs.TAX_LINE_STATUS_CODE = 'ACTIVE'
AND txs.TAX_CALL_TYPE_CODE = 'UPFRONT_TAX'
AND txs.KHR_ID = rg2.dnz_chr_id
AND txs.KHR_ID = rl2.dnz_chr_id
AND rg2.id = rl2.rgp_id
AND rg2.rgd_code = 'LAHDTX'
AND rl2.rule_information_category = 'LASTPR'
AND rl2.rule_information1 = p_feetype
AND cle.id = rg1.cle_id
AND cle.sts_code <> 'ABANDONED';
SELECT NVL(SUM(NVL(TOTAL_TAX,0)),0)
FROM okl_tax_sources txs
,okc_rule_groups_b rg1
,okc_rules_b rl1
,okc_k_lines_b cle
WHERE txs.KHR_ID = p_Contract_id
AND txs.khr_id = rg1.dnz_chr_id
AND txs.kle_id = rg1.cle_id
AND txs.khr_id = rl1.dnz_chr_id
AND rg1.rgd_code = 'LAASTX'
AND rg1.id = rl1.rgp_id
AND rl1.rule_information_category = 'LAASTX'
AND rl1.rule_information11 = p_feetype
AND txs.TAX_LINE_STATUS_CODE = 'ACTIVE'
AND txs.TAX_CALL_TYPE_CODE = 'UPFRONT_TAX'
AND cle.id = rg1.cle_id
AND cle.sts_code <> 'ABANDONED'; -- Bug 5005269
SELECT 1
FROM okl_tax_sources txs,
okc_rule_groups_b rg1,
okc_rules_b rl1,
okc_k_lines_b cle
WHERE txs.khr_id = p_contract_id
AND txs.trx_id = p_transaction_id
-- R12B ebtax: Active and Inactive lines will be billed during rebook
-- AND txs.tax_line_status_code = 'ACTIVE'
AND txs.tax_call_type_code = 'UPFRONT_TAX'
AND NVL(txs.total_tax,0) <> 0
AND rg1.dnz_chr_id = txs.khr_id
AND rg1.cle_id = txs.kle_id
AND rg1.rgd_code = 'LAASTX'
AND rl1.dnz_chr_id = rg1.dnz_chr_id
AND rl1.rgp_id = rg1.id
AND rl1.rule_information_category = 'LAASTX'
AND rl1.rule_information11 = 'BILLED'
AND cle.id = rg1.cle_id
AND cle.sts_code <> 'ABANDONED';
SELECT 1
FROM okl_tax_sources txs,
okc_rule_groups_b rg1,
okc_rules_b rl1,
okc_k_lines_b cle
WHERE txs.khr_id = p_contract_id
AND txs.trx_id = p_transaction_id
-- R12B ebtax: Active and Inactive lines will be billed during rebook
-- AND txs.tax_line_status_code = 'ACTIVE'
AND txs.tax_call_type_code = 'UPFRONT_TAX'
AND NVL(txs.total_tax,0) <> 0
AND rg1.dnz_chr_id = txs.khr_id
AND rg1.cle_id = txs.kle_id
AND rg1.rgd_code = 'LAASTX'
AND rl1.dnz_chr_id = rg1.dnz_chr_id
AND rl1.rgp_id = rg1.id
AND rl1.rule_information_category = 'LAASTX'
AND NVL(rl1.rule_information11,'BILLED') = 'BILLED'
AND cle.id = rg1.cle_id
AND cle.sts_code <> 'ABANDONED';
SELECT 1
FROM okl_tax_sources txs
WHERE txs.khr_id = p_contract_id
AND txs.trx_id = p_transaction_id
AND txs.kle_id IS NULL
AND txs.tax_line_status_code = 'ACTIVE'
AND txs.tax_call_type_code = 'UPFRONT_TAX'
AND NVL(txs.total_tax,0) <> 0;
SELECT '!'
FROM okc_k_headers_b CHR
WHERE CHR.ID = chrid
AND CHR.orig_system_source_code = 'OKL_REBOOK';
SELECT '!'
FROM okc_k_headers_b chr
WHERE chr.ID = p_chr_id
AND EXISTS (SELECT '1'
FROM okl_trx_contracts ktrx
WHERE 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 Project
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 'x'
FROM okl_tax_sources
WHERE khr_id = cp_chr_id
AND tax_call_type_code = 'UPFRONT_TAX' ;
SELECT 'Y'
FROM okc_k_headers_b CHR
WHERE nvl(chr.orig_system_source_code,'XXXX') <> 'OKL_RELEASE'
and chr.ID = cp_chr_id
AND exists ( SELECT '1'
FROM okl_k_lines kle
,okc_k_lines_b cleb
WHERE cleb.dnz_chr_id = chr.id
AND cleb.chr_id = chr.id
AND cleb.id = kle.id
AND cleb.lse_id = 33
AND cleb.sts_code <> 'ABANDONED'
AND nvl(kle.re_lease_yn,'N') = 'Y')
AND exists ( SELECT '1'
FROM okl_k_lines kle
,okc_k_lines_b cleb
WHERE cleb.dnz_chr_id = chr.id
AND cleb.chr_id = chr.id
AND cleb.id = kle.id
AND cleb.lse_id = 33
AND cleb.sts_code <> 'ABANDONED'
AND nvl(kle.re_lease_yn,'N') = 'N');
SELECT 'Y'
FROM okc_k_headers_b CHR
WHERE nvl(chr.orig_system_source_code,'XXXX') <> 'OKL_RELEASE'
and chr.ID = ( SELECT orig_system_id1
FROM okc_k_headers_b
WHERE id = cp_chr_id )
AND exists ( SELECT '1'
FROM okl_k_lines kle
,okc_k_lines_b cleb
WHERE cleb.dnz_chr_id = chr.id
AND cleb.chr_id = chr.id
AND cleb.id = kle.id
AND cleb.lse_id = 33
AND cleb.sts_code <> 'ABANDONED'
AND nvl(kle.re_lease_yn,'N') = 'Y')
AND not exists ( SELECT '1'
FROM okl_k_lines kle
,okc_k_lines_b cleb
WHERE cleb.dnz_chr_id = chr.id
AND cleb.chr_id = chr.id
AND cleb.id = kle.id
AND cleb.lse_id = 33
AND cleb.sts_code <> 'ABANDONED'
AND nvl(kle.re_lease_yn,'N') = 'N');
select deal_type
from okl_k_headers
where deal_type = 'LOAN'
and id = p_chr_id;
SELECT rul.rule_information1, rul.rule_information3, rul.rule_information4,
rul.rule_information5, rul.rule_information6, rul.rule_information7
FROM OKC_RULES_V rul,
OKC_RULE_GROUPS_V rgp
WHERE rul.rgp_id = rgp.id
AND rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id is NULL
AND rgp.rgd_code = 'LAHDTX'
AND rul.rule_information_category = 'LASTCL';
SELECT rul.rule_information1
FROM OKC_RULES_V rul,
OKC_RULE_GROUPS_V rgp
WHERE rul.rgp_id = rgp.id
AND rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id is NULL
AND rgp.rgd_code = 'LAHDTX'
AND rul.rule_information_category = 'LASTPR';
SELECT id
FROM OKC_RULE_GROUPS_B rgp
WHERE rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id is NOT NULL
AND rgp.rgd_code = 'LAASTX'
AND rgp.cle_id = p_cle_id; -- 5179119
SELECT rul.rule_information1, rul.rule_information3, rul.rule_information4,
rul.rule_information5, rul.rule_information6, rul.rule_information7
FROM OKC_RULES_V rul,
OKC_RULE_GROUPS_V rgp
WHERE rul.rgp_id = rgp.id
AND rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id is NULL
AND rgp.rgd_code = 'LAHDTX'
AND rul.rule_information_category = 'LASTCL';
SELECT cle.id
FROM OKC_K_LINES_V cle,
OKC_LINE_STYLES_V lse
WHERE cle.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM1'
AND lse.lse_type = 'TLS'
AND cle.dnz_chr_id = p_chr_id;
select 'Y'
from okc_k_headers_b
where id = p_chr_id
and nvl(orig_system_source_code,'XXX')='OKL_REBOOK';
SELECT rul.id, rul.rule_information5, cle.ORIG_SYSTEM_ID1
FROM OKC_RULES_V rul,
OKC_RULE_GROUPS_V rgp,
okc_k_lines_b cle
WHERE rul.rgp_id = rgp.id
AND rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id = p_cle_id
AND rgp.rgd_code = 'LAASTX'
AND rul.rule_information_category = 'LAASTX'
AND cle.id = rgp.cle_id
AND cle.id = p_cle_id;
SELECT rul.rule_information1, rul.rule_information2,
rul.rule_information3, rul.rule_information4, rul.rule_information4
FROM OKC_RULES_V rul,
OKC_RULE_GROUPS_V rgp
WHERE rul.rgp_id = rgp.id
AND rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id is NULL
AND rgp.rgd_code = 'LAHDTX'
AND rul.rule_information_category = 'LASTPR';
select col.form_left_prompt
from fnd_descr_flex_col_usage_vl col,
okc_rule_defs_v rdef
where col.application_id = 540
and col.application_id = rdef.application_id
and col.descriptive_flexfield_name=rdef.descriptive_flexfield_name
and col.descriptive_flex_context_code=rdef.rule_code
and rdef.rule_code = 'LASTPR'
and col.application_column_name = p_ri;
--- get all the contract lines and create/update
-- the lines with contract sales tax info
FOR r_get_contract_lines IN get_contract_lines(p_chr_id => p_chr_id) LOOP
lv_asset_k_rule_information5 := null; -- Reset the value
OKL_RULE_PUB.update_rule(
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_rulv_rec => l_rulv_rec,
x_rulv_rec => lx_rulv_rec);
SELECT id
FROM okl_trx_types_tl
WHERE name = p_transaction_type
AND language = 'US';
Select txh.ID HeaderTransID,
txh.date_transaction_occurred date_transaction_occurred
From okl_trx_contracts txh
Where txh.khr_id = khrId
--rkuttiya added for 12.1.1 Multi GAAP
AND txh.representation_type = 'PRIMARY'
--
AND txh.tcn_type = tcntype;
SELECT contract_number
FROM OKC_K_HEADERS_B
WHERE id = p_ctr_id1;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Populate Trx Procedure : before Okl_Trx_Contracts_Pub.update_trx_contracts ');
Okl_Trx_Contracts_Pub.update_trx_contracts(
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_tcnv_rec => l_upd_trxH_rec
,x_tcnv_rec => lx_upd_trxH_rec);
SELECT NVL(SUM(NVL(total_tax,0)),0)
FROM okl_tax_sources txs
WHERE txs.khr_id = p_contract_id
AND txs.kle_id IS NULL
AND txs.tax_line_status_code = 'ACTIVE'
AND txs.tax_call_type_code = 'UPFRONT_TAX';
PROCEDURE update_fee(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
p_contract_id IN NUMBER,
p_transaction_id IN NUMBER,
p_fee_line_id IN NUMBER,
p_required_feetype IN VARCHAR2,
p_default_feetype IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
)
IS
l_fee_types_rec OKL_MAINTAIN_FEE_PVT.fee_types_rec_type;
SELECT cle.id, cle.name, cle.item_description
FROM okc_k_lines_v cle,
okc_rule_groups_b rg1,
okc_rules_b rl1,
okc_line_styles_b lse,
okc_rule_groups_b rg2,
okc_rules_b rl2
WHERE cle.chr_ID = p_contract_id
AND cle.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM1'
AND cle.dnz_chr_id = rg1.dnz_chr_id
AND cle.id = rg1.cle_id
AND cle.dnz_chr_id = rl1.dnz_chr_id
AND rg1.id = rl1.rgp_id
AND rg1.rgd_code = 'LAASTX'
AND rl1.rule_information_category = 'LAASTX'
AND ( rl1.rule_information11 IS NULL
OR
rl1.rule_information11 = p_fee_type)
AND cle.dnz_chr_id = rg2.dnz_chr_id
AND cle.dnz_chr_id = rl2.dnz_chr_id
AND rg2.id = rl2.rgp_id
AND rg2.rgd_code = 'LAHDTX'
AND rl2.rule_information_category = 'LASTPR'
AND rl2.rule_information1 = p_fee_type
AND cle.sts_code <> 'ABANDONED';
SELECT cle.id, cle.name, cle.item_description
FROM okc_k_lines_v cle,
okc_rule_groups_b rg1,
okc_rules_b rl1,
okc_line_styles_b lse
WHERE cle.dnz_chr_id = p_contract_id
AND cle.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM1'
AND cle.dnz_chr_id = rg1.dnz_chr_id
AND cle.id = rg1.cle_id
AND cle.dnz_chr_id = rl1.dnz_chr_id
AND rg1.id = rl1.rgp_id
AND rg1.rgd_code = 'LAASTX'
AND rl1.rule_information_category = 'LAASTX'
AND rl1.rule_information11 = p_fee_type
AND cle.sts_code <> 'ABANDONED';
SELECT NVL(SUM(NVL(total_tax,0)),0)
FROM okl_tax_sources txs
WHERE txs.khr_ID = p_contract_id
AND txs.trx_id = p_transaction_id
AND txs.kle_id = asset_line_id
AND txs.tax_line_status_code = 'ACTIVE'
AND txs.tax_call_type_code = 'UPFRONT_TAX';
SELECT NVL(kle_fee.capital_amount,kle_fee.amount) amount,
kle_fee.fee_type,
cleb_fee.start_date,
cleb_fee.end_date,
cim_fee.id cim_fee_id,
cim_fee.object1_id1
FROM okl_k_lines kle_fee,
okc_k_lines_b cleb_fee,
okc_k_items cim_fee
WHERE cleb_fee.id = p_fee_cle_id
AND kle_fee.id = cleb_fee.id
AND cim_fee.cle_id = cleb_fee.id
AND cim_fee.dnz_chr_id = cleb_fee.dnz_chr_id
AND cim_fee.jtot_object1_code = 'OKL_STRMTYP';
select name
from OKL_STRM_TYPE_TL
where id = stream_type_id
AND language = USERENV('LANG');
SELECT cleb_cov_asset.id cleb_cov_asset_id,
NVL(kle_cov_asset.capital_amount,kle_cov_asset.amount) amount,
cim_cov_asset.id cim_cov_asset_id
FROM okc_k_lines_b cleb_cov_asset,
okl_k_lines kle_cov_asset,
okc_k_items cim_cov_asset
WHERE cleb_cov_asset.cle_id = p_fee_cle_id
AND cleb_cov_asset.dnz_chr_id = p_chr_id
AND kle_cov_asset.id = cleb_cov_asset.id
AND cim_cov_asset.cle_id = cleb_cov_asset.id
AND cim_cov_asset.dnz_chr_id = cleb_cov_asset.dnz_chr_id
AND cim_cov_asset.object1_id1 = p_fin_asset_cle_id
AND cim_cov_asset.jtot_object1_code = 'OKX_COVASST'
AND cleb_cov_asset.sts_code <> 'ABANDONED';
SELECT rul_lafexp.id,
rul_lafexp.rgp_id,
rul_lafexp.dnz_chr_id,
rul_lafexp.rule_information_category,
TO_NUMBER(rul_lafexp.rule_information2) amount
FROM okc_rule_groups_b rgp_lafexp,
okc_rules_b rul_lafexp
WHERE rgp_lafexp.dnz_chr_id = p_chr_id
AND rgp_lafexp.cle_id = p_fee_cle_id
AND rgp_lafexp.rgd_code = 'LAFEXP'
AND rul_lafexp.rgp_id = rgp_lafexp.id
AND rul_lafexp.dnz_chr_id = rgp_lafexp.dnz_chr_id
AND rul_lafexp.rule_information_category = 'LAFEXP';
SELECT cle.id
FROM okc_k_lines_b cle,
okl_k_lines kle
WHERE cle.id = kle.id
AND cle.dnz_chr_id = p_chr_id
AND kle.fee_purpose_code = 'SALESTAX'
AND kle.fee_type = p_fee_type
AND cle.sts_code <> 'ABANDONED';
SELECT cleb_cov_asset.id cleb_cov_asset_id,
cim_cov_asset.id cim_cov_asset_id
FROM okc_k_lines_b cleb_cov_asset,
okc_k_items cim_cov_asset,
okc_k_lines_b cleb_fin,
okc_rule_groups_b rgp,
okc_rules_b rul
WHERE cleb_cov_asset.cle_id = p_fee_cle_id
AND cleb_cov_asset.dnz_chr_id = p_chr_id
AND cim_cov_asset.cle_id = cleb_cov_asset.id
AND cim_cov_asset.dnz_chr_id = cleb_cov_asset.dnz_chr_id
AND cim_cov_asset.jtot_object1_code = 'OKX_COVASST'
AND cleb_fin.id = cim_cov_asset.object1_id1
AND cleb_fin.chr_id = cim_cov_asset.dnz_chr_id
AND cleb_fin.dnz_chr_id = cim_cov_asset.dnz_chr_id
AND rgp.dnz_chr_id = cleb_fin.dnz_chr_id
AND rgp.cle_id = cleb_fin.id
AND rgp.rgd_code = 'LAASTX'
AND rul.dnz_chr_id = rgp.dnz_chr_id
AND rul.rgp_id = rgp.id
AND rul.rule_information_category = 'LAASTX'
AND rul.rule_information11 = 'BILLED'
AND cleb_fin.sts_code <> 'ABANDONED'
AND cleb_cov_asset.sts_code <> 'ABANDONED';
SELECT cleb_cov_asset.id cleb_cov_asset_id,
cim_cov_asset.id cim_cov_asset_id
FROM okc_k_lines_b cleb_cov_asset,
okc_k_items cim_cov_asset,
okc_k_lines_b cleb_fin,
okc_rule_groups_b rgp,
okc_rules_b rul
WHERE cleb_cov_asset.cle_id = p_fee_cle_id
AND cleb_cov_asset.dnz_chr_id = p_chr_id
AND cim_cov_asset.cle_id = cleb_cov_asset.id
AND cim_cov_asset.dnz_chr_id = cleb_cov_asset.dnz_chr_id
AND cim_cov_asset.jtot_object1_code = 'OKX_COVASST'
AND cleb_fin.id = cim_cov_asset.object1_id1
AND cleb_fin.chr_id = cim_cov_asset.dnz_chr_id
AND cleb_fin.dnz_chr_id = cim_cov_asset.dnz_chr_id
AND rgp.dnz_chr_id = cleb_fin.dnz_chr_id
AND rgp.cle_id = cleb_fin.id
AND rgp.rgd_code = 'LAASTX'
AND rul.dnz_chr_id = rgp.dnz_chr_id
AND rul.rgp_id = rgp.id
AND rul.rule_information_category = 'LAASTX'
AND NVL(rul.rule_information11,'BILLED') = 'BILLED'
AND cleb_fin.sts_code <> 'ABANDONED'
AND cleb_cov_asset.sts_code <> 'ABANDONED';
update_fee_exception exception;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: l_upfront_tax_fee_amount: '||l_upfront_tax_fee_amount);
OKL_MAINTAIN_FEE_PVT.delete_fee_type(
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_fee_types_rec => l_del_fee_types_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: after OKL_MAINTAIN_FEE_PVT.delete_fee_type: x_return_status '||x_return_status);
RAISE update_fee_exception;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: l_fee_rec.amount: '||l_fee_rec.amount);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: before OKL_MAINTAIN_FEE_PVT.update_fee_type ');
OKL_MAINTAIN_FEE_PVT.update_fee_type(
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_fee_types_rec => l_fee_types_rec,
x_fee_types_rec => lx_fee_types_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: after OKL_MAINTAIN_FEE_PVT.update_fee_type: x_return_status '||x_return_status);
RAISE update_fee_exception;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: before get_asset_tax_amt_csr ');
RAISE update_fee_exception;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: l_asset_tax_amt '||l_asset_tax_amt);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: l_cov_asset_line_rec.cleb_cov_asset_id '||l_cov_asset_line_rec.cleb_cov_asset_id);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: l_cov_asset_line_rec.amount '||l_cov_asset_line_rec.amount);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: before get_asset_tax_amt_csr ');
RAISE update_fee_exception;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: before okl_contract_line_item_pvt.create_contract_line_item ');
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: x_return_status '||x_return_status);
RAISE update_fee_exception;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: before okl_contract_line_item_pvt.update_contract_line_item ');
okl_contract_line_item_pvt.update_contract_line_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_line_item_tbl => l_u_line_item_tbl,
x_line_item_tbl => lx_u_line_item_tbl);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: x_return_status '||x_return_status);
RAISE update_fee_exception;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: before okl_contract_line_item_pvt.delete_contract_line_item ');
okl_contract_line_item_pvt.delete_contract_line_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_line_item_tbl => l_d_line_item_tbl);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: x_return_status '||x_return_status);
RAISE update_fee_exception;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: before OKL_RULE_PUB.update_rule ');
OKL_RULE_PUB.update_rule(
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_rulv_tbl => l_rulv_tbl,
x_rulv_tbl => lx_rulv_tbl);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: x_return_status '||x_return_status);
RAISE update_fee_exception;
when update_fee_exception then
x_return_status := OKL_API.G_RET_STS_ERROR;
END update_fee;
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_FEE';
SELECT cle.id,
kle.fee_type
FROM okc_k_lines_b cle,
okl_k_lines kle
WHERE cle.id = kle.id
AND cle.dnz_chr_id = p_chr_id
AND cle.chr_id = p_chr_id
AND kle.fee_purpose_code = 'SALESTAX'
AND cle.sts_code <> 'ABANDONED';
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: Default Fee Type '||l_rule_info_rec.rule_information1);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: Sales Tax Fee Line Id '||l_fee_rec.id);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: Sales Tax Fee Type '||l_fee_rec.fee_type);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: Before OKL_LA_SALES_TAX_PVT.update_fee ');
OKL_LA_SALES_TAX_PVT.update_fee
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_contract_id => p_chr_id,
p_transaction_id => p_transaction_id,
p_fee_line_id => l_fee_rec.id,
p_required_feetype => l_fee_rec.fee_type,
p_default_feetype => l_rule_info_rec.rule_information1,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: After OKL_LA_SALES_TAX_PVT.update_fee: x_return_status '||x_return_status);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: Update Fee Procedure: After OKL_LA_SALES_TAX_PVT.validate_upfront_tax_fee: x_return_status '||x_return_status);
OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx(
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_khr_id => p_chr_id ,
p_prog_short_name => OKL_BOOK_CONTROLLER_PVT.G_CALC_UPFRONT_TAX ,
p_progress_status => l_upfront_tax_prog_sts);
OKL_CONTRACT_STATUS_PUB.update_contract_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_khr_status => 'PASSED',
p_chr_id => p_chr_id);
OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx(
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_khr_id => p_chr_id ,
p_prog_short_name => OKL_BOOK_CONTROLLER_PVT.G_PRICE_CONTRACT,
p_progress_status => OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_PENDING);
OKL_BOOK_CONTROLLER_PVT.update_book_controller_trx(
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_khr_id => p_chr_id ,
p_prog_short_name => OKL_BOOK_CONTROLLER_PVT.G_SUBMIT_CONTRACT,
p_progress_status => OKL_BOOK_CONTROLLER_PVT.G_PROG_STS_PENDING);
SELECT cle.id
FROM OKC_K_LINES_b cle,
OKL_K_LINES KLE
WHERE cle.id = kle.id
AND cle.dnz_chr_id = p_contract_id
AND kle.fee_purpose_code = 'SALESTAX'
AND kle.fee_type = l_fee_type
AND cle.sts_code <> 'ABANDONED';
select name
from OKL_STRM_TYPE_TL
where id = stream_type_id
AND language = USERENV('LANG');
SELECT start_date, end_date
FROM okc_k_headers_b
WHERE id = p_contract_id;
SELECT cle.id, cle.name, cle.item_description
FROM okc_k_lines_v cle,
okc_rule_groups_b rg1,
okc_rules_b rl1,
okc_line_styles_b lse,
okc_rule_groups_b rg2,
okc_rules_b rl2
WHERE cle.chr_ID = p_contract_id
AND cle.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM1'
AND cle.dnz_chr_id = rg1.dnz_chr_id
AND cle.id = rg1.cle_id
AND cle.dnz_chr_id = rl1.dnz_chr_id
AND rg1.id = rl1.rgp_id
AND rg1.rgd_code = 'LAASTX'
AND rl1.rule_information_category = 'LAASTX'
AND ( rl1.rule_information11 IS NULL
OR
rl1.rule_information11 = p_fee_type)
AND cle.dnz_chr_id = rg2.dnz_chr_id
AND cle.dnz_chr_id = rl2.dnz_chr_id
AND rg2.id = rl2.rgp_id
AND rg2.rgd_code = 'LAHDTX'
AND rl2.rule_information_category = 'LASTPR'
AND rl2.rule_information1 = p_fee_type
AND cle.sts_code <> 'ABANDONED';
SELECT cle.id, cle.name, cle.item_description
FROM okc_k_lines_v cle,
okc_rule_groups_b rg1,
okc_rules_b rl1,
okc_line_styles_b lse
WHERE cle.dnz_chr_ID = p_contract_id
AND cle.lse_id = lse.id
AND lse.lty_code = 'FREE_FORM1'
AND cle.dnz_chr_id = rg1.dnz_chr_id
AND cle.id = rg1.cle_id
AND cle.dnz_chr_id = rl1.dnz_chr_id
AND rg1.id = rl1.rgp_id
AND rg1.rgd_code = 'LAASTX'
AND rl1.rule_information_category = 'LAASTX'
AND rl1.rule_information11 = p_fee_type
AND cle.sts_code <> 'ABANDONED';
SELECT NVL(SUM(NVL(TOTAL_TAX,0)),0)
FROM okl_tax_sources txs
WHERE txs.KHR_ID = p_contract_id
AND txs.trx_id = p_transaction_id
AND txs.kle_id = asset_line_id
AND txs.TAX_LINE_STATUS_CODE = 'ACTIVE'
AND txs.TAX_CALL_TYPE_CODE = 'UPFRONT_TAX';
OKL_LA_SALES_TAX_PVT.update_fee
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_contract_id => p_contract_id,
p_transaction_id => p_transaction_id,
p_fee_line_id => l_fee_line_id,
p_required_feetype => p_required_feetype,
p_default_feetype => p_default_feetype,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
OKL_LA_SALES_TAX_PVT.update_fee
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
p_contract_id => p_contract_id,
p_transaction_id => p_transaction_id,
p_fee_line_id => l_fee_line_id,
p_required_feetype => p_required_feetype,
p_default_feetype => p_default_feetype,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT mo_global.get_current_org_id() l_fnd_profile
FROM dual;
SELECT cust_trx_type_id l_cust_trx_type_id
FROM ra_cust_trx_types
WHERE name = 'Invoice-OKL';
SELECT ct.object1_id1 id
,chr.scs_code scs_code
FROM okc_contacts ct,
okc_contact_sources csrc,
okc_k_party_roles_b pty,
okc_k_headers_b chr
WHERE ct.cpl_id = pty.id
AND ct.cro_code = csrc.cro_code
AND ct.jtot_object1_code = csrc.jtot_object_code
AND ct.dnz_chr_id = chr.id
AND pty.rle_code = csrc.rle_code
AND csrc.cro_code = 'SALESPERSON'
AND csrc.rle_code = 'LESSOR'
AND csrc.buy_or_sell = chr.buy_or_sell
AND pty.dnz_chr_id = chr.id
AND pty.chr_id = chr.id
AND chr.id = p_trxh_out_rec.khr_id;
SELECT bill_to_site_use_id cust_acct_site_id
FROM okc_k_headers_b
WHERE id = p_trxh_out_rec.khr_id;
SELECT bill_to_site_use_id cust_acct_site_id
FROM okc_k_lines_b
WHERE id = p_cle_id;
SELECT account_derivation
FROM okl_sys_acct_opts;
SELECT NVL(rul.rule_information11, p_default_tax_treatment)
tax_treatment
, rgp.cle_id asset_id
, txs.id tax_header_id
, txl.tax_line_id tax_line_id
, txl.tax_amt tax_amount
FROM OKC_RULES_V rul
, OKC_RULE_GROUPS_V rgp
, okl_tax_sources txs
, zx_lines txl
WHERE rul.rgp_id = rgp.id
AND rgp.dnz_chr_id = p_contract_id
AND rgp.rgd_code = 'LAASTX'
AND rul.rule_information_category = 'LAASTX'
AND (rul.rule_information11 = p_default_tax_treatment
OR
rul.rule_information11 IS NULL)
AND txs.khr_id = rgp.dnz_chr_id
AND txs.kle_id = rgp.cle_id
AND txs.kle_id IS NOT NULL -- change
AND txs.TAX_LINE_STATUS_CODE = 'ACTIVE'
AND txs.TAX_CALL_TYPE_CODE = 'UPFRONT_TAX'
AND txs.trx_id = txl.trx_id
AND txs.trx_line_id = txl.trx_line_id
AND txl.entity_code = 'CONTRACTS' -- change
AND txl.event_class_code = 'BOOKING' -- change
AND txs.entity_code = txl.entity_code -- change
AND txs.event_class_code = txl.event_class_code -- change
AND txl.application_id = 540
AND txl.trx_level_type = 'LINE'
AND txs.application_id = txl.application_id
AND txs.trx_level_type = txl.trx_level_type;
SELECT rul.rule_information11 tax_treatment
, rgp.cle_id asset_id
, txs.id tax_header_id
, txl.tax_line_id tax_line_id
, txl.tax_amt tax_amount
FROM OKC_RULES_V rul
, OKC_RULE_GROUPS_V rgp
, okl_tax_sources txs
, zx_lines txl
WHERE rul.rgp_id = rgp.id
AND rgp.dnz_chr_id = p_contract_id
AND rgp.rgd_code = 'LAASTX'
AND rul.rule_information_category = 'LAASTX'
AND rul.rule_information11 <> 'BILLED'
AND txs.khr_id = rgp.dnz_chr_id
AND txs.kle_id = rgp.cle_id
AND txs.kle_id IS NOT NULL -- change
AND txs.TAX_LINE_STATUS_CODE = 'ACTIVE'
AND txs.TAX_CALL_TYPE_CODE = 'UPFRONT_TAX'
AND txs.trx_id = txl.trx_id
AND txs.trx_line_id = txl.trx_line_id
AND txl.entity_code = 'CONTRACTS' -- change
AND txl.event_class_code = 'BOOKING' -- change
AND txs.entity_code = txl.entity_code -- change
AND txs.event_class_code = txl.event_class_code -- change
AND txl.application_id = 540
AND txl.trx_level_type = 'LINE'
AND txs.application_id = txl.application_id
AND txs.trx_level_type = txl.trx_level_type;
SELECT rul.rule_information11 tax_treatment
, rgp.cle_id asset_id
, txs.id tax_header_id
, txl.tax_line_id tax_line_id
, txl.tax_amt tax_amount
FROM OKC_RULES_V rul
, OKC_RULE_GROUPS_V rgp
, okl_tax_sources txs
, zx_lines txl
WHERE rul.rgp_id = rgp.id
AND rgp.dnz_chr_id = p_contract_id
AND rgp.rgd_code = 'LAASTX'
AND rul.rule_information_category = 'LAASTX'
AND rul.rule_information11 = 'BILLED'
AND txs.khr_id = rgp.dnz_chr_id
AND txs.kle_id = rgp.cle_id
AND txs.kle_id IS NOT NULL -- change
AND txs.TAX_LINE_STATUS_CODE = 'ACTIVE'
AND txs.TAX_CALL_TYPE_CODE = 'UPFRONT_TAX'
AND txs.trx_id = txl.trx_id
AND txs.trx_line_id = txl.trx_line_id
AND txl.entity_code = 'CONTRACTS' -- change
AND txl.event_class_code = 'BOOKING' -- change
AND txs.entity_code = txl.entity_code -- change
AND txs.event_class_code = txl.event_class_code -- change
AND txl.application_id = 540
AND txl.trx_level_type = 'LINE'
AND txs.application_id = txl.application_id
AND txs.trx_level_type = txl.trx_level_type;
SELECT 'BILLED' tax_treatment
, txs.kle_id asset_id
, txs.id tax_header_id
, txl.tax_line_id tax_line_id
, txl.tax_amt tax_amount
FROM okl_tax_sources txs
, zx_lines txl
WHERE txs.khr_id = p_contract_id
AND txs.kle_id IS NULL
AND txs.TAX_LINE_STATUS_CODE = 'ACTIVE'
AND txs.TAX_CALL_TYPE_CODE = 'UPFRONT_TAX'
AND txs.trx_id = txl.trx_id
AND txs.trx_line_id = txl.trx_line_id
AND txl.entity_code = 'CONTRACTS'
AND txl.event_class_code = 'BOOKING'
AND txs.entity_code = txl.entity_code
AND txs.event_class_code = txl.event_class_code
AND txl.application_id = 540
AND txl.trx_level_type = 'LINE'
AND txs.application_id = txl.application_id
AND txs.trx_level_type = txl.trx_level_type;
SELECT 1
FROM dual
WHERE EXISTS (SELECT a.id
FROM okl_trx_contracts_all a
,okl_trx_types_v b
WHERE a.khr_id = p_contract_id
AND a.try_id = b.id
--rkuttiya added for 12.1.1 Multi GAAP
AND a.representation_type = 'PRIMARY'
--
AND b.name = 'Upfront Tax');
SELECT NVL(rul.rule_information11,p_default_tax_treatment)
tax_treatment
, rgp.cle_id asset_id
, txs.id tax_header_id
, txl.tax_line_id tax_line_id
, txl.tax_amt tax_amount
FROM OKC_RULES_V rul
, OKC_RULE_GROUPS_V rgp
, okl_tax_sources txs
, zx_lines txl
WHERE rul.rgp_id = rgp.id
AND rgp.dnz_chr_id = p_contract_id
AND rgp.rgd_code = 'LAASTX'
AND rul.rule_information_category = 'LAASTX'
AND (rul.rule_information11 = p_default_tax_treatment
OR
rul.rule_information11 IS NULL)
AND txs.khr_id = rgp.dnz_chr_id
AND txs.kle_id = rgp.cle_id
AND txs.kle_id IS NOT NULL
AND txs.TAX_LINE_STATUS_CODE = 'INACTIVE'
AND txs.TAX_CALL_TYPE_CODE = 'UPFRONT_TAX'
AND txs.trx_id = txl.trx_id
AND txs.trx_line_id = txl.trx_line_id
AND txl.entity_code IN ('CONTRACTS','ASSETS')
AND txl.event_class_code IN ('BOOKING','ASSET_RELOCATION')
AND txs.entity_code = txl.entity_code
AND txs.event_class_code = txl.event_class_code
AND txl.application_id = 540
AND txl.trx_level_type = 'LINE'
AND txs.application_id = txl.application_id
AND txs.trx_level_type = txl.trx_level_type
AND (txs.trx_id,txs.trx_line_id)
IN
(SELECT adjusted_doc_trx_id
,adjusted_doc_trx_line_id
FROM okl_tax_sources
WHERE trx_id = p_rbk_trx_id
AND tax_line_status_code = 'INACTIVE'
AND adjusted_doc_trx_id IS NOT NULL
AND adjusted_doc_trx_line_id IS NOT NULL);
SELECT rul.rule_information11 tax_treatment
, rgp.cle_id asset_id
, txs.id tax_header_id
, txl.tax_line_id tax_line_id
, txl.tax_amt tax_amount
FROM OKC_RULES_V rul
, OKC_RULE_GROUPS_V rgp
, okl_tax_sources txs
, zx_lines txl
WHERE rul.rgp_id = rgp.id
AND rgp.dnz_chr_id = p_contract_id
AND rgp.rgd_code = 'LAASTX'
AND rul.rule_information_category = 'LAASTX'
AND rul.rule_information11 <> 'BILLED'
AND txs.khr_id = rgp.dnz_chr_id
AND txs.kle_id = rgp.cle_id
AND txs.kle_id IS NOT NULL
AND txs.TAX_LINE_STATUS_CODE = 'INACTIVE'
AND txs.TAX_CALL_TYPE_CODE = 'UPFRONT_TAX'
AND txs.trx_id = txl.trx_id
AND txs.trx_line_id = txl.trx_line_id
AND txl.entity_code IN ('CONTRACTS','ASSETS')
AND txl.event_class_code IN ('BOOKING','ASSET_RELOCATION')
AND txs.entity_code = txl.entity_code
AND txs.event_class_code = txl.event_class_code
AND txl.application_id = 540
AND txl.trx_level_type = 'LINE'
AND txs.application_id = txl.application_id
AND txs.trx_level_type = txl.trx_level_type
AND (txs.trx_id,txs.trx_line_id)
IN
(SELECT adjusted_doc_trx_id
,adjusted_doc_trx_line_id
FROM okl_tax_sources
WHERE trx_id = p_rbk_trx_id
AND tax_line_status_code = 'INACTIVE'
AND adjusted_doc_trx_id IS NOT NULL
AND adjusted_doc_trx_line_id IS NOT NULL);
SELECT rul.rule_information11 tax_treatment
, rgp.cle_id asset_id
, txs.id tax_header_id
, txl.tax_line_id tax_line_id
, txl.tax_amt tax_amount
FROM OKC_RULES_V rul
, OKC_RULE_GROUPS_V rgp
, okl_tax_sources txs
, zx_lines txl
WHERE rul.rgp_id = rgp.id
AND rgp.dnz_chr_id = p_contract_id
AND rgp.rgd_code = 'LAASTX'
AND rul.rule_information_category = 'LAASTX'
AND rul.rule_information11 = 'BILLED'
AND txs.khr_id = rgp.dnz_chr_id
AND txs.kle_id = rgp.cle_id
AND txs.kle_id IS NOT NULL
AND txs.TAX_LINE_STATUS_CODE = 'INACTIVE'
AND txs.TAX_CALL_TYPE_CODE = 'UPFRONT_TAX'
AND txs.trx_id = txl.trx_id
AND txs.trx_line_id = txl.trx_line_id
AND txl.entity_code IN ('CONTRACTS','ASSETS')
AND txl.event_class_code IN ('BOOKING','ASSET_RELOCATION')
AND txs.entity_code = txl.entity_code
AND txs.event_class_code = txl.event_class_code
AND txl.application_id = 540
AND txl.trx_level_type = 'LINE'
AND txs.application_id = txl.application_id
AND txs.trx_level_type = txl.trx_level_type
AND (txs.trx_id,txs.trx_line_id)
IN
(SELECT adjusted_doc_trx_id
,adjusted_doc_trx_line_id
FROM okl_tax_sources
WHERE trx_id = p_rbk_trx_id
AND tax_line_status_code = 'INACTIVE'
AND adjusted_doc_trx_id IS NOT NULL
AND adjusted_doc_trx_line_id IS NOT NULL);
SELECT 'BILLED' tax_treatment
, txs.kle_id asset_id
, txs.id tax_header_id
, txl.tax_line_id tax_line_id
, txl.tax_amt tax_amount
FROM okl_tax_sources txs
, zx_lines txl
WHERE txs.khr_id = p_contract_id
AND txs.kle_id IS NULL
AND txs.TAX_LINE_STATUS_CODE = 'INACTIVE'
AND txs.TAX_CALL_TYPE_CODE = 'UPFRONT_TAX'
AND txs.trx_id = txl.trx_id
AND txs.trx_line_id = txl.trx_line_id
AND txl.entity_code = 'CONTRACTS'
AND txl.event_class_code = 'BOOKING'
AND txs.entity_code = txl.entity_code
AND txs.event_class_code = txl.event_class_code
AND txl.application_id = 540
AND txl.trx_level_type = 'LINE'
AND txs.application_id = txl.application_id
AND txs.trx_level_type = txl.trx_level_type
AND (txs.trx_id,txs.trx_line_id)
IN
(SELECT adjusted_doc_trx_id
,adjusted_doc_trx_line_id
FROM okl_tax_sources
WHERE trx_id = p_rbk_trx_id
AND tax_line_status_code = 'INACTIVE'
AND adjusted_doc_trx_id IS NOT NULL
AND adjusted_doc_trx_line_id IS NOT NULL);
l_accoutable_tax_lines.DELETE;
l_tclv_tbl.DELETE;
l_acc_gen_tbl.DELETE;
l_tmpl_identify_tbl.DELETE;
l_dist_info_tbl.DELETE;
l_ctxt_tbl.DELETE;
l_accoutable_tax_lines.DELETE;
l_tclv_tbl.DELETE;
l_acc_gen_tbl.DELETE;
l_tmpl_identify_tbl.DELETE;
l_dist_info_tbl.DELETE;
l_ctxt_tbl.DELETE;
SELECT cle.id
FROM okc_k_lines_b cle,
okl_k_lines kle
WHERE cle.id = kle.id
AND cle.dnz_chr_id = p_chr_id
AND kle.fee_purpose_code = 'SALESTAX'
AND kle.fee_type = NVL(p_fee_type,kle.fee_type)
AND cle.sts_code <> 'ABANDONED';
l_fee_type_to_delete OKL_K_LINES.fee_type%TYPE;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: '||p_transaction_type||': CAPITALIZED Sales Tax Fee to Delete: '|| l_del_fee_line_id);
OKL_MAINTAIN_FEE_PVT.delete_fee_type(
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_fee_types_rec => l_del_fee_types_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: '||p_transaction_type||' Procedure: after calling OKL_MAINTAIN_FEE_PVT.delete_fee_type: x_return_status '|| x_return_status );
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: '||p_transaction_type||': FINANCED Sales Tax Fee to Delete: '|| l_del_fee_line_id);
OKL_MAINTAIN_FEE_PVT.delete_fee_type(
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_fee_types_rec => l_del_fee_types_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: '||p_transaction_type||' Procedure: after calling OKL_MAINTAIN_FEE_PVT.delete_fee_type: x_return_status '|| x_return_status );
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: '||p_transaction_type||' Procedure: verifying if multiple fee treatments are selected ');
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: '||p_transaction_type||' Procedure: if multiple fee treatments are NOT selected');
l_fee_type_to_delete := 'CAPITALIZED';
l_fee_type_to_delete := 'FINANCED';
p_fee_type => l_fee_type_to_delete);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: '||p_transaction_type||': '||l_fee_type_to_delete||' Sales Tax Fee to Delete: '|| l_del_fee_line_id);
OKL_MAINTAIN_FEE_PVT.delete_fee_type(
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_fee_types_rec => l_del_fee_types_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: '||p_transaction_type||' Procedure: after calling OKL_MAINTAIN_FEE_PVT.delete_fee_type: x_return_status '|| x_return_status );
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: '||p_transaction_type||': Sales Tax Fee to Delete: '|| l_del_fee_line_id);
OKL_MAINTAIN_FEE_PVT.delete_fee_type(
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_fee_types_rec => l_del_fee_types_rec
);
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'OKL: '||p_transaction_type||' Procedure: after calling OKL_MAINTAIN_FEE_PVT.delete_fee_type: x_return_status '|| x_return_status );
SELECT start_date
FROM okc_k_headers_b
WHERE id = p_contract_id;
,'OKL: Booking Procedure: verifying if multiple fee treatments are selected ');
SELECT account_derivation
FROM okl_sys_acct_opts;
,'OKL: Rebook Procedure: verifying if multiple fee treatments are selected ');
SELECT nvl(tax_upfront_yn,'N')
--, nvl(tax_schedule_yn,'N') -- added for Bug 4748910 -- Not required in R12
FROM okl_system_params;
SELECT start_date, end_date, currency_code
FROM OKC_K_HEADERS_B
WHERE ID = p_contract_id;
SELECT ID
FROM OKL_TRX_TYPES_TL
WHERE name = p_name
AND LANGUAGE = 'US';
SELECT NVL(rule_information1,'N')
FROM okc_rules_b rul
WHERE rul.dnz_chr_id = p_chr_id
AND rul.rule_information_category = 'LARLES'; */
SELECT COUNT(KLE.id) new_asset_cnt
FROM okc_k_lines_b CLEB
,okl_k_lines KLE
WHERE CLEB.id = KLE.id
AND CLEB.dnz_chr_id = p_chr_id
AND CLEB.chr_id = p_chr_id
AND CLEB.LSE_ID = 33 --for asset line
AND NVL(KLE.re_lease_yn,'N')= 'N';
okl_trx_requests_pub.insert_trx_requests(
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_trqv_tbl => l_trqv_tbl,
x_trqv_tbl => x_trqv_tbl);
Select meaning,
description
From fnd_lookups
Where lookup_type = fndType
and lookup_code = fndCode;
SELECT authoring_org_id
FROM okc_k_headers_b
WHERE id = p_chr_id;
select crl.object1_id1,
crl.RULE_INFORMATION1,
crl.RULE_INFORMATION2,
crl.RULE_INFORMATION3,
crl.RULE_INFORMATION4,
crl.RULE_INFORMATION5,
crl.RULE_INFORMATION6,
crl.RULE_INFORMATION7,
crl.RULE_INFORMATION10,
crl.RULE_INFORMATION11
from OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl
where crl.rgp_id = crg.id
and crg.RGD_CODE = rgcode
and crl.RULE_INFORMATION_CATEGORY = rlcat
and crg.dnz_chr_id = chrId;
select crl.id slh_id,
crl.object1_id1,
crl.RULE_INFORMATION1,
crl.RULE_INFORMATION2,
crl.RULE_INFORMATION3,
crl.RULE_INFORMATION5,
crl.RULE_INFORMATION6,
crl.RULE_INFORMATION7,
crl.RULE_INFORMATION8,
crl.RULE_INFORMATION10
from OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl
where crl.rgp_id = crg.id
and crg.RGD_CODE = rgcode
and crl.RULE_INFORMATION_CATEGORY = rlcat
and crg.dnz_chr_id = chrId
and nvl(crg.cle_id,-1) = cleId
order by crl.RULE_INFORMATION1;
SELECT cleb.id,
kle.fee_type,
kle.amount
--decode(kle.fee_type,'CAPITALIZED',kle.capital_amount,kle.amount) amount
FROM
okc_k_lines_b cleb,
okc_k_lines_tl clet,
okl_k_lines kle,
okc_line_styles_v sty,
okc_statuses_v sts
WHERE cleb.lse_id = sty.id
AND cleb.sts_code = sts.code
AND sts.ste_code not in ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED')
AND sty.lty_code = 'FEE'
AND kle.FEE_PURPOSE_CODE='SALESTAX'
AND cleb.dnz_chr_id = p_chrId
AND cleb.id = clet.id
AND clet.LANGUAGE = USERENV('LANG')
AND kle.id = cleb.id;
SELECT cleb.id,
nvl(kle.amount,0) amount,
nvl(kle.capital_amount,0) capital_amount
FROM okc_k_lines_b cleb,
okl_k_lines kle,
okc_line_styles_b sty
WHERE cle_id = p_cleId
AND cleb.id = kle.id
AND cleb.lse_id = sty.id
AND sty.lty_code = 'LINK_FEE_ASSET';
SELECT crl.id slh_id,
crl.object1_id1,
crl.RULE_INFORMATION11
FROM OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl
WHERE crl.rgp_id = crg.id
AND crg.RGD_CODE = 'LAASTX'
AND crl.RULE_INFORMATION_CATEGORY = 'LAASTX'
AND crg.dnz_chr_id = p_chr_id
AND nvl(crg.cle_id,-1) = p_cleId;
SELECT cle.id,
name
FROM okc_k_lines_v cle,
okc_line_styles_b sty,
okc_statuses_b sts
WHERE cle.lse_id = sty.id
AND cle.dnz_chr_id = p_chr_id
AND lty_code = 'FREE_FORM1'
AND cle.sts_code = sts.code
AND sts.ste_code not in ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
SELECT item.object1_id1,cleb.id,cleb.cle_id,
clet_asset.name,
kle.amount,
kle.fee_type
FROM okc_k_lines_b fee_line,
okc_k_lines_b cleb,
okl_k_lines kle,
okc_k_items item,
okc_k_lines_tl clet_asset,
okc_line_styles_b sty
WHERE fee_line.id = kle.id
AND kle.FEE_PURPOSE_CODE='SALESTAX'
AND cleb.lse_id = sty.id
AND sty.lty_code = 'LINK_FEE_ASSET'
AND item.cle_id = cleb.id
AND item.dnz_chr_id = p_chr_id
AND cleb.dnz_chr_id = p_chr_id
AND cleb.cle_id = fee_line.id
AND clet_asset.id = item.object1_id1
AND clet_asset.id = p_assetId
AND clet_asset.language = USERENV('LANG');
SELECT SUM(NVL(TOTAL_TAX,0))
FROM okl_tax_sources txs
--,okl_tax_trx_details txl
WHERE txs.khr_id = p_chr_id
--AND txs.id = txl.txs_id
AND txs.tax_line_status_code = 'ACTIVE'
AND txs.tax_call_type_code = 'UPFRONT_TAX';
SELECT SUM(NVL(TOTAL_TAX,0)) tax_amount -- SUM added for 4740150 issue
FROM okl_tax_sources txs
--,okl_tax_trx_details txl
WHERE txs.khr_id = p_chr_id
-- AND txs.id = txl.txs_id
AND txs.tax_line_status_code = 'ACTIVE'
AND txs.tax_call_type_code = 'UPFRONT_TAX'
AND txs.kle_id = p_asset_id;
SELECT COUNT(cleb.id)
FROM okc_k_lines_b cleb,
okl_k_lines kle,
okc_line_styles_b sty,
okc_statuses_b sts
WHERE cleb.lse_id = sty.id
AND sty.lty_code = 'FEE'
AND cleb.sts_code = sts.code
AND sts.ste_code not in ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED')
AND kle.fee_purpose_code = 'SALESTAX'
AND cleb.dnz_chr_id = p_chr_id
AND cleb.chr_id = p_chr_id
AND kle.id = cleb.id;
SELECT '!'
FROM okc_rule_groups_b crg,
okc_rules_b crl
WHERE crl.rgp_id = crg.ID
AND crg.rgd_code = 'LAASTX'
AND crl.rule_information_category = 'LAASTX'
AND crg.dnz_chr_id = p_chr_id
AND crg.cle_id IS NOT NULL
AND crl.rule_information11 IN ('BILLED', 'CAPITALIZE', 'FINANCE');
SELECT NVL(rule_information1,'N')
FROM okc_rules_b rul
WHERE rul.dnz_chr_id = p_chr_id
AND rul.rule_information_category = 'LARLES';*/
SELECT COUNT(KLE.id) new_asset_cnt
FROM okc_k_lines_b CLEB
,okl_k_lines KLE
WHERE CLEB.id = KLE.id
AND CLEB.dnz_chr_id = p_chr_id
AND CLEB.chr_id = p_chr_id
AND CLEB.LSE_ID = 33 --for asset line
AND NVL(KLE.re_lease_yn,'N')= 'N';
SELECT NVL(kle_top.re_lease_yn,'N') release_flag
FROM okl_k_lines kle_top
WHERE kle_top.id = p_kle_id;
OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error: '||'OKL_QA_ST_FEE_ASSET_ERROR '||'The "Bill Upfront Tax" is selected for the asset = '
||l_asset_rec.name||'. Please remove this asset association from Sales Tax Fee line ('||l_tax_fee_meaning||').');
SELECT 'Y'
FROM okc_rule_groups_b rgp,
okc_rules_b rule,
okc_k_lines_b cle
WHERE rule.rgp_id = rgp.id
AND rgd_code = 'LAASTX'
AND rule_information_category = 'LAASTX'
AND rgp.dnz_chr_id = p_chr_id
AND nvl(rgp.cle_id,-1) <> p_line_id
AND nvl(RULE_INFORMATION11,'XXX') NOT IN (p_line_upfront_tax,'BILLED','XXX')
AND cle.id = rgp.cle_id
AND cle.sts_code <> 'ABANDONED';
SELECT kle.id,
kle.amount,
clev.name,
lsev.name line_type,
--Bug# 12807203
kle.fee_type
FROM okc_k_lines_v clev,
okl_k_lines kle,
okc_statuses_b okcsts,
okc_line_styles_v lsev
WHERE clev.dnz_chr_id = p_chr_id
AND clev.chr_id = p_chr_id
AND kle.id = clev.id
--Bug# 12807203
AND kle.fee_type IN ('FINANCED','CAPITALIZED')
AND kle.fee_purpose_code = 'SALESTAX'
AND lsev.id = clev.lse_id
AND okcsts.code = clev.sts_code
AND okcsts.ste_code NOT IN ('EXPIRED','HOLD','CANCELLED','TERMINATED', 'ABANDONED');
SELECT kle.id,
kle.amount,
cleb.end_date,
cleb.start_date
FROM okc_k_lines_b cleb,
okl_k_lines kle,
okc_statuses_b okcsts
WHERE cleb.dnz_chr_id = p_chr_id
AND kle.id = cleb.id
AND cleb.cle_id = p_fee_line_id
AND okcsts.code = cleb.sts_code
AND okcsts.ste_code NOT IN ('EXPIRED','HOLD','CANCELLED','ABANDONED');
SELECT Fnd_Date.canonical_to_date(sll.rule_information2) start_date,
SLL.rule_information3 periods,
DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) mpp,
TRUNC(ADD_MONTHS(Fnd_Date.canonical_to_date(sll.rule_information2),
TO_NUMBER(sll.rule_information3)*DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12)) - 1) end_date,
styt.name stream_type
FROM okc_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp,
okl_strm_type_b sty,
okl_strm_type_tl styt
WHERE rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id = p_fee_line_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH'
AND slh.object1_id1 = TO_CHAR(sty.id)
AND styt.language = USERENV('LANG')
AND sty.id = styt.id
AND TO_CHAR(slh.id) = sll.object2_id1
AND sll.rule_information_category = 'LASLL';
SELECT slh.id
FROM okc_rules_b slh,
okc_rule_groups_b rgp,
okl_strm_type_b sty,
okl_strm_type_tl styt
WHERE rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id = p_fee_line_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH'
AND slh.object1_id1 = TO_CHAR(sty.id)
AND styt.language = USERENV('LANG')
AND sty.id = styt.id;
SELECT styt.name stream_type,
rule.id rule_id,
rgp.id rgp_id
FROM okc_rules_b rule,
okc_rule_groups_b rgp,
okl_strm_type_b sty,
okl_strm_type_tl styt
WHERE NVL(rgp.cle_id, -1) = p_kle_id
AND rgp.dnz_chr_id = p_khr_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = rule.rgp_id
AND rule.rule_information_category = 'LASLH'
AND TO_NUMBER(rule.object1_id1) = sty.id
AND styt.LANGUAGE = USERENV('LANG')
AND sty.id = styt.id;
SELECT Fnd_Date.canonical_to_date(sll.rule_information2) start_date,
sll.rule_information1 seq,
sll.rule_information6 amt,
sll.rule_information7 stub_day,
sll.rule_information13 rate
FROM okc_rules_b sll
WHERE sll.rgp_id = p_rgp_id
AND sll.object2_id1 = TO_CHAR(p_rule_id)
AND sll.rule_information_category = 'LASLL'
ORDER BY 1,2;