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