[Home] [Help]
114: SELECT 1 FROM OKL_TXL_ITM_INSTS
115: WHERE INVENTORY_ITEM_ID = p_item_id
116: AND SERIAL_NUMBER = p_serial_number
117: AND NOT EXISTS (
118: select 1 From okc_k_lines_b cleb
119: where cleb.id=OKL_TXL_ITM_INSTS.kle_id
120: and cleb.STS_CODE = 'ABANDONED'
121: );
122: --Bug# 4698117: End
322: p_token2_value => sqlerrm);
323: RETURN x_return_status;
324: END generate_asset_number;
325: ----------------------------------------------------------------------------------------------------------
326: FUNCTION get_sts_code(p_dnz_chr_id IN OKC_K_LINES_B.DNZ_CHR_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
327: p_cle_id IN OKC_K_LINES_B.CLE_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
328: x_sts_code OUT NOCOPY OKC_K_HEADERS_B.STS_CODE%TYPE)
329: RETURN VARCHAR2 IS
330: x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
323: RETURN x_return_status;
324: END generate_asset_number;
325: ----------------------------------------------------------------------------------------------------------
326: FUNCTION get_sts_code(p_dnz_chr_id IN OKC_K_LINES_B.DNZ_CHR_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
327: p_cle_id IN OKC_K_LINES_B.CLE_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
328: x_sts_code OUT NOCOPY OKC_K_HEADERS_B.STS_CODE%TYPE)
329: RETURN VARCHAR2 IS
330: x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
331: CURSOR c_get_tls_sts_code(p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE) IS
327: p_cle_id IN OKC_K_LINES_B.CLE_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
328: x_sts_code OUT NOCOPY OKC_K_HEADERS_B.STS_CODE%TYPE)
329: RETURN VARCHAR2 IS
330: x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
331: CURSOR c_get_tls_sts_code(p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE) IS
332: SELECT chrv.sts_code
333: FROM OKC_K_HEADERS_V chrv
334: WHERE chrv.id = p_dnz_chr_id;
335:
332: SELECT chrv.sts_code
333: FROM OKC_K_HEADERS_V chrv
334: WHERE chrv.id = p_dnz_chr_id;
335:
336: CURSOR c_get_sls_sts_code(p_cle_id OKC_K_LINES_B.CLE_ID%TYPE) IS
337: SELECT cle.sts_code
338: FROM OKC_K_LINES_V cle
339: WHERE cle.id = p_cle_id;
340: BEGIN
434: END IF;
435: RETURN(x_return_status);
436: END get_sts_code;
437: ----------------------------------------------------------------------------------------------------------------
438: FUNCTION get_end_date(p_dnz_chr_id IN OKC_K_LINES_B.DNZ_CHR_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
439: p_cle_id IN OKC_K_LINES_B.CLE_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
440: x_end_date OUT NOCOPY OKC_K_HEADERS_B.END_DATE%TYPE)
441: RETURN VARCHAR2 IS
442: x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
435: RETURN(x_return_status);
436: END get_sts_code;
437: ----------------------------------------------------------------------------------------------------------------
438: FUNCTION get_end_date(p_dnz_chr_id IN OKC_K_LINES_B.DNZ_CHR_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
439: p_cle_id IN OKC_K_LINES_B.CLE_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
440: x_end_date OUT NOCOPY OKC_K_HEADERS_B.END_DATE%TYPE)
441: RETURN VARCHAR2 IS
442: x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
443: CURSOR c_get_tls_end_date(p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE) IS
439: p_cle_id IN OKC_K_LINES_B.CLE_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
440: x_end_date OUT NOCOPY OKC_K_HEADERS_B.END_DATE%TYPE)
441: RETURN VARCHAR2 IS
442: x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
443: CURSOR c_get_tls_end_date(p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE) IS
444: SELECT chrv.end_Date
445: FROM OKC_K_HEADERS_V chrv
446: WHERE chrv.id = p_dnz_chr_id;
447:
444: SELECT chrv.end_Date
445: FROM OKC_K_HEADERS_V chrv
446: WHERE chrv.id = p_dnz_chr_id;
447:
448: CURSOR c_get_sls_end_date(p_cle_id OKC_K_LINES_B.CLE_ID%TYPE) IS
449: SELECT cle.end_date
450: FROM OKC_K_LINES_V cle
451: WHERE cle.id = p_cle_id;
452: BEGIN
530: END IF;
531: RETURN(x_return_status);
532: END get_end_date;
533: ----------------------------------------------------------------------------------------------------------------
534: FUNCTION get_start_date(p_dnz_chr_id IN OKC_K_LINES_B.DNZ_CHR_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
535: p_cle_id IN OKC_K_LINES_B.CLE_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
536: x_start_date OUT NOCOPY OKC_K_HEADERS_B.START_DATE%TYPE)
537: RETURN VARCHAR2 IS
538: x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
531: RETURN(x_return_status);
532: END get_end_date;
533: ----------------------------------------------------------------------------------------------------------------
534: FUNCTION get_start_date(p_dnz_chr_id IN OKC_K_LINES_B.DNZ_CHR_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
535: p_cle_id IN OKC_K_LINES_B.CLE_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
536: x_start_date OUT NOCOPY OKC_K_HEADERS_B.START_DATE%TYPE)
537: RETURN VARCHAR2 IS
538: x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
539: lv_orig_sys_source_code OKC_K_HEADERS_B.ORIG_SYSTEM_SOURCE_CODE%TYPE;
537: RETURN VARCHAR2 IS
538: x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
539: lv_orig_sys_source_code OKC_K_HEADERS_B.ORIG_SYSTEM_SOURCE_CODE%TYPE;
540: ln_orig_system_id1 OKC_K_HEADERS_B.ORIG_SYSTEM_ID1%TYPE;
541: ld_start_date OKC_K_LINES_B.START_DATE%TYPE;
542: CURSOR c_get_tls_start_date(p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE) IS
543: SELECT chrv.start_Date,
544: chrv.orig_system_source_code,
545: chrv.orig_system_id1
538: x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
539: lv_orig_sys_source_code OKC_K_HEADERS_B.ORIG_SYSTEM_SOURCE_CODE%TYPE;
540: ln_orig_system_id1 OKC_K_HEADERS_B.ORIG_SYSTEM_ID1%TYPE;
541: ld_start_date OKC_K_LINES_B.START_DATE%TYPE;
542: CURSOR c_get_tls_start_date(p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE) IS
543: SELECT chrv.start_Date,
544: chrv.orig_system_source_code,
545: chrv.orig_system_id1
546: FROM OKC_K_HEADERS_V chrv
545: chrv.orig_system_id1
546: FROM OKC_K_HEADERS_V chrv
547: WHERE chrv.id = p_dnz_chr_id;
548:
549: CURSOR c_get_sls_start_date(p_cle_id OKC_K_LINES_B.CLE_ID%TYPE) IS
550: SELECT cle.start_date
551: FROM OKC_K_LINES_V cle
552: WHERE cle.id = p_cle_id;
553:
550: SELECT cle.start_date
551: FROM OKC_K_LINES_V cle
552: WHERE cle.id = p_cle_id;
553:
554: CURSOR c_get_trx_date_trans_occ(p_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE) IS
555: SELECT date_transaction_occurred
556: FROM okl_trx_contracts
557: WHERE khr_id = p_chr_id
558: AND tcn_type = 'TRBK'
649: END IF;
650: RETURN(x_return_status);
651: END get_start_date;
652: ----------------------------------------------------------------------------------------------------------------
653: FUNCTION get_currency_code(p_dnz_chr_id IN OKC_K_LINES_B.DNZ_CHR_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
654: p_cle_id IN OKC_K_LINES_B.CLE_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
655: x_currency_code OUT NOCOPY OKC_K_HEADERS_B.CURRENCY_CODE%TYPE)
656: RETURN VARCHAR2 IS
657: x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
650: RETURN(x_return_status);
651: END get_start_date;
652: ----------------------------------------------------------------------------------------------------------------
653: FUNCTION get_currency_code(p_dnz_chr_id IN OKC_K_LINES_B.DNZ_CHR_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
654: p_cle_id IN OKC_K_LINES_B.CLE_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
655: x_currency_code OUT NOCOPY OKC_K_HEADERS_B.CURRENCY_CODE%TYPE)
656: RETURN VARCHAR2 IS
657: x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
658: CURSOR c_get_tls_currency_code(p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE) IS
654: p_cle_id IN OKC_K_LINES_B.CLE_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
655: x_currency_code OUT NOCOPY OKC_K_HEADERS_B.CURRENCY_CODE%TYPE)
656: RETURN VARCHAR2 IS
657: x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
658: CURSOR c_get_tls_currency_code(p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE) IS
659: SELECT chrv.currency_code
660: FROM OKC_K_HEADERS_V chrv
661: WHERE chrv.id = p_dnz_chr_id;
662:
659: SELECT chrv.currency_code
660: FROM OKC_K_HEADERS_V chrv
661: WHERE chrv.id = p_dnz_chr_id;
662:
663: CURSOR c_get_sls_currency_code(p_cle_id OKC_K_LINES_B.CLE_ID%TYPE) IS
664: SELECT cle.currency_code
665: FROM OKC_K_LINES_V cle
666: WHERE cle.id = p_cle_id;
667: BEGIN
754: -- Parameters :
755: -- Version :
756: -- End of Commnets
757:
758: FUNCTION default_contract_line_values(p_dnz_chr_id IN OKC_K_LINES_B.DNZ_CHR_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
759: p_cle_id IN OKC_K_LINES_B.CLE_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
760: p_clev_rec IN OUT NOCOPY clev_rec_type)
761: RETURN VARCHAR2 IS
762: x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
755: -- Version :
756: -- End of Commnets
757:
758: FUNCTION default_contract_line_values(p_dnz_chr_id IN OKC_K_LINES_B.DNZ_CHR_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
759: p_cle_id IN OKC_K_LINES_B.CLE_ID%TYPE DEFAULT OKL_API.G_MISS_NUM,
760: p_clev_rec IN OUT NOCOPY clev_rec_type)
761: RETURN VARCHAR2 IS
762: x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
763: lv_orig_sys_source_code OKC_K_HEADERS_B.ORIG_SYSTEM_SOURCE_CODE%TYPE;
761: RETURN VARCHAR2 IS
762: x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
763: lv_orig_sys_source_code OKC_K_HEADERS_B.ORIG_SYSTEM_SOURCE_CODE%TYPE;
764: ln_orig_system_id1 OKC_K_HEADERS_B.ORIG_SYSTEM_ID1%TYPE;
765: ld_start_date OKC_K_LINES_B.START_DATE%TYPE;
766: ld_end_date OKC_K_LINES_B.END_DATE%TYPE;
767: lv_currency_code OKC_K_HEADERS_B.CURRENCY_CODE%TYPE;
768: lv_sts_code OKC_K_HEADERS_B.STS_CODE%TYPE;
769:
762: x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
763: lv_orig_sys_source_code OKC_K_HEADERS_B.ORIG_SYSTEM_SOURCE_CODE%TYPE;
764: ln_orig_system_id1 OKC_K_HEADERS_B.ORIG_SYSTEM_ID1%TYPE;
765: ld_start_date OKC_K_LINES_B.START_DATE%TYPE;
766: ld_end_date OKC_K_LINES_B.END_DATE%TYPE;
767: lv_currency_code OKC_K_HEADERS_B.CURRENCY_CODE%TYPE;
768: lv_sts_code OKC_K_HEADERS_B.STS_CODE%TYPE;
769:
770: CURSOR c_get_contract_header_details(p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE) IS
766: ld_end_date OKC_K_LINES_B.END_DATE%TYPE;
767: lv_currency_code OKC_K_HEADERS_B.CURRENCY_CODE%TYPE;
768: lv_sts_code OKC_K_HEADERS_B.STS_CODE%TYPE;
769:
770: CURSOR c_get_contract_header_details(p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE) IS
771: SELECT chrv.sts_code,
772: chrv.end_date,
773: chrv.currency_code,
774: chrv.start_Date,
776: chrv.orig_system_id1
777: FROM OKC_K_HEADERS_V chrv
778: WHERE chrv.id = p_dnz_chr_id;
779:
780: CURSOR c_get_contract_line_details(p_cle_id OKC_K_LINES_B.CLE_ID%TYPE) IS
781: SELECT cle.sts_code,
782: cle.end_date,
783: cle.currency_code,
784: cle.start_date
784: cle.start_date
785: FROM OKC_K_LINES_V cle
786: WHERE cle.id = p_cle_id;
787:
788: CURSOR c_get_trx_date_trans_occ(p_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE) IS
789: SELECT date_transaction_occurred
790: FROM okl_trx_contracts
791: WHERE khr_id = p_chr_id
792: AND tcn_type = 'TRBK'
1482: CIM.LAST_UPDATED_BY,
1483: CIM.LAST_UPDATE_DATE,
1484: CIM.LAST_UPDATE_LOGIN
1485: FROM okc_k_items_v cim,
1486: okc_k_lines_b cle,
1487: okc_line_styles_b lse,
1488: okc_k_lines_b cle1
1489: WHERE cim.dnz_chr_id = p_dnz_chr_id
1490: AND cim.cle_id = cle.id
1484: CIM.LAST_UPDATE_LOGIN
1485: FROM okc_k_items_v cim,
1486: okc_k_lines_b cle,
1487: okc_line_styles_b lse,
1488: okc_k_lines_b cle1
1489: WHERE cim.dnz_chr_id = p_dnz_chr_id
1490: AND cim.cle_id = cle.id
1491: AND cle.lse_id = lse.id
1492: AND lse.lty_code = 'ITEM'
2494: ln_okx_assets_v NUMBER := 0;
2495: ln_okx_asset_lines_v NUMBER := 0;
2496: ln_okl_txd_assets_v NUMBER := 0;
2497: lv_source_code OKC_K_HEADERS_B.ORIG_SYSTEM_SOURCE_CODE%TYPE;
2498: lv_asset_source_code OKC_K_LINES_B.ORIG_SYSTEM_SOURCE_CODE%TYPE;
2499: lv_asset_number OKL_TXL_ASSETS_V.ASSET_NUMBER%TYPE;
2500: lv_release_asset OKC_RULES_V.RULE_INFORMATION1%TYPE := 'N';
2501: x_msg_count NUMBER;
2502: x_msg_data VARCHAR2(100);
2505: IS
2506: SELECT NAME
2507: FROM OKC_K_LINES_TL
2508: WHERE ID = (SELECT CLE_ID
2509: FROM OKC_K_LINES_B
2510: WHERE ID = p_kle_Id)
2511: AND language = USERENV('lang');
2512:
2513: CURSOR c_txl_asset_number(p_asset_number OKX_ASSETS_V.ASSET_NUMBER%TYPE,
2549:
2550: CURSOR c_source_code(p_kle_Id OKL_TXL_ASSETS_V.KLE_ID%TYPE) is
2551: SELECT nvl(chr.orig_system_source_code,'x')
2552: FROM okc_k_headers_b chr,
2553: okc_k_lines_b cle
2554: WHERE cle.id = p_kle_Id
2555: AND cle.dnz_chr_id = chr.id;
2556:
2557: CURSOR c_check_release_asset(p_dnz_chr_id OKC_K_HEADERS_B.ID%TYPE) is
3495: p_ib_loc1 IN OKL_TXL_ITM_INSTS_V.OBJECT_ID1_NEW%TYPE,
3496: p_ib_loc2 IN OKL_TXL_ITM_INSTS_V.OBJECT_ID2_NEW%TYPE,
3497: p_fa_loc IN OKL_TXL_ASSETS_B.FA_LOCATION_ID%TYPE,
3498: p_refinance_amount IN OKL_K_LINES.REFINANCE_AMOUNT%TYPE,
3499: p_chr_id IN OKC_K_LINES_B.DNZ_CHR_ID%TYPE)
3500: RETURN VARCHAR2 IS
3501: x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
3502: ln_dummy NUMBER := 0;
3503: ln1_dummy NUMBER := 0;
3506: lv_deal_type OKL_K_HEADERS_V.DEAL_TYPE%TYPE := null;
3507: lv_scs_code OKC_K_HEADERS_V.SCS_CODE%TYPE := null;
3508: --Bug# 4419339
3509: l_orig_system_source_code okc_k_headers_b.orig_system_source_code%TYPE;
3510: CURSOR get_deal_type(p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE)
3511: IS
3512: SELECT khr.deal_type,
3513: chr.scs_code,
3514: --Bug# 4419339
4725: p_chr_id IN NUMBER) IS
4726: SELECT tal.id,
4727: tal.asset_number
4728: FROM okl_txl_assets_b tal,
4729: okc_k_lines_b cleb_fa
4730: WHERE cleb_fa.cle_id = p_cleb_fin_id
4731: AND cleb_fa.dnz_chr_id = p_chr_id
4732: AND cleb_fa.lse_id = 42
4733: AND tal.kle_id = cleb_fa.id;
4869:
4870: --Added by dpsingh for LE uptake
4871: CURSOR get_chr_id_csr(p_kle_id1 NUMBER) IS
4872: SELECT DNZ_CHR_ID
4873: FROM OKC_K_LINES_B
4874: WHERE ID = p_kle_id1;
4875:
4876: l_chr_id NUMBER;
4877: l_cntrct_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
5896: ln_line_number OKL_TXL_ASSETS_V.LINE_NUMBER%TYPE;
5897: l_api_name CONSTANT VARCHAR2(30) := 'CREATE_FA_LINE_ITEM';
5898: CURSOR c_asset_exist_chr(p_id1 OKX_ASSETS_V.ID1%TYPE,
5899: p_id2 OKX_ASSETS_V.ID2%TYPE,
5900: p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE)
5901: IS
5902: SELECT 1
5903: --FROM DUAL
5904: --WHERE EXISTS (SELECT '1'
6398: cim.number_of_items
6399: FROM okc_line_styles_b lse2,
6400: okc_line_styles_b lse1,
6401: okc_k_items cim,
6402: okc_k_lines_b cle
6403: WHERE cle.id = p_cle_id
6404: AND cle.dnz_chr_id = p_dnz_chr_id
6405: AND cle.id = cim.cle_id
6406: AND cim.dnz_chr_id = cle.dnz_chr_id
6607: cim.number_of_items
6608: FROM okc_line_styles_b lse2,
6609: okc_line_styles_b lse1,
6610: okc_k_items cim,
6611: okc_k_lines_b cle
6612: WHERE cle.id = p_cle_id
6613: AND cle.dnz_chr_id = p_dnz_chr_id
6614: AND cle.id = cim.cle_id
6615: AND cim.dnz_chr_id = cle.dnz_chr_id
6818: SELECT cle.id
6819: FROM okc_subclass_top_line stl,
6820: okc_line_styles_b lse2,
6821: okc_line_styles_b lse1,
6822: okc_k_lines_b cle
6823: WHERE cle.dnz_chr_id = p_dnz_chr_id
6824: AND cle.lse_id = lse1.id
6825: AND lse1.lty_code = G_FA_LINE_LTY_CODE
6826: AND lse1.lse_parent_id = lse2.id
6830: AND cle.cle_id in (SELECT distinct to_char(cle.cle_id)
6831: FROM okc_subclass_top_line stl,
6832: okc_line_styles_b lse2,
6833: okc_line_styles_b lse1,
6834: okc_k_lines_b cle
6835: WHERE cle.id = p_top_line
6836: AND cle.dnz_chr_id = p_dnz_chr_id
6837: AND cle.lse_id = lse1.id
6838: AND lse1.lty_code = G_MODEL_LINE_LTY_CODE
7016: SELECT cle.id
7017: FROM okc_subclass_top_line stl,
7018: okc_line_styles_b lse2,
7019: okc_line_styles_b lse1,
7020: okc_k_lines_b cle
7021: WHERE cle.dnz_chr_id = p_dnz_chr_id
7022: AND cle.lse_id = lse1.id
7023: AND lse1.lty_code = G_FA_LINE_LTY_CODE
7024: AND lse1.lse_parent_id = lse2.id
7028: AND cle.cle_id in (SELECT distinct to_char(cle.cle_id)
7029: FROM okc_subclass_top_line stl,
7030: okc_line_styles_b lse2,
7031: okc_line_styles_b lse1,
7032: okc_k_lines_b cle
7033: WHERE cle.id = p_top_line
7034: AND cle.dnz_chr_id = p_dnz_chr_id
7035: AND cle.lse_id = lse1.id
7036: AND lse1.lty_code = G_MODEL_LINE_LTY_CODE
8391: SELECT cle.id
8392: -- FROM okc_subclass_top_line stl,
8393: -- okc_line_styles_b lse2,
8394: -- okc_line_styles_b lse1,
8395: FROM okc_k_lines_b cle
8396: WHERE cle.cle_id = p_top_line
8397: AND cle.dnz_chr_id = p_dnz_chr_id
8398: -- AND lse1.id = cle.lse_id
8399: -- AND lse1.lty_code = G_FA_LINE_LTY_CODE
10340: CURSOR c_model_top_line(p_model_line_id OKC_K_LINES_V.ID%TYPE,
10341: p_dnz_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE) IS
10342: -- #4414408
10343: SELECT cle.cle_id
10344: FROM okc_k_lines_b cle
10345: WHERE cle.id = p_model_line_id
10346: AND cle.dnz_chr_id = p_dnz_chr_id
10347: AND cle.lse_id = G_MODEL_LINE_LTY_ID;
10348:
10625: CURSOR c_model_top_line(p_model_line_id OKC_K_LINES_V.ID%TYPE,
10626: p_dnz_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE) IS
10627: -- #4414408
10628: SELECT cle.cle_id
10629: FROM okc_k_lines_b cle
10630: WHERE cle.id = p_model_line_id
10631: AND cle.dnz_chr_id = p_dnz_chr_id
10632: AND cle.lse_id = G_MODEL_LINE_LTY_ID;
10633:
10872: CURSOR c_model_top_line(p_model_line_id OKC_K_LINES_V.ID%TYPE,
10873: p_dnz_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE) IS
10874: -- #4414408
10875: SELECT cle.cle_id
10876: FROM okc_k_lines_b cle
10877: WHERE cle.id = p_model_line_id
10878: AND cle.dnz_chr_id = p_dnz_chr_id
10879: AND cle.lse_id = G_MODEL_LINE_LTY_ID;
10880:
12450: trunc(decode(sign(cle_orig.end_date - cle_orig.date_terminated),-1,cle_orig.end_date,cle_orig.date_terminated)) date_terminated,
12451: --Bug# 6328924
12452: asr.legal_entity_id
12453: FROM OKL_ASSET_RETURNS_B asr,
12454: OKC_K_LINES_B cle_orig,
12455: OKC_LINE_STYLES_B lse_orig,
12456: OKC_K_ITEMS cim_orig
12457: WHERE asr.kle_id = cle_orig.cle_id
12458: AND asr.ars_code = 'RE_LEASE'
12474: chr_id,
12475: cle_id,
12476: dnz_chr_id,
12477: orig_system_id1
12478: FROM okc_k_lines_b
12479: CONNECT BY PRIOR id = cle_id
12480: START WITH id = p_from_cle_id;
12481:
12482: l_lines_rec l_lines_csr%ROWTYPE;
12511: txlb.deprn_rate,
12512: txlb.depreciation_id,
12513: txlb.asset_number
12514: from okl_txl_assets_b txlb,
12515: okc_k_lines_b fa_cleb,
12516: okc_line_styles_b fa_lseb
12517: where txlb.kle_id = fa_cleb.id
12518: and txlb.tal_type = 'CRL'
12519: and fa_cleb.cle_id = p_cle_id
12528: p_chr_id in number) IS
12529: select model_cleb.id model_cle_id,
12530: model_cim.id model_cim_id
12531: from okc_k_items model_cim,
12532: okc_k_lines_b model_cleb,
12533: okc_line_styles_b model_lseb
12534: where model_cim.cle_id = model_cleb.id
12535: and model_cim.dnz_chr_id = model_cleb.dnz_chr_id
12536: and model_cleb.cle_id = p_cle_id
12557: CURSOR l_addonline_csr (p_cle_id in number,
12558: p_chr_id in number) IS
12559:
12560: select addon_cleb.id addon_cle_id
12561: from okc_k_lines_b addon_cleb,
12562: okc_line_styles_b addon_lseb,
12563: okc_k_lines_b model_cleb,
12564: okc_line_styles_b model_lseb
12565: where addon_cleb.cle_id = model_cleb.id
12559:
12560: select addon_cleb.id addon_cle_id
12561: from okc_k_lines_b addon_cleb,
12562: okc_line_styles_b addon_lseb,
12563: okc_k_lines_b model_cleb,
12564: okc_line_styles_b model_lseb
12565: where addon_cleb.cle_id = model_cleb.id
12566: and addon_cleb.dnz_chr_id = model_cleb.dnz_chr_id
12567: and addon_cleb.lse_id = addon_lseb.id
12570: and model_cleb.dnz_chr_id = p_chr_id
12571: and model_cleb.lse_id = model_lseb.id
12572: and model_lseb.lty_code = 'ITEM';
12573:
12574: l_addonline_id OKC_K_LINES_B.ID%TYPE;
12575:
12576: --cursor to get any existing subsidies for deletion
12577: CURSOR l_subsidyline_csr (p_cle_id in number,
12578: p_chr_id in number) IS
12576: --cursor to get any existing subsidies for deletion
12577: CURSOR l_subsidyline_csr (p_cle_id in number,
12578: p_chr_id in number) IS
12579: select subsidy_cleb.id subsidy_cle_id
12580: from okc_k_lines_b subsidy_cleb,
12581: okc_line_styles_b subsidy_lseb
12582: where subsidy_cleb.cle_id = p_cle_id
12583: and subsidy_cleb.dnz_chr_id = p_chr_id
12584: and subsidy_cleb.lse_id = subsidy_lseb.id
12583: and subsidy_cleb.dnz_chr_id = p_chr_id
12584: and subsidy_cleb.lse_id = subsidy_lseb.id
12585: and subsidy_lseb.lty_code = 'SUBSIDY';
12586:
12587: l_subsidyline_id OKC_K_LINES_B.ID%TYPE;
12588:
12589:
12590: --cursor to get party roles attached to copied line
12591: CURSOR l_cpl_csr (p_cle_id in number,
13574: CURSOR l_lnk_ast_csr (p_orig_cle_id in NUMBER,
13575: p_chr_id in NUMBER) is
13576: SELECT cim.id
13577: FROM okc_k_items cim,
13578: okc_k_lines_b cleb,
13579: okc_line_styles_b lseb
13580: WHERE cim.object1_id1 = to_char(p_orig_cle_id)
13581: AND cim.object1_id2 = '#'
13582: AND cim.jtot_object1_code = 'OKX_COVASST'
13656: okl_subsidies_tl subt,
13657: po_vendors pov,
13658: okc_k_party_roles_b sub_cplb,
13659: okl_k_lines sub_kle,
13660: okc_k_lines_b sub_cleb,
13661: okc_line_styles_b sub_lseb
13662: where subt.id = subb.id
13663: and subt.language = userenv('LANG')
13664: and subb.id = sub_kle.subsidy_id
13921: select
13922: fa_cim.object1_id1 asset_id,
13923: fa_cim.dnz_chr_id chr_id
13924: from okc_k_items fa_cim,
13925: okc_k_lines_b fa_cleb,
13926: okc_line_styles_b fa_lseb
13927: where fa_cim.cle_id = fa_cleb.id
13928: and fa_cim.dnz_chr_id = fa_cleb.dnz_chr_id
13929: and fa_cleb.cle_id = p_fin_asst_id
13940: chr_id,
13941: cle_id,
13942: dnz_chr_id,
13943: orig_system_id1
13944: FROM okc_k_lines_b
13945: CONNECT BY PRIOR id = cle_id
13946: START WITH id = p_from_cle_id;
13947:
13948: l_lines_rec l_lines_csr%ROWTYPE;
13952: select 'N' change_flag,
13953: chrb.orig_system_source_code
13954: from fa_additions_b fab,
13955: okl_txl_assets_b txlb,
13956: okc_k_lines_b fa_cleb,
13957: okc_line_styles_b fa_lseb,
13958: okc_k_headers_b chrb
13959: where fab.asset_number = txlb.asset_number
13960: and fab.asset_id = p_asset_id
14144: l_api_name CONSTANT VARCHAR2(30) := 'RESOLVE_RESIDUL';
14145: l_api_version CONSTANT NUMBER := 1.0;
14146:
14147:
14148: l_top_line_id OKC_K_LINES_B.ID%TYPE;
14149: l_oec OKL_K_LINES.OEC%TYPE;
14150: l_residual_value OKL_K_LINES.RESIDUAL_VALUE%TYPE;
14151: lx_residual_value OKL_K_LINES.RESIDUAL_VALUE%TYPE;
14152:
14154: --cursor to get model line id
14155: CURSOR l_modelline_csr(p_cle_id IN NUMBER,
14156: p_chr_id IN NUMBER) IS
14157: SELECT model_cleb.id
14158: FROM OKC_K_LINES_B model_cleb,
14159: OKC_LINE_STYLES_B model_lseb
14160: WHERE model_cleb.cle_id = p_cle_id
14161: AND model_cleb.dnz_chr_id = p_chr_id
14162: AND model_cleb.lse_id = model_lseb.id
14161: AND model_cleb.dnz_chr_id = p_chr_id
14162: AND model_cleb.lse_id = model_lseb.id
14163: AND model_lseb.lty_code = 'ITEM';
14164:
14165: l_model_line_id OKC_K_LINES_B.ID%TYPE;
14166:
14167: --Bug# 4631549
14168: cursor l_exp_cost_csr (p_kle_id in number) is
14169: select kle.expected_asset_cost
14410: txlb.salvage_value,
14411: txlb.asset_number
14412: from okl_trx_assets trx,
14413: okl_txl_assets_b txlb,
14414: okc_k_lines_b cleb_fa,
14415: okc_line_styles_b lseb_fa
14416: where trx.id = txlb.tas_id
14417: and trx.tsu_code = 'ENTERED'
14418: --Bug# 3783518
14437: iti.object_id1_new,
14438: iti.object_id2_new
14439: from okl_trx_assets trx,
14440: okl_txl_itm_insts iti,
14441: okc_k_lines_b cleb_ib,
14442: okc_line_styles_b lseb_ib,
14443: okc_k_lines_b cleb_inst,
14444: okc_line_styles_b lseb_inst
14445: where trx.id = iti.tas_id
14439: from okl_trx_assets trx,
14440: okl_txl_itm_insts iti,
14441: okc_k_lines_b cleb_ib,
14442: okc_line_styles_b lseb_ib,
14443: okc_k_lines_b cleb_inst,
14444: okc_line_styles_b lseb_inst
14445: where trx.id = iti.tas_id
14446: and trx.tsu_code = 'ENTERED'
14447: --akrangan bug 5362977 start
14482: cleb.chr_id,
14483: cleb.cle_id,
14484: cleb.dnz_chr_id,
14485: cleb.lse_id
14486: FROM okc_k_lines_b cleb
14487: CONNECT BY PRIOR cleb.id = cle_id
14488: START WITH cleb.id = p_from_cle_id;
14489:
14490: l_lines_rec l_lines_csr%ROWTYPE;
14506: p_chr_id in number,
14507: p_lty_code in varchar2) is
14508: select cleb.id,
14509: cleb.price_unit
14510: from okc_k_lines_b cleb,
14511: okc_line_styles_b lseb
14512: where cleb.cle_id = p_cle_id
14513: and cleb.dnz_chr_id = p_chr_id
14514: and cleb.lse_id = lseb.id
15126: IS
15127: SELECT CII.SERIAL_NUMBER IB_SERIAL_NUMBER,
15128: CII.INSTANCE_ID
15129: FROM
15130: OKC_K_HEADERS_V OKHV, OKC_K_LINES_V KLE_FA, OKC_LINE_STYLES_B LSE_FA, OKC_K_LINES_B KLE_IL,
15131: OKC_LINE_STYLES_B LSE_IL, OKC_K_LINES_B KLE_IB, OKC_LINE_STYLES_B LSE_IB, OKC_K_ITEMS ITE,
15132: CSI_ITEM_INSTANCES CII
15133: WHERE
15134: kle_fa.chr_id = okhv.id AND lse_fa.id = kle_fa.lse_id AND lse_fa.lty_code = 'FREE_FORM1'
15127: SELECT CII.SERIAL_NUMBER IB_SERIAL_NUMBER,
15128: CII.INSTANCE_ID
15129: FROM
15130: OKC_K_HEADERS_V OKHV, OKC_K_LINES_V KLE_FA, OKC_LINE_STYLES_B LSE_FA, OKC_K_LINES_B KLE_IL,
15131: OKC_LINE_STYLES_B LSE_IL, OKC_K_LINES_B KLE_IB, OKC_LINE_STYLES_B LSE_IB, OKC_K_ITEMS ITE,
15132: CSI_ITEM_INSTANCES CII
15133: WHERE
15134: kle_fa.chr_id = okhv.id AND lse_fa.id = kle_fa.lse_id AND lse_fa.lty_code = 'FREE_FORM1'
15135: AND kle_il.cle_id = kle_fa.id AND lse_il.id = kle_il.lse_id AND lse_il.lty_code = 'FREE_FORM2'
16213: lv_jtot_object_code_new OKL_TXL_ITM_INSTS.JTOT_OBJECT_CODE_NEW%TYPE;
16214: -- end rravikir
16215:
16216: CURSOR c_asset_iti(p_asset_number OKL_TXL_ASSETS_B.ASSET_NUMBER%TYPE,
16217: p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE)
16218: IS
16219: select cle_ib.id id
16220: from okc_line_styles_b lse_ib,
16221: okc_k_lines_b cle_ib,
16217: p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE)
16218: IS
16219: select cle_ib.id id
16220: from okc_line_styles_b lse_ib,
16221: okc_k_lines_b cle_ib,
16222: okc_line_styles_b lse_inst,
16223: okc_k_lines_b cle_inst,
16224: okc_line_styles_b lse_tl,
16225: okc_k_lines_v cleb_tl
16219: select cle_ib.id id
16220: from okc_line_styles_b lse_ib,
16221: okc_k_lines_b cle_ib,
16222: okc_line_styles_b lse_inst,
16223: okc_k_lines_b cle_inst,
16224: okc_line_styles_b lse_tl,
16225: okc_k_lines_v cleb_tl
16226: where cleb_tl.name = P_asset_number
16227: and cleb_tl.dnz_chr_id = p_dnz_chr_id
16235: and cle_ib.lse_id = lse_ib.id
16236: and lse_ib.lty_code = G_IB_LINE_LTY_CODE;
16237:
16238: -- rravikir added
16239: CURSOR c_get_txl_itm_insts(p_top_line_id OKC_K_LINES_B.ID%TYPE,
16240: p_txl_inst_id OKL_TXL_ITM_INSTS.ID%TYPE)
16241: IS
16242: select iti.id, iti.inventory_item_id, iti.inventory_org_id,
16243: iti.object_id1_new, iti.object_id2_new, iti.jtot_object_code_new
16242: select iti.id, iti.inventory_item_id, iti.inventory_org_id,
16243: iti.object_id1_new, iti.object_id2_new, iti.jtot_object_code_new
16244: from okl_txl_itm_insts iti,
16245: okc_line_styles_b lse_ib,
16246: okc_k_lines_b cle_ib,
16247: okc_line_styles_b lse_inst,
16248: okc_k_lines_b cle_inst
16249: where cle_inst.cle_id = p_top_line_id
16250: and cle_inst.lse_id = lse_inst.id
16244: from okl_txl_itm_insts iti,
16245: okc_line_styles_b lse_ib,
16246: okc_k_lines_b cle_ib,
16247: okc_line_styles_b lse_inst,
16248: okc_k_lines_b cle_inst
16249: where cle_inst.cle_id = p_top_line_id
16250: and cle_inst.lse_id = lse_inst.id
16251: and lse_inst.lty_code = G_INST_LINE_LTY_CODE
16252: and cle_ib.cle_id = cle_inst.id
16272: fin_kle.residual_value,
16273: fin_kle.residual_percentage
16274: from
16275: okl_k_lines fin_kle,
16276: okc_k_lines_b fin_cleb
16277: where fin_kle.id = fin_cleb.id
16278: and fin_cleb.id = p_cle_id
16279: and fin_cleb.chr_id = p_chr_id
16280: and fin_cleb.dnz_chr_id = p_chr_id;
16322: --Bug# 4161221: start
16323: CURSOR c_addon_line_id(p_model_cle_id OKC_K_LINES_V.CLE_ID%TYPE,
16324: p_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE) IS
16325: SELECT cle.id
16326: FROM okc_k_lines_b cle,
16327: okc_line_styles_b lse
16328: WHERE cle.dnz_chr_id = p_chr_id
16329: AND cle.lse_id = lse.id
16330: AND lse.lty_code = G_ADDON_LINE_LTY_CODE
16358: --Bug# 4631549
16359: cursor l_orig_cle_csr (p_cle_id in number
16360: ) is
16361: select cleb.orig_system_id1 orig_cle_id
16362: from okc_k_lines_b cleb
16363: where cleb.id = p_cle_id;
16364:
16365: l_orig_cle_rec l_orig_cle_csr%ROWTYPE;
16366:
17935: FROM okc_subclass_top_line stl,
17936: okc_line_styles_b lse3,
17937: okc_line_styles_b lse2,
17938: okc_line_styles_b lse1,
17939: okc_k_lines_b cle
17940: WHERE cle.dnz_chr_id = p_chr_id
17941: AND cle.lse_id = lse1.id
17942: AND lse1.lty_code = G_ADDON_LINE_LTY_CODE
17943: AND lse1.lse_parent_id = lse2.id
17949: AND cle.cle_id in (SELECT cle.id
17950: FROM okc_subclass_top_line stl,
17951: okc_line_styles_b lse2,
17952: okc_line_styles_b lse1,
17953: okc_k_lines_b cle
17954: WHERE cle.cle_id = p_cle_id
17955: AND cle.dnz_chr_id = p_chr_id
17956: AND cle.lse_id = lse1.id
17957: AND lse1.lty_code = G_MODEL_LINE_LTY_CODE
18975: FROM okc_subclass_top_line stl,
18976: okc_line_styles_b lse3,
18977: okc_line_styles_b lse2,
18978: okc_line_styles_b lse1,
18979: okc_k_lines_b cle
18980: WHERE cle.dnz_chr_id = p_chr_id
18981: AND cle.lse_id = lse1.id
18982: AND lse1.lty_code = G_ADDON_LINE_LTY_CODE
18983: AND lse1.lse_parent_id = lse2.id
18989: AND cle.cle_id in (SELECT cle.id
18990: FROM okc_subclass_top_line stl,
18991: okc_line_styles_b lse2,
18992: okc_line_styles_b lse1,
18993: okc_k_lines_b cle
18994: WHERE cle.cle_id = p_cle_id
18995: AND cle.dnz_chr_id = p_chr_id
18996: AND cle.lse_id = lse1.id
18997: AND lse1.lty_code = G_MODEL_LINE_LTY_CODE
19856: FROM okc_subclass_top_line stl,
19857: okc_line_styles_b lse3,
19858: okc_line_styles_b lse2,
19859: okc_line_styles_b lse1,
19860: okc_k_lines_b cle
19861: WHERE cle.dnz_chr_id = p_chr_id
19862: AND cle.lse_id = lse1.id
19863: AND lse1.lty_code = G_ADDON_LINE_LTY_CODE
19864: AND lse1.lse_parent_id = lse2.id
19870: AND cle.cle_id in (SELECT cle.id
19871: FROM okc_subclass_top_line stl,
19872: okc_line_styles_b lse2,
19873: okc_line_styles_b lse1,
19874: okc_k_lines_b cle
19875: WHERE cle.cle_id = p_cle_id
19876: AND cle.dnz_chr_id = p_chr_id
19877: AND cle.lse_id = lse1.id
19878: AND lse1.lty_code = G_MODEL_LINE_LTY_CODE
19933: l_clev_ib_tbl(j).id = OKL_API.G_MISS_NUM THEN
19934: OKL_API.set_message(p_app_name => G_APP_NAME,
19935: p_msg_name => G_REQUIRED_VALUE,
19936: p_token1 => G_COL_NAME_TOKEN,
19937: p_token1_value => 'IB OKC_K_LINES_B.ID');
19938: EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
19939: END IF;
19940: x_return_status := get_rec_clev(l_clev_ib_tbl(j).id,
19941: l_clev_ib_rec);