eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.OKS_QA_DATA_INTEGRITY dependencies on OKC_K_LINES_B

Line 23: FROM OKC_K_LINES_B

19: RETURN VARCHAR2 IS
20:
21: CURSOR l_get_top_line_number_csr (p_line_id NUMBER) IS
22: SELECT line_number, cle_id
23: FROM OKC_K_LINES_B
24: WHERE id = p_line_id;
25:
26:
27: l_line_num VARCHAR2(150);

Line 145: FROM OKC_K_LINES_B

141: clev.id = p_cle_id;
142:
143: CURSOR l_get_top_line_number_csr (p_line_id NUMBER) IS
144: SELECT line_number
145: FROM OKC_K_LINES_B
146: WHERE id = p_line_id;
147:
148: CURSOR l_line_name_csr IS
149: SELECT RTRIM(RTRIM(line_number) || ', ' || RTRIM(lsev.name) || ' ' ||

Line 517: FROM okc_k_lines_b WHERE chr_id = p_chr_id

513:
514: CURSOR get_line_info(p_chr_id NUMBER) IS
515: /**
516: SELECT line_number, price_list_id, sts_code
517: FROM okc_k_lines_b WHERE chr_id = p_chr_id
518: AND lse_id IN (1, 12, 46, 19)
519: AND date_cancelled IS NULL ; --Changes [llc]
520: **/
521: --bug 5442886

Line 523: FROM okc_k_lines_b,

519: AND date_cancelled IS NULL ; --Changes [llc]
520: **/
521: --bug 5442886
522: SELECT line_number, price_list_id
523: FROM okc_k_lines_b,
524: okc_statuses_b
525: WHERE chr_id = p_chr_id
526: AND lse_id IN (1,12,46,19)
527: AND ste_code = 'ENTERED'

Line 531: TYPE chr150_tbl_type IS TABLE OF okc_k_lines_b.line_number%TYPE INDEX BY BINARY_INTEGER;

527: AND ste_code = 'ENTERED'
528: AND code = sts_code
529: AND date_cancelled IS NULL;
530:
531: TYPE chr150_tbl_type IS TABLE OF okc_k_lines_b.line_number%TYPE INDEX BY BINARY_INTEGER;
532: TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
533:
534: l_line_number_tbl chr150_tbl_type;
535: l_price_list_id_tbl num_tbl_type;

Line 1063: OKC_K_LINES_B cle,

1059: , chdr.contract_number_modifier
1060: FROM okc_k_headers_all_b chdr,
1061: OKC_K_ITEMS cim,
1062: OKC_LINE_STYLES_V lse,
1063: OKC_K_LINES_B cle,
1064: OKC_STATUSES_B sts
1065: WHERE chdr.id = cle.dnz_chr_id
1066: AND object1_id1 = p_object1_id1
1067: AND object1_id2 = p_object1_id2

Line 1259: okc_k_lines_b trgtl,

1255: srcsl.id srcsl_id
1256: FROM okc_k_headers_all_b trgh,
1257: okc_k_lines_v trgsl,
1258: okc_k_items trgi,
1259: okc_k_lines_b trgtl,
1260: okc_line_styles_v trgst,
1261: okc_k_lines_v srcsl,
1262: okc_k_items srci,
1263: okc_k_lines_b srctl,

Line 1263: okc_k_lines_b srctl,

1259: okc_k_lines_b trgtl,
1260: okc_line_styles_v trgst,
1261: okc_k_lines_v srcsl,
1262: okc_k_items srci,
1263: okc_k_lines_b srctl,
1264: okc_line_styles_v srcst,
1265: okc_statuses_b sts
1266: WHERE srcsl.dnz_chr_id = cp_chr_id
1267: AND srcsl.lse_id IN (7, 8, 9, 10, 11, 35, 13, 18, 25)

Line 1585: OKC_K_LINES_B cle

1581:
1582: CURSOR l_cva_csr (p_cle_id NUMBER) IS
1583: SELECT COUNT( * )
1584: FROM OKC_LINE_STYLES_B lse,
1585: OKC_K_LINES_B cle
1586: WHERE lse.lty_code = 'COVERAGE'
1587: AND lse.id = cle.lse_id
1588: AND cle.cle_id = p_cle_id
1589: GROUP BY cle.cle_id;

Line 1631: , okc_k_lines_b lines

1627: CURSOR l_Contact_csr(p_cle_id NUMBER) IS
1628: SELECT Contact.object1_id1
1629: FROM Okc_contacts Contact
1630: , Okc_k_party_roles_b Party
1631: , okc_k_lines_b lines
1632: WHERE Contact.cpl_id = Party.id
1633: AND party.cle_id = p_cle_id
1634: AND party.jtot_object1_code = 'OKX_PARTY'
1635: AND Contact.cro_code = 'CUST_BILLING'

Line 1642: FROM OKC_K_LINES_B

1638:
1639: -- object1_id1 in BTO
1640: CURSOR l_billto_csr(p_id NUMBER) IS
1641: SELECT BILL_TO_SITE_USE_ID
1642: FROM OKC_K_LINES_B
1643: -- Bug 4558172 --
1644: -- where cle_id = p_id;
1645: WHERE id = p_id;
1646: -- Bug 4558172 --

Line 2043: OKC_K_LINES_B cle

2039: CURSOR l_cle_csr IS
2040: /**
2041: SELECT cle.id, COUNT( * )
2042: FROM OKC_K_ITEMS cim,
2043: OKC_K_LINES_B cle
2044: WHERE cim.cle_id = cle.id
2045: AND cle.dnz_chr_id = p_chr_id
2046: AND cle.date_cancelled IS NULL --Changes [llc]
2047: GROUP BY cle.id

Line 2109: FROM OKC_K_LINES_B

2105:
2106: /***
2107: CURSOR l_get_top_line_csr IS
2108: SELECT id
2109: FROM OKC_K_LINES_B
2110: WHERE dnz_chr_id = p_chr_id
2111: AND lse_id IN (1, 19)
2112: AND date_cancelled IS NULL ; --Changes [llc]
2113:

Line 2116: FROM OKC_K_LINES_B

2112: AND date_cancelled IS NULL ; --Changes [llc]
2113:
2114: CURSOR l_get_sub_line_csr (p_cle_id NUMBER) IS
2115: SELECT id, price_unit
2116: FROM OKC_K_LINES_B
2117: WHERE dnz_chr_id = p_chr_id
2118: AND cle_id = p_cle_id
2119: AND lse_id IN (7, 8, 9, 10, 11, 18, 25, 35)
2120: AND date_cancelled IS NULL --Changes [llc]

Line 2135: FROM okc_k_lines_b rla,

2131: CURSOR l_get_line_details_csr IS
2132: SELECT /*+ ordered use_nl(rlb,ri) */
2133: rlb.id sub_line_id, rlb.price_unit,
2134: ri.uom_code
2135: FROM okc_k_lines_b rla,
2136: okc_k_lines_b rlb,
2137: okc_k_items_v ri
2138: WHERE rla.dnz_chr_id = p_chr_id
2139: AND rla.lse_id IN (1,19)

Line 2136: okc_k_lines_b rlb,

2132: SELECT /*+ ordered use_nl(rlb,ri) */
2133: rlb.id sub_line_id, rlb.price_unit,
2134: ri.uom_code
2135: FROM okc_k_lines_b rla,
2136: okc_k_lines_b rlb,
2137: okc_k_items_v ri
2138: WHERE rla.dnz_chr_id = p_chr_id
2139: AND rla.lse_id IN (1,19)
2140: AND rla.date_cancelled IS NULL

Line 2203: FROM okc_k_lines_b

2199: /*** Get customer id for all the above service lines ***/
2200: -- object1_id1 in CAN rule
2201: CURSOR l_csr_get_customer_id IS
2202: SELECT CUST_ACCT_ID -- object1_id1
2203: FROM okc_k_lines_b
2204: WHERE id = l_service_id;
2205:
2206:
2207: /*** Get service item id for all the service lines from OKC_K_ITEMS_V ***/

Line 2255: FROM okc_k_lines_b

2251:
2252: l_date_cancelled := NULL;
2253: SELECT date_cancelled
2254: INTO l_date_cancelled
2255: FROM okc_k_lines_b
2256: WHERE id = l_cle_rec.cle_id;
2257:
2258: IF l_date_cancelled IS NULL THEN
2259: OKC_API.set_message(

Line 2910: OKC_K_LINES_B CLEB,

2906: exempt_certificate_number,
2907: exempt_reason_code
2908:
2909: FROM
2910: OKC_K_LINES_B CLEB,
2911: OKS_K_LINES_B KLN
2912: WHERE
2913: CLEB.dnz_chr_id = p_chr_id AND
2914: CLEB.ID = KLN.CLE_ID AND

Line 3787: FROM okc_k_lines_b rl,

3783: -- BTO rule. Replaced okx_cust_site_uses_v with HZ_CUST_SITE_USES_ALL
3784: CURSOR check_address (line_id NUMBER, l_use_code VARCHAR2)
3785: IS
3786: SELECT CS.SITE_USE_ID, CS.STATUS, CS.CUST_ACCT_SITE_ID
3787: FROM okc_k_lines_b rl,
3788: HZ_CUST_SITE_USES_ALL CS
3789: WHERE rl.dnz_chr_id = p_chr_id
3790: AND rl.id = line_id
3791: AND CS.SITE_USE_ID = decode(l_use_code, 'BILL_TO', rl.BILL_TO_SITE_USE_ID, rl.SHIP_TO_SITE_USE_ID)

Line 3805: SELECT id, line_number FROM okc_k_lines_b

3801:
3802: /******
3803: -- checks bill to address for top lines.
3804: CURSOR line_cur IS
3805: SELECT id, line_number FROM okc_k_lines_b
3806: WHERE dnz_chr_id = p_chr_id
3807: AND chr_id = p_chr_id
3808: AND cle_id IS NULL
3809: AND lse_id IN (1, 12, 14, 19, 46)

Line 3824: FROM okc_k_lines_b rl,

3820: cs.site_use_id, cs.status site_use_status,
3821: cs.cust_acct_site_id,
3822: ca.status site_status,
3823: 'BILL_TO' use_code
3824: FROM okc_k_lines_b rl,
3825: hz_cust_site_uses_all cs,
3826: hz_cust_acct_sites_all ca
3827: WHERE rl.dnz_chr_id = p_chr_id
3828: AND rl.chr_id = p_chr_id

Line 3844: FROM okc_k_lines_b rl,

3840: cs.site_use_id, cs.status site_use_status,
3841: cs.cust_acct_site_id,
3842: ca.status site_status,
3843: 'SHIP_TO' use_code
3844: FROM okc_k_lines_b rl,
3845: hz_cust_site_uses_all cs,
3846: hz_cust_acct_sites_all ca
3847: WHERE rl.dnz_chr_id = p_chr_id
3848: AND rl.chr_id = p_chr_id

Line 3859: TYPE chr150_tbl_type IS TABLE OF okc_k_lines_b.line_number%TYPE INDEX BY BINARY_INTEGER;

3855: AND cs.site_use_code (+)= 'SHIP_TO' --l_use_code -- 'bill_to' or 'ship_to'
3856: --
3857: AND ca.cust_acct_site_id (+)= cs.cust_acct_site_id;
3858:
3859: TYPE chr150_tbl_type IS TABLE OF okc_k_lines_b.line_number%TYPE INDEX BY BINARY_INTEGER;
3860: TYPE chr7_tbl_type IS TABLE OF VARCHAR2(7) INDEX BY BINARY_INTEGER;
3861: TYPE chr1a_tbl_type IS TABLE OF hz_cust_site_uses_all.status%TYPE INDEX BY BINARY_INTEGER;
3862: TYPE chr1b_tbl_type IS TABLE OF hz_cust_acct_sites_all.status%TYPE INDEX BY BINARY_INTEGER;
3863: TYPE num_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

Line 4207: okc_k_lines_b cle,

4203: SELECT cle.id, cle.lse_id, sts.ste_code sts_code,
4204: cle.start_date, cle.end_date, cle.date_terminated,
4205: cim.jtot_object1_code, cim.object1_id1, cim.object1_id2
4206: FROM okc_k_items cim,
4207: okc_k_lines_b cle,
4208: okc_statuses_b sts
4209: WHERE cle.dnz_chr_id = p_chr_id
4210: AND cle.cle_id IS NULL
4211: AND sts.code = cle.sts_code

Line 4252: OKC_K_LINES_B cle,

4248: cle.start_date, cle.end_date, cle.date_terminated,
4249: cim.jtot_object1_code, cim.object1_id1,
4250: cim.object1_id2
4251: FROM OKC_K_ITEMS cim,
4252: OKC_K_LINES_B cle,
4253: OKC_STATUSES_B sts
4254: WHERE cim.cle_id = cle.id
4255: AND cle.cle_id = p_cle_id
4256: AND sts.code = cle.sts_code

Line 4774: okc_k_lines_b okc

4770: oks.trxn_extension_id,
4771: oks.payment_type,
4772: okc.bill_to_site_use_id
4773: FROM oks_k_lines_b oks,
4774: okc_k_lines_b okc
4775: WHERE oks.dnz_chr_id = p_chr_id
4776: AND oks.cle_id = okc.id
4777: AND okc.date_cancelled IS NULL --4735326
4778: AND oks.trxn_extension_id IS NOT NULL; --process only lines with credit cards

Line 5389: FROM okc_k_lines_b lines

5385: CURSOR top_line_grp_csr (p_hdr_id NUMBER) IS
5386: SELECT lines.id, lines.lse_id lse_id, lines.cle_id,
5387: lines.start_date, lines.end_date, lines.line_number, date_terminated,
5388: lines.price_negotiated
5389: FROM okc_k_lines_b lines
5390: WHERE lines.dnz_chr_id = p_hdr_id
5391: AND lines.cle_id IS NULL
5392: AND lines.lse_id IN (1, 12, 19, 46)
5393: AND lines.date_terminated IS NULL -- added by mkhayer 11/21/2002.

Line 5403: FROM okc_k_lines_b lines , oks_k_lines_b oks

5399: CURSOR top_line_grp_csr (p_hdr_id NUMBER) IS
5400: SELECT lines.id, lines.lse_id lse_id, lines.cle_id,
5401: lines.start_date, lines.end_date, lines.line_number, date_terminated,
5402: lines.price_negotiated, oks.credit_amount, oks.suppressed_credit, oks.override_amount
5403: FROM okc_k_lines_b lines , oks_k_lines_b oks
5404: WHERE lines.dnz_chr_id = p_hdr_id
5405: AND lines.cle_id IS NULL
5406: AND lines.lse_id IN (1, 12, 19, 46)
5407: AND lines.date_terminated IS NULL -- added by mkhayer 11/21/2002.

Line 5416: FROM okc_k_lines_b lines

5412: -- Sub Line information
5413: CURSOR line_grp_csr (p_hdr_id NUMBER, p_cle_id NUMBER) IS
5414: SELECT lines.id, lines.lse_id lse_id, lines.cle_id,
5415: lines.start_date, lines.end_date, lines.price_negotiated, lines.line_number, date_terminated
5416: FROM okc_k_lines_b lines
5417: WHERE lines.dnz_chr_id = p_hdr_id
5418: AND lines.cle_id = p_cle_id
5419: AND lines.lse_id IN (7, 8, 9, 10, 11, 13, 18, 25, 35)
5420: AND lines.date_terminated IS NULL -- uncommented by mkhayer

Line 5474: FROM okc_k_lines_b

5470: AND date_completed IS NOT NULL;
5471:
5472: CURSOR get_line_price(l_line_id NUMBER) IS
5473: SELECT price_negotiated
5474: FROM okc_k_lines_b
5475: WHERE id = l_line_id
5476: AND lse_id IN (7, 8, 9, 10, 11, 18, 25, 35, 46);
5477:
5478:

Line 6161: FROM okc_k_lines_b cle,

6157:
6158: /*** Get all service lines for given Contract header id ***/
6159: CURSOR l_csr_get_service_line_id(p_chr_id NUMBER) IS
6160: SELECT cle.id, sts.ste_code sts_code
6161: FROM okc_k_lines_b cle,
6162: okc_statuses_b sts
6163: WHERE cle.dnz_chr_id = p_chr_id
6164: AND sts.code = cle.sts_code
6165: AND cle.lse_id IN (1, 19)

Line 6173: FROM okc_k_lines_b

6169: /*** Get customer id for all the above service lines ***/
6170: -- object1_id1 of CAN rule
6171: CURSOR l_csr_get_customer_id(p_chr_id NUMBER, l_service_id NUMBER) IS
6172: SELECT CUST_ACCT_ID
6173: FROM okc_k_lines_b
6174: WHERE dnz_chr_id = p_chr_id
6175: AND id = l_service_id;
6176:
6177:

Line 6189: FROM okc_k_lines_b

6185:
6186: /*** Get all product lines and item lines for each service line ***/
6187: CURSOR l_csr_get_product_line_id IS
6188: SELECT id, start_date, lse_id
6189: FROM okc_k_lines_b
6190: WHERE cle_id = l_service_id
6191: AND lse_id IN (9, 25, 7) -- 7 added for bug#2430496
6192: AND date_cancelled IS NULL --Changes [llc]
6193: ;

Line 6552: FROM okc_k_lines_b

6548: -- lines that have been terminated (with termination date less than sysdate)
6549:
6550: CURSOR get_cust_acct_lines(p_chr_id NUMBER) IS
6551: SELECT cust_acct_id, id, ship_to_site_use_id, line_number
6552: FROM okc_k_lines_b
6553: WHERE dnz_chr_id = p_chr_id
6554: AND cust_acct_id IS NOT NULL
6555: AND chr_id IS NOT NULL
6556: AND date_cancelled IS NULL --Changes [llc]

Line 7095: FROM okc_k_lines_b

7091: ) IS
7092: -- Get sublines with covered products
7093: CURSOR get_cp_lines IS
7094: SELECT id subline_id, start_date, end_date, line_number subline_number, cle_id
7095: FROM okc_k_lines_b
7096: WHERE dnz_chr_id = p_chr_id
7097: AND cle_id IS NOT NULL
7098: AND lse_id = 9
7099: AND date_cancelled IS NULL ; --Changes [llc]

Line 7120: FROM okc_k_lines_b

7116:
7117: -- See if the cp dates fall within the the subscription line start date, end date
7118: CURSOR check_effectivity(subscr_line_Id NUMBER, cpStartDate DATE, cpEndDate DATE) IS
7119: SELECT sts_code
7120: FROM okc_k_lines_b
7121: WHERE id = subscr_line_Id AND (cpStartDate BETWEEN start_date AND end_date)
7122: AND (cpEndDate BETWEEN start_date AND end_date) AND lse_id = 46 AND cle_id IS NULL ;
7123:
7124: l_inst_id NUMBER;

Line 7198: FROM okc_k_lines_b

7194: x_return_status OUT NOCOPY VARCHAR2) IS
7195:
7196: CURSOR get_subscr_line IS
7197: SELECT id ,date_terminated -- Added "date_terminated" for Bug 5702660
7198: FROM okc_k_lines_b
7199: WHERE dnz_chr_id = p_chr_id
7200: AND lse_id = 46
7201: AND date_cancelled IS NULL --Changes [llc]
7202: ;

Line 7284: FROM okc_k_lines_b

7280: x_return_status OUT NOCOPY VARCHAR2) IS
7281:
7282: CURSOR get_subscr_lines IS
7283: SELECT id
7284: FROM okc_k_lines_b
7285: WHERE dnz_chr_id = p_chr_id
7286: AND lse_id = 46
7287: AND date_cancelled IS NULL --Changes [llc]
7288: ;

Line 7303: FROM OKC_K_LINES_B

7299: -- Get the ship to rule for the shipable subscription line.
7300: -- OBJECT1_ID1 of STO for lines only
7301: CURSOR get_ship_to_rule(cleId NUMBER) IS
7302: SELECT SHIP_TO_SITE_USE_ID
7303: FROM OKC_K_LINES_B
7304: WHERE id = cleId;
7305:
7306:
7307: -- Get the ship to address for shipable subscription line.

Line 7399: FROM okc_k_lines_b

7395: x_return_status OUT NOCOPY VARCHAR2) IS
7396:
7397: CURSOR Get_Covered_Prod(l_chr_id NUMBER) IS
7398: SELECT id
7399: FROM okc_k_lines_b
7400: WHERE lse_id IN (9, 25)
7401: AND dnz_chr_id = l_chr_id
7402: AND date_cancelled IS NULL --Changes [llc]
7403: ;

Line 7591: FROM okc_k_lines_b a, oks_k_lines_b b

7587:
7588: -- Gets old line id's that have a lock
7589: CURSOR get_old_line_id(l_old_chr_id NUMBER) IS
7590: SELECT b.cle_id
7591: FROM okc_k_lines_b a, oks_k_lines_b b
7592: WHERE a.id = b.cle_id
7593: AND b.dnz_chr_id = l_old_chr_id
7594: AND b.dnz_chr_id = a.dnz_chr_id
7595: AND a.lse_id IN (12, 13)

Line 7604: FROM okc_k_lines_b

7600: --and b.prorate is not null; -- prorate is not mandatory
7601:
7602: CURSOR get_new_line_id(l_chr_id NUMBER, l_old_line_id NUMBER) IS
7603: SELECT id
7604: FROM okc_k_lines_b
7605: WHERE dnz_chr_id = l_chr_id
7606: AND lse_id IN (12, 13)
7607: AND orig_system_id1 = l_old_line_id;
7608:

Line 7797: FROM okc_k_lines_b b, okc_statuses_b s

7793: AND e.ste_code NOT IN ('ACTIVE', 'SIGNED', 'HOLD', 'EXPIRED');
7794:
7795: CURSOR cur_lines_status IS
7796: SELECT b.id, b.line_number, s.ste_code
7797: FROM okc_k_lines_b b, okc_statuses_b s
7798: WHERE dnz_chr_id = p_chr_id
7799: AND cle_id IS NULL
7800: AND s.code = b.sts_code;
7801:

Line 7805: FROM okc_k_lines_b b, okc_statuses_b s

7801:
7802:
7803: CURSOR cur_sublines_status(p_cle_id NUMBER) IS
7804: SELECT b.id, b.line_number, s.ste_code
7805: FROM okc_k_lines_b b, okc_statuses_b s
7806: WHERE cle_id = p_cle_id
7807: AND s.code = b.sts_code
7808: AND s.ste_code = 'CANCELLED';
7809:

Line 7819: FROM okc_operation_lines a, okc_operation_instances b, okc_class_operations c, okc_k_lines_b d

7815:
7816:
7817: CURSOR cur_is_topline_renewed (p_cle_id NUMBER) IS
7818: SELECT subject_cle_id
7819: FROM okc_operation_lines a, okc_operation_instances b, okc_class_operations c, okc_k_lines_b d
7820: WHERE a.object_cle_id = d.id
7821: AND a.object_chr_id = d.dnz_chr_id
7822: AND d.cle_id = p_cle_id -- should be a top line id
7823: AND a.object_chr_id = p_chr_id

Line 7837: FROM okc_k_lines_b

7833: WHERE id = p_contract_id;
7834:
7835: CURSOR cur_target_line_number (p_cle_id NUMBER) IS
7836: SELECT line_number
7837: FROM okc_k_lines_b
7838: WHERE id = p_cle_id;
7839:
7840: CURSOR cur_target_subline_number (p_cle_id NUMBER) IS
7841: SELECT lines2.line_number || '.' || lines1.line_number

Line 7842: FROM okc_k_lines_b lines1, okc_k_lines_b lines2

7838: WHERE id = p_cle_id;
7839:
7840: CURSOR cur_target_subline_number (p_cle_id NUMBER) IS
7841: SELECT lines2.line_number || '.' || lines1.line_number
7842: FROM okc_k_lines_b lines1, okc_k_lines_b lines2
7843: WHERE lines1.id = p_cle_id
7844: AND lines1.cle_id = lines2.id;
7845:
7846: