[Home] [Help]
PACKAGE BODY: APPS.OKL_CURE_RECON_PVT
Source
1 PACKAGE BODY OKL_CURE_RECON_PVT AS
2 /* $Header: OKLRRCOB.pls 120.16 2008/05/12 10:08:39 akrangan noship $ */
3
4 G_MODULE VARCHAR2(255) := 'okl.cure.request.OKL_CURE_RECON_PVT';
5 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
7
8 --private procedure
9
10 FUNCTION get_factor_synd(p_khr_id IN NUMBER) RETURN VARCHAR2 IS
11
12 CURSOR c_synd IS
13 SELECT scs_code
14 FROM okc_k_headers_b
15 WHERE scs_code = 'SYNDICATION'
16 AND id = p_khr_id;
17
18 CURSOR c_fact IS
19 SELECT 1
20 FROM okc_rules_b
21 WHERE dnz_chr_id = p_khr_id
22 AND rule_information_category = 'LAFCTG';
23
24 l_contract_type VARCHAR2(30);
25
26 BEGIN
27
28 OPEN c_synd;
29 FETCH c_synd INTO l_contract_type;
30 CLOSE c_synd;
31
32 IF l_contract_type IS NOT NULL THEN
33 RETURN l_contract_type;
34 END IF;
35
36 OPEN c_fact;
37 FETCH c_fact INTO l_contract_type;
38 CLOSE c_fact;
39
40 IF l_contract_type IS NOT NULL THEN
41 l_contract_type := 'FACTORING';
42 RETURN l_contract_type;
43 END IF;
44
45 RETURN NULL;
46
47 EXCEPTION
48
49 WHEN OTHERS THEN
50 OKL_API.SET_MESSAGE (p_app_name => G_APP_NAME,
51 p_msg_name => G_UNEXPECTED_ERROR,
52 p_token1 => G_SQLCODE_TOKEN,
53 p_token1_value => SQLCODE,
54 p_token2 => G_SQLERRM_TOKEN,
55 p_token2_value => SQLERRM);
56
57 END get_factor_synd;
58
59
60 /**Name AddMissingArgMsg
61 **Appends to a message the api name, parameter name and parameter Value
62 */
63
64 PROCEDURE AddMissingArgMsg
65 ( p_api_name IN VARCHAR2,
66 p_param_name IN VARCHAR2 )IS
67 BEGIN
68 fnd_message.set_name('OKL', 'OKL_API_ALL_MISSING_PARAM');
69 fnd_message.set_token('API_NAME', p_api_name);
70 fnd_message.set_token('MISSING_PARAM', p_param_name);
71 fnd_msg_pub.add;
72
73 END AddMissingArgMsg;
74
75 /**Name AddFailMsg
76 **Appends to a message the name of the object and
77 ** the operation (insert, update ,delete)
78 */
79
80 PROCEDURE AddfailMsg
81 ( p_object IN VARCHAR2,
82 p_operation IN VARCHAR2 ) IS
83
84 BEGIN
85 fnd_message.set_name('OKL', 'OKL_FAILED_OPERATION');
86 fnd_message.set_token('UPDATE', p_operation);
87 fnd_message.set_token('OBJECT', p_object);
88 fnd_message.set_token('OPERATION', p_operation);
89 fnd_msg_pub.add;
90
91 END AddfailMsg;
92
93 PROCEDURE Get_Messages (
94 p_message_count IN NUMBER,
95 x_message OUT NOCOPY VARCHAR2) IS
96
97
98 l_msg_list VARCHAR2(32627) := '';
99 l_temp_msg VARCHAR2(32627);
100 l_appl_short_name VARCHAR2(50) ;
101 l_message_name VARCHAR2(50) ;
102 l_id NUMBER;
103 l_message_num NUMBER;
104 l_msg_count NUMBER;
105 l_msg_data VARCHAR2(32627);
106
107 Cursor Get_Appl_Id (x_short_name VARCHAR2) IS
108 SELECT application_id
109 FROM fnd_application_vl
110 WHERE application_short_name = x_short_name;
111
112 Cursor Get_Message_Num (x_msg VARCHAR2, x_id NUMBER, x_lang_id NUMBER) IS
113 SELECT msg.message_number
114 FROM fnd_new_messages msg, fnd_languages_vl lng
115 WHERE msg.message_name = x_msg
116 and msg.application_id = x_id
117 and lng.LANGUAGE_CODE = msg.language_code
118 and lng.language_id = x_lang_id;
119
120 BEGIN
121 FOR l_count in 1..p_message_count LOOP
122
123 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_true);
124 fnd_message.parse_encoded(l_temp_msg, l_appl_short_name, l_message_name);
125 OPEN Get_Appl_Id (l_appl_short_name);
126 FETCH Get_Appl_Id into l_id;
127 CLOSE Get_Appl_Id;
128
129 l_message_num := NULL;
130 IF l_id is not NULL
131 THEN
132 OPEN Get_Message_Num (l_message_name, l_id,
133 to_number(NVL(FND_PROFILE.Value('LANGUAGE'), '0')));
134 FETCH Get_Message_Num into l_message_num;
135 CLOSE Get_Message_Num;
136 END IF;
137
138 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_previous, fnd_api.g_true);
139
140 IF NVL(l_message_num, 0) <> 0
141 THEN
142 l_temp_msg := 'APP-' || to_char(l_message_num) || ': ';
143 ELSE
144 l_temp_msg := NULL;
145 END IF;
146
147 IF l_count = 1
148 THEN
149 l_msg_list := l_msg_list || l_temp_msg ||
150 fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
151 ELSE
152 l_msg_list := l_msg_list || l_temp_msg ||
153 fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
154 END IF;
155
156 l_msg_list := l_msg_list || '';
157
158 END LOOP;
159
160 x_message := l_msg_list;
161
162
163 END Get_Messages;
164
165 PROCEDURE UPDATE_CRT ( p_report_id IN NUMBER,
166 p_status IN VARCHAR2,
167 x_return_status OUT NOCOPY VARCHAR2,
168 x_msg_count OUT NOCOPY NUMBER,
169 x_msg_data OUT NOCOPY VARCHAR2)
170 IS
171
172 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
173 l_msg_count NUMBER ;
174 l_msg_data VARCHAR2(32627);
175 l_message VARCHAR2(32627);
176 l_api_name CONSTANT VARCHAR2(50) := 'UPDATE_CRT';
177 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'
178 || l_api_name;
179 Cursor c_get_obj_ver (p_report_id IN NUMBER)
180 is
181 select object_version_number
182 from okl_cure_reports
183 where cure_report_id =p_report_id;
184
185 lp_crtv_rec OKL_crt_pvt.crtv_rec_type;
186 xp_crtv_rec OKL_crt_pvt.crtv_rec_type;
187
188 BEGIN
189
190 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_CRT : START ');
191
192 IF (G_DEBUG_ENABLED = 'Y') THEN
193 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
194 END IF;
195
196 SAVEPOINT UPDATE_CRT;
197 IF (G_IS_DEBUG_STATEMENT_ON = true)
198 THEN
199 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'start update_cure_reports');
200 END IF;
201
202 x_return_status := FND_API.G_RET_STS_SUCCESS;
203
204 -- update Cure reports table set error message,so this will be prefixed before
205 -- the actual message, so it makes more sense than displaying an OKL message.
206 AddfailMsg(
207 p_object => 'RECORD IN OKL_CURE_REPORTS ',
208 p_operation => 'UPDATE' );
209
210 lp_crtv_rec.cure_report_id :=p_report_id;
211 lp_crtv_rec.approval_status :=p_status;
212 OPEN c_get_obj_ver(p_report_id);
213 FETCH c_get_obj_ver INTO lp_crtv_rec.object_version_number;
214 CLOSE c_get_obj_ver;
215
216 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_CRT : lp_crtv_rec.cure_report_id : '||lp_crtv_rec.cure_report_id);
217 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_CRT : lp_crtv_rec.approval_status : '||lp_crtv_rec.approval_status);
218
219 okl_cure_reports_pub.update_cure_reports(
220 p_api_version =>1.0
221 ,p_init_msg_list =>FND_API.G_FALSE
222 ,x_return_status =>l_return_status
223 ,x_msg_count =>l_msg_count
224 ,x_msg_data =>l_msg_data
225 ,p_crtv_rec =>lp_crtv_rec
226 ,x_crtv_rec =>xp_crtv_rec);
227
228 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_CRT : okl_cure_reports_pub.update_cure_reports : '||l_return_status);
229
230 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS)
231 THEN
232 Get_Messages (l_msg_count,l_message);
233 IF (G_IS_DEBUG_STATEMENT_ON = true)
234 THEN
235 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error is :' ||l_message);
236 END IF;
237 raise FND_API.G_EXC_ERROR;
238 ELSE
239 IF (G_IS_DEBUG_STATEMENT_ON = true)
240 THEN
241 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Updated cure reports table ');
242 END IF;
243 FND_MSG_PUB.initialize;
244 END IF;
245
246 FND_MSG_PUB.Count_And_Get
247 ( p_count => x_msg_count,
248 p_data => x_msg_data
249 );
250
251 IF (G_IS_DEBUG_STATEMENT_ON = true)
252 THEN
253 OKL_DEBUG_PUB.LOG_DEBUG( FND_LOG.LEVEL_STATEMENT
254 , G_MODULE,
255 ' End of Procedure => OKL_PAY_RECON_PVT.UPDATE_CRT');
256 END IF;
257
258 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_CRT : END ');
259
260 EXCEPTION
261 WHEN Fnd_Api.G_EXC_ERROR THEN
262 ROLLBACK TO UPDATE_CRT;
263 x_return_status := Fnd_Api.G_RET_STS_ERROR;
264 x_msg_count := l_msg_count ;
265 x_msg_data := l_msg_data ;
266 Fnd_Msg_Pub.count_and_get(
267 p_count => x_msg_count
268 ,p_data => x_msg_data);
269 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
270 ROLLBACK TO UPDATE_CRT;
271 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
272 x_msg_count := l_msg_count ;
273 x_msg_data := l_msg_data ;
274 Fnd_Msg_Pub.count_and_get(
275 p_count => x_msg_count
276 ,p_data => x_msg_data);
277 WHEN OTHERS THEN
278 ROLLBACK TO UPDATE_CRT;
279 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
280 x_msg_count := l_msg_count ;
281 x_msg_data := l_msg_data ;
282 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_RECON_PVT','UPDATE_CRT');
283 Fnd_Msg_Pub.count_and_get(
284 p_count => x_msg_count
285 ,p_data => x_msg_data);
286
287 END UPDATE_CRT;
288
289 PROCEDURE UPDATE_CAM ( p_cam_tbl IN cure_amount_tbl,
290 p_report_id IN NUMBER,
291 x_return_status OUT NOCOPY VARCHAR2,
292 x_msg_count OUT NOCOPY NUMBER,
293 x_msg_data OUT NOCOPY VARCHAR2)
294 IS
295
296 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
297 l_msg_count NUMBER ;
298 l_msg_data VARCHAR2(32627);
299 l_message VARCHAR2(32627);
300 l_api_name CONSTANT VARCHAR2(50) := 'UPDATE_CAM';
301 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'
302 || l_api_name;
303 Cursor c_get_obj_ver (p_cam_id IN NUMBER) is
304 select a.object_version_number,a.cure_amount,b.khr_id
305 from okl_cure_amounts a, okl_k_headers b
306 where cure_amount_id =p_cam_id
307 and a.chr_id =b.id;
308
309 lp_camv_rec OKL_cam_pvt.camv_rec_type;
310 xp_camv_rec OKL_cam_pvt.camv_rec_type;
311 l_short_fund_amount okl_cure_amounts.short_fund_amount%TYPE;
312 l_cure_amount okl_cure_amounts.cure_amount%TYPE;
313 l_khr_id okl_k_headers.khr_id%TYPE;
314
315 l_rule_name VARCHAR2(200);
316 l_rule_value VARCHAR2(2000);
317 l_id1 VARCHAR2(40);
318 l_id2 VARCHAR2(200);
319
320 BEGIN
321
322 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_CAM : START ');
323
324 IF (G_DEBUG_ENABLED = 'Y')
325 THEN
326 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
327 END IF;
328
329 SAVEPOINT UPDATE_CAM;
330
331 IF (G_IS_DEBUG_STATEMENT_ON = true)
332 THEN
333 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'start update_cure amounts');
334 END IF;
335 x_return_status := FND_API.G_RET_STS_SUCCESS;
336
337 -- update Cure amounts table set error message,so this will be prefixed before
338 -- the actual message, so it makes more sense than displaying an OKL message.
339 AddfailMsg(
340 p_object => 'RECORD IN OKL_CURE_AMOUNTS ',
341 p_operation => 'UPDATE' );
342
343 FOR i in p_cam_tbl.FIRST..p_cam_tbl.LAST
344 LOOP
345 OPEN c_get_obj_ver(p_cam_tbl(i).cam_id);
346 FETCH c_get_obj_ver
347 INTO lp_camv_rec.object_version_number,
348 l_cure_amount,
349 l_khr_id;
350 CLOSE c_get_obj_ver;
351
352 --calculate short fund amount if rule value is set
353 l_return_status := okl_contract_info.get_rule_value(
354 p_contract_id => l_khr_id
355 ,p_rule_group_code => 'COCURP'
356 ,p_rule_code => 'COCURE'
357 ,p_segment_number => 8
358 ,x_id1 => l_id1
359 ,x_id2 => l_id2
360 ,x_value => l_rule_value);
361
362 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_CAM : okl_contract_info.get_rule_value : '||l_return_status);
363
364 IF l_return_status =FND_Api.G_RET_STS_SUCCESS
365 THEN
366 IF (l_rule_value = 'Yes')
367 THEN
368 IF (G_IS_DEBUG_STATEMENT_ON = true)
369 THEN
370 OKL_DEBUG_PUB.LOG_DEBUG( FND_LOG.LEVEL_STATEMENT
371 , G_MODULE
372 ,' Short fund rule is applicable');
373 END IF;
374 lp_camv_rec.short_fund_amount := nvl(l_cure_amount,0)
375 - nvl(p_cam_tbl(i).negotiated_amount,0);
376 END IF;
377 END IF;
378
379 IF lp_camv_rec.short_fund_amount = OKL_API.G_MISS_NUM
380 THEN
381 lp_camv_rec.short_fund_amount :=NULL;
382 END IF;
383
384 lp_camv_rec.cure_amount_id :=p_cam_tbl(i).cam_id;
385 lp_camv_rec.negotiated_amount :=p_cam_tbl(i).negotiated_amount;
386 lp_camv_rec.crt_id :=p_report_id;
387
388 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_CAM : lp_camv_rec.cure_amount_id : '||lp_camv_rec.cure_amount_id);
389 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_CAM : lp_camv_rec.negotiated_amount : '||lp_camv_rec.negotiated_amount);
390 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_CAM : lp_camv_rec.crt_id : '||lp_camv_rec.crt_id);
391
392 --jsanju 09/24/03
393 -- new column in cure amounts table ,indicating what action has been done.
394 -- possible values are 'CURE','REPURCHASE' & 'DONOTPROCESS'
395
396 --jsanju 11/26/03
397 IF p_cam_tbl(i).process IN ('REPURCHASE', 'DONOTPROCESS') THEN
398 lp_camv_rec.process :=p_cam_tbl(i).process;
399 ELSE
400 lp_camv_rec.process := 'CURE';
401 END IF;
402
403 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_CAM : lp_camv_rec.process : '||lp_camv_rec.process);
404
405 okl_cure_amounts_pub.update_cure_amounts(
406 p_api_version =>1.0
407 ,p_init_msg_list =>FND_API.G_FALSE
408 ,x_return_status =>l_return_status
409 ,x_msg_count =>l_msg_count
410 ,x_msg_data =>l_msg_data
411 ,p_camv_rec =>lp_camv_rec
412 ,x_camv_rec =>xp_camv_rec);
413
414 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_CAM : okl_cure_amounts_pub.update_cure_amounts : '||l_return_status);
415
416 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS)
417 THEN
418 Get_Messages (l_msg_count,l_message);
419 IF (G_IS_DEBUG_STATEMENT_ON = true)
420 THEN
421 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error is :' ||l_message);
422 END IF;
423 raise FND_API.G_EXC_ERROR;
424 ELSE
425 IF (G_IS_DEBUG_STATEMENT_ON = true)
426 THEN
427 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Updated cure amounts table ');
428 END IF;
429 END IF;
430
431 END LOOP;
432 FND_MSG_PUB.initialize;
433
434 FND_MSG_PUB.Count_And_Get
435 ( p_count => x_msg_count,
436 p_data => x_msg_data
437 );
438
439 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
440 OKL_DEBUG_PUB.LOG_DEBUG( FND_LOG.LEVEL_STATEMENT
441 , G_MODULE
442 ,' End of Procedure => OKL_PAY_RECON_PVT.UPDATE_CAM');
443
444 END IF;
445
446 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_CAM : END ');
447
448 EXCEPTION
449 WHEN Fnd_Api.G_EXC_ERROR THEN
450 ROLLBACK TO UPDATE_CAM;
451 x_return_status := Fnd_Api.G_RET_STS_ERROR;
452 x_msg_count := l_msg_count ;
453 x_msg_data := l_msg_data ;
454 Fnd_Msg_Pub.count_and_get(
455 p_count => x_msg_count
456 ,p_data => x_msg_data);
457 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
458 ROLLBACK TO UPDATE_CAM;
459 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
460 x_msg_count := l_msg_count ;
461 x_msg_data := l_msg_data ;
462 Fnd_Msg_Pub.count_and_get(
463 p_count => x_msg_count
464 ,p_data => x_msg_data);
465 WHEN OTHERS THEN
466 ROLLBACK TO UPDATE_CAM;
467 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
468 x_msg_count := l_msg_count ;
469 x_msg_data := l_msg_data ;
470 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_RECON_PVT','UPDATE_CAM');
471 Fnd_Msg_Pub.count_and_get(
472 p_count => x_msg_count
473 ,p_data => x_msg_data);
474
475 END UPDATE_CAM;
476
477 PROCEDURE UPDATE_INVOICE_HDR_LINES
478 (p_negotiated_amount IN NUMBER,
479 p_tai_id IN NUMBER,
480 p_trx_status IN VARCHAR2,
481 p_invoice_date IN DATE,
482 x_return_status OUT NOCOPY VARCHAR2,
483 x_msg_count OUT NOCOPY NUMBER,
484 x_msg_data OUT NOCOPY VARCHAR2) IS
485
486 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
487 l_msg_count NUMBER ;
488 l_msg_data VARCHAR2(32627);
489 l_message VARCHAR2(32627);
490 l_api_name CONSTANT VARCHAR2(50) := 'UPDATE_INVOICE_HDR_LINES';
491 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'
492 || l_api_name;
493
494 CURSOR c_get_obj_ver ( p_tai_id IN NUMBER)
495 IS
496 SELECT object_version_number
497 FROM okl_trx_ar_invoices_b
498 WHERE id = p_tai_id;
499
500 CURSOR c_get_til_obj_ver ( p_tai_id IN NUMBER)
501 IS
502 SELECT object_version_number,id
503 FROM okl_txl_ar_inv_lns_b
504 WHERE tai_id = p_tai_id;
505
506 CURSOR c_get_txd_obj_ver ( p_til_id_details IN NUMBER)
507 IS
508 SELECT object_version_number,id
509 FROM OKL_TXD_AR_LN_DTLS_b
510 WHERE til_id_details = p_til_id_details;
511
512 lp_taiv_rec okl_tai_pvt.taiv_rec_type;
513 xp_taiv_rec okl_tai_pvt.taiv_rec_type;
514 lp_tilv_rec okl_til_pvt.tilv_rec_type;
515 xp_tilv_rec okl_til_pvt.tilv_rec_type;
516 lp_tldv_rec okl_tld_pvt.tldv_rec_type;
517 xp_tldv_rec okl_tld_pvt.tldv_rec_type;
518
519 BEGIN
520
521 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_INVOICE_HDR_LINES : START ');
522 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_INVOICE_HDR_LINES : p_tai_id : '||p_tai_id);
523 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_INVOICE_HDR_LINES : p_trx_status : '||p_trx_status);
524 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_INVOICE_HDR_LINES : p_invoice_date : '||p_invoice_date);
525 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_INVOICE_HDR_LINES : p_negotiated_amount : '||p_negotiated_amount);
526
527 SAVEPOINT UPDATE_INVOICE_HDR_LINES;
528
529 IF (G_IS_DEBUG_STATEMENT_ON = true)
530 THEN
531 OKL_DEBUG_PUB.LOG_DEBUG( FND_LOG.LEVEL_STATEMENT
532 , G_MODULE
533 , 'start UPDATE_INVOICE_HDR_LINES');
534 END IF;
535 x_return_status := FND_API.G_RET_STS_SUCCESS;
536
537 -- update okl_trx__ar_invoices_b set error message,so this will be prefixed before
538 -- the actual message, so it makes more sense than displaying an OKL message.
539 AddfailMsg(
540 p_object => 'RECORD IN OKL_TRX_AR_INVOICES ',
541 p_operation => 'UPDATE' );
542
543
544 OPEN c_get_obj_ver(p_tai_id);
545 FETCH c_get_obj_ver INTO lp_taiv_rec.object_version_number;
546 CLOSE c_get_obj_ver;
547
548 lp_taiv_rec.id := p_tai_id;
549 lp_taiv_rec.date_entered := SYSDATE;
550 lp_taiv_rec.date_invoiced := p_invoice_date;
551 lp_taiv_rec.amount := p_negotiated_amount;
552 lp_taiv_rec.trx_status_code := p_trx_status;
553
554 -- Following is new as per Ashim's instructions
555 lp_taiv_rec.okl_source_billing_trx := 'CURE';
556 lp_taiv_rec.set_of_books_id := okl_accounting_util.get_set_of_books_id;
557 lp_taiv_rec.tax_exempt_flag := 'S';
558 lp_taiv_rec.tax_exempt_reason_code := NULL;
559
560
561 okl_trx_ar_invoices_pub.update_trx_ar_invoices
562 (p_api_version => 1.0,
563 p_init_msg_list => 'F',
564 x_return_status => l_return_status,
565 x_msg_count => l_msg_count,
566 x_msg_data => l_msg_data,
567 p_taiv_rec => lp_taiv_rec,
568 x_taiv_rec => xp_taiv_rec);
569
570 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_INVOICE_HDR_LINES : okl_trx_ar_invoices_pub.update_trx_ar_invoices : '||l_return_status);
571
572 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS)
573 THEN
574 Get_Messages (l_msg_count,l_message);
575 IF (G_IS_DEBUG_STATEMENT_ON = true)
576 THEN
577 OKL_DEBUG_PUB.LOG_DEBUG( FND_LOG.LEVEL_STATEMENT
578 , G_MODULE
579 ,'Error in updating okl_trx_ar_invoices_b '||l_message);
580 END IF;
581 raise FND_API.G_EXC_ERROR;
582 ELSE
583 FND_MSG_PUB.initialize;
584 -- update okl_txl_ar_inv_lns set error message,so this will be prefixed before
585 -- the actual message, so it makes more sense than displaying an OKL message.
586
587 AddfailMsg(
588 p_object => 'RECORD IN OKL_TXL_AR_INV_LNS ',
589 p_operation => 'UPDATE' );
590
591 OPEN c_get_til_obj_ver(p_tai_id);
592 FETCH c_get_til_obj_ver
593 INTO lp_tilv_rec.object_version_number,
594 lp_tilv_rec.id;
595 CLOSE c_get_til_obj_ver;
596
597 lp_tilv_rec.amount :=p_negotiated_amount;
598
599 okl_txl_ar_inv_lns_pub.update_txl_ar_inv_lns
600 (p_api_version => 1.0,
601 p_init_msg_list => 'F',
602 x_return_status => l_return_status,
603 x_msg_count => l_msg_count,
604 x_msg_data => l_msg_data,
605 p_tilv_rec => lp_tilv_rec,
606 x_tilv_rec => xp_tilv_rec);
607
608 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_INVOICE_HDR_LINES : okl_txl_ar_inv_lns_pub.update_txl_ar_inv_lns : '||l_return_status);
609
610 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS)
611 THEN
612 Get_Messages (l_msg_count,l_message);
613 IF (G_IS_DEBUG_STATEMENT_ON = true)
614 THEN
615 OKL_DEBUG_PUB.LOG_DEBUG( FND_LOG.LEVEL_STATEMENT
616 , G_MODULE
617 ,'Error in updating okl_txl_ar_inv_lns '||l_message);
618 END IF;
619 raise FND_API.G_EXC_ERROR;
620 ELSE
621 FND_MSG_PUB.initialize;
622 -- R12 CHANGE- START
623 AddfailMsg(
624 p_object => 'RECORD IN OKL_TXL_AR_LN_DTLS ',
625 p_operation => 'UPDATE' );
626
627 OPEN c_get_txd_obj_ver(lp_tilv_rec.id);
628 FETCH c_get_txd_obj_ver
629 INTO lp_tldv_rec.object_version_number,
630 lp_tldv_rec.id;
631 CLOSE c_get_txd_obj_ver;
632
633 lp_tldv_rec.amount :=p_negotiated_amount;
634
635 okl_tld_pvt.update_row(
636 p_api_version => 1.0,
637 p_init_msg_list => 'F',
638 x_return_status => l_return_status,
639 x_msg_count => x_msg_count,
640 x_msg_data => x_msg_data,
641 p_tldv_rec => lp_tldv_rec,
642 x_tldv_rec => xp_tldv_rec);
643
644 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_INVOICE_HDR_LINES : okl_tld_pvt.update_row : '||l_return_status);
645
646 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS)
647 THEN
648 IF (l_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR)
649 THEN
650 x_return_status := l_return_status;
651 END IF;
652 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
653 ELSE
654 FND_MSG_PUB.initialize;
655 END IF; -- for okl_tld_pvt
656
657 -- R12 CHANGE- END
658
659 END IF; -- for okl_txl_ar_inv_lns
660
661 END IF; -- for okl_trx_ar_invoices
662
663 FND_MSG_PUB.Count_And_Get
664 ( p_count => x_msg_count,
665 p_data => x_msg_data
666 );
667
668 IF (G_IS_DEBUG_STATEMENT_ON = true)
669 THEN
670 OKL_DEBUG_PUB.LOG_DEBUG( FND_LOG.LEVEL_STATEMENT
671 , G_MODULE
672 ,' End of Procedure => OKL_PAY_RECON_PVT.UPDATE_INVOICE_HDR_LINES');
673 END IF;
674
675 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_INVOICE_HDR_LINES : END ');
676
677 EXCEPTION
678 WHEN Fnd_Api.G_EXC_ERROR
679 THEN
680 ROLLBACK TO UPDATE_INVOICE_HDR_LINES;
681 x_return_status := Fnd_Api.G_RET_STS_ERROR;
682 x_msg_count := l_msg_count ;
683 x_msg_data := l_msg_data ;
684 Fnd_Msg_Pub.count_and_get(
685 p_count => x_msg_count
686 ,p_data => x_msg_data);
687 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR
688 THEN
689 ROLLBACK TO UPDATE_INVOICE_HDR_LINES;
690 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
691 x_msg_count := l_msg_count ;
692 x_msg_data := l_msg_data ;
693 Fnd_Msg_Pub.count_and_get(
694 p_count => x_msg_count
695 ,p_data => x_msg_data);
696 WHEN OTHERS
697 THEN
698 ROLLBACK TO UPDATE_INVOICE_HDR_LINES;
699 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
700 x_msg_count := l_msg_count ;
701 x_msg_data := l_msg_data ;
702 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_RECON_PVT','UPDATE_INVOICE_HDR_LINES');
703 Fnd_Msg_Pub.count_and_get(
704 p_count => x_msg_count
705 ,p_data => x_msg_data);
706
707 END update_invoice_hdr_lines;
708
709
710 ----------------------------------------------------------------------------------
711 -- Start of comments
712 --
713 -- Procedure Name : additional_tld_attr
714 -- Description : Internal procedure to add additional columns for
715 -- OKL_TXD_AR_LN_DTLS_B
716 -- Important Note : This procedure taken from BPD package on Ashim's advise
717 -- Business Rules :
718 -- Parameters :
719 --
720 -- Version : 1.0
721 -- End of comments
722 ----------------------------------------------------------------------------------
723 PROCEDURE additional_tld_attr(
724 p_api_version IN NUMBER
725 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
726 ,x_return_status OUT NOCOPY VARCHAR2
727 ,x_msg_count OUT NOCOPY NUMBER
728 ,x_msg_data OUT NOCOPY VARCHAR2
729 ,p_tldv_rec IN okl_tld_pvt.tldv_rec_type
730 ,x_tldv_rec OUT NOCOPY okl_tld_pvt.tldv_rec_type )
731 IS
732 l_api_name CONSTANT VARCHAR2(30) := 'additional_tld_attr';
733 l_api_version CONSTANT NUMBER := 1.0;
734 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
735
736 -- Get currency attributes
737 CURSOR l_curr_csr(p_khr_id number) IS
738 SELECT c.minimum_accountable_unit,
739 c.PRECISION
740 FROM fnd_currencies c,
741 okl_trx_ar_invoices_b b
742 WHERE c.currency_code = b.currency_code
743 AND b.khr_id = p_khr_id;
744
745 l_min_acct_unit fnd_currencies.minimum_accountable_unit%TYPE;
746 l_precision fnd_currencies.PRECISION %TYPE;
747 l_rounded_amount OKL_TXD_AR_LN_DTLS_B.amount%TYPE;
748
749 -- to get inventory_org_id bug 4890024 begin
750 CURSOR inv_org_id_csr(p_contract_id NUMBER) IS
751 SELECT NVL(inv_organization_id, -99)
752 FROM okc_k_headers_b
753 WHERE id = p_contract_id;
754
755 BEGIN
756 -- Set API savepoint
757 SAVEPOINT additional_tld_attr;
758 IF (G_DEBUG_ENABLED = 'Y') THEN
759 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
760 END IF;
761
762 --Print Input Variables
763 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
764 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_tldv_rec.id :'||p_tldv_rec.id);
765 END IF;
766 -- Check for call compatibility
767 IF (NOT FND_API.Compatible_API_Call (l_api_version,
768 p_api_version,
769 l_api_name,
770 G_PKG_NAME ))
771 THEN
772 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
773 END IF;
774
775 -- Initialize message list if requested
776 IF (FND_API.to_Boolean(p_init_msg_list))
777 THEN
778 FND_MSG_PUB.initialize;
779 END IF;
780
781 -- Initialize API status to success
782 x_return_status := OKL_API.G_RET_STS_SUCCESS;
783
784
785 /*** Begin API body ****************************************************/
786 -- assign all passed in attributes from IN to OUT record
787 x_tldv_rec := p_tldv_rec;
788 /* For R12, okl_arfetch_pub is absolete, so the following logic won't work
789 since the receivable_invoice_id is null
790 --For Credit Memo Processing
791 IF p_tldv_rec.tld_id_reverses IS NOT NULL THEN
792 -- Null out variables
793 l_recv_inv_id := NULL;
794
795 OPEN reverse_csr1(p_tldv_rec.tld_id_reverses);
796 FETCH reverse_csr1
797 INTO l_recv_inv_id;
798 CLOSE reverse_csr1;
799 x_tldv_rec.reference_line_id := l_recv_inv_id;
800 ELSE
801 x_tldv_rec.reference_line_id := NULL;
802 END IF;
803
804 x_tldv_rec.receivables_invoice_id := NULL;
805 -- Populated later by fetch
806 */
807
808 IF(p_tldv_rec.inventory_org_id IS NULL)
809 THEN
810 OPEN inv_org_id_csr(p_tldv_rec.khr_id);
811 FETCH inv_org_id_csr
812 INTO x_tldv_rec.inventory_org_id;
813 CLOSE inv_org_id_csr;
814 ELSE
815 x_tldv_rec.inventory_org_id := p_tldv_rec.inventory_org_id;
816 END IF;
817
818 -- Bug 4890024 end
819 -------- Rounded Amount --------------
820 l_rounded_amount := NULL;
821 l_min_acct_unit := NULL;
822 l_precision := NULL;
823
824 OPEN l_curr_csr(p_tldv_rec.khr_id);
825 FETCH l_curr_csr
826 INTO l_min_acct_unit,
827 l_precision;
828 CLOSE l_curr_csr;
829
830 IF(NVL(l_min_acct_unit, 0) <> 0)
831 THEN
832 -- Round the amount to the nearest Min Accountable Unit
833 l_rounded_amount := ROUND(p_tldv_rec.amount / l_min_acct_unit) * l_min_acct_unit;
834 ELSE
835 -- Round the amount to the nearest precision
836 l_rounded_amount := ROUND(p_tldv_rec.amount, l_precision);
837 END IF;
838
839 -------- Rounded Amount --------------
840 x_tldv_rec.amount := l_rounded_amount;
841 --TIL
842 /*** End API body ******************************************************/
843
844 -- Get message count and if count is 1, get message info
845 FND_MSG_PUB.Count_And_Get
846 (p_count => x_msg_count,
847 p_data => x_msg_data);
848
849 EXCEPTION
850 WHEN OKL_API.G_EXCEPTION_ERROR THEN
851 ROLLBACK TO additional_tld_attr;
852 x_return_status := OKL_API.G_RET_STS_ERROR;
853 FND_MSG_PUB.Count_And_Get
854 (p_count => x_msg_count,
855 p_data => x_msg_data);
856
857 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
858 ROLLBACK TO additional_tld_attr;
859 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
860 FND_MSG_PUB.Count_And_Get
861 (p_count => x_msg_count,
862 p_data => x_msg_data);
863
864 WHEN OTHERS THEN
865 ROLLBACK TO additional_tld_attr;
866 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
867 OKL_API.Set_Message(p_app_name => G_APP_NAME,
868 p_msg_name => G_UNEXPECTED_ERROR,
869 p_token1 => G_SQLCODE_TOKEN,
870 p_token1_value => SQLCODE,
871 p_token2 => G_SQLERRM_TOKEN,
872 p_token2_value => SQLERRM);
873 FND_MSG_PUB.Count_And_Get
874 (p_count => x_msg_count,
875 p_data => x_msg_data);
876
877 end additional_tld_attr;
878
879 PROCEDURE INSERT_INVOICE_HDR_LINES
880 (p_negotiated_amount IN NUMBER,
881 p_cam_id IN NUMBER,
882 p_trx_status IN VARCHAR2,
883 p_invoice_date IN DATE,
884 x_return_status OUT NOCOPY VARCHAR2,
885 x_msg_count OUT NOCOPY NUMBER,
886 x_msg_data OUT NOCOPY VARCHAR2) IS
887
888 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
889 l_msg_count NUMBER ;
890 l_msg_data VARCHAR2(32627);
891 l_message VARCHAR2(32627);
892 l_api_name CONSTANT VARCHAR2(50) := 'INSERT_INVOICE_HDR_LINES';
893 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'
894 || l_api_name;
895
896 lp_taiv_rec okl_tai_pvt.taiv_rec_type;
897 xp_taiv_rec okl_tai_pvt.taiv_rec_type;
898 lp_tilv_rec okl_til_pvt.tilv_rec_type;
899 xp_tilv_rec okl_til_pvt.tilv_rec_type;
900 lp_tldv_rec okl_tld_pvt.tldv_rec_type;
901 xp_tldv_rec okl_tld_pvt.tldv_rec_type;
902
903 -- R12 Change - START
904 -- added contract header table to extract additional parameters to
905 -- populate invoice headers table. Otherwise it is not transferring to AR.
906 -- vdamerla Fix issue where the cure streams are not being processed by
907 -- BPD Billing programs
908 -- vdamerla added additional column chr.authoring_org_id
909
910 CURSOR c_get_khr_id ( p_cam_id IN NUMBER )
911 IS
912 SELECT cam.chr_id chr_id
913 ,chr.currency_code currency_code
914 ,chr.conversion_type conversion_type
915 ,chr.conversion_rate conversion_rate
916 ,chr.conversion_rate_date conversion_rate_date
917 ,chr.authoring_org_id org_id
918 FROM okl_cure_amounts cam
919 ,okc_k_headers_b chr
920 WHERE chr.id = cam.chr_id
921 AND cam.cure_amount_id = p_cam_id;
922
923 -- vdamerla Fix issue where the cure streams are not being processed by
924 -- BPD Billing programs
925 -- vdamerla added cursor to get the cust_trx_type_id
926 CURSOR ra_cust_csr
927 IS
928 SELECT cust_trx_type_id l_cust_trx_type_id
929 FROM ra_cust_trx_types
930 WHERE name = 'Invoice-OKL';
931
932 -- R12 Change - END
933
934 CURSOR get_trx_id IS
935 SELECT id FROM okl_trx_types_tl
936 WHERE name = 'Billing' AND language = 'US';
937
938 l_khr_id NUMBER;
939 x_primary_sty_id number;
940
941 CURSOR l_rcpt_mthd_csr (cp_cust_rct_mthd IN NUMBER) IS
942 SELECT c.receipt_method_id
943 FROM ra_cust_receipt_methods c
944 WHERE c.cust_receipt_method_id = cp_cust_rct_mthd;
945
946 CURSOR l_site_use_csr (
947 cp_site_use_id IN NUMBER,
948 cp_site_use_code IN VARCHAR2) IS
949 SELECT a.cust_account_id cust_account_id,
950 a.cust_acct_site_id cust_acct_site_id,
951 a.payment_term_id payment_term_id
952 FROM okx_cust_site_uses_v a,
953 okx_customer_accounts_v c
954 WHERE a.id1 = cp_site_use_id
955 AND a.site_use_code = cp_site_use_code
956 AND c.id1 = a.cust_account_id;
957
958 l_site_use_rec l_site_use_csr%ROWTYPE;
959
960 CURSOR l_std_terms_csr (
961 cp_cust_id IN NUMBER,
962 cp_site_use_id IN NUMBER) IS
963 SELECT c.standard_terms standard_terms
964 FROM hz_customer_profiles c
965 WHERE c.cust_account_id = cp_cust_id
966 AND c.site_use_id = cp_site_use_id
967 UNION
968 SELECT c1.standard_terms standard_terms
969 FROM hz_customer_profiles c1
970 WHERE c1.cust_account_id = cp_cust_id
971 AND c1.site_use_id IS NULL
972 AND NOT EXISTS (
973 SELECT '1'
974 FROM hz_customer_profiles c2
975 WHERE c2.cust_account_id = cp_cust_id
976 AND c2.site_use_id = cp_site_use_id);
977
978
979
980 -- Code segment for Customer Account/bill to address
981 -- as mentioned in OKC Rules Migration HLD
982 -- Start
983
984 CURSOR bill_to_csr (p_program_id IN NUMBER) IS
985 select BILL_TO_SITE_USE_ID
986 from okc_k_party_roles_v
987 where dnz_chr_id = p_program_id
988 and RLE_CODE ='OKL_VENDOR';
989
990 -- Code segment for Customer Account/bill to address
991 -- as mentioned in OKC Rules Migration HLD
992 -- End
993
994
995 cursor c_program_id (p_contract_id IN NUMBER ) IS
996 select khr_id from okl_k_headers where id= p_contract_id;
997
998
999 l_program_id okl_k_headers.khr_id%TYPE;
1000
1001 l_id1 VARCHAR2(40) :=NULL;
1002 l_id2 VARCHAR2(200) :=NULL;
1003 l_rule_value VARCHAR2(2000):=NULL;
1004
1005
1006 l_btc_id NUMBER;
1007 l_bill_to_address_id NUMBER;
1008
1009 BEGIN
1010
1011 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : START ');
1012 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : p_cam_id : '||p_cam_id);
1013 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : p_trx_status : '||p_trx_status);
1014 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : p_invoice_date : '||p_invoice_date);
1015 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : p_negotiated_amount : '||p_negotiated_amount);
1016
1017 SAVEPOINT INSERT_INVOICE_HDR_LINES;
1018 IF (G_IS_DEBUG_STATEMENT_ON = true)
1019 THEN
1020 OKL_DEBUG_PUB.LOG_DEBUG( FND_LOG.LEVEL_STATEMENT
1021 , G_MODULE
1022 , 'start INSERT_INVOICE_HDR_LINES');
1023 END IF;
1024 x_return_status := FND_API.G_RET_STS_SUCCESS;
1025
1026 --INSERT okl_trx_ar_invoices_b set error message,so this will be prefixed before
1027 --the actual message, so it makes more sense than displaying an OKL message.
1028 AddfailMsg(
1029 p_object => 'RECORD IN OKL_TRX_AR_INVOICES ',
1030 p_operation => 'INSERT' );
1031 OPEN c_get_khr_id(p_cam_id);
1032
1033 -- vdamerla Fix issue where the cure streams are not being processed by
1034 -- BPD Billing programs
1035 -- vdamerla modifed the FETCH to store the org_id
1036 FETCH c_get_khr_id INTO lp_taiv_rec.khr_id
1037 ,lp_taiv_rec.currency_code
1038 ,lp_taiv_rec.currency_conversion_type
1039 ,lp_taiv_rec.currency_conversion_rate
1040 ,lp_taiv_rec.currency_conversion_date
1041 ,lp_taiv_rec.org_id;
1042 CLOSE c_get_khr_id;
1043
1044 -- vdamerla Fix issue where the cure streams are not being processed by
1045 -- BPD Billing programs
1046 -- vdamerla begin: added the code to get the currency conversion details
1047
1048 --Check for currency code
1049
1050 IF(lp_taiv_rec.currency_conversion_type = 'User') THEN
1051
1052 IF(lp_taiv_rec.currency_code = Okl_Accounting_Util.get_func_curr_code) THEN
1053 lp_taiv_rec.currency_conversion_rate := 1;
1054 END IF;
1055
1056 ELSE
1057 lp_taiv_rec.currency_conversion_rate := NULL;
1058 END IF;
1059
1060
1061 IF(lp_taiv_rec.currency_conversion_type IS NULL OR lp_taiv_rec.currency_conversion_date = OKL_API.G_MISS_DATE) THEN
1062 lp_taiv_rec.currency_conversion_type := 'User';
1063 lp_taiv_rec.currency_conversion_rate := 1;
1064 lp_taiv_rec.currency_conversion_date := SYSDATE;
1065 END IF;
1066
1067 -- vdamerla end: added the code to get the currency conversion details
1068
1069 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : lp_taiv_rec.khr_id : '||lp_taiv_rec.khr_id);
1070 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : lp_taiv_rec.currency_code : '||lp_taiv_rec.currency_code);
1071 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : lp_taiv_rec.currency_conversion_type : '||lp_taiv_rec.currency_conversion_type);
1072 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : lp_taiv_rec.currency_conversion_rate : '||lp_taiv_rec.currency_conversion_rate);
1073 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : lp_taiv_rec.currency_conversion_date : '||lp_taiv_rec.currency_conversion_date);
1074 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : lp_taiv_rec.org_id : '||lp_taiv_rec.org_id);
1075
1076 IF (G_IS_DEBUG_STATEMENT_ON = true)
1077 THEN
1078 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'khrid '||lp_taiv_rec.khr_id);
1079 END IF;
1080
1081 IF lp_taiv_rec.khr_id IS NULL
1082 THEN
1083 OKL_API.SET_MESSAGE (p_app_name => 'OKL',
1084 p_msg_name => G_REQUIRED_VALUE,
1085 p_token1 => 'COL_NAME',
1086 p_token1_value => 'Contract Id');
1087 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1088 END IF;
1089
1090 OPEN get_trx_id;
1091 FETCH get_trx_id INTO lp_taiv_rec.try_id;
1092 CLOSE get_trx_id;
1093
1094 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : lp_taiv_rec.try_id : '||lp_taiv_rec.try_id);
1095
1096 IF (G_IS_DEBUG_STATEMENT_ON = true)
1097 THEN
1098 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'trxid '||lp_taiv_rec.try_id);
1099 END IF;
1100
1101 IF lp_taiv_rec.try_id IS NULL
1102 THEN
1103 OKL_API.SET_MESSAGE (p_app_name => 'OKL',
1104 p_msg_name => G_REQUIRED_VALUE,
1105 p_token1 => 'COL_NAME',
1106 p_token1_value => 'Transaction Type');
1107 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1108 END IF;
1109
1110 l_khr_id := lp_taiv_rec.khr_id;
1111
1112 OKL_STREAMS_UTIL.get_primary_stream_type(
1113 p_khr_id => l_khr_id,
1114 p_primary_sty_purpose => 'CURE',
1115 x_return_status => x_return_status,
1116 x_primary_sty_id => x_primary_sty_id);
1117
1118 lp_tilv_rec.sty_id := x_primary_sty_id;
1119
1120 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : lp_tilv_rec.sty_id : '||lp_tilv_rec.sty_id);
1121
1122 IF (G_IS_DEBUG_STATEMENT_ON = true)
1123 THEN
1124 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'sty_id '||lp_tilv_rec.sty_id);
1125 END IF;
1126
1127 IF lp_tilv_rec.sty_id IS NULL
1128 THEN
1129 OKL_API.SET_MESSAGE (p_app_name => 'OKL',
1130 p_msg_name => G_REQUIRED_VALUE,
1131 p_token1 => 'COL_NAME',
1132 p_token1_value => 'Sty Id');
1133 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1134 END IF;
1135
1136 -- need to populate 4 fields. so that cure invoice gets generated for vendor
1137 -- and not for the customer ibt_id,ixx_id,irm_id,irt_id get cust_account from
1138 -- rule vendor billing set up
1139
1140 OPEN c_program_id(lp_taiv_rec.khr_id);
1141 FETCH c_program_id INTO l_program_id;
1142 CLOSE c_program_id;
1143
1144 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : l_program_id : '||l_program_id);
1145
1146 IF l_program_id IS NULL
1147 THEN
1148 OKL_API.SET_MESSAGE (p_app_name => 'OKL',
1149 p_msg_name => G_REQUIRED_VALUE,
1150 p_token1 => 'COL_NAME',
1151 p_token1_value => 'Vendor Program');
1152 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1153 END IF;
1154
1155 IF (G_IS_DEBUG_STATEMENT_ON = true)
1156 THEN
1157 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'program Id' ||l_program_id);
1158 END IF;
1159
1160 -- New code for bill to address START
1161 OPEN bill_to_csr (l_program_id);
1162 FETCH bill_to_csr INTO l_bill_to_address_id;
1163 CLOSE bill_to_csr;
1164
1165 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : l_bill_to_address_id : '||l_bill_to_address_id);
1166
1167 IF trunc(l_bill_to_address_id) IS NULL
1168 THEN
1169 IF (G_IS_DEBUG_STATEMENT_ON = true)
1170 THEN
1171 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Retrieval of Bill To Address Id failed');
1172 END IF;
1173 OKL_API.SET_MESSAGE (p_app_name => 'OKL',
1174 p_msg_name => 'OKL_REQUIRED_VALUE',
1175 p_token1 => 'COL_NAME',
1176 p_token1_value => 'Bill To Address Id');
1177 RAISE OKL_API.G_EXCEPTION_ERROR;
1178 END IF;
1179
1180 l_btc_id :=l_bill_to_address_id;
1181
1182 IF (G_IS_DEBUG_STATEMENT_ON = true)
1183 THEN
1184 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Bill to address id from rule is ' || l_btc_id);
1185 END IF;
1186
1187 -- *****************************************************
1188 -- Extract Customer, Bill To and Payment Term from rules
1189 -- *****************************************************
1190
1191 OPEN l_site_use_csr (l_btc_id, 'BILL_TO');
1192 FETCH l_site_use_csr INTO l_site_use_rec;
1193 CLOSE l_site_use_csr;
1194
1195 lp_taiv_rec.ibt_id := l_site_use_rec.cust_acct_site_id;
1196 lp_taiv_rec.ixx_id := l_site_use_rec.cust_account_id;
1197 lp_taiv_rec.irt_id := l_site_use_rec.payment_term_id;
1198
1199 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : lp_taiv_rec.ibt_id : '||lp_taiv_rec.ibt_id);
1200 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : lp_taiv_rec.ixx_id : '||lp_taiv_rec.ixx_id);
1201 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : lp_taiv_rec.irt_id : '||lp_taiv_rec.irt_id);
1202
1203 IF lp_taiv_rec.irt_id IS NULL OR lp_taiv_rec.irt_id = FND_API.G_MISS_NUM
1204 THEN
1205 IF (G_IS_DEBUG_STATEMENT_ON = true)
1206 THEN
1207 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'value of irt' ||lp_taiv_rec.irt_id);
1208 END IF;
1209 OPEN l_std_terms_csr ( l_site_use_rec.cust_account_id,
1210 l_btc_id);
1211 FETCH l_std_terms_csr
1212 INTO lp_taiv_rec.irt_id;
1213 CLOSE l_std_terms_csr;
1214 END IF;
1215
1216 IF lp_taiv_rec.ixx_id IS NULL OR lp_taiv_rec.ixx_id = FND_API.G_MISS_NUM
1217 THEN
1218 OKL_API.SET_MESSAGE (p_app_name => 'OKL',
1219 p_msg_name => G_REQUIRED_VALUE,
1220 p_token1 => 'COL_NAME',
1221 p_token1_value => 'Customer Account Id');
1222 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1223 END IF;
1224
1225 IF lp_taiv_rec.ibt_id IS NULL OR lp_taiv_rec.ibt_id = FND_API.G_MISS_NUM
1226 THEN
1227 OKL_API.SET_MESSAGE (p_app_name => 'OKL',
1228 p_msg_name => G_REQUIRED_VALUE,
1229 p_token1 => 'COL_NAME',
1230 p_token1_value => 'Bill To Address Id');
1231 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1232 END IF;
1233
1234 IF (G_IS_DEBUG_STATEMENT_ON = true)
1235 THEN
1236 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'value of irt--->' ||lp_taiv_rec.irt_id);
1237 END IF;
1238
1239 IF lp_taiv_rec.irt_id IS NULL OR lp_taiv_rec.irt_id = FND_API.G_MISS_NUM
1240 THEN
1241 OKL_API.SET_MESSAGE (p_app_name => 'OKL',
1242 p_msg_name => G_REQUIRED_VALUE,
1243 p_token1 => 'COL_NAME',
1244 p_token1_value => 'Payment Term Id');
1245 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1246 END IF;
1247
1248 l_rule_value := NULL;
1249 l_id1 := NULL;
1250 l_id2 := NULL;
1251
1252 l_return_status := okl_contract_info.get_rule_value(
1253 p_contract_id => l_program_id
1254 ,p_rule_group_code => 'LAVENB'
1255 ,p_rule_code => 'LAPMTH'
1256 ,p_segment_number => 16
1257 ,x_id1 => l_id1
1258 ,x_id2 => l_id2
1259 ,x_value => l_rule_value);
1260
1261 IF l_return_status =FND_Api.G_RET_STS_SUCCESS AND l_id1 IS NOT NULL
1262 THEN
1263 lp_taiv_rec.irm_id :=l_id1;
1264 IF (G_IS_DEBUG_STATEMENT_ON = true)
1265 THEN
1266 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Payment method from rule is ' || l_id1);
1267 END IF;
1268 ELSE
1269 IF (G_IS_DEBUG_STATEMENT_ON = true)
1270 THEN
1271 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Retrieval of Payment Method Id failed');
1272 END IF;
1273 OKL_API.SET_MESSAGE (p_app_name => 'OKL',
1274 p_msg_name => 'OKL_REQUIRED_VALUE',
1275 p_token1 => 'COL_NAME',
1276 p_token1_value => 'Payment Method ');
1277 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1278 END IF;
1279
1280 OPEN l_rcpt_mthd_csr (l_id1);
1281 FETCH l_rcpt_mthd_csr INTO lp_taiv_rec.irm_id;
1282 CLOSE l_rcpt_mthd_csr;
1283
1284 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : lp_taiv_rec.irm_id : '||lp_taiv_rec.irm_id);
1285
1286 IF lp_taiv_rec.irm_id IS NULL OR lp_taiv_rec.irm_id = FND_API.G_MISS_NUM
1287 THEN
1288 IF (G_IS_DEBUG_STATEMENT_ON = true)
1289 THEN
1290 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'receipt method id is not found');
1291 END IF;
1292 OKL_API.SET_MESSAGE (p_app_name => 'OKL',
1293 p_msg_name => 'OKL_REQUIRED_VALUE',
1294 p_token1 => 'COL_NAME',
1295 p_token1_value => 'receipt method id ');
1296 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1297 END IF;
1298
1299 lp_taiv_rec.object_version_number :=1;
1300 lp_taiv_rec.date_entered :=SYSDATE;
1301 lp_taiv_rec.date_invoiced :=p_invoice_date;
1302 lp_taiv_rec.amount :=p_negotiated_amount;
1303 lp_taiv_rec.description := 'Cure Invoice';
1304 lp_taiv_rec.trx_status_code :=p_trx_status;
1305 lp_taiv_rec.cpy_id :=p_cam_id;
1306 lp_taiv_rec.legal_entity_id :=OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(p_khr_id => lp_taiv_rec.khr_id);
1307
1308 -- R12 Changes - START
1309 -- Following is new as per Ashim's instructions
1310 lp_taiv_rec.okl_source_billing_trx := 'CURE';
1311 lp_taiv_rec.set_of_books_id := okl_accounting_util.get_set_of_books_id;
1312
1313 lp_taiv_rec.tax_exempt_flag := 'S';
1314 lp_taiv_rec.tax_exempt_reason_code := NULL;
1315
1316 open ra_cust_csr;
1317 fetch ra_cust_csr into lp_taiv_rec.cust_trx_type_id;
1318 close ra_cust_csr;
1319
1320 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : lp_taiv_rec.cust_trx_type_id : '||lp_taiv_rec.cust_trx_type_id);
1321 -- R12 Changes - END
1322
1323 IF (G_IS_DEBUG_STATEMENT_ON = true)
1324 THEN
1325 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT,
1326 G_MODULE,
1327 'taiv_rec.cpy_id' ||lp_taiv_rec.cpy_id ||
1328 ' taiv_rec.try_id' ||lp_taiv_rec.try_id||
1329 ' taiv_rec.khr_id' ||lp_taiv_rec.khr_id||
1330 ' taiv_rec.irm_id'||lp_taiv_rec.irm_id||
1331 ' taiv_rec.ibt_id'||lp_taiv_rec.ibt_id||
1332 ' taiv_rec.ixx_id '||lp_taiv_rec.ixx_id||
1333 ' taiv_rec.legal_entity_id '||lp_taiv_rec.legal_entity_id||
1334 ' taiv_rec.irt_id'||lp_taiv_rec.irt_id);
1335 END IF;
1336
1337 okl_trx_ar_invoices_pub.INSERT_trx_ar_invoices
1338 (p_api_version => 1.0,
1339 p_init_msg_list => 'F',
1340 x_return_status => l_return_status,
1341 x_msg_count => l_msg_count,
1342 x_msg_data => l_msg_data,
1343 p_taiv_rec => lp_taiv_rec,
1344 x_taiv_rec => xp_taiv_rec);
1345
1346 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : okl_trx_ar_invoices_pub.INSERT_trx_ar_invoices : '||l_return_status);
1347
1348 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS)
1349 THEN
1350 Get_Messages (l_msg_count,l_message);
1351 IF (G_IS_DEBUG_STATEMENT_ON = true)
1352 THEN
1353 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT,
1354 G_MODULE,
1355 'Error in updating okl_trx_ar_invoices_b '||l_message);
1356 END IF;
1357 raise FND_API.G_EXC_ERROR;
1358 ELSE
1359 FND_MSG_PUB.initialize;
1360
1361 --INSERT okl_txl_ar_inv_lns set error message,so this will be prefixed before
1362 --the actual message, so it makes more sense than displaying an OKL message.
1363
1364 AddfailMsg( p_object => 'RECORD IN OKL_TXL_AR_INV_LNS ',
1365 p_operation => 'INSERT' );
1366 lp_tilv_rec.amount :=p_negotiated_amount;
1367 lp_tilv_rec.object_version_number :=1;
1368 lp_tilv_rec.tai_id :=xp_taiv_rec.id;
1369 lp_tilv_rec.description :='Cure Invoice';
1370 lp_tilv_rec.inv_receiv_line_code :='LINE';
1371 lp_tilv_rec.line_number :=1; -- TXL_AR_LINE_NUMBER
1372
1373 -- R12 Change - START
1374 -- Following is new as per Ashim's instructions
1375 lp_tilv_rec.txl_ar_line_number :=1;
1376 -- R12 Change - END
1377
1378 IF (G_IS_DEBUG_STATEMENT_ON = true)
1379 THEN
1380 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT,
1381 G_MODULE,
1382 'tilv_rec.tai_id' ||lp_tilv_rec.tai_id||
1383 'tilv_rec.amount' ||lp_tilv_rec.amount||
1384 'tilv_rec.sty_id' ||lp_tilv_rec.sty_id);
1385 END IF;
1386 okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns
1387 (p_api_version => 1.0,
1388 p_init_msg_list => 'F',
1389 x_return_status => l_return_status,
1390 x_msg_count => l_msg_count,
1391 x_msg_data => l_msg_data,
1392 p_tilv_rec => lp_tilv_rec,
1393 x_tilv_rec => xp_tilv_rec);
1394
1395 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns : '||l_return_status);
1396
1397 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS)
1398 THEN
1399 Get_Messages (l_msg_count,l_message);
1400 IF (G_IS_DEBUG_STATEMENT_ON = true)
1401 THEN
1402 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT,
1403 G_MODULE,
1404 'Error in updating okl_txl_ar_inv_lns '||l_message);
1405 END IF;
1406 raise FND_API.G_EXC_ERROR;
1407 ELSE
1408 FND_MSG_PUB.initialize;
1409
1410 -- R12 Change - START
1411 -- Ashim's instructions for TXD table
1412 -- populate sty_id, kle_id(NULL), khr_id, amount, til_id_details, txl_ar_line_number
1413 AddfailMsg( p_object => 'RECORD IN OKL_TXD_AR_LN_DTLS ',
1414 p_operation => 'INSERT' );
1415
1416 lp_tldv_rec.TIL_ID_DETAILS := xp_tilv_rec.id;
1417 lp_tldv_rec.STY_ID := xp_tilv_rec.STY_ID;
1418 lp_tldv_rec.AMOUNT := xp_tilv_rec.AMOUNT;
1419 lp_tldv_rec.ORG_ID := xp_tilv_rec.ORG_ID;
1420 lp_tldv_rec.INVENTORY_ORG_ID := xp_tilv_rec.INVENTORY_ORG_ID;
1421 lp_tldv_rec.INVENTORY_ITEM_ID := xp_tilv_rec.INVENTORY_ITEM_ID;
1422 lp_tldv_rec.LINE_DETAIL_NUMBER := 1;
1423 lp_tldv_rec.KHR_ID := lp_taiv_rec.KHR_ID;
1424 lp_tldv_rec.txl_ar_line_number :=1;
1425
1426 okl_internal_billing_pvt.Get_Invoice_format(
1427 p_api_version => 1.0
1428 ,p_init_msg_list => OKL_API.G_FALSE
1429 ,x_return_status => l_return_status
1430 ,x_msg_count => x_msg_count
1431 ,x_msg_data => x_msg_data
1432 ,p_inf_id => lp_taiv_rec.inf_id
1433 ,p_sty_id => lp_tldv_rec.STY_ID
1434 ,x_invoice_format_type => lp_tldv_rec.invoice_format_type
1435 ,x_invoice_format_line_type => lp_tldv_rec.invoice_format_line_type);
1436
1437 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : okl_internal_billing_pvt.Get_Invoice_format : '||l_return_status);
1438
1439 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)
1440 THEN
1441 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1442 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR)
1443 THEN
1444 RAISE Fnd_Api.G_EXC_ERROR;
1445 END IF;
1446
1447 additional_tld_attr(
1448 p_api_version => 1.0,
1449 p_init_msg_list => OKL_API.G_FALSE,
1450 x_return_status => l_return_status,
1451 x_msg_count => x_msg_count,
1452 x_msg_data => x_msg_data,
1453 p_tldv_rec => lp_tldv_rec,
1454 x_tldv_rec => xp_tldv_rec);
1455
1456 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : additional_tld_attr : '||l_return_status);
1457
1458 lp_tldv_rec := xp_tldv_rec;
1459
1460 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS)
1461 THEN
1462 IF (l_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR)
1463 THEN
1464 x_return_status := l_return_status;
1465 END IF;
1466 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1467 END IF;
1468
1469 okl_tld_pvt.insert_row(
1470 p_api_version => 1.0,
1471 p_init_msg_list => OKL_API.G_FALSE,
1472 x_return_status => l_return_status,
1473 x_msg_count => x_msg_count,
1474 x_msg_data => x_msg_data,
1475 p_tldv_rec => lp_tldv_rec,
1476 x_tldv_rec => xp_tldv_rec);
1477
1478 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : okl_tld_pvt.insert_row : '||l_return_status);
1479
1480 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS)
1481 THEN
1482 IF (l_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR)
1483 THEN
1484 x_return_status := l_return_status;
1485 END IF;
1486 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1487 ELSE
1488 FND_MSG_PUB.initialize;
1489 END IF; -- for okl_tld_pvt
1490 -- R12 Change - END
1491
1492 END IF; -- for okl_txl_ar_inv_lns
1493
1494 END IF; -- for okl_trx_ar_invoices
1495
1496 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1497 p_data => x_msg_data );
1498
1499 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1500 OKL_DEBUG_PUB.LOG_DEBUG( FND_LOG.LEVEL_STATEMENT
1501 , G_MODULE
1502 ,' End of Procedure => OKL_PAY_RECON_PVT.INSERT_INVOICE_HDR_LINES');
1503 END IF;
1504
1505 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : INSERT_INVOICE_HDR_LINES : END ');
1506
1507 EXCEPTION
1508
1509 WHEN Fnd_Api.G_EXC_ERROR THEN
1510 ROLLBACK TO INSERT_INVOICE_HDR_LINES;
1511 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1512 x_msg_count := l_msg_count ;
1513 x_msg_data := l_msg_data ;
1514 Fnd_Msg_Pub.count_and_get(
1515 p_count => x_msg_count
1516 ,p_data => x_msg_data);
1517
1518 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1519 ROLLBACK TO INSERT_INVOICE_HDR_LINES;
1520 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1521 x_msg_count := l_msg_count ;
1522 x_msg_data := l_msg_data ;
1523 Fnd_Msg_Pub.count_and_get(
1524 p_count => x_msg_count
1525 ,p_data => x_msg_data);
1526
1527 WHEN OTHERS THEN
1528 ROLLBACK TO INSERT_INVOICE_HDR_LINES;
1529 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1530 x_msg_count := l_msg_count ;
1531 x_msg_data := l_msg_data ;
1532 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_RECON_PVT','INSERT_INVOICE_HDR_LINES');
1533 Fnd_Msg_Pub.count_and_get(
1534 p_count => x_msg_count
1535 ,p_data => x_msg_data);
1536
1537 END insert_invoice_hdr_lines;
1538
1539 PROCEDURE TERMINATE_QUOTE
1540 (
1541 p_cam_id IN NUMBER,
1542 x_return_status OUT NOCOPY VARCHAR2,
1543 x_msg_count OUT NOCOPY NUMBER,
1544 x_msg_data OUT NOCOPY VARCHAR2) IS
1545
1546 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1547 l_msg_count NUMBER ;
1548 l_msg_data VARCHAR2(32627);
1549 l_message VARCHAR2(32627);
1550 l_api_name CONSTANT VARCHAR2(50) := 'TERMINATE_QUOTE';
1551 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'
1552 || l_api_name;
1553
1554 cursor c_get_qte_id ( p_cam_id IN NUMBER ) is
1555 select cam.qte_id,qte.date_effective_to
1556 from okl_cure_amounts cam, okl_trx_quotes_b qte
1557 where cam.cure_amount_id =p_cam_id
1558 and qte.id =cam.qte_id;
1559
1560 lp_term_rec OKL_AM_TERMNT_QUOTE_PUB.term_rec_type;
1561 lx_term_rec OKL_AM_TERMNT_QUOTE_PUB.term_rec_type;
1562 l_err_msg VARCHAR2(2000);
1563
1564 BEGIN
1565
1566 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : TERMINATE_QUOTE : START ');
1567
1568 SAVEPOINT TERMINATE_QUOTE;
1569 IF (G_IS_DEBUG_STATEMENT_ON = true)
1570 THEN
1571 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'start TERMINATE_QUOTE');
1572 END IF;
1573 x_return_status := FND_API.G_RET_STS_SUCCESS;
1574
1575 --set error message,so this will be prefixed before the actual message, so it
1576 --makes more sense than displaying an OKL message.
1577 AddfailMsg( p_object => 'RECORD IN OKL_TRX_QUOTE_B ',
1578 p_operation => 'UPDATE' );
1579
1580 OPEN c_get_qte_id(p_cam_id);
1581 FETCH c_get_qte_id INTO lp_term_rec.id, lp_term_rec.date_effective_to;
1582 CLOSE c_get_qte_id;
1583
1584 lp_term_rec.accepted_yn := 'Y';
1585
1586 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : TERMINATE_QUOTE : lp_term_rec.id : '||lp_term_rec.id);
1587
1588 OKL_AM_TERMNT_QUOTE_PUB.TERMINATE_QUOTE(
1589 p_api_version => 1.0
1590 ,p_init_msg_list => 'T'
1591 ,x_return_status => l_return_status
1592 ,x_msg_count => l_msg_count
1593 ,x_msg_data => l_msg_data
1594 ,p_term_rec => lp_term_rec
1595 ,x_term_rec => lx_term_rec
1596 ,x_err_msg => l_err_msg);
1597
1598 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : TERMINATE_QUOTE : OKL_AM_TERMNT_QUOTE_PUB.TERMINATE_QUOTE : '||l_return_status);
1599
1600 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS)
1601 THEN
1602 Get_Messages (l_msg_count,l_message);
1603 IF (G_IS_DEBUG_STATEMENT_ON = true)
1604 THEN
1605 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT,
1606 G_MODULE,
1607 'Error in updating okl_trx_ar_invoices_b '||l_message);
1608 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT,
1609 G_MODULE,
1610 'Error from the API : ' ||l_err_msg);
1611 END IF;
1612 raise FND_API.G_EXC_ERROR;
1613 END IF;
1614
1615 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1616 p_data => x_msg_data );
1617 IF (G_IS_DEBUG_STATEMENT_ON = true)
1618 THEN
1619 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT,
1620 G_MODULE,
1621 ' End of Procedure =>OKL_PAY_RECON_PVT.TERMINATE_QUOTE');
1622 END IF;
1623
1624 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : TERMINATE_QUOTE : END ');
1625
1626 EXCEPTION
1627 WHEN Fnd_Api.G_EXC_ERROR THEN
1628 ROLLBACK TO TERMINATE_QUOTE;
1629 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1630 x_msg_count := l_msg_count ;
1631 x_msg_data := l_msg_data ;
1632 Fnd_Msg_Pub.count_and_get(
1633 p_count => x_msg_count
1634 ,p_data => x_msg_data);
1635 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1636 ROLLBACK TO TERMINATE_QUOTE;
1637 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1638 x_msg_count := l_msg_count ;
1639 x_msg_data := l_msg_data ;
1640 Fnd_Msg_Pub.count_and_get(
1641 p_count => x_msg_count
1642 ,p_data => x_msg_data);
1643 WHEN OTHERS THEN
1644 ROLLBACK TO TERMINATE_QUOTE;
1645 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1646 x_msg_count := l_msg_count ;
1647 x_msg_data := l_msg_data ;
1648 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_RECON_PVT','TERMINATE_QUOTE');
1649 Fnd_Msg_Pub.count_and_get(
1650 p_count => x_msg_count
1651 ,p_data => x_msg_data);
1652
1653 END TERMINATE_QUOTE;
1654
1655 PROCEDURE UPDATE_TAI_TIL (p_cam_tbl IN cure_amount_tbl,
1656 p_invoice_date IN DATE,
1657 p_trx_status IN VARCHAR2,
1658 x_return_status OUT NOCOPY VARCHAR2,
1659 x_msg_count OUT NOCOPY NUMBER,
1660 x_msg_data OUT NOCOPY VARCHAR2) IS
1661
1662 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1663 l_msg_count NUMBER ;
1664 l_msg_data VARCHAR2(32627);
1665 l_message VARCHAR2(32627);
1666 l_api_name CONSTANT VARCHAR2(50) := 'UPDATE_TAI_TIL';
1667 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'
1668 || l_api_name;
1669
1670 Cursor c_get_tai_id ( p_cam_id IN NUMBER) is
1671 select id
1672 from okl_trx_ar_invoices_b where
1673 cpy_id =p_cam_id;
1674
1675 l_tai_id okl_trx_ar_invoices_b.id%TYPE;
1676 l_process OKL_CURE_AMOUNTS.process%TYPE;
1677 BEGIN
1678
1679 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_TAI_TIL : START ');
1680
1681 IF (G_DEBUG_ENABLED = 'Y')
1682 THEN
1683 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1684 END IF;
1685
1686 SAVEPOINT UPDATE_TAI_TIL;
1687 IF (G_IS_DEBUG_STATEMENT_ON = true)
1688 THEN
1689 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'start UPDATE_TAI_TIL');
1690 END IF;
1691 x_return_status := FND_API.G_RET_STS_SUCCESS;
1692
1693 -- if cpy_id is populated then we will update the TAI tables
1694 -- else do an insert in tai
1695 --09/24/03 -- Do the above only if the process ='CURE'
1696 -- else call Terminate Quote.
1697
1698 FOR i in p_cam_tbl.FIRST..p_cam_tbl.LAST
1699 LOOP
1700
1701 --11/26/03 if process is cure, the html screen does not
1702 -- have a drop down field
1703 --jsanju 11/26/03
1704 IF p_cam_tbl(i).process = 'REPURCHASE'
1705 THEN
1706 l_process :='REPURCHASE' ;
1707 ELSIF p_cam_tbl(i).process = 'DONOTPROCESS'
1708 THEN
1709 l_process :='DONOTPROCESS' ;
1710 ELSE
1711 l_process :='CURE';
1712 END IF;
1713
1714 IF l_process ='CURE'
1715 THEN
1716 l_tai_id :=NULL;
1717 OPEN c_get_tai_id(p_cam_tbl(i).cam_id);
1718 FETCH c_get_tai_id INTO l_tai_id;
1719 CLOSE c_get_tai_id;
1720
1721 -- ASHIM CHANGE - START
1722
1723 IF l_tai_id IS NOT NULL
1724 THEN
1725 update_invoice_hdr_lines (
1726 p_negotiated_amount =>p_cam_tbl(i).negotiated_amount,
1727 p_tai_id =>l_tai_id,
1728 p_trx_status =>p_trx_status,
1729 p_invoice_date =>p_invoice_date,
1730 x_return_status =>l_return_status,
1731 x_msg_count =>l_msg_count,
1732 x_msg_data =>l_msg_data);
1733 ELSE
1734 insert_invoice_hdr_lines (
1735 p_negotiated_amount =>p_cam_tbl(i).negotiated_amount,
1736 p_cam_id =>p_cam_tbl(i).cam_id,
1737 p_trx_status =>p_trx_status,
1738 p_invoice_date =>p_invoice_date,
1739 x_return_status =>l_return_status,
1740 x_msg_count =>l_msg_count,
1741 x_msg_data =>l_msg_data);
1742
1743 END IF;
1744
1745 -- ASHIM CHANGE - END
1746
1747 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS)
1748 THEN
1749 Get_Messages (l_msg_count,l_message);
1750 IF (G_IS_DEBUG_STATEMENT_ON = true)
1751 THEN
1752 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT,
1753 G_MODULE,
1754 'Error in update tai_til :' ||l_message);
1755 END IF;
1756 raise FND_API.G_EXC_ERROR;
1757 END IF;
1758
1759 --if process is 'REPURCHASE
1760 --and if p_trx_status ='SUBMITTED' that means the action from the UI
1761 --is 'SUBMIT' , we do not call terminate quote if action is update
1762 ELSIF l_process ='REPURCHASE' AND p_trx_status ='SUBMITTED'
1763 THEN
1764 Terminate_quote (
1765 p_cam_id =>p_cam_tbl(i).cam_id,
1766 x_return_status =>l_return_status,
1767 x_msg_count =>l_msg_count,
1768 x_msg_data =>l_msg_data);
1769
1770 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS)
1771 THEN
1772 Get_Messages (l_msg_count,l_message);
1773 IF (G_IS_DEBUG_STATEMENT_ON = true)
1774 THEN
1775 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT,
1776 G_MODULE,
1777 'Error in Termination of Quote :' ||l_message);
1778 END IF;
1779 raise FND_API.G_EXC_ERROR;
1780 END IF;
1781
1782 END IF; --if process is CURE
1783
1784 END LOOP;
1785
1786 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1787 p_data => x_msg_data);
1788
1789 IF (G_IS_DEBUG_STATEMENT_ON = true)
1790 THEN
1791 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT,
1792 G_MODULE,
1793 ' End of Procedure =>OKL_PAY_RECON_PVT.UPDATE_TAI_TIL');
1794 END IF;
1795
1796 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_TAI_TIL : END ');
1797
1798 EXCEPTION
1799 WHEN Fnd_Api.G_EXC_ERROR THEN
1800 ROLLBACK TO UPDATE_TAI_TIL;
1801 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1802 x_msg_count := l_msg_count ;
1803 x_msg_data := l_msg_data ;
1804 Fnd_Msg_Pub.count_and_get(
1805 p_count => x_msg_count
1806 ,p_data => x_msg_data);
1807 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1808 ROLLBACK TO UPDATE_TAI_TIL;
1809 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1810 x_msg_count := l_msg_count ;
1811 x_msg_data := l_msg_data ;
1812 Fnd_Msg_Pub.count_and_get(
1813 p_count => x_msg_count
1814 ,p_data => x_msg_data);
1815 WHEN OTHERS THEN
1816 ROLLBACK TO UPDATE_TAI_TIL;
1817 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1818 x_msg_count := l_msg_count ;
1819 x_msg_data := l_msg_data ;
1820 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_RECON_PVT','UPDATE_TAI_TIL');
1821 Fnd_Msg_Pub.count_and_get(
1822 p_count => x_msg_count
1823 ,p_data => x_msg_data);
1824
1825 END UPDATE_TAI_TIL;
1826
1827 PROCEDURE UPDATE_INVOICES (p_cam_tbl IN cure_amount_tbl,
1828 p_report_id IN NUMBER,
1829 p_invoice_date IN DATE,
1830 x_return_status OUT NOCOPY VARCHAR2,
1831 x_msg_count OUT NOCOPY NUMBER,
1832 x_msg_data OUT NOCOPY VARCHAR2) IS
1833 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1834 l_msg_count NUMBER ;
1835 l_msg_data VARCHAR2(32627);
1836 l_message VARCHAR2(32627);
1837 l_api_name CONSTANT VARCHAR2(50) := 'UPDATE_INVOICES';
1838 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'
1839 || l_api_name;
1840
1841 BEGIN
1842
1843 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_INVOICES : START ');
1844 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_INVOICES : p_report_id : '||p_report_id);
1845 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_INVOICES : p_invoice_date : '||p_invoice_date);
1846
1847 IF (G_DEBUG_ENABLED = 'Y') THEN
1848 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
1849 END IF;
1850
1851 -- step 1 create TAI ab
1852 -- step 2 update CAM
1853 -- step 3 update CRT
1854
1855 --09/24 --check the process type, if
1856 --1)CURE -create TAI,updateCAM,updateCRT
1857 --2)REPURCHASE -create quote,UpdateCAM,updateCRT
1858 --3)DONOTPROCESS - UpdateCAM,UpdateCRT
1859
1860 SAVEPOINT UPDATE_INVOICES;
1861 IF (G_IS_DEBUG_STATEMENT_ON = true)
1862 THEN
1863 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'start UPDATE_INVOICES');
1864 END IF;
1865 x_return_status := FND_API.G_RET_STS_SUCCESS;
1866
1867 UPDATE_TAI_TIL(p_cam_tbl =>p_cam_tbl,
1868 p_invoice_date =>p_invoice_date,
1869 p_trx_status =>'WORKING',
1870 x_return_status =>l_return_status,
1871 x_msg_count =>l_msg_count,
1872 x_msg_data =>l_msg_data);
1873
1874 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS)
1875 THEN
1876 Get_Messages (l_msg_count,l_message);
1877 IF (G_IS_DEBUG_STATEMENT_ON = true)
1878 THEN
1879 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT,
1880 G_MODULE,
1881 'Error in updating cure amounts:'||l_message);
1882 END IF;
1883 raise FND_API.G_EXC_ERROR;
1884 END IF;
1885
1886 UPDATE_CAM (p_cam_tbl =>p_cam_tbl,
1887 p_report_id =>p_report_id,
1888 x_return_status =>l_return_status,
1889 x_msg_count =>l_msg_count,
1890 x_msg_data =>l_msg_data);
1891
1892 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS)
1893 THEN
1894 Get_Messages (l_msg_count,l_message);
1895 IF (G_IS_DEBUG_STATEMENT_ON = true)
1896 THEN
1897 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT,
1898 G_MODULE,
1899 'Error in updating cure amounts:'||l_message);
1900 END IF;
1901 raise FND_API.G_EXC_ERROR;
1902 END IF;
1903
1904 UPDATE_CRT (p_report_id =>p_report_id,
1905 p_status =>'ACCEPTANCE_IN_PROGRESS',
1906 x_return_status =>l_return_status,
1907 x_msg_count =>l_msg_count,
1908 x_msg_data =>l_msg_data);
1909
1910 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS)
1911 THEN
1912 Get_Messages (l_msg_count,l_message);
1913 IF (G_IS_DEBUG_STATEMENT_ON = true)
1914 THEN
1915 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error is :' ||l_message);
1916 END IF;
1917 raise FND_API.G_EXC_ERROR;
1918 ELSE
1919 IF (G_IS_DEBUG_STATEMENT_ON = true)
1920 THEN
1921 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Updated cure reports table ');
1922 END IF;
1923 END IF;
1924
1925 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1926 p_data => x_msg_data );
1927
1928 IF (G_IS_DEBUG_STATEMENT_ON = true)
1929 THEN
1930 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT,
1931 G_MODULE,
1932 ' End of Procedure =>OKL_PAY_RECON_PVT.update_invoices');
1933 END IF;
1934
1935 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_INVOICES : END ');
1936
1937 EXCEPTION
1938 WHEN Fnd_Api.G_EXC_ERROR THEN
1939 ROLLBACK TO UPDATE_INVOICES;
1940 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1941 x_msg_count := l_msg_count ;
1942 x_msg_data := l_msg_data ;
1943 Fnd_Msg_Pub.count_and_get(
1944 p_count => x_msg_count
1945 ,p_data => x_msg_data);
1946 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1947 ROLLBACK TO UPDATE_INVOICES;
1948 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1949 x_msg_count := l_msg_count ;
1950 x_msg_data := l_msg_data ;
1951 Fnd_Msg_Pub.count_and_get(
1952 p_count => x_msg_count
1953 ,p_data => x_msg_data);
1954 WHEN OTHERS THEN
1955 ROLLBACK TO UPDATE_INVOICES;
1956 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1957 x_msg_count := l_msg_count ;
1958 x_msg_data := l_msg_data ;
1959 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_RECON_PVT','UPDATE_INVOICES');
1960 Fnd_Msg_Pub.count_and_get(
1961 p_count => x_msg_count
1962 ,p_data => x_msg_data);
1963
1964 END UPDATE_INVOICES;
1965
1966 PROCEDURE CREATE_ACCOUNTING(p_cam_tbl IN cure_amount_tbl,
1967 x_return_status OUT NOCOPY VARCHAR2,
1968 x_msg_count OUT NOCOPY NUMBER,
1969 x_msg_data OUT NOCOPY VARCHAR2)
1970 IS
1971
1972 -- Cursors plucked from OKL_LA_JE_PVT for a/c - START
1973 CURSOR fnd_pro_csr
1974 IS
1975 SELECT mo_global.get_current_org_id() l_fnd_profile
1976 FROM dual;
1977
1978 fnd_pro_rec fnd_pro_csr%ROWTYPE;
1979
1980 CURSOR ra_cust_csr
1981 IS
1982 SELECT cust_trx_type_id l_cust_trx_type_id
1983 FROM ra_cust_trx_types
1984 WHERE name = 'Invoice-OKL';
1985
1986 ra_cust_rec ra_cust_csr%ROWTYPE;
1987
1988 CURSOR salesP_csr (p_contract_id IN NUMBER)
1989 IS
1990 SELECT ct.object1_id1 id
1991 ,chr.scs_code scs_code
1992 FROM okc_contacts ct,
1993 okc_contact_sources csrc,
1994 okc_k_party_roles_b pty,
1995 okc_k_headers_b chr
1996 WHERE ct.cpl_id = pty.id
1997 AND ct.cro_code = csrc.cro_code
1998 AND ct.jtot_object1_code = csrc.jtot_object_code
1999 AND ct.dnz_chr_id = chr.id
2000 AND pty.rle_code = csrc.rle_code
2001 AND csrc.cro_code = 'SALESPERSON'
2002 AND csrc.rle_code = 'LESSOR'
2003 AND csrc.buy_or_sell = chr.buy_or_sell
2004 AND pty.dnz_chr_id = chr.id
2005 AND pty.chr_id = chr.id
2006 AND chr.id = p_contract_id;
2007
2008 l_salesP_rec salesP_csr%ROWTYPE;
2009
2010 CURSOR custBillTo_csr (p_contract_id IN NUMBER)
2011 IS
2012 SELECT bill_to_site_use_id cust_acct_site_id
2013 FROM okc_k_headers_b
2014 WHERE id = p_contract_id;
2015
2016 l_custBillTo_rec custBillTo_csr%ROWTYPE;
2017
2018 -- Cursors plucked from OKL_LA_JE_PVT for a/c - END
2019
2020
2021 cursor c_get_contract_currency (l_khr_id IN NUMBER)
2022 IS
2023 select currency_code
2024 from OKC_K_HEADERS_b
2025 where id =l_khr_id;
2026
2027 CURSOR curr_csr (l_khr_id NUMBER)
2028 IS
2029 SELECT currency_conversion_type,
2030 currency_conversion_rate,
2031 currency_conversion_date
2032 FROM okl_k_headers
2033 WHERE id = l_khr_id;
2034
2035 l_functional_currency okl_trx_contracts.currency_code%TYPE;
2036 l_currency_conversion_type okl_k_headers.currency_conversion_type%TYPE;
2037 l_currency_conversion_rate okl_k_headers.currency_conversion_rate%TYPE;
2038 l_currency_conversion_date okl_k_headers.currency_conversion_date%TYPE;
2039 l_contract_currency OKC_K_HEADERS_b.currency_code%TYPE;
2040
2041 next_row integer;
2042
2043 CURSOR c_get_accounting(p_cam_id IN NUMBER)
2044 IS
2045 SELECT tai.id
2046 ,tai.try_id
2047 ,til.sty_id
2048 ,tld.id
2049 ,tai.khr_id
2050 ,tai.date_invoiced
2051 ,tai.amount
2052 FROM okl_trx_ar_invoices_b tai
2053 ,okl_txl_ar_inv_lns_b til
2054 ,okl_txd_ar_ln_dtls_b tld
2055 WHERE tai.cpy_id = p_cam_id
2056 AND tai.id = til.tai_id
2057 AND tld.til_id_details = til.id;
2058
2059 l_tai_id okl_trx_ar_invoices_b.id%TYPE;
2060 l_sty_id okl_txl_ar_inv_lns_b.sty_id%TYPE;
2061 l_try_id okl_trx_ar_invoices_b.try_id%TYPE;
2062 l_line_id okl_txd_ar_ln_dtls_b.id%TYPE;
2063 l_khr_id okc_k_headers_b.id%TYPE;
2064 l_date_invoiced okl_trx_ar_invoices_b.date_invoiced%TYPE;
2065 l_amount okl_trx_ar_invoices_b.amount%TYPE;
2066
2067 CURSOR get_product_id(p_cam_id IN NUMBER)
2068 IS
2069 SELECT okl.pdt_id
2070 ,okl.id
2071 ,okc.scs_code -- Bug# 4622198
2072 FROM okl_k_headers okl,
2073 okl_cure_amounts cam,
2074 okc_k_headers_b okc -- Bug# 4622198
2075 WHERE okc.id = okl.id -- Bug# 4622198
2076 and okl.id = cam.chr_id
2077 and cam.cure_amount_id =p_cam_id;
2078
2079 /* -- OKL.H Code commented out
2080 l_tmpl_identify_rec Okl_Account_Dist_Pvt.TMPL_IDENTIFY_REC_TYPE;
2081 l_dist_info_rec Okl_Account_Dist_Pvt.dist_info_REC_TYPE;
2082 l_ctxt_val_tbl okl_execute_formula_pvt.ctxt_val_tbl_type;
2083 l_acc_gen_primary_key_tbl Okl_Account_Generator_Pvt.primary_key_tbl;
2084 l_template_tbl Okl_Account_Dist_Pub.AVLV_TBL_TYPE;
2085 l_amount_tbl Okl_Account_Dist_Pub.AMOUNT_TBL_TYPE;
2086 */
2087
2088 -- R12 Change - START
2089 l_tmpl_identify_tbl okl_account_dist_pvt.tmpl_identify_tbl_type;
2090 l_dist_info_tbl okl_account_dist_pvt.dist_info_tbl_type;
2091 l_template_tbl okl_account_dist_pvt.avlv_out_tbl_type;
2092 l_amount_tbl okl_account_dist_pvt.amount_out_tbl_type;
2093 l_ctxt_val_tbl okl_account_dist_pvt.ctxt_tbl_type;
2094 --l_acc_gen_primary_key_tbl okl_account_dist_pvt.acc_gen_tbl_type;
2095 l_acc_gen_primary_key_tbl okl_account_dist_pvt.acc_gen_primary_key;
2096 l_acc_gen_tbl okl_account_dist_pvt.ACC_GEN_TBL_TYPE;
2097 -- R12 Change - END
2098
2099 l_factoring_synd VARCHAR2(30);
2100 l_syndication_code VARCHAR2(30) DEFAULT NULL;
2101 l_factoring_code VARCHAR2(30) DEFAULT NULL;
2102 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2103 l_msg_count NUMBER ;
2104 l_msg_data VARCHAR2(32627);
2105 l_message VARCHAR2(32627);
2106 l_api_name CONSTANT VARCHAR2(50) := 'CREATE_ACCOUNTING';
2107 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'
2108 || l_api_name;
2109 l_process OKL_CURE_AMOUNTS.process%TYPE;
2110
2111 --Bug# 4622198 :For special accounting treatment - START
2112
2113 l_fact_synd_code FND_LOOKUPS.Lookup_code%TYPE;
2114 l_inv_acct_code OKC_RULES_B.Rule_Information1%TYPE;
2115 l_scs_code okc_k_headers_b.SCS_CODE%TYPE;
2116
2117 --Bug# 4622198 :For special accounting treatment - END
2118
2119 BEGIN
2120
2121 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : CREATE_ACCOUNTING : START ');
2122
2123 IF (G_DEBUG_ENABLED = 'Y') THEN
2124 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2125 END IF;
2126
2127 SAVEPOINT CREATE_ACCOUNTING;
2128 IF (G_IS_DEBUG_STATEMENT_ON = true)
2129 THEN
2130 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'start submit_cure_invoices');
2131 END IF;
2132 x_return_status := FND_API.G_RET_STS_SUCCESS;
2133
2134 FOR i in p_cam_tbl.FIRST..p_cam_tbl.LAST
2135 LOOP
2136 --11/26/03 if process is cure, the html screen does not
2137 -- have a drop down field
2138 --jsanju 11/26/03
2139 IF p_cam_tbl(i).process = 'REPURCHASE'
2140 THEN
2141 l_process :='REPURCHASE' ;
2142 ELSIF p_cam_tbl(i).process = 'DONOTPROCESS'
2143 THEN
2144 l_process :='DONOTPROCESS' ;
2145 ELSE
2146 l_process :='CURE';
2147 END IF;
2148
2149 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : CREATE_ACCOUNTING : l_process : '||l_process);
2150
2151 IF l_process ='CURE'
2152 THEN
2153 OPEN get_product_id(p_cam_tbl(i).cam_id);
2154 FETCH get_product_id
2155 INTO l_tmpl_identify_tbl(1).product_id
2156 ,l_khr_id
2157 ,l_scs_code;
2158 CLOSE get_product_id;
2159
2160 IF l_tmpl_identify_tbl(1).product_id IS NULL
2161 THEN
2162 OKL_API.SET_MESSAGE (p_app_name => 'OKL',
2163 p_msg_name => 'OKL_NO_PRODUCT_FOUND');
2164 raise FND_API.G_EXC_ERROR;
2165 END IF;
2166
2167 l_factoring_synd := get_factor_synd(l_khr_id);
2168 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : CREATE_ACCOUNTING : l_factoring_synd : '||l_factoring_synd);
2169
2170 OPEN c_get_Accounting(p_cam_tbl(i).cam_id);
2171 FETCH c_get_Accounting
2172 INTO l_tai_id
2173 ,l_try_id
2174 ,l_sty_id
2175 ,l_line_id
2176 ,l_khr_id
2177 ,l_date_invoiced
2178 ,l_amount;
2179 CLOSE c_get_Accounting;
2180
2181 l_tmpl_identify_tbl(1).transaction_type_id := l_try_id;
2182 l_tmpl_identify_tbl(1).stream_type_id := l_sty_id;
2183 l_tmpl_identify_tbl(1).ADVANCE_ARREARS := NULL;
2184 l_tmpl_identify_tbl(1).FACTORING_SYND_FLAG := NULL;
2185 l_tmpl_identify_tbl(1).SYNDICATION_CODE := NULL;
2186 l_tmpl_identify_tbl(1).FACTORING_CODE := NULL;
2187 l_tmpl_identify_tbl(1).MEMO_YN := 'N';
2188 l_tmpl_identify_tbl(1).PRIOR_YEAR_YN := 'N';
2189 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : CREATE_ACCOUNTING : l_tmpl_identify_tbl(1).transaction_type_id : '||l_tmpl_identify_tbl(1).transaction_type_id);
2190 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : CREATE_ACCOUNTING : l_tmpl_identify_tbl(1).stream_type_id : '||l_tmpl_identify_tbl(1).stream_type_id);
2191
2192 l_dist_info_tbl(1).source_id := l_line_id;
2193 l_dist_info_tbl(1).source_table := 'OKL_TXD_AR_LN_DTLS_B';
2194 l_dist_info_tbl(1).accounting_date := l_date_invoiced;
2195 l_dist_info_tbl(1).gl_reversal_flag :='N';
2196 l_dist_info_tbl(1).post_to_gl :='N';
2197 l_dist_info_tbl(1).contract_id :=l_khr_id;
2198 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : CREATE_ACCOUNTING : l_dist_info_tbl(1).source_id : '||l_dist_info_tbl(1).source_id);
2199 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : CREATE_ACCOUNTING : l_dist_info_tbl(1).source_table : '||l_dist_info_tbl(1).source_table);
2200 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : CREATE_ACCOUNTING : l_dist_info_tbl(1).accounting_date : '||l_dist_info_tbl(1).accounting_date);
2201 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : CREATE_ACCOUNTING : l_dist_info_tbl(1).contract_id : '||l_dist_info_tbl(1).contract_id);
2202
2203 -- New Accounting call Start set accounting call required values
2204 -- Fetch the functional currency
2205
2206 l_functional_currency := OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE;
2207
2208 -- Fetch the currency conversion factors if functional currency is not equal
2209 -- to the transaction currency
2210
2211 OPEN c_get_contract_currency (l_khr_id);
2212 FETCH c_get_contract_currency INTO l_contract_currency;
2213 CLOSE c_get_contract_currency;
2214
2215 l_dist_info_tbl(1).currency_code := l_contract_currency;
2216
2217 IF l_functional_currency <> l_contract_currency
2218 THEN
2219 -- Fetch the currency conversion factors from Contracts
2220 FOR curr_rec IN curr_csr(l_khr_id)
2221 LOOP
2222 l_currency_conversion_type := curr_rec.currency_conversion_type;
2223 l_currency_conversion_rate := curr_rec.currency_conversion_rate;
2224 l_currency_conversion_date := curr_rec.currency_conversion_date;
2225 END LOOP;
2226
2227 -- Fetch the currency conversion factors from GL_DAILY_RATES if the
2228 -- conversion type is not 'USER'.
2229
2230 IF UPPER(l_currency_conversion_type) <> 'USER'
2231 THEN
2232 l_currency_conversion_date := SYSDATE;
2233 l_currency_conversion_rate := okl_accounting_util.get_curr_con_rate
2234 (p_from_curr_code => l_contract_currency,
2235 p_to_curr_code => l_functional_currency,
2236 p_con_date => l_currency_conversion_date,
2237 p_con_type => l_currency_conversion_type);
2238
2239 END IF; -- End IF for (UPPER(l_currency_conversion_type) <> 'USER')
2240
2241 END IF; -- End IF for (l_functional_currency <> l_contract_currency)
2242
2243 -- Populate the currency conversion factors
2244 l_dist_info_tbl(1).currency_conversion_type := l_currency_conversion_type;
2245 l_dist_info_tbl(1).currency_conversion_rate := l_currency_conversion_rate;
2246 l_dist_info_tbl(1).currency_conversion_date := l_currency_conversion_date;
2247
2248 -- Round the transaction amount
2249 l_dist_info_tbl(1).amount:= okl_accounting_util.cross_currency_round_amount
2250 (p_amount => l_amount,
2251 p_currency_code => l_contract_currency);
2252
2253 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : CREATE_ACCOUNTING : l_dist_info_tbl(1).amount : '||l_dist_info_tbl(1).amount);
2254
2255 --set error message,so this will be prefixed before the
2256 --actual message, so it makes more sense than displaying an
2257 -- OKL message.
2258 -- R12 CHANGE- START
2259 /*
2260 --Do not know what this segment does. Hence commented out,
2261 --will enable if required during test run
2262 AddfailMsg( p_object => 'Okl_Acc_Call_Pvt.Okl_Populate_Acc_Gen ',
2263 p_operation => 'CREATE' );
2264
2265 Okl_Acc_Call_Pvt.Okl_Populate_Acc_Gen (
2266 p_contract_id => l_khr_id,
2267 p_contract_line_id => NULL,
2268 x_acc_gen_tbl => l_acc_gen_primary_key_tbl,
2269 x_return_status => l_return_status );
2270
2271 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS)
2272 THEN
2273 raise FND_API.G_EXC_ERROR;
2274 ELSE
2275 IF (G_IS_DEBUG_STATEMENT_ON = true)
2276 THEN
2277 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT,
2278 G_MODULE,
2279 'l_acc_gen_primary_key_tbl'
2280 --||l_acc_gen_primary_key_tbl(1).count
2281 ||l_acc_gen_primary_key_tbl(1).primary_key_column
2282 ||l_acc_gen_primary_key_tbl(1).source_table );
2283 END IF;
2284 FND_MSG_PUB.initialize;
2285 END IF;
2286 */
2287
2288 l_acc_gen_primary_key_tbl(1).source_table := 'FINANCIALS_SYSTEM_PARAMETERS';
2289 OPEN fnd_pro_csr;
2290 FETCH fnd_pro_csr INTO fnd_pro_rec;
2291 IF ( fnd_pro_csr%NOTFOUND )
2292 THEN
2293 l_acc_gen_primary_key_tbl(1).primary_key_column := '';
2294 ELSE
2295 l_acc_gen_primary_key_tbl(1).primary_key_column := fnd_pro_rec.l_fnd_profile;
2296 End IF;
2297 CLOSE fnd_pro_csr;
2298
2299 l_acc_gen_primary_key_tbl(2).source_table := 'AR_SITE_USES_V';
2300 OPEN custBillTo_csr(l_khr_id);
2301 FETCH custBillTo_csr INTO l_custBillTo_rec;
2302 CLOSE custBillTo_csr;
2303 l_acc_gen_primary_key_tbl(2).primary_key_column := l_custBillTo_rec.cust_acct_site_id;
2304
2305 l_acc_gen_primary_key_tbl(3).source_table := 'RA_CUST_TRX_TYPES';
2306 OPEN ra_cust_csr;
2307 FETCH ra_cust_csr INTO ra_cust_rec;
2308 IF ( ra_cust_csr%NOTFOUND ) THEN
2309 l_acc_gen_primary_key_tbl(3).primary_key_column := '';
2310 ELSE
2311 l_acc_gen_primary_key_tbl(3).primary_key_column := TO_CHAR(ra_cust_rec.l_cust_trx_type_id);
2312 END IF;
2313 CLOSE ra_cust_csr;
2314
2315 l_acc_gen_primary_key_tbl(4).source_table := 'JTF_RS_SALESREPS_MO_V';
2316 OPEN salesP_csr(l_khr_id);
2317 FETCH salesP_csr INTO l_salesP_rec;
2318 CLOSE salesP_csr;
2319 l_acc_gen_primary_key_tbl(4).primary_key_column := l_salesP_rec.id;
2320
2321 l_acc_gen_tbl(1).acc_gen_key_tbl := l_acc_gen_primary_key_tbl;
2322 l_acc_gen_tbl(1).source_id := l_line_id;
2323
2324 -- R12 CHANGE- END
2325
2326 --set error message,so this will be prefixed before the
2327 --actual message, so it makes more sense than displaying an
2328 -- OKL message.
2329 AddfailMsg( p_object => 'OKL_SECURITIZATION_PVT.Check_Khr_ia_associated ',
2330 p_operation => 'CREATE' );
2331
2332 --Bug# 4622198 :For special accounting treatment - START
2333 OKL_SECURITIZATION_PVT.Check_Khr_ia_associated(
2334 p_api_version => 1.0,
2335 p_init_msg_list => OKL_API.G_FALSE,
2336 x_return_status => x_return_status,
2337 x_msg_count => x_msg_count,
2338 x_msg_data => x_msg_data,
2339 p_khr_id => l_khr_id,
2340 --p_scs_code => l_scs_code,
2341 p_scs_code => l_salesP_rec.scs_code,
2342 p_trx_date => l_date_invoiced,
2343 x_fact_synd_code => l_fact_synd_code,
2344 x_inv_acct_code => l_inv_acct_code
2345 );
2346
2347 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : CREATE_ACCOUNTING : OKL_SECURITIZATION_PVT.Check_Khr_ia_associated : '||x_return_status);
2348
2349 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)
2350 THEN
2351 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2352 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR)
2353 THEN
2354 RAISE OKL_API.G_EXCEPTION_ERROR;
2355 END IF;
2356
2357 l_tmpl_identify_tbl(1).factoring_synd_flag := l_fact_synd_code;
2358 l_tmpl_identify_tbl(1).investor_code := l_inv_acct_code;
2359 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : CREATE_ACCOUNTING : l_tmpl_identify_tbl(1).factoring_synd_flag : '||l_tmpl_identify_tbl(1).factoring_synd_flag);
2360 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : CREATE_ACCOUNTING : l_tmpl_identify_tbl(1).investor_code : '||l_tmpl_identify_tbl(1).investor_code);
2361
2362
2363 --Bug# 4622198 :For special accounting treatment - END
2364
2365 --set error message,so this will be prefixed before the
2366 --actual message, so it makes more sense than displaying an
2367 -- OKL message.
2368 AddfailMsg( p_object => 'Okl_Account_Dist_Pub.CREATE_ACCOUNTING_DIST ',
2369 p_operation => 'CREATE' );
2370
2371 /* OKL.H code commented out
2372 Okl_Account_Dist_Pvt.CREATE_ACCOUNTING_DIST(
2373 p_api_version => 1.0
2374 ,p_init_msg_list => 'F'
2375 ,x_return_status => l_return_status
2376 ,x_msg_count => l_msg_count
2377 ,x_msg_data => l_msg_data
2378 ,p_tmpl_identify_rec => l_tmpl_identify_rec
2379 ,p_dist_info_rec => l_dist_info_rec
2380 ,p_ctxt_val_tbl => l_ctxt_val_tbl
2381 ,p_acc_gen_primary_key_tbl => l_acc_gen_primary_key_tbl
2382 ,x_template_tbl => l_template_tbl
2383 ,x_amount_tbl => l_amount_tbl);
2384 */
2385
2386 -- R12 CHANGE - START
2387 okl_account_dist_pvt.create_accounting_dist(
2388 p_api_version => 1.0,
2389 p_init_msg_list => OKL_API.G_FALSE,
2390 x_return_status => l_return_status,
2391 x_msg_count => l_msg_count,
2392 x_msg_data => l_msg_data,
2393 p_tmpl_identify_tbl => l_tmpl_identify_tbl,
2394 p_dist_info_tbl => l_dist_info_tbl,
2395 p_ctxt_val_tbl => l_ctxt_val_tbl,
2396 p_acc_gen_primary_key_tbl => l_acc_gen_tbl,
2397 x_template_tbl => l_template_tbl,
2398 x_amount_tbl => l_amount_tbl,
2399 p_trx_header_id => l_tai_id,
2400 p_trx_header_table => 'OKL_TRX_AR_INVOICES_B');
2401
2402 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : CREATE_ACCOUNTING : okl_account_dist_pvt.create_accounting_dist : '||l_return_status);
2403
2404 -- R12 CHANGE - END
2405
2406 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS)
2407 THEN
2408 Get_Messages (l_msg_count,l_message);
2409 IF (G_IS_DEBUG_STATEMENT_ON = true)
2410 THEN
2411 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
2412 END IF;
2413 raise FND_API.G_EXC_ERROR;
2414 ELSE
2415 IF (G_IS_DEBUG_STATEMENT_ON = true)
2416 THEN
2417 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'count of l_template_tbl'||l_template_tbl.count);
2418 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'count of l_amount_tbl'||l_amount_tbl.count);
2419 END IF;
2420 FND_MSG_PUB.initialize;
2421 END IF;
2422 END IF ;-- IF p.cam.tbl(i).Process ='CURE'
2423 END LOOP;
2424
2425 IF (G_IS_DEBUG_STATEMENT_ON = true)
2426 THEN
2427 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT,
2428 G_MODULE,
2429 'after accounting dist '||l_return_status);
2430 END IF;
2431
2432 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2433 p_data => x_msg_data );
2434 IF (G_IS_DEBUG_STATEMENT_ON = true)
2435 THEN
2436 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT,
2437 G_MODULE,
2438 ' End of Procedure =>OKL_PAY_RECON_PVT.CREATE_ACCOUNTING');
2439 END IF;
2440
2441 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : CREATE_ACCOUNTING : END ');
2442
2443 EXCEPTION
2444 WHEN Fnd_Api.G_EXC_ERROR THEN
2445 ROLLBACK TO CREATE_ACCOUNTING;
2446 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2447 x_msg_count := l_msg_count ;
2448 x_msg_data := l_msg_data ;
2449 Fnd_Msg_Pub.count_and_get(
2450 p_count => x_msg_count
2451 ,p_data => x_msg_data);
2452 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
2453 ROLLBACK TO CREATE_ACCOUNTING;
2454 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2455 x_msg_count := l_msg_count ;
2456 x_msg_data := l_msg_data ;
2457 Fnd_Msg_Pub.count_and_get(
2458 p_count => x_msg_count
2459 ,p_data => x_msg_data);
2460 WHEN OTHERS THEN
2461 ROLLBACK TO CREATE_ACCOUNTING;
2462 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2463 x_msg_count := l_msg_count ;
2464 x_msg_data := l_msg_data ;
2465 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_RECON_PVT','CREATE_ACCOUNTING');
2466 Fnd_Msg_Pub.count_and_get(
2467 p_count => x_msg_count
2468 ,p_data => x_msg_data);
2469
2470 END CREATE_ACCOUNTING;
2471
2472 PROCEDURE SUBMIT_INVOICES (p_cam_tbl IN cure_amount_tbl,
2473 p_report_id IN NUMBER,
2474 p_invoice_date IN DATE,
2475 x_return_status OUT NOCOPY VARCHAR2,
2476 x_msg_count OUT NOCOPY NUMBER,
2477 x_msg_data OUT NOCOPY VARCHAR2)
2478 IS
2479
2480 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2481 l_msg_count NUMBER ;
2482 l_msg_data VARCHAR2(32627);
2483 l_message VARCHAR2(32627);
2484 l_api_name CONSTANT VARCHAR2(50) := 'SUBMIT_INVOICES';
2485 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'|| l_api_name;
2486
2487 BEGIN
2488
2489 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : SUBMIT_INVOICES : START ');
2490
2491 IF (G_DEBUG_ENABLED = 'Y') THEN
2492 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2493 END IF;
2494
2495 SAVEPOINT SUBMIT_INVOICES;
2496 IF (G_IS_DEBUG_STATEMENT_ON = true)
2497 THEN
2498 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'start submit_cure_invoices');
2499 END IF;
2500 x_return_status := FND_API.G_RET_STS_SUCCESS;
2501
2502 UPDATE_TAI_TIL(p_cam_tbl =>p_cam_tbl,
2503 p_invoice_date =>p_invoice_date,
2504 p_trx_status =>'SUBMITTED',
2505 x_return_status =>l_return_status,
2506 x_msg_count =>l_msg_count,
2507 x_msg_data =>l_msg_data);
2508
2509 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : SUBMIT_INVOICES : UPDATE_TAI_TIL : '||l_return_status);
2510
2511 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS)
2512 THEN
2513 Get_Messages (l_msg_count,l_message);
2514 IF (G_IS_DEBUG_STATEMENT_ON = true)
2515 THEN
2516 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT,
2517 G_MODULE,
2518 'Error in updating cure amounts:'||l_message);
2519 END IF;
2520 raise FND_API.G_EXC_ERROR;
2521 END IF;
2522
2523 CREATE_ACCOUNTING(p_cam_tbl =>p_cam_tbl,
2524 x_return_status =>l_return_status,
2525 x_msg_count =>l_msg_count,
2526 x_msg_data =>l_msg_data);
2527
2528 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : SUBMIT_INVOICES : CREATE_ACCOUNTING : '||l_return_status);
2529
2530 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS)
2531 THEN
2532 Get_Messages (l_msg_count,l_message);
2533 IF (G_IS_DEBUG_STATEMENT_ON = true)
2534 THEN
2535 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT,
2536 G_MODULE,
2537 'Error in Creating distributions'||l_message);
2538 END IF;
2539 raise FND_API.G_EXC_ERROR;
2540 END IF;
2541
2542 UPDATE_CAM (p_cam_tbl =>p_cam_tbl,
2543 p_report_id =>p_report_id,
2544 x_return_status =>l_return_status,
2545 x_msg_count =>l_msg_count,
2546 x_msg_data =>l_msg_data);
2547
2548 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : SUBMIT_INVOICES : UPDATE_CAM : '||l_return_status);
2549
2550 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS)
2551 THEN
2552 Get_Messages (l_msg_count,l_message);
2553 IF (G_IS_DEBUG_STATEMENT_ON = true)
2554 THEN
2555 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT,
2556 G_MODULE,
2557 'Error in updating cure amounts:'||l_message);
2558 END IF;
2559 raise FND_API.G_EXC_ERROR;
2560 END IF;
2561
2562 UPDATE_CRT (p_report_id =>p_report_id,
2563 p_status =>'ACCEPTANCE_COMPLETED',
2564 x_return_status =>l_return_status,
2565 x_msg_count =>l_msg_count,
2566 x_msg_data =>l_msg_data);
2567
2568 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : SUBMIT_INVOICES : UPDATE_CRT : '||l_return_status);
2569
2570 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS)
2571 THEN
2572 Get_Messages (l_msg_count,l_message);
2573 IF (G_IS_DEBUG_STATEMENT_ON = true)
2574 THEN
2575 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error is :' ||l_message);
2576 END IF;
2577 raise FND_API.G_EXC_ERROR;
2578 ELSE
2579 IF (G_IS_DEBUG_STATEMENT_ON = true)
2580 THEN
2581 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Updated cure reports table ');
2582 END IF;
2583 END IF;
2584
2585 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2586 p_data => x_msg_data );
2587
2588 IF (G_IS_DEBUG_STATEMENT_ON = true)
2589 THEN
2590 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT,
2591 G_MODULE,
2592 ' End of Procedure => OKL_PAY_RECON_PVT.submit_Cure_invoices');
2593
2594 END IF;
2595
2596 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : SUBMIT_INVOICES : END ');
2597
2598 EXCEPTION
2599 WHEN Fnd_Api.G_EXC_ERROR THEN
2600 ROLLBACK TO SUBMIT_INVOICES;
2601 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2602 x_msg_count := l_msg_count ;
2603 x_msg_data := l_msg_data ;
2604 Fnd_Msg_Pub.count_and_get(
2605 p_count => x_msg_count
2606 ,p_data => x_msg_data);
2607 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
2608 ROLLBACK TO SUBMIT_INVOICES;
2609 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2610 x_msg_count := l_msg_count ;
2611 x_msg_data := l_msg_data ;
2612 Fnd_Msg_Pub.count_and_get(
2613 p_count => x_msg_count
2614 ,p_data => x_msg_data);
2615 WHEN OTHERS THEN
2616 ROLLBACK TO SUBMIT_INVOICES;
2617 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2618 x_msg_count := l_msg_count ;
2619 x_msg_data := l_msg_data ;
2620 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_RECON_PVT','SUBMIT_INVOICES');
2621 Fnd_Msg_Pub.count_and_get(
2622 p_count => x_msg_count
2623 ,p_data => x_msg_data);
2624
2625 END SUBMIT_INVOICES;
2626
2627 PROCEDURE UPDATE_CURE_INVOICE (
2628 p_api_version IN NUMBER,
2629 p_init_msg_list IN VARCHAR2 DEFAULT fnd_api.G_FALSE,
2630 p_commit IN VARCHAR2 DEFAULT fnd_api.G_FALSE,
2631 p_report_id IN NUMBER,
2632 p_invoice_date IN DATE,
2633 p_cam_tbl IN cure_amount_tbl,
2634 p_operation IN VARCHAR2,
2635 x_return_status OUT NOCOPY VARCHAR2,
2636 x_msg_count OUT NOCOPY NUMBER,
2637 x_msg_data OUT NOCOPY VARCHAR2) IS
2638
2639 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
2640 l_msg_count NUMBER;
2641 l_msg_data VARCHAR2(32627);
2642 l_message VARCHAR2(32627);
2643
2644 BEGIN
2645
2646 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_CURE_INVOICE : START ');
2647
2648 IF (G_DEBUG_ENABLED = 'Y')
2649 THEN
2650 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2651 END IF;
2652
2653 SAVEPOINT UPDATE_CURE_INVOICE_PVT;
2654 x_return_status := FND_API.G_RET_STS_SUCCESS;
2655
2656 IF p_operation ='UPDATE'
2657 THEN
2658 IF (G_IS_DEBUG_STATEMENT_ON = true)
2659 THEN
2660 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'Operation is Update');
2661 END IF;
2662 Update_invoices (p_cam_tbl =>p_cam_tbl,
2663 p_report_id =>p_report_id,
2664 p_invoice_date =>p_invoice_date,
2665 x_return_status =>l_return_status,
2666 x_msg_count =>l_msg_count,
2667 x_msg_data =>l_msg_data);
2668
2669 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_CURE_INVOICE : Update_invoices : '||l_return_status);
2670
2671 ELSE
2672 IF (G_IS_DEBUG_STATEMENT_ON = true)
2673 THEN
2674 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'Operation is Submit');
2675 END IF;
2676 submit_invoices(p_cam_tbl =>p_cam_tbl,
2677 p_report_id =>p_report_id,
2678 --p_invoice_date =>SYSDATE,
2679 p_invoice_date =>p_invoice_date,
2680 x_return_status =>l_return_status,
2681 x_msg_count =>l_msg_count,
2682 x_msg_data =>l_msg_data);
2683
2684 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_CURE_INVOICE : submit_invoices : '||l_return_status);
2685 END IF;
2686
2687 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS)
2688 THEN
2689 Get_Messages (l_msg_count,l_message);
2690 IF (G_IS_DEBUG_STATEMENT_ON = true)
2691 THEN
2692 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error is :' ||l_message);
2693 END IF;
2694 raise FND_API.G_EXC_ERROR;
2695 ELSE
2696 IF (G_IS_DEBUG_STATEMENT_ON = true)
2697 THEN
2698 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Invoices updated');
2699 END IF;
2700 END IF;
2701
2702 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2703 p_data => x_msg_data);
2704
2705 IF (G_IS_DEBUG_STATEMENT_ON = true)
2706 THEN
2707 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT,
2708 G_MODULE,
2709 ' End of Procedure => OKL_PAY_RECON_PVT.update_Cure_invoices');
2710 END IF;
2711
2712 okl_debug_pub.logmessage('OKL_CURE_RECON_PVT : UPDATE_CURE_INVOICE : END ');
2713
2714 EXCEPTION
2715 WHEN Fnd_Api.G_EXC_ERROR THEN
2716 ROLLBACK TO UPDATE_CURE_INVOICE_PVT;
2717 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2718 x_msg_count := l_msg_count ;
2719 x_msg_data := l_msg_data ;
2720 Fnd_Msg_Pub.count_and_get(
2721 p_count => x_msg_count
2722 ,p_data => x_msg_data);
2723 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
2724 ROLLBACK TO UPDATE_CURE_INVOICE_PVT;
2725 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2726 x_msg_count := l_msg_count ;
2727 x_msg_data := l_msg_data ;
2728 Fnd_Msg_Pub.count_and_get(
2729 p_count => x_msg_count
2730 ,p_data => x_msg_data);
2731 WHEN OTHERS THEN
2732 ROLLBACK TO UPDATE_CURE_INVOICE_PVT;
2733 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2734 x_msg_count := l_msg_count ;
2735 x_msg_data := l_msg_data ;
2736 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_CURE_RECON_PVT','UPDATE_CURE_INVOICE');
2737 Fnd_Msg_Pub.count_and_get(
2738 p_count => x_msg_count
2739 ,p_data => x_msg_data);
2740
2741 END UPDATE_CURE_INVOICE;
2742
2743 END OKL_CURE_RECON_PVT;