[Home] [Help]
PACKAGE BODY: APPS.OKL_EXT_BILLING_CHARGES_PVT
Source
1 PACKAGE BODY OKL_EXT_BILLING_CHARGES_PVT AS
2 /* $Header: OKLRBCGB.pls 120.28.12020000.2 2012/11/21 12:29:41 venkatho ship $ */
3
4 G_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_esg_transport_pvt';
5 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
7 -- Start of wraper code generated automatically by Debug code generator
8 L_MODULE VARCHAR2(40) := 'LEASE.RECEIVABLES.BILLING';
9 L_DEBUG_ENABLED VARCHAR2(10);
10 L_LEVEL_PROCEDURE NUMBER;
11 IS_DEBUG_PROCEDURE_ON BOOLEAN;
12 -- End of wraper code generated automatically by Debug code generator
13
14 -- Global variable for total records processed
15 g_total_rec_count NUMBER := 0;
16 g_txn_rec_count NUMBER := 0;
17 g_apt_rec_count NUMBER := 0;
18 g_batch_num NUMBER := 0;
19
20 ------------------------------------------------------------------
21 -- Procedure BIL_STREAMS to bill outstanding stream elements
22 ------------------------------------------------------------------
23 PROCEDURE billing_charges(
24 p_api_version IN NUMBER
25 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
26 ,x_return_status OUT NOCOPY VARCHAR2
27 ,x_msg_count OUT NOCOPY NUMBER
28 ,x_msg_data OUT NOCOPY VARCHAR2
29 ,p_name IN VARCHAR2 DEFAULT NULL
30 ,p_sequence_number IN NUMBER DEFAULT NULL
31 ,p_date_transmission IN DATE DEFAULT NULL
32 ,p_origin IN VARCHAR2 DEFAULT NULL
33 ,p_destination IN VARCHAR2 DEFAULT NULL)
34 IS
35 ------------------------------------------------------------
36 -- Extract all External records to be billed
37 ------------------------------------------------------------
38 CURSOR c_bill_chrgs
39 IS
40 SELECT rowid
41 , CONTRACT_NUMBER
42 , STY_NAME
43 , ASSET_NUMBER
44 , INVOICE_DATE
45 , AMOUNT
46 , CURRENCY_CODE
47 , STREAM_TYPE_PURPOSE
48 FROM OKL_EXT_BILLING_INTERFACE
49 WHERE TRX_STATUS_CODE = 'SUBMITTED';
50
51 -- --------------------------------------------
52 -- Cursor for all actual property tax records
53 -- --------------------------------------------
54 CURSOR act_prop_tax_csr ( p_request_id NUMBER )
55 IS
56 SELECT rowid
57 , TRX_STATUS_CODE
58 , ERROR_MESSAGE
59 , CONTRACT_NUMBER
60 , CONTRACT_ID
61 , STY_ID
62 , STY_NAME
63 , ASSET_ID
64 , ASSET_NUMBER
65 , INVOICE_DATE
66 , AMOUNT
67 , CURRENCY_CODE
68 , ORG_ID
69 , JURSDCTN_TYPE
70 , JURSDCTN_NAME
71 , MLRT_TAX
72 , TAX_VENDOR_ID
73 , TAX_VENDOR_NAME
74 , TAX_VENDOR_SITE_ID
75 , TAX_VENDOR_SITE_NAME
76 , STREAM_TYPE_PURPOSE
77 , TAX_ASSESSMENT_DATE --FPbug#5891876
78 FROM OKL_EXT_BILLING_INTERFACE
79 WHERE TRX_STATUS_CODE = 'PASSED'
80 AND REQUEST_ID = p_request_id
81 AND STREAM_TYPE_PURPOSE = 'ACTUAL_PROPERTY_TAX'
82 ORDER BY contract_id, asset_id, sty_id ; -- Bug 6375368
83
84 /* Bug#6375368 The Unique Constraint error fires if there are multiple
85 * contracts to be processed in OKL_EXT_BILLING_INTERFACE table. As the
86 * contract changes and the contract repeats for further processing the
87 * max_line_num_csr returns l_max_line_num as NULL or a number thats used
88 * earlier for the contract, as the insert for okl_strm_elements is done
89 * outside the loop. When the insert is done a unique constraint is fired
90 * due to duplication of se_line_number in SEL_TBL. The order by clause
91 * is added in act_prop_tax_csr to process the records in order of
92 * contract_id, asset_id,sty_id resulting in unique se_line_number for
93 * each contract, asset and sty_id.*/
94 -- --------------------------------------------
95 -- Cursor for all non-actual property tax records
96 -- --------------------------------------------
97 CURSOR bill_txn_csr ( p_request_id NUMBER )
98 IS
99 SELECT rowid
100 , TRX_STATUS_CODE
101 , ERROR_MESSAGE
102 , CONTRACT_NUMBER
103 , CONTRACT_ID
104 , STY_ID
105 , STY_NAME
106 , ASSET_ID
107 , ASSET_NUMBER
108 , INVOICE_DATE
109 , AMOUNT
110 , CURRENCY_CODE
111 , ORG_ID
112 , JURSDCTN_TYPE
113 , JURSDCTN_NAME
114 , MLRT_TAX
115 , TAX_VENDOR_ID
116 , TAX_VENDOR_NAME
117 , TAX_VENDOR_SITE_ID
118 , TAX_VENDOR_SITE_NAME
119 , STREAM_TYPE_PURPOSE
120 , TAX_ASSESSMENT_DATE --FPbug#5891876
121 FROM OKL_EXT_BILLING_INTERFACE
122 WHERE TRX_STATUS_CODE = 'PASSED'
123 AND REQUEST_ID = p_request_id
124 AND STREAM_TYPE_PURPOSE <> 'ACTUAL_PROPERTY_TAX';
125
126 ------------------------------------------------------------
127 -- Declare variables required by APIs
128 ------------------------------------------------------------
129 l_api_version CONSTANT NUMBER := 1;
130 l_api_name CONSTANT VARCHAR2(30) := 'EXTERNAL_BILLING_CHARGES';
131 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
132 l_stm_id NUMBER;
133
134 CURSOR c_stm_id ( p_khr_id NUMBER, p_sty_id NUMBER ) IS
135 SELECT id
136 FROM okl_streams
137 WHERE khr_id = p_khr_id
138 AND sty_id = p_sty_id;
139
140 CURSOR c_sty_id ( p_stream_name VARCHAR2 ) IS
141 SELECT id
142 FROM okl_strm_type_v
143 WHERE name = p_stream_name;
144
145 CURSOR get_khr_id_csr ( p_contract_number VARCHAR2 ) IS
146 SELECT id
147 FROM okc_k_headers_b
148 WHERE contract_number = p_contract_number;
149
150 CURSOR get_currency_csr ( p_contract_number VARCHAR2 ) IS
151 SELECT currency_code
152 FROM okc_k_headers_b
153 WHERE contract_number = p_contract_number;
154
155 CURSOR get_try_id_csr (cp_name VARCHAR2, cp_language VARCHAR2) IS
156 SELECT id
157 FROM okl_trx_types_tl
158 WHERE name = cp_name
159 AND LANGUAGE = cp_language;
160
161 CURSOR get_sty_id_csr ( p_sty_name VARCHAR2, p_purpose_code VARCHAR2 ) IS
162 SELECT id, billable_yn
163 FROM okl_strm_type_v
164 WHERE name = p_sty_name
165 AND stream_type_purpose = p_purpose_code;
166
167 CURSOR get_sty_name_csr ( p_sty_id NUMBER ) IS
168 SELECT name
169 FROM okl_strm_type_v
170 WHERE id = p_sty_id;
171
172 CURSOR get_purpose_csr ( p_sty_name VARCHAR2 ) IS
173 SELECT stream_type_purpose
174 FROM okl_strm_type_v
175 WHERE name = p_sty_name;
176
177 CURSOR get_kle_id_csr ( p_asset_number VARCHAR2 ) IS
178 SELECT ID
179 FROM okl_k_lines_full_v
180 WHERE name = p_asset_number;
181
182 CURSOR curr_code_csr ( p_currency_code VARCHAR2) IS
183 SELECT currency_code
184 FROM fnd_currencies
185 WHERE currency_code = p_currency_code;
186
187 ------------------------------------------------------------------
188 --Variables required for Billing Engine
189 ------------------------------------------------------------------
190 l_taiv_rec okl_tai_pvt.taiv_rec_type;
191 l_tldv_rec okl_tld_pvt.tldv_rec_type;
192 l_tilv_rec okl_til_pvt.tilv_rec_type;
193 l_tilv_tbl okl_til_pvt.tilv_tbl_type;
194 l_tldv_tbl okl_tld_pvt.tldv_tbl_type;
195 x_taiv_rec okl_tai_pvt.taiv_rec_type;
196 x_tilv_tbl okl_til_pvt.tilv_tbl_type;
197 x_tldv_tbl okl_tld_pvt.tldv_tbl_type;
198 l_bsl_id OKS_BILL_SUB_LINES.id%TYPE;
199 l_bcl_id OKS_BILL_CONT_LINES.id%TYPE;
200 l_btn_id OKC_K_REL_OBJS.id%TYPE;
201
202 -- ********************************
203 -- Local Variables
204 -- ********************************
205 l_khr_id okc_k_headers_b.id%type;
206 l_kle_id NUMBER;
207 l_try_id okl_trx_types_tl.id%type;
208 l_sty_id okl_strm_type_b.id%type;
209 l_currency_code fnd_currencies.currency_code%type;
210 l_khr_currency fnd_currencies.currency_code%type;
211 l_amount okl_trx_ar_invoices_v.amount%TYPE;
212 l_strm_purpose okl_strm_type_v.stream_type_purpose%TYPE;
213 l_trx_type_name VARCHAR2(30);
214 l_trx_type_lang CONSTANT VARCHAR2(30) := 'US';
215 l_err_status VARCHAR2(1);
216 l_err_msg VARCHAR2(1995);
217
218 -- Transaction Headers
219 i_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
220 r_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
221
222 -- Transaction Lines
223 i_tilv_rec Okl_Txl_Ar_Inv_Lns_Pub.tilv_rec_type;
224 r_tilv_rec Okl_Txl_Ar_Inv_Lns_Pub.tilv_rec_type;
225 l_init_ptcv_rec okl_property_tax_pub.ptcv_rec_type;
226 p_ptcv_rec okl_property_tax_pub.ptcv_rec_type;
227 x_ptcv_rec okl_property_tax_pub.ptcv_rec_type;
228
229 ------------------------------------------------------------
230 -- Declare variables to call Accounting Engine.
231 ------------------------------------------------------------
232 p_bpd_acc_rec Okl_Acc_Call_Pub.bpd_acc_rec_type;
233
234 l_actual_tax_count NUMBER;
235 l_max_line_num NUMBER;
236
237 l_p_tax_applicable okc_rules_b.rule_information3%TYPE;
238 l_vendor_id NUMBER;
239 l_vendor_site_id NUMBER;
240 l_p_tax_option okl_property_tax_setups.payable_invoice%TYPE;
241
242 ------------------------------------------------------------
243 -- Get Count of specific stream type
244 ------------------------------------------------------------
245 CURSOR c_sty_count_csr ( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_id NUMBER ) IS
246 SELECT count(*)
247 FROM okl_streams stm
248 , okl_strm_type_v sty
249 WHERE stm.khr_id = p_khr_id
250 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
251 AND stm.sty_id = sty.id
252 AND stm.say_code = 'CURR'
253 AND stm.active_yn = 'Y'
254 AND sty.id = p_sty_id;
255
256 ------------------------------------------------------------
257 -- Get stm_id of Actual Property Tax record
258 ------------------------------------------------------------
259 CURSOR c_stm_id_csr ( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_id NUMBER ) IS
260 SELECT stm.id
261 FROM okl_streams stm,
262 okl_strm_type_v sty
263 WHERE stm.khr_id = p_khr_id
264 AND NVL(stm.kle_id, -99) = NVL(p_kle_id, -99)
265 AND stm.sty_id = sty.id
266 AND stm.say_code = 'CURR'
267 AND stm.active_yn = 'Y'
268 AND sty.id = p_sty_id;
269
270 ------------------------------------------------------------
271 -- Transaction Number Cursor
272 ------------------------------------------------------------
273 CURSOR c_tran_num_csr IS
274 SELECT okl_sif_seq.nextval
275 FROM dual;
276
277 ------------------------------------------------------------
278 -- Max Line Number
279 ------------------------------------------------------------
280 CURSOR max_line_num_csr (p_stm_id NUMBER) IS
281 SELECT max(se_line_number)
282 FROM okl_strm_elements
283 WHERE stm_id = p_stm_id;
284
285 ------------------------------------------------------------
286 -- Property Tax applicable cursor
287 ------------------------------------------------------------
288 CURSOR p_tax_app_csr( p_chr_id IN NUMBER, p_cle_id IN NUMBER ) IS
289 SELECT rul.rule_information1
290 FROM okc_rule_groups_b rgp,
291 okc_rules_b rul
292 WHERE rgp.id = rul.rgp_id
293 AND rgp.rgd_code = 'LAASTX'
294 AND rul.RULE_INFORMATION_CATEGORY = 'LAPRTX'
295 AND rul.rule_information3 is not null
296 AND rgp.dnz_chr_id = p_chr_id
297 AND rgp.cle_id = p_cle_id;
298
299 ------------------------------------------------------------
300 -- Vendor Site Cursor (revalidate and retrieve)
301 ------------------------------------------------------------
302 CURSOR vendor_site_csr( p_vendor_id IN NUMBER,
303 p_vendor_site_id IN NUMBER,
304 p_vendor_site_code IN VARCHAR2 ) IS
305 SELECT VENDOR_SITE_ID
306 FROM po_vendor_sites_all
307 WHERE VENDOR_ID = p_vendor_id
308 AND (VENDOR_SITE_ID = p_vendor_site_id OR VENDOR_SITE_CODE = p_vendor_site_code);
309
310 ------------------------------------------------------------
311 -- Property Tax set up cursor
312 ------------------------------------------------------------
313 CURSOR p_tax_options_csr IS
314 SELECT PAYABLE_INVOICE
315 FROM okl_property_tax_setups;
316
317 -- -------------------------------------------------
318 -- Streams Record
319 -- -------------------------------------------------
320 l_stmv_rec Okl_Streams_Pub.stmv_rec_type;
321 lx_stmv_rec Okl_Streams_Pub.stmv_rec_type;
322 l_init_stmv_rec Okl_Streams_Pub.stmv_rec_type;
323 -- -------------------------------------------------
324 -- Stream Elements Record
325 -- -------------------------------------------------
326 p_selv_rec Okl_Sel_Pvt.selv_rec_type;
327 x_selv_rec Okl_Sel_Pvt.selv_rec_type;
328 l_init_selv_rec Okl_Sel_Pvt.selv_rec_type;
329
330 p_man_inv_rec OKL_PAY_INVOICES_MAN_PVT.man_inv_rec_type;
331 x_man_inv_rec OKL_PAY_INVOICES_MAN_PVT.man_inv_rec_type;
332
333 l_primary_sty_id OKL_STRM_TYPE_V.id%TYPE;
334 l_dependent_sty_id OKL_STRM_TYPE_V.id%TYPE;
335
336 l_billable_yn OKL_STRM_TYPE_V.billable_yn%TYPE;
337
338 l_sty_name OKL_STRM_TYPE_V.name%TYPE;
339 l_valid_strm_yn VARCHAR2(1);
340
341 l_bill_try_id NUMBER;
342 l_cm_try_id NUMBER;
343 l_commit_cnt NUMBER;
344 l_MAX_commit_cnt NUMBER := 500;
345
346 -- ------------------------------------------
347 -- Performance improvements
348 -- ------------------------------------------
349 ext_bill_tbl ext_tbl_type;
350 L_FETCH_SIZE NUMBER := 5000;
351
352 type num_tbl is table of number index by binary_integer ;
353 type date_tbl is table of date index by binary_integer ;
354 type chr_tbl is table of varchar2(2000) index by binary_integer ;
355 type rowid_tbl is table of rowid index by binary_integer ;
356
357 l_rowid_tbl rowid_tbl;
358 l_contract_tbl chr_tbl;
359 l_sty_tbl chr_tbl;
360 l_asset_num chr_tbl;
361 l_inv_date date_tbl;
362 l_amt_tbl num_tbl;
363 l_curr_code_tbl chr_tbl;
364 l_strm_purpose_tbl chr_tbl;
365 upd_rowid_tbl rowid_tbl;
366 tai_succ_rowid_tbl rowid_tbl;
367 tai_err_rowid_tbl rowid_tbl;
368
369 -- Base tables for insert
370 TYPE tai_tbl_type IS TABLE OF OKL_TRX_AR_INVOICES_B%ROWTYPE INDEX BY BINARY_INTEGER;
371 TYPE til_tbl_type IS TABLE OF OKL_TXL_AR_INV_LNS_B%ROWTYPE INDEX BY BINARY_INTEGER;
372 TYPE ptc_tbl_type IS TABLE OF OKL_PROPERTY_TAX_B%ROWTYPE INDEX BY BINARY_INTEGER;
373 TYPE stm_tbl_type IS TABLE OF OKL_STREAMS%ROWTYPE INDEX BY BINARY_INTEGER;
374 TYPE sel_tbl_type IS TABLE OF OKL_STRM_ELEMENTS%ROWTYPE INDEX BY BINARY_INTEGER;
375
376 -- TL tables for insert
377 TYPE taitl_tbl_type IS TABLE OF OKL_TRX_AR_INVOICES_TL%ROWTYPE INDEX BY BINARY_INTEGER;
378 TYPE tiltl_tbl_type IS TABLE OF OKL_TXL_AR_INV_LNS_TL%ROWTYPE INDEX BY BINARY_INTEGER;
379
380 TYPE ptctl_tbl_type IS TABLE OF OKL_PROPERTY_TAX_TL%ROWTYPE INDEX BY BINARY_INTEGER;
381
382 tai_tbl tai_tbl_type;
383 til_tbl til_tbl_type;
384 ptc_tbl ptc_tbl_type;
385 stm_tbl stm_tbl_type;
386 sel_tbl sel_tbl_type;
387
388 taitl_tbl taitl_tbl_type;
389 tiltl_tbl tiltl_tbl_type;
390 ptctl_tbl ptctl_tbl_type;
391
392 -- Record definitions
393 type tai_succ_rec_type is record (id number);
394 type tai_succ_tbl_type is table of tai_succ_rec_type index by binary_integer;
395
396 tai_succ_tbl num_tbl;
397 tai_err_tbl num_tbl;
398 header_id_tbl num_tbl;
399 upd_sel_tbl num_tbl;
400
401 l_header_id NUMBER;
402 l_line_id NUMBER;
403 l_taitl_cnt NUMBER;
404 l_tiltl_cnt NUMBER;
405 l_ptctl_cnt NUMBER;
406 l_stmtbl_cnt NUMBER;
407 l_acc_cmt_cnt NUMBER;
408 l_legal_entity_id OKL_TRX_AR_INVOICES_B.legal_entity_id%TYPE; -- for LE Uptake project 08-11-2006
409
410 CURSOR get_languages IS
411 SELECT *
412 FROM FND_LANGUAGES
413 WHERE INSTALLED_FLAG IN ('I', 'B');
414
415 -- ----------------------
416 -- Std Who columns
417 -- ----------------------
418 l_last_updated_by okl_trx_ar_invoices_v.last_updated_by%TYPE := Fnd_Global.USER_ID;
419 l_last_update_login okl_trx_ar_invoices_v.last_update_login%TYPE := Fnd_Global.LOGIN_ID;
420 l_request_id okl_trx_ar_invoices_v.request_id%TYPE := Fnd_Global.CONC_REQUEST_ID;
421
422 l_program_application_id
423 okl_trx_ar_invoices_v.program_application_id%TYPE := Fnd_Global.PROG_APPL_ID;
424 l_program_id okl_trx_ar_invoices_v.program_id%TYPE := Fnd_Global.CONC_PROGRAM_ID;
425
426 bulk_errors EXCEPTION;
427 PRAGMA EXCEPTION_INIT (bulk_errors, -24381);
428
429 -- print processing summary variables
430 -- ----------------------------------------------------
431 -- count successful records for actual property tax
432 -- also counts errors
433 -- ----------------------------------------------------
434 cursor ext_apt_stat_csr( p_request_id NUMBER, p_sts_code VARCHAR2, p_strm_purpose VARCHAR2 ) is
435 select count(*)
436 from okl_ext_billing_interface
437 where request_id = p_request_id
438 and trx_status_code = p_sts_code
439 and STREAM_TYPE_PURPOSE = p_strm_purpose;
440
441 -- ------------------------------------------------------
442 -- count successful records for non-actual property tax
443 -- also counts errors
444 -- ------------------------------------------------------
445 cursor ext_non_apt_stat_csr( p_request_id NUMBER, p_sts_code VARCHAR2, p_strm_purpose VARCHAR2 ) is
446 select count(*)
447 from okl_ext_billing_interface
448 where request_id = p_request_id
449 and trx_status_code = p_sts_code
450 and STREAM_TYPE_PURPOSE <> p_strm_purpose;
451
452 -- ------------------------------------------------------
453 -- print error message
454 -- ------------------------------------------------------
455 cursor error_msg_csr( p_request_id NUMBER, p_sts_code VARCHAR2 ) is
456 select rpad(substr(contract_number,1,30),30,' ') contract_number,
457 rpad(substr(asset_number,1,30),30,' ') asset_number,
458 rpad(substr(sty_name,1,30),30,' ') sty_name,
459 to_char(invoice_date,'DD-MON-RRRR') invoice_date,
460 error_message
461 from okl_ext_billing_interface
462 where request_id = p_request_id
463 and trx_status_code = p_sts_code;
464
465 -- ----------------------------------------------------------
466 -- Operating Unit
467 -- ----------------------------------------------------------
468 CURSOR op_unit_csr IS
469 SELECT NAME
470 FROM hr_operating_units
471 WHERE ORGANIZATION_ID = MO_GLOBAL.GET_CURRENT_ORG_ID; -- MOAC fix - Bug#5378114 --varangan- 29-9-06
472
473 l_succ_apt_cnt NUMBER;
474 l_err_apt_cnt NUMBER;
475
476 l_succ_non_apt_cnt NUMBER;
477 l_err_non_apt_cnt NUMBER;
478 lx_msg_data VARCHAR2(450);
479 l_msg_index_out NUMBER := 0;
480 l_op_unit_name hr_operating_units.name%TYPE;
481
482 processed_sts okl_trx_ar_invoices_v.trx_status_code%TYPE := 'PROCESSED';
483 error_sts okl_trx_ar_invoices_v.trx_status_code%TYPE := 'ERROR';
484
485 --added by kbbhavsa : Bug 5344799/5362220 : 27-June-06
486 l_sty_desc OKL_STRM_TYPE_V.name%TYPE;
487 l_temp_index NUMBER;
488 x_multiple_line_error EXCEPTION;
489 PRAGMA EXCEPTION_INIT (x_multiple_line_error, -24381 ); -- ORA-24381: error(s) in array DML
490
491 -- Start of Declarations added by Venkatho as part of Bug# 14119181
492 l_cust_acct_id NUMBER;
493 l_cust_party_id NUMBER;
494 l_bankruptcy_flag VARCHAR2(1);
495
496 CURSOR c_get_khr IS
497 SELECT distinct contract_number, contract_id
498 FROM okl_ext_billing_interface
499 WHERE contract_number IS NOT NULL
500 AND trx_status_code = 'SUBMITTED';
501
502 -- End of Declarations added by Venkatho as part of Bug# 14119181
503
504 BEGIN
505 L_DEBUG_ENABLED := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
506
507 IF (L_DEBUG_ENABLED='Y' and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
508 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
509 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Begin(+)');
510 END IF;
511 END IF;
512
513 ------------------------------------------------------------
514 -- Start processing
515 ------------------------------------------------------------
516 x_return_status := OKL_API.G_RET_STS_SUCCESS;
517
518 l_return_status := OKL_API.START_ACTIVITY(
519 p_api_name => l_api_name
520 ,p_pkg_name => G_PKG_NAME
521 ,p_init_msg_list => p_init_msg_list
522 ,l_api_version => l_api_version
523 ,p_api_version => p_api_version
524 ,p_api_type => '_PVT'
525 ,x_return_status => l_return_status);
526
527 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
528 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
529 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
530 RAISE Okl_Api.G_EXCEPTION_ERROR;
531 END IF;
532
533 ------------------------------------------------------------
534 -- Process every external billing line for billing import
535 ------------------------------------------------------------
536 FND_FILE.PUT_LINE (FND_FILE.log, '================================================================');
537 FND_FILE.PUT_LINE (FND_FILE.log, ' *** START PROCESSING THIRD PARTY BILLING RECORDS ***');
538 FND_FILE.PUT_LINE (FND_FILE.log, '================================================================');
539
540 -- -----------------------------------------------------
541 -- Fetch property tax
542 -- -----------------------------------------------------
543 l_p_tax_applicable := NULL;
544 OPEN p_tax_options_csr;
545 FETCH p_tax_options_csr INTO l_p_tax_applicable;
546 CLOSE p_tax_options_csr;
547
548 -- -----------------------------------------------------
549 -- Get transaction type
550 -- -----------------------------------------------------
551 l_trx_type_name := 'Billing';
552 l_bill_try_id := NULL;
553 OPEN get_try_id_csr (l_trx_type_name, l_trx_type_lang);
554 FETCH get_try_id_csr INTO l_bill_try_id;
555 CLOSE get_try_id_csr;
556
557 l_trx_type_name := 'Credit Memo';
558 l_cm_try_id := NULL;
559 OPEN get_try_id_csr (l_trx_type_name, l_trx_type_lang);
560 FETCH get_try_id_csr INTO l_cm_try_id;
561 CLOSE get_try_id_csr;
562 -- --------------------------------------------
563 -- Print error message and stop processing
564 -- --------------------------------------------
565 if l_bill_try_id is null or l_cm_try_id is null then
566 FND_FILE.PUT_LINE (FND_FILE.log, '********************** ERROR **********************');
567 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'BILLING or CREDIT MEMO transaction type is invalid.');
568 FND_FILE.PUT_LINE (FND_FILE.log, '********************** ERROR **********************');
569
570 RAISE OKL_API.G_EXCEPTION_ERROR;
571 END IF;
572
573 if l_request_id = -1 then
574 l_request_id := NULL;
575 end if;
576
577 if l_program_application_id = -1 then
578 l_program_application_id := NULL;
579 end if;
580
581 if l_program_id = -1 then
582 l_program_id := NULL;
583 end if;
584
585 if l_request_id is null then
586 FND_FILE.PUT_LINE (FND_FILE.log, '********************** ERROR **********************');
587 FND_FILE.PUT_LINE (FND_FILE.log,
588 'Cannot determine request Id from profile. The function Fnd_Global.CONC_REQUEST_ID returns -1.');
589 FND_FILE.PUT_LINE (FND_FILE.log, '********************** ERROR **********************');
590 RAISE OKL_API.G_EXCEPTION_ERROR;
591 END IF;
592
593 -- -------------------------------
594 -- Process each cursor record
595 -- -------------------------------
596 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
597 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'*** Start Validation ***');
598 END IF;
599
600 g_batch_num := 0;
601
602 OPEN c_bill_chrgs;
603 LOOP
604 g_batch_num := g_batch_num + 1;
605 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
606 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - validating batch number: '||g_batch_num);
607 END IF;
608
609 l_rowid_tbl.delete;
610 l_contract_tbl.delete;
611 l_sty_tbl.delete;
612 l_asset_num.delete;
613 l_inv_date.delete;
614 l_amt_tbl.delete;
615 l_curr_code_tbl.delete;
616 l_strm_purpose_tbl.delete;
617
618 FETCH c_bill_chrgs BULK COLLECT INTO
619 l_rowid_tbl,
620 l_contract_tbl,
621 l_sty_tbl,
622 l_asset_num,
623 l_inv_date,
624 l_amt_tbl,
625 l_curr_code_tbl,
626 l_strm_purpose_tbl
627 LIMIT L_FETCH_SIZE;
628
629 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
630 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'*=> number of records: '||l_rowid_tbl.count);
631 END IF;
632
633 -- update global total rec count
634 g_total_rec_count := g_total_rec_count + l_rowid_tbl.count;
635
636 -- -------------------------------------------------------
637 -- update std who parameters in okl_ext_billing_interface
638 -- -------------------------------------------------------
639 if l_rowid_tbl.count > 0 then
640 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
641 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - populate std who columns.');
642 END IF;
643 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
644 update okl_ext_billing_interface
645 set CREATED_BY = l_last_updated_by,
646 CREATION_DATE = sysdate,
647 LAST_UPDATED_BY = l_last_updated_by,
648 LAST_UPDATE_DATE = sysdate,
649 LAST_UPDATE_LOGIN = l_last_update_login,
650 REQUEST_ID = l_request_id,
651 PROGRAM_APPLICATION_ID = l_program_application_id,
652 PROGRAM_ID = l_program_id,
653 PROGRAM_UPDATE_DATE = sysdate
654 where rowid = l_rowid_tbl(indx);
655 COMMIT;
656 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
657 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - populate std who columns.');
658 END IF;
659 END IF;
660
661 IF l_strm_purpose_tbl.count > 0 THEN
662 -- ---------------------------------
663 -- Validate Stream Type Purpose
664 -- ---------------------------------
665 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
666 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - validate stream type purpose.');
667 END IF;
668 forall indx in l_strm_purpose_tbl.first..l_strm_purpose_tbl.last
669 update okl_ext_billing_interface
670 set trx_status_code =
671 decode(l_strm_purpose_tbl(indx),NULL,'ERROR',
672 -- 'RENT','ERROR',
673 -- 'ADVANCE_RENT','ERROR',
674 -- 'INTEREST_PAYMENT','ERROR',
675 -- 'PRINCIPAL_PAYMENT','ERROR',
676 trx_status_code
677 )
678 ,error_message =
679 decode(l_strm_purpose_tbl(indx),NULL,'Stream Type purpose cannot be null. '--,
680 -- 'RENT','STREAM PURPOSE RENT is not supported. ',
681 -- 'ADVANCE_RENT','STREAM PURPOSE ADVANCE RENT is not supported. ',
682 -- 'INTEREST_PAYMENT','STREAM PURPOSE INTEREST PAYMENT is not supported. ',
683 -- 'PRINCIPAL_PAYMENT','STREAM PURPOSE PRINCIPAL PAYMENT is not supported. '
684 )
685 where rowid = l_rowid_tbl(indx);
686 COMMIT;
687 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
688 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - validate stream type purpose.');
689 END IF;
690 END IF; -- stream type purpose validation
691
692 -- ---------------------------------
693 -- Validate Amount
694 -- ---------------------------------
695 if l_amt_tbl.count > 0 then
696 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
697 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - validate amount.');
698 END IF;
699 forall indx in l_amt_tbl.first..l_amt_tbl.last
700 update okl_ext_billing_interface
701 set trx_status_code = decode(l_amt_tbl(indx),NULL, 'ERROR',trx_status_code),
702 error_message =
703 decode(l_amt_tbl(indx),NULL, error_message||'Amount Cannot be null. ',
704 0, error_message||'Amount must be non-zero. ',
705 error_message)
706 where rowid = l_rowid_tbl(indx);
707 COMMIT;
708 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
709 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - validate amount.');
710 END IF;
711 END IF; -- Update rows to passed status
712
713 -- ---------------------------------
714 -- Validate Contract
715 -- ---------------------------------
716 if l_contract_tbl.count > 0 then
717 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
718 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - validate contract number.');
719 END IF;
720 FORALL indx in l_contract_tbl.first..l_contract_tbl.last
721 update okl_ext_billing_interface a
722 set contract_id = (select id
723 from okc_k_headers_b b
724 where b.contract_number = a.contract_number)
725 where rowid = l_rowid_tbl(indx);
726 COMMIT;
727 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
728 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - validate contract number.');
729 END IF;
730 END IF;
731
732 -- ---------------------------------
733 -- Validate Asset
734 -- ---------------------------------
735 -- PAGARG - Bug# 6884934 - Modified Start
736 -- Modified to enhance the logic when ASSET_ID is also part of import record
737 -- The following cases are handled by the BULK UPDATION CODE
738 -- CASE 1 - When ASSET_NUMBER is NULL, the record is not updated
739 -- CASE 2 - When ASSET_NUMBER present and ASSET_ID present, code validates and updates the
740 -- NULL if the ASSET_ID doesnot correspond to the ASSET_NUMBER
741 -- CASE 3 - When ASSET_NUMBER present and ASSET_ID is NULL, code checks if there are multiple
742 -- contract lines with same line name and errors accordingly
743 IF l_asset_num.count > 0 THEN
744 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
745 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - validate asset number.');
746 END IF;
747 BEGIN
748 FORALL indx IN l_asset_num.FIRST..l_asset_num.LAST SAVE EXCEPTIONS
749 UPDATE okl_ext_billing_interface a
750 SET asset_id = (SELECT id
751 FROM OKL_K_LINES_FULL_V b
752 -- NVL is for imported contracts where
753 -- Service Name is NULL in OKC_K_LINES_TL
754 WHERE NVL(b.name, a.ASSET_NUMBER) = a.ASSET_NUMBER
755 AND b.ID = NVL(A.ASSET_ID, b.ID)
756 AND b.dnz_chr_id = a.contract_id)
757 WHERE rowid = l_rowid_tbl(indx)
758 AND a.ASSET_NUMBER IS NOT NULL;
759 EXCEPTION
760 WHEN x_multiple_line_error THEN
761 -- This exception will be thrown if there are similar service lines
762 -- on the same contract and ASSET_ID is not present in the record
763 -- OKL_EXT_BILLING_INTERFACE. In this case the select query returns
764 -- all the service lines based on the Service Name
765
766 -- Update the status of the record as ERROR with error message
767 FOR err_indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
768 LOOP
769 l_temp_index := SQL%BULK_EXCEPTIONS(err_indx).ERROR_INDEX;
770 UPDATE OKL_EXT_BILLING_INTERFACE B_INT
771 SET TRX_STATUS_CODE = 'ERROR'
772 , ERROR_MESSAGE = ERROR_MESSAGE || 'Multiple contract lines with same name. Please also provide Contract Line ID.'
773 WHERE rowid = l_rowid_tbl(l_temp_index);
774 END LOOP;
775 END; -- end of ASSET_ID updation
776 COMMIT;
777 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
778 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - validate asset number.');
779 END IF;
780 END IF;
781 -- PAGARG - Bug# 6884934 - Modified End
782
783 -- ---------------------------------
784 -- Validate Stream Type Name
785 -- ---------------------------------
786 IF l_sty_tbl.count > 0 THEN
787 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
788 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - validate stream type assignment to a stream generation template.');
789 END IF;
790 forall indx in l_sty_tbl.first..l_sty_tbl.last
791 update okl_ext_billing_interface a
792 set sty_id = (SELECT GTLV.PRIMARY_STY_ID STY_ID
793 FROM
794 OKL_ST_GEN_TMPT_LNS GTLV,
795 OKL_ST_GEN_TEMPLATES GTTV,
796 OKL_ST_GEN_TMPT_SETS GTSV,
797 OKL_AE_TMPT_SETS AES,
798 OKL_PRODUCTS PDT,
799 OKL_STRM_TYPE_V STY,
800 okl_k_headers khr,
801 okc_k_headers_b chr
802 WHERE
803 GTLV.GTT_ID = GTTV.ID AND
804 GTTV.GTS_ID = GTSV.ID AND
805 GTTV.TMPT_STATUS = 'ACTIVE' AND
806 GTSV.ID = AES.GTS_ID AND
807 AES.ID = PDT.AES_ID AND
808 GTLV.PRIMARY_STY_ID = STY.ID AND
809 GTLV.PRIMARY_YN = 'Y' and
810 -- added stmathew
811 khr.id = chr.id and
812 khr.pdt_id = pdt.id and
813 GTTV.start_date <= chr.start_date and
814 (GTTV.end_date >= chr.start_date or GTTV.end_date is null ) and
815 sty.billable_yn = 'Y' and
816 khr.id = a.CONTRACT_ID and
817 sty.name = a.STY_NAME and
818 sty.stream_type_purpose = a.STREAM_TYPE_PURPOSE
819 UNION ALL
820 SELECT
821 DISTINCT
822 GTLV.DEPENDENT_STY_ID STY_ID
823 FROM
824 OKL_ST_GEN_TMPT_LNS GTLV,
825 OKL_ST_GEN_TEMPLATES GTTV,
826 OKL_ST_GEN_TMPT_SETS GTSV,
827 OKL_AE_TMPT_SETS AES,
828 OKL_PRODUCTS PDT,
829 OKL_STRM_TYPE_V STY,
830 okl_k_headers khr,
831 okc_k_headers_b chr
832 WHERE
833 GTLV.GTT_ID = GTTV.ID AND
834 GTTV.GTS_ID = GTSV.ID AND
835 GTTV.TMPT_STATUS = 'ACTIVE' AND
836 GTSV.ID = AES.GTS_ID AND
837 AES.ID = PDT.AES_ID AND
838 GTLV.DEPENDENT_STY_ID = STY.ID AND
839 (GTLV.PRIMARY_YN = 'N' or GTLV.PRIMARY_YN is null) AND
840 -- added stmathew
841 khr.id = chr.id and
842 khr.pdt_id = pdt.id and
843 GTTV.start_date <= chr.start_date and
844 (GTTV.end_date >= chr.start_date or GTTV.end_date is null ) and
845 sty.billable_yn = 'Y' and
846 khr.id = a.CONTRACT_ID and
847 sty.name = a.STY_NAME and
848 sty.stream_type_purpose = a.STREAM_TYPE_PURPOSE)
849 WHERE rowid = l_rowid_tbl(indx);
850 COMMIT;
851 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
852 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - validate stream type assignment to a stream generation template.');
853 END IF;
854 END IF;
855
856 -- ---------------------------------
857 -- Validate khr_id
858 -- ---------------------------------
859 if l_rowid_tbl.count > 0 then
860 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
861 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - flag invalid contracts.');
862 END IF;
863 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
864 update okl_ext_billing_interface
865 set trx_status_code = decode(contract_id,NULL,'ERROR',trx_status_code),
866 error_message = decode(contract_id,NULL,error_message||'Invalid Contract. ',error_message)
867 where rowid = l_rowid_tbl(indx);
868 commit;
869 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
870 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - flag invalid contracts.');
871 END IF;
872 END IF;
873
874
875 -- Start of code added by Venkatho as part of Bug# 14119181
876 -------------------------------------------------------------------
877 -- Validate Bankruptcy check for customers on different Contracts
878 -------------------------------------------------------------------
879 BEGIN
880
881 IF (G_IS_DEBUG_STATEMENT_ON = TRUE) THEN
882 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - flag Bankruptcy check for Customers on contracts.');
883 END IF;
884
885 FOR l_get_khr IN c_get_khr
886 LOOP
887
888 l_cust_acct_id := NULL;
889 l_cust_party_id := NULL;
890
891 IF (l_get_khr.contract_id IS NOT NULL)
892 THEN
893
894 Okl_Stream_Billing_Pvt.get_customer_dtls (p_khr_id => l_get_khr.contract_id,
895 p_cust_acct_id => l_cust_acct_id,
896 p_cust_party_id => l_cust_party_id);
897 END IF;
898
899 l_bankruptcy_flag := NULL;
900
901 IF (l_cust_acct_id IS NOT NULL AND l_cust_party_id IS NOT NULL)
902 THEN
903
904 l_bankruptcy_flag := Okl_Stream_Billing_Pvt.get_bankruptcy_status_new (p_cust_acct_id => l_cust_acct_id,
905 p_cust_party_id => l_cust_party_id
906 );
907 END IF;
908
909 IF (l_bankruptcy_flag = 'Y') THEN
910
911 IF (G_IS_DEBUG_STATEMENT_ON = TRUE) THEN
912 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Invoices of contract => ' || l_get_khr.contract_number || ' not billed due to bankruptcy.');
913 END IF;
914
915 UPDATE okl_ext_billing_interface
916 SET trx_status_code = 'ERROR',
917 error_message = 'Invoices of contract => ' || l_get_khr.contract_number || ' not billed due to bankruptcy.'
918 WHERE contract_number = l_get_khr.contract_number;
919
920 END IF;
921
922 END LOOP;
923
924 IF (G_IS_DEBUG_STATEMENT_ON = TRUE) THEN
925 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ end - flag Bankruptcy check for Customers on contracts.');
926 END IF;
927
928 END;
929 -- End of code added by Venkatho as part of Bug# 14119181
930
931
932 -- PAGARG - Bug# 6884934 - Modified Start
933 -- ---------------------------------
934 -- Validate kle_id
935 -- Errors if
936 -- Line ID doesnot match the Line Name
937 -- Line ID is not correct for the contract
938 -- Line Name is not correct for the contract
939 -- ---------------------------------
940 IF l_rowid_tbl.count > 0 THEN
941 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
942 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - flag invalid lines.');
943 END IF;
944 FORALL indx in l_rowid_tbl.first..l_rowid_tbl.last
945 UPDATE OKL_EXT_BILLING_INTERFACE a
946 SET TRX_STATUS_CODE = 'ERROR',
947 ERROR_MESSAGE = ERROR_MESSAGE||'Invalid Line name or Line Id.'
948 WHERE rowid = l_rowid_tbl(indx)
949 AND (a.ASSET_NUMBER IS NOT NULL OR a.ASSET_ID IS NOT NULL)
950 AND NOT EXISTS ( SELECT 1
951 FROM OKL_K_LINES_FULL_V LNS
952 WHERE LNS.ID = a.ASSET_ID
953 -- IS NULL condition is useful for imported contracts
954 -- where Service Name is NULL in OKC_K_LINES_TL
955 AND ( LNS.NAME IS NULL OR LNS.NAME = NVL(a.ASSET_NUMBER, LNS.NAME))
956 AND LNS.DNZ_CHR_ID = a.CONTRACT_ID);
957 COMMIT;
958 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
959 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - flag invalid lines.');
960 END IF;
961 END IF;
962 -- PAGARG - Bug# 6884934 - Modified End
963
964 -- ---------------------------------
965 -- Validate sty_id
966 -- ---------------------------------
967 if l_rowid_tbl.count > 0 then
968 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
969 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - flag invalid stream types.');
970 END IF;
971 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
972 update okl_ext_billing_interface
973 set trx_status_code = decode(sty_id,NULL,'ERROR',trx_status_code),
974 error_message = decode(sty_id,NULL,
975 error_message||'Stream type is invalid, non-billable or unattached to a template. ',
976 error_message)
977 where rowid = l_rowid_tbl(indx);
978 commit;
979 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
980 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - flag invalid stream types.');
981 END IF;
982 end if;
983
984 -- ---------------------------------
985 -- Validate invoice_date
986 -- ---------------------------------
987 if l_rowid_tbl.count > 0 then
988 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
989 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - validate invoice date.');
990 END IF;
991 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
992 update okl_ext_billing_interface
993 set trx_status_code = decode(invoice_date,NULL,'ERROR',trx_status_code),
994 error_message = decode(invoice_date,NULL,error_message||'Invoice Date is Null. ',error_message)
995 where rowid = l_rowid_tbl(indx);
996 commit;
997 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
998 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - validate invoice date.');
999 END IF;
1000 end if;
1001
1002 -- ---------------------------------
1003 -- update vendor_id
1004 -- ---------------------------------
1005 if l_rowid_tbl.count > 0 then
1006 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1007 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - populate vendor id.');
1008 END IF;
1009 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
1010 update okl_ext_billing_interface a
1011 set TAX_VENDOR_ID = (SELECT VENDOR_ID
1012 FROM po_vendors b
1013 WHERE b.VENDOR_TYPE_LOOKUP_CODE = 'TAX AUTHORITY'
1014 AND b.VENDOR_NAME = a.TAX_VENDOR_NAME)
1015 where rowid = l_rowid_tbl(indx)
1016 and stream_type_purpose = 'ACTUAL_PROPERTY_TAX';
1017 commit;
1018 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1019 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - populate vendor id.');
1020 END IF;
1021 end if;
1022
1023 -- ---------------------------------
1024 -- update vendor_site_id
1025 -- ---------------------------------
1026 if l_rowid_tbl.count > 0 then
1027 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1028 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - populate vendor site id.');
1029 END IF;
1030 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
1031 update okl_ext_billing_interface a
1032 set TAX_VENDOR_SITE_ID = (SELECT VENDOR_SITE_ID
1033 FROM po_vendor_sites_all b
1034 WHERE b.VENDOR_ID = a.TAX_VENDOR_ID
1035 AND b.VENDOR_SITE_CODE = a.TAX_VENDOR_SITE_NAME
1036 AND b.ORG_ID = a.ORG_ID ) --6144718
1037 where rowid = l_rowid_tbl(indx)
1038 and stream_type_purpose = 'ACTUAL_PROPERTY_TAX';
1039 commit;
1040 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1041 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - populate vendor site id.');
1042 END IF;
1043 end if;
1044
1045 -- --------------------------------------
1046 -- validate vendor_id or vendor site id
1047 -- for actual property tax
1048 -- --------------------------------------
1049 if l_rowid_tbl.count > 0 then
1050 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1051 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - flag invalid tax vendor id.');
1052 END IF;
1053 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
1054 update okl_ext_billing_interface a
1055 set trx_status_code = decode( TAX_VENDOR_ID,NULL,'ERROR', trx_status_code),
1056 error_message = decode( TAX_VENDOR_ID,NULL,error_message||'Invalid Tax Vendor Id. ', error_message)
1057 where rowid = l_rowid_tbl(indx)
1058 and stream_type_purpose = 'ACTUAL_PROPERTY_TAX';
1059 commit;
1060 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1061 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - flag invalid tax vendor id.');
1062 END IF;
1063 end if;
1064
1065 if l_rowid_tbl.count > 0 then
1066 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1067 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - flag invalid tax vendor site id.');
1068 END IF;
1069 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
1070 update okl_ext_billing_interface a
1071 set trx_status_code = decode( TAX_VENDOR_SITE_ID,NULL,'ERROR', trx_status_code),
1072 error_message = decode( TAX_VENDOR_SITE_ID,NULL,
1073 error_message||'Invalid Tax Vendor Site Id. ', error_message)
1074 where rowid = l_rowid_tbl(indx)
1075 and stream_type_purpose = 'ACTUAL_PROPERTY_TAX';
1076 commit;
1077 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1078 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - flag invalid tax vendor site id.');
1079 END IF;
1080 end if;
1081
1082 -- --------------------------------------------
1083 -- kle_id is mandatory for Actual Property Tax
1084 -- --------------------------------------------
1085 if l_rowid_tbl.count > 0 then
1086 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1087 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - asset id check for actual property tax.');
1088 END IF;
1089 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
1090 update okl_ext_billing_interface a
1091 set trx_status_code = decode( ASSET_ID,NULL,'ERROR', trx_status_code),
1092 error_message = decode( ASSET_ID,NULL,
1093 error_message||'Asset Id is mandatory for ACTUAL_PROPERTY_TAX. ', error_message)
1094 where rowid = l_rowid_tbl(indx)
1095 and stream_type_purpose = 'ACTUAL_PROPERTY_TAX';
1096 commit;
1097 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1098 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - asset id check for actual property tax.');
1099 END IF;
1100 end if;
1101
1102 -- --------------------------------------------
1103 -- prop_tax_applicable_yn update with right values
1104 -- for actual property tax
1105 -- --------------------------------------------
1106 if l_rowid_tbl.count > 0 then
1107 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1108 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - populate property tax applicable flag.');
1109 END IF;
1110 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
1111 update okl_ext_billing_interface a
1112 set prop_tax_applicable_yn = (
1113 SELECT NVL(rul.rule_information1,'N')
1114 FROM okc_rule_groups_b rgp,
1115 okc_rules_b rul
1116 WHERE rgp.id = rul.rgp_id
1117 AND rgp.rgd_code = 'LAASTX'
1118 AND rul.RULE_INFORMATION_CATEGORY = 'LAPRTX'
1119 AND rul.rule_information3 is not null
1120 AND rgp.dnz_chr_id = a.contract_id
1121 AND rgp.cle_id = a.asset_id)
1122 where rowid = l_rowid_tbl(indx)
1123 and stream_type_purpose = 'ACTUAL_PROPERTY_TAX';
1124 commit;
1125 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1126 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - populate property tax applicable flag.');
1127 END IF;
1128 end if;
1129
1130 -- --------------------------------------------
1131 -- validate prop_tax_applicable_yn
1132 -- --------------------------------------------
1133 if l_rowid_tbl.count > 0 then
1134 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1135 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - validate property tax applicable flag.');
1136 END IF;
1137 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
1138 update okl_ext_billing_interface a
1139 set trx_status_code = decode( prop_tax_applicable_yn,NULL,'ERROR','N','ERROR', trx_status_code),
1140 error_message = decode( prop_tax_applicable_yn,NULL,error_message
1141 ||'Property Tax Not Applicable for Asset. '
1142 ,'N',error_message
1143 ||'Property Tax Not Applicable for Asset. '
1144 ,error_message)
1145 where rowid = l_rowid_tbl(indx)
1146 and stream_type_purpose = 'ACTUAL_PROPERTY_TAX';
1147 commit;
1148 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1149 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - validate property tax applicable flag.');
1150 END IF;
1151 end if;
1152
1153 -- -----------------------------------------
1154 -- Update non-error rows to PASSED status
1155 -- -----------------------------------------
1156 if l_rowid_tbl.count > 0 then
1157 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1158 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - update successfully validated rows to status of PASSED.');
1159 END IF;
1160 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
1161 update okl_ext_billing_interface
1162 set trx_status_code = decode(trx_status_code,'SUBMITTED', 'PASSED',trx_status_code)
1163 where rowid = l_rowid_tbl(indx);
1164 commit;
1165 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1166 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - update successfully validated rows to status of PASSED.');
1167 END IF;
1168 end if; -- Update rows to passed status
1169
1170 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1171 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - validating batch number: '||g_batch_num);
1172 END IF;
1173 EXIT WHEN c_bill_chrgs%NOTFOUND;
1174 END LOOP;
1175 CLOSE c_bill_chrgs;
1176
1177 g_batch_num := 0;
1178 -- -------------------------------------
1179 -- process billing transaction records
1180 -- -------------------------------------
1181 OPEN bill_txn_csr( l_request_id );
1182 LOOP
1183 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1184 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - processing billing transactions batch number: '||g_batch_num);
1185 END IF;
1186 ext_bill_tbl.delete;
1187 tai_tbl.delete;
1188 til_tbl.delete;
1189 taitl_tbl.delete;
1190 tiltl_tbl.delete;
1191 upd_rowid_tbl.delete;
1192 header_id_tbl.delete;
1193 FETCH bill_txn_csr BULK COLLECT
1194 INTO ext_bill_tbl
1195 LIMIT L_FETCH_SIZE;
1196
1197 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1198 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'*=> number of records: '||ext_bill_tbl.count);
1199 END IF;
1200
1201 -- -------------------------------------
1202 -- process billing transaction records
1203 -- -------------------------------------
1204 if ext_bill_tbl.count > 0 then
1205 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1206 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - populating transaction header and line record.');
1207 END IF;
1208 for indx in ext_bill_tbl.first..ext_bill_tbl.last loop
1209 -- save rowid for later update
1210 upd_rowid_tbl(indx) := ext_bill_tbl(indx).ext_rowid;
1211
1212 tai_tbl(indx).khr_id := ext_bill_tbl(indx).contract_id;
1213 tai_tbl(indx).legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(ext_bill_tbl(indx).contract_id);
1214 tai_tbl(indx).date_invoiced := ext_bill_tbl(indx).INVOICE_DATE;
1215
1216 IF ext_bill_tbl(indx).amount > 0 THEN
1217 tai_tbl(indx).try_id := l_bill_try_id;
1218 ELSE
1219 tai_tbl(indx).try_id := l_cm_try_id;
1220 END IF;
1221
1222 tai_tbl(indx).date_entered := sysdate;
1223 tai_tbl(indx).trx_status_code := 'ERROR';
1224 tai_tbl(indx).amount := ext_bill_tbl(indx).amount;
1225 --added by kbbhavsa : Bug 5344799/5362220 : 27-June-06
1226 l_sty_desc := ext_bill_tbl(indx).STY_NAME;
1227 ---------------------------------------------
1228 -- Other Mandatory Columns
1229 ---------------------------------------------
1230 tai_tbl(indx).CREATION_DATE := SYSDATE;
1231 tai_tbl(indx).CREATED_BY := l_last_updated_by;
1232 tai_tbl(indx).LAST_UPDATE_DATE := SYSDATE;
1233 tai_tbl(indx).LAST_UPDATED_BY := l_last_updated_by;
1234 tai_tbl(indx).LAST_UPDATE_LOGIN := l_last_update_login;
1235 tai_tbl(indx).OBJECT_VERSION_NUMBER := 1;
1236 l_header_id := Okc_P_Util.raw_to_number(sys_guid());
1237 tai_tbl(indx).ID := l_header_id;
1238 tai_tbl(indx).trx_number := SUBSTR(TO_CHAR(l_header_id),-6);
1239 tai_tbl(indx).request_id := l_request_id;
1240 tai_tbl(indx).program_application_id := l_program_application_id;
1241 tai_tbl(indx).program_id := l_program_id;
1242 tai_tbl(indx).program_update_date := sysdate;
1243
1244 -- save tai_id's
1245 header_id_tbl(indx) := l_header_id;
1246 -------------------------------------------------------------------
1247 -- Process Invoice record for calling Billing Engine
1248 -------------------------------------------------------------------
1249 IF ext_bill_tbl(indx).amount > 0 THEN
1250 l_taiv_rec.try_id := l_bill_try_id;
1251 ELSE
1252 l_taiv_rec.try_id := l_cm_try_id;
1253 END IF;
1254 l_khr_id := ext_bill_tbl(indx).contract_id;
1255 l_taiv_rec.khr_id := l_khr_id;
1256 l_taiv_rec.date_invoiced := ext_bill_tbl(indx).INVOICE_DATE;
1257 l_taiv_rec.date_entered := sysdate;
1258 l_taiv_rec.amount := ext_bill_tbl(indx).amount;
1259 /* sosharma 10-Apr-07
1260 added parameters to be passed to billing procedure
1261 Start Changes*/
1262 l_taiv_rec.trx_status_code:='SUBMITTED';
1263 l_taiv_rec.okl_source_billing_trx:='THIRD_PARTY_IMPORT';
1264 l_taiv_rec.description := l_sty_desc;
1265 /* sosharma end changes */
1266 --other mandatory columns
1267 l_taiv_rec.CREATION_DATE := SYSDATE;
1268 l_taiv_rec.CREATED_BY := l_last_updated_by;
1269 l_taiv_rec.LAST_UPDATE_DATE := SYSDATE;
1270 l_taiv_rec.LAST_UPDATED_BY := l_last_updated_by;
1271 l_taiv_rec.LAST_UPDATE_LOGIN := l_last_update_login;
1272 l_taiv_rec.OBJECT_VERSION_NUMBER := 1;
1273 l_taiv_rec.request_id := l_request_id;
1274 l_taiv_rec.program_application_id := l_program_application_id;
1275 l_taiv_rec.program_id := l_program_id;
1276 l_taiv_rec.program_update_date := sysdate;
1277
1278 ---------------------------------------------
1279 -- Create TAI_TL records
1280 ---------------------------------------------
1281 l_taitl_cnt := taitl_tbl.count;
1282 FOR l_lang_rec IN get_languages LOOP
1283 l_taitl_cnt := l_taitl_cnt + 1;
1284 taitl_tbl(l_taitl_cnt).ID := l_header_id;
1285 taitl_tbl(l_taitl_cnt).LANGUAGE := l_lang_rec.language_code;
1286 taitl_tbl(l_taitl_cnt).SOURCE_LANG := USERENV('LANG');
1287 taitl_tbl(l_taitl_cnt).SFWT_FLAG := 'N';
1288 --taitl_tbl(l_taitl_cnt).DESCRIPTION := 'Imported Billing Transaction';
1289 taitl_tbl(l_taitl_cnt).DESCRIPTION := l_sty_desc; -- Added : bug 5362220 : prasjian
1290 taitl_tbl(l_taitl_cnt).CREATION_DATE := SYSDATE;
1291 taitl_tbl(l_taitl_cnt).CREATED_BY := l_last_updated_by;
1292 taitl_tbl(l_taitl_cnt).LAST_UPDATE_DATE := SYSDATE;
1293 taitl_tbl(l_taitl_cnt).LAST_UPDATED_BY := l_last_updated_by;
1294 taitl_tbl(l_taitl_cnt).LAST_UPDATE_LOGIN := l_last_update_login;
1295 END LOOP;
1296
1297 ---------------------------------------------
1298 -- Populate required columns
1299 ---------------------------------------------
1300 til_tbl(indx).kle_id := ext_bill_tbl(indx).asset_id;
1301 til_tbl(indx).line_number := 1;
1302 til_tbl(indx).tai_id := l_header_id;
1303 til_tbl(indx).sty_id := ext_bill_tbl(indx).sty_id;
1304 til_tbl(indx).inv_receiv_line_code := 'LINE';
1305 til_tbl(indx).amount := ext_bill_tbl(indx).amount;
1306 l_line_id := Okc_P_Util.raw_to_number(sys_guid());
1307 til_tbl(indx).ID := l_line_id;
1308 til_tbl(indx).OBJECT_VERSION_NUMBER := 1;
1309 til_tbl(indx).CREATION_DATE := SYSDATE;
1310 til_tbl(indx).CREATED_BY := l_last_updated_by;
1311 til_tbl(indx).LAST_UPDATE_DATE := SYSDATE;
1312 til_tbl(indx).LAST_UPDATED_BY := l_last_updated_by;
1313 til_tbl(indx).LAST_UPDATE_LOGIN := l_last_update_login;
1314 til_tbl(indx).request_id := l_request_id;
1315 til_tbl(indx).program_application_id := l_program_application_id;
1316 til_tbl(indx).program_id := l_program_id;
1317 til_tbl(indx).program_update_date := sysdate;
1318
1319 ---------------------------------------------
1320 -- Create TIL_TL records
1321 ---------------------------------------------
1322 l_tiltl_cnt := tiltl_tbl.count;
1323 FOR l_lang_rec IN get_languages LOOP
1324 l_tiltl_cnt := l_tiltl_cnt + 1;
1325 tiltl_tbl(l_tiltl_cnt).ID := l_line_id;
1326 tiltl_tbl(l_tiltl_cnt).LANGUAGE := l_lang_rec.language_code;
1327 tiltl_tbl(l_tiltl_cnt).SOURCE_LANG := USERENV('LANG');
1328 tiltl_tbl(l_tiltl_cnt).SFWT_FLAG := 'N';
1329 -- tiltl_tbl(l_tiltl_cnt).DESCRIPTION := 'Imported Billing Transaction';
1330 tiltl_tbl(l_tiltl_cnt).DESCRIPTION := l_sty_desc; -- Added : bug 5362220 : prasjian
1331 tiltl_tbl(l_tiltl_cnt).CREATION_DATE := SYSDATE;
1332 tiltl_tbl(l_tiltl_cnt).CREATED_BY := l_last_updated_by;
1333 tiltl_tbl(l_tiltl_cnt).LAST_UPDATE_DATE := SYSDATE;
1334 tiltl_tbl(l_tiltl_cnt).LAST_UPDATED_BY := l_last_updated_by;
1335 tiltl_tbl(l_tiltl_cnt).LAST_UPDATE_LOGIN := l_last_update_login;
1336 END LOOP;
1337
1338 ---------------------------------------------------------------------
1339 -- Process Invoice Line record for Billing Engine
1340 ---------------------------------------------------------------------
1341 l_tilv_rec.kle_id := ext_bill_tbl(indx).asset_id;
1342 l_tilv_rec.line_number := 1;
1343 l_tilv_rec.sty_id := ext_bill_tbl(indx).sty_id;
1344 l_tilv_rec.inv_receiv_line_code := 'LINE';
1345 l_tilv_rec.amount := ext_bill_tbl(indx).amount;
1346 --other mandatory columns
1347 l_tilv_rec.OBJECT_VERSION_NUMBER := 1;
1348 l_tilv_rec.CREATION_DATE := SYSDATE;
1349 l_tilv_rec.CREATED_BY := l_last_updated_by;
1350 l_tilv_rec.LAST_UPDATE_DATE := SYSDATE;
1351 l_tilv_rec.LAST_UPDATED_BY := l_last_updated_by;
1352 l_tilv_rec.LAST_UPDATE_LOGIN := l_last_update_login;
1353 l_tilv_rec.request_id := l_request_id;
1354 l_tilv_rec.program_application_id := l_program_application_id;
1355 l_tilv_rec.program_id := l_program_id;
1356 l_tilv_rec.program_update_date := sysdate;
1357
1358 /* sosharma 10-Apr-07
1359 added parameters to be passed to billing procedure
1360 Srart Changes*/
1361 l_tilv_rec.TXL_AR_LINE_NUMBER :=1;
1362 l_tilv_rec.description := l_sty_desc;
1363 /* sosharma end changes */
1364 ---------------------------------------------------------------------
1365 -- Process Line Detail record for Billing Engine
1366 ---------------------------------------------------------------------
1367 l_tilv_tbl(0) := l_tilv_rec;
1368
1369 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1370 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - call to billing centralized API.');
1371 END IF;
1372
1373 OKL_INTERNAL_BILLING_PVT.CREATE_BILLING_TRX(
1374 p_api_version
1375 ,p_init_msg_list
1376 ,x_return_status
1377 ,x_msg_count
1378 ,x_msg_data
1379 ,l_taiv_rec
1380 ,l_tilv_tbl
1381 ,l_tldv_tbl
1382 ,x_taiv_rec
1383 ,x_tilv_tbl
1384 ,x_tldv_tbl);
1385
1386 IF x_return_status <> 'S' THEN
1387 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1388 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- ERROR: Creating Billing Transactions using Billing Engine');
1389 END IF;
1390 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1391 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1392 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1393 RAISE Okl_Api.G_EXCEPTION_ERROR;
1394 END IF;
1395 END IF;
1396 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1397 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - call to billing centralized API.');
1398 END IF;
1399
1400 l_tilv_tbl.delete;
1401 l_tldv_tbl.delete;
1402 END LOOP; -- process records in ext_bill_tbl
1403
1404 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1405 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - populating transaction header and line record.');
1406 END IF;
1407 END IF; -- check if ext_bill_tbl has records
1408 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1409 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - processing billing transactions batch number: '||g_batch_num);
1410 END IF;
1411 EXIT WHEN bill_txn_csr%NOTFOUND;
1412 END LOOP; -- process transaction records
1413
1414 COMMIT;
1415 -- ----------------------------------------------
1416 -- clean up after processing transaction records
1417 -- ----------------------------------------------
1418 ext_bill_tbl.delete;
1419 tai_tbl.delete;
1420 til_tbl.delete;
1421 taitl_tbl.delete;
1422 tiltl_tbl.delete;
1423 upd_rowid_tbl.delete;
1424 header_id_tbl.delete;
1425
1426 -- ---------------------------------------
1427 -- end process billing transaction records
1428 -- ---------------------------------------
1429
1430 -- ---------------------------------------
1431 -- begin processing actual prop tax strm
1432 -- elements
1433 -- ---------------------------------------
1434 g_batch_num := 0;
1435 OPEN act_prop_tax_csr( l_request_id );
1436 LOOP
1437 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1438 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - processing actual property tax records batch number: '||g_batch_num);
1439 END IF;
1440
1441 ext_bill_tbl.delete;
1442 ptc_tbl.delete;
1443 ptctl_tbl.delete;
1444 stm_tbl.delete;
1445 sel_tbl.delete;
1446 upd_rowid_tbl.delete;
1447 upd_sel_tbl.delete;
1448 FETCH act_prop_tax_csr BULK COLLECT
1449 INTO ext_bill_tbl
1450 LIMIT L_FETCH_SIZE;
1451 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1452 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'*=> number of records: '||ext_bill_tbl.count);
1453 END IF;
1454 -- -------------------------------------
1455 -- process billing transaction records
1456 -- -------------------------------------
1457 IF ext_bill_tbl.count > 0 THEN
1458 -- -------------------------------------------
1459 -- loop thru ext_bill_tbl and process records
1460 -- -------------------------------------------
1461 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1462 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - populate property tax parameters record, stm rec and sel rec.');
1463 END IF;
1464 FOR indx in ext_bill_tbl.first..ext_bill_tbl.last
1465 LOOP
1466 -- save rowid for later update
1467 upd_rowid_tbl(indx) := ext_bill_tbl(indx).ext_rowid;
1468
1469 ptc_tbl(indx).id := Okc_P_Util.raw_to_number(sys_guid());
1470 ptc_tbl(indx).asset_id := ext_bill_tbl(indx).ASSET_ID;
1471 ptc_tbl(indx).asset_number := ext_bill_tbl(indx).ASSET_NUMBER;
1472 ptc_tbl(indx).khr_id := ext_bill_tbl(indx).contract_id;
1473 ptc_tbl(indx).contract_number := ext_bill_tbl(indx).CONTRACT_NUMBER;
1474 --rviriyal 05-Feb-2008 bug#6764803 Populated kle_id to view actual property tax in lease center.
1475 ptc_tbl(indx).kle_id := ext_bill_tbl(indx).ASSET_ID;
1476 --end 05-Feb-2008 bug#6764803
1477 ptc_tbl(indx).sty_name := ext_bill_tbl(indx).STY_NAME;
1478 ptc_tbl(indx).sty_id := ext_bill_tbl(indx).sty_id;
1479 ptc_tbl(indx).invoice_date := ext_bill_tbl(indx).INVOICE_DATE;
1480 ptc_tbl(indx).amount := ext_bill_tbl(indx).AMOUNT;
1481 ptc_tbl(indx).org_id := ext_bill_tbl(indx).ORG_ID;
1482 ptc_tbl(indx).JURSDCTN_TYPE := ext_bill_tbl(indx).JURSDCTN_TYPE;
1483 ptc_tbl(indx).JURSDCTN_NAME := ext_bill_tbl(indx).JURSDCTN_NAME;
1484 ptc_tbl(indx).MLRT_TAX := ext_bill_tbl(indx).MLRT_TAX;
1485 ptc_tbl(indx).TAX_VENDOR_ID := ext_bill_tbl(indx).TAX_VENDOR_ID;
1486 ptc_tbl(indx).TAX_VENDOR_NAME := ext_bill_tbl(indx).TAX_VENDOR_NAME;
1487 ptc_tbl(indx).TAX_VENDOR_SITE_ID := ext_bill_tbl(indx).TAX_VENDOR_SITE_ID;
1488 ptc_tbl(indx).TAX_VENDOR_SITE_NAME := ext_bill_tbl(indx).TAX_VENDOR_SITE_NAME;
1489 ptc_tbl(indx).CREATED_BY := l_last_updated_by;
1490 ptc_tbl(indx).CREATION_DATE := SYSDATE;
1491 ptc_tbl(indx).LAST_UPDATED_BY := l_last_updated_by;
1492 ptc_tbl(indx).LAST_UPDATE_DATE := SYSDATE;
1493 ptc_tbl(indx).LAST_UPDATE_LOGIN := l_last_update_login;
1494 ptc_tbl(indx).TAX_ASSESSMENT_DATE := ext_bill_tbl(indx).TAX_ASSESSMENT_DATE; --vpanwar FPbug#5891876
1495
1496 l_ptctl_cnt := ptctl_tbl.count;
1497 FOR l_lang_rec IN get_languages
1498 LOOP
1499 l_ptctl_cnt := l_ptctl_cnt + 1;
1500 ptctl_tbl(l_ptctl_cnt).ID := ptc_tbl(indx).id;
1501 ptctl_tbl(l_ptctl_cnt).LANGUAGE := l_lang_rec.language_code;
1502 ptctl_tbl(l_ptctl_cnt).SOURCE_LANG := USERENV('LANG');
1503 ptctl_tbl(l_ptctl_cnt).SFWT_FLAG := 'N';
1504 ptctl_tbl(l_ptctl_cnt).CREATION_DATE := SYSDATE;
1505 ptctl_tbl(l_ptctl_cnt).CREATED_BY := l_last_updated_by;
1506 ptctl_tbl(l_ptctl_cnt).LAST_UPDATE_DATE := SYSDATE;
1507 ptctl_tbl(l_ptctl_cnt).LAST_UPDATED_BY := l_last_updated_by;
1508 ptctl_tbl(l_ptctl_cnt).LAST_UPDATE_LOGIN := l_last_update_login;
1509 END LOOP;
1510
1511 -- check for actual property tax stream type
1512 l_actual_tax_count := 0;
1513 OPEN c_sty_count_csr ( ext_bill_tbl(indx).contract_id,
1514 ext_bill_tbl(indx).asset_id,
1515 ext_bill_tbl(indx).sty_id );
1516 FETCH c_sty_count_csr INTO l_actual_tax_count;
1517 CLOSE c_sty_count_csr;
1518
1519 IF l_actual_tax_count > 0 THEN -- check for actual property tax stream
1520 NULL;
1521 ELSE -- check for actual property tax stream
1522 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1523 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' ++ begin - create ACTUAL PROPERTY TAX stream.');
1524 END IF;
1525 -- -------------------------
1526 -- Null out records
1527 -- -------------------------
1528 l_stmtbl_cnt := stm_tbl.count;
1529 l_stmtbl_cnt := l_stmtbl_cnt + 1;
1530 l_stmv_rec := l_init_stmv_rec;
1531 lx_stmv_rec := l_init_stmv_rec;
1532
1533 OPEN c_tran_num_csr;
1534 FETCH c_tran_num_csr INTO stm_tbl(l_stmtbl_cnt).transaction_number;
1535 CLOSE c_tran_num_csr;
1536
1537 stm_tbl(l_stmtbl_cnt).sty_id := ext_bill_tbl(indx).sty_id;
1538 stm_tbl(l_stmtbl_cnt).khr_id := ext_bill_tbl(indx).contract_id;
1539 stm_tbl(l_stmtbl_cnt).kle_id := ext_bill_tbl(indx).asset_id;
1540 stm_tbl(l_stmtbl_cnt).sgn_code := 'MANL';
1541 stm_tbl(l_stmtbl_cnt).say_code := 'CURR';
1542 stm_tbl(l_stmtbl_cnt).active_yn := 'Y';
1543 stm_tbl(l_stmtbl_cnt).date_current := sysdate;
1544 stm_tbl(l_stmtbl_cnt).comments := 'ACTUAL PROPERTY TAX';
1545
1546 -- other mandatory columns
1547 stm_tbl(l_stmtbl_cnt).id := Okc_P_Util.raw_to_number(sys_guid());
1548 stm_tbl(l_stmtbl_cnt).OBJECT_VERSION_NUMBER := 1;
1549 stm_tbl(l_stmtbl_cnt).PROGRAM_ID := l_program_id;
1550 stm_tbl(l_stmtbl_cnt).REQUEST_ID := l_request_id;
1551 stm_tbl(l_stmtbl_cnt).PROGRAM_APPLICATION_ID := l_program_application_id;
1552 stm_tbl(l_stmtbl_cnt).PROGRAM_UPDATE_DATE := sysdate;
1553 stm_tbl(l_stmtbl_cnt).CREATED_BY := l_last_updated_by;
1554 stm_tbl(l_stmtbl_cnt).CREATION_DATE := sysdate;
1555 stm_tbl(l_stmtbl_cnt).LAST_UPDATED_BY := l_last_updated_by;
1556 stm_tbl(l_stmtbl_cnt).LAST_UPDATE_DATE := sysdate;
1557 stm_tbl(l_stmtbl_cnt).LAST_UPDATE_LOGIN := l_last_update_login;
1558
1559 -- insert stm records
1560 if stm_tbl.count > 0 then
1561 forall i in stm_tbl.first..stm_tbl.last
1562 insert into okl_streams
1563 values stm_tbl(i);
1564 commit;
1565 stm_tbl.delete;
1566 end if;
1567 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1568 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- end - create ACTUAL PROPERTY TAX stream.');
1569 END IF;
1570 END IF; -- end check for actual property tax stream
1571
1572 -- Create stream element actual property tax stream element
1573 -- Create Stream Element
1574 l_stm_id := NULL;
1575 OPEN c_stm_id_csr ( ext_bill_tbl(indx).contract_id,
1576 ext_bill_tbl(indx).asset_id,
1577 ext_bill_tbl(indx).sty_id );
1578 FETCH c_stm_id_csr INTO l_stm_id;
1579 CLOSE c_stm_id_csr;
1580
1581 -- --------------------------------
1582 -- fetch max se_line_number from
1583 -- sel table
1584 -- --------------------------------
1585 l_max_line_num := 0;
1586 OPEN max_line_num_csr ( l_stm_id );
1587 FETCH max_line_num_csr INTO l_max_line_num;
1588 CLOSE max_line_num_csr;
1589
1590 -- populate parent PK
1591 sel_tbl(indx).stm_id := l_stm_id;
1592
1593 -- --------------------------------
1594 -- To prevent constraint violation
1595 -- OKL_SEL_U2
1596 -- --------------------------------
1597 IF sel_tbl.exists(indx-1) then
1598 IF sel_tbl(indx-1).stm_id = l_stm_id then
1599 l_max_line_num := sel_tbl(indx-1).SE_LINE_NUMBER;
1600 END IF;
1601 END IF;
1602
1603 sel_tbl(indx).SE_LINE_NUMBER := NVL( l_max_line_num, 0 ) + 1;
1604 sel_tbl(indx).STREAM_ELEMENT_DATE := ext_bill_tbl(indx).INVOICE_DATE;
1605 sel_tbl(indx).AMOUNT := ext_bill_tbl(indx).AMOUNT;
1606 sel_tbl(indx).COMMENTS := 'ACTUAL PROPERTY TAX';
1607 sel_tbl(indx).SOURCE_ID := ptc_tbl(indx).id;
1608 sel_tbl(indx).SOURCE_TABLE := 'OKL_PROPERTY_TAX_V';
1609 -- Other columns
1610 sel_tbl(indx).id := Okc_P_Util.raw_to_number(sys_guid());
1611 -- save sel_id for update
1612 upd_sel_tbl(indx) := sel_tbl(indx).id;
1613
1614 sel_tbl(indx).OBJECT_VERSION_NUMBER := 1;
1615 sel_tbl(indx).PROGRAM_ID := l_program_id;
1616 sel_tbl(indx).REQUEST_ID := l_request_id;
1617 sel_tbl(indx).PROGRAM_APPLICATION_ID := l_program_application_id;
1618 sel_tbl(indx).PROGRAM_UPDATE_DATE := sysdate;
1619 sel_tbl(indx).CREATED_BY := l_last_updated_by;
1620 sel_tbl(indx).CREATION_DATE := sysdate;
1621 sel_tbl(indx).LAST_UPDATED_BY := l_last_updated_by;
1622 sel_tbl(indx).LAST_UPDATE_DATE := sysdate;
1623 sel_tbl(indx).LAST_UPDATE_LOGIN := l_last_update_login;
1624
1625 -- end creation of actual property tax stream element
1626 END LOOP; -- end processing ext table records
1627
1628 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1629 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - populate property tax parameters record, stm rec and sel rec.');
1630 END IF;
1631
1632 -- insert records into okl_property_tax_b
1633 if ptc_tbl.count > 0 then
1634 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1635 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' ++ begin - create ptc_b rec.');
1636 END IF;
1637 forall i in ptc_tbl.first..ptc_tbl.last
1638 insert into okl_property_tax_b
1639 values ptc_tbl(i);
1640 COMMIT;
1641 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1642 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- end - create ptc_b rec.');
1643 END IF;
1644 END IF;
1645
1646 -- insert records into okl_property_tax_tl
1647 if ptctl_tbl.count > 0 then
1648 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1649 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' ++ begin - create ptc_tl rec.');
1650 END IF;
1651 FORALL i in ptctl_tbl.first..ptctl_tbl.last
1652 INSERT INTO okl_property_tax_tl
1653 VALUES ptctl_tbl(i);
1654 COMMIT;
1655 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1656 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- end - create ptc_tl rec.');
1657 END IF;
1658 END IF;
1659
1660 -- update okl_ext_billing_interface with sel_id
1661 IF upd_rowid_tbl.count > 0 then
1662 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1663 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' ++ begin - update ext billing interface with sel_id.');
1664 END IF;
1665 FORALL i in upd_rowid_tbl.first..upd_rowid_tbl.last
1666 UPDATE okl_ext_billing_interface
1667 SET sel_id = upd_sel_tbl(i)
1668 WHERE rowid = upd_rowid_tbl(i);
1669 COMMIT;
1670 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1671 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- end - update ext billing interface with sel_id.');
1672 END IF;
1673 END IF;
1674
1675 -- insert sel records
1676 IF sel_tbl.count > 0 THEN
1677 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1678 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' ++ begin - create actual property tax stream elements.');
1679 END IF;
1680 forall i in sel_tbl.first..sel_tbl.last
1681 insert into okl_strm_elements
1682 values sel_tbl(i);
1683 COMMIT;
1684 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1685 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- end - create actual property tax stream elements.');
1686 END IF;
1687 END IF;
1688
1689 -- create payable invoices
1690 IF ( l_p_tax_applicable = 'YES' ) THEN
1691 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1692 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' ++ begin - create payable invoice.');
1693 END IF;
1694 l_acc_cmt_cnt := 0;
1695 FOR indx in ext_bill_tbl.first..ext_bill_tbl.last
1696 LOOP
1697 l_acc_cmt_cnt := l_acc_cmt_cnt + 1;
1698
1699 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1700 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- Creating Payable Invoice to Tax Authority');
1701 END IF;
1702 p_man_inv_rec.ipvs_id := ext_bill_tbl(indx).TAX_VENDOR_SITE_ID;
1703 p_man_inv_rec.khr_id := ext_bill_tbl(indx).contract_id;
1704 p_man_inv_rec.vendor_id := ext_bill_tbl(indx).TAX_VENDOR_ID;
1705 p_man_inv_rec.invoice_date := ext_bill_tbl(indx).INVOICE_DATE;
1706 p_man_inv_rec.amount := ext_bill_tbl(indx).AMOUNT;
1707 p_man_inv_rec.sty_id := ext_bill_tbl(indx).sty_id;
1708 p_man_inv_rec.sel_id := upd_sel_tbl(indx);
1709 -- for LE Uptake project 08-11-2006
1710 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(ext_bill_tbl(indx).contract_id);
1711 IF l_legal_entity_id IS NOT NULL THEN
1712 p_man_inv_rec.legal_entity_id := l_legal_entity_id;
1713 END IF;
1714 -- for LE Uptake project 08-11-2006
1715
1716 OKL_PAY_INVOICES_MAN_PUB.manual_entry(
1717 p_api_version
1718 ,p_init_msg_list
1719 ,x_return_status
1720 ,x_msg_count
1721 ,x_msg_data
1722 ,p_man_inv_rec
1723 ,x_man_inv_rec);
1724
1725 -- ---------------------------------------
1726 -- Create a payable Invoice
1727 -- ---------------------------------------
1728 IF x_return_status <> 'S' THEN
1729 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1730 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- ERROR: Creating Payable Invoice');
1731 END IF;
1732 UPDATE OKL_EXT_BILLING_INTERFACE
1733 SET trx_status_code = 'ERROR',
1734 ERROR_MESSAGE = ERROR_MESSAGE||'Error Creating Payable Invoice. '
1735 WHERE rowid = ext_bill_tbl(indx).ext_rowid;
1736 -- ------------------------------
1737 -- delete orphan record in PTC for
1738 -- referential integrity
1739 -- ------------------------------
1740 delete from OKL_PROPERTY_TAX_tl where id = ptc_tbl(indx).id;
1741 delete from OKL_PROPERTY_TAX_b where id = ptc_tbl(indx).id;
1742 delete from okl_strm_elements where id = upd_sel_tbl(indx);
1743 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1744 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- Deleting records from OKL_PROPERTY_TAX_tl and OKL_PROPERTY_TAX_b.');
1745 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- Deleting record from OKL_STRM_ELEMENTS.');
1746 END IF;
1747 END IF;
1748
1749 IF l_acc_cmt_cnt > 500 THEN
1750 COMMIT;
1751 l_acc_cmt_cnt := 0;
1752 END IF;
1753 END LOOP;
1754 COMMIT;
1755 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1756 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- end - create payable invoice.');
1757 END IF;
1758 END IF;
1759 -- end creation of payable invoices
1760
1761 -- Update records in the interface to PROCESSED status
1762 if upd_rowid_tbl.count > 0 then
1763 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1764 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' ++ begin - update status on okl_ext_billing_interface for successful records .');
1765 END IF;
1766 forall j in upd_rowid_tbl.first..upd_rowid_tbl.last
1767 update okl_ext_billing_interface
1768 set trx_status_code = decode(trx_status_code,'PASSED','PROCESSED',trx_status_code)
1769 where rowid = upd_rowid_tbl(j);
1770 COMMIT;
1771 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1772 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- end - update status on okl_ext_billing_interface for successful records .');
1773 END IF;
1774 END IF;
1775
1776 -- Start Bug 4520466
1777 for indx in ext_bill_tbl.first..ext_bill_tbl.last loop
1778 OKL_BILLING_CONTROLLER_PVT.track_next_bill_date( ext_bill_tbl(indx).contract_id );
1779 end loop;
1780 -- End Bug 4520466
1781 END IF; -- check if ext_bill_tbl has records
1782
1783 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1784 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - processing actual property tax records batch number: '||g_batch_num);
1785 END IF;
1786 EXIT WHEN act_prop_tax_csr%NOTFOUND;
1787 END LOOP; -- process transaction records
1788
1789 -- -----------------------------
1790 -- Print processing summary
1791 -- -----------------------------
1792 -- Success Count for actual property tax
1793 l_succ_apt_cnt := NULL;
1794 OPEN ext_apt_stat_csr( l_request_id, 'PROCESSED', 'ACTUAL_PROPERTY_TAX' );
1795 FETCH ext_apt_stat_csr INTO l_succ_apt_cnt;
1796 CLOSE ext_apt_stat_csr;
1797
1798 -- Error Count for actual property tax
1799 l_err_apt_cnt := NULL;
1800 OPEN ext_apt_stat_csr( l_request_id, 'ERROR', 'ACTUAL_PROPERTY_TAX' );
1801 FETCH ext_apt_stat_csr INTO l_err_apt_cnt;
1802 CLOSE ext_apt_stat_csr;
1803
1804 -- Success Count for NON-actual property tax
1805 l_succ_non_apt_cnt := NULL;
1806 OPEN ext_non_apt_stat_csr( l_request_id, 'PROCESSED', 'ACTUAL_PROPERTY_TAX' );
1807 FETCH ext_non_apt_stat_csr INTO l_succ_non_apt_cnt;
1808 CLOSE ext_non_apt_stat_csr;
1809
1810 -- Error Count for NON-actual property tax
1811 l_err_non_apt_cnt := NULL;
1812 OPEN ext_non_apt_stat_csr( l_request_id, 'ERROR', 'ACTUAL_PROPERTY_TAX' );
1813 FETCH ext_non_apt_stat_csr INTO l_err_non_apt_cnt;
1814 CLOSE ext_non_apt_stat_csr;
1815
1816 ----------------------------------------
1817 -- Get Operating unit name
1818 ----------------------------------------
1819 l_op_unit_name := NULL;
1820 OPEN op_unit_csr;
1821 FETCH op_unit_csr INTO l_op_unit_name;
1822 CLOSE op_unit_csr;
1823
1824 -- Start New Out File stmathew
1825 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 54, ' ')||'Oracle Lease and Finance Management'||LPAD(' ', 55, ' '));
1826 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
1827 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 53, ' ')||'Third Party Billing Import'||LPAD(' ', 53, ' '));
1828 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 54, ' ')||'------------------------'||LPAD(' ', 54, ' '));
1829 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
1830 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
1831 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Operating Unit: '||l_op_unit_name);
1832 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Request Id: '||l_request_id||LPAD(' ',74,' ') ||'Run Date: '||TO_CHAR(SYSDATE));
1833 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Currency: '||Okl_Accounting_Util.get_func_curr_code);
1834 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD('-', 132, '-'));
1835 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
1836 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
1837 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Processing Details:'||LPAD(' ', 113, ' '));
1838 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
1839 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Number of Successful Actual Property Tax records: '||l_succ_apt_cnt);
1840 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Number of Errored Actual Property Tax records: '||l_err_apt_cnt);
1841 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Number of Successful Non-Actual Property Tax records: '||l_succ_non_apt_cnt);
1842 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Number of Errored Non-Actual Property Tax records: '||l_err_non_apt_cnt);
1843 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Total records processed: '||g_total_rec_count);
1844 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
1845 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD('-', 132, '-'));
1846 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
1847
1848 -- End New Out File stmathew
1849 IF x_msg_count > 0 THEN
1850 FOR i IN 1..x_msg_count LOOP
1851 IF i = 1 THEN
1852 Fnd_File.PUT_LINE (Fnd_File.log,'Details of TAPI errors:'||LPAD(' ', 97, ' '));
1853 Fnd_File.PUT_LINE (Fnd_File.log,RPAD(' ', 132, ' '));
1854 END IF;
1855 Fnd_Msg_Pub.get (p_msg_index => i,
1856 p_encoded => 'F',
1857 p_data => lx_msg_data,
1858 p_msg_index_out => l_msg_index_out);
1859 Fnd_File.PUT_LINE (Fnd_File.log,TO_CHAR(i) || ': ' || lx_msg_data);
1860 END LOOP;
1861 END IF;
1862
1863 -- ---------------------------------------------------------
1864 -- print all error messages from okl_ext_billing_interface
1865 -- ---------------------------------------------------------
1866 Fnd_File.PUT_LINE (Fnd_File.log,RPAD(' ', 132, ' '));
1867 Fnd_File.PUT_LINE (Fnd_File.log,RPAD(' ', 132, ' '));
1868 Fnd_File.PUT_LINE (Fnd_File.log,'Contract Number'||LPAD(' ', 16, ' ')
1869 ||'Asset Number'||LPAD(' ', 19, ' ')
1870 ||'Stream Name'||LPAD(' ', 20, ' ')
1871 ||'Invoice Date'||' Error Message');
1872 Fnd_File.PUT_LINE (Fnd_File.log,'---------------'||LPAD(' ', 16, ' ')
1873 ||'------------'||LPAD(' ', 19, ' ')
1874 ||'-----------'||LPAD(' ', 20, ' ')
1875 ||'------------'||' -------------');
1876
1877 FOR error_msg_rec in error_msg_csr( l_request_id, 'ERROR' )
1878 LOOP
1879 Fnd_File.PUT_LINE (Fnd_File.log,error_msg_rec.contract_number||' '
1880 ||error_msg_rec.asset_number||' '
1881 ||error_msg_rec.sty_name||' '
1882 ||error_msg_rec.invoice_date||' '
1883 ||error_msg_rec.error_message);
1884 END LOOP;
1885 Fnd_File.PUT_LINE (Fnd_File.log,RPAD(' ', 132, ' '));
1886 Fnd_File.PUT_LINE (Fnd_File.log,RPAD(' ', 132, ' '));
1887 Fnd_File.PUT_LINE (Fnd_File.log,RPAD(' ', 132, ' '));
1888 -- -------------------------------
1889 -- End Print processing Summary
1890 -- -------------------------------
1891
1892 FND_FILE.PUT_LINE (FND_FILE.log, '================================================================');
1893 FND_FILE.PUT_LINE (FND_FILE.log, ' *** END PROCESSING THIRD PARTY BILLING RECORDS ***');
1894 FND_FILE.PUT_LINE (FND_FILE.log, '================================================================');
1895
1896 ------------------------------------------------------------
1897 -- End processing
1898 ------------------------------------------------------------
1899 IF (L_DEBUG_ENABLED='Y' and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1900 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1901 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'End (-)');
1902 END IF;
1903 END IF;
1904
1905 Okl_Api.END_ACTIVITY (
1906 x_msg_count => x_msg_count,
1907 x_msg_data => x_msg_data);
1908 EXCEPTION
1909 ------------------------------------------------------------
1910 -- Exception handling
1911 ------------------------------------------------------------
1912 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1913 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=>ERROR: '||SQLERRM);
1914 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
1915 p_api_name => l_api_name,
1916 p_pkg_name => G_PKG_NAME,
1917 p_exc_name => 'Okl_Api.G_RET_STS_ERROR',
1918 x_msg_count => x_msg_count,
1919 x_msg_data => x_msg_data,
1920 p_api_type => '_PVT');
1921 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1922 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=>ERROR: '||SQLERRM);
1923 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
1924 p_api_name => l_api_name,
1925 p_pkg_name => G_PKG_NAME,
1926 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
1927 x_msg_count => x_msg_count,
1928 x_msg_data => x_msg_data,
1929 p_api_type => '_PVT');
1930 WHEN OTHERS THEN
1931 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=>ERROR: '||SQLERRM);
1932 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
1933 p_api_name => l_api_name,
1934 p_pkg_name => G_PKG_NAME,
1935 p_exc_name => 'OTHERS',
1936 x_msg_count => x_msg_count,
1937 x_msg_data => x_msg_data,
1938 p_api_type => '_PVT');
1939 END BILLING_CHARGES;
1940
1941 END OKL_EXT_BILLING_CHARGES_PVT;