[Home] [Help]
PACKAGE BODY: APPS.OKS_BILLING_PUB
Source
1 Package Body OKS_BILLING_PUB as
2 /* $Header: OKSPBILB.pls 120.21.12000000.4 2007/06/29 21:11:16 hvaladip ship $ */
3
4
5 /*
6 This Procedure is used for billing
7 of Service / Usage / Subscription lines either
8 of a single contract or all the contract lines eligibile for billing.
9 */
10
11 -- Global var holding the User Id
12 user_id NUMBER;
13
14 -- Global var to hold the ERROR value.
15 ERROR NUMBER := 0;
16
17 -- Global var to hold the SUCCESS value.
18 SUCCESS NUMBER := 1;
19 WARNING NUMBER := 1;
20
21
22 -- Global var to hold the Concurrent Process return value
23 conc_ret_code NUMBER := SUCCESS;
24
25 -- Global constant for the threshold count before splitting into sub-requests
26 MAX_SINGLE_REQUEST NUMBER := 500;
27
28 -- Global constant for the maximum allowed sub-requests (parallel workers)
29 MAX_JOBS NUMBER := 30;
30
31 -- Global vars to hold the min and max hdr_id for each sub-request range
32 type range_rec is record (
33 lo number,
34 hi number);
35 type rangeArray is VARRAY(50) of range_rec;
36 range_arr rangeArray;
37
38 /* *** PL/sql tables and variables for report **** */
39
40 l_processed_lines_tbl OKS_BILL_REC_PUB.line_report_tbl_type;
41 l_processed_sub_lines_tbl OKS_BILL_REC_PUB.line_report_tbl_type;
42
43 l_pr_tbl_idx Number := 0; /* Lines table */
44 l_prs_tbl_idx Number := 0; /* Sub Lines table */
45
46 -- Global table for holding number of periods to process
47 level_elements_tab Oks_bill_util_pub.level_element_tab;
48 level_coverage Oks_bill_util_pub.level_element_tab;
49 -- Variables to control log writing and report generation.
50 /*****
51 l_write_log BOOLEAN;
52 l_write_report BOOLEAN;
53 l_yes_no VARCHAR2(10);
54 *****/
55
56 /*
57 Procedure LEVEL is for levelling across cps
58 of usage items if level flag is set to 'Y'.
59 It is done only if usage type is Actual per period.
60 */
61
62 -------------------------------------------------------------------------
63 -- Begin partial period computation logic
64 -- Developer Mani Choudhary
65 -- Date 10-JUN-2005
66 -- Added parameters p_period_type,p_period_start to the procedure level.
67 -------------------------------------------------------------------------
68 Procedure level
69 (
70 P_level_qty IN NUMBER,
71 P_cov_tbl IN OUT NOCOPY OKS_BILL_REC_PUB.COVERED_TBL,
72 P_qty IN NUMBER,
73 --P_line_tbl IN OUT NOCOPY OKS_QP_INT_PVT.G_SLINE_TBL_TYPE ,
74 p_usage_period IN VARCHAR2,
75 p_time_uom_code IN VARCHAR2,
76 p_uom_code IN VARCHAR2,
77 p_period_type IN VARCHAR2,
78 p_period_start IN VARCHAR2,
79 X_return_status OUT NOCOPY VARCHAR2
80 )
81 IS
82 Cursor l_bill_qty_csr (p_id IN NUMBER) IS
83 SELECT fixed_quantity fixed_qty
84 ,minimum_quantity minimum_qty
85 ,default_quantity default_qty
86 ,amcv_flag amcv_flag
87 ,usage_period usage_period
88 ,usage_duration usage_duration
89 ,level_yn level_yn
90 ,base_reading base_reading
91 ,usage_type usage_Type
92 FROM oks_k_lines_b
93 WHERE cle_id = p_id ;
94
95 l_bill_qty_rec l_bill_qty_csr%rowtype;
96 l_from_date DATE;
97 l_to_date DATE;
98 l_minimum NUMBER;
99 l_temp NUMBER;
100 l_temp_qty NUMBER;
101 l_negative_yn VARCHAR2(10);
102
103 BEGIN
104
105 X_return_status := 'S';
106 l_negative_yn := NVL(FND_PROFILE.VALUE('OKS_NEGATIVE_BILLING_YN'),'NO');
107 FOR l_ptr in 1..p_cov_tbl.count
108 LOOP
109 OPEN l_bill_qty_csr(p_cov_tbl(l_ptr).id);
110 FETCH l_bill_qty_csr into l_bill_qty_rec;
111 CLOSE l_bill_qty_csr;
112
113 l_minimum := NVL(l_bill_qty_rec.Minimum_qty,0) ;
114 l_temp_qty := p_cov_tbl(l_ptr).result ; --used to temp storage of result
115 l_from_date := p_cov_tbl(l_ptr).date_billed_from;
116 l_to_date := p_cov_tbl(l_ptr).date_billed_to;
117
118 IF (l_minimum > 0) THEN
119 -------------------------------------------------------------------------
120 -- Begin partial period computation logic
121 -- Developer Mani Choudhary
122 -- Date 11-JUN-2005
123 -- call oks_bill_rec_pub.Get_prorated_Usage_Qty to get the prorated usage
124 -------------------------------------------------------------------------
125 IF p_period_type IS NOT NULL AND
126 p_period_start IS NOT NULL
127 THEN
128 l_minimum := OKS_BILL_REC_PUB.Get_Prorated_Usage_Qty
129 (
130 p_start_date => l_from_date,
131 p_end_date => l_to_date,
132 p_qty => l_minimum,
133 p_usage_uom => p_usage_period,
134 p_billing_uom => p_uom_code,
135 p_period_type => p_period_type
136 );
137 IF Nvl(l_minimum,0) = 0 THEN
138 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error OKS_BILL_REC_PUB.Get_Prorated_Usage_Qty returns l_minimum as '||l_minimum);
139 Raise G_EXCEPTION_HALT_VALIDATION;
140 END IF;
141 l_minimum := Round(l_minimum,0);
142 ELSE
143 --Existing logic
144 l_temp := OKS_TIME_MEASURES_PUB.GET_TARGET_QTY
145 (
146 p_start_date => l_from_date,
147 p_source_qty => 1,
148 p_source_uom => p_usage_period,
149 p_target_uom => p_time_uom_code,
150 p_round_dec => 0
151 );
152
153 IF Nvl(l_temp,0) = 0 THEN
154 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error Get_target_qty returns Zero');
155 Raise G_EXCEPTION_HALT_VALIDATION;
156 END IF;
157 l_minimum := Round((trunc(l_to_date) - trunc(l_from_date) + 1)
158 * (l_minimum /l_temp) ,0) ;
159 END IF;
160 ------------------------------------------------------------------------------
161 END IF;
162
163 IF (l_bill_qty_rec.level_yn = 'Y') THEN
164 p_cov_tbl(l_ptr).result := nvl(p_level_qty, 0);
165 IF (p_level_qty = 0) THEN
166 p_cov_tbl(l_ptr).adjustment_level := 0;
167 ELSE
168 p_cov_tbl(l_ptr).adjustment_level := p_cov_tbl(l_ptr).result - p_qty;
169 END IF;
170
171 p_cov_tbl(l_ptr).adjustment_minimum := 0;
172 --p_line_tbl(l_ptr).item_qty := nvl(p_level_qty, 0);
173
174 IF ((p_level_qty < l_minimum) AND (nvl(p_cov_tbl(l_ptr).flag,'X') <> 'S')) THEN
175 p_cov_tbl(l_ptr).result := l_minimum;
176 -- BUG FIX 3402724.
177 -- populate adjustment_minimum with L_minimum
178 p_cov_tbl(l_ptr).adjustment_minimum := l_minimum ; -- p_level_qty;
179 END IF;
180
181 ELSIF (nvl(p_cov_tbl(l_ptr).flag,'X') not in ('D','S')) THEN
182 IF ((p_cov_tbl(l_ptr).result < l_minimum) AND (l_bill_qty_rec.Minimum_qty is NOT NULL)) THEN
183 p_cov_tbl(l_ptr).result := l_minimum;
184 -- BUG FIX 3402724.
185 -- populate adjustment_minimum with L_minimum
186 p_cov_tbl(l_ptr).adjustment_minimum := l_minimum ; -- p_level_qty;
187 END IF;
188
189
190 END IF;
191
192 /* If flag is Default */
193 -- BUG FIX 3443896.Default qty in billing history not populated properly
194 IF ( p_cov_tbl(l_ptr).flag = 'D') THEN
195 p_cov_tbl(l_ptr).default_default := p_cov_tbl(l_ptr).result; -- p_qty ;
196 /* If flag is AMCV */
197 ELSIF (p_cov_tbl(l_ptr).flag = 'M') THEN
198 p_cov_tbl(l_ptr).amcv_yn := 'Y';
199 --p_cov_tbl(l_ptr).default_default := p_qty ;
200 END IF;
201
202 IF ( sign(p_cov_tbl(l_ptr).result) = -1) THEN
203
204 IF (l_negative_yn = 'YES') THEN
205 p_cov_tbl(l_ptr).sign := -1;
206 p_cov_tbl(l_ptr).result := abs(p_cov_tbl(l_ptr).result);
207 --p_line_tbl(l_ptr).item_qty := abs(p_line_tbl(l_ptr).item_qty );
208
209 ELSE
210 p_cov_tbl(l_ptr).sign := 1;
211 p_cov_tbl(l_ptr).result := nvl(l_minimum,0);
212 --p_line_tbl(l_ptr).item_qty := 0;
213 END IF;
214 ELSE
215 p_cov_tbl(l_ptr).sign := 1;
216 END IF ;
217
218
219 /****
220 added this code for new pricing API to retain the Quantity and
221 to be passed as a parameter to pricing API
222 ****/
223 p_cov_tbl(l_ptr).average := nvl(p_cov_tbl(l_ptr).result, 0);
224
225 END LOOP;
226
227 EXCEPTION
228 WHEN G_EXCEPTION_HALT_VALIDATION THEN
229 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error in Level Procedure --G_exception_halt_validation raised' || sqlerrm);
230 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
231 WHEN OTHERS THEN
232 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error in Level Procedure --OTHERS Exception raised' || sqlerrm);
233 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
234 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
235
236 END level;
237
238
239 /* Procedure to calculate AMCV */
240 PROCEDURE OKS_REG_GET_AMCV
241 (
242 X_Return_Status OUT NOCOPY VARCHAR2,
243 P_cle_id IN NUMBER,
244 X_Volume OUT NOCOPY NUMBER
245 )
246 IS
247 l_mth NUMBER := 1;
248 l_prddays NUMBER;
249 l_TotVol NUMBER;
250 l_Date_Billed_From DATE;
251 l_Date_Billed_To DATE;
252
253 Cursor l_date_csr Is
254 SELECT Min(bsl.DATE_BILLED_FROM)
255 ,Max(bsl.DATE_BILLED_TO)
256 FROM oks_bill_cont_lines bcl ,
257 oks_bill_sub_lines bsl
258 WHERE bsl.cle_id = p_cle_id
259 AND bsl.bcl_id = bcl.id
260 AND bcl.bill_action <> 'AVG';
261
262 Cursor l_tot_csr Is
263 SELECT NVL(Sum(NVL(bsd.Result,0)),0)
264 FROM oks_bill_cont_lines bcl,
265 oks_bill_sub_lines bsl,
266 oks_bill_sub_line_dtls bsd
267 WHERE bsl.cle_id = p_cle_id
268 AND bsl.bcl_id = bcl.id
269 AND bcl.bill_action <> 'AVG'
270 AND bsd.bsl_id = bsl.id;
271
272
273
274 BEGIN
275 x_Volume := 0;
276 l_totvol := 0;
277 x_Return_Status := OKC_API.G_RET_STS_SUCCESS;
278
279
280 OPEN l_date_csr;
281 FETCH l_date_csr into l_date_billed_from,l_date_billed_to;
282 CLOSE l_date_csr;
283
284 OPEN l_tot_csr;
285 FETCH l_tot_csr into l_totvol;
286 CLOSE l_tot_csr;
287
288 l_prddays := trunc(l_Date_Billed_To) - trunc(l_Date_Billed_From) + 1;
289
290
291 x_Volume := Round(l_TotVol / l_prddays,0);
292
293
294 EXCEPTION
295 WHEN G_EXCEPTION_HALT_VALIDATION THEN
296 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error in OKS_REG_GET_AMCV -- G_Exception_halt_validation raised' );
297 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
298 WHEN OTHERS THEN
299 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error in OKS_REG_GET_AMCV -- Others Exception raised' );
300 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,
301 G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
302 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
303
304
305
306 END OKS_REG_GET_AMCV;
307
308 /*Procedure Create_line_for_report is for billing Output report.
309 It copies each processed line in table
310 */
311 Procedure Create_line_for_report(
312 P_L_PR_TBL_IDX IN NUMBER,
313 P_DNZ_CHR_ID IN NUMBER,
314 P_CONTRACT_NUMBER IN VARCHAR2,
315 P_CONTRACT_NUMBER_MODIFIER IN VARCHAR2,
316 P_CURRENCY_CODE IN VARCHAR2,
317 P_INV_ORGANIZATION_ID IN NUMBER,
318 P_ID IN NUMBER,
319 P_LINE_NUMBER IN VARCHAR2,
320 P_CLE_ID IN NUMBER,
321 P_LSE_ID IN NUMBER,
322 P_OBJECT1_ID1 IN VARCHAR2,
323 P_OBJECT1_ID2 IN VARCHAR2,
324 P_SUMMARY_YN IN VARCHAR2)
325 IS
326 BEGIN
327 l_processed_lines_tbl(l_pr_tbl_idx).dnz_chr_id := p_dnz_chr_id ;
328 l_processed_lines_tbl(l_pr_tbl_idx).Contract_number := p_Contract_number ;
329 l_processed_lines_tbl(l_pr_tbl_idx).Contract_number_modifier := p_Contract_number_modifier ;
330 l_processed_lines_tbl(l_pr_tbl_idx).Currency_code := p_Currency_code;
331 l_processed_lines_tbl(l_pr_tbl_idx).Organization_id := p_Inv_Organization_id;
332 l_processed_lines_tbl(l_pr_tbl_idx).Line_id := p_id ;
333 l_processed_lines_tbl(l_pr_tbl_idx).Line_Number := p_Line_Number ;
334 l_processed_lines_tbl(l_pr_tbl_idx).Cle_id := p_cle_id ;
335 l_processed_lines_tbl(l_pr_tbl_idx).Lse_Id := p_lse_id ;
336 l_processed_lines_tbl(l_pr_tbl_idx).Sub_line_id := Null ;
337 l_processed_lines_tbl(l_pr_tbl_idx).Sub_line_Number := Null ;
338 l_processed_lines_tbl(l_pr_tbl_idx).Pty_object1_id1 := p_object1_id1 ;
339 l_processed_lines_tbl(l_pr_tbl_idx).Pty_object1_id2 := p_object1_id2 ;
340 l_processed_lines_tbl(l_pr_tbl_idx).record_type := 'LINE' ;
341 l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'Y' ;
342 l_processed_lines_tbl(l_pr_tbl_idx).Bill_Amount := 0 ;
343 l_processed_lines_tbl(l_pr_tbl_idx).Line_Type := Null ;
344
345 IF (l_processed_lines_tbl(l_pr_tbl_idx).lse_id = 12) THEN
346 l_processed_lines_tbl(l_pr_tbl_idx).Summary_bill_YN := 'N';
347 ELSIF (l_processed_lines_tbl(l_pr_tbl_idx).lse_id = 46) THEN
348 l_processed_lines_tbl(l_pr_tbl_idx).Summary_bill_YN := 'Y';
349 ELSE
350 IF (p_summary_yn = 'Y') THEN
351 l_processed_lines_tbl(l_pr_tbl_idx).Summary_bill_YN := 'Y';
352 ELSE
353 IF ( FND_PROFILE.VALUE('OKS_AR_TRANSACTIONS_SUBMIT_SUMMARY_YN') = 'YES') THEN
354 l_processed_lines_tbl(l_pr_tbl_idx).Summary_bill_YN := 'Y';
355 ELSE
356 l_processed_lines_tbl(l_pr_tbl_idx).Summary_bill_YN := 'N';
357 END IF;
358 END IF;
359 END IF;
360 END;
361
362
363 /*Procedure Create_subline_for_report is for billing Output report.
364 It copies each processed subline in table
365 */
366 Procedure Create_subline_for_report(
367 l_prs_tbl_idx IN NUMBER ,
368 p_dnz_chr_id IN NUMBER,
369 p_contract_number IN VARCHAR2,
370 p_con_num_modifier IN VARCHAR2,
371 p_currency_code IN VARCHAR2,
372 p_inv_organization_id IN NUMBER,
373 p_line_id IN NUMBER,
374 p_line_number IN VARCHAR2,
375 p_lse_id IN NUMBER,
376 p_cov_id IN NUMBER,
377 p_cov_line_number IN VARCHAR2,
378 p_object1_id1 IN VARCHAR2,
379 p_object1_id2 IN VARCHAR2,
380 p_line_type IN VARCHAR2,
381 p_amount IN NUMBER,
382 p_summary_yn IN VARCHAR2
383 )
384 IS
385 BEGIN
386 l_processed_sub_lines_tbl(l_prs_tbl_idx).dnz_chr_id := p_dnz_chr_id ;
387 l_processed_sub_lines_tbl(l_prs_tbl_idx).Contract_number := p_Contract_number ;
388 l_processed_sub_lines_tbl(l_prs_tbl_idx).Contract_number_modifier := p_con_num_modifier ;
389 l_processed_sub_lines_tbl(l_prs_tbl_idx).Currency_code := p_Currency_code;
390 l_processed_sub_lines_tbl(l_prs_tbl_idx).Organization_id := p_Inv_Organization_id;
391 l_processed_sub_lines_tbl(l_prs_tbl_idx).Line_id := p_line_id ;
392 l_processed_sub_lines_tbl(l_prs_tbl_idx).Line_Number := p_line_Number ;
393 l_processed_sub_lines_tbl(l_prs_tbl_idx).Cle_id := p_line_id ;
394 l_processed_sub_lines_tbl(l_prs_tbl_idx).Lse_Id := p_lse_id ;
395 l_processed_sub_lines_tbl(l_prs_tbl_idx).Sub_line_id := p_cov_id ;
396 l_processed_sub_lines_tbl(l_prs_tbl_idx).Sub_line_Number := p_Line_Number||'.'||p_cov_line_number ;
397 l_processed_sub_lines_tbl(l_prs_tbl_idx).Pty_object1_id1 := p_object1_id1 ;
398 l_processed_sub_lines_tbl(l_prs_tbl_idx).Pty_object1_id2 := p_object1_id2 ;
399 l_processed_sub_lines_tbl(l_prs_tbl_idx).record_type := 'SUB_LINE' ;
400 l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'Y' ;
401 --l_processed_sub_lines_tbl(l_prs_tbl_idx).Bill_Amount := 0 ;
402 l_processed_sub_lines_tbl(l_prs_tbl_idx).Line_Type := p_line_type ;
403
404 IF (l_processed_sub_lines_tbl(l_prs_tbl_idx).lse_id = 12) THEN
405 l_processed_sub_lines_tbl(l_prs_tbl_idx).Summary_bill_YN := 'N';
406 ELSIF (l_processed_sub_lines_tbl(l_prs_tbl_idx).lse_id = 46) THEN
407 l_processed_sub_lines_tbl(l_prs_tbl_idx).Summary_bill_YN := 'Y';
408 ELSE
409 IF (p_summary_yn = 'Y') THEN
410 l_processed_sub_lines_tbl(l_prs_tbl_idx).Summary_bill_YN := 'Y';
411 ELSE
412 IF ( FND_PROFILE.VALUE('OKS_AR_TRANSACTIONS_SUBMIT_SUMMARY_YN') = 'YES') THEN
413 l_processed_sub_lines_tbl(l_prs_tbl_idx).Summary_bill_YN := 'Y';
414 ELSE
415 l_processed_sub_lines_tbl(l_prs_tbl_idx).Summary_bill_YN := 'N';
416 END IF;
417 END IF;
418 END IF;
419
420
421 END Create_subline_for_report;
422
423 -------------------------------------------------------------------------
424 -- Begin partial period computation logic
425 -- Developer Mani Choudhary
426 -- Date 10-JUN-2005
427 -- Added parameters p_period_type,p_period_start
428 -------------------------------------------------------------------------
429 Procedure Bill_usage_item(
430 p_dnz_chr_id IN NUMBER,
431 p_contract_number IN VARCHAR2,
432 p_con_num_modifier IN VARCHAR2,
433 p_line_number IN NUMBER,
434 p_lse_id IN NUMBER,
435 p_object1_id1 IN VARCHAR2,
436 p_object1_id2 IN VARCHAR2,
437 p_top_line_id IN NUMBER,
438 p_top_line_start_date IN DATE,
439 p_top_line_term_date IN DATE,
440 p_top_line_end_date IN DATE,
441 p_inv_organization_id IN NUMBER,
442 p_currency_code IN VARCHAR2,
443 p_settlement_interval IN VARCHAR2,
444 p_uom_code IN VARCHAR2,
445 p_time_uom_code IN VARCHAR2,
446 p_okl_flag IN NUMBER,
447 p_prv IN NUMBER,
448 p_date IN DATE,
449 p_billrep_tbl IN OUT NOCOPY OKS_BILL_REC_PUB.bill_report_tbl_type,
450 p_billrep_tbl_idx IN NUMBER,
451 p_billrep_err_tbl IN OUT NOCOPY OKS_BILL_REC_PUB.billrep_error_tbl_type,
452 p_billrep_err_tbl_idx IN OUT NOCOPY NUMBER,
453 p_ar_feeder_ctr IN OUT NOCOPY NUMBER,
454 p_period_type IN VARCHAR2,
455 p_period_start IN VARCHAR2,
456 p_return_status IN OUT NOCOPY VARCHAR2
457 )
458 IS
459 l_usage_type VARCHAR2(10);
460 l_usage_period VARCHAR2(10);
461 l_counter_uom_code VARCHAR2(30);
462 l_flag VARCHAR2(10);
463 l_prorate VARCHAR2(10);
464 l_break_uom_code VARCHAR2(10);
465 e_ptr NUMBER;
466 l_ptr NUMBER;
467 l_qty NUMBER;
468 l_level_qty NUMBER;
469 l_break_amount NUMBER;
470 l_temp NUMBER;
471 l_sign NUMBER;
472 no_of_cycles NUMBER;
473 l_line_total NUMBER;
474 l_total NUMBER;
475 l_final_qty NUMBER;
476 l_bcl_id NUMBER;
477 l_amount NUMBER;
478 l_init_value NUMBER;
479 l_final_value NUMBER;
480 l_base_reading NUMBER;
481 l_counter_value_id NUMBER;
482 l_counter_grp_id NUMBER;
483 l_sub_id NUMBER;
484 l_tsub_id NUMBER;
485 l_estimated_qty NUMBER;
486 l_actual_qty NUMBER;
487 l_quantity_ordered NUMBER;
488 l_locked_price_list_id NUMBER;
489 l_locked_price_list_line_id NUMBER;
490 i NUMBER;
491 l_inv_date DATE;
492 l_ar_inv_date DATE;
493 l_bill_start_date DATE;
494 l_bill_end_date DATE;
495 l_exception_amount NUMBER;
496 l_subline_count NUMBER := 0;
497 l_counter_id NUMBER;
498 l_lock_id NUMBER;
499 l_object_version NUMBER;
500 l_lock_date DATE;
501
502
503 /* Variable for calling std API*/
504 l_api_version CONSTANT NUMBER := 1.0;
505 l_called_from CONSTANT NUMBER := 1;
506 l_init_msg_list CONSTANT VARCHAR2(1) := 'F';
507 l_msg_count NUMBER;
508 l_msg_data VARCHAR2(2000);
509 l_return_status VARCHAR2(1);
510
511
512 l_counter_reading_lock_rec csi_ctr_datastructures_pub.ctr_reading_lock_rec;
513
514 l_pbr_rec_in OKS_PBR_PVT.pbrv_rec_type;
515 l_pbr_rec_out OKS_PBR_PVT.pbrv_rec_type;
516 -------------------------------------------------------------------------
517 -- Begin partial period computation logic
518 -- Developer Mani Choudhary
519 -- Date 11-JUN-2005
520 -- local variables and cursors declared
521 -------------------------------------------------------------------------
522 l_rule_id NUMBER;
523 l_uom_code VARCHAR2(30);
524 CURSOR l_billing_uom_csr(p_rul_id IN NUMBER) IS
525 SELECT uom_code
526 FROM oks_stream_levels_b
527 WHERE id = p_rul_id;
528 -------------------------------------------------------------------------
529
530 CURSOR l_inv_item_csr(p_cle_id Number,p_org_id Number) Is
531 SELECT item.Object1_id1
532 ,mtl.usage_item_flag
533 ,mtl.service_item_flag
534 ,mtl.primary_uom_code
535 FROM Okc_K_items Item
536 ,mtl_system_items_b mtl --Okx_system_items_v mtl
537 WHERE item.cle_id = p_cle_id
538 --AND mtl.id1 = item.object1_id1
539 AND mtl.inventory_item_id = item.object1_id1
540 AND mtl.organization_id = p_org_id;
541
542
543 CURSOR l_usage_csr (p_cle_id IN NUMBER) IS
544 SELECT usage_type Usage_Type,
545 usage_period Usage_period,
546 prorate Prorate,
547 locked_price_list_id locked_price_list_id,
548 locked_price_list_line_id locked_price_list_line_id
549 FROM OKS_K_LINES_B
550 WHERE cle_id = p_cle_id ;
551
552 Cursor qty_uom_csr_sub(p_cle_id Number) Is
553 SELECT okc.Number_of_items
554 ,tl.Unit_of_measure uom_code
555 FROM okc_k_items okc
556 ,mtl_units_of_measure_tl tl
557 WHERE okc.cle_id = p_cle_id
558 AND tl.uom_code = okc.uom_code
559 AND tl.language = USERENV('LANG');
560
561 /*
562 SELECT Number_of_items
563 ,OKX.Unit_of_measure uom_code
564 FROM OKC_K_ITEMS OKC
565 ,OKX_UNITS_OF_MEASURE_V OKX
566 WHERE cle_id = P_cle_id
567 AND Okx.uom_code = OKC.uom_code ;
568 */
569
570 Cursor bsl_price_csr(p_bcl_id IN NUMBER, p_prv IN NUMBER) is
571 SELECT bsl.id bsl_id, bsl.average average, bsd.unit_of_measure uom_code,
572 bsl.date_billed_from ,bsl.date_billed_to,
573 bsl.cle_id, rline.prorate,
574 rline.locked_price_list_id,
575 rline.locked_price_list_line_id,
576 rline.dnz_chr_id
577 FROM
578 oks_k_lines_b rline,
579 oks_bill_sub_lines bsl,
580 oks_bill_sub_line_dtls bsd
581 WHERE bsl.bcl_id = p_bcl_id
582 AND bsl.id = bsd.bsl_id
583 AND rline.cle_id = bsl.cle_id
584 AND p_prv = 1
585 UNION
586 SELECT bsl.id bsl_id, bsl.average average, bsd.unit_of_measure uom_code,
587 bsl.date_billed_from ,bsl.date_billed_to,
588 bsl.cle_id , rline.prorate,
589 rline.LOCKED_PRICE_LIST_ID,
590 rline.locked_price_list_line_id,
591 rline.dnz_chr_id
592 FROM
593 oks_k_lines_b rline,
594 oks_bsl_pr bsl,
595 oks_bsd_pr bsd
596 WHERE bsl.bcl_id = p_bcl_id
597 AND bsl.id = bsd.bsl_id
598 AND rline.cle_id = bsl.cle_id
599 AND p_prv = 2;
600
601
602 CURSOR l_subline_Csr(p_cle_id Number) Is
603 SELECT
604 sub_line.id id
605 ,sub_line.cle_id cle_id
606 ,sub_line.dnz_chr_id dnz_chr_id
607 ,sub_line.price_negotiated price_negotiated
608 ,sub_line.start_date start_date
609 ,sub_line.end_date end_date
610 ,sub_line.date_terminated date_terminated
611 ,sub_line.line_number line_number
612 ,rul.fixed_quantity fixed_qty
613 ,rul.minimum_quantity minimum_qty
614 ,rul.default_quantity default_qty
615 ,rul.amcv_flag amcv_flag
616 ,rul.usage_period usage_period
617 ,rul.usage_duration usage_duration
618 ,rul.level_yn level_yn
619 ,rul.base_reading base_reading
620 ,rul.usage_type usage_Type
621 ,rul.usage_est_yn usage_est_yn
622 ,rul.usage_est_method usage_est_method
623 ,rul.usage_est_start_date usage_est_start_date
624 FROM OKC_K_LINES_B sub_line ,
625 OKS_K_LINES_B rul
626 WHERE sub_line.cle_id = p_cle_id
627 AND sub_line.date_cancelled is NULL --[llc]
628 AND sub_line.id = rul.cle_id
629 AND sub_line.lse_id in (8,7,9,10,11,13,25,35)
630 AND not exists ( select 1 from okc_k_rel_objs rel
631 WHERE rel.cle_id = sub_line.id );
632
633
634 /*FOR BILLING REPORT*/
635
636 Cursor subline_count(p_cle_id Number) Is
637 SELECT count(sub_line.id)
638 FROM OKC_K_LINES_B sub_line
639 WHERE sub_line.cle_id = p_cle_id
640 AND sub_line.lse_id in (8,7,9,10,11,13,25,35)
641 AND sub_line.date_cancelled is NULL --[llc]
642 AND not exists ( select 1 from okc_k_rel_objs rel
643 where rel.cle_id = sub_line.id );
644
645
646 Cursor get_counter_qty(p_cle_id Number, p_lock_read number) IS
647 select value_timestamp, counter_id
648 from cs_counter_values, okc_k_items
649 where cle_id = p_cle_id
650 and to_char(counter_id) = object1_id1
651 and counter_reading = p_lock_read;
652
653 Cursor get_counter_vrt(p_cle_id Number) IS
654 select to_number(object1_id1)
655 from okc_k_items
656 where cle_id = p_cle_id;
657
658
659 --23-DEC-2005 mchoudha fix for bug#4915367
660 Cursor bill_amount_npr (p_id IN NUMBER,p_hdr_id IN NUMBER,p_date_start IN DATE,p_date_end IN DATE) IS
661 SELECT lvl.amount
662 FROM oks_level_elements lvl
663 WHERE lvl.cle_id = p_id
664 And lvl.dnz_chr_id = p_hdr_id
665 And lvl.date_start = p_date_start
666 And lvl.date_end = p_date_end;
667
668
669 l_item_rec l_inv_item_csr%ROWTYPE;
670 l_qty_uom_sub_rec qty_uom_csr_sub%ROWTYPE;
671 l_subline_id NUMBER;
672
673 usage_exception EXCEPTION;
674
675 BEGIN
676 If l_write_log then
677 FND_FILE.PUT_LINE(FND_FILE.LOG,'***Processing Usage Item ***');
678 FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Id: ' || p_top_line_id);
679 FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Start Date: ' || p_top_line_start_date);
680 FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Termination Date/End date: ' || nvl(p_top_line_term_date,p_top_line_end_date));
681 End If;
682
683
684 p_return_status := 'S';
685 l_prorate := '';
686 l_locked_price_list_id:= '';
687 l_locked_price_list_line_id:= '';
688
689 --Start mchoudha Bug#3537100 22-APR-04
690 --For Billing Report
691 OPEN subline_count(p_top_line_id);
692 FETCH subline_count into l_subline_count;
693 CLOSE subline_count;
694 --End mchoudha Bug#3537100
695
696
697 OPEN l_inv_item_csr(p_top_line_id,p_inv_organization_id);
698 FETCH l_inv_item_csr into l_item_rec;
699 CLOSE l_inv_item_csr;
700
701 OPEN l_usage_csr(p_top_line_id);
702 FETCH l_usage_csr into l_usage_type, l_usage_period , l_prorate ,
703 l_locked_price_list_id, l_locked_price_list_line_id;
704 CLOSE l_usage_csr;
705
706 l_processed_lines_tbl(l_pr_tbl_idx).record_type := 'Usage' ;
707 level_elements_tab.delete;
708
709 OKS_BILL_UTIL_PUB.get_next_level_element(
710 P_API_VERSION => l_api_version,
711 P_ID => p_top_line_id,
712 P_COVD_FLAG => 'N', ---- flag to indicate Top line
713 P_DATE => p_date,
714 P_INIT_MSG_LIST => l_init_msg_list,
715 X_RETURN_STATUS => l_return_status,
716 X_MSG_COUNT => l_msg_count,
717 X_MSG_DATA => l_msg_data,
718 X_NEXT_LEVEL_ELEMENT => level_elements_tab );
719
720 IF (l_return_status <> 'S') THEN
721 oks_bill_rec_pub.get_message(
722 l_msg_cnt => l_msg_count,
723 l_msg_data => l_msg_data);
724 l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
725 l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
726
727 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Failed in getting next level ');
728
729 /*Needs to determine or revisited */
730 --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
731 Raise USAGE_EXCEPTION;
732 END IF;
733
734 IF (level_elements_tab.count < 1) THEN
735 l_processed_lines_tbl.DELETE(l_pr_tbl_idx) ;
736 END IF;
737
738 e_ptr := 1;
739 no_of_cycles := level_elements_tab.count;
740 l_tsub_id := l_prs_tbl_idx;
741
742 WHILE (e_ptr <= no_of_cycles)
743 LOOP
744 l_line_total := l_line_total + level_elements_tab(e_ptr).bill_amount ;
745
746 l_inv_date := level_elements_tab(e_ptr).date_to_interface;
747 l_ar_inv_date := level_elements_tab(e_ptr).date_transaction;
748 l_bill_start_date := level_elements_tab(e_ptr).bill_from_date;
749 l_bill_end_date := level_elements_tab(e_ptr).bill_to_date;
750 -------------------------------------------------------------------------
751 -- Begin partial period computation logic
752 -- Developer Mani Choudhary
753 -- Date 11-JUN-2005
754 -- get the rul_id for oks_level_elements
755 -------------------------------------------------------------------------
756 IF p_period_type IS NOT NULL AND
757 p_period_start IS NOT NULL
758 THEN
759 l_rule_id := level_elements_tab(e_ptr).rule_id;
760 OPEN l_billing_uom_csr(l_rule_id);
761 FETCH l_billing_uom_csr INTO l_uom_code;
762 CLOSE l_billing_uom_csr;
763 END IF;
764 --------------------------------------------------------------------------
765 /* Date_billed_to of top line should be manipulated if
766 termination_date lies between start_date and end_date
767 of billing period
768 */
769
770 IF ( ( p_top_line_term_date >= l_bill_start_date) AND
771 ( p_top_line_term_date <= l_bill_end_date) ) THEN
772 l_bill_end_date := p_top_line_term_date - 1 ;
773 END IF;
774
775 If l_write_log then
776 FND_FILE.PUT_LINE( FND_FILE.LOG,'Line Interface Date :' ||l_inv_date );
777 FND_FILE.PUT_LINE( FND_FILE.LOG,'Billing Period Start_date: ' || l_bill_start_date||' To '||l_bill_end_date);
778 End If;
779
780 l_ptr := 0;
781 l_total := 0;
782 l_final_qty := 0;
783
784 l_cov_tbl.delete;
785
786 IF (trunc(l_inv_date) <= trunc(p_date)) THEN
787
788 /*******
789 create bank account is no longer needed as part of R12 Bank account
790 consolidation project
791
792 OKS_BILL_REC_PUB.create_bank_Account(
793 p_dnz_chr_id => p_dnz_chr_id,
794 p_bill_start_date => p_top_line_start_date,
795 p_currency_code => p_currency_code,
796 x_status => l_return_status,
797 l_msg_count => l_msg_count,
798 l_msg_data => l_msg_data
799 );
800
801 IF (l_return_status <> 'S') THEN
802 OKS_BILL_REC_PUB.get_message
803 (l_msg_cnt => l_msg_count,
804 l_msg_data => l_msg_data);
805 l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N';
806 l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
807
808 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Failed in creating account');
809
810
811
812 --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION') ;
813 Raise USAGE_EXCEPTION;
814 END IF;
815
816 *******/
817
818 FOR l_covlvl_rec in l_subline_csr(p_top_line_id)
819 LOOP
820
821 IF ( ((l_covlvl_rec.date_terminated is not null) and
822 (l_covlvl_rec.date_terminated > l_bill_start_date)) --l_inv_date))
823 OR
824 (l_covlvl_rec.date_terminated is null))THEN
825 IF ((l_ar_inv_date is not null)
826 And (trunc(l_ar_inv_date) < trunc(sysdate))) THEN
827 l_ar_inv_date := sysdate;
828 END IF;
829
830 /*FOR BILLING REPORT*/
831 l_subline_id := l_covlvl_rec.id;
832
833 OKS_BILL_REC_PUB.Insert_bcl
834 (P_CALLEDFROM => l_called_from,
835 X_RETURN_STAT => l_return_status,
836 P_CLE_ID => p_top_line_id,
837 P_DATE_BILLED_FROM => l_bill_start_date,
838 P_DATE_BILLED_TO => l_bill_end_date,
839 P_DATE_NEXT_INVOICE => l_ar_inv_date,
840 P_BILL_ACTION => 'RI',
841 P_OKL_FLAG => p_okl_flag,
842 P_PRV => p_prv,
843 P_MSG_COUNT => l_msg_count,
844 P_MSG_DATA => l_msg_data,
845 X_BCL_ID => l_bcl_id);
846
847 IF (l_return_status <> 'S') THEN
848 oks_bill_rec_pub.get_message
849 (l_msg_cnt => l_msg_count,
850 l_msg_data => l_msg_data);
851 l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
852 l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
853 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Failed in insert bcl');
854
855 --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
856 Raise USAGE_EXCEPTION;
857 END IF;
858
859 IF (p_prv <> 2) THEN
860 UPDATE oks_level_elements
861 SET date_completed = l_bill_end_date
862 WHERE id = level_elements_tab(e_ptr).id;
863 END IF;
864
865 l_ptr := l_ptr + 1;
866 level_coverage.delete;
867
868 OKS_BILL_UTIL_PUB.get_next_level_element(
869 P_API_VERSION => l_api_version,
870 P_ID => l_covlvl_rec.id,
871 P_COVD_FLAG => 'Y', -- flag to indicate Covered level
872 P_DATE => l_inv_date , --l_bill_end_date,
873 P_INIT_MSG_LIST => l_init_msg_list,
874 X_RETURN_STATUS => l_return_status,
875 X_MSG_COUNT => l_msg_count,
876 X_MSG_DATA => l_msg_data,
877 X_NEXT_LEVEL_ELEMENT => level_coverage );
878
879 IF ((l_return_status <> 'S')
880 OR (level_coverage.count = 0)) THEN
881 OKS_BILL_REC_PUB.get_message
882 (l_msg_cnt => l_msg_count,
883 l_msg_data => l_msg_data);
884 l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
885 l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
886 l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'N' ;
887 l_processed_sub_lines_tbl(l_prs_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
888 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Failed in get next level of coverage ');
889 --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
890 Raise USAGE_EXCEPTION;
891 END IF;
892
893 /*Update date completed in coverage line */
894 IF (P_PRV <> 2) THEN
895 UPDATE oks_level_elements
896 SET date_completed = l_bill_end_date
897 WHERE id = level_coverage(1).id;
898 END IF;
899
900 l_amount := nvl(level_coverage(1).bill_amount,0);
901 l_calc_rec.l_calc_sdate := level_coverage(1).bill_from_date;
902 l_calc_rec.l_calc_edate := level_coverage(1).bill_to_date;
903
904 IF (l_write_log) THEN
905 FND_FILE.PUT_LINE( FND_FILE.LOG,' Coverage amount : '||l_amount);
906 FND_FILE.PUT_LINE( FND_FILE.LOG,' Coverage start_date: '||l_calc_rec.l_calc_sdate);
907 FND_FILE.PUT_LINE( FND_FILE.LOG,' Coverage end_date : '||l_calc_rec.l_calc_edate);
908 END IF;
909
910 IF ( l_covlvl_rec.date_terminated is not null) AND
911 ( trunc(l_calc_rec.l_calc_edate) >=
912 trunc(l_covlvl_rec.date_terminated)) THEN
913 l_calc_rec.l_calc_edate := l_covlvl_rec.date_terminated - 1;
914 END IF;
915
916 l_cov_tbl(l_ptr).flag := Null;
917 l_cov_tbl(l_ptr).id := l_covlvl_rec.id ;
918 l_cov_tbl(l_ptr).bcl_id := l_bcl_id;
919 l_cov_tbl(l_ptr).date_billed_from := l_calc_rec.l_calc_sdate;
920 l_cov_tbl(l_ptr).date_billed_to := l_calc_rec.l_calc_edate;
921
922 IF (p_prv = 2) THEN -- FIX for BUG# 2998682
923 l_cov_tbl(l_ptr).date_billed_from
924 := level_elements_tab(e_ptr).bill_from_date;
925 l_cov_tbl(l_ptr).date_billed_to
926 := level_elements_tab(e_ptr).bill_to_date;
927
928 ----for bug 4455174
929 l_calc_rec.l_calc_sdate :=level_elements_tab(e_ptr).bill_from_date;
930 l_calc_rec.l_calc_edate :=level_elements_tab(e_ptr).bill_to_date;
931
932 --23-DEC-2005 mchoudha Fix for bug#4915367
933 --fetching amount in case of negotiated usage type
934 IF l_usage_type = 'NPR' THEN
935 Open bill_amount_npr(l_covlvl_rec.id,l_covlvl_rec.dnz_chr_id,l_cov_tbl(l_ptr).date_billed_from,
936 l_cov_tbl(l_ptr).date_billed_to);
937 Fetch bill_amount_npr into l_amount;
938 Close bill_amount_npr;
939 END IF;
940
941 END IF;
942
943 l_cov_tbl(l_ptr).amount := 0;
944 l_cov_tbl(l_ptr).average := 0;
945 l_cov_tbl(l_ptr).unit_of_measure := p_uom_code;
946 l_cov_tbl(l_ptr).fixed := 0;
947 l_cov_tbl(l_ptr).actual := null;
948 l_cov_tbl(l_ptr).default_default := 0;
949 l_cov_tbl(l_ptr).amcv_yn := NVL(l_covlvl_rec.amcv_flag,'N');
950 l_cov_tbl(l_ptr).adjustment_level := 0 ;
951 l_cov_tbl(l_ptr).adjustment_minimum:= 0 ;
952 l_cov_tbl(l_ptr).result := 1 ;--0 ; --check it out
953 l_cov_tbl(l_ptr).x_stat := Null ;
954 l_cov_tbl(l_ptr).amount := l_amount;
955 l_cov_tbl(l_ptr).bcl_amount :=nvl(l_calc_rec.l_bcl_amount,0);
956 l_cov_tbl(l_ptr).date_to_interface := sysdate;
957
958 IF (l_usage_type = 'NPR') THEN
959 IF (l_write_log) THEN
960 FND_FILE.PUT_LINE( FND_FILE.LOG, 'USAGE_TYPE of subline = NPR' );
961 END IF;
962
963 OPEN qty_uom_csr_sub(l_covlvl_rec.id);
964 FETCH qty_uom_csr_sub into l_qty_uom_sub_rec;
965 CLOSE qty_uom_csr_sub;
966
967 l_cov_tbl(l_ptr).result := 0; --l_qty_uom_sub_rec.number_of_items;
968 l_cov_tbl(l_ptr).actual := 0;
969 l_cov_tbl(l_ptr).estimated_quantity := 0;
970 l_cov_tbl(l_ptr).x_stat := null;
971 l_cov_tbl(l_ptr).unit_of_measure := l_qty_uom_sub_rec.uom_code;
972 l_cov_tbl(l_ptr).amount := l_amount;
973
974
975 ELSIF (l_usage_type = 'FRT') THEN
976 IF (l_write_log) THEN
977 FND_FILE.PUT_LINE( FND_FILE.LOG, 'USAGE_TYPE of subline = FRT');
978 END IF;
979
980 l_qty := l_covlvl_rec.fixed_qty;
981
982 IF (nvl(l_qty,0) <> 0) THEN
983 -------------------------------------------------------------------------
984 -- Begin partial period computation logic
985 -- Developer Mani Choudhary
986 -- Date 11-JUN-2005
987 -- call oks_bill_rec_pub.Get_prorated_Usage_Qty to get the prorated usage
988 -------------------------------------------------------------------------
989 IF p_period_type IS NOT NULL AND
990 p_period_start IS NOT NULL
991 THEN
992
993 l_qty := OKS_BILL_REC_PUB.Get_Prorated_Usage_Qty
994 (
995 p_start_date => l_calc_rec.l_calc_sdate,
996 p_end_date => l_calc_rec.l_calc_edate,
997 p_qty => l_qty,
998 p_usage_uom => l_usage_period,
999 p_billing_uom => l_uom_code,
1000 p_period_type => p_period_type
1001 );
1002 IF (Nvl(l_qty,0) = 0) THEN
1003 l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
1004 l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||' Target Quantity is zero ';
1005 l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'N' ;
1006 l_processed_sub_lines_tbl(l_prs_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||' Target Quantity is zero';
1007 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error Get_Prorated_Usage_Qty returns l_qty as '||l_qty);
1008
1009 --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
1010 Raise USAGE_EXCEPTION;
1011 END IF;
1012 l_qty := Round(l_qty,0);
1013 ELSE
1014 --Existing logic
1015 l_temp := OKS_TIME_MEASURES_PUB.GET_TARGET_QTY
1016 (p_start_date => l_calc_rec.l_calc_sdate,
1017 p_source_qty => 1,
1018 p_source_uom => l_usage_period,
1019 p_target_uom => p_time_uom_code,
1020 p_round_dec => 0
1021 );
1022
1023 IF (Nvl(l_temp,0) = 0) THEN
1024 l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
1025 l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||' Target Quantity is zero ';
1026 l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'N' ;
1027 l_processed_sub_lines_tbl(l_prs_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||' Target Quantity is zero';
1028 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error Get_target_qty returns Zero');
1029
1030 --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
1031 Raise USAGE_EXCEPTION;
1032 END IF;
1033
1034 l_qty := Round((l_qty * (l_calc_rec.l_calc_edate -
1035 l_calc_rec.l_calc_sdate + 1))/l_temp ,0) ;
1036
1037 END IF; --p_period_type IS NOT NULL
1038 END IF; --IF (nvl(l_qty,0) <> 0)
1039 l_cov_tbl(l_ptr).unit_of_measure := p_uom_code;
1040 l_cov_tbl(l_ptr).fixed := l_qty;
1041 l_cov_tbl(l_ptr).result := l_qty;
1042 l_cov_tbl(l_ptr).actual := 0;
1043 l_cov_tbl(l_ptr).estimated_quantity := 0;
1044 l_cov_tbl(l_ptr).sign := 1;
1045 l_cov_tbl(l_ptr).average := l_qty;
1046 l_cov_tbl(l_ptr).unit_of_measure :=l_item_rec.primary_uom_code;
1047
1048 ELSIF (l_usage_type in ('VRT','QTY')) THEN
1049 -------------------------------------------------------------------------
1050 -- Begin partial period computation logic
1051 -- Developer Mani Choudhary
1052 -- Date 11-JUN-2005
1053 -- Added additional period_type, period start parameters
1054 -------------------------------------------------------------------------
1055 OKS_BILL_REC_PUB.Usage_qty_to_bill
1056 (
1057 P_calledfrom => p_prv, --1 for normal ,2 for preview,
1058 P_cle_id => l_covlvl_rec.id,
1059 P_Usage_type => l_usage_type,
1060 P_estimation_flag => l_covlvl_rec.usage_est_yn,
1061 P_estimation_method => l_covlvl_rec.usage_est_method,
1062 p_default_qty => l_covlvl_rec.Default_qty,
1063 P_cov_start_date => l_covlvl_rec.start_date,
1064 P_cov_end_date => l_covlvl_rec.end_date,
1065 P_cov_prd_start_date => l_calc_rec.l_calc_sdate,
1066 P_cov_prd_end_date => l_calc_rec.l_calc_edate,
1067 p_usage_period => l_usage_period,
1068 p_time_uom_code => p_time_uom_code,
1069 p_settle_interval => p_settlement_interval,
1070 p_minimum_quantity => l_covlvl_rec.minimum_qty,
1071 p_usg_est_start_date => l_covlvl_rec.usage_est_start_date,
1072 p_period_type => p_period_type, --period type
1073 p_period_start => p_period_start, --period start
1074 X_qty => l_qty,
1075 X_Uom_Code => l_counter_uom_code,
1076 X_flag => l_flag,
1077 X_end_reading => l_final_value,
1078 X_start_reading => l_init_value,
1079 X_base_reading => l_base_reading,
1080 X_estimated_qty => l_estimated_qty,
1081 X_actual_qty => l_actual_qty,
1082 X_counter_value_id => l_counter_value_id,
1083 X_counter_group_id => l_counter_grp_id,
1084 X_return_status => l_return_status
1085 );
1086
1087
1088 IF (l_return_status <> 'S') THEN
1089 oks_bill_rec_pub.get_message
1090 (l_msg_cnt => l_msg_count,
1091 l_msg_data => l_msg_data);
1092 l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
1093 l_processed_lines_tbl(l_pr_tbl_idx).Error_Message:= 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
1094 l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'N' ;
1095 l_processed_sub_lines_tbl(l_prs_tbl_idx).Error_Message:= 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
1096
1097 Raise USAGE_EXCEPTION;
1098 END IF;
1099
1100
1101 IF (l_write_log) THEN
1102 FND_FILE.PUT_LINE( FND_FILE.LOG, 'After counter values X_value'||' '||l_qty);
1103 END IF;
1104
1105 l_cov_tbl(l_ptr).result :=nvl(l_qty, 0);
1106 l_cov_tbl(l_ptr).actual :=l_actual_qty ;
1107 l_cov_tbl(l_ptr).unit_of_measure :=l_item_rec.primary_uom_code;
1108 l_cov_tbl(l_ptr).start_reading :=l_init_value;
1109 l_cov_tbl(l_ptr).end_reading :=l_final_value;
1110 l_cov_tbl(l_ptr).base_reading :=l_base_reading;
1111 l_cov_tbl(l_ptr).ccr_id :=l_counter_value_id;
1112 l_cov_tbl(l_ptr).cgr_id :=l_counter_grp_id;
1113 l_cov_tbl(l_ptr).flag :=l_flag;
1114 l_cov_tbl(l_ptr).estimated_quantity :=l_estimated_qty;
1115
1116 /******
1117 code changes for R12 project IB Counters Uptake.
1118 The below code is to lock the counter for Actual Per Period and Actual By Qty
1119 *****/
1120
1121 if p_prv = 1 Then
1122 IF nvl(l_actual_qty,0) > 0 Then ---- reading captured for the billed period
1123 open get_counter_qty(l_covlvl_rec.id,l_final_value);
1124 fetch get_counter_qty into l_lock_date, l_counter_id;
1125 close get_counter_qty;
1126
1127 IF (l_write_log) THEN
1128 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Cll IB Lock '||l_lock_date||', Counter id '||l_counter_id);
1129 END IF;
1130
1131 l_counter_reading_lock_rec.reading_lock_date := l_lock_date;
1132 l_counter_reading_lock_rec.counter_id := l_counter_id;
1133 l_counter_reading_lock_rec.source_line_ref_id := l_covlvl_rec.id;
1134 l_counter_reading_lock_rec.source_line_ref := 'CONTRACT_LINE';
1135
1136 Csi_Counter_Pub.create_reading_lock
1137 (
1138 p_api_version => 1.0,
1139 p_commit => 'F',
1140 p_init_msg_list => 'T',
1141 p_validation_level => 100,
1142 p_ctr_reading_lock_rec => l_counter_reading_lock_rec,
1143 x_return_status => l_return_status,
1144 x_msg_count => l_msg_count,
1145 x_msg_data => l_msg_data,
1146 x_reading_lock_id => l_lock_id
1147 );
1148
1149 End If; --- for actual qty check
1150 End if;
1151
1152
1153 END IF; -- l_usage_type = NPR
1154
1155 IF (l_covlvl_rec.level_yn = 'Y') THEN
1156 l_final_qty := nvl(l_final_qty,0) + nvl(l_qty,0);
1157 l_total := nvl(l_total,0) + 1;
1158 END IF;
1159
1160 l_cov_tbl(l_ptr).average := nvl(l_qty, 0);
1161
1162 END IF;
1163 l_tsub_id := l_tsub_id +1;
1164 END LOOP; --Covered level for loop
1165
1166 IF (l_cov_tbl.count > 0) THEN
1167 IF (l_usage_type in ('VRT','QTY')) THEN
1168 IF (( nvl(l_total,0) <> 0) and (nvl(l_final_qty,0) <> 0) ) THEN
1169 l_level_qty := Round(l_final_qty/l_total);
1170 END IF;
1171
1172 -------------------------------------------------------------------------
1173 -- Begin partial period computation logic
1174 -- Developer Mani Choudhary
1175 -- Date 12-JUN-2005
1176 -- Added two parameters p_period_start and p_period_type
1177 -------------------------------------------------------------------------
1178 level
1179 (
1180 P_LEVEL_QTY => l_level_qty,
1181 P_COV_TBL => l_cov_tbl,
1182 P_QTY => l_qty,
1183 --P_LINE_TBL => l_line_tbl,
1184 P_USAGE_PERIOD => l_usage_period,
1185 P_TIME_UOM_CODE => p_time_uom_code,
1186 P_UOM_CODE => l_uom_code,
1187 P_PERIOD_TYPE => P_PERIOD_TYPE,
1188 P_PERIOD_START => P_PERIOD_START,
1189 X_RETURN_STATUS => l_return_status
1190 );
1191
1192 IF (l_write_log) THEN
1193 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Level '||' '||l_return_status);
1194 END IF;
1195
1196 IF (l_return_status <> 'S') THEN
1197 l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
1198 l_processed_lines_tbl(l_pr_tbl_idx).Error_Message:= 'Error: '|| sqlerrm||'. Error Message:' ;
1199
1200 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error in LEVEL ') ;
1201 --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
1202 Raise USAGE_EXCEPTION;
1203 END IF;
1204 END IF; -- l_usage_type in 'VRT','QTY'
1205
1206 p_ar_feeder_ctr := 1;
1207 l_sign := l_cov_tbl(l_ptr).sign;
1208
1209 OKS_BILL_REC_PUB.Insert_all_subline
1210 (
1211 P_CALLEDFROM => l_called_from,
1212 X_RETURN_STAT => l_return_status,
1213 P_COVERED_TBL => l_cov_tbl,
1214 P_CURRENCY_CODE => p_currency_code,
1215 P_DNZ_CHR_ID => p_dnz_chr_id,
1216 P_PRV => p_prv,
1217 P_MSG_COUNT => l_msg_count,
1218 P_MSG_DATA => l_msg_data
1219 );
1220
1221 IF (l_usage_type <> 'NPR') THEN
1222 l_sub_id := l_prs_tbl_idx;
1223
1224 FOR bsl_price_rec in bsl_price_csr(l_bcl_id,p_prv)
1225 LOOP
1226 l_price_break_details.delete;
1227
1228 l_line_rec.line_id := p_top_line_id;
1229 l_line_rec.intent := 'USG';
1230 l_line_rec.usage_qty := bsl_price_rec.average; -- qty
1231 l_line_rec.usage_uom_code := bsl_price_rec.uom_code;
1232 l_line_rec.bsl_id := bsl_price_rec.bsl_id;
1233 l_line_rec.subline_id := bsl_price_rec.cle_id;
1234
1235 IF ( nvl(bsl_price_rec.prorate,l_prorate) = 'ALL') THEN
1236 l_line_rec.bill_from_date := bsl_price_rec.date_billed_from;
1237 l_line_rec.bill_to_date := bsl_price_rec.date_billed_to;
1238
1239
1240 OKS_TIME_MEASURES_PUB.get_duration_uom
1241 ( P_START_DATE => bsl_price_rec.date_billed_from,
1242 P_END_DATE => bsl_price_rec.date_billed_to,
1243 X_DURATION => l_quantity_ordered,
1244 X_TIMEUNIT => l_break_uom_code,
1245 X_RETURN_STATUS => l_return_status
1246 );
1247
1248 l_line_rec.break_uom_code := l_break_uom_code;
1249
1250 ELSE
1251 l_line_rec.bill_from_date := '';
1252 l_line_rec.bill_to_date := '';
1253 --mchoudha bug#4128070 22-JAN-2005
1254 l_line_rec.break_uom_code := NULL;
1255 END IF;
1256
1257
1258 l_line_rec.price_list
1259 :=nvl(bsl_price_rec.LOCKED_PRICE_LIST_ID,l_locked_price_list_id);
1260 l_line_rec.price_list_line_id
1261 :=nvl(bsl_price_rec.locked_price_list_line_id,l_locked_price_list_line_id);
1262
1263
1264 /*Pricing API to calculate amount */
1265 OKS_QP_PKG.CALC_PRICE
1266 (
1267 P_DETAIL_REC => l_line_rec,
1268 X_PRICE_DETAILS => l_price_rec,
1269 X_MODIFIER_DETAILS => l_modifier_details,
1270 X_PRICE_BREAK_DETAILS => l_price_break_details,
1271 X_RETURN_STATUS => l_return_status,
1272 X_MSG_COUNT => l_msg_count,
1273 X_MSG_DATA => l_msg_data
1274 );
1275
1276
1277 IF (l_write_log) THEN
1278 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Price Calculated: '||l_price_rec.PROD_EXT_AMOUNT);
1279 END IF;
1280
1281 /*FOR BILLING REPORT*/
1282 l_exception_amount := l_price_rec.PROD_EXT_AMOUNT;
1283
1284 IF (l_return_status <> 'S') THEN
1285 oks_bill_rec_pub.get_message
1286 (l_msg_cnt => l_msg_count,
1287 l_msg_data => l_msg_data);
1288
1289 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Calculate Price Error'||' '||l_return_status);
1290
1291 --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
1292 Raise USAGE_EXCEPTION;
1293 END IF;
1294
1295 l_price_rec.PROD_EXT_AMOUNT:=l_price_rec.PROD_EXT_AMOUNT*l_sign;
1296
1297 OKS_BILL_REC_PUB.update_bsl
1298 (
1299 X_RET_STAT => l_return_status,
1300 P_DNZ_CHR_ID => p_dnz_chr_id,
1301 P_BSL_ID => bsl_price_rec.bsl_id,
1302 P_BCL_ID => l_bcl_id,
1303 P_AMOUNT => l_price_rec.PROD_EXT_AMOUNT,
1304 P_CURRENCY_CODE=> p_currency_code,
1305 P_PRV => p_prv
1306 );
1307
1308 IF (l_return_status <> 'S') THEN
1309 oks_bill_rec_pub.get_message
1310 (l_msg_cnt => l_msg_count,
1311 l_msg_data => l_msg_data);
1312
1313 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Error update bsl'||' '||l_return_status);
1314 --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
1315 Raise USAGE_EXCEPTION;
1316 END IF;
1317
1318 l_break_amount := 0;
1319 /* Populate Price Break Record here and Insert Price Breaks
1320 Details
1321 */
1322 --FOR i in l_price_break_details.first..l_price_break_details.last
1323 IF (l_price_break_details.COUNT) > 0 THEN
1324 i := l_price_break_details.FIRST;
1325 LOOP
1326
1327 l_pbr_rec_in.bcl_id := l_bcl_id;
1328 l_pbr_rec_in.bsl_id := bsl_price_rec.bsl_id;
1329 l_pbr_rec_in.cle_id := bsl_price_rec.cle_id;
1330 l_pbr_rec_in.chr_id := bsl_price_rec.dnz_chr_id;
1331 l_pbr_rec_in.unit_price := l_price_break_details(i).unit_price;
1332 l_pbr_rec_in.amount := l_price_break_details(i).amount;
1333
1334
1335 l_pbr_rec_in.amount :=
1336 OKS_EXTWAR_UTIL_PVT.round_currency_amt(l_price_break_details(i).amount, p_currency_code);
1337
1338 l_pbr_rec_in.quantity_from
1339 := l_price_break_details(i).quantity_from;
1340 --:= l_price_break_details(i).pricing_attr_value_from;
1341 l_pbr_rec_in.quantity_to
1342 := l_price_break_details(i).quantity_to;
1343 l_pbr_rec_in.quantity
1344 := l_price_break_details(i).quantity;
1345 l_pbr_rec_in.prorate := nvl(bsl_price_rec.prorate,l_prorate);
1346 --l_pbr_rec_in.lock_flag
1347 -- := l_price_break_details(i).lock_flag;
1348 --l_pbr_rec_in.locked_price_list_id
1349 -- := l_price_break_details(i).locked_price_list_id;
1350 --l_pbr_rec_in.locked_price_list_line_id
1351 -- := l_price_break_details(i).locked_price_list_line_id;
1352 --l_pbr_rec_in.price_list_id
1353 -- := l_price_break_details(i).price_list_id;
1354 --l_pbr_rec_in.price_list_line_id
1355 -- := l_price_break_details(i).price_list_line_id;
1356
1357
1358 l_break_amount := nvl(l_break_amount,0) + nvl(l_pbr_rec_in.amount,0) ;
1359
1360 IF ( nvl(l_pbr_rec_in.quantity,0) > 0) THEN
1361 OKS_PBR_PVT.insert_row(
1362 P_API_VERSION => 1,
1363 P_INIT_MSG_LIST => l_init_msg_list,
1364 X_RETURN_STATUS => l_return_status,
1365 X_MSG_COUNT => l_msg_count,
1366 X_MSG_DATA => l_msg_data,
1367 P_PBRV_REC => l_pbr_rec_in,
1368 X_PBRV_REC => l_pbr_rec_out) ;
1369
1370 END IF;
1371 EXIT WHEN i = l_price_break_details.LAST;
1372 i := l_price_break_details.NEXT(i);
1373 END LOOP;
1374 END IF; -- l_price_break_details.COUNT > 0
1375
1376
1377 IF ( l_break_amount <> abs(l_price_rec.PROD_EXT_AMOUNT)) THEN
1378 OKS_BILL_REC_PUB.prorate_price_breaks (
1379 P_BSL_ID => bsl_price_rec.bsl_id,
1380 P_BREAK_AMOUNT => l_break_amount,
1381 P_TOT_AMOUNT => l_price_rec.PROD_EXT_AMOUNT ,
1382 X_RETURN_STATUS => l_return_status) ;
1383 END IF;
1384
1385
1386 l_sub_id := l_sub_id + 1;
1387 END LOOP; --FOR bsl_price_rec in bsl_price_csr(l_bcl_id)
1388
1389 END IF; -- (l_usage_type <> 'NPR')
1390
1391 END IF;
1392
1393 END IF; -- l_inv_date <= p_date
1394
1395
1396 e_ptr := e_ptr + 1;
1397 l_tsub_id := l_prs_tbl_idx;
1398
1399 END LOOP; --While eptr < no_of_cycles
1400
1401
1402
1403
1404 EXCEPTION
1405 WHEN USAGE_EXCEPTION THEN
1406 /*FOR BILLING REPORT*/
1407 p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines := p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines + l_subline_count;
1408 p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines_Value := p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines_Value + nvl(l_amount,0) + nvl(l_exception_amount,0) ;
1409 /*FOR ERROR REPORT*/
1410 p_billrep_err_tbl_idx := p_billrep_err_tbl_idx + 1;
1411 p_billrep_err_tbl(p_billrep_err_tbl_idx).Top_Line_id := p_top_line_id;
1412 p_billrep_err_tbl(p_billrep_err_tbl_idx).Lse_Id := 12;
1413 p_billrep_err_tbl(p_billrep_err_tbl_idx).Sub_line_id := l_subline_id ;
1414 p_billrep_err_tbl(p_billrep_err_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
1415
1416 p_return_status := 'E';
1417 WHEN OTHERS THEN
1418 /*FOR BILLING REPORT*/
1419 p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines := p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines + l_subline_count;
1420 p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines_Value := p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines_Value + nvl(l_amount,0) + nvl(l_exception_amount,0) ;
1421 /*FOR ERROR REPORT*/
1422 p_billrep_err_tbl_idx := p_billrep_err_tbl_idx + 1;
1423 p_billrep_err_tbl(p_billrep_err_tbl_idx).Top_Line_id := p_top_line_id;
1424 p_billrep_err_tbl(p_billrep_err_tbl_idx).Lse_Id := 12;
1425 p_billrep_err_tbl(p_billrep_err_tbl_idx).Sub_line_id := l_subline_id ;
1426 p_billrep_err_tbl(p_billrep_err_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
1427
1428
1429 p_return_status := 'E';
1430 END Bill_usage_item;
1431
1432 Procedure Bill_Service_Item (
1433 p_dnz_chr_id IN NUMBER,
1434 p_contract_number IN VARCHAR2,
1435 p_con_num_modifier IN VARCHAR2,
1436 p_line_number IN VARCHAR2,
1437 p_lse_id IN NUMBER,
1438 p_inv_org_id IN NUMBER,
1439 p_top_line_id IN NUMBER,
1440 p_top_line_start_date IN DATE,
1441 p_top_line_term_date IN DATE,
1442 p_top_line_end_date IN DATE,
1443 p_currency_code IN VARCHAR2,
1444 p_object1_id1 IN VARCHAR2,
1445 p_object1_id2 IN VARCHAR2,
1446 p_okl_flag IN NUMBER,
1447 p_prv IN NUMBER,
1448 p_date IN DATE,
1449 p_summary_yn IN VARCHAR2,
1450 p_ar_feeder_ctr IN OUT NOCOPY NUMBER,
1451 p_billrep_tbl IN OUT NOCOPY OKS_BILL_REC_PUB.bill_report_tbl_type,
1452 p_billrep_tbl_idx IN NUMBER,
1453 p_billrep_err_tbl IN OUT NOCOPY OKS_BILL_REC_PUB.billrep_error_tbl_type,
1454 p_billrep_err_tbl_idx IN OUT NOCOPY NUMBER,
1455 p_return_status IN OUT NOCOPY VARCHAR2
1456 )
1457 IS
1458
1459
1460 /* -- This cursor gives all the covered lines of service or usage lines */
1461 CURSOR l_subline_csr(p_cle_id Number) Is
1462 SELECT sub_line.id id
1463 ,sub_line.cle_id cle_id
1464 ,sub_line.price_negotiated price_negotiated
1465 ,sub_line.start_date start_date
1466 ,sub_line.end_date end_date
1467 ,sub_line.date_terminated date_terminated
1468 ,sub_line.line_number line_number /* Report */
1469 FROM OKC_K_LINES_B sub_line
1470 WHERE sub_line.cle_id = p_cle_id
1471 AND sub_line.date_cancelled is NULL -- [llc]
1472 AND sub_line.lse_id in (8,7,9,10,11,13,25,35)
1473 AND not exists ( select 1 from okc_k_rel_objs rel
1474 where rel.cle_id = sub_line.id );
1475
1476 Cursor qty_uom_csr(p_cle_id Number) Is
1477 SELECT okc.Number_of_items
1478 ,tl.Unit_of_measure uom_code
1479 FROM okc_k_items okc
1480 ,mtl_units_of_measure_tl tl
1481 WHERE okc.cle_id = p_cle_id
1482 AND tl.uom_code = okc.uom_code
1483 AND tl.language = USERENV('LANG');
1484
1485 /*
1486 Select Number_of_items
1487 ,OKX.Unit_of_measure uom_code
1488 From OKC_K_ITEMS OKC
1489 ,OKX_UNITS_OF_MEASURE_V OKX
1490 Where cle_id = P_cle_id
1491 And Okx.uom_code = OKC.uom_code ;
1492 */
1493
1494 /*FOR BILLING REPORT*/
1495 Cursor subline_count(p_cle_id Number) Is
1496 SELECT count(sub_line.id)
1497 FROM OKC_K_LINES_B sub_line
1498 WHERE sub_line.cle_id = p_cle_id
1499 AND sub_line.date_cancelled is NULL -- [llc]
1500 AND sub_line.lse_id in (8,7,9,10,11,13,25,35)
1501 AND not exists ( select 1 from okc_k_rel_objs rel
1502 where rel.cle_id = sub_line.id );
1503 /*FOR BILLING REPORT*/
1504 Cursor top_line_amount(p_cle_id NUMBER) Is
1505 Select sum(AMOUNT)
1506 FROM oks_level_elements
1507 where CLE_ID= p_cle_id
1508 AND DATE_TO_INTERFACE <= p_date
1509 AND DATE_COMPLETED IS NULL;
1510
1511
1512
1513 l_ptr NUMBER;
1514 e_ptr NUMBER;
1515 no_of_cycles NUMBER;
1516 l_sub_line_total NUMBER;
1517 l_line_total NUMBER;
1518 l_level_elements_count NUMBER;
1519 l_amount NUMBER;
1520 l_bcl_id NUMBER;
1521 l_inv_date DATE;
1522 l_ar_inv_date DATE;
1523
1524 l_summary_yn VARCHAR2(1);
1525 l_subline_count NUMBER := 0;
1526 l_subline_id NUMBER;
1527 l_errep_amount NUMBER;
1528
1529 /* Variable for calling std API*/
1530 l_api_version CONSTANT NUMBER := 1.0;
1531 l_called_from CONSTANT NUMBER := 1;
1532 l_init_msg_list CONSTANT VARCHAR2(1) := 'F';
1533 l_msg_count NUMBER;
1534 l_msg_data VARCHAR2(2000);
1535 l_return_status VARCHAR2(1);
1536 qty_uom_rec QTY_UOM_CSR%rowtype;
1537
1538 service_exception EXCEPTION;
1539 sub_service_exception EXCEPTION;
1540 BEGIN
1541 IF (l_write_log) THEN
1542 FND_FILE.PUT_LINE(FND_FILE.LOG,'***Processing Service/Ext Warranty Item Starts***');
1543 FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Top Line Id: ' || p_top_line_id);
1544 FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Top Line Start Date: ' || p_top_line_start_date);
1545 FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Top Line Termination Date/End date: ' || nvl(p_top_line_term_date,p_top_line_end_date));
1546 END IF;
1547
1548 p_return_status := 'S';
1549 l_level_elements_count := 0;
1550 l_line_total := 0;
1551
1552 --Start mchoudha Bug#3537100 17-APR-04
1553 --For Billing Report
1554 OPEN subline_count(p_top_line_id);
1555 FETCH subline_count into l_subline_count;
1556 CLOSE subline_count;
1557
1558 IF (p_summary_yn = 'Y') THEN
1559 l_summary_yn := 'Y';
1560 ELSE
1561 IF ( FND_PROFILE.VALUE('OKS_AR_TRANSACTIONS_SUBMIT_SUMMARY_YN') = 'YES') THEN
1562 l_summary_yn := 'Y';
1563 ELSE
1564 l_summary_yn := 'N';
1565 END IF;
1566 END IF;
1567
1568 --End mchoudha Bug#3537100
1569 IF (l_write_log) THEN
1570 FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Going to process all sublines for top line '||p_top_line_id);
1571 END IF;
1572
1573 FOR l_covlvl_rec in l_subline_csr(p_top_line_id )
1574 LOOP
1575 BEGIN
1576
1577 /*FOR BILLING REPORT*/
1578 l_subline_id := l_covlvl_rec.id;
1579 IF (l_write_log) THEN
1580 FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => inside FOR l_covlvl_rec Processing Coverage Id: '||l_covlvl_rec.id);
1581 END IF;
1582
1583 level_elements_tab.delete;
1584
1585 OKS_BILL_UTIL_PUB.get_next_level_element(
1586 P_API_VERSION => l_api_version,
1587 P_ID => l_covlvl_rec.id,
1588 P_COVD_FLAG => 'Y', ---- flag to indicate covered level
1589 P_DATE => p_date,
1590 P_INIT_MSG_LIST => l_init_msg_list,
1591 X_RETURN_STATUS => l_return_status,
1592 X_MSG_COUNT => l_msg_count,
1593 X_MSG_DATA => l_msg_data,
1594 X_NEXT_LEVEL_ELEMENT => level_elements_tab );
1595
1596 IF (l_write_log) THEN
1597 FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => After calling OKS_BILL_UTIL_PUB.get_next_level_element l_return_status '||l_return_status);
1598 END IF;
1599
1600 IF (l_return_status <> 'S') THEN
1601 oks_bill_rec_pub.get_message
1602 (l_msg_cnt => l_msg_count,
1603 l_msg_data => l_msg_data);
1604
1605 l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
1606 l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
1607 l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'N' ;
1608 l_processed_sub_lines_tbl(l_prs_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
1609
1610 FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Failed in Creation of get_next_level_element For coverage Id: '||l_covlvl_rec.id );
1611 FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Rolling Back the Whole Service With Top Line ID: '||p_top_line_id );
1612
1613 --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
1614
1615 RAISE sub_service_exception;
1616 END IF;
1617
1618 IF (level_elements_tab.count < 1) THEN
1619 l_processed_sub_lines_tbl.DELETE(l_prs_tbl_idx) ;
1620 END IF;
1621
1622 e_ptr := 1;
1623 no_of_cycles := level_elements_tab.count;
1624
1625 l_sub_line_total := 0;
1626
1627 l_level_elements_count := l_level_elements_count + level_elements_tab.count;
1628
1629 IF (l_write_log) THEN
1630 FND_FILE.PUT_LINE(FND_FILE.LOG,'No of Period for coverage: ' || no_of_cycles);
1631 END IF;
1632
1633 OPEN qty_uom_csr(l_covlvl_rec.id);
1634 FETCH qty_uom_csr into qty_uom_rec;
1635 CLOSE qty_uom_csr;
1636
1637 IF (l_write_log) THEN
1638 FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Going inside WHILE (e_ptr <= no_of_cycles) ');
1639 END IF;
1640
1641 WHILE (e_ptr <= no_of_cycles)
1642 LOOP
1643
1644 l_cov_tbl.delete;
1645 l_sub_line_total :=nvl(l_sub_line_total,0)
1646 + level_elements_tab(e_ptr).bill_amount ;
1647 l_line_total := nvl(l_line_total,0) +
1648 + level_elements_tab(e_ptr).bill_amount ;
1649 l_inv_date := level_elements_tab(e_ptr).date_to_interface;
1650 l_ar_inv_date := level_elements_tab(e_ptr).date_transaction;
1651 l_amount := level_elements_tab(e_ptr).bill_amount;
1652 l_calc_rec.l_calc_sdate := level_elements_tab(e_ptr).bill_from_date ;
1653 l_calc_rec.l_calc_edate := level_elements_tab(e_ptr).bill_to_date;
1654
1655 IF ( l_covlvl_rec.date_terminated is not null) AND
1656 ( trunc(l_calc_rec.l_calc_edate) >=
1657 trunc(l_covlvl_rec.date_terminated)) THEN
1658 l_calc_rec.l_calc_edate := l_covlvl_rec.date_terminated - 1;
1659 END IF;
1660
1661 IF (l_write_log) THEN
1662 FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Coverage Interface Date: '||l_inv_date);
1663 FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Coverage Amount : ' || l_amount);
1664 FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Coverage Start Date : '||l_calc_rec.l_calc_sdate);
1665 FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => Coverage End Date : '||l_calc_rec.l_calc_edate);
1666 END IF;
1667
1668 IF (trunc(l_inv_date) <= trunc(p_date)) THEN
1669
1670 IF ((l_ar_inv_date is not null) And
1671 (l_ar_inv_date < sysdate)) THEN
1672 l_ar_inv_date := sysdate;
1673 END IF;
1674
1675 IF (l_write_log) THEN
1676 FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => l_ar_inv_date '||l_ar_inv_date);
1677 END IF;
1678
1679 l_ptr := 1;
1680
1681 /*******
1682 create bank account is no longer needed as part of R12 Bank account
1683 consolidation project
1684
1685 OKS_BILL_REC_PUB.create_bank_Account(
1686 P_DNZ_CHR_ID => p_dnz_chr_id,
1687 P_BILL_START_DATE => p_top_line_start_date,
1688 P_CURRENCY_CODE => p_currency_code,
1689 X_STATUS => l_return_status,
1690 L_MSG_COUNT => l_msg_count,
1691 L_MSG_DATA => l_msg_data
1692 );
1693
1694 IF (l_write_log) THEN
1695 FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => After calling OKS_BILL_REC_PUB.create_bank_Account l_return_status '||l_return_status);
1696 END IF;
1697
1698 IF (l_return_status <> 'S') THEN
1699 oks_bill_rec_pub.get_message
1700 (l_msg_cnt => l_msg_count,
1701 l_msg_data => l_msg_data);
1702
1703 l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
1704 l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
1705 l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'N' ;
1706 l_processed_sub_lines_tbl(l_prs_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
1707
1708 FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Failed in Creation of bank account For coverage Id: '||l_covlvl_rec.id );
1709 FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Rolling Back the Whole Service With Top Line ID: '||p_top_line_id );
1710
1711 --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
1712
1713 RAISE sub_service_exception;
1714 END IF;
1715 *****/
1716
1717 OKS_BILL_REC_PUB.Insert_bcl
1718 (
1719 P_CALLEDFROM => l_called_from,
1720 X_RETURN_STAT => l_return_status,
1721 P_CLE_ID => p_top_line_id,
1722 P_DATE_BILLED_FROM => l_calc_rec.l_calc_sdate,
1723 P_DATE_BILLED_TO => l_calc_rec.l_calc_edate,
1724 P_DATE_NEXT_INVOICE => l_ar_inv_date,
1725 P_BILL_ACTION => 'RI',
1726 P_OKL_FLAG => p_okl_flag,
1727 P_PRV => p_prv,
1728 P_MSG_COUNT => l_msg_count,
1729 P_MSG_DATA => l_msg_data,
1730 X_BCL_ID => l_bcl_id
1731 );
1732
1733 IF (l_write_log) THEN
1734 FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => After calling OKS_BILL_REC_PUB.Insert_bcl l_return_status '||l_return_status);
1735 END IF;
1736
1737 IF (l_return_status <> 'S') THEN
1738 oks_bill_rec_pub.get_message(
1739 l_msg_cnt => l_msg_count,
1740 l_msg_data => l_msg_data);
1741 l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
1742 l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
1743 l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'N' ;
1744 l_processed_sub_lines_tbl(l_prs_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
1745
1746 FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Failed in Insert BCL For coverage Id: ' ||l_covlvl_rec.id );
1747 FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Rolling Back the Whole Service With Top Line ID: '||p_top_line_id );
1748
1749 RAISE sub_service_exception;
1750 END IF;
1751
1752 IF (p_prv <> 2) THEN
1753
1754 UPDATE oks_level_elements
1755 SET date_Completed = l_calc_rec.l_calc_edate
1756 WHERE id = level_elements_tab(e_ptr).id;
1757
1758 END IF;
1759
1760 l_cov_tbl(l_ptr).id := l_covlvl_rec.id;
1761 l_cov_tbl(l_ptr).bcl_id := l_bcl_id;
1762 l_cov_tbl(l_ptr).date_billed_from := l_calc_rec.l_calc_sdate;
1763 l_cov_tbl(l_ptr).date_billed_to := l_calc_rec.l_calc_edate;
1764 l_cov_tbl(l_ptr).average := 0;
1765 l_cov_tbl(l_ptr).unit_of_measure := qty_uom_rec.uom_code;
1766 l_cov_tbl(l_ptr).fixed := 0 ;
1767 l_cov_tbl(l_ptr).actual := null;
1768 l_cov_tbl(l_ptr).default_default := 0;
1769 l_cov_tbl(l_ptr).amcv_yn := 'N';
1770 l_cov_tbl(l_ptr).adjustment_level := 0 ;
1771 l_cov_tbl(l_ptr).result := qty_uom_rec.number_of_items ;
1772 l_cov_tbl(l_ptr).x_stat := null;
1773 l_cov_tbl(l_ptr).amount := l_amount;
1774 l_cov_tbl(l_ptr).bcl_amount := nvl(l_calc_rec.l_bcl_amount,0);
1775 l_cov_tbl(l_ptr).date_to_interface := sysdate;
1776
1777 END IF; -- (trunc(l_inv_date) <= trunc(p_date))
1778
1779 IF ( l_write_log) THEN
1780 FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => l_cov_tbl.count '||l_cov_tbl.count);
1781 END IF;
1782
1783 IF (l_cov_tbl.count > 0) THEN
1784 p_ar_feeder_ctr := 1;
1785 OKS_BILL_REC_PUB.Insert_all_subline
1786 (
1787 P_CALLEDFROM => l_called_from,
1788 X_RETURN_STAT => l_return_status,
1789 P_COVERED_TBL => l_cov_tbl,
1790 P_CURRENCY_CODE => p_currency_code,
1791 P_DNZ_CHR_ID => p_dnz_chr_id,
1792 P_PRV => p_prv,
1793 P_MSG_COUNT => l_msg_count,
1794 P_MSG_DATA => l_msg_data
1795 );
1796
1797 END IF;
1798
1799 IF (l_write_log) THEN
1800 FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => After calling OKS_BILL_REC_PUB.Insert_all_subline l_return_status '||l_return_status);
1801 END IF;
1802
1803 IF (l_return_status <> 'S') THEN
1804 oks_bill_rec_pub.get_message(
1805 l_msg_cnt => l_msg_count,
1806 l_msg_data => l_msg_data);
1807 l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
1808 l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
1809 l_processed_sub_lines_tbl(l_prs_tbl_idx).Billed_YN := 'N' ;
1810 l_processed_sub_lines_tbl(l_prs_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
1811
1812 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Bill_Service_Item => Insert into sublines table failed Contract line id :'||p_top_line_id);
1813 FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Rolling Back the Whole Service With Top Line ID: '||p_top_line_id );
1814 --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
1815 RAISE sub_service_exception;
1816 END IF;
1817
1818 /* Following code is to update the date_completed field of top line */
1819
1820 IF (p_prv <> 2) THEN
1821 UPDATE oks_level_elements
1822 SET date_completed = l_calc_rec.l_calc_edate
1823 WHERE cle_id = p_top_line_id
1824 AND date_completed is null
1825 AND date_start <= l_calc_rec.l_calc_sdate;
1826 END IF;
1827
1828 IF (l_write_log) THEN
1829 FND_FILE.PUT_LINE(FND_FILE.LOG,'Bill_Service_Item => After updating oks_level_elements ');
1830 END IF;
1831
1832 e_ptr := e_ptr + 1;
1833
1834 END LOOP; -- While eptr loop
1835
1836 l_processed_sub_lines_tbl(l_prs_tbl_idx).Bill_Amount := l_sub_line_total;
1837
1838
1839
1840
1841 EXCEPTION
1842 WHEN sub_service_exception THEN
1843 p_return_status := 'E';
1844
1845 /* FOR BILLING REPORT */
1846 Open top_line_amount(p_top_line_id);
1847 Fetch top_line_amount into l_errep_amount;
1848 Close top_line_amount;
1849 IF (l_summary_yn = 'N') THEN
1850 p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines := p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines + l_subline_count;
1851 p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines_Value := p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines_Value
1852 + nvl(l_errep_amount,0);
1853
1854 /*FOR ERROR REPORT*/
1855 p_billrep_err_tbl_idx := p_billrep_err_tbl_idx + 1;
1856 p_billrep_err_tbl(p_billrep_err_tbl_idx).Top_Line_id := p_top_line_id;
1857 p_billrep_err_tbl(p_billrep_err_tbl_idx).Lse_Id := 1;
1858 p_billrep_err_tbl(p_billrep_err_tbl_idx).Sub_line_id :=l_subline_id ;
1859 p_billrep_err_tbl(p_billrep_err_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
1860
1861
1862 ELSE
1863 /*FOR ERROR REPORT*/
1864 p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines := p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines + 1;
1865 p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value := p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value
1866 + nvl(l_errep_amount,0);
1867
1868 p_billrep_err_tbl_idx := p_billrep_err_tbl_idx + 1;
1869 p_billrep_err_tbl(p_billrep_err_tbl_idx).Top_Line_id := p_top_line_id;
1870 p_billrep_err_tbl(p_billrep_err_tbl_idx).Lse_Id := 1;
1871 p_billrep_err_tbl(p_billrep_err_tbl_idx).Sub_line_id :=NULL;
1872 p_billrep_err_tbl(p_billrep_err_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
1873
1874 END IF;
1875
1876 FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Failed- sub_service_exception exception raised for coverage: '||l_covlvl_rec.id||' With Error: '||sqlerrm );
1877 EXIT;
1878 WHEN OTHERS THEN
1879 --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
1880 /* FOR BILLING REPORT */
1881 Open top_line_amount(p_top_line_id);
1882 Fetch top_line_amount into l_errep_amount;
1883 Close top_line_amount;
1884 IF (l_summary_yn = 'N') THEN
1885 p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines := p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines + l_subline_count;
1886 p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines_Value := p_billrep_tbl(p_billrep_tbl_idx).Rejected_SubLines_Value + nvl(l_errep_amount,0);
1887
1888 /*FOR ERROR REPORT*/
1889 p_billrep_err_tbl_idx := p_billrep_err_tbl_idx + 1;
1890 p_billrep_err_tbl(p_billrep_err_tbl_idx).Top_Line_id := p_top_line_id;
1891 p_billrep_err_tbl(p_billrep_err_tbl_idx).Lse_Id := 1;
1892 p_billrep_err_tbl(p_billrep_err_tbl_idx).Sub_line_id :=l_subline_id ;
1893 p_billrep_err_tbl(p_billrep_err_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
1894
1895
1896 ELSE
1897 /*FOR ERROR REPORT*/
1898 p_billrep_err_tbl_idx := p_billrep_err_tbl_idx + 1;
1899 p_billrep_err_tbl(p_billrep_err_tbl_idx).Top_Line_id := p_top_line_id;
1900 p_billrep_err_tbl(p_billrep_err_tbl_idx).Lse_Id := 1;
1901 p_billrep_err_tbl(p_billrep_err_tbl_idx).Sub_line_id :=NULL;
1902 p_billrep_err_tbl(p_billrep_err_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
1903
1904 END IF;
1905 p_return_status := 'E';
1906 FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Failed- when others exception raised for coverage '||l_covlvl_rec.id||' With Error: '||sqlerrm );
1907 EXIT;
1908 END;
1909 END LOOP; -- FOR loop for covered level
1910
1911 --l_processed_lines_tbl(l_pr_tbl_idx).Bill_Amount := l_line_total ;
1912
1913 /* FOR BILLING REPORT */
1914 -- IF (l_summary_yn = 'Y' AND p_return_status <> 'E') THEN
1915 -- p_billrep_tbl(p_billrep_tbl_idx).Successful_Lines := p_billrep_tbl(p_billrep_tbl_idx).Successful_Lines + 1;
1916 -- p_billrep_tbl(p_billrep_tbl_idx).Successful_Lines_Value := p_billrep_tbl(p_billrep_tbl_idx).Successful_Lines_Value + nvl(l_line_total,0);
1917 -- END IF;
1918
1919 IF (l_summary_yn = 'Y' AND p_return_status = 'E') THEN
1920 p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines := p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines + 1;
1921 p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value := p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value + nvl(l_line_total,0);
1922 END IF;
1923
1924
1925
1926 /* ** Delete the record from billing report table if no subline is billed***/
1927
1928 IF (l_level_elements_count <= 0) THEN
1929 l_processed_lines_tbl.DELETE(l_pr_tbl_idx) ;
1930 END IF;
1931
1932 IF (l_write_log) THEN
1933 FND_FILE.PUT_LINE(FND_FILE.LOG,'***Processing Service/Ext Warranty Item Ends ***');
1934 END IF;
1935
1936
1937 EXCEPTION
1938 WHEN SERVICE_EXCEPTION THEN
1939 p_return_status := 'E';
1940 /* FOR BILLING REPORT */
1941 IF (l_summary_yn = 'Y') THEN
1942 p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines := p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines + 1;
1943 p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value := p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value + nvl(l_line_total,0);
1944 END IF;
1945 FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Failed- when SERVICE_EXCEPTION exception raised '||sqlerrm );
1946 WHEN OTHERS THEN
1947 p_return_status := 'E';
1948 /* FOR BILLING REPORT */
1949 IF (l_summary_yn = 'Y') THEN
1950 p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines := p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines + 1;
1951 p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value := p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value + nvl(l_line_total,0);
1952 END IF;
1953 FND_FILE.PUT_LINE( FND_FILE.LOG,'Bill_Service_Item => Failed-outside when OTHERS exception raised '||sqlerrm );
1954 END Bill_Service_Item;
1955
1956
1957 Procedure Bill_Subscription_item(
1958 p_dnz_chr_id IN NUMBER,
1959 p_top_line_id IN NUMBER,
1960 p_top_line_start_date IN DATE,
1961 p_top_line_term_date IN DATE,
1962 p_top_line_end_date IN DATE,
1963 p_currency_code IN VARCHAR2,
1964 p_okl_flag IN NUMBER,
1965 p_nos_of_items IN NUMBER,
1966 p_uom_code IN VARCHAR2,
1967 p_prv IN NUMBER,
1968 p_date IN DATE,
1969 p_billrep_tbl IN OUT NOCOPY OKS_BILL_REC_PUB.bill_report_tbl_type,
1970 p_billrep_tbl_idx IN NUMBER,
1971 p_billrep_err_tbl IN OUT NOCOPY OKS_BILL_REC_PUB.billrep_error_tbl_type,
1972 p_billrep_err_tbl_idx IN OUT NOCOPY NUMBER,
1973 p_ar_feeder_ctr IN OUT NOCOPY NUMBER,
1974 p_return_status IN OUT NOCOPY VARCHAR2)
1975 IS
1976 l_ptr NUMBER := 0;
1977 e_ptr NUMBER;
1978 no_of_elements NUMBER;
1979 l_amount NUMBER;
1980 l_bcl_id NUMBER;
1981 l_line_total NUMBER;
1982 l_inv_date DATE;
1983 l_ar_inv_date DATE;
1984 l_bill_start_date DATE;
1985 l_bill_end_date DATE;
1986
1987
1988 /* Variable for calling std API*/
1989 l_api_version CONSTANT NUMBER := 1.0;
1990 l_called_from CONSTANT NUMBER := 1;
1991 l_init_msg_list CONSTANT VARCHAR2(1) := 'F';
1992 l_msg_count NUMBER;
1993 l_msg_data VARCHAR2(2000);
1994 l_return_status VARCHAR2(1);
1995
1996 /*Exception Definition */
1997 SUBSRIPTION_EXCEPTION EXCEPTION;
1998
1999 BEGIN
2000 p_return_status := 'S';
2001 level_elements_tab.delete;
2002
2003 IF (l_write_log) THEN
2004 FND_FILE.PUT_LINE(FND_FILE.LOG,'***Processing Subscription Item ***');
2005 FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Id: ' || p_top_line_id);
2006 FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Start Date: ' || p_top_line_start_date);
2007 FND_FILE.PUT_LINE(FND_FILE.LOG,' Top Line Termination Date/End date: ' || nvl(p_top_line_term_date,p_top_line_end_date));
2008 END IF;
2009
2010 oks_bill_util_pub.get_next_level_element(
2011 p_api_version => l_api_version,
2012 p_id => p_top_line_id,
2013 p_covd_flag => 'N', ---- flag to indicate Top line
2014 p_date => p_date,
2015 p_init_msg_list => l_init_msg_list,
2016 x_return_status => l_return_status,
2017 x_msg_count => l_msg_count,
2018 x_msg_data => l_msg_data,
2019 x_next_level_element => level_elements_tab );
2020
2021 IF (l_return_status <> 'S') Then
2022 oks_bill_rec_pub.get_message(
2023 l_msg_cnt => l_msg_count,
2024 l_msg_data => l_msg_data);
2025 l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
2026 l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '||
2027 sqlerrm||'. Error Message: '||l_msg_data ;
2028
2029 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Failed in getting next level ');
2030
2031 Raise SUBSRIPTION_EXCEPTION;
2032 END IF;
2033
2034 e_ptr := 1;
2035 no_of_elements := level_elements_tab.count;
2036 l_line_total := 0;
2037
2038 WHILE (e_ptr <= no_of_elements)
2039 LOOP
2040 l_inv_date := level_elements_tab(e_ptr).date_to_interface;
2041 l_ar_inv_date := level_elements_tab(e_ptr).date_transaction;
2042 l_amount := level_elements_tab(e_ptr).bill_amount;
2043 l_bill_start_date := level_elements_tab(e_ptr).bill_from_date;
2044 l_bill_end_date := level_elements_tab(e_ptr).bill_to_date;
2045 l_line_total := l_line_total+level_elements_tab(e_ptr).bill_amount;
2046
2047 l_ptr := 1;
2048 l_cov_tbl.delete;
2049
2050
2051 IF (trunc(l_inv_date) <= trunc(p_date)) THEN
2052 IF ((l_ar_inv_date is not null) AND
2053 (l_ar_inv_date < sysdate)) THEN
2054 l_ar_inv_date := sysdate;
2055 END IF;
2056
2057 /*******
2058 create bank account is no longer needed as part of R12 Bank account
2059 consolidation project
2060
2061 OKS_BILL_REC_PUB.create_bank_Account(
2062 P_DNZ_CHR_ID => p_dnz_chr_id,
2063 P_BILL_START_DATE => p_top_line_start_date,
2064 P_CURRENCY_CODE => p_currency_code,
2065 X_STATUS => l_return_status,
2066 L_MSG_COUNT => l_msg_count,
2067 L_MSG_DATA => l_msg_data
2068 );
2069
2070 IF (l_return_status <> 'S') THEN
2071 oks_bill_rec_pub.get_message(
2072 l_msg_cnt => l_msg_count,
2073 l_msg_data => l_msg_data);
2074 l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
2075 l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
2076
2077 --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
2078 FND_FILE.PUT_LINE( FND_FILE.LOG,' Failed in Creation of bank account');
2079 FND_FILE.PUT_LINE( FND_FILE.LOG,' Rolling Back the Whole Service '||p_top_line_id );
2080
2081 RAISE SUBSRIPTION_EXCEPTION;
2082 END IF;
2083 ***/
2084
2085 /*This procedure insert the into bill_con_lines,
2086 if the record is not already present for the same period.
2087 Since the out table returns the row_id of the inserted row, get_bcl_id
2088 which was present in earliar version is now removed*/
2089 OKS_BILL_REC_PUB.Insert_bcl
2090 (
2091 P_CALLEDFROM => l_called_from,
2092 X_RETURN_STAT => l_return_status,
2093 P_CLE_ID => p_top_line_id,
2094 P_DATE_BILLED_FROM => l_bill_start_date,
2095 P_DATE_BILLED_TO => l_bill_end_date,
2096 P_DATE_NEXT_INVOICE => l_ar_inv_date,
2097 P_BILL_ACTION => 'RI',
2098 P_OKL_FLAG => p_okl_flag,
2099 P_PRV => p_prv,
2100 P_MSG_COUNT => l_msg_count,
2101 P_MSG_DATA => l_msg_data,
2102 X_BCL_ID => l_bcl_id
2103 );
2104
2105 IF (l_return_status <> 'S') THEN
2106 oks_bill_rec_pub.get_message(
2107 l_msg_cnt => l_msg_count,
2108 l_msg_data => l_msg_data);
2109 l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
2110 l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
2111
2112 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Failed in insert bcl ');
2113 Raise SUBSRIPTION_EXCEPTION;
2114 END IF;
2115
2116 IF (p_prv <> 2) THEN
2117
2118 UPDATE oks_level_elements
2119 SET date_completed = l_bill_end_date
2120 WHERE id = level_elements_tab(e_ptr).id;
2121
2122 END IF;
2123
2124 l_cov_tbl(l_ptr).flag := Null;
2125 l_cov_tbl(l_ptr).id := p_top_line_id ;
2126 l_cov_tbl(l_ptr).bcl_id := l_bcl_id;
2127 l_cov_tbl(l_ptr).date_billed_from := l_bill_start_date;
2128 l_cov_tbl(l_ptr).date_billed_to := l_bill_end_date;
2129 l_cov_tbl(l_ptr).amount := l_amount;
2130 l_cov_tbl(l_ptr).average := 0;
2131 l_cov_tbl(l_ptr).unit_of_measure := p_uom_code;
2132 l_cov_tbl(l_ptr).fixed := 0;
2133 l_cov_tbl(l_ptr).actual := null;
2134 l_cov_tbl(l_ptr).default_default := 0;
2135 l_cov_tbl(l_ptr).amcv_yn := 'N';
2136 l_cov_tbl(l_ptr).adjustment_level := 0 ;
2137 l_cov_tbl(l_ptr).adjustment_minimum := 0 ;
2138 l_cov_tbl(l_ptr).result := 1 ;
2139 l_cov_tbl(l_ptr).x_stat := Null ;
2140 l_cov_tbl(l_ptr).bcl_amount := 0;--nvl(l_calc_rec.l_bcl_amount,0);
2141 l_cov_tbl(l_ptr).date_to_interface := sysdate;
2142
2143 IF (l_cov_tbl.count > 0) THEN
2144 /* check if this is to be passed back to main api */
2145 p_ar_feeder_ctr := 1;
2146 OKS_BILL_REC_PUB.Insert_all_subline
2147 (
2148 P_CALLEDFROM => l_called_from,
2149 X_RETURN_STAT => l_return_status,
2150 P_COVERED_TBL => l_cov_tbl,
2151 P_CURRENCY_CODE => p_currency_code,
2152 P_DNZ_CHR_ID => p_dnz_chr_id,
2153 P_PRV => p_prv,
2154 P_MSG_COUNT => l_msg_count,
2155 P_MSG_DATA => l_msg_data
2156 );
2157
2158 IF (l_write_log) THEN
2159 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Status after insert into sublines '||l_return_status );
2160 END IF;
2161
2162 IF (l_return_status <> 'S') THEN
2163 oks_bill_rec_pub.get_message(
2164 l_msg_cnt => l_msg_count,
2165 l_msg_data => l_msg_data);
2166 l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N ' ;
2167 l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
2168 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Insert into sublines table failed Contract line id : '||p_top_line_id);
2169 --DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION ');
2170 Raise SUBSRIPTION_EXCEPTION;
2171 END IF;
2172
2173 END IF; -- l_cov_tbl.count > 0
2174 END IF; -- l_inv_date <= p_date
2175 e_ptr := e_ptr + 1 ;
2176
2177 END LOOP;
2178
2179
2180
2181 EXCEPTION
2182 WHEN SUBSRIPTION_EXCEPTION THEN
2183 /*FOR BILLING REPORT*/
2184 p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines := p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines + 1;
2185 p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value := p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value + nvl(l_line_total,0);
2186 /*FOR ERROR REPORT*/
2187 p_billrep_err_tbl_idx := p_billrep_err_tbl_idx + 1;
2188 p_billrep_err_tbl(p_billrep_err_tbl_idx).Top_Line_id := p_top_line_id;
2189 p_billrep_err_tbl(p_billrep_err_tbl_idx).Lse_Id := 46;
2190 p_billrep_err_tbl(p_billrep_err_tbl_idx).Sub_line_id := NULL;
2191 p_billrep_err_tbl(p_billrep_err_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
2192 p_return_status := 'E';
2193 WHEN OTHERS THEN
2194 /*FOR BILLING REPORT*/
2195 p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines := p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines + 1;
2196 p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value := p_billrep_tbl(p_billrep_tbl_idx).Rejected_Lines_Value + nvl(l_line_total,0);
2197 /*FOR ERROR REPORT*/
2198 p_billrep_err_tbl_idx := p_billrep_err_tbl_idx + 1;
2199 p_billrep_err_tbl(p_billrep_err_tbl_idx).Top_Line_id := p_top_line_id;
2200 p_billrep_err_tbl(p_billrep_err_tbl_idx).Lse_Id := 46;
2201 p_billrep_err_tbl(p_billrep_err_tbl_idx).Sub_line_id := NULL;
2202 p_billrep_err_tbl(p_billrep_err_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
2203 p_return_status := 'E';
2204 END Bill_Subscription_item;
2205
2206
2207
2208 procedure update_version (
2209 p_dnz_chr_id IN NUMBER
2210 ) IS pragma autonomous_transaction;
2211
2212 l_con_update_date date;
2213 l_chrv_rec OKC_CONTRACT_PUB.chrv_rec_type;
2214 l_chrv_out_rec OKC_CONTRACT_PUB.chrv_rec_type;
2215 l_cvmv_rec OKC_CVM_PVT.cvmv_rec_type ;
2216 l_cvmv_out_rec OKC_CVM_PVT.cvmv_rec_type ;
2217 l_return_status VARCHAR2(1);
2218 l_msg_data VARCHAR2(2000);
2219 l_msg_cnt NUMBER;
2220 l_api_version CONSTANT NUMBER := 1.0;
2221 l_init_msg_list CONSTANT VARCHAR2(1) := 'F';
2222
2223 Cursor l_contract_update_date(p_chr_id IN NUMBER) is
2224 SELECT last_update_date from okc_k_headers_b
2225 WHERE id = p_chr_id;
2226
2227
2228 BEGIN
2229
2230 OPEN l_contract_update_date(p_dnz_chr_id);
2231 FETCH l_contract_update_date into l_con_update_date;
2232 CLOSE l_contract_update_date;
2233
2234 IF (trunc(l_con_update_date) <> trunc(sysdate)) THEN
2235 okc_cvm_pvt.g_trans_id := 'XXX';
2236 l_cvmv_rec.chr_id := p_dnz_chr_id;
2237
2238 OKC_CVM_PVT.update_contract_version(
2239 P_API_VERSION => l_api_version,
2240 P_INIT_MSG_LIST => l_init_msg_list,
2241 X_RETURN_STATUS => l_return_status,
2242 X_MSG_COUNT => l_msg_cnt,
2243 X_MSG_DATA => l_msg_data,
2244 P_CVMV_REC => l_cvmv_rec,
2245 X_CVMV_REC => l_cvmv_out_rec);
2246
2247 IF l_write_log THEN
2248 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => After calling OKC_CVM_PVT.update_contract_version l_return_status '||l_return_status);
2249 END IF;
2250
2251 l_chrv_rec.id := p_dnz_chr_id;
2252 l_chrv_rec.last_update_date := sysdate;
2253
2254 OKC_CONTRACT_PUB.update_contract_header(
2255 P_API_VERSION => l_api_version,
2256 X_RETURN_STATUS => l_return_status,
2257 P_INIT_MSG_LIST => OKC_API.G_TRUE,
2258 X_MSG_COUNT => l_msg_cnt,
2259 X_MSG_DATA => l_msg_data,
2260 P_RESTRICTED_UPDATE => OKC_API.G_TRUE,
2261 P_CHRV_REC => l_chrv_rec,
2262 X_CHRV_REC => l_chrv_out_rec);
2263
2264 IF l_write_log THEN
2265 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => After calling OKC_CONTRACT_PUB.update_contract_header l_return_status '||l_return_status);
2266 END IF;
2267
2268 COMMIT;
2269
2270 END IF;
2271
2272 EXCEPTION
2273 WHEN OTHERS THEN
2274 FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Failed- updating version'||sqlerrm );
2275
2276 End update_version;
2277
2278
2279
2280 Procedure Calculate_bill
2281 (
2282 ERRBUF OUT NOCOPY VARCHAR2
2283 ,RETCODE OUT NOCOPY NUMBER
2284 ,P_calledfrom IN NUMBER
2285 ,P_flag IN NUMBER
2286 ,P_date IN DATE
2287 ,P_process_from IN NUMBER
2288 ,P_process_to IN NUMBER
2289 ,P_Prv IN NUMBER
2290 )
2291
2292
2293 Is
2294 Cursor billing_process (p_line_from IN NUMBER,
2295 p_line_to IN NUMBER) IS
2296 SELECT chr_id,cle_id,line_no
2297 FROM oks_process_billing
2298 WHERE line_no between p_line_from and p_line_to;
2299
2300 -------------------------------------------------------------------------
2301 -- Begin partial period computation logic
2302 -- Developer Mani Choudhary
2303 -- Date 11-JUN-2005
2304 -- Added period_start,period_type and price_uom in the select clause
2305 -- This is done to avoid calling oks_renew_util_pub.get_period_defaults
2306 -- so avoind hitting the database once more
2307 -------------------------------------------------------------------------
2308 Cursor l_bill_line_csr(p_hdr_id IN NUMBER,
2309 p_line_id IN NUMBER) IS
2310 SELECT line.id
2311 ,Hdr.Contract_number
2312 ,Hdr.Contract_number_modifier
2313 ,Hdr.Currency_code
2314 ,Hdr.Inv_organization_id
2315 ,Hdr.authoring_org_id
2316 ,Hdr.org_id
2317 ,line.dnz_chr_id
2318 ,line.cle_id
2319 ,line.lse_id
2320 ,line.start_date
2321 ,line.end_date
2322 ,line.price_negotiated
2323 ,line.date_terminated
2324 ,okp.object1_id1
2325 ,okp.object1_id2
2326 ,line.line_number
2327 ,rul.ar_interface_yn
2328 ,rul.period_start
2329 ,rul.period_type
2330 ,rul.price_uom
2331 ,nvl(rul.summary_trx_yn,'N') summary_yn
2332 ,rline.settlement_interval
2333 FROM
2334 OKC_K_PARTY_ROLES_B okp
2335 ,OKC_K_LINES_B line
2336 ,OKS_K_LINES_B rline
2337 ,OKC_K_HEADERS_B Hdr
2338 ,OKS_K_HEADERS_B rul
2339 WHERE Hdr.id = p_hdr_id
2340 AND line.id = p_line_id
2341 AND rline.cle_id = line.id
2342 AND rul.chr_id = Hdr.id
2343 AND line.dnz_chr_id = Hdr.id
2344 AND line.lse_id in (1,12,19,46)
2345 AND okp.dnz_chr_id = hdr.id
2346 AND okp.rle_code in ( 'CUSTOMER','SUBSCRIBER');
2347
2348
2349
2350 Cursor l_inv_item_csr(p_cle_id Number,p_org_id Number) Is
2351 SELECT item.Object1_id1
2352 ,mtl.usage_item_flag
2353 ,mtl.service_item_flag
2354 ,mtl.primary_uom_code
2355 FROM Okc_K_items Item
2356 ,mtl_system_items_b mtl --Okx_system_items_v mtl
2357 WHERE item.cle_id = p_cle_id
2358 --AND mtl.id1 = item.object1_id1
2359 AND mtl.inventory_item_id = item.object1_id1
2360 AND mtl.organization_id = p_org_id;
2361
2362 Cursor qty_uom_csr(p_cle_id Number) Is
2363 SELECT okc.Number_of_items
2364 ,tl.Unit_of_measure uom_code
2365 FROM okc_k_items okc
2366 ,mtl_units_of_measure_tl tl
2367 WHERE okc.cle_id = p_cle_id
2368 AND tl.uom_code = okc.uom_code
2369 AND tl.language = USERENV('LANG');
2370
2371 /*
2372 SELECT Number_of_items
2373 ,OKX.Unit_of_measure uom_code
2374 FROM OKC_K_ITEMS OKC
2375 ,OKX_UNITS_OF_MEASURE_V OKX
2376 WHERE cle_id = P_cle_id
2377 AND Okx.uom_code = OKC.uom_code ;
2378 */
2379
2380 Cursor l_uom_csr Is
2381 SELECT uom_code
2382 FROM Okc_time_code_units_v
2383 WHERE tce_code = 'DAY'
2384 AND quantity = 1
2385 AND active_flag = 'Y';
2386
2387
2388 Cursor l_okl_contract_csr(p_chr_id IN NUMBER) is
2389 SELECT 1 from okc_k_rel_objs
2390 WHERE rty_code in ('OKLSRV','OKLUBB')
2391 AND jtot_object1_code = 'OKL_SERVICE'
2392 AND object1_id1 = to_char(p_chr_id);
2393
2394
2395 Cursor l_contract_update_date(p_chr_id IN NUMBER) is
2396 SELECT last_update_date from okc_k_headers_b
2397 WHERE id = p_chr_id;
2398
2399
2400
2401
2402 X_return_status Varchar2(1); -- was a parameter until subrequest impl.
2403 l_cvmv_rec OKC_CVM_PVT.cvmv_rec_type ;
2404 l_cvmv_out_rec OKC_CVM_PVT.cvmv_rec_type ;
2405 l_chrv_rec OKC_CONTRACT_PUB.chrv_rec_type;
2406 l_chrv_out_rec OKC_CONTRACT_PUB.chrv_rec_type;
2407 qty_uom_rec QTY_UOM_CSR%rowtype;
2408 l_bill_rec L_BILL_LINE_CSR%rowtype;
2409 l_item_rec L_INV_ITEM_CSR%rowtype;
2410
2411 l_return_status VARCHAR2(1);
2412 l_msg_count NUMBER;
2413 l_msg_data VARCHAR2(2000);
2414 l_okl_flag NUMBER := 0;
2415 l_msg_cnt NUMBER;
2416 l_api_version CONSTANT NUMBER := 1.0;
2417 l_init_msg_list CONSTANT VARCHAR2(1) := 'F';
2418 l_uom_code VARCHAR2(25);
2419 l_select_counter NUMBER := 0;
2420 l_reject_counter NUMBER := 0;
2421 l_process_counter NUMBER := 0;
2422 l_ar_feeder_ctr NUMBER := 0;
2423 l_con_update_date DATE ;
2424
2425 sub_line_exception EXCEPTION ;
2426 Main_line_exception EXCEPTION ;
2427
2428 SUBTYPE l_bclv_tbl_type_in is OKS_bcl_PVT.bclv_tbl_type;
2429
2430 l_bclv_tbl_in l_bclv_tbl_type_in;
2431 l_bclv_tbl_out l_bclv_tbl_type_in;
2432
2433 Type l_num_tbl is table of NUMBER index by BINARY_INTEGER ;
2434 chr_id l_num_tbl;
2435 cle_id l_num_tbl;
2436 l_line_no l_num_tbl;
2437
2438 l_sign Number;
2439 l_line_total Number := 0;
2440 l_level_elements_count Number := 0;
2441 l_sub_line_total Number := 0;
2442 l_sub_line_total_tmp Number := 0;
2443 l_g_ptr Number := 0 ;
2444 l_g_tbl_count Number := 0 ;
2445
2446
2447 --Start mchoudha Bug#3537100 17-APR-04
2448 --For Billing Report
2449
2450 l_billrep_tbl OKS_BILL_REC_PUB.bill_report_tbl_type;
2451 l_billrep_tbl_idx NUMBER;
2452 l_billrep_found BOOLEAN;
2453 j NUMBER;
2454 l_billrep_err_tbl OKS_BILL_REC_PUB.billrep_error_tbl_type;
2455 l_billrep_errtbl_idx NUMBER;
2456
2457 --End mchoudha Bug#3537100
2458
2459 --Bug#5378184
2460 l_err_code varchar2(10);
2461
2462 BEGIN
2463
2464 RETCODE := 0;
2465 X_return_status := 'S';
2466
2467 --mchoudha Fix for bug#4198616
2468 --initializing the variables in case of parallel workers
2469 l_yes_no := Fnd_profile.value('OKS_BILLING_REPORT_AND_LOG');
2470 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => OKS: Billing Report And Log is set to '||l_yes_no);
2471
2472 If l_write_log then
2473 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill processing starts ');
2474 End If;
2475
2476 If l_yes_no = 'YES' then
2477 l_write_log := TRUE;
2478 l_write_report := TRUE;
2479 Else
2480 l_write_log := FALSE;
2481 l_write_report := FALSE;
2482 End If;
2483
2484
2485 OPEN l_uom_csr;
2486 FETCH l_uom_csr into l_uom_code;
2487 CLOSE l_uom_csr;
2488
2489 IF l_uom_code Is Null Then
2490 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Time Units Of measure not set for DAY');
2491 Raise G_EXCEPTION_HALT_VALIDATION;
2492 END IF;
2493
2494 --/*Set The p_date which is used to control the program*/
2495 --p_date := nvl(P_Default_Date,sysdate);
2496
2497 --Start by mchoudha Bug#3537100 17-APR-04
2498 --For Billing Report
2499 /*
2500 l_pr_tbl_idx := 0 ;
2501 l_prs_tbl_idx := 0 ;
2502 */
2503
2504 l_billrep_tbl_idx := -1 ;
2505 l_billrep_errtbl_idx := -1 ;
2506 --End mchoudha Bug#3537100
2507
2508 If l_write_log then
2509 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => Opening billing_process cursor from '||p_process_from||' to '||p_process_to);
2510 End If;
2511
2512
2513
2514
2515 OPEN billing_process(p_process_from , p_process_to) ;
2516 LOOP
2517 FETCH billing_process bulk collect
2518 INTO chr_id, cle_id,l_line_no LIMIT 10000;
2519
2520 If l_write_log then
2521 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => After Fetch billing_process cursor count of line ids fetched '||cle_id.count);
2522 End If;
2523
2524 IF cle_id.COUNT > 0 THEN --chk for count
2525
2526
2527 FOR i in cle_id.FIRST..cle_id.LAST
2528 LOOP
2529
2530 BEGIN
2531
2532 DBMS_TRANSACTION.SAVEPOINT('BEFORE_TRANSACTION');
2533
2534 IF l_write_log THEN
2535 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => B4 opening cursor l_bill_line_csr line id '||cle_id(i)||' header ID '||chr_id(i)||' line number '||l_line_no(i));
2536 END IF;
2537
2538 /*****
2539 The policy context is set to multiple for Bug# 6158988
2540 This is because context is set for a particular org
2541 down the code and need to reset it back
2542 *****/
2543 mo_global.set_policy_context('M', Null);
2544
2545
2546 OPEN l_bill_line_csr (chr_id(i),cle_id(i));
2547 FETCH l_bill_line_csr into l_bill_rec ;
2548 IF (l_bill_line_csr%notfound) THEN
2549 FND_FILE.PUT_LINE(FND_FILE.LOG,'not found l_bill_line_csr for line id '||cle_id(i)||' for contract id '||chr_id(i));
2550 CLOSE l_bill_line_csr;
2551 RAISE MAIN_LINE_EXCEPTION;
2552 END IF;
2553 CLOSE l_bill_line_csr;
2554
2555
2556 l_select_counter := l_select_counter + 1;
2557 l_ar_Feeder_ctr := 0;
2558
2559 IF l_write_log THEN
2560 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Contract# : '||l_bill_rec.Contract_number);
2561 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Modifier: '||l_bill_rec.Contract_number_modifier);
2562 FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Parameter Default Date ' || P_date);
2563 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Period Start# : '||l_bill_rec.period_start);
2564 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Period Type: '||l_bill_rec.period_type);
2565 FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Price_uom ' ||l_bill_rec.price_uom);
2566 END IF;
2567
2568 -- Commented as part of R12. Authoring org id changed to org_id (MOAC)
2569 -- okc_context.set_okc_org_context(l_bill_Rec.authoring_org_id, Null);
2570 okc_context.set_okc_org_context(l_bill_Rec.org_id, Null);
2571
2572 IF l_write_log THEN
2573 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => After setting okc_context.set_okc_org_context for org '||l_bill_Rec.org_id);
2574 END IF;
2575
2576
2577 l_okl_flag := 0; --- check for OKL contract
2578
2579 OPEN l_okl_contract_csr (l_bill_rec.dnz_chr_id);
2580 FETCH l_okl_contract_csr into l_okl_flag;
2581 CLOSE l_okl_contract_csr;
2582
2583 If l_write_log then
2584 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => B4 opening cursor l_bill_line_csr l_okl_flag '||l_okl_flag);
2585 End If;
2586
2587 /*Invoices are to be generated only for contracts whose
2588 AR_interface_flag = 'Y'
2589 */
2590 If l_write_log then
2591 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => l_bill_rec.ar_interface_yn '||Nvl(l_bill_rec.ar_interface_yn,'Y'));
2592 End If;
2593
2594 IF Nvl(l_bill_rec.ar_interface_yn,'Y') = 'Y' THEN -- {
2595
2596 --Start mchoudha Bug#3537100 17-APR-04
2597 --For Billing Report
2598
2599 /* *** Insert the lines to a PL/SQL table ** */
2600 -- l_pr_tbl_idx := l_pr_tbl_idx + 1;
2601
2602
2603
2604 If l_write_log then
2605 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => B4 initializing Billing Report ');
2606 End If;
2607
2608 l_billrep_found := FALSE;
2609
2610 IF (l_billrep_tbl.count > 0) THEN
2611 j := l_billrep_tbl.FIRST;
2612 LOOP
2613 IF(l_billrep_tbl(j).Currency_code = l_bill_rec.Currency_code) THEN
2614 l_billrep_found := TRUE;
2615 l_billrep_tbl_idx := j; --point to the index containing the currency code
2616 EXIT;
2617 END IF;
2618 EXIT WHEN j = l_billrep_tbl.LAST;
2619 j := l_billrep_tbl.NEXT(j);
2620 END LOOP;
2621 END IF;
2622
2623
2624 IF (l_billrep_found = FALSE) THEN
2625 l_billrep_tbl_idx := l_billrep_tbl_idx + 1;
2626 l_billrep_tbl(l_billrep_tbl_idx).Currency_code := l_bill_rec.Currency_code;
2627 l_billrep_tbl(l_billrep_tbl_idx).Successful_Lines := 0;
2628 l_billrep_tbl(l_billrep_tbl_idx).Rejected_Lines := 0;
2629 l_billrep_tbl(l_billrep_tbl_idx).Successful_SubLines := 0;
2630 l_billrep_tbl(l_billrep_tbl_idx).Rejected_SubLines := 0;
2631 l_billrep_tbl(l_billrep_tbl_idx).Successful_Lines_Value := 0;
2632 l_billrep_tbl(l_billrep_tbl_idx).Rejected_Lines_Value := 0;
2633 l_billrep_tbl(l_billrep_tbl_idx).Successful_SubLines_Value := 0;
2634 l_billrep_tbl(l_billrep_tbl_idx).Rejected_SubLines_Value := 0;
2635 END IF;
2636
2637 If l_write_log then
2638 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => After initializing Billing Report ');
2639 End If;
2640
2641 --End mchoudha Bug#3537100
2642
2643 OPEN qty_uom_csr(l_bill_rec.id);
2644 FETCH qty_uom_csr into qty_uom_rec;
2645 CLOSE qty_uom_csr;
2646
2647 If l_write_log then
2648 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => After opening qty_uom_csr uom_code '||qty_uom_rec.uom_code);
2649 End If;
2650
2651
2652 IF (l_bill_rec.lse_id = 46) THEN
2653 -----------------------------------------------------------------
2654 If l_write_log then
2655 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => B4 calling Bill_Subscription_item for '||l_bill_rec.id);
2656 End If;
2657
2658 Bill_Subscription_item(
2659 l_bill_rec.dnz_chr_id,
2660 l_bill_rec.id,
2661 l_bill_rec.start_date,
2662 l_bill_rec.date_terminated,
2663 l_bill_rec.end_date,
2664 l_bill_rec.currency_code,
2665 l_okl_flag,
2666 qty_uom_rec.number_of_items,
2667 qty_uom_rec.uom_code,
2668 p_prv,
2669 p_date,
2670 l_billrep_tbl,
2671 l_billrep_tbl_idx,
2672 l_billrep_err_tbl,
2673 l_billrep_errtbl_idx,
2674 l_ar_feeder_ctr,
2675 l_return_status);
2676
2677
2678 IF (l_return_status <> 'S') THEN
2679 ---DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
2680 RAISE MAIN_LINE_EXCEPTION;
2681 END IF;
2682 -----------------------------------------------------------------
2683 ELSIF (l_bill_rec.lse_id in (1,19)) Then
2684 --Process_service_items
2685 -----------------------------------------------------------------
2686 If l_write_log then
2687 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => B4 calling Bill_Service_item for '||l_bill_rec.id);
2688 End If;
2689
2690 Bill_Service_item(
2691 l_bill_rec.dnz_chr_id,
2692 l_bill_rec.contract_number,
2693 l_bill_rec.Contract_number_modifier ,
2694 l_bill_rec.line_number,
2695 l_bill_rec.lse_id ,
2696 l_bill_rec.inv_organization_id,
2697 l_bill_rec.id,
2698 l_bill_rec.start_date,
2699 l_bill_rec.date_terminated,
2700 l_bill_rec.end_date,
2701 l_bill_rec.currency_code,
2702 l_bill_rec.object1_id1,
2703 l_bill_rec.object1_id2,
2704 l_okl_flag,
2705 --qty_uom_rec.number_of_items,
2706 --qty_uom_rec.uom_code,
2707 p_prv,
2708 p_date,
2709 l_bill_rec.summary_yn,
2710 l_ar_feeder_ctr,
2711 l_billrep_tbl,
2712 l_billrep_tbl_idx,
2713 l_billrep_err_tbl,
2714 l_billrep_errtbl_idx,
2715 l_return_status
2716 );
2717
2718 IF (l_return_status <> 'S') THEN
2719 ----DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
2720 RAISE MAIN_LINE_EXCEPTION;
2721 END IF;
2722 -----------------------------------------------------------------
2723
2724 ELSIF (l_bill_rec.lse_id = 12) THEN
2725 -- Process_usage_items
2726 If l_write_log then
2727 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => B4 calling Bill_Usage_Item for '||l_bill_rec.id);
2728 End If;
2729 -------------------------------------------------------------------------
2730 -- Begin partial period computation logic
2731 -- Developer Mani Choudhary
2732 -- Date 12-JUN-2005
2733 -- added period type and period start as two parameters
2734 -------------------------------------------------------------------------
2735 Bill_Usage_Item(
2736 l_bill_rec.dnz_chr_id,
2737 l_bill_rec.contract_number,
2738 l_bill_rec.Contract_number_modifier ,
2739 l_bill_rec.line_number,
2740 l_bill_rec.lse_id ,
2741 l_bill_rec.object1_id1,
2742 l_bill_rec.object1_id2,
2743 l_bill_rec.id,
2744 l_bill_rec.start_date,
2745 l_bill_rec.date_terminated,
2746 l_bill_rec.end_date,
2747 l_bill_rec.inv_organization_id,
2748 l_bill_rec.currency_code,
2749 l_bill_rec.settlement_interval,
2750 qty_uom_rec.uom_code,
2751 l_uom_code,
2752 l_okl_flag,
2753 p_prv,
2754 p_date,
2755 l_billrep_tbl,
2756 l_billrep_tbl_idx,
2757 l_billrep_err_tbl,
2758 l_billrep_errtbl_idx,
2759 l_ar_feeder_ctr,
2760 l_bill_rec.period_type,
2761 l_bill_rec.period_start,
2762 l_return_status
2763 );
2764 IF (l_return_status <> 'S') THEN
2765 ----DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
2766 RAISE MAIN_LINE_EXCEPTION;
2767 END IF;
2768
2769 --------------------------------------------------------
2770 END IF; --lse_id if stmt
2771
2772 END IF; -- } end if for Bill Yes / No
2773
2774
2775 IF (l_ar_feeder_ctr = 1 ) THEN
2776 If l_write_log then
2777 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => B4 calling OKS_ARFEEDER_PUB.Get_REC_FEEDER for '||l_bill_rec.id);
2778 End If;
2779
2780 OKS_ARFEEDER_PUB.Get_REC_FEEDER
2781 (
2782 X_RETURN_STATUS => l_return_status,
2783 X_MSG_COUNT => l_msg_cnt,
2784 X_MSG_DATA => l_msg_data,
2785 P_FLAG => p_flag, -- checkout
2786 P_CALLED_FROM => p_calledfrom,
2787 P_DATE => p_date,
2788 P_CLE_ID => l_bill_Rec.id,
2789 P_PRV => p_prv,
2790 P_BILLREP_TBL => l_billrep_tbl,
2791 P_BILLREP_TBL_IDX => l_billrep_tbl_idx,
2792 P_BILLREP_ERR_TBL => l_billrep_err_tbl,
2793 P_BILLREP_ERR_TBL_IDX => l_billrep_errtbl_idx
2794 );
2795
2796 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
2797 oks_bill_rec_pub.get_message (
2798 l_msg_cnt => l_msg_count,
2799 l_msg_data => l_msg_data);
2800 l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
2801 l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
2802
2803 oks_bill_Rec_pub.Set_sub_line(
2804 P_PROCESSED_LINES_TBL => l_processed_lines_tbl,
2805 P_PROCESSED_SUB_LINES_TBL => l_processed_sub_lines_tbl,
2806 P_ERROR_MESSAGE => l_processed_lines_tbl(l_pr_tbl_idx).Error_Message,
2807 P_TOP_LINE => l_bill_rec.id) ;
2808
2809 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => Failed in AR FEEDER :'||' '||l_bill_rec.id);
2810
2811 ---DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
2812 Raise Main_Line_Exception;
2813 END IF;
2814
2815
2816 /*
2817 Contract version updates is done for OKI.
2818 OKI pulls data depending upon minor version change.
2819 Bug# 5637820 - This procedure is made as an autonomous transaction
2820 */
2821
2822 update_version(p_dnz_chr_id => l_bill_rec.dnz_chr_id);
2823
2824
2825 END IF; -- l_ar_Feeder = 1
2826
2827
2828 l_process_counter := l_process_counter + 1;
2829
2830 EXCEPTION
2831 WHEN MAIN_LINE_EXCEPTION THEN
2832 --Added by pmallara begin exception block to fix bug#3961046
2833 BEGIN
2834 l_reject_counter := l_reject_counter + 1;
2835 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
2836 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => MAIN LINE EXCEPTION RAISE'||' '||sqlerrm);
2837 RETCODE := 1;
2838 EXCEPTION
2839 WHEN OTHERS THEN
2840 FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Failed- when trying to rollback inside MAIN_LINE_EXCEPTION '||sqlerrm );
2841 exit;
2842 END;
2843
2844 WHEN OTHERS THEN
2845 --Added by pmallara begin exception block to fix bug#3961046
2846 BEGIN
2847 l_processed_lines_tbl(l_pr_tbl_idx).Billed_YN := 'N' ;
2848 l_processed_lines_tbl(l_pr_tbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||' Main Loop - When others ' ;
2849
2850 /*FOR ERROR REPORT*/
2851 l_billrep_errtbl_idx := l_billrep_errtbl_idx + 1;
2852 l_billrep_err_tbl(l_billrep_errtbl_idx).Top_Line_id := l_bill_rec.id;
2853 l_billrep_err_tbl(l_billrep_errtbl_idx).Lse_Id :=l_bill_rec.lse_id ;
2854 l_billrep_err_tbl(l_billrep_errtbl_idx).Sub_line_id := NULL;
2855 l_billrep_err_tbl(l_billrep_errtbl_idx).Error_Message := 'Error: '|| sqlerrm||'. Error Message: '||l_msg_data ;
2856
2857 l_reject_counter := l_reject_counter + 1;
2858 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
2859 FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Failed- when others exception raised in Mainline loop '||sqlerrm );
2860 RETCODE := 1;
2861
2862 --Bug#5378184
2863 l_err_code := substr(sqlerrm,instr(sqlerrm,'-')+1,5);
2864 if (l_err_code = '01555') then
2865 raise;
2866 end if;
2867 --End Bug#5378184
2868
2869 EXCEPTION
2870 WHEN OTHERS THEN
2871 FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => Failed- when trying to rollback inside WHEN OTHERS exception'||sqlerrm );
2872 exit;
2873 END;
2874 END;
2875 END LOOP; --end of loop for cle_id tbl
2876
2877 --Added to clear pl/sql tables after processing records
2878 cle_id.delete;
2879 chr_id.delete;
2880 l_line_no.delete;
2881 END IF; ----end of count for cle_id tbl
2882
2883 EXIT WHEN billing_process%NOTFOUND ;
2884 END LOOP; --MAIN LOOP END for billing_process cursor
2885
2886 /* *** Create the output file for Billing program. *** */
2887
2888 If l_write_report then
2889 OKS_BILL_UTIL_PUB.CREATE_REPORT
2890 ( p_billrep_table => l_billrep_tbl
2891 ,p_billrep_err_tbl => l_billrep_err_tbl
2892 ,p_line_from => P_process_from
2893 ,p_line_to => P_process_to
2894 ,x_return_status => l_return_status);
2895
2896 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) THEN
2897 FND_FILE.PUT_LINE(FND_FILE.LOG,'OKS_BILLING_PUB.Calculate_bill => *** Failed to create Billing Report ***' );
2898 END IF;
2899 End If;
2900
2901 If l_write_log then
2902 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill processing Ends ');
2903 End If;
2904
2905 ---- Added if clause to issue warning for any data related issue
2906 IF (RETCODE <> 1) THEN
2907 RETCODE := 0;
2908 End if;
2909
2910 EXCEPTION
2911
2912 WHEN G_EXCEPTION_HALT_VALIDATION THEN
2913 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2914 RETCODE := 2;
2915 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => when g_exception_rollback raised'||sqlerrm);
2916 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
2917 WHEN G_EXCEPTION_ROLLBACK THEN
2918 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => when g_exception_rollback raised'||sqlerrm);
2919 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
2920
2921 WHEN OTHERS THEN
2922 x_return_status := OKC_API.G_RET_STS_UNEXP_ERROR;
2923 RETCODE := 2;
2924 FND_FILE.PUT_LINE( FND_FILE.LOG, 'OKS_BILLING_PUB.Calculate_bill => when others raised'||sqlerrm);
2925 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,
2926 G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
2927 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
2928
2929 END Calculate_bill;
2930
2931 /*----------------------------------------------------------------------
2932 Concurrent Program Wrapper for Regular Service and Usage Billing Program
2933 ------------------------------------------------------------------------*/
2934
2935
2936 PROCEDURE Billing_Main
2937 (
2938 ERRBUF OUT NOCOPY VARCHAR2,
2939 RETCODE OUT NOCOPY NUMBER,
2940 P_CONTRACT_HDR_ID IN NUMBER,
2941 -- nechatur 29-Nov-2005 bug#4459229 changing type of P_DEFAULT_TYPE to VARCHAR2
2942 --P_DEFAULT_DATE IN DATE,
2943 P_DEFAULT_DATE IN VARCHAR2,
2944 --end bug#4459229
2945 P_ORG_ID IN NUMBER,
2946 P_CUSTOMER_ID IN NUMBER,
2947 P_CATEGORY IN VARCHAR2,
2948 P_GRP_ID IN NUMBER,
2949 P_PROCESS IN VARCHAR2 --No for Normal processing,Yes for Preview Processing
2950 )
2951 IS
2952
2953
2954 CONC_STATUS BOOLEAN;
2955 l_ret INTEGER;
2956 l_subrequests INTEGER;
2957 l_errbuf VARCHAR2(240);
2958 l_msg_data VARCHAR2(2000);
2959 l_return_status VARCHAR2(1);
2960 v_truncstring VARCHAR2(500);
2961 l_view_by_org VARCHAR2(10);
2962 l_dummy VARCHAR2(1);
2963 l_statement VARCHAR2(30000);
2964 p_date DATE;
2965 l_line_no NUMBER;
2966 l_max_boundary NUMBER;
2967 l_min_boundary NUMBER;
2968 l_slot_size NUMBER;
2969 l_retcode NUMBER;
2970 l_msg_count NUMBER;
2971 p_prv NUMBER;
2972 l_org_id NUMBER;
2973 l_flag NUMBER := 0;
2974 v_cursor NUMBER;
2975
2976 l_billrep_tbl OKS_BILL_REC_PUB.bill_report_tbl_type;
2977 l_billrep_tbl_idx NUMBER;
2978 l_billrep_err_tbl OKS_BILL_REC_PUB.billrep_error_tbl_type;
2979 l_billrep_errtbl_idx NUMBER;
2980
2981 BEGIN
2982
2983 FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Billing_Main Starts');
2984
2985 l_yes_no := Fnd_profile.value('OKS_BILLING_REPORT_AND_LOG');
2986
2987 FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Billing_Main => OKS: Billing Report And Log is set to '||l_yes_no);
2988
2989 If l_yes_no = 'YES' then
2990 l_write_log := TRUE;
2991 l_write_report := TRUE;
2992 Else
2993 l_write_log := FALSE;
2994 l_write_report := FALSE;
2995 End If;
2996
2997 l_org_id := p_org_id;
2998
2999 --mchoudha fixed bug#4729936
3000 --IF (nvl(fnd_profile.value('OKC_VIEW_K_BY_ORG'),'N') = 'Y' ) THEN
3001 -- l_org_id := fnd_profile.value('ORG_ID');
3002 If l_write_log then
3003 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => parameter Org ID is '||l_org_id);
3004 End If;
3005 -- END IF;
3006
3007 If l_write_log then
3008 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => Preview Y/N ='||P_process);
3009 End If;
3010
3011 /* Set p_prv flag, which is used to in calls to AR Feeder.
3012 p_prv = 2 -- preview billing
3013 */
3014 IF (P_PROCESS = 'Y') then
3015 P_PRV := 2;
3016 ELSE
3017 P_PRV := 1;
3018 END IF;
3019
3020 user_id := FND_GLOBAL.USER_ID;
3021 If l_write_log then
3022 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => User_Id ='||to_char(user_id));
3023 End If;
3024
3025 --nechatur 29-Nov-2005 for bug#4459229
3026 --p_date := nvl(trunc(p_default_date),trunc(sysdate));
3027 ----p_date := nvl(trunc(TO_DATE(p_default_date, 'yyyy/mm/dd hh24:mi:ss')),trunc(sysdate));
3028 -- end bug#4459229
3029
3030 ----Hari bug# 5704211
3031 p_date := nvl(fnd_date.canonical_to_date(p_default_date), trunc(sysdate));
3032
3033 --Added this If condition so that the table does not get
3034 --truncated during the restart of the parent
3035 If(FND_CONC_GLOBAL.request_data is null) THEN
3036
3037 If l_write_log then
3038 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => Truncating oks_process_billing');
3039 End If;
3040
3041 v_cursor := DBMS_SQL.OPEN_CURSOR;
3042 /*Following line is required to avoid GSCC errors at ARU creation.
3043 Concatination of string is required to avoid run time error.
3044 Ampersand 1 is taking value from un_oks param in dbdrv command line.*/
3045 --v_truncstring := 'Truncate Table oks';
3046 --v_truncstring := v_truncstring||'.oks_process_billing';
3047 v_truncstring := 'Truncate Table OKS'||'.oks_process_billing';
3048 DBMS_SQL.PARSE(v_cursor,v_truncstring,DBMS_SQL.V7);
3049 DBMS_SQL.CLOSE_CURSOR(v_cursor);
3050 END IF;
3051
3052
3053 /* This call to AR Feeder is done to process any termination records.
3054 It is kept in the begining of the code with purpose. In case of parallel
3055 run of billing program , this api may not get executed if placed at the
3056 end of api. This is because there is return statement in parallel worker
3057 code. This is fix for bug # 2963174
3058 */
3059 IF (FND_CONC_GLOBAL.request_data is null) THEN -- not a restart of parent
3060
3061 If l_write_log then
3062 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => Call to OKS_ARFEEDER_PUB.Get_REC_FEEDER to interface termination records');
3063 End If;
3064
3065 OKS_ARFEEDER_PUB.Get_REC_FEEDER
3066 (
3067 x_return_status => l_return_status,
3068 x_msg_count => l_msg_count,
3069 x_msg_data => l_msg_data,
3070 p_flag => l_flag,
3071 p_called_from => 1,
3072 p_date => p_date,
3073 p_cle_id => NULL,
3074 p_prv => 3, --to interface termination records and any stray records
3075 p_billrep_tbl => l_billrep_tbl,
3076 p_billrep_tbl_idx => l_billrep_tbl_idx,
3077 p_billrep_err_tbl => l_billrep_err_tbl,
3078 p_billrep_err_tbl_idx => l_billrep_errtbl_idx
3079 ) ;
3080
3081 If l_write_log then
3082 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => After Call to OKS_ARFEEDER_PUB.Get_REC_FEEDER to interface termination records l_return_status '||l_return_status);
3083 End If;
3084
3085
3086 -- We need to see if we should spawn sub-requests;
3087 -- i.e., the number of contract lines that qualify is > MAX_SINGLE_REQUEST.
3088
3089 DBMS_TRANSACTION.SAVEPOINT('BEFORE_MAIN_BILLING');
3090
3091
3092 l_line_no := 0;
3093
3094 IF (p_grp_id is not null) or (p_customer_id is not null) or
3095 (p_category is not null) or (l_org_id is not null) or
3096 (p_contract_hdr_id is not null) Then
3097 If l_write_log then
3098 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Parameters Passed => Contract Id '||p_contract_hdr_id);
3099 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Customer '||P_customer_id|| ' Category '||p_category);
3100 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Org '||l_org_id|| ' Group '||p_grp_id);
3101 End If;
3102
3103 End If;
3104
3105
3106 IF (p_grp_id is null) and (p_customer_id is null) and
3107 (p_category is null) and (l_org_id is null) and
3108 (p_contract_hdr_id is null) Then
3109
3110 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Blind Query ' || ': with date '||p_date);
3111
3112 INSERT /*+ append */ into oks_process_billing (chr_id,cle_id,line_no,currency_code)
3113 SELECT /*+ leading(lvl) ORDERED USE_HASH(line hdr sts as1)
3114 swap_join_inputs(hdr) swap_join_inputs(sts) */
3115 hdr.id hdr_id, line.id line_id, rownum, hdr.currency_code
3116 from (
3117 Select /*+ FULL (lvl) no_merge */ distinct lvl.parent_cle_id
3118 from oks_level_elements lvl
3119 where lvl.date_completed is null
3120 and trunc(lvl.date_to_interface) <= trunc(p_date)
3121 ) lvl,
3122 okc_k_lines_b line,
3123 (
3124 Select /*+ FULL (a) no_merge */ distinct scs_code,sts_code
3125 from okc_assents a
3126 where a.opn_code = 'INVOICE'
3127 and a.allowed_yn = 'Y'
3128 ) as1,
3129 okc_k_headers_b hdr,
3130 (
3131 Select /*+ FULL (osb) no_merge */ distinct code
3132 from okc_statuses_b osb
3133 where osb.ste_code <> 'HOLD'
3134 ) sts
3135 WHERE Hdr.id = line.dnz_chr_id
3136 AND Hdr.scs_code in ('SERVICE','WARRANTY','SUBSCRIPTION')
3137 AND Hdr.Template_yn = 'N'
3138 AND line.id = lvl.parent_cle_id
3139 AND line.lse_id in (1,12,19,46)
3140 AND line.sts_code = as1.sts_code
3141 AND as1.scs_code = Hdr.scs_code
3142 AND sts.code = Hdr.sts_code
3143 AND line.id not in ( Select /*+ index_ffs (rel) HASH_AJ */ rel.cle_id
3144 From okc_k_rel_objs rel
3145 Where rel.cle_id is not null
3146 );
3147
3148 Elsif
3149 (p_grp_id is null) and (p_customer_id is null) and
3150 (p_category is null) and (l_org_id is not null) and
3151 (p_contract_hdr_id is null) Then
3152
3153 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Query for Org id');
3154
3155 INSERT /*+ append */ into oks_process_billing (chr_id,cle_id,line_no,currency_code)
3156 SELECT /*+ leading(lvl) ORDERED USE_HASH(line hdr sts as1)
3157 swap_join_inputs(hdr) swap_join_inputs(sts) */
3158 hdr.id hdr_id, line.id line_id, rownum, hdr.currency_code
3159 from (
3160 Select /*+ FULL (lvl) no_merge */ distinct lvl.parent_cle_id
3161 from oks_level_elements lvl
3162 where lvl.date_completed is null
3163 and trunc(lvl.date_to_interface) <= trunc(p_date)
3164 ) lvl,
3165 okc_k_lines_b line,
3166 (
3167 Select /*+ FULL (a) no_merge */ distinct scs_code,sts_code
3168 from okc_assents a
3169 where a.opn_code = 'INVOICE'
3170 and a.allowed_yn = 'Y'
3171 ) as1,
3172 okc_k_headers_b hdr,
3173 (
3174 Select /*+ FULL (osb) no_merge */ distinct code
3175 from okc_statuses_b osb
3176 where osb.ste_code <> 'HOLD'
3177 ) sts
3178 WHERE Hdr.id = line.dnz_chr_id
3179 AND Hdr.scs_code in ('SERVICE','WARRANTY','SUBSCRIPTION')
3180 AND Hdr.Authoring_Org_Id = l_org_id
3181 AND Hdr.Template_yn = 'N'
3182 AND line.id = lvl.parent_cle_id
3183 AND line.lse_id in (1,12,19,46)
3184 AND line.sts_code = as1.sts_code
3185 AND as1.scs_code = Hdr.scs_code
3186 AND sts.code = Hdr.sts_code
3187 AND line.id not in ( Select /*+ index_ffs (rel) HASH_AJ */ rel.cle_id
3188 From okc_k_rel_objs rel
3189 Where rel.cle_id is not null
3190 );
3191 Elsif
3192 (p_contract_hdr_id is not null) Then
3193
3194 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Query for Contract id');
3195
3196 INSERT into oks_process_billing (chr_id,cle_id,line_no,currency_code)
3197
3198 SELECT hdr.id hdr_id,line.id line_id,rownum,hdr.currency_code
3199 from okc_k_headers_b hdr, okc_k_lines_b line
3200 WHERE Hdr.id = line.dnz_chr_id
3201 AND Hdr.scs_code in ('SERVICE','WARRANTY','SUBSCRIPTION')
3202 AND Hdr.Template_yn = 'N'
3203 AND Hdr.id = p_contract_hdr_id
3204 AND line.id IN (Select lvl.parent_cle_id
3205 from oks_level_elements lvl
3206 where lvl.date_completed is null
3207 and trunc(lvl.date_to_interface) <= trunc(p_date) )
3208 AND exists (Select 1 from okc_statuses_b osb
3209 where osb.ste_code <> 'HOLD'
3210 and osb.code = Hdr.sts_code )
3211 AND exists ( SELECT 1 from okc_assents a
3212 where line.sts_code = a.sts_code
3213 and a.scs_code = Hdr.scs_code
3214 and a.opn_code = 'INVOICE'
3215 and a.allowed_yn = 'Y' )
3216 AND line.lse_id in (1,12,19,46)
3217 AND line.id not in ( Select rel.cle_id
3218 From okc_k_rel_objs rel
3219 Where rel.cle_id is not null );
3220
3221 Elsif
3222 (p_grp_id is not null) and (p_customer_id is null) and
3223 (p_category is null) and (l_org_id is not null) and
3224 (p_contract_hdr_id is null) Then
3225
3226 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Query for contract group and org id');
3227
3228 INSERT /*+ append */ into oks_process_billing (chr_id,cle_id,line_no,currency_code)
3229 SELECT /*+ leading(lvl) ORDERED USE_HASH(line hdr sts as1 cgp)
3230 swap_join_inputs(hdr) swap_join_inputs(sts) swap_join_inputs(cgp) */
3231 hdr.id hdr_id, line.id line_id, rownum, hdr.currency_code
3232 from (
3233 Select /*+ FULL (lvl) no_merge */ distinct lvl.parent_cle_id
3234 from oks_level_elements lvl
3235 where lvl.date_completed is null
3236 and trunc(lvl.date_to_interface) <= trunc(p_date)
3237 ) lvl,
3238 okc_k_lines_b line,
3239 (
3240 Select /*+ FULL (a) no_merge */ distinct scs_code,sts_code
3241 from okc_assents a
3242 where a.opn_code = 'INVOICE'
3243 and a.allowed_yn = 'Y'
3244 ) as1,
3245 okc_k_headers_b hdr,
3246 (
3247 Select /*+ FULL (osb) no_merge */ distinct code
3248 from okc_statuses_b osb
3249 where osb.ste_code <> 'HOLD'
3250 ) sts,
3251 (
3252 Select /*+ FULL (grp) no_merge */ distinct included_chr_id
3253 from OKC_K_GRPINGS grp
3254 where grp.cgp_parent_id = p_grp_id
3255 ) cgp
3256 WHERE Hdr.id = line.dnz_chr_id
3257 AND Hdr.scs_code in ('SERVICE','WARRANTY','SUBSCRIPTION')
3258 AND Hdr.Template_yn = 'N'
3259 AND Hdr.Authoring_Org_Id = l_org_id
3260 AND line.id = lvl.parent_cle_id
3261 AND line.lse_id in (1,12,19,46)
3262 AND line.sts_code = as1.sts_code
3263 AND as1.scs_code = Hdr.scs_code
3264 AND sts.code = Hdr.sts_code
3265 AND cgp.included_chr_id = Hdr.id
3266 AND line.id not in ( Select /*+ index_ffs (rel) HASH_AJ */ rel.cle_id
3267 From okc_k_rel_objs rel
3268 Where rel.cle_id is not null
3269 );
3270
3271 Else
3272
3273 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Query for rest of the parameters');
3274
3275 INSERT into oks_process_billing (chr_id,cle_id,line_no,currency_code)
3276
3277 SELECT hdr.id hdr_id,line.id line_id,rownum,hdr.currency_code
3278 from okc_k_headers_b hdr, okc_k_lines_b line
3279 WHERE Hdr.id = line.dnz_chr_id
3280 AND Hdr.scs_code in ('SERVICE','WARRANTY','SUBSCRIPTION')
3281 AND Hdr.Template_yn = 'N'
3282 AND Hdr.Authoring_Org_Id = nvl(l_org_id, hdr.authoring_org_id)
3283 AND Hdr.Scs_code = nvl(p_category, hdr.scs_code)
3284 AND exists
3285 (select 1 from OKC_K_PARTY_ROLES_B okp
3286 where okp.dnz_chr_id = hdr.id
3287 and okp.rle_code in ('CUSTOMER','SUBSCRIBER')
3288 and okp.object1_id1 = nvl(P_customer_id, okp.object1_id1) )
3289 AND exists
3290 (select 1 from OKC_K_GRPINGS okg
3291 where okg.included_chr_id = hdr.id
3292 and okg.cgp_parent_id = nvl(p_grp_id, okg.cgp_parent_id) )
3293 AND line.id IN (Select lvl.parent_cle_id
3294 from oks_level_elements lvl
3295 where lvl.date_completed is null
3296 and trunc(lvl.date_to_interface) <= trunc(p_date) )
3297 AND exists (Select 1 from okc_statuses_b osb
3298 where osb.ste_code <> 'HOLD'
3299 and osb.code = Hdr.sts_code )
3300 AND exists ( SELECT 1 from okc_assents a
3301 where line.sts_code = a.sts_code
3302 and a.scs_code = Hdr.scs_code
3303 and a.opn_code = 'INVOICE'
3304 and a.allowed_yn = 'Y' )
3305 AND line.lse_id in (1,12,19,46)
3306 AND line.id not in ( Select rel.cle_id
3307 From okc_k_rel_objs rel
3308 Where rel.cle_id is not null );
3309
3310 End if; -- End if clause for null parameters check
3311
3312 ---this commit is required for the oks_process_billing table. should not be commented:
3313 commit;
3314 select count(*) into l_line_no from oks_process_billing;
3315
3316 If l_write_log then
3317 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => Number of records in oks_process_billing is '||l_line_no);
3318 End If;
3319
3320
3321 END IF;
3322
3323
3324 IF (p_contract_hdr_id is null) AND -- not a specific contract
3325 (FND_CONC_GLOBAL.request_data is null) AND -- not a restart of parent
3326 (nvl(FND_PROFILE.VALUE('OKS_PARALLEL_WORKER'),'NO') = 'YES') THEN
3327
3328 -- l_line_no represent total records to be processed here
3329 If l_write_log then
3330 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => Profile option OKS: Parallel Worker is set to YES');
3331 End If;
3332
3333 IF (l_line_no > MAX_SINGLE_REQUEST ) THEN
3334 -- populate lo,hi varrays
3335
3336 l_subrequests := 30; -- hard coded.
3337 l_slot_size := ceil(l_line_no / l_subrequests);
3338
3339 --SELECT ceil(count(*)/10) into l_slot_size from oks_process_billing;
3340 l_min_boundary := 1;
3341 l_max_boundary := l_slot_size;
3342 FOR idx in 1..l_subrequests
3343 LOOP
3344 l_ret := FND_REQUEST.submit_request
3345 ('OKS','OKS_BILLING_SUB',
3346 to_char(idx), -- UI job display
3347 null, TRUE, -- TRUE means isSubRequest
3348 1, l_flag, p_date,
3349 l_min_boundary, l_max_boundary,
3350 p_prv);
3351
3352
3353
3354
3355
3356 IF (l_ret = 0) THEN
3357 errbuf := fnd_message.get;
3358 retcode := 2;
3359 FND_FILE.PUT_LINE (FND_FILE.LOG,'Sub-request failed to submit: '
3360 || errbuf);
3361 return;
3362 ELSE
3363 FND_FILE.PUT_LINE (FND_FILE.LOG,'Sub-request '||to_char(l_ret)||
3364 ' submitted for line numbers '||l_min_boundary||' to '||l_max_boundary);
3365 END IF;
3366
3367 IF (l_max_boundary >= l_line_no) THEN
3368 EXIT;
3369 END IF;
3370
3371 l_min_boundary := l_max_boundary + 1;
3372 l_max_boundary := l_max_boundary + l_slot_size;
3373 END LOOP;
3374 -- after submitting sub-requests, set the parent status to PAUSED
3375 -- and set the request_data to a non-null value to detect restart
3376 FND_CONC_GLOBAL.set_req_globals
3377 (conc_status => 'PAUSED',
3378 request_data => to_char(l_subrequests));
3379 errbuf := to_char(l_subrequests) || ' sub-requests submitted';
3380 retcode := 0;
3381 return; -- parent exits and waits for children to finish before restart
3382 END IF; -- l_agg_rec.total
3383
3384 ELSIF ((FND_CONC_GLOBAL.request_data is not null ) AND
3385 (nvl(FND_PROFILE.VALUE('OKS_PARALLEL_WORKER'),'NO') = 'YES')) THEN
3386 -- restart detected (sub-requests finished)...cleanup and exit.
3387 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => Commiting here');
3388 COMMIT;
3389
3390 v_cursor := DBMS_SQL.OPEN_CURSOR;
3391 /* Following line is required to avoid GSCC errors at ARU creation.
3392 Concatination of string is required to avoid run time error */
3393 -- v_truncstring := 'Truncate Table oks';
3394 -- v_truncstring := v_truncstring||'.oks_process_billing';
3395 v_truncstring := 'Truncate Table OKS'||'.oks_process_billing';
3396 DBMS_SQL.PARSE(v_cursor,v_truncstring,DBMS_SQL.V7);
3397 DBMS_SQL.CLOSE_CURSOR(v_cursor);
3398
3399
3400 retcode := 0;
3401 return;
3402 END IF; -- parent test
3403
3404 If l_write_log then
3405 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => B4 Calling OKS_BILLING_PUB.CALCULATE_BILL for sequential run');
3406 End If;
3407
3408 OKS_BILLING_PUB.CALCULATE_BILL
3409 (l_errbuf, l_retcode, 1, l_flag,
3410 p_date,1 ,l_line_no,
3411 p_prv);
3412
3413 If l_write_log then
3414 FND_FILE.PUT_LINE(FND_FILE.LOG, 'OKS_BILLING_PUB.Billing_Main => After Calling OKS_BILLING_PUB.CALCULATE_BILL for sequential run');
3415 End If;
3416
3417 IF (l_retcode = 0) THEN
3418 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Billing Main is successfully completed');
3419 conc_ret_code := SUCCESS;
3420 RETCODE := 0;
3421 ELSIF (l_retcode = 1) THEN
3422 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Billing Main is successfully completed with warnings');
3423 conc_ret_code := WARNING;
3424 RETCODE := 1;
3425 ELSE
3426 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Billing Main completed with errors');
3427 conc_ret_code := ERROR;
3428 RETCODE := 2;
3429 DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_MAIN_BILLING');
3430 END IF;
3431
3432
3433 COMMIT;
3434
3435 v_cursor := DBMS_SQL.OPEN_CURSOR;
3436 /* Following line is required to avoid GSCC errors at ARU creation.
3437 Concatination of string is required to avoid run time error */
3438 --v_truncstring := 'Truncate Table oks';
3439 --v_truncstring := v_truncstring||'.oks_process_billing';
3440 v_truncstring := 'Truncate Table OKS'||'.oks_process_billing';
3441 DBMS_SQL.PARSE(v_cursor,v_truncstring,DBMS_SQL.V7);
3442 DBMS_SQL.CLOSE_CURSOR(v_cursor);
3443
3444 FND_FILE.PUT_LINE( FND_FILE.LOG,'OKS_BILLING_PUB.Billing_Main Ends');
3445
3446 EXCEPTION
3447 WHEN UTL_FILE.INVALID_PATH THEN
3448 FND_FILE.PUT_LINE (FND_FILE.LOG, 'FILE LOCATION OR NAME WAS INVALID');
3449 WHEN UTL_FILE.INVALID_MODE THEN
3450 FND_FILE.PUT_LINE (FND_FILE.LOG, 'FILE OPEN MODE STRING WAS INVALID');
3451 WHEN UTL_FILE.INVALID_FILEHANDLE THEN
3452 FND_FILE.PUT_LINE (FND_FILE.LOG, 'FILE HANDLE WAS INVALID');
3453 WHEN UTL_FILE.INVALID_OPERATION THEN
3454 FND_FILE.PUT_LINE (FND_FILE.LOG, 'FILE IS NOT OPEN FOR WRITTING');
3455 WHEN UTL_FILE.WRITE_ERROR THEN
3456 FND_FILE.PUT_LINE (FND_FILE.LOG,'OS ERROR OCCURRED DURING WRITE OPERATION');
3457
3458 END Billing_Main;
3459
3460
3461 PROCEDURE Process_Suppress_Credits
3462 (
3463 ERRBUF OUT NOCOPY VARCHAR2,
3464 RETCODE OUT NOCOPY NUMBER,
3465 P_CONTRACT_HDR_ID IN NUMBER,
3466 P_ORG_ID IN NUMBER,
3467 P_CATEGORY IN VARCHAR2
3468 )
3469 IS
3470
3471
3472 l_bclv_tbl_in OKS_bcl_PVT.bclv_tbl_type;
3473 l_bclv_tbl_out OKS_bcl_PVT.bclv_tbl_type;
3474 l_return_status VARCHAR2(10);
3475 l_msg_cnt NUMBER;
3476 l_msg_data VARCHAR2(2000);
3477
3478 l_org_id NUMBER;
3479 l_retcode NUMBER;
3480
3481 --Bug # 4928184 19-JAN-2006 maanand
3482 /* Commenting code as this is giving perf. issue for condition
3483 AND hdr.id = nvl(p_contract_hdr_id,hdr.id)
3484 Hence, breaking this cursor into two cursors depeding on condition
3485 process_cur into process_cur_hdr_id and process_cur
3486 */
3487
3488 /*
3489 CURSOR process_cur(p_contract_hdr_id IN NUMBER,
3490 p_category IN VARCHAR2,
3491 p_org_id IN NUMBER) is
3492 SELECT bcl.cle_id bcl_cle_id,
3493 bsl.cle_id bsl_cle_id,
3494 abs(bsl.amount) bsl_amount,
3495 line.lse_id bcl_lse_id
3496 FROM okc_k_headers_b hdr,
3497 oks_bill_sub_lines bsl,
3498 oks_bill_cont_lines bcl,
3499 okc_k_lines_b line
3500 WHERE line.id = bcl.cle_id
3501 AND hdr.id = line.dnz_chr_id
3502 AND hdr.id = nvl(p_contract_hdr_id,hdr.id) --Full Table Scan due to nvl condition
3503 AND hdr.scs_code = nvl(p_category,hdr.scs_code)
3504 --AND hdr.authoring_org_id = nvl(p_org_id,hdr.authoring_org_id)
3505 AND hdr.org_id = nvl(p_org_id,hdr.org_id)
3506 AND bcl.cle_id = line.id
3507 AND bsl.bcl_id = bcl.id
3508 AND bcl.bill_action = 'TR'
3509 AND bcl.btn_id = -44;
3510 */
3511
3512 --Cursor to consider when p_contract_hdr_id is not null
3513
3514 CURSOR process_cur_hdr_id (p_contract_hdr_id IN NUMBER,
3515 p_category IN VARCHAR2,
3516 p_org_id IN NUMBER) is
3517
3518 SELECT bcl.cle_id bcl_cle_id,
3519 bsl.cle_id bsl_cle_id,
3520 abs(bsl.amount) bsl_amount,
3521 line.lse_id bcl_lse_id
3522 FROM okc_k_headers_b hdr,
3523 oks_bill_sub_lines bsl,
3524 oks_bill_cont_lines bcl,
3525 okc_k_lines_b line
3526 WHERE line.id = bcl.cle_id
3527 AND hdr.id = line.dnz_chr_id
3528 AND hdr.id = p_contract_hdr_id
3529 AND hdr.scs_code = nvl(p_category,hdr.scs_code)
3530 AND hdr.org_id = nvl(p_org_id,hdr.org_id)
3531 AND bcl.cle_id = line.id
3532 AND bsl.bcl_id = bcl.id
3533 AND bcl.bill_action = 'TR'
3534 AND bcl.btn_id = -44;
3535
3536
3537 --Cursor to consider when p_contract_hdr_id is NULL
3538
3539 CURSOR process_cur (p_category IN VARCHAR2,
3540 p_org_id IN NUMBER) is
3541
3542 SELECT bcl.cle_id bcl_cle_id,
3543 bsl.cle_id bsl_cle_id,
3544 abs(bsl.amount) bsl_amount,
3545 line.lse_id bcl_lse_id
3546 FROM okc_k_headers_b hdr,
3547 oks_bill_sub_lines bsl,
3548 oks_bill_cont_lines bcl,
3549 okc_k_lines_b line
3550 WHERE line.id = bcl.cle_id
3551 AND hdr.id = line.dnz_chr_id
3552 AND hdr.scs_code = nvl(p_category,hdr.scs_code)
3553 AND hdr.org_id = nvl(p_org_id,hdr.org_id)
3554 AND bcl.cle_id = line.id
3555 AND bsl.bcl_id = bcl.id
3556 AND bcl.bill_action = 'TR'
3557 AND bcl.btn_id = -44;
3558
3559
3560
3561 l_billrep_tbl OKS_BILL_REC_PUB.bill_report_tbl_type;
3562 l_billrep_tbl_idx NUMBER;
3563 l_billrep_err_tbl OKS_BILL_REC_PUB.billrep_error_tbl_type;
3564 l_billrep_errtbl_idx NUMBER;
3565
3566
3567 BEGIN
3568
3569 l_retcode := 0;
3570
3571 ----DBMS_TRANSACTION.SAVEPOINT('BEFORE_TRANSACTION');
3572
3573 l_org_id := p_org_id;
3574
3575 --mchoudha fixed bug#4729936
3576 --IF (nvl(fnd_profile.value('OKC_VIEW_K_BY_ORG'),'N') = 'Y' ) THEN
3577 -- l_org_id := fnd_profile.value('ORG_ID');
3578 --END IF;
3579
3580 --Bug # 4928184 19-JAN-2006 maanand
3581
3582 IF (p_contract_hdr_id is not null) THEN
3583
3584 FOR cur in process_cur_hdr_id(p_contract_hdr_id ,
3585 p_category ,
3586 l_org_id )
3587 LOOP
3588
3589 /*Update subline info*/
3590 IF (cur.bcl_lse_id <> 46) THEN
3591 UPDATE oks_k_lines_b
3592 SET credit_amount = nvl(credit_amount,0) + nvl(cur.bsl_amount,0) ,
3593 suppressed_credit = nvl(suppressed_credit,0) - nvl(cur.bsl_amount,0)
3594 WHERE cle_id = cur.bsl_cle_id;
3595 END IF;
3596
3597 /*Update topline info*/
3598 UPDATE oks_k_lines_b
3599 SET credit_amount = nvl(credit_amount,0) + nvl(cur.bsl_amount,0) ,
3600 suppressed_credit = nvl(suppressed_credit,0) - nvl(cur.bsl_amount,0)
3601 WHERE cle_id = cur.bcl_cle_id;
3602
3603 END LOOP;
3604
3605 /*
3606 BTN_ID is updated to null for Each record which qualifies the user inputs.
3607 Order management orginated line can have btn_id = -44.
3608 To avoid process these records bill_action = 'TR' condition is added
3609 in below where clause
3610 */
3611
3612 UPDATE oks_bill_cont_lines bcl
3613 SET bcl.btn_id = NULL
3614 WHERE bcl.btn_id = -44
3615 AND bcl.bill_Action = 'TR'
3616 AND EXISTS
3617 (SELECT 1 from okc_k_headers_b hdr,
3618 okc_k_lines_b line
3619 WHERE line.id = bcl.cle_id
3620 AND hdr.id = line.dnz_chr_id
3621 AND hdr.id = p_contract_hdr_id
3622 AND hdr.scs_code = nvl(p_category,hdr.scs_code)
3623 AND hdr.org_id = nvl(l_org_id,hdr.org_id));
3624
3625
3626 ELSE --p_contract_hdr_id is NULL
3627
3628 FOR cur in process_cur(p_category ,
3629 l_org_id )
3630 LOOP
3631
3632 /*Update subline info*/
3633 IF (cur.bcl_lse_id <> 46) THEN
3634 UPDATE oks_k_lines_b
3635 SET credit_amount = nvl(credit_amount,0) + nvl(cur.bsl_amount,0) ,
3636 suppressed_credit = nvl(suppressed_credit,0) - nvl(cur.bsl_amount,0)
3637 WHERE cle_id = cur.bsl_cle_id;
3638 END IF;
3639
3640 /*Update topline info*/
3641 UPDATE oks_k_lines_b
3642 SET credit_amount = nvl(credit_amount,0) + nvl(cur.bsl_amount,0) ,
3643 suppressed_credit = nvl(suppressed_credit,0) - nvl(cur.bsl_amount,0)
3644 WHERE cle_id = cur.bcl_cle_id;
3645
3646 END LOOP;
3647
3648 /*
3649 BTN_ID is updated to null for Each record which qualifies the user inputs.
3650 Order management orginated line can have btn_id = -44.
3651 To avoid process these records bill_action = 'TR' condition is added
3652 in below where clause
3653 */
3654
3655 UPDATE oks_bill_cont_lines bcl
3656 SET bcl.btn_id = NULL
3657 WHERE bcl.btn_id = -44
3658 AND bcl.bill_Action = 'TR'
3659 AND EXISTS
3660 (SELECT 1 from okc_k_headers_b hdr,
3661 okc_k_lines_b line
3662 WHERE line.id = bcl.cle_id
3663 AND hdr.id = line.dnz_chr_id
3664 AND hdr.scs_code = nvl(p_category,hdr.scs_code)
3665 AND hdr.org_id = nvl(l_org_id,hdr.org_id));
3666
3667 END IF; --p_contract_hdr_id is not null
3668
3669
3670 --Bug # 4928184 19-JAN-2006 maanand
3671 --Commented this code
3672 /*
3673 --BTN_ID is updated to null for Each record which qualifies the user inputs.
3674 --Order management orginated line can have btn_id = -44.
3675 --To avoid process these records bill_action = 'TR' condition is added
3676 --in below where clause
3677
3678
3679 UPDATE oks_bill_cont_lines bcl
3680 SET bcl.btn_id = NULL
3681 WHERE bcl.btn_id = -44
3682 AND bcl.bill_Action = 'TR'
3683 AND EXISTS
3684 (SELECT 1 from okc_k_headers_b hdr,
3685 okc_k_lines_b line
3686 WHERE line.id = bcl.cle_id
3687 AND hdr.id = line.dnz_chr_id
3688 AND hdr.id = nvl(p_contract_hdr_id,hdr.id)
3689 AND hdr.scs_code = nvl(p_category,hdr.scs_code)
3690 --AND hdr.authoring_org_id = nvl(l_org_id,hdr.authoring_org_id));
3691 AND hdr.org_id = nvl(l_org_id,hdr.org_id));
3692
3693
3694
3695 --Once Btn_id is update as null. Calling AR Feeder to process termination
3696 --records. These records are now processed as normal termination records
3697 --in AR Feeder. Calling AR Feeder with P_Prv =3 to process remaing
3698 --termination records
3699 */
3700
3701 --END Bug # 4928184 19-JAN-2006 maanand
3702
3703 OKS_ARFEEDER_PUB.Get_REC_FEEDER
3704 (
3705 x_return_status => l_return_status,
3706 x_msg_count => l_msg_cnt,
3707 x_msg_data => l_msg_data,
3708 p_flag => 2,
3709 p_called_from => 1,
3710 p_date => trunc(sysdate),
3711 p_cle_id => NULL,
3712 p_prv => 3, -- to interface termination records
3713 p_billrep_tbl => l_billrep_tbl,
3714 p_billrep_tbl_idx => l_billrep_tbl_idx,
3715 p_billrep_err_tbl => l_billrep_err_tbl,
3716 p_billrep_err_tbl_idx => l_billrep_errtbl_idx
3717 ) ;
3718
3719 IF (l_return_status <> 'S') THEN
3720 OKS_BILL_REC_PUB.GET_MESSAGE(
3721 l_msg_cnt => l_msg_cnt,
3722 l_msg_data => l_msg_data);
3723 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error: Failed in AR FEEDER');
3724 ---DBMS_TRANSACTION.ROLLBACK_SAVEPOINT('BEFORE_TRANSACTION');
3725 l_retcode := 1;
3726 END IF;
3727
3728 IF (l_retcode = 0) THEN
3729 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Supress credit is successfully completed');
3730 conc_ret_code := SUCCESS;
3731 RETCODE := 0;
3732 END IF;
3733
3734 COMMIT;
3735
3736 EXCEPTION
3737 WHEN OTHERS THEN
3738 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Supress credit completed with Errors');
3739 conc_ret_code := ERROR;
3740 RETCODE := 2;
3741 OKC_API.set_message(G_APP_NAME, G_UNEXPECTED_ERROR,
3742 G_SQLCODE_TOKEN,SQLCODE,G_SQLERRM_TOKEN,SQLERRM);
3743 End Process_Suppress_Credits;
3744
3745 End OKS_BILLING_PUB;