[Home] [Help]
PACKAGE BODY: APPS.OKL_PAY_CURE_REFUNDS_PVT
Source
1 PACKAGE BODY OKL_PAY_CURE_REFUNDS_PVT as
2 /* $Header: OKLRPCRB.pls 120.16 2007/09/20 16:41:19 cklee noship $ */
3
4 G_MODULE VARCHAR2(255) := 'okl.cure.refund.OKL_PAY_CURE_REFUNDS_PVT';
5 G_DEBUG_ENABLED CONSTANT VARCHAR2(10) := OKL_DEBUG_PUB.CHECK_LOG_ENABLED;
6 G_IS_DEBUG_STATEMENT_ON BOOLEAN;
7
8
9 --private procedure
10 /**Name AddMissingArgMsg
11 **Appends to a message the api name, parameter name and parameter Value
12 */
13
14 PROCEDURE AddMissingArgMsg
15 ( p_api_name IN VARCHAR2,
16 p_param_name IN VARCHAR2 )IS
17 BEGIN
18 fnd_message.set_name('OKL', 'OKL_API_ALL_MISSING_PARAM');
19 fnd_message.set_token('API_NAME', p_api_name);
20 fnd_message.set_token('MISSING_PARAM', p_param_name);
21 fnd_msg_pub.add;
22
23 END AddMissingArgMsg;
24
25 /**Name AddFailMsg
26 **Appends to a message the name of the object and
27 ** the operation (insert, update ,delete)
28 */
29
30 PROCEDURE AddfailMsg
31 ( p_object IN VARCHAR2,
32 p_operation IN VARCHAR2 ) IS
33
34 BEGIN
35 fnd_message.set_name('OKL', 'OKL_FAILED_OPERATION');
36 fnd_message.set_token('OBJECT', p_object);
37 fnd_message.set_token('OPERATION', p_operation);
38 fnd_msg_pub.add;
39
40 END AddfailMsg;
41
42
43 PROCEDURE Get_Messages (
44 p_message_count IN NUMBER,
45 x_message OUT NOCOPY VARCHAR2) IS
46
47
48 l_msg_list VARCHAR2(32627) := '';
49 l_temp_msg VARCHAR2(32627);
50 l_appl_short_name VARCHAR2(50) ;
51 l_message_name VARCHAR2(50) ;
52 l_id NUMBER;
53 l_message_num NUMBER;
54 l_msg_count NUMBER;
55 l_msg_data VARCHAR2(32627);
56
57 Cursor Get_Appl_Id (x_short_name VARCHAR2) IS
58 SELECT application_id
59 FROM fnd_application_vl
60 WHERE application_short_name = x_short_name;
61
62 Cursor Get_Message_Num (x_msg VARCHAR2, x_id NUMBER, x_lang_id NUMBER) IS
63 SELECT msg.message_number
64 FROM fnd_new_messages msg, fnd_languages_vl lng
65 WHERE msg.message_name = x_msg
66 and msg.application_id = x_id
67 and lng.LANGUAGE_CODE = msg.language_code
68 and lng.language_id = x_lang_id;
69
70 BEGIN
71 FOR l_count in 1..p_message_count LOOP
72
73 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_true);
74 fnd_message.parse_encoded(l_temp_msg, l_appl_short_name, l_message_name);
75 OPEN Get_Appl_Id (l_appl_short_name);
76 FETCH Get_Appl_Id into l_id;
77 CLOSE Get_Appl_Id;
78
79 l_message_num := NULL;
80 IF l_id is not NULL
81 THEN
82 OPEN Get_Message_Num (l_message_name, l_id,
83 to_number(NVL(FND_PROFILE.Value('LANGUAGE'), '0')));
84 FETCH Get_Message_Num into l_message_num;
85 CLOSE Get_Message_Num;
86 END IF;
87
88 l_temp_msg := fnd_msg_pub.get(fnd_msg_pub.g_previous, fnd_api.g_true);
89
90 IF NVL(l_message_num, 0) <> 0
91 THEN
92 l_temp_msg := 'APP-' || to_char(l_message_num) || ': ';
93 ELSE
94 l_temp_msg := NULL;
95 END IF;
96
97 IF l_count = 1
98 THEN
99 l_msg_list := l_msg_list || l_temp_msg ||
100 fnd_msg_pub.get(fnd_msg_pub.g_first, fnd_api.g_false);
101 ELSE
102 l_msg_list := l_msg_list || l_temp_msg ||
103 fnd_msg_pub.get(fnd_msg_pub.g_next, fnd_api.g_false);
104 END IF;
105
106 l_msg_list := l_msg_list || '';
107
108 END LOOP;
109
110 x_message := l_msg_list;
111
112
113 END Get_Messages;
114
115
116
117 PROCEDURE create_refund(
118 p_pay_cure_refunds_rec IN pay_cure_refunds_rec_type
119 ,x_cure_refund_id OUT NOCOPY NUMBER
120 ,x_return_status OUT NOCOPY VARCHAR2
121 ,x_msg_count OUT NOCOPY NUMBER
122 ,x_msg_data OUT NOCOPY VARCHAR2
123 )IS
124
125 l_init_msg_list VARCHAR2(1);
126 l_return_status VARCHAR2(1);
127 l_msg_count NUMBER ;
128 l_msg_data VARCHAR2(32627);
129 l_message VARCHAR2(32627);
130
131
132 l_cure_refund_id okl_cure_refunds.cure_refund_id%type;
133 l_cure_refund_number okl_cure_refunds.refund_number%type;
134 l_api_name CONSTANT VARCHAR2(50) := 'CREATE_REFUND';
135 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'
136 || l_api_name;
137
138 l_okl_application_id NUMBER(3) := 540;
139 l_document_category VARCHAR2(100):= 'OKL Lease Pay Invoices';
140 lX_dbseqnm VARCHAR2(2000):= '';
141 lX_dbseqid NUMBER(38):= NULL;
142
143 -----------------------------------------------------------
144 -- Declare records: Payable Invoice Headers, Lines and Distributions
145 ------------------------------------------------------------
146 lp_tapv_rec okl_tap_pvt.tapv_rec_type;
147 lx_tapv_rec okl_tap_pvt.tapv_rec_type;
148 lp_tplv_rec okl_tpl_pvt.tplv_rec_type;
149 lx_tplv_rec okl_tpl_pvt.tplv_rec_type;
150
151 /* ankushar 22-JAN-2007
152 added table definitions
153 start changes
154 */
155 lp_tplv_tbl okl_tpl_pvt.tplv_tbl_type;
156 lx_tplv_tbl okl_tpl_pvt.tplv_tbl_type;
157 /* ankushar end changes*/
158
159 l_tmpl_identify_rec Okl_Account_Dist_Pvt.TMPL_IDENTIFY_REC_TYPE;
160 l_dist_info_rec Okl_Account_Dist_Pvt.dist_info_REC_TYPE;
161 l_ctxt_val_tbl okl_execute_formula_pvt.ctxt_val_tbl_type;
162 l_acc_gen_primary_key_tbl Okl_Account_Generator_Pvt.primary_key_tbl;
163 l_template_tbl OKL_TMPT_SET_PUB.avlv_tbl_type;
164 l_amount_tbl Okl_Account_Dist_Pvt.AMOUNT_TBL_TYPE;
165
166 lp_crfv_rec okl_crf_pvt.crfv_rec_type;
167 lx_crfv_rec okl_crf_pvt.crfv_rec_type;
168
169
170
171 CURSOR org_id_csr ( p_khr_id NUMBER ) IS
172 SELECT chr.authoring_org_id
173 FROM okc_k_headers_b chr
174 WHERE id = p_khr_id;
175
176 CURSOR sob_csr ( p_org_id NUMBER ) IS
177 SELECT hru.set_of_books_id
178 FROM HR_OPERATING_UNITS HRU
179 WHERE ORGANIZATION_ID = p_org_id;
180
181 CURSOR try_id_csr IS
182 SELECT id
183 FROM okl_trx_types_tl
184 WHERE name = 'Disbursement'
185 AND LANGUAGE = USERENV('LANG');
186
187 /* --User Defined Stream fix
188 CURSOR stream_type_csr IS
189 SELECT id
190 FROM okl_strm_type_tl
191 WHERE name = 'CURE'
192 AND LANGUAGE = USERENV('LANG');
193 */
194 x_primary_sty_id number;
195 l_khr_id number;
196
197 cursor chk_refund_number(p_refund_number IN VARCHAR2) IS
198 select refund_number
199 from okl_cure_refunds
200 where refund_number =p_refund_number;
201
202 CURSOR c_app
203 IS
204 select a.application_id
205 from FND_APPLICATION a
206 where APPLICATION_SHORT_NAME = 'OKL';
207
208
209 BEGIN
210
211 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund : START ');
212
213 IF (G_DEBUG_ENABLED = 'Y') THEN
214 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
215 END IF;
216
217 SAVEPOINT CREATE_REFUND;
218 -- Initialize message list if p_init_msg_list is set to TRUE.
219
220 FND_MSG_PUB.initialize;
221
222
223 /*** Logic for refunds ********
224 ** 1) Invoke the common disbursement API for ap header and line creation
225 ** 2) create accounting record
226 ** 3) create cure refund record
227 **/
228
229 -- STEP 1
230 --populate the ap invoice header table (okl_trx_ap_invoices_b)
231 IF PG_DEBUG < 11 THEN
232 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
233 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'before creating TAP record ');
234 END IF;
235 END IF;
236
237 x_return_status := FND_API.G_RET_STS_SUCCESS;
238
239 lp_tapv_rec.org_id := NULL;
240 OPEN org_id_csr ( p_pay_cure_refunds_rec.chr_id) ;
241 FETCH org_id_csr INTO lp_tapv_rec.org_id;
242 CLOSE org_id_csr;
243
244 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund : lp_tapv_rec.org_id : '||lp_tapv_rec.org_id);
245
246 IF (lp_tapv_rec.org_id IS NULL) THEN
247 AddMissingArgMsg(
248 p_api_name => l_api_name_full,
249 p_param_name => 'org_id' );
250 RAISE FND_API.G_EXC_ERROR;
251 ELSE
252 IF PG_DEBUG < 11 THEN
253 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
254 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lp_tapv_rec.org_id '||
255 lp_tapv_rec.org_id);
256 END IF;
257 END IF;
258 END IF;
259
260 OPEN sob_csr ( lp_tapv_rec.org_id );
261 FETCH sob_csr INTO lp_tapv_rec.set_of_books_id;
262 CLOSE sob_csr;
263
264 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund : lp_tapv_rec.set_of_books_id : '||lp_tapv_rec.set_of_books_id);
265
266 IF (lp_tapv_rec.set_of_books_id IS NULL) THEN
267 AddMissingArgMsg(
268 p_api_name => l_api_name_full,
269 p_param_name => 'set_of_books_id' );
270 RAISE FND_API.G_EXC_ERROR;
271 ELSE
272 IF PG_DEBUG < 11 THEN
273 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
274 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lp_tapv_rec.set_of_books_id'||
275 lp_tapv_rec.set_of_books_id);
276 END IF;
277 END IF;
278 END IF;
279
280
281 lp_tapv_rec.try_id := NULL;
282 OPEN try_id_csr;
283 FETCH try_id_csr INTO lp_tapv_rec.try_id;
284 CLOSE try_id_csr;
285 IF (lp_tapv_rec.try_id IS NULL) THEN
286 AddMissingArgMsg(
287 p_api_name => l_api_name_full,
288 p_param_name => 'try_id' );
289 RAISE FND_API.G_EXC_ERROR;
290 ELSE
291 IF PG_DEBUG < 11 THEN
292 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
293 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lp_tapv_rec.try_id'||
294 lp_tapv_rec.try_id);
295 END IF;
296 END IF;
297 END IF;
298 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund : lp_tapv_rec.try_id : '||lp_tapv_rec.try_id);
299 lp_tapv_rec.invoice_number := NULL;
300
301 --
302 -- display specific application error if 'OKL Lease Pay Invoices'
303 -- has not been setup or setup incorrectly
304 --
305
306 OPEN c_app;
307 FETCH c_app INTO l_okl_application_id;
308 CLOSE c_app;
309 l_okl_application_id := nvl(l_okl_application_id,540);
310
311 BEGIN
312 lp_tapv_rec.invoice_number := fnd_seqnum.get_next_sequence
313 (appid => l_okl_application_id,
314 cat_code => l_document_category,
315 sobid => lp_tapv_rec.set_of_books_id,
316 met_code => 'A',
317 trx_date => SYSDATE,
318 dbseqnm => lx_dbseqnm,
319 dbseqid => lx_dbseqid);
320
321 EXCEPTION
322 WHEN OTHERS THEN
323 IF SQLCODE = 100 THEN
324 fnd_message.set_name('OKL', 'OKL_PAY_INV_SEQ_CHECK');
325 fnd_msg_pub.add;
326 RAISE FND_API.G_EXC_ERROR;
327 END IF;
328 END;
329
330 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund : lp_tapv_rec.invoice_number : '||lp_tapv_rec.invoice_number);
331
332 IF (lp_tapv_rec.invoice_number IS NULL) THEN
333 AddMissingArgMsg(
334 p_api_name => l_api_name_full,
335 p_param_name => 'invoice_number' );
336 RAISE FND_API.G_EXC_ERROR;
337 ELSE
338 IF PG_DEBUG < 11 THEN
339 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
340 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lp_tapv_rec.invoice_number'||
341 lp_tapv_rec.invoice_number);
342 END IF;
343 END IF;
344
345 END IF;
346
347
348 lp_tapv_rec.vendor_invoice_number := lp_tapv_rec.invoice_number;
349 -- sjalasut, commented the assignment of khr_id below. khr_id would henceforth referred
350 -- in l_tplv_rec (internal disbursements lines table). changes made as part of OKLR12B
351 -- disbursements project.
352 lp_tapv_rec.khr_id := p_pay_cure_refunds_rec.chr_id; -- cklee 09/20/2007
353 -- lp_tapv_rec.khr_id := NULL;
354
355 lp_tapv_rec.ipvs_id := p_pay_cure_refunds_rec.vendor_site_id;
356 lp_tapv_rec.ippt_id := p_pay_cure_refunds_rec.pay_terms;
357 lp_tapv_rec.payment_method_code := p_pay_cure_refunds_rec.payment_method_code;
358 lp_tapv_rec.currency_code := p_pay_cure_refunds_rec.currency;
359 lp_tapv_rec.date_entered := sysdate;
360 lp_tapv_rec.date_invoiced := p_pay_cure_refunds_rec.invoice_date;
361 lp_tapv_rec.amount := p_pay_cure_refunds_rec.refund_amount;
362 lp_tapv_rec.trx_status_code := 'PENDINGI';
363 lp_tapv_rec.object_version_number := 1;
364 --20-NOV-2006 ANSETHUR R12B - LEGAL ENTITY UPTAKE PROJECT
365 lp_tapv_rec.legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(p_pay_cure_refunds_rec.chr_id);
366
367 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund : lp_tapv_rec.amount : '||lp_tapv_rec.amount);
368 -- not sure of these 4 variable
369 /* invoice_type,
370 invoice_category_code,
371 pay_group_lookup_code,
372 nettable_yn,
373 if invoice_type is credit then amount is -ve
374 */
375
376 -- STEP 2
377 --populate the line table (okl_txl_ap_inv_lns_b)
378 -- sjalasut, added code to have khr_id populated from the cursor p_pay_cure_refunds_rec
379 -- changes made as part of OKLR12B disbursements project
380 lp_tplv_rec.khr_id := p_pay_cure_refunds_rec.chr_id;
381
382 lp_tplv_rec.amount := lp_tapv_rec.amount;
383 lp_tplv_rec.inv_distr_line_code := 'MANUAL';
384 lp_tplv_rec.line_number := 1;
385 lp_tplv_rec.org_id := lp_tapv_rec.org_id;
386 lp_tplv_rec.disbursement_basis_code := 'BILL_DATE';
387 lp_tplv_rec.object_version_number := 1;
388
389
390 /* what about other columns
391 sty_id,
392 * is disbursement_basis_code= 'bill_date'
393 */
394
395 /*
396 FOR stream_rec IN stream_type_csr
397 LOOP
398 lp_tplv_rec.sty_id := stream_rec.id;
399 IF PG_DEBUG < 11 THEN
400 okl_debug_pub.logmessage ('sty_id ' ||stream_rec.id);
401 END IF;
402 END LOOP;
403 */
404 -- sjalasut, modified the below assignment to have khr_id populated from the lp_tplv_rec
405 -- changes made as part of OKLR12B disbursements project
406 -- l_khr_id := lp_tapv_rec.khr_id;
407 l_khr_id := lp_tplv_rec.khr_id;
408
409 OKL_STREAMS_UTIL.get_primary_stream_type(
410 p_khr_id => l_khr_id,
411 p_primary_sty_purpose => 'CURE',
412 x_return_status => l_return_status,
413 x_primary_sty_id => x_primary_sty_id
414 );
415
416 lp_tplv_rec.sty_id := x_primary_sty_id;
417
418 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS or x_primary_sty_id is null) THEN
419 Get_Messages (l_msg_count,l_message);
420 IF PG_DEBUG < 11 THEN
421 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
422 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
423 END IF;
424 END IF;
425 raise FND_API.G_EXC_ERROR;
426
427 ELSE
428
429 IF PG_DEBUG < 11 THEN
430 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
431 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'sty_id ' ||x_primary_sty_id);
432 END IF;
433 END IF;
434
435 END IF;
436
437 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund : lp_tplv_rec.sty_id : '||lp_tplv_rec.sty_id);
438 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund : lp_tplv_rec.amount : '||lp_tplv_rec.amount);
439 --set error message,so this will be prefixed before the
440 --actual message, so it makes more sense than displaying an
441 -- OKL message.
442 AddfailMsg(
443 p_object => 'RECORD IN OKL_TXL_AP_INV_LNS_B ',
444 p_operation => 'CREATE' );
445
446 /* ankushar 23-JAN-2007
447 Call to the common Disbursement API
448 start changes
449 */
450
451 -- Add tpl_rec to table
452 -- start:
453 --cklee 06/04/2007 Reverse the original code back due to the duplicated
454 -- accounting entries will be created
455 /*
456 lp_tplv_tbl(1) := lp_tplv_rec;
457
458 --Call the commong disbursement API to create transactions
459 Okl_Create_Disb_Trans_Pvt.create_disb_trx(
460 p_api_version => 1.0
461 ,p_init_msg_list => 'F'
462 ,x_return_status => x_return_status
463 ,x_msg_count => x_msg_count
464 ,x_msg_data => x_msg_data
465 ,p_tapv_rec => lp_tapv_rec
466 ,p_tplv_tbl => lp_tplv_tbl
467 ,x_tapv_rec => lx_tapv_rec
468 ,x_tplv_tbl => lx_tplv_tbl);
469 */
470 OKL_TRX_AP_INVOICES_PUB.INSERT_TRX_AP_INVOICES(
471 p_api_version => 1.0,
472 p_init_msg_list => 'F',
473 x_return_status => x_return_status,
474 x_msg_count => x_msg_count,
475 x_msg_data => x_msg_data,
476 p_tapv_rec => lp_tapv_rec,
477 x_tapv_rec => lx_tapv_rec);
478 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund : OKL_TRX_AP_INVOICES_PUB.INSERT_TRX_AP_INVOICES : '||x_return_status);
479 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
480 Get_Messages (l_msg_count,l_message);
481 IF PG_DEBUG <11 THEN
482 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
483 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
484 END IF;
485 END IF;
486 raise FND_API.G_EXC_ERROR;
487 ELSE
488 IF PG_DEBUG < 11 THEN
489 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
490 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lx_tapv_rec.id'
491 ||lx_tapv_rec.id);
492 END IF;
493 END IF;
494 FND_MSG_PUB.initialize;
495 END IF;
496
497 lp_tplv_rec.tap_id := lx_tapv_rec.id;
498
499 OKL_TXL_AP_INV_LNS_PUB.INSERT_TXL_AP_INV_LNS(
500 p_api_version => 1.0,
501 p_init_msg_list => 'F',
502 x_return_status => x_return_status,
503 x_msg_count => x_msg_count,
504 x_msg_data => x_msg_data,
505 p_tplv_rec => lp_tplv_rec,
506 x_tplv_rec => lx_tplv_rec);
507 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund : OKL_TXL_AP_INV_LNS_PUB.INSERT_TXL_AP_INV_LNS : '||x_return_status);
508
509 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
510 Get_Messages (l_msg_count,l_message);
511 IF PG_DEBUG <11 THEN
512 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
513 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
514 END IF;
515 END IF;
516 raise FND_API.G_EXC_ERROR;
517 ELSE
518 IF PG_DEBUG < 11 THEN
519 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
520 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lx_tplv_rec.id'
521 ||lx_tplv_rec.id);
522 END IF;
523 END IF;
524 FND_MSG_PUB.initialize;
525 END IF;
526 -- end:
527 --cklee 06/04/2007 Reverse the original code back due to the duplicated
528 -- accounting entries will be created
529 /* ankushar end changes */
530
531 --Step 4
532 --create cure refund record
533 lp_crfv_rec.refund_number := p_pay_cure_refunds_rec.refund_number;
534 lp_crfv_rec.chr_id := p_pay_cure_refunds_rec.chr_id;
535 lp_crfv_rec.vendor_site_id := p_pay_cure_refunds_rec.vendor_site_id;
536 lp_crfv_rec.disbursement_amount := p_pay_cure_refunds_rec.refund_amount;
537 lp_crfv_rec.total_refund_due := p_pay_cure_refunds_rec.refund_amount_due;
538 lp_crfv_rec.refund_date := p_pay_cure_refunds_rec.invoice_date;
539 lp_crfv_rec.object_version_number := 1;
540 lp_crfv_rec.tap_id := lx_tapv_rec.id;
541 lp_crfv_rec.cure_refund_header_id :=p_pay_cure_refunds_rec.refund_header_id;
542
543 --set error message,so this will be prefixed before the
544 --actual message, so it makes more sense than displaying an
545 -- OKL message.
546 AddfailMsg(
547 p_object => 'RECORD IN OKL_CURE_REFUNDS ',
548 p_operation => 'CREATE' );
549
550 OKL_cure_refunds_pub.insert_cure_refunds(
551 p_api_version => 1.0
552 ,p_init_msg_list => 'F'
553 ,x_return_status => l_return_status
554 ,x_msg_count => l_msg_count
555 ,x_msg_data => l_msg_data
556 ,p_crfv_rec => lp_crfv_rec
557 ,x_crfv_rec => lx_crfv_rec);
558
559 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund : OKL_cure_refunds_pub.insert_cure_refunds : '||l_return_status);
560
561 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
562 Get_Messages (l_msg_count,l_message);
563 IF PG_DEBUG < 11 THEN
564 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
565 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
566 END IF;
567 END IF;
568 raise FND_API.G_EXC_ERROR;
569 ELSE
570 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
571 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lx_crfv_rec.cure_refund_id'
572 ||lx_crfv_rec.cure_refund_id);
573 END IF;
574 x_cure_refund_id :=lx_crfv_rec.cure_refund_id;
575 END IF;
576
577 IF x_return_status =FND_API.G_RET_STS_SUCCESS THEN
578 FND_MSG_PUB.initialize;
579 END IF;
580
581 FND_MSG_PUB.Count_And_Get
582 ( p_count => x_msg_count,
583 p_data => x_msg_data
584 );
585
586 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
587 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'* End of Procedure'||
588 '=>OKL_PAY_CURE_REFUNDS_PVT.'||
589 'create_refund *');
590
591 END IF;
592
593 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund : START ');
594
595 EXCEPTION
596
597 WHEN Fnd_Api.G_EXC_ERROR THEN
598 ROLLBACK TO CREATE_REFUND;
599 x_return_status := Fnd_Api.G_RET_STS_ERROR;
600 x_msg_count := l_msg_count ;
601 x_msg_data := l_msg_data ;
602 Fnd_Msg_Pub.count_and_get(
603 p_count => x_msg_count
604 ,p_data => x_msg_data);
605 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
606 ROLLBACK TO CREATE_REFUND;
607 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
608 x_msg_count := l_msg_count ;
609 x_msg_data := l_msg_data ;
610 Fnd_Msg_Pub.count_and_get(
611 p_count => x_msg_count
612 ,p_data => x_msg_data);
613 WHEN OTHERS THEN
614 ROLLBACK TO CREATE_REFUND;
615 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
616 x_msg_count := l_msg_count ;
617 x_msg_data := l_msg_data ;
618 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_PAY_CURE_REFUNDS_PVT','CREATE_REFUND');
619 Fnd_Msg_Pub.count_and_get(
620 p_count => x_msg_count
621 ,p_data => x_msg_data);
622
623 END create_refund;
624
625 PROCEDURE check_contract(p_pay_cure_refunds_rec IN pay_cure_refunds_rec_type
626 ,x_return_status OUT NOCOPY VARCHAR2
627 ,x_contract_number OUT NOCOPY VARCHAR2) IS
628
629 l_id1 VARCHAR2(40);
630 l_id2 VARCHAR2(200);
631 l_rule_value VARCHAR2(2000);
632 l_days_allowed NUMBER :=0;
633 l_program_id okl_k_headers.khr_id%TYPE;
634 l_return_status VARCHAR2(1):= FND_API.G_RET_STS_SUCCESS;
635
636 cursor c_program_id (p_contract_id IN NUMBER ) IS
637 select khr_id from okl_k_headers
638 where id= p_contract_id;
639
640
641
642 -- ASHIM CHANGE - START
643
644
645 /*CURSOR c_amount_past_due(p_contract_id IN NUMBER,
646 p_grace_days IN NUMBER) IS
647 SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
648 FROM okl_cnsld_ar_strms_b ocas
649 ,ar_payment_schedules_all aps
650 WHERE ocas.khr_id = p_contract_id
651 AND ocas.receivables_invoice_id = aps.customer_trx_id
652 AND aps.class IN ('INV','CM')
653 AND (aps.due_date + p_grace_days) < sysdate
654 AND NVL(aps.amount_due_remaining, 0) > 0;*/
655
656 CURSOR c_amount_past_due(p_contract_id IN NUMBER,
657 p_grace_days IN NUMBER) IS
658 SELECT SUM(NVL(aps.amount_due_remaining, 0)) past_due_amount
659 FROM okl_bpd_tld_ar_lines_v ocas
660 ,ar_payment_schedules_all aps
661 WHERE ocas.khr_id = p_contract_id
662 AND ocas.customer_trx_id = aps.customer_trx_id
663 AND aps.class IN ('INV','CM')
664 AND (aps.due_date + p_grace_days) < sysdate
665 AND NVL(aps.amount_due_remaining, 0) > 0;
666
667
668 -- ASHIM CHANGE - END
669
670
671
672 TYPE c_getcontractsCurTyp IS REF CURSOR;
673 c_getcontracts c_getcontractsCurTyp; -- declare cursor variable
674
675 l_contract_id okl_cure_refunds_dtls_uv.contract_id%TYPE;
676 l_contract_number okl_cure_refunds_dtls_uv.contract_number%TYPE;
677 l_idx INTEGER;
678 l_amount_past_due NUMBER;
679 BEGIN
680
681 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: check_contract : START ');
682
683 IF (G_DEBUG_ENABLED = 'Y') THEN
684 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
685 END IF;
686
687 x_return_status := FND_API.G_RET_STS_SUCCESS;
688 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
689 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'Start of check_contract' );
690
691 END IF;
692 IF p_pay_cure_refunds_rec.chr_id is not null THEN
693 -- then check for this contract only
694 --start changed by abhsaxen for Bug#6174484
695 OPEN c_getcontracts
696 FOR
697 SELECT st.khr_id contract_id,
698 cn.contract_number
699 FROM okl_xtl_sell_invs_v xls,
700 okl_txl_ar_inv_lns_v til,
701 okl_trx_ar_invoices_v tai,
702 okc_k_headers_b cn,
703 ar_payment_schedules_all ps,
704 ar_receivable_applications_all arapp,
705 okl_cnsld_ar_strms_b st
706 WHERE st.id = xls.lsm_id
707 AND st.receivables_invoice_id = ps.customer_trx_id
708 AND ps.class IN('INV', 'CM')
709 AND arapp.applied_payment_schedule_id = ps.payment_schedule_id
710 AND cn.id = st.khr_id(+)
711 AND tai.id = til.tai_id
712 AND til.id = xls.til_id
713 AND tai.cpy_id IS NOT NULL
714 and st.khr_id = p_pay_cure_refunds_rec.chr_id;
715 --end changed by abhsaxen for Bug#6174484
716 ELSE
717 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
718 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'in else part of ref cursor for check contract' );
719 END IF;
720 --check for other 2 types;
721 if p_pay_cure_refunds_rec.REFUND_TYPE ='VENDOR_SITE' THEN
722 OPEN c_getcontracts
723 FOR
724 --start changed by abhsaxen for Bug#6174484
725 SELECT st.khr_id contract_id,
726 cn.contract_number
727 FROM okl_xtl_sell_invs_v xls,
728 okl_txl_ar_inv_lns_b til,
729 okl_trx_ar_invoices_b tai,
730 okc_k_headers_all_b cn,
731 ar_payment_schedules_all ps,
732 ar_receivable_applications_all arapp,
733 okl_cnsld_ar_strms_b st,
734 okc_k_party_roles_b pty,
735 okc_rules_b rul,
736 okc_k_headers_b CHR,
737 po_vendors pvn,
738 po_vendor_sites_all pvs
739 WHERE st.id = xls.lsm_id
740 AND st.receivables_invoice_id = ps.customer_trx_id
741 AND ps.class IN('INV', 'CM')
742 AND arapp.applied_payment_schedule_id = ps.payment_schedule_id
743 AND cn.id = st.khr_id(+)
744 AND tai.id = til.tai_id
745 AND til.id = xls.til_id
746 AND tai.cpy_id IS NOT NULL
747 AND rul.dnz_chr_id = CHR.id
748 AND rul.rule_information_category = 'COVNAG'
749 AND CHR.id = pty.chr_id
750 AND rle_code = 'OKL_VENDOR'
751 AND pty.object1_id1 = pvn.vendor_id
752 AND pvn.vendor_id = pvs.vendor_id
753 AND pvs.vendor_site_id = rul.rule_information1
754 AND CHR.id = cn.id
755 AND CHR.scs_code = 'PROGRAM'
756 AND pvs.vendor_site_id = p_pay_cure_refunds_rec.vendor_site_id
757 AND cn.currency_code = p_pay_cure_refunds_rec.currency;
758 --end changed by abhsaxen for Bug#6174484
759 elsif p_pay_cure_refunds_rec.REFUND_TYPE ='ACROSS_SITES' THEN
760 OPEN c_getcontracts
761 FOR
762 --start changed by abhsaxen for Bug#6174484
763 SELECT st.khr_id contract_id,
764 cn.contract_number
765 FROM okl_xtl_sell_invs_v xls,
766 okl_txl_ar_inv_lns_b til,
767 okl_trx_ar_invoices_b tai,
768 okc_k_headers_b cn,
769 ar_payment_schedules_all ps,
770 ar_receivable_applications_all arapp,
771 okl_cnsld_ar_strms_b st,
772 okc_k_party_roles_b pty,
773 okc_rules_b rul,
774 okc_k_headers_all_b CHR,
775 po_vendors pvn
776 WHERE st.id = xls.lsm_id
777 AND st.receivables_invoice_id = ps.customer_trx_id
778 AND ps.class IN('INV', 'CM')
779 AND arapp.applied_payment_schedule_id = ps.payment_schedule_id
780 AND cn.id = st.khr_id(+)
781 AND tai.id = til.tai_id
782 AND til.id = xls.til_id
783 AND tai.cpy_id IS NOT NULL
784 AND rul.dnz_chr_id = CHR.id
785 AND rul.rule_information_category = 'COVNAG'
786 AND CHR.id = pty.chr_id
787 AND rle_code = 'OKL_VENDOR'
788 AND pty.object1_id1 = pvn.vendor_id
789 AND CHR.id = cn.id
790 AND CHR.scs_code = 'PROGRAM'
791 AND pvn.vendor_id = p_pay_cure_refunds_rec.vendor_id
792 AND cn.currency_code = p_pay_cure_refunds_rec.currency;
793 --end changed by abhsaxen for Bug#6174484
794 end if;
795 END IF;
796
797 LOOP
798 l_amount_past_due :=0;
799 FETCH c_getcontracts INTO l_contract_id,l_contract_number;
800
801 IF c_getcontracts%NOTFOUND THEN
802 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
803 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'coming out from the cursor');
804 END IF;
805 x_return_status := FND_API.G_RET_STS_SUCCESS;
806 x_contract_number:=l_contract_number;
807 EXIT;
808 END IF;
809 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: check_contract : l_contract_id : '||l_contract_id);
810 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: check_contract : l_contract_number : '||l_contract_number);
811
812 -- Get Contract allowed value for days past due from rules
813 OPEN c_program_id(l_contract_id);
814 FETCH c_program_id INTO l_program_id;
815 CLOSE c_program_id;
816
817 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: check_contract : l_program_id : '||l_program_id);
818
819 l_return_status := okl_contract_info.get_rule_value(
820 p_contract_id => l_program_id
821 ,p_rule_group_code => 'COCURP'
822 ,p_rule_code => 'COCURE'
823 ,p_segment_number => 3
824 ,x_id1 => l_id1
825 ,x_id2 => l_id2
826 ,x_value => l_rule_value);
827
828 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: check_contract : okl_contract_info.get_rule_value : '||l_return_status);
829
830 IF l_return_status =FND_Api.G_RET_STS_SUCCESS THEN
831 l_days_allowed :=nvl(l_rule_value,0);
832 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
833 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,
834 'l_days allowed for days past due ' || l_days_allowed);
835 END IF;
836 END IF;
837
838 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: check_contract : l_days_allowed : '||l_days_allowed);
839
840 -- Get Past Due Amount
841 OPEN c_amount_past_due (l_contract_id,l_days_allowed);
842 FETCH c_amount_past_due INTO l_amount_past_due;
843 CLOSE c_amount_past_due;
844
845 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: check_contract : l_amount_past_due : '||l_amount_past_due);
846
847 IF l_amount_past_due > 0 THEN
848 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
849 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'Contract' ||l_contract_number ||
850 ' is delinquent');
851 END IF;
852 x_return_status := FND_API.G_RET_STS_ERROR;
853 x_contract_number:=l_contract_number;
854 EXIT;
855 END IF;
856
857 END LOOP;
858 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
859 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'Result of check Contract is '||
860 x_return_status);
861 END IF;
862 CLOSE c_getcontracts;
863
864 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: check_contract : END ');
865
866 END check_contract;
867
868 PROCEDURE populate_chr_tbl(p_pay_cure_refunds_rec IN pay_cure_refunds_rec_type
869 ,x_pay_tbl OUT NOCOPY pay_cure_refunds_tbl_type) IS
870
871
872 total_rfnd_amt NUMBER :=0;
873 con_rfnd_amt NUMBER :=0;
874 old_rfnd_amt NUMBER :=0;
875
876 l_idx INTEGER;
877
878 TYPE c_getcontractsCurTyp IS REF CURSOR;
879 c_getcontracts c_getcontractsCurTyp; -- declare cursor variable
880
881 l_contract_id okl_cure_refunds_dtls_uv.contract_id%TYPE;
882 l_refund_amount_due okl_cure_refunds_dtls_uv.refund_amount_due%TYPE;
883
884 BEGIN
885 IF (G_DEBUG_ENABLED = 'Y') THEN
886 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
887 END IF;
888 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
889 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'Start of populate_chr_tbl' );
890
891 END IF;
892 total_rfnd_amt := p_pay_cure_refunds_rec.refund_amount;
893
894 IF p_pay_cure_refunds_rec.REFUND_TYPE ='VENDOR_SITE' THEN
895 OPEN c_getcontracts
896 FOR
897 select contract_id,refund_amount_due
898 from okl_cure_refunds_dtls_uv
899 where vendor_site_id =p_pay_cure_refunds_rec.vendor_site_id and
900 contract_currency_code =p_pay_cure_refunds_rec.currency;
901
902 elsif p_pay_cure_refunds_rec.REFUND_TYPE ='ACROSS_SITES' THEN
903 OPEN c_getcontracts
904 FOR
905 select contract_id,refund_amount_due
906 from okl_cure_refunds_dtls_uv
907 where vendor_id =p_pay_cure_refunds_rec.vendor_id and
908 contract_currency_code =p_pay_cure_refunds_rec.currency;
909
910
911 END IF;
912 LOOP
913 FETCH c_getcontracts INTO l_contract_id,l_refund_amount_due;
914 IF c_getcontracts%NOTFOUND THEN
915 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
916 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'coming out from the cursor');
917 END IF;
918 EXIT;
919 END IF;
920 con_rfnd_amt := 0;
921 --Distribute refund amount among the contracts
922 IF total_rfnd_amt > 0 THEN
923 --store the old value
924 old_rfnd_amt := total_rfnd_amt;
925 total_rfnd_amt := total_rfnd_amt - l_refund_amount_due;
926
927 if total_rfnd_amt < 0 THEN
928 con_rfnd_amt :=old_rfnd_amt;
929 else
930 con_rfnd_amt :=l_refund_amount_due;
931 end if;
932
933 l_idx := nvl(x_pay_tbl.LAST,0) + 1;
934 x_pay_tbl(l_idx).chr_id :=l_contract_id;
935 x_pay_tbl(l_idx).refund_amount_due :=l_refund_amount_due;
936 x_pay_tbl(l_idx).refund_amount :=con_rfnd_amt;
937
938 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
939 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'contract id '||x_pay_tbl(l_idx).chr_id ||
940 ' refund_amount_due '||
941 x_pay_tbl(l_idx).refund_amount_due ||
942 ' refund_amount '||
943 x_pay_tbl(l_idx).refund_amount);
944 END IF;
945 ELSE
946 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
947 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'amount exhausted');
948 END IF;
949 EXIT;
950 END IF;
951
952 END LOOP;
953 CLOSE c_getcontracts;
954
955
956 END populate_chr_tbl;
957
958
959 PROCEDURE create_refund_hdr
960 ( p_api_version IN NUMBER
961 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_TRUE
962 ,p_commit IN VARCHAR2 DEFAULT OKC_API.G_FALSE
963 ,p_pay_cure_refunds_rec IN pay_cure_refunds_rec_type
964 ,x_cure_refund_header_id OUT NOCOPY NUMBER
965 ,x_return_status OUT NOCOPY VARCHAR2
966 ,x_msg_count OUT NOCOPY NUMBER
967 ,x_msg_data OUT NOCOPY VARCHAR2
968 )IS
969
970 l_init_msg_list VARCHAR2(1);
971 l_return_status VARCHAR2(1);
972 l_msg_count NUMBER ;
973 l_msg_data VARCHAR2(32627);
974 l_message VARCHAR2(32627);
975 l_cure_refund_id okl_cure_refunds.cure_refund_id%type;
976 l_cure_refund_header_id okl_cure_refund_headers_b.cure_refund_header_id%type;
977 l_cure_refund_header_number okl_cure_refund_headers_b.refund_header_number%type;
978 l_api_name CONSTANT VARCHAR2(50) := 'CREATE_REFUND_HDR';
979 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'
980 || l_api_name;
981
982 lp_chdv_rec okl_chd_pvt.chdv_rec_type;
983 lx_chdv_rec okl_chd_pvt.chdv_rec_type;
984
985 x_pay_tbl pay_cure_refunds_tbl_type;
986
987 l_pay_cure_refunds_rec pay_cure_refunds_rec_type;
988 cursor chk_refund_number(p_refund_header_number IN VARCHAR2) IS
989 select refund_header_number
990 from okl_cure_refund_headers_b
991 where refund_header_number =p_refund_header_number;
992
993 x_contract_number okc_k_headers_b.contract_number%TYPE;
994
995 BEGIN
996
997 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund_hdr : START ');
998
999 SAVEPOINT CREATE_REFUND_HDR;
1000 -- Initialize message list if p_init_msg_list is set to TRUE.
1001 IF FND_API.to_Boolean( p_init_msg_list )
1002 THEN
1003 FND_MSG_PUB.initialize;
1004 END IF;
1005
1006 x_return_status := FND_API.G_RET_STS_SUCCESS;
1007
1008 --duplicate refund_number check
1009 OPEN chk_refund_number(p_pay_cure_refunds_rec.refund_number);
1010 FETCH chk_refund_number INTO l_cure_refund_header_number;
1011 CLOSE chk_refund_number;
1012 if l_cure_refund_header_number IS NOT NULL THEN
1013
1014 IF PG_DEBUG < 11 THEN
1015 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1016 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'duplicate refund number' );
1017 END IF;
1018 END IF;
1019 fnd_message.set_name('OKL', 'OKL_DUPLICATE_REFUND_NUMBER');
1020 fnd_msg_pub.add;
1021 RAISE FND_API.G_EXC_ERROR;
1022 END IF;
1023
1024 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund_hdr : l_cure_refund_header_number : '||l_cure_refund_header_number);
1025
1026 --check if refund amount is less than total_refund_due
1027 IF nvl(p_pay_cure_refunds_rec.refund_amount,0)
1028 > nvl(p_pay_cure_refunds_rec.refund_amount_due,0) THEN
1029 IF PG_DEBUG < 11 THEN
1030 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1031 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Refund amount exceeds total refund due' );
1032 END IF;
1033 END IF;
1034 fnd_message.set_name('OKL', 'OKL_CURE_REFUND_EXCEEDS');
1035 fnd_msg_pub.add;
1036 RAISE FND_API.G_EXC_ERROR;
1037 END IF;
1038
1039 IF PG_DEBUG < 11 THEN
1040 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1041 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'before calling create refund');
1042 END IF;
1043 END IF;
1044
1045
1046 --02/27/03
1047 --Check if any of the contracts are in delinquency
1048 --We are going to check if the contract has any delinquent
1049 --invoices.(due_date + gracedays(from rule) < SYSDATE )
1050 --If it is delinquent , show error message
1051 --Alternate way was to check if the case with the contract
1052 --is in was in Delinquency or not. ( this would not consider the grace days)
1053
1054 CHECK_CONTRACT(p_pay_cure_refunds_rec,
1055 l_return_status,
1056 x_contract_number);
1057
1058 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund_hdr : CHECK_CONTRACT : '||l_return_status);
1059
1060 If l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1061 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1062 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Contract ' ||x_contract_number);
1063 END IF;
1064 fnd_message.set_name('OKL', 'OKL_CO_CONTRACT_DELINQUENT');
1065 fnd_message.set_token('CONTRACT_NUMBER', x_contract_number);
1066 fnd_msg_pub.add;
1067 raise FND_API.G_EXC_ERROR;
1068 END IF;
1069
1070
1071 --create hdr first
1072 --create cure refund hdr record
1073 lp_chdv_rec.refund_header_number := p_pay_cure_refunds_rec.refund_number;
1074 lp_chdv_rec.refund_type := p_pay_cure_refunds_rec.refund_type;
1075 lp_chdv_rec.vendor_site_id := p_pay_cure_refunds_rec.vendor_site_id;
1076 lp_chdv_rec.disbursement_amount := p_pay_cure_refunds_rec.refund_amount;
1077 lp_chdv_rec.total_refund_due := p_pay_cure_refunds_rec.refund_amount_due;
1078 lp_chdv_rec.refund_due_date := p_pay_cure_refunds_rec.invoice_date;
1079 lp_chdv_rec.object_version_number := 1;
1080 lp_chdv_rec.description := p_pay_cure_refunds_rec.description;
1081 lp_chdv_rec.refund_status :='PENDINGI';
1082 lp_chdv_rec.currency_code :=p_pay_cure_refunds_rec.currency;
1083 lp_chdv_rec.payment_method :=p_pay_cure_refunds_rec.payment_method_code;
1084 lp_chdv_rec.payment_term_id :=p_pay_cure_refunds_rec.pay_terms;
1085 lp_chdv_rec.chr_id :=p_pay_cure_refunds_rec.chr_id;
1086 lp_chdv_rec.vendor_site_cure_due :=p_pay_cure_refunds_rec.vendor_site_cure_due;
1087 lp_chdv_rec.vendor_cure_due :=p_pay_cure_refunds_rec.vendor_cure_due;
1088
1089 l_pay_cure_refunds_rec :=p_pay_cure_refunds_rec;
1090
1091
1092 IF l_pay_cure_refunds_rec.chr_id is not null THEN
1093 lp_chdv_rec.refund_type := 'CONTRACT' ;
1094 l_pay_cure_refunds_rec.refund_type := 'CONTRACT' ;
1095
1096 x_pay_tbl(1).chr_id :=l_pay_cure_refunds_rec.chr_id;
1097 x_pay_tbl(1).refund_amount_due :=l_pay_cure_refunds_rec.refund_amount_due;
1098 x_pay_tbl(1).refund_amount :=l_pay_cure_refunds_rec.refund_amount;
1099 ELSE
1100 populate_chr_tbl(l_pay_cure_refunds_rec,x_pay_tbl);
1101 END IF;
1102
1103
1104
1105 OKL_cure_rfnd_hdr_pub.insert_cure_rfnd_hdr(
1106 p_api_version => 1.0
1107 ,p_init_msg_list => 'T'
1108 ,x_return_status => l_return_status
1109 ,x_msg_count => l_msg_count
1110 ,x_msg_data => l_msg_data
1111 ,p_chdv_rec => lp_chdv_rec
1112 ,x_chdv_rec => lx_chdv_rec);
1113
1114 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund_hdr : OKL_cure_rfnd_hdr_pub.insert_cure_rfnd_hdr : '||l_return_status);
1115
1116 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1117 Get_Messages (l_msg_count,l_message);
1118 IF PG_DEBUG < 11 THEN
1119 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1120 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
1121 END IF;
1122 END IF;
1123 raise FND_API.G_EXC_ERROR;
1124 ELSE
1125 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1126 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lx_chdv_rec.cure_refund_header_id'
1127 ||lx_chdv_rec.cure_refund_header_id);
1128 END IF;
1129 l_pay_cure_refunds_rec.refund_header_id :=
1130 lx_chdv_rec.cure_refund_header_id;
1131 x_cure_refund_header_id :=
1132 lx_chdv_rec.cure_refund_header_id;
1133 END IF;
1134
1135 --have loop and distibute amounts
1136 --for the corresponding contracts
1137 --distribute amounts
1138 --if contract id is passed then
1139 --create payable only for that contract
1140 --else get all contract for the vendor or vendor site
1141
1142
1143 IF x_pay_tbl.COUNT > 0 THEN
1144 FOR i in x_pay_tbl.FIRST..x_pay_tbl.LAST
1145 LOOP
1146 l_pay_cure_refunds_rec.refund_amount_due:=x_pay_tbl(i).refund_amount_due;
1147 l_pay_cure_refunds_rec.refund_amount:=x_pay_tbl(i).refund_amount;
1148 l_pay_cure_refunds_rec.chr_id :=x_pay_tbl(i).chr_id;
1149 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1150 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_pay_cure_refunds_rec.refund_TYPE'||
1151 l_pay_cure_refunds_rec.refund_TYPE);
1152 END IF;
1153 create_refund
1154 (p_pay_cure_refunds_rec => l_pay_cure_refunds_rec
1155 ,x_cure_refund_id =>l_cure_refund_id
1156 ,x_return_status =>l_return_status
1157 ,x_msg_count =>l_msg_count
1158 ,x_msg_data =>l_msg_data
1159 );
1160
1161 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund_hdr : create_refund : '||l_return_status);
1162
1163 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1164 Get_Messages (l_msg_count,l_message);
1165 IF PG_DEBUG < 11 THEN
1166 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1167 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
1168 END IF;
1169 END IF;
1170 raise FND_API.G_EXC_ERROR;
1171 ELSE
1172 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1173 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_cure_refund_id'
1174 ||l_cure_refund_id);
1175 END IF;
1176 END IF;
1177
1178 END LOOP;
1179
1180 END IF; -- table count of cure refunds is >0
1181
1182
1183 -- Standard check for p_commit
1184 IF FND_API.to_Boolean( p_commit )
1185 THEN
1186 COMMIT WORK;
1187 END IF;
1188
1189 FND_MSG_PUB.Count_And_Get
1190 ( p_count => x_msg_count,
1191 p_data => x_msg_data
1192 );
1193
1194 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund_hdr : END ');
1195
1196 EXCEPTION
1197
1198 WHEN Fnd_Api.G_EXC_ERROR THEN
1199 ROLLBACK TO CREATE_REFUND_HDR;
1200 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1201 x_msg_count := l_msg_count ;
1202 x_msg_data := l_msg_data ;
1203 Fnd_Msg_Pub.count_and_get(
1204 p_count => x_msg_count
1205 ,p_data => x_msg_data);
1206 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1207 ROLLBACK TO CREATE_REFUND_HDR;
1208 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1209 x_msg_count := l_msg_count ;
1210 x_msg_data := l_msg_data ;
1211 Fnd_Msg_Pub.count_and_get(
1212 p_count => x_msg_count
1213 ,p_data => x_msg_data);
1214 WHEN OTHERS THEN
1215 ROLLBACK TO CREATE_REFUND_HDR;
1216 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1217 x_msg_count := l_msg_count ;
1218 x_msg_data := l_msg_data ;
1219 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_PAY_CURE_REFUNDS_PVT','CREATE_REFUND_HDR');
1220 Fnd_Msg_Pub.count_and_get(
1221 p_count => x_msg_count
1222 ,p_data => x_msg_data);
1223
1224
1225 END create_refund_hdr;
1226
1227 PROCEDURE update_refund_hdr
1228 ( p_api_version IN NUMBER
1229 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_TRUE
1230 ,p_commit IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1231 ,p_pay_cure_refunds_rec IN pay_cure_refunds_rec_type
1232 ,x_return_status OUT NOCOPY VARCHAR2
1233 ,x_msg_count OUT NOCOPY NUMBER
1234 ,x_msg_data OUT NOCOPY VARCHAR2
1235 ) IS
1236
1237 cursor c_get_tap_ids (p_cure_refund_header_id IN NUMBER ) is
1238 select a.tap_id,
1239 a.cure_refund_id,
1240 a.object_version_number,
1241 b.invoice_number
1242 from okl_cure_refunds a, okl_trx_ap_invoices_b b
1243 where cure_refund_header_id =p_cure_refund_header_id
1244 and a.tap_id =b.id;
1245
1246 cursor c_getobj(p_cure_refund_header_id IN NUMBER ) is
1247 select object_version_number from okl_cure_refund_headers_b
1248 where cure_refund_header_id =p_cure_refund_header_id;
1249
1250
1251
1252 l_init_msg_list VARCHAR2(1);
1253 l_return_status VARCHAR2(1);
1254 l_msg_count NUMBER ;
1255 l_msg_data VARCHAR2(32627);
1256 l_message VARCHAR2(32627);
1257 l_api_name CONSTANT VARCHAR2(50) := 'UPDATE_REFUND_HDR';
1258 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'
1259 || l_api_name;
1260
1261 lp_tapv_tbl okl_tap_pvt.tapv_tbl_type;
1262 lx_tapv_tbl okl_tap_pvt.tapv_tbl_type;
1263 lp_chdv_rec okl_chd_pvt.chdv_rec_type;
1264 lx_chdv_rec okl_chd_pvt.chdv_rec_type;
1265 next_row integer;
1266 lp_crfv_tbl okl_crf_pvt.crfv_tbl_type;
1267 lx_crfv_tbl okl_crf_pvt.crfv_tbl_type;
1268
1269 BEGIN
1270 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: update_refund_hdr : START ');
1271 SAVEPOINT UPDATE_REFUND_HDR;
1272
1273 -- Initialize message list if p_init_msg_list is set to TRUE.
1274 IF FND_API.to_Boolean( p_init_msg_list )
1275 THEN
1276 FND_MSG_PUB.initialize;
1277 END IF;
1278 x_return_status := FND_API.G_RET_STS_SUCCESS;
1279
1280 --update tap and cure_refund_headers table
1281 FOR i in c_get_tap_ids (p_pay_cure_refunds_rec.refund_header_id)
1282 LOOP
1283 next_row := nvl(lp_tapv_tbl.LAST,0) +1;
1284 lp_tapv_tbl(next_row).id :=i.tap_id;
1285 lp_tapv_tbl(next_row).date_invoiced := p_pay_cure_refunds_rec.invoice_date;
1286 lp_tapv_tbl(next_row).ippt_id := p_pay_cure_refunds_rec.pay_terms;
1287 lp_tapv_tbl(next_row).payment_method_code
1288 :=p_pay_cure_refunds_rec.payment_method_code;
1289 lp_tapv_tbl(next_row).vendor_invoice_number := i.invoice_number;
1290 lp_crfv_tbl(next_row).refund_date := p_pay_cure_refunds_rec.invoice_date;
1291 lp_crfv_tbl(next_row).cure_refund_id :=i.cure_refund_id;
1292 lp_crfv_tbl(next_row).object_version_number :=i.object_version_number;
1293 END LOOP;
1294
1295 IF PG_DEBUG <11 THEN
1296 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1297 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'no of records to be updated in TAP'||
1298 lp_tapv_tbl.COUNT);
1299 END IF;
1300 END IF;
1301
1302 okl_trx_ap_invoices_pub.update_trx_ap_invoices(
1303 p_api_version => 1.0
1304 ,p_init_msg_list => 'T'
1305 ,x_return_status => l_return_status
1306 ,x_msg_count => l_msg_count
1307 ,x_msg_data => l_msg_data
1308 ,p_tapv_tbl => lp_tapv_tbl
1309 ,x_tapv_tbl => lx_tapv_tbl);
1310
1311 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: update_refund_hdr : okl_trx_ap_invoices_pub.update_trx_ap_invoices : '||l_return_status);
1312
1313 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1314 Get_Messages (l_msg_count,l_message);
1315 IF PG_DEBUG <11 THEN
1316 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1317 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
1318 END IF;
1319 END IF;
1320 raise FND_API.G_EXC_ERROR;
1321 ELSE
1322 IF PG_DEBUG < 11 THEN
1323 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1324 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Succesfully updated tap records');
1325 END IF;
1326 END IF;
1327 FND_MSG_PUB.initialize;
1328 END IF;
1329
1330
1331
1332 --set error message,so this will be prefixed before the
1333 --actual message, so it makes more sense than displaying an
1334 -- OKL message.
1335 AddfailMsg(
1336 p_object => 'RECORD IN OKL_CURE_REFUNDS ',
1337 p_operation => 'UPDATE' );
1338
1339 OKL_cure_refunds_pub.update_cure_refunds(
1340 p_api_version => 1.0
1341 ,p_init_msg_list => 'F'
1342 ,x_return_status => l_return_status
1343 ,x_msg_count => l_msg_count
1344 ,x_msg_data => l_msg_data
1345 ,p_crfv_tbl => lp_crfv_tbl
1346 ,x_crfv_tbl => lx_crfv_tbl);
1347
1348 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: update_refund_hdr : OKL_cure_refunds_pub.update_cure_refunds : '||l_return_status);
1349
1350 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1351 Get_Messages (l_msg_count,l_message);
1352 IF PG_DEBUG < 11 THEN
1353 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1354 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
1355 END IF;
1356 END IF;
1357 raise FND_API.G_EXC_ERROR;
1358 ELSE
1359 IF PG_DEBUG < 11 THEN
1360 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1361 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Succesfully updated CRF records');
1362 END IF;
1363 END IF;
1364 FND_MSG_PUB.initialize;
1365 END IF;
1366
1367
1368 --set error message,so this will be prefixed before the
1369 --actual message, so it makes more sense than displaying an
1370 -- OKL message.
1371 AddfailMsg(
1372 p_object => 'RECORD IN OKL_CURE_REFUND_HEADERS ',
1373 p_operation => 'UPDATE' );
1374
1375 lp_chdv_rec.cure_refund_header_id :=p_pay_cure_refunds_rec.refund_header_id;
1376 lp_chdv_rec.refund_due_date :=p_pay_cure_refunds_rec.invoice_date;
1377 lp_chdv_rec.payment_method :=p_pay_cure_refunds_rec.payment_method_code;
1378 lp_chdv_rec.payment_term_id :=p_pay_cure_refunds_rec.pay_terms;
1379
1380 OPEN c_getobj(p_pay_cure_refunds_rec.refund_header_id);
1381 FETCH c_getobj INTO lp_chdv_rec.object_version_number;
1382 CLOSE c_getobj;
1383
1384
1385 OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr(
1386 p_api_version => 1.0
1387 ,p_init_msg_list => 'F'
1388 ,x_return_status => l_return_status
1389 ,x_msg_count => l_msg_count
1390 ,x_msg_data => l_msg_data
1391 ,p_chdv_rec => lp_chdv_rec
1392 ,x_chdv_rec => lx_chdv_rec);
1393
1394 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: update_refund_hdr : OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr : '||l_return_status);
1395
1396 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1397 Get_Messages (l_msg_count,l_message);
1398 IF PG_DEBUG < 11 THEN
1399 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1400 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
1401 END IF;
1402 END IF;
1403 raise FND_API.G_EXC_ERROR;
1404 ELSE
1405 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1406 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Successfully updated Cure refund '||
1407 'header table');
1408
1409 END IF;
1410 END IF;
1411
1412 IF x_return_status =FND_API.G_RET_STS_SUCCESS THEN
1413 FND_MSG_PUB.initialize;
1414 END IF;
1415
1416
1417 -- Standard check for p_commit
1418 IF FND_API.to_Boolean( p_commit )
1419 THEN
1420 COMMIT WORK;
1421 END IF;
1422
1423 FND_MSG_PUB.Count_And_Get
1424 ( p_count => x_msg_count,
1425 p_data => x_msg_data
1426 );
1427
1428 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: update_refund_hdr : END ');
1429
1430 EXCEPTION
1431
1432 WHEN Fnd_Api.G_EXC_ERROR THEN
1433 ROLLBACK TO UPDATE_REFUND_HDR;
1434 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1435 x_msg_count := l_msg_count ;
1436 x_msg_data := l_msg_data ;
1437 Fnd_Msg_Pub.count_and_get(
1438 p_count => x_msg_count
1439 ,p_data => x_msg_data);
1440 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1441 ROLLBACK TO UPDATE_REFUND_HDR;
1442 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1443 x_msg_count := l_msg_count ;
1444 x_msg_data := l_msg_data ;
1445 Fnd_Msg_Pub.count_and_get(
1446 p_count => x_msg_count
1447 ,p_data => x_msg_data);
1448 WHEN OTHERS THEN
1449 ROLLBACK TO UPDATE_REFUND_HDR;
1450 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1451 x_msg_count := l_msg_count ;
1452 x_msg_data := l_msg_data ;
1453 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_PAY_CURE_REFUNDS_PVT','UPDATE_REFUND_HDR');
1454 Fnd_Msg_Pub.count_and_get(
1455 p_count => x_msg_count
1456 ,p_data => x_msg_data);
1457
1458 END update_refund_hdr;
1459
1460 PROCEDURE delete_refund_hdr
1461 ( p_api_version IN NUMBER
1462 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_TRUE
1463 ,p_commit IN VARCHAR2 DEFAULT OKC_API.G_FALSE
1464 ,p_refund_header_id IN NUMBER
1465 ,x_return_status OUT NOCOPY VARCHAR2
1466 ,x_msg_count OUT NOCOPY NUMBER
1467 ,x_msg_data OUT NOCOPY VARCHAR2
1468 ) IS
1469
1470 cursor c_get_tap_ids (p_cure_refund_header_id IN NUMBER ) is
1471 select crf.tap_id,
1472 crf.cure_refund_id,
1473 crf.object_version_number,
1474 til.id til_id
1475 from okl_cure_refunds crf,
1476 okl_txl_ap_inv_lns_b til
1477 where cure_refund_header_id =p_cure_refund_header_id
1478 and til.tap_id =crf.tap_id;
1479
1480
1481 cursor c_getobj(p_cure_refund_header_id IN NUMBER ) is
1482 select object_version_number from okl_cure_refund_headers_b
1483 where cure_refund_header_id =p_cure_refund_header_id;
1484
1485
1486
1487 l_init_msg_list VARCHAR2(1);
1488 l_return_status VARCHAR2(1);
1489 l_msg_count NUMBER ;
1490 l_msg_data VARCHAR2(32627);
1491 l_message VARCHAR2(32627);
1492
1493 l_api_name CONSTANT VARCHAR2(50) := 'DELETE_REFUND_HDR';
1494 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'
1495 || l_api_name;
1496
1497 lp_tapv_tbl okl_tap_pvt.tapv_tbl_type;
1498 lp_tplv_tbl okl_tpl_pvt.tplv_tbl_type;
1499 lp_chdv_rec okl_chd_pvt.chdv_rec_type;
1500 lp_crfv_tbl okl_crf_pvt.crfv_tbl_type;
1501 next_row integer;
1502
1503
1504 BEGIN
1505
1506 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: delete_refund_hdr : START ');
1507
1508 SAVEPOINT DELETE_REFUND_HDR;
1509
1510 -- Initialize message list if p_init_msg_list is set to TRUE.
1511 IF FND_API.to_Boolean( p_init_msg_list )
1512 THEN
1513 FND_MSG_PUB.initialize;
1514 END IF;
1515
1516 x_return_status := FND_API.G_RET_STS_SUCCESS;
1517
1518 IF (p_refund_header_id IS NULL) THEN
1519 AddMissingArgMsg(
1520 p_api_name => l_api_name_full,
1521 p_param_name => 'cure_refund_header_id' );
1522 RAISE FND_API.G_EXC_ERROR;
1523 END IF;
1524
1525
1526 --update tap and cure_refund_headers table
1527 FOR i in c_get_tap_ids (p_refund_header_id)
1528 LOOP
1529 next_row := nvl(lp_tapv_tbl.LAST,0) +1;
1530 lp_tapv_tbl(next_row).id :=i.tap_id;
1531 lp_tplv_tbl(next_row).id :=i.til_id;
1532 lp_crfv_tbl(next_row).cure_refund_id :=i.cure_refund_id;
1533 lp_crfv_tbl(next_row).object_version_number :=i.object_version_number;
1534 END LOOP;
1535
1536 IF PG_DEBUG <11 THEN
1537 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1538 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'no of records to be updated in TAP'||
1539 lp_tapv_tbl.COUNT);
1540 END IF;
1541 END IF;
1542
1543 okl_trx_ap_invoices_pub.delete_trx_ap_invoices(
1544 p_api_version => 1.0
1545 ,p_init_msg_list => 'T'
1546 ,x_return_status => l_return_status
1547 ,x_msg_count => l_msg_count
1548 ,x_msg_data => l_msg_data
1549 ,p_tapv_tbl => lp_tapv_tbl);
1550
1551 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: delete_refund_hdr : okl_trx_ap_invoices_pub.delete_trx_ap_invoices : '||l_return_status);
1552
1553 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1554 Get_Messages (l_msg_count,l_message);
1555 IF PG_DEBUG <11 THEN
1556 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1557 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
1558 END IF;
1559 END IF;
1560 raise FND_API.G_EXC_ERROR;
1561 ELSE
1562 IF PG_DEBUG < 11 THEN
1563 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1564 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Succesfully deleted tap records');
1565 END IF;
1566 END IF;
1567 FND_MSG_PUB.initialize;
1568 END IF;
1569
1570 --set error message,so this will be prefixed before the
1571 --actual message, so it makes more sense than displaying an
1572 -- OKL message.
1573 AddfailMsg(
1574 p_object => 'RECORD IN OKL_TXL_AP_INV_LNS_B ',
1575 p_operation => 'DELETE' );
1576
1577 okl_txl_ap_inv_lns_pub.delete_txl_ap_inv_lns (
1578 p_api_version => 1.0
1579 ,p_init_msg_list => 'F'
1580 ,x_return_status => l_return_status
1581 ,x_msg_count => l_msg_count
1582 ,x_msg_data => l_msg_data
1583 ,p_tplv_tbl => lp_tplv_tbl);
1584
1585 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: delete_refund_hdr : okl_txl_ap_inv_lns_pub.delete_txl_ap_inv_lns : '||l_return_status);
1586
1587 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1588 Get_Messages (l_msg_count,l_message);
1589 IF PG_DEBUG <11 THEN
1590 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1591 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
1592 END IF;
1593 END IF;
1594 raise FND_API.G_EXC_ERROR;
1595 ELSE
1596 IF PG_DEBUG < 11 THEN
1597 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1598 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Succesfully deleted tap records');
1599 END IF;
1600 END IF;
1601 FND_MSG_PUB.initialize;
1602 END IF;
1603
1604 --set error message,so this will be prefixed before the
1605 --actual message, so it makes more sense than displaying an
1606 -- OKL message.
1607 AddfailMsg(
1608 p_object => 'RECORD IN OKL_CURE_REFUNDS ',
1609 p_operation => 'DELETE' );
1610
1611 OKL_cure_refunds_pub.delete_cure_refunds(
1612 p_api_version => 1.0
1613 ,p_init_msg_list => 'F'
1614 ,x_return_status => l_return_status
1615 ,x_msg_count => l_msg_count
1616 ,x_msg_data => l_msg_data
1617 ,p_crfv_tbl => lp_crfv_tbl);
1618 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: delete_refund_hdr : OKL_cure_refunds_pub.delete_cure_refunds : '||l_return_status);
1619
1620 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1621 Get_Messages (l_msg_count,l_message);
1622 IF PG_DEBUG < 11 THEN
1623 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1624 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
1625 END IF;
1626 END IF;
1627 raise FND_API.G_EXC_ERROR;
1628 ELSE
1629 IF PG_DEBUG < 11 THEN
1630 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1631 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Succesfully deleted CRF records');
1632 END IF;
1633 END IF;
1634 FND_MSG_PUB.initialize;
1635 END IF;
1636
1637
1638 --set error message,so this will be prefixed before the
1639 --actual message, so it makes more sense than displaying an
1640 -- OKL message.
1641 AddfailMsg(
1642 p_object => 'RECORD IN OKL_CURE_REFUND_HEADERS ',
1643 p_operation => 'DELETE' );
1644
1645 lp_chdv_rec.cure_refund_header_id :=p_refund_header_id;
1646
1647 OPEN c_getobj(p_refund_header_id);
1648 FETCH c_getobj INTO lp_chdv_rec.object_version_number;
1649 CLOSE c_getobj;
1650
1651
1652 OKL_cure_rfnd_hdr_pub.delete_cure_rfnd_hdr(
1653 p_api_version => 1.0
1654 ,p_init_msg_list => 'F'
1655 ,x_return_status => l_return_status
1656 ,x_msg_count => l_msg_count
1657 ,x_msg_data => l_msg_data
1658 ,p_chdv_rec => lp_chdv_rec);
1659
1660 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: delete_refund_hdr : OKL_cure_rfnd_hdr_pub.delete_cure_rfnd_hdr : '||l_return_status);
1661
1662 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1663 Get_Messages (l_msg_count,l_message);
1664 IF PG_DEBUG < 11 THEN
1665 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1666 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
1667 END IF;
1668 END IF;
1669 raise FND_API.G_EXC_ERROR;
1670 ELSE
1671 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1672 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Successfully deleted Cure refund '||
1673 'header table');
1674
1675 END IF;
1676 END IF;
1677
1678
1679 IF x_return_status =FND_API.G_RET_STS_SUCCESS THEN
1680 FND_MSG_PUB.initialize;
1681 END IF;
1682
1683
1684 -- Standard check for p_commit
1685 IF FND_API.to_Boolean( p_commit )
1686 THEN
1687 COMMIT WORK;
1688 END IF;
1689
1690 FND_MSG_PUB.Count_And_Get
1691 ( p_count => x_msg_count,
1692 p_data => x_msg_data
1693 );
1694
1695 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: delete_refund_hdr : END ');
1696 EXCEPTION
1697
1698 WHEN Fnd_Api.G_EXC_ERROR THEN
1699 ROLLBACK TO DELETE_REFUND_HDR;
1700 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1701 x_msg_count := l_msg_count ;
1702 x_msg_data := l_msg_data ;
1703 Fnd_Msg_Pub.count_and_get(
1704 p_count => x_msg_count
1705 ,p_data => x_msg_data);
1706 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1707 ROLLBACK TO DELETE_REFUND_HDR;
1708 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1709 x_msg_count := l_msg_count ;
1710 x_msg_data := l_msg_data ;
1711 Fnd_Msg_Pub.count_and_get(
1712 p_count => x_msg_count
1713 ,p_data => x_msg_data);
1714 WHEN OTHERS THEN
1715 ROLLBACK TO DELETE_REFUND_HDR;
1716 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1717 x_msg_count := l_msg_count ;
1718 x_msg_data := l_msg_data ;
1719 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_PAY_CURE_REFUNDS_PVT','DELETE_REFUND_HDR');
1720 Fnd_Msg_Pub.count_and_get(
1721 p_count => x_msg_count
1722 ,p_data => x_msg_data);
1723
1724 END delete_refund_hdr;
1725
1726 PROCEDURE CREATE_TAI_ACCOUNTING
1727 (p_cure_refund_header_id IN NUMBER,
1728 x_return_status OUT NOCOPY VARCHAR2,
1729 x_msg_count OUT NOCOPY NUMBER,
1730 x_msg_data OUT NOCOPY VARCHAR2) IS
1731
1732 cursor c_get_contract_currency (l_khr_id IN NUMBER) IS
1733 select currency_code from OKC_K_HEADERS_b
1734 where id =l_khr_id;
1735
1736 CURSOR curr_csr (l_khr_id NUMBER) IS
1737 SELECT currency_conversion_type,
1738 currency_conversion_rate,
1739 currency_conversion_date
1740 FROM okl_k_headers
1741 WHERE id = l_khr_id;
1742
1743
1744 l_functional_currency okl_trx_contracts.currency_code%TYPE;
1745 l_currency_conversion_type okl_k_headers.currency_conversion_type%TYPE;
1746 l_currency_conversion_rate okl_k_headers.currency_conversion_rate%TYPE;
1747 l_currency_conversion_date okl_k_headers.currency_conversion_date%TYPE;
1748 l_contract_currency OKC_K_HEADERS_b.currency_code%TYPE;
1749
1750 next_row integer;
1751
1752
1753 -- ASHIM CHANGE - START
1754
1755
1756 /*cursor c_get_accounting(p_refund_header_id IN NUMBER) is
1757 select tai.try_id,
1758 til.sty_id,
1759 til.id,
1760 tai.khr_id,
1761 tai.date_invoiced,
1762 tai.amount,
1763 tai.currency_code
1764 from
1765 okl_trx_ar_invoices_b tai,
1766 okl_txl_ar_inv_lns_b til,
1767 okl_cure_refunds crf
1768 where tai.id =til.tai_id
1769 and tai.id =crf.tai_id
1770 and crf.cure_refund_header_id =p_refund_header_id;*/
1771
1772 cursor c_get_accounting(p_refund_header_id IN NUMBER) is
1773 select tai.id tai_id,
1774 tai.try_id try_id,
1775 txd.sty_id sty_id,
1776 txd.id txd_id,
1777 tai.khr_id khr_id,
1778 tai.date_invoiced date_invoiced,
1779 tai.amount amount,
1780 tai.currency_code currency_code
1781 from okl_trx_ar_invoices_b tai,
1782 okl_txl_ar_inv_lns_b til,
1783 okl_txd_ar_ln_dtls_b txd,
1784 okl_cure_refunds crf
1785 where crf.cure_refund_header_id = p_refund_header_id
1786 and tai.id = crf.tai_id
1787 and tai.id = til.tai_id
1788 and til.id = txd.til_id_details ;
1789
1790
1791
1792 -- ASHIM CHANGE - END
1793
1794 l_tai_id okl_trx_ar_invoices_b.id%TYPE;
1795 l_sty_id okl_txl_ar_inv_lns_b.sty_id%TYPE;
1796 l_try_id okl_trx_ar_invoices_b.try_id%TYPE;
1797 l_line_id okl_txl_ar_inv_lns_b.id%TYPE;
1798 l_khr_id okc_k_headers_b.id%TYPE;
1799 l_date_invoiced okl_trx_ar_invoices_b.date_invoiced%TYPE;
1800 l_amount okl_trx_ar_invoices_b.amount%TYPE;
1801
1802 CURSOR product_csr (p_chr_id IN NUMBER) IS
1803 SELECT khr.pdt_id,
1804 chr.scs_code --Bug# 4622198
1805 FROM okl_k_headers khr,
1806 okc_k_headers_b chr --Bug# 4622198
1807 WHERE chr.id = khr.id --Bug# 4622198
1808 and khr.id = p_chr_id;
1809
1810 /* -- OKL.H Code commented out
1811 l_tmpl_identify_rec Okl_Account_Dist_Pvt.TMPL_IDENTIFY_REC_TYPE;
1812 l_dist_info_rec Okl_Account_Dist_Pvt.dist_info_REC_TYPE;
1813 l_ctxt_val_tbl okl_execute_formula_pvt.ctxt_val_tbl_type;
1814 l_acc_gen_primary_key_tbl Okl_Account_Generator_Pvt.primary_key_tbl;
1815 l_template_tbl Okl_Account_Dist_Pub.AVLV_TBL_TYPE;
1816 l_amount_tbl Okl_Account_Dist_Pub.AMOUNT_TBL_TYPE;
1817 */
1818
1819 -- R12 Change - START
1820
1821 l_tmpl_identify_tbl okl_account_dist_pvt.tmpl_identify_tbl_type;
1822 l_dist_info_tbl okl_account_dist_pvt.dist_info_tbl_type;
1823 l_template_tbl okl_account_dist_pvt.avlv_out_tbl_type;
1824 l_amount_tbl okl_account_dist_pvt.amount_out_tbl_type;
1825 l_ctxt_val_tbl okl_account_dist_pvt.ctxt_tbl_type;
1826 l_acc_gen_primary_key_tbl okl_account_dist_pvt.acc_gen_tbl_type;
1827
1828 -- R12 Change - END
1829
1830 l_factoring_synd VARCHAR2(30);
1831 l_syndication_code VARCHAR2(30) DEFAULT NULL;
1832 l_factoring_code VARCHAR2(30) DEFAULT NULL;
1833
1834 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
1835 l_msg_count NUMBER ;
1836 l_msg_data VARCHAR2(32627);
1837 l_message VARCHAR2(32627);
1838 l_api_name CONSTANT VARCHAR2(50) := 'CREATE_TAI_ACCOUNTING';
1839 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'
1840 || l_api_name;
1841
1842 --Bug# 4622198 :For special accounting treatment - START
1843 l_fact_synd_code FND_LOOKUPS.Lookup_code%TYPE;
1844 l_inv_acct_code OKC_RULES_B.Rule_Information1%TYPE;
1845 l_scs_code okc_k_headers_b.SCS_CODE%TYPE;
1846 --Bug# 4622198 :For special accounting treatment - END
1847
1848
1849 BEGIN
1850
1851 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: CREATE_TAI_ACCOUNTING : START ');
1852
1853 SAVEPOINT CREATE_TAI_ACCOUNTING;
1854 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1855 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'start CREATE_TAI_ACCOUNTING');
1856
1857 END IF;
1858 x_return_status := FND_API.G_RET_STS_SUCCESS;
1859
1860
1861 FOR j in c_get_accounting(p_cure_refund_header_id)
1862 LOOP
1863
1864 FOR i IN product_csr (j.khr_id)
1865 LOOP
1866 l_tmpl_identify_tbl(1).product_id := i.pdt_id;
1867 l_scs_code := i.scs_code;
1868 IF l_tmpl_identify_tbl(1).product_id IS NULL THEN
1869 OKL_API.SET_MESSAGE (p_app_name => 'OKL',
1870 p_msg_name => 'OKL_NO_PRODUCT_FOUND');
1871 raise FND_API.G_EXC_ERROR;
1872 END IF;
1873 IF PG_DEBUG < 11 THEN
1874 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1875 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'product_id '
1876 ||l_tmpl_identify_tbl(1).product_id);
1877 END IF;
1878 END IF;
1879 END LOOP;
1880 /*--- New Code Start Here ---*/
1881 -- Fetch the functional currency
1882 l_functional_currency := OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE;
1883
1884 -- Fetch the currency conversion factors if functional currency is not equal
1885 -- to the transaction currency
1886
1887 OPEN c_get_contract_currency (j.khr_id);
1888 FETCH c_get_contract_currency INTO l_contract_currency;
1889 CLOSE c_get_contract_currency;
1890 l_dist_info_tbl(1).currency_code := l_contract_currency;
1891
1892 IF l_functional_currency <> l_contract_currency THEN
1893
1894 -- Fetch the currency conversion factors from Contracts
1895 FOR curr_rec IN curr_csr(j.khr_id) LOOP
1896 l_currency_conversion_type := curr_rec.currency_conversion_type;
1897 l_currency_conversion_rate := curr_rec.currency_conversion_rate;
1898 l_currency_conversion_date := curr_rec.currency_conversion_date;
1899 END LOOP;
1900
1901 -- Fetch the currency conversion factors from GL_DAILY_RATES if the
1902 -- conversion type is not 'USER'.
1903
1904 IF UPPER(l_currency_conversion_type) <> 'USER' THEN
1905 l_currency_conversion_date := SYSDATE;
1906 l_currency_conversion_rate := okl_accounting_util.get_curr_con_rate
1907 (p_from_curr_code => l_contract_currency,
1908 p_to_curr_code => l_functional_currency,
1909 p_con_date => l_currency_conversion_date,
1910 p_con_type => l_currency_conversion_type);
1911
1912 END IF; -- End IF for (UPPER(l_currency_conversion_type) <> 'USER')
1913
1914 END IF; -- End IF for (l_functional_currency <> l_contract_currency)
1915
1916 -- Populate the currency conversion factors
1917
1918 l_dist_info_tbl(1).currency_conversion_type := l_currency_conversion_type;
1919 l_dist_info_tbl(1).currency_conversion_rate := l_currency_conversion_rate;
1920 l_dist_info_tbl(1).currency_conversion_date := l_currency_conversion_date;
1921
1922 -- Round the transaction amount
1923 l_dist_info_tbl(1).amount:= okl_accounting_util.cross_currency_round_amount
1924 (p_amount => j.amount,
1925 p_currency_code => l_contract_currency);
1926
1927 l_dist_info_tbl(1).contract_id := j.khr_id;
1928 l_dist_info_tbl(1).amount:= l_dist_info_tbl(1).amount * -1;
1929
1930 /*--- New Code End Here ---*/
1931
1932
1933 l_tmpl_identify_tbl(1).transaction_type_id := j.try_id;
1934 l_tmpl_identify_tbl(1).stream_type_id := j.sty_id;
1935 l_tmpl_identify_tbl(1).advance_arrears := null;
1936 l_tmpl_identify_tbl(1).factoring_synd_flag := null;
1937 l_tmpl_identify_tbl(1).syndication_code := null;
1938 l_tmpl_identify_tbl(1).factoring_code := null;
1939 l_tmpl_identify_tbl(1).memo_yn := 'N';
1940 l_tmpl_identify_tbl(1).prior_year_yn := 'N';
1941
1942 l_dist_info_tbl(1).source_id := j.txd_id;
1943 --start: cklee 06/28/07
1944 -- l_dist_info_tbl(1).source_table := 'OKL_TXL_AR_INV_LNS_B';
1945 l_dist_info_tbl(1).source_table := 'OKL_TXD_AR_LN_DTLS_B';
1946 --end: cklee 06/28/07
1947 l_dist_info_tbl(1).accounting_date := j.date_invoiced;
1948 l_dist_info_tbl(1).gl_reversal_flag :='N';
1949 l_dist_info_tbl(1).post_to_gl :='N';
1950 l_dist_info_tbl(1).currency_code := l_contract_currency;
1951 l_dist_info_tbl(1).contract_id := j.khr_id;
1952
1953 --set error message,so this will be prefixed before the
1954 --actual message, so it makes more sense than displaying an
1955 -- OKL message.
1956 -- R12 CHANGE- START
1957
1958 --Do no know what this segment does. Hence commented out,
1959 --will enable if required during test run
1960 -- enabled by cklee 06/29/07
1961
1962 AddfailMsg(
1963 p_object => 'Okl_Acc_Call_Pvt.Okl_Populate_Acc_Gen ',
1964 p_operation => 'CREATE' );
1965
1966 Okl_Acc_Call_Pvt.Okl_Populate_Acc_Gen (
1967 p_contract_id => j.khr_id,
1968 p_contract_line_id => NULL,
1969 x_acc_gen_tbl => l_acc_gen_primary_key_tbl(1).acc_gen_key_tbl,
1970 x_return_status => l_return_status);
1971
1972 l_acc_gen_primary_key_tbl(1).source_id := j.txd_id; -- cklee 06/29/07
1973
1974 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
1975 raise FND_API.G_EXC_ERROR;
1976 ELSE
1977 FND_MSG_PUB.initialize;
1978 IF PG_DEBUG < 11 THEN
1979 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
1980 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_acc_gen_primary_key_tbl for TAI'
1981 ||l_acc_gen_primary_key_tbl(1).acc_gen_key_tbl.count
1982 ||l_acc_gen_primary_key_tbl(1).acc_gen_key_tbl(1).primary_key_column
1983 ||l_acc_gen_primary_key_tbl(1).acc_gen_key_tbl(1).source_table );
1984 END IF;
1985 END IF;
1986
1987 END IF;
1988
1989 -- R12 CHANGE- END
1990
1991
1992 --set error message,so this will be prefixed before the
1993 --actual message, so it makes more sense than displaying an
1994 -- OKL message.
1995 AddfailMsg(
1996 p_object => 'OKL_SECURITIZATION_PVT.Check_Khr_ia_associated ',
1997 p_operation => 'CREATE' );
1998
1999 --Bug# 4622198 :For special accounting treatment - START
2000 OKL_SECURITIZATION_PVT.Check_Khr_ia_associated(
2001 p_api_version => 1.0,
2002 p_init_msg_list => OKL_API.G_FALSE,
2003 x_return_status => x_return_status,
2004 x_msg_count => x_msg_count,
2005 x_msg_data => x_msg_data,
2006 p_khr_id => j.khr_id,
2007 p_scs_code => l_scs_code,
2008 p_trx_date => j.date_invoiced,
2009 x_fact_synd_code => l_fact_synd_code,
2010 x_inv_acct_code => l_inv_acct_code
2011 );
2012
2013 IF (x_return_status = OKL_API.G_RET_STS_UNEXP_ERROR) THEN
2014 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
2015 ELSIF (x_return_status = OKL_API.G_RET_STS_ERROR) THEN
2016 RAISE OKL_API.G_EXCEPTION_ERROR;
2017 END IF;
2018
2019 l_tmpl_identify_tbl(1).factoring_synd_flag := l_fact_synd_code;
2020 l_tmpl_identify_tbl(1).investor_code := l_inv_acct_code;
2021 --Bug# 4622198 :For special accounting treatment - END
2022
2023
2024 --set error message,so this will be prefixed before the
2025 --actual message, so it makes more sense than displaying an
2026 -- OKL message.
2027 AddfailMsg(
2028 p_object => 'Okl_Account_Dist_Pub.CREATE_ACCOUNTING_DIST ',
2029 p_operation => 'CREATE' );
2030
2031 /* OKL.H code commented out
2032 Okl_Account_Dist_Pub.CREATE_ACCOUNTING_DIST(
2033 p_api_version => 1.0
2034 ,p_init_msg_list => 'F'
2035 ,x_return_status => l_return_status
2036 ,x_msg_count => l_msg_count
2037 ,x_msg_data => l_msg_data
2038 ,p_tmpl_identify_rec => l_tmpl_identify_rec
2039 ,p_dist_info_rec => l_dist_info_rec
2040 ,p_ctxt_val_tbl => l_ctxt_val_tbl
2041 ,p_acc_gen_primary_key_tbl => l_acc_gen_primary_key_tbl
2042 ,x_template_tbl => l_template_tbl
2043 ,x_amount_tbl => l_amount_tbl);
2044 */
2045 -- R12 CHANGE - START
2046 okl_account_dist_pvt.create_accounting_dist(
2047 p_api_version => 1.0,
2048 p_init_msg_list => OKL_API.G_FALSE,
2049 x_return_status => l_return_status,
2050 x_msg_count => l_msg_count,
2051 x_msg_data => l_msg_data,
2052 p_tmpl_identify_tbl => l_tmpl_identify_tbl,
2053 p_dist_info_tbl => l_dist_info_tbl,
2054 p_ctxt_val_tbl => l_ctxt_val_tbl,
2055 p_acc_gen_primary_key_tbl => l_acc_gen_primary_key_tbl,
2056 x_template_tbl => l_template_tbl,
2057 x_amount_tbl => l_amount_tbl,
2058 p_trx_header_id => j.tai_id,--); 06/28/07 cklee
2059 p_trx_header_table => 'OKL_TRX_AR_INVOICES_B'); -- 06/28/07 cklee
2060
2061 -- R12 CHANGE - END
2062
2063 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: CREATE_TAI_ACCOUNTING : okl_account_dist_pvt.create_accounting_dist : '||l_return_status);
2064
2065 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2066 Get_Messages (l_msg_count,l_message);
2067 IF PG_DEBUG < 11 THEN
2068 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2069 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
2070 END IF;
2071 END IF;
2072 raise FND_API.G_EXC_ERROR;
2073 ELSE
2074 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2075 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'count of l_template_tbl'||l_template_tbl.count);
2076 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'count of l_amount_tbl'||l_amount_tbl.count);
2077 END IF;
2078 FND_MSG_PUB.initialize;
2079 END IF;
2080
2081 END LOOP; -- for c_get_accounting cursor
2082
2083
2084 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2085 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'after accounting dist '||l_return_status);
2086
2087 END IF;
2088 FND_MSG_PUB.Count_And_Get
2089 ( p_count => x_msg_count,
2090 p_data => x_msg_data
2091 );
2092 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2093 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' End of Procedure'||
2094 '=>OKL_PAY_RECON_PVT.'||
2095 'CREATE_TAI_ACCOUNTING');
2096 END IF;
2097
2098 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: CREATE_TAI_ACCOUNTING : END ');
2099
2100 EXCEPTION
2101 WHEN Fnd_Api.G_EXC_ERROR THEN
2102 ROLLBACK TO CREATE_TAI_ACCOUNTING;
2103 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2104 x_msg_count := l_msg_count ;
2105 x_msg_data := l_msg_data ;
2106 Fnd_Msg_Pub.count_and_get(
2107 p_count => x_msg_count
2108 ,p_data => x_msg_data);
2109 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
2110 ROLLBACK TO CREATE_TAI_ACCOUNTING;
2111 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2112 x_msg_count := l_msg_count ;
2113 x_msg_data := l_msg_data ;
2114 Fnd_Msg_Pub.count_and_get(
2115 p_count => x_msg_count
2116 ,p_data => x_msg_data);
2117 WHEN OTHERS THEN
2118 ROLLBACK TO CREATE_TAI_ACCOUNTING;
2119 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2120 x_msg_count := l_msg_count ;
2121 x_msg_data := l_msg_data ;
2122 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_RECON_PVT','CREATE_TAI_ACCOUNTING');
2123 Fnd_Msg_Pub.count_and_get(
2124 p_count => x_msg_count
2125 ,p_data => x_msg_data);
2126
2127 END CREATE_TAI_ACCOUNTING;
2128
2129
2130
2131 PROCEDURE submit_cure_refund_hdr
2132 ( p_api_version IN NUMBER
2133 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_TRUE
2134 ,p_commit IN VARCHAR2 DEFAULT OKC_API.G_FALSE
2135 ,p_refund_header_id IN NUMBER
2136 ,x_return_status OUT NOCOPY VARCHAR2
2137 ,x_msg_count OUT NOCOPY NUMBER
2138 ,x_msg_data OUT NOCOPY VARCHAR2
2139 ) IS
2140
2141 l_init_msg_list VARCHAR2(1);
2142 l_return_status VARCHAR2(1);
2143 l_msg_count NUMBER ;
2144 l_msg_data VARCHAR2(32627);
2145 l_message VARCHAR2(32627);
2146
2147 l_api_name CONSTANT VARCHAR2(50) := 'SUBMIT_CURE_REFUND_HDR';
2148 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'
2149 || l_api_name;
2150 lp_tapv_tbl okl_tap_pvt.tapv_tbl_type;
2151 lx_tapv_tbl okl_tap_pvt.tapv_tbl_type;
2152 lp_chdv_rec okl_chd_pvt.chdv_rec_type;
2153 lx_chdv_rec okl_chd_pvt.chdv_rec_type;
2154 lp_crsv_tbl okl_crs_pvt.crsv_tbl_type;
2155 xp_crsv_tbl okl_crs_pvt.crsv_tbl_type;
2156 lp_taiv_tbl okl_tai_pvt.taiv_tbl_type;
2157 lx_taiv_tbl okl_tai_pvt.taiv_tbl_type;
2158
2159 /* -- OKL.H Code commented out
2160 l_tmpl_identify_rec Okl_Account_Dist_Pvt.TMPL_IDENTIFY_REC_TYPE;
2161 l_dist_info_rec Okl_Account_Dist_Pvt.dist_info_REC_TYPE;
2162 l_ctxt_val_tbl okl_execute_formula_pvt.ctxt_val_tbl_type;
2163 l_acc_gen_primary_key_tbl Okl_Account_Generator_Pvt.primary_key_tbl;
2164 l_template_tbl Okl_Account_Dist_Pub.AVLV_TBL_TYPE;
2165 l_amount_tbl Okl_Account_Dist_Pub.AMOUNT_TBL_TYPE;
2166 */
2167
2168 -- R12 Change - START
2169
2170 l_tmpl_identify_tbl okl_account_dist_pvt.tmpl_identify_tbl_type;
2171 l_dist_info_tbl okl_account_dist_pvt.dist_info_tbl_type;
2172 l_template_tbl okl_account_dist_pvt.avlv_out_tbl_type;
2173 l_amount_tbl okl_account_dist_pvt.amount_out_tbl_type;
2174 l_ctxt_val_tbl okl_account_dist_pvt.ctxt_tbl_type;
2175 l_acc_gen_primary_key_tbl okl_account_dist_pvt.acc_gen_tbl_type;
2176 --start:REM 28-June-2007 cklee
2177 l_fact_synd_code fnd_lookups.lookup_code%TYPE;
2178 l_inv_acct_code okc_rules_b.RULE_INFORMATION1%TYPE;
2179 l_tpl_id okl_txl_ap_inv_lns_all_b.id%type;
2180 --end:REM 28-June-2007 cklee
2181 -- R12 Change - END
2182
2183 cursor c_get_tap_ids (p_cure_refund_header_id IN NUMBER ) is
2184 select crf.tap_id,tap.invoice_number,
2185 crs.object_version_number
2186 ,crs.cure_refund_Stage_id
2187 ,crf.tai_id
2188 from okl_cure_refunds crf,okl_trx_ap_invoices_b tap
2189 ,okl_cure_refund_stage crs
2190 where crf.cure_refund_header_id =p_cure_refund_header_id
2191 and crf.tap_id =tap.id
2192 and crs.cure_refund_stage_id =crf.cure_refund_stage_id;
2193
2194 cursor c_getobj(p_cure_refund_header_id IN NUMBER ) is
2195 select object_version_number from okl_cure_refund_headers_b
2196 where cure_refund_header_id =p_cure_refund_header_id;
2197
2198 next_row integer;
2199 tai_next_row integer;
2200
2201 -- sjalasut, modified the below cursor to have khr_id referred from
2202 -- okl_txl_ap_inv_lns_all_b instead of okl_trx_ap_invoices_b. changes made
2203 -- as part of OKLR12B disbursements project
2204 cursor c_get_accounting(p_cure_refund_header_id IN NUMBER) is
2205 select tap.id tap_id,
2206 tap.try_id try_id,
2207 til.sty_id sty_id,
2208 til.id id,
2209 tap.date_invoiced date_invoiced,
2210 tap.amount amount,
2211 tap.currency_code currency_code,
2212 til.khr_id khr_id
2213 from okl_trx_ap_invoices_b tap,
2214 okl_txl_ap_inv_lns_b til,
2215 okl_cure_refunds crf
2216 where crf.cure_refund_header_id = p_cure_refund_header_id
2217 and tap.id = til.tap_id
2218 and crf.tap_id = tap.id;
2219
2220
2221 CURSOR product_csr (p_chr_id IN NUMBER) IS
2222 SELECT khr.pdt_id
2223 FROM okl_k_headers khr
2224 WHERE khr.id = p_chr_id;
2225
2226 /*---New Code start ---*/
2227 CURSOR curr_csr (l_khr_id NUMBER) IS
2228 SELECT currency_conversion_type,
2229 currency_conversion_rate,
2230 currency_conversion_date
2231 FROM okl_k_headers
2232 WHERE id = l_khr_id;
2233
2234 cursor c_get_contract_currency (l_khr_id IN NUMBER) IS
2235 select currency_code from OKC_K_HEADERS_b
2236 where id =l_khr_id;
2237
2238 l_functional_currency okl_trx_contracts.currency_code%TYPE;
2239 l_currency_conversion_type okl_k_headers.currency_conversion_type%TYPE;
2240 l_currency_conversion_rate okl_k_headers.currency_conversion_rate%TYPE;
2241 l_currency_conversion_date okl_k_headers.currency_conversion_date%TYPE;
2242 l_contract_currency OKC_K_HEADERS_b.currency_code%TYPE;
2243 /*---New Code end ---*/
2244
2245 BEGIN
2246 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refund_hdr : START ');
2247 SAVEPOINT SUBMIT_CURE_REFUND_HDR;
2248
2249 -- Initialize message list if p_init_msg_list is set to TRUE.
2250 IF FND_API.to_Boolean( p_init_msg_list )
2251 THEN
2252 FND_MSG_PUB.initialize;
2253 END IF;
2254
2255 x_return_status := FND_API.G_RET_STS_SUCCESS;
2256
2257 IF (p_refund_header_id IS NULL) THEN
2258 AddMissingArgMsg(
2259 p_api_name => l_api_name_full,
2260 p_param_name => 'cure_refund_header_id' );
2261 RAISE FND_API.G_EXC_ERROR;
2262 END IF;
2263
2264
2265 FOR i in c_get_tap_ids (p_refund_header_id)
2266 LOOP
2267 next_row := nvl(lp_tapv_tbl.LAST,0) +1;
2268 lp_tapv_tbl(next_row).id :=i.tap_id;
2269 lp_tapv_tbl(next_row).trx_status_code :='ENTERED';
2270 lp_tapv_tbl(next_row).vendor_invoice_number := i.invoice_number;
2271 lp_crsv_tbl(next_row).cure_refund_stage_id :=i.cure_refund_stage_id;
2272 lp_crsv_tbl(next_row).status:='SUBMITTED';
2273 lp_crsv_tbl(next_row).object_version_number
2274 :=i.object_version_number;
2275
2276 IF i.tai_id is not null THEN
2277 tai_next_row := nvl(lp_taiv_tbl.LAST,0) +1;
2278 lp_taiv_tbl(tai_next_row).id :=i.tai_id;
2279 lp_taiv_tbl(tai_next_row).trx_status_code :='SUBMITTED';
2280 END IF;
2281
2282 END LOOP;
2283
2284
2285 IF PG_DEBUG <11 THEN
2286 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2287 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'no of records to be updated in TAP'||
2288 lp_tapv_tbl.COUNT);
2289 END IF;
2290 END IF;
2291
2292 okl_trx_ap_invoices_pub.update_trx_ap_invoices(
2293 p_api_version => 1.0
2294 ,p_init_msg_list => 'T'
2295 ,x_return_status => l_return_status
2296 ,x_msg_count => l_msg_count
2297 ,x_msg_data => l_msg_data
2298 ,p_tapv_tbl => lp_tapv_tbl
2299 ,x_tapv_tbl => lx_tapv_tbl);
2300
2301 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refund_hdr : okl_trx_ap_invoices_pub.update_trx_ap_invoices : '||l_return_status);
2302
2303 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2304 Get_Messages (l_msg_count,l_message);
2305 IF PG_DEBUG <11 THEN
2306 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2307 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
2308 END IF;
2309 END IF;
2310 raise FND_API.G_EXC_ERROR;
2311 ELSE
2312 IF PG_DEBUG < 11 THEN
2313 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2314 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Succesfully updated tap records');
2315 END IF;
2316 END IF;
2317 FND_MSG_PUB.initialize;
2318 END IF;
2319
2320
2321 IF lp_taiv_tbl.COUNT > 0 THEN
2322 --Update trx ar invoices
2323 --set error message,so this will be prefixed before the
2324 --actual message, so it makes more sense than displaying an
2325 -- OKL message.
2326
2327
2328 -- ASHIM CHANGE - START
2329
2330
2331 AddfailMsg(
2332 p_object => 'RECORD IN OKL_TRX_AR_INVOICES',
2333 p_operation => 'UPDATE' );
2334
2335 okl_trx_ar_invoices_pub.update_trx_ar_invoices(
2336 p_api_version => 1.0
2337 ,p_init_msg_list => 'T'
2338 ,x_return_status => l_return_status
2339 ,x_msg_count => l_msg_count
2340 ,x_msg_data => l_msg_data
2341 ,p_taiv_tbl => lp_taiv_tbl
2342 ,x_taiv_tbl => lx_taiv_tbl);
2343
2344 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refund_hdr : okl_trx_ar_invoices_pub.update_trx_ar_invoices : '||l_return_status);
2345 -- ASHIM CHANGE - END
2346
2347
2348
2349 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2350 Get_Messages (l_msg_count,l_message);
2351 IF PG_DEBUG <11 THEN
2352 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2353 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
2354 END IF;
2355 END IF;
2356 raise FND_API.G_EXC_ERROR;
2357 ELSE
2358 IF PG_DEBUG < 11 THEN
2359 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2360 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Succesfully updated tai records');
2361 END IF;
2362 END IF;
2363 FND_MSG_PUB.initialize;
2364 END IF;
2365 END IF; -- if tai table count > 0
2366
2367
2368 --Update OKL_CURE_REFUND_STAGE
2369 --set error message,so this will be prefixed before the
2370 --actual message, so it makes more sense than displaying an
2371 -- OKL message.
2372 AddfailMsg(
2373 p_object => 'RECORD IN OKL_CURE_REFUND_STAGE',
2374 p_operation => 'UPDATE' );
2375
2376 OKL_cure_rfnd_stage_pub.update_cure_refunds(
2377 p_api_version => 1.0
2378 ,p_init_msg_list =>'F'
2379 ,x_return_status => l_return_status
2380 ,x_msg_count => l_msg_count
2381 ,x_msg_data => l_msg_data
2382 ,p_crsv_tbl => lp_crsv_tbl
2383 ,x_crsv_tbl => xp_crsv_tbl);
2384
2385 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refund_hdr : OKL_cure_rfnd_stage_pub.update_cure_refunds : '||l_return_status);
2386
2387 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2388 Get_Messages (l_msg_count,l_message);
2389 IF PG_DEBUG < 11 THEN
2390 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2391 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
2392 END IF;
2393 END IF;
2394 raise FND_API.G_EXC_ERROR;
2395 ELSE
2396 IF PG_DEBUG < 11 THEN
2397 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2398 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Success -updated cure refund stage' );
2399 END IF;
2400 END IF;
2401 FND_MSG_PUB.initialize;
2402 END IF;
2403
2404 --create accounting
2405 -- set accounting call required values
2406
2407
2408 -- following call gets the product id for the accounting call
2409 FOR j in c_get_accounting(p_refund_header_id)
2410 LOOP
2411
2412 FOR i IN product_csr (j.khr_id)
2413 LOOP
2414 l_tmpl_identify_tbl(1).product_id := i.pdt_id;
2415 l_acc_gen_primary_key_tbl(1).source_id := j.id; -- cklee 06/29/07
2416 IF PG_DEBUG < 11 THEN
2417 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2418 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'product_id '
2419 ||l_tmpl_identify_tbl(1).product_id);
2420 END IF;
2421 END IF;
2422 END LOOP;
2423
2424 /*--- New Code Start Here ---*/
2425 -- Fetch the functional currency
2426 l_functional_currency := OKL_ACCOUNTING_UTIL.GET_FUNC_CURR_CODE;
2427
2428 -- Fetch the currency conversion factors if functional currency is not equal
2429 -- to the transaction currency
2430
2431 OPEN c_get_contract_currency (j.khr_id);
2432 FETCH c_get_contract_currency INTO l_contract_currency;
2433 CLOSE c_get_contract_currency;
2434
2435 l_dist_info_tbl(1).currency_code := l_contract_currency;
2436
2437 IF l_functional_currency <> l_contract_currency THEN
2438
2439 -- Fetch the currency conversion factors from Contracts
2440 FOR curr_rec IN curr_csr(j.khr_id) LOOP
2441 l_currency_conversion_type := curr_rec.currency_conversion_type;
2442 l_currency_conversion_rate := curr_rec.currency_conversion_rate;
2443 l_currency_conversion_date := curr_rec.currency_conversion_date;
2444 END LOOP;
2445
2446 -- Fetch the currency conversion factors from GL_DAILY_RATES if the
2447 -- conversion type is not 'USER'.
2448
2449 IF UPPER(l_currency_conversion_type) <> 'USER' THEN
2450 l_currency_conversion_date := SYSDATE;
2451 l_currency_conversion_rate := okl_accounting_util.get_curr_con_rate
2452 (p_from_curr_code => l_contract_currency,
2453 p_to_curr_code => l_functional_currency,
2454 p_con_date => l_currency_conversion_date,
2455 p_con_type => l_currency_conversion_type);
2456
2457 END IF; -- End IF for (UPPER(l_currency_conversion_type) <> 'USER')
2458
2459 END IF; -- End IF for (l_functional_currency <> l_contract_currency)
2460
2461 -- Populate the currency conversion factors
2462
2463 l_dist_info_tbl(1).currency_conversion_type := l_currency_conversion_type;
2464 l_dist_info_tbl(1).currency_conversion_rate := l_currency_conversion_rate;
2465 l_dist_info_tbl(1).currency_conversion_date := l_currency_conversion_date;
2466
2467 -- Round the transaction amount
2468 l_dist_info_tbl(1).amount:= okl_accounting_util.cross_currency_round_amount
2469 (p_amount => j.amount,
2470 p_currency_code => l_contract_currency);
2471
2472 l_dist_info_tbl(1).contract_id := j.khr_id;
2473
2474 /*--- New Code End Here ---*/
2475 --start:REM 28-June-2007 cklee
2476 -- We need to call once per khr_id
2477 Okl_Securitization_Pvt.check_khr_ia_associated(
2478 p_api_version => 1.0
2479 ,p_init_msg_list =>'F'
2480 ,x_return_status => l_return_status
2481 ,x_msg_count => l_msg_count
2482 ,x_msg_data => l_msg_data
2483 ,p_khr_id => j.khr_id
2484 ,p_scs_code => NULL
2485 ,p_trx_date => j.date_invoiced
2486 ,x_fact_synd_code => l_fact_synd_code
2487 ,x_inv_acct_code => l_inv_acct_code);
2488
2489 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2490 Get_Messages (l_msg_count,l_message);
2491 IF PG_DEBUG < 11 THEN
2492 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2493 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
2494 END IF;
2495 END IF;
2496 raise FND_API.G_EXC_ERROR;
2497 ELSE
2498 IF PG_DEBUG < 11 THEN
2499 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2500 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Success -Okl_Securitization_Pvt.check_khr_ia_associated' );
2501 END IF;
2502 END IF;
2503 FND_MSG_PUB.initialize;
2504 END IF;
2505 --end:REM 28-June-2007 cklee
2506
2507
2508 l_tmpl_identify_tbl(1).transaction_type_id := j.try_id;
2509 l_tmpl_identify_tbl(1).stream_type_id := j.sty_id;
2510 l_tmpl_identify_tbl(1).advance_arrears := null;
2511 l_tmpl_identify_tbl(1).factoring_synd_flag := l_fact_synd_code;
2512 l_tmpl_identify_tbl(1).investor_code := l_inv_acct_code; -- cklee 06/29/07
2513 l_tmpl_identify_tbl(1).syndication_code := null;
2514 l_tmpl_identify_tbl(1).factoring_code := null;
2515 l_tmpl_identify_tbl(1).memo_yn := 'N';
2516 l_tmpl_identify_tbl(1).prior_year_yn := 'N';
2517
2518 l_dist_info_tbl(1).source_id := j.id;
2519 l_dist_info_tbl(1).source_table := 'OKL_TXL_AP_INV_LNS_B';
2520 l_dist_info_tbl(1).accounting_date := j.date_invoiced;
2521 l_dist_info_tbl(1).gl_reversal_flag :='N';
2522 l_dist_info_tbl(1).post_to_gl :='N';
2523 -- l_dist_info_tbl(1).amount := ABS(j.amount);
2524 l_dist_info_tbl(1).amount := j.amount; --start: 06/04/07 cklee
2525 l_dist_info_tbl(1).currency_code := j.currency_code;
2526 l_dist_info_tbl(1).contract_id := j.khr_id;
2527
2528
2529 --set error message,so this will be prefixed before the
2530 --actual message, so it makes more sense than displaying an
2531 -- OKL message.
2532 -- R12 CHANGE - START
2533 --Do not know what this segment does. Hence commented out,
2534 --will enable if required during test run
2535 -- enabled by cklee 06/29/07
2536
2537 AddfailMsg(
2538 p_object => 'Okl_Acc_Call_Pvt.Okl_Populate_Acc_Gen ',
2539 p_operation => 'CREATE' );
2540
2541
2542 Okl_Acc_Call_Pvt.Okl_Populate_Acc_Gen (
2543 p_contract_id => j.khr_id,
2544 p_contract_line_id => NULL,
2545 x_acc_gen_tbl => l_acc_gen_primary_key_tbl(1).acc_gen_key_tbl,
2546 x_return_status => l_return_status);
2547
2548 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2549 raise FND_API.G_EXC_ERROR;
2550 ELSE
2551 FND_MSG_PUB.initialize;
2552 IF PG_DEBUG < 11 THEN
2553 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2554 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'l_acc_gen_primary_key_tbl'
2555 ||l_acc_gen_primary_key_tbl(1).acc_gen_key_tbl.count
2556 ||l_acc_gen_primary_key_tbl(1).acc_gen_key_tbl(1).primary_key_column
2557 ||l_acc_gen_primary_key_tbl(1).acc_gen_key_tbl(1).source_table );
2558 END IF;
2559 END IF;
2560
2561 END IF;
2562
2563 -- R12 CHANGE - END
2564
2565 --set error message,so this will be prefixed before the
2566 --actual message, so it makes more sense than displaying an
2567 -- OKL message.
2568 AddfailMsg(
2569 p_object => 'Okl_Account_Dist_Pub.CREATE_ACCOUNTING_DIST ',
2570 p_operation => 'CREATE' );
2571
2572 /* OKL.H code commented out
2573 Okl_Account_Dist_Pub.CREATE_ACCOUNTING_DIST(
2574 p_api_version => 1.0
2575 ,p_init_msg_list => 'F'
2576 ,x_return_status => l_return_status
2577 ,x_msg_count => l_msg_count
2578 ,x_msg_data => l_msg_data
2579 ,p_tmpl_identify_rec => l_tmpl_identify_rec
2580 ,p_dist_info_rec => l_dist_info_rec
2581 ,p_ctxt_val_tbl => l_ctxt_val_tbl
2582 ,p_acc_gen_primary_key_tbl => l_acc_gen_primary_key_tbl
2583 ,x_template_tbl => l_template_tbl
2584 ,x_amount_tbl => l_amount_tbl);
2585 */
2586
2587 -- R12 CHANGE - START
2588 okl_account_dist_pvt.create_accounting_dist(
2589 p_api_version => 1.0,
2590 p_init_msg_list => OKL_API.G_FALSE,
2591 x_return_status => l_return_status,
2592 x_msg_count => l_msg_count,
2593 x_msg_data => l_msg_data,
2594 p_tmpl_identify_tbl => l_tmpl_identify_tbl,
2595 p_dist_info_tbl => l_dist_info_tbl,
2596 p_ctxt_val_tbl => l_ctxt_val_tbl,
2597 p_acc_gen_primary_key_tbl => l_acc_gen_primary_key_tbl,
2598 x_template_tbl => l_template_tbl,
2599 x_amount_tbl => l_amount_tbl,
2600 --start: 06/04/07 cklee
2601 -- p_trx_header_id => j.tap_id);
2602 p_trx_header_id => j.tap_id,
2603 p_trx_header_table => 'OKL_TRX_AP_INVOICES_B'); -- cklee 07/06/07
2604 --end: 06/04/07 cklee
2605 -- R12 CHANGE - END
2606 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refund_hdr : okl_account_dist_pvt.create_accounting_dist : '||l_return_status);
2607
2608 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2609 Get_Messages (l_msg_count,l_message);
2610 IF PG_DEBUG < 11 THEN
2611 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2612 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
2613 END IF;
2614 END IF;
2615 raise FND_API.G_EXC_ERROR;
2616 ELSE
2617 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2618 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'count of l_template_tbl for tap '||l_template_tbl.count);
2619 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'count of l_amount_tbl for tap '||l_amount_tbl.count);
2620 END IF;
2621 FND_MSG_PUB.initialize;
2622 END IF;
2623
2624
2625 END LOOP; -- for c_get_accounting cursor
2626
2627
2628 -- ASHIM CHANGE - START
2629
2630
2631
2632 --create accounting for tai lines
2633 CREATE_TAI_ACCOUNTING(p_cure_refund_header_id =>p_refund_header_id,
2634 x_return_status =>l_return_status,
2635 x_msg_count =>l_msg_count,
2636 x_msg_data =>l_msg_data);
2637
2638 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refund_hdr : CREATE_TAI_ACCOUNTING : '||l_return_status);
2639
2640
2641 -- ASHIM CHANGE - END
2642
2643
2644 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2645 Get_Messages (l_msg_count,l_message);
2646 IF PG_DEBUG < 11 THEN
2647 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2648 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
2649 END IF;
2650 END IF;
2651 raise FND_API.G_EXC_ERROR;
2652 ELSE
2653 IF PG_DEBUG < 11 THEN
2654 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2655 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Success -created accounting for tai records');
2656 END IF;
2657 END IF;
2658 FND_MSG_PUB.initialize;
2659 END IF;
2660
2661 IF PG_DEBUG <11 THEN
2662 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2663 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Before updating cure refund header table');
2664 END IF;
2665 END IF;
2666
2667 --set error message,so this will be prefixed before the
2668 --actual message, so it makes more sense than displaying an
2669 -- OKL message.
2670 AddfailMsg(
2671 p_object => 'RECORD IN OKL_CURE_REFUND_HEADERS ',
2672 p_operation => 'UPDATE' );
2673
2674 lp_chdv_rec.cure_refund_header_id :=p_refund_header_id;
2675 lp_chdv_rec.refund_status :='APPROVED';
2676
2677 OPEN c_getobj(p_refund_header_id);
2678 FETCH c_getobj INTO lp_chdv_rec.object_version_number;
2679 CLOSE c_getobj;
2680
2681 OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr(
2682 p_api_version => 1.0
2683 ,p_init_msg_list => 'F'
2684 ,x_return_status => l_return_status
2685 ,x_msg_count => l_msg_count
2686 ,x_msg_data => l_msg_data
2687 ,p_chdv_rec => lp_chdv_rec
2688 ,x_chdv_rec => lx_chdv_rec);
2689
2690 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refund_hdr : OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr : '||l_return_status);
2691
2692 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2693 Get_Messages (l_msg_count,l_message);
2694 IF PG_DEBUG < 11 THEN
2695 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2696 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
2697 END IF;
2698 END IF;
2699 raise FND_API.G_EXC_ERROR;
2700 ELSE
2701 IF PG_DEBUG < 11 THEN
2702 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2703 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Successfully updated Cure refund '||
2704 'header table');
2705 END IF;
2706 END IF;
2707
2708 END IF;
2709
2710
2711 IF x_return_status =FND_API.G_RET_STS_SUCCESS THEN
2712 FND_MSG_PUB.initialize;
2713 END IF;
2714
2715
2716 -- Standard check for p_commit
2717 IF FND_API.to_Boolean( p_commit )
2718 THEN
2719 COMMIT WORK;
2720 END IF;
2721
2722 FND_MSG_PUB.Count_And_Get
2723 ( p_count => x_msg_count,
2724 p_data => x_msg_data
2725 );
2726
2727 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refund_hdr : END ');
2728 EXCEPTION
2729
2730 WHEN Fnd_Api.G_EXC_ERROR THEN
2731 ROLLBACK TO SUBMIT_CURE_REFUND_HDR;
2732 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2733 x_msg_count := l_msg_count ;
2734 x_msg_data := l_msg_data ;
2735 Fnd_Msg_Pub.count_and_get(
2736 p_count => x_msg_count
2737 ,p_data => x_msg_data);
2738 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
2739 ROLLBACK TO SUBMIT_CURE_REFUND_HDR;
2740 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2741 x_msg_count := l_msg_count ;
2742 x_msg_data := l_msg_data ;
2743 Fnd_Msg_Pub.count_and_get(
2744 p_count => x_msg_count
2745 ,p_data => x_msg_data);
2746 WHEN OTHERS THEN
2747 ROLLBACK TO SUBMIT_CURE_REFUND_HDR;
2748 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2749 x_msg_count := l_msg_count ;
2750 x_msg_data := l_msg_data ;
2751 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_PAY_CURE_REFUNDS_PVT','SUBMIT_CURE_REFUND_HDR');
2752 Fnd_Msg_Pub.count_and_get(
2753 p_count => x_msg_count
2754 ,p_data => x_msg_data);
2755
2756
2757
2758 END submit_cure_refund_hdr;
2759
2760 --07/01/03
2761 -- Send a notification to a vendor indicating about
2762 -- the offset contract/credit memo ( if there is any)
2763 -- Populate the Role for the notification
2764 -- Check in workflow if role is populated THEN
2765 -- send notification
2766
2767 PROCEDURE GET_ROLE(
2768 p_refund_header_id IN NUMBER
2769 ,x_role OUT NOCOPY VARCHAR2
2770 ,x_return_status OUT NOCOPY VARCHAR2
2771 ,x_msg_count OUT NOCOPY NUMBER
2772 ,x_msg_data OUT NOCOPY VARCHAR2 )
2773
2774 IS
2775
2776 l_init_msg_list VARCHAR2(1);
2777 l_return_status VARCHAR2(1);
2778 l_msg_count NUMBER ;
2779 l_msg_data VARCHAR2(32627);
2780 l_message VARCHAR2(32627);
2781
2782 l_api_name CONSTANT VARCHAR2(50) := 'GET_ROLE';
2783 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'||l_api_name ;
2784
2785
2786 l_email po_vendor_sites.email_address%TYPE;
2787 l_role_prefix VARCHAR2(10) := 'OKLVENDOR_';
2788 l_role_name VARCHAR2(30);
2789 l_role_display_name po_vendor_sites.vendor_site_code%TYPE;
2790 l_role_exists NUMBER;
2791
2792
2793 /*
2794 l_notification_pref wf_local_users.notification_preference%TYPE;
2795 l_lang wf_local_users.language%TYPE;
2796 l_territory wf_local_users.territory%TYPE;
2797 */
2798
2799
2800 Cursor c_vendor_info (p_refund_header_id IN NUMBER) Is
2801 select pos.vendor_site_id,
2802 pos.vendor_site_code,
2803 pos.email_address,
2804 crh.cure_refund_header_id
2805 from po_vendor_sites pos,
2806 okl_cure_refund_headers_b crh,
2807 okl_cure_refunds crl
2808 where crh.vendor_site_id =pos.vendor_site_id
2809 and crh.cure_refund_header_id=p_refund_header_id
2810 and rownum <2;
2811
2812 BEGIN
2813 IF (G_DEBUG_ENABLED = 'Y') THEN
2814 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2815 END IF;
2816
2817 x_return_status := FND_API.G_RET_STS_SUCCESS;
2818 SAVEPOINT GET_ROLE;
2819
2820 FOR i in c_vendor_info(p_refund_header_id)
2821 LOOP
2822 l_role_name := l_role_prefix||i.vendor_site_id;
2823 l_role_display_name :=i.vendor_site_code;
2824 l_email :=i.email_address;
2825 END LOOP;
2826
2827 IF PG_DEBUG < 11 THEN
2828 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2829 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Email Address ' ||l_email ||
2830 ' Role ' ||l_role_name||
2831 ' Role Display ' ||l_role_display_name);
2832 END IF;
2833 END IF;
2834
2835 --set error stack
2836 IF l_email is NULL THEN
2837 fnd_message.set_name('OKL', 'OKL_MISSING_EMAIL_ID');
2838 fnd_msg_pub.add;
2839 RAISE FND_API.G_EXC_ERROR;
2840 END IF;
2841
2842 -- check if role exists, otherwise create a new role
2843 -- wf_local_roles using WF_DIRECTORY.CreateAdHocRole
2844
2845 /* WF_DIRECTORY.GetRoleInfo
2846 (Role =>l_role_name,
2847 Display_Name =>l_role_display_name,
2848 Email_Address =>l_email,
2849 Notification_Preference =>l_notification_pref,
2850 Language =>l_lang,
2851 Territory =>l_territory
2852 );
2853 */
2854 --- assumption is wf_local_roles is a public table
2855 select count(1)
2856 into l_role_exists
2857 from WF_LOCAL_ROLES
2858 where name = l_role_name;
2859
2860 if l_role_exists = 0 then
2861 --create ad hoc role
2862 WF_DIRECTORY.CreateAdHocRole
2863 ( role_name =>l_role_name,
2864 role_display_name =>l_role_display_name,
2865 notification_preference =>'MAILHTML',
2866 email_address =>l_email,
2867 status =>'ACTIVE',
2868 expiration_date =>to_DATE(NULL));
2869
2870 ELSE
2871 x_role:=l_role_name;
2872 end if;
2873
2874 FND_MSG_PUB.Count_And_Get
2875 ( p_count => x_msg_count,
2876 p_data => x_msg_data
2877 );
2878
2879 EXCEPTION
2880 WHEN Fnd_Api.G_EXC_ERROR THEN
2881 ROLLBACK TO GET_ROLE;
2882 x_return_status := Fnd_Api.G_RET_STS_ERROR;
2883 x_msg_count := l_msg_count ;
2884 x_msg_data := l_msg_data ;
2885 Fnd_Msg_Pub.count_and_get(
2886 p_count => x_msg_count
2887 ,p_data => x_msg_data);
2888
2889 WHEN OTHERS THEN
2890 ROLLBACK TO GET_ROLE;
2891 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2892 x_msg_count := l_msg_count ;
2893 x_msg_data := l_msg_data ;
2894 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_PAY_CURE_REFUNDS_PVT','GET_ROLE');
2895 Fnd_Msg_Pub.count_and_get(
2896 p_count => x_msg_count
2897 ,p_data => x_msg_data);
2898
2899 End GET_ROLE;
2900
2901
2902 PROCEDURE invoke_refund_wf(
2903 p_refund_header_id IN NUMBER
2904 ,x_return_status OUT NOCOPY VARCHAR2
2905 ,x_msg_count OUT NOCOPY NUMBER
2906 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
2907
2908 l_parameter_list wf_parameter_list_t;
2909 l_key VARCHAR2(240);
2910 l_seq NUMBER;
2911 l_event_name varchar2(240) := 'oracle.apps.okl.co.approverefund';
2912
2913 l_init_msg_list VARCHAR2(1);
2914 l_return_status VARCHAR2(1);
2915 l_msg_count NUMBER ;
2916 l_msg_data VARCHAR2(32627);
2917 l_message VARCHAR2(32627);
2918
2919 l_api_name CONSTANT VARCHAR2(50) := 'INVOKE_REFUND_WF';
2920 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'
2921 || l_api_name;
2922 -- Selects the nextval from sequence, used later for defining event key
2923 CURSOR okl_key_csr IS
2924 SELECT okl_wf_item_s.nextval
2925 FROM dual;
2926
2927 cursor c_get_ref_details (p_refund_header_id IN NUMBER)
2928 is select crh.refund_header_number
2929 ,crh.disbursement_amount
2930 ,pov.vendor_name
2931 from okl_cure_refund_headers_b crh,
2932 po_vendors pov,
2933 po_vendor_sites_All povs
2934 where crh.vendor_site_id =povs.vendor_site_id
2935 and pov.vendor_id =povs.vendor_id
2936 and crh.cure_refund_header_id =p_refund_header_id;
2937
2938 l_refund_amount okl_cure_refund_headers_b.disbursement_amount%TYPE;
2939 l_refund_number okl_cure_refund_headers_b.refund_header_number%TYPE;
2940
2941 l_vendor_name po_vendors.vendor_name%TYPE;
2942 l_notification_agent varchar2(100) := 'SYSADMIN';
2943
2944 cursor c_get_agent(p_user_id IN NUMBER) is
2945 select wfr.name
2946 from fnd_user fuser,wf_roles wfr
2947 where orig_system = 'PER'
2948 and wfr.orig_system_id =fuser.employee_id
2949 and fuser.user_id =p_user_id;
2950
2951
2952 l_user_id NUMBER := to_number(fnd_profile.value('OKL_REFUND_APPROVAL_USER'));
2953
2954 --vendor role
2955 l_role_name VARCHAR2(30);
2956 l_offset_count NUMBER;
2957
2958 BEGIN
2959 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: invoke_refund_wf : START ');
2960 IF (G_DEBUG_ENABLED = 'Y') THEN
2961 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
2962 END IF;
2963
2964 x_return_status := FND_API.G_RET_STS_SUCCESS;
2965 --set error message,so this will be prefixed before the
2966 --actual message, so it makes more sense than displaying an
2967 -- OKL message.
2968 /*AddfailMsg(
2969 p_object => 'BEFORE CALLING WORKFLOW ',
2970 p_operation => 'CREATE' );
2971 */
2972 SAVEPOINT INVOKE_REFUND_WF;
2973 OPEN okl_key_csr;
2974 FETCH okl_key_csr INTO l_seq;
2975 CLOSE okl_key_csr;
2976 l_key := l_event_name ||'-'||l_seq;
2977
2978 IF PG_DEBUG < 11 THEN
2979 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
2980 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Event Key ' ||l_key);
2981 END IF;
2982 END IF;
2983
2984 OPEN c_get_ref_details (p_refund_header_id );
2985
2986 FETCH c_get_ref_details INTO l_refund_number,
2987 l_refund_amount,
2988 l_vendor_name;
2989
2990 CLOSE c_get_ref_details;
2991
2992 OPEN c_get_agent (l_user_id);
2993 FETCH c_get_agent INTO l_notification_agent;
2994 CLOSE c_get_Agent;
2995
2996 IF l_notification_agent IS NULL THEN
2997 l_notification_agent := 'SYSADMIN';
2998 END IF;
2999
3000
3001 wf_event.AddParameterToList('NOTIFY_AGENT',
3002 l_notification_agent,
3003 l_parameter_list);
3004
3005 wf_event.AddParameterToList('CURE_REFUND_HEADER_ID',
3006 to_char(p_refund_header_id),
3007 l_parameter_list);
3008
3009 wf_event.AddParameterToList('REFUND_AMOUNT',
3010 to_char(l_refund_amount),
3011 l_parameter_list);
3012
3013 wf_event.AddParameterToList('VENDOR_NAME',
3014 l_vendor_name,
3015 l_parameter_list);
3016
3017 wf_event.AddParameterToList('REFUND_NUMBER',
3018 l_refund_number,
3019 l_parameter_list);
3020
3021
3022
3023 --07/01/03
3024 -- Send a notification to a vendor indicating about
3025 -- the offset contract/credit memo ( if there is any)
3026 -- Populate the Role for the notification
3027 -- Check in workflow if role is populated THEN
3028 -- send notification
3029
3030 -- getrole if there are offset contracts
3031 --jsanju 10/31
3032
3033 select count(*) into l_offset_count
3034 from okl_cure_refunds
3035 where offset_contract is not null
3036 and cure_refund_header_id =p_refund_header_id;
3037
3038 IF l_offset_count >0 THEN
3039
3040 GET_ROLE(p_refund_header_id =>p_refund_header_id,
3041 x_role =>l_role_name
3042 ,x_return_status =>l_return_status
3043 ,x_msg_count =>l_msg_count
3044 ,x_msg_data =>l_msg_data);
3045
3046
3047 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3048 Get_Messages (l_msg_count,l_message);
3049 IF PG_DEBUG < 11 THEN
3050 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3051 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
3052 END IF;
3053 END IF;
3054 raise FND_API.G_EXC_ERROR;
3055 END IF;
3056 END IF;
3057
3058 --set Attribute
3059 wf_event.AddParameterToList('VENDOR_ROLE',
3060 l_role_name,
3061 l_parameter_list);
3062
3063 --added by akrangan
3064 wf_event.AddParameterToList('ORG_ID',mo_global.get_current_org_id ,l_parameter_list);
3065
3066
3067 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3068 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'before launching workflow');
3069
3070 END IF;
3071 wf_event.raise(p_event_name => l_event_name
3072 ,p_event_key => l_key
3073 ,p_parameters => l_parameter_list);
3074
3075 COMMIT ;
3076 l_parameter_list.DELETE;
3077
3078
3079
3080 IF PG_DEBUG < 11 THEN
3081 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3082 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Successfully launched Cure refund workflow');
3083 END IF;
3084 END IF;
3085
3086
3087 FND_MSG_PUB.Count_And_Get
3088 ( p_count => x_msg_count,
3089 p_data => x_msg_data
3090 );
3091 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: invoke_refund_wf : END ');
3092 EXCEPTION
3093 WHEN Fnd_Api.G_EXC_ERROR THEN
3094 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: invoke_refund_wf : Fnd_Api.G_EXC_ERROR ');
3095 ROLLBACK TO INVOKE_REFUND_WF;
3096 x_return_status := Fnd_Api.G_RET_STS_ERROR;
3097 x_msg_count := l_msg_count ;
3098 x_msg_data := l_msg_data ;
3099 Fnd_Msg_Pub.count_and_get(
3100 p_count => x_msg_count
3101 ,p_data => x_msg_data);
3102
3103 WHEN OTHERS THEN
3104 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: invoke_refund_wf : OTHERS ');
3105 ROLLBACK TO INVOKE_REFUND_WF;
3106 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3107 x_msg_count := l_msg_count ;
3108 x_msg_data := l_msg_data ;
3109 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_PAY_CURE_REFUNDS_PVT','INVOKE_REFUND_WF');
3110 Fnd_Msg_Pub.count_and_get(
3111 p_count => x_msg_count
3112 ,p_data => x_msg_data);
3113
3114 End invoke_refund_wf;
3115
3116
3117 ----------------------------------------------------------------------------------
3118 -- Start of comments
3119 --
3120 -- Procedure Name : additional_tld_attr
3121 -- Description : Internal procedure to add additional columns for
3122 -- OKL_TXD_AR_LN_DTLS_B
3123 -- Business Rules :
3124 -- Parameters :
3125 --
3126 -- Version : 1.0
3127 -- End of comments
3128 ----------------------------------------------------------------------------------
3129 PROCEDURE additional_tld_attr(
3130 p_api_version IN NUMBER
3131 ,p_init_msg_list IN VARCHAR2 DEFAULT OKL_API.G_FALSE
3132 ,x_return_status OUT NOCOPY VARCHAR2
3133 ,x_msg_count OUT NOCOPY NUMBER
3134 ,x_msg_data OUT NOCOPY VARCHAR2
3135 ,p_tldv_rec IN okl_tld_pvt.tldv_rec_type
3136 ,x_tldv_rec OUT NOCOPY okl_tld_pvt.tldv_rec_type
3137 )
3138 is
3139 l_api_name CONSTANT VARCHAR2(30) := 'additional_tld_attr';
3140 l_api_version CONSTANT NUMBER := 1.0;
3141 l_return_status VARCHAR2(1) := OKL_API.G_RET_STS_SUCCESS;
3142 /*
3143 l_recv_inv_id NUMBER;
3144 CURSOR reverse_csr1(p_tld_id NUMBER) IS
3145 SELECT receivables_invoice_id
3146 FROM okl_txd_ar_ln_dtls_v
3147 WHERE id = p_tld_id;
3148
3149 CURSOR reverse_csr2(p_til_id NUMBER) IS
3150 SELECT receivables_invoice_id
3151 FROM okl_txl_ar_inv_lns_v
3152 WHERE id = p_til_id;
3153
3154
3155 -- Get currency attributes
3156 CURSOR l_curr_csr(cp_currency_code VARCHAR2) IS
3157 SELECT c.minimum_accountable_unit,
3158 c.PRECISION
3159 FROM fnd_currencies c
3160 WHERE c.currency_code = cp_currency_code;
3161 */
3162 -- Get currency attributes
3163 CURSOR l_curr_csr(p_khr_id number) IS
3164 SELECT c.minimum_accountable_unit,
3165 c.PRECISION
3166 FROM fnd_currencies c,
3167 okl_trx_ar_invoices_b b
3168 WHERE c.currency_code = b.currency_code
3169 AND b.khr_id = p_khr_id;
3170
3171
3172 l_min_acct_unit fnd_currencies.minimum_accountable_unit%TYPE;
3173 l_precision fnd_currencies.PRECISION %TYPE;
3174
3175 l_rounded_amount OKL_TXD_AR_LN_DTLS_B.amount%TYPE;
3176
3177 -- to get inventory_org_id bug 4890024 begin
3178 CURSOR inv_org_id_csr(p_contract_id NUMBER) IS
3179 SELECT NVL(inv_organization_id, -99)
3180 FROM okc_k_headers_b
3181 WHERE id = p_contract_id;
3182
3183 begin
3184 -- Set API savepoint
3185 SAVEPOINT additional_tld_attr;
3186 IF (G_DEBUG_ENABLED = 'Y') THEN
3187 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
3188 END IF;
3189 --Print Input Variables
3190 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3191 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'p_tldv_rec.id :'||p_tldv_rec.id);
3192 END IF;
3193 -- Check for call compatibility
3194 IF (NOT FND_API.Compatible_API_Call (l_api_version,
3195 p_api_version,
3196 l_api_name,
3197 G_PKG_NAME ))
3198 THEN
3199 RAISE OKL_API.G_EXCEPTION_UNEXPECTED_ERROR;
3200 END IF;
3201
3202 -- Initialize message list if requested
3203 IF (FND_API.to_Boolean(p_init_msg_list)) THEN
3204 FND_MSG_PUB.initialize;
3205 END IF;
3206
3207 -- Initialize API status to success
3208 x_return_status := OKL_API.G_RET_STS_SUCCESS;
3209
3210
3211 /*** Begin API body ****************************************************/
3212 -- assign all passed in attributes from IN to OUT record
3213 x_tldv_rec := p_tldv_rec;
3214 /* For R12, okl_arfetch_pub is absolete, so the following logic won't work
3215 since the receivable_invoice_id is null
3216 --For Credit Memo Processing
3217 IF p_tldv_rec.tld_id_reverses IS NOT NULL THEN
3218 -- Null out variables
3219 l_recv_inv_id := NULL;
3220
3221 OPEN reverse_csr1(p_tldv_rec.tld_id_reverses);
3222 FETCH reverse_csr1
3223 INTO l_recv_inv_id;
3224 CLOSE reverse_csr1;
3225 x_tldv_rec.reference_line_id := l_recv_inv_id;
3226 ELSE
3227 x_tldv_rec.reference_line_id := NULL;
3228 END IF;
3229
3230 x_tldv_rec.receivables_invoice_id := NULL;
3231 -- Populated later by fetch
3232 */
3233
3234 IF(p_tldv_rec.inventory_org_id IS NULL) THEN
3235
3236 OPEN inv_org_id_csr(p_tldv_rec.khr_id);
3237 FETCH inv_org_id_csr
3238 INTO x_tldv_rec.inventory_org_id;
3239 CLOSE inv_org_id_csr;
3240 ELSE
3241 x_tldv_rec.inventory_org_id := p_tldv_rec.inventory_org_id;
3242 END IF;
3243
3244 -- Bug 4890024 end
3245
3246 -------- Rounded Amount --------------
3247 l_rounded_amount := NULL;
3248 l_min_acct_unit := NULL;
3249 l_precision := NULL;
3250
3251 OPEN l_curr_csr(p_tldv_rec.khr_id);
3252 FETCH l_curr_csr
3253 INTO l_min_acct_unit,
3254 l_precision;
3255 CLOSE l_curr_csr;
3256
3257 IF(NVL(l_min_acct_unit, 0) <> 0) THEN
3258 -- Round the amount to the nearest Min Accountable Unit
3259 l_rounded_amount := ROUND(p_tldv_rec.amount / l_min_acct_unit) * l_min_acct_unit;
3260
3261 ELSE
3262 -- Round the amount to the nearest precision
3263 l_rounded_amount := ROUND(p_tldv_rec.amount, l_precision);
3264 END IF;
3265 -------- Rounded Amount --------------
3266 x_tldv_rec.amount := l_rounded_amount;
3267 --TIL
3268 /*** End API body ******************************************************/
3269
3270 -- Get message count and if count is 1, get message info
3271 FND_MSG_PUB.Count_And_Get
3272 (p_count => x_msg_count,
3273 p_data => x_msg_data);
3274
3275 EXCEPTION
3276 WHEN OKL_API.G_EXCEPTION_ERROR THEN
3277 ROLLBACK TO additional_tld_attr;
3278 x_return_status := OKL_API.G_RET_STS_ERROR;
3279 FND_MSG_PUB.Count_And_Get
3280 (p_count => x_msg_count,
3281 p_data => x_msg_data);
3282
3283 WHEN OKL_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
3284 ROLLBACK TO additional_tld_attr;
3285 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR;
3286 FND_MSG_PUB.Count_And_Get
3287 (p_count => x_msg_count,
3288 p_data => x_msg_data);
3289
3290 WHEN OTHERS THEN
3291 ROLLBACK TO additional_tld_attr;
3292 x_return_status := OKL_API.G_RET_STS_UNEXP_ERROR ;
3293 OKL_API.Set_Message(p_app_name => G_APP_NAME,
3294 p_msg_name => G_UNEXPECTED_ERROR,
3295 p_token1 => G_SQLCODE_TOKEN,
3296 p_token1_value => SQLCODE,
3297 p_token2 => G_SQLERRM_TOKEN,
3298 p_token2_value => SQLERRM);
3299 FND_MSG_PUB.Count_And_Get
3300 (p_count => x_msg_count,
3301 p_data => x_msg_data);
3302
3303 end additional_tld_attr;
3304
3305
3306
3307
3308
3309 PROCEDURE create_credit_memo
3310 (p_contract_id IN NUMBER
3311 ,p_cure_refund_id IN NUMBER
3312 ,p_amount IN NUMBER
3313 ,x_return_status OUT NOCOPY VARCHAR2
3314 ,x_msg_count OUT NOCOPY NUMBER
3315 ,x_msg_data OUT NOCOPY VARCHAR2 ) IS
3316
3317 l_init_msg_list VARCHAR2(1);
3318 l_return_status VARCHAR2(1);
3319 l_msg_count NUMBER ;
3320 l_msg_data VARCHAR2(32627);
3321 l_message VARCHAR2(32627);
3322 l_api_name CONSTANT VARCHAR2(50) := 'CREATE_CREDIT_MEMO';
3323 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'
3324 || l_api_name;
3325
3326 lp_taiv_rec okl_tai_pvt.taiv_rec_type;
3327 xp_taiv_rec okl_tai_pvt.taiv_rec_type;
3328 lp_tilv_rec okl_til_pvt.tilv_rec_type;
3329 xp_tilv_rec okl_til_pvt.tilv_rec_type;
3330 lp_tldv_rec okl_tld_pvt.tldv_rec_type;
3331 xp_tldv_rec okl_tld_pvt.tldv_rec_type;
3332
3333
3334 CURSOR get_trx_id IS
3335 SELECT id FROM okl_trx_types_tl
3336 WHERE name = 'Credit Memo' AND LANGUAGE = USERENV('LANG');
3337 --WHERE name = 'Billing' AND LANGUAGE = USERENV('LANG');
3338
3339
3340
3341 /*
3342 CURSOR get_sty_id IS
3343 SELECT sty.id
3344 FROM okl_strm_type_tl styt, okl_strm_type_b sty
3345 WHERE styt.name = 'CURE' AND styt.language = 'US'
3346 AND sty.id = styt.id AND sty.start_date <= TRUNC(SYSDATE)
3347 AND NVL(sty.end_date, SYSDATE) >= TRUNC(SYSDATE);
3348 */
3349
3350 CURSOR l_rcpt_mthd_csr (cp_cust_rct_mthd IN NUMBER) IS
3351 SELECT c.receipt_method_id
3352 FROM ra_cust_receipt_methods c
3353 WHERE c.cust_receipt_method_id = cp_cust_rct_mthd;
3354
3355 CURSOR l_site_use_csr (
3356 cp_site_use_id IN NUMBER,
3357 cp_site_use_code IN VARCHAR2) IS
3358 SELECT a.cust_account_id cust_account_id,
3359 a.cust_acct_site_id cust_acct_site_id,
3360 a.payment_term_id payment_term_id
3361 FROM okx_cust_site_uses_v a,
3362 okx_customer_accounts_v c
3363 WHERE a.id1 = cp_site_use_id
3364 AND a.site_use_code = cp_site_use_code
3365 AND c.id1 = a.cust_account_id;
3366
3367 l_site_use_rec l_site_use_csr%ROWTYPE;
3368
3369 CURSOR l_std_terms_csr (
3370 cp_cust_id IN NUMBER,
3371 cp_site_use_id IN NUMBER) IS
3372 SELECT c.standard_terms standard_terms
3373 FROM hz_customer_profiles c
3374 WHERE c.cust_account_id = cp_cust_id
3375 AND c.site_use_id = cp_site_use_id
3376 UNION
3377 SELECT c1.standard_terms standard_terms
3378 FROM hz_customer_profiles c1
3379 WHERE c1.cust_account_id = cp_cust_id
3380 AND c1.site_use_id IS NULL
3381 AND NOT EXISTS (
3382 SELECT '1'
3383 FROM hz_customer_profiles c2
3384 WHERE c2.cust_account_id = cp_cust_id
3385 AND c2.site_use_id = cp_site_use_id);
3386
3387
3388 cursor c_program_id (p_contract_id IN NUMBER ) IS
3389 select khr_id from okl_k_headers where id= p_contract_id;
3390
3391
3392 l_program_id okl_k_headers.khr_id%TYPE;
3393
3394 l_id1 VARCHAR2(40) :=NULL;
3395 l_id2 VARCHAR2(200) :=NULL;
3396 l_rule_value VARCHAR2(2000):=NULL;
3397
3398
3399 l_btc_id NUMBER;
3400
3401 cursor c_getobj_ver(p_cure_refund_id IN NUMBER ) is
3402 select object_version_number from okl_cure_refunds
3403 where cure_refund_id =p_cure_refund_id;
3404 lp_crfv_rec okl_crf_pvt.crfv_rec_type;
3405 lx_crfv_rec okl_crf_pvt.crfv_rec_type;
3406
3407 -- Code segment for Customer Account/bill to address
3408 -- as mentioned in OKC Rules Migration HLD
3409 -- Start
3410
3411 CURSOR bill_to_csr (p_program_id IN NUMBER) IS
3412 select BILL_TO_SITE_USE_ID
3413 from okc_k_party_roles_b
3414 where dnz_chr_id = p_program_id
3415 and RLE_CODE ='OKL_VENDOR';
3416
3417 -- Code segment for Customer Account/bill to address
3418 -- as mentioned in OKC Rules Migration HLD
3419 -- End
3420
3421 l_bill_to_address_id NUMBER;
3422 x_primary_sty_id NUMBER;
3423
3424 BEGIN
3425 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_credit_memo : START ');
3426 SAVEPOINT CREATE_CREDIT_MEMO;
3427 -- Initialize message list if p_init_msg_list is set to TRUE.
3428 FND_MSG_PUB.initialize;
3429
3430
3431 x_return_status := FND_API.G_RET_STS_SUCCESS;
3432
3433 /**logic
3434 1) create tai * TIL
3435 **/
3436
3437 --INSERT okl_trx_ar_invoices_b
3438 --set error message,so this will be prefixed before the
3439 --actual message, so it makes more sense than displaying an
3440 -- OKL message.
3441 AddfailMsg(
3442 p_object => 'RECORD IN OKL_TRX_AR_INVOICES ',
3443 p_operation => 'INSERT' );
3444
3445 lp_taiv_rec.khr_id := p_contract_id;
3446
3447 OPEN get_trx_id;
3448 FETCH get_trx_id INTO lp_taiv_rec.try_id;
3449 CLOSE get_trx_id;
3450
3451 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3452 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'trxid '||lp_taiv_rec.try_id);
3453
3454 END IF;
3455 IF lp_taiv_rec.try_id IS NULL THEN
3456 OKL_API.SET_MESSAGE (
3457 p_app_name => 'OKL',
3458 p_msg_name => 'OKL_REQUIRED_VALUE',
3459 p_token1 => 'COL_NAME',
3460 p_token1_value => 'Transaction Type');
3461 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3462 END IF;
3463
3464 /*
3465 OPEN get_sty_id;
3466 FETCH get_sty_id INTO lp_tilv_rec.sty_id ;
3467 CLOSE get_sty_id;
3468 */
3469 OKL_STREAMS_UTIL.get_primary_stream_type(
3470 p_khr_id => p_contract_id,
3471 p_primary_sty_purpose => 'CURE',
3472 x_return_status => l_return_status,
3473 x_primary_sty_id => x_primary_sty_id
3474 );
3475
3476 lp_tilv_rec.sty_id := x_primary_sty_id;
3477 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3478 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'sty_id '||lp_tilv_rec.sty_id);
3479
3480 END IF;
3481 IF lp_tilv_rec.sty_id IS NULL THEN
3482 OKL_API.SET_MESSAGE (
3483 p_app_name => 'OKL',
3484 p_msg_name => 'OKL_REQUIRED_VALUE',
3485 p_token1 => 'COL_NAME',
3486 p_token1_value => 'Transaction Type');
3487 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3488 END IF;
3489 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_credit_memo : lp_tilv_rec.sty_id : '||lp_tilv_rec.sty_id);
3490 -- need to populate 4 fields. so that cure invoice gets
3491 --generated for vendor and not for the customer
3492 -- ibt_id,ixx_id,irm_id,irt_id
3493 --get cust_account from rule vendor billing set up
3494
3495 OPEN c_program_id(lp_taiv_rec.khr_id);
3496 FETCH c_program_id INTO l_program_id;
3497 CLOSE c_program_id;
3498 IF l_program_id IS NULL THEN
3499 OKL_API.SET_MESSAGE (
3500 p_app_name => 'OKL',
3501 p_msg_name => 'OKL_REQUIRED_VALUE',
3502 p_token1 => 'COL_NAME',
3503 p_token1_value => 'Vendor Program');
3504 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3505 END IF;
3506 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3507 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'program Id' ||l_program_id);
3508
3509
3510 END IF;
3511
3512 -- New code for bill to address START
3513 OPEN bill_to_csr (l_program_id);
3514 FETCH bill_to_csr INTO l_bill_to_address_id;
3515 CLOSE bill_to_csr;
3516
3517 IF trunc(l_bill_to_address_id) IS NULL THEN
3518 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3519 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Retrieval of Bill To Address Id failed');
3520 END IF;
3521 OKL_API.SET_MESSAGE (
3522 p_app_name => 'OKL',
3523 p_msg_name => 'OKL_REQUIRED_VALUE',
3524 p_token1 => 'COL_NAME',
3525 p_token1_value => 'Bill To Address Id');
3526 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3527 END IF;
3528 l_btc_id :=l_bill_to_address_id;
3529 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3530 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Bill to address id from rule is ' || l_btc_id);
3531
3532 END IF;
3533 -- *****************************************************
3534 -- Extract Customer, Bill To and Payment Term from rules
3535 -- *****************************************************
3536
3537 OPEN l_site_use_csr (l_btc_id, 'BILL_TO');
3538 FETCH l_site_use_csr INTO l_site_use_rec;
3539 CLOSE l_site_use_csr;
3540
3541 lp_taiv_rec.ibt_id := l_site_use_rec.cust_acct_site_id;
3542 lp_taiv_rec.ixx_id := l_site_use_rec.cust_account_id;
3543 lp_taiv_rec.irt_id := l_site_use_rec.payment_term_id;
3544
3545 IF lp_taiv_rec.irt_id IS NULL
3546 OR lp_taiv_rec.irt_id = FND_API.G_MISS_NUM THEN
3547 OPEN l_std_terms_csr (
3548 l_site_use_rec.cust_account_id,
3549 l_btc_id);
3550 FETCH l_std_terms_csr INTO lp_taiv_rec.irt_id;
3551 CLOSE l_std_terms_csr;
3552 END IF;
3553
3554
3555 IF lp_taiv_rec.ixx_id IS NULL
3556 OR lp_taiv_rec.ixx_id = FND_API.G_MISS_NUM THEN
3557 OKL_API.SET_MESSAGE (
3558 p_app_name => 'OKL',
3559 p_msg_name => 'OKL_REQUIRED_VALUE',
3560 p_token1 => 'COL_NAME',
3561 p_token1_value => 'Customer Account Id');
3562 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3563 END IF;
3564
3565 IF lp_taiv_rec.ibt_id IS NULL
3566 OR lp_taiv_rec.ibt_id = FND_API.G_MISS_NUM THEN
3567 OKL_API.SET_MESSAGE (
3568 p_app_name => 'OKL',
3569 p_msg_name => 'OKL_REQUIRED_VALUE',
3570 p_token1 => 'COL_NAME',
3571 p_token1_value => 'Bill To Address Id');
3572 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3573 END IF;
3574
3575 IF lp_taiv_rec.irt_id IS NULL
3576 OR lp_taiv_rec.irt_id = FND_API.G_MISS_NUM THEN
3577 OKL_API.SET_MESSAGE (
3578 p_app_name => 'OKL',
3579 p_msg_name => 'OKL_REQUIRED_VALUE',
3580 p_token1 => 'COL_NAME',
3581 p_token1_value => 'Payment Term Id');
3582 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3583 END IF;
3584
3585
3586 l_rule_value := NULL;
3587 l_id1 := NULL;
3588 l_id2 := NULL;
3589
3590 l_return_status := okl_contract_info.get_rule_value(
3591 p_contract_id => l_program_id
3592 ,p_rule_group_code => 'LAVENB'
3593 ,p_rule_code => 'LAPMTH'
3594 ,p_segment_number => 16
3595 ,x_id1 => l_id1
3596 ,x_id2 => l_id2
3597 ,x_value => l_rule_value);
3598
3599 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_credit_memo : okl_contract_info.get_rule_value : '||l_return_status);
3600 if l_return_status =FND_Api.G_RET_STS_SUCCESS
3601 and l_id1 IS NOT NULL THEN
3602 lp_taiv_rec.irm_id :=l_id1;
3603 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3604 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Payment method from rule is ' || l_id1);
3605 END IF;
3606 else
3607 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3608 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Retrieval of Payment Method Id failed');
3609 END IF;
3610 OKL_API.SET_MESSAGE (
3611 p_app_name => 'OKL',
3612 p_msg_name => 'OKL_REQUIRED_VALUE',
3613 p_token1 => 'COL_NAME',
3614 p_token1_value => 'Payment Method ');
3615 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3616 end if;
3617
3618 OPEN l_rcpt_mthd_csr (l_id1);
3619 FETCH l_rcpt_mthd_csr INTO lp_taiv_rec.irm_id;
3620 CLOSE l_rcpt_mthd_csr;
3621
3622 IF lp_taiv_rec.irm_id IS NULL
3623 OR lp_taiv_rec.irm_id = FND_API.G_MISS_NUM THEN
3624 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3625 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'receipt method id is not found');
3626 END IF;
3627 OKL_API.SET_MESSAGE (
3628 p_app_name => 'OKL',
3629 p_msg_name => 'OKL_REQUIRED_VALUE',
3630 p_token1 => 'COL_NAME',
3631 p_token1_value => 'receipt method id ');
3632 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3633 END IF;
3634
3635 lp_taiv_rec.object_version_number :=1;
3636 lp_taiv_rec.date_entered :=SYSDATE;
3637 lp_taiv_rec.date_invoiced :=SYSDATE;
3638 lp_taiv_rec.amount :=p_amount * - 1;
3639 lp_taiv_rec.description := 'Cure Invoice';
3640 lp_taiv_rec.trx_status_code :='PENDINGI';
3641 -- this will establish a link for offset contracts with a refund line
3642 --lp_taiv_rec.cpy_id :=p_cure_refund_id;
3643 --20-NOV-2006 ANSETHUR R12B - LEGAL ENTITY UPTAKE PROJECT
3644 lp_taiv_rec.legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(lp_taiv_rec.khr_id);
3645
3646 -- R12 Changes - START
3647 -- Following is new as per Ashim's instructions
3648
3649 lp_taiv_rec.okl_source_billing_trx := 'CURE';
3650
3651 -- R12 Changes - END
3652
3653
3654 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3655 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'taiv_rec.cpy_id' ||lp_taiv_rec.cpy_id ||
3656 ' taiv_rec.try_id' ||lp_taiv_rec.try_id||
3657 ' taiv_rec.khr_id' ||lp_taiv_rec.khr_id||
3658 ' taiv_rec.irm_id'||lp_taiv_rec.irm_id||
3659 ' taiv_rec.ibt_id'||lp_taiv_rec.ibt_id||
3660 ' taiv_rec.ixx_id '||lp_taiv_rec.ixx_id||
3661 ' taiv_rec.irt_id'||lp_taiv_rec.irt_id);
3662
3663 END IF;
3664
3665
3666 -- ASHIM CHANGE - START
3667
3668
3669 okl_trx_ar_invoices_pub.INSERT_trx_ar_invoices
3670 (p_api_version => 1.0,
3671 p_init_msg_list => 'F',
3672 x_return_status => l_return_status,
3673 x_msg_count => l_msg_count,
3674 x_msg_data => l_msg_data,
3675 p_taiv_rec => lp_taiv_rec,
3676 x_taiv_rec => xp_taiv_rec);
3677
3678 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_credit_memo : okl_trx_ar_invoices_pub.INSERT_trx_ar_invoices : '||l_return_status);
3679
3680 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3681 Get_Messages (l_msg_count,l_message);
3682 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3683 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error in updating okl_trx_ar_invoices_b '
3684 ||l_message);
3685 END IF;
3686 raise FND_API.G_EXC_ERROR;
3687 ELSE
3688 FND_MSG_PUB.initialize;
3689 --INSERT okl_txl_ar_inv_lns
3690 --set error message,so this will be prefixed before the
3691 --actual message, so it makes more sense than displaying an
3692 -- OKL message.
3693 AddfailMsg(
3694 p_object => 'RECORD IN OKL_TXL_AR_INV_LNS ',
3695 p_operation => 'INSERT' );
3696
3697
3698 lp_tilv_rec.amount :=p_amount * -1;
3699 lp_tilv_rec.object_version_number :=1;
3700 lp_tilv_rec.tai_id :=xp_taiv_rec.id;
3701 lp_tilv_rec.description :='Cure Invoice';
3702 lp_tilv_rec.inv_receiv_line_code :='LINE';
3703 lp_tilv_rec.line_number :=1;
3704
3705 -- R12 Change - START
3706 -- Following is new as per Ashim's instructions
3707
3708 lp_tilv_rec.txl_ar_line_number :=1;
3709
3710 -- R12 Change - END
3711
3712 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3713 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE, 'tilv_rec.tai_id' ||lp_tilv_rec.tai_id||
3714 'tilv_rec.amount' ||lp_tilv_rec.amount||
3715 'tilv_rec.sty_id' ||lp_tilv_rec.sty_id);
3716
3717
3718 END IF;
3719 okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns
3720 (p_api_version => 1.0,
3721 p_init_msg_list => 'F',
3722 x_return_status => l_return_status,
3723 x_msg_count => l_msg_count,
3724 x_msg_data => l_msg_data,
3725 p_tilv_rec => lp_tilv_rec,
3726 x_tilv_rec => xp_tilv_rec);
3727
3728 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_credit_memo : okl_txl_ar_inv_lns_pub.insert_txl_ar_inv_lns : '||l_return_status);
3729 -- ASHIM CHANGE - END
3730
3731
3732
3733 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3734 Get_Messages (l_msg_count,l_message);
3735 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3736 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error in updating okl_txl_ar_inv_lns '
3737 ||l_message);
3738 END IF;
3739 raise FND_API.G_EXC_ERROR;
3740 ELSE
3741 FND_MSG_PUB.initialize;
3742
3743 -- R12 Change - START
3744 -- Ashim's instructions for TXD table
3745 -- populate sty_id, kle_id(NULL), khr_id, amount, til_id_details, txl_ar_line_number
3746
3747 AddfailMsg(
3748 p_object => 'RECORD IN OKL_TXD_AR_LN_DTLS ',
3749 p_operation => 'INSERT' );
3750
3751 lp_tldv_rec.TIL_ID_DETAILS := xp_tilv_rec.id;
3752 lp_tldv_rec.STY_ID := xp_tilv_rec.STY_ID;
3753 lp_tldv_rec.AMOUNT := xp_tilv_rec.AMOUNT;
3754 lp_tldv_rec.ORG_ID := xp_tilv_rec.ORG_ID;
3755 lp_tldv_rec.INVENTORY_ORG_ID := xp_tilv_rec.INVENTORY_ORG_ID;
3756 lp_tldv_rec.INVENTORY_ITEM_ID := xp_tilv_rec.INVENTORY_ITEM_ID;
3757 lp_tldv_rec.LINE_DETAIL_NUMBER := 1;
3758 lp_tldv_rec.KHR_ID := lp_taiv_rec.KHR_ID;
3759 lp_tldv_rec.txl_ar_line_number :=1;
3760
3761
3762 okl_internal_billing_pvt.Get_Invoice_format(
3763 p_api_version => 1.0
3764 ,p_init_msg_list => OKL_API.G_FALSE
3765 ,x_return_status => l_return_status
3766 ,x_msg_count => x_msg_count
3767 ,x_msg_data => x_msg_data
3768 ,p_inf_id => lp_taiv_rec.inf_id
3769 ,p_sty_id => lp_tldv_rec.STY_ID
3770 ,x_invoice_format_type => lp_tldv_rec.invoice_format_type
3771 ,x_invoice_format_line_type => lp_tldv_rec.invoice_format_line_type);
3772 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_credit_memo : okl_internal_billing_pvt.Get_Invoice_format : '||l_return_status);
3773
3774 IF (l_return_status = OKL_API.G_RET_STS_UNEXP_ERROR)
3775 THEN
3776 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3777 ELSIF (l_return_status = OKL_API.G_RET_STS_ERROR)
3778 THEN
3779 RAISE Fnd_Api.G_EXC_ERROR;
3780 END IF;
3781
3782 additional_tld_attr(
3783 p_api_version => 1.0,
3784 p_init_msg_list => OKL_API.G_FALSE,
3785 x_return_status => l_return_status,
3786 x_msg_count => x_msg_count,
3787 x_msg_data => x_msg_data,
3788 p_tldv_rec => lp_tldv_rec,
3789 x_tldv_rec => xp_tldv_rec);
3790 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_credit_memo : additional_tld_attr : '||l_return_status);
3791 lp_tldv_rec := xp_tldv_rec;
3792
3793 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3794 IF (l_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3795 x_return_status := l_return_status;
3796 END IF;
3797 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3798 END IF;
3799
3800 okl_tld_pvt.insert_row(
3801 p_api_version => 1.0,
3802 p_init_msg_list => OKL_API.G_FALSE,
3803 x_return_status => l_return_status,
3804 x_msg_count => x_msg_count,
3805 x_msg_data => x_msg_data,
3806 p_tldv_rec => lp_tldv_rec,
3807 x_tldv_rec => xp_tldv_rec);
3808
3809 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_credit_memo : okl_tld_pvt.insert_row : '||l_return_status);
3810
3811 IF (l_return_status <> OKL_API.G_RET_STS_SUCCESS) THEN
3812 IF (l_return_status <> OKL_API.G_RET_STS_UNEXP_ERROR) THEN
3813 x_return_status := l_return_status;
3814 END IF;
3815 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3816 ELSE
3817 FND_MSG_PUB.initialize;
3818
3819 END IF; -- for okl_txd_ar_ln_dtls
3820
3821 -- R12 Change - END
3822
3823
3824 END IF; -- for okl_txl_ar_inv_lns
3825 END IF; -- for okl_trx_ar_invoices
3826
3827 --update tai_id in cure_refund_table
3828 --set error message,so this will be prefixed before the
3829 --actual message, so it makes more sense than displaying an
3830 -- OKL message.
3831 AddfailMsg(
3832 p_object => 'RECORD IN OKL_CURE_REFUNDS ',
3833 p_operation => 'UPDATE' );
3834
3835
3836 lp_crfv_rec.cure_refund_id := p_cure_refund_id;
3837 lp_crfv_rec.tai_id := xp_taiv_rec.id;
3838
3839 OPEN c_getobj_ver(p_cure_refund_id);
3840 FETCH c_getobj_ver INTO lp_crfv_rec.object_version_number;
3841 CLOSE c_getobj_ver;
3842
3843
3844 OKL_cure_refunds_pub.update_cure_refunds(
3845 p_api_version => 1.0
3846 ,p_init_msg_list => 'F'
3847 ,x_return_status => l_return_status
3848 ,x_msg_count => l_msg_count
3849 ,x_msg_data => l_msg_data
3850 ,p_crfv_rec => lp_crfv_rec
3851 ,x_crfv_rec => lx_crfv_rec);
3852 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_credit_memo : OKL_cure_refunds_pub.update_cure_refunds : '||l_return_status);
3853 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3854 Get_Messages (l_msg_count,l_message);
3855 IF PG_DEBUG < 11 THEN
3856 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3857 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
3858 END IF;
3859 END IF;
3860 raise FND_API.G_EXC_ERROR;
3861 ELSE
3862 IF PG_DEBUG < 11 THEN
3863 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
3864 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Succesfully updated CRF records');
3865 END IF;
3866 END IF;
3867 FND_MSG_PUB.initialize;
3868 END IF;
3869
3870 IF x_return_status =FND_API.G_RET_STS_SUCCESS THEN
3871 FND_MSG_PUB.initialize;
3872 END IF;
3873
3874
3875 FND_MSG_PUB.Count_And_Get
3876 ( p_count => x_msg_count,
3877 p_data => x_msg_data
3878 );
3879
3880 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_credit_memo : END ');
3881 EXCEPTION
3882 WHEN Fnd_Api.G_EXC_ERROR THEN
3883 ROLLBACK TO CREATE_CREDIT_MEMO;
3884 x_return_status := Fnd_Api.G_RET_STS_ERROR;
3885 x_msg_count := l_msg_count ;
3886 x_msg_data := l_msg_data ;
3887 Fnd_Msg_Pub.count_and_get(
3888 p_count => x_msg_count
3889 ,p_data => x_msg_data);
3890 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
3891 ROLLBACK TO CREATE_CREDIT_MEMO;
3892 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3893 x_msg_count := l_msg_count ;
3894 x_msg_data := l_msg_data ;
3895 Fnd_Msg_Pub.count_and_get(
3896 p_count => x_msg_count
3897 ,p_data => x_msg_data);
3898 WHEN OTHERS THEN
3899 ROLLBACK TO CREATE_CREDIT_MEMO;
3900 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3901 x_msg_count := l_msg_count ;
3902 x_msg_data := l_msg_data ;
3903 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_PAY_CURE_REFUNDS_PVT','CREATE_CREDIT_MEMO');
3904 Fnd_Msg_Pub.count_and_get(
3905 p_count => x_msg_count
3906 ,p_data => x_msg_data);
3907
3908 END CREATE_CREDIT_MEMO;
3909
3910
3911 PROCEDURE CREATE_CUREREFUNDS
3912 (p_commit IN VARCHAR2 DEFAULT OKC_API.G_FALSE
3913 ,p_refund_header_id IN NUMBER
3914 ,x_return_status OUT NOCOPY VARCHAR2
3915 ,x_msg_count OUT NOCOPY NUMBER
3916 ,x_msg_data OUT NOCOPY VARCHAR2
3917 )IS
3918 l_init_msg_list VARCHAR2(1);
3919 l_return_status VARCHAR2(1);
3920 l_msg_count NUMBER ;
3921 l_msg_data VARCHAR2(32627);
3922 l_message VARCHAR2(32627);
3923 l_api_name CONSTANT VARCHAR2(50) := 'CREATE_CUREREFUNDS';
3924 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'
3925 || l_api_name;
3926 lp_tapv_rec okl_tap_pvt.tapv_rec_type;
3927 lx_tapv_rec okl_tap_pvt.tapv_rec_type;
3928 lp_tplv_rec okl_tpl_pvt.tplv_rec_type;
3929 lx_tplv_rec okl_tpl_pvt.tplv_rec_type;
3930
3931 /* ankushar 22-JAN-2007
3932 added table definitions
3933 start changes
3934 */
3935 lp_tplv_tbl okl_tpl_pvt.tplv_tbl_type;
3936 lx_tplv_tbl okl_tpl_pvt.tplv_tbl_type;
3937 /* ankushar end changes*/
3938
3939 lp_crfv_rec okl_crf_pvt.crfv_rec_type;
3940 lx_crfv_rec okl_crf_pvt.crfv_rec_type;
3941
3942
3943
3944 lp_chdv_rec okl_chd_pvt.chdv_rec_type;
3945 lx_chdv_rec okl_chd_pvt.chdv_rec_type;
3946
3947 cursor c_getobj(p_cure_refund_header_id IN NUMBER ) is
3948 select object_version_number from okl_cure_refund_headers_b
3949 where cure_refund_header_id =p_cure_refund_header_id;
3950
3951 next_row integer;
3952
3953 cursor c_get_refunds(p_cure_refund_header_id IN NUMBER) is
3954 select crf.chr_id,
3955 crf.disbursement_amount,
3956 crf.offset_contract,
3957 crf.offset_amount,
3958 crf.object_version_number,
3959 crf.cure_refund_id,
3960 crh.vendor_site_id,
3961 crh.payment_term_id,
3962 crh.payment_method,
3963 crh.currency_code,
3964 crh.refund_due_date
3965
3966 from okl_cure_refund_headers_b crh,
3967 okl_cure_refunds crf
3968 where crh.cure_refund_header_id =p_cure_refund_header_id
3969 and crh.cure_refund_header_id =crf.cure_refund_header_id;
3970
3971 CURSOR product_csr (p_chr_id IN NUMBER) IS
3972 SELECT khr.pdt_id
3973 FROM okl_k_headers khr
3974 WHERE khr.id = p_chr_id;
3975
3976
3977 CURSOR org_id_csr ( p_khr_id NUMBER ) IS
3978 SELECT chr.authoring_org_id
3979 FROM okc_k_headers_b chr
3980 WHERE id = p_khr_id;
3981
3982 CURSOR sob_csr ( p_org_id NUMBER ) IS
3983 SELECT hru.set_of_books_id
3984 FROM HR_OPERATING_UNITS HRU
3985 WHERE ORGANIZATION_ID = p_org_id;
3986
3987 CURSOR try_id_csr IS
3988 SELECT id
3989 FROM okl_trx_types_tl
3990 WHERE name = 'Disbursement'
3991 AND LANGUAGE = USERENV('LANG');
3992
3993 /* -- user defined streams
3994 CURSOR stream_type_csr IS
3995 SELECT id
3996 FROM okl_strm_type_tl
3997 WHERE name = 'CURE'
3998 AND LANGUAGE = USERENV('LANG');
3999 */
4000
4001 x_primary_sty_id number;
4002 l_khr_id number;
4003
4004 CURSOR c_app
4005 IS
4006 select a.application_id
4007 from FND_APPLICATION a
4008 where APPLICATION_SHORT_NAME = 'OKL';
4009
4010 l_okl_application_id NUMBER(3) := 540;
4011 l_document_category VARCHAR2(100):= 'OKL Lease Pay Invoices';
4012 lX_dbseqnm VARCHAR2(2000):= '';
4013 lX_dbseqid NUMBER(38):= NULL;
4014
4015
4016 BEGIN
4017 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: CREATE_CUREREFUNDS : START ');
4018 SAVEPOINT CREATE_CUREREFUNDS;
4019 -- Initialize message list if p_init_msg_list is set to TRUE.
4020 FND_MSG_PUB.initialize;
4021
4022 x_return_status := FND_API.G_RET_STS_SUCCESS;
4023
4024 IF (p_refund_header_id IS NULL) THEN
4025 AddMissingArgMsg(
4026 p_api_name => l_api_name_full,
4027 p_param_name => 'p_refund_header_id');
4028 RAISE FND_API.G_EXC_ERROR;
4029 ELSE
4030 IF PG_DEBUG < 11 THEN
4031 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4032 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lp_tapv_rec.org_id '||
4033 lp_tapv_rec.org_id);
4034 END IF;
4035 END IF;
4036 END IF;
4037
4038
4039 /*** Logic for refunds ********
4040 ** 1) Invoke the common disbursement API for ap header and line creation
4041 **/
4042 -- STEP 1
4043 --populate the ap invoice header table (okl_trx_ap_invoices_b)
4044 IF PG_DEBUG < 11 THEN
4045 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4046 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'update cure refunds ');
4047 END IF;
4048 END IF;
4049
4050
4051 -- STEP 1
4052 --populate the ap invoice header table (okl_trx_ap_invoices_b)
4053 IF PG_DEBUG < 11 THEN
4054 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4055 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'before creating TAP record ');
4056 END IF;
4057 END IF;
4058
4059
4060 FOR i in c_get_refunds (p_refund_header_id)
4061 LOOP
4062 lp_tapv_rec.org_id := NULL;
4063 OPEN org_id_csr ( i.chr_id) ;
4064 FETCH org_id_csr INTO lp_tapv_rec.org_id;
4065 CLOSE org_id_csr;
4066
4067 IF (lp_tapv_rec.org_id IS NULL) THEN
4068 AddMissingArgMsg(
4069 p_api_name => l_api_name_full,
4070 p_param_name => 'org_id' );
4071 RAISE FND_API.G_EXC_ERROR;
4072 ELSE
4073 IF PG_DEBUG < 11 THEN
4074 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4075 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lp_tapv_rec.org_id '||
4076 lp_tapv_rec.org_id);
4077 END IF;
4078 END IF;
4079 END IF;
4080
4081 OPEN sob_csr ( lp_tapv_rec.org_id );
4082 FETCH sob_csr INTO lp_tapv_rec.set_of_books_id;
4083 CLOSE sob_csr;
4084
4085 IF (lp_tapv_rec.set_of_books_id IS NULL) THEN
4086 AddMissingArgMsg(
4087 p_api_name => l_api_name_full,
4088 p_param_name => 'set_of_books_id' );
4089 RAISE FND_API.G_EXC_ERROR;
4090 ELSE
4091 IF PG_DEBUG < 11 THEN
4092 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4093 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lp_tapv_rec.set_of_books_id'||
4094 lp_tapv_rec.set_of_books_id);
4095 END IF;
4096 END IF;
4097 END IF;
4098
4099 lp_tapv_rec.try_id := NULL;
4100 OPEN try_id_csr;
4101 FETCH try_id_csr INTO lp_tapv_rec.try_id;
4102 CLOSE try_id_csr;
4103
4104 IF (lp_tapv_rec.try_id IS NULL) THEN
4105 AddMissingArgMsg(
4106 p_api_name => l_api_name_full,
4107 p_param_name => 'try_id' );
4108 RAISE FND_API.G_EXC_ERROR;
4109 ELSE
4110 IF PG_DEBUG < 11 THEN
4111 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4112 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lp_tapv_rec.try_id'||
4113 lp_tapv_rec.try_id);
4114 END IF;
4115 END IF;
4116 END IF;
4117 lp_tapv_rec.invoice_number := NULL;
4118
4119 --
4120 -- display specific application error if 'OKL Lease Pay Invoices'
4121 -- has not been setup or setup incorrectly
4122 --
4123
4124 OPEN c_app;
4125 FETCH c_app INTO l_okl_application_id;
4126 CLOSE c_app;
4127 l_okl_application_id := nvl(l_okl_application_id,540);
4128
4129 BEGIN
4130 lp_tapv_rec.invoice_number := fnd_seqnum.get_next_sequence
4131 (appid => l_okl_application_id,
4132 cat_code => l_document_category,
4133 sobid => lp_tapv_rec.set_of_books_id,
4134 met_code => 'A',
4135 trx_date => SYSDATE,
4136 dbseqnm => lx_dbseqnm,
4137 dbseqid => lx_dbseqid);
4138
4139 EXCEPTION
4140 WHEN OTHERS THEN
4141 IF SQLCODE = 100 THEN
4142 fnd_message.set_name('OKL', 'OKL_PAY_INV_SEQ_CHECK');
4143 fnd_msg_pub.add;
4144 RAISE FND_API.G_EXC_ERROR;
4145 END IF;
4146 END;
4147
4148 IF (lp_tapv_rec.invoice_number IS NULL) THEN
4149 AddMissingArgMsg(
4150 p_api_name => l_api_name_full,
4151 p_param_name => 'invoice_number' );
4152 RAISE FND_API.G_EXC_ERROR;
4153 ELSE
4154 IF PG_DEBUG < 11 THEN
4155 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4156 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lp_tapv_rec.invoice_number'||
4157 lp_tapv_rec.invoice_number);
4158 END IF;
4159 END IF;
4160
4161 END IF;
4162
4163
4164 -- sjalasut, commented the below khr_id assignment as khr_id would be henceforth referred in
4165 -- okl_txl_ap_inv_lns_all_b. changes made as part of OKLR12B disbursements project.
4166 lp_tapv_rec.khr_id := i.chr_id; -- cklee 09/20/2007
4167 -- lp_tapv_rec.khr_id := NULL;
4168
4169 lp_tapv_rec.ipvs_id := i.vendor_site_id;
4170 lp_tapv_rec.ippt_id := i.payment_term_id;
4171 lp_tapv_rec.payment_method_code := i.payment_method;
4172 lp_tapv_rec.currency_code := i.currency_code;
4173 lp_tapv_rec.date_entered := sysdate;
4174 lp_tapv_rec.date_invoiced := i.refund_due_date;
4175 lp_tapv_rec.amount := i.disbursement_amount;
4176 lp_tapv_rec.trx_status_code := 'PENDINGI';
4177 lp_tapv_rec.object_version_number := 1;
4178 --20-NOV-2006 ANSETHUR R12B - LEGAL ENTITY UPTAKE PROJECT
4179 -- sjalasut, changed the parameter from lp_tapv_rec.khr_id to i.chr_id as part of OKLR12B
4180 -- disbursements project.
4181 lp_tapv_rec.legal_entity_id := OKL_LEGAL_ENTITY_UTIL.get_khr_le_id(i.chr_id);
4182
4183 -- not sure of these 4 variable
4184 /* invoice_type,
4185 invoice_category_code,
4186 pay_group_lookup_code,
4187 nettable_yn,
4188 if invoice_type is credit then amount is -ve
4189 */
4190
4191 --populate the line table (okl_txl_ap_inv_lns_b)
4192 -- sjalasut, added the khr_id assignment as part of OKLR12B disbursements project
4193 lp_tplv_rec.khr_id := i.chr_id;
4194
4195 lp_tplv_rec.tap_id := lx_tapv_rec.id;
4196 lp_tplv_rec.amount := lp_tapv_rec.amount;
4197 lp_tplv_rec.inv_distr_line_code := 'MANUAL';
4198 lp_tplv_rec.line_number := 1;
4199 lp_tplv_rec.org_id := lp_tapv_rec.org_id;
4200 lp_tplv_rec.disbursement_basis_code := 'BILL_DATE';
4201 lp_tplv_rec.object_version_number := 1;
4202
4203 /* what about other columns
4204 sty_id,
4205 * is disbursement_basis_code= 'bill_date'
4206 */
4207
4208
4209 /* --User Defines Streams fix
4210 FOR stream_rec IN stream_type_csr
4211 LOOP
4212 lp_tplv_rec.sty_id := stream_rec.id;
4213 IF PG_DEBUG < 11 THEN
4214 okl_debug_pub.logmessage ('sty_id ' ||stream_rec.id);
4215 END IF;
4216 END LOOP;
4217 */
4218
4219 l_khr_id := i.chr_id;
4220
4221 OKL_STREAMS_UTIL.get_primary_stream_type(
4222 p_khr_id => l_khr_id,
4223 p_primary_sty_purpose => 'CURE',
4224 x_return_status => l_return_status,
4225 x_primary_sty_id => x_primary_sty_id
4226 );
4227
4228 lp_tplv_rec.sty_id := x_primary_sty_id;
4229
4230 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4231 Get_Messages (l_msg_count,l_message);
4232 IF PG_DEBUG < 11 THEN
4233 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4234 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
4235 END IF;
4236 END IF;
4237 raise FND_API.G_EXC_ERROR;
4238
4239 ELSE
4240
4241 IF PG_DEBUG < 11 THEN
4242 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4243 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'sty_id ' ||x_primary_sty_id);
4244 END IF;
4245 END IF;
4246
4247 END IF;
4248
4249 /* ankushar 23-JAN-2007
4250 Call to the common Disbursement API
4251 start changes */
4252
4253 -- Add tpl_rec to table
4254 lp_tplv_tbl(1) := lp_tplv_rec;
4255
4256 --Call the commong disbursement API to create transactions
4257 -- start:
4258 --cklee 06/04/2007 Reverse the original code back due to the duplicated
4259 -- accounting entries will be created
4260 /*
4261 lp_tplv_tbl(1) := lp_tplv_rec;
4262
4263 --Call the commong disbursement API to create transactions
4264 Okl_Create_Disb_Trans_Pvt.create_disb_trx(
4265 p_api_version => 1.0
4266 ,p_init_msg_list => 'F'
4267 ,x_return_status => x_return_status
4268 ,x_msg_count => x_msg_count
4269 ,x_msg_data => x_msg_data
4270 ,p_tapv_rec => lp_tapv_rec
4271 ,p_tplv_tbl => lp_tplv_tbl
4272 ,x_tapv_rec => lx_tapv_rec
4273 ,x_tplv_tbl => lx_tplv_tbl);
4274 */
4275 OKL_TRX_AP_INVOICES_PUB.INSERT_TRX_AP_INVOICES(
4276 p_api_version => 1.0,
4277 p_init_msg_list => 'F',
4278 x_return_status => x_return_status,
4279 x_msg_count => x_msg_count,
4280 x_msg_data => x_msg_data,
4281 p_tapv_rec => lp_tapv_rec,
4282 x_tapv_rec => lx_tapv_rec);
4283
4284 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: CREATE_CUREREFUNDS : OKL_TRX_AP_INVOICES_PUB.INSERT_TRX_AP_INVOICES : '||x_return_status);
4285
4286 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4287 Get_Messages (l_msg_count,l_message);
4288 IF PG_DEBUG <11 THEN
4289 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4290 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
4291 END IF;
4292 END IF;
4293 raise FND_API.G_EXC_ERROR;
4294 ELSE
4295 IF PG_DEBUG < 11 THEN
4296 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4297 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lx_tapv_rec.id'
4298 ||lx_tapv_rec.id);
4299 END IF;
4300 END IF;
4301 FND_MSG_PUB.initialize;
4302 END IF;
4303
4304 lp_tplv_rec.tap_id := lx_tapv_rec.id;
4305
4306 OKL_TXL_AP_INV_LNS_PUB.INSERT_TXL_AP_INV_LNS(
4307 p_api_version => 1.0,
4308 p_init_msg_list => 'F',
4309 x_return_status => x_return_status,
4310 x_msg_count => x_msg_count,
4311 x_msg_data => x_msg_data,
4312 p_tplv_rec => lp_tplv_rec,
4313 x_tplv_rec => lx_tplv_rec);
4314 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: CREATE_CUREREFUNDS : OKL_TXL_AP_INV_LNS_PUB.INSERT_TXL_AP_INV_LNS : '||x_return_status);
4315 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4316 Get_Messages (l_msg_count,l_message);
4317 IF PG_DEBUG <11 THEN
4318 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4319 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
4320 END IF;
4321 END IF;
4322 raise FND_API.G_EXC_ERROR;
4323 ELSE
4324 IF PG_DEBUG < 11 THEN
4325 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4326 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lx_tplv_rec.id'
4327 ||lx_tplv_rec.id);
4328 END IF;
4329 END IF;
4330 FND_MSG_PUB.initialize;
4331 END IF;
4332 -- end:
4333 --cklee 06/04/2007 Reverse the original code back due to the duplicated
4334 -- accounting entries will be created
4335 /* ankushar end changes */
4336
4337 --set error message,so this will be prefixed before the
4338 --actual message, so it makes more sense than displaying an
4339 -- OKL message.
4340 AddfailMsg(
4341 p_object => 'RECORD IN OKL_CURE_REFUNDS ',
4342 p_operation => 'UPDATE' );
4343
4344
4345 lp_crfv_rec.cure_refund_id := i.cure_refund_id;
4346 lp_crfv_rec.tap_id := lx_tapv_rec.id;
4347 lp_crfv_rec.object_version_number :=i.object_version_number;
4348
4349 OKL_cure_refunds_pub.update_cure_refunds(
4350 p_api_version => 1.0
4351 ,p_init_msg_list => 'F'
4352 ,x_return_status => l_return_status
4353 ,x_msg_count => l_msg_count
4354 ,x_msg_data => l_msg_data
4355 ,p_crfv_rec => lp_crfv_rec
4356 ,x_crfv_rec => lx_crfv_rec);
4357 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: CREATE_CUREREFUNDS : OKL_cure_refunds_pub.update_cure_refunds : '||x_return_status);
4358 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4359 Get_Messages (l_msg_count,l_message);
4360 IF PG_DEBUG < 11 THEN
4361 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4362 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
4363 END IF;
4364 END IF;
4365 raise FND_API.G_EXC_ERROR;
4366 ELSE
4367 IF PG_DEBUG < 11 THEN
4368 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4369 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Succesfully updated CRF records');
4370 END IF;
4371 END IF;
4372 FND_MSG_PUB.initialize;
4373 END IF;
4374
4375 IF i.offset_contract is not null THEN
4376 --Update Cure refunds table
4377 --set error message,so this will be prefixed before the
4378 --actual message, so it makes more sense than displaying an
4379 -- OKL message.
4380 AddfailMsg(
4381 p_object => 'RECORD IN OKL_TRX_AR_INVOICES_B ',
4382 p_operation => 'CREATE' );
4383
4384
4385 -- ASHIM CHANGE - START
4386
4387
4388
4389 create_credit_memo (p_contract_id => i.offset_contract
4390 ,p_cure_refund_id => i.cure_refund_id
4391 ,p_amount => i.offset_amount
4392 ,x_return_status => l_return_status
4393 ,x_msg_count => l_msg_count
4394 ,x_msg_data => l_msg_data );
4395
4396 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: CREATE_CUREREFUNDS : create_credit_memo : '||l_return_status);
4397
4398 -- ASHIM CHANGE - END
4399
4400
4401 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4402 Get_Messages (l_msg_count,l_message);
4403 IF PG_DEBUG < 11 THEN
4404 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4405 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
4406 END IF;
4407 END IF;
4408 raise FND_API.G_EXC_ERROR;
4409 ELSE
4410 IF PG_DEBUG < 11 THEN
4411 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4412 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Success ' );
4413 END IF;
4414 END IF;
4415 FND_MSG_PUB.initialize;
4416 END IF;
4417 END IF; --offset contract
4418
4419 END LOOP;
4420
4421 IF x_return_status =FND_API.G_RET_STS_SUCCESS THEN
4422 FND_MSG_PUB.initialize;
4423 END IF;
4424
4425 -- Standard check for p_commit
4426 IF FND_API.to_Boolean( p_commit ) THEN
4427 COMMIT WORK;
4428 END IF;
4429
4430 FND_MSG_PUB.Count_And_Get
4431 ( p_count => x_msg_count,
4432 p_data => x_msg_data
4433 );
4434
4435 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: CREATE_CUREREFUNDS : END ');
4436 EXCEPTION
4437 WHEN Fnd_Api.G_EXC_ERROR THEN
4438 ROLLBACK TO CREATE_CUREREFUNDS;
4439 x_return_status := Fnd_Api.G_RET_STS_ERROR;
4440 x_msg_count := l_msg_count ;
4441 x_msg_data := l_msg_data ;
4442 Fnd_Msg_Pub.count_and_get(
4443 p_count => x_msg_count
4444 ,p_data => x_msg_data);
4445 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
4446 ROLLBACK TO CREATE_CUREREFUNDS;
4447 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4448 x_msg_count := l_msg_count ;
4449 x_msg_data := l_msg_data ;
4450 Fnd_Msg_Pub.count_and_get(
4451 p_count => x_msg_count
4452 ,p_data => x_msg_data);
4453 WHEN OTHERS THEN
4454 ROLLBACK TO CREATE_CUREREFUNDS;
4455 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4456 x_msg_count := l_msg_count ;
4457 x_msg_data := l_msg_data ;
4458 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_PAY_CURE_REFUNDS_PVT','CREATE_CUREREFUNDS');
4459 Fnd_Msg_Pub.count_and_get(
4460 p_count => x_msg_count
4461 ,p_data => x_msg_data);
4462
4463 END create_curerefunds;
4464
4465
4466 PROCEDURE approve_cure_refunds
4467 ( p_api_version IN NUMBER
4468 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_TRUE
4469 ,p_commit IN VARCHAR2 DEFAULT OKC_API.G_FALSE
4470 ,p_refund_header_id IN NUMBER
4471 ,x_return_status OUT NOCOPY VARCHAR2
4472 ,x_msg_count OUT NOCOPY NUMBER
4473 ,x_msg_data OUT NOCOPY VARCHAR2
4474 )IS
4475 l_init_msg_list VARCHAR2(1);
4476 l_return_status VARCHAR2(1);
4477 l_msg_count NUMBER ;
4478 l_msg_data VARCHAR2(32627);
4479 l_message VARCHAR2(32627);
4480 l_api_name CONSTANT VARCHAR2(50) := 'APPROVE_CURE_REFUNDS';
4481 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'
4482 || l_api_name;
4483
4484 lp_chdv_rec okl_chd_pvt.chdv_rec_type;
4485 lx_chdv_rec okl_chd_pvt.chdv_rec_type;
4486
4487 cursor c_getobj(p_cure_refund_header_id IN NUMBER ) is
4488 select object_version_number,
4489 refund_header_number,
4490 refund_status
4491 from okl_cure_refund_headers_b
4492 where cure_refund_header_id =p_cure_refund_header_id;
4493 l_refund_header_number okl_cure_refund_headers_b.refund_header_number%TYPE;
4494
4495 l_refund_status okl_cure_refund_headers_b.refund_status%TYPE;
4496
4497 next_row integer;
4498 c_check_dtls_ctr NUMBER :=0;
4499
4500 cursor c_check_dtls (p_refund_header_id IN NUMBER) is
4501 select count(cure_refund_id) from okl_cure_refunds where
4502 cure_refund_header_id =p_refund_header_id;
4503
4504
4505 BEGIN
4506 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: approve_cure_refunds : START ');
4507 SAVEPOINT APPROVE_CURE_REFUNDS_PVT;
4508 -- Initialize message list if p_init_msg_list is set to TRUE.
4509 IF FND_API.to_Boolean( p_init_msg_list )
4510 THEN
4511 FND_MSG_PUB.initialize;
4512 END IF;
4513
4514 x_return_status := FND_API.G_RET_STS_SUCCESS;
4515
4516 /*** Logic for refunds ********
4517 ** check if details exists before submitting
4518 ** check if refund_status ='ENTERED'
4519 ** 0 )create tap, tai (if offset contract is present)
4520 ** 1) Call Workflow for approving Refund
4521 ** 2) Update Cure Refund hdr - WAITING FOR APPROVAL '
4522 **/
4523
4524
4525
4526 OPEN c_check_dtls (p_refund_header_id);
4527 FETCH c_check_dtls into c_check_dtls_ctr;
4528 CLOSE c_check_dtls;
4529
4530 OPEN c_getobj(p_refund_header_id);
4531 FETCH c_getobj INTO lp_chdv_rec.object_version_number,
4532 l_refund_header_number,
4533 l_refund_status;
4534 CLOSE c_getobj;
4535
4536 IF c_check_dtls_ctr = 0 THEN
4537 -- no refund details
4538 IF PG_DEBUG < 11 THEN
4539 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4540 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'No refund details exists for '
4541 ||p_refund_header_id);
4542 END IF;
4543 END IF;
4544 fnd_message.set_name('OKL', 'OKL_CO_MISSING_REFUND_DETAILS');
4545 fnd_message.set_token('REFUND_NUMBER',l_refund_header_number);
4546 fnd_msg_pub.add;
4547 RAISE FND_API.G_EXC_ERROR;
4548 ELSE
4549 --check for refund header status
4550 IF l_refund_status <> 'IN_PROGRESS' THEN
4551 IF PG_DEBUG < 11 THEN
4552 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4553 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Refund status is not in progress');
4554 END IF;
4555 END IF;
4556 fnd_message.set_name('OKL', 'OKL_CO_REFUND_STATUS');
4557 fnd_message.set_token('REFUND_NUMBER',l_refund_header_number);
4558 fnd_msg_pub.add;
4559 RAISE FND_API.G_EXC_ERROR;
4560 END IF; --refund_status check
4561 END IF; -- if details exists
4562 -- STEP 0
4563 --populate the ap invoice header table (okl_trx_ap_invoices_b)
4564 IF PG_DEBUG < 11 THEN
4565 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4566 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'before creating TAP record ');
4567 END IF;
4568 END IF;
4569
4570 create_curerefunds(
4571 p_commit =>'F'
4572 ,p_refund_header_id =>p_refund_header_id
4573 ,x_return_status => l_return_status
4574 ,x_msg_count => l_msg_count
4575 ,x_msg_data => l_msg_data );
4576 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: approve_cure_refunds : create_curerefunds : '||l_return_status);
4577
4578 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4579 Get_Messages (l_msg_count,l_message);
4580 IF PG_DEBUG < 11 THEN
4581 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4582 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error after calling WF' ||l_message);
4583 END IF;
4584 END IF;
4585 raise FND_API.G_EXC_ERROR;
4586 ELSE
4587 FND_MSG_PUB.initialize;
4588 END IF;
4589
4590 --call refund_workflow
4591 invoke_refund_wf(
4592 p_refund_header_id=>p_refund_header_id
4593 ,x_return_status => l_return_status
4594 ,x_msg_count => l_msg_count
4595 ,x_msg_data => l_msg_data );
4596
4597 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: approve_cure_refunds : invoke_refund_wf : '||l_return_status);
4598 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4599 Get_Messages (l_msg_count,l_message);
4600 IF PG_DEBUG < 11 THEN
4601 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4602 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error after calling WF' ||l_message);
4603 END IF;
4604 END IF;
4605 raise FND_API.G_EXC_ERROR;
4606 ELSE
4607 FND_MSG_PUB.initialize;
4608 END IF;
4609
4610 --Update Cure refunds headers table
4611 --set error message,so this will be prefixed before the
4612 --actual message, so it makes more sense than displaying an
4613 -- OKL message.
4614 AddfailMsg(
4615 p_object => 'RECORD IN OKL_CURE_REFUND_HEADERS ',
4616 p_operation => 'UPDATE' );
4617
4618 lp_chdv_rec.cure_refund_header_id :=p_refund_header_id;
4619 lp_chdv_rec.refund_status :='PENDINGI';
4620
4621
4622 OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr(
4623 p_api_version => 1.0
4624 ,p_init_msg_list => 'F'
4625 ,x_return_status => l_return_status
4626 ,x_msg_count => l_msg_count
4627 ,x_msg_data => l_msg_data
4628 ,p_chdv_rec => lp_chdv_rec
4629 ,x_chdv_rec => lx_chdv_rec);
4630
4631 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: approve_cure_refunds : OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr : '||l_return_status);
4632
4633 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4634 Get_Messages (l_msg_count,l_message);
4635 IF PG_DEBUG < 11 THEN
4636 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4637 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error after updating Refund header'
4638 ||l_message);
4639 END IF;
4640 END IF;
4641 raise FND_API.G_EXC_ERROR;
4642 ELSE
4643 IF PG_DEBUG < 11 THEN
4644 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4645 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Success -updated cure refund header' );
4646 END IF;
4647 END IF;
4648 FND_MSG_PUB.initialize;
4649 END IF;
4650
4651
4652
4653 IF x_return_status =FND_API.G_RET_STS_SUCCESS THEN
4654 FND_MSG_PUB.initialize;
4655 END IF;
4656
4657 -- Standard check for p_commit
4658 IF FND_API.to_Boolean( p_commit )THEN
4659 COMMIT WORK;
4660 END IF;
4661
4662 FND_MSG_PUB.Count_And_Get
4663 ( p_count => x_msg_count,
4664 p_data => x_msg_data
4665 );
4666
4667 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: approve_cure_refunds : END ');
4668 EXCEPTION
4669 WHEN Fnd_Api.G_EXC_ERROR THEN
4670 ROLLBACK TO APPROVE_CURE_REFUNDS_PVT;
4671 x_return_status := Fnd_Api.G_RET_STS_ERROR;
4672 x_msg_count := l_msg_count ;
4673 x_msg_data := l_msg_data ;
4674 Fnd_Msg_Pub.count_and_get(
4675 p_count => x_msg_count
4676 ,p_data => x_msg_data);
4677 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
4678 ROLLBACK TO APPROVE_CURE_REFUNDS_PVT;
4679 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4680 x_msg_count := l_msg_count ;
4681 x_msg_data := l_msg_data ;
4682 Fnd_Msg_Pub.count_and_get(
4683 p_count => x_msg_count
4684 ,p_data => x_msg_data);
4685 WHEN OTHERS THEN
4686 ROLLBACK TO APPROVE_CURE_REFUNDS;
4687 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4688 x_msg_count := l_msg_count ;
4689 x_msg_data := l_msg_data ;
4690 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_PAY_CURE_REFUNDS_PVT','APPROVE_CURE_REFUNDS');
4691 Fnd_Msg_Pub.count_and_get(
4692 p_count => x_msg_count
4693 ,p_data => x_msg_data);
4694
4695
4696
4697 END approve_cure_refunds;
4698
4699
4700 PROCEDURE submit_cure_refunds
4701 ( p_api_version IN NUMBER
4702 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_TRUE
4703 ,p_commit IN VARCHAR2 DEFAULT OKC_API.G_FALSE
4704 ,p_status IN VARCHAR2
4705 ,p_refund_header_id IN NUMBER
4706 ,x_return_status OUT NOCOPY VARCHAR2
4707 ,x_msg_count OUT NOCOPY NUMBER
4708 ,x_msg_data OUT NOCOPY VARCHAR2
4709 ) IS
4710
4711 l_init_msg_list VARCHAR2(1);
4712 l_return_status VARCHAR2(1);
4713 l_msg_count NUMBER ;
4714 l_msg_data VARCHAR2(32627);
4715 l_message VARCHAR2(32627);
4716 l_api_name CONSTANT VARCHAR2(50) := 'SUBMIT_CURE_REFUNDS';
4717 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'
4718 || l_api_name;
4719 lp_chdv_rec okl_chd_pvt.chdv_rec_type;
4720 lx_chdv_rec okl_chd_pvt.chdv_rec_type;
4721
4722
4723 lp_tapv_tbl okl_tap_pvt.tapv_tbl_type;
4724 lx_tapv_tbl okl_tap_pvt.tapv_tbl_type;
4725 lp_taiv_tbl okl_tai_pvt.taiv_tbl_type;
4726 lx_taiv_tbl okl_tai_pvt.taiv_tbl_type;
4727 lp_crsv_tbl okl_crs_pvt.crsv_tbl_type;
4728 xp_crsv_tbl okl_crs_pvt.crsv_tbl_type;
4729
4730 cursor c_getobj(p_cure_refund_header_id IN NUMBER ) is
4731 select object_version_number from okl_cure_refund_headers_b
4732 where cure_refund_header_id =p_cure_refund_header_id;
4733
4734
4735 cursor c_get_refunds (p_refund_header_id IN NUMBER)
4736 is select crf.cure_refund_stage_id,
4737 crf.tai_id, crf.tap_id,
4738 crs.object_version_number
4739 from okl_cure_refunds crf,
4740 okl_cure_refund_stage crs
4741 where crf.cure_refund_header_id =p_refund_header_id
4742 and crs.cure_refund_stage_id=crf.cure_refund_stage_id;
4743
4744 next_row integer;
4745 tai_next_row integer;
4746 BEGIN
4747 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refunds : START ');
4748
4749 SAVEPOINT SUBMIT_CURE_REFUNDS;
4750 -- Initialize message list if p_init_msg_list is set to TRUE.
4751 IF FND_API.to_Boolean( p_init_msg_list )
4752 THEN
4753 FND_MSG_PUB.initialize;
4754 END IF;
4755
4756 IF p_status ='APPROVED' THEN
4757
4758 submit_cure_refund_hdr
4759 ( p_api_version =>p_api_version
4760 ,p_init_msg_list =>p_init_msg_list
4761 ,p_commit =>p_commit
4762 ,p_refund_header_id =>p_refund_header_id
4763 ,x_return_status => l_return_status
4764 ,x_msg_count => l_msg_count
4765 ,x_msg_data => l_msg_data);
4766 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refunds : submit_cure_refund_hdr : '||l_return_status);
4767
4768 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4769 Get_Messages (l_msg_count,l_message);
4770 IF PG_DEBUG < 11 THEN
4771 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4772 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
4773 END IF;
4774 END IF;
4775 raise FND_API.G_EXC_ERROR;
4776 ELSE
4777 IF PG_DEBUG < 11 THEN
4778 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4779 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Success -updated TAP ' );
4780 END IF;
4781 END IF;
4782 FND_MSG_PUB.initialize;
4783 END IF;
4784
4785 ELSE
4786 /** logic**
4787 1) update tai -status to 'REJECTED'--if offset contract is populated
4788 2) update tap -status to 'REJECTED'
4789 4)update cure_refund_stage -status back to 'ENTERED'
4790 5) update cure_refund_headers -status to 'REJECTED'
4791 **/
4792
4793 IF PG_DEBUG < 11 THEN
4794 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4795 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' cure refund header id ' ||
4796 p_refund_header_id);
4797 END IF;
4798 END IF;
4799
4800 FOR i in c_get_refunds (p_refund_header_id)
4801 LOOP
4802 IF i.tai_id is not null THEN
4803 tai_next_row := nvl(lp_taiv_tbl.LAST,0) +1;
4804 lp_taiv_tbl(tai_next_row).id :=i.tai_id;
4805 lp_taiv_tbl(tai_next_row).trx_status_code :='REJECTED';
4806 END IF;
4807 next_row := nvl(lp_tapv_tbl.LAST,0) +1;
4808 lp_tapv_tbl(next_row).id :=i.tap_id;
4809 lp_tapv_tbl(next_row).trx_status_code :='REJECTED';
4810
4811 lp_crsv_tbl(next_row).cure_refund_stage_id
4812 :=i.cure_refund_stage_id;
4813 lp_crsv_tbl(next_row).status
4814 :='ENTERED';
4815
4816 lp_crsv_tbl(next_row).object_version_number
4817 :=i.object_version_number;
4818
4819 END LOOP;
4820
4821 --Update trx ar invoices
4822 --set error message,so this will be prefixed before the
4823 --actual message, so it makes more sense than displaying an
4824 -- OKL message.
4825 AddfailMsg(
4826 p_object => 'RECORD IN OKL_TRX_AR_INVOICES',
4827 p_operation => 'UPDATE' );
4828
4829 okl_trx_ap_invoices_pub.update_trx_ap_invoices(
4830 p_api_version => 1.0
4831 ,p_init_msg_list => 'T'
4832 ,x_return_status => l_return_status
4833 ,x_msg_count => l_msg_count
4834 ,x_msg_data => l_msg_data
4835 ,p_tapv_tbl => lp_tapv_tbl
4836 ,x_tapv_tbl => lx_tapv_tbl);
4837 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refunds : okl_trx_ap_invoices_pub.update_trx_ap_invoices : '||l_return_status);
4838 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4839 Get_Messages (l_msg_count,l_message);
4840 IF PG_DEBUG <11 THEN
4841 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4842 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
4843 END IF;
4844 END IF;
4845 raise FND_API.G_EXC_ERROR;
4846 ELSE
4847 IF PG_DEBUG < 11 THEN
4848 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4849 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Succesfully updated tap records');
4850 END IF;
4851 END IF;
4852 FND_MSG_PUB.initialize;
4853 END IF;
4854
4855 IF lp_taiv_tbl.COUNT > 0 THEN
4856 --Update trx ar invoices
4857 --set error message,so this will be prefixed before the
4858 --actual message, so it makes more sense than displaying an
4859 -- OKL message.
4860 AddfailMsg(
4861 p_object => 'RECORD IN OKL_TRX_AR_INVOICES',
4862 p_operation => 'UPDATE' );
4863
4864
4865 -- ASHIM CHANGE - START
4866
4867
4868
4869 okl_trx_ar_invoices_pub.update_trx_ar_invoices(
4870 p_api_version => 1.0
4871 ,p_init_msg_list => 'T'
4872 ,x_return_status => l_return_status
4873 ,x_msg_count => l_msg_count
4874 ,x_msg_data => l_msg_data
4875 ,p_taiv_tbl => lp_taiv_tbl
4876 ,x_taiv_tbl => lx_taiv_tbl);
4877
4878 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refunds : okl_trx_ar_invoices_pub.update_trx_ar_invoices : '||l_return_status);
4879 -- ASHIM CHANGE - END
4880
4881
4882 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4883 Get_Messages (l_msg_count,l_message);
4884 IF PG_DEBUG <11 THEN
4885 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4886 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
4887 END IF;
4888 END IF;
4889 raise FND_API.G_EXC_ERROR;
4890 ELSE
4891 IF PG_DEBUG < 11 THEN
4892 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4893 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Succesfully updated tai records');
4894 END IF;
4895 END IF;
4896 FND_MSG_PUB.initialize;
4897 END IF;
4898 END IF; -- if tai table count > 0
4899
4900 --Update OKL_CURE_REFUND_STAGE
4901 --set error message,so this will be prefixed before the
4902 --actual message, so it makes more sense than displaying an
4903 -- OKL message.
4904 AddfailMsg(
4905 p_object => 'RECORD IN OKL_CURE_REFUND_STAGE',
4906 p_operation => 'UPDATE' );
4907
4908 OKL_cure_rfnd_stage_pub.update_cure_refunds(
4909 p_api_version => 1.0
4910 ,p_init_msg_list =>'F'
4911 ,x_return_status => l_return_status
4912 ,x_msg_count => l_msg_count
4913 ,x_msg_data => l_msg_data
4914 ,p_crsv_tbl => lp_crsv_tbl
4915 ,x_crsv_tbl => xp_crsv_tbl);
4916 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refunds : OKL_cure_rfnd_stage_pub.update_cure_refunds : '||l_return_status);
4917 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4918 Get_Messages (l_msg_count,l_message);
4919 IF PG_DEBUG < 11 THEN
4920 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4921 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
4922 END IF;
4923 END IF;
4924 raise FND_API.G_EXC_ERROR;
4925 ELSE
4926 IF PG_DEBUG < 11 THEN
4927 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4928 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Success -updated cure refund header' );
4929 END IF;
4930 END IF;
4931 END IF;
4932
4933 END IF; --p_status
4934
4935 --Update Cure refunds table
4936 --set error message,so this will be prefixed before the
4937 --actual message, so it makes more sense than displaying an
4938 -- OKL message.
4939 AddfailMsg(
4940 p_object => 'RECORD IN OKL_CURE_REFUND_HEADERS ',
4941 p_operation => 'UPDATE' );
4942
4943
4944
4945 lp_chdv_rec.cure_refund_header_id :=p_refund_header_id;
4946 lp_chdv_rec.refund_status :=p_status;
4947
4948 OPEN c_getobj(p_refund_header_id);
4949 FETCH c_getobj INTO lp_chdv_rec.object_version_number;
4950 CLOSE c_getobj;
4951
4952 OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr(
4953 p_api_version => 1.0
4954 ,p_init_msg_list => 'F'
4955 ,x_return_status => l_return_status
4956 ,x_msg_count => l_msg_count
4957 ,x_msg_data => l_msg_data
4958 ,p_chdv_rec => lp_chdv_rec
4959 ,x_chdv_rec => lx_chdv_rec);
4960
4961 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refunds : OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr : '||l_return_status);
4962
4963 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4964 Get_Messages (l_msg_count,l_message);
4965 IF PG_DEBUG < 11 THEN
4966 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4967 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
4968 END IF;
4969 END IF;
4970 raise FND_API.G_EXC_ERROR;
4971 ELSE
4972 IF PG_DEBUG < 11 THEN
4973 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
4974 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Success -updated cure refund header' );
4975 END IF;
4976 END IF;
4977 FND_MSG_PUB.initialize;
4978 END IF;
4979
4980
4981 IF x_return_status =FND_API.G_RET_STS_SUCCESS THEN
4982 FND_MSG_PUB.initialize;
4983 END IF;
4984
4985
4986 -- Standard check for p_commit
4987 IF FND_API.to_Boolean( p_commit )
4988 THEN
4989 COMMIT WORK;
4990 END IF;
4991
4992 FND_MSG_PUB.Count_And_Get
4993 ( p_count => x_msg_count,
4994 p_data => x_msg_data
4995 );
4996
4997 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: submit_cure_refunds : END ');
4998 EXCEPTION
4999 WHEN Fnd_Api.G_EXC_ERROR THEN
5000 ROLLBACK TO SUBMIT_CURE_REFUNDS;
5001 x_return_status := Fnd_Api.G_RET_STS_ERROR;
5002 x_msg_count := l_msg_count ;
5003 x_msg_data := l_msg_data ;
5004 Fnd_Msg_Pub.count_and_get(
5005 p_count => x_msg_count
5006 ,p_data => x_msg_data);
5007 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
5008 ROLLBACK TO SUBMIT_CURE_REFUNDS;
5009 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
5010 x_msg_count := l_msg_count ;
5011 x_msg_data := l_msg_data ;
5012 Fnd_Msg_Pub.count_and_get(
5013 p_count => x_msg_count
5014 ,p_data => x_msg_data);
5015 WHEN OTHERS THEN
5016 ROLLBACK TO SUBMIT_CURE_REFUNDS;
5017 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
5018 x_msg_count := l_msg_count ;
5019 x_msg_data := l_msg_data ;
5020 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_PAY_CURE_REFUNDS_PVT','SUBMIT_CURE_REFUNDS');
5021 Fnd_Msg_Pub.count_and_get(
5022 p_count => x_msg_count
5023 ,p_data => x_msg_data);
5024
5025
5026 END submit_cure_refunds;
5027
5028 /**
5029 called from the workflow to update cure refunds based on
5030 the approval
5031 **/
5032 PROCEDURE set_approval_status (itemtype in varchar2,
5033 itemkey in varchar2,
5034 actid in number,
5035 funcmode in varchar2,
5036 result out nocopy varchar2) IS
5037 l_api_version NUMBER := 1;
5038 l_init_msg_list VARCHAR2(1);
5039 l_return_status VARCHAR2(1);
5040 l_msg_count NUMBER ;
5041 l_msg_data VARCHAR2(32627);
5042 l_message VARCHAR2(32627) :=NULL;
5043
5044 l_refund_header_id VARCHAR2(32627);
5045 --okl_cure_refund_headers_b.cure_refund_header_id%TYPE;
5046 l_refund_status okl_cure_refund_headers_b.refund_status%TYPE;
5047 l_nid NUMBER;
5048 l_role_name VARCHAR2(30);
5049
5050 cursor c_getobj(p_cure_refund_header_id IN NUMBER ) is
5051 select object_version_number from okl_cure_refund_headers_b
5052 where cure_refund_header_id =p_cure_refund_header_id;
5053
5054 lp_chdv_rec okl_chd_pvt.chdv_rec_type;
5055 lx_chdv_rec okl_chd_pvt.chdv_rec_type;
5056
5057
5058
5059
5060 BEGIN
5061 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: set_approval_status : START ');
5062
5063 IF (G_DEBUG_ENABLED = 'Y') THEN
5064 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
5065 END IF;
5066
5067 if funcmode <> 'RUN' then
5068 result := wf_engine.eng_null;
5069 return;
5070 end if;
5071
5072 l_refund_header_id := wf_engine.GetItemAttrText(
5073 itemtype => itemtype,
5074 itemkey => itemkey,
5075 aname => 'CURE_REFUND_HEADER_ID');
5076
5077 IF PG_DEBUG < 11 THEN
5078 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
5079 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,' cure refund header id ' ||
5080 l_refund_header_id);
5081 END IF;
5082 END IF;
5083
5084 OKL_PAY_CURE_REFUNDS_PVT.SUBMIT_CURE_REFUNDS(
5085 p_api_version => 1.0
5086 ,p_init_msg_list => 'T'
5087 ,p_commit => 'F'
5088 ,p_status => 'APPROVED'
5089 ,p_refund_header_id => to_number(l_refund_header_id)
5090 ,x_return_status => l_return_status
5091 ,x_msg_count => l_msg_count
5092 ,x_msg_data => l_msg_data
5093 );
5094 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: set_approval_status : OKL_PAY_CURE_REFUNDS_PVT.SUBMIT_CURE_REFUNDS : '||l_return_status);
5095 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5096 Get_Messages (l_msg_count,l_message);
5097 IF PG_DEBUG < 11 THEN
5098 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
5099 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error---->' ||l_message);
5100 END IF;
5101 --set error message
5102 -- sent a notification to SYSADMIN with the error message
5103 -- Also update the refund_header to IN_PROGRESS
5104 END IF;
5105
5106 wf_engine.SetItemAttrText(itemtype => itemtype,
5107 itemkey => itemkey,
5108 aname => 'ERROR_MESSAGE',
5109 avalue => l_message);
5110
5111 --the message is sent to the SYSADMIN
5112 -- could be sent to any one , only need to populate the notify_error attribute
5113
5114 wf_engine.SetItemAttrText(itemtype => itemtype,
5115 itemkey => itemkey,
5116 aname => 'NOTIFY_ERROR',
5117 avalue => 'SYSADMIN');
5118
5119
5120 result := wf_engine.eng_completed ||':'||'E';
5121
5122 lp_chdv_rec.cure_refund_header_id := l_refund_header_id;
5123 lp_chdv_rec.refund_status := 'IN_PROGRESS';
5124
5125 OPEN c_getobj(l_refund_header_id);
5126 FETCH c_getobj INTO lp_chdv_rec.object_version_number;
5127 CLOSE c_getobj;
5128
5129 OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr(
5130 p_api_version => 1.0
5131 ,p_init_msg_list => 'F'
5132 ,x_return_status => l_return_status
5133 ,x_msg_count => l_msg_count
5134 ,x_msg_data => l_msg_data
5135 ,p_chdv_rec => lp_chdv_rec
5136 ,x_chdv_rec => lx_chdv_rec);
5137 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: set_approval_status : OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr : '||l_return_status);
5138 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5139 Get_Messages (l_msg_count,l_message);
5140 IF PG_DEBUG < 11 THEN
5141 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
5142 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error in update of cure refund to PENDINGI' ||l_message);
5143 END IF;
5144 END IF;
5145 raise FND_API.G_EXC_ERROR;
5146 ELSE
5147 IF PG_DEBUG < 11 THEN
5148 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
5149 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Success -updated cure refund header' );
5150 END IF;
5151 END IF;
5152 END IF;
5153
5154 ELSE
5155 IF PG_DEBUG < 11 THEN
5156 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
5157 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Success -updated cure refund header' );
5158 END IF;
5159 END IF;
5160
5161 --send notification to Vendor
5162 --get Vendor_role ( if null do not send notification,
5163 --that means there are no notifications)
5164 --
5165 l_role_name :=wf_engine.GetItemAttrText(
5166 itemtype => itemtype,
5167 itemkey => itemkey,
5168 aname => 'VENDOR_ROLE');
5169 IF PG_DEBUG < 11 THEN
5170 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
5171 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,
5172 'Vendor Role Name is ' ||l_role_name );
5173 END IF;
5174 END IF;
5175
5176 -- Role name will be populated if there are offset contracts
5177 -- and notification will be sent if there are offset contract
5178 -- and result is 'Y to sent notifications
5179 if l_role_name is not null THEN
5180 result := wf_engine.eng_completed ||':'||'Y';
5181 else
5182 result := wf_engine.eng_completed ||':'||'N';
5183 End if;
5184
5185 END IF;
5186
5187
5188 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: set_approval_status : END ');
5189
5190 EXCEPTION
5191 WHEN FND_API.G_EXC_ERROR THEN
5192 --resultout := wf_engine.eng_completed ||':'||wf_no;
5193 wf_core.context('OKL_PAY_CURE_REFUNDS_PVT',
5194 'set_approval_status',
5195 itemtype,
5196 itemkey,
5197 to_char(actid),
5198 funcmode);
5199 raise;
5200
5201 when others then
5202 --resultout := wf_engine.eng_completed ||':'||wf_no;
5203 wf_core.context('OKL_PAY_CURE_REFUNDS_PVT',
5204 'set_approval_status',
5205 itemtype,
5206 itemkey,
5207 to_char(actid),
5208 funcmode);
5209 raise;
5210
5211 END set_approval_status;
5212 /**
5213 called from the workflow to update cure refunds based on
5214 the approval
5215 **/
5216 PROCEDURE set_reject_status (itemtype in varchar2,
5217 itemkey in varchar2,
5218 actid in number,
5219 funcmode in varchar2,
5220 result out nocopy varchar2) IS
5221 l_api_version NUMBER := 1;
5222 l_init_msg_list VARCHAR2(1);
5223 l_return_status VARCHAR2(1);
5224 l_msg_count NUMBER ;
5225 l_msg_data VARCHAR2(32627);
5226 l_message VARCHAR2(32627);
5227
5228 l_refund_header_id VARCHAR2(32627);
5229 l_refund_status okl_cure_refund_headers_b.refund_status%TYPE;
5230
5231
5232 BEGIN
5233 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: set_reject_status : START ');
5234 IF (G_DEBUG_ENABLED = 'Y') THEN
5235 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
5236 END IF;
5237
5238 if funcmode <> 'RUN' then
5239 result := wf_engine.eng_null;
5240 return;
5241 end if;
5242
5243 l_refund_header_id := wf_engine.GetItemAttrText(
5244 itemtype => itemtype,
5245 itemkey => itemkey,
5246 aname => 'CURE_REFUND_HEADER_ID');
5247
5248
5249 OKL_PAY_CURE_REFUNDS_PVT.SUBMIT_CURE_REFUNDS(
5250 p_api_version => 1.0
5251 ,p_init_msg_list => 'T'
5252 ,p_commit => 'F'
5253 ,p_status => 'REJECTED'
5254 ,p_refund_header_id => to_number(l_refund_header_id)
5255 ,x_return_status => l_return_status
5256 ,x_msg_count => l_msg_count
5257 ,x_msg_data => l_msg_data
5258 );
5259 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: set_reject_status : OKL_PAY_CURE_REFUNDS_PVT.SUBMIT_CURE_REFUNDS : '||l_return_status);
5260 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5261 Get_Messages (l_msg_count,l_message);
5262 IF PG_DEBUG < 11 THEN
5263 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
5264 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
5265 END IF;
5266 END IF;
5267 raise FND_API.G_EXC_ERROR;
5268 ELSE
5269 IF PG_DEBUG < 11 THEN
5270 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
5271 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Success -updated cure refund header' );
5272 END IF;
5273 END IF;
5274 END IF;
5275 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: set_reject_status : END ');
5276 EXCEPTION
5277 WHEN FND_API.G_EXC_ERROR THEN
5278 --resultout := wf_engine.eng_completed ||':'||wf_no;
5279 wf_core.context('OKL_PAY_CURE_REFUNDS_PVT',
5280 'set_reject_status',
5281 itemtype,
5282 itemkey,
5283 to_char(actid),
5284 funcmode);
5285 raise;
5286
5287 when others then
5288 --resultout := wf_engine.eng_completed ||':'||wf_no;
5289 wf_core.context('OKL_PAY_CURE_REFUNDS_PVT',
5290 'set_reject_status',
5291 itemtype,
5292 itemkey,
5293 to_char(actid),
5294 funcmode);
5295 raise;
5296
5297 END set_reject_status;
5298
5299
5300 PROCEDURE create_refund_headers
5301 ( p_api_version IN NUMBER
5302 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_TRUE
5303 ,p_commit IN VARCHAR2 DEFAULT OKC_API.G_FALSE
5304 ,p_pay_cure_refunds_rec IN pay_cure_refunds_rec_type
5305 ,x_cure_refund_header_id OUT NOCOPY NUMBER
5306 ,x_return_status OUT NOCOPY VARCHAR2
5307 ,x_msg_count OUT NOCOPY NUMBER
5308 ,x_msg_data OUT NOCOPY VARCHAR2
5309 ) IS
5310 l_init_msg_list VARCHAR2(1);
5311 l_return_status VARCHAR2(1);
5312 l_msg_count NUMBER ;
5313 l_msg_data VARCHAR2(32627);
5314 l_message VARCHAR2(32627);
5315 l_cure_refund_id okl_cure_refunds.cure_refund_id%type;
5316 l_cure_refund_header_id okl_cure_refund_headers_b.cure_refund_header_id%type;
5317 l_cure_refund_header_number okl_cure_refund_headers_b.refund_header_number%type;
5318 l_api_name CONSTANT VARCHAR2(50) := 'CREATE_REFUND_HEADERS';
5319 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'
5320 || l_api_name;
5321
5322 lp_chdv_rec okl_chd_pvt.chdv_rec_type;
5323 lx_chdv_rec okl_chd_pvt.chdv_rec_type;
5324
5325 x_pay_tbl pay_cure_refunds_tbl_type;
5326
5327 l_pay_cure_refunds_rec pay_cure_refunds_rec_type;
5328 cursor chk_refund_number(p_refund_header_number IN VARCHAR2) IS
5329 select refund_header_number
5330 from okl_cure_refund_headers_b
5331 where refund_header_number =p_refund_header_number;
5332
5333 x_contract_number okc_k_headers_b.contract_number%TYPE;
5334
5335 BEGIN
5336 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund_headers : START ');
5337 SAVEPOINT CREATE_REFUND_HEADERS;
5338 -- Initialize message list if p_init_msg_list is set to TRUE.
5339 IF FND_API.to_Boolean( p_init_msg_list )
5340 THEN
5341 FND_MSG_PUB.initialize;
5342 END IF;
5343
5344 x_return_status := FND_API.G_RET_STS_SUCCESS;
5345
5346 --duplicate refund_number check
5347 OPEN chk_refund_number(p_pay_cure_refunds_rec.refund_number);
5348 FETCH chk_refund_number INTO l_cure_refund_header_number;
5349 CLOSE chk_refund_number;
5350 if l_cure_refund_header_number IS NOT NULL THEN
5351
5352 IF PG_DEBUG < 11 THEN
5353 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
5354 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'duplicate refund number' );
5355 END IF;
5356 END IF;
5357 fnd_message.set_name('OKL', 'OKL_DUPLICATE_REFUND_NUMBER');
5358 fnd_msg_pub.add;
5359 RAISE FND_API.G_EXC_ERROR;
5360 END IF;
5361
5362
5363 --create HEADERS first
5364 --create cure refund HEADERS record
5365 lp_chdv_rec.refund_header_number := p_pay_cure_refunds_rec.refund_number;
5366 lp_chdv_rec.refund_type := 'ALL';
5367 lp_chdv_rec.vendor_site_id := p_pay_cure_refunds_rec.vendor_site_id;
5368 -- lp_chdv_rec.disbursement_amount := p_pay_cure_refunds_rec.refund_amount;
5369 -- lp_chdv_rec.total_refund_due := p_pay_cure_refunds_rec.refund_amount_due;
5370 lp_chdv_rec.refund_due_date := p_pay_cure_refunds_rec.invoice_date;
5371 lp_chdv_rec.object_version_number := 1;
5372 lp_chdv_rec.description := p_pay_cure_refunds_rec.description;
5373 lp_chdv_rec.refund_status :='IN_PROGRESS';
5374 lp_chdv_rec.currency_code :=p_pay_cure_refunds_rec.currency;
5375 lp_chdv_rec.payment_method :=p_pay_cure_refunds_rec.payment_method_code;
5376 lp_chdv_rec.payment_term_id :=p_pay_cure_refunds_rec.pay_terms;
5377 --lp_chdv_rec.chr_id :=p_pay_cure_refunds_rec.chr_id;
5378 --lp_chdv_rec.vendor_site_cure_due :=p_pay_cure_refunds_rec.vendor_site_cure_due;
5379 --lp_chdv_rec.vendor_cure_due :=p_pay_cure_refunds_rec.vendor_cure_due;
5380
5381 l_pay_cure_refunds_rec :=p_pay_cure_refunds_rec;
5382
5383 OKL_cure_rfnd_hdr_pub.insert_cure_rfnd_hdr(
5384 p_api_version => 1.0
5385 ,p_init_msg_list => 'T'
5386 ,x_return_status => l_return_status
5387 ,x_msg_count => l_msg_count
5388 ,x_msg_data => l_msg_data
5389 ,p_chdv_rec => lp_chdv_rec
5390 ,x_chdv_rec => lx_chdv_rec);
5391 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund_headers : OKL_cure_rfnd_hdr_pub.insert_cure_rfnd_hdr : '||l_return_status);
5392 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5393 Get_Messages (l_msg_count,l_message);
5394 IF PG_DEBUG < 11 THEN
5395 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
5396 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
5397 END IF;
5398 END IF;
5399 raise FND_API.G_EXC_ERROR;
5400 ELSE
5401 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
5402 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'lx_chdv_rec.cure_refund_header_id'
5403 ||lx_chdv_rec.cure_refund_header_id);
5404 END IF;
5405 l_pay_cure_refunds_rec.refund_header_id :=
5406 lx_chdv_rec.cure_refund_header_id;
5407 x_cure_refund_header_id :=
5408 lx_chdv_rec.cure_refund_header_id;
5409 END IF;
5410
5411
5412
5413 -- Standard check for p_commit
5414 IF FND_API.to_Boolean( p_commit )
5415 THEN
5416 COMMIT WORK;
5417 END IF;
5418
5419 FND_MSG_PUB.Count_And_Get
5420 ( p_count => x_msg_count,
5421 p_data => x_msg_data
5422 );
5423
5424 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: create_refund_headers : END ');
5425 EXCEPTION
5426
5427 WHEN Fnd_Api.G_EXC_ERROR THEN
5428 ROLLBACK TO CREATE_REFUND_HEADERS;
5429 x_return_status := Fnd_Api.G_RET_STS_ERROR;
5430 x_msg_count := l_msg_count ;
5431 x_msg_data := l_msg_data ;
5432 Fnd_Msg_Pub.count_and_get(
5433 p_count => x_msg_count
5434 ,p_data => x_msg_data);
5435 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
5436 ROLLBACK TO CREATE_REFUND_HEADERS;
5437 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
5438 x_msg_count := l_msg_count ;
5439 x_msg_data := l_msg_data ;
5440 Fnd_Msg_Pub.count_and_get(
5441 p_count => x_msg_count
5442 ,p_data => x_msg_data);
5443 WHEN OTHERS THEN
5444 ROLLBACK TO CREATE_REFUND_HEADERS;
5445 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
5446 x_msg_count := l_msg_count ;
5447 x_msg_data := l_msg_data ;
5448 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_PAY_CURE_REFUNDS_PVT','CREATE_REFUND_HEADERS');
5449 Fnd_Msg_Pub.count_and_get(
5450 p_count => x_msg_count
5451 ,p_data => x_msg_data);
5452
5453
5454
5455 END create_refund_headers;
5456
5457 PROCEDURE update_refund_headers
5458 ( p_api_version IN NUMBER
5459 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_TRUE
5460 ,p_commit IN VARCHAR2 DEFAULT OKC_API.G_FALSE
5461 ,p_pay_cure_refunds_rec IN pay_cure_refunds_rec_type
5462 ,x_return_status OUT NOCOPY VARCHAR2
5463 ,x_msg_count OUT NOCOPY NUMBER
5464 ,x_msg_data OUT NOCOPY VARCHAR2
5465 )IS
5466 cursor c_get_tap_ids (p_cure_refund_header_id IN NUMBER ) is
5467 select a.tap_id,
5468 a.cure_refund_id,
5469 a.object_version_number,
5470 b.invoice_number
5471 from okl_cure_refunds a, okl_trx_ap_invoices_b b
5472 where cure_refund_header_id =p_cure_refund_header_id
5473 and a.tap_id =b.id;
5474
5475 cursor c_getobj(p_cure_refund_header_id IN NUMBER ) is
5476 select object_version_number from okl_cure_refund_headers_b
5477 where cure_refund_header_id =p_cure_refund_header_id;
5478
5479
5480
5481 l_init_msg_list VARCHAR2(1);
5482 l_return_status VARCHAR2(1);
5483 l_msg_count NUMBER ;
5484 l_msg_data VARCHAR2(32627);
5485 l_message VARCHAR2(32627);
5486 l_api_name CONSTANT VARCHAR2(50) := 'UPDATE_REFUND_HEADERS';
5487 l_api_name_full CONSTANT VARCHAR2(150):= g_pkg_name || '.'
5488 || l_api_name;
5489
5490 lp_tapv_tbl okl_tap_pvt.tapv_tbl_type;
5491 lx_tapv_tbl okl_tap_pvt.tapv_tbl_type;
5492 lp_chdv_rec okl_chd_pvt.chdv_rec_type;
5493 lx_chdv_rec okl_chd_pvt.chdv_rec_type;
5494 next_row integer;
5495 lp_crfv_tbl okl_crf_pvt.crfv_tbl_type;
5496 lx_crfv_tbl okl_crf_pvt.crfv_tbl_type;
5497
5498 BEGIN
5499 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: update_refund_headers : START');
5500 SAVEPOINT UPDATE_REFUND_HEADERS;
5501
5502 -- Initialize message list if p_init_msg_list is set to TRUE.
5503 IF FND_API.to_Boolean( p_init_msg_list )
5504 THEN
5505 FND_MSG_PUB.initialize;
5506 END IF;
5507 x_return_status := FND_API.G_RET_STS_SUCCESS;
5508
5509 --set error message,so this will be prefixed before the
5510 --actual message, so it makes more sense than displaying an
5511 -- OKL message.
5512 AddfailMsg(
5513 p_object => 'RECORD IN OKL_CURE_REFUND_HEADERS ',
5514 p_operation => 'UPDATE' );
5515
5516 lp_chdv_rec.cure_refund_header_id :=p_pay_cure_refunds_rec.refund_header_id;
5517 lp_chdv_rec.refund_due_date :=p_pay_cure_refunds_rec.invoice_date;
5518 lp_chdv_rec.payment_method :=p_pay_cure_refunds_rec.payment_method_code;
5519 lp_chdv_rec.payment_term_id :=p_pay_cure_refunds_rec.pay_terms;
5520 lp_chdv_rec.description :=p_pay_cure_refunds_rec.description;
5521
5522 OPEN c_getobj(p_pay_cure_refunds_rec.refund_header_id);
5523 FETCH c_getobj INTO lp_chdv_rec.object_version_number;
5524 CLOSE c_getobj;
5525
5526
5527 OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr(
5528 p_api_version => 1.0
5529 ,p_init_msg_list => 'F'
5530 ,x_return_status => l_return_status
5531 ,x_msg_count => l_msg_count
5532 ,x_msg_data => l_msg_data
5533 ,p_chdv_rec => lp_chdv_rec
5534 ,x_chdv_rec => lx_chdv_rec);
5535 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: update_refund_headers : OKL_cure_rfnd_hdr_pub.update_cure_rfnd_hdr :'||l_return_status);
5536 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5537 Get_Messages (l_msg_count,l_message);
5538 IF PG_DEBUG < 11 THEN
5539 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
5540 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Error' ||l_message);
5541 END IF;
5542 END IF;
5543 raise FND_API.G_EXC_ERROR;
5544 ELSE
5545 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
5546 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'Successfully updated Cure refund '||
5547 'header table');
5548
5549 END IF;
5550 END IF;
5551
5552 IF x_return_status =FND_API.G_RET_STS_SUCCESS THEN
5553 FND_MSG_PUB.initialize;
5554 END IF;
5555
5556
5557 -- Standard check for p_commit
5558 IF FND_API.to_Boolean( p_commit )
5559 THEN
5560 COMMIT WORK;
5561 END IF;
5562
5563 FND_MSG_PUB.Count_And_Get
5564 ( p_count => x_msg_count,
5565 p_data => x_msg_data
5566 );
5567
5568 okl_debug_pub.logmessage ('OKL_PAY_CURE_REFUNDS_PVT: update_refund_headers : END');
5569 EXCEPTION
5570
5571 WHEN Fnd_Api.G_EXC_ERROR THEN
5572 ROLLBACK TO UPDATE_REFUND_HEADERS;
5573 x_return_status := Fnd_Api.G_RET_STS_ERROR;
5574 x_msg_count := l_msg_count ;
5575 x_msg_data := l_msg_data ;
5576 Fnd_Msg_Pub.count_and_get(
5577 p_count => x_msg_count
5578 ,p_data => x_msg_data);
5579 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
5580 ROLLBACK TO UPDATE_REFUND_HEADERS;
5581 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
5582 x_msg_count := l_msg_count ;
5583 x_msg_data := l_msg_data ;
5584 Fnd_Msg_Pub.count_and_get(
5585 p_count => x_msg_count
5586 ,p_data => x_msg_data);
5587 WHEN OTHERS THEN
5588 ROLLBACK TO UPDATE_REFUND_HEADERS;
5589 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
5590 x_msg_count := l_msg_count ;
5591 x_msg_data := l_msg_data ;
5592 Fnd_Msg_Pub.ADD_EXC_MSG('OKL_PAY_CURE_REFUNDS_PVT','UPDATE_REFUND_HEADERS');
5593 Fnd_Msg_Pub.count_and_get(
5594 p_count => x_msg_count
5595 ,p_data => x_msg_data);
5596
5597
5598
5599 END update_refund_headers;
5600
5601 PROCEDURE create_refund_details
5602 ( p_api_version IN NUMBER
5603 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_TRUE
5604 ,p_commit IN VARCHAR2 DEFAULT OKC_API.G_FALSE
5605 ,p_pay_cure_refunds_tbl IN pay_cure_refunds_tbl_type
5606 ,x_return_status OUT NOCOPY VARCHAR2
5607 ,x_msg_count OUT NOCOPY NUMBER
5608 ,x_msg_data OUT NOCOPY VARCHAR2
5609 )IS
5610 BEGIN
5611
5612 null;
5613 END create_refund_details ;
5614
5615 PROCEDURE update_refund_details
5616 ( p_api_version IN NUMBER
5617 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_TRUE
5618 ,p_commit IN VARCHAR2 DEFAULT OKC_API.G_FALSE
5619 ,p_pay_cure_refunds_tbl IN pay_cure_refunds_tbl_type
5620 ,x_return_status OUT NOCOPY VARCHAR2
5621 ,x_msg_count OUT NOCOPY NUMBER
5622 ,x_msg_data OUT NOCOPY VARCHAR2
5623 )IS
5624 BEGIN
5625
5626 null;
5627 END update_refund_details ;
5628
5629 PROCEDURE delete_refund_details
5630 ( p_api_version IN NUMBER
5631 ,p_init_msg_list IN VARCHAR2 DEFAULT OKC_API.G_TRUE
5632 ,p_commit IN VARCHAR2 DEFAULT OKC_API.G_FALSE
5633 ,p_pay_cure_refunds_tbl IN pay_cure_refunds_tbl_type
5634 ,x_return_status OUT NOCOPY VARCHAR2
5635 ,x_msg_count OUT NOCOPY NUMBER
5636 ,x_msg_data OUT NOCOPY VARCHAR2
5637 ) IS
5638 BEGIN
5639
5640 null;
5641
5642 END delete_refund_details;
5643
5644
5645
5646 PROCEDURE gen_doc (document_id IN VARCHAR2
5647 ,display_type IN VARCHAR2
5648 ,document IN OUT NOCOPY VARCHAR2
5649 ,document_type IN OUT NOCOPY VARCHAR2)
5650 IS
5651
5652
5653 l_cure_refund_header_id NUMBER := TO_NUMBER(document_id);
5654 l_table_row VARCHAR2(1000);
5655
5656 CURSOR c_emps (p_cure_refund_header_id IN NUMBER)
5657 IS
5658 select a.contract_number,
5659 b.offset_amount
5660 from okl_cure_refunds b, okc_k_headers_b a
5661 where a.id =b.offset_contract
5662 and b.cure_refund_header_id =p_cure_refund_header_id;
5663
5664 c_emps_rec c_emps%ROWTYPE;
5665
5666 BEGIN
5667 IF (G_DEBUG_ENABLED = 'Y') THEN
5668 G_IS_DEBUG_STATEMENT_ON := OKL_DEBUG_PUB.CHECK_LOG_ON(G_MODULE, FND_LOG.LEVEL_STATEMENT);
5669 END IF;
5670
5671 IF PG_DEBUG < 11 THEN
5672 IF (G_IS_DEBUG_STATEMENT_ON = true) THEN
5673 OKL_DEBUG_PUB.LOG_DEBUG(FND_LOG.LEVEL_STATEMENT, G_MODULE,'In gen Doc' ||document_id);
5674 END IF;
5675 END IF;
5676
5677 document_type := document_type;
5678 document := NULL;
5679
5680 IF c_emps%ISOPEN THEN
5681 CLOSE c_emps;
5682 END IF;
5683
5684 OPEN c_emps (l_cure_refund_header_id);
5685 LOOP
5686
5687 FETCH c_emps INTO c_emps_rec;
5688 EXIT WHEN c_emps%NOTFOUND;
5689
5690 l_table_row := '<tr><td>'||c_emps_rec.contract_number||'</td><td>'
5691 ||c_emps_rec.offset_amount ||'</td></tr>';
5692
5693 document := document||l_table_row;
5694
5695 END LOOP;
5696 CLOSE c_emps;
5697
5698 -- Close off the HTML table definition
5699
5700 document := document||'</table>';
5701
5702 END gen_doc;
5703 end OKL_PAY_CURE_REFUNDS_PVT;