[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: --Bug# 15992711 : The re-leased flag is no longer checked at contract header
2568: FROM okl_asset_returns_all_b KAR
2569: WHERE KAR.kle_id IN (SELECT KLE.cle_id
2570: FROM okl_txl_assets_b TXL
2571: ,okl_txd_assets_b TXD
2572: ,okc_k_lines_b KLE
2573: WHERE TXD.asset_number = p_asset_number
2574: AND KLE.id = TXL.kle_id
2575: AND TXL.id = TXD.tal_id )
2576: AND KAR.ars_code = 'RE_LEASE';
2929: FROM okl_asset_returns_all_b KAR
2930: WHERE KAR.kle_id IN (SELECT KLE.cle_id
2931: FROM okl_txl_assets_b TXL
2932: ,okl_txd_assets_b TXD
2933: ,okc_k_lines_b KLE
2934: WHERE TXD.asset_number = p_asset_number
2935: AND KLE.id = TXL.kle_id
2936: AND TXL.id = TXD.tal_id )
2937: AND KAR.ars_code = 'RE_LEASE';
3536: p_ib_loc1 IN OKL_TXL_ITM_INSTS_V.OBJECT_ID1_NEW%TYPE,
3537: p_ib_loc2 IN OKL_TXL_ITM_INSTS_V.OBJECT_ID2_NEW%TYPE,
3538: p_fa_loc IN OKL_TXL_ASSETS_B.FA_LOCATION_ID%TYPE,
3539: p_refinance_amount IN OKL_K_LINES.REFINANCE_AMOUNT%TYPE,
3540: p_chr_id IN OKC_K_LINES_B.DNZ_CHR_ID%TYPE)
3541: RETURN VARCHAR2 IS
3542: x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
3543: ln_dummy NUMBER := 0;
3544: ln1_dummy NUMBER := 0;
3547: lv_deal_type OKL_K_HEADERS_V.DEAL_TYPE%TYPE := null;
3548: lv_scs_code OKC_K_HEADERS_V.SCS_CODE%TYPE := null;
3549: --Bug# 4419339
3550: l_orig_system_source_code okc_k_headers_b.orig_system_source_code%TYPE;
3551: CURSOR get_deal_type(p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE)
3552: IS
3553: SELECT khr.deal_type,
3554: chr.scs_code,
3555: --Bug# 4419339
4766: p_chr_id IN NUMBER) IS
4767: SELECT tal.id,
4768: tal.asset_number
4769: FROM okl_txl_assets_b tal,
4770: okc_k_lines_b cleb_fa
4771: WHERE cleb_fa.cle_id = p_cleb_fin_id
4772: AND cleb_fa.dnz_chr_id = p_chr_id
4773: AND cleb_fa.lse_id = 42
4774: AND tal.kle_id = cleb_fa.id;
4910:
4911: --Added by dpsingh for LE uptake
4912: CURSOR get_chr_id_csr(p_kle_id1 NUMBER) IS
4913: SELECT DNZ_CHR_ID
4914: FROM OKC_K_LINES_B
4915: WHERE ID = p_kle_id1;
4916:
4917: l_chr_id NUMBER;
4918: l_cntrct_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
5937: ln_line_number OKL_TXL_ASSETS_V.LINE_NUMBER%TYPE;
5938: l_api_name CONSTANT VARCHAR2(30) := 'CREATE_FA_LINE_ITEM';
5939: CURSOR c_asset_exist_chr(p_id1 OKX_ASSETS_V.ID1%TYPE,
5940: p_id2 OKX_ASSETS_V.ID2%TYPE,
5941: p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE)
5942: IS
5943: SELECT 1
5944: --FROM DUAL
5945: --WHERE EXISTS (SELECT '1'
6439: cim.number_of_items
6440: FROM okc_line_styles_b lse2,
6441: okc_line_styles_b lse1,
6442: okc_k_items cim,
6443: okc_k_lines_b cle
6444: WHERE cle.id = p_cle_id
6445: AND cle.dnz_chr_id = p_dnz_chr_id
6446: AND cle.id = cim.cle_id
6447: AND cim.dnz_chr_id = cle.dnz_chr_id
6648: cim.number_of_items
6649: FROM okc_line_styles_b lse2,
6650: okc_line_styles_b lse1,
6651: okc_k_items cim,
6652: okc_k_lines_b cle
6653: WHERE cle.id = p_cle_id
6654: AND cle.dnz_chr_id = p_dnz_chr_id
6655: AND cle.id = cim.cle_id
6656: AND cim.dnz_chr_id = cle.dnz_chr_id
6859: SELECT cle.id
6860: FROM okc_subclass_top_line stl,
6861: okc_line_styles_b lse2,
6862: okc_line_styles_b lse1,
6863: okc_k_lines_b cle
6864: WHERE cle.dnz_chr_id = p_dnz_chr_id
6865: AND cle.lse_id = lse1.id
6866: AND lse1.lty_code = G_FA_LINE_LTY_CODE
6867: AND lse1.lse_parent_id = lse2.id
6871: AND cle.cle_id in (SELECT distinct to_char(cle.cle_id)
6872: FROM okc_subclass_top_line stl,
6873: okc_line_styles_b lse2,
6874: okc_line_styles_b lse1,
6875: okc_k_lines_b cle
6876: WHERE cle.id = p_top_line
6877: AND cle.dnz_chr_id = p_dnz_chr_id
6878: AND cle.lse_id = lse1.id
6879: AND lse1.lty_code = G_MODEL_LINE_LTY_CODE
7057: SELECT cle.id
7058: FROM okc_subclass_top_line stl,
7059: okc_line_styles_b lse2,
7060: okc_line_styles_b lse1,
7061: okc_k_lines_b cle
7062: WHERE cle.dnz_chr_id = p_dnz_chr_id
7063: AND cle.lse_id = lse1.id
7064: AND lse1.lty_code = G_FA_LINE_LTY_CODE
7065: AND lse1.lse_parent_id = lse2.id
7069: AND cle.cle_id in (SELECT distinct to_char(cle.cle_id)
7070: FROM okc_subclass_top_line stl,
7071: okc_line_styles_b lse2,
7072: okc_line_styles_b lse1,
7073: okc_k_lines_b cle
7074: WHERE cle.id = p_top_line
7075: AND cle.dnz_chr_id = p_dnz_chr_id
7076: AND cle.lse_id = lse1.id
7077: AND lse1.lty_code = G_MODEL_LINE_LTY_CODE
8432: SELECT cle.id
8433: -- FROM okc_subclass_top_line stl,
8434: -- okc_line_styles_b lse2,
8435: -- okc_line_styles_b lse1,
8436: FROM okc_k_lines_b cle
8437: WHERE cle.cle_id = p_top_line
8438: AND cle.dnz_chr_id = p_dnz_chr_id
8439: -- AND lse1.id = cle.lse_id
8440: -- AND lse1.lty_code = G_FA_LINE_LTY_CODE
10425: CURSOR c_model_top_line(p_model_line_id OKC_K_LINES_V.ID%TYPE,
10426: p_dnz_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE) IS
10427: -- #4414408
10428: SELECT cle.cle_id
10429: FROM okc_k_lines_b cle
10430: WHERE cle.id = p_model_line_id
10431: AND cle.dnz_chr_id = p_dnz_chr_id
10432: AND cle.lse_id = G_MODEL_LINE_LTY_ID;
10433:
10710: CURSOR c_model_top_line(p_model_line_id OKC_K_LINES_V.ID%TYPE,
10711: p_dnz_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE) IS
10712: -- #4414408
10713: SELECT cle.cle_id
10714: FROM okc_k_lines_b cle
10715: WHERE cle.id = p_model_line_id
10716: AND cle.dnz_chr_id = p_dnz_chr_id
10717: AND cle.lse_id = G_MODEL_LINE_LTY_ID;
10718:
10957: CURSOR c_model_top_line(p_model_line_id OKC_K_LINES_V.ID%TYPE,
10958: p_dnz_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE) IS
10959: -- #4414408
10960: SELECT cle.cle_id
10961: FROM okc_k_lines_b cle
10962: WHERE cle.id = p_model_line_id
10963: AND cle.dnz_chr_id = p_dnz_chr_id
10964: AND cle.lse_id = G_MODEL_LINE_LTY_ID;
10965:
12535: trunc(decode(sign(cle_orig.end_date - cle_orig.date_terminated),-1,cle_orig.end_date,cle_orig.date_terminated)) date_terminated,
12536: --Bug# 6328924
12537: asr.legal_entity_id
12538: FROM OKL_ASSET_RETURNS_B asr,
12539: OKC_K_LINES_B cle_orig,
12540: OKC_LINE_STYLES_B lse_orig,
12541: OKC_K_ITEMS cim_orig
12542: WHERE asr.kle_id = cle_orig.cle_id
12543: AND asr.ars_code = 'RE_LEASE'
12560: cle_id,
12561: dnz_chr_id,
12562: orig_system_id1,
12563: lse_id -- Bug # 15992711 added the column
12564: FROM okc_k_lines_b
12565: CONNECT BY PRIOR id = cle_id
12566: START WITH id = p_from_cle_id;
12567:
12568: l_lines_rec l_lines_csr%ROWTYPE;
12597: txlb.deprn_rate,
12598: txlb.depreciation_id,
12599: txlb.asset_number
12600: from okl_txl_assets_b txlb,
12601: okc_k_lines_b fa_cleb,
12602: okc_line_styles_b fa_lseb
12603: where txlb.kle_id = fa_cleb.id
12604: and txlb.tal_type = 'CRL'
12605: and fa_cleb.cle_id = p_cle_id
12614: p_chr_id in number) IS
12615: select model_cleb.id model_cle_id,
12616: model_cim.id model_cim_id
12617: from okc_k_items model_cim,
12618: okc_k_lines_b model_cleb,
12619: okc_line_styles_b model_lseb
12620: where model_cim.cle_id = model_cleb.id
12621: and model_cim.dnz_chr_id = model_cleb.dnz_chr_id
12622: and model_cleb.cle_id = p_cle_id
12643: CURSOR l_addonline_csr (p_cle_id in number,
12644: p_chr_id in number) IS
12645:
12646: select addon_cleb.id addon_cle_id
12647: from okc_k_lines_b addon_cleb,
12648: okc_line_styles_b addon_lseb,
12649: okc_k_lines_b model_cleb,
12650: okc_line_styles_b model_lseb
12651: where addon_cleb.cle_id = model_cleb.id
12645:
12646: select addon_cleb.id addon_cle_id
12647: from okc_k_lines_b addon_cleb,
12648: okc_line_styles_b addon_lseb,
12649: okc_k_lines_b model_cleb,
12650: okc_line_styles_b model_lseb
12651: where addon_cleb.cle_id = model_cleb.id
12652: and addon_cleb.dnz_chr_id = model_cleb.dnz_chr_id
12653: and addon_cleb.lse_id = addon_lseb.id
12656: and model_cleb.dnz_chr_id = p_chr_id
12657: and model_cleb.lse_id = model_lseb.id
12658: and model_lseb.lty_code = 'ITEM';
12659:
12660: l_addonline_id OKC_K_LINES_B.ID%TYPE;
12661:
12662: --cursor to get any existing subsidies for deletion
12663: CURSOR l_subsidyline_csr (p_cle_id in number,
12664: p_chr_id in number) IS
12662: --cursor to get any existing subsidies for deletion
12663: CURSOR l_subsidyline_csr (p_cle_id in number,
12664: p_chr_id in number) IS
12665: select subsidy_cleb.id subsidy_cle_id
12666: from okc_k_lines_b subsidy_cleb,
12667: okc_line_styles_b subsidy_lseb
12668: where subsidy_cleb.cle_id = p_cle_id
12669: and subsidy_cleb.dnz_chr_id = p_chr_id
12670: and subsidy_cleb.lse_id = subsidy_lseb.id
12669: and subsidy_cleb.dnz_chr_id = p_chr_id
12670: and subsidy_cleb.lse_id = subsidy_lseb.id
12671: and subsidy_lseb.lty_code = 'SUBSIDY';
12672:
12673: l_subsidyline_id OKC_K_LINES_B.ID%TYPE;
12674:
12675:
12676: --cursor to get party roles attached to copied line
12677: CURSOR l_cpl_csr (p_cle_id in number,
13673: CURSOR l_lnk_ast_csr (p_orig_cle_id in NUMBER,
13674: p_chr_id in NUMBER) is
13675: SELECT cim.id
13676: FROM okc_k_items cim,
13677: okc_k_lines_b cleb,
13678: okc_line_styles_b lseb
13679: WHERE cim.object1_id1 = to_char(p_orig_cle_id)
13680: AND cim.object1_id2 = '#'
13681: AND cim.jtot_object1_code = 'OKX_COVASST'
13755: okl_subsidies_tl subt,
13756: po_vendors pov,
13757: okc_k_party_roles_b sub_cplb,
13758: okl_k_lines sub_kle,
13759: okc_k_lines_b sub_cleb,
13760: okc_line_styles_b sub_lseb
13761: where subt.id = subb.id
13762: and subt.language = userenv('LANG')
13763: and subb.id = sub_kle.subsidy_id
14020: select
14021: fa_cim.object1_id1 asset_id,
14022: fa_cim.dnz_chr_id chr_id
14023: from okc_k_items fa_cim,
14024: okc_k_lines_b fa_cleb,
14025: okc_line_styles_b fa_lseb
14026: where fa_cim.cle_id = fa_cleb.id
14027: and fa_cim.dnz_chr_id = fa_cleb.dnz_chr_id
14028: and fa_cleb.cle_id = p_fin_asst_id
14039: chr_id,
14040: cle_id,
14041: dnz_chr_id,
14042: orig_system_id1
14043: FROM okc_k_lines_b
14044: CONNECT BY PRIOR id = cle_id
14045: START WITH id = p_from_cle_id;
14046:
14047: l_lines_rec l_lines_csr%ROWTYPE;
14051: select 'N' change_flag,
14052: chrb.orig_system_source_code
14053: from fa_additions_b fab,
14054: okl_txl_assets_b txlb,
14055: okc_k_lines_b fa_cleb,
14056: okc_line_styles_b fa_lseb,
14057: okc_k_headers_b chrb
14058: where fab.asset_number = txlb.asset_number
14059: and fab.asset_id = p_asset_id
14250: l_api_name CONSTANT VARCHAR2(30) := 'RESOLVE_RESIDUL';
14251: l_api_version CONSTANT NUMBER := 1.0;
14252:
14253:
14254: l_top_line_id OKC_K_LINES_B.ID%TYPE;
14255: l_oec OKL_K_LINES.OEC%TYPE;
14256: l_residual_value OKL_K_LINES.RESIDUAL_VALUE%TYPE;
14257: lx_residual_value OKL_K_LINES.RESIDUAL_VALUE%TYPE;
14258:
14260: --cursor to get model line id
14261: CURSOR l_modelline_csr(p_cle_id IN NUMBER,
14262: p_chr_id IN NUMBER) IS
14263: SELECT model_cleb.id
14264: FROM OKC_K_LINES_B model_cleb,
14265: OKC_LINE_STYLES_B model_lseb
14266: WHERE model_cleb.cle_id = p_cle_id
14267: AND model_cleb.dnz_chr_id = p_chr_id
14268: AND model_cleb.lse_id = model_lseb.id
14267: AND model_cleb.dnz_chr_id = p_chr_id
14268: AND model_cleb.lse_id = model_lseb.id
14269: AND model_lseb.lty_code = 'ITEM';
14270:
14271: l_model_line_id OKC_K_LINES_B.ID%TYPE;
14272:
14273: --Bug# 4631549
14274: cursor l_exp_cost_csr (p_kle_id in number) is
14275: select kle.expected_asset_cost
14516: txlb.salvage_value,
14517: txlb.asset_number
14518: from okl_trx_assets trx,
14519: okl_txl_assets_b txlb,
14520: okc_k_lines_b cleb_fa,
14521: okc_line_styles_b lseb_fa
14522: where trx.id = txlb.tas_id
14523: and trx.tsu_code = 'ENTERED'
14524: --Bug# 3783518
14543: iti.object_id1_new,
14544: iti.object_id2_new
14545: from okl_trx_assets trx,
14546: okl_txl_itm_insts iti,
14547: okc_k_lines_b cleb_ib,
14548: okc_line_styles_b lseb_ib,
14549: okc_k_lines_b cleb_inst,
14550: okc_line_styles_b lseb_inst
14551: where trx.id = iti.tas_id
14545: from okl_trx_assets trx,
14546: okl_txl_itm_insts iti,
14547: okc_k_lines_b cleb_ib,
14548: okc_line_styles_b lseb_ib,
14549: okc_k_lines_b cleb_inst,
14550: okc_line_styles_b lseb_inst
14551: where trx.id = iti.tas_id
14552: and trx.tsu_code = 'ENTERED'
14553: --akrangan bug 5362977 start
14588: cleb.chr_id,
14589: cleb.cle_id,
14590: cleb.dnz_chr_id,
14591: cleb.lse_id
14592: FROM okc_k_lines_b cleb
14593: CONNECT BY PRIOR cleb.id = cle_id
14594: START WITH cleb.id = p_from_cle_id;
14595:
14596: l_lines_rec l_lines_csr%ROWTYPE;
14612: p_chr_id in number,
14613: p_lty_code in varchar2) is
14614: select cleb.id,
14615: cleb.price_unit
14616: from okc_k_lines_b cleb,
14617: okc_line_styles_b lseb
14618: where cleb.cle_id = p_cle_id
14619: and cleb.dnz_chr_id = p_chr_id
14620: and cleb.lse_id = lseb.id
15235: IS
15236: SELECT CII.SERIAL_NUMBER IB_SERIAL_NUMBER,
15237: CII.INSTANCE_ID
15238: FROM
15239: OKC_K_HEADERS_V OKHV, OKC_K_LINES_V KLE_FA, OKC_LINE_STYLES_B LSE_FA, OKC_K_LINES_B KLE_IL,
15240: OKC_LINE_STYLES_B LSE_IL, OKC_K_LINES_B KLE_IB, OKC_LINE_STYLES_B LSE_IB, OKC_K_ITEMS ITE,
15241: CSI_ITEM_INSTANCES CII
15242: WHERE
15243: kle_fa.chr_id = okhv.id AND lse_fa.id = kle_fa.lse_id AND lse_fa.lty_code = 'FREE_FORM1'
15236: SELECT CII.SERIAL_NUMBER IB_SERIAL_NUMBER,
15237: CII.INSTANCE_ID
15238: FROM
15239: OKC_K_HEADERS_V OKHV, OKC_K_LINES_V KLE_FA, OKC_LINE_STYLES_B LSE_FA, OKC_K_LINES_B KLE_IL,
15240: OKC_LINE_STYLES_B LSE_IL, OKC_K_LINES_B KLE_IB, OKC_LINE_STYLES_B LSE_IB, OKC_K_ITEMS ITE,
15241: CSI_ITEM_INSTANCES CII
15242: WHERE
15243: kle_fa.chr_id = okhv.id AND lse_fa.id = kle_fa.lse_id AND lse_fa.lty_code = 'FREE_FORM1'
15244: AND kle_il.cle_id = kle_fa.id AND lse_il.id = kle_il.lse_id AND lse_il.lty_code = 'FREE_FORM2'
15279: FROM okl_asset_returns_all_b KAR
15280: WHERE KAR.kle_id IN (SELECT KLE.cle_id
15281: FROM okl_txl_assets_b TXL
15282: ,okl_txd_assets_b TXD
15283: ,okc_k_lines_b KLE
15284: WHERE TXD.asset_number = p_asset_number
15285: AND KLE.id = TXL.kle_id
15286: AND TXL.id = TXD.tal_id )
15287: AND KAR.ars_code = 'RE_LEASE');
16351: lv_jtot_object_code_new OKL_TXL_ITM_INSTS.JTOT_OBJECT_CODE_NEW%TYPE;
16352: -- end rravikir
16353:
16354: CURSOR c_asset_iti(p_asset_number OKL_TXL_ASSETS_B.ASSET_NUMBER%TYPE,
16355: p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE)
16356: IS
16357: select cle_ib.id id
16358: from okc_line_styles_b lse_ib,
16359: okc_k_lines_b cle_ib,
16355: p_dnz_chr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE)
16356: IS
16357: select cle_ib.id id
16358: from okc_line_styles_b lse_ib,
16359: okc_k_lines_b cle_ib,
16360: okc_line_styles_b lse_inst,
16361: okc_k_lines_b cle_inst,
16362: okc_line_styles_b lse_tl,
16363: okc_k_lines_v cleb_tl
16357: select cle_ib.id id
16358: from okc_line_styles_b lse_ib,
16359: okc_k_lines_b cle_ib,
16360: okc_line_styles_b lse_inst,
16361: okc_k_lines_b cle_inst,
16362: okc_line_styles_b lse_tl,
16363: okc_k_lines_v cleb_tl
16364: where cleb_tl.name = P_asset_number
16365: and cleb_tl.dnz_chr_id = p_dnz_chr_id
16373: and cle_ib.lse_id = lse_ib.id
16374: and lse_ib.lty_code = G_IB_LINE_LTY_CODE;
16375:
16376: -- rravikir added
16377: CURSOR c_get_txl_itm_insts(p_top_line_id OKC_K_LINES_B.ID%TYPE,
16378: p_txl_inst_id OKL_TXL_ITM_INSTS.ID%TYPE)
16379: IS
16380: select iti.id, iti.inventory_item_id, iti.inventory_org_id,
16381: iti.object_id1_new, iti.object_id2_new, iti.jtot_object_code_new
16380: select iti.id, iti.inventory_item_id, iti.inventory_org_id,
16381: iti.object_id1_new, iti.object_id2_new, iti.jtot_object_code_new
16382: from okl_txl_itm_insts iti,
16383: okc_line_styles_b lse_ib,
16384: okc_k_lines_b cle_ib,
16385: okc_line_styles_b lse_inst,
16386: okc_k_lines_b cle_inst
16387: where cle_inst.cle_id = p_top_line_id
16388: and cle_inst.lse_id = lse_inst.id
16382: from okl_txl_itm_insts iti,
16383: okc_line_styles_b lse_ib,
16384: okc_k_lines_b cle_ib,
16385: okc_line_styles_b lse_inst,
16386: okc_k_lines_b cle_inst
16387: where cle_inst.cle_id = p_top_line_id
16388: and cle_inst.lse_id = lse_inst.id
16389: and lse_inst.lty_code = G_INST_LINE_LTY_CODE
16390: and cle_ib.cle_id = cle_inst.id
16410: fin_kle.residual_value,
16411: fin_kle.residual_percentage
16412: from
16413: okl_k_lines fin_kle,
16414: okc_k_lines_b fin_cleb
16415: where fin_kle.id = fin_cleb.id
16416: and fin_cleb.id = p_cle_id
16417: and fin_cleb.chr_id = p_chr_id
16418: and fin_cleb.dnz_chr_id = p_chr_id;
16453: chr.start_date
16454: FROM okc_k_headers_b CHR,
16455: okl_txl_assets_b txlb,
16456: okc_line_styles_b lseb,
16457: okc_k_lines_b cleb_top,
16458: okl_k_lines kle_top
16459: WHERE txlb.kle_id = cleb_top.id
16460: AND cleb_top.lse_id = lseb.id
16461: AND lseb.lty_code = 'FIXED_ASSET'
16484: --Bug# 4161221: start
16485: CURSOR c_addon_line_id(p_model_cle_id OKC_K_LINES_V.CLE_ID%TYPE,
16486: p_chr_id OKC_K_LINES_V.DNZ_CHR_ID%TYPE) IS
16487: SELECT cle.id
16488: FROM okc_k_lines_b cle,
16489: okc_line_styles_b lse
16490: WHERE cle.dnz_chr_id = p_chr_id
16491: AND cle.lse_id = lse.id
16492: AND lse.lty_code = G_ADDON_LINE_LTY_CODE
16520: --Bug# 4631549
16521: cursor l_orig_cle_csr (p_cle_id in number
16522: ) is
16523: select cleb.orig_system_id1 orig_cle_id
16524: from okc_k_lines_b cleb
16525: where cleb.id = p_cle_id;
16526:
16527: l_orig_cle_rec l_orig_cle_csr%ROWTYPE;
16528:
16552:
16553: CURSOR c_get_line_details(p_dnz_chr_id NUMBER, p_clev_fin_id NUMBER) IS
16554: SELECT cim_mdl.number_of_items,
16555: cleb_mdl.price_unit
16556: FROM okc_k_lines_b cleb_mdl,
16557: okc_line_styles_b lse_mdl,
16558: okc_k_items cim_mdl
16559: WHERE cim_mdl.cle_id = cleb_mdl.id
16560: AND cim_mdl.dnz_chr_id = cleb_mdl.dnz_chr_id
18142: FROM okc_subclass_top_line stl,
18143: okc_line_styles_b lse3,
18144: okc_line_styles_b lse2,
18145: okc_line_styles_b lse1,
18146: okc_k_lines_b cle
18147: WHERE cle.dnz_chr_id = p_chr_id
18148: AND cle.lse_id = lse1.id
18149: AND lse1.lty_code = G_ADDON_LINE_LTY_CODE
18150: AND lse1.lse_parent_id = lse2.id
18156: AND cle.cle_id in (SELECT cle.id
18157: FROM okc_subclass_top_line stl,
18158: okc_line_styles_b lse2,
18159: okc_line_styles_b lse1,
18160: okc_k_lines_b cle
18161: WHERE cle.cle_id = p_cle_id
18162: AND cle.dnz_chr_id = p_chr_id
18163: AND cle.lse_id = lse1.id
18164: AND lse1.lty_code = G_MODEL_LINE_LTY_CODE
18202: ,p_asset_num IN OKX_ASSETS_V.ASSET_NUMBER%TYPE) is
18203: SELECT NVL(kle_top.re_lease_yn,'N') release_flag
18204: FROM okl_txl_assets_b txlb,
18205: okc_line_styles_b lseb,
18206: okc_k_lines_b cleb_top,
18207: okl_k_lines kle_top
18208: WHERE txlb.kle_id = cleb_top.id
18209: AND cleb_top.lse_id = lseb.id
18210: AND lseb.lty_code = 'FIXED_ASSET'
19206: FROM okc_subclass_top_line stl,
19207: okc_line_styles_b lse3,
19208: okc_line_styles_b lse2,
19209: okc_line_styles_b lse1,
19210: okc_k_lines_b cle
19211: WHERE cle.dnz_chr_id = p_chr_id
19212: AND cle.lse_id = lse1.id
19213: AND lse1.lty_code = G_ADDON_LINE_LTY_CODE
19214: AND lse1.lse_parent_id = lse2.id
19220: AND cle.cle_id in (SELECT cle.id
19221: FROM okc_subclass_top_line stl,
19222: okc_line_styles_b lse2,
19223: okc_line_styles_b lse1,
19224: okc_k_lines_b cle
19225: WHERE cle.cle_id = p_cle_id
19226: AND cle.dnz_chr_id = p_chr_id
19227: AND cle.lse_id = lse1.id
19228: AND lse1.lty_code = G_MODEL_LINE_LTY_CODE
19246: ,p_asset_num IN OKX_ASSETS_V.ASSET_NUMBER%TYPE) is
19247: SELECT NVL(kle_top.re_lease_yn,'N') release_flag
19248: FROM okl_txl_assets_b txlb,
19249: okc_line_styles_b lseb,
19250: okc_k_lines_b cleb_top,
19251: okl_k_lines kle_top
19252: WHERE txlb.kle_id = cleb_top.id
19253: AND cleb_top.lse_id = lseb.id
19254: AND lseb.lty_code = 'FIXED_ASSET'
20112: FROM okc_subclass_top_line stl,
20113: okc_line_styles_b lse3,
20114: okc_line_styles_b lse2,
20115: okc_line_styles_b lse1,
20116: okc_k_lines_b cle
20117: WHERE cle.dnz_chr_id = p_chr_id
20118: AND cle.lse_id = lse1.id
20119: AND lse1.lty_code = G_ADDON_LINE_LTY_CODE
20120: AND lse1.lse_parent_id = lse2.id
20126: AND cle.cle_id in (SELECT cle.id
20127: FROM okc_subclass_top_line stl,
20128: okc_line_styles_b lse2,
20129: okc_line_styles_b lse1,
20130: okc_k_lines_b cle
20131: WHERE cle.cle_id = p_cle_id
20132: AND cle.dnz_chr_id = p_chr_id
20133: AND cle.lse_id = lse1.id
20134: AND lse1.lty_code = G_MODEL_LINE_LTY_CODE
20152: ,p_asset_number IN OKX_ASSETS_V.ASSET_NUMBER%TYPE) is
20153: SELECT NVL(kle_top.re_lease_yn,'N') release_flag
20154: FROM okl_txl_assets_b txlb,
20155: okc_line_styles_b lseb,
20156: okc_k_lines_b cleb_top,
20157: okl_k_lines kle_top
20158: WHERE txlb.kle_id = cleb_top.id
20159: AND cleb_top.lse_id = lseb.id
20160: AND lseb.lty_code = 'FIXED_ASSET'
20208: l_clev_ib_tbl(j).id = OKL_API.G_MISS_NUM THEN
20209: OKL_API.set_message(p_app_name => G_APP_NAME,
20210: p_msg_name => G_REQUIRED_VALUE,
20211: p_token1 => G_COL_NAME_TOKEN,
20212: p_token1_value => 'IB OKC_K_LINES_B.ID');
20213: EXIT WHEN (x_return_status = OKL_API.G_RET_STS_ERROR);
20214: END IF;
20215: x_return_status := get_rec_clev(l_clev_ib_tbl(j).id,
20216: l_clev_ib_rec);