[Home] [Help]
PACKAGE BODY: APPS.OKS_USAGE_SETTLE_PUB
Source
1 PACKAGE BODY OKS_USAGE_SETTLE_PUB as
2 /* $Header: OKSPSTLB.pls 120.7 2006/09/13 21:55:34 hvaladip noship $ */
3
4 PROCEDURE Calculate_Settlement
5 (
6 ERRBUF OUT NOCOPY VARCHAR2,
7 RETCODE OUT NOCOPY NUMBER,
8 P_DNZ_CHR_ID IN NUMBER
9 )
10 IS
11 CURSOR line_cur (p_chr_id IN NUMBER)
12 IS
13 SELECT line.id, rline.settlement_interval, rline.usage_type,rline.usage_period, line.line_number --Bug 5284103
14 FROM oks_k_lines_b rline,
15 okc_k_lines_b line
16 WHERE line.dnz_chr_id = p_chr_id
17 AND rline.cle_id = line.id
18 AND line.lse_id = 12
19 AND rline.usage_type = 'VRT'
20 AND not exists (select 1 from oks_bill_cont_lines bcl
21 where line.id = bcl.cle_id
22 and bcl.bill_action = 'SRI');
23
24 CURSOR subline_cur (p_cle_id IN NUMBER)
25 IS
26 SELECT line.id,rline.minimum_quantity,
27 line.start_date,line.end_date
28 FROM OKC_K_LINES_B line,
29 OKS_K_LINES_B rline
30 WHERE line.cle_id = p_cle_id
31 AND line.lse_id = 13
32 AND rline.cle_id = line.id;
33
34 CURSOR max_date_billed(p_cle_id IN NUMBER)
35 IS
36 SELECT max(date_end),max(date_start)
37 FROM oks_level_elements
38 WHERE cle_id = p_cle_id;
39
40 CURSOR bsl_billed(p_cle_id IN NUMBER,p_date_billed_From IN DATE,
41 p_date_billed_to IN DATE)
42 IS
43 SELECT id
44 FROM oks_bill_sub_lines
45 WHERE cle_id = p_cle_id
46 AND trunc(date_billed_From)= trunc(p_date_billed_From)
47 AND trunc(date_billed_to) = trunc(p_date_billed_to) ;
48
49 CURSOR l_actual_billed_csr(p_cle_id IN NUMBER) Is
50 SELECT NVL(sum(NVL(Result,0)),0) qty,
51 sum(line.amount) amt,
52 max(line.date_billed_to) max_bill_to
53 FROM oks_bill_sub_line_dtls ldtl
54 ,oks_bill_sub_lines line
55 WHERE line.cle_id = p_cle_id
56 AND ldtl.bsl_id = line.id;
57
58
59 Cursor bsl_billed_period (p_cle_id IN NUMBER)
60 IS
61 SELECT bsl.id,
62 bsl.date_billed_from,
63 bsl.date_billed_to,
64 bsl.amount,
65 bsd.result
66 FROM oks_bill_cont_lines bcl,
67 oks_bill_sub_lines bsl,
68 oks_bill_sub_line_dtls bsd
69 WHERE bcl.id = bsl.bcl_id
70 AND bcl.bill_action = 'RI'
71 AND bsl.cle_id = p_cle_id
72 AND bsd.bsl_id = bsl.id
73 ORDER by bsl.date_billed_to desc;
74
75 CURSOR count_bsl_csr(p_cle_id IN NUMBER)
76 IS
77 SELECT date_billed_from,date_billed_to
78 FROM oks_bill_sub_lines
79 WHERE cle_id = p_cle_id;
80
81 CURSOR l_uom_csr IS
82 SELECT uom_code
83 FROM Okc_time_code_units_v
84 WHERE tce_code = 'DAY'
85 AND quantity = 1;
86
87 CURSOR l_inv_item_csr(p_cle_id IN NUMBER) Is
88 SELECT mtl.primary_uom_code
89 FROM Okc_K_items Item
90 ,mtl_system_items_b mtl --Okx_system_items_v mtl
91 ,okc_k_headers_b hdr
92 ,okc_k_lines_b line
93 WHERE item.cle_id = line.id --p_cle_id
94 AND line.id = p_cle_id
95 AND line.dnz_chr_id = hdr.id
96 --AND mtl.id1 = item.object1_id1
97 AND mtl.inventory_item_id = item.object1_id1
98 AND mtl.organization_id = hdr.inv_organization_id;
99
100
101 --Bug# 5284103
102
103 Cursor get_counter_qty(p_id Number, p_lock_read number) IS
104 select value_timestamp from cs_counter_values
105 where counter_id = p_id
106 and counter_reading = p_lock_read;
107
108 CURSOR get_bp_lookup_meaning_csr Is
109 SELECT Fnd.Meaning
110 FROM FND_LOOKUPS Fnd
111 WHERE Fnd.Lookup_Type = 'OKS_SETTLEMENT_INTERVAL'
112 AND Fnd.Lookup_Code = 'BP';
113
114 l_bp_fnd_meaning varchar2(30);
115 l_settlement_interval varchar2(30);
116
117 --End Bug# 5284103
118
119 l_counter_reading_lock_rec csi_ctr_datastructures_pub.ctr_reading_lock_rec;
120 l_return_status VARCHAR2(1) := 'S';
121 l_qty NUMBER;
122 l_temp NUMBER;
123 l_billed_qty NUMBER;
124 l_billed_amt NUMBER;
125 l_max_bill_to DATE;
126 l_credit_amount NUMBER;
127 l_term_amount NUMBER;
128 l_credit_qty NUMBER;
129 l_term_qty NUMBER;
130 l_id NUMBER;
131 l_counter_value NUMBER;
132 l_counter_date DATE;
133 l_period_start_date DATE;
134 l_period_end_date DATE;
135 l_start_reading NUMBER;
136 l_end_reading NUMBER;
137 l_base_reading NUMBER;
138 l_counter_value_id NUMBER;
139 l_counter_group_id NUMBER;
140 l_counter_id NUMBER;
141 l_minimum NUMBER;
142 l_bsl_count NUMBER;
143 l_uom_code VARCHAR2(20);
144 l_usage_type VARCHAR2(20);
145 l_time_uom VARCHAR2(20);
146 l_primary_uom_code VARCHAR2(20);
147 l_billrep_tbl OKS_BILL_REC_PUB.bill_report_tbl_type;
148 l_billrep_tbl_idx NUMBER := 0;
149 l_billrep_err_tbl OKS_BILL_REC_PUB.billrep_error_tbl_type;
150 l_billrep_errtbl_idx NUMBER;
151
152
153
154 l_lock_id NUMBER;
155 l_msg_cnt NUMBER;
156 l_msg_data VARCHAR2(2000);
157
158
159 l_line_rec OKS_QP_PKG.Input_details ;
160 l_price_rec OKS_QP_PKG.Price_Details ;
161 l_modifier_details QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
162 l_price_break_details OKS_QP_PKG.G_PRICE_BREAK_TBL_TYPE;
163
164 -------------------------------------------------------------------------
165 -- Begin partial period computation logic
166 -- Developer Mani Choudhary
167 -- Date 30-MAY-2005
168 -- local variables for partal periods
169 -------------------------------------------------------------------------
170 l_hdr_id NUMBER;
171 l_price_uom OKS_K_HEADERS_B.PRICE_UOM%TYPE;
172 l_period_start OKS_K_HEADERS_B.PERIOD_START%TYPE;
173 l_period_type OKS_K_HEADERS_B.PERIOD_TYPE%TYPE;
174 -------------------------------------------------------------------------
175
176 l_ignore_settlement_msg varchar2(1000); --Bug# 5284103
177
178 BEGIN
179 -------------------------------------------------------------------------
180 -- Begin partial period computation logic
181 -- Developer Mani Choudhary
182 -- Date 30-MAY-2005
183 -- Call oks_renew_util_pub.get_period_defaults to fetch period start and period type
184 -------------------------------------------------------------------------
185 IF P_DNZ_CHR_ID IS NOT NULL THEN
186 OKS_RENEW_UTIL_PUB.Get_Period_Defaults
187 (
188 p_hdr_id => P_DNZ_CHR_ID,
189 p_org_id => NULL,
190 x_period_start => l_period_start,
191 x_period_type => l_period_type,
192 x_price_uom => l_price_uom,
193 x_return_status => l_return_status);
194 END IF;
195 --For usage , the period start should be SERVICE
196 l_period_start := 'SERVICE';
197 FOR line_Rec in line_cur(p_dnz_chr_id)
198 LOOP
199 --Bug# 5284103
200 IF (line_Rec.settlement_interval = 'EU') THEN
201
202 OPEN l_inv_item_csr(line_Rec.id);
203 FETCH l_inv_item_csr into l_primary_uom_code;
204 CLOSE l_inv_item_csr;
205
206 OPEN l_uom_csr;
207 FETCH l_uom_csr into l_time_uom;
208 CLOSE l_uom_csr;
209
210 /*Run settlement for each subline */
211 FOR subline_Rec in subline_cur(line_rec.id)
212 LOOP
213
214
215 OPEN max_date_billed(subline_Rec.id);
216 FETCH max_date_billed INTO l_period_end_date,l_period_start_date;
217 CLOSE max_date_billed;
218
219 /* Check if the subline is fully billed */
220 OPEN bsl_billed(subline_Rec.id,l_period_start_date,l_period_end_date);
221 FETCH bsl_billed INTO l_id;
222 IF (bsl_billed%FOUND) THEN
223 OKS_BILL_REC_PUB.Counter_Values
224 (
225 --P_CALLEDFROM => 3,
226 P_CALLEDFROM => 4, --refers to Settlement Bug# 5235116
227 P_START_DATE => subline_rec.start_date, --l_period_start_date,
228 P_END_DATE => subline_rec.end_date , --l_period_end_date,
229 P_CLE_ID => subline_rec.id,
230 P_USAGE_TYPE => line_rec.usage_type , --l_usage_type,
231 X_VALUE => l_qty,
232 X_COUNTER_VALUE => l_counter_value,
233 X_COUNTER_DATE => l_counter_date,
234 X_UOM_CODE => l_uom_code,
235 X_END_READING => l_end_reading,
236 X_START_READING => l_start_reading,
237 X_BASE_READING => l_base_reading,
238 X_COUNTER_VALUE_ID => l_counter_value_id,
239 X_COUNTER_GROUP_ID => l_counter_group_id,
240 X_COUNTER_ID => l_counter_id,
241 X_RETURN_STATUS => l_return_status
242 );
243
244 OPEN l_actual_billed_csr(subline_Rec.id);
245 FETCH l_actual_billed_csr INTO l_billed_qty, l_billed_amt, l_max_bill_to;
246 CLOSE l_actual_billed_csr;
247
248
249 IF (l_qty < 0) THEN
250 l_minimum := 0;
251 /*
252 OPEN count_bsl_csr(subline_Rec.id);
253 FETCH count_bsl_csr into l_bsl_count;
254 CLOSE count_bsl_csr;
255
256 l_minimum := l_bsl_count * subline_Rec.minimum_quantity;
257 */
258
259 FOR bsl_rec in count_bsl_csr(subline_Rec.id)
260 LOOP
261 /* This Loop is required to calc minimum Qty for entire subline effectivity.
262 Loop is required to get minimum qty per billed period which can be of different time length
263 */
264 -------------------------------------------------------------------------
265 -- Begin partial period computation logic
266 -- Developer Mani Choudhary
267 -- Date 13-JUN-2005
268 -- call oks_bill_rec_pub.Get_prorated_Usage_Qty to get the prorated usage
269 -------------------------------------------------------------------------
270 IF l_period_type IS NOT NULL AND
271 l_period_start IS NOT NULL
272 THEN
273 l_temp := OKS_BILL_REC_PUB.Get_Prorated_Usage_Qty
274 (
275 p_start_date => bsl_rec.date_billed_from,
276 p_end_date => bsl_rec.date_billed_to,
277 p_qty => subline_Rec.minimum_quantity,
278 p_usage_uom => line_rec.usage_period,
279 p_billing_uom => l_time_uom,
280 p_period_type => l_period_type
281 );
282 l_minimum := l_minimum + Round(l_temp,0);
283 ELSE
284 --Existing logic
285 l_temp := OKS_TIME_MEASURES_PUB.GET_TARGET_QTY
286 (
287 p_start_date => bsl_rec.date_billed_from,
288 p_source_qty => 1,
289 p_source_uom => line_rec.usage_period,
290 p_target_uom => l_time_uom,
291 p_round_dec => 0
292 );
293
294 l_minimum := l_minimum + Round((trunc(bsl_rec.date_billed_to) - trunc(bsl_rec.date_billed_from) + 1)
295 * (subline_Rec.minimum_quantity /l_temp) ,0) ;
296 END IF; --period start and period type are not null
297 END LOOP;
298
299 IF (l_counter_value < l_minimum) THEN
300 l_qty := -1 * (l_billed_qty - l_minimum);
301 END IF;
302 END IF;
303
304 l_line_rec.line_id := line_Rec.id;
305 l_line_rec.intent := 'USG';
306 l_line_rec.usage_qty := abs(l_qty ); -- qty
307 l_line_rec.usage_uom_code := l_primary_uom_code;
308 --l_line_rec.bsl_id := bsl_price_rec.bsl_id;
309 l_line_rec.subline_id := subline_Rec.id;
310
311
312 /*Pricing API to calculate amount */
313 OKS_QP_PKG.CALC_PRICE
314 (
315 P_DETAIL_REC => l_line_rec,
316 X_PRICE_DETAILS => l_price_rec,
317 X_MODIFIER_DETAILS => l_modifier_details,
318 X_PRICE_BREAK_DETAILS => l_price_break_details,
319 X_RETURN_STATUS => l_return_status,
320 X_MSG_COUNT => l_msg_cnt,
321 X_MSG_DATA => l_msg_data
322 );
323
324 /* If Quantity already billed is greater than actual , then issue credit */
325 IF (l_qty < 0 ) THEN -- l_billed_qty) THEN
326 --issue credit
327 l_credit_amount := l_price_rec.prod_Ext_amount;
328 l_credit_qty := abs(l_qty);
329 FOR cur in bsl_billed_period(subline_Rec.id)
330 LOOP
331 IF (l_credit_amount <= 0) THEN
332 EXIT;
333 END IF;
334
335 IF (l_credit_amount >= cur.amount) THEN
336 l_term_amount := cur.amount;
337 l_term_qty := cur.result;
338 ELSE
339 l_term_amount := l_credit_amount;
340 l_term_qty := l_credit_qty;
341 END IF;
342
343 OKS_BILL_REC_PUB.Create_trx_records(
344 P_CALLED_FROM => 3 ,
345 P_TOP_LINE_ID => line_rec.id,
346 P_COV_LINE_ID => subline_Rec.id,
347 P_DATE_FROM => cur.date_billed_from ,
348 P_DATE_TO => cur.date_billed_to,
349 P_AMOUNT => l_term_qty,
350 P_OVERRIDE_AMOUNT => NULL,
351 --P_EXISTING_CREDIT => NULL,
352 P_SUPPRESS_CREDIT => 'N',
353 P_CON_TERMINATE_AMOUNT => l_term_amount,
354 P_BILL_ACTION => 'STR',
355 X_RETURN_STATUS => l_return_status
356 );
357 l_credit_amount := l_credit_amount - cur.amount;
358 l_credit_qty := l_credit_qty - cur.result;
359 END LOOP;
360
361 ELSIF (l_qty > 0) THEN
362 /* If Quantity already billed is lesser than actual , then issue invoice */
363 --issue invoice
364 OKS_BILL_REC_PUB.Create_trx_records(
365 P_CALLED_FROM => 3,
366 P_TOP_LINE_ID => line_rec.id,
367 P_COV_LINE_ID => subline_Rec.id,
368 P_DATE_FROM => l_period_start_date ,
369 P_DATE_TO => l_period_end_date,
370 P_AMOUNT => l_qty,
371 P_OVERRIDE_AMOUNT => NULL,
372 --P_EXISTING_CREDIT => NULL,
373 P_SUPPRESS_CREDIT => 'N',
374 P_CON_TERMINATE_AMOUNT => l_price_rec.PROD_EXT_AMOUNT,
375 P_BILL_ACTION => 'SRI',
376 X_RETURN_STATUS => l_return_status
377 );
378
379 END IF;
380
381 END IF;
382 CLOSE bsl_billed ;
383
384 open get_counter_qty(l_counter_id,l_end_reading);
385 fetch get_counter_qty into l_max_bill_to;
386 close get_counter_qty;
387
388 l_counter_reading_lock_rec.reading_lock_date := l_max_bill_to;
389 l_counter_reading_lock_rec.counter_id := l_counter_id;
390 l_counter_reading_lock_rec.source_line_ref_id := subline_Rec.id;
391 l_counter_reading_lock_rec.source_line_ref := 'CONTRACT_LINE';
392
393 Csi_Counter_Pub.create_reading_lock
394 (
395 p_api_version => 1.0,
396 p_commit => 'F',
397 p_init_msg_list => 'T',
398 p_validation_level => 100,
399 p_ctr_reading_lock_rec => l_counter_reading_lock_rec,
400 x_return_status => l_return_status,
401 x_msg_count => l_msg_cnt,
402 x_msg_data => l_msg_data,
403 x_reading_lock_id => l_lock_id
404 );
405
406
407 END LOOP;
408
409 ELSE --(line_Rec.settlement_interval = 'EU')
410 --Bug# 5284103
411 --Print message in output and log file
412 If (line_Rec.settlement_interval = 'BP') Then
413 Open get_bp_lookup_meaning_csr;
414 Fetch get_bp_lookup_meaning_csr into l_bp_fnd_meaning;
415 Close get_bp_lookup_meaning_csr;
416
417 l_settlement_interval := l_bp_fnd_meaning;
418 Else
419 l_settlement_interval := 'NULL';
420 End If;
421
422 FND_MESSAGE.CLEAR;
423 FND_MESSAGE.SET_NAME('OKS','OKS_IGNORE_SETTLEMENT');
424 fnd_message.set_token('LINE_NO', line_Rec.line_number);
425 fnd_message.set_token('SETLLEMENT_INTERVAL', l_settlement_interval);
426 l_ignore_settlement_msg := FND_MESSAGE.GET;
427
428 FND_FILE.PUT_LINE(FND_FILE.OUTPUT , l_ignore_settlement_msg);
429 FND_FILE.PUT_LINE(FND_FILE.LOG, l_ignore_settlement_msg);
430
431 --Line <line_number> is ignored since settlement interval is <Null/ Billing Period>
432
433 END IF; -- (line_Rec.settlement_interval = 'EU')
434 --End Bug# 5284103
435
436 END LOOP;
437
438
439 /* Interface records to AR */
440 OKS_ARFEEDER_PUB.Get_REC_FEEDER
441 (
442 X_RETURN_STATUS => l_return_status,
443 X_MSG_COUNT => l_msg_cnt,
444 X_MSG_DATA => l_msg_data,
445 P_FLAG => 0, -- checkout
446 P_CALLED_FROM => 1,
447 P_DATE => sysdate,
448 P_CLE_ID => NULL,
449 P_PRV => 3,
450 p_billrep_tbl => l_billrep_tbl,
451 p_billrep_tbl_idx => l_billrep_tbl_idx,
452 p_billrep_err_tbl => l_billrep_err_tbl,
453 p_billrep_err_tbl_idx => l_billrep_errtbl_idx
454 ) ;
455
456 COMMIT;
457 END Calculate_Settlement;
458
459 END OKS_USAGE_SETTLE_PUB;