[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT khr.deal_type
FROM okl_k_headers_v khr ,fnd_lookups fnd
WHERE fnd.lookup_type = 'OKL_BOOK_CLASS'
AND fnd.lookup_code = khr.deal_type
AND id = p_contract_id;
SELECT revenue_recognition_method
FROM okl_product_parameters_v pdt,
okl_k_headers khr
WHERE KHR.ID = p_khr_id
AND KHR.PDT_ID = PDT.ID;
SELECT MIN(sel.stream_element_date)
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_v sty
WHERE stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND sty.billable_yn = 'Y'
AND sty.code NOT LIKE '%TAX%'
AND stm.purpose_code is NULL
AND stm.khr_id = c_contract_id
AND sel.stm_id = stm.id
AND sel.stream_element_date > sysdate;
SELECT MIN(sel.stream_element_date)
FROM okl_strm_elements sel
,okl_streams stm
,okl_strm_type_v sty
WHERE sty.stream_type_purpose = c_stream_type_purpose
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.purpose_code is NULL
AND stm.khr_id = c_contract_id
AND sel.stm_id = stm.id
and date_billed is null
AND sel.amount > 0 ;
SELECT NVL(sum(sel.amount),0)
FROM okl_strm_elements sel,
okl_streams stm,
okl_strm_type_v sty
--Bug 4084405
WHERE sty.stream_type_purpose = c_stream_type_purpose
AND stm.sty_id = sty.id
AND stm.say_code = 'CURR'
AND stm.active_yn = 'Y'
AND stm.purpose_code is NULL
AND stm.khr_id = c_contract_id
AND sel.stm_id = stm.id
AND date_billed is null
AND sel.stream_element_date = c_next_due_date;
SELECT deal_type
FROM okl_k_headers
WHERE id = p_contract_id;
/*SELECT (AR.amount * NVL(AR.exchange_rate,1)), AR.receipt_date
FROM ar_cash_receipts_all ar,
okc_k_headers_b chr,
ar_receivable_applications_all ara,
okl_cnSld_ar_strms_b lsm
WHERE
ar.pay_from_customer = chr.cust_acct_id
and chr.id = p_contract_id
AND ar.cash_receipt_id = ara.cash_receipt_id
AND LSM.RECEIVABLES_INVOICE_ID = ARA.APPLIED_CUSTOMER_TRX_ID
AND lsm.khr_id = p_contract_id
ORDER BY receipt_date DESC;*/
SELECT (AR.amount * NVL(AR.exchange_rate,1)), AR.receipt_date
FROM ar_cash_receipts_all ar,
okl_receipt_applications_uv app,
okc_k_headers_b chr
WHERE chr.id = p_contract_id
AND ar.cash_receipt_id = app.cash_receipt_id
AND ar.pay_from_customer = p_customer_id
AND CHR.contract_number = app.contract_number
ORDER BY receipt_date DESC;
select nvl(sum(okl.oec) ,0)
from okc_k_lines_v okc
,okl_k_lines okl
,okc_line_styles_v lse
where okc.id=okl.id
and okc.lse_id = lse.id
and lse.lty_code='FREE_FORM1'
and okc.chr_id = p_contract_id;
select okc.id
from okc_k_lines_v okc
,okl_k_lines okl
,okc_line_styles_v lse
where okc.id=okl.id
and okc.lse_id = lse.id
and lse.lty_code='FREE_FORM1'
and okc.chr_id = p_contract_id;
select nvl(sum(nvl(KLE1.subsidy_override_amount,KLE1.amount)),0) amount
from OKL_K_LINES KLE1,
OKC_K_LINES_B CLE1,
OKC_LINE_STYLES_B LS1,
OKL_ASSET_SUBSIDY_UV SUB,
OKL_SUBSIDIES_B SUBB,
OKL_SUBSIDIES_TL SUBT,
OKC_STATUSES_V STS1
where KLE1.ID = CLE1.ID
AND CLE1.LSE_ID = LS1.ID
AND LS1.LTY_CODE ='SUBSIDY'
AND cle1.dnz_chr_id = c_contract_id -- from parameter
AND CLE1.STS_CODE = STS1.CODE
AND CLE1.STS_CODE <> 'ABANDONED'
AND SUB.SUBSIDY_ID = KLE1.SUBSIDY_ID
AND SUB.ASSET_CLE_ID = c_parent_line_id -- parent_line_id from grid
AND SUB.dnz_chr_id = cle1.dnz_chr_id
AND SUB.subsidy_cle_id = KLE1.ID
AND SUBB.ID = KLE1.SUBSIDY_ID
AND SUBT.ID = SUBB.ID
And subt.language = userenv('LANG')
AND SUBB.accounting_method_code = 'NET'
AND SUBB.CUSTOMER_VISIBLE_YN = 'Y';
SELECT khr.start_date,khr.end_date,okhr.term_duration
FROM OKL_K_HEADERS okhr ,okc_k_headers_v khr
WHERE okhr.id = khr.id
AND khr.id = p_contract_id;
SELECT SUM(NVL(orv1.rule_information6,0))
FROM okc_rules_v orv1,
okc_rule_groups_b org1
WHERE org1.dnz_chr_id = p_contract_id
and org1.dnz_chr_id = org1.chr_id
AND org1.id = orv1.rgp_id
AND orv1.rule_information_category = 'LASLL'
AND exists
( SELECT 'x'
FROM okc_k_headers_v okhdr,
okc_rule_groups_b org,
okc_rules_v orv,
OKL_STRMTYP_SOURCE_V stm
WHERE okhdr.id = org1.dnz_chr_id
and okhdr.id = org.dnz_chr_id
and org.chr_id = org.dnz_chr_id
AND org.rgd_code = 'LALEVL'
AND org.id = orv.rgp_id
AND orv.rule_information_category ='LASLH'
AND jtot_object1_code ='OKL_STRMTYP'
AND object1_id1 = stm.id1
AND object1_id2 = stm.id2
AND stm.stream_type_purpose ='RENT');
SELECT ste.amount amount
FROM okc_k_lines_b kle,
okc_line_styles_b lse,
okc_k_items ite,
okl_strm_type_b sty1,
okl_streams stm,
okl_strm_type_b sty2,
okl_strm_elements ste
WHERE kle.chr_id = p_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 sty1.id = ite.object1_id1
AND sty1.stream_type_purpose= 'SECURITY_DEPOSIT'
AND stm.kle_id = kle.id
AND stm.khr_id = p_contract_id
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
--multigaap changes
AND stm.PURPOSE_CODE IS NULL
--end multigaap changes
AND sty2.id = stm.sty_id
AND sty2.stream_type_purpose= 'SECURITY_DEPOSIT'
AND ste.stm_id = stm.id
AND ste.date_billed IS NOT NULL
AND NVL (ste.amount, 0) <> 0;
SELECT DECODE(rule_information1, 'Y', 'Variable', 'N', 'Fixed', 'Unknown')
FROM OKC_K_HEADERS_B CHR,okc_rule_groups_b RGP,okc_rules_b RUL
WHERE CHR.ID = RGP.DNZ_CHR_ID AND
--CHR.ID = RGP.CHR_ID AND
RGP.ID = RUL.RGP_ID AND
RUL.rule_information_category = 'LAINTP' AND
CHR.id = p_contract_id;
SELECT notes,last_update_date FROM jtf_notes_vl
WHERE source_object_id = p_contract_id
AND SOURCE_OBJECT_CODE = 'OKC_K_HEADER'
ORDER BY last_update_date DESC;
SELECT PROGRAM_CONTRACT_NUMBER
FROM okl_k_hdrs_full_uv
WHERE CHR_ID = p_contract_id;
SELECT currency_code
FROM okc_k_headers_b
WHERE id = p_contract_id;
SELECT 'Y' FROM okc_k_headers_b CHR
WHERE id = p_contract_id
AND EXISTS
(
SELECT 'x' FROM okc_k_items cim
WHERE cim.object1_id1 = TO_CHAR(CHR.id)
AND EXISTS
(
SELECT 'x' FROM okc_k_lines_b cle, okc_line_styles_b lse
WHERE cle.lse_id = lse.id
AND lse.lty_code = 'SHARED'
AND cle.id = cim.cle_id
)
AND EXISTS
(
SELECT 'x' FROM okc_k_headers_b chr2
WHERE chr2.id = cim.dnz_chr_id
AND chr2.scs_code = 'SYNDICATION'
AND chr2.sts_code NOT IN ('TERMINATED','ABANDONED')
)
)
AND CHR.scs_code IN ('LEASE','LOAN');
SELECT authoring_org_id
FROM okc_k_headers_b
WHERE id = p_contract_id;
SELECT resource_id
FROM jtf_rs_resource_extns
WHERE user_id = l_user_id;
SELECT 'Y' FROM okc_k_headers_b CHR
WHERE id = p_contract_id
AND EXISTS
(
SELECT 'x' FROM okc_k_items cim
WHERE cim.object1_id1 = TO_CHAR(CHR.id)
AND EXISTS
(
SELECT 'x' FROM okc_k_lines_b cle, okc_line_styles_b lse
WHERE cle.lse_id = lse.id
AND lse.lty_code = 'SHARED'
AND cle.id = cim.cle_id
)
AND EXISTS
(
SELECT 'x' FROM okc_k_headers_b chr2
WHERE chr2.id = cim.dnz_chr_id
AND chr2.scs_code = 'SYNDICATION'
AND chr2.sts_code NOT IN ('TERMINATED','ABANDONED')
)
)
AND CHR.scs_code IN ('LEASE','LOAN');
SELECT scs_code
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT ras.access_level
FROM okc_subclass_resps ras
WHERE ras.scs_code = l_scs_code
AND ras.resp_id = Fnd_Global.resp_id
AND l_date BETWEEN ras.start_date AND NVL(ras.end_date, l_date);
SELECT res.resource_id
FROM jtf_rs_resource_extns res,
jtf_rs_role_relations rrr,
jtf_rs_roles_b rr
WHERE res.user_id = Fnd_Global.user_id
AND l_date BETWEEN res.start_date_active
AND NVL(res.end_date_active, l_date)
AND res.resource_id = rrr.role_resource_id
AND rrr.role_resource_type = 'RS_INDIVIDUAL'
AND NVL(rrr.delete_flag,'N') = 'N'
AND l_date BETWEEN rrr.start_date_active
AND NVL(rrr.end_date_active, l_date)
AND rrr.role_id = rr.role_id
AND rr.role_type_code = 'CONTRACTS';
SELECT cas.access_level
FROM okc_k_accesses cas
WHERE cas.chr_id = p_chr_id
AND cas.resource_id = g_user_resource_id;
SELECT cas.group_id,
cas.access_level
FROM okc_k_accesses cas
WHERE cas.chr_id = p_chr_id
AND cas.group_id IS NOT NULL
ORDER BY 2 DESC;
SELECT rgm.group_id
FROM jtf_rs_group_members rgm,
jtf_rs_role_relations rrr,
jtf_rs_roles_b rr,
jtf_rs_groups_b rgb
WHERE rgm.resource_id = g_user_resource_id
AND rgm.group_id = rgb.group_id
AND l_date BETWEEN NVL(rgb.start_date_active, l_date)
AND NVL(rgb.end_date_active, l_date)
AND rgm.group_id = rrr.role_resource_id
AND NVL(rgm.delete_flag,'N') = 'N'
AND rrr.role_resource_type = 'RS_GROUP'
AND NVL(rrr.delete_flag,'N') = 'N'
AND l_date BETWEEN rrr.start_date_active
AND NVL(rrr.end_date_active, l_date)
AND rrr.role_id = rr.role_id
AND rr.role_type_code = 'CONTRACTS'
UNION
SELECT rgd.parent_group_id
FROM jtf_rs_group_members rgm,
jtf_rs_groups_denorm rgd,
jtf_rs_role_relations rrr,
jtf_rs_roles_b rr,
jtf_rs_groups_b rgb
WHERE rgm.resource_id = g_user_resource_id
AND NVL(rgm.delete_flag,'N') = 'N'
AND rgd.group_id = rgm.group_id
AND rgd.parent_group_id = rgb.group_id
AND l_date BETWEEN NVL(rgb.start_date_active, l_date)
AND NVL(rgb.end_date_active, l_date)
AND rgd.parent_group_id = rrr.role_resource_id
AND rrr.role_resource_type = 'RS_GROUP'
AND NVL(rrr.delete_flag,'N') = 'N'
AND l_date BETWEEN rrr.start_date_active
AND NVL(rrr.end_date_active, l_date)
AND rrr.role_id = rr.role_id
AND rr.role_type_code = 'CONTRACTS';
SELECT notes_detail
FROM jtf_notes_tl
WHERE jtf_note_id = p_note_id
AND LANGUAGE = USERENV('LANG');
SELECT A.meaning
FROM ar_lookups A, hz_parties p
WHERE p.party_id = p_object_id
AND A.lookup_code = p.party_type
AND A.lookup_type = 'PARTY_TYPE';
p_select_id VARCHAR2,
p_select_name VARCHAR2,
p_select_details VARCHAR2,
p_from_table VARCHAR2,
p_where_clause VARCHAR2,
p_object_id NUMBER)
RETURN VARCHAR2 IS
l_sql_statement VARCHAR2(2000);
IF p_from_table IS NOT NULL AND p_select_id IS NOT NULL AND p_object_id IS NOT NULL THEN
IF p_select_name IS NOT NULL THEN
l_sql_statement := 'SELECT ' || p_select_name || ' ';
IF p_select_details IS NOT NULL THEN
IF l_sql_statement IS NOT NULL THEN
l_sql_statement := l_sql_statement || ' || '' - '' || ';
l_sql_statement := 'SELECT ';
l_sql_statement := l_sql_statement || p_select_details || ' ';
l_sql_statement := l_sql_statement || 'WHERE ' || p_select_id || ' = :p_object_id ';
SELECT sts_code FROM okc_k_headers_b
WHERE ID = p_contract_id;
SELECT ste_code FROM okc_statuses_b
WHERE code=p_okl_Status;
SELECT COUNT(*)
FROM HZ_CUST_ACCOUNTS CA,
HZ_PARTIES P,OKC_K_HEADERS_V CHR,OKC_STATUSES_V STAT
WHERE CHR.scs_code = 'LEASE' AND
CHR.authoring_org_id = mo_global.get_current_org_id() AND
ca.cust_account_id =chr.cust_acct_id AND
ca.party_id = p.party_id AND
CHR.sts_code = stat.code AND
stat.code = 'BOOKED' AND
ca.cust_account_id = p_cust_acct_id;
PROCEDURE update_deal(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2,
p_durv_tbl IN deal_tbl_type,
x_durv_tbl OUT NOCOPY deal_tbl_type
) AS
i number;
okl_deal_create_pub.update_deal(
p_api_version => p_api_version,
p_init_msg_list => FND_API.G_FALSE,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_durv_rec => p_durv_tbl(i),
x_durv_rec => x_durv_tbl(i));
END update_deal;
PROCEDURE commit_update AS
BEGIN
commit;
select SECURITIZED_CODE
from OKL_K_HEADERS
where id = p_contract_id;
SELECT SUM(tax_amt)
FROM OKL_TAX_TRX_DETAILS
WHERE txs_id = p_trx_id;
SELECT sum(amount) from okl_cs_payment_detail_uv
WHERE khr_id = p_khr_id
AND sty_id = p_sty_id
AND nvl(kle_id,-1) = nvl(p_kle_id,-1) ;
SELECT fat.name
FROM okc_k_lines_b fa,
okc_k_lines_tl fat,
okc_line_styles_b stl,
okc_k_lines_b top_cle,
okc_line_styles_b top_stl,
okc_k_lines_b sub_cle,
okc_line_styles_b sub_stl,
okc_k_items cim
WHERE top_cle.lse_id = top_stl.id
AND top_stl.lty_code in ('SOLD_SERVICE','FEE')
AND top_cle.id = sub_cle.cle_id
AND sub_cle.lse_id = sub_stl.id
AND sub_stl.lty_code in ('LINK_SERV_ASSET','LINK FEE ASSET')
AND cim.cle_id = sub_cle.id
AND CIM.JTOT_OBJECT1_CODE = 'OKX_COVASST'
AND CIM.OBJECT1_ID1 = FA.ID
AND FA.LSE_ID = STL.ID
AND fa.id = fat.id
AND fat.language = USERENV('LANG')
AND STL.LTY_CODE = 'FREE_FORM1'
AND sub_cle.id = cp_kle_id;
SELECT SUM(tax_line.tax_amt) TAX_AMOUNT
FROM ap_invoice_lines_all inv_ln
, zx_lines tax_line
, fnd_application app_ap
WHERE tax_line.application_id = app_ap.application_id
AND app_ap.application_short_name = 'SQLAP'
AND tax_line.entity_code = 'AP_INVOICES'
AND tax_line.event_class_code = 'STANDARD INVOICES'
AND tax_line.trx_id = inv_ln.invoice_id
AND tax_line.trx_level_type = 'LINE'
AND tax_line.trx_line_number = inv_ln.line_number
AND inv_ln.invoice_id = p_invoice_id
AND inv_ln.line_number = p_line_number;
select rule_information5
from okc_rules_b
where dnz_chr_id = p_contract_id
and rule_information_category = 'LASTPR';
SELECT cim1.object1_id1
FROM okc_k_lines_b cle,
okc_k_lines_b cle1, /* to get all line types associated with free form 1 in that FIXED_ASSET */
okc_k_items cim, /* to get the one having covered asset */
okc_k_lines_b cle2, /* After getting the one having the covered asset take the id of the service line */
okc_line_styles_b lse, /* to get free_form 1 */
okc_line_styles_b lse1, /* to get FIXED_ASSET lty_code */
okc_line_styles_b lse2, /* to get LINK_SERV_ASSET lty_code */
okc_k_items cim1 /* to take the actual asset id */
WHERE lse.id = cle.lse_id
AND lse1.id = cle1.lse_id
AND lse1.lty_code = 'FIXED_ASSET'
AND cle.id = cle1.cle_id
AND lse.lty_code = 'FREE_FORM1'
AND cle1.cle_id = cim.object1_id1
AND cim.jtot_object1_code = 'OKX_COVASST'
AND cim.cle_id = cle2.id
AND lse2.id = cle2.lse_id
AND cle1.id = cim1.cle_id
AND cim1.jtot_object1_code = 'OKX_ASSET'
AND cle2.id = cp_kle_id;
For clarity in the query result, select the following in the above SELECT statement to see
the asset_id of the asset associated to a service line, the line id of the associated asset line and
the service id to which the asset is associated.
Also the corresponding lty_code is selected to verify the result set of the query.
SELECT cim1.object1_id1 "Asset ID",
cle1.id "FIXED ASSET LINE ID", lse1.lty_code "lty code of fixed asset line",
cle2.id "SERVICE LINE ID", lse2.lty_code "lty code of service line"
Note: The input for the above query would be the id of the service line.
*/
l_asset_id okc_k_items.object1_id1%TYPE;
SELECT COUNT(1) payment_remaining
FROM OKL_STRM_TYPE_v STYT,
okl_strm_elements sele,
okl_streams str
WHERE sele.stm_id = str.id
AND str.sty_id = styt.id
AND str.say_code = 'CURR'
AND STR.ACTIVE_YN = 'Y'
AND STR.PURPOSE_CODE IS NULL
AND SELE.DATE_BILLED IS NULL
AND styt.billable_yn = 'Y'
AND styt.stream_type_purpose IN ('RENT','PRINCIPAL_PAYMENT','LOAN_PAYMENT')
AND str.khr_id = p_contract_id
AND str.kle_id = p_cle_id;
SELECT rgp_lalevl.cle_id,
RUL_LASLL.RULE_INFORMATION5 STRUCTURE_CODE,
RUL_LASLL.OBJECT1_ID1 FREQUENCY_CODE,
NVL(RUL_LASLL.RULE_INFORMATION10,'N') ARREARS_YN,
FND_DATE.canonical_to_date(rul_lasll.rule_information2) start_date,
rul_lasll.rule_information7 stub_days,
rul_lasll.rule_information3 periods
FROM OKC_RULE_GROUPS_B RGP_LALEVL,
OKC_RULES_B RUL_LASLH,
OKC_RULES_B RUL_LASLL,
OKL_STRM_TYPE_v STYT
WHERE RGP_LALEVL.RGD_CODE = 'LALEVL'
AND RUL_LASLH.RGP_ID = RGP_LALEVL.ID
AND RUL_LASLH.RULE_INFORMATION_CATEGORY = 'LASLH'
AND RUL_LASLH.DNZ_CHR_ID = RGP_LALEVL.DNZ_CHR_ID
AND STYT.ID = RUL_LASLH.OBJECT1_ID1
AND RUL_LASLL.RULE_INFORMATION_CATEGORY = 'LASLL'
AND RUL_LASLL.RGP_ID = RUL_LASLH.RGP_ID
AND RUL_LASLL.DNZ_CHR_ID = RUL_LASLH.DNZ_CHR_ID
AND RUL_LASLL.OBJECT2_ID1 = RUL_LASLH.ID
AND NVL(RUL_LASLL.OBJECT2_ID2,'#') = '#'
AND RUL_LASLL.JTOT_OBJECT2_CODE = 'OKL_STRMHDR'
AND styt.stream_type_purpose IN ('RENT','PRINCIPAL_PAYMENT','LOAN_PAYMENT')
AND RGP_LALEVL.DNZ_CHR_ID = p_contract_id;
SELECT okhr.term_duration-DECODE(sign(sysdate-khr.start_date),-1,0,DECODE(sign(sysdate-khr.end_date),1,okhr.term_duration,TRUNC(MONTHS_BETWEEN(sysdate,khr.start_date))))
payment_remaining
FROM okl_k_headers okhr ,
okc_k_headers_v khr
WHERE okhr.id = khr.id
AND khr.id = p_contract_id;
SELECT SUM(amount_original+amount_adjusted)
FROM okl_cs_bpd_inv_dtl_v
WHERE amount_remaining > 0
AND chr_id = cp_khr_id;
SELECT SUM(NVL(APS.AMOUNT_ADJUSTED,0)) AMOUNT_ADJUSTED
FROM AR_PAYMENT_SCHEDULES_ALL APS,
RA_CUSTOMER_TRX_ALL RACTRX,
RA_CUSTOMER_TRX_LINES_ALL RACTRX_LINE,
OKC_K_HEADERS_B OKC
WHERE RACTRX.CUSTOMER_TRX_ID = APS.CUSTOMER_TRX_ID
AND RACTRX.CUSTOMER_TRX_ID = RACTRX_LINE.CUSTOMER_TRX_ID
AND OKC.CONTRACT_NUMBER = RACTRX_LINE.INTERFACE_LINE_ATTRIBUTE6
AND OKC.ID = cp_khr_id;
SELECT SUM(amount_applied) + SUM(amount_credited)
FROM okl_cs_bpd_inv_dtl_v
WHERE amount_remaining > 0
AND chr_id = cp_khr_id;
SELECT SUM(amount_remaining)
FROM okl_cs_bpd_inv_dtl_v
WHERE amount_remaining > 0
AND chr_id = cp_khr_id;