[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT status_code
FROM okl_pools
WHERE id = p_pol_id;
SELECT transaction_status,id FROM OKL_POOL_TRANSACTIONS pools
where pools.transaction_status <> G_POOL_TRX_STATUS_COMPLETE
and pools.transaction_type='ADD' and pools.transaction_reason='ADJUSTMENTS'
and pools.pol_id=p_pol_id;
PROCEDURE update_pool_status(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_pool_status IN okl_pools.status_code%TYPE
,p_pol_id IN okl_pools.id%TYPE)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_pool_status';
SELECT poc.id
FROM okl_pool_contents poc
WHERE poc.pol_id = p_pol_id
;
SAVEPOINT update_pool_status_PVT;
Okl_Pool_Pvt.update_pool(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_polv_rec => lp_polv_rec,
x_polv_rec => lx_polv_rec);
Okl_Pool_Pvt.update_pool_contents(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pocv_rec => lp_pocv_rec,
x_pocv_rec => lx_pocv_rec);
ROLLBACK TO update_pool_status_PVT;
ROLLBACK TO update_pool_status_PVT;
ROLLBACK TO update_pool_status_PVT;
END update_pool_status;
l_polv_rec.DATE_LAST_UPDATED := SYSDATE;
Okl_Pol_Pvt.insert_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_polv_rec => l_polv_rec,
x_polv_rec => x_polv_rec);
PROCEDURE update_pool(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_polv_rec IN polv_rec_type
,x_polv_rec OUT NOCOPY polv_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_pool_pvt';
SAVEPOINT update_pool_PVT;
l_polv_rec.DATE_LAST_UPDATED := SYSDATE;
IF (l_polv_rec.last_updated_by IS NULL) THEN
l_polv_rec.last_updated_by :=Okl_Api.G_MISS_NUM;
IF (l_polv_rec.last_update_date IS NULL) THEN
l_polv_rec.last_update_date :=Okl_Api.G_MISS_DATE;
IF (l_polv_rec.last_update_login IS NULL) THEN
l_polv_rec.last_update_login :=Okl_Api.G_MISS_NUM;
Okl_Pol_Pvt.update_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_polv_rec => l_polv_rec,
x_polv_rec => x_polv_rec);
ROLLBACK TO update_pool_PVT;
ROLLBACK TO update_pool_PVT;
ROLLBACK TO update_pool_PVT;
END update_pool;
PROCEDURE delete_pool(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_polv_rec IN polv_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_pool_pvt';
SAVEPOINT delete_pool_PVT;
Okl_Pol_Pvt.delete_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_polv_rec => l_polv_rec);
ROLLBACK TO delete_pool_PVT;
ROLLBACK TO delete_pool_PVT;
ROLLBACK TO delete_pool_PVT;
END delete_pool;
Okl_Poc_Pvt.insert_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pocv_rec => l_pocv_rec,
x_pocv_rec => x_pocv_rec);
Okl_Pool_Pvt.update_pool(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_polv_rec => lp_polv_rec,
x_polv_rec => lx_polv_rec);
PROCEDURE update_pool_contents(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_pocv_rec IN pocv_rec_type
,x_pocv_rec OUT NOCOPY pocv_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_pool_contents_pvt';
SELECT pol_id
FROM okl_pool_contents
WHERE id = p_id
;
SAVEPOINT update_pool_contents_pvt;
Okl_Poc_Pvt.update_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pocv_rec => l_pocv_rec,
x_pocv_rec => x_pocv_rec);
Okl_Pool_Pvt.update_pool(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_polv_rec => lp_polv_rec,
x_polv_rec => lx_polv_rec);
ROLLBACK TO update_pool_contents_PVT;
ROLLBACK TO update_pool_contents_pvt;
ROLLBACK TO update_pool_contents_pvt;
END update_pool_contents;
PROCEDURE update_pool_contents(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_pocv_tbl IN pocv_tbl_type
,x_pocv_tbl OUT NOCOPY pocv_tbl_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'update_pool_contents_pvt';
SAVEPOINT update_pool_contents_PVT2;
Okl_Pool_Pvt.update_pool_contents(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pocv_rec => l_pocv_tbl(i),
x_pocv_rec => x_pocv_tbl(i));
ROLLBACK TO update_pool_contents_PVT2;
ROLLBACK TO update_pool_contents_pvt2;
ROLLBACK TO update_pool_contents_pvt2;
END update_pool_contents;
PROCEDURE delete_pool_contents(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_pocv_rec IN pocv_rec_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_pool_contents_pvt';
SELECT pol_id
FROM okl_pool_contents
WHERE id = p_id
;
SAVEPOINT delete_pool_contents_pvt;
Okl_Poc_Pvt.delete_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pocv_rec => l_pocv_rec);
Okl_Pool_Pvt.update_pool(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_polv_rec => lp_polv_rec,
x_polv_rec => lx_polv_rec);
ROLLBACK TO delete_pool_contents_PVT;
ROLLBACK TO delete_pool_contents_pvt;
ROLLBACK TO delete_pool_contents_pvt;
END delete_pool_contents;
PROCEDURE delete_pool_contents(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_pocv_tbl IN pocv_tbl_type
) IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_pool_contents_pvt';
SAVEPOINT delete_pool_contents_pvt2;
Okl_Pool_Pvt.delete_pool_contents(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pocv_rec => l_pocv_tbl(i));
ROLLBACK TO delete_pool_contents_PVT2;
ROLLBACK TO delete_pool_contents_pvt2;
ROLLBACK TO delete_pool_contents_pvt2;
END delete_pool_contents;
Okl_Pox_Pvt.insert_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_poxv_rec => l_poxv_rec,
x_poxv_rec => x_poxv_rec);
PROCEDURE update_pool_transaction(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_poxv_rec IN poxv_rec_type
,x_poxv_rec OUT NOCOPY poxv_rec_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_pool_transaction_pvt';
SAVEPOINT update_pool_transaction_pvt;
Okl_Pox_Pvt.update_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_poxv_rec => l_poxv_rec,
x_poxv_rec => x_poxv_rec);
ROLLBACK TO update_pool_transaction_PVT;
ROLLBACK TO update_pool_transaction_pvt;
ROLLBACK TO update_pool_transaction_pvt;
END update_pool_transaction;
PROCEDURE delete_pool_transaction(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_poxv_rec IN poxv_rec_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'delete_pool_transaction_pvt';
SAVEPOINT delete_pool_transaction_pvt2;
Okl_Pox_Pvt.delete_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_poxv_rec => l_poxv_rec);
ROLLBACK TO delete_pool_transaction_PVT2;
ROLLBACK TO delete_pool_transaction_pvt2;
ROLLBACK TO delete_pool_transaction_pvt2;
END delete_pool_transaction;
SELECT
NVL(SUM(NVL(ele.AMOUNT,0)),0) STREAM_AMOUNT
-- SUM(ele.AMOUNT) STREAM_AMOUNT --fixed cklee 06/05/2003
--for streams
FROM
okl_streams strm
,okl_strm_elements ele
,okl_pool_contents cnt
WHERE strm.id = ele.stm_id
AND cnt.ID = p_poc_id
-- mvasudev, 08/11/2003 , Restoring stm_id changes
--AND strm.KHR_ID = cnt.KHR_ID
--AND strm.KLE_ID = cnt.KLE_ID
--AND strm.STY_ID = cnt.STY_ID
AND strm.ID = cnt.STM_ID
AND strm.say_code = 'CURR'
AND strm.active_yn = 'Y'
AND cnt.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
AND ele.STREAM_ELEMENT_DATE
BETWEEN cnt.STREAMS_FROM_DATE AND NVL(cnt.STREAMS_TO_DATE,G_FINAL_DATE)
;
SELECT poc.id
FROM okl_pool_contents poc
WHERE poc.pol_id = p_pol_id
AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
;
SELECT poc.id
FROM okl_pool_contents poc
WHERE poc.pol_id = p_pol_id
AND poc.status_code IN (G_POC_STS_PENDING); -- Getting only pending POC - for Bug 6691554
SELECT NVL(SUM(NVL(ele.AMOUNT,0)),0) STREAM_AMOUNT
FROM okl_streams strm
,okl_strm_elements ele
,okl_pool_contents cnt
WHERE strm.id = ele.stm_id
AND cnt.ID = p_poc_id
AND strm.ID = cnt.STM_ID
AND strm.say_code = 'CURR'
AND strm.active_yn = 'Y'
AND cnt.status_code IN (G_POC_STS_PENDING)
AND ele.STREAM_ELEMENT_DATE
BETWEEN cnt.STREAMS_FROM_DATE AND NVL(cnt.STREAMS_TO_DATE,G_FINAL_DATE);
SELECT ph.id
FROM okl_pools ph
WHERE ph.khr_id = p_khr_id
;
SELECT DISTINCT poc.khr_id,
poc.kle_id,
DECODE(khr.deal_type, G_DEAL_TYPE_LEASEDF, G_NET_INVESTMENT_DF
, G_DEAL_TYPE_LEASEST, G_NET_INVESTMENT_DF
, G_DEAL_TYPE_LOAN, G_NET_INVESTMENT_LOAN
, G_DEAL_TYPE_LEASEOP, G_NET_INVESTMENT_OP
, G_NET_INVESTMENT_OTHERS)
FROM okl_pool_contents poc,
okc_k_headers_b CHR,
okl_k_headers khr
WHERE poc.khr_id = CHR.id
AND khr.id = CHR.id
AND poc.pol_id = p_pol_id
;
SELECT khr.id,
DECODE(khr.deal_type, G_DEAL_TYPE_LEASEDF, G_NET_INVESTMENT_DF
, G_DEAL_TYPE_LEASEST, G_NET_INVESTMENT_DF
, G_DEAL_TYPE_LOAN, G_NET_INVESTMENT_LOAN
, G_DEAL_TYPE_LEASEOP, G_NET_INVESTMENT_OP
, G_NET_INVESTMENT_OTHERS)
FROM okl_k_headers khr
WHERE EXISTS (SELECT '1'
FROM okl_pool_contents poc
WHERE khr.id = poc.khr_id
AND poc.pol_id = p_pol_id
AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE))
;
SELECT CHR.contract_number,
khr.deal_type
FROM okc_k_headers_b CHR,
okl_k_headers khr
WHERE CHR.id = khr.id
AND CHR.id = p_chr_id
;
Okl_Pool_Pvt.update_pool(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_polv_rec => lp_polv_rec,
x_polv_rec => lx_polv_rec);
SELECT ph.id
FROM okl_pools ph
WHERE ph.khr_id = p_khr_id
;
Okl_Pool_Pvt.update_pool(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_polv_rec => lp_polv_rec,
x_polv_rec => lx_polv_rec);
SELECT
pol.dnz_chr_id khr_id
,pol.kle_id
,pol.sty_id
,pol.stream_type_code sty_code
,MIN(pol.stream_element_due_date) streams_from_date
,DECODE(pol.stream_type_subclass, 'RESIDUAL', NULL, pol.end_date) streams_to_date
-- mvasudev, stm_id changes
,pol.stm_id
FROM okl_pool_srch_v pol
WHERE
-- pre-req
pol.currency_code = p_currency_code
AND pol.sts_code = 'BOOKED'
AND pol.assignable_yn = 'Y'
AND pol.stream_element_date_billed IS NULL
AND NVL(pol.cust_object1_id1,G_DEFAULT_NUM) = NVL(p_cust_object1_id1, NVL(pol.cust_object1_id1,G_DEFAULT_NUM))
AND NVL(pol.sic_code,G_DEFAULT_CHAR) = NVL(p_sic_code, NVL(pol.sic_code,G_DEFAULT_CHAR))
AND NVL(pol.dnz_chr_id,G_DEFAULT_NUM) = NVL(p_khr_id, NVL(pol.dnz_chr_id,G_DEFAULT_NUM))
AND NVL(pol.pre_tax_yield,G_DEFAULT_NUM) BETWEEN NVL(p_pre_tax_yield_from, NVL(pol.pre_tax_yield,G_DEFAULT_NUM))
AND NVL(p_pre_tax_yield_to, NVL(pol.pre_tax_yield,G_DEFAULT_NUM))
--and pol.contract_number
AND NVL(pol.book_classification,G_DEFAULT_CHAR) = NVL(p_book_classification, NVL(pol.book_classification,G_DEFAULT_CHAR))
AND NVL(pol.pdt_id,G_DEFAULT_NUM) = NVL(p_pdt_id, NVL(pol.pdt_id,G_DEFAULT_NUM))
AND NVL(pol.start_date, G_DEFAULT_DATE)
BETWEEN NVL(p_start_date_from, NVL(pol.start_date, G_DEFAULT_DATE))
AND NVL(p_start_date_to, NVL(pol.start_date, G_DEFAULT_DATE))
AND NVL(pol.end_date,G_DEFAULT_DATE)
BETWEEN NVL(p_end_date_from, NVL(pol.end_date,G_DEFAULT_DATE))
AND NVL(p_end_date_to, NVL(pol.end_date,G_DEFAULT_DATE))
AND NVL(pol.tax_owner,G_DEFAULT_CHAR) = NVL(p_tax_owner, NVL(pol.tax_owner,G_DEFAULT_CHAR))
AND NVL(pol.stream_element_due_date, G_DEFAULT_DATE)
BETWEEN NVL(p_stream_element_from_date,
NVL(pol.stream_element_due_date, G_DEFAULT_DATE))
AND NVL(p_stream_element_to_date,NVL(pol.stream_element_due_date, G_DEFAULT_DATE))
-- pre-req condition
-- start for 11.5.10 by cklee 08/06/03 stream_type_subclass ER
AND pol.stream_type_subclass IN ('RENT', 'RESIDUAL')
AND NVL(pol.stream_type_subclass,G_DEFAULT_CHAR) = NVL(p_stream_type_subclass, NVL(pol.stream_type_subclass,G_DEFAULT_CHAR))
-- end for 11.5.10 by cklee 08/06/03 stream_type_subclass ER
AND pol.stream_say_code = 'CURR'
AND pol.stream_active_yn = 'Y'
AND pol.stream_element_due_date > SYSDATE
-- multi-org
-- non existing check
-- start for 11.5.10 by cklee 08/06/03 stream_type_subclass ER
AND NOT EXISTS -- okl_pool_contents
(SELECT '1'
FROM okl_pool_contents pol_cnts,
okl_strm_type_b styb
WHERE pol_cnts.pol_id = pol_id
-- mvasudev, stm_id changes
-- AND pol_cnts.sty_id = styb.id
-- AND pol.dnz_chr_id = pol_cnts.khr_id
AND pol.stm_id = pol_cnts.stm_id
AND styb.stream_type_subclass = pol.stream_type_subclass
AND pol_cnts.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
)
-- end for 11.5.10 by cklee 08/06/03 stream_type_subclass ER
AND NOT EXISTS -- variable interest rate
(SELECT '1'
FROM okc_rule_groups_b rgp
,okc_rules_b rg
WHERE rgp.id = rg.rgp_id
AND rgp.rgd_code = 'LAIIND'
AND rg.rule_information_category= 'LAINTP'
AND rg.rule_information1 = 'Y'
AND rgp.dnz_chr_id = pol.dnz_chr_id)
AND NOT EXISTS -- revision contract: rebook, split contract, reverse
(SELECT '1'
FROM okl_trx_contracts trxc
WHERE trxc.tcn_type IN ('TRBK','SPLC','RVS')
AND trxc.tsu_code NOT IN ('PROCESSED', 'ERROR','CANCELED') -- condition changes 01/13/2003 cklee
AND trxc.khr_id = pol.dnz_chr_id)
AND NOT EXISTS -- split assets, split assets components
(SELECT '1'
FROM okl_txd_assets_v tdas,
okl_txl_assets_b tal,
okc_k_lines_b cle
WHERE cle.id = tal.kle_id
AND tal.id = tdas.tal_id
AND tal.tal_type = 'ALI'
-- link from okl_pool_srch_v pol
AND cle.cle_id = pol.kle_id -- top line id
AND tal.dnz_khr_id = pol.dnz_chr_id
-- link from okl_pool_srch_v pol
AND EXISTS (SELECT '1'
FROM okl_trx_assets tas
WHERE tas.id = tal.tas_id
AND tas.tas_type = 'ALI'
AND tas.tsu_code NOT IN ('PROCESSED','CANCELED')))--cklee 02/24/03
AND NOT EXISTS -- contract is under deliquent status
(SELECT '1'
FROM iex_case_objects ico,
iex_delinquencies_all del
WHERE ico. cas_id = del.case_id
AND del.status ='DELINQUENT'
AND ico.object_id = pol.dnz_chr_id)
AND NOT EXISTS -- contract line has been terminated
(SELECT '1'
FROM okc_k_lines_b cle,
okc_statuses_b sts
WHERE sts.code = cle.sts_code
AND sts.ste_code IN ('HOLD','EXPIRED','TERMINATED','CANCELLED')
AND cle.id = pol.kle_id)
GROUP BY
pol.dnz_chr_id
,pol.kle_id
,pol.sty_id
,pol.stream_type_code
,pol.stream_type_subclass
,pol.end_date
-- mvasudev, stm_id changes
,pol.stm_id
;
SELECT
pol.dnz_chr_id khr_id
,pol.kle_id
,pol.sty_id
,pol.stream_type_code sty_code
,MIN(pol.stream_element_due_date) streams_from_date
,DECODE(pol.stream_type_subclass, 'RESIDUAL', NULL, pol.end_date) streams_to_date
-- mvasudev, stm_id changes
,pol.stm_id
FROM okl_pool_srch_payfreq_v pol
WHERE
-- pre-req
pol.currency_code = p_currency_code
AND pol.sts_code = 'BOOKED'
AND pol.assignable_yn = 'Y'
AND pol.stream_element_date_billed IS NULL
--
AND NVL(pol.cust_object1_id1,G_DEFAULT_NUM) = NVL(p_cust_object1_id1, NVL(pol.cust_object1_id1,G_DEFAULT_NUM))
--and pol.lessee
AND NVL(pol.sic_code,G_DEFAULT_CHAR) = NVL(p_sic_code, NVL(pol.sic_code,G_DEFAULT_CHAR))
AND NVL(pol.dnz_chr_id,G_DEFAULT_NUM) = NVL(p_khr_id, NVL(pol.dnz_chr_id,G_DEFAULT_NUM))
AND NVL(pol.pre_tax_yield,G_DEFAULT_NUM) BETWEEN NVL(p_pre_tax_yield_from, NVL(pol.pre_tax_yield,G_DEFAULT_NUM))
AND NVL(p_pre_tax_yield_to, NVL(pol.pre_tax_yield,G_DEFAULT_NUM))
--and pol.contract_number
AND NVL(pol.book_classification,G_DEFAULT_CHAR) = NVL(p_book_classification, NVL(pol.book_classification,G_DEFAULT_CHAR))
AND NVL(pol.pdt_id,G_DEFAULT_NUM) = NVL(p_pdt_id, NVL(pol.pdt_id,G_DEFAULT_NUM))
AND NVL(pol.start_date, G_DEFAULT_DATE)
BETWEEN NVL(p_start_date_from, NVL(pol.start_date, G_DEFAULT_DATE))
AND NVL(p_start_date_to, NVL(pol.start_date, G_DEFAULT_DATE))
AND NVL(pol.end_date,G_DEFAULT_DATE)
BETWEEN NVL(p_end_date_from, NVL(pol.end_date,G_DEFAULT_DATE))
AND NVL(p_end_date_to, NVL(pol.end_date,G_DEFAULT_DATE))
AND NVL(pol.tax_owner,G_DEFAULT_CHAR) = NVL(p_tax_owner, NVL(pol.tax_owner,G_DEFAULT_CHAR))
AND NVL(pol.stream_element_due_date, G_DEFAULT_DATE)
BETWEEN NVL(p_stream_element_from_date,
NVL(pol.stream_element_due_date, G_DEFAULT_DATE))
AND NVL(p_stream_element_to_date,
NVL(pol.stream_element_due_date, G_DEFAULT_DATE))
-- cklee 02/21/2003 bug fixed
AND NVL(pol.PAYMENT_FREQ,G_DEFAULT_CHAR) = NVL(p_STREAM_ELEMENT_PAYMENT_FREQ, NVL(pol.PAYMENT_FREQ,G_DEFAULT_CHAR))
-- pre-req condition
-- start for 11.5.10 by cklee 08/06/03 stream_type_subclass ER
AND pol.stream_type_subclass IN ('RENT', 'RESIDUAL')
AND NVL(pol.stream_type_subclass,G_DEFAULT_CHAR) = NVL(p_stream_type_subclass, NVL(pol.stream_type_subclass,G_DEFAULT_CHAR))
-- end for 11.5.10 by cklee 08/06/03 stream_type_subclass ER
AND pol.stream_say_code = 'CURR'
AND pol.stream_active_yn = 'Y'
AND pol.stream_element_due_date > SYSDATE
-- multi-org
-- non existing check
-- start for 11.5.10 by cklee 08/06/03 stream_type_subclass ER
AND NOT EXISTS -- okl_pool_contents
(SELECT '1'
FROM okl_pool_contents pol_cnts,
okl_strm_type_b styb
WHERE pol_cnts.pol_id = pol_id
-- mvasudev, stm_id changes
--AND pol_cnts.sty_id = styb.id
--AND pol.dnz_chr_id = pol_cnts.khr_id
AND pol.stm_id = pol_cnts.stm_id
AND styb.stream_type_subclass = pol.stream_type_subclass
AND pol_cnts.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
)
-- end for 11.5.10 by cklee 08/06/03 stream_type_subclass ER
AND NOT EXISTS -- variable interest rate
(SELECT '1'
FROM okc_rule_groups_b rgp
,okc_rules_b rg
WHERE rgp.id = rg.rgp_id
AND rgp.rgd_code = 'LAIIND'
AND rg.rule_information_category= 'LAINTP'
AND rg.rule_information1 = 'Y'
AND rgp.dnz_chr_id = pol.dnz_chr_id)
AND NOT EXISTS -- revision contract: rebook, split contract, reverse
(SELECT '1'
FROM okl_trx_contracts
WHERE tcn_type IN ('TRBK','SPLC','RVS')
AND tsu_code NOT IN ('PROCESSED', 'ERROR','CANCELED') -- condition changes 01/13/2003 cklee
AND khr_id = pol.dnz_chr_id)
AND NOT EXISTS -- split assets, split assets components
(SELECT '1'
FROM okl_txd_assets_v tdas,
okl_txl_assets_b tal,
okc_k_lines_b cle
WHERE cle.id = tal.kle_id
AND tal.id = tdas.tal_id
AND tal.tal_type = 'ALI'
-- link from okl_pool_srch_v pol
AND cle.cle_id = pol.kle_id -- top line id
AND tal.dnz_khr_id = pol.dnz_chr_id
-- link from okl_pool_srch_v pol
AND EXISTS (SELECT '1'
FROM okl_trx_assets tas
WHERE tas.id = tal.tas_id
AND tas.tas_type = 'ALI'
AND tas.tsu_code NOT IN ('PROCESSED','CANCELED'))) -- cklee 02/24/03
AND NOT EXISTS -- contract is under deliquent status
(SELECT '1'
FROM iex_case_objects ico,
iex_delinquencies_all del
WHERE ico. cas_id = del.case_id
AND del.status ='DELINQUENT'
AND ico.object_id = pol.dnz_chr_id)
AND NOT EXISTS -- contract line has been terminated
(SELECT '1'
FROM okc_k_lines_b cle,
okc_statuses_b sts
WHERE sts.code = cle.sts_code
AND sts.ste_code IN ('HOLD','EXPIRED','TERMINATED','CANCELLED')
AND cle.id = pol.kle_id)
GROUP BY
pol.dnz_chr_id
,pol.kle_id
,pol.sty_id
,pol.stream_type_code
,pol.stream_type_subclass
,pol.end_date
-- mvasudev, stm_id changes
,pol.stm_id
;
SELECT
pol.dnz_chr_id khr_id
,pol.kle_id
,pol.sty_id
,pol.stream_type_code sty_code
,MIN(pol.stream_element_due_date) streams_from_date
-- mvasudev, 02/06/2004
,DECODE(pol.stream_type_subclass, 'RESIDUAL', NULL, pol.end_date+1) streams_to_date
,pol.stm_id
-- extra
,pol.stream_type_subclass
,pol.contract_number
,pol.lessee
,lkup.meaning sty_subclass_meaning
,pol.asset_number
,hcp.credit_classification
FROM okl_pool_srch_v pol
,fnd_lookups lkup
,hz_customer_profiles hcp
,okl_strm_type_b strm -- added for bug 13997160 :vsgandhi
WHERE
-- pre-req
pol.currency_code = p_currency_code
AND pol.sts_code IN ('BOOKED','EVERGREEN')
AND pol.assignable_yn = 'Y'
AND pol.stream_element_date_billed IS NULL
AND pol.stream_element_amount <> 0 -- vsgandhi : Bug 13997160
-- to fetch stream_type_subclass name
AND lkup.lookup_type = 'OKL_STREAM_TYPE_SUBCLASS'
AND lkup.lookup_code = pol.stream_type_subclass
-- customer
AND NVL(pol.cust_object1_id1,G_DEFAULT_NUM) = NVL(p_cust_object1_id1, NVL(pol.cust_object1_id1,G_DEFAULT_NUM))
AND NVL(pol.sic_code,G_DEFAULT_CHAR) = NVL(p_sic_code, NVL(pol.sic_code,G_DEFAULT_CHAR))
-- contract number
AND NVL(pol.dnz_chr_id,G_DEFAULT_NUM) = NVL(p_khr_id, NVL(pol.dnz_chr_id,G_DEFAULT_NUM))
AND NVL(pol.pre_tax_yield,G_DEFAULT_NUM) BETWEEN NVL(p_pre_tax_yield_from, NVL(pol.pre_tax_yield,G_DEFAULT_NUM))
AND NVL(p_pre_tax_yield_to, NVL(pol.pre_tax_yield,G_DEFAULT_NUM))
AND NVL(pol.book_classification,G_DEFAULT_CHAR) = NVL(p_book_classification, NVL(pol.book_classification,G_DEFAULT_CHAR))
AND NVL(pol.pdt_id,G_DEFAULT_NUM) = NVL(p_pdt_id, NVL(pol.pdt_id,G_DEFAULT_NUM))
AND NVL(pol.start_date, G_DEFAULT_DATE)
BETWEEN NVL(p_start_date_from, NVL(pol.start_date, G_DEFAULT_DATE))
AND NVL(p_start_date_to, NVL(pol.start_date, G_DEFAULT_DATE))
AND NVL(pol.end_date,G_DEFAULT_DATE)
BETWEEN NVL(p_end_date_from, NVL(pol.end_date,G_DEFAULT_DATE))
AND NVL(p_end_date_to, NVL(pol.end_date,G_DEFAULT_DATE))
AND NVL(pol.tax_owner,G_DEFAULT_CHAR) = NVL(p_tax_owner, NVL(pol.tax_owner,G_DEFAULT_CHAR))
-- streams
AND NVL(pol.stream_element_due_date, G_DEFAULT_DATE)
BETWEEN NVL(p_stream_element_from_date,
NVL(pol.stream_element_due_date, G_DEFAULT_DATE))
AND NVL(p_stream_element_to_date,NVL(pol.stream_element_due_date, G_DEFAULT_DATE))
--Bug 674000 ssdeshpa start
AND pol.stream_type_subclass IN ('RENT', 'RESIDUAL', 'LOAN_PAYMENT')
--Bug 674000 ssdeshpa end
AND NVL(pol.stream_type_subclass,G_DEFAULT_CHAR) = NVL(p_stream_type_subclass, NVL(pol.stream_type_subclass,G_DEFAULT_CHAR))
AND pol.stream_say_code = 'CURR'
AND pol.stream_active_yn = 'Y'
-- mvasudev, 02/06/2004
--AND pol.stream_element_due_date > SYSDATE
-- multi-org
--Bug # 6691554 Changes for Cust Credit Classification Lov Start
AND pol.cust_object1_id1 = hcp.party_id(+)
AND hcp.cust_account_id(+) = -1
AND hcp.site_use_id(+) IS NULL
AND NVL(hcp.credit_classification(+),G_DEFAULT_CHAR) = NVL(p_cust_crd_clf_code, NVL(hcp.credit_classification(+),G_DEFAULT_CHAR))
--Bug # 6691554 Changes for Cust Credit Classification Lov End
--Bug # 13997160 only billable streams can be added to a pool : vsgandhi
AND strm.id = pol.sty_id
AND strm.billable_yn = 'Y'
--Bug # 13997160 : vsgandhi
GROUP BY
pol.dnz_chr_id
,pol.kle_id
,pol.sty_id
,pol.stream_type_code
,pol.stream_type_subclass
,pol.end_date
-- mvasudev, stm_id changes
,pol.stm_id
,pol.contract_number
,pol.lessee
,lkup.meaning
,pol.asset_number
,hcp.credit_classification;
SELECT '1'
FROM okl_pool_contents pol_cnts,
okl_strm_type_b styb,
okl_strm_elements sel
WHERE pol_cnts.pol_id = pol_id
AND pol_cnts.stm_id = p_stm_id
AND styb.stream_type_subclass = p_stream_type_subclass
AND pol_cnts.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE,G_POC_STS_PENDING)
AND sel.stm_id = pol_cnts.stm_id
AND sel.date_billed IS NULL
GROUP BY sel.stm_id
HAVING MAX(sel.STREAM_ELEMENT_DATE) <= MAX(nvl(pol_cnts.STREAMS_TO_DATE,pol_cnts.STREAMS_FROM_DATE));
SELECT '1'
FROM okl_pool_contents pol_cnts
WHERE pol_cnts.pol_id = pol_id
AND pol_cnts.stm_id = p_stm_id
AND pol_cnts.status_code = 'INACTIVE';
SELECT MAX(nvl(pol_cnts.STREAMS_TO_DATE,pol_cnts.STREAMS_FROM_DATE)) eff_from_date
FROM okl_pool_contents pol_cnts
WHERE pol_cnts.pol_id = pol_id
AND pol_cnts.stm_id = p_stm_id
AND pol_cnts.status_code = 'ACTIVE';
SELECT '1'
FROM okc_rule_groups_b rgp
,okc_rules_b rg
WHERE rgp.id = rg.rgp_id
AND rgp.rgd_code = 'LAIIND'
AND rg.rule_information_category= 'LAINTP'
AND rg.rule_information1 = 'Y'
AND rgp.dnz_chr_id = p_dnz_chr_id;
SELECT '1'
FROM okl_trx_contracts trxc
WHERE trxc.tcn_type IN ('TRBK','SPLC','RVS')
AND trxc.tsu_code NOT IN ('PROCESSED', 'ERROR','CANCELED')
AND trxc.khr_id = p_dnz_chr_id;
SELECT '1'
FROM okl_txd_assets_v tdas,
okl_txl_assets_b tal,
okc_k_lines_b cle
WHERE cle.id = tal.kle_id
AND tal.id = tdas.tal_id
AND tal.tal_type = 'ALI'
-- link from okl_pool_srch_v pol
AND cle.cle_id = p_kle_id -- top line id
AND tal.dnz_khr_id = p_dnz_chr_id
-- link from okl_pool_srch_v pol
AND EXISTS (SELECT '1'
FROM okl_trx_assets tas
WHERE tas.id = tal.tas_id
AND tas.tas_type = 'ALI'
AND tas.tsu_code NOT IN ('PROCESSED','CANCELED')
);
SELECT '1'
FROM iex_case_objects ico,
iex_delinquencies_all del
WHERE ico. cas_id = del.case_id
AND del.status ='DELINQUENT'
AND ico.object_id = p_dnz_chr_id;
SELECT '1'
FROM okc_k_lines_b cle,
okc_statuses_b sts
WHERE sts.code = cle.sts_code
AND sts.ste_code IN ('HOLD','EXPIRED','TERMINATED','CANCELLED')
AND cle.id = p_kle_id;
SELECT '1'
FROM okl_k_headers khr,
okl_pools pol
WHERE pol.legal_entity_id <> khr.legal_entity_id
AND pol.id =p_pol_id
AND khr.id = p_khr_id;
SELECT lookup_code,
meaning
FROM fnd_lookups
WHERE LOOKUP_TYPE LIKE 'OKL_POOL_REJECT_REASON'
ORDER BY LOOKUP_CODE;
SELECT id pox_id,transaction_number FROM OKL_POOL_TRANSACTIONS pools
where pools.transaction_status in (G_POOL_TRX_STATUS_INCOMPLETE,G_POOL_TRX_STATUS_NEW,G_POOL_TRX_STATUS_APPREJ)
and pools.transaction_type='ADD' and pools.transaction_reason='ADJUSTMENTS'
and pools.pol_id=p_pol_id;
SELECT legal_entity_id
FROM okl_pools
WHERE id = p_pol_id;
Okl_Pool_Pvt.update_pool_transaction(p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_poxv_rec => lp_poxv_rec
,x_poxv_rec => lx_poxv_rec);
SELECT pocv.poc_id
,pocv.contract_number
,pocv.asset_number
,pocv.lessee
,pocv.stream_type_name
,pocv.sty_subclass_code
,pocv.sty_subclass
-- mvasudev, 09/28/2004, Bug#3909240
,pocv.stream_type_purpose
,hcp.credit_classification
FROM okl_pool_contents_uv pocv
,hz_customer_profiles hcp
-- pool_number
WHERE pocv.pol_id = p_pol_id
-- customer
AND NVL(pocv.cust_object1_id1,G_DEFAULT_NUM) = NVL(p_cust_object1_id1, NVL(pocv.cust_object1_id1,G_DEFAULT_NUM))
AND NVL(pocv.sic_code,G_DEFAULT_CHAR) = NVL(p_sic_code, NVL(pocv.sic_code,G_DEFAULT_CHAR))
-- contract
AND NVL(pocv.dnz_chr_id,G_DEFAULT_NUM) = NVL(p_dnz_chr_id, NVL(pocv.dnz_chr_id,G_DEFAULT_NUM))
AND NVL(pocv.pre_tax_yield,G_DEFAULT_NUM) BETWEEN NVL(p_pre_tax_yield_from, NVL(pocv.pre_tax_yield,G_DEFAULT_NUM))
AND NVL(p_pre_tax_yield_to, NVL(pocv.pre_tax_yield,G_DEFAULT_NUM))
AND NVL(pocv.book_classification,G_DEFAULT_CHAR) = NVL(p_book_classification, NVL(pocv.book_classification,G_DEFAULT_CHAR))
AND NVL(pocv.pdt_id,G_DEFAULT_NUM) = NVL(p_pdt_id, NVL(pocv.pdt_id,G_DEFAULT_NUM))
AND NVL(pocv.start_date, G_DEFAULT_DATE)
BETWEEN NVL(p_start_from_date, NVL(pocv.start_date, G_DEFAULT_DATE))
AND NVL(p_start_to_date, NVL(pocv.start_date, G_DEFAULT_DATE))
AND NVL(pocv.end_date,G_FINAL_DATE)
BETWEEN NVL(p_end_from_date, NVL(pocv.end_date,G_FINAL_DATE))
AND NVL(p_end_to_date, NVL(pocv.end_date,G_FINAL_DATE))
AND NVL(pocv.tax_owner,G_DEFAULT_CHAR) = NVL(p_tax_owner, NVL(pocv.tax_owner,G_DEFAULT_CHAR))
-- asset
/* cklee, 04/23/2003
AND NVL(pocv.asset_id,G_DEFAULT_NUM) = NVL(p_asset_id, NVL(pocv.asset_id,G_DEFAULT_NUM))
AND NVL(UPPER(pocv.model_number),G_DEFAULT_CHAR) LIKE NVL(UPPER(p_model_number),NVL(UPPER(pocv.model_number),G_DEFAULT_CHAR))
AND NVL(UPPER(pocv.manufacturer_name),G_DEFAULT_CHAR) LIKE NVL(UPPER(p_manufacturer_name),NVL(UPPER(pocv.manufacturer_name),G_DEFAULT_CHAR))
AND NVL(pocv.item_id1,G_DEFAULT_NUM) = NVL(p_item_id1, NVL(pocv.item_id1,G_DEFAULT_NUM))
AND NVL(pocv.vendor_id1,G_DEFAULT_NUM) = NVL(p_vendor_id1, NVL(pocv.vendor_id1,G_DEFAULT_NUM))
AND NVL(pocv.oec,G_DEFAULT_NUM) BETWEEN NVL(p_oec_from, NVL(pocv.oec,G_DEFAULT_NUM))
AND NVL(p_oec_to, NVL(pocv.oec,G_DEFAULT_NUM))
AND NVL(pocv.residual_percentage,G_DEFAULT_NUM) = NVL(p_residual_percentage, NVL(pocv.residual_percentage,G_DEFAULT_NUM))
*/
-- streams
-- mvasudev, 11.5.10
--AND NVL(pocv.sty_id,G_DEFAULT_NUM) = NVL(p_sty_id, NVL(pocv.sty_id,G_DEFAULT_NUM))
AND NVL(pocv.sty_subclass_code,G_DEFAULT_CHAR) = NVL(p_stream_type_subclass, NVL(pocv.sty_subclass_code,G_DEFAULT_CHAR))
-- end, mvasudev, 11.5.10
AND NVL(pocv.streams_from_date, G_DEFAULT_DATE)
BETWEEN NVL(p_streams_from_date, NVL(pocv.streams_from_date, G_DEFAULT_DATE))
AND NVL(p_streams_to_date, NVL(pocv.streams_to_date, G_FINAL_DATE))
-- cklee 04/10/2003
AND EXISTS (SELECT '1'
FROM okl_pool_contents pc
WHERE pc.id = pocv.POC_ID
AND pc.status_code IN (p_allowed_sts))
-- cklee 04/10/2003
--Bug # 6691554 Changes for Cust Credit Classification Lov Start
AND pocv.cust_object1_id1 = hcp.party_id(+)
AND hcp.cust_account_id(+) = -1
AND hcp.site_use_id(+) IS NULL
AND NVL(hcp.credit_classification(+),G_DEFAULT_CHAR) = NVL(p_cust_crd_clf_code, NVL(hcp.credit_classification(+),G_DEFAULT_CHAR))
--Bug # 6691554 Changes for Cust Credit Classification Lov End
ORDER BY pocv.contract_number
,pocv.asset_number
,pocv.stream_type_name;
SELECT NVL(SUM(NVL(ele.AMOUNT,0)),0) STREAM_AMOUNT
FROM okl_streams strm
,okl_strm_elements ele
,okl_pool_contents cnt
WHERE strm.id = ele.stm_id
AND cnt.ID = p_poc_id
AND strm.ID = cnt.STM_ID
AND strm.say_code = 'CURR'
AND strm.active_yn = 'Y'
AND cnt.status_code IN (G_POC_STS_PENDING)
AND ele.STREAM_ELEMENT_DATE
BETWEEN cnt.STREAMS_FROM_DATE AND NVL(cnt.STREAMS_TO_DATE,G_FINAL_DATE);
Okl_Pool_Pvt.delete_pool_contents(p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_pocv_tbl => lp_pocv_tbl);
Okl_Pool_Pvt.update_pool_transaction(p_api_version => p_api_version
,p_init_msg_list => p_init_msg_list
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,p_poxv_rec => lp_poxv_rec
,x_poxv_rec => lx_poxv_rec);
SELECT poc.id
FROM okl_pool_contents poc,
okl_pools pol,
okl_strm_type_b sty,
okc_k_headers_b CHR
WHERE pol.id = p_pol_id
AND pol.id = poc.pol_id
AND poc.sty_id = sty.id
AND sty.STREAM_TYPE_SUBCLASS ='RENT'
AND poc.KHR_ID = CHR.id
AND CHR.sts_code ='EVERGREEN'
AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE) ;
SELECT poc.id
FROM okl_pool_contents poc,
okc_k_headers_b chrb,
okl_k_headers khrb
WHERE poc.pol_id = p_pol_id
AND poc.khr_id = chrb.id
AND poc.khr_id = khrb.id
-- cklee 04/10/2003 never reconcile historical data
AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
-- cklee 04/10/2003 never reconcile historical data
AND (chrb.sts_code NOT IN ('BOOKED','EVERGREEN') OR khrb.assignable_yn <> 'Y');
SELECT poc.id
FROM okl_pool_contents poc,
iex_case_objects ico,
iex_delinquencies_all del
WHERE poc.pol_id = p_pol_id
AND poc.khr_id = ico.object_id
AND ico. cas_id = del.case_id
-- cklee 04/10/2003 never reconcile historical data
AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
-- cklee 04/10/2003 never reconcile historical data
AND del.status = 'DELINQUENT';
SELECT poc.id
FROM okl_pool_contents poc,
okc_k_headers_b chrb
WHERE poc.pol_id = p_pol_id
AND poc.khr_id = chrb.id
-- cklee 04/10/2003 never reconcile historical data
AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
-- cklee 04/10/2003 never reconcile historical data
-- AND chrb.sts_code = 'BOOKED'
AND EXISTS -- revision contract: rebook, split contract, reverse
(SELECT '1'
FROM okl_trx_contracts trxb
WHERE trxb.tcn_type IN ('TRBK','SPLC','RVS')
-- AND trxb.tsu_code = 'ENTERED'
AND trxb.tsu_code NOT IN ('PROCESSED', 'ERROR','CANCELED') -- condition changes 01/13/2003 cklee
AND trxb.khr_id = poc.khr_id
)
AND EXISTS -- split assets, split assets components
(SELECT '1'
FROM okl_txd_assets_v tdas,
okl_txl_assets_b talb,
okc_k_lines_b cleb
WHERE cleb.id = talb.kle_id
AND talb.ID = tdas.TAL_ID
AND talb.TAL_TYPE = 'ALI'
AND cleb.cle_id = poc.kle_id -- top line id
AND talb.dnz_khr_id = poc.khr_id
AND EXISTS (SELECT '1'
FROM okl_trx_assets tas
WHERE tas.id = talb.tas_id
AND tas.tas_type = 'ALI'
AND tas.tsu_code = 'PROCESSED')
);
SELECT poc.id
FROM okl_pool_contents poc
WHERE poc.khr_id
IN
(SELECT poc.khr_id
FROM okl_pool_contents poc,
okc_k_lines_b cleb,
okc_statuses_b stsb
WHERE poc.pol_id = p_pol_id
AND poc.kle_id = cleb.id
AND cleb.sts_code = stsb.code
-- cklee 04/10/2003 never reconcile historical data
AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
-- cklee 04/10/2003 never reconcile historical data
AND stsb.ste_code IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
);
SELECT poc.id
FROM okl_pool_contents poc,
okl_streams stmb
WHERE poc.pol_id = p_pol_id
AND poc.kle_id = stmb.kle_id
AND poc.sty_id = stmb.sty_id
AND (
-- if the streams are not active
stmb.active_yn <> 'Y'
--if stream elements do not exist
OR NOT EXISTS
(
SELECT '1'
FROM okl_strm_elements selb
WHERE selb.stm_id = stmb.id
)
);
SELECT poc.id
FROM okl_pool_contents poc
WHERE poc.pol_id = p_pol_id
AND NOT EXISTS
( SELECT '1'
FROM OKL_POOL_STREAMS_UV pols
WHERE poc.stm_id = pols.stm_id
AND pols.stream_say_code = 'CURR'
AND pols.stream_active_yn = 'Y'
-- mvasudev, 02/06/2004
--AND pols.stream_element_due_date > SYSDATE
)
-- cklee 04/10/2003 never reconcile historical data
AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
;
CURSOR l_okl_update_khr_dates_csr(p_pol_id IN NUMBER)
IS
SELECT poc.id, chrb.end_date
FROM okl_pool_contents poc,
okc_k_headers_b chrb
WHERE poc.pol_id = p_pol_id
AND poc.khr_id = chrb.id
-- cklee 04/10/2003 never reconcile historical data
AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
-- cklee 04/10/2003 never reconcile historical data
-- mvasudev,02/06/2004
AND poc.streams_to_date <> chrb.end_date+1;
CURSOR l_okl_update_dates_csr(p_pol_id IN NUMBER)
IS
SELECT poc.id, poc.stm_id
FROM okl_pool_contents poc
,okl_streams stmb
WHERE poc.pol_id = p_pol_id
AND poc.stm_id = stmb.id
AND stmb.say_code = 'CURR'
AND stmb.active_yn = 'Y'
-- cklee 04/10/2003 never reconcile historical data
AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
-- cklee 04/10/2003 never reconcile historical data
AND TRUNC(poc.streams_from_date) <> ( SELECT TRUNC(MIN(selb.stream_element_date))
FROM okl_strm_elements selb
WHERE selb.stm_id = stmb.id
AND selb.date_billed IS NULL
AND selb.amount <> 0 -- vsgandhi : Bug 13997160
-- mvasudev, 02/06/2004
--AND selb.stream_element_date > SYSDATE
);
SELECT poc.id
FROM okl_pool_contents poc,
okl_pools pol,
okl_strm_type_b sty,
okc_k_headers_b CHR
WHERE pol.id = p_pol_id
AND pol.id = poc.pol_id
AND poc.sty_id = sty.id
AND sty.STREAM_TYPE_SUBCLASS ='RENT'
AND poc.KHR_ID = CHR.id
AND CHR.sts_code ='EVERGREEN'
AND poc.status_code = G_POC_STS_PENDING ;
SELECT poc.id
FROM okl_pool_contents poc,
okc_k_headers_b chrb,
okl_k_headers khrb
WHERE poc.pol_id = p_pol_id
AND poc.khr_id = chrb.id
AND poc.khr_id = khrb.id
-- cklee 04/10/2003 never reconcile historical data
AND poc.status_code IN (G_POC_STS_PENDING)
-- cklee 04/10/2003 never reconcile historical data
AND (chrb.sts_code NOT IN ('BOOKED','EVERGREEN') OR khrb.assignable_yn <> 'Y');
SELECT poc.id
FROM okl_pool_contents poc,
iex_case_objects ico,
iex_delinquencies_all del
WHERE poc.pol_id = p_pol_id
AND poc.khr_id = ico.object_id
AND ico. cas_id = del.case_id
-- cklee 04/10/2003 never reconcile historical data
AND poc.status_code IN (G_POC_STS_PENDING)
-- cklee 04/10/2003 never reconcile historical data
AND del.status = 'DELINQUENT';
SELECT poc.id
FROM okl_pool_contents poc,
okc_k_headers_b chrb
WHERE poc.pol_id = p_pol_id
AND poc.khr_id = chrb.id
-- cklee 04/10/2003 never reconcile historical data
AND poc.status_code IN (G_POC_STS_PENDING)
-- cklee 04/10/2003 never reconcile historical data
-- AND chrb.sts_code = 'BOOKED'
AND EXISTS -- revision contract: rebook, split contract, reverse
(SELECT '1'
FROM okl_trx_contracts trxb
WHERE trxb.tcn_type IN ('TRBK','SPLC','RVS')
-- AND trxb.tsu_code = 'ENTERED'
AND trxb.tsu_code NOT IN ('PROCESSED', 'ERROR','CANCELED') -- condition changes 01/13/2003 cklee
AND trxb.khr_id = poc.khr_id
)
AND EXISTS -- split assets, split assets components
(SELECT '1'
FROM okl_txd_assets_v tdas,
okl_txl_assets_b talb,
okc_k_lines_b cleb
WHERE cleb.id = talb.kle_id
AND talb.ID = tdas.TAL_ID
AND talb.TAL_TYPE = 'ALI'
AND cleb.cle_id = poc.kle_id -- top line id
AND talb.dnz_khr_id = poc.khr_id
AND EXISTS (SELECT '1'
FROM okl_trx_assets tas
WHERE tas.id = talb.tas_id
AND tas.tas_type = 'ALI'
AND tas.tsu_code = 'PROCESSED')
);
SELECT poc.id
FROM okl_pool_contents poc
WHERE poc.khr_id
IN
(SELECT poc.khr_id
FROM okl_pool_contents poc,
okc_k_lines_b cleb,
okc_statuses_b stsb
WHERE poc.pol_id = p_pol_id
AND poc.kle_id = cleb.id
AND cleb.sts_code = stsb.code
-- cklee 04/10/2003 never reconcile historical data
AND poc.status_code IN (G_POC_STS_PENDING)
-- cklee 04/10/2003 never reconcile historical data
AND stsb.ste_code IN ('HOLD', 'EXPIRED', 'TERMINATED', 'CANCELLED')
);
SELECT poc.id
FROM okl_pool_contents poc
WHERE poc.pol_id = p_pol_id
AND NOT EXISTS
( SELECT '1'
FROM OKL_POOL_STREAMS_UV pols
WHERE poc.stm_id = pols.stm_id
AND pols.stream_say_code = 'CURR'
AND pols.stream_active_yn = 'Y'
-- mvasudev, 02/06/2004
--AND pols.stream_element_due_date > SYSDATE
)
-- cklee 04/10/2003 never reconcile historical data
AND poc.status_code IN (G_POC_STS_PENDING)
;
CURSOR l_okl_update_khr_dts_pend_csr(p_pol_id IN NUMBER)
IS
SELECT poc.id, chrb.end_date
FROM okl_pool_contents poc,
okc_k_headers_b chrb
WHERE poc.pol_id = p_pol_id
AND poc.khr_id = chrb.id
-- cklee 04/10/2003 never reconcile historical data
AND poc.status_code IN (G_POC_STS_NEW, G_POC_STS_ACTIVE)
-- cklee 04/10/2003 never reconcile historical data
-- mvasudev,02/06/2004
AND poc.streams_to_date <> chrb.end_date+1;
CURSOR l_okl_update_dates_pend_csr(p_pol_id IN NUMBER)
IS
SELECT poc.id, poc.stm_id
FROM okl_pool_contents poc
,okl_streams stmb
WHERE poc.pol_id = p_pol_id
AND poc.stm_id = stmb.id
AND stmb.say_code = 'CURR'
AND stmb.active_yn = 'Y'
-- cklee 04/10/2003 never reconcile historical data
AND poc.status_code IN (G_POC_STS_PENDING)
-- cklee 04/10/2003 never reconcile historical data
AND TRUNC(poc.streams_from_date) <> ( SELECT TRUNC(MIN(selb.stream_element_date))
FROM okl_strm_elements selb
WHERE selb.stm_id = stmb.id
AND selb.date_billed IS NULL
AND selb.amount <> 0 -- vsgandhi : Bug 13997160
-- mvasudev, 02/06/2004
--AND selb.stream_element_date > SYSDATE
);
SELECT MIN(selb.stream_element_date)
FROM okl_strm_elements selb
,okl_streams stmb
WHERE selb.stm_id = stmb.id
AND selb.date_billed IS NULL
AND selb.amount <> 0 -- vsgandhi : Bug 13997160
-- mvasudev, 02/06/2004
--AND selb.stream_element_date > SYSDATE
AND stmb.id = p_stm_id
AND stmb.say_code = 'CURR'
AND stmb.active_yn = 'Y';
Okl_Pool_Pvt.delete_pool_contents(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pocv_tbl => lp_pocv_tbl);
lp_pocv_tbl.DELETE; -- clear
Okl_Pool_Pvt.delete_pool_contents(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pocv_tbl => lp_pocv_tbl);
lp_pocv_tbl.DELETE; -- clear
Okl_Pool_Pvt.delete_pool_contents(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pocv_tbl => lp_pocv_tbl);
lp_pocv_tbl.DELETE; -- clear
Okl_Pool_Pvt.delete_pool_contents(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pocv_tbl => lp_pocv_tbl);
lp_pocv_tbl.DELETE; -- clear
Okl_Pool_Pvt.delete_pool_contents(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pocv_tbl => lp_pocv_tbl);
lp_pocv_tbl.DELETE; -- clear
Okl_Pool_Pvt.delete_pool_contents(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pocv_tbl => lp_pocv_tbl);
lp_pocv_tbl.DELETE; -- clear the contents to get updatable rows
FOR l_okl_update_khr_dates IN l_okl_update_khr_dates_csr(p_pol_id)
LOOP
lp_pocv_tbl(i).id := l_okl_update_khr_dates.id;
lp_pocv_tbl(i).streams_to_date := l_okl_update_khr_dates.end_date+1;
-- Update the rows with correct stream dates
Okl_Pool_Pvt.update_pool_contents(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pocv_tbl => lp_pocv_tbl,
x_pocv_tbl => lx_pocv_tbl);
lp_pocv_tbl.DELETE; -- clear the contents to get new updatable rows
FOR l_okl_update_dates IN l_okl_update_dates_csr(p_pol_id)
LOOP
l_date := NULL;
OPEN l_okl_valid_dates_csr(l_okl_update_dates.stm_id);
lp_pocv_tbl(i).id := l_okl_update_dates.id;
-- Update the rows with correct stream dates
Okl_Pool_Pvt.update_pool_contents(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pocv_tbl => lp_pocv_tbl,
x_pocv_tbl => lx_pocv_tbl);
Okl_Pool_Pvt.delete_pool_contents(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pocv_tbl => lp_pocv_tbl);
lp_pocv_tbl.DELETE; -- clear
Okl_Pool_Pvt.delete_pool_contents(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pocv_tbl => lp_pocv_tbl);
lp_pocv_tbl.DELETE; -- clear
Okl_Pool_Pvt.delete_pool_contents(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pocv_tbl => lp_pocv_tbl);
lp_pocv_tbl.DELETE; -- clear
Okl_Pool_Pvt.delete_pool_contents(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pocv_tbl => lp_pocv_tbl);
lp_pocv_tbl.DELETE; -- clear
Okl_Pool_Pvt.delete_pool_contents(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pocv_tbl => lp_pocv_tbl);
lp_pocv_tbl.DELETE; -- clear
Okl_Pool_Pvt.delete_pool_contents(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pocv_tbl => lp_pocv_tbl);
lp_pocv_tbl.DELETE; -- clear the contents to get updatable rows
FOR l_okl_update_khr_dates IN l_okl_update_khr_dts_pend_csr(p_pol_id)
LOOP
lp_pocv_tbl(i).id := l_okl_update_khr_dates.id;
lp_pocv_tbl(i).streams_to_date := l_okl_update_khr_dates.end_date+1;
-- Update the rows with correct stream dates
Okl_Pool_Pvt.update_pool_contents(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pocv_tbl => lp_pocv_tbl,
x_pocv_tbl => lx_pocv_tbl);
lp_pocv_tbl.DELETE; -- clear the contents to get new updatable rows
FOR l_okl_update_dates IN l_okl_update_dates_pend_csr(p_pol_id)
LOOP
l_date := NULL;
OPEN l_okl_valid_dates_csr(l_okl_update_dates.stm_id);
lp_pocv_tbl(i).id := l_okl_update_dates.id;
-- Update the rows with correct stream dates
Okl_Pool_Pvt.update_pool_contents(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pocv_tbl => lp_pocv_tbl,
x_pocv_tbl => lx_pocv_tbl);
Okl_Pol_Pvt.update_row(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => l_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_polv_rec => lp_polv_rec,
x_polv_rec => lx_polv_rec);
PROCEDURE update_pool_status_active(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_pol_id IN okl_pools.id%TYPE)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_pool_status_active';
SAVEPOINT update_pool_status_active_PVT;
update_pool_status(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pool_status => G_POL_STS_ACTIVE,
p_pol_id => p_pol_id);
ROLLBACK TO update_pool_status_active_PVT;
ROLLBACK TO update_pool_status_active_PVT;
ROLLBACK TO update_pool_status_active_PVT;
END update_pool_status_active;
PROCEDURE update_pool_status_expired(
p_api_version IN NUMBER
,p_init_msg_list IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,p_pol_id IN okl_pools.id%TYPE)
IS
l_api_name CONSTANT VARCHAR2(30) := 'update_pool_status_expired';
SAVEPOINT update_pool_status_expired_PVT;
update_pool_status(
p_api_version => p_api_version,
p_init_msg_list => p_init_msg_list,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data,
p_pool_status => G_POL_STS_EXPIRED,
p_pol_id => p_pol_id);
ROLLBACK TO update_pool_status_expired_PVT;
ROLLBACK TO update_pool_status_expired_PVT;
ROLLBACK TO update_pool_status_expired_PVT;
END update_pool_status_expired;
SELECT NVL(SUM(NVL(selb.amount,0)),0) amount
FROM okl_streams stmb
,okl_strm_elements selb
,okl_pool_contents pocb
,okl_strm_type_v styv
,okc_k_headers_b chrb
WHERE pocb.stm_id = stmb.id
AND stmb.id = selb.stm_id
AND pocb.id = p_poc_id
-- Bug#3520846,mvasudev, 3/22/2004
AND pocb.status_code = 'ACTIVE'
AND selb.date_billed IS NULL
/*
ankushar --Bug 6594724: Unable to terminate Investor Agreement with Residual Streams
Start changes
*/
AND stmb.sty_id = styv.id
AND pocb.khr_id = chrb.id
AND ( selb.stream_element_date > SYSDATE OR
( styv.stream_type_subclass = 'RESIDUAL'
and chrb.STS_CODE IN ('TERMINATED','EXPIRED')
)
)
/* ankushar Bug 6594724
End Changes
*/
-- end, mvasudev
AND selb.stream_element_date
BETWEEN pocb.streams_from_date AND NVL(pocb.streams_to_date,G_FINAL_DATE)
;
SELECT NVL(SUM(NVL(selb.amount,0)),0) amount
FROM okl_streams stmb
,okl_strm_elements selb
,okl_pool_contents pocb
WHERE pocb.stm_id = stmb.id
AND stmb.id = selb.stm_id
AND pocb.id = p_poc_id
AND selb.stream_element_date
BETWEEN pocb.streams_from_date AND NVL(pocb.streams_to_date,G_FINAL_DATE)
;
SELECT status_code
FROM okl_pools
WHERE id = p_pol_id;
SELECT '1'
FROM FND_CURRENCIES_VL
WHERE FND_CURRENCIES_VL.currency_code = currency_code;
IF l_action = 'update_pool' THEN
IF ((p_polv_rec.id is null) OR (p_polv_rec.id=OKL_API.G_MISS_NUM )) THEN
OKL_API.set_message(G_APP_NAME, G_REQUIRED_VALUE, G_COL_NAME_TOKEN, 'id');