1 PACKAGE BODY IBY_DISBURSEMENT_COMP_PUB AS
2 /*$Header: ibydiscb.pls 120.45 2011/08/10 12:08:39 gmaheswa ship $*/
3
4 --
5 -- Declare Global variables
6 --
7 G_CURRENT_RUNTIME_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
8 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
9 -- User Defined Exceptions
10 g_abort_program EXCEPTION;
11
12 -- Lookups for profile applicablility types (from IBY_PMT_PROF_LOV_APL_TYPES)
13 APL_TYPE_PAYER_ORG CONSTANT VARCHAR2(100) := 'PAYER_ORG';
14 -- APL_TYPE_ORG_ID CONSTANT VARCHAR2(100) := 'PAYER_ORG_ID';
15 -- APL_TYPE_ORG_TYPE CONSTANT VARCHAR2(100) := 'PAYER_ORG_TYPE';
16 APL_TYPE_PMT_METHOD CONSTANT VARCHAR2(100) := 'PAYMENT_METHOD';
17 APL_TYPE_PMT_FORMAT CONSTANT VARCHAR2(100) := 'PAYMENT_FORMAT';
18 APL_TYPE_PMT_CURRENCY CONSTANT VARCHAR2(100) := 'CURRENCY_CODE';
19 APL_TYPE_INT_BANK_ACCT CONSTANT VARCHAR2(100) := 'INTERNAL_BANK_ACCOUNT';
20
21 TYPE t_payee_bankacct_rec_type IS RECORD(
22 Payee_Party_Id IBY_EXTERNAL_PAYEES_ALL.payee_party_id%TYPE,
23 Payee_Party_Site_Id IBY_EXTERNAL_PAYEES_ALL.party_site_id%TYPE,
24 Supplier_Site_Id IBY_EXTERNAL_PAYEES_ALL.supplier_site_id%TYPE,
25 Payee_Org_Id IBY_EXTERNAL_PAYEES_ALL.org_id%TYPE,
26 Payee_Org_Type IBY_EXTERNAL_PAYEES_ALL.org_type%TYPE,
27 Payment_Currency IBY_DOCS_PAYABLE_ALL.payment_currency_code%TYPE,
28 Payment_Function IBY_EXTERNAL_PAYEES_ALL.payment_function%TYPE,
29 Payee_BankAccount_Name IBY_EXT_BANK_ACCOUNTS_V.bank_account_name%TYPE,
30 Payee_BankAccount_Id IBY_EXT_BANK_ACCOUNTS_V.ext_bank_account_id%TYPE,
31 Payee_BankAccount_Num IBY_EXT_BANK_ACCOUNTS_V.bank_account_number%TYPE,
32 Currency_Code IBY_EXT_BANK_ACCOUNTS_V.CURRENCY_CODE%TYPE,
33 IBAN IBY_EXT_BANK_ACCOUNTS_V.IBAN_NUMBER%TYPE,
34 Payee_BankName IBY_EXT_BANK_ACCOUNTS_V.BANK_NAME%TYPE,
35 Payee_BankNumber IBY_EXT_BANK_ACCOUNTS_V.BANK_NUMBER%TYPE,
36 Payee_BranchName IBY_EXT_BANK_ACCOUNTS_V.BANK_BRANCH_NAME%TYPE,
37 Payee_BranchNumber IBY_EXT_BANK_ACCOUNTS_V.BRANCH_NUMBER%TYPE,
38 Bank_Country IBY_EXT_BANK_ACCOUNTS_V.COUNTRY_CODE%TYPE,
39 Alter_BankAccount_Name IBY_EXT_BANK_ACCOUNTS_V.ALTERNATE_ACCOUNT_NAME%TYPE,
40 BankAccount_Type IBY_EXT_BANK_ACCOUNTS_V.BANK_ACCOUNT_TYPE%TYPE,
41 BankAccount_Suffix IBY_EXT_BANK_ACCOUNTS_V.ACCOUNT_SUFFIX%TYPE,
42 BankAccount_Desc IBY_EXT_BANK_ACCOUNTS_V.DESCRIPTION%TYPE,
43 Foreign_PayUse_Flag IBY_EXT_BANK_ACCOUNTS_V.FOREIGN_PAYMENT_USE_FLAG%TYPE,
44 Pay_Factor_Flag IBY_EXT_BANK_ACCOUNTS_V.PAYMENT_FACTOR_FLAG%TYPE,
45 EFT_Swift_Code IBY_EXT_BANK_ACCOUNTS_V.EFT_SWIFT_CODE%TYPE
46 );
47 TYPE t_payee_bankacct_tbl_type IS TABLE OF t_payee_bankacct_rec_type INDEX BY VARCHAR2(2000);
48 t_payee_bankacct_tbl t_payee_bankacct_tbl_type;
49
50 --
51 -- Forward Declarations
52 --
53 PROCEDURE print_debuginfo(p_module IN VARCHAR2,
54 p_debug_text IN VARCHAR2);
55
56 FUNCTION ifelse(p_bool IN BOOLEAN,
57 x_true IN VARCHAR2,
58 x_false IN VARCHAR2)
59 RETURN VARCHAR2;
60
61 PROCEDURE evaluate_Rule_Based_Default(
62 p_trxn_attributes IN Trxn_Attributes_Rec_Type,
63 x_pmt_method_rec IN OUT NOCOPY Payment_Method_Rec_Type);
64
65
66 -- Start of comments
67 -- API name : Get_Applicable_Delivery_Channels
68 -- Type : Public
69 -- Pre-reqs : None.
70 -- Function : get the list of applicable Delivery Channels.
71 -- Parameters :
72 -- IN : p_api_version IN NUMBER Required
73 -- p_init_msg_list IN VARCHAR2 Optional
74 -- p_trxn_attributes_rec IN Trxn_Attributes_Rec_Type Required
75 -- OUT : x_return_status OUT VARCHAR2 Required
76 -- x_msg_count OUT NUMBER Required
77 -- x_msg_data OUT VARCHAR2 Required
78 -- x_delivery_channels_tbl OUT Delivery_Channels_Tab_Type Required
79 --
80 -- Version : Current version 1.0
81 -- Previous version None
82 -- Initial version 1.0
83 -- End of comments
84
85 PROCEDURE Get_Appl_Delivery_Channels (
86 p_api_version IN NUMBER,
87 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
88 p_trxn_attributes_rec IN Trxn_Attributes_Rec_Type,
89 x_return_status OUT NOCOPY VARCHAR2,
90 x_msg_count OUT NOCOPY NUMBER,
91 x_msg_data OUT NOCOPY VARCHAR2,
92 x_delivery_channels_tbl OUT NOCOPY Delivery_Channel_Tab_Type
93 )
94 IS
95
96 l_api_name CONSTANT VARCHAR2(30) := 'Get_Applicable_Dlvry_Channels';
97 l_api_version CONSTANT NUMBER := 1.0;
98 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Get_Applicable_Dlvry_Channels';
99
100 l_delivery_channels_tbl Delivery_Channel_Tab_Type;
101 l_payer_country VARCHAR2(35);
102
103 CURSOR delivery_channels_csr(p_payer_country VARCHAR2)
104 IS
105 SELECT delivery_channel_code,
106 meaning,
107 description,
108 territory_code
109 FROM IBY_DELIVERY_CHANNELS_VL ibydlv
110 WHERE (ibydlv.territory_code = p_payer_country OR ibydlv.territory_code is NULL)
111 AND (ibydlv.inactive_date is NULL OR ibydlv.inactive_date >= trunc(sysdate));
112
113 CURSOR payer_country_csr(p_payer_le_id NUMBER)
114 IS
115 SELECT xle.country
116 FROM XLE_FIRSTPARTY_INFORMATION_V xle
117 WHERE xle.legal_entity_id = p_payer_le_id;
118
119 BEGIN
120
121 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
122 print_debuginfo(l_module_name, 'ENTER');
123 print_debuginfo(l_module_name,'Application_id : '|| p_trxn_attributes_rec.application_id);
124 print_debuginfo(l_module_name,'First party LE id : '|| p_trxn_attributes_rec.payer_legal_entity_id);
125
126 END IF;
127 -- Standard call to check for call compatibility.
128 IF NOT FND_API.Compatible_API_Call(l_api_version,
129 p_api_version,
130 l_api_name,
131 G_PKG_NAME) THEN
132 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
133 END IF;
134
135 -- Initialize message list if p_init_msg_list is set to TRUE.
136 IF FND_API.to_Boolean(p_init_msg_list) THEN
137 FND_MSG_PUB.initialize;
138 END IF;
139
140 -- Initialize API return status to success
141 x_return_status := FND_API.G_RET_STS_SUCCESS;
142
143 IF (p_trxn_attributes_rec.payer_legal_entity_id IS NULL) THEN
144 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
145 print_debuginfo(l_module_name,'Error: Mandatory Parameter ''First party legal entity Id'' missing.');
146 END IF;
147 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
148 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_1PARTY_LE_ID'));
149 FND_MSG_PUB.Add;
150 RAISE FND_API.G_EXC_ERROR;
151 END IF;
152
153 OPEN payer_country_csr(p_trxn_attributes_rec.payer_legal_entity_id);
154 FETCH payer_country_csr INTO l_payer_country;
155 CLOSE payer_country_csr;
156
157 IF (l_payer_country IS NULL) THEN
158 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
159 print_debuginfo(l_module_name,'Error: First party legal entity country not populated.');
160 END IF;
161 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_DATA');
162 FND_MESSAGE.SET_TOKEN('PARAM', 'First party legal entity country');
163 FND_MSG_PUB.Add;
164 RAISE FND_API.G_EXC_ERROR;
165 END IF;
166
167 OPEN delivery_channels_csr(l_payer_country);
168 FETCH delivery_channels_csr BULK COLLECT INTO l_delivery_channels_tbl;
169 CLOSE delivery_channels_csr;
170
171 IF (l_delivery_channels_tbl.COUNT = 0) THEN
172 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
173 print_debuginfo(l_module_name,'Warning: No Delivery Channels Applicable');
174 END IF;
175 ELSE
176 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
177 print_debuginfo(l_module_name,'Applicable Delivery Channels Count : '|| l_delivery_channels_tbl.COUNT);
178 END IF;
179 x_delivery_channels_tbl := l_delivery_channels_tbl;
180 END IF;
181
182 -- End of API body.
183
184 -- Standard call to get message count and if count is 1, get message info.
185 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
186
187 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
188 print_debuginfo(l_module_name, 'RETURN');
189
190 END IF;
191 EXCEPTION
192 WHEN FND_API.G_EXC_ERROR THEN
193 x_return_status := FND_API.G_RET_STS_ERROR;
194 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
195 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
196 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
197 print_debuginfo(l_module_name,'SQLerr is :'
198 || substr(SQLERRM, 1, 150));
199 END IF;
200 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
201 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
202 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
203 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
204 print_debuginfo(l_module_name,'Unexpected ERROR: Exception occured during call to API ');
205 print_debuginfo(l_module_name,'SQLerr is :'
206 || substr(SQLERRM, 1, 150));
207 END IF;
208 WHEN OTHERS THEN
209 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
210
211 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
212 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
213 END IF;
214 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
215 print_debuginfo(l_module_name,'Other ERROR: Exception occured during call to API ');
216 print_debuginfo(l_module_name,'SQLerr is :'
217 || substr(SQLERRM, 1, 150));
218 END IF;
219 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
220
221 END Get_Appl_Delivery_Channels;
222
223
224 -- Start of comments
225 -- API name : Get_Applicable_Payee_BankAccts
226 -- Type : Public
227 -- Pre-reqs : None.
228 -- Function : get the list of applicable Payee Bank Accounts.
229 -- Parameters :
230 -- IN : p_api_version IN NUMBER Required
231 -- p_init_msg_list IN VARCHAR2 Optional
232 -- p_trxn_attributes_rec IN Trxn_Attributes_Rec_Type Required
233 -- OUT : x_return_status OUT VARCHAR2 Required
234 -- x_msg_count OUT NUMBER Required
235 -- x_msg_data OUT VARCHAR2 Required
236 -- x_payee_bankaccounts_tbl OUT Payee_BankAccount_Tab_Type Required
237 --
238 -- Version : Current version 1.0
239 -- Previous version None
240 -- Initial version 1.0
241 -- End of comments
242
243 PROCEDURE Get_Applicable_Payee_BankAccts (
244 p_api_version IN NUMBER,
245 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
246 p_trxn_attributes_rec IN Trxn_Attributes_Rec_Type,
247 x_return_status OUT NOCOPY VARCHAR2,
248 x_msg_count OUT NOCOPY NUMBER,
249 x_msg_data OUT NOCOPY VARCHAR2,
250 x_payee_bankaccounts_tbl OUT NOCOPY Payee_BankAccount_Tab_Type
251 )
252 IS
253
254 l_api_name CONSTANT VARCHAR2(30) := 'Get_Applicable_Payee_BankAccts';
255 l_api_version CONSTANT NUMBER := 1.0;
256 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Get_Applicable_Payee_BankAccts';
257
258 l_payee_bankaccounts_tbl Payee_BankAccount_Tab_Type;
259
260 CURSOR payee_bankacct_csr(p_payee_party_id NUMBER,
261 p_payee_party_site_id NUMBER,
262 p_supplier_site_id NUMBER,
263 p_payer_org_id NUMBER,
264 p_payer_org_type VARCHAR2,
265 p_payment_currency VARCHAR2,
266 p_payment_function VARCHAR2)
267 IS
268 SELECT DISTINCT b.bank_account_name,
269 b.ext_bank_account_id,
270 b.bank_account_number,
271 b.currency_code,
272 b.iban_number,
273 b.bank_name,
274 b.bank_number,
275 b.bank_branch_name,
276 b.branch_number,
277 b.country_code,
278 b.alternate_account_name,
279 b.bank_account_type,
280 b.account_suffix,
281 b.description,
282 b.foreign_payment_use_flag,
283 b.payment_factor_flag,
284 b.eft_swift_code
285 FROM IBY_PMT_INSTR_USES_ALL ibyu,
286 IBY_EXT_BANK_ACCOUNTS_V b,
287 IBY_EXTERNAL_PAYEES_ALL ibypayee
288 WHERE ibyu.instrument_id = b.ext_bank_account_id
289 AND ibyu.instrument_type = 'BANKACCOUNT'
290 AND (b.currency_code = p_payment_currency OR b.currency_code is null)
291 AND ibyu.ext_pmt_party_id = ibypayee.ext_payee_id
292 AND ibypayee.payment_function = p_payment_function
293 AND ibypayee.payee_party_id = p_payee_party_id
294 AND trunc(sysdate) between
295 NVL(ibyu.start_date,trunc(sysdate)) AND NVL(ibyu.end_date-1,trunc(sysdate))
296 AND trunc(sysdate) between
297 NVL(b.start_date,trunc(sysdate)) AND NVL(b.end_date-1,trunc(sysdate))
298 AND (ibypayee.party_site_id is null OR ibypayee.party_site_id = p_payee_party_site_id)
299 AND (ibypayee.supplier_site_id is null OR ibypayee.supplier_site_id = p_supplier_site_id)
300 AND (ibypayee.org_id is null OR
301 (ibypayee.org_id = p_payer_org_id AND ibypayee.org_type = p_payer_org_type));
302
303 BEGIN
304
305 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
306 print_debuginfo(l_module_name,'ENTER');
307 print_debuginfo(l_module_name,'Org Id : '|| p_trxn_attributes_rec.payer_org_id);
308 print_debuginfo(l_module_name,'Org Type : '|| p_trxn_attributes_rec.payer_org_type);
309 print_debuginfo(l_module_name,'Payee Id : '|| p_trxn_attributes_rec.payee_party_id);
310 print_debuginfo(l_module_name,'Payee Site Id : '|| p_trxn_attributes_rec.payee_party_site_id);
311 print_debuginfo(l_module_name,'Supplier Site Id : '|| p_trxn_attributes_rec.supplier_site_id);
312 print_debuginfo(l_module_name,'Payment Currency : '|| p_trxn_attributes_rec.payment_currency);
313 print_debuginfo(l_module_name,'Payment Amount : '|| p_trxn_attributes_rec.payment_amount);
314 print_debuginfo(l_module_name,'Account Usage : '|| p_trxn_attributes_rec.payment_function);
315
316 END IF;
317 -- Standard call to check for call compatibility.
318 IF NOT FND_API.Compatible_API_Call(l_api_version,
319 p_api_version,
320 l_api_name,
321 G_PKG_NAME) THEN
322 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
323 END IF;
324
325 -- Initialize message list if p_init_msg_list is set to TRUE.
326 IF FND_API.to_Boolean(p_init_msg_list) THEN
327 FND_MSG_PUB.initialize;
328 END IF;
329
330 -- Initialize API return status to success
331 x_return_status := FND_API.G_RET_STS_SUCCESS;
332
333 -- Check for mandatory params
334 IF (p_trxn_attributes_rec.payee_party_id IS NULL) THEN
335 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
336 print_debuginfo(l_module_name,'Error: Mandatory Parameter ''Payee Party Id'' missing.');
337 END IF;
338 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
339 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_PAYEE_PARTY_ID_FIELD'));
340 FND_MSG_PUB.Add;
341 RAISE FND_API.G_EXC_ERROR;
342 END IF;
343
344 IF (p_trxn_attributes_rec.payment_currency IS NULL) THEN
345 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
346 print_debuginfo(l_module_name,'Error: Mandatory Parameter ''Payment Currency'' missing.');
347 END IF;
348 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
349 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_PMT_CURR_FIELD'));
350 FND_MSG_PUB.Add;
351 RAISE FND_API.G_EXC_ERROR;
352 END IF;
353
354 IF (p_trxn_attributes_rec.payment_function IS NULL) THEN
355 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
356 print_debuginfo(l_module_name,'Error: Mandatory Parameter ''Account Usage'' missing.');
357 END IF;
358 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
359 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_ACCT_USG_FIELD'));
360 FND_MSG_PUB.Add;
361 RAISE FND_API.G_EXC_ERROR;
362 END IF;
363
364 -- Start of API body.
365 OPEN payee_bankacct_csr(p_trxn_attributes_rec.Payee_Party_Id,
366 p_trxn_attributes_rec.Payee_Party_Site_Id,
367 p_trxn_attributes_rec.Supplier_Site_Id,
368 p_trxn_attributes_rec.Payer_Org_Id,
369 p_trxn_attributes_rec.Payer_Org_Type,
370 p_trxn_attributes_rec.Payment_Currency,
371 p_trxn_attributes_rec.Payment_Function);
372
373 FETCH payee_bankacct_csr BULK COLLECT INTO l_payee_bankaccounts_tbl;
374 CLOSE payee_bankacct_csr;
375
376 IF (l_payee_bankaccounts_tbl.COUNT = 0) THEN
377 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
378 print_debuginfo(l_module_name,'Warning: No Payee Bank Accounts Applicable');
379 END IF;
380 ELSE
381 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
382 print_debuginfo(l_module_name,'Applicable Payee Bank Accounts Count : '|| l_payee_bankaccounts_tbl.COUNT);
383 END IF;
384 x_payee_bankaccounts_tbl := l_payee_bankaccounts_tbl;
385 END IF;
386
387 -- End of API body.
388
389 -- Standard call to get message count and if count is 1, get message info.
390 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
391
392 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
393 print_debuginfo(l_module_name, 'RETURN');
394
395 END IF;
396 EXCEPTION
397 WHEN FND_API.G_EXC_ERROR THEN
398 x_return_status := FND_API.G_RET_STS_ERROR;
399 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
400 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
401 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
402 print_debuginfo(l_module_name,'SQLerr is :'
403 || substr(SQLERRM, 1, 150));
404 END IF;
405 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
406 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
407 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
408 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
409 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
410 print_debuginfo(l_module_name,'SQLerr is :'
411 || substr(SQLERRM, 1, 150));
412 END IF;
413 WHEN OTHERS THEN
414 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
415
416 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR))
417 THEN
418 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
419 END IF;
420 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
421 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
422 print_debuginfo(l_module_name,'SQLerr is :'
423 || substr(SQLERRM, 1, 150));
424 END IF;
425 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
426
427 END Get_Applicable_Payee_BankAccts;
428
429 -- Start of comments
430 -- API name : Get_Applicable_Payment_Formats
431 -- Type : Public
432 -- Pre-reqs : None.
433 -- Function : get the list of applicable Delivery Channels.
434 -- Parameters :
435 -- IN : p_api_version IN NUMBER Required
436 -- p_init_msg_list IN VARCHAR2 Optional
437 -- OUT : x_return_status OUT VARCHAR2 Required
438 -- x_msg_count OUT NUMBER Required
439 -- x_msg_data OUT VARCHAR2 Required
440 -- x_payment_format_tbl OUT Payment_Format_Tab_Type Required
441 --
442 -- Version : Current version 1.0
443 -- Previous version None
444 -- Initial version 1.0
445 -- End of comments
446 --
447 -- As the payment format of a transaction is uniquely determined by the payment profie,
448 -- this procedure to get the appliable payment formats is supposed to be rarely used.
449
450 PROCEDURE Get_Applicable_Payment_Formats(
451 p_api_version IN NUMBER,
452 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE ,
453 x_return_status OUT NOCOPY VARCHAR2 ,
454 x_msg_count OUT NOCOPY NUMBER ,
455 x_msg_data OUT NOCOPY VARCHAR2 ,
456 x_payment_formats_tbl OUT NOCOPY Payment_Format_Tab_Type
457 )
458 IS
459 l_api_name CONSTANT VARCHAR2(30) := 'Get_Applicable_Payment_Formats';
460 l_api_version CONSTANT NUMBER := 1.0;
461 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Get_Applicable_Payment_Formats';
462
463 l_payment_formats_tbl Payment_Format_Tab_Type;
464
465 CURSOR payment_formats_csr
466 IS
467 SELECT f.format_name,
468 f.format_code
469 FROM IBY_PAYMENT_PROFILES p,
470 IBY_FORMATS_VL f,
471 IBY_APPLICABLE_PMT_PROFS apf,
472 IBY_APPLICABLE_PMT_PROFS apm,
473 IBY_PAYMENT_METHODS_B m
474 WHERE f.format_code = p.payment_format_code
475 AND apf.system_profile_code = p.system_profile_code
476 AND (apf.applicable_type_code = APL_TYPE_PMT_FORMAT AND
477 (apf.applicable_value_to = f.format_code OR
478 apf.applicable_value_to IS NULL))
479 AND apm.system_profile_code = p.system_profile_code
480 AND (m.inactive_date is null OR m.inactive_date >= trunc(sysdate))
481 AND apm.applicable_type_code = APL_TYPE_PMT_METHOD
482 AND apm.applicable_value_to = m.payment_method_code;
483
484 BEGIN
485
486 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
487 print_debuginfo(l_module_name, 'ENTER');
488
489 END IF;
490 -- Standard call to check for call compatibility.
491 IF NOT FND_API.Compatible_API_Call(l_api_version,
492 p_api_version,
493 l_api_name,
494 G_PKG_NAME) THEN
495 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
496 END IF;
497
498 -- Initialize message list if p_init_msg_list is set to TRUE.
499 IF FND_API.to_Boolean(p_init_msg_list) THEN
500 FND_MSG_PUB.initialize;
501 END IF;
502
503 -- Initialize API return status to success
504 x_return_status := FND_API.G_RET_STS_SUCCESS;
505
506 -- Start of API body.
507
508 OPEN payment_formats_csr();
509 FETCH payment_formats_csr BULK COLLECT INTO l_payment_formats_tbl;
510 CLOSE payment_formats_csr;
511
512 IF (l_payment_formats_tbl.COUNT = 0) THEN
513 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
514 print_debuginfo(l_module_name,'Warning: No Payment Formats Applicable');
515 END IF;
516 ELSE
517 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
518 print_debuginfo(l_module_name,'Applicable Payment Formats Count : '|| l_payment_formats_tbl.COUNT);
519 END IF;
520 x_payment_formats_tbl := l_payment_formats_tbl;
521 END IF;
522
523 -- End of API body.
524
525 -- Standard call to get message count and if count is 1, get message info.
526 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
527
528 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
529 print_debuginfo(l_module_name, 'RETURN');
530
531 END IF;
532 EXCEPTION
533 WHEN FND_API.G_EXC_ERROR THEN
534 x_return_status := FND_API.G_RET_STS_ERROR;
535 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
536 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
537 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
538 print_debuginfo(l_module_name,'SQLerr is :'
539 || substr(SQLERRM, 1, 150));
540 END IF;
541 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
542 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
543 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
544 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
545 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
546 print_debuginfo(l_module_name,'SQLerr is :'
547 || substr(SQLERRM, 1, 150));
548 END IF;
549 WHEN OTHERS THEN
550 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
551
552 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR))
553 THEN
554 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
555 END IF;
556 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
557 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
558 print_debuginfo(l_module_name,'SQLerr is :'
559 || substr(SQLERRM, 1, 150));
560 END IF;
561 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
562
563 END Get_Applicable_Payment_Formats;
564
565 -- Start of comments
566 -- API name : Get_Applicable_Payment_Methods
567 -- Type : Public
568 -- Pre-reqs : None.
569 -- Function : get the list of applicable Payment Methods.
570 -- Parameters :
571 -- IN : p_api_version IN NUMBER Required
572 -- p_init_msg_list IN VARCHAR2 Optional
573 -- p_ignore_payee_prefer IN VARCHAR2
574 -- p_trxn_attributes_rec IN Trxn_Attributes_Rec_Type Required
575 -- OUT : x_return_status OUT VARCHAR2 Required
576 -- x_msg_count OUT NUMBER Required
577 -- x_msg_data OUT VARCHAR2 Required
578 -- x_payment_methods_tbl OUT Payment_Method_Tab_Type Required
579 --
580 -- Version : Current version 1.0
581 -- Previous version None
582 -- Initial version 1.0
583 -- End of comments
584
585 PROCEDURE Get_Applicable_Payment_Methods(
586 p_api_version IN NUMBER,
587 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
588 p_ignore_payee_prefer IN VARCHAR2,
589 p_trxn_attributes_rec IN Trxn_Attributes_Rec_Type,
590 x_return_status OUT NOCOPY VARCHAR2,
591 x_msg_count OUT NOCOPY NUMBER,
592 x_msg_data OUT NOCOPY VARCHAR2,
593 x_payment_methods_tbl OUT NOCOPY Payment_Method_Tab_Type)
594 IS
595 l_api_name CONSTANT VARCHAR2(30) := 'Get_Applicable_Payment_Methods';
596 l_api_version CONSTANT NUMBER := 1.0;
597 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Get_Applicable_Payment_Methods';
598
599 l_pmtmthd_table Payment_Method_Tab_Type;
600 l_pmt_mthds_rec Payment_Method_Rec_Type;
601 l_index NUMBER := 1;
602
603 l_payer_country VARCHAR2(30);
604 l_payee_country VARCHAR2(30);
605 l_accounting_curr VARCHAR2(30);
606
607 l_payer_le_match BOOLEAN;
608 l_payer_org_match BOOLEAN;
609 l_trxn_type_match BOOLEAN;
610 l_currency_match BOOLEAN;
611 l_cross_border_match BOOLEAN;
612 l_match BOOLEAN;
613
614 CURSOR payment_methods(p_application_id NUMBER,
615 p_payee_party_id NUMBER,
616 p_payee_psite_id NUMBER,
617 p_supplier_site_id NUMBER,
618 p_org_id NUMBER,
619 p_org_type VARCHAR2,
620 p_payment_function VARCHAR2,
621 p_ignore_flag VARCHAR2)
622
623 IS
624 SELECT m.Payment_Method_Name,
625 am.PAYMENT_METHOD_CODE,
626 m.SUPPORT_BILLS_PAYABLE_FLAG,
627 m.MATURITY_DATE_OFFSET_DAYS,
628 m.DESCRIPTION
629 FROM IBY_APPLICABLE_PMT_MTHDS am,
630 IBY_PAYMENT_METHODS_VL m
631 WHERE am.PAYMENT_FLOW = 'DISBURSEMENTS'
632 AND am.APPLICABLE_TYPE_CODE = 'PAYEE'
633 AND am.APPLICABLE_VALUE_TO is null
634 AND (am.APPLICATION_ID is null OR am.APPLICATION_ID = p_application_id)
635 AND (m.INACTIVE_DATE is null OR m.INACTIVE_DATE >= trunc(sysdate))
636 AND (am.INACTIVE_DATE is null OR am.INACTIVE_DATE >= trunc(sysdate))
637 AND am.PAYMENT_METHOD_CODE = m.PAYMENT_METHOD_CODE
638 AND NOT EXISTS (select 1
639 from IBY_EXT_PARTY_PMT_MTHDS ppm,
640 IBY_EXTERNAL_PAYEES_ALL payee
641 where ppm.PAYMENT_FLOW = 'DISBURSEMENTS'
642 and ppm.PAYMENT_METHOD_CODE = am.PAYMENT_METHOD_CODE
643 and ppm.PAYMENT_FUNCTION = p_payment_function
644 and ppm.INACTIVE_DATE < trunc(sysdate)
645 and ppm.EXT_PMT_PARTY_ID = payee.EXT_PAYEE_ID
646 and payee.PAYEE_PARTY_ID = p_payee_party_id
647 AND (payee.PARTY_SITE_ID is null OR payee.PARTY_SITE_ID = p_payee_psite_id)
648 AND (payee.SUPPLIER_SITE_ID is null OR payee.SUPPLIER_SITE_ID = p_supplier_site_id)
649 AND (payee.ORG_ID is null OR (payee.ORG_ID = p_org_id AND payee.ORG_TYPE = p_org_type)))
650 AND p_ignore_flag = 'N'
651 UNION
652 SELECT m.Payment_Method_Name,
653 ppm.PAYMENT_METHOD_CODE,
654 m.SUPPORT_BILLS_PAYABLE_FLAG,
655 m.MATURITY_DATE_OFFSET_DAYS,
656 m.DESCRIPTION
657 FROM IBY_EXT_PARTY_PMT_MTHDS ppm,
658 IBY_EXTERNAL_PAYEES_ALL payee,
659 IBY_PAYMENT_METHODS_VL m
660 WHERE ppm.PAYMENT_FLOW = 'DISBURSEMENTS'
661 AND ppm.PAYMENT_FUNCTION = p_payment_function
662 AND (m.INACTIVE_DATE is null OR m.INACTIVE_DATE >= trunc(sysdate))
663 AND (ppm.INACTIVE_DATE is null OR ppm.INACTIVE_DATE >= trunc(sysdate))
664 AND ppm.PAYMENT_METHOD_CODE = m.PAYMENT_METHOD_CODE
665 AND ppm.EXT_PMT_PARTY_ID = payee.EXT_PAYEE_ID
666 AND payee.PAYEE_PARTY_ID = p_payee_party_id
667 AND (payee.PARTY_SITE_ID is null OR payee.PARTY_SITE_ID = p_payee_psite_id)
668 AND (payee.SUPPLIER_SITE_ID is null OR payee.SUPPLIER_SITE_ID = p_supplier_site_id)
669 AND (payee.ORG_ID is null OR (payee.ORG_ID = p_org_id AND payee.ORG_TYPE = p_org_type))
670 AND p_ignore_flag = 'N'
671 UNION
672 SELECT pmthds.Payment_Method_Name,
673 pmthds.Payment_Method_Code,
674 pmthds.SUPPORT_BILLS_PAYABLE_FLAG,
675 pmthds.MATURITY_DATE_OFFSET_DAYS,
676 pmthds.DESCRIPTION
677 FROM IBY_PAYMENT_METHODS_VL pmthds
678 WHERE (pmthds.inactive_date is NULL OR pmthds.inactive_date >= trunc(sysdate))
679 AND NOT EXISTS (select 1
680 from IBY_EXT_PARTY_PMT_MTHDS ppm,
681 IBY_EXTERNAL_PAYEES_ALL payee
682 where ppm.PAYMENT_FLOW = 'DISBURSEMENTS'
683 and PAYMENT_METHOD_CODE = pmthds.PAYMENT_METHOD_CODE
684 and ppm.PAYMENT_FUNCTION = p_payment_function
685 and ppm.INACTIVE_DATE < trunc(sysdate)
686 and ppm.EXT_PMT_PARTY_ID = payee.EXT_PAYEE_ID
687 and payee.PAYEE_PARTY_ID = p_payee_party_id
688 AND (payee.PARTY_SITE_ID is null OR payee.PARTY_SITE_ID = p_payee_psite_id)
689 AND (payee.SUPPLIER_SITE_ID is null OR payee.SUPPLIER_SITE_ID = p_supplier_site_id)
690 AND (payee.ORG_ID is null OR (payee.ORG_ID = p_org_id AND payee.ORG_TYPE = p_org_type)))
691 AND p_ignore_flag = 'Y';
692
693 CURSOR pmthd_drivers_csr(p_payment_method_code IN VARCHAR2,
694 p_application_id IN NUMBER)
695 IS
696 SELECT Payment_Method_Code,
697 Applicable_Type_Code,
698 Applicable_Value_From,
699 Applicable_Value_To
700 FROM IBY_APPLICABLE_PMT_MTHDS apmthds
701 WHERE apmthds.Payment_method_code = p_payment_method_code
702 AND (apmthds.application_id = p_application_id
703 OR apmthds.application_id is NULL);
704
705 CURSOR payer_info_csr(p_payer_le_id IN NUMBER)
706 IS
707 SELECT xlev.country,
708 glv.currency_code
709 FROM XLE_FIRSTPARTY_INFORMATION_V xlev,
710 GL_LEDGER_LE_V glv
711 WHERE xlev.legal_entity_id = glv.legal_entity_id
712 AND glv.ledger_category_code = 'PRIMARY'
713 AND xlev.legal_entity_id = p_payer_le_id;
714
715 CURSOR payee_country_csr(p_payee_id IN NUMBER)
716 IS
717 SELECT country
718 FROM HZ_PARTIES
719 WHERE party_id = p_payee_id;
720
721 CURSOR payeesite_country_csr(p_payee_id IN NUMBER,
722 p_payee_site_id IN NUMBER)
723 IS
724 SELECT locs.country
725 FROM HZ_PARTY_SITES sites,
726 HZ_LOCATIONS locs
727 WHERE sites.party_id = p_payee_id
728 AND sites.party_site_id = p_payee_site_id
729 AND sites.location_id = locs.location_id;
730
731 BEGIN
732
733 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
734 print_debuginfo(l_module_name, 'ENTER');
735 print_debuginfo(l_module_name,'Application_id : '|| p_trxn_attributes_rec.application_id);
736 print_debuginfo(l_module_name,'1st party LE id : '|| p_trxn_attributes_rec.payer_legal_entity_id);
737 print_debuginfo(l_module_name,'Org Id : '|| p_trxn_attributes_rec.payer_org_id);
738 print_debuginfo(l_module_name,'Org Type : '|| p_trxn_attributes_rec.payer_org_type);
739 print_debuginfo(l_module_name,'Payer Id : '|| p_trxn_attributes_rec.payee_party_id);
740 print_debuginfo(l_module_name,'Payee Site Id : '|| p_trxn_attributes_rec.payee_party_site_id);
741 print_debuginfo(l_module_name,'Supplier Site Id : '|| p_trxn_attributes_rec.supplier_site_id);
742 print_debuginfo(l_module_name,'Trxn Type Code : '|| p_trxn_attributes_rec.pay_proc_trxn_type_code);
743 print_debuginfo(l_module_name,'Payment Currency : '|| p_trxn_attributes_rec.payment_currency);
744 print_debuginfo(l_module_name,'Payment Amount : '|| p_trxn_attributes_rec.payment_amount);
745 print_debuginfo(l_module_name,'Account Usage : '|| p_trxn_attributes_rec.payment_function);
746
747 END IF;
748 -- Standard call to check for call compatibility.
749 IF NOT FND_API.Compatible_API_Call(l_api_version,
750 p_api_version,
751 l_api_name,
752 G_PKG_NAME) THEN
753 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
754 END IF;
755
756 -- Initialize message list if p_init_msg_list is set to TRUE.
757 IF FND_API.to_Boolean(p_init_msg_list) THEN
758 FND_MSG_PUB.initialize;
759 END IF;
760
761 -- Initialize API return status to success
762 x_return_status := FND_API.G_RET_STS_SUCCESS;
763
764 -- Check for mandatory params
765 IF (p_trxn_attributes_rec.application_id IS NULL) THEN
766 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
767 print_debuginfo(l_module_name,'Error: Mandatory Parameter ''Application Id'' missing.');
768 END IF;
769 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
770 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_APP_ID'));
771 FND_MSG_PUB.Add;
772 RAISE FND_API.G_EXC_ERROR;
773 END IF;
774
775 IF (p_trxn_attributes_rec.payer_legal_entity_id IS NULL) THEN
776 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
777 print_debuginfo(l_module_name,'Error: Mandatory Parameter ''First party legal entity Id'' missing.');
778 END IF;
779 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
780 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_1PARTY_LE_ID'));
781 FND_MSG_PUB.Add;
782 RAISE FND_API.G_EXC_ERROR;
783 END IF;
784
785 IF (p_trxn_attributes_rec.payee_party_id IS NULL) THEN
786 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
787 print_debuginfo(l_module_name,'Error: Mandatory Parameter ''Payee Party Id'' missing.');
788 END IF;
789 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
790 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_PAYEE_PARTY_ID_FIELD'));
791 FND_MSG_PUB.Add;
792 RAISE FND_API.G_EXC_ERROR;
793 END IF;
794
795 IF (p_trxn_attributes_rec.pay_proc_trxn_type_code IS NULL) THEN
796 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
797 print_debuginfo(l_module_name,'Error: Mandatory Parameter ''Transaction Type Id'' missing.');
798 END IF;
799 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
800 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_TRANS_TYPE_ID'));
801 FND_MSG_PUB.Add;
802 RAISE FND_API.G_EXC_ERROR;
803 END IF;
804
805 IF (p_trxn_attributes_rec.payment_currency IS NULL) THEN
806 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
807 print_debuginfo(l_module_name,'Error: Mandatory Parameter ''Payment Currency'' missing.');
808 END IF;
809 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
810 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_PMT_CURR_FIELD'));
811 FND_MSG_PUB.Add;
812 RAISE FND_API.G_EXC_ERROR;
813 END IF;
814
815 IF (p_trxn_attributes_rec.payment_function IS NULL) THEN
816 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
817 print_debuginfo(l_module_name,'Error: Mandatory Parameter ''Account Usage'' missing.');
818 END IF;
819 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
820 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_ACCT_USG_FIELD'));
821 FND_MSG_PUB.Add;
822 RAISE FND_API.G_EXC_ERROR;
823 END IF;
824
825 -- Fetch Accounting Currency and 1st part Payer country
826 IF (p_trxn_attributes_rec.payer_legal_entity_id IS NOT NULL) THEN
827 OPEN payer_info_csr(p_trxn_attributes_rec.payer_legal_entity_id);
828 FETCH payer_info_csr INTO l_payer_country, l_accounting_curr;
829 CLOSE payer_info_csr;
830 END IF;
831 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
832 print_debuginfo(l_module_name,'Accounting currency : '|| l_accounting_curr);
833 print_debuginfo(l_module_name,'First party legal entity country : '|| l_payer_country);
834
835 END IF;
836 IF (p_trxn_attributes_rec.payee_party_id IS NOT NULL) THEN
837 IF (p_trxn_attributes_rec.payee_party_site_id IS NOT NULL) THEN
838 -- Fetch Payee Site Country
839 OPEN payeesite_country_csr(p_trxn_attributes_rec.payee_party_id,
840 p_trxn_attributes_rec.payee_party_site_id);
841 FETCH payeesite_country_csr INTO l_payee_country;
842 CLOSE payeesite_country_csr;
843 ELSE
844 -- Fetch Payee Country
845 OPEN payee_country_csr(p_trxn_attributes_rec.payee_party_site_id);
846 FETCH payee_country_csr INTO l_payee_country;
847 CLOSE payee_country_csr;
848 END IF;
849 END IF;
850
851 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
852 print_debuginfo(l_module_name, 'Payee Country : '|| l_payee_country);
853
854 END IF;
855 --
856 -- Pick up payment methods
857 --
858 OPEN payment_methods(p_trxn_attributes_rec.application_id,
859 p_trxn_attributes_rec.payee_party_id,
860 p_trxn_attributes_rec.payee_party_site_id,
861 p_trxn_attributes_rec.supplier_site_id,
862 p_trxn_attributes_rec.payer_org_id,
863 p_trxn_attributes_rec.payer_org_type,
864 p_trxn_attributes_rec.payment_function,
865 p_ignore_payee_prefer);
866
867 LOOP
868 l_payer_le_match := FALSE;
869 l_payer_org_match := FALSE;
870 l_trxn_type_match := FALSE;
871 l_currency_match := FALSE;
872 l_cross_border_match := FALSE;
873 l_match := FALSE;
874
875 -- If no rows are returned, no Payment Methods are defined for this application
876
877 FETCH payment_methods INTO l_pmt_mthds_rec;
878 EXIT WHEN(payment_methods%NOTFOUND);
879
880 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
881 print_debuginfo(l_module_name,'Payment Method : '|| l_pmt_mthds_rec.payment_method_code);
882
883 END IF;
884 -- Pick up all driving parameters for this Payment Method
885 FOR pmthd_drivers_rec IN pmthd_drivers_csr(l_pmt_mthds_rec.payment_method_code,
886 p_trxn_attributes_rec.application_id)
887 LOOP
888 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
889 print_debuginfo(l_module_name,' Applicable_Type_Code : '|| pmthd_drivers_rec.Applicable_Type_Code);
890 print_debuginfo(l_module_name,' Applicable_Type_Value : '|| pmthd_drivers_rec.Applicable_Value_To);
891
892 END IF;
893 CASE pmthd_drivers_rec.Applicable_Type_Code
894 WHEN 'PAYER_LE' THEN
895 l_payer_le_match := l_payer_le_match
896 OR (pmthd_drivers_rec.Applicable_Value_To = p_trxn_attributes_rec.payer_legal_entity_id)
897 OR (pmthd_drivers_rec.Applicable_Value_To IS NULL);
898
899 IF (l_payer_le_match) THEN
900 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
901 print_debuginfo(l_module_name,' l_payer_le_match : TRUE');
902 END IF;
903 END IF;
904
905 WHEN 'PAYER_ORG' THEN
906 l_payer_org_match := l_payer_org_match
907 OR (pmthd_drivers_rec.Applicable_Value_To = p_trxn_attributes_rec.payer_org_id)
908 OR (pmthd_drivers_rec.Applicable_Value_To IS NULL);
909
910 IF (l_payer_org_match) THEN
911 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
912 print_debuginfo(l_module_name,' l_payer_org_match : TRUE');
913 END IF;
914 END IF;
915
916 WHEN 'PAY_PROC_TRXN_TYPE' THEN
917 l_trxn_type_match := l_trxn_type_match
918 OR (pmthd_drivers_rec.Applicable_Value_To =
919 to_char(p_trxn_attributes_rec.pay_proc_trxn_type_code))
920 OR (pmthd_drivers_rec.Applicable_Value_To IS NULL);
921
922 IF (l_trxn_type_match) THEN
923 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
924 print_debuginfo(l_module_name,' l_trxn_type_match : TRUE');
925 END IF;
926 END IF;
927
928 WHEN 'FOREIGN_CURRENCY_FLAG' THEN
929 l_currency_match := l_currency_match
930 OR (l_accounting_curr = p_trxn_attributes_rec.payment_currency
931 AND pmthd_drivers_rec.Applicable_Value_To = 'DOMESTIC')
932 OR (l_accounting_curr <> p_trxn_attributes_rec.payment_currency
933 AND pmthd_drivers_rec.Applicable_Value_To = 'FOREIGN')
934 OR (pmthd_drivers_rec.Applicable_Value_To = 'FOREIGN_AND_DOMESTIC')
935 OR (pmthd_drivers_rec.Applicable_Value_To IS NULL);
936
937 IF (l_currency_match) THEN
938 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
939 print_debuginfo(l_module_name,' l_currency_match : TRUE');
940 END IF;
941 END IF;
942
943 WHEN 'CROSS_BORDER_FLAG' THEN
944 l_cross_border_match := l_cross_border_match
945 OR (NVL(l_payee_country,l_payer_country) = l_payer_country
946 AND pmthd_drivers_rec.Applicable_Value_To = 'DOMESTIC')
947 OR (NVL(l_payee_country,l_payer_country) <> l_payer_country
948 AND pmthd_drivers_rec.Applicable_Value_To = 'FOREIGN')
949 OR (pmthd_drivers_rec.Applicable_Value_To = 'FOREIGN_AND_DOMESTIC')
950 OR (pmthd_drivers_rec.Applicable_Value_To IS NULL);
951
952 IF (l_cross_border_match) THEN
953 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
954 print_debuginfo(l_module_name,' l_cross_border_match : TRUE');
955 END IF;
956 END IF;
957
958 ELSE
959 NULL; -- Not a recognized driving parameter, hence ignoring it
960 END CASE;
961
962 -- driving parameters loop
963 END LOOP;
964
965 l_match := (l_payer_le_match AND l_payer_org_match AND
966 l_trxn_type_match AND l_currency_match AND
967 l_cross_border_match );
968
969 -- insert matched Payment Method record into pl/sql table
970 IF (l_match) THEN
971 l_pmtmthd_table(l_index) := l_pmt_mthds_rec;
972 l_index := l_index + 1;
973
974 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
975 print_debuginfo(l_module_name, 'Match Found');
976 END IF;
977 ELSE
978 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
979 print_debuginfo(l_module_name, 'Not a Match');
980 END IF;
981 END IF;
982
983 END LOOP; -- applicable payment methods loop
984 CLOSE payment_methods;
985
986 IF (l_pmtmthd_table.COUNT = 0) THEN
987 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
988 print_debuginfo(l_module_name,'Error: No Payment Methods Applicable');
989
990 END IF;
991 FND_MESSAGE.set_name('IBY', 'IBY_NO_APPLICABLE_PAYMENT_METHODS');
992 FND_MSG_PUB.Add;
993 raise FND_API.G_EXC_ERROR;
994 ELSE
995 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
996 print_debuginfo(l_module_name,'Applicable Payment Methods Count : '|| l_pmtmthd_table.COUNT);
997 END IF;
998 x_payment_methods_tbl := l_pmtmthd_table;
999 END IF;
1000
1001 -- End of API body.
1002 -- Standard call to get message count and if count is 1, get message info.
1003 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1004
1005 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1006 print_debuginfo(l_module_name, 'RETURN');
1007
1008 END IF;
1009 EXCEPTION
1010 WHEN FND_API.G_EXC_ERROR THEN
1011 x_return_status := FND_API.G_RET_STS_ERROR;
1012 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1013 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1014 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
1015 print_debuginfo(l_module_name,'SQLerr is :'
1016 || substr(SQLERRM, 1, 150));
1017 END IF;
1018 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1019 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1020 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1021 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1022 print_debuginfo(l_module_name,'Unexpected ERROR: Exception occured during call to API ');
1023 print_debuginfo(l_module_name,'SQLerr is :'
1024 || substr(SQLERRM, 1, 150));
1025 END IF;
1026 WHEN OTHERS THEN
1027 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1028
1029 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR))
1030 THEN
1031 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1032 END IF;
1033 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1034 print_debuginfo(l_module_name,'Other ERROR: Exception occured during call to API ');
1035 print_debuginfo(l_module_name,'SQLerr is :'
1036 || substr(SQLERRM, 1, 150));
1037 END IF;
1038 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1039
1040 END Get_Applicable_Payment_Methods;
1041
1042 -- Start of comments
1043 -- API name : Get_Applicable_Pmt_Profiles
1044 -- Type : Public
1045 -- Pre-reqs : None
1046 -- Function : Get the list of applicable payment profiles
1047 -- based on the given profile drivers
1048 -- Parameters :
1049 -- IN : p_api_version IN NUMBER Required
1050 -- p_init_msg_list IN VARCHAR2 Optional
1051 -- p_ppp_drivers_rec IN PPP_Drivers_Rec_Type
1052 -- Required
1053 -- OUT : x_return_status OUT VARCHAR2 Required
1054 -- x_msg_count OUT NUMBER Required
1055 -- x_msg_data OUT VARCHAR2 Required
1056 -- x_payment_profiles_tbl OUT
1057 -- Payment_Profiles_Tab_Type
1058 -- Required
1059 -- Version : Current version 1.0
1060 -- Previous version None
1061 -- Initial version 1.0
1062 -- End of comments
1063
1064 PROCEDURE Get_Applicable_Pmt_Profiles(
1065 p_api_version IN NUMBER,
1066 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
1067 p_ppp_drivers_rec IN PPP_Drivers_Rec_Type,
1068 x_return_status OUT NOCOPY VARCHAR2,
1069 x_msg_count OUT NOCOPY NUMBER,
1070 x_msg_data OUT NOCOPY VARCHAR2,
1071 x_payment_profiles_tbl OUT NOCOPY Payment_Profile_Tab_Type)
1072 IS
1073 l_api_name CONSTANT VARCHAR2(30) := 'Get_Applicable_Pmt_Profiles';
1074 l_api_version CONSTANT NUMBER := 1.0;
1075 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Get_Applicable_Pmt_Profiles';
1076
1077 l_pmt_profs_tab Payment_Profile_Tab_Type;
1078 l_pmt_profs_rec Payment_Profile_Rec_Type;
1079 l_index NUMBER := 1;
1080
1081 /*
1082 * We need to select payment profiles that are applicable to
1083 * given (payment method, org, format, currency, int bank account).
1084 *
1085 *
1086 * | Profiles |
1087 * | applicable to |
1088 * | given pmt | Profiles applicable to
1089 * | method | given payment currency
1090 * | | /
1091 * | | | /
1092 * | V | L
1093 * | |
1094 * |----------------|--------------------------
1095 * |/ \| Profiles
1096 * | | applicable to
1097 * | Intersection | <-- given
1098 * | | org
1099 * |\ /|
1100 * |----------------|--------------------------
1101 * | |
1102 * | | .__
1103 * | ^ | |\
1104 * | | | \
1105 * | | |
1106 * | | Profiles applicable to
1107 * | Profiles | given internal bank
1108 * | applicable to | account
1109 * | given format |
1110 * | |
1111 *
1112 * We need the intersection of (profiles applicable to
1113 * a given payment method) and (profiles applicable to
1114 * a given org) and (profiles applicable to a given
1115 * format) and (profiles applicable to given payment
1116 * currency) and (profiles applicable to given internal
1117 * bank account) as shown in the graphic.
1118 *
1119 * Therefore, we need to join with the IBY_APPLICABLE_PMT_PROFS
1120 * five times - once to get the profiles for the method, once to get
1121 * the profiles for the org, and once to get the profiles for the
1122 * format etc. If we are able to get a non-null intersect for these
1123 * five queries, it means that there is a profile that matches the
1124 * (org, method, format, currency, bank acct) combination.
1125 *
1126 * If the 'applicable_value_to' is set to NULL, it means that the
1127 * profile is applicable to 'all orgs' | 'all methods' |
1128 * 'all formats' etc., depending upon the applicable_type_code.
1129 * Therefore, we need to factor this condition in the join.
1130 *
1131 * Payment format is not a driving item for payment profile.
1132 */
1133 CURSOR c_profiles(
1134 p_pmt_method_cd IN IBY_DOCS_PAYABLE_ALL.
1135 payment_method_code%TYPE,
1136 p_org_id IN IBY_DOCS_PAYABLE_ALL.org_id%TYPE,
1137 p_org_type IN IBY_DOCS_PAYABLE_ALL.org_type%TYPE,
1138 p_pmt_currency IN IBY_DOCS_PAYABLE_ALL.
1139 payment_currency_code%TYPE,
1140 p_int_bank_acct_id IN IBY_DOCS_PAYABLE_ALL.
1141 internal_bank_account_id%TYPE
1142 )
1143 IS
1144
1145 SELECT
1146 prof.payment_profile_id,
1147 prof.payment_profile_name,
1148 prof.processing_type
1149 FROM
1150 IBY_APPLICABLE_PMT_PROFS app1,
1151 IBY_APPLICABLE_PMT_PROFS app2,
1152 IBY_APPLICABLE_PMT_PROFS app3,
1153 IBY_APPLICABLE_PMT_PROFS app4,
1154 IBY_PAYMENT_PROFILES prof
1155 WHERE
1156 (app1.applicable_type_code=APL_TYPE_PAYER_ORG AND
1157 ((app1.applicable_value_to=TO_CHAR(p_org_id) AND
1158 app1.applicable_value_from=p_org_type) OR
1159 (app1.applicable_value_to IS NULL AND
1160 app1.applicable_value_from IS NULL)) )
1161 AND (app2.applicable_type_code=APL_TYPE_PMT_METHOD AND
1162 (app2.applicable_value_to=p_pmt_method_cd OR
1163 app2.applicable_value_to IS NULL))
1164 AND (app3.applicable_type_code=APL_TYPE_PMT_CURRENCY AND
1165 (app3.applicable_value_to=p_pmt_currency OR
1166 app3.applicable_value_to IS NULL))
1167 AND (app4.applicable_type_code=APL_TYPE_INT_BANK_ACCT AND
1168 (app4.applicable_value_to=TO_CHAR(p_int_bank_acct_id) OR
1169 app4.applicable_value_to IS NULL))
1170 AND app1.system_profile_code=app2.system_profile_code
1171 AND app2.system_profile_code=app3.system_profile_code
1172 AND app3.system_profile_code=app4.system_profile_code
1173 AND app4.system_profile_code=app1.system_profile_code
1174 AND app1.system_profile_code=prof.system_profile_code
1175 /*
1176 * Fix for bug 5929889:
1177 *
1178 * Filter profiles by inactive date so that we do not
1179 * pick up end-dated profiles.
1180 */
1181 AND NVL(prof.inactive_date, SYSDATE + 1) > SYSDATE
1182 ;
1183
1184 BEGIN
1185
1186 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1187 print_debuginfo(l_module_name, 'ENTER');
1188
1189 print_debuginfo(l_module_name, 'Checking for profiles '
1190 || 'applicable for given org '
1191 || p_ppp_drivers_rec.Payer_Org_Id
1192 || ' and org type '
1193 || p_ppp_drivers_rec.Payer_Org_Type
1194 || ' and payment method '
1195 || p_ppp_drivers_rec.Payment_Method_Code
1196 || ' and payment currency '
1197 || p_ppp_drivers_rec.Payment_Currency
1198 || ' and internal bank account '
1199 || p_ppp_drivers_rec.Int_Bank_Account_Id
1200 || ' combination ...'
1201 );
1202
1203 END IF;
1204 -- Standard call to check for call compatibility.
1205 IF NOT FND_API.Compatible_API_Call(
1206 l_api_version,
1207 p_api_version,
1208 l_api_name,
1209 G_PKG_NAME) THEN
1210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1211 END IF;
1212
1213 -- Initialize message list if p_init_msg_list is set to TRUE.
1214 IF FND_API.to_Boolean(p_init_msg_list) THEN
1215 FND_MSG_PUB.initialize;
1216 END IF;
1217
1218 -- Initialize API return status to success
1219 x_return_status := FND_API.G_RET_STS_SUCCESS;
1220
1221 --
1222 -- Pick up all payment profiles that match the given profile drivers.
1223 --
1224 OPEN c_profiles(p_ppp_drivers_rec.Payment_Method_Code,
1225 p_ppp_drivers_rec.Payer_Org_Id,
1226 p_ppp_drivers_rec.Payer_Org_Type,
1227 p_ppp_drivers_rec.Payment_Currency,
1228 p_ppp_drivers_rec.Int_Bank_Account_Id
1229 );
1230 FETCH c_profiles BULK COLLECT INTO l_pmt_profs_tab;
1231 CLOSE c_profiles;
1232
1233 IF (l_pmt_profs_tab.COUNT = 0) THEN
1234 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1235 print_debuginfo(l_module_name,'Error: No applicable payment profiles '
1236 || 'were found.');
1237 END IF;
1238 FND_MESSAGE.set_name('IBY', 'IBY_NO_APPLICABLE_PAYMENT_PROFILES');
1239 FND_MSG_PUB.Add;
1240 raise FND_API.G_EXC_ERROR;
1241 ELSE
1242 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1243 print_debuginfo(l_module_name, 'Count of applicable payment profiles: '
1244 || l_pmt_profs_tab.COUNT);
1245 END IF;
1246 x_payment_profiles_tbl := l_pmt_profs_tab;
1247
1248 END IF;
1249
1250 -- End of API body.
1251
1252 -- Standard call to get message count and if count is 1, get message info.
1253 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1254
1255 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1256 print_debuginfo(l_module_name, 'RETURN');
1257
1258 END IF;
1259 EXCEPTION
1260
1261 WHEN FND_API.G_EXC_ERROR THEN
1262 x_return_status := FND_API.G_RET_STS_ERROR;
1263 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1264 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1265 print_debuginfo(l_module_name, 'ERROR: Exception occured '
1266 || 'during call to API ');
1267 print_debuginfo(l_module_name, 'SQLerr is :'
1268 || substr(SQLERRM, 1, 150));
1269
1270 END IF;
1271 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1272 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1273 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1274 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1275 print_debuginfo(l_module_name, 'ERROR: Exception occured during '
1276 || 'call to API ');
1277 print_debuginfo(l_module_name,'SQLerr is :'
1278 || substr(SQLERRM, 1, 150));
1279
1280 END IF;
1281 WHEN OTHERS THEN
1282 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1283
1284 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1285 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1286 END IF;
1287
1288 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1289 print_debuginfo(l_module_name, 'ERROR: Exception occured during '
1290 || 'call to API ');
1291 print_debuginfo(l_module_name,'SQLerr is :'
1292 || substr(SQLERRM, 1, 150));
1293 END IF;
1294 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1295
1296 END Get_Applicable_Pmt_Profiles;
1297
1298 -- Start of comments
1299 -- API name : Get_Pmt_Profiles_Intersect
1300 -- Type : Public
1301 -- Pre-reqs : None
1302 -- Function : Get the list of applicable payment profiles
1303 -- that are applicable across the given profile
1304 -- drivers list.
1305 --
1306 -- We already have a method to get the payment
1307 -- profiles for a single set of profile drivers;
1308 -- This method will attempt to get the payment profiles
1309 -- for every given set of payment drivers in the list
1310 -- and return their intersection.
1311 --
1312 -- Parameters :
1313 -- IN : p_api_version IN NUMBER Required
1314 -- p_init_msg_list IN VARCHAR2 Optional
1315 -- p_ppp_drivers_tab IN PPP_Drivers_Tab_Type
1316 -- Required
1317 -- OUT : x_return_status OUT VARCHAR2 Required
1318 -- x_msg_count OUT NUMBER Required
1319 -- x_msg_data OUT VARCHAR2 Required
1320 -- x_payment_profiles_tbl OUT
1321 -- Payment_Profiles_Tab_Type
1322 -- Required
1323 -- Version : Current version 1.0
1324 -- Previous version None
1325 -- Initial version 1.0
1326 -- End of comments
1327
1328 PROCEDURE Get_Pmt_Profiles_Intersect(
1329 p_api_version IN NUMBER,
1330 p_init_msg_list IN VARCHAR2 DEFAULT
1331 FND_API.G_FALSE,
1332 p_ppp_drivers_tab IN PPP_Drivers_Tab_Type,
1333 x_return_status OUT NOCOPY VARCHAR2,
1334 x_msg_count OUT NOCOPY NUMBER,
1335 x_msg_data OUT NOCOPY VARCHAR2,
1336 x_payment_profiles_tbl OUT NOCOPY Payment_Profile_Tab_Type)
1337 IS
1338 l_api_name CONSTANT VARCHAR2(30) :=
1339 'Get_Pmt_Profiles_Intersect';
1340 l_api_version CONSTANT NUMBER := 1.0;
1341 l_module_name CONSTANT VARCHAR2(200) :=
1342 G_PKG_NAME || '.Get_Pmt_Profiles_Intersect';
1343
1344 l_prof_intsct_tab Payment_Profile_Tab_Type;
1345 l_prof_tab Payment_Profile_Tab_Type;
1346 l_pmt_prof_rec Payment_Profile_Rec_Type;
1347
1348 l_prof_tabs_list Payment_Profile_2D_Tab_Type;
1349 l_index NUMBER := 1;
1350 l_first_set BOOLEAN := FALSE;
1351 l_match BOOLEAN := FALSE;
1352
1353 BEGIN
1354
1355 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1356 print_debuginfo(l_module_name, 'ENTER');
1357
1358 END IF;
1359 -- Standard call to check for call compatibility.
1360 IF NOT FND_API.Compatible_API_Call(
1361 l_api_version,
1362 p_api_version,
1363 l_api_name,
1364 G_PKG_NAME) THEN
1365 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1366 END IF;
1367
1368 -- Initialize message list if p_init_msg_list is set to TRUE.
1369 IF FND_API.to_Boolean(p_init_msg_list) THEN
1370 FND_MSG_PUB.initialize;
1371 END IF;
1372
1373 -- Initialize API return status to success
1374 x_return_status := FND_API.G_RET_STS_SUCCESS;
1375
1376 --
1377 -- If no driver sets are given, do nothing
1378 --
1379 IF (p_ppp_drivers_tab.COUNT = 0) THEN
1380
1381 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1382 print_debuginfo(l_module_name, 'No profile drivers were '
1383 || 'specified. Exiting .. ');
1384 print_debuginfo(l_module_name, 'RETURN');
1385
1386 END IF;
1387 RETURN;
1388
1389 END IF;
1390
1391 --
1392 -- Call the applicable payment profiles API for each set
1393 -- of profile drivers.
1394 --
1395 FOR i IN p_ppp_drivers_tab.FIRST .. p_ppp_drivers_tab.LAST LOOP
1396
1397 Get_Applicable_Pmt_Profiles(
1398 p_api_version,
1399 p_init_msg_list ,
1400 p_ppp_drivers_tab(i),
1401 x_return_status,
1402 x_msg_count,
1403 x_msg_data,
1404 l_prof_tab
1405 );
1406
1407 IF (x_return_status = FND_API.G_RET_STS_SUCCESS) THEN
1408
1409 /*
1410 * Add the returned list of payment profiles into
1411 * our list of payment profile tables.
1412 */
1413 l_prof_tabs_list(l_prof_tabs_list.COUNT + 1) := l_prof_tab;
1414
1415 ELSE
1416
1417 /*
1418 * We cannot proceed because the API call to get payment
1419 * profiles for a particular set of payment profile
1420 * drivers has failed. Raise an exception.
1421 */
1422 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1423
1424 END IF;
1425
1426
1427 END LOOP;
1428
1429 /*
1430 * Start processing the list of profile drivers for each document
1431 * one-by-one.
1432 */
1433
1434 /* find intersect profiles */
1435 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1436 print_debuginfo(l_module_name, 'Finding intersect profiles .. ');
1437
1438 END IF;
1439 l_first_set := TRUE;
1440 FOR i in l_prof_tabs_list.FIRST .. l_prof_tabs_list.LAST LOOP
1441
1442 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1443 print_debuginfo(l_module_name, 'Popping table: ' || i);
1444
1445 END IF;
1446 l_prof_tab := l_prof_tabs_list(i);
1447
1448 IF (l_first_set = TRUE) THEN
1449
1450 /* Add the first table to the intersect list */
1451 /*
1452 * We'll start eliminating those elements from
1453 * this intersect list that are not found in the
1454 * comparison list.
1455 */
1456 l_prof_intsct_tab := l_prof_tab;
1457
1458 IF (l_prof_intsct_tab.COUNT > 0) THEN
1459
1460 FOR n in l_prof_intsct_tab.FIRST .. l_prof_intsct_tab.LAST LOOP
1461
1462 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1463 print_debuginfo(l_module_name, 'Seeded intersect '
1464 || 'profile id: '
1465 || l_prof_intsct_tab(n).Payment_Profile_Id
1466 );
1467
1468 END IF;
1469 END LOOP;
1470
1471 END IF;
1472
1473 l_first_set := FALSE;
1474
1475 END IF;
1476
1477 /* eliminate from the intersect list */
1478
1479 IF (l_prof_intsct_tab.COUNT > 0) THEN
1480 FOR k in l_prof_intsct_tab.FIRST .. l_prof_intsct_tab.LAST
1481 LOOP
1482
1483 /*
1484 * Since we are eliminating rows from the intersect
1485 * table, we have to ensure that the rows exists before
1486 * each iteration begins.
1487 */
1488 IF (l_prof_intsct_tab.EXISTS(k)) THEN
1489
1490 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1491 print_debuginfo(l_module_name, 'Current intersect '
1492 || 'profile id: '
1493 || l_prof_intsct_tab(k).Payment_Profile_Id
1494 );
1495
1496 END IF;
1497 /*
1498 * Loop through all the given profiles searching
1499 * if any of them is stored in the intersect
1500 * table.
1501 */
1502 l_match := FALSE;
1503
1504 IF (l_prof_tab.COUNT > 0) THEN
1505
1506 FOR m in l_prof_tab.FIRST .. l_prof_tab.LAST LOOP
1507
1508 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1509 print_debuginfo(l_module_name, 'Comparing intersect '
1510 || 'profile id: '
1511 || l_prof_intsct_tab(k).Payment_Profile_Id
1512 || ' with profile '
1513 || l_prof_tab(m).Payment_Profile_Id
1514 );
1515
1516 END IF;
1517 IF (l_prof_intsct_tab(k).Payment_Profile_Id =
1518 l_prof_tab(m).Payment_Profile_Id) THEN
1519
1520 l_match := TRUE;
1521
1522 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1523 print_debuginfo(l_module_name, 'Profile id: '
1524 || l_prof_intsct_tab(k).Payment_Profile_Id
1525 || ' matched.'
1526 );
1527
1528 END IF;
1529 ELSE
1530
1531 IF (l_match <> TRUE) THEN
1532 l_match := FALSE;
1533 END IF;
1534
1535 END IF;
1536
1537 END LOOP; -- for each profile in current set
1538
1539 ELSE
1540
1541 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1542 print_debuginfo(l_module_name, 'Comparison list is empty. '
1543 || 'This means that there are no intersection '
1544 || 'elements. Emptying out intersection list ..'
1545 );
1546
1547 END IF;
1548 l_match := FALSE;
1549
1550 END IF;
1551
1552 IF (l_match = FALSE) THEN
1553 /*
1554 * This means that the current profile
1555 * from the intersect was not found
1556 * in the entire list of profiles
1557 * that we were comparing with.
1558 *
1559 * Therefore, this profile is no longer
1560 * in the intersection. Eliminate this
1561 * profile from the intersect list.
1562 */
1563 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1564 print_debuginfo(l_module_name, 'Eliminating profile id: '
1565 || l_prof_intsct_tab(k).Payment_Profile_Id);
1566
1567 END IF;
1568 l_prof_intsct_tab.DELETE(k);
1569
1570 IF (l_prof_intsct_tab.COUNT = 0) THEN
1571
1572 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1573 print_debuginfo(l_module_name, 'Intersect list '
1574 || 'is empty. '
1575 || 'Exiting ..');
1576
1577 END IF;
1578 GOTO label_finish;
1579
1580 END IF;
1581
1582 END IF;
1583
1584 END IF; -- if row exists in intersect table
1585
1586 END LOOP; -- for each profile in intersect
1587 END IF; -- if intersect is non-zero
1588
1589 END LOOP;
1590
1591 <<label_finish>>
1592
1593 /* Finally print the profile intersection */
1594 IF (l_prof_intsct_tab.COUNT = 0) THEN
1595
1596 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1597 print_debuginfo(l_module_name, 'Profile intersection is NULL');
1598
1599 END IF;
1600 ELSE
1601
1602 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1603 print_debuginfo(l_module_name, '-----------------------');
1604 END IF;
1605 FOR i IN l_prof_intsct_tab.FIRST .. l_prof_intsct_tab.LAST LOOP
1606
1607 IF (l_prof_intsct_tab.EXISTS(i)) THEN
1608
1609 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1610 print_debuginfo(l_module_name, 'Intersection profile: '
1611 || l_prof_intsct_tab(i).Payment_Profile_Id);
1612
1613 END IF;
1614 END IF;
1615
1616 END LOOP;
1617 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1618 print_debuginfo(l_module_name, '-----------------------');
1619
1620 END IF;
1621 END IF;
1622
1623 /*
1624 * Copy back the payment profiles intersect onto
1625 * the output param.
1626 */
1627 x_payment_profiles_tbl := l_prof_intsct_tab;
1628
1629 -- End of API body.
1630 -- Standard call to get message count and if count is 1, get message info.
1631 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1632
1633 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1634 print_debuginfo(l_module_name, 'RETURN');
1635
1636 END IF;
1637 EXCEPTION
1638
1639 WHEN FND_API.G_EXC_ERROR THEN
1640 x_return_status := FND_API.G_RET_STS_ERROR;
1641 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1642 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1643 print_debuginfo(l_module_name, 'ERROR: Exception occured '
1644 || 'during call to API ');
1645 print_debuginfo(l_module_name, 'SQLerr is :'
1646 || substr(SQLERRM, 1, 150));
1647
1648 END IF;
1649 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1650 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1651 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1652 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1653 print_debuginfo(l_module_name, 'ERROR: Exception occured during '
1654 || 'call to API ');
1655 print_debuginfo(l_module_name,'SQLerr is :'
1656 || substr(SQLERRM, 1, 150));
1657
1658 END IF;
1659 WHEN OTHERS THEN
1660 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1661
1662 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR))
1663 THEN
1664
1665 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1666
1667 END IF;
1668
1669 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1670 print_debuginfo(l_module_name, 'ERROR: Exception occured during '
1671 || 'call to API ');
1672 print_debuginfo(l_module_name,'SQLerr is :'
1673 || substr(SQLERRM, 1, 150));
1674 END IF;
1675 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1676
1677 END Get_Pmt_Profiles_Intersect;
1678
1679 -- Start of comments
1680 -- API name : Get_Applicable_Payment_Reasons
1681 -- Type : Public
1682 -- Pre-reqs : None.
1683 -- Function : get the list of applicable Payment Reasons.
1684 -- Parameters :
1685 -- IN : p_api_version IN NUMBER Required
1686 -- p_init_msg_list IN VARCHAR2 Optional
1687 -- p_trxn_attributes_rec IN Trxn_Attributes_Rec_Type Required
1688 -- OUT : x_return_status OUT VARCHAR2 Required
1689 -- x_msg_count OUT NUMBER Required
1690 -- x_msg_data OUT VARCHAR2 Required
1691 -- x_payment_reason_tbl OUT Payment_Reason_Tab_Type Required
1692 --
1693 -- Version : Current version 1.0
1694 -- Previous version None
1695 -- Initial version 1.0
1696 -- End of comments
1697
1698 PROCEDURE Get_Applicable_Payment_Reasons(
1699 p_api_version IN NUMBER,
1700 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE ,
1701 p_trxn_attributes_rec IN Trxn_Attributes_Rec_Type,
1702 x_return_status OUT NOCOPY VARCHAR2 ,
1703 x_msg_count OUT NOCOPY NUMBER ,
1704 x_msg_data OUT NOCOPY VARCHAR2 ,
1705 x_payment_reason_tbl OUT NOCOPY Payment_Reason_Tab_Type
1706 )
1707 IS
1708
1709 l_api_name CONSTANT VARCHAR2(30) := 'Get_Applicable_Payment_Reason';
1710 l_api_version CONSTANT NUMBER := 1.0;
1711 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Get_Applicable_Payment_Reason';
1712
1713 l_payment_reason_tbl Payment_Reason_Tab_Type;
1714 l_payer_country VARCHAR2(35);
1715
1716 CURSOR payment_reason_csr(p_payer_country VARCHAR2)
1717 IS
1718 SELECT payment_reason_code,
1719 description,
1720 meaning,
1721 territory_code
1722 FROM IBY_PAYMENT_REASONS_VL ibypr
1723 WHERE (ibypr.territory_code = p_payer_country OR ibypr.territory_code is NULL)
1724 AND (ibypr.inactive_date is NULL OR ibypr.inactive_date >= trunc(sysdate));
1725
1726 CURSOR payer_country_csr(p_payer_le_id NUMBER)
1727 IS
1728 SELECT xle.country
1729 FROM XLE_FIRSTPARTY_INFORMATION_V xle
1730 WHERE xle.legal_entity_id = p_payer_le_id;
1731
1732 BEGIN
1733 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1734 print_debuginfo(l_module_name,'ENTER');
1735
1736 END IF;
1737 -- Standard call to check for call compatibility.
1738 IF NOT FND_API.Compatible_API_Call(l_api_version,
1739 p_api_version,
1740 l_api_name,
1741 G_PKG_NAME) THEN
1742 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1743 END IF;
1744
1745 -- Initialize message list if p_init_msg_list is set to TRUE
1746 IF FND_API.to_Boolean(p_init_msg_list) THEN
1747 FND_MSG_PUB.initialize;
1748 END IF;
1749
1750 -- Initialize API return status to success
1751 x_return_status := FND_API.G_RET_STS_SUCCESS;
1752
1753 IF (p_trxn_attributes_rec.payer_legal_entity_id IS NULL) THEN
1754 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1755 print_debuginfo(l_module_name,'Error: Mandatory Parameter ''First party legal entity Id'' missing.');
1756 END IF;
1757 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
1758 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_1PARTY_LE_ID'));
1759 FND_MSG_PUB.Add;
1760 RAISE FND_API.G_EXC_ERROR;
1761 END IF;
1762
1763 OPEN payer_country_csr(p_trxn_attributes_rec.payer_legal_entity_id);
1764 FETCH payer_country_csr INTO l_payer_country;
1765 CLOSE payer_country_csr;
1766
1767 IF (l_payer_country IS NULL) THEN
1768 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1769 print_debuginfo(l_module_name,'Error: First party country Not populated.');
1770 END IF;
1771 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_DATA');
1772 FND_MESSAGE.SET_TOKEN('PARAM', 'First party country');
1773 FND_MSG_PUB.Add;
1774 RAISE FND_API.G_EXC_ERROR;
1775 END IF;
1776
1777 OPEN payment_reason_csr(l_payer_country);
1778 FETCH payment_reason_csr BULK COLLECT INTO l_payment_reason_tbl;
1779 CLOSE payment_reason_csr;
1780
1781 IF (l_payment_reason_tbl.COUNT = 0) THEN
1782 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1783 print_debuginfo(l_module_name,'Warning: No Payment Reasons Applicable');
1784 END IF;
1785 ELSE
1786 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1787 print_debuginfo(l_module_name,'Applicable Payment Reasons Count : '|| l_payment_reason_tbl.COUNT);
1788 END IF;
1789 x_payment_reason_tbl := l_payment_reason_tbl;
1790 END IF;
1791
1792 -- End of API body.
1793
1794 -- Standard call to get message count and if count is 1, get message info.
1795 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1796
1797 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1798 print_debuginfo(l_module_name, 'RETURN');
1799
1800 END IF;
1801 EXCEPTION
1802 WHEN FND_API.G_EXC_ERROR THEN
1803 x_return_status := FND_API.G_RET_STS_ERROR;
1804 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1805 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1806 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
1807 print_debuginfo(l_module_name,'SQLerr is :'
1808 || substr(SQLERRM, 1, 150));
1809 END IF;
1810 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1811 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1812 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1813 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1814 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
1815 print_debuginfo(l_module_name,'SQLerr is :'
1816 || substr(SQLERRM, 1, 150));
1817 END IF;
1818 WHEN OTHERS THEN
1819 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1820
1821 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
1822 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
1823 END IF;
1824 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
1825 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
1826 print_debuginfo(l_module_name,'SQLerr is :'
1827 || substr(SQLERRM, 1, 150));
1828 END IF;
1829 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
1830
1831 END Get_Applicable_Payment_Reasons;
1832
1833 -- Start of comments
1834 -- API name : Get_Default_Payment_Attributes
1835 -- Type : Public
1836 -- Pre-reqs : None.
1837 -- Function : get the default values of all Payment attributes.
1838 -- Parameters :
1839 -- IN : p_api_version IN NUMBER Required
1840 -- p_application_id IN NUMBER Required
1841 -- p_init_msg_list IN VARCHAR2 Optional
1842 -- p_trxn_attributes_rec IN Trxn_Attributes_Rec_Type Required
1843 -- OUT : x_return_status OUT VARCHAR2 Required
1844 -- x_msg_count OUT NUMBER Required
1845 -- x_msg_data OUT VARCHAR2 Required
1846 -- x_default_pmt_attrs_rec OUT Default_Pmt_Attrs_Rec_Type Required
1847 --
1848 -- Version : Current version 1.0
1849 -- Previous version None
1850 -- Initial version 1.0
1851 -- End of comments
1852
1853 PROCEDURE Get_Default_Payment_Attributes(
1854 p_api_version IN NUMBER,
1855 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
1856 p_ignore_payee_pref IN VARCHAR2,
1857 p_trxn_attributes_rec IN Trxn_Attributes_Rec_Type,
1858 x_return_status OUT NOCOPY VARCHAR2,
1859 x_msg_count OUT NOCOPY NUMBER,
1860 x_msg_data OUT NOCOPY VARCHAR2,
1861 x_default_pmt_attrs_rec OUT NOCOPY Default_Pmt_Attrs_Rec_Type
1862 )
1863 IS
1864 l_api_name CONSTANT VARCHAR2(30) := 'Get_Default_Payment_Attributes';
1865 l_api_version CONSTANT NUMBER := 1.0;
1866 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Get_Default_Payment_Attributes';
1867
1868 l_payee_override_flag VARCHAR2(1);
1869
1870 l_payment_method_rec Payment_Method_Rec_Type;
1871 l_payment_format_rec Payment_Format_Rec_Type;
1872 l_payee_bankaccount_rec Payee_BankAccount_Rec_Type;
1873 l_payment_reason_rec Payment_Reason_Rec_Type;
1874 l_delivery_channel_rec Delivery_Channel_Rec_Type;
1875 l_bank_charge_bearer Bank_Charge_Bearer_Rec_Type;
1876 l_settlement_priority Settlement_Priority_Rec_Type;
1877
1878 l_pay_alone VARCHAR2(1);
1879 l_payment_reason_comments VARCHAR2(240);
1880
1881 l_payee1 IBY_EXTERNAL_PAYEES_ALL.ext_payee_id%TYPE;
1882 l_payee2 IBY_EXTERNAL_PAYEES_ALL.ext_payee_id%TYPE;
1883 l_payee3 IBY_EXTERNAL_PAYEES_ALL.ext_payee_id%TYPE;
1884 l_payee4 IBY_EXTERNAL_PAYEES_ALL.ext_payee_id%TYPE;
1885
1886 l_key VARCHAR2(2000);
1887
1888
1889 CURSOR payee_override_ent_cur IS
1890 SELECT payment_method_at_payee_flag
1891 FROM IBY_INTERNAL_PAYERS_ALL
1892 WHERE org_id is null;
1893
1894 CURSOR payee_override_org_cur(p_org_id NUMBER,
1895 p_org_type VARCHAR2) IS
1896 SELECT payment_method_at_payee_flag
1897 FROM IBY_INTERNAL_PAYERS_ALL
1898 WHERE org_id = p_org_id
1899 AND org_type = p_org_type;
1900
1901 /*
1902 * OBSOLETE:
1903 *
1904 * Left here for reference purposes.
1905 *
1906 * This cursor has been split into two parts;
1907 * payee_defaults_curA1 and payee_defaults_curB1
1908 * below are it's replacements.
1909 *
1910 * The split has been done to improve performance
1911 * and legibility.
1912 */
1913 CURSOR payee_defaults_cur (p_payee_party_id NUMBER,
1914 p_payee_party_site_id NUMBER,
1915 p_supplier_site_id NUMBER,
1916 p_org_id NUMBER,
1917 p_org_type VARCHAR2,
1918 p_payment_function VARCHAR2,
1919 p_Pay_Proc_Trxn_Type_Code VARCHAR2,
1920 p_payer_le_id NUMBER) IS
1921 SELECT pm.payment_method_code,
1922 m.payment_method_name,
1923 m.SUPPORT_BILLS_PAYABLE_FLAG,
1924 m.MATURITY_DATE_OFFSET_DAYS,
1925 payee.payment_format_code,
1926 f.format_name,
1927 payee.bank_charge_bearer,
1928 payee.delivery_channel_code,
1929 d.meaning delivery_channel_meaning,
1930 d.description delivery_channel_description,
1931 payee.payment_reason_code,
1932 r.meaning payment_reason,
1933 r.description payment_reason_description,
1934 payee.payment_reason_comments,
1935 payee.exclusive_payment_flag,
1936 payee.settlement_priority,
1937 d.territory_code delivery_channel_country,
1938 r.territory_code payment_reason_country
1939 FROM IBY_EXTERNAL_PAYEES_ALL payee,
1940 IBY_EXT_PARTY_PMT_MTHDS pm,
1941 IBY_PAYMENT_METHODS_VL m,
1942 IBY_FORMATS_VL f,
1943 IBY_DELIVERY_CHANNELS_VL d,
1944 IBY_PAYMENT_REASONS_VL r,
1945 IBY_APPLICABLE_PMT_MTHDS am1,
1946 IBY_APPLICABLE_PMT_MTHDS am2,
1947 IBY_APPLICABLE_PMT_MTHDS am3
1948 WHERE payee.payee_party_id = p_payee_party_id
1949 AND am1.PAYMENT_METHOD_CODE(+) = m.PAYMENT_METHOD_CODE
1950 AND am1.PAYMENT_FLOW(+) = 'DISBURSEMENTS'
1951 AND am1.APPLICABLE_TYPE_CODE(+) = 'PAY_PROC_TRXN_TYPE'
1952 AND (am1.APPLICABLE_VALUE_TO is null OR
1953 am1.APPLICABLE_VALUE_TO=p_Pay_Proc_Trxn_Type_Code )
1954 AND (am1.INACTIVE_DATE is null OR am1.INACTIVE_DATE >= trunc(sysdate))
1955 AND am2.PAYMENT_METHOD_CODE(+) = am1.PAYMENT_METHOD_CODE
1956 AND am2.APPLICABLE_TYPE_CODE(+) = 'PAYER_LE'
1957 AND (am2.APPLICABLE_VALUE_TO is null OR am2.APPLICABLE_VALUE_TO=p_payer_le_id )
1958 AND (am2.INACTIVE_DATE is null OR am2.INACTIVE_DATE >= trunc(sysdate))
1959 AND am3.PAYMENT_METHOD_CODE(+) = am2.PAYMENT_METHOD_CODE
1960 AND am3.APPLICABLE_TYPE_CODE(+) = 'PAYER_ORG'
1961 AND (am3.APPLICABLE_VALUE_TO is null OR am3.APPLICABLE_VALUE_TO=p_org_id )
1962 AND (am3.INACTIVE_DATE is null OR am3.INACTIVE_DATE >= trunc(sysdate))
1963 AND payee.payment_function = p_payment_function
1964 AND payee.ext_payee_id = pm.ext_pmt_party_id(+)
1965 AND pm.payment_method_code = m.payment_method_code(+)
1966 AND pm.payment_function(+) = p_payment_function
1967 AND pm.primary_flag(+) = 'Y'
1968 AND (pm.inactive_date is null OR pm.inactive_date >= trunc(sysdate))
1969 AND (m.inactive_date is null OR m.inactive_date >= trunc(sysdate))
1970 AND payee.payment_format_code = f.format_code(+)
1971 AND payee.delivery_channel_code = d.delivery_channel_code(+)
1972 AND payee.payment_reason_code = r.payment_reason_code(+)
1973 AND (payee.org_id is NULL
1974 OR (payee.org_id = p_org_id AND payee.org_type = p_org_type))
1975 AND (payee.party_site_id is NULL OR payee.party_site_id = p_payee_party_site_id)
1976 AND (payee.supplier_site_id is NULL OR payee.supplier_site_id = p_supplier_site_id)
1977 ORDER by payee.supplier_site_id,
1978 payee.party_site_id,
1979 payee.org_id;
1980
1981 /*
1982 * Fix for performance bug 5548886:
1983 *
1984 * Use the ext payee id as the key to drive this
1985 * cursor as it will significantly improve
1986 * performance and improve maintainability.
1987 */
1988 CURSOR payee_defaults_curA1 (
1989 p_payee_party_id NUMBER,
1990 p_payee_party_site_id NUMBER,
1991 p_supplier_site_id NUMBER,
1992 p_org_id NUMBER,
1993 p_org_type VARCHAR2,
1994 p_payment_function VARCHAR2,
1995 p_Pay_Proc_Trxn_Type_Code VARCHAR2,
1996 p_payer_le_id NUMBER,
1997 p_payee1 NUMBER,
1998 p_payee2 NUMBER,
1999 p_payee3 NUMBER,
2000 p_payee4 NUMBER
2001 )
2002 IS
2003 SELECT
2004 payee.payment_format_code,
2005 f.format_name,
2006 payee.bank_charge_bearer,
2007 payee.delivery_channel_code,
2008 d.meaning delivery_channel_meaning,
2009 d.description delivery_channel_description,
2010 payee.payment_reason_code,
2011 r.meaning payment_reason,
2012 r.description payment_reason_description,
2013 payee.payment_reason_comments,
2014 payee.exclusive_payment_flag,
2015 payee.settlement_priority,
2016 d.territory_code delivery_channel_country,
2017 r.territory_code payment_reason_country
2018 FROM
2019 IBY_EXTERNAL_PAYEES_ALL payee,
2020 IBY_FORMATS_VL f,
2021 IBY_DELIVERY_CHANNELS_VL d,
2022 IBY_PAYMENT_REASONS_VL r
2023 WHERE
2024 payee.ext_payee_id IN
2025 (
2026 p_payee1,
2027 p_payee2,
2028 p_payee3,
2029 p_payee4
2030 )
2031 AND payee.payment_format_code = f.format_code(+)
2032 AND payee.delivery_channel_code = d.delivery_channel_code(+)
2033 AND payee.payment_reason_code = r.payment_reason_code(+)
2034 ORDER BY
2035 payee.supplier_site_id,
2036 payee.party_site_id,
2037 payee.org_id
2038 ;
2039
2040 /*
2041 * Fix for performance bug 5548886:
2042 *
2043 * Use the ext payee id as the key to drive this
2044 * cursor as it will significantly improve
2045 * performance and improve maintainability.
2046 */
2047 CURSOR payee_defaults_curB1 (
2048 p_payee_party_id NUMBER,
2049 p_payee_party_site_id NUMBER,
2050 p_supplier_site_id NUMBER,
2051 p_org_id NUMBER,
2052 p_org_type VARCHAR2,
2053 p_payment_function VARCHAR2,
2054 p_Pay_Proc_Trxn_Type_Code VARCHAR2,
2055 p_payer_le_id NUMBER,
2056 p_payee1 NUMBER,
2057 p_payee2 NUMBER,
2058 p_payee3 NUMBER,
2059 p_payee4 NUMBER
2060 )
2061 IS
2062 SELECT
2063 pm.payment_method_code,
2064 m.payment_method_name,
2065 m.support_bills_payable_flag,
2066 m.maturity_date_offset_days
2067 FROM
2068 IBY_EXTERNAL_PAYEES_ALL payee,
2069 IBY_EXT_PARTY_PMT_MTHDS pm,
2070 IBY_PAYMENT_METHODS_VL m
2071 WHERE
2072 payee.ext_payee_id IN (
2073 p_payee1,
2074 p_payee2,
2075 p_payee3,
2076 p_payee4
2077 )
2078 AND payee.payment_function = p_payment_function
2079 AND payee.ext_payee_id = pm.ext_pmt_party_id
2080 AND pm.payment_method_code = m.payment_method_code
2081 AND pm.payment_function = p_payment_function
2082 AND pm.primary_flag = 'Y'
2083 AND (pm.inactive_date IS NULL OR pm.inactive_date >= trunc(sysdate))
2084 AND (m.inactive_date IS NULL OR m.inactive_date >= trunc(sysdate))
2085 AND EXISTS (SELECT 1 FROM IBY_APPLICABLE_PMT_MTHDS am1
2086 WHERE am1.payment_method_code = m.payment_method_code
2087 AND am1.payment_flow(+) = 'DISBURSEMENTS'
2088 AND am1.applicable_type_code = 'PAY_PROC_TRXN_TYPE'
2089 AND (am1.applicable_value_to IS NULL OR
2090 am1.applicable_value_to=p_pay_proc_trxn_type_code)
2091 AND (am1.inactive_date IS null OR
2092 am1.inactive_date >= trunc(sysdate)))
2093 AND EXISTS (SELECT 1 FROM IBY_APPLICABLE_PMT_MTHDS am2
2094 WHERE am2.payment_method_code = m.payment_method_code
2095 AND am2.applicable_type_code = 'PAYER_LE'
2096 AND (am2.applicable_value_to IS NULL OR
2097 am2.applicable_value_to=p_payer_le_id)
2098 AND (am2.inactive_date IS NULL OR
2099 am2.inactive_date >= trunc(sysdate)))
2100 AND EXISTS (SELECT 1 FROM IBY_APPLICABLE_PMT_MTHDS am3
2101 WHERE am3.payment_method_code = m.payment_method_code
2102 AND am3.applicable_type_code = 'PAYER_ORG'
2103 AND (am3.applicable_value_to IS NULL OR
2104 am3.applicable_value_to=p_org_id)
2105 AND (am3.inactive_date IS NULL OR
2106 am3.inactive_date >= trunc(sysdate))
2107 )
2108 ORDER BY
2109 payee.supplier_site_id,
2110 payee.party_site_id,
2111 payee.org_id
2112 ;
2113
2114
2115 CURSOR payee_bankaccount_cur(p_payee_party_id NUMBER,
2116 p_payee_party_site_id NUMBER,
2117 p_supplier_site_id NUMBER,
2118 p_payer_org_id NUMBER,
2119 p_payer_org_type VARCHAR2,
2120 p_payment_function VARCHAR2,
2121 p_payment_currency VARCHAR2) IS
2122 SELECT b.bank_account_name,
2123 b.ext_bank_account_id,
2124 b.bank_account_number,
2125 b.currency_code,
2126 b.iban_number,
2127 b.bank_name,
2128 b.bank_number,
2129 b.bank_branch_name,
2130 b.branch_number,
2131 b.country_code,
2132 b.alternate_account_name,
2133 b.bank_account_type,
2134 b.account_suffix,
2135 b.description,
2136 b.foreign_payment_use_flag,
2137 b.payment_factor_flag,
2138 b.eft_swift_code
2139 FROM IBY_PMT_INSTR_USES_ALL ibyu,
2140 IBY_EXT_BANK_ACCOUNTS_V b,
2141 IBY_EXTERNAL_PAYEES_ALL ibypayee
2142 WHERE ibyu.instrument_id = b.ext_bank_account_id
2143 AND ibyu.instrument_type = 'BANKACCOUNT'
2144 AND (b.currency_code = p_payment_currency
2145 OR b.currency_code is null)
2146 AND ibyu.payment_function = p_payment_function
2147 AND ibyu.ext_pmt_party_id = ibypayee.ext_payee_id
2148 AND ibypayee.payee_party_id = p_payee_party_id
2149 AND trunc(sysdate) between NVL(trunc(ibyu.start_date),trunc(sysdate)) AND
2150 NVL(trunc(ibyu.end_date-1),trunc(sysdate))
2151 AND trunc(sysdate) between NVL(trunc(b.start_date),trunc(sysdate)) AND
2152 NVL(trunc(b.end_date-1),trunc(sysdate))
2153 AND (ibypayee.party_site_id is NULL OR ibypayee.party_site_id = p_payee_party_site_id)
2154 AND (ibypayee.supplier_site_id is NULL OR ibypayee.supplier_site_id = p_supplier_site_id)
2155 AND (ibypayee.org_id is null
2156 OR (ibypayee.org_id = p_payer_org_id AND ibypayee.org_type = p_payer_org_type) )
2157
2158 /*
2159 * Fix for bug 5505267:
2160 *
2161 * Add payment flow predicate to improve
2162 * performance.
2163 */
2164 AND ibyu.payment_flow='DISBURSEMENTS'
2165 ORDER by ibypayee.supplier_site_id,
2166 ibypayee.party_site_id,
2167 ibypayee.org_id,
2168 ibyu.order_of_preference;
2169
2170 CURSOR get_fnd_lookup(p_lookup_type VARCHAR2,
2171 p_lookup_code VARCHAR2)
2172 IS
2173 SELECT lookup_code,
2174 meaning,
2175 description
2176 FROM FND_LOOKUP_VALUES_VL
2177 WHERE lookup_type = p_lookup_type
2178 AND lookup_code = p_lookup_code
2179 AND (trunc(sysdate) BETWEEN NVL(start_date_active,trunc(sysdate)) AND
2180 NVL(end_date_active,trunc(sysdate)));
2181
2182 BEGIN
2183
2184 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2185 print_debuginfo(l_module_name,'ENTER');
2186 print_debuginfo(l_module_name,'Application_id : '|| p_trxn_attributes_rec.application_id);
2187 print_debuginfo(l_module_name,'1st party LE id : '|| p_trxn_attributes_rec.payer_legal_entity_id);
2188 print_debuginfo(l_module_name,'Org Id : '|| p_trxn_attributes_rec.payer_org_id);
2189 print_debuginfo(l_module_name,'Org Type : '|| p_trxn_attributes_rec.payer_org_type);
2190 print_debuginfo(l_module_name,'Payer Id : '|| p_trxn_attributes_rec.payee_party_id);
2191 print_debuginfo(l_module_name,'Payee Site Id : '|| p_trxn_attributes_rec.payee_party_site_id);
2192 print_debuginfo(l_module_name,'Supplier Site Id : '|| p_trxn_attributes_rec.supplier_site_id);
2193 print_debuginfo(l_module_name,'Trxn Type Code : '|| p_trxn_attributes_rec.pay_proc_trxn_type_code);
2194 print_debuginfo(l_module_name,'Payment Currency : '|| p_trxn_attributes_rec.payment_currency);
2195 print_debuginfo(l_module_name,'Payment Amount : '|| p_trxn_attributes_rec.payment_amount);
2196 print_debuginfo(l_module_name,'Payment Function : '|| p_trxn_attributes_rec.payment_function);
2197
2198 END IF;
2199 -- Standard call to check for call compatibility.
2200 IF NOT FND_API.Compatible_API_Call (l_api_version,
2201 p_api_version,
2202 l_api_name,
2203 G_PKG_NAME)
2204 THEN
2205 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2206 END IF;
2207
2208 -- Initialize message list if p_init_msg_list is set to TRUE.
2209 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2210 FND_MSG_PUB.initialize;
2211 END IF;
2212
2213 -- Initialize API return status to success
2214 x_return_status := FND_API.G_RET_STS_SUCCESS;
2215
2216
2217 /*
2218 * Fix for bug 5682499:
2219 *
2220 * Payment methods can be set up at the supplier level or
2221 * the supplier site level.
2222 *
2223 * If no payment method is setup at the supplier site level
2224 * then the payment method at the supplier level should be
2225 * used for defaulting.
2226 *
2227 * This means that we need to pick up two possible ext
2228 * payee ids - one ext payee id with exact context including
2229 * supplier site id, and one ext payee id with partial context
2230 * where supplier site id is null.
2231 *
2232 * The only purpose of the second ext payee id is to pick up
2233 * payment methods that are defaulted at the supplier level.
2234 *
2235 * The where clause below uses the IN syntax to pick up
2236 * these two ext payee ids.
2237 */
2238
2239 /*
2240 * Update:
2241 *
2242 * We need to pick up all possible ext payee ids based on the
2243 * following chart. This means that there can be upto 4 possible
2244 * ext payee ids in the select statement.
2245 *
2246 * EXT PARTY ID PRECEDENCE CHART
2247 * -------------------------------------------------------------
2248 * | | | | |
2249 * | supp | org | party | party |
2250 * | site | | site | |
2251 * -------------------------------------------------------------
2252 * | | | | |
2253 * supp site | Y | Y | Y | Y |
2254 * | | | | |
2255 * -------------------------------------------------------------
2256 * | | | | |
2257 * party site (org) | null | Y | Y | Y |
2258 * | | | | |
2259 * -------------------------------------------------------------
2260 * | | | | |
2261 * party site (no org) | null | null | Y | Y |
2262 * | | | | |
2263 * -------------------------------------------------------------
2264 * | | | | |
2265 * party | null | null | null | Y |
2266 * | | | | |
2267 * --------------------------------------------------------------
2268 *
2269 * Y = value provided, null = no value provided
2270 *
2271 * Matching by supp site has the highest precedence
2272 * Matching by party alone has the lowest precedence
2273 *
2274 */
2275
2276 /*
2277 * exact context:
2278 * supplier site level
2279 */
2280 l_payee1 := IBY_DISBURSE_SUBMIT_PUB_PKG.deriveExactPayeeIdFromContext(
2281 p_trxn_attributes_rec.Payee_Party_Id,
2282 p_trxn_attributes_rec.Payee_Party_Site_Id,
2283 p_trxn_attributes_rec.Supplier_Site_Id,
2284 p_trxn_attributes_rec.Payer_Org_Id,
2285 p_trxn_attributes_rec.Payer_Org_Type,
2286 p_trxn_attributes_rec.Payment_Function
2287 );
2288
2289 /*
2290 * partial context:
2291 * party site level with org
2292 */
2293 l_payee2 := IBY_DISBURSE_SUBMIT_PUB_PKG.deriveExactPayeeIdFromContext(
2294 p_trxn_attributes_rec.Payee_Party_Id,
2295 p_trxn_attributes_rec.Payee_Party_Site_Id,
2296 null,
2297 p_trxn_attributes_rec.Payer_Org_Id,
2298 p_trxn_attributes_rec.Payer_Org_Type,
2299 p_trxn_attributes_rec.Payment_Function
2300 );
2301
2302
2303 /*
2304 * partial context:
2305 * party site level without org
2306 */
2307 l_payee3 := IBY_DISBURSE_SUBMIT_PUB_PKG.deriveExactPayeeIdFromContext(
2308 p_trxn_attributes_rec.Payee_Party_Id,
2309 p_trxn_attributes_rec.Payee_Party_Site_Id,
2310 null,
2311 null,
2312 null,
2313 p_trxn_attributes_rec.Payment_Function
2314 );
2315
2316
2317 /*
2318 * partial context:
2319 * party level
2320 */
2321 l_payee4 := IBY_DISBURSE_SUBMIT_PUB_PKG.deriveExactPayeeIdFromContext(
2322 p_trxn_attributes_rec.Payee_Party_Id,
2323 null,
2324 null,
2325 null,
2326 null,
2327 p_trxn_attributes_rec.Payment_Function
2328 );
2329
2330
2331 -- Start of API body
2332 FOR payee_defaults_rec in payee_defaults_curA1(
2333 p_trxn_attributes_rec.Payee_Party_Id,
2334 p_trxn_attributes_rec.Payee_Party_Site_Id,
2335 p_trxn_attributes_rec.Supplier_Site_Id,
2336 p_trxn_attributes_rec.Payer_Org_Id,
2337 p_trxn_attributes_rec.Payer_Org_Type,
2338 p_trxn_attributes_rec.payment_function,
2339 p_trxn_attributes_rec.Pay_Proc_Trxn_Type_Code,
2340 p_trxn_attributes_rec.payer_legal_entity_id,
2341 l_payee1,
2342 l_payee2,
2343 l_payee3,
2344 l_payee4
2345 )
2346 LOOP
2347
2348 -- Payment Format
2349 IF (l_payment_format_rec.Payment_Format_Code is NULL) THEN
2350 l_payment_format_rec.Payment_Format_Name := payee_defaults_rec.format_name;
2351 l_payment_format_rec.Payment_Format_Code := payee_defaults_rec.payment_format_code;
2352 END IF;
2353
2354 -- Payment Reason
2355 IF (l_payment_reason_rec.Code is NULL) THEN
2356 -- bug 4880032
2357 l_payment_reason_rec.Code := payee_defaults_rec.payment_reason_code;
2358 l_payment_reason_rec.Meaning := payee_defaults_rec.payment_reason;
2359 l_payment_reason_rec.Description := payee_defaults_rec.payment_reason_description;
2360 l_payment_reason_rec.Country := payee_defaults_rec.payment_reason_country;
2361 END IF;
2362
2363
2364 -- Delivery Channel
2365 IF (l_delivery_channel_rec.Code is NULL) THEN
2366 l_delivery_channel_rec.Code := payee_defaults_rec.delivery_channel_code;
2367 l_delivery_channel_rec.Meaning := payee_defaults_rec.delivery_channel_meaning;
2368 l_delivery_channel_rec.Description := payee_defaults_rec.delivery_channel_description;
2369 l_delivery_channel_rec.Country := payee_defaults_rec.delivery_channel_Country;
2370
2371 END IF;
2372
2373 -- Bank Charge Bearer
2374 IF (l_bank_charge_bearer.Code is NULL) THEN
2375 l_bank_charge_bearer.Code := payee_defaults_rec.bank_charge_bearer;
2376
2377 END IF;
2378
2379 -- Pay Alone Flag
2380 IF (l_pay_alone is NULL) THEN
2381 l_pay_alone := payee_defaults_rec.exclusive_payment_flag;
2382 END IF;
2383
2384 -- Payment reason comments
2385
2386 IF(l_payment_reason_comments is NULL) THEN
2387 l_payment_reason_comments :=payee_defaults_rec.payment_reason_comments;
2388 END IF;
2389
2390 -- Express Payment Flag
2391 IF (l_settlement_priority.code is NULL) THEN
2392 l_settlement_priority.code := payee_defaults_rec.settlement_priority;
2393 END IF;
2394
2395 END LOOP;
2396
2397 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2398 print_debuginfo(l_module_name,'Default Payment Format Name : '|| l_payment_format_rec.Payment_Format_Name);
2399 print_debuginfo(l_module_name,'Default Payment Reason Code : '|| l_payment_reason_rec.Code);
2400 print_debuginfo(l_module_name,'Default Delivery Channel Code : '|| l_delivery_channel_rec.Code);
2401 print_debuginfo(l_module_name,'Default Bank Charge Bearer : '|| l_bank_charge_bearer.Code);
2402 print_debuginfo(l_module_name,'Default Exclusive Payment Flag : '|| l_pay_alone);
2403 print_debuginfo(l_module_name,'Default Settlement Priority : '|| l_settlement_priority.code);
2404 print_debuginfo(l_module_name,'Default Payment Reason Comments : '|| l_payment_reason_comments);
2405
2406 END IF;
2407 -- Start of API body
2408 FOR payee_defaults_rec in payee_defaults_curB1(
2409 p_trxn_attributes_rec.Payee_Party_Id,
2410 p_trxn_attributes_rec.Payee_Party_Site_Id,
2411 p_trxn_attributes_rec.Supplier_Site_Id,
2412 p_trxn_attributes_rec.Payer_Org_Id,
2413 p_trxn_attributes_rec.Payer_Org_Type,
2414 p_trxn_attributes_rec.payment_function,
2415 p_trxn_attributes_rec.Pay_Proc_Trxn_Type_Code,
2416 p_trxn_attributes_rec.payer_legal_entity_id,
2417 l_payee1,
2418 l_payee2,
2419 l_payee3,
2420 l_payee4
2421 )
2422 LOOP
2423
2424 -- Payment Method
2425 IF (l_payment_method_rec.Payment_Method_Name is NULL) THEN
2426 l_payment_method_rec.Payment_Method_Name := payee_defaults_rec.payment_method_name;
2427 l_payment_method_rec.Payment_Method_Code := payee_defaults_rec.payment_method_code;
2428 l_payment_method_rec.Bill_Payable_Flag := payee_defaults_rec.support_bills_payable_flag;
2429 l_payment_method_rec.Maturity_Date_Offset := payee_defaults_rec.maturity_date_offset_days;
2430 END IF;
2431
2432 END LOOP;
2433
2434 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2435 print_debuginfo(l_module_name,'Default Payment Method Name : '|| l_payment_method_rec.Payment_Method_Name);
2436
2437 END IF;
2438 -- Get Default Bank Charge Bearer record
2439 OPEN get_fnd_lookup(BANK_CHARGE_BEARER_LOOKUP,l_bank_charge_bearer.Code);
2440 FETCH get_fnd_lookup into l_bank_charge_bearer;
2441 CLOSE get_fnd_lookup;
2442
2443 -- Get Default Express Payment record
2444 OPEN get_fnd_lookup(SETTLEMENT_PRIORITY_LOOKUP,l_settlement_priority.code);
2445 FETCH get_fnd_lookup into l_settlement_priority;
2446 CLOSE get_fnd_lookup;
2447
2448 -- Change the default payment method according to the override setup
2449 OPEN payee_override_org_cur(p_trxn_attributes_rec.Payer_Org_Id,
2450 p_trxn_attributes_rec.Payer_Org_Type);
2451 FETCH payee_override_org_cur into l_payee_override_flag;
2452 CLOSE payee_override_org_cur;
2453
2454 IF (l_payee_override_flag is null) THEN
2455 OPEN payee_override_ent_cur;
2456 FETCH payee_override_ent_cur into l_payee_override_flag;
2457 CLOSE payee_override_ent_cur;
2458 END IF;
2459
2460 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2461 print_debuginfo(l_module_name,'Payee OverRide Flag : '|| l_payee_override_flag);
2462
2463 END IF;
2464 /* Bug 6045110: When Allow Payee Override system option is set to 'Y',
2465 but, payment method is not set at supplier or supplier site level,
2466 it is expected that payment method needs to be defaulted from the
2467 defaulting rules.
2468 */
2469 IF (p_ignore_payee_pref = 'Y'
2470 OR (l_payee_override_flag = 'Y' AND l_payment_method_rec.Payment_Method_Code is null) -- Bug 6045110
2471 OR l_payee_override_flag <> 'Y') THEN
2472 -- Initialize the payment method record as it will be purely rule-based
2473 l_payment_method_rec.Payment_Method_Name := null;
2474 l_payment_method_rec.Payment_Method_Code := null;
2475 l_payment_method_rec.Bill_Payable_Flag := null;
2476 l_payment_method_rec.Maturity_Date_Offset := null;
2477 evaluate_Rule_Based_Default(p_trxn_attributes_rec,l_payment_method_rec);
2478 END IF;
2479
2480 -- Get Default Payee BankAccount cursor
2481 l_key := p_trxn_attributes_rec.Payee_Party_Id ||'$'|| p_trxn_attributes_rec.Payee_Party_Site_Id ||'$'|| p_trxn_attributes_rec.Supplier_Site_Id ||'$'|| p_trxn_attributes_rec.Payer_Org_Id ||'$'|| p_trxn_attributes_rec.Payer_Org_Type;
2482 l_key := l_key ||'$'|| p_trxn_attributes_rec.Payment_Currency ||'$'|| p_trxn_attributes_rec.Payment_Function;
2483 -- Start of API body.
2484 IF (NOT(t_payee_bankacct_tbl.EXISTS( l_key ))) THEN
2485
2486 OPEN payee_bankaccount_cur(p_trxn_attributes_rec.Payee_Party_Id,
2487 p_trxn_attributes_rec.Payee_Party_Site_Id,
2488 p_trxn_attributes_rec.Supplier_Site_Id,
2489 p_trxn_attributes_rec.Payer_Org_Id,
2490 p_trxn_attributes_rec.Payer_Org_Type,
2491 p_trxn_attributes_rec.Payment_Function,
2492 p_trxn_attributes_rec.Payment_Currency);
2493 FETCH payee_bankaccount_cur INTO l_payee_bankaccount_rec;
2494 CLOSE payee_bankaccount_cur;
2495 t_payee_bankacct_tbl(l_key).Payee_Party_Id := p_trxn_attributes_rec.Payee_Party_Id;
2496 t_payee_bankacct_tbl(l_key).Payee_Party_Site_Id := p_trxn_attributes_rec.Payee_Party_Site_Id;
2497 t_payee_bankacct_tbl(l_key).Supplier_Site_Id := p_trxn_attributes_rec.Payer_Org_Id;
2498 t_payee_bankacct_tbl(l_key).Payee_Org_Id := p_trxn_attributes_rec.Payer_Org_Id;
2499 t_payee_bankacct_tbl(l_key).Payee_Org_Type := p_trxn_attributes_rec.Payer_Org_Type;
2500 t_payee_bankacct_tbl(l_key).Payment_Currency := p_trxn_attributes_rec.Payment_Currency;
2501 t_payee_bankacct_tbl(l_key).Payment_Function := p_trxn_attributes_rec.Payment_Function;
2502 t_payee_bankacct_tbl(l_key).Payee_BankAccount_Name := l_payee_bankaccount_rec.Payee_BankAccount_Name;
2503 t_payee_bankacct_tbl(l_key).Payee_BankAccount_Id := l_payee_bankaccount_rec.Payee_BankAccount_Id ;
2504 t_payee_bankacct_tbl(l_key).Payee_BankAccount_Num := l_payee_bankaccount_rec.Payee_BankAccount_Num ;
2505 t_payee_bankacct_tbl(l_key).Currency_Code := l_payee_bankaccount_rec.Currency_Code ;
2506 t_payee_bankacct_tbl(l_key).IBAN := l_payee_bankaccount_rec.IBAN ;
2507 t_payee_bankacct_tbl(l_key).Payee_BankName := l_payee_bankaccount_rec.Payee_BankName ;
2508 t_payee_bankacct_tbl(l_key).Payee_BankNumber := l_payee_bankaccount_rec.Payee_BankNumber ;
2509 t_payee_bankacct_tbl(l_key).Payee_BranchName := l_payee_bankaccount_rec.Payee_BranchName ;
2510 t_payee_bankacct_tbl(l_key).Payee_BranchNumber := l_payee_bankaccount_rec.Payee_BranchNumber ;
2511 t_payee_bankacct_tbl(l_key).Bank_Country := l_payee_bankaccount_rec.Bank_Country ;
2512 t_payee_bankacct_tbl(l_key).Alter_BankAccount_Name := l_payee_bankaccount_rec.Alter_BankAccount_Name;
2513 t_payee_bankacct_tbl(l_key).BankAccount_Type := l_payee_bankaccount_rec.BankAccount_Type ;
2514 t_payee_bankacct_tbl(l_key).BankAccount_Suffix := l_payee_bankaccount_rec.BankAccount_Suffix ;
2515 t_payee_bankacct_tbl(l_key).BankAccount_Desc := l_payee_bankaccount_rec.BankAccount_Desc ;
2516 t_payee_bankacct_tbl(l_key).Foreign_PayUse_Flag := l_payee_bankaccount_rec.Foreign_PayUse_Flag ;
2517 t_payee_bankacct_tbl(l_key).Pay_Factor_Flag := l_payee_bankaccount_rec.Pay_Factor_Flag ;
2518 t_payee_bankacct_tbl(l_key).EFT_Swift_Code := l_payee_bankaccount_rec.EFT_Swift_Code ;
2519
2520 ELSE
2521 l_payee_bankaccount_rec.Payee_BankAccount_Name := t_payee_bankacct_tbl(l_key).Payee_BankAccount_Name;
2522 l_payee_bankaccount_rec.Payee_BankAccount_Id := t_payee_bankacct_tbl(l_key).Payee_BankAccount_Id ;
2523 l_payee_bankaccount_rec.Payee_BankAccount_Num := t_payee_bankacct_tbl(l_key).Payee_BankAccount_Num ;
2524 l_payee_bankaccount_rec.Currency_Code := t_payee_bankacct_tbl(l_key).Currency_Code ;
2525 l_payee_bankaccount_rec.IBAN := t_payee_bankacct_tbl(l_key).IBAN ;
2526 l_payee_bankaccount_rec.Payee_BankName := t_payee_bankacct_tbl(l_key).Payee_BankName ;
2527 l_payee_bankaccount_rec.Payee_BankNumber := t_payee_bankacct_tbl(l_key).Payee_BankNumber ;
2528 l_payee_bankaccount_rec.Payee_BranchName := t_payee_bankacct_tbl(l_key).Payee_BranchName ;
2529 l_payee_bankaccount_rec.Payee_BranchNumber := t_payee_bankacct_tbl(l_key).Payee_BranchNumber ;
2530 l_payee_bankaccount_rec.Bank_Country := t_payee_bankacct_tbl(l_key).Bank_Country ;
2531 l_payee_bankaccount_rec.Alter_BankAccount_Name := t_payee_bankacct_tbl(l_key).Alter_BankAccount_Name;
2532 l_payee_bankaccount_rec.BankAccount_Type := t_payee_bankacct_tbl(l_key).BankAccount_Type ;
2533 l_payee_bankaccount_rec.BankAccount_Suffix := t_payee_bankacct_tbl(l_key).BankAccount_Suffix ;
2534 l_payee_bankaccount_rec.BankAccount_Desc := t_payee_bankacct_tbl(l_key).BankAccount_Desc ;
2535 l_payee_bankaccount_rec.Foreign_PayUse_Flag := t_payee_bankacct_tbl(l_key).Foreign_PayUse_Flag ;
2536 l_payee_bankaccount_rec.Pay_Factor_Flag := t_payee_bankacct_tbl(l_key).Pay_Factor_Flag ;
2537 l_payee_bankaccount_rec.EFT_Swift_Code := t_payee_bankacct_tbl(l_key).EFT_Swift_Code ;
2538 END IF;
2539
2540 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2541 print_debuginfo(l_module_name,'Default payee bank account : '|| l_payee_bankaccount_rec.Payee_BankAccount_Name);
2542
2543 END IF;
2544 -- Assign to ouput nested record structure
2545 x_default_pmt_attrs_rec.Payment_Method := l_payment_method_rec;
2546 x_default_pmt_attrs_rec.Payment_Format := l_payment_format_rec;
2547 x_default_pmt_attrs_rec.Payee_BankAccount := l_payee_bankaccount_rec;
2548 x_default_pmt_attrs_rec.Payment_Reason := l_payment_reason_rec;
2549 x_default_pmt_attrs_rec.Delivery_Channel := l_delivery_channel_rec;
2550 x_default_pmt_attrs_rec.Bank_Charge_Bearer := l_bank_charge_bearer;
2551 x_default_pmt_attrs_rec.Pay_Alone := l_pay_alone;
2552 x_default_pmt_attrs_rec.Settlement_Priority := l_settlement_priority;
2553 x_default_pmt_attrs_rec.payment_reason_comments :=l_payment_reason_comments;
2554 -- End of API body.
2555
2556 -- Standard call to get message count and if count is 1, get message info.
2557 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2558 p_data => x_msg_data);
2559
2560 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2561 print_debuginfo(l_module_name, 'RETURN');
2562
2563 END IF;
2564 EXCEPTION
2565 WHEN FND_API.G_EXC_ERROR THEN
2566 x_return_status := FND_API.G_RET_STS_ERROR ;
2567 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2568 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
2569 print_debuginfo(l_module_name,'SQLerr is :'
2570 || substr(SQLERRM, 1, 150));
2571 END IF;
2572 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2573 p_data => x_msg_data);
2574
2575 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2576 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2577 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2578 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
2579 print_debuginfo(l_module_name,'SQLerr is :'
2580 || substr(SQLERRM, 1, 150));
2581 END IF;
2582 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2583 p_data => x_msg_data);
2584
2585 WHEN OTHERS THEN
2586 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2587 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2588 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
2589 print_debuginfo(l_module_name,'SQLerr is :'
2590 || substr(SQLERRM, 1, 150));
2591 END IF;
2592 IF (FND_MSG_PUB.Check_Msg_Level
2593 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
2594 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
2595 l_api_name);
2596 END IF;
2597 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2598 p_data => x_msg_data);
2599
2600 END Get_Default_Payment_Attributes;
2601
2602 -- Start of comments
2603 -- API name : Get_Default_Payee_Bank_Acc
2604 -- Type : Public
2605 -- Pre-reqs : None.
2606 -- Function : get the default payee bank account attributes.
2607 -- Parameters :
2608 -- IN : p_api_version IN NUMBER Required
2609 -- p_init_msg_list IN VARCHAR2 Optional
2610 -- p_trxn_attributes_rec IN Trxn_Attributes_Rec_Type Required
2611 -- OUT : x_return_status OUT VARCHAR2 Required
2612 -- x_msg_count OUT NUMBER Required
2613 -- x_msg_data OUT VARCHAR2 Required
2614 -- x_payee_bankaccount OUT Payee_BankAccount_Rec_Type Required
2615 --
2616 -- Version : Current version 1.0
2617 -- Previous version None
2618 -- Initial version 1.0
2619 -- End of comments
2620
2621 PROCEDURE Get_Default_Payee_Bank_Acc(
2622 p_api_version IN NUMBER,
2623 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
2624 p_trxn_attributes_rec IN Trxn_Attributes_Rec_Type,
2625 x_return_status OUT NOCOPY VARCHAR2,
2626 x_msg_count OUT NOCOPY NUMBER,
2627 x_msg_data OUT NOCOPY VARCHAR2,
2628 x_payee_bankaccount OUT NOCOPY Payee_BankAccount_Rec_Type
2629 )
2630 IS
2631 l_api_name CONSTANT VARCHAR2(30) := 'Get_Default_Payee_Bank_Acc';
2632 l_api_version CONSTANT NUMBER := 1.0;
2633 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Get_Default_Payee_Bank_Acc';
2634
2635 l_payee_bankaccount_rec Payee_BankAccount_Rec_Type;
2636 l_key VARCHAR2(2000);
2637
2638 CURSOR payee_bankaccount_cur(p_payee_party_id VARCHAR2,
2639 p_payee_party_site_id VARCHAR2,
2640 p_supplier_site_id VARCHAR2,
2641 p_payer_org_id NUMBER,
2642 p_payer_org_type VARCHAR2,
2643 p_payment_function VARCHAR2,
2644 p_payment_currency VARCHAR2)
2645 IS
2646 SELECT b.bank_account_name,
2647 b.ext_bank_account_id,
2648 b.bank_account_number,
2649 b.currency_code,
2650 b.iban_number,
2651 b.bank_name,
2652 b.bank_number,
2653 b.bank_branch_name,
2654 b.branch_number,
2655 b.country_code,
2656 b.alternate_account_name,
2657 b.bank_account_type,
2658 b.account_suffix,
2659 b.description,
2660 b.foreign_payment_use_flag,
2661 b.payment_factor_flag,
2662 b.eft_swift_code
2663 FROM IBY_PMT_INSTR_USES_ALL ibyu,
2664 IBY_EXT_BANK_ACCOUNTS_V b,
2665 IBY_EXTERNAL_PAYEES_ALL ibypayee
2666 WHERE ibyu.instrument_id = b.ext_bank_account_id
2667 AND ibyu.instrument_type = 'BANKACCOUNT'
2668 AND (b.currency_code = p_payment_currency
2669 OR b.currency_code is null)
2670 AND ibyu.payment_function = p_payment_function
2671 AND ibyu.ext_pmt_party_id = ibypayee.ext_payee_id
2672 AND ibypayee.payee_party_id = p_payee_party_id
2673 AND trunc(sysdate) between NVL(ibyu.start_date,trunc(sysdate)) AND
2674 NVL(ibyu.end_date-1,trunc(sysdate))
2675 AND trunc(sysdate) between NVL(b.start_date,trunc(sysdate)) AND
2676 NVL(b.end_date-1,trunc(sysdate))
2677 AND (ibypayee.party_site_id is null
2678 OR ibypayee.party_site_id = p_payee_party_site_id)
2679 AND (ibypayee.supplier_site_id is NULL OR ibypayee.supplier_site_id = p_supplier_site_id) --Bug 9194531
2680 AND (ibypayee.org_id is null
2681 OR (ibypayee.org_id = p_payer_org_id AND ibypayee.org_type = p_payer_org_type) )
2682 AND ibyu.payment_flow='DISBURSEMENTS'
2683 ORDER by ibypayee.supplier_site_id,
2684 ibypayee.party_site_id,
2685 ibypayee.org_id,
2686 ibyu.order_of_preference;
2687
2688 BEGIN
2689
2690 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2691 print_debuginfo(l_module_name,'ENTER');
2692 print_debuginfo(l_module_name,'Application_id : '|| p_trxn_attributes_rec.application_id);
2693 print_debuginfo(l_module_name,'Org Id : '|| p_trxn_attributes_rec.payer_org_id);
2694 print_debuginfo(l_module_name,'Org Type : '|| p_trxn_attributes_rec.payer_org_type);
2695 print_debuginfo(l_module_name,'Payee Id : '|| p_trxn_attributes_rec.payee_party_id);
2696 print_debuginfo(l_module_name,'Payee Site Id : '|| p_trxn_attributes_rec.payee_party_site_id);
2697 print_debuginfo(l_module_name,'Supplier Site ID : '|| p_trxn_attributes_rec.Supplier_Site_Id);
2698 print_debuginfo(l_module_name,'Payment Currency : '|| p_trxn_attributes_rec.payment_currency);
2699 print_debuginfo(l_module_name,'Payment Function : '|| p_trxn_attributes_rec.payment_function);
2700
2701 END IF;
2702 -- Standard call to check for call compatibility.
2703 IF NOT FND_API.Compatible_API_Call (l_api_version,
2704 p_api_version,
2705 l_api_name,
2706 G_PKG_NAME)
2707 THEN
2708 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2709 END IF;
2710
2711 -- Initialize message list if p_init_msg_list is set to TRUE.
2712 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2713 FND_MSG_PUB.initialize;
2714 END IF;
2715
2716 -- Initialize API return status to success
2717 x_return_status := FND_API.G_RET_STS_SUCCESS;
2718
2719 l_key := p_trxn_attributes_rec.Payee_Party_Id ||'$'|| p_trxn_attributes_rec.Payee_Party_Site_Id ||'$'|| p_trxn_attributes_rec.Supplier_Site_Id ||'$'|| p_trxn_attributes_rec.Payer_Org_Id ||'$'|| p_trxn_attributes_rec.Payer_Org_Type;
2720 l_key := l_key ||'$'|| p_trxn_attributes_rec.Payment_Currency ||'$'|| p_trxn_attributes_rec.Payment_Function;
2721 -- Start of API body.
2722 IF (NOT(t_payee_bankacct_tbl.EXISTS( l_key ))) THEN
2723
2724 OPEN payee_bankaccount_cur(p_trxn_attributes_rec.Payee_Party_Id,
2725 p_trxn_attributes_rec.Payee_Party_Site_Id,
2726 p_trxn_attributes_rec.Supplier_Site_Id,
2727 p_trxn_attributes_rec.Payer_Org_Id,
2728 p_trxn_attributes_rec.Payer_Org_Type,
2729 p_trxn_attributes_rec.Payment_Function,
2730 p_trxn_attributes_rec.Payment_Currency);
2731 FETCH payee_bankaccount_cur INTO l_payee_bankaccount_rec;
2732 CLOSE payee_bankaccount_cur;
2733
2734 t_payee_bankacct_tbl(l_key).Payee_Party_Id := p_trxn_attributes_rec.Payee_Party_Id;
2735 t_payee_bankacct_tbl(l_key).Payee_Party_Site_Id := p_trxn_attributes_rec.Payee_Party_Site_Id;
2736 t_payee_bankacct_tbl(l_key).Supplier_Site_Id := p_trxn_attributes_rec.Payer_Org_Id;
2737 t_payee_bankacct_tbl(l_key).Payee_Org_Id := p_trxn_attributes_rec.Payer_Org_Id;
2738 t_payee_bankacct_tbl(l_key).Payee_Org_Type := p_trxn_attributes_rec.Payer_Org_Type;
2739 t_payee_bankacct_tbl(l_key).Payment_Currency := p_trxn_attributes_rec.Payment_Currency;
2740 t_payee_bankacct_tbl(l_key).Payment_Function := p_trxn_attributes_rec.Payment_Function;
2741 t_payee_bankacct_tbl(l_key).Payee_BankAccount_Name := l_payee_bankaccount_rec.Payee_BankAccount_Name;
2742 t_payee_bankacct_tbl(l_key).Payee_BankAccount_Id := l_payee_bankaccount_rec.Payee_BankAccount_Id ;
2743 t_payee_bankacct_tbl(l_key).Payee_BankAccount_Num := l_payee_bankaccount_rec.Payee_BankAccount_Num ;
2744 t_payee_bankacct_tbl(l_key).Currency_Code := l_payee_bankaccount_rec.Currency_Code ;
2745 t_payee_bankacct_tbl(l_key).IBAN := l_payee_bankaccount_rec.IBAN ;
2746 t_payee_bankacct_tbl(l_key).Payee_BankName := l_payee_bankaccount_rec.Payee_BankName ;
2747 t_payee_bankacct_tbl(l_key).Payee_BankNumber := l_payee_bankaccount_rec.Payee_BankNumber ;
2748 t_payee_bankacct_tbl(l_key).Payee_BranchName := l_payee_bankaccount_rec.Payee_BranchName ;
2749 t_payee_bankacct_tbl(l_key).Payee_BranchNumber := l_payee_bankaccount_rec.Payee_BranchNumber ;
2750 t_payee_bankacct_tbl(l_key).Bank_Country := l_payee_bankaccount_rec.Bank_Country ;
2751 t_payee_bankacct_tbl(l_key).Alter_BankAccount_Name := l_payee_bankaccount_rec.Alter_BankAccount_Name;
2752 t_payee_bankacct_tbl(l_key).BankAccount_Type := l_payee_bankaccount_rec.BankAccount_Type ;
2753 t_payee_bankacct_tbl(l_key).BankAccount_Suffix := l_payee_bankaccount_rec.BankAccount_Suffix ;
2754 t_payee_bankacct_tbl(l_key).BankAccount_Desc := l_payee_bankaccount_rec.BankAccount_Desc ;
2755 t_payee_bankacct_tbl(l_key).Foreign_PayUse_Flag := l_payee_bankaccount_rec.Foreign_PayUse_Flag ;
2756 t_payee_bankacct_tbl(l_key).Pay_Factor_Flag := l_payee_bankaccount_rec.Pay_Factor_Flag ;
2757 t_payee_bankacct_tbl(l_key).EFT_Swift_Code := l_payee_bankaccount_rec.EFT_Swift_Code ;
2758
2759 ELSE
2760 l_payee_bankaccount_rec.Payee_BankAccount_Name := t_payee_bankacct_tbl(l_key).Payee_BankAccount_Name;
2761 l_payee_bankaccount_rec.Payee_BankAccount_Id := t_payee_bankacct_tbl(l_key).Payee_BankAccount_Id ;
2762 l_payee_bankaccount_rec.Payee_BankAccount_Num := t_payee_bankacct_tbl(l_key).Payee_BankAccount_Num ;
2763 l_payee_bankaccount_rec.Currency_Code := t_payee_bankacct_tbl(l_key).Currency_Code ;
2764 l_payee_bankaccount_rec.IBAN := t_payee_bankacct_tbl(l_key).IBAN ;
2765 l_payee_bankaccount_rec.Payee_BankName := t_payee_bankacct_tbl(l_key).Payee_BankName ;
2766 l_payee_bankaccount_rec.Payee_BankNumber := t_payee_bankacct_tbl(l_key).Payee_BankNumber ;
2767 l_payee_bankaccount_rec.Payee_BranchName := t_payee_bankacct_tbl(l_key).Payee_BranchName ;
2768 l_payee_bankaccount_rec.Payee_BranchNumber := t_payee_bankacct_tbl(l_key).Payee_BranchNumber ;
2769 l_payee_bankaccount_rec.Bank_Country := t_payee_bankacct_tbl(l_key).Bank_Country ;
2770 l_payee_bankaccount_rec.Alter_BankAccount_Name := t_payee_bankacct_tbl(l_key).Alter_BankAccount_Name;
2771 l_payee_bankaccount_rec.BankAccount_Type := t_payee_bankacct_tbl(l_key).BankAccount_Type ;
2772 l_payee_bankaccount_rec.BankAccount_Suffix := t_payee_bankacct_tbl(l_key).BankAccount_Suffix ;
2773 l_payee_bankaccount_rec.BankAccount_Desc := t_payee_bankacct_tbl(l_key).BankAccount_Desc ;
2774 l_payee_bankaccount_rec.Foreign_PayUse_Flag := t_payee_bankacct_tbl(l_key).Foreign_PayUse_Flag ;
2775 l_payee_bankaccount_rec.Pay_Factor_Flag := t_payee_bankacct_tbl(l_key).Pay_Factor_Flag ;
2776 l_payee_bankaccount_rec.EFT_Swift_Code := t_payee_bankacct_tbl(l_key).EFT_Swift_Code ;
2777 END IF;
2778
2779 x_payee_bankaccount := l_payee_bankaccount_rec;
2780 -- End of API body.
2781
2782 -- Standard call to get message count and if count is 1, get message info.
2783 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2784 p_data => x_msg_data);
2785
2786 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2787 print_debuginfo(l_module_name, 'RETURN');
2788
2789 END IF;
2790 EXCEPTION
2791 WHEN FND_API.G_EXC_ERROR THEN
2792 x_return_status := FND_API.G_RET_STS_ERROR ;
2793 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2794 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
2795 print_debuginfo(l_module_name,'SQLerr is :'
2796 || substr(SQLERRM, 1, 150));
2797 END IF;
2798 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2799 p_data => x_msg_data);
2800
2801 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2802 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2803 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2804 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
2805 print_debuginfo(l_module_name,'SQLerr is :'
2806 || substr(SQLERRM, 1, 150));
2807 END IF;
2808 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2809 p_data => x_msg_data);
2810
2811 WHEN OTHERS THEN
2812 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2813 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2814 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
2815 print_debuginfo(l_module_name,'SQLerr is :'
2816 || substr(SQLERRM, 1, 150));
2817 END IF;
2818 IF (FND_MSG_PUB.Check_Msg_Level
2819 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
2820 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
2821 l_api_name);
2822 END IF;
2823 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2824 p_data => x_msg_data);
2825
2826 END Get_Default_Payee_Bank_Acc;
2827
2828 -- Start of comments
2829 -- API name : Get_Payment_Field_Properties
2830 -- Type : Public
2831 -- Pre-reqs : None.
2832 -- Function : get the list of applicable Payment attributes.
2833 -- Parameters :
2834 -- IN : p_api_version IN NUMBER Required
2835 -- p_application_id IN NUMBER Required
2836 -- p_init_msg_list IN VARCHAR2 Optional
2837 -- p_validation_level IN NUMBER Optional
2838 -- p_payment_method_id IN BY_PAYMENT_METHODS_VL.payment_method_id%TYPE Required
2839 -- OUT : x_return_status OUT VARCHAR2 Required
2840 -- x_msg_count OUT NUMBER Required
2841 -- x_msg_data OUT VARCHAR2 Required
2842 -- x_Payment_Field_Properties OUT Payment_Field_Properties_Rec_Type Required
2843 --
2844 -- Version : Current version 1.0
2845 -- Previous version None
2846 -- Initial version 1.0
2847 -- End of comments
2848
2849 PROCEDURE Get_Payment_Field_Properties (
2850 p_api_version IN NUMBER,
2851 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
2852 p_payment_method_id IN
2853 IBY_PAYMENT_METHODS_VL.payment_method_code%TYPE,
2854 x_return_status OUT NOCOPY VARCHAR2,
2855 x_msg_count OUT NOCOPY NUMBER,
2856 x_msg_data OUT NOCOPY VARCHAR2,
2857 x_Payment_Field_Properties OUT NOCOPY Applicable_Pmt_Attrs_Rec_Type
2858 )
2859 IS
2860 l_api_name CONSTANT VARCHAR2(30) := 'Get_Payment_Field_Properties';
2861 l_api_version CONSTANT NUMBER := 1.0;
2862 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Get_Payment_Field_Properties';
2863
2864 l_Payment_Field_Properties Applicable_Pmt_Attrs_Rec_Type;
2865
2866 cursor pmt_field_prop_csr(p_payment_method_code varchar2)
2867 IS
2868 SELECT payment_reason_comnt_apl_flag,
2869 remittance_message1_apl_flag,
2870 remittance_message2_apl_flag,
2871 remittance_message3_apl_flag,
2872
2873 unique_remittance_id_apl_flag,
2874 uri_check_digit_apl_flag,
2875 payment_format_apl_flag,
2876 delivery_channel_apl_flag,
2877 bank_charge_bearer_apl_flag,
2878 settlement_priority_apl_flag,
2879 payment_reason_apl_flag,
2880 external_bank_acct_apl_flag,
2881 exclusive_pmt_apl_flag,
2882 inactive_date
2883 FROM IBY_PAYMENT_METHODS_B
2884 WHERE payment_method_code = p_payment_method_code;
2885
2886 BEGIN
2887
2888 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2889 print_debuginfo(l_module_name, 'ENTER');
2890 print_debuginfo(l_module_name,'Payment Method Id : '|| p_payment_method_id);
2891
2892 END IF;
2893 -- Standard call to check for call compatibility.
2894 IF NOT FND_API.Compatible_API_Call (l_api_version,
2895 p_api_version,
2896 l_api_name,
2897 G_PKG_NAME) THEN
2898 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2899 END IF;
2900
2901 -- Initialize message list if p_init_msg_list is set to TRUE.
2902 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2903 FND_MSG_PUB.initialize;
2904 END IF;
2905
2906 -- Initialize API return status to success
2907 x_return_status := FND_API.G_RET_STS_SUCCESS;
2908 -- print_debuginfo(l_module_name,'Before fetch');
2909 OPEN pmt_field_prop_csr(p_payment_method_id);
2910 FETCH pmt_field_prop_csr INTO l_Payment_Field_Properties;
2911
2912 -- print_debuginfo(l_module_name,'After fetch');
2913 IF (trunc(sysdate) < NVL(l_Payment_Field_Properties.inactive_date, trunc(sysdate))) THEN
2914 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2915 print_debuginfo(l_module_name,'Error: Payment Method is inactive.');
2916 END IF;
2917 FND_MESSAGE.set_name('IBY', 'IBY_INACTIVE_ATTRIBUTE');
2918 FND_MESSAGE.SET_TOKEN('PARAM', 'Payment Method');
2919 FND_MSG_PUB.Add;
2920 RAISE FND_API.G_EXC_ERROR;
2921 ELSE
2922 x_Payment_Field_Properties := l_Payment_Field_Properties;
2923 END IF;
2924
2925 CLOSE pmt_field_prop_csr;
2926
2927 -- End of API body.
2928
2929 -- Standard call to get message count and if count is 1, get message info.
2930 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2931 p_data => x_msg_data);
2932
2933 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2934 print_debuginfo(l_module_name, 'RETURN');
2935 END IF;
2936 EXCEPTION
2937 WHEN FND_API.G_EXC_ERROR THEN
2938 x_return_status := FND_API.G_RET_STS_ERROR ;
2939 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2940 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
2941 print_debuginfo(l_module_name,'SQLerr is :'
2942 || substr(SQLERRM, 1, 150));
2943 END IF;
2944 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2945 p_data => x_msg_data);
2946
2947 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2948 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2949 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2950 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
2951 print_debuginfo(l_module_name,'SQLerr is :'
2952 || substr(SQLERRM, 1, 150));
2953 END IF;
2954 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2955 p_data => x_msg_data);
2956
2957 WHEN OTHERS THEN
2958 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2959 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
2960 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
2961 print_debuginfo(l_module_name,'SQLerr is :'
2962 || substr(SQLERRM, 1, 150));
2963 END IF;
2964 IF (FND_MSG_PUB.Check_Msg_Level
2965 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
2966 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
2967 l_api_name);
2968 END IF;
2969 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
2970 p_data => x_msg_data);
2971
2972 END Get_Payment_Field_Properties;
2973
2974
2975 -- Start of comments
2976 -- API name : ValidateDocument
2977 -- Type : Public
2978 -- Pre-reqs : None.
2979 -- Function : validates the documents in the global temporary table
2980 -- IBY_DOCUMENTS_PAYABLE_GT
2981 -- Parameters :
2982 -- IN : p_api_version IN NUMBER Required
2983 -- p_application_id IN NUMBER Required
2984 -- p_init_msg_list IN VARCHAR2 Optional
2985 -- p_validation_level IN NUMBER Optional
2986 --
2987 -- OUT : x_return_status OUT VARCHAR2 Required
2988 -- x_msg_count OUT NUMBER Required
2989 -- x_msg_data OUT VARCHAR2 Required
2990 --
2991 --
2992 -- Version : Current version 1.0
2993 -- Previous version None
2994 -- Initial version 1.0
2995 -- End of comments
2996
2997 PROCEDURE Validate_Documents(
2998 p_api_version IN NUMBER,
2999 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
3000 p_document_id IN IBY_DOCS_PAYABLE_ALL.document_payable_id%TYPE,
3001 x_return_status OUT NOCOPY VARCHAR2,
3002 x_msg_count OUT NOCOPY NUMBER,
3003 x_msg_data OUT NOCOPY VARCHAR2
3004 )
3005 IS
3006 l_api_name CONSTANT VARCHAR2(30) := 'ValidateDocument';
3007 l_api_version CONSTANT NUMBER := 1.0;
3008 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.ValidateDocument';
3009
3010 l_return_status VARCHAR2(10);
3011
3012 BEGIN
3013
3014 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3015 print_debuginfo(l_module_name, 'ENTER');
3016
3017 END IF;
3018 -- Standard call to check for call compatibility.
3019 IF NOT FND_API.Compatible_API_Call (l_api_version,
3020 p_api_version,
3021 l_api_name,
3022 G_PKG_NAME)
3023 THEN
3024 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3025 END IF;
3026
3027 -- Initialize message list if p_init_msg_list is set to TRUE.
3028 IF FND_API.to_Boolean( p_init_msg_list ) THEN
3029 FND_MSG_PUB.initialize;
3030 END IF;
3031
3032 -- Initialize API return status to success
3033 x_return_status := FND_API.G_RET_STS_SUCCESS;
3034
3035 -- Start of API body.
3036
3037 IBY_VALIDATIONSETS_PUB.performOnlineValidations(p_document_id,l_return_status);
3038
3039 IF (l_return_status = -1) THEN
3040 x_return_status := FND_API.G_RET_STS_ERROR;
3041 ELSE
3042 x_return_status := FND_API.G_RET_STS_SUCCESS;
3043 END IF;
3044
3045 -- End of API body.
3046
3047 -- Standard call to get message count and if count is 1, get message info.
3048 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3049 p_data => x_msg_data);
3050
3051 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3052 print_debuginfo(l_module_name, 'RETURN');
3053
3054 END IF;
3055 EXCEPTION
3056 WHEN FND_API.G_EXC_ERROR THEN
3057 x_return_status := FND_API.G_RET_STS_ERROR ;
3058 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3059 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
3060 print_debuginfo(l_module_name,'SQLerr is :'
3061 || substr(SQLERRM, 1, 150));
3062 END IF;
3063 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3064 p_data => x_msg_data);
3065
3066 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3067 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3068 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3069 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
3070 print_debuginfo(l_module_name,'SQLerr is :'
3071 || substr(SQLERRM, 1, 150));
3072 END IF;
3073 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3074 p_data => x_msg_data);
3075
3076 WHEN OTHERS THEN
3077 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
3078 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3079 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
3080 print_debuginfo(l_module_name,'SQLerr is :'
3081 || substr(SQLERRM, 1, 150));
3082 END IF;
3083 IF (FND_MSG_PUB.Check_Msg_Level
3084 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)) THEN
3085 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
3086 l_api_name);
3087 END IF;
3088 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count,
3089 p_data => x_msg_data);
3090
3091 END Validate_Documents;
3092
3093 --
3094 --
3095 --
3096
3097 Procedure evaluate_Rule_Based_Default(
3098 p_trxn_attributes IN Trxn_Attributes_Rec_Type,
3099 x_pmt_method_rec IN OUT NOCOPY Payment_Method_Rec_Type)
3100 IS
3101
3102 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Evaluate_Rule_Based_Default';
3103
3104 l_payer_le_flag BOOLEAN := FALSE;
3105 l_payer_org_flag BOOLEAN := FALSE;
3106 l_trxn_type_flag BOOLEAN := FALSE;
3107 l_currency_flag BOOLEAN := FALSE;
3108 l_cross_border_flag BOOLEAN := FALSE;
3109 l_pmt_amount_flag BOOLEAN := FALSE;
3110
3111 l_match BOOLEAN := FALSE;
3112
3113 -- the cummulative flags for each driver type
3114 l_cumm_payer_le_match BOOLEAN := TRUE;
3115 l_cumm_payer_org_match BOOLEAN := TRUE;
3116 l_cumm_trxn_type_match BOOLEAN := TRUE;
3117 l_cumm_currency_match BOOLEAN := TRUE;
3118 l_cumm_cross_border_match BOOLEAN := TRUE;
3119 l_cumm_pmt_amount_match BOOLEAN := TRUE;
3120
3121
3122 l_payer_country VARCHAR2(30);
3123 l_payee_country VARCHAR2(30);
3124 l_accounting_curr VARCHAR2(30);
3125
3126 -- Pick up all rules for active payment methods.
3127 CURSOR DefaultingRules_csr( p_application_id NUMBER,
3128 p_pay_proc_trxn_type VARCHAR2,
3129 p_payer_le_id NUMBER,
3130 p_payer_org_id NUMBER)
3131 IS
3132 SELECT ibypmtrules.payment_rule_id,
3133 ibypmtmthds.payment_method_code,
3134 ibypmtmthds.payment_method_name,
3135 ibypmtmthds.support_bills_payable_flag,
3136 ibypmtmthds.maturity_date_offset_days
3137 FROM IBY_PAYMENT_RULES ibypmtrules,
3138 IBY_PAYMENT_METHODS_VL ibypmtmthds,
3139 IBY_APPLICABLE_PMT_MTHDS am1,
3140 IBY_APPLICABLE_PMT_MTHDS am2,
3141 IBY_APPLICABLE_PMT_MTHDS am3
3142 WHERE ibypmtrules.payment_method_code = ibypmtmthds.payment_method_code
3143 AND ibypmtrules.application_id = p_application_id
3144 AND NVL(ibypmtmthds.inactive_date,trunc(sysdate)) >= trunc(sysdate)
3145 AND am1.payment_method_code=ibypmtmthds.payment_method_code
3146 AND am1.applicable_type_code='PAY_PROC_TRXN_TYPE'
3147 AND am1.application_id= p_application_id
3148 AND (am1.applicable_value_to is null or
3149 am1.applicable_value_to=p_pay_proc_trxn_type)
3150 AND am2.payment_method_code=am1.payment_method_code
3151 AND am2.applicable_type_code='PAYER_LE'
3152 AND am2.application_id= p_application_id
3153 AND (am2.applicable_value_to is null or
3154 am2.applicable_value_to=p_payer_le_id)
3155 AND am3.payment_method_code=am2.payment_method_code
3156 AND am3.applicable_type_code='PAYER_ORG'
3157 AND am3.application_id= p_application_id
3158 AND (am3.applicable_value_to is null or
3159 am3.applicable_value_to=p_payer_org_id)
3160 ORDER BY ibypmtrules.payment_rule_priority;
3161
3162
3163 -- Pick up all conditions for a given payment rule.
3164 CURSOR DefaultingCondt_csr(p_payment_rule_id NUMBER)
3165 IS
3166 SELECT rule_condition_type_code,
3167 operator_code,
3168 rule_condition_value
3169 FROM IBY_RULE_CONDITIONS ibyruleconds
3170 WHERE ibyruleconds.payment_rule_id = p_payment_rule_id;
3171
3172 CURSOR payer_info_csr(p_le_id IN NUMBER)
3173 IS
3174 SELECT xlev.country,
3175 glv.currency_code
3176 FROM XLE_FIRSTPARTY_INFORMATION_V xlev,
3177 GL_LEDGER_LE_V glv
3178 WHERE xlev.legal_entity_id = glv.legal_entity_id
3179 AND glv.ledger_category_code = 'PRIMARY'
3180 AND xlev.legal_entity_id = p_le_id;
3181
3182 CURSOR payee_country_csr(p_payee_id IN NUMBER)
3183 IS
3184 SELECT country
3185 FROM HZ_PARTIES
3186 WHERE party_id = p_payee_id;
3187
3188 CURSOR payeesite_country_csr(p_payee_id IN NUMBER,
3189 p_payee_site_id IN NUMBER)
3190 IS
3191 SELECT locs.country
3192 FROM HZ_PARTY_SITES sites,
3193 HZ_LOCATIONS locs
3194 WHERE sites.party_id = p_payee_id
3195 AND sites.party_site_id = p_payee_site_id
3196 AND sites.location_id = locs.location_id;
3197
3198 BEGIN
3199 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3200 print_debuginfo(l_module_name, 'ENTER');
3201
3202 END IF;
3203 -- Fetch 1st party Payer country
3204 IF (p_trxn_attributes.payer_legal_entity_id IS NOT NULL) THEN
3205 OPEN payer_info_csr(p_trxn_attributes.payer_legal_entity_id);
3206 FETCH payer_info_csr INTO l_payer_country, l_accounting_curr;
3207 CLOSE payer_info_csr;
3208 END IF;
3209
3210 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3211 print_debuginfo(l_module_name,'Accounting currency : '|| l_accounting_curr);
3212 print_debuginfo(l_module_name,'First party country : '|| l_payer_country);
3213
3214 END IF;
3215 IF (p_trxn_attributes.payee_party_id IS NOT NULL) THEN
3216 IF (p_trxn_attributes.payee_party_site_id IS NOT NULL) THEN
3217 -- Fetch Payee Site Country
3218 OPEN payeesite_country_csr(p_trxn_attributes.payee_party_id,
3219 p_trxn_attributes.payee_party_site_id);
3220 FETCH payeesite_country_csr INTO l_payee_country;
3221 CLOSE payeesite_country_csr;
3222 ELSE
3223 -- Fetch Payee Country
3224 OPEN payee_country_csr(p_trxn_attributes.payee_party_site_id);
3225 FETCH payee_country_csr INTO l_payee_country;
3226 CLOSE payee_country_csr;
3227 END IF;
3228 END IF;
3229
3230 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3231 print_debuginfo(l_module_name, 'Payee Country : '|| l_payee_country);
3232
3233 END IF;
3234 FOR v_defaultingRules IN DefaultingRules_csr(
3235 p_trxn_attributes.application_id,
3236 p_trxn_attributes.pay_proc_trxn_type_code,
3237 p_trxn_attributes.Payer_Legal_Entity_Id,
3238 p_trxn_attributes.payer_org_id)
3239 LOOP
3240 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3241 print_debuginfo(l_module_name,'Evaluate Rule:' || v_defaultingRules.payment_rule_id);
3242 END IF;
3243 FOR v_ruleCondt IN DefaultingCondt_csr(v_defaultingRules.payment_rule_id)
3244 LOOP
3245 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3246 print_debuginfo(l_module_name,'Evaluate Rule Condition:' || v_ruleCondt.rule_condition_value);
3247 END IF;
3248 --
3249 -- evaluate rule condition based on 'PAYMENT_AMOUNT'
3250 -- and the operators are "EQ", "NE", "LE", "LT", "GE", "GT"
3251 --
3252 IF (v_ruleCondt.rule_condition_type_code = 'PAYMENT_AMOUNT') THEN
3253
3254 CASE v_ruleCondt.operator_code
3255 WHEN 'EQ' THEN
3256 l_pmt_amount_flag := l_pmt_amount_flag
3257 OR (p_trxn_attributes.payment_amount =
3258 v_ruleCondt.rule_condition_value);
3259 WHEN 'NE' THEN
3260 l_pmt_amount_flag := l_pmt_amount_flag
3261 OR (p_trxn_attributes.payment_amount <>
3262 v_ruleCondt.rule_condition_value);
3263 WHEN 'LE' THEN
3264 l_pmt_amount_flag := l_pmt_amount_flag
3265 OR (p_trxn_attributes.payment_amount <=
3266 v_ruleCondt.rule_condition_value);
3267 WHEN 'LT' THEN
3268 l_pmt_amount_flag := l_pmt_amount_flag
3269 OR (p_trxn_attributes.payment_amount <
3270 v_ruleCondt.rule_condition_value);
3271 WHEN 'GE' THEN
3272 l_pmt_amount_flag := l_pmt_amount_flag
3273 OR (p_trxn_attributes.payment_amount >=
3274 v_ruleCondt.rule_condition_value);
3275 WHEN 'GT' THEN
3276 l_pmt_amount_flag := l_pmt_amount_flag
3277 OR (p_trxn_attributes.payment_amount >
3278 v_ruleCondt.rule_condition_value);
3279 ELSE
3280 NULL; -- Not a recognized operator code
3281 END CASE;
3282
3283 l_cumm_pmt_amount_match := l_pmt_amount_flag;
3284 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3285 print_debuginfo(l_module_name, 'l_cumm_pmt_amount_match :'
3286 || ifelse(l_cumm_pmt_amount_match,'true','false'));
3287 END IF;
3288
3289 --
3290 -- evaluate rule condition based on 'PAYER_ORG'
3291 -- and the operators are "EQ" , "NE"
3292 --
3293 ELSIF (v_ruleCondt.rule_condition_type_code = 'PAYER_ORG') THEN
3294
3295 CASE v_ruleCondt.operator_code
3296 WHEN 'EQ' THEN
3297 l_payer_org_flag := l_payer_org_flag
3298 OR (v_ruleCondt.rule_condition_value is NULL)
3299 OR (p_trxn_attributes.Payer_Org_Id =
3300 v_ruleCondt.rule_condition_value);
3301 WHEN 'NE' THEN
3302 l_payer_org_flag := l_payer_org_flag
3303 OR (p_trxn_attributes.Payer_Org_Id <>
3304 v_ruleCondt.rule_condition_value);
3305 ELSE
3306 NULL; -- Not a recognized operator code
3307 END CASE;
3308
3309 l_cumm_payer_org_match := l_payer_org_flag;
3310 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3311 print_debuginfo(l_module_name, 'l_cumm_payer_org_match :'
3312 || ifelse(l_cumm_payer_org_match,'true','false'));
3313
3314 END IF;
3315 --
3316 -- evaluate rule condition based on 'PAYER_lE'
3317 -- and the operators are "EQ" , "NE"
3318 --
3319 ELSIF (v_ruleCondt.rule_condition_type_code = 'PAYER_LE') THEN
3320
3321 CASE v_ruleCondt.operator_code
3322 WHEN 'EQ' THEN
3323 l_payer_le_flag := l_payer_le_flag
3324 OR (v_ruleCondt.rule_condition_value is NULL)
3325 OR (p_trxn_attributes.payer_legal_entity_id =
3326 v_ruleCondt.rule_condition_value);
3327 WHEN 'NE' THEN
3328 l_payer_le_flag := l_payer_le_flag
3329 OR (p_trxn_attributes.payer_legal_entity_id <>
3330 v_ruleCondt.rule_condition_value);
3331 ELSE
3332 NULL; -- Not a recognized operator code
3333 END CASE;
3334
3335 l_cumm_payer_le_match := l_payer_le_flag;
3336 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3337 print_debuginfo(l_module_name, 'l_cumm_payer_le_match :'
3338 || ifelse(l_cumm_payer_le_match,'true','false'));
3339
3340 END IF;
3341 --
3342 -- evaluate rule condition based on 'PAY_PROC_TRXN_TYPE'
3343 -- and the operators are "EQ" , "NE"
3344 --
3345 ELSIF (v_ruleCondt.rule_condition_type_code = 'PAY_PROC_TRXN_TYPE') THEN
3346
3347 CASE v_ruleCondt.operator_code
3348 WHEN 'EQ' THEN
3349 l_trxn_type_flag := l_trxn_type_flag
3350 OR (v_ruleCondt.rule_condition_value is NULL)
3351 OR (p_trxn_attributes.Pay_Proc_Trxn_Type_Code =
3352 v_ruleCondt.rule_condition_value);
3353 WHEN 'NE' THEN
3354 l_trxn_type_flag := l_trxn_type_flag
3355 OR (p_trxn_attributes.Pay_Proc_Trxn_Type_Code <>
3356 v_ruleCondt.rule_condition_value);
3357 ELSE
3358 NULL; -- Not a recognized operator code
3359 END CASE;
3360
3361 l_cumm_trxn_type_match := l_trxn_type_flag;
3362 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3363 print_debuginfo(l_module_name, 'l_cumm_trxn_type_match :'
3364 || ifelse(l_cumm_trxn_type_match,'true','false'));
3365
3366
3367 END IF;
3368 --
3369 -- evaluate rule condition based on 'CROSS_BORDER_FLAG'
3370 -- and the operators are "EQ"
3371 --
3372 ELSIF (v_ruleCondt.rule_condition_type_code = 'CROSS_BORDER_FLAG') THEN
3373
3374 CASE v_ruleCondt.operator_code
3375 WHEN 'EQ' THEN
3376 l_cross_border_flag := l_cross_border_flag
3377 OR (v_ruleCondt.rule_condition_value='FOREIGN_AND_DOMESTIC')
3378 OR (v_ruleCondt.rule_condition_value = 'DOMESTIC'
3379 AND NVL(l_payee_country,l_payer_country) = l_payer_country)
3380 OR (v_ruleCondt.rule_condition_value = 'FOREIGN'
3381 AND NVL(l_payee_country,l_payer_country) <> l_payer_country);
3382 ELSE
3383 NULL; -- Not a recognized operator code
3384 END CASE;
3385
3386 l_cumm_cross_border_match := l_cross_border_flag;
3387 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3388 print_debuginfo(l_module_name,'l_cumm_cross_border_match :'
3389 || ifelse(l_cumm_cross_border_match,'true','false'));
3390
3391
3392 END IF;
3393 --
3394 -- evaluate rule condition based on 'FOREIGN_CURRENCY_FLAG'
3395 -- and the operators are "EQ"
3396 --
3397 ELSIF (v_ruleCondt.rule_condition_type_code = 'FOREIGN_CURRENCY_FLAG') THEN
3398
3399 CASE v_ruleCondt.operator_code
3400 WHEN 'EQ' THEN
3401 l_currency_flag := l_currency_flag
3402 OR (v_ruleCondt.rule_condition_value='FOREIGN_AND_DOMESTIC')
3403 OR (v_ruleCondt.rule_condition_value = 'DOMESTIC'
3404 AND p_trxn_attributes.Payment_Currency = l_accounting_curr)
3405 OR (v_ruleCondt.rule_condition_value = 'FOREIGN'
3406 AND p_trxn_attributes.Payment_Currency <> l_accounting_curr);
3407
3408 ELSE
3409 NULL; -- Not a recognized operator code
3410 END CASE;
3411
3412 l_cumm_currency_match := l_currency_flag;
3413 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3414 print_debuginfo(l_module_name,'l_cumm_currency_match :'
3415 || ifelse(l_cumm_currency_match,'true','false'));
3416 END IF;
3417 END IF; -- if v_ruleCondt.parameter_code = 'PAYMENT_AMOUNT'
3418
3419 END LOOP;
3420
3421 l_match := (l_cumm_payer_le_match AND l_cumm_payer_org_match AND
3422 l_cumm_trxn_type_match AND l_cumm_currency_match AND
3423 l_cumm_cross_border_match AND l_cumm_pmt_amount_match);
3424
3425 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3426 print_debuginfo(l_module_name, 'l_match :'
3427 ||ifelse(l_match,'true','false'));
3428
3429 END IF;
3430 IF (l_match = TRUE) THEN
3431 x_pmt_method_rec.Payment_Method_Name :=
3432 v_DefaultingRules.payment_method_name;
3433 x_pmt_method_rec.Payment_Method_Code :=
3434 v_DefaultingRules.payment_method_code;
3435 x_pmt_method_rec.Bill_Payable_Flag :=
3436 v_DefaultingRules.support_bills_payable_flag;
3437 x_pmt_method_rec.Maturity_Date_Offset :=
3438 v_DefaultingRules.maturity_date_offset_days;
3439 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3440 print_debuginfo(l_module_name, 'Default Payment Method Name :'
3441 ||v_DefaultingRules.payment_method_name);
3442 END IF;
3443 EXIT;
3444 END IF;
3445 END LOOP;
3446
3447 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3448 print_debuginfo(l_module_name, 'RETURN');
3449
3450 END IF;
3451 END Evaluate_Rule_Based_Default;
3452
3453
3454 --
3455 --
3456 --
3457 PROCEDURE print_debuginfo(p_module IN VARCHAR2,
3458 p_debug_text IN VARCHAR2)
3459 IS
3460
3461 l_default_debug_level VARCHAR2(200) := FND_LOG.LEVEL_STATEMENT;
3462
3463 BEGIN
3464
3465 --
3466 -- Writing debug text to the pl/sql debug file.
3467 --
3468 -- FND_FILE.PUT_LINE(FND_FILE.LOG, p_module||p_debug_text);
3469 --
3470
3471 /*
3472 * Fix for bug 5578607:
3473 *
3474 * Call the underlying routine only if the current debug
3475 * level exceeds the runtime debug level.
3476 */
3477 IF (l_default_debug_level >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
3478
3479 iby_debug_pub.add(substr(RPAD(p_module,55)||' : '||
3480 p_debug_text, 0, 150),
3481 iby_debug_pub.G_LEVEL_INFO,
3482 G_DEBUG_MODULE);
3483
3484 END IF;
3485
3486 END print_debuginfo;
3487
3488 --
3489 --
3490 --
3491 FUNCTION ifelse(p_bool IN BOOLEAN,
3492 x_true IN VARCHAR2,
3493 x_false IN VARCHAR2)
3494 RETURN VARCHAR2
3495 IS
3496 BEGIN
3497 --
3498 --
3499 --
3500 IF (p_bool) THEN
3501 RETURN x_true;
3502 ELSE
3503 RETURN x_false;
3504 END IF;
3505
3506 END ifelse;
3507
3508
3509 -- Start of comments
3510 -- API name : Get_Applicable_Payee_Acc_list
3511 -- Type : Public
3512 -- Pre-reqs : None.
3513 -- Function : get the list of all applicable Payee Bank Accounts.
3514 -- Parameters :
3515 -- IN : p_api_version IN NUMBER Required
3516 -- p_init_msg_list IN VARCHAR2 Optional
3517 -- p_trxn_attributes_rec IN Trxn_Attributes_Rec_Type Required
3518 -- OUT : x_return_status OUT VARCHAR2 Required
3519 -- x_msg_count OUT NUMBER Required
3520 -- x_msg_data OUT VARCHAR2 Required
3521 -- x_payee_bankaccounts_tbl OUT Payee_BankAccount_Tab_Type Required
3522 --
3523 -- Version : Current version 1.0
3524 -- Previous version None
3525 -- Initial version 1.0
3526 -- End of comments
3527
3528 PROCEDURE Get_Applicable_Payee_Acc_list (
3529 p_api_version IN NUMBER,
3530 p_init_msg_list IN VARCHAR2 default FND_API.G_FALSE,
3531 p_trxn_attributes_rec IN Trxn_Attributes_Rec_Type,
3532 x_return_status OUT NOCOPY VARCHAR2,
3533 x_msg_count OUT NOCOPY NUMBER,
3534 x_msg_data OUT NOCOPY VARCHAR2,
3535 x_payee_bankaccounts_tbl OUT NOCOPY Payee_BankAccount_Tab_Type
3536 )
3537 IS
3538
3539 l_api_name CONSTANT VARCHAR2(30) := 'Get_Applicable_Payee_BankAccts';
3540 l_api_version CONSTANT NUMBER := 1.0;
3541 l_module_name CONSTANT VARCHAR2(200) := G_PKG_NAME || '.Get_Applicable_Payee_BankAccts';
3542
3543 l_payee_bankaccounts_tbl Payee_BankAccount_Tab_Type;
3544
3545 CURSOR payee_bankacct_csr(p_payee_party_id NUMBER,
3546 p_payee_party_site_id NUMBER,
3547 p_supplier_site_id NUMBER,
3548 p_payer_org_id NUMBER,
3549 p_payer_org_type VARCHAR2,
3550 p_payment_currency VARCHAR2,
3551 p_payment_function VARCHAR2)
3552 IS
3553 SELECT b.bank_account_name,
3554 b.ext_bank_account_id,
3555 b.bank_account_number,
3556 b.currency_code,
3557 b.iban_number,
3558 b.bank_name,
3559 b.bank_number,
3560 b.bank_branch_name,
3561 b.branch_number,
3562 b.country_code,
3563 b.alternate_account_name,
3564 b.bank_account_type,
3565 b.account_suffix,
3566 b.description,
3567 b.foreign_payment_use_flag,
3568 b.payment_factor_flag,
3569 b.eft_swift_code
3570 FROM IBY_PMT_INSTR_USES_ALL ibyu,
3571 IBY_EXT_BANK_ACCOUNTS_V b,
3572 IBY_EXTERNAL_PAYEES_ALL ibypayee
3573 WHERE ibyu.instrument_id = b.ext_bank_account_id
3574 AND ibyu.instrument_type = 'BANKACCOUNT'
3575 AND (b.currency_code = p_payment_currency OR b.currency_code is null)
3576 AND ibyu.ext_pmt_party_id = ibypayee.ext_payee_id
3577 AND ibypayee.payment_function = p_payment_function
3578 AND ibypayee.payee_party_id = p_payee_party_id
3579 AND trunc(sysdate) between
3580 trunc(NVL(ibyu.start_date,sysdate)) AND trunc(NVL(ibyu.end_date-1,sysdate))
3581 AND trunc(sysdate) between
3582 trunc(NVL(b.start_date,sysdate)) AND trunc(NVL(b.end_date-1,sysdate))
3583 AND (ibypayee.party_site_id is null OR ibypayee.party_site_id = p_payee_party_site_id)
3584 AND (ibypayee.supplier_site_id is null OR ibypayee.supplier_site_id = p_supplier_site_id)
3585 AND (ibypayee.org_id is null OR
3586 (ibypayee.org_id = p_payer_org_id AND ibypayee.org_type = p_payer_org_type))
3587 AND ibyu.payment_flow='DISBURSEMENTS'
3588 ORDER BY
3589 ibypayee.supplier_site_id,
3590 ibypayee.party_site_id,
3591 ibypayee.org_id,
3592 ibyu.order_of_preference;
3593
3594 BEGIN
3595
3596 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3597 print_debuginfo(l_module_name,'ENTER');
3598 print_debuginfo(l_module_name,'Org Id : '|| p_trxn_attributes_rec.payer_org_id);
3599 print_debuginfo(l_module_name,'Org Type : '|| p_trxn_attributes_rec.payer_org_type);
3600 print_debuginfo(l_module_name,'Party Id : '|| p_trxn_attributes_rec.payee_party_id);
3601 print_debuginfo(l_module_name,'Party Site Id : '|| p_trxn_attributes_rec.payee_party_site_id);
3602 print_debuginfo(l_module_name,'Supplier Site Id : '|| p_trxn_attributes_rec.supplier_site_id);
3603 print_debuginfo(l_module_name,'Payment Currency : '|| p_trxn_attributes_rec.payment_currency);
3604 print_debuginfo(l_module_name,'Payment Amount : '|| p_trxn_attributes_rec.payment_amount);
3605 print_debuginfo(l_module_name,'Account Usage : '|| p_trxn_attributes_rec.payment_function);
3606
3607 END IF;
3608 -- Standard call to check for call compatibility.
3609 IF NOT FND_API.Compatible_API_Call(l_api_version,
3610 p_api_version,
3611 l_api_name,
3612 G_PKG_NAME) THEN
3613 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3614 END IF;
3615
3616 -- Initialize message list if p_init_msg_list is set to TRUE.
3617 IF FND_API.to_Boolean(p_init_msg_list) THEN
3618 FND_MSG_PUB.initialize;
3619 END IF;
3620
3621 -- Initialize API return status to success
3622 x_return_status := FND_API.G_RET_STS_SUCCESS;
3623
3624 -- Check for mandatory params
3625 IF (p_trxn_attributes_rec.payee_party_id IS NULL) THEN
3626 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3627 print_debuginfo(l_module_name,'Error: Mandatory Parameter ''Payee Party Id'' missing.');
3628 END IF;
3629 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
3630 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_PAYEE_PARTY_ID_FIELD'));
3631 FND_MSG_PUB.Add;
3632 RAISE FND_API.G_EXC_ERROR;
3633 END IF;
3634
3635 IF (p_trxn_attributes_rec.payment_currency IS NULL) THEN
3636 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3637 print_debuginfo(l_module_name,'Error: Mandatory Parameter ''Payment Currency'' missing.');
3638 END IF;
3639 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
3640 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_PMT_CURR_FIELD'));
3641 FND_MSG_PUB.Add;
3642 RAISE FND_API.G_EXC_ERROR;
3643 END IF;
3644
3645 IF (p_trxn_attributes_rec.payment_function IS NULL) THEN
3646 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3647 print_debuginfo(l_module_name,'Error: Mandatory Parameter ''Account Usage'' missing.');
3648 END IF;
3649 FND_MESSAGE.set_name('IBY', 'IBY_MISSING_MANDATORY_PARAM');
3650 FND_MESSAGE.SET_TOKEN('PARAM', fnd_message.GET_String('IBY','IBY_ACCT_USG_FIELD'));
3651 FND_MSG_PUB.Add;
3652 RAISE FND_API.G_EXC_ERROR;
3653 END IF;
3654
3655 -- Start of API body.
3656 OPEN payee_bankacct_csr(p_trxn_attributes_rec.Payee_Party_Id,
3657 p_trxn_attributes_rec.Payee_Party_Site_Id,
3658 p_trxn_attributes_rec.Supplier_Site_Id,
3659 p_trxn_attributes_rec.Payer_Org_Id,
3660 p_trxn_attributes_rec.Payer_Org_Type,
3661 p_trxn_attributes_rec.Payment_Currency,
3662 p_trxn_attributes_rec.Payment_Function);
3663
3664 FETCH payee_bankacct_csr BULK COLLECT INTO l_payee_bankaccounts_tbl;
3665 CLOSE payee_bankacct_csr;
3666
3667 IF (l_payee_bankaccounts_tbl.COUNT = 0) THEN
3668 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3669 print_debuginfo(l_module_name,'Warning: No Payee Bank Accounts Applicable');
3670 END IF;
3671 ELSE
3672 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3673 print_debuginfo(l_module_name,'Applicable Payee Bank Accounts Count : '|| l_payee_bankaccounts_tbl.COUNT);
3674 END IF;
3675 x_payee_bankaccounts_tbl := l_payee_bankaccounts_tbl;
3676 END IF;
3677
3678 -- End of API body.
3679
3680 -- Standard call to get message count and if count is 1, get message info.
3681 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3682
3683 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3684 print_debuginfo(l_module_name, 'RETURN');
3685
3686 END IF;
3687 EXCEPTION
3688 WHEN FND_API.G_EXC_ERROR THEN
3689 x_return_status := FND_API.G_RET_STS_ERROR;
3690 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3691 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3692 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
3693 print_debuginfo(l_module_name,'SQLerr is :'
3694 || substr(SQLERRM, 1, 150));
3695 END IF;
3696 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3697 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3698 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3699 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3700 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
3701 print_debuginfo(l_module_name,'SQLerr is :'
3702 || substr(SQLERRM, 1, 150));
3703 END IF;
3704 WHEN OTHERS THEN
3705 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3706
3707 IF (FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR))
3708 THEN
3709 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
3710 END IF;
3711 IF ( G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL) THEN
3712 print_debuginfo(l_module_name,'ERROR: Exception occured during call to API ');
3713 print_debuginfo(l_module_name,'SQLerr is :'
3714 || substr(SQLERRM, 1, 150));
3715 END IF;
3716 FND_MSG_PUB.Count_And_Get(p_count => x_msg_count, p_data => x_msg_data);
3717
3718 END Get_Applicable_Payee_Acc_list;
3719
3720 END IBY_DISBURSEMENT_COMP_PUB;
[Home] [Help]