[Home] [Help]
PACKAGE BODY: APPS.OKL_BILLING_CONTROLLER_PVT
Source
1 PACKAGE BODY OKL_BILLING_CONTROLLER_PVT AS
2 /* $Header: OKLPBICB.pls 120.37.12010000.2 2008/12/12 20:25:23 cklee ship $*/
3
4 TYPE req_tab_type is TABLE of NUMBER index by binary_integer;
5 TYPE batch_tab_type is TABLE of VARCHAR2(30) index by binary_integer;
6
7 -- Bug 4546873
8 g_opp_seq_num okl_parallel_processes.assigned_process%type;
9 -- end Bug 4546873;
10
11 L_MODULE VARCHAR2(40) := 'LEASE.RECEIVABLES.BILLING';
12 L_DEBUG_ENABLED VARCHAR2(10);
13 -- L_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
14 L_LEVEL_PROCEDURE NUMBER;
15 IS_DEBUG_PROCEDURE_ON BOOLEAN;
16
17 -- Contract or Investor Agreement type
18 L_IA_TYPE VARCHAR2(10) :='IA';
19 L_CONTRACT_TYPE VARCHAR2(10) :='CONTRACT';
20 -- Contract or Investor Agreement type
21
22 -- Bug 4520466
23 FUNCTION get_next_bill_date ( p_khr_id IN NUMBER )
24 return date IS
25
26 l_bill_date okl_strm_elements.stream_element_date%type;
27
28 cursor next_bill_date_csr( p_khr_id IN NUMBER ) IS
29 SELECT MIN(ste.stream_element_date)
30 FROM OKL_STRM_ELEMENTS ste,
31 OKL_STREAMS stm,
32 okl_strm_type_v sty,
33 okc_k_headers_b khr,
34 OKL_K_HEADERS khl,
35 okc_k_lines_b kle,
36 okc_statuses_b khs,
37 okc_statuses_b kls
38 WHERE ste.amount <> 0
39 AND stm.id = ste.stm_id
40 AND ste.date_billed IS NULL
41 AND stm.active_yn = 'Y'
42 AND stm.say_code = 'CURR'
43 AND sty.id = stm.sty_id
44 AND sty.billable_yn = 'Y'
45 AND khr.id = stm.khr_id
46 -- changed by zrehman for Bug#6788005 start
47 AND ((khr.scs_code IN ('LEASE', 'LOAN') AND khl.deal_type IS NOT NULL) OR (khr.scs_code = 'INVESTOR'))
48 -- changed by zrehman for Bug#6788005 start
49 --AND khr.sts_code IN ( 'BOOKED','EVERGREEN','TERMINATED')
50 AND khr.id = p_khr_id
51 AND khl.id = stm.khr_id
52 --AND khl.deal_type IS NOT NULL
53 AND khs.code = khr.sts_code
54 AND kle.id (+) = stm.kle_id
55 AND kls.code (+) = kle.sts_code;
56 --AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED');
57
58 begin
59
60 l_bill_date := null;
61
62 open next_bill_date_csr( p_khr_id );
63 fetch next_bill_date_csr into l_bill_date;
64 close next_bill_date_csr;
65
66 return l_bill_date;
67
68 exception
69 when others then
70 fnd_file.put_line(fnd_file.log,' **** Error deriving NEXT BILL DATE: '||SQLERRM||'. ****');
71 return null;
72 end get_next_bill_date;
73
74 procedure track_next_bill_date ( p_khr_id IN NUMBER )
75
76 is
77
78 cursor khr_bill_date_csr( p_khr_id NUMBER ) is
79 select nbd.khr_id
80 from okl_k_control nbd,
81 okl_k_headers_full_v khr
82 where nbd.khr_id = khr.id
83 and khr.id = p_khr_id;
84
85 l_khr_id number;
86 l_next_bill_date date;
87
88 -- Org Id and standard who columns
89 l_last_updated_by okl_k_control.last_updated_by%TYPE := Fnd_Global.USER_ID;
90 l_last_update_login okl_k_control.last_update_login%TYPE := Fnd_Global.LOGIN_ID;
91 l_request_id okl_k_control.request_id%TYPE := Fnd_Global.CONC_REQUEST_ID;
92 l_program_id okl_k_control.program_id%TYPE := Fnd_Global.CONC_PROGRAM_ID;
93
94 begin
95
96 if p_khr_id is null then
97 fnd_file.put_line(fnd_file.log,' **** KHR_ID must be supplied for TRACK_NEXT_BILL_DATE. ****');
98 return;
99 end if;
100
101 l_khr_id := null;
102
103 open khr_bill_date_csr( p_khr_id );
104 fetch khr_bill_date_csr into l_khr_id;
105 close khr_bill_date_csr;
106
107 l_next_bill_date := null;
108 l_next_bill_date := get_next_bill_date( p_khr_id );
109
110 -- if a record exists in NBD, then update it, else create a new entry
111 if l_khr_id is not null then
112
113 update okl_k_control
114 set EARLIEST_STRM_BILL_DATE = l_next_bill_date,
115 LAST_UPDATE_DATE = sysdate,
116 LAST_UPDATED_BY = l_last_updated_by,
117 LAST_UPDATE_LOGIN = l_last_update_login,
118 REQUEST_ID = l_request_id,
119 PROGRAM_ID = l_program_id,
120 PROGRAM_UPDATE_DATE = sysdate
121 where khr_id = l_khr_id;
122
123 else -- if no entry exists in NBD
124
125 INSERT INTO okl_k_control
126 (
127 khr_id, EARLIEST_STRM_BILL_DATE,
128 CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,
129 REQUEST_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE
130 )
131 VALUES
132 ( p_khr_id, l_next_bill_date,
133 l_last_updated_by,sysdate,sysdate,l_last_updated_by,l_last_update_login,
134 l_request_id,l_program_id,sysdate
135 );
136
137 end if;
138
139 exception
140 when others then
141 null;
142 end track_next_bill_date;
143 -- End Bug 4520466
144 -- -------------------------------------------------
145 -- To purge parallel processes table
146 -- -------------------------------------------------
147 -- Bug 4546873;
148 PROCEDURE PURGE_PARALLEL_PROCESSES (
149 errbuf OUT NOCOPY VARCHAR2,
150 retcode OUT NOCOPY NUMBER,
151 p_source IN VARCHAR2 DEFAULT NULL)
152 IS
153
154 -- --------------------------------------
155 -- To check for running concurrent pgms
156 -- --------------------------------------
157 CURSOR cnt_csr (p_conc_pgm1 VARCHAR2, p_conc_pgm2 VARCHAR2) IS
158 select count(*)
159 from fnd_concurrent_requests req,
160 fnd_concurrent_programs_vl pgm
161 where pgm.USER_CONCURRENT_PROGRAM_NAME
162 IN ( p_conc_pgm1, p_conc_pgm2)
163 and req.concurrent_program_id = pgm.concurrent_program_id
164 and req.PHASE_CODE <> 'C';
165
166 l_strm_bill_cnt NUMBER;
167 l_cnsld_cnt NUMBER;
168 l_xfer_cnt NUMBER;
169 l_prep_cnt NUMBER;
170
171 -- Bug 4546873
172 -- Org Id and standard who columns
173 l_last_updated_by okl_parallel_processes.last_updated_by%TYPE := Fnd_Global.USER_ID;
174 l_last_update_login okl_parallel_processes.last_update_login%TYPE := Fnd_Global.LOGIN_ID;
175 l_request_id okl_parallel_processes.request_id%TYPE := Fnd_Global.CONC_REQUEST_ID;
176 l_program_id okl_parallel_processes.program_id%TYPE := Fnd_Global.CONC_PROGRAM_ID;
177 l_org_id okl_parallel_processes.org_id%type;
178 -- End Bug 4546873
179
180 -- Print variables
181 l_print_strm_cnt NUMBER;
182 l_print_cons_cnt NUMBER;
183 l_print_xfer_cnt NUMBER;
184 l_print_prep_cnt NUMBER;
185
186 -- ----------------------------------------------------------
187 -- Operating Unit
188 -- ----------------------------------------------------------
189 CURSOR op_unit_csr IS
190 SELECT Name org_name, organization_id org_id
191 FROM hr_operating_units
192 WHERE mo_global.check_access(organization_id) = 'Y'; --MOAC
193
194
195
196 l_op_unit_name hr_operating_units.name%TYPE;
197 l_op_unit_id hr_operating_units.organization_id%TYPE;--MOAC
198
199 BEGIN
200
201
202
203 For I in op_unit_csr --for Multi Org
204 Loop
205
206 l_org_id := I.org_id;
207
208 -- ----------------------------
209 -- Initialize print variables
210 -- ----------------------------
211 l_print_strm_cnt := 0;
212 l_print_cons_cnt := 0;
213 l_print_xfer_cnt := 0;
214
215
216 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
217 fnd_file.put_line(fnd_file.log,'p_source: '||p_source);
218 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
219
220 IF p_source = 'STREAM_BILLING' THEN
221
222 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
223 fnd_file.put_line(fnd_file.log,'** START: Delete all Stream Billing records from OKL_PARALLEL_PROCESSES. **');
224
225 l_strm_bill_cnt := 0;
226 OPEN cnt_csr ('Master Program -- Process Billable Streams',
227 'Process Billable Streams');
228 FETCH cnt_csr INTO l_strm_bill_cnt;
229 CLOSE cnt_csr;
230
231 IF l_strm_bill_cnt > 0 THEN
232
233 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
234 fnd_file.put_line(fnd_file.log,' => Could not perform a delete all for Stream Billing '
235 ||'records because not all requests have Completed.');
236 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
237
238 ELSE
239 delete from okl_parallel_processes
240 where OBJECT_TYPE = 'CONTRACT';
241
242 l_print_strm_cnt := l_print_strm_cnt + (sql%rowcount);
243
244 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
245 fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
246 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
247
248 commit;
249 END IF;
250 fnd_file.put_line(fnd_file.log,'** END: Delete all Stream Billing records from OKL_PARALLEL_PROCESSES. **');
251
252 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
253 fnd_file.put_line(fnd_file.log,'** START: Delete Org specific Stream Billing records from OKL_PARALLEL_PROCESSES. **');
254
255 delete from okl_parallel_processes
256 where OBJECT_TYPE = 'CONTRACT'
257 and org_id = l_org_id
258 and request_id in (
259 select req.request_id
260 from fnd_concurrent_requests req,
261 fnd_concurrent_programs_vl pgm
262 where pgm.USER_CONCURRENT_PROGRAM_NAME IN ( 'Master Program -- Process Billable Streams',
263 'Process Billable Streams')
264 and req.concurrent_program_id = pgm.concurrent_program_id
265 and req.PHASE_CODE = 'C');
266
267 l_print_strm_cnt := l_print_strm_cnt + (sql%rowcount);
268
269 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
270 fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
271 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
272 fnd_file.put_line(fnd_file.log,'** END: Delete Org specific Stream Billing records from OKL_PARALLEL_PROCESSES. **');
273
274 commit;
275 -- rmunjulu R12 fixes -- comment out consolidation
276 /*
277 ELSIF p_source = 'CONSOLIDATION' THEN
278
279 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
280 fnd_file.put_line(fnd_file.log,'** START: Delete all Consolidation records from OKL_PARALLEL_PROCESSES. **');
281 l_cnsld_cnt := 0;
282 OPEN cnt_csr ('Master Program -- Receivables Bills Consolidation',
283 'Receivables Bills Consolidation');
284 FETCH cnt_csr INTO l_cnsld_cnt;
285 CLOSE cnt_csr;
286
287 IF l_cnsld_cnt > 0 THEN
288 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
289 fnd_file.put_line(fnd_file.log,' => Could not perform a delete all for Consolidation '
290 ||'records because not all requests have Completed.');
291 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
292 ELSE
293 delete from okl_parallel_processes
294 where OBJECT_TYPE = 'CUSTOMER';
295
296 l_print_cons_cnt := l_print_cons_cnt + (sql%rowcount);
297
298 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
299 fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
300 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
301
302 commit;
303 END IF;
304 fnd_file.put_line(fnd_file.log,'** END: Delete all Consolidation records from OKL_PARALLEL_PROCESSES. **');
305
306 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
307 fnd_file.put_line(fnd_file.log,'** START: Delete Org specific Consolidation records from OKL_PARALLEL_PROCESSES. **');
308 delete from okl_parallel_processes
309 where OBJECT_TYPE = 'CUSTOMER'
310 and org_id = l_org_id
311 and request_id in (
312 select req.request_id
313 from fnd_concurrent_requests req,
314 fnd_concurrent_programs_vl pgm
315 where pgm.USER_CONCURRENT_PROGRAM_NAME IN ( 'Master Program -- Receivables Bills Consolidation',
316 'Receivables Bills Consolidation')
317 and req.concurrent_program_id = pgm.concurrent_program_id
318 and req.PHASE_CODE = 'C');
319
320 l_print_cons_cnt := l_print_cons_cnt + (sql%rowcount);
321
322 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
323 fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
324 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
325
326 commit;
327 fnd_file.put_line(fnd_file.log,'** END: Delete Org specific Consolidation records from OKL_PARALLEL_PROCESSES. **');
328 */
329 ELSIF p_source = 'AR_TRANSFER' THEN
330 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
331 fnd_file.put_line(fnd_file.log,'** START: Delete all Transfer records from OKL_PARALLEL_PROCESSES. **');
332 l_xfer_cnt := 0;
333 OPEN cnt_csr ('Master Program -- Receivables Invoice Transfer',
334 'Receivables Invoice Transfer to AR');
335 FETCH cnt_csr INTO l_xfer_cnt;
336 CLOSE cnt_csr;
337
338 IF l_xfer_cnt > 0 THEN
339 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
340 fnd_file.put_line(fnd_file.log,' => Could not perform a delete all for Transfer '
341 ||'records because not all requests have Completed.');
342 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
343 ELSE
344 delete from okl_parallel_processes
345 where OBJECT_TYPE = 'XTRX_CONTRACT';
346
347 l_print_xfer_cnt := l_print_xfer_cnt + (sql%rowcount);
348
349 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
350 fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
351 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
352
353 commit;
354 END IF;
355 fnd_file.put_line(fnd_file.log,'** END: Delete all Transfer records from OKL_PARALLEL_PROCESSES. **');
356
357 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
358 fnd_file.put_line(fnd_file.log,'** START: Delete Org Specific Transfer records from OKL_PARALLEL_PROCESSES. **');
359 delete from okl_parallel_processes
360 where OBJECT_TYPE = 'XTRX_CONTRACT'
361 and org_id = l_org_id
362 and request_id in (
363 select req.request_id
364 from fnd_concurrent_requests req,
365 fnd_concurrent_programs_vl pgm
366 where pgm.USER_CONCURRENT_PROGRAM_NAME IN ( 'Master Program -- Receivables Invoice Transfer',
367 'Receivables Invoice Transfer to AR')
368 and req.concurrent_program_id = pgm.concurrent_program_id
369 and req.PHASE_CODE = 'C');
370
371 l_print_xfer_cnt := l_print_xfer_cnt + (sql%rowcount);
372
373 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
374 fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
375 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
376
377 commit;
378 fnd_file.put_line(fnd_file.log,'** END: Delete Org Specific Transfer records from OKL_PARALLEL_PROCESSES. **');
379 --fmiao 5209209 change
380 -- rmunjulu R12 fixes -- comment out prepare recvbles
381 /*
382 ELSIF p_source = 'AR_PREPARE' THEN
383 Fnd_File.PUT_LINE (Fnd_File.LOG,RPAD(' ', 132, ' '));
384 Fnd_File.put_line(Fnd_File.LOG,'** START: Delete all Prepare Receivables records from OKL_PARALLEL_PROCESSES. **');
385 l_prep_cnt := 0;
386 OPEN cnt_csr ('Master Program -- Prepare Receivables',
387 'Prepare Receivables Bills');
388 FETCH cnt_csr INTO l_prep_cnt;
389 CLOSE cnt_csr;
390
391 IF l_prep_cnt > 0 THEN
392 Fnd_File.PUT_LINE (Fnd_File.LOG,RPAD(' ', 132, ' '));
393 Fnd_File.put_line(Fnd_File.LOG,' => Could not perform a delete all for Prepare Receivables '
394 ||'records because not all requests have Completed.');
395 Fnd_File.PUT_LINE (Fnd_File.LOG,RPAD(' ', 132, ' '));
396 ELSE
397 DELETE FROM okl_parallel_processes
398 WHERE OBJECT_TYPE = 'PREP_CONTRACT';
399
400 l_print_prep_cnt := l_print_prep_cnt + (SQL%rowcount);
401
402 Fnd_File.PUT_LINE (Fnd_File.LOG,RPAD(' ', 132, ' '));
403 Fnd_File.put_line(Fnd_File.LOG,' => Deleted '||SQL%rowcount||' row(s).');
404 Fnd_File.PUT_LINE (Fnd_File.LOG,RPAD(' ', 132, ' '));
405
406 COMMIT;
407 END IF;
408 Fnd_File.put_line(Fnd_File.LOG,'** END: Delete all Prepare Receivables records from OKL_PARALLEL_PROCESSES. **');
409
410 Fnd_File.PUT_LINE (Fnd_File.LOG,RPAD(' ', 132, ' '));
411 Fnd_File.put_line(Fnd_File.LOG,'** START: Delete Org Specific Prepare Receivables records from OKL_PARALLEL_PROCESSES. **');
412 DELETE FROM okl_parallel_processes
413 WHERE OBJECT_TYPE = 'PREP_CONTRACT'
414 AND org_id = l_org_id
415 AND request_id IN (
416 SELECT req.request_id
417 FROM fnd_concurrent_requests req,
418 fnd_concurrent_programs_vl pgm
419 WHERE pgm.USER_CONCURRENT_PROGRAM_NAME IN ( 'Master Program -- Prepare Receivables',
420 'Prepare Receivables Bills')
421 AND req.concurrent_program_id = pgm.concurrent_program_id
422 AND req.PHASE_CODE = 'C');
423
424 l_print_prep_cnt := l_print_prep_cnt + (SQL%rowcount);
425
426 Fnd_File.PUT_LINE (Fnd_File.LOG,RPAD(' ', 132, ' '));
427 Fnd_File.put_line(Fnd_File.LOG,' => Deleted '||SQL%rowcount||' row(s).');
428 Fnd_File.PUT_LINE (Fnd_File.LOG,RPAD(' ', 132, ' '));
429
430 COMMIT;
431 Fnd_File.put_line(Fnd_File.LOG,'** END: Delete Org Specific Prepare Receivables records from OKL_PARALLEL_PROCESSES. **');
432
433 --fmiao 5209209 end
434 */
435 ELSIF p_source = 'ALL' THEN
436
437 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
438 fnd_file.put_line(fnd_file.log,'** START: Delete all Stream Billing records from OKL_PARALLEL_PROCESSES. **');
439 l_strm_bill_cnt := 0;
440 OPEN cnt_csr ('Master Program -- Process Billable Streams',
441 'Process Billable Streams');
442 FETCH cnt_csr INTO l_strm_bill_cnt;
443 CLOSE cnt_csr;
444
445 IF l_strm_bill_cnt > 0 THEN
446
447 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
448 fnd_file.put_line(fnd_file.log,' => Could not perform a delete all for Stream Billing '
449 ||'records because not all requests have Completed.');
450 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
451
452 ELSE
453 delete from okl_parallel_processes
454 where OBJECT_TYPE = 'CONTRACT';
455
456 l_print_strm_cnt := l_print_strm_cnt + (sql%rowcount);
457
458 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
459 fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
460 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
461
462 commit;
463 END IF;
464 fnd_file.put_line(fnd_file.log,'** END: Delete all Stream Billing records from OKL_PARALLEL_PROCESSES. **');
465
466 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
467 fnd_file.put_line(fnd_file.log,'** START: Delete Org specific Stream Billing records from OKL_PARALLEL_PROCESSES. **');
468
469 delete from okl_parallel_processes
470 where OBJECT_TYPE = 'CONTRACT'
471 and org_id = l_org_id
472 and request_id in (
473 select req.request_id
474 from fnd_concurrent_requests req,
475 fnd_concurrent_programs_vl pgm
476 where pgm.USER_CONCURRENT_PROGRAM_NAME IN ( 'Master Program -- Process Billable Streams',
477 'Process Billable Streams')
478 and req.concurrent_program_id = pgm.concurrent_program_id
479 and req.PHASE_CODE = 'C');
480
481 l_print_strm_cnt := l_print_strm_cnt + (sql%rowcount);
482
483 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
484 fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
485 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
486 fnd_file.put_line(fnd_file.log,'** END: Delete Org specific Stream Billing records from OKL_PARALLEL_PROCESSES. **');
487
488 commit;
489
490 -- rmunjulu R12 fixes -- comment out consolidation
491 /*
492 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
493 fnd_file.put_line(fnd_file.log,'** START: Delete all Consolidation records from OKL_PARALLEL_PROCESSES. **');
494 l_cnsld_cnt := 0;
495 OPEN cnt_csr ('Master Program -- Receivables Bills Consolidation',
496 'Receivables Bills Consolidation');
497 FETCH cnt_csr INTO l_cnsld_cnt;
498 CLOSE cnt_csr;
499
500 IF l_cnsld_cnt > 0 THEN
501 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
502 fnd_file.put_line(fnd_file.log,' => Could not perform a delete all for Consolidation '
503 ||'records because not all requests have Completed.');
504 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
505 ELSE
506 delete from okl_parallel_processes
507 where OBJECT_TYPE = 'CUSTOMER';
508
509 l_print_cons_cnt := l_print_cons_cnt + (sql%rowcount);
510
511 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
512 fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
513 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
514 commit;
515 END IF;
516 fnd_file.put_line(fnd_file.log,'** END: Delete all Consolidation records from OKL_PARALLEL_PROCESSES. **');
517
518 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
519 fnd_file.put_line(fnd_file.log,'** START: Delete Org specific Consolidation records from OKL_PARALLEL_PROCESSES. **');
520
521 delete from okl_parallel_processes
522 where OBJECT_TYPE = 'CUSTOMER'
523 and org_id = l_org_id
524 and request_id in (
525 select req.request_id
526 from fnd_concurrent_requests req,
527 fnd_concurrent_programs_vl pgm
528 where pgm.USER_CONCURRENT_PROGRAM_NAME IN ( 'Master Program -- Receivables Bills Consolidation',
529 'Receivables Bills Consolidation')
530 and req.concurrent_program_id = pgm.concurrent_program_id
531 and req.PHASE_CODE = 'C');
532
533 l_print_cons_cnt := l_print_cons_cnt + (sql%rowcount);
534
535 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
536 fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
537 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
538
539 commit;
540 fnd_file.put_line(fnd_file.log,'** END: Delete Org specific Consolidation records from OKL_PARALLEL_PROCESSES. **');
541 */
542 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
543 fnd_file.put_line(fnd_file.log,'** START: Delete all Transfer records from OKL_PARALLEL_PROCESSES. **');
544
545 l_xfer_cnt := 0;
546
547 OPEN cnt_csr ('Master Program -- Receivables Invoice Transfer',
548 'Receivables Invoice Transfer to AR');
549 FETCH cnt_csr INTO l_xfer_cnt;
550 CLOSE cnt_csr;
551
552 IF l_xfer_cnt > 0 THEN
553 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
554 fnd_file.put_line(fnd_file.log,' => Could not perform a delete all for Transfer '
555 ||'records because not all requests have Completed.');
556 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
557 ELSE
558 delete from okl_parallel_processes
559 where OBJECT_TYPE = 'XTRX_CONTRACT';
560
561 l_print_xfer_cnt := l_print_xfer_cnt + (sql%rowcount);
562
563 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
564 fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
565 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
566
567 commit;
568 END IF;
569
570 fnd_file.put_line(fnd_file.log,'** END: Delete all Transfer records from OKL_PARALLEL_PROCESSES. **');
571
572 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
573 fnd_file.put_line(fnd_file.log,'** START: Delete Org Specific Transfer records from OKL_PARALLEL_PROCESSES. **');
574 delete from okl_parallel_processes
575 where OBJECT_TYPE = 'XTRX_CONTRACT'
576 and org_id = l_org_id
577 and request_id in (
578 select req.request_id
579 from fnd_concurrent_requests req,
580 fnd_concurrent_programs_vl pgm
581 where pgm.USER_CONCURRENT_PROGRAM_NAME IN ( 'Master Program -- Receivables Invoice Transfer',
582 'Receivables Invoice Transfer to AR')
583 and req.concurrent_program_id = pgm.concurrent_program_id
584 and req.PHASE_CODE = 'C');
585
586 l_print_xfer_cnt := l_print_xfer_cnt + (sql%rowcount);
587
588 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
589 fnd_file.put_line(fnd_file.log,' => Deleted '||sql%rowcount||' row(s).');
590 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
591
592 commit;
593
594 fnd_file.put_line(fnd_file.log,'** END: Delete Org Specific Transfer records from OKL_PARALLEL_PROCESSES. **');
595 Fnd_File.PUT_LINE (fnd_file.log,RPAD(' ', 132, ' '));
596
597 -- rmunjulu R12 fixes -- comment out prepare recvbles
598 /*
599 --fmiao 5209209 change
600 Fnd_File.PUT_LINE (Fnd_File.LOG,RPAD(' ', 132, ' '));
601 Fnd_File.put_line(Fnd_File.LOG,'** START: Delete all Prepare Receivables records from OKL_PARALLEL_PROCESSES. **');
602
603 l_prep_cnt := 0;
604
605 OPEN cnt_csr ('Master Program -- Prepare Receivables',
606 'Prepare Receivables Bills');
607 FETCH cnt_csr INTO l_prep_cnt;
608 CLOSE cnt_csr;
609
610 IF l_prep_cnt > 0 THEN
611 Fnd_File.PUT_LINE (Fnd_File.LOG,RPAD(' ', 132, ' '));
612 Fnd_File.put_line(Fnd_File.LOG,' => Could not perform a delete all for Prepare Receivables '
613 ||'records because not all requests have Completed.');
614 Fnd_File.PUT_LINE (Fnd_File.LOG,RPAD(' ', 132, ' '));
615 ELSE
616 DELETE FROM okl_parallel_processes
617 WHERE OBJECT_TYPE = 'PREP_CONTRACT';
618
619 l_print_prep_cnt := l_print_prep_cnt + (SQL%rowcount);
620
621 Fnd_File.PUT_LINE (Fnd_File.LOG,RPAD(' ', 132, ' '));
622 Fnd_File.put_line(Fnd_File.LOG,' => Deleted '||SQL%rowcount||' row(s).');
623 Fnd_File.PUT_LINE (Fnd_File.LOG,RPAD(' ', 132, ' '));
624
625 COMMIT;
626 END IF;
627
628 Fnd_File.put_line(Fnd_File.LOG,'** END: Delete all Prepare Receivables records from OKL_PARALLEL_PROCESSES. **');
629
630 Fnd_File.PUT_LINE (Fnd_File.LOG,RPAD(' ', 132, ' '));
631 Fnd_File.put_line(Fnd_File.LOG,'** START: Delete Org Specific Prepare Receivables records from OKL_PARALLEL_PROCESSES. **');
632 DELETE FROM okl_parallel_processes
633 WHERE OBJECT_TYPE = 'PREP_CONTRACT'
634 AND org_id = l_org_id
635 AND request_id IN (
636 SELECT req.request_id
637 FROM fnd_concurrent_requests req,
638 fnd_concurrent_programs_vl pgm
639 WHERE pgm.USER_CONCURRENT_PROGRAM_NAME IN ( 'Master Program -- Prepare Receivables',
640 'Prepare Receivables Bills')
641 AND req.concurrent_program_id = pgm.concurrent_program_id
642 AND req.PHASE_CODE = 'C');
643
644 l_print_prep_cnt := l_print_prep_cnt + (SQL%rowcount);
645
646 Fnd_File.PUT_LINE (Fnd_File.LOG,RPAD(' ', 132, ' '));
647 Fnd_File.put_line(Fnd_File.LOG,' => Deleted '||SQL%rowcount||' row(s).');
648 Fnd_File.PUT_LINE (Fnd_File.LOG,RPAD(' ', 132, ' '));
649
650 COMMIT;
651
652 Fnd_File.put_line(Fnd_File.LOG,'** END: Delete Org Specific Prepare Receivables records from OKL_PARALLEL_PROCESSES. **');
653 Fnd_File.PUT_LINE (Fnd_File.LOG,RPAD(' ', 132, ' '));
654 --fmiao 5209209 end
655 */
656 END IF;
657 -- ------------------------
658 -- Print Summary report
659 -- ------------------------
660 l_op_unit_name := NULL;
661
662 --for multi org
663 l_op_unit_name:=I.org_name;
664
665 /* OPEN op_unit_csr;
666 FETCH op_unit_csr INTO l_op_unit_name;
667 CLOSE op_unit_csr; */ --commented for Multi Org records
668
669 -- Start New Out File stmathew 15-OCT-2004
670 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 54, ' ')||'Oracle Leasing and Finance Management'||LPAD(' ', 55, ' '));
671 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
672 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 51, ' ')||'Purge Parallel Porcesses Table'||LPAD(' ', 51, ' '));
673 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 51, ' ')||'------------------------------'||LPAD(' ', 51, ' '));
674 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
675 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
676 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Operating Unit: '||l_op_unit_name);
677 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Request Id: '||l_request_id||LPAD(' ',74,' ') ||'Run Date: '||TO_CHAR(SYSDATE));
678 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Currency: '||Okl_Accounting_Util.get_func_curr_code);
679 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD('-', 132, '-'));
680 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'Billing Source : ' ||p_source);
681 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD('-', 132, '-'));
682 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
683 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
684
685 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Processing Details:'||LPAD(' ', 113, ' '));
686 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
687 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Number of Deleted Stream Billing Records: '||l_print_strm_cnt);
688 -- rmunjulu R12 Fixes - comment out consolidation
689 -- Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Number of Deleted Consolidation Records : '||l_print_cons_cnt);
690 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Number of Deleted AR Transfer Records : '||l_print_xfer_cnt);
691 --fmiao 5209209 change
692 -- rmunjulu R12 fixes -- comment out prepare recvbles
693 -- Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Number of Deleted Prepare Recievables Records : '||l_print_prep_cnt);
694 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Total: '||(l_print_strm_cnt+l_print_cons_cnt+l_print_xfer_cnt+l_print_prep_cnt));
695 -- fmiao 5209209 end
696 --Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Total: '||(l_print_strm_cnt+l_print_cons_cnt+l_print_xfer_cnt));
697 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
698 End Loop; --for Multi Org records
699
700 EXCEPTION
701 WHEN OTHERS THEN
702 fnd_file.put_line (fnd_file.log,'Purge Program failed with error: '||SQLERRM);
703 END PURGE_PARALLEL_PROCESSES;
704 -- End Bug 4546873;
705
706 -- -------------------------------------------------
707 -- To print log messages
708 -- -------------------------------------------------
709 PROCEDURE print_to_log(p_message IN VARCHAR2)
710 IS
711 BEGIN
712
713 if (L_DEBUG_ENABLED='Y' and fnd_log.level_statement >= fnd_log.g_current_runtime_level) then
714 fnd_log.string(fnd_log.level_statement,'okl_bill_controll',
715 p_message );
716
717 end if;
718
719 if L_DEBUG_ENABLED = 'Y' then
720 fnd_file.put_line (fnd_file.log,p_message);
721 okl_debug_pub.logmessage(p_message);
722 end if;
723
724 -- dbms_output.put_line(p_message);
725 END print_to_log;
726
727 PROCEDURE write_to_log(
728 p_message IN VARCHAR2
729 ) IS
730 BEGIN
731 --dbms_output.put_line(p_message);
732 fnd_file.put_line(fnd_file.output, p_message);
733 END write_to_log;
734
735
736 PROCEDURE Process_Spawner (
737 errbuf OUT NOCOPY VARCHAR2,
738 retcode OUT NOCOPY NUMBER,
739 p_num_processes IN NUMBER,
740 p_start_date_from IN VARCHAR2,
741 p_start_date_to IN VARCHAR2,
742 p_contract_number IN VARCHAR2,
743 p_cust_acct_id IN NUMBER,
744 p_inv_msg IN VARCHAR2,
745 p_source IN VARCHAR2,
746 -- modified by zrehman for Bug#6788005 on 01-Feb-2008 start
747 p_ia_contract_type IN VARCHAR2,
748 p_inv_cust_acct_id IN NUMBER
749 -- modified by zrehman for Bug#6788005 on 01-Feb-2008 end
750 ) IS
751 request_id NUMBER := 0;
752
753 cursor chk_update_header_csr ( p_date date, orgId VARCHAr2 ) is -- rmunjulu R12 Forward Port missing on R12 (oklh bug 4728636)
754 -- -- bug# 5872306 (ssiruvol)
755 SELECT khr.contract_number contract_number, khr.id khr_id
756 FROM okc_k_headers_b khr, -- rmunjulu R12 Forward Port missing on R12 (oklh bug 4728636)
757 okl_k_control nbd -- rmunjulu R12 Forward Port missing on R12 (oklh bug 4728636)
758 WHERE ((p_contract_number is not null and KHR.CONTRACT_NUMBER=p_contract_number)
759 or (p_contract_number is null and KHR.CONTRACT_NUMBER=KHR.CONTRACT_NUMBER))
760 AND ((p_cust_acct_id is not null and KHR.CUST_ACCT_ID=p_cust_acct_id)
761 or (p_cust_acct_id is null and KHR.CUST_ACCT_ID=KHR.CUST_ACCT_ID))
762 AND khr.scs_code IN ('LEASE', 'LOAN')
763 AND khr.sts_code IN ( 'BOOKED','EVERGREEN','TERMINATED', 'EXPIRED') -- Bug 6472228 added - Expired status
764 AND p_source = 'BILL_STREAMS'
765 AND khr.id = nbd.khr_id -- rmunjulu R12 Forward Port missing on R12 (oklh bug 4728636)
766 -- rmunjulu R12 Forward Port missing on R12 (oklh bug 4728636) + fixes for bug 5634652 logic to check for print lead days
767 AND nbd.earliest_strm_bill_date <= (NVL(p_date, SYSDATE) + OKL_STREAM_BILLING_PVT.get_printing_lead_days(khr.id))
768 AND KHR.authoring_org_id = NVL(TO_NUMBER(orgId),-99)
769 AND nvl(p_ia_contract_type, L_CONTRACT_TYPE) = L_CONTRACT_TYPE
770 AND NOT EXISTS
771 (SELECT '1'
772 FROM OKL_PARALLEL_PROCESSES opp
773 WHERE khr.contract_number = opp.object_value
774 AND opp.object_type = 'CONTRACT'
775 AND opp.process_status in ('PENDING_ASSIGNMENT', 'ASSIGNED'))
776 --fmiao 5209209 change
777 -- rmunjulu R12 fixes - comment out Prepare Recvbles
778 /* UNION
779 --3 levels
780 SELECT khr.contract_number contract_number, khr.id khr_id
781 FROM okc_k_headers_b khr
782 WHERE id IN (
783 SELECT CHR.id
784 FROM okl_txd_ar_ln_dtls_b tld,
785 okl_txl_ar_inv_lns_b til,
786 okl_trx_ar_invoices_b tai,
787 okl_strm_type_v sty,
788 okc_k_headers_b CHR
789 WHERE tai.trx_status_code = 'SUBMITTED'
790 AND tai.khr_id = CHR.id
791 AND til.tai_id = tai.id
792 AND tld.til_id_details = til.id
793 AND sty.id = tld.sty_id
794 AND p_source = 'AR_PREPARE'
795 AND NOT EXISTS
796 (SELECT '1'
797 FROM OKL_PARALLEL_PROCESSES opp
798 WHERE CHR.contract_number = opp.object_value
799 AND opp.object_type = 'PREP_CONTRACT'
800 AND opp.process_status IN ('PENDING_ASSIGNMENT', 'ASSIGNED')) )
801 GROUP BY khr.contract_number, khr.id
802
803 UNION
804 -- 2 levels
805 SELECT khr.contract_number contract_number, khr.id khr_id
806 FROM okc_k_headers_b khr
807 WHERE id IN (
808 SELECT CHR.id
809 FROM okl_txl_ar_inv_lns_b til,
810 okl_trx_ar_invoices_b tai,
811 okl_strm_type_v sty,
812 okc_k_headers_b CHR
813 WHERE tai.trx_status_code = 'SUBMITTED'
814 AND tai.khr_id = CHR.id
815 AND til.tai_id = tai.id
816 AND til.sty_id = sty.id
817 AND p_source = 'AR_PREPARE'
818 AND NOT EXISTS
819 (SELECT *
820 FROM okl_txd_ar_ln_dtls_b tld
821 WHERE tld.til_id_details = til.id
822 )
823 AND NOT EXISTS
824 (SELECT '1'
825 FROM OKL_PARALLEL_PROCESSES opp
826 WHERE CHR.contract_number = opp.object_value
827 AND opp.object_type = 'PREP_CONTRACT'
828 AND opp.process_status IN ('PENDING_ASSIGNMENT', 'ASSIGNED')) )
829 GROUP BY khr.contract_number, khr.id
830 --fmiao 5209209 end
831 */
832 -- rmunjulu R12 Fixes modify AR Transfer
833 UNION
834 SELECT khr.contract_number contract_number, khr.id khr_id
835 FROM okc_k_headers_b khr
836 WHERE id in (
837 SELECT TAI.khr_id -- rmunjulu R12 fixes - changed to TAI
838 FROM --okl_ext_sell_invs_b xsi, -- rmunjulu R12 fixes - commented
839 --okl_xtl_sell_invs_b xls, -- rmunjulu R12 fixes - commented
840 --okl_txd_ar_ln_dtls_b tld,-- rmunjulu R12 fixes - commented
841 --okl_txl_ar_inv_lns_b til,-- rmunjulu R12 fixes - commented
842 okl_trx_ar_invoices_b tai,
843 okc_k_headers_b chr-- rmunjulu R12 fixes - commented
844 WHERE tai.TRX_STATUS_CODE = 'SUBMITTED' -- rmunjulu R12 fixes - changed to TAI + SUBMITTED
845 --AND XSI.ID = XLS.XSI_ID_DETAILS -- rmunjulu R12 fixes - commented
846 --AND xls.tld_id = tld.id -- rmunjulu R12 fixes - commented
847 --and tld.til_id_details = til.id -- rmunjulu R12 fixes - commented
848 --and til.tai_id = tai.id -- rmunjulu R12 fixes - commented
849 and tai.khr_id = chr.id
850 AND p_source = 'AR_TRANSFER'
851 AND NOT EXISTS
852 (SELECT '1'
853 FROM OKL_PARALLEL_PROCESSES opp
854 WHERE chr.contract_number = opp.object_value
855 AND opp.object_type = 'XTRX_CONTRACT'
856 AND opp.process_status in ('PENDING_ASSIGNMENT', 'ASSIGNED')) )
857 group by khr.contract_number, khr.id
858 -- rmunjulu R12 Fixes -- below select not needed as will be same as above select
859 /*
860 UNION
861 SELECT khr.contract_number contract_number, khr.id khr_id
862 FROM okc_k_headers_b khr
863 WHERE id in (
864 SELECT chr.id
865 FROM okl_ext_sell_invs_b xsi,
866 okl_xtl_sell_invs_b xls,
867 okl_txl_ar_inv_lns_b til,
868 okl_trx_ar_invoices_b tai,
869 okc_k_headers_b chr
870 WHERE XSI.TRX_STATUS_CODE = 'WORKING'
871 AND XSI.ID = XLS.XSI_ID_DETAILS
872 AND xls.til_id = til.id
873 and til.tai_id = tai.id
874 and tai.khr_id = chr.id
875 AND p_source = 'AR_TRANSFER'
876 AND NOT EXISTS
877 (SELECT '1'
878 FROM OKL_PARALLEL_PROCESSES opp
879 WHERE chr.contract_number = opp.object_value
880 AND opp.object_type = 'XTRX_CONTRACT'
881 AND opp.process_status in ('PENDING_ASSIGNMENT', 'ASSIGNED')) )
882 group by khr.contract_number, khr.id
883 */
884 -- rmunjulu R12 Fixes -- comment out Consolidation
885 /*
886 UNION
887 SELECT to_char(CUSTOMER_ID), null khr_id
888 FROM OKL_EXT_SELL_INVS_B ext
889 WHERE ext.TRX_STATUS_CODE = 'SUBMITTED'
890 AND p_source = 'CONSOLIDATION'
891 AND NOT EXISTS
892 (SELECT '1'
893 FROM OKL_PARALLEL_PROCESSES opp
894 WHERE ext.CUSTOMER_ID = to_number(opp.object_value)
895 AND opp.object_type = 'CUSTOMER'
896 AND opp.process_status in ('PENDING_ASSIGNMENT', 'ASSIGNED'));
897 */
898 -- modified by zrehman for Bug#6788005 on 01-Feb-2008 start
899 UNION
900 SELECT khr.contract_number contract_number, khr.id khr_id
901 FROM okc_k_headers_b khr,
902 okl_k_control nbd,
903 okc_k_lines_b cle
904 WHERE ((p_contract_number is not null and KHR.CONTRACT_NUMBER=p_contract_number)
905 or (p_contract_number is null and KHR.CONTRACT_NUMBER=KHR.CONTRACT_NUMBER))
906 AND ((p_inv_cust_acct_id is not null and cle.CUST_ACCT_ID IS NOT NULL AND cle.CUST_ACCT_ID = p_inv_cust_acct_id )
907 or (p_inv_cust_acct_id is null and cle.CUST_ACCT_ID = cle.CUST_ACCT_ID))
908 AND cle.dnz_chr_id = khr.id
909 AND khr.scs_code ='INVESTOR'
910 AND khr.sts_code = 'ACTIVE'
911 AND p_source = 'BILL_STREAMS'
912 AND nvl(p_ia_contract_type, L_IA_TYPE) = L_IA_TYPE
913 AND khr.id = nbd.khr_id
914 AND nbd.earliest_strm_bill_date <= (NVL(p_date, SYSDATE) + OKL_STREAM_BILLING_PVT.get_printing_lead_days(khr.id))
915 AND KHR.authoring_org_id = NVL(TO_NUMBER(orgId),-99)
916 AND NOT EXISTS
917 (SELECT '1'
918 FROM OKL_PARALLEL_PROCESSES opp
919 WHERE khr.contract_number = opp.object_value
920 AND opp.object_type = 'CONTRACT'
921 AND opp.process_status in ('PENDING_ASSIGNMENT', 'ASSIGNED'))
922 -- modified by zrehman for Bug#6788005 on 01-Feb-2008 end
923 ;
924 cursor chk_data_volume_csr(p_date_from DATE,
925 p_date_to DATE,
926 p_seq_next VARCHAR2 ) is
927 SELECT
928 opp.object_value CONTRACT_NUMBER,
929 COUNT(STE.ID) LINE_COUNT
930 FROM
931 OKL_STRM_ELEMENTS STE,
932 OKL_STREAMS STM,
933 OKL_STRM_TYPE_V STY,
934 OKL_PARALLEL_PROCESSES OPP
935 WHERE
936 OPP.ASSIGNED_PROCESS = p_seq_next AND
937 OPP.OBJECT_TYPE = 'CONTRACT' AND
938 (
939 (p_date_from is not null and STE.STREAM_ELEMENT_DATE >= p_date_from)
940 OR
941 (p_date_from is null)
942 ) AND
943 STE.STREAM_ELEMENT_DATE <= (NVL(p_date_to, SYSDATE) + OKL_STREAM_BILLING_PVT.get_printing_lead_days(opp.khr_id)) AND -- Bug 6377127
944 STE.AMOUNT <> 0 AND
945 STM.ID = STE.STM_ID AND
946 STE.DATE_BILLED IS NULL AND
947 STM.ACTIVE_YN = 'Y' AND
948 STM.SAY_CODE = 'CURR' AND
949 STY.ID = STM.STY_ID AND
950 STY.BILLABLE_YN = 'Y' AND
951 opp.khr_id = STM.KHR_ID AND
952 p_source = 'BILL_STREAMS'
953 GROUP BY opp.object_value
954 --fmiao 5209209 change
955 -- rmunjulu R12 Fixes comment out Prepare Recevbles
956 /*
957 UNION
958 -- 3 levels
959 SELECT
960 CHR.CONTRACT_NUMBER CONTRACT_NUMBER,
961 COUNT(*) LINE_COUNT
962 FROM
963 OKL_TXD_AR_LN_DTLS_B TLD,
964 OKL_TXL_AR_INV_LNS_B TIL,
965 OKL_TRX_AR_INVOICES_B TAI,
966 OKC_K_HEADERS_B CHR,
967 OKL_STRM_TYPE_V STY,
968 OKL_PARALLEL_PROCESSES OPP
969 WHERE tai.trx_status_code = 'SUBMITTED'
970 AND tai.khr_id = CHR.id
971 AND til.tai_id = tai.id
972 AND tld.til_id_details = til.id
973 AND sty.id = tld.sty_id
974 AND OPP.OBJECT_VALUE = CHR.CONTRACT_NUMBER
975 AND OPP.ASSIGNED_PROCESS = p_seq_next
976 AND OPP.OBJECT_TYPE = 'PREP_CONTRACT'
977 AND p_source = 'AR_PREPARE'
978 GROUP BY CHR.CONTRACT_NUMBER
979 UNION
980 --2 levels
981 SELECT
982 CHR.CONTRACT_NUMBER CONTRACT_NUMBER,
983 COUNT(*) LINE_COUNT
984 FROM
985 OKL_TXL_AR_INV_LNS_B TIL,
986 OKL_TRX_AR_INVOICES_B TAI,
987 OKC_K_HEADERS_B CHR,
988 OKL_STRM_TYPE_V STY,
989 OKL_PARALLEL_PROCESSES OPP
990 WHERE tai.trx_status_code = 'SUBMITTED'
991 AND tai.khr_id = CHR.id
992 AND til.tai_id = tai.id
993 AND til.sty_id = sty.id
994 AND OPP.OBJECT_VALUE = CHR.CONTRACT_NUMBER
995 AND OPP.ASSIGNED_PROCESS = p_seq_next
996 AND OPP.OBJECT_TYPE = 'PREP_CONTRACT'
997 AND p_source = 'AR_PREPARE'
998 AND NOT EXISTS
999 (SELECT *
1000 FROM okl_txd_ar_ln_dtls_b tld
1001 WHERE tld.til_id_details = til.id
1002 )
1003 GROUP BY CHR.CONTRACT_NUMBER
1004 --fmiao 5209209 end
1005 */
1006 -- rmunjulu R12 Fixes Modify AR TRANSFER
1007 UNION
1008 -- transfer 3
1009 SELECT
1010 KHR.CONTRACT_NUMBER CONTRACT_NUMBER,
1011 COUNT(*) LINE_COUNT
1012 FROM
1013 --OKL_EXT_SELL_INVS_B XSI, -- rmunjulu R12 fixes - commented
1014 --OKL_XTL_SELL_INVS_B XLS, -- rmunjulu R12 fixes - commented
1015 --OKL_TXD_AR_LN_DTLS_B TLD, -- rmunjulu R12 fixes - commented
1016 --OKL_TXL_AR_INV_LNS_B TIL, -- rmunjulu R12 fixes - commented
1017 OKL_TRX_AR_INVOICES_B TAI,
1018 OKC_K_HEADERS_B KHR,
1019 OKL_PARALLEL_PROCESSES OPP
1020 WHERE
1021 TAI.TRX_STATUS_CODE = 'SUBMITTED' AND -- rmunjulu R12 fixes - changed to TAI and SUBMITTED
1022 --XSI.ID = XLS.XSI_ID_DETAILS AND -- rmunjulu R12 fixes - commented
1023 --XLS.TLD_ID = TLD.ID AND -- rmunjulu R12 fixes - commented
1024 --TLD.TIL_ID_DETAILS = TIL.ID AND -- rmunjulu R12 fixes - commented
1025 --TIL.TAI_ID = TAI.ID AND -- rmunjulu R12 fixes - commented
1026 TAI.KHR_ID = KHR.ID AND
1027 OPP.OBJECT_VALUE = KHR.CONTRACT_NUMBER AND
1028 OPP.ASSIGNED_PROCESS = p_seq_next AND
1029 OPP.OBJECT_TYPE = 'XTRX_CONTRACT' AND
1030 p_source = 'AR_TRANSFER'
1031 GROUP BY KHR.CONTRACT_NUMBER
1032 /* -- rmunjulu R12 Fixes -- below select not needed as will be same as above select
1033 UNION
1034 -- transfer 2
1035 SELECT
1036 KHR.CONTRACT_NUMBER CONTRACT_NUMBER,
1037 COUNT(*) LINE_COUNT
1038 FROM
1039 OKL_EXT_SELL_INVS_V XSI,
1040 OKL_XTL_SELL_INVS_V XLS,
1041 OKL_TXL_AR_INV_LNS_V TIL,
1042 OKL_TRX_AR_INVOICES_V TAI,
1043 OKC_K_HEADERS_B KHR,
1044 OKL_PARALLEL_PROCESSES OPP
1045 WHERE
1046 XSI.TRX_STATUS_CODE = 'WORKING' AND
1047 XSI.ID = XLS.XSI_ID_DETAILS AND
1048 XLS.TIL_ID = TIL.ID AND
1049 TIL.TAI_ID = TAI.ID AND
1050 TAI.KHR_ID = KHR.ID AND
1051 OPP.OBJECT_VALUE = KHR.CONTRACT_NUMBER AND
1052 OPP.ASSIGNED_PROCESS = p_seq_next AND
1053 OPP.OBJECT_TYPE = 'XTRX_CONTRACT' AND
1054 p_source = 'AR_TRANSFER'
1055 GROUP BY KHR.CONTRACT_NUMBER
1056
1057 UNION
1058 */
1059 -- consolidation
1060 /* -- rmunjulu -- comment out consolidation
1061 SELECT
1062 TO_CHAR(CUSTOMER_ID) CONTRACT_NUMBER,
1063 COUNT(*)
1064 FROM
1065 OKL_EXT_SELL_INVS_B EXT,
1066 OKL_PARALLEL_PROCESSES OPP
1067 WHERE
1068 EXT.TRX_STATUS_CODE = 'SUBMITTED' AND
1069 OPP.OBJECT_VALUE = EXT.CUSTOMER_ID AND
1070 OPP.ASSIGNED_PROCESS = p_seq_next AND
1071 OPP.OBJECT_TYPE = 'CUSTOMER' AND
1072 p_source = 'CONSOLIDATION'
1073 GROUP BY CUSTOMER_ID;
1074 */
1075 ;
1076 type l_contract_rec is
1077 record (batch_number VARCHAR2(60),
1078 contract_number VARCHAR2(60),
1079 line_count NUMBER,
1080 worker_number NUMBER,
1081 khr_id NUMBER);
1082
1083 type contract_tab is table of l_contract_rec index by PLS_INTEGER;
1084
1085 type worker_load_rec is
1086 record (worker_number NUMBER,
1087 worker_load NUMBER);
1088
1089 type worker_load_tab IS TABLE OF worker_load_rec index by PLS_INTEGER;
1090
1091 type contract_list is
1092 record (contract_number VARCHAR2(60));
1093
1094 type contract_list_tab is table of contract_list index by PLS_INTEGER;
1095
1096 l_contract_list contract_list_tab;
1097 l_worker_load worker_load_tab;
1098
1099 l_contract_tab contract_tab;
1100 l_sort_tab1 contract_tab;
1101 l_temp_tab contract_tab;
1102
1103 l_int_counter INTEGER;
1104 l_max_lines NUMBER;
1105 l_init_loop BOOLEAN := TRUE;
1106 l_sort_int_counter INTEGER;
1107 l_next_highest_val NUMBER;
1108 l_lightest_worker NUMBER;
1109 l_lightest_load NUMBER;
1110 l_seq_next NUMBER;
1111 l_data_found BOOLEAN := FALSE;
1112 lp_date_from DATE;
1113 lp_date_to DATE;
1114 l_req_data VARCHAR2(10);
1115 l_req_counter NUMBER;
1116
1117 -- Bug 4546873
1118 -- Org Id and standard who columns
1119 l_last_updated_by okl_parallel_processes.last_updated_by%TYPE := Fnd_Global.USER_ID;
1120 l_last_update_login okl_parallel_processes.last_update_login%TYPE := Fnd_Global.LOGIN_ID;
1121 l_request_id okl_parallel_processes.request_id%TYPE := Fnd_Global.CONC_REQUEST_ID;
1122 l_program_id okl_parallel_processes.program_id%TYPE := Fnd_Global.CONC_PROGRAM_ID;
1123 l_org_id okl_parallel_processes.org_id%type;
1124
1125 --
1126 cursor parent_sts_csr(p_request_id NUMBER) IS
1127 --start modified abhsaxen for performance SQLID 20562749
1128 select count(*)
1129 from fnd_concurrent_requests req,
1130 fnd_concurrent_programs pgm
1131 where req.PRIORITY_REQUEST_ID = p_request_id
1132 and req.concurrent_program_id = pgm.concurrent_program_id
1133 and req.PHASE_CODE = 'C'
1134 and request_id <> p_request_id
1135 and STATUS_CODE = 'E'
1136 --end modified abhsaxen for performance SQLID 20562749
1137 ;
1138 l_child_in_error NUMBER;
1139 -- End Bug 4546873
1140
1141
1142 -- Start Bug 4581177;
1143 cursor parent_warn_sts_csr(p_request_id NUMBER) IS
1144 --start modified abhsaxen for performance SQLID 20562754
1145 select count(*)
1146 from fnd_concurrent_requests req,
1147 fnd_concurrent_programs pgm
1148 where req.priority_request_id = p_request_id
1149 and req.concurrent_program_id = pgm.concurrent_program_id
1150 and req.phase_code = 'C'
1151 and request_id <> p_request_id
1152 and status_code = 'G'
1153 --end modified abhsaxen for performance SQLID 20562754
1154 ;
1155 l_child_in_warn NUMBER;
1156 -- End Bug 4581177;
1157 orgId VARCHAr2(200);
1158
1159 cursor check_contract_type(p_contract_number VARCHAR2) IS
1160 select decode(chr.scs_code, 'INVESTOR', 'IA', 'LEASE', 'C', null)
1161 from
1162 okc_k_headers_all_b chr
1163 ,okl_k_headers khr
1164 where chr.id = khr.id
1165 and chr.scs_code in ('INVESTOR', 'LEASE')
1166 and chr.contract_number = p_contract_number;
1167
1168 l_contr_type VARCHAR2(3);
1169
1170 begin
1171
1172 -- MDokal
1173 -- 10-May-2005
1174 -- The following block has been added to control the sub-request program
1175 -- submission. It ensures that this program is not executed recurrsively.
1176 l_req_data := fnd_conc_global.request_data;
1177 -- If l_req_data has a value within this session, the program is attempting to
1178 -- run again, therefore break out of the loop.
1179
1180 -- Start Bug 4546873
1181 if l_req_data is not null then
1182 write_to_log('## 1 : '||l_req_data);
1183 l_child_in_error := 0;
1184 OPEN parent_sts_csr( l_request_id );
1185 FETCH parent_sts_csr INTO l_child_in_error;
1186 CLOSE parent_sts_csr;
1187
1188 l_child_in_warn := 0;
1189 OPEN parent_warn_sts_csr( l_request_id );
1190 FETCH parent_warn_sts_csr INTO l_child_in_warn;
1191 CLOSE parent_warn_sts_csr;
1192
1193
1194 if l_child_in_error > 0 then
1195 errbuf := 'Done, but with error!';
1196 retcode := 2;
1197 return;
1198 end if;
1199
1200 if l_child_in_warn > 0 then
1201 errbuf := 'Done, but with warning(s)!';
1202 retcode := 1;
1203 return;
1204 end if;
1205
1206 errbuf := 'Done!';
1207 retcode := 0;
1208 return;
1209
1210 end if;
1211 -- End Bug 4546873
1212
1213 -- MDokal
1214 -- 10-May-2005
1215 -- If p_num_processes is 0 or 1 then no need to go through the algorithm.
1216 if nvl(p_num_processes,0) = 0 then
1217 write_to_log('No workers specified');
1218 return;
1219 end if;
1220
1221 /* MDOKAL 28-JUL-2005
1222 if p_num_processes = 1 and p_source = 'BILL_STREAMS' then
1223 request_id := FND_REQUEST.SUBMIT_REQUEST(
1224 application => 'OKL',
1225 program => 'OKL_STREAM_BILLING',
1226 sub_request => FALSE,
1227 argument1 => p_start_date_from,
1228 argument2 => p_start_date_to,
1229 argument3 => p_contract_number,
1230 argument4 => p_cust_acct_id,
1231 argument5 => NULL
1232 );
1233 if (request_id = 0) then
1234 errbuf := fnd_message.get;
1235 retcode := 2;
1236 end if;
1237 return;
1238 end if;
1239 */
1240 -- modified by zrehman for Bug#6788005 on 01-Feb-2008 start
1241 -- put validations for Investor Agreement Number or Contract number depending on Contract/Investor Agreement type
1242 IF p_ia_contract_type IS NOT NULL THEN
1243 IF p_contract_number IS NOT NULL THEN
1244 OPEN check_contract_type(p_contract_number);
1245 FETCH check_contract_type INTO l_contr_type;
1246 CLOSE check_contract_type;
1247 IF p_ia_contract_type = 'CONTRACT' AND l_contr_type IS NOT NULL AND l_contr_type = 'IA' THEN
1248 write_to_log('Please enter valid Contract Number');
1249 return;
1250 ELSIF p_ia_contract_type = 'IA' AND l_contr_type IS NOT NULL AND l_contr_type = 'C' THEN
1251 write_to_log('Please enter valid Investor Agreement Number');
1252 return;
1253 ELSIF l_contr_type IS NULL THEN
1254 write_to_log('Please enter valid Contract or Investor Agreement Number');
1255 return;
1256 END IF;
1257 END IF;
1258 END IF;
1259 -- modified by zrehman for Bug#6788005 on 01-Feb-2008 end
1260 lp_date_from := FND_DATE.CANONICAL_TO_DATE(p_start_date_from);
1261 lp_date_to := FND_DATE.CANONICAL_TO_DATE(p_start_date_to);
1262
1263 -- Start Bug 4520466
1264 if lp_date_to is null then
1265 lp_date_to := sysdate;
1266 end if;
1267 -- End Bug 4520466
1268
1269 l_int_counter := 0;
1270 l_max_lines := 0;
1271
1272 -- Select sequence for marking processes
1273 select okl_opp_seq.nextval
1274 into l_seq_next
1275 from dual ;
1276
1277 -- Bug 4546873
1278 g_opp_seq_num := to_char(l_seq_next);
1279 -- End Bug 4546873
1280
1281 WRITE_TO_LOG('p_source: '||p_source);
1282 WRITE_TO_LOG('p_seq: '||l_seq_next);
1283
1284 -- mark records for processing
1285
1286 --dbms_application_info.READ_CLIENT_INFO(orgId); -- -- bug# 5872306 (ssiruvol)
1287 orgId := mo_global.get_current_org_id();
1288 for chk_update_header_csr_rec in chk_update_header_csr (lp_date_to, orgId) loop -- rmunjulu R12 Forward Port missing on R12 (oklh bug 4728636)
1289
1290 INSERT INTO OKL_PARALLEL_PROCESSES
1291 (
1292 object_type, object_value, assigned_process, process_status, start_date, khr_id,
1293 ORG_ID,CREATED_BY,CREATION_DATE,LAST_UPDATE_DATE,LAST_UPDATED_BY,LAST_UPDATE_LOGIN,
1294 REQUEST_ID,PROGRAM_ID,PROGRAM_UPDATE_DATE
1295 )
1296 VALUES
1297 (decode (p_source,'BILL_STREAMS','CONTRACT',
1298 'AR_TRANSFER','XTRX_CONTRACT',
1299 'CONSOLIDATION','CUSTOMER',
1300 --fmiao 5209209 change
1301 'AR_PREPARE','PREP_CONTRACT',NULL), -- fmiao 5209209 end
1302 chk_update_header_csr_rec.contract_number, to_char(l_seq_next),'PENDING_ASSIGNMENT', sysdate, chk_update_header_csr_rec.khr_id,
1303 l_org_id,l_last_updated_by,sysdate,sysdate,l_last_updated_by,l_last_update_login,
1304 l_request_id,l_program_id,sysdate
1305 );
1306 COMMIT;
1307
1308 l_data_found := TRUE;
1309 end loop;
1310
1311 if l_data_found then
1312
1313 -- Start Bug 4520466
1314 if ( p_source = 'BILL_STREAMS' ) then
1315
1316 -- Set initial volume
1317 update okl_parallel_processes opp
1318 set volume = (select count(*)
1319 from okc_k_lines_b chl
1320 where chl.dnz_chr_id = opp.khr_id)
1321 WHERE OPP.ASSIGNED_PROCESS = TO_CHAR(l_seq_next); -- bug# 5872306 (ssiruvol)
1322 commit;
1323
1324 -- update volume with number of months
1325 update okl_parallel_processes opp
1326 -- set volume = volume* (select ceil((lp_date_to - nbd.earliest_strm_bill_date)/30) -- rmunjulu R12 Forward Port bug 5634652
1327 SET volume = volume* (SELECT CEIL((lp_date_to
1328 -- rmunjulu R12 Forward Port bug 5634652 logic to check print lead days
1329 + OKL_STREAM_BILLING_PVT.get_printing_lead_days(opp.khr_id)
1330 -- rmunjulu R12 Forward Port bug 5710903 Add ONE to account for scenario where these dates are same
1331 - nbd.earliest_strm_bill_date + 1)/30)
1332 from okl_k_control nbd
1333 where nbd.khr_id = opp.khr_id)
1334
1335 WHERE OPP.ASSIGNED_PROCESS = TO_CHAR(l_seq_next); -- bug# 5872306 (ssiruvol)
1336 commit;
1337
1338 end if;
1339 -- End Bug 4520466
1340
1341 for chk_data_volume_csr_rec in chk_data_volume_csr(lp_date_from, lp_date_to, l_seq_next) loop
1342
1343
1344 l_int_counter := l_int_counter + 1;
1345
1346 if l_init_loop then -- initialize minimum and maximum lines
1347 l_init_loop := FALSE;
1348 l_max_lines := chk_data_volume_csr_rec.line_count;
1349 end if;
1350
1351 l_contract_tab(l_int_counter).contract_number := chk_data_volume_csr_rec.contract_number;
1352 l_contract_tab(l_int_counter).line_count := chk_data_volume_csr_rec.line_count;
1353 if chk_data_volume_csr_rec.line_count > l_max_lines then
1354 l_max_lines := chk_data_volume_csr_rec.line_count;
1355 end if;
1356 end loop;
1357
1358 -- reset, ready for use again
1359 l_init_loop := TRUE;
1360
1361 if l_int_counter = 0 then
1362 write_to_log('No Data Found for criteria passed ');
1363 end if;
1364
1365 -- find the maximum line count from the original table and delete it
1366 -- put this as the first element of the new sorted table
1367 l_sort_int_counter := 0;
1368 for i in 1..l_int_counter loop
1369 if l_contract_tab(i).line_count = l_max_lines then
1370 l_sort_int_counter := l_sort_int_counter+1;
1371 l_sort_tab1(l_sort_int_counter).contract_number := l_contract_tab(i).contract_number;
1372 l_sort_tab1(l_sort_int_counter).line_count := l_contract_tab(i).line_count;
1373 l_contract_tab.DELETE(i);
1374 end if;
1375 end loop;
1376
1377 -- start sorting
1378 if l_contract_tab.FIRST is not null then
1379
1380 for i in 1..l_contract_tab.COUNT loop
1381 -- find the next highest value in original table
1382 for i in 1..l_contract_tab.LAST loop
1383 if l_init_loop then
1384 if l_contract_tab.EXISTS(i) then
1385 l_next_highest_val := l_contract_tab(i).line_count;
1386 l_init_loop := FALSE;
1387 end if;
1388 end if;
1389 if l_contract_tab.EXISTS(i) and l_contract_tab(i).line_count > l_next_highest_val then
1390 l_next_highest_val := l_contract_tab(i).line_count;
1391 end if;
1392 end loop;
1393
1394 -- reset flag, ready for use again
1395 l_init_loop := TRUE;
1396 -- continue populating sort table in order
1397 for i in 1..l_contract_tab.LAST loop
1398 if l_contract_tab.EXISTS(i) and l_contract_tab(i).line_count = l_next_highest_val then
1399 l_sort_int_counter := l_sort_int_counter+1;
1400 l_sort_tab1(l_sort_int_counter).contract_number := l_contract_tab(i).contract_number;
1401 l_sort_tab1(l_sort_int_counter).line_count := l_contract_tab(i).line_count;
1402 l_contract_tab.DELETE(i);
1403 end if;
1404 end loop;
1405 exit when l_contract_tab.LAST is null;
1406 end loop;
1407 end if; -- end sorting
1408
1409 -- begin processing load for workers
1410 for i in 1..p_num_processes loop -- put all workers into a table
1411 l_worker_load(i).worker_number := i;
1412 l_worker_load(i).worker_load := 0; -- initialize load with zero
1413 end loop;
1414
1415 if p_num_processes > 0 then
1416
1417 l_lightest_worker := 1;
1418 -- loop through the sorted table and ensure each contract has a worker
1419 for i in 1..l_sort_tab1.COUNT loop
1420 l_sort_tab1(i).worker_number := l_lightest_worker;
1421 -- put current contract into the lightest worker
1422 if l_worker_load.EXISTS(l_lightest_worker) then
1423 l_worker_load(l_lightest_worker).worker_load := l_worker_load(l_lightest_worker).worker_load + l_sort_tab1(i).line_count;
1424 end if;
1425 -- default the lighest load with the first element as a starting point
1426 if l_worker_load.EXISTS(1) then
1427 l_lightest_load := l_worker_load(1).worker_load;
1428 l_lightest_worker := l_worker_load(1).worker_number;
1429 -- logic to find lightest load
1430 for i in 1..l_worker_load.COUNT loop
1431 if (l_worker_load(i).worker_load = 0) or (l_worker_load(i).worker_load < l_lightest_load) then
1432 l_lightest_load := l_worker_load(i).worker_load;
1433 l_lightest_worker := l_worker_load(i).worker_number;
1434 end if;
1435 end loop;
1436 end if;
1437 end loop;
1438 end if;
1439
1440
1441 l_sort_int_counter := 0;
1442
1443 for j in l_worker_load.FIRST..l_worker_load.LAST loop
1444 if l_sort_tab1.count > 0 THEN
1445 for i in l_sort_tab1.FIRST..l_sort_tab1.LAST loop
1446 if l_sort_tab1.EXISTS(i) and(l_sort_tab1(i).worker_number = l_worker_load(j).worker_number )then
1447
1448 IF p_source = 'BILL_STREAMS' THEN
1449
1450 UPDATE OKL_PARALLEL_PROCESSES
1451 SET
1452 assigned_process = l_seq_next||'-'||l_sort_tab1(i).worker_number,
1453 volume = l_sort_tab1(i).line_count,
1454 process_status = 'ASSIGNED'
1455 WHERE object_Type = 'CONTRACT'
1456 AND object_value = l_sort_tab1(i).contract_number
1457 AND process_status = 'PENDING_ASSIGNMENT';
1458 --fmiao 5209209 change
1459 -- rmunjulu R12 Fixes - comment out PRepare recvbles
1460 /*
1461 ELSIF p_source = 'AR_PREPARE' THEN
1462
1463 UPDATE OKL_PARALLEL_PROCESSES
1464 SET
1465 assigned_process = l_seq_next||'-'||l_sort_tab1(i).worker_number,
1466 volume = l_sort_tab1(i).line_count,
1467 process_status = 'ASSIGNED'
1468 WHERE object_Type = 'PREP_CONTRACT'
1469 AND object_value = l_sort_tab1(i).contract_number
1470 AND process_status = 'PENDING_ASSIGNMENT';
1471 -- fmiao 5209209 end
1472 */
1473 ELSIF p_source = 'AR_TRANSFER' THEN
1474
1475 UPDATE OKL_PARALLEL_PROCESSES
1476 SET
1477 assigned_process = l_seq_next||'-'||l_sort_tab1(i).worker_number,
1478 volume = l_sort_tab1(i).line_count,
1479 process_status = 'ASSIGNED'
1480 WHERE object_Type = 'XTRX_CONTRACT'
1481 AND object_value = l_sort_tab1(i).contract_number
1482 AND process_status = 'PENDING_ASSIGNMENT';
1483 -- rmunjulu R12 Fixes comment out consolidation
1484 /*
1485 ELSIF p_source = 'CONSOLIDATION' THEN
1486
1487 UPDATE OKL_PARALLEL_PROCESSES
1488 SET
1489 assigned_process = l_seq_next||'-'||l_sort_tab1(i).worker_number,
1490 volume = l_sort_tab1(i).line_count,
1491 process_status = 'ASSIGNED'
1492 WHERE object_Type = 'CUSTOMER'
1493 AND object_value = l_sort_tab1(i).contract_number
1494 AND process_status = 'PENDING_ASSIGNMENT';
1495 */
1496 ELSE
1497 NULL;
1498 END IF;
1499
1500
1501 COMMIT;
1502 l_sort_tab1.DELETE(i);
1503 end if;
1504 end loop; -- Sort Tab Loop
1505 end if; -- Sort tab count check
1506 end loop; -- Worker Tab Loop
1507
1508 for j in l_worker_load.FIRST..l_worker_load.LAST loop
1509
1510 -- MDokal
1511 -- 28-Jul-2005
1512 -- Do not spawn a worker if theres no data to process
1513 -- This occurs if more workers are requested and the
1514 -- distribution of data does not utilize them all
1515 IF l_worker_load(j).worker_load > 0 THEN
1516
1517 IF p_source = 'BILL_STREAMS' THEN
1518
1519 -- MDokal
1520 -- 10-May-2005
1521 -- Default processing for managing sub-requests, starts here
1522 l_req_data := fnd_conc_global.request_data;
1523 if (l_req_data is not null) then
1524 l_req_counter := l_req_counter + to_number(l_req_data);
1525 else
1526 l_req_counter := 1;
1527 end if;
1528
1529 if l_req_counter < (p_num_processes+1) then
1530 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
1531 request_id := FND_REQUEST.SUBMIT_REQUEST(
1532 application => 'OKL',
1533 program => 'OKL_STREAM_BILLING',
1534 sub_request => TRUE,
1535 argument1 => p_ia_contract_type,
1536 argument2 => p_start_date_from,
1537 argument3 => p_start_date_to,
1538 argument4 => p_contract_number,
1539 argument5 => p_cust_acct_id,
1540 -- modified by zrehman for Bug#6788005 on 01-Feb-2008 start
1541 argument6 => p_inv_cust_acct_id,
1542 -- modified by zrehman for Bug#6788005 on 01-Feb-2008 end
1543 argument7 => l_seq_next||'-'||j
1544 );
1545
1546 write_to_log('Launching Process '||l_seq_next||'-'||j ||' with Request ID '||request_id);
1547
1548 if (request_id = 0) then
1549 -- If request submission failed, exit with error
1550 errbuf := fnd_message.get;
1551 retcode := 2;
1552 else
1553 -- Set the globals to put the master into PAUSED mode
1554 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
1555 request_data => to_char(1));
1556 errbuf := 'Sub-Request submitted!';
1557 retcode := 0;
1558 end if;
1559 end if;
1560 -- MDokal
1561 -- 10-May-2005
1562 -- Default processing for managing sub-requests, ends here
1563 --fmiao 5209209 change
1564
1565 -- rmunjulu R12 Fixes comment out Prepare Recvbles
1566 /*
1567 ELSIF p_source = 'AR_PREPARE' THEN
1568
1569 l_req_data := Fnd_Conc_Global.request_data;
1570 IF (l_req_data IS NOT NULL) THEN
1571 l_req_counter := l_req_counter + TO_NUMBER(l_req_data);
1572 ELSE
1573 l_req_counter := 1;
1574 END IF;
1575
1576
1577 IF l_req_counter < (p_num_processes+1) THEN
1578 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
1579 request_id := Fnd_Request.SUBMIT_REQUEST(
1580 application => 'OKL',
1581 program => 'OKL_INTERNAL_TO_EXTERNAL',
1582 sub_request => TRUE,
1583 argument1 => p_contract_number,
1584 argument2 => l_seq_next||'-'||j
1585 );
1586
1587 IF (request_id = 0) THEN
1588 -- If request submission failed, exit with error
1589 errbuf := Fnd_Message.get;
1590 retcode := 2;
1591 ELSE
1592 -- Set the globals to put the master into PAUSED mode
1593 Fnd_Conc_Global.set_req_globals(conc_status => 'PAUSED',
1594 request_data => TO_CHAR(1));
1595 errbuf := 'Sub-Request submitted!';
1596 retcode := 0;
1597 END IF;
1598 END IF;
1599 --fmiao 5209209 end
1600 */
1601 ELSIF p_source = 'AR_TRANSFER' THEN
1602
1603 -- MDokal
1604 -- 10-May-2005
1605 -- Default processing for managing sub-requests, starts here
1606 l_req_data := fnd_conc_global.request_data;
1607 if (l_req_data is not null) then
1608 l_req_counter := l_req_counter + to_number(l_req_data);
1609 else
1610 l_req_counter := 1;
1611 end if;
1612
1613 if l_req_counter < (p_num_processes+1) then
1614 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
1615 request_id := FND_REQUEST.SUBMIT_REQUEST(
1616 application => 'OKL',
1617 program => 'OKL_ARINTF',
1618 sub_request => TRUE,
1619 argument1 => NULL,
1620 argument2 => NULL,
1621 argument3 => l_seq_next||'-'||j
1622 );
1623
1624 write_to_log('Launching Process '||l_seq_next||'-'||j ||' with Request ID '||request_id);
1625
1626 if (request_id = 0) then
1627 -- If request submission failed, exit with error
1628 errbuf := fnd_message.get;
1629 retcode := 2;
1630 else
1631 -- Set the globals to put the master into PAUSED mode
1632 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
1633 request_data => to_char(1));
1634 errbuf := 'Sub-Request submitted!';
1635 retcode := 0;
1636 end if;
1637 end if;
1638 -- MDokal
1639 -- 10-May-2005
1640 -- Default processing for managing sub-requests, ends here
1641 -- rmunjulu R12 Fixes comment out consolidation
1642 /*
1643 ELSIF p_source = 'CONSOLIDATION' THEN
1644
1645 -- MDokal
1646 -- 10-May-2005
1647 -- Default processing for managing sub-requests, starts here
1648 l_req_data := fnd_conc_global.request_data;
1649 if (l_req_data is not null) then
1650 l_req_counter := l_req_counter + to_number(l_req_data);
1651 else
1652 l_req_counter := 1;
1653 end if;
1654
1655 if l_req_counter < (p_num_processes+1) then
1656 FND_REQUEST.set_org_id(mo_global.get_current_org_id); --MOAC- Concurrent request
1657 request_id := FND_REQUEST.SUBMIT_REQUEST(
1658 application => 'OKL',
1659 program => 'OKL_CONS_BILL',
1660 sub_request => TRUE,
1661 argument1 => p_inv_msg,
1662 argument2 => l_seq_next||'-'||j
1663 );
1664
1665 write_to_log('Launching Process '||l_seq_next||'-'||j ||' with Request ID '||request_id);
1666
1667 if (request_id = 0) then
1668 -- If request submission failed, exit with error
1669 errbuf := fnd_message.get;
1670 retcode := 2;
1671 else
1672 -- Set the globals to put the master into PAUSED mode
1673 fnd_conc_global.set_req_globals(conc_status => 'PAUSED',
1674 request_data => to_char(1));
1675 errbuf := 'Sub-Request submitted!';
1676 retcode := 0;
1677 end if;
1678 end if;
1679 -- MDokal
1680 -- 10-May-2005
1681 -- Default processing for managing sub-requests, ends here
1682 */
1683 ELSE
1684 NULL;
1685 END IF;
1686 END IF; -- check worker load before spwaning
1687 end loop;
1688
1689 else
1690 write_to_log('No workers assigned due to no data found for prcocesing');
1691 end if; -- l_data_found
1692
1693 -- clean up
1694 -- Delete records from in chk_update_header_csr that were unassigned
1695 DELETE OKL_PARALLEL_PROCESSES
1696 WHERE process_status = 'PENDING_ASSIGNMENT'
1697 AND assigned_process = to_char(l_seq_next);
1698
1699 -- Start Bug 4520466
1700 commit;
1701
1702 DELETE OKL_PARALLEL_PROCESSES
1703 WHERE volume = 0
1704 AND assigned_process like to_char(l_seq_next)||'%';
1705 -- End Bug 4520466
1706
1707 COMMIT;
1708
1709 exception
1710 when others then
1711 write_to_log('Unhandled Exception '||sqlerrm);
1712
1713 DELETE OKL_PARALLEL_PROCESSES
1714 WHERE assigned_process = to_char(l_seq_next);
1715 COMMIT;
1716
1717 END Process_Spawner;
1718
1719 PROCEDURE BILL_STREAMS_MASTER (
1720 errbuf OUT NOCOPY VARCHAR2,
1721 retcode OUT NOCOPY NUMBER,
1722 p_ia_contract_type IN VARCHAR2,
1723 p_start_date_from IN VARCHAR2,
1724 p_start_date_to IN VARCHAR2,
1725 p_contract_number IN VARCHAR2,
1726 p_cust_acct_id IN NUMBER,
1727 p_inv_cust_acct_id IN NUMBER,
1728 p_num_processes IN NUMBER
1729 )
1730 IS
1731
1732 BEGIN
1733 WRITE_TO_LOG('p_num_processes: '||p_num_processes);
1734 WRITE_TO_LOG('p_start_date_from: '||p_start_date_from);
1735 WRITE_TO_LOG('p_start_date_to: '||p_start_date_to);
1736 WRITE_TO_LOG('p_cust_acct_id: '||p_cust_acct_id);
1737
1738 Process_Spawner (
1739 errbuf => errbuf,
1740 retcode => retcode,
1741 p_num_processes => NVL(p_num_processes,1),
1742 p_start_date_from => p_start_date_from,
1743 p_start_date_to => p_start_date_to,
1744 p_contract_number => p_contract_number,
1745 p_cust_acct_id => p_cust_acct_id,
1746 p_source => 'BILL_STREAMS',
1747 p_ia_contract_type => p_ia_contract_type,
1748 p_inv_cust_acct_id => p_inv_cust_acct_id);
1749 EXCEPTION
1750 WHEN OTHERS THEN
1751 WRITE_TO_LOG('UNHANDLED EXCEPTION '||SQLERRM);
1752
1753 DELETE OKL_PARALLEL_PROCESSES
1754 WHERE assigned_process = to_char(g_opp_seq_num);
1755 COMMIT;
1756
1757 END BILL_STREAMS_MASTER;
1758
1759 PROCEDURE AR_TRANSFER_MASTER (
1760 errbuf OUT NOCOPY VARCHAR2,
1761 retcode OUT NOCOPY NUMBER,
1762 p_start_date_from IN VARCHAR2,
1763 p_start_date_to IN VARCHAR2,
1764 p_num_processes IN NUMBER
1765 )
1766 IS
1767
1768 BEGIN
1769
1770 Process_Spawner (
1771 errbuf => errbuf,
1772 retcode => retcode,
1773 p_num_processes => NVL(p_num_processes,1),
1774 p_start_date_from => p_start_date_from,
1775 p_start_date_to => p_start_date_to,
1776 p_cust_acct_id => NULL,
1777 p_source => 'AR_TRANSFER',
1778 p_ia_contract_type => NULL,
1779 p_inv_cust_acct_id => NULL);
1780 EXCEPTION
1781 WHEN OTHERS THEN
1782 WRITE_TO_LOG('UNHANDLED EXCEPTION '||SQLERRM);
1783
1784 DELETE OKL_PARALLEL_PROCESSES
1785 WHERE assigned_process = to_char(g_opp_seq_num);
1786 COMMIT;
1787
1788 END AR_TRANSFER_MASTER;
1789
1790
1791
1792 PROCEDURE OKL_CONS_MASTER ( errbuf OUT NOCOPY VARCHAR2,
1793 retcode OUT NOCOPY NUMBER,
1794 p_inv_msg IN VARCHAR2,
1795 p_num_processes IN NUMBER
1796 )
1797 IS
1798
1799 BEGIN
1800 /* -- rmunjulu R12 Fixes -- comment out consolidation
1801 Process_Spawner (
1802 errbuf => errbuf,
1803 retcode => retcode,
1804 p_num_processes => NVL(p_num_processes,1),
1805 p_inv_msg => p_inv_msg,
1806 p_source => 'CONSOLIDATION');
1807 */
1808 NULL; -- rmunjulu R12 Fixes
1809 EXCEPTION
1810 WHEN OTHERS THEN
1811 WRITE_TO_LOG('UNHANDLED EXCEPTION '||SQLERRM);
1812
1813 DELETE OKL_PARALLEL_PROCESSES
1814 WHERE assigned_process = to_char(g_opp_seq_num);
1815 COMMIT;
1816
1817 END OKL_CONS_MASTER;
1818
1819 --fmiao 5209209 change
1820 PROCEDURE PREPARE_RECEIVABLES_MASTER (
1821 errbuf OUT NOCOPY VARCHAR2,
1822 retcode OUT NOCOPY NUMBER,
1823 p_num_processes IN NUMBER
1824 )
1825 IS
1826
1827 BEGIN
1828 -- rmunjulu R12 Fixes -- comment out Prepare Recvbles
1829 /*
1830 WRITE_TO_LOG('p_num_processes: '||p_num_processes);
1831
1832 Process_Spawner (
1833 errbuf => errbuf,
1834 retcode => retcode,
1835 p_num_processes => NVL(p_num_processes,1),
1836 p_source => 'AR_PREPARE');
1837 */
1838 NULL; -- rmunjulu R12 Fixes, added
1839 EXCEPTION
1840 WHEN OTHERS THEN
1841 WRITE_TO_LOG('UNHANDLED EXCEPTION '||SQLERRM);
1842
1843 DELETE OKL_PARALLEL_PROCESSES
1844 WHERE assigned_process = TO_CHAR(g_opp_seq_num);
1845 COMMIT;
1846
1847 END PREPARE_RECEIVABLES_MASTER;
1848
1849 --fmiao 5209209 change end
1850
1851 PROCEDURE process_break(
1852 p_contract_number IN VARCHAR2,
1853 p_commit IN VARCHAR2,
1854 saved_bill_rec IN OUT NOCOPY saved_bill_rec_type,
1855 l_update_tbl IN OUT NOCOPY update_tbl_type)
1856 IS
1857
1858 l_old_cnr_id NUMBER;
1859 l_old_lln_id NUMBER;
1860 l_cnr_amount okl_cnsld_ar_hdrs_v.amount%TYPE;
1861 l_lln_amount okl_cnsld_ar_lines_v.amount%TYPE;
1862
1863 BEGIN
1864
1865 -- rmunjulu R12 Fixes -- comment out entire code and put NULL
1866 NULL;
1867 /*
1868 IF (L_DEBUG_ENABLED='Y' and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1869 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'okl_cons_bill'
1870 ,'Process_Break Begin(+)');
1871 END IF;
1872
1873 -- ------------------------------------
1874 -- Start header break detection logic
1875 -- ------------------------------------
1876
1877 -- If there was no error processing any records then
1878 IF l_update_tbl.COUNT > 0 THEN
1879 IF saved_bill_rec.l_overall_status IS NULL THEN
1880 l_old_cnr_id := -9;
1881 --PRINT_TO_LOG( 'Updating Consolidated Invoice Header');
1882 -- PRINT_TO_LOG( 'Done updating Consolidated Invoice Line');
1883
1884 PRINT_TO_LOG( 'Updating External Transaction Header');
1885 FOR m in l_update_tbl.FIRST..l_update_tbl.LAST LOOP
1886
1887 PRINT_TO_LOG( 'l_update_tbl.cnr_id '||l_update_tbl(m).cnr_id);
1888 PRINT_TO_LOG( 'l_update_tbl.cons_inv_number '||l_update_tbl(m).cons_inv_number);
1889 PRINT_TO_LOG( 'l_update_tbl.lln_id '||l_update_tbl(m).lln_id);
1890 PRINT_TO_LOG( 'l_update_tbl.lsm_id '||l_update_tbl(m).lsm_id);
1891 PRINT_TO_LOG( 'l_update_tbl.asset_number '||l_update_tbl(m).asset_number);
1892 PRINT_TO_LOG( 'l_update_tbl.invoice_format '||l_update_tbl(m).invoice_format);
1893 PRINT_TO_LOG( 'l_update_tbl.line_type '||l_update_tbl(m).line_type);
1894 PRINT_TO_LOG( 'l_update_tbl.sty_name '||l_update_tbl(m).sty_name);
1895 PRINT_TO_LOG( 'l_update_tbl.contract_number '||l_update_tbl(m).contract_number);
1896 PRINT_TO_LOG( 'l_update_tbl.lsm_amount '||l_update_tbl(m).lsm_amount);
1897 PRINT_TO_LOG( 'l_update_tbl.xsi_id '||l_update_tbl(m).xsi_id);
1898 PRINT_TO_LOG( 'l_update_tbl.xls_id '||l_update_tbl(m).xls_id);
1899
1900 g_xsi_counter := g_xsi_counter + 1;
1901 g_xsi_tbl(g_xsi_counter).id :=l_update_tbl(m).xsi_id;
1902 g_xsi_tbl(g_xsi_counter).xtrx_invoice_pull_yn := 'Y';
1903 IF p_contract_number IS NULL THEN
1904 g_xsi_tbl(g_xsi_counter).trx_status_code := 'WORKING';
1905 ELSE
1906 g_xsi_tbl(g_xsi_counter).trx_status_code := 'ENTERED';
1907 END IF;
1908
1909 g_xsi_tl_counter := g_xsi_tl_counter + 1;
1910 g_xsi_tl_tbl(g_xsi_tl_counter).id :=l_update_tbl(m).xsi_id;
1911 -- mdokal, Bug 4442702
1912 g_xsi_tl_tbl(g_xsi_tl_counter).xtrx_cons_invoice_number := l_update_tbl(m).cons_inv_number;
1913 g_xsi_tl_tbl(g_xsi_tl_counter).xtrx_format_type := l_update_tbl(m).invoice_format;
1914 g_xsi_tl_tbl(g_xsi_tl_counter).xtrx_private_label := l_update_tbl(m).private_label;
1915
1916 g_xls_counter := g_xls_counter + 1;
1917 g_xls_tbl(g_xls_counter).id :=l_update_tbl(m).xls_id;
1918 g_xls_tbl(g_xls_counter).lsm_id :=l_update_tbl(m).LSM_ID;
1919 g_xls_tbl(g_xls_counter).xtrx_cons_stream_id :=l_update_tbl(m).lsm_id;
1920
1921 g_xls_tl_counter := g_xls_tl_counter + 1;
1922 g_xls_tl_tbl(g_xls_tl_counter).id :=l_update_tbl(m).xls_id;
1923 g_xls_tl_tbl(g_xls_tl_counter).xtrx_contract := l_update_tbl(m).contract_number;
1924 g_xls_tl_tbl(g_xls_tl_counter).xtrx_asset := l_update_tbl(m).asset_number;
1925 g_xls_tl_tbl(g_xls_tl_counter).xtrx_stream_type := l_update_tbl(m).sty_name;
1926 g_xls_tl_tbl(g_xls_tl_counter).xtrx_stream_group := l_update_tbl(m).line_type;
1927
1928 END LOOP;
1929 END IF;
1930 l_update_tbl.DELETE;
1931 END IF;
1932 PRINT_TO_LOG( 'Done updating External Transaction Header');
1933
1934 -- PRINT_TO_LOG( 'Updating External Transaction Line');
1935
1936 -- ------------------------------------
1937 -- End header break detection logic
1938 -- ------------------------------------
1939
1940 -- IF saved_bill_rec.l_commit_cnt > G_Commit_Max THEN
1941 -- IF FND_API.To_Boolean( p_commit ) THEN
1942 -- COMMIT;
1943 -- END IF;
1944 -- saved_bill_rec.l_commit_cnt := 0;
1945 -- END IF;
1946
1947 IF (L_DEBUG_ENABLED='Y' and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1948 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'okl_cons_bill'
1949 ,'Process_Break End(-)');
1950 END IF;
1951 */
1952 EXCEPTION
1953 WHEN OTHERS THEN
1954 IF (L_DEBUG_ENABLED='Y' and FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1955 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'okl_cons_bill',
1956 'EXCEPTION (OTHERS) :'||SQLERRM);
1957 END IF;
1958
1959 PRINT_TO_LOG( 'EXCEPTION in Procedure Process_Break: '||SQLERRM);
1960 END process_break;
1961
1962 FUNCTION get_invoice_group(p_khr_id NUMBER)
1963 RETURN VARCHAR2 IS
1964 CURSOR grp_csr ( cp_khr_id NUMBER ) IS
1965 select RULE_INFORMATION1
1966 from okc_rule_groups_v rgp,
1967 okc_rules_v rul
1968 where rgp.dnz_chr_id = cp_khr_id AND
1969 rgp.chr_id = rgp.dnz_chr_id AND
1970 rgp.id = rul.rgp_id AND
1971 rgp.cle_id IS NULL AND
1972 rgp.rgd_code = 'LABILL' AND
1973 rul.rule_information_category = 'LAINVD';
1974
1975 l_grp okc_rules_v.rule_information1%type:= 'NONE';
1976
1977 BEGIN
1978
1979 OPEN grp_csr(p_khr_id);
1980 FETCH grp_csr INTO l_grp;
1981 CLOSE grp_csr;
1982
1983 return l_grp;
1984
1985 END get_invoice_group;
1986
1987 PROCEDURE create_new_invoice(
1988 p_ibt_id IN NUMBER,
1989 p_ixx_id IN NUMBER,
1990 p_currency_code IN VARCHAR2,
1991 p_irm_id IN NUMBER,
1992 p_inf_id IN NUMBER,
1993 p_set_of_books_id IN NUMBER,
1994 p_private_label IN VARCHAR2,
1995 p_date_consolidated IN DATE,
1996 p_org_id IN NUMBER,
1997 p_legal_entity_id IN NUMBER, -- for LE Uptake project 08-11-2006
1998 p_last_rec IN BOOLEAN,
1999 x_cnr_id OUT NOCOPY NUMBER,
2000 x_cons_inv_num OUT NOCOPY VARCHAR2
2001 )
2002 IS
2003
2004 x_cnrv_rec Okl_Cnr_Pvt.cnrv_rec_type;
2005 x_cnrv_tbl Okl_Cnr_Pvt.cnrv_tbl_type;
2006
2007 p_cnrv_rec Okl_Cnr_Pvt.cnrv_rec_type;
2008 p_cnrv_tbl Okl_Cnr_Pvt.cnrv_tbl_type;
2009
2010 p_imav_rec Okl_ima_pvt.imav_rec_type;
2011 x_imav_rec Okl_ima_pvt.imav_rec_type;
2012
2013
2014 p_api_version NUMBER := 1.0;
2015 p_init_msg_list VARCHAR2(1) := Okl_Api.g_false;
2016 x_return_status VARCHAR2(1);
2017 x_msg_count NUMBER;
2018 x_msg_data VARCHAR2(2000);
2019 -- For automatic generation of sequence numbers from
2020 -- the database
2021 l_Invoice_Number NUMBER := '';
2022 l_document_category VARCHAR2(100):= 'OKL Lease Receipt Invoices';
2023 l_application_id NUMBER(3) := 540 ;
2024 x_dbseqnm VARCHAR2(100):= NULL;
2025 x_dbseqid NUMBER;
2026
2027 CURSOR msg_csr IS
2028 SELECT id,
2029 priority,
2030 pkg_name,
2031 proc_name
2032 FROM okl_invoice_mssgs_v;
2033
2034 l_save_priority okl_invoice_mssgs_v.priority%TYPE;
2035 l_save_ims_id okl_invoice_mssgs_v.id%TYPE;
2036
2037 l_priority okl_invoice_mssgs_v.priority%TYPE;
2038 l_pkg_name okl_invoice_mssgs_v.pkg_name%TYPE;
2039 l_proc_name okl_invoice_mssgs_v.proc_name%TYPE;
2040
2041 l_bind_proc VARCHAR2(3000);
2042 l_msg_return VARCHAR2(1); --BOOLEAN;
2043 l_ims_id okl_invoice_mssgs_v.id%TYPE;
2044
2045
2046 BEGIN
2047
2048 -- rmunjulu R12 Fixes -- comment out entire code and put NULL
2049 NULL;
2050 /*
2051 PRINT_TO_LOG( '*** HEADER RECORD CREATION FOR : ***');
2052 PRINT_TO_LOG( '*** ++++++++++++++++++++++++++++ ***');
2053 PRINT_TO_LOG( '* ====> CUSTOMER_ID: '||p_ixx_id||' CURRENCY: '||p_currency_code);
2054 PRINT_TO_LOG( '* ====> BILL_TO_SITE: '||p_ibt_id||' PAYMENT_METHOD: '||p_irm_id);
2055 PRINT_TO_LOG( '* ====> PRIVATE_LABEL: '||p_private_label||' DATE_CONSOLIDATED: '||p_date_consolidated);
2056 PRINT_TO_LOG( '* ====> INF_ID: '||p_inf_id||' SET_OF_BOOKS_ID: '||p_set_of_books_id);
2057 PRINT_TO_LOG( '* ====> ORG_ID: '||p_org_id);
2058 PRINT_TO_LOG( '*** ++++++++++++++++++++++++++++ ***');
2059
2060 g_header_counter := g_header_counter+1;
2061
2062 g_cnr_tbl(g_header_counter).id := Okc_P_Util.raw_to_number(sys_guid());
2063 g_cnr_tbl(g_header_counter).IBT_ID := p_ibt_id;
2064 g_cnr_tbl(g_header_counter).IXX_ID := p_ixx_id;
2065 g_cnr_tbl(g_header_counter).CURRENCY_CODE := p_currency_code;
2066 g_cnr_tbl(g_header_counter).IRM_ID := p_irm_id;
2067 g_cnr_tbl(g_header_counter).INF_ID := p_inf_id;
2068 g_cnr_tbl(g_header_counter).SET_OF_BOOKS_ID := p_set_of_books_id;
2069 g_cnr_tbl(g_header_counter).ORG_ID := p_org_id;
2070 g_cnr_tbl(g_header_counter).LEGAL_ENTITY_ID := p_legal_entity_id; -- for LE Uptake project 08-11-2006
2071 g_cnr_tbl(g_header_counter).date_consolidated := p_date_consolidated;
2072 g_cnr_tbl(g_header_counter).invoice_pull_yn := 'Y';
2073 g_cnr_tbl(g_header_counter).trx_status_code := 'PROCESSED'; --'SUBMITTED';
2074 -- stmathew, added on 07/20/2005
2075 g_cnr_tbl(g_header_counter).amount := 0;
2076 -- end addition
2077
2078 g_cnr_tbl(g_header_counter).consolidated_invoice_number :=
2079 Fnd_Seqnum.get_next_sequence ( l_application_id,
2080 l_document_category,
2081 p_set_of_books_id,
2082 'A',
2083 SYSDATE,
2084 x_dbseqnm,
2085 x_dbseqid);
2086
2087
2088 -- DB generated sequence number for the Consolidated Invoice
2089 PRINT_TO_LOG( '====> Generating Cons Bill SEQUENCE');
2090 g_cnr_tbl(g_header_counter).creation_date := SYSDATE;
2091 g_cnr_tbl(g_header_counter).created_by := Fnd_Global.USER_ID;
2092 g_cnr_tbl(g_header_counter).last_update_date := SYSDATE;
2093 g_cnr_tbl(g_header_counter).last_updated_by := Fnd_Global.USER_ID;
2094 g_cnr_tbl(g_header_counter).object_version_number := 1;
2095 g_cnr_tbl(g_header_counter).request_id := Fnd_Global.CONC_REQUEST_ID;
2096 g_cnr_tbl(g_header_counter).program_application_id := Fnd_Global.PROG_APPL_ID;
2097 g_cnr_tbl(g_header_counter).program_id := Fnd_Global.CONC_PROGRAM_ID;
2098 if Fnd_Global.CONC_REQUEST_ID <> -1 then
2099 g_cnr_tbl(g_header_counter).program_update_date := SYSDATE;
2100 end if;
2101
2102 PRINT_TO_LOG( '====> Cons Bill Number: '||g_cnr_tbl(g_header_counter).consolidated_invoice_number);
2103
2104
2105 --MDOKAL
2106 x_cnr_id := g_cnr_tbl(g_header_counter).id;
2107 x_cons_inv_num := g_cnr_tbl(g_header_counter).CONSOLIDATED_INVOICE_NUMBER;
2108 */
2109 EXCEPTION
2110 --Seed FND_MESSAGE like 'Could NOT CREATE Header RECORD'
2111 WHEN OTHERS THEN
2112 PRINT_TO_LOG('*=> Error Message(H1): '||SQLERRM);
2113 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME,
2114 p_msg_name => G_OTHERS);
2115 END create_new_invoice;
2116
2117 --This function checks for the existence of an consolidated invoice line
2118 -- in okl_cnsld_ar_lines_v.This function is called when the
2119 -- group_by_assets flag is set to 'Y'
2120 PROCEDURE line_exist (p_cnr_id IN NUMBER,
2121 p_khr_id IN NUMBER,
2122 p_kle_id IN NUMBER,
2123 p_ilt_id IN NUMBER,
2124 p_sequence_number IN NUMBER,
2125 p_group_by_contract_yn IN VARCHAR2,
2126 p_group_by_assets_yn IN VARCHAR2,
2127 x_lln_id OUT NOCOPY NUMBER,
2128 exists_flag OUT NOCOPY VARCHAR2
2129 )
2130 IS
2131
2132 BEGIN
2133
2134 -- rmunjulu R12 Fixes -- comment out entire code and put NULL
2135 NULL;
2136 /*
2137 -- Prime Local Variable
2138 exists_flag := 'Y';
2139 x_lln_id := NULL;
2140
2141 PRINT_TO_LOG( '*** CONSOLIDATED LINES CHECK: if a line exists for the following: ***');
2142 PRINT_TO_LOG( '* ====> CNR_ID: '||p_cnr_id);
2143 PRINT_TO_LOG( '* ====> KHR_ID: '||p_khr_id);
2144 PRINT_TO_LOG( '* ====> KLE_ID: '||p_kle_id);
2145 PRINT_TO_LOG( '* ====> ILT_ID: '||p_ilt_id);
2146 PRINT_TO_LOG( '* ====> SEQUENCE_NUMBER: '||p_sequence_number);
2147 PRINT_TO_LOG( '* ====> GROUP_BY_CONTRACT_YN: '||p_group_by_contract_yn);
2148 PRINT_TO_LOG( '* ====> GROUP_BY_ASSETS_YN: '||p_group_by_assets_yn);
2149 PRINT_TO_LOG( '* ====> GROUP_BY_ASSETS_YN: '||p_group_by_assets_yn);
2150 PRINT_TO_LOG( '*** End Invoice Group Details ***');
2151
2152 --MDOKAL
2153 if g_lln_tbl.COUNT > 0 then
2154 for n in g_lln_tbl.first..g_lln_tbl.last loop
2155
2156 if p_group_by_contract_yn = 'Y' and
2157 p_group_by_assets_yn = 'Y' and
2158 g_lln_tbl(n).cnr_id = p_cnr_id and
2159 g_lln_tbl(n).khr_id = p_khr_id and
2160 g_lln_tbl(n).ilt_id = p_ilt_id and
2161 g_lln_tbl(n).sequence_number = p_sequence_number
2162 then
2163 PRINT_TO_LOG( '====> Using SQL in check_line1 ');
2164 PRINT_TO_LOG('=***********> SELECT id FROM okl_cnsld_ar_lines_v');
2165 PRINT_TO_LOG('=***********> WHERE cnr_id = '||p_cnr_id||' AND ');
2166 PRINT_TO_LOG('=***********> khr_id = '||p_khr_id||' AND ');
2167 PRINT_TO_LOG('=***********> ilt_id = '||p_ilt_id||' AND ');
2168 PRINT_TO_LOG('=***********> sequence_number = '||p_sequence_number||';');
2169 x_lln_id := g_lln_tbl(n).id;
2170 exit;
2171 elsif
2172 p_group_by_contract_yn = 'Y' and
2173 p_group_by_assets_yn = 'Y' and
2174 p_kle_id is not null and
2175 g_lln_tbl(n).cnr_id = p_cnr_id and
2176 g_lln_tbl(n).khr_id = p_khr_id and
2177 g_lln_tbl(n).kle_id = p_kle_id and
2178 g_lln_tbl(n).ilt_id = p_ilt_id and
2179 g_lln_tbl(n).sequence_number = p_sequence_number
2180 then
2181 PRINT_TO_LOG( '====> Using SQL in check_line2 ');
2182 PRINT_TO_LOG('=***********> SELECT id FROM okl_cnsld_ar_lines_v');
2183 PRINT_TO_LOG('=***********> WHERE cnr_id = '||p_cnr_id||' AND ');
2184 PRINT_TO_LOG('=***********> khr_id = '||p_khr_id||' AND ');
2185 PRINT_TO_LOG('=***********> kle_id = '||p_kle_id||' AND ');
2186 PRINT_TO_LOG('=***********> ilt_id = '||p_ilt_id||' AND ');
2187 PRINT_TO_LOG('=***********> sequence_number = '||p_sequence_number||';');
2188 x_lln_id := g_lln_tbl(n).id;
2189 exit;
2190 elsif
2191 p_group_by_contract_yn = 'Y' and
2192 p_group_by_assets_yn = 'Y' and
2193 p_kle_id is null and
2194 g_lln_tbl(n).cnr_id = p_cnr_id and
2195 g_lln_tbl(n).khr_id = p_khr_id and
2196 g_lln_tbl(n).kle_id is null and
2197 g_lln_tbl(n).ilt_id = p_ilt_id and
2198 g_lln_tbl(n).sequence_number = p_sequence_number
2199 then
2200 PRINT_TO_LOG( '====> Using SQL in check_line3 ');
2201 PRINT_TO_LOG('=***********> SELECT id FROM okl_cnsld_ar_lines_v');
2202 PRINT_TO_LOG('=***********> WHERE cnr_id = '||p_cnr_id||' AND ');
2203 PRINT_TO_LOG('=***********> khr_id = '||p_khr_id||' AND ');
2204 PRINT_TO_LOG('=***********> kle_id is null AND ');
2205 PRINT_TO_LOG('=***********> ilt_id = '||p_ilt_id||' AND ');
2206 PRINT_TO_LOG('=***********> sequence_number = '||p_sequence_number||';');
2207 x_lln_id := g_lln_tbl(n).id;
2208 exit;
2209 elsif
2210 p_group_by_contract_yn <> 'Y' and
2211 g_lln_tbl(n).cnr_id = p_cnr_id and
2212 g_lln_tbl(n).ilt_id = p_ilt_id and
2213 g_lln_tbl(n).sequence_number = p_sequence_number
2214 then
2215 PRINT_TO_LOG( '====> Using SQL in check_line4 ');
2216 PRINT_TO_LOG('=***********> SELECT id FROM okl_cnsld_ar_lines_v');
2217 PRINT_TO_LOG('=***********> WHERE cnr_id = '||p_cnr_id||' AND ');
2218 PRINT_TO_LOG('=***********> ilt_id = '||p_ilt_id||' AND ');
2219 PRINT_TO_LOG('=***********> sequence_number = '||p_sequence_number||';');
2220 x_lln_id := g_lln_tbl(n).id;
2221 exit;
2222 end if;
2223 end loop;
2224 end if;
2225
2226 IF ( x_lln_id IS NULL ) THEN
2227 exists_flag := 'N';
2228 PRINT_TO_LOG( '====> No Line Exists for this combination. ');
2229 ELSE
2230 PRINT_TO_LOG( '====> Found an existing line for this combination. The id is '||x_lln_id);
2231 END IF;
2232
2233 PRINT_TO_LOG( '*** END CONSOLIDATED LINES CHECK ***');
2234 */
2235 EXCEPTION
2236 WHEN NO_DATA_FOUND THEN
2237 PRINT_TO_LOG('*=> Error Message(L1): '||SQLERRM);
2238 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME,
2239 p_msg_name => G_NO_DATA_FOUND);
2240
2241 exists_flag := 'N';
2242 WHEN TOO_MANY_ROWS THEN
2243 PRINT_TO_LOG('*=> Error Message(L2): '||SQLERRM);
2244 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME,
2245 p_msg_name => G_TOO_MANY_ROWS);
2246
2247 exists_flag := NULL;
2248 WHEN OTHERS THEN
2249 PRINT_TO_LOG('*=> Error Message(L3): '||SQLERRM);
2250 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME,
2251 p_msg_name => G_OTHERS);
2252 exists_flag := NULL;
2253
2254 END line_exist;
2255
2256 --This procedure creates a new consolidated invoice line
2257 --based on the parameters passed
2258 PROCEDURE create_new_line(
2259 p_khr_id IN NUMBER,
2260 p_cnr_id IN NUMBER,
2261 p_kle_id IN NUMBER,
2262 p_ilt_id IN NUMBER,
2263 p_currency_code IN VARCHAR2,
2264 p_sequence_number IN NUMBER,
2265 p_line_type IN VARCHAR2,
2266 p_group_by_contract_yn IN VARCHAR2,
2267 p_group_by_assets_yn IN VARCHAR2,
2268 p_contract_level_yn IN VARCHAR2,
2269 x_lln_id OUT NOCOPY NUMBER
2270 )
2271
2272 IS
2273
2274 x_llnv_rec Okl_Lln_Pvt.llnv_rec_type;
2275 x_llnv_tbl Okl_Lln_Pvt.llnv_tbl_type;
2276
2277 p_llnv_rec Okl_Lln_Pvt.llnv_rec_type;
2278 p_llnv_tbl Okl_Lln_Pvt.llnv_tbl_type;
2279
2280
2281 p_api_version NUMBER := 1.0;
2282 p_init_msg_list VARCHAR2(1) := Okl_Api.g_false;
2283 x_return_status VARCHAR2(1) := 'S';
2284 x_msg_count NUMBER;
2285 x_msg_data VARCHAR2(2000);
2286
2287
2288 BEGIN
2289 --MDOKAL
2290 -- rmunjulu R12 Fixes -- comment out entire code and put NULL
2291 NULL;
2292 /*
2293 g_line_counter := g_line_counter+1;
2294 g_lln_tbl(g_line_counter).id := Okc_P_Util.raw_to_number(sys_guid());
2295 g_lln_tbl(g_line_counter).sequence_number := nvl(p_sequence_number, 1);
2296 if (nvl(p_group_by_contract_yn, 'N') = 'Y' OR nvl(p_contract_level_yn, 'N') = 'N') then
2297 if nvl(p_group_by_assets_yn, 'N') = 'N' then
2298 g_lln_tbl(g_line_counter).kle_id := p_kle_id;
2299 end if;
2300 end if;
2301 if (nvl(p_group_by_contract_yn, 'N') = 'Y' OR nvl(p_contract_level_yn, 'N') = 'N') then
2302 g_lln_tbl(g_line_counter).khr_id := p_khr_id;
2303 end if;
2304 g_lln_tbl(g_line_counter).cnr_id := p_cnr_id;
2305 g_lln_tbl(g_line_counter).ilt_id := p_ilt_id;
2306 g_lln_tbl(g_line_counter).line_type := SUBSTR(p_line_type,1,50);
2307 g_lln_tbl(g_line_counter).creation_date := SYSDATE;
2308 g_lln_tbl(g_line_counter).created_by := Fnd_Global.USER_ID;
2309 g_lln_tbl(g_line_counter).last_update_date := SYSDATE;
2310 g_lln_tbl(g_line_counter).last_updated_by := Fnd_Global.USER_ID;
2311 g_lln_tbl(g_line_counter).object_version_number := 1;
2312 g_lln_tbl(g_line_counter).request_id := Fnd_Global.CONC_REQUEST_ID;
2313 g_lln_tbl(g_line_counter).program_application_id := Fnd_Global.PROG_APPL_ID;
2314
2315 -- stmathew added on 07/20/2005
2316 g_lln_tbl(g_line_counter).amount := 0;
2317 -- end
2318
2319 g_lln_tbl(g_line_counter).program_id := Fnd_Global.CONC_PROGRAM_ID;
2320 if Fnd_Global.CONC_REQUEST_ID <> -1 then
2321 g_lln_tbl(g_line_counter).program_update_date := SYSDATE;
2322 end if;
2323
2324 PRINT_TO_LOG( '*** LINE RECORD CREATION FOR : ***');
2325 PRINT_TO_LOG( '*** ++++++++++++++++++++++++++++ ***');
2326 PRINT_TO_LOG( '* ====> KHR_ID: '||g_lln_tbl(g_line_counter).khr_id||' KLE_ID: '||g_lln_tbl(g_line_counter).kle_id);
2327 PRINT_TO_LOG( '* ====> CNR_ID: '||g_lln_tbl(g_line_counter).cnr_id ||' ILT_ID: '||g_lln_tbl(g_line_counter).ilt_id);
2328 PRINT_TO_LOG( '* ====> SEQUENCE_NUMBER: '||g_lln_tbl(g_line_counter).sequence_number||' LINE_TYPE: '||g_lln_tbl(g_line_counter).line_type);
2329 PRINT_TO_LOG( '*** ++++++++++++++++++++++++++++ ***');
2330
2331 IF ( x_return_status = 'S' ) THEN
2332 PRINT_TO_LOG('====> Consolidated Line Created.');
2333 ELSE
2334 PRINT_TO_LOG('*=> FAILED: Consolidated Line NOT Created.');
2335 END IF;
2336
2337 --MDOKAL
2338 -- running totals
2339
2340 if nvl(g_prev_cnr_id, 1) = p_cnr_id then
2341 null;
2342 --g_cnr_total := g_cnr_total + g_lln_total;
2343 else
2344
2345 if g_cnr_tbl.exists(g_header_counter-1) then
2346 if g_cnr_total = 0 and g_header_counter = 2 then
2347 null;
2348 --g_cnr_tbl(g_header_counter-1).amount := g_lln_total;
2349 else
2350 null;
2351 -- g_cnr_tbl(g_header_counter-1).amount := g_cnr_total;
2352 end if;
2353 end if;
2354
2355 --g_cnr_total := g_lln_total;
2356 g_prev_cnr_id := p_cnr_id;
2357
2358 end if;
2359
2360 x_lln_id := g_lln_tbl(g_line_counter).id;
2361 */
2362 EXCEPTION
2363 --Seed FND_MESSAGE like 'Could NOT CREATE Line RECORD'
2364 WHEN OTHERS THEN
2365 PRINT_TO_LOG('*=> Error Message(L1): '||SQLERRM);
2366 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME,
2367 p_msg_name => G_OTHERS);
2368 END create_new_line;
2369
2370
2371 --This procedure creates a new consolidated invoice streams
2372 --based on the parameters passed
2373 PROCEDURE create_new_streams(
2374 p_lln_id IN NUMBER,
2375 p_sty_id IN NUMBER,
2376 p_kle_id IN NUMBER,
2377 p_khr_id IN NUMBER,
2378 p_amount IN NUMBER,
2379 p_sel_id IN NUMBER,
2380 p_cnr_id IN NUMBER,
2381 x_lsm_id OUT NOCOPY NUMBER,
2382 x_return_status OUT NOCOPY VARCHAR2
2383 )
2384
2385 IS
2386
2387 x_cnrv_rec Okl_Cnr_Pvt.cnrv_rec_type;
2388 x_cnrv_tbl Okl_Cnr_Pvt.cnrv_tbl_type;
2389
2390 x_lsmv_rec Okl_Lsm_Pvt.lsmv_rec_type;
2391 x_lsmv_tbl Okl_Lsm_Pvt.lsmv_tbl_type;
2392
2393 p_lsmv_rec Okl_Lsm_Pvt.lsmv_rec_type;
2394 p_lsmv_tbl Okl_Lsm_Pvt.lsmv_tbl_type;
2395
2396 -- fmiao - Bug#5232919 - Modified - Start
2397 -- Commenting the decalration of Invoice Message Attribute Records as
2398 -- henceforth these are tracked as global records
2399 --p_imav_rec Okl_ima_pvt.imav_rec_type;
2400 --x_imav_rec Okl_ima_pvt.imav_rec_type;
2401
2402 -- Defining table record for out record of Invoice Message Attribute
2403 x_imav_tbl Okl_Ima_Pvt.imav_tbl_type;
2404 -- fmiao - Bug#5232919 - Modified - end
2405
2406 -- ssiruvol - Bug#5354130 - Added - Start
2407 -- temporary records for storing CNR table of records
2408 l_cnr_tbl cnr_tbl_type;
2409 -- ssiruvol - Bug#5354130 - Added - End
2410
2411 l_save_ims_id okl_invoice_mssgs_v.id%TYPE;
2412 l_save_priority okl_invoice_mssgs_v.priority%TYPE;
2413 l_bind_proc VARCHAR2(3000);
2414 l_msg_return VARCHAR2(1);
2415
2416 p_api_version NUMBER := 1.0;
2417 p_init_msg_list VARCHAR2(1) := Okl_Api.g_false;
2418 x_msg_count NUMBER;
2419 x_msg_data VARCHAR2(2000);
2420 l_cnr_rec cnr_rec_type;
2421 l_lln_rec lln_rec_type;
2422
2423 -- fmiao - Bug#5232919 - Modified - Start
2424 l_date_consolidated DATE;
2425 -- fmiao - Bug#5232919 - Modified - end
2426
2427 -- BUG#4621302
2428 -- cursor to fetch the installed languages
2429 CURSOR get_languages IS
2430 SELECT language_code
2431 FROM FND_LANGUAGES
2432 WHERE INSTALLED_FLAG IN ('I', 'B');
2433
2434 TYPE lang_tbl_type IS TABLE OF get_languages%ROWTYPE INDEX BY BINARY_INTEGER;
2435
2436 l_lang_tbl lang_tbl_type;
2437 lang_count NUMBER;
2438 tl_count NUMBER;
2439 -- BUG#4621302
2440 BEGIN
2441 --MDOKAL, if headers have reached max size then perform insert
2442 -- and keep the last record for ongoing processing.
2443
2444 -- rmunjulu R12 Fixes - comment out entire code and put NULL
2445 NULL;
2446 /*
2447
2448
2449 if (g_lsm_tbl.COUNT > G_Commit_Max )
2450 --and
2451 -- g_lln_tbl(g_line_counter).id <> g_lsm_tbl(g_stream_counter).lln_id)
2452 then
2453
2454 if g_cnr_tbl.count >= 1 then
2455
2456 -- First store the last record locally
2457 l_cnr_rec:= g_cnr_tbl(g_header_counter);
2458 -- Now delete the last record from the main table
2459 -- because it will be inserted next time around
2460 g_cnr_tbl.DELETE(g_header_counter);
2461 g_header_counter := g_header_counter-1;
2462
2463 -- #4621302
2464 lang_count := 1;
2465 FOR l_lang_rec in get_languages
2466 LOOP
2467 l_lang_tbl(lang_count) := l_lang_rec;
2468 lang_count := lang_count+1;
2469 END LOOP;
2470
2471 -- check table count
2472 if g_cnr_tbl.count > 0 then
2473 tl_count := g_cnr_tbl.first;
2474 -- Create TL Records
2475 for z in g_cnr_tbl.first..g_cnr_tbl.last loop
2476 -- #4621302
2477 FOR lang_count IN l_lang_tbl.first..l_lang_tbl.last LOOP
2478 g_cnr_tl_tbl(tl_count).id := g_cnr_tbl(z).id;
2479 g_cnr_tl_tbl(tl_count).language := l_lang_tbl(lang_count).language_code;
2480 g_cnr_tl_tbl(tl_count).source_lang := USERENV('LANG');
2481 g_cnr_tl_tbl(tl_count).sfwt_flag := 'N';
2482 g_cnr_tl_tbl(tl_count).created_by := fnd_global.user_id;
2483 g_cnr_tl_tbl(tl_count).creation_date := sysdate;
2484 g_cnr_tl_tbl(tl_count).last_updated_by := fnd_global.user_id;
2485 g_cnr_tl_tbl(tl_count).last_update_date := sysdate;
2486 g_cnr_tl_tbl(tl_count).last_update_login := fnd_global.login_id;
2487 tl_count := tl_count + 1;
2488 END LOOP; -- languages loop
2489
2490 -- invoice messaging processing
2491 if g_inv_msg = 'TRUE' and g_msg_tbl.COUNT > 0 then
2492 -- Find message with the highest priority
2493 l_save_priority := NULL;
2494 for e in g_msg_tbl.FIRST..g_msg_tbl.LAST loop
2495 -- fmiao - Bug#5232919 - Modified - Start
2496 l_date_consolidated := TRUNC(g_cnr_tbl(z).date_consolidated);
2497 -- Check if the invoice message is effective for this consolidated invoice
2498 IF ( l_date_consolidated BETWEEN NVL(g_msg_tbl(e).start_date,l_date_consolidated)
2499 AND NVL(g_msg_tbl(e).end_date,l_date_consolidated)) THEN
2500
2501 PRINT_TO_LOG('====> IMS_ID: '||g_msg_tbl(e).id);
2502 PRINT_TO_LOG('====> PKG: '||g_msg_tbl(e).pkg_name);
2503 PRINT_TO_LOG('====> PROC: '||g_msg_tbl(e).proc_name);
2504
2505 l_bind_proc := 'BEGIN OKL_QUAL_INV_MSGS.'||g_msg_tbl(e).proc_name||'(:1,:2); END;';
2506
2507 PRINT_TO_LOG('l_bind_proc : '||l_bind_proc);
2508 PRINT_TO_LOG('g_cnr_tbl(z).id : '||g_cnr_tbl(z).id);
2509 BEGIN
2510 EXECUTE IMMEDIATE l_bind_proc USING IN g_cnr_tbl(z).id, OUT l_msg_return;
2511 EXCEPTION
2512 WHEN OTHERS THEN
2513 PRINT_TO_LOG('Invoice Message error -- '||SQLERRM);
2514 END;
2515
2516 if (l_msg_return = '1' ) then
2517 if (l_save_priority is null) or (g_msg_tbl(e).priority < l_save_priority) then
2518 l_save_priority := g_msg_tbl(e).priority;
2519 l_save_ims_id := g_msg_tbl(e).id;
2520 end if;
2521 end if;
2522 END IF;
2523 -- end of check for effective dates of invoice message
2524 -- fmiao - Bug#5232919 - Modified - End
2525
2526 end loop; -- end of message table loop
2527
2528 -- Create Intersection Record
2529 if (l_save_priority is not null) then
2530 -- fmiao - Bug#5232919 - Modified - Started
2531 -- Populating the global Inv Messg Attr records
2532 g_imav_counter := g_imav_counter + 1;
2533 g_imav_tbl(g_imav_counter).cnr_id := g_cnr_tbl(z).id;
2534 g_imav_tbl(g_imav_counter).ims_id := l_save_ims_id;
2535
2536 --p_imav_rec.cnr_id := x_cnrv_rec.id;
2537 --p_imav_rec.ims_id := l_save_ims_id;
2538 -- Commenting code that inserts record into Inv Msg Attr table because
2539 -- at this point the CNR_ID is not yet in the CNSLD HDR table and this will
2540 -- fail validation at TAPI of Inv Msg Attr table
2541 */
2542 /*
2543 -- Start of wraper code generated automatically by Debug code generator for okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT
2544 IF(IS_DEBUG_PROCEDURE_ON) THEN
2545 BEGIN
2546 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
2547 END;
2548 END IF;
2549
2550 okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT(
2551 p_api_version
2552 ,p_init_msg_list
2553 ,x_return_status
2554 ,x_msg_count
2555 ,x_msg_data
2556 ,p_imav_rec
2557 ,x_imav_rec
2558 );
2559 IF(IS_DEBUG_PROCEDURE_ON) THEN
2560 BEGIN
2561 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
2562 END;
2563 END IF;
2564 -- End of wraper code generated automatically by Debug code generator for okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT
2565 IF ( x_return_status = 'S' ) THEN
2566 PRINT_TO_LOG('====> Message Created.');
2567 ELSE
2568 PRINT_TO_LOG('*=> FAILED:Message Creation');
2569 END IF;
2570 */
2571 -- fmiao - Bug#5232919 - Modified - End
2572 /*
2573 ELSE
2574 PRINT_TO_LOG('====> NO Message Qualified');
2575 END IF;
2576 END IF; -- end of check for invoice message processing
2577 end loop; -- End Create TL Records
2578
2579 end if; -- check table count
2580
2581 PRINT_TO_LOG('Performing bulk insert for cnr, record count is '||g_cnr_tbl.count);
2582 BEGIN
2583 savepoint H1;
2584
2585 -- check table count
2586 if g_cnr_tbl.count > 0 then
2587 forall x in g_cnr_tbl.first..g_cnr_tbl.last
2588 save exceptions
2589 insert into okl_cnsld_ar_hdrs_b
2590 values g_cnr_tbl(x);
2591
2592 forall d in g_cnr_tl_tbl.first..g_cnr_tl_tbl.last
2593 save exceptions
2594 insert into okl_cnsld_ar_hdrs_tl
2595 values g_cnr_tl_tbl(d);
2596
2597 end if; -- check table count
2598
2599 EXCEPTION
2600 WHEN OTHERS THEN
2601 PRINT_TO_LOG('Error during Header Insertion, rollback to H1');
2602 rollback to H1;
2603 g_cnr_tbl.DELETE;
2604 g_cnr_tl_tbl.DELETE;
2605 g_lln_tbl.DELETE;
2606 g_lln_tl_tbl.DELETE;
2607 g_lsm_tbl.DELETE;
2608 g_lsm_tl_tbl.DELETE;
2609 g_xsi_tbl.DELETE;
2610 g_xsi_tl_tbl.DELETE;
2611 g_xls_tbl.DELETE;
2612 g_xls_tl_tbl.DELETE;
2613 RAISE;
2614 END;
2615
2616 end if;
2617 g_header_counter := 0;
2618 -- flush tables
2619 g_cnr_tbl.delete;
2620 g_cnr_tl_tbl.delete;
2621
2622 -- add the last record back into the pl/sql table
2623 if l_cnr_rec.id is not null then
2624 g_header_counter := 1;
2625 g_cnr_tbl(g_header_counter) := l_cnr_rec;
2626 --g_cnr_tbl(g_header_counter).amount := g_cnr_total;
2627 end if;
2628 -- End Header Inserts
2629
2630 --MDOKAL, if lines have reached max size then perform insert
2631 -- and keep the last record for ongoing processing.
2632
2633 if g_lln_tbl.count >= 1 then
2634
2635 -- First store the last record locally
2636 l_lln_rec:= g_lln_tbl(g_line_counter);
2637 -- Now delete the last record from the main table
2638 -- because it will be inserted next time around
2639 g_lln_tbl.DELETE(g_line_counter);
2640 g_line_counter := g_line_counter-1;
2641
2642 -- check table count
2643 if g_lln_tbl.count > 0 then
2644
2645 -- Create TL Records
2646 tl_count := g_lln_tbl.first;
2647 for x in g_lln_tbl.first..g_lln_tbl.last loop
2648 -- BUG#4621302
2649 FOR lang_count in l_lang_tbl.first..l_lang_tbl.last
2650 LOOP
2651 g_lln_tl_tbl(tl_count).id := g_lln_tbl(x).id;
2652 g_lln_tl_tbl(tl_count).language := l_lang_tbl(lang_count).language_code;
2653 g_lln_tl_tbl(tl_count).source_lang := USERENV('LANG');
2654 g_lln_tl_tbl(tl_count).sfwt_flag := 'N';
2655 g_lln_tl_tbl(tl_count).created_by := fnd_global.user_id;
2656 g_lln_tl_tbl(tl_count).creation_date := sysdate;
2657 g_lln_tl_tbl(tl_count).last_updated_by := fnd_global.user_id;
2658 g_lln_tl_tbl(tl_count).last_update_date := sysdate;
2659 g_lln_tl_tbl(tl_count).last_update_login := fnd_global.login_id;
2660 tl_count := tl_count + 1;
2661 END LOOP; -- languages loop
2662 end loop;
2663
2664 end if; -- check table count
2665
2666 BEGIN
2667 savepoint L1;
2668 PRINT_TO_LOG('Performing bulk insert for lln, record count is '||g_lln_tbl.count);
2669
2670 -- check table count
2671 if g_lln_tbl.count > 0 then
2672
2673 forall x in g_lln_tbl.first..g_lln_tbl.last
2674 save exceptions
2675 insert into okl_cnsld_ar_lines_b
2676 values g_lln_tbl(x);
2677
2678 forall e in g_lln_tl_tbl.first..g_lln_tl_tbl.last
2679 save exceptions
2680 insert into okl_cnsld_ar_lines_tl
2681 values g_lln_tl_tbl(e);
2682
2683 end if; -- check table count
2684
2685 EXCEPTION
2686 WHEN OTHERS THEN
2687 PRINT_TO_LOG('Error during Line Insertion, rollback to L1');
2688 rollback to L1;
2689 g_lln_tl_tbl.DELETE;
2690 g_lsm_tbl.DELETE;
2691 g_lsm_tl_tbl.DELETE;
2692 g_xsi_tbl.DELETE;
2693 g_xsi_tl_tbl.DELETE;
2694 g_xls_tbl.DELETE;
2695 g_xls_tl_tbl.DELETE;
2696
2697 -- check table count
2698 if g_lln_tbl.count > 0 then
2699
2700 for e in g_lln_tbl.FIRST..g_lln_tbl.LAST loop
2701 delete from okl_cnsld_ar_hdrs_b
2702 where id = g_lln_tbl(e).cnr_id;
2703 end loop;
2704
2705 end if; -- check table count
2706
2707 g_lln_tbl.DELETE;
2708 RAISE;
2709 END;
2710
2711 end if;
2712 g_line_counter := 0;
2713 -- flush tables
2714 g_lln_tbl.delete;
2715 g_lln_tl_tbl.delete;
2716
2717 -- add the last record back into the pl/sql table
2718 if l_lln_rec.id is not null then
2719 g_line_counter := 1;
2720 g_lln_tbl(g_line_counter) := l_lln_rec;
2721 --g_lln_tbl(g_line_counter).amount := g_lln_total;
2722 end if;
2723
2724 -- End Line Inserts
2725
2726 --MDOKAL --process inserts
2727
2728 -- insert consolidated streams
2729 if g_lsm_tbl.count > 0 then
2730
2731 -- Create TL Records
2732 tl_count := g_lsm_tbl.first;
2733 for y in g_lsm_tbl.first..g_lsm_tbl.last loop
2734 -- BUG#4621302
2735 FOR lang_count in l_lang_tbl.first..l_lang_tbl.last
2736 LOOP
2737 g_lsm_tl_tbl(tl_count).id := g_lsm_tbl(y).id;
2738 g_lsm_tl_tbl(tl_count).language := l_lang_tbl(lang_count).language_code;
2739 g_lsm_tl_tbl(tl_count).source_lang := USERENV('LANG');
2740 g_lsm_tl_tbl(tl_count).sfwt_flag := 'N';
2741 g_lsm_tl_tbl(tl_count).created_by := fnd_global.user_id;
2742 g_lsm_tl_tbl(tl_count).creation_date := sysdate;
2743 g_lsm_tl_tbl(tl_count).last_updated_by := fnd_global.user_id;
2744 g_lsm_tl_tbl(tl_count).last_update_date := sysdate;
2745 g_lsm_tl_tbl(tl_count).last_update_login := fnd_global.login_id;
2746 tl_count := tl_count + 1;
2747 END LOOP; -- languages loop
2748 end loop;
2749
2750 PRINT_TO_LOG('Performing bulk insert for lsm, record count is '||g_lsm_tbl.count);
2751
2752 BEGIN
2753 savepoint D1;
2754 forall x in g_lsm_tbl.first..g_lsm_tbl.last
2755 save exceptions
2756 insert into okl_cnsld_ar_strms_b
2757 values g_lsm_tbl(x);
2758
2759 forall f in g_lsm_tl_tbl.first..g_lsm_tl_tbl.last
2760 save exceptions
2761 insert into okl_cnsld_ar_strms_tl
2762 values g_lsm_tl_tbl(f);
2763
2764 commit;
2765 EXCEPTION
2766 WHEN OTHERS THEN
2767 PRINT_TO_LOG('Error during Stream Insertion, rollback to D1');
2768 rollback to D1;
2769 g_cnr_tl_tbl.delete;
2770 g_lln_tl_tbl.DELETE;
2771 g_lsm_tl_tbl.DELETE;
2772 g_xsi_tbl.DELETE;
2773 g_xsi_tl_tbl.DELETE;
2774 g_xls_tbl.DELETE;
2775 g_xls_tl_tbl.DELETE;
2776
2777 -- check table count
2778 if g_lsm_tbl.count > 0 then
2779
2780 for e in g_lsm_tbl.FIRST..g_lsm_tbl.LAST loop
2781
2782 -- check table count
2783 if g_lln_tbl.count > 0 then
2784
2785 for f in g_lln_tbl.FIRST..g_lln_tbl.LAST loop
2786 delete from okl_cnsld_ar_hdrs_b
2787 where id = g_lln_tbl(f).cnr_id;
2788 end loop;
2789
2790 end if; -- check table count
2791
2792 delete from okl_cnsld_ar_lines_b
2793 where id = g_lsm_tbl(e).lln_id;
2794 end loop;
2795
2796 end if; -- check table count
2797
2798 g_lsm_tbl.DELETE;
2799 g_lln_tbl.DELETE;
2800 g_cnr_tbl.DELETE;
2801 RAISE;
2802 END;
2803 end if;
2804 -- flush tables
2805 g_lsm_tbl.delete;
2806 g_lsm_tl_tbl.delete;
2807
2808 g_stream_counter := 0;
2809
2810 -- fmiao - Bug#5232919 - Modified - Start
2811 -- Code to insert the table of records into OKL_INV_MSSG_ATT
2812 IF ( g_imav_tbl.COUNT > 0) THEN
2813 -- Start of wraper code generated automatically by Debug code generator for okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT
2814 IF(IS_DEBUG_PROCEDURE_ON) THEN
2815 BEGIN
2816 Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
2817 END;
2818 END IF;
2819
2820 Okl_Inv_Mssg_Att_Pub.INSERT_INV_MSSG_ATT(
2821 p_api_version
2822 ,p_init_msg_list
2823 ,x_return_status
2824 ,x_msg_count
2825 ,x_msg_data
2826 ,g_imav_tbl
2827 ,x_imav_tbl
2828 );
2829
2830 IF(IS_DEBUG_PROCEDURE_ON) THEN
2831 BEGIN
2832 Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
2833 END;
2834 END IF;
2835 -- End of wraper code generated automatically by Debug code generator for okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT
2836 IF ( x_return_status = 'S' ) THEN
2837 PRINT_TO_LOG('====> Message Created.');
2838 ELSE
2839 PRINT_TO_LOG('*=> FAILED:Message Creation');
2840 END IF;
2841
2842 -- flush the global table of records
2843 g_imav_tbl.DELETE;
2844 g_imav_counter := 0;
2845 END IF; -- end of checking for presence of g_imav_tbl records
2846 -- fmiao - Bug#5232919 - Modified - End
2847
2848 end if; -- main
2849 g_stream_counter := g_stream_counter+1;
2850 g_lsm_tbl(g_stream_counter).id := Okc_P_Util.raw_to_number(sys_guid());
2851 g_lsm_tbl(g_stream_counter).KLE_ID := p_kle_id;
2852 g_lsm_tbl(g_stream_counter).KHR_ID := p_khr_id;
2853 g_lsm_tbl(g_stream_counter).STY_ID := p_sty_id;
2854 g_lsm_tbl(g_stream_counter).LLN_ID := p_lln_id;
2855 g_lsm_tbl(g_stream_counter).AMOUNT := p_amount;
2856 g_lsm_tbl(g_stream_counter).SEL_ID := p_sel_id;
2857 g_lsm_tbl(g_stream_counter).receivables_invoice_id := -99999;
2858 g_lsm_tbl(g_stream_counter).creation_date := (SYSDATE);
2859 g_lsm_tbl(g_stream_counter).created_by := Fnd_Global.USER_ID;
2860 g_lsm_tbl(g_stream_counter).last_update_date := (SYSDATE);
2861 g_lsm_tbl(g_stream_counter).last_updated_by := Fnd_Global.USER_ID;
2862 g_lsm_tbl(g_stream_counter).object_version_number := 1;
2863 g_lsm_tbl(g_stream_counter).request_id := Fnd_Global.CONC_REQUEST_ID;
2864 g_lsm_tbl(g_stream_counter).program_application_id := Fnd_Global.PROG_APPL_ID;
2865 g_lsm_tbl(g_stream_counter).program_id := Fnd_Global.CONC_PROGRAM_ID;
2866 if Fnd_Global.CONC_REQUEST_ID <> -1 then
2867 g_lsm_tbl(g_stream_counter).program_update_date := (SYSDATE);
2868 end if;
2869 PRINT_TO_LOG( '*** STREAM RECORD CREATION FOR : ***');
2870 PRINT_TO_LOG( '*** ++++++++++++++++++++++++++++ ***');
2871 PRINT_TO_LOG( '* ====> KHR_ID: '||g_lsm_tbl(g_stream_counter).KHR_ID||' KLE_ID: '||g_lsm_tbl(g_stream_counter).KLE_ID);
2872 PRINT_TO_LOG( '* ====> STY_ID: '||g_lsm_tbl(g_stream_counter).STY_ID||' LLN_ID: '||g_lsm_tbl(g_stream_counter).LLN_ID);
2873 PRINT_TO_LOG( '* ====> AMOUNT: '||g_lsm_tbl(g_stream_counter).AMOUNT);
2874 PRINT_TO_LOG( '*** ++++++++++++++++++++++++++++ ***');
2875
2876 x_return_status := 'S';
2877 IF ( x_return_status = 'S' ) THEN
2878 PRINT_TO_LOG('====> Consolidated Streams Created.');
2879 ELSE
2880 PRINT_TO_LOG('*=> FAILED: Consolidated Streams NOT Created.');
2881 END IF;
2882
2883 --MDOKAL
2884 -- running totals
2885
2886 if nvl(g_prev_lln_id, 1) = p_lln_id then
2887 null;
2888 --g_lln_total := g_lln_total + p_amount;
2889 else
2890 if g_lln_tbl.exists(g_line_counter-1) then
2891 if nvl(g_lln_total, 0) = 0 then
2892 null;
2893 --g_lln_tbl(g_line_counter-1).amount := p_amount;
2894 else
2895 null;
2896 --g_lln_tbl(g_line_counter-1).amount := g_lln_total;
2897 end if;
2898 end if;
2899 --g_lln_total := p_amount;
2900 g_prev_lln_id := p_lln_id;
2901 end if;
2902
2903 if g_xls_tbl.COUNT > G_Commit_Max or
2904 g_xsi_tbl.COUNT > G_Commit_Max or
2905 g_xls_tl_tbl.COUNT > G_Commit_Max or
2906 g_xsi_tl_tbl.COUNT > G_Commit_Max
2907 then
2908 if g_xsi_tbl.COUNT > 0 then
2909 PRINT_TO_LOG('Performing bulk update for xsi, record count is '||g_xsi_tbl.COUNT );
2910 BEGIN
2911 savepoint U1;
2912 for indx in g_xsi_tbl.first..g_xsi_tbl.last loop
2913 -- rseela BUG#4733028 removed the updation of xtrx_invoice_pull_yn
2914 update okl_ext_sell_invs_b
2915 set trx_status_code = g_xsi_tbl(indx).trx_status_code,
2916 -- xtrx_invoice_pull_yn = g_xsi_tbl(indx).xtrx_invoice_pull_yn,
2917 last_update_date = sysdate,
2918 last_updated_by = fnd_global.user_id,
2919 last_update_login = fnd_global.login_id
2920 where id = g_xsi_tbl(indx).id;
2921 end loop;
2922 EXCEPTION
2923 WHEN OTHERS THEN
2924 PRINT_TO_LOG('Error during Update of okl_ext_sell_invs_b, rollback to U1');
2925 rollback to U1;
2926 RAISE;
2927 END;
2928 commit;
2929 end if;
2930 -- flush table
2931 g_xsi_tbl.delete;
2932 g_xsi_counter := 0;
2933
2934 if g_xls_tbl.COUNT > 0 then
2935 PRINT_TO_LOG('Performing bulk update for xls, record count is '||g_xls_tbl.COUNT );
2936 BEGIN
2937 savepoint U2;
2938 for s in g_xls_tbl.first..g_xls_tbl.last loop
2939 update okl_xtl_sell_invs_b
2940 set lsm_id = g_xls_tbl(s).lsm_id,
2941 xtrx_cons_stream_id = g_xls_tbl(s).lsm_id,
2942 last_update_date = sysdate,
2943 last_updated_by = fnd_global.user_id,
2944 last_update_login = fnd_global.login_id
2945 where id = g_xls_tbl(s).id;
2946 end loop;
2947 EXCEPTION
2948 WHEN OTHERS THEN
2949 PRINT_TO_LOG('Error during Update of okl_xtl_sell_invs_b, rollback to U2');
2950 rollback to U2;
2951 RAISE;
2952 END;
2953 commit;
2954 end if;
2955 -- flush table
2956 g_xls_tbl.delete;
2957 g_xls_counter := 0;
2958
2959 if g_xsi_tl_tbl.COUNT > 0 then
2960 PRINT_TO_LOG('Performing bulk update for xsi tl, record count is '||g_xsi_tl_tbl.COUNT );
2961 BEGIN
2962 savepoint U3;
2963 for u in g_xsi_tl_tbl.first..g_xsi_tl_tbl.last loop
2964 update okl_ext_sell_invs_tl
2965 set xtrx_cons_invoice_number = g_xsi_tl_tbl(u).xtrx_cons_invoice_number,
2966 xtrx_format_type = g_xsi_tl_tbl(u).xtrx_format_type,
2967 xtrx_private_label = g_xsi_tl_tbl(u).xtrx_private_label,
2968 last_update_date = sysdate,
2969 last_updated_by = fnd_global.user_id,
2970 last_update_login = fnd_global.login_id
2971 where id = g_xsi_tl_tbl(u).id;
2972 end loop;
2973 EXCEPTION
2974 WHEN OTHERS THEN
2975 PRINT_TO_LOG('Error during Update of okl_ext_sell_invs_tl, rollback to U3');
2976 rollback to U3;
2977 RAISE;
2978 END;
2979 commit;
2980 end if;
2981 -- flush table
2982 g_xsi_tl_tbl.delete;
2983 g_xsi_tl_counter := 0;
2984
2985 if g_xls_tl_tbl.COUNT > 0 then
2986 PRINT_TO_LOG('Performing bulk update for xls tl, record count is '||g_xls_tl_tbl.COUNT );
2987 BEGIN
2988 savepoint U4;
2989 for t in g_xls_tl_tbl.first..g_xls_tl_tbl.last loop
2990
2991 update okl_xtl_sell_invs_tl
2992 set xtrx_contract = g_xls_tl_tbl(t).xtrx_contract,
2993 xtrx_asset = g_xls_tl_tbl(t).xtrx_asset,
2994 xtrx_stream_type = g_xls_tl_tbl(t).xtrx_stream_type,
2995 xtrx_stream_group = g_xls_tl_tbl(t).xtrx_stream_group,
2996 last_update_date = sysdate,
2997 last_updated_by = fnd_global.user_id,
2998 last_update_login = fnd_global.login_id
2999 where id = g_xls_tl_tbl(t).id;
3000 end loop;
3001 EXCEPTION
3002 WHEN OTHERS THEN
3003 PRINT_TO_LOG('Error during Update of okl_xtl_sell_invs_tl, rollback to U4');
3004 rollback to U4;
3005 RAISE;
3006 END;
3007 commit;
3008 end if;
3009 -- flush table
3010 g_xls_tl_tbl.delete;
3011 g_xls_tl_counter := 0;
3012 end if;
3013
3014 x_lsm_id := g_lsm_tbl(g_stream_counter).id;
3015 */
3016 EXCEPTION
3017 --Seed FND_MESSAGE like 'Could NOT CREATE Stream RECORD'
3018 WHEN OTHERS THEN
3019 PRINT_TO_LOG('*=> Error Message(D1): '||SQLERRM);
3020 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME,
3021 p_msg_name => G_OTHERS);
3022 END create_new_streams;
3023
3024
3025 PROCEDURE process_cons_bill_tbl(
3026 p_contract_number IN VARCHAR2,
3027 p_api_version IN NUMBER,
3028 p_init_msg_list IN VARCHAR2,
3029 p_commit IN VARCHAR2,
3030 x_return_status OUT NOCOPY VARCHAR2,
3031 x_msg_count OUT NOCOPY NUMBER,
3032 x_msg_data OUT NOCOPY VARCHAR2,
3033 p_cons_bill_tbl IN OUT NOCOPY cons_bill_tbl_type,
3034 p_saved_bill_rec IN OUT NOCOPY saved_bill_rec_type,
3035 p_update_tbl IN OUT NOCOPY update_tbl_type)
3036 IS
3037
3038
3039 l_api_name CONSTANT VARCHAR2(30) := 'process_cons_bill_tbl';
3040 l_format_name okl_invoice_formats_v.name%TYPE;
3041 l_contract_level_yn VARCHAR2(3);
3042 l_group_asset_yn VARCHAR2(3);
3043 l_group_by_contract_yn VARCHAR2(3);
3044 l_ilt_id NUMBER;
3045 l_cnr_id NUMBER;
3046 l_lln_id NUMBER;
3047 l_lsm_id NUMBER;
3048
3049 l_line_name VARCHAR2(150);
3050 l_ity_id NUMBER;
3051 l_format_type okl_invoice_types_v.name%TYPE;
3052
3053 l_sequence_number okl_invc_line_types_v.sequence_number%TYPE;
3054 l_cons_line_name VARCHAR2(150);
3055 l_stream_name VARCHAR2(150);
3056 i NUMBER;
3057 l_funct_return VARCHAR2(1);
3058
3059 l_cons_inv_num okl_cnsld_ar_hdrs_v.consolidated_invoice_number%TYPE;
3060 l_cnr_amount okl_cnsld_ar_hdrs_v.amount%TYPE;
3061 l_lln_amount okl_cnsld_ar_lines_v.amount%TYPE;
3062
3063 l_update_tbl update_tbl_type;
3064
3065 l_kle_id NUMBER;
3066 l_top_kle_id NUMBER;
3067 l_chr_id okc_k_lines_b.chr_id%TYPE;
3068 l_asset_name varchar2(2000);
3069 l_prev_khr_id NUMBER;
3070 l_asset_tbl asset_tbl;
3071
3072 CURSOR check_top_line ( p_cle_id NUMBER ) IS
3073 SELECT chr_id
3074 FROM okc_k_lines_b
3075 WHERE id = p_cle_id;
3076
3077 CURSOR top_line_asset ( p_cle_id NUMBER ) IS
3078 SELECT name
3079 FROM okc_k_lines_v
3080 WHERE id = p_cle_id;
3081
3082 CURSOR derive_top_line_id (p_lsm_id NUMBER) IS
3083 SELECT FA.ID
3084 FROM OKC_K_HEADERS_B CHR,
3085 OKC_K_LINES_B TOP_CLE,
3086 OKC_LINE_STYLES_b TOP_LSE,
3087 OKC_K_LINES_B SUB_CLE,
3088 OKC_LINE_STYLES_b SUB_LSE,
3089 OKC_K_ITEMS CIM,
3090 OKC_K_LINES_V FA,
3091 OKC_LINE_STYLES_B AST_LSE,
3092 OKL_CNSLD_AR_STRMS_B LSM
3093 WHERE
3094 CHR.ID = TOP_CLE.DNZ_CHR_ID AND
3095 TOP_CLE.LSE_ID = TOP_LSE.ID AND
3096 TOP_LSE.LTY_CODE IN('SOLD_SERVICE','FEE') AND
3097 TOP_CLE.ID = SUB_CLE.CLE_ID AND
3098 SUB_CLE.LSE_ID = SUB_LSE.ID AND
3099 SUB_LSE.LTY_CODE IN ('LINK_SERV_ASSET', 'LINK_FEE_ASSET') AND
3100 SUB_CLE.ID = LSM.KLE_ID AND
3101 LSM.ID = p_lsm_id AND
3102 CIM.CLE_ID = SUB_CLE.ID AND
3103 CIM.JTOT_OBJECT1_CODE = 'OKX_COVASST' AND
3104 CIM.OBJECT1_ID1 = FA.ID AND
3105 FA.LSE_ID = AST_LSE.ID AND
3106 AST_LSE.LTY_CODE = 'FREE_FORM1';
3107
3108
3109 CURSOR inv_format_csr ( p_format_id IN NUMBER, p_stream_id IN NUMBER ) IS
3110 SELECT
3111 inf.name inf_name,
3112 inf.contract_level_yn,
3113 ity.id ity_id,
3114 ity.name ity_name,
3115 ity.group_asset_yn,
3116 ity.group_by_contract_yn,
3117 ilt.id ilt_id,
3118 ilt.sequence_number,
3119 ilt.name ilt_name,
3120 sty.name sty_name
3121 FROM okl_invoice_formats_v inf,
3122 okl_invoice_types_v ity,
3123 okl_invc_line_types_v ilt,
3124 okl_invc_frmt_strms_v frs,
3125 okl_strm_type_v sty
3126 WHERE inf.id = p_format_id
3127 AND ity.inf_id = inf.id
3128 AND ilt.ity_id = ity.id
3129 AND frs.ilt_id = ilt.id
3130 AND sty.id = frs.sty_id
3131 AND frs.sty_id = p_stream_id;
3132
3133 CURSOR inv_format_default_csr ( p_format_id IN NUMBER ) IS
3134 SELECT
3135 inf.name inf_name,
3136 inf.contract_level_yn,
3137 ity.id ity_id,
3138 ity.name ity_name,
3139 ity.group_asset_yn,
3140 ity.group_by_contract_yn,
3141 ilt.id ilt_id,
3142 ilt.sequence_number,
3143 ilt.name ilt_name
3144 FROM okl_invoice_formats_v inf,
3145 okl_invoice_types_v ity,
3146 okl_invc_line_types_v ilt
3147 WHERE inf.id = p_format_id
3148 AND ity.inf_id = inf.id
3149 AND ilt.ity_id = ity.id
3150 AND inf.ilt_id = ilt.id;
3151
3152 l_cons_invoice_num OKL_CNSLD_AR_HDRS_B.CONSOLIDATED_INVOICE_NUMBER%TYPE;
3153 l_invoice_format OKL_INVOICE_FORMATS_V.NAME%TYPE;
3154 l_sty_name OKL_STRM_TYPE_V.NAME%TYPE;
3155
3156 l_old_cnr_id NUMBER;
3157 l_old_lln_id NUMBER;
3158 l_cnt NUMBER;
3159
3160 -- Get all Streams and cache locally
3161 CURSOR strm_csr_perf IS
3162 SELECT id, name
3163 FROM okl_strm_type_v;
3164
3165 TYPE stream_rec_type IS RECORD (
3166 id okl_strm_type_v.id%TYPE,
3167 name okl_strm_type_v.name%TYPE);
3168
3169 TYPE stream_table IS TABLE OF stream_rec_type
3170 INDEX BY BINARY_INTEGER;
3171
3172 l_stream_table stream_table;
3173
3174 -- Get invoice formats and cache locally
3175 CURSOR inv_format_csr_perf IS
3176 SELECT
3177 inf.id inf_id,
3178 inf.name inf_name,
3179 inf.contract_level_yn,
3180 ity.id ity_id,
3181 ity.name ity_name,
3182 ity.group_asset_yn,
3183 ity.group_by_contract_yn,
3184 ilt.id ilt_id,
3185 ilt.sequence_number,
3186 ilt.name ilt_name,
3187 sty.name sty_name,
3188 frs.sty_id sty_id
3189 FROM okl_invoice_formats_v inf,
3190 okl_invoice_types_v ity,
3191 okl_invc_line_types_v ilt,
3192 okl_invc_frmt_strms_v frs,
3193 okl_strm_type_v sty
3194 WHERE ity.inf_id = inf.id
3195 AND ilt.ity_id = ity.id
3196 AND frs.ilt_id = ilt.id
3197 AND sty.id = frs.sty_id
3198 UNION -- default invlice format
3199 SELECT
3200 inf.id inf_id,
3201 inf.name inf_name,
3202 inf.contract_level_yn,
3203 ity.id ity_id,
3204 ity.name ity_name,
3205 ity.group_asset_yn,
3206 ity.group_by_contract_yn,
3207 ilt.id ilt_id,
3208 ilt.sequence_number,
3209 ilt.name ilt_name,
3210 'DEFAULT FORMAT' sty_name,
3211 NULL
3212 FROM okl_invoice_formats_v inf,
3213 okl_invoice_types_v ity,
3214 okl_invc_line_types_v ilt
3215 WHERE ity.inf_id = inf.id
3216 AND ilt.ity_id = ity.id
3217 AND inf.ilt_id = ilt.id;
3218 TYPE inv_format_type IS RECORD (
3219 inf_id okl_invoice_formats_v.id%type,
3220 inf_name okl_invoice_formats_v.name%type,
3221 contract_level_yn okl_invoice_formats_v.contract_level_yn%type,
3222 ity_id okl_invoice_types_v.id%type,
3223 ity_name okl_invoice_types_v.name%type,
3224 group_asset_yn okl_invoice_types_v.group_asset_yn%type,
3225 group_by_contract_yn okl_invoice_types_v.group_by_contract_yn%type,
3226 ilt_id okl_invc_line_types_v.id%type,
3227 sequence_number okl_invc_line_types_v.sequence_number%type,
3228 ilt_name okl_invc_line_types_v.name%type,
3229 sty_name okl_strm_type_v.name%type,
3230 sty_id okl_invc_frmt_strms_v.sty_id%type);
3231
3232 TYPE inv_format_table IS TABLE OF inv_format_type
3233 INDEX BY BINARY_INTEGER;
3234
3235 l_inv_format_table inv_format_table;
3236 l_lln_tbl lln_tbl_type;
3237 l_line_counter NUMBER := 0;
3238 l_inf_id okl_invoice_formats_v.id%type;
3239 l_sty_id okl_invc_frmt_strms_v.sty_id%type;
3240 l_loop_counter NUMBER := 0;
3241 l_asset_counter NUMBER := 0;
3242
3243 -- fmiao - Bug#5232919 - Modified - Start
3244 -- query the effective dates of the invoice message to restrict the messages
3245 -- based on the consolidated invoice date
3246 CURSOR msg_csr_perf IS
3247 SELECT id, priority, pkg_name, proc_name
3248 , start_date, end_date
3249 FROM okl_invoice_mssgs_v;
3250 -- fmiao - Bug#5232919 - Modified - End
3251
3252 BEGIN
3253
3254 -- rmunjulu R12 Fixes -- comment out entire code and put NULL
3255 NULL;
3256 /*
3257 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
3258 PRINT_TO_LOG( 'Total rec count is : '||p_cons_bill_tbl.count);
3259
3260 g_cons_bill_tbl := p_cons_bill_tbl.count;
3261
3262 if p_cons_bill_tbl.count > 0 then
3263 if l_stream_table.COUNT = 0 then
3264 open strm_csr_perf;
3265 loop
3266 fetch strm_csr_perf bulk collect into l_stream_table;
3267 exit when strm_csr_perf%notfound;
3268 end loop;
3269 close strm_csr_perf;
3270 end if;
3271
3272 if l_inv_format_table.COUNT = 0 then
3273 open inv_format_csr_perf;
3274 loop
3275 fetch inv_format_csr_perf bulk collect into l_inv_format_table;
3276 exit when inv_format_csr_perf%notfound;
3277 end loop;
3278 close inv_format_csr_perf;
3279 end if;
3280
3281 -- Cache messages
3282 if g_msg_tbl.COUNT = 0 then
3283 open msg_csr_perf;
3284 loop
3285 fetch msg_csr_perf bulk collect into g_msg_tbl;
3286 exit when msg_csr_perf%notfound;
3287 end loop;
3288 close msg_csr_perf;
3289 end if;
3290 end if;
3291
3292
3293 FOR k IN p_cons_bill_tbl.FIRST..p_cons_bill_tbl.LAST LOOP
3294
3295 --MDOKAL
3296 if p_cons_bill_tbl(k).sty_id <> nvl(l_sty_id, 1) then
3297
3298 l_sty_name := NULL;
3299 for t in l_stream_table.first..l_stream_table.count loop
3300 if p_cons_bill_tbl(k).sty_id = l_stream_table(t).id then
3301 l_sty_name := l_stream_table(t).name;
3302 exit;
3303 end if;
3304 end loop;
3305 end if;
3306
3307 PRINT_TO_LOG( '*** CONSOLIDATION DETAILS ***');
3308 PRINT_TO_LOG( '*** PREVIOUS RECORD WAS FOR: ***');
3309 PRINT_TO_LOG( '*** ++++++++++++++++++++++++++++ ***');
3310 PRINT_TO_LOG( '* ====> CUSTOMER_ID: '||p_saved_bill_rec.l_customer_id||' CURRENCY: '||p_saved_bill_rec.l_currency);
3311 PRINT_TO_LOG( '* ====> BILL_TO_SITE: '||p_saved_bill_rec.l_bill_to_site||' PAYMENT_METHOD: '||p_saved_bill_rec.l_payment_method);
3312 PRINT_TO_LOG( '* ====> PRIVATE_LABEL: '||NVL(p_saved_bill_rec.l_private_label,'N/A')||' DATE_CONSOLIDATED: '||TRUNC(p_saved_bill_rec.l_date_consolidated));
3313 PRINT_TO_LOG( '* ====> CONTRACT_ID: '||p_saved_bill_rec.l_prev_khr_id||' INVOICE GROUP ID: '||p_saved_bill_rec.l_saved_format_id);
3314 PRINT_TO_LOG( '* ====> ORIGINAL CONS INV (For credit memos): '||p_saved_bill_rec.l_saved_prev_cons_num);
3315 PRINT_TO_LOG( '* ====> Overall Error Status: '||p_saved_bill_rec.l_overall_status);
3316 PRINT_TO_LOG( '*** ++++++++++++++++++++++++++++ ***');
3317
3318 PRINT_TO_LOG( '*** CURRENT RECORD IS FOR: ***');
3319 PRINT_TO_LOG( '*** ++++++++++++++++++++++++++++ ***');
3320 PRINT_TO_LOG( '* ====> CUSTOMER_ID: '||p_cons_bill_tbl(k).customer_id||' CURRENCY: '||p_cons_bill_tbl(k).currency);
3321 PRINT_TO_LOG( '* ====> BILL_TO_SITE: '||p_cons_bill_tbl(k).bill_to_site||' PAYMENT_METHOD: '||p_cons_bill_tbl(k).payment_method);
3322 PRINT_TO_LOG( '* ====> PRIVATE_LABEL: '||NVL(p_cons_bill_tbl(k).private_label,'N/A')||' DATE_CONSOLIDATED: '||TRUNC(p_cons_bill_tbl(k).date_consolidated));
3323 PRINT_TO_LOG( '* ====> CONTRACT_ID: '||p_cons_bill_tbl(k).contract_id||' INVOICE GROUP ID: '||p_cons_bill_tbl(k).inf_id);
3324 PRINT_TO_LOG( '* ====> ORIGINAL CONS INV (For credit memos): '||p_cons_bill_tbl(k).prev_cons_invoice_num);
3325 PRINT_TO_LOG( '*** ++++++++++++++++++++++++++++ ***');
3326 PRINT_TO_LOG( '*** END CONSOLIDATION DETAILS ***');
3327
3328 i:= 0;
3329 PRINT_TO_LOG( '====> Invoice Groups: Checking If Stream assigned to a Line Type.');
3330
3331 --MDOKAL
3332 -- check if the last invoice format is still the required format
3333 -- to prevent unecessary looping.
3334
3335 -- mdokal, Bug 4442702, nvl around parameters
3336 --vthiruva..Bug 4473916..05-JUL-05..modified AND to OR condition in IF clause
3337 if (nvl(p_cons_bill_tbl(k).inf_id , 0) <> nvl(l_inf_id, 1)
3338 or nvl(p_cons_bill_tbl(k).sty_id, 0) <> nvl(l_sty_id, 1)) then
3339
3340 if l_inv_format_table.exists(1) then
3341 for j in l_inv_format_table.first..l_inv_format_table.count loop
3342 if (nvl(p_cons_bill_tbl(k).inf_id, 0) = nvl(l_inv_format_table(j).inf_id, 1) and
3343 p_cons_bill_tbl(k).sty_id = l_inv_format_table(j).sty_id) then
3344 l_format_name := l_inv_format_table(j).inf_name;
3345 l_contract_level_yn := l_inv_format_table(j).contract_level_yn;
3346 l_ity_id := l_inv_format_table(j).ity_id;
3347 l_format_type := l_inv_format_table(j).ity_name;
3348 l_group_asset_yn := l_inv_format_table(j).group_asset_yn;
3349 l_group_by_contract_yn := l_inv_format_table(j).group_by_contract_yn;
3350 l_ilt_id := l_inv_format_table(j).ilt_id;
3351 l_sequence_number := l_inv_format_table(j).sequence_number;
3352 l_cons_line_name := l_inv_format_table(j).ilt_name;
3353 l_stream_name := l_inv_format_table(j).sty_name;
3354 l_inf_id := l_inv_format_table(j).inf_id;
3355 l_sty_id := l_inv_format_table(j).sty_id;
3356 i := i+1;
3357 exit;
3358 elsif (nvl(p_cons_bill_tbl(k).inf_id, 0) = nvl(l_inv_format_table(j).inf_id, 1))
3359 and i = 0
3360 and nvl(l_inv_format_table(j).sty_name, 'x') = 'DEFAULT FORMAT' then
3361 PRINT_TO_LOG( '====> Invoice Groups: Stream not assigned to a Line Type.');
3362 PRINT_TO_LOG( '====> Invoice Groups: Checking If Default Line Type exists. ');
3363 l_format_name := l_inv_format_table(j).inf_name;
3364 l_contract_level_yn := l_inv_format_table(j).contract_level_yn;
3365 l_ity_id := l_inv_format_table(j).ity_id;
3366 l_format_type := l_inv_format_table(j).ity_name;
3367 l_group_asset_yn := l_inv_format_table(j).group_asset_yn;
3368 l_group_by_contract_yn := l_inv_format_table(j).group_by_contract_yn;
3369 l_ilt_id := l_inv_format_table(j).ilt_id;
3370 l_sequence_number := l_inv_format_table(j).sequence_number;
3371 l_cons_line_name := l_inv_format_table(j).ilt_name;
3372 l_stream_name := null;
3373 l_inf_id := l_inv_format_table(j).inf_id;
3374 l_sty_id := l_inv_format_table(j).sty_id;
3375 exit;
3376 else
3377 l_format_name := null;
3378 l_contract_level_yn := null;
3379 l_ity_id := null;
3380 l_format_type := null;
3381 l_group_asset_yn := null;
3382 l_group_by_contract_yn := null;
3383 l_ilt_id := null;
3384 l_sequence_number := null;
3385 l_cons_line_name := null;
3386 l_stream_name := null;
3387 l_inf_id := null;
3388 l_sty_id := null;
3389 end if;
3390 end loop;
3391 end if;
3392 end if;
3393
3394 PRINT_TO_LOG( '*** Qualifying Invoice Group Details ***');
3395 PRINT_TO_LOG( '* ====> NAME: '||l_format_name);
3396 PRINT_TO_LOG( '* ====> CONTRACT_LEVEL_YN: '||l_contract_level_yn);
3397 PRINT_TO_LOG( '* ====> INVOICE TYPE NAME: '||l_format_type||' With Id of: '||l_ity_id);
3398 PRINT_TO_LOG( '* ====> GROUP_ASSET_YN: '||l_group_asset_yn);
3399 PRINT_TO_LOG( '* ====> LINE NAME: '||l_cons_line_name||' With Id of: '||l_ilt_id);
3400 PRINT_TO_LOG( '* ====> SEQUENCE NUMBER: '||l_sequence_number);
3401 PRINT_TO_LOG( '*** End Invoice Group Details ***');
3402
3403 IF ( (p_cons_bill_tbl(k).customer_id = p_saved_bill_rec.l_customer_id)
3404 AND (p_cons_bill_tbl(k).currency = p_saved_bill_rec.l_currency)
3405 AND (p_cons_bill_tbl(k).bill_to_site = p_saved_bill_rec.l_bill_to_site)
3406 AND (NVL(p_cons_bill_tbl(k).payment_method,-999)= NVL(p_saved_bill_rec.l_payment_method,-999))
3407 AND (NVL(p_cons_bill_tbl(k).private_label,'N/A') = NVL(p_saved_bill_rec.l_private_label,'N/A'))
3408 AND (TRUNC(p_cons_bill_tbl(k).date_consolidated) = TRUNC(p_saved_bill_rec.l_date_consolidated) )
3409 AND (p_cons_bill_tbl(k).ity_id = p_saved_bill_rec.l_saved_ity_id) --bug 5138822
3410 AND (p_cons_bill_tbl(k).inf_id = p_saved_bill_rec.l_saved_format_id)
3411 AND (p_cons_bill_tbl(k).prev_cons_invoice_num = p_saved_bill_rec.l_saved_prev_cons_num)
3412 )
3413 THEN
3414 PRINT_TO_LOG( '====> No Break Detected, Check Contract Level YN: '||l_contract_level_yn);
3415 -- -------------------------------------------------------------------
3416 -- Check multi-contract invoices
3417 -- -------------------------------------------------------------------
3418 IF ( p_saved_bill_rec.l_prev_khr_id <> p_cons_bill_tbl(k).contract_id ) THEN
3419
3420 IF (l_contract_level_yn = 'Y') THEN
3421 PRINT_TO_LOG( '====> Reusing CNR_ID, as Contract Level YN is Y : '||p_saved_bill_rec.l_cnr_id);
3422 ELSE
3423 -- ---------------------------
3424 -- Process Header Break Logic
3425 -- ---------------------------
3426
3427 process_break(p_contract_number,
3428 p_commit,
3429 p_saved_bill_rec,
3430 p_update_tbl);
3431
3432 -- Reset update table after processing
3433 p_update_tbl := l_update_tbl;
3434
3435 -- ------------------------------------
3436 -- Finish post header break detection logic
3437 -- ------------------------------------
3438
3439 PRINT_TO_LOG( '====> Create new Invoice as Contract Level YN is N.');
3440 l_cnr_id := NULL;
3441
3442 PRINT_TO_LOG( '*** CREATE CONSOLIDATED INVOICE HEADER ***');
3443 l_cons_inv_num := NULL;
3444
3445 create_new_invoice(
3446 p_cons_bill_tbl(k).bill_to_site,
3447 p_cons_bill_tbl(k).customer_id,
3448 p_cons_bill_tbl(k).currency,
3449 p_cons_bill_tbl(k).payment_method,
3450 p_cons_bill_tbl(k).inf_id,
3451 p_cons_bill_tbl(k).set_of_books_id,
3452 p_cons_bill_tbl(k).private_label,
3453 p_cons_bill_tbl(k).date_consolidated,
3454 p_cons_bill_tbl(k).org_id,
3455 p_cons_bill_tbl(k).legal_entity_id, -- for LE Uptake project 08-11-2006
3456 g_last_rec,
3457 l_cnr_id,
3458 l_cons_inv_num);
3459
3460 p_saved_bill_rec.l_cnr_id := l_cnr_id;
3461 p_saved_bill_rec.l_cons_inv_num := l_cons_inv_num;
3462
3463 PRINT_TO_LOG( '*** DONE CREATION OF CONSOLIDATED INVOICE HEADER.Assigned Id: '||l_cnr_id||' ***'||'p_saved_bill_rec.l_cons_inv_num: '||p_saved_bill_rec.l_cons_inv_num );
3464 END IF;
3465 ELSE
3466 PRINT_TO_LOG( '====> Reusing CNR_ID (Same Contract) : '||l_cnr_id);
3467 END IF;
3468
3469 PRINT_TO_LOG( '*** ++++++++++++++++++++++++++++ ***');
3470 PRINT_TO_LOG( '*** CHECK IF A CONSOLIDATED LINE EXISTS ***');
3471 l_lln_id := NULL;
3472 line_exist (l_cnr_id,
3473 p_cons_bill_tbl(k).contract_id,
3474 p_cons_bill_tbl(k).kle_id,
3475 l_ilt_id,
3476 l_sequence_number,
3477 l_group_by_contract_yn,
3478 l_group_asset_yn,
3479 l_lln_id,
3480 l_funct_return
3481 );
3482
3483 p_saved_bill_rec.l_lln_id := l_lln_id;
3484
3485 PRINT_TO_LOG( '*** END CHECK FOR CONSOLIDATED LINE ***');
3486 PRINT_TO_LOG( '*** ++++++++++++++++++++++++++++ ***');
3487
3488 IF l_funct_return = 'N' THEN
3489 -- -----------------------------------------------------
3490 -- Line break detected, update LLN record with amount
3491 -- -----------------------------------------------------
3492
3493 PRINT_TO_LOG( '*** CREATE CONSOLIDATED INVOICE LINE *** for CNR_ID: '||l_cnr_id);
3494 l_lln_id := NULL;
3495 l_cnr_id := p_saved_bill_rec.l_cnr_id;
3496
3497 create_new_line(
3498 p_cons_bill_tbl(k).contract_id,
3499 l_cnr_id,
3500 p_cons_bill_tbl(k).kle_id,
3501 l_ilt_id,
3502 p_cons_bill_tbl(k).currency,
3503 l_sequence_number,
3504 'CHARGE',
3505 l_group_by_contract_yn,
3506 l_group_asset_yn,
3507 l_contract_level_yn,
3508 l_lln_id
3509 );
3510
3511 p_saved_bill_rec.l_lln_id := l_lln_id;
3512 PRINT_TO_LOG( '*** DONE CREATION OF CONSOLIDATED INVOICE LINE.Assigned Id: '||l_lln_id||' ***');
3513 END IF;
3514 ELSE -- 'ELSE' for the Uppermost level 'IF' for hierarchy checks
3515
3516 -- ------------------------------------
3517 -- Start header break detection logic
3518 -- ------------------------------------
3519
3520 process_break(p_contract_number,
3521 p_commit,
3522 p_saved_bill_rec,
3523 p_update_tbl);
3524
3525 -- Reset update table after processing
3526 p_update_tbl := l_update_tbl;
3527
3528 -- ------------------------------------
3529 -- Finish post header break detection logic
3530 -- ------------------------------------
3531
3532 -- -----------------------------------
3533 -- Break detected
3534 -- -----------------------------------
3535 PRINT_TO_LOG( '====> Break Detected.');
3536 PRINT_TO_LOG( '*** CREATE CONSOLIDATED INVOICE HEADER ***');
3537 -- Null out current value in local variable.
3538 l_cnr_id := NULL;
3539 l_cons_inv_num := NULL;
3540
3541 create_new_invoice(
3542 p_cons_bill_tbl(k).bill_to_site,
3543 p_cons_bill_tbl(k).customer_id,
3544 p_cons_bill_tbl(k).currency,
3545 p_cons_bill_tbl(k).payment_method,
3546 p_cons_bill_tbl(k).inf_id,
3547 p_cons_bill_tbl(k).set_of_books_id,
3548 p_cons_bill_tbl(k).private_label,
3549 p_cons_bill_tbl(k).date_consolidated,
3550 p_cons_bill_tbl(k).org_id,
3551 p_cons_bill_tbl(k).legal_entity_id, -- for LE Uptake project 08-11-2006
3552 g_last_rec,
3553 l_cnr_id,
3554 l_cons_inv_num);
3555
3556 p_saved_bill_rec.l_cnr_id := l_cnr_id;
3557 p_saved_bill_rec.l_cons_inv_num := l_cons_inv_num;
3558
3559 PRINT_TO_LOG( '*** DONE CREATION OF CONSOLIDATED INVOICE HEADER.Assigned Id: '||l_cnr_id||' ***'||'p_saved_bill_rec.l_cons_inv_num: '||p_saved_bill_rec.l_cons_inv_num );
3560
3561 PRINT_TO_LOG( '*** CREATE CONSOLIDATED INVOICE LINE *** for CNR_ID '||l_cnr_id);
3562 -- Null out current value in local variable.
3563 l_lln_id := NULL;
3564
3565 l_cnr_id := p_saved_bill_rec.l_cnr_id;
3566 create_new_line(
3567 p_cons_bill_tbl(k).contract_id,
3568 l_cnr_id,
3569 p_cons_bill_tbl(k).kle_id,
3570 l_ilt_id,
3571 p_cons_bill_tbl(k).currency,
3572 l_sequence_number,
3573 'CHARGE',
3574 l_group_by_contract_yn,
3575 l_group_asset_yn,
3576 l_contract_level_yn,
3577 l_lln_id);
3578
3579 p_saved_bill_rec.l_lln_id := l_lln_id;
3580
3581 PRINT_TO_LOG( '*** DONE CREATION OF CONSOLIDATED INVOICE LINE.Assigned Id: '||l_lln_id||' ***');
3582 END IF;
3583 PRINT_TO_LOG( '*** CREATE CONSOLIDATED INVOICE STREAMS *** for CNR_ID: '||l_cnr_id||' and LLN_ID: '||l_lln_id);
3584 --Null out local variable.
3585 l_lsm_id := null;
3586
3587 l_lln_id := p_saved_bill_rec.l_lln_id;
3588
3589 create_new_streams(
3590 l_lln_id,
3591 p_cons_bill_tbl(k).sty_id,
3592 p_cons_bill_tbl(k).kle_id,
3593 p_cons_bill_tbl(k).contract_id,
3594 p_cons_bill_tbl(k).amount,
3595 p_cons_bill_tbl(k).sel_id,
3596 l_cnr_id,
3597 l_lsm_id,
3598 x_return_status);
3599
3600 PRINT_TO_LOG( '*** DONE CREATION OF CONSOLIDATED INVOICE STREAMS.Assigned Id: '||l_lsm_id||' ***');
3601
3602
3603 --Set local variables to cursor values for
3604 --comparison purposes
3605 p_saved_bill_rec.l_customer_id := p_cons_bill_tbl(k).customer_id;
3606 p_saved_bill_rec.l_currency := p_cons_bill_tbl(k).currency;
3607 p_saved_bill_rec.l_bill_to_site := p_cons_bill_tbl(k).bill_to_site;
3608 p_saved_bill_rec.l_payment_method := p_cons_bill_tbl(k).payment_method;
3609 p_saved_bill_rec.l_private_label := p_cons_bill_tbl(k).private_label;
3610 p_saved_bill_rec.l_date_consolidated := p_cons_bill_tbl(k).date_consolidated;
3611 p_saved_bill_rec.l_saved_ity_id := p_cons_bill_tbl(k).ity_id; -- 5138822
3612 p_saved_bill_rec.l_saved_format_id := p_cons_bill_tbl(k).inf_id;
3613 p_saved_bill_rec.l_prev_khr_id := p_cons_bill_tbl(k).contract_id;
3614 p_saved_bill_rec.l_saved_prev_cons_num := p_cons_bill_tbl(k).prev_cons_invoice_num;
3615 p_saved_bill_rec.l_commit_cnt := NVL(p_saved_bill_rec.l_commit_cnt,0) + 1;
3616
3617 -- -----------------------
3618 -- Work out asset name
3619 -- -----------------------
3620 l_chr_id := NULL;
3621
3622 --MDOKAL
3623
3624 l_asset_name := NULL;
3625
3626 IF p_cons_bill_tbl(k).contract_id = p_saved_bill_rec.l_prev_khr_id then
3627
3628 if l_asset_tbl.count > 0 then
3629 for l in l_asset_tbl.FIRST..l_asset_tbl.LAST loop
3630 if l_asset_tbl(l).id = p_cons_bill_tbl(k).kle_id then
3631 l_asset_name := l_asset_tbl(l).name;
3632 exit;
3633 end if;
3634 end loop;
3635 end if;
3636 ELSE
3637 l_asset_tbl.delete;
3638 END IF;
3639
3640 if l_asset_name is null then
3641 OPEN check_top_line( p_cons_bill_tbl(k).kle_id );
3642 FETCH check_top_line INTO l_chr_id;
3643 CLOSE check_top_line;
3644
3645 IF l_chr_id IS NOT NULL THEN
3646 l_kle_id := p_cons_bill_tbl(k).kle_id;
3647 ELSE
3648 l_top_kle_id := NULL;
3649 OPEN derive_top_line_id ( l_lsm_id );
3650 FETCH derive_top_line_id INTO l_top_kle_id;
3651 CLOSE derive_top_line_id;
3652 l_kle_id := l_top_kle_id;
3653 END IF;
3654
3655 l_asset_name := NULL;
3656 OPEN top_line_asset ( l_kle_id );
3657 FETCH top_line_asset INTO l_asset_name;
3658 CLOSE top_line_asset;
3659
3660 l_asset_counter := l_asset_counter + 1;
3661 l_asset_tbl(l_asset_counter).id := p_cons_bill_tbl(k).kle_id;
3662 l_asset_tbl(l_asset_counter).name := l_asset_name;
3663
3664 end if;
3665
3666 -- --------------------------
3667 -- Index counter
3668 -- --------------------------
3669 l_cnt := p_update_tbl.count;
3670 l_cnt := l_cnt + 1;
3671
3672 PRINT_TO_LOG( 'DEL Updates (p_saved_bill_rec.l_cons_inv_num)'||p_saved_bill_rec.l_cons_inv_num);
3673 PRINT_TO_LOG( 'DEL Updates (l_format_name)'||l_format_name);
3674
3675 p_update_tbl(l_cnt).cnr_id := p_saved_bill_rec.l_cnr_id;
3676 p_update_tbl(l_cnt).cons_inv_number := p_saved_bill_rec.l_cons_inv_num;
3677 p_update_tbl(l_cnt).lln_id := p_saved_bill_rec.l_lln_id;
3678 p_update_tbl(l_cnt).lsm_id := l_lsm_id;
3679 p_update_tbl(l_cnt).asset_number := l_asset_name;
3680 p_update_tbl(l_cnt).invoice_format := l_format_name;
3681 p_update_tbl(l_cnt).line_type := l_cons_line_name;
3682 p_update_tbl(l_cnt).sty_name := l_sty_name;
3683 p_update_tbl(l_cnt).contract_number := p_cons_bill_tbl(k).contract_number;
3684
3685 -- Start; Bug 4525643; STMATHEW
3686 p_update_tbl(l_cnt).private_label := p_cons_bill_tbl(k).private_label;
3687 -- End; Bug 4525643; STMATHEW
3688
3689 p_update_tbl(l_cnt).lsm_amount := p_cons_bill_tbl(k).amount;
3690 p_update_tbl(l_cnt).xsi_id := p_cons_bill_tbl(k).xsi_id;
3691 p_update_tbl(l_cnt).xls_id := p_cons_bill_tbl(k).xls_id;
3692
3693 if p_saved_bill_rec.l_cnr_id = l_old_cnr_id then
3694 p_update_tbl(l_cnt).cnr_total := p_update_tbl(l_cnt).cnr_total + p_cons_bill_tbl(k).amount;
3695 else
3696 p_update_tbl(l_cnt).cnr_total := p_cons_bill_tbl(k).amount;
3697 l_old_cnr_id := p_saved_bill_rec.l_cnr_id;
3698 end if;
3699
3700 if p_saved_bill_rec.l_lln_id = l_old_lln_id then
3701 p_update_tbl(l_cnt).lln_total := p_update_tbl(l_cnt).lln_total + p_cons_bill_tbl(k).amount;
3702 else
3703 p_update_tbl(l_cnt).lln_total := p_cons_bill_tbl(k).amount;
3704 l_old_lln_id := p_saved_bill_rec.l_lln_id;
3705 end if;
3706
3707 END LOOP;
3708
3709 */
3710 EXCEPTION
3711 ------------------------------------------------------------
3712 -- Exception handling
3713 ------------------------------------------------------------
3714
3715 WHEN OTHERS THEN
3716 IF (L_DEBUG_ENABLED='Y' and FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3717 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'okl_cons_bill',
3718 'EXCEPTION :'||'OTHERS');
3719 END IF;
3720
3721 PRINT_TO_LOG('*=> Error Message(O3): '||SQLERRM);
3722 x_return_status := 'E';
3723
3724 END process_cons_bill_tbl;
3725
3726 PROCEDURE create_cons_bill(
3727 p_contract_number IN VARCHAR2,
3728 p_api_version IN NUMBER,
3729 p_init_msg_list IN VARCHAR2,
3730 p_commit IN VARCHAR2,
3731 p_inv_msg IN VARCHAR2,
3732 p_assigned_process IN VARCHAR2,
3733 x_return_status OUT NOCOPY VARCHAR2,
3734 x_msg_count OUT NOCOPY NUMBER,
3735 x_msg_data OUT NOCOPY VARCHAR2)
3736
3737 IS
3738
3739 -- ------------------------------------------------------------------------
3740 -- Cursor for consolidated invoices having headers, lines and details for
3741 -- a txn. Excludes UBB records, Quote and Collections records
3742 -- ------------------------------------------------------------------------
3743 CURSOR C IS
3744 --start modified abhsaxen for performance SQLID 20563033
3745 SELECT
3746 xsib.customer_id customer_id,
3747 xsib.currency_code currency,
3748 xsib.customer_address_id bill_to_site,
3749 xsib.receipt_method_id payment_method,
3750 xsit.xtrx_private_label private_label,
3751 TRUNC(xsiB.TRX_DATE) date_consolidated,
3752 NVL(
3753 nvl(
3754 (SELECT ity.id ity_id
3755 FROM okl_invoice_types_b ity,
3756 okl_invc_line_types_b ilt,
3757 okl_invc_frmt_strms frs
3758 WHERE ity.inf_id = inf.id
3759 AND ilt.ity_id = ity.id
3760 AND frs.ilt_id = ilt.id
3761 AND tld.sty_id = frs.sty_id),
3762 (select ity1.id
3763 from okl_invoice_types_b ity1,
3764 okl_invc_line_types_b ilt1
3765 where ilt1.id = inf.ilt_id
3766 and ilt1.ity_id = ity1.id)), -1
3767 ) inv_type,
3768 xsib.inf_id inf_id,
3769 tai.khr_id contract_id,
3770 chr.contract_number contract_number,
3771 '-1' prev_cons_invoice_num,
3772 xsib.org_id org_id,
3773 xsib.set_of_books_id set_of_books_id,
3774 til.kle_id kle_id,
3775 tld.sty_id stream_id,
3776 xsib.id xsi_id,
3777 xls.id xls_id,
3778 xls.amount c_amount,
3779 xls.sel_id sel_id,
3780 xsib.legal_entity_id legal_entity_id -- for LE Uptake project 08-11-2006
3781 FROM
3782 okl_ext_sell_invs_b xsib,
3783 okl_ext_sell_invs_tl xsit,
3784 okl_xtl_sell_invs_b xls,
3785 okl_txd_ar_ln_dtls_b tld,
3786 okl_txl_ar_inv_lns_b til,
3787 okl_trx_ar_invoices_b tai,
3788 okc_k_headers_b chr,
3789 okl_invoice_formats_b inf, -- 5138822
3790 okl_parallel_processes pws
3791 WHERE
3792 xsiB.TRX_STATUS_CODE = 'SUBMITTED' AND
3793 xls.xsi_id_details = xsiB.id AND
3794 tld.id = xls.tld_id AND
3795 til.id = tld.TIL_ID_DETAILS AND
3796 tai.id = til.tai_id AND
3797 tai.khr_id = chr.id
3798 AND
3799 chr.contract_number = NVL(p_contract_number,chr.contract_number) AND
3800 tai.clg_id IS NULL AND
3801 tai.cpy_id IS NULL AND
3802 tai.qte_id IS NULL AND
3803 xls.amount >=0 AND
3804 xsiB.inf_id = inf.id (+) AND
3805 pws.object_type = 'CUSTOMER' AND
3806 XSIB.CUSTOMER_ID = to_number(pws.object_value) AND
3807 pws.assigned_process = p_assigned_process AND
3808 XSIB.ID = XSIT.ID
3809 and XSIT.LANGUAGE = USERENV('LANG')
3810 UNION
3811 SELECT
3812 xsib.customer_id customer_id,
3813 xsib.currency_code currency,
3814 xsib.customer_address_id bill_to_site,
3815 xsib.receipt_method_id payment_method,
3816 xsit.xtrx_private_label private_label,
3817 TRUNC(xsib.TRX_DATE) date_consolidated,
3818 NVL(
3819 NVL((SELECT ity.id ity_id
3820 FROM okl_invoice_types_b ity,
3821 okl_invc_line_types_b ilt,
3822 okl_invc_frmt_strms frs
3823 WHERE ity.inf_id = inf.id
3824 AND ilt.ity_id = ity.id
3825 AND frs.ilt_id = ilt.id
3826 AND tld.sty_id = frs.sty_id),
3827 (select ity1.id from okl_invoice_types_b ity1,
3828 okl_invc_line_types_b ilt1
3829 where ilt1.id = inf.ilt_id
3830 and ilt1.ity_id = ity1.id)), -1
3831 ) inv_type, -- bug 5138822
3832 xsib.inf_id inf_id,
3833 tai.khr_id contract_id, -- get contract Id
3834 chr.contract_number contract_number,
3835 '-1' prev_cons_invoice_num,
3836 xsib.org_id org_id,
3837 xsib.set_of_books_id set_of_books_id,
3838 til.kle_id kle_id,
3839 tld.sty_id stream_id, -- to get the line seq #
3840 xsib.id xsi_id,
3841 xls.id xls_id,
3842 xls.amount c_amount,
3843 xls.sel_id sel_id,
3844 xsib.legal_entity_id legal_entity_id -- for LE Uptake project 08-11-2006
3845 FROM
3846 okl_ext_sell_invs_b xsib,
3847 okl_ext_sell_invs_tl xsit,
3848 okl_xtl_sell_invs_b xls,
3849 okl_txd_ar_ln_dtls_b tld,
3850 okl_txl_ar_inv_lns_b til,
3851 okl_trx_ar_invoices_b tai,
3852 okc_k_headers_b chr,
3853 okl_invoice_formats_b inf -- 5138822
3854 WHERE
3855 xsib.TRX_STATUS_CODE = 'SUBMITTED' AND
3856 xls.xsi_id_details = xsib.id AND
3857 tld.id = xls.tld_id AND
3858 til.id = tld.TIL_ID_DETAILS AND
3859 tai.id = til.tai_id AND
3860 tai.khr_id = chr.id
3861 AND
3862 chr.contract_number = p_contract_number AND
3863 tai.clg_id IS NULL AND
3864 tai.cpy_id IS NULL AND
3865 tai.qte_id IS NULL AND
3866 xls.amount >= 0 AND
3867 xsib.inf_id = inf.id (+) and
3868 XSIB.ID = XSIT.ID and
3869 XSIT.LANGUAGE = USERENV('LANG')
3870 ORDER BY 1,2,3,4,5,6,7,8,9,10
3871 --end modified abhsaxen for performance SQLID 20563033
3872 ;
3873 ---------------------------------------------------------------------------
3874 -- Cursor for consolidated invoices having only headers and lines for a txn
3875 -- Excludes UBB records, Quote and Collections records
3876 ---------------------------------------------------------------------------
3877 CURSOR c1 IS SELECT
3878 xsi.customer_id customer_id,
3879 xsi.currency_code currency,
3880 xsi.customer_address_id bill_to_site,
3881 xsi.receipt_method_id payment_method,
3882 xsi.xtrx_private_label private_label,
3883 TRUNC(xsi.TRX_DATE) date_consolidated,
3884 NVL(
3885 NVL((SELECT ity.id ity_id
3886 FROM okl_invoice_types_b ity,
3887 okl_invc_line_types_b ilt,
3888 okl_invc_frmt_strms frs
3889 WHERE ity.inf_id = inf.id
3890 AND ilt.ity_id = ity.id
3891 AND frs.ilt_id = ilt.id
3892 AND til.sty_id = frs.sty_id),
3893 (select ity1.id from okl_invoice_types_b ity1,
3894 okl_invc_line_types_b ilt1
3895 where ilt1.id = inf.ilt_id
3896 and ilt1.ity_id = ity1.id)), -1
3897 ) inv_type, -- bug 5138822
3898 xsi.inf_id inf_id,
3899 tai.khr_id contract_id,
3900 chr.contract_number contract_number,
3901 '-1' prev_cons_invoice_num,
3902 xsi.org_id org_id,
3903 xsi.set_of_books_id set_of_books_id,
3904 til.kle_id kle_id,
3905 til.sty_id stream_id,
3906 xsi.id xsi_id,
3907 xls.id xls_id,
3908 xls.amount c1_amount,
3909 xls.sel_id sel_id,
3910 xsi.legal_entity_id legal_entity_id -- for LE Uptake project 08-11-2006
3911 FROM
3912 okl_ext_sell_invs_v xsi,
3913 okl_xtl_sell_invs_v xls,
3914 okl_txl_ar_inv_lns_v til,
3915 okl_trx_ar_invoices_v tai,
3916 okc_k_headers_b chr,
3917 okl_invoice_formats_b inf, -- 5138822
3918 okl_parallel_processes pws
3919 WHERE
3920 xsi.TRX_STATUS_CODE = 'SUBMITTED' AND
3921 xls.xsi_id_details = xsi.id AND
3922 til.id = xls.til_id AND
3923 tai.id = til.tai_id AND
3924 tai.khr_id = chr.id AND
3925 -- Contract Specific consolidation
3926 chr.contract_number = NVL(p_contract_number,chr.contract_number) AND
3927 -- Contract Specific consolidation
3928 tai.clg_id IS NULL AND
3929 tai.cpy_id IS NULL AND
3930 tai.qte_id IS NULL AND
3931 xls.amount >= 0 AND
3932 xsi.inf_id = inf.id (+) AND -- 5138822
3933 pws.object_type = 'CUSTOMER' AND
3934 XSI.CUSTOMER_ID = to_number(pws.object_value) AND
3935 pws.assigned_process = p_assigned_process
3936 ORDER BY 1,2,3,4,5,6,7,8,9,10;
3937
3938 -- BUG#4621302
3939 -- cursor to fetch the installed languages
3940 CURSOR get_languages IS
3941 SELECT language_code
3942 FROM FND_LANGUAGES
3943 WHERE INSTALLED_FLAG IN ('I', 'B');
3944
3945 TYPE lang_tbl_type IS TABLE OF get_languages%ROWTYPE INDEX BY BINARY_INTEGER;
3946 l_lang_tbl lang_tbl_type;
3947 lang_count NUMBER;
3948 tl_count NUMBER;
3949
3950 -- Billing performance fix
3951 cons_bill_tbl cons_bill_tbl_type;
3952 saved_bill_rec saved_bill_rec_type;
3953 l_init_bill_rec saved_bill_rec_type;
3954
3955 l_update_tbl update_tbl_type;
3956
3957 -- ssiruvol - Bug#5354130 - Added - Start
3958 -- temporary records for storing CNR table of records
3959 l_cnr_tbl cnr_tbl_type;
3960 -- ssiruvol - Bug#5354130 - Added - End
3961
3962 L_FETCH_SIZE NUMBER := 5000;
3963
3964 l_cons_inv_num okl_cnsld_ar_hdrs_v.consolidated_invoice_number%TYPE;
3965 -- Billing performance fix
3966
3967 CURSOR line_seq_csr(p_cnr_id NUMBER) IS
3968 SELECT *
3969 FROM okl_cnsld_ar_lines_v
3970 WHERE cnr_id = p_cnr_id
3971 ORDER BY sequence_number;
3972
3973 l_cnr_id NUMBER;
3974 l_lln_id NUMBER;
3975 l_lsm_id NUMBER;
3976 l_seq_num NUMBER;
3977
3978
3979 l_line_amount NUMBER;
3980 l_consbill_amount NUMBER;
3981
3982 TYPE cnr_update_rec_type IS RECORD (
3983 cnr_id NUMBER,
3984 lln_id NUMBER,
3985 lsm_id NUMBER,
3986 xsi_id NUMBER,
3987 xls_id NUMBER,
3988 return_status VARCHAR2(1)
3989 );
3990
3991 TYPE cnr_update_tbl_type IS TABLE OF cnr_update_rec_type
3992 INDEX BY BINARY_INTEGER;
3993
3994 cnr_update_tbl cnr_update_tbl_type;
3995 cnr_tab_idx NUMBER;
3996
3997 -- In and Out records for the external sell invoice tables
3998 l_xsiv_rec Okl_Xsi_Pvt.xsiv_rec_type;
3999 x_xsiv_rec Okl_Xsi_Pvt.xsiv_rec_type;
4000 null_xsiv_rec Okl_Xsi_Pvt.xsiv_rec_type;
4001
4002 l_xlsv_rec Okl_Xls_Pvt.xlsv_rec_type;
4003 x_xlsv_rec Okl_Xls_Pvt.xlsv_rec_type;
4004 null_xlsv_rec Okl_Xls_Pvt.xlsv_rec_type;
4005
4006 -- For Updating header and line amnounts and sequences
4007 u_cnrv_rec Okl_Cnr_Pvt.cnrv_rec_type;
4008 x_cnrv_rec Okl_Cnr_Pvt.cnrv_rec_type;
4009 null_cnrv_rec Okl_Cnr_Pvt.cnrv_rec_type;
4010
4011 u_llnv_rec Okl_Lln_Pvt.llnv_rec_type;
4012 x_llnv_rec Okl_Lln_Pvt.llnv_rec_type;
4013 null_llnv_rec Okl_Lln_Pvt.llnv_rec_type;
4014
4015 --All the below variables for a successful rules invocation
4016 l_rul_format_name OKC_RULES_B.RULE_INFORMATION1%TYPE;
4017 l_init_msg_list VARCHAR2(1) ;
4018 l_msg_count NUMBER ;
4019 l_msg_data VARCHAR2(2000);
4020 l_rulv_rec Okl_Rule_Apis_Pvt.rulv_rec_type;
4021 null_rulv_rec Okl_Rule_Apis_Pvt.rulv_rec_type;
4022
4023 ------------------------------------------------------------
4024 -- Declare variables required by UBB Billing Consolidation
4025 ------------------------------------------------------------
4026 l_clg_id NUMBER;
4027
4028 ------------------------------------------------------------
4029 -- Declare variables required by Termination Quote Billing
4030 ------------------------------------------------------------
4031 l_qte_id NUMBER := -1;
4032
4033 l_qte_cust_id okl_ext_sell_invs_v.Customer_id%TYPE;
4034 ------------------------------------------------------------
4035 -- Declare variables required by Collections Billing
4036 ------------------------------------------------------------
4037 l_cpy_id NUMBER := -1;
4038
4039 ------------------------------------------------------------
4040 -- Declare variables required by APIs
4041 ------------------------------------------------------------
4042
4043 l_api_version CONSTANT NUMBER := 1;
4044 l_api_name CONSTANT VARCHAR2(30) := 'CONSOLIDATED BILLING';
4045 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
4046
4047 CURSOR cntrct_csr ( p_id NUMBER ) IS
4048 SELECT contract_number
4049 FROM okc_k_headers_b
4050 WHERE id = p_id;
4051
4052 CURSOR strm_csr ( p_id NUMBER ) IS
4053 SELECT name
4054 FROM okl_strm_type_v
4055 WHERE id = p_id;
4056
4057 l_contract_number okc_k_headers_b.contract_number%TYPE;
4058 l_stream_name1 okl_strm_type_v.name%TYPE;
4059
4060
4061 l_temp_khr_id NUMBER;
4062
4063 CURSOR get_khr_id ( p_lsm_id NUMBER ) IS
4064 SELECT khr_id
4065 FROM okl_cnsld_ar_strms_b
4066 WHERE id = p_lsm_id;
4067
4068
4069 -- Variable to track commit record size
4070 l_commit_cnt NUMBER;
4071
4072 -- --------------------------------------------------------
4073 -- To Print log messages
4074 -- --------------------------------------------------------
4075 l_request_id NUMBER;
4076
4077 CURSOR req_id_csr IS
4078 SELECT
4079 DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID)
4080 FROM dual;
4081
4082 ------------------------------------------------------------
4083 -- Operating Unit
4084 ------------------------------------------------------------
4085 CURSOR op_unit_csr IS
4086 SELECT NAME
4087 FROM hr_operating_units
4088 WHERE ORGANIZATION_ID=MO_GLOBAL.GET_CURRENT_ORG_ID;--MOAC- Concurrent request
4089
4090
4091 l_succ_cnt NUMBER;
4092 l_err_cnt NUMBER;
4093 l_op_unit_name hr_operating_units.name%TYPE;
4094 lx_msg_data VARCHAR2(450);
4095 l_msg_index_out NUMBER :=0;
4096 processed_sts okl_cnsld_ar_hdrs_v.trx_status_code%TYPE;
4097 error_sts okl_cnsld_ar_hdrs_v.trx_status_code%TYPE;
4098
4099 -- -----------------------------
4100 -- New fields
4101 -- -----------------------------
4102 l_old_cnr_id NUMBER;
4103 l_old_lln_id NUMBER;
4104 l_cnr_amount okl_cnsld_ar_hdrs_v.amount%TYPE;
4105 l_lln_amount okl_cnsld_ar_lines_v.amount%TYPE;
4106
4107 x_cnrv_tbl Okl_Cnr_Pvt.cnrv_tbl_type;
4108
4109 -- fmiao - Bug#5232919 - Modified - Start
4110 -- Commenting the decalration of Invoice Message Attribute Records as
4111 -- henceforth these are tracked as global records
4112 --p_imav_rec Okl_ima_pvt.imav_rec_type;
4113 --x_imav_rec Okl_ima_pvt.imav_rec_type;
4114
4115 -- Defining table record for out record of Invoice Message Attribute
4116 x_imav_tbl Okl_Ima_Pvt.imav_tbl_type;
4117 -- fmiao - Bug#5232919 - Modified - End
4118
4119 l_save_ims_id okl_invoice_mssgs_v.id%TYPE;
4120 l_save_priority okl_invoice_mssgs_v.priority%TYPE;
4121 l_bind_proc VARCHAR2(3000);
4122 l_msg_return VARCHAR2(1);
4123
4124 l_temp_cnr_id number;
4125
4126 -- fmiao - Bug#5232919 - Modified - Start
4127 l_date_consolidated DATE;
4128 -- fmiao - Bug#5232919 - Modified - End
4129 BEGIN
4130
4131 -- rmunjulu R12 Fixes - Comment out entire code and Put NULL
4132 NULL;
4133 /*
4134 L_DEBUG_ENABLED := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
4135
4136 IF (L_DEBUG_ENABLED='Y' and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
4137 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'okl_cons_bill'
4138 ,'Begin(+)');
4139 END IF;
4140
4141 -- ------------------------
4142 -- Print Input variables
4143 -- ------------------------
4144 PRINT_TO_LOG('p_commit '||p_commit);
4145 PRINT_TO_LOG('p_contract_number '||p_contract_number);
4146 PRINT_TO_LOG('p_assigned_process '||p_assigned_process);
4147
4148 g_inv_msg := p_inv_msg;
4149 ------------------------------------------------------------
4150 -- Start processing
4151 ------------------------------------------------------------
4152
4153 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
4154 */
4155 /*
4156 l_return_status := Okl_Api.START_ACTIVITY(
4157 p_api_name => l_api_name,
4158 p_pkg_name => G_PKG_NAME,
4159 p_init_msg_list => p_init_msg_list,
4160 l_api_version => l_api_version,
4161 p_api_version => p_api_version,
4162 p_api_type => '_PVT',
4163 x_return_status => l_return_status);
4164
4165 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
4166 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
4167 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
4168 RAISE Okl_Api.G_EXCEPTION_ERROR;
4169 END IF;
4170 */
4171 /*
4172 PRINT_TO_LOG( '========== **** BEGIN PROGRAM EXECUTION **** ============');
4173
4174 -- IF p_contract_number IS NULL THEN
4175
4176 PRINT_TO_LOG( '========== START: Three LEVEL Processing ============');
4177 -- ---------------------------------------
4178 -- Initialize table and record parameters
4179 -- ---------------------------------------
4180 saved_bill_rec := l_init_bill_rec;
4181 cons_bill_tbl.delete;
4182 l_update_tbl.delete;
4183
4184 OPEN c;
4185 LOOP
4186 cons_bill_tbl.delete;
4187 FETCH C BULK COLLECT INTO cons_bill_tbl LIMIT L_FETCH_SIZE;
4188 FND_FILE.PUT_LINE (FND_FILE.LOG, 'C cons_bill_tbl count is: '||cons_bill_tbl.COUNT);
4189 IF cons_bill_tbl.COUNT > 0 THEN
4190 process_cons_bill_tbl(
4191 p_contract_number => p_contract_number,
4192 p_api_version => p_api_version,
4193 p_init_msg_list => p_init_msg_list,
4194 p_commit => p_commit,
4195 x_return_status => x_return_status,
4196 x_msg_count => x_msg_count,
4197 x_msg_data => x_msg_data,
4198 p_cons_bill_tbl => cons_bill_tbl,
4199 p_saved_bill_rec => saved_bill_rec,
4200 p_update_tbl => l_update_tbl);
4201 END IF;
4202 EXIT WHEN C%NOTFOUND;
4203 END LOOP;
4204 CLOSE C;
4205
4206 -- -----------------------------
4207 -- Process Last set of records
4208 -- -----------------------------
4209 process_break(p_contract_number,
4210 p_commit,
4211 saved_bill_rec,
4212 l_update_tbl);
4213
4214 -- IF FND_API.To_Boolean( p_commit ) THEN
4215 -- COMMIT;
4216 -- END IF;
4217
4218 -- insert remaining records here
4219 -- main insert is in create_new_invoice
4220 -- bulk inserts
4221 -- insert consolidated headers
4222 -- BUG#4621302
4223 lang_count := 1;
4224 FOR l_lang_rec IN get_languages
4225 LOOP
4226 l_lang_tbl(lang_count) := l_lang_rec;
4227 lang_count := lang_count + 1;
4228 END LOOP;
4229
4230 if g_cnr_tbl.count > 0 then
4231 -- Create TL Records
4232 tl_count := g_cnr_tbl.first;
4233
4234 for z in g_cnr_tbl.first..g_cnr_tbl.last loop
4235 -- BUG#4621302
4236 FOR lang_count IN l_lang_tbl.first..l_lang_tbl.last
4237 LOOP
4238 g_cnr_tl_tbl(tl_count).id := g_cnr_tbl(z).id;
4239 g_cnr_tl_tbl(tl_count).language := l_lang_tbl(lang_count).language_code;
4240 g_cnr_tl_tbl(tl_count).source_lang := USERENV('LANG');
4241 g_cnr_tl_tbl(tl_count).sfwt_flag := 'N';
4242 g_cnr_tl_tbl(tl_count).created_by := fnd_global.user_id;
4243 g_cnr_tl_tbl(tl_count).creation_date := sysdate;
4244 g_cnr_tl_tbl(tl_count).last_updated_by := fnd_global.user_id;
4245 g_cnr_tl_tbl(tl_count).last_update_date := sysdate;
4246 g_cnr_tl_tbl(tl_count).last_update_login := fnd_global.login_id;
4247 tl_count := tl_count + 1;
4248 END LOOP; -- languages loop
4249 -- invoice messaging processing
4250 if g_inv_msg = 'TRUE' then
4251 if g_msg_tbl.COUNT > 0 then
4252 -- Find message with the highest priority
4253 l_save_priority := NULL;
4254 for e in g_msg_tbl.FIRST..g_msg_tbl.LAST loop
4255 -- fmiao - Bug#5232919 - Modified - Start
4256 l_date_consolidated := TRUNC(g_cnr_tbl(z).date_consolidated);
4257 -- Check if the invoice message is effective for this consolidated invoice
4258 IF ( l_date_consolidated BETWEEN NVL(g_msg_tbl(e).start_date,l_date_consolidated)
4259 AND NVL(g_msg_tbl(e).end_date,l_date_consolidated)) THEN
4260
4261 PRINT_TO_LOG('====> IMS_ID: '||g_msg_tbl(e).id);
4262 PRINT_TO_LOG('====> PKG: '||g_msg_tbl(e).pkg_name);
4263 PRINT_TO_LOG('====> PROC: '||g_msg_tbl(e).proc_name);
4264
4265 l_bind_proc := 'BEGIN OKL_QUAL_INV_MSGS.'||g_msg_tbl(e).proc_name||'(:1,:2); END;';
4266
4267 PRINT_TO_LOG('l_bind_proc 2 : '||l_bind_proc);
4268 PRINT_TO_LOG('g_cnr_tbl(z).id 2: '||g_cnr_tbl(z).id);
4269
4270 BEGIN
4271 EXECUTE IMMEDIATE l_bind_proc USING IN g_cnr_tbl(z).id, OUT l_msg_return;
4272 EXCEPTION
4273 WHEN OTHERS THEN
4274 PRINT_TO_LOG('Invoice Message error 2 -- '||SQLERRM);
4275 END;
4276
4277 if (l_msg_return = '1' ) then
4278 if (l_save_priority is null) or (g_msg_tbl(e).priority < l_save_priority) then
4279 l_save_priority := g_msg_tbl(e).priority;
4280 l_save_ims_id := g_msg_tbl(e).id;
4281 end if;
4282 end if;
4283 END IF; -- end of check for effective dates of invoice message
4284 -- fmiao - Bug#5232919 - Modified - End
4285 end loop;
4286 end if;
4287 -- Create Intersection Record
4288 if (l_save_priority is not null) then
4289 -- fmiao - Bug#5232919 - Modified - Start
4290 -- Populating the global Inv Messg Attr records
4291 g_imav_counter := g_imav_counter + 1;
4292 g_imav_tbl(g_imav_counter).cnr_id := g_cnr_tbl(z).id;
4293 g_imav_tbl(g_imav_counter).ims_id := l_save_ims_id;
4294 --p_imav_rec.cnr_id := x_cnrv_rec.id;
4295 --p_imav_rec.ims_id := l_save_ims_id;
4296 -- Commenting code that inserts record into Inv Msg Attr table because
4297 -- at this point the CNR_ID is not yet in the CNSLD HDR table and this will
4298 -- fail validation at TAPI of Inv Msg Attr table
4299 */
4300 /*
4301 -- Start of wraper code generated automatically by Debug code generator for okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT
4302 IF(IS_DEBUG_PROCEDURE_ON) THEN
4303 BEGIN
4304 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
4305 END;
4306 END IF;
4307
4308 okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT(
4309 p_api_version
4310 ,p_init_msg_list
4311 ,x_return_status
4312 ,x_msg_count
4313 ,x_msg_data
4314 ,p_imav_rec
4315 ,x_imav_rec);
4316
4317 IF(IS_DEBUG_PROCEDURE_ON) THEN
4318 BEGIN
4319 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
4320 END;
4321 END IF;
4322 -- End of wraper code generated automatically by Debug code generator for okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT
4323 IF ( x_return_status = 'S' ) THEN
4324 PRINT_TO_LOG('====> Message Created.');
4325 ELSE
4326 PRINT_TO_LOG('*=> FAILED:Message Creation');
4327 END IF;
4328 */
4329 -- fmiao - Bug#5232919 - Modified - End
4330 /*
4331 ELSE
4332 PRINT_TO_LOG('====> NO Message Qualified');
4333 END IF;
4334 end if; -- Boolean test for invoice message processing
4335 end loop; --TL processing
4336
4337 --g_cnr_tbl(g_cnr_tbl.count).amount := g_cnr_total + g_lln_total;
4338
4339 BEGIN
4340 savepoint H2;
4341 PRINT_TO_LOG('Performing bulk insert for cnr, record count is '||g_cnr_tbl.count);
4342 forall x in g_cnr_tbl.first..g_cnr_tbl.last
4343 save exceptions
4344 insert into okl_cnsld_ar_hdrs_b
4345 values g_cnr_tbl(x);
4346
4347 forall d in g_cnr_tl_tbl.first..g_cnr_tl_tbl.last
4348 save exceptions
4349 insert into okl_cnsld_ar_hdrs_tl
4350 values g_cnr_tl_tbl(d);
4351
4352 EXCEPTION
4353 WHEN OTHERS THEN
4354 rollback to H2;
4355 PRINT_TO_LOG('Error during Header Insertion, rollback to H2');
4356 g_cnr_tbl.DELETE;
4357 g_cnr_tl_tbl.DELETE;
4358 g_lln_tbl.DELETE;
4359 g_lln_tl_tbl.DELETE;
4360 g_lsm_tbl.DELETE;
4361 g_lsm_tl_tbl.DELETE;
4362 g_xsi_tbl.DELETE;
4363 g_xsi_tl_tbl.DELETE;
4364 g_xls_tbl.DELETE;
4365 g_xls_tl_tbl.DELETE;
4366 RAISE;
4367 END;
4368
4369 end if;
4370 -- flush table
4371 g_cnr_tbl.delete;
4372 g_cnr_tl_tbl.delete;
4373 -- insert consolidated lines
4374 if g_lln_tbl.count > 0 then
4375
4376 -- Create TL Records
4377 tl_count := g_lln_tbl.first;
4378
4379 for x in g_lln_tbl.first..g_lln_tbl.last loop
4380 -- BUG#4621302
4381 FOR lang_count IN l_lang_tbl.first..l_lang_tbl.last
4382 LOOP
4383 g_lln_tl_tbl(tl_count).id := g_lln_tbl(x).id;
4384 g_lln_tl_tbl(tl_count).language := l_lang_tbl(lang_count).language_code;
4385 g_lln_tl_tbl(tl_count).source_lang := USERENV('LANG');
4386 g_lln_tl_tbl(tl_count).sfwt_flag := 'N';
4387 g_lln_tl_tbl(tl_count).created_by := fnd_global.user_id;
4388 g_lln_tl_tbl(tl_count).creation_date := sysdate;
4389 g_lln_tl_tbl(tl_count).last_updated_by := fnd_global.user_id;
4390 g_lln_tl_tbl(tl_count).last_update_date := sysdate;
4391 g_lln_tl_tbl(tl_count).last_update_login := fnd_global.login_id;
4392 tl_count := tl_count + 1;
4393 END LOOP; -- languages loop
4394 end loop;
4395
4396 -- g_lln_tbl(g_lln_tbl.count).amount := g_lln_total;
4397
4398 BEGIN
4399 savepoint L2;
4400 PRINT_TO_LOG('Performing bulk insert for lln, record count is '||g_lln_tbl.count);
4401 forall x in g_lln_tbl.first..g_lln_tbl.last
4402 save exceptions
4403 insert into okl_cnsld_ar_lines_b
4404 values g_lln_tbl(x);
4405
4406 forall e in g_lln_tl_tbl.first..g_lln_tl_tbl.last
4407 save exceptions
4408 insert into okl_cnsld_ar_lines_tl
4409 values g_lln_tl_tbl(e);
4410
4411 EXCEPTION
4412 WHEN OTHERS THEN
4413 PRINT_TO_LOG('Error during Line Insertion, rollback to L2');
4414 rollback to L2;
4415 g_lln_tl_tbl.DELETE;
4416 g_lsm_tbl.DELETE;
4417 g_lsm_tl_tbl.DELETE;
4418 g_xsi_tbl.DELETE;
4419 g_xsi_tl_tbl.DELETE;
4420 g_xls_tbl.DELETE;
4421 g_xls_tl_tbl.DELETE;
4422
4423 for e in g_lln_tbl.FIRST..g_lln_tbl.LAST loop
4424 delete from okl_cnsld_ar_hdrs_b
4425 where id = g_lln_tbl(e).cnr_id;
4426 end loop;
4427 g_lln_tbl.DELETE;
4428 commit;
4429 RAISE;
4430 END;
4431 end if;
4432 -- flush table
4433 g_lln_tbl.delete;
4434 g_lln_tl_tbl.delete;
4435
4436 -- insert consolidated streams
4437 if g_lsm_tbl.count > 0 then
4438
4439 -- Create TL Records
4440 tl_count := g_lsm_tbl.first;
4441
4442 for y in g_lsm_tbl.first..g_lsm_tbl.last loop
4443 -- BUG#4621302
4444 FOR lang_count IN l_lang_tbl.first..l_lang_tbl.last
4445 LOOP
4446 g_lsm_tl_tbl(tl_count).id := g_lsm_tbl(y).id;
4447 g_lsm_tl_tbl(tl_count).language := l_lang_tbl(lang_count).language_code;
4448 g_lsm_tl_tbl(tl_count).source_lang := USERENV('LANG');
4449 g_lsm_tl_tbl(tl_count).sfwt_flag := 'N';
4450 g_lsm_tl_tbl(tl_count).created_by := fnd_global.user_id;
4451 g_lsm_tl_tbl(tl_count).creation_date := sysdate;
4452 g_lsm_tl_tbl(tl_count).last_updated_by := fnd_global.user_id;
4453 g_lsm_tl_tbl(tl_count).last_update_date := sysdate;
4454 g_lsm_tl_tbl(tl_count).last_update_login := fnd_global.login_id;
4455 tl_count := tl_count + 1;
4456 END LOOP; -- languages loop
4457 end loop;
4458
4459
4460 BEGIN
4461 savepoint D2;
4462 PRINT_TO_LOG('Performing bulk insert for lsm, record count is '||g_lsm_tbl.count);
4463 forall x in g_lsm_tbl.first..g_lsm_tbl.last
4464 save exceptions
4465 insert into okl_cnsld_ar_strms_b
4466 values g_lsm_tbl(x);
4467
4468 forall f in g_lsm_tl_tbl.first..g_lsm_tl_tbl.last
4469 save exceptions
4470 insert into okl_cnsld_ar_strms_tl
4471 values g_lsm_tl_tbl(f);
4472
4473 commit;
4474 EXCEPTION
4475 WHEN OTHERS THEN
4476 PRINT_TO_LOG('Error during Stream Insertion, rollback to D2');
4477 rollback to D2;
4478 g_cnr_tl_tbl.delete;
4479 g_lln_tl_tbl.DELETE;
4480 g_lsm_tl_tbl.DELETE;
4481 g_xsi_tbl.DELETE;
4482 g_xsi_tl_tbl.DELETE;
4483 g_xls_tbl.DELETE;
4484 g_xls_tl_tbl.DELETE;
4485
4486 for e in g_lsm_tbl.FIRST..g_lsm_tbl.LAST loop
4487 for f in g_lln_tbl.FIRST..g_lln_tbl.LAST loop
4488 delete from okl_cnsld_ar_hdrs_b
4489 where id = g_lln_tbl(f).cnr_id;
4490 end loop;
4491 delete from okl_cnsld_ar_lines_b
4492 where id = g_lsm_tbl(e).lln_id;
4493 end loop;
4494 g_lsm_tbl.DELETE;
4495 g_lln_tbl.DELETE;
4496 g_cnr_tbl.DELETE;
4497 commit;
4498 RAISE;
4499 END;
4500 end if;
4501 -- flush table
4502 g_lsm_tbl.delete;
4503 g_lsm_tl_tbl.delete;
4504
4505 -- fmiao - Bug#5232919 - Modified - Start
4506 IF ( g_imav_tbl.COUNT > 0) THEN
4507 -- Code to insert the table of records into OKL_INV_MSSG_ATT
4508 -- Start of wraper code generated automatically by Debug code generator for okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT
4509 IF(IS_DEBUG_PROCEDURE_ON) THEN
4510 BEGIN
4511 Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
4512 END;
4513 END IF;
4514
4515 Okl_Inv_Mssg_Att_Pub.INSERT_INV_MSSG_ATT(
4516 p_api_version
4517 ,p_init_msg_list
4518 ,x_return_status
4519 ,x_msg_count
4520 ,x_msg_data
4521 ,g_imav_tbl
4522 ,x_imav_tbl
4523 );
4524 IF(IS_DEBUG_PROCEDURE_ON) THEN
4525 BEGIN
4526 Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
4527 END;
4528 END IF;
4529 -- End of wraper code generated automatically by Debug code generator for okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT
4530 IF ( x_return_status = 'S' ) THEN
4531 PRINT_TO_LOG('====> Message Created.');
4532 ELSE
4533 PRINT_TO_LOG('*=> FAILED:Message Creation');
4534 END IF;
4535
4536 -- flush the global table of records
4537 g_imav_tbl.DELETE;
4538 g_imav_counter := 0;
4539 END IF; -- end of check for presence of g_imav_tbl records
4540 -- fmiao - Bug#5232919 - Modified - End
4541 --MDOKAL final updates
4542
4543 if g_xsi_tbl.COUNT > 0 then
4544
4545 PRINT_TO_LOG('Performing final bulk update for xsi, record count is '||g_xsi_tbl.COUNT );
4546
4547 BEGIN
4548 savepoint U5;
4549 for indx in g_xsi_tbl.first..g_xsi_tbl.last loop
4550 -- rseela BUG#4733028 removed the updation of xtrx_invoice_pull_yn
4551 update okl_ext_sell_invs_b
4552 set trx_status_code = g_xsi_tbl(indx).trx_status_code,
4553 -- xtrx_invoice_pull_yn = g_xsi_tbl(indx).xtrx_invoice_pull_yn,
4554 last_update_date = sysdate,
4555 last_updated_by = fnd_global.user_id,
4556 last_update_login = fnd_global.login_id
4557 where id = g_xsi_tbl(indx).id;
4558 end loop;
4559 commit;
4560 -- flush table
4561 g_xsi_tbl.delete;
4562 g_xsi_counter := 0;
4563 EXCEPTION
4564 WHEN OTHERS THEN
4565 PRINT_TO_LOG('Error during Update of okl_ext_sell_invs_b, rollback to U5');
4566 rollback to U5;
4567 RAISE;
4568 END;
4569 end if;
4570
4571 if g_xls_tbl.COUNT > 0 then
4572
4573 PRINT_TO_LOG('Performing final bulk update for xls, record count is '||g_xls_tbl.COUNT );
4574 BEGIN
4575 savepoint U6;
4576 for s in g_xls_tbl.first..g_xls_tbl.last loop
4577 update okl_xtl_sell_invs_b
4578 set lsm_id = g_xls_tbl(s).lsm_id,
4579 xtrx_cons_stream_id = g_xls_tbl(s).lsm_id,
4580 last_update_date = sysdate,
4581 last_updated_by = fnd_global.user_id,
4582 last_update_login = fnd_global.login_id
4583 where id = g_xls_tbl(s).id;
4584 end loop;
4585 commit;
4586 -- flush table
4587 g_xls_tbl.delete;
4588 g_xls_counter := 0;
4589 EXCEPTION
4590 WHEN OTHERS THEN
4591 PRINT_TO_LOG('Error during Update of okl_xtl_sell_invs_b, rollback to U6');
4592 rollback to U6;
4593 RAISE;
4594 END;
4595 end if;
4596
4597 if g_xsi_tl_tbl.COUNT > 0 then
4598
4599 PRINT_TO_LOG('Performing final bulk update for xsi tl, record count is '||g_xsi_tl_tbl.COUNT );
4600 BEGIN
4601 savepoint U7;
4602 for u in g_xsi_tl_tbl.first..g_xsi_tl_tbl.last loop
4603 update okl_ext_sell_invs_tl
4604 set xtrx_cons_invoice_number = g_xsi_tl_tbl(u).xtrx_cons_invoice_number,
4605 xtrx_format_type = g_xsi_tl_tbl(u).xtrx_format_type,
4606 xtrx_private_label = g_xsi_tl_tbl(u).xtrx_private_label,
4607 last_update_date = sysdate,
4608 last_updated_by = fnd_global.user_id,
4609 last_update_login = fnd_global.login_id
4610 where id = g_xsi_tl_tbl(u).id;
4611 end loop;
4612 commit;
4613 -- flush table
4614 g_xsi_tl_tbl.delete;
4615 g_xsi_tl_counter := 0;
4616 EXCEPTION
4617 WHEN OTHERS THEN
4618 PRINT_TO_LOG('Error during Update of okl_ext_sell_invs_tl, rollback to U7');
4619 rollback to U7;
4620 RAISE;
4621 END;
4622 end if;
4623
4624 if g_xls_tl_tbl.COUNT > 0 then
4625
4626 PRINT_TO_LOG('Performing final bulk update for xls tl, record count is '||g_xls_tl_tbl.COUNT );
4627 BEGIN
4628 savepoint U8;
4629 for t in g_xls_tl_tbl.first..g_xls_tl_tbl.last loop
4630 update okl_xtl_sell_invs_tl
4631 set xtrx_contract = g_xls_tl_tbl(t).xtrx_contract,
4632 xtrx_asset = g_xls_tl_tbl(t).xtrx_asset,
4633 xtrx_stream_type = g_xls_tl_tbl(t).xtrx_stream_type,
4634 xtrx_stream_group = g_xls_tl_tbl(t).xtrx_stream_group,
4635 last_update_date = sysdate,
4636 last_updated_by = fnd_global.user_id,
4637 last_update_login = fnd_global.login_id
4638 where id = g_xls_tl_tbl(t).id;
4639 end loop;
4640 commit;
4641 -- flush table
4642 g_xls_tl_tbl.delete;
4643 g_xls_tl_counter := 0;
4644 EXCEPTION
4645 WHEN OTHERS THEN
4646 PRINT_TO_LOG('Error during Update of okl_xtl_sell_invs_tl, rollback to U8');
4647 rollback to U8;
4648 RAISE;
4649 END;
4650 end if;
4651
4652 PRINT_TO_LOG( '========== END: Three LEVEL Processing ============');
4653
4654 PRINT_TO_LOG( '========== START: Two LEVEL Processing ============');
4655 -- ---------------------------------------
4656 -- Initialize table and record parameters
4657 -- ---------------------------------------
4658 saved_bill_rec := l_init_bill_rec;
4659 cons_bill_tbl.delete;
4660 l_update_tbl.delete;
4661
4662 -- delete all other records
4663 g_cnr_tbl.DELETE;
4664 g_cnr_tl_tbl.DELETE;
4665 g_lln_tbl.DELETE;
4666 g_lln_tl_tbl.DELETE;
4667 g_lsm_tbl.DELETE;
4668 g_lsm_tl_tbl.DELETE;
4669 g_xsi_tbl.DELETE;
4670 g_xsi_tl_tbl.DELETE;
4671 g_xls_tbl.DELETE;
4672 g_xls_tl_tbl.DELETE;
4673
4674
4675 OPEN C1;
4676 LOOP
4677 cons_bill_tbl.delete;
4678 FETCH C1 BULK COLLECT INTO cons_bill_tbl LIMIT L_FETCH_SIZE;
4679
4680 FND_FILE.PUT_LINE (FND_FILE.LOG, 'C1 cons_bill_tbl count is: '||cons_bill_tbl.COUNT);
4681 IF cons_bill_tbl.COUNT > 0 THEN
4682 process_cons_bill_tbl(
4683 p_contract_number => p_contract_number,
4684 p_api_version => p_api_version,
4685 p_init_msg_list => p_init_msg_list,
4686 p_commit => p_commit,
4687 x_return_status => x_return_status,
4688 x_msg_count => x_msg_count,
4689 x_msg_data => x_msg_data,
4690 p_cons_bill_tbl => cons_bill_tbl,
4691 p_saved_bill_rec => saved_bill_rec,
4692 p_update_tbl => l_update_tbl);
4693 END IF;
4694 EXIT WHEN C1%NOTFOUND;
4695 END LOOP;
4696 CLOSE C1;
4697
4698 -- -----------------------------
4699 -- Process Last set of records
4700 -- -----------------------------
4701 process_break(p_contract_number,
4702 p_commit,
4703 saved_bill_rec,
4704 l_update_tbl);
4705
4706 -- IF FND_API.To_Boolean( p_commit ) THEN
4707 -- COMMIT;
4708 -- END IF;
4709
4710 PRINT_TO_LOG( '========== END: Two LEVEL Processing ============');
4711
4712 PRINT_TO_LOG( '========== START: CREDIT MEMO Two LEVEL Processing ============');
4713 -- ---------------------------------------
4714 -- Initialize table and record parameters
4715 -- ---------------------------------------
4716 saved_bill_rec := l_init_bill_rec;
4717 cons_bill_tbl.delete;
4718 l_update_tbl.delete;
4719
4720
4721 -- Update the XTRX columns in XSI and XLS and Resequence the
4722 -- Consolidated bill lines
4723
4724 l_cnr_id := -1;
4725
4726 PRINT_TO_LOG( '========== START: UPDATING Processed Records ============');
4727
4728 l_commit_cnt := 0;
4729
4730 PRINT_TO_LOG( '========== **** END PROGRAM EXECUTION **** ============');
4731 --MDOKAL
4732 -- insert remaining records here
4733 -- main insert is in create_new_invoice
4734 -- bulk inserts
4735 -- insert consolidated headers
4736
4737
4738 if g_cnr_tbl.count > 0 then
4739
4740 -- Create TL Records
4741 tl_count := g_cnr_tbl.first;
4742
4743 for z in g_cnr_tbl.first..g_cnr_tbl.last loop
4744 -- BUG#4621302
4745 FOR lang_count IN l_lang_tbl.first..l_lang_tbl.last
4746 LOOP
4747 g_cnr_tl_tbl(tl_count).id := g_cnr_tbl(z).id;
4748 g_cnr_tl_tbl(tl_count).language := l_lang_tbl(lang_count).language_code;
4749 g_cnr_tl_tbl(tl_count).source_lang := USERENV('LANG');
4750 g_cnr_tl_tbl(tl_count).sfwt_flag := 'N';
4751 g_cnr_tl_tbl(tl_count).created_by := fnd_global.user_id;
4752 g_cnr_tl_tbl(tl_count).creation_date := sysdate;
4753 g_cnr_tl_tbl(tl_count).last_updated_by := fnd_global.user_id;
4754 g_cnr_tl_tbl(tl_count).last_update_date := sysdate;
4755 g_cnr_tl_tbl(tl_count).last_update_login := fnd_global.login_id;
4756 tl_count := tl_count + 1;
4757 END LOOP; -- languages code
4758 -- invoice messaging processing
4759 if g_inv_msg = 'TRUE' then
4760 if g_msg_tbl.COUNT > 0 then
4761 -- Find message with the highest priority
4762 l_save_priority := NULL;
4763 for e in g_msg_tbl.FIRST..g_msg_tbl.LAST loop
4764 -- fmiao - Bug#5232919 - Modified - Start
4765 l_date_consolidated := TRUNC(g_cnr_tbl(z).date_consolidated);
4766 -- Check if the invoice message is effective for this consolidated invoice
4767 IF ( l_date_consolidated BETWEEN NVL(g_msg_tbl(e).start_date,l_date_consolidated)
4768 AND NVL(g_msg_tbl(e).end_date,l_date_consolidated)) THEN
4769 PRINT_TO_LOG('====> IMS_ID: '||g_msg_tbl(e).id);
4770 PRINT_TO_LOG('====> PKG: '||g_msg_tbl(e).pkg_name);
4771 PRINT_TO_LOG('====> PROC: '||g_msg_tbl(e).proc_name);
4772
4773 l_bind_proc := 'BEGIN OKL_QUAL_INV_MSGS.'||g_msg_tbl(e).proc_name||'(:1,:2); END;';
4774
4775 PRINT_TO_LOG('l_bind_proc 2 : '||l_bind_proc);
4776 PRINT_TO_LOG('g_cnr_tbl(z).id 2: '||g_cnr_tbl(z).id);
4777
4778 BEGIN
4779 EXECUTE IMMEDIATE l_bind_proc USING IN g_cnr_tbl(z).id, OUT l_msg_return;
4780 EXCEPTION
4781 WHEN OTHERS THEN
4782 PRINT_TO_LOG('Invoice Message error 2 -- '||SQLERRM);
4783 END;
4784
4785 if (l_msg_return = '1' ) then
4786 if (l_save_priority is null) or (g_msg_tbl(e).priority < l_save_priority) then
4787 l_save_priority := g_msg_tbl(e).priority;
4788 l_save_ims_id := g_msg_tbl(e).id;
4789 end if;
4790 end if;
4791 END IF; -- end of check for effective dates of invoice message
4792 -- fmiao - Bug#5232919 - Modified - End
4793 end loop;
4794 end if;
4795 -- Create Intersection Record
4796 if (l_save_priority is not null) then
4797 -- fmiao - Bug#5232919 - Modified - Start
4798 -- Populating the global Inv Messg Attr records
4799 g_imav_counter := g_imav_counter + 1;
4800 g_imav_tbl(g_imav_counter).cnr_id := g_cnr_tbl(z).id;
4801 g_imav_tbl(g_imav_counter).ims_id := l_save_ims_id;
4802 --p_imav_rec.cnr_id := x_cnrv_rec.id;
4803 --p_imav_rec.ims_id := l_save_ims_id;
4804 -- Commenting code that inserts record into Inv Msg Attr table because
4805 -- at this point the CNR_ID is not yet in the CNSLD HDR table and this will
4806 -- fail validation at TAPI of Inv Msg Attr table
4807 */
4808 /*
4809 -- Start of wraper code generated automatically by Debug code generator for okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT
4810 IF(IS_DEBUG_PROCEDURE_ON) THEN
4811 BEGIN
4812 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
4813 END;
4814 END IF;
4815
4816 okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT(
4817 p_api_version
4818 ,p_init_msg_list
4819 ,x_return_status
4820 ,x_msg_count
4821 ,x_msg_data
4822 ,p_imav_rec
4823 ,x_imav_rec);
4824
4825 IF(IS_DEBUG_PROCEDURE_ON) THEN
4826 BEGIN
4827 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
4828 END;
4829 END IF;
4830 -- End of wraper code generated automatically by Debug code generator for okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT
4831 IF ( x_return_status = 'S' ) THEN
4832 PRINT_TO_LOG('====> Message Created.');
4833 ELSE
4834 PRINT_TO_LOG('*=> FAILED:Message Creation');
4835 END IF;
4836 */
4837 -- fmiao - Bug#5232919 - Modified - End
4838 /*
4839 ELSE
4840 PRINT_TO_LOG('====> NO Message Qualified');
4841 END IF;
4842 end if; -- Boolean test
4843 end loop; --TL processing
4844
4845 --g_cnr_tbl(g_cnr_tbl.count).amount := g_cnr_total + g_lln_total;
4846
4847 BEGIN
4848 savepoint H2;
4849 PRINT_TO_LOG('Performing final bulk insert for cnr, record count is '||g_cnr_tbl.count);
4850 forall x in g_cnr_tbl.first..g_cnr_tbl.last
4851 save exceptions
4852 insert into okl_cnsld_ar_hdrs_b
4853 values g_cnr_tbl(x);
4854
4855 forall d in g_cnr_tl_tbl.first..g_cnr_tl_tbl.last
4856 save exceptions
4857 insert into okl_cnsld_ar_hdrs_tl
4858 values g_cnr_tl_tbl(d);
4859
4860 EXCEPTION
4861 WHEN OTHERS THEN
4862 rollback to H2;
4863 PRINT_TO_LOG('Error during Header Insertion, rollback to H2');
4864 g_cnr_tbl.DELETE;
4865 g_cnr_tl_tbl.DELETE;
4866 g_lln_tbl.DELETE;
4867 g_lln_tl_tbl.DELETE;
4868 g_lsm_tbl.DELETE;
4869 g_lsm_tl_tbl.DELETE;
4870 g_xsi_tbl.DELETE;
4871 g_xsi_tl_tbl.DELETE;
4872 g_xls_tbl.DELETE;
4873 g_xls_tl_tbl.DELETE;
4874 RAISE;
4875 END;
4876
4877 end if;
4878 -- flush table
4879 g_cnr_tbl.delete;
4880 g_cnr_tl_tbl.delete;
4881 -- insert consolidated lines
4882 if g_lln_tbl.count > 0 then
4883
4884 -- Create TL Records
4885 tl_count := g_lln_tbl.first;
4886 for x in g_lln_tbl.first..g_lln_tbl.last loop
4887 -- BUG#4621302
4888 FOR lang_count IN l_lang_tbl.first..l_lang_tbl.last
4889 LOOP
4890 g_lln_tl_tbl(tl_count).id := g_lln_tbl(x).id;
4891 g_lln_tl_tbl(tl_count).language := l_lang_tbl(lang_count).language_code;
4892 g_lln_tl_tbl(tl_count).source_lang := USERENV('LANG');
4893 g_lln_tl_tbl(tl_count).sfwt_flag := 'N';
4894 g_lln_tl_tbl(tl_count).created_by := fnd_global.user_id;
4895 g_lln_tl_tbl(tl_count).creation_date := sysdate;
4896 g_lln_tl_tbl(tl_count).last_updated_by := fnd_global.user_id;
4897 g_lln_tl_tbl(tl_count).last_update_date := sysdate;
4898 g_lln_tl_tbl(tl_count).last_update_login := fnd_global.login_id;
4899 tl_count := tl_count + 1;
4900 END LOOP; -- languages loop
4901 end loop;
4902
4903 --g_lln_tbl(g_lln_tbl.count).amount := g_lln_total;
4904
4905 BEGIN
4906 savepoint L2;
4907 PRINT_TO_LOG('Performing final bulk insert for lln, record count is '||g_lln_tbl.count);
4908 forall x in g_lln_tbl.first..g_lln_tbl.last
4909 save exceptions
4910 insert into okl_cnsld_ar_lines_b
4911 values g_lln_tbl(x);
4912
4913 forall e in g_lln_tl_tbl.first..g_lln_tl_tbl.last
4914 save exceptions
4915 insert into okl_cnsld_ar_lines_tl
4916 values g_lln_tl_tbl(e);
4917
4918 EXCEPTION
4919 WHEN OTHERS THEN
4920 PRINT_TO_LOG('Error during Line Insertion, rollback to L2');
4921 rollback to L2;
4922 g_lln_tl_tbl.DELETE;
4923 g_lsm_tbl.DELETE;
4924 g_lsm_tl_tbl.DELETE;
4925 g_xsi_tbl.DELETE;
4926 g_xsi_tl_tbl.DELETE;
4927 g_xls_tbl.DELETE;
4928 g_xls_tl_tbl.DELETE;
4929
4930 for e in g_lln_tbl.FIRST..g_lln_tbl.LAST loop
4931 delete from okl_cnsld_ar_hdrs_b
4932 where id = g_lln_tbl(e).cnr_id;
4933 end loop;
4934 g_lln_tbl.DELETE;
4935 commit;
4936 RAISE;
4937 END;
4938 end if;
4939 -- flush table
4940 g_lln_tbl.delete;
4941 g_lln_tl_tbl.delete;
4942
4943 -- insert consolidated streams
4944 if g_lsm_tbl.count > 0 then
4945
4946 -- Create TL Records
4947 tl_count := g_lsm_tbl.first;
4948 for y in g_lsm_tbl.first..g_lsm_tbl.last loop
4949 -- BUG#4621302
4950 FOR lang_count IN l_lang_tbl.first..l_lang_tbl.last
4951 LOOP
4952 g_lsm_tl_tbl(tl_count).id := g_lsm_tbl(y).id;
4953 g_lsm_tl_tbl(tl_count).language := l_lang_tbl(lang_count).language_code;
4954 g_lsm_tl_tbl(tl_count).source_lang := USERENV('LANG');
4955 g_lsm_tl_tbl(tl_count).sfwt_flag := 'N';
4956 g_lsm_tl_tbl(tl_count).created_by := fnd_global.user_id;
4957 g_lsm_tl_tbl(tl_count).creation_date := sysdate;
4958 g_lsm_tl_tbl(tl_count).last_updated_by := fnd_global.user_id;
4959 g_lsm_tl_tbl(tl_count).last_update_date := sysdate;
4960 g_lsm_tl_tbl(tl_count).last_update_login := fnd_global.login_id;
4961 tl_count := tl_count + 1;
4962 END LOOP; -- languages loop
4963 end loop;
4964
4965
4966 BEGIN
4967 savepoint D2;
4968 PRINT_TO_LOG('Performing final bulk insert for lsm, record count is '||g_lsm_tbl.count);
4969 forall x in g_lsm_tbl.first..g_lsm_tbl.last
4970 save exceptions
4971 insert into okl_cnsld_ar_strms_b
4972 values g_lsm_tbl(x);
4973
4974 forall f in g_lsm_tl_tbl.first..g_lsm_tl_tbl.last
4975 save exceptions
4976 insert into okl_cnsld_ar_strms_tl
4977 values g_lsm_tl_tbl(f);
4978
4979 commit;
4980 EXCEPTION
4981 WHEN OTHERS THEN
4982 PRINT_TO_LOG('Error during Stream Insertion, rollback to D2');
4983 rollback to D2;
4984 g_cnr_tl_tbl.delete;
4985 g_lln_tl_tbl.DELETE;
4986 g_lsm_tl_tbl.DELETE;
4987 g_xsi_tbl.DELETE;
4988 g_xsi_tl_tbl.DELETE;
4989 g_xls_tbl.DELETE;
4990 g_xls_tl_tbl.DELETE;
4991
4992 for e in g_lsm_tbl.FIRST..g_lsm_tbl.LAST loop
4993 for f in g_lln_tbl.FIRST..g_lln_tbl.LAST loop
4994 delete from okl_cnsld_ar_hdrs_b
4995 where id = g_lln_tbl(f).cnr_id;
4996 end loop;
4997 delete from okl_cnsld_ar_lines_b
4998 where id = g_lsm_tbl(e).lln_id;
4999 end loop;
5000 g_lsm_tbl.DELETE;
5001 g_lln_tbl.DELETE;
5002 g_cnr_tbl.DELETE;
5003 commit;
5004 RAISE;
5005 END;
5006 end if;
5007 -- flush table
5008 g_lsm_tbl.delete;
5009 g_lsm_tl_tbl.delete;
5010 IF ( g_imav_tbl.COUNT > 0) THEN
5011 -- Code to insert the table of records into OKL_INV_MSSG_ATT
5012 -- Start of wraper code generated automatically by Debug code generator for okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT
5013 IF(IS_DEBUG_PROCEDURE_ON) THEN
5014 BEGIN
5015 Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
5016 END;
5017 END IF;
5018
5019 Okl_Inv_Mssg_Att_Pub.INSERT_INV_MSSG_ATT(
5020 p_api_version
5021 ,p_init_msg_list
5022 ,x_return_status
5023 ,x_msg_count
5024 ,x_msg_data
5025 ,g_imav_tbl
5026 ,x_imav_tbl
5027 );
5028 IF(IS_DEBUG_PROCEDURE_ON) THEN
5029 BEGIN
5030 Okl_Debug_Pub.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
5031 END;
5032 END IF;
5033 -- End of wraper code generated automatically by Debug code generator for okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT
5034 IF ( x_return_status = 'S' ) THEN
5035 PRINT_TO_LOG('====> Message Created.');
5036 ELSE
5037 PRINT_TO_LOG('*=> FAILED:Message Creation');
5038 END IF;
5039
5040 -- flush the global table of records
5041 g_imav_tbl.DELETE;
5042 g_imav_counter := 0;
5043 END IF; -- end of check for presence of g_imav_tbl records
5044 -- fmiao - Bug#5232919 - Modified - Start
5045 --MDOKAL final updates
5046
5047 if g_xsi_tbl.COUNT > 0 then
5048
5049 PRINT_TO_LOG('Performing final bulk update for xsi, record count is '||g_xsi_tbl.COUNT );
5050
5051 BEGIN
5052 savepoint U5;
5053 for indx in g_xsi_tbl.first..g_xsi_tbl.last loop
5054 -- rseela BUG#4733028 removed the updation of xtrx_invoice_pull_yn
5055 update okl_ext_sell_invs_b
5056 set trx_status_code = g_xsi_tbl(indx).trx_status_code,
5057 -- xtrx_invoice_pull_yn = g_xsi_tbl(indx).xtrx_invoice_pull_yn,
5058 last_update_date = sysdate,
5059 last_updated_by = fnd_global.user_id,
5060 last_update_login = fnd_global.login_id
5061 where id = g_xsi_tbl(indx).id;
5062 end loop;
5063 commit;
5064 -- flush table
5065 g_xsi_tbl.delete;
5066 g_xsi_counter := 0;
5067 EXCEPTION
5068 WHEN OTHERS THEN
5069 PRINT_TO_LOG('Error during Update of okl_ext_sell_invs_b, rollback to U5');
5070 rollback to U5;
5071 RAISE;
5072 END;
5073 end if;
5074
5075
5076 if g_xls_tbl.COUNT > 0 then
5077
5078 PRINT_TO_LOG('Performing final bulk update for xls, record count is '||g_xls_tbl.COUNT );
5079 BEGIN
5080 savepoint U6;
5081 for s in g_xls_tbl.first..g_xls_tbl.last loop
5082 update okl_xtl_sell_invs_b
5083 set lsm_id = g_xls_tbl(s).lsm_id,
5084 xtrx_cons_stream_id = g_xls_tbl(s).lsm_id,
5085 last_update_date = sysdate,
5086 last_updated_by = fnd_global.user_id,
5087 last_update_login = fnd_global.login_id
5088 where id = g_xls_tbl(s).id;
5089 end loop;
5090 commit;
5091 -- flush table
5092 g_xls_tbl.delete;
5093 g_xls_counter := 0;
5094 EXCEPTION
5095 WHEN OTHERS THEN
5096 PRINT_TO_LOG('Error during Update of okl_xtl_sell_invs_b, rollback to U6');
5097 rollback to U6;
5098 RAISE;
5099 END;
5100 end if;
5101
5102 if g_xsi_tl_tbl.COUNT > 0 then
5103
5104 PRINT_TO_LOG('Performing final bulk update for xsi tl, record count is '||g_xsi_tl_tbl.COUNT );
5105 BEGIN
5106 savepoint U7;
5107 for u in g_xsi_tl_tbl.first..g_xsi_tl_tbl.last loop
5108 update okl_ext_sell_invs_tl
5109 set xtrx_cons_invoice_number = g_xsi_tl_tbl(u).xtrx_cons_invoice_number,
5110 xtrx_format_type = g_xsi_tl_tbl(u).xtrx_format_type,
5111 xtrx_private_label = g_xsi_tl_tbl(u).xtrx_private_label,
5112 last_update_date = sysdate,
5113 last_updated_by = fnd_global.user_id,
5114 last_update_login = fnd_global.login_id
5115 where id = g_xsi_tl_tbl(u).id;
5116 end loop;
5117 commit;
5118 -- flush table
5119 g_xsi_tl_tbl.delete;
5120 g_xsi_tl_counter := 0;
5121 EXCEPTION
5122 WHEN OTHERS THEN
5123 PRINT_TO_LOG('Error during Update of okl_ext_sell_invs_tl, rollback to U7');
5124 rollback to U7;
5125 RAISE;
5126 END;
5127 end if;
5128
5129 if g_xls_tl_tbl.COUNT > 0 then
5130
5131 PRINT_TO_LOG('Performing final bulk update for xls tl, record count is '||g_xls_tl_tbl.COUNT );
5132 BEGIN
5133 savepoint U8;
5134 for t in g_xls_tl_tbl.first..g_xls_tl_tbl.last loop
5135 update okl_xtl_sell_invs_tl
5136 set xtrx_contract = g_xls_tl_tbl(t).xtrx_contract,
5137 xtrx_asset = g_xls_tl_tbl(t).xtrx_asset,
5138 xtrx_stream_type = g_xls_tl_tbl(t).xtrx_stream_type,
5139 xtrx_stream_group = g_xls_tl_tbl(t).xtrx_stream_group,
5140 last_update_date = sysdate,
5141 last_updated_by = fnd_global.user_id,
5142 last_update_login = fnd_global.login_id
5143 where id = g_xls_tl_tbl(t).id;
5144 end loop;
5145 commit;
5146 -- flush table
5147 g_xls_tl_tbl.delete;
5148 g_xls_tl_counter := 0;
5149 EXCEPTION
5150 WHEN OTHERS THEN
5151 PRINT_TO_LOG('Error during Update of okl_xtl_sell_invs_tl, rollback to U8');
5152 rollback to U8;
5153 RAISE;
5154 END;
5155 end if;
5156 */
5157 ------------------------------------------------------------
5158 -- End processing
5159 ------------------------------------------------------------
5160 /*
5161 Okl_Api.END_ACTIVITY (
5162 x_msg_count => x_msg_count,
5163 x_msg_data => x_msg_data);
5164 */
5165
5166 EXCEPTION
5167 ------------------------------------------------------------
5168 -- Exception handling
5169 ------------------------------------------------------------
5170
5171 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
5172 IF (L_DEBUG_ENABLED='Y' and FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5173 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'okl_cons_bill',
5174 'EXCEPTION :'||'Okl_Api.G_EXCEPTION_ERROR');
5175 END IF;
5176
5177 PRINT_TO_LOG('*=> Error Message(O1): '||SQLERRM);
5178 -- -------------------------------------------
5179 -- purge data from the parallel process table
5180 -- -------------------------------------------
5181 if p_assigned_process is not null then
5182 delete okl_parallel_processes
5183 where assigned_process = p_assigned_process;
5184 commit;
5185 end if;
5186
5187 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
5188 p_api_name => l_api_name,
5189 p_pkg_name => G_PKG_NAME,
5190 p_exc_name => 'Okl_Api.G_RET_STS_ERROR',
5191 x_msg_count => x_msg_count,
5192 x_msg_data => x_msg_data,
5193 p_api_type => '_PVT');
5194
5195 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
5196
5197 IF (L_DEBUG_ENABLED='Y' and FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5198 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'okl_cons_bill',
5199 'EXCEPTION :'||'Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR');
5200 END IF;
5201
5202 PRINT_TO_LOG('*=> Error Message(O2): '||SQLERRM);
5203 -- -------------------------------------------
5204 -- purge data from the parallel process table
5205 -- -------------------------------------------
5206 if p_assigned_process is not null then
5207 delete okl_parallel_processes
5208 where assigned_process = p_assigned_process;
5209 commit;
5210 end if;
5211
5212 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
5213 p_api_name => l_api_name,
5214 p_pkg_name => G_PKG_NAME,
5215 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
5216 x_msg_count => x_msg_count,
5217 x_msg_data => x_msg_data,
5218 p_api_type => '_PVT');
5219
5220 WHEN OTHERS THEN
5221 IF (L_DEBUG_ENABLED='Y' and FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
5222 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'okl_cons_bill',
5223 'EXCEPTION :'||'OTHERS');
5224 END IF;
5225
5226 PRINT_TO_LOG('*=> Error Message(O3): '||SQLERRM);
5227 -- -------------------------------------------
5228 -- purge data from the parallel process table
5229 -- -------------------------------------------
5230 if p_assigned_process is not null then
5231 delete okl_parallel_processes
5232 where assigned_process = p_assigned_process;
5233 commit;
5234 end if;
5235
5236 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
5237 p_api_name => l_api_name,
5238 p_pkg_name => G_PKG_NAME,
5239 p_exc_name => 'OTHERS',
5240 x_msg_count => x_msg_count,
5241 x_msg_data => x_msg_data,
5242 p_api_type => '_PVT');
5243
5244 END create_cons_bill;
5245
5246 END OKL_BILLING_CONTROLLER_PVT;