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