[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.26.12010000.2 2008/12/12 20:26:24 cklee 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 BEGIN
491 L_DEBUG_ENABLED := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
492
493 IF (L_DEBUG_ENABLED='Y' and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
494 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
495 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Begin(+)');
496 END IF;
497 END IF;
498
499 ------------------------------------------------------------
500 -- Start processing
501 ------------------------------------------------------------
502 x_return_status := OKL_API.G_RET_STS_SUCCESS;
503
504 l_return_status := OKL_API.START_ACTIVITY(
505 p_api_name => l_api_name
506 ,p_pkg_name => G_PKG_NAME
507 ,p_init_msg_list => p_init_msg_list
508 ,l_api_version => l_api_version
509 ,p_api_version => p_api_version
510 ,p_api_type => '_PVT'
511 ,x_return_status => l_return_status);
512
513 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
514 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
515 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
516 RAISE Okl_Api.G_EXCEPTION_ERROR;
517 END IF;
518
519 ------------------------------------------------------------
520 -- Process every external billing line for billing import
521 ------------------------------------------------------------
522 FND_FILE.PUT_LINE (FND_FILE.log, '================================================================');
523 FND_FILE.PUT_LINE (FND_FILE.log, ' *** START PROCESSING THIRD PARTY BILLING RECORDS ***');
524 FND_FILE.PUT_LINE (FND_FILE.log, '================================================================');
525
526 -- -----------------------------------------------------
527 -- Fetch property tax
528 -- -----------------------------------------------------
529 l_p_tax_applicable := NULL;
530 OPEN p_tax_options_csr;
531 FETCH p_tax_options_csr INTO l_p_tax_applicable;
532 CLOSE p_tax_options_csr;
533
534 -- -----------------------------------------------------
535 -- Get transaction type
536 -- -----------------------------------------------------
537 l_trx_type_name := 'Billing';
538 l_bill_try_id := NULL;
539 OPEN get_try_id_csr (l_trx_type_name, l_trx_type_lang);
540 FETCH get_try_id_csr INTO l_bill_try_id;
541 CLOSE get_try_id_csr;
542
543 l_trx_type_name := 'Credit Memo';
544 l_cm_try_id := NULL;
545 OPEN get_try_id_csr (l_trx_type_name, l_trx_type_lang);
546 FETCH get_try_id_csr INTO l_cm_try_id;
547 CLOSE get_try_id_csr;
548 -- --------------------------------------------
549 -- Print error message and stop processing
550 -- --------------------------------------------
551 if l_bill_try_id is null or l_cm_try_id is null then
552 FND_FILE.PUT_LINE (FND_FILE.log, '********************** ERROR **********************');
553 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'BILLING or CREDIT MEMO transaction type is invalid.');
554 FND_FILE.PUT_LINE (FND_FILE.log, '********************** ERROR **********************');
555
556 RAISE OKL_API.G_EXCEPTION_ERROR;
557 END IF;
558
559 if l_request_id = -1 then
560 l_request_id := NULL;
561 end if;
562
563 if l_program_application_id = -1 then
564 l_program_application_id := NULL;
565 end if;
566
567 if l_program_id = -1 then
568 l_program_id := NULL;
569 end if;
570
571 if l_request_id is null then
572 FND_FILE.PUT_LINE (FND_FILE.log, '********************** ERROR **********************');
573 FND_FILE.PUT_LINE (FND_FILE.log,
574 'Cannot determine request Id from profile. The function Fnd_Global.CONC_REQUEST_ID returns -1.');
575 FND_FILE.PUT_LINE (FND_FILE.log, '********************** ERROR **********************');
576 RAISE OKL_API.G_EXCEPTION_ERROR;
577 END IF;
578
579 -- -------------------------------
580 -- Process each cursor record
581 -- -------------------------------
582 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
583 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'*** Start Validation ***');
584 END IF;
585
586 g_batch_num := 0;
587
588 OPEN c_bill_chrgs;
589 LOOP
590 g_batch_num := g_batch_num + 1;
591 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
592 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - validating batch number: '||g_batch_num);
593 END IF;
594
595 l_rowid_tbl.delete;
596 l_contract_tbl.delete;
597 l_sty_tbl.delete;
598 l_asset_num.delete;
599 l_inv_date.delete;
600 l_amt_tbl.delete;
601 l_curr_code_tbl.delete;
602 l_strm_purpose_tbl.delete;
603
604 FETCH c_bill_chrgs BULK COLLECT INTO
605 l_rowid_tbl,
606 l_contract_tbl,
607 l_sty_tbl,
608 l_asset_num,
609 l_inv_date,
610 l_amt_tbl,
611 l_curr_code_tbl,
612 l_strm_purpose_tbl
613 LIMIT L_FETCH_SIZE;
614
615 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
616 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'*=> number of records: '||l_rowid_tbl.count);
617 END IF;
618
619 -- update global total rec count
620 g_total_rec_count := g_total_rec_count + l_rowid_tbl.count;
621
622 -- -------------------------------------------------------
623 -- update std who parameters in okl_ext_billing_interface
624 -- -------------------------------------------------------
625 if l_rowid_tbl.count > 0 then
626 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
627 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - populate std who columns.');
628 END IF;
629 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
630 update okl_ext_billing_interface
631 set CREATED_BY = l_last_updated_by,
632 CREATION_DATE = sysdate,
633 LAST_UPDATED_BY = l_last_updated_by,
634 LAST_UPDATE_DATE = sysdate,
635 LAST_UPDATE_LOGIN = l_last_update_login,
636 REQUEST_ID = l_request_id,
637 PROGRAM_APPLICATION_ID = l_program_application_id,
638 PROGRAM_ID = l_program_id,
639 PROGRAM_UPDATE_DATE = sysdate
640 where rowid = l_rowid_tbl(indx);
641 COMMIT;
642 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
643 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - populate std who columns.');
644 END IF;
645 END IF;
646
647 IF l_strm_purpose_tbl.count > 0 THEN
648 -- ---------------------------------
649 -- Validate Stream Type Purpose
650 -- ---------------------------------
651 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
652 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - validate stream type purpose.');
653 END IF;
654 forall indx in l_strm_purpose_tbl.first..l_strm_purpose_tbl.last
655 update okl_ext_billing_interface
656 set trx_status_code =
657 decode(l_strm_purpose_tbl(indx),NULL,'ERROR',
658 -- 'RENT','ERROR',
659 -- 'ADVANCE_RENT','ERROR',
660 -- 'INTEREST_PAYMENT','ERROR',
661 -- 'PRINCIPAL_PAYMENT','ERROR',
662 trx_status_code
663 )
664 ,error_message =
665 decode(l_strm_purpose_tbl(indx),NULL,'Stream Type purpose cannot be null. '--,
666 -- 'RENT','STREAM PURPOSE RENT is not supported. ',
667 -- 'ADVANCE_RENT','STREAM PURPOSE ADVANCE RENT is not supported. ',
668 -- 'INTEREST_PAYMENT','STREAM PURPOSE INTEREST PAYMENT is not supported. ',
669 -- 'PRINCIPAL_PAYMENT','STREAM PURPOSE PRINCIPAL PAYMENT is not supported. '
670 )
671 where rowid = l_rowid_tbl(indx);
672 COMMIT;
673 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
674 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - validate stream type purpose.');
675 END IF;
676 END IF; -- stream type purpose validation
677
678 -- ---------------------------------
679 -- Validate Amount
680 -- ---------------------------------
681 if l_amt_tbl.count > 0 then
682 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
683 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - validate amount.');
684 END IF;
685 forall indx in l_amt_tbl.first..l_amt_tbl.last
686 update okl_ext_billing_interface
687 set trx_status_code = decode(l_amt_tbl(indx),NULL, 'ERROR',trx_status_code),
688 error_message =
689 decode(l_amt_tbl(indx),NULL, error_message||'Amount Cannot be null. ',
690 0, error_message||'Amount must be non-zero. ',
691 error_message)
692 where rowid = l_rowid_tbl(indx);
693 COMMIT;
694 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
695 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - validate amount.');
696 END IF;
697 END IF; -- Update rows to passed status
698
699 -- ---------------------------------
700 -- Validate Contract
701 -- ---------------------------------
702 if l_contract_tbl.count > 0 then
703 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
704 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - validate contract number.');
705 END IF;
706 FORALL indx in l_contract_tbl.first..l_contract_tbl.last
707 update okl_ext_billing_interface a
708 set contract_id = (select id
709 from okc_k_headers_b b
710 where b.contract_number = a.contract_number)
711 where rowid = l_rowid_tbl(indx);
712 COMMIT;
713 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
714 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - validate contract number.');
715 END IF;
716 END IF;
717
718 -- ---------------------------------
719 -- Validate Asset
720 -- ---------------------------------
721 -- PAGARG - Bug# 6884934 - Modified Start
722 -- Modified to enhance the logic when ASSET_ID is also part of import record
723 -- The following cases are handled by the BULK UPDATION CODE
724 -- CASE 1 - When ASSET_NUMBER is NULL, the record is not updated
725 -- CASE 2 - When ASSET_NUMBER present and ASSET_ID present, code validates and updates the
726 -- NULL if the ASSET_ID doesnot correspond to the ASSET_NUMBER
727 -- CASE 3 - When ASSET_NUMBER present and ASSET_ID is NULL, code checks if there are multiple
728 -- contract lines with same line name and errors accordingly
729 IF l_asset_num.count > 0 THEN
730 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
731 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - validate asset number.');
732 END IF;
733 BEGIN
734 FORALL indx IN l_asset_num.FIRST..l_asset_num.LAST SAVE EXCEPTIONS
735 UPDATE okl_ext_billing_interface a
736 SET asset_id = (SELECT id
737 FROM OKL_K_LINES_FULL_V b
738 -- NVL is for imported contracts where
739 -- Service Name is NULL in OKC_K_LINES_TL
740 WHERE NVL(b.name, a.ASSET_NUMBER) = a.ASSET_NUMBER
741 AND b.ID = NVL(A.ASSET_ID, b.ID)
742 AND b.dnz_chr_id = a.contract_id)
743 WHERE rowid = l_rowid_tbl(indx)
744 AND a.ASSET_NUMBER IS NOT NULL;
745 EXCEPTION
746 WHEN x_multiple_line_error THEN
747 -- This exception will be thrown if there are similar service lines
748 -- on the same contract and ASSET_ID is not present in the record
749 -- OKL_EXT_BILLING_INTERFACE. In this case the select query returns
750 -- all the service lines based on the Service Name
751
752 -- Update the status of the record as ERROR with error message
753 FOR err_indx IN 1 .. SQL%BULK_EXCEPTIONS.COUNT
754 LOOP
755 l_temp_index := SQL%BULK_EXCEPTIONS(err_indx).ERROR_INDEX;
756 UPDATE OKL_EXT_BILLING_INTERFACE B_INT
757 SET TRX_STATUS_CODE = 'ERROR'
758 , ERROR_MESSAGE = ERROR_MESSAGE || 'Multiple contract lines with same name. Please also provide Contract Line ID.'
759 WHERE rowid = l_rowid_tbl(l_temp_index);
760 END LOOP;
761 END; -- end of ASSET_ID updation
762 COMMIT;
763 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
764 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - validate asset number.');
765 END IF;
766 END IF;
767 -- PAGARG - Bug# 6884934 - Modified End
768
769 -- ---------------------------------
770 -- Validate Stream Type Name
771 -- ---------------------------------
772 IF l_sty_tbl.count > 0 THEN
773 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
774 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - validate stream type assignment to a stream generation template.');
775 END IF;
776 forall indx in l_sty_tbl.first..l_sty_tbl.last
777 update okl_ext_billing_interface a
778 set sty_id = (SELECT GTLV.PRIMARY_STY_ID STY_ID
779 FROM
780 OKL_ST_GEN_TMPT_LNS GTLV,
781 OKL_ST_GEN_TEMPLATES GTTV,
782 OKL_ST_GEN_TMPT_SETS GTSV,
783 OKL_AE_TMPT_SETS AES,
784 OKL_PRODUCTS PDT,
785 OKL_STRM_TYPE_V STY,
786 okl_k_headers khr,
787 okc_k_headers_b chr
788 WHERE
789 GTLV.GTT_ID = GTTV.ID AND
790 GTTV.GTS_ID = GTSV.ID AND
791 GTTV.TMPT_STATUS = 'ACTIVE' AND
792 GTSV.ID = AES.GTS_ID AND
793 AES.ID = PDT.AES_ID AND
794 GTLV.PRIMARY_STY_ID = STY.ID AND
795 GTLV.PRIMARY_YN = 'Y' and
796 -- added stmathew
797 khr.id = chr.id and
798 khr.pdt_id = pdt.id and
799 GTTV.start_date <= chr.start_date and
800 (GTTV.end_date >= chr.start_date or GTTV.end_date is null ) and
801 sty.billable_yn = 'Y' and
802 khr.id = a.CONTRACT_ID and
803 sty.name = a.STY_NAME and
804 sty.stream_type_purpose = a.STREAM_TYPE_PURPOSE
805 UNION ALL
806 SELECT
807 DISTINCT
808 GTLV.DEPENDENT_STY_ID STY_ID
809 FROM
810 OKL_ST_GEN_TMPT_LNS GTLV,
811 OKL_ST_GEN_TEMPLATES GTTV,
812 OKL_ST_GEN_TMPT_SETS GTSV,
813 OKL_AE_TMPT_SETS AES,
814 OKL_PRODUCTS PDT,
815 OKL_STRM_TYPE_V STY,
816 okl_k_headers khr,
817 okc_k_headers_b chr
818 WHERE
819 GTLV.GTT_ID = GTTV.ID AND
820 GTTV.GTS_ID = GTSV.ID AND
821 GTTV.TMPT_STATUS = 'ACTIVE' AND
822 GTSV.ID = AES.GTS_ID AND
823 AES.ID = PDT.AES_ID AND
824 GTLV.DEPENDENT_STY_ID = STY.ID AND
825 (GTLV.PRIMARY_YN = 'N' or GTLV.PRIMARY_YN is null) AND
826 -- added stmathew
827 khr.id = chr.id and
828 khr.pdt_id = pdt.id and
829 GTTV.start_date <= chr.start_date and
830 (GTTV.end_date >= chr.start_date or GTTV.end_date is null ) and
831 sty.billable_yn = 'Y' and
832 khr.id = a.CONTRACT_ID and
833 sty.name = a.STY_NAME and
834 sty.stream_type_purpose = a.STREAM_TYPE_PURPOSE)
835 WHERE rowid = l_rowid_tbl(indx);
836 COMMIT;
837 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
838 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - validate stream type assignment to a stream generation template.');
839 END IF;
840 END IF;
841
842 -- ---------------------------------
843 -- Validate khr_id
844 -- ---------------------------------
845 if l_rowid_tbl.count > 0 then
846 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
847 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - flag invalid contracts.');
848 END IF;
849 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
850 update okl_ext_billing_interface
851 set trx_status_code = decode(contract_id,NULL,'ERROR',trx_status_code),
852 error_message = decode(contract_id,NULL,error_message||'Invalid Contract. ',error_message)
853 where rowid = l_rowid_tbl(indx);
854 commit;
855 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
856 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - flag invalid contracts.');
857 END IF;
858 END IF;
859
860 -- PAGARG - Bug# 6884934 - Modified Start
861 -- ---------------------------------
862 -- Validate kle_id
863 -- Errors if
864 -- Line ID doesnot match the Line Name
865 -- Line ID is not correct for the contract
866 -- Line Name is not correct for the contract
867 -- ---------------------------------
868 IF l_rowid_tbl.count > 0 THEN
869 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
870 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - flag invalid lines.');
871 END IF;
872 FORALL indx in l_rowid_tbl.first..l_rowid_tbl.last
873 UPDATE OKL_EXT_BILLING_INTERFACE a
874 SET TRX_STATUS_CODE = 'ERROR',
875 ERROR_MESSAGE = ERROR_MESSAGE||'Invalid Line name or Line Id.'
876 WHERE rowid = l_rowid_tbl(indx)
877 AND (a.ASSET_NUMBER IS NOT NULL OR a.ASSET_ID IS NOT NULL)
878 AND NOT EXISTS ( SELECT 1
879 FROM OKL_K_LINES_FULL_V LNS
880 WHERE LNS.ID = a.ASSET_ID
881 -- IS NULL condition is useful for imported contracts
882 -- where Service Name is NULL in OKC_K_LINES_TL
883 AND ( LNS.NAME IS NULL OR LNS.NAME = NVL(a.ASSET_NUMBER, LNS.NAME))
884 AND LNS.DNZ_CHR_ID = a.CONTRACT_ID);
885 COMMIT;
886 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
887 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - flag invalid lines.');
888 END IF;
889 END IF;
890 -- PAGARG - Bug# 6884934 - Modified End
891
892 -- ---------------------------------
893 -- Validate sty_id
894 -- ---------------------------------
895 if l_rowid_tbl.count > 0 then
896 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
897 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - flag invalid stream types.');
898 END IF;
899 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
900 update okl_ext_billing_interface
901 set trx_status_code = decode(sty_id,NULL,'ERROR',trx_status_code),
902 error_message = decode(sty_id,NULL,
903 error_message||'Stream type is invalid, non-billable or unattached to a template. ',
904 error_message)
905 where rowid = l_rowid_tbl(indx);
906 commit;
907 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
908 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - flag invalid stream types.');
909 END IF;
910 end if;
911
912 -- ---------------------------------
913 -- Validate invoice_date
914 -- ---------------------------------
915 if l_rowid_tbl.count > 0 then
916 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
917 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - validate invoice date.');
918 END IF;
919 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
920 update okl_ext_billing_interface
921 set trx_status_code = decode(invoice_date,NULL,'ERROR',trx_status_code),
922 error_message = decode(invoice_date,NULL,error_message||'Invoice Date is Null. ',error_message)
923 where rowid = l_rowid_tbl(indx);
924 commit;
925 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
926 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - validate invoice date.');
927 END IF;
928 end if;
929
930 -- ---------------------------------
931 -- update vendor_id
932 -- ---------------------------------
933 if l_rowid_tbl.count > 0 then
934 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
935 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - populate vendor id.');
936 END IF;
937 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
938 update okl_ext_billing_interface a
939 set TAX_VENDOR_ID = (SELECT VENDOR_ID
940 FROM po_vendors b
941 WHERE b.VENDOR_TYPE_LOOKUP_CODE = 'TAX AUTHORITY'
942 AND b.VENDOR_NAME = a.TAX_VENDOR_NAME)
943 where rowid = l_rowid_tbl(indx)
944 and stream_type_purpose = 'ACTUAL_PROPERTY_TAX';
945 commit;
946 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
947 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - populate vendor id.');
948 END IF;
949 end if;
950
951 -- ---------------------------------
952 -- update vendor_site_id
953 -- ---------------------------------
954 if l_rowid_tbl.count > 0 then
955 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
956 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - populate vendor site id.');
957 END IF;
958 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
959 update okl_ext_billing_interface a
960 set TAX_VENDOR_SITE_ID = (SELECT VENDOR_SITE_ID
961 FROM po_vendor_sites_all b
962 WHERE b.VENDOR_ID = a.TAX_VENDOR_ID
963 AND b.VENDOR_SITE_CODE = a.TAX_VENDOR_SITE_NAME
964 AND b.ORG_ID = a.ORG_ID ) --6144718
965 where rowid = l_rowid_tbl(indx)
966 and stream_type_purpose = 'ACTUAL_PROPERTY_TAX';
967 commit;
968 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
969 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - populate vendor site id.');
970 END IF;
971 end if;
972
973 -- --------------------------------------
974 -- validate vendor_id or vendor site id
975 -- for actual property tax
976 -- --------------------------------------
977 if l_rowid_tbl.count > 0 then
978 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
979 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - flag invalid tax vendor id.');
980 END IF;
981 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
982 update okl_ext_billing_interface a
983 set trx_status_code = decode( TAX_VENDOR_ID,NULL,'ERROR', trx_status_code),
984 error_message = decode( TAX_VENDOR_ID,NULL,error_message||'Invalid Tax Vendor Id. ', error_message)
985 where rowid = l_rowid_tbl(indx)
986 and stream_type_purpose = 'ACTUAL_PROPERTY_TAX';
987 commit;
988 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
989 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - flag invalid tax vendor id.');
990 END IF;
991 end if;
992
993 if l_rowid_tbl.count > 0 then
994 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
995 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - flag invalid tax vendor site id.');
996 END IF;
997 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
998 update okl_ext_billing_interface a
999 set trx_status_code = decode( TAX_VENDOR_SITE_ID,NULL,'ERROR', trx_status_code),
1000 error_message = decode( TAX_VENDOR_SITE_ID,NULL,
1001 error_message||'Invalid Tax Vendor Site Id. ', error_message)
1002 where rowid = l_rowid_tbl(indx)
1003 and stream_type_purpose = 'ACTUAL_PROPERTY_TAX';
1004 commit;
1005 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1006 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - flag invalid tax vendor site id.');
1007 END IF;
1008 end if;
1009
1010 -- --------------------------------------------
1011 -- kle_id is mandatory for Actual Property Tax
1012 -- --------------------------------------------
1013 if l_rowid_tbl.count > 0 then
1014 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1015 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - asset id check for actual property tax.');
1016 END IF;
1017 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
1018 update okl_ext_billing_interface a
1019 set trx_status_code = decode( ASSET_ID,NULL,'ERROR', trx_status_code),
1020 error_message = decode( ASSET_ID,NULL,
1021 error_message||'Asset Id is mandatory for ACTUAL_PROPERTY_TAX. ', error_message)
1022 where rowid = l_rowid_tbl(indx)
1023 and stream_type_purpose = 'ACTUAL_PROPERTY_TAX';
1024 commit;
1025 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1026 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - asset id check for actual property tax.');
1027 END IF;
1028 end if;
1029
1030 -- --------------------------------------------
1031 -- prop_tax_applicable_yn update with right values
1032 -- for actual property tax
1033 -- --------------------------------------------
1034 if l_rowid_tbl.count > 0 then
1035 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1036 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - populate property tax applicable flag.');
1037 END IF;
1038 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
1039 update okl_ext_billing_interface a
1040 set prop_tax_applicable_yn = (
1041 SELECT NVL(rul.rule_information1,'N')
1042 FROM okc_rule_groups_b rgp,
1043 okc_rules_b rul
1044 WHERE rgp.id = rul.rgp_id
1045 AND rgp.rgd_code = 'LAASTX'
1046 AND rul.RULE_INFORMATION_CATEGORY = 'LAPRTX'
1047 AND rul.rule_information3 is not null
1048 AND rgp.dnz_chr_id = a.contract_id
1049 AND rgp.cle_id = a.asset_id)
1050 where rowid = l_rowid_tbl(indx)
1051 and stream_type_purpose = 'ACTUAL_PROPERTY_TAX';
1052 commit;
1053 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1054 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - populate property tax applicable flag.');
1055 END IF;
1056 end if;
1057
1058 -- --------------------------------------------
1059 -- validate prop_tax_applicable_yn
1060 -- --------------------------------------------
1061 if l_rowid_tbl.count > 0 then
1062 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1063 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - validate property tax applicable flag.');
1064 END IF;
1065 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
1066 update okl_ext_billing_interface a
1067 set trx_status_code = decode( prop_tax_applicable_yn,NULL,'ERROR','N','ERROR', trx_status_code),
1068 error_message = decode( prop_tax_applicable_yn,NULL,error_message
1069 ||'Property Tax Not Applicable for Asset. '
1070 ,'N',error_message
1071 ||'Property Tax Not Applicable for Asset. '
1072 ,error_message)
1073 where rowid = l_rowid_tbl(indx)
1074 and stream_type_purpose = 'ACTUAL_PROPERTY_TAX';
1075 commit;
1076 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1077 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - validate property tax applicable flag.');
1078 END IF;
1079 end if;
1080
1081 -- -----------------------------------------
1082 -- Update non-error rows to PASSED status
1083 -- -----------------------------------------
1084 if l_rowid_tbl.count > 0 then
1085 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1086 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - update successfully validated rows to status of PASSED.');
1087 END IF;
1088 forall indx in l_rowid_tbl.first..l_rowid_tbl.last
1089 update okl_ext_billing_interface
1090 set trx_status_code = decode(trx_status_code,'SUBMITTED', 'PASSED',trx_status_code)
1091 where rowid = l_rowid_tbl(indx);
1092 commit;
1093 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1094 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - update successfully validated rows to status of PASSED.');
1095 END IF;
1096 end if; -- Update rows to passed status
1097
1098 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1099 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - validating batch number: '||g_batch_num);
1100 END IF;
1101 EXIT WHEN c_bill_chrgs%NOTFOUND;
1102 END LOOP;
1103 CLOSE c_bill_chrgs;
1104
1105 g_batch_num := 0;
1106 -- -------------------------------------
1107 -- process billing transaction records
1108 -- -------------------------------------
1109 OPEN bill_txn_csr( l_request_id );
1110 LOOP
1111 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1112 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - processing billing transactions batch number: '||g_batch_num);
1113 END IF;
1114 ext_bill_tbl.delete;
1115 tai_tbl.delete;
1116 til_tbl.delete;
1117 taitl_tbl.delete;
1118 tiltl_tbl.delete;
1119 upd_rowid_tbl.delete;
1120 header_id_tbl.delete;
1121 FETCH bill_txn_csr BULK COLLECT
1122 INTO ext_bill_tbl
1123 LIMIT L_FETCH_SIZE;
1124
1125 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1126 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'*=> number of records: '||ext_bill_tbl.count);
1127 END IF;
1128
1129 -- -------------------------------------
1130 -- process billing transaction records
1131 -- -------------------------------------
1132 if ext_bill_tbl.count > 0 then
1133 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1134 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - populating transaction header and line record.');
1135 END IF;
1136 for indx in ext_bill_tbl.first..ext_bill_tbl.last loop
1137 -- save rowid for later update
1138 upd_rowid_tbl(indx) := ext_bill_tbl(indx).ext_rowid;
1139
1140 tai_tbl(indx).khr_id := ext_bill_tbl(indx).contract_id;
1141 tai_tbl(indx).legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(ext_bill_tbl(indx).contract_id);
1142 tai_tbl(indx).date_invoiced := ext_bill_tbl(indx).INVOICE_DATE;
1143
1144 IF ext_bill_tbl(indx).amount > 0 THEN
1145 tai_tbl(indx).try_id := l_bill_try_id;
1146 ELSE
1147 tai_tbl(indx).try_id := l_cm_try_id;
1148 END IF;
1149
1150 tai_tbl(indx).date_entered := sysdate;
1151 tai_tbl(indx).trx_status_code := 'ERROR';
1152 tai_tbl(indx).amount := ext_bill_tbl(indx).amount;
1153 --added by kbbhavsa : Bug 5344799/5362220 : 27-June-06
1154 l_sty_desc := ext_bill_tbl(indx).STY_NAME;
1155 ---------------------------------------------
1156 -- Other Mandatory Columns
1157 ---------------------------------------------
1158 tai_tbl(indx).CREATION_DATE := SYSDATE;
1159 tai_tbl(indx).CREATED_BY := l_last_updated_by;
1160 tai_tbl(indx).LAST_UPDATE_DATE := SYSDATE;
1161 tai_tbl(indx).LAST_UPDATED_BY := l_last_updated_by;
1162 tai_tbl(indx).LAST_UPDATE_LOGIN := l_last_update_login;
1163 tai_tbl(indx).OBJECT_VERSION_NUMBER := 1;
1164 l_header_id := Okc_P_Util.raw_to_number(sys_guid());
1165 tai_tbl(indx).ID := l_header_id;
1166 tai_tbl(indx).trx_number := SUBSTR(TO_CHAR(l_header_id),-6);
1167 tai_tbl(indx).request_id := l_request_id;
1168 tai_tbl(indx).program_application_id := l_program_application_id;
1169 tai_tbl(indx).program_id := l_program_id;
1170 tai_tbl(indx).program_update_date := sysdate;
1171
1172 -- save tai_id's
1173 header_id_tbl(indx) := l_header_id;
1174 -------------------------------------------------------------------
1175 -- Process Invoice record for calling Billing Engine
1176 -------------------------------------------------------------------
1177 IF ext_bill_tbl(indx).amount > 0 THEN
1178 l_taiv_rec.try_id := l_bill_try_id;
1179 ELSE
1180 l_taiv_rec.try_id := l_cm_try_id;
1181 END IF;
1182 l_khr_id := ext_bill_tbl(indx).contract_id;
1183 l_taiv_rec.khr_id := l_khr_id;
1184 l_taiv_rec.date_invoiced := ext_bill_tbl(indx).INVOICE_DATE;
1185 l_taiv_rec.date_entered := sysdate;
1186 l_taiv_rec.amount := ext_bill_tbl(indx).amount;
1187 /* sosharma 10-Apr-07
1188 added parameters to be passed to billing procedure
1189 Start Changes*/
1190 l_taiv_rec.trx_status_code:='SUBMITTED';
1191 l_taiv_rec.okl_source_billing_trx:='THIRD_PARTY_IMPORT';
1192 l_taiv_rec.description := l_sty_desc;
1193 /* sosharma end changes */
1194 --other mandatory columns
1195 l_taiv_rec.CREATION_DATE := SYSDATE;
1196 l_taiv_rec.CREATED_BY := l_last_updated_by;
1197 l_taiv_rec.LAST_UPDATE_DATE := SYSDATE;
1198 l_taiv_rec.LAST_UPDATED_BY := l_last_updated_by;
1199 l_taiv_rec.LAST_UPDATE_LOGIN := l_last_update_login;
1200 l_taiv_rec.OBJECT_VERSION_NUMBER := 1;
1201 l_taiv_rec.request_id := l_request_id;
1202 l_taiv_rec.program_application_id := l_program_application_id;
1203 l_taiv_rec.program_id := l_program_id;
1204 l_taiv_rec.program_update_date := sysdate;
1205
1206 ---------------------------------------------
1207 -- Create TAI_TL records
1208 ---------------------------------------------
1209 l_taitl_cnt := taitl_tbl.count;
1210 FOR l_lang_rec IN get_languages LOOP
1211 l_taitl_cnt := l_taitl_cnt + 1;
1212 taitl_tbl(l_taitl_cnt).ID := l_header_id;
1213 taitl_tbl(l_taitl_cnt).LANGUAGE := l_lang_rec.language_code;
1214 taitl_tbl(l_taitl_cnt).SOURCE_LANG := USERENV('LANG');
1215 taitl_tbl(l_taitl_cnt).SFWT_FLAG := 'N';
1216 --taitl_tbl(l_taitl_cnt).DESCRIPTION := 'Imported Billing Transaction';
1217 taitl_tbl(l_taitl_cnt).DESCRIPTION := l_sty_desc; -- Added : bug 5362220 : prasjian
1218 taitl_tbl(l_taitl_cnt).CREATION_DATE := SYSDATE;
1219 taitl_tbl(l_taitl_cnt).CREATED_BY := l_last_updated_by;
1220 taitl_tbl(l_taitl_cnt).LAST_UPDATE_DATE := SYSDATE;
1221 taitl_tbl(l_taitl_cnt).LAST_UPDATED_BY := l_last_updated_by;
1222 taitl_tbl(l_taitl_cnt).LAST_UPDATE_LOGIN := l_last_update_login;
1223 END LOOP;
1224
1225 ---------------------------------------------
1226 -- Populate required columns
1227 ---------------------------------------------
1228 til_tbl(indx).kle_id := ext_bill_tbl(indx).asset_id;
1229 til_tbl(indx).line_number := 1;
1230 til_tbl(indx).tai_id := l_header_id;
1231 til_tbl(indx).sty_id := ext_bill_tbl(indx).sty_id;
1232 til_tbl(indx).inv_receiv_line_code := 'LINE';
1233 til_tbl(indx).amount := ext_bill_tbl(indx).amount;
1234 l_line_id := Okc_P_Util.raw_to_number(sys_guid());
1235 til_tbl(indx).ID := l_line_id;
1236 til_tbl(indx).OBJECT_VERSION_NUMBER := 1;
1237 til_tbl(indx).CREATION_DATE := SYSDATE;
1238 til_tbl(indx).CREATED_BY := l_last_updated_by;
1239 til_tbl(indx).LAST_UPDATE_DATE := SYSDATE;
1240 til_tbl(indx).LAST_UPDATED_BY := l_last_updated_by;
1241 til_tbl(indx).LAST_UPDATE_LOGIN := l_last_update_login;
1242 til_tbl(indx).request_id := l_request_id;
1243 til_tbl(indx).program_application_id := l_program_application_id;
1244 til_tbl(indx).program_id := l_program_id;
1245 til_tbl(indx).program_update_date := sysdate;
1246
1247 ---------------------------------------------
1248 -- Create TIL_TL records
1249 ---------------------------------------------
1250 l_tiltl_cnt := tiltl_tbl.count;
1251 FOR l_lang_rec IN get_languages LOOP
1252 l_tiltl_cnt := l_tiltl_cnt + 1;
1253 tiltl_tbl(l_tiltl_cnt).ID := l_line_id;
1254 tiltl_tbl(l_tiltl_cnt).LANGUAGE := l_lang_rec.language_code;
1255 tiltl_tbl(l_tiltl_cnt).SOURCE_LANG := USERENV('LANG');
1256 tiltl_tbl(l_tiltl_cnt).SFWT_FLAG := 'N';
1257 -- tiltl_tbl(l_tiltl_cnt).DESCRIPTION := 'Imported Billing Transaction';
1258 tiltl_tbl(l_tiltl_cnt).DESCRIPTION := l_sty_desc; -- Added : bug 5362220 : prasjian
1259 tiltl_tbl(l_tiltl_cnt).CREATION_DATE := SYSDATE;
1260 tiltl_tbl(l_tiltl_cnt).CREATED_BY := l_last_updated_by;
1261 tiltl_tbl(l_tiltl_cnt).LAST_UPDATE_DATE := SYSDATE;
1262 tiltl_tbl(l_tiltl_cnt).LAST_UPDATED_BY := l_last_updated_by;
1263 tiltl_tbl(l_tiltl_cnt).LAST_UPDATE_LOGIN := l_last_update_login;
1264 END LOOP;
1265
1266 ---------------------------------------------------------------------
1267 -- Process Invoice Line record for Billing Engine
1268 ---------------------------------------------------------------------
1269 l_tilv_rec.kle_id := ext_bill_tbl(indx).asset_id;
1270 l_tilv_rec.line_number := 1;
1271 l_tilv_rec.sty_id := ext_bill_tbl(indx).sty_id;
1272 l_tilv_rec.inv_receiv_line_code := 'LINE';
1273 l_tilv_rec.amount := ext_bill_tbl(indx).amount;
1274 --other mandatory columns
1275 l_tilv_rec.OBJECT_VERSION_NUMBER := 1;
1276 l_tilv_rec.CREATION_DATE := SYSDATE;
1277 l_tilv_rec.CREATED_BY := l_last_updated_by;
1278 l_tilv_rec.LAST_UPDATE_DATE := SYSDATE;
1279 l_tilv_rec.LAST_UPDATED_BY := l_last_updated_by;
1280 l_tilv_rec.LAST_UPDATE_LOGIN := l_last_update_login;
1281 l_tilv_rec.request_id := l_request_id;
1282 l_tilv_rec.program_application_id := l_program_application_id;
1283 l_tilv_rec.program_id := l_program_id;
1284 l_tilv_rec.program_update_date := sysdate;
1285
1286 /* sosharma 10-Apr-07
1287 added parameters to be passed to billing procedure
1288 Srart Changes*/
1289 l_tilv_rec.TXL_AR_LINE_NUMBER :=1;
1290 l_tilv_rec.description := l_sty_desc;
1291 /* sosharma end changes */
1292 ---------------------------------------------------------------------
1293 -- Process Line Detail record for Billing Engine
1294 ---------------------------------------------------------------------
1295 l_tilv_tbl(0) := l_tilv_rec;
1296
1297 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1298 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - call to billing centralized API.');
1299 END IF;
1300
1301 OKL_INTERNAL_BILLING_PVT.CREATE_BILLING_TRX(
1302 p_api_version
1303 ,p_init_msg_list
1304 ,x_return_status
1305 ,x_msg_count
1306 ,x_msg_data
1307 ,l_taiv_rec
1308 ,l_tilv_tbl
1309 ,l_tldv_tbl
1310 ,x_taiv_rec
1311 ,x_tilv_tbl
1312 ,x_tldv_tbl);
1313
1314 IF x_return_status <> 'S' THEN
1315 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1316 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- ERROR: Creating Billing Transactions using Billing Engine');
1317 END IF;
1318 IF (x_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1319 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1320 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1321 RAISE Okl_Api.G_EXCEPTION_ERROR;
1322 END IF;
1323 END IF;
1324 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1325 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - call to billing centralized API.');
1326 END IF;
1327
1328 l_tilv_tbl.delete;
1329 l_tldv_tbl.delete;
1330 END LOOP; -- process records in ext_bill_tbl
1331
1332 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1333 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - populating transaction header and line record.');
1334 END IF;
1335 END IF; -- check if ext_bill_tbl has records
1336 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1337 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - processing billing transactions batch number: '||g_batch_num);
1338 END IF;
1339 EXIT WHEN bill_txn_csr%NOTFOUND;
1340 END LOOP; -- process transaction records
1341
1342 COMMIT;
1343 -- ----------------------------------------------
1344 -- clean up after processing transaction records
1345 -- ----------------------------------------------
1346 ext_bill_tbl.delete;
1347 tai_tbl.delete;
1348 til_tbl.delete;
1349 taitl_tbl.delete;
1350 tiltl_tbl.delete;
1351 upd_rowid_tbl.delete;
1352 header_id_tbl.delete;
1353
1354 -- ---------------------------------------
1355 -- end process billing transaction records
1356 -- ---------------------------------------
1357
1358 -- ---------------------------------------
1359 -- begin processing actual prop tax strm
1360 -- elements
1361 -- ---------------------------------------
1362 g_batch_num := 0;
1363 OPEN act_prop_tax_csr( l_request_id );
1364 LOOP
1365 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1366 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - processing actual property tax records batch number: '||g_batch_num);
1367 END IF;
1368
1369 ext_bill_tbl.delete;
1370 ptc_tbl.delete;
1371 ptctl_tbl.delete;
1372 stm_tbl.delete;
1373 sel_tbl.delete;
1374 upd_rowid_tbl.delete;
1375 upd_sel_tbl.delete;
1376 FETCH act_prop_tax_csr BULK COLLECT
1377 INTO ext_bill_tbl
1378 LIMIT L_FETCH_SIZE;
1379 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1380 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'*=> number of records: '||ext_bill_tbl.count);
1381 END IF;
1382 -- -------------------------------------
1383 -- process billing transaction records
1384 -- -------------------------------------
1385 IF ext_bill_tbl.count > 0 THEN
1386 -- -------------------------------------------
1387 -- loop thru ext_bill_tbl and process records
1388 -- -------------------------------------------
1389 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1390 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'++ begin - populate property tax parameters record, stm rec and sel rec.');
1391 END IF;
1392 FOR indx in ext_bill_tbl.first..ext_bill_tbl.last
1393 LOOP
1394 -- save rowid for later update
1395 upd_rowid_tbl(indx) := ext_bill_tbl(indx).ext_rowid;
1396
1397 ptc_tbl(indx).id := Okc_P_Util.raw_to_number(sys_guid());
1398 ptc_tbl(indx).asset_id := ext_bill_tbl(indx).ASSET_ID;
1399 ptc_tbl(indx).asset_number := ext_bill_tbl(indx).ASSET_NUMBER;
1400 ptc_tbl(indx).khr_id := ext_bill_tbl(indx).contract_id;
1401 ptc_tbl(indx).contract_number := ext_bill_tbl(indx).CONTRACT_NUMBER;
1402 --rviriyal 05-Feb-2008 bug#6764803 Populated kle_id to view actual property tax in lease center.
1403 ptc_tbl(indx).kle_id := ext_bill_tbl(indx).ASSET_ID;
1404 --end 05-Feb-2008 bug#6764803
1405 ptc_tbl(indx).sty_name := ext_bill_tbl(indx).STY_NAME;
1406 ptc_tbl(indx).sty_id := ext_bill_tbl(indx).sty_id;
1407 ptc_tbl(indx).invoice_date := ext_bill_tbl(indx).INVOICE_DATE;
1408 ptc_tbl(indx).amount := ext_bill_tbl(indx).AMOUNT;
1409 ptc_tbl(indx).org_id := ext_bill_tbl(indx).ORG_ID;
1410 ptc_tbl(indx).JURSDCTN_TYPE := ext_bill_tbl(indx).JURSDCTN_TYPE;
1411 ptc_tbl(indx).JURSDCTN_NAME := ext_bill_tbl(indx).JURSDCTN_NAME;
1412 ptc_tbl(indx).MLRT_TAX := ext_bill_tbl(indx).MLRT_TAX;
1413 ptc_tbl(indx).TAX_VENDOR_ID := ext_bill_tbl(indx).TAX_VENDOR_ID;
1414 ptc_tbl(indx).TAX_VENDOR_NAME := ext_bill_tbl(indx).TAX_VENDOR_NAME;
1415 ptc_tbl(indx).TAX_VENDOR_SITE_ID := ext_bill_tbl(indx).TAX_VENDOR_SITE_ID;
1416 ptc_tbl(indx).TAX_VENDOR_SITE_NAME := ext_bill_tbl(indx).TAX_VENDOR_SITE_NAME;
1417 ptc_tbl(indx).CREATED_BY := l_last_updated_by;
1418 ptc_tbl(indx).CREATION_DATE := SYSDATE;
1419 ptc_tbl(indx).LAST_UPDATED_BY := l_last_updated_by;
1420 ptc_tbl(indx).LAST_UPDATE_DATE := SYSDATE;
1421 ptc_tbl(indx).LAST_UPDATE_LOGIN := l_last_update_login;
1422 ptc_tbl(indx).TAX_ASSESSMENT_DATE := ext_bill_tbl(indx).TAX_ASSESSMENT_DATE; --vpanwar FPbug#5891876
1423
1424 l_ptctl_cnt := ptctl_tbl.count;
1425 FOR l_lang_rec IN get_languages
1426 LOOP
1427 l_ptctl_cnt := l_ptctl_cnt + 1;
1428 ptctl_tbl(l_ptctl_cnt).ID := ptc_tbl(indx).id;
1429 ptctl_tbl(l_ptctl_cnt).LANGUAGE := l_lang_rec.language_code;
1430 ptctl_tbl(l_ptctl_cnt).SOURCE_LANG := USERENV('LANG');
1431 ptctl_tbl(l_ptctl_cnt).SFWT_FLAG := 'N';
1432 ptctl_tbl(l_ptctl_cnt).CREATION_DATE := SYSDATE;
1433 ptctl_tbl(l_ptctl_cnt).CREATED_BY := l_last_updated_by;
1434 ptctl_tbl(l_ptctl_cnt).LAST_UPDATE_DATE := SYSDATE;
1435 ptctl_tbl(l_ptctl_cnt).LAST_UPDATED_BY := l_last_updated_by;
1436 ptctl_tbl(l_ptctl_cnt).LAST_UPDATE_LOGIN := l_last_update_login;
1437 END LOOP;
1438
1439 -- check for actual property tax stream type
1440 l_actual_tax_count := 0;
1441 OPEN c_sty_count_csr ( ext_bill_tbl(indx).contract_id,
1442 ext_bill_tbl(indx).asset_id,
1443 ext_bill_tbl(indx).sty_id );
1444 FETCH c_sty_count_csr INTO l_actual_tax_count;
1445 CLOSE c_sty_count_csr;
1446
1447 IF l_actual_tax_count > 0 THEN -- check for actual property tax stream
1448 NULL;
1449 ELSE -- check for actual property tax stream
1450 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1451 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' ++ begin - create ACTUAL PROPERTY TAX stream.');
1452 END IF;
1453 -- -------------------------
1454 -- Null out records
1455 -- -------------------------
1456 l_stmtbl_cnt := stm_tbl.count;
1457 l_stmtbl_cnt := l_stmtbl_cnt + 1;
1458 l_stmv_rec := l_init_stmv_rec;
1459 lx_stmv_rec := l_init_stmv_rec;
1460
1461 OPEN c_tran_num_csr;
1462 FETCH c_tran_num_csr INTO stm_tbl(l_stmtbl_cnt).transaction_number;
1463 CLOSE c_tran_num_csr;
1464
1465 stm_tbl(l_stmtbl_cnt).sty_id := ext_bill_tbl(indx).sty_id;
1466 stm_tbl(l_stmtbl_cnt).khr_id := ext_bill_tbl(indx).contract_id;
1467 stm_tbl(l_stmtbl_cnt).kle_id := ext_bill_tbl(indx).asset_id;
1468 stm_tbl(l_stmtbl_cnt).sgn_code := 'MANL';
1469 stm_tbl(l_stmtbl_cnt).say_code := 'CURR';
1470 stm_tbl(l_stmtbl_cnt).active_yn := 'Y';
1471 stm_tbl(l_stmtbl_cnt).date_current := sysdate;
1472 stm_tbl(l_stmtbl_cnt).comments := 'ACTUAL PROPERTY TAX';
1473
1474 -- other mandatory columns
1475 stm_tbl(l_stmtbl_cnt).id := Okc_P_Util.raw_to_number(sys_guid());
1476 stm_tbl(l_stmtbl_cnt).OBJECT_VERSION_NUMBER := 1;
1477 stm_tbl(l_stmtbl_cnt).PROGRAM_ID := l_program_id;
1478 stm_tbl(l_stmtbl_cnt).REQUEST_ID := l_request_id;
1479 stm_tbl(l_stmtbl_cnt).PROGRAM_APPLICATION_ID := l_program_application_id;
1480 stm_tbl(l_stmtbl_cnt).PROGRAM_UPDATE_DATE := sysdate;
1481 stm_tbl(l_stmtbl_cnt).CREATED_BY := l_last_updated_by;
1482 stm_tbl(l_stmtbl_cnt).CREATION_DATE := sysdate;
1483 stm_tbl(l_stmtbl_cnt).LAST_UPDATED_BY := l_last_updated_by;
1484 stm_tbl(l_stmtbl_cnt).LAST_UPDATE_DATE := sysdate;
1485 stm_tbl(l_stmtbl_cnt).LAST_UPDATE_LOGIN := l_last_update_login;
1486
1487 -- insert stm records
1488 if stm_tbl.count > 0 then
1489 forall i in stm_tbl.first..stm_tbl.last
1490 insert into okl_streams
1491 values stm_tbl(i);
1492 commit;
1493 stm_tbl.delete;
1494 end if;
1495 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1496 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- end - create ACTUAL PROPERTY TAX stream.');
1497 END IF;
1498 END IF; -- end check for actual property tax stream
1499
1500 -- Create stream element actual property tax stream element
1501 -- Create Stream Element
1502 l_stm_id := NULL;
1503 OPEN c_stm_id_csr ( ext_bill_tbl(indx).contract_id,
1504 ext_bill_tbl(indx).asset_id,
1505 ext_bill_tbl(indx).sty_id );
1506 FETCH c_stm_id_csr INTO l_stm_id;
1507 CLOSE c_stm_id_csr;
1508
1509 -- --------------------------------
1510 -- fetch max se_line_number from
1511 -- sel table
1512 -- --------------------------------
1513 l_max_line_num := 0;
1514 OPEN max_line_num_csr ( l_stm_id );
1515 FETCH max_line_num_csr INTO l_max_line_num;
1516 CLOSE max_line_num_csr;
1517
1518 -- populate parent PK
1519 sel_tbl(indx).stm_id := l_stm_id;
1520
1521 -- --------------------------------
1522 -- To prevent constraint violation
1523 -- OKL_SEL_U2
1524 -- --------------------------------
1525 IF sel_tbl.exists(indx-1) then
1526 IF sel_tbl(indx-1).stm_id = l_stm_id then
1527 l_max_line_num := sel_tbl(indx-1).SE_LINE_NUMBER;
1528 END IF;
1529 END IF;
1530
1531 sel_tbl(indx).SE_LINE_NUMBER := NVL( l_max_line_num, 0 ) + 1;
1532 sel_tbl(indx).STREAM_ELEMENT_DATE := ext_bill_tbl(indx).INVOICE_DATE;
1533 sel_tbl(indx).AMOUNT := ext_bill_tbl(indx).AMOUNT;
1534 sel_tbl(indx).COMMENTS := 'ACTUAL PROPERTY TAX';
1535 sel_tbl(indx).SOURCE_ID := ptc_tbl(indx).id;
1536 sel_tbl(indx).SOURCE_TABLE := 'OKL_PROPERTY_TAX_V';
1537 -- Other columns
1538 sel_tbl(indx).id := Okc_P_Util.raw_to_number(sys_guid());
1539 -- save sel_id for update
1540 upd_sel_tbl(indx) := sel_tbl(indx).id;
1541
1542 sel_tbl(indx).OBJECT_VERSION_NUMBER := 1;
1543 sel_tbl(indx).PROGRAM_ID := l_program_id;
1544 sel_tbl(indx).REQUEST_ID := l_request_id;
1545 sel_tbl(indx).PROGRAM_APPLICATION_ID := l_program_application_id;
1546 sel_tbl(indx).PROGRAM_UPDATE_DATE := sysdate;
1547 sel_tbl(indx).CREATED_BY := l_last_updated_by;
1548 sel_tbl(indx).CREATION_DATE := sysdate;
1549 sel_tbl(indx).LAST_UPDATED_BY := l_last_updated_by;
1550 sel_tbl(indx).LAST_UPDATE_DATE := sysdate;
1551 sel_tbl(indx).LAST_UPDATE_LOGIN := l_last_update_login;
1552
1553 -- end creation of actual property tax stream element
1554 END LOOP; -- end processing ext table records
1555
1556 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1557 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - populate property tax parameters record, stm rec and sel rec.');
1558 END IF;
1559
1560 -- insert records into okl_property_tax_b
1561 if ptc_tbl.count > 0 then
1562 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1563 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' ++ begin - create ptc_b rec.');
1564 END IF;
1565 forall i in ptc_tbl.first..ptc_tbl.last
1566 insert into okl_property_tax_b
1567 values ptc_tbl(i);
1568 COMMIT;
1569 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1570 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- end - create ptc_b rec.');
1571 END IF;
1572 END IF;
1573
1574 -- insert records into okl_property_tax_tl
1575 if ptctl_tbl.count > 0 then
1576 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1577 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' ++ begin - create ptc_tl rec.');
1578 END IF;
1579 FORALL i in ptctl_tbl.first..ptctl_tbl.last
1580 INSERT INTO okl_property_tax_tl
1581 VALUES ptctl_tbl(i);
1582 COMMIT;
1583 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1584 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- end - create ptc_tl rec.');
1585 END IF;
1586 END IF;
1587
1588 -- update okl_ext_billing_interface with sel_id
1589 IF upd_rowid_tbl.count > 0 then
1590 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1591 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' ++ begin - update ext billing interface with sel_id.');
1592 END IF;
1593 FORALL i in upd_rowid_tbl.first..upd_rowid_tbl.last
1594 UPDATE okl_ext_billing_interface
1595 SET sel_id = upd_sel_tbl(i)
1596 WHERE rowid = upd_rowid_tbl(i);
1597 COMMIT;
1598 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1599 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- end - update ext billing interface with sel_id.');
1600 END IF;
1601 END IF;
1602
1603 -- insert sel records
1604 IF sel_tbl.count > 0 THEN
1605 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1606 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' ++ begin - create actual property tax stream elements.');
1607 END IF;
1608 forall i in sel_tbl.first..sel_tbl.last
1609 insert into okl_strm_elements
1610 values sel_tbl(i);
1611 COMMIT;
1612 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1613 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- end - create actual property tax stream elements.');
1614 END IF;
1615 END IF;
1616
1617 -- create payable invoices
1618 IF ( l_p_tax_applicable = 'YES' ) THEN
1619 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1620 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' ++ begin - create payable invoice.');
1621 END IF;
1622 l_acc_cmt_cnt := 0;
1623 FOR indx in ext_bill_tbl.first..ext_bill_tbl.last
1624 LOOP
1625 l_acc_cmt_cnt := l_acc_cmt_cnt + 1;
1626
1627 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1628 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- Creating Payable Invoice to Tax Authority');
1629 END IF;
1630 p_man_inv_rec.ipvs_id := ext_bill_tbl(indx).TAX_VENDOR_SITE_ID;
1631 p_man_inv_rec.khr_id := ext_bill_tbl(indx).contract_id;
1632 p_man_inv_rec.vendor_id := ext_bill_tbl(indx).TAX_VENDOR_ID;
1633 p_man_inv_rec.invoice_date := ext_bill_tbl(indx).INVOICE_DATE;
1634 p_man_inv_rec.amount := ext_bill_tbl(indx).AMOUNT;
1635 p_man_inv_rec.sty_id := ext_bill_tbl(indx).sty_id;
1636 p_man_inv_rec.sel_id := upd_sel_tbl(indx);
1637 -- for LE Uptake project 08-11-2006
1638 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(ext_bill_tbl(indx).contract_id);
1639 IF l_legal_entity_id IS NOT NULL THEN
1640 p_man_inv_rec.legal_entity_id := l_legal_entity_id;
1641 END IF;
1642 -- for LE Uptake project 08-11-2006
1643
1644 OKL_PAY_INVOICES_MAN_PUB.manual_entry(
1645 p_api_version
1646 ,p_init_msg_list
1647 ,x_return_status
1648 ,x_msg_count
1649 ,x_msg_data
1650 ,p_man_inv_rec
1651 ,x_man_inv_rec);
1652
1653 -- ---------------------------------------
1654 -- Create a payable Invoice
1655 -- ---------------------------------------
1656 IF x_return_status <> 'S' THEN
1657 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1658 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- ERROR: Creating Payable Invoice');
1659 END IF;
1660 UPDATE OKL_EXT_BILLING_INTERFACE
1661 SET trx_status_code = 'ERROR',
1662 ERROR_MESSAGE = ERROR_MESSAGE||'Error Creating Payable Invoice. '
1663 WHERE rowid = ext_bill_tbl(indx).ext_rowid;
1664 -- ------------------------------
1665 -- delete orphan record in PTC for
1666 -- referential integrity
1667 -- ------------------------------
1668 delete from OKL_PROPERTY_TAX_tl where id = ptc_tbl(indx).id;
1669 delete from OKL_PROPERTY_TAX_b where id = ptc_tbl(indx).id;
1670 delete from okl_strm_elements where id = upd_sel_tbl(indx);
1671 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1672 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- Deleting records from OKL_PROPERTY_TAX_tl and OKL_PROPERTY_TAX_b.');
1673 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- Deleting record from OKL_STRM_ELEMENTS.');
1674 END IF;
1675 END IF;
1676
1677 IF l_acc_cmt_cnt > 500 THEN
1678 COMMIT;
1679 l_acc_cmt_cnt := 0;
1680 END IF;
1681 END LOOP;
1682 COMMIT;
1683 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1684 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- end - create payable invoice.');
1685 END IF;
1686 END IF;
1687 -- end creation of payable invoices
1688
1689 -- Update records in the interface to PROCESSED status
1690 if upd_rowid_tbl.count > 0 then
1691 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1692 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' ++ begin - update status on okl_ext_billing_interface for successful records .');
1693 END IF;
1694 forall j in upd_rowid_tbl.first..upd_rowid_tbl.last
1695 update okl_ext_billing_interface
1696 set trx_status_code = decode(trx_status_code,'PASSED','PROCESSED',trx_status_code)
1697 where rowid = upd_rowid_tbl(j);
1698 COMMIT;
1699 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1700 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' -- end - update status on okl_ext_billing_interface for successful records .');
1701 END IF;
1702 END IF;
1703
1704 -- Start Bug 4520466
1705 for indx in ext_bill_tbl.first..ext_bill_tbl.last loop
1706 OKL_BILLING_CONTROLLER_PVT.track_next_bill_date( ext_bill_tbl(indx).contract_id );
1707 end loop;
1708 -- End Bug 4520466
1709 END IF; -- check if ext_bill_tbl has records
1710
1711 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1712 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'-- end - processing actual property tax records batch number: '||g_batch_num);
1713 END IF;
1714 EXIT WHEN act_prop_tax_csr%NOTFOUND;
1715 END LOOP; -- process transaction records
1716
1717 -- -----------------------------
1718 -- Print processing summary
1719 -- -----------------------------
1720 -- Success Count for actual property tax
1721 l_succ_apt_cnt := NULL;
1722 OPEN ext_apt_stat_csr( l_request_id, 'PROCESSED', 'ACTUAL_PROPERTY_TAX' );
1723 FETCH ext_apt_stat_csr INTO l_succ_apt_cnt;
1724 CLOSE ext_apt_stat_csr;
1725
1726 -- Error Count for actual property tax
1727 l_err_apt_cnt := NULL;
1728 OPEN ext_apt_stat_csr( l_request_id, 'ERROR', 'ACTUAL_PROPERTY_TAX' );
1729 FETCH ext_apt_stat_csr INTO l_err_apt_cnt;
1730 CLOSE ext_apt_stat_csr;
1731
1732 -- Success Count for NON-actual property tax
1733 l_succ_non_apt_cnt := NULL;
1734 OPEN ext_non_apt_stat_csr( l_request_id, 'PROCESSED', 'ACTUAL_PROPERTY_TAX' );
1735 FETCH ext_non_apt_stat_csr INTO l_succ_non_apt_cnt;
1736 CLOSE ext_non_apt_stat_csr;
1737
1738 -- Error Count for NON-actual property tax
1739 l_err_non_apt_cnt := NULL;
1740 OPEN ext_non_apt_stat_csr( l_request_id, 'ERROR', 'ACTUAL_PROPERTY_TAX' );
1741 FETCH ext_non_apt_stat_csr INTO l_err_non_apt_cnt;
1742 CLOSE ext_non_apt_stat_csr;
1743
1744 ----------------------------------------
1745 -- Get Operating unit name
1746 ----------------------------------------
1747 l_op_unit_name := NULL;
1748 OPEN op_unit_csr;
1749 FETCH op_unit_csr INTO l_op_unit_name;
1750 CLOSE op_unit_csr;
1751
1752 -- Start New Out File stmathew
1753 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 54, ' ')||'Oracle Leasing and Finance Management'||LPAD(' ', 55, ' '));
1754 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
1755 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 53, ' ')||'Third Party Billing Import'||LPAD(' ', 53, ' '));
1756 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 54, ' ')||'------------------------'||LPAD(' ', 54, ' '));
1757 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
1758 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
1759 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Operating Unit: '||l_op_unit_name);
1760 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Request Id: '||l_request_id||LPAD(' ',74,' ') ||'Run Date: '||TO_CHAR(SYSDATE));
1761 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Currency: '||Okl_Accounting_Util.get_func_curr_code);
1762 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD('-', 132, '-'));
1763 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
1764 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
1765 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,'Processing Details:'||LPAD(' ', 113, ' '));
1766 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
1767 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Number of Successful Actual Property Tax records: '||l_succ_apt_cnt);
1768 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Number of Errored Actual Property Tax records: '||l_err_apt_cnt);
1769 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Number of Successful Non-Actual Property Tax records: '||l_succ_non_apt_cnt);
1770 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Number of Errored Non-Actual Property Tax records: '||l_err_non_apt_cnt);
1771 Fnd_File.PUT_LINE (Fnd_File.OUTPUT, ' Total records processed: '||g_total_rec_count);
1772 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
1773 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD('-', 132, '-'));
1774 Fnd_File.PUT_LINE (Fnd_File.OUTPUT,RPAD(' ', 132, ' '));
1775
1776 -- End New Out File stmathew
1777 IF x_msg_count > 0 THEN
1778 FOR i IN 1..x_msg_count LOOP
1779 IF i = 1 THEN
1780 Fnd_File.PUT_LINE (Fnd_File.log,'Details of TAPI errors:'||LPAD(' ', 97, ' '));
1781 Fnd_File.PUT_LINE (Fnd_File.log,RPAD(' ', 132, ' '));
1782 END IF;
1783 Fnd_Msg_Pub.get (p_msg_index => i,
1784 p_encoded => 'F',
1785 p_data => lx_msg_data,
1786 p_msg_index_out => l_msg_index_out);
1787 Fnd_File.PUT_LINE (Fnd_File.log,TO_CHAR(i) || ': ' || lx_msg_data);
1788 END LOOP;
1789 END IF;
1790
1791 -- ---------------------------------------------------------
1792 -- print all error messages from okl_ext_billing_interface
1793 -- ---------------------------------------------------------
1794 Fnd_File.PUT_LINE (Fnd_File.log,RPAD(' ', 132, ' '));
1795 Fnd_File.PUT_LINE (Fnd_File.log,RPAD(' ', 132, ' '));
1796 Fnd_File.PUT_LINE (Fnd_File.log,'Contract Number'||LPAD(' ', 16, ' ')
1797 ||'Asset Number'||LPAD(' ', 19, ' ')
1798 ||'Stream Name'||LPAD(' ', 20, ' ')
1799 ||'Invoice Date'||' Error Message');
1800 Fnd_File.PUT_LINE (Fnd_File.log,'---------------'||LPAD(' ', 16, ' ')
1801 ||'------------'||LPAD(' ', 19, ' ')
1802 ||'-----------'||LPAD(' ', 20, ' ')
1803 ||'------------'||' -------------');
1804
1805 FOR error_msg_rec in error_msg_csr( l_request_id, 'ERROR' )
1806 LOOP
1807 Fnd_File.PUT_LINE (Fnd_File.log,error_msg_rec.contract_number||' '
1808 ||error_msg_rec.asset_number||' '
1809 ||error_msg_rec.sty_name||' '
1810 ||error_msg_rec.invoice_date||' '
1811 ||error_msg_rec.error_message);
1812 END LOOP;
1813 Fnd_File.PUT_LINE (Fnd_File.log,RPAD(' ', 132, ' '));
1814 Fnd_File.PUT_LINE (Fnd_File.log,RPAD(' ', 132, ' '));
1815 Fnd_File.PUT_LINE (Fnd_File.log,RPAD(' ', 132, ' '));
1816 -- -------------------------------
1817 -- End Print processing Summary
1818 -- -------------------------------
1819
1820 FND_FILE.PUT_LINE (FND_FILE.log, '================================================================');
1821 FND_FILE.PUT_LINE (FND_FILE.log, ' *** END PROCESSING THIRD PARTY BILLING RECORDS ***');
1822 FND_FILE.PUT_LINE (FND_FILE.log, '================================================================');
1823
1824 ------------------------------------------------------------
1825 -- End processing
1826 ------------------------------------------------------------
1827 IF (L_DEBUG_ENABLED='Y' and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1828 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1829 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'End (-)');
1830 END IF;
1831 END IF;
1832
1833 Okl_Api.END_ACTIVITY (
1834 x_msg_count => x_msg_count,
1835 x_msg_data => x_msg_data);
1836 EXCEPTION
1837 ------------------------------------------------------------
1838 -- Exception handling
1839 ------------------------------------------------------------
1840 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1841 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=>ERROR: '||SQLERRM);
1842 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
1843 p_api_name => l_api_name,
1844 p_pkg_name => G_PKG_NAME,
1845 p_exc_name => 'Okl_Api.G_RET_STS_ERROR',
1846 x_msg_count => x_msg_count,
1847 x_msg_data => x_msg_data,
1848 p_api_type => '_PVT');
1849 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1850 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=>ERROR: '||SQLERRM);
1851 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
1852 p_api_name => l_api_name,
1853 p_pkg_name => G_PKG_NAME,
1854 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
1855 x_msg_count => x_msg_count,
1856 x_msg_data => x_msg_data,
1857 p_api_type => '_PVT');
1858 WHEN OTHERS THEN
1859 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '*=>ERROR: '||SQLERRM);
1860 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
1861 p_api_name => l_api_name,
1862 p_pkg_name => G_PKG_NAME,
1863 p_exc_name => 'OTHERS',
1864 x_msg_count => x_msg_count,
1865 x_msg_data => x_msg_data,
1866 p_api_type => '_PVT');
1867 END BILLING_CHARGES;
1868
1869 END OKL_EXT_BILLING_CHARGES_PVT;