[Home] [Help]
PACKAGE BODY: APPS.OKL_INTERNAL_TO_EXTERNAL
Source
1 PACKAGE BODY OKL_INTERNAL_TO_EXTERNAL AS
2 /* $Header: OKLRIEXB.pls 120.24.12010000.3 2009/06/03 04:19:20 racheruv ship $ */
3
4 -- Start of wraper code generated automatically by Debug code generator
5 l_module VARCHAR2(40) := 'LEASE.RECEIVABLES.INVOICE';
6 l_debug_enabled CONSTANT VARCHAR2(10) := Okl_Debug_Pub.check_log_enabled;
7 l_level_procedure NUMBER;
8 is_debug_procedure_on BOOLEAN;
9 -- End of wraper code generated automatically by Debug code generator
10
11 --fmiao 5209209 change
12 ----------------------------------------------
13 -- Global variables for bulk processing
14 ----------------------------------------------
15
16 l_xsi_cnt NUMBER := 0;
17 l_xls_cnt NUMBER := 0;
18 l_esd_cnt NUMBER := 0;
19
20 TYPE xsi_tbl_type IS TABLE OF OKL_EXT_SELL_INVS_B%ROWTYPE INDEX BY BINARY_INTEGER;
21 TYPE xls_tbl_type IS TABLE OF OKL_XTL_SELL_INVS_B%ROWTYPE INDEX BY BINARY_INTEGER;
22 TYPE esd_tbl_type IS TABLE OF OKL_XTD_SELL_INVS_B%ROWTYPE INDEX BY BINARY_INTEGER;
23
24 xsi_tbl xsi_tbl_type;
25 xls_tbl xls_tbl_type;
26 esd_tbl esd_tbl_type;
27
28 l_xsitl_cnt NUMBER := 0;
29 l_xlstl_cnt NUMBER := 0;
30 l_esdtl_cnt NUMBER := 0;
31
32 TYPE xsitl_tbl_type IS TABLE OF OKL_EXT_SELL_INVS_TL%ROWTYPE INDEX BY BINARY_INTEGER;
33 TYPE xlstl_tbl_type IS TABLE OF OKL_XTL_SELL_INVS_TL%ROWTYPE INDEX BY BINARY_INTEGER;
34 TYPE esdtl_tbl_type IS TABLE OF OKL_XTD_SELL_INVS_TL%ROWTYPE INDEX BY BINARY_INTEGER;
35
36 xsitl_tbl xsitl_tbl_type;
37 xlstl_tbl xlstl_tbl_type;
38 esdtl_tbl esdtl_tbl_type;
39
40 TYPE error_rec_type IS RECORD (id NUMBER);
41 TYPE error_tbl_type IS TABLE OF error_rec_type INDEX BY BINARY_INTEGER;
42
43 error_tbl error_tbl_type;
44 total_error_tbl error_tbl_type;
45
46 --for bulk update
47 l_tai_id_cnt NUMBER := 0;
48 TYPE tai_id_tbl_type IS TABLE OF okl_trx_ar_invoices_b.id%TYPE
49 INDEX BY BINARY_INTEGER;
50 --TYPE num_tbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
51 tai_id_tbl tai_id_tbl_type;
52
53 l_commit_cnt NUMBER := 0;
54 l_commit_cnt2 NUMBER := 0;
55 l_khr_id okl_trx_ar_invoices_v.khr_id%TYPE := -1;
56 l_max_commit_cnt NUMBER := 500;
57
58 --fmiao 5209209 change end
59
60 -- -------------------------------------------------
61 -- To print log messages
62 -- -------------------------------------------------
63 PROCEDURE print_to_log(p_message IN VARCHAR2) IS
64 BEGIN
65
66 Fnd_File.PUT_LINE(Fnd_File.LOG, p_message);
67
68 IF(Fnd_Log.level_statement >= Fnd_Log.g_current_runtime_level) THEN
69 Fnd_Log.string(Fnd_Log.level_statement, 'okl_internal_to_external', p_message);
70 END IF;
71
72 Okl_Debug_Pub.logmessage(p_message);
73 --dbms_output.put_line(p_message);
74 END print_to_log;
75
76 --fmiao 5209209 change
77 /*
78 -- -------------------------------------------------
79 -- To print log messages for xsi_rec
80 -- -------------------------------------------------
81 PROCEDURE print_xsi_rec(i_xsiv_rec IN okl_xsi_pvt.xsiv_rec_type) IS
82 BEGIN
83 print_to_log('Start XSI Record (+)');
84 print_to_log('i_xsiv_rec.trx_date ' || i_xsiv_rec.trx_date);
85 print_to_log('i_xsiv_rec.customer_id ' || i_xsiv_rec.customer_id);
86 print_to_log('i_xsiv_rec.receipt_method_id ' || i_xsiv_rec.receipt_method_id);
87 print_to_log('i_xsiv_rec.term_id ' || i_xsiv_rec.term_id);
88 print_to_log('i_xsiv_rec.currency_code ' || i_xsiv_rec.currency_code);
89 print_to_log('i_xsiv_rec.currency_conversion_type ' || i_xsiv_rec.currency_conversion_type);
90 print_to_log('i_xsiv_rec.currency_conversion_rate ' || i_xsiv_rec.currency_conversion_rate);
91 print_to_log('i_xsiv_rec.currency_conversion_date ' || i_xsiv_rec.currency_conversion_date);
92 print_to_log('i_xsiv_rec.customer_address_id ' || i_xsiv_rec.customer_address_id);
93 print_to_log('i_xsiv_rec.set_of_books_id ' || i_xsiv_rec.set_of_books_id);
94 print_to_log('i_xsiv_rec.cust_trx_type_id ' || i_xsiv_rec.cust_trx_type_id);
95 print_to_log('i_xsiv_rec.description ' || i_xsiv_rec.description);
96 print_to_log('i_xsiv_rec.CUSTOMER_BANK_ACCOUNT_ID ' || i_xsiv_rec.customer_bank_account_id);
97 print_to_log('i_xsiv_rec.org_id ' || i_xsiv_rec.org_id);
98 print_to_log('i_xsiv_rec.trx_status_code ' || i_xsiv_rec.trx_status_code);
99 print_to_log('i_xsiv_rec.tax_exempt_flag ' || i_xsiv_rec.tax_exempt_flag);
100 print_to_log('i_xsiv_rec.tax_exempt_reason_code ' || i_xsiv_rec.tax_exempt_reason_code);
101 print_to_log('End XSI Record (-)');
102 END print_xsi_rec;
103
104 -- -------------------------------------------------
105 -- To print log messages for xls_rec
106 -- -------------------------------------------------
107 PROCEDURE print_xls_rec(i_xlsv_rec IN okl_xls_pvt.xlsv_rec_type) IS
108 BEGIN
109
110 print_to_log('Start XLS Record (+)');
111
112 print_to_log('i_xlsv_rec.TLD_ID ' || i_xlsv_rec.tld_id);
113 print_to_log('i_xlsv_rec.XSI_ID_DETAILS ' || i_xlsv_rec.xsi_id_details);
114 print_to_log('i_xlsv_rec.LINE_TYPE ' || i_xlsv_rec.line_type);
115 print_to_log('i_xlsv_rec.DESCRIPTION ' || i_xlsv_rec.description);
116 print_to_log('i_xlsv_rec.AMOUNT ' || i_xlsv_rec.amount);
117 print_to_log('i_xlsv_rec.ORG_ID ' || i_xlsv_rec.org_id);
118 print_to_log('i_xlsv_rec.SEL_ID ' || i_xlsv_rec.sel_id);
119
120 print_to_log('End XLS Record (-)');
121 END print_xls_rec;
122
123 -- -------------------------------------------------
124 -- To print log messages for esd_rec
125 -- -------------------------------------------------
126 PROCEDURE print_esd_rec(i_esdv_rec IN okl_esd_pvt.esdv_rec_type) IS
127 BEGIN
128
129 print_to_log('Start ESD Record (+)');
130 print_to_log('i_esdv_rec.code_combination_id ' || i_esdv_rec.code_combination_id);
131 print_to_log('i_esdv_rec.xls_id ' || i_esdv_rec.xls_id);
132 print_to_log('i_esdv_rec.amount ' || i_esdv_rec.amount);
133 print_to_log('i_esdv_rec.percent ' || i_esdv_rec.percent);
134 print_to_log('i_esdv_rec.account_class ' || i_esdv_rec.account_class);
135 print_to_log('End ESD Record (-)');
136
137 END print_esd_rec;
138 */
139
140
141 PROCEDURE bulk_process
142 ( p_api_version IN NUMBER
143 ,p_init_msg_list IN VARCHAR2
144 ,x_return_status OUT NOCOPY VARCHAR2
145 ,x_msg_count OUT NOCOPY NUMBER
146 ,x_msg_data OUT NOCOPY VARCHAR2
147 ,p_commit IN VARCHAR2
148 ) IS
149
150 l_api_name CONSTANT VARCHAR2(30) := 'BULK_PROCESS';
151 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
152 l_api_version CONSTANT NUMBER := 1;
153
154 i NUMBER := 0;
155 j NUMBER := 0;
156
157 CURSOR del_error_csr(p_xsi_id NUMBER) IS
158 SELECT xls.id xls_id
159 FROM okl_ext_sell_invs_v xsi,
160 okl_xtl_sell_invs_v xls
161 WHERE xls.xsi_id_details = xsi.id AND
162 xsi.id = p_xsi_id;
163
164 CURSOR del_xtd_csr( p_xls_id NUMBER ) IS
165 SELECT id esd_id
166 FROM okl_xtd_sell_invs_v
167 WHERE xls_id = p_xls_id;
168
169 --d_xlsv_rec Okl_Xls_Pvt.xlsv_rec_type;
170 --d_esdv_rec Okl_Esd_Pvt.esdv_rec_type;
171
172 BEGIN
173
174 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
175 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE,'okl_internal_to_external'
176 ,'Begin(+)');
177 END IF;
178
179 -- ------------------------
180 -- Print Input variables
181 -- ------------------------
182 PRINT_TO_LOG('BULK p_commit '||p_commit);
183
184 l_return_status := Okl_Api.START_ACTIVITY(
185 p_api_name => l_api_name,
186 p_pkg_name => G_PKG_NAME,
187 p_init_msg_list => p_init_msg_list,
188 l_api_version => l_api_version,
189 p_api_version => p_api_version,
190 p_api_type => '_PVT',
191 x_return_status => l_return_status);
192
193 --Starting process
194 error_tbl.DELETE;
195
196 -----------------------------------------
197 -- Transfer Xsi records to the Xsi table
198 -- --------------------------------------
199 PRINT_TO_LOG('BULK Transfering XSI records to XSI table...');
200 PRINT_TO_LOG('BULK xsi_tbl.COUNT : ' || xsi_tbl.COUNT);
201
202 IF xsi_tbl.COUNT > 0 THEN
203 FORALL indx IN xsi_tbl.first..xsi_tbl.LAST
204 SAVE EXCEPTIONS
205 INSERT INTO OKL_EXT_SELL_INVS_B
206 VALUES xsi_tbl(indx);
207 END IF;
208
209 IF SQL%BULK_EXCEPTIONS.COUNT > 0 THEN
210 FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
211 PRINT_TO_LOG('BULK For inserting external header, error ' || i || ' occurred during '||
212 'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
213 PRINT_TO_LOG('BULK Oracle error is ' ||
214 SQLERRM(SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
215
216 error_tbl(error_tbl.COUNT + 1).id := TO_NUMBER(xsi_tbl(i).id);
217 total_error_tbl(total_error_tbl.COUNT + 1).id := TO_NUMBER(xsi_tbl(i).id);
218 END LOOP;
219 END IF;
220 PRINT_TO_LOG('BULK Done Inserting into okl_ext_sell_invs_b');
221
222 -- --------------------------------------
223 -- Transfer XsiTl records to the XsiTl table
224 -- --------------------------------------
225 PRINT_TO_LOG('BULK Transfering XSI_TL records to XSI_TL table...');
226 PRINT_TO_LOG('BULK xsitl_tbl.COUNT : ' || xsitl_tbl.COUNT);
227
228 IF xsitl_tbl.COUNT > 0 THEN
229 FORALL indx IN xsitl_tbl.first..xsitl_tbl.LAST
230 SAVE EXCEPTIONS
231 INSERT INTO OKL_EXT_SELL_INVS_TL
232 VALUES xsitl_tbl(indx);
233 END IF;
234
235 IF SQL%BULK_EXCEPTIONS.COUNT > 0 THEN
236 FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
237 PRINT_TO_LOG('BULK For inserting external header tl, error ' || i || ' occurred during '||
238 'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
239 PRINT_TO_LOG('BULK Oracle error is ' ||
240 SQLERRM(SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
241
242 --error_tbl(error_tbl.count + 1).id := to_number(xsitl_tbl(i).id);
243 END LOOP;
244 END IF;
245 PRINT_TO_LOG('BULK Done Inserting into okl_ext_sell_invs_tl');
246
247 -- --------------------------------------
248 -- Transfer Xls records to the Xls table
249 -- --------------------------------------
250 PRINT_TO_LOG('BULK Transfering XLS records to XLS table...');
251 PRINT_TO_LOG('BULK xls_tbl.COUNT : ' || xls_tbl.COUNT);
252
253 IF xls_tbl.COUNT > 0 THEN
254 FORALL indx IN xls_tbl.first..xls_tbl.LAST
255 SAVE EXCEPTIONS
256 INSERT INTO OKL_XTL_SELL_INVS_B
257 VALUES xls_tbl(indx);
258 END IF;
259
260 IF SQL%BULK_EXCEPTIONS.COUNT > 0 THEN
261 FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
262 PRINT_TO_LOG('BULK For inserting external lines, error ' || i || ' occurred during '||
263 'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
264 PRINT_TO_LOG('BULK Oracle error is ' ||
265 SQLERRM(SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
266
267 --error_tbl(error_tbl.count + 1).xls_id := to_number(xls_tbl(i).id);
268 END LOOP;
269 END IF;
270 PRINT_TO_LOG('BULK Done Inserting into okl_xtl_sell_invs_b');
271
272 -- --------------------------------------
273 -- Transfer XlsTl records to the XlsTl table
274 -- --------------------------------------
275 PRINT_TO_LOG('BULK Transfering XLS_TL records to XLS_TL table...');
276 PRINT_TO_LOG('BULK xlstl_tbl.COUNT : ' || xlstl_tbl.COUNT);
277
278 IF xlstl_tbl.COUNT > 0 THEN
279 FORALL indx IN xlstl_tbl.first..xlstl_tbl.LAST
280 SAVE EXCEPTIONS
281 INSERT INTO OKL_XTL_SELL_INVS_TL
282 VALUES xlstl_tbl(indx);
283 END IF;
284
285 IF SQL%BULK_EXCEPTIONS.COUNT > 0 THEN
286 FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
287 PRINT_TO_LOG('BULK For inserting external lines tl, error ' || i || ' occurred during '||
288 'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
289 PRINT_TO_LOG('BULK Oracle error is ' ||
290 SQLERRM(SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
291
292 --error_tbl(error_tbl.count + 1).id := to_number(xlstl_tbl(i).id);
293 END LOOP;
294 END IF;
295 PRINT_TO_LOG('BULK Done Inserting into okl_xtl_sell_invs_tl');
296
297 -- --------------------------------------
298 -- Transfer Xtd records to the Xtd table
299 -- --------------------------------------
300 PRINT_TO_LOG('BULK Transfering XTD records to XTD table...');
301 PRINT_TO_LOG('BULK xtd_tbl.COUNT : ' || esd_tbl.COUNT);
302
303 IF esd_tbl.COUNT > 0 THEN
304 FORALL indx IN esd_tbl.first..esd_tbl.LAST
305 SAVE EXCEPTIONS
306 INSERT INTO OKL_XTD_SELL_INVS_B
307 VALUES esd_tbl(indx);
308 END IF;
309
310 IF SQL%BULK_EXCEPTIONS.COUNT > 0 THEN
311 FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
312 PRINT_TO_LOG('BULK For inserting external details, error ' || i || ' occurred during '||
313 'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
314 PRINT_TO_LOG('BULK Oracle error is ' ||
315 SQLERRM(SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
316
317 --error_tbl(error_tbl.count + 1).esd_id := to_number(esd_tbl(i).id);
318 END LOOP;
319 END IF;
320 PRINT_TO_LOG('BULK Done Inserting into okl_xtd_sell_invs_b');
321
322 -- --------------------------------------
323 -- Transfer XtdTl records to the XtdTl table
324 -- --------------------------------------
325 PRINT_TO_LOG('BULK Transfering XTD_TL records to XTD_TL table...');
326 PRINT_TO_LOG('BULK xtdtl_tbl.COUNT : ' || esdtl_tbl.COUNT);
327
328 IF esdtl_tbl.COUNT > 0 THEN
329 FORALL indx IN esdtl_tbl.first..esdtl_tbl.LAST
330 SAVE EXCEPTIONS
331 INSERT INTO OKL_XTD_SELL_INVS_TL
332 VALUES esdtl_tbl(indx);
333 END IF;
334
335 IF SQL%BULK_EXCEPTIONS.COUNT > 0 THEN
336 --Fnd_File.PUT_LINE (Fnd_File.LOG, 'esdtl_tbl insert');
337 FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
338 PRINT_TO_LOG('BULK For inserting external details tl, error ' || i || ' occurred during '||
339 'iteration ' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
340 PRINT_TO_LOG('BULK Oracle error is ' ||
341 SQLERRM(SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
342
343 --error_tbl(error_tbl.count + 1).id := to_number(esdtl_tbl(i).id);
344 END LOOP;
345 END IF;
346 PRINT_TO_LOG('BULK Done Inserting into okl_xtd_sell_invs_tl');
347
348 -- update the status
349 PRINT_TO_LOG('BULK tai_id_tbl.COUNT: '||tai_id_tbl.COUNT);
350 IF (tai_id_tbl.COUNT > 0) THEN
351 FORALL indx IN tai_id_tbl.FIRST..tai_id_tbl.LAST
352 UPDATE okl_trx_ar_invoices_b
353 SET trx_status_code = 'PROCESSED',
354 last_update_date = SYSDATE,
355 last_updated_by = Fnd_Global.USER_ID,
356 last_update_login = Fnd_Global.LOGIN_ID
357 WHERE ID = tai_id_tbl(indx);
358
359 COMMIT;
360 Fnd_File.PUT_LINE (Fnd_File.LOG, 'BULK Status updated!');
361 END IF;
362
363
364 --Fnd_File.PUT_LINE (Fnd_File.LOG, 'BULK commit commented 1Y');
365 l_commit_cnt := 0;
366 PRINT_TO_LOG('BULK error_tbl.count = '||error_tbl.COUNT);
367 --Fnd_File.PUT_LINE (Fnd_File.LOG, 'before the error_tbl print');
368
369 IF (error_tbl.COUNT > 0) THEN
370 PRINT_TO_LOG('BULK Processing error ...');
371 FOR i IN error_tbl.first..error_tbl.last LOOP
372 l_commit_cnt := l_commit_cnt + 1;
373
374 PRINT_TO_LOG('BULK Error XSI ID = ' || error_tbl(i).id);
375 FOR del_error_rec IN del_error_csr(error_tbl(i).id) LOOP
376
377 FOR del_xtd_rec IN del_xtd_csr( del_error_rec.xls_id ) LOOP
378 PRINT_TO_LOG('BULK Deleting xtd when error ...');
379
380 DELETE FROM Okl_Xtd_Sell_Invs_B
381 WHERE id = del_xtd_rec.esd_id;
382
383 DELETE FROM Okl_Xtd_Sell_Invs_TL
384 WHERE id = del_xtd_rec.esd_id;
385
386 END LOOP;
387
388 PRINT_TO_LOG('BULK Deleting xls when error ...');
389 DELETE FROM Okl_Xtl_Sell_Invs_B
390 WHERE id = del_error_rec.xls_id;
391
392 DELETE FROM Okl_Xtl_Sell_Invs_TL
393 WHERE id = del_error_rec.xls_id;
394
395 PRINT_TO_LOG('BULK Finally deleting xsi when error ...');
396 DELETE FROM Okl_Ext_Sell_Invs_B
397 WHERE id = error_tbl(i).id;
398
399 DELETE FROM Okl_Ext_Sell_Invs_TL
400 WHERE id = error_tbl(i).id;
401 END LOOP;
402
403 -- Performance Improvement
404 IF Fnd_Api.To_Boolean( p_commit )THEN
405 COMMIT;
406 END IF;
407
408 END LOOP; --error_tbl
409
410 PRINT_TO_LOG('BULK End Processing error ...');
411 END IF; --error_tbl.count > 0
412
413
414 -----------------------
415 -- Commit
416 -----------------------
417 PRINT_TO_LOG('BULK p_commit in bulk process' ||p_commit);
418 IF Fnd_Api.To_Boolean( p_commit ) THEN
419 COMMIT;
420 PRINT_TO_LOG('BULK after commit in bulk process');
421 END IF;
422
423 ------------------------------------------
424 -- Clean up the tables after processing
425 ------------------------------------------
426 error_tbl.DELETE;
427 tai_id_tbl.DELETE;
428
429 xsi_tbl.DELETE;
430 xls_tbl.DELETE;
431 esd_tbl.DELETE;
432
433 xsitl_tbl.DELETE;
434 xlstl_tbl.DELETE;
435 esdtl_tbl.DELETE;
436
437 l_xsi_cnt := 0;
438 l_xls_cnt := 0;
439 l_esd_cnt := 0;
440
441 l_xsitl_cnt := 0;
442 l_xlstl_cnt := 0;
443 l_esdtl_cnt := 0;
444 l_tai_id_cnt := 0;
445
446 IF (Fnd_Log.LEVEL_PROCEDURE >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
447 Fnd_Log.STRING(Fnd_Log.LEVEL_PROCEDURE,'okl_internal_to_external','End(-)');
448 END IF;
449
450 Okl_Api.END_ACTIVITY (
451 x_msg_count => x_msg_count,
452 x_msg_data => x_msg_data);
453
454 EXCEPTION
455
456 ------------------------------------------------------------
457 -- Exception handling
458 ------------------------------------------------------------
459
460 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
461 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'Error (EXCP) => '||SQLERRM);
462
463 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
464 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,'bulk_process',
465 'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
466 END IF;
467
468 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
469 p_api_name => l_api_name,
470 p_pkg_name => G_PKG_NAME,
471 p_exc_name => 'Okl_Api.G_RET_STS_ERROR',
472 x_msg_count => x_msg_count,
473 x_msg_data => x_msg_data,
474 p_api_type => '_PVT');
475
476 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
477 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'Error (UNEXP) => '||SQLERRM);
478
479 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
480 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,'bulk_process',
481 'EXCEPTION :'||'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
482 END IF;
483
484 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
485 p_api_name => l_api_name,
486 p_pkg_name => G_PKG_NAME,
487 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
488 x_msg_count => x_msg_count,
489 x_msg_data => x_msg_data,
490 p_api_type => '_PVT');
491
492 WHEN OTHERS THEN
493 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'Error (OTHERS 1) => '||SQLERRM);
494
495 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
496 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,'bulk_process',
497 'EXCEPTION :'||'OTHERS');
498 END IF;
499
500 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
501 p_api_name => l_api_name,
502 p_pkg_name => G_PKG_NAME,
503 p_exc_name => 'OTHERS',
504 x_msg_count => x_msg_count,
505 x_msg_data => x_msg_data,
506 p_api_type => '_PVT');
507
508
509
510 END bulk_process;
511
512 -- populate the tbl structures
513 PROCEDURE process_ie_tbl
514 ( p_api_version IN NUMBER
515 ,p_init_msg_list IN VARCHAR2
516 ,x_return_status OUT NOCOPY VARCHAR2
517 ,x_msg_count OUT NOCOPY NUMBER
518 ,x_msg_data OUT NOCOPY VARCHAR2
519 ,p_commit IN VARCHAR2
520 ,p_contract_number IN VARCHAR2 DEFAULT NULL
521 ,p_ie_tbl1 IN ie_tbl_type1
522 ,p_ie_tbl2 IN ie_tbl_type2
523 ,p_end_of_records IN VARCHAR2 DEFAULT NULL
524 )
525 IS
526 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_IE_TBL';
527 l_xsi_id OKL_EXT_SELL_INVS_B.id%TYPE;
528 l_xls_id OKL_XTL_SELL_INVS_B.id%TYPE;
529 l_esd_id OKL_XTD_SELL_INVS_B.id%TYPE;
530 l_legal_entity_id OKL_EXT_SELL_INVS_B.legal_entity_id%TYPE; -- for LE Uptake project 08-11-2006
531
532 -- Selects all distributions created by the accounting Engine
533 CURSOR acc_dstrs_csr(p_source_id IN NUMBER, p_source_table IN VARCHAR2) IS
534 SELECT cr_dr_flag,
535 code_combination_id,
536 source_id,
537 amount,
538 percentage,
539 --Start code changes for rev rec by fmiao on 10/05/2004
540 NVL(comments, '-99') comments --End code changes for rev rec by fmiao on 10/05/2004
541 FROM okl_trns_acc_dstrs
542 WHERE source_id = p_source_id
543 AND source_table = p_source_table;
544
545 -- Local Variables Used in this API
546 l_api_version NUMBER := 1;
547 l_init_msg_list VARCHAR2(1);
548 l_return_status VARCHAR2(1);
549 l_msg_count NUMBER;
550 l_msg_data VARCHAR2(2000);
551 /*
552 -- In and Out records for the Internal AR transaction tables
553 l_taiv_rec okl_tai_pvt.taiv_rec_type;
554 x_taiv_rec okl_tai_pvt.taiv_rec_type;
555 n_taiv_rec okl_tai_pvt.taiv_rec_type;
556 null_taiv_rec okl_tai_pvt.taiv_rec_type;
557
558 l_tilv_rec okl_til_pvt.tilv_rec_type;
559 x_tilv_rec okl_til_pvt.tilv_rec_type;
560 null_tilv_rec okl_til_pvt.tilv_rec_type;
561
562 l_tldv_rec okl_tld_pvt.tldv_rec_type;
563 x_tldv_rec okl_tld_pvt.tldv_rec_type;
564 null_tldv_rec okl_tld_pvt.tldv_rec_type;
565
566 -- In and Out records for the external sell invoice tables
567 l_xsiv_rec okl_xsi_pvt.xsiv_rec_type;
568 x_xsiv_rec okl_xsi_pvt.xsiv_rec_type;
569 d_xsiv_rec okl_xsi_pvt.xsiv_rec_type;
570 null_xsiv_rec okl_xsi_pvt.xsiv_rec_type;
571
572 l_xlsv_rec okl_xls_pvt.xlsv_rec_type;
573 x_xlsv_rec okl_xls_pvt.xlsv_rec_type;
574 d_xlsv_rec okl_xls_pvt.xlsv_rec_type;
575 null_xlsv_rec okl_xls_pvt.xlsv_rec_type;
576
577 l_esdv_rec okl_esd_pvt.esdv_rec_type;
578 x_esdv_rec okl_esd_pvt.esdv_rec_type;
579 d_esdv_rec okl_esd_pvt.esdv_rec_type;
580 null_esdv_rec okl_esd_pvt.esdv_rec_type;
581
582 i NUMBER;
583 l_recv_inv_id NUMBER;
584 tab_cntr NUMBER;
585
586 type int_hdr_rec_type IS record(tai_id NUMBER := okl_api.g_miss_num, return_status VARCHAR2(1));
587
588 type int_hdr_tbl_type IS TABLE OF int_hdr_rec_type INDEX BY binary_integer;
589
590 int_hdr_status int_hdr_tbl_type;
591
592 CURSOR del_xsi_3csr(p_tai_id NUMBER) IS
593 SELECT xsi.id xsi_id,
594 xls.id xls_id
595 FROM okl_ext_sell_invs_v xsi,
596 okl_xtl_sell_invs_v xls,
597 okl_trx_ar_invoices_v tai,
598 okl_txl_ar_inv_lns_v til,
599 okl_txd_ar_ln_dtls_v tld
600 WHERE til.tai_id = tai.id
601 AND tld.til_id_details = til.id
602 AND xls.xsi_id_details = xsi.id
603 AND xls.tld_id = tld.id
604 AND tai.id = p_tai_id;
605
606 CURSOR del_xsi_2csr(p_tai_id NUMBER) IS
607 SELECT xsi.id xsi_id,
608 xls.id xls_id
609 FROM okl_ext_sell_invs_v xsi,
610 okl_xtl_sell_invs_v xls,
611 okl_trx_ar_invoices_v tai,
612 okl_txl_ar_inv_lns_v til
613 WHERE til.tai_id = tai.id
614 AND xls.xsi_id_details = xsi.id
615 AND xls.til_id = til.id
616 AND tai.id = p_tai_id;
617
618 CURSOR del_xtd_csr(p_xls_id NUMBER) IS
619 SELECT id esd_id
620 FROM okl_xtd_sell_invs_v
621 WHERE xls_id = p_xls_id;
622 */
623 i NUMBER;
624 l_recv_inv_id NUMBER;
625 CURSOR reverse_csr1(p_tld_id NUMBER) IS
626 SELECT receivables_invoice_id
627 FROM okl_txd_ar_ln_dtls_v
628 WHERE id = p_tld_id;
629
630 CURSOR reverse_csr2(p_til_id NUMBER) IS
631 SELECT receivables_invoice_id
632 FROM okl_txl_ar_inv_lns_v
633 WHERE id = p_til_id;
634
635 -- Cursors to fetch Rule based data
636 CURSOR customer_id_csr(p_khr_id NUMBER) IS
637 SELECT object1_id1
638 FROM okc_k_party_roles_b
639 WHERE jtot_object1_code = 'OKX_PARTY'
640 AND rle_code = 'CUSTOMER'
641 AND chr_id = p_khr_id
642 and dnz_chr_id = chr_id ;
643
644 l_jtot_object1_code okc_rules_b.jtot_object1_code%TYPE;
645 l_jtot_object2_code okc_rules_b.jtot_object2_code%TYPE;
646 l_object1_id1 okc_rules_b.object1_id1%TYPE;
647 l_object1_id2 okc_rules_b.object1_id2%TYPE;
648
649 CURSOR rule_code_csr(p_khr_id NUMBER, p_rule_category VARCHAR2) IS
650 SELECT jtot_object1_code,
651 object1_id1,
652 object1_id2
653 FROM okc_rules_b
654 WHERE rgp_id =
655 (SELECT id
656 FROM okc_rule_groups_b
657 WHERE dnz_chr_id = p_khr_id
658 AND cle_id IS NULL
659 AND rgd_code = 'LABILL')
660 AND rule_information_category = p_rule_category;
661
662 --commented out for rules migration
663
664 /*CURSOR bto_csr( p_khr_id NUMBER, p_rule_category VARCHAR2 ) IS
665 SELECT Jtot_object1_code, Jtot_object2_code, object1_id1
666 FROM OKC_RULES_B
667 WHERE Rgp_id = (SELECT id
668 FROM Okc_rule_groups_B
669 WHERE dnz_chr_id = p_khr_id AND cle_id IS NULL
670 AND rgd_code = 'LABILL') AND
671 rule_information_category = p_rule_category;*/
672
673 CURSOR cust_acct_id_csr(p_id1 NUMBER) IS
674 SELECT cust_acct_site_id,
675 payment_term_id
676 FROM okx_cust_site_uses_v
677 WHERE id1 = p_id1;
678
679 l_cust_acct_site_id okx_cust_site_uses_v.cust_acct_site_id%TYPE;
680 l_cust_bank_acct okx_rcpt_method_accounts_v.bank_account_id%TYPE;
681 l_payment_term_id okx_cust_site_uses_v.payment_term_id%TYPE;
682
683 l_rulv_rec Okl_Rule_Apis_Pvt.rulv_rec_type;
684 null_rulv_rec Okl_Rule_Apis_Pvt.rulv_rec_type;
685
686 CURSOR cust_trx_type_csr(p_sob_id NUMBER, p_org_id NUMBER) IS
687 SELECT id1
688 FROM okx_cust_trx_types_v
689 WHERE name = 'Invoice-OKL'
690 AND set_of_books_id = p_sob_id
691 AND org_id = p_org_id;
692
693 CURSOR cm_trx_type_csr(p_sob_id NUMBER, p_org_id NUMBER) IS
694 SELECT id1
695 FROM okx_cust_trx_types_v
696 WHERE name = 'Credit Memo-OKL'
697 AND set_of_books_id = p_sob_id
698 AND org_id = p_org_id;
699
700 /*commented out for rules migration on 21-Aug-2003
701 CURSOR cust_id_csr(p_khr_id NUMBER) IS
702 SELECT object1_id1
703 FROM okc_rules_b rul
704 WHERE rul.rule_information_category = 'CAN'
705 AND EXISTS (SELECT '1' FROM okc_rule_groups_b rgp
706 WHERE rgp.id = rul.rgp_id
707 AND rgp.rgd_code = 'LACAN'
708 AND rgp.chr_id = rul.dnz_chr_id
709 AND rgp.chr_id = p_khr_id);
710 */
711
712 CURSOR org_id_csr(p_khr_id NUMBER) IS
713 SELECT authoring_org_id
714 FROM okc_k_headers_b
715 WHERE id = p_khr_id;
716
717 /*CURSOR Cur_address_billto(p_id IN VARCHAR2,Code VARCHAR2) IS
718 SELECT A.cust_account_id,
719 A.cust_acct_site_id,
720 A.payment_term_id
721 FROM Okx_cust_site_uses_v A, okx_customer_accounts_v C
722 WHERE A.id1 = p_id
723 AND C.id1 = A.cust_account_id
724 AND A.site_use_code = Code;*/
725 --added for rules migration
726 CURSOR cur_address_billto(p_contract_id IN VARCHAR2) IS
727 SELECT a.cust_acct_id cust_account_id,
728 b.cust_acct_site_id,
729 c.standard_terms payment_term_id
730 FROM okc_k_headers_v a,
731 okx_cust_site_uses_v b,
732 hz_customer_profiles c
733 WHERE a.id = p_contract_id
734 AND a.bill_to_site_use_id = b.id1
735 AND a.bill_to_site_use_id = c.site_use_id(+);
736
737 billto_rec cur_address_billto % ROWTYPE;
738
739 CURSOR rcpt_mthd_csr(p_cust_rct_mthd NUMBER) IS
740 SELECT c.receipt_method_id
741 FROM ra_cust_receipt_methods c
742 WHERE c.cust_receipt_method_id = p_cust_rct_mthd;
743
744 -- For bank accounts
745 CURSOR bank_acct_csr(p_id NUMBER) IS
746 SELECT bank_account_id
747 FROM okx_rcpt_method_accounts_v
748 WHERE id1 = p_id;
749
750 -- Default term Id
751 cursor std_terms_csr IS
752 SELECT B.TERM_ID
753 FROM RA_TERMS_TL T, RA_TERMS_B B
754 where T.name = 'IMMEDIATE' and T.LANGUAGE = userenv('LANG')
755 and B.TERM_ID = T.TERM_ID;
756
757 CURSOR cntrct_csr(p_khr_id NUMBER) IS
758 SELECT contract_number
759 FROM okc_k_headers_b
760 WHERE id = p_khr_id;
761
762 CURSOR sty_id_csr(p_sty_id NUMBER) IS
763 SELECT name
764 FROM okl_strm_type_v
765 WHERE id = p_sty_id;
766
767 CURSOR rcpt_method_csr(p_rct_method_id NUMBER) IS
768 SELECT c.creation_method_code
769 FROM ar_receipt_methods m,
770 ar_receipt_classes c
771 WHERE m.receipt_class_id = c.receipt_class_id
772 AND m.receipt_method_id = p_rct_method_id;
773
774 l_contract_number okc_k_headers_b.contract_number%TYPE;
775 l_stream_name okl_strm_type_v.name%TYPE;
776 l_rct_method_code ar_receipt_classes.creation_method_code%TYPE;
777
778 -- Get currency attributes
779 CURSOR l_curr_csr(cp_currency_code VARCHAR2) IS
780 SELECT c.minimum_accountable_unit,
781 c.PRECISION
782 FROM fnd_currencies c
783 WHERE c.currency_code = cp_currency_code;
784
785 l_min_acct_unit fnd_currencies.minimum_accountable_unit%TYPE;
786 l_precision fnd_currencies.PRECISION %TYPE;
787
788 l_rounded_amount okl_txl_ar_inv_lns_v.amount%TYPE;
789
790 --Start code added by pgomes on 20-NOV-2002
791 SUBTYPE khr_id_type IS okl_k_headers_v.khr_id%TYPE;
792 l_khr_id khr_id_type;
793 l_currency_code okl_ext_sell_invs_b.currency_code%TYPE;
794 l_currency_conversion_type okl_ext_sell_invs_b.currency_conversion_type%TYPE;
795 l_currency_conversion_rate okl_ext_sell_invs_b.currency_conversion_rate%TYPE;
796 l_currency_conversion_date okl_ext_sell_invs_b.currency_conversion_date%TYPE;
797
798 --Get currency conversion attributes for a contract
799 CURSOR l_curr_conv_csr(cp_khr_id IN khr_id_type) IS
800 SELECT currency_code,
801 currency_conversion_type,
802 currency_conversion_rate,
803 currency_conversion_date
804 FROM okl_k_headers_full_v
805 WHERE id = cp_khr_id;
806
807 --End code added by pgomes on 20-NOV-2002
808
809 /* 5162232 Start
810 -- Tax Cursor for Exempt Or Standard
811 CURSOR astx_csr ( p_khr_id NUMBER ) IS
812 SELECT rule_information1
813 FROM OKC_RULES_B rul,
814 Okc_rule_groups_B rgp
815 WHERE rul.rgp_id = rgp.id AND
816 rgp.rgd_code = 'LAASTX' AND
817 rgp.dnz_chr_id = rgp.chr_id AND
818 rul.rule_information_category = 'LAASTX' AND
819 rgp.dnz_chr_id = p_khr_id;
820
821 -- Tax Cursor for Exempt Or Standard at Line level
822 CURSOR astx_line_csr ( p_khr_id NUMBER, p_cle_id NUMBER ) IS
823 SELECT rule_information1
824 FROM OKC_RULES_B rul,
825 Okc_rule_groups_B rgp
826 WHERE rul.rgp_id = rgp.id AND
827 rgp.rgd_code = 'LAASTX' AND
828 rgp.cle_id = p_cle_id AND
829 rul.rule_information_category = 'LAASTX' AND
830 rgp.dnz_chr_id = p_khr_id;
831
832 l_asst_tax OKC_RULES_B.rule_information1%TYPE;
833 l_asst_line_tax OKC_RULES_B.rule_information1%TYPE;
834
835 5162232 End */ -- Performance Improvement
836 l_max_commit NUMBER := 500;
837 --l_commit_cnt NUMBER;
838
839 -- ----------------------
840 -- Std requests columns
841 -- ----------------------
842 l_request_id NUMBER(15);
843 l_program_application_id NUMBER(15);
844 l_program_id NUMBER(15);
845 l_program_update_date DATE;
846
847 /*
848 -- ------------------------------------------------
849 -- Printing and debug log
850 -- ------------------------------------------------
851 l_request_id NUMBER;
852
853 CURSOR req_id_csr IS
854 SELECT decode(fnd_global.conc_request_id, -1, NULL, fnd_global.conc_request_id)
855 FROM dual;
856
857 ------------------------------------------------------------
858 -- Operating Unit
859 ------------------------------------------------------------
860 CURSOR op_unit_csr IS
861 SELECT name
862 FROM hr_operating_units
863 WHERE organization_id = nvl(to_number(substrb(userenv('CLIENT_INFO'), 1, 10)), -99);
864
865 CURSOR xsi_cnt_succ_csr(p_req_id NUMBER, p_sts VARCHAR2) IS
866 SELECT COUNT(*)
867 FROM okl_ext_sell_invs_v
868 WHERE trx_status_code = p_sts
869 AND request_id = p_req_id;
870
871 CURSOR xsi_cnt_err_csr(p_req_id NUMBER, p_sts VARCHAR2) IS
872 SELECT COUNT(*)
873 FROM okl_ext_sell_invs_v
874 WHERE trx_status_code = p_sts
875 AND request_id = p_req_id;
876
877 l_succ_cnt NUMBER;
878 l_err_cnt NUMBER;
879 l_op_unit_name hr_operating_units.name%TYPE;
880 lx_msg_data VARCHAR2(450);
881 l_msg_index_out NUMBER := 0;
882
883 -- ------------------------------------------------
884 -- Bind variables to address issues in bug 3761940
885 -- ------------------------------------------------
886 submitted_sts okl_ext_sell_invs_v.trx_status_code%TYPE;
887 error_sts okl_ext_sell_invs_v.trx_status_code%TYPE;
888 */
889 -- -------------------------------------------
890 -- To support new fields in XSI and XLS
891 -- Added on 21-MAR-2005
892 -- -------------------------------------------
893 -- rseela BUG# 4733028 Start: fetching review invoice flag
894 CURSOR inv_frmt_csr(cp_khr_id IN NUMBER) IS
895 SELECT inf.id,
896 rul.rule_information4 review_invoice_yn
897 FROM okc_rule_groups_v rgp,
898 okc_rules_v rul,
899 okl_invoice_formats_v inf
900 WHERE rgp.dnz_chr_id = cp_khr_id
901 AND rgp.chr_id = rgp.dnz_chr_id
902 AND rgp.id = rul.rgp_id
903 AND rgp.cle_id IS NULL
904 AND rgp.rgd_code = 'LABILL'
905 AND rul.rule_information_category = 'LAINVD'
906 AND rul.rule_information1 = inf.name;
907
908 l_inf_id okl_invoice_formats_v.id%TYPE;
909
910 -- -------------------------------------------
911 -- To support private label transfers to
912 -- AR. Bug 4525643
913 -- -------------------------------------------
914 CURSOR pvt_label_csr(cp_khr_id IN NUMBER) IS
915 SELECT rule_information1 private_label
916 FROM okc_rule_groups_b a,
917 okc_rules_b b
918 WHERE a.dnz_chr_id = cp_khr_id
919 AND a.rgd_code = 'LALABL'
920 AND a.id = b.rgp_id
921 AND b.rule_information_category = 'LALOGO';
922
923 l_private_label okc_rules_b.rule_information1%TYPE;
924
925 -- to get inventory_org_id bug 4890024 begin
926 CURSOR inv_org_id_csr(p_contract_id NUMBER) IS
927 SELECT NVL(inv_organization_id, -99)
928 FROM okc_k_headers_b
929 WHERE id = p_contract_id;
930 -- bug 4890024 end
931 --bug 5160519
932 lx_remrkt_sty_id NUMBER;
933 l_populate_pmnt_method VARCHAR2(1) := 'Y';
934 l_populate_bank_acct VARCHAR2(1) := 'Y';
935 --bug 5160519: end
936
937 CURSOR get_languages IS
938 SELECT *
939 FROM FND_LANGUAGES
940 WHERE INSTALLED_FLAG IN ('I', 'B');
941
942 -- Start : Bug#5964007 : PRASJAIN
943 -- Cursor to check if 3 level credit memo is on-account
944 CURSOR c_3level_cm(p_tld_id OKL_TXD_AR_LN_DTLS_B.ID%TYPE) IS
945 SELECT 'X' FROM
946 OKL_TXD_AR_LN_DTLS_B
947 WHERE ID = p_tld_id
948 AND TLD_ID_REVERSES IS NULL;
949
950 -- Cursor to check if 2 level credit memo is on-account
951 CURSOR c_2level_cm(p_til_id OKL_TXL_AR_INV_LNS_B.ID%TYPE) IS
952 SELECT 'X' FROM
953 OKL_TXL_AR_INV_LNS_B
954 WHERE ID = p_til_id
955 AND TIL_ID_REVERSES IS NULL;
956
957 l_on_acc_cm BOOLEAN;
958 l_chk VARCHAR2(1);
959 -- End : Bug#5964007 : PRASJAIN
960
961 BEGIN
962
963 l_return_status := Okl_Api.START_ACTIVITY(
964 p_api_name => l_api_name,
965 p_pkg_name => G_PKG_NAME,
966 p_init_msg_list => p_init_msg_list,
967 l_api_version => l_api_version,
968 p_api_version => p_api_version,
969 p_api_type => '_PVT',
970 x_return_status => l_return_status);
971
972 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
973
974 IF(Fnd_Log.level_procedure >= Fnd_Log.g_current_runtime_level) THEN
975 Fnd_Log.string(Fnd_Log.level_procedure, 'process_ie_tbl', 'Begin(+)');
976 END IF;
977
978 -- ------------------------
979 -- Print Input variables
980 -- ------------------------
981 print_to_log('TBL p_commit ' || p_commit);
982 print_to_log('TBL p_contract_number ' || p_contract_number);
983
984 -- This cursor processes all records with 3-level of
985 -- detail in the internal transaction table
986
987 /*
988 i := 0;
989
990 tab_cntr := 0;
991 int_hdr_status(tab_cntr).tai_id := 0;
992
993 -- Initialize commit counter
994 l_commit_cnt := 0;
995
996 FOR ln_dtls_rec IN int_lns_csr1
997 LOOP
998 l_commit_cnt := l_commit_cnt + 1;
999
1000 -- Initialize Records
1001 l_xsiv_rec := null_xsiv_rec;
1002 x_xsiv_rec := null_xsiv_rec;
1003
1004 l_xlsv_rec := null_xlsv_rec;
1005 x_xlsv_rec := null_xlsv_rec;
1006
1007 l_esdv_rec := null_esdv_rec;
1008 x_esdv_rec := null_esdv_rec;
1009 */
1010 IF p_end_of_records = 'Y' THEN
1011 PRINT_TO_LOG('TBL Done building XSI, XLS and XTD records ...');
1012
1013 -- caling Bulk insert
1014 bulk_process
1015 (p_api_version
1016 ,p_init_msg_list
1017 ,x_return_status
1018 ,x_msg_count
1019 ,x_msg_data
1020 ,p_commit);
1021
1022
1023 ELSE -- p_end_of_records = 'N'
1024 BEGIN
1025 SELECT
1026 DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),
1027 DECODE(Fnd_Global.PROG_APPL_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID),
1028 DECODE(Fnd_Global.CONC_PROGRAM_ID,-1,NULL,Fnd_Global.CONC_PROGRAM_ID),
1029 DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,SYSDATE)
1030 INTO
1031 l_request_id,
1032 l_program_application_id,
1033 l_program_id,
1034 l_program_update_date
1035 FROM dual;
1036 EXCEPTION
1037 WHEN OTHERS THEN
1038 Fnd_File.PUT_LINE (Fnd_File.LOG,'(Exception): When resolving request_id'||SQLERRM );
1039 END;
1040
1041
1042 -- Build table records for bulk processing
1043
1044 PRINT_TO_LOG('Building XSI, XLS and XTD records ...');
1045
1046
1047 IF (p_ie_tbl1.COUNT > 0) THEN
1048 PRINT_TO_LOG('TBL p_ie_tbl1.COUNT= '||p_ie_tbl1.COUNT);
1049 i := 0;
1050
1051 l_commit_cnt := 0;
1052
1053 l_xsi_cnt := 0;
1054 l_xls_cnt := 0;
1055 l_esd_cnt := 0;
1056 l_xsitl_cnt := 0;
1057 l_xlstl_cnt := 0;
1058 l_esdtl_cnt := 0;
1059 l_tai_id_cnt := 0;
1060
1061 xsi_tbl.DELETE;
1062 xls_tbl.DELETE;
1063 esd_tbl.DELETE;
1064 xsitl_tbl.DELETE;
1065 xlstl_tbl.DELETE;
1066 esdtl_tbl.DELETE;
1067
1068 tai_id_tbl.DELETE;
1069
1070 FOR k IN p_ie_tbl1.FIRST..p_ie_tbl1.LAST LOOP
1071
1072 l_commit_cnt := l_commit_cnt + 1;
1073 l_xsi_cnt := l_xsi_cnt + 1;
1074 l_tai_id_cnt := l_tai_id_cnt + 1;
1075
1076 IF l_commit_cnt > l_max_commit_cnt THEN
1077
1078 PRINT_TO_LOG(' TBL Done building XSI, XLS and XTD records ...');
1079
1080
1081 -- caling Bulk insert
1082 bulk_process
1083 (p_api_version
1084 ,p_init_msg_list
1085 ,x_return_status
1086 ,x_msg_count
1087 ,x_msg_data
1088 ,p_commit);
1089
1090 l_commit_cnt := 0;
1091
1092 END IF;
1093
1094 -- Initialize variable for updating xsi trx_status_code
1095 tai_id_tbl(l_tai_id_cnt) := p_ie_tbl1(k).tai_id;
1096 PRINT_TO_LOG('TBL tai_id_tbl(l_tai_id_cnt): '||tai_id_tbl(l_tai_id_cnt));
1097 PRINT_TO_LOG('TBL p_ie_tbl1(k).tld_id : '||p_ie_tbl1(k).tld_id);
1098
1099 --added by pgomes 11/20/2002 (multi-currency er)
1100 l_khr_id := p_ie_tbl1(k).contract_id;
1101
1102 --Start code added by pgomes on 11/21/2002
1103 l_currency_code := NULL;
1104 l_currency_conversion_type := NULL;
1105 l_currency_conversion_rate := NULL;
1106 l_currency_conversion_date := NULL;
1107
1108 print_to_log('TBL l_khr_id: ' || l_khr_id);
1109 FOR cur IN l_curr_conv_csr(l_khr_id)
1110 LOOP
1111 l_currency_code := cur.currency_code;
1112 l_currency_conversion_type := cur.currency_conversion_type;
1113 l_currency_conversion_rate := cur.currency_conversion_rate;
1114 l_currency_conversion_date := cur.currency_conversion_date;
1115 END LOOP;
1116
1117 --End code added by pgomes on 11/21/2002
1118
1119 l_contract_number := NULL;
1120
1121 OPEN cntrct_csr(l_khr_id);
1122 FETCH cntrct_csr
1123 INTO l_contract_number;
1124 CLOSE cntrct_csr;
1125
1126 -- Initialize variable
1127 l_stream_name := NULL;
1128
1129 OPEN sty_id_csr(p_ie_tbl1(k).sty_id);
1130 FETCH sty_id_csr
1131 INTO l_stream_name;
1132 CLOSE sty_id_csr;
1133
1134 -- Start; Bug 4525643; stmathew
1135 -- Private Label
1136 l_private_label := NULL;
1137
1138 OPEN pvt_label_csr(p_ie_tbl1(k).contract_id);
1139 FETCH pvt_label_csr
1140 INTO l_private_label;
1141 CLOSE pvt_label_csr;
1142 -- End; Bug 4525643; stmathew
1143
1144 print_to_log('TBL Processing: Contract #: ' || l_contract_number || ' ,Stream: ' || l_stream_name || ' ,Amount: ' || p_ie_tbl1(k).amount);
1145
1146 l_jtot_object1_code := NULL;
1147 l_object1_id1 := NULL;
1148 l_object1_id2 := NULL;
1149 l_jtot_object2_code := NULL;
1150
1151 i := i + 1;
1152 -- for LE Uptake project 08-11-2006
1153 IF (p_ie_tbl1(k).legal_entity_id IS NULL OR (p_ie_tbl1(k).legal_entity_id = Okl_Api.G_MISS_NUM)) THEN
1154 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(p_ie_tbl1(k).contract_id);
1155 ELSE l_legal_entity_id := p_ie_tbl1(k).legal_entity_id;
1156 END IF;
1157 xsi_tbl(l_xsi_cnt).legal_entity_id := l_legal_entity_id;
1158 -- for LE Uptake project 08-11-2006
1159 xsi_tbl(l_xsi_cnt).trx_status_code := p_ie_tbl1(k).trx_status_code;
1160 --TAI
1161 xsi_tbl(l_xsi_cnt).isi_id := NULL;
1162 --Populated later during fetch
1163
1164 --l_xsiv_rec.TRX_NUMBER := ln_dtls_rec.trx_number; -- Not populated in OKS
1165 xsi_tbl(l_xsi_cnt).trx_number := NULL;
1166
1167 xsi_tbl(l_xsi_cnt).trx_date := p_ie_tbl1(k).date_invoiced;
1168
1169 xsi_tbl(l_xsi_cnt).receipt_method_id := NULL;
1170
1171 IF p_ie_tbl1(k).contract_id IS NOT NULL THEN
1172 -- Changed if condition for bug 4155476
1173
1174 IF(p_ie_tbl1(k).irm_id IS NULL) THEN
1175 --AND ln_dtls_rec.IXX_ID IS NULL )THEN
1176
1177 OPEN rule_code_csr(p_ie_tbl1(k).contract_id, 'LAPMTH');
1178 FETCH rule_code_csr
1179 INTO l_jtot_object1_code,
1180 l_object1_id1,
1181 l_object1_id2;
1182 CLOSE rule_code_csr;
1183
1184 IF l_object1_id2 <> '#' THEN
1185 xsi_tbl(l_xsi_cnt).receipt_method_id := l_object1_id2;
1186 ELSE
1187 -- This cursor needs to be removed when the view changes to
1188 -- include id2
1189
1190 OPEN rcpt_mthd_csr(l_object1_id1);
1191 FETCH rcpt_mthd_csr
1192 INTO xsi_tbl(l_xsi_cnt).receipt_method_id;
1193 CLOSE rcpt_mthd_csr;
1194 END IF;
1195
1196 ELSE
1197 xsi_tbl(l_xsi_cnt).receipt_method_id := p_ie_tbl1(k).irm_id;
1198 END IF;
1199
1200 print_to_log('TBL....Receipt Method ID: ' || xsi_tbl(l_xsi_cnt).receipt_method_id);
1201
1202 -- Null out local variables
1203 l_jtot_object1_code := NULL;
1204 l_object1_id1 := NULL;
1205 l_jtot_object2_code := NULL;
1206
1207 --commented out for rules migration
1208
1209 /*
1210 OPEN bto_csr( ln_dtls_rec.contract_id, 'BTO');
1211 FETCH bto_csr INTO l_jtot_object1_code,
1212 l_jtot_object2_code,
1213 l_object1_id1;
1214 CLOSE bto_csr;
1215 */
1216
1217 billto_rec.cust_account_id := NULL;
1218 billto_rec.cust_acct_site_id := NULL;
1219 billto_rec.payment_term_id := NULL;
1220
1221 OPEN cur_address_billto(p_ie_tbl1(k).contract_id);
1222 FETCH cur_address_billto
1223 INTO billto_rec;
1224 CLOSE cur_address_billto;
1225
1226 xsi_tbl(l_xsi_cnt).customer_id := NVL(p_ie_tbl1(k).ixx_id, billto_rec.cust_account_id);
1227
1228 print_to_log('TBL....Customer ID: ' || xsi_tbl(l_xsi_cnt).customer_id);
1229 -- FOR Term ID
1230
1231 OPEN std_terms_csr;
1232 FETCH std_terms_csr
1233 INTO xsi_tbl(l_xsi_cnt).term_id;
1234 CLOSE std_terms_csr;
1235
1236 print_to_log('TBL....Term ID: ' || xsi_tbl(l_xsi_cnt).term_id);
1237
1238 xsi_tbl(l_xsi_cnt).customer_address_id := NVL(p_ie_tbl1(k).ibt_id, billto_rec.cust_acct_site_id);
1239
1240 print_to_log('TBL....Customer Address ID: ' || xsi_tbl(l_xsi_cnt).customer_address_id);
1241
1242 print_to_log('TBL p_ie_tbl1(k).org_id: ' || p_ie_tbl1(k).org_id);
1243 print_to_log('TBL p_ie_tbl1(k).contract_id: ' || p_ie_tbl1(k).contract_id);
1244 IF p_ie_tbl1(k).org_id IS NULL THEN
1245
1246 OPEN org_id_csr(p_ie_tbl1(k).contract_id);
1247 FETCH org_id_csr
1248 INTO xsi_tbl(l_xsi_cnt).org_id;
1249 CLOSE org_id_csr;
1250 ELSE
1251 xsi_tbl(l_xsi_cnt).org_id := p_ie_tbl1(k).org_id;
1252 --TAI
1253 END IF;
1254
1255 print_to_log('TBL....Org ID: ' || xsi_tbl(l_xsi_cnt).org_id);
1256
1257
1258 -- To resolve the bank account for the customer
1259 -- If receipt method is manual do not supply customer bank account
1260 -- Id. This is required for Auto Invoice Validation
1261
1262 -- Null out variable
1263 l_rct_method_code := NULL;
1264
1265 OPEN rcpt_method_csr(xsi_tbl(l_xsi_cnt).receipt_method_id);
1266 FETCH rcpt_method_csr
1267 INTO l_rct_method_code;
1268 CLOSE rcpt_method_csr;
1269
1270 --Null out variables
1271 l_jtot_object1_code := NULL;
1272 l_object1_id1 := NULL;
1273 l_object1_id2 := NULL;
1274 l_cust_bank_acct := NULL;
1275
1276 IF(l_rct_method_code <> 'MANUAL') THEN
1277
1278 OPEN rule_code_csr(p_ie_tbl1(k).contract_id, 'LABACC');
1279 FETCH rule_code_csr
1280 INTO l_jtot_object1_code,
1281 l_object1_id1,
1282 l_object1_id2;
1283 CLOSE rule_code_csr;
1284
1285 OPEN bank_acct_csr(l_object1_id1);
1286 FETCH bank_acct_csr
1287 INTO l_cust_bank_acct;
1288 CLOSE bank_acct_csr;
1289
1290 xsi_tbl(l_xsi_cnt).customer_bank_account_id := l_cust_bank_acct;
1291 END IF;
1292
1293 --pgomes 11/22/2002 changed below line to output l_cust_bank_acct instead of l_xsiv_rec.customer_bank_account_id
1294 print_to_log('TBL....Bank Acct ID: ' || l_cust_bank_acct);
1295 ELSE
1296 -- Else for contract_id
1297
1298 IF p_ie_tbl1(k).ixx_id IS NULL THEN
1299 --d*bms_output.put_line ('IXX_ID must be populated WHEN the contract header IS NULL!');
1300 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'IXX_ID must be populated WHEN the contract header IS NULL!');
1301 ELSE
1302 xsi_tbl(l_xsi_cnt).customer_id := p_ie_tbl1(k).ixx_id;
1303 END IF;
1304
1305 IF p_ie_tbl1(k).irm_id IS NULL THEN
1306 -- d*bms_output.put_line ('IRM_ID must be populated WHEN the contract header IS NULL!');
1307 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'IRM_ID must be populated WHEN the contract header IS NULL');
1308 ELSE
1309 xsi_tbl(l_xsi_cnt).receipt_method_id := p_ie_tbl1(k).irm_id;
1310 END IF;
1311
1312 IF p_ie_tbl1(k).irt_id IS NULL THEN
1313 -- d*bms_output.put_line ('IRT_ID must be populated WHEN the contract header IS NULL!');
1314 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'IRT_ID must be populated WHEN the contract header IS NULL');
1315 ELSE
1316 xsi_tbl(l_xsi_cnt).term_id := p_ie_tbl1(k).irt_id;
1317 END IF;
1318
1319 IF p_ie_tbl1(k).ibt_id IS NULL THEN
1320 --d*bms_output.put_line ('IBT_ID must be populated WHEN the contract header IS NULL!');
1321 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'IBT_ID must be populated WHEN the contract header IS NULL');
1322 ELSE
1323 xsi_tbl(l_xsi_cnt).customer_address_id := p_ie_tbl1(k).ibt_id;
1324 END IF;
1325
1326 IF p_ie_tbl1(k).org_id IS NULL THEN
1327 --d*bms_output.put_line ('ORG_ID must be populated WHEN the contract header IS NULL!');
1328 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'ORG_ID must be populated WHEN the contract header IS NULL');
1329 ELSE
1330 --l_xsiv_rec.ORG_ID := ln_dtls_rec.ORG_ID; --TAI
1331 xsi_tbl(l_xsi_cnt).org_id := NULL;
1332 END IF;
1333 -- for LE Uptake project 08-11-2006
1334 IF ( p_ie_tbl1(k).legal_entity_id IS NULL OR (p_ie_tbl1(k).legal_entity_id = Okl_Api.G_MISS_NUM)) THEN
1335 --d*bms_output.put_line ('LEGAL_ENTITY_ID must be populated WHEN the contract header IS NULL!');
1336 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'LEGAL_ENTITY_ID must be populated WHEN the contract header IS NULL');
1337 ELSE
1338 xsi_tbl(l_xsi_cnt).legal_entity_id := p_ie_tbl1(k).legal_entity_id;
1339 END IF;
1340 -- for LE Uptake project 08-11-2006
1341 END IF;
1342
1343 --How to get the set_of_books_id ?
1344
1345 IF p_ie_tbl1(k).set_of_books_id IS NULL THEN
1346 xsi_tbl(l_xsi_cnt).set_of_books_id := Okl_Accounting_Util.get_set_of_books_id;
1347 ELSE
1348 xsi_tbl(l_xsi_cnt).set_of_books_id := p_ie_tbl1(k).set_of_books_id;
1349 --TAI
1350 END IF;
1351
1352 print_to_log('TBL ....SET OF Books ID: ' || xsi_tbl(l_xsi_cnt).set_of_books_id);
1353
1354 -- How to get from set_of_books_id
1355 -- This field is varchar2 on the XSI table. Change to number
1356
1357 --Start code added by pgomes on 20-NOV-2002
1358 --Check for currency code
1359
1360 IF p_ie_tbl1(k).currency_code IS NULL THEN
1361 xsi_tbl(l_xsi_cnt).currency_code := l_currency_code;
1362 ELSE
1363 xsi_tbl(l_xsi_cnt).currency_code := p_ie_tbl1(k).currency_code;
1364 END IF;
1365 print_to_log('TBL ....currency_code: ' || xsi_tbl(l_xsi_cnt).currency_code);
1366 --Check for currency conversion type
1367
1368 IF p_ie_tbl1(k).currency_conversion_type IS NULL THEN
1369 xsi_tbl(l_xsi_cnt).currency_conversion_type := l_currency_conversion_type;
1370 ELSE
1371 xsi_tbl(l_xsi_cnt).currency_conversion_type := p_ie_tbl1(k).currency_conversion_type;
1372 END IF;
1373
1374 --Check for currency conversion rate
1375
1376 IF(xsi_tbl(l_xsi_cnt).currency_conversion_type = 'User') THEN
1377
1378 IF(xsi_tbl(l_xsi_cnt).currency_code = Okl_Accounting_Util.get_func_curr_code) THEN
1379 xsi_tbl(l_xsi_cnt).currency_conversion_rate := 1;
1380 ELSE
1381
1382 IF p_ie_tbl1(k).currency_conversion_rate IS NULL THEN
1383 xsi_tbl(l_xsi_cnt).currency_conversion_rate := l_currency_conversion_rate;
1384 ELSE
1385 xsi_tbl(l_xsi_cnt).currency_conversion_rate := p_ie_tbl1(k).currency_conversion_rate;
1386 END IF;
1387
1388 END IF;
1389
1390 ELSE
1391 xsi_tbl(l_xsi_cnt).currency_conversion_rate := NULL;
1392 END IF;
1393
1394 --Check for currency conversion date
1395
1396 IF p_ie_tbl1(k).currency_conversion_date IS NULL THEN
1397 xsi_tbl(l_xsi_cnt).currency_conversion_date := l_currency_conversion_date;
1398 ELSE
1399 xsi_tbl(l_xsi_cnt).currency_conversion_date := p_ie_tbl1(k).currency_conversion_date;
1400 END IF;
1401
1402 --End code added by pgomes on 20-NOV-2002
1403
1404 --Start code added by pgomes on 06-JAN-2003
1405
1406 IF(xsi_tbl(l_xsi_cnt).currency_conversion_type IS NULL) THEN
1407 xsi_tbl(l_xsi_cnt).currency_conversion_type := 'User';
1408 xsi_tbl(l_xsi_cnt).currency_conversion_rate := 1;
1409 xsi_tbl(l_xsi_cnt).currency_conversion_date := SYSDATE;
1410 END IF;
1411
1412 --End code added by pgomes on 06-JAN-2003
1413
1414 print_to_log('TBL....Currency Code: ' || xsi_tbl(l_xsi_cnt).currency_code);
1415 --For Credit Memo Processing
1416
1417 IF p_ie_tbl1(k).tld_id_reverses IS NOT NULL THEN
1418 -- Null out variables
1419 l_recv_inv_id := NULL;
1420
1421 OPEN reverse_csr1(p_ie_tbl1(k).tld_id_reverses);
1422 FETCH reverse_csr1
1423 INTO l_recv_inv_id;
1424 CLOSE reverse_csr1;
1425 xsi_tbl(l_xsi_cnt).reference_line_id := l_recv_inv_id;
1426 ELSE
1427 xsi_tbl(l_xsi_cnt).reference_line_id := NULL;
1428 END IF;
1429
1430 xsi_tbl(l_xsi_cnt).receivables_invoice_id := NULL;
1431 -- Populated later by fetch
1432
1433 -- Populate Customer TRX-TYPE ID From AR setup
1434
1435 IF p_ie_tbl1(k).amount < 0 THEN
1436 xsi_tbl(l_xsi_cnt).term_id := NULL;
1437
1438 --OPEN cm_trx_type_csr(xsi_tbl(l_xsi_cnt).set_of_books_id, xsi_tbl(l_xsi_cnt).org_id);
1439 --xsi_tbl(l_xsi_cnt).org_id was null out, so use p_ie_tbl1(k).org_id
1440 OPEN cm_trx_type_csr(xsi_tbl(l_xsi_cnt).set_of_books_id, p_ie_tbl1(k).org_id);
1441 FETCH cm_trx_type_csr
1442 INTO xsi_tbl(l_xsi_cnt).cust_trx_type_id;
1443 CLOSE cm_trx_type_csr;
1444 ELSE
1445
1446 --OPEN cust_trx_type_csr(xsi_tbl(l_xsi_cnt).set_of_books_id, xsi_tbl(l_xsi_cnt).org_id);
1447 OPEN cust_trx_type_csr(xsi_tbl(l_xsi_cnt).set_of_books_id, p_ie_tbl1(k).org_id);
1448 FETCH cust_trx_type_csr
1449 INTO xsi_tbl(l_xsi_cnt).cust_trx_type_id;
1450 CLOSE cust_trx_type_csr;
1451 END IF;
1452 print_to_log('TBL xsi_tbl(l_xsi_cnt).cust_trx_type_id: ' || xsi_tbl(l_xsi_cnt).cust_trx_type_id);
1453
1454 --l_xsiv_rec.CUST_TRX_TYPE_ID := NULL;
1455
1456 --Updated during consolidation fron INV_MSGS
1457 -- Use messaging API to
1458 -- xsi_tbl(l_xsi_cnt).invoice_message := NULL;
1459 --xsi_tbl(l_xsi_cnt).description := p_ie_tbl1(k).tai_description;
1460 --TAI
1461
1462 /*
1463 -- Null Rule records
1464 l_rulv_rec := null_rulv_rec;
1465 --Tax exempt Y_N from the rules
1466 Okl_Bp_Rules.EXTRACT_RULES(
1467 l_api_version,
1468 l_init_msg_list,
1469 ln_dtls_rec.contract_id,
1470 NULL,
1471 'LAASTX',
1472 'LAASTX',
1473 l_return_status,
1474 l_msg_count,
1475 l_msg_data,
1476 l_rulv_rec);
1477 l_xsiv_rec.TAX_EXEMPT_FLAG := l_rulv_rec.rule_information1;
1478 --l_xsiv_rec.TAX_EXEMPT_FLAG := NULL;
1479 l_xsiv_rec.TAX_EXEMPT_REASON_CODE := NULL;
1480 */
1481
1482 /* 5162232 Start
1483 -- Start Tax Code addition
1484 -- Null Out tax details
1485 l_asst_tax := NULL;
1486 l_asst_line_tax := NULL;
1487
1488 -- Compute Tax Info
1489 OPEN astx_csr( ln_dtls_rec.contract_id );
1490 FETCH astx_csr INTO l_asst_tax;
1491 CLOSE astx_csr;
1492
1493 -- Compute Tax Info at asset line
1494 OPEN astx_line_csr( ln_dtls_rec.contract_id, ln_dtls_rec.kle_id );
1495 FETCH astx_line_csr INTO l_asst_line_tax;
1496 CLOSE astx_line_csr;
1497
1498 -- Set Tax exempt flag to Standard
1499 l_xsiv_rec.tax_exempt_flag := 'S';
1500 l_xsiv_rec.tax_exempt_reason_code := NULL;
1501
1502 IF l_asst_tax IS NOT NULL THEN
1503 -- Check header code to test and set
1504 IF l_asst_tax IN ( 'E','N' ) THEN
1505 l_xsiv_rec.tax_exempt_flag := 'E';
1506 l_xsiv_rec.tax_exempt_reason_code := 'MANUFACTURER';
1507 ELSE
1508 l_xsiv_rec.tax_exempt_flag := 'S';
1509 l_xsiv_rec.tax_exempt_reason_code := NULL;
1510 END IF;
1511
1512 -- Line level rule instance
1513 IF l_asst_line_tax IS NOT NULL THEN
1514 -- Check line code to test and set
1515 IF l_asst_line_tax IN ( 'E','N' ) THEN
1516 l_xsiv_rec.tax_exempt_flag := 'E';
1517 l_xsiv_rec.tax_exempt_reason_code := 'MANUFACTURER';
1518 ELSE
1519 l_xsiv_rec.tax_exempt_flag := 'S';
1520 l_xsiv_rec.tax_exempt_reason_code := NULL;
1521 END IF;
1522
1523 -- if stream is not taxable, override
1524 IF ln_dtls_rec.taxable_default_yn = 'N' THEN
1525 l_xsiv_rec.tax_exempt_flag := 'E';
1526 l_xsiv_rec.tax_exempt_reason_code := 'MANUFACTURER';
1527 END IF;
1528 ELSE
1529 -- if stream is not taxable, override
1530 IF ln_dtls_rec.taxable_default_yn = 'N' THEN
1531 l_xsiv_rec.tax_exempt_flag := 'E';
1532 l_xsiv_rec.tax_exempt_reason_code := 'MANUFACTURER';
1533 END IF;
1534 END IF;
1535 ELSE
1536 -- Line level rule instance
1537 IF l_asst_line_tax IS NOT NULL THEN
1538 -- Check line code to test and set
1539 IF l_asst_line_tax IN ( 'E','N' ) THEN
1540 l_xsiv_rec.tax_exempt_flag := 'E';
1541 l_xsiv_rec.tax_exempt_reason_code := 'MANUFACTURER';
1542 ELSE
1543 l_xsiv_rec.tax_exempt_flag := 'S';
1544 l_xsiv_rec.tax_exempt_reason_code := NULL;
1545 END IF;
1546
1547 -- if stream is not taxable, override
1548 IF ln_dtls_rec.taxable_default_yn = 'N' THEN
1549 l_xsiv_rec.tax_exempt_flag := 'E';
1550 l_xsiv_rec.tax_exempt_reason_code := 'MANUFACTURER';
1551 END IF;
1552 ELSE
1553 -- if stream is not taxable, override
1554 IF ln_dtls_rec.taxable_default_yn = 'N' THEN
1555 l_xsiv_rec.tax_exempt_flag := 'E';
1556 l_xsiv_rec.tax_exempt_reason_code := 'MANUFACTURER';
1557 END IF;
1558 END IF;
1559 END IF;
1560 -- End Tax Code addition
1561 */ -- Set Tax exempt flag to Standard
1562 xsi_tbl(l_xsi_cnt).tax_exempt_flag := 'S';
1563 xsi_tbl(l_xsi_cnt).tax_exempt_reason_code := NULL;
1564 -- 5162232 End
1565 -- Updated after consolidation
1566 --xsi_tbl(l_xsi_cnt).xtrx_cons_invoice_number := NULL;
1567 --xsi_tbl(l_xsi_cnt).xtrx_format_type := NULL;
1568 xsi_tbl(l_xsi_cnt).xtrx_invoice_pull_yn := NULL;
1569
1570 -- Start; Bug 4525643; stmathew
1571 --xsi_tbl(l_xsi_cnt).xtrx_private_label := l_private_label;
1572 -- End; Bug 4525643; stmathew
1573
1574 -- New fields added on 21-MAR-2005
1575 l_inf_id := NULL;
1576 -- rseela BUG# 4733028 Start: populating review invoice flag
1577
1578 OPEN inv_frmt_csr(p_ie_tbl1(k).contract_id);
1579 FETCH inv_frmt_csr
1580 INTO xsi_tbl(l_xsi_cnt).inf_id,
1581 xsi_tbl(l_xsi_cnt).xtrx_invoice_pull_yn;
1582 CLOSE inv_frmt_csr;
1583
1584 -- copied from G
1585
1586 -- Populate id and other columns
1587 l_xsi_id := Okc_P_Util.raw_to_number(sys_guid());
1588 xsi_tbl(l_xsi_cnt).ID := l_xsi_id;
1589 xsi_tbl(l_xsi_cnt).OBJECT_VERSION_NUMBER := 1;
1590 xsi_tbl(l_xsi_cnt).CREATION_DATE := SYSDATE;
1591 xsi_tbl(l_xsi_cnt).CREATED_BY := Fnd_Global.USER_ID;
1592 xsi_tbl(l_xsi_cnt).LAST_UPDATE_DATE := SYSDATE;
1593 xsi_tbl(l_xsi_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
1594 xsi_tbl(l_xsi_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
1595
1596 xsi_tbl(l_xsi_cnt).request_id := l_request_id;
1597 xsi_tbl(l_xsi_cnt).program_application_id := l_program_application_id;
1598 xsi_tbl(l_xsi_cnt).program_id := l_program_id;
1599 xsi_tbl(l_xsi_cnt).program_update_date := l_program_update_date;
1600
1601
1602 FOR l_lang_rec IN get_languages LOOP
1603
1604 l_xsitl_cnt := l_xsitl_cnt + 1;
1605
1606 xsitl_tbl(l_xsitl_cnt).ID := l_xsi_id;
1607 xsitl_tbl(l_xsitl_cnt).xtrx_private_label := l_private_label;
1608 --Original code for the view tbl structure
1609 xsitl_tbl(l_xsitl_cnt).INVOICE_MESSAGE := NULL;
1610 xsitl_tbl(l_xsitl_cnt).DESCRIPTION := p_ie_tbl1(k).tai_description; --TAI
1611 -- Updated after consolidation
1612 xsitl_tbl(l_xsitl_cnt).XTRX_CONS_INVOICE_NUMBER := NULL;
1613 xsitl_tbl(l_xsitl_cnt).XTRX_FORMAT_TYPE := NULL;
1614 --xsitl_tbl(l_xsitl_cnt).XTRX_PRIVATE_LABEL := l_private_label;
1615 xsitl_tbl(l_xsitl_cnt).LANGUAGE := l_lang_rec.language_code;
1616 xsitl_tbl(l_xsitl_cnt).SOURCE_LANG := USERENV('LANG');
1617 xsitl_tbl(l_xsitl_cnt).SFWT_FLAG := 'N';
1618 --xsitl_tbl(l_xsitl_cnt).DESCRIPTION := l_def_desc;
1619
1620 xsitl_tbl(l_xsitl_cnt).CREATION_DATE := SYSDATE;
1621 xsitl_tbl(l_xsitl_cnt).CREATED_BY := Fnd_Global.USER_ID;
1622 xsitl_tbl(l_xsitl_cnt).LAST_UPDATE_DATE := SYSDATE;
1623 xsitl_tbl(l_xsitl_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
1624 xsitl_tbl(l_xsitl_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
1625
1626 END LOOP;
1627
1628 -- end copy from g
1629
1630 -- Start of wraper code generated automatically by Debug code generator for Okl_Ext_Sell_Invs_Pub.INSERT_EXT_SELL_INVS
1631 /*
1632 IF(l_debug_enabled = 'Y') THEN
1633 l_level_procedure := fnd_log.level_procedure;
1634 is_debug_procedure_on := okl_debug_pub.check_log_on(l_module, l_level_procedure);
1635 END IF;
1636
1637 IF(is_debug_procedure_on) THEN
1638 BEGIN
1639 okl_debug_pub.log_debug(l_level_procedure, l_module, 'Begin Debug OKLRIEXB.pls call Okl_Ext_Sell_Invs_Pub.INSERT_EXT_SELL_INVS ');
1640 END;
1641 END IF;
1642
1643 print_xsi_rec(l_xsiv_rec);
1644 okl_ext_sell_invs_pub.insert_ext_sell_invs(l_api_version, l_init_msg_list, x_return_status, x_msg_count, x_msg_data, l_xsiv_rec, x_xsiv_rec);
1645
1646 IF(is_debug_procedure_on) THEN
1647 BEGIN
1648 okl_debug_pub.log_debug(l_level_procedure, l_module, 'End Debug OKLRIEXB.pls call Okl_Ext_Sell_Invs_Pub.INSERT_EXT_SELL_INVS ');
1649 END;
1650 END IF;
1651
1652 -- End of wraper code generated automatically by Debug code generator for Okl_Ext_Sell_Invs_Pub.INSERT_EXT_SELL_INVS
1653
1654 IF(x_return_status = 'S') THEN
1655 print_to_log('====>External Header Created.');
1656 END IF;
1657 */
1658 l_xls_cnt := l_xls_cnt + 1;
1659
1660 -- One of TLD or TIL
1661 xls_tbl(l_xls_cnt).tld_id := p_ie_tbl1(k).tld_id;
1662 -- Updated after Consolidation
1663 xls_tbl(l_xls_cnt).lsm_id := NULL;
1664 xls_tbl(l_xls_cnt).isl_id := 1;
1665 --One of TLD or TIL
1666 xls_tbl(l_xls_cnt).til_id := NULL;
1667
1668 -- To be updated by fetch program
1669 xls_tbl(l_xls_cnt).ill_id := NULL;
1670 xls_tbl(l_xls_cnt).xsi_id_details := l_xsi_id;
1671 xls_tbl(l_xls_cnt).line_type := p_ie_tbl1(k).inv_receiv_line_code;
1672 --??
1673 --xls_tbl(l_xls_cnt).description := p_ie_tbl1(k).til_description;
1674 --TIL
1675 -- Start changes on remarketing by fmiao on 10/18/04 --
1676 xls_tbl(l_xls_cnt).inventory_item_id := p_ie_tbl1(k).inventory_item_id;
1677 -- End changes on remarketing by fmiao on 10/18/04 --
1678 -- Bug 4890024 begin
1679
1680 IF(p_ie_tbl1(k).inventory_org_id IS NULL) THEN
1681
1682 OPEN inv_org_id_csr(p_ie_tbl1(k).contract_id);
1683 FETCH inv_org_id_csr
1684 INTO xls_tbl(l_xls_cnt).inventory_org_id;
1685 CLOSE inv_org_id_csr;
1686 ELSE
1687 xls_tbl(l_xls_cnt).inventory_org_id := p_ie_tbl1(k).inventory_org_id;
1688 END IF;
1689 print_to_log('TBL xls_tbl(l_xls_cnt).inventory_org_id: ' || xls_tbl(l_xls_cnt).inventory_org_id);
1690
1691 -- Bug 4890024 end
1692
1693 -------- Rounded Amount --------------
1694 l_rounded_amount := NULL;
1695 l_min_acct_unit := NULL;
1696 l_precision := NULL;
1697
1698 print_to_log('TBL xsi_tbl(l_xsi_cnt).currency_code: ' || xsi_tbl(l_xsi_cnt).currency_code);
1699 OPEN l_curr_csr(xsi_tbl(l_xsi_cnt).currency_code);
1700 FETCH l_curr_csr
1701 INTO l_min_acct_unit,
1702 l_precision;
1703 CLOSE l_curr_csr;
1704
1705 IF(NVL(l_min_acct_unit, 0) <> 0) THEN
1706 -- Round the amount to the nearest Min Accountable Unit
1707 l_rounded_amount := ROUND(p_ie_tbl1(k).amount / l_min_acct_unit) * l_min_acct_unit;
1708 ELSE
1709 -- Round the amount to the nearest precision
1710 l_rounded_amount := ROUND(p_ie_tbl1(k).amount, l_precision);
1711 END IF;
1712 print_to_log('TBL l_rounded_amount: ' || l_rounded_amount);
1713 print_to_log('TBL l_min_acct_unit: ' || l_min_acct_unit);
1714 print_to_log('TBL p_ie_tbl1(k).amount: ' || p_ie_tbl1(k).amount);
1715
1716 -------- Rounded Amount --------------
1717 xls_tbl(l_xls_cnt).amount := l_rounded_amount;
1718 --TIL
1719
1720 xls_tbl(l_xls_cnt).quantity := p_ie_tbl1(k).quantity;
1721
1722 --copy from g
1723 xls_tbl(l_xls_cnt).sel_id := p_ie_tbl1(k).sel_id; --TIL
1724
1725 -- Updated after Consolidation
1726 xls_tbl(l_xls_cnt).XTRX_CONS_LINE_NUMBER := NULL;
1727 --xls_tbl(l_xls_cnt).XTRX_CONTRACT := NULL;
1728 --xls_tbl(l_xls_cnt).XTRX_ASSET := NULL;
1729 --xls_tbl(l_xls_cnt).XTRX_STREAM_GROUP := NULL;
1730 --xls_tbl(l_xls_cnt).XTRX_STREAM_TYPE := NULL;
1731 xls_tbl(l_xls_cnt).XTRX_CONS_STREAM_ID := NULL;
1732
1733 l_xls_id := Okc_P_Util.raw_to_number(sys_guid());
1734 xls_tbl(l_xls_cnt).ID := l_xls_id;
1735 xls_tbl(l_xls_cnt).OBJECT_VERSION_NUMBER := 1;
1736 xls_tbl(l_xls_cnt).CREATION_DATE := SYSDATE;
1737 xls_tbl(l_xls_cnt).CREATED_BY := Fnd_Global.USER_ID;
1738 xls_tbl(l_xls_cnt).LAST_UPDATE_DATE := SYSDATE;
1739 xls_tbl(l_xls_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
1740 xls_tbl(l_xls_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
1741
1742 xls_tbl(l_xls_cnt).request_id := l_request_id;
1743 xls_tbl(l_xls_cnt).program_application_id := l_program_application_id;
1744 xls_tbl(l_xls_cnt).program_id := l_program_id;
1745 xls_tbl(l_xls_cnt).program_update_date := l_program_update_date;
1746
1747 FOR l_lang_rec IN get_languages LOOP
1748
1749 l_xlstl_cnt := l_xlstl_cnt + 1;
1750
1751 xlstl_tbl(l_xlstl_cnt).ID := l_xls_id;
1752 xlstl_tbl(l_xlstl_cnt).LANGUAGE := l_lang_rec.language_code;
1753 xlstl_tbl(l_xlstl_cnt).SOURCE_LANG := USERENV('LANG');
1754 xlstl_tbl(l_xlstl_cnt).SFWT_FLAG := 'N';
1755 --xlstl_tbl(l_xlstl_cnt).DESCRIPTION := p_bill_tbl(k).sty_name;
1756 xlstl_tbl(l_xlstl_cnt).DESCRIPTION := p_ie_tbl1(k).til_description; --TIL
1757
1758 -- Updated after Consolidation
1759 xlstl_tbl(l_xlstl_cnt).XTRX_CONTRACT := NULL;
1760 xlstl_tbl(l_xlstl_cnt).XTRX_ASSET := NULL;
1761 xlstl_tbl(l_xlstl_cnt).XTRX_STREAM_GROUP := NULL;
1762 xlstl_tbl(l_xlstl_cnt).XTRX_STREAM_TYPE := NULL;
1763
1764 xlstl_tbl(l_xlstl_cnt).CREATION_DATE := SYSDATE;
1765 xlstl_tbl(l_xlstl_cnt).CREATED_BY := Fnd_Global.USER_ID;
1766 xlstl_tbl(l_xlstl_cnt).LAST_UPDATE_DATE := SYSDATE;
1767 xlstl_tbl(l_xlstl_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
1768 xlstl_tbl(l_xlstl_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
1769
1770 END LOOP;
1771
1772 --end copy from g
1773
1774 --TIL
1775 /*
1776 l_xlsv_rec.sel_id := ln_dtls_rec.sel_id;
1777 --TIL
1778
1779 -- Updated after Consolidation
1780 l_xlsv_rec.xtrx_cons_line_number := NULL;
1781 l_xlsv_rec.xtrx_contract := NULL;
1782 l_xlsv_rec.xtrx_asset := NULL;
1783 l_xlsv_rec.xtrx_stream_group := NULL;
1784 l_xlsv_rec.xtrx_stream_type := NULL;
1785 l_xlsv_rec.xtrx_cons_stream_id := NULL;
1786
1787 -- Start of wraper code generated automatically by Debug code generator for Okl_Xtl_Sell_Invs_Pub.INSERT_XTL_SELL_INVS
1788
1789 IF(is_debug_procedure_on) THEN
1790 BEGIN
1791 okl_debug_pub.log_debug(l_level_procedure, l_module, 'Begin Debug OKLRIEXB.pls call Okl_Xtl_Sell_Invs_Pub.INSERT_XTL_SELL_INVS ');
1792 END;
1793 END IF;
1794
1795 print_xls_rec(l_xlsv_rec);
1796 okl_xtl_sell_invs_pub.insert_xtl_sell_invs(p_api_version, p_init_msg_list, x_return_status, x_msg_count, x_msg_data, l_xlsv_rec, x_xlsv_rec);
1797
1798 IF(is_debug_procedure_on) THEN
1799 BEGIN
1800 okl_debug_pub.log_debug(l_level_procedure, l_module, 'End Debug OKLRIEXB.pls call Okl_Xtl_Sell_Invs_Pub.INSERT_XTL_SELL_INVS ');
1801 END;
1802 END IF;
1803
1804 -- End of wraper code generated automatically by Debug code generator for Okl_Xtl_Sell_Invs_Pub.INSERT_XTL_SELL_INVS
1805
1806 IF(x_return_status = 'S') THEN
1807 print_to_log('====>External Line Created.');
1808 END IF;
1809 */
1810 -- Create External Distribution Lines from AR
1811 --print_to_log('TBL p_ie_tbl1(k).tld_id: ' || p_ie_tbl1(k).tld_id);
1812 FOR acc_dtls_rec IN acc_dstrs_csr(p_ie_tbl1(k).tld_id, 'OKL_TXD_AR_LN_DTLS_B')
1813 LOOP
1814 l_esd_cnt := l_esd_cnt + 1;
1815
1816 esd_tbl(l_esd_cnt).xls_id := l_xls_id;
1817 esd_tbl(l_esd_cnt).code_combination_id := acc_dtls_rec.code_combination_id;
1818 esd_tbl(l_esd_cnt).amount := acc_dtls_rec.amount;
1819 esd_tbl(l_esd_cnt).percent := acc_dtls_rec.percentage;
1820 --esd_tbl(l_esd_cnt).sfwt_flag := 'Y';
1821 esd_tbl(l_esd_cnt).ild_id := 99;
1822
1823 -- Start : Bug#5964007 : PRASJAIN
1824 -- Re-intialize every time the loop come over
1825 l_on_acc_cm := FALSE;
1826 -- Start : Bug#5964007 : PRASJAIN
1827
1828 IF p_ie_tbl1(k).amount > 0 THEN
1829
1830 IF(acc_dtls_rec.cr_dr_flag = 'C') THEN
1831 esd_tbl(l_esd_cnt).account_class := 'REV';
1832 ELSE
1833 esd_tbl(l_esd_cnt).account_class := 'REC';
1834 END IF;
1835 ELSE
1836 IF(acc_dtls_rec.cr_dr_flag = 'C') THEN
1837 esd_tbl(l_esd_cnt).account_class := 'REC';
1838 ELSE
1839 esd_tbl(l_esd_cnt).account_class := 'REV';
1840 END IF;
1841
1842 -- Start : Bug#5964007 : PRASJAIN
1843 -- Adding logic to determine if credit memo is On-account
1844 OPEN c_3level_cm(p_ie_tbl1(k).tld_id);
1845 FETCH c_3level_cm INTO l_chk ;
1846 IF c_3level_cm%FOUND THEN
1847 l_on_acc_cm := TRUE;
1848 END IF;
1849 CLOSE c_3level_cm;
1850 -- End : Bug#5964007 : PRASJAIN
1851 END IF;
1852
1853 -- Start of wraper code generated automatically by Debug code generator for Okl_Xtd_Sell_Invs_Pub.insert_xtd_sell_invs
1854 IF(is_debug_procedure_on) THEN
1855 BEGIN
1856 Okl_Debug_Pub.log_debug(l_level_procedure, l_module, 'Begin Debug OKLRIEXB.pls call Okl_Xtd_Sell_Invs_Pub.insert_xtd_sell_invs ');
1857 END;
1858 END IF;
1859
1860 --Start code changes for rev rec by fmiao on 10/05/2004
1861 IF(acc_dtls_rec.comments = 'CASH_RECEIPT'
1862 AND esd_tbl(l_esd_cnt).account_class <> 'REC' AND NOT l_on_acc_cm) -- added AND NOT l_on_acc_cm by prasjian for bug 5964007
1863 OR(acc_dtls_rec.comments <> 'CASH_RECEIPT') THEN
1864
1865 IF(acc_dtls_rec.comments = 'CASH_RECEIPT') THEN
1866 esd_tbl(l_esd_cnt).account_class := 'UNEARN';
1867 END IF;
1868 /*
1869 print_esd_rec(l_esdv_rec);
1870 okl_xtd_sell_invs_pub.insert_xtd_sell_invs(p_api_version, p_init_msg_list, x_return_status, x_msg_count, x_msg_data, l_esdv_rec, x_esdv_rec);
1871 END IF;
1872
1873 --End code changes for rev rec by fmiao on 10/05/2004
1874
1875 IF(is_debug_procedure_on) THEN
1876 BEGIN
1877 okl_debug_pub.log_debug(l_level_procedure, l_module, 'End Debug OKLRIEXB.pls call Okl_Xtd_Sell_Invs_Pub.insert_xtd_sell_invs ');
1878 END;
1879 END IF;
1880
1881 -- End of wraper code generated automatically by Debug code generator for Okl_Xtd_Sell_Invs_Pub.insert_xtd_sell_invs
1882
1883 IF(x_return_status = 'S') THEN
1884 print_to_log('====>External Distributions Created FOR ' || l_esdv_rec.account_class);
1885 END IF;
1886
1887 END LOOP;
1888
1889 -- Done Creating External distribution lines from AR
1890
1891 IF(int_hdr_status(tab_cntr).tai_id <> ln_dtls_rec.tai_id) THEN
1892 tab_cntr := tab_cntr + 1;
1893 int_hdr_status(tab_cntr).tai_id := ln_dtls_rec.tai_id;
1894 int_hdr_status(tab_cntr).return_status := x_return_status;
1895 ELSE
1896
1897 IF(x_return_status <> 'S') THEN
1898 int_hdr_status(tab_cntr).return_status := x_return_status;
1899 END IF;
1900
1901 END IF;
1902
1903 -- Performance Improvement
1904
1905 IF l_commit_cnt > l_max_commit THEN
1906 l_commit_cnt := 0;
1907 --
1908
1909 IF fnd_api.to_boolean(p_commit) THEN
1910 -- Commit and restart
1911 COMMIT;
1912 END IF;
1913
1914 --
1915 END IF;
1916
1917 END LOOP;
1918
1919 print_to_log(' NUMBER OF 3 LEVEL RECORD processed = ' || i);
1920
1921 --d*bms_output.put_line(' NUMBER OF 3 LEVEL RECORD processed = '||i);
1922 --fnd_file.PUT_LINE('OUT',' NUMBER OF 3 LEVEL RECORD processed = '||TO_CHAR(i));
1923
1924 l_commit_cnt := 0;
1925
1926 FOR i IN 1 .. tab_cntr
1927 LOOP
1928 l_commit_cnt := l_commit_cnt + 1;
1929
1930 n_taiv_rec.id := int_hdr_status(i).tai_id;
1931
1932 IF(int_hdr_status(i).return_status = 'S') THEN
1933 n_taiv_rec.trx_status_code := 'PROCESSED';
1934 ELSE
1935 n_taiv_rec.trx_status_code := 'ERROR';
1936
1937 FOR del3level IN del_xsi_3csr(n_taiv_rec.id)
1938 LOOP
1939
1940 FOR delrec IN del_xtd_csr(del3level.xls_id)
1941 LOOP
1942 d_esdv_rec.id := delrec.esd_id;
1943
1944 DELETE FROM okl_xtd_sell_invs_b
1945 WHERE id = d_esdv_rec.id;
1946
1947 DELETE FROM okl_xtd_sell_invs_tl
1948 WHERE id = d_esdv_rec.id;
1949
1950 END LOOP;
1951
1952 d_xlsv_rec.id := del3level.xls_id;
1953
1954 DELETE FROM okl_xtl_sell_invs_b
1955 WHERE id = d_xlsv_rec.id;
1956
1957 DELETE FROM okl_xtl_sell_invs_tl
1958 WHERE id = d_xlsv_rec.id;
1959
1960 d_xsiv_rec.id := del3level.xsi_id;
1961
1962 DELETE FROM okl_ext_sell_invs_b
1963 WHERE id = d_xsiv_rec.id;
1964
1965 DELETE FROM okl_ext_sell_invs_tl
1966 WHERE id = d_xsiv_rec.id;
1967
1968 END LOOP;
1969 END IF;
1970
1971 UPDATE okl_trx_ar_invoices_b
1972 SET trx_status_code = n_taiv_rec.trx_status_code
1973 WHERE id = n_taiv_rec.id;
1974
1975 -- Performance Improvement
1976
1977 IF l_commit_cnt > l_max_commit THEN
1978 l_commit_cnt := 0;
1979
1980 IF fnd_api.to_boolean(p_commit) THEN
1981 COMMIT;
1982 END IF;
1983
1984 END IF;
1985
1986 END LOOP;
1987
1988 i := 0;
1989
1990 tab_cntr := 0;
1991 int_hdr_status(tab_cntr).tai_id := 0;
1992
1993 l_commit_cnt := 0;
1994
1995 FOR ln_no_dtls_rec IN int_lns_csr2
1996 LOOP
1997
1998 l_commit_cnt := l_commit_cnt + 1;
1999
2000 -- Initialize Records
2001 */
2002 --copy from g
2003 l_esd_id := Okc_P_Util.raw_to_number(sys_guid());
2004 esd_tbl(l_esd_cnt).ID := l_esd_id;
2005 esd_tbl(l_esd_cnt).OBJECT_VERSION_NUMBER := 1;
2006
2007 esd_tbl(l_esd_cnt).ORG_ID := NULL;
2008 esd_tbl(l_esd_cnt).CREATION_DATE := SYSDATE;
2009 esd_tbl(l_esd_cnt).CREATED_BY := Fnd_Global.USER_ID;
2010 esd_tbl(l_esd_cnt).LAST_UPDATE_DATE := SYSDATE;
2011 esd_tbl(l_esd_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
2012 esd_tbl(l_esd_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
2013
2014 esd_tbl(l_esd_cnt).request_id := l_request_id;
2015 esd_tbl(l_esd_cnt).program_application_id := l_program_application_id;
2016 esd_tbl(l_esd_cnt).program_id := l_program_id;
2017 esd_tbl(l_esd_cnt).program_update_date := l_program_update_date;
2018
2019
2020 FOR l_lang_rec IN get_languages LOOP
2021
2022 l_esdtl_cnt := l_esdtl_cnt + 1;
2023
2024 esdtl_tbl(l_esdtl_cnt).ID := l_esd_id;
2025 esdtl_tbl(l_esdtl_cnt).LANGUAGE := l_lang_rec.language_code;
2026 esdtl_tbl(l_esdtl_cnt).SOURCE_LANG := USERENV('LANG');
2027 esdtl_tbl(l_esdtl_cnt).SFWT_FLAG := 'N';
2028
2029 esdtl_tbl(l_esdtl_cnt).CREATION_DATE := SYSDATE;
2030 esdtl_tbl(l_esdtl_cnt).CREATED_BY := Fnd_Global.USER_ID;
2031 esdtl_tbl(l_esdtl_cnt).LAST_UPDATE_DATE := SYSDATE;
2032 esdtl_tbl(l_esdtl_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
2033 esdtl_tbl(l_esdtl_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
2034
2035 END LOOP;
2036 END IF;
2037 END LOOP; --loop through acc_dtls_rec
2038
2039 END LOOP; -- loop through p_ie_tbl1
2040 PRINT_TO_LOG('TBL NUMBER OF 3 LEVEL RECORD processed = '||i);
2041 END IF; -- p_ie_tbl1 >0
2042
2043 IF (p_ie_tbl2.COUNT > 0) THEN
2044 PRINT_TO_LOG('TBL2 p_ie_tbl2.COUNT= '||p_ie_tbl2.COUNT);
2045 -- construct level 2 tbl structure
2046 i := 0;
2047
2048 --tab_cntr := 0;
2049 --int_hdr_status(tab_cntr).tai_id := 0;
2050
2051 -- clear out the tbl for 2 level insert --
2052 l_commit_cnt2 := 0;
2053
2054 l_xsi_cnt := 0;
2055 l_xls_cnt := 0;
2056 l_esd_cnt := 0;
2057 l_xsitl_cnt := 0;
2058 l_xlstl_cnt := 0;
2059 l_esdtl_cnt := 0;
2060 l_tai_id_cnt := 0;
2061
2062 xsi_tbl.DELETE;
2063 xls_tbl.DELETE;
2064 esd_tbl.DELETE;
2065 xsitl_tbl.DELETE;
2066 xlstl_tbl.DELETE;
2067 esdtl_tbl.DELETE;
2068
2069 tai_id_tbl.DELETE;
2070
2071 FOR h IN p_ie_tbl2.FIRST.. p_ie_tbl2.LAST LOOP
2072
2073
2074 l_commit_cnt2 := l_commit_cnt2 + 1;
2075 l_xsi_cnt := l_xsi_cnt + 1;
2076 l_tai_id_cnt := l_tai_id_cnt + 1;
2077
2078 IF l_commit_cnt2 > l_max_commit_cnt THEN
2079
2080 PRINT_TO_LOG('TBL2 Done building XSI ,XLS and XTD records ...');
2081
2082 -- Bulk insert/update records, Commit and restart
2083 bulk_process
2084 (p_api_version
2085 ,p_init_msg_list
2086 ,x_return_status
2087 ,x_msg_count
2088 ,x_msg_data
2089 ,p_commit);
2090
2091 l_commit_cnt2 := 0;
2092 --PRINT_TO_LOG('TBL2 l_commit_cnt2: '||l_commit_cnt2);
2093 END IF;
2094
2095 --populate tai table for update
2096 tai_id_tbl(l_tai_id_cnt) := p_ie_tbl2(h).tai_id;
2097 PRINT_TO_LOG('TBL2 tai_id_tbl(l_tai_id_cnt): '||tai_id_tbl(l_tai_id_cnt));
2098
2099 --added by pgomes 11/20/2002 (multi-currency er)
2100
2101 --end copy from g
2102 --added by pgomes 11/20/2002 (multi-currency er)
2103 l_khr_id := p_ie_tbl2(h).contract_id;
2104 PRINT_TO_LOG('TBL2 l_khr_id: '||l_khr_id);
2105
2106 --Start code added by pgomes on 11/21/2002
2107 l_currency_code := NULL;
2108 l_currency_conversion_type := NULL;
2109 l_currency_conversion_rate := NULL;
2110 l_currency_conversion_date := NULL;
2111
2112 FOR cur IN l_curr_conv_csr(l_khr_id)
2113 LOOP
2114 l_currency_code := cur.currency_code;
2115 l_currency_conversion_type := cur.currency_conversion_type;
2116 l_currency_conversion_rate := cur.currency_conversion_rate;
2117 l_currency_conversion_date := cur.currency_conversion_date;
2118 END LOOP;
2119 PRINT_TO_LOG('TBL2 l_currency_code: '||l_currency_code);
2120
2121 --End code added by pgomes on 11/21/2002
2122 /*
2123 l_xsiv_rec := null_xsiv_rec;
2124 x_xsiv_rec := null_xsiv_rec;
2125
2126 l_xlsv_rec := null_xlsv_rec;
2127 x_xlsv_rec := null_xlsv_rec;
2128
2129 l_esdv_rec := null_esdv_rec;
2130 x_esdv_rec := null_esdv_rec;
2131 */
2132 -- Null out variable
2133 l_contract_number := NULL;
2134
2135 OPEN cntrct_csr(p_ie_tbl2(h).contract_id);
2136 FETCH cntrct_csr
2137 INTO l_contract_number;
2138 CLOSE cntrct_csr;
2139
2140 -- Null out variable
2141 l_stream_name := NULL;
2142
2143 OPEN sty_id_csr(p_ie_tbl2(h).sty_id);
2144 FETCH sty_id_csr
2145 INTO l_stream_name;
2146 CLOSE sty_id_csr;
2147
2148 -- Start; Bug 4525643; stmathew
2149 -- Private Label
2150 l_private_label := NULL;
2151
2152 OPEN pvt_label_csr(p_ie_tbl2(h).contract_id);
2153 FETCH pvt_label_csr
2154 INTO l_private_label;
2155 CLOSE pvt_label_csr;
2156 -- End; Bug 4525643; stmathew
2157
2158 print_to_log('TBL2 Processing: Contract #: ' || l_contract_number || ' ,Stream: ' || l_stream_name || ' ,Amount: ' || p_ie_tbl2(h).amount);
2159
2160 l_jtot_object1_code := NULL;
2161 l_object1_id1 := NULL;
2162 l_object1_id2 := NULL;
2163 l_jtot_object2_code := NULL;
2164
2165 i := i + 1;
2166
2167 xsi_tbl(l_xsi_cnt).trx_status_code := p_ie_tbl2(h).trx_status_code;
2168 xsi_tbl(l_xsi_cnt).isi_id := NULL;
2169
2170 -- Cannot Import into AR
2171 -- l_xsiv_rec.TRX_NUMBER := ln_no_dtls_rec.trx_number;
2172 xsi_tbl(l_xsi_cnt).trx_number := NULL;
2173
2174 xsi_tbl(l_xsi_cnt).trx_date := p_ie_tbl2(h).date_invoiced;
2175
2176 IF p_ie_tbl2(h).contract_id IS NOT NULL THEN
2177 --bug 5160519 : Sales Order Billing
2178 -- Order Management sales for remarketing, these billing details are
2179 --purely from the Order, so if payment method,Bank Account is not passed,
2180 --then pass as NULL.
2181
2182 l_populate_bank_acct := 'Y';
2183 l_populate_pmnt_method := 'Y';
2184 --get primary stream type for remarketing stream
2185 Okl_Streams_Util.get_primary_stream_type(p_ie_tbl2(h).contract_id, 'ASSET_SALE_RECEIVABLE', l_return_status, lx_remrkt_sty_id);
2186
2187 IF l_return_status = Okl_Api.g_ret_sts_success THEN
2188
2189 IF(lx_remrkt_sty_id = p_ie_tbl2(h).sty_id) THEN
2190
2191 IF p_ie_tbl2(h).bank_acct_id IS NULL THEN
2192 xsi_tbl(l_xsi_cnt).customer_bank_account_id := NULL;
2193 --l_remrkt_flag:='Y';
2194 l_populate_bank_acct := 'N';
2195 END IF;
2196
2197 IF p_ie_tbl2(h).irm_id IS NULL THEN
2198 xsi_tbl(l_xsi_cnt).receipt_method_id := NULL;
2199
2200 l_populate_pmnt_method := 'N';
2201 END IF;
2202
2203 END IF;
2204
2205 END IF;
2206
2207 --bug 5160519 : end
2208
2209 --bug 5160519 : Lease Vendor Billing
2210 -- For termination quote to Lease Vendor AND repurchase quote to Lease Vendor
2211 -- on VPA...the payment method should be taken from the Vendor Billing Details,
2212 -- if NULL, then as per above, pass nothing to AR and let AR default to Primary
2213 -- payment method
2214
2215 IF p_ie_tbl2(h).qte_id IS NOT NULL THEN
2216 -- if termination record
2217
2218 IF(p_ie_tbl2(h).ixx_id IS NOT NULL)
2219 AND(p_ie_tbl2(h).ibt_id IS NOT NULL)
2220 AND(p_ie_tbl2(h).irt_id IS NOT NULL) THEN
2221 -- it means the transaction is for the additional recipant
2222 -- if payment method is passed as NULL then we will keep
2223 -- it as null
2224
2225 IF(p_ie_tbl2(h).irm_id IS NULL) THEN
2226 xsi_tbl(l_xsi_cnt).receipt_method_id := NULL;
2227 l_populate_pmnt_method := 'N';
2228 END IF;
2229
2230 IF(p_ie_tbl2(h).bank_acct_id IS NULL) THEN
2231 xsi_tbl(l_xsi_cnt).customer_bank_account_id := NULL;
2232 l_populate_bank_acct := 'N';
2233 END IF;
2234
2235 END IF;
2236
2237 END IF;
2238
2239 --bug 5160519:end
2240
2241 --bug 5160519
2242 --if not remarketing invoice
2243
2244 IF(l_populate_pmnt_method = 'Y') THEN
2245 --bug 5160519:end
2246 -- Changed if condition for bug 4155476
2247
2248 IF(p_ie_tbl2(h).irm_id IS NULL) THEN
2249 -- AND ln_no_dtls_rec.IXX_ID IS NULL) THEN
2250
2251 -- Null out variables
2252 l_jtot_object1_code := NULL;
2253 l_object1_id1 := NULL;
2254 l_object1_id2 := NULL;
2255
2256 OPEN rule_code_csr(p_ie_tbl2(h).contract_id, 'LAPMTH');
2257 FETCH rule_code_csr
2258 INTO l_jtot_object1_code,
2259 l_object1_id1,
2260 l_object1_id2;
2261 CLOSE rule_code_csr;
2262
2263 IF l_object1_id2 <> '#' THEN
2264 xsi_tbl(l_xsi_cnt).receipt_method_id := l_object1_id2;
2265 ELSE
2266 -- This cursor needs to be removed when the view changes to
2267 -- include id2
2268
2269 OPEN rcpt_mthd_csr(l_object1_id1);
2270 FETCH rcpt_mthd_csr
2271 INTO xsi_tbl(l_xsi_cnt).receipt_method_id;
2272 CLOSE rcpt_mthd_csr;
2273 END IF;
2274
2275 ELSE
2276 xsi_tbl(l_xsi_cnt).receipt_method_id := p_ie_tbl2(h).irm_id;
2277 END IF;
2278
2279 --bug 5160519
2280 END IF;
2281
2282 --bug 5160519:end
2283 print_to_log('TBL2....Receipt Method ID: ' || xsi_tbl(l_xsi_cnt).receipt_method_id);
2284
2285 -- Null out variables
2286 l_jtot_object1_code := NULL;
2287 l_jtot_object2_code := NULL;
2288 l_object1_id1 := NULL;
2289 --commented out for rules migration
2290
2291 /*OPEN bto_csr( ln_no_dtls_rec.contract_id, 'BTO');
2292 FETCH bto_csr INTO l_jtot_object1_code,
2293 l_jtot_object2_code,
2294 l_object1_id1;
2295
2296 CLOSE bto_csr;
2297 */
2298
2299 billto_rec.cust_account_id := NULL;
2300 billto_rec.cust_acct_site_id := NULL;
2301 billto_rec.payment_term_id := NULL;
2302
2303 OPEN cur_address_billto(p_ie_tbl2(h).contract_id);
2304 FETCH cur_address_billto
2305 INTO billto_rec;
2306 CLOSE cur_address_billto;
2307
2308 xsi_tbl(l_xsi_cnt).customer_id := NVL(p_ie_tbl2(h).ixx_id, billto_rec.cust_account_id);
2309 print_to_log('TBL2....Customer ID: ' || xsi_tbl(l_xsi_cnt).customer_id);
2310
2311 -- FOR Term ID
2312
2313 OPEN std_terms_csr;
2314 FETCH std_terms_csr
2315 INTO xsi_tbl(l_xsi_cnt).term_id;
2316 CLOSE std_terms_csr;
2317
2318 print_to_log('TBL2....Term ID: ' || xsi_tbl(l_xsi_cnt).term_id);
2319 xsi_tbl(l_xsi_cnt).customer_address_id := NVL(p_ie_tbl2(h).ibt_id, billto_rec.cust_acct_site_id);
2320
2321 print_to_log('TBL2....Customer Address ID: ' || xsi_tbl(l_xsi_cnt).customer_address_id);
2322
2323 print_to_log('TBL2 p_ie_tbl2(h).org_id: ' || p_ie_tbl2(h).org_id);
2324 print_to_log('TBL2 p_ie_tbl2(h).contract_id: ' || p_ie_tbl2(h).contract_id);
2325 IF p_ie_tbl2(h).org_id IS NULL THEN
2326
2327 OPEN org_id_csr(p_ie_tbl2(h).contract_id);
2328 FETCH org_id_csr
2329 INTO xsi_tbl(l_xsi_cnt).org_id;
2330 CLOSE org_id_csr;
2331 ELSE
2332 xsi_tbl(l_xsi_cnt).org_id := p_ie_tbl2(h).org_id;
2333 --TAI
2334 END IF;
2335
2336 print_to_log('TBL2....Org ID: ' || xsi_tbl(l_xsi_cnt).org_id);
2337
2338 -- To resolve the bank account for the customer
2339 -- If receipt method is manual do not supply customer bank account
2340 -- Id. This is required for Auto Invoice Validation
2341 l_rct_method_code := NULL;
2342 --bug 5160519
2343
2344 IF(l_populate_bank_acct = 'Y') THEN
2345 --bug 5160519:end
2346
2347 OPEN rcpt_method_csr(xsi_tbl(l_xsi_cnt).receipt_method_id);
2348 FETCH rcpt_method_csr
2349 INTO l_rct_method_code;
2350 CLOSE rcpt_method_csr;
2351
2352 -- Null out variables
2353 l_jtot_object1_code := NULL;
2354 l_object1_id1 := NULL;
2355 l_object1_id2 := NULL;
2356 l_cust_bank_acct := NULL;
2357
2358 IF(l_rct_method_code <> 'MANUAL') THEN
2359
2360 -- Start Bug 4673593
2361
2362 IF p_ie_tbl2(h).bank_acct_id IS NULL THEN
2363
2364 OPEN rule_code_csr(p_ie_tbl2(h).contract_id, 'LABACC');
2365 FETCH rule_code_csr
2366 INTO l_jtot_object1_code,
2367 l_object1_id1,
2368 l_object1_id2;
2369 CLOSE rule_code_csr;
2370
2371 OPEN bank_acct_csr(l_object1_id1);
2372 FETCH bank_acct_csr
2373 INTO l_cust_bank_acct;
2374 CLOSE bank_acct_csr;
2375 ELSE
2376 l_cust_bank_acct := p_ie_tbl2(h).bank_acct_id;
2377 END IF;
2378
2379 -- End Bug 4673593
2380
2381 xsi_tbl(l_xsi_cnt).customer_bank_account_id := l_cust_bank_acct;
2382 END IF;
2383
2384 --bug 5160519
2385 END IF;
2386
2387 --bug 5160519:end
2388
2389 print_to_log('TBL2....Bank Acct ID: ' || xsi_tbl(l_xsi_cnt).customer_bank_account_id);
2390
2391 ELSE
2392 -- Else for contract_id
2393
2394 IF p_ie_tbl2(h).ixx_id IS NULL THEN
2395 --d*bms_output.put_line ('IXX_ID must be populated WHEN the contract header IS NULL!');
2396 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'IXX_ID must be populated WHEN the contract header IS NULL');
2397 ELSE
2398 xsi_tbl(l_xsi_cnt).customer_id := p_ie_tbl2(h).ixx_id;
2399 END IF;
2400
2401 IF p_ie_tbl2(h).irm_id IS NULL THEN
2402 --d*bms_output.put_line ('IRM_ID must be populated WHEN the contract header IS NULL!');
2403 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'IRM_ID must be populated WHEN the contract header IS NULL');
2404 ELSE
2405 xsi_tbl(l_xsi_cnt).receipt_method_id := p_ie_tbl2(h).irm_id;
2406 END IF;
2407
2408 IF p_ie_tbl2(h).irt_id IS NULL THEN
2409 --d*bms_output.put_line ('IRT_ID must be populated WHEN the contract header IS NULL!');
2410 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'IRT_ID must be populated WHEN the contract header IS NULL');
2411 ELSE
2412 xsi_tbl(l_xsi_cnt).term_id := p_ie_tbl2(h).irt_id;
2413 END IF;
2414
2415 IF p_ie_tbl2(h).ibt_id IS NULL THEN
2416 --d*bms_output.put_line ('IBT_ID must be populated WHEN the contract header IS NULL!');
2417 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'IBT_ID must be populated WHEN the contract header IS NULL');
2418 ELSE
2419 xsi_tbl(l_xsi_cnt).customer_address_id := p_ie_tbl2(h).ibt_id;
2420 END IF;
2421
2422 IF p_ie_tbl2(h).org_id IS NULL THEN
2423 --d*bms_output.put_line ('ORG_ID must be populated WHEN the contract header IS NULL!');
2424 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'ORG_ID must be populated WHEN the contract header IS NULL');
2425 ELSE
2426 --l_xsiv_rec.ORG_ID := ln_no_dtls_rec.ORG_ID; --TAI
2427 xsi_tbl(l_xsi_cnt).org_id := NULL;
2428 END IF;
2429 -- for LE Uptake project 08-11-2006
2430 IF (p_ie_tbl2(h).legal_entity_id IS NULL OR (p_ie_tbl2(h).legal_entity_id = Okl_Api.G_MISS_NUM)) THEN
2431 --d*bms_output.put_line ('LEGAL_ENTITY_ID must be populated WHEN the contract header IS NULL!');
2432 Fnd_File.PUT_LINE(Fnd_File.OUTPUT, 'LEGAL_ENTITY_ID must be populated WHEN the contract header IS NULL');
2433 ELSE
2434 xsi_tbl(l_xsi_cnt).legal_entity_id := p_ie_tbl2(h).legal_entity_id;
2435 END IF;
2436 -- for LE Uptake project 08-11-2006
2437
2438 END IF;
2439
2440 IF p_ie_tbl2(h).set_of_books_id IS NULL THEN
2441 xsi_tbl(l_xsi_cnt).set_of_books_id := Okl_Accounting_Util.get_set_of_books_id;
2442 ELSE
2443 xsi_tbl(l_xsi_cnt).set_of_books_id := p_ie_tbl2(h).set_of_books_id;
2444 --TAI
2445 END IF;
2446
2447 print_to_log('TBL2....xsi_tbl(l_xsi_cnt).set_of_books_id: ' || xsi_tbl(l_xsi_cnt).set_of_books_id);
2448 --Start code added by pgomes on 20-NOV-2002
2449 --Check for currency code
2450
2451 IF p_ie_tbl2(h).currency_code IS NULL THEN
2452 xsi_tbl(l_xsi_cnt).currency_code := l_currency_code;
2453 ELSE
2454 xsi_tbl(l_xsi_cnt).currency_code := p_ie_tbl2(h).currency_code;
2455 END IF;
2456
2457 --Check for currency conversion type
2458
2459 IF p_ie_tbl2(h).currency_conversion_type IS NULL THEN
2460 xsi_tbl(l_xsi_cnt).currency_conversion_type := l_currency_conversion_type;
2461 ELSE
2462 xsi_tbl(l_xsi_cnt).currency_conversion_type := p_ie_tbl2(h).currency_conversion_type;
2463 END IF;
2464
2465 --Check for currency conversion rate
2466
2467 IF(xsi_tbl(l_xsi_cnt).currency_conversion_type = 'User') THEN
2468
2469 IF(xsi_tbl(l_xsi_cnt).currency_code = Okl_Accounting_Util.get_func_curr_code) THEN
2470 xsi_tbl(l_xsi_cnt).currency_conversion_rate := 1;
2471 ELSE
2472
2473 IF p_ie_tbl2(h).currency_conversion_rate IS NULL THEN
2474 xsi_tbl(l_xsi_cnt).currency_conversion_rate := l_currency_conversion_rate;
2475 ELSE
2476 xsi_tbl(l_xsi_cnt).currency_conversion_rate := p_ie_tbl2(h).currency_conversion_rate;
2477 END IF;
2478
2479 END IF;
2480
2481 ELSE
2482 xsi_tbl(l_xsi_cnt).currency_conversion_rate := NULL;
2483 END IF;
2484
2485 --Check for currency conversion date
2486
2487 IF p_ie_tbl2(h).currency_conversion_date IS NULL THEN
2488 xsi_tbl(l_xsi_cnt).currency_conversion_date := l_currency_conversion_date;
2489 ELSE
2490 xsi_tbl(l_xsi_cnt).currency_conversion_date := p_ie_tbl2(h).currency_conversion_date;
2491 END IF;
2492
2493 --End code added by pgomes on 20-NOV-2002
2494
2495 --Start code added by pgomes on 06-JAN-2003
2496
2497 IF(xsi_tbl(l_xsi_cnt).currency_conversion_type IS NULL) THEN
2498 xsi_tbl(l_xsi_cnt).currency_conversion_type := 'User';
2499 xsi_tbl(l_xsi_cnt).currency_conversion_rate := 1;
2500 xsi_tbl(l_xsi_cnt).currency_conversion_date := SYSDATE;
2501 END IF;
2502
2503 --End code added by pgomes on 06-JAN-2003
2504
2505 --For Credit Memo Processing
2506
2507 IF p_ie_tbl2(h).til_id_reverses IS NOT NULL THEN
2508
2509 -- Null out variables
2510 l_recv_inv_id := NULL;
2511
2512 OPEN reverse_csr2(p_ie_tbl2(h).til_id_reverses);
2513 FETCH reverse_csr2
2514 INTO l_recv_inv_id;
2515 CLOSE reverse_csr2;
2516 xsi_tbl(l_xsi_cnt).reference_line_id := l_recv_inv_id;
2517 ELSE
2518 xsi_tbl(l_xsi_cnt).reference_line_id := NULL;
2519 END IF;
2520
2521 xsi_tbl(l_xsi_cnt).receivables_invoice_id := NULL;
2522 -- Populated later by fetch
2523
2524 -- Populate Customer TRX-TYPE ID From AR setup
2525
2526 IF p_ie_tbl2(h).amount < 0 THEN
2527 xsi_tbl(l_xsi_cnt).term_id := NULL;
2528
2529 --OPEN cm_trx_type_csr(xsi_tbl(l_xsi_cnt).set_of_books_id, xsi_tbl(l_xsi_cnt).org_id);
2530 OPEN cm_trx_type_csr(xsi_tbl(l_xsi_cnt).set_of_books_id, p_ie_tbl2(h).org_id );
2531 FETCH cm_trx_type_csr
2532 INTO xsi_tbl(l_xsi_cnt).cust_trx_type_id;
2533 CLOSE cm_trx_type_csr;
2534 ELSE
2535
2536 --OPEN cust_trx_type_csr(xsi_tbl(l_xsi_cnt).set_of_books_id, xsi_tbl(l_xsi_cnt).org_id);
2537 OPEN cust_trx_type_csr(xsi_tbl(l_xsi_cnt).set_of_books_id, p_ie_tbl2(h).org_id);
2538 FETCH cust_trx_type_csr
2539 INTO xsi_tbl(l_xsi_cnt).cust_trx_type_id;
2540 CLOSE cust_trx_type_csr;
2541 END IF;
2542
2543 --xsi_tbl(l_xsi_cnt).invoice_message := NULL;
2544 --xsi_tbl(l_xsi_cnt).description := p_ie_tbl2(h).tai_description;
2545
2546 /*
2547 -- Null Rule records
2548 l_rulv_rec := null_rulv_rec;
2549 --Tax exempt Y_N from the rules
2550 Okl_Bp_Rules.EXTRACT_RULES(
2551 l_api_version,
2552 l_init_msg_list,
2553 ln_no_dtls_rec.contract_id,
2554 NULL,
2555 'LAASTX',
2556 'LAASTX',
2557 l_return_status,
2558 l_msg_count,
2559 l_msg_data,
2560 l_rulv_rec);
2561
2562 l_xsiv_rec.TAX_EXEMPT_FLAG := l_rulv_rec.rule_information1;
2563
2564 --l_xsiv_rec.TAX_EXEMPT_FLAG := NULL;
2565 l_xsiv_rec.TAX_EXEMPT_REASON_CODE := NULL;
2566 */
2567
2568 /* 5162232 Start
2569 -- Start Tax Code addition
2570 -- Null Out tax details
2571 l_asst_tax := NULL;
2572 l_asst_line_tax := NULL;
2573
2574 -- Compute Tax Info
2575 OPEN astx_csr( ln_no_dtls_rec.contract_id );
2576 FETCH astx_csr INTO l_asst_tax;
2577 CLOSE astx_csr;
2578
2579 -- Compute Tax Info at asset line
2580 OPEN astx_line_csr( ln_no_dtls_rec.contract_id, ln_no_dtls_rec.kle_id );
2581 FETCH astx_line_csr INTO l_asst_line_tax;
2582 CLOSE astx_line_csr;
2583
2584 -- Set Tax exempt flag to Standard
2585 l_xsiv_rec.tax_exempt_flag := 'S';
2586 l_xsiv_rec.tax_exempt_reason_code := NULL;
2587
2588 IF l_asst_tax IS NOT NULL THEN
2589 -- Check header code to test and set
2590 IF l_asst_tax IN ( 'E','N' ) THEN
2591 l_xsiv_rec.tax_exempt_flag := 'E';
2592 l_xsiv_rec.tax_exempt_reason_code := 'MANUFACTURER';
2593 ELSE
2594 l_xsiv_rec.tax_exempt_flag := 'S';
2595 l_xsiv_rec.tax_exempt_reason_code := NULL;
2596 END IF;
2597
2598 -- Line level rule instance
2599 IF l_asst_line_tax IS NOT NULL THEN
2600 -- Check line code to test and set
2601 IF l_asst_line_tax IN ( 'E','N' ) THEN
2602 l_xsiv_rec.tax_exempt_flag := 'E';
2603 l_xsiv_rec.tax_exempt_reason_code := 'MANUFACTURER';
2604 ELSE
2605 l_xsiv_rec.tax_exempt_flag := 'S';
2606 l_xsiv_rec.tax_exempt_reason_code := NULL;
2607 END IF;
2608
2609 -- if stream is not taxable, override
2610 IF ln_no_dtls_rec.taxable_default_yn = 'N' THEN
2611 l_xsiv_rec.tax_exempt_flag := 'E';
2612 l_xsiv_rec.tax_exempt_reason_code := 'MANUFACTURER';
2613 END IF;
2614 ELSE
2615 -- if stream is not taxable, override
2616 IF ln_no_dtls_rec.taxable_default_yn = 'N' THEN
2617 l_xsiv_rec.tax_exempt_flag := 'E';
2618 l_xsiv_rec.tax_exempt_reason_code := 'MANUFACTURER';
2619 END IF;
2620 END IF;
2621 ELSE
2622 -- Line level rule instance
2623 IF l_asst_line_tax IS NOT NULL THEN
2624 -- Check line code to test and set
2625 IF l_asst_line_tax IN ( 'E','N' ) THEN
2626 l_xsiv_rec.tax_exempt_flag := 'E';
2627 l_xsiv_rec.tax_exempt_reason_code := 'MANUFACTURER';
2628 ELSE
2629 l_xsiv_rec.tax_exempt_flag := 'S';
2630 l_xsiv_rec.tax_exempt_reason_code := NULL;
2631 END IF;
2632
2633 -- if stream is not taxable, override
2634 IF ln_no_dtls_rec.taxable_default_yn = 'N' THEN
2635 l_xsiv_rec.tax_exempt_flag := 'E';
2636 l_xsiv_rec.tax_exempt_reason_code := 'MANUFACTURER';
2637 END IF;
2638 ELSE
2639 -- if stream is not taxable, override
2640 IF ln_no_dtls_rec.taxable_default_yn = 'N' THEN
2641 l_xsiv_rec.tax_exempt_flag := 'E';
2642 l_xsiv_rec.tax_exempt_reason_code := 'MANUFACTURER';
2643 END IF;
2644 END IF;
2645 END IF;
2646 -- End Tax Code addition
2647 5162232 End*/ -- Set Tax exempt flag to Standard
2648 xsi_tbl(l_xsi_cnt).tax_exempt_flag := 'S';
2649 xsi_tbl(l_xsi_cnt).tax_exempt_reason_code := NULL;
2650 -- 5162232 End
2651 -- Updated after consolidation
2652 --xsi_tbl(l_xsi_cnt).xtrx_cons_invoice_number := NULL;
2653 --xsi_tbl(l_xsi_cnt).xtrx_format_type := NULL;
2654 xsi_tbl(l_xsi_cnt).xtrx_invoice_pull_yn := NULL;
2655
2656 -- Start; Bug 4525643; stmathew
2657 --xsi_tbl(l_xsi_cnt).xtrx_private_label := l_private_label;
2658 -- End; Bug 4525643; stmathew
2659
2660 -- New fields added on 21-MAR-2005
2661 l_inf_id := NULL;
2662 -- rseela BUG# 4733028 Start: populating review invoice flag
2663
2664 print_to_log('TBL2 p_ie_tbl2(h).contract_id: ' || p_ie_tbl2(h).contract_id);
2665 OPEN inv_frmt_csr(p_ie_tbl2(h).contract_id);
2666 FETCH inv_frmt_csr
2667 INTO xsi_tbl(l_xsi_cnt).inf_id,
2668 xsi_tbl(l_xsi_cnt).xtrx_invoice_pull_yn;
2669 CLOSE inv_frmt_csr;
2670
2671 PRINT_TO_LOG('TBL2 XTRX_INVOICE_PULL_YN = '|| xsi_tbl(l_xsi_cnt).XTRX_INVOICE_PULL_YN);
2672
2673 l_xsi_id := Okc_P_Util.raw_to_number(sys_guid());
2674 xsi_tbl(l_xsi_cnt).ID := l_xsi_id;
2675 xsi_tbl(l_xsi_cnt).OBJECT_VERSION_NUMBER := 1;
2676 xsi_tbl(l_xsi_cnt).CREATION_DATE := SYSDATE;
2677 xsi_tbl(l_xsi_cnt).CREATED_BY := Fnd_Global.USER_ID;
2678 xsi_tbl(l_xsi_cnt).LAST_UPDATE_DATE := SYSDATE;
2679 xsi_tbl(l_xsi_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
2680 xsi_tbl(l_xsi_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
2681
2682 xsi_tbl(l_xsi_cnt).request_id := l_request_id;
2683 xsi_tbl(l_xsi_cnt).program_application_id := l_program_application_id;
2684 xsi_tbl(l_xsi_cnt).program_id := l_program_id;
2685 xsi_tbl(l_xsi_cnt).program_update_date := l_program_update_date;
2686
2687
2688 FOR l_lang_rec IN get_languages LOOP
2689
2690 l_xsitl_cnt := l_xsitl_cnt + 1;
2691
2692 xsitl_tbl(l_xsitl_cnt).ID := l_xsi_id;
2693 -- Start; Bug 4525643; stmathew
2694 xsitl_tbl(l_xsitl_cnt).xtrx_private_label := l_private_label;
2695 -- End; Bug 4525643; stmathew
2696
2697 --Original code for the view tbl structure
2698 xsitl_tbl(l_xsitl_cnt).INVOICE_MESSAGE := NULL;
2699 xsitl_tbl(l_xsitl_cnt).DESCRIPTION := p_ie_tbl2(h).tai_description; --TAI
2700 -- Updated after consolidation
2701 xsitl_tbl(l_xsitl_cnt).XTRX_CONS_INVOICE_NUMBER := NULL;
2702 xsitl_tbl(l_xsitl_cnt).XTRX_FORMAT_TYPE := NULL;
2703 --xsitl_tbl(l_xsitl_cnt).XTRX_PRIVATE_LABEL := l_private_label;
2704
2705 xsitl_tbl(l_xsitl_cnt).LANGUAGE := l_lang_rec.language_code;
2706 xsitl_tbl(l_xsitl_cnt).SOURCE_LANG := USERENV('LANG');
2707 xsitl_tbl(l_xsitl_cnt).SFWT_FLAG := 'N';
2708 --xsitl_tbl(l_xsitl_cnt).DESCRIPTION := l_def_desc;
2709
2710 xsitl_tbl(l_xsitl_cnt).CREATION_DATE := SYSDATE;
2711 xsitl_tbl(l_xsitl_cnt).CREATED_BY := Fnd_Global.USER_ID;
2712 xsitl_tbl(l_xsitl_cnt).LAST_UPDATE_DATE := SYSDATE;
2713 xsitl_tbl(l_xsitl_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
2714 xsitl_tbl(l_xsitl_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
2715
2716 END LOOP;
2717 --PRINT_TO_LOG('TBL2 after populateing xsi tbl ');
2718
2719 -- Start of wraper code generated automatically by Debug code generator for Okl_Ext_Sell_Invs_Pub.INSERT_EXT_SELL_INVS
2720 /*
2721 IF(is_debug_procedure_on) THEN
2722 BEGIN
2723 okl_debug_pub.log_debug(l_level_procedure, l_module, 'Begin Debug OKLRIEXB.pls call Okl_Ext_Sell_Invs_Pub.INSERT_EXT_SELL_INVS ');
2724 END;
2725 END IF;
2726
2727 print_xsi_rec(l_xsiv_rec);
2728 okl_ext_sell_invs_pub.insert_ext_sell_invs(l_api_version, l_init_msg_list, x_return_status, x_msg_count, x_msg_data, l_xsiv_rec, x_xsiv_rec);
2729
2730 IF(is_debug_procedure_on) THEN
2731 BEGIN
2732 okl_debug_pub.log_debug(l_level_procedure, l_module, 'End Debug OKLRIEXB.pls call Okl_Ext_Sell_Invs_Pub.INSERT_EXT_SELL_INVS ');
2733 END;
2734 END IF;
2735
2736 -- End of wraper code generated automatically by Debug code generator for Okl_Ext_Sell_Invs_Pub.INSERT_EXT_SELL_INVS
2737
2738 IF(x_return_status = 'S') THEN
2739 print_to_log('====>External Header Created.');
2740 END IF;
2741 */
2742 l_xls_cnt := l_xls_cnt + 1;
2743
2744 xls_tbl(l_xls_cnt).tld_id := NULL;
2745 xls_tbl(l_xls_cnt).lsm_id := NULL;
2746 xls_tbl(l_xls_cnt).isl_id := 1;
2747 xls_tbl(l_xls_cnt).til_id := p_ie_tbl2(h).til_id;
2748 --xls_tbl(l_xls_cnt).til_id := ln_no_dtls_rec.til_id;
2749
2750 -- To be updated by fetch program
2751 xls_tbl(l_xls_cnt).ill_id := NULL;
2752 xls_tbl(l_xls_cnt).xsi_id_details := l_xsi_id;
2753 xls_tbl(l_xls_cnt).line_type := p_ie_tbl2(h).inv_receiv_line_code;
2754 --xls_tbl(l_xls_cnt).description := p_ie_tbl2(h).til_description;
2755 -- Start changes on remarketing by fmiao on 10/18/04 --
2756 xls_tbl(l_xls_cnt).inventory_item_id := p_ie_tbl2(h).inventory_item_id;
2757 -- End changes on remarketing by fmiao on 10/18/04 --
2758 -- Bug 4890024 begin
2759
2760 IF(p_ie_tbl2(h).inventory_org_id IS NULL) THEN
2761
2762 OPEN inv_org_id_csr(p_ie_tbl2(h).contract_id);
2763 FETCH inv_org_id_csr
2764 INTO xls_tbl(l_xls_cnt).inventory_org_id;
2765 CLOSE inv_org_id_csr;
2766 ELSE
2767 xls_tbl(l_xls_cnt).inventory_org_id := p_ie_tbl2(h).inventory_org_id;
2768 END IF;
2769 print_to_log('TBL2 xls_tbl(l_xls_cnt).inventory_org_id: ' || xls_tbl(l_xls_cnt).inventory_org_id);
2770
2771 -- Bug 4890024 end
2772
2773 -------- Rounded Amount --------------
2774 l_rounded_amount := NULL;
2775
2776 -- Null out variables
2777 l_min_acct_unit := NULL;
2778 l_precision := NULL;
2779
2780 OPEN l_curr_csr(xsi_tbl(l_xsi_cnt).currency_code);
2781 FETCH l_curr_csr
2782 INTO l_min_acct_unit,
2783 l_precision;
2784 CLOSE l_curr_csr;
2785
2786 print_to_log('TBL2 l_min_acct_unit: ' || l_min_acct_unit);
2787 print_to_log('TBL2 l_precision: ' || l_precision);
2788 print_to_log('TBL2 xsi_tbl(l_xsi_cnt).currency_code: ' || xsi_tbl(l_xsi_cnt).currency_code);
2789 IF(NVL(l_min_acct_unit, 0) <> 0) THEN
2790 -- Round the amount to the nearest Min Accountable Unit
2791 l_rounded_amount := ROUND(p_ie_tbl2(h).amount / l_min_acct_unit) * l_min_acct_unit;
2792 ELSE
2793 -- Round the amount to the nearest precision
2794 l_rounded_amount := ROUND(p_ie_tbl2(h).amount, l_precision);
2795 END IF;
2796
2797 ------ Rounded Amount --------------
2798
2799 xls_tbl(l_xls_cnt).amount := l_rounded_amount;
2800 xls_tbl(l_xls_cnt).quantity := p_ie_tbl2(h).quantity;
2801 print_to_log('TBL2 l_rounded_amount: '||l_rounded_amount);
2802 print_to_log('TBL2 xls_tbl(l_xls_cnt).amount: ' || xls_tbl(l_xls_cnt).amount);
2803
2804 --copy from g PRINT_TO_LOG('TBL2 AMOUNT = '||xls_tbl(l_xls_cnt).AMOUNT);
2805
2806 -- Updated after Consolidation
2807 xls_tbl(l_xls_cnt).XTRX_CONS_LINE_NUMBER := NULL;
2808 --xls_tbl(l_xls_cnt).XTRX_CONTRACT := NULL;
2809 --xls_tbl(l_xls_cnt).XTRX_ASSET := NULL;
2810 --xls_tbl(l_xls_cnt).XTRX_STREAM_GROUP := NULL;
2811 --xls_tbl(l_xls_cnt).XTRX_STREAM_TYPE := NULL;
2812 xls_tbl(l_xls_cnt).XTRX_CONS_STREAM_ID := NULL;
2813
2814 l_xls_id := Okc_P_Util.raw_to_number(sys_guid());
2815 xls_tbl(l_xls_cnt).ID := l_xls_id;
2816 xls_tbl(l_xls_cnt).OBJECT_VERSION_NUMBER := 1;
2817 xls_tbl(l_xls_cnt).CREATION_DATE := SYSDATE;
2818 xls_tbl(l_xls_cnt).CREATED_BY := Fnd_Global.USER_ID;
2819 xls_tbl(l_xls_cnt).LAST_UPDATE_DATE := SYSDATE;
2820 xls_tbl(l_xls_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
2821 xls_tbl(l_xls_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
2822
2823 xls_tbl(l_xls_cnt).request_id := l_request_id;
2824 xls_tbl(l_xls_cnt).program_application_id := l_program_application_id;
2825 xls_tbl(l_xls_cnt).program_id := l_program_id;
2826 xls_tbl(l_xls_cnt).program_update_date := l_program_update_date;
2827
2828
2829 FOR l_lang_rec IN get_languages LOOP
2830
2831 l_xlstl_cnt := l_xlstl_cnt + 1;
2832
2833 xlstl_tbl(l_xlstl_cnt).ID := l_xls_id;
2834 xlstl_tbl(l_xlstl_cnt).LANGUAGE := l_lang_rec.language_code;
2835 xlstl_tbl(l_xlstl_cnt).SOURCE_LANG := USERENV('LANG');
2836 xlstl_tbl(l_xlstl_cnt).SFWT_FLAG := 'N';
2837 --xlstl_tbl(l_xlstl_cnt).DESCRIPTION := p_bill_tbl(k).sty_name;
2838 xlstl_tbl(l_xlstl_cnt).DESCRIPTION := p_ie_tbl2(h).til_description; --TIL
2839
2840 -- Updated after Consolidation
2841 xlstl_tbl(l_xlstl_cnt).XTRX_CONTRACT := NULL;
2842 xlstl_tbl(l_xlstl_cnt).XTRX_ASSET := NULL;
2843 xlstl_tbl(l_xlstl_cnt).XTRX_STREAM_GROUP := NULL;
2844 xlstl_tbl(l_xlstl_cnt).XTRX_STREAM_TYPE := NULL;
2845
2846 xlstl_tbl(l_xlstl_cnt).CREATION_DATE := SYSDATE;
2847 xlstl_tbl(l_xlstl_cnt).CREATED_BY := Fnd_Global.USER_ID;
2848 xlstl_tbl(l_xlstl_cnt).LAST_UPDATE_DATE := SYSDATE;
2849 xlstl_tbl(l_xlstl_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
2850 xlstl_tbl(l_xlstl_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
2851
2852 END LOOP;
2853 --PRINT_TO_LOG('TBL2 after populateing xls tbl ');
2854
2855 /*
2856 -- Updated after Consolidation
2857 l_xlsv_rec.xtrx_cons_line_number := NULL;
2858 l_xlsv_rec.xtrx_contract := NULL;
2859 l_xlsv_rec.xtrx_asset := NULL;
2860 l_xlsv_rec.xtrx_stream_group := NULL;
2861 l_xlsv_rec.xtrx_stream_type := NULL;
2862 l_xlsv_rec.xtrx_cons_stream_id := NULL;
2863
2864 -- Start of wraper code generated automatically by Debug code generator for Okl_Xtl_Sell_Invs_Pub.INSERT_XTL_SELL_INVS
2865
2866 IF(is_debug_procedure_on) THEN
2867 BEGIN
2868 okl_debug_pub.log_debug(l_level_procedure, l_module, 'Begin Debug OKLRIEXB.pls call Okl_Xtl_Sell_Invs_Pub.INSERT_XTL_SELL_INVS ');
2869 END;
2870 END IF;
2871
2872 print_xls_rec(l_xlsv_rec);
2873 okl_xtl_sell_invs_pub.insert_xtl_sell_invs(p_api_version, p_init_msg_list, x_return_status, x_msg_count, x_msg_data, l_xlsv_rec, x_xlsv_rec);
2874
2875 IF(is_debug_procedure_on) THEN
2876 BEGIN
2877 okl_debug_pub.log_debug(l_level_procedure, l_module, 'End Debug OKLRIEXB.pls call Okl_Xtl_Sell_Invs_Pub.INSERT_XTL_SELL_INVS ');
2878 END;
2879 END IF;
2880
2881 -- End of wraper code generated automatically by Debug code generator for Okl_Xtl_Sell_Invs_Pub.INSERT_XTL_SELL_INVS
2882
2883 IF(x_return_status = 'S') THEN
2884 print_to_log('====>External Line Created.');
2885 END IF;
2886 */
2887 -- Create Accounting Distributions in the External Table
2888 FOR acc_no_dtls_rec IN acc_dstrs_csr(p_ie_tbl2(h).til_id, 'OKL_TXL_AR_INV_LNS_B')
2889 LOOP
2890 l_esd_cnt := l_esd_cnt + 1;
2891
2892 esd_tbl(l_esd_cnt).xls_id := l_xls_id;
2893 esd_tbl(l_esd_cnt).code_combination_id := acc_no_dtls_rec.code_combination_id;
2894 esd_tbl(l_esd_cnt).amount := acc_no_dtls_rec.amount;
2895 esd_tbl(l_esd_cnt).percent := acc_no_dtls_rec.percentage;
2896 --esd_tbl(l_esd_cnt).sfwt_flag := 'Y';
2897 esd_tbl(l_esd_cnt).ild_id := 99;
2898
2899 -- Start : prasjain : bug 5964007
2900 -- Re-intialize every time the loop come over
2901 l_on_acc_cm := FALSE;
2902 -- End : prasjain : bug 5964007
2903
2904 print_to_log('TBL2 p_ie_tbl2(h).amount: ' || p_ie_tbl2(h).amount);
2905 IF p_ie_tbl2(h).amount > 0 THEN
2906
2907 IF(acc_no_dtls_rec.cr_dr_flag = 'C') THEN
2908 esd_tbl(l_esd_cnt).account_class := 'REV';
2909 ELSE
2910 esd_tbl(l_esd_cnt).account_class := 'REC';
2911 END IF;
2912
2913 ELSE
2914
2915 IF(acc_no_dtls_rec.cr_dr_flag = 'C') THEN
2916 esd_tbl(l_esd_cnt).account_class := 'REC';
2917 ELSE
2918 esd_tbl(l_esd_cnt).account_class := 'REV';
2919 END IF;
2920
2921 -- Start : bug 5964007 : prasjain
2922 -- Adding logic to determine if credit memo is On-account
2923 OPEN c_2level_cm (p_ie_tbl2(h).til_id);
2924 FETCH c_2level_cm INTO l_chk ;
2925 IF c_2level_cm%FOUND THEN
2926 l_on_acc_cm := TRUE;
2927 END IF;
2928 CLOSE c_2level_cm;
2929 -- End : bug 5964007 : prasjain
2930
2931 END IF;
2932
2933 -- Start of wraper code generated automatically by Debug code generator for Okl_Xtd_Sell_Invs_Pub.insert_xtd_sell_invs
2934
2935 IF(is_debug_procedure_on) THEN
2936 BEGIN
2937 Okl_Debug_Pub.log_debug(l_level_procedure, l_module, 'Begin Debug OKLRIEXB.pls call Okl_Xtd_Sell_Invs_Pub.insert_xtd_sell_invs ');
2938 END;
2939 END IF;
2940
2941 --Start code changes for rev rec by fmiao on 10/05/2004
2942
2943 IF(acc_no_dtls_rec.comments = 'CASH_RECEIPT'
2944 AND esd_tbl(l_esd_cnt).account_class <> 'REC' AND NOT l_on_acc_cm) -- bug 5964007 : prasjain
2945 OR(acc_no_dtls_rec.comments <> 'CASH_RECEIPT') THEN
2946
2947 IF(acc_no_dtls_rec.comments = 'CASH_RECEIPT') THEN
2948 esd_tbl(l_esd_cnt).account_class := 'UNEARN';
2949 END IF;
2950
2951 l_esd_id := Okc_P_Util.raw_to_number(sys_guid());
2952 esd_tbl(l_esd_cnt).ID := l_esd_id;
2953 esd_tbl(l_esd_cnt).OBJECT_VERSION_NUMBER := 1;
2954
2955 esd_tbl(l_esd_cnt).ORG_ID := NULL;
2956 esd_tbl(l_esd_cnt).CREATION_DATE := SYSDATE;
2957 esd_tbl(l_esd_cnt).CREATED_BY := Fnd_Global.USER_ID;
2958 esd_tbl(l_esd_cnt).LAST_UPDATE_DATE := SYSDATE;
2959 esd_tbl(l_esd_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
2960 esd_tbl(l_esd_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
2961
2962 esd_tbl(l_esd_cnt).request_id := l_request_id;
2963 esd_tbl(l_esd_cnt).program_application_id := l_program_application_id;
2964 esd_tbl(l_esd_cnt).program_id := l_program_id;
2965 esd_tbl(l_esd_cnt).program_update_date := l_program_update_date;
2966
2967 FOR l_lang_rec IN get_languages LOOP
2968
2969 l_esdtl_cnt := l_esdtl_cnt + 1;
2970
2971 esdtl_tbl(l_esdtl_cnt).ID := l_esd_id;
2972 esdtl_tbl(l_esdtl_cnt).LANGUAGE := l_lang_rec.language_code;
2973 esdtl_tbl(l_esdtl_cnt).SOURCE_LANG := USERENV('LANG');
2974 esdtl_tbl(l_esdtl_cnt).SFWT_FLAG := 'N';
2975
2976 esdtl_tbl(l_esdtl_cnt).CREATION_DATE := SYSDATE;
2977 esdtl_tbl(l_esdtl_cnt).CREATED_BY := Fnd_Global.USER_ID;
2978 esdtl_tbl(l_esdtl_cnt).LAST_UPDATE_DATE := SYSDATE;
2979 esdtl_tbl(l_esdtl_cnt).LAST_UPDATED_BY := Fnd_Global.USER_ID;
2980 esdtl_tbl(l_esdtl_cnt).LAST_UPDATE_LOGIN := Fnd_Global.LOGIN_ID;
2981
2982 END LOOP;
2983 --PRINT_TO_LOG('TBL2 after populateing l_esd_tbl');
2984 END IF;
2985 END LOOP; -- acct distr tbl
2986
2987 -- End creating Acctng Distributions in the External Table
2988 END LOOP; -- loop throught p_ie_tbl2
2989 END IF; --p_ie_tbl2.count > 0
2990
2991 END IF;--p_end_of_records = 'N''
2992
2993 --Insert when level 3 or 2 is populated
2994 PRINT_TO_LOG('TBL insert for < 500 records ...commented out');
2995
2996 -- Bulk insert/update records, Commit and restart
2997 bulk_process
2998 (p_api_version
2999 ,p_init_msg_list
3000 ,x_return_status
3001 ,x_msg_count
3002 ,x_msg_data
3003 ,p_commit);
3004
3005 --tai_id_tbl.DELETE;
3006 PRINT_TO_LOG('TBL End time process_id_tbl : '||TO_CHAR(SYSDATE, 'HH:MI:SS'));
3007
3008 PRINT_TO_LOG('TBL End process_id_tbl (-)');
3009
3010 Okl_Api.END_ACTIVITY (
3011 x_msg_count => x_msg_count,
3012 x_msg_data => x_msg_data);
3013
3014 /*
3015 print_esd_rec(l_esdv_rec);
3016 okl_xtd_sell_invs_pub.insert_xtd_sell_invs(p_api_version, p_init_msg_list, x_return_status, x_msg_count, x_msg_data, l_esdv_rec, x_esdv_rec);
3017 END IF;
3018
3019 --End code changes for rev rec by fmiao on 10/05/2004
3020
3021 IF(is_debug_procedure_on) THEN
3022 BEGIN
3023 okl_debug_pub.log_debug(l_level_procedure, l_module, 'End Debug OKLRIEXB.pls call Okl_Xtd_Sell_Invs_Pub.insert_xtd_sell_invs ');
3024 END;
3025 END IF;
3026
3027 -- End of wraper code generated automatically by Debug code generator for Okl_Xtd_Sell_Invs_Pub.insert_xtd_sell_invs
3028
3029 IF(x_return_status = 'S') THEN
3030 print_to_log('====>External Distributions Created FOR ' || l_esdv_rec.account_class);
3031 END IF;
3032
3033 END LOOP;
3034
3035 -- End creating Acctng Distributions in the External Table
3036
3037 IF(int_hdr_status(tab_cntr).tai_id <> ln_no_dtls_rec.tai_id) THEN
3038 tab_cntr := tab_cntr + 1;
3039 int_hdr_status(tab_cntr).tai_id := ln_no_dtls_rec.tai_id;
3040 int_hdr_status(tab_cntr).return_status := x_return_status;
3041 ELSE
3042
3043 IF(x_return_status <> 'S') THEN
3044 int_hdr_status(tab_cntr).return_status := x_return_status;
3045 END IF;
3046
3047 END IF;
3048
3049 -- Performance Improvement
3050
3051 IF l_commit_cnt > l_max_commit THEN
3052 l_commit_cnt := 0;
3053
3054 IF fnd_api.to_boolean(p_commit) THEN
3055 -- Commit and restart
3056 COMMIT;
3057 END IF;
3058
3059 END IF;
3060
3061 END LOOP;
3062
3063 print_to_log(' NUMBER OF 2 LEVEL RECORD processed = ' || i);
3064
3065 l_commit_cnt := 0;
3066
3067 FOR i IN 1 .. tab_cntr
3068 LOOP
3069 l_commit_cnt := l_commit_cnt + 1;
3070
3071 n_taiv_rec.id := int_hdr_status(i).tai_id;
3072
3073 IF(int_hdr_status(i).return_status = 'S') THEN
3074 n_taiv_rec.trx_status_code := 'PROCESSED';
3075 ELSE
3076 n_taiv_rec.trx_status_code := 'ERROR';
3077
3078 FOR del2level IN del_xsi_2csr(n_taiv_rec.id)
3079 LOOP
3080
3081 FOR delrec IN del_xtd_csr(del2level.xls_id)
3082 LOOP
3083 d_esdv_rec.id := delrec.esd_id;
3084
3085 DELETE FROM okl_xtd_sell_invs_b
3086 WHERE id = d_esdv_rec.id;
3087
3088 DELETE FROM okl_xtd_sell_invs_tl
3089 WHERE id = d_esdv_rec.id;
3090
3091 END LOOP;
3092
3093 d_xlsv_rec.id := del2level.xls_id;
3094
3095 DELETE FROM okl_xtl_sell_invs_b
3096 WHERE id = d_xlsv_rec.id;
3097
3098 DELETE FROM okl_xtl_sell_invs_tl
3099 WHERE id = d_xlsv_rec.id;
3100
3101 d_xsiv_rec.id := del2level.xsi_id;
3102
3103 DELETE FROM okl_ext_sell_invs_b
3104 WHERE id = d_xsiv_rec.id;
3105
3106 DELETE FROM okl_ext_sell_invs_tl
3107 WHERE id = d_xsiv_rec.id;
3108 END LOOP;
3109 END IF;
3110
3111 UPDATE okl_trx_ar_invoices_b
3112 SET trx_status_code = n_taiv_rec.trx_status_code
3113 WHERE id = n_taiv_rec.id;
3114
3115 -- Performance Improvement
3116
3117 IF l_commit_cnt > l_max_commit THEN
3118 l_commit_cnt := 0;
3119
3120 IF fnd_api.to_boolean(p_commit) THEN
3121 -- Commit and restart
3122 COMMIT;
3123 END IF;
3124
3125 END IF;
3126
3127 END LOOP;
3128
3129 ------------------------------------------------------------
3130 -- Print log and output messages
3131 ------------------------------------------------------------
3132
3133 -- Get the request Id
3134 l_request_id := NULL;
3135
3136 OPEN req_id_csr;
3137 FETCH req_id_csr
3138 INTO l_request_id;
3139 CLOSE req_id_csr;
3140
3141 submitted_sts := 'SUBMITTED';
3142 error_sts := 'ERROR';
3143
3144 l_succ_cnt := 0;
3145 l_err_cnt := 0;
3146
3147 -- Success Count
3148
3149 OPEN xsi_cnt_succ_csr(l_request_id, submitted_sts);
3150 FETCH xsi_cnt_succ_csr
3151 INTO l_succ_cnt;
3152 CLOSE xsi_cnt_succ_csr;
3153
3154 -- Error Count
3155
3156 OPEN xsi_cnt_err_csr(l_request_id, error_sts);
3157 FETCH xsi_cnt_err_csr
3158 INTO l_err_cnt;
3159 CLOSE xsi_cnt_err_csr;
3160
3161 ----------------------------------------
3162 -- Get Operating unit name
3163 ----------------------------------------
3164 l_op_unit_name := NULL;
3165
3166 OPEN op_unit_csr;
3167 FETCH op_unit_csr
3168 INTO l_op_unit_name;
3169 CLOSE op_unit_csr;
3170
3171 -- Start New Out File stmathew 15-OCT-2004
3172 fnd_file.PUT_LINE(fnd_file.OUTPUT, rpad(' ', 54, ' ') || 'Oracle Lease and Finance Management' || lpad(' ', 55, ' '));
3173 fnd_file.PUT_LINE(fnd_file.OUTPUT, rpad(' ', 132, ' '));
3174 fnd_file.PUT_LINE(fnd_file.OUTPUT, rpad(' ', 53, ' ') || 'Prepare Receivables Bills' || lpad(' ', 54, ' '));
3175 fnd_file.PUT_LINE(fnd_file.OUTPUT, rpad(' ', 53, ' ') || '-------------------------' || lpad(' ', 54, ' '));
3176 fnd_file.PUT_LINE(fnd_file.OUTPUT, rpad(' ', 132, ' '));
3177 fnd_file.PUT_LINE(fnd_file.OUTPUT, rpad(' ', 132, ' '));
3178 fnd_file.PUT_LINE(fnd_file.OUTPUT, 'Operating Unit: ' || l_op_unit_name);
3179 fnd_file.PUT_LINE(fnd_file.OUTPUT, 'Request Id: ' || l_request_id || lpad(' ', 74, ' ') || 'Run Date: ' || to_char(sysdate));
3180 fnd_file.PUT_LINE(fnd_file.OUTPUT, 'Currency: ' || okl_accounting_util.get_func_curr_code);
3181 fnd_file.PUT_LINE(fnd_file.OUTPUT, rpad('-', 132, '-'));
3182 fnd_file.PUT_LINE(fnd_file.OUTPUT, rpad(' ', 132, ' '));
3183
3184 fnd_file.PUT_LINE(fnd_file.OUTPUT, 'Processing Details:' || lpad(' ', 113, ' '));
3185 fnd_file.PUT_LINE(fnd_file.OUTPUT, rpad(' ', 132, ' '));
3186 fnd_file.PUT_LINE(fnd_file.OUTPUT, ' Number of Successful Records: ' || l_succ_cnt);
3187 fnd_file.PUT_LINE(fnd_file.OUTPUT, ' Number of Errored Records: ' || l_err_cnt);
3188 fnd_file.PUT_LINE(fnd_file.OUTPUT, ' Total: ' ||(l_succ_cnt + l_err_cnt));
3189 fnd_file.PUT_LINE(fnd_file.OUTPUT, rpad(' ', 132, ' '));
3190
3191 IF x_msg_count > 0 THEN
3192 FOR i IN 1 .. x_msg_count
3193 LOOP
3194
3195 IF i = 1 THEN
3196 fnd_file.PUT_LINE(fnd_file.OUTPUT, 'Details of Errored Records:' || lpad(' ', 97, ' '));
3197 fnd_file.PUT_LINE(fnd_file.OUTPUT, rpad(' ', 132, ' '));
3198 END IF;
3199
3200 fnd_msg_pub.GET(p_msg_index => i, p_encoded => 'F', p_data => lx_msg_data, p_msg_index_out => l_msg_index_out);
3201
3202 fnd_file.PUT_LINE(fnd_file.OUTPUT, to_char(i) || ': ' || lx_msg_data);
3203
3204 IF(fnd_log.level_exception >= fnd_log.g_current_runtime_level) THEN
3205 fnd_log.string(fnd_log.level_exception, 'okl_internal_to_external', to_char(i) || ': ' || lx_msg_data);
3206 END IF;
3207
3208 END LOOP;
3209 END IF;
3210
3211 IF(fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
3212 fnd_log.string(fnd_log.level_procedure, 'okl_internal_to_external', 'End(-)');
3213 END IF;
3214
3215 ------------------------------------------------------------
3216 -- End processing
3217 ------------------------------------------------------------
3218
3219 okl_api.end_activity(x_msg_count => x_msg_count, x_msg_data => x_msg_data);*/
3220
3221 EXCEPTION
3222 WHEN OTHERS THEN
3223
3224 IF(Fnd_Log.level_exception >= Fnd_Log.g_current_runtime_level) THEN
3225 Fnd_Log.string(Fnd_Log.level_exception, 'okl_internal_to_external', 'EXCEPTION :' || 'OTHERS');
3226 END IF;
3227
3228 print_to_log('*=> Error Message(O1): ' || SQLERRM);
3229
3230 END process_ie_tbl;
3231
3232 PROCEDURE internal_to_external
3233 (p_api_version IN NUMBER
3234 ,p_init_msg_list IN VARCHAR2
3235 ,x_return_status OUT NOCOPY VARCHAR2
3236 ,x_msg_count OUT NOCOPY NUMBER
3237 ,x_msg_data OUT NOCOPY VARCHAR2
3238 ,p_commit IN VARCHAR2
3239 ,p_contract_number IN VARCHAR2
3240 ,p_assigned_process IN VARCHAR2
3241 ) IS
3242
3243 l_api_name CONSTANT VARCHAR2(30) := 'INTERNAL_TO_EXTERNAL';
3244 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
3245 l_api_version CONSTANT NUMBER := 1;
3246
3247
3248 -- Cursor picks lines with detail records
3249 CURSOR int_lns_csr1 IS
3250 SELECT t1.id tai_id,
3251 t1.khr_id contract_id,
3252 t1.trx_status_code trx_status_code,
3253 t1.date_invoiced date_invoiced,
3254 t1.ixx_id ixx_id,
3255 t1.irm_id irm_id,
3256 t1.irt_id irt_id,
3257 t1.ibt_id ibt_id,
3258 t1.set_of_books_id set_of_books_id,
3259 t1.description tai_description,
3260 t1.currency_code currency_code,
3261 --Start code added by pgomes on 20-NOV-2002
3262 t1.currency_conversion_type currency_conversion_type,
3263 t1.currency_conversion_rate currency_conversion_rate,
3264 t1.currency_conversion_date currency_conversion_date,
3265 --End code added by pgomes on 20-NOV-2002
3266 t1.org_id org_id,
3267 t1.trx_number trx_number,
3268 t1.legal_entity_id, -- for LE Uptake project 08-11-2006
3269 t2.inv_receiv_line_code inv_receiv_line_code,
3270 NVL(t3.description, t2.description) til_description,
3271 t2.quantity quantity,
3272 t2.kle_id kle_id,
3273 t3.id tld_id,
3274 t3.amount amount,
3275 t3.tld_id_reverses tld_id_reverses,
3276 t3.sty_id sty_id,
3277 t4.taxable_default_yn taxable_default_yn,
3278 t3.sel_id sel_id,
3279 -- Start changes on remarketing by fmiao on 10/18/04 --
3280 t3.inventory_item_id inventory_item_id,
3281 -- End changes on remarketing by fmiao on 10/18/04 --
3282 NVL(t3.inventory_org_id, t2.inventory_org_id) inventory_org_id
3283 FROM okl_trx_ar_invoices_v t1,
3284 okl_txl_ar_inv_lns_v t2,
3285 okl_txd_ar_ln_dtls_v t3,
3286 okl_strm_type_v t4,
3287 okc_k_headers_b CHR
3288 WHERE t1.trx_status_code = 'SUBMITTED'
3289 AND t1.khr_id = CHR.id
3290 AND CHR.contract_number = NVL(p_contract_number, CHR.contract_number)
3291 AND t2.tai_id = t1.id
3292 AND t3.til_id_details = t2.id
3293 AND t4.id = t3.sty_id
3294 ORDER BY tai_id;
3295
3296 -- Pick lines with no detail records
3297 CURSOR int_lns_csr2 IS
3298 SELECT t1.id tai_id,
3299 t1.khr_id contract_id,
3300 t1.trx_status_code trx_status_code,
3301 t1.date_invoiced date_invoiced,
3302 t1.ixx_id ixx_id,
3303 t1.irm_id irm_id,
3304 t1.irt_id irt_id,
3305 t1.ibt_id ibt_id,
3306 t1.set_of_books_id set_of_books_id,
3307 t1.description tai_description,
3308 t1.currency_code currency_code,
3309 --Start code added by pgomes on 20-NOV-2002
3310 t1.currency_conversion_type currency_conversion_type,
3311 t1.currency_conversion_rate currency_conversion_rate,
3312 t1.currency_conversion_date currency_conversion_date,
3313 --End code added by pgomes on 20-NOV-2002
3314 t1.org_id org_id,
3315 t1.trx_number trx_number,
3316 t1.legal_entity_id, -- for LE Uptake project 08-11-2006
3317 t2.id til_id,
3318 t2.kle_id kle_id,
3319 t2.inv_receiv_line_code inv_receiv_line_code,
3320 t2.description til_description,
3321 t2.quantity quantity,
3322 t2.amount amount,
3323 t2.til_id_reverses til_id_reverses,
3324 t2.sty_id sty_id,
3325 t4.taxable_default_yn taxable_default_yn,
3326 -- Start changes on remarketing by fmiao on 10/18/04 --
3327 t2.inventory_item_id inventory_item_id,
3328 -- End changes on remarketing by fmiao on 10/18/04 --
3329 t2.inventory_org_id inventory_org_id,
3330 -- Start Bug 4673593
3331 t2.bank_acct_id bank_acct_id, -- End Bug 4673593 --bug 5160519,
3332 t1.qte_id qte_id --Termination Quote id --bug 5160519:end
3333 FROM okl_trx_ar_invoices_v t1,
3334 okl_txl_ar_inv_lns_v t2,
3335 okl_strm_type_v t4,
3336 okc_k_headers_b CHR
3337 WHERE t1.trx_status_code = 'SUBMITTED'
3338 AND t1.khr_id = CHR.id
3339 AND CHR.contract_number = NVL(p_contract_number, CHR.contract_number)
3340 AND t2.tai_id = t1.id
3341 AND t4.id = t2.sty_id
3342 AND NOT EXISTS
3343 (SELECT *
3344 FROM okl_txd_ar_ln_dtls_b t3
3345 WHERE t3.til_id_details = t2.id)
3346 ORDER BY tai_id;
3347
3348 -- Cursor picks lines with detail records
3349 CURSOR int_lns_csr3 IS
3350 SELECT t1.id tai_id,
3351 t1.khr_id contract_id,
3352 t1.trx_status_code trx_status_code,
3353 t1.date_invoiced date_invoiced,
3354 t1.ixx_id ixx_id,
3355 t1.irm_id irm_id,
3356 t1.irt_id irt_id,
3357 t1.ibt_id ibt_id,
3358 t1.set_of_books_id set_of_books_id,
3359 t1.description tai_description,
3360 t1.currency_code currency_code,
3361 --Start code added by pgomes on 20-NOV-2002
3362 t1.currency_conversion_type currency_conversion_type,
3363 t1.currency_conversion_rate currency_conversion_rate,
3364 t1.currency_conversion_date currency_conversion_date,
3365 --End code added by pgomes on 20-NOV-2002
3366 t1.org_id org_id,
3367 t1.trx_number trx_number,
3368 t1.legal_entity_id, -- for LE Uptake project 08-11-2006
3369 t2.inv_receiv_line_code inv_receiv_line_code,
3370 NVL(t3.description, t2.description) til_description,
3371 t2.quantity quantity,
3372 t2.kle_id kle_id,
3373 t3.id tld_id,
3374 t3.amount amount,
3375 t3.tld_id_reverses tld_id_reverses,
3376 t3.sty_id sty_id,
3377 t4.taxable_default_yn taxable_default_yn,
3378 t3.sel_id sel_id,
3379 -- Start changes on remarketing by fmiao on 10/18/04 --
3380 t3.inventory_item_id inventory_item_id,
3381 -- End changes on remarketing by fmiao on 10/18/04 --
3382 NVL(t3.inventory_org_id, t2.inventory_org_id) inventory_org_id
3383 FROM okl_trx_ar_invoices_v t1,
3384 okl_txl_ar_inv_lns_v t2,
3385 okl_txd_ar_ln_dtls_v t3,
3386 okl_strm_type_v t4,
3387 okc_k_headers_b CHR,
3388 OKL_PARALLEL_PROCESSES pws
3389 WHERE t1.trx_status_code = 'SUBMITTED'
3390 AND t1.khr_id = CHR.id
3391 AND CHR.contract_number = NVL(p_contract_number, CHR.contract_number)
3392 AND t2.tai_id = t1.id
3393 AND t3.til_id_details = t2.id
3394 AND t4.id = t3.sty_id
3395 -- parallel process
3396 AND pws.object_type = 'PREP_CONTRACT'
3397 AND pws.object_value = CHR.contract_number
3398 AND pws.assigned_process = p_assigned_process
3399 ORDER BY tai_id;
3400
3401 -- Pick lines with no detail records
3402 CURSOR int_lns_csr4 IS
3403 SELECT t1.id tai_id,
3404 t1.khr_id contract_id,
3405 t1.trx_status_code trx_status_code,
3406 t1.date_invoiced date_invoiced,
3407 t1.ixx_id ixx_id,
3408 t1.irm_id irm_id,
3409 t1.irt_id irt_id,
3410 t1.ibt_id ibt_id,
3411 t1.set_of_books_id set_of_books_id,
3412 t1.description tai_description,
3413 t1.currency_code currency_code,
3414 --Start code added by pgomes on 20-NOV-2002
3415 t1.currency_conversion_type currency_conversion_type,
3416 t1.currency_conversion_rate currency_conversion_rate,
3417 t1.currency_conversion_date currency_conversion_date,
3418 --End code added by pgomes on 20-NOV-2002
3419 t1.org_id org_id,
3420 t1.trx_number trx_number,
3421 t1.legal_entity_id, -- for LE Uptake project 08-11-2006
3422 t2.id til_id,
3423 t2.kle_id kle_id,
3424 t2.inv_receiv_line_code inv_receiv_line_code,
3425 t2.description til_description,
3426 t2.quantity quantity,
3427 t2.amount amount,
3428 t2.til_id_reverses til_id_reverses,
3429 t2.sty_id sty_id,
3430 t4.taxable_default_yn taxable_default_yn,
3431 -- Start changes on remarketing by fmiao on 10/18/04 --
3432 t2.inventory_item_id inventory_item_id,
3433 -- End changes on remarketing by fmiao on 10/18/04 --
3434 t2.inventory_org_id inventory_org_id,
3435 -- Start Bug 4673593
3436 t2.bank_acct_id bank_acct_id, -- End Bug 4673593 --bug 5160519,
3437 t1.qte_id qte_id --Termination Quote id
3438 --bug 5160519:end
3439 FROM okl_trx_ar_invoices_v t1,
3440 okl_txl_ar_inv_lns_v t2,
3441 okl_strm_type_v t4,
3442 okc_k_headers_b CHR,
3443 OKL_PARALLEL_PROCESSES pws
3444 WHERE t1.trx_status_code = 'SUBMITTED'
3445 AND t1.khr_id = CHR.id
3446 AND CHR.contract_number = NVL(p_contract_number, CHR.contract_number)
3447 AND t2.tai_id = t1.id
3448 AND t4.id = t2.sty_id
3449 AND NOT EXISTS
3450 (SELECT *
3451 FROM okl_txd_ar_ln_dtls_v t3
3452 WHERE t3.til_id_details = t2.id)
3453 -- parallel process
3454 AND pws.object_type = 'PREP_CONTRACT'
3455 AND pws.object_value = CHR.contract_number
3456 AND pws.assigned_process = p_assigned_process
3457 ORDER BY tai_id;
3458
3459
3460 ie_tbl1 ie_tbl_type1;
3461 ie_tbl2 ie_tbl_type2;
3462
3463 l_fetch_size NUMBER := 5000;
3464
3465 -- --------------------------------------------------------
3466 -- To Print log messages
3467 -- --------------------------------------------------------
3468
3469 l_request_id NUMBER;
3470
3471 CURSOR req_id_csr IS
3472 SELECT
3473 DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID)
3474 FROM dual;
3475
3476
3477 CURSOR xsi_cnt_succ_csr( p_req_id NUMBER, p_sts VARCHAR2 ) IS
3478 SELECT COUNT(*)
3479 FROM okl_ext_sell_invs_v
3480 WHERE trx_status_code = p_sts AND
3481 request_id = p_req_id ;
3482
3483 ------------------------------------------------------------
3484 -- Operating Unit
3485 ------------------------------------------------------------
3486 CURSOR op_unit_csr IS
3487 SELECT NAME
3488 FROM hr_operating_units
3489 WHERE ORGANIZATION_ID=MO_GLOBAL.GET_CURRENT_ORG_ID; -- MOAC fix - Bug#5378114 --varangan- 29-9-06
3490
3491
3492 l_succ_cnt NUMBER;
3493 l_op_unit_name hr_operating_units.name%TYPE;
3494 lx_msg_data VARCHAR2(450);
3495 l_msg_index_out NUMBER :=0;
3496 submitted_sts okl_trx_ar_invoices_v.trx_status_code%TYPE;
3497 l_end_of_records VARCHAR2(1);
3498
3499 BEGIN
3500 l_return_status := Okl_Api.START_ACTIVITY(
3501 p_api_name => l_api_name,
3502 p_pkg_name => G_PKG_NAME,
3503 p_init_msg_list => p_init_msg_list,
3504 l_api_version => l_api_version,
3505 p_api_version => p_api_version,
3506 p_api_type => '_PVT',
3507 x_return_status => l_return_status);
3508
3509 --L_DEBUG_ENABLED := Okl_Debug_Pub.CHECK_LOG_ENABLED;
3510
3511 l_end_of_records := 'N';
3512
3513
3514 PRINT_TO_LOG('=========================================================================================');
3515 PRINT_TO_LOG(' ** Start Processing. Please See Error Log for any errored transactions ** ');
3516 PRINT_TO_LOG('=========================================================================================');
3517
3518 PRINT_TO_LOG('IE p_assigned_process: '||p_assigned_process);
3519 PRINT_TO_LOG('IE Start time internal_to_external : '||TO_CHAR(SYSDATE, 'HH:MI:SS'));
3520
3521 IF p_assigned_process IS NOT NULL THEN
3522 -- Cursors 3,4 with parallel process
3523 ie_tbl1.DELETE;
3524 ie_tbl2.DELETE;
3525
3526 OPEN int_lns_csr3;
3527 LOOP
3528 -- ----------------------------
3529 -- Clear table contents
3530 -- ----------------------------
3531
3532 FETCH int_lns_csr3 BULK COLLECT INTO ie_tbl1 LIMIT l_fetch_size;
3533 PRINT_TO_LOG('IE int_lns_csr3 ie_tbl1 count is: '||ie_tbl1.COUNT);
3534
3535 IF ie_tbl1.COUNT > 0 THEN
3536
3537 process_ie_tbl
3538 (p_api_version
3539 ,p_init_msg_list
3540 ,x_return_status
3541 ,x_msg_count
3542 ,x_msg_data
3543 ,p_commit
3544 ,p_contract_number
3545 ,ie_tbl1
3546 ,ie_tbl2
3547 ,l_end_of_records
3548 );
3549
3550 END IF;
3551 EXIT WHEN int_lns_csr3%NOTFOUND;
3552 END LOOP;
3553 CLOSE int_lns_csr3;
3554
3555 ie_tbl1.DELETE;
3556 ie_tbl2.DELETE;
3557
3558 OPEN int_lns_csr4;
3559 LOOP
3560 -- ----------------------------
3561 -- Clear table contents
3562 -- ----------------------------
3563
3564 FETCH int_lns_csr4 BULK COLLECT INTO ie_tbl2 LIMIT l_fetch_size;
3565 PRINT_TO_LOG('IE int_lns_csr4 ie_tbl2 count is: '||ie_tbl2.COUNT);
3566
3567 /*
3568 FOR i IN ie_tbl2.first..ie_tbl2.last LOOP
3569 Fnd_File.PUT_LINE (Fnd_File.LOG, 'IE4 ie_tbl2(i).tai_id: '||ie_tbl2(i).tai_id);
3570 Fnd_File.PUT_LINE (Fnd_File.LOG, 'IE4 ie_tbl2(i).kle_id: '||ie_tbl2(i).kle_id);
3571 Fnd_File.PUT_LINE (Fnd_File.LOG, 'IE4 ie_tbl2(i).contract_id: '||ie_tbl2(i).contract_id);
3572 Fnd_File.PUT_LINE (Fnd_File.LOG, 'IE4 ie_tbl2(i).amount: '||ie_tbl2(i).amount);
3573 END LOOP;
3574 */
3575 IF ie_tbl2.COUNT > 0 THEN
3576 process_ie_tbl
3577 (p_api_version
3578 ,p_init_msg_list
3579 ,x_return_status
3580 ,x_msg_count
3581 ,x_msg_data
3582 ,p_commit
3583 ,p_contract_number
3584 ,ie_tbl1
3585 ,ie_tbl2
3586 ,l_end_of_records
3587 );
3588 END IF;
3589 EXIT WHEN int_lns_csr4%NOTFOUND;
3590 END LOOP;
3591 CLOSE int_lns_csr4;
3592 ELSE -- p_assigned_process is null
3593 -- Cursors 1,2 without parallel processes
3594 ie_tbl1.DELETE;
3595 ie_tbl2.DELETE;
3596
3597 OPEN int_lns_csr1;
3598 LOOP
3599 -- ----------------------------
3600 -- Clear table contents
3601 -- ----------------------------
3602
3603 FETCH int_lns_csr1 BULK COLLECT INTO ie_tbl1 LIMIT l_fetch_size;
3604 PRINT_TO_LOG('IE int_lns_csr1 ie_tbl1 count is: '||ie_tbl1.COUNT);
3605 IF ie_tbl1.COUNT > 0 THEN
3606 process_ie_tbl
3607 (p_api_version
3608 ,p_init_msg_list
3609 ,x_return_status
3610 ,x_msg_count
3611 ,x_msg_data
3612 ,p_commit
3613 ,p_contract_number
3614 ,ie_tbl1
3615 ,ie_tbl2
3616 ,l_end_of_records
3617 );
3618 END IF;
3619 EXIT WHEN int_lns_csr1%NOTFOUND;
3620 END LOOP;
3621 CLOSE int_lns_csr1;
3622
3623 ie_tbl1.DELETE;
3624 ie_tbl2.DELETE;
3625
3626 OPEN int_lns_csr2;
3627 LOOP
3628 -- ----------------------------
3629 -- Clear table contents
3630 -- ----------------------------
3631
3632 FETCH int_lns_csr2 BULK COLLECT INTO ie_tbl2 LIMIT l_fetch_size;
3633 PRINT_TO_LOG('IE int_lns_csr2 ie_tbl2 count is: '||ie_tbl2.COUNT);
3634 IF ie_tbl2.COUNT > 0 THEN
3635 process_ie_tbl
3636 (p_api_version
3637 ,p_init_msg_list
3638 ,x_return_status
3639 ,x_msg_count
3640 ,x_msg_data
3641 ,p_commit
3642 ,p_contract_number
3643 ,ie_tbl1
3644 ,ie_tbl2
3645 ,l_end_of_records
3646 );
3647 END IF;
3648 EXIT WHEN int_lns_csr2%NOTFOUND;
3649 END LOOP;
3650 CLOSE int_lns_csr2;
3651 END IF; -- p_assigned_process is null
3652
3653
3654 ------------------------------------------------
3655 -- Call bulk_process to mark end of process
3656 ------------------------------------------------
3657 l_end_of_records := 'Y';
3658 IF (ie_tbl1.COUNT > 0 OR ie_tbl2.COUNT > 0) THEN
3659 process_ie_tbl
3660 (p_api_version
3661 ,p_init_msg_list
3662 ,x_return_status
3663 ,x_msg_count
3664 ,x_msg_data
3665 ,p_commit
3666 ,p_contract_number
3667 ,ie_tbl1
3668 ,ie_tbl2
3669 ,l_end_of_records
3670 );
3671 END IF;
3672 -----------------------------------------------------------
3673 -- Print log and output messages
3674 ------------------------------------------------------------
3675
3676 -- Get the request Id
3677 l_request_id := NULL;
3678 OPEN req_id_csr;
3679 FETCH req_id_csr INTO l_request_id;
3680 CLOSE req_id_csr;
3681
3682 submitted_sts := 'SUBMITTED';
3683 l_succ_cnt := 0;
3684 -- Success Count
3685 OPEN xsi_cnt_succ_csr( l_request_id, submitted_sts );
3686 FETCH xsi_cnt_succ_csr INTO l_succ_cnt;
3687 CLOSE xsi_cnt_succ_csr;
3688
3689 -- --------------------------------------
3690 -- Get Operating unit name
3691 -- --------------------------------------
3692 l_op_unit_name := NULL;
3693 OPEN op_unit_csr;
3694 FETCH op_unit_csr INTO l_op_unit_name;
3695 CLOSE op_unit_csr;
3696
3697 -- Start New Out File stmathew 15-OCT-2004
3698 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 54, ' ')||'Oracle Lease and Finance Management'||LPAD(' ', 55, ' '));
3699 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
3700 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 53, ' ')||'Prepare Receivables Bills'||LPAD(' ', 54, ' '));
3701 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 53, ' ')||'-------------------------'||LPAD(' ', 54, ' '));
3702 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
3703 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
3704 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Operating Unit: '||l_op_unit_name);
3705 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Request Id: '||l_request_id||LPAD(' ',74,' ') ||'Run Date: '||TO_CHAR(SYSDATE));
3706 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Currency: '||Okl_Accounting_Util.get_func_curr_code);
3707 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD('-', 132, '-'));
3708 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
3709
3710 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Processing Details:'||LPAD(' ', 113, ' '));
3711 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
3712 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Number of Successful Records: '||l_succ_cnt);
3713 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Number of Errored Records: '||total_error_tbl.COUNT);
3714 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Total: '||(l_succ_cnt+total_error_tbl.COUNT));
3715 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
3716
3717 total_error_tbl.DELETE;
3718
3719 -- End New Out File stmathew 15-OCT-2004
3720 IF x_msg_count > 0 THEN
3721 FOR i IN 1..x_msg_count LOOP
3722 IF i = 1 THEN
3723 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Details of Errored Records:'||LPAD(' ', 97, ' '));
3724 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
3725 END IF;
3726 Fnd_Msg_Pub.get (p_msg_index => i,
3727 p_encoded => 'F',
3728 p_data => lx_msg_data,
3729 p_msg_index_out => l_msg_index_out);
3730
3731 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,TO_CHAR(i) || ': ' || lx_msg_data);
3732
3733 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
3734 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,'okl_internal_to_external',
3735 TO_CHAR(i) || ': ' || lx_msg_data);
3736 END IF;
3737
3738 END LOOP;
3739 END IF;
3740
3741 ie_tbl1.DELETE;
3742 ie_tbl2.DELETE;
3743
3744 -- -------------------------------------------
3745 -- Purge data from the Parallel process Table
3746 -- -------------------------------------------
3747
3748 IF p_assigned_process IS NOT NULL THEN
3749
3750 DELETE OKL_PARALLEL_PROCESSES
3751 WHERE assigned_process = p_assigned_process;
3752
3753 COMMIT;
3754
3755 END IF;
3756
3757 Okl_Api.END_ACTIVITY (
3758 x_msg_count => x_msg_count,
3759 x_msg_data => x_msg_data);
3760 PRINT_TO_LOG('IE End time internal_to_external : '||TO_CHAR(SYSDATE, 'HH:MI:SS'));
3761
3762 PRINT_TO_LOG('=========================================================================================');
3763 PRINT_TO_LOG(' ** End Processing. Please See Error Log for any errored transactions ** ');
3764 PRINT_TO_LOG('=========================================================================================');
3765
3766
3767 EXCEPTION
3768 ------------------------------------------------------------
3769 -- Exception handling
3770 ------------------------------------------------------------
3771 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
3772 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'Error (EXCP) => '||SQLERRM);
3773
3774 IF p_assigned_process IS NOT NULL THEN
3775 DELETE OKL_PARALLEL_PROCESSES
3776 WHERE assigned_process = p_assigned_process;
3777 COMMIT;
3778 END IF;
3779
3780 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
3781 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,'okl_internal_to_external',
3782 'EXCEPTION :'||'OKL_API.G_EXCEPTION_ERROR');
3783 END IF;
3784
3785 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
3786 p_api_name => l_api_name,
3787 p_pkg_name => G_PKG_NAME,
3788 p_exc_name => 'Okl_Api.G_RET_STS_ERROR',
3789 x_msg_count => x_msg_count,
3790 x_msg_data => x_msg_data,
3791 p_api_type => '_PVT');
3792
3793 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
3794 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'Error (UNEXP) => '||SQLERRM);
3795
3796 IF p_assigned_process IS NOT NULL THEN
3797 DELETE OKL_PARALLEL_PROCESSES
3798 WHERE assigned_process = p_assigned_process;
3799 COMMIT;
3800 END IF;
3801
3802 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
3803 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,'okl_internal_to_external',
3804 'EXCEPTION :'||'OKL_API.G_EXCEPTION_UNEXPECTED_ERROR');
3805 END IF;
3806 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
3807 p_api_name => l_api_name,
3808 p_pkg_name => G_PKG_NAME,
3809 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
3810 x_msg_count => x_msg_count,
3811 x_msg_data => x_msg_data,
3812 p_api_type => '_PVT');
3813
3814 WHEN OTHERS THEN
3815 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, 'Error (OTHERS 2) => '||SQLERRM);
3816
3817 IF p_assigned_process IS NOT NULL THEN
3818 DELETE OKL_PARALLEL_PROCESSES
3819 WHERE assigned_process = p_assigned_process;
3820 COMMIT;
3821 END IF;
3822
3823 IF (Fnd_Log.LEVEL_EXCEPTION >= Fnd_Log.G_CURRENT_RUNTIME_LEVEL) THEN
3824 Fnd_Log.STRING(Fnd_Log.LEVEL_EXCEPTION,'okl_internal_to_external',
3825 'EXCEPTION :'||'OTHERS');
3826 END IF;
3827 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
3828 p_api_name => l_api_name,
3829 p_pkg_name => G_PKG_NAME,
3830 p_exc_name => 'OTHERS',
3831 x_msg_count => x_msg_count,
3832 x_msg_data => x_msg_data,
3833 p_api_type => '_PVT');
3834
3835
3836 END internal_to_external;
3837
3838
3839 END Okl_Internal_To_External;