[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT opp.khr_id khr_id
,opp.object_value contract_number
,opp.volume no_of_assets
FROM okl_parallel_processes opp
WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE -- 'ESG_PRB_UPGRADE_CONTRACT'
AND opp.process_status = 'ASSIGNED' -- Dont fetch any unallocated contracts for processing
AND opp.assigned_process = p_worker_id; -- Fetch only this worker related contracts
OKL_LLA_UTIL_PVT.update_external_id(p_chr_id => l_esg_upg_cntrcts_tbl(i).khr_id,
x_return_status => l_return_status);
fnd_msg_pub.delete_msg();
DELETE OKL_PARALLEL_PROCESSES opp
WHERE khr_id = l_khr_id_tbl(khr_index)
AND opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE -- 'ESG_PRB_UPGRADE_CONTRACT'
AND opp.assigned_process = p_worker_id; -- Fetch only this worker related contracts;
SELECT khr_id khr_id
,object_value contract_number
,volume no_of_assets
,process_status status
,'Pending Assignment' status_meaning
FROM OKL_PARALLEL_PROCESSES opp
WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE -- 'ESG_PRB_UPGRADE_CONTRACT'
AND opp.process_status = 'PENDING_ASSIGNMENT'
AND opp.assigned_process = p_process_sequence
UNION ALL
SELECT khr_id khr_id
,object_value contract_number
,volume no_of_assets
,process_status status
,'Revision in Progress' status_meaning
FROM OKL_PARALLEL_PROCESSES opp
--,fnd_lookups lkup
WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE -- 'ESG_PRB_UPGRADE_CONTRACT'
AND opp.process_status <> 'PENDING_ASSIGNMENT'
AND opp.assigned_process = p_process_sequence;
SELECT
(SELECT name from hr_operating_units where organization_id = p_org_id) org_id
,DECODE(p_criteria_set, 'CONTRACT', 'Contract - Criteria', 'REAMORT', 'Reamort', 'Revision - Criteria' ) criteria_set
,( SELECT DISTINCT legal_entity_name from XLE_LE_OU_LEDGER_V
WHERE legal_entity_id = p_le_id
AND rownum <= 1 ) le_id
,( SELECT contract_number FROM OKC_K_HEADERS_B WHERE id = p_khr_id ) khr_id
,OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_BOOK_CLASS',p_book_classification) book_classification
,( SELECT name from okl_products where id = p_pdt_id ) pdt_id
,OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_INTEREST_CALCULATION_BASIS',p_int_calc_method) int_calc_method
,OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_REVENUE_RECOGNITION_METHOD',p_rev_rec_method) rev_rec_method
,p_start_date_low start_date_low
,p_start_date_high start_date_high
,p_end_date_low end_date_low
,p_end_date_high end_date_high
,OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_UPG_INTRANSIT_CAT',p_in_transit_category) in_transit_category
,DECODE(p_mode_of_run, 'REVIEW', 'Review', 'SUBMIT', 'Submit' ) mode_of_run
,p_tag_name tag_name
,p_no_of_workers no_of_workers
FROM DUAL;
SELECT
chr.contract_number contract_number
,lkup.meaning pricing_status
,sif.sis_code pricing_code
,sif.orp_code pricing_orp_code
,DECODE( sif.sis_code,
'PROCESSING_FAILED', 1,
'PROCESS_ABORTED', 2,
'TIME_OUT', 3,
'SERVER_NA', 4, 100) pricing_error_level
FROM okc_k_headers_b chr -- Org Specific
,okl_k_headers khr
,okl_products pdt
,okl_ae_tmpt_sets_all aes
,okl_st_gen_tmpt_sets_all gts
,okl_stream_interfaces sif
,fnd_lookups lkup
WHERE chr.id = khr.id
AND chr.scs_code = 'LEASE'
AND chr.template_yn = 'N'
AND chr.orig_system_source_code <> 'OKL_REBOOK'
AND khr.pdt_id = pdt.id
AND pdt.aes_id = aes.id
AND aes.gts_id = gts.id
AND gts.pricing_engine = 'EXTERNAL'
AND sif.khr_id = chr.id
AND lkup.lookup_type = 'OKL_SIF_STATUS'
AND sif.sis_code = lkup.lookup_code
AND sif.orp_code = 'UPGRADE'
AND sif.sis_code IN ( 'PROCESSING_FAILED', 'PROCESS_ABORTED', 'TIME_OUT', 'SERVER_NA')
-- And make sure that the same contract was not upgraded successfully later on
AND NOT EXISTS
(
SELECT 1
FROM okl_stream_trx_data trx,
okl_stream_interfaces osi
WHERE osi.transaction_number = trx.transaction_number
AND osi.khr_id = chr.id
AND trx.last_trx_state = 'Y'
)
order by 5;
SELECT NVL(AMORT_INC_ADJ_REV_DT_YN, 'N')
INTO l_prb_enabled
FROM okl_sys_acct_opts_all
WHERE org_id = p_org_id;
log_msg(FND_FILE.LOG, 'Please select a valid In-Trasit Category.');
SELECT okl_opp_seq.NEXTVAL
INTO l_seq_next
FROM DUAL;
log_msg(FND_FILE.LOG, 'Before calling the Bulk Insert into the OKL_PARALLEL_PROCESSES' );
'INSERT INTO OKL_PARALLEL_PROCESSES(OBJECT_TYPE,OBJECT_VALUE,ASSIGNED_PROCESS' ||
',PROCESS_STATUS,CREATION_DATE,KHR_ID,VOLUME) ' ||
'SELECT ''' || G_ESG_PRB_KHR_UPG_OBJ_TYPE || ''' ' || -- OBJECT_TYPE
' ,chr.contract_number' || -- OBJECT_VALUE
' ,TO_CHAR( ''' || l_seq_next || ''' ) ' || -- ASSIGNED_PROCESS
' ,''PENDING_ASSIGNMENT'' ' || -- PROCESS_STATUS
' ,SYSDATE ' || -- CREATION_DATE
' ,chr.id ' || -- KHR_ID
' ,COUNT(cle.id) ' || -- VOLUME = Number of Assets
' FROM okc_k_headers_b chr, okl_k_headers khr, okc_k_lines_b cle ' ||
' ,okl_products pdt ,okl_ae_tmpt_sets aes , okl_st_gen_tmpt_sets gts ' ;
|| ' AND NOT EXISTS ( SELECT 1 FROM okl_stream_trx_data trx, okl_stream_interfaces osi '
|| ' WHERE osi.transaction_number = trx.transaction_number AND osi.khr_id = chr.id '
|| ' AND ( ( trx.last_trx_state = ''Y'' AND '
|| ' ((osi.orp_code = ''UPGRADE'' AND osi.sis_code = ''PROCESS_COMPLETE'') OR '
|| ' (osi.orp_code = ''AUTH'' AND osi.sis_code = ''PROCESS_COMPLETE'')) ) OR '
|| ' (osi.orp_code = ''UPGRADE'' AND osi.sis_code IN (''PROCESSING_REQUEST'', ''RET_DATA_RECEIVED'' )) )) ';
l_query_string := l_query_string || ' SELECT trx.khr_id orig_contract_id FROM okl_trx_contracts trx '
|| ' WHERE trx.khr_id_new IS NOT NULL AND trx.tsu_code = ''ENTERED'' AND trx.rbr_code is NOT NULL '
|| ' AND trx.tcn_type = ''TRBK'' AND trx.representation_type = ''PRIMARY'' ';
l_query_string := l_query_string || ' SELECT rsc.khr_id orig_contract_id FROM okl_rbk_selected_contract rsc, okc_k_headers_b chrb '
|| ' WHERE rsc.transaction_id IS NULL AND rsc.status <> ''PROCESSED'' AND chrb.id = rsc.khr_id ';
l_query_string := l_query_string || ' SELECT trq.dnz_khr_id khr_id FROM okl_trx_requests trq '
|| ' WHERE trq.request_type_code = ''PRINCIPAL_PAYDOWN'' AND trq.request_status_code NOT IN '
|| ' (''ACCEPTED'', ''REJECTED'', ''ERROR'' ,''PROCESSED'' '
|| ' ,''CANCELLED'' ,''REBOOK_IN_PROCESS'' ,''REBOOK_COMPLETE'' ) '
|| ' AND trq.tcn_id IS NULL AND trq.org_id = ' || p_org_id;
l_query_string := l_query_string || ' SELECT l.dnz_khr_id FROM OKL_TRX_ASSETS h, okl_txl_assets_b l '
|| ' WHERE h.id = l.tas_id AND h.tsu_code IN (''ENTERED'',''ERROR'') AND h.tas_type = ''ARC'' ';
l_query_string := l_query_string || ' SELECT khr_id FROM okl_trx_quotes_b '
|| ' WHERE partial_yn = ''Y'' and qst_code not IN (''ACCEPTED'',''COMPLETE'',''IN_PROCESS'') ';
log_msg(FND_FILE.LOG, 'Committed the Insertion of the OKL_PARALLEL_PROCESSES Records' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
UPDATE OKL_PARALLEL_PROCESSES opp
SET process_status = 'OKL_STATUS_INAPPROPRIATE'
WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE
AND opp.process_status = 'PENDING_ASSIGNMENT'
AND opp.assigned_process = TO_CHAR(l_seq_next)
AND EXISTS
( SELECT 'INVALID'
FROM okc_k_headers_all_b chr
WHERE chr.id = opp.khr_id
AND chr.sts_code NOT IN ( 'COMPLETE','BOOKED','APPROVED' )
);
UPDATE OKL_PARALLEL_PROCESSES opp
SET process_status = 'OKL_CLOB_EXISTS'
WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE
AND opp.process_status = 'PENDING_ASSIGNMENT'
AND opp.assigned_process = TO_CHAR(l_seq_next)
AND EXISTS
(
SELECT 'CLOB_EXISTS'
FROM okl_stream_trx_data trx,
okl_stream_interfaces osi
WHERE osi.transaction_number = trx.transaction_number
AND osi.khr_id = opp.khr_id
AND ( ( trx.last_trx_state = 'Y'
AND ( (osi.orp_code = 'UPGRADE' AND osi.sis_code = 'PROCESS_COMPLETE')
OR (osi.orp_code = 'AUTH' AND osi.sis_code = 'PROCESS_COMPLETE')
)
)
OR ( osi.orp_code = 'UPGRADE' AND
osi.sis_code IN ('PROCESSING_REQUEST', 'RET_DATA_RECEIVED' )
)
)
);
UPDATE OKL_PARALLEL_PROCESSES opp
SET process_status = 'OKL_MRBK_NOT_PROCESSED'
WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE
AND opp.process_status = 'PENDING_ASSIGNMENT'
AND opp.assigned_process = TO_CHAR(l_seq_next)
AND opp.khr_id IN
(
SELECT rsc.khr_id orig_contract_id
FROM okl_rbk_selected_contract rsc
WHERE opp.khr_id = rsc.khr_id
AND rsc.transaction_id IS NULL
AND rsc.status <> 'PROCESSED'
);
UPDATE OKL_PARALLEL_PROCESSES opp
SET process_status = 'OKL_ONLINE_RBK_NOT_PROCESSED'
WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE
AND opp.process_status = 'PENDING_ASSIGNMENT'
AND opp.assigned_process = TO_CHAR(l_seq_next)
AND opp.khr_id IN
(
SELECT trx.khr_id orig_contract_id
FROM okl_trx_contracts trx
WHERE trx.khr_id_new IS NOT NULL
AND trx.tsu_code = 'ENTERED'
AND trx.rbr_code IS NOT NULL
AND trx.tcn_type = 'TRBK'
AND trx.representation_type = 'PRIMARY'
);
UPDATE OKL_PARALLEL_PROCESSES opp
SET process_status = 'OKL_RVWD_NOT_PROCESSED'
WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE
AND opp.process_status = 'PENDING_ASSIGNMENT'
AND opp.assigned_process = TO_CHAR(l_seq_next)
AND opp.khr_id IN
(
SELECT l.dnz_khr_id
FROM OKL_TRX_ASSETS h,
okl_txl_assets_b l
WHERE h.id = l.tas_id
AND h.tsu_code IN ('ENTERED','ERROR')
AND h.tas_type = 'ARC'
);
UPDATE OKL_PARALLEL_PROCESSES opp
SET process_status = 'OKL_TQ_NOT_ACCEPTED'
WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE
AND opp.process_status = 'PENDING_ASSIGNMENT'
AND opp.assigned_process = TO_CHAR(l_seq_next)
AND opp.khr_id IN
(
SELECT khr_id
FROM okl_trx_quotes_b
WHERE partial_yn = 'Y'
AND qst_code NOT IN ('ACCEPTED','COMPLETE','IN_PROCESS')
);
UPDATE OKL_PARALLEL_PROCESSES opp
SET process_status = 'OKL_REVISION_IN_PROGRESS'
WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE
AND opp.process_status = 'PENDING_ASSIGNMENT'
AND opp.assigned_process = TO_CHAR(l_seq_next)
AND exists
(
select q.khr_id
from okl_trx_quotes_b q
where q.qtp_code like 'TER%' -- Termination quote
and NVL(q.consolidated_yn,'N') = 'N'
and q.partial_yn = 'Y'
and q.qst_code = 'ACCEPTED'
and q.khr_id = opp.khr_id
and q.id not in (select t.qte_id from okl_trx_contracts_all t where q.id = t.qte_id)
);
UPDATE OKL_PARALLEL_PROCESSES opp
SET process_status = 'OKL_REVISION_IN_PROGRESS'
WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE
AND opp.process_status = 'PENDING_ASSIGNMENT'
AND opp.assigned_process = TO_CHAR(l_seq_next)
AND exists
(
select q.khr_id
from okl_trx_quotes_b q, okl_trx_contracts_all t
where q.qtp_code like 'TER%'
and NVL(q.consolidated_yn,'N') = 'N'
and q.partial_yn = 'Y'
and q.khr_id = opp.khr_id
and q.id = t.qte_id
and t.tcn_type = 'ALT'
and t.tmt_status_code not in ('PROCESSED')
);
UPDATE OKL_PARALLEL_PROCESSES opp
SET process_status = 'OKL_REVISION_IN_PROGRESS'
WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE
AND opp.process_status = 'PENDING_ASSIGNMENT'
AND opp.assigned_process = TO_CHAR(l_seq_next)
AND exists
(
SELECT 1
FROM okl_trx_contracts ktrx
where ktrx.khr_id = opp.khr_id
AND ktrx.khr_id_new IS NULL
AND ktrx.tsu_code = 'ENTERED'
AND ktrx.rbr_code IS NOT NULL
AND ktrx.tcn_type = 'TRBK'
AND ktrx.representation_type = 'PRIMARY'
AND EXISTS (SELECT '1'
FROM okl_rbk_selected_contract rbk_khr
WHERE rbk_khr.khr_id = ktrx.khr_id
AND rbk_khr.status <> 'PROCESSED')
);
l_temp_upg_contracts_tbl.DELETE;
SELECT b.meaning
INTO l_k_status
FROM okc_k_headers_all_b a
,okc_statuses_tl b
WHERE a.contract_number = l_non_upg_contracts_tbl(non_upg_index).contract_number
AND a.sts_code = b.code
AND b.language = USERENV('LANG');
log_msg(FND_FILE.LOG, 'Updated the Records in OKL_PARALLEL_PROCESSES with the Assigned Process - Start: '
|| TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
UPDATE OKL_PARALLEL_PROCESSES
SET assigned_process = l_seq_next || '-' || l_assigned_process_tbl(upg_index)
,process_status = 'ASSIGNED'
WHERE object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE
AND object_value = l_object_value_tbl(upg_index)
AND process_status = 'PENDING_ASSIGNMENT'
AND khr_id = l_khr_id_tbl(upg_index);
log_msg(FND_FILE.LOG, 'Updated the Records in OKL_PARALLEL_PROCESSES with the Assigned Process - End : '
|| TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
DELETE OKL_PARALLEL_PROCESSES opp
WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE -- 'ESG_PRB_UPGRADE_CONTRACT'
AND opp.assigned_process = TO_CHAR(l_seq_next);