[Home] [Help]
PACKAGE: APPS.OKS_BILL_SCH
Source
1 Package OKS_BILL_SCH AUTHID CURRENT_USER AS
2 /* $Header: OKSBLSHS.pls 120.2.12020000.2 2012/12/25 13:51:49 spingali ship $ */
3
4 -- GLOBAL VARIABLES
5 -------------------------------------------------------------------------------
6 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKS_BILL_SCH';
7 G_APP_NAME_OKS CONSTANT VARCHAR2(3) := 'OKS';
8 G_APP_NAME_OKC CONSTANT VARCHAR2(3) := 'OKC';
9 -------------------------------------------------------------------------------
10
11
12 -- GLOBAL_MESSAGE_CONSTANTS
13 ---------------------------------------------------------------------------------------------
14 G_TRUE CONSTANT VARCHAR2(1) := OKC_API.G_TRUE;
15 G_FALSE CONSTANT VARCHAR2(1) := OKC_API.G_FALSE;
16 G_RET_STS_SUCCESS CONSTANT VARCHAR2(1) := OKC_API.G_RET_STS_SUCCESS;
17 G_RET_STS_ERROR CONSTANT VARCHAR2(1) := OKC_API.G_RET_STS_ERROR;
18 G_RET_STS_UNEXP_ERROR CONSTANT VARCHAR2(1) := OKC_API.G_RET_STS_UNEXP_ERROR;
19 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(30) := 'OKS_CONTRACTS_UNEXPECTED_ERROR';
20 G_SQLERRM_TOKEN CONSTANT VARCHAR2(30) := 'SQLerrm';
21 G_SQLCODE_TOKEN CONSTANT VARCHAR2(30) := 'SQLcode';
22 G_REQUIRED_VALUE CONSTANT VARCHAR2(30):=OKC_API.G_REQUIRED_VALUE;
23 G_COL_NAME_TOKEN CONSTANT VARCHAR2(30):=OKC_API.G_COL_NAME_TOKEN;
24 ---------------------------------------------------------------------------------------------
25 -- Constants used for Message Logging
26 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
27 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
28 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
29 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
30 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
31 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
32 G_LEVEL_CURRENT CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
33 G_MODULE_CURRENT CONSTANT VARCHAR2(255) := 'oks.plsql.oks_bill_sch';
34 --------------------------------------------------------
35 G_EXCEPTION_HALT_VALIDATION EXCEPTION;
36
37
38 TYPE Line_Type IS RECORD
39 ( start_dt Date,
40 end_dt Date,
41 amount Number,
42 currency_code varchar2(15)
43 );
44
45 /* added for bug#3307323*/
46
47 TYPE StreamHdr_Type Is Record
48 (
49 Chr_Id Number,
50 Cle_Id Number,
51 Rule_Information1 Varchar2 (450),
52 Rule_Information2 Varchar2 (450),
53 Rule_Information3 Varchar2 (450),
54 Rule_Information4 Varchar2 (450),
55 Rule_Information5 Varchar2 (450),
56 Rule_Information6 Varchar2 (450),
57 Rule_Information7 Varchar2 (450),
58 Rule_Information8 Varchar2 (450),
59 Rule_Information9 Varchar2 (450),
60 Rule_Information10 Varchar2 (450),
61 Rule_Information11 Varchar2 (450),
62 Rule_Information12 Varchar2 (450),
63 Rule_Information13 Varchar2 (450),
64 Rule_Information14 Varchar2 (450),
65 Rule_Information15 Varchar2 (450),
66 Rule_Information_Category Varchar2 (90),
67 Object1_Id1 Varchar2 (40),
68 Object1_Id2 Varchar2 (200),
69 Object2_Id1 Varchar2 (40),
70 Object2_Id2 Varchar2 (200),
71 Object3_Id1 Varchar2 (40),
72 Object3_Id2 Varchar2 (200),
73 Jtot_Object1_Code Varchar2 (30),
74 Jtot_Object2_Code Varchar2 (30),
75 Jtot_Object3_Code Varchar2 (30)
76 );
77
78
79 TYPE Subline_id_Type Is Record
80 (
81 id number);
82
83 Type Subline_id_tbl is TABLE of Subline_id_type index by binary_integer;
84
85
86 TYPE StreamLvl_Type Is Record
87 (
88 id number,
89 CHR_ID Number,
90 CLE_ID Number,
91 DNZ_CHR_ID Number,
92 Sequence_no Number,
93 uom_code Varchar2 (3),
94 start_date Date,
95 end_date date,
96 level_periods Number,
97 uom_per_period Number,
98 advance_periods Number,
99 level_amount Number,
100 invoice_offset_days Number,
101 interface_offset_days Number,
102 comments Varchar2 (240),
103 due_ARR_YN Varchar2 (1),
104 AMOUNT Number,
105 LINES_DETAILED_YN Varchar2 (1),
106 Rule_Information1 Varchar2 (450),
107 Rule_Information2 Varchar2 (450),
108 Rule_Information3 Varchar2 (450),
109 Rule_Information4 Varchar2 (450),
110 Rule_Information_Category Varchar2 (90),
111 Object1_Id1 Varchar2 (40)
112 );
113
114 Type StreamLvl_tbl is TABLE of StreamLvl_type index by binary_integer;
115
116
117
118
119
120 TYPE ItemBillSch_Type Is Record
121 (
122 Chr_Id Number,
123 Cle_Id Number,
124 Strm_Lvl_Seq_Num NUMBER,
125 Lvl_Element_Seq_Num Varchar2 (240),
126 Tx_Date Date,
127 Bill_From_Date Date,
128 Bill_To_Date Date,
129 Interface_Date Date,
130 Date_Completed Date,
131 Amount Number,
132 Rule_Id Number
133 );
134
135 Type ItemBillSch_tbl is TABLE of ItemBillSch_Type index by binary_integer;
136
137
138 /* overloaded procedure just for OKL bug# 3307323*/
139 Procedure Create_Bill_Sch_Rules
140 (
141 p_slh_rec IN StreamHdr_Type
142 , p_sll_tbl IN StreamLvl_tbl
143 , p_invoice_rule_id IN Number
144 , x_bil_sch_out_tbl OUT NOCOPY ItemBillSch_tbl
145 , x_return_status OUT NOCOPY Varchar2
146 );
147
148
149
150 Procedure Create_Bill_Sch_Rules
151 (
152 p_billing_type IN Varchar2
153 , p_sll_tbl IN StreamLvl_tbl
154 , p_invoice_rule_id IN Number
155 , x_bil_sch_out_tbl OUT NOCOPY ItemBillSch_tbl
156 , x_return_status OUT NOCOPY Varchar2
157 );
158
159 Procedure Create_Header_Bill_Sch
160 (
161 p_billing_type IN Varchar2
162 , p_sll_tbl IN StreamLvl_tbl
163 , p_invoice_rule_id IN Number
164 , x_bil_sch_out_tbl OUT NOCOPY ItemBillSch_tbl
165 , x_return_status OUT NOCOPY Varchar2
166 );
167
168 PROCEDURE Copy_Bill_Sch
169 (
170 p_chr_id IN Number,
171 p_cle_id IN Number,
172 x_copy_bill_sch OUT NOCOPY ItemBillSch_tbl,
173 x_return_status OUT NOCOPY Varchar2
174 );
175
176 Procedure Update_Sll_Amount
177 (
178 p_line_id IN NUMBER,
179 x_return_status OUT NOCOPY Varchar2
180 );
181
182 Procedure Cal_hdr_Sll_Amount
183 (
184 p_hdr_id IN NUMBER,
185 x_return_status OUT NOCOPY Varchar2
186 );
187
188
189 ---top line id is passed.
190 Procedure Cascade_Dates_SLL
191 (
192 p_top_line_id IN NUMBER,
193 x_return_status OUT NOCOPY Varchar2,
194 x_msg_count OUT NOCOPY NUMBER,
195 x_msg_data OUT NOCOPY VARCHAR2);
196
197
198 Procedure Create_Bill_Sch_CP
199 (
200 p_top_line_id IN NUMBER,
201 p_cp_line_id IN NUMBER,
202 p_cp_new IN Varchar2, ---('Y'if cp new else 'N')
203 x_return_status OUT NOCOPY Varchar2,
204 x_msg_count OUT NOCOPY NUMBER,
205 x_msg_data OUT NOCOPY VARCHAR2);
206
207 Function Cal_Sllid_amount
208 (
209 p_Sll_id IN NUMBER,
210 x_return_status OUT NOCOPY Varchar2,
211 x_msg_count OUT NOCOPY NUMBER,
212 x_msg_data OUT NOCOPY VARCHAR2
213 )RETURN NUMBER;
214
215
216 ---only be called for OM contracts (one time billing) to update SLL dates of existing SLL
217 --and creating level elements.
218
219 Procedure Update_OM_SLL_Date
220 (
221 p_top_line_id IN NUMBER,
222 x_return_status OUT NOCOPY Varchar2,
223 x_msg_count OUT NOCOPY NUMBER,
224 x_msg_data OUT NOCOPY VARCHAR2);
225
226
227 ---delete all sll,lvlelements of top and sublines
228
229 PROCEDURE Del_rul_elements(p_top_line_id IN NUMBER,
230 x_return_status OUT NOCOPY VARCHAR2,
231 x_msg_count OUT NOCOPY NUMBER,
232 x_msg_data OUT NOCOPY VARCHAR2);
233
234
235
236
237 ---delete sll of subline and refresh the lvl amt of top line for 'Top Level' billing
238 PROCEDURE Del_subline_lvl_rule(p_top_line_id IN NUMBER,
239 p_sub_line_id IN NUMBER,
240 x_return_status OUT NOCOPY VARCHAR2,
241 x_msg_count OUT NOCOPY NUMBER,
242 x_msg_data OUT NOCOPY VARCHAR2);
243
244
245 PROCEDURE update_bs_interface_date(p_top_line_id IN NUMBER,
246 p_invoice_rule_id IN Number,
247 x_return_status OUT NOCOPY VARCHAR2,
248 x_msg_count OUT NOCOPY NUMBER,
249 x_msg_data OUT NOCOPY VARCHAR2);
250 ---contract id passed
251 Procedure Cascade_Dt_lines_SLL
252 (
253 p_contract_id IN NUMBER,
254 p_line_id IN NUMBER,
255 x_return_status OUT NOCOPY Varchar2);
256
257 Procedure Create_Subcription_bs
258 (
259 p_top_line_id IN NUMBER,
260 p_full_credit IN VARCHAR2,
261 x_return_status OUT NOCOPY Varchar2,
262 x_msg_count OUT NOCOPY NUMBER,
263 x_msg_data OUT NOCOPY VARCHAR2);
264
265 Procedure Terminate_bill_sch(
266 p_top_line_id IN NUMBER,
267 p_sub_line_id IN NUMBER,
268 p_term_dt IN DATE,
269 x_return_status OUT NOCOPY Varchar2,
270 x_msg_count OUT NOCOPY NUMBER,
271 x_msg_data OUT NOCOPY VARCHAR2);
272
273
274 ----the procedure will create level elements only
275 ---for the contract,it will be called from copy and renewal.
276
277 Procedure Create_hdr_schedule
278 (
279 p_contract_id IN NUMBER,
280 x_return_status OUT NOCOPY VARCHAR2,
281 x_msg_count OUT NOCOPY NUMBER,
282 x_msg_data OUT NOCOPY VARCHAR2);
283
284 ----the procedure will delete all the sll and level elements for the whole contract
285 ---(i.e header,line,subline)
286
287 Procedure Delete_contract_bs_sll
288 (
289 p_contract_id IN NUMBER,
290 x_return_status OUT NOCOPY VARCHAR2,
291 x_msg_count OUT NOCOPY NUMBER,
292 x_msg_data OUT NOCOPY VARCHAR2);
293
294
295
296 ----the procedure will update end date of sll and level elements for migrated contracts.
297
298 Procedure UPDATE_BS_ENDDATE(p_line_id IN NUMBER,
299 p_chr_id IN NUMBER,
300 x_return_status OUT NOCOPY VARCHAR2);
301
302
303 Procedure Preview_Subscription_Bs(p_sll_tbl IN StreamLvl_tbl,
304 p_invoice_rule_id IN Number,
305 p_line_detail IN LINE_TYPE,
306 x_bil_sch_out_tbl OUT NOCOPY ItemBillSch_tbl,
307 x_return_status OUT NOCOPY Varchar2);
308
309
310 PROCEDURE ADJUST_REPLACE_PRODUCT_BS(p_old_cp_id IN NUMBER,
311 p_new_cp_id IN NUMBER,
312 x_return_status OUT NOCOPY VARCHAR2,
313 x_msg_count OUT NOCOPY NUMBER,
314 x_msg_data OUT NOCOPY VARCHAR2);
315
316
317 Procedure ADJUST_SPLIT_BILL_SCH(p_old_cp_id IN NUMBER,
318 p_new_cp_tbl IN OKS_BILL_SCH.SUBLINE_ID_TBL,
319 x_return_status OUT NOCOPY VARCHAR2,
320 x_msg_count OUT NOCOPY NUMBER,
321 x_msg_data OUT NOCOPY VARCHAR2);
322
323
324 --[llc] Sts_change_subline_lvl_rule
325
326 /* This procedure updates the amount on the top line when the status of sub-line is
327 changed from 'Entered' to 'Cancelled' or 'Cancelled' to 'Entered'.
328 */
329
330
331 PROCEDURE Sts_change_subline_lvl_rule(
332 p_cle_id IN NUMBER,
333 p_from_ste_code IN VARCHAR2,
334 p_to_ste_code IN VARCHAR2,
335 x_return_status OUT NOCOPY VARCHAR2,
336 x_msg_count OUT NOCOPY NUMBER,
337 x_msg_data OUT NOCOPY VARCHAR2);
338
339
340 --ppc R12
341 FUNCTION Get_Converted_price (
342 p_price_uom IN VARCHAR2,
343 p_pl_uom IN VARCHAR2,
344 p_period_start IN VARCHAR2,
345 p_period_type IN VARCHAR2,
346 p_price_negotiated IN NUMBER,
347 p_unit_price IN NUMBER,
348 p_start_date IN DATE,
349 p_end_date IN DATE
350
351 )RETURN NUMBER;
352
353 ---Added for ER 16039680
354 PROCEDURE Get_Billed_Amount (
355 p_start_date IN Date, ---Start_Date of the line
356 p_end_date IN Date, ---End_Date of the line
357 p_total_amount IN NUMBER, ---Total Amount of the line
358 p_item_type IN VARCHAR2, --- Subscription/Service
359 p_org_id IN NUMBER, ---Operating Unit
360 p_billingprofile_Id IN NUMBER, ---Billing Profile Id
361 p_currency_code IN VARCHAR2,
362 p_substemplate_id IN NUMBER DEFAULT NULL,
363 x_bill_amount OUT NOCOPY NUMBER,
364 x_bill_enddate OUT NOCOPY DATE
365 ) ;
366
367
368
369
370 END OKS_BILL_SCH;