[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
asset_msg_tbl.DELETE;
SELECT hou.NAME
FROM hr_operating_units hou
WHERE hou.organization_id = p_org_id;
SELECT CHR.start_date,
CHR.end_date,
CHR.sts_code
FROM okc_k_headers_b CHR
WHERE CHR.ID = p_chr_id;
SELECT trn.tmt_status_code status
--akrangan changes for sla tmt_status_code cr
FROM okl_trx_contracts trn
WHERE trn.khr_id = p_chr_id
AND trn.tcn_type IN ('TMT', 'ALT')
--rkuttiya added for 12.1.1 Multi GAAP
AND trn.representation_type = 'PRIMARY'
--
AND trn.tmt_status_code NOT IN ('PROCESSED', 'CANCELED');
SELECT ID
FROM okl_trx_quotes_v
WHERE khr_id = p_khr_id
AND qst_code = 'ACCEPTED'
AND (qtp_code LIKE 'TER%' OR qtp_code LIKE 'RES%');
SELECT ID,
tcn_type,
khr_id,
try_id,
tmt_status_code, --akrangan changes for sla tmt_status_code cr
date_transaction_occurred,
tmt_evergreen_yn,
tmt_close_balances_yn,
tmt_accounting_entries_yn,
tmt_cancel_insurance_yn,
tmt_asset_disposition_yn,
tmt_amortization_yn,
tmt_asset_return_yn,
tmt_contract_updated_yn,
tmt_recycle_yn,
tmt_validated_yn,
tmt_streams_updated_yn,
tmt_split_asset_yn,
tmt_generic_flag1_yn, -- RMUNJULU 2757312 Added
tmt_generic_flag2_yn, -- RMUNJULU 2757312 Added
tmt_generic_flag3_yn, -- RMUNJULU 2757312 Added
qte_id
FROM okl_trx_contracts
WHERE khr_id = p_khr_id
AND tcn_type IN ('TMT', 'ALT', 'EVG')
--rkuttiya added for 12.1.1. Multi GAAP
AND representation_type = 'PRIMARY'
--
-- akrangan bug 5354501 fix ADDED 'EVG'
AND tmt_status_code NOT IN ('PROCESSED', 'CANCELED');
lp_tcnv_rec.tmt_contract_updated_yn,
lp_tcnv_rec.tmt_recycle_yn,
lp_tcnv_rec.tmt_validated_yn,
lp_tcnv_rec.tmt_streams_updated_yn,
lp_tcnv_rec.tmt_split_asset_yn,
--RMUNJULU 17-DEC-02 Bug # 2484327 Added
lp_tcnv_rec.tmt_generic_flag1_yn, -- RMUNJULU 2757312 Added
lp_tcnv_rec.tmt_generic_flag2_yn, -- RMUNJULU 2757312 Added
lp_tcnv_rec.tmt_generic_flag3_yn, -- RMUNJULU 2757312 Added
lp_tcnv_rec.qte_id;
SELECT trx.ID,
trx.tcn_type,
trx.khr_id,
trx.try_id,
trx.tmt_status_code,
trx.date_transaction_occurred,
trx.tmt_evergreen_yn,
trx.tmt_close_balances_yn,
trx.tmt_accounting_entries_yn,
trx.tmt_cancel_insurance_yn,
trx.tmt_asset_disposition_yn,
trx.tmt_amortization_yn,
trx.tmt_asset_return_yn,
trx.tmt_contract_updated_yn,
trx.tmt_recycle_yn,
trx.tmt_validated_yn,
trx.tmt_streams_updated_yn,
trx.tmt_split_asset_yn,
trx.tmt_generic_flag1_yn, -- RMUNJULU 2757312 Added
trx.tmt_generic_flag2_yn, -- RMUNJULU 2757312 Added
trx.tmt_generic_flag3_yn, -- RMUNJULU 2757312 Added
trx.qte_id
FROM okl_trx_contracts trx
WHERE trx.ID = p_trn_id;
lp_tcnv_rec.tmt_contract_updated_yn,
lp_tcnv_rec.tmt_recycle_yn,
lp_tcnv_rec.tmt_validated_yn,
lp_tcnv_rec.tmt_streams_updated_yn,
lp_tcnv_rec.tmt_split_asset_yn,
lp_tcnv_rec.tmt_generic_flag1_yn, -- RMUNJULU 2757312 Added
lp_tcnv_rec.tmt_generic_flag2_yn, -- RMUNJULU 2757312 Added
lp_tcnv_rec.tmt_generic_flag3_yn, -- RMUNJULU 2757312 Added
lp_tcnv_rec.qte_id;
SELECT k.contract_number
FROM okc_k_headers_v k
WHERE k.ID = p_khr_id;
SELECT khr.ID,
khr.contract_number
FROM okc_k_headers_v khr
WHERE TRUNC (khr.end_date) < TRUNC (p_sysdate)
AND NVL (khr.sts_code, '?') IN ('BOOKED')
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.ID NOT IN (
-- Contracts which have unprocessed transactions
SELECT NVL (tcn.khr_id, -9999) khr_id
FROM okl_trx_contracts tcn
WHERE NVL (tcn.tcn_type, '?') IN ('TMT', 'ALT', 'EVG')
-- akrangan bug 5354501 fix added 'EVG'
AND tcn.tmt_status_code NOT IN
('CANCELED', 'PROCESSED')
--rkuttiya added for 12.1.1 Multi GAAP
AND tcn.representation_type = 'PRIMARY'
--
--akrangan changed for sla tmt_status_Code cr
AND tcn.khr_id = khr.ID) -- rmunjulu PERF
AND khr.ID NOT IN (
-- Contracts which have accepted quotes with no transactions
SELECT NVL (qte.khr_id, -9999) khr_id
FROM okl_trx_quotes_v qte
WHERE NVL (qte.accepted_yn, 'N') = 'Y'
AND NVL (qte.consolidated_yn, 'N') = 'N'
AND qte.khr_id = khr.ID -- rmunjulu PERF
AND qte.ID NOT IN (
SELECT NVL (tcn.qte_id, -9999) qte_id
FROM okl_trx_contracts tcn
WHERE NVL (tcn.tcn_type, '?') IN
('TMT', 'ALT', 'EVG')
--rkuttiya added for 12.1.1 Multi GAAP
AND tcn.representation_type = 'PRIMARY'
--
-- akrangan bug 5354501 fix added 'EVG'
AND tcn.qte_id = qte.ID)); -- rmunjulu PERF
SELECT k.ID,
k.contract_number,
t.ID trn_id -- RMUNJULU 17-NOV-02 Bug # 2484327 Added
FROM okc_k_headers_v k, okl_trx_contracts t
WHERE NVL (t.tmt_recycle_yn, '?') = 'Y'
AND NVL (t.tmt_status_code, '?') NOT IN ('PROCESSED', 'CANCELED')
--rkuttiya added for 12.1.1 Multi GAAP
AND t.representation_type = 'PRIMARY'
--
--akrangan changed for sla tmt_status_Code cr
AND NVL (t.tcn_type, '?') IN ('TMT', 'ALT', 'EVG')
-- akrangan bug 5354501 fix added 'EVG'
--AND NVL(K.sts_code,'?') IN('BOOKED')
AND k.scs_code IN ('LEASE', 'LOAN')
AND k.ID = t.khr_id;
SELECT khr.ID,
khr.contract_number
FROM okc_k_headers_v khr
WHERE khr.ID = p_khr_id
AND TRUNC (khr.end_date) < TRUNC (p_sysdate)
AND NVL (khr.sts_code, '?') IN ('BOOKED')
AND khr.scs_code IN ('LEASE', 'LOAN')
AND khr.ID NOT IN (
-- Contracts which have unprocessed transactions
SELECT NVL (tcn.khr_id, -9999) khr_id
FROM okl_trx_contracts tcn
WHERE NVL (tcn.tcn_type, '?') IN ('TMT', 'ALT', 'EVG')
-- akrangan bug 5354501 fix added 'EVG'
AND tcn.tmt_status_code NOT IN
('CANCELED', 'PROCESSED')
--akrangan changed for sla tmt_status_Code cr
--rkuttiya added for 12.1.1 Multi GAAP
AND tcn.representation_type = 'PRIMARY'
AND tcn.khr_id = khr.ID) -- rmunjulu PERF
AND khr.ID NOT IN (
-- Contracts which have accepted quotes with no transactions
SELECT NVL (qte.khr_id, -9999) khr_id
FROM okl_trx_quotes_v qte
WHERE NVL (qte.accepted_yn, 'N') = 'Y'
AND NVL (qte.consolidated_yn, 'N') = 'N'
AND qte.khr_id = khr.ID -- rmunjulu PERF
AND qte.ID NOT IN (
SELECT NVL (tcn.qte_id, -9999) qte_id
FROM okl_trx_contracts tcn
WHERE NVL (tcn.tcn_type, '?') IN
('TMT', 'ALT', 'EVG')
-- akrangan bug 5354501 fix added 'EVG'
--rkuttiya added for 12.1.1. Multi GAAP
AND representation_type = 'PRIMARY'
--
AND tcn.qte_id = qte.ID)); -- rmunjulu PERF
SELECT k.ID,
k.contract_number,
t.ID trn_id
FROM okc_k_headers_v k, okl_trx_contracts t
WHERE k.ID = p_khr_id
AND NVL (t.tmt_recycle_yn, '?') = 'Y'
AND NVL (t.tmt_status_code, '?') NOT IN ('PROCESSED', 'CANCELED')
--akrangan changed for sla tmt_status_Code cr
AND NVL (t.tcn_type, '?') IN ('TMT', 'ALT', 'EVG')
-- akrangan bug 5354501 fix added 'EVG'
AND k.scs_code IN ('LEASE', 'LOAN')
--rkuttiya added for 12.1.1 Multi GAAP
AND t.representation_type = 'PRIMARY'
--
AND k.ID = t.khr_id;
SELECT deal_type
FROM okl_k_headers
WHERE ID = p_khr_id;
SELECT SYSDATE
INTO db_sysdate
FROM DUAL;
SELECT opp.khr_id khr_id,
opp.object_value contract_number,
opp.trx_id trx_id
FROM okl_parallel_processes opp
WHERE opp.assigned_process = p_assigned_process;
SELECT deal_type
FROM okl_k_headers
WHERE ID = p_khr_id;
DELETE FROM okl_parallel_processes
WHERE assigned_process = p_assigned_processes;
CURSOR chk_update_header_csr IS
SELECT contract_number,
khr_id,
trx_id
FROM (SELECT khr.contract_number contract_number,
khr.ID khr_id,
NULL trx_id
FROM okc_k_headers_b khr
WHERE TRUNC (khr.end_date) < TRUNC (SYSDATE)
AND NVL (khr.sts_code, '?') IN ('BOOKED')
AND khr.scs_code IN ('LEASE', 'LOAN')
-- rmunjulu --start -- added the following or else same record picked twice
AND khr.ID NOT IN (
-- Contracts which have unprocessed transactions
SELECT NVL (tcn.khr_id, -9999) khr_id
FROM okl_trx_contracts tcn
WHERE NVL (tcn.tcn_type, '?') IN
('TMT', 'ALT', 'EVG')
-- akrangan bug 5354501 fix added 'EVG'
AND tcn.tmt_status_code NOT IN
('CANCELED', 'PROCESSED')
--akrangan changed for sla tmt_status_Code cr
--rkuttiya added for 12.1.1 Multi GAAP
AND tcn.representation_type = 'PRIMARY'
--
AND tcn.khr_id = khr.ID) -- rmunjulu PERF
AND khr.ID NOT IN (
-- Contracts which have accepted quotes with no transactions
SELECT NVL (qte.khr_id, -9999) khr_id
FROM okl_trx_quotes_v qte
WHERE NVL (qte.accepted_yn, 'N') = 'Y'
AND NVL (qte.consolidated_yn, 'N') = 'N'
AND qte.khr_id = khr.ID -- rmunjulu PERF
AND qte.ID NOT IN (
SELECT NVL (tcn.qte_id, -9999) qte_id
FROM okl_trx_contracts tcn
WHERE NVL (tcn.tcn_type, '?') IN
('TMT', 'ALT', 'EVG')
-- akrangan bug 5354501 fix added 'EVG'
--rkuttiya added for 12.1.1 Multi GAAP
AND tcn.representation_type = 'PRIMARY'
--
AND tcn.qte_id = qte.ID))
-- rmunjulu PERF
-- rmunjulu -- end
UNION
SELECT k.contract_number,
k.ID khr_id,
t.ID trx_id
FROM okc_k_headers_b k, okl_trx_contracts t
WHERE NVL (t.tmt_recycle_yn, '?') = 'Y'
AND NVL (t.tmt_status_code, '?') NOT IN
('PROCESSED', 'CANCELED')
--akrangan changed for sla tmt_status_Code cr
AND NVL (t.tcn_type, '?') IN ('TMT', 'ALT', 'EVG')
-- akrangan bug 5354501 fix added 'EVG'
AND k.scs_code IN ('LEASE', 'LOAN')
--rkuttiya added for 12.1.1 Multi GAAP
AND t.representation_type = 'PRIMARY'
--
AND k.ID = t.khr_id)
WHERE NOT EXISTS (
SELECT '1'
FROM okl_parallel_processes opp
WHERE contract_number = opp.object_value
AND opp.object_type = 'CONT_TERM'
AND opp.process_status IN
('PENDING_ASSIGNMENT', 'ASSIGNED'));
/* SELECT opp.object_value contract_number , count(KLE.id) line_count
FROM OKC_K_LINES_B KLE,
okl_parallel_processes opp
WHERE opp.khr_id = KLE.dnz_chr_id
AND KLE.sts_code = ('BOOKED')
AND opp.khr_id NOT IN(
SELECT NVL(TCN.khr_id,-9999) khr_id
FROM OKL_TRX_CONTRACTS TCN
WHERE NVL(TCN.tcn_type,'?') IN ('TMT','ALT','EVG') -- akrangan bug 5354501 fix added 'EVG'
AND TCN.tmt_status_code NOT IN ('CANCELED','PROCESSED')--akrangan changed for sla tmt_status_Code cr
AND TCN.khr_id = opp.khr_id)
AND opp.khr_id NOT IN (
SELECT NVL(QTE.khr_id,-9999) khr_id
FROM OKL_TRX_QUOTES_V QTE
WHERE NVL(QTE.accepted_yn,'N') = 'Y'
AND NVL(QTE.consolidated_yn,'N') = 'N'
AND QTE.khr_id = opp.khr_id
AND QTE.id NOT IN (
SELECT NVL(TCN.qte_id,-9999) qte_id
FROM OKL_TRX_CONTRACTS TCN
WHERE NVL(TCN.tcn_type,'?') IN ('TMT','ALT','EVG') -- akrangan bug 5354501 fix added 'EVG'
AND TCN.qte_id = QTE.id))
AND opp.object_type = 'CONT_TERM'
AND opp.assigned_process = p_seq_next
GROUP BY opp.object_value
UNION
SELECT opp.object_value contract_number, count(KLE.id) line_count
FROM okl_parallel_processes opp,
OKL_TRX_CONTRACTS T,
OKC_K_LINES_B KLE
WHERE NVL(T.tmt_recycle_yn,'?') = 'Y'
AND opp.khr_id = KLE.dnz_chr_id
AND KLE.sts_code = 'BOOKED'
AND NVL(T.tmt_status_code,'?') NOT IN('PROCESSED', 'CANCELED')--akrangan changed for sla tmt_status_Code cr
AND NVL(T.tcn_type,'?') IN( 'TMT', 'ALT','EVG') -- akrangan bug 5354501 fix added 'EVG'
AND opp.khr_id = T.khr_id
AND opp.object_type = 'CONT_TERM'
AND opp.assigned_process = p_seq_next
GROUP BY opp.object_value; */
SELECT opp.object_value contract_number,
COUNT (kle.ID) line_count
FROM okc_k_lines_b kle, okl_parallel_processes opp
WHERE opp.khr_id = kle.dnz_chr_id
AND kle.sts_code = ('BOOKED')
AND opp.khr_id NOT IN (
SELECT NVL (tcn.khr_id, -9999) khr_id
FROM okl_trx_contracts_all tcn
WHERE tcn.tcn_type IN ('TMT', 'ALT', 'EVG')
-- akrangan bug 5354501 fix added 'EVG'
AND tcn.tmt_status_code NOT IN
('CANCELED', 'PROCESSED')
--akrangan changed for sla tmt_status_Code cr
--rkuttiya added for 12.1.1 Multi GAAP
AND tcn.representation_Type = 'PRIMARY'
--
AND tcn.khr_id = opp.khr_id)
AND opp.khr_id NOT IN (
SELECT NVL (qte.khr_id, -9999) khr_id
FROM okl_trx_quotes_b qte
WHERE NVL (qte.accepted_yn, 'N') = 'Y'
AND NVL (qte.consolidated_yn, 'N') = 'N'
AND qte.khr_id = opp.khr_id
AND qte.ID NOT IN (
SELECT NVL (tcn.qte_id, -9999) qte_id
FROM okl_trx_contracts_all tcn
WHERE tcn.tcn_type IN ('TMT', 'ALT', 'EVG')
-- akrangan bug 5354501 fix added 'EVG'
--rkuttiya added for 12.1.1 Multi GAAP
AND tcn.representation_type = 'PRIMARY'
--
AND tcn.qte_id = qte.ID))
AND opp.object_type = 'CONT_TERM'
AND opp.assigned_process = p_seq_next
GROUP BY opp.object_value
UNION
SELECT opp.object_value contract_number,
COUNT (kle.ID) line_count
FROM okl_parallel_processes opp,
okl_trx_contracts t,
okc_k_lines_b kle
WHERE NVL (t.tmt_recycle_yn, '?') = 'Y'
AND opp.khr_id = kle.dnz_chr_id
AND kle.sts_code = 'BOOKED'
AND NVL (t.tmt_status_code, '?') NOT IN
('PROCESSED', 'CANCELED')
--akrangan changed for sla tmt_status_Code cr
AND t.tcn_type IN ('TMT', 'ALT', 'EVG')
-- akrangan bug 5354501 fix added 'EVG'
AND opp.khr_id = t.khr_id
--rkuttiya added for 12.1.1. Multi GAAP
AND t.representation_type = 'PRIMARY'
--
AND opp.object_type = 'CONT_TERM'
AND opp.assigned_process = p_seq_next
GROUP BY opp.object_value;
SELECT okl_opp_seq.NEXTVAL
INTO l_seq_next
FROM DUAL;
FOR chk_update_header_csr_rec IN chk_update_header_csr
LOOP
INSERT INTO okl_parallel_processes
(object_type,
object_value,
assigned_process,
process_status,
start_date,
khr_id,
trx_id
)
VALUES ('CONT_TERM',
chk_update_header_csr_rec.contract_number,
TO_CHAR (l_seq_next),
'PENDING_ASSIGNMENT',
SYSDATE,
chk_update_header_csr_rec.khr_id,
chk_update_header_csr_rec.trx_id
);
l_contract_tab.DELETE (i);
l_contract_tab.DELETE (i);
UPDATE okl_parallel_processes
SET assigned_process =
l_seq_next
|| '-'
|| l_sort_tab1 (i).worker_number,
volume = l_sort_tab1 (i).line_count,
process_status = 'ASSIGNED'
WHERE object_type = 'CONT_TERM'
AND object_value = l_sort_tab1 (i).contract_number
AND process_status = 'PENDING_ASSIGNMENT';
l_sort_tab1.DELETE (i);
DELETE okl_parallel_processes
WHERE process_status = 'PENDING_ASSIGNMENT'
AND assigned_process = TO_CHAR (l_seq_next);