[Home] [Help]
PACKAGE BODY: APPS.OKL_REBOOK_CM_PVT
Source
1 PACKAGE BODY OKL_REBOOK_CM_PVT AS
2 /* $Header: OKLRCMRB.pls 120.17.12010000.3 2009/01/30 05:54:55 rpillay ship $ */
3 -- Start of wraper code generated automatically by Debug code generator
4 L_MODULE VARCHAR2(40) := 'LEASE.RECEIVABLES.BILLING';
5 L_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 L_LEVEL_PROCEDURE NUMBER;
7 IS_DEBUG_PROCEDURE_ON BOOLEAN;
8 -- End of wraper code generated automatically by Debug code generator
9
10
11 ------------------------------------------------------------------
12 -- Function GET_TRX_TYPE to extract transaction type
13 ------------------------------------------------------------------
14
15
16
17
18 FUNCTION get_trx_type
19 (p_name VARCHAR2,
20 p_language VARCHAR2)
21 RETURN NUMBER IS
22
23
24 CURSOR c_trx_type (cp_name VARCHAR2, cp_language VARCHAR2) IS
25 SELECT id
26 FROM okl_trx_types_tl
27 WHERE name = cp_name
28 AND LANGUAGE = cp_language;
29
30
31 -- Replace with following query
32 CURSOR c_trx_id( p_sob_id NUMBER, p_org_id NUMBER ) IS
33 SELECT ID1
34 FROM OKX_CUST_TRX_TYPES_V
35 WHERE name = 'Invoice-OKL' AND
36 set_of_books_id = p_sob_id AND
37 org_id = p_org_id;
38
39
40 l_trx_type okl_trx_types_v.id%TYPE;
41
42
43 BEGIN
44
45
46 l_trx_type := NULL;
47
48
49 OPEN c_trx_type (p_name, p_language);
50 FETCH c_trx_type INTO l_trx_type;
51 CLOSE c_trx_type;
52
53
54 RETURN l_trx_type;
55
56
57 END get_trx_type;
58
59
60 ------------------------------------------------------------------
61 -- Procedure CM_Bill_adjustments to create adjustments to
62 -- Rebooked and unfulfilled invoices with the amounts on the
63 -- Billing_Adjustment Stream Type
64 ------------------------------------------------------------------
65
66
67 PROCEDURE CM_Bill_adjustments
68 (p_api_version IN NUMBER
69 ,p_init_msg_list IN VARCHAR2
70 ,x_return_status OUT NOCOPY VARCHAR2
71 ,x_msg_count OUT NOCOPY NUMBER
72 ,x_msg_data OUT NOCOPY VARCHAR2
73 ,p_contract_number IN VARCHAR2
74 ) IS
75
76
77 ------------------------------------------------------------
78 -- Get Rebooked Contracts
79 ------------------------------------------------------------
80 CURSOR rbk_ctrct_csr ( p_contract_number VARCHAR2 ) IS
81 SELECT distinct KHR.ID
82 FROM OKC_K_HEADERS_B KHR,
83 OKL_STREAMS STM,
84 OKL_STRM_TYPE_B STY,
85 OKL_STRM_ELEMENTS SEL
86 WHERE KHR.contract_number = NVL(p_contract_number, contract_number)
87 AND KHR.STS_CODE = 'BOOKED'
88 AND KHR.ID = STM.KHR_ID
89 AND STM.sty_id = STY.id
90 AND STY.STREAM_TYPE_PURPOSE = 'REBOOK_BILLING_ADJUSTMENT'
91 AND STM.active_yn = 'Y'
92 AND sel.stm_id = stm.id
93 AND SEL.DATE_BILLED IS NULL;
94
95
96 /* SELECT id */
97 /* FROM okc_k_headers_b */
98 /* WHERE contract_number = NVL(p_contract_number, contract_number)
99 AND */
100 /* sts_code = 'BOOKED' AND */
101 /* id in ( */
102 /* SELECT rebook.id */
103 /* FROM okc_k_headers_b orig, */
104 /* okc_k_headers_b rebook */
105 /* WHERE orig.contract_number = NVL(p_contract_number,
106 orig.contract_number) and */
107 /* orig.authoring_org_id =
108 NVL(TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),1,10)),-99) and */
109 /* rebook.orig_system_id1 = orig.id and */
110 /* rebook.orig_system_source_code = 'OKL_REBOOK' */
111 /* UNION */
112 /* SELECT mass_rebook.id */
113 /* FROM okc_k_headers_b mass_rebook, */
114 /* okl_rbk_selected_contract rbk */
115 /* WHERE mass_rebook.contract_number = NVL(p_contract_number,
116 mass_rebook.contract_number) and */
117 /* mass_rebook.authoring_org_id =
118 NVL(TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),1,10)),-99) and */
119 /* rbk.khr_id = mass_rebook.id) */
120 /* order by 1; */
121
122
123 ------------------------------------------------------------
124 -- Get Rebooked Contracts with a Billing Adjustment
125 -- Stream Type
126 ------------------------------------------------------------
127 CURSOR adj_streams_csr( p_khr_id NUMBER ) IS
128 SELECT stm.khr_id khr_id,
129 TRUNC (ste.stream_element_date) bill_date,
130 stm.kle_id kle_id,
131 ste.id sel_id,
132 stm.sty_id sty_id,
133 khr.contract_number contract_number,
134 khr.currency_code currency_code,
135 khr.authoring_org_id authoring_org_id,
136 --sty.name stream_name,
137 sty.taxable_default_yn taxable_default_yn,
138 ste.amount amount,
139 khr.sts_code sts_code
140 FROM
141 okl_strm_elements ste,
142 okl_streams stm,
143 okl_strm_type_b sty,
144 okc_k_headers_b khr,
145 okl_k_headers khl,
146 okc_k_lines_b kle,
147 okc_statuses_b khs,
148 okc_statuses_b kls
149 WHERE ste.amount <> 0
150 AND stm.id = ste.stm_id
151 AND ste.date_billed IS NULL
152 AND stm.active_yn = 'Y'
153 -- AND stm.say_code = 'CURR'
154 AND sty.id = stm.sty_id
155 AND sty.stream_type_purpose = 'REBOOK_BILLING_ADJUSTMENT'
156 -- AND sty.billable_yn = 'Y'
157 AND khr.id = stm.khr_id
158 AND khr.scs_code IN ('LEASE', 'LOAN')
159 AND khr.sts_code = 'BOOKED'
160 AND khr.id = p_khr_id
161 -- AND khr.contract_number =
162 -- NVL (NULL, khr.contract_number)
163 AND khl.id = stm.khr_id
164 AND khl.deal_type IS NOT NULL
165 AND khs.code = khr.sts_code
166 AND khs.ste_code = 'ACTIVE'
167 AND kle.id (+)= stm.kle_id
168 AND kls.code (+)= kle.sts_code
169 -- Bug 3816891 adjust terminated asset lines
170 AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
171 ORDER BY 1, 2, 3;
172
173
174 ------------------------------------------------------------
175 -- Get Unpaid Invoices For the Contract Line
176 ------------------------------------------------------------
177 --Bug 5000886: Removed references of stream tables in the following cursor
178
179 -- Bug 6802801
180 -- Bug# 7720775: Changed API call to
181 -- OKL_BILLING_UTIL_PVT.INV_LN_AMT_REMAINING_W_INCTAX
182 CURSOR unpaid_invs_csr ( p_khr_id NUMBER, p_kle_id NUMBER ) IS
183 SELECT
184 tai.date_entered date_billed,
185 khr.id khr_id,
186 TRUNC (tai.date_invoiced) bill_date,
187 tld.kle_id kle_id,
188 tld.sel_id sel_id,
189 tld.sty_id sty_id,
190 khr.contract_number contract_number,
191 khr.currency_code currency_code,
192 khr.authoring_org_id authoring_org_id,
193 sty.code comments,
194 sty.taxable_default_yn taxable_default_yn,
195 OKL_BILLING_UTIL_PVT.INV_LN_AMT_ORIG_WOTAX
196 (tldv.CUSTOMER_TRX_ID, tldv.CUSTOMER_TRX_LINE_ID) amount,
197 khr.sts_code sts_code,
198 tld.id tld_id,
199 OKL_BILLING_UTIL_PVT.INV_LN_AMT_REMAINING_W_INCTAX
200 (tldv.CUSTOMER_TRX_ID, tldv.CUSTOMER_TRX_LINE_ID) amount_due_remaining,
201 PS.trx_number trx_number,
202 PS.class,
203 PS.TERMS_SEQUENCE_NUMBER
204 FROM
205 okl_strm_type_b sty,
206 okc_k_headers_b khr,
207 okl_k_headers khl,
208 okc_k_lines_b kle,
209 okc_statuses_b khs,
210 okc_statuses_b kls,
211 okl_txd_ar_ln_dtls_v tld,
212 okl_txl_ar_inv_lns_v til,
213 okl_trx_ar_invoices_v tai,
214 okl_bpd_tld_ar_lines_v tldv,
215 AR_PAYMENT_SCHEDULES_ALL PS
216 WHERE sty.billable_yn = 'Y'
217 AND tld.sty_id = sty.id
218 AND khr.id = p_khr_id
219 AND khr.scs_code IN ('LEASE', 'LOAN')
220 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
221 AND tld.kle_id = p_kle_id
222 AND tld.kle_id = kle.id
223 AND khl.id = khr.id
224 AND khl.deal_type IS NOT NULL
225 AND khs.code = khr.sts_code
226 AND khs.ste_code = 'ACTIVE'
227 AND kls.code (+) = kle.sts_code
228 AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
229 AND tai.trx_status_code = 'PROCESSED'
230 AND tai.id = til.tai_id
231 AND til.id = tld.til_id_details
232 AND tldv.tld_id = tld.id
233 AND tldv.khr_id = khr.id
234 AND tldv.customer_trx_id = ps.customer_trx_id
235 AND tldv.customer_trx_id IS NOT NULL
236 AND PS.TERMS_SEQUENCE_NUMBER = 1
237 AND PS.amount_due_remaining > 0
238 ORDER BY 1, 2, 3;
239
240 --Bug 5000886:End
241
242 ------------------------------------------------------------
243 -- Get trx_id for Invoice
244 ------------------------------------------------------------
245 CURSOR c_trx_id( p_sob_id NUMBER, p_org_id NUMBER ) IS
246 SELECT ID1
247 FROM OKX_CUST_TRX_TYPES_V
248 WHERE name = 'Invoice-OKL' AND
249 set_of_books_id = p_sob_id AND
250 org_id = p_org_id;
251
252
253 /* --Commented out, since this cursor never used--Bug#5484903
254 ------------------------------------------------------------
255 -- Get trx_id for Credit Memo
256 ------------------------------------------------------------
257 CURSOR c_trx_id1( p_sob_id NUMBER, p_org_id NUMBER ) IS
258 SELECT ID1
259 FROM OKX_CUST_TRX_TYPES_V
260 WHERE name = 'Credit Memo-OKL' AND
261 set_of_books_id = p_sob_id AND
262 org_id = p_org_id; */
263
264
265 ------------------------------------------------------------
266 -- Initialise constants
267 ------------------------------------------------------------
268
269
270 l_def_desc CONSTANT VARCHAR2(30) := 'Regular Stream Billing';
271 l_line_code CONSTANT VARCHAR2(30) := 'LINE';
272 l_init_status CONSTANT VARCHAR2(30) := 'ENTERED';
273 l_final_status CONSTANT VARCHAR2(30) := 'PROCESSED';
274 l_trx_type_name CONSTANT VARCHAR2(30) := 'Billing';
275 l_trx_type_lang CONSTANT VARCHAR2(30) := 'US';
276 l_date_entered CONSTANT DATE := SYSDATE;
277 l_zero_amount CONSTANT NUMBER := 0;
278 l_first_line CONSTANT NUMBER := 1;
279 l_line_step CONSTANT NUMBER := 1;
280 l_def_no_val CONSTANT NUMBER := -1;
281 l_null_kle_id CONSTANT NUMBER := -2;
282
283
284 ------------------------------------------------------------
285 -- Declare records: i - insert, u - update, r - result
286 ------------------------------------------------------------
287
288
289 -- Transaction headers
290 i_taiv_rec okl_tai_pvt.taiv_rec_type;
291 u_taiv_rec okl_tai_pvt.taiv_rec_type;
292 lx_taiv_rec okl_tai_pvt.taiv_rec_type;
293 l_init_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
294 r_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
295
296
297 -- Transaction lines
298 i_tilv_rec okl_til_pvt.tilv_rec_type;
299 i_tilv_tbl okl_til_pvt.tilv_tbl_type;
300 lx_tilv_tbl okl_til_pvt.tilv_tbl_type;
301 u_tilv_rec Okl_Txl_Ar_Inv_Lns_Pub.tilv_rec_type;
302 x_tilv_rec Okl_Txl_Ar_Inv_Lns_Pub.tilv_rec_type;
303 l_init_tilv_rec Okl_Txl_Ar_Inv_Lns_Pub.tilv_rec_type;
304 r_tilv_rec Okl_Txl_Ar_Inv_Lns_Pub.tilv_rec_type;
305
306
307 -- Transaction line details
308 i_tldv_rec okl_tld_pvt.tldv_rec_type;
309 i_tldv_tbl okl_tld_pvt.tldv_tbl_type;
310 lx_tldv_tbl okl_tld_pvt.tldv_tbl_type;
311 u_tldv_rec Okl_Txd_Ar_Ln_Dtls_Pub.tldv_rec_type;
312 x_tldv_rec Okl_Txd_Ar_Ln_Dtls_Pub.tldv_rec_type;
313 l_init_tldv_rec Okl_Txd_Ar_Ln_Dtls_Pub.tldv_rec_type;
314 r_tldv_rec Okl_Txd_Ar_Ln_Dtls_Pub.tldv_rec_type;
315
316
317 -- Ext Transaction Header
318 i_xsiv_rec Okl_Ext_Sell_Invs_Pub.xsiv_rec_type;
319 l_init_xsiv_rec Okl_Ext_Sell_Invs_Pub.xsiv_rec_type;
320 r_xsiv_rec Okl_Ext_Sell_Invs_Pub.xsiv_rec_type;
321
322
323 -- Ext Transaction Lines
324 i_xlsv_rec Okl_Xtl_Sell_Invs_Pub.xlsv_rec_type;
325 l_init_xlsv_rec Okl_Xtl_Sell_Invs_Pub.xlsv_rec_type;
326 r_xlsv_rec Okl_Xtl_Sell_Invs_Pub.xlsv_rec_type;
327
328
329 -- Ext Transaction Details
330 i_esdv_rec Okl_Xtd_Sell_Invs_Pub.esdv_rec_type;
331 l_init_esdv_rec Okl_Xtd_Sell_Invs_Pub.esdv_rec_type;
332 r_esdv_rec Okl_Xtd_Sell_Invs_Pub.esdv_rec_type;
333
334
335 -- Stream elements
336 u_selv_rec Okl_Streams_Pub.selv_rec_type;
337 l_init_selv_rec Okl_Streams_Pub.selv_rec_type;
338 r_selv_rec Okl_Streams_Pub.selv_rec_type;
339
340
341 ------------------------------------------------------------
342 -- Declare local variables used in the program
343 ------------------------------------------------------------
344
345
346 l_khr_id okl_trx_ar_invoices_v.khr_id%TYPE;
347 l_bill_date okl_trx_ar_invoices_v.date_invoiced%TYPE;
348 l_trx_type okl_trx_ar_invoices_v.try_id%TYPE;
349 l_kle_id okl_txl_ar_inv_lns_v.kle_id%TYPE;
350
351
352 l_line_number okl_txl_ar_inv_lns_v.line_number%TYPE;
353 l_detail_number okl_txd_ar_ln_dtls_v.line_detail_number%TYPE;
354
355
356 l_header_amount okl_trx_ar_invoices_v.amount%TYPE;
357 l_line_amount okl_txl_ar_inv_lns_v.amount%TYPE;
358
359
360 l_header_id okl_trx_ar_invoices_v.id%TYPE;
361 l_line_id okl_txl_ar_inv_lns_v.id%TYPE;
362
363
364 ------------------------------------------------------------
365 -- Declare variables required by APIs
366 ------------------------------------------------------------
367
368
369 l_api_version CONSTANT NUMBER := 1;
370 l_api_name CONSTANT VARCHAR2(30) := 'BILL_STREAMS';
371 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
372
373
374 ------------------------------------------------------------
375 -- Declare variables to call Accounting Engine.
376 ------------------------------------------------------------
377 p_bpd_acc_rec Okl_Acc_Call_Pub.bpd_acc_rec_type;
378 l_init_bpd_acc_rec Okl_Acc_Call_Pub.bpd_acc_rec_type;
379
380
381
382
383 ------------------------------------------------------------
384 -- Variables for Error Processing and Committing Stream Billing
385 -- Transactions
386 ------------------------------------------------------------
387
388
389 l_error_status VARCHAR2(1);
390 l_error_message VARCHAR2(2000);
391 l_trx_status_code Okl_trx_ar_invoices_v.trx_status_code%TYPE;
392
393
394 -- To enforce commit frequency
395 l_commit_cnt NUMBER;
396 l_max_commit_cnt NUMBER := 500;
397
398
399 -- For currecy precision rounded amount
400 l_ste_amount okl_strm_elements.amount%type;
401 l_curr_code okc_k_headers_b.currency_code%TYPE;
402 ------------------------------------------------------------
403 -- For errors in Stream Elements Table
404 ------------------------------------------------------------
405
406
407 l_distr_cnt NUMBER;
408
409
410 -------------------------------------------------------------------------
411 -- Account Builder Code
412 -------------------------------------------------------------------------
413 l_acc_gen_primary_key_tbl Okl_Account_Dist_Pub.acc_gen_primary_key;
414 l_init_acc_gen_primary_key_tbl Okl_Account_Dist_Pub.acc_gen_primary_key;
415
416
417
418
419 TYPE sel_err_rec_type IS RECORD (
420 sel_id NUMBER,
421 tld_id NUMBER,
422 xsi_id NUMBER,
423 bill_date DATE,
424 contract_number okc_k_headers_b.contract_number%type,
425 stream_purpose okl_strm_type_v.stream_type_purpose%type,
426 amount okl_strm_elements.amount%type,
427 error_message Varchar2(2000)
428 );
429
430
431 TYPE sel_err_tbl_type IS TABLE OF sel_err_rec_type
432 INDEX BY BINARY_INTEGER;
433
434
435 sel_error_log_table sel_err_tbl_type;
436 l_init_sel_table sel_err_tbl_type;
437
438
439 l_sel_tab_index NUMBER;
440
441
442 ------------------------------------------------------------
443 -- Cursors for Rule based values
444 ------------------------------------------------------------
445
446
447 -- Local Vars for Rule based values --EXT
448 l_ext_customer_id Okl_Ext_Sell_Invs_V.customer_id%TYPE;
449 l_ext_receipt_method_id Okl_Ext_Sell_Invs_V.receipt_method_id%TYPE;
450 l_ext_term_id Okl_Ext_Sell_Invs_V.term_id%TYPE;
451 l_ext_sob_id Okl_Ext_Sell_Invs_V.set_of_books_id%TYPE;
452 l_ext_trx_type_id Okl_Ext_Sell_Invs_V.cust_trx_type_id%TYPE;
453 l_ext_addr_id Okl_Ext_Sell_Invs_V.customer_address_id%TYPE;
454 l_ext_cust_bank_id Okl_Ext_Sell_Invs_V.customer_bank_account_id%TYPE;
455 l_addr_id1 OKC_RULES_B.OBJECT1_ID1%TYPE;
456 l_pmth_id1 OKC_RULES_B.OBJECT1_ID1%TYPE;
457 l_bank_id1 OKC_RULES_B.OBJECT1_ID1%TYPE;
458 l_rct_method_code AR_RECEIPT_CLASSES.CREATION_METHOD_CODE%TYPE;
459 l_asst_tax OKC_RULES_B.rule_information1%TYPE;
460 l_asst_line_tax OKC_RULES_B.rule_information1%TYPE;
461 l_product_id okl_k_headers_full_v.pdt_id%TYPE;
462
463
464 -----------------------------------------
465 -- Local Variables for Rebook Credit Memo
466 -- amounts
467 -----------------------------------------
468 l_bill_ajst_amt NUMBER;
469 lx_tai_id NUMBER;
470 l_credit_amount NUMBER;
471
472
473 l_err_status VARCHAR2(1);
474
475
476 TYPE err_rec_type IS RECORD (
477 tai_id NUMBER,
478 trx_number ra_customer_trx_all.trx_number%TYPE,
479 amount NUMBER
480 );
481
482
483 TYPE err_tbl_type IS TABLE OF err_rec_type
484 INDEX BY BINARY_INTEGER;
485
486
487 err_tbl err_tbl_type;
488 l_init_err_tbl err_tbl_type;
489 err_idx NUMBER;
490
491
492 l_commit_cntr NUMBER;
493 l_credit_date DATE;
494 BEGIN
495
496
497 ------------------------------------------------------------
498 -- Start processing
499 ------------------------------------------------------------
500
501
502 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
503
504
505 l_return_status := Okl_Api.START_ACTIVITY(
506 p_api_name => l_api_name,
507 p_pkg_name => G_PKG_NAME,
508 p_init_msg_list => p_init_msg_list,
509 l_api_version => l_api_version,
510 p_api_version => p_api_version,
511 p_api_type => '_PVT',
512 x_return_status => l_return_status);
513
514
515 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
516 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
517 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
518 RAISE Okl_Api.G_EXCEPTION_ERROR;
519 END IF;
520
521
522 ------------------------------------------------------------
523 -- Process all Or specific rebooked Contracts
524 -- with Billing Adjustment lines
525 ------------------------------------------------------------
526 l_commit_cntr := 0;
527
528
529 FOR rbk_ctrct_rec IN rbk_ctrct_csr( p_contract_number ) LOOP
530
531
532 --------------------------------------------------------
533 -- Process Lines with Adjustment amounts
534 --------------------------------------------------------
535 FOR adj_streams_rec IN adj_streams_csr( rbk_ctrct_rec.id ) LOOP
536 FND_FILE.PUT_LINE (FND_FILE.LOG,'Adjustment Amount: '
537 ||adj_streams_rec.amount
538 ||' for Contract: '
539 ||adj_streams_rec.contract_number);
540
541
542 -------------------------------------------
543 -- Track commit batch size
544 -------------------------------------------
545 l_commit_cntr := l_commit_cntr + 1;
546
547
548 IF l_commit_cntr >= 500 THEN
549 COMMIT;
550 l_commit_cntr := 0;
551 END IF;
552 -------------------------------------------
553 -- Create adjustment Invoice if amount > 0
554 -- (This is an unlikely case given the rebook
555 -- process functionality)
556 -------------------------------------------
557 IF adj_streams_rec.amount > 0 THEN
558
559
560 ------------------------------------------------
561 -- Initialize the error processing variables
562 ------------------------------------------------
563 l_err_status := 'S';
564 err_tbl := l_init_err_tbl;
565 err_idx := 0;
566
567
568 i_taiv_rec := l_init_taiv_rec;
569
570
571 i_taiv_rec.trx_status_code := 'SUBMITTED';
572 i_taiv_rec.khr_id := adj_streams_rec.khr_id;
573 i_taiv_rec.amount := adj_streams_rec.amount;
574
575
576 l_trx_type := get_trx_type ('Billing', 'US');
577 i_taiv_rec.try_id := l_trx_type;
578 i_taiv_rec.date_invoiced := adj_streams_rec.bill_date;
579 i_taiv_rec.date_entered := SYSDATE;
580 i_taiv_rec.description := 'Rebook Adjustment Invoice';
581 i_taiv_rec.okl_source_billing_trx := 'REBOOK';
582
583
584 --rkuttiya commented for R12 B Billing Architecture, calling the common Billing API to create Billing Transactions
585
586 i_tilv_rec := l_init_tilv_rec;
587 i_tilv_rec.amount := adj_streams_rec.amount;
588 i_tilv_rec.kle_id := adj_streams_rec.kle_id;
589 i_tilv_rec.inv_receiv_line_code := l_line_code;
590 i_tilv_rec.line_number := 1;
591 i_tilv_rec.txl_ar_line_number := 1;
592 i_tilv_rec.description := 'Rebook Adjustment Invoice';
593 i_tilv_rec.date_bill_period_start := adj_streams_rec.bill_date;
594 i_tilv_rec.sty_id := adj_streams_rec.sty_id; -- 6328168
595
596 i_tilv_tbl(1) := i_tilv_rec;
597
598
599
600
601 i_tldv_rec := l_init_tldv_rec;
602
603
604 i_tldv_rec.amount := adj_streams_rec.amount;
605 i_tldv_rec.line_detail_number := 1;
606 i_tldv_rec.sty_id := adj_streams_rec.sty_id;
607 i_tldv_rec.sel_id := adj_streams_rec.sel_id;
608 i_tldv_rec.description := 'Rebook Adjustment Invoice';
609 i_tldv_rec.txl_ar_line_number := 1;
610
611
612 i_tldv_tbl(1) := i_tldv_rec;
613
614
615 --rkuttiya R12 B Billing Architecture
616 -- call central Billing API to create transaction and accounting distributions
617 OKL_INTERNAL_BILLING_PVT.create_billing_trx( p_api_version
618 => l_api_version
619 ,p_init_msg_list
620 => p_init_msg_list
621 ,x_return_status
622 => l_return_status
623 ,x_msg_count
624 => x_msg_count
625 ,x_msg_data
626 => x_msg_data
627 ,p_taiv_rec
628 => i_taiv_rec
629 ,p_tilv_tbl
630 => i_tilv_tbl
631 ,p_tldv_tbl
632 => i_tldv_tbl
633 ,x_taiv_rec
634 => lx_taiv_rec
635 ,x_tilv_tbl
636 => lx_tilv_tbl
637 ,x_tldv_tbl
638 => lx_tldv_tbl);
639
640
641 ----------------------------------------
642 -- Record tai_id for error processing
643 ----------------------------------------
644 err_idx := err_idx + 1;
645 err_tbl(err_idx).tai_id := lx_taiv_rec.id;
646 err_tbl(err_idx).trx_number := NULL;
647 err_tbl(err_idx).amount := i_taiv_rec.amount;
648
649
650 IF (x_return_status = 'S' ) THEN
651 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Billing Transactions Created.');
652 ELSE
653 l_err_status := 'E';
654 FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR Creating Billing Transactions.');
655 END IF;
656
657
658 IF (x_return_status <> 'S' ) THEN
659 UPDATE okl_trx_ar_invoices_b
660 SET trx_status_code = 'ERROR'
661 WHERE id = lx_taiv_rec.id;
662 FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR Creating Billing Transactions.');
663 ELSE
664 ----------------------------------------------
665 -- Check if there was ever an error and update
666 -- accordingly
667 ----------------------------------------------
668 IF l_err_status = 'E' THEN
669 -----------------------------------------
670 -- Flag all TAI records for this bill adj
671 -- as error
672 -----------------------------------------
673 FOR i in err_tbl.FIRST..err_tbl.LAST LOOP
674 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Reversing Invoice for '||err_tbl(i).amount);
675
676
677 UPDATE okl_trx_ar_invoices_b
678 SET trx_status_code = 'ERROR'
679 WHERE id = err_tbl(i).tai_id;
680 END LOOP;
681
682
683 ELSE
684 UPDATE okl_strm_elements
685 SET date_billed = SYSDATE
686 WHERE id = adj_streams_rec.sel_id;
687 END IF;
688 END IF;
689
690
691 ELSE -- (create Invoice specific credit memo)
692
693
694 ------------------------------------------------
695 -- Initialize the error processing variables
696 ------------------------------------------------
697 l_err_status := 'S';
698 err_tbl := l_init_err_tbl;
699 err_idx := 0;
700
701
702 l_bill_ajst_amt := adj_streams_rec.amount;
703
704
705 FOR unpaid_invs_rec IN unpaid_invs_csr (
706 adj_streams_rec.khr_id,
707 adj_streams_rec.kle_id ) LOOP
708
709
710 IF l_bill_ajst_amt >= 0 THEN
711 EXIT;
712 END IF;
713
714
715 r_taiv_rec := l_init_taiv_rec;
716 l_credit_amount := 0;
717
718
719 IF ( abs(l_bill_ajst_amt) > unpaid_invs_rec.amount_due_remaining )
720 THEN
721 l_credit_amount := unpaid_invs_rec.amount_due_remaining;
722 ELSE
723 l_credit_amount := abs(l_bill_ajst_amt);
724 END IF;
725
726
727 -- ----------------------------
728 -- Credit date
729 -- ----------------------------
730 l_credit_date := NULL;
731 IF unpaid_invs_rec.bill_date > SYSDATE THEN
732 l_credit_date := unpaid_invs_rec.bill_date;
733 ELSE
734 l_credit_date := SYSDATE;
735 END IF;
736
737
738
739
740 --rkuttiya R12 B Billing Architecture
741 -- changed p_lsm_id to p_tld_id
742 okl_credit_memo_pub.insert_request(
743 p_api_version => p_api_version,
744 p_init_msg_list => p_init_msg_list,
745 p_tld_id => unpaid_invs_rec.tld_id,
746 p_credit_amount => (-1*l_credit_amount),
747 p_credit_sty_id => NULL,
748 p_credit_desc => 'Rebook Adjustment Credit Memo',
749 p_credit_date => l_credit_date,
750 p_try_id => NULL,
751 p_transaction_source=>'REBOOK', -- bug 6328168
752 x_tai_id => lx_tai_id,
753 x_taiv_rec => r_taiv_rec,
754 x_return_status => l_return_status,
755 x_msg_count => x_msg_count,
756 x_msg_data => x_msg_data);
757
758
759 ----------------------------------------
760 -- Record tai_id for error processing
761 ----------------------------------------
762 err_idx := err_idx + 1;
763 err_tbl(err_idx).tai_id := lx_tai_id;
764 err_tbl(err_idx).trx_number := unpaid_invs_rec.trx_number;
765 err_tbl(err_idx).amount := l_credit_amount;
766
767
768 IF l_return_status = 'S' THEN
769 FND_FILE.PUT_LINE (FND_FILE.LOG,'Credited AR Invoice: '
770 ||unpaid_invs_rec.trx_number
771 ||' for: '||l_credit_amount);
772 l_bill_ajst_amt := l_bill_ajst_amt + unpaid_invs_rec.amount_due_remaining;
773 ELSE
774 l_err_status := 'E';
775
776
777 FND_FILE.PUT_LINE (FND_FILE.LOG,'ERROR Crediting AR Invoice: '
778 ||unpaid_invs_rec.trx_number
779 ||' for: '||l_credit_amount);
780 EXIT;
781 END IF;
782 END LOOP;
783
784
785 -- If there was more credit than Invoices could use,
786 -- then, create an On-account CM
787 IF l_bill_ajst_amt < 0 THEN
788
789
790 i_taiv_rec := l_init_taiv_rec;
791
792
793 i_taiv_rec.trx_status_code := 'SUBMITTED';
794 i_taiv_rec.khr_id := adj_streams_rec.khr_id;
795 i_taiv_rec.amount := l_bill_ajst_amt;
796
797
798 l_trx_type := get_trx_type ('Credit Memo', 'US');
799 i_taiv_rec.try_id := l_trx_type;
800 i_taiv_rec.date_invoiced := adj_streams_rec.bill_date;
801 i_taiv_rec.date_entered := SYSDATE;
802 i_taiv_rec.description := 'Rebook Adjustment Invoice';
803 i_taiv_rec.okl_source_billing_trx := 'REBOOK';
804
805 i_tilv_rec := l_init_tilv_rec;
806 i_tilv_rec.amount := l_bill_ajst_amt;
807 i_tilv_rec.kle_id := adj_streams_rec.kle_id;
808 i_tilv_rec.inv_receiv_line_code := l_line_code;
809 i_tilv_rec.line_number := 1;
810 i_tilv_rec.description := 'Rebook Adjustment Invoice';
811 i_tilv_rec.date_bill_period_start := adj_streams_rec.bill_date;
812 i_tilv_rec.sty_id := adj_streams_rec.sty_id;
813 -- 6328168
814
815
816 i_tilv_tbl(1) := i_tilv_rec;
817
818
819 i_tldv_rec := l_init_tldv_rec;
820
821
822 i_tldv_rec.amount := l_bill_ajst_amt;
823 i_tldv_rec.line_detail_number := 1;
824 i_tldv_rec.sty_id := adj_streams_rec.sty_id;
825 i_tldv_rec.sel_id := adj_streams_rec.sel_id;
826 i_tldv_rec.txl_ar_line_number := 1;
827
828
829 i_tldv_tbl(1) := i_tldv_rec;
830
831
832 --rkuttiya R12 B Billing Architecture
833 -- call central Billing API to create transaction and accounting distributions
834 OKL_INTERNAL_BILLING_PVT.create_billing_trx( p_api_version
835 => l_api_version
836 ,p_init_msg_list
837 => p_init_msg_list
838 ,x_return_status
839 => l_return_status
840 ,x_msg_count
841 => x_msg_count
842 ,x_msg_data
843 => x_msg_data
844 ,p_taiv_rec
845 => i_taiv_rec
846 ,p_tilv_tbl
847 => i_tilv_tbl
848 ,p_tldv_tbl
849 => i_tldv_tbl
850 ,x_taiv_rec
851 => lx_taiv_rec
852 ,x_tilv_tbl
853 => lx_tilv_tbl
854 ,x_tldv_tbl
855 => lx_tldv_tbl);
856
857
858 ----------------------------------------
859 -- Record tai_id for error processing
860 ----------------------------------------
861 err_idx := err_idx + 1;
862 err_tbl(err_idx).tai_id := lx_taiv_rec.id;
863 err_tbl(err_idx).trx_number := NULL;
864 err_tbl(err_idx).amount := lx_taiv_rec.amount;
865
866
867 IF (x_return_status = 'S' ) THEN
868 FND_FILE.PUT_LINE (FND_FILE.LOG, 'On Account Billing Transactions Created.');
869 ELSE
870 l_err_status := 'E';
871 FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR Creating On Account Billing Transactions.');
872 END IF;
873
874
875
876
877 END IF; -- More Credit remaining than needed
878
879
880 --IF (x_return_status = 'S' ) THEN
881 -- UPDATE okl_strm_elements
882 -- SET date_billed = SYSDATE
883 -- WHERE id = adj_streams_rec.sel_id;
884 --END IF;
885 IF (x_return_status <> 'S' ) THEN
886 IF l_err_status = 'E' THEN
887 -----------------------------------------
888 -- Flag all TAI records for this bill adj
889 -- as error
890 -----------------------------------------
891 FOR i in err_tbl.FIRST..err_tbl.LAST LOOP
892 IF err_tbl(i).trx_number IS NOT NULL THEN
893 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Reversing Credited Invoice '
894 ||err_tbl(i).trx_number
895 ||' for amount '
896 ||err_tbl(i).amount);
897 ELSE
898 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Reversing Credit for amount '
899 ||err_tbl(i).amount);
900 END IF;
901
902
903 UPDATE okl_trx_ar_invoices_b
904 SET trx_status_code = 'ERROR'
905 WHERE id = err_tbl(i).tai_id;
906 END LOOP;
907 ELSE
908 UPDATE okl_strm_elements
909 SET date_billed = SYSDATE
910 WHERE id = adj_streams_rec.sel_id;
911 END IF;
912
913
914 --UPDATE okl_trx_ar_invoices_b
915 --SET trx_status_code = 'ERROR'
916 --WHERE id = x_taiv_rec.id;
917 --FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR Creating Accounting Distributions.');
918 ELSE
919 IF l_err_status = 'E' THEN
920 -----------------------------------------
921 -- Flag all TAI records for this bill adj
922 -- as error
923 -----------------------------------------
924 FOR i in err_tbl.FIRST..err_tbl.LAST LOOP
925 IF err_tbl(i).trx_number IS NOT NULL THEN
926 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Reversing Credited Invoice '
927 ||err_tbl(i).trx_number
928 ||' for amount '
929 ||err_tbl(i).amount);
930 ELSE
931 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Reversing Credit for amount '
932 ||err_tbl(i).amount);
933 END IF;
934
935
936 UPDATE okl_trx_ar_invoices_b
937 SET trx_status_code = 'ERROR'
938 WHERE id = err_tbl(i).tai_id;
939 END LOOP;
940 ELSE
941 UPDATE okl_strm_elements
942 SET date_billed = SYSDATE
943 WHERE id = adj_streams_rec.sel_id;
944 END IF;
945 END IF;
946
947
948 END IF;
949 END LOOP;
950 END LOOP;
951
952
953 ------------------------------------------------------------
954 -- End processing
955 ------------------------------------------------------------
956
957
958 Okl_Api.END_ACTIVITY (
959 x_msg_count => x_msg_count,
960 x_msg_data => x_msg_data);
961
962
963
964
965 EXCEPTION
966
967
968 ------------------------------------------------------------
969 -- Exception handling
970 ------------------------------------------------------------
971
972
973 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
974 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (EXCP) => '||SQLERRM);
975 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
976 p_api_name => l_api_name,
977 p_pkg_name => G_PKG_NAME,
978 p_exc_name => 'Okl_Api.G_RET_STS_ERROR',
979 x_msg_count => x_msg_count,
980 x_msg_data => x_msg_data,
981 p_api_type => '_PVT');
982
983
984 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
985 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (UNEXP) => '||SQLERRM);
986 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
987 p_api_name => l_api_name,
988 p_pkg_name => G_PKG_NAME,
989 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
990 x_msg_count => x_msg_count,
991 x_msg_data => x_msg_data,
992 p_api_type => '_PVT');
993
994
995 WHEN OTHERS THEN
996 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (OTHERS) => '||SQLERRM);
997 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
998 p_api_name => l_api_name,
999 p_pkg_name => G_PKG_NAME,
1000 p_exc_name => 'OTHERS',
1001 x_msg_count => x_msg_count,
1002 x_msg_data => x_msg_data,
1003 p_api_type => '_PVT');
1004
1005
1006 END CM_Bill_adjustments;
1007
1008
1009
1010
1011
1012
1013 PROCEDURE CUSTOM_CM_Bill_adjustments
1014 (p_api_version IN NUMBER
1015 ,p_init_msg_list IN VARCHAR2
1016 ,x_return_status OUT NOCOPY VARCHAR2
1017 ,x_msg_count OUT NOCOPY NUMBER
1018 ,x_msg_data OUT NOCOPY VARCHAR2
1019 ,p_contract_number IN VARCHAR2
1020 ) IS
1021 BEGIN
1022
1023
1024 NULL;
1025
1026
1027 END CUSTOM_CM_Bill_adjustments;
1028
1029
1030
1031
1032 PROCEDURE ON_ACCT_Bill_adjustments
1033 (p_api_version IN NUMBER
1034 ,p_init_msg_list IN VARCHAR2
1035 ,x_return_status OUT NOCOPY VARCHAR2
1036 ,x_msg_count OUT NOCOPY NUMBER
1037 ,x_msg_data OUT NOCOPY VARCHAR2
1038 ,p_contract_number IN VARCHAR2
1039 )
1040 IS
1041
1042
1043
1044
1045 ------------------------------------------------------------
1046 -- Get Rebooked Contracts
1047 ------------------------------------------------------------
1048 CURSOR rbk_ctrct_csr ( p_contract_number VARCHAR2 ) IS
1049 SELECT distinct KHR.ID
1050 FROM OKC_K_HEADERS_B KHR,
1051 OKL_STREAMS STM,
1052 OKL_STRM_TYPE_B STY,
1053 OKL_STRM_ELEMENTS SEL
1054 WHERE KHR.contract_number = NVL(p_contract_number, contract_number)
1055 AND KHR.STS_CODE = 'BOOKED'
1056 AND KHR.ID = STM.KHR_ID
1057 AND STM.sty_id = STY.id
1058 AND STY.STREAM_TYPE_PURPOSE = 'REBOOK_BILLING_ADJUSTMENT'
1059 AND STM.active_yn = 'Y'
1060 AND sel.stm_id = stm.id
1061 AND SEL.DATE_BILLED IS NULL;
1062
1063
1064 /* SELECT id */
1065 /* FROM okc_k_headers_b */
1066 /* WHERE contract_number = NVL(p_contract_number, contract_number)
1067 AND */
1068 /* sts_code = 'BOOKED' AND */
1069 /* id in ( */
1070 /* SELECT rebook.id */
1071 /* FROM okc_k_headers_b orig, */
1072 /* okc_k_headers_b rebook */
1073 /* WHERE orig.contract_number = NVL(p_contract_number,
1074 orig.contract_number) and */
1075 /* orig.authoring_org_id =
1076 NVL(TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),1,10)),-99) and */
1077 /* rebook.orig_system_id1 = orig.id and */
1078 /* rebook.orig_system_source_code = 'OKL_REBOOK' */
1079 /* UNION */
1080 /* SELECT mass_rebook.id */
1081 /* FROM okc_k_headers_b mass_rebook, */
1082 /* okl_rbk_selected_contract rbk */
1083 /* WHERE mass_rebook.contract_number = NVL(p_contract_number,
1084 mass_rebook.contract_number) and */
1085 /* mass_rebook.authoring_org_id =
1086 NVL(TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'),1,10)),-99) and */
1087 /* rbk.khr_id = mass_rebook.id) */
1088 /* order by 1; */
1089
1090
1091
1092
1093 ------------------------------------------------------------
1094 -- Get Rebooked Contracts with a Billing Adjustment
1095 -- Stream Type
1096 ------------------------------------------------------------
1097 CURSOR adj_streams_csr( p_khr_id NUMBER ) IS
1098 SELECT stm.khr_id khr_id,
1099 TRUNC (ste.stream_element_date) bill_date,
1100 stm.kle_id kle_id,
1101 ste.id sel_id,
1102 stm.sty_id sty_id,
1103 khr.contract_number contract_number,
1104 khr.currency_code currency_code,
1105 khr.authoring_org_id authoring_org_id,
1106 --sty.name stream_name,
1107 sty.taxable_default_yn taxable_default_yn,
1108 ste.amount amount,
1109 khr.sts_code sts_code
1110 FROM
1111 okl_strm_elements ste,
1112 okl_streams stm,
1113 okl_strm_type_b sty,
1114 okc_k_headers_b khr,
1115 okl_k_headers khl,
1116 okc_k_lines_b kle,
1117 okc_statuses_b khs,
1118 okc_statuses_b kls
1119 WHERE ste.amount <> 0
1120 AND stm.id = ste.stm_id
1121 AND ste.date_billed IS NULL
1122 AND stm.active_yn = 'Y'
1123 -- AND stm.say_code = 'CURR'
1124 AND sty.id = stm.sty_id
1125 AND sty.stream_type_purpose = 'REBOOK_BILLING_ADJUSTMENT'
1126 -- AND sty.billable_yn = 'Y'
1127 AND khr.id = stm.khr_id
1128 AND khr.scs_code IN ('LEASE', 'LOAN')
1129 AND khr.sts_code = 'BOOKED'
1130 AND khr.id = p_khr_id
1131 -- AND khr.contract_number =
1132 -- NVL (NULL, khr.contract_number)
1133 AND khl.id = stm.khr_id
1134 AND khl.deal_type IS NOT NULL
1135 AND khs.code = khr.sts_code
1136 AND khs.ste_code = 'ACTIVE'
1137 AND kle.id (+)= stm.kle_id
1138 AND kls.code (+)= kle.sts_code
1139 AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
1140 ORDER BY 1, 2, 3;
1141
1142
1143 ------------------------------------------------------------
1144 -- Declare variables required by APIs
1145 ------------------------------------------------------------
1146
1147
1148 l_api_version CONSTANT NUMBER := 1;
1149 l_api_name CONSTANT VARCHAR2(30) := 'BILL_STREAMS';
1150 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1151
1152
1153 ------------------------------------------------------------
1154 -- Declare variables to call Accounting Engine.
1155 ------------------------------------------------------------
1156 p_bpd_acc_rec
1157 Okl_Acc_Call_Pub.bpd_acc_rec_type;
1158 l_init_bpd_acc_rec
1159 Okl_Acc_Call_Pub.bpd_acc_rec_type;
1160
1161
1162 ------------------------------------------------------------
1163 -- Declare records: i - insert, u - update, r - result
1164 ------------------------------------------------------------
1165
1166
1167 -- Transaction headers
1168 i_taiv_rec okl_tai_pvt.taiv_rec_type;
1169 u_taiv_rec okl_tai_pvt.taiv_rec_type;
1170 lx_taiv_rec okl_tai_pvt.taiv_rec_type;
1171 l_init_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
1172 r_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
1173
1174
1175 -- Transaction lines
1176 i_tilv_rec okl_til_pvt.tilv_rec_type;
1177 i_tilv_tbl okl_til_pvt.tilv_tbl_type;
1178 u_tilv_rec okl_til_pvt.tilv_rec_type;
1179 lx_tilv_tbl okl_til_pvt.tilv_tbl_type;
1180 l_init_tilv_rec okl_til_pvt.tilv_rec_type;
1181 r_tilv_rec Okl_Txl_Ar_Inv_Lns_Pub.tilv_rec_type;
1182
1183
1184 -- Transaction line details
1185 i_tldv_rec okl_tld_pvt .tldv_rec_type;
1186 i_tldv_tbl okl_tld_pvt.tldv_tbl_type;
1187 u_tldv_rec okl_tld_pvt.tldv_rec_type;
1188 lx_tldv_tbl okl_tld_pvt.tldv_tbl_type;
1189 l_init_tldv_rec okl_tld_pvt.tldv_rec_type;
1190 r_tldv_rec okl_tld_pvt.tldv_rec_type;
1191
1192
1193 l_trx_type okl_trx_ar_invoices_v.try_id%TYPE;
1194 l_line_code CONSTANT VARCHAR2(30) := 'LINE';
1195
1196
1197 -----------------------------------------
1198 -- Local Variables for Rebook Credit Memo
1199 -- amounts
1200 -----------------------------------------
1201 l_bill_ajst_amt NUMBER;
1202
1203
1204
1205
1206 BEGIN
1207
1208
1209 ------------------------------------------------------------
1210 -- Start processing
1211 ------------------------------------------------------------
1212
1213
1214 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1215
1216
1217 l_return_status := Okl_Api.START_ACTIVITY(
1218 p_api_name => l_api_name,
1219 p_pkg_name => G_PKG_NAME,
1220 p_init_msg_list => p_init_msg_list,
1221 l_api_version => l_api_version,
1222 p_api_version => p_api_version,
1223 p_api_type => '_PVT',
1224 x_return_status => l_return_status);
1225
1226
1227 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
1228 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
1229 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
1230 RAISE Okl_Api.G_EXCEPTION_ERROR;
1231 END IF;
1232
1233
1234 ------------------------------------------------------------
1235 -- Process all Or specific rebooked Contracts
1236 -- with Billing Adjustment lines
1237 ------------------------------------------------------------
1238 FOR rbk_ctrct_rec IN rbk_ctrct_csr( p_contract_number ) LOOP
1239
1240
1241 --------------------------------------------------------
1242 -- Process Lines with Adjustment amounts
1243 --------------------------------------------------------
1244 FOR adj_streams_rec IN adj_streams_csr( rbk_ctrct_rec.id ) LOOP
1245 FND_FILE.PUT_LINE (FND_FILE.LOG,'Adjustment Amount: '
1246 ||adj_streams_rec.amount
1247 ||' for Contract: '
1248 ||adj_streams_rec.contract_number);
1249
1250
1251
1252
1253 -------------------------------------------
1254 -- Create adjustment Invoice if amount > 0
1255 -------------------------------------------
1256 IF adj_streams_rec.amount > 0 THEN
1257
1258
1259 i_taiv_rec := l_init_taiv_rec;
1260
1261
1262 i_taiv_rec.trx_status_code := 'SUBMITTED';
1263 i_taiv_rec.khr_id := adj_streams_rec.khr_id;
1264 i_taiv_rec.amount := adj_streams_rec.amount;
1265
1266
1267 l_trx_type := get_trx_type ('Billing', 'US');
1268 i_taiv_rec.try_id := l_trx_type;
1269 i_taiv_rec.date_invoiced := adj_streams_rec.bill_date;
1270 i_taiv_rec.date_entered := SYSDATE;
1271 i_taiv_rec.description := 'Rebook Adjustment Invoice';
1272 i_taiv_rec.okl_source_billing_trx := 'REBOOK';
1273
1274
1275 i_tilv_rec := l_init_tilv_rec;
1276 i_tilv_rec.amount := adj_streams_rec.amount;
1277 i_tilv_rec.kle_id := adj_streams_rec.kle_id;
1278 i_tilv_rec.inv_receiv_line_code := l_line_code;
1279 i_tilv_rec.line_number := 1;
1280 i_tilv_rec.txl_ar_line_number := 1;
1281 i_tilv_rec.description := 'Rebook Adjustment Invoice';
1282 i_tilv_rec.date_bill_period_start := adj_streams_rec.bill_date;
1283
1284
1285 i_tilv_tbl(1) := i_tilv_rec;
1286
1287
1288 i_tldv_rec := l_init_tldv_rec;
1289
1290
1291 i_tldv_rec.amount := adj_streams_rec.amount;
1292 i_tldv_rec.line_detail_number := 1;
1293 i_tldv_rec.sty_id := adj_streams_rec.sty_id;
1294 i_tldv_rec.sel_id := adj_streams_rec.sel_id;
1295 i_tldv_rec.description := 'Rebook Adjustment Invoice';
1296 i_tldv_rec.txl_ar_line_number := 1;
1297
1298
1299 i_tldv_tbl(1) := i_tldv_rec;
1300
1301
1302 --rkuttiya R12 B Billing Architecture
1303 --call to central Billing API to create Billing transactions and accounting distributions
1304
1305
1306 OKL_INTERNAL_BILLING_PVT.create_billing_trx( p_api_version
1307 => l_api_version
1308 ,p_init_msg_list
1309 => p_init_msg_list
1310 ,x_return_status
1311 => l_return_status
1312 ,x_msg_count
1313 => x_msg_count
1314 ,x_msg_data
1315 => x_msg_data
1316 ,p_taiv_rec
1317 => i_taiv_rec
1318 ,p_tilv_tbl
1319 => i_tilv_tbl
1320 ,p_tldv_tbl
1321 => i_tldv_tbl
1322 ,x_taiv_rec
1323 => lx_taiv_rec
1324 ,x_tilv_tbl
1325 => lx_tilv_tbl
1326 ,x_tldv_tbl
1327 => lx_tldv_tbl);
1328
1329
1330 IF (x_return_status = 'S' ) THEN
1331 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Billing Transactions Created.');
1332 ELSE
1333 FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR Creating Billing Transactions.');
1334 END IF;
1335
1336
1337 IF (x_return_status <> 'S' ) THEN
1338 UPDATE okl_trx_ar_invoices_b
1339 SET trx_status_code = 'ERROR'
1340 WHERE id = lx_taiv_rec.id;
1341 FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR Creating Billing Transactions.');
1342 ELSE
1343 UPDATE okl_strm_elements
1344 SET date_billed = SYSDATE
1345 WHERE id = adj_streams_rec.sel_id;
1346 END IF;
1347
1348
1349 ELSE -- (create Invoice specific credit memo)
1350
1351
1352 l_bill_ajst_amt := adj_streams_rec.amount;
1353
1354
1355 IF l_bill_ajst_amt < 0 THEN
1356
1357
1358 i_taiv_rec := l_init_taiv_rec;
1359
1360
1361 i_taiv_rec.trx_status_code := 'SUBMITTED';
1362 i_taiv_rec.khr_id := adj_streams_rec.khr_id;
1363 i_taiv_rec.amount := l_bill_ajst_amt;
1364
1365
1366 l_trx_type := get_trx_type ('Credit Memo',
1367 'US');
1368 i_taiv_rec.try_id := l_trx_type;
1369 i_taiv_rec.date_invoiced := adj_streams_rec.bill_date;
1370 i_taiv_rec.date_entered := SYSDATE;
1371 i_taiv_rec.description := 'Rebook Adjustment Invoice';
1372 i_taiv_rec.okl_source_billing_trx := 'REBOOK';
1373
1374
1375 i_tilv_rec := l_init_tilv_rec;
1376 i_tilv_rec.amount := l_bill_ajst_amt;
1377 i_tilv_rec.kle_id := adj_streams_rec.kle_id;
1378 i_tilv_rec.inv_receiv_line_code := l_line_code;
1379 i_tilv_rec.line_number := 1;
1380 i_tilv_rec.txl_ar_line_number := 1;
1381 i_tilv_rec.description := 'Rebook Adjustment Invoice';
1382 i_tilv_rec.date_bill_period_start :=
1383 adj_streams_rec.bill_date;
1384 i_tilv_rec.sty_id := adj_streams_rec.sty_id;--6328168
1385
1386
1387 i_tilv_tbl(1) := i_tilv_rec;
1388
1389
1390 i_tldv_rec := l_init_tldv_rec;
1391
1392
1393 i_tldv_rec.amount := l_bill_ajst_amt;
1394 i_tldv_rec.line_detail_number := 1;
1395 i_tldv_rec.sty_id := adj_streams_rec.sty_id;
1396 i_tldv_rec.sel_id := adj_streams_rec.sel_id;
1397 i_tldv_rec.description := 'Rebook Adjustment Invoice';
1398 i_tldv_rec.txl_ar_line_number := 1;
1399
1400
1401 i_tldv_tbl(1) := i_tldv_rec;
1402
1403
1404 --rkuttiya R12 B Billing Architecture
1405 --call to central Billing API to create Billing transactions and accounting distributions
1406
1407
1408 OKL_INTERNAL_BILLING_PVT.create_billing_trx( p_api_version
1409 => l_api_version
1410 ,p_init_msg_list
1411 => p_init_msg_list
1412 ,x_return_status
1413 => l_return_status
1414 ,x_msg_count
1415 => x_msg_count
1416 ,x_msg_data
1417 => x_msg_data
1418 ,p_taiv_rec
1419 => i_taiv_rec
1420 ,p_tilv_tbl
1421 => i_tilv_tbl
1422 ,p_tldv_tbl
1423 => i_tldv_tbl
1424 ,x_taiv_rec
1425 => lx_taiv_rec
1426 ,x_tilv_tbl
1427 => lx_tilv_tbl
1428 ,x_tldv_tbl
1429 => lx_tldv_tbl);
1430
1431
1432 IF (x_return_status = 'S' ) THEN
1433 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Billing Transactions Created');
1434 ELSE
1435 FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR Creating Billing Transactions.');
1436 END IF;
1437
1438
1439 IF (x_return_status <> 'S' ) THEN
1440 UPDATE okl_trx_ar_invoices_b
1441 SET trx_status_code = 'ERROR'
1442 WHERE id = lx_taiv_rec.id;
1443 FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR Creating Billing Transactions.');
1444 ELSE
1445 UPDATE okl_strm_elements
1446 SET date_billed = SYSDATE
1447 WHERE id = adj_streams_rec.sel_id;
1448 END IF;
1449
1450
1451 END IF;
1452 END IF;
1453 END LOOP;
1454 END LOOP;
1455
1456
1457 ------------------------------------------------------------
1458 -- End processing
1459 ------------------------------------------------------------
1460
1461
1462 Okl_Api.END_ACTIVITY (
1463 x_msg_count => x_msg_count,
1464 x_msg_data => x_msg_data);
1465
1466
1467 EXCEPTION
1468
1469
1470 ------------------------------------------------------------
1471 -- Exception handling
1472 ------------------------------------------------------------
1473
1474
1475 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
1476 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (EXCP) => '||SQLERRM);
1477 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
1478 p_api_name => l_api_name,
1479 p_pkg_name => G_PKG_NAME,
1480 p_exc_name => 'Okl_Api.G_RET_STS_ERROR',
1481 x_msg_count => x_msg_count,
1482 x_msg_data => x_msg_data,
1483 p_api_type => '_PVT');
1484
1485
1486 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
1487 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (UNEXP) => '||SQLERRM);
1488 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
1489 p_api_name => l_api_name,
1490 p_pkg_name => G_PKG_NAME,
1491 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
1492 x_msg_count => x_msg_count,
1493 x_msg_data => x_msg_data,
1494 p_api_type => '_PVT');
1495
1496
1497 WHEN OTHERS THEN
1498 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (OTHERS) => '||SQLERRM);
1499 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
1500 p_api_name => l_api_name,
1501 p_pkg_name => G_PKG_NAME,
1502 p_exc_name => 'OTHERS',
1503 x_msg_count => x_msg_count,
1504 x_msg_data => x_msg_data,
1505 p_api_type => '_PVT');
1506
1507
1508 END ON_ACCT_Bill_adjustments;
1509
1510
1511
1512
1513 PROCEDURE CM_Bill_adjustments_conc (
1514 errbuf OUT NOCOPY VARCHAR2
1515 ,retcode OUT NOCOPY NUMBER
1516 ,p_contract_number IN VARCHAR2
1517 ) IS
1518
1519
1520 l_api_version NUMBER := 1;
1521 lx_msg_count NUMBER;
1522 l_from_bill_date DATE;
1523 l_to_bill_date DATE;
1524 l_count1 NUMBER :=0;
1525 l_count2 NUMBER :=0;
1526 l_count NUMBER :=0;
1527 I NUMBER :=0;
1528 l_msg_index_out NUMBER :=0;
1529 lx_msg_data VARCHAR2(450);
1530 lx_return_status VARCHAR2(1);
1531
1532
1533 l_request_id NUMBER;
1534
1535
1536 CURSOR req_id_csr IS
1537 SELECT
1538 DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID)
1539 FROM dual;
1540
1541
1542 CURSOR txd_cnt_succ_csr( p_req_id NUMBER ) IS
1543 SELECT count(*)
1544 FROM okl_trx_ar_invoices_v a,
1545 okl_txl_ar_inv_lns_v b,
1546 okl_txd_ar_ln_dtls_v c
1547 WHERE a.id = b.tai_id AND
1548 b.id = c.til_id_details AND
1549 a.trx_status_code = 'SUBMITTED' AND
1550 a.request_id = p_req_id ;
1551
1552
1553 CURSOR txd_cnt_err_csr( p_req_id NUMBER ) IS
1554 SELECT count(*)
1555 FROM okl_trx_ar_invoices_v a,
1556 okl_txl_ar_inv_lns_v b,
1557 okl_txd_ar_ln_dtls_v c
1558 WHERE a.id = b.tai_id AND
1559 b.id = c.til_id_details AND
1560 a.trx_status_code = 'ERROR' AND
1561 a.request_id = p_req_id ;
1562
1563
1564 l_succ_cnt NUMBER;
1565 l_err_cnt NUMBER;
1566 BEGIN
1567
1568
1569 l_succ_cnt := 0;
1570 l_err_cnt := 0;
1571
1572
1573 -- Get the request Id
1574 l_request_id := NULL;
1575 OPEN req_id_csr;
1576 FETCH req_id_csr INTO l_request_id;
1577 CLOSE req_id_csr;
1578
1579
1580 FND_FILE.PUT_LINE (FND_FILE.LOG, '** * **');
1581 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Contract Number = ' ||p_contract_number);
1582 FND_FILE.PUT_LINE (FND_FILE.LOG, '** * **');
1583
1584
1585 IF fnd_profile.value('OKL_APPLY_CM') IS NULL THEN
1586 -- On Account Credit Memo For Manual application
1587
1588
1589 FND_FILE.PUT_LINE (FND_FILE.LOG,
1590 '******************************************');
1591 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Profile OKL:Apply Billing Adjustment: NULL');
1592 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Create On-Invoice Credit Memo.');
1593 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Invoking OKL_REBOOK_CM_PVT.ON_ACCT_Bill_adjustments');
1594 FND_FILE.PUT_LINE (FND_FILE.LOG,
1595 '******************************************');
1596
1597
1598 ON_ACCT_Bill_adjustments (
1599 p_api_version => l_api_version,
1600 p_init_msg_list => Okl_Api.G_FALSE,
1601 x_return_status => lx_return_status,
1602 x_msg_count => lx_msg_count,
1603 x_msg_data => errbuf,
1604 p_contract_number => p_contract_number
1605 );
1606
1607
1608 ELSIF (fnd_profile.value('OKL_APPLY_CM') = 'SEEDED') THEN
1609 -- Development Logic
1610 FND_FILE.PUT_LINE (FND_FILE.LOG,
1611 '******************************************');
1612 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Profile OKL:Apply Billing Adjustment: SEEDED');
1613 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Create On-Invoice and On-Acct Credit Memo.');
1614 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Invoking OKL_REBOOK_CM_PVT.CM_Bill_adjustments');
1615 FND_FILE.PUT_LINE (FND_FILE.LOG,
1616 '******************************************');
1617
1618
1619 CM_Bill_adjustments (
1620 p_api_version => l_api_version,
1621 p_init_msg_list => Okl_Api.G_FALSE,
1622 x_return_status => lx_return_status,
1623 x_msg_count => lx_msg_count,
1624 x_msg_data => errbuf,
1625 p_contract_number => p_contract_number
1626 );
1627
1628
1629 ELSIF (fnd_profile.value('OKL_APPLY_CM') = 'CUSTOM') THEN
1630
1631
1632 -- Custom Logic
1633 FND_FILE.PUT_LINE (FND_FILE.LOG,
1634 '******************************************');
1635 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Profile OKL:Apply Billing Adjustment: CUSTOM');
1636 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Create Credit Memo using CUSTOM Logic.');
1637 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Invoking OKL_REBOOK_CM_PVT.CUSTOM_CM_Bill_adjustments');
1638 FND_FILE.PUT_LINE (FND_FILE.LOG,
1639 '******************************************');
1640
1641
1642 OKL_CUSTOM_PVT.CUSTOM_CM_Bill_adjustments (
1643 p_api_version => l_api_version,
1644 p_init_msg_list => Okl_Api.G_FALSE,
1645 x_return_status => lx_return_status,
1646 x_msg_count => lx_msg_count,
1647 x_msg_data => errbuf,
1648 p_contract_number => p_contract_number
1649 );
1650
1651
1652 ELSE
1653 FND_FILE.PUT_LINE (FND_FILE.LOG,
1654 '******************************************');
1655 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Profile OKL:Apply Billing Adjustment: '||fnd_profile.value('OKL_APPLY_CM'));
1656 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Acceptable Values are: NULL, SEEDED OR CUSTOM.');
1657 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Exiting Procedure ..');
1658 FND_FILE.PUT_LINE (FND_FILE.LOG,
1659 '******************************************');
1660 END IF;
1661
1662
1663 -- Success Count
1664 OPEN txd_cnt_succ_csr( l_request_id );
1665 FETCH txd_cnt_succ_csr INTO l_succ_cnt;
1666 CLOSE txd_cnt_succ_csr;
1667
1668
1669 -- Error Count
1670 OPEN txd_cnt_err_csr( l_request_id );
1671 FETCH txd_cnt_err_csr INTO l_err_cnt;
1672 CLOSE txd_cnt_err_csr;
1673
1674
1675 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Automatic Adjustments for Rebooked Contracts');
1676 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, '************************************');
1677 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Program Run Date: '||SYSDATE||' Request Id: '||l_request_id);
1678 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
1679 '***********************************************');
1680 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'PARAMETERS');
1681 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Contract Number = '
1682 ||p_contract_number);
1683 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,
1684 '***********************************************');
1685
1686
1687 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Number of Successful Stream Lines in Okl_Txd_Ar_Ln_Dtls_b = '||l_succ_cnt);
1688 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Number of Errored Stream Lines in Okl_Txd_Ar_Ln_Dtls_b = '||l_err_cnt);
1689 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Detailed Error Messages For Each Records and Columns from TAPI ');
1690
1691
1692 IF lx_msg_count > 0 THEN
1693 FOR i IN 1..lx_msg_count LOOP
1694 fnd_msg_pub.get (p_msg_index => i,
1695 p_encoded => 'F',
1696 p_data => lx_msg_data,
1697 p_msg_index_out => l_msg_index_out);
1698
1699
1700 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,TO_CHAR(i) || ': ' ||
1701 lx_msg_data);
1702 END LOOP;
1703 END IF;
1704 EXCEPTION
1705 WHEN OTHERS THEN
1706 NULL ;
1707 END CM_Bill_adjustments_conc;
1708
1709
1710 ------------------------------------------------------------------
1711 -- Procedure CM_Bill_adjustments to create adjustments to
1712 -- Rebooked and unfulfilled invoices
1713 ------------------------------------------------------------------
1714
1715
1716 PROCEDURE CM_Bill_adjustments
1717 (p_api_version IN NUMBER
1718 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1719 ,x_return_status OUT NOCOPY VARCHAR2
1720 ,x_msg_count OUT NOCOPY NUMBER
1721 ,x_msg_data OUT NOCOPY VARCHAR2
1722 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
1723 ,p_rebook_adj_tbl IN rebook_adj_tbl_type
1724 ) IS
1725
1726
1727 ------------------------------------------------------------
1728 -- Get Unpaid Invoices For the Contract Stream
1729 ------------------------------------------------------------
1730 -- Bug 6802801
1731 -- Bug# 7720775: Changed API call to
1732 -- OKL_BILLING_UTIL_PVT.INV_LN_AMT_REMAINING_W_INCTAX
1733 -- Added parameter p_sty_id
1734 CURSOR unpaid_invs_csr ( p_khr_id NUMBER, p_kle_id NUMBER, p_sty_id NUMBER ) IS
1735 SELECT
1736 tai.date_entered date_billed,
1737 khr.id khr_id,
1738 TRUNC (tai.date_invoiced) bill_date,
1739 tld.kle_id kle_id,
1740 tld.sel_id sel_id,
1741 tld.sty_id sty_id,
1742 khr.contract_number contract_number,
1743 khr.currency_code currency_code,
1744 khr.authoring_org_id authoring_org_id,
1745 sty.code comments,
1746 sty.taxable_default_yn taxable_default_yn,
1747 OKL_BILLING_UTIL_PVT.INV_LN_AMT_ORIG_WOTAX
1748 (tldv.CUSTOMER_TRX_ID, tldv.CUSTOMER_TRX_LINE_ID) amount,
1749 khr.sts_code sts_code,
1750 tld.id tld_id,
1751 OKL_BILLING_UTIL_PVT.INV_LN_AMT_REMAINING_W_INCTAX
1752 (tldv.CUSTOMER_TRX_ID, tldv.CUSTOMER_TRX_LINE_ID) amount_due_remaining,
1753 PS.trx_number trx_number,
1754 PS.class,
1755 PS.TERMS_SEQUENCE_NUMBER
1756 FROM
1757 okl_strm_type_b sty,
1758 okc_k_headers_b khr,
1759 okl_k_headers khl,
1760 okc_k_lines_b kle,
1761 okc_statuses_b khs,
1762 okc_statuses_b kls,
1763 okl_txd_ar_ln_dtls_v tld,
1764 okl_txl_ar_inv_lns_v til,
1765 okl_trx_ar_invoices_v tai,
1766 okl_bpd_tld_ar_lines_v tldv,
1767 AR_PAYMENT_SCHEDULES_ALL PS
1768 WHERE sty.billable_yn = 'Y'
1769 AND tld.sty_id = p_sty_id
1770 AND tld.sty_id = sty.id
1771 AND khr.id = p_khr_id
1772 AND khr.scs_code IN ('LEASE', 'LOAN')
1773 AND khr.sts_code IN ( 'BOOKED','EVERGREEN')
1774 AND tld.kle_id = p_kle_id
1775 AND tld.kle_id = kle.id
1776 AND khl.id = khr.id
1777 AND khl.deal_type IS NOT NULL
1778 AND khs.code = khr.sts_code
1779 AND khs.ste_code = 'ACTIVE'
1780 AND kls.code (+) = kle.sts_code
1781 AND NVL (kls.ste_code, 'ACTIVE') IN ('ACTIVE', 'TERMINATED')
1782 AND tai.trx_status_code = 'PROCESSED'
1783 AND tai.id = til.tai_id
1784 AND til.id = tld.til_id_details
1785 AND tldv.tld_id = tld.id
1786 AND tldv.khr_id = khr.id
1787 AND tldv.customer_trx_id = ps.customer_trx_id
1788 AND tldv.customer_trx_id IS NOT NULL
1789 AND PS.TERMS_SEQUENCE_NUMBER = 1
1790 AND PS.amount_due_remaining > 0
1791 ORDER BY 1, 2, 3;
1792
1793 --Bug 5000886 : Removed reference of stream tables in the following cursor
1794 -- Bug 6328168: Modified unpaid_invs_csr cursor
1795
1796 --Bug 5000886: End
1797
1798
1799 ------------------------------------------------------------
1800 -- Get trx_id for Invoice
1801 ------------------------------------------------------------
1802 CURSOR c_trx_id( p_sob_id NUMBER, p_org_id NUMBER ) IS
1803 SELECT ID1
1804 FROM OKX_CUST_TRX_TYPES_V
1805 WHERE name = 'Invoice-OKL' AND
1806 set_of_books_id = p_sob_id AND
1807 org_id = p_org_id;
1808
1809
1810 /* -- commented out since this cursor never used - bug#5484903
1811 ------------------------------------------------------------
1812 -- Get trx_id for Credit Memo
1813 ------------------------------------------------------------
1814 CURSOR c_trx_id1( p_sob_id NUMBER, p_org_id NUMBER ) IS
1815 SELECT ID1
1816 FROM OKX_CUST_TRX_TYPES_V
1817 WHERE name = 'Credit Memo-OKL' AND
1818 set_of_books_id = p_sob_id AND
1819 org_id = p_org_id; */
1820
1821
1822 ------------------------------------------------------------
1823 -- Initialise constants
1824 ------------------------------------------------------------
1825
1826
1827 l_def_desc CONSTANT VARCHAR2(30) := 'Regular Stream Billing';
1828 l_line_code CONSTANT VARCHAR2(30) := 'LINE';
1829 l_init_status CONSTANT VARCHAR2(30) := 'ENTERED';
1830 l_final_status CONSTANT VARCHAR2(30) := 'PROCESSED';
1831 l_trx_type_name CONSTANT VARCHAR2(30) := 'Billing';
1832 l_trx_type_lang CONSTANT VARCHAR2(30) := 'US';
1833 l_date_entered CONSTANT DATE := SYSDATE;
1834 l_zero_amount CONSTANT NUMBER := 0;
1835 l_first_line CONSTANT NUMBER := 1;
1836 l_line_step CONSTANT NUMBER := 1;
1837 l_def_no_val CONSTANT NUMBER := -1;
1838 l_null_kle_id CONSTANT NUMBER := -2;
1839
1840
1841 ------------------------------------------------------------
1842 -- Declare records: i - insert, u - update, r - result
1843 ------------------------------------------------------------
1844
1845
1846 -- Transaction headers
1847 i_taiv_rec okl_tai_pvt.taiv_rec_type;
1848 u_taiv_rec okl_tai_pvt.taiv_rec_type;
1849 lx_taiv_rec okl_tai_pvt.taiv_rec_type;
1850 l_init_taiv_rec okl_tai_pvt.taiv_rec_type;
1851 r_taiv_rec Okl_Trx_Ar_Invoices_Pub.taiv_rec_type;
1852
1853
1854 -- Transaction lines
1855 i_tilv_rec okl_til_pvt.tilv_rec_type;
1856 i_tilv_tbl okl_til_pvt.tilv_tbl_type;
1857 u_tilv_rec okl_til_pvt.tilv_rec_type;
1858 lx_tilv_tbl okl_til_pvt.tilv_tbl_type;
1859 l_init_tilv_rec okl_til_pvt.tilv_rec_type;
1860 r_tilv_rec okl_til_pvt.tilv_rec_type;
1861
1862
1863 -- Transaction line details
1864 i_tldv_rec okl_tld_pvt.tldv_rec_type;
1865 i_tldv_tbl okl_tld_pvt.tldv_tbl_type;
1866 u_tldv_rec okl_tld_pvt.tldv_rec_type;
1867 lx_tldv_tbl okl_tld_pvt.tldv_tbl_type;
1868 l_init_tldv_rec okl_tld_pvt.tldv_rec_type;
1869 r_tldv_rec okl_tld_pvt.tldv_rec_type;
1870
1871
1872 -- Ext Transaction Header
1873 i_xsiv_rec Okl_Ext_Sell_Invs_Pub.xsiv_rec_type;
1874 l_init_xsiv_rec Okl_Ext_Sell_Invs_Pub.xsiv_rec_type;
1875 r_xsiv_rec Okl_Ext_Sell_Invs_Pub.xsiv_rec_type;
1876
1877
1878 -- Ext Transaction Lines
1879 i_xlsv_rec Okl_Xtl_Sell_Invs_Pub.xlsv_rec_type;
1880 l_init_xlsv_rec Okl_Xtl_Sell_Invs_Pub.xlsv_rec_type;
1881 r_xlsv_rec Okl_Xtl_Sell_Invs_Pub.xlsv_rec_type;
1882
1883
1884 -- Ext Transaction Details
1885 i_esdv_rec Okl_Xtd_Sell_Invs_Pub.esdv_rec_type;
1886 l_init_esdv_rec Okl_Xtd_Sell_Invs_Pub.esdv_rec_type;
1887 r_esdv_rec Okl_Xtd_Sell_Invs_Pub.esdv_rec_type;
1888
1889
1890 -- Stream elements
1891 u_selv_rec Okl_Streams_Pub.selv_rec_type;
1892 l_init_selv_rec Okl_Streams_Pub.selv_rec_type;
1893 r_selv_rec Okl_Streams_Pub.selv_rec_type;
1894
1895
1896 ------------------------------------------------------------
1897 -- Declare local variables used in the program
1898 ------------------------------------------------------------
1899
1900
1901 l_khr_id okl_trx_ar_invoices_v.khr_id%TYPE;
1902 l_bill_date okl_trx_ar_invoices_v.date_invoiced%TYPE;
1903 l_trx_type okl_trx_ar_invoices_v.try_id%TYPE;
1904 l_kle_id okl_txl_ar_inv_lns_v.kle_id%TYPE;
1905
1906
1907 l_line_number okl_txl_ar_inv_lns_v.line_number%TYPE;
1908 l_detail_number okl_txd_ar_ln_dtls_v.line_detail_number%TYPE;
1909
1910
1911 l_header_amount okl_trx_ar_invoices_v.amount%TYPE;
1912 l_line_amount okl_txl_ar_inv_lns_v.amount%TYPE;
1913
1914
1915 l_header_id okl_trx_ar_invoices_v.id%TYPE;
1916 l_line_id okl_txl_ar_inv_lns_v.id%TYPE;
1917
1918
1919 ------------------------------------------------------------
1920 -- Declare variables required by APIs
1921 ------------------------------------------------------------
1922
1923
1924 l_api_version CONSTANT NUMBER := 1;
1925 l_api_name CONSTANT VARCHAR2(30) := 'BILL_STREAMS';
1926 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
1927
1928
1929 ------------------------------------------------------------
1930 -- Declare variables to call Accounting Engine.
1931 ------------------------------------------------------------
1932 p_bpd_acc_rec
1933 Okl_Acc_Call_Pub.bpd_acc_rec_type;
1934 l_init_bpd_acc_rec
1935 Okl_Acc_Call_Pub.bpd_acc_rec_type;
1936
1937
1938
1939
1940 ------------------------------------------------------------
1941 -- Variables for Error Processing and Committing Stream Billing
1942 -- Transactions
1943 ------------------------------------------------------------
1944
1945
1946 l_error_status VARCHAR2(1);
1947 l_error_message VARCHAR2(2000);
1948 l_trx_status_code Okl_trx_ar_invoices_v.trx_status_code%TYPE;
1949
1950
1951 -- To enforce commit frequency
1952 l_commit_cnt NUMBER;
1953 l_max_commit_cnt NUMBER := 500;
1954
1955
1956 -- For currecy precision rounded amount
1957 l_ste_amount okl_strm_elements.amount%type;
1958 l_curr_code okc_k_headers_b.currency_code%TYPE;
1959 ------------------------------------------------------------
1960 -- For errors in Stream Elements Table
1961 ------------------------------------------------------------
1962
1963
1964 l_distr_cnt NUMBER;
1965
1966
1967 -------------------------------------------------------------------------
1968 -- Account Builder Code
1969 -------------------------------------------------------------------------
1970 l_acc_gen_primary_key_tbl
1971 Okl_Account_Dist_Pub.acc_gen_primary_key;
1972 l_init_acc_gen_primary_key_tbl
1973 Okl_Account_Dist_Pub.acc_gen_primary_key;
1974
1975
1976
1977
1978 TYPE sel_err_rec_type IS RECORD (
1979 sel_id NUMBER,
1980 tld_id NUMBER,
1981 xsi_id NUMBER,
1982 bill_date DATE,
1983 contract_number okc_k_headers_b.contract_number%type,
1984 stream_name okl_strm_type_v.name%type,
1985 amount okl_strm_elements.amount%type,
1986 error_message Varchar2(2000)
1987 );
1988
1989
1990 TYPE sel_err_tbl_type IS TABLE OF sel_err_rec_type
1991 INDEX BY BINARY_INTEGER;
1992
1993
1994 sel_error_log_table sel_err_tbl_type;
1995 l_init_sel_table sel_err_tbl_type;
1996
1997
1998 l_sel_tab_index NUMBER;
1999
2000
2001 ------------------------------------------------------------
2002 -- Cursors for Rule based values
2003 ------------------------------------------------------------
2004
2005
2006 -- Local Vars for Rule based values --EXT
2007 l_ext_customer_id Okl_Ext_Sell_Invs_V.customer_id%TYPE;
2008 l_ext_receipt_method_id Okl_Ext_Sell_Invs_V.receipt_method_id%TYPE;
2009 l_ext_term_id Okl_Ext_Sell_Invs_V.term_id%TYPE;
2010 l_ext_sob_id Okl_Ext_Sell_Invs_V.set_of_books_id%TYPE;
2011 l_ext_trx_type_id Okl_Ext_Sell_Invs_V.cust_trx_type_id%TYPE;
2012 l_ext_addr_id Okl_Ext_Sell_Invs_V.customer_address_id%TYPE;
2013 l_ext_cust_bank_id Okl_Ext_Sell_Invs_V.customer_bank_account_id%TYPE;
2014 l_addr_id1 OKC_RULES_B.OBJECT1_ID1%TYPE;
2015 l_pmth_id1 OKC_RULES_B.OBJECT1_ID1%TYPE;
2016 l_bank_id1 OKC_RULES_B.OBJECT1_ID1%TYPE;
2017 l_rct_method_code AR_RECEIPT_CLASSES.CREATION_METHOD_CODE%TYPE;
2018 l_asst_tax OKC_RULES_B.rule_information1%TYPE;
2019 l_asst_line_tax OKC_RULES_B.rule_information1%TYPE;
2020 l_product_id okl_k_headers_full_v.pdt_id%TYPE;
2021
2022
2023 -----------------------------------------
2024 -- Local Variables for Rebook Credit Memo
2025 -- amounts
2026 -----------------------------------------
2027 l_bill_ajst_amt NUMBER;
2028 lx_tai_id NUMBER;
2029 l_credit_amount NUMBER;
2030
2031
2032 l_err_status VARCHAR2(1);
2033
2034
2035 TYPE err_rec_type IS RECORD (
2036 tai_id NUMBER,
2037 trx_number ra_customer_trx_all.trx_number%TYPE,
2038 amount NUMBER
2039 );
2040
2041
2042 TYPE err_tbl_type IS TABLE OF err_rec_type
2043 INDEX BY BINARY_INTEGER;
2044
2045
2046 err_tbl err_tbl_type;
2047 l_init_err_tbl err_tbl_type;
2048 err_idx NUMBER;
2049
2050
2051 l_commit_cntr NUMBER;
2052 l_credit_date DATE;
2053 i NUMBER := 0;
2054 l_rebook_adj_rec rebook_adj_rec_type;
2055
2056
2057 BEGIN
2058
2059
2060 ------------------------------------------------------------
2061 -- Start processing
2062 ------------------------------------------------------------
2063
2064
2065 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
2066
2067
2068 l_return_status := Okl_Api.START_ACTIVITY(
2069 p_api_name => l_api_name,
2070 p_pkg_name => G_PKG_NAME,
2071 p_init_msg_list => p_init_msg_list,
2072 l_api_version => l_api_version,
2073 p_api_version => p_api_version,
2074 p_api_type => '_PVT',
2075 x_return_status => l_return_status);
2076
2077
2078 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2079 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2080 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2081 RAISE Okl_Api.G_EXCEPTION_ERROR;
2082 END IF;
2083
2084
2085 ------------------------------------------------------------
2086 -- Process Adjustments
2087 ------------------------------------------------------------
2088 IF (p_rebook_adj_tbl.COUNT > 0) THEN
2089 i := p_rebook_adj_tbl.FIRST;
2090 LOOP
2091 l_rebook_adj_rec := p_rebook_adj_tbl(i);
2092 -------------------------------------------
2093 -- Create adjustment Invoice if amount > 0
2094 -------------------------------------------
2095 IF l_rebook_adj_rec.adjusted_amount > 0 THEN
2096
2097
2098 ------------------------------------------------
2099 -- Initialize the error processing variables
2100 ------------------------------------------------
2101 l_err_status := 'S';
2102 err_tbl := l_init_err_tbl;
2103 err_idx := 0;
2104
2105
2106 i_taiv_rec := l_init_taiv_rec;
2107
2108
2109 i_taiv_rec.trx_status_code := 'SUBMITTED';
2110 i_taiv_rec.khr_id := l_rebook_adj_rec.khr_id;
2111 i_taiv_rec.amount := l_rebook_adj_rec.adjusted_amount;
2112
2113
2114 l_trx_type := get_trx_type ('Billing', 'US');
2115 i_taiv_rec.try_id := l_trx_type;
2116 IF l_rebook_adj_rec.date_invoiced IS NULL THEN
2117 i_taiv_rec.date_invoiced := SYSDATE;
2118 ELSE
2119 i_taiv_rec.date_invoiced := l_rebook_adj_rec.date_invoiced;
2120 END IF;
2121 i_taiv_rec.date_entered := SYSDATE;
2122 i_taiv_rec.description := 'Rebook Adjustment Invoice';
2123 i_taiv_rec.okl_source_billing_trx := 'REBOOK';
2124
2125
2126 i_tilv_rec := l_init_tilv_rec;
2127 i_tilv_rec.amount :=
2128 l_rebook_adj_rec.adjusted_amount;
2129 i_tilv_rec.kle_id := l_rebook_adj_rec.kle_id;
2130 i_tilv_rec.inv_receiv_line_code := l_line_code;
2131 i_tilv_rec.line_number := 1;
2132 i_tilv_rec.txl_ar_line_number := 1;
2133 i_tilv_rec.description := 'Rebook Adjustment Invoice';
2134 i_tilv_rec.date_bill_period_start :=
2135 l_rebook_adj_rec.date_invoiced;
2136 i_tilv_rec.sty_id := l_rebook_adj_rec.sty_id; -- 6328168
2137
2138
2139 i_tilv_tbl(1) := i_tilv_rec;
2140
2141
2142 --rkuttiya R12 B Billing Architecture
2143 --call to central Billing API to create Billing transactions and accounting distributions
2144 OKL_INTERNAL_BILLING_PVT.create_billing_trx( p_api_version
2145 => l_api_version
2146 ,p_init_msg_list
2147 => p_init_msg_list
2148 ,x_return_status
2149 => l_return_status
2150 ,x_msg_count
2151 => x_msg_count
2152 ,x_msg_data
2153 => x_msg_data
2154 ,p_taiv_rec
2155 => i_taiv_rec
2156 ,p_tilv_tbl
2157 => i_tilv_tbl
2158 ,p_tldv_tbl
2159 => i_tldv_tbl
2160 ,x_taiv_rec
2161 => lx_taiv_rec
2162 ,x_tilv_tbl
2163 => lx_tilv_tbl
2164 ,x_tldv_tbl
2165 => lx_tldv_tbl);
2166
2167
2168
2169
2170 ----------------------------------------
2171 -- Record tai_id for error processing
2172 ----------------------------------------
2173 err_idx := err_idx + 1;
2174 err_tbl(err_idx).tai_id := lx_taiv_rec.id;
2175 err_tbl(err_idx).trx_number := NULL;
2176 err_tbl(err_idx).amount := i_taiv_rec.amount;
2177
2178
2179
2180
2181 IF (x_return_status = 'S' ) THEN
2182 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Accounting Distributions Created.');
2183 ELSE
2184 l_err_status := 'E';
2185 FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR Creating Accounting Distributions.');
2186 END IF;
2187
2188
2189 IF (x_return_status <> 'S' ) THEN
2190 UPDATE okl_trx_ar_invoices_b
2191 SET trx_status_code = 'ERROR'
2192 WHERE id = lx_taiv_rec.id;
2193 FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR Creating Accounting Distributions.');
2194 ELSE
2195 ----------------------------------------------
2196 -- Check if there was ever an error and update
2197 -- accordingly
2198 ----------------------------------------------
2199 IF l_err_status = 'E' THEN
2200 -----------------------------------------
2201 -- Flag all TAI records for this bill adj
2202 -- as error
2203 -----------------------------------------
2204 FOR j in err_tbl.FIRST..err_tbl.LAST LOOP
2205 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Reversing Invoice for '||err_tbl(j).amount);
2206
2207
2208 UPDATE okl_trx_ar_invoices_b
2209 SET trx_status_code = 'ERROR'
2210 WHERE id = err_tbl(j).tai_id;
2211 END LOOP;
2212 END IF;
2213 END IF;
2214
2215
2216 ELSE -- (create Invoice specific credit memo)
2217
2218
2219 ------------------------------------------------
2220 -- Initialize the error processing variables
2221 ------------------------------------------------
2222 l_err_status := 'S';
2223 err_tbl := l_init_err_tbl;
2224 err_idx := 0;
2225
2226
2227 l_bill_ajst_amt := l_rebook_adj_rec.adjusted_amount;
2228
2229 --Bug# 7720775: Added parameter sty_id
2230 FOR unpaid_invs_rec IN unpaid_invs_csr (
2231 l_rebook_adj_rec.khr_id,
2232 l_rebook_adj_rec.kle_id,
2233 l_rebook_adj_rec.sty_id ) LOOP
2234
2235
2236 IF l_bill_ajst_amt >= 0 THEN
2237 EXIT;
2238 END IF;
2239
2240
2241 r_taiv_rec := l_init_taiv_rec;
2242 l_credit_amount := 0;
2243
2244 --Bug# 7720775: Added check for Invoice Balance > 0
2245 IF (unpaid_invs_rec.amount_due_remaining > 0) THEN
2246
2247 IF ( abs(l_bill_ajst_amt) > unpaid_invs_rec.amount_due_remaining )
2248 THEN
2249 l_credit_amount := unpaid_invs_rec.amount_due_remaining;
2250 ELSE
2251 l_credit_amount := abs(l_bill_ajst_amt);
2252 END IF;
2253
2254
2255 -- ----------------------------
2256 -- Credit date
2257 -- ----------------------------
2258 l_credit_date := NULL;
2259 IF unpaid_invs_rec.bill_date > SYSDATE THEN
2260 l_credit_date := unpaid_invs_rec.bill_date;
2261 ELSE
2262 l_credit_date := SYSDATE;
2263 END IF;
2264
2265
2266
2267
2268
2269 okl_credit_memo_pub.insert_request(
2270 p_api_version => p_api_version,
2271 p_init_msg_list => p_init_msg_list,
2272 p_tld_id => unpaid_invs_rec.tld_id,
2273 p_credit_amount => (-1*l_credit_amount),
2274 p_credit_sty_id => NULL,
2275 p_credit_desc => 'Rebook Adjustment Credit Memo',
2276 p_credit_date => l_credit_date,
2277 p_try_id => NULL,
2278 p_transaction_source=>'REBOOK', -- Bug 6328168
2279 x_tai_id => lx_tai_id,
2280 x_taiv_rec => r_taiv_rec,
2281 x_return_status => l_return_status,
2282 x_msg_count => x_msg_count,
2283 x_msg_data => x_msg_data);
2284
2285
2286 ----------------------------------------
2287 -- Record tai_id for error processing
2288 ----------------------------------------
2289 err_idx := err_idx + 1;
2290 err_tbl(err_idx).tai_id := lx_tai_id;
2291 err_tbl(err_idx).trx_number := unpaid_invs_rec.trx_number;
2292 err_tbl(err_idx).amount := l_credit_amount;
2293
2294
2295 IF l_return_status = 'S' THEN
2296 FND_FILE.PUT_LINE (FND_FILE.LOG,'Credited AR Invoice: '
2297 ||unpaid_invs_rec.trx_number
2298 ||' for: '||l_credit_amount);
2299 l_bill_ajst_amt := l_bill_ajst_amt +
2300 unpaid_invs_rec.amount_due_remaining;
2301 ELSE
2302 l_err_status := 'E';
2303
2304
2305 FND_FILE.PUT_LINE (FND_FILE.LOG,'ERROR Crediting AR Invoice: '
2306 ||unpaid_invs_rec.trx_number
2307 ||' for: '||l_credit_amount);
2308 EXIT;
2309 END IF;
2310 END IF;
2311 --Bug# 7720775: Added check for Invoice Balance > 0
2312 END LOOP;
2313
2314
2315 -- If there was more credit than Invoices could use,
2316 -- then, create an On-account CM
2317 IF l_bill_ajst_amt < 0 THEN
2318
2319
2320 i_taiv_rec := l_init_taiv_rec;
2321
2322
2323 i_taiv_rec.trx_status_code := 'SUBMITTED';
2324 i_taiv_rec.khr_id := l_rebook_adj_rec.khr_id;
2325 i_taiv_rec.amount := l_bill_ajst_amt;
2326
2327
2328 l_trx_type := get_trx_type ('Credit Memo', 'US');
2329 i_taiv_rec.try_id := l_trx_type;
2330 i_taiv_rec.date_invoiced := l_rebook_adj_rec.date_invoiced;
2331 i_taiv_rec.date_entered := SYSDATE;
2332 i_taiv_rec.description := 'Rebook Adjustment Invoice';
2333 i_taiv_rec.okl_source_billing_trx := 'REBOOK';
2334
2335
2336 i_tilv_rec := l_init_tilv_rec;
2337 i_tilv_rec.amount := l_bill_ajst_amt;
2338 i_tilv_rec.kle_id := l_rebook_adj_rec.kle_id;
2339 i_tilv_rec.inv_receiv_line_code := l_line_code;
2340 i_tilv_rec.line_number := 1;
2341 i_tilv_rec.txl_ar_line_number := 1;
2342 i_tilv_rec.date_bill_period_start := l_rebook_adj_rec.date_invoiced;
2343 i_tilv_rec.sty_id := l_rebook_adj_rec.sty_id; -- 6328168
2344
2345
2346 i_tilv_tbl(1) := i_tilv_rec;
2347
2348
2349 --rkuttiya R12 B Billing Architecture commented out details record structure, as not required to pass here
2350 -- since sel id is NULL
2351 /*i_tldv_rec := l_init_tldv_rec;
2352
2353
2354 i_tldv_rec.amount := l_bill_ajst_amt;
2355 i_tldv_rec.line_detail_number := 1;
2356 i_tldv_rec.sty_id := l_rebook_adj_rec.sty_id;
2357 i_tldv_rec.sel_id := NULL;
2358
2359
2360 i_tldv_tbl(1) := i_tldv_rec; */
2361
2362
2363
2364 --rkuttiya R12 B Billing Architecture
2365 -- call central Billing API to create billing transactions and accounting distributions
2366 OKL_INTERNAL_BILLING_PVT.create_billing_trx( p_api_version
2367 => l_api_version
2368 ,p_init_msg_list
2369 => p_init_msg_list
2370 ,x_return_status
2371 => l_return_status
2372 ,x_msg_count
2373 => x_msg_count
2374 ,x_msg_data
2375 => x_msg_data
2376 ,p_taiv_rec
2377 => i_taiv_rec
2378 ,p_tilv_tbl
2379 => i_tilv_tbl
2380 ,p_tldv_tbl
2381 => i_tldv_tbl
2382 ,x_taiv_rec
2383 => lx_taiv_rec
2384 ,x_tilv_tbl
2385 => lx_tilv_tbl
2386 ,x_tldv_tbl
2387 => lx_tldv_tbl);
2388
2389
2390
2391
2392 ----------------------------------------
2393 -- Record tai_id for error processing
2394 ----------------------------------------
2395 err_idx := err_idx + 1;
2396 err_tbl(err_idx).tai_id := lx_taiv_rec.id;
2397 err_tbl(err_idx).trx_number := NULL;
2398 err_tbl(err_idx).amount := i_taiv_rec.amount;
2399
2400
2401 IF (x_return_status = 'S' ) THEN
2402 FND_FILE.PUT_LINE (FND_FILE.LOG, 'On Account Accounting Distributions Created.');
2403 ELSE
2404 l_err_status := 'E';
2405 FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR Creating On Account Accounting Distributions.');
2406 END IF;
2407
2408
2409 END IF; -- More Credit remaining than needed
2410
2411
2412 --IF (x_return_status = 'S' ) THEN
2413 -- UPDATE okl_strm_elements
2414 -- SET date_billed = SYSDATE
2415 -- WHERE id = adj_streams_rec.sel_id;
2416 --END IF;
2417 IF (x_return_status <> 'S' ) THEN
2418 IF l_err_status = 'E' THEN
2419 -----------------------------------------
2420 -- Flag all TAI records for this bill adj
2421 -- as error
2422 -----------------------------------------
2423 FOR j in err_tbl.FIRST..err_tbl.LAST LOOP
2424 IF err_tbl(j).trx_number IS NOT NULL THEN
2425 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Reversing Credited Invoice '
2426 ||err_tbl(j).trx_number
2427 ||' for amount '
2428 ||err_tbl(j).amount);
2429 ELSE
2430 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Reversing Credit for amount '
2431 ||err_tbl(j).amount);
2432 END IF;
2433
2434
2435 UPDATE okl_trx_ar_invoices_b
2436 SET trx_status_code = 'ERROR'
2437 WHERE id = err_tbl(j).tai_id;
2438 END LOOP;
2439 END IF;
2440
2441
2442 --UPDATE okl_trx_ar_invoices_b
2443 --SET trx_status_code = 'ERROR'
2444 --WHERE id = x_taiv_rec.id;
2445 --FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR Creating Accounting Distributions.');
2446 ELSE
2447 IF l_err_status = 'E' THEN
2448 -----------------------------------------
2449 -- Flag all TAI records for this bill adj
2450 -- as error
2451 -----------------------------------------
2452 FOR j in err_tbl.FIRST..err_tbl.LAST LOOP
2453 IF err_tbl(j).trx_number IS NOT NULL THEN
2454 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Reversing Credited Invoice '
2455 ||err_tbl(j).trx_number
2456 ||' for amount '
2457 ||err_tbl(j).amount);
2458 ELSE
2459 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Reversing Credit for amount '
2460 ||err_tbl(j).amount);
2461 END IF;
2462
2463
2464 UPDATE okl_trx_ar_invoices_b
2465 SET trx_status_code = 'ERROR'
2466 WHERE id = err_tbl(j).tai_id;
2467 END LOOP;
2468 END IF;
2469 END IF;
2470
2471
2472 END IF;
2473
2474
2475 EXIT WHEN (i = p_rebook_adj_tbl.LAST);
2476 i := p_rebook_adj_tbl.NEXT(i);
2477 END LOOP;
2478 END IF;
2479
2480
2481 IF FND_API.To_Boolean( p_commit ) THEN
2482 COMMIT;
2483 END IF;
2484
2485
2486 ------------------------------------------------------------
2487 -- End processing
2488 ------------------------------------------------------------
2489
2490
2491 Okl_Api.END_ACTIVITY (
2492 x_msg_count => x_msg_count,
2493 x_msg_data => x_msg_data);
2494
2495
2496
2497
2498 EXCEPTION
2499
2500
2501 ------------------------------------------------------------
2502 -- Exception handling
2503 ------------------------------------------------------------
2504
2505
2506 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2507 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (EXCP) => '||SQLERRM);
2508 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
2509 p_api_name => l_api_name,
2510 p_pkg_name => G_PKG_NAME,
2511 p_exc_name => 'Okl_Api.G_RET_STS_ERROR',
2512 x_msg_count => x_msg_count,
2513 x_msg_data => x_msg_data,
2514 p_api_type => '_PVT');
2515
2516
2517 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2518 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (UNEXP) => '||SQLERRM);
2519 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
2520 p_api_name => l_api_name,
2521 p_pkg_name => G_PKG_NAME,
2522 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
2523 x_msg_count => x_msg_count,
2524 x_msg_data => x_msg_data,
2525 p_api_type => '_PVT');
2526
2527
2528 WHEN OTHERS THEN
2529 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (OTHERS) => '||SQLERRM);
2530 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
2531 p_api_name => l_api_name,
2532 p_pkg_name => G_PKG_NAME,
2533 p_exc_name => 'OTHERS',
2534 x_msg_count => x_msg_count,
2535 x_msg_data => x_msg_data,
2536 p_api_type => '_PVT');
2537
2538
2539 END CM_Bill_adjustments;
2540
2541
2542
2543
2544 PROCEDURE CUSTOM_CM_Bill_adjustments
2545 (p_api_version IN NUMBER
2546 ,p_init_msg_list IN VARCHAR2
2547 ,x_return_status OUT NOCOPY VARCHAR2
2548 ,x_msg_count OUT NOCOPY NUMBER
2549 ,x_msg_data OUT NOCOPY VARCHAR2
2550 ,p_rebook_adj_tbl IN rebook_adj_tbl_type
2551 ) IS
2552 BEGIN
2553
2554
2555 NULL;
2556
2557
2558 END CUSTOM_CM_Bill_adjustments;
2559
2560
2561
2562
2563 PROCEDURE ON_ACCT_Bill_adjustments
2564 (p_api_version IN NUMBER
2565 ,p_init_msg_list IN VARCHAR2
2566 ,x_return_status OUT NOCOPY VARCHAR2
2567 ,x_msg_count OUT NOCOPY NUMBER
2568 ,x_msg_data OUT NOCOPY VARCHAR2
2569 ,p_rebook_adj_tbl IN rebook_adj_tbl_type
2570 )
2571 IS
2572
2573
2574 ------------------------------------------------------------
2575 -- Declare variables required by APIs
2576 ------------------------------------------------------------
2577
2578
2579 l_api_version CONSTANT NUMBER := 1;
2580 l_api_name CONSTANT VARCHAR2(30) := 'BILL_STREAMS';
2581 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2582
2583
2584 ------------------------------------------------------------
2585 -- Declare variables to call Accounting Engine.
2586 ------------------------------------------------------------
2587 p_bpd_acc_rec
2588 Okl_Acc_Call_Pub.bpd_acc_rec_type;
2589 l_init_bpd_acc_rec
2590 Okl_Acc_Call_Pub.bpd_acc_rec_type;
2591
2592
2593 ------------------------------------------------------------
2594 -- Declare records: i - insert, u - update, r - result
2595 ------------------------------------------------------------
2596
2597
2598 -- Transaction headers
2599 i_taiv_rec okl_tai_pvt.taiv_rec_type;
2600 u_taiv_rec okl_tai_pvt.taiv_rec_type;
2601 lx_taiv_rec okl_tai_pvt.taiv_rec_type;
2602 l_init_taiv_rec okl_tai_pvt.taiv_rec_type;
2603 r_taiv_rec okl_tai_pvt.taiv_rec_type;
2604
2605
2606 -- Transaction lines
2607 i_tilv_rec okl_til_pvt.tilv_rec_type;
2608 i_tilv_tbl okl_til_pvt.tilv_tbl_type;
2609 u_tilv_rec okl_til_pvt.tilv_rec_type;
2610 lx_tilv_tbl okl_til_pvt.tilv_tbl_type;
2611 l_init_tilv_rec okl_til_pvt.tilv_rec_type;
2612 r_tilv_rec okl_til_pvt.tilv_rec_type;
2613
2614
2615 -- Transaction line details
2616 i_tldv_rec okl_tld_pvt.tldv_rec_type;
2617 i_tldv_tbl okl_tld_pvt.tldv_tbl_type;
2618 u_tldv_rec okl_tld_pvt.tldv_rec_type;
2619 lx_tldv_tbl okl_tld_pvt.tldv_tbl_type;
2620 l_init_tldv_rec okl_tld_pvt.tldv_rec_type;
2621 r_tldv_rec okl_tld_pvt.tldv_rec_type;
2622
2623
2624 l_trx_type okl_trx_ar_invoices_v.try_id%TYPE;
2625 l_line_code CONSTANT VARCHAR2(30) := 'LINE';
2626
2627
2628 -----------------------------------------
2629 -- Local Variables for Rebook Credit Memo
2630 -- amounts
2631 -----------------------------------------
2632 l_bill_ajst_amt NUMBER;
2633 i NUMBER := 0;
2634 l_rebook_adj_rec rebook_adj_rec_type;
2635
2636
2637 BEGIN
2638
2639
2640 ------------------------------------------------------------
2641 -- Start processing
2642 ------------------------------------------------------------
2643
2644
2645 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
2646
2647
2648 l_return_status := Okl_Api.START_ACTIVITY(
2649 p_api_name => l_api_name,
2650 p_pkg_name => G_PKG_NAME,
2651 p_init_msg_list => p_init_msg_list,
2652 l_api_version => l_api_version,
2653 p_api_version => p_api_version,
2654 p_api_type => '_PVT',
2655 x_return_status => l_return_status);
2656
2657
2658 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2659 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2660 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2661 RAISE Okl_Api.G_EXCEPTION_ERROR;
2662 END IF;
2663
2664
2665
2666
2667 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Count'||p_rebook_adj_tbl.COUNT);
2668 ------------------------------------------------------------
2669 -- Process Adjustments
2670 ------------------------------------------------------------
2671 IF (p_rebook_adj_tbl.COUNT > 0) THEN
2672 i := p_rebook_adj_tbl.FIRST;
2673 LOOP
2674 l_rebook_adj_rec := p_rebook_adj_tbl(i);
2675
2676
2677
2678 -------------------------------------------
2679 -- Create adjustment Invoice if amount > 0
2680 -------------------------------------------
2681 IF l_rebook_adj_rec.adjusted_amount > 0 THEN
2682
2683
2684 i_taiv_rec := l_init_taiv_rec;
2685
2686
2687 i_taiv_rec.trx_status_code := 'SUBMITTED';
2688 i_taiv_rec.khr_id := l_rebook_adj_rec.khr_id;
2689 i_taiv_rec.amount := l_rebook_adj_rec.adjusted_amount;
2690
2691
2692 l_trx_type := get_trx_type ('Billing', 'US');
2693 i_taiv_rec.try_id := l_trx_type;
2694 IF l_rebook_adj_rec.date_invoiced IS NULL THEN
2695 i_taiv_rec.date_invoiced := SYSDATE;
2696 ELSE
2697 i_taiv_rec.date_invoiced := l_rebook_adj_rec.date_invoiced;
2698 END IF;
2699 i_taiv_rec.date_entered := SYSDATE;
2700 i_taiv_rec.description := 'Rebook Adjustment Invoice';
2701 i_taiv_rec.okl_source_billing_trx := 'REBOOK';
2702
2703
2704 i_tilv_rec := l_init_tilv_rec;
2705 i_tilv_rec.amount := l_rebook_adj_rec.adjusted_amount;
2706 i_tilv_rec.kle_id := l_rebook_adj_rec.kle_id;
2707 i_tilv_rec.inv_receiv_line_code := l_line_code;
2708 i_tilv_rec.line_number := 1;
2709 i_tilv_rec.txl_ar_line_number := 1;
2710 i_tilv_rec.description := 'Rebook Adjustment Invoice';
2711 i_tilv_rec.date_bill_period_start := l_rebook_adj_rec.date_invoiced;
2712
2713 -- rmunjulu - added
2714 i_tilv_rec.sty_id := l_rebook_adj_rec.sty_id;
2715
2716 i_tilv_tbl(1) := i_tilv_rec;
2717
2718
2719 --rkuttiya R12 B Billing Architecture commented details record structure since sel id is NULL
2720
2721
2722 /*i_tldv_rec := l_init_tldv_rec;
2723
2724
2725 i_tldv_rec.amount := l_rebook_adj_rec.adjusted_amount;
2726 i_tldv_rec.line_detail_number := 1;
2727 i_tldv_rec.sty_id := l_rebook_adj_rec.sty_id;
2728 i_tldv_rec.sel_id := NULL;
2729
2730
2731 i_tldv_tbl(1) := i_tldv_rec; */
2732
2733
2734 --rkuttiya R12 B Billing Architecture
2735 -- call central Billing API to create transaction and accounting distributions
2736 OKL_INTERNAL_BILLING_PVT.create_billing_trx( p_api_version
2737 => l_api_version
2738 ,p_init_msg_list
2739 => p_init_msg_list
2740 ,x_return_status
2741 => l_return_status
2742 ,x_msg_count
2743 => x_msg_count
2744 ,x_msg_data
2745 => x_msg_data
2746 ,p_taiv_rec
2747 => i_taiv_rec
2748 ,p_tilv_tbl
2749 => i_tilv_tbl
2750 ,p_tldv_tbl
2751 => i_tldv_tbl
2752 ,x_taiv_rec
2753 => lx_taiv_rec
2754 ,x_tilv_tbl
2755 => lx_tilv_tbl
2756 ,x_tldv_tbl
2757 => lx_tldv_tbl);
2758
2759
2760 IF (x_return_status = 'S' ) THEN
2761 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Billing Transactions Created.');
2762 ELSE
2763 FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR Creating Billing Transactions.');
2764 END IF;
2765
2766
2767 IF (x_return_status <> 'S' ) THEN
2768 UPDATE okl_trx_ar_invoices_b
2769 SET trx_status_code = 'ERROR'
2770 WHERE id = lx_taiv_rec.id;
2771 FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR Creating Billing Transactions.');
2772 END IF;
2773
2774
2775 ELSE -- (create On-account credit memo)
2776
2777
2778 l_bill_ajst_amt := l_rebook_adj_rec.adjusted_amount;
2779
2780 IF l_bill_ajst_amt < 0 THEN
2781
2782
2783 i_taiv_rec := l_init_taiv_rec;
2784
2785
2786 i_taiv_rec.trx_status_code := 'SUBMITTED';
2787 i_taiv_rec.khr_id := l_rebook_adj_rec.khr_id;
2788 i_taiv_rec.amount := l_bill_ajst_amt;
2789
2790
2791 l_trx_type := get_trx_type ('Credit Memo', 'US');
2792 i_taiv_rec.try_id := l_trx_type;
2793 i_taiv_rec.date_invoiced := l_rebook_adj_rec.date_invoiced;
2794 i_taiv_rec.date_entered := SYSDATE;
2795 i_taiv_rec.description := 'Rebook Adjustment Invoice';
2796 i_taiv_rec.okl_source_billing_trx := 'REBOOK';
2797
2798
2799 i_tilv_rec := l_init_tilv_rec;
2800 i_tilv_rec.amount := l_bill_ajst_amt;
2801 i_tilv_rec.kle_id :=
2802 l_rebook_adj_rec.kle_id;
2803 i_tilv_rec.inv_receiv_line_code := l_line_code;
2804 i_tilv_rec.line_number := 1;
2805 i_tilv_rec.txl_ar_line_number := 1;
2806 i_tilv_rec.description := 'Rebook Adjustment Invoice';
2807 i_tilv_rec.date_bill_period_start :=
2808 l_rebook_adj_rec.date_invoiced;
2809 i_tilv_rec.sty_id := l_rebook_adj_rec.sty_id; -- bug 6328168
2810
2811
2812
2813 i_tilv_tbl(1) := i_tilv_rec;
2814
2815
2816
2817
2818 --rkuttiya R12 B Billing Architecture commented details record structure as sel id IS NULL
2819 /* i_tldv_rec := l_init_tldv_rec;
2820
2821
2822 i_tldv_rec.amount := l_bill_ajst_amt;
2823 i_tldv_rec.line_detail_number := 1;
2824 i_tldv_rec.sty_id := l_rebook_adj_rec.sty_id;
2825 i_tldv_rec.sel_id := NULL;
2826 i_tldv_rec.txl_ar_line_number := 1;
2827
2828
2829
2830 i_tldv_tbl(1) := i_tldv_rec;*/
2831
2832
2833
2834 --rkuttiya R12 B Billing Architecture
2835 -- call central Billing API to create transaction and accounting distributions
2836 OKL_INTERNAL_BILLING_PVT.create_billing_trx( p_api_version
2837 => l_api_version
2838 ,p_init_msg_list
2839 => p_init_msg_list
2840 ,x_return_status
2841 => l_return_status
2842 ,x_msg_count
2843 => x_msg_count
2844 ,x_msg_data
2845 => x_msg_data
2846 ,p_taiv_rec
2847 => i_taiv_rec
2848 ,p_tilv_tbl
2849 => i_tilv_tbl
2850 ,p_tldv_tbl
2851 => i_tldv_tbl
2852 ,x_taiv_rec
2853 => lx_taiv_rec
2854 ,x_tilv_tbl
2855 => lx_tilv_tbl
2856 ,x_tldv_tbl
2857 => lx_tldv_tbl);
2858
2859
2860
2861 IF (x_return_status = 'S' ) THEN
2862 FND_FILE.PUT_LINE (FND_FILE.LOG, 'On Account Billing Transactions Created.');
2863 ELSE
2864 FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR Creating On Account Billing Transactions.');
2865 END IF;
2866
2867
2868 IF (x_return_status <> 'S' ) THEN
2869 UPDATE okl_trx_ar_invoices_b
2870 SET trx_status_code = 'ERROR'
2871 WHERE id = lx_taiv_rec.id;
2872 FND_FILE.PUT_LINE (FND_FILE.LOG, 'ERROR Creating Billing Transactions');
2873 END IF;
2874
2875
2876 END IF;
2877 END IF;
2878
2879
2880 EXIT WHEN (i = p_rebook_adj_tbl.LAST);
2881 i := p_rebook_adj_tbl.NEXT(i);
2882 END LOOP;
2883 END IF;
2884
2885
2886 ------------------------------------------------------------
2887 -- End processing
2888 ------------------------------------------------------------
2889
2890
2891 Okl_Api.END_ACTIVITY (
2892 x_msg_count => x_msg_count,
2893 x_msg_data => x_msg_data);
2894
2895
2896 EXCEPTION
2897
2898
2899 ------------------------------------------------------------
2900 -- Exception handling
2901 ------------------------------------------------------------
2902
2903
2904 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
2905 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (EXCP) => '||SQLERRM);
2906 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
2907 p_api_name => l_api_name,
2908 p_pkg_name => G_PKG_NAME,
2909 p_exc_name => 'Okl_Api.G_RET_STS_ERROR',
2910 x_msg_count => x_msg_count,
2911 x_msg_data => x_msg_data,
2912 p_api_type => '_PVT');
2913
2914
2915 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
2916 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (UNEXP) => '||SQLERRM);
2917 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
2918 p_api_name => l_api_name,
2919 p_pkg_name => G_PKG_NAME,
2920 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
2921 x_msg_count => x_msg_count,
2922 x_msg_data => x_msg_data,
2923 p_api_type => '_PVT');
2924
2925
2926 WHEN OTHERS THEN
2927 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (OTHERS) => '||SQLERRM);
2928 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
2929 p_api_name => l_api_name,
2930 p_pkg_name => G_PKG_NAME,
2931 p_exc_name => 'OTHERS',
2932 x_msg_count => x_msg_count,
2933 x_msg_data => x_msg_data,
2934 p_api_type => '_PVT');
2935
2936
2937 END ON_ACCT_Bill_adjustments;
2938
2939
2940
2941
2942 PROCEDURE Rebook_Bill_adjustments
2943 (p_api_version IN NUMBER
2944 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE
2945 ,x_return_status OUT NOCOPY VARCHAR2
2946 ,x_msg_count OUT NOCOPY NUMBER
2947 ,x_msg_data OUT NOCOPY VARCHAR2
2948 ,p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE
2949 ,p_rebook_adj_tbl IN rebook_adj_tbl_type
2950 ) IS
2951
2952
2953 ------------------------------------------------------------
2954 -- Declare variables required by APIs
2955 ------------------------------------------------------------
2956
2957
2958 l_api_version CONSTANT NUMBER := 1;
2959 l_api_name CONSTANT VARCHAR2(30) := 'BILL_STREAMS';
2960 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2961
2962
2963 BEGIN
2964
2965
2966
2967
2968 ------------------------------------------------------------
2969 -- Start processing
2970 ------------------------------------------------------------
2971
2972
2973 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
2974
2975
2976 l_return_status := Okl_Api.START_ACTIVITY(
2977 p_api_name => l_api_name,
2978 p_pkg_name => G_PKG_NAME,
2979 p_init_msg_list => p_init_msg_list,
2980 l_api_version => l_api_version,
2981 p_api_version => p_api_version,
2982 p_api_type => '_PVT',
2983 x_return_status => l_return_status);
2984
2985
2986 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2987 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2988 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2989 RAISE Okl_Api.G_EXCEPTION_ERROR;
2990 END IF;
2991
2992
2993 ------------------------------------------------------------
2994 -- Check the profile value and call corresponding
2995 -- procedure with processing logic
2996 ------------------------------------------------------------
2997
2998
2999 IF fnd_profile.value('OKL_APPLY_CM') IS NULL THEN
3000 -- On Account Credit Memo For Manual application
3001
3002
3003 FND_FILE.PUT_LINE (FND_FILE.LOG,
3004 '******************************************');
3005 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Profile OKL:Apply Billing Adjustment: NULL');
3006 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Create On-Invoice Credit Memo.');
3007 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Invoking OKL_REBOOK_CM_PVT.ON_ACCT_Bill_adjustments');
3008 FND_FILE.PUT_LINE (FND_FILE.LOG,
3009 '******************************************');
3010
3011
3012 ON_ACCT_Bill_adjustments (
3013 p_api_version => p_api_version,
3014 p_init_msg_list => Okl_Api.G_FALSE,
3015 x_return_status => x_return_status,
3016 x_msg_count => x_msg_count,
3017 x_msg_data => x_msg_data,
3018 p_rebook_adj_tbl => p_rebook_adj_tbl
3019 );
3020
3021
3022 ELSIF (fnd_profile.value('OKL_APPLY_CM') = 'SEEDED') THEN
3023 -- Development Logic
3024 FND_FILE.PUT_LINE (FND_FILE.LOG,
3025 '******************************************');
3026 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Profile OKL:Apply Billing Adjustment: SEEDED');
3027 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Create On-Invoice and On-Acct Credit Memo.');
3028 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Invoking OKL_REBOOK_CM_PVT.CM_Bill_adjustments');
3029 FND_FILE.PUT_LINE (FND_FILE.LOG,
3030 '******************************************');
3031
3032
3033 CM_Bill_adjustments (
3034 p_api_version => p_api_version,
3035 p_init_msg_list => Okl_Api.G_FALSE,
3036 x_return_status => x_return_status,
3037 x_msg_count => x_msg_count,
3038 x_msg_data => x_msg_data,
3039 p_commit => p_commit,
3040 p_rebook_adj_tbl => p_rebook_adj_tbl
3041 );
3042
3043
3044 ELSIF (fnd_profile.value('OKL_APPLY_CM') = 'CUSTOM') THEN
3045
3046
3047 -- Custom Logic
3048 FND_FILE.PUT_LINE (FND_FILE.LOG,
3049 '******************************************');
3050 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Profile OKL:Apply Billing Adjustment: CUSTOM');
3051 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Create Credit Memo using CUSTOM Logic.');
3052 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Invoking OKL_REBOOK_CM_PVT.CUSTOM_CM_Bill_adjustments');
3053 FND_FILE.PUT_LINE (FND_FILE.LOG,
3054 '******************************************');
3055
3056
3057 OKL_CUSTOM_PVT.CUSTOM_CM_Bill_adjustments (
3058 p_api_version => p_api_version,
3059 p_init_msg_list => Okl_Api.G_FALSE,
3060 x_return_status => x_return_status,
3061 x_msg_count => x_msg_count,
3062 x_msg_data => x_msg_data,
3063 p_rebook_adj_tbl => p_rebook_adj_tbl
3064 );
3065
3066
3067 ELSE
3068 FND_FILE.PUT_LINE (FND_FILE.LOG,
3069 '******************************************');
3070 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Profile OKL:Apply Billing Adjustment: '||fnd_profile.value('OKL_APPLY_CM'));
3071 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Acceptable Values are: NULL, SEEDED OR CUSTOM.');
3072 FND_FILE.PUT_LINE (FND_FILE.LOG, 'Exiting Procedure ..');
3073 FND_FILE.PUT_LINE (FND_FILE.LOG,
3074 '******************************************');
3075 END IF;
3076
3077
3078 IF FND_API.To_Boolean( p_commit ) THEN
3079 COMMIT;
3080 END IF;
3081
3082
3083 ------------------------------------------------------------
3084 -- End processing
3085 ------------------------------------------------------------
3086
3087
3088 Okl_Api.END_ACTIVITY (
3089 x_msg_count => x_msg_count,
3090 x_msg_data => x_msg_data);
3091
3092
3093 EXCEPTION
3094
3095
3096 ------------------------------------------------------------
3097 -- Exception handling
3098 ------------------------------------------------------------
3099
3100
3101 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
3102 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (EXCP) => '||SQLERRM);
3103 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
3104 p_api_name => l_api_name,
3105 p_pkg_name => G_PKG_NAME,
3106 p_exc_name => 'Okl_Api.G_RET_STS_ERROR',
3107 x_msg_count => x_msg_count,
3108 x_msg_data => x_msg_data,
3109 p_api_type => '_PVT');
3110
3111
3112 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
3113 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (UNEXP) => '||SQLERRM);
3114 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
3115 p_api_name => l_api_name,
3116 p_pkg_name => G_PKG_NAME,
3117 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
3118 x_msg_count => x_msg_count,
3119 x_msg_data => x_msg_data,
3120 p_api_type => '_PVT');
3121
3122
3123 WHEN OTHERS THEN
3124 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, 'Error (OTHERS) => '||SQLERRM);
3125 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
3126 p_api_name => l_api_name,
3127 p_pkg_name => G_PKG_NAME,
3128 p_exc_name => 'OTHERS',
3129 x_msg_count => x_msg_count,
3130 x_msg_data => x_msg_data,
3131 p_api_type => '_PVT');
3132
3133
3134 END Rebook_Bill_adjustments;
3135
3136
3137 END OKL_REBOOK_CM_PVT;