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