1 PACKAGE BODY OKL_PRB_UPGRADE_PVT AS
2 /* $Header: OKLRPRBB.pls 120.6 2011/03/29 07:14:30 rgooty noship $ */
3
4 PROCEDURE log_msg(
5 p_destination IN NUMBER
6 ,p_msg IN VARCHAR2)
7 IS
8 BEGIN
9 FND_FILE.PUT_LINE(p_destination, p_msg );
10 END;
11
12 PROCEDURE log_n_print_msg(
13 p_msg IN VARCHAR2)
14 IS
15 BEGIN
16 FND_FILE.PUT_LINE(FND_FILE.LOG, p_msg );
17 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, p_msg );
18 END;
19
20 ------------------------------------------------------------------------------
21 -- Start of comments
22 -- API name : reamort_upgrade
23 -- Pre-reqs : None
24 -- Description : API to request Transaction State Upgrade of an ESG Lease Reamort Contract
25 -- Parameters :
26 -- IN :
27 -- Workers ID Mandatory
28 -- History : Ravindranath Gooty created
29 -- Version : 1.0
30 -- End of comments
31 ------------------------------------------------------------------------------
32 PROCEDURE reamort_upgrade(
33 errbuf OUT NOCOPY VARCHAR2
34 ,retcode OUT NOCOPY NUMBER
35 ,p_worker_id IN VARCHAR2
36 )
37 IS
38 BEGIN
39 log_msg(FND_FILE.LOG, 'reamort_upgrade: Begin (+)' );
40 log_msg(FND_FILE.LOG, 'reamort_upgrade: Before Calling API eff_dated_rbk_upgrade' );
41 eff_dated_rbk_upgrade(
42 errbuf => errbuf
43 ,retcode => retcode
44 ,p_worker_id => p_worker_id
45 );
46 log_msg(FND_FILE.LOG, 'reamort_upgrade: retcode = ' || retcode );
47 log_msg(FND_FILE.LOG, 'reamort_upgrade: End (-)' );
48 END reamort_upgrade;
49
50 ------------------------------------------------------------------------------
51 -- Start of comments
52 -- API name : reamort_upgrade_conc
53 -- Pre-reqs : None
54 -- Description : API to identify eligible contracts for ESG PRB Upgrade
55 -- based on the Criteria given and launch multiple workers
56 -- Parameters :
57 -- IN :
58 -- Operating Unit Mandatory
59 -- Criteria Set Mandatory [REAMORT]
60 -- Legal Entity Optional
61 -- Contract Number Optional
62 -- Product Optional
63 -- Start Date [Low] Optional
64 -- Start Date [High] Optional
65 -- End Date [Low] Optional
66 -- End Date [High] Optional
67 -- Mode Optional [REVIEW/SUBMIT]
68 -- Tag Name Optional
69 -- # of Workers Optional
70 -- History : Ravindranath Gooty created
71 -- Version : 1.0
72 -- End of comments
73 ------------------------------------------------------------------------------
74 PROCEDURE reamort_upgrade_conc(
75 errbuf OUT NOCOPY VARCHAR2
76 ,retcode OUT NOCOPY NUMBER
77 ,p_org_id IN NUMBER
78 ,p_criteria_set IN VARCHAR2
79 ,p_dummy_crit_set_contract IN VARCHAR2
80 ,p_le_id IN NUMBER
81 ,p_khr_id IN NUMBER
82 ,p_pdt_id IN NUMBER
83 ,p_start_date_low IN VARCHAR2
84 ,p_start_date_high IN VARCHAR2
85 ,p_end_date_low IN VARCHAR2
86 ,p_end_date_high IN VARCHAR2
87 ,p_mode_of_run IN VARCHAR2
88 ,p_tag_name IN VARCHAR2
89 ,p_no_of_workers IN NUMBER
90 )
91 IS
92 BEGIN
93 log_msg(FND_FILE.LOG, 'reamort_upgrade_conc: Begin (+)' );
94 log_msg(FND_FILE.LOG, 'reamort_upgrade_conc: Before Calling API eff_dated_rbk_upgrade_conc' );
95 eff_dated_rbk_upgrade_conc(
96 errbuf => errbuf
97 ,retcode => retcode
98 ,p_org_id => p_org_id
99 ,p_criteria_set => p_criteria_set -- 'REAMORT' is being passed
100 ,p_dummy_crit_set_contract => p_dummy_crit_set_contract
101 ,p_dummy_crit_set_revision => NULL
102 ,p_le_id => p_le_id
103 ,p_khr_id => p_khr_id
104 ,p_book_classification => NULL
105 ,p_pdt_id => p_pdt_id
106 ,p_int_calc_method => NULL
107 ,p_rev_rec_method => NULL
108 ,p_start_date_low => p_start_date_low
109 ,p_start_date_high => p_start_date_high
110 ,p_end_date_low => p_end_date_low
111 ,p_end_date_high => p_end_date_high
112 ,p_in_transit_category => NULL
113 ,p_mode_of_run => p_mode_of_run
114 ,p_tag_name => p_tag_name
115 ,p_no_of_workers => p_no_of_workers
116 );
117 log_msg(FND_FILE.LOG, 'reamort_upgrade_conc: retcode = ' || retcode );
118 log_msg(FND_FILE.LOG, 'reamort_upgrade_conc: End (-)' );
119 END;
120
121 ------------------------------------------------------------------------------
122 -- Start of comments
123 -- API name : eff_dated_rbk_upgrade
124 -- Pre-reqs : None
125 -- Description : API to request PRB Upgrade of an ESG Lease Contract
126 -- Parameters :
127 -- IN :
128 -- Workers ID Mandatory
129 -- History : Ravindranath Gooty created
130 -- Version : 1.0
131 -- End of comments
132 ------------------------------------------------------------------------------
133 PROCEDURE eff_dated_rbk_upgrade(
134 errbuf OUT NOCOPY VARCHAR2
135 ,retcode OUT NOCOPY NUMBER
136 ,p_worker_id IN VARCHAR2
137 )
138 IS
139 CURSOR get_esg_upgw_contracts_csr(
140 p_worker_id VARCHAR2
141 )
142 IS
143 SELECT opp.khr_id khr_id
144 ,opp.object_value contract_number
145 ,opp.volume no_of_assets
146 FROM okl_parallel_processes opp
147 WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE -- 'ESG_PRB_UPGRADE_CONTRACT'
148 AND opp.process_status = 'ASSIGNED' -- Dont fetch any unallocated contracts for processing
149 AND opp.assigned_process = p_worker_id; -- Fetch only this worker related contracts
150 TYPE esg_upg_cntrcts_tbl_type IS TABLE OF get_esg_upgw_contracts_csr%ROWTYPE
151 INDEX BY BINARY_INTEGER;
152 -- Local Variable Declaration
153 l_outer_error_msg_tbl Okl_Accounting_Util.Error_Message_Type;
154 l_esg_upg_cntrcts_tbl esg_upg_cntrcts_tbl_type;
155 l_khr_id NUMBER;
156 -- Common Local Variables
157 l_api_name CONSTANT VARCHAR2(30) := 'EFF_DATED_RBK_UPGRADE';
158 l_init_msg_list VARCHAR2(2000) := OKL_API.G_FALSE;
159 -- Local Variables specific to ESG request
160 l_request_id NUMBER;
161 l_trans_status VARCHAR2(100);
162 l_rep_request_id NUMBER;
163 l_rep_trans_status VARCHAR2(100);
164 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
165 l_msg_count NUMBER;
166 l_msg_data VARCHAR2(2000);
167 l_api_version CONSTANT NUMBER := 1.0;
168
169 --Added by bkatraga for bug 10649684
170 x_msg_count NUMBER;
171 x_msg_data VARCHAR2(2000);
172 l_msg_index_out NUMBER;
173 --end bkatraga
174
175 l_khr_id_tbl Okl_Streams_Util.NumberTabTyp;
176 khr_index NUMBER;
177 l_text VARCHAR2(4000);
178 l_time_taken NUMBER;
179 l_start_mark DATE;
180 BEGIN
181 -- Assign the input params to the Local Variables
182 log_msg(FND_FILE.LOG, 'Parameters: ' );
183 log_msg(FND_FILE.LOG, ' Worker ID = ' || p_worker_id );
184 -- Initialize the khr_index
185 khr_index := 0;
186 -- Fetch all the OKL Assets for which FA has generated Depreciation Transactions
187 -- in the inputted Asset Book and Period
188 log_msg(FND_FILE.LOG, 'Before Executing the Cursor get_esg_upgw_contracts_csr' );
189 OPEN get_esg_upgw_contracts_csr( p_worker_id => p_worker_id );
190 LOOP
191 log_msg(FND_FILE.LOG, 'After Executing the Cursor get_esg_upgw_contracts_csr-Start: '
192 || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
193 FETCH get_esg_upgw_contracts_csr BULK COLLECT INTO l_esg_upg_cntrcts_tbl
194 LIMIT 10000;
195 log_msg(FND_FILE.LOG, 'After Executing the Cursor get_esg_upgw_contracts_csr-End : '
196 || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
197 -- Exit when there are no Assets to be Processed
198 EXIT WHEN get_esg_upgw_contracts_csr%ROWCOUNT = 0;
199 log_msg(FND_FILE.LOG, 'Number of Contracts to be Processed: ' || l_esg_upg_cntrcts_tbl.COUNT );
200 IF l_esg_upg_cntrcts_tbl.COUNT > 0
201 THEN
202 log_msg(FND_FILE.OUTPUT, '------------------------------------------------------------------------------------------------------------------------------------' );
203 log_msg(FND_FILE.OUTPUT, 'Contract # Primary Secondary Time Error ');
204 log_msg(FND_FILE.OUTPUT, ' Number Trx Number Trx. Status Trx. Number Trx. Status Taken(Sec) Message ' );
205 log_msg(FND_FILE.OUTPUT, '------------------------------------------------------------------------------------------------------------------------------------' );
206 FOR i IN l_esg_upg_cntrcts_tbl.FIRST .. l_esg_upg_cntrcts_tbl.LAST
207 LOOP
208 -- Logic:
209 -- Frame the appropriate Parameters and call the Granular API
210 -- to request Stream Generation for the eligible Contract
211 -- Increment the khr_index and store the contract number in the l_khr_id_tbl
212 l_khr_id_tbl(khr_index) := l_esg_upg_cntrcts_tbl(i).khr_id;
213 khr_index := khr_index + 1;
214 -- Re-Initialize things
215 l_return_status := NULL;
216 l_msg_count := NULL;
217 l_msg_data := NULL;
218 l_request_id := NULL;
219 l_trans_status := NULL;
220 l_rep_request_id := NULL;
221 l_rep_trans_status := NULL;
222 log_msg(FND_FILE.LOG, 'ESG for ' || l_esg_upg_cntrcts_tbl(i).contract_number ||
223 ' - Start: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
224 l_start_mark := SYSDATE;
225 l_time_taken := NULL;
226 l_text := NULL;
227
228 -- establish the external_id values for the contracts, if they don't have one.
229 OKL_LLA_UTIL_PVT.update_external_id(p_chr_id => l_esg_upg_cntrcts_tbl(i).khr_id,
230 x_return_status => l_return_status);
231
232 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS
233 THEN
234 log_msg(FND_FILE.LOG, 'Error: ' || SUBSTR(l_msg_data, 1, 2000) );
235 END IF;
236
237 okl_la_stream_pvt.upgrade_esg_khr_for_prb(
238 p_chr_id => l_esg_upg_cntrcts_tbl(i).khr_id
239 ,x_return_status => l_return_status
240 ,x_msg_count => l_msg_count
241 ,x_msg_data => l_msg_data
242 ,x_request_id => l_request_id
243 ,x_trans_status => l_trans_status
244 ,x_rep_request_id => l_rep_request_id
245 ,x_rep_trans_status => l_rep_trans_status );
246 l_time_taken := ( SYSDATE - l_start_mark ) * 86400; -- To convert in seconds
247 log_msg(FND_FILE.LOG,'Return Status: ' || l_return_status );
248 l_text := SUBSTR(
249 RPAD(l_esg_upg_cntrcts_tbl(i).contract_number, 35, ' ' ) ||
250 LPAD(l_request_id, 10, ' ' ) || ' ' ||
251 RPAD(l_trans_status, 30, ' ' ) || ' ' ||
252 LPAD(l_rep_request_id, 10, ' ' ) || ' ' ||
253 RPAD(l_rep_trans_status, 30, ' ' ) || ' ' ||
254 LPAD(l_time_taken, 10, ' ') || ' ' ||
255 SUBSTR(l_msg_data, 1, 2000)
256 , 1, 4000);
257 log_msg(FND_FILE.OUTPUT, l_text );
258 IF l_return_status <> OKL_API.G_RET_STS_SUCCESS
259 THEN
260 --Added by bkatraga for bug 10649684
261 x_msg_count := fnd_msg_pub.count_msg;
262 IF(x_msg_count > 0) THEN
263 FOR i in 1..x_msg_count
264 LOOP
265 FND_MSG_PUB.GET(
266 p_msg_index => i,
267 p_encoded => FND_API.G_FALSE,
268 p_data => x_msg_data,
269 p_msg_index_out => l_msg_index_out
270 );
271 log_msg (FND_FILE.LOG,'Error '||to_char(i)||': '||x_msg_data);
272 END LOOP;
273 fnd_msg_pub.delete_msg();
274 END IF;
275 --end bkatraga for bug 10649684
276 END IF;
277 --Added by bkatraga for bug 10649684
278 log_msg(FND_FILE.LOG, 'ESG for ' || l_esg_upg_cntrcts_tbl(i).contract_number ||
279 ' - End : ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
280 END LOOP; -- FOR i IN l_deprn_assets_tbl.FIRST .. l_deprn_assets_tbl.LAST
281 END IF; -- IF l_esg_upg_cntrcts_tbl.COUNT > 0
282 -- Exit When Cursor Has been Exhausted fetching all the Records
283 EXIT WHEN get_esg_upgw_contracts_csr%NOTFOUND;
284 END LOOP; -- Loop on get_esg_upgw_contracts_csr
285 CLOSE get_esg_upgw_contracts_csr; -- Close the Cursor
286 -- Now Delete all the processed records from parallel process table
287 log_msg(FND_FILE.LOG, 'Deletion of Processed Records - Start: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
288 FORALL khr_index IN l_khr_id_tbl.FIRST .. l_khr_id_tbl.LAST
289 DELETE OKL_PARALLEL_PROCESSES opp
290 WHERE khr_id = l_khr_id_tbl(khr_index)
291 AND opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE -- 'ESG_PRB_UPGRADE_CONTRACT'
292 AND opp.assigned_process = p_worker_id; -- Fetch only this worker related contracts;
293 log_msg(FND_FILE.LOG, 'Deletion of Processed Records - End : ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
294 -- Return the Proper Return status
295 retcode := 0; -- 0 Indicates 'S'uccess Status
296 EXCEPTION
297 ------------------------------------------------------------
298 -- Exception handling
299 ------------------------------------------------------------
300 WHEN Okl_Api.G_EXCEPTION_ERROR
301 THEN
302 l_return_status := Okl_Api.G_RET_STS_ERROR;
303 -- print the error message in the log file and output files
304 log_msg(FND_FILE.OUTPUT,'');
305 log_msg(FND_FILE.OUTPUT,FND_MESSAGE.GET_STRING('OKL','OKL_AGN_RPT_PROGRAM_ERROR'));
306 log_msg(FND_FILE.LOG,FND_MESSAGE.GET_STRING('OKL','OKL_AGN_RPT_PROGRAM_STATUS')
307 ||' '||l_return_status);
308 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_outer_error_msg_tbl);
309 IF (l_outer_error_msg_tbl.COUNT > 0) THEN
310 FOR i IN l_outer_error_msg_tbl.FIRST..l_outer_error_msg_tbl.LAST
311 LOOP
312 log_msg(FND_FILE.LOG, l_outer_error_msg_tbl(i));
313 END LOOP;
314 END IF;
315 retcode := 2;
316
317 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR
318 THEN
319 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
320 -- print the error message in the log file
321 log_msg(FND_FILE.OUTPUT,'');
322 log_msg(FND_FILE.OUTPUT,FND_MESSAGE.GET_STRING('OKL','OKL_AGN_RPT_PROGRAM_ERROR'));
323 log_msg(FND_FILE.LOG,FND_MESSAGE.GET_STRING('OKL','OKL_AGN_RPT_PROGRAM_STATUS')
324 ||' '||l_return_status);
325 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_outer_error_msg_tbl);
326 IF (l_outer_error_msg_tbl.COUNT > 0)
327 THEN
328 FOR i IN l_outer_error_msg_tbl.FIRST..l_outer_error_msg_tbl.LAST
329 LOOP
330 log_msg(FND_FILE.LOG, l_outer_error_msg_tbl(i));
331 END LOOP;
332 END IF;
333 retcode := 2;
334
335 WHEN OTHERS
336 THEN
337 l_return_status := Okl_Api.G_RET_STS_UNEXP_ERROR;
338 -- print the error message in the log file
339 log_msg(FND_FILE.OUTPUT,'');
340 log_msg(FND_FILE.OUTPUT,FND_MESSAGE.GET_STRING('OKL','OKL_AGN_RPT_PROGRAM_ERROR'));
341 log_msg(FND_FILE.LOG,FND_MESSAGE.GET_STRING('OKL','OKL_AGN_RPT_PROGRAM_STATUS')
342 ||' '||l_return_status);
343 Okl_Accounting_Util.GET_ERROR_MESSAGE(l_outer_error_msg_tbl);
344 IF (l_outer_error_msg_tbl.COUNT > 0)
345 THEN
346 FOR i IN l_outer_error_msg_tbl.FIRST..l_outer_error_msg_tbl.LAST
347 LOOP
348 log_msg(FND_FILE.LOG, l_outer_error_msg_tbl(i));
349 END LOOP;
350 END IF;
351 errbuf := SQLERRM;
352 retcode := 2;
353 END eff_dated_rbk_upgrade;
354 ------------------------------------------------------------------------------
355 -- Start of comments
356 -- API name : eff_dated_rbk_upgrade_conc
357 -- Pre-reqs : None
358 -- Description : API to identify eligible contracts for ESG PRB Upgrade
359 -- based on the Criteria given and launch multiple workers
360 -- Parameters :
361 -- IN :
362 -- Operating Unit Mandatory
363 -- Criteria Set Mandatory [CONTRACT/REVISION]
364 -- Legal Entity Optional
365 -- Contract Number Optional
366 -- Book Classification Optional
367 -- Product Optional
368 -- Interest Calculation Method Optional
369 -- Revenue Recognition Method Optional
370 -- Start Date [Low] Optional
371 -- Start Date [High] Optional
372 -- End Date [Low] Optional
373 -- End Date [High] Optional
374 -- In-Transit Category Optional
375 -- Mode Optional [REVIEW/SUBMIT]
376 -- Tag Name Optional
377 -- # of Workers Optional
378 -- History : Ravindranath Gooty created
379 -- Version : 1.0
380 -- End of comments
381 ------------------------------------------------------------------------------
382 PROCEDURE eff_dated_rbk_upgrade_conc(
383 errbuf OUT NOCOPY VARCHAR2
384 ,retcode OUT NOCOPY NUMBER
385 ,p_org_id IN NUMBER
386 ,p_criteria_set IN VARCHAR2
387 ,p_dummy_crit_set_contract IN VARCHAR2
388 ,p_dummy_crit_set_revision IN VARCHAR2
389 ,p_le_id IN NUMBER
390 ,p_khr_id IN NUMBER
391 ,p_book_classification IN VARCHAR2
392 ,p_pdt_id IN NUMBER
393 ,p_int_calc_method IN VARCHAR2
394 ,p_rev_rec_method IN VARCHAR2
395 ,p_start_date_low IN VARCHAR2
396 ,p_start_date_high IN VARCHAR2
397 ,p_end_date_low IN VARCHAR2
398 ,p_end_date_high IN VARCHAR2
399 ,p_in_transit_category IN VARCHAR2
400 ,p_mode_of_run IN VARCHAR2
401 ,p_tag_name IN VARCHAR2
402 ,p_no_of_workers IN NUMBER
403 )
404 IS
405 -- Cursor Definitions
406 -- Cursor: To fetch the eligible Contracts for Processing the ESG Upgrade
407 CURSOR get_esg_upg_contracts_csr( p_process_sequence IN VARCHAR2 )
408 IS
409 SELECT khr_id khr_id
410 ,object_value contract_number
411 ,volume no_of_assets
412 ,process_status status
413 ,'Pending Assignment' status_meaning
414 FROM OKL_PARALLEL_PROCESSES opp
415 WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE -- 'ESG_PRB_UPGRADE_CONTRACT'
416 AND opp.process_status = 'PENDING_ASSIGNMENT'
417 AND opp.assigned_process = p_process_sequence
418 UNION ALL
419 SELECT khr_id khr_id
420 ,object_value contract_number
421 ,volume no_of_assets
422 ,process_status status
423 ,'Revision in Progress' status_meaning
424 FROM OKL_PARALLEL_PROCESSES opp
425 --,fnd_lookups lkup
426 WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE -- 'ESG_PRB_UPGRADE_CONTRACT'
427 AND opp.process_status <> 'PENDING_ASSIGNMENT'
428 AND opp.assigned_process = p_process_sequence;
429 --AND lkup.lookup_type = 'OKL_UPG_INTRANSIT_CAT'
430 --AND opp.process_status = lkup.lookup_code;
431
432 TYPE esg_upg_contracts_tbl_type IS TABLE OF get_esg_upg_contracts_csr%ROWTYPE
433 INDEX BY BINARY_INTEGER;
434
435 -- Cursor to get the meaning of the Parameters appropriately
436 CURSOR get_params_def_csr
437 IS
438 SELECT
439 (SELECT name from hr_operating_units where organization_id = p_org_id) org_id
440 ,DECODE(p_criteria_set, 'CONTRACT', 'Contract - Criteria', 'REAMORT', 'Reamort', 'Revision - Criteria' ) criteria_set
441 ,( SELECT DISTINCT legal_entity_name from XLE_LE_OU_LEDGER_V
442 WHERE legal_entity_id = p_le_id
443 AND rownum <= 1 ) le_id
444 ,( SELECT contract_number FROM OKC_K_HEADERS_B WHERE id = p_khr_id ) khr_id
445 ,OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_BOOK_CLASS',p_book_classification) book_classification
446 ,( SELECT name from okl_products where id = p_pdt_id ) pdt_id
447 ,OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_INTEREST_CALCULATION_BASIS',p_int_calc_method) int_calc_method
448 ,OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING('OKL_REVENUE_RECOGNITION_METHOD',p_rev_rec_method) rev_rec_method
449 ,p_start_date_low start_date_low
450 ,p_start_date_high start_date_high
451 ,p_end_date_low end_date_low
452 ,p_end_date_high end_date_high
453 ,OKL_ACCOUNTING_UTIL.GET_LOOKUP_MEANING( 'OKL_UPG_INTRANSIT_CAT',p_in_transit_category) in_transit_category
454 ,DECODE(p_mode_of_run, 'REVIEW', 'Review', 'SUBMIT', 'Submit' ) mode_of_run
455 ,p_tag_name tag_name
456 ,p_no_of_workers no_of_workers
457 FROM DUAL;
458
459 -- Cursor to fetch the contracts that were earlier upgraded with a failure.
460 CURSOR get_khr_errored_during_upgrade
461 IS
462 SELECT
463 chr.contract_number contract_number
464 ,lkup.meaning pricing_status
465 ,sif.sis_code pricing_code
466 ,sif.orp_code pricing_orp_code
467 ,DECODE( sif.sis_code,
468 'PROCESSING_FAILED', 1,
469 'PROCESS_ABORTED', 2,
470 'TIME_OUT', 3,
471 'SERVER_NA', 4, 100) pricing_error_level
472 FROM okc_k_headers_b chr -- Org Specific
473 ,okl_k_headers khr
474 ,okl_products pdt
475 ,okl_ae_tmpt_sets_all aes
476 ,okl_st_gen_tmpt_sets_all gts
477 ,okl_stream_interfaces sif
478 ,fnd_lookups lkup
479 WHERE chr.id = khr.id
480 AND chr.scs_code = 'LEASE'
481 AND chr.template_yn = 'N'
482 AND chr.orig_system_source_code <> 'OKL_REBOOK'
483 AND khr.pdt_id = pdt.id
484 AND pdt.aes_id = aes.id
485 AND aes.gts_id = gts.id
486 AND gts.pricing_engine = 'EXTERNAL'
487 AND sif.khr_id = chr.id
488 AND lkup.lookup_type = 'OKL_SIF_STATUS'
489 AND sif.sis_code = lkup.lookup_code
490 AND sif.orp_code = 'UPGRADE'
491 AND sif.sis_code IN ( 'PROCESSING_FAILED', 'PROCESS_ABORTED', 'TIME_OUT', 'SERVER_NA')
492 -- And make sure that the same contract was not upgraded successfully later on
493 AND NOT EXISTS
494 (
495 SELECT 1
496 FROM okl_stream_trx_data trx,
497 okl_stream_interfaces osi
498 WHERE osi.transaction_number = trx.transaction_number
499 AND osi.khr_id = chr.id
500 AND trx.last_trx_state = 'Y'
501 )
502 order by 5;
503
504 l_temp_upg_contracts_tbl esg_upg_contracts_tbl_type;
505 l_upg_contracts_tbl esg_upg_contracts_tbl_type;
506 l_non_upg_contracts_tbl esg_upg_contracts_tbl_type;
507 upg_index NUMBER := 0; -- Index for the l_upg_contracts_tbl
508 non_upg_index NUMBER := 0;
509 l_err_upg_index NUMBER := 0;
510 -- Variable Declarations
511 l_object_value_tbl Okl_Streams_Util.Var240TabTyp;
512 l_assigned_process_tbl Okl_Streams_Util.Var30TabTyp;
513 l_khr_id_tbl Okl_Streams_Util.NumberTabTyp;
514 l_volume_tbl Okl_Streams_Util.NumberTabTyp;
515 -- Local Variable Declaration
516 req_data VARCHAR2(10);
517 l_num_workers NUMBER;
518 l_seq_next NUMBER;
519 l_worker_id VARCHAR2(2000);
520 l_worker_load worker_load_tab;
521 i NUMBER;
522 l_lightest_worker NUMBER;
523 l_lightest_load NUMBER;
524 l_reqid FND_CONCURRENT_REQUESTS.request_id%TYPE;
525 l_query_string VARCHAR2(4000);
526 G_LIMIT_SIZE CONSTANT NUMBER := 10000;
527 -- Date related variables
528 l_start_date_low DATE;
529 l_start_date_high DATE;
530 l_end_date_low DATE;
531 l_end_date_high DATE;
532
533 l_prb_enabled VARCHAR2(1);
534 l_k_status VARCHAR2(5000);
535
536 BEGIN
537 req_data := fnd_conc_global.request_data;
538 log_msg(FND_FILE.LOG, 'Request Data= ' || req_data );
539 IF req_data IS NOT NULL
540 THEN
541 --errbuf:='Done';
542 retcode := 0;
543 log_msg(FND_FILE.LOG, 'Returning Out Successfully !' );
544 RETURN;
545 ELSE
546 log_msg(FND_FILE.LOG, 'MOAC Org Context : ' || mo_global.get_current_org_id );
547 l_start_date_low := FND_DATE.CANONICAL_TO_DATE( p_start_date_low );
548 l_start_date_high := FND_DATE.CANONICAL_TO_DATE( p_start_date_high );
549 l_end_date_low := FND_DATE.CANONICAL_TO_DATE( p_end_date_low );
550 l_end_date_high := FND_DATE.CANONICAL_TO_DATE( p_end_date_high );
551
552 FOR t_rec IN get_params_def_csr
553 LOOP
554 -- Formatting output specific to the Reamort Upgrade Run
555 IF p_criteria_set = 'REAMORT'
556 THEN
557 log_n_print_msg( ' Upgrade Reamortization Lease Pricing History Report : ' || t_rec.mode_of_run);
558 log_n_print_msg( ' ------------------------------------------------------------ ' );
559 log_n_print_msg( ' ' );
560 log_n_print_msg( ' ' );
561 log_n_print_msg( ' Request ID : ' || Fnd_Global.CONC_REQUEST_ID );
562 log_n_print_msg( ' Date : ' || FND_DATE.DATE_TO_CANONICAL(SYSDATE));
563 log_n_print_msg( ' ' );
564 log_n_print_msg( ' ' );
565 log_n_print_msg( ' ' );
566 END IF;
567 -- When the req_data is NULL, it means that this is the first run of the Program ..
568 -- in the Sense, the current request is the run before triggerring off any parallel workers
569 -- Log the Input Variables
570 log_n_print_msg( 'Parameters: ' );
571 log_n_print_msg( ' Operating Unit : ' || t_rec.org_id );
572 IF p_criteria_set <> 'REAMORT'
573 THEN
574 log_n_print_msg( ' Criteria Set : ' || t_rec.criteria_set );
575 END IF;
576 log_n_print_msg( ' Legal Entity : ' || t_rec.le_id );
577 log_n_print_msg( ' Contract Number : ' || t_rec.khr_id );
578 IF p_criteria_set <> 'REAMORT'
579 THEN
580 log_n_print_msg( ' Book Classification : ' || t_rec.book_classification);
581 END IF;
582 log_n_print_msg( ' Product : ' || t_rec.pdt_id );
583 IF p_criteria_set <> 'REAMORT'
584 THEN
585 log_n_print_msg( ' Interest Calculation Method : ' || t_rec.int_calc_method );
586 log_n_print_msg( ' Revenue Recognition Method : ' || t_rec.rev_rec_method );
587 END IF;
588 log_n_print_msg( ' Start Date [Low] : ' || t_rec.start_date_low );
589 log_n_print_msg( ' Start Date [High] : ' || t_rec.start_date_high);
590 log_n_print_msg( ' End Date [Low] : ' || t_rec.end_date_low );
591 log_n_print_msg( ' End Date [High] : ' || t_rec.end_date_high );
592 IF p_criteria_set <> 'REAMORT'
593 THEN
594 log_n_print_msg( ' In-Transit Category : ' || t_rec.in_transit_category);
595 END IF;
596 log_n_print_msg( ' Mode : ' || t_rec.mode_of_run );
597 log_n_print_msg( ' Tag Name : ' || t_rec.tag_name );
598 log_n_print_msg( ' # of Workers : ' || t_rec.no_of_workers );
599 log_n_print_msg( ' ');
600 log_n_print_msg( ' ');
601 END LOOP;
602
603 IF p_criteria_set = 'CONTRACT'
604 THEN
605 -- Validations
606 -- Check if the effective dated rebook feature is enabled or not
607 -- Bugs 8928055, 8927961
608 SELECT NVL(AMORT_INC_ADJ_REV_DT_YN, 'N')
609 INTO l_prb_enabled
610 FROM okl_sys_acct_opts_all
611 WHERE org_id = p_org_id;
612 IF l_prb_enabled = 'N'
613 THEN
614 log_msg(FND_FILE.LOG, FND_MESSAGE.GET_STRING('OKL', 'OKL_PRB_UPG_NOT_VALID'));
615 RETURN;
616 END IF;
617 --
618 END IF; -- IF p_criteria_set = 'CONTRACT'
619
620 IF p_mode_of_run = 'SUBMIT'
621 THEN
622 -- Fetch the Number of Workers to be Assigned
623 l_num_workers := p_no_of_workers; -- FND_PROFILE.VALUE(G_OKL_DEPRN_WORKERS);
624 log_msg(FND_FILE.LOG, 'Number of Workers ' || TO_CHAR(l_num_workers) );
625 IF l_num_workers IS NULL OR l_num_workers <= 0
626 THEN
627 log_msg(FND_FILE.LOG, 'Please specify positive value for the Parameter "Number of Workers".');
628 RAISE OKL_API.G_EXCEPTION_ERROR;
629 END IF;
630 END IF; -- IF p_mode_of_run = 'SUBMIT'
631 IF p_criteria_set = 'REVISION'
632 THEN
633 IF p_in_transit_category IS NULL
634 THEN
635 log_msg(FND_FILE.LOG, 'Please select a valid In-Trasit Category.');
636 RAISE OKL_API.G_EXCEPTION_ERROR;
637 END IF;
638 END IF;
639 -- Select sequence for marking processes
640 SELECT okl_opp_seq.NEXTVAL
641 INTO l_seq_next
642 FROM DUAL;
643 log_msg(FND_FILE.LOG, 'Process Sequence ID: ' || l_seq_next );
644 -- Fetch all Contracts eligible for Upgrade and Store them in OKL_PARALLEL_PROCESSES
645 log_msg(FND_FILE.LOG, 'Before calling the Bulk Insert into the OKL_PARALLEL_PROCESSES' );
646 l_query_string :=
647 'INSERT INTO OKL_PARALLEL_PROCESSES(OBJECT_TYPE,OBJECT_VALUE,ASSIGNED_PROCESS' ||
648 ',PROCESS_STATUS,CREATION_DATE,KHR_ID,VOLUME) ' ||
649 'SELECT ''' || G_ESG_PRB_KHR_UPG_OBJ_TYPE || ''' ' || -- OBJECT_TYPE
650 ' ,chr.contract_number' || -- OBJECT_VALUE
651 ' ,TO_CHAR( ''' || l_seq_next || ''' ) ' || -- ASSIGNED_PROCESS
652 ' ,''PENDING_ASSIGNMENT'' ' || -- PROCESS_STATUS
653 ' ,SYSDATE ' || -- CREATION_DATE
654 ' ,chr.id ' || -- KHR_ID
655 ' ,COUNT(cle.id) ' || -- VOLUME = Number of Assets
656 ' FROM okc_k_headers_b chr, okl_k_headers khr, okc_k_lines_b cle ' ||
657 ' ,okl_products pdt ,okl_ae_tmpt_sets aes , okl_st_gen_tmpt_sets gts ' ;
658 -- Appending the Where Caluse including the Mandatory Predicates
659 l_query_string := l_query_string
660 || ' WHERE chr.id = khr.id AND chr.id = cle.dnz_chr_id AND cle.lse_id = 33 ' -- FREE_FORM1 for Assets
661 || ' AND chr.scs_code = ''LEASE'' AND chr.template_yn = ''N'' AND chr.orig_system_source_code <> ''OKL_REBOOK'' '
662 || ' AND khr.pdt_id = pdt.id AND pdt.aes_id = aes.id AND aes.gts_id = gts.id AND gts.pricing_engine = ''EXTERNAL'' '
663 -- Operating Unit related predicate
664 || ' AND chr.authoring_org_id = ' || p_org_id || ' ';
665 -- Predicate to check whether an Upgrade ESG Transaction which is completed/in process exists for this contracts or not
666 -- Predicate to check whether this contract has the PRM content already or not
667 IF p_criteria_set IN ( 'CONTRACT' )
668 THEN
669 l_query_string := l_query_string
670 || ' AND NOT EXISTS ( SELECT 1 FROM okl_stream_trx_data trx, okl_stream_interfaces osi '
671 || ' WHERE osi.transaction_number = trx.transaction_number AND osi.khr_id = chr.id '
672 || ' AND ( ( trx.last_trx_state = ''Y'' AND '
673 || ' ((osi.orp_code = ''UPGRADE'' AND osi.sis_code = ''PROCESS_COMPLETE'') OR '
674 || ' (osi.orp_code = ''AUTH'' AND osi.sis_code = ''PROCESS_COMPLETE'')) ) OR '
675 || ' (osi.orp_code = ''UPGRADE'' AND osi.sis_code IN (''PROCESSING_REQUEST'', ''RET_DATA_RECEIVED'' )) )) ';
676 END IF;
677
678
679 IF p_criteria_set IN ( 'CONTRACT', 'REAMORT' )
680 THEN
681 -- Append the another default predicate
682 IF p_criteria_set = 'CONTRACT'
683 THEN
684 l_query_string := l_query_string
685 || ' AND chr.sts_code IN (' || ' ''COMPLETE'', ''BOOKED'', ''APPROVED'', ''EVERGREEN'' ' || ') ' ;
686 END IF;
687 IF p_le_id IS NOT NULL
688 THEN
689 l_query_string := l_query_string || ' AND khr.legal_entity_id = ' || p_le_id || ' ';
690 END IF;
691 IF p_khr_id IS NOT NULL
692 THEN
693 l_query_string := l_query_string || ' AND chr.id = ' || p_khr_id || ' ';
694 END IF;
695 log_msg(FND_FILE.LOG,'Handling the Date Criteria: Start ');
696 IF p_start_date_low IS NOT NULL THEN
697 l_query_string := l_query_string
698 || ' AND chr.start_date >= FND_DATE.CANONICAL_TO_DATE(''' ||p_start_date_low || ''') ';
699 END IF;
700 IF p_start_date_high IS NOT NULL THEN
701 l_query_string := l_query_string
702 || ' AND chr.start_date <= FND_DATE.CANONICAL_TO_DATE(''' ||p_start_date_high || ''') ';
703 END IF;
704 IF p_end_date_low IS NOT NULL THEN
705 l_query_string := l_query_string
706 || ' AND chr.end_date >= FND_DATE.CANONICAL_TO_DATE(''' ||p_end_date_low || ''') ';
707 END IF;
708 IF p_end_date_high IS NOT NULL THEN
709 l_query_string := l_query_string
710 || ' AND chr.end_date <= FND_DATE.CANONICAL_TO_DATE(''' ||p_end_date_high || ''') ';
711 END IF;
712 log_msg(FND_FILE.LOG,'Handling the Date Criteria: End ');
713 IF p_pdt_id IS NOT NULL
714 THEN
715 l_query_string := l_query_string || ' AND pdt.id = ' || p_pdt_id || ' ';
716 END IF;
717 IF p_book_classification IS NOT NULL THEN
718 l_query_string := l_query_string || ' AND khr.deal_type = ''' || p_book_classification || ''' ';
719 END IF;
720 IF p_int_calc_method IS NOT NULL THEN
721 l_query_string := l_query_string || ' AND gts.interest_calc_meth_code = ''' || p_int_calc_method || ''' ';
722 END IF;
723 IF p_rev_rec_method IS NOT NULL THEN
724 l_query_string := l_query_string || ' AND gts.revenue_recog_meth_code = ''' || p_rev_rec_method || ''' ';
725 END IF;
726 END IF; -- IF p_criteria_set = 'CONTRACT'
727
728 IF p_criteria_set = 'REAMORT'
729 THEN
730 -- 1. Leases with Interest Calculation Method Reamortization
731 l_query_string := l_query_string || 'AND gts.deal_type LIKE''LEASE%'' ';
732 l_query_string := l_query_string || 'AND gts.interest_calc_meth_code = ''REAMORT'' ';
733 END IF;
734
735
736 IF p_criteria_set = 'REVISION'
737 THEN
738 -- Start the Contract ID Not in Predicate
739 l_query_string := l_query_string || ' AND chr.id IN ( ';
740 IF p_in_transit_category = 'ONLINE_RBK'
741 THEN
742 -- Online Rebook not Activated
743 l_query_string := l_query_string || ' SELECT trx.khr_id orig_contract_id FROM okl_trx_contracts trx '
744 || ' WHERE trx.khr_id_new IS NOT NULL AND trx.tsu_code = ''ENTERED'' AND trx.rbr_code is NOT NULL '
745 || ' AND trx.tcn_type = ''TRBK'' AND trx.representation_type = ''PRIMARY'' ';
746 ELSIF p_in_transit_category = 'ONLINE_MASS_RBK'
747 THEN
748 -- Online Mass Rebook Not Processed
749 l_query_string := l_query_string || ' SELECT rsc.khr_id orig_contract_id FROM okl_rbk_selected_contract rsc, okc_k_headers_b chrb '
750 || ' WHERE rsc.transaction_id IS NULL AND rsc.status <> ''PROCESSED'' AND chrb.id = rsc.khr_id ';
751 ELSIF p_in_transit_category = 'PAYDOWN'
752 THEN
753 -- Paydown Not Accepted
754 l_query_string := l_query_string || ' SELECT trq.dnz_khr_id khr_id FROM okl_trx_requests trq '
755 || ' WHERE trq.request_type_code = ''PRINCIPAL_PAYDOWN'' AND trq.request_status_code NOT IN '
756 || ' (''ACCEPTED'', ''REJECTED'', ''ERROR'' ,''PROCESSED'' '
757 || ' ,''CANCELLED'' ,''REBOOK_IN_PROCESS'' ,''REBOOK_COMPLETE'' ) '
758 || ' AND trq.tcn_id IS NULL AND trq.org_id = ' || p_org_id;
759 ELSIF p_in_transit_category = 'RESIDUAL'
760 THEN
761 -- Residual Value Writedown not Processed
762 l_query_string := l_query_string || ' SELECT l.dnz_khr_id FROM OKL_TRX_ASSETS h, okl_txl_assets_b l '
763 || ' WHERE h.id = l.tas_id AND h.tsu_code IN (''ENTERED'',''ERROR'') AND h.tas_type = ''ARC'' ';
764 ELSIF p_in_transit_category = 'TERMINATION'
765 THEN
766 -- Termination Quote Not Accepted
767 l_query_string := l_query_string || ' SELECT khr_id FROM okl_trx_quotes_b '
768 || ' WHERE partial_yn = ''Y'' and qst_code not IN (''ACCEPTED'',''COMPLETE'',''IN_PROCESS'') ';
769 END IF; -- IF p_in_transit_category
770 l_query_string := l_query_string || ' ) ';
771 END IF; -- IF p_criteria_set = 'REVISION'
772
773 -- Group By Clause: Addition
774 l_query_string := l_query_string || ' GROUP BY chr.contract_number, chr.id ';
775
776 log_msg(FND_FILE.LOG, 'Query has been formulated: Start' );
777 log_msg(FND_FILE.LOG, l_query_string );
778 log_msg(FND_FILE.LOG, 'Executing the Formulated Query - Start: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
779 EXECUTE IMMEDIATE l_query_string;
780 log_msg(FND_FILE.LOG, 'Executing the Formulated Query - End: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
781 -- Commit the Records
782 COMMIT;
783 log_msg(FND_FILE.LOG, 'Committed the Insertion of the OKL_PARALLEL_PROCESSES Records' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
784
785
786 -- For Reamort Upgrade, perform the following checks
787 IF p_criteria_set IN ( 'REAMORT' )
788 THEN
789 -- Case 1: Contract status should be Booked/Complete/Approved
790 log_msg(FND_FILE.LOG, 'Checking: Contract status should be Booked/Complete/Approved - Start: '
791 || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
792 UPDATE OKL_PARALLEL_PROCESSES opp
793 SET process_status = 'OKL_STATUS_INAPPROPRIATE'
794 WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE
795 AND opp.process_status = 'PENDING_ASSIGNMENT'
796 AND opp.assigned_process = TO_CHAR(l_seq_next)
797 AND EXISTS
798 ( SELECT 'INVALID'
799 FROM okc_k_headers_all_b chr
800 WHERE chr.id = opp.khr_id
801 AND chr.sts_code NOT IN ( 'COMPLETE','BOOKED','APPROVED' )
802 );
803 log_msg(FND_FILE.LOG, 'Checking: Contract status should be Booked/Complete/Approved - End: '
804 || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
805
806 -- Case 2: Contract do not have a transaction state (CLOB) already associated to it
807 log_msg(FND_FILE.LOG, 'Checking: Contract do not have a transaction state (CLOB) already associated to it - Start: '
808 || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
809 UPDATE OKL_PARALLEL_PROCESSES opp
810 SET process_status = 'OKL_CLOB_EXISTS'
811 WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE
812 AND opp.process_status = 'PENDING_ASSIGNMENT'
813 AND opp.assigned_process = TO_CHAR(l_seq_next)
814 AND EXISTS
815 (
816 SELECT 'CLOB_EXISTS'
817 FROM okl_stream_trx_data trx,
818 okl_stream_interfaces osi
819 WHERE osi.transaction_number = trx.transaction_number
820 AND osi.khr_id = opp.khr_id
821 AND ( ( trx.last_trx_state = 'Y'
822 AND ( (osi.orp_code = 'UPGRADE' AND osi.sis_code = 'PROCESS_COMPLETE')
823 OR (osi.orp_code = 'AUTH' AND osi.sis_code = 'PROCESS_COMPLETE')
824 )
825 )
826 OR ( osi.orp_code = 'UPGRADE' AND
827 osi.sis_code IN ('PROCESSING_REQUEST', 'RET_DATA_RECEIVED' )
828 )
829 )
830 );
831 log_msg(FND_FILE.LOG, 'Checking: Contract do not have a transaction state (CLOB) already associated to it - End: '
832 || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
833
834
835 -- Case 3: Online Mass Rebook Not Processed
836 log_msg(FND_FILE.LOG, 'Checking: Online Mass Rebook Not Processed - Start: '
837 || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
838 UPDATE OKL_PARALLEL_PROCESSES opp
839 SET process_status = 'OKL_MRBK_NOT_PROCESSED'
840 WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE
841 AND opp.process_status = 'PENDING_ASSIGNMENT'
842 AND opp.assigned_process = TO_CHAR(l_seq_next)
843 AND opp.khr_id IN
844 (
845 SELECT rsc.khr_id orig_contract_id
846 FROM okl_rbk_selected_contract rsc
847 WHERE opp.khr_id = rsc.khr_id
848 AND rsc.transaction_id IS NULL
849 AND rsc.status <> 'PROCESSED'
850 );
851 log_msg(FND_FILE.LOG, 'Checking: Online Mass Rebook Not Processed - End: '
852 || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
853
854 -- Case 4: Online Rebook not Activated
855 log_msg(FND_FILE.LOG, 'Checking: Online Rebook not Activated - Start: '
856 || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
857 UPDATE OKL_PARALLEL_PROCESSES opp
858 SET process_status = 'OKL_ONLINE_RBK_NOT_PROCESSED'
859 WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE
860 AND opp.process_status = 'PENDING_ASSIGNMENT'
861 AND opp.assigned_process = TO_CHAR(l_seq_next)
862 AND opp.khr_id IN
863 (
864 SELECT trx.khr_id orig_contract_id
865 FROM okl_trx_contracts trx
866 WHERE trx.khr_id_new IS NOT NULL
867 AND trx.tsu_code = 'ENTERED'
868 AND trx.rbr_code IS NOT NULL
869 AND trx.tcn_type = 'TRBK'
870 AND trx.representation_type = 'PRIMARY'
871 );
872 log_msg(FND_FILE.LOG, 'Checking: Online Rebook not Activated - End: '
873 || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
874
875 -- Case 5: Residual Value Writedown not Processed
876 log_msg(FND_FILE.LOG, 'Checking: Residual Value Writedown not Processed - Start: '
877 || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
878 UPDATE OKL_PARALLEL_PROCESSES opp
879 SET process_status = 'OKL_RVWD_NOT_PROCESSED'
880 WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE
881 AND opp.process_status = 'PENDING_ASSIGNMENT'
882 AND opp.assigned_process = TO_CHAR(l_seq_next)
883 AND opp.khr_id IN
884 (
885 SELECT l.dnz_khr_id
886 FROM OKL_TRX_ASSETS h,
887 okl_txl_assets_b l
888 WHERE h.id = l.tas_id
889 AND h.tsu_code IN ('ENTERED','ERROR')
890 AND h.tas_type = 'ARC'
891 );
892 log_msg(FND_FILE.LOG, 'Checking: Residual Value Writedown not Processed - End: '
893 || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
894
895 -- Case 6: Termination Quote Not Accepted
896 log_msg(FND_FILE.LOG, 'Checking: Termination Quote Not Accepted - Start: '
897 || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
898 UPDATE OKL_PARALLEL_PROCESSES opp
899 SET process_status = 'OKL_TQ_NOT_ACCEPTED'
900 WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE
901 AND opp.process_status = 'PENDING_ASSIGNMENT'
902 AND opp.assigned_process = TO_CHAR(l_seq_next)
903 AND opp.khr_id IN
904 (
905 SELECT khr_id
906 FROM okl_trx_quotes_b
907 WHERE partial_yn = 'Y'
908 AND qst_code NOT IN ('ACCEPTED','COMPLETE','IN_PROCESS')
909 );
910 log_msg(FND_FILE.LOG, 'Checking: Termination Quote Not Accepted - End: '
911 || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
912 END IF;
913
914 -- Identify the contracts which are ineligible for upgrade. Following fall into that category:
915 -- 1. Termination quote accepted and before it raised the termination transaction the quote failed
916 -- 2. Termination transaction has been raised but the termination transaction has errored out
917 -- 3. Any mass rebook transactions in progress.
918
919 IF p_criteria_set IN ('CONTRACT', 'REAMORT')
920 THEN
921 -- Case 1: TQ accepted and before it raised the termination transaction the quote failed
922 log_msg(FND_FILE.LOG, 'Checking: TQ accepted and fails before trx created - Start: '
923 || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
924 UPDATE OKL_PARALLEL_PROCESSES opp
925 SET process_status = 'OKL_REVISION_IN_PROGRESS'
926 WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE
927 AND opp.process_status = 'PENDING_ASSIGNMENT'
928 AND opp.assigned_process = TO_CHAR(l_seq_next)
929 AND exists
930 (
931 select q.khr_id
932 from okl_trx_quotes_b q
933 where q.qtp_code like 'TER%' -- Termination quote
934 and NVL(q.consolidated_yn,'N') = 'N'
935 and q.partial_yn = 'Y'
936 and q.qst_code = 'ACCEPTED'
937 and q.khr_id = opp.khr_id
938 and q.id not in (select t.qte_id from okl_trx_contracts_all t where q.id = t.qte_id)
939 );
940
941 log_msg(FND_FILE.LOG, 'Checking: TQ accepted and fails before trx created - End : ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
942
943 -- Case 2: Termination transaction has been raised but the termination transaction phase has errored out
944 log_msg(FND_FILE.LOG, 'Checking: TQ accepted and termination trx phase fails - Start: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
945
946 UPDATE OKL_PARALLEL_PROCESSES opp
947 SET process_status = 'OKL_REVISION_IN_PROGRESS'
948 WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE
949 AND opp.process_status = 'PENDING_ASSIGNMENT'
950 AND opp.assigned_process = TO_CHAR(l_seq_next)
951 AND exists
952 (
953 select q.khr_id
954 from okl_trx_quotes_b q, okl_trx_contracts_all t
955 where q.qtp_code like 'TER%'
956 and NVL(q.consolidated_yn,'N') = 'N'
957 and q.partial_yn = 'Y'
958 and q.khr_id = opp.khr_id
959 and q.id = t.qte_id
960 and t.tcn_type = 'ALT'
961 and t.tmt_status_code not in ('PROCESSED')
962 );
963
964 log_msg(FND_FILE.LOG, 'Checking: TQ accepted and termination trx phase fails - End : ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
965
966 -- Case 3: Mass Rebook in progress
967 log_msg(FND_FILE.LOG, 'Checking: Mass Rebook in progress - Start: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
968
969 UPDATE OKL_PARALLEL_PROCESSES opp
970 SET process_status = 'OKL_REVISION_IN_PROGRESS'
971 WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE
972 AND opp.process_status = 'PENDING_ASSIGNMENT'
973 AND opp.assigned_process = TO_CHAR(l_seq_next)
974 AND exists
975 (
976 SELECT 1
977 FROM okl_trx_contracts ktrx
978 where ktrx.khr_id = opp.khr_id
979 AND ktrx.khr_id_new IS NULL
980 AND ktrx.tsu_code = 'ENTERED'
981 AND ktrx.rbr_code IS NOT NULL
982 AND ktrx.tcn_type = 'TRBK'
983 AND ktrx.representation_type = 'PRIMARY'
984 AND EXISTS (SELECT '1'
985 FROM okl_rbk_selected_contract rbk_khr
986 WHERE rbk_khr.khr_id = ktrx.khr_id
987 AND rbk_khr.status <> 'PROCESSED')
988 );
989
990 log_msg(FND_FILE.LOG, 'Checking: Mass Rebook in progres - End : ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
991 -- Finally Commiting: Marking the Contracts not eligible for the Upgrade
992 COMMIT;
993 END IF; -- IF p_criteria_set IN ('CONTRACT', 'REAMORT')
994
995 -- First of all fetch the Total Information into a PL/SQL table
996 log_msg(FND_FILE.LOG, 'Opening the Cursor get_esg_upg_contracts_csr' );
997 OPEN get_esg_upg_contracts_csr( p_process_sequence => TO_CHAR(l_seq_next) );
998 LOOP
999 -- Bulk Collect the Contracts which has Assets depreciated in the inputted
1000 -- Book Type and Period
1001 log_msg(FND_FILE.LOG, 'Before Executing the fetch on the Cursor get_esg_upg_contracts_csr: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
1002 FETCH get_esg_upg_contracts_csr BULK COLLECT INTO l_temp_upg_contracts_tbl
1003 LIMIT G_LIMIT_SIZE;
1004 log_msg(FND_FILE.LOG, 'After Executing the fetch on the Cursor get_esg_upg_contracts_csr: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
1005 log_msg(FND_FILE.LOG, 'Distinct Contracts fetched in this Loop ' || l_temp_upg_contracts_tbl.COUNT );
1006 -- Exit Conditionally ..
1007 EXIT WHEN get_esg_upg_contracts_csr%ROWCOUNT = 0;
1008 -- Loop on the l_temp_upg_contracts_tbl and append the records at the end of the
1009 -- l_upg_contracts_tbl / l_non_upg_contracts_tbl
1010 log_msg(FND_FILE.LOG, 'Copying the Contracts [' || l_temp_upg_contracts_tbl.COUNT
1011 || '] fetched in this Loop - Start: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
1012 FOR i IN l_temp_upg_contracts_tbl.FIRST .. l_temp_upg_contracts_tbl.LAST
1013 LOOP
1014 IF l_temp_upg_contracts_tbl(i).status = 'PENDING_ASSIGNMENT'
1015 THEN
1016 l_upg_contracts_tbl(upg_index) := l_temp_upg_contracts_tbl(i);
1017 -- Increment the upg_index
1018 upg_index := upg_index + 1;
1019 ELSE
1020 l_non_upg_contracts_tbl(non_upg_index) := l_temp_upg_contracts_tbl(i);
1021 -- Increment the non_upg_index
1022 non_upg_index := non_upg_index + 1;
1023 END IF;
1024 END LOOP;
1025 log_msg(FND_FILE.LOG, 'Copying the Contracts [' || l_temp_upg_contracts_tbl.COUNT
1026 || '] fetched in this Loop - End : ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
1027 -- Delete the Temporary Table now ..
1028 l_temp_upg_contracts_tbl.DELETE;
1029 -- Exit when there are no Assets to be Processed
1030 EXIT WHEN get_esg_upg_contracts_csr%NOTFOUND;
1031 END LOOP; -- Loop on get_esg_upg_contracts_csr
1032 CLOSE get_esg_upg_contracts_csr; -- Close the Cursor
1033
1034 IF p_criteria_set IN ( 'REAMORT' )
1035 THEN
1036 -- Log n Print the Following Data:
1037 log_n_print_msg( 'Total Contracts Eligible for Upgrade : ' ||
1038 NVL( NVL(l_non_upg_contracts_tbl.COUNT,0) +
1039 NVL(l_upg_contracts_tbl.COUNT,0)
1040 ,0) );
1041 log_n_print_msg( 'Total Contracts Initiated for Upgrade : ' ||
1042 NVL(l_upg_contracts_tbl.COUNT,0) );
1043 log_n_print_msg( 'Total Eligible Contracts Excluded from Upgrade : ' ||
1044 NVL(l_non_upg_contracts_tbl.COUNT,0) );
1045 ELSE
1046 -- Log n Print the Following Data:
1047 -- Number of Contracts picked for Processing
1048 log_n_print_msg( 'Number of Contracts matching the Criteria : ' ||
1049 NVL( NVL(l_non_upg_contracts_tbl.COUNT,0) +
1050 NVL(l_upg_contracts_tbl.COUNT,0)
1051 ,0) );
1052 log_n_print_msg( 'Number of Contracts eligible for Upgrade : ' ||
1053 NVL(l_upg_contracts_tbl.COUNT,0) );
1054 log_n_print_msg( 'Number of Contracts NOT eligible for Upgrade : ' ||
1055 NVL(l_non_upg_contracts_tbl.COUNT,0) );
1056 END IF;
1057
1058 IF l_non_upg_contracts_tbl.COUNT > 0 AND p_criteria_set IN ( 'CONTRACT', 'REVISION' )
1059 THEN
1060 log_msg(FND_FILE.OUTPUT, ' ');
1061 log_msg(FND_FILE.OUTPUT, ' ');
1062 log_msg(FND_FILE.OUTPUT, 'The following contracts cannot be upgraded as processing is in progress :' );
1063 log_msg(FND_FILE.OUTPUT, '-------------------------------------------------------------------------' );
1064 log_msg(FND_FILE.OUTPUT, ' ');
1065 log_msg(FND_FILE.OUTPUT, 'SL. # Contract Number Status ');
1066 log_msg(FND_FILE.OUTPUT, '--------------------------------------------------------------------------------------------------');
1067 FOR non_upg_index IN l_non_upg_contracts_tbl.FIRST .. l_non_upg_contracts_tbl.LAST
1068 LOOP
1069 SELECT b.meaning
1070 INTO l_k_status
1071 FROM okc_k_headers_all_b a
1072 ,okc_statuses_tl b
1073 WHERE a.contract_number = l_non_upg_contracts_tbl(non_upg_index).contract_number
1074 AND a.sts_code = b.code
1075 AND b.language = USERENV('LANG');
1076
1077 log_msg(FND_FILE.OUTPUT,
1078 LPAD(non_upg_index+1, 8, ' ' ) || ' ' ||
1079 RPAD(l_non_upg_contracts_tbl(non_upg_index).contract_number, 43, ' ' ) ||
1080 ' - ' || l_k_status);
1081 END LOOP;
1082 log_msg(FND_FILE.OUTPUT, '----------------------------------------------------------------------------------------');
1083 END IF; -- IF l_non_upg_contracts_tbl.COUNT > 0
1084
1085 IF l_non_upg_contracts_tbl.COUNT > 0 AND p_criteria_set IN ( 'REAMORT' )
1086 THEN
1087 log_msg(FND_FILE.OUTPUT, ' ');
1088 log_msg(FND_FILE.OUTPUT, ' ');
1089 log_msg(FND_FILE.OUTPUT, '--------------------' );
1090 log_msg(FND_FILE.OUTPUT, 'Excluded Contracts :' );
1091 log_msg(FND_FILE.OUTPUT, '--------------------' );
1092 log_msg(FND_FILE.OUTPUT, ' ');
1093 log_msg(FND_FILE.OUTPUT, 'SL. # Contract Number Exclusion Reason ');
1094 log_msg(FND_FILE.OUTPUT, '-----------------------------------------------------------------------------------------------------------------------------------------------------------');
1095 l_k_status := '';
1096 FOR non_upg_index IN l_non_upg_contracts_tbl.FIRST .. l_non_upg_contracts_tbl.LAST
1097 LOOP
1098 IF l_non_upg_contracts_tbl(non_upg_index).status = 'OKL_STATUS_INAPPROPRIATE'
1099 THEN
1100 l_k_status := 'Contract is not in Complete, Approved or Booked status.';
1101 ELSIF l_non_upg_contracts_tbl(non_upg_index).status = 'OKL_CLOB_EXISTS'
1102 THEN
1103 l_k_status := 'Valid pricing state (CLOB) exists for contract.';
1104 ELSIF l_non_upg_contracts_tbl(non_upg_index).status = 'OKL_MRBK_NOT_PROCESSED'
1105 THEN
1106 l_k_status := 'Mass Online Rebook request has not been processed.';
1107 ELSIF l_non_upg_contracts_tbl(non_upg_index).status = 'OKL_ONLINE_RBK_NOT_PROCESSED'
1108 THEN
1109 l_k_status := 'Online Rebook request has not been processed.';
1110 ELSIF l_non_upg_contracts_tbl(non_upg_index).status = 'OKL_RVWD_NOT_PROCESSED'
1111 THEN
1112 l_k_status := 'Residual Value Writedown has not been processed.';
1113 ELSIF l_non_upg_contracts_tbl(non_upg_index).status = 'OKL_TQ_NOT_ACCEPTED'
1114 THEN
1115 l_k_status := 'Termination Quote has not been Processed.';
1116 ELSIF l_non_upg_contracts_tbl(non_upg_index).status = 'OKL_REVISION_IN_PROGRESS'
1117 THEN
1118 l_k_status := 'Revision in Progress.';
1119 END IF;
1120
1121 log_msg(FND_FILE.OUTPUT,
1122 LPAD(non_upg_index+1, 8, ' ' ) || ' ' ||
1123 RPAD(l_non_upg_contracts_tbl(non_upg_index).contract_number, 43, ' ' ) ||
1124 ' - ' || l_k_status);
1125 END LOOP;
1126 log_msg(FND_FILE.OUTPUT, '-----------------------------------------------------------------------------------------------------------------------------------------------------------');
1127 END IF; -- IF l_non_upg_contracts_tbl.COUNT > 0
1128
1129 IF l_upg_contracts_tbl.COUNT > 0
1130 THEN
1131 log_msg(FND_FILE.OUTPUT, ' ');
1132 log_msg(FND_FILE.OUTPUT, ' ');
1133 log_msg(FND_FILE.OUTPUT, 'Eligible Contracts :' );
1134 log_msg(FND_FILE.OUTPUT, '--------------------');
1135 log_msg(FND_FILE.OUTPUT, ' ');
1136 log_msg(FND_FILE.OUTPUT, ' SL. # Contract Number ');
1137 log_msg(FND_FILE.OUTPUT, '----------------------------------------------------------------------------------------');
1138 FOR upg_index IN l_upg_contracts_tbl.FIRST .. l_upg_contracts_tbl.LAST
1139 LOOP
1140 log_msg(FND_FILE.OUTPUT,LPAD(upg_index+1, 8, ' ' ) || ' ' ||
1141 l_upg_contracts_tbl(upg_index).contract_number);
1142 END LOOP;
1143 log_msg(FND_FILE.OUTPUT, '----------------------------------------------------------------------------------------');
1144 END IF; -- IF l_non_upg_contracts_tbl.COUNT > 0
1145
1146 IF p_mode_of_run = 'REVIEW' AND p_criteria_set IN ( 'REAMORT' )
1147 THEN
1148 log_msg(FND_FILE.LOG, 'In REVIEW Mode and REAMORT Upgrade Program ' );
1149 log_msg(FND_FILE.LOG, 'Start: Trying to fetch all contracts that were kicked off for Upgrade but havent completed successfully. ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
1150 -- Initialize the l_err_upg_index index
1151 l_err_upg_index := 0;
1152 log_msg(FND_FILE.OUTPUT, ' ');
1153 log_msg(FND_FILE.OUTPUT, ' ');
1154 log_msg(FND_FILE.OUTPUT, 'Previously Upgraded Contracts with Pricing Errors :' );
1155 log_msg(FND_FILE.OUTPUT, '---------------------------------------------------' );
1156 log_msg(FND_FILE.OUTPUT, ' SL. # Contract Number Pricing Status');
1157 log_msg(FND_FILE.OUTPUT, '-----------------------------------------------------------------------------------------------------------------------------------------------------------');
1158 -- Section to print the contracts that were attempted earlier for Upgrade during earlier runs
1159 FOR t_rec IN get_khr_errored_during_upgrade
1160 LOOP
1161 l_err_upg_index := l_err_upg_index + 1;
1162 log_msg(FND_FILE.OUTPUT,
1163 LPAD(l_err_upg_index, 8, ' ' ) || ' ' ||
1164 RPAD(t_rec.contract_number, 43, ' ' ) || ' - ' ||
1165 t_rec.pricing_status );
1166 END LOOP;
1167 log_msg(FND_FILE.OUTPUT, '-----------------------------------------------------------------------------------------------------------------------------------------------------------');
1168 log_msg(FND_FILE.LOG, 'End: Trying to fetch all contracts that were kicked off for Upgrade but havent completed successfully. ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
1169 END IF;
1170
1171 IF p_mode_of_run = 'SUBMIT'
1172 THEN
1173 IF l_upg_contracts_tbl.COUNT > 0
1174 THEN
1175 log_msg(FND_FILE.LOG, 'Total Number of records fetched=' || l_upg_contracts_tbl.COUNT );
1176 -- Assign the data from the l_deprn_contracts_tbl to l_pp_deprn_khrs_tbl
1177 FOR upg_index IN l_upg_contracts_tbl.FIRST .. l_upg_contracts_tbl.LAST
1178 LOOP
1179 l_object_value_tbl(upg_index) := l_upg_contracts_tbl(upg_index).contract_number;
1180 l_khr_id_tbl(upg_index) := l_upg_contracts_tbl(upg_index).khr_id;
1181 l_volume_tbl(upg_index) := l_upg_contracts_tbl(upg_index).no_of_assets;
1182 l_assigned_process_tbl(upg_index) := TO_CHAR(l_seq_next);
1183 END LOOP;
1184
1185 -- Create l_num_workers number of Workers
1186 FOR i in 1..l_num_workers
1187 LOOP -- put all workers into a table
1188 l_worker_load(i).worker_number := i;
1189 l_worker_load(i).worker_load := 0; -- initialize load with zero
1190 l_worker_load(i).used := FALSE; -- Initialize with FALSE as none are assigned to this
1191 END LOOP;
1192 log_msg(FND_FILE.LOG, 'Initialized totally ' || l_num_workers || ' workers ' );
1193 log_msg(FND_FILE.LOG, 'Allocation of Workers for every contract is in Progress .. ' );
1194 l_lightest_worker := 1;
1195
1196 -- Loop through the Depreciation Contracts and Assign the Workers
1197 FOR upg_index IN l_upg_contracts_tbl.FIRST .. l_upg_contracts_tbl.LAST
1198 LOOP
1199 l_assigned_process_tbl(upg_index) := l_lightest_worker;
1200 -- put current contract into the lightest worker
1201 IF l_worker_load.EXISTS(l_lightest_worker)
1202 THEN
1203 -- Increment the Assigned Worker Load by Number of Assets
1204 l_worker_load(l_lightest_worker).worker_load :=
1205 l_worker_load(l_lightest_worker).worker_load +
1206 l_upg_contracts_tbl(upg_index).no_of_assets;
1207 -- Update the used flag of the current lightest worker to indicate that its used.
1208 l_worker_load(l_lightest_worker).used := TRUE;
1209 END IF;
1210 -- default the lighest load with the first element as a starting point
1211 IF l_worker_load.EXISTS(1)
1212 THEN
1213 l_lightest_load := l_worker_load(1).worker_load;
1214 l_lightest_worker := l_worker_load(1).worker_number;
1215 -- logic to find lightest load
1216 FOR i in 1..l_worker_load.COUNT
1217 LOOP
1218 IF (l_worker_load(i).worker_load = 0)
1219 OR (l_worker_load(i).worker_load < l_lightest_load)
1220 THEN
1221 l_lightest_load := l_worker_load(i).worker_load;
1222 l_lightest_worker := l_worker_load(i).worker_number;
1223 END IF;
1224 END LOOP;
1225 END IF;
1226 END LOOP; -- FOR upg_index IN l_upg_contracts_tbl.FIRST .. l_upg_contracts_tbl.LAST
1227 log_msg(FND_FILE.LOG, 'Done with allocation of Workers for every contract.' );
1228 log_msg(FND_FILE.LOG, 'Process Sequence Number = ' || l_seq_next );
1229 log_msg(FND_FILE.LOG, 'G_ESG_PRB_KHR_UPG_OBJ_TYPE = ' || G_ESG_PRB_KHR_UPG_OBJ_TYPE );
1230
1231 -- log_msg(FND_FILE.LOG, 'Assigned Process Contract Number KHR_ID Volume ');
1232 -- log_msg(FND_FILE.LOG, '------------------------------------------------------------------------------------------------------------------------');
1233 --
1234 -- FOR upg_index in l_upg_contracts_tbl.FIRST .. l_upg_contracts_tbl.LAST
1235 -- LOOP
1236 -- log_msg(FND_FILE.LOG, RPAD(l_assigned_process_tbl(upg_index),30, ' ') ||
1237 -- RPAD(l_object_value_tbl(upg_index),40, ' ') ||
1238 -- RPAD(l_khr_id_tbl(upg_index),32, ' ' ) ||
1239 -- LPAD(l_volume_tbl(upg_index),15, ' ') );
1240 -- END LOOP;
1241
1242 -- Now Bulk Update the Contract Numbers in Parallel Processes with the
1243 -- Assigned Worker Number
1244 log_msg(FND_FILE.LOG, 'Updated the Records in OKL_PARALLEL_PROCESSES with the Assigned Process - Start: '
1245 || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
1246 FORALL upg_index in l_upg_contracts_tbl.FIRST .. l_upg_contracts_tbl.LAST
1247 UPDATE OKL_PARALLEL_PROCESSES
1248 SET assigned_process = l_seq_next || '-' || l_assigned_process_tbl(upg_index)
1249 ,process_status = 'ASSIGNED'
1250 WHERE object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE
1251 AND object_value = l_object_value_tbl(upg_index)
1252 AND process_status = 'PENDING_ASSIGNMENT'
1253 AND khr_id = l_khr_id_tbl(upg_index);
1254 log_msg(FND_FILE.LOG, 'Updated the Records in OKL_PARALLEL_PROCESSES with the Assigned Process - End : '
1255 || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
1256 -- COMMIT the Updation;
1257 COMMIT;
1258 log_msg(FND_FILE.LOG, 'Committed the Updation Changes: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
1259
1260 FOR i in l_worker_load.FIRST .. l_worker_load.LAST
1261 LOOP
1262 -- Request only if the Worker is used and has some load to process ..
1263 IF l_worker_load(i).used
1264 THEN
1265 l_worker_id := TO_CHAR(l_seq_next)||'-'||TO_CHAR(i);
1266 -- FND_REQUEST.set_org_id(MO_GLOBAL.get_current_org_id); --MOAC- Concurrent request
1267 IF p_criteria_set = 'CONTRACT'
1268 THEN
1269 log_msg(FND_FILE.LOG, 'CONTRACT: Submitting the Request with worker_id=' || l_worker_id );
1270 l_reqid := FND_REQUEST.submit_request(
1271 application => 'OKL'
1272 ,program => 'OKLESGPRBCONCW' -- PRB Upgrade: Parallel Worker Conc. Program
1273 ,sub_request => TRUE
1274 ,argument1 => l_worker_id);
1275 log_msg(FND_FILE.LOG, ' CONTRACT: Returned request_id=' || l_reqid );
1276 ELSIF p_criteria_set = 'REAMORT'
1277 THEN
1278 log_msg(FND_FILE.LOG, 'REAMORT: Submitting the Request with worker_id=' || l_worker_id );
1279 l_reqid := FND_REQUEST.submit_request(
1280 application => 'OKL'
1281 ,program => 'OKLESGREAMORTUPGW' -- Reamort Upgrade: Parallel Worker Conc. Program
1282 ,sub_request => TRUE
1283 ,argument1 => l_worker_id);
1284 log_msg(FND_FILE.LOG, ' REAMORT: Returned request_id=' || l_reqid );
1285 END IF;
1286
1287 IF l_reqid = 0
1288 THEN
1289 -- Request Submission failed with Error .. Hence, Exit with Error
1290 errbuf := fnd_message.get;
1291 retcode := 2;
1292 ELSE
1293 errbuf := 'Sub-Request submitted successfully';
1294 retcode := 0 ;
1295 END IF;
1296 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Launching Process '||l_worker_id||' with Request ID '||l_reqid);
1297 END IF; -- IF l_worker_load(i).used
1298 END LOOP; -- FOR j in 1 .. l_worker_load.LAST
1299 -- Set the Request Data to be used in the re-run of the Master Program ..
1300 FND_CONC_GLOBAL.set_req_globals(
1301 conc_status => 'PAUSED'
1302 ,request_data => '2 RUN'); -- Instead of NULL, it was i here ..
1303 ELSE
1304 log_msg(FND_FILE.LOG, 'No Workers Assigned. Reason: No Data Found for Processing!');
1305 END IF; -- IF l_upg_contracts_tbl.COUNT > 0
1306 ELSIF p_mode_of_run = 'REVIEW'
1307 THEN
1308 -- Delete the records populated here finally
1309 log_msg(FND_FILE.LOG, 'Review Mode: Deletion of Records - Start: ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
1310
1311 DELETE OKL_PARALLEL_PROCESSES opp
1312 WHERE opp.object_type = G_ESG_PRB_KHR_UPG_OBJ_TYPE -- 'ESG_PRB_UPGRADE_CONTRACT'
1313 AND opp.assigned_process = TO_CHAR(l_seq_next);
1314
1315 log_msg(FND_FILE.LOG, 'Review Mode: Deletion of Records - End : ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH:MI:SS') );
1316 -- End of Review Mode
1317 retcode := 0;
1318 END IF; -- IF p_mode_of_run = 'SUBMIT'
1319 END IF; -- IF req_data IS NOT NULL
1320 log_msg(FND_FILE.LOG, 'eff_dated_rbk_upgrade_conc: retcode = ' || retcode );
1321 log_msg(FND_FILE.LOG, 'eff_dated_rbk_upgrade_conc: End (-)' );
1322 END eff_dated_rbk_upgrade_conc;
1323
1324 END OKL_PRB_UPGRADE_PVT;
[Home] [Help]