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