[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 Line Financial Report
|
| CALLED FROM
| Concurrent Program "Master Program -- Contract Line Financial Report"
|
| CALLS PROCEDURES/FUNCTIONS
|
| KNOWN ISSUES
|
| NOTES
|
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 30-Oct-2008 Durga Janaswamy Created
| 15-Dec-2008 Seema Chawla Added params p_template_code,p_report_language,p_report_format
*=======================================================================*/
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_TEMPLATE_CODE IN VARCHAR2, --sechawla 7628379
P_REPORT_LANGUAGE IN VARCHAR2,
P_REPORT_FORMAT IN VARCHAR2,
P_START_DATE_FROM IN VARCHAR2,
P_START_DATE_TO IN VARCHAR2,
P_BOOK_CLASS IN VARCHAR2,
P_LEASE_PRODUCT IN VARCHAR2,
P_CONTRACT_NUMBER IN VARCHAR2,
P_CONTRACT_STATUS IN VARCHAR2,
P_CONTRACT_LINE_STATUS IN VARCHAR2,
P_CONTRACT_LINE_TYPE IN VARCHAR2,
P_CUSTOMER_NAME IN VARCHAR2,
P_CUSTOMER_NUMBER IN VARCHAR2,
P_VENDOR_NAME IN VARCHAR2,
P_VENDOR_NUMBER IN VARCHAR2,
P_FA_INFO_YN IN VARCHAR2,
P_TAX_BOOK 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_LINE_EXT-CONTRACT'
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;
argument17 => P_DELETE_DATA_YN
);
DELETE OKL_PARALLEL_PROCESSES
WHERE assigned_process like l_char_seq_num||'%' ;
write_to_log('Deleted '||l_row_count||' rows from OKL_PARALLEL_PROCESSES.');
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,
object_status,
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_LINE_EXT-CONTRACT',
chr.contract_number,
OKL_K_LINE_FIN_EXT_PVT.Contract_Active_YN(khr.id, khr.DEAL_TYPE,chr.STS_CODE),
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 CHR.CONTRACT_NUMBER like NVL(P_CONTRACT_NUMBER, CHR.CONTRACT_NUMBER)
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_LINE_EXT-CONTRACT'
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');
argument17 => P_DELETE_DATA_YN,
argument18 => P_NUM_PROCESSES,
argument19 => 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.');