[Home] [Help]
PACKAGE BODY: APPS.IBY_DISBURSEMENT_SETUP_PUB
Source
1 PACKAGE BODY IBY_DISBURSEMENT_SETUP_PUB AS
2 /*$Header: ibyfdstb.pls 120.31.12020000.5 2013/04/02 05:32:44 yiyu ship $*/
3
4 --
5 -- Declare Global variables
6 --
7
8 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
9 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
10 -- User Defined Exceptions
11 g_abort_program EXCEPTION;
12
13 --
14 -- Forward Declarations
15 --
16
17 PROCEDURE print_debuginfo(p_module IN VARCHAR2,
18 p_debug_text IN VARCHAR2)
19 IS
20 BEGIN
21 -- Writing debug text to the concurrent manager log file.
22 iby_build_utils_pkg.print_debuginfo(p_module, p_debug_text);
23 -- dbms_output.put_line(p_module || ': ' || p_debug_text);
24
25 END print_debuginfo;
26
27 Procedure insert_payee_row(ext_payee_id IN NUMBER,
28 ext_payee_rec IN External_Payee_Rec_Type,
29 x_return_status OUT NOCOPY VARCHAR2 )
30 is
31 l_module_name VARCHAR2(100) := G_PKG_NAME || 'insert_payee_row';
32
33 --bug 10374184
34
35 l_remit_advice_delivery_method IBY_EXTERNAL_PAYEES_ALL.remit_advice_delivery_method%TYPE;
36 l_remit_advice_email IBY_EXTERNAL_PAYEES_ALL.remit_advice_email%TYPE;
37 l_remit_advice_fax IBY_EXTERNAL_PAYEES_ALL.REMIT_ADVICE_FAX%TYPE;
38
39 CURSOR ar_refund_payee_csr(p_payee_party_id NUMBER,
40 p_party_site_id NUMBER,
41 p_payer_org_id NUMBER,
42 p_payer_org_type VARCHAR2)
43 IS
44 SELECT payer.DEBIT_ADVICE_DELIVERY_METHOD,
45 payer.DEBIT_ADVICE_EMAIL, payer.DEBIT_ADVICE_FAX
46 FROM iby_external_payers_all payer,hz_cust_accounts acct, hz_cust_acct_sites_all hzcustacct, HZ_CUST_SITE_USES_ALL siteuses
47 WHERE payer.PARTY_ID = p_payee_party_id
48 AND nvl(payer.ORG_ID,-1) = nvl(p_payer_org_id,-1)
49 AND nvl(payer.ORG_TYPE,-1) = nvl(p_payer_org_type,-1)
50 AND nvl(payer.acct_site_use_id,-1) = nvl(siteuses.site_use_id,-1)
51 AND acct.party_id = payer.PARTY_ID
52 AND acct.cust_account_id = payer.cust_account_id
53 AND acct.cust_account_id = hzcustacct.cust_account_id(+)
54 AND hzcustacct.cust_acct_site_id = siteuses.cust_acct_site_id(+)
55 AND hzcustacct.party_site_id(+) = nvl(p_party_site_id,-1)
56 AND siteuses.site_use_code(+) = 'BILL_TO' ;
57 begin
58
59 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
60 print_debuginfo(l_module_name, 'ENTER');
61 END IF;
62
63 IF (ext_payee_rec.Payment_Function = 'AR_CUSTOMER_REFUNDS') THEN
64
65 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
66 print_debuginfo(l_module_name, 'Payment_Function is ' || ext_payee_rec.Payment_Function);
67 END IF;
68
69 OPEN ar_refund_payee_csr(ext_payee_rec.Payee_Party_Id,
70 ext_payee_rec.Payee_Party_Site_Id,
71 ext_payee_rec.Payer_Org_Id,
72 ext_payee_rec.Payer_Org_Type);
73 FETCH ar_refund_payee_csr INTO l_remit_advice_delivery_method,
74 l_remit_advice_email, l_remit_advice_fax;
75 CLOSE ar_refund_payee_csr;
76
77 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
78 print_debuginfo(l_module_name, 'Remit_advice_delivery_method is ' || l_remit_advice_delivery_method);
79 print_debuginfo(l_module_name, 'Remit_advice_email is ' || l_remit_advice_email);
80 print_debuginfo(l_module_name, 'remit_advice_fax is ' || l_remit_advice_fax);
81 END IF;
82
83 ELSE
84 l_remit_advice_delivery_method := ext_payee_rec.REMIT_ADVICE_DELIVERY_METHOD;
85 l_remit_advice_email := ext_payee_rec.REMIT_ADVICE_EMAIL;
86 l_remit_advice_fax := ext_payee_rec.remit_advice_fax;
87
88 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
89 print_debuginfo(l_module_name, 'Remit_advice_delivery_method is ' || l_remit_advice_delivery_method);
90 print_debuginfo(l_module_name, 'Remit_advice_email is ' || l_remit_advice_email);
91 print_debuginfo(l_module_name, 'remit_advice_fax is ' || l_remit_advice_fax);
92 END IF;
93
94 END IF;
95
96 x_return_status := FND_API.G_RET_STS_SUCCESS;
97
98 insert into IBY_EXTERNAL_PAYEES_ALL (
99 EXT_PAYEE_ID,
100 PAYEE_PARTY_ID,
101 PAYMENT_FUNCTION,
102 EXCLUSIVE_PAYMENT_FLAG,
103 CREATED_BY,
104 CREATION_DATE,
105 LAST_UPDATED_BY,
106 LAST_UPDATE_DATE,
107 LAST_UPDATE_LOGIN,
108 OBJECT_VERSION_NUMBER,
109 PARTY_SITE_ID,
110 SUPPLIER_SITE_ID,
111 ORG_ID,
112 ORG_TYPE,
113 DEFAULT_PAYMENT_METHOD_CODE,
114 ECE_TP_LOCATION_CODE,
115 BANK_CHARGE_BEARER,
116 BANK_INSTRUCTION1_CODE,
117 BANK_INSTRUCTION2_CODE,
118 BANK_INSTRUCTION_DETAILS,
119 PAYMENT_REASON_CODE,
120 PAYMENT_REASON_COMMENTS,
121 INACTIVE_DATE,
122 PAYMENT_TEXT_MESSAGE1,
123 PAYMENT_TEXT_MESSAGE2,
124 PAYMENT_TEXT_MESSAGE3,
125 DELIVERY_CHANNEL_CODE,
126 PAYMENT_FORMAT_CODE,
127 SETTLEMENT_PRIORITY,
128 REMIT_ADVICE_DELIVERY_METHOD,
129 REMIT_ADVICE_EMAIL,
130 REMIT_ADVICE_FAX)
131 values (
132 ext_payee_id,
133 ext_payee_rec.Payee_Party_Id,
134 ext_payee_rec.Payment_Function,
135 ext_payee_rec.Exclusive_Pay_Flag,
136 fnd_global.user_id,
137 SYSDATE, -- bug 13881024
138 fnd_global.user_id,
139 SYSDATE,
140 fnd_global.user_id,
141 1.0,
142 ext_payee_rec.Payee_Party_Site_Id,
143 ext_payee_rec.Supplier_Site_Id,
144 ext_payee_rec.Payer_Org_Id,
145 ext_payee_rec.Payer_Org_Type,
146 nvl(ext_payee_rec.edi_payment_method,ext_payee_rec.Default_Pmt_method),
147 ext_payee_rec.ECE_TP_Loc_Code,
148 ext_payee_rec.Bank_Charge_Bearer,
149 nvl(ext_payee_rec.edi_payment_format,ext_payee_rec.Bank_Instr1_Code),
150 nvl(ext_payee_rec.edi_transaction_handling,ext_payee_rec.Bank_Instr2_Code),
151 ext_payee_rec.Bank_Instr_Detail,
152 ext_payee_rec.Pay_Reason_Code,
153 ext_payee_rec.Pay_Reason_Com,
154 ext_payee_rec.Inactive_Date,
155 nvl(ext_payee_rec.edi_remittance_instruction,ext_payee_rec.Pay_Message1),
156 ext_payee_rec.Pay_Message2,
157 ext_payee_rec.Pay_Message3,
158 nvl(ext_payee_rec.edi_remittance_method,ext_payee_rec.Delivery_Channel),
159 ext_payee_rec.Pmt_Format,
160 ext_payee_rec.Settlement_Priority,
161 l_remit_advice_delivery_method,
162 l_remit_advice_email,
163 l_remit_advice_fax
164 );
165
166 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
167 print_debuginfo(l_module_name, 'RETURN');
168
169 END IF;
170 exception
171 when others then
172 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
173 print_debuginfo(l_module_name, 'Exception while insertion into iby_external_payees_all. ');
174 END IF;
175 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
176
177 end insert_payee_row;
178
179 FUNCTION Exists_Instr(p_instr IN IBY_FNDCPT_SETUP_PUB.PmtInstrument_rec_type) RETURN BOOLEAN
180 IS
181 l_instr_count NUMBER := 0;
182
183 CURSOR c_creditcard(ci_instrid IN iby_creditcard.instrid%TYPE)
184 IS
185 SELECT COUNT(instrid)
186 FROM iby_creditcard
187 WHERE (instrid = ci_instrid);
188
189 CURSOR c_bankaccount(ci_instrid IN iby_ext_bank_accounts_v.ext_bank_account_id%TYPE)
190 IS
191 SELECT COUNT(ext_bank_account_id)
192 FROM iby_ext_bank_accounts_v
193 WHERE (ext_bank_account_id = ci_instrid);
194
195 BEGIN
196
197 IF (c_creditcard%ISOPEN) THEN
198 CLOSE c_creditcard;
199 END IF;
200 IF (c_bankaccount%ISOPEN) THEN
201 CLOSE c_bankaccount;
202 END IF;
203
204 IF (p_instr.Instrument_Type = IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_CREDITCARD)
205 THEN
206 OPEN c_creditcard(p_instr.Instrument_Id);
207 FETCH c_creditcard INTO l_instr_count;
208 CLOSE c_creditcard;
209 ELSIF (p_instr.Instrument_Type = IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_BANKACCT)
210 THEN
211 OPEN c_bankaccount(p_instr.Instrument_Id);
212 FETCH c_bankaccount INTO l_instr_count;
213 CLOSE c_bankaccount;
214 END IF;
215
216 IF (l_instr_count < 1) THEN
217 RETURN FALSE;
218 ELSE
219 RETURN TRUE;
220 END IF;
221
222 END Exists_Instr;
223
224 FUNCTION Validate_Payee (
225 p_payee IN PayeeContext_rec_type,
226 p_val_level IN VARCHAR2
227 ) RETURN VARCHAR2
228 IS
229 vendor_type VARCHAR2(50) ;
230 vendor_site VARCHAR2(100);
231 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Validate_Payee';
232
233 BEGIN
234
235 -- party id and payment function always mandatory
236 IF ( (p_payee.Party_Id IS NULL) OR
237 (NOT iby_utility_pvt.check_lookup_val(p_payee.Payment_Function,
238 IBY_FNDCPT_COMMON_PUB.G_LKUP_PMT_FUNCTION))
239 )
240 THEN
241 RETURN G_RC_INVALID_PAYEE;
242 END IF;
243
244 IF (p_val_level = FND_API.G_VALID_LEVEL_FULL) THEN
245 IF (NOT iby_utility_pvt.validate_party_id(p_payee.Party_Id)) THEN
246 RETURN G_RC_INVALID_PAYEE;
247 END IF;
248 END IF;
249
250 IF (p_payee.Supplier_Site_id IS NOT NULL) AND
251 (p_payee.Party_Site_id IS NOT NULL) AND
252 (p_payee.Org_Id IS NOT NULL) AND
253 (p_payee.Org_Type IS NOT NULL) THEN
254 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
255 print_debuginfo(l_module_name , 'Payee level is supplier site');
256
257 END IF;
258 RETURN G_PAYEE_LEVEL_SUPP_SITE;
259 ELSIF (p_payee.Party_Site_id IS NOT NULL) AND
260 (p_payee.Org_Id IS NOT NULL) AND
261 (p_payee.Org_Type IS NOT NULL) THEN
262 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
263 print_debuginfo(l_module_name , 'Payee level is site-org');
264 END IF;
265 RETURN G_PAYEE_LEVEL_SITE_ORG;
266 ELSIF (p_payee.Party_Site_id IS NOT NULL) THEN
267 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
268 print_debuginfo(l_module_name , 'Payee level is party site');
269 END IF;
270 RETURN G_PAYEE_LEVEL_SITE;
271
272 ELSIF (p_payee.Supplier_Site_id IS NULL) AND
273 (p_payee.Party_Site_id IS NULL) AND
274 (p_payee.Org_Id IS NULL) THEN
275 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
276 print_debuginfo(l_module_name , 'Payee level is party');
277
278 END IF;
279 RETURN G_PAYEE_LEVEL_PARTY;
280 ELSIF (p_payee.Supplier_Site_id IS NOT NULL) AND
281 (p_payee.Party_Site_id IS NULL) AND
282 (p_payee.Org_Id IS NOT NULL) THEN
283
284
285 SELECT nvl(vendor_type_lookup_code, 'NOT EMPLOYEE'),
286 nvl(vendor_site_code, 'NOT EMPLOYEE')
287 INTO vendor_type,
288 vendor_site
289 FROM ap_suppliers aps,
290 ap_supplier_sites_all apss
291 WHERE apss.vendor_site_id = p_payee.supplier_site_id
292 AND aps.party_id = p_payee.party_id
293 and aps.vendor_id = apss.vendor_id; /* bug 16521484 */
294
295 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
296 print_debuginfo(l_module_name, 'Validate_Payee' ||'supplier_site_id ='||p_payee.Supplier_Site_id||'party_site_id ='||p_payee.party_site_id||'org_id' ||p_payee.org_id);
297
298 END IF;
299 IF (vendor_type = 'EMPLOYEE') AND
300 (vendor_site ='HOME' or vendor_site='OFFICE') THEN
301
302 print_debuginfo(l_module_name , 'Payee level is EMPLOYEE SUPPLIER');
303 RETURN G_PAYEE_EMP_SITE;
304 ELSE
305 print_debuginfo(l_module_name ,'Invalid payee');
306 RETURN G_RC_INVALID_PAYEE;
307 END IF;
308 ELSIF (p_payee.Supplier_Site_id IS NULL) AND
309 (p_payee.Party_Site_id IS NULL) AND
310 (p_payee.Org_Id IS NOT NULL) AND
311 (p_payee.Org_type = 'LEGAL_ENTITY') THEN
312 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
313 print_debuginfo(l_module_name , 'LE level is party for bank account transfers');
314 END IF;
315 RETURN G_LE_LEVEL_PARTY;
316 ELSE
317 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
318 print_debuginfo(l_module_name ,'Invalid payee');
319 END IF;
320 RETURN G_RC_INVALID_PAYEE;
321
322 END IF;
323
324 END Validate_Payee;
325
326 PROCEDURE Get_Payee_Id (
327 p_payee_context IN PayeeContext_rec_type,
328 p_validation_level IN VARCHAR2,
329 x_payee_level OUT NOCOPY VARCHAR2,
330 x_payee_id OUT NOCOPY iby_external_payees_all.ext_payee_id%TYPE
331 )
332 IS
333
334 CURSOR c_payee
335 (ci_party_id IN p_payee_context.Party_Id%TYPE,
336 ci_party_site_id IN p_payee_context.Party_Site_id%TYPE,
337 ci_supplier_site_id IN p_payee_context.Supplier_Site_id%TYPE,
338 ci_org_type IN p_payee_context.Org_Type%TYPE,
339 ci_org_id IN p_payee_context.Org_Id%TYPE,
340 ci_pmt_function IN p_payee_context.Payment_Function%TYPE)
341 IS
342 SELECT ext_payee_id
343 FROM iby_external_payees_all payee
344 WHERE payee.PAYEE_PARTY_ID = ci_party_id
345 AND payee.PAYMENT_FUNCTION = ci_pmt_function
346 AND ((ci_party_site_id is NULL and payee.PARTY_SITE_ID is NULL) OR
347 (payee.PARTY_SITE_ID = ci_party_site_id))
348 AND ((ci_supplier_site_id is NULL and payee.SUPPLIER_SITE_ID is NULL) OR
349 (payee.SUPPLIER_SITE_ID = ci_supplier_site_id))
350 AND ((ci_org_id is NULL and payee.ORG_ID is NULL) OR
351 (payee.ORG_ID = ci_org_id AND payee.ORG_TYPE = ci_org_type));
352
353 BEGIN
354
355 IF (c_payee%ISOPEN) THEN
356 CLOSE c_payee;
357 END IF;
358
359 x_payee_level := Validate_Payee(p_payee_context,p_validation_level);
360
361 IF (x_payee_level = G_RC_INVALID_PAYEE) THEN
362
363 x_payee_id := NULL;
364 RETURN;
365 END IF;
366
367 OPEN c_payee(p_payee_context.Party_Id,
368 p_payee_context.Party_Site_id,
369 p_payee_context.Supplier_Site_id,
370 p_payee_context.Org_Type,
371 p_payee_context.Org_Id,
372 p_payee_context.Payment_Function );
373 FETCH c_payee INTO x_payee_id;
374 IF c_payee%NOTFOUND THEN x_payee_id := NULL; END IF;
375 CLOSE c_payee;
376 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
377 print_debuginfo('Get_Payee_id:', 'Payee_id from Get_Payee_id function-' ||x_payee_id );
378 END IF;
379 END Get_Payee_Id;
380
381 procedure raise_biz_event(bank_acc_id NUMBER,
382 party_id NUMBER,
383 assignment_id NUMBER)
384 IS
385 l_parameter_list wf_parameter_list_t := wf_parameter_list_t();
386 begin
387 wf_event.AddParameterToList(p_name=>'ExternalBankAccountID',
388 p_value=>bank_acc_id,
389 p_parameterlist=>l_parameter_list);
390 wf_event.AddParameterToList(p_name=>'PartyID',
391 p_value=>party_id,
392 p_parameterlist=>l_parameter_list);
393 wf_event.AddParameterToList(p_name=>'InstrumentAssignmentID',
394 p_value=>assignment_id,
395 p_parameterlist=>l_parameter_list);
396
397 wf_event.raise( p_event_name => 'oracle.apps.iby.bankaccount.assignment_inactivated',
398 p_event_key => 'IBY',
399 p_parameters => l_parameter_list);
400
401 l_parameter_list.DELETE;
402
403 end raise_biz_event;
404
405
406 -- Public API
407
408 -- Start of comments
409 -- API name : Create_External_Payee
410 -- Type : Public
411 -- Pre-reqs : None
412 -- Function : Create payees for records passed in through the payee PL/SQL table
413 -- Parameters :
414 -- IN : p_api_version IN NUMBER Required
415 -- p_init_msg_list IN VARCHAR2 Optional
416 -- p_ext_payee_tab IN External_Payee_Tab_Type Required
417 -- OUT : x_return_status OUT VARCHAR2 Required
418 -- x_msg_count OUT NUMBER Required
419 -- x_msg_data OUT VARCHAR2 Required
420 -- x_ext_payee_id_tab OUT Ext_Payee_ID_Tab_Type
421 -- x_ext_payee_status_tab OUT Ext_Payee_Create_Tab_Type Required
422 --
423 -- Version : Current version 1.0
424 -- Previous version None
425 -- Initial version 1.0
426 -- End of comments
427
428 PROCEDURE Create_External_Payee (
429 p_api_version IN NUMBER,
430 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
431 p_ext_payee_tab IN External_Payee_Tab_Type,
432 x_return_status OUT NOCOPY VARCHAR2,
433 x_msg_count OUT NOCOPY NUMBER,
434 x_msg_data OUT NOCOPY VARCHAR2,
435 x_ext_payee_id_tab OUT NOCOPY Ext_Payee_ID_Tab_Type,
436 x_ext_payee_status_tab OUT NOCOPY Ext_Payee_Create_Tab_Type
437 )
438 IS
439
440 l_api_name CONSTANT VARCHAR2(30) := 'Create_External_Payee';
441 l_api_version CONSTANT NUMBER := 1.0;
442 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Create_External_Payee';
443
444 l_ext_payee_tab External_Payee_Tab_Type;
445
446
447 counter NUMBER;
448 l_status VARCHAR2(1);
449 l_payee_cnt NUMBER;
450 l_payee_id NUMBER;
451 l_pm_count NUMBER;
452 l_ext_payee_id NUMBER;
453 l_message FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
454
455 l_ext_payee_id_rec Ext_Payee_ID_Rec_Type;
456 l_ext_payee_crt_rec Ext_Payee_Create_Rec_Type;
457 l_payee_crt_status VARCHAR2(30);
458
459 CURSOR external_payee_csr(p_payee_party_id NUMBER,
460 p_party_site_id NUMBER,
461 p_supplier_site_id NUMBER,
462 p_payer_org_id NUMBER,
463 p_payer_org_type VARCHAR2,
464 p_payment_function VARCHAR2)
465 IS
466 SELECT count(payee.EXT_PAYEE_ID), max(payee.EXT_PAYEE_ID)
467 FROM iby_external_payees_all payee
468 WHERE payee.PAYEE_PARTY_ID = p_payee_party_id
469 AND payee.PAYMENT_FUNCTION = p_payment_function
470 AND ((p_party_site_id is NULL and payee.PARTY_SITE_ID is NULL) OR
471 (payee.PARTY_SITE_ID = p_party_site_id))
472 AND ((p_supplier_site_id is NULL and payee.SUPPLIER_SITE_ID is NULL) OR
473 (payee.SUPPLIER_SITE_ID = p_supplier_site_id))
474 AND ((p_payer_org_id is NULL and payee.ORG_ID is NULL) OR
475 (payee.ORG_ID = p_payer_org_id AND payee.ORG_TYPE = p_payer_org_type));
476
477
478 BEGIN
479
480 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
481 print_debuginfo(l_module_name, 'ENTER');
482
483 END IF;
484 -- Standard call to check for call compatibility.
485 IF NOT FND_API.Compatible_API_Call(l_api_version,
486 p_api_version,
487 l_api_name,
488 G_PKG_NAME) THEN
489 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
490 END IF;
491
492 -- Initialize message list if p_init_msg_list is set to TRUE.
493 IF FND_API.to_Boolean(p_init_msg_list) THEN
494 FND_MSG_PUB.initialize;
495 END IF;
496
497 -- Initialize API return status to success
498 x_return_status := FND_API.G_RET_STS_SUCCESS;
499
500 IF p_ext_payee_tab.COUNT > 0 THEN
501 counter := p_ext_payee_tab.FIRST;
502
503 while (counter <= p_ext_payee_tab.LAST)
504 loop
505 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
506 print_debuginfo(l_module_name, 'Loop thru external payee ' || counter);
507
508 END IF;
509 IF p_ext_payee_tab(counter).Payee_Party_Id IS NULL THEN
510 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
511 print_debuginfo(l_module_name,'Payee party Id is null.');
512 END IF;
513 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
514 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_PAYEE_PARTY_ID_FIELD'));
515 FND_MSG_PUB.Add;
516 l_message := fnd_message.get;
517
518 l_ext_payee_id_rec.Ext_Payee_ID := -1;
519 l_ext_payee_crt_rec.Payee_Creation_Status := 'E';
520 l_ext_payee_crt_rec.Payee_Creation_Msg := l_message;
521
522 x_return_status := FND_API.G_RET_STS_ERROR;
523 -- RAISE FND_API.G_EXC_ERROR;
524 ELSIF (p_ext_payee_tab(counter).Payment_Function IS NULL) THEN
525 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
526 print_debuginfo(l_module_name,'Payment function is null.');
527 END IF;
528 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
529 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_FD_PPP_GRP_PMT_T_PF'));
530 FND_MSG_PUB.Add;
531 l_message := fnd_message.get;
532
533 l_ext_payee_id_rec.Ext_Payee_ID := -1;
534 l_ext_payee_crt_rec.Payee_Creation_Status := 'E';
535 l_ext_payee_crt_rec.Payee_Creation_Msg := l_message;
536
537 x_return_status := FND_API.G_RET_STS_ERROR;
538 -- RAISE FND_API.G_EXC_ERROR;
539 -- orgid is required if supplier site id passed
540 ELSIF ((p_ext_payee_tab(counter).Payer_ORG_ID IS NULL) and
541 (p_ext_payee_tab(counter).Supplier_Site_Id IS NOT NULL)) THEN
542 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
543 print_debuginfo(l_module_name,'Payer Org Id is null.');
544 END IF;
545 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
546 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_PAYER_ORG_ID_FIELD'));
547 FND_MSG_PUB.Add;
548 l_message := fnd_message.get;
549
550 l_ext_payee_id_rec.Ext_Payee_ID := -1;
551 l_ext_payee_crt_rec.Payee_Creation_Status := 'E';
552 l_ext_payee_crt_rec.Payee_Creation_Msg := l_message;
553 x_return_status := FND_API.G_RET_STS_ERROR;
554
555 ELSIF ((p_ext_payee_tab(counter).Payer_ORG_ID IS NOT NULL) and
556 (p_ext_payee_tab(counter).Payer_Org_Type IS NULL)) THEN
557 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
558 print_debuginfo(l_module_name,'Payer Org Id is null.');
559 END IF;
560 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
561 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_PAYER_ORG_TYPE_FIELD'));
562 FND_MSG_PUB.Add;
563 l_message := fnd_message.get;
564
565 l_ext_payee_id_rec.Ext_Payee_ID := -1;
566 l_ext_payee_crt_rec.Payee_Creation_Status := 'E';
567 l_ext_payee_crt_rec.Payee_Creation_Msg := l_message;
568 x_return_status := FND_API.G_RET_STS_ERROR;
569
570 ELSIF ((p_ext_payee_tab(counter).Payer_Org_Type IS NOT NULL) and
571 (p_ext_payee_tab(counter).Payer_ORG_ID IS NULL)) THEN
572 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
573 print_debuginfo(l_module_name,'Payer Org Id is null but Org_type is not null.');
574 END IF;
575 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
576 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_PAYER_ORG_ID_FIELD'));
577 FND_MSG_PUB.Add;
578 l_message := fnd_message.get;
579
580 l_ext_payee_id_rec.Ext_Payee_ID := -1;
581 l_ext_payee_crt_rec.Payee_Creation_Status := 'E';
582 l_ext_payee_crt_rec.Payee_Creation_Msg := l_message;
583 x_return_status := FND_API.G_RET_STS_ERROR;
584 -- Payment_Function
585 ELSIF (((p_ext_payee_tab(counter).Payer_Org_Type IS NOT NULL) or
586 (p_ext_payee_tab(counter).Payer_ORG_ID IS NOT NULL)) and
587 (p_ext_payee_tab(counter).Supplier_Site_Id IS NULL and
588 p_ext_payee_tab(counter).Payee_Party_Site_Id IS NULL) and
589 ( p_ext_payee_tab(counter).Payment_Function <> 'CASH_PAYMENT')
590 ) THEN
591 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
592 print_debuginfo(l_module_name,'Org Id or Org Type is not null but Party_site_id and supplier_site_id are null.');
593 END IF;
594 FND_MESSAGE.set_name('IBY', 'INVALID_ORG_IN_PAYEE_CONTEXT');
595 FND_MSG_PUB.Add;
596 l_message := fnd_message.get;
597
598 l_ext_payee_id_rec.Ext_Payee_ID := -1;
599 l_ext_payee_crt_rec.Payee_Creation_Status := 'E';
600 l_ext_payee_crt_rec.Payee_Creation_Msg := l_message;
601 x_return_status := FND_API.G_RET_STS_ERROR;
602
603 /* Bug: 9139631
604 Description: This will no longer be considered as an error.
605 If the value is passed as null , then we derieve the value from
606 IBY_INTERNAL_PAYEES, and if nothing is present we use null as default.
607 ELSIF p_ext_payee_tab(counter).Exclusive_Pay_Flag IS NULL THEN
608 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
609 print_debuginfo(l_module_name,'Exclusive payment flag is null.');
610 END IF;
611 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
612 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_EXCL_PMT_FLAG_FIELD'));
613 FND_MSG_PUB.Add;
614 l_message := fnd_message.get;
615
616 l_ext_payee_id_rec.Ext_Payee_ID := -1;
617 l_ext_payee_crt_rec.Payee_Creation_Status := 'E';
618 l_ext_payee_crt_rec.Payee_Creation_Msg := l_message;
619
620 x_return_status := FND_API.G_RET_STS_ERROR;
621 */ -- RAISE FND_API.G_EXC_ERROR;
622 ELSE
623 l_ext_payee_tab :=p_ext_payee_tab;
624
625 -- Bug 9139631.
626 IF p_ext_payee_tab(counter).Exclusive_Pay_Flag IS NULL THEN
627
628 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
629 print_debuginfo(l_module_name,'Exclusive payment flag is null. Fetching value set at enterprise level');
630 END IF;
631
632 BEGIN
633
634 SELECT nvl(exclusive_payment_flag,'N')
635 INTO l_ext_payee_tab(counter).Exclusive_Pay_Flag
636 FROM iby_internal_payers_all
637 WHERE ORG_ID IS NULL;
638
639 EXCEPTION
640 WHEN NO_DATA_FOUND THEN
641 l_ext_payee_tab(counter).Exclusive_Pay_Flag := 'N';
642 WHEN OTHERS THEN
643 l_ext_payee_tab(counter).Exclusive_Pay_Flag := 'N';
644 END;
645
646 END IF;
647
648
649 OPEN external_payee_csr(p_ext_payee_tab(counter).Payee_Party_Id,
650 p_ext_payee_tab(counter).Payee_Party_Site_Id,
651 p_ext_payee_tab(counter).Supplier_Site_Id,
652 p_ext_payee_tab(counter).Payer_Org_Id,
653 p_ext_payee_tab(counter).Payer_Org_Type,
654 p_ext_payee_tab(counter).Payment_Function);
655 FETCH external_payee_csr INTO l_payee_cnt, l_payee_id;
656 CLOSE external_payee_csr;
657
658 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
659 print_debuginfo(l_module_name, 'Payee count is ' || l_payee_cnt);
660 print_debuginfo(l_module_name, 'Payee Id is ' || l_payee_id);
661
662 --bug 10374184 for REFUND process
663 print_debuginfo(l_module_name, 'Payment_Function is ' || p_ext_payee_tab(counter).Payment_Function);
664
665 END IF;
666
667 IF l_payee_cnt = 0 THEN
668 select IBY_EXTERNAL_PAYEES_ALL_S.nextval into l_ext_payee_id from dual;
669 insert_payee_row(l_ext_payee_id,
670 l_ext_payee_tab(counter),
671 l_payee_crt_status);
672
673 IF (l_payee_crt_status = FND_API.G_RET_STS_SUCCESS) THEN
674 l_ext_payee_id_rec.Ext_Payee_ID := l_ext_payee_id;
675 -- create the default payment method
676 IF(p_ext_payee_tab(counter).Default_Pmt_method is not NULL) THEN
677 select count(1)
678 into l_pm_count
679 from iby_payment_methods_b
680 where payment_method_code=p_ext_payee_tab(counter).Default_Pmt_method;
681
682 IF (l_pm_count>0) then
683 -- insert into the external payment method table
684 INSERT INTO IBY_EXT_PARTY_PMT_MTHDS
685 (EXT_PARTY_PMT_MTHD_ID,
686 PAYMENT_METHOD_CODE,
687 PAYMENT_FLOW,
688 EXT_PMT_PARTY_ID,
689 PAYMENT_FUNCTION,
690 PRIMARY_FLAG,
691 CREATED_BY,
692 CREATION_DATE,
693 LAST_UPDATED_BY,
694 LAST_UPDATE_DATE,
695 LAST_UPDATE_LOGIN,
696 OBJECT_VERSION_NUMBER
697 )
698 VALUES
699 (
700 IBY_EXT_PARTY_PMT_MTHDS_S.nextval,
701 p_ext_payee_tab(counter).Default_Pmt_method,
702 'DISBURSEMENTS',
703 l_ext_payee_id,
704 p_ext_payee_tab(counter).Payment_function,
705 'Y',
706 fnd_global.user_id,
707 SYSDATE, -- bug 13881024
708 fnd_global.user_id,
709 SYSDATE,
710 fnd_global.user_id,
711 1.0
712 );
713 end if;
714 end if;
715
716 l_ext_payee_crt_rec.Payee_Creation_Status := 'S';
717 ELSE
718 l_ext_payee_id_rec.Ext_Payee_ID := -1;
719 l_ext_payee_crt_rec.Payee_Creation_Status := 'E';
720
721 l_message := 'Creating an external payee failed.';
722 l_ext_payee_crt_rec.Payee_Creation_Msg := l_message;
723 END IF;
724 ELSIF l_payee_cnt > 0 THEN
725 l_ext_payee_id_rec.Ext_Payee_ID := -1;
726 l_ext_payee_crt_rec.Payee_Creation_Status := 'W';
727
728 FND_MESSAGE.set_name('IBY', 'IBY_DUPLICATE_EXT_PAYEE');
729 l_message := fnd_message.get;
730 l_ext_payee_crt_rec.Payee_Creation_Msg := l_message;
731
732 -- x_return_status := FND_API.G_RET_STS_SUCCESS;
733 END IF;
734 END IF;
735
736 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
737 print_debuginfo(l_module_name, 'External payee Id is ' || l_ext_payee_id_rec.Ext_Payee_ID);
738 print_debuginfo(l_module_name, 'Creation status is ' || l_ext_payee_crt_rec.Payee_Creation_Status);
739 print_debuginfo(l_module_name, '------------------------------');
740
741 END IF;
742 x_ext_payee_id_tab(counter) := l_ext_payee_id_rec;
743 x_ext_payee_status_tab(counter) := l_ext_payee_crt_rec;
744
745 counter := counter + 1;
746
747 end loop;
748 END IF;
749 -- End of API body.
750 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
751 print_debuginfo(l_module_name, 'End of external payee loop.');
752
753 END IF;
754
755 /* Bug Number: 8752267
756 */
757 -- Standard call to get message count and if count is 1, get message info.
758 FND_MSG_PUB.Count_And_Get(p_encoded => fnd_api.g_false, p_count => x_msg_count, p_data => x_msg_data);
759
760
761 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
762 print_debuginfo(l_module_name, 'RETURN');
763
764 END IF;
765 EXCEPTION
766 WHEN FND_API.G_EXC_ERROR THEN
767 x_return_status := FND_API.G_RET_STS_ERROR;
768
769 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
770 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
771 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
772 print_debuginfo(l_module_name,'SQLerr is :'
773 || substr(SQLERRM, 1, 150));
774 END IF;
775 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
776 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
777
778 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
779 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
780 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
781 print_debuginfo(l_module_name,'SQLerr is :'
782 || substr(SQLERRM, 1, 150));
783 END IF;
784 WHEN OTHERS THEN
785 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
786
787 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
788 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
789 END IF;
790 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
791 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
792 print_debuginfo(l_module_name,'SQLerr is :'
793 || substr(SQLERRM, 1, 150));
794 END IF;
795 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
796
797 END Create_External_Payee;
798
799 -- Start of comments
800 -- API name : Set_Payee_Instr_Assignment
801 -- Type : Public
802 -- Pre-reqs : None
803 -- Function : Create payee bank account assignment
804 -- Parameters :
805 -- IN : p_api_version IN NUMBER Required
806 -- p_init_msg_list IN VARCHAR2 Optional
807 -- p_ext_payee_tab IN External_Payee_Tab_Type Required
808 -- OUT : x_return_status OUT VARCHAR2 Required
809 -- x_msg_count OUT NUMBER Required
810 -- x_msg_data OUT VARCHAR2 Required
811 -- x_ext_payee_id_tab OUT Ext_Payee_ID_Tab_Type
812 -- x_ext_payee_status_tab OUT Ext_Payee_Create_Tab_Type Required
813 --
814 -- Version : Current version 1.0
815 -- Previous version None
816 -- Initial version 1.0
817 -- End of comments
818
819 PROCEDURE Set_Payee_Instr_Assignment (
820 p_api_version IN NUMBER,
821 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
822 p_commit IN VARCHAR2 := FND_API.G_TRUE,
823 x_return_status OUT NOCOPY VARCHAR2,
824 x_msg_count OUT NOCOPY NUMBER,
825 x_msg_data OUT NOCOPY VARCHAR2,
826 p_payee IN PayeeContext_rec_type,
827 p_assignment_attribs IN IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type,
828 x_assign_id OUT NOCOPY NUMBER,
829 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
830 )
831 IS
832 l_api_version CONSTANT NUMBER := 1.0;
833 l_module CONSTANT VARCHAR2(30) := 'Set_Payee_Instr_Assignment';
834 l_prev_msg_count NUMBER;
835
836 l_payee_level VARCHAR2(30);
837 l_payee_id iby_external_payees_all.ext_payee_id%TYPE;
838
839 l_result IBY_FNDCPT_COMMON_PUB.Result_rec_type;
840
841 l_assign_id NUMBER;
842 l_instr_id NUMBER;
843 l_priority NUMBER;
844
845 l_ext_payee_rec External_Payee_Rec_Type;
846 l_payee_crt_status VARCHAR2(30);
847
848 l_bnkacct_owner_cnt NUMBER;
849
850 l_giv_op NUMBER; --Bug 14488642 Variable for given order of preference
851 l_cur_op NUMBER; --Variable for current order of preference
852
853 CURSOR c_instr_assignment
854 (ci_assign_id IN iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE,
855 ci_payee_id IN iby_pmt_instr_uses_all.ext_pmt_party_id%TYPE,
856 ci_instr_type IN iby_pmt_instr_uses_all.instrument_type%TYPE,
857 ci_instr_id IN iby_pmt_instr_uses_all.instrument_id%TYPE
858 )
859 IS
860 SELECT instrument_payment_use_id
861 FROM iby_pmt_instr_uses_all
862 WHERE (payment_flow = G_PMT_FLOW_DISBURSE)
863 AND ( (instrument_payment_use_id = NVL(ci_assign_id,-1))
864 OR (ext_pmt_party_id = ci_payee_id
865 AND instrument_type = ci_instr_type
866 AND instrument_id = ci_instr_id )
867 );
868
869 CURSOR c_bnkacct_owner
870 (ci_party_id IN iby_pmt_instr_uses_all.ext_pmt_party_id%TYPE,
871 ci_instr_id IN iby_pmt_instr_uses_all.instrument_id%TYPE
872 )
873 IS
874 SELECT count(*)
875 FROM IBY_ACCOUNT_OWNERS
876 WHERE EXT_BANK_ACCOUNT_ID = ci_instr_id
877 AND ACCOUNT_OWNER_PARTY_ID = ci_party_id;
878
879 BEGIN
880
881 IF (c_instr_assignment%ISOPEN) THEN
882 CLOSE c_instr_assignment;
883 END IF;
884
885 IF NOT FND_API.Compatible_API_Call (l_api_version,
886 p_api_version,
887 l_module,
888 G_PKG_NAME)
889 THEN
890 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
891 debug_level => FND_LOG.LEVEL_ERROR,
892 module => G_DEBUG_MODULE || l_module);
893 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
894 FND_MSG_PUB.Add;
895 RAISE FND_API.G_EXC_ERROR;
896 END IF;
897
898 IF FND_API.to_Boolean( p_init_msg_list ) THEN
899 FND_MSG_PUB.initialize;
900 END IF;
901 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
902
903 -- Bug# 8470581
904 -- Do not allow an assignment if the payee party_id is not a joint
905 -- account owner
906 IF ((p_assignment_attribs.Assignment_Id IS NULL) AND
907 (p_assignment_attribs.Instrument.Instrument_Type = 'BANKACCOUNT')) THEN
908 IF(c_bnkacct_owner%ISOPEN) THEN CLOSE c_bnkacct_owner; END IF;
909 OPEN c_bnkacct_owner(p_payee.Party_Id, p_assignment_attribs.Instrument.Instrument_Id);
910 FETCH c_bnkacct_owner INTO l_bnkacct_owner_cnt;
911
912 IF (l_bnkacct_owner_cnt <= 0) THEN
913 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
914 print_debuginfo(l_module, 'Payee party is not a joint account owner. Aborting..');
915 END IF;
916 x_response.Result_Code := G_RC_INVALID_PAYEE;
917 RETURN;
918 END IF;
919 END IF;
920
921 Get_Payee_Id(p_payee, FND_API.G_VALID_LEVEL_FULL,l_payee_level, l_payee_id);
922
923 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
924 print_debuginfo(l_module, 'After Get_Payee_Id');
925 print_debuginfo(l_module, 'Payee level is ' || l_payee_level);
926
927
928 END IF;
929 IF (l_payee_level = G_RC_INVALID_PAYEE) THEN
930 x_response.Result_Code := G_RC_INVALID_PAYEE;
931 ELSIF ( (p_assignment_attribs.Assignment_Id IS NULL) AND
932 (NOT Exists_Instr(p_assignment_attribs.Instrument)) ) THEN
933 x_response.Result_Code := G_RC_INVALID_INSTRUMENT;
934 ELSIF ((p_assignment_attribs.End_Date IS NOT NULL) AND
935 (nvl(p_assignment_attribs.Start_Date, sysdate - 1) > p_assignment_attribs.End_Date) ) THEN
936 x_response.Result_Code := G_RC_INVALID_DATE_RANGE;
937 ELSE
938 SAVEPOINT Set_Payee_Instr_Assignment;
939
940 -- create the payee entity if it does not exist
941 IF (l_payee_id IS NULL) THEN
942 -- Create a default external payee
943 print_debuginfo(l_module,'Inside if l_payee_id is null, trying to insert in external_payees_all ');
944 select IBY_EXTERNAL_PAYEES_ALL_S.nextval into l_payee_id from dual;
945
946 l_ext_payee_rec.Payee_Party_Id := p_payee.Party_Id;
947 l_ext_payee_rec.Payee_Party_Site_Id := p_payee.Party_Site_id;
948 l_ext_payee_rec.Supplier_Site_Id := p_payee.Supplier_Site_id;
949 l_ext_payee_rec.Payer_Org_Type := p_payee.Org_Type;
950 l_ext_payee_rec.Payer_Org_Id := p_payee.Org_Id;
951 l_ext_payee_rec.Payment_Function := p_payee.Payment_Function;
952 l_ext_payee_rec.Exclusive_Pay_Flag := 'N';
953
954 insert_payee_row(l_payee_id, l_ext_payee_rec, l_payee_crt_status);
955
956 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
957 print_debuginfo(l_module, 'After inserting a default payee row.');
958
959
960 END IF;
961 IF (l_payee_crt_status <> FND_API.G_RET_STS_SUCCESS) THEN
962 x_response.Result_Code := G_RC_INVALID_PAYEE;
963 RETURN;
964 END IF;
965 END IF;
966
967 -- for the combined query cursor, only 1 query condition should be used,
968 -- either the assingment id or the (payer id, instr type, instr id)
969 -- combination
970 --
971 IF (p_assignment_attribs.Assignment_Id IS NOT NULL) THEN
972 l_assign_id := p_assignment_attribs.Assignment_Id;
973 ELSE
974 l_instr_id := p_assignment_attribs.Instrument.Instrument_Id;
975 END IF;
976
977 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
978 print_debuginfo(l_module, 'Assignment id is ' || l_assign_id);
979 print_debuginfo(l_module, 'Instrument id is ' || l_instr_id);
980
981 END IF;
982 OPEN c_instr_assignment(l_assign_id,
983 l_payee_id,
984 p_assignment_attribs.Instrument.Instrument_Type,
985 l_instr_id);
986 FETCH c_instr_assignment INTO x_assign_id;
987 IF (c_instr_assignment%NOTFOUND) THEN x_assign_id := NULL; END IF;
988 CLOSE c_instr_assignment;
989
990 l_priority := GREATEST(NVL(p_assignment_attribs.Priority,1),1);
991
992 l_giv_op := p_assignment_attribs.Priority; --Bug 14488642
993 IF(x_assign_id IS NOT NULL) THEN
994 SELECT order_of_preference
995 INTO l_cur_op
996 FROM iby_pmt_instr_uses_all
997 WHERE instrument_payment_use_id = x_assign_id;
998 END IF;
999
1000 -- only need to shift instrument priorities if this is a new instrument
1001 -- or if this is an update with a non-NULL priority
1002 IF (x_assign_id IS NULL)
1003 THEN
1004 --Per bug 6851476
1005 --Eleminating the expensive CONNECT BY clause
1006 UPDATE iby_pmt_instr_uses_all
1007 SET order_of_preference = order_of_preference + 1,
1008 last_updated_by = fnd_global.user_id,
1009 last_update_date = SYSDATE,
1010 last_update_login = fnd_global.login_id,
1011 object_version_number = object_version_number + 1
1012 WHERE ext_pmt_party_id = l_payee_id
1013 AND payment_flow = G_PMT_FLOW_DISBURSE
1014 AND order_of_preference >= l_priority;
1015
1016 ELSE
1017 --Shifting priorities via API
1018 IF(p_assignment_attribs.Priority IS NOT NULL) THEN --Bug 13827657
1019 IF(l_cur_op > l_giv_op) THEN
1020 UPDATE iby_pmt_instr_uses_all
1021 SET order_of_preference = order_of_preference + 1,
1022 last_updated_by = fnd_global.user_id,
1023 last_update_date = SYSDATE,
1024 last_update_login = fnd_global.login_id
1025 WHERE ext_pmt_party_id = l_payee_id
1026 AND payment_flow = G_PMT_FLOW_DISBURSE
1027 AND order_of_preference < l_cur_op
1028 AND order_of_preference >= l_giv_op ;
1029 ELSIF(l_cur_op < l_giv_op) THEN
1030 UPDATE iby_pmt_instr_uses_all
1031 SET order_of_preference = order_of_preference - 1,
1032 last_updated_by = fnd_global.user_id,
1033 last_update_date = SYSDATE,
1034 last_update_login = fnd_global.login_id
1035 WHERE ext_pmt_party_id = l_payee_id
1036 AND payment_flow = G_PMT_FLOW_DISBURSE
1037 AND order_of_preference > l_cur_op
1038 AND order_of_preference <= l_giv_op ;
1039 END IF;
1040 END IF;
1041 END IF;
1042
1043 IF (x_assign_id IS NULL) THEN
1044 SELECT iby_pmt_instr_uses_all_s.nextval
1045 INTO x_assign_id
1046 FROM DUAL;
1047
1048 INSERT INTO iby_pmt_instr_uses_all
1049 (instrument_payment_use_id,
1050 ext_pmt_party_id,
1051 instrument_type,
1052 instrument_id,
1053 payment_function,
1054 payment_flow,
1055 order_of_preference,
1056 debit_auth_flag,
1057 debit_auth_method,
1058 debit_auth_reference,
1059 debit_auth_begin,
1060 debit_auth_end,
1061 start_date,
1062 end_date,
1063 created_by,
1064 creation_date,
1065 last_updated_by,
1066 last_update_date,
1067 last_update_login,
1068 object_version_number)
1069 VALUES
1070 (x_assign_id,
1071 l_payee_id,
1072 p_assignment_attribs.Instrument.Instrument_Type,
1073 p_assignment_attribs.Instrument.Instrument_Id,
1074 p_payee.Payment_Function,
1075 G_PMT_FLOW_DISBURSE,
1076 l_priority,
1077 null, null, null, null, null,
1078 NVL(p_assignment_attribs.Start_Date,SYSDATE),
1079 p_assignment_attribs.End_Date,
1080 fnd_global.user_id,
1081 SYSDATE, -- bug 13881024
1082 fnd_global.user_id,
1083 SYSDATE,
1084 fnd_global.login_id,
1085 1);
1086 ELSE
1087 UPDATE iby_pmt_instr_uses_all
1088 SET
1089 order_of_preference =
1090 NVL(p_assignment_attribs.Priority,order_of_preference),
1091 start_date = NVL(p_assignment_attribs.Start_Date,start_date),
1092 end_date = p_assignment_attribs.End_Date,
1093 last_updated_by = fnd_global.user_id,
1094 last_update_date = SYSDATE,
1095 last_update_login = fnd_global.login_id,
1096 object_version_number = object_version_number + 1
1097 WHERE instrument_payment_use_id = x_assign_id;
1098 END IF;
1099
1100 x_response.Result_Code := G_RC_SUCCESS;
1101
1102 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1103 print_debuginfo(l_module, 'After access instr uses table.');
1104 END IF;
1105 END IF;
1106
1107 /*
1108 IF p_assignment_attribs.End_Date IS NOT NULL THEN
1109 raise_biz_event(l_instr_id, l_payee_id, x_assign_id);
1110 END IF;
1111 */
1112
1113 IF FND_API.To_Boolean(p_commit) THEN
1114 COMMIT;
1115 END IF;
1116
1117 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1118 print_debuginfo(l_module, 'Before prepare the result.');
1119
1120
1121 END IF;
1122 iby_fndcpt_common_pub.Prepare_Result
1123 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1124
1125 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1126 print_debuginfo(l_module, 'RETURN');
1127
1128 END IF;
1129 EXCEPTION
1130
1131 WHEN FND_API.G_EXC_ERROR THEN
1132 ROLLBACK TO Set_Payee_Instr_Assignment;
1133 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
1134 debug_level => FND_LOG.LEVEL_ERROR,
1135 module => G_DEBUG_MODULE || l_module);
1136 x_return_status := FND_API.G_RET_STS_ERROR ;
1137 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1138 p_data => x_msg_data
1139 );
1140 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1141 ROLLBACK TO Set_Payee_Instr_Assignment;
1142 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
1143 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1144 module => G_DEBUG_MODULE || l_module);
1145 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1146 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1147 p_data => x_msg_data
1148 );
1149 WHEN OTHERS THEN
1150 ROLLBACK TO Set_Payee_Instr_Assignment;
1151 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
1152 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1153 module => G_DEBUG_MODULE || l_module);
1154 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1155 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1156 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_module);
1157 END IF;
1158
1159 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1160 p_data => x_msg_data
1161 );
1162 END Set_Payee_Instr_Assignment;
1163
1164 -- Start of comments
1165 -- API name : Get_Payee_Instr_Assignments
1166 -- Type : Public
1167 -- Pre-reqs : None
1168 -- Function : Create payee bank account assignment
1169 -- Parameters :
1170 -- IN : p_api_version IN NUMBER Required
1171 -- p_init_msg_list IN VARCHAR2 Optional
1172 -- p_ext_payee_tab IN External_Payee_Tab_Type Required
1173 -- OUT : x_return_status OUT VARCHAR2 Required
1174 -- x_msg_count OUT NUMBER Required
1175 -- x_msg_data OUT VARCHAR2 Required
1176 -- x_ext_payee_id_tab OUT Ext_Payee_ID_Tab_Type
1177 -- x_ext_payee_status_tab OUT Ext_Payee_Create_Tab_Type Required
1178 --
1179 -- Version : Current version 1.0
1180 -- Previous version None
1181 -- Initial version 1.0
1182 -- End of comments
1183
1184 PROCEDURE Get_Payee_Instr_Assignments (
1185 p_api_version IN NUMBER,
1186 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1187 x_return_status OUT NOCOPY VARCHAR2,
1188 x_msg_count OUT NOCOPY NUMBER,
1189 x_msg_data OUT NOCOPY VARCHAR2,
1190 p_payee IN PayeeContext_rec_type,
1191 x_assignments OUT NOCOPY IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_tbl_type,
1192 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
1193 )
1194 IS
1195 l_api_version CONSTANT NUMBER := 1.0;
1196 l_module CONSTANT VARCHAR2(30) := 'Get_Payer_Instr_Assignments';
1197 l_prev_msg_count NUMBER;
1198
1199 l_payee_level VARCHAR2(30);
1200 l_payee_id iby_external_payees_all.ext_payee_id%TYPE;
1201
1202 l_assign_count NUMBER := 0;
1203
1204 CURSOR c_instr_assignments
1205 (ci_payee_id IN iby_pmt_instr_uses_all.ext_pmt_party_id%TYPE)
1206 IS
1207 SELECT instrument_payment_use_id,
1208 instrument_type,
1209 instrument_id,
1210 order_of_preference,
1211 start_date,
1212 end_date
1213 FROM iby_pmt_instr_uses_all
1214 WHERE (payment_flow = G_PMT_FLOW_DISBURSE)
1215 AND (ext_pmt_party_id = ci_payee_id);
1216
1217 BEGIN
1218
1219 IF (c_instr_assignments%ISOPEN) THEN
1220 CLOSE c_instr_assignments;
1221 END IF;
1222
1223 IF NOT FND_API.Compatible_API_Call (l_api_version,
1224 p_api_version,
1225 l_module,
1226 G_PKG_NAME)
1227 THEN
1228 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
1229 debug_level => FND_LOG.LEVEL_ERROR,
1230 module => G_DEBUG_MODULE || l_module);
1231 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1232 FND_MSG_PUB.Add;
1233 RAISE FND_API.G_EXC_ERROR;
1234 END IF;
1235
1236 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1237 FND_MSG_PUB.initialize;
1238 END IF;
1239 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
1240
1241 Get_Payee_Id(p_payee,FND_API.G_VALID_LEVEL_FULL,l_payee_level,l_payee_id);
1242
1243 IF (l_payee_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
1244 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
1245 ELSE
1246 l_assign_count := 0;
1247 FOR assign_rec IN c_instr_assignments(l_payee_id)
1248 LOOP
1249 l_assign_count := l_assign_count + 1;
1250
1251 x_assignments(l_assign_count).Assignment_Id :=
1252 assign_rec.instrument_payment_use_id;
1253 x_assignments(l_assign_count).Instrument.Instrument_Type :=
1254 assign_rec.instrument_type;
1255 x_assignments(l_assign_count).Instrument.Instrument_Id :=
1256 assign_rec.instrument_id;
1257 x_assignments(l_assign_count).Priority := assign_rec.order_of_preference;
1258 x_assignments(l_assign_count).Start_Date := assign_rec.start_date;
1259 x_assignments(l_assign_count).End_Date := assign_rec.end_date;
1260 END LOOP;
1261
1262 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
1263
1264 END IF;
1265
1266 iby_fndcpt_common_pub.Prepare_Result
1267 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1268
1269 EXCEPTION
1270
1271 WHEN FND_API.G_EXC_ERROR THEN
1272
1273 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
1274 debug_level => FND_LOG.LEVEL_ERROR,
1275 module => G_DEBUG_MODULE || l_module);
1276 x_return_status := FND_API.G_RET_STS_ERROR ;
1277 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1278 p_data => x_msg_data
1279 );
1280 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1281
1282 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
1283 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1284 module => G_DEBUG_MODULE || l_module);
1285 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1286 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1287 p_data => x_msg_data
1288 );
1289 WHEN OTHERS THEN
1290
1291 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
1292 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1293 module => G_DEBUG_MODULE || l_module);
1294 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1295 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1296 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_module);
1297 END IF;
1298
1299 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1300 p_data => x_msg_data
1301 );
1302 END Get_Payee_Instr_Assignments;
1303
1304 -- Start of comments
1305 -- API name : Get_Payee_All_Instruments
1306 -- Type : Public
1307 -- Pre-reqs : None
1308 -- Function : Create payee bank account assignment
1309 -- Parameters :
1310 -- IN : p_api_version IN NUMBER Required
1311 -- p_init_msg_list IN VARCHAR2 Optional
1312 -- p_ext_payee_tab IN External_Payee_Tab_Type Required
1313 -- OUT : x_return_status OUT VARCHAR2 Required
1314 -- x_msg_count OUT NUMBER Required
1315 -- x_msg_data OUT VARCHAR2 Required
1316 -- x_ext_payee_id_tab OUT Ext_Payee_ID_Tab_Type
1317 -- x_ext_payee_status_tab OUT Ext_Payee_Create_Tab_Type Required
1318 --
1319 -- Version : Current version 1.0
1320 -- Previous version None
1321 -- Initial version 1.0
1322 -- End of comments
1323
1324 PROCEDURE Get_Payee_All_Instruments (
1325 p_api_version IN NUMBER,
1326 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1327 x_return_status OUT NOCOPY VARCHAR2,
1328 x_msg_count OUT NOCOPY NUMBER,
1329 x_msg_data OUT NOCOPY VARCHAR2,
1330 p_party_id IN NUMBER,
1331 x_instruments OUT NOCOPY IBY_FNDCPT_SETUP_PUB.PmtInstrument_tbl_type,
1332 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
1333 )
1334 IS
1335 l_api_version CONSTANT NUMBER := 1.0;
1336 l_module CONSTANT VARCHAR2(30) := 'Get_Payer_All_Assignments';
1337 l_prev_msg_count NUMBER;
1338
1339 l_instr_count NUMBER := 0;
1340
1341 CURSOR c_instr_assignments
1342 (ci_party_id IN iby_external_payees_all.payee_party_id%TYPE)
1343 IS
1344 SELECT DISTINCT u.instrument_type, u.instrument_id
1345 FROM iby_pmt_instr_uses_all u, iby_external_payees_all p
1346 WHERE (u.payment_flow = G_PMT_FLOW_DISBURSE)
1347 AND (u.ext_pmt_party_id = p.ext_payee_id)
1348 AND (p.payee_party_id = ci_party_id);
1349
1350 BEGIN
1351
1352 IF (c_instr_assignments%ISOPEN) THEN
1353 CLOSE c_instr_assignments;
1354 END IF;
1355
1356 IF NOT FND_API.Compatible_API_Call (l_api_version,
1357 p_api_version,
1358 l_module,
1359 G_PKG_NAME)
1360 THEN
1361 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
1362 debug_level => FND_LOG.LEVEL_ERROR,
1363 module => G_DEBUG_MODULE || l_module);
1364 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1365 FND_MSG_PUB.Add;
1366 RAISE FND_API.G_EXC_ERROR;
1367 END IF;
1368
1369 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1370 FND_MSG_PUB.initialize;
1371 END IF;
1372 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
1373
1374 l_instr_count := 0;
1375 FOR assign_rec IN c_instr_assignments(p_party_id) LOOP
1376 l_instr_count := l_instr_count + 1;
1377
1378 x_instruments(l_instr_count).Instrument_Type := assign_rec.instrument_type;
1379 x_instruments(l_instr_count).Instrument_Id := assign_rec.instrument_id;
1380 END LOOP;
1381
1382 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
1383
1384 iby_fndcpt_common_pub.Prepare_Result
1385 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1386
1387 EXCEPTION
1388
1389 WHEN FND_API.G_EXC_ERROR THEN
1390
1391 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
1392 debug_level => FND_LOG.LEVEL_ERROR,
1393 module => G_DEBUG_MODULE || l_module);
1394 x_return_status := FND_API.G_RET_STS_ERROR ;
1395 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1396 p_data => x_msg_data
1397 );
1398 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1399
1400 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
1401 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1402 module => G_DEBUG_MODULE || l_module);
1403 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1404 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1405 p_data => x_msg_data
1406 );
1407 WHEN OTHERS THEN
1408
1409 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
1410 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1411 module => G_DEBUG_MODULE || l_module);
1412 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1413 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1414 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_module);
1415 END IF;
1416
1417 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1418 p_data => x_msg_data
1419 );
1420
1421 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
1422 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1423 module => G_DEBUG_MODULE || l_module);
1424 iby_debug_pub.add(debug_msg => 'Exit Exception',
1425 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1426 module => G_DEBUG_MODULE || l_module);
1427 END Get_Payee_All_Instruments;
1428
1429 -- CheckInLookup
1430 --
1431 --
1432 FUNCTION CheckInLookup(
1433 p_value VARCHAR2,
1434 p_loopkup_type VARCHAR2
1435 ) RETURN BOOLEAN
1436 IS
1437 l_count PLS_INTEGER;
1438
1439 CURSOR lookup_csr(p_lookup_type IN VARCHAR2,
1440 p_lookup_code IN VARCHAR2)
1441 IS
1442 SELECT COUNT(LOOKUP_CODE)
1443 FROM FND_LOOKUPS
1444 WHERE LOOKUP_TYPE = p_lookup_type
1445 AND LOOKUP_CODE = p_lookup_code;
1446 BEGIN
1447
1448 OPEN lookup_csr(p_loopkup_type, p_value);
1449 FETCH lookup_csr INTO l_count;
1450 CLOSE lookup_csr;
1451
1452 IF (l_count = 0) THEN
1453 FND_MESSAGE.set_name('IBY', 'IBY_LOOKUP_VAL_ERROR');
1454 FND_MESSAGE.SET_TOKEN('LOOKUPTYPE', p_loopkup_type);
1455 FND_MESSAGE.SET_TOKEN('VALUE', p_value);
1456 FND_MSG_PUB.Add;
1457 RETURN FALSE;
1458 ELSE
1459 RETURN TRUE;
1460 END IF;
1461
1462 END CheckInLookup;
1463
1464 -- Validate_External_Payee
1465 --
1466 -- API name : Validate_External_Payee
1467 -- Type : Public
1468 -- Pre-reqs : None
1469 -- Function : Validate an External Payee
1470 -- Current version : 1.0
1471 -- Previous version: 1.0
1472 -- Initial version : 1.0
1473
1474 PROCEDURE Validate_External_Payee (
1475 p_api_version IN NUMBER,
1476 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
1477 p_ext_payee_rec IN External_Payee_Rec_Type,
1478 x_return_status OUT NOCOPY VARCHAR2,
1479 x_msg_count OUT NOCOPY NUMBER,
1480 x_msg_data OUT NOCOPY VARCHAR2
1481 ) IS
1482
1483 l_api_name CONSTANT VARCHAR2(30) := 'Validate_External_Payee';
1484 l_api_version CONSTANT NUMBER := 1.0;
1485 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
1486
1487
1488 l_payee_country VARCHAR2(30);
1489 l_valid BOOLEAN;
1490 l_temp_valid BOOLEAN;
1491 l_count PLS_INTEGER;
1492
1493 CURSOR payee_country_csr(p_payee_id IN NUMBER)
1494 IS
1495 SELECT country
1496 FROM HZ_PARTIES
1497 WHERE party_id = p_payee_id;
1498
1499 CURSOR payeesite_country_csr(p_payee_id IN NUMBER,
1500 p_payee_site_id IN NUMBER)
1501 IS
1502 SELECT locs.country
1503 FROM HZ_PARTY_SITES sites,
1504 HZ_LOCATIONS locs
1505 WHERE sites.party_id = p_payee_id
1506 AND sites.party_site_id = p_payee_site_id
1507 AND sites.location_id = locs.location_id;
1508
1509 CURSOR pmt_reasons_csr(p_pmt_reason_code VARCHAR2)
1510 IS
1511 SELECT COUNT(payment_reason_code)
1512 FROM IBY_PAYMENT_REASONS_VL ibypr
1513 WHERE ibypr.payment_reason_code = p_pmt_reason_code
1514 AND (ibypr.inactive_date is NULL OR ibypr.inactive_date >= trunc(sysdate));
1515
1516 CURSOR dlv_channels_csr(p_dlv_channel_code VARCHAR2)
1517 IS
1518 SELECT COUNT(delivery_channel_code)
1519 FROM IBY_DELIVERY_CHANNELS_VL ibydlv
1520 WHERE ibydlv.delivery_channel_code = p_dlv_channel_code
1521 AND (ibydlv.inactive_date is NULL OR ibydlv.inactive_date >= trunc(sysdate));
1522
1523 CURSOR payment_formats_csr(p_payment_format_code VARCHAR2)
1524 IS
1525 SELECT COUNT(f.format_code)
1526 FROM IBY_FORMATS_VL f
1527 WHERE f.format_code = p_payment_format_code;
1528
1529 CURSOR pmt_mthds_csr(p_payment_mthd_code VARCHAR2)
1530 IS
1531 SELECT COUNT(Payment_Method_Name)
1532 PAYMENT_METHOD_CODE
1533 FROM IBY_PAYMENT_METHODS_VL
1534 WHERE PAYMENT_METHOD_CODE = p_payment_mthd_code;
1535
1536 BEGIN
1537
1538 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1539 print_debuginfo(l_module_name, 'Enter');
1540 END IF;
1541
1542 SAVEPOINT Validate_External_Payee_pub;
1543
1544 -- Standard call to check for call compatibility.
1545 IF NOT FND_API.Compatible_API_Call(l_api_version,
1546 p_api_version,
1547 l_api_name,
1548 G_PKG_NAME) THEN
1549 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1550 END IF;
1551
1552 -- Initialize message list if p_init_msg_list is set to TRUE.
1553 IF FND_API.to_Boolean(p_init_msg_list) THEN
1554 FND_MSG_PUB.initialize;
1555 END IF;
1556
1557 -- Initialize API return status to success
1558 x_return_status := FND_API.G_RET_STS_SUCCESS;
1559
1560 -- Start of API body
1561 IF (p_ext_payee_rec.Payee_Party_Id IS NOT NULL) THEN
1562 IF (p_ext_payee_rec.Payee_Party_Site_Id IS NOT NULL) THEN
1563 -- Fetch Payee Site Country
1564 OPEN payeesite_country_csr(p_ext_payee_rec.Payee_Party_Id,
1565 p_ext_payee_rec.Payee_Party_Site_Id);
1566 FETCH payeesite_country_csr INTO l_payee_country;
1567 CLOSE payeesite_country_csr;
1568 ELSE
1569 -- Fetch Payee Country
1570 OPEN payee_country_csr(p_ext_payee_rec.Payee_Party_Id);
1571 FETCH payee_country_csr INTO l_payee_country;
1572 CLOSE payee_country_csr;
1573 END IF;
1574 END IF;
1575
1576 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1577 print_debuginfo(l_module_name, 'Validate Payment Function');
1578 print_debuginfo(l_module_name, 'Payment Function::'||p_ext_payee_rec.Payment_Function);
1579 END IF;
1580 -- 1. Validate Payment Function (lookup: IBY_PAYMENT_FUNCTIONS)
1581 -- Payment Function is Mandatory
1582 l_temp_valid := CheckInLookup(p_ext_payee_rec.Payment_Function,
1583 'IBY_PAYMENT_FUNCTIONS');
1584
1585 l_valid := l_valid AND l_temp_valid;
1586
1587 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1588 print_debuginfo(l_module_name, 'Validate Exclusive Pay Flag');
1589 print_debuginfo(l_module_name, 'Exclusive Pay Flag::'||p_ext_payee_rec.Exclusive_Pay_Flag);
1590 END IF;
1591
1592 -- 2. Validate Exclusive Payment Flag (lookup:)
1593 -- Exclusive Payment Flag is mandatory
1594 l_temp_valid := (p_ext_payee_rec.Exclusive_Pay_Flag IN ('Y','N'));
1595
1596 l_valid := l_valid AND l_temp_valid;
1597
1598 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1599 print_debuginfo(l_module_name, 'Validate Default Payment Method');
1600 print_debuginfo(l_module_name, 'Default Payment Method::'||p_ext_payee_rec.Default_Pmt_method);
1601 END IF;
1602
1603 -- 3. Validate Default Payment Method (table: IBY_PAYMENT_METHODS_VL)
1604 -- is not mandatory
1605 IF (p_ext_payee_rec.Default_Pmt_method IS NOT NULL) THEN
1606
1607 OPEN pmt_mthds_csr(p_ext_payee_rec.Default_Pmt_method);
1608 FETCH pmt_mthds_csr INTO l_count;
1609 CLOSE pmt_mthds_csr;
1610
1611 IF (l_count = 0) THEN
1612 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1613 print_debuginfo(l_module_name, 'InValid Default Payment Method');
1614 END IF;
1615 FND_MESSAGE.set_name('IBY', 'IBY_TABLE_VAL_ERROR');
1616 FND_MESSAGE.SET_TOKEN('TABLE', 'IBY_PAYMENT_METHODS_V');
1617 FND_MESSAGE.SET_TOKEN('VALUE', p_ext_payee_rec.Default_Pmt_method);
1618 FND_MSG_PUB.Add;
1619 l_valid := FALSE;
1620 END IF;
1621 END IF;
1622
1623 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1624 print_debuginfo(l_module_name, 'Validate Bank Charge Bearer');
1625 print_debuginfo(l_module_name, 'Bank Charge Bearer::'||p_ext_payee_rec.Bank_Charge_Bearer);
1626 END IF;
1627
1628 -- 4. Validate Bank Charge Bearer (lookup: IBY_BANK_CHARGE_BEARER)
1629 -- is not mandatory
1630 IF (p_ext_payee_rec.Bank_Charge_Bearer IS NOT NULL) THEN
1631 l_temp_valid := CheckInLookup(p_ext_payee_rec.Bank_Charge_Bearer,
1632 'IBY_BANK_CHARGE_BEARER');
1633
1634 l_valid := l_valid AND l_temp_valid;
1635 END IF;
1636
1637
1638 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1639 print_debuginfo(l_module_name, 'Validate Payment Reason Code');
1640 print_debuginfo(l_module_name, 'Payment Reason Code::'||p_ext_payee_rec.Pay_Reason_Code);
1641 END IF;
1642 -- 5. Validate Payment Reason Code (table: IBY_PAYMENT_REASONS_VL by country)
1643 -- is not mandatory
1644 IF (p_ext_payee_rec.Pay_Reason_Code IS NOT NULL) THEN
1645 OPEN pmt_reasons_csr(p_ext_payee_rec.Pay_Reason_Code);
1646 FETCH pmt_reasons_csr INTO l_count;
1647 CLOSE pmt_reasons_csr;
1648
1649 IF (l_count = 0) THEN
1650 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1651 print_debuginfo(l_module_name, 'InValid Payment Reason Code');
1652 END IF;
1653 FND_MESSAGE.set_name('IBY', 'IBY_TABLE_VAL_ERROR');
1654 FND_MESSAGE.SET_TOKEN('TABLE', 'IBY_PAYMENT_REASONS_VL');
1655 FND_MESSAGE.SET_TOKEN('VALUE', p_ext_payee_rec.Pay_Reason_Code);
1656 FND_MSG_PUB.Add;
1657 l_valid := FALSE;
1658 END IF;
1659 END IF;
1660
1661 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1662 print_debuginfo(l_module_name, 'Validate Delivery Channel Code');
1663 print_debuginfo(l_module_name, 'Delivery Channel Code::'||p_ext_payee_rec.Delivery_Channel);
1664 END IF;
1665
1666 -- 6. Validate Delivery Channel Code (table: IBY_DELIVERY_CHANNELS_VL by country)
1667 -- is not mandatory
1668 IF (p_ext_payee_rec.Delivery_Channel IS NOT NULL) THEN
1669 OPEN dlv_channels_csr(p_ext_payee_rec.Delivery_Channel);
1670 FETCH dlv_channels_csr INTO l_count;
1671 CLOSE dlv_channels_csr;
1672
1673 IF (l_count = 0) THEN
1674 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1675 print_debuginfo(l_module_name, 'InValid Delivery Channel');
1676 END IF;
1677 FND_MESSAGE.set_name('IBY', 'IBY_TABLE_VAL_ERROR');
1678 FND_MESSAGE.SET_TOKEN('TABLE', 'IBY_DELIVERY_CHANNELS_VL');
1679 FND_MESSAGE.SET_TOKEN('VALUE', p_ext_payee_rec.Delivery_Channel);
1680 FND_MSG_PUB.Add;
1681 l_valid := FALSE;
1682 END IF;
1683 END IF;
1684
1685
1686 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1687 print_debuginfo(l_module_name, 'Validate Payment Format Code');
1688 print_debuginfo(l_module_name, 'Payment Format Code::'||p_ext_payee_rec.Pmt_Format);
1689 END IF;
1690 -- 7. Validate Payment Format Code (table: IBY_FORMATS_VL)
1691 -- is not mandatory
1692 IF (p_ext_payee_rec.Pmt_Format IS NOT NULL) THEN
1693 OPEN payment_formats_csr(p_ext_payee_rec.Pmt_Format);
1694 FETCH payment_formats_csr INTO l_count;
1695 CLOSE payment_formats_csr;
1696
1697 IF (l_count = 0) THEN
1698 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1699 print_debuginfo(l_module_name, 'InValid Payment Format Code');
1700 END IF;
1701 FND_MESSAGE.set_name('IBY', 'IBY_TABLE_VAL_ERROR');
1702 FND_MESSAGE.SET_TOKEN('TABLE', 'IBY_FORMATS_VL');
1703 FND_MESSAGE.SET_TOKEN('VALUE', p_ext_payee_rec.Pmt_Format);
1704 FND_MSG_PUB.Add;
1705 l_valid := FALSE;
1706 END IF;
1707 END IF;
1708
1709 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1710 print_debuginfo(l_module_name, 'Validate Settlement Priority');
1711 print_debuginfo(l_module_name, 'Settlement Priority::'||p_ext_payee_rec.Settlement_Priority);
1712 END IF;
1713 -- 8. Validate Settlement Priority (lookup: IBY_SETTLEMENT_PRIORITY)
1714 -- is not mandatory
1715 IF (p_ext_payee_rec.Settlement_Priority IS NOT NULL) THEN
1716 l_temp_valid := CheckInLookup(p_ext_payee_rec.Settlement_Priority,
1717 'IBY_SETTLEMENT_PRIORITY');
1718 l_valid := l_valid AND l_temp_valid;
1719 END IF;
1720
1721 -- Return Error if any validations has failed.
1722 IF (NOT l_valid) THEN
1723 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1724 print_debuginfo(l_module_name, 'Returning Error Status');
1725 END IF;
1726 x_return_status := FND_API.G_RET_STS_ERROR;
1727 END IF;
1728
1729 -- End of API body
1730
1731 -- get message count and if count is 1, get message info.
1732 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1733 p_count => x_msg_count,
1734 p_data => x_msg_data);
1735
1736 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1737 print_debuginfo(l_module_name, 'Exit');
1738 END IF;
1739 EXCEPTION
1740 WHEN fnd_api.g_exc_error THEN
1741 ROLLBACK TO Validate_External_Payee_pub;
1742 x_return_status := fnd_api.g_ret_sts_error;
1743 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1744 p_count => x_msg_count,
1745 p_data => x_msg_data);
1746
1747
1748 WHEN fnd_api.g_exc_unexpected_error THEN
1749 ROLLBACK TO Validate_External_Payee_pub;
1750 x_return_status := fnd_api.g_ret_sts_unexp_error;
1751 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1752 p_count => x_msg_count,
1753 p_data => x_msg_data);
1754
1755
1756 WHEN OTHERS THEN
1757 ROLLBACK TO Validate_External_Payee_pub;
1758 x_return_status := fnd_api.g_ret_sts_unexp_error;
1759 fnd_message.set_name('IBY', 'IBY_API_OTHERS_EXCEP');
1760 fnd_message.set_token('ERROR',SQLERRM);
1761 fnd_msg_pub.add;
1762 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1763 p_count => x_msg_count,
1764 p_data => x_msg_data);
1765
1766
1767 END Validate_External_Payee;
1768
1769
1770 -- Create_Temp_Ext_Bank_Acct
1771 --
1772 -- API name : Create_Temp_Ext_Bank_Acct
1773 -- Type : Public
1774 -- Pre-reqs : None
1775 -- Function : Create_Temp_Ext_Bank_Acct
1776 -- Current version : 1.0
1777 -- Previous version: 1.0
1778 -- Initial version : 1.0
1779
1780 PROCEDURE Create_Temp_Ext_Bank_Acct (
1781 p_api_version IN NUMBER,
1782 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
1783 x_return_status OUT NOCOPY VARCHAR2,
1784 x_msg_count OUT NOCOPY NUMBER,
1785 x_msg_data OUT NOCOPY VARCHAR2,
1786 p_temp_ext_acct_id IN NUMBER,
1787 x_bank_acc_id OUT NOCOPY Number,
1788 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
1789 ) IS
1790
1791 l_api_name CONSTANT VARCHAR2(30) := 'Create_Temp_Ext_Bank_Acct';
1792 l_api_version CONSTANT NUMBER := 1.0;
1793 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
1794
1795 CURSOR iby_ext_bank_csr(p_temp_ext_acct_id NUMBER)
1796 IS
1797 SELECT
1798 EXT_BANK_ACCOUNT_ID,
1799 COUNTRY_CODE,
1800 BRANCH_ID,
1801 BANK_ID,
1802 BANK_NAME,
1803 BANK_NUMBER,
1804 BANK_NAME_ALT,
1805 BANK_INSTITUTION_TYPE,
1806 BANK_ADDRESS_ID,
1807 BRANCH_NUMBER,
1808 BRANCH_TYPE,
1809 BRANCH_NAME,
1810 BRANCH_NAME_ALT,
1811 BIC,
1812 RFC_IDENTIFIER,
1813 BANK_CODE,
1814 BRANCH_ADDRESS_ID,
1815 ACCOUNT_OWNER_PARTY_ID,
1816 OWNER_PRIMARY_FLAG,
1817 BANK_ACCOUNT_NAME,
1818 BANK_ACCOUNT_NUM,
1819 CURRENCY_CODE,
1820 IBAN,
1821 CHECK_DIGITS,
1822 BANK_ACCOUNT_NAME_ALT,
1823 BANK_ACCOUNT_TYPE,
1824 ACCOUNT_SUFFIX,
1825 DESCRIPTION,
1826 AGENCY_LOCATION_CODE,
1827 PAYMENT_FACTOR_FLAG,
1828 FOREIGN_PAYMENT_USE_FLAG,
1829 EXCHANGE_RATE_AGREEMENT_NUM,
1830 EXCHANGE_RATE_AGREEMENT_TYPE,
1831 EXCHANGE_RATE,
1832 START_DATE,
1833 END_DATE,
1834 ATTRIBUTE_CATEGORY,
1835 NOTE,
1836 NOTE_ALT,
1837 ATTRIBUTE1,
1838 ATTRIBUTE2,
1839 ATTRIBUTE3,
1840 ATTRIBUTE4,
1841 ATTRIBUTE5,
1842 ATTRIBUTE6,
1843 ATTRIBUTE7,
1844 ATTRIBUTE8,
1845 ATTRIBUTE9,
1846 ATTRIBUTE10,
1847 ATTRIBUTE11,
1848 ATTRIBUTE12,
1849 ATTRIBUTE13,
1850 ATTRIBUTE14,
1851 ATTRIBUTE15,
1852 STATUS,
1853 LAST_UPDATE_DATE,
1854 LAST_UPDATED_BY,
1855 CREATION_DATE,
1856 CREATED_BY,
1857 LAST_UPDATE_LOGIN,
1858 REQUEST_ID,
1859 PROGRAM_APPLICATION_ID,
1860 PROGRAM_ID,
1861 PROGRAM_UPDATE_DATE,
1862 OBJECT_VERSION_NUMBER,
1863 CALLING_APP_UNIQUE_REF1,
1864 CALLING_APP_UNIQUE_REF2,
1865 EXT_PAYEE_ID
1866 FROM IBY_TEMP_EXT_BANK_ACCTS
1867 WHERE TEMP_EXT_BANK_ACCT_ID = p_temp_ext_acct_id;
1868
1869 temp_ext_bank_acct_rec iby_ext_bank_csr%ROWTYPE;
1870 ext_bank_acct_rec IBY_EXT_BANKACCT_PUB.ExtBankAcct_rec_type;
1871
1872 BEGIN
1873
1874 SAVEPOINT Create_Temp_Ext_Bank_Acct_pub;
1875
1876 -- Standard call to check for call compatibility.
1877 IF NOT FND_API.Compatible_API_Call(l_api_version,
1878 p_api_version,
1879 l_api_name,
1880 G_PKG_NAME) THEN
1881 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1882 END IF;
1883
1884 -- Initialize message list if p_init_msg_list is set to TRUE.
1885 IF FND_API.to_Boolean(p_init_msg_list) THEN
1886 FND_MSG_PUB.initialize;
1887 END IF;
1888
1889 -- Initialize API return status to success
1890 x_return_status := FND_API.G_RET_STS_SUCCESS;
1891
1892 -- Start of API body
1893 OPEN iby_ext_bank_csr(p_temp_ext_acct_id);
1894 FETCH iby_ext_bank_csr INTO temp_ext_bank_acct_rec;
1895 CLOSE iby_ext_bank_csr;
1896
1897 ext_bank_acct_rec.country_code := temp_ext_bank_acct_rec.COUNTRY_CODE;
1898 ext_bank_acct_rec.branch_id := temp_ext_bank_acct_rec.branch_id;
1899 ext_bank_acct_rec.bank_id := temp_ext_bank_acct_rec.bank_id;
1900 ext_bank_acct_rec.acct_owner_party_id := temp_ext_bank_acct_rec.ACCOUNT_OWNER_PARTY_ID;
1901 ext_bank_acct_rec.bank_account_name := temp_ext_bank_acct_rec.bank_account_name;
1902 ext_bank_acct_rec.bank_account_num := temp_ext_bank_acct_rec.bank_account_num;
1903 ext_bank_acct_rec.currency := temp_ext_bank_acct_rec.CURRENCY_CODE;
1904 ext_bank_acct_rec.iban := temp_ext_bank_acct_rec.iban;
1905 ext_bank_acct_rec.check_digits := temp_ext_bank_acct_rec.check_digits;
1906 ext_bank_acct_rec.alternate_acct_name := temp_ext_bank_acct_rec.BANK_ACCOUNT_NAME_ALT;
1907 ext_bank_acct_rec.acct_type := temp_ext_bank_acct_rec.BANK_ACCOUNT_TYPE;
1908 ext_bank_acct_rec.acct_suffix := temp_ext_bank_acct_rec.ACCOUNT_SUFFIX;
1909 ext_bank_acct_rec.description := temp_ext_bank_acct_rec.description;
1910 ext_bank_acct_rec.agency_location_code := temp_ext_bank_acct_rec.agency_location_code;
1911 ext_bank_acct_rec.foreign_payment_use_flag := temp_ext_bank_acct_rec.foreign_payment_use_flag;
1912 ext_bank_acct_rec.exchange_rate_agreement_num := temp_ext_bank_acct_rec.exchange_rate_agreement_num;
1913 ext_bank_acct_rec.exchange_rate_agreement_type := temp_ext_bank_acct_rec.exchange_rate_agreement_type;
1914 ext_bank_acct_rec.exchange_rate := temp_ext_bank_acct_rec.exchange_rate;
1915 ext_bank_acct_rec.payment_factor_flag := temp_ext_bank_acct_rec.payment_factor_flag;
1916 ext_bank_acct_rec.end_date := temp_ext_bank_acct_rec.end_date;
1917 ext_bank_acct_rec.START_DATE := temp_ext_bank_acct_rec.START_DATE;
1918 ext_bank_acct_rec.attribute_category := temp_ext_bank_acct_rec.attribute_category;
1919 ext_bank_acct_rec.attribute1 := temp_ext_bank_acct_rec.attribute1;
1920 ext_bank_acct_rec.attribute2 := temp_ext_bank_acct_rec.attribute2;
1921 ext_bank_acct_rec.attribute3 := temp_ext_bank_acct_rec.attribute3;
1922 ext_bank_acct_rec.attribute4 := temp_ext_bank_acct_rec.attribute4;
1923 ext_bank_acct_rec.attribute5 := temp_ext_bank_acct_rec.attribute5;
1924 ext_bank_acct_rec.attribute6 := temp_ext_bank_acct_rec.attribute6;
1925 ext_bank_acct_rec.attribute7 := temp_ext_bank_acct_rec.attribute7;
1926 ext_bank_acct_rec.attribute8 := temp_ext_bank_acct_rec.attribute8;
1927 ext_bank_acct_rec.attribute9 := temp_ext_bank_acct_rec.attribute9;
1928 ext_bank_acct_rec.attribute10 := temp_ext_bank_acct_rec.attribute10;
1929 ext_bank_acct_rec.attribute11 := temp_ext_bank_acct_rec.attribute11;
1930 ext_bank_acct_rec.attribute12 := temp_ext_bank_acct_rec.attribute12;
1931 ext_bank_acct_rec.attribute13 := temp_ext_bank_acct_rec.attribute13;
1932 ext_bank_acct_rec.attribute14 := temp_ext_bank_acct_rec.attribute14;
1933 ext_bank_acct_rec.attribute15 := temp_ext_bank_acct_rec.attribute15;
1934
1935 -- Calling to create external bank account
1936 IBY_EXT_BANKACCT_PUB.create_ext_bank_acct(
1937 p_api_version => 1.0,
1938 p_init_msg_list => 'F',
1939 p_ext_bank_acct_rec => ext_bank_acct_rec,
1940 x_acct_id => x_bank_acc_id,
1941 x_return_status => x_return_status,
1942 x_msg_count => x_msg_count,
1943 x_msg_data => x_msg_data,
1944 x_response => x_response
1945 );
1946
1947
1948
1949 IF (x_bank_acc_id IS NULL) THEN
1950 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1951 END IF;
1952 -- End of API body
1953
1954 -- get message count and if count is 1, get message info.
1955 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1956 p_count => x_msg_count,
1957 p_data => x_msg_data);
1958
1959
1960 EXCEPTION
1961 WHEN fnd_api.g_exc_error THEN
1962 ROLLBACK TO Create_Temp_Ext_Bank_Acct_pub;
1963 x_return_status := fnd_api.g_ret_sts_error;
1964 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1965 p_count => x_msg_count,
1966 p_data => x_msg_data);
1967
1968
1969 WHEN fnd_api.g_exc_unexpected_error THEN
1970 ROLLBACK TO Create_Temp_Ext_Bank_Acct_pub;
1971 x_return_status := fnd_api.g_ret_sts_unexp_error;
1972 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1973 p_count => x_msg_count,
1974 p_data => x_msg_data);
1975
1976
1977 WHEN OTHERS THEN
1978 ROLLBACK TO Create_Temp_Ext_Bank_Acct_pub;
1979 x_return_status := fnd_api.g_ret_sts_unexp_error;
1980 fnd_message.set_name('IBY', 'IBY_API_OTHERS_EXCEP');
1981 fnd_message.set_token('ERROR',SQLERRM);
1982 fnd_msg_pub.add;
1983 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
1984 p_count => x_msg_count,
1985 p_data => x_msg_data);
1986
1987 END Create_Temp_Ext_Bank_Acct;
1988
1989 --Modified for the bug 6461487
1990 -- Create_Temp_Ext_Bank_Acct -- overloaded
1991 --
1992 -- API name : Create_Temp_Ext_Bank_Acct
1993 -- Type : Public
1994 -- Pre-reqs : None
1995 -- Function : Create_Temp_Ext_Bank_Acct
1996 -- Current version : 1.0
1997 -- Previous version: 1.0
1998 -- Initial version : 1.0
1999
2000 PROCEDURE Create_Temp_Ext_Bank_Acct (
2001 p_api_version IN NUMBER,
2002 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
2003 x_return_status OUT NOCOPY VARCHAR2,
2004 x_msg_count OUT NOCOPY NUMBER,
2005 x_msg_data OUT NOCOPY VARCHAR2,
2006 p_temp_ext_acct_id IN NUMBER,
2007 p_association_level IN VARCHAR2,
2008 p_supplier_site_id IN NUMBER,
2009 p_party_site_id IN NUMBER,
2010 p_org_id IN NUMBER,
2011 p_org_type IN VARCHAR2 default NULL,
2012 x_bank_acc_id OUT NOCOPY Number,
2013 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
2014 ) IS
2015
2016 l_api_name CONSTANT VARCHAR2(30) := 'Create_Temp_Ext_Bank_Acct';
2017 l_api_version CONSTANT NUMBER := 1.0;
2018 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
2019 l_dup_acct_id number;
2020 l_acct_owner_id number;
2021 l_dup_start_date date;
2022 l_dup_end_date date;
2023 l_assign_id NUMBER;
2024 l_rec IBY_DISBURSEMENT_SETUP_PUB.PayeeContext_rec_type;
2025 l_assign IBY_FNDCPT_SETUP_PUB.PmtInstrAssignment_rec_type;
2026
2027
2028
2029 CURSOR iby_ext_bank_csr(p_temp_ext_acct_id NUMBER)
2030 IS
2031 SELECT
2032 EXT_BANK_ACCOUNT_ID,
2033 COUNTRY_CODE,
2034 BRANCH_ID,
2035 BANK_ID,
2036 BANK_NAME,
2037 BANK_NUMBER,
2038 BANK_NAME_ALT,
2039 BANK_INSTITUTION_TYPE,
2040 BANK_ADDRESS_ID,
2041 BRANCH_NUMBER,
2042 BRANCH_TYPE,
2043 BRANCH_NAME,
2044 BRANCH_NAME_ALT,
2045 BIC,
2046 RFC_IDENTIFIER,
2047 BANK_CODE,
2048 BRANCH_ADDRESS_ID,
2049 ACCOUNT_OWNER_PARTY_ID,
2050 OWNER_PRIMARY_FLAG,
2051 BANK_ACCOUNT_NAME,
2052 BANK_ACCOUNT_NUM,
2053 CURRENCY_CODE,
2054 IBAN,
2055 CHECK_DIGITS,
2056 BANK_ACCOUNT_NAME_ALT,
2057 BANK_ACCOUNT_TYPE,
2058 ACCOUNT_SUFFIX,
2059 DESCRIPTION,
2060 AGENCY_LOCATION_CODE,
2061 PAYMENT_FACTOR_FLAG,
2062 FOREIGN_PAYMENT_USE_FLAG,
2063 EXCHANGE_RATE_AGREEMENT_NUM,
2064 EXCHANGE_RATE_AGREEMENT_TYPE,
2065 EXCHANGE_RATE,
2066 START_DATE,
2067 END_DATE,
2068 ATTRIBUTE_CATEGORY,
2069 NOTE,
2070 NOTE_ALT,
2071 ATTRIBUTE1,
2072 ATTRIBUTE2,
2073 ATTRIBUTE3,
2074 ATTRIBUTE4,
2075 ATTRIBUTE5,
2076 ATTRIBUTE6,
2077 ATTRIBUTE7,
2078 ATTRIBUTE8,
2079 ATTRIBUTE9,
2080 ATTRIBUTE10,
2081 ATTRIBUTE11,
2082 ATTRIBUTE12,
2083 ATTRIBUTE13,
2084 ATTRIBUTE14,
2085 ATTRIBUTE15,
2086 STATUS,
2087 LAST_UPDATE_DATE,
2088 LAST_UPDATED_BY,
2089 CREATION_DATE,
2090 CREATED_BY,
2091 LAST_UPDATE_LOGIN,
2092 REQUEST_ID,
2093 PROGRAM_APPLICATION_ID,
2094 PROGRAM_ID,
2095 PROGRAM_UPDATE_DATE,
2096 OBJECT_VERSION_NUMBER,
2097 CALLING_APP_UNIQUE_REF1,
2098 CALLING_APP_UNIQUE_REF2,
2099 EXT_PAYEE_ID
2100 FROM IBY_TEMP_EXT_BANK_ACCTS
2101 WHERE TEMP_EXT_BANK_ACCT_ID = p_temp_ext_acct_id;
2102
2103 temp_ext_bank_acct_rec iby_ext_bank_csr%ROWTYPE;
2104 ext_bank_acct_rec IBY_EXT_BANKACCT_PUB.ExtBankAcct_rec_type;
2105 -- Bug# 7451534 Begin
2106 intermediate_bank_acct_rec IBY_EXT_BANKACCT_PUB.IntermediaryAcct_rec_type;
2107 x_intmediary_bank_acct_id NUMBER;
2108 x_intermediary_return_status VARCHAR2(100);
2109 x_intermediary_msg_count NUMBER;
2110 x_intermediary_msg_data VARCHAR2(100);
2111 x_intermediary_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
2112 -- Bug# 7451534 End
2113
2114 BEGIN
2115 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2116 print_debuginfo(l_module_name, 'ENTER');
2117 END IF;
2118 SAVEPOINT Create_Temp_Ext_Bank_Acct_pub;
2119
2120 -- Standard call to check for call compatibility.
2121 IF NOT FND_API.Compatible_API_Call(l_api_version,
2122 p_api_version,
2123 l_api_name,
2124 G_PKG_NAME) THEN
2125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2126 END IF;
2127
2128 -- Initialize message list if p_init_msg_list is set to TRUE.
2129 IF FND_API.to_Boolean(p_init_msg_list) THEN
2130 FND_MSG_PUB.initialize;
2131 END IF;
2132
2133 -- Initialize API return status to success
2134 x_return_status := FND_API.G_RET_STS_SUCCESS;
2135
2136 -- Start of API body
2137 OPEN iby_ext_bank_csr(p_temp_ext_acct_id);
2138 FETCH iby_ext_bank_csr INTO temp_ext_bank_acct_rec;
2139 CLOSE iby_ext_bank_csr;
2140
2141 ext_bank_acct_rec.country_code := temp_ext_bank_acct_rec.COUNTRY_CODE;
2142 ext_bank_acct_rec.branch_id := temp_ext_bank_acct_rec.branch_id;
2143 ext_bank_acct_rec.bank_id := temp_ext_bank_acct_rec.bank_id;
2144 ext_bank_acct_rec.acct_owner_party_id := temp_ext_bank_acct_rec.ACCOUNT_OWNER_PARTY_ID;
2145 ext_bank_acct_rec.bank_account_name := temp_ext_bank_acct_rec.bank_account_name;
2146 ext_bank_acct_rec.bank_account_num := temp_ext_bank_acct_rec.bank_account_num;
2147 ext_bank_acct_rec.currency := temp_ext_bank_acct_rec.CURRENCY_CODE;
2148 ext_bank_acct_rec.iban := temp_ext_bank_acct_rec.iban;
2149 ext_bank_acct_rec.check_digits := temp_ext_bank_acct_rec.check_digits;
2150 ext_bank_acct_rec.alternate_acct_name := temp_ext_bank_acct_rec.BANK_ACCOUNT_NAME_ALT;
2151 ext_bank_acct_rec.acct_type := temp_ext_bank_acct_rec.BANK_ACCOUNT_TYPE;
2152 ext_bank_acct_rec.acct_suffix := temp_ext_bank_acct_rec.ACCOUNT_SUFFIX;
2153 ext_bank_acct_rec.description := temp_ext_bank_acct_rec.description;
2154 ext_bank_acct_rec.agency_location_code := temp_ext_bank_acct_rec.agency_location_code;
2155 ext_bank_acct_rec.foreign_payment_use_flag := temp_ext_bank_acct_rec.foreign_payment_use_flag;
2156 ext_bank_acct_rec.exchange_rate_agreement_num := temp_ext_bank_acct_rec.exchange_rate_agreement_num;
2157 ext_bank_acct_rec.exchange_rate_agreement_type := temp_ext_bank_acct_rec.exchange_rate_agreement_type;
2158 ext_bank_acct_rec.exchange_rate := temp_ext_bank_acct_rec.exchange_rate;
2159 ext_bank_acct_rec.payment_factor_flag := temp_ext_bank_acct_rec.payment_factor_flag;
2160 ext_bank_acct_rec.end_date := temp_ext_bank_acct_rec.end_date;
2161 ext_bank_acct_rec.START_DATE := temp_ext_bank_acct_rec.START_DATE;
2162 ext_bank_acct_rec.attribute_category := temp_ext_bank_acct_rec.attribute_category;
2163 ext_bank_acct_rec.attribute1 := temp_ext_bank_acct_rec.attribute1;
2164 ext_bank_acct_rec.attribute2 := temp_ext_bank_acct_rec.attribute2;
2165 ext_bank_acct_rec.attribute3 := temp_ext_bank_acct_rec.attribute3;
2166 ext_bank_acct_rec.attribute4 := temp_ext_bank_acct_rec.attribute4;
2167 ext_bank_acct_rec.attribute5 := temp_ext_bank_acct_rec.attribute5;
2168 ext_bank_acct_rec.attribute6 := temp_ext_bank_acct_rec.attribute6;
2169 ext_bank_acct_rec.attribute7 := temp_ext_bank_acct_rec.attribute7;
2170 ext_bank_acct_rec.attribute8 := temp_ext_bank_acct_rec.attribute8;
2171 ext_bank_acct_rec.attribute9 := temp_ext_bank_acct_rec.attribute9;
2172 ext_bank_acct_rec.attribute10 := temp_ext_bank_acct_rec.attribute10;
2173 ext_bank_acct_rec.attribute11 := temp_ext_bank_acct_rec.attribute11;
2174 ext_bank_acct_rec.attribute12 := temp_ext_bank_acct_rec.attribute12;
2175 ext_bank_acct_rec.attribute13 := temp_ext_bank_acct_rec.attribute13;
2176 ext_bank_acct_rec.attribute14 := temp_ext_bank_acct_rec.attribute14;
2177 ext_bank_acct_rec.attribute15 := temp_ext_bank_acct_rec.attribute15;
2178
2179
2180 /* CAll to verify if bank account is already created in the application*/
2181 IBY_EXT_BANKACCT_PUB.check_ext_acct_exist(
2182 p_api_version => 1.0,
2183 p_init_msg_list => 'F',
2184 p_ext_bank_acct_rec => ext_bank_acct_rec,
2185 x_acct_id => l_dup_acct_id,
2186 x_start_date => l_dup_start_date,
2187 x_end_date => l_dup_end_date,
2188 x_return_status => x_return_status,
2189 x_msg_count => x_msg_count,
2190 x_msg_data => x_msg_data,
2191 x_response => x_response
2192 );
2193 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2194 print_debuginfo(l_module_name, 'After the call to check_ext_acct_exist');
2195 print_debuginfo(l_module_name, 'X_RETURN_STATUS::'||x_return_status);
2196 print_debuginfo(l_module_name, 'Account Id::'||l_dup_acct_id);
2197 END IF;
2198
2199 /* If bank account doesn't exist in the application*/
2200 IF (x_return_status = FND_API.G_RET_STS_SUCCESS and l_dup_acct_id is null) THEN
2201
2202
2203 /* Calling Bank Account Validation API */
2204 IBY_DISBURSEMENT_SETUP_PUB.Validate_Temp_Ext_Bank_Acct(
2205 p_api_version => 1.0,
2206 p_init_msg_list => FND_API.G_FALSE,
2207 x_return_status => x_return_status,
2208 x_msg_count => x_msg_count,
2209 x_msg_data => x_msg_data,
2210 p_temp_ext_acct_id => p_temp_ext_acct_id);
2211
2212 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2213 print_debuginfo(l_module_name, 'After the call to Validate_Temp_Ext_Bank_Acct');
2214 print_debuginfo(l_module_name, 'Return Status::'||x_return_status);
2215 END IF;
2216
2217 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2218
2219 -- Calling to create external bank account
2220 IBY_EXT_BANKACCT_PUB.create_ext_bank_acct(
2221 p_api_version => 1.0,
2222 p_init_msg_list => 'F',
2223 p_ext_bank_acct_rec => ext_bank_acct_rec,
2224 p_association_level => p_association_level,
2225 p_supplier_site_id => p_supplier_site_id ,
2226 p_party_site_id => p_party_site_id ,
2227 p_org_id => p_org_id ,
2228 p_org_type => p_org_type,
2229 x_acct_id => x_bank_acc_id,
2230 x_return_status => x_return_status,
2231 x_msg_count => x_msg_count,
2232 x_msg_data => x_msg_data,
2233 x_response => x_response
2234 );
2235
2236 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2237 print_debuginfo(l_module_name, 'After the call to create_ext_bank_accout');
2238 print_debuginfo(l_module_name, 'Ext Bank Acct Id::'||x_bank_acc_id);
2239 print_debuginfo(l_module_name, 'Return Status::'||x_return_status);
2240 END IF;
2241
2242 IF (x_bank_acc_id IS NULL) THEN
2243 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2244 END IF;
2245 ELSE
2246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2247 FND_MESSAGE.SET_NAME('SQLAP','AP_INVALID_BANK_ACCT_INFO');
2248 FND_MSG_PUB.ADD;
2249 print_debuginfo(l_module_name, 'Bank Account Validation Failed');
2250 END IF;
2251
2252 /* If Bank account is already existing in the application*/
2253 ELSIF (l_dup_acct_id is not null) THEN
2254 x_return_status := FND_API.G_RET_STS_SUCCESS;
2255
2256 /* API call to find out whether this party is owner of bank account*/
2257 IBY_EXT_BANKACCT_PUB.check_bank_acct_owner (
2258 p_api_version => 1.0,
2259 p_init_msg_list => 'F',
2260 p_bank_acct_id => l_dup_acct_id,
2261 p_acct_owner_party_id => ext_bank_acct_rec.acct_owner_party_id,
2262 x_return_status => x_return_status,
2263 x_msg_count => x_msg_count,
2264 x_msg_data => x_msg_data,
2265 x_response => x_response
2266 );
2267 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2268 print_debuginfo(l_module_name, 'After the call to check_bank_acct_owner');
2269 print_debuginfo(l_module_name, 'X_RETURN_STATUS::'||x_return_status);
2270 END IF;
2271
2272 /* If party is not owner of that bank account*/
2273 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2274
2275 /* Add Party Id as Joint Account Owner of the Bank Account*/
2276 IBY_EXT_BANKACCT_PUB.add_joint_account_owner (
2277 p_api_version => 1.0,
2278 p_init_msg_list => 'F',
2279 p_bank_account_id => l_dup_acct_id,
2280 p_acct_owner_party_id => ext_bank_acct_rec.acct_owner_party_id,
2281 x_joint_acct_owner_id => l_acct_owner_id,
2282 x_return_status => x_return_status,
2283 x_msg_count => x_msg_count,
2284 x_msg_data => x_msg_data,
2285 x_response => x_response
2286 );
2287
2288 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2289 print_debuginfo(l_module_name, 'After the call to add_joint_account_owner');
2290 print_debuginfo(l_module_name, 'X_RETURN_STATUS::'||x_return_status);
2291 print_debuginfo(l_module_name, 'l_acct_owner_id::'||l_acct_owner_id);
2292 END IF;
2293
2294 END IF;
2295
2296 l_rec.Party_Site_id :=p_party_site_id;
2297 l_rec.Supplier_Site_id:=p_supplier_site_id;
2298 l_rec.Org_Id:=p_org_id;
2299 l_rec.Org_Type:=p_org_type;
2300 l_rec.Payment_Function :='PAYABLES_DISB';
2301 l_rec.Party_Id :=ext_bank_acct_rec.acct_owner_party_id;
2302 l_assign.Instrument.Instrument_Type := 'BANKACCOUNT';
2303 l_assign.Instrument.Instrument_Id := l_dup_acct_id;
2304
2305 /* API call to assing the bank account to the Payee*/
2306 IBY_DISBURSEMENT_SETUP_PUB.Set_Payee_Instr_Assignment(
2307 p_api_version => p_api_version,
2308 p_init_msg_list => 'F',
2309 p_commit => NULL,
2310 x_return_status => x_return_status,
2311 x_msg_count => x_msg_count,
2312 x_msg_data => x_msg_data,
2313 p_payee => l_rec,
2314 p_assignment_attribs => l_assign,
2315 x_assign_id => l_assign_id,
2316 x_response => x_response);
2317
2318 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2319 print_debuginfo(l_module_name, 'After the call to Set_Payee_Instr_Assignment');
2320 print_debuginfo(l_module_name, 'Assignment Id::'||l_assign_id);
2321 print_debuginfo(l_module_name, 'X_RETURN_STATUS::'||x_return_status);
2322 END IF;
2323
2324 /*ELSE
2325 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2326 FND_MESSAGE.SET_NAME('IBY','IBY_IMP_SUP_NOT_OWN');
2327 FND_MSG_PUB.ADD;
2328 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2329 print_debuginfo(l_module_name, 'Party is not owner of the account');
2330 END IF;
2331
2332 END IF;*/
2333 ELSE
2334 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2335 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2336 print_debuginfo(l_module_name, 'check_ext_acct_exist returned inconsistent data');
2337 END IF;
2338 END IF;
2339
2340 IF( x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
2341
2342 -- Bug# 7451534 begin
2343
2344 intermediate_bank_acct_rec.bank_account_id := x_bank_acc_id;
2345 intermediate_bank_acct_rec.object_version_number :=1;
2346 -- Calling Create_intermediary_acct to create record for Intermediary Bank Account1
2347 IBY_EXT_BANKACCT_PUB.create_intermediary_acct (
2348 p_api_version => 1.0,
2349 p_init_msg_list => 'F',
2350 p_intermed_acct_rec=>intermediate_bank_acct_rec,
2351 x_intermediary_acct_id => x_intmediary_bank_acct_id,
2352 x_return_status => x_intermediary_return_status,
2353 x_msg_count => x_intermediary_msg_count,
2354 x_msg_data => x_intermediary_msg_data,
2355 x_response => x_intermediary_response
2356 );
2357 -- Calling Create_intermediary_acct to create record for Intermediary Bank Account2
2358 IBY_EXT_BANKACCT_PUB.create_intermediary_acct (
2359 p_api_version => 1.0,
2360 p_init_msg_list => 'F',
2361 p_intermed_acct_rec=>intermediate_bank_acct_rec,
2362 x_intermediary_acct_id => x_intmediary_bank_acct_id,
2363 x_return_status => x_intermediary_return_status,
2364 x_msg_count => x_intermediary_msg_count,
2365 x_msg_data => x_intermediary_msg_data,
2366 x_response => x_intermediary_response
2367 );
2368 -- Bug# 7451534 Online modification to test End
2369 -- End of API body
2370 END IF;
2371
2372 -- get message count and if count is 1, get message info.
2373 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2374 p_count => x_msg_count,
2375 p_data => x_msg_data);
2376
2377 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2378 print_debuginfo(l_module_name, 'EXIT');
2379 END IF;
2380 EXCEPTION
2381 WHEN fnd_api.g_exc_error THEN
2382 ROLLBACK TO Create_Temp_Ext_Bank_Acct_pub;
2383 x_return_status := fnd_api.g_ret_sts_error;
2384 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2385 p_count => x_msg_count,
2386 p_data => x_msg_data);
2387
2388
2389 WHEN fnd_api.g_exc_unexpected_error THEN
2390 ROLLBACK TO Create_Temp_Ext_Bank_Acct_pub;
2391 x_return_status := fnd_api.g_ret_sts_unexp_error;
2392 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2393 p_count => x_msg_count,
2394 p_data => x_msg_data);
2395
2396
2397 WHEN OTHERS THEN
2398 ROLLBACK TO Create_Temp_Ext_Bank_Acct_pub;
2399 x_return_status := fnd_api.g_ret_sts_unexp_error;
2400 fnd_message.set_name('IBY', 'IBY_API_OTHERS_EXCEP');
2401 fnd_message.set_token('ERROR',SQLERRM);
2402 fnd_msg_pub.add;
2403 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2404 p_count => x_msg_count,
2405 p_data => x_msg_data);
2406
2407 END Create_Temp_Ext_Bank_Acct;
2408
2409
2410
2411
2412
2413
2414 -- Validate_Temp_Ext_Bank_Acct
2415 --
2416 -- API name : Validate_Temp_Ext_Bank_Acct
2417 -- Type : Public
2418 -- Pre-reqs : None
2419 -- Function : Validate_Temp_Ext_Bank_Acct
2420 -- Current version : 1.0
2421 -- Previous version: 1.0
2422 -- Initial version : 1.0
2423
2424 PROCEDURE Validate_Temp_Ext_Bank_Acct (
2425 p_api_version IN NUMBER,
2426 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
2427 x_return_status OUT NOCOPY VARCHAR2,
2428 x_msg_count OUT NOCOPY NUMBER,
2429 x_msg_data OUT NOCOPY VARCHAR2,
2430 p_temp_ext_acct_id IN NUMBER
2431 ) IS
2432
2433 l_api_name CONSTANT VARCHAR2(30) := 'Validate_Temp_Ext_Bank_Acct';
2434 l_api_version CONSTANT NUMBER := 1.0;
2435 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.' || l_api_name;
2436
2437 CURSOR ext_bank_acct_csr(p_temp_ext_acct_id NUMBER)
2438 IS
2439 SELECT
2440 EXT_BANK_ACCOUNT_ID,
2441 COUNTRY_CODE,
2442 BRANCH_ID,
2443 BANK_ID,
2444 BANK_NAME,
2445 BANK_NUMBER,
2446 BANK_NAME_ALT,
2447 BANK_INSTITUTION_TYPE,
2448 BANK_ADDRESS_ID,
2449 BRANCH_NUMBER,
2450 BRANCH_TYPE,
2451 BRANCH_NAME,
2452 BRANCH_NAME_ALT,
2453 BIC,
2454 RFC_IDENTIFIER,
2455 BANK_CODE,
2456 BRANCH_ADDRESS_ID,
2457 ACCOUNT_OWNER_PARTY_ID,
2458 OWNER_PRIMARY_FLAG,
2459 BANK_ACCOUNT_NAME,
2460 BANK_ACCOUNT_NUM,
2461 CURRENCY_CODE,
2462 IBAN,
2463 CHECK_DIGITS,
2464 BANK_ACCOUNT_NAME_ALT,
2465 BANK_ACCOUNT_TYPE,
2466 ACCOUNT_SUFFIX,
2467 DESCRIPTION,
2468 AGENCY_LOCATION_CODE,
2469 PAYMENT_FACTOR_FLAG,
2470 FOREIGN_PAYMENT_USE_FLAG,
2471 EXCHANGE_RATE_AGREEMENT_NUM,
2472 EXCHANGE_RATE_AGREEMENT_TYPE,
2473 EXCHANGE_RATE,
2474 START_DATE,
2475 END_DATE,
2476 ATTRIBUTE_CATEGORY,
2477 NOTE,
2478 NOTE_ALT,
2479 ATTRIBUTE1,
2480 ATTRIBUTE2,
2481 ATTRIBUTE3,
2482 ATTRIBUTE4,
2483 ATTRIBUTE5,
2484 ATTRIBUTE6,
2485 ATTRIBUTE7,
2486 ATTRIBUTE8,
2487 ATTRIBUTE9,
2488 ATTRIBUTE10,
2489 ATTRIBUTE11,
2490 ATTRIBUTE12,
2491 ATTRIBUTE13,
2492 ATTRIBUTE14,
2493 ATTRIBUTE15,
2494 STATUS,
2495 LAST_UPDATE_DATE,
2496 LAST_UPDATED_BY,
2497 CREATION_DATE,
2498 CREATED_BY,
2499 LAST_UPDATE_LOGIN,
2500 REQUEST_ID,
2501 PROGRAM_APPLICATION_ID,
2502 PROGRAM_ID,
2503 PROGRAM_UPDATE_DATE,
2504 OBJECT_VERSION_NUMBER,
2505 CALLING_APP_UNIQUE_REF1,
2506 CALLING_APP_UNIQUE_REF2,
2507 EXT_PAYEE_ID
2508 FROM IBY_TEMP_EXT_BANK_ACCTS
2509 WHERE TEMP_EXT_BANK_ACCT_ID = p_temp_ext_acct_id;
2510
2511 temp_ext_bank_acct_rec ext_bank_acct_csr%ROWTYPE;
2512 ext_bank_acct_rec IBY_EXT_BANKACCT_PUB.ExtBankAcct_rec_type;
2513 ext_bank_rec IBY_EXT_BANKACCT_PUB.ExtBank_rec_type;
2514 ext_bank_branch_rec IBY_EXT_BANKACCT_PUB.ExtBankBranch_rec_type;
2515 l_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
2516
2517 CURSOR ext_bank_csr(p_bank_id NUMBER)
2518 IS
2519 SELECT BANK_PARTY_ID,
2520 bank_name,
2521 bank_number,
2522 BANK_INSTITUTION_TYPE,
2523 HOME_COUNTRY,
2524 BANK_NAME_ALT,
2525 description,
2526 SHORT_BANK_NAME
2527 FROM CE_BANKS_V
2528 WHERE BANK_PARTY_ID = p_bank_id;
2529
2530 temp_ext_bank_rec ext_bank_csr%ROWTYPE;
2531
2532 CURSOR ext_bank_branch_csr(p_bank_id NUMBER,
2533 p_bank_branch_id NUMBER)
2534 IS
2535 SELECT branch_party_id,
2536 bank_party_id,
2537 BANK_BRANCH_NAME,
2538 branch_number,
2539 BANK_BRANCH_TYPE,
2540 BANK_BRANCH_NAME_ALT
2541 FROM CE_BANK_BRANCHES_V
2542 WHERE bank_party_id = p_bank_id
2543 AND branch_party_id = p_bank_branch_id;
2544
2545 temp_ext_bank_branch_rec ext_bank_branch_csr%ROWTYPE;
2546
2547 BEGIN
2548
2549 SAVEPOINT Val_Temp_Ext_Bank_Acct_pub;
2550
2551 -- Standard call to check for call compatibility.
2552 IF NOT FND_API.Compatible_API_Call(l_api_version,
2553 p_api_version,
2554 l_api_name,
2555 G_PKG_NAME) THEN
2556 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2557 END IF;
2558
2559 -- Initialize message list if p_init_msg_list is set to TRUE.
2560 IF FND_API.to_Boolean(p_init_msg_list) THEN
2561 FND_MSG_PUB.initialize;
2562 END IF;
2563
2564 -- Initialize API return status to success
2565 x_return_status := FND_API.G_RET_STS_SUCCESS;
2566
2567 -- Start of API body
2568
2569 OPEN ext_bank_acct_csr(p_temp_ext_acct_id);
2570 FETCH ext_bank_acct_csr INTO temp_ext_bank_acct_rec;
2571 CLOSE ext_bank_acct_csr;
2572
2573 ext_bank_acct_rec.country_code := temp_ext_bank_acct_rec.COUNTRY_CODE;
2574 ext_bank_acct_rec.branch_id := temp_ext_bank_acct_rec.branch_id;
2575 ext_bank_acct_rec.bank_id := temp_ext_bank_acct_rec.bank_id;
2576 ext_bank_acct_rec.acct_owner_party_id := temp_ext_bank_acct_rec.ACCOUNT_OWNER_PARTY_ID;
2577 ext_bank_acct_rec.bank_account_name := temp_ext_bank_acct_rec.bank_account_name;
2578 ext_bank_acct_rec.bank_account_num := temp_ext_bank_acct_rec.bank_account_num;
2579 ext_bank_acct_rec.currency := temp_ext_bank_acct_rec.CURRENCY_CODE;
2580 ext_bank_acct_rec.iban := temp_ext_bank_acct_rec.iban;
2581 ext_bank_acct_rec.check_digits := temp_ext_bank_acct_rec.check_digits;
2582 ext_bank_acct_rec.alternate_acct_name := temp_ext_bank_acct_rec.BANK_ACCOUNT_NAME_ALT;
2583 ext_bank_acct_rec.acct_type := temp_ext_bank_acct_rec.BANK_ACCOUNT_TYPE;
2584 ext_bank_acct_rec.acct_suffix := temp_ext_bank_acct_rec.ACCOUNT_SUFFIX;
2585 ext_bank_acct_rec.description := temp_ext_bank_acct_rec.description;
2586 ext_bank_acct_rec.agency_location_code := temp_ext_bank_acct_rec.agency_location_code;
2587 ext_bank_acct_rec.foreign_payment_use_flag := temp_ext_bank_acct_rec.foreign_payment_use_flag;
2588 ext_bank_acct_rec.exchange_rate_agreement_num := temp_ext_bank_acct_rec.exchange_rate_agreement_num;
2589 ext_bank_acct_rec.exchange_rate_agreement_type := temp_ext_bank_acct_rec.exchange_rate_agreement_type;
2590 ext_bank_acct_rec.exchange_rate := temp_ext_bank_acct_rec.exchange_rate;
2591 ext_bank_acct_rec.payment_factor_flag := temp_ext_bank_acct_rec.payment_factor_flag;
2592 ext_bank_acct_rec.end_date := temp_ext_bank_acct_rec.end_date;
2593 ext_bank_acct_rec.START_DATE := temp_ext_bank_acct_rec.START_DATE;
2594 ext_bank_acct_rec.attribute_category := temp_ext_bank_acct_rec.attribute_category;
2595 ext_bank_acct_rec.attribute1 := temp_ext_bank_acct_rec.attribute1;
2596 ext_bank_acct_rec.attribute2 := temp_ext_bank_acct_rec.attribute2;
2597 ext_bank_acct_rec.attribute3 := temp_ext_bank_acct_rec.attribute3;
2598 ext_bank_acct_rec.attribute4 := temp_ext_bank_acct_rec.attribute4;
2599 ext_bank_acct_rec.attribute5 := temp_ext_bank_acct_rec.attribute5;
2600 ext_bank_acct_rec.attribute6 := temp_ext_bank_acct_rec.attribute6;
2601 ext_bank_acct_rec.attribute7 := temp_ext_bank_acct_rec.attribute7;
2602 ext_bank_acct_rec.attribute8 := temp_ext_bank_acct_rec.attribute8;
2603 ext_bank_acct_rec.attribute9 := temp_ext_bank_acct_rec.attribute9;
2604 ext_bank_acct_rec.attribute10 := temp_ext_bank_acct_rec.attribute10;
2605 ext_bank_acct_rec.attribute11 := temp_ext_bank_acct_rec.attribute11;
2606 ext_bank_acct_rec.attribute12 := temp_ext_bank_acct_rec.attribute12;
2607 ext_bank_acct_rec.attribute13 := temp_ext_bank_acct_rec.attribute13;
2608 ext_bank_acct_rec.attribute14 := temp_ext_bank_acct_rec.attribute14;
2609 ext_bank_acct_rec.attribute15 := temp_ext_bank_acct_rec.attribute15;
2610
2611 OPEN ext_bank_csr(temp_ext_bank_acct_rec.bank_id);
2612 FETCH ext_bank_csr INTO temp_ext_bank_rec;
2613 CLOSE ext_bank_csr;
2614
2615 -- Populate the external bank branch record
2616 ext_bank_rec.bank_id := temp_ext_bank_rec.BANK_PARTY_ID;
2617 ext_bank_rec.bank_name := temp_ext_bank_rec.bank_name;
2618 ext_bank_rec.bank_number := temp_ext_bank_rec.bank_number;
2619 ext_bank_rec.institution_type := temp_ext_bank_rec.BANK_INSTITUTION_TYPE;
2620 ext_bank_rec.country_code := temp_ext_bank_rec.HOME_COUNTRY;
2621 ext_bank_rec.bank_alt_name := temp_ext_bank_rec.BANK_NAME_ALT;
2622 ext_bank_rec.bank_short_name := temp_ext_bank_rec.SHORT_BANK_NAME;
2623 ext_bank_rec.description := temp_ext_bank_rec.description;
2624
2625 OPEN ext_bank_branch_csr(temp_ext_bank_acct_rec.bank_id,
2626 temp_ext_bank_acct_rec.branch_id);
2627 FETCH ext_bank_branch_csr INTO temp_ext_bank_branch_rec;
2628 CLOSE ext_bank_branch_csr;
2629
2630 -- Populate the external bank branch record
2631 ext_bank_branch_rec.branch_party_id := temp_ext_bank_branch_rec.branch_party_id;
2632 ext_bank_branch_rec.bank_party_id := temp_ext_bank_branch_rec.bank_party_id;
2633 ext_bank_branch_rec.branch_name := temp_ext_bank_branch_rec.BANK_BRANCH_NAME;
2634 ext_bank_branch_rec.branch_number := temp_ext_bank_branch_rec.branch_number;
2635 ext_bank_branch_rec.branch_type := temp_ext_bank_branch_rec.BANK_BRANCH_TYPE;
2636 ext_bank_branch_rec.alternate_branch_name := temp_ext_bank_branch_rec.BANK_BRANCH_NAME_ALT;
2637
2638
2639 -- Call Validations
2640 IBY_EXT_BANKACCT_VALIDATIONS.iby_validate_account(
2641 p_api_version => p_api_version,
2642 p_init_msg_list => FND_API.G_TRUE,
2643 p_create_flag => FND_API.G_TRUE,
2644 p_ext_bank_rec => ext_bank_rec,
2645 p_ext_bank_branch_rec => ext_bank_branch_rec,
2646 p_ext_bank_acct_rec => ext_bank_acct_rec,
2647 x_return_status => x_return_status,
2648 x_msg_count => x_msg_count,
2649 x_msg_data => x_msg_data,
2650 x_response => l_response
2651 );
2652
2653 IF (fnd_msg_pub.count_msg > 0) THEN
2654 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2655 END IF;
2656
2657 -- End of API body
2658
2659 -- get message count and if count is 1, get message info.
2660 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2661 p_count => x_msg_count,
2662 p_data => x_msg_data);
2663
2664
2665 EXCEPTION
2666 WHEN fnd_api.g_exc_error THEN
2667 ROLLBACK TO Val_Temp_Ext_Bank_Acct_pub;
2668 x_return_status := fnd_api.g_ret_sts_error;
2669 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2670 p_count => x_msg_count,
2671 p_data => x_msg_data);
2672
2673
2674 WHEN fnd_api.g_exc_unexpected_error THEN
2675 ROLLBACK TO Val_Temp_Ext_Bank_Acct_pub;
2676 x_return_status := fnd_api.g_ret_sts_unexp_error;
2677 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2678 p_count => x_msg_count,
2679 p_data => x_msg_data);
2680
2681
2682 WHEN OTHERS THEN
2683 ROLLBACK TO Val_Temp_Ext_Bank_Acct_pub;
2684 x_return_status := fnd_api.g_ret_sts_unexp_error;
2685 fnd_message.set_name('IBY', 'IBY_API_OTHERS_EXCEP');
2686 fnd_message.set_token('ERROR',SQLERRM);
2687 fnd_msg_pub.add;
2688 fnd_msg_pub.count_and_get(p_encoded => fnd_api.g_false,
2689 p_count => x_msg_count,
2690 p_data => x_msg_data);
2691
2692 END Validate_Temp_Ext_Bank_Acct;
2693
2694 -- Public API
2695
2696 -- Start of comments
2697 -- API name : Update_External_Payee
2698 -- Type : Public
2699 -- Pre-reqs : None
2700 -- Function : Update payees for records passed in through the payee PL/SQL table
2701 -- Parameters :
2702 -- IN : p_api_version IN NUMBER Required
2703 -- p_init_msg_list IN VARCHAR2 Optional
2704 -- p_ext_payee_tab IN External_Payee_Tab_Type Required
2705 -- OUT : x_return_status OUT VARCHAR2 Required
2706 -- x_msg_count OUT NUMBER Required
2707 -- x_msg_data OUT VARCHAR2 Required
2708 -- x_ext_payee_id_tab OUT Ext_Payee_ID_Tab_Type
2709 -- x_ext_payee_status_tab OUT Ext_Payee_Create_Tab_Type Required
2710 --
2711 -- Version : Current version 1.0
2712 -- Previous version None
2713 -- Initial version 1.0
2714 -- End of comments
2715
2716 PROCEDURE Update_External_Payee (
2717 p_api_version IN NUMBER,
2718 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
2719 p_ext_payee_tab IN External_Payee_Tab_Type,
2720 p_ext_payee_id_tab IN Ext_Payee_ID_Tab_Type,
2721 x_return_status OUT NOCOPY VARCHAR2,
2722 x_msg_count OUT NOCOPY NUMBER,
2723 x_msg_data OUT NOCOPY VARCHAR2,
2724 x_ext_payee_status_tab OUT NOCOPY Ext_Payee_Update_Tab_Type) IS
2725
2726 l_api_name CONSTANT VARCHAR2(30) := 'Create_External_Payee';
2727 l_api_version CONSTANT NUMBER := 1.0;
2728 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Create_External_Payee';
2729
2730 counter NUMBER;
2731 l_payee_cnt NUMBER;
2732 l_payee_id NUMBER;
2733 l_pm_count NUMBER;
2734 l_message FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
2735 l_primary_flag iby_ext_party_pmt_mthds.primary_flag%TYPE;
2736
2737 l_ext_payee_upd_rec Ext_Payee_Update_Rec_Type;
2738 l_payee_upd_status VARCHAR2(30);
2739
2740 CURSOR external_payee_csr(p_payee_party_id NUMBER,
2741 p_party_site_id NUMBER,
2742 p_supplier_site_id NUMBER,
2743 p_payer_org_id NUMBER,
2744 p_payer_org_type VARCHAR2,
2745 p_payment_function VARCHAR2)
2746 IS
2747 SELECT count(payee.EXT_PAYEE_ID), max(payee.EXT_PAYEE_ID)
2748 FROM iby_external_payees_all payee
2749 WHERE payee.PAYEE_PARTY_ID = p_payee_party_id
2750 AND payee.PAYMENT_FUNCTION = p_payment_function
2751 AND ((p_party_site_id is NULL and payee.PARTY_SITE_ID is NULL) OR
2752 (payee.PARTY_SITE_ID = p_party_site_id))
2753 AND ((p_supplier_site_id is NULL and payee.SUPPLIER_SITE_ID is NULL) OR
2754 (payee.SUPPLIER_SITE_ID = p_supplier_site_id))
2755 AND ((p_payer_org_id is NULL and payee.ORG_ID is NULL) OR
2756 (payee.ORG_ID = p_payer_org_id AND payee.ORG_TYPE = p_payer_org_type));
2757
2758 BEGIN
2759 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2760 print_debuginfo(l_module_name, 'ENTER');
2761
2762 END IF;
2763 -- Standard call to check for call compatibility.
2764 IF NOT FND_API.Compatible_API_Call(l_api_version,
2765 p_api_version,
2766 l_api_name,
2767 G_PKG_NAME) THEN
2768 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2769 END IF;
2770
2771 -- Initialize message list if p_init_msg_list is set to TRUE.
2772 IF FND_API.to_Boolean(p_init_msg_list) THEN
2773 FND_MSG_PUB.initialize;
2774 END IF;
2775
2776 -- Initialize API return status to success
2777 x_return_status := FND_API.G_RET_STS_SUCCESS;
2778
2779 IF p_ext_payee_tab.COUNT > 0 THEN
2780 counter := p_ext_payee_tab.FIRST;
2781
2782 while (counter <= p_ext_payee_tab.LAST) loop
2783 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2784 print_debuginfo(l_module_name, 'Loop thru external payee ' || counter);
2785
2786 END IF;
2787 IF p_ext_payee_id_tab(counter).Ext_Payee_ID IS NULL THEN
2788 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2789 print_debuginfo(l_module_name,'Payee to update does not exist.');
2790 END IF;
2791 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
2792 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_EXT_PAYEE_ID'));
2793 l_message := fnd_message.get;
2794 FND_MSG_PUB.Add;
2795
2796 l_ext_payee_upd_rec.Payee_Update_Status := 'E';
2797 l_ext_payee_upd_rec.Payee_Update_Msg := l_message;
2798
2799 x_return_status := FND_API.G_RET_STS_ERROR;
2800
2801 ELSIF p_ext_payee_tab(counter).Payee_Party_Id IS NULL THEN
2802 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2803 print_debuginfo(l_module_name,'Payee party Id is null.');
2804 END IF;
2805 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
2806 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_PAYEE_PARTY_ID_FIELD'));
2807 l_message := fnd_message.get;
2808 FND_MSG_PUB.Add;
2809
2810 l_ext_payee_upd_rec.Payee_Update_Status := 'E';
2811 l_ext_payee_upd_rec.Payee_Update_Msg := l_message;
2812
2813 x_return_status := FND_API.G_RET_STS_ERROR;
2814
2815 ELSIF (p_ext_payee_tab(counter).Payment_Function IS NULL) THEN
2816 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2817 print_debuginfo(l_module_name,'Payment function is null.');
2818 END IF;
2819 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
2820 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_FD_PPP_GRP_PMT_T_PF'));
2821 l_message := fnd_message.get;
2822 FND_MSG_PUB.Add;
2823
2824 l_ext_payee_upd_rec.Payee_Update_Status := 'E';
2825 l_ext_payee_upd_rec.Payee_Update_Msg := l_message;
2826
2827 x_return_status := FND_API.G_RET_STS_ERROR;
2828
2829 -- orgid is required if supplier site id passed
2830 ELSIF ((p_ext_payee_tab(counter).Payer_ORG_ID IS NULL) and
2831 (p_ext_payee_tab(counter).Supplier_Site_Id IS NOT NULL)) THEN
2832 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2833 print_debuginfo(l_module_name,'Payer Org Id is null.');
2834 END IF;
2835 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
2836 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_PAYER_ORG_ID_FIELD'));
2837 l_message := fnd_message.get;
2838 FND_MSG_PUB.Add;
2839
2840 l_ext_payee_upd_rec.Payee_Update_Status := 'E';
2841 l_ext_payee_upd_rec.Payee_Update_Msg := l_message;
2842
2843 ELSIF ((p_ext_payee_tab(counter).Payer_ORG_ID IS NOT NULL) and
2844 (p_ext_payee_tab(counter).Payer_Org_Type IS NULL)) THEN
2845 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2846 print_debuginfo(l_module_name,'Payer Org Id is null.');
2847 END IF;
2848 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
2849 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_PAYER_ORG_TYPE_FIELD'));
2850 l_message := fnd_message.get;
2851 FND_MSG_PUB.Add;
2852
2853 l_ext_payee_upd_rec.Payee_Update_Status := 'E';
2854 l_ext_payee_upd_rec.Payee_Update_Msg := l_message;
2855
2856 ELSIF p_ext_payee_tab(counter).Exclusive_Pay_Flag IS NULL THEN
2857 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2858 print_debuginfo(l_module_name,'Exclusive payment flag is null.');
2859 END IF;
2860 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
2861 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_EXCL_PMT_FLAG_FIELD'));
2862 l_message := fnd_message.get;
2863 FND_MSG_PUB.Add;
2864
2865 l_ext_payee_upd_rec.Payee_Update_Status := 'E';
2866 l_ext_payee_upd_rec.Payee_Update_Msg := l_message;
2867
2868 x_return_status := FND_API.G_RET_STS_ERROR;
2869
2870 ELSE
2871 OPEN external_payee_csr(p_ext_payee_tab(counter).Payee_Party_Id,
2872 p_ext_payee_tab(counter).Payee_Party_Site_Id,
2873 p_ext_payee_tab(counter).Supplier_Site_Id,
2874 p_ext_payee_tab(counter).Payer_Org_Id,
2875 p_ext_payee_tab(counter).Payer_Org_Type,
2876 p_ext_payee_tab(counter).Payment_Function);
2877 FETCH external_payee_csr INTO l_payee_cnt, l_payee_id;
2878 CLOSE external_payee_csr;
2879
2880 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2881 print_debuginfo(l_module_name, 'Payee count is ' || l_payee_cnt);
2882 print_debuginfo(l_module_name, 'Payee Id is ' || l_payee_id);
2883
2884 END IF;
2885 IF (l_payee_cnt = 0 OR l_payee_id <> p_ext_payee_id_tab(counter).ext_payee_id) THEN
2886
2887 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2888 print_debuginfo(l_module_name,'Payee id does not exist based on parameters or is different from'||
2889 'parameter ext_payee_id');
2890 END IF;
2891 FND_MESSAGE.set_name('IBY', 'IBY_EXT_PAYEE_NOT_EXIST');
2892 FND_MESSAGE.SET_TOKEN('EXT_PAYEE_ID', p_ext_payee_id_tab(counter).ext_payee_id);
2893 l_message := fnd_message.get;
2894 FND_MSG_PUB.Add;
2895
2896 l_ext_payee_upd_rec.Payee_Update_Status := 'E';
2897 l_ext_payee_upd_rec.Payee_Update_Msg := l_message;
2898
2899 x_return_status := FND_API.G_RET_STS_ERROR;
2900
2901 ELSE
2902 -- update external payee
2903 UPDATE iby_external_payees_all
2904 SET exclusive_payment_flag = p_ext_payee_tab(counter).exclusive_pay_flag,
2905 last_updated_by = fnd_global.user_id,
2906 last_update_date = SYSDATE, -- bug 13881024
2907 last_update_login = fnd_global.user_id,
2908 object_version_number = object_version_number+1,
2909 default_payment_method_code = p_ext_payee_tab(counter).Default_Pmt_method,
2910 ece_tp_location_code = p_ext_payee_tab(counter).ece_tp_loc_code,
2911 bank_charge_bearer = p_ext_payee_tab(counter).Bank_Charge_Bearer,
2912 bank_instruction1_code = p_ext_payee_tab(counter).Bank_Instr1_Code,
2913 bank_instruction2_code = p_ext_payee_tab(counter).Bank_Instr2_Code,
2914 bank_instruction_details = p_ext_payee_tab(counter).Bank_Instr_Detail,
2915 payment_reason_code = p_ext_payee_tab(counter).Pay_Reason_Code,
2916 payment_reason_comments = p_ext_payee_tab(counter).Pay_Reason_Com,
2917 inactive_date = p_ext_payee_tab(counter).Inactive_Date,
2918 payment_text_message1 = p_ext_payee_tab(counter).Pay_Message1,
2919 payment_text_message2 = p_ext_payee_tab(counter).Pay_Message2,
2920 payment_text_message3 = p_ext_payee_tab(counter).Pay_Message3,
2921 delivery_channel_code = p_ext_payee_tab(counter).Delivery_Channel,
2922 payment_format_code = p_ext_payee_tab(counter).Pmt_Format,
2923 settlement_priority = p_ext_payee_tab(counter).Settlement_Priority,
2924 remit_advice_email = p_ext_payee_tab(counter).Remit_advice_email,
2925 remit_advice_delivery_method = p_ext_payee_tab(counter).Remit_advice_delivery_method,
2926 remit_advice_fax = p_ext_payee_tab(counter).remit_advice_fax
2927 WHERE ext_payee_id = p_ext_payee_id_tab(counter).ext_payee_id;
2928
2929 -- update default payment method
2930 IF(p_ext_payee_tab(counter).Default_Pmt_method IS NULL) THEN
2931 BEGIN
2932 UPDATE iby_ext_party_pmt_mthds
2933 SET primary_flag = 'N',
2934 last_update_date = SYSDATE,
2935 last_updated_by = fnd_global.user_id,
2936 last_update_login = fnd_global.user_id,
2937 object_version_number = object_version_number+1
2938 WHERE ext_pmt_party_id = p_ext_payee_id_tab(counter).ext_payee_id
2939 AND payment_function = p_ext_payee_tab(counter).payment_function
2940 AND primary_flag = 'Y';
2941 EXCEPTION
2942 WHEN OTHERS THEN NULL;
2943 END;
2944
2945 ELSE
2946 -- default payment method is not null
2947 SELECT COUNT(1)
2948 INTO l_pm_count
2949 FROM iby_payment_methods_b
2950 WHERE payment_method_code = p_ext_payee_tab(counter).Default_Pmt_method;
2951
2952 IF (l_pm_count>0) THEN
2953 -- payment method exists
2954 BEGIN
2955 SELECT primary_flag
2956 INTO l_primary_flag
2957 FROM iby_ext_party_pmt_mthds
2958 WHERE ext_pmt_party_id = p_ext_payee_id_tab(counter).ext_payee_id
2959 AND payment_function = p_ext_payee_tab(counter).payment_function
2960 AND payment_method_code=p_ext_payee_tab(counter).Default_Pmt_method;
2961
2962 EXCEPTION
2963 WHEN no_data_found THEN
2964 INSERT INTO IBY_EXT_PARTY_PMT_MTHDS
2965 (EXT_PARTY_PMT_MTHD_ID,
2966 PAYMENT_METHOD_CODE,
2967 PAYMENT_FLOW,
2968 EXT_PMT_PARTY_ID,
2969 PAYMENT_FUNCTION,
2970 PRIMARY_FLAG,
2971 CREATED_BY,
2972 CREATION_DATE,
2973 LAST_UPDATED_BY,
2974 LAST_UPDATE_DATE,
2975 LAST_UPDATE_LOGIN,
2976 OBJECT_VERSION_NUMBER
2977 ) VALUES (
2978 IBY_EXT_PARTY_PMT_MTHDS_S.nextval,
2979 p_ext_payee_tab(counter).Default_Pmt_method,
2980 'DISBURSEMENTS',
2981 p_ext_payee_id_tab(counter).ext_payee_id,
2982 p_ext_payee_tab(counter).Payment_function,
2983 'Y',
2984 fnd_global.user_id,
2985 SYSDATE, -- bug 13881024
2986 fnd_global.user_id,
2987 SYSDATE,
2988 fnd_global.user_id,
2989 1.0
2990 );
2991
2992 END;
2993
2994 -- update primary_flag for all rows.
2995 BEGIN
2996 UPDATE iby_ext_party_pmt_mthds
2997 SET primary_flag = DECODE(payment_method_code,
2998 p_ext_payee_tab(counter).Default_Pmt_method, 'Y', 'N'),
2999 last_update_date = SYSDATE, -- bug 13881024
3000 last_updated_by = fnd_global.user_id,
3001 last_update_login = fnd_global.user_id,
3002 object_version_number = object_version_number+1
3003 WHERE ext_pmt_party_id = p_ext_payee_id_tab(counter).ext_payee_id
3004 AND payment_function = p_ext_payee_tab(counter).payment_function;
3005 EXCEPTION
3006 WHEN OTHERS THEN NULL;
3007 END;
3008 END IF; --payment method exists
3009
3010 END IF; -- default payment method is not null
3011 l_ext_payee_upd_rec.Payee_Update_Status := 'S';
3012
3013 END IF;
3014 END IF;
3015
3016 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3017 print_debuginfo(l_module_name, 'External payee Id is '||p_ext_payee_id_tab(counter).ext_payee_id);
3018 print_debuginfo(l_module_name, 'Creation status is ' || l_ext_payee_upd_rec.Payee_Update_Status);
3019 print_debuginfo(l_module_name, '------------------------------');
3020
3021 END IF;
3022 x_ext_payee_status_tab(counter) := l_ext_payee_upd_rec;
3023
3024 counter := counter + 1;
3025
3026 END LOOP;
3027 END IF;
3028 -- End of API body.
3029
3030 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3031 print_debuginfo(l_module_name, 'End of external payee loop.');
3032 END IF;
3033 -- Standard call to get message count and if count is 1, get message info.
3034 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3035 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3036 print_debuginfo(l_module_name, 'RETURN');
3037
3038 END IF;
3039 EXCEPTION
3040 WHEN FND_API.G_EXC_ERROR THEN
3041 x_return_status := FND_API.G_RET_STS_ERROR;
3042
3043 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3044 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3045 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
3046 print_debuginfo(l_module_name,'SQLerr is :'|| substr(SQLERRM, 1, 150));
3047
3048 END IF;
3049 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3050 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3051
3052 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3053 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3054 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
3055 print_debuginfo(l_module_name,'SQLerr is :'|| substr(SQLERRM, 1, 150));
3056
3057 END IF;
3058 WHEN OTHERS THEN
3059 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3060
3061 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
3062 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3063 END IF;
3064 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3065 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
3066 print_debuginfo(l_module_name,'SQLerr is :'|| substr(SQLERRM, 1, 150));
3067 END IF;
3068 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3069
3070 END Update_External_Payee;
3071
3072
3073
3074 END IBY_DISBURSEMENT_SETUP_PUB;