1 package oks_bill_util_pub AUTHID CURRENT_USER as
2 /* $Header: OKSBUTLS.pls 120.3.12020000.2 2012/12/25 14:12:34 spingali ship $ */
3
4
5 G_FND_APP CONSTANT VARCHAR2(200) := OKC_API.G_FND_APP;
6 G_FORM_UNABLE_TO_RESERVE_REC CONSTANT VARCHAR2(200) := OKC_API.G_FORM_UNABLE_TO_RESERVE_REC;
7 G_FORM_RECORD_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_FORM_RECORD_DELETED;
8 G_FORM_RECORD_CHANGED CONSTANT VARCHAR2(200) := OKC_API.G_FORM_RECORD_CHANGED;
9 G_RECORD_LOGICALLY_DELETED CONSTANT VARCHAR2(200) := OKC_API.G_RECORD_LOGICALLY_DELETED;
10 G_REQUIRED_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_REQUIRED_VALUE;
11 G_INVALID_VALUE CONSTANT VARCHAR2(200) := OKC_API.G_INVALID_VALUE;
12 G_COL_NAME_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_COL_NAME_TOKEN;
13 G_PARENT_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_PARENT_TABLE_TOKEN;
14 G_CHILD_TABLE_TOKEN CONSTANT VARCHAR2(200) := OKC_API.G_CHILD_TABLE_TOKEN;
15 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(200) := 'OKC_CONTRACTS_UNEXP_ERROR';
16 G_SQLERRM_TOKEN CONSTANT VARCHAR2(200) := 'SQLerrm';
17 G_SQLCODE_TOKEN CONSTANT VARCHAR2(200) := 'SQLcode';
18 G_UPPERCASE_REQUIRED CONSTANT VARCHAR2(200) := 'OKC_UPPERCASE_REQUIRED';
19
20 ------------------------------------------------------------------------------------
21
22 -- Constants used for Message Logging
23 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
24 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
25 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
26 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
27 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
28 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
29 G_LEVEL_CURRENT CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
30 G_MODULE_CURRENT CONSTANT VARCHAR2(255) := 'oks.plsql.oks_bill_sch';
31 --------------------------------------------------------
32 -- GLOBAL EXCEPTION
33 ---------------------------------------------------------------------------
34 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
35
36 -- GLOBAL VARIABLES
37 ---------------------------------------------------------------------------
38 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKS_BILL_UTIL_PUB';
39 G_APP_NAME CONSTANT VARCHAR2(3) := 'OKS';
40 ---------------------------------------------------------------------------
41
42
43
44 /***bill rec type for usage billing***/
45
46 l_line_rec OKS_QP_PKG.Input_details ;
47 l_price_rec OKS_QP_PKG.Price_Details ;
48 l_modifier_details qp_preq_grp.line_detail_tbl_type;
49 l_price_break_details oks_qp_pkg.g_price_break_tbl_type;
50
51
52
53 TYPE Bill_Rec_Type IS RECORD (
54 Counter_id NUMBER,
55 Reading_date DATE,
56 Meter_reading NUMBER,
57 Last_Meter_reading NUMBER,
58 Net_reading NUMBER,
59 Level_reading NUMBER,
60 Bill_amount NUMBER);
61
62 Type Bill_tbl_type is TABLE of Bill_Rec_Type index by binary_integer;
63
64
65 Type sll_prorated_rec_type IS RECORD
66 ( sll_seq_num Number,
67 sll_start_date DATE,
68 sll_end_date DATE,
69 sll_tuom VARCHAR2(40),
70 sll_amount Number
71 );
72
73 Type bill_det_inp_rec IS RECORD
74 (line_start_date DATE,
75 line_end_date DATE,
76 cycle_start_date DATE,
77 tuom_per_period Number,
78 tuom Varchar2(3),
79 total_amount Number,
80 invoice_offset_days Number,
81 interface_offset_days Number,
82 bill_type VARCHAR2(1), ----values may be E,T,P
83 uom_per_period Number --mchoudha added this parameter
84 );
85
86 Type bill_sch_rec IS RECORD
87 (
88 next_cycle_date DATE,
89 cycle_amount Number,
90 date_transaction DATE,
91 date_revenue_rule_start DATE,
92 date_recievable_gl DATE,
93 date_due DATE,
94 date_print DATE,
95 date_to_interface DATE,
96 date_completed DATE
97 );
98
99 Type next_level_element_type IS RECORD
100 (
101 id NUMBER,
102 sequence_number NUMBER,
103 bill_from_date DATE,
104 bill_to_date DATE,
105 bill_amount Number,
106 date_to_interface DATE,
107 date_receivable_gl DATE,
108 date_revenue_rule_start DATE,
109 date_transaction DATE,
110 date_due DATE,
111 date_print DATE,
112 date_completed DATE,
113 rule_id NUMBER
114 );
115
116 Type level_element_tab is Table of next_level_element_type index by
117 binary_integer;
118
119 Type sll_prorated_tab_type is Table of sll_prorated_rec_type index by binary_integer;
120
121 PROCEDURE get_seeded_timeunit ( p_timeunit in varchar2,
122 x_return_status out NOCOPY varchar2,
123 x_quantity out NOCOPY number,
124 x_timeunit out NOCOPY varchar2) ;
125
126 PROCEDURE Get_sll_amount ( p_api_version IN NUMBER,
127 p_total_amount IN NUMBER,
128 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
129 x_return_status OUT NOCOPY VARCHAR2,
130 x_msg_count OUT NOCOPY NUMBER,
131 x_msg_data OUT NOCOPY VARCHAR2,
132 p_currency_code IN VARCHAR2,
133 p_sll_prorated_tab IN OUT NOCOPY sll_prorated_tab_type );
134
135
136 PROCEDURE pre_del_level_elements(
137 p_api_version IN NUMBER,
138 p_terminated_date IN DATE,
139 p_id IN NUMBER ,
140 p_flag IN NUMBER,
141 x_return_status OUT NOCOPY VARCHAR2
142 );
143
144
145 PROCEDURE delete_level_elements(
146 p_api_version IN NUMBER,
147 p_terminated_date IN DATE,
148 p_chr_id IN NUMBER,
149 p_cle_id IN NUMBER ,
150 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
151 x_return_status OUT NOCOPY VARCHAR2,
152 x_msg_count OUT NOCOPY NUMBER,
153 x_msg_data OUT NOCOPY VARCHAR2
154 );
155
156
157 Procedure Get_prorate_amount
158 ( p_api_version IN NUMBER,
159 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
160 x_return_status OUT NOCOPY VARCHAR2,
161 x_msg_count OUT NOCOPY NUMBER,
162 x_msg_data OUT NOCOPY VARCHAR2,
163 p_invoicing_rule_id IN Number,
164 p_bill_sch_detail_rec IN bill_det_inp_rec,
165 x_bill_sch_detail_rec OUT NOCOPY bill_sch_rec
166 );
167
168
169 -------------------------------------------------------------------------
170 -- Begin partial period computation logic
171 -- Developer Mani Choudhary
172 -- Date 09-MAY-2005
173 -- 1) Added two new parameters P_period_start,P_period_type in procedure
174 -- Get_next_bill_sch
175 -- 2) Added function get_enddate_cal
176 -- 3) Added function get_periods
177 -------------------------------------------------------------------------
178 Procedure Get_next_bill_sch
179 ( p_api_version IN NUMBER,
180 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
181 x_return_status OUT NOCOPY VARCHAR2,
182 x_msg_count OUT NOCOPY NUMBER,
183 x_msg_data OUT NOCOPY VARCHAR2,
184 p_invoicing_rule_id IN Number,
185 p_bill_sch_detail_rec IN bill_det_inp_rec,
186 x_bill_sch_detail_rec OUT NOCOPY bill_sch_rec,
187 P_period_start IN VARCHAR2,
188 P_period_type IN VARCHAR2,
189 -- Start - Added by PMALLARA - Bug #3992530
190 Strm_Start_Date IN DATE
191 -- End - Added by PMALLARA - Bug #3992530
192 );
193
194 --This new function will determine the end date of the
195 --SLL in case of "Calendar month" period start.
196 FUNCTION get_enddate_cal(p_start_date IN DATE,
197 p_uom_code IN VARCHAR2,
198 p_duration IN NUMBER,
199 p_level_periods IN NUMBER
200 )
201 RETURN DATE;
202
203 --This new function will determine numbr of periods of SLL given
204 --the start date, end date,uom_per_period and uom of the SLL.
205 FUNCTION get_periods (p_start_date IN DATE,
206 p_end_date IN DATE,
207 p_uom_code IN VARCHAR2,
208 p_period_start IN VARCHAR2
209 )
210 RETURN NUMBER;
211
212 -------------------------------------------------------------------------
213 -- End partial period computation logic
214 -- Date 09-MAY-2005
215 -------------------------------------------------------------------------
216
217 /*** Takes input cp_id and p_Date, returns level_element table ****/
218 Procedure Get_next_level_element
219 ( p_api_version IN NUMBER,
220 p_id IN NUMBER,
221 p_covd_flag IN Varchar2,
222 p_date IN DATE,
223 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
224 x_return_status OUT NOCOPY VARCHAR2,
225 x_msg_count OUT NOCOPY NUMBER,
226 x_msg_data OUT NOCOPY VARCHAR2,
227 x_next_level_element OUT NOCOPY level_element_tab
228 );
229
230 /** Function will return total invoices billed, for given rule id ****/
231
232 Function Get_total_inv_billed (p_api_version IN Varchar2,
233 p_rule_id IN Number,
234 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
235 x_return_status OUT NOCOPY VARCHAR2,
236 x_msg_count OUT NOCOPY NUMBER,
237 x_msg_data OUT NOCOPY VARCHAR2)
238 return Number;
239
240
241 /** Procedure to delete rows from Oks_level_elements, for a given rule_id **/
242
243 Procedure Delete_level_elements( p_api_version IN NUMBER,
244 p_rule_id IN Number,
245 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
246 x_msg_count OUT NOCOPY NUMBER,
247 x_msg_data OUT NOCOPY VARCHAR2,
248 x_return_status OUT NOCOPY Varchar2 );
249
250 PROCEDURE delete_rule(
251 p_api_version IN NUMBER,
252 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
253 p_chr_id IN NUMBER,
254 x_return_status OUT NOCOPY VARCHAR2,
255 x_msg_count OUT NOCOPY NUMBER,
256 x_msg_data OUT NOCOPY VARCHAR2);
257
258 PROCEDURE delete_slh_rule(
259 p_api_version IN NUMBER,
260 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
261 p_cle_id IN NUMBER,
262 x_return_status OUT NOCOPY VARCHAR2,
263 x_msg_count OUT NOCOPY NUMBER,
264 x_msg_data OUT NOCOPY VARCHAR2);
265
266
267 /*Procedure Create_covlv_billsch
268 (
269 p_cp_id NUMBER DEFAULT NULL,
270 p_cle_id NUMBER,
271 p_hdr_id NUMBER,
272 p_srv_id NUMBER,
273 p_organization_id NUMBER,
274 x_return_status out NOCOPY VARCHAR2,
275 x_msg_data out NOCOPY VARCHAR2,
276 x_msg_count out NOCOPY NUMBER
277 );*/
278
279 /** Procedure for copying/splitting service lines **/
280 TYPE copy_source_rec is RECORD
281 (cle_id NUMBER,
282 item_id VARCHAR2(40),
283 amount NUMBER);
284 TYPE copy_target_rec is RECORD
285 (cle_id NUMBER,
286 item_id VARCHAR2(40),
287 amount NUMBER,
288 percentage NUMBER);
289 TYPE copy_target_tbl is table of copy_target_rec INDEX BY BINARY_INTEGER;
290 PROCEDURE copy_service( p_api_version IN NUMBER,
291 p_init_msg_list IN VARCHAR2,
292 p_source_rec IN copy_source_rec,
293 p_target_tbl IN OUT NOCOPY copy_target_tbl,
294 x_return_status OUT NOCOPY VARCHAR2,
295 x_msg_count OUT NOCOPY NUMBER,
296 x_msg_data OUT NOCOPY VARCHAR2
297 );
298
299 /**Procedure for usage billing*/
300
301
302 Procedure Calculate_Bill_Amount (
303 p_api_version IN NUMBER,
304 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
305 p_bill_tbl IN OUT NOCOPY Bill_tbl_type,
306 x_return_status OUT NOCOPY VARCHAR2,
307 x_msg_count OUT NOCOPY NUMBER,
308 x_msg_data OUT NOCOPY VARCHAR2);
309
310
311 Function Get_Credit_Amount (p_api_version IN Varchar2,
312 p_cp_line_id IN Number,
313 p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_FALSE,
314 x_return_status OUT NOCOPY VARCHAR2,
315 x_msg_count OUT NOCOPY NUMBER,
316 x_msg_data OUT NOCOPY VARCHAR2)
317 RETURN NUMBER;
318
319 Function Get_frequency
320 (p_tce_code IN VARCHAR2,
321 p_fr_start_date IN DATE,
322 p_fr_end_date IN DATE,
323 p_uom_quantity IN Number,
324 x_return_status OUT NOCOPY VARCHAR2
325 ) Return NUMBER ;
326
327
328 /* Procedure to create billing report */
329
330 Procedure Create_Report (
331 p_billrep_table IN OKS_BILL_REC_PUB.bill_report_tbl_type
332 ,p_billrep_err_tbl IN OKS_BILL_REC_PUB.billrep_error_tbl_type
333 ,p_line_from IN NUMBER
334 ,p_line_to IN NUMBER
335 ,x_return_status OUT NOCOPY Varchar2
336 ) ;
337
338
339 /* Procedure to update the OKS_LEVEL_ELEMENST */
340 PROCEDURE UPDATE_OKS_LEVEL_ELEMENTS
341 ( p_line_id IN number ,
342 x_return_status OUT NOCOPY varchar2 ) ;
343
344
345
346 --This is to insert BCL for contracts orginated from Order management.
347 PROCEDURE CREATE_BCL_FOR_OM ( P_LINE_ID IN NUMBER ,
348 X_RETURN_STATUS OUT NOCOPY VARCHAR2 );
349
350 --This is to insert BSL for contracts orginated from Order management.
351 PROCEDURE CREATE_BSL_FOR_OM ( P_LINE_ID IN NUMBER ,
352 P_BCL_ID IN NUMBER ,
353 X_RETURN_STATUS OUT NOCOPY VARCHAR2 ,
354 X_SUB_LINES_INSERTED OUT NOCOPY NUMBER ,
355 X_TOTAL_AMOUNT OUT NOCOPY NUMBER ) ;
356
357 /*Added for ER 16039680 */
358 --This is to insert BCL for Contracts orginated from Order management with flexible Billing
359 PROCEDURE CREATE_BCL_FOR_OM_OKS_BILLING( P_LINE_ID IN NUMBER ,
360 P_DATE_TO IN DATE,
361 X_RETURN_STATUS OUT NOCOPY VARCHAR2 );
362 --This is to insert BSL for Contracts orginated from Order management with flexible Billing
363 PROCEDURE CREATE_BSL_FOR_OM_OKS_BILLING( P_LINE_ID IN NUMBER ,
364 P_BCL_ID IN NUMBER ,
365 P_DATE_FROM IN DATE,
366 P_DATE_TO IN DATE,
367 X_RETURN_STATUS OUT NOCOPY VARCHAR2 ,
368 X_SUB_LINES_INSERTED OUT NOCOPY NUMBER ,
369 X_TOTAL_AMOUNT OUT NOCOPY NUMBER ) ;
370 /*Added for ER 16039680 */
371
372
373 ---This will give the billed qty for subcription line
374
375 Function Get_Billed_Qty (p_line_id IN Number,
376 x_return_status OUT NOCOPY VARCHAR2) return Number;
377
378 Function Get_Billed_Upto ( p_id IN Number,
379 p_level IN Varchar2 -- 'H'eader, 'T'opline, 'S'ubline
380 ) Return Date;
381
382 FUNCTION Is_Sc_Allowed (p_org_id IN Number) RETURN BOOLEAN;
383
384 Function IS_Contract_billed (p_header_id IN Number,
385 x_return_status OUT NOCOPY VARCHAR2) return Boolean;
386
387
388 PROCEDURE ADJUST_SPLIT_BILL_REC(p_old_cp_id IN NUMBER,
389 p_new_cp_id IN NUMBER,
390 p_rgp_id IN NUMBER,
391 p_currency_code IN VARCHAR2,
392 p_old_cp_lvl_tbl IN oks_bill_level_elements_pvt.letv_tbl_type,
393 p_new_cp_lvl_tbl IN oks_bill_level_elements_pvt.letv_tbl_type,
394 x_return_status OUT NOCOPY VARCHAR2,
395 x_msg_count OUT NOCOPY NUMBER,
396 x_msg_data OUT NOCOPY VARCHAR2);
397
398 Procedure Adjust_line_price(p_top_line_id IN NUMBER,
399 p_sub_line_id IN NUMBER,
400 p_end_date IN DATE,
401 p_amount IN NUMBER,
402 p_dnz_chr_id IN NUMBER,
403 x_amount OUT NOCOPY NUMBER,
404 x_return_status OUT NOCOPY VARCHAR2);
405
406 End oks_bill_util_pub;
407
[Home] [Help]