[Home] [Help]
PACKAGE: APPS.OKL_BILLING_CONTROLLER_PVT
Source
1 PACKAGE OKL_BILLING_CONTROLLER_PVT AS
2 /* $Header: OKLPBICS.pls 120.13 2008/02/07 13:21:40 zrehman noship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(200) := 'OKL_BILLING_CONTROLLER_PVT';
5 G_APP_NAME CONSTANT VARCHAR2(3) := OKL_API.G_APP_NAME;
6
7 G_API_TYPE CONSTANT VARCHAR2(4) := '_PVT';
8 G_UNEXPECTED_ERROR CONSTANT VARCHAR2(1000) := 'OKL_UNEXPECTED_ERROR';
9
10 -- Bug 4520466
11 FUNCTION get_next_bill_date ( p_khr_id IN NUMBER ) return date;
12
13 PROCEDURE track_next_bill_date ( p_khr_id IN NUMBER );
14 -- End Bug 4520466
15
16 -- Bug 4546873;
17 PROCEDURE PURGE_PARALLEL_PROCESSES (
18 errbuf OUT NOCOPY VARCHAR2,
19 retcode OUT NOCOPY NUMBER,
20 p_source IN VARCHAR2 DEFAULT NULL
21 );
22 -- End Bug 4546873;
23
24
25 PROCEDURE Process_Spawner (
26 errbuf OUT NOCOPY VARCHAR2,
27 retcode OUT NOCOPY NUMBER,
28 p_num_processes IN NUMBER,
29 p_start_date_from IN VARCHAR2 DEFAULT NULL,
30 p_start_date_to IN VARCHAR2 DEFAULT NULL,
31 p_contract_number IN VARCHAR2 DEFAULT NULL,
32 p_cust_acct_id IN NUMBER DEFAULT NULL,
33 p_inv_msg IN VARCHAR2 DEFAULT 'TRUE',
34 p_source IN VARCHAR2 DEFAULT NULL,
35 -- modified by zrehman for Bug#6788005 on 01-Feb-2008 start
36 p_ia_contract_type IN VARCHAR2,
37 p_inv_cust_acct_id IN NUMBER
38 -- modified by zrehman for Bug#6788005 on 01-Feb-2008 end
39 );
40
41
42 PROCEDURE BILL_STREAMS_MASTER (
43 errbuf OUT NOCOPY VARCHAR2,
44 retcode OUT NOCOPY NUMBER,
45 p_ia_contract_type IN VARCHAR2,
46 p_start_date_from IN VARCHAR2,
47 p_start_date_to IN VARCHAR2,
48 p_contract_number IN VARCHAR2 DEFAULT NULL,
49 p_cust_acct_id IN NUMBER DEFAULT NULL,
50 p_inv_cust_acct_id IN NUMBER,
51 p_num_processes IN NUMBER
52 );
53
54 --fmiao 5209209 change
55 PROCEDURE PREPARE_RECEIVABLES_MASTER (
56 errbuf OUT NOCOPY VARCHAR2,
57 retcode OUT NOCOPY NUMBER,
58 p_num_processes IN NUMBER
59 );
60 --fmiao 5209209 change end
61
62
63 PROCEDURE AR_TRANSFER_MASTER (
64 errbuf OUT NOCOPY VARCHAR2,
65 retcode OUT NOCOPY NUMBER,
66 p_start_date_from IN VARCHAR2,
67 p_start_date_to IN VARCHAR2,
68 p_num_processes IN NUMBER
69 );
70
71
72 PROCEDURE OKL_CONS_MASTER (
73 errbuf OUT NOCOPY VARCHAR2,
74 retcode OUT NOCOPY NUMBER,
75 p_inv_msg IN VARCHAR2 DEFAULT 'TRUE',
76 p_num_processes IN NUMBER
77 );
78
79
80 G_NO_DATA_FOUND CONSTANT VARCHAR2(200) := 'NO_DATA_FOUND';
81 G_TOO_MANY_ROWS CONSTANT VARCHAR2(200) := 'TOO_MANY_ROWS';
82 G_OTHERS CONSTANT VARCHAR2(200) := 'OTHERS';
83
84 -- ------------------------------
85 -- Commit frequency
86 -- ------------------------------
87 G_Commit_Max CONSTANT NUMBER := 500;
88 g_cons_bill_data_cached BOOLEAN := FALSE;
89
90 -- -------------------------------------------------------------------------
91 -- Consolidated Billing data structure
92 -- -------------------------------------------------------------------------
93 TYPE cons_bill_rec_type IS RECORD (
94 customer_id okl_ext_sell_invs_v.customer_id%TYPE,
95 currency okl_ext_sell_invs_v.currency_code%TYPE,
96 bill_to_site okl_ext_sell_invs_v.customer_address_id%TYPE,
97 payment_method okl_ext_sell_invs_v.receipt_method_id%TYPE,
98 private_label okl_ext_sell_invs_v.xtrx_private_label%TYPE,
99 date_consolidated okl_ext_sell_invs_v.TRX_DATE%TYPE,
100 ity_id okl_invoice_types_b.id%TYPE, -- bug 5138822
101 inf_id okl_invoice_formats_b.id%TYPE,
102 contract_id okc_k_headers_b.id%TYPE,
103 contract_number okc_k_headers_b.contract_number%TYPE,
104 prev_cons_invoice_num okl_cnsld_ar_hdrs_v.consolidated_invoice_number%TYPE,
105 org_id okl_ext_sell_invs_v.org_id%TYPE,
106 set_of_books_id okl_ext_sell_invs_v.set_of_books_id%TYPE,
107 kle_id okc_k_lines_b.id%TYPE,
108 sty_id okl_strm_type_v.id%TYPE,
109 xsi_id okl_ext_sell_invs_v.id%TYPE,
110 xls_id okl_xtl_sell_invs_v.id%TYPE,
111 amount okl_xtl_sell_invs_v.amount%TYPE,
112 sel_id okl_xtl_sell_invs_v.sel_id%TYPE,
113 legal_entity_id okl_ext_sell_invs_v.legal_entity_id%TYPE -- for LE Uptake project 08-11-2006
114 );
115
116 TYPE cons_bill_tbl_type IS TABLE OF cons_bill_rec_type
117 INDEX BY BINARY_INTEGER;
118
119 TYPE saved_bill_rec_type IS RECORD (
120 l_customer_id okl_ext_sell_invs_v.customer_id%TYPE,
121 l_currency okl_ext_sell_invs_v.currency_code%TYPE,
122 l_bill_to_site okl_ext_sell_invs_v.customer_address_id%TYPE,
123 l_payment_method okl_ext_sell_invs_v.receipt_method_id%TYPE,
124 l_private_label okl_ext_sell_invs_v.xtrx_private_label%TYPE,
125 l_date_consolidated okl_ext_sell_invs_v.TRX_DATE%TYPE,
126 l_saved_ity_id okl_invoice_types_b.id%TYPE, -- bug 5138822
127 l_saved_format_id okl_invoice_formats_v.id%TYPE,
128 l_saved_prev_cons_num okl_cnsld_ar_hdrs_v.consolidated_invoice_number%TYPE,
129 l_prev_khr_id okc_k_headers_b.id%TYPE,
130 l_line_number NUMBER,
131 l_commit_cnt NUMBER,
132 l_overall_status VARCHAR2(1),
133 l_cnr_id NUMBER,
134 l_lln_id NUMBER,
135 l_cons_inv_num okl_cnsld_ar_hdrs_v.consolidated_invoice_number%TYPE);
136
137 TYPE update_rec_type IS RECORD (
138 cnr_id NUMBER,
139 cons_inv_number okl_cnsld_ar_hdrs_b.consolidated_invoice_number%TYPE,
140 lln_id NUMBER,
141 lsm_id NUMBER,
142 asset_number Okl_Xtl_Sell_Invs_tl.XTRX_ASSET%TYPE,
143 invoice_format OKL_INVOICE_FORMATS_V.NAME%TYPE,
144 line_type okl_invc_line_types_v.NAME%TYPE,
145 private_label Okl_Ext_Sell_Invs_tl.xtrx_private_label%TYPE,
146 sty_name okl_strm_type_v.NAME%TYPE,
147 contract_number okc_k_headers_b.contract_number%TYPE,
148 lsm_amount NUMBER,
149 xsi_id NUMBER,
150 xls_id NUMBER,
151 cnr_total NUMBER,
152 lln_total NUMBER
153 );
154
155 TYPE update_tbl_type IS TABLE OF update_rec_type
156 INDEX BY BINARY_INTEGER;
157
158
159 --MDOKAL
160 TYPE cnr_rec_type IS RECORD (
161 id NUMBER := null,
162 consolidated_invoice_number OKL_CNSLD_AR_HDRS_B.CONSOLIDATED_INVOICE_NUMBER%TYPE := '',
163 due_date OKL_CNSLD_AR_HDRS_B.DUE_DATE%TYPE := null,
164 trx_status_code OKL_CNSLD_AR_HDRS_B.TRX_STATUS_CODE%TYPE := '',
165 currency_code OKL_CNSLD_AR_HDRS_B.CURRENCY_CODE%TYPE := '',
166 set_of_books_id NUMBER := null,
167 ibt_id NUMBER := null,
168 ixx_id NUMBER := null,
169 irm_id NUMBER := null,
170 inf_id NUMBER := null,
171 amount NUMBER := null,
172 date_consolidated OKL_CNSLD_AR_HDRS_B.DATE_CONSOLIDATED%TYPE := null,
173 invoice_pull_yn OKL_CNSLD_AR_HDRS_B.INVOICE_PULL_YN%TYPE := '',
174 object_version_number NUMBER := null,
175 request_id NUMBER := null,
176 program_application_id NUMBER := null,
177 program_id NUMBER := null,
178 program_update_date OKL_CNSLD_AR_HDRS_B.PROGRAM_UPDATE_DATE%TYPE := null,
179 org_id NUMBER := null,
180 attribute_category OKL_CNSLD_AR_HDRS_B.ATTRIBUTE_CATEGORY%TYPE := '',
181 attribute1 OKL_CNSLD_AR_HDRS_B.ATTRIBUTE1%TYPE := '',
182 attribute2 OKL_CNSLD_AR_HDRS_B.ATTRIBUTE2%TYPE := '',
183 attribute3 OKL_CNSLD_AR_HDRS_B.ATTRIBUTE3%TYPE := '',
184 attribute4 OKL_CNSLD_AR_HDRS_B.ATTRIBUTE4%TYPE := '',
185 attribute5 OKL_CNSLD_AR_HDRS_B.ATTRIBUTE5%TYPE := '',
186 attribute6 OKL_CNSLD_AR_HDRS_B.ATTRIBUTE6%TYPE := '',
187 attribute7 OKL_CNSLD_AR_HDRS_B.ATTRIBUTE7%TYPE := '',
188 attribute8 OKL_CNSLD_AR_HDRS_B.ATTRIBUTE8%TYPE := '',
189 attribute9 OKL_CNSLD_AR_HDRS_B.ATTRIBUTE9%TYPE := '',
190 attribute10 OKL_CNSLD_AR_HDRS_B.ATTRIBUTE10%TYPE := '',
191 attribute11 OKL_CNSLD_AR_HDRS_B.ATTRIBUTE11%TYPE := '',
192 attribute12 OKL_CNSLD_AR_HDRS_B.ATTRIBUTE12%TYPE := '',
193 attribute13 OKL_CNSLD_AR_HDRS_B.ATTRIBUTE13%TYPE := '',
194 attribute14 OKL_CNSLD_AR_HDRS_B.ATTRIBUTE14%TYPE := '',
195 attribute15 OKL_CNSLD_AR_HDRS_B.ATTRIBUTE15%TYPE := '',
196 created_by NUMBER := null,
197 creation_date OKL_CNSLD_AR_HDRS_B.CREATION_DATE%TYPE := null,
198 last_updated_by NUMBER := null,
199 last_update_date OKL_CNSLD_AR_HDRS_B.LAST_UPDATE_DATE%TYPE := null,
200 last_update_login NUMBER := null,
201 legal_entity_id OKL_CNSLD_AR_HDRS_B.LEGAL_ENTITY_ID%TYPE := null); -- for LE Uptake project 08-11-2006
202
203 TYPE cnr_tl_rec_type IS RECORD (
204 id OKL_CNSLD_AR_HDRS_TL.ID%TYPE := null,
205 language OKL_CNSLD_AR_HDRS_TL.LANGUAGE%TYPE := '',
206 source_lang OKL_CNSLD_AR_HDRS_TL.SOURCE_LANG%TYPE := '',
207 sfwt_flag OKL_CNSLD_AR_HDRS_TL.SFWT_FLAG%TYPE := '',
208 private_label_logo_url OKL_CNSLD_AR_HDRS_TL.PRIVATE_LABEL_LOGO_URL%TYPE := '',
209 created_by OKL_CNSLD_AR_HDRS_TL.CREATED_BY%TYPE := null,
210 creation_date OKL_CNSLD_AR_HDRS_TL.CREATION_DATE%TYPE := null,
211 last_updated_by OKL_CNSLD_AR_HDRS_TL.LAST_UPDATED_BY%TYPE := null,
212 last_update_date OKL_CNSLD_AR_HDRS_TL.LAST_UPDATE_DATE%TYPE := null,
213 last_update_login OKL_CNSLD_AR_HDRS_TL.LAST_UPDATE_LOGIN%TYPE := null);
214
215 TYPE lln_rec_type IS RECORD (
216 id NUMBER := null,
217 sequence_number NUMBER := null,
218 lln_id_parent NUMBER := null,
219 kle_id NUMBER := null,
220 khr_id NUMBER := null,
221 cnr_id NUMBER := null,
222 ilt_id NUMBER := null,
223 line_type OKL_CNSLD_AR_LINES_B.LINE_TYPE%TYPE := '',
224 amount NUMBER := null,
225 tax_amount NUMBER := null,
226 object_version_number NUMBER := null,
227 request_id NUMBER := null,
228 program_application_id NUMBER := null,
229 program_id NUMBER := null,
230 program_update_date OKL_CNSLD_AR_LINES_B.PROGRAM_UPDATE_DATE%TYPE := null,
231 org_id NUMBER := null,
232 attribute_category OKL_CNSLD_AR_LINES_B.ATTRIBUTE_CATEGORY%TYPE := '',
233 attribute1 OKL_CNSLD_AR_LINES_B.ATTRIBUTE1%TYPE := '',
234 attribute2 OKL_CNSLD_AR_LINES_B.ATTRIBUTE2%TYPE := '',
235 attribute3 OKL_CNSLD_AR_LINES_B.ATTRIBUTE3%TYPE := '',
236 attribute4 OKL_CNSLD_AR_LINES_B.ATTRIBUTE4%TYPE := '',
237 attribute5 OKL_CNSLD_AR_LINES_B.ATTRIBUTE5%TYPE := '',
238 attribute6 OKL_CNSLD_AR_LINES_B.ATTRIBUTE6%TYPE := '',
239 attribute7 OKL_CNSLD_AR_LINES_B.ATTRIBUTE7%TYPE := '',
240 attribute8 OKL_CNSLD_AR_LINES_B.ATTRIBUTE8%TYPE := '',
241 attribute9 OKL_CNSLD_AR_LINES_B.ATTRIBUTE9%TYPE := '',
242 attribute10 OKL_CNSLD_AR_LINES_B.ATTRIBUTE10%TYPE := '',
243 attribute11 OKL_CNSLD_AR_LINES_B.ATTRIBUTE11%TYPE := '',
244 attribute12 OKL_CNSLD_AR_LINES_B.ATTRIBUTE12%TYPE := '',
245 attribute13 OKL_CNSLD_AR_LINES_B.ATTRIBUTE13%TYPE := '',
246 attribute14 OKL_CNSLD_AR_LINES_B.ATTRIBUTE14%TYPE := '',
247 attribute15 OKL_CNSLD_AR_LINES_B.ATTRIBUTE15%TYPE := '',
248 created_by NUMBER := null,
249 creation_date OKL_CNSLD_AR_LINES_B.CREATION_DATE%TYPE := null,
250 last_updated_by NUMBER := null,
251 last_update_date OKL_CNSLD_AR_LINES_B.LAST_UPDATE_DATE%TYPE := null,
252 last_update_login NUMBER := null);
253
254 TYPE lln_tl_rec_type IS RECORD (
255 id OKL_CNSLD_AR_LINES_TL.ID%TYPE := null,
256 language OKL_CNSLD_AR_LINES_TL.LANGUAGE%TYPE := '',
257 source_lang OKL_CNSLD_AR_LINES_TL.SOURCE_LANG%TYPE := '',
258 sfwt_flag OKL_CNSLD_AR_LINES_TL.SFWT_FLAG%TYPE := '',
259 created_by OKL_CNSLD_AR_LINES_TL.CREATED_BY%TYPE := null,
260 creation_date OKL_CNSLD_AR_LINES_TL.CREATION_DATE%TYPE := null,
261 last_updated_by OKL_CNSLD_AR_LINES_TL.LAST_UPDATED_BY%TYPE := null,
262 last_update_date OKL_CNSLD_AR_LINES_TL.LAST_UPDATE_DATE%TYPE := null,
263 last_update_login OKL_CNSLD_AR_LINES_TL.LAST_UPDATE_LOGIN%TYPE := null);
264
265 TYPE lsm_rec_type IS RECORD (
266 id NUMBER := null,
267 lln_id NUMBER := null,
268 sty_id NUMBER := null,
269 kle_id NUMBER := null,
270 khr_id NUMBER := null,
271 amount NUMBER := null,
272 LATE_INT_ASSESS_DATE OKL_CNSLD_AR_STRMS_B.LATE_INT_ASSESS_DATE%TYPE := null,
273 LATE_CHARGE_ASSESS_DATE OKL_CNSLD_AR_STRMS_B.LATE_CHARGE_ASSESS_DATE%TYPE := null,
274 LATE_CHARGE_ASS_YN OKL_CNSLD_AR_STRMS_B.LATE_CHARGE_ASS_YN%TYPE := '',
275 LATE_INT_ASS_YN OKL_CNSLD_AR_STRMS_B.LATE_INT_ASS_YN%TYPE := '',
276 tax_amount NUMBER := null,
277 receivables_invoice_id NUMBER := null,
278 object_version_number NUMBER := null,
279 pay_status_code OKL_CNSLD_AR_STRMS_B.PAY_STATUS_CODE%TYPE := '',
280 request_id NUMBER := null,
281 program_application_id NUMBER := null,
282 program_id NUMBER := null,
283 program_update_date OKL_CNSLD_AR_STRMS_B.PROGRAM_UPDATE_DATE%TYPE := null,
284 org_id NUMBER := null,
285 attribute_category OKL_CNSLD_AR_STRMS_B.ATTRIBUTE_CATEGORY%TYPE := '',
286 attribute1 OKL_CNSLD_AR_STRMS_B.ATTRIBUTE1%TYPE := '',
287 attribute2 OKL_CNSLD_AR_STRMS_B.ATTRIBUTE2%TYPE := '',
288 attribute3 OKL_CNSLD_AR_STRMS_B.ATTRIBUTE3%TYPE := '',
289 attribute4 OKL_CNSLD_AR_STRMS_B.ATTRIBUTE4%TYPE := '',
290 attribute5 OKL_CNSLD_AR_STRMS_B.ATTRIBUTE5%TYPE := '',
291 attribute6 OKL_CNSLD_AR_STRMS_B.ATTRIBUTE6%TYPE := '',
292 attribute7 OKL_CNSLD_AR_STRMS_B.ATTRIBUTE7%TYPE := '',
293 attribute8 OKL_CNSLD_AR_STRMS_B.ATTRIBUTE8%TYPE := '',
294 attribute9 OKL_CNSLD_AR_STRMS_B.ATTRIBUTE9%TYPE := '',
295 attribute10 OKL_CNSLD_AR_STRMS_B.ATTRIBUTE10%TYPE := '',
296 attribute11 OKL_CNSLD_AR_STRMS_B.ATTRIBUTE11%TYPE := '',
297 attribute12 OKL_CNSLD_AR_STRMS_B.ATTRIBUTE12%TYPE := '',
298 attribute13 OKL_CNSLD_AR_STRMS_B.ATTRIBUTE13%TYPE := '',
299 attribute14 OKL_CNSLD_AR_STRMS_B.ATTRIBUTE14%TYPE := '',
300 attribute15 OKL_CNSLD_AR_STRMS_B.ATTRIBUTE15%TYPE := '',
301 created_by NUMBER := null,
302 creation_date OKL_CNSLD_AR_STRMS_B.CREATION_DATE%TYPE := null,
303 last_updated_by NUMBER := null,
304 last_update_date OKL_CNSLD_AR_STRMS_B.LAST_UPDATE_DATE%TYPE := null,
305 last_update_login NUMBER := null,
306 investor_disb_status OKL_CNSLD_AR_STRMS_B.investor_disb_status%TYPE := '',
307 investor_disb_err_mg OKL_CNSLD_AR_STRMS_B.investor_disb_err_mg%TYPE := '',
308 sel_id NUMBER := null,
309 date_disbursed OKL_CNSLD_AR_STRMS_B.date_disbursed%TYPE := null );
310
311
312 TYPE lsm_tl_rec_type IS RECORD (
313 id OKL_CNSLD_AR_STRMS_TL.ID%TYPE := null,
314 language OKL_CNSLD_AR_STRMS_TL.LANGUAGE%TYPE := '',
315 source_lang OKL_CNSLD_AR_STRMS_TL.SOURCE_LANG%TYPE := '',
316 sfwt_flag OKL_CNSLD_AR_STRMS_TL.SFWT_FLAG%TYPE := '',
317 created_by OKL_CNSLD_AR_STRMS_TL.CREATED_BY%TYPE := null,
318 creation_date OKL_CNSLD_AR_STRMS_TL.CREATION_DATE%TYPE := null,
319 last_updated_by OKL_CNSLD_AR_STRMS_TL.LAST_UPDATED_BY%TYPE := null,
320 last_update_date OKL_CNSLD_AR_STRMS_TL.LAST_UPDATE_DATE%TYPE := null,
321 last_update_login OKL_CNSLD_AR_STRMS_TL.LAST_UPDATE_LOGIN%TYPE := null);
322
323 TYPE lln_tbl_type IS TABLE OF lln_rec_type
324 INDEX BY BINARY_INTEGER;
325
326 TYPE lln_tl_tbl_type IS TABLE OF lln_tl_rec_type
327 INDEX BY BINARY_INTEGER;
328
329 g_line_counter NUMBER := 0;
330 g_cons_bill_tbl NUMBER := 0;
331 g_lln_tbl lln_tbl_type;
332 g_lln_tl_tbl lln_tl_tbl_type;
333
334 TYPE lsm_tbl_type IS TABLE OF lsm_rec_type
335 INDEX BY BINARY_INTEGER;
336
337 TYPE lsm_tl_tbl_type IS TABLE OF lsm_tl_rec_type
338 INDEX BY BINARY_INTEGER;
339
340 g_lsm_tbl lsm_tbl_type;
341 g_lsm_tl_tbl lsm_tl_tbl_type;
342 g_stream_counter NUMBER := 0;
343
344 TYPE cnr_tbl_type IS TABLE OF cnr_rec_type
345 INDEX BY BINARY_INTEGER;
346
347 TYPE cnr_tl_tbl_type IS TABLE OF cnr_tl_rec_type
348 INDEX BY BINARY_INTEGER;
349
350 g_cnr_tbl cnr_tbl_type;
351 g_cnr_tl_tbl cnr_tl_tbl_type;
352 g_header_counter NUMBER := 0;
353
354
355 TYPE lln_total_rec IS RECORD (
356 cnr_id NUMBER,
357 lln_id NUMBER,
358 amount NUMBER
359 );
360
361 TYPE lln_total_tbl IS TABLE OF lln_total_rec
362 INDEX BY BINARY_INTEGER;
363
364
365 g_last_rec BOOLEAN :=FALSE;
366 g_cnr_total NUMBER := 0;
367 g_prev_cnr_id NUMBER;
368
369 g_lln_total NUMBER := 0;
370 g_prev_lln_id NUMBER;
371
372 TYPE xsi_rec IS RECORD (
373 id okl_ext_sell_invs_b.id%type,
374 trx_status_code okl_ext_sell_invs_b.trx_status_code%type,
375 xtrx_invoice_pull_yn okl_ext_sell_invs_b. xtrx_invoice_pull_yn%type);
376
377 TYPE xsi_tbl IS TABLE OF xsi_rec
378 INDEX BY BINARY_INTEGER;
379
380 g_xsi_tbl xsi_tbl;
381 g_xsi_counter NUMBER := 0;
382
383 TYPE xls_rec IS RECORD (
384 id okl_xtl_sell_invs_b.id%type,
385 lsm_id okl_xtl_sell_invs_b.lsm_id%type,
386 xtrx_cons_line_number okl_xtl_sell_invs_b.xtrx_cons_line_number%type,
387 xtrx_cons_stream_id okl_xtl_sell_invs_b.xtrx_cons_stream_id%type);
388
389 TYPE xls_tbl IS TABLE OF xls_rec
390 INDEX BY BINARY_INTEGER;
391
392 g_xls_tbl xls_tbl;
393 g_xls_counter NUMBER := 0;
394
395
396 TYPE xsi_tl_rec IS RECORD (
397 id okl_ext_sell_invs_tl.id%type,
398 xtrx_cons_invoice_number okl_ext_sell_invs_tl.xtrx_cons_invoice_number%type,
399 xtrx_format_type okl_ext_sell_invs_tl.xtrx_format_type%type,
400 xtrx_private_label okl_ext_sell_invs_tl.xtrx_private_label%type);
401
402 TYPE xsi_tl_tbl IS TABLE OF xsi_tl_rec
403 INDEX BY BINARY_INTEGER;
404
405 g_xsi_tl_tbl xsi_tl_tbl;
406 g_xsi_tl_counter NUMBER := 0;
407
408 TYPE xls_tl_rec IS RECORD (
409 id okl_xtl_sell_invs_tl.id%type,
410 xtrx_contract okl_xtl_sell_invs_tl.xtrx_contract%type,
411 xtrx_asset okl_xtl_sell_invs_tl.xtrx_asset%type,
412 xtrx_stream_type okl_xtl_sell_invs_tl.xtrx_stream_type%type,
413 xtrx_stream_group okl_xtl_sell_invs_tl.xtrx_stream_group%type);
414
415 TYPE xls_tl_tbl IS TABLE OF xls_tl_rec
416 INDEX BY BINARY_INTEGER;
417
418 g_xls_tl_tbl xls_tl_tbl;
419 g_xls_tl_counter NUMBER := 0;
420
421 TYPE asset_rec IS RECORD (
422 id okc_k_lines_b.id%type,
423 name okc_k_lines_tl.name%type);
424
425 TYPE asset_tbl IS TABLE OF asset_rec
426 INDEX BY BINARY_INTEGER;
427
428 -- fmiao 5232919 change start
429 -- Defining global table records for OKL_INV_MSSG_ATT records
430 g_imav_tbl OKL_IMA_PVT.imav_tbl_type;
431 g_imav_counter NUMBER := 0;
432
433 TYPE msg_rec IS RECORD (
434 id okl_invoice_mssgs_v.id%type,
435 priority okl_invoice_mssgs_v.priority%type,
436 pkg_name okl_invoice_mssgs_v.pkg_name%type,
437 proc_name okl_invoice_mssgs_v.proc_name%type,
438 start_date okl_invoice_mssgs_v.start_date%TYPE,
439 end_date okl_invoice_mssgs_v.end_date%TYPE);
440 -- fmiao 5232919 change end
441
442 TYPE msg_tbl IS TABLE OF msg_rec
443 INDEX BY BINARY_INTEGER;
444
445 g_msg_tbl msg_tbl;
446 g_inv_msg VARCHAR2(25) := 'TRUE';
447
448 PROCEDURE create_cons_bill(
449 p_contract_number IN VARCHAR2 DEFAULT NULL,
450 p_api_version IN NUMBER,
451 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
452 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
453 p_inv_msg IN VARCHAR2 DEFAULT 'TRUE',
454 p_assigned_process IN VARCHAR2 DEFAULT NULL,
455 x_return_status OUT NOCOPY VARCHAR2,
456 x_msg_count OUT NOCOPY NUMBER,
457 x_msg_data OUT NOCOPY VARCHAR2);
458
459 PROCEDURE process_cons_bill_tbl(
460 p_contract_number IN VARCHAR2 DEFAULT NULL,
461 p_api_version IN NUMBER,
462 p_init_msg_list IN VARCHAR2 DEFAULT Okl_Api.G_FALSE,
463 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
464 x_return_status OUT NOCOPY VARCHAR2,
465 x_msg_count OUT NOCOPY NUMBER,
466 x_msg_data OUT NOCOPY VARCHAR2,
467 p_cons_bill_tbl IN OUT NOCOPY cons_bill_tbl_type,
468 p_saved_bill_rec IN OUT NOCOPY saved_bill_rec_type,
469 p_update_tbl IN OUT NOCOPY update_tbl_type);
470
471 FUNCTION get_invoice_group(p_khr_id NUMBER)
472 RETURN VARCHAR2;
473
474 PRAGMA RESTRICT_REFERENCES(get_invoice_group, WNDS);
475
476 END OKL_BILLING_CONTROLLER_PVT;