[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select 'Y'
from okc_k_headers_b chrb,
okc_subclasses_b scsb
where chrb.id = p_chr_id
and scsb.code = chrb.scs_code
and scsb.cls_code = 'OKL';
select 'Y'
from okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_headers_b chrb,
okc_subclasses_b scsb
where chrb.id = cleb.dnz_chr_id
and scsb.code = chrb.scs_code
and scsb.cls_code = 'OKL'
and lseb.lty_code = nvl(p_lty_code,lseb.lty_code)
and lseb.id = cleb.lse_id
and cleb.id = p_cle_id;
select 'Y'
from fnd_lookups
where lookup_code = p_lookup_code
and lookup_type = 'OKL_SUBACCT_METHOD';
select 'Y'
from po_vendors
where vendor_id = p_vendor_id;
select 'Y'
from dual
where exists (select '1'
from okc_k_lines_b cleb,
okc_line_styles_b lseb
where cleb.dnz_chr_id = p_chr_id
and cleb.sts_code <> 'ABANDONED'
and lseb.id = cleb.lse_id
and lseb.lty_code = 'SUBSIDY'
);
select 'Y'
from dual
where exists (select '1'
from okc_k_lines_b cleb,
okc_line_styles_b lseb
where cleb.cle_id = p_asset_cle_id
and cleb.sts_code <> 'ABANDONED'
and lseb.id = cleb.lse_id
and lseb.lty_code = 'SUBSIDY'
);
SELECT okl_sif_seq.nextval INTO l_transaction_number FROM DUAL;
Select id
from okl_strm_type_v
--BUG# 4181025
--where name = 'RENT';
SELECT chr.template_yn,
chr.currency_code,
chr.start_date,
khr.deal_type,
khr.term_duration,
NVL(khr.generate_accrual_yn, 'Y')
FROM okc_k_headers_b chr,
okl_k_headers khr
WHERE khr.id = p_khr_id
AND chr.id = khr.id;
l_selv_tbl.DELETE;
l_selv_tbl.delete;
l_pt_tbl.delete;
lx_selv_tbl.delete;
select cleb.start_date,
cleb.end_date
from okc_k_lines_b cleb
where cleb.id = p_cle_id;
select styb.id
from okl_strm_type_b styb
--Bug# 4181025
--where code = p_strm_type;
okl_streams_pub.delete_streams(
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_stmv_rec => l_stmv_rec_stub);
okl_streams_pub.delete_streams(
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_stmv_rec => l_stmv_rec_rent);
select sll_rulb.rule_information2 start_date,
sll_rulb.rule_information3 periods,
sll_rulb.object1_id1 frequency,
sll_rulb.rule_information5 structure,
nvl( sll_rulb.rule_information10,'N') arrears_yn,
sll_rulb.rule_information6 amount,
sll_rulb.rule_information7 stub_days,
sll_rulb.rule_information8 stub_amount,
sll_rulb.rule_information13 rate
from okc_rules_b sll_rulb,
okc_rules_b slh_rulb,
okl_strm_type_b styb,
okc_rule_groups_b rgpb
where sll_rulb.rgp_id = rgpb.id
and sll_rulb.rule_information_category = 'LASLL'
and sll_rulb.dnz_chr_id = rgpb.dnz_chr_id
and sll_rulb.object2_id1 = to_char(slh_rulb.id)
and slh_rulb.rgp_id = rgpb.id
and slh_rulb.rule_information_category = 'LASLH'
and slh_rulb.dnz_chr_id = rgpb.dnz_chr_id
and styb.id = slh_rulb.object1_id1
--Bug# 4181025 :
--and styb.code = 'RENT'
and styb.stream_type_purpose = 'RENT'
and rgpb.cle_id = p_asset_cle_id
and rgpb.rgd_code = 'LALEVL'
order by FND_DATE.canonical_to_date(sll_rulb.rule_information2);
select clet.name
from okc_k_lines_tl clet
where id = p_cle_id
and language = userenv('LANG');
Select sub_cle.id subsidy_cle_id,
sub_cle.dnz_chr_id dnz_chr_id,
sub_cle.start_date subsidy_start_date,
sub_kle.subsidy_id subsidy_id,
sub_kle.amount subsidy_line_amount,
sub_cle.cle_id asset_cle_id,
sub_kle.subsidy_override_amount subsidy_override_amount,
ast_kle.oec asset_oec,
ast_kle.capital_amount asset_capital_amount,
ast_kle.residual_value residual_value,
subb.subsidy_calc_basis subsidy_calc_basis,
subb.amount subsidy_setup_amount,
subb.name subsidy_name,
subb.percent subsidy_setup_percent,
subb.formula_id formula_id,
subb.rate_points rate_points,
subb.currency_code subsidy_setup_currency,
sub_cle.currency_code contract_currency,
--Bug# 3313802 :
subb.maximum_financed_amount maximum_financed_amount,
subb.maximum_subsidy_amount maximum_subsidy_amount
from okc_k_lines_b sub_cle,
okl_k_lines sub_kle,
okl_k_lines ast_kle,
okl_subsidies_b subb
where subb.id = sub_kle.subsidy_id
and ast_kle.id = sub_cle.cle_id
and sub_kle.id = sub_cle.id
and sub_cle.id = p_subsidy_cle_id
and sub_cle.sts_code <> 'ABANDONED';
Select name
from okl_formulae_b
where id = p_formula_id;
select flkup.meaning
from fnd_lookups flkup
where flkup.lookup_type = p_lookup_type
and flkup.lookup_code = p_lookup_code;
select kle.subsidy_override_amount
from okl_k_lines kle,
okc_k_lines_b cleb
where kle.id = cleb.id
and cleb.sts_code <> 'ABANDONED'
and cleb.id = p_cle_id;
select nvl(kle.subsidy_override_amount,nvl(kle.amount,0))
from okl_k_lines kle,
okc_k_lines_b cleb
where kle.id = cleb.id
and cleb.id = p_subsidy_cle_id
and cleb.sts_code <> 'ABANDONED';
select
sub_kle.subsidy_id subsidy_id
,sub_cleb.id subsidy_cle_id
,sub_clet.name name
,sub_clet.item_description description
,sub_kle.sty_id stream_type_id
,subb.accounting_method_code accounting_method_code
,subb.maximum_term maximum_term
,sub_kle.subsidy_override_amount subsidy_override_amount
,sub_cleb.dnz_chr_id dnz_chr_id
,sub_cleb.cle_id asset_cle_id
,cplb.id cpl_id
,pov.vendor_id vendor_id
,pov.vendor_name vendor_name
,ppyd.pay_site_id pay_site_id
,ppyd.payment_term_id payment_term_id
,ppyd.payment_method_code payment_method_code
,ppyd.pay_group_code pay_group_code
--
,sub_cleb.start_date start_date
,sub_cleb.end_date end_date
,subb.expire_after_days expire_after_days
,subb.currency_code currency_code
,subb.exclusive_yn exclusive_yn
,subb.applicable_to_release_yn applicable_to_release_yn
,subb.recourse_yn recourse_yn
,subb.termination_refund_basis termination_refund_basis
,subb.refund_formula_id refund_formula_id
,subb.receipt_method_code receipt_method_code
,subb.customer_visible_yn customer_visible_yn
from okl_subsidies_b subb,
okc_k_lines_b sub_cleb,
okc_k_lines_tl sub_clet,
okl_k_lines sub_kle,
okc_k_party_roles_b cplb,
po_vendors pov,
okl_party_payment_dtls ppyd
where ppyd.cpl_id(+) = cplb.id --payment details may not be mandatory
and ppyd.vendor_id(+) = cplb.object1_id1
and to_char(pov.vendor_id) = cplb.object1_id1
and cplb.object1_id2 = '#'
and cplb.jtot_object1_code = 'OKX_VENDOR'
and cplb.rle_code = 'OKL_VENDOR'
and cplb.cle_id = sub_cleb.id
and cplb.dnz_chr_id = sub_cleb.dnz_chr_id
and subb.id = sub_kle.subsidy_id
and sub_kle.id = sub_cleb.id
and sub_clet.id = sub_cleb.id
and sub_clet.language = userenv('LANG')
and sub_cleb.id = p_subsidy_cle_id;
select sub_cle.id
from okl_subsidies_b subb,
okl_k_lines sub_kle,
okc_k_lines_b sub_cle,
okc_line_styles_b sub_lse
where subb.id = sub_kle.subsidy_id
and subb.accounting_method_code = nvl(upper(p_accounting_method),subb.accounting_method_code)
and sub_kle.id = sub_cle.id
and sub_cle.cle_id = p_asset_cle_id
and sub_cle.lse_id = sub_lse.id
and sub_lse.lty_code = 'SUBSIDY'
and sub_cle.sts_code <> 'ABANDONED';
select sub_cle.id
from okc_k_lines_b sub_cle,
okc_line_styles_b sub_lse
where sub_cle.cle_id = p_asset_cle_id
and sub_cle.lse_id = sub_lse.id
and sub_lse.lty_code = 'SUBSIDY'
and sub_cle.sts_code <> 'ABANDONED';
select sub_cle.id
from okc_k_lines_b sub_cle,
okc_line_styles_b sub_lse
where sub_cle.cle_id = p_asset_cle_id
and sub_cle.lse_id = sub_lse.id
and sub_lse.lty_code = 'SUBSIDY'
and sub_cle.sts_code <> 'ABANDONED';
select cleb.id
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 cleb.lse_id = lseb.id
and lseb.lty_code = 'FREE_FORM1'
and cleb.sts_code = stsb.code
and stsb.ste_code not in ('HOLD','EXPIRED','TERMINATED','CANCELLED');
select cleb.id
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 cleb.lse_id = lseb.id
and lseb.lty_code = 'FREE_FORM1'
and cleb.sts_code = stsb.code
and stsb.ste_code not in ('HOLD','EXPIRED','TERMINATED','CANCELLED');
l_asset_asbv_tbl.delete;
select cleb.id
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 cleb.lse_id = lseb.id
and lseb.lty_code = 'FREE_FORM1'
and cleb.sts_code = stsb.code
and stsb.ste_code not in ('HOLD','EXPIRED','TERMINATED','CANCELLED');
select sub_cle.id
from
okl_k_lines sub_kle,
okc_k_lines_b sub_cle,
okc_line_styles_b sub_lse,
okl_subsidies_b subb,
okc_k_party_roles_b sub_vend,
okc_k_party_roles_b asst_vend,
okc_k_lines_b model_cle,
okc_line_styles_b model_lse,
okc_k_lines_b asst_cle,
okc_statuses_b asst_sts
--subsidy vendor
where sub_vend.dnz_chr_id = sub_cle.dnz_chr_id
and sub_vend.object1_id1 = nvl(to_char(p_vendor_id),sub_vend.object1_id1)
and sub_vend.object1_id1 = asst_vend.object1_id1 --to make sure asset and subsidy vendors are same
and sub_vend.object1_id2 = '#'
and sub_vend.jtot_object1_code = 'OKX_VENDOR'
and sub_vend.rle_code = 'OKL_VENDOR'
and sub_vend.cle_id = sub_cle.id
--subsidy receipt method is 'FUND' and subsidy accounting method is 'NET' (discount)
and subb.id = sub_kle.subsidy_id
and subb.receipt_method_code = 'FUND'
and subb.accounting_method_code = 'NET'
and sub_kle.id = sub_cle.id
--subsidy line
and sub_cle.cle_id = asst_cle.id
and sub_cle.dnz_chr_id = asst_cle.dnz_chr_id
and sub_cle.sts_code <> 'ABANDONED'
and sub_lse.id = sub_cle.lse_id
and sub_lse.lty_code = 'SUBSIDY'
--model line vendor
and asst_vend.dnz_chr_id = model_cle.dnz_chr_id
and asst_vend.object1_id1 = nvl(to_char(p_vendor_id),asst_vend.object1_id1)
and asst_vend.object1_id2 = '#'
and asst_vend.jtot_object1_code = 'OKX_VENDOR'
and asst_vend.rle_code = 'OKL_VENDOR'
and asst_vend.cle_id = model_cle.id
--model line
and model_cle.cle_id = asst_cle.id
and model_cle.dnz_chr_id = asst_cle.dnz_chr_id
and model_lse.id = model_cle.lse_id
and model_lse.lty_code = 'ITEM'
--financial asset
and asst_sts.code = asst_cle.sts_code
and asst_sts.ste_code not in ('HOLD','EXPIRED','CANCELLED')
and asst_cle.dnz_chr_id = p_chr_id
and asst_cle.chr_id = p_chr_id
and asst_cle.id = p_asset_cle_id;
select 'Y' from dual where exists
(
select null from OKC_LINE_STYLES_B SUB_LSE,
OKC_K_LINES_B SUB_CLE
where SUB_CLE.dnz_chr_id=p_chr_id
and SUB_LSE.LTY_CODE = 'SUBSIDY'
and SUB_CLE.lse_id= SUB_LSE.id
and SUB_CLE.CLE_ID =p_asset_cle_id
);
select sub_cle.id,
sub_cle.dnz_chr_id
from okl_k_lines sub_kle,
okc_k_lines_b sub_cle,
okc_line_styles_b sub_lse,
okl_subsidies_b subb,
okc_k_party_roles_b sub_vend,
okc_k_party_roles_b asst_vend,
okc_k_lines_b model_cle,
okc_line_styles_b model_lse,
okc_k_lines_b asst_cle,
okc_statuses_b asst_sts
--subsidy vendor
where sub_vend.dnz_chr_id = sub_cle.dnz_chr_id
--and sub_vend.object1_id1 = to_char(nvl(p_vendor_id,sub_vend.object1_id1))
and sub_vend.object1_id1 = asst_vend.object1_id1 --to make sure asset and subsidy vendors are same
and sub_vend.object1_id2 = '#'
and sub_vend.jtot_object1_code = 'OKX_VENDOR'
and sub_vend.rle_code = 'OKL_VENDOR'
and sub_vend.cle_id = sub_cle.id
--subsidy receipt method is 'FUND' and accounting method is 'NET'
and subb.id = sub_kle.subsidy_id
and subb.receipt_method_code = 'FUND'
and subb.accounting_method_code IN ('NET', 'AMORTIZE') --Added 'AMORTIZE' for bug 7664571
and sub_kle.id = sub_cle.id
--subsidy line
and sub_cle.cle_id = asst_cle.id
and sub_cle.dnz_chr_id = asst_cle.dnz_chr_id
and sub_cle.sts_code <> 'ABANDONED'
and sub_lse.id = sub_cle.lse_id
and sub_lse.lty_code = 'SUBSIDY'
--model line vendor
and asst_vend.dnz_chr_id = model_cle.dnz_chr_id
--and asst_vend.object1_id1 = nvl(to_char(p_vendor_id),asst_vend.object1_id1)
and asst_vend.object1_id2 = '#'
and asst_vend.jtot_object1_code = 'OKX_VENDOR'
and asst_vend.rle_code = 'OKL_VENDOR'
and asst_vend.cle_id = model_cle.id
--model line
and model_cle.cle_id = asst_cle.id
and model_cle.dnz_chr_id = asst_cle.dnz_chr_id
and model_lse.id = model_cle.lse_id
and model_lse.lty_code = 'ITEM'
--financial asset
and asst_sts.code = asst_cle.sts_code
and asst_sts.ste_code not in ('HOLD','EXPIRED','CANCELLED')
and asst_cle.dnz_chr_id = asst_cle.chr_id
and asst_cle.id = p_asset_cle_id;
SELECT cpl.id cpl_id,
cpl.jtot_object1_code object1_code,
cpl.object1_id1 object1_id1,
cpl.object1_id2 object1_id2,
rgd.id rgd_id,
cpl.bill_to_site_use_id bill_to_site_use_id,
cpl.role party_role,
cpl.cust_acct_id cust_acct_id
FROM okc_k_party_roles_v cpl,
okc_rg_party_roles rgpr,
okc_rule_groups_v rgd
WHERE cpl.id = cp_cpl_id
AND cpl.rle_code = 'OKL_VENDOR'
AND rgpr.cpl_id (+) = cpl.id
AND rgd.id (+) = rgpr.rgp_id
AND rgd.rgd_code(+) = 'LAVENB';
SELECT khr.Id khr_Id,
Par.Id Par_Id,
rgd.Id rgd_Id,
cPl.Bill_To_Site_Use_Id Bill_To_Site_Use_Id,
-- cPl.ROLE ParACty_Role,
cPl.cUst_acct_Id cUst_acct_Id
FROM Okl_k_Headers khr,
Okc_k_Headers_b Par,
Okc_Rule_Groups_v rgd,
Okc_k_Party_Roles_b cPl
WHERE khr.Id = cp_khr_Id
AND Par.Id = khr.khr_Id
AND Par.scs_Code = 'PROGRAM'
AND rgd.chr_Id = Par.Id
AND rgd.dnz_chr_Id = Par.Id
AND rgd.cle_Id IS NULL
AND rgd.rgd_Code = 'LAVENB'
AND Par.Id = cPl.chr_Id
AND cPl.rle_Code = 'OKL_VENDOR'
AND cPl.Object1_Id1 = cp_Vendor_Id
AND cPl.Object1_Id2 = '#'
AND cPl.jTot_Object1_Code = 'OKX_VENDOR';
SELECT c.receipt_method_id
FROM ra_cust_receipt_methods c
WHERE c.cust_receipt_method_id = cp_cust_rct_mthd;
SELECT a.cust_account_id cust_account_id,
a.cust_acct_site_id cust_acct_site_id,
a.payment_term_id payment_term_id
FROM okx_cust_site_uses_v a,
okx_customer_accounts_v c
WHERE a.id1 = cp_site_use_id
AND a.site_use_code = cp_site_use_code
AND c.id1 = a.cust_account_id;
SELECT c.standard_terms standard_terms
FROM hz_customer_profiles c
WHERE c.cust_account_id = cp_cust_id
AND c.site_use_id = cp_site_use_id
UNION
SELECT c1.standard_terms standard_terms
FROM hz_customer_profiles c1
WHERE c1.cust_account_id = cp_cust_id
AND c1.site_use_id IS NULL
AND NOT EXISTS (
SELECT '1'
FROM hz_customer_profiles c2
WHERE c2.cust_account_id = cp_cust_id
AND c2.site_use_id = cp_site_use_id);
select rule_information1,
rule_information2,
rule_information3,
rule_information4,
rule_information5,
rule_information6,
jtot_object1_code,
object1_id1,
object1_id2
from okc_rules_b
where rgp_id = p_rgp_id
and rule_information_category = p_rul_code
and dnz_chr_id = p_chr_id;
select rmc.bank_account_id
from OKX_RCPT_METHOD_ACCOUNTS_V rmc
where rmc.id1 = p_bank_acc_uses_id;
SELECT name
FROM okl_invoice_formats_v
WHERE ID = p_invoice_format_id;
SELECT id
FROM okl_trx_types_tl t
WHERE Upper (t.name) LIKE Upper (cp_try_name)
AND t.language = Upper (cp_language);
PROCEDURE insert_billing_records
(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_chr_id IN NUMBER
, p_asdv_tbl IN asbv_tbl_type) IS
l_return_status VARCHAR2(1) default OKL_API.G_RET_STS_SUCCESS;
l_api_name CONSTANT varchar2(30) := 'insert_billing_records';
Select 'Y'
From okc_k_party_roles_b cplb,
okc_k_lines_b cleb,
okc_line_styles_b lseb
where cplb.cle_id = cleb.id
and cleb.cle_id = p_asset_cle_id
and lseb.id = cleb.lse_id
and lseb.lty_code = 'ITEM'
and cplb.dnz_chr_id = p_chr_id
and cplb.object1_id1 = to_char(p_vendor_id)
and cplb.object1_id2 = '#'
and cplb.jtot_object1_code = 'OKX_VENDOR'
and cplb.rle_code = 'OKL_VENDOR';
select cplb.id
from okc_k_party_roles_b cplb
where cplb.chr_id = p_chr_id
and cplb.dnz_chr_id = p_chr_id
and cplb.cle_id is null
and cplb.object1_id1 = to_char(p_vendor_id)
and cplb.object1_id2 = '#'
and cplb.jtot_object1_code = 'OKX_VENDOR'
and cplb.rle_code = 'OKL_VENDOR';
okl_trx_ar_invoices_pub.insert_trx_ar_invoices (
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_taiv_rec => l_taiv_rec,
x_taiv_rec => lx_taiv_rec);
okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns (
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_tilv_rec => l_tilv_rec,
x_tilv_rec => lx_tilv_rec);
l_asdv_tbl_proc.delete;
END insert_billing_records;
select kle.SUBSIDY_ID
,clet.NAME
,clet.ITEM_DESCRIPTION
,kle.AMOUNT
,kle.SUBSIDY_OVERRIDE_AMOUNT
,cleb.orig_system_id1
,cplb.object1_id1 vendor_id
,cplb.id cpl_id
,kle.sty_id sty_id
from okl_k_lines kle,
okc_k_lines_tl clet,
okc_k_lines_b cleb,
okc_statuses_b stsb,
okc_line_styles_b lseb,
okc_k_party_roles_b cplb
where kle.id = cleb.id
and clet.id = cleb.id
and clet.language = userenv('LANG')
and cleb.dnz_chr_id = p_chr_id
and cleb.orig_system_id1 is not null
and stsb.code = cleb.sts_code
and stsb.ste_code not in ('CANCELLED')
and lseb.id = cleb.lse_id
and lseb.lty_code = 'SUBSIDY'
and cplb.cle_id = cleb.id
and cplb.rle_code = 'OKL_VENDOR'
and cplb.dnz_chr_id = p_chr_id;
select kle.SUBSIDY_ID
,clet.NAME
,clet.ITEM_DESCRIPTION
,kle.AMOUNT
,kle.SUBSIDY_OVERRIDE_AMOUNT
,cplb.object1_id1 vendor_id
,cplb.id cpl_id
,kle.sty_id sty_id
from okl_k_lines kle,
okc_k_lines_tl clet,
okc_k_lines_b cleb,
okc_statuses_b stsb,
okc_line_styles_b lseb,
okc_k_party_roles_b cplb
where kle.id = cleb.id
and clet.id = cleb.id
and clet.language = userenv('LANG')
and cleb.id = p_cle_id
and cleb.dnz_chr_id = p_chr_id
and stsb.code = cleb.sts_code
and stsb.ste_code not in ('CANCELLED')
and lseb.id = cleb.lse_id
and lseb.lty_code = 'SUBSIDY'
and cplb.cle_id = cleb.id
and cplb.rle_code = 'OKL_VENDOR'
and cplb.dnz_chr_id = p_chr_id;
select ID
,CPL_ID
,VENDOR_ID
,PAY_SITE_ID
,PAYMENT_TERM_ID
,PAYMENT_METHOD_CODE
,PAY_GROUP_CODE
from okl_party_payment_dtls
where cpl_id = p_cpl_id;
select ID
,CPL_ID
,VENDOR_ID
,PAY_SITE_ID
,PAYMENT_TERM_ID
,PAYMENT_METHOD_CODE
,PAY_GROUP_CODE
from okl_party_payment_dtls
where cpl_id = p_cpl_id;
select cleb.id cle_id,
cplb.id cpl_id
from okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_party_roles_b cplb
where cleb.dnz_chr_id = p_orig_chr_id
and lseb.id = cleb.lse_id
and lseb.lty_code = 'SUBSIDY'
and cplb.cle_id = cleb.id
and cplb.dnz_chr_id = p_orig_chr_id
and cplb.rle_code = 'OKL_VENDOR'
--Bug# 8766336
and cleb.sts_code <> 'ABANDONED'
--line was deleted from rebook copy :
and not exists (select '1'
from okc_k_lines_b cleb2
where cleb2.orig_system_id1 = cleb.id
and cleb2.dnz_chr_id = p_rbk_chr_id
--Bug# 8766336
and cleb2.sts_code <> 'ABANDONED')
--line is not a new line created during this rebook
and not exists (select '1'
from okc_k_lines_b cleb3
where cleb3.id = cleb.orig_system_id1
and cleb3.dnz_chr_id = p_rbk_chr_id);
select kle.subsidy_id subsidy_id,
cleb.id subsidy_cle_id,
clet.name name,
clet.item_description description,
kle.amount amount,
kle.subsidy_override_amount subsidy_override_amount,
cleb.dnz_chr_id dnz_chr_id,
cleb.cle_id asset_cle_id,
cplb.id cpl_id,
cplb.object1_id1 vendor_id,
cleb.lse_id lse_id,
cleb.display_sequence display_sequence,
cleb.start_date start_date,
cleb.end_date end_date,
cleb.currency_code currency_code,
cleb.sts_code sts_code,
kle.sty_id sty_id,
asst_cleb.orig_system_id1 orig_asst_cle_id,
--Bug# 8677460
kle.orig_contract_line_id orig_contract_line_id
from
okc_k_lines_b asst_cleb,
okc_statuses_b asst_sts,
okc_k_party_roles_b cplb,
okc_k_lines_tl clet,
okl_k_lines kle,
okc_line_styles_b lseb,
okc_k_lines_b cleb
Where asst_cleb.id = cleb.cle_id
And asst_cleb.dnz_chr_id = cleb.dnz_chr_id
And asst_sts.code = asst_cleb.sts_code
And asst_sts.ste_code not in ('HOLD','EXPIRED','TERMINATED','CANCELLED')
And cplb.jtot_object1_code = 'OKX_VENDOR'
And cplb.dnz_chr_id = cleb.dnz_chr_id
And cplb.cle_id = cleb.id
And cplb.rle_code = 'OKL_VENDOR'
And clet.id = cleb.id
And clet.language = userenv('LANG')
And kle.id = cleb.id
And lseb.id = cleb.lse_id
And lseb.lty_code = 'SUBSIDY'
And cleb.dnz_chr_id = p_chr_id
And cleb.orig_system_id1 is null
And asst_cleb.orig_system_id1 is not null
And cleb.sts_code <> 'ABANDONED';
select cleb.id
from okc_k_lines_b cleb
where cleb.orig_system_id1 = p_orig_cle_id;
okl_contract_pub.update_contract_line
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_sub_clev_rec,
p_klev_rec => l_sub_klev_rec,
x_clev_rec => lx_sub_clev_rec,
x_klev_rec => lx_sub_klev_rec);
okl_contract_pub.update_contract_line
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_clev_fin_rec,
p_klev_rec => l_klev_fin_rec,
x_clev_rec => lx_clev_fin_rec,
x_klev_rec => lx_klev_fin_rec
);
OKL_PYD_PVT.delete_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_ppydv_rec => l_pydv_rec);
OKL_PYD_PVT.insert_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_ppydv_rec => l_pydv_rec,
x_ppydv_rec => lx_pydv_rec);
okl_pyd_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_ppydv_rec => l_pydv_rec,
x_ppydv_rec => lx_pydv_rec);
okl_contract_pub.update_contract_line
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_sub_clev_rec,
p_klev_rec => l_sub_klev_rec,
x_clev_rec => lx_sub_clev_rec,
x_klev_rec => lx_sub_klev_rec);
/*********can not physically delete line on a booked K ****/
--Bug# 4899328 : Recalculate OEC and capital amount and update financial asset line
l_orig_asset_cle_id := lx_sub_clev_rec.cle_id;
okl_contract_pub.update_contract_line
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_clev_fin_rec,
p_klev_rec => l_klev_fin_rec,
x_clev_rec => lx_clev_fin_rec,
x_klev_rec => lx_klev_fin_rec
);
okl_contract_pub.update_contract_line
(p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_clev_rec => l_clev_fin_rec,
p_klev_rec => l_klev_fin_rec,
x_clev_rec => lx_clev_fin_rec,
x_klev_rec => lx_klev_fin_rec
);
OKL_PYD_PVT.insert_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_ppydv_rec => l_pydv_rec,
x_ppydv_rec => lx_pydv_rec);
insert_billing_records(
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_chr_id => p_orig_chr_id
, p_asdv_tbl => l_new_asdv_tbl);
SELECT CLEB.ID
FROM OKC_K_LINES_B CLEB,
OKC_STATUSES_B STSB,
OKC_LINE_STYLES_B LSEB
WHERE CLEB.ORIG_SYSTEM_ID1 = p_cle_id
AND CLEB.ORIG_SYSTEM_SOURCE_CODE ='OKC_LINE'
AND CLEB.DNZ_CHR_ID = p_chr_id
AND STSB.CODE = CLEB.STS_CODE
AND STSB.STE_CODE NOT IN ('CANCELLED')
AND LSEB.ID = CLEB.LSE_ID
AND LSEB.LTY_CODE = 'SUBSIDY';
SELECT 'Y'
FROM OKC_K_LINES_B ORIG_CLEB,
OKC_K_LINES_B RBK_CLEB
WHERE ORIG_CLEB.ID = p_cle_id
AND ORIG_CLEB.ORIG_SYSTEM_SOURCE_CODE ='OKC_LINE'
AND ORIG_CLEB.ORIG_SYSTEM_ID1 = RBK_CLEB.ID
AND RBK_CLEB.DNZ_CHR_ID = p_rbk_chr_id
AND RBK_CLEB.ORIG_SYSTEM_ID1 IS NULL;
SELECT TRX.ID
, TRX.DESCRIPTION
FROM OKL_TRX_AR_INVOICES_V TRX
, OKL_TXL_AR_INV_LNS_B TIL
WHERE TIL.TAI_ID = TRX.ID
AND TIL.KLE_ID = p_cle_id -- subsidy cle id
AND TRX.KHR_ID = p_chr_id
AND TRX.TRX_STATUS_CODE = G_SUBMIT_STATUS;
SELECT ARL.receivables_invoice_id receivables_invoice_id,
ARL.RECEIVABLES_INVOICE_LINE_ID invoice_line_id,
tai.ibt_id cust_acct_site_id,
tai.ixx_id cust_acct_id,
tai.irt_id payment_term_id,
tai.irm_id payment_method_id,
tai.khr_id khr_id,
tai.description tai_description,
tai.currency_code currency_code,
tai.date_invoiced date_invoiced,
tai.amount tai_amount,
tai.try_id try_id,
tai.trx_status_code trx_status_code,
tai.date_entered date_entered,
til.id til_id_reverses,
til.tai_id tai_id,
til.amount til_amount,
til.kle_id subsidy_cle_id,
til.description til_description,
til.sty_id stream_type_id,
til.line_number line_number,
til.inv_receiv_line_code inv_receiv_line_code
, til.bank_acct_id bank_acct_id
-- varangan - Bug#5588871 - Modified - Start
-- Consider Invoice line balance instead of invoice balance itself
, ARL.AMOUNT_LINE_ITEMS_REMAINING amount_remaining
-- varangan - Bug#5588871 - Modified - End
, ARL.AMOUNT_DUE_ORIGINAL invoice_amount
, ARL.AMOUNT_DUE_REMAINING amount_due_remaining
FROM OKL_BPD_AR_INV_LINES_V ARL
, OKL_TXL_AR_INV_LNS_V TIL
, OKL_TRX_AR_INVOICES_V TAI
WHERE
TIL.KLE_ID = p_cle_id -- < SUBSIDY CLE ID >
AND TAI.ID = TIL.TAI_ID
AND TAI.KHR_ID = p_chr_id
AND TAI.TRX_STATUS_CODE = 'PROCESSED'
AND TIL.ID = ARL.TIL_ID_DETAILS
AND ARL.AMOUNT_DUE_ORIGINAL > 0 -- donot consider credit memos
AND ARL.AMOUNT_DUE_REMAINING > 0 -- only those invoices that have some balance
AND NOT EXISTS (SELECT 1
FROM okl_cnsld_ar_strms_b lsm
WHERE lsm.receivables_invoice_id = ARL.receivables_invoice_id)
UNION
SELECT lsm.receivables_invoice_id receivables_invoice_id,
NULL invoice_line_id,
tai.ibt_id cust_acct_site_id,
tai.ixx_id cust_acct_id,
tai.irt_id payment_term_id,
tai.irm_id payment_method_id,
tai.khr_id khr_id,
tai.description tai_description,
tai.currency_code currency_code,
tai.date_invoiced date_invoiced,
tai.amount tai_amount,
tai.try_id try_id,
tai.trx_status_code trx_status_code,
tai.date_entered date_entered,
til.id til_id_reverses,
til.tai_id tai_id,
til.amount til_amount,
til.kle_id subsidy_cle_id,
til.description til_description,
til.sty_id stream_type_id,
til.line_number line_number,
til.inv_receiv_line_code inv_receiv_line_code
, til.bank_acct_id bank_acct_id
-- smadhava - Bug#5564198 - Modified - Start
-- Consider Invoice line balance instead of invoice balance itself
, APS.AMOUNT_LINE_ITEMS_REMAINING amount_remaining
-- smadhava - Bug#5564198 - Modified - End
, APS.AMOUNT_DUE_ORIGINAL invoice_amount
, APS.AMOUNT_DUE_REMAINING amount_due_remaining
FROM AR_PAYMENT_SCHEDULES_ALL APS
, OKL_CNSLD_AR_STRMS_B LSM
, OKL_XTL_SELL_INVS_B XTL
, OKL_TXL_AR_INV_LNS_V TIL
, OKL_TRX_AR_INVOICES_V TAI
WHERE LSM.RECEIVABLES_INVOICE_ID = APS.CUSTOMER_TRX_ID
AND XTL.LSM_ID = LSM.ID
AND TIL.ID = XTL.TIL_ID
AND TIL.KLE_ID = p_cle_id -- < SUBSIDY CLE ID >
AND TAI.ID = TIL.TAI_ID
AND TAI.KHR_ID = p_chr_id
AND APS.AMOUNT_DUE_ORIGINAL > 0 -- donot consider credit memos
AND APS.AMOUNT_DUE_REMAINING > 0 -- only those invoices that have some balance
ORDER BY AMOUNT_DUE_REMAINING DESC;
SELECT tai.ibt_id cust_acct_site_id,
tai.ixx_id cust_acct_id,
tai.irt_id payment_term_id,
tai.irm_id payment_method_id,
tai.khr_id khr_id,
tai.description tai_description,
tai.currency_code currency_code,
tai.date_invoiced date_invoiced,
tai.amount tai_amount,
tai.try_id try_id,
tai.trx_status_code trx_status_code,
tai.date_entered date_entered,
til.id til_id_reverses,
til.tai_id tai_id,
til.amount til_amount,
til.kle_id subsidy_cle_id,
til.description til_description,
til.sty_id stream_type_id,
til.line_number line_number,
til.inv_receiv_line_code inv_receiv_line_code
, til.bank_acct_id bank_acct_id
FROM OKL_TXL_AR_INV_LNS_V TIL
, OKL_TRX_AR_INVOICES_V TAI
WHERE TIL.KLE_ID = p_cle_id -- < SUBSIDY CLE ID >
AND TAI.ID = TIL.TAI_ID
AND TAI.KHR_ID = p_chr_id;
SELECT 'Y'
FROM OKL_TRX_AR_INVOICES_B TAI
, OKL_TXL_AR_INV_LNS_B TXL
WHERE TXL.TAI_ID = TAI.ID
AND TXL.KLE_ID = p_cle_id -- subsidy cle id
AND TAI.KHR_ID = p_chr_id
AND TAI.TRX_STATUS_CODE = G_PROCESSED_STATUS;
SELECT chr_cplb.id
FROM okc_k_party_roles_b chr_cplb,
okc_k_party_roles_b cle_cplb
WHERE cle_cplb.id = p_subsidy_cpl_id
AND chr_cplb.chr_id = p_chr_id
AND chr_cplb.dnz_chr_id = p_chr_id
AND chr_cplb.object1_id1 = cle_cplb.object1_id1
AND chr_cplb.object1_id2 = '#'
AND chr_cplb.jtot_object1_code = 'OKX_VENDOR'
AND chr_cplb.rle_code = 'OKL_VENDOR';
OKL_TRX_AR_INVOICES_PUB.insert_trx_ar_invoices (
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_taiv_rec => l_taiv_rec,
x_taiv_rec => lx_taiv_rec);
okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns (
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_tilv_rec => l_tilv_rec,
x_tilv_rec => lx_tilv_rec);
OKL_TRX_AR_INVOICES_PUB.insert_trx_ar_invoices (
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_taiv_rec => l_taiv_rec,
x_taiv_rec => lx_taiv_rec);
okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns (
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_tilv_rec => l_tilv_rec,
x_tilv_rec => lx_tilv_rec);
OKL_TRX_AR_INVOICES_PUB.insert_trx_ar_invoices (
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_taiv_rec => l_taiv_rec,
x_taiv_rec => lx_taiv_rec);
okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns (
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_tilv_rec => l_tilv_rec,
x_tilv_rec => lx_tilv_rec);
insert_billing_records(
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_chr_id => p_orig_chr_id
, p_asdv_tbl => l_new_asdv_tbl);
okl_trx_ar_invoices_pub.update_trx_ar_invoices (
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_taiv_tbl => l_cancel_taiv_tbl,
x_taiv_tbl => lx_cancel_taiv_tbl);
Select 'Y'
From okc_k_party_roles_b cplb,
okc_k_lines_b cleb,
okc_line_styles_b lseb
where cplb.cle_id = cleb.id
and cleb.cle_id = p_asset_cle_id
and lseb.id = cleb.lse_id
and lseb.lty_code = 'ITEM'
and cplb.dnz_chr_id = p_chr_id
and cplb.object1_id1 = to_char(p_vendor_id)
and cplb.object1_id2 = '#'
and cplb.jtot_object1_code = 'OKX_VENDOR'
and cplb.rle_code = 'OKL_VENDOR';
select cplb.id
from okc_k_party_roles_b cplb
where cplb.chr_id = p_chr_id
and cplb.dnz_chr_id = p_chr_id
and cplb.cle_id is null
and cplb.object1_id1 = to_char(p_vendor_id)
and cplb.object1_id2 = '#'
and cplb.jtot_object1_code = 'OKX_VENDOR'
and cplb.rle_code = 'OKL_VENDOR';
SELECT 'Y',
chrb.orig_system_id1,
ktrx.date_transaction_occurred
FROM okc_k_headers_b CHRB,
okl_trx_contracts ktrx
WHERE ktrx.khr_id_new = chrb.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
AND ktrx.representation_type = 'PRIMARY'
--
AND CHRB.id = p_chr_id
AND CHRB.ORIG_SYSTEM_SOURCE_CODE = 'OKL_REBOOK';
insert_billing_records(
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_chr_id => l_chr_id
, p_asdv_tbl => l_asdv_tbl);
l_asdv_tbl.delete;
select tcn.rbr_code
from okl_trx_contracts tcn,
okl_trx_types_tl ttl
where ttl.id = tcn.try_id
and ttl.language = 'US'
and ttl.name = 'Release'
and tcn.tsu_code = 'ENTERED'
and tcn.tcn_type = 'MAE'
--rkuttiya added for 12.1.1 Multi GAAP
and tcn.representation_type = 'PRIMARY'
--
and tcn.khr_id = p_chr_id;
select subb.transfer_basis_code
from okl_subsidies_b subb
where id = p_subsidy_id;