[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT end_date
FROM okc_k_headers_b
WHERE id = p_contract_id;
SELECT start_date
FROM okc_k_headers_b
WHERE id = p_contract_id;
ELSIF p_quote_number IS NOT NULL AND p_source = 'UPDATE' THEN
-- Quote QUOTE_NUMBER can not be accepted. Please process interest calculation
-- for contract CONTRACT_NUMBER up to the quote effective from date.
OKL_API.set_message (
p_app_name => 'OKL',
p_msg_name => 'OKL_AM_UPD_TQ_RUN_INT_CALC',
p_token1 => 'QUOTE_NUMBER',
p_token1_value => p_quote_number,
p_token2 => 'CONTRACT_NUMBER',
p_token2_value => p_contract_number);
ELSIF p_quote_number IS NOT NULL AND p_source = 'UPDATE' THEN
-- Quote QUOTE_NUMBER can not be accepted. Please process interest calculation
-- for contract CONTRACT_NUMBER up to the quote effective from date.
OKL_API.set_message (
p_app_name => 'OKL',
p_msg_name => 'OKL_AM_UPD_TQ_RUN_INT_CALC',
p_token1 => 'QUOTE_NUMBER',
p_token1_value => p_quote_number,
p_token2 => 'CONTRACT_NUMBER',
p_token2_value => p_contract_number);
SELECT NVL(MAX(TMG.tmg_run),0) last_run
FROM OKL_TRX_MSGS TMG
WHERE TMG.trx_id = p_trx_id;
SELECT TMG.id,
TMG.object_version_number
FROM OKL_TRX_MSGS TMG
WHERE TMG.trx_id = p_trx_id
AND TMG.tmg_run IS NULL;
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'calling OKL_TRX_MSGS_PUB.update_trx_msgs');
OKL_TRX_MSGS_PUB.update_trx_msgs (
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_tmgv_tbl => lp_tmgv_tbl,
x_tmgv_tbl => lx_tmgv_tbl);
OKL_DEBUG_PUB.LOG_DEBUG(G_LEVEL_STATEMENT,l_module_name,'called OKL_TRX_MSGS_PUB.update_trx_msgs , return status: ' || l_return_status);
SELECT KREL.chr_id okl_contract_id
FROM OKC_K_HEADERS_B OKS_CHRB,
OKC_LINE_STYLES_B OKS_COV_PD_LSE,
OKC_K_LINES_B OKS_COV_PD_CLEB,
OKC_K_REL_OBJS KREL,
OKC_LINE_STYLES_B LNK_SRV_LSE,
OKC_STATUSES_B LNK_SRV_STS,
OKC_K_LINES_B LNK_SRV_CLEB,
OKC_K_ITEMS LNK_SRV_CIM
WHERE OKS_CHRB.scs_code = 'SERVICE'
AND OKS_CHRB.id = OKS_COV_PD_CLEB.dnz_chr_id
AND OKS_COV_PD_CLEB.lse_id = OKS_COV_PD_LSE.id
AND OKS_COV_PD_LSE.lty_code = 'COVER_PROD'
AND '#' = KREL.object1_id2
AND OKS_COV_PD_CLEB.id = KREL.object1_id1
AND KREL.rty_code = 'OKLSRV'
AND KREL.cle_id = LNK_SRV_CLEB.id
AND LNK_SRV_CLEB.lse_id = LNK_SRV_LSE.id
AND LNK_SRV_LSE.lty_code = 'LINK_SERV_ASSET'
AND LNK_SRV_CLEB.sts_code = LNK_SRV_STS.code
AND LNK_SRV_CLEB.id = LNK_SRV_CIM.cle_id
AND LNK_SRV_CIM.jtot_object1_code = 'OKX_COVASST'
AND LNK_SRV_CIM.object1_id2 = '#'
AND LNK_SRV_CIM.object1_id1 = p_kle_id;
SELECT COUNT(TQL.id) no_of_quote_assets
FROM OKL_TXL_QUOTE_LINES_B TQL
WHERE TQL.qlt_code = 'AMCFIA'
AND TQL.qte_id = p_qte_id;
SELECT COUNT(CLE.id) no_of_contract_assets
FROM OKC_K_LINES_B CLE,
OKC_LINE_STYLES_B LSE,
--OKC_STATUSES_B STS,
OKC_K_HEADERS_B CHR
WHERE CLE.lse_id = LSE.id
AND LSE.lty_code = 'FREE_FORM1'
--AND CLE.sts_code = STS.code
AND CLE.sts_code = CHR.sts_code
AND CHR.id = CLE.dnz_chr_id
--AND STS.ste_code NOT IN ('HOLD','EXPIRED','TERMINATED','CANCELLED') --OKC STATUSES
AND CLE.dnz_chr_id = p_khr_id;
SELECT TQL.kle_id,
TQL.asset_quantity,
TQL.quote_quantity
FROM OKL_TXL_QUOTE_LINES_B TQL
WHERE TQL.qlt_code = 'AMCFIA'
AND TQL.qte_id = p_qte_id;
SELECT TQL.kle_id,
TQL.asset_quantity,
TQL.quote_quantity
FROM OKL_TXL_QUOTE_LINES_B TQL
WHERE TQL.qlt_code = 'AMCFIA'
AND TQL.qte_id = p_qte_id;
SELECT 1
FROM OKC_K_LINES_B CLE,
OKC_LINE_STYLES_B LSE,
OKC_K_HEADERS_B CHR
WHERE CLE.lse_id = LSE.id
AND LSE.lty_code = 'FREE_FORM1'
AND CLE.sts_code = CHR.sts_code
AND CHR.id = CLE.dnz_chr_id
AND CLE.dnz_chr_id = p_khr_id
AND CLE.id NOT IN
(SELECT kle_id
FROM OKL_TXL_QUOTE_LINES_B
WHERE qte_id = p_qte_id
AND qlt_code = 'AMCFIA');
SELECT QTE.date_accepted
FROM OKL_TRX_QUOTES_B QTE
WHERE QTE.id = p_qte_id;
SELECT SYP.delink_yn
FROM OKL_SYSTEM_PARAMS SYP;
SELECT CHR.sts_code
FROM OKC_K_HEADERS_B CHR
WHERE CHR.id = p_chr_id;
p_oks_contract => l_oks_chr_id, -- RMUNJULU 23-DEC-03 SERVICE K UPDATES Pass OKS contract to WF
p_transaction_date => l_termination_date);
SELECT chr.id,
chr.contract_number,
chr.template_yn,
chr.end_date,
khr.deal_type,
chr.scs_code,
chr.sts_code,
sts.meaning
FROM OKC_K_HEADERS_V chr,
OKC_STATUSES_V sts,
OKL_K_HEADERS_V khr
WHERE chr.id = p_contract_id
AND chr.sts_code = sts.code
AND chr.id = khr.id;
SELECT khr.contract_number, sts.ste_code, sts.meaning
FROM okc_k_headers_b khr, okc_statuses_v sts
WHERE khr.sts_code = sts.code
AND khr.id = p_contract_id;
SELECT 'x'
FROM OKC_K_PROCESSES v
WHERE v.chr_id = p_contract_id
AND v.in_process_yn = 'Y';
SELECT 'x'
FROM OKX_INCIDENT_STATUSES_V xis,
OKC_K_LINES_B cle
WHERE cle.id = xis.contract_service_id
AND cle.dnz_chr_id = p_contract_id
AND xis.status_code IN ('OPEN');
SELECT 'x'
FROM OKL_TRX_QUOTES_V
WHERE khr_id = p_contract_id
AND qst_code = 'ACCEPTED'
AND (qtp_code LIKE 'TER%' OR qtp_code LIKE 'RES%');
SELECT SYSDATE INTO l_sys_date FROM DUAL;
ELSIF (p_control_flag = 'TRMNT_QUOTE_UPDATE') THEN
-- Check user status
IF k_header_rec.sts_code NOT IN ('BOOKED', 'EVERGREEN', 'BANKRUPTCY_HOLD',
'LITIGATION_HOLD', 'TERMINATION_HOLD') THEN
-- Message: Contract (CONTRACT_NUMBER) is (STATUS).
OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
p_msg_name => 'OKL_AM_CONTRACT_INACTIVE',
p_token1 => 'CONTRACT_NUMBER',
p_token1_value => k_header_rec.contract_number,
p_token2 => 'STATUS',
p_token2_value => k_header_rec.meaning);
ELSIF (p_control_flag = 'RESTR_QUOTE_UPDATE') THEN
-- Check user status
IF k_header_rec.sts_code NOT IN ( 'BOOKED',
'BANKRUPTCY_HOLD',
'LITIGATION_HOLD') THEN
-- Message: Contract (CONTRACT_NUMBER) is (STATUS).
OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
p_msg_name => 'OKL_AM_CONTRACT_INACTIVE',
p_token1 => 'CONTRACT_NUMBER',
p_token1_value => k_header_rec.contract_number,
p_token2 => 'STATUS',
p_token2_value => k_header_rec.meaning);
ELSIF (p_control_flag = 'REPUR_QUOTE_UPDATE') THEN
-- Check user status
IF k_header_rec.sts_code NOT IN ( 'TERMINATED','EXPIRED') THEN
-- Message: Contract CONTRACT_NUMBER is still STATUS.
-- Unable to generate the quote until the contract
-- has been terminated or expired.
OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
p_msg_name => 'OKL_AM_CONTRACT_STILL_ACTIVE',
p_token1 => 'CONTRACT_NUMBER',
p_token1_value => k_header_rec.contract_number,
p_token2 => 'STATUS',
p_token2_value => k_header_rec.meaning);
ELSIF (p_control_flag = 'ASSET_RETURN_UPDATE') THEN
-- Check user status
IF k_header_rec.sts_code NOT IN ( 'TERMINATED','EXPIRED') THEN
--Message: Contract CONTRACT_NUMBER is still STATUS.
-- Unable to return assets for this contract.
OKL_API.set_message(p_app_name => OKL_API.G_APP_NAME,
p_msg_name => 'OKL_AM_K_INVALID_FOR_ART_ERR',
p_token1 => 'CONTRACT_NUMBER',
p_token1_value => k_header_rec.contract_number,
p_token2 => 'STATUS',
p_token2_value => k_header_rec.meaning);
SELECT id,
object_version_number,
sts_code,
scs_code
FROM OKC_K_HEADERS_B
WHERE id = p_term_rec.p_contract_id;
SELECT deal_type
FROM OKL_K_HEADERS_V
WHERE id = p_term_rec.p_contract_id;
SELECT QTE.id,
QTE.partial_yn
FROM OKL_TRX_QUOTES_V QTE
WHERE QTE.id = p_qte_id;
SELECT trunc(qte.date_effective_from),
trunc(qte.date_accepted)
FROM okl_trx_quotes_b qte
WHERE qte.id = p_quote_id;
SELECT SYSDATE INTO l_trn_date FROM DUAL;
select KLE1.amount,sub.stream_type_id, sub.refund_formula_id,
sub.subsidy_calc_basis, sub.recourse_yn,
sub.termination_refund_basis, sub.receipt_method_code,
sub.accounting_method_code accounting_method_code,sub.effective_to_date,
sub.vendor_id,sub.currency_code,
-- to_number(sgn.VALUE) accrual_stream_type_id, --RMUNJULU *** --rmunjulu bug 4399352
cle1.id subsidy_line_id, -- smoduga ***
nvl(kle1.subsidy_override_amount,nvl(kle1.amount,0)) subsidy_amount -- RMUNJULU 3280473
from OKL_K_LINES KLE1,
OKC_K_LINES_B CLE1,
OKC_LINE_STYLES_B LS1,
okl_subsidies_b SUB,
okl_subsidies_tl subt,
OKC_STATUSES_V STS1
-- ,okl_sgn_translations sgn --RMUNJULU *** --rmunjulu bug 4399352
where KLE1.ID = CLE1.ID
AND CLE1.LSE_ID = LS1.ID
AND LS1.LTY_CODE ='SUBSIDY'
AND cle1.dnz_chr_id = c_contract_id
AND CLE1.STS_CODE = STS1.CODE
AND CLE1.STS_CODE <> 'ABANDONED'
AND cle1.cle_id= c_parent_line_id
AND SUB.ID = KLE1.SUBSIDY_ID
AND SUBT.ID = SUB.ID
And subt.language = userenv('LANG') ;
select quote_number
from okl_trx_quotes_v
where id = c_quote_id
AND accepted_yn ='Y';
select end_date
from okc_k_headers_b
where id = c_contract_id;
select CURRENCY_CONVERSION_TYPE,CURRENCY_CONVERSION_RATE,
CURRENCY_CONVERSION_DATE
from okl_k_headers
where id = c_contract_id;
SELECT APPLICATION_ID
FROM FND_APPLICATION
WHERE APPLICATION_SHORT_NAME = 'OKL' ;
SELECT pay.vendor_id,
pay.pay_site_id
FROM okl_party_payment_dtls_v pay,
okc_k_party_roles_v party
WHERE pay.cpl_id = party.id
AND party.cle_id = p_subsidy_line_id ;
SELECT khr.pdt_id
FROM okl_k_headers khr
WHERE khr.id = p_khr_id;
SELECT id
FROM okl_trx_types_tl
WHERE name = c_name
AND language = c_language;
SELECT NAME
FROM OKL_FORMULAE_V
WHERE ID = c_refund_formula_id;
SELECT max(stream_element_date) accelerate_till_date
FROM okl_strm_elements_v sel,
okl_streams stm
where SEL.STM_ID = STM.ID
and STM.STY_ID = c_accrual_sty_id
and STM.khr_id = c_khr_id
AND STM.active_yn = 'Y'
AND STM.say_code = 'CURR';
SELECT chr.scs_code
FROM okc_k_headers_b chr
WHERE chr.id = p_khr_id;
SELECT NVL(CHK_ACCRUAL_PREVIOUS_MNTH_YN,'N')
FROM OKL_SYSTEM_PARAMS;
SELECT DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),
DECODE(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),
DECODE(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),
DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),
mo_global.get_current_org_id() INTO l_tapv_rec.REQUEST_ID,
l_tapv_rec.PROGRAM_APPLICATION_ID,
l_tapv_rec.PROGRAM_ID,
l_tapv_rec.PROGRAM_UPDATE_DATE,
l_tapv_rec.ORG_ID
FROM dual;
SELECT DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,FND_GLOBAL.CONC_REQUEST_ID),
DECODE(FND_GLOBAL.PROG_APPL_ID,-1,NULL,FND_GLOBAL.PROG_APPL_ID),
DECODE(FND_GLOBAL.CONC_PROGRAM_ID,-1,NULL,FND_GLOBAL.CONC_PROGRAM_ID),
DECODE(FND_GLOBAL.CONC_REQUEST_ID,-1,NULL,SYSDATE),
mo_global.get_current_org_id() INTO l_tapv_rec.REQUEST_ID,
l_tapv_rec.PROGRAM_APPLICATION_ID,
l_tapv_rec.PROGRAM_ID,
l_tapv_rec.PROGRAM_UPDATE_DATE,
l_tapv_rec.ORG_ID FROM dual;
SELECT 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
FROM okl_trx_quotes_b qte
WHERE qte.id = p_qte_id;
SELECT stm.sty_id,
SUM(ste.amount) amount
FROM okl_streams stm
,okl_strm_type_b sty
,okc_k_lines_b kle
,okc_statuses_b kls
,okl_strm_elements ste
WHERE stm.khr_id = p_khr_id
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND ste.stm_id = stm.id
AND sty.id = stm.sty_id
AND sty.billable_yn = 'Y'
AND kle.id = stm.kle_id
AND kls.code = kle.sts_code
AND kls.ste_code = 'ACTIVE'
AND ste.date_billed IS NULL
AND trunc(ste.stream_element_date) > trunc(p_date)
AND kle.id = p_kle_id
AND STY.STREAM_TYPE_PURPOSE = 'ADVANCE_RENT'
GROUP BY stm.sty_id;
SELECT CLE.ID
FROM OKC_k_LINES_B CLE,
OKC_LINE_STYLES_B LTY
WHERE CLE.LSE_ID = LTY.ID
AND LTY.LTY_CODE IN ('FEE', 'SOLD_SERVICE') -- rmunjulu TNA Give the proper Alias
AND CLE.CHR_ID = p_chr_id; -- rmunjulu TNA Give the proper Alias
SELECT NVL(CHK_ACCRUAL_PREVIOUS_MNTH_YN,'N')
FROM OKL_SYSTEM_PARAMS;
SELECT A.MULTI_GAAP_YN,
B.REPORTING_PDT_ID
FROM OKL_K_HEADERS A,
OKL_PRODUCTS B
WHERE A.ID = p_chr_id
AND A.PDT_ID = B.ID;
SELECT secondary_rep_method
FROM okl_sys_acct_opts;
okl_trx_ar_invoices_pub.insert_trx_ar_invoices (
p_api_version => P_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 => lx_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 => lx_tilv_rec);
OKL_TRX_AR_INVOICES_PUB.insert_trx_ar_invoices (
p_api_version => P_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 => lx_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 => lx_tilv_rec);