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_RENEW_CONTRACT_PVT dependencies on OKC_K_LINES_B

Line 157: bulk operations to maximize performance. Since only start_date and end_date are updated in OKC_K_LINES_B and OKC_K_HEADERS_B, TAPI is ignored. It replaces the existing OKC procedure OKC_RENEW_PVT.update_renewal_dates.

153: /* Internal Procedure
154: This is a new module that will be called for all service contracts after copying the
155: old contract. It will update the new contract header and lines start dates and end dates
156: based on renewal type for the line (Full Duration, Keep Duration, Do not renew). It uses
157: bulk operations to maximize performance. Since only start_date and end_date are updated in OKC_K_LINES_B and OKC_K_HEADERS_B, TAPI is ignored. It replaces the existing OKC procedure OKC_RENEW_PVT.update_renewal_dates.
158:
159: Parameters
160: p_chr_id : id of the renewed contract
161: p_new_start_date : header start date for the renewed contract

Line 180: lrt okc_k_lines_b.line_renewal_type_code%TYPE,

176: IS
177: TYPE line_rec IS RECORD
178: (id NUMBER,
179: cle_id NUMBER,
180: lrt okc_k_lines_b.line_renewal_type_code%TYPE,
181: old_start_date DATE,
182: old_end_date DATE,
183: new_start_date DATE,
184: new_end_date DATE);

Line 211: SELECT id from okc_k_lines_b

207:
208:
209:
210: cursor c_kep_lines(cp_chr_id IN NUMBER) IS
211: SELECT id from okc_k_lines_b
212: where dnz_chr_id = cp_chr_id and nvl(line_renewal_type_code,'X') = 'KEP';
213:
214: cursor c_lines(cp_chr_id in number) is
215: select id, cle_id, line_renewal_type_code, start_date, end_date, null, null

Line 216: from okc_k_lines_b

212: where dnz_chr_id = cp_chr_id and nvl(line_renewal_type_code,'X') = 'KEP';
213:
214: cursor c_lines(cp_chr_id in number) is
215: select id, cle_id, line_renewal_type_code, start_date, end_date, null, null
216: from okc_k_lines_b
217: start with (dnz_chr_id = cp_chr_id and cle_id is null)
218: connect by prior id = cle_id;
219:
220:

Line 311: select id from okc_k_lines_b

307: PROCEDURE DELETE_DNR_LINES
308: IS
309:
310: cursor c_dnr_lines(cp_chr_id in number) is
311: select id from okc_k_lines_b
312: where dnz_chr_id = cp_chr_id and nvl(line_renewal_type_code,'X') = 'DNR';
313:
314: l_dnr_lines_tbl num_tbl_type;
315:

Line 432: UPDATE okc_k_lines_b SET

428: close c_kep_lines;
429:
430: --no keep duration lines
431: IF (l_dummy IS NULL) THEN
432: UPDATE okc_k_lines_b SET
433: start_date = to_date(to_char(p_new_start_date, 'DD/MM/YYYY')|| to_char(start_date,'HH24:MI:SS'), 'DD/MM/YYYYHH24:MI:SS'),
434: end_date = to_date(to_char(p_new_end_date, 'DD/MM/YYYY')|| to_char(end_date,'HH24:MI:SS'), 'DD/MM/YYYYHH24:MI:SS')
435: WHERE dnz_chr_id = p_chr_id;
436:

Line 609: update okc_k_lines_b set

605: --becuase of bug 2689096, we need to capture the time component also for each
606: --start date and end date
607: --If time component is nulled out, duration can be different and this will affect pricing
608: forall i in l_id_tbl.first..l_id_tbl.last
609: update okc_k_lines_b set
610: start_date = to_date(to_char(l_start_date_tbl(i), 'DD/MM/YYYY')|| to_char(start_date,'HH24:MI:SS'), 'DD/MM/YYYYHH24:MI:SS'),
611: end_date = to_date(to_char(l_end_date_tbl(i), 'DD/MM/YYYY')|| to_char(end_date,'HH24:MI:SS'), 'DD/MM/YYYYHH24:MI:SS')
612: where id = l_id_tbl(i);
613:

Line 697: FROM okc_k_lines_b okl, okc_operation_lines ol

693: --gets the line in the source contract for which the lines actualluy exist in the target
694: --contract
695: CURSOR c_old_lines(cp_new_chr_id IN NUMBER, cp_old_chr_id in number) IS
696: SELECT okl.id
697: FROM okc_k_lines_b okl, okc_operation_lines ol
698: WHERE ol.object_chr_id = cp_old_chr_id
699: AND ol.subject_chr_id = cp_new_chr_id
700: AND ol.object_cle_id IS NOT NULL AND ol.subject_cle_id IS NOT NULL
701: AND ol.process_flag = 'P' AND ol.active_yn = 'Y'

Line 728: UPDATE okc_k_lines_b

724:
725: EXIT WHEN (l_id_tbl.count = 0);
726:
727: FORALL i IN l_id_tbl.first..l_id_tbl.last
728: UPDATE okc_k_lines_b
729: SET date_renewed = l_date,
730: object_version_number = (object_version_number + 1)
731: WHERE id = l_id_tbl(i);
732:

Line 893: Updates okc_k_lines_b.price_list_id for 'LST' pricing method

889: END UPDATE_CONDITION_HEADERS;
890:
891: /*
892: Internal procedure for repricing a contract based on the renewal rules specified.
893: Updates okc_k_lines_b.price_list_id for 'LST' pricing method
894: Calls OKS_REPRICE_PVT.Call_Pricing_API to reprice the contract
895: Parameters
896: p_chr_id : id of the contract that need to be repriced
897: p_price_method : Pricing method, 'MAN', 'LST' or 'PCT'

Line 921: FROM okc_k_lines_b c, oks_k_lines_b s

917: l_reprice_rec OKS_REPRICE_PVT.reprice_rec_type;
918:
919: CURSOR c_top_lines(cp_chr_id IN NUMBER) IS
920: SELECT c.cle_id, sum(nvl(c.price_negotiated,0)), sum(nvl(s.tax_amount,0))
921: FROM okc_k_lines_b c, oks_k_lines_b s
922: WHERE c.dnz_chr_id = cp_chr_id
923: --get only sublines for 1,12,19 (14:no renewal, 46:no sublines)
924: AND c.lse_id IN (7,8,9,10,11,35, 13, 25)
925: AND s.cle_id = c.id

Line 948: UPDATE okc_k_lines_b

944: ELSIF (p_price_method = 'LST')THEN
945: l_reprice_rec.price_type := p_price_method;
946: l_reprice_rec.price_list_id := p_price_list_id;
947: --update toplines with price list id
948: UPDATE okc_k_lines_b
949: SET price_list_id = p_price_list_id
950: WHERE dnz_chr_id = p_chr_id
951: AND cle_id IS NULL;
952: ELSIF (p_price_method = 'PCT') THEN

Line 1000: UPDATE okc_k_lines_b

996:
997: EXIT WHEN (l_id_tbl.count = 0);
998:
999: FORALL i IN l_id_tbl.first..l_id_tbl.last
1000: UPDATE okc_k_lines_b
1001: SET price_negotiated = l_price_tbl(i)
1002: WHERE id = l_id_tbl(i);
1003:
1004: FORALL i IN l_id_tbl.first..l_id_tbl.last

Line 1021: (SELECT sum(price_negotiated) FROM okc_k_lines_b tl

1017:
1018: --update the header
1019: UPDATE okc_k_headers_all_b h
1020: SET h.estimated_amount =
1021: (SELECT sum(price_negotiated) FROM okc_k_lines_b tl
1022: WHERE tl.dnz_chr_id = p_chr_id AND tl.cle_id IS NULL
1023: AND tl.lse_id IN (1,12,19,46))
1024: WHERE h.id = p_chr_id;
1025:

Line 1028: (SELECT sum(stl.tax_amount) FROM okc_k_lines_b ctl, oks_k_lines_b stl

1024: WHERE h.id = p_chr_id;
1025:
1026: UPDATE oks_k_headers_b h
1027: SET h.tax_amount =
1028: (SELECT sum(stl.tax_amount) FROM okc_k_lines_b ctl, oks_k_lines_b stl
1029: WHERE ctl.dnz_chr_id = p_chr_id AND ctl.cle_id IS NULL
1030: AND ctl.lse_id IN (1,12,19,46) AND stl.cle_id = ctl.id)
1031: WHERE h.chr_id = p_chr_id;
1032:

Line 1376: FROM okc_k_lines_b

1372:
1373: --gets the toplines for creating the sales credit
1374: CURSOR c_get_top_lines(cp_chr_id IN NUMBER) IS
1375: SELECT id
1376: FROM okc_k_lines_b
1377: WHERE dnz_chr_id = cp_chr_id AND cle_id IS NULL AND lse_id IN (1,12,19,46);
1378:
1379: l_prof_enable_sc VARCHAR2(30);
1380: l_prof_rev_type VARCHAR2(30);

Line 1899: FROM okc_k_lines_b

1895: l_lse_id_tbl num_tbl_type;
1896:
1897: CURSOR c_subscr_service_lines(cp_chr_id IN NUMBER) IS
1898: SELECT id, nvl(orig_system_id1, cle_id_renewed) old_id, lse_id
1899: FROM okc_k_lines_b
1900: WHERE dnz_chr_id = cp_chr_id AND cle_id IS NULL
1901: AND lse_id IN (1,19,46);
1902:
1903: BEGIN

Line 2068: FROM okc_k_lines_b oldc, oks_k_lines_b olds, okc_k_lines_b newc

2064: l_old_break_uom_tbl chr_tbl_type;
2065:
2066: CURSOR c_get_usage_price_locks(cp_chr_id IN NUMBER) IS
2067: SELECT olds.locked_price_list_line_id, newc.id, nvl(olds.break_uom, 'X')
2068: FROM okc_k_lines_b oldc, oks_k_lines_b olds, okc_k_lines_b newc
2069: WHERE newc.dnz_chr_id = cp_chr_id AND newc.lse_id IN (12, 13)
2070: AND oldc.id = newc.orig_system_id1 AND olds.cle_id = oldc.id
2071: AND olds.locked_price_list_id IS NOT NULL
2072: AND nvl(oldc.price_list_id, -99) = nvl(newc.price_list_id, -98);

Line 2389: FROM okc_k_lines_b renc, oks_k_lines_b rens,

2385: SELECT renc.id, renc.inv_rule_id, renc.orig_system_id1, rens.billing_schedule_type,
2386: nvl(renc.price_negotiated,0) new_line_amt,
2387: (nvl(oldc.price_negotiated, 0) + nvl(olds.ubt_amount, 0) +
2388: nvl(olds.credit_amount, 0) + nvl(olds.suppressed_credit, 0) ) old_line_amt
2389: FROM okc_k_lines_b renc, oks_k_lines_b rens,
2390: okc_k_lines_b oldc, oks_k_lines_b olds
2391: WHERE renc.dnz_chr_id = cp_chr_id
2392: AND renc.cle_id IS NULL AND renc.lse_id IN (1,12,19,46) AND rens.cle_id = renc.id
2393: AND oldc.id = renc.orig_system_id1

Line 2390: okc_k_lines_b oldc, oks_k_lines_b olds

2386: nvl(renc.price_negotiated,0) new_line_amt,
2387: (nvl(oldc.price_negotiated, 0) + nvl(olds.ubt_amount, 0) +
2388: nvl(olds.credit_amount, 0) + nvl(olds.suppressed_credit, 0) ) old_line_amt
2389: FROM okc_k_lines_b renc, oks_k_lines_b rens,
2390: okc_k_lines_b oldc, oks_k_lines_b olds
2391: WHERE renc.dnz_chr_id = cp_chr_id
2392: AND renc.cle_id IS NULL AND renc.lse_id IN (1,12,19,46) AND rens.cle_id = renc.id
2393: AND oldc.id = renc.orig_system_id1
2394: AND olds.cle_id = renc.orig_system_id1;

Line 2601: FROM okc_k_lines_b a, oks_k_lines_b b

2597:
2598: CURSOR c_bp_toplines (cp_chr_id IN NUMBER) IS
2599: SELECT a.id, a.start_date, a.end_date, nvl(b.billing_schedule_type, 'XX'),
2600: a.lse_id, nvl(b.usage_type, 'XX')
2601: FROM okc_k_lines_b a, oks_k_lines_b b
2602: WHERE a.dnz_chr_id = cp_chr_id AND a.id = b.cle_id
2603: AND a.cle_id IS NULL;
2604:
2605: CURSOR c_chk_accounting_rule(l_id NUMBER) IS

Line 2816: --update okc_k_lines_b toplines with inv_rule_id

2812:
2813: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2814: FND_LOG.string(FND_LOG.level_statement, l_mod_name,'upd_inv_rul : updating invoice rule');
2815: END IF;
2816: --update okc_k_lines_b toplines with inv_rule_id
2817: UPDATE okc_k_lines_b
2818: SET inv_rule_id = l_invoice_rule_id
2819: WHERE dnz_chr_id = p_chr_id AND cle_id IS NULL;
2820:

Line 2817: UPDATE okc_k_lines_b

2813: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2814: FND_LOG.string(FND_LOG.level_statement, l_mod_name,'upd_inv_rul : updating invoice rule');
2815: END IF;
2816: --update okc_k_lines_b toplines with inv_rule_id
2817: UPDATE okc_k_lines_b
2818: SET inv_rule_id = l_invoice_rule_id
2819: WHERE dnz_chr_id = p_chr_id AND cle_id IS NULL;
2820:
2821: --update variarable usage type lines with "Arrears" (-3) invoice rule if billing profile's invoice

Line 2829: UPDATE okc_k_lines_b a

2825: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
2826: FND_LOG.string(FND_LOG.level_statement, l_mod_name,'upd_inv_rul : updating usage invoice rule');
2827: END IF;
2828:
2829: UPDATE okc_k_lines_b a
2830: SET a.inv_rule_id = -3
2831: WHERE a.dnz_chr_id = p_chr_id AND a.cle_id IS NULL AND a.lse_id = 12
2832: AND EXISTS (SELECT 1 FROM oks_k_lines_b b
2833: WHERE b.cle_id = a.id AND b.usage_type IN ('VRT', 'QTY'));

Line 2842: WHERE cle_id IN (SELECT id FROM okc_k_lines_b

2838: END IF;
2839: --update oks_k_lines_b toplines with acct_rule_id
2840: UPDATE oks_k_lines_b
2841: SET acct_rule_id = l_account_rule_id
2842: WHERE cle_id IN (SELECT id FROM okc_k_lines_b
2843: WHERE dnz_chr_id = p_chr_id AND cle_id IS NULL);
2844:
2845: END IF;-- bug 5112991 l_id_tbl.count <> 0 THEN
2846:

Line 3032: (SELECT id FROM OKC_K_LINES_B WHERE dnz_chr_id = p_chr_id

3028: --and we are replacing that call
3029: UPDATE oks_k_lines_b
3030: SET billing_schedule_type = NULL
3031: WHERE cle_id IN
3032: (SELECT id FROM OKC_K_LINES_B WHERE dnz_chr_id = p_chr_id
3033: AND lse_id IN (7,8,9,10,11,35,13,18,25));
3034:
3035: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
3036: FND_LOG.string(FND_LOG.level_statement, l_mod_name || '.delete_billing', 'done');

Line 3725: UPDATE okc_k_lines_b

3721: conversion_euro_rate = l_conv_euro_rate
3722: WHERE id = p_chr_id;
3723:
3724: --null out OKC/OKS top lines payment instruction and po number attributes
3725: UPDATE okc_k_lines_b
3726: SET payment_instruction_type = NULL
3727: WHERE dnz_chr_id = p_chr_id AND cle_id IS NULL AND lse_id IN (1,12,19,46);
3728:
3729: UPDATE oks_k_lines_b b

Line 3733: AND b.cle_id IN (SELECT a.id FROM okc_k_lines_b a

3729: UPDATE oks_k_lines_b b
3730: SET b.cust_po_number = NULL,
3731: b.cust_po_number_req_yn = NULL
3732: WHERE b.dnz_chr_id = p_chr_id
3733: AND b.cle_id IN (SELECT a.id FROM okc_k_lines_b a
3734: WHERE a.dnz_chr_id = p_chr_id AND a.cle_id IS NULL AND a.lse_id IN (1,12,19,46));
3735:
3736:
3737: --for new contracts the workflow process is started by the OKS TAPI

Line 3935: FROM okc_k_lines_b kl

3931:
3932: --cursor to check if all sublines and subscr toplines have been renew consolidated
3933: CURSOR c_check_line_rencon(cp_chr_id IN NUMBER) IS
3934: SELECT kl.id
3935: FROM okc_k_lines_b kl
3936: WHERE kl.dnz_chr_id = cp_chr_id AND kl.lse_id IN (7, 8, 9, 10, 11, 13, 25, 35, 46)
3937: AND kl.id NOT IN(
3938: SELECT ol.object_cle_id
3939: FROM okc_operation_lines ol, okc_operation_instances oi, okc_class_operations oo

Line 3965: FROM okc_k_lines_b

3961:
3962: --cursor to check if all sublines and subscr toplines have been cancelled or terminated
3963: CURSOR c_check_line_term_canc (cp_chr_id IN NUMBER) IS
3964: SELECT id
3965: FROM okc_k_lines_b
3966: WHERE dnz_chr_id = cp_chr_id AND lse_id IN (7, 8, 9, 10, 11, 13, 25, 35, 46)
3967: AND date_terminated IS NULL AND date_cancelled IS NULL;
3968:
3969: --cursor to determine if any sublines or toplines exist with an effective renewal type

Line 3974: FROM okc_k_lines_b a, okc_k_lines_b b

3970: --that is not DNR. If a topline has renewal type DNR, none of it's sublines are considered.
3971: --If topline is not DNR then sublines are checked to see if any exist with renewal type not DNR
3972: CURSOR c_check_line_dnr (cp_chr_id IN NUMBER) IS
3973: SELECT a.id
3974: FROM okc_k_lines_b a, okc_k_lines_b b
3975: WHERE a.dnz_chr_id = cp_chr_id
3976: AND b.dnz_chr_id (+) = cp_chr_id
3977: AND a.cle_id = b.id (+)
3978: AND a.lse_id IN (7,8,9,10,11,13,25,35,46)

Line 3986: FROM okc_k_lines_b

3982:
3983: --cursor to check if the contract contains any warranty lines
3984: CURSOR c_check_line_warr (cp_chr_id IN NUMBER) IS
3985: SELECT id
3986: FROM okc_k_lines_b
3987: WHERE dnz_chr_id = cp_chr_id AND lse_id = 14;
3988:
3989: --cursor to check id there are any valid sublines and subscr toplines
3990: --we need to do line level checks only if a valid line exists, other wise we get

Line 3994: FROM okc_k_lines_b kl, okc_statuses_b st

3990: --we need to do line level checks only if a valid line exists, other wise we get
3991: --redundant error messages
3992: CURSOR c_check_valid_line(cp_chr_id IN NUMBER) IS
3993: SELECT id
3994: FROM okc_k_lines_b kl, okc_statuses_b st
3995: WHERE kl.dnz_chr_id = cp_chr_id AND kl.lse_id IN (7, 8, 9, 10, 11, 13, 25, 35, 46)
3996: AND kl.sts_code = st.ste_code
3997: AND st.ste_code IN ('ACTIVE', 'EXPIRED', 'SIGNED', 'CANCELLED', 'TERMINATED');
3998:

Line 4507: okc_k_items it, oks_k_lines_b sl, okc_k_lines_b kl

4503: --kl.lse_id, bk.inventory_item_id id1, bk.organization_id id2 , bk.organization_id inv_org_id,
4504: sl.id, bt.description name, bk.concatenated_segments description, null,
4505: null, null, to_char(kl.start_date,'DD-MON-YYYY'), to_char(kl.end_date,'DD-MON-YYYY')
4506: FROM mtl_system_items_b_kfv bk, mtl_system_items_tl bt,
4507: okc_k_items it, oks_k_lines_b sl, okc_k_lines_b kl
4508: WHERE bk.inventory_item_id = bt.inventory_item_id
4509: AND bk.organization_id = bt.organization_id
4510: AND bt.language = USERENV('LANG')
4511: AND bk.inventory_item_id = it.object1_id1

Line 4574: ) iv, okc_k_items it, oks_k_lines_b sl, okc_k_lines_b kl, okc_k_headers_all_b kh

4570: T.DESCRIPTION NAME, B.CONCATENATED_SEGMENTS DESCRIPTION, B.ORGANIZATION_ID INV_ORG_ID
4571: FROM MTL_SYSTEM_ITEMS_B_KFV B,MTL_SYSTEM_ITEMS_TL T
4572: WHERE B.INVENTORY_ITEM_ID = T.INVENTORY_ITEM_ID AND B.ORGANIZATION_ID = T.ORGANIZATION_ID
4573: AND T.LANGUAGE = USERENV('LANG')
4574: ) iv, okc_k_items it, oks_k_lines_b sl, okc_k_lines_b kl, okc_k_headers_all_b kh
4575: WHERE iv.type = it.jtot_object1_code -- bug 5218936
4576: AND iv.id1 = it.object1_id1
4577: AND iv.id2 = it.object1_id2
4578: AND decode(iv.inv_org_id, null, kh.inv_organization_id, iv.inv_org_id) = kh.inv_organization_id

Line 5465: UPDATE okc_k_lines_b

5461: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN
5462: FND_LOG.string(FND_LOG.level_statement, l_mod_name , ' calling update to annualized_factor , p_new_chr_id='||x_chr_id);
5463: END IF;
5464:
5465: UPDATE okc_k_lines_b
5466: SET annualized_factor = OKS_SETUP_UTIL_PUB.Get_Annualized_Factor(start_date, end_date, lse_id)
5467: WHERE dnz_chr_id = x_chr_id;
5468:
5469: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN