[Home] [Help]
PACKAGE BODY: APPS.OKS_AVG_SET_PUB
Source
1 Package Body OKS_AVG_SET_PUB AS
2 /* $Header: OKSPAVGB.pls 120.4 2006/06/20 01:43:20 hvaladip noship $ */
3
4 ------------------------------------------------------------------------
5 -- FUNCTION avg_api
6 ------------------------------------------------------------------------
7 PROCEDURE average_api(
8 p_called_from IN NUMBER,
9 p_cle_id IN NUMBER,
10 p_hdr_id IN NUMBER,
11 p_avg_interval IN NUMBER,
12 X_return_status IN OUT NOCOPY VARCHAR2)
13
14 IS
15 Cursor get_curr_csr (p_hdr_id IN NUMBER)
16 IS
17 SELECT currency_code FROM okc_k_headers_b
18 WHERE id = p_hdr_id;
19
20 Cursor get_avg_date (p_cle_id IN NUMBER)
21 IS
22 SELECT trunc(date_billed_from) date_billed_from FROM oks_bill_cont_lines
23 WHERE cle_id = p_cle_id
24 order by date_billed_to desc;
25
26 Cursor sub_line_csr (p_cle_id IN NUMBER,
27 p_date IN DATE)
28 IS
29 SELECT sum(bsd.result) qty_billed,
30 min(bsl.date_billed_from) date_billed_from,
31 max(bsl.date_billed_to) date_billed_to,
32 sum(bsl.amount) amt_billed,
33 bsl.cle_id
34 FROM
35 oks_bill_sub_line_dtls bsd,
36 oks_bill_sub_lines bsl
37 WHERE bsd.bsl_id = bsl.id
38 AND bsl.bcl_id in
39 (select bcl.id from oks_bill_cont_lines bcl
40 where bcl.averaged_yn is null
41 AND trunc(bcl.date_billed_from) >= p_date
42 and bcl.cle_id = p_cle_id )
43 group by bsl.cle_id;
44
45 Cursor process_avg_csr (p_cle_id IN NUMBER,
46 p_date_billed_from IN DATE,
47 p_date_billed_to IN DATE)
48 IS
49 SELECT id bcl_id
50 ,Date_Billed_from Date_Billed_from
51 ,Date_Billed_to Date_Billed_to
52 FROM oks_bill_cont_lines
53 Where cle_id = p_cle_id
54 AND trunc(date_billed_from ) >= trunc(p_date_billed_from)
55 AND trunc(date_billed_to ) <= trunc(p_date_billed_to)
56 AND averaged_yn is NULL;
57
58 Cursor subline_credit_csr(p_cle_id IN NUMBER)
59 IS
60 SELECT bsd.unit_of_measure uom_code,
61 bsd.amcv_yn amcv_yn,
62 bsl.date_billed_from date_from,
63 bsl.date_billed_to date_to
64 FROM oks_bill_sub_lines bsl,
65 oks_bill_sub_line_dtls bsd
66 WHERE bsl.cle_id = p_cle_id
67 AND bsl.id = bsd.bsl_id
68 order by bsl.date_billed_to desc;
69
70 l_bill_qty NUMBER;
71 l_avg_amount NUMBER;
72 l_temp_average NUMBER;
73 l_average NUMBER;
74 l_term_amount NUMBER;
75 l_total_credit NUMBER := 0;
76 l_msg_count NUMBER;
77 l_msg_data VARCHAR2(2000);
78 l_curr_code VARCHAR2(15);
79 l_return_status VARCHAR2(1) := 'S';
80 l_called_from CONSTANT NUMBER := 1;
81
82 l_process_rec process_avg_csr%ROWTYPE;
83
84 l_cov_tbl OKS_BILL_REC_PUB.COVERED_TBL;
85 l_line_rec OKS_QP_PKG.INPUT_DETAILS ;
86 l_price_rec OKS_QP_PKG.PRICE_dETAILS ;
87 l_modifier_details QP_PREQ_GRP.LINE_DETAIL_TBL_TYPE;
88 l_price_break_details OKS_QP_PKG.G_PRICE_BREAK_TBL_TYPE;
89 l_billrep_tbl OKS_BILL_REC_PUB.bill_report_tbl_type;
90 l_billrep_tbl_idx NUMBER := 0;
91 l_billrep_err_tbl OKS_BILL_REC_PUB.billrep_error_tbl_type;
92 l_billrep_errtbl_idx NUMBER;
93 l_bcl_id NUMBER;
94
95 l_term_tbl OKS_BILL_REC_PUB.TERMINATE_TBL;
96
97 l_uom_code varchar2(30);
98 l_amcv_yn varchar2(3);
99 l_date_from date;
100 l_date_to date;
101 l_date_to_average date;
102 j number;
103
104
105
106 BEGIN
107
108 j := 1;
109
110 FOR get_avg_csr in get_avg_date(p_cle_id)
111 LOOP
112 if j = p_avg_interval Then
113 l_date_to_average := get_avg_csr.date_billed_from;
114 exit;
115 End if;
116 j := j + 1;
117 END LOOP;
118
119
120
121 /* Check if there is sufficient periods to process */
122 OPEN get_curr_csr (p_hdr_id);
123 FETCH get_curr_csr into l_curr_code;
124 CLOSE get_curr_csr;
125
126 FND_FILE.PUT_LINE(FND_FILE.LOG,'cle_id '||p_cle_id);
127 FND_FILE.PUT_LINE(FND_FILE.LOG,' avg '||p_avg_interval);
128 FND_FILE.PUT_LINE(FND_FILE.LOG,'Dt to be averaged from '||l_date_to_average);
129
130 /*While loop is to process multiple slots. It will take care
131 of past periods where averaging was not run
132 */
133 FOR sub_line_rec in sub_line_csr(p_cle_id, l_date_to_average)
134 LOOP
135
136 /* Get total qty billed for line in averaging interval that is
137 being processed
138 */
139 l_average := round(sub_line_rec.qty_billed / p_avg_interval,0);
140 l_avg_amount := 0;
141
142
143 OPEN subline_credit_csr(sub_line_rec.cle_id);
144 FETCH subline_credit_csr into l_uom_code, l_amcv_yn, l_date_from, l_date_to;
145 CLOSE subline_credit_csr;
146
147 l_line_rec.line_id := p_cle_id;
148 l_line_rec.intent := 'USG';
149 l_line_rec.usage_qty := l_average;
150 l_line_rec.usage_uom_code := l_uom_code;
151 l_line_rec.bsl_id := -99; --- no price breaks for average
152
153 l_avg_amount := 0;
154
155 OKS_QP_PKG.CALC_PRICE
156 (
157 P_DETAIL_REC => l_line_rec,
158 X_PRICE_DETAILS => l_price_rec,
159 X_MODIFIER_DETAILS => l_modifier_details,
160 X_PRICE_BREAK_DETAILS => l_price_break_details,
161 X_RETURN_STATUS => l_return_status,
162 X_MSG_COUNT => l_msg_count,
163 X_MSG_DATA => l_msg_data
164 );
165
166 FND_FILE.PUT_LINE(FND_FILE.LOG,'pricing engine amt '||l_price_rec.PROD_EXT_AMOUNT);
167
168 l_avg_amount := l_price_rec.PROD_EXT_AMOUNT * p_avg_interval;
169
170 FND_FILE.PUT_LINE(FND_FILE.LOG,'averaged amt '||l_avg_amount);
171 FND_FILE.PUT_LINE(FND_FILE.LOG,'Amount billed '||sub_line_rec.amt_billed);
172
173
174 IF NVL(l_avg_amount,0) < sub_line_rec.amt_billed Then
175 l_avg_amount := sub_line_rec.amt_billed - l_avg_amount;
176 l_total_credit := l_total_credit + l_avg_amount;
177 l_avg_amount := l_avg_amount * -1;
178
179 l_cov_tbl(1).fixed := 0;
180 l_cov_tbl(1).result := l_average;
181 l_cov_tbl(1).actual := l_average;
182 l_cov_tbl(1).estimated_quantity := 0;
183 l_cov_tbl(1).sign := 1;
184 l_cov_tbl(1).average := 0;
185 l_cov_tbl(1).unit_of_measure := l_uom_code;
186 l_cov_tbl(1).amount := l_avg_amount;
187 l_cov_tbl(1).amcv_yn := l_amcv_yn;
188 l_cov_tbl(1).id := sub_line_rec.cle_id;
189 l_cov_tbl(1).date_billed_from := l_date_from;
190 l_cov_tbl(1).date_billed_to := l_date_to;
191
192
193 OKS_BILL_REC_PUB.Insert_bcl
194 (P_CALLEDFROM => 1,
195 X_RETURN_STAT => l_return_status,
196 P_CLE_ID => p_cle_id,
197 P_DATE_BILLED_FROM => l_date_from,
198 P_DATE_BILLED_TO => l_date_to,
199 P_DATE_NEXT_INVOICE => sysdate,
200 P_BILL_ACTION => 'AV',
201 P_OKL_FLAG => 2,
202 P_PRV => 1,
203 P_MSG_COUNT => l_msg_count,
204 P_MSG_DATA => l_msg_data,
205 X_BCL_ID => l_bcl_id);
206
207 IF (l_return_status <> 'S') THEN
208 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Failed in insert bcl');
209 ROLLBACK;
210 End if;
211
212 FND_FILE.PUT_LINE( FND_FILE.LOG, 'bcl '||l_bcl_id);
213
214 l_cov_tbl(1).bcl_id := l_bcl_id;
215
216 OKS_BILL_REC_PUB.Insert_all_subline
217 (
218 P_CALLEDFROM => 1,
219 X_RETURN_STAT => l_return_status,
220 P_COVERED_TBL => l_cov_tbl,
221 P_CURRENCY_CODE => l_curr_code,
222 P_DNZ_CHR_ID => p_hdr_id,
223 P_PRV => 1,
224 P_MSG_COUNT => l_msg_count,
225 P_MSG_DATA => l_msg_data
226 );
227
228 IF (l_return_status <> 'S') THEN
229 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Failed in insert bsl');
230 ROLLBACK;
231 End if;
232
233 End if;
234
235
236 END LOOP;
237
238 if l_total_credit > 0 then
239
240 UPDATE oks_bill_cont_lines
241 Set averaged_YN = 'Y'
242 WHERE cle_id = p_cle_id;
243
244 OKS_ARFEEDER_PUB.Get_REC_FEEDER
245 (
246 x_return_status => l_return_status,
247 x_msg_count => l_msg_count,
248 x_msg_data => l_msg_data,
249 p_flag => 2,
250 p_called_from => 1,
251 p_date => trunc(sysdate),
252 p_cle_id => p_cle_id,
253 p_prv => 1,-- to interface termination records
254 p_billrep_tbl => l_billrep_tbl,
255 p_billrep_tbl_idx => l_billrep_tbl_idx,
256 p_billrep_err_tbl => l_billrep_err_tbl,
257 p_billrep_err_tbl_idx => l_billrep_errtbl_idx
258 ) ;
259
260 IF (l_return_status <> OKC_API.G_RET_STS_SUCCESS) Then
261 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Average => Failed in AR FEEDER :'||p_cle_id);
262 ROLLBACK;
263 End if;
264 End if;
265
266 Exception
267 WHEN OTHERS THEN
268 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Average => Failed in AR FEEDER :'||p_cle_id);
269 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Average => Error is :'||sqlerrm);
270 ROLLBACK;
271
272 END average_api;
273
274
275 /*------------------------------------------------------------------
276 Concurrent Program Wrapper for Usage Biling Averaging and Settlement
277 --------------------------------------------------------------------*/
278 PROCEDURE Average_Main
279 (ERRBUF OUT NOCOPY VARCHAR2,
280 RETCODE OUT NOCOPY NUMBER,
281 P_CONTRACT_ID IN NUMBER)
282 IS
283 Cursor line_cur(p_id IN NUMBER) is
284 SELECT averaging_interval,
285 settlement_interval,
286 cle_id
287 FROM oks_k_lines_b
288 WHERE dnz_chr_id = p_id
289 and averaging_interval is not null;
290
291
292 CONC_STATUS BOOLEAN;
293 l_return_status VARCHAR2(1) := 'S';
294 l_msg_data VARCHAR2(2000);
295 l_msg_count NUMBER;
296 l_user_id NUMBER;
297 l_called_from CONSTANT NUMBER := 1;
298
299 BEGIN
300
301 l_user_id := FND_GLOBAL.USER_ID;
302 FND_FILE.PUT_LINE(FND_FILE.LOG, 'User_Id ='||to_char(l_user_id));
303
304 FOR line_Rec in line_cur(p_contract_id)
305 LOOP
306 IF (line_rec.averaging_interval IS NOT NULL) THEN
307 average_api(
308 p_called_from => l_called_from ,
309 p_cle_id => line_rec.cle_id ,
310 p_hdr_id => p_contract_id ,
311 p_avg_interval => line_rec.averaging_interval ,
312 X_return_status => l_return_status
313 );
314 IF (l_return_status <> 'S') THEN
315 FND_FILE.PUT_LINE( FND_FILE.LOG,
316 'Error in processing averaging for line = '||line_rec.cle_id );
317 END IF;
318
319 END IF;
320 END LOOP;
321
322 COMMIT;
323
324
325 EXCEPTION
326 WHEN OTHERS THEN
327 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Average => Failed in AR FEEDER :'||p_contract_id);
328 FND_FILE.PUT_LINE( FND_FILE.LOG, 'Average => Error is :'||sqlerrm);
329 ROLLBACK;
330
331 END Average_Main;
332
333 END OKS_AVG_SET_PUB;