[Home] [Help]
208: -- Called By:
209: ------------------------------------------------------------------------------
210: PROCEDURE get_bill_to(
211: x_return_status OUT NOCOPY VARCHAR2,
212: p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
213: x_bill_to_id OUT NOCOPY OKC_K_HEADERS_B.BILL_TO_SITE_USE_ID%TYPE
214: ) IS
215:
216: l_proc_name VARCHAR2(35) := 'GET_BILL_TO';
209: ------------------------------------------------------------------------------
210: PROCEDURE get_bill_to(
211: x_return_status OUT NOCOPY VARCHAR2,
212: p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
213: x_bill_to_id OUT NOCOPY OKC_K_HEADERS_B.BILL_TO_SITE_USE_ID%TYPE
214: ) IS
215:
216: l_proc_name VARCHAR2(35) := 'GET_BILL_TO';
217:
214: ) IS
215:
216: l_proc_name VARCHAR2(35) := 'GET_BILL_TO';
217:
218: CURSOR bill_to_csr (p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
219: SELECT bill_to_site_use_id
220: FROM okc_k_headers_b
221: WHERE id = p_chr_id;
222:
216: l_proc_name VARCHAR2(35) := 'GET_BILL_TO';
217:
218: CURSOR bill_to_csr (p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
219: SELECT bill_to_site_use_id
220: FROM okc_k_headers_b
221: WHERE id = p_chr_id;
222:
223: bill_to_failed EXCEPTION;
224:
262: -- Called By:
263: ------------------------------------------------------------------------------
264: PROCEDURE get_cust_account(
265: x_return_status OUT NOCOPY VARCHAR2,
266: p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
267: x_cust_acc_id OUT NOCOPY OKC_K_HEADERS_B.CUST_ACCT_ID%TYPE
268: ) IS
269:
270: l_proc_name VARCHAR2(35) := 'GET_CUST_ACCOUNT';
263: ------------------------------------------------------------------------------
264: PROCEDURE get_cust_account(
265: x_return_status OUT NOCOPY VARCHAR2,
266: p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
267: x_cust_acc_id OUT NOCOPY OKC_K_HEADERS_B.CUST_ACCT_ID%TYPE
268: ) IS
269:
270: l_proc_name VARCHAR2(35) := 'GET_CUST_ACCOUNT';
271:
268: ) IS
269:
270: l_proc_name VARCHAR2(35) := 'GET_CUST_ACCOUNT';
271:
272: CURSOR cust_acc_csr (p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
273: SELECT cust_acct_id
274: FROM okc_k_headers_b
275: WHERE id = p_chr_id;
276:
270: l_proc_name VARCHAR2(35) := 'GET_CUST_ACCOUNT';
271:
272: CURSOR cust_acc_csr (p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
273: SELECT cust_acct_id
274: FROM okc_k_headers_b
275: WHERE id = p_chr_id;
276:
277: cust_acc_failed EXCEPTION;
278:
350: l_inactive_assets VARCHAR2(1) DEFAULT 'N';
351: --cursor to check if there are any lines on the contract
352: CURSOR l_lines_exist_csr(chrid NUMBER) IS
353: SELECT 'Y'
354: FROM okc_k_headers_b chrb
355: WHERE chrb.id = chrid
356: AND EXISTS (SELECT '1'
357: FROM okc_k_lines_b cleb
358: WHERE cleb.chr_id = chrb.id
363: --Bug# 16681373: Added cursor to check for Mass rebook in progress
364: CURSOR l_chk_mass_rbk_csr(p_chr_id IN NUMBER)
365: IS
366: SELECT 'Y'
367: FROM okc_k_headers_b chrb,
368: okl_trx_contracts ktrx
369: WHERE chrb.id = p_chr_id
370: AND ktrx.khr_id = chrb.id
371: AND ktrx.tsu_code = 'ENTERED'
457:
458: l_oksrl_rec l_oksrl_csr%ROWTYPE;
459: l_hdrrl_rec l_hdrrl_csr%ROWTYPE;
460:
461: l_okl_bill_to_id OKC_K_HEADERS_B.BILL_TO_SITE_USE_ID%TYPE;
462: l_oks_bill_to_id OKC_K_HEADERS_B.BILL_TO_SITE_USE_ID%TYPE;
463:
464: BEGIN
465:
458: l_oksrl_rec l_oksrl_csr%ROWTYPE;
459: l_hdrrl_rec l_hdrrl_csr%ROWTYPE;
460:
461: l_okl_bill_to_id OKC_K_HEADERS_B.BILL_TO_SITE_USE_ID%TYPE;
462: l_oks_bill_to_id OKC_K_HEADERS_B.BILL_TO_SITE_USE_ID%TYPE;
463:
464: BEGIN
465:
466: -- initialize return status
1311: -- Bug 5114815
1312: CURSOR contract_start_date_csr(p_id NUMBER) IS
1313: SELECT start_date,
1314: end_date
1315: FROM okc_k_headers_b
1316: WHERE id = p_id;
1317: l_contract_start_date DATE;
1318:
1319: l_hdr l_hdr_csr%ROWTYPE;
1512: l_pmnt_lns_in_hdr_rec2 l_pmnt_lns_in_hdr2%ROWTYPE;
1513:
1514: CURSOR cust_print_lead_days_csr(p_id NUMBER) IS
1515: SELECT term.printing_lead_days
1516: FROM okc_k_headers_b khr
1517: ,hz_customer_profiles cp
1518: ,ra_terms_b term
1519: WHERE khr.id = p_id
1520: AND khr.bill_to_site_use_id = cp.site_use_id
1557: --Bug# 13146882
1558: CURSOR l_rbk_csr(p_chr_id NUMBER)
1559: IS
1560: SELECT ktrx.date_transaction_occurred
1561: FROM okc_k_headers_b chrb,
1562: okl_trx_contracts ktrx
1563: WHERE chrb.ID = p_chr_id
1564: AND ktrx.khr_id = chrb.id
1565: AND ktrx.tsu_code = 'ENTERED'
2706: khr.syndicatable_yn,
2707: khr.DEAL_TYPE,
2708: khr.term_duration term,
2709: NVL(pdt.reporting_pdt_id, -1) report_pdt_id
2710: FROM OKC_K_HEADERS_B CHR,
2711: OKL_K_HEADERS khr,
2712: OKL_PRODUCTS_V pdt
2713: WHERE CHR.id = chrid
2714: AND CHR.id = khr.id
2718: l_hdr_rec1 l_hdr_csr1%ROWTYPE;
2719: lx_return_status VARCHAR2(1);
2720:
2721: PROCEDURE l_formula_processing(x_return_status OUT NOCOPY VARCHAR2,
2722: p_chr_id IN OKC_K_HEADERS_B.ID%TYPE) IS
2723: BEGIN
2724: -- initialize return status
2725: x_return_status := Okl_Api.G_RET_STS_SUCCESS;
2726: ind := 0;
3336: vDtls.PAYMENT_FREQUENCY,
3337: chr.END_DATE CONTRACT_END_DATE
3338: FROM okl_party_payment_hdr vHdr,
3339: okl_party_payment_dtls vDtls,
3340: okc_k_headers_b chr
3341: WHERE vDtls.payment_hdr_id = vHdr.id
3342: AND vHdr.CLE_ID = cleId
3343: AND vHdr.DNZ_CHR_ID = khrId
3344: AND vHdr.PASSTHRU_TERM = 'BASE'
3430:
3431:
3432: CURSOR con_type_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
3433: SELECT 'Y'
3434: FROM okc_k_headers_b
3435: WHERE orig_system_source_code = 'OKL_REBOOK'
3436: AND id = p_chr_id;
3437:
3438: --------------
3437:
3438: --------------
3439: --Bug# 4350255
3440: --------------
3441: CURSOR pth_csr (p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
3442: SELECT line.id,
3443: DECODE(style.lty_code,'SOLD_SERVICE','SERVICE',style.lty_code) line_type,
3444: NVL(line.name,line.item_description) name,
3445: line.fee_type,
4509: p_chr_id IN NUMBER
4510: ) IS
4511:
4512: --Bug#3877032
4513: CURSOR serial_csr (p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
4514: SELECT top.name,
4515: top.id top_id,
4516: item.id item_id,
4517: kitem.object1_id1,
4787: AND l.location_id = site.location_id;
4788:
4789: CURSOR header_details_csr (p_chr_id NUMBER) IS
4790: SELECT h.start_date, mtl.master_organization_id
4791: FROM okc_k_headers_b h,
4792: mtl_parameters mtl
4793: WHERE h.id = p_chr_id
4794: AND mtl.organization_id = h.inv_organization_id;
4795:
4803: l_party_site_id hz_party_site_uses.party_site_id%TYPE;
4804: l_asset_num okc_k_lines_tl.name%TYPE;
4805: l_okl_location_name VARCHAR2(160);
4806: l_ib_location_name VARCHAR2(160);
4807: l_contract_start_date okc_k_headers_b.start_date%TYPE;
4808: l_inv_organization_id okc_k_headers_b.inv_organization_id%TYPE;
4809:
4810:
4811: BEGIN
4804: l_asset_num okc_k_lines_tl.name%TYPE;
4805: l_okl_location_name VARCHAR2(160);
4806: l_ib_location_name VARCHAR2(160);
4807: l_contract_start_date okc_k_headers_b.start_date%TYPE;
4808: l_inv_organization_id okc_k_headers_b.inv_organization_id%TYPE;
4809:
4810:
4811: BEGIN
4812:
5042: CURSOR con_type_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
5043: SELECT NVL(okc.orig_system_source_code,'X') orig_system_source_code,
5044: NVL(okl.deal_type,'X') deal_type
5045: --FROM okl_k_headers_full_v
5046: FROM okc_k_headers_b okc,
5047: okl_k_headers okl
5048: WHERE okc.id = p_chr_id
5049: AND okc.id = okl.id;
5050:
5134:
5135: l_strm_profile VARCHAR2(50);
5136: lx_return_status VARCHAR2(1);
5137:
5138: CURSOR c_fin_fee (p_khr_id OKC_K_HEADERS_B.ID%TYPE) IS
5139: SELECT cle.id,
5140: DECODE (lse.lty_code, 'FREE_FORM1', 'ASSET', 'FEE', 'FEE',
5141: 'SOLD_SERVICE','SERVICE') line_type
5142: FROM okc_k_lines_b cle,
5147: AND cle.sts_code = sts.code
5148: AND sts.ste_code NOT IN ('HOLD','EXPIRED','TERMINATED','CANCELLED')
5149: AND lse.lty_code IN ('FREE_FORM1', 'FEE', 'SOLD_SERVICE');
5150:
5151: CURSOR c_strm_sll (p_khr_id OKC_K_HEADERS_B.ID%TYPE,
5152: p_kle_id OKC_K_LINES_B.ID%TYPE) IS
5153: SELECT sll.rule_information2 start_date,
5154: SLL.rule_information3 periods,
5155: DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) mpp,
5174: AND sll.rule_information_category = 'LASLL';
5175: --ORDER BY fnd_date.canonical_to_date(sll.rule_information2);
5176:
5177: /*
5178: CURSOR c_strm_sll_ext (p_khr_id OKC_K_HEADERS_B.ID%TYPE,
5179: p_kle_id OKC_K_LINES_B.ID%TYPE) IS
5180: SELECT
5181: SUM(NVL(TO_NUMBER(SLL.rule_information3),0)) periods,
5182: DECODE(sll.object1_id1, 'M', 1, 'Q', 3, 'S', 6, 'A', 12) mpp,
5200: styt.name;
5201: */
5202:
5203: --Bug#3877032
5204: CURSOR c_strm_slh_ext (p_khr_id OKC_K_HEADERS_B.ID%TYPE,
5205: p_kle_id OKC_K_LINES_B.ID%TYPE) IS
5206: SELECT (select STYT.NAME from OKL_STRM_TYPE_B STY ,OKL_STRM_TYPE_TL STYT
5207: where STY.ID = STYT.ID AND STYT.LANGUAGE = USERENV ( 'LANG' )
5208: AND to_number(RULE.OBJECT1_ID1) = STY.ID ) STREAM_TYPE,
5234: -- cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
5235: ORDER BY sll.rule_information2 ;
5236:
5237:
5238: CURSOR contract_csr (p_khr_id OKC_K_HEADERS_B.ID%TYPE) IS
5239: SELECT start_date,
5240: end_date
5241: FROM OKC_K_HEADERS_B
5242: WHERE ID = p_khr_id;
5237:
5238: CURSOR contract_csr (p_khr_id OKC_K_HEADERS_B.ID%TYPE) IS
5239: SELECT start_date,
5240: end_date
5241: FROM OKC_K_HEADERS_B
5242: WHERE ID = p_khr_id;
5243:
5244: l_start_date OKC_K_HEADERS_B.START_DATE%TYPE;
5245: l_end_date OKC_K_HEADERS_B.END_DATE%TYPE;
5240: end_date
5241: FROM OKC_K_HEADERS_B
5242: WHERE ID = p_khr_id;
5243:
5244: l_start_date OKC_K_HEADERS_B.START_DATE%TYPE;
5245: l_end_date OKC_K_HEADERS_B.END_DATE%TYPE;
5246:
5247: l_strm_sll_start_date DATE;
5248: l_strm_sll_periods NUMBER;
5241: FROM OKC_K_HEADERS_B
5242: WHERE ID = p_khr_id;
5243:
5244: l_start_date OKC_K_HEADERS_B.START_DATE%TYPE;
5245: l_end_date OKC_K_HEADERS_B.END_DATE%TYPE;
5246:
5247: l_strm_sll_start_date DATE;
5248: l_strm_sll_periods NUMBER;
5249:
5253: l_strm_sll_ext_stream_type VARCHAR2(100);
5254: l_pmnt_end_date DATE;
5255:
5256: -- START: cklee/mvasudev,06-02-2005,Bug#4392051/okl.h 4437938
5257: CURSOR c_strm_sll_count (p_khr_id OKC_K_HEADERS_B.ID%TYPE,
5258: p_kle_id OKC_K_LINES_B.ID%TYPE) IS
5259: SELECT COUNT(sll.id)
5260: FROM okc_rules_b sll,
5261: okc_rules_b slh,
6143:
6144: /* Cursor to get the top fee line and the rollover quote
6145: for top fee line of a given contract. */
6146:
6147: CURSOR l_rq_top_fee_ln_csr ( chrID IN OKC_K_HEADERS_B.ID%TYPE ) IS
6148: SELECT kle.id, kle.qte_id, kle.amount, cleb.name
6149: FROM okc_k_lines_v cleb,
6150: okl_k_lines kle
6151: WHERE cleb.dnz_chr_id = chrID
6161:
6162: /* Cursor to get the top fee line and the rollover quote
6163: for top fee line of a given contract. */
6164:
6165: CURSOR l_rq_top_fee_ln_csr1 ( chrID IN OKC_K_HEADERS_B.ID%TYPE ) IS
6166: SELECT kle.qte_id
6167: FROM okc_k_lines_b cleb,
6168: okl_k_lines kle
6169: WHERE cleb.dnz_chr_id = chrID
6179:
6180: /* Cursor to get the rollover quote fee sub-lines (applied to assets) for a
6181: given contract. */
6182:
6183: CURSOR l_rq_sub_ln_fee_csr ( chrID IN OKC_K_HEADERS_B.ID%TYPE,
6184: feeTopLine IN OKL_K_LINES.ID%TYPE) IS
6185: SELECT kle.id, kle.amount, cleb.end_date
6186: FROM okc_k_lines_b cleb,
6187: okl_k_lines kle
6208:
6209: /* Cursor to get the rollover quote fee top/sub-line payments for a
6210: given contract. */
6211:
6212: CURSOR l_rq_fee_pmt_csr ( chrID IN OKC_K_HEADERS_B.ID%TYPE,
6213: feeLine IN OKL_K_LINES.ID%TYPE) IS
6214:
6215: SELECT sll.rule_information2 start_date,
6216: SLL.rule_information3 periods,
6237:
6238: /* Cursor to get the rollover quote fee top/sub-line payments HEADER for a
6239: given contract. */
6240:
6241: CURSOR l_rq_fee_pmtH_csr ( chrID IN OKC_K_HEADERS_B.ID%TYPE,
6242: feeLine IN OKL_K_LINES.ID%TYPE) IS
6243:
6244: SELECT slh.id
6245: FROM okc_rules_b slh,
6616:
6617: -- R12B Authoring OA Migration
6618: -- Validation of Sales Tax Financed Fee will be done
6619: -- after the Calculate Upfront Tax process
6620: CURSOR l_fn_top_fee_ln_csr ( chrID IN OKC_K_HEADERS_B.ID%TYPE ) IS
6621: SELECT kle.id, kle.amount, cleb.name
6622: FROM okc_k_lines_v cleb,
6623: okl_k_lines kle,
6624: okc_statuses_b okcsts
6633:
6634: /* Cursor to get the financed fee sub-lines (applied to assets) for a
6635: given contract. */
6636:
6637: CURSOR l_fn_sub_ln_fee_csr ( chrID IN OKC_K_HEADERS_B.ID%TYPE,
6638: feeTopLine IN OKL_K_LINES.ID%TYPE) IS
6639: SELECT kle.id, kle.amount, cleb.end_date
6640: ,cleb.start_date -- added for bug 5115701
6641: FROM okc_k_lines_b cleb,
6651:
6652: /* Cursor to get the financed fee top/sub-line payments for a
6653: given contract. */
6654:
6655: CURSOR l_fn_fee_pmt_csr ( chrID IN OKC_K_HEADERS_B.ID%TYPE,
6656: feeLine IN OKL_K_LINES.ID%TYPE) IS
6657:
6658: SELECT Fnd_Date.canonical_to_date(sll.rule_information2) start_date, -- formated for bug 5115701,
6659: SLL.rule_information3 periods,
6680:
6681: /* Cursor to get the financed fee top/sub-line payments HEADER for a
6682: given contract. */
6683:
6684: CURSOR l_fn_fee_pmtH_csr ( chrID IN OKC_K_HEADERS_B.ID%TYPE,
6685: feeLine IN OKL_K_LINES.ID%TYPE) IS
6686:
6687: SELECT slh.id
6688: FROM okc_rules_b slh,
6926:
6927: /* Cursor to if the contract start date is not in the future
6928: (less than or equal to SYSDATE). */
6929:
6930: CURSOR l_k_std_csr ( chrID OKC_K_HEADERS_B.ID%TYPE ) IS
6931: SELECT 1
6932: FROM okc_k_lines_b cleb,
6933: okl_k_lines kle,
6934: okc_k_headers_b khr
6930: CURSOR l_k_std_csr ( chrID OKC_K_HEADERS_B.ID%TYPE ) IS
6931: SELECT 1
6932: FROM okc_k_lines_b cleb,
6933: okl_k_lines kle,
6934: okc_k_headers_b khr
6935: WHERE khr.id = chrID
6936: AND cleb.dnz_chr_id = khr.id
6937: AND kle.ID = cleb.ID
6938: AND kle.fee_type = 'ROLLOVER'
7283: pdt.name
7284: ,pdt.PRODUCT_STATUS_CODE
7285: FROM okl_products_v pdt
7286: ,okl_k_headers_v khr
7287: ,okc_k_headers_b CHR
7288: WHERE 1=1
7289: AND khr.id = p_chr_id
7290: AND pdt_id = pdt.id
7291: AND khr.id = CHR.id;
7508: --cursor to check if the contract is undergoing on-line rebook
7509: cursor l_chk_rbk_csr(p_chr_id IN NUMBER) is
7510: SELECT '!',
7511: orig_system_id1
7512: FROM okc_k_headers_b CHR,
7513: okl_trx_contracts ktrx
7514: WHERE ktrx.khr_id_new = chr.id
7515: AND ktrx.tsu_code = 'ENTERED'
7516: AND ktrx.rbr_code is NOT NULL
7609: SELECT svc_cle.dnz_chr_id
7610: FROM okc_k_lines_b svc_cle,
7611: okc_k_items svc_item,
7612: okc_statuses_b sts,
7613: okc_k_headers_b svc_chr
7614: WHERE svc_item.object1_id1 = p_object1_id1
7615: AND svc_item.jtot_object1_code = p_jtot_object_code
7616: AND svc_cle.id = svc_item.cle_id
7617: AND svc_cle.dnz_chr_id = svc_item.dnz_chr_id
7626: SELECT svc_cle.dnz_chr_id
7627: FROM okc_k_lines_b svc_cle,
7628: okc_k_items svc_item,
7629: okc_statuses_b sts,
7630: okc_k_headers_b svc_chr,
7631: cs_counter_groups csg,
7632: cs_counters cc
7633: WHERE svc_item.object1_id1 = TO_CHAR(cc.counter_id)
7634: AND svc_item.jtot_object1_code = p_jtot_object_code
8066: --Bug#3877032
8067: CURSOR l_contract_name ( n VARCHAR2 ) IS
8068: SELECT 'Y'
8069: --Select count(*) cnt
8070: FROM okc_k_headers_b WHERE contract_number = n;
8071: l_cn l_contract_name%ROWTYPE;
8072:
8073:
8074: l_hdr l_hdr_csr%ROWTYPE;
8099:
8100: l_rpt_rec l_rpt_csr%ROWTYPE;
8101: l_report_tax_book VARCHAR2(256);
8102:
8103: CURSOR party_csr (p_chr_id OKC_K_HEADERS_B.ID%TYPE,
8104: p_cle_id OKC_K_LINES_B.ID%TYPE) IS
8105: SELECT object1_id1
8106: FROM okc_k_party_roles_b
8107: WHERE dnz_chr_id = p_chr_id
8116: AND vendor_id = p_vendor_id
8117: AND status = 'A'
8118: AND TRUNC(SYSDATE) >= NVL(TRUNC(start_date_active), TRUNC(SYSDATE));
8119:
8120: CURSOR line_amt_csr (p_chr_id OKC_K_HEADERS_B.ID%TYPE,
8121: p_lty_code OKC_LINE_STYLES_V.LTY_CODE%TYPE) IS
8122: SELECT line.id,
8123: line.line_number,
8124: line.amount
8289: SELECT
8290: pdt.reporting_pdt_id
8291: FROM okl_products_v pdt
8292: ,okl_k_headers_v khr
8293: ,okc_k_headers_b CHR
8294: WHERE 1=1
8295: AND khr.id = p_chr_id
8296: AND pdt_id = pdt.id
8297: AND khr.id = CHR.id;
9650: AND cust_acct_site_status = 'A';
9651: --AND NVL(ORG_ID, -99) = SYS_CONTEXT('OKC_CONTEXT','ORG_ID');
9652:
9653: --l_cust_acct okc_rules_b.object1_id1%TYPE;
9654: l_cust_acct OKC_K_HEADERS_B.CUST_ACCT_ID%TYPE;
9655:
9656: l_bill_to okc_rules_b.object1_id1%TYPE;
9657: l_bill_to_valid VARCHAR2(1) := 'N';
9658: */
10402: l_options options_tbl_type;
10403:
10404: l_hdrrl_rec l_hdrrl_csr%ROWTYPE;
10405: i NUMBER;
10406: l_okl_bill_to_id OKC_K_HEADERS_B.BILL_TO_SITE_USE_ID%TYPE;
10407:
10408: -- Bug# 8219011
10409: l_cust_acct_id NUMBER;
10410: CURSOR pymt_method_csr(p_cust_acct_id IN NUMBER,
10565: ,CHR.sts_code contract_status
10566: ,khr.deal_type deal_type
10567: FROM okl_products_v pdt
10568: ,okl_k_headers_v khr
10569: ,okc_k_headers_b CHR
10570: WHERE 1=1
10571: AND khr.id = p_contract_id
10572: AND pdt_id = pdt.id
10573: AND khr.id = CHR.id;
10643: l_diff NUMBER;
10644:
10645: CURSOR con_le_roll_qt_le_csr(p_contract_id IN NUMBER) IS
10646: SELECT 1 FROM
10647: OKC_K_HEADERS_B chr,
10648: OKL_K_HEADERS khr,
10649: OKL_K_LINES kle,
10650: OKC_K_LINES_B cle,
10651: OKL_TRX_QUOTES_B qte,
10669: ,chr.start_date start_date
10670: ,chr.orig_system_source_code orig_system_source_code
10671: FROM okl_products_v pdt
10672: ,okl_k_headers khr
10673: ,okc_k_headers_b CHR
10674: WHERE khr.id = p_chr_id
10675: AND khr.pdt_id = pdt.id
10676: AND khr.id = CHR.id;
10677:
11017: x_return_status OUT NOCOPY VARCHAR2,
11018: p_chr_id IN NUMBER
11019: ) IS
11020:
11021: CURSOR contract_csr (p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
11022: SELECT start_date
11023: FROM okc_k_headers_b
11024: WHERE id = p_chr_id;
11025:
11019: ) IS
11020:
11021: CURSOR contract_csr (p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
11022: SELECT start_date
11023: FROM okc_k_headers_b
11024: WHERE id = p_chr_id;
11025:
11026: CURSOR fee_topline_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
11027: SELECT line.id,
11186: p_chr_id IN NUMBER
11187: ) IS
11188:
11189: --Bug#3931587
11190: CURSOR pmnt_csr (p_chr_id OKC_K_HEADERS_B.ID%TYPE,
11191: p_line_type OKC_LINE_STYLES_B.LTY_CODE%TYPE) IS
11192: SELECT COUNT(1)
11193: FROM okc_rules_b rule,
11194: okc_rule_groups_b rgp,
12248: AND kle.fee_type = 'FINANCED'
12249: AND NVL(kle.fee_purpose_code,'XXX') <> 'SALESTAX';
12250:
12251: --Bug#3877032
12252: CURSOR l_strm_slh_csr (p_khr_id OKC_K_HEADERS_B.ID%TYPE,
12253: p_kle_id OKC_K_LINES_B.ID%TYPE) IS
12254: SELECT styt.name stream_type,
12255: rule.id rule_id,
12256: rgp.id rgp_id
12506:
12507: --cursor to check if the contract is undergoing on-line rebook
12508: cursor l_chk_rbk_csr(p_chr_id IN NUMBER) is
12509: SELECT '!'
12510: FROM okc_k_headers_b CHR,
12511: okl_trx_contracts ktrx
12512: WHERE ktrx.khr_id_new = chr.id
12513: AND ktrx.tsu_code = 'ENTERED'
12514: AND ktrx.rbr_code is NOT NULL
12910: --cursor to check if the contract is undergoing on-line rebook
12911: cursor l_chk_rbk_csr(p_chr_id IN NUMBER) is
12912: SELECT '!',
12913: chr.orig_system_id1
12914: FROM okc_k_headers_b CHR,
12915: okl_trx_contracts ktrx
12916: WHERE ktrx.khr_id_new = chr.id
12917: AND ktrx.tsu_code = 'ENTERED'
12918: AND ktrx.rbr_code is NOT NULL
12923: AND chr.id = p_chr_id
12924: AND chr.orig_system_source_code = 'OKL_REBOOK';
12925:
12926: l_rbk_khr VARCHAR2(1) DEFAULT '?';
12927: l_orig_chr_id okc_k_headers_b.id%TYPE;
12928:
12929: CURSOR l_fin_ast_cle_csr (p_chr_id IN NUMBER) IS
12930: SELECT cle.id,
12931: cle.orig_system_id1
13239:
13240: cursor cust_acct_dtls(p_chr_id NUMBER) is
13241: select STATUS, DESCRIPTION
13242: from OKX_CUSTOMER_ACCOUNTS_V
13243: where Id1 = (select cust_acct_id from okc_k_headers_b where id =p_chr_id);
13244:
13245: cust_acct_status varchar(1);
13246: cust_acct_desc varchar(30);
13247:
13332: SELECT
13333: pdt.name
13334: FROM okl_products_v pdt
13335: ,okl_k_headers_v khr
13336: ,okc_k_headers_b CHR
13337: WHERE 1=1
13338: AND khr.id = p_chr_id
13339: AND pdt_id = pdt.id
13340: AND khr.id = CHR.id;
13726: ) IS
13727:
13728: CURSOR l_chk_rbk_csr(p_chr_id IN NUMBER) is
13729: SELECT date_transaction_occurred
13730: FROM okc_k_headers_b chr,
13731: okl_trx_contracts ktrx
13732: WHERE ktrx.khr_id_new = chr.id
13733: AND ktrx.tsu_code = 'ENTERED'
13734: AND ktrx.rbr_code is NOT NULL
13913:
13914: CURSOR l_chk_rbk_csr(p_chr_id IN NUMBER) is
13915: SELECT date_transaction_occurred,
13916: chr.orig_system_id1 orig_chr_id
13917: FROM okc_k_headers_b chr,
13918: okl_trx_contracts ktrx
13919: WHERE ktrx.khr_id_new = chr.id
13920: AND ktrx.tsu_code = 'ENTERED'
13921: AND ktrx.rbr_code is NOT NULL
14312: ) IS
14313:
14314: CURSOR chr_csr(p_chr_id IN NUMBER) IS
14315: SELECT start_date
14316: FROM okc_k_headers_b
14317: WHERE id = p_chr_id;
14318:
14319: l_chr_rec chr_csr%ROWTYPE;
14320: