[Home] [Help]
901: x_msg_count OUT NOCOPY NUMBER,
902: x_msg_data OUT NOCOPY VARCHAR2,
903: x_process_action OUT NOCOPY VARCHAR2,
904: x_transaction_id OUT NOCOPY OKL_TRX_CONTRACTS.ID%TYPE,
905: x_child_chrid1 OUT NOCOPY OKC_K_HEADERS_B.ID%TYPE,
906: x_child_chrid2 OUT NOCOPY OKC_K_HEADERS_B.ID%TYPE,
907: p_contract_id IN OKC_K_HEADERS_V.ID%TYPE) IS
908:
909: l_api_name CONSTANT VARCHAR2(30) := 'check_split_process';
902: x_msg_data OUT NOCOPY VARCHAR2,
903: x_process_action OUT NOCOPY VARCHAR2,
904: x_transaction_id OUT NOCOPY OKL_TRX_CONTRACTS.ID%TYPE,
905: x_child_chrid1 OUT NOCOPY OKC_K_HEADERS_B.ID%TYPE,
906: x_child_chrid2 OUT NOCOPY OKC_K_HEADERS_B.ID%TYPE,
907: p_contract_id IN OKC_K_HEADERS_V.ID%TYPE) IS
908:
909: l_api_name CONSTANT VARCHAR2(30) := 'check_split_process';
910: ln_split_contract1 OKC_K_HEADERS_B.ID%TYPE := NULL;
906: x_child_chrid2 OUT NOCOPY OKC_K_HEADERS_B.ID%TYPE,
907: p_contract_id IN OKC_K_HEADERS_V.ID%TYPE) IS
908:
909: l_api_name CONSTANT VARCHAR2(30) := 'check_split_process';
910: ln_split_contract1 OKC_K_HEADERS_B.ID%TYPE := NULL;
911: ln_split_contract2 OKC_K_HEADERS_B.ID%TYPE := NULL;
912: ln_transaction_id OKL_TRX_CONTRACTS.ID%TYPE;
913: ln_contract_id NUMBER := 0;
914: lb_value BOOLEAN := FALSE;
907: p_contract_id IN OKC_K_HEADERS_V.ID%TYPE) IS
908:
909: l_api_name CONSTANT VARCHAR2(30) := 'check_split_process';
910: ln_split_contract1 OKC_K_HEADERS_B.ID%TYPE := NULL;
911: ln_split_contract2 OKC_K_HEADERS_B.ID%TYPE := NULL;
912: ln_transaction_id OKL_TRX_CONTRACTS.ID%TYPE;
913: ln_contract_id NUMBER := 0;
914: lb_value BOOLEAN := FALSE;
915:
914: lb_value BOOLEAN := FALSE;
915:
916:
917: -- To check the existence of the contract.
918: CURSOR c_get_contract(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
919: SELECT 1
920: FROM DUAL
921: WHERE EXISTS (SELECT '1'
922: FROM OKC_K_HEADERS_B
918: CURSOR c_get_contract(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
919: SELECT 1
920: FROM DUAL
921: WHERE EXISTS (SELECT '1'
922: FROM OKC_K_HEADERS_B
923: WHERE ID = p_chr_id);
924:
925: -- To get the transaction created for the contract.
926: CURSOR c_get_transaction_id(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
922: FROM OKC_K_HEADERS_B
923: WHERE ID = p_chr_id);
924:
925: -- To get the transaction created for the contract.
926: CURSOR c_get_transaction_id(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
927: SELECT id
928: FROM okl_trx_contracts
929: WHERE khr_id = p_chr_id
930: AND tcn_type = 'SPLC'
933: --
934: AND tsu_code IN ('ENTERED','WORKING','WAITING','SUBMITTED');
935:
936: -- To get the contracts created during Split process
937: CURSOR c_get_split_contracts(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
938: SELECT id
939: FROM okc_k_headers_b
940: WHERE orig_system_id1 = p_chr_id
941: AND orig_system_source_code = 'OKL_SPLIT'
935:
936: -- To get the contracts created during Split process
937: CURSOR c_get_split_contracts(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
938: SELECT id
939: FROM okc_k_headers_b
940: WHERE orig_system_id1 = p_chr_id
941: AND orig_system_source_code = 'OKL_SPLIT'
942: ORDER BY CREATION_DATE;
943:
948: x_return_status := OKL_API.G_RET_STS_ERROR;
949: OKL_API.set_message(p_app_name => G_APP_NAME,
950: p_msg_name => G_REQUIRED_VALUE,
951: p_token1 => G_COL_NAME_TOKEN,
952: p_token1_value => 'OKC_K_HEADERS_B.ID');
953: -- halt validation as it is a required field
954: RAISE G_EXCEPTION_STOP_VALIDATION;
955: END IF;
956:
1249: -- Parameters :
1250: -- Version : 1.0
1251: -- End of Commnets
1252:
1253: PROCEDURE validate_chr_id(p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
1254: x_contract_number OUT NOCOPY OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE,
1255: x_return_status OUT NOCOPY VARCHAR2)
1256: IS
1257: CURSOR get_k_number(p_chr_id IN OKC_K_HEADERS_B.ID%TYPE)
1250: -- Version : 1.0
1251: -- End of Commnets
1252:
1253: PROCEDURE validate_chr_id(p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
1254: x_contract_number OUT NOCOPY OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE,
1255: x_return_status OUT NOCOPY VARCHAR2)
1256: IS
1257: CURSOR get_k_number(p_chr_id IN OKC_K_HEADERS_B.ID%TYPE)
1258: IS
1253: PROCEDURE validate_chr_id(p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
1254: x_contract_number OUT NOCOPY OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE,
1255: x_return_status OUT NOCOPY VARCHAR2)
1256: IS
1257: CURSOR get_k_number(p_chr_id IN OKC_K_HEADERS_B.ID%TYPE)
1258: IS
1259: SELECT CHR.contract_number
1260: FROM OKC_K_HEADERS_B CHR
1261: WHERE CHR.id = p_chr_id;
1256: IS
1257: CURSOR get_k_number(p_chr_id IN OKC_K_HEADERS_B.ID%TYPE)
1258: IS
1259: SELECT CHR.contract_number
1260: FROM OKC_K_HEADERS_B CHR
1261: WHERE CHR.id = p_chr_id;
1262: BEGIN
1263: -- initialize return status
1264: x_return_status := OKL_API.G_RET_STS_SUCCESS;
1319: -- notify caller of an error as UNEXPETED error
1320: x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
1321: END validate_chr_id;
1322: --------------------------------------------------------------------------------------------------
1323: FUNCTION get_qcl_id(p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
1324: x_qcl_id OUT NOCOPY NUMBER)
1325: RETURN VARCHAR2 IS
1326: x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
1327: ln_qcl_id OKC_K_HEADERS_B.QCL_ID%TYPE;
1323: FUNCTION get_qcl_id(p_chr_id IN OKC_K_HEADERS_B.ID%TYPE,
1324: x_qcl_id OUT NOCOPY NUMBER)
1325: RETURN VARCHAR2 IS
1326: x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
1327: ln_qcl_id OKC_K_HEADERS_B.QCL_ID%TYPE;
1328: ln_chr_id OKC_K_HEADERS_B.ID%TYPE;
1329: CURSOR get_qcl_id_name(p_qcl_name VARCHAR2) IS
1330: SELECT id
1331: FROM okc_qa_check_lists_v
1324: x_qcl_id OUT NOCOPY NUMBER)
1325: RETURN VARCHAR2 IS
1326: x_return_status VARCHAR2(3) := OKL_API.G_RET_STS_SUCCESS;
1327: ln_qcl_id OKC_K_HEADERS_B.QCL_ID%TYPE;
1328: ln_chr_id OKC_K_HEADERS_B.ID%TYPE;
1329: CURSOR get_qcl_id_name(p_qcl_name VARCHAR2) IS
1330: SELECT id
1331: FROM okc_qa_check_lists_v
1332: WHERE name = p_qcl_name;
1332: WHERE name = p_qcl_name;
1333:
1334: CURSOR get_qcl_id_chr (p_chr_id NUMBER) IS
1335: SELECT NVL(qcl_id,0)
1336: FROM okc_k_headers_b
1337: WHERE id = p_chr_id;
1338: BEGIN
1339: OPEN get_qcl_id_chr(p_chr_id => p_chr_id);
1340: FETCH get_qcl_id_chr INTO ln_qcl_id;
1556: l_chrv_rec chrv_rec_type;
1557: l_khrv_rec khrv_rec_type;
1558: l_tcnv_rec OKL_TRX_CONTRACTS_PVT.tcnv_rec_type;
1559: lx_tcnv_rec OKL_TRX_CONTRACTS_PVT.tcnv_rec_type;
1560: ln_orig_system_id1 OKC_K_HEADERS_B.ORIG_SYSTEM_ID1%TYPE;
1561: l_stmv_rec OKL_STREAMS_PUB.stmv_rec_type;
1562: r_tcnv_rec OKL_TRX_CONTRACTS_PVT.tcnv_rec_type;
1563:
1564: CURSOR c_get_k_stream(p_khr_id OKC_K_LINES_B.DNZ_CHR_ID%TYPE) IS
1589: AND lse.lse_parent_id IS NULL
1590: AND lse.lse_type = G_TLS_TYPE;
1591:
1592: -- To get the orig system id for p_chr_id
1593: CURSOR get_orig_sys_id1(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
1594: SELECT orig_system_id1
1595: FROM okc_k_headers_b
1596: WHERE id = p_chr_id;
1597:
1591:
1592: -- To get the orig system id for p_chr_id
1593: CURSOR get_orig_sys_id1(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
1594: SELECT orig_system_id1
1595: FROM okc_k_headers_b
1596: WHERE id = p_chr_id;
1597:
1598: -- To get status of splited transaction Chr id
1599: CURSOR get_trx_id(p_org_sys_id OKC_K_HEADERS_B.ORIG_SYSTEM_ID1%TYPE) IS
1595: FROM okc_k_headers_b
1596: WHERE id = p_chr_id;
1597:
1598: -- To get status of splited transaction Chr id
1599: CURSOR get_trx_id(p_org_sys_id OKC_K_HEADERS_B.ORIG_SYSTEM_ID1%TYPE) IS
1600: SELECT trx.id trx_id
1601: FROM okl_trx_contracts trx,
1602: okl_trx_types_tl try,
1603: okc_k_headers_b CHR
1599: CURSOR get_trx_id(p_org_sys_id OKC_K_HEADERS_B.ORIG_SYSTEM_ID1%TYPE) IS
1600: SELECT trx.id trx_id
1601: FROM okl_trx_contracts trx,
1602: okl_trx_types_tl try,
1603: okc_k_headers_b CHR
1604: WHERE try.name = 'Split Contract'
1605: AND try.LANGUAGE = 'US'
1606: AND trx.try_id = try.id
1607: AND trx.tsu_code = 'ENTERED'
1886: x_msg_data OUT NOCOPY VARCHAR2,
1887: p_contract_id IN OKC_K_HEADERS_V.ID%TYPE) IS
1888:
1889: l_api_name CONSTANT VARCHAR2(30) := 'cancel_split_process';
1890: ln_split_contract1 OKC_K_HEADERS_B.ID%TYPE := NULL;
1891: ln_split_contract2 OKC_K_HEADERS_B.ID%TYPE := NULL;
1892: ln_transaction_id OKL_TRX_CONTRACTS.ID%TYPE;
1893: ln_contract_id NUMBER := 0;
1894: lb_value BOOLEAN := FALSE;
1887: p_contract_id IN OKC_K_HEADERS_V.ID%TYPE) IS
1888:
1889: l_api_name CONSTANT VARCHAR2(30) := 'cancel_split_process';
1890: ln_split_contract1 OKC_K_HEADERS_B.ID%TYPE := NULL;
1891: ln_split_contract2 OKC_K_HEADERS_B.ID%TYPE := NULL;
1892: ln_transaction_id OKL_TRX_CONTRACTS.ID%TYPE;
1893: ln_contract_id NUMBER := 0;
1894: lb_value BOOLEAN := FALSE;
1895: l_tcnv_rec OKL_TRX_CONTRACTS_PVT.tcnv_rec_type;
1895: l_tcnv_rec OKL_TRX_CONTRACTS_PVT.tcnv_rec_type;
1896: lx_tcnv_rec OKL_TRX_CONTRACTS_PVT.tcnv_rec_type;
1897:
1898: -- To check the existence of the contract.
1899: CURSOR c_get_contract(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
1900: SELECT 1
1901: FROM DUAL
1902: WHERE EXISTS (SELECT '1'
1903: FROM OKC_K_HEADERS_B
1899: CURSOR c_get_contract(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
1900: SELECT 1
1901: FROM DUAL
1902: WHERE EXISTS (SELECT '1'
1903: FROM OKC_K_HEADERS_B
1904: WHERE ID = p_chr_id);
1905:
1906: -- To get the transaction created for the contract.
1907: CURSOR c_get_trx_id(p_chr_id OKC_K_HEADERS_B.ORIG_SYSTEM_ID1%TYPE) IS
1903: FROM OKC_K_HEADERS_B
1904: WHERE ID = p_chr_id);
1905:
1906: -- To get the transaction created for the contract.
1907: CURSOR c_get_trx_id(p_chr_id OKC_K_HEADERS_B.ORIG_SYSTEM_ID1%TYPE) IS
1908: SELECT trx.id trx_id
1909: FROM okl_trx_contracts trx,
1910: okl_trx_types_tl try
1911: WHERE try.name = 'Split Contract'
1918: --
1919: AND trx.khr_id = p_chr_id;
1920:
1921: -- To get the contracts created during Split process
1922: CURSOR c_get_split_contracts(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
1923: SELECT id
1924: FROM okc_k_headers_b
1925: WHERE orig_system_id1 = p_chr_id
1926: AND orig_system_source_code = 'OKL_SPLIT';
1920:
1921: -- To get the contracts created during Split process
1922: CURSOR c_get_split_contracts(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
1923: SELECT id
1924: FROM okc_k_headers_b
1925: WHERE orig_system_id1 = p_chr_id
1926: AND orig_system_source_code = 'OKL_SPLIT';
1927:
1928: BEGIN
1945: x_return_status := OKL_API.G_RET_STS_ERROR;
1946: OKL_API.set_message(p_app_name => G_APP_NAME,
1947: p_msg_name => G_REQUIRED_VALUE,
1948: p_token1 => G_COL_NAME_TOKEN,
1949: p_token1_value => 'OKC_K_HEADERS_B.ID');
1950: -- halt validation as it is a required field
1951: RAISE G_EXCEPTION_STOP_VALIDATION;
1952: END IF;
1953:
2103: l_klev_rec klev_rec_type;
2104: lx_clev_rec clev_rec_type;
2105: lx_klev_rec klev_rec_type;
2106:
2107: CURSOR get_fee_service_lines(p_chr_id OKC_K_HEADERS_B.ID%TYPE)
2108: IS
2109: SELECT cle.id id
2110: FROM okl_k_lines_v kle,
2111: okc_k_lines_v cle,
2215: x_msg_data OUT NOCOPY VARCHAR2,
2216: p_contract_id IN OKC_K_HEADERS_V.ID%TYPE) IS
2217:
2218: l_api_name CONSTANT VARCHAR2(30) := 'LOCAL_PROCESS_SPLIT_K';
2219: ln_old_chr_id OKC_K_HEADERS_B.ID%TYPE;
2220: ln_dummy NUMBER := 0;
2221: i NUMBER := 0;
2222: j NUMBER := 0;
2223: k NUMBER := 0;
2242:
2243: -- We need to find out weahter we have fee, service, and Usage lines first
2244: -- so that we can process further
2245:
2246: CURSOR check_other_line(p_chr_id OKC_K_HEADERS_B.ID%TYPE)
2247: IS
2248: SELECT '1'
2249: FROM DUAL
2250: WHERE EXISTS (SELECT '1'
2259: G_USG_LINE_LTY_CODE,
2260: G_USL_LINE_LTY_CODE));
2261:
2262: -- To get the orig system id for p_chr_id
2263: CURSOR get_orig_sys_id1(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
2264: SELECT orig_system_id1
2265: FROM okc_k_headers_b
2266: WHERE id = p_chr_id;
2267:
2261:
2262: -- To get the orig system id for p_chr_id
2263: CURSOR get_orig_sys_id1(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
2264: SELECT orig_system_id1
2265: FROM okc_k_headers_b
2266: WHERE id = p_chr_id;
2267:
2268: /*CURSOR get_asset_info(p_chr_id OKC_K_HEADERS_B.ID%TYPE)
2269: IS
2264: SELECT orig_system_id1
2265: FROM okc_k_headers_b
2266: WHERE id = p_chr_id;
2267:
2268: /*CURSOR get_asset_info(p_chr_id OKC_K_HEADERS_B.ID%TYPE)
2269: IS
2270: SELECT sum(cle.capital_amount) total_capital_amount
2271: FROM okl_k_lines_full_v cle,
2272: okc_line_styles_b lse
3274: p_init_msg_list IN VARCHAR2,
3275: x_return_status OUT NOCOPY VARCHAR2,
3276: x_msg_count OUT NOCOPY NUMBER,
3277: x_msg_data OUT NOCOPY VARCHAR2,
3278: p_chr_id IN OKC_K_HEADERS_B.ID%TYPE) IS
3279: l_api_name VARCHAR2(35) := 'SPLIT_CONTRACT_AFTER_YIELD';
3280: l_proc_name VARCHAR2(35) := 'SPLIT_CONTRACT_AFTER_YIELD';
3281:
3282: REPORTING_EXCEPTION EXCEPTION;
3281:
3282: REPORTING_EXCEPTION EXCEPTION;
3283: ln_dummy NUMBER :=0;
3284: ln1_dummy NUMBER :=0;
3285: ln_orig_system_id1 OKC_K_HEADERS_B.ORIG_SYSTEM_ID1%TYPE;
3286: lv_ok_to_terminate_orig_K VARCHAR2(3):= 'N';
3287: i NUMBER := 0;
3288: j NUMBER := 0;
3289: k NUMBER := 0;
3295: lx_tcnv_rec OKL_TRX_CONTRACTS_PVT.tcnv_rec_type;
3296:
3297: -- rravikir added for Bug 2927173, 2901442
3298: lprv_rec OKL_REV_LOSS_PROV_PVT.lprv_rec_type;
3299: ln_orig_contract_id OKC_K_HEADERS_B.ID%TYPE;
3300: lv_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
3301: ld_contract_start_date OKC_K_HEADERS_B.START_DATE%TYPE;
3302: ld_split_date OKL_TRX_CONTRACTS.DATE_TRANSACTION_OCCURRED%TYPE;
3303: -- end for Bug 2927173, 2901442
3296:
3297: -- rravikir added for Bug 2927173, 2901442
3298: lprv_rec OKL_REV_LOSS_PROV_PVT.lprv_rec_type;
3299: ln_orig_contract_id OKC_K_HEADERS_B.ID%TYPE;
3300: lv_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
3301: ld_contract_start_date OKC_K_HEADERS_B.START_DATE%TYPE;
3302: ld_split_date OKL_TRX_CONTRACTS.DATE_TRANSACTION_OCCURRED%TYPE;
3303: -- end for Bug 2927173, 2901442
3304:
3297: -- rravikir added for Bug 2927173, 2901442
3298: lprv_rec OKL_REV_LOSS_PROV_PVT.lprv_rec_type;
3299: ln_orig_contract_id OKC_K_HEADERS_B.ID%TYPE;
3300: lv_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
3301: ld_contract_start_date OKC_K_HEADERS_B.START_DATE%TYPE;
3302: ld_split_date OKL_TRX_CONTRACTS.DATE_TRANSACTION_OCCURRED%TYPE;
3303: -- end for Bug 2927173, 2901442
3304:
3305: -- rravikir added for Bug 3487162
3308:
3309: ln_chr_id OKC_K_HEADERS_V.ID%TYPE;
3310: ln_service_id OKC_K_HEADERS_V.ID%TYPE;
3311:
3312: CURSOR get_old_service_id(p_chr_id OKC_K_HEADERS_B.ID%TYPE)
3313: IS
3314: SELECT object1_id1 service_contract_id
3315: FROM okc_k_rel_objs_v rel
3316: WHERE rel.chr_id = P_chr_id;
3314: SELECT object1_id1 service_contract_id
3315: FROM okc_k_rel_objs_v rel
3316: WHERE rel.chr_id = P_chr_id;
3317:
3318: CURSOR check_other_line(p_chr_id OKC_K_HEADERS_B.ID%TYPE)
3319: IS
3320: SELECT '1'
3321: FROM DUAL
3322: WHERE EXISTS (SELECT '1'
3327: AND lse.lty_code IN (G_USG_LINE_LTY_CODE,
3328: G_USL_LINE_LTY_CODE));
3329:
3330: -- to check weather the given Chr id belongs to a Split Contract process
3331: CURSOR check_split_k_csr (p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
3332: SELECT 1
3333: FROM dual
3334: WHERE EXISTS (SELECT 1
3335: FROM okl_trx_contracts trx,
3345: AND khr.id = trx.khr_id
3346: AND trx.khr_id = p_chr_id);
3347:
3348: -- To get the orig system id for p_chr_id
3349: CURSOR get_orig_sys_id1(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
3350: SELECT orig_system_id1
3351: FROM okc_k_headers_b
3352: WHERE id = p_chr_id
3353: AND orig_system_source_code = 'OKL_SPLIT';
3347:
3348: -- To get the orig system id for p_chr_id
3349: CURSOR get_orig_sys_id1(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
3350: SELECT orig_system_id1
3351: FROM okc_k_headers_b
3352: WHERE id = p_chr_id
3353: AND orig_system_source_code = 'OKL_SPLIT';
3354:
3355: -- To get status of splited transaction Chr id
3352: WHERE id = p_chr_id
3353: AND orig_system_source_code = 'OKL_SPLIT';
3354:
3355: -- To get status of splited transaction Chr id
3356: CURSOR check_ctrct_status(p_chr_id OKC_K_HEADERS_B.ORIG_SYSTEM_ID1%TYPE) IS
3357: SELECT CHR.id chr_id,
3358: sts_code sts_code,
3359: trx.id trx_id
3360: FROM okl_trx_contracts trx,
3358: sts_code sts_code,
3359: trx.id trx_id
3360: FROM okl_trx_contracts trx,
3361: okl_trx_types_tl try,
3362: okc_k_headers_b CHR
3363: WHERE try.name = 'Split Contract'
3364: AND try.LANGUAGE = 'US'
3365: AND trx.try_id = try.id
3366: AND trx.tsu_code = 'ENTERED'
3371: AND CHR.orig_system_source_code = 'OKL_SPLIT'
3372: AND CHR.orig_system_id1= p_chr_id;
3373:
3374: -- To get the orig system id for Fixed Asset lines of p_chr_id
3375: CURSOR get_orig_fa(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
3376: SELECT orig_system_id1 orig_cle_fa,
3377: txl.tas_id tas_id_fa,
3378: cle.id id
3379: FROM OKC_K_LINES_V cle,
3384: AND lse.lty_code = G_FA_LINE_LTY_CODE
3385: AND cle.id = txl.kle_id;
3386:
3387: -- To get the orig system id for Install Base lines of p_chr_id
3388: CURSOR get_orig_ib(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
3389: SELECT orig_system_id1 orig_cle_ib,
3390: iti.tas_id tas_id_ib,
3391: cle.id id
3392: FROM OKC_K_LINES_V cle,
3397: AND lse.lty_code = G_IB_LINE_LTY_CODE
3398: AND cle.id = iti.kle_id;
3399:
3400: -- To get the item information from original line id and original contract id
3401: CURSOR get_item_info(p_orig_chr_id OKC_K_HEADERS_B.ID%TYPE,
3402: p_orig_cle_id OKC_K_LINES_B.ID%TYPE) IS
3403: SELECT object1_id1,
3404: object1_id2
3405: FROM okc_k_items
3407: AND dnz_chr_Id = p_orig_chr_id;
3408:
3409: -- To get the Contract number and Split contract transaction date
3410: -- rravikir added - Bug 2927173, 2901442
3411: CURSOR get_split_info(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
3412: SELECT KHR.ID, KHR.CONTRACT_NUMBER, KHR.START_DATE, TRX.DATE_TRANSACTION_OCCURRED
3413: FROM OKC_K_HEADERS_B KHR,
3414: OKL_TRX_CONTRACTS TRX
3415: WHERE TRX.KHR_ID = KHR.ID
3409: -- To get the Contract number and Split contract transaction date
3410: -- rravikir added - Bug 2927173, 2901442
3411: CURSOR get_split_info(p_chr_id OKC_K_HEADERS_B.ID%TYPE) IS
3412: SELECT KHR.ID, KHR.CONTRACT_NUMBER, KHR.START_DATE, TRX.DATE_TRANSACTION_OCCURRED
3413: FROM OKC_K_HEADERS_B KHR,
3414: OKL_TRX_CONTRACTS TRX
3415: WHERE TRX.KHR_ID = KHR.ID
3416: AND TRX.TSU_CODE = 'PROCESSED'
3417: AND TRX.TCN_TYPE = 'SPLC'
3454: IF check_split_k_csr%NOTFOUND THEN
3455: OKL_API.set_message(p_app_name => G_APP_NAME,
3456: p_msg_name => G_NO_MATCHING_RECORD,
3457: p_token1 => G_COL_NAME_TOKEN,
3458: p_token1_value => 'OKC_K_HEADERS_B.ORIG_SYSTEM_ID1');
3459:
3460: IF (G_DEBUG_SPLIT) THEN
3461: FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Contract used in the Split process
3462: is not of the type "Split Contract"');
4469: ln_qcl_id2 NUMBER;
4470: lt1_msg_tbl OKL_QA_CHECK_PUB.msg_tbl_type;
4471: lt2_msg_tbl OKL_QA_CHECK_PUB.msg_tbl_type;
4472: lv_severity OKC_QA_LIST_PROCESSES_V.SEVERITY%TYPE;
4473: lv1_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
4474: lv2_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
4475: lv_data VARCHAR2(2000);
4476: lv1_stream_id OKL_STREAM_INTERFACES.ID%TYPE;
4477: lv2_stream_id OKL_STREAM_INTERFACES.ID%TYPE;
4470: lt1_msg_tbl OKL_QA_CHECK_PUB.msg_tbl_type;
4471: lt2_msg_tbl OKL_QA_CHECK_PUB.msg_tbl_type;
4472: lv_severity OKC_QA_LIST_PROCESSES_V.SEVERITY%TYPE;
4473: lv1_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
4474: lv2_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
4475: lv_data VARCHAR2(2000);
4476: lv1_stream_id OKL_STREAM_INTERFACES.ID%TYPE;
4477: lv2_stream_id OKL_STREAM_INTERFACES.ID%TYPE;
4478: lv1_sis_code OKL_STREAM_INTERFACES.SIS_CODE%TYPE;
4476: lv1_stream_id OKL_STREAM_INTERFACES.ID%TYPE;
4477: lv2_stream_id OKL_STREAM_INTERFACES.ID%TYPE;
4478: lv1_sis_code OKL_STREAM_INTERFACES.SIS_CODE%TYPE;
4479: lv2_sis_code OKL_STREAM_INTERFACES.SIS_CODE%TYPE;
4480: lv1_sts_code OKC_K_HEADERS_B.STS_CODE%TYPE;
4481: lv2_sts_code OKC_K_HEADERS_B.STS_CODE%TYPE;
4482: lv_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
4483: l_stream_path OKL_ST_GEN_TMPT_SETS.PRICING_ENGINE%TYPE;
4484:
4477: lv2_stream_id OKL_STREAM_INTERFACES.ID%TYPE;
4478: lv1_sis_code OKL_STREAM_INTERFACES.SIS_CODE%TYPE;
4479: lv2_sis_code OKL_STREAM_INTERFACES.SIS_CODE%TYPE;
4480: lv1_sts_code OKC_K_HEADERS_B.STS_CODE%TYPE;
4481: lv2_sts_code OKC_K_HEADERS_B.STS_CODE%TYPE;
4482: lv_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
4483: l_stream_path OKL_ST_GEN_TMPT_SETS.PRICING_ENGINE%TYPE;
4484:
4485: -- Log Directory Path variable
4478: lv1_sis_code OKL_STREAM_INTERFACES.SIS_CODE%TYPE;
4479: lv2_sis_code OKL_STREAM_INTERFACES.SIS_CODE%TYPE;
4480: lv1_sts_code OKC_K_HEADERS_B.STS_CODE%TYPE;
4481: lv2_sts_code OKC_K_HEADERS_B.STS_CODE%TYPE;
4482: lv_contract_number OKC_K_HEADERS_B.CONTRACT_NUMBER%TYPE;
4483: l_stream_path OKL_ST_GEN_TMPT_SETS.PRICING_ENGINE%TYPE;
4484:
4485: -- Log Directory Path variable
4486: l_temp_dir VARCHAR2(1000);
4490: SELECT a.id,
4491: a.sis_code,
4492: h.sts_code
4493: FROM okl_stream_interfaces a,
4494: okc_k_headers_b h
4495: WHERE a.khr_id = p_khr_id
4496: AND h.id = a.khr_id
4497: AND TRUNC(a.date_processed) IN (SELECT MAX(TRUNC(b.date_processed))
4498: FROM okl_stream_interfaces b
4503: INSTR(TRANSLATE(LTRIM(value),',',' '),' ') - 1),value)
4504: FROM v$parameter
4505: WHERE name = 'utl_file_dir';
4506:
4507: CURSOR c_get_sts_code(p_khr_id OKC_K_HEADERS_B.ID%TYPE) IS
4508: SELECT sts_code
4509: FROM okc_k_headers_b
4510: WHERE id = p_khr_id;
4511:
4505: WHERE name = 'utl_file_dir';
4506:
4507: CURSOR c_get_sts_code(p_khr_id OKC_K_HEADERS_B.ID%TYPE) IS
4508: SELECT sts_code
4509: FROM okc_k_headers_b
4510: WHERE id = p_khr_id;
4511:
4512: CURSOR c_get_contract_number(p_khr_id OKC_K_HEADERS_B.ID%TYPE) IS
4513: SELECT contract_number
4508: SELECT sts_code
4509: FROM okc_k_headers_b
4510: WHERE id = p_khr_id;
4511:
4512: CURSOR c_get_contract_number(p_khr_id OKC_K_HEADERS_B.ID%TYPE) IS
4513: SELECT contract_number
4514: FROM okc_k_headers_b
4515: WHERE id = (SELECT ORIG_SYSTEM_ID1
4516: FROM okc_k_headers_b
4510: WHERE id = p_khr_id;
4511:
4512: CURSOR c_get_contract_number(p_khr_id OKC_K_HEADERS_B.ID%TYPE) IS
4513: SELECT contract_number
4514: FROM okc_k_headers_b
4515: WHERE id = (SELECT ORIG_SYSTEM_ID1
4516: FROM okc_k_headers_b
4517: WHERE id = p_khr_id);
4518:
4512: CURSOR c_get_contract_number(p_khr_id OKC_K_HEADERS_B.ID%TYPE) IS
4513: SELECT contract_number
4514: FROM okc_k_headers_b
4515: WHERE id = (SELECT ORIG_SYSTEM_ID1
4516: FROM okc_k_headers_b
4517: WHERE id = p_khr_id);
4518:
4519: CURSOR c_ok_stream_loop(p_khr_id OKL_STREAM_INTERFACES.KHR_ID%TYPE,
4520: p_stream_id OKL_STREAM_INTERFACES.ID%TYPE) IS
4520: p_stream_id OKL_STREAM_INTERFACES.ID%TYPE) IS
4521: SELECT a.sis_code,
4522: h.sts_code
4523: FROM okl_stream_interfaces a,
4524: okc_k_headers_b h
4525: WHERE a.id = p_stream_id
4526: AND a.khr_id = p_khr_id
4527: AND h.id = a.khr_id
4528: AND TRUNC(a.date_processed) IN (SELECT MAX(TRUNC(b.date_processed))
4535: WHERE khr_id = p_khr_id;
4536:
4537: CURSOR c_org_csr (p_chr_id OKC_K_HEADERS_V.ID%TYPE) IS
4538: SELECT authoring_org_id
4539: FROM okc_k_headers_b
4540: WHERE id = p_chr_id;
4541:
4542: l_org_id okc_k_headers_b.authoring_org_id%TYPE;
4543:
4538: SELECT authoring_org_id
4539: FROM okc_k_headers_b
4540: WHERE id = p_chr_id;
4541:
4542: l_org_id okc_k_headers_b.authoring_org_id%TYPE;
4543:
4544: /*
4545: -- mvasudev, 08/23/2004
4546: -- Added PROCEDURE to enable Business Event
4550: )
4551: IS
4552: -- Cursor to get the old chr id
4553: /*
4554: CURSOR l_old_chr_csr (p_khr_id OKC_K_HEADERS_B.ID%TYPE) IS
4555: SELECT chrb.orig_system_id1 old_chr_id
4556: ,trxb.date_transaction_occurred date_transaction_occurred
4557: FROM okc_k_headers_b chrb
4558: ,okl_trx_contracts trxb
4553: /*
4554: CURSOR l_old_chr_csr (p_khr_id OKC_K_HEADERS_B.ID%TYPE) IS
4555: SELECT chrb.orig_system_id1 old_chr_id
4556: ,trxb.date_transaction_occurred date_transaction_occurred
4557: FROM okc_k_headers_b chrb
4558: ,okl_trx_contracts trxb
4559: ,okl_trx_types_b tryv
4560: WHERE chrb.id = p_khr_id
4561: -- AND trxb.khr_id_old = chrb.orig_system_id1
4563: AND trxb.tsu_code = 'PROCESSED'
4564: AND trxb.try_id = tryv.id;
4565: */
4566:
4567: CURSOR l_old_chr_csr (p_khr_id OKC_K_HEADERS_B.ID%TYPE) IS
4568: SELECT chrb.orig_system_id1 old_chr_id
4569: ,trxb2.date_transaction_occurred date_transaction_occurred
4570: FROM okc_k_headers_b chrb
4571: ,okl_trx_contracts trxb1
4566:
4567: CURSOR l_old_chr_csr (p_khr_id OKC_K_HEADERS_B.ID%TYPE) IS
4568: SELECT chrb.orig_system_id1 old_chr_id
4569: ,trxb2.date_transaction_occurred date_transaction_occurred
4570: FROM okc_k_headers_b chrb
4571: ,okl_trx_contracts trxb1
4572: ,okl_trx_types_b tryv
4573: ,okl_trx_contracts trxb2
4574: WHERE chrb.id = p_khr_id
5014: IF c_get_sts_code%NOTFOUND THEN
5015: OKL_API.set_message(p_app_name => G_APP_NAME,
5016: p_msg_name => G_NO_MATCHING_RECORD,
5017: p_token1 => G_COL_NAME_TOKEN,
5018: p_token1_value => 'OKC_K_HEADERS_B.STS_CODE');
5019: x_return_status := OKL_API.G_RET_STS_ERROR;
5020: END IF;
5021: CLOSE c_get_sts_code;
5022:
5235: IF c_get_sts_code%NOTFOUND THEN
5236: OKL_API.set_message(p_app_name => G_APP_NAME,
5237: p_msg_name => G_NO_MATCHING_RECORD,
5238: p_token1 => G_COL_NAME_TOKEN,
5239: p_token1_value => 'OKC_K_HEADERS_B.STS_CODE');
5240: x_return_status := OKL_API.G_RET_STS_ERROR;
5241: END IF;
5242: CLOSE c_get_sts_code;
5243: