[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DEAL_TYPE
FROM OKL_K_HEADERS
WHERE ID = p_chr_id;
SELECT RULE_INFORMATION1
FROM OKC_RULES_B
WHERE RULE_INFORMATION_CATEGORY = 'LAEVEL'
AND DNZ_CHR_ID = p_chr_id;
SELECT RULE_INFORMATION1
FROM OKC_RULES_B
WHERE RULE_INFORMATION_CATEGORY = 'LAEVEL'
AND DNZ_CHR_ID = p_chr_id;
SELECT OBJECT1_ID1,OBJECT2_ID1
FROM OKC_RULES_B
WHERE RULE_INFORMATION_CATEGORY = 'LAEVPT'
AND DNZ_CHR_ID = p_chr_id;
SELECT DEAL_TYPE
FROM OKL_K_HEADERS
WHERE ID = p_chr_id;
SELECT bill_to_site_use_id
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT cust_acct_id
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT 'Y'
FROM DUAL
WHERE EXISTS
(
SELECT 'Y'
FROM OKC_K_ITEMS fa_cim,
OKC_K_LINES_B fa_cle,
OKC_LINE_STYLES_B fa_lse,
OKC_STATUSES_B fa_sts
WHERE fa_cim.cle_id = fa_cle.id
AND fa_cim.dnz_chr_id = fa_cle.dnz_chr_id
AND fa_cle.dnz_chr_id = chrid
AND fa_cle.lse_id = fa_lse.id
AND fa_lse.lty_code = 'FIXED_ASSET'
AND fa_cle.sts_code = fa_sts.code
AND fa_sts.ste_code NOT IN ('CANCELLED','TERMINATED','HOLD','EXPIRED')
AND fa_cim.object1_id1 IS NULL
AND EXISTS (SELECT '1'
FROM OKC_K_LINES_B fa_cle2,
OKC_LINE_STYLES_B fa_lse2,
OKC_STATUSES_B fa_sts2
WHERE fa_cle2.lse_id = fa_lse2.id
AND fa_lse2.lty_code = 'FIXED_ASSET'
AND fa_cle2.sts_code = fa_sts2.code
AND fa_sts2.ste_code NOT IN ('CANCELLED','TERMINATED','HOLD','EXPIRED')
AND fa_cle2.dnz_chr_id = chrid
)
UNION
SELECT 'Y'
FROM OKC_K_ITEMS fa_cim,
OKC_K_LINES_B fa_cle,
OKC_LINE_STYLES_B fa_lse,
OKC_STATUSES_B fa_sts
WHERE fa_cim.cle_id = fa_cle.id
AND fa_cim.dnz_chr_id = fa_cle.dnz_chr_id
AND fa_cle.dnz_chr_id = chrid
AND fa_cle.lse_id = fa_lse.id
AND fa_lse.lty_code = 'FIXED_ASSET'
AND fa_cle.sts_code = fa_sts.code
AND fa_sts.ste_code NOT IN ('CANCELLED','TERMINATED','HOLD','EXPIRED')
AND fa_cim.object1_id1 IS NOT NULL
AND NOT EXISTS(SELECT '1'
FROM FA_ADDITIONS_B FAB
WHERE FAB.Asset_id = TO_NUMBER(fa_cim.object1_id1)
)
AND EXISTS (SELECT '1'
FROM OKC_K_LINES_B fa_cle2,
OKC_LINE_STYLES_B fa_lse2,
OKC_STATUSES_B fa_sts2
WHERE fa_cle2.lse_id = fa_lse2.id
AND fa_lse2.lty_code = 'FIXED_ASSET'
AND fa_cle2.sts_code = fa_sts2.code
AND fa_sts2.ste_code NOT IN ('CANCELLED','TERMINATED','HOLD','EXPIRED')
AND fa_cle2.dnz_chr_id = chrid
) ); */
SELECT 'Y'
FROM DUAL
WHERE EXISTS
(
SELECT 1
FROM OKC_K_LINES_B fa_cle,
OKC_LINE_STYLES_B fa_lse,
OKC_STATUSES_B fa_sts,
OKC_K_ITEMS fa_cim,
FA_ADDITIONS_B FAB
WHERE fa_cim.cle_id = fa_cle.id
AND fa_cim.dnz_chr_id = fa_cle.dnz_chr_id
AND fa_cle.dnz_chr_id = chrId
AND fa_cle.lse_id = fa_lse.id
AND fa_lse.lty_code = 'FIXED_ASSET'
AND fa_cle.sts_code = fa_sts.code
AND fa_sts.ste_code NOT IN ('CANCELLED','TERMINATED','HOLD','EXPIRED')
AND FAB.Asset_id(+) = fa_cim.object1_id1
AND FAB.Asset_id is null
);
SELECT 'Y'
FROM okc_k_headers_b chrb
WHERE chrb.id = chrid
AND EXISTS (SELECT '1'
FROM okc_k_lines_b cleb
WHERE cleb.chr_id = chrb.id
AND cleb.dnz_chr_id = chrb.id);
SELECT item_id inventory_item_id,
SUM(service_item_qty) quantity
FROM okl_la_cov_asset_uv
WHERE serv_top_line_id = p_serv_top_line_id
GROUP BY item_id;
SELECT item_id inventory_item_id,
SUM(NVL(price_negotiated,0)) amount
FROM okl_la_cov_asset_uv
WHERE serv_top_line_id = p_serv_top_line_id
GROUP BY item_id;
SELECT
rgpb.cle_id kle_id,
rulb2.RULE_INFORMATION2 start_date,
rulb2.RULE_INFORMATION3 level_periods,
rulb2.RULE_INFORMATION7 stub_days,
rulb2.RULE_INFORMATION8 stub_amount,
rulb2.RULE_INFORMATION10 arrear_yn
FROM okc_k_lines_b cleb,
okc_rule_groups_b rgpb,
okc_rules_b rulb,
okc_rules_b rulb2,
okl_strm_type_b styb
WHERE rgpb.chr_id IS NULL
AND rgpb.dnz_chr_id = cleb.dnz_chr_id
AND rgpb.cle_id = cleb.id
AND cleb.dnz_chr_id = p_chr_id
AND rgpb.rgd_code = 'LALEVL'
AND rulb.rgp_id = rgpb.id
AND rulb.rule_information_category = 'LASLH'
AND TO_CHAR(styb.id) = rulb.object1_id1
AND rulb2.object2_id1 = TO_CHAR(rulb.id)
AND rulb2.rgp_id = rgpb.id
AND rulb2.rule_information_category = 'LASLL'
AND styb.STREAM_TYPE_PURPOSE = p_stream_purpose_code
ORDER BY kle_id, start_date, level_periods;
SELECT contract_number
FROM okc_k_headers_v WHERE id = p_id;
SELECT start_date
FROM okc_k_headers_b
WHERE id = p_id;
SELECT crg.cle_id,
crl.id,
stty.stream_type_purpose,
crl.object1_id1,
crl.RULE_INFORMATION1,
crl.RULE_INFORMATION2,
crl.RULE_INFORMATION3,
crl.RULE_INFORMATION4,
crl.RULE_INFORMATION5,
crl.RULE_INFORMATION6,
crl.RULE_INFORMATION10,
crl.RULE_INFORMATION11
FROM OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl,
OKL_STRM_TYPE_B stty
WHERE stty.id = crl.object1_id1
AND stty.stream_type_purpose IN (
'RENT', 'PRINCIPAL_PAYMENT', 'VARIABLE_INTEREST_SCHEDULE',
'LOAN_PAYMENT'
)
AND crl.rgp_id = crg.id
AND crg.RGD_CODE = rgcode
AND crl.RULE_INFORMATION_CATEGORY = rlcat
AND crg.dnz_chr_id = chrId;
SELECT crg.cle_id,
crl.id,
crl.object1_id1,
crl.RULE_INFORMATION1,
crl.RULE_INFORMATION2,
crl.RULE_INFORMATION3,
crl.RULE_INFORMATION4,
crl.RULE_INFORMATION5,
crl.RULE_INFORMATION6,
crl.RULE_INFORMATION10,
crl.RULE_INFORMATION11
FROM OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl,
OKL_STRM_TYPE_B stty
WHERE stty.id = crl.object1_id1
AND stty.stream_type_purpose = pmnt_strm_purpose
AND crl.rgp_id = crg.id
AND crg.RGD_CODE = rgcode
AND crl.RULE_INFORMATION_CATEGORY = rlcat
AND crg.dnz_chr_id = chrId;
SELECT crg.cle_id,
crl.id,
crl.object1_id1,
crl.RULE_INFORMATION1,
crl.RULE_INFORMATION2,
crl.RULE_INFORMATION3,
crl.RULE_INFORMATION4,
crl.RULE_INFORMATION5,
crl.RULE_INFORMATION6,
crl.RULE_INFORMATION10,
crl.RULE_INFORMATION11
FROM OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl,
OKL_STRM_TYPE_B stty
WHERE stty.id = crl.object1_id1
AND stty.stream_type_purpose = pmnt_strm_purpose
AND crl.rgp_id = crg.id
AND crg.RGD_CODE = rgcode
AND crl.RULE_INFORMATION_CATEGORY = rlcat
AND crg.dnz_chr_id = chrId;
SELECT
crl2.object1_id1,
crl2.object1_id2,
crl2.rule_information2,
NVL(crl2.rule_information3,0) rule_information3,
crl2.rule_information4,
crl2.rule_information5,
crl2.rule_information6,
crl2.rule_information7,
crl2.rule_information8,
crl2.rule_information10
FROM OKC_RULES_B crl1, OKC_RULES_B crl2
WHERE crl1.id = crl2.object2_id1
AND crl1.id = p_id
AND crl2.RULE_INFORMATION_CATEGORY = 'LASLL'
AND crl1.RULE_INFORMATION_CATEGORY = 'LASLH'
AND crl1.dnz_chr_id = chrId
AND crl2.dnz_chr_id = chrId
ORDER BY crl2.rule_information2 ASC;
SELECT
crl2.object1_id1,
crl2.object1_id2,
crl2.rule_information2,
NVL(crl2.rule_information3,0) rule_information3,
crl2.rule_information4,
crl2.rule_information5,
crl2.rule_information6,
crl2.rule_information7,
crl2.rule_information8,
crl2.rule_information10
FROM OKC_RULES_B crl1, OKC_RULES_B crl2
WHERE crl1.id = crl2.object2_id1
AND crl1.id = p_id
AND crl2.RULE_INFORMATION_CATEGORY = 'LASLL'
AND crl1.RULE_INFORMATION_CATEGORY = 'LASLH'
AND crl1.dnz_chr_id = chrId
AND crl2.dnz_chr_id = chrId
ORDER BY crl2.rule_information2 ASC;
SELECT term.printing_lead_days
FROM okc_k_headers_b khr
,hz_customer_profiles cp
,ra_terms_b term
WHERE khr.id = p_id
AND khr.bill_to_site_use_id = cp.site_use_id
AND cp.standard_terms = term.term_id;
SELECT TO_NUMBER(RULE_INFORMATION3)
FROM OKC_RULES_B RULE,
OKC_RULE_GROUPS_B RGP
WHERE RGP.ID = RULE.RGP_ID
AND RGP.DNZ_CHR_ID = p_id
AND RGD_CODE = 'LABILL'
AND RULE_INFORMATION_CATEGORY = 'LAINVD';
SELECT a.capital_amount capital_amount,
b.name asset_number,
capital_reduction,
NVL(capitalize_down_payment_yn,'N') capitalize_down_payment_yn,
capital_reduction_percent,
oec,
e.ste_code ste_code -- 5264170
FROM OKL_K_LINES a,
OKC_K_LINES_TL b,
OKC_LINE_STYLES_V C,
OKC_K_LINES_B d,
OKC_STATUSES_B e
WHERE a.ID = p_id
AND a.ID = b.ID
AND b.LANGUAGE = USERENV('LANG')
AND c.lty_code ='FREE_FORM1'
AND d.ID = p_ID
AND d.lse_id = c.id
AND d.sts_code = e.code;
SELECT crg.cle_id,
crl.id,
crl.object1_id1,
crl.RULE_INFORMATION1,
crl.RULE_INFORMATION2,
crl.RULE_INFORMATION3,
crl.RULE_INFORMATION4,
crl.RULE_INFORMATION5,
crl.RULE_INFORMATION6,
crl.RULE_INFORMATION10,
crl.RULE_INFORMATION11
FROM OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl,
OKL_STRM_TYPE_B stty
WHERE stty.id = crl.object1_id1
--and stty.code = pmnt_strm_name
AND stty.stream_type_purpose = pmnt_strm_purpose
AND crl.rgp_id = crg.id
AND crg.RGD_CODE = rgcode
AND crl.RULE_INFORMATION_CATEGORY = rlcat
AND crg.dnz_chr_id = chrId;
SELECT
crl2.object1_id1,
crl2.object1_id2,
crl2.rule_information2,
NVL(crl2.rule_information3,0) rule_information3,
crl2.rule_information4,
crl2.rule_information5,
crl2.rule_information6,
crl2.rule_information7,
crl2.rule_information8,
crl2.rule_information10
FROM OKC_RULES_B crl1, OKC_RULES_B crl2
WHERE crl1.id = crl2.object2_id1
AND crl1.id = p_id
AND crl2.RULE_INFORMATION_CATEGORY = 'LASLL'
AND crl1.RULE_INFORMATION_CATEGORY = 'LASLH'
AND crl1.dnz_chr_id = chrId
AND crl2.dnz_chr_id = chrId
ORDER BY crl2.rule_information2 ASC;
SELECT CHR.SCS_CODE,
CHR.START_DATE,
CHR.END_DATE,
CHR.DATE_SIGNED,
CHR.CURRENCY_CODE,
CHR.TEMPLATE_YN,
CHR.contract_number,
khr.accepted_date,
khr.syndicatable_yn,
khr.DEAL_TYPE,
khr.term_duration term,
NVL(pdt.reporting_pdt_id, -1) report_pdt_id
FROM OKC_K_HEADERS_B CHR,
OKL_K_HEADERS khr,
OKL_PRODUCTS_V pdt
WHERE CHR.id = chrid
AND CHR.id = khr.id
--AND khr.pdt_id = pdt.id(+);
SELECT 'Y'
FROM okl_trx_ap_invoices_b hdr
,okl_txl_ap_inv_lns_all_b tpl
WHERE hdr.funding_type_code = 'PREFUNDING'
AND hdr.trx_status_code NOT IN ('APPROVED', 'PROCESSED', 'ERROR')
AND hdr.id = tpl.tap_id
AND tpl.khr_id = chrId;
SELECT '1'
FROM okl_party_payment_hdr
WHERE dnz_chr_id = p_chr_id
AND cle_id IS NULL;
SELECT object1_id1, po.vendor_name
FROM okc_k_party_roles_b, po_vendors po
WHERE chr_id = p_chr_id
AND rle_code = 'OKL_VENDOR'
AND po.vendor_id = object1_id1;
SELECT '1'
FROM okl_party_payment_dtls ppy, okl_party_payment_hdr pph
WHERE ppy.vendor_id = p_vendor_id
AND ppy.payment_hdr_id = pph.id
AND pph.dnz_chr_id = p_chr_id
AND pph.cle_id IS NULL;
SELECT cle.id,cle.name,lse.lty_code
FROM okl_k_lines_full_v cle, okc_line_styles_v lse
WHERE cle.dnz_chr_id = p_chr_id
AND cle.lse_id = lse.id
AND lse.lty_code IN ('FREE_FORM1','FEE','SOLD_SERVICE');
SELECT SUM(ppy.disbursement_fixed_amount)
FROM okl_party_payment_dtls ppy, okl_party_payment_hdr pph
WHERE pph.dnz_chr_id = p_chr_id
AND NVL(pph.cle_id, -9999) = p_cle_id
AND pph.id = ppy.payment_hdr_id
AND pph.passthru_term = p_term
GROUP BY ppy.payment_hdr_id;
SELECT SUM(ppy.disbursement_fixed_amount), pph.passthru_term, pph.payout_basis
FROM okl_party_payment_dtls ppy, okl_party_payment_hdr pph
WHERE pph.dnz_chr_id = p_chr_id
AND pph.cle_id IS NULL
AND pph.id = ppy.payment_hdr_id
GROUP BY ppy.payment_hdr_id,pph.passthru_term, pph.payout_basis;
SELECT pph.passthru_term, pph.payout_basis
FROM okl_party_payment_hdr pph
WHERE pph.dnz_chr_id = p_chr_id
AND pph.cle_id = p_cle_id;
SELECT Fnd_Date.canonical_to_date(sll.rule_information2) start_date,
DECODE(sll.rule_information7, NULL,
(ADD_MONTHS(Fnd_Date.canonical_to_date(sll.rule_information2),
NVL(TO_NUMBER(sll.rule_information3),1) *
DECODE(sll.object1_id1, 'M',1,'Q',3,'S',6,'A',12)) - 1),
Fnd_Date.canonical_to_date(sll.rule_information2) +
TO_NUMBER(sll.rule_information7) - 1) end_date,
TO_NUMBER(sll.rule_information6) amount
FROM okc_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp
WHERE rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id = p_cle_id
AND rgp.rgd_code = 'LALEVL'
AND slh.rgp_id = rgp.id
AND slh.rule_information_category = 'LASLH'
AND sll.object2_id1 = slh.id
AND sll.rule_information_category = 'LASLL'
AND sll.rgp_id = rgp.id
AND NVL(sll.rule_information6,0) <> 0
ORDER BY start_date, end_date;
SELECT ppy.disbursement_basis, po.vendor_name
FROM okl_party_payment_dtls ppy, okl_party_payment_hdr pph, okc_k_party_roles_b rle, po_vendors po
WHERE pph.dnz_chr_id = p_chr_id
AND pph.cle_id = p_cle_id
AND pph.id = ppy.payment_hdr_id
AND ppy.cpl_id = rle.id
AND rle.rle_code = 'OKL_VENDOR'
AND rle.object1_id1 = po.vendor_id;
SELECT SUM(ppy.processing_fee_fixed_amount)
FROM okl_party_payment_dtls ppy, okl_party_payment_hdr pph
WHERE pph.dnz_chr_id = p_chr_id
AND pph.cle_id = p_cle_id
AND pph.id = ppy.payment_hdr_id;
SELECT SUM(ppy.disbursement_fixed_amount)
FROM okl_party_payment_dtls ppy, okl_party_payment_hdr pph
WHERE pph.dnz_chr_id = p_chr_id
AND pph.cle_id = p_cle_id
AND pph.id = ppy.payment_hdr_id;
SELECT 'Y'
FROM okc_k_headers_b
WHERE orig_system_source_code = 'OKL_REBOOK'
AND id = p_chr_id;
SELECT line.id,
DECODE(style.lty_code,'SOLD_SERVICE','SERVICE',style.lty_code) line_type,
NVL(line.name,line.item_description) name,
line.fee_type,
style.name line_style,
line.amount amount,
line.start_date,
line.end_date,
pph.id payment_hdr_id,
pph.passthru_start_date,
pph.passthru_term,
pph.passthru_stream_type_id
FROM okl_k_lines_full_v line,
okc_line_styles_v style,
okc_statuses_v sts,
okl_party_payment_hdr pph
WHERE line.lse_id = style.id
AND line.sts_code = sts.code
AND sts.ste_code NOT IN ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED')
AND style.lty_code IN ('SOLD_SERVICE','FEE','FREE_FORM1')
AND line.dnz_chr_id = p_chr_id
AND pph.cle_id = line.id
AND pph.dnz_chr_id = p_chr_id;
SELECT disbursement_basis,
disbursement_fixed_amount,
disbursement_percent,
payment_start_date,
vendor_id
FROM okl_party_payment_dtls
WHERE payment_hdr_id = p_payment_hdr_id;
SELECT vendor_name
FROM po_vendors
WHERE vendor_id = p_vendor_id;
SELECT line.id,
decode(style.lty_code,'SOLD_SERVICE','SERVICE',style.lty_code) line_type,
nvl(line.name,line.item_description) name,
line.fee_type,
rule.object1_id1,
style.name line_style
FROM okl_k_lines_full_v line,
okc_line_styles_v style,
okc_statuses_v sts,
okc_rule_groups_b rgp,
okc_rules_b rule
WHERE rgp.id = rule.rgp_id
AND line.id = rgp.cle_id
AND line.lse_id = style.id
AND line.sts_code = sts.code
AND sts.ste_code NOT IN ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED')
AND style.lty_code IN ('SOLD_SERVICE','FEE')
AND rgp.rgd_code = 'LAPSTH'
AND rule.rule_information_category = 'LASTRM'
AND line.dnz_chr_id = p_chr_id
AND rgp.dnz_chr_id = p_chr_id;*/
SELECT COUNT(*) cnt
FROM okc_k_headers_v WHERE contract_number = n;
SELECT kle.id,
kle.name
FROM OKC_K_LINES_V kle,
OKC_LINE_STYLES_B ls,
OKC_STATUSES_B sts
WHERE kle.lse_id = ls.id
AND ls.lty_code = ltycode
AND kle.dnz_chr_id = chrid
AND sts.code = kle.sts_code
AND sts.ste_code NOT IN ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
SELECT crl.RULE_INFORMATION1,
crl.RULE_INFORMATION3
FROM OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl
WHERE crl.rgp_id = crg.id
AND crg.RGD_CODE = rgcode
AND crl.RULE_INFORMATION_CATEGORY = rlcat
AND crg.dnz_chr_id = chrId
AND crg.cle_id = cleId;
SELECT crl.id,
crl.object1_id1
FROM OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl,
OKL_STRM_TYPE_B stty
WHERE stty.id = TO_NUMBER(crl.object1_id1)
AND stty.stream_type_purpose = pmnt_strm_purpose
AND crl.rgp_id = crg.id
AND crg.RGD_CODE = rgcode
AND crl.RULE_INFORMATION_CATEGORY = rlcat
AND crg.cle_id = cleId
AND crg.dnz_chr_id = chrId;
SELECT 'Y'
FROM okc_rule_groups_b rgp,
okc_rules_b rule,
okl_strm_type_b stty
WHERE rgp.id = rule.rgp_id
AND stty.id = TO_NUMBER(rule.object1_id1)
AND rgp.rgd_code = 'LALEVL'
AND rule.rule_information_category = 'LASLH'
AND rgp.cle_id IS NULL
AND stty.stream_type_purpose = 'ESTIMATED_PROPERTY_TAX'
AND rgp.dnz_chr_id = p_chr_id;
SELECT strm.stream_type_purpose
--SELECT strm.name
FROM okl_strm_type_v strm,
okc_rules_b rule
WHERE rule.id = p_slh_id
AND rule.rule_information_category = 'LASLH'
AND rule.object1_id1 = strm.id;
SELECT
'Y' only_stub,
rule.object2_id1
FROM okc_rules_b rule,
okc_rule_groups_b rgp
WHERE rgp.id = rule.rgp_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.chr_id = p_chr_id
AND rgp.dnz_chr_id = rgp.chr_id
AND rgp.cle_id IS NULL
AND rule.rule_information7 IS NOT NULL
AND NOT EXISTS (
SELECT 'Y'
FROM okc_rules_b rule2
WHERE rule2.rule_information3 IS NOT NULL
AND rule2.rgp_id = rgp.id
AND rule2.object2_id1 = rule.object2_id1
);
SELECT style.lty_code,
style.name,
rule.object2_id1,
'Y' stub_only
FROM okc_rules_b rule,
okc_rule_groups_b rgp,
okc_k_lines_b line,
okc_line_styles_v style,
okc_statuses_b sts
WHERE rgp.id = rule.rgp_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id = line.id
AND line.lse_id = style.id
AND line.sts_code = sts.code
AND sts.ste_code NOT IN ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED')
AND rule.rule_information7 IS NOT NULL
AND NOT EXISTS (
SELECT 'Y'
FROM okc_rules_b rule2
WHERE rule2.rule_information3 IS NOT NULL
AND rule2.rgp_id = rgp.id
AND rule2.object2_id1 = rule.object2_id1
);
SELECT top.name,
top.id top_id,
item.id item_id,
kitem.object1_id1,
kitem.number_of_items,
mtl.description,
mtl.serial_number_control_code
FROM okc_k_lines_v top,
okc_k_lines_b item,
okc_line_styles_b item_style,
okc_line_styles_b top_style,
okc_k_items kitem,
mtl_system_items mtl
WHERE top.dnz_chr_id = p_chr_id
AND top.lse_id = top_style.id
AND top_style.lty_code = 'FREE_FORM1'
AND top.id = item.cle_id
AND item.id = kitem.cle_id
AND item.dnz_chr_id = top.dnz_chr_id
AND item.dnz_chr_id = kitem.dnz_chr_id
AND kitem.jtot_object1_code = 'OKX_SYSITEM'
AND mtl.inventory_item_id = kitem.object1_id1
AND TO_CHAR(mtl.organization_id) = kitem.object1_id2
AND item.lse_id = item_style.id
AND item_style.lty_code = 'ITEM';
SELECT COUNT(inst.serial_number) no_srl
FROM okc_k_lines_b ib,
okc_k_lines_b f2,
okc_line_styles_b style,
okl_txl_itm_insts inst
WHERE f2.cle_id = p_top_cle_id
AND f2.lse_id = style.id
AND f2.dnz_chr_id = p_chr_id
AND ib.dnz_chr_id = p_chr_id
AND style.lty_code = 'FREE_FORM2'
AND f2.id = ib.cle_id
AND ib.id = inst.kle_id;
SELECT cle.id,
cle.cle_id
FROM okc_k_lines_b cle,
okc_statuses_b sts
WHERE cle.lse_id = G_IB_LINE_LTY_ID
AND cle.dnz_chr_id = p_chr_id
AND cle.sts_code = sts.code
AND sts.ste_code NOT IN ('HOLD','EXPIRED','TERMINATED','CANCELLED');
SELECT
cim.id,
cim.object1_id1,
cim.object1_id2,
cim.jtot_object1_code,
cim.number_of_items
FROM okc_k_items_v cim
WHERE cle_id = p_cle_id;
SELECT id,
kle_id,
tal_type,
instance_number_ib,
object_id1_new, -- party_site_use_id
object_id2_new,
jtot_object_code_new,
inventory_org_id,
serial_number,
mfg_serial_number_yn,
inventory_item_id,
inv_master_org_id
FROM okl_txl_itm_insts iti
WHERE iti.kle_id = p_kle_id
AND iti.tal_type = G_TRX_LINE_TYPE_BOOK
AND EXISTS (SELECT '1' FROM okl_trx_assets
WHERE okl_trx_assets.tas_type = G_TRX_LINE_TYPE_BOOK
AND okl_trx_assets.tsu_code = G_TSU_CODE_ENTERED
AND okl_trx_assets.id = iti.tas_id);
SELECT 'X'
FROM mtl_system_items mtl
WHERE mtl.inventory_item_id = p_inv_item_id
AND mtl.organization_id = p_inventory_org_id
AND mtl.serial_number_control_code in (2,5,6);
SELECT instance_id,
install_location_id,
install_location_type_code
FROM csi_item_instances inst,
csi_instance_statuses stat
WHERE inst.serial_number like p_serial_number -- nullable col
AND inst.inventory_item_id = p_inventory_item_id
AND inst.inv_master_organization_id = p_inv_master_organization_id
AND NVL (inst.active_end_date, (p_contract_start_date + 1)) > p_contract_start_date
AND stat.instance_status_id = inst.instance_status_id
AND NVL(stat.terminated_flag, 'N') <> 'Y';
SELECT party_site_id
FROM hz_party_site_uses
WHERE party_site_use_id = p_party_site_use_id;
SELECT location_id
FROM hz_party_sites
WHERE PARTY_SITE_ID = p_party_site_id;
SELECT name
FROM okc_k_lines_b cle1,
okc_k_lines_tl cle_tl,
okc_k_lines_b cle2
WHERE cle_tl.id = cle2.id
AND cle1.id = p_cle_id
AND cle2.id = cle1.cle_id
AND cle_tl.language = USERENV('LANG');
SELECT
substr(arp_addr_label_pkg.format_address(null,l.address1,l.address2,l.address3,l.address4,l.city,l.county,
l.state,l.province,l.postal_code,null,l.country,null,null,null,null,null,null,null,'n','n',80,1,1),1,80)
FROM hz_locations l
WHERE l.location_id = p_location_id;
SELECT
substr(arp_addr_label_pkg.format_address(null,l.address1,l.address2,l.address3,l.address4,l.city,l.county,
l.state,l.province,l.postal_code,null,l.country,null,null,null,null,null,null,null,'n','n',80,1,1),1,80)
FROM hz_locations l,
hz_party_sites site
WHERE site.party_site_id = p_party_site_id
AND l.location_id = site.location_id;
SELECT h.start_date, mtl.master_organization_id
FROM okc_k_headers_b h,
mtl_parameters mtl
WHERE h.id = p_chr_id
AND mtl.organization_id = h.inv_organization_id;
SELECT NVL(okc.orig_system_source_code,'X') orig_system_source_code,
NVL(okl.deal_type,'X') deal_type
--FROM okl_k_headers_full_v
FROM okc_k_headers_b okc,
okl_k_headers okl
WHERE okc.id = p_chr_id
AND okc.id = okl.id;
SELECT 'Y'
FROM okl_lessee_as_vendors_uv
WHERE dnz_chr_id = p_chr_id;
*SELECT fnd_profile.VALUE('OKL_STREAMS_GEN_PATH')
*FROM dual;
SELECT cle.id,
DECODE (lse.lty_code, 'FREE_FORM1', 'ASSET', 'FEE', 'FEE',
'SOLD_SERVICE','SERVICE') line_type
FROM okc_k_lines_b cle,
okc_line_styles_b lse,
okc_statuses_b sts
WHERE cle.chr_id = p_khr_id
AND cle.lse_id = lse.id
AND cle.sts_code = sts.code
AND sts.ste_code NOT IN ('HOLD','EXPIRED','TERMINATED','CANCELLED')
AND lse.lty_code IN ('FREE_FORM1', 'FEE', 'SOLD_SERVICE');
SELECT sll.rule_information2 start_date,
SLL.rule_information3 periods,
DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) mpp,
( select STYT.NAME from OKL_STRM_TYPE_B STY ,OKL_STRM_TYPE_TL STYT where STY.ID = STYT.ID AND STYT.LANGUAGE = USERENV ( 'LANG' )
AND to_number(SLH.OBJECT1_ID1) = STY.ID ) STREAM_TYPE
--styt.name stream_type
FROM okc_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp
--okl_strm_type_b sty,
--okl_strm_type_tl styt
WHERE rgp.dnz_chr_id = p_khr_id
AND rgp.cle_id = p_kle_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH'
AND rgp.id = sll.rgp_id
--AND slh.object1_id1 = TO_CHAR(sty.id) 4929573
--AND styt.LANGUAGE = USERENV('LANG')
--AND sty.id = styt.id
AND TO_CHAR(slh.id) = sll.object2_id1
AND sll.rule_information_category = 'LASLL';
SELECT
SUM(NVL(TO_NUMBER(SLL.rule_information3),0)) periods,
DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) mpp,
styt.name stream_type
FROM okc_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp,
okl_strm_type_b sty,
okl_strm_type_tl styt
WHERE rgp.dnz_chr_id = p_khr_id
AND rgp.cle_id = p_kle_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH'
AND slh.object1_id1 = TO_CHAR(sty.id)
AND styt.language = USERENV('LANG')
AND sty.id = styt.id
AND TO_CHAR(slh.id) = sll.object2_id1
AND sll.rule_information_category = 'LASLL'
GROUP BY DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12),
styt.name;
SELECT (select STYT.NAME from OKL_STRM_TYPE_B STY ,OKL_STRM_TYPE_TL STYT
where STY.ID = STYT.ID AND STYT.LANGUAGE = USERENV ( 'LANG' )
AND to_number(RULE.OBJECT1_ID1) = STY.ID ) STREAM_TYPE,
rule.id rule_id,
rgp.id rgp_id
FROM okc_rules_b rule,
okc_rule_groups_b rgp
--okl_strm_type_b sty, Bug 4929573
--okl_strm_type_tl styt
WHERE rgp.cle_id = p_kle_id
AND rgp.dnz_chr_id = p_khr_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = rule.rgp_id
AND rule.rule_information_category = 'LASLH' ;
SELECT sll.rule_information2 start_date,
sll.rule_information3 periods,
sll.rule_information7 stub_day,
DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) mpp
FROM okc_rules_b sll
WHERE sll.rgp_id = p_rgp_id
AND sll.object2_id1 = TO_CHAR(p_rule_id)
AND sll.rule_information_category = 'LASLL'
-- cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
ORDER BY sll.rule_information2 ;
SELECT start_date,
end_date
FROM OKC_K_HEADERS_B
WHERE ID = p_khr_id;
SELECT COUNT(sll.id)
FROM okc_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp,
okl_strm_type_b sty,
okl_strm_type_tl styt
WHERE rgp.dnz_chr_id = p_khr_id
AND rgp.cle_id = p_kle_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH' --| 17-Jan-06 cklee Fixed bug#4956483 |
AND slh.object1_id1 = TO_CHAR(sty.id)
AND styt.LANGUAGE = USERENV('LANG')
AND sty.id = styt.id
AND TO_CHAR(slh.id) = sll.object2_id1
AND sll.rule_information_category = 'LASLL'; --| 17-Jan-06 cklee Fixed bug#4956483 |
SELECT COUNT(sll.id)
FROM okc_rules_b sll
WHERE sll.rgp_id = p_rgp_id
AND sll.object2_id1 = TO_CHAR(p_rule_id)
AND sll.rule_information_category = 'LASLL'; --| 17-Jan-06 cklee Fixed bug#4956483 |
Select count(*) cnt
From okc_k_headers_v where contract_number = n;
SELECT cleb.id
FROM okc_k_lines_b cleb
WHERE cleb.dnz_chr_id = chrId
AND cleb.chr_id = chrId
AND CLEB.ID =kleId
and exists
( SELECT '1'
FROM okc_k_lines_b cleb1,
okc_statuses_b okcsts1
WHERE cleb1.dnz_chr_id = cleb.chr_id
AND cleb1.cle_id = cleb.id
AND okcsts1.code = cleb1.sts_code
AND okcsts1.ste_code NOT IN ('EXPIRED','HOLD','CANCELLED','TERMINATED'));
SELECT l.id,
l.line_number,
l.start_date,
l.end_date,
l.name,
l.amount,
l.capital_amount,
l.fee_type
FROM okl_k_lines_full_v l,
okc_line_styles_v sty,
okc_statuses_v sts
WHERE l.lse_id = sty.id
AND l.sts_code = sts.code
AND sts.ste_code NOT IN ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED')
AND sty.lty_code = 'FEE'
AND l.dnz_chr_id = p_chrId;
SELECT meaning
FROM fnd_lookups
WHERE lookup_code = p_fee_type_code
AND lookup_type = 'OKL_FEE_TYPES';
SELECT 1
FROM okl_party_payment_hdr pph
WHERE cle_id = p_cle_id
AND dnz_chr_id = p_chr_id
AND passthru_term = 'BASE';
SELECT kle.id, kle.qte_id, kle.amount, cleb.name
FROM okc_k_lines_v cleb,
okl_k_lines kle
WHERE cleb.dnz_chr_id = chrID
AND kle.ID = cleb.ID
AND kle.fee_type = 'ROLLOVER'
-- AND cleb.sts_code NOT IN ('HOLD','EXPIRED','TERMINATED','CANCELLED', 'ABANDONED');
SELECT 'Y'
FROM okc_statuses_v okcsts
WHERE okcsts.code = cleb.sts_code
AND okcsts.ste_code IN ('EXPIRED','HOLD','CANCELLED','TERMINATED', 'ABANDONED'));
SELECT kle.qte_id
FROM okc_k_lines_b cleb,
okl_k_lines kle
WHERE cleb.dnz_chr_id = chrID
AND kle.ID = cleb.ID
AND kle.fee_type = 'ROLLOVER'
-- AND cleb.sts_code NOT IN ('HOLD','EXPIRED','TERMINATED','CANCELLED', 'ABANDONED');
SELECT 'Y'
FROM okc_statuses_v okcsts
WHERE okcsts.code = cleb.sts_code
AND okcsts.ste_code IN ('EXPIRED','HOLD','CANCELLED','TERMINATED', 'ABANDONED'));
SELECT kle.id, kle.amount, cleb.end_date
FROM okc_k_lines_b cleb,
okl_k_lines kle
WHERE cleb.dnz_chr_id = chrID
AND kle.ID = cleb.ID
AND cleb.CLE_ID = feeTopLine
-- AND cleb.sts_code NOT IN ('HOLD','EXPIRED','TERMINATED','CANCELLED', 'ABANDONED');
SELECT 'Y'
FROM okc_statuses_v okcsts
WHERE okcsts.code = cleb.sts_code
--Bug# 4959361: Include Terminated lines when fetching sub-line amount
--AND okcsts.ste_code IN ('EXPIRED','HOLD','CANCELLED','TERMINATED', 'ABANDONED'));
SELECT quote_number
FROM okl_trx_quotes_v
WHERE id = qteID;
SELECT sll.rule_information2 start_date,
SLL.rule_information3 periods,
DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) mpp,
TRUNC(ADD_MONTHS(Fnd_Date.canonical_to_date(sll.rule_information2),
TO_NUMBER(SLL.rule_information3)*DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12)) - 1) end_date,
styt.name stream_type
FROM okc_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp,
okl_strm_type_b sty,
okl_strm_type_tl styt
WHERE rgp.dnz_chr_id = chrID
AND rgp.cle_id = feeLine
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH'
AND slh.object1_id1 = TO_CHAR(sty.id)
AND styt.LANGUAGE = USERENV('LANG')
AND sty.id = styt.id
AND TO_CHAR(slh.id) = sll.object2_id1
AND sll.rule_information_category = 'LASLL';
SELECT slh.id
FROM okc_rules_b slh,
okc_rule_groups_b rgp,
okl_strm_type_b sty,
okl_strm_type_tl styt
WHERE rgp.dnz_chr_id = chrID
AND rgp.cle_id = feeLine
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH'
AND slh.object1_id1 = TO_CHAR(sty.id)
AND styt.LANGUAGE = USERENV('LANG')
AND sty.id = styt.id;
SELECT kle.id, kle.amount, cleb.name
FROM okc_k_lines_v cleb,
okl_k_lines kle,
okc_statuses_b okcsts
WHERE cleb.dnz_chr_id = chrID
AND cleb.chr_id = chrID
AND kle.ID = cleb.ID
AND kle.fee_type = 'FINANCED'
AND okcsts.code = cleb.sts_code
AND okcsts.ste_code NOT IN ('EXPIRED','HOLD','CANCELLED','TERMINATED', 'ABANDONED')
AND NVL(kle.fee_purpose_code,'XXX') <> 'SALESTAX';
SELECT kle.id, kle.amount, cleb.end_date
,cleb.start_date -- added for bug 5115701
FROM okc_k_lines_b cleb,
okl_k_lines kle,
okc_statuses_b okcsts
WHERE cleb.dnz_chr_id = chrID
AND kle.ID = cleb.ID
AND cleb.CLE_ID = feeTopLine
AND okcsts.code = cleb.sts_code
--Bug# 4959361: Include Terminated lines when fetching sub-line amount
AND okcsts.ste_code NOT IN ('EXPIRED','HOLD','CANCELLED','ABANDONED');
SELECT Fnd_Date.canonical_to_date(sll.rule_information2) start_date, -- formated for bug 5115701,
SLL.rule_information3 periods,
DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) mpp,
TRUNC(ADD_MONTHS(Fnd_Date.canonical_to_date(sll.rule_information2),
TO_NUMBER(SLL.rule_information3)*DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12)) - 1) end_date,
styt.name stream_type
FROM okc_rules_b sll,
okc_rules_b slh,
okc_rule_groups_b rgp,
okl_strm_type_b sty,
okl_strm_type_tl styt
WHERE rgp.dnz_chr_id = chrID
AND rgp.cle_id = feeLine
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH'
AND slh.object1_id1 = TO_CHAR(sty.id)
AND styt.LANGUAGE = USERENV('LANG')
AND sty.id = styt.id
AND TO_CHAR(slh.id) = sll.object2_id1
AND sll.rule_information_category = 'LASLL';
SELECT slh.id
FROM okc_rules_b slh,
okc_rule_groups_b rgp,
okl_strm_type_b sty,
okl_strm_type_tl styt
WHERE rgp.dnz_chr_id = chrID
AND rgp.cle_id = feeLine
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = slh.rgp_id
AND slh.rule_information_category = 'LASLH'
AND slh.object1_id1 = TO_CHAR(sty.id)
AND styt.LANGUAGE = USERENV('LANG')
AND sty.id = styt.id;
SELECT 1
FROM okc_k_lines_b cleb,
okl_k_lines kle,
okc_k_headers_b khr
WHERE khr.id = chrID
AND cleb.dnz_chr_id = khr.id
AND kle.ID = cleb.ID
AND kle.fee_type = 'ROLLOVER'
AND TRUNC(khr.start_date) > SYSDATE
AND NOT EXISTS (
SELECT 'Y'
FROM okc_statuses_b okcsts
WHERE okcsts.code = cleb.sts_code
AND okcsts.ste_code IN ('EXPIRED','HOLD','CANCELLED','TERMINATED', 'ABANDONED'));
SELECT ls.lty_code,
ls.name line_type,
kle.id,
kle.name
FROM OKL_K_LINES_FULL_V kle,
OKC_LINE_STYLES_V ls,
OKC_STATUSES_B sts
WHERE kle.lse_id = ls.id
AND ls.lty_code IN ('FREE_FORM1', 'FEE', 'SOLD_SERVICE')
AND kle.dnz_chr_id = chrid
AND sts.code = kle.sts_code
AND sts.ste_code NOT IN ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
SELECT itm.object1_id1
FROM okc_k_items itm
WHERE cle_id = lineid
AND dnz_chr_id = chrid
AND jtot_object1_code = 'OKL_STRMTYP';
SELECT rule.object1_id1
FROM okc_rule_groups_v rgp,
okc_rules_v rule
WHERE rgp.id = rule.rgp_id
AND rgp.cle_id IS NULL
AND rgp.dnz_chr_id = chrid
AND rgp.rgd_code = 'LALEVL'
AND rule.rule_information_category = 'LASLH';
SELECT
pdt.name
,pdt.PRODUCT_STATUS_CODE
FROM okl_products_v pdt
,okl_k_headers_v khr
,okc_k_headers_b CHR
WHERE 1=1
AND khr.id = p_chr_id
AND pdt_id = pdt.id
AND khr.id = CHR.id;
select 'Y'
from okl_k_headers
where id = p_chr_id
and deal_type = 'LOAN';
SELECT rgp.id,rgp.cle_id
FROM OKC_RULE_GROUPS_B rgp,
OKC_RULES_B crl2,
okc_k_lines_b cleb,
OKL_STRM_TYPE_B stty
WHERE stty.id = crl2.object1_id1
AND stty.stream_type_purpose = 'RENT'
AND rgp.id = crl2.rgp_id
AND crl2.RULE_INFORMATION_CATEGORY = 'LASLH'
AND rgp.rgd_code = 'LALEVL'
and rgp.dnz_chr_id = cleb.dnz_chr_id
AND cleb.dnz_chr_id = p_chr_id
AND cleb.id = rgp.cle_id
AND rgp.cle_id is not null
AND cleb.lse_id = 33
AND NOT EXISTS (
SELECT 'Y'
FROM okc_statuses_v okcsts
WHERE okcsts.code = cleb.sts_code
AND okcsts.ste_code IN ('EXPIRED','HOLD','CANCELLED','TERMINATED', 'ABANDONED'));
SELECT nvl(capital_amount,0) capital_amount, kle.name name
FROM okl_k_lines_full_v kle
WHERE kle.id = p_id;
select nvl(sum(tot_amt),0)
from(
SELECT to_number((NVL(crl2.rule_information3,0) * NVL(crl2.rule_information6,0))) tot_amt
FROM OKC_RULES_B crl1,
OKC_RULES_B crl2
WHERE crl1.id = crl2.object2_id1
AND crl2.RULE_INFORMATION_CATEGORY = 'LASLL'
AND crl1.RULE_INFORMATION_CATEGORY = 'LASLH'
AND crl1.dnz_chr_id = p_chr_id
AND crl2.dnz_chr_id = p_chr_id
AND crl1.rgp_id = p_rgp_id
AND crl2.rgp_id = p_rgp_id
union all
SELECT
to_number(nvl(crl2.rule_information8,0)) tot_amt
FROM OKC_RULES_B crl1,
OKC_RULES_B crl2
WHERE crl1.id = crl2.object2_id1
AND crl2.RULE_INFORMATION_CATEGORY = 'LASLL'
AND crl1.RULE_INFORMATION_CATEGORY = 'LASLH'
AND crl1.dnz_chr_id = p_chr_id
AND crl2.dnz_chr_id = p_chr_id
AND crl1.rgp_id = p_rgp_id
AND crl2.rgp_id = p_rgp_id
);
SELECT '!',
orig_system_id1
FROM okc_k_headers_b CHR,
okl_trx_contracts ktrx
WHERE ktrx.khr_id_new = chr.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 chr.id = p_chr_id
AND chr.orig_system_source_code = 'OKL_REBOOK';
SELECT model_cle.id rbk_model_cle_id,
model_cle.orig_system_id1 orig_model_cle_id,
fin_ast_cle.id rbk_fin_ast_cle_id,
fin_ast_cle.orig_system_id1 orig_fin_ast_cle_id,
fin_ast_cle.name asset_number
FROM okc_k_lines_b model_cle,
okc_k_lines_v fin_ast_cle,
okc_statuses_b sts
WHERE fin_ast_cle.chr_id = p_chr_id
AND fin_ast_cle.dnz_chr_id = p_chr_id
AND fin_ast_cle.lse_id = 33 -- Financial Asset Line
AND model_cle.dnz_chr_id = p_chr_id
AND model_cle.cle_id = fin_ast_cle.id
AND model_cle.lse_id = 34 --Model Line
and sts.code = fin_ast_cle.sts_code
and sts.ste_code NOT IN ('HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
SELECT ib_cim.object1_id1 instance_id
FROM okc_k_lines_b ib_cle,
okc_k_lines_b inst_cle,
okc_statuses_b sts,
okc_k_items ib_cim
WHERE inst_cle.dnz_chr_id = p_chr_id
AND inst_cle.cle_id = p_fin_ast_cle_id
AND inst_cle.lse_id = 43 -- FREE_FORM2 Line
AND ib_cle.dnz_chr_id = p_chr_id
AND ib_cle.cle_id = inst_cle.id
AND ib_cle.lse_id = 45 --IB Line
AND ib_cim.cle_id = ib_cle.id
AND ib_cim.dnz_chr_id = p_chr_id
AND sts.code = ib_cle.sts_code
AND sts.ste_code NOT IN ('HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
SELECT cim.object1_id1,
cim.object1_id2,
cim.number_of_items
FROM okc_k_items cim
WHERE cim.cle_id = p_cle_id
AND cim.dnz_chr_id = p_chr_id;
SELECT orig_ib_cim.object1_id1 instance_id
FROM okc_k_items orig_ib_cim,
okc_k_lines_b orig_ib_cle,
okc_k_lines_b orig_inst_cle,
okc_statuses_b inst_sts
WHERE orig_inst_cle.dnz_chr_id = p_orig_chr_id
AND orig_inst_cle.cle_id = p_orig_fin_ast_cle_id
AND orig_inst_cle.lse_id = 43
AND orig_ib_cle.cle_id = orig_inst_cle.id
AND orig_ib_cle.dnz_chr_id = p_orig_chr_id
AND orig_ib_cle.lse_id = 45
AND orig_ib_cim.cle_id = orig_ib_cle.id
AND orig_ib_cim.dnz_chr_id = p_orig_chr_id
AND orig_ib_cim.object1_id1 IS NOT NULL
AND inst_sts.code = orig_ib_cle.sts_code
AND inst_sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
AND NOT EXISTS (
SELECT 1
FROM okc_k_lines_b rbk_inst_cle,
okc_statuses_b rbk_inst_sts
WHERE rbk_inst_cle.orig_system_id1 = orig_inst_cle.id
AND rbk_inst_cle.lse_id = 43
AND rbk_inst_cle.dnz_chr_id = p_rbk_chr_id
AND rbk_inst_cle.cle_id = p_rbk_fin_ast_cle_id
AND rbk_inst_sts.code = rbk_inst_cle.sts_code
AND rbk_inst_sts.ste_code NOT IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED'));
SELECT svc_cle.dnz_chr_id
FROM okc_k_lines_b svc_cle,
okc_k_items svc_item,
okc_statuses_b sts,
okc_k_headers_b svc_chr
WHERE svc_item.object1_id1 = p_object1_id1
AND svc_item.jtot_object1_code = p_jtot_object_code
AND svc_cle.id = svc_item.cle_id
AND svc_cle.dnz_chr_id = svc_item.dnz_chr_id
AND svc_cle.sts_code = sts.code
AND sts.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED','TERMINATED')
AND svc_chr.id = svc_cle.dnz_chr_id
AND svc_chr.scs_code = 'SERVICE';
SELECT svc_cle.dnz_chr_id
FROM okc_k_lines_b svc_cle,
okc_k_items svc_item,
okc_statuses_b sts,
okc_k_headers_b svc_chr,
cs_counter_groups csg,
cs_counters cc
WHERE svc_item.object1_id1 = TO_CHAR(cc.counter_id)
AND svc_item.jtot_object1_code = p_jtot_object_code
AND svc_cle.id = svc_item.cle_id
AND svc_cle.dnz_chr_id = svc_item.dnz_chr_id
AND svc_cle.sts_code = sts.code
AND sts.ste_code NOT IN ('HOLD','EXPIRED','CANCELLED','TERMINATED')
AND svc_chr.id = svc_cle.dnz_chr_id
AND svc_chr.scs_code = 'SERVICE'
AND csg.source_object_id = p_instance_id
AND csg.source_object_code = p_source_object_code
AND csg.counter_group_id = cc.counter_group_id;
SELECT tradein_amount
FROM okl_k_headers_v
WHERE id = p_chr_id;
SELECT cle.id,
cle.name,
kle.tradein_amount,
capital_reduction,
capitalize_down_payment_yn,
-- Bug 6417667 Start
kle.capital_reduction_percent,
kle.oec
-- Bug 6417667 End
--down_payment_yes_no
FROM
okl_k_lines kle,
okc_k_lines_v cle,
okc_line_styles_b sty,
okc_statuses_b sts
WHERE cle.id = kle.id
AND dnz_chr_id = p_chr_id
AND cle.lse_id = sty.id
AND sty.lty_code = 'FREE_FORM1'
AND cle.sts_code = sts.code
AND sts.ste_code NOT IN ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
SELECT crg.cle_id,
crl.id,
crl.object1_id1,
crl.RULE_INFORMATION6
FROM OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl,
OKL_STRM_TYPE_B stty
WHERE stty.id = crl.object1_id1
AND stty.stream_type_purpose = 'DOWN_PAYMENT'
AND crl.rgp_id = crg.id
AND crg.RGD_CODE = 'LALEVL'
AND crl.RULE_INFORMATION_CATEGORY = 'LASLH'
AND crg.cle_id = p_cle_id
AND crg.dnz_chr_id = p_chr_id;
SELECT crl2.object1_id1,
crl2.object1_id2,
crl2.rule_information2,
NVL(crl2.rule_information3,0) rule_information3,
NVL(crl2.rule_information6,0) rule_information6
FROM OKC_RULES_B crl1, OKC_RULES_B crl2, OKC_RULE_GROUPS_B rgp
WHERE crl1.id = crl2.object2_id1
AND crl1.id = p_id
AND rgp.cle_id = p_cle_id
AND rgp.id = crl1.rgp_id
AND crl2.RULE_INFORMATION_CATEGORY = 'LASLL'
AND crl1.RULE_INFORMATION_CATEGORY = 'LASLH'
AND crl1.dnz_chr_id = p_chr_id
AND crl2.dnz_chr_id = p_chr_id;
SELECT 'Y'
--Select count(*) cnt
FROM okc_k_headers_b WHERE contract_number = n;
SELECT 'Y'
FROM fa_book_controls
WHERE book_class = 'TAX'
--and nvl(initial_date,dat) <= dat Bug#3636801
AND NVL(date_ineffective,dat+1) > dat
AND book_type_code = bk;
SELECT object1_id1
FROM okc_k_party_roles_b
WHERE dnz_chr_id = p_chr_id
AND cle_id = p_cle_id
AND jtot_object1_code = 'OKX_VENDOR';
SELECT 'Y'
FROM okx_vendor_sites_v
WHERE id1 = p_site_id
AND vendor_id = p_vendor_id
AND status = 'A'
AND TRUNC(SYSDATE) >= NVL(TRUNC(start_date_active), TRUNC(SYSDATE));
SELECT line.id,
line.line_number,
line.amount
FROM okl_k_lines_full_v line,
okc_line_styles_v style,
okc_statuses_b sts
WHERE line.lse_id = style.id
AND style.lty_code = p_lty_code
AND sts.code = line.sts_code
AND sts.ste_code NOT IN ('HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED')
AND line.dnz_chr_id = p_chr_id;
SELECT SUM(NVL(capital_amount,0))
FROM okl_k_lines_full_v line
WHERE line.cle_id = p_line_id;
SELECT
sgn.value book_type,
COUNT(*) book_count
FROM Okl_txd_assets_v txd,
okl_txl_assets_b txl,
okl_sgn_translations sgn
WHERE txd.tal_id = txl.id
AND txl.kle_id = Kleid
AND sgn.jtot_object1_code = 'FA_BOOK_CONTROLS'
AND sgn.object1_id1 = txd.tax_book
AND sgn.sgn_code = 'STMP' -- Bug# 3533552
GROUP BY sgn.value;
SELECT 'Y'
FROM OKX_AST_CAT_BKS_V
WHERE category_id = p_cat_id
AND book_type_code = p_book_type_code;
SELECT 'Y'
FROM FA_BOOK_CONTROLS fa,
OKL_SYS_ACCT_OPTS sys
WHERE book_class='CORPORATE'
AND fa.set_of_books_id = sys.set_of_books_id
AND book_type_code = p_book_type_code;
SELECT name
FROM okx_asst_catgrs_v
WHERE category_id = p_cat_id;
SELECT 1
FROM okl_party_payment_hdr pph
WHERE cle_id = p_cle_id
AND dnz_chr_id = p_chr_id;
SELECT ppd.vendor_id,
ppd.pay_site_id,
pph.passthru_term
FROM okl_party_payment_hdr pph,
okl_party_payment_dtls ppd
WHERE pph.cle_id = p_cle_id
AND pph.dnz_chr_id = p_chr_id
AND ppd.payment_hdr_id = pph.id;
SELECT vendor_name
FROM po_vendors
WHERE vendor_id = p_vendor_id;
SELECT NVL(kle.name,kle.item_description) name,
kle.id,
ls.name line_style
FROM OKL_K_LINES_FULL_V kle,
OKC_LINE_STYLES_v ls,
OKC_STATUSES_B sts
WHERE kle.lse_id = ls.id
AND ls.lty_code = ltycode
AND kle.dnz_chr_id = chrid
AND sts.code = kle.sts_code
AND sts.ste_code NOT IN ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
SELECT kle.id
FROM OKL_K_LINES_FULL_V kle,
OKC_LINE_STYLES_v ls,
OKC_STATUSES_B sts
WHERE kle.lse_id = ls.id
AND ls.lty_code = ltycode
AND kle.dnz_chr_id = chrid
AND sts.code = kle.sts_code
AND sts.ste_code NOT IN ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED')
and kle.cle_id = cleid;
SELECT A.ASST_ADD_BOOK_TYPE_CODE,
C.secondary_rep_method secondary_rep_method
FROM okl_system_params_all a,
okc_k_headers_all_b b,
okl_sys_acct_opts_all c
WHERE b.id = p_chr_id
AND b.authoring_org_id = a.org_id
AND c.org_id = a.org_id;
SELECT
pdt.reporting_pdt_id
FROM okl_products_v pdt
,okl_k_headers_v khr
,okc_k_headers_b CHR
WHERE 1=1
AND khr.id = p_chr_id
AND pdt_id = pdt.id
AND khr.id = CHR.id;
SELECT cle.id,
name,
kle.residual_value
FROM okc_k_lines_v cle,
okl_k_lines kle,
okc_line_styles_b sty,
okc_statuses_b sts
WHERE cle.lse_id = sty.id
AND cle.dnz_chr_id = p_chr_id
AND cle.id = kle.id
AND lty_code = 'FREE_FORM1'
AND cle.sts_code = sts.code
AND sts.ste_code NOT IN ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
* select kl.dnz_chr_id syndId
* from OKC_K_ITEMS_V ITM,
* OKC_K_LINES_V KL,
* OKL_K_HEADERS_FULL_V KHR,
* OKC_LINE_STYLES_B lse
* where KL.LSE_ID=lse.id
* and lse.lty_code='SHARED'
* AND KL.CHR_ID =KL.DNZ_CHR_ID
* AND KL.DNZ_CHR_ID = ITM.DNZ_CHR_ID
* AND ITM.CLE_ID = KL.ID
* AND to_char(KHR.ID) = ITM.OBJECT1_ID1
* and khr.id = chrId;
SELECT crl.RULE_INFORMATION1, crl.RULE_INFORMATION2
FROM OKC_RULE_GROUPS_B crg, OKC_RULES_B crl
WHERE crl.rgp_id = crg.id
AND crg.RGD_CODE = rgcode
AND crl.RULE_INFORMATION_CATEGORY = rlcat
AND crg.dnz_chr_id = chrId;
SELECT inst.object_id1_new
FROM OKL_TXL_ITM_INSTS inst,
OKL_K_LINES_FULL_V kle,
OKC_LINE_STYLES_B lse
WHERE inst.kle_id = kle.id
AND lse.id = kle.lse_id
AND lse.lty_code = 'INST_ITEM'
AND kle.dnz_chr_id = chrId;
SELECT inv.shipping_address_id1
FROM OKL_SUPP_INVOICE_DTLS inv,
OKL_K_LINES_FULL_V kle,
OKC_LINE_STYLES_B lse
WHERE inv.cle_id = kle.id
AND lse.id = kle.lse_id
AND lse.lty_code = 'ITEM'
AND kle.dnz_chr_id = chrId;
SELECT 'Y' isThere
FROM dual
WHERE EXISTS(
SELECT A.party_site_use_id
FROM HZ_PARTY_SITE_USES A,
HZ_PARTY_SITES B
WHERE b.party_site_id = a.party_site_id
AND a.party_site_use_id = instId
AND a.site_use_type = rleCode
AND b.party_id = ptyId);
SELECT 'Y'
FROM okx_cust_site_uses_v
WHERE id1 = p_bill_to
AND cust_account_id = p_cust_acct
AND site_use_code = 'BILL_TO'
AND b_status = 'A'
AND cust_acct_site_status = 'A';
SELECT 'Y'
FROM OKX_RECEIPT_METHODS_V
WHERE customer_id = p_cust_acct_id
AND site_use_id = p_bill_to_id
AND id1 = p_pymt_method_id;
SELECT 'Y'
FROM okx_rcpt_method_accounts_v okx_custbkac
WHERE SYSDATE < NVL(end_date_active,SYSDATE+1)
AND customer_id = p_cust_acct_id
AND customer_site_use_id = p_bill_to_id
AND id1 = p_bank_acct_id;
SELECT crl.RULE_INFORMATION1, crl.RULE_INFORMATION2
FROM OKC_RULE_GROUPS_B crg, OKC_RULES_B crl
WHERE crl.rgp_id = crg.id
AND crg.RGD_CODE = rgcode
AND crl.RULE_INFORMATION_CATEGORY = rlcat
AND crg.dnz_chr_id = chrid;
SELECT 'Y'
FROM OKX_RECEIPT_METHODS_V
WHERE customer_id = p_cust_acct_id
AND site_use_id = p_bill_to_id
AND id1 = p_pymt_method_id;
SELECT 'Y'
FROM okx_rcpt_method_accounts_v okx_custbkac
WHERE SYSDATE < NVL(end_date_active,SYSDATE+1)
AND customer_id = p_cust_acct_id
AND customer_site_use_id = p_bill_to_id
AND id1 = p_bank_acct_id;
SELECT pdt.id product_id
,pdt.name product_name
,CHR.sts_code contract_status
,khr.deal_type deal_type
FROM okl_products_v pdt
,okl_k_headers_v khr
,okc_k_headers_b CHR
WHERE 1=1
AND khr.id = p_contract_id
AND pdt_id = pdt.id
AND khr.id = CHR.id;
SELECT 1 FROM
OKC_K_HEADERS_B chr,
OKL_K_HEADERS khr,
OKL_K_LINES kle,
OKC_K_LINES_B cle,
OKL_TRX_QUOTES_B qte,
OKC_LINE_STYLES_B cls
WHERE cle.id = kle.id
AND chr.id = p_contract_id
AND chr.id = cle.chr_id
AND chr.id = cle.dnz_chr_id
AND cls.lty_code = 'FEE'
AND cle.lse_id = cls.id
AND kle.qte_id = qte.id
AND chr.id = khr.id
AND khr.legal_entity_id <> qte.legal_entity_id;
SELECT pdt.id product_id
,pdt.name product_name
,CHR.sts_code contract_status
--Bug# 4869443
,chr.start_date start_date
,chr.orig_system_source_code orig_system_source_code
,rul.rule_information1 Release_asset_yn
FROM okl_products_v pdt
,okl_k_headers_v khr
,okc_k_headers_b CHR
,okc_rules_b RUL
WHERE 1=1
AND khr.id = p_chr_id
AND pdt_id = pdt.id
AND khr.id = CHR.id
AND rul.dnz_chr_id = p_chr_id
AND rul.rule_information_category = 'LARLES';
select --trunc(orig_cleb.date_terminated) date_terminated,
trunc(decode(sign(orig_cleb.end_date - orig_cleb.date_terminated),-1,orig_cleb.end_date,orig_cleb.date_terminated)) date_terminated,
orig_clet.name asset_number
from okc_k_lines_b orig_cleb,
okc_k_lines_tl orig_clet,
okc_k_lines_b cleb
where orig_clet.id = orig_cleb.id
and orig_clet.language = userenv('LANG')
and orig_cleb.id = cleb.orig_system_id1
and cleb.chr_id = p_chr_id
and cleb.dnz_chr_id = p_chr_id
and cleb.sts_code <> 'ABANDONED'
and cleb.lse_id = 33; --for financial asset line
select txl.id,
txl.asset_number,
txl.original_cost,
txl.salvage_value,
txl.percent_salvage_value
from okl_txl_assets_b txl
where txl.dnz_khr_id = p_chr_id;
SELECT txd.cost,
txd.deprn_method_tax,
txd.tax_book
FROM okl_txd_assets_b txd,
okl_txl_assets_b txl
WHERE txd.tal_id = txl.id
AND txl.kle_id = kleid;
SELECT start_date
FROM okc_k_headers_b
WHERE id = p_chr_id;
SELECT line.id,
NVL(line.capital_amount,0) capital_amount,
line.start_date,
strm.code strm_type
FROM okl_k_lines_full_v line,
okc_k_items item,
okl_strmtyp_source_v strm,
okc_line_styles_v style,
okc_statuses_b sts
WHERE line.dnz_chr_id = p_chr_id
AND line.lse_id = style.id
AND line.sts_code = sts.code
AND line.id = item.cle_id
AND item.object1_id1 = strm.id1
AND item.jtot_object1_code = 'OKL_STRMTYP'
AND NVL(strm.capitalize_yn,'N') = 'Y'
AND sts.ste_code NOT IN ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED')
AND style.lty_code = 'FEE'
-- Bug 6497111 Start Udhenuko Added.
AND NVL(line.fee_purpose_code,'XXX') <> 'SALESTAX';
SELECT id,
NVL(capital_amount,0) capital_amount
FROM okl_k_lines_full_v line,
okc_statuses_b sts
WHERE line.cle_id = p_cle_id
AND line.sts_code = sts.code
--Bug# 4959361: Include Terminated lines when fetching sub-line amount
--AND sts.ste_code not in ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
SELECT line.start_date
FROM okc_k_lines_b line,
okc_k_items item
WHERE item.cle_id = p_line_id
AND item.object1_id1 = line.id
AND item.object1_id2 = '#'
AND line.dnz_chr_id = p_chr_id
AND item.dnz_chr_id = p_chr_id
AND item.jtot_object1_code = 'OKX_COVASST';
SELECT COUNT(1)
FROM okc_rules_b rule,
okc_rule_groups_b rgp,
okc_k_lines_b line,
okc_line_styles_b style,
okl_strm_type_b strm
WHERE rgp.dnz_chr_id = p_chr_id
AND rgp.cle_id IS NOT NULL
AND rgp.cle_id = line.id
AND line.lse_id = style.id
AND style.lty_code = p_line_type
AND rgp.id = rule.rgp_id
AND rule.rule_information_category = 'LASLH'
AND rule.jtot_object1_code = 'OKL_STRMTYP'
AND rule.object1_id1 = strm.id
--AND strm.code = 'RENT';
SELECT kle.name,
kle.CURRENCY_CODE,
kle.id,
kle.RESIDUAL_VALUE,
kle.TRACKED_RESIDUAL,
kle.CAPITAL_REDUCTION,
kle.TRADEIN_AMOUNT,
kle.RVI_PREMIUM,
kle.OEC,
kle.residual_code,
kle.residual_grnty_amount,
kle.start_date,
kle.end_date
FROM OKL_K_LINES_FULL_V kle,
OKC_LINE_STYLES_B ls,
OKC_STATUSES_B sts
WHERE kle.lse_id = ls.id
AND ls.lty_code = ltycode
AND kle.dnz_chr_id = chrid
AND sts.code = kle.sts_code
AND sts.ste_code NOT IN ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
SELECT line.id,
line.name
FROM okl_k_lines_full_v line,
okc_line_styles_v style,
okc_statuses_b sts
WHERE line. dnz_chr_id = p_chr_id
AND line.lse_id = style.id
AND style.lty_code = 'FREE_FORM1'
AND line.sts_code = sts.code
AND sts.ste_code NOT IN ( 'HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
SELECT crl.id slh_id,
crl.object1_id1,
crl.RULE_INFORMATION1,
crl.RULE_INFORMATION2,
crl.RULE_INFORMATION3,
crl.RULE_INFORMATION4,
crl.RULE_INFORMATION5,
crl.RULE_INFORMATION6,
crl.RULE_INFORMATION7,
crl.RULE_INFORMATION10
FROM OKC_RULE_GROUPS_B crg,
OKC_RULES_B crl
WHERE crl.rgp_id = crg.id
AND crg.RGD_CODE = rgcode
AND crl.RULE_INFORMATION_CATEGORY = rlcat
AND crg.cle_id = cleId;
SELECT kle.subsidy_id subsidy_id,
cleb.cle_id asset_cle_id,
clet.name subsidy_name,
clet_asst.name asset_number,
cleb.id subsidy_cle_id,
cleb_asst.start_date asset_start_date,
kle.amount,
kle.subsidy_override_amount
FROM okl_k_lines kle,
okc_k_lines_tl clet,
okc_k_lines_b cleb,
okc_line_styles_b lseb,
okc_k_lines_tl clet_asst,
okc_k_lines_b cleb_asst,
okc_line_styles_b lseb_asst
WHERE kle.id = cleb.id
AND clet.id = cleb.id
AND clet.LANGUAGE = USERENV('LANG')
AND cleb.cle_id = cleb_asst.id
AND cleb.dnz_chr_id = cleb_asst.dnz_chr_id
AND cleb.sts_code <> 'ABANDONED'
AND lseb.id = cleb.lse_id
AND lseb.lty_code = 'SUBSIDY'
AND clet_asst.id = cleb_asst.id
AND clet_asst.LANGUAGE = USERENV('LANG')
AND lseb_asst.id = cleb_asst.lse_id
AND lseb_asst.lty_code = 'FREE_FORM1'
AND cleb_asst.sts_code <> 'ABANDONED'
AND cleb_asst.dnz_chr_id = p_chr_id
AND cleb_asst.chr_id = p_chr_id ;
SELECT recourse_yn
FROM okl_subsidies_b
WHERE id = p_subsidy_id;
SELECT pyd.vendor_id
FROM okl_party_payment_dtls pyd,
okc_k_party_roles_b cplb,
okc_k_lines_b cleb
WHERE pyd.cpl_id = cplb.id
AND cplb.cle_id = cleb.id
AND cplb.chr_id IS NULL
AND cplb.RLE_CODE = 'OKL_VENDOR'
AND cleb.id = p_subsidy_cle_id;
SELECT rule.rule_information1
FROM okc_rules_b rule,
okc_rule_groups_b rgp
WHERE rule.rgp_id = rgp.id
AND rgp.dnz_chr_id = p_chr_id
AND rgp.chr_id = p_chr_id
AND rgp.rgd_code = 'LABILL'
AND rule.rule_information_category = 'LAINVD';
SELECT txl.asset_number asset_num,
fa.id fa_id,
txd.tax_book
FROM okc_k_items cim,
okc_k_lines_V fa,
okc_line_styles_b fa_lse,
OKL_TRX_ASSETS trx,
okl_txl_assets_b txl,
okl_txd_assets_v txd,
okc_statuses_b sts
WHERE cim.cle_id = fa.id
AND cim.dnz_chr_id = p_chr_id
AND fa.lse_id = fa_lse.id
AND txd.tal_id = txl.id
AND txl.kle_id = fa.id
AND txl.tas_id = trx.id
AND fa_lse.lty_code = 'FIXED_ASSET'
AND fa.dnz_chr_id = cim.dnz_chr_id
AND sts.code = fa.sts_code
AND sts.ste_code NOT IN ('HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED')
AND trx.tsu_code = 'ENTERED'
AND txl.tal_type = 'CFA';
SELECT 'Y'
FROM okl_sgn_translations sgn
WHERE sgn.jtot_object1_code = 'FA_BOOK_CONTROLS'
AND sgn.object1_id1 = p_tax_book
AND sgn.sgn_code = 'STMP';
SELECT khr.id,
khr.deal_type
FROM OKL_K_HEADERS khr
WHERE khr.id = p_chr_id;
SELECT lne.id id,
style.name line_type
FROM okc_k_lines_b lne,
okc_line_styles_v style
WHERE dnz_chr_id = p_chr_id
AND style.id = lne.lse_id;
SELECT cle.id,
style.name line_type
FROM okl_k_lines kle,
okc_k_lines_b cle,
okc_line_styles_v style
WHERE cle.dnz_chr_id = p_chr_id
AND cle.id = kle.id
AND cle.lse_id = style.id
AND kle.fee_type = 'FINANCED'
AND NVL(kle.fee_purpose_code,'XXX') <> 'SALESTAX';
SELECT styt.name stream_type,
rule.id rule_id,
rgp.id rgp_id
FROM okc_rules_b rule,
okc_rule_groups_b rgp,
okl_strm_type_b sty,
okl_strm_type_tl styt
WHERE NVL(rgp.cle_id, -1) = p_kle_id
AND rgp.dnz_chr_id = p_khr_id
AND rgp.rgd_code = 'LALEVL'
AND rgp.id = rule.rgp_id
AND rule.rule_information_category = 'LASLH'
AND TO_NUMBER(rule.object1_id1) = sty.id
AND styt.LANGUAGE = USERENV('LANG')
AND sty.id = styt.id;
SELECT Fnd_Date.canonical_to_date(sll.rule_information2) start_date,
sll.rule_information1 seq,
sll.rule_information6 amt,
sll.rule_information7 stub_day,
sll.rule_information13 rate
FROM okc_rules_b sll
WHERE sll.rgp_id = p_rgp_id
AND sll.object2_id1 = TO_CHAR(p_rule_id)
AND sll.rule_information_category = 'LASLL'
ORDER BY 1,2;
SELECT '!'
FROM okc_k_headers_b CHR,
okl_trx_contracts ktrx
WHERE ktrx.khr_id_new = chr.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 chr.id = p_chr_id
AND chr.orig_system_source_code = 'OKL_REBOOK';
select txl.id tal_id ,
txl.asset_number asset_number,
fab.cost original_cost,
txl.depreciation_cost new_cost
from okl_trx_assets trx,
okl_txl_assets_b txl,
okc_k_lines_b cleb,
okc_statuses_b sts,
fa_books fab,
fa_additions fa
where trx.id = txl.tas_id
and trx.tsu_code = 'ENTERED'
and trx.tas_type = 'CRB'
and txl.kle_id = cleb.id
and txl.tal_type = 'CRB'
and cleb.dnz_chr_id = p_chr_id
and cleb.lse_id = 42
and sts.code = cleb.sts_code
and sts.ste_code NOT IN ('HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED')
and fab.book_type_code = txl.corporate_book
and fab.asset_id = fa.asset_id
and fa.asset_number = txl.asset_number
and fab.transaction_header_id_out is null;
select txd.asset_number,
txd.tax_book,
txd.cost new_cost,
fab.cost original_cost
from okl_txd_assets_b txd,
fa_books fab,
fa_additions fa
where txd.tal_id = p_tal_id
and fab.book_type_code = txd.tax_book
and fab.asset_id = fa.asset_id
and fa.asset_number = txd.asset_number
and fab.transaction_header_id_out is null;
SELECT '!',
chr.orig_system_id1
FROM okc_k_headers_b CHR,
okl_trx_contracts ktrx
WHERE ktrx.khr_id_new = chr.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 chr.id = p_chr_id
AND chr.orig_system_source_code = 'OKL_REBOOK';
SELECT cle.id,
cle.orig_system_id1
FROM okc_k_lines_b cle,
okc_statuses_b sts
WHERE cle.chr_id = p_chr_id
AND cle.lse_id = 33 --Financial Asset Line
and sts.code = cle.sts_code
and sts.ste_code NOT IN ('HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
SELECT cle.id,
cim.object1_id1,
cim.object1_id2
FROM okc_k_lines_b cle,
okc_k_items cim,
okc_line_styles_b lse,
okc_statuses_b sts
WHERE cle.dnz_chr_id = p_chr_id
AND cle.cle_id = p_cle_id
AND cle.lse_id = lse.id
AND lse.lty_code = p_lty_code
AND cim.cle_id = cle.id
AND cim.dnz_chr_id = cle.dnz_chr_id
AND sts.code = cle.sts_code
AND sts.ste_code NOT IN ('HOLD', 'TERMINATED', 'EXPIRED', 'CANCELLED');
SELECT asset_category_id
FROM mtl_system_items
WHERE inventory_item_id = p_inv_item_id
AND organization_id = p_org_id;
SELECT asset_category_id,
asset_number
FROM fa_additions
WHERE asset_id = p_asset_id;
select STATUS, name
from OKX_PARTIES_V
where Id1 = OBJECT1_ID1;
select STATUS, DESCRIPTION
from OKX_CUSTOMER_ACCOUNTS_V
where Id1 = (select cust_acct_id from okc_k_headers_b where id =p_chr_id);
SELECT
pdt.name
FROM okl_products_v pdt
,okl_k_headers_v khr
,okc_k_headers_b CHR
WHERE 1=1
AND khr.id = p_chr_id
AND pdt_id = pdt.id
AND khr.id = CHR.id;