[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT HOU.name
FROM HR_OPERATING_UNITS HOU
WHERE HOU.organization_id = p_org_id;
SELECT DISTINCT CHR.id,
CHR.contract_number,
CHR.start_date,
CHR.end_date,
CHR.sts_code,
CHR.date_terminated
FROM OKL_POOLS POL,
OKL_POOL_CONTENTS POC,
OKC_K_HEADERS_B KHR,
OKC_K_HEADERS_B CHR
WHERE KHR.id = p_ia_id
AND KHR.id = POL.khr_id
AND POL.id = POC.pol_id
AND POL.status_code = 'ACTIVE' -- Pool status
AND POC.status_code = POL.status_code
AND POC.khr_id = CHR.id;
SELECT 1 id
FROM DUAL WHERE EXISTS (
SELECT POC.id
FROM OKL_POOLS POL,
OKL_POOL_CONTENTS POC,
OKC_K_HEADERS_B CHR
WHERE CHR.id = p_ia_id
AND CHR.id = POL.khr_id
AND POL.id = POC.pol_id
AND NVL(POC.streams_to_date, CHR.end_date+1) > CHR.end_date
AND POL.status_code = 'ACTIVE'
AND POC.status_code = POL.status_code);
SELECT 1 id
FROM DUAL WHERE EXISTS (
SELECT SEL.id
FROM OKL_STREAMS STM,
OKL_STRM_ELEMENTS SEL,
OKL_STRM_TYPE_B STY,
OKL_POOLS POL,
OKL_POOL_CONTENTS POC,
OKC_K_HEADERS_B CHR
WHERE CHR.id = p_ia_id
AND CHR.id = POL.khr_id
AND POL.id = POC.pol_id
AND POL.status_code = 'ACTIVE'
AND POC.status_code = POL.status_code
AND POC.sty_id = STM.sty_id
AND STM.id = SEL.stm_id
AND SEL.stream_element_date > CHR.end_date
AND POC.kle_id = STM.kle_id
AND STM.say_code = 'CURR' -- CURRENT
AND STM.active_yn = G_YES -- ACTIVE
AND STM.sty_id = STY.id
AND NVL(STY.billable_yn,G_NO) = G_YES); -- BILLABLE
SELECT 1 id
FROM DUAL WHERE EXISTS (
SELECT SEL.id
FROM OKL_STREAMS STM,
OKL_STRM_ELEMENTS SEL,
OKL_STRM_TYPE_B STY,
OKL_POOLS POL,
OKL_POOL_CONTENTS POC,
OKC_K_HEADERS_B KHR
WHERE KHR.id = p_ia_id
AND KHR.id = POL.khr_id
AND POL.id = POC.pol_id
AND POL.status_code = 'ACTIVE'
AND POC.status_code = POL.status_code
AND POC.sty_id = STM.sty_id
AND STM.id = SEL.stm_id
AND (SEL.stream_element_date BETWEEN POC.streams_from_date
AND POC.streams_to_date)
AND POC.kle_id = STM.kle_id
AND STM.say_code = 'CURR' -- CURRENT
AND STM.active_yn = G_YES -- ACTIVE
AND SEL.date_billed IS NULL -- Not billed
AND STM.sty_id = STY.id
AND NVL(STY.billable_yn,G_NO) = G_YES); -- BILLABLE
SELECT 1 id
FROM DUAL WHERE EXISTS (
SELECT
ste.id sel_id
FROM
okl_strm_elements ste,
okl_streams stm,
okl_strm_type_v sty
WHERE ste.amount <> 0
AND stm.id = ste.stm_id
AND sty.stream_type_subclass IS NULL
AND sty.id IN (p_invdisbas_sty_id,p_invpbl_sty_id,p_prindisbas_sty_id,
p_prinpbl_sty_id,p_intdisbas_sty_id,p_intpbl_sty_id, p_ppddisbas_sty_id,p_ppdpbl_sty_id)
AND ste.date_billed IS NULL -- Once disb is done date_billed is populated
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND sty.id = stm.sty_id
AND sty.billable_yn = 'N'
AND stm.source_id = p_ia_id); -- Investor Agreement is now stored on disb stream
SELECT 1 id
FROM DUAL WHERE EXISTS (
SELECT
ste.id sel_id
FROM
okl_strm_elements ste,
okl_streams stm,
okl_strm_type_v sty
WHERE ste.amount <> 0
AND stm.id = ste.stm_id
AND sty.stream_type_subclass = 'INVESTOR_DISBURSEMENT'
AND ste.date_billed IS NULL -- Once disb is done date_billed is populated
AND stm.active_yn = 'Y'
AND stm.say_code = 'CURR'
AND sty.id = stm.sty_id
AND sty.billable_yn = 'N'
AND stm.source_id = p_ia_id); -- Investor Agreement is now stored on disb stream
SELECT CHR.id,
CHR.contract_number,
CHR.START_DATE,
CHR.end_date,
CHR.sts_code, -- Should be ACTIVE
CHR.scs_code, -- should be INVESTOR
KHR.pdt_id,
POL.id pool_id,
POL.pool_number
FROM OKC_K_HEADERS_B CHR,
OKL_K_HEADERS KHR,
OKL_POOLS POL
WHERE CHR.id = p_ia_id
AND CHR.id = KHR.id
AND CHR.id = POL.khr_id;
SELECT TRN.tsu_code
FROM OKL_TRX_CONTRACTS TRN
WHERE TRN.khr_id = p_ia_id
--rkuttiya added for 12.1.1 Multi GAAP
AND TRN.representation_type = 'PRIMARY'
--
AND TRN.tcn_type = 'IAT';
SELECT CLE.id,
CLE.name,
CLE.sts_code
FROM OKC_K_HEADERS_B CHR,
OKC_K_LINES_V CLE
WHERE CHR.id = p_ia_id
AND CHR.id = CLE.dnz_chr_id
AND CLE.sts_code = CHR.sts_code;
SELECT POX.id
FROM OKL_POOL_TRANSACTIONS POX,
OKL_POOLS POL
WHERE POL.id=POX.pol_id
AND POX.transaction_status <> 'COMPLETE'
AND POL.khr_id =p_ia_id
AND POX.transaction_type='ADD'
AND POX.transaction_reason='ADJUSTMENTS';
PROCEDURE pop_or_insert_transaction(
p_ia_rec IN ia_rec_type,
p_sys_date IN DATE,
x_trn_already_yn OUT NOCOPY VARCHAR2,
px_tcnv_rec IN OUT NOCOPY tcnv_rec_type,
p_validate_success IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
-- Get the trn if exists
CURSOR get_trn_csr ( p_ia_id IN NUMBER ) IS
SELECT TRN.id,
TRN.trx_number,
TRN.tsu_code,
TRN.tcn_type,
TRN.try_id,
TRN.khr_id,
TRN.tmt_validated_yn,
TRN.tmt_accounting_entries_yn,
TRN.tmt_contract_updated_yn,
TRN.tmt_recycle_yn,
TRN.tmt_generic_flag1_yn,
TRN.tmt_generic_flag2_yn,
TRN.tmt_generic_flag3_yn,
TRN.legal_entity_id
FROM OKL_TRX_CONTRACTS TRN
WHERE TRN.khr_id = p_ia_id
--rkuttiya added for 12.1.1 Multi GAAP
AND TRN.representation_type = 'PRIMARY'
--
AND TRN.tcn_type = 'IAT';
SELECT legal_entity_id
FROM Okl_k_headers
WHERE khr_id = p_ia_id;
'OKL_AM_TERMINATE_INV_AGMT_PVT.pop_or_insert_transaction.',
'Begin(+)');
SAVEPOINT pop_insert_trn_trx;
lx_tcnv_rec.tmt_contract_updated_yn := get_trn_rec.tmt_contract_updated_yn;
-- Insert TRN if not exists
-- *************
IF lx_tcnv_rec.id IS NULL
OR lx_tcnv_rec.id = G_MISS_NUM THEN
-- Get the Transaction Id
OKL_AM_UTIL_PVT.get_transaction_id (
p_try_name => 'Termination',
x_return_status => l_return_status,
x_try_id => l_try_id);
'OKL_AM_TERMINATE_INV_AGMT_PVT.pop_or_insert_transaction.',
'End(-)');
ROLLBACK TO pop_insert_trn_trx;
'OKL_AM_TERMINATE_INV_AGMT_PVT.pop_or_insert_transaction.',
'EXP - G_EXCEPTION_ERROR');
ROLLBACK TO pop_insert_trn_trx;
'OKL_AM_TERMINATE_INV_AGMT_PVT.pop_or_insert_transaction.',
'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
ROLLBACK TO pop_insert_trn_trx;
'OKL_AM_TERMINATE_INV_AGMT_PVT.pop_or_insert_transaction.',
'EXP - OTHERS');
END pop_or_insert_transaction;
PROCEDURE update_ia_and_lines(
p_ia_rec IN ia_rec_type,
p_termination_date IN DATE,
p_ialn_tbl IN ialn_tbl_type,
x_return_status OUT NOCOPY VARCHAR2) IS
lp_chrv_rec OKL_OKC_MIGRATION_PVT.chrv_rec_type;
'OKL_AM_TERMINATE_INV_AGMT_PVT.update_ia_and_lines.',
'Begin(+)');
SAVEPOINT update_ia_and_lines_trx;
OKL_CONTRACT_PUB.update_contract_line(
p_api_version => l_api_version,
p_init_msg_list => G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_clev_rec => lp_clev_rec,
p_klev_rec => lp_klev_rec,
x_clev_rec => lx_clev_rec,
x_klev_rec => lx_klev_rec);
OKL_CONTRACT_PUB.update_contract_header(
p_api_version => l_api_version,
p_init_msg_list => G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_chrv_rec => lp_chrv_rec,
p_khrv_rec => lp_khrv_rec,
x_chrv_rec => lx_chrv_rec,
x_khrv_rec => lx_khrv_rec);
'OKL_AM_TERMINATE_INV_AGMT_PVT.update_ia_and_lines.',
'End(-)');
ROLLBACK TO update_ia_and_lines_trx;
'OKL_AM_TERMINATE_INV_AGMT_PVT.update_ia_and_lines.',
'EXP - G_EXCEPTION_ERROR');
ROLLBACK TO update_ia_and_lines_trx;
'OKL_AM_TERMINATE_INV_AGMT_PVT.update_ia_and_lines.',
'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
ROLLBACK TO update_ia_and_lines_trx;
'OKL_AM_TERMINATE_INV_AGMT_PVT.update_ia_and_lines.',
'EXP - OTHERS');
END update_ia_and_lines;
PROCEDURE update_pools(
p_ia_rec IN ia_rec_type,
p_termination_date IN DATE,
x_return_status OUT NOCOPY VARCHAR2) IS
-- get the active pools for the IA
CURSOR get_pools_csr(p_ia_id IN NUMBER) IS
SELECT POL.id
FROM OKL_POOLS POL,
OKC_K_HEADERS_B KHR
WHERE KHR.id = p_ia_id
AND KHR.id = POL.khr_id
AND POL.status_code = 'ACTIVE'; -- ACTIVE
SELECT POC.id,
POC.transaction_number_in
FROM OKL_POOLS POL,
OKL_POOL_CONTENTS POC
WHERE POL.id = p_pol_id
AND POC.pol_id = POL.id
AND POC.status_code = 'ACTIVE';
CURSOR get_k_update_csr(p_pol_id IN NUMBER) IS
SELECT DISTINCT POCA.khr_id
FROM OKL_POOL_CONTENTS POCA
WHERE POCA.pol_id = p_pol_id
AND NOT EXISTS (
SELECT POCB.khr_id
FROM OKL_POOL_CONTENTS POCB
WHERE POCB.pol_id <> POCA.pol_id
AND POCB.khr_id = POCA.khr_id
AND POCB.status_code = 'ACTIVE'
);
SELECT pol.legal_entity_id
FROM okl_pools pol
WHERE pol.id = p_pool_id;
'OKL_AM_TERMINATE_INV_AGMT_PVT.update_pools.',
'Begin(+)');
SAVEPOINT update_pools_trx;
OKL_POL_PVT.update_row(
p_api_version => l_api_version,
p_init_msg_list => G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_polv_rec => lp_polv_rec,
x_polv_rec => lx_polv_rec);
OKL_POX_PVT.insert_row(
p_api_version => l_api_version,
p_init_msg_list => G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_poxv_rec => lp_poxv_rec,
x_poxv_rec => lx_poxv_rec);
OKL_POC_PVT.update_row(
p_api_version => l_api_version,
p_init_msg_list => G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_pocv_rec => lp_pocv_rec,
x_pocv_rec => lx_pocv_rec);
FOR get_k_update_rec IN get_k_update_csr (get_pools_rec.id)LOOP
l_chrv_rec.id := get_k_update_rec.khr_id;
l_khrv_rec.id := get_k_update_rec.khr_id;
OKL_CONTRACT_PUB.update_contract_header(
p_api_version => l_api_version,
p_init_msg_list => OKL_API.G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_chrv_rec => l_chrv_rec,
p_khrv_rec => l_khrv_rec,
x_chrv_rec => lx_chrv_rec,
x_khrv_rec => lx_khrv_rec);
'OKL_AM_TERMINATE_INV_AGMT_PVT.update_pools.',
'End(-)');
ROLLBACK TO update_pools_trx;
'OKL_AM_TERMINATE_INV_AGMT_PVT.update_pools.',
'EXP - G_EXCEPTION_ERROR');
ROLLBACK TO update_pools_trx;
'OKL_AM_TERMINATE_INV_AGMT_PVT.update_pools.',
'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
ROLLBACK TO update_pools_trx;
'OKL_AM_TERMINATE_INV_AGMT_PVT.update_pools.',
'EXP - OTHERS');
END update_pools;
PROCEDURE update_investor_agreement(
p_ia_rec IN ia_rec_type,
p_termination_date IN DATE,
p_ialn_tbl IN ialn_tbl_type,
px_tcnv_rec IN OUT NOCOPY tcnv_rec_type,
p_overall_status IN VARCHAR2,
x_return_status OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
'OKL_AM_TERMINATE_INV_AGMT_PVT.update_investor_agreement.',
'Begin(+)');
SAVEPOINT update_ia_trx;
update_pools(
p_ia_rec => p_ia_rec,
p_termination_date => p_termination_date,
x_return_status => l_return_status);
update_ia_and_lines(
p_ia_rec => p_ia_rec,
p_termination_date => p_termination_date,
p_ialn_tbl => p_ialn_tbl,
x_return_status => l_return_status);
px_tcnv_rec.tmt_contract_updated_yn := G_YES;
'OKL_AM_TERMINATE_INV_AGMT_PVT.update_investor_agreement.',
'End(-)');
ROLLBACK TO update_ia_trx;
px_tcnv_rec.tmt_contract_updated_yn := G_NO;
'OKL_AM_TERMINATE_INV_AGMT_PVT.update_investor_agreement.',
'EXP - G_EXCEPTION_ERROR');
ROLLBACK TO update_ia_trx;
px_tcnv_rec.tmt_contract_updated_yn := G_NO;
'OKL_AM_TERMINATE_INV_AGMT_PVT.update_investor_agreement.',
'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
ROLLBACK TO update_ia_trx;
px_tcnv_rec.tmt_contract_updated_yn := G_NO;
'OKL_AM_TERMINATE_INV_AGMT_PVT.update_investor_agreement.',
'EXP - OTHERS');
END update_investor_agreement;
PROCEDURE update_transaction(
p_ia_rec IN ia_rec_type,
p_termination_date IN DATE,
p_tcnv_rec IN tcnv_rec_type,
x_return_status OUT NOCOPY VARCHAR2) IS
l_return_status VARCHAR2(1) := G_RET_STS_SUCCESS;
'OKL_AM_TERMINATE_INV_AGMT_PVT.update_transaction.',
'Begin(+)');
SAVEPOINT update_transaction_trx;
OKL_TRX_CONTRACTS_PUB.update_trx_contracts(
p_api_version => l_api_version,
p_init_msg_list => G_FALSE,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data,
p_tcnv_rec => lp_tcnv_rec,
x_tcnv_rec => lx_tcnv_rec);
'OKL_AM_TERMINATE_INV_AGMT_PVT.update_transaction.',
'End(-)');
ROLLBACK TO update_transaction_trx;
'OKL_AM_TERMINATE_INV_AGMT_PVT.update_transaction.',
'EXP - G_EXCEPTION_ERROR');
ROLLBACK TO update_transaction_trx;
'OKL_AM_TERMINATE_INV_AGMT_PVT.update_transaction.',
'EXP - G_EXCEPTION_UNEXPECTED_ERROR');
ROLLBACK TO update_transaction_trx;
'OKL_AM_TERMINATE_INV_AGMT_PVT.update_transaction.',
'EXP - OTHERS');
END update_transaction;
SELECT CHR.sts_code status
FROM OKC_K_HEADERS_B CHR
WHERE CHR.id = p_ia_id;
SELECT sysdate INTO l_sys_date FROM DUAL;
pop_or_insert_transaction(
p_ia_rec => l_ia_rec,
p_sys_date => l_sys_date,
x_trn_already_yn => l_trn_already_yn,
px_tcnv_rec => l_tcnv_rec,
p_validate_success => l_overall_status,
x_return_status => l_return_status);
'OKL_AM_TERMINATE_INV_AGMT_PVT.pop_or_insert_transaction.',
'pop_or_insert_transaction = '||l_return_status );
update_transaction(
p_ia_rec => l_ia_rec,
p_termination_date => l_sys_date,
p_tcnv_rec => l_tcnv_rec,
x_return_status => l_return_status);
'update_transaction = '||l_return_status );
update_investor_agreement(
p_ia_rec => l_ia_rec,
p_termination_date => l_sys_date,
p_ialn_tbl => l_ialn_tbl,
px_tcnv_rec => l_tcnv_rec,
p_overall_status => l_overall_status,
x_return_status => l_return_status);
'OKL_AM_TERMINATE_INV_AGMT_PVT.update_investor_agreement.',
'update_investor_agreement = '||l_return_status );
update_transaction(
p_ia_rec => l_ia_rec,
p_termination_date => l_sys_date,
p_tcnv_rec => l_tcnv_rec,
x_return_status => l_return_status);
'update_transaction = '||l_return_status );
SELECT CHR.id,
CHR.contract_number
FROM OKC_K_HEADERS_B CHR
WHERE CHR.scs_code = 'INVESTOR' -- IA
AND CHR.sts_code = 'ACTIVE' -- ACTIVE
AND CHR.date_terminated IS NULL -- Not Terminated
AND CHR.end_date <= TRUNC(p_date) -- Ended -- RMUNJULU 115.4 3061748 Changed to pick equal dates
AND CHR.id NOT IN (SELECT TRX.khr_id FROM OKL_TRX_CONTRACTS TRX -- Dont get IA's with Processed TRN
WHERE TRX.tsu_code = 'PROCESSED'
--rkuttiya added for 12.1.1 Multi GAAP
AND TRX.representation_type = 'PRIMARY'
--
AND TRX.tcn_type = 'IAT');
SELECT SYSDATE INTO l_sys_date FROM DUAL;