[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT line_number, cle_id
FROM OKC_K_LINES_B
WHERE id = p_line_id;
SELECT contract_number, contract_number_modifier
FROM okc_k_headers_all_b WHERE id = p_chr_id;
SELECT clev.cle_id, RTRIM(clev.line_number) line_number
FROM
OKC_K_LINES_V clev
WHERE
clev.id = p_cle_id;
SELECT line_number
FROM OKC_K_LINES_B
WHERE id = p_line_id;
SELECT RTRIM(RTRIM(line_number) || ', ' || RTRIM(lsev.name) || ' ' ||
RTRIM(clev.name)) "LINE_NAME"
FROM OKC_LINE_STYLES_V lsev,
OKC_K_LINES_V clev
WHERE lsev.id = clev.lse_id
AND clev.id = p_cle_id;
SELECT renewal_status
FROM
OKS_K_HEADERS_B
WHERE
chr_id = p_chr_id;
SELECT CUST_PO_NUMBER_REQ_YN, CUST_PO_NUMBER
FROM okc_k_headers_all_b
WHERE id = p_chr_id AND CUST_PO_NUMBER_REQ_YN = 'Y';
SELECT SERVICE_PO_NUMBER sr_po_num, SERVICE_PO_REQUIRED sr_po_flag
FROM
OKS_K_HEADERS_B
WHERE chr_id = p_chr_id;
SELECT currency_code, authoring_org_id
FROM okc_k_headers_all_b
WHERE id = p_chr_id;
SELECT CONVERSION_TYPE --object1_id1
FROM okc_k_headers_all_b
WHERE id = p_chr_id;
SELECT currency_code, price_list_id, sts_code
FROM okc_k_headers_all_b WHERE id = p_chr_id;
SELECT ste_code
FROM okc_statuses_b
WHERE ste_code = 'ENTERED' AND code = l_sts_code;
SELECT CURRENCY_CODE
FROM QP_LIST_HEADERS_B
WHERE list_type_code IN ('PRL','AGR')
AND id1 = l_price_list_id;*/
SELECT line_number, price_list_id, sts_code
FROM okc_k_lines_b WHERE chr_id = p_chr_id
AND lse_id IN (1, 12, 46, 19)
AND date_cancelled IS NULL ; --Changes [llc]
SELECT line_number, price_list_id
FROM okc_k_lines_b,
okc_statuses_b
WHERE chr_id = p_chr_id
AND lse_id IN (1,12,46,19)
AND ste_code = 'ENTERED'
AND code = sts_code
AND date_cancelled IS NULL;
SELECT RENEWAL_PRICE_LIST FROM OKS_K_HEADERS_B
WHERE CHR_ID = p_chr_id;
SELECT authoring_org_id
FROM okc_k_headers_all_b
WHERE id = p_chr_id;
SELECT tax_method_code
FROM zx_product_options_all
WHERE org_id = p_org_id
AND application_id = 222;
SELECT inv_trx_type INTO l_inv_trx_type
FROM oks_k_headers_b
WHERE id = p_chr_id;
SELECT length(p_cc_num_stripped)
INTO l_len_credit_card_num
FROM dual;
SELECT to_number(substr(p_cc_num_stripped, i, 1))
INTO l_stripped_num_table(i)
FROM dual;
SELECT chdr.contract_number
, cle.id
, chdr.contract_number_modifier
FROM okc_k_headers_all_b chdr,
OKC_K_ITEMS cim,
OKC_LINE_STYLES_V lse,
OKC_K_LINES_B cle,
OKC_STATUSES_B sts
WHERE chdr.id = cle.dnz_chr_id
AND object1_id1 = p_object1_id1
AND object1_id2 = p_object1_id2
AND jtot_object1_code = p_jtot_object1_code
AND cim.cle_id = cle.id
AND (p_start_date BETWEEN cle.start_date
AND NVL(cle.date_terminated, cle.end_date)
OR p_end_date BETWEEN cle.start_date
AND NVL(cle.date_terminated, cle.end_date)
OR (p_start_date < cle.start_date
AND p_end_date > cle.start_date ))
AND lse.lty_code = p_lty_code
AND lse.id = cle.lse_id
AND cle.id <> p_cle_id
AND nvl(cle.date_terminated, SYSDATE + 1) > SYSDATE -- added condition for bug # 3646108
AND sts.code = chdr.sts_code
AND sts.ste_code NOT IN ('CANCELLED', 'TERMINATED', 'EXPIRED')
AND cle.date_cancelled IS NULL -- Changes [llc]
;
SELECT cle.id
, lse.lty_code
, cle.name
, cle.start_date
, cle.end_date
, cim.jtot_object1_code
, cim.object1_id1
, cim.object1_id2
FROM OKC_K_ITEMS cim,
OKC_LINE_STYLES_B lse,
OKC_K_LINES_V cle
WHERE cim.cle_id = cle.id
and lse.id = cle.lse_id
and cle.chr_id = p_chr_id
and cle.date_cancelled is null --Changes [llc]
;
SELECT cle.id
,cle.PRICE_NEGOTIATED
, cle.name
, cle.start_date
, cle.end_date
, lse.lty_code
, cim.jtot_object1_code
, cim.object1_id1
, cim.object1_id2
, cle.date_terminated
FROM OKC_K_ITEMS cim,
OKC_LINE_STYLES_B lse,
OKC_K_LINES_V cle
WHERE cim.cle_id = cle.id
AND lse.LTY_CODE IN
('COVER_CUST', 'COVER_ITEM', 'COVER_PROD',
'COVER_PTY', 'COVER_SITE', 'COVER_SYS','INST_CTR')
AND lse.id = cle.lse_id
AND cle.cle_id = p_cle_id
AND nvl(cle.date_terminated, sysdate+1) > sysdate -- added condition for bug # 3646108
AND cle.date_cancelled is null; --Changes [llc]
SELECT trgh.contract_number, nvl(trgh.contract_number_modifier, OKC_API.G_MISS_CHAR),
nvl(rtrim(trgtl.line_number) || '.' || RTRIM(trgsl.line_number) ||', '|| RTRIM(trgst.name) ||' '|| RTRIM(trgsl.name), OKC_API.G_MISS_CHAR) trg_name,
nvl(rtrim(srctl.line_number) || '.' || RTRIM(srcsl.line_number) ||', '|| RTRIM(srcst.name) ||' '|| RTRIM(srcsl.name), OKC_API.G_MISS_CHAR) src_name,
srcsl.id srcsl_id
FROM okc_k_headers_all_b trgh,
okc_k_lines_v trgsl,
okc_k_items trgi,
okc_k_lines_b trgtl,
okc_line_styles_v trgst,
okc_k_lines_v srcsl,
okc_k_items srci,
okc_k_lines_b srctl,
okc_line_styles_v srcst,
okc_statuses_b sts
WHERE srcsl.dnz_chr_id = cp_chr_id
AND srcsl.lse_id IN (7, 8, 9, 10, 11, 35, 13, 18, 25)
AND nvl(srcsl.date_terminated, cp_date + 1) > cp_date
AND srci.cle_id = srcsl.id
AND srcst.id = srcsl.lse_id
AND srctl.id = srcsl.cle_id
AND trgi.object1_id1 = srci.object1_id1
AND trgi.jtot_object1_code = srci.jtot_object1_code
AND trgi.object1_id2 = srci.object1_id2
AND trgsl.id = trgi.cle_id
AND trgsl.id <> srcsl.id
AND nvl(trgsl.date_terminated, cp_date + 1) > cp_date
AND (
(srcsl.start_date BETWEEN trgsl.start_date
AND nvl(trgsl.date_terminated, trgsl.end_date))
OR (nvl(srcsl.date_terminated, srcsl.end_date) BETWEEN trgsl.start_date
AND nvl(trgsl.date_terminated, trgsl.end_date))
OR (srcsl.start_date < trgsl.start_date
AND nvl(srcsl.date_terminated, srcsl.end_date) > trgsl.start_date)
)
AND nvl(trgsl.date_terminated, trgsl.end_date) <> trgsl.start_date/*Bugfix 6040062-FP of 6013613*/
AND trgst.id = trgsl.lse_id
AND trgtl.id = trgsl.cle_id
AND trgh.id = trgsl.dnz_chr_id
AND sts.code = trgh.sts_code
AND sts.ste_code NOT IN ('CANCELLED', 'TERMINATED', 'EXPIRED')
AND trgsl.date_cancelled IS NULL -- Changes [llc]
ORDER BY srcsl.id; -- note: each time the source subline changes, we dump the warning/error messages for the previous line
SELECT cle.id, lse.lty_code, cle.name, cle.lse_id,
cle.start_date, cle.end_date,
cim.jtot_object1_code, cim.object1_id1, cim.object1_id2
FROM OKC_K_ITEMS cim,
OKC_LINE_STYLES_B lse,
OKC_K_LINES_V cle
WHERE cim.cle_id = cle.id
AND lse.id = cle.lse_id
AND cle.chr_id = p_chr_id
AND cle.date_cancelled IS NULL --Changes [llc]
;
SELECT cle.id, cle.PRICE_NEGOTIATED, cle.name, cle.lse_id,
cle.start_date, cle.end_date, lse.lty_code,
cim.jtot_object1_code, cim.object1_id1, cim.object1_id2
FROM OKC_K_ITEMS cim,
OKC_LINE_STYLES_B lse,
OKC_K_LINES_V cle
WHERE cim.cle_id = cle.id
AND lse.LTY_CODE IN
('COVER_CUST', 'COVER_ITEM', 'COVER_PROD',
'COVER_PTY', 'COVER_SITE', 'COVER_SYS', 'INST_CTR')
AND lse.id = cle.lse_id
AND cle.cle_id = p_cle_id
AND cle.date_cancelled IS NULL ; --Changes [llc]
SELECT COUNT( * )
FROM OKC_LINE_STYLES_B lse,
OKC_K_LINES_B cle
WHERE lse.lty_code = 'COVERAGE'
AND lse.id = cle.lse_id
AND cle.cle_id = p_cle_id
GROUP BY cle.cle_id;
SELECT Usage_type
FROM OKS_K_LINES_B
WHERE cle_id = p_id;
SELECT Default_quantity
FROM OKS_K_LINES_B
WHERE cle_id = p_cle_id;
SELECT NVL(SUM(PERCENT), 0)
FROM OKS_K_SALES_CREDITS sc,
OE_SALES_CREDIT_TYPES sct
WHERE sc.cle_id = p_cle_id
AND sc.sales_credit_type_id1 = sct.sales_credit_type_id
AND sct.quota_flag = 'Y';
SELECT cust_acct_id
-- FROM OKX_CUST_ROLE_RESP_V
FROM OKX_CUST_CONTACTS_V --Bug 4558172
WHERE id1 = p_contact_id;
SELECT CA.CUST_ACCOUNT_ID
FROM HZ_CUST_ACCT_SITES_ALL CA, HZ_CUST_SITE_USES_ALL CS
WHERE CS.SITE_USE_ID = l_billto_siteuse_id
AND CS.SITE_USE_CODE = 'BILL_TO'
AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID;
SELECT Contact.object1_id1
FROM Okc_contacts Contact
, Okc_k_party_roles_b Party
, okc_k_lines_b lines
WHERE Contact.cpl_id = Party.id
AND party.cle_id = p_cle_id
AND party.jtot_object1_code = 'OKX_PARTY'
AND Contact.cro_code = 'CUST_BILLING'
AND party.cle_id = lines.id
AND party.dnz_chr_id = lines.dnz_chr_id;
SELECT BILL_TO_SITE_USE_ID
FROM OKC_K_LINES_B
-- Bug 4558172 --
-- where cle_id = p_id;
SELECT cle.id id
FROM okc_k_lines_v cle, okc_line_styles_v lse
WHERE lse.id = cle.lse_id
AND lse.LTY_CODE IN
('COVER_CUST', 'COVER_ITEM', 'COVER_PROD',
'COVER_PTY', 'COVER_SITE', 'COVER_SYS', 'INST_CTR')
AND cle.dnz_chr_id = p_chr_id
AND cle.date_cancelled IS NULL --Changes [llc]
;
SELECT object1_id1
FROM okc_k_items
WHERE cle_id = line_id ;
SELECT org_id --from R12, we use org_id instead of authoring_org_id
FROM okc_k_headers_all_b
WHERE id = p_chr_id;
SELECT contact.object1_id1
FROM okc_contacts contact
,okc_k_party_roles_b party
,okc_k_headers_all_b header
WHERE contact.cpl_id = party.id
AND party.chr_id = p_chr_id
AND party.jtot_object1_code = 'OKX_OPERUNIT' --'okx_party'
--npalepu modified on 06-FEB-2007 for bug # 5855434
/* AND contact.cro_code = 'SALESPERSON' */
AND contact.cro_code in (SELECT cro_code
FROM okc_contact_sources
WHERE rle_code in ('VENDOR', 'MERCHANT')
AND buy_or_sell = 'S'
AND jtot_object_code = 'OKX_SALEPERS')
--end npalepu
AND party.chr_id = header.id
--npalepu added condition on party.dnz_chr_id for bug # 5845463
AND party.dnz_chr_id = p_chr_id;
SELECT cle.id, COUNT( * )
FROM OKC_K_ITEMS cim,
OKC_K_LINES_B cle
WHERE cim.cle_id = cle.id
AND cle.dnz_chr_id = p_chr_id
AND cle.date_cancelled IS NULL --Changes [llc]
GROUP BY cle.id
HAVING COUNT( * ) > 1;
SELECT cle_id, count(*)
FROM okc_k_items
WHERE dnz_chr_id = p_chr_id
GROUP BY cle_id
HAVING COUNT(*) > 1;
SELECT cle.id
FROM OKC_K_LINES_V cle
WHERE cle.dnz_chr_id = p_chr_id
AND cle.name IS NULL
AND cle.date_cancelled IS NULL ; --Changes [llc]
SELECT COUNT( * )
FROM OKC_K_PARTY_ROLES_B cpl
WHERE cpl.rle_code = p_rle_code
AND cpl.dnz_chr_id = p_chr_id
AND cpl.cle_id IS NULL;
SELECT contract_number, Short_description
FROM OKC_K_HEADERS_V
WHERE id = p_chr_id;
SELECT contract_number, QCL_ID, scs_code
FROM okc_k_headers_all_b
WHERE id = p_chr_id;
SELECT price_list_id -- Object1_id1
FROM okc_k_headers_all_b
WHERE id = p_chr_id;
SELECT id
FROM OKC_K_GRPINGS
WHERE Included_chr_id = p_chr_id;
SELECT id
FROM OKC_K_PROCESSES
WHERE Chr_id = p_chr_id;
SELECT id
FROM OKC_K_LINES_B
WHERE dnz_chr_id = p_chr_id
AND lse_id IN (1, 19)
AND date_cancelled IS NULL ; --Changes [llc]
SELECT id, price_unit
FROM OKC_K_LINES_B
WHERE dnz_chr_id = p_chr_id
AND cle_id = p_cle_id
AND lse_id IN (7, 8, 9, 10, 11, 18, 25, 35)
AND date_cancelled IS NULL --Changes [llc]
;
SELECT id, uom_code
FROM OKC_K_ITEMS_V
WHERE dnz_chr_id = p_chr_id
AND cle_id = p_cle_id;
SELECT /*+ ordered use_nl(rlb,ri) */
rlb.id sub_line_id, rlb.price_unit,
ri.uom_code
FROM okc_k_lines_b rla,
okc_k_lines_b rlb,
okc_k_items_v ri
WHERE rla.dnz_chr_id = p_chr_id
AND rla.lse_id IN (1,19)
AND rla.date_cancelled IS NULL
AND rlb.cle_id = rla.id
AND rlb.lse_id IN (7,8,9,10,11,18,25,35)
AND rlb.date_cancelled IS NULL
------AND ri.dnz_chr_id = rla.dnz_chr_id --not necessary
AND ri.cle_id = rlb.id
AND (rlb.price_unit IS NULL OR ri.uom_code IS NULL);
SELECT authoring_org_id
FROM okc_k_headers_all_b
WHERE id = p_chr_id;
SELECT cle.id, sts.ste_code sts_code
FROM okc_k_lines_v cle,
okc_statuses_b sts
WHERE cle.dnz_chr_id = p_chr_id
AND sts.code = cle.sts_code
AND cle.lse_id IN (1, 19);
SELECT CUST_ACCT_ID -- object1_id1
FROM okc_k_lines_b
WHERE id = l_service_id;
SELECT object1_id1
FROM okc_k_items_v
WHERE cle_id = p_cle_id ;
SELECT id, start_date
FROM okc_k_lines_v
WHERE cle_id = l_service_id
AND lse_id IN (9, 25, 7); -- 7 added for bug#2430496
SELECT object1_id1
FROM okc_k_items_v
WHERE cle_id = p_cle_id ;
SELECT inventory_item_id
FROM csi_item_instances
WHERE instance_id = p_cp_id;
SELECT date_cancelled
INTO l_date_cancelled
FROM okc_k_lines_b
WHERE id = l_cle_rec.cle_id;
SELECT Cust_trx_type_id
FROM RA_CUST_TRX_TYPES_ALL
WHERE SET_OF_BOOKS_ID = bookId
AND org_id = orgId
AND Cust_trx_type_id = NVL(object1Id1, - 99);
SELECT Cust_trx_type_id
FROM RA_CUST_TRX_TYPES_ALL
WHERE SET_OF_BOOKS_ID = bookId
AND org_id = orgId
AND TYPE = 'INV' AND name = 'Invoice-OKS' AND SYSDATE <= nvl(end_date, SYSDATE);
SELECT set_of_books_id
FROM ar_system_parameters_all
WHERE org_id = p_org_id;
SELECT v2.exempt_certificate_number
FROM zx_exemptions v2
WHERE (trunc(l_start_date) BETWEEN trunc(v2.EFFECTIVE_FROM)
AND nvl(trunc(v2.EFFECTIVE_TO), trunc(l_start_date)))
AND v2.tax_exemption_id = l_tax_exemption_id;
SELECT exempt_certificate_number, effective_from, effective_to
FROM zx_exemptions v1
WHERE EXEMPTION_STATUS_CODE IN ('PRIMARY', 'MANUAL', 'UNAPPROVED')
AND v1.tax_exemption_id = l_tax_exemption_id --selected tax_exemption_id from top_line_csr
AND NOT EXISTS(
SELECT 1 FROM zx_exemptions v2
WHERE v2.tax_exemption_id = l_tax_exemption_id
AND trunc(l_trx_date) BETWEEN trunc(v1.EFFECTIVE_FROM) AND nvl(trunc(v1.EFFECTIVE_TO), trunc(l_trx_date ))
)
;
SELECT exemption_status_code
FROM ZX_EXEMPTIONS
WHERE exemption_status_code = 'UNAPPROVED'
AND TAX_EXEMPTION_ID = l_tax_exempt_id;
SELECT c.party_id,
a.party_site_id,
a.cust_account_id,
a.org_id
FROM hz_cust_acct_sites_all a,
hz_cust_site_uses_all b,
hz_party_sites c
WHERE a.cust_acct_site_id = b.cust_acct_site_id
AND c.party_site_id = a.party_site_id
AND b.site_use_id = p_site_use_id;
SELECT CLEB.id,
tax_exemption_id,
line_number,
bill_to_site_use_id,
ship_to_site_use_id,
cust_acct_id,
start_date,
exempt_certificate_number,
exempt_reason_code
FROM
OKC_K_LINES_B CLEB,
OKS_K_LINES_B KLN
WHERE
CLEB.dnz_chr_id = p_chr_id AND
CLEB.ID = KLN.CLE_ID AND
lse_id IN (1, 12, 19, 46);
SELECT MAX(date_transaction)
FROM oks_level_elements
WHERE parent_cle_id = l_cle_id;
SELECT cust_account_id
FROM zx_exemptions
WHERE exemption_status_code IN ('PRIMARY', 'MANUAL', 'UNAPPROVED')
AND tax_exemption_id = l_tax_exemption_id; --selected tax_exemption_id from top_line_csr
SELECT cust_account_id
FROM zx_exemptions_v
WHERE EXEMPT_CERTIFICATE_NUMBER = l_tax_exempt_number
AND EXEMPT_REASON_CODE = l_tax_exempt_reason_code
AND exemption_status_code IN ('PRIMARY', 'MANUAL', 'UNAPPROVED');
SELECT name
FROM okx_customer_accounts_v
WHERE id1 = p_cust_acct_id;
SELECT
tax_exemption_id,
exempt_certificate_number,
exempt_reason_code,
bill_to_site_use_id,
ship_to_site_use_id,
cust_acct_id,
start_date,
end_date
FROM
okc_k_headers_all_b OKC,
OKS_K_HEADERS_B OKS
WHERE
OKC.id = p_chr_id AND
OKC.ID = OKS.chr_id
AND (exempt_certificate_number IS NOT NULL
OR tax_exemption_id IS NOT NULL);
SELECT BATCH_SOURCE_ID
FROM ra_batch_sources_all
WHERE org_id = p_org_id
AND NAME = 'OKS_CONTRACTS';
SELECT inv_trx_type
FROM OKS_K_HEADERS_B
WHERE OKS_K_HEADERS_B.id = p_chr_id;
SELECT ACCT_SITE_SHIP.CUST_ACCOUNT_ID
FROM
HZ_CUST_SITE_USES_ALL S_SHIP,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE_SHIP
WHERE S_SHIP.SITE_USE_ID = p_site_use_id
AND S_SHIP.CUST_ACCT_SITE_ID = acct_site_ship.cust_acct_site_id
;
SELECT prt.party_name
FROM oe_hold_sources_all ohs,
HZ_PARTIES prt,
HZ_PARTY_SITES PS, HZ_CUST_ACCT_SITES_ALL CA, HZ_CUST_SITE_USES_ALL CS,
okc_k_headers_all_b rl
WHERE ohs.hold_entity_code = 'C'
AND ohs.released_flag = 'N'
AND ohs.org_id = okc_context.get_okc_org_id
AND PS.PARTY_SITE_ID = CA.PARTY_SITE_ID
AND CA.CUST_ACCT_SITE_ID = CS.CUST_ACCT_SITE_ID
AND CS.SITE_USE_CODE = 'BILL_TO'
AND PS.PARTY_ID = prt.party_id
AND prt.PARTY_TYPE IN ('PERSON', 'ORGANIZATION')
AND CS.SITE_USE_ID = rl.BILL_TO_SITE_USE_ID -- object1_id1
AND rl.id = p_chr_id
AND ohs.hold_entity_id = CA.CUST_ACCOUNT_ID; --okx_bill_to.cust_account_id
SELECT CS.SITE_USE_ID, CS.STATUS, CS.CUST_ACCT_SITE_ID
FROM okc_k_lines_b rl,
HZ_CUST_SITE_USES_ALL CS
WHERE rl.dnz_chr_id = p_chr_id
AND rl.id = line_id
AND CS.SITE_USE_ID = decode(l_use_code, 'BILL_TO', rl.BILL_TO_SITE_USE_ID, rl.SHIP_TO_SITE_USE_ID)
AND CS.SITE_USE_CODE = l_use_code -- 'BILL_TO' or 'SHIP_TO'
AND rl.date_cancelled IS NULL --Changes [llc]
;
SELECT CA.STATUS STATUS
FROM HZ_CUST_ACCT_SITES_ALL CA
WHERE CA.CUST_ACCT_SITE_ID = l_cust_acct_site_id;
SELECT id, line_number FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id
AND chr_id = p_chr_id
AND cle_id IS NULL
AND lse_id IN (1, 12, 14, 19, 46)
AND (date_terminated IS NULL OR date_terminated > SYSDATE)
AND date_cancelled IS NULL --Changes [llc]
;
SELECT rl.id, rl.line_number,
cs.site_use_id, cs.status site_use_status,
cs.cust_acct_site_id,
ca.status site_status,
'BILL_TO' use_code
FROM okc_k_lines_b rl,
hz_cust_site_uses_all cs,
hz_cust_acct_sites_all ca
WHERE rl.dnz_chr_id = p_chr_id
AND rl.chr_id = p_chr_id
AND rl.cle_id IS NULL
AND rl.lse_id IN (1,12, 14, 19, 46)
AND (rl.date_terminated IS NULL OR rl.date_terminated > SYSDATE)
AND rl.date_cancelled IS NULL
--
AND cs.site_use_id (+)= rl.bill_to_site_use_id
AND cs.site_use_code (+)= 'BILL_TO'
--
AND ca.cust_acct_site_id (+)= cs.cust_acct_site_id
UNION ALL
SELECT rl.id, rl.line_number,
cs.site_use_id, cs.status site_use_status,
cs.cust_acct_site_id,
ca.status site_status,
'SHIP_TO' use_code
FROM okc_k_lines_b rl,
hz_cust_site_uses_all cs,
hz_cust_acct_sites_all ca
WHERE rl.dnz_chr_id = p_chr_id
AND rl.chr_id = p_chr_id
AND rl.cle_id IS NULL
AND rl.lse_id IN (1,12, 14, 19, 46)
AND (rl.date_terminated IS NULL OR rl.date_terminated > SYSDATE)
AND rl.date_cancelled IS NULL
--
AND cs.site_use_id (+)= rl.ship_to_site_use_id
AND cs.site_use_code (+)= 'SHIP_TO' --l_use_code -- 'bill_to' or 'ship_to'
--
AND ca.cust_acct_site_id (+)= cs.cust_acct_site_id;
SELECT CS.SITE_USE_ID, CS.STATUS, CS.CUST_ACCT_SITE_ID
FROM okc_k_headers_all_b rl,
HZ_CUST_SITE_USES_ALL CS
WHERE rl.id = p_chr_id
AND CS.SITE_USE_ID = l_site_use_id
AND CS.SITE_USE_CODE = l_site_use_code;
SELECT bill_to_site_use_id, ship_to_site_use_id
FROM okc_k_headers_all_b
WHERE id = p_chr_id ;
SELECT cle.id, cle.lse_id, sts.ste_code sts_code,
cle.start_date, cle.end_date, cle.date_terminated,
cim.jtot_object1_code, cim.object1_id1, cim.object1_id2
FROM okc_k_items cim,
okc_k_lines_b cle,
okc_statuses_b sts
WHERE cle.dnz_chr_id = p_chr_id
AND cle.cle_id IS NULL
AND sts.code = cle.sts_code
AND cim.cle_id = cle.id
AND cle.date_cancelled IS NULL --Changes [llc]
;
SELECT 1
FROM Mtl_system_items
WHERE Inventory_item_id = p_inv_item
AND Organization_id = p_org_id
AND (SYSDATE BETWEEN
NVL(start_date_active, SYSDATE) AND NVL(end_date_active, SYSDATE));
SELECT service_item_flag, vendor_warranty_flag,
usage_item_flag, serviceable_product_flag,
customer_order_enabled_flag, internal_order_enabled_flag,
invoice_enabled_flag
FROM MTL_SYSTEM_ITEMS_B_KFV
WHERE Inventory_item_id = p_inv_item
AND Organization_id = p_org_id;
SELECT inventory_item_id
FROM csi_item_instances -- okx_cust_prod_v
WHERE instance_id = p_cp_id;
SELECT cle.id, cle.PRICE_NEGOTIATED, cle.lse_id, sts.ste_code sts_code,
cle.start_date, cle.end_date, cle.date_terminated,
cim.jtot_object1_code, cim.object1_id1,
cim.object1_id2
FROM OKC_K_ITEMS cim,
OKC_K_LINES_B cle,
OKC_STATUSES_B sts
WHERE cim.cle_id = cle.id
AND cle.cle_id = p_cle_id
AND sts.code = cle.sts_code
AND cle.lse_id IN (35, 7, 8, 9, 10, 11, 13, 18, 25);
SELECT object1_id1
FROM Okc_k_items
WHERE cle_id = p_id;
SELECT DECODE(ENABLED_FLAG, 'Y', 'A', 'I') status
FROM MTL_SYSTEM_ITEMS_B_KFV --okx_system_items_v
WHERE INVENTORY_ITEM_ID = p_inv_item
AND Organization_id = p_org_id
AND serviceable_product_flag = 'Y';
SELECT status
FROM HZ_PARTIES --okx_parties_v
WHERE PARTY_TYPE IN ('PERSON', 'ORGANIZATION') AND PARTY_ID = l_inv_id;
SELECT cs.Service_order_allowed_flag, CP.ACTIVE_END_DATE
FROM CS_CUSTOMER_PRODUCT_STATUSES cs,
csi_item_instances CP, MTL_SYSTEM_ITEMS_B_KFV BK
WHERE BK.INVENTORY_ITEM_ID = CP.INVENTORY_ITEM_ID
AND CP.INSTANCE_STATUS_ID = cs.customer_product_status_id
AND CP.INSTANCE_ID = p_inv_id
AND BK.ORGANIZATION_ID = p_org_id;
SELECT 'A'
FROM CSI_SYSTEMS_B CSB
WHERE CSB.system_id = p_system_id
AND sysdate between NVL(start_date_active, sysdate) and NVL(end_date_active, sysdate);
SELECT authoring_org_id, inv_organization_id
FROM okc_k_headers_all_b
WHERE id = p_chr_id;
SELECT status
FROM HZ_PARTY_SITES -- okx_party_sites_v
WHERE party_site_id = partySiteId;
SELECT sts_code --sts.ste_code sts_code
FROM okc_k_headers_all_b
-- ,okc_statuses_b sts
WHERE id = l_chr_id;
SELECT
name, nvl(merchant_ref, 0)
FROM AR_RECEIPT_METHODS
WHERE RECEIPT_METHOD_ID = pay_id
AND SYSDATE >= NVL(START_DATE, SYSDATE)
AND SYSDATE <= NVL(END_DATE, SYSDATE)
AND PAYMENT_TYPE_CODE = 'CREDIT_CARD';
| 4. Update OKS header with payment information.
|
| In Parameters: p_chr_id the contract id
| Out Parameters: x_return_status standard return status
|
+============================================================================*/
PROCEDURE Check_Authorize_Payment
(x_return_status OUT NOCOPY VARCHAR2,
p_chr_id IN NUMBER
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Check_Authorize_Payment';
SELECT s.id,
s.object_version_number,
s.trxn_extension_id,
s.payment_type,
NVL(c.org_id, c.authoring_org_id) org_id,
c.currency_code,
c.bill_to_site_use_id
FROM oks_k_headers_b s,
okc_k_headers_all_b c
WHERE s.chr_id = c.id
AND c.id = p_chr_id;
SELECT oks.id,
oks.object_version_number,
oks.trxn_extension_id,
oks.payment_type,
okc.bill_to_site_use_id
FROM oks_k_lines_b oks,
okc_k_lines_b okc
WHERE oks.dnz_chr_id = p_chr_id
AND oks.cle_id = okc.id
AND okc.date_cancelled IS NULL --4735326
AND oks.trxn_extension_id IS NOT NULL; --process only lines with credit cards
SELECT accts.party_id,
sites.cust_account_id
FROM hz_cust_site_uses_all site_uses,
hz_cust_acct_sites_all sites,
hz_cust_accounts accts
WHERE site_uses.site_use_id = p_site_use_id
AND site_uses.site_use_code = p_site_use_code
AND site_uses.cust_acct_site_id = sites.cust_acct_site_id
AND sites.cust_account_id = accts.cust_account_id;
FND_MSG_PUB.delete_msg(x_msg_count);
FND_MSG_PUB.delete_msg(x_msg_count);
FND_MSG_PUB.delete_msg(x_msg_count);
FND_MSG_PUB.delete_msg(x_msg_count);
UPDATE oks_k_headers_b
SET cc_auth_code = l_auth_result.auth_code
WHERE id = l_oks_header_rec.id;
FND_MSG_PUB.delete_msg(x_msg_count);
FND_MSG_PUB.delete_msg(x_msg_count);
UPDATE oks_k_lines_b
SET cc_auth_code = l_auth_result.auth_code
WHERE id = l_oks_lines_rec.id;
SELECT k_hdr.currency_code
FROM okc_k_headers_all_b k_hdr
WHERE k_hdr.id = p_chr_id ;
SELECT lines.id, lines.lse_id lse_id, lines.cle_id,
lines.start_date, lines.end_date, lines.line_number, date_terminated,
lines.price_negotiated
FROM okc_k_lines_b lines
WHERE lines.dnz_chr_id = p_hdr_id
AND lines.cle_id IS NULL
AND lines.lse_id IN (1, 12, 19, 46)
AND lines.date_terminated IS NULL -- added by mkhayer 11/21/2002.
AND lines.date_cancelled IS NULL --Changes [llc]
ORDER BY lines.id;
SELECT lines.id, lines.lse_id lse_id, lines.cle_id,
lines.start_date, lines.end_date, lines.line_number, date_terminated,
lines.price_negotiated, oks.credit_amount, oks.suppressed_credit, oks.override_amount
FROM okc_k_lines_b lines , oks_k_lines_b oks
WHERE lines.dnz_chr_id = p_hdr_id
AND lines.cle_id IS NULL
AND lines.lse_id IN (1, 12, 19, 46)
AND lines.date_terminated IS NULL -- added by mkhayer 11/21/2002.
AND lines.date_cancelled IS NULL --Changes [llc]
AND oks.cle_id = lines.id
ORDER BY lines.id;
SELECT lines.id, lines.lse_id lse_id, lines.cle_id,
lines.start_date, lines.end_date, lines.price_negotiated, lines.line_number, date_terminated
FROM okc_k_lines_b lines
WHERE lines.dnz_chr_id = p_hdr_id
AND lines.cle_id = p_cle_id
AND lines.lse_id IN (7, 8, 9, 10, 11, 13, 18, 25, 35)
AND lines.date_terminated IS NULL -- uncommented by mkhayer
AND lines.date_cancelled IS NULL; --Changes [llc]
SELECT id,
uom_code,
SEQUENCE_NO,
start_date,
level_periods,
uom_per_period,
advance_periods,
level_amount --rule_information6
FROM oks_stream_levels_b --okc_rules_b
WHERE dnz_chr_id = p_hdr_id
AND cle_id = p_cle_id;
SELECT date_start, date_transaction, date_to_interface,
amount, date_completed
FROM oks_level_elements
WHERE rul_id = p_id -- is the id in oks_level_elements
AND date_completed IS NULL
ORDER BY date_start; -- Added for bug# 2517147
SELECT COUNT(rul_id) lvl_count, SUM(amount) lvl_amt
FROM oks_level_elements
WHERE rul_id = p_id -- is the id in oks_level_elements
GROUP BY rul_id;
SELECT BILLING_SCHEDULE_TYPE --rule_information1
FROM oks_k_lines_b --okc_rules_b
WHERE dnz_chr_id = p_hdr_id
AND cle_id = p_cle_id;
SELECT SUM(amount) billed_amt
FROM oks_level_elements
WHERE rul_id = p_id
AND date_completed IS NOT NULL;
SELECT price_negotiated
FROM okc_k_lines_b
WHERE id = l_line_id
AND lse_id IN (7, 8, 9, 10, 11, 18, 25, 35, 46);
SELECT SUM(amount)
FROM oks_bill_sub_lines
WHERE cle_id = l_sub_line_id
AND bcl_id IN (SELECT id
FROM oks_bill_cont_lines
WHERE bill_action = 'RI');
SELECT SUM(amount)
FROM oks_bill_cont_lines
WHERE cle_id = l_line_id AND
bill_action = 'RI';
SELECT MAX(end_date)
FROM oks_stream_levels_b WHERE cle_id = l_cle_id;
SELECT MIN(start_date)
FROM oks_stream_levels_b WHERE cle_id = l_cle_id;
SELECT amount FROM oks_level_elements
WHERE nvl(amount, 0) < 0 AND
cle_id = l_cle_id;
SELECT COUNT(id)
INTO l_top_line_SLL
FROM oks_stream_levels_b -- okc_rules_b
WHERE dnz_chr_id = p_chr_id
AND cle_id = top_line_grp_rec.id;
SELECT COUNT(id)
INTO l_bs_rec
FROM oks_level_elements
WHERE cle_id = top_line_grp_rec.id; -- rul_id = rules_rec.id;
SELECT COUNT(id)
INTO l_sub_line_SLL
FROM oks_stream_levels_b
WHERE dnz_chr_id = p_chr_id
AND cle_id = line_grp_rec.id;
SELECT COUNT(id)
INTO l_bs_rec
FROM oks_level_elements
WHERE cle_id = line_grp_rec.id; -- rul_id = rules_rec.id;
SELECT authoring_org_id
FROM okc_k_headers_all_b
WHERE id = p_chr_id;
SELECT cle.id, sts.ste_code sts_code
FROM okc_k_lines_b cle,
okc_statuses_b sts
WHERE cle.dnz_chr_id = p_chr_id
AND sts.code = cle.sts_code
AND cle.lse_id IN (1, 19)
AND cle.date_cancelled IS NULL --Changes [llc]
;
SELECT CUST_ACCT_ID
FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id
AND id = l_service_id;
SELECT object1_id1
FROM okc_k_items_v
WHERE cle_id = p_cle_id ;
SELECT id, start_date, lse_id
FROM okc_k_lines_b
WHERE cle_id = l_service_id
AND lse_id IN (9, 25, 7) -- 7 added for bug#2430496
AND date_cancelled IS NULL --Changes [llc]
;
SELECT object1_id1
FROM okc_k_items_v
WHERE cle_id = p_cle_id ;
SELECT inventory_item_id
FROM csi_item_instances
WHERE instance_id = p_cp_id;
SELECT P.PARTY_NAME name, CA.PARTY_ID party_id
FROM HZ_CUST_ACCOUNTS CA, HZ_PARTIES P
WHERE CA.PARTY_ID = P.PARTY_ID AND
CA.CUST_ACCOUNT_ID = l_cust_acct_id;
SELECT object1_id1 party_id
FROM okc_k_party_roles_v
WHERE rle_code NOT IN ('VENDOR', 'MERCHANT')
AND object1_id1 = cust_id
AND dnz_chr_id = p_chr_id
AND chr_id = p_chr_id;
SELECT AUTHORING_ORG_ID
FROM okc_k_headers_all_b
WHERE id = p_chr_id;
SELECT object1_id1 party_id
FROM okc_k_party_roles_v
WHERE rle_code NOT IN ('VENDOR', 'MERCHANT')
AND dnz_chr_id = p_chr_id
AND chr_id = p_chr_id;
SELECT A.CUST_ACCOUNT_ID, A.RELATED_CUST_ACCOUNT_ID
FROM HZ_CUST_ACCT_RELATE_ALL A,
HZ_CUST_ACCOUNTS CA
WHERE CA.CUST_ACCOUNT_ID = A.CUST_ACCOUNT_ID
AND CA.PARTY_ID = custPartyId
AND A.RELATED_CUST_ACCOUNT_ID = relatedCustAccId
AND CA.STATUS = 'A'
AND A.status = 'A'
AND A.org_id = orgId;
SELECT cust_acct_id, id, ship_to_site_use_id, line_number
FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id
AND cust_acct_id IS NOT NULL
AND chr_id IS NOT NULL
AND date_cancelled IS NULL --Changes [llc]
AND (date_terminated IS NULL OR date_terminated > SYSDATE); -- Bug 4915718
SELECT CA.cust_account_id
FROM HZ_CUST_SITE_USES_ALL CS, HZ_CUST_ACCT_SITES_ALL CA
WHERE CS.SITE_USE_ID = l_site_use_id
AND CS.site_use_code = 'SHIP_TO'
AND CS.cust_acct_site_id = CA.cust_acct_site_id;
SELECT status
FROM hz_cust_accounts
WHERE cust_account_id = l_cust_acct_id
AND status = 'A';
SELECT object1_id1 party_id, rle_code
FROM okc_k_party_roles_v
WHERE rle_code NOT IN ('VENDOR', 'MERCHANT')
AND dnz_chr_id = p_chr_id
AND chr_id = p_chr_id;
SELECT CA.CUST_ACCOUNT_ID,
ca.status,
decode(CA.ACCOUNT_NAME, NULL, P.PARTY_NAME, CA.Account_NAME) NAME
FROM HZ_CUST_ACCOUNTS CA, HZ_PARTIES P
WHERE CA.PARTY_ID = P.PARTY_ID
AND P.PARTY_ID = l_party_id;
SELECT bill_to_site_use_id, ship_to_site_use_id
FROM okc_k_headers_all_b
WHERE id = p_chr_id ;
SELECT
c.cust_account_id CUST_ID1,
p.party_id party_id
FROM OKX_CUST_SITE_USES_V a,
hz_cust_accounts c,
hz_parties p
WHERE a.id1 = p_bill_to_site_use_id
AND c.cust_account_id = a.cust_account_id
AND a.site_use_code = 'BILL_TO'
AND p.party_id = c.party_id;
SELECT
c.cust_account_id CUST_ID1,
p.party_id party_id
FROM OKX_CUST_SITE_USES_V a,
hz_cust_accounts c,
hz_parties p
WHERE a.id1 = p_ship_to_site_use_id
AND c.cust_account_id = a.cust_account_id
AND a.site_use_code = 'SHIP_TO'
AND p.party_id = c.party_id;
SELECT CA.CUST_ACCOUNT_ID, ca.status,
decode(CA.ACCOUNT_NAME, NULL, P.PARTY_NAME, CA.Account_NAME) NAME
FROM HZ_CUST_ACCOUNTS CA, HZ_PARTIES P
WHERE CA.PARTY_ID = P.PARTY_ID
AND P.PARTY_ID = l_party_id
AND CA.CUST_ACCOUNT_ID = l_cust_acct_id;
SELECT rle_code
FROM okc_k_party_roles_b
WHERE rle_code NOT IN ('VENDOR', 'MERCHANT')
AND dnz_chr_id = p_chr_id
AND chr_id = p_chr_id
AND object1_id1 = p_party_id;
SELECT rle_code, B.Status, B.PARTY_STATUS FROM okc_k_party_roles_v A,
(SELECT CA.PARTY_ID, A.Status, CA.STATUS PARTY_STATUS FROM HZ_CUST_ACCT_RELATE_ALL A,
HZ_CUST_ACCOUNTS CA WHERE CA.CUST_ACCOUNT_ID = A.CUST_ACCOUNT_ID
AND A.RELATED_CUST_ACCOUNT_ID = p_id
AND A.org_id = p_org_id) B
WHERE A.rle_code NOT IN ('VENDOR', 'MERCHANT')
AND A.dnz_chr_id = p_chr_id
AND A.chr_id = p_chr_id
AND A.OBJECT1_ID1 = B.Party_ID ;
SELECT CA.CUST_ACCOUNT_ID, ca.status,
decode(CA.ACCOUNT_NAME, NULL, P.PARTY_NAME, CA.Account_NAME) NAME
FROM HZ_CUST_ACCOUNTS CA, HZ_PARTIES P
WHERE CA.PARTY_ID = P.PARTY_ID
AND P.PARTY_ID = l_party_id
AND CA.STATUS = 'A';
SELECT AUTHORING_ORG_ID
FROM okc_k_headers_all_b
WHERE id = p_chr_id;
SELECT id subline_id, start_date, end_date, line_number subline_number, cle_id
FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id
AND cle_id IS NOT NULL
AND lse_id = 9
AND date_cancelled IS NULL ; --Changes [llc]
SELECT a.object1_id1
FROM okc_k_items a, oks_subscr_header_b b
WHERE a.cle_id = subline_id
AND b.instance_id = a.object1_id1;
SELECT b.dnz_chr_id subscr_chr_id, b.cle_id subscr_line_id
FROM okc_k_headers_all_b a, oks_subscr_header_b b
WHERE b.instance_id = instId AND b.dnz_chr_id = a.id AND
(a.id = p_chr_id OR (a.id <> p_chr_id AND a.sts_code IN ('ACTIVE', 'SIGNED')) );
SELECT sts_code
FROM okc_k_lines_b
WHERE id = subscr_line_Id AND (cpStartDate BETWEEN start_date AND end_date)
AND (cpEndDate BETWEEN start_date AND end_date) AND lse_id = 46 AND cle_id IS NULL ;
SELECT id ,date_terminated -- Added "date_terminated" for Bug 5702660
FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id
AND lse_id = 46
AND date_cancelled IS NULL --Changes [llc]
;
SELECT a.id
FROM oks_subscr_header_b a
WHERE a.cle_id = cleId AND a.dnz_chr_id = p_chr_id
AND a.fulfillment_channel = 'OM';
SELECT b.id
FROM oks_subscr_elements b
WHERE b.dnz_cle_id = cleId AND b.dnz_chr_id = p_chr_id
AND b.osh_id = oshId;
SELECT id
FROM okc_k_lines_b
WHERE dnz_chr_id = p_chr_id
AND lse_id = 46
AND date_cancelled IS NULL --Changes [llc]
;
SELECT SHIPPABLE_ITEM_FLAG
FROM mtl_system_items a, okc_k_items b
WHERE b.cle_id = cleId AND a.SHIPPABLE_ITEM_FLAG = 'Y'
AND a.INVENTORY_ITEM_ID = b.object1_id1
AND a.ORGANIZATION_ID = b.object1_id2;
SELECT SHIP_TO_SITE_USE_ID
FROM OKC_K_LINES_B
WHERE id = cleId;
SELECT a.location_id
FROM Okx_cust_site_uses_v a
WHERE a.id1 = p_id
AND a.site_use_code = Code;
SELECT id
FROM okc_k_lines_b
WHERE lse_id IN (9, 25)
AND dnz_chr_id = l_chr_id
AND date_cancelled IS NULL --Changes [llc]
;
SELECT object1_id1, number_of_items, uom_code
FROM okc_k_items
WHERE cle_id = l_cp_line_id;
SELECT b.instance_id
FROM oks_subscr_header_b b
WHERE b.instance_id = l_inst_id;
SELECT instance_id, quantity, unit_of_measure
FROM csi_item_instances
WHERE instance_id = l_instance_id;
SELECT orig_system_id1
FROM okc_k_headers_all_b
WHERE id = l_chr_id
AND datetime_cancelled IS NULL; --Changes [llc]
SELECT b.cle_id
FROM okc_k_lines_b a, oks_k_lines_b b
WHERE a.id = b.cle_id
AND b.dnz_chr_id = l_old_chr_id
AND b.dnz_chr_id = a.dnz_chr_id
AND a.lse_id IN (12, 13)
AND b.LOCKED_PRICE_LIST_ID IS NOT NULL
AND b.LOCKED_PRICE_LIST_LINE_ID IS NOT NULL
AND a.date_cancelled IS NULL --Changes [llc]
;
SELECT id
FROM okc_k_lines_b
WHERE dnz_chr_id = l_chr_id
AND lse_id IN (12, 13)
AND orig_system_id1 = l_old_line_id;
SELECT cle_id
FROM oks_k_lines_b
WHERE dnz_chr_id = l_chr_id
AND cle_id = l_cle_id
AND LOCKED_PRICE_LIST_ID IS NOT NULL
AND LOCKED_PRICE_LIST_LINE_ID IS NOT NULL;
SELECT 1
FROM okc_operation_lines a, okc_operation_instances b, okc_class_operations c, okc_k_headers_all_b d, okc_statuses_b e
WHERE a.subject_chr_id = p_chr_id
AND c.id = b.cop_id
AND c.opn_code IN('RENEWAL', 'REN_CON')
AND a.oie_id = b.id
AND a.active_yn = 'Y'
AND a.object_chr_id = d.id
AND e.code = d.sts_code
AND e.ste_code NOT IN ('ACTIVE', 'SIGNED', 'HOLD', 'EXPIRED');
SELECT d.id target_contract_id
FROM okc_operation_lines a, okc_operation_instances b, okc_class_operations c, okc_k_headers_all_b d, okc_statuses_b e
WHERE a.object_chr_id = p_chr_id
AND c.id = b.cop_id
AND c.opn_code IN('RENEWAL', 'REN_CON')
AND a.oie_id = b.id
AND a.active_yn = 'Y'
AND a.subject_chr_id = d.id
AND e.code = d.sts_code
AND e.ste_code NOT IN ('ACTIVE', 'SIGNED', 'HOLD', 'EXPIRED');
SELECT b.id, b.line_number, s.ste_code
FROM okc_k_lines_b b, okc_statuses_b s
WHERE dnz_chr_id = p_chr_id
AND cle_id IS NULL
AND s.code = b.sts_code;
SELECT b.id, b.line_number, s.ste_code
FROM okc_k_lines_b b, okc_statuses_b s
WHERE cle_id = p_cle_id
AND s.code = b.sts_code
AND s.ste_code = 'CANCELLED';
SELECT subject_cle_id
FROM okc_operation_lines
WHERE object_cle_id = p_cle_id;
SELECT subject_cle_id
FROM okc_operation_lines a, okc_operation_instances b, okc_class_operations c, okc_k_lines_b d
WHERE a.object_cle_id = d.id
AND a.object_chr_id = d.dnz_chr_id
AND d.cle_id = p_cle_id -- should be a top line id
AND a.object_chr_id = p_chr_id
AND c.id = b.cop_id
AND c.opn_code IN('RENEWAL', 'REN_CON')
AND a.oie_id = b.id
AND a.active_yn = 'Y';
SELECT contract_number
FROM okc_k_headers_all_b
WHERE id = p_contract_id;
SELECT line_number
FROM okc_k_lines_b
WHERE id = p_cle_id;
SELECT lines2.line_number || '.' || lines1.line_number
FROM okc_k_lines_b lines1, okc_k_lines_b lines2
WHERE lines1.id = p_cle_id
AND lines1.cle_id = lines2.id;