[Home] [Help]
4028: p_amount IN NUMBER,
4029: x_status OUT NOCOPY VARCHAR2) IS
4030: CURSOR subline_count IS
4031: SELECT COUNT( * )
4032: FROM okc_k_lines_b
4033: WHERE cle_id = p_cle_id
4034: AND lse_id IN (7, 8, 9, 10, 11, 35, 18, 25);
4035:
4036: CURSOR subline_cur IS
4034: AND lse_id IN (7, 8, 9, 10, 11, 35, 18, 25);
4035:
4036: CURSOR subline_cur IS
4037: SELECT id, price_negotiated
4038: FROM okc_k_lines_b
4039: WHERE cle_id = p_cle_id
4040: AND lse_id IN (7, 8, 9, 10, 11, 35, 18, 25);
4041:
4042: l_total_amt NUMBER := 0;
4085: l_inv_rule_id NUMBER;
4086:
4087: CURSOR inv_rule_cur(p_id IN NUMBER) IS
4088: SELECT inv_rule_id
4089: FROM okc_k_lines_b
4090: WHERE id = p_id;
4091:
4092: l_sll_tbl OKS_BILL_SCH.StreamLvl_tbl;
4093: l_bill_sch_out_tbl OKS_BILL_SCH.ItemBillSch_tbl;
4207: FUNCTION GetCoverage(p_cle_id IN NUMBER) RETURN NUMBER
4208: IS
4209: CURSOR l_cov_csr IS
4210: SELECT id
4211: FROM okc_k_lines_b
4212: WHERE cle_id = p_cle_id
4213: AND lse_id IN(2, 20);
4214: l_cov_id NUMBER := NULL;
4215: BEGIN
4368: --LLC
4369:
4370: CURSOR get_topline_tax_amt_csr (p_cle_id IN NUMBER) IS
4371: SELECT nvl(SUM(nvl(tax_amount, 0)), 0)
4372: FROM okc_k_lines_b cle, oks_k_lines_b sle
4373: WHERE cle.cle_id = p_cle_id
4374: AND cle.lse_id IN (7, 8, 9, 10, 11, 18, 25, 35)
4375: AND cle.id = sle.cle_id
4376: AND cle.dnz_chr_id = sle.dnz_chr_id
4377: AND cle.date_cancelled IS NULL;
4378:
4379: CURSOR get_hdr_tax_amt_csr (p_chr_id IN NUMBER) IS
4380: SELECT nvl(SUM(nvl(tax_amount, 0)), 0)
4381: FROM okc_k_lines_b cle, oks_k_lines_b sle
4382: WHERE cle.dnz_chr_id = p_chr_id
4383: AND cle.lse_id IN (1, 12, 14, 19, 46)
4384: AND cle.cle_id IS NULL
4385: AND cle.id = sle.cle_id
4494: , cle.price_negotiated amt
4495: , cle.dnz_chr_id chr_id
4496: , kln.id kln_id
4497: , kln.object_version_number
4498: FROM okc_k_lines_b cle,
4499: oks_k_lines_b kln
4500: WHERE cle.cle_id = p_cle_id
4501: AND kln.cle_id = cle.id
4502: AND lse_id NOT IN (2, 15, 20, 46);
4634: l_chrv_tbl_out okc_contract_pub.chrv_tbl_type;
4635:
4636: CURSOR total_amount(p_chr_id IN NUMBER) IS
4637: SELECT nvl(SUM(price_negotiated), 0) SUM
4638: FROM okc_k_lines_b
4639: WHERE dnz_chr_id = p_chr_id
4640: AND lse_id IN (7, 8, 9, 10, 11, 35, 13, 18, 25)
4641: AND date_cancelled IS NULL; --ignore cancelled sublines
4642:
4673: IS
4674:
4675: CURSOR header_cur IS
4676: SELECT dnz_chr_id, start_date, end_date, object_version_number, lse_id
4677: FROM okc_k_lines_b
4678: WHERE id = p_source_rec.cle_id;
4679:
4680: CURSOR get_contract_modifier_cur (p_chr_id NUMBER)
4681: IS
4756: --TAX
4757:
4758: CURSOR get_price_negotiated_amt_csr (p_cle_id IN NUMBER) IS
4759: SELECT nvl(SUM(price_negotiated), 0) amt
4760: FROM okc_k_lines_b
4761: WHERE cle_id = p_cle_id
4762: AND date_cancelled IS NULL; ---Added condition to exclude cancelled sublines
4763:
4764: get_price_negotiated_amt_rec get_price_negotiated_amt_csr%ROWTYPE;
4764: get_price_negotiated_amt_rec get_price_negotiated_amt_csr%ROWTYPE;
4765:
4766: CURSOR sum_price_negotiated_lines_csr (p_chr_id NUMBER) IS
4767: SELECT nvl(SUM(price_negotiated), 0) amt
4768: FROM okc_k_lines_b
4769: WHERE dnz_chr_id = p_chr_id
4770: AND cle_id IS NULL
4771: AND date_cancelled IS NULL; ---Added condition to exclude cancelled lines
4772:
4787: --LLC CANCELLED_AMOUNT
4788:
4789: CURSOR sum_cancelled_amt_lines_csr (p_chr_id NUMBER) IS
4790: SELECT SUM(nvl(cancelled_amount, 0))
4791: FROM okc_k_lines_b
4792: WHERE dnz_chr_id = p_chr_id
4793: AND cle_id IS NULL;
4794:
4795:
4794:
4795:
4796: CURSOR get_topline_cancelled_amt (p_cle_id IN NUMBER) IS
4797: SELECT nvl(SUM(nvl(price_negotiated, 0)), 0) --BUG FIX 4758886 --Forced to return this query 0 value
4798: FROM okc_k_lines_b
4799: WHERE cle_id = p_cle_id
4800: AND date_cancelled IS NOT NULL; --Condition to consider only the cancelled sublines
4801:
4802:
4854: --Fix for bug#2221910 start. Get Max Of Top Lines.
4855:
4856: SELECT nvl(MAX(to_number(line_number)), 0)
4857: INTO l_top_line_number
4858: FROM OKC_K_LINES_B
4859: WHERE dnz_chr_id = g_chr_id
4860: AND cle_id IS NULL;
4861: --Fix for bug#2221910 end.
4862:
4933:
4934: --Fix for bug#2221910 start. Update Top Line Sequence number.
4935:
4936: l_top_line_number := l_top_line_number + 1;
4937: UPDATE okc_k_lines_b SET line_number = l_top_line_number
4938: WHERE id = l_target_tbl(idx).cle_id;
4939: --Fix for bug#2221910 end.
4940:
4941: IF l_return_status <> OKC_API.G_RET_STS_SUCCESS THEN
5174: l_curr_code
5175: );
5176: --Updating topline with the price_negotiated and cancelled_amount
5177:
5178: UPDATE okc_k_lines_b
5179: SET price_negotiated = l_curr_price_negotiated_amt,
5180: cancelled_amount = l_curr_cancelled_amt
5181: WHERE id = l_target_tbl(idx).cle_id;
5182:
5615: -- Get customer account id
5616: CURSOR l_csr_get_cust_acct_id(p_cle_id IN NUMBER)
5617: IS
5618: SELECT cust_acct_id
5619: FROM okc_k_lines_b
5620: WHERE id = p_cle_id;
5621:
5622:
5623: CURSOR l_Get_party_id(p_cust_acct_id IN NUMBER)
5669: IS
5670: CURSOR l_csr_get_shipto_billto(p_cle_id IN NUMBER)
5671: IS
5672: SELECT ship_to_site_use_id, bill_to_site_use_id
5673: FROM okc_k_lines_b
5674: WHERE id = p_cle_id;
5675:
5676:
5677: CURSOR l_csr_partysite_from_custsite(p_shipto_or_billto IN NUMBER)
6167: UPDATE okc_k_headers_b
6168: SET date_renewed = TRUNC(p_operation_lines_tbl(i).creation_date)
6169: WHERE id = p_operation_lines_tbl(i).object_chr_id;
6170: ELSE
6171: UPDATE okc_k_lines_b
6172: SET date_renewed = TRUNC(p_operation_lines_tbl(i).creation_date)
6173: WHERE id = p_operation_lines_tbl(i).object_cle_id;
6174: END IF;
6175: END LOOP;
6197: IS
6198:
6199: CURSOR line_cur(c_cle_id NUMBER) IS
6200: SELECT cle_id, line_number
6201: FROM okc_k_lines_b
6202: WHERE id = c_cle_id;
6203:
6204: CURSOR price_adj_cur(c_list_line_id NUMBER, c_cle_id NUMBER) IS
6205: SELECT 'x'
6214: AND chr_id = c_chr_id;
6215:
6216: CURSOR sub_line_cur(c_cle_id NUMBER) IS
6217: SELECT id, line_number
6218: FROM okc_k_lines_b
6219: WHERE cle_id = c_cle_id
6220: AND lse_id IN(9, 25);
6221:
6222: l_line_number VARCHAR2(301) := NULL;
6312:
6313: CURSOR l_csr_total_line_amount(p_line_id IN NUMBER)
6314: IS
6315: SELECT nvl(SUM(nvl(price_negotiated, 0)), 0)
6316: FROM okc_k_lines_b
6317: WHERE cle_id = p_line_id
6318: AND date_cancelled IS NULL; -- line Level Cancellation
6319:
6320: CURSOR toplinetax_cur(p_cle_id IN NUMBER) IS
6318: AND date_cancelled IS NULL; -- line Level Cancellation
6319:
6320: CURSOR toplinetax_cur(p_cle_id IN NUMBER) IS
6321: SELECT SUM(nvl(tax_amount, 0)) amount
6322: FROM okc_k_lines_b cle, oks_k_lines_b kln
6323: WHERE cle.cle_id = p_cle_id
6324: AND cle.id = kln.cle_id
6325: AND cle.lse_id IN (7, 8, 9, 10, 11, 13, 35, 25, 46)
6326: AND cle.date_cancelled IS NULL;
6458:
6459: -- Header Level Tax Amount --
6460: CURSOR total_amount IS
6461: SELECT nvl(SUM(price_negotiated), 0) SUM
6462: FROM okc_k_lines_b
6463: WHERE dnz_chr_id = p_header_id
6464: AND cle_id IS NULL
6465: AND date_cancelled IS NULL; -- line Level Cancellation
6466: --Commented URP
6468:
6469: -- Header Tax Total
6470: CURSOR hdrtax_cur IS
6471: SELECT SUM(kln.tax_amount) amount
6472: FROM okc_k_lines_b cle, oks_k_lines_b kln
6473: WHERE cle.dnz_chr_id = p_header_id
6474: AND cle.id = kln.cle_id
6475: AND cle.lse_id IN (7, 8, 9, 10, 11, 13, 35, 25, 46)
6476: AND cle.date_cancelled IS NULL;
6557: lines.date_terminated,
6558: lines.price_unit,
6559: lines.price_unit_percent,
6560: lines.id
6561: FROM okc_k_lines_b lines
6562: WHERE lines.cle_id = p_contract_line_id
6563: AND lines.lse_id IN(7, 8, 9, 10, 11, 18, 25, 35);
6564: l_subline_rec l_subline_csr%ROWTYPE;
6565: l_prorated_amount NUMBER := 0;
6605:
6606: CURSOR l_svc_csr IS
6607: SELECT nvl(SUM(NVL(price_negotiated, 0)), 0) service_amount
6608: , COUNT(price_negotiated)
6609: FROM okc_k_lines_b lines
6610: WHERE lines.cle_id = p_contract_line_id
6611: AND lines.lse_id IN(7, 8, 9, 10, 11, 18, 25, 35)
6612: AND lines.date_cancelled IS NULL; -- line Level Cancellation
6613:
6627: okc.dnz_chr_id,
6628: currency_code,
6629: oks.id oks_id,
6630: oks.object_version_number
6631: FROM okc_k_lines_b okc, oks_k_lines_b oks
6632: WHERE okc.Cle_id = p_contract_line_id
6633: AND OKS.cle_id = okc.id
6634: AND (okc.date_terminated IS NULL OR okc.date_terminated > TRUNC(SYSDATE))
6635: AND lse_id NOT IN(2, 15, 20)
6718:
6719: FUNCTION Get_Header_Id RETURN NUMBER IS
6720: CURSOR l_hdr_csr IS
6721: SELECT dnz_chr_id
6722: FROM okc_k_lines_b
6723: WHERE id = p_contract_line_id;
6724: l_header_id OKC_K_HEADERS_B.ID%TYPE;
6725: BEGIN
6726: OPEN l_hdr_csr;
6820:
6821: ----------errorout_ad(' after updating coverag level ' ||clvl_rec.lse_id||' '||clvl_rec.id);
6822:
6823: /* select price_negotiated into cp_price
6824: from okc_k_lines_b
6825: where id = clvl_rec.id; */
6826:
6827: ----------errorout_ad(' price nego after update '||cp_price);
6828:
7180: )
7181: IS
7182: CURSOR get_all_instances_csr IS
7183: SELECT id
7184: FROM okc_k_lines_b
7185: WHERE cle_id = p_cle_id
7186: AND lse_id IN (9, 25);
7187:
7188: CURSOR get_okc_item_qty_csr (p_item_cle_id NUMBER) IS
7753: x_msg_data OUT NOCOPY VARCHAR2
7754: ) RETURN BOOLEAN IS
7755:
7756: CURSOR check_eligiblity_line IS
7757: SELECT 'Y' FROM OKC_K_LINES_B
7758: WHERE dnz_chr_id = p_contract_hdr_id
7759: AND id = p_contract_line_id
7760: AND(date_terminated IS NULL OR date_terminated > TRUNC(SYSDATE))
7761: AND date_cancelled IS NULL
7762: AND end_date > NVL(oks_bill_util_pub.get_billed_upto(id, p_intent), end_date - 1);
7763:
7764:
7765: CURSOR get_lines_id IS
7766: SELECT ID, LSE_ID FROM OKC_K_LINES_B
7767: WHERE dnz_chr_id = p_contract_hdr_id
7768: AND cle_id IS NULL
7769: AND(date_terminated IS NULL OR date_terminated > TRUNC(SYSDATE))
7770: AND date_cancelled IS NULL
7796: IF P_intent = 'H'
7797: THEN
7798: FOR cur_rec IN get_lines_id
7799: LOOP
7800: UPDATE okc_k_lines_b
7801: SET price_list_id = p_price_list_id
7802: WHERE dnz_chr_id = p_contract_hdr_id
7803: AND cle_id IS NULL
7804: AND ID = CUR_REC.id;
7944: SELECT a.id, nvl(a.price_negotiated, 0) topline_amt,
7945: SUM(nvl(b.price_negotiated,0)) sum_subline_amt,
7946: nvl(c.tax_amount,0) topline_tax_amt,
7947: SUM(nvl(d.tax_amount,0)) sum_subline_tax_amt
7948: FROM okc_k_lines_b a, okc_k_lines_b b,
7949: oks_k_lines_b c, oks_k_lines_b d
7950: WHERE a.dnz_chr_id = cp_chr_id AND a.lse_id IN (1,12,14,19)
7951: AND b.dnz_chr_id = a.dnz_chr_id AND b.cle_id = a.id
7952: AND a.id=c.cle_id AND b.id=d.cle_id
7959:
7960:
7961: CURSOR c_lock_tl(cp_id IN NUMBER) IS
7962: SELECT price_negotiated
7963: FROM okc_k_lines_b WHERE id = cp_id
7964: FOR UPDATE OF price_negotiated NOWAIT;
7965:
7966: CURSOR c_line_name(cp_id IN NUMBER) IS
7967: SELECT substr(RTRIM(RTRIM(line_number) || ', ' || RTRIM(lsev.name) || ' ' || RTRIM(clev.name)), 1, 2000) line_name
7974: nvl(a.estimated_amount, 0) hdr_amt,
7975: SUM(nvl(b.price_negotiated,0)) sum_tl_line_amt,
7976: nvl(c.tax_amount,0) hdr_tax_amt,
7977: SUM(nvl(d.tax_amount,0)) sum_tl_line_amt_tax
7978: FROM okc_k_headers_all_b a, okc_k_lines_b b,
7979: oks_k_headers_b c, oks_k_lines_b d
7980: WHERE a.id = cp_chr_id
7981: AND b.dnz_chr_id = a.id AND b.cle_id IS NULL
7982: AND d.dnz_chr_id = c.chr_id AND d.cle_id=b.id
7992: -- select all the toplines which has cancelled amount not equal to those of sublines
7993: CURSOR c_k_toplines_cancelled (cp_chr_id IN NUMBER) IS
7994: SELECT a.id, nvl(a.cancelled_amount, 0) topline_canceled_amt,
7995: SUM(nvl(b.price_negotiated,0)) sum_subline_cancelled_amt
7996: FROM okc_k_lines_b a, okc_k_lines_b b
7997: WHERE a.dnz_chr_id = cp_chr_id AND a.lse_id IN (1,12,14,19)
7998: AND b.dnz_chr_id = a.dnz_chr_id AND b.cle_id = a.id
7999: AND a.date_cancelled is null
8000: AND b.date_cancelled is not null
8005: CURSOR c_k_hdr_cancelled(cp_chr_id IN NUMBER) IS
8006: SELECT
8007: nvl(a.cancelled_amount, 0) hdr_cancelled_amt,
8008: SUM(nvl(b.cancelled_amount,0)) sum_tl_line_cancelled_amt
8009: FROM okc_k_headers_all_b a, okc_k_lines_b b
8010: WHERE a.id = cp_chr_id
8011: AND b.dnz_chr_id = a.id AND b.cle_id IS NULL
8012: AND b.lse_id IN (1,12,14,19,46)
8013: GROUP BY a.cancelled_amount;
8090: END IF;
8091:
8092: --if we come till here, we where able to obtain locks on all the toplines fectched previously
8093: FORALL j IN l_tl_id_tbl.first..l_tl_id_tbl.last
8094: UPDATE okc_k_lines_b
8095: SET price_negotiated = l_sl_amt_tbl(j)
8096: WHERE id = l_tl_id_tbl(j);
8097:
8098: FORALL j IN l_tl_id_tbl.first..l_tl_id_tbl.last
8226: END IF;
8227:
8228: --if we come till here, we where able to obtain locks on all the toplines fectched previously
8229: FORALL j IN l_tl_id_tbl.first..l_tl_id_tbl.last
8230: update okc_k_lines_b
8231: set cancelled_amount =l_sl_amt_tbl(j)
8232: where id=l_tl_id_tbl(j);
8233:
8234: IF (FND_LOG.level_statement >= FND_LOG.g_current_runtime_level) THEN