[Home] [Help]
PACKAGE BODY: APPS.IBY_FD_USER_API_PUB
Source
1 PACKAGE BODY IBY_FD_USER_API_PUB AS
2 /*$Header: ibyfduab.pls 120.21 2010/09/01 16:21:43 gmaheswa ship $*/
3
4 --
5 -- Declare Global variables
6 --
7 SRA_INVALID_INSTR EXCEPTION;
8
9
10 -- Data structure to cache instruction access
11 -- Bug 8850654
12 TYPE t_instr_record_type IS RECORD(
13 access number
14 );
15
16 TYPE instr_table_type IS TABLE OF t_instr_record_type INDEX BY BINARY_INTEGER;
17
18 g_instr_table instr_table_type;
19
20
21 --
22 -- Forward Declarations
23 --
24 PROCEDURE print_debuginfo(p_module IN VARCHAR2,
25 p_debug_text IN VARCHAR2);
26
27 PROCEDURE Validate_Method_and_Profile (
28 p_api_version IN NUMBER,
29 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
30 p_payment_method_code IN VARCHAR2,
31 p_ppp_id IN NUMBER,
32 p_payment_document_id IN NUMBER,
33 p_crt_instr_flag IN VARCHAR2,
34 p_int_bank_acc_arr IN Int_Bank_Acc_Tab_Type,
35 p_le_arr IN Legal_Entity_Tab_Type,
36 p_org_arr IN Org_Tab_Type,
37 p_curr_arr IN Currency_Tab_Type,
38 x_return_status OUT NOCOPY VARCHAR2,
39 x_msg_count OUT NOCOPY NUMBER,
40 x_msg_data OUT NOCOPY VARCHAR2
41 )
42 IS
43 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Method_and_Profile';
44 l_api_version CONSTANT NUMBER := 1.0;
45 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Validate_Method_and_Profile';
46
47 l_count NUMBER;
48 l_le_name VARCHAR2(240);
49 l_org_name VARCHAR2(240);
50 l_acc_name VARCHAR2(240);
51 l_profile_name VARCHAR2(100);
52 l_method_name VARCHAR2(100);
53 l_payment_document_id NUMBER;
54 l_proc_type VARCHAR2(30);
55
56 CURSOR method_appl_cur (p_method_code VARCHAR2,
57 p_driver_type VARCHAR2,
58 p_driver_value VARCHAR2)
59 IS
60 select count(APPLICABLE_PMT_MTHD_ID)
61 from iby_applicable_pmt_mthds
62 where payment_method_code = p_method_code
63 and applicable_type_code = p_driver_type
64 and (applicable_value_to is null or applicable_value_to = p_driver_value);
65
66 CURSOR profile_appl_cur (p_profile_id NUMBER,
67 p_driver_type VARCHAR2,
68 p_driver_value VARCHAR2)
69 IS
70 select count(ap.applicable_pmt_prof_id)
71 from iby_applicable_pmt_profs ap,
72 iby_payment_profiles p
73 where ap.system_profile_code = p.system_profile_code
74 and p.payment_profile_id = p_profile_id
75 and ap.applicable_type_code = p_driver_type
76 and (ap.applicable_value_to is null OR ap.applicable_value_to = p_driver_value);
77
78 CURSOR profile_org_appl_cur (p_profile_id NUMBER,
79 p_driver_id_value VARCHAR2,
80 p_driver_type_value VARCHAR2)
81 IS
82 select count(ap.applicable_pmt_prof_id)
83 from iby_applicable_pmt_profs ap,
84 iby_payment_profiles p
85 where ap.system_profile_code = p.system_profile_code
86 and p.payment_profile_id = p_profile_id
87 and ap.applicable_type_code = 'PAYER_ORG'
88 and ((ap.applicable_value_to is null AND ap.applicable_value_from is null) OR
89 (ap.applicable_value_to = p_driver_id_value AND
90 ap.applicable_value_from = p_driver_type_value));
91
92
93 BEGIN
94 -- Standard call to check for call compatibility.
95 IF NOT FND_API.Compatible_API_Call(l_api_version,
96 p_api_version,
97 l_api_name,
98 G_PKG_NAME) THEN
99 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
100 END IF;
101
102 -- Initialize message list if p_init_msg_list is set to TRUE.
103 IF FND_API.to_Boolean(p_init_msg_list) THEN
104 FND_MSG_PUB.initialize;
105 END IF;
106
107 -- Initialize API return status to success
108 x_return_status := FND_API.G_RET_STS_SUCCESS;
109
110 IF p_payment_method_code is not null THEN
111 print_debuginfo(l_module_name,'Payment method ' || p_payment_method_code || ' is to be validated.');
112
113 select payment_method_name
114 into l_method_name
115 from IBY_PAYMENT_METHODS_VL
116 where payment_method_code = p_payment_method_code;
117
118 -- Validate legal entities for the payment method
119 IF (p_le_arr.COUNT > 0) THEN
120 FOR i in p_le_arr.FIRST..p_le_arr.LAST LOOP
121 OPEN method_appl_cur(p_payment_method_code, 'PAYER_LE', to_char(p_le_arr(i)));
122 FETCH method_appl_cur INTO l_count;
123 CLOSE method_appl_cur;
124
125 IF l_count = 0 THEN
126 select name
127 into l_le_name
128 from XLE_FIRSTPARTY_INFORMATION_V
129 where legal_entity_id = p_le_arr(i);
130
131 FND_MESSAGE.set_name('IBY', 'IBY_AP_VLDT_METHOD_LE');
132 FND_MESSAGE.SET_TOKEN('METHOD', l_method_name);
133 FND_MESSAGE.SET_TOKEN('OBJECT', l_le_name);
134 FND_MSG_PUB.Add;
135
136 x_return_status := FND_API.G_RET_STS_ERROR;
137 END IF;
138 END LOOP;
139 END IF;
140
141 -- Validate organizations for the payment method
142 IF (p_org_arr.COUNT > 0) THEN
143 FOR i in p_org_arr.FIRST..p_org_arr.LAST LOOP
144 OPEN method_appl_cur(p_payment_method_code, 'PAYER_ORG', to_char(p_org_arr(i).org_id));
145 FETCH method_appl_cur INTO l_count;
146 CLOSE method_appl_cur;
147
148 IF l_count = 0 THEN
149 select name
150 into l_org_name
151 from HR_ALL_ORGANIZATION_UNITS_VL
152 where organization_id = p_org_arr(i).org_id;
153
154 FND_MESSAGE.set_name('IBY', 'IBY_AP_VLDT_METHOD_ORG');
155 FND_MESSAGE.SET_TOKEN('METHOD', l_method_name);
156 FND_MESSAGE.SET_TOKEN('OBJECT', l_org_name);
157 FND_MSG_PUB.Add;
158
159 x_return_status := FND_API.G_RET_STS_ERROR;
160 END IF;
161 END LOOP;
162 END IF;
163
164 END IF; -- if payment method is not null
165
166 IF p_ppp_id is not null THEN
167 print_debuginfo(l_module_name,'Payment profile ' || p_ppp_id || ' is to be validated.');
168
169 select payment_profile_name
170 into l_profile_name
171 from IBY_PAYMENT_PROFILES
172 where payment_profile_id = p_ppp_id;
173
174 -- Validate legal entities for the payment profile
175 IF (p_int_bank_acc_arr.COUNT > 0) THEN
176 FOR i in p_int_bank_acc_arr.FIRST..p_int_bank_acc_arr.LAST LOOP
177 OPEN profile_appl_cur(p_ppp_id, 'INTERNAL_BANK_ACCOUNT', to_char(p_int_bank_acc_arr(i)));
178 FETCH profile_appl_cur INTO l_count;
179 CLOSE profile_appl_cur;
180
181 IF l_count = 0 THEN
182 select bank_account_name
183 into l_acc_name
184 from CE_INTERNAL_BANK_ACCOUNTS_V
185 where bank_account_id = p_int_bank_acc_arr(i);
186
187 FND_MESSAGE.set_name('IBY', 'IBY_AP_VLDT_PROF_ACC');
188 FND_MESSAGE.SET_TOKEN('PROFILE', l_profile_name);
189 FND_MESSAGE.SET_TOKEN('OBJECT', l_acc_name);
190 FND_MSG_PUB.Add;
191
192 x_return_status := FND_API.G_RET_STS_ERROR;
193 END IF;
194 END LOOP;
195 END IF;
196
197 -- Validate organizations for the payment profile
198 IF (p_org_arr.COUNT > 0) THEN
199 FOR i in p_org_arr.FIRST..p_org_arr.LAST LOOP
200 OPEN profile_org_appl_cur(p_ppp_id, p_org_arr(i).org_id, p_org_arr(i).org_type);
201 FETCH profile_org_appl_cur INTO l_count;
202 CLOSE profile_org_appl_cur;
203
204 IF l_count = 0 THEN
205 select name
206 into l_org_name
207 from HR_ALL_ORGANIZATION_UNITS_VL
208 where organization_id = p_org_arr(i).org_id;
209
210 FND_MESSAGE.set_name('IBY', 'IBY_AP_VLDT_PROF_ORG');
211 FND_MESSAGE.SET_TOKEN('PROFILE', l_profile_name);
212 FND_MESSAGE.SET_TOKEN('OBJECT', l_org_name);
213 FND_MSG_PUB.Add;
214
215 x_return_status := FND_API.G_RET_STS_ERROR;
216 END IF;
217 END LOOP;
218 END IF;
219
220 -- Validate currencies for the payemnt profile
221 IF (p_curr_arr.COUNT > 0) THEN
222 FOR i in p_curr_arr.FIRST..p_curr_arr.LAST LOOP
223 OPEN profile_appl_cur(p_ppp_id, 'CURRENCY_CODE', p_curr_arr(i));
224 FETCH profile_appl_cur INTO l_count;
225 CLOSE profile_appl_cur;
226
227 IF l_count = 0 THEN
228 FND_MESSAGE.set_name('IBY', 'IBY_AP_VLDT_PROF_CURR');
229 FND_MESSAGE.SET_TOKEN('PROFILE', l_profile_name);
230 FND_MESSAGE.SET_TOKEN('OBJECT', p_curr_arr(i));
231 FND_MSG_PUB.Add;
232
233 x_return_status := FND_API.G_RET_STS_ERROR;
234 END IF;
235 END LOOP;
236 END IF;
237
238 -- Validate payment method for the payemnt profile
239 IF p_payment_method_code is not null THEN
240 select count(ap.applicable_pmt_prof_id)
241 into l_count
242 from iby_applicable_pmt_profs ap,
243 iby_payment_profiles p
244 where ap.system_profile_code = p.system_profile_code
245 and p.payment_profile_id = p_ppp_id
246 and ap.applicable_type_code = 'PAYMENT_METHOD'
247 and (ap.applicable_value_to is null OR ap.applicable_value_to = p_payment_method_code);
248
249 IF l_count = 0 THEN
250 FND_MESSAGE.set_name('IBY', 'IBY_AP_VLDT_PROF_METHOD');
251 FND_MESSAGE.SET_TOKEN('PROFILE', l_profile_name);
252 FND_MESSAGE.SET_TOKEN('OBJECT', l_method_name);
253 FND_MSG_PUB.Add;
254
255 x_return_status := FND_API.G_RET_STS_ERROR;
256 END IF;
257 END IF;
258
259 END IF; -- if payment profile is not null
260
261 -- Validate Create Instruction Flag, etc.
262 IF p_crt_instr_flag = 'Y' THEN
263 IF p_ppp_id is null THEN
264 /*
265 FND_MESSAGE.set_name('IBY', 'IBY_BUILD_INV_PARAMS');
266 FND_MSG_PUB.Add;
267
268 x_return_status := FND_API.G_RET_STS_ERROR;*/
269 print_debuginfo(l_module_name,'Payment profile entry considered optional after bug 8781032');
270 ELSE
271 IF p_payment_document_id is null THEN
272 select nvl(DEFAULT_PAYMENT_DOCUMENT_ID, -1),
273 processing_type
274 into l_payment_document_id,
275 l_proc_type
276 from iby_payment_profiles
277 where payment_profile_id = p_ppp_id;
278
279 IF (l_payment_document_id = -1) and
280 (l_proc_type = 'PRINTED') THEN
281 FND_MESSAGE.set_name('IBY', 'IBY_APSUB_NO_DEFAULT_PMT_DOC');
282 FND_MSG_PUB.Add;
283
284 x_return_status := FND_API.G_RET_STS_ERROR;
285 END IF;
286 END IF;
287 END IF;
288 END IF;
289
290 IF p_payment_document_id is not null THEN
291 IF p_ppp_id is null OR p_int_bank_acc_arr.COUNT = 0 THEN
292 FND_MESSAGE.set_name('IBY', 'IBY_BUILD_MISS_PMT_DOC_REL_PAR');
293 FND_MSG_PUB.Add;
294
295 x_return_status := FND_API.G_RET_STS_ERROR;
296 END IF;
297 END IF;
298
299 -- End of API body.
300
301 -- Standard call to get message count and if count is 1, get message info.
302 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
303
304 print_debuginfo(l_module_name, 'RETURN Validate_Method_Profile');
305
306 EXCEPTION
307 WHEN FND_API.G_EXC_ERROR THEN
308 x_return_status := FND_API.G_RET_STS_ERROR;
309 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
310 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
311 print_debuginfo(l_module_name,'SQLerr is :' || substr(SQLERRM, 1, 150));
312
313 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
314 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
315 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
316 print_debuginfo(l_module_name,'Unexpected ERROR: Exception occured during call to API ');
317 print_debuginfo(l_module_name,'SQLerr is :' || substr(SQLERRM, 1, 150));
318
319 WHEN OTHERS THEN
320 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
321 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
322 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
323 END IF;
324 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
325 print_debuginfo(l_module_name,'Other ERROR: Exception occured during call to API ');
326 print_debuginfo(l_module_name,'SQLerr is :' || substr(SQLERRM, 1, 150));
327
328 END Validate_Method_and_Profile;
329
330 FUNCTION Payment_Instruction_Action (
331 p_instruction_status IN VARCHAR2
332 ) RETURN VARCHAR2 IS
333 l_action VARCHAR2(100);
334 BEGIN
335 if p_instruction_status = 'CREATION_ERROR' then
336 l_action := 'IBY_FD_INSTRUCTION_VALIDATE';
337 elsif p_instruction_status = 'FORMATTED_READY_TO_TRANSMIT' then
338 l_action := 'IBY_FD_INSTRUCTION_TRANSMIT';
339 elsif p_instruction_status = 'TRANSMISSION_FAILED' then
340 l_action := 'IBY_FD_INSTRUCTION_TRANS_ERR';
341 elsif p_instruction_status = 'FORMATTED_READY_FOR_PRINTING' then
342 l_action := 'IBY_FD_PAYMENT_PRINT';
343 elsif p_instruction_status = 'SUBMITTED_FOR_PRINTING' then
344 l_action := 'IBY_FD_INSTRUCTION_DETAIL';
345 elsif p_instruction_status = 'CREATED_READY_FOR_PRINTING' then
346 l_action := 'IBY_FD_PAYMENT_PRINT';
347 elsif p_instruction_status = 'CREATED_READY_FOR_FORMATTING' then
348 l_action := 'IBY_FD_PAYMENT_PRINT';
349 elsif p_instruction_status = 'FORMATTED' then
350 l_action := 'IBY_FD_PAYMENT_PRINT_RECORD';
351 elsif p_instruction_status = 'CREATED' then
352 l_action := 'IBY_FD_PAYMENT_PRINT';
353 elsif p_instruction_status = 'TRANSMITTED' then
354 l_action := 'TRANSMITTED_RETRY_COMPLETION';
355 else
356 l_action := 'Dummy';
357 end if;
358
359 return l_action;
360 EXCEPTION
361 when others then
362 return 'Dummy';
363 END Payment_Instruction_Action;
364
365 FUNCTION Pmt_Instr_Terminate_Enabled (
366 p_instruction_status IN VARCHAR2,
367 p_instruction_id IN NUMBER,
368 p_request_id IN NUMBER DEFAULT NULL
369 ) RETURN VARCHAR2 IS
370
371 l_action VARCHAR2(100);
372 l_request_status VARCHAR2(100);
373
374 BEGIN
375 IF (p_instruction_status IN ('CREATION_ERROR',
376 'FORMATTED_READY_TO_TRANSMIT',
377 'TRANSMISSION_FAILED',
378 'FORMATTED_READY_FOR_PRINTING',
379 'CREATED_READY_FOR_PRINTING',
380 'CREATED_READY_FOR_FORMATTING',
381 'FORMATTED',
382 'CREATED') AND p_request_id IS NOT NULL) THEN
383
384 l_request_status := iby_disburse_ui_api_pub_pkg.get_conc_request_status(p_request_id);
385
386 IF (l_request_status = 'SUCCESS') THEN
387 l_action := 'Terminate_Disabled';
388 ELSIF (l_request_status = 'ERROR') THEN
389 l_action := 'Terminate_Enabled';
390 ELSE
391 l_action := 'Terminate_Disabled';
392 END IF;
393
394 ELSE
395 l_action := 'Terminate_Disabled';
396 END IF;
397
398 RETURN l_action;
399 EXCEPTION
400 when others then
401 return 'Terminate_Disabled';
402
403 END Pmt_Instr_Terminate_Enabled;
404
405 FUNCTION Instr_Sec_Terminate_Enabled (
406 p_instruction_status IN VARCHAR2,
407 p_org_id IN NUMBER,
408 p_instruction_id IN NUMBER,
409 p_request_id IN NUMBER DEFAULT NULL
410 ) RETURN VARCHAR2 IS
411
412 l_action VARCHAR2(100);
413 l_access NUMBER;
414 l_request_status VARCHAR2(100);
415
416 BEGIN
417
418 if p_org_id is null then
419 declare
420 d_access NUMBER;
421 begin
422 select 0
423 into l_access
424 from dual
425 where exists
426 (select null
427 from iby_payments_all
428 where payment_instruction_id = p_instruction_id
429 and org_id <> -1
430 and MO_GLOBAL.CHECK_ACCESS(org_id) = 'N');
431
432 l_access := 0;
433
434 exception
435 when no_data_found then
436 l_access := 1;
437 when others then
438 raise;
439 end;
440
441 elsif MO_GLOBAL.CHECK_ACCESS(p_org_id) = 'Y' then
442 l_access := 1;
443 else
444 l_access := 0;
445 end if;
446
447 IF NOT (l_access = 1) OR Is_Pmt_Instr_Complete(p_instruction_id)='Y' THEN
448 -- If not MOAC access to all payments
449 l_action := 'Terminate_Disabled';
450
451 ELSE
452 IF (p_instruction_status IN ('CREATION_ERROR',
453 'FORMATTED_READY_TO_TRANSMIT',
454 'TRANSMISSION_FAILED',
455 'FORMATTED_READY_FOR_PRINTING',
456 'CREATED_READY_FOR_PRINTING',
457 'CREATED_READY_FOR_FORMATTING',
458 'FORMATTED',
459 'CREATED') AND p_request_id IS NOT NULL) THEN
460
461 l_request_status := iby_disburse_ui_api_pub_pkg.get_conc_request_status(p_request_id);
462
463 IF (l_request_status = 'SUCCESS') THEN
464 l_action := 'Terminate_Disabled';
465 ELSIF (l_request_status = 'ERROR') THEN
466 l_action := 'Terminate_Enabled';
467 ELSE
468 l_action := 'Terminate_Disabled';
469 END IF;
470
471 ELSE
472 l_action := 'Terminate_Disabled';
473 END IF;
474 END IF;
475
476 RETURN l_action;
477 EXCEPTION
478 when others then
479 return 'Terminate_Disabled';
480
481 END Instr_Sec_Terminate_Enabled;
482
483
484 FUNCTION Pmt_Instr_Action_Enabled (
485 p_instruction_status IN VARCHAR2,
486 p_org_id IN NUMBER,
487 p_instruction_id IN NUMBER,
488 p_request_id IN NUMBER DEFAULT NULL
489 ) RETURN VARCHAR2 IS
490
491 l_action VARCHAR2(100);
492 l_access NUMBER;
493 l_request_status VARCHAR2(100);
494
495 BEGIN
496 if p_org_id is null then
497 IF(g_instr_table.EXISTS(p_instruction_id) AND g_instr_table(p_instruction_id).access IS NOT NULL) THEN
498 l_access := g_instr_table(p_instruction_id).access;
499 ELSE
500 begin
501 select 0
502 into l_access
503 from dual
504 where exists
505 (select null
506 from iby_payments_all
507 where payment_instruction_id = p_instruction_id
508 and org_id <> -1
509 and MO_GLOBAL.CHECK_ACCESS(org_id) = 'N');
510
511 l_access := 0;
512
513 exception
514 when no_data_found then
515 l_access := 1;
516 when others then
517 raise;
518 end;
519 g_instr_table(p_instruction_id).access := l_access;
520 END IF;
521 elsif MO_GLOBAL.CHECK_ACCESS(p_org_id) = 'Y' then
522 l_access := 1;
523 else
524 l_access := 0;
525 end if;
526
527 IF NOT (l_access = 1) OR (p_instruction_status = 'CREATED' and Is_Pmt_Instr_Complete(p_instruction_id)='Y') THEN
528 -- If not MOAC access to all payments
529 l_action := 'TakeActionDisabled';
530
531 ELSE
532 IF (p_instruction_status IN ('CREATION_ERROR',
533 'FORMATTED_READY_TO_TRANSMIT',
534 'TRANSMISSION_FAILED',
535 'FORMATTED_READY_FOR_PRINTING',
536 'SUBMITTED_FOR_PRINTING',
537 'CREATED_READY_FOR_PRINTING',
538 'CREATED_READY_FOR_FORMATTING',
539 'FORMATTED',
540 'CREATED')) THEN
541
542 l_request_status := iby_disburse_ui_api_pub_pkg.get_conc_request_status(p_request_id);
543
544 IF (l_request_status = 'SUCCESS') THEN
545 l_action := 'TakeActionEnabled';
546 ELSIF (l_request_status = 'ERROR') THEN
547 l_action := 'WarningIndEvenActive';
548 ELSE
549 l_action := 'InProgressIndStatus';
550 END IF;
551 ELSE
552 IF ((p_instruction_status = 'TRANSMITTED') AND (Is_transmitted_Pmt_Inst_Compl(p_instruction_id) = 'N'))THEN
553 l_action := 'TakeActionEnabled';
554 ELSE
555 l_action := 'TakeActionDisabled';
556 END IF;
557 END IF;
558 END IF;
559
560 RETURN l_action;
561 EXCEPTION
562 when others then
563 return 'TakeActionDisabled';
564
565 END Pmt_Instr_Action_Enabled;
566
567 PROCEDURE retrieve_default_sra_format(
568 p_api_version IN NUMBER,
569 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
570 p_instr_id IN NUMBER,
571 x_default_sra_format_code OUT NOCOPY VARCHAR2,
572 x_default_sra_format_name OUT NOCOPY VARCHAR2,
573 x_return_status OUT NOCOPY VARCHAR2,
574 x_msg_count OUT NOCOPY NUMBER,
575 x_msg_data OUT NOCOPY VARCHAR2)
576 IS
577 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Method_and_Profile';
578 l_api_version CONSTANT NUMBER := 1.0;
579 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.retrieve_default_sra_format';
580
581 l_instr_sra_ok NUMBER;
582 BEGIN
583 -- Standard call to check for call compatibility.
584 IF NOT FND_API.Compatible_API_Call(l_api_version,
585 p_api_version,
586 l_api_name,
587 G_PKG_NAME) THEN
588 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
589 END IF;
590
591 -- Initialize message list if p_init_msg_list is set to TRUE.
592 IF FND_API.to_Boolean(p_init_msg_list) THEN
593 FND_MSG_PUB.initialize;
594 END IF;
595
596 -- Initialize API return status to success
597 x_return_status := FND_API.G_RET_STS_SUCCESS;
598
599 IF p_instr_id is null THEN
600 FND_MESSAGE.set_name('IBY', 'IBY_SRA_SUBMIT_INVALID_INSTR');
601 FND_MESSAGE.SET_TOKEN('INSTR_ID', p_instr_id);
602 FND_MSG_PUB.Add;
603 raise SRA_INVALID_INSTR;
604 END IF;
605
606 select count(*)
607 into l_instr_sra_ok
608 from IBY_PAY_INSTRUCTIONS_ALL
609 where generate_sep_remit_advice_flag = 'Y'
610 and (REMITTANCE_ADVICE_CREATED_FLAG = 'N' or
611 IBY_FD_POST_PICP_PROGS_PVT.get_allow_multiple_sra_flag(p_instr_id) = 'Y')
612 and payment_instruction_status not in ('CREATION_ERROR', 'RETRY_CREATION', 'TERMINATED')
613 and IBY_FD_POST_PICP_PROGS_PVT.val_instruction_accessible(p_instr_id) = 'Y';
614
615 IF l_instr_sra_ok = 0 THEN
616 FND_MESSAGE.set_name('IBY', 'IBY_SRA_SUBMIT_INVALID_INSTR');
617 FND_MESSAGE.SET_TOKEN('INSTR_ID', p_instr_id);
618 FND_MSG_PUB.Add;
619 raise SRA_INVALID_INSTR;
620 ELSE
621 select sra_setup.remittance_advice_format_code,
622 f.format_name
623 into x_default_sra_format_code,
624 x_default_sra_format_name
625 from iby_pay_instructions_all ins,
626 iby_payment_profiles pp,
627 iby_remit_advice_setup sra_setup,
628 iby_formats_vl f
629 where ins.payment_instruction_id = p_instr_id
630 and pp.payment_profile_id = ins.payment_profile_id
631 and pp.system_profile_code = sra_setup.system_profile_code
632 and sra_setup.remittance_advice_format_code = f.FORMAT_CODE (+);
633 END IF;
634
635 -- End of API body.
636
637 -- Standard call to get message count and if count is 1, get message info.
638 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
639
640 print_debuginfo(l_module_name, 'RETURN retrieve_default_sra_format');
641
642 EXCEPTION
643 WHEN FND_API.G_EXC_ERROR THEN
644 x_return_status := FND_API.G_RET_STS_ERROR;
645 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
646 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
647 print_debuginfo(l_module_name,'SQLerr is :' || substr(SQLERRM, 1, 150));
648
649 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
651 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
652 print_debuginfo(l_module_name,'Unexpected ERROR: Exception occured during call to API ');
653 print_debuginfo(l_module_name,'SQLerr is :' || substr(SQLERRM, 1, 150));
654
655 WHEN SRA_INVALID_INSTR THEN
656 x_return_status := FND_API.G_RET_STS_ERROR;
657 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
658 print_debuginfo(l_module_name,'ERROR: The payment instruction is not available for SRA. ');
659
660 WHEN OTHERS THEN
661 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
662 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
663 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
664 END IF;
665 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
666 print_debuginfo(l_module_name,'Other ERROR: Exception occured during call to API ');
667 print_debuginfo(l_module_name,'SQLerr is :' || substr(SQLERRM, 1, 150));
668
669 END retrieve_default_sra_format;
670
671
672 --
673 --
674 --
675 PROCEDURE print_debuginfo(p_module IN VARCHAR2,
676 p_debug_text IN VARCHAR2)
677 IS
678 BEGIN
679 --
680 -- Writing debug text to the pl/sql debug file.
681 --
682 FND_FILE.PUT_LINE(FND_FILE.LOG, p_module||p_debug_text);
683
684 IBY_DEBUG_PUB.add(substr(RPAD(p_module,55)||' : '|| p_debug_text, 0, 150),
685 FND_LOG.G_CURRENT_RUNTIME_LEVEL,
686 G_DEBUG_MODULE);
687
688 END print_debuginfo;
689
690
691 FUNCTION Is_Pmt_Instr_Complete (
692 p_instruction_id IN NUMBER
693 ) RETURN VARCHAR2 IS
694
695 l_complete_code VARCHAR2(30);
696
697 BEGIN
698
699 SELECT payments_complete_code
700 INTO l_complete_code
701 FROM iby_pay_instructions_all
702 WHERE payment_instruction_id = p_instruction_id;
703
704 IF (l_complete_code = 'YES') THEN
705 RETURN 'Y';
706 ELSE
707 RETURN 'N';
708 END IF;
709
710 END Is_Pmt_Instr_Complete;
711
712 FUNCTION Is_transmitted_Pmt_Inst_Compl (
713 p_instruction_id IN NUMBER
714 ) RETURN VARCHAR2 IS
715
716 l_complete_code VARCHAR2(30);
717 l_mark_complete_event VARCHAR2(30);
718 BEGIN
719
720 SELECT inst.payments_complete_code, pp.mark_complete_event
721 INTO l_complete_code, l_mark_complete_event
722 FROM iby_pay_instructions_all inst, iby_payment_profiles pp
723 WHERE inst.payment_profile_id = pp.payment_profile_id
724 AND inst.payment_instruction_id = p_instruction_id;
725
726 IF (l_complete_code = 'YES') THEN
727 RETURN 'Y';
728 ELSIF l_mark_complete_event <> 'TRANSMITTED' THEN
729 RETURN 'Y';
730 ELSE
731 RETURN 'N';
732 END IF;
733
734 END Is_transmitted_Pmt_Inst_Compl;
735
736
737 FUNCTION Pmt_Instr_Terminate_Allowed (
738 p_instruction_id IN NUMBER
739 ) RETURN VARCHAR2 IS
740
741 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Pmt_Instr_Terminate_Allowed';
742 l_allowed VARCHAR2(20) :='NO';
743 l_request_status VARCHAR2(100);
744 l_instruction_status varchar2(30);
745 l_pmt_complete_code varchar2(30);
746 l_process_type varchar2(30);
747 l_request_id number(15);
748
749 BEGIN
750
751 print_debuginfo(l_module_name,'Enter');
752
753 FND_MSG_PUB.initialize;
754
755 print_debuginfo(l_module_name,'Instruction Id::'||p_instruction_id);
756
757
758 select PAYMENT_INSTRUCTION_STATUS,
759 PROCESS_TYPE,
760 PAYMENTS_COMPLETE_CODE,
761 REQUEST_ID
762 into l_instruction_status,
763 l_process_type,
764 l_pmt_complete_code,
765 l_request_id
766 from iby_pay_instructions_all
767 where payment_instruction_id = p_instruction_id;
768
769 print_debuginfo(l_module_name,'Instruction Status::'||l_instruction_status);
770 print_debuginfo(l_module_name,'Process Type::'||l_process_type);
771 print_debuginfo(l_module_name,'Payment Complete Code::'||l_pmt_complete_code);
772 print_debuginfo(l_module_name,'Request Id::'||l_request_id);
773
774 IF(l_instruction_status = 'SUBMITTED_FOR_PRINTING')
775 THEN
776 FND_MESSAGE.set_name('IBY', 'IBY_INSTR_TERM_SUB_PRNT');
777 FND_MSG_PUB.Add;
778 ELSIF(l_process_type = 'IMMEDIATE')
779 THEN
780 FND_MESSAGE.set_name('IBY', 'IBY_INSTR_TERM_SING_PMT');
781 FND_MSG_PUB.Add;
782 ELSIF(l_pmt_complete_code = 'YES')
783 THEN
784 FND_MESSAGE.set_name('IBY', 'IBY_INSTR_TERM_COMP');
785 FND_MSG_PUB.Add;
786 ELSIF(l_instruction_status IN ('CREATION_ERROR',
787 'FORMATTED_READY_TO_TRANSMIT' ,
788 'TRANSMISSION_FAILED',
789 'FORMATTED_READY_FOR_PRINTING',
790 'FORMATTED_ELECTRONIC',
791 'FORMATTED',
792 'CREATED_READY_FOR_PRINTING',
793 'CREATED_READY_FOR_FORMATTING',
794 'CREATED'))THEN
795
796 IF(l_request_id is not null) THEN
797 l_request_status := iby_disburse_ui_api_pub_pkg.get_conc_request_status(l_request_id);
798
799 IF (l_request_status = 'SUCCESS') THEN
800 l_allowed := 'YES';
801 ELSIF (l_request_status = 'ERROR') THEN
802 l_allowed := 'YES';
803 ELSE
804 FND_MESSAGE.set_name('IBY', 'IBY_INSTR_TERM_FRMT_PRG');
805 FND_MSG_PUB.Add;
806 END IF;
807 ELSE
808 l_allowed := 'YES';
809 END IF;
810 END IF;
811 print_debuginfo(l_module_name,'Return Variable::'||l_allowed);
812 print_debuginfo(l_module_name,'Exit');
813
814 return l_allowed;
815
816 END Pmt_Instr_Terminate_Allowed;
817
818
819 FUNCTION Pmt_Instr_Sec_Term_Allowed (
820 p_instruction_status IN VARCHAR2,
821 p_process_type IN VARCHAR2,
822 p_instruction_id IN NUMBER,
823 p_org_id IN NUMBER,
824 p_pmt_complete_code IN VARCHAR2,
825 p_request_id IN NUMBER DEFAULT NULL,
826 p_msg_req IN VARCHAR2 DEFAULT 'Y'
827 ) RETURN VARCHAR2 IS
828
829 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Pmt_Instr_Sec_Term_Allowed';
830 l_access NUMBER;
831 l_allowed VARCHAR2(20) :='NO';
832 l_request_status VARCHAR2(100);
833
834 BEGIN
835
836 print_debuginfo(l_module_name,'Enter');
837 print_debuginfo(l_module_name,'Instruction Status::'||p_instruction_status);
838 print_debuginfo(l_module_name,'Process Type::'||p_process_type);
839 print_debuginfo(l_module_name,'Instruction Id::'||p_instruction_id);
840 print_debuginfo(l_module_name,'Org Id::'||p_org_id);
841 print_debuginfo(l_module_name,'Payment Complete Code::'||p_pmt_complete_code);
842 print_debuginfo(l_module_name,'Request Id::'||p_request_id);
843 FND_MSG_PUB.initialize;
844
845
846 if p_org_id is null then
847
848 begin
849 select 0
850 into l_access
851 from dual
852 where exists
853 (select null
854 from iby_payments_all
855 where payment_instruction_id = p_instruction_id
856 and org_id <> -1
857 and MO_GLOBAL.CHECK_ACCESS(org_id) = 'N');
858
859 l_access := 0;
860
861 exception
862 when no_data_found then
863 l_access := 1;
864 when others then
865 raise;
866 end;
867
868 elsif MO_GLOBAL.CHECK_ACCESS(p_org_id) = 'Y' then
869 l_access := 1;
870 else
871 l_access := 0;
872 end if;
873
874 IF NOT(l_access = 1) THEN
875 -- If not MOAC access to all payments
876 l_allowed := 'NO';
877 IF(p_msg_req = 'Y') THEN
878 FND_MESSAGE.set_name('IBY', 'IBY_INSTR_TERM_INV_ACCESS');
879 FND_MSG_PUB.Add;
880 END IF;
881
882 ELSE
883
884
885 IF(p_instruction_status = 'SUBMITTED_FOR_PRINTING')
886 THEN
887 l_allowed := 'NO';
888 IF(p_msg_req = 'Y') THEN
889 FND_MESSAGE.set_name('IBY', 'IBY_INSTR_TERM_SUB_PRNT');
890 FND_MSG_PUB.Add;
891 END IF;
892 ELSIF(p_process_type = 'IMMEDIATE')
893 THEN
894 l_allowed := 'NO';
895 IF(p_msg_req = 'Y') THEN
896 FND_MESSAGE.set_name('IBY', 'IBY_INSTR_TERM_SING_PMT');
897 FND_MSG_PUB.Add;
898 END IF;
899 ELSIF(p_pmt_complete_code = 'YES')
900 THEN
901 l_allowed := 'NO';
902 IF(p_msg_req = 'Y') THEN
903 FND_MESSAGE.set_name('IBY', 'IBY_INSTR_TERM_COMP');
904 FND_MSG_PUB.Add;
905 END IF;
906 ELSIF(p_instruction_status IN ('CREATION_ERROR',
907 'FORMATTED_READY_TO_TRANSMIT' ,
908 'TRANSMISSION_FAILED',
909 'FORMATTED_READY_FOR_PRINTING',
910 'FORMATTED_ELECTRONIC',
911 'FORMATTED',
912 'CREATED_READY_FOR_PRINTING',
913 'CREATED_READY_FOR_FORMATTING',
914 'CREATED'))THEN
915
916 IF(p_request_id is not null) THEN
917 l_request_status := iby_disburse_ui_api_pub_pkg.get_conc_request_status(p_request_id);
918
919 IF (l_request_status = 'SUCCESS') THEN
920 l_allowed := 'YES';
921 ELSIF (l_request_status = 'ERROR') THEN
922 l_allowed := 'YES';
923 ELSE
924 l_allowed := 'NO';
925 IF(p_msg_req = 'Y') THEN
926 FND_MESSAGE.set_name('IBY', 'IBY_INSTR_TERM_FRMT_PRG');
927 FND_MSG_PUB.Add;
928 END IF;
929
930 END IF;
931 ELSE
932 l_allowed := 'YES';
933 END IF;
934 END IF;
935 END IF;
936 print_debuginfo(l_module_name,'Return Variable::'||l_allowed);
937 print_debuginfo(l_module_name,'Exit');
938
939 return l_allowed;
940
941 END Pmt_Instr_Sec_Term_Allowed;
942
943
944 FUNCTION PPR_Sec_Term_Allowed (
945 p_pay_service_req_id IN NUMBER
946 ) RETURN VARCHAR2 IS
947
948 l_allowed VARCHAR2(20):= 'YES';
949 l_instruction_status VARCHAR2(30);
950 l_process_type VARCHAR2(30);
951 l_instruction_id NUMBER(15);
952 l_org_id NUMBER(15);
953 l_pmt_complete_code VARCHAR2(30);
954 l_request_id NUMBER(15);
955
956 CURSOR instr_for_ppr(ppr_id number) is
957 SELECT PAYMENT_INSTRUCTION_STATUS,
958 PROCESS_TYPE,
959 PAYMENT_INSTRUCTION_ID,
960 ORG_ID,
961 PAYMENTS_COMPLETE_CODE,
962 REQUEST_ID
963 FROM IBY_PAY_INSTRUCTIONS_ALL INSTR
964 WHERE EXISTS (SELECT 'PAYMENTS'
965 FROM IBY_PAYMENTS_ALL PMT
966 WHERE PMT.PAYMENT_SERVICE_REQUEST_ID= ppr_id
967 AND PMT.PAYMENT_INSTRUCTION_ID = InStr.PAYMENT_INSTRUCTION_ID);
968
969 BEGIN
970 FND_MSG_PUB.initialize;
971
972 OPEN instr_for_ppr(p_pay_service_req_id);
973 loop
974 FETCH instr_for_ppr INTO l_instruction_status,
975 l_process_type,
976 l_instruction_id,
977 l_org_id,
978 l_pmt_complete_code,
979 l_request_id ;
980 exit when instr_for_ppr%NOTFOUND;
981
982
983 l_allowed := Pmt_Instr_Sec_Term_Allowed (
984 l_instruction_status,
985 l_process_type,
986 l_instruction_id,
987 l_org_id,
988 l_pmt_complete_code,
989 l_request_id,
990 'N'
991 );
992 IF(l_allowed = 'NO') THEN
993 return l_allowed;
994 END IF;
995 end loop;
996 return l_allowed;
997 END PPR_Sec_Term_Allowed;
998
999
1000 END IBY_FD_USER_API_PUB;