[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| upon a few primary input paramaters, inserts these contracts into a temp table and
| assigns a worker to each contract. It then spawns child request(s), based upon
| the parameter 'p_num_processes'. Once child requests complete, it launches
| request for Contract Financial Report
|
| CALLED FROM
| Concurrent Program "Master Program -- Contract Financial Report"
|
| CALLS PROCEDURES/FUNCTIONS
|
| KNOWN ISSUES
|
| NOTES
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 19-Sep-2008 SECHAWLA Created
*=======================================================================*/
PROCEDURE Process_Spawner (
errbuf OUT NOCOPY VARCHAR2,
retcode OUT NOCOPY NUMBER,
P_OPERATING_UNIT IN NUMBER,
P_REPORT_DATE IN VARCHAR2,
P_DATA_SOURCE_CODE IN VARCHAR2,
P_REPORT_TEMPLATE_NAME IN VARCHAR2,
P_REPORT_LANGUAGE IN VARCHAR2,
P_REPORT_FORMAT IN VARCHAR2,
P_START_DATE_FROM IN VARCHAR2,
P_START_DATE_TO IN VARCHAR2,
P_AR_INFO_YN IN VARCHAR2,
P_BOOK_CLASS IN VARCHAR2,
P_LEASE_PRODUCT IN VARCHAR2,
P_CONTRACT_STATUS IN VARCHAR2,
P_CUSTOMER_NUMBER IN VARCHAR2,
P_CUSTOMER_NAME IN VARCHAR2,
P_SIC_CODE IN VARCHAR2,
P_VENDOR_NUMBER IN VARCHAR2,
P_VENDOR_NAME IN VARCHAR2,
P_SALES_CHANNEL IN VARCHAR2,
P_GEN_ACCRUAL IN VARCHAR2,
P_END_DATE_FROM IN VARCHAR2,
P_END_DATE_TO IN VARCHAR2,
P_TERMINATE_DATE_FROM IN VARCHAR2,
P_TERMINATE_DATE_TO IN VARCHAR2,
P_DELETE_DATA_YN IN VARCHAR2,
p_num_processes IN NUMBER
) IS
CURSOR l_parallel_worker_csr(cp_assigned_process IN VARCHAR2) IS
SELECT object_value, khr_id, assigned_process
FROM OKL_PARALLEL_PROCESSES
WHERE object_type = 'CONTRACT_FIN_EXTRACT'
AND assigned_process = cp_assigned_process
AND process_status = 'PENDING_ASSIGNMENT';
SELECT count(*)
from fnd_concurrent_requests req,
fnd_concurrent_programs pgm
where req.PRIORITY_REQUEST_ID = p_request_id
and req.concurrent_program_id = pgm.concurrent_program_id
and req.PHASE_CODE = 'C'
and request_id <> p_request_id
and STATUS_CODE = 'E';
SELECT count(*)
from fnd_concurrent_requests req,
fnd_concurrent_programs pgm
where req.priority_request_id = p_request_id
and req.concurrent_program_id = pgm.concurrent_program_id
and req.phase_code = 'C'
and request_id <> p_request_id
and status_code = 'G';
l_last_updated_by okl_parallel_processes.last_updated_by%TYPE := Fnd_Global.USER_ID;
l_last_update_login okl_parallel_processes.last_update_login%TYPE := Fnd_Global.LOGIN_ID;
argument20 => P_DELETE_DATA_YN
);
write_to_log('P_DELETE_DATA_YN '||P_DELETE_DATA_YN);
select okl_opp_seq.nextval
into l_seq_next
from dual ;
INSERT INTO OKL_PARALLEL_PROCESSES
(
object_type, object_value, assigned_process, process_status, start_date, khr_id,
ORG_ID,CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,
REQUEST_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE
)
SELECT 'CONTRACT_FIN_EXTRACT', chr.contract_number, l_char_seq_num, 'PENDING_ASSIGNMENT', sysdate, chr.id ,
l_org_id,l_last_updated_by,sysdate,sysdate,l_last_updated_by,l_last_update_login,
l_request_id,l_program_id,sysdate
FROM okc_k_headers_all_b chr,
OKL_K_HEADERS khr
WHERE chr.id = khr.id
AND chr.SCS_CODE = 'LEASE'
AND chr.sts_code IN ('BANKRUPTCY_HOLD','ENTERED','BOOKED', 'COMPLETE', 'EVERGREEN', 'EXPIRED', 'INCOMPLETE',
'LITIGATION_HOLD', 'NEW', 'PASSED', 'REVERSED', 'TERMINATED') --sechawla 13-jan-09 7693771
AND chr.AUTHORING_ORG_ID = P_OPERATING_UNIT
AND (chr.start_date IS NOT NULL AND chr.START_DATE >= lp_k_start_date_from)
AND (chr.start_date IS NOT NULL AND chr.START_DATE <= lp_k_start_date_to)
AND nvl(khr.DEAL_TYPE,'XXX') = nvl(P_BOOK_CLASS, nvl(khr.DEAL_TYPE,'XXX'))
AND nvl(khr.pdt_id,-9999) = nvl(P_LEASE_PRODUCT,nvl(khr.pdt_id,-9999))
AND chr.sts_code = nvl(P_CONTRACT_STATUS, chr.sts_code)
AND ( (lp_k_end_date_from IS NULL) OR (chr.END_DATE IS NOT NULL AND chr.END_DATE >= lp_k_end_date_from) )
AND ( (lp_k_end_date_to IS NULL ) OR (chr.END_DATE IS NOT NULL AND chr.END_DATE <= lp_k_end_date_to) )
AND NOT EXISTS
(SELECT '1'
FROM OKL_PARALLEL_PROCESSES opp
WHERE chr.contract_number = opp.object_value
AND opp.object_type = 'CONTRACT_FIN_EXTRACT'
AND opp.process_status in ('PENDING_ASSIGNMENT', 'ASSIGNED'));
write_to_log('Number of rows inserted in OKL_PARALLEL_PROCESSES :'||l_row_count);
l_parallel_worker_tbl.DELETE;
l_parallel_worker_temp_tbl.DELETE;
UPDATE OKL_PARALLEL_PROCESSES
SET assigned_process = l_parallel_worker_tbl(k).assigned_process,
process_status = 'ASSIGNED'
WHERE object_Type = 'CONTRACT_FIN_EXTRACT'
AND object_value = l_parallel_worker_tbl(k).object_value
AND process_status = 'PENDING_ASSIGNMENT';
write_to_log('OKL_PARALLEL_PROCESSES Updated with worker assignment');
argument20 => P_DELETE_DATA_YN,
argument21 => p_num_processes,
argument22 => l_seq_next||'-'||j
);
DELETE OKL_PARALLEL_PROCESSES
WHERE assigned_process like l_char_seq_num||'%' ;
write_to_log('Deleted '||l_row_count||' rows from OKL_PARALLEL_PROCESSES.');
DELETE OKL_PARALLEL_PROCESSES
WHERE assigned_process like l_char_seq_num||'%' ;
write_to_log('Deleted '||sql%rowcount||' rows from OKL_PARALLEL_PROCESSES.');