[Home] [Help]
PACKAGE BODY: APPS.OKL_ARFETCH_PUB
Source
1 PACKAGE BODY Okl_Arfetch_Pub AS
2 /* $Header: OKLPARFB.pls 120.7 2006/09/21 05:08:26 abhsaxen noship $ */
3
4 -- -------------------------------------------------
5 -- To print log messages
6 -- -------------------------------------------------
7 PROCEDURE PRINT_TO_LOG(p_message IN VARCHAR2)
8 IS
9 BEGIN
10
11 IF (FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
12 FND_FILE.PUT_LINE (FND_FILE.LOG,p_message);
13
14 FND_LOG.STRING(FND_LOG.LEVEL_STATEMENT,'okl_arfetch_pub',
15 p_message );
16
17 END IF;
18
19 okl_debug_pub.logmessage(NVL(p_message, 'NONE'));
20
21 FND_FILE.PUT_LINE(FND_FILE.LOG,p_message);
22 --dbms_output.put_line(p_message);
23 END PRINT_TO_LOG;
24
25
26 PROCEDURE Get_AR_Invoice_numbers (
27 p_api_version IN NUMBER,
28 p_init_msg_list IN VARCHAR2 DEFAULT Okc_Api.G_FALSE,
29 x_return_status OUT NOCOPY VARCHAR2,
30 x_msg_count OUT NOCOPY NUMBER,
31 x_msg_data OUT NOCOPY VARCHAR2)
32 IS
33
34
35 CURSOR unfetched_invs_csr IS
36 --start modified abhsaxen for performance SQLID 20562651
37 SELECT
38 customer_trx_id,
39 to_number(interface_line_attribute10||interface_line_attribute11) lsm_id,
40 xsi.id xsi_id,
41 xls.tld_id tld_id,
42 xls.til_id til_id,
43 cnr.id cnr_id,
44 lln.id lln_id,
45 cnr.consolidated_invoice_number,
46 sty.name sty_name,
47 lsm.amount lsm_amount
48 FROM ra_customer_trx_lines_all trx,
49 okl_cnsld_ar_strms_b lsm,
50 okl_cnsld_ar_lines_b lln,
51 okl_cnsld_ar_hdrs_b cnr,
52 okl_xtl_sell_invs_b xls,
53 okl_ext_sell_invs_b xsi,
54 okl_strm_type_v sty
55 WHERE trx.line_type <> 'TAX'
56 AND trx.interface_line_context = 'OKL_CONTRACTS'
57 AND (trx.interface_line_attribute10||trx.interface_line_attribute11)
58 = to_char(lsm.id)
59 AND lln.cnr_id = cnr.id
60 AND lln.id = lsm.lln_id
61 AND lsm.receivables_invoice_id = -99999
62 AND xls.lsm_id = lsm.id
63 AND xsi.id = xls.xsi_id_details
64 AND xsi.trx_status_code = 'PROCESSED'
65 AND sty.id = lsm.sty_id
66 AND cnr.org_id = trx.org_id
67 ;
68 --end modified abhsaxen for performance SQLID 20562651
69
70 -- --------------------------------------------
71 -- Cursor to fetch due dates for an AR invoice
72 -- --------------------------------------------
73 CURSOR ar_due_date_csr ( p_cust_trx_id NUMBER ) IS
74 SELECT due_date, trx.trx_number
75 FROM ar_payment_schedules_all ps,
76 ra_customer_trx_all trx
77 WHERE ps.customer_trx_id = p_cust_trx_id
78 AND trx.customer_trx_id = ps.customer_trx_id;
79
80 -- ----------------------------------------------
81 -- Cursor to fetch tax amounts for an AR invoice
82 -- ----------------------------------------------
83 CURSOR ar_tax_csr ( p_cust_trx_id NUMBER ) IS
84 SELECT SUM(NVL( extended_amount ,0))
85 FROM ra_customer_trx_lines
86 WHERE customer_trx_id = p_cust_trx_id AND
87 LINE_TYPE = 'TAX';
88
89
90 Type num_tbl is table of NUMBER index by BINARY_INTEGER ;
91 Type date_tbl is table of DATE index by BINARY_INTEGER ;
92 Type chr_tbl is table of Varchar2(2000) index by BINARY_INTEGER ;
93
94 customer_trx_id_tbl num_tbl;
95 lsm_id_tbl num_tbl;
96 xsi_id_tbl num_tbl;
97 tld_id_tbl num_tbl;
98 til_id_tbl num_tbl;
99 cnr_id_tbl num_tbl;
100 lln_id_tbl num_tbl;
101 cons_inv chr_tbl;
102 sty_name chr_tbl;
103 due_date_tbl date_tbl;
104 trx_number_tbl chr_tbl;
105 tax_amount_tbl num_tbl;
106 lsm_amount_tbl num_tbl;
107
108 L_FETCH_SIZE NUMBER := 10000;
109
110 -- *******************************************
111 -- End Bulk Fetch changes
112 -- *******************************************
113
114 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
115 l_api_name CONSTANT VARCHAR2(30) := 'AR Fetcher Routine';
116
117 -- ----------------------
118 -- Std Who columns
119 -- ----------------------
120 lx_last_updated_by okl_ext_sell_invs_v.last_updated_by%TYPE := Fnd_Global.USER_ID;
121 lx_last_update_login okl_ext_sell_invs_v.last_update_login%TYPE := Fnd_Global.LOGIN_ID;
122 lx_request_id okl_ext_sell_invs_v.request_id%TYPE := Fnd_Global.CONC_REQUEST_ID;
123
124 lx_program_application_id
125 okl_ext_sell_invs_v.program_application_id%TYPE := Fnd_Global.PROG_APPL_ID;
126 lx_program_id okl_ext_sell_invs_v.program_id%TYPE := Fnd_Global.CONC_PROGRAM_ID;
127
128
129 bulk_errors EXCEPTION;
130
131 PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
132
133 BEGIN
134
135 l_return_status := OKC_API.START_ACTIVITY(l_api_name,
136 p_init_msg_list,
137 '_PVT',
138 x_return_status);
139
140 IF (l_return_status = OKC_API.G_RET_STS_UNEXP_ERROR) THEN
141 RAISE OKC_API.G_EXCEPTION_UNEXPECTED_ERROR;
142 ELSIF (l_return_status = OKC_API.G_RET_STS_ERROR) THEN
143 RAISE OKC_API.G_EXCEPTION_ERROR;
144 END IF;
145
146 -- ------------------------------
147 -- Bulk Fetch and Bulk Updates
148 -- ------------------------------
149
150
151 OPEN unfetched_invs_csr;
152 LOOP
153
154 customer_trx_id_tbl.delete;
155 lsm_id_tbl.delete;
156 xsi_id_tbl.delete;
157 tld_id_tbl.delete;
158 til_id_tbl.delete;
159 cnr_id_tbl.delete;
160 lln_id_tbl.delete;
161 cons_inv.delete;
162 sty_name.delete;
163 lsm_amount_tbl.delete;
164
165 FETCH unfetched_invs_csr
166 BULK COLLECT INTO customer_trx_id_tbl,
167 lsm_id_tbl,
168 xsi_id_tbl,
169 tld_id_tbl,
170 til_id_tbl,
171 cnr_id_tbl,
172 lln_id_tbl,
173 cons_inv,
174 sty_name,
175 lsm_amount_tbl
176 LIMIT L_FETCH_SIZE;
177
178 -- ------------------------------------------------------
179 -- Update Tax amounts on CNR/LLN/LSM
180 -- ------------------------------------------------------
181
182 tax_amount_tbl.delete;
183
184 if customer_trx_id_tbl.count > 0 then
185 for indx in customer_trx_id_tbl.first..customer_trx_id_tbl.last loop
186
187 open ar_tax_csr( customer_trx_id_tbl(indx) );
188 fetch ar_tax_csr into tax_amount_tbl(indx);
189 close ar_tax_csr;
190
191 end loop;
192
193 forall indx in cnr_id_tbl.first..cnr_id_tbl.last
194 save exceptions
195 update okl_cnsld_ar_hdrs_b
196 set amount = 0
197 where id = cnr_id_tbl(indx);
198
199
200 forall indx in tax_amount_tbl.first..tax_amount_tbl.last
201 save exceptions
202 update okl_cnsld_ar_hdrs_b
203 set amount = amount + nvl(lsm_amount_tbl(indx),0) + nvl( tax_amount_tbl(indx), 0),
204 last_update_date = sysdate,
205 last_updated_by = lx_last_updated_by,
206 last_update_login = lx_last_update_login,
207 request_id = lx_request_id,
208 program_update_date = sysdate,
209 program_application_id = lx_program_application_id,
210 program_id = lx_program_id
211 where id = cnr_id_tbl(indx);
212
213 if sql%bulk_exceptions.count > 0 then
214 for i in 1..sql%bulk_exceptions.count loop
215 print_to_log('while fetching, error ' || i || ' occurred during '||
216 'iteration ' || sql%bulk_exceptions(i).error_index);
217 print_to_log('oracle error is ' ||
218 sqlerrm(sql%bulk_exceptions(i).error_code));
219 end loop;
220 end if;
221
222 forall indx in tax_amount_tbl.first..tax_amount_tbl.last
223 save exceptions
224 update okl_cnsld_ar_lines_b
225 set tax_amount = nvl(tax_amount,0)+ nvl(tax_amount_tbl(indx),0),
226 last_update_date = sysdate,
227 last_updated_by = lx_last_updated_by,
228 last_update_login = lx_last_update_login,
229 request_id = lx_request_id,
230 program_update_date = sysdate,
231 program_application_id = lx_program_application_id,
232 program_id = lx_program_id
233 where id = lln_id_tbl(indx);
234
235 if sql%bulk_exceptions.count > 0 then
236 for i in 1..sql%bulk_exceptions.count loop
237 print_to_log('while fetching, error ' || i || ' occurred during '||
238 'iteration ' || sql%bulk_exceptions(i).error_index);
239 print_to_log('oracle error is ' ||
240 sqlerrm(sql%bulk_exceptions(i).error_code));
241 end loop;
242 end if;
243
244 forall indx in tax_amount_tbl.first..tax_amount_tbl.last
245 save exceptions
246 update okl_cnsld_ar_strms_b
247 set tax_amount = nvl(tax_amount_tbl(indx),0),
248 last_update_date = sysdate,
249 last_updated_by = lx_last_updated_by,
250 last_update_login = lx_last_update_login,
251 request_id = lx_request_id,
252 program_update_date = sysdate,
253 program_application_id = lx_program_application_id,
254 program_id = lx_program_id
255 where id = lsm_id_tbl(indx);
256
257 if sql%bulk_exceptions.count > 0 then
258 for i in 1..sql%bulk_exceptions.count loop
259 print_to_log('while fetching, error ' || i || ' occurred during '||
260 'iteration ' || sql%bulk_exceptions(i).error_index);
261 print_to_log('oracle error is ' ||
262 sqlerrm(sql%bulk_exceptions(i).error_code));
263 end loop;
264 end if;
265
266 end if; -- Update Tax amounts
267
268 commit;
269 -- ------------------------------------------------------
270 -- Update Due Date on CNR
271 -- ------------------------------------------------------
272
273 due_date_tbl.delete;
274 trx_number_tbl.delete;
275
276
277 IF customer_trx_id_tbl.COUNT > 0 THEN
278
279 FOR indx IN customer_trx_id_tbl.FIRST..customer_trx_id_tbl.LAST LOOP
280
281 OPEN ar_due_date_csr ( customer_trx_id_tbl(indx) );
282 FETCH ar_due_date_csr INTO due_date_tbl(indx),trx_number_tbl(indx);
283 CLOSE ar_due_date_csr;
284
285 END LOOP;
286
287 FORALL indx in customer_trx_id_tbl.FIRST..customer_trx_id_tbl.LAST
288 save exceptions
289 UPDATE okl_cnsld_ar_hdrs_b
290 SET due_date = due_date_tbl(indx),
291 last_update_date = sysdate,
292 last_updated_by = lx_last_updated_by,
293 last_update_login = lx_last_update_login,
294 request_id = lx_request_id,
295 program_update_date = sysdate,
296 program_application_id = lx_program_application_id,
297 program_id = lx_program_id
298 where id = cnr_id_tbl(indx);
299
300 if sql%bulk_exceptions.count > 0 then
301 for i in 1..sql%bulk_exceptions.count loop
302 print_to_log('while fetching, error ' || i || ' occurred during '||
303 'iteration ' || sql%bulk_exceptions(i).error_index);
304 print_to_log('oracle error is ' ||
305 sqlerrm(sql%bulk_exceptions(i).error_code));
306 end loop;
307 end if;
308
309 END IF;
310
311 commit;
312
313 -- ------------------------------------------------------
314 -- Update receivables_invoice_id in XSI,TLD,TIL and LSM
315 -- ------------------------------------------------------
316 IF xsi_id_tbl.COUNT > 0 THEN
317
318 -- ------------------------------------------------------
319 -- Populate customer_trx_id in tld.receivables_invoice_id
320 -- ------------------------------------------------------
321 FORALL indx IN xsi_id_tbl.FIRST..xsi_id_tbl.LAST
322 save exceptions
323 update okl_ext_sell_invs_b
324 set receivables_invoice_id = customer_trx_id_tbl(indx),
325 last_update_date = sysdate,
326 last_updated_by = lx_last_updated_by,
327 last_update_login = lx_last_update_login,
328 request_id = lx_request_id,
329 program_update_date = sysdate,
330 program_application_id = lx_program_application_id,
331 program_id = lx_program_id
332 where id = xsi_id_tbl(indx);
333
334 if sql%bulk_exceptions.count > 0 then
335 for i in 1..sql%bulk_exceptions.count loop
336 print_to_log('while fetching, error ' || i || ' occurred during '||
337 'iteration ' || sql%bulk_exceptions(i).error_index);
338 print_to_log('oracle error is ' ||
339 sqlerrm(sql%bulk_exceptions(i).error_code));
340 end loop;
341 end if;
342
343 END IF; -- Xsi records found
344
345 IF tld_id_tbl.COUNT > 0 THEN
346
347 -- ------------------------------------------------------
348 -- Populate customer_trx_id in tld.receivables_invoice_id
349 -- ------------------------------------------------------
350 FORALL indx IN til_id_tbl.FIRST..til_id_tbl.LAST
351 save exceptions
352 update okl_txd_ar_ln_dtls_b
353 set receivables_invoice_id = customer_trx_id_tbl(indx),
354 last_update_date = sysdate,
355 last_updated_by = lx_last_updated_by,
356 last_update_login = lx_last_update_login,
357 request_id = lx_request_id,
358 program_update_date = sysdate,
359 program_application_id = lx_program_application_id,
360 program_id = lx_program_id
361
362 where id = tld_id_tbl(indx);
363
364 if sql%bulk_exceptions.count > 0 then
365 for i in 1..sql%bulk_exceptions.count loop
366 print_to_log('while fetching, error ' || i || ' occurred during '||
367 'iteration ' || sql%bulk_exceptions(i).error_index);
368 print_to_log('oracle error is ' ||
369 sqlerrm(sql%bulk_exceptions(i).error_code));
370 end loop;
371 end if;
372
373 END IF; -- Tld records found
374
375
376 IF til_id_tbl.COUNT > 0 THEN
377
378 -- ------------------------------------------------------
379 -- Populate customer_trx_id in til.receivables_invoice_id
380 -- ------------------------------------------------------
381 FORALL indx IN til_id_tbl.FIRST..til_id_tbl.LAST
382 save exceptions
383 update okl_txl_ar_inv_lns_b
384 set receivables_invoice_id = customer_trx_id_tbl(indx),
385 last_update_date = sysdate,
386 last_updated_by = lx_last_updated_by,
387 last_update_login = lx_last_update_login,
388 request_id = lx_request_id,
389 program_update_date = sysdate,
390 program_application_id = lx_program_application_id,
391 program_id = lx_program_id
392 where id = til_id_tbl(indx);
393
394 if sql%bulk_exceptions.count > 0 then
395 for i in 1..sql%bulk_exceptions.count loop
396 print_to_log('while fetching, error ' || i || ' occurred during '||
397 'iteration ' || sql%bulk_exceptions(i).error_index);
398 print_to_log('oracle error is ' ||
399 sqlerrm(sql%bulk_exceptions(i).error_code));
400 end loop;
401 end if;
402
403 END IF; -- Til records found
404
405 IF lsm_id_tbl.COUNT > 0 THEN
406
407 -- ------------------------------------------------------
408 -- Populate customer_trx_id in lsm.receivables_invoice_id
409 -- ------------------------------------------------------
410 FORALL indx IN lsm_id_tbl.FIRST..lsm_id_tbl.LAST
411 save exceptions
412 update okl_cnsld_ar_strms_b
413 set receivables_invoice_id = customer_trx_id_tbl(indx),
414 last_update_date = sysdate,
415 last_updated_by = lx_last_updated_by,
416 last_update_login = lx_last_update_login,
417 request_id = lx_request_id,
418 program_update_date = sysdate,
419 program_application_id = lx_program_application_id,
420 program_id = lx_program_id
421 where id = lsm_id_tbl(indx);
422
423 if sql%bulk_exceptions.count > 0 then
424 for i in 1..sql%bulk_exceptions.count loop
425 print_to_log('while fetching, error ' || i || ' occurred during '||
426 'iteration ' || sql%bulk_exceptions(i).error_index);
427 print_to_log('oracle error is ' ||
428 sqlerrm(sql%bulk_exceptions(i).error_code));
429 end loop;
430 end if;
431
432 END IF; -- Lsm records found
433
434 IF customer_trx_id_tbl.count > 0 THEN
435 FOR indx in customer_trx_id_tbl.FIRST..customer_trx_id_tbl.LAST LOOP
436 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'Processing: Consolidated Invoice=> '||cons_inv(indx)
437 ||' , Stream=> '||sty_name(indx)
438 ||' ,Amount=> '||lsm_amount_tbl(indx));
439 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Fetching Assigned AR Invoice=> '||trx_number_tbl(indx));
440 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' TAX Amount is => '||tax_amount_tbl(indx));
441 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Due Date is => '||due_date_tbl(indx));
442 END LOOP;
443 END IF;
444
445
446 EXIT WHEN unfetched_invs_csr%NOTFOUND;
447 END LOOP;
448 CLOSE unfetched_invs_csr;
449
450 COMMIT;
451 x_return_status := l_return_status;
452
453
454 Okl_Api.END_ACTIVITY (
455 x_msg_count => x_msg_count,
456 x_msg_data => x_msg_data);
457
458 EXCEPTION
459 WHEN bulk_errors THEN
460 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'ERROR (01): '||SQLERRM);
461
462 if sql%bulk_exceptions.count > 0 then
463 for i in 1..sql%bulk_exceptions.count loop
464 print_to_log('while fetching, error ' || i || ' occurred during '||
465 'iteration ' || sql%bulk_exceptions(i).error_index);
466 print_to_log('oracle error is ' ||
467 sqlerrm(sql%bulk_exceptions(i).error_code));
468
469 end loop;
470 end if;
471
472 WHEN OTHERS THEN
473 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'ERROR (02): '||SQLERRM);
474 x_return_status :=Okc_Api.HANDLE_EXCEPTIONS
475 (
476 l_api_name,
477 'Okl_Arfetch_Pub',
478 'OTHERS',
479 x_msg_count,
480 x_msg_data,
481 '_PVT'
482 );
483 END get_AR_invoice_numbers;
484
485 PROCEDURE Get_AR_Invoice_numbers_conc (
486 errbuf OUT NOCOPY VARCHAR2 ,
487 retcode OUT NOCOPY NUMBER )
488
489 IS
490
491 l_api_version NUMBER := 1;
492 lx_msg_count NUMBER;
493 l_count1 NUMBER :=0;
494 l_count2 NUMBER :=0;
495 l_count NUMBER :=0;
496 I NUMBER :=0;
497 l_msg_index_out NUMBER :=0;
498 lx_msg_data VARCHAR2(450);
499 lx_return_status VARCHAR2(1);
500
501 BEGIN
502
503 Fnd_File.PUT_LINE (Fnd_File.LOG, 'Starting Fetcher Program.. ');
504 Okl_Arfetch_Pub.get_AR_invoice_numbers (
505 p_api_version => l_api_version,
506 p_init_msg_list => Okl_Api.G_FALSE,
507 x_return_status => lx_return_status,
508 x_msg_count => lx_msg_count,
509 x_msg_data => errbuf
510 );
511 Fnd_File.PUT_LINE (Fnd_File.LOG, 'Ending Fetcher Program.. ');
512 IF lx_msg_count > 0 THEN
513 FOR i IN 1..lx_msg_count LOOP
514 Fnd_Msg_Pub.get (p_msg_index => i,
515 p_encoded => 'F',
516 p_data => lx_msg_data,
517 p_msg_index_out => l_msg_index_out);
518 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,TO_CHAR(i) || ': ' || lx_msg_data);
519 END LOOP;
520 END IF;
521
522 EXCEPTION
523 WHEN OTHERS THEN
524 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Error '||TO_CHAR(SQLCODE)||': '||SQLERRM);
525 END get_AR_invoice_numbers_conc;
526
527 END Okl_Arfetch_Pub;