[Home] [Help]
PACKAGE BODY: APPS.OKL_CONS_BILL
Source
1 PACKAGE BODY Okl_Cons_Bill AS
2 /* $Header: OKLRKONB.pls 120.27.12010000.2 2008/12/12 20:28:19 cklee ship $ */
3
4 G_MODULE VARCHAR2(255) := 'okl.stream.esg.okl_esg_transport_pvt';
5 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
7 -- Start of wraper code generated automatically by Debug code generator
8 L_MODULE VARCHAR2(40) := 'LEASE.RECEIVABLES.BILLING';
9 L_DEBUG_ENABLED VARCHAR2(10);
10 -- L_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
11 L_LEVEL_PROCEDURE NUMBER;
12 IS_DEBUG_PROCEDURE_ON BOOLEAN;
13 -- End of wraper code generated automatically by Debug code generator
14
15 --This function checks for the existence of an consolidated invoice
16 -- in okl_cnsld_ar_hdrs_v
17
18 --This procedure creates a new consolidated invoice header
19 --based on the parameters passed
20
21 PROCEDURE process_break(
22 p_contract_number IN VARCHAR2,
23 p_commit IN VARCHAR2,
24 saved_bill_rec IN OUT NOCOPY saved_bill_rec_type,
25 l_update_tbl IN OUT NOCOPY update_tbl_type)
26 IS
27
28 l_old_cnr_id NUMBER;
29 l_old_lln_id NUMBER;
30 l_cnr_amount okl_cnsld_ar_hdrs_v.amount%TYPE;
31 l_lln_amount okl_cnsld_ar_lines_v.amount%TYPE;
32
33 CURSOR cnr_amt_csr ( p_cnr_id IN NUMBER ) IS
34 SELECT SUM(lsm.amount)
35 FROM okl_cnsld_ar_hdrs_b cnr,
36 okl_cnsld_ar_lines_b lln,
37 okl_cnsld_ar_strms_b lsm
38 WHERE cnr.id = p_cnr_id AND
39 cnr.id = lln.cnr_id AND
40 lln.id = lsm.lln_id;
41
42 CURSOR lln_amt_csr ( p_lln_id IN NUMBER ) IS
43 SELECT SUM(lsm.amount)
44 FROM okl_cnsld_ar_lines_b lln,
45 okl_cnsld_ar_strms_b lsm
46 WHERE lln.id = p_lln_id AND
47 lln.id = lsm.lln_id;
48
49
50 BEGIN
51 IF (G_DEBUG_ENABLED = 'Y') THEN
52 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
53 END IF;
54
55 IF (L_DEBUG_ENABLED='Y' and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
56 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'okl_cons_bill'
57 ,'Process_Break Begin(+)');
58 END IF;
59
60 -- ------------------------------------
61 -- Start header break detection logic
62 -- ------------------------------------
63
64 -- If there was no error processing any records then
65 IF l_update_tbl.COUNT > 0 THEN
66
67 FOR m in l_update_tbl.FIRST..l_update_tbl.LAST LOOP
68 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
69 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.cnr_id '||l_update_tbl(m).cnr_id);
70 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.cons_inv_number '||l_update_tbl(m).cons_inv_number);
71 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.lln_id '||l_update_tbl(m).lln_id);
72 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.lsm_id '||l_update_tbl(m).lsm_id);
73 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.asset_number '||l_update_tbl(m).asset_number);
74 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.invoice_format '||l_update_tbl(m).invoice_format);
75 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.line_type '||l_update_tbl(m).line_type);
76 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.sty_name '||l_update_tbl(m).sty_name);
77 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.contract_number '||l_update_tbl(m).contract_number);
78 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.lsm_amount '||l_update_tbl(m).lsm_amount);
79 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.xsi_id '||l_update_tbl(m).xsi_id);
80 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'l_update_tbl.xls_id '||l_update_tbl(m).xls_id);
81 END IF;
82 END LOOP;
83
84 IF saved_bill_rec.l_overall_status IS NULL THEN
85
86 l_old_cnr_id := -9;
87 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
88 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'Updating Consolidated Invoice Header');
89 END IF;
90 FOR m in l_update_tbl.FIRST..l_update_tbl.LAST LOOP
91 IF l_update_tbl(m).cnr_id <> l_old_cnr_id THEN
92 l_cnr_amount := NULL;
93 OPEN cnr_amt_csr ( l_update_tbl(m).cnr_id );
94 FETCH cnr_amt_csr INTO l_cnr_amount;
95 CLOSE cnr_amt_csr;
96
97 UPDATE okl_cnsld_ar_hdrs_b
98 SET trx_status_code = 'PROCESSED',
99 amount = l_cnr_amount,
100 last_update_date = sysdate,
101 last_updated_by = Fnd_Global.USER_ID,
102 last_update_login = Fnd_Global.LOGIN_ID
103 WHERE id = l_update_tbl(m).cnr_id;
104
105 l_old_cnr_id := l_update_tbl(m).cnr_id;
106 END IF;
107 END LOOP;
108 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
109 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'Done updating Consolidated Invoice Header');
110 END IF;
111
112 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
113 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'Updating Consolidated Invoice Line');
114 END IF;
115 l_old_lln_id := -9;
116 FOR m in l_update_tbl.FIRST..l_update_tbl.LAST LOOP
117 IF l_update_tbl(m).lln_id <> l_old_lln_id THEN
118 l_lln_amount := NULL;
119 OPEN lln_amt_csr( l_update_tbl(m).lln_id );
120 FETCH lln_amt_csr INTO l_lln_amount;
121 CLOSE lln_amt_csr;
122
123 UPDATE okl_cnsld_ar_lines_b
124 SET amount = l_lln_amount,
125 last_update_date = sysdate,
126 last_updated_by = Fnd_Global.USER_ID,
127 last_update_login = Fnd_Global.LOGIN_ID
128 WHERE id = l_update_tbl(m).lln_id;
129
130 l_old_lln_id := l_update_tbl(m).lln_id;
131 END IF;
132 END LOOP;
133 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
134 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'Done updating Consolidated Invoice Line');
135 END IF;
136
137 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
138 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'Updating External Transaction Header');
139 END IF;
140 IF p_contract_number IS NULL THEN
141 FOR m in l_update_tbl.FIRST..l_update_tbl.LAST LOOP
142 UPDATE Okl_Ext_Sell_Invs_b
143 SET TRX_STATUS_CODE = 'WORKING',
144 XTRX_INVOICE_PULL_YN = 'Y',
145 last_update_date = sysdate,
146 last_updated_by = Fnd_Global.USER_ID,
147 last_update_login = Fnd_Global.LOGIN_ID
148 WHERE id = l_update_tbl(m).xsi_id;
149 END LOOP;
150 ELSE
151 FOR m in l_update_tbl.FIRST..l_update_tbl.LAST LOOP
152 UPDATE Okl_Ext_Sell_Invs_b
153 SET TRX_STATUS_CODE = 'ENTERED',
154 XTRX_INVOICE_PULL_YN = 'Y',
155 last_update_date = sysdate,
156 last_updated_by = Fnd_Global.USER_ID,
157 last_update_login = Fnd_Global.LOGIN_ID
158 WHERE id = l_update_tbl(m).xsi_id;
159 END LOOP;
160 END IF;
161 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
162 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'Done updating External Transaction Header');
163 END IF;
164
165 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
166 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'Updating External Transaction Line');
167 END IF;
168 FOR m in l_update_tbl.FIRST..l_update_tbl.LAST LOOP
169 UPDATE Okl_Ext_Sell_Invs_tl
170 SET XTRX_CONS_INVOICE_NUMBER = l_update_tbl(m).cons_inv_number,
171 XTRX_FORMAT_TYPE = l_update_tbl(m).invoice_format,
172 XTRX_PRIVATE_LABEL = l_update_tbl(m).private_label,
173 last_update_date = sysdate,
174 last_updated_by = Fnd_Global.USER_ID,
175 last_update_login = Fnd_Global.LOGIN_ID
176 WHERE id = l_update_tbl(m).xsi_id;
177
178 UPDATE Okl_Xtl_Sell_Invs_b
179 SET LSM_ID = l_update_tbl(m).LSM_ID,
180 -- XTRX_CONS_LINE_NUMBER = l_update_tbl(m).line_number,
181 XTRX_CONS_STREAM_ID = l_update_tbl(m).lsm_id,
182 last_update_date = sysdate,
183 last_updated_by = Fnd_Global.USER_ID,
184 last_update_login = Fnd_Global.LOGIN_ID
185 WHERE id = l_update_tbl(m).xls_id;
186
187 UPDATE Okl_Xtl_Sell_Invs_tl
188 SET XTRX_CONTRACT = l_update_tbl(m).contract_number,
189 XTRX_ASSET = l_update_tbl(m).asset_number,
190 XTRX_STREAM_TYPE = l_update_tbl(m).sty_name,
191 XTRX_STREAM_GROUP = l_update_tbl(m).line_type,
192 last_update_date = sysdate,
193 last_updated_by = Fnd_Global.USER_ID,
194 last_update_login = Fnd_Global.LOGIN_ID
195 WHERE id = l_update_tbl(m).xls_id;
196 END LOOP;
197 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
198 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'Done updating External Transaction Line');
199 END IF;
200 ELSE -- goes with check of overall status
201
202 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
203 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'Updating Concolidated Header Status to Error');
204 END IF;
205 l_old_cnr_id := -9;
206 FOR m in l_update_tbl.FIRST..l_update_tbl.LAST LOOP
207 IF l_update_tbl(m).cnr_id <> l_old_cnr_id THEN
208
209 UPDATE okl_cnsld_ar_hdrs_b
210 SET trx_status_code = 'ERROR',
211 amount = l_cnr_amount,
212 last_update_date = sysdate,
213 last_updated_by = Fnd_Global.USER_ID,
214 last_update_login = Fnd_Global.LOGIN_ID
215 WHERE id = l_update_tbl(m).cnr_id;
216
217 l_old_cnr_id := l_update_tbl(m).cnr_id;
218 END IF;
219 END LOOP;
220
221 -- ----------------------------------------------
222 -- Delete LSM LLN and CNR records
223 -- ----------------------------------------------
224 FOR m in l_update_tbl.FIRST..l_update_tbl.LAST LOOP
225 DELETE FROM okl_cnsld_ar_strms_b
226 WHERE id = l_update_tbl(m).lsm_id;
227
228 DELETE FROM okl_cnsld_ar_strms_tl
229 WHERE id = l_update_tbl(m).lsm_id;
230
231 END LOOP;
232
233 FOR m in l_update_tbl.FIRST..l_update_tbl.LAST LOOP
234 DELETE FROM okl_cnsld_ar_lines_b
235 WHERE id = l_update_tbl(m).lln_id;
236
237 DELETE FROM okl_cnsld_ar_lines_tl
238 WHERE id = l_update_tbl(m).lln_id;
239 END LOOP;
240
241 FOR m in l_update_tbl.FIRST..l_update_tbl.LAST LOOP
242 DELETE FROM okl_cnsld_ar_hdrs_b
243 WHERE id = l_update_tbl(m).cnr_id;
244
245 DELETE FROM okl_cnsld_ar_hdrs_tl
246 WHERE id = l_update_tbl(m).cnr_id;
247
248 END LOOP;
249 END IF;
250
251 -- ------------------------------------
252 -- End header break detection logic
253 -- ------------------------------------
254 END IF;-- If any records exist for updating
255
256 IF saved_bill_rec.l_commit_cnt > G_Commit_Max THEN
257 IF FND_API.To_Boolean( p_commit ) THEN
258 COMMIT;
259 END IF;
260 saved_bill_rec.l_commit_cnt := 0;
261 END IF;
262
263 IF (L_DEBUG_ENABLED='Y' and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
264 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'okl_cons_bill'
265 ,'Process_Break End(-)');
266 END IF;
267
268 EXCEPTION
269 WHEN OTHERS THEN
270 IF (L_DEBUG_ENABLED='Y' and FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
271 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'okl_cons_bill',
272 'EXCEPTION (OTHERS) :'||SQLERRM);
273 END IF;
274
275 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
276 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'EXCEPTION in Procedure Process_Break: '||SQLERRM);
277 END IF;
278 END process_break;
279
280 FUNCTION get_invoice_group(p_khr_id NUMBER)
281 RETURN VARCHAR2 IS
282 CURSOR grp_csr ( cp_khr_id NUMBER ) IS
283 select RULE_INFORMATION1
284 from okc_rule_groups_v rgp,
285 okc_rules_v rul
286 where rgp.dnz_chr_id = cp_khr_id AND
287 rgp.chr_id = rgp.dnz_chr_id AND
288 rgp.id = rul.rgp_id AND
289 rgp.cle_id IS NULL AND
290 rgp.rgd_code = 'LABILL' AND
291 rul.rule_information_category = 'LAINVD';
292
293 l_grp okc_rules_v.rule_information1%type:= 'NONE';
294
295 BEGIN
296
297 OPEN grp_csr(p_khr_id);
298 FETCH grp_csr INTO l_grp;
299 CLOSE grp_csr;
300
301 return l_grp;
302
303 END get_invoice_group;
304
305 PROCEDURE create_new_invoice(
306 p_ibt_id IN NUMBER,
307 p_ixx_id IN NUMBER,
308 p_currency_code IN VARCHAR2,
309 p_irm_id IN NUMBER,
310 p_inf_id IN NUMBER,
311 p_set_of_books_id IN NUMBER,
312 p_private_label IN VARCHAR2,
313 p_date_consolidated IN DATE,
314 p_org_id IN NUMBER,
315 p_legal_entity_id IN NUMBER, -- for LE Uptake project 08-11-2006
316 x_cnr_id OUT NOCOPY NUMBER,
317 x_cons_inv_num OUT NOCOPY VARCHAR2
318 )
319 IS
320
321 x_cnrv_rec Okl_Cnr_Pvt.cnrv_rec_type;
322 x_cnrv_tbl Okl_Cnr_Pvt.cnrv_tbl_type;
323
324 p_cnrv_rec Okl_Cnr_Pvt.cnrv_rec_type;
325 p_cnrv_tbl Okl_Cnr_Pvt.cnrv_tbl_type;
326
327 p_imav_rec Okl_ima_pvt.imav_rec_type;
328 x_imav_rec Okl_ima_pvt.imav_rec_type;
329
330
331 p_api_version NUMBER := 1.0;
332 p_init_msg_list VARCHAR2(1) := Okl_Api.g_false;
333 x_return_status VARCHAR2(1);
334 x_msg_count NUMBER;
335 x_msg_data VARCHAR2(2000);
336 -- For automatic generation of sequence numbers from
337 -- the database
338 l_Invoice_Number NUMBER := '';
339 l_document_category VARCHAR2(100):= 'OKL Lease Receipt Invoices';
340 l_application_id NUMBER(3) := 540 ;
341 x_dbseqnm VARCHAR2(100):= NULL;
342 x_dbseqid NUMBER;
343
344 -- fmiao 5232919 modification start
345 -- Added clause to restrict based on consolidated invoice date
346 CURSOR msg_csr (cp_consolidated_inv_date DATE) IS
347 SELECT id,
348 priority,
349 pkg_name,
350 proc_name
351 FROM okl_invoice_mssgs_v
352 WHERE cp_consolidated_inv_date
353 BETWEEN NVL(START_DATE,cp_consolidated_inv_date) AND
354 NVL(END_DATE,cp_consolidated_inv_date);
355 -- fmiao 5232919 change end
356
357 l_save_priority okl_invoice_mssgs_v.priority%TYPE;
358 l_save_ims_id okl_invoice_mssgs_v.id%TYPE;
359
360 l_priority okl_invoice_mssgs_v.priority%TYPE;
361 l_pkg_name okl_invoice_mssgs_v.pkg_name%TYPE;
362 l_proc_name okl_invoice_mssgs_v.proc_name%TYPE;
363
364 l_bind_proc VARCHAR2(3000);
365 l_msg_return VARCHAR2(1); --BOOLEAN;
366 l_ims_id okl_invoice_mssgs_v.id%TYPE;
367
368
369 BEGIN
370 IF (G_DEBUG_ENABLED = 'Y') THEN
371 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
372 END IF;
373 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
374 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** HEADER RECORD CREATION FOR : ***');
375 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** ++++++++++++++++++++++++++++ ***');
376 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> CUSTOMER_ID: '||p_ixx_id||' CURRENCY: '||p_currency_code);
377 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> BILL_TO_SITE: '||p_ibt_id||' PAYMENT_METHOD: '||p_irm_id);
378 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> PRIVATE_LABEL: '||p_private_label||' DATE_CONSOLIDATED: '||p_date_consolidated);
379 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> INF_ID: '||p_inf_id||' SET_OF_BOOKS_ID: '||p_set_of_books_id);
380 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> ORG_ID: '||p_org_id);
381 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** ++++++++++++++++++++++++++++ ***');
382 END IF;
383
384 p_cnrv_rec.IBT_ID := p_ibt_id;
385 p_cnrv_rec.IXX_ID := p_ixx_id;
386 p_cnrv_rec.CURRENCY_CODE := p_currency_code;
387 p_cnrv_rec.IRM_ID := p_irm_id;
388 p_cnrv_rec.INF_ID := p_inf_id;
389 p_cnrv_rec.SET_OF_BOOKS_ID := p_set_of_books_id;
390 p_cnrv_rec.ORG_ID := p_org_id;
391 p_cnrv_rec.LEGAL_ENTITY_ID := p_legal_entity_id; -- for LE Uptake project 08-11-2006
392 -- Added to support date in the consolidation hierarchy
393 -- 02/28/2002
394 p_cnrv_rec.date_consolidated := p_date_consolidated;
395
396
397 -- DB generated sequence number for the Consolidated Invoice
398 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
399 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '====> Generating Cons Bill SEQUENCE');
400 END IF;
401 l_Invoice_Number := Fnd_Seqnum.get_next_sequence (l_application_id,
402 l_document_category,
403 p_set_of_books_id,
404 'A',
405 SYSDATE,
406 x_dbseqnm,
407 x_dbseqid);
408
409 p_cnrv_rec.CONSOLIDATED_INVOICE_NUMBER := TO_CHAR(l_invoice_number);
410
411 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
412 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '====> Cons Bill Number: '||p_cnrv_rec.CONSOLIDATED_INVOICE_NUMBER);
413 END IF;
414
415 p_cnrv_rec.INVOICE_PULL_YN := 'Y';
416 p_cnrv_rec.PRIVATE_LABEL_LOGO_URL := p_private_label;
417 p_cnrv_rec.trx_status_code := 'SUBMITTED';
418
419 -- Start of wraper code generated automatically by Debug code generator for Okl_Cnsld_Ar_Hdrs_Pub.INSERT_CNSLD_AR_HDRS
420 IF(L_DEBUG_ENABLED='Y') THEN
421 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
422 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
423 END IF;
424 IF(IS_DEBUG_PROCEDURE_ON) THEN
425 BEGIN
426 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRKONB.pls call Okl_Cnsld_Ar_Hdrs_Pub.INSERT_CNSLD_AR_HDRS ');
427 END;
428 END IF;
429 Okl_Cnsld_Ar_Hdrs_Pub.INSERT_CNSLD_AR_HDRS(
430 p_api_version
431 ,p_init_msg_list
432 ,x_return_status
433 ,x_msg_count
434 ,x_msg_data
435 ,p_cnrv_rec
436 ,x_cnrv_rec
437 );
438 IF(IS_DEBUG_PROCEDURE_ON) THEN
439 BEGIN
440 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRKONB.pls call Okl_Cnsld_Ar_Hdrs_Pub.INSERT_CNSLD_AR_HDRS ');
441 END;
442 END IF;
443 -- End of wraper code generated automatically by Debug code generator for Okl_Cnsld_Ar_Hdrs_Pub.INSERT_CNSLD_AR_HDRS
444
445 IF ( x_return_status = 'S' ) THEN
446 BEGIN
447 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
448 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'====> Consolidated Header Record Created');
449 END IF;
450
451 -- Find message with the highest priority
452 l_save_priority := NULL;
453 -- fmiao - Bug#5232919 - Modified - Start
454 -- Added clause to restrict based on consolidated invoice date
455 FOR msg_csr_rec IN msg_csr(TRUNC(p_date_consolidated)) LOOP
456 --fmiao - Bug#5232919 - Modified - end
457 l_ims_id := msg_csr_rec.id;
458 l_priority := msg_csr_rec.priority;
459 l_pkg_name := msg_csr_rec.pkg_name;
460 l_proc_name := msg_csr_rec.proc_name;
461
462 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
463 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'====> IMS_ID: '||l_ims_id);
464 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'====> PKG: '||l_pkg_name);
465 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'====> PROC: '||l_proc_name);
466 END IF;
467
468 l_bind_proc := 'BEGIN OKL_QUAL_INV_MSGS.'||l_proc_name||'(:1,:2); END;';
469
470 -- EXECUTE IMMEDIATE l_bind_proc USING IN x_cnrv_rec.id RETURNING INTO l_msg_return;
471
472 BEGIN
473 EXECUTE IMMEDIATE l_bind_proc USING IN x_cnrv_rec.id, OUT l_msg_return;
474 EXCEPTION
475 WHEN OTHERS THEN
476 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
477 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Invoice Message error -- '||SQLERRM);
478 END IF;
479 END;
480
481
482 IF (l_msg_return = '1' ) THEN
483 IF l_save_priority IS NULL THEN
484 l_save_priority := l_priority;
485 l_save_ims_id := l_ims_id;
486 ELSE
487 IF (l_priority < l_save_priority) THEN
488 l_save_priority := l_priority;
489 l_save_ims_id := l_ims_id;
490 END IF;
491 END IF;
492 END IF;
493 END LOOP;
494 -- Create Intersection Record
495 IF (l_save_priority IS NOT NULL) THEN
496 p_imav_rec.CNR_ID := x_cnrv_rec.id;
497 p_imav_rec.IMS_ID := l_save_ims_id;
498
499 -- Start of wraper code generated automatically by Debug code generator for okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT
500 IF(IS_DEBUG_PROCEDURE_ON) THEN
501 BEGIN
502 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
503 END;
504 END IF;
505 okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT(
506 p_api_version
507 ,p_init_msg_list
508 ,x_return_status
509 ,x_msg_count
510 ,x_msg_data
511 ,p_imav_rec
512 ,x_imav_rec
513 );
514 IF(IS_DEBUG_PROCEDURE_ON) THEN
515 BEGIN
516 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRKONB.pls call okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT ');
517 END;
518 END IF;
519 -- End of wraper code generated automatically by Debug code generator for okl_inv_mssg_att_pub.INSERT_INV_MSSG_ATT
520 IF ( x_return_status = 'S' ) THEN
521 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
522 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'====> Message Created.');
523 END IF;
524 ELSE
525 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
526 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'*=> FAILED:Message Creation');
527 END IF;
528 END IF;
529 ELSE
530 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
531 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'====> NO Message Qualified');
532 END IF;
533 END IF;
534 EXCEPTION
535 WHEN OTHERS THEN
536 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
537 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'====> PROBLEMS WITH MESSAGING');
538 END IF;
539 END;
540 ELSE
541 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
542 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'*=> FAILED: Consolidated Header NOT Created.');
543 END IF;
544 END IF;
545
546 x_cnr_id := x_cnrv_rec.id;
547 x_cons_inv_num := p_cnrv_rec.CONSOLIDATED_INVOICE_NUMBER;
548
549 EXCEPTION
550 --Seed FND_MESSAGE like 'Could NOT CREATE Header RECORD'
551 WHEN OTHERS THEN
552 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
553 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'*=> Error Message(H1): '||SQLERRM);
554 END IF;
555 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME,
556 p_msg_name => G_OTHERS);
557 END create_new_invoice;
558
559 --This function checks for the existence of an consolidated invoice line
560 -- in okl_cnsld_ar_lines_v.This function is called when the
561 -- group_by_assets flag is set to 'Y'
562 PROCEDURE line_exist (p_cnr_id IN NUMBER,
563 p_khr_id IN NUMBER,
564 p_kle_id IN NUMBER,
565 p_ilt_id IN NUMBER,
566 p_sequence_number IN NUMBER,
567 p_group_by_contract_yn IN VARCHAR2,
568 p_group_by_assets_yn IN VARCHAR2,
569 x_lln_id OUT NOCOPY NUMBER,
570 exists_flag OUT NOCOPY VARCHAR2
571 )
572 IS
573
574 CURSOR check_line1 ( p_cnr_id NUMBER, p_khr_id NUMBER, p_ilt_id NUMBER, p_sequence_number NUMBER ) IS
575 SELECT id
576 FROM okl_cnsld_ar_lines_v
577 WHERE cnr_id = p_cnr_id AND
578 khr_id = p_khr_id AND
579 ilt_id = p_ilt_id AND
580 sequence_number = p_sequence_number;
581
582 CURSOR check_line2 (p_cnr_id NUMBER, p_khr_id NUMBER, p_kle_id NUMBER, p_ilt_id NUMBER ) IS
583 SELECT id
584 FROM okl_cnsld_ar_lines_v
585 WHERE cnr_id = p_cnr_id AND
586 khr_id = p_khr_id AND
587 kle_id = p_kle_id AND
588 ilt_id = p_ilt_id AND
589 sequence_number = p_sequence_number;
590
591 CURSOR check_line3 ( p_cnr_id NUMBER, p_khr_id NUMBER, p_ilt_id NUMBER, p_sequence_number NUMBER ) IS
592 SELECT id
593 FROM okl_cnsld_ar_lines_v
594 WHERE cnr_id = p_cnr_id AND
595 khr_id = p_khr_id AND
596 kle_id IS NULL AND
597 ilt_id = p_ilt_id AND
598 sequence_number = p_sequence_number;
599
600 CURSOR check_line4 ( p_cnr_id NUMBER, p_ilt_id NUMBER, p_sequence_number NUMBER ) IS
601 SELECT id
602 FROM okl_cnsld_ar_lines_v
603 WHERE cnr_id = p_cnr_id AND
604 ilt_id = p_ilt_id AND
605 sequence_number = p_sequence_number;
606
607
608 BEGIN
609 IF (G_DEBUG_ENABLED = 'Y') THEN
610 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
611 END IF;
612 -- Prime Local Variable
613 exists_flag := 'Y';
614 x_lln_id := NULL;
615
616 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
617 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** CONSOLIDATED LINES CHECK: if a line exists for the following: ***');
618 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> CNR_ID: '||p_cnr_id);
619 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> KHR_ID: '||p_khr_id);
620 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> KLE_ID: '||p_kle_id);
621 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> ILT_ID: '||p_ilt_id);
622 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> SEQUENCE_NUMBER: '||p_sequence_number);
623 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> GROUP_BY_CONTRACT_YN: '||p_group_by_contract_yn);
624 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> GROUP_BY_ASSETS_YN: '||p_group_by_assets_yn);
625 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> GROUP_BY_ASSETS_YN: '||p_group_by_assets_yn);
626 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** End Invoice Group Details ***');
627 END IF;
628
629
630 --Consider making this a cursor
631 IF p_group_by_contract_yn = 'Y' THEN
632 IF p_group_by_assets_yn = 'Y' THEN
633
634 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
635 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '====> Using SQL in check_line1 ');
636 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> SELECT id FROM okl_cnsld_ar_lines_v');
637 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> WHERE cnr_id = '||p_cnr_id||' AND ');
638 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> khr_id = '||p_khr_id||' AND ');
639 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> ilt_id = '||p_ilt_id||' AND ');
640 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> sequence_number = '||p_sequence_number||';');
641 END IF;
642
643
644 OPEN check_line1 ( p_cnr_id, p_khr_id, p_ilt_id, p_sequence_number );
645 FETCH check_line1 INTO x_lln_id;
646 CLOSE check_line1;
647
648 ELSE
649 IF p_kle_id IS NOT NULL THEN
650 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
651 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '====> Using SQL in check_line2 ');
652 END IF;
653
654 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
655 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> SELECT id FROM okl_cnsld_ar_lines_v');
656 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> WHERE cnr_id = '||p_cnr_id||' AND ');
657 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> khr_id = '||p_khr_id||' AND ');
658 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> kle_id = '||p_kle_id||' AND ');
659 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> ilt_id = '||p_ilt_id||' AND ');
660 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> sequence_number = '||p_sequence_number||';');
661 END IF;
662
663 OPEN check_line2 ( p_cnr_id, p_khr_id, p_kle_id, p_ilt_id );
664 FETCH check_line2 INTO x_lln_id;
665 CLOSE check_line2;
666
667 ELSE
668 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
669 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '====> Using SQL in check_line3 ');
670 END IF;
671
672 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
673 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> SELECT id FROM okl_cnsld_ar_lines_v');
674 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> WHERE cnr_id = '||p_cnr_id||' AND ');
675 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> khr_id = '||p_khr_id||' AND ');
676 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> kle_id is null AND ');
677 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> ilt_id = '||p_ilt_id||' AND ');
678 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> sequence_number = '||p_sequence_number||';');
679 END IF;
680
681 OPEN check_line3 ( p_cnr_id, p_khr_id, p_ilt_id, p_sequence_number );
682 FETCH check_line3 INTO x_lln_id;
683 CLOSE check_line3;
684
685 END IF;
686 END IF;
687 ELSE
688 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
689 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '====> Using SQL in check_line4 ');
690 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> SELECT id FROM okl_cnsld_ar_lines_v');
691 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> WHERE cnr_id = '||p_cnr_id||' AND ');
692 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> ilt_id = '||p_ilt_id||' AND ');
693 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'=***********> sequence_number = '||p_sequence_number||';');
694 END IF;
695
696 OPEN check_line4 ( p_cnr_id, p_ilt_id, p_sequence_number );
697 FETCH check_line4 INTO x_lln_id;
698 CLOSE check_line4;
699
700 END IF;
701
702 IF ( x_lln_id IS NULL ) THEN
703 exists_flag := 'N';
704 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
705 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '====> No Line Exists for this combination. ');
706 END IF;
707 ELSE
708 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
709 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '====> Found an existing line for this combination. The id is '||x_lln_id);
710 END IF;
711 END IF;
712
713 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
714 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** END CONSOLIDATED LINES CHECK ***');
715 END IF;
716 EXCEPTION
717 WHEN NO_DATA_FOUND THEN
718 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
719 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'*=> Error Message(L1): '||SQLERRM);
720 END IF;
721 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME,
722 p_msg_name => G_NO_DATA_FOUND);
723
724 exists_flag := 'N';
725 WHEN TOO_MANY_ROWS THEN
726 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
727 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'*=> Error Message(L2): '||SQLERRM);
728 END IF;
729 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME,
730 p_msg_name => G_TOO_MANY_ROWS);
731
732 exists_flag := NULL;
733 WHEN OTHERS THEN
734 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
735 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'*=> Error Message(L3): '||SQLERRM);
736 END IF;
737 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME,
738 p_msg_name => G_OTHERS);
739 exists_flag := NULL;
740
741 END line_exist;
742
743 --This procedure creates a new consolidated invoice line
744 --based on the parameters passed
745 PROCEDURE create_new_line(
746 p_khr_id IN NUMBER,
747 p_cnr_id IN NUMBER,
748 p_kle_id IN NUMBER,
749 p_ilt_id IN NUMBER,
750 p_currency_code IN VARCHAR2,
751 p_sequence_number IN NUMBER,
752 p_line_type IN VARCHAR2,
753 p_group_by_contract_yn IN VARCHAR2,
754 p_group_by_assets_yn IN VARCHAR2,
755 p_contract_level_yn IN VARCHAR2,
756 x_lln_id OUT NOCOPY NUMBER
757 )
758
759 IS
760
761 x_llnv_rec Okl_Lln_Pvt.llnv_rec_type;
762 x_llnv_tbl Okl_Lln_Pvt.llnv_tbl_type;
763
764 p_llnv_rec Okl_Lln_Pvt.llnv_rec_type;
765 p_llnv_tbl Okl_Lln_Pvt.llnv_tbl_type;
766
767
768 p_api_version NUMBER := 1.0;
769 p_init_msg_list VARCHAR2(1) := Okl_Api.g_false;
770 x_return_status VARCHAR2(1);
771 x_msg_count NUMBER;
772 x_msg_data VARCHAR2(2000);
773
774 BEGIN
775 IF (G_DEBUG_ENABLED = 'Y') THEN
776 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
777 END IF;
778
779 -- Null out values for khr_id and kle_id so that
780 -- it doesn't get set to g_miss_num
781 p_llnv_rec.KHR_ID := NULL;
782 p_llnv_rec.KLE_ID := NULL;
783
784
785 IF (p_group_by_contract_yn = 'Y' OR p_contract_level_yn = 'N') THEN
786 p_llnv_rec.KHR_ID := p_khr_id;
787 IF p_group_by_assets_yn = 'N' THEN
788 p_llnv_rec.KLE_ID := p_kle_id;
789 END IF;
790 END IF;
791
792 p_llnv_rec.CNR_ID := p_cnr_id;
793 p_llnv_rec.ILT_ID := p_ilt_id;
794
795 IF ( p_sequence_number IS NULL ) THEN
796 p_llnv_rec.SEQUENCE_NUMBER := 1;
797 ELSE
798 p_llnv_rec.SEQUENCE_NUMBER := p_sequence_number;
799 END IF;
800
801 p_llnv_rec.LINE_TYPE := SUBSTR(p_line_type,1,50);
802
803 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
804 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** LINE RECORD CREATION FOR : ***');
805 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** ++++++++++++++++++++++++++++ ***');
806 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> KHR_ID: '||p_llnv_rec.KHR_ID||' KLE_ID: '||p_llnv_rec.KLE_ID);
807 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> CNR_ID: '||p_llnv_rec.CNR_ID||' ILT_ID: '||p_llnv_rec.ILT_ID);
808 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> SEQUENCE_NUMBER: '||p_llnv_rec.SEQUENCE_NUMBER||' LINE_TYPE: '||p_llnv_rec.LINE_TYPE);
809 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** ++++++++++++++++++++++++++++ ***');
810 END IF;
811
812
813 -- Start of wraper code generated automatically by Debug code generator for Okl_Cnsld_Ar_Lines_Pub.INSERT_CNSLD_AR_LINES
814 IF(L_DEBUG_ENABLED='Y') THEN
815 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
816 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
817 END IF;
818 IF(IS_DEBUG_PROCEDURE_ON) THEN
819 BEGIN
820 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRKONB.pls call Okl_Cnsld_Ar_Lines_Pub.INSERT_CNSLD_AR_LINES ');
821 END;
822 END IF;
823 Okl_Cnsld_Ar_Lines_Pub.INSERT_CNSLD_AR_LINES (
824 p_api_version
825 ,p_init_msg_list
826 ,x_return_status
827 ,x_msg_count
828 ,x_msg_data
829 ,p_llnv_rec
830 ,x_llnv_rec
831 );
832 IF(IS_DEBUG_PROCEDURE_ON) THEN
833 BEGIN
834 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRKONB.pls call Okl_Cnsld_Ar_Lines_Pub.INSERT_CNSLD_AR_LINES ');
835 END;
836 END IF;
837 -- End of wraper code generated automatically by Debug code generator for Okl_Cnsld_Ar_Lines_Pub.INSERT_CNSLD_AR_LINES
838
839 IF ( x_return_status = 'S' ) THEN
840 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
841 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'====> Consolidated Line Created.');
842 END IF;
843 ELSE
844 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
845 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'*=> FAILED: Consolidated Line NOT Created.');
846 END IF;
847 END IF;
848
849 x_lln_id := x_llnv_rec.id;
850
851 EXCEPTION
852 --Seed FND_MESSAGE like 'Could NOT CREATE Line RECORD'
853 WHEN OTHERS THEN
854 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
855 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'*=> Error Message(L1): '||SQLERRM);
856 END IF;
857 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME,
858 p_msg_name => G_OTHERS);
859 END create_new_line;
860
861
862 --This procedure creates a new consolidated invoice streams
863 --based on the parameters passed
864 PROCEDURE create_new_streams(
865 p_lln_id IN NUMBER,
866 p_sty_id IN NUMBER,
867 p_kle_id IN NUMBER,
868 p_khr_id IN NUMBER,
869 p_amount IN NUMBER,
870 p_sel_id IN NUMBER,
871 x_lsm_id OUT NOCOPY NUMBER,
872 x_return_status OUT NOCOPY VARCHAR2
873 )
874
875 IS
876
877 x_lsmv_rec Okl_Lsm_Pvt.lsmv_rec_type;
878 x_lsmv_tbl Okl_Lsm_Pvt.lsmv_tbl_type;
879
880 p_lsmv_rec Okl_Lsm_Pvt.lsmv_rec_type;
881 p_lsmv_tbl Okl_Lsm_Pvt.lsmv_tbl_type;
882
883
884 p_api_version NUMBER := 1.0;
885 p_init_msg_list VARCHAR2(1) := Okl_Api.g_false;
886 x_msg_count NUMBER;
887 x_msg_data VARCHAR2(2000);
888
889
890 BEGIN
891 IF (G_DEBUG_ENABLED = 'Y') THEN
892 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
893 END IF;
894
895 p_lsmv_rec.KLE_ID := p_kle_id;
896 p_lsmv_rec.KHR_ID := p_khr_id;
897 p_lsmv_rec.STY_ID := p_sty_id;
898 p_lsmv_rec.LLN_ID := p_lln_id;
899 p_lsmv_rec.AMOUNT := p_amount;
900 p_lsmv_rec.SEL_ID := p_sel_id;
901 p_lsmv_rec.receivables_invoice_id := -99999;
902
903 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
904 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** STREAM RECORD CREATION FOR : ***');
905 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** ++++++++++++++++++++++++++++ ***');
906 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> KHR_ID: '||p_lsmv_rec.KHR_ID||' KLE_ID: '||p_lsmv_rec.KLE_ID);
907 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> STY_ID: '||p_lsmv_rec.STY_ID||' LLN_ID: '||p_lsmv_rec.LLN_ID);
908 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> AMOUNT: '||p_lsmv_rec.AMOUNT);
909 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** ++++++++++++++++++++++++++++ ***');
910 END IF;
911
912 -- Start of wraper code generated automatically by Debug code generator for Okl_Cnsld_Ar_Strms_Pub.INSERT_CNSLD_AR_STRMS
913 IF(L_DEBUG_ENABLED='Y') THEN
914 L_LEVEL_PROCEDURE :=FND_LOG.LEVEL_PROCEDURE;
915 IS_DEBUG_PROCEDURE_ON := OKL_DEBUG_PUB.Check_Log_On(L_MODULE, L_LEVEL_PROCEDURE);
916 END IF;
917 IF(IS_DEBUG_PROCEDURE_ON) THEN
918 BEGIN
919 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRKONB.pls call Okl_Cnsld_Ar_Strms_Pub.INSERT_CNSLD_AR_STRMS ');
920 END;
921 END IF;
922 Okl_Cnsld_Ar_Strms_Pub.INSERT_CNSLD_AR_STRMS(
923 p_api_version
924 ,p_init_msg_list
925 ,x_return_status
926 ,x_msg_count
927 ,x_msg_data
928 ,p_lsmv_rec
929 ,x_lsmv_rec
930 );
931 IF(IS_DEBUG_PROCEDURE_ON) THEN
932 BEGIN
933 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'End Debug OKLRKONB.pls call Okl_Cnsld_Ar_Strms_Pub.INSERT_CNSLD_AR_STRMS ');
934 END;
935 END IF;
936 -- End of wraper code generated automatically by Debug code generator for Okl_Cnsld_Ar_Strms_Pub.INSERT_CNSLD_AR_STRMS
937
938 IF ( x_return_status = 'S' ) THEN
939 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
940 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'====> Consolidated Streams Created.');
941 END IF;
942 ELSE
943 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
944 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'*=> FAILED: Consolidated Streams NOT Created.');
945 END IF;
946 END IF;
947
948 x_lsm_id := x_lsmv_rec.id;
949 EXCEPTION
950 --Seed FND_MESSAGE like 'Could NOT CREATE Stream RECORD'
951 WHEN OTHERS THEN
952 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
953 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'*=> Error Message(D1): '||SQLERRM);
954 END IF;
955 Okl_Api.SET_MESSAGE( p_app_name => G_APP_NAME,
956 p_msg_name => G_OTHERS);
957 END create_new_streams;
958
959
960 PROCEDURE process_cons_bill_tbl(
961 p_contract_number IN VARCHAR2,
962 p_api_version IN NUMBER,
963 p_init_msg_list IN VARCHAR2,
964 p_commit IN VARCHAR2,
965 x_return_status OUT NOCOPY VARCHAR2,
966 x_msg_count OUT NOCOPY NUMBER,
967 x_msg_data OUT NOCOPY VARCHAR2,
968 p_cons_bill_tbl IN OUT NOCOPY cons_bill_tbl_type,
969 p_saved_bill_rec IN OUT NOCOPY saved_bill_rec_type,
970 p_update_tbl IN OUT NOCOPY update_tbl_type)
971 IS
972
973
974 l_api_name CONSTANT VARCHAR2(30) := 'process_cons_bill_tbl';
975 l_format_name okl_invoice_formats_v.name%TYPE;
976 l_contract_level_yn VARCHAR2(3);
977 l_group_asset_yn VARCHAR2(3);
978 l_group_by_contract_yn VARCHAR2(3);
979 l_ilt_id NUMBER;
980 l_cnr_id NUMBER;
981 l_lln_id NUMBER;
982 l_lsm_id NUMBER;
983
984 l_line_name VARCHAR2(150);
985 l_ity_id NUMBER;
986 l_format_type okl_invoice_types_v.name%TYPE;
987
988 l_sequence_number okl_invc_line_types_v.sequence_number%TYPE;
989 l_cons_line_name VARCHAR2(150);
990 l_stream_name VARCHAR2(150);
991 i NUMBER;
992 l_funct_return VARCHAR2(1);
993
994 l_cons_inv_num okl_cnsld_ar_hdrs_v.consolidated_invoice_number%TYPE;
995 l_cnr_amount okl_cnsld_ar_hdrs_v.amount%TYPE;
996 l_lln_amount okl_cnsld_ar_lines_v.amount%TYPE;
997 l_legal_entity_id okl_ext_sell_invs_v.legal_entity_id%TYPE; -- for LE Uptake project 08-11-2006
998
999 l_update_tbl update_tbl_type;
1000
1001 l_kle_id NUMBER;
1002 l_top_kle_id NUMBER;
1003 l_chr_id okc_k_lines_b.chr_id%TYPE;
1004 l_asset_name okc_k_lines_v.name%TYPE;
1005
1006 CURSOR check_top_line ( p_cle_id NUMBER ) IS
1007 SELECT chr_id
1008 FROM okc_k_lines_b
1009 WHERE id = p_cle_id;
1010
1011 CURSOR top_line_asset ( p_cle_id NUMBER ) IS
1012 SELECT name
1013 FROM okc_k_lines_v
1014 WHERE id = p_cle_id;
1015
1016 CURSOR derive_top_line_id (p_lsm_id NUMBER) IS
1017 SELECT FA.ID
1018 FROM OKC_K_HEADERS_B CHR,
1019 OKC_K_LINES_B TOP_CLE,
1020 OKC_LINE_STYLES_b TOP_LSE,
1021 OKC_K_LINES_B SUB_CLE,
1022 OKC_LINE_STYLES_b SUB_LSE,
1023 OKC_K_ITEMS CIM,
1024 OKC_K_LINES_V FA,
1025 OKC_LINE_STYLES_B AST_LSE,
1026 OKL_CNSLD_AR_STRMS_B LSM
1027 WHERE
1028 CHR.ID = TOP_CLE.DNZ_CHR_ID AND
1029 TOP_CLE.LSE_ID = TOP_LSE.ID AND
1030 TOP_LSE.LTY_CODE IN('SOLD_SERVICE','FEE') AND
1031 TOP_CLE.ID = SUB_CLE.CLE_ID AND
1032 SUB_CLE.LSE_ID = SUB_LSE.ID AND
1033 SUB_LSE.LTY_CODE IN ('LINK_SERV_ASSET', 'LINK_FEE_ASSET') AND
1034 SUB_CLE.ID = LSM.KLE_ID AND
1035 LSM.ID = p_lsm_id AND
1036 CIM.CLE_ID = SUB_CLE.ID AND
1037 CIM.JTOT_OBJECT1_CODE = 'OKX_COVASST' AND
1038 CIM.OBJECT1_ID1 = FA.ID AND
1039 FA.LSE_ID = AST_LSE.ID AND
1040 AST_LSE.LTY_CODE = 'FREE_FORM1';
1041
1042
1043 CURSOR inv_format_csr ( p_format_id IN NUMBER, p_stream_id IN NUMBER ) IS
1044 SELECT
1045 inf.name inf_name,
1046 inf.contract_level_yn,
1047 ity.id ity_id,
1048 ity.name ity_name,
1049 ity.group_asset_yn,
1050 ity.group_by_contract_yn,
1051 ilt.id ilt_id,
1052 ilt.sequence_number,
1053 ilt.name ilt_name,
1054 sty.name sty_name
1055 FROM okl_invoice_formats_v inf,
1056 okl_invoice_types_v ity,
1057 okl_invc_line_types_v ilt,
1058 okl_invc_frmt_strms_v frs,
1059 okl_strm_type_v sty
1060 WHERE inf.id = p_format_id
1061 AND ity.inf_id = inf.id
1062 AND ilt.ity_id = ity.id
1063 AND frs.ilt_id = ilt.id
1064 AND sty.id = frs.sty_id
1065 AND frs.sty_id = p_stream_id;
1066
1067 CURSOR inv_format_default_csr ( p_format_id IN NUMBER ) IS
1068 SELECT
1069 inf.name inf_name,
1070 inf.contract_level_yn,
1071 ity.id ity_id,
1072 ity.name ity_name,
1073 ity.group_asset_yn,
1074 ity.group_by_contract_yn,
1075 ilt.id ilt_id,
1076 ilt.sequence_number,
1077 ilt.name ilt_name
1078 FROM okl_invoice_formats_v inf,
1079 okl_invoice_types_v ity,
1080 okl_invc_line_types_v ilt
1081 WHERE inf.id = p_format_id
1082 AND ity.inf_id = inf.id
1083 AND ilt.ity_id = ity.id
1084 AND inf.ilt_id = ilt.id;
1085
1086 l_cons_invoice_num OKL_CNSLD_AR_HDRS_B.CONSOLIDATED_INVOICE_NUMBER%TYPE;
1087 l_invoice_format OKL_INVOICE_FORMATS_V.NAME%TYPE;
1088 l_sty_name OKL_STRM_TYPE_V.NAME%TYPE;
1089
1090 l_old_cnr_id NUMBER;
1091 l_old_lln_id NUMBER;
1092 l_cnt NUMBER;
1093
1094 CURSOR cnr_amt_csr ( p_cnr_id IN NUMBER ) IS
1095 SELECT SUM(lsm.amount)
1096 FROM okl_cnsld_ar_hdrs_b cnr,
1097 okl_cnsld_ar_lines_b lln,
1098 okl_cnsld_ar_strms_b lsm
1099 WHERE cnr.id = p_cnr_id AND
1100 cnr.id = lln.cnr_id AND
1101 lln.id = lsm.lln_id;
1102
1103 CURSOR lln_amt_csr ( p_lln_id IN NUMBER ) IS
1104 SELECT SUM(lsm.amount)
1105 FROM okl_cnsld_ar_lines_b lln,
1106 okl_cnsld_ar_strms_b lsm
1107 WHERE lln.id = p_lln_id AND
1108 lln.id = lsm.lln_id;
1109
1110 CURSOR strm_csr ( p_id NUMBER ) IS
1111 SELECT name
1112 FROM okl_strm_type_v
1113 WHERE id = p_id;
1114
1115 BEGIN
1116 IF (G_DEBUG_ENABLED = 'Y') THEN
1117 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1118 END IF;
1119
1120 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
1121
1122 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1123 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'Total rec count is : '||p_cons_bill_tbl.count);
1124 END IF;
1125
1126 FOR k IN p_cons_bill_tbl.FIRST..p_cons_bill_tbl.LAST LOOP
1127
1128
1129 l_sty_name := NULL;
1130 OPEN strm_csr ( p_cons_bill_tbl(k).sty_id );
1131 FETCH strm_csr INTO l_sty_name;
1132 CLOSE strm_csr;
1133
1134 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1135 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** CONSOLIDATION DETAILS ***');
1136 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** PREVIOUS RECORD WAS FOR: ***');
1137 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** ++++++++++++++++++++++++++++ ***');
1138 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> CUSTOMER_ID: '||p_saved_bill_rec.l_customer_id||' CURRENCY: '||p_saved_bill_rec.l_currency);
1139 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> BILL_TO_SITE: '||p_saved_bill_rec.l_bill_to_site||' PAYMENT_METHOD: '||p_saved_bill_rec.l_payment_method);
1140 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> PRIVATE_LABEL: '||NVL(p_saved_bill_rec.l_private_label,'N/A')||' DATE_CONSOLIDATED: '||TRUNC(p_saved_bill_rec.l_date_consolidated));
1141 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> CONTRACT_ID: '||p_saved_bill_rec.l_prev_khr_id||' INVOICE GROUP ID: '||p_saved_bill_rec.l_saved_format_id);
1142 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> ORIGINAL CONS INV (For credit memos): '||p_saved_bill_rec.l_saved_prev_cons_num);
1143 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> Overall Error Status: '||p_saved_bill_rec.l_overall_status);
1144 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** ++++++++++++++++++++++++++++ ***');
1145 END IF;
1146
1147 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1148 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** CURRENT RECORD IS FOR: ***');
1149 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** ++++++++++++++++++++++++++++ ***');
1150 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> CUSTOMER_ID: '||p_cons_bill_tbl(k).customer_id||' CURRENCY: '||p_cons_bill_tbl(k).currency);
1151 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> BILL_TO_SITE: '||p_cons_bill_tbl(k).bill_to_site||' PAYMENT_METHOD: '||p_cons_bill_tbl(k).payment_method);
1152 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> PRIVATE_LABEL: '||NVL(p_cons_bill_tbl(k).private_label,'N/A')||' DATE_CONSOLIDATED: '||TRUNC(p_cons_bill_tbl(k).date_consolidated));
1153 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> CONTRACT_ID: '||p_cons_bill_tbl(k).contract_id||' INVOICE GROUP ID: '||p_cons_bill_tbl(k).inf_id);
1154 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> ORIGINAL CONS INV (For credit memos): '||p_cons_bill_tbl(k).prev_cons_invoice_num);
1155 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** ++++++++++++++++++++++++++++ ***');
1156 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** END CONSOLIDATION DETAILS ***');
1157 END IF;
1158
1159
1160 i:= 0;
1161 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1162 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '====> Invoice Groups: Checking If Stream assigned to a Line Type.');
1163 END IF;
1164 FOR inv_format IN inv_format_csr (p_cons_bill_tbl(k).inf_id , p_cons_bill_tbl(k).sty_id) LOOP
1165 i := i+1;
1166 l_format_name := inv_format.inf_name;
1167 l_contract_level_yn := inv_format.contract_level_yn;
1168 l_ity_id := inv_format.ity_id;
1169 l_format_type := inv_format.ity_name;
1170 l_group_asset_yn := inv_format.group_asset_yn;
1171 l_group_by_contract_yn := inv_format.group_by_contract_yn;
1172 l_ilt_id := inv_format.ilt_id;
1173 l_sequence_number := inv_format.sequence_number;
1174 l_cons_line_name := inv_format.ilt_name;
1175 l_stream_name := inv_format.sty_name;
1176 END LOOP;
1177
1178 IF i = 0 THEN
1179 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1180 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '====> Invoice Groups: Stream not assigned to a Line Type.');
1181 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '====> Invoice Groups: Checking If Default Line Type exists. ');
1182 END IF;
1183 FOR inv_format_default IN inv_format_default_csr(p_cons_bill_tbl(k).inf_id) LOOP
1184 i := i + 1;
1185
1186 l_format_name := inv_format_default.inf_name;
1187 l_contract_level_yn := inv_format_default.contract_level_yn;
1188 l_ity_id := inv_format_default.ity_id;
1189 l_format_type := inv_format_default.ity_name;
1190 l_group_asset_yn := inv_format_default.group_asset_yn;
1191 l_group_by_contract_yn := inv_format_default.group_by_contract_yn;
1192 l_ilt_id := inv_format_default.ilt_id;
1193 l_sequence_number := inv_format_default.sequence_number;
1194 l_cons_line_name := inv_format_default.ilt_name;
1195 END LOOP;
1196 END IF;
1197
1198 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1199 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** Qualifying Invoice Group Details ***');
1200 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> NAME: '||l_format_name);
1201 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> CONTRACT_LEVEL_YN: '||l_contract_level_yn);
1202 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> INVOICE TYPE NAME: '||l_format_type||' With Id of: '||l_ity_id);
1203 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> GROUP_ASSET_YN: '||l_group_asset_yn);
1204 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> LINE NAME: '||l_cons_line_name||' With Id of: '||l_ilt_id);
1205 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '* ====> SEQUENCE NUMBER: '||l_sequence_number);
1206 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** End Invoice Group Details ***');
1207 END IF;
1208
1209 IF ( (p_cons_bill_tbl(k).customer_id = p_saved_bill_rec.l_customer_id)
1210 AND (p_cons_bill_tbl(k).currency = p_saved_bill_rec.l_currency)
1211 AND (p_cons_bill_tbl(k).bill_to_site = p_saved_bill_rec.l_bill_to_site)
1212 AND (NVL(p_cons_bill_tbl(k).payment_method,-999)= NVL(p_saved_bill_rec.l_payment_method,-999))
1213 AND (NVL(p_cons_bill_tbl(k).private_label,'N/A') = NVL(p_saved_bill_rec.l_private_label,'N/A'))
1214 AND (TRUNC(p_cons_bill_tbl(k).date_consolidated) = TRUNC(p_saved_bill_rec.l_date_consolidated) )
1215 AND (p_cons_bill_tbl(k).inf_id = p_saved_bill_rec.l_saved_format_id)
1216 AND (p_cons_bill_tbl(k).prev_cons_invoice_num = p_saved_bill_rec.l_saved_prev_cons_num)
1217 )
1218 THEN
1219 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1220 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '====> No Break Detected, Check Contract Level YN: '||l_contract_level_yn);
1221 END IF;
1222 -- -------------------------------------------------------------------
1223 -- Check multi-contract invoices
1224 -- -------------------------------------------------------------------
1225 IF ( p_saved_bill_rec.l_prev_khr_id <> p_cons_bill_tbl(k).contract_id ) THEN
1226
1227 IF (l_contract_level_yn = 'Y') THEN
1228 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1229 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '====> Reusing CNR_ID, as Contract Level YN is Y : '||p_saved_bill_rec.l_cnr_id);
1230 END IF;
1231 ELSE
1232 -- ---------------------------
1233 -- Process Header Break Logic
1234 -- ---------------------------
1235 process_break(p_contract_number,
1236 p_commit,
1237 p_saved_bill_rec,
1238 p_update_tbl);
1239
1240 -- Reset update table after processing
1241 p_update_tbl := l_update_tbl;
1242
1243 -- ------------------------------------
1244 -- Finish post header break detection logic
1245 -- ------------------------------------
1246
1247 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1248 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '====> Create new Invoice as Contract Level YN is N.');
1249 END IF;
1250 l_cnr_id := NULL;
1251
1252 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1253 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** CREATE CONSOLIDATED INVOICE HEADER ***');
1254 END IF;
1255 l_cons_inv_num := NULL;
1256 -- for LE Uptake project 08-11-2006
1257 IF (p_cons_bill_tbl(k).legal_entity_id IS NULL OR (p_cons_bill_tbl(k).legal_entity_id = Okl_Api.G_MISS_NUM)) THEN
1258 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(p_cons_bill_tbl(k).contract_id);
1259 ELSE
1260 l_legal_entity_id := p_cons_bill_tbl(k).legal_entity_id;
1261 END IF;
1262 -- for LE Uptake project 08-11-2006
1263 create_new_invoice(
1264 p_cons_bill_tbl(k).bill_to_site,
1265 p_cons_bill_tbl(k).customer_id,
1266 p_cons_bill_tbl(k).currency,
1267 p_cons_bill_tbl(k).payment_method,
1268 p_cons_bill_tbl(k).inf_id,
1269 p_cons_bill_tbl(k).set_of_books_id,
1270 p_cons_bill_tbl(k).private_label,
1271 p_cons_bill_tbl(k).date_consolidated,
1272 p_cons_bill_tbl(k).org_id,
1273 l_legal_entity_id, -- for LE Uptake project 08-11-2006
1274 l_cnr_id,
1275 l_cons_inv_num);
1276
1277 p_saved_bill_rec.l_cnr_id := l_cnr_id;
1278 p_saved_bill_rec.l_cons_inv_num := l_cons_inv_num;
1279
1280 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1281 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** DONE CREATION OF CONSOLIDATED INVOICE HEADER.Assigned Id: '
1282 ||l_cnr_id||' ***'||'p_saved_bill_rec.l_cons_inv_num: '||p_saved_bill_rec.l_cons_inv_num );
1283 END IF;
1284 END IF;
1285 ELSE
1286 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1287 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '====> Reusing CNR_ID (Same Contract) : '||l_cnr_id);
1288 END IF;
1289 END IF;
1290
1291 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1292 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** ++++++++++++++++++++++++++++ ***');
1293 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** CHECK IF A CONSOLIDATED LINE EXISTS ***');
1294 END IF;
1295 l_lln_id := NULL;
1296 line_exist (l_cnr_id,
1297 p_cons_bill_tbl(k).contract_id,
1298 p_cons_bill_tbl(k).kle_id,
1299 l_ilt_id,
1300 l_sequence_number,
1301 l_group_by_contract_yn,
1302 l_group_asset_yn,
1303 l_lln_id,
1304 l_funct_return
1305 );
1306
1307 p_saved_bill_rec.l_lln_id := l_lln_id;
1308
1309 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1310 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** END CHECK FOR CONSOLIDATED LINE ***');
1311 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** ++++++++++++++++++++++++++++ ***');
1312 END IF;
1313
1314 IF l_funct_return = 'N' THEN
1315 -- -----------------------------------------------------
1316 -- Line break detected, update LLN record with amount
1317 -- -----------------------------------------------------
1318
1319 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1320 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** CREATE CONSOLIDATED INVOICE LINE *** for CNR_ID: '||l_cnr_id);
1321 END IF;
1322 l_lln_id := NULL;
1323 l_cnr_id := p_saved_bill_rec.l_cnr_id;
1324
1325 create_new_line(
1326 p_cons_bill_tbl(k).contract_id,
1327 l_cnr_id,
1328 p_cons_bill_tbl(k).kle_id,
1329 l_ilt_id,
1330 p_cons_bill_tbl(k).currency,
1331 l_sequence_number,
1332 'CHARGE',
1333 l_group_by_contract_yn,
1334 l_group_asset_yn,
1335 l_contract_level_yn,
1336 l_lln_id
1337 );
1338 p_saved_bill_rec.l_lln_id := l_lln_id;
1339 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1340 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** DONE CREATION OF CONSOLIDATED INVOICE LINE.Assigned Id: '||l_lln_id||' ***');
1341 END IF;
1342 END IF;
1343 ELSE -- 'ELSE' for the Uppermost level 'IF' for hierarchy checks
1344
1345
1346 -- ------------------------------------
1347 -- Start header break detection logic
1348 -- ------------------------------------
1349 process_break(p_contract_number,
1350 p_commit,
1351 p_saved_bill_rec,
1352 p_update_tbl);
1353
1354 -- Reset update table after processing
1355 p_update_tbl := l_update_tbl;
1356
1357 -- ------------------------------------
1358 -- Finish post header break detection logic
1359 -- ------------------------------------
1360
1361
1362 -- -----------------------------------
1363 -- Break detected
1364 -- -----------------------------------
1365
1366 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1367 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '====> Break Detected.');
1368 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** CREATE CONSOLIDATED INVOICE HEADER ***');
1369 END IF;
1370 -- Null out current value in local variable.
1371 l_cnr_id := NULL;
1372 l_cons_inv_num := NULL;
1373 -- for LE Uptake project 08-11-2006
1374 IF (p_cons_bill_tbl(k).legal_entity_id IS NULL OR (p_cons_bill_tbl(k).legal_entity_id = Okl_Api.G_MISS_NUM)) THEN
1375 l_legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(p_cons_bill_tbl(k).contract_id);
1376 ELSE
1377 l_legal_entity_id := p_cons_bill_tbl(k).legal_entity_id;
1378 END IF;
1379 -- for LE Uptake project 08-11-2006
1380 create_new_invoice(
1381 p_cons_bill_tbl(k).bill_to_site,
1382 p_cons_bill_tbl(k).customer_id,
1383 p_cons_bill_tbl(k).currency,
1384 p_cons_bill_tbl(k).payment_method,
1385 p_cons_bill_tbl(k).inf_id,
1386 p_cons_bill_tbl(k).set_of_books_id,
1387 p_cons_bill_tbl(k).private_label,
1388 p_cons_bill_tbl(k).date_consolidated,
1389 p_cons_bill_tbl(k).org_id,
1390 l_legal_entity_id, -- for LE Uptake project 08-11-2006
1391 l_cnr_id,
1392 l_cons_inv_num);
1393
1394 p_saved_bill_rec.l_cnr_id := l_cnr_id;
1395 p_saved_bill_rec.l_cons_inv_num := l_cons_inv_num;
1396
1397 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1398 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** DONE CREATION OF CONSOLIDATED INVOICE HEADER.Assigned Id: '||l_cnr_id||' ***'||'p_saved_bill_rec.l_cons_inv_num: '||p_saved_bill_rec.l_cons_inv_num );
1399 END IF;
1400
1401 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1402 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** CREATE CONSOLIDATED INVOICE LINE *** for CNR_ID '||l_cnr_id);
1403 END IF;
1404 -- Null out current value in local variable.
1405 l_lln_id := NULL;
1406
1407 l_cnr_id := p_saved_bill_rec.l_cnr_id;
1408 create_new_line(
1409 p_cons_bill_tbl(k).contract_id,
1410 l_cnr_id,
1411 p_cons_bill_tbl(k).kle_id,
1412 l_ilt_id,
1413 p_cons_bill_tbl(k).currency,
1414 l_sequence_number,
1415 'CHARGE',
1416 l_group_by_contract_yn,
1417 l_group_asset_yn,
1418 l_contract_level_yn,
1419 l_lln_id);
1420
1421 p_saved_bill_rec.l_lln_id := l_lln_id;
1422
1423 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1424 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** DONE CREATION OF CONSOLIDATED INVOICE LINE.Assigned Id: '||l_lln_id||' ***');
1425 END IF;
1426 END IF;
1427
1428 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1429 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** CREATE CONSOLIDATED INVOICE STREAMS *** for CNR_ID: '||l_cnr_id||' and LLN_ID: '||l_lln_id);
1430 END IF;
1431 --Null out local variable.
1432 l_lsm_id := null;
1433
1434 l_lln_id := p_saved_bill_rec.l_lln_id;
1435
1436 create_new_streams(
1437 l_lln_id,
1438 p_cons_bill_tbl(k).sty_id,
1439 p_cons_bill_tbl(k).kle_id,
1440 p_cons_bill_tbl(k).contract_id,
1441 p_cons_bill_tbl(k).amount,
1442 p_cons_bill_tbl(k).sel_id,
1443 l_lsm_id,
1444 x_return_status);
1445
1446 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1447 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** DONE CREATION OF CONSOLIDATED INVOICE STREAMS.Assigned Id: '||l_lsm_id||' ***');
1448 END IF;
1449
1450
1451
1452 --Set local variables to cursor values for
1453 --comparison purposes
1454 p_saved_bill_rec.l_customer_id := p_cons_bill_tbl(k).customer_id;
1455 p_saved_bill_rec.l_currency := p_cons_bill_tbl(k).currency;
1456 p_saved_bill_rec.l_bill_to_site := p_cons_bill_tbl(k).bill_to_site;
1457 p_saved_bill_rec.l_payment_method := p_cons_bill_tbl(k).payment_method;
1458 p_saved_bill_rec.l_private_label := p_cons_bill_tbl(k).private_label;
1459 p_saved_bill_rec.l_date_consolidated := p_cons_bill_tbl(k).date_consolidated;
1460 p_saved_bill_rec.l_saved_format_id := p_cons_bill_tbl(k).inf_id;
1461 p_saved_bill_rec.l_prev_khr_id := p_cons_bill_tbl(k).contract_id;
1462 p_saved_bill_rec.l_saved_prev_cons_num := p_cons_bill_tbl(k).prev_cons_invoice_num;
1463 p_saved_bill_rec.l_commit_cnt := NVL(p_saved_bill_rec.l_commit_cnt,0) + 1;
1464
1465 -- -----------------------
1466 -- Work out asset name
1467 -- -----------------------
1468 l_chr_id := NULL;
1469
1470 OPEN check_top_line( p_cons_bill_tbl(k).kle_id );
1471 FETCH check_top_line INTO l_chr_id;
1472 CLOSE check_top_line;
1473
1474 IF l_chr_id IS NOT NULL THEN
1475 l_kle_id := p_cons_bill_tbl(k).kle_id;
1476 ELSE
1477 l_top_kle_id := NULL;
1478 OPEN derive_top_line_id ( l_lsm_id );
1479 FETCH derive_top_line_id INTO l_top_kle_id;
1480 CLOSE derive_top_line_id;
1481 l_kle_id := l_top_kle_id;
1482 END IF;
1483
1484 l_asset_name := NULL;
1485 OPEN top_line_asset ( l_kle_id );
1486 FETCH top_line_asset INTO l_asset_name;
1487 CLOSE top_line_asset;
1488
1489 -- --------------------------
1490 -- Index counter
1491 -- --------------------------
1492 l_cnt := p_update_tbl.count;
1493 l_cnt := l_cnt + 1;
1494
1495 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1496 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'DEL Updates (p_saved_bill_rec.l_cons_inv_num)'||p_saved_bill_rec.l_cons_inv_num);
1497 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'DEL Updates (l_format_name)'||l_format_name);
1498 END IF;
1499
1500 p_update_tbl(l_cnt).cnr_id := p_saved_bill_rec.l_cnr_id;
1501 p_update_tbl(l_cnt).cons_inv_number := p_saved_bill_rec.l_cons_inv_num;
1502 p_update_tbl(l_cnt).lln_id := p_saved_bill_rec.l_lln_id;
1503 p_update_tbl(l_cnt).lsm_id := l_lsm_id;
1504 p_update_tbl(l_cnt).asset_number := l_asset_name;
1505 p_update_tbl(l_cnt).invoice_format := l_format_name;
1506 p_update_tbl(l_cnt).line_type := l_cons_line_name;
1507 p_update_tbl(l_cnt).sty_name := l_sty_name;
1508 p_update_tbl(l_cnt).contract_number := p_cons_bill_tbl(k).contract_number;
1509
1510 -- Start; Bug 4525643; STMATHEW
1511 p_update_tbl(l_cnt).private_label := p_cons_bill_tbl(k).private_label;
1512 -- End; Bug 4525643; STMATHEW
1513
1514 p_update_tbl(l_cnt).lsm_amount := p_cons_bill_tbl(k).amount;
1515 p_update_tbl(l_cnt).xsi_id := p_cons_bill_tbl(k).xsi_id;
1516 p_update_tbl(l_cnt).xls_id := p_cons_bill_tbl(k).xls_id;
1517
1518 END LOOP;
1519
1520
1521 EXCEPTION
1522 ------------------------------------------------------------
1523 -- Exception handling
1524 ------------------------------------------------------------
1525
1526 WHEN OTHERS THEN
1527 IF (L_DEBUG_ENABLED='Y' and FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
1528 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'okl_cons_bill',
1529 'EXCEPTION :'||'OTHERS');
1530 END IF;
1531
1532 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1533 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'*=> Error Message(O3): '||SQLERRM);
1534 END IF;
1535 x_return_status := 'E';
1536
1537 END process_cons_bill_tbl;
1538
1539 PROCEDURE create_cons_bill(
1540 p_api_version IN NUMBER,
1541 p_init_msg_list IN VARCHAR2,
1542 p_commit IN VARCHAR2,
1543 x_return_status OUT NOCOPY VARCHAR2,
1544 x_msg_count OUT NOCOPY NUMBER,
1545 x_msg_data OUT NOCOPY VARCHAR2,
1546 p_contract_number IN VARCHAR2 DEFAULT NULL,
1547 p_inv_msg IN VARCHAR2,
1548 p_assigned_process IN VARCHAR2
1549 )
1550
1551 IS
1552
1553 --Removed Cursor C , C1 def'n
1554 --Fixed Bug #5484903
1555 ---------------------------------------------------------------------------
1556 -- Cursor for consolidated invoices having only headers and lines for a txn
1557 -- Only for UBB billing
1558 ---------------------------------------------------------------------------
1559 CURSOR ubb_csr IS SELECT
1560 xsi.customer_id customer_id,
1561 xsi.currency_code currency,
1562 xsi.customer_address_id bill_to_site,
1563 xsi.receipt_method_id payment_method,
1564 xsi.xtrx_private_label private_label,
1565 TRUNC(xsi.TRX_DATE) date_consolidated,
1566 tai.khr_id contract_id, -- get contract Id
1567 xsi.org_id org_id,
1568 tai.clg_id clg_id,
1569 xsi.set_of_books_id set_of_books_id,
1570 til.kle_id kle_id,
1571 tld.sty_id stream_id, -- to get the line seq #
1572 til.line_number ubb_line_number,
1573 xsi.id xsi_id,
1574 xls.id xls_id,
1575 xls.amount ubb_amount,
1576 xls.sel_id sel_id
1577 --vthiruva added for bug#4438971 fix..24-JUN-2005
1578 ,xsi.inf_id inf_id
1579 ,xsi.legal_entity_id legal_entity_id -- for LE Uptake project 08-11-2006
1580 FROM
1581 okl_ext_sell_invs_v xsi,
1582 okl_xtl_sell_invs_v xls,
1583 okl_txd_ar_ln_dtls_v tld,
1584 okl_txl_ar_inv_lns_v til,
1585 okl_trx_ar_invoices_v tai,
1586 okc_k_headers_b chr,
1587 okl_parallel_processes pws
1588 WHERE
1589 xsi.TRX_STATUS_CODE = 'SUBMITTED' AND
1590 xls.xsi_id_details = xsi.id AND
1591 tld.id = xls.tld_id AND
1592 til.id = tld.TIL_ID_DETAILS AND
1593 tai.id = til.tai_id AND
1594 tai.khr_id = chr.id
1595 AND
1596 -- Contract Specific consolidation
1597 chr.contract_number = NVL(p_contract_number,chr.contract_number) AND
1598 -- Contract Specific consolidation
1599 tai.clg_id IS NOT NULL AND
1600 xls.amount > 0 AND
1601 PWS.OBJECT_TYPE = 'CUSTOMER' AND
1602 XSI.CUSTOMER_ID = TO_NUMBER(pws.object_value) AND
1603 PWS.ASSIGNED_PROCESS = P_ASSIGNED_PROCESS
1604 ORDER BY 1,2,3,4,5,6,7,8;
1605
1606
1607 ---------------------------------------------------------------------------
1608 -- Cursor for consolidated invoices having only headers and lines for a txn
1609 -- Only for Termination Quote billing
1610 ---------------------------------------------------------------------------
1611 CURSOR qte_csr IS SELECT
1612 -- Start Bug 4731187 (changed Order by)
1613 tai.qte_id qte_id,
1614 TRUNC(xsi.TRX_DATE) date_consolidated,
1615 xls.amount qte_amount,
1616 xsi.customer_id customer_id,
1617 xsi.currency_code currency,
1618 xsi.customer_address_id bill_to_site,
1619 xsi.receipt_method_id payment_method,
1620 xsi.xtrx_private_label private_label,
1621 tai.khr_id contract_id,
1622 xsi.org_id org_id,
1623 xsi.set_of_books_id set_of_books_id,
1624 til.kle_id kle_id,
1625 til.sty_id stream_id,
1626 til.line_number qte_line_number,
1627 til.description description,
1628 xsi.id xsi_id,
1629 xls.id xls_id,
1630 xls.sel_id sel_id
1631 --vthiruva added for bug#4438971 fix..24-JUN-2005
1632 ,xsi.inf_id inf_id
1633 ,xsi.legal_entity_id legal_entity_id -- for LE Uptake project 08-11-2006
1634 FROM
1635 okl_ext_sell_invs_v xsi,
1636 okl_xtl_sell_invs_v xls,
1637 okl_txl_ar_inv_lns_v til,
1638 okl_trx_ar_invoices_v tai,
1639 okc_k_headers_b chr,
1640 okl_parallel_processes pws
1641 WHERE
1642 xsi.TRX_STATUS_CODE = 'SUBMITTED' AND
1643 xls.xsi_id_details = xsi.id AND
1644 til.id = xls.til_id AND
1645 tai.id = til.tai_id AND
1646 tai.khr_id = chr.id
1647 AND
1648 -- Contract Specific consolidation
1649 chr.contract_number = NVL(p_contract_number,chr.contract_number) AND
1650 -- Contract Specific consolidation
1651 tai.qte_id IS NOT NULL AND
1652 PWS.OBJECT_TYPE = 'CUSTOMER' AND
1653 XSI.CUSTOMER_ID = TO_NUMBER(pws.object_value) AND
1654 PWS.ASSIGNED_PROCESS = P_ASSIGNED_PROCESS
1655 ORDER BY 1,2,3,4,5,6,7,8,9;
1656 -- End Bug 4731187 (changed Order by)
1657
1658 ---------------------------------------------------------------------------
1659 -- Cursor for consolidated invoices having only headers and lines for a txn
1660 -- Only for Collecetions Billing
1661 ---------------------------------------------------------------------------
1662 CURSOR cpy_csr IS SELECT
1663 xsi.customer_id customer_id,
1664 xsi.currency_code currency,
1665 xsi.customer_address_id bill_to_site,
1666 xsi.receipt_method_id payment_method,
1667 xsi.xtrx_private_label private_label,
1668 tai.khr_id contract_id,
1669 TRUNC(xsi.TRX_DATE) date_consolidated,
1670 tai.cpy_id cpy_id,
1671 xsi.org_id org_id,
1672 xsi.set_of_books_id set_of_books_id,
1673 til.kle_id kle_id,
1674 til.sty_id stream_id,
1675 til.line_number cpy_line_number,
1676 xsi.id xsi_id,
1677 xls.id xls_id,
1678 xls.amount cpy_amount,
1679 xls.sel_id sel_id
1680 --vthiruva added for bug#4438971 fix..24-JUN-2005
1681 ,xsi.inf_id inf_id
1682 ,xsi.legal_entity_id legal_entity_id -- for LE Uptake project 08-11-2006
1683 FROM
1684 okl_ext_sell_invs_v xsi,
1685 okl_xtl_sell_invs_v xls,
1686 okl_txl_ar_inv_lns_v til,
1687 okl_trx_ar_invoices_v tai,
1688 okc_k_headers_b chr,
1689 okl_parallel_processes pws
1690 WHERE
1691 xsi.TRX_STATUS_CODE = 'SUBMITTED' AND
1692 xls.xsi_id_details = xsi.id AND
1693 til.id = xls.til_id AND
1694 tai.id = til.tai_id AND
1695 tai.khr_id = chr.id
1696 AND
1697 -- Contract Specific consolidation
1698 chr.contract_number = NVL(p_contract_number,chr.contract_number) AND
1699 -- Contract Specific consolidation
1700 tai.cpy_id IS NOT NULL AND
1701 xls.amount > 0 AND
1702 PWS.OBJECT_TYPE = 'CUSTOMER' AND
1703 XSI.CUSTOMER_ID = TO_NUMBER(pws.object_value) AND
1704 PWS.ASSIGNED_PROCESS = P_ASSIGNED_PROCESS
1705 ORDER BY 1,2,3,4,5,6,7,8;
1706 --start changed by abhsaxen for Bug#6174484
1707 CURSOR cm_two_lvl_csr IS
1708 SELECT xsi.customer_id customer_id,
1709 xsi.currency_code currency,
1710 xsi.customer_address_id bill_to_site,
1711 xsi.receipt_method_id payment_method,
1712 xsi.xtrx_private_label private_label,
1713 TRUNC(xsi.trx_date) date_consolidated,
1714 tai.khr_id contract_id,
1715 CHR.contract_number contract_number,
1716 xsi.inf_id inf_id,
1717 '-9958' prev_cons_invoice_num,
1718 xsi.org_id org_id,
1719 xsi.set_of_books_id set_of_books_id,
1720 til.kle_id kle_id,
1721 til.sty_id stream_id,
1722 xsi.id xsi_id,
1723 xls.id xls_id,
1724 xls.amount cm2_amount,
1725 xls.sel_id sel_id,
1726 xsi.legal_entity_id legal_entity_id --FOR le uptake project 8 -11 -2006
1727 FROM okl_ext_sell_invs_v xsi,
1728 okl_xtl_sell_invs_b xls,
1729 okl_txl_ar_inv_lns_b til,
1730 okl_trx_ar_invoices_b tai,
1731 okc_k_headers_all_b CHR,
1732 okl_parallel_processes pws
1733 WHERE xsi.trx_status_code = 'SUBMITTED'
1734 AND xls.xsi_id_details = xsi.id
1735 AND til.id = xls.til_id
1736 AND tai.id = til.tai_id
1737 AND tai.khr_id = CHR.id
1738 AND --contract specific consolidation
1739 CHR.contract_number = nvl(p_contract_number, CHR.contract_number)
1740 AND --contract specific consolidation
1741 tai.qte_id IS NULL
1742 AND xls.amount < 0
1743 AND til.til_id_reverses IS NULL
1744 AND pws.object_type = 'CUSTOMER'
1745 AND xsi.customer_id = to_number(pws.object_value)
1746 AND pws.assigned_process = p_assigned_process
1747 UNION
1748 SELECT xsi.customer_id customer_id,
1749 xsi.currency_code currency,
1750 xsi.customer_address_id bill_to_site,
1751 xsi.receipt_method_id payment_method,
1752 xsi.xtrx_private_label private_label,
1753 TRUNC(xsi.trx_date) date_consolidated,
1754 tai.khr_id contract_id,
1755 CHR.contract_number contract_number,
1756 xsi.inf_id inf_id,
1757 xsir.xtrx_cons_invoice_number prev_cons_invoice_num,
1758 xsi.org_id org_id,
1759 xsi.set_of_books_id set_of_books_id,
1760 til.kle_id kle_id,
1761 til.sty_id stream_id,
1762 xsi.id xsi_id,
1763 xls.id xls_id,
1764 xls.amount cm2_amount,
1765 xls.sel_id sel_id,
1766 xsi.legal_entity_id legal_entity_id --FOR le uptake project 8 -11 -2006
1767 FROM okl_ext_sell_invs_v xsi,
1768 okl_ext_sell_invs_v xsir,
1769 okl_xtl_sell_invs_b xls,
1770 okl_xtl_sell_invs_b xlsr,
1771 okl_txl_ar_inv_lns_b til,
1772 okl_trx_ar_invoices_b tai,
1773 okc_k_headers_all_b CHR,
1774 okl_parallel_processes pws
1775 WHERE xsi.trx_status_code = 'SUBMITTED'
1776 AND xls.xsi_id_details = xsi.id
1777 AND til.id = xls.til_id
1778 AND tai.id = til.tai_id
1779 AND tai.khr_id = CHR.id
1780 AND --contract specific consolidation
1781 CHR.contract_number = nvl(p_contract_number, CHR.contract_number)
1782 AND --contract specific consolidation
1783 tai.qte_id IS NULL
1784 AND xls.amount <= 0
1785 AND til.til_id_reverses = xlsr.til_id
1786 AND xlsr.xsi_id_details = xsir.id
1787 AND pws.object_type = 'CUSTOMER'
1788 AND xsi.customer_id = to_number(pws.object_value)
1789 AND pws.assigned_process = p_assigned_process
1790 ORDER BY 1, 2, 3, 4, 5, 6, 7, 8, 9, 10;
1791 --end changed by abhsaxen for Bug#6174484
1792 CURSOR cm_three_lvl_csr IS
1793 SELECT
1794 xsi.customer_id customer_id,
1795 xsi.currency_code currency,
1796 xsi.customer_address_id bill_to_site,
1797 xsi.receipt_method_id payment_method,
1798 xsi.xtrx_private_label private_label,
1799 TRUNC(xsi.TRX_DATE) date_consolidated,
1800 tai.khr_id contract_id, -- get contract Id
1801 chr.contract_number contract_number,
1802 xsi.inf_id inf_id,
1803 '-9958' prev_cons_invoice_num,
1804 xsi.org_id org_id,
1805 xsi.set_of_books_id set_of_books_id,
1806 til.kle_id kle_id,
1807 tld.sty_id stream_id, -- to get the line seq #
1808 xsi.id xsi_id,
1809 xls.id xls_id,
1810 xls.amount cm3_amount,
1811 xls.sel_id sel_id
1812 ,xsi.legal_entity_id legal_entity_id -- for LE Uptake project 08-11-2006
1813 FROM
1814 okl_ext_sell_invs_v xsi,
1815 okl_xtl_sell_invs_v xls,
1816 okl_txd_ar_ln_dtls_v tld,
1817 okl_txl_ar_inv_lns_v til,
1818 okl_trx_ar_invoices_v tai,
1819 okc_k_headers_b chr,
1820 okl_parallel_processes pws
1821 WHERE
1822 xsi.TRX_STATUS_CODE = 'SUBMITTED' AND
1823 xls.xsi_id_details = xsi.id AND
1824 tld.id = xls.tld_id AND
1825 til.id = tld.TIL_ID_DETAILS AND
1826 tai.id = til.tai_id AND
1827 tai.khr_id = chr.id
1828 AND
1829 -- Contract Specific consolidation
1830 chr.contract_number = NVL(p_contract_number,chr.contract_number) AND
1831 -- Contract Specific consolidation
1832 xls.amount < 0 AND
1833 tld.tld_id_reverses IS NULL AND
1834 PWS.OBJECT_TYPE = 'CUSTOMER' AND
1835 XSI.CUSTOMER_ID = TO_NUMBER(pws.object_value) AND
1836 PWS.ASSIGNED_PROCESS = P_ASSIGNED_PROCESS
1837 UNION
1838 SELECT
1839 xsi.customer_id customer_id,
1840 xsi.currency_code currency,
1841 xsi.customer_address_id bill_to_site,
1842 xsi.receipt_method_id payment_method,
1843 xsi.xtrx_private_label private_label,
1844 TRUNC(xsi.TRX_DATE) date_consolidated,
1845 tai.khr_id contract_id, -- get contract Id
1846 chr.contract_number contract_number,
1847 xsi.inf_id inf_id,
1848 xsir.xtrx_cons_invoice_number prev_cons_invoice_num,
1849 xsi.org_id org_id,
1850 xsi.set_of_books_id set_of_books_id,
1851 til.kle_id kle_id,
1852 tld.sty_id stream_id, -- to get the line seq #
1853 xsi.id xsi_id,
1854 xls.id xls_id,
1855 xls.amount cm3_amount,
1856 xls.sel_id sel_id
1857 ,xsi.legal_entity_id legal_entity_id -- for LE Uptake project 08-11-2006
1858 FROM
1859 okl_ext_sell_invs_v xsi,
1860 okl_ext_sell_invs_v xsir,
1861 okl_xtl_sell_invs_v xls,
1862 okl_xtl_sell_invs_v xlsr,
1863 okl_txd_ar_ln_dtls_v tld,
1864 okl_txl_ar_inv_lns_v til,
1865 okl_trx_ar_invoices_v tai,
1866 okc_k_headers_b chr,
1867 okl_parallel_processes pws
1868 WHERE
1869 xsi.TRX_STATUS_CODE = 'SUBMITTED' AND
1870 xls.xsi_id_details = xsi.id AND
1871 tld.id = xls.tld_id AND
1872 til.id = tld.TIL_ID_DETAILS AND
1873 tai.id = til.tai_id AND
1874 tai.khr_id = chr.id
1875 AND
1876 -- Contract Specific consolidation
1877 chr.contract_number = NVL(p_contract_number,chr.contract_number) AND
1878 -- Contract Specific consolidation
1879 xls.amount <= 0 AND
1880 tld.tld_id_reverses IS NOT NULL AND
1881 xlsr.tld_id = tld.tld_id_reverses AND
1882 xsir.id = xlsr.xsi_id_details AND
1883 PWS.OBJECT_TYPE = 'CUSTOMER' AND
1884 XSI.CUSTOMER_ID = TO_NUMBER(pws.object_value) AND
1885 PWS.ASSIGNED_PROCESS = P_ASSIGNED_PROCESS
1886 ORDER BY 1,2,3,4,5,6,7,8,9,10;
1887
1888 -- Billing performance fix
1889 cons_bill_tbl cons_bill_tbl_type;
1890 saved_bill_rec saved_bill_rec_type;
1891 l_init_bill_rec saved_bill_rec_type;
1892
1893 l_update_tbl update_tbl_type;
1894
1895 L_FETCH_SIZE NUMBER := 1000;
1896
1897 l_cons_inv_num okl_cnsld_ar_hdrs_v.consolidated_invoice_number%TYPE;
1898 -- Billing performance fix
1899
1900 CURSOR line_seq_csr(p_cnr_id NUMBER) IS
1901 SELECT *
1902 FROM okl_cnsld_ar_lines_v
1903 WHERE cnr_id = p_cnr_id
1904 ORDER BY sequence_number;
1905
1906 CURSOR format_id_csr (p_format_name VARCHAR2) IS
1907 SELECT id
1908 FROM okl_invoice_formats_v
1909 WHERE name = p_format_name;
1910
1911 CURSOR asset_line_csr (p_lsm_id NUMBER) IS
1912 SELECT ASSET_NUMBER
1913 FROM
1914 OKL_CNSLD_AR_STRMS_B LSM
1915 ,OKX_ASSET_LINES_V KLE
1916
1917 WHERE LSM.ID = p_lsm_id
1918 AND KLE.PARENT_LINE_ID = LSM.KLE_ID;
1919
1920 CURSOR service_asset_csr (p_lsm_id NUMBER) IS
1921 SELECT FA.NAME
1922 FROM OKC_K_HEADERS_B CHR,
1923 OKC_K_LINES_B TOP_CLE,
1924 OKC_LINE_STYLES_b TOP_LSE,
1925 OKC_K_LINES_B SUB_CLE,
1926 OKC_LINE_STYLES_b SUB_LSE,
1927 OKC_K_ITEMS CIM,
1928 OKC_K_LINES_V FA,
1929 OKC_LINE_STYLES_B AST_LSE,
1930 OKL_CNSLD_AR_STRMS_B LSM
1931 WHERE
1932 CHR.ID = TOP_CLE.DNZ_CHR_ID AND
1933 TOP_CLE.LSE_ID = TOP_LSE.ID AND
1934 TOP_LSE.LTY_CODE IN('SOLD_SERVICE','FEE') AND
1935 TOP_CLE.ID = SUB_CLE.CLE_ID AND
1936 SUB_CLE.LSE_ID = SUB_LSE.ID AND
1937 SUB_LSE.LTY_CODE IN ('LINK_SERV_ASSET', 'LINK_FEE_ASSET') AND
1938 SUB_CLE.ID = LSM.KLE_ID AND
1939 LSM.ID = p_lsm_id AND
1940 CIM.CLE_ID = SUB_CLE.ID AND
1941 CIM.JTOT_OBJECT1_CODE = 'OKX_COVASST' AND
1942 CIM.OBJECT1_ID1 = FA.ID AND
1943 FA.LSE_ID = AST_LSE.ID AND
1944 AST_LSE.LTY_CODE = 'FREE_FORM1';
1945
1946 l_cnr_id NUMBER;
1947 l_lln_id NUMBER;
1948 l_lsm_id NUMBER;
1949 l_seq_num NUMBER;
1950
1951
1952 l_line_amount NUMBER;
1953 l_consbill_amount NUMBER;
1954
1955 TYPE cnr_update_rec_type IS RECORD (
1956 cnr_id NUMBER,
1957 lln_id NUMBER,
1958 lsm_id NUMBER,
1959 xsi_id NUMBER,
1960 xls_id NUMBER,
1961 return_status VARCHAR2(1)
1962 );
1963
1964 TYPE cnr_update_tbl_type IS TABLE OF cnr_update_rec_type
1965 INDEX BY BINARY_INTEGER;
1966
1967 cnr_update_tbl cnr_update_tbl_type;
1968 cnr_tab_idx NUMBER;
1969
1970 -- In and Out records for the external sell invoice tables
1971 l_xsiv_rec Okl_Xsi_Pvt.xsiv_rec_type;
1972 x_xsiv_rec Okl_Xsi_Pvt.xsiv_rec_type;
1973 null_xsiv_rec Okl_Xsi_Pvt.xsiv_rec_type;
1974
1975 l_xlsv_rec Okl_Xls_Pvt.xlsv_rec_type;
1976 x_xlsv_rec Okl_Xls_Pvt.xlsv_rec_type;
1977 null_xlsv_rec Okl_Xls_Pvt.xlsv_rec_type;
1978
1979 -- For Updating header and line amnounts and sequences
1980 u_cnrv_rec Okl_Cnr_Pvt.cnrv_rec_type;
1981 x_cnrv_rec Okl_Cnr_Pvt.cnrv_rec_type;
1982 null_cnrv_rec Okl_Cnr_Pvt.cnrv_rec_type;
1983
1984 u_llnv_rec Okl_Lln_Pvt.llnv_rec_type;
1985 x_llnv_rec Okl_Lln_Pvt.llnv_rec_type;
1986 null_llnv_rec Okl_Lln_Pvt.llnv_rec_type;
1987
1988 --All the below variables for a successful rules invocation
1989 l_rul_format_name OKC_RULES_B.RULE_INFORMATION1%TYPE;
1990 l_init_msg_list VARCHAR2(1) ;
1991 l_msg_count NUMBER ;
1992 l_msg_data VARCHAR2(2000);
1993 l_rulv_rec Okl_Rule_Apis_Pvt.rulv_rec_type;
1994 null_rulv_rec Okl_Rule_Apis_Pvt.rulv_rec_type;
1995
1996 ------------------------------------------------------------
1997 -- Declare variables required by UBB Billing Consolidation
1998 ------------------------------------------------------------
1999 l_clg_id NUMBER;
2000
2001 ------------------------------------------------------------
2002 -- Declare variables required by Termination Quote Billing
2003 ------------------------------------------------------------
2004 l_qte_id NUMBER := -1;
2005
2006 l_qte_cust_id okl_ext_sell_invs_v.Customer_id%TYPE;
2007 ------------------------------------------------------------
2008 -- Declare variables required by Collections Billing
2009 ------------------------------------------------------------
2010 l_cpy_id NUMBER := -1;
2011
2012 ------------------------------------------------------------
2013 -- Declare variables required by APIs
2014 ------------------------------------------------------------
2015
2016 l_api_version CONSTANT NUMBER := 1;
2017 l_api_name CONSTANT VARCHAR2(30) := 'CONSOLIDATED BILLING';
2018 l_return_status VARCHAR2(1) := Okl_Api.G_RET_STS_SUCCESS;
2019
2020 CURSOR cntrct_csr ( p_id NUMBER ) IS
2021 SELECT contract_number
2022 FROM okc_k_headers_b
2023 WHERE id = p_id;
2024
2025 CURSOR strm_csr ( p_id NUMBER ) IS
2026 SELECT name
2027 FROM okl_strm_type_v
2028 WHERE id = p_id;
2029
2030 l_contract_number okc_k_headers_b.contract_number%TYPE;
2031 l_stream_name1 okl_strm_type_v.name%TYPE;
2032
2033 l_temp_khr_id NUMBER;
2034
2035 CURSOR get_khr_id ( p_lsm_id NUMBER ) IS
2036 SELECT khr_id
2037 FROM okl_cnsld_ar_strms_b
2038 WHERE id = p_lsm_id;
2039
2040
2041 -- Variable to track commit record size
2042 l_commit_cnt NUMBER;
2043
2044 -- --------------------------------------------
2045 -- Get distinct currencies processed in a run
2046 -- --------------------------------------------
2047 CURSOR curr_csr( p_request_id NUMBER ) IS
2048 SELECT DISTINCT CURRENCY_CODE
2049 FROM okl_cnsld_ar_hdrs_v
2050 WHERE request_id = p_request_id;
2051
2052 CURSOR cnr_cnt_csr( p_request_id NUMBER, p_trx_sts VARCHAR2, p_curr_code VARCHAR2 ) IS
2053 SELECT count(*)
2054 FROM okl_cnsld_ar_hdrs_v
2055 WHERE request_id = p_request_id
2056 AND TRX_STATUS_CODE = p_trx_sts
2057 AND CURRENCY_CODE = p_curr_code;
2058
2059 -- --------------------------------------------------------
2060 -- To Print log messages
2061 -- --------------------------------------------------------
2062 l_request_id NUMBER;
2063
2064 CURSOR req_id_csr IS
2065 SELECT
2066 DECODE(Fnd_Global.CONC_REQUEST_ID,-1,NULL,Fnd_Global.CONC_REQUEST_ID)
2067 FROM dual;
2068
2069 ------------------------------------------------------------
2070 -- Operating Unit
2071 ------------------------------------------------------------
2072 --Fixed Bug 5484903.
2073 CURSOR op_unit_csr IS
2074 SELECT name
2075 FROM hr_operating_units
2076 WHERE organization_id = mo_global.get_current_org_id();
2077
2078
2079 l_succ_cnt NUMBER;
2080 l_err_cnt NUMBER;
2081 l_op_unit_name hr_operating_units.name%TYPE;
2082 lx_msg_data VARCHAR2(450);
2083 l_msg_index_out NUMBER :=0;
2084 processed_sts okl_cnsld_ar_hdrs_v.trx_status_code%TYPE;
2085 error_sts okl_cnsld_ar_hdrs_v.trx_status_code%TYPE;
2086
2087 -- -----------------------------
2088 -- New fields
2089 -- -----------------------------
2090 l_old_cnr_id NUMBER;
2091 l_old_lln_id NUMBER;
2092 l_cnr_amount okl_cnsld_ar_hdrs_v.amount%TYPE;
2093 l_lln_amount okl_cnsld_ar_lines_v.amount%TYPE;
2094
2095 CURSOR cnr_amt_csr ( p_cnr_id IN NUMBER ) IS
2096 SELECT SUM(lsm.amount)
2097 FROM okl_cnsld_ar_hdrs_b cnr,
2098 okl_cnsld_ar_lines_b lln,
2099 okl_cnsld_ar_strms_b lsm
2100 WHERE cnr.id = p_cnr_id AND
2101 cnr.id = lln.cnr_id AND
2102 lln.id = lsm.lln_id;
2103
2104 CURSOR lln_amt_csr ( p_lln_id IN NUMBER ) IS
2105 SELECT SUM(lsm.amount)
2106 FROM okl_cnsld_ar_lines_b lln,
2107 okl_cnsld_ar_strms_b lsm
2108 WHERE lln.id = p_lln_id AND
2109 lln.id = lsm.lln_id;
2110
2111 -- Start Bug 4731187
2112 l_qte_trx_date DATE;
2113 -- End Bug 4731187
2114
2115
2116 BEGIN
2117 IF (G_DEBUG_ENABLED = 'Y') THEN
2118 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2119 END IF;
2120
2121 L_DEBUG_ENABLED := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
2122
2123 IF (L_DEBUG_ENABLED='Y' and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2124 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'okl_cons_bill'
2125 ,'Begin(+)');
2126 END IF;
2127
2128 -- ------------------------
2129 -- Print Input variables
2130 -- ------------------------
2131 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2132 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_commit '||p_commit);
2133 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_contract_number '||p_contract_number);
2134 END IF;
2135
2136 ------------------------------------------------------------
2137 -- Start processing
2138 ------------------------------------------------------------
2139
2140 x_return_status := Okl_Api.G_RET_STS_SUCCESS;
2141
2142 l_return_status := Okl_Api.START_ACTIVITY(
2143 p_api_name => l_api_name,
2144 p_pkg_name => G_PKG_NAME,
2145 p_init_msg_list => p_init_msg_list,
2146 l_api_version => l_api_version,
2147 p_api_version => p_api_version,
2148 p_api_type => '_PVT',
2149 x_return_status => l_return_status);
2150
2151 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2152 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2153 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2154 RAISE Okl_Api.G_EXCEPTION_ERROR;
2155 END IF;
2156
2157 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2158 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '========== **** BEGIN PROGRAM EXECUTION **** ============');
2159 END IF;
2160
2161 -- IF p_contract_number IS NULL THEN
2162
2163 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2164 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '========== START: Three LEVEL Processing ============');
2165 END IF;
2166 -- ---------------------------------------
2167 -- Initialize table and record parameters
2168 -- ---------------------------------------
2169 saved_bill_rec := l_init_bill_rec;
2170 cons_bill_tbl.delete;
2171 l_update_tbl.delete;
2172
2173 /* MDOKAL -- This logic now executed separately
2174 OPEN c;
2175 LOOP
2176 cons_bill_tbl.delete;
2177 FETCH C BULK COLLECT INTO cons_bill_tbl LIMIT L_FETCH_SIZE;
2178
2179 FND_FILE.PUT_LINE (FND_FILE.LOG, 'C cons_bill_tbl count is: '||cons_bill_tbl.COUNT);
2180 IF cons_bill_tbl.COUNT > 0 THEN
2181 process_cons_bill_tbl(
2182 p_contract_number => p_contract_number,
2183 p_api_version => p_api_version,
2184 p_init_msg_list => p_init_msg_list,
2185 p_commit => p_commit,
2186 x_return_status => x_return_status,
2187 x_msg_count => x_msg_count,
2188 x_msg_data => x_msg_data,
2189 p_cons_bill_tbl => cons_bill_tbl,
2190 p_saved_bill_rec => saved_bill_rec,
2191 p_update_tbl => l_update_tbl);
2192 END IF;
2193 EXIT WHEN C%NOTFOUND;
2194 END LOOP;
2195 CLOSE C;
2196
2197 -- -----------------------------
2198 -- Process Last set of records
2199 -- -----------------------------
2200 process_break(p_contract_number,
2201 p_commit,
2202 saved_bill_rec,
2203 l_update_tbl);
2204
2205 IF FND_API.To_Boolean( p_commit ) THEN
2206 COMMIT;
2207 END IF;
2208
2209 PRINT_TO_LOG( '========== END: Three LEVEL Processing ============');
2210
2211 PRINT_TO_LOG( '========== START: Two LEVEL Processing ============');
2212 -- ---------------------------------------
2213 -- Initialize table and record parameters
2214 -- ---------------------------------------
2215 saved_bill_rec := l_init_bill_rec;
2216 cons_bill_tbl.delete;
2217 l_update_tbl.delete;
2218 */
2219
2220 OKL_BILLING_CONTROLLER_PVT.create_cons_bill(
2221 p_contract_number => p_contract_number,
2222 p_api_version => p_api_version,
2223 p_init_msg_list => p_init_msg_list,
2224 p_commit => p_commit,
2225 p_inv_msg => p_inv_msg,
2226 p_assigned_process => p_assigned_process,
2227 x_return_status => l_return_status,
2228 x_msg_count => x_msg_count,
2229 x_msg_data => x_msg_data);
2230
2231 IF (l_return_status = Okl_Api.G_RET_STS_UNEXP_ERROR) THEN
2232 RAISE Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR;
2233 ELSIF (l_return_status = Okl_Api.G_RET_STS_ERROR) THEN
2234 RAISE Okl_Api.G_EXCEPTION_ERROR;
2235 END IF;
2236
2237 /*
2238 OPEN C1;
2239 LOOP
2240 cons_bill_tbl.delete;
2241 FETCH C1 BULK COLLECT INTO cons_bill_tbl LIMIT L_FETCH_SIZE;
2242
2243 FND_FILE.PUT_LINE (FND_FILE.LOG, 'C1 cons_bill_tbl count is: '||cons_bill_tbl.COUNT);
2244 IF cons_bill_tbl.COUNT > 0 THEN
2245 process_cons_bill_tbl(
2246 p_contract_number => p_contract_number,
2247 p_api_version => p_api_version,
2248 p_init_msg_list => p_init_msg_list,
2249 p_commit => p_commit,
2250 x_return_status => x_return_status,
2251 x_msg_count => x_msg_count,
2252 x_msg_data => x_msg_data,
2253 p_cons_bill_tbl => cons_bill_tbl,
2254 p_saved_bill_rec => saved_bill_rec,
2255 p_update_tbl => l_update_tbl);
2256 END IF;
2257 EXIT WHEN C1%NOTFOUND;
2258 END LOOP;
2259 CLOSE C1;
2260
2261 -- -----------------------------
2262 -- Process Last set of records
2263 -- -----------------------------
2264 process_break(p_contract_number,
2265 p_commit,
2266 saved_bill_rec,
2267 l_update_tbl);
2268
2269 IF FND_API.To_Boolean( p_commit ) THEN
2270 COMMIT;
2271 END IF;
2272
2273 PRINT_TO_LOG( '========== END: Two LEVEL Processing ============');
2274
2275 PRINT_TO_LOG( '========== START: CREDIT MEMO Two LEVEL Processing ============');
2276 -- ---------------------------------------
2277 -- Initialize table and record parameters
2278 -- ---------------------------------------
2279 saved_bill_rec := l_init_bill_rec;
2280 cons_bill_tbl.delete;
2281 l_update_tbl.delete;
2282 */
2283 OPEN cm_two_lvl_csr;
2284 LOOP
2285 cons_bill_tbl.delete;
2286 FETCH cm_two_lvl_csr BULK COLLECT INTO cons_bill_tbl LIMIT L_FETCH_SIZE;
2287
2288 FND_FILE.PUT_LINE (FND_FILE.LOG, 'cm_two_lvl_csr cons_bill_tbl count is: '||cons_bill_tbl.COUNT);
2289 IF cons_bill_tbl.COUNT > 0 THEN
2290 process_cons_bill_tbl(
2291 p_contract_number => p_contract_number,
2292 p_api_version => p_api_version,
2293 p_init_msg_list => p_init_msg_list,
2294 p_commit => p_commit,
2295 x_return_status => x_return_status,
2296 x_msg_count => x_msg_count,
2297 x_msg_data => x_msg_data,
2298 p_cons_bill_tbl => cons_bill_tbl,
2299 p_saved_bill_rec => saved_bill_rec,
2300 p_update_tbl => l_update_tbl);
2301 END IF;
2302 EXIT WHEN cm_two_lvl_csr%NOTFOUND;
2303 END LOOP;
2304 CLOSE cm_two_lvl_csr;
2305
2306 -- -----------------------------
2307 -- Process Last set of records
2308 -- -----------------------------
2309 process_break(p_contract_number,
2310 p_commit,
2311 saved_bill_rec,
2312 l_update_tbl);
2313
2314 IF FND_API.To_Boolean( p_commit ) THEN
2315 COMMIT;
2316 END IF;
2317
2318 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2319 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '========== END: CREDIT MEMO Two LEVEL Processing ============');
2320 END IF;
2321
2322 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2323 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '========== START: CREDIT MEMO Three LEVEL Processing ============');
2324 END IF;
2325
2326 -- ---------------------------------------
2327 -- Initialize table and record parameters
2328 -- ---------------------------------------
2329 saved_bill_rec := l_init_bill_rec;
2330 cons_bill_tbl.delete;
2331 l_update_tbl.delete;
2332
2333 OPEN cm_three_lvl_csr;
2334 LOOP
2335 cons_bill_tbl.delete;
2336 FETCH cm_three_lvl_csr BULK COLLECT INTO cons_bill_tbl LIMIT L_FETCH_SIZE;
2337
2338 FND_FILE.PUT_LINE (FND_FILE.LOG, 'cm_three_lvl_csr cons_bill_tbl count is: '||cons_bill_tbl.COUNT);
2339 IF cons_bill_tbl.COUNT > 0 THEN
2340 process_cons_bill_tbl(
2341 p_contract_number => p_contract_number,
2342 p_api_version => p_api_version,
2343 p_init_msg_list => p_init_msg_list,
2344 p_commit => p_commit,
2345 x_return_status => x_return_status,
2346 x_msg_count => x_msg_count,
2347 x_msg_data => x_msg_data,
2348 p_cons_bill_tbl => cons_bill_tbl,
2349 p_saved_bill_rec => saved_bill_rec,
2350 p_update_tbl => l_update_tbl);
2351 END IF;
2352 EXIT WHEN cm_three_lvl_csr%NOTFOUND;
2353 END LOOP;
2354 CLOSE cm_three_lvl_csr;
2355
2356 -- -----------------------------
2357 -- Process Last set of records
2358 -- -----------------------------
2359 process_break(p_contract_number,
2360 p_commit,
2361 saved_bill_rec,
2362 l_update_tbl);
2363
2364 IF FND_API.To_Boolean( p_commit ) THEN
2365 COMMIT;
2366 END IF;
2367
2368 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2369 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '========== END: CREDIT MEMO Three LEVEL Processing ============');
2370 END IF;
2371
2372 -- ELSE -- Null value for contract specific consolidation
2373 -- NULL;
2374 -- END IF;
2375
2376
2377
2378
2379
2380
2381
2382 --Initialize the local variables to null, start afresh
2383
2384
2385 -- Set the table index to the next value
2386 cnr_tab_idx := NVL (cnr_update_tbl.LAST, 0) + 1;
2387
2388 ------------------------------------------------------------
2389 -- Prime UBB Tracker
2390 ------------------------------------------------------------
2391 l_clg_id := -1;
2392 l_cnr_id := NULL;
2393 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2394 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '========== START: UBB Processing ============');
2395 END IF;
2396
2397 l_commit_cnt := 0;
2398
2399 FOR ubb_rec IN ubb_csr LOOP
2400 l_commit_cnt := l_commit_cnt + 1;
2401
2402 l_contract_number := null;
2403 l_stream_name1 := null;
2404
2405 OPEN cntrct_csr ( ubb_rec.contract_id );
2406 FETCH cntrct_csr INTO l_contract_number;
2407 CLOSE cntrct_csr;
2408
2409 OPEN strm_csr ( ubb_rec.stream_id );
2410 FETCH strm_csr INTO l_stream_name1;
2411 CLOSE strm_csr;
2412
2413 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2414 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, ' Processing Contract: '||l_contract_number||' ,Stream: '||l_stream_name1);
2415 END IF;
2416
2417 -- Start Fresh for Creating next UBB
2418 l_lln_id := NULL;
2419 l_lsm_id := NULL;
2420
2421 --------------------------------------------
2422 -- Create an Invoice Header if first time
2423 -- Or break detected
2424 --------------------------------------------
2425 IF ( l_clg_id <> ubb_rec.clg_id ) THEN
2426 --------------------------------
2427 -- Reset CNR ID for new Value
2428 -------------------------------
2429
2430 -- Commit and reset if the limit reached
2431 IF l_commit_cnt > G_Commit_Max THEN
2432 IF FND_API.To_Boolean( p_commit ) THEN
2433 COMMIT;
2434 END IF;
2435 l_commit_cnt := 0;
2436 END IF;
2437
2438 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2439 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** CREATE CONSOLIDATED INVOICE HEADER ***');
2440 END IF;
2441 l_cnr_id := NULL;
2442 create_new_invoice(
2443 ubb_rec.bill_to_site,
2444 ubb_rec.customer_id,
2445 ubb_rec.currency,
2446 ubb_rec.payment_method,
2447 --vthiruva bug#4438971 fix start..24-JUN-2005..passing fetched inf_id
2448 --NULL,
2449 ubb_rec.inf_id,
2450 --vthiruva bug#4438971 fix end
2451 ubb_rec.set_of_books_id,
2452 ubb_rec.private_label,
2453 ubb_rec.date_consolidated,
2454 ubb_rec.org_id,
2455 ubb_rec.legal_entity_id, -- for LE Uptake project 08-11-2006
2456 l_cnr_id,
2457 l_cons_inv_num);
2458 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2459 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** DONE CREATION OF CONSOLIDATED INVOICE HEADER.Assigned Id: '||l_cnr_id||' ***');
2460 END IF;
2461
2462
2463 -----------------------------------
2464 -- Save CLG ID for break detection
2465 -----------------------------------
2466 l_clg_id := ubb_rec.clg_id;
2467
2468 END IF;
2469
2470
2471 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2472 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** CREATE CONSOLIDATED INVOICE LINE *** for CNR_ID '||l_cnr_id);
2473 END IF;
2474 -- Null out current value in local variable.
2475 l_lln_id := NULL;
2476 create_new_line(
2477 ubb_rec.contract_id,
2478 l_cnr_id,
2479 ubb_rec.kle_id,
2480 NULL,
2481 ubb_rec.currency,
2482 ubb_rec.ubb_line_number,
2483 'CHARGE',
2484 'Y',
2485 'N',
2486 'N',
2487 l_lln_id
2488 );
2489 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2490 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** DONE CREATION OF CONSOLIDATED INVOICE LINE.Assigned Id: '||l_lln_id||' ***');
2491 END IF;
2492
2493
2494
2495 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2496 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** CREATE CONSOLIDATED INVOICE STREAMS *** for CNR_ID: '||l_cnr_id||' and LLN_ID: '||l_lln_id);
2497 END IF;
2498 --Null out local variable.
2499 l_lsm_id := null;
2500 create_new_streams(
2501 l_lln_id,
2502 ubb_rec.stream_id,
2503 ubb_rec.kle_id,
2504 ubb_rec.contract_id,
2505 ubb_rec.ubb_amount,
2506 ubb_rec.sel_id,
2507 l_lsm_id,
2508 x_return_status);
2509 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2510 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** DONE CREATION OF CONSOLIDATED INVOICE STREAMS.Assigned Id: '||l_lsm_id||' ***');
2511 END IF;
2512
2513 -- Build a PL/SQL table for later Updates
2514 cnr_update_tbl(cnr_tab_idx).cnr_id := l_cnr_id;
2515 cnr_update_tbl(cnr_tab_idx).lln_id := l_lln_id;
2516 cnr_update_tbl(cnr_tab_idx).lsm_id := l_lsm_id;
2517 cnr_update_tbl(cnr_tab_idx).xsi_id := ubb_rec.xsi_id;
2518 cnr_update_tbl(cnr_tab_idx).xls_id := ubb_rec.xls_id;
2519 cnr_update_tbl(cnr_tab_idx).return_status := x_return_status;
2520
2521 -- Increment the PL/SQL table index for updates
2522 cnr_tab_idx := cnr_tab_idx + 1;
2523 END LOOP; -- UBB Processing
2524
2525 IF FND_API.To_Boolean( p_commit ) THEN
2526 COMMIT;
2527 END IF;
2528
2529 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2530 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '========== END: UBB Processing ============');
2531 END IF;
2532
2533 ---------------------------------------------------
2534 -- Process all Termination Quote requests --
2535 ---------------------------------------------------
2536 -- Set the table index to the next value
2537 cnr_tab_idx := NVL (cnr_update_tbl.LAST, 0) + 1;
2538
2539 ------------------------------------------------------------
2540 -- Prime QTE Tracker
2541 ------------------------------------------------------------
2542 l_qte_id := -1;
2543 l_qte_cust_id := -1;
2544
2545 -- Start Bug 4731187
2546 l_qte_trx_date := (sysdate - 732000);
2547 -- End Bug 4731187
2548 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2549 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '========== START: TERMINATION QUOTE Processing ============');
2550 END IF;
2551
2552 l_commit_cnt := 0;
2553
2554 FOR qte_rec in qte_csr LOOP
2555
2556 l_commit_cnt := l_commit_cnt + 1;
2557
2558 l_contract_number := null;
2559 l_stream_name1 := null;
2560
2561 OPEN cntrct_csr ( qte_rec.contract_id );
2562 FETCH cntrct_csr INTO l_contract_number;
2563 CLOSE cntrct_csr;
2564
2565 OPEN strm_csr ( qte_rec.stream_id );
2566 FETCH strm_csr INTO l_stream_name1;
2567 CLOSE strm_csr;
2568
2569 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2570 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, ' Processing Contract: '||l_contract_number||' ,Stream: '||l_stream_name1);
2571 END IF;
2572
2573 -- Start Fresh for Creating next UBB
2574 l_lln_id := NULL;
2575 l_lsm_id := NULL;
2576
2577 --------------------------------------------
2578 -- Create an Invoice Header if first time
2579 -- Or break detected
2580 --------------------------------------------
2581 -- Start Bug 4731187
2582 IF ( (l_qte_id <> qte_rec.qte_id)
2583 Or (l_qte_cust_id <> qte_rec.customer_id)
2584 Or ( l_qte_id = qte_rec.qte_id AND l_qte_trx_date <> qte_rec.date_consolidated )
2585
2586 ) THEN
2587 -- End Bug 4731187
2588 --------------------------------
2589 -- Reset CNR ID for new Value
2590 -------------------------------
2591
2592 -- Commit and reset if the limit reached
2593 IF l_commit_cnt > G_Commit_Max THEN
2594 IF FND_API.To_Boolean( p_commit ) THEN
2595 COMMIT;
2596 END IF;
2597 l_commit_cnt := 0;
2598 END IF;
2599
2600 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2601 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** CREATE CONSOLIDATED INVOICE HEADER ***');
2602 END IF;
2603 l_cnr_id := NULL;
2604 create_new_invoice(
2605 qte_rec.bill_to_site,
2606 qte_rec.customer_id,
2607 qte_rec.currency,
2608 qte_rec.payment_method,
2609 --vthiruva bug#4438971 fix start..24-JUN-2005..passing fetched inf_id
2610 --NULL,
2611 qte_rec.inf_id,
2612 --vthiruva bug#4438971 fix end
2613 qte_rec.set_of_books_id,
2614 qte_rec.private_label,
2615 qte_rec.date_consolidated,
2616 qte_rec.org_id,
2617 qte_rec.legal_entity_id, -- for LE Uptake project 08-11-2006
2618 l_cnr_id,
2619 l_cons_inv_num);
2620 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2621 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** DONE CREATION OF CONSOLIDATED INVOICE HEADER.Assigned Id: '||l_cnr_id||' ***');
2622 END IF;
2623
2624
2625 -----------------------------------
2626 -- Save QTE ID for break detection
2627 -----------------------------------
2628 l_qte_id := qte_rec.qte_id;
2629 l_qte_cust_id := qte_rec.customer_id;
2630
2631 -- Start Bug 4731187
2632 l_qte_trx_date := qte_rec.date_consolidated;
2633 -- End Bug 4731187
2634 END IF;
2635
2636
2637 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2638 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** CREATE CONSOLIDATED INVOICE LINE *** for CNR_ID '||l_cnr_id);
2639 END IF;
2640 -- Null out current value in local variable.
2641 l_lln_id := NULL;
2642 create_new_line(
2643 qte_rec.contract_id,
2644 l_cnr_id,
2645 qte_rec.kle_id,
2646 NULL,
2647 qte_rec.currency,
2648 qte_rec.qte_line_number,
2649 qte_rec.description,
2650 'Y',
2651 'N',
2652 'N',
2653 l_lln_id
2654 );
2655 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2656 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** DONE CREATION OF CONSOLIDATED INVOICE LINE.Assigned Id: '||l_lln_id||' ***');
2657 END IF;
2658
2659
2660
2661 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2662 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** CREATE CONSOLIDATED INVOICE STREAMS *** for CNR_ID: '||l_cnr_id||' and LLN_ID: '||l_lln_id);
2663 END IF;
2664 --Null out local variable.
2665 l_lsm_id := null;
2666 create_new_streams(
2667 l_lln_id,
2668 qte_rec.stream_id,
2669 qte_rec.kle_id,
2670 qte_rec.contract_id,
2671 qte_rec.qte_amount,
2672 qte_rec.sel_id,
2673 l_lsm_id,
2674 x_return_status);
2675 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2676 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** DONE CREATION OF CONSOLIDATED INVOICE STREAMS.Assigned Id: '||l_lsm_id||' ***');
2677 END IF;
2678
2679 -- Build a PL/SQL table for later Updates
2680 cnr_update_tbl(cnr_tab_idx).cnr_id := l_cnr_id;
2681 cnr_update_tbl(cnr_tab_idx).lln_id := l_lln_id;
2682 cnr_update_tbl(cnr_tab_idx).lsm_id := l_lsm_id;
2683 cnr_update_tbl(cnr_tab_idx).xsi_id := qte_rec.xsi_id;
2684 cnr_update_tbl(cnr_tab_idx).xls_id := qte_rec.xls_id;
2685 cnr_update_tbl(cnr_tab_idx).return_status := x_return_status;
2686
2687 -- Increment the PL/SQL table index for updates
2688 cnr_tab_idx := cnr_tab_idx + 1;
2689
2690 END LOOP;
2691 IF FND_API.To_Boolean( p_commit ) THEN
2692 COMMIT;
2693 END IF;
2694
2695 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2696 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '========== END: TERMINATION QUOTE Processing ============');
2697 END IF;
2698
2699 -----------------------------------
2700 --Processing Collections Records
2701 -----------------------------------
2702
2703 -- Set the table index to the next value
2704 cnr_tab_idx := NVL (cnr_update_tbl.LAST, 0) + 1;
2705
2706 ------------------------------------------------------------
2707 -- Prime CPY Tracker
2708 ------------------------------------------------------------
2709 l_cpy_id := -1;
2710
2711 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2712 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '========== START: Collections Records Processing ============');
2713 END IF;
2714
2715 l_commit_cnt := 0;
2716
2717 FOR cpy_rec in cpy_csr LOOP
2718
2719 l_commit_cnt := l_commit_cnt + 1;
2720
2721 l_contract_number := null;
2722 l_stream_name1 := null;
2723
2724 OPEN cntrct_csr ( cpy_rec.contract_id );
2725 FETCH cntrct_csr INTO l_contract_number;
2726 CLOSE cntrct_csr;
2727
2728 OPEN strm_csr ( cpy_rec.stream_id );
2729 FETCH strm_csr INTO l_stream_name1;
2730 CLOSE strm_csr;
2731
2732 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2733 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, ' Processing Contract: '||l_contract_number||' ,Stream: '||l_stream_name1);
2734 END IF;
2735
2736 -- Start Fresh for Creating next UBB
2737 l_lln_id := NULL;
2738 l_lsm_id := NULL;
2739
2740 --------------------------------------------
2741 -- Create an Invoice Header if first time
2742 -- Or break detected
2743 --------------------------------------------
2744 IF ( l_cpy_id <> cpy_rec.cpy_id ) THEN
2745 --------------------------------
2746 -- Reset CNR ID for new Value
2747 -------------------------------
2748 -- Commit and reset if the limit reached
2749 IF l_commit_cnt > G_Commit_Max THEN
2750 IF FND_API.To_Boolean( p_commit ) THEN
2751 COMMIT;
2752 END IF;
2753 l_commit_cnt := 0;
2754 END IF;
2755
2756 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2757 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** CREATE CONSOLIDATED INVOICE HEADER ***');
2758 END IF;
2759 l_cnr_id := NULL;
2760 create_new_invoice(
2761 cpy_rec.bill_to_site,
2762 cpy_rec.customer_id,
2763 cpy_rec.currency,
2764 cpy_rec.payment_method,
2765 --vthiruva bug#4438971 fix start..24-JUN-2005..passing fetched inf_id
2766 --NULL,
2767 cpy_rec.inf_id,
2768 --vthiruva bug#4438971 fix end
2769 cpy_rec.set_of_books_id,
2770 cpy_rec.private_label,
2771 cpy_rec.date_consolidated,
2772 cpy_rec.org_id,
2773 cpy_rec.legal_entity_id, -- for LE Uptake project 08-11-2006
2774 l_cnr_id,
2775 l_cons_inv_num);
2776 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2777 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** DONE CREATION OF CONSOLIDATED INVOICE HEADER.Assigned Id: '||l_cnr_id||' ***');
2778 END IF;
2779
2780
2781 -----------------------------------
2782 -- Save QTE ID for break detection
2783 -----------------------------------
2784 l_cpy_id := cpy_rec.cpy_id;
2785
2786 END IF;
2787
2788 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2789 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** CREATE CONSOLIDATED INVOICE LINE *** for CNR_ID '||l_cnr_id);
2790 END IF;
2791 -- Null out current value in local variable.
2792 l_lln_id := NULL;
2793 create_new_line(
2794 cpy_rec.contract_id,
2795 l_cnr_id,
2796 cpy_rec.kle_id,
2797 NULL,
2798 cpy_rec.currency,
2799 cpy_rec.cpy_line_number,
2800 'CHARGE',
2801 'Y',
2802 'N',
2803 'N',
2804 l_lln_id
2805 );
2806 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2807 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** DONE CREATION OF CONSOLIDATED INVOICE LINE.Assigned Id: '||l_lln_id||' ***');
2808 END IF;
2809
2810 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2811 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** CREATE CONSOLIDATED INVOICE STREAMS *** for CNR_ID: '||l_cnr_id||' and LLN_ID: '||l_lln_id);
2812 END IF;
2813 --Null out local variable.
2814 l_lsm_id := null;
2815 create_new_streams(
2816 l_lln_id,
2817 cpy_rec.stream_id,
2818 cpy_rec.kle_id,
2819 cpy_rec.contract_id,
2820 cpy_rec.cpy_amount,
2821 cpy_rec.sel_id,
2822 l_lsm_id,
2823 x_return_status);
2824 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2825 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '*** DONE CREATION OF CONSOLIDATED INVOICE STREAMS.Assigned Id: '||l_lsm_id||' ***');
2826 END IF;
2827
2828 -- Build a PL/SQL table for later Updates
2829 cnr_update_tbl(cnr_tab_idx).cnr_id := l_cnr_id;
2830 cnr_update_tbl(cnr_tab_idx).lln_id := l_lln_id;
2831 cnr_update_tbl(cnr_tab_idx).lsm_id := l_lsm_id;
2832 cnr_update_tbl(cnr_tab_idx).xsi_id := cpy_rec.xsi_id;
2833 cnr_update_tbl(cnr_tab_idx).xls_id := cpy_rec.xls_id;
2834 cnr_update_tbl(cnr_tab_idx).return_status := x_return_status;
2835
2836 -- Increment the PL/SQL table index for updates
2837 cnr_tab_idx := cnr_tab_idx + 1;
2838
2839 END LOOP;
2840 IF FND_API.To_Boolean( p_commit ) THEN
2841 COMMIT;
2842 END IF;
2843
2844 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2845 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '========== END: Collections Records Processing ============');
2846 END IF;
2847
2848 -- Update the XTRX columns in XSI and XLS and Resequence the
2849 -- Consolidated bill lines
2850
2851 l_cnr_id := -1;
2852
2853 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2854 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '========== START: UPDATING Processed Records ============');
2855 END IF;
2856
2857 l_commit_cnt := 0;
2858
2859 IF (cnr_update_tbl.COUNT > 0) THEN
2860 cnr_tab_idx := cnr_update_tbl.FIRST;
2861 LOOP
2862 l_commit_cnt := l_commit_cnt + 1;
2863
2864 -- This will resequence the consolidated bill lines
2865 -- and update the amounts at the line and consolidated bill
2866 -- level
2867 IF l_cnr_id <> cnr_update_tbl(cnr_tab_idx).cnr_id THEN
2868 l_cnr_id := cnr_update_tbl(cnr_tab_idx).cnr_id;
2869
2870 l_seq_num := 0;
2871 FOR line_seq IN line_seq_csr(l_cnr_id) LOOP
2872 l_seq_num := l_seq_num + 1;
2873
2874 l_line_amount := 0;
2875 BEGIN
2876 SELECT SUM(amount) INTO l_line_amount
2877 FROM okl_cnsld_ar_strms_v
2878 WHERE lln_id = line_seq.id;
2879 EXCEPTION
2880 WHEN OTHERS THEN
2881 l_line_amount := 0;
2882 END;
2883
2884 --Initialize records
2885 u_llnv_rec := null_llnv_rec;
2886 x_llnv_rec := null_llnv_rec;
2887 -- Update the consolidated lines with line num
2888 -- and amount
2889 u_llnv_rec.id := line_seq.id;
2890 u_llnv_rec.sequence_number := l_seq_num;
2891 u_llnv_rec.amount := l_line_amount;
2892
2893 UPDATE Okl_Cnsld_Ar_Lines_b
2894 SET sequence_number = l_seq_num,
2895 amount = l_line_amount
2896 WHERE id = line_seq.id;
2897
2898
2899 END LOOP;
2900
2901 -- Update the amount on the Cons Bill header
2902 l_consbill_amount := 0;
2903 BEGIN
2904 SELECT SUM(amount) INTO l_consbill_amount
2905 FROM okl_cnsld_ar_lines_v
2906 WHERE cnr_id = l_cnr_id;
2907 EXCEPTION
2908 WHEN OTHERS THEN
2909 l_consbill_amount := 0;
2910 END;
2911
2912 --Update the consolidated headers table
2913 --Initialize records
2914 u_cnrv_rec := null_cnrv_rec;
2915 x_cnrv_rec := null_cnrv_rec;
2916
2917 u_cnrv_rec.id := l_cnr_id;
2918 u_cnrv_rec.amount := l_consbill_amount;
2919 u_cnrv_rec.trx_status_code := 'PROCESSED';
2920 -- Start of wraper code generated automatically by Debug code generator for Okl_Cnsld_Ar_Hdrs_Pub.UPDATE_CNSLD_AR_HDRS
2921
2922 UPDATE Okl_Cnsld_Ar_Hdrs_b
2923 SET amount = l_consbill_amount,
2924 trx_status_code = 'PROCESSED'
2925 WHERE id = l_cnr_id;
2926
2927 -- End of wraper code generated automatically by Debug code generator for Okl_Cnsld_Ar_Hdrs_Pub.UPDATE_CNSLD_AR_HDRS
2928
2929 END IF;
2930
2931 -- Update the xtrx_fields on XSI
2932 -- Initialize records
2933 l_xsiv_rec := null_xsiv_rec;
2934 x_xsiv_rec := null_xsiv_rec;
2935
2936 -----------------------------------------------------------
2937 -- Update only if consolidation entries were created
2938 -----------------------------------------------------------
2939 IF cnr_update_tbl(cnr_tab_idx).cnr_id IS NOT NULL THEN
2940
2941 l_xsiv_rec.id := cnr_update_tbl(cnr_tab_idx).xsi_id;
2942
2943 -- Initialize fields
2944 l_xsiv_rec.XTRX_CONS_INVOICE_NUMBER := NULL;
2945 l_xsiv_rec.XTRX_FORMAT_TYPE := NULL;
2946
2947 BEGIN
2948 SELECT cnr.consolidated_invoice_number,
2949 inf.name
2950 INTO l_xsiv_rec.XTRX_CONS_INVOICE_NUMBER,
2951 l_xsiv_rec.XTRX_FORMAT_TYPE
2952 FROM okl_cnsld_ar_hdrs_v cnr,
2953 okl_invoice_formats_b infb,
2954 okl_invoice_formats_tl inf
2955 WHERE cnr.id = cnr_update_tbl(cnr_tab_idx).cnr_id
2956 AND cnr.inf_id = infb.id(+)
2957 and infb.id = inf.id(+)
2958 and inf.language(+) = userenv('LANG');
2959 EXCEPTION
2960 WHEN OTHERS THEN
2961 l_xsiv_rec.XTRX_CONS_INVOICE_NUMBER := NULL;
2962 l_xsiv_rec.XTRX_FORMAT_TYPE := NULL;
2963 END;
2964
2965 l_xsiv_rec.XTRX_INVOICE_PULL_YN := 'Y';
2966 --l_xsiv_rec.XTRX_INVOICE_PULL_YN := from rule;
2967
2968
2969 IF p_contract_number IS NULL THEN
2970 -- ------------------------------------------------
2971 -- To be used by Receivable Invoice Transfer to AR
2972 -- ------------------------------------------------
2973 l_xsiv_rec.TRX_STATUS_CODE := 'WORKING';
2974 ELSE
2975 -- -------------------------------------------
2976 -- To be used by the real time invoice API
2977 -- -------------------------------------------
2978 l_xsiv_rec.TRX_STATUS_CODE := 'ENTERED';
2979 END IF;
2980
2981 UPDATE Okl_Ext_Sell_Invs_b
2982 SET TRX_STATUS_CODE = l_xsiv_rec.TRX_STATUS_CODE,
2983 XTRX_INVOICE_PULL_YN = 'Y'
2984 WHERE id = cnr_update_tbl(cnr_tab_idx).xsi_id;
2985
2986
2987 UPDATE Okl_Ext_Sell_Invs_tl
2988 SET XTRX_CONS_INVOICE_NUMBER = l_xsiv_rec.XTRX_CONS_INVOICE_NUMBER,
2989 XTRX_FORMAT_TYPE = l_xsiv_rec.XTRX_FORMAT_TYPE
2990 WHERE id = cnr_update_tbl(cnr_tab_idx).xsi_id;
2991
2992 -- Update the xtrx_fields on XLS
2993 -- Initialize records
2994 l_xlsv_rec := null_xlsv_rec;
2995 x_xlsv_rec := null_xlsv_rec;
2996
2997 l_xlsv_rec.id := cnr_update_tbl(cnr_tab_idx).xls_id;
2998
2999 -- Initialize fields
3000 l_xlsv_rec.XTRX_CONS_LINE_NUMBER := NULL;
3001 l_xlsv_rec.XTRX_CONTRACT := NULL;
3002 l_xlsv_rec.XTRX_STREAM_GROUP := NULL;
3003 l_xlsv_rec.XTRX_ASSET := NULL;
3004 l_xlsv_rec.XTRX_STREAM_TYPE := NULL;
3005
3006 BEGIN
3007 SELECT TO_CHAR(lln.sequence_number),
3008 SUBSTR(CONTRACT_NUMBER,1,30),
3009 -- TO_CHAR(lln.kle_id),
3010 -- Added NVL for bug 4528015
3011 NVL(ilt.name, lln.line_type)
3012 INTO l_xlsv_rec.XTRX_CONS_LINE_NUMBER,
3013 l_xlsv_rec.XTRX_CONTRACT,
3014 -- l_xlsv_rec.XTRX_ASSET,
3015 l_xlsv_rec.XTRX_STREAM_GROUP
3016 FROM okl_cnsld_ar_lines_v lln,
3017 okl_invc_line_types_v ilt,
3018 okc_k_headers_b khr
3019 WHERE lln.id = cnr_update_tbl(cnr_tab_idx).lln_id AND
3020 khr.id = lln.khr_id AND
3021 lln.ilt_id = ilt.id(+);
3022 EXCEPTION
3023 WHEN OTHERS THEN
3024 l_xlsv_rec.XTRX_CONS_LINE_NUMBER := NULL;
3025 l_xlsv_rec.XTRX_CONTRACT := NULL;
3026 l_xlsv_rec.XTRX_STREAM_GROUP := NULL;
3027 END;
3028
3029
3030 l_xlsv_rec.XTRX_ASSET := NULL;
3031 OPEN asset_line_csr(cnr_update_tbl(cnr_tab_idx).lsm_id);
3032 FETCH asset_line_csr INTO l_xlsv_rec.XTRX_ASSET;
3033 CLOSE asset_line_csr;
3034
3035 -- Check for Subline asset for Service
3036 IF l_xlsv_rec.XTRX_ASSET IS NULL THEN
3037 OPEN service_asset_csr(cnr_update_tbl(cnr_tab_idx).lsm_id);
3038 FETCH service_asset_csr INTO l_xlsv_rec.XTRX_ASSET;
3039 CLOSE service_asset_csr;
3040 END IF;
3041
3042 BEGIN
3043 SELECT sty.name
3044 INTO l_xlsv_rec.XTRX_STREAM_TYPE
3045 FROM okl_cnsld_ar_strms_v lsm,
3046 okl_strm_type_v sty
3047 WHERE lsm.id = cnr_update_tbl(cnr_tab_idx).lsm_id AND
3048 sty.id = lsm.sty_id;
3049 EXCEPTION
3050 WHEN OTHERS THEN
3051 l_xlsv_rec.XTRX_STREAM_TYPE := NULL;
3052 END;
3053
3054
3055 -- Update the Contract Number using khr_id in the
3056 -- Streams table. This will override the xtrx_contract
3057 -- set in the block select above.
3058
3059 -- Initialize variables
3060 l_temp_khr_id := NULL;
3061 l_xlsv_rec.XTRX_CONTRACT := NULL;
3062 l_xlsv_rec.LSM_ID := NULL;
3063 l_xlsv_rec.XTRX_CONS_STREAM_ID := NULL;
3064
3065
3066 OPEN get_khr_id(cnr_update_tbl(cnr_tab_idx).lsm_id);
3067 FETCH get_khr_id INTO l_temp_khr_id;
3068 CLOSE get_khr_id;
3069
3070 OPEN cntrct_csr( l_temp_khr_id );
3071 FETCH cntrct_csr INTO l_xlsv_rec.XTRX_CONTRACT;
3072 CLOSE cntrct_csr;
3073
3074 l_xlsv_rec.LSM_ID := cnr_update_tbl(cnr_tab_idx).lsm_id;
3075 l_xlsv_rec.XTRX_CONS_STREAM_ID := cnr_update_tbl(cnr_tab_idx).lsm_id;
3076
3077
3078 -- Update the XLS
3079 -- Start of wraper code generated automatically by Debug code generator for Okl_Xtl_Sell_Invs_Pub.UPDATE_XTL_SELL_INVS
3080 IF(IS_DEBUG_PROCEDURE_ON) THEN
3081 BEGIN
3082 OKL_DEBUG_PUB.LOG_DEBUG(L_LEVEL_PROCEDURE,L_MODULE,'Begin Debug OKLRKONB.pls call Okl_Xtl_Sell_Invs_Pub.UPDATE_XTL_SELL_INVS ');
3083 END;
3084 END IF;
3085
3086 UPDATE Okl_Xtl_Sell_Invs_b
3087 SET LSM_ID = l_xlsv_rec.LSM_ID,
3088 XTRX_CONS_LINE_NUMBER = l_xlsv_rec.XTRX_CONS_LINE_NUMBER,
3089 XTRX_CONS_STREAM_ID = l_xlsv_rec.XTRX_CONS_STREAM_ID
3090 WHERE id = l_xlsv_rec.id;
3091
3092 UPDATE Okl_Xtl_Sell_Invs_tl
3093 SET XTRX_CONTRACT = l_xlsv_rec.XTRX_CONTRACT,
3094 XTRX_ASSET = l_xlsv_rec.XTRX_ASSET,
3095 XTRX_STREAM_TYPE = l_xlsv_rec.XTRX_STREAM_TYPE,
3096 XTRX_STREAM_GROUP = l_xlsv_rec.XTRX_STREAM_GROUP
3097 WHERE id = l_xlsv_rec.id;
3098
3099 ELSE-- CNR entries not created
3100
3101 UPDATE okl_ext_sell_invs_b
3102 SET trx_status_code = 'ERROR'
3103 WHERE id = cnr_update_tbl(cnr_tab_idx).xsi_id;
3104
3105 END IF; -- CNR entries not created
3106
3107 -- Commit and reset if the limit reached
3108 IF l_commit_cnt > G_Commit_Max THEN
3109 IF FND_API.To_Boolean( p_commit ) THEN
3110 COMMIT;
3111 END IF;
3112 l_commit_cnt := 0;
3113 END IF;
3114 -- End of wraper code generated automatically by Debug code generator for Okl_Xtl_Sell_Invs_Pub.UPDATE_XTL_SELL_INVS
3115 EXIT WHEN (cnr_tab_idx = cnr_update_tbl.LAST);
3116 cnr_tab_idx := cnr_update_tbl.NEXT(cnr_tab_idx);
3117 END LOOP;
3118 END IF;
3119 IF FND_API.To_Boolean( p_commit ) THEN
3120 COMMIT;
3121 END IF;
3122
3123 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3124 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '========== END: UPDATING Processed Records ============');
3125 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, '========== **** END PROGRAM EXECUTION **** ============');
3126 END IF;
3127
3128 -- ----------------------------------------------------------
3129 -- Print net output by currency
3130 -- ----------------------------------------------------------
3131 -- Get the request Id
3132 l_request_id := NULL;
3133 OPEN req_id_csr;
3134 FETCH req_id_csr INTO l_request_id;
3135 CLOSE req_id_csr;
3136
3137 processed_sts := 'PROCESSED';
3138 error_sts := 'ERROR';
3139
3140 ----------------------------------------
3141 -- Get Operating unit name
3142 ----------------------------------------
3143 l_op_unit_name := NULL;
3144 OPEN op_unit_csr;
3145 FETCH op_unit_csr INTO l_op_unit_name;
3146 CLOSE op_unit_csr;
3147
3148 -- Start New Out File stmathew 15-OCT-2004
3149 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 54, ' ')||'Oracle Leasing and Finance Management'||lpad(' ', 55, ' '));
3150 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 132, ' '));
3151 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 51, ' ')||'Receivable Bills Consolidation'||lpad(' ', 51, ' '));
3152 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 51, ' ')||'------------------------------'||lpad(' ', 51, ' '));
3153 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 132, ' '));
3154 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 132, ' '));
3155 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Operating Unit: '||l_op_unit_name);
3156 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Request Id: '||l_request_id||lpad(' ',74,' ') ||'Run Date: '||to_char(sysdate));
3157 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad('-', 132, '-'));
3158 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 132, ' '));
3159 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 132, ' '));
3160 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Processing Details:'||lpad(' ', 113, ' '));
3161 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 132, ' '));
3162
3163 -- ----------------------------------------
3164 -- Loop thru consolidated invoices created
3165 -- ----------------------------------------
3166 FOR curr_rec in curr_csr( l_request_id ) LOOP
3167
3168 l_succ_cnt := 0;
3169 l_err_cnt := 0;
3170
3171 -- ---------------------------------------------
3172 -- Success Count
3173 -- ---------------------------------------------
3174 OPEN cnr_cnt_csr( l_request_id, processed_sts, curr_rec.currency_code );
3175 FETCH cnr_cnt_csr INTO l_succ_cnt;
3176 CLOSE cnr_cnt_csr;
3177
3178 -- ---------------------------------------------
3179 -- Error Count
3180 -- ---------------------------------------------
3181 OPEN cnr_cnt_csr( l_request_id, error_sts, curr_rec.currency_code );
3182 FETCH cnr_cnt_csr INTO l_err_cnt;
3183 CLOSE cnr_cnt_csr;
3184
3185 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Currency '||curr_rec.currency_code);
3186 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Number of Consolidated Invoices Created: '||(l_succ_cnt+l_err_cnt));
3187 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Number of Successful Invoice Lines : '||l_succ_cnt);
3188 FND_FILE.PUT_LINE (FND_FILE.OUTPUT, ' Number of Errored Invoice Lines : '||l_err_cnt);
3189
3190 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,rpad(' ', 132, ' '));
3191
3192
3193 END LOOP;
3194
3195 IF x_msg_count > 0 THEN
3196 FOR i IN 1..x_msg_count LOOP
3197 IF i = 1 THEN
3198 FND_FILE.PUT_LINE (FND_FILE.LOG,'Details of Errored Stream Elements:'||lpad(' ', 97, ' '));
3199 FND_FILE.PUT_LINE (FND_FILE.LOG,rpad(' ', 132, ' '));
3200 END IF;
3201
3202 fnd_msg_pub.get (p_msg_index => i,
3203 p_encoded => 'F',
3204 p_data => lx_msg_data,
3205 p_msg_index_out => l_msg_index_out);
3206
3207 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,TO_CHAR(i) || ': ' || lx_msg_data);
3208 END LOOP;
3209 END IF;
3210
3211 IF (L_DEBUG_ENABLED='Y' and FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3212 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,'okl_cons_bill'
3213 ,'End(-)');
3214 END IF;
3215
3216 -- -------------------------------------------
3217 -- Purge data from the Parallel process Table
3218 -- -------------------------------------------
3219
3220 IF p_assigned_process IS NOT NULL THEN
3221 DELETE OKL_PARALLEL_PROCESSES
3222 WHERE assigned_process = p_assigned_process;
3223 COMMIT;
3224 END IF;
3225
3226
3227 ------------------------------------------------------------
3228 -- End processing
3229 ------------------------------------------------------------
3230
3231 Okl_Api.END_ACTIVITY (
3232 x_msg_count => x_msg_count,
3233 x_msg_data => x_msg_data);
3234
3235
3236 EXCEPTION
3237 ------------------------------------------------------------
3238 -- Exception handling
3239 ------------------------------------------------------------
3240
3241 WHEN Okl_Api.G_EXCEPTION_ERROR THEN
3242
3243 -- -------------------------------------------
3244 -- Purge data from the Parallel process Table
3245 -- -------------------------------------------
3246 IF p_assigned_process IS NOT NULL THEN
3247 DELETE OKL_PARALLEL_PROCESSES
3248 WHERE assigned_process = p_assigned_process;
3249 COMMIT;
3250 END IF;
3251
3252 IF (L_DEBUG_ENABLED='Y' and FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3253 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'okl_cons_bill',
3254 'EXCEPTION :'||'Okl_Api.G_EXCEPTION_ERROR');
3255 END IF;
3256
3257 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3258 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'*=> Error Message(O1): '||SQLERRM);
3259 END IF;
3260 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
3261 p_api_name => l_api_name,
3262 p_pkg_name => G_PKG_NAME,
3263 p_exc_name => 'Okl_Api.G_RET_STS_ERROR',
3264 x_msg_count => x_msg_count,
3265 x_msg_data => x_msg_data,
3266 p_api_type => '_PVT');
3267
3268 WHEN Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR THEN
3269
3270 -- -------------------------------------------
3271 -- Purge data from the Parallel process Table
3272 -- -------------------------------------------
3273 IF p_assigned_process IS NOT NULL THEN
3274 DELETE OKL_PARALLEL_PROCESSES
3275 WHERE assigned_process = p_assigned_process;
3276 COMMIT;
3277 END IF;
3278
3279 IF (L_DEBUG_ENABLED='Y' and FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3280 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'okl_cons_bill',
3281 'EXCEPTION :'||'Okl_Api.G_EXCEPTION_UNEXPECTED_ERROR');
3282 END IF;
3283
3284 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3285 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'*=> Error Message(O2): '||SQLERRM);
3286 END IF;
3287 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
3288 p_api_name => l_api_name,
3289 p_pkg_name => G_PKG_NAME,
3290 p_exc_name => 'Okl_Api.G_RET_STS_UNEXP_ERROR',
3291 x_msg_count => x_msg_count,
3292 x_msg_data => x_msg_data,
3293 p_api_type => '_PVT');
3294
3295 WHEN OTHERS THEN
3296
3297 -- -------------------------------------------
3298 -- Purge data from the Parallel process Table
3299 -- -------------------------------------------
3300 IF p_assigned_process IS NOT NULL THEN
3301 DELETE OKL_PARALLEL_PROCESSES
3302 WHERE assigned_process = p_assigned_process;
3303 COMMIT;
3304 END IF;
3305
3306 IF (L_DEBUG_ENABLED='Y' and FND_LOG.LEVEL_EXCEPTION >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3307 FND_LOG.STRING(FND_LOG.LEVEL_EXCEPTION,'okl_cons_bill',
3308 'EXCEPTION :'||'OTHERS');
3309 END IF;
3310
3311 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3312 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'*=> Error Message(O3): '||SQLERRM);
3313 END IF;
3314 x_return_status := Okl_Api.HANDLE_EXCEPTIONS (
3315 p_api_name => l_api_name,
3316 p_pkg_name => G_PKG_NAME,
3317 p_exc_name => 'OTHERS',
3318 x_msg_count => x_msg_count,
3319 x_msg_data => x_msg_data,
3320 p_api_type => '_PVT');
3321
3322 END create_cons_bill;
3323
3324 END Okl_Cons_Bill;