The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT s.sty_id
FROM okl_quote_line_strm s
WHERE s.quote_line_type_code = cp_qlt_code;
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, -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
cpl.role party_role -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
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, -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
cpl.role party_role -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
FROM okl_k_headers khr,
okc_k_headers_all_b par,
okc_rule_groups_b rgd,
okc_k_party_roles_v cpl -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
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.dnz_chr_id (+) -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
AND cpl.rle_code (+) = 'OKL_VENDOR'; -- RMUNJULU 29-AUG-03 OKC RULES MIGRATION changes
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);
okl_trx_ar_invoices_pub.insert_trx_ar_invoices (
p_api_version => l_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_taiv_rec => l_taiv_rec,
x_taiv_rec => x_pos_taiv_rec);
okl_trx_ar_invoices_pub.insert_trx_ar_invoices (
p_api_version => l_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_taiv_rec => l_taiv_rec,
x_taiv_rec => x_neg_taiv_rec);
okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns (
p_api_version => l_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_tilv_rec => l_tilv_rec,
x_tilv_rec => x_tilv_rec);
SELECT kle.chr_id chr_id,
kle.id cle_id,
kle.name asset_number,
acn.currency_code currency_code,
acn.currency_conversion_type currency_conversion_type,
acn.currency_conversion_rate currency_conversion_rate,
acn.currency_conversion_date currency_conversion_date
,chr.inv_organization_id inv_organization_id
FROM okl_asset_cndtn_lns_b acn,
okl_asset_cndtns acd,
okc_k_lines_v kle
,okc_k_headers_b chr
WHERE acn.id = cp_acn_id
AND acd.id = acn.acd_id
AND kle.id = acd.kle_id
AND kle.dnz_chr_id = chr.id;
SELECT acn.part_name
FROM okl_asset_cndtn_lns_v acn
WHERE acn.id = cp_acn_id;
SELECT NVL(LEASE_INV_ORG_YN, 'N') LEASE_INV_ORG_YN
FROM OKL_SYSTEM_PARAMS;
SELECT legal_entity_id
FROM okl_asset_returns_all_b
WHERE kle_id = cp_kle_id;
SELECT oli.unit_selling_price price,
oli.ordered_quantity quantity,
ohe.ordered_date ordered_date,
kle.chr_id chr_id,
kle.id cle_id,
kle.name asset_number,
kle.item_description asset_description,
csu.cust_acct_site_id ibt_id,
csu.cust_account_id ixx_id,
art.currency_code currency_code,
art.currency_conversion_type currency_conversion_type,
art.currency_conversion_rate currency_conversion_rate,
art.currency_conversion_date currency_conversion_date,
NVL (oli.payment_term_id, ohe.payment_term_id) irt_id,
NVL (oli.sold_from_org_id, ohe.sold_from_org_id) org_id,
NVL(oli.ship_from_org_id, ohe.ship_from_org_id) ship_from_org_id, -- rmunjulu Bug 3985369 Get Ship_From_Org_Id which will be passed to inventory_org_id when creating a invoice transaction.
NULL set_of_books_id, -- derived from org_id
NULL irm_id, -- defaulted in AR
art.legal_entity_id
FROM oe_order_lines_all oli,
oe_order_headers_all ohe,
okl_asset_returns_b art,
okc_k_lines_v kle,
okx_cust_site_uses_v csu
WHERE oli.line_id = cp_order_line_id
AND ohe.header_id = oli.header_id
AND art.imr_id = oli.inventory_item_id
AND kle.id = art.kle_id
AND csu.id1 = nvl (oli.invoice_to_org_id, ohe.invoice_to_org_id);
SELECT til.id
FROM okl_txl_ar_inv_lns_v til
WHERE til.isl_id = cp_order_line_id;
SELECT inventory_item_id
FROM oe_order_lines_all
WHERE line_id = cp_order_line_id;
SELECT REMK_ITEM_INVOICED_CODE
,NVL(LEASE_INV_ORG_YN, 'N') LEASE_INV_ORG_YN
FROM OKL_SYSTEM_PARAMS ;
SELECT SYS_CONTEXT ('USERENV','NLS_DATE_FORMAT')
FROM dual;
SELECT end_date
FROM okc_k_headers_b
WHERE id = cp_contract_id;
SELECT sel.amount amount,
tld.id tld_id,
NULL lsm_id --ansethur 03/02/2007 Added For R12B Billing Architecture project
-- xls.lsm_id lsm_id --ansethur 03/02/2007 commented for R12B Billing Architecture project
FROM okc_k_lines_b kle,
okc_line_styles_b lse,
okc_k_items ite,
okl_streams stm,
okl_strm_elements sel,
okl_txd_ar_ln_dtls_b tld
-- ,okl_xtl_sell_invs_b xls --ansethur 03/02/2007 commented for R12B Billing Architecture project
WHERE kle.chr_id = cp_contract_id
AND lse.id = kle.lse_id
AND lse.lty_code = 'FEE'
AND ite.cle_id = kle.id
AND ite.jtot_object1_code = 'OKL_STRMTYP'
AND cp_sty_id = ite.object1_id1
AND stm.kle_id = kle.id
AND stm.khr_id = cp_contract_id
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND cp_sty_id = stm.sty_id
AND sel.stm_id = stm.id
AND sel.date_billed IS NOT NULL
AND NVL (sel.amount, 0) <> 0
AND tld.sel_id = sel.id
AND tld.tld_id_reverses IS NULL
-- AND xls.tld_id = tld.id --ansethur 03/02/2007 commented for R12B Billing Architecture project
ORDER BY sel.date_billed;
SELECT tld.id, tld.amount
FROM okl_txd_ar_ln_dtls_b tld
WHERE tld.tld_id_reverses = cp_inv_tld_id;
SELECT 'TERMINATION_QUOTE' transaction_source
FROM OKL_TRX_QUOTES_B qte
WHERE qte.id = p_quote_id; --akrangan modified for bug 7036873
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling okl_credit_memo_pub.insert_on_acc_cm_request');
okl_credit_memo_pub.insert_on_acc_cm_request (
p_api_version => l_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_tld_id => l_sdd_tbl(i).tld_id, -- ansethur 03/02/2007 Added For Billing Architecture Project
-- p_tld_id => l_sdd_tbl(i).tld_id, -- ansethur 03/02/2007 Commmented For Billing Architecture Project
p_credit_amount => l_dispose_amount,
p_credit_desc => l_description,
x_tai_id => l_tai_id,
x_taiv_rec => l_taiv_rec
,p_transaction_source => l_transaction_source);
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called okl_credit_memo_pub.insert_on_acc_cm_request , return status: ' || l_return_status);
SELECT qte.khr_id khr_id,
qte.qtp_code qtp_code,
flo.meaning description,
qte.date_accepted date_invoiced,
qte.currency_code currency_code,
qte.currency_conversion_type currency_conversion_type,
qte.currency_conversion_rate currency_conversion_rate,
qte.currency_conversion_date currency_conversion_date
--PAGARG Bug 4044659 Query inventory org id
,khr.inv_organization_id inv_organization_id,
qte.art_id -- RRAVIKIR Legal Entity Changes
FROM okl_trx_quotes_b qte,
fnd_lookups flo,
okc_k_headers_b khr
WHERE qte.id = cp_quote_id
AND flo.lookup_type = 'OKL_QUOTE_TYPE'
AND flo.lookup_code = qte.qtp_code
AND khr.id = qte.khr_id;
SELECT kpr.rle_code rle_code,
qpt.cpl_id cpl_id,
qpt.qpt_code qpt_code,
qpt.party_jtot_object1_code party_code,
qpt.party_object1_id1 party_id1,
qpt.party_object1_id2 party_id2,
qpt.allocation_percentage allc_perc
FROM okl_quote_parties qpt,
okc_k_party_roles_b kpr
WHERE qpt.qte_id = cp_quote_id
AND qpt.qpt_code IN ('RECIPIENT','RECIPIENT_ADDITIONAL')
AND kpr.id (+) = qpt.cpl_id
AND qpt.allocation_percentage <> 0; -- rmunjulu bug 4341480
SELECT qlt.kle_id kle_id,
qlt.amount amount,
qlt.line_number line_number,
qlt.sty_id sty_id,
qlt.qlt_code qlt_code,
flo.meaning description,
qlt.id quote_line_id -- rmunjulu sales_tax_enhancement
FROM okl_txl_quote_lines_b qlt,
fnd_lookups flo
WHERE qlt.qte_id = cp_quote_id
AND qlt.amount NOT IN (G_MISS_NUM, 0)
AND flo.lookup_type = 'OKL_QUOTE_LINE_TYPE'
AND flo.lookup_code = qlt.qlt_code
AND qlt.qlt_code NOT IN (
'BILL_ADJST', -- rmunjulu EDAT Added since billing adjustments will be handled separately
'AMCFIA', -- Used to save quote assets, not amounts
'AMCTAX', -- Estimated tax, AR will recalculate tax
'AMYOUB'); -- Outstanding balances are already billed
SELECT tai.id
FROM okl_trx_ar_invoices_v tai
WHERE tai.qte_id = cp_quote_id;
SELECT upper(nvl(qte.partial_yn,'N')) partial_yn,
qte.khr_id khr_id,
qte.date_effective_from date_eff_from,
QTE.DATE_ACCEPTED DATE_ACCEPTED, -- RMUNJULU 4547765 FUTURE_BILLS_BUG
qte.creation_date creation_date, --SECHAWLA 05-JAN-05 4926740 : added creation_date
chr.inv_organization_id inv_organization_id,
QTE.perdiem_amount -- rmunjulu LOANS_ENHACEMENTS
FROM OKL_TRX_QUOTES_B qte,
OKC_K_HEADERS_B chr
WHERE qte.id = p_quote_id
AND qte.khr_id = chr.id;-- rmunjulu 3985369
SELECT fnd.meaning meaning
FROM FND_LOOKUPS fnd
WHERE fnd.lookup_type = 'OKL_QUOTE_LINE_TYPE'
AND fnd.lookup_code = 'BILL_ADJST';
SELECT NVL(LEASE_INV_ORG_YN, 'N') LEASE_INV_ORG_YN
FROM OKL_SYSTEM_PARAMS;
SELECT legal_entity_id
FROM okl_asset_returns_all_b
WHERE id = cp_id;
SELECT ORIG_SYSTEM_SOURCE_CODE
FROM OKC_K_HEADERS_B
WHERE id = cp_khr_id;
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling okl_trx_ar_invoices_pub.update_trx_ar_invoices');
okl_trx_ar_invoices_pub.update_trx_ar_invoices (
p_api_version => l_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_taiv_rec => lu_taiv_rec,
x_taiv_rec => lx_taiv_rec);
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called okl_trx_ar_invoices_pub.update_trx_ar_invoices , return status: ' || l_return_status);
--amount remaining for the contract' in termination quote messages screen as per the update
--by PM *** MCORNEL 01/09/08 11:07 am *** in the bug#6508911 .
-- This message is already set in Create_Scrt_Dpst_Dsps_Inv.
OKL_AM_UTIL_PVT.process_messages(
p_trx_source_table => 'OKL_TRX_QUOTES_V',
p_trx_id => p_quote_id,
x_return_status => l_return_status
);
l_r_taiv_tbl.DELETE;
SELECT qtp_code, khr_id
FROM okl_trx_quotes_b
WHERE id = p_quote_id;
select fl.meaning
from fnd_lookups fl
where fl.lookup_type = 'OKL_QUOTE_LINE_TYPE'
and fl.lookup_code = p_qlt_code;
SELECT NVL(LEASE_INV_ORG_YN, 'N') LEASE_INV_ORG_YN
FROM OKL_SYSTEM_PARAMS;
SELECT fnd.meaning meaning
FROM FND_LOOKUPS fnd
WHERE fnd.lookup_type = 'OKL_QUOTE_LINE_TYPE'
AND fnd.lookup_code = 'BILL_ADJST';
SELECT upper(nvl(qte.partial_yn,'N')) partial_yn,
qte.khr_id khr_id,
qte.date_effective_from date_eff_from,
qte.date_accepted date_accepted,
chr.inv_organization_id inv_organization_id
FROM OKL_TRX_QUOTES_B qte,
OKC_K_HEADERS_B chr
WHERE qte.id = p_quote_id
AND qte.khr_id = chr.id;
SELECT qte.khr_id khr_id,
qte.qtp_code qtp_code,
flo.meaning description,
qte.date_accepted date_invoiced,
qte.currency_code currency_code,
qte.currency_conversion_type currency_conversion_type,
qte.currency_conversion_rate currency_conversion_rate,
qte.currency_conversion_date currency_conversion_date,
khr.inv_organization_id inv_organization_id
FROM okl_trx_quotes_b qte,
fnd_lookups flo,
okc_k_headers_b khr
WHERE qte.id = cp_quote_id
AND flo.lookup_type = 'OKL_QUOTE_TYPE'
AND flo.lookup_code = qte.qtp_code
AND khr.id = qte.khr_id;
l_tmp_tilv_tbl.delete; -- rmunjulu bug 6791004
-- The sum of adjustment is again updated the the amount of taiv_rec for the roll over quote
l_l_cnt := l_tilv_tbl.FIRST;
okl_trx_ar_invoices_pub.update_trx_ar_invoices(
p_api_version => l_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_taiv_rec => l_roll_upd_taiv_rec,
x_taiv_rec => lx_roll_upd_taiv_rec);
okl_trx_ar_invoices_pub.update_trx_ar_invoices(
p_api_version => l_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_taiv_rec => l_roll_upd_taiv_rec,
x_taiv_rec => lx_roll_upd_taiv_rec);