[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT id
FROM okl_trx_types_tl
WHERE name = cp_name
AND LANGUAGE = cp_language;
select cle_inst.cle_id financial_asset_id
from
okc_k_lines_b cle_inst,
okc_k_lines_b cle_ib,
okc_k_items cim_ib,
csi_item_instances cii,
cs_csi_counter_groups ccg,
csi_counters_vl cc,
okc_k_items cim,
okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_headers_b chrb
where cle_ib.id = cim_ib.cle_id
and cle_ib.dnz_chr_id = cim_ib.dnz_chr_id
--
and cle_inst.id = cle_ib.cle_id
and cle_inst.dnz_chr_id = cle_ib.dnz_chr_id
--
and cim_ib.object1_id1 = to_char(cii.instance_id)
and cim_ib.object1_id2 = '#'
and cim_ib.jtot_object1_code = 'OKX_IB_ITEM'
--
and cii.instance_id = ccg.source_object_id
and ccg.counter_group_id = cc.group_id
and cc.counter_id = cim.object1_id1
and cim.object1_id2 = '#'
and cim.jtot_object1_code = 'OKX_COUNTER'
and cim.cle_id = cleb.id
and cim.dnz_chr_id = cleb.dnz_chr_id
and cleb.dnz_chr_id = chrb.id
and lseb.id = cleb.lse_id
and lseb.lty_code = 'INST_CTR'
and chrb.id = c_khr_id
and cleb.id = c_kle_id;
select distinct rel.object1_id1 oks_khr_id, rel.chr_id okl_khr_id, cov_asset.id cov_asset_id, lns.id cle_id,
oks_cont.id oks_line_id, oks_cont.btn_id BTN_ID, oks_cont.amount LINE_AMOUNT,
oks_cont.CURRENCY_CODE, oks_cont.CLE_ID OKS_CLE_ID,
oks_lns.bcl_id BCL_ID, OKS_LNS.DATE_BILLED_FROM DATE_BILLED_FROM,
OKS_LNS.DATE_BILLED_TO DATE_BILLED_TO, CNTR.CLG_ID,
OKS_LNS.AMOUNT ASSET_AMOUNT, OKS_LNS.ID OKS_DETAIL_ID,
chr.contract_number contract_number
from okc_k_rel_objs rel, okc_k_lines_v lns, oks_bill_cont_lines_v oks_cont,
OKS_BILL_SUB_LINES_V OKS_LNS, OKC_K_HEADERS_B chr,
OKC_K_ITEMS ITEMS, OKL_CNTR_LVLNG_LNS_V CNTR, okc_k_lines_v cov_asset
where rel.rty_code = 'OKLUBB'
and lns.chr_id = rel.object1_id1
and lns.id = cov_asset.cle_id
and lns.id = oks_cont.cle_id
and OKS_LNS.BCL_ID = oks_cont.ID
AND cov_asset.id = oks_lns.cle_id -- Fix for bug 4659666
AND chr.contract_number = p_contract_number -- Added for Bug# 14119181
AND ITEMS.CLE_ID = OKS_LNS.CLE_ID
AND ITEMS.OBJECT1_ID1 = CNTR.KLE_ID(+)
AND OKS_LNS.amount > 0
AND rel.chr_id = chr.id
AND OKS_LNS.DATE_BILLED_FROM > (select NVL(max(tai.date_invoiced),add_months(sysdate,-1000))
from okl_trx_ar_invoices_v tai where tai.khr_id = rel.chr_id
and tai.description = 'OKS Usage')
AND not exists(select 'x' from okl_trx_ar_invoices_v tai, OKL_CNTR_LVLNG_LNS_V CNTR
where tai.khr_id = rel.chr_id
and CNTR.clg_id = tai.clg_id);
select distinct rel.chr_id okl_khr_id,
chr.contract_number contract_number,
iex_wf_ban_status_pub.get_turninvoff(hca.party_id,hca.cust_account_id,SYSDATE) bankruptcy_sts
from okc_k_rel_objs rel, okc_k_lines_v lns, oks_bill_cont_lines_v oks_cont,
OKS_BILL_SUB_LINES_V OKS_LNS, OKC_K_HEADERS_B chr,
OKC_K_ITEMS ITEMS, OKL_CNTR_LVLNG_LNS_V CNTR, okc_k_lines_v cov_asset,
hz_cust_accounts hca
where rel.rty_code = 'OKLUBB'
and chr.cust_acct_id = hca.cust_account_id
and hca.status = 'A'
and lns.chr_id = rel.object1_id1
and lns.id = cov_asset.cle_id
and lns.id = oks_cont.cle_id
and OKS_LNS.BCL_ID = oks_cont.ID
AND cov_asset.id = oks_lns.cle_id -- Fix for bug 4659666
AND ITEMS.CLE_ID = OKS_LNS.CLE_ID
AND ITEMS.OBJECT1_ID1 = CNTR.KLE_ID(+)
AND OKS_LNS.amount > 0
AND rel.chr_id = chr.id
AND OKS_LNS.DATE_BILLED_FROM > (select NVL(max(tai.date_invoiced),add_months(sysdate,-1000))
from okl_trx_ar_invoices_v tai where tai.khr_id = rel.chr_id
and tai.description = 'OKS Usage')
AND not exists(select 'x' from okl_trx_ar_invoices_v tai, OKL_CNTR_LVLNG_LNS_V CNTR
where tai.khr_id = rel.chr_id
and CNTR.clg_id = tai.clg_id);
SELECT ID FROM okl_trx_types_tl WHERE NAME = 'Billing' and LANGUAGE = 'US';
SELECT ID FROM okl_strm_type_v WHERE NAME = 'USAGE CHARGE';
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRUBBB.pls call Okl_Trx_Ar_Invoices_Pub.insert_trx_ar_invoices ');
Okl_Trx_Ar_Invoices_Pub.insert_trx_ar_invoices(
l_api_version
,l_init_msg_list
,l_return_status
,l_msg_count
,l_msg_data
,l_taiv_rec
,lx_taiv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRUBBB.pls call Okl_Trx_Ar_Invoices_Pub.insert_trx_ar_invoices ');
/*-- Start of wraper code generated automatically by Debug code generator for okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns
IF(IS_DEBUG_PROCEDURE_ON) THEN
BEGIN
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRUBBB.pls call okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns ');
okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns(
l_api_version
,l_init_msg_list
,l_return_status
,l_msg_count
,l_msg_data
,l_tilv_rec
,lx_tilv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRUBBB.pls call okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns ');
/*-- Start of wraper code generated automatically by Debug code generator for Okl_Txd_Ar_Ln_Dtls_Pub.insert_txd_ar_ln_dtls
IF(IS_DEBUG_PROCEDURE_ON) THEN
BEGIN
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRUBBB.pls call Okl_Txd_Ar_Ln_Dtls_Pub.insert_txd_ar_ln_dtls ');
Okl_Txd_Ar_Ln_Dtls_Pub.insert_txd_ar_ln_dtls(
l_api_version
,l_init_msg_list
,l_return_status
,l_msg_count
,l_msg_data
,l_tldv_rec
,lx_tldv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRUBBB.pls call Okl_Txd_Ar_Ln_Dtls_Pub.insert_txd_ar_ln_dtls ');
fnd_msg_pub.delete_msg();
select max(AR.DUE_DATE) last_bill_date, tai.description transaction_type
from okl_cnsld_ar_strms_b cnsld,
AR_PAYMENT_SCHEDULES_ALL AR,
OKL_XTL_SELL_INVS_V XTL,
okl_trx_ar_invoices_v tai,
okl_txl_ar_inv_lns_v til,
okl_txd_ar_ln_dtls_v tld
where cnsld.receivables_invoice_id = AR.customer_trx_id
and cnsld.khr_id = c_khr_id
and cnsld.id = XTL.lsm_id
and xtl.tld_id = tld.id
and til.tai_id = tai.id
and til.id = tld.til_id_details
and tai.description in ('Regular Stream Billing')
and cnsld.sel_id in (SELECT SEL.id
FROM OKL_STREAMS_V STM,
OKL_STRM_ELEMENTS_V SEL,
OKC_K_HEADERS_V KHR,
OKL_STRM_TYPE_V STY
WHERE KHR.id = c_khr_id
AND SEL.stream_element_date <= c_transaction_date
AND KHR.id = STM.khr_id
AND STM.id = SEL.stm_id
AND STM.say_code = 'CURR'
AND STM.active_yn = 'Y'
AND STM.sty_id = STY.id
AND NVL(STY.billable_yn,'N') = 'Y'
AND STY.stream_type_purpose = 'RENT'
AND SEL.amount > 0)
group by tai.description;
SELECT max(SEL.stream_element_date) last_sche_bill_date, sel.id
FROM OKL_STREAMS_V STM,
OKL_STRM_ELEMENTS_V SEL,
OKC_K_HEADERS_V KHR,
OKL_STRM_TYPE_B STY
WHERE KHR.id = c_khr_id
AND SEL.stream_element_date <= c_transaction_date
AND KHR.id = STM.khr_id
AND STM.id = SEL.stm_id
AND STM.say_code = 'CURR'
AND STM.active_yn = 'Y'
AND SEL.date_billed IS NULL
AND STM.sty_id = STY.id
AND NVL(STY.billable_yn,'N') = 'Y'
AND SEL.amount > 0
AND ROWNUM < 2;
SELECT sel.id stream_id,
sel.stream_element_date last_sche_bill_date
FROM OKL_STREAMS_V STM,
OKL_STRM_ELEMENTS_V SEL,
OKL_STRM_TYPE_V STY
WHERE sel.stream_element_date = (SELECT max(SEL.stream_element_date) last_sche_bill_date
FROM OKL_STREAMS_V STM,
OKL_STRM_ELEMENTS_V SEL,
OKC_K_HEADERS_V KHR,
OKL_STRM_TYPE_V STY
WHERE KHR.id = c_khr_id
AND SEL.stream_element_date <= c_transaction_date
AND KHR.id = STM.khr_id
AND STM.id = SEL.stm_id
AND STM.say_code = 'CURR'
AND STM.active_yn = 'Y'
AND STM.sty_id = STY.id
AND NVL(STY.billable_yn,'N') = 'Y'
AND STY.stream_type_purpose = 'RENT'
AND SEL.amount > 0)
AND STM.id = SEL.stm_id
AND STM.sty_id = STY.id
AND STY.stream_type_purpose = 'RENT'
AND STM.khr_id = c_khr_id
AND STM.say_code = 'CURR'
AND STM.active_yn = 'Y'
AND NVL(STY.billable_yn,'N') = 'Y'
AND ROWNUM < 2;
select max(schd.date_to_interface) last_sche_bill_date
from okc_k_rel_objs rel,
okc_k_headers_b hdr,
okc_k_headers_b oks,
okc_k_lines_b oks_line,
OKS_LEVEL_ELEMENTS_V schd, OKS_STREAM_LEVELS_B strm
where hdr.id = c_khr_id
and rty_code = 'OKLSRV'
and rel.jtot_object1_code = 'OKL_SERVICE'
and rel.cle_id is null
and rel.chr_id = hdr.id
and rel.object1_id1 = to_char(oks.id)
and oks.id = oks_line.dnz_chr_id
and oks_line.lse_id in (7,8,9,10,11,35)
and oks_line.id = strm.cle_id
and strm.id = schd.rul_id
and schd.date_to_interface <= c_transaction_date;
select max(schd.date_to_interface) last_sche_bill_date
from okc_k_rel_objs rel,
okc_k_headers_b hdr,
okc_k_headers_b oks,
okc_k_lines_b oks_line,
OKS_LEVEL_ELEMENTS_V schd,
okc_rules_b rules,
okc_rule_groups_b rgp
where hdr.id = c_khr_id
and rty_code = 'OKLSRV'
and rel.jtot_object1_code = 'OKL_SERVICE'
and rel.cle_id is null
and rel.chr_id = hdr.id
and rel.object1_id1 = to_char(oks.id)
and oks.id = oks_line.dnz_chr_id
and oks_line.lse_id in (7,8,9,10,11,35)
and oks_line.id = rgp.cle_id
and rules.rgp_id = rgp.id
and rules.id = schd.rul_id
and rules.rule_information_category = 'SLL'
and schd.date_to_interface <= c_transaction_date;
SELECT 1
FROM okc_class_operations
WHERE cls_code = 'SERVICE'
AND opn_code = 'CHECK_RULE';
-- Declare records: i - insert, u - update, r - result
------------------------------------------------------------
-- Transaction headers
i_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
select chr.contract_number contract_number, hdr.id khr_id, lns.id kle_id,
rel.object1_id1 oks_line_id, OKS_LNS.DATE_BILLED_FROM DATE_BILLED_FROM,
OKS_LNS.DATE_BILLED_TO DATE_BILLED_TO, OKS_LNS.AMOUNT asset_amount,
OKS_CONT.AMOUNT line_amount, OKS_CONT.CURRENCY_CODE CURRENCY_CODE,
okll.sty_id sty_id
from okc_k_rel_objs rel, okl_k_headers hdr, okc_k_headers_b chr, okc_k_lines_b lns,
okc_line_styles_b lse, okc_k_lines_b lnsb, OKS_BILL_CONT_LINES_V OKS_CONT,
OKS_BILL_SUB_LINES_V OKS_LNS, okl_k_lines okll
where rty_code = 'OKLSRV'
and rel.jtot_object1_code = 'OKL_COV_PROD'
and rel.chr_id = hdr.id
and hdr.id = chr.id
and chr.contract_number = NVL(c_contract_number,chr.contract_number)
and lse.lty_code = 'SOLD_SERVICE'
and lns.lse_id = lse.id
and lns.id = lnsb.cle_id
and rel.cle_id = lnsb.id
and lns.id = okll.id
and OKS_LNS.CLE_ID = rel.object1_id1
and OKS_CONT.ID = OKS_LNS.BCL_ID
--and OKS_LNS.DATE_BILLED_TO <= sysdate
and OKS_LNS.DATE_BILLED_FROM > (select NVL(max(tai.date_invoiced),add_months(sysdate,-1000))
from okl_trx_ar_invoices_v tai where tai.khr_id = hdr.id
and tai.description = 'OKS Billing')
order by chr.contract_number, OKS_LNS.date_billed_from, lns.id;
SELECT ID FROM okl_strm_type_v WHERE NAME = 'SERVICE FEE';
Okl_Trx_Ar_Invoices_Pub.update_trx_ar_invoices
(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,u_taiv_rec
,r_taiv_rec);
-- Insert transaction header record
---------------------------------------------
Okl_Trx_Ar_Invoices_Pub.insert_trx_ar_invoices
(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,i_taiv_rec
,r_taiv_rec);
Okl_Txl_Ar_Inv_Lns_Pub.update_txl_ar_inv_lns
(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,u_tilv_rec
,r_tilv_rec);
-- Insert transaction line record
---------------------------------------------
Okl_Txl_Ar_Inv_Lns_Pub.insert_txl_ar_inv_lns
(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,i_tilv_rec
,r_tilv_rec);
-- Insert transaction line detail record
----------------------------------------------------
Okl_Txd_Ar_Ln_Dtls_Pub.insert_txd_ar_ln_dtls
(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,i_tldv_rec
,r_tldv_rec);
Okl_Trx_Ar_Invoices_Pub.update_trx_ar_invoices
(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,u_taiv_rec
,r_taiv_rec);
Okl_Txl_Ar_Inv_Lns_Pub.update_txl_ar_inv_lns
(p_api_version
,p_init_msg_list
,l_return_status
,x_msg_count
,x_msg_data
,u_tilv_rec
,r_tilv_rec);