[Home] [Help]
PACKAGE BODY: APPS.IBY_FNDCPT_SETUP_PUB
Source
1 PACKAGE BODY IBY_FNDCPT_SETUP_PUB AS
2 /*$Header: ibyfcstb.pls 120.30.12010000.6 2009/03/09 11:55:41 lmallick ship $*/
3
4
5 G_PKG_NAME CONSTANT VARCHAR2(30) := 'IBY_FNDCPT_SETUP_PUB';
6
7 G_DEBUG_MODULE CONSTANT VARCHAR2(100) := 'iby.plsql.IBY_FNDCPT_SETUP_PUB';
8
9
10 FUNCTION Exists_Pmt_Channel(p_pmt_channel IN VARCHAR2)
11 RETURN BOOLEAN
12 IS
13 l_code VARCHAR2(30);
14 l_exists BOOLEAN;
15
16 CURSOR c_channel(ci_channel_code IN iby_fndcpt_pmt_chnnls_b.payment_channel_code%TYPE)
17 IS
18 SELECT payment_channel_code
19 FROM iby_fndcpt_pmt_chnnls_b
20 WHERE (payment_channel_code = ci_channel_code)
21 AND (NVL(inactive_date,SYSDATE-10)<SYSDATE);
22 BEGIN
23
24 IF (c_channel%ISOPEN) THEN
25 CLOSE c_channel;
26 END IF;
27 OPEN c_channel(p_pmt_channel);
28 FETCH c_channel INTO l_code;
29 l_exists := NOT c_channel%NOTFOUND;
30 CLOSE c_channel;
31
32 RETURN l_exists;
33
34 END Exists_Pmt_Channel;
35
36 FUNCTION Exists_Instr(p_instr IN PmtInstrument_rec_type)
37 RETURN BOOLEAN
38 IS
39
40 l_instr_count NUMBER := 0;
41
42 CURSOR c_creditcard(ci_instrid IN iby_creditcard.instrid%TYPE)
43 IS
44 SELECT COUNT(instrid)
45 FROM iby_creditcard
46 WHERE (instrid = ci_instrid);
47
48 CURSOR c_bankaccount
49 (ci_instrid IN iby_ext_bank_accounts.ext_bank_account_id%TYPE)
50 IS
51 SELECT COUNT(ext_bank_account_id)
52 FROM iby_ext_bank_accounts
53 WHERE (ext_bank_account_id = ci_instrid);
54
55 BEGIN
56
57 IF (c_creditcard%ISOPEN) THEN
58 CLOSE c_creditcard;
59 END IF;
60 IF (c_bankaccount%ISOPEN) THEN
61 CLOSE c_bankaccount;
62 END IF;
63
64 IF (p_instr.Instrument_Type = IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_CREDITCARD)
65 THEN
66 OPEN c_creditcard(p_instr.Instrument_Id);
67 FETCH c_creditcard INTO l_instr_count;
68 CLOSE c_creditcard;
69 ELSIF (p_instr.Instrument_Type = IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_BANKACCT)
70 THEN
71 OPEN c_bankaccount(p_instr.Instrument_Id);
72 FETCH c_bankaccount INTO l_instr_count;
73 CLOSE c_bankaccount;
74 END IF;
75
76 IF (l_instr_count < 1) THEN
77 RETURN FALSE;
78 ELSE
79 RETURN TRUE;
80 END IF;
81
82 END Exists_Instr;
83
84 -- Validates the billing address passed for a credit card instrument
85 FUNCTION Validate_CC_Billing
86 ( p_is_update IN VARCHAR2, p_creditcard IN CreditCard_rec_type )
87 RETURN BOOLEAN
88 IS
89
90 lx_return_status VARCHAR2(1);
91 lx_msg_count NUMBER;
92 lx_msg_data VARCHAR2(3000);
93 lx_result IBY_FNDCPT_COMMON_PUB.Result_rec_type;
94 lx_channel_attribs PmtChannel_AttribUses_rec_type;
95
96 l_addressid iby_creditcard.addressid%TYPE;
97 l_billing_zip iby_creditcard.billing_addr_postal_code%TYPE;
98 l_billing_terr iby_creditcard.bill_addr_territory_code%TYPE;
99
100 BEGIN
101
102 IF (p_creditcard.Info_Only_Flag = 'Y') THEN
103 RETURN TRUE;
104 END IF;
105
106 l_addressid := p_creditcard.Billing_Address_Id;
107 l_billing_zip := p_creditcard.Billing_Postal_Code;
108 l_billing_terr := p_creditcard.Billing_Address_Territory;
109
110 IF FND_API.to_Boolean(p_is_update) THEN
111 IF (l_addressid = FND_API.G_MISS_NUM) THEN
112 l_addressid := NULL;
113 ELSIF (l_addressid IS NULL) THEN
114 l_addressid := FND_API.G_MISS_NUM;
115 END IF;
116 IF (l_billing_zip = FND_API.G_MISS_CHAR) THEN
117 l_billing_zip := NULL;
118 ELSIF (l_billing_zip IS NULL) THEN
119 l_billing_zip := FND_API.G_MISS_CHAR;
120 END IF;
121 IF (l_billing_terr = FND_API.G_MISS_CHAR) THEN
122 l_billing_terr := NULL;
123 ELSIF (l_billing_terr IS NULL) THEN
124 l_billing_terr := FND_API.G_MISS_CHAR;
125 END IF;
126 END IF;
127
128 IF ( (NOT (l_addressid IS NULL OR l_addressid = FND_API.G_MISS_NUM))
129 AND
130 (NOT (l_billing_zip IS NULL OR l_billing_zip = FND_API.G_MISS_CHAR))
131 )
132 THEN
133 RETURN FALSE;
134 END IF;
135
136 IF ( (NOT (l_billing_zip IS NULL OR l_billing_zip = FND_API.G_MISS_CHAR))
137 AND (l_billing_terr IS NULL OR l_billing_terr = FND_API.G_MISS_CHAR)
138 )
139 THEN
140 RETURN FALSE;
141 ELSIF ( (NOT (l_billing_terr IS NULL OR l_billing_terr = FND_API.G_MISS_CHAR))
142
143 AND (l_billing_zip IS NULL OR l_billing_zip = FND_API.G_MISS_CHAR)
144 )
145 THEN
146 RETURN FALSE;
147 END IF;
148
149 Get_Payment_Channel_Attribs
150 (1.0, FND_API.G_FALSE, lx_return_status, lx_msg_count, lx_msg_data,
151 G_CHANNEL_CREDIT_CARD, lx_channel_attribs, lx_result);
152
153 IF ((lx_channel_attribs.Instr_Billing_Address = G_CHNNL_ATTRIB_USE_REQUIRED)
154 AND ((l_addressid IS NULL) AND (l_billing_zip IS NULL))
155 )
156 THEN
157 RETURN FALSE;
158 END IF;
159
160 IF ((lx_channel_attribs.Instr_Billing_Address = G_CHNNL_ATTRIB_USE_DISABLED)
161 AND ((NOT l_addressid IS NULL) OR (NOT l_billing_zip IS NULL))
162 )
163 THEN
164 RETURN FALSE;
165 END IF;
166
167 RETURN TRUE;
168 END Validate_CC_Billing;
169
170 PROCEDURE Get_Payer_Id
171 (
172 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
173 p_validation_level IN VARCHAR2,
174 x_payer_level OUT NOCOPY VARCHAR2,
175 x_payer_id OUT NOCOPY iby_external_payers_all.ext_payer_id%TYPE,
176 x_payer_attribs OUT NOCOPY PayerAttributes_rec_type
177 )
178 IS
179
180 CURSOR c_payer
181 (ci_pmt_function IN p_payer.Payment_Function%TYPE,
182 ci_party_id IN p_payer.Party_Id%TYPE,
183 ci_account_id IN p_payer.Cust_Account_Id%TYPE,
184 ci_site_id IN p_payer.Account_Site_Id%TYPE,
185 ci_org_type IN p_payer.Org_Type%TYPE,
186 ci_org_id IN p_payer.Org_Id%TYPE,
187 ci_payer_level IN VARCHAR2)
188 IS
189 SELECT ext_payer_id, bank_charge_bearer_code, dirdeb_instruction_code
190 FROM iby_external_payers_all
191 WHERE (payment_function = ci_pmt_function)
192 AND (party_id = ci_party_id)
193 AND ((cust_account_id = ci_account_id)
194 OR (cust_account_id IS NULL AND ci_account_id IS NULL))
195 AND ((org_type = ci_org_type AND org_id = ci_org_id)
196 OR (org_type IS NULL AND org_id IS NULL AND ci_org_type IS NULL AND ci_org_id IS NULL))
197 AND ((acct_site_use_id = ci_site_id)
198 OR (acct_site_use_id IS NULL AND ci_site_id IS NULL));
199
200 BEGIN
201
202 IF (c_payer%ISOPEN) THEN
203 CLOSE c_payer;
204 END IF;
205
206 x_payer_level :=
207 IBY_FNDCPT_COMMON_PUB.Validate_Payer(p_payer,p_validation_level);
208
209 IF (x_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
210 x_payer_id := NULL;
211 RETURN;
212 END IF;
213
214 OPEN c_payer(p_payer.Payment_Function, p_payer.Party_Id,
215 p_payer.Cust_Account_Id, p_payer.Account_Site_Id, p_payer.Org_Type,
216 p_payer.Org_Id, x_payer_level);
217 FETCH c_payer INTO x_payer_id, x_payer_attribs.Bank_Charge_Bearer,
218 x_payer_attribs.DirectDebit_BankInstruction;
219 IF c_payer%NOTFOUND THEN x_payer_id := NULL; END IF;
220 CLOSE c_payer;
221
222 END Get_Payer_Id;
223
224
225 PROCEDURE Set_Payer_Attributes
226 (
227 p_api_version IN NUMBER,
228 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
229 p_commit IN VARCHAR2 := FND_API.G_TRUE,
230 x_return_status OUT NOCOPY VARCHAR2,
231 x_msg_count OUT NOCOPY NUMBER,
232 x_msg_data OUT NOCOPY VARCHAR2,
233 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
234 p_payer_attributes IN PayerAttributes_rec_type,
235 x_payer_attribs_id OUT NOCOPY NUMBER,
236 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
237 )
238 IS
239 l_api_version CONSTANT NUMBER := 1.0;
240 l_module CONSTANT VARCHAR2(30) := 'Set_Payer_Attributes';
241 l_payer_level VARCHAR2(30);
242 l_payer_attribs PayerAttributes_rec_type;
243 l_prev_msg_count NUMBER;
244
245 BEGIN
246
247 IF NOT FND_API.Compatible_API_Call (l_api_version,
248 p_api_version,
249 l_module,
250 G_PKG_NAME)
251 THEN
252 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
253 debug_level => FND_LOG.LEVEL_ERROR,
254 module => G_DEBUG_MODULE || l_module);
255 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
256 FND_MSG_PUB.Add;
257 RAISE FND_API.G_EXC_ERROR;
258 END IF;
259
260 IF FND_API.to_Boolean( p_init_msg_list ) THEN
261 FND_MSG_PUB.initialize;
262 END IF;
263 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
264
265 Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
266 l_payer_level,x_payer_attribs_id,l_payer_attribs);
267
268 IF (l_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
269 x_response.Result_Code := l_payer_level;
270 ELSIF (x_payer_attribs_id IS NULL) THEN
271
272 SELECT iby_external_payers_all_s.nextval
273 INTO x_payer_attribs_id
274 FROM dual;
275
276 INSERT INTO iby_external_payers_all
277 (ext_payer_id, payment_function, party_id, org_type, org_id,
278 cust_account_id, acct_site_use_id, bank_charge_bearer_code,
279 dirdeb_instruction_code, created_by, creation_date, last_updated_by,
280 last_update_date, last_update_login, object_version_number
281 )
282 VALUES
283 (x_payer_attribs_id, p_payer.Payment_Function,
284 p_payer.Party_Id, p_payer.Org_Type, p_payer.Org_Id,
285 p_payer.Cust_Account_Id, p_payer.Account_Site_Id,
286 p_payer_attributes.Bank_Charge_Bearer,
287 p_payer_attributes.DirectDebit_BankInstruction,
288 fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE,
289 fnd_global.login_id, 1
290 );
291 ELSIF (NOT (p_payer_attributes.DirectDebit_BankInstruction IS NULL)
292 AND (p_payer_attributes.Bank_Charge_Bearer IS NULL)
293 )
294 THEN
295
296 UPDATE iby_external_payers_all
297 SET
298 dirdeb_instruction_code =
299 DECODE(p_payer_attributes.DirectDebit_BankInstruction,
300 FND_API.G_MISS_CHAR,NULL, dirdeb_instruction_code),
301 bank_charge_bearer_code =
302 DECODE(p_payer_attributes.Bank_Charge_Bearer,
303 FND_API.G_MISS_CHAR,NULL, bank_charge_bearer_code),
304 last_updated_by = fnd_global.user_id,
305 last_update_date = SYSDATE,
306 last_update_login = fnd_global.login_id,
307 object_version_number = object_version_number + 1
308 WHERE (ext_payer_id = x_payer_attribs_id);
309 END IF;
310
311 IF FND_API.To_Boolean(p_commit) THEN
312 COMMIT;
313 END IF;
314
315 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
316 iby_fndcpt_common_pub.Prepare_Result
317 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
318
319 EXCEPTION
320
321 WHEN FND_API.G_EXC_ERROR THEN
322
323 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
324 debug_level => FND_LOG.LEVEL_ERROR,
325 module => G_DEBUG_MODULE || l_module);
326 x_return_status := FND_API.G_RET_STS_ERROR ;
327 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
328 p_data => x_msg_data
329 );
330 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
331
332 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
333 debug_level => FND_LOG.LEVEL_UNEXPECTED,
334 module => G_DEBUG_MODULE || l_module);
335 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
336 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
337 p_data => x_msg_data
338 );
339 WHEN OTHERS THEN
340
341 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
342 debug_level => FND_LOG.LEVEL_UNEXPECTED,
343 module => G_DEBUG_MODULE || l_module);
344
345 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
346
347 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
348 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
349 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
350 END IF;
351
352 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
353 p_data => x_msg_data
354 );
355 END Set_Payer_Attributes;
356
357 PROCEDURE Get_Payer_Attributes
358 (
359 p_api_version IN NUMBER,
360 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
361 x_return_status OUT NOCOPY VARCHAR2,
362 x_msg_count OUT NOCOPY NUMBER,
363 x_msg_data OUT NOCOPY VARCHAR2,
364 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
365 x_payer_attributes OUT NOCOPY PayerAttributes_rec_type,
366 x_payer_attribs_id OUT NOCOPY NUMBER,
367 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
368 )
369 IS
370 l_api_version CONSTANT NUMBER := 1.0;
371 l_module CONSTANT VARCHAR2(30) := 'Get_Payer_Attributes';
372 l_payer_level VARCHAR2(30);
373 l_prev_msg_count NUMBER;
374
375 BEGIN
376
377 IF NOT FND_API.Compatible_API_Call (l_api_version,
378 p_api_version,
379 l_module,
380 G_PKG_NAME)
381 THEN
382 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
383 debug_level => FND_LOG.LEVEL_ERROR,
384 module => G_DEBUG_MODULE || l_module);
385 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
386 FND_MSG_PUB.Add;
387 RAISE FND_API.G_EXC_ERROR;
388 END IF;
389
390 IF FND_API.to_Boolean( p_init_msg_list ) THEN
391 FND_MSG_PUB.initialize;
392 END IF;
393 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
394
395 Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
396 l_payer_level,x_payer_attribs_id,x_payer_attributes);
397 IF (x_payer_attribs_id IS NULL) THEN
398 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
399 ELSE
400 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
401 END IF;
402
403 iby_fndcpt_common_pub.Prepare_Result
404 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
405
406 EXCEPTION
407
408 WHEN FND_API.G_EXC_ERROR THEN
409
410 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
411 debug_level => FND_LOG.LEVEL_ERROR,
412 module => G_DEBUG_MODULE || l_module);
413 x_return_status := FND_API.G_RET_STS_ERROR ;
414 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
415 p_data => x_msg_data
416 );
417 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
418
419 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
420 debug_level => FND_LOG.LEVEL_UNEXPECTED,
421 module => G_DEBUG_MODULE || l_module);
422 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
423 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
424 p_data => x_msg_data
425 );
426 WHEN OTHERS THEN
427
428 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
429 debug_level => FND_LOG.LEVEL_UNEXPECTED,
430 module => G_DEBUG_MODULE || l_module);
431 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
432 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
433 FND_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, l_module);
434 END IF;
435
436 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
437 p_data => x_msg_data
438 );
439 END Get_Payer_Attributes;
440
441 PROCEDURE Get_Payment_Channel_Attribs
442 (
443 p_api_version IN NUMBER,
444 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
445 x_return_status OUT NOCOPY VARCHAR2,
446 x_msg_count OUT NOCOPY NUMBER,
447 x_msg_data OUT NOCOPY VARCHAR2,
448 p_channel_code IN VARCHAR2,
449 x_channel_attrib_uses OUT NOCOPY PmtChannel_AttribUses_rec_type,
450 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
451 )
452 IS
453 l_api_version CONSTANT NUMBER := 1.0;
454 l_module CONSTANT VARCHAR2(30) := 'Get_Payment_Channel_Attribs';
455 l_prev_msg_count NUMBER;
456
457 CURSOR c_appl_attribs
458 (ci_pmt_channel iby_fndcpt_pmt_chnnls_b.payment_channel_code%TYPE)
459 IS
460 SELECT NVL(isec.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL),
461 NVL(ibill.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL),
462 NVL(vaflag.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL),
463 NVL(vacode.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL),
464 NVL(vadate.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL),
465 NVL(ponum.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL),
466 NVL(poline.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL),
467 NVL(addinfo.attribute_applicability,G_CHNNL_ATTRIB_USE_OPTIONAL)
468 FROM iby_fndcpt_pmt_chnnls_b pc, iby_pmt_mthd_attrib_appl isec,
469 iby_pmt_mthd_attrib_appl ibill, iby_pmt_mthd_attrib_appl vaflag,
470 iby_pmt_mthd_attrib_appl vacode, iby_pmt_mthd_attrib_appl vadate,
471 iby_pmt_mthd_attrib_appl ponum, iby_pmt_mthd_attrib_appl poline,
472 iby_pmt_mthd_attrib_appl addinfo
473 WHERE (pc.payment_channel_code = ci_pmt_channel)
474 -- instrument security
475 AND (pc.payment_channel_code = isec.payment_method_code(+))
476 AND (isec.payment_flow(+) = 'FUNDS_CAPTURE')
477 AND (isec.attribute_code(+) = 'INSTR_SECURITY_CODE')
478 -- instrument billing address
479 AND (pc.payment_channel_code = ibill.payment_method_code(+))
480 AND (ibill.attribute_code(+) = 'INSTR_BILLING_ADDRESS')
481 AND (ibill.payment_flow(+) = 'FUNDS_CAPTURE')
482 -- voice auth flag
483 AND (pc.payment_channel_code = vaflag.payment_method_code(+))
484 AND (vaflag.attribute_code(+) = 'VOICE_AUTH_FLAG')
485 AND (vaflag.payment_flow(+) = 'FUNDS_CAPTURE')
486 -- voice auth code
487 AND (pc.payment_channel_code = vacode.payment_method_code(+))
488 AND (vacode.attribute_code(+) = 'VOICE_AUTH_CODE')
489 AND (vacode.payment_flow(+) = 'FUNDS_CAPTURE')
490 -- voice auth date
491 AND (pc.payment_channel_code = vadate.payment_method_code(+))
492 AND (vadate.attribute_code(+) = 'VOICE_AUTH_DATE')
493 AND (vadate.payment_flow(+) = 'FUNDS_CAPTURE')
494 -- purcharse order number
495 AND (pc.payment_channel_code = ponum.payment_method_code(+))
496 AND (ponum.attribute_code(+) = 'PO_NUMBER')
497 AND (ponum.payment_flow(+) = 'FUNDS_CAPTURE')
498 -- purchase order line
499 AND (pc.payment_channel_code = poline.payment_method_code(+))
500 AND (poline.attribute_code(+) = 'PO_LINE_NUMBER')
501 AND (poline.payment_flow(+) = 'FUNDS_CAPTURE')
502 -- additional info
503 AND (pc.payment_channel_code = addinfo.payment_method_code(+))
504 AND (addinfo.attribute_code(+) = 'ADDITIONAL_INFO')
505 AND (addinfo.payment_flow(+) = 'FUNDS_CAPTURE');
506
507 BEGIN
508
509 IF (c_appl_attribs%ISOPEN) THEN CLOSE c_appl_attribs; END IF;
510
511 IF NOT FND_API.Compatible_API_Call (l_api_version,
512 p_api_version,
513 l_module,
514 G_PKG_NAME)
515 THEN
516 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
517 debug_level => FND_LOG.LEVEL_ERROR,
518 module => G_DEBUG_MODULE || l_module);
519 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
520 FND_MSG_PUB.Add;
521 RAISE FND_API.G_EXC_ERROR;
522 END IF;
523
524 IF FND_API.to_Boolean( p_init_msg_list ) THEN
525 FND_MSG_PUB.initialize;
526 END IF;
527 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
528
529 OPEN c_appl_attribs(p_channel_code);
530 FETCH c_appl_attribs INTO
531 x_channel_attrib_uses.Instr_SecCode_Use,
532 x_channel_attrib_uses.Instr_Billing_Address,
533 x_channel_attrib_uses.Instr_VoiceAuthFlag_Use,
534 x_channel_attrib_uses.Instr_VoiceAuthCode_Use,
535 x_channel_attrib_uses.Instr_VoiceAuthDate_Use,
536 x_channel_attrib_uses.PO_Number_Use,
537 x_channel_attrib_uses.PO_Line_Number_Use,
538 x_channel_attrib_uses.AddInfo_Use;
539
540 IF (c_appl_attribs%NOTFOUND) THEN
541 x_response.Result_Code := G_RC_INVALID_CHNNL;
542 ELSE
543 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
544 END IF;
545
546 CLOSE c_appl_attribs;
547
548 iby_fndcpt_common_pub.Prepare_Result
549 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
550
551 EXCEPTION
552
553 WHEN FND_API.G_EXC_ERROR THEN
554
555 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
556 debug_level => FND_LOG.LEVEL_ERROR,
557 module => G_DEBUG_MODULE || l_module);
558 x_return_status := FND_API.G_RET_STS_ERROR ;
559 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
560 p_data => x_msg_data
561 );
562 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
563
564 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
565 debug_level => FND_LOG.LEVEL_UNEXPECTED,
566 module => G_DEBUG_MODULE || l_module);
567 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
568 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
569 p_data => x_msg_data
570 );
571 WHEN OTHERS THEN
572
573 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
574 debug_level => FND_LOG.LEVEL_UNEXPECTED,
575 module => G_DEBUG_MODULE || l_module);
576
577 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
578
579 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
580 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
581 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
582 END IF;
583
584 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
585 p_data => x_msg_data
586 );
587 END Get_Payment_Channel_Attribs;
588
589 PROCEDURE Set_Payer_Default_Pmt_Channel
590 (
591 p_api_version IN NUMBER,
592 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
593 p_commit IN VARCHAR2 := FND_API.G_TRUE,
594 x_return_status OUT NOCOPY VARCHAR2,
595 x_msg_count OUT NOCOPY NUMBER,
596 x_msg_data OUT NOCOPY VARCHAR2,
597 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
598 p_channel_assignment IN PmtChannelAssignment_rec_type,
599 x_assignment_id OUT NOCOPY NUMBER,
600 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
601 )
602 IS
603 l_api_version CONSTANT NUMBER := 1.0;
604 l_module CONSTANT VARCHAR2(30) := 'Set_Payer_Default_Pmt_Channel';
605 l_payer_level VARCHAR2(30);
606 l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
607 l_payer_attribs PayerAttributes_rec_type;
608
609 l_result IBY_FNDCPT_COMMON_PUB.Result_rec_type;
610 l_prev_msg_count NUMBER;
611
612 CURSOR c_chnnl_assign
613 (ci_payer_id IN iby_ext_party_pmt_mthds.ext_pmt_party_id%TYPE)
614 IS
615 SELECT ext_party_pmt_mthd_id
616 FROM iby_ext_party_pmt_mthds
617 WHERE (ext_pmt_party_id = ci_payer_id)
618 AND (payment_flow = G_PMT_FLOW_FNDCPT)
619 AND (primary_flag = 'Y')
620 AND (NVL(inactive_date,SYSDATE-10)<SYSDATE);
621 BEGIN
622
623 IF (c_chnnl_assign%ISOPEN) THEN
624 CLOSE c_chnnl_assign;
625 END IF;
626
627 IF NOT FND_API.Compatible_API_Call (l_api_version,
628 p_api_version,
629 l_module,
630 G_PKG_NAME)
631 THEN
632 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
633 debug_level => FND_LOG.LEVEL_ERROR,
634 module => G_DEBUG_MODULE || l_module);
635 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
636 FND_MSG_PUB.Add;
637 RAISE FND_API.G_EXC_ERROR;
638 END IF;
639
640 IF FND_API.to_Boolean( p_init_msg_list ) THEN
641 FND_MSG_PUB.initialize;
642 END IF;
643 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
644
645 Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,l_payer_level,
646 l_payer_id,l_payer_attribs);
647
648 IF (l_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
649 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
650 ELSIF (NOT Exists_Pmt_Channel(p_channel_assignment.Pmt_Channel_Code)) THEN
651 x_response.Result_Code := G_RC_INVALID_CHNNL;
652 ELSE
653
654 SAVEPOINT Set_Payer_Default_Pmt_Channel;
655
656 IF (l_payer_id IS NULL) THEN
657 IBY_FNDCPT_SETUP_PUB.Set_Payer_Attributes
658 (
659 1.0,
660 FND_API.G_FALSE,
661 FND_API.G_FALSE,
662 x_return_status,
663 x_msg_count,
664 x_msg_data,
665 p_payer,
666 l_payer_attribs,
667 l_payer_id,
668 l_result
669 );
670
671 IF (l_result.Result_Code <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS) THEN
672 x_response := l_result;
673 RETURN;
674 END IF;
675 END IF;
676
677 OPEN c_chnnl_assign(l_payer_id);
678 FETCH c_chnnl_assign INTO x_assignment_id;
679 CLOSE c_chnnl_assign;
680 IF (x_assignment_id IS NULL) THEN
681
682 SELECT iby_ext_party_pmt_mthds_s.NEXTVAL
683 INTO x_assignment_id
684 FROM DUAL;
685
686 INSERT INTO iby_ext_party_pmt_mthds
687 (ext_party_pmt_mthd_id, payment_method_code, payment_flow,
688 ext_pmt_party_id, payment_function, primary_flag, inactive_date,
689 created_by, creation_date, last_updated_by, last_update_date,
690 last_update_login, object_version_number)
691 VALUES
692 (x_assignment_id, p_channel_assignment.Pmt_Channel_Code,
693 G_PMT_FLOW_FNDCPT, l_payer_id, p_payer.Payment_Function, 'Y',
694 p_channel_assignment.Inactive_Date, fnd_global.user_id, SYSDATE,
695 fnd_global.user_id, SYSDATE, fnd_global.login_id, 1);
696
697 ELSE
698
699 UPDATE iby_ext_party_pmt_mthds
700 SET inactive_date = p_channel_assignment.Inactive_Date,
701 payment_method_code =
702 NVL(p_channel_assignment.Pmt_Channel_code,payment_method_code),
703 last_updated_by = fnd_global.user_id,
704 last_update_date = SYSDATE,
705 last_update_login = fnd_global.login_id,
706 object_version_number = object_version_number + 1
707 WHERE ext_party_pmt_mthd_id = x_assignment_id;
708
709 END IF;
710
711 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
712 END IF;
713
714
715 iby_fndcpt_common_pub.Prepare_Result
716 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
717
718 IF FND_API.To_Boolean(p_commit) THEN
719 COMMIT;
720 END IF;
721
722 EXCEPTION
723
724 WHEN FND_API.G_EXC_ERROR THEN
725 ROLLBACK TO Set_Payer_Default_Pmt_Channel;
726 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
727 debug_level => FND_LOG.LEVEL_ERROR,
728 module => G_DEBUG_MODULE || l_module);
729 x_return_status := FND_API.G_RET_STS_ERROR ;
730 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
731 p_data => x_msg_data
732 );
733 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
734 ROLLBACK TO Set_Payer_Default_Pmt_Channel;
735 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
736 debug_level => FND_LOG.LEVEL_UNEXPECTED,
737 module => G_DEBUG_MODULE || l_module);
738 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
739 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
740 p_data => x_msg_data
741 );
742 WHEN OTHERS THEN
743 ROLLBACK TO Set_Payer_Default_Pmt_Channel;
744
745 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
746 debug_level => FND_LOG.LEVEL_UNEXPECTED,
747 module => G_DEBUG_MODULE || l_module);
748
749 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
750
751 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
752 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
753 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
754 END IF;
755
756 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
757 p_data => x_msg_data
758 );
759 END Set_Payer_Default_Pmt_Channel;
760
761 PROCEDURE Get_Payer_Default_Pmt_Channel
762 (
763 p_api_version IN NUMBER,
764 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
765 x_return_status OUT NOCOPY VARCHAR2,
766 x_msg_count OUT NOCOPY NUMBER,
767 x_msg_data OUT NOCOPY VARCHAR2,
768 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
769 x_channel_assignment OUT NOCOPY PmtChannelAssignment_rec_type,
770 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
771 )
772 IS
773 l_api_version CONSTANT NUMBER := 1.0;
774 l_module CONSTANT VARCHAR2(30) := 'Get_Payer_Default_Pmt_Channel';
775 l_payer_level VARCHAR2(30);
776 l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
777 l_payer_attribs PayerAttributes_rec_type;
778 l_prev_msg_count NUMBER;
779
780 CURSOR c_chnnl_assign
781 (ci_payer_id IN iby_ext_party_pmt_mthds.ext_pmt_party_id%TYPE)
782 IS
783 SELECT payment_method_code, primary_flag, inactive_date
784 FROM iby_ext_party_pmt_mthds
785 WHERE (ext_pmt_party_id = ci_payer_id)
786 AND (payment_flow = G_PMT_FLOW_FNDCPT)
787 AND (primary_flag = 'Y')
788 AND (NVL(inactive_date,SYSDATE-10)<SYSDATE);
789
790 BEGIN
791
792 IF (c_chnnl_assign%ISOPEN) THEN
793 CLOSE c_chnnl_assign;
794 END IF;
795
796 IF NOT FND_API.Compatible_API_Call (l_api_version,
797 p_api_version,
798 l_module,
799 G_PKG_NAME)
800 THEN
801 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
802 debug_level => FND_LOG.LEVEL_ERROR,
803 module => G_DEBUG_MODULE || l_module);
804 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
805 FND_MSG_PUB.Add;
806 RAISE FND_API.G_EXC_ERROR;
807 END IF;
808
809 IF FND_API.to_Boolean( p_init_msg_list ) THEN
810 FND_MSG_PUB.initialize;
811 END IF;
812 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
813
814 Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
815 l_payer_level,l_payer_id,l_payer_attribs);
816
817 IF (l_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
818 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
819 ELSE
820 OPEN c_chnnl_assign(l_payer_id);
821 FETCH c_chnnl_assign INTO x_channel_assignment.Pmt_Channel_Code,
822 x_channel_assignment.Default_Flag, x_channel_assignment.Inactive_Date;
823 IF (c_chnnl_assign%NOTFOUND) THEN
824 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
825 ELSE
826 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
827 END IF;
828 CLOSE c_chnnl_assign;
829 END IF;
830
831 iby_fndcpt_common_pub.Prepare_Result
832 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
833
834 EXCEPTION
835
836 WHEN FND_API.G_EXC_ERROR THEN
837
838 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
839 debug_level => FND_LOG.LEVEL_ERROR,
840 module => G_DEBUG_MODULE || l_module);
841 x_return_status := FND_API.G_RET_STS_ERROR ;
842 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
843 p_data => x_msg_data
844 );
845 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
846
847 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
848 debug_level => FND_LOG.LEVEL_UNEXPECTED,
849 module => G_DEBUG_MODULE || l_module);
850 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
851 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
852 p_data => x_msg_data
853 );
854 WHEN OTHERS THEN
855
856 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
857 debug_level => FND_LOG.LEVEL_UNEXPECTED,
858 module => G_DEBUG_MODULE || l_module);
859
860 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
861
862 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
863 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
864 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
865 END IF;
866
867 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
868 p_data => x_msg_data
869 );
870 END Get_Payer_Default_Pmt_Channel;
871
872 PROCEDURE Get_Trxn_Appl_Pmt_Channels
873 (
874 p_api_version IN NUMBER,
875 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
876 x_return_status OUT NOCOPY VARCHAR2,
877 x_msg_count OUT NOCOPY NUMBER,
878 x_msg_data OUT NOCOPY VARCHAR2,
879 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
880 p_payer_equivalency IN VARCHAR2
881 := IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
882 p_conditions IN IBY_FNDCPT_COMMON_PUB.TrxnContext_rec_type,
883 p_result_limit IN IBY_FNDCPT_COMMON_PUB.ResultLimit_rec_type,
884 x_channels OUT NOCOPY PmtChannel_tbl_type,
885 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
886 )
887 IS
888 l_api_version CONSTANT NUMBER := 1.0;
889 l_module CONSTANT VARCHAR2(30) := 'Get_Trxn_Appl_Pmt_Channels';
890 l_payer_level VARCHAR2(30);
891 l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
892 l_payer_attribs PayerAttributes_rec_type;
893 l_prev_msg_count NUMBER;
894
895 l_channel_count NUMBER;
896
897 -- currently do not use any transaction values for applicability;
898 -- all system channels are applicable that are not site-wide
899 -- deactivated (end-dated)
900 --
901 CURSOR c_trxn_channels
902 (ci_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
903 ci_payer_level IN VARCHAR2,
904 ci_payer_equiv IN VARCHAR2)
905 IS
906 SELECT c.payment_channel_code, c.instrument_type
907 FROM iby_ext_party_pmt_mthds pm, iby_fndcpt_pmt_chnnls_b c
908 WHERE (pm.payment_method_code = c.payment_channel_code)
909 AND (NVL(pm.inactive_date,SYSDATE-10)<SYSDATE)
910 AND (NVL(c.inactive_date,SYSDATE-10)<SYSDATE)
911 AND (pm.payment_flow = G_PMT_FLOW_FNDCPT)
912 AND pm.ext_pmt_party_id IN
913 (
914 SELECT ext_payer_id
915 FROM iby_external_payers_all
916 WHERE (payment_function = ci_payer.Payment_Function)
917 AND (party_id = ci_payer.Party_Id)
918 AND (IBY_FNDCPT_COMMON_PUB.Compare_Payer
919 (ci_payer.org_type, ci_payer.org_id,
920 ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
921 ci_payer_level,ci_payer_equiv,org_type,org_id,
922 cust_account_id,acct_site_use_id) = 'T')
923 );
924 BEGIN
925
926 IF (c_trxn_channels%ISOPEN) THEN
927 CLOSE c_trxn_channels;
928 END IF;
929
930 IF NOT FND_API.Compatible_API_Call (l_api_version,
931 p_api_version,
932 l_module,
933 G_PKG_NAME)
934 THEN
935 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
936 debug_level => FND_LOG.LEVEL_ERROR,
937 module => G_DEBUG_MODULE || l_module);
938 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
939 FND_MSG_PUB.Add;
940 RAISE FND_API.G_EXC_ERROR;
941 END IF;
942
943 IF FND_API.to_Boolean( p_init_msg_list ) THEN
944 FND_MSG_PUB.initialize;
945 END IF;
946 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
947
948 Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
949 l_payer_level,l_payer_id,l_payer_attribs);
950
951 IF (l_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
952 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
953 ELSE
954 l_channel_count := 0;
955
956 FOR channel_rec IN c_trxn_channels(p_payer,l_payer_level,
957 p_payer_equivalency)
958 LOOP
959 l_channel_count := l_channel_count + 1;
960 x_channels(l_channel_count).Pmt_Channel_Code :=
961 channel_rec.payment_channel_code;
962 x_channels(l_channel_count).Instrument_Type := channel_rec.instrument_type;
963 END LOOP;
964
965 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
966 END IF;
967
968 iby_fndcpt_common_pub.Prepare_Result
969 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
970
971 EXCEPTION
972
973 WHEN FND_API.G_EXC_ERROR THEN
974
975 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
976 debug_level => FND_LOG.LEVEL_ERROR,
977 module => G_DEBUG_MODULE || l_module);
978 x_return_status := FND_API.G_RET_STS_ERROR ;
979 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
980 p_data => x_msg_data
981 );
982 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
983
984 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
985 debug_level => FND_LOG.LEVEL_UNEXPECTED,
986 module => G_DEBUG_MODULE || l_module);
987 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
988 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
989 p_data => x_msg_data
990 );
991 WHEN OTHERS THEN
992
993 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
994 debug_level => FND_LOG.LEVEL_UNEXPECTED,
995 module => G_DEBUG_MODULE || l_module);
996
997 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
998
999 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1000 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1001 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
1002 END IF;
1003
1004 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1005 p_data => x_msg_data
1006 );
1007 END Get_Trxn_Appl_Pmt_Channels;
1008
1009
1010 PROCEDURE Set_Payer_Instr_Assignment
1011 (
1012 p_api_version IN NUMBER,
1013 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1014 p_commit IN VARCHAR2 := FND_API.G_TRUE,
1015 x_return_status OUT NOCOPY VARCHAR2,
1016 x_msg_count OUT NOCOPY NUMBER,
1017 x_msg_data OUT NOCOPY VARCHAR2,
1018 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
1019 p_assignment_attribs IN PmtInstrAssignment_rec_type,
1020 x_assign_id OUT NOCOPY NUMBER,
1021 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
1022 )
1023 IS
1024 l_api_version CONSTANT NUMBER := 1.0;
1025 l_module CONSTANT VARCHAR2(30) := 'Set_Payer_Instr_Assignment';
1026 l_payer_level VARCHAR2(30);
1027 l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
1028 l_payer_attribs PayerAttributes_rec_type;
1029 l_prev_msg_count NUMBER;
1030
1031 l_result IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1032
1033 l_assign_id NUMBER;
1034 l_instr_id NUMBER;
1035 l_priority NUMBER;
1036
1037 -- for call to TCA hook
1038 l_last_update DATE;
1039 l_op_type VARCHAR2(1);
1040 l_parent_type VARCHAR2(50);
1041 l_parent_table VARCHAR2(50);
1042 l_parent_id NUMBER;
1043 l_party_type VARCHAR2(50);
1044 l_instr_type VARCHAR2(50);
1045
1046 CURSOR c_instr_assignment
1047 (ci_assign_id IN iby_pmt_instr_uses_all.instrument_payment_use_id%TYPE,
1048 ci_payer_id IN iby_pmt_instr_uses_all.ext_pmt_party_id%TYPE,
1049 ci_instr_type IN iby_pmt_instr_uses_all.instrument_type%TYPE,
1050 ci_instr_id IN iby_pmt_instr_uses_all.instrument_id%TYPE
1051 )
1052 IS
1053 SELECT instrument_payment_use_id
1054 FROM iby_pmt_instr_uses_all
1055 WHERE (payment_flow = G_PMT_FLOW_FNDCPT)
1056 AND ( (instrument_payment_use_id = NVL(ci_assign_id,-1))
1057 OR (ext_pmt_party_id = ci_payer_id
1058 AND instrument_type = ci_instr_type
1059 AND instrument_id = ci_instr_id
1060 )
1061 );
1062
1063 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
1064 BEGIN
1065 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
1066
1067 IF (c_instr_assignment%ISOPEN) THEN CLOSE c_instr_assignment; END IF;
1068
1069 IF NOT FND_API.Compatible_API_Call (l_api_version,
1070 p_api_version,
1071 l_module,
1072 G_PKG_NAME)
1073 THEN
1074 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
1075 debug_level => FND_LOG.LEVEL_ERROR,
1076 module => G_DEBUG_MODULE || l_module);
1077 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1078 FND_MSG_PUB.Add;
1079 RAISE FND_API.G_EXC_ERROR;
1080 END IF;
1081
1082 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1083 FND_MSG_PUB.initialize;
1084 END IF;
1085 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
1086
1087 Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
1088 l_payer_level,l_payer_id,l_payer_attribs);
1089
1090 IF (l_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
1091 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
1092 --
1093 -- CHANGE?: does PL/SQL do logical short circuiting? If not then
1094 -- change the condition evaluations as the exists_instrument
1095 -- function is relatively expensive
1096 --
1097 ELSIF ( (p_assignment_attribs.Assignment_Id IS NULL) AND
1098 (NOT Exists_Instr(p_assignment_attribs.Instrument)) ) THEN
1099 x_response.Result_Code := G_RC_INVALID_INSTRUMENT;
1100 ELSE
1101 SAVEPOINT Set_Payer_Instr_Assignment;
1102 -- create the payer entity if it does not exist
1103 IF (l_payer_id IS NULL) THEN
1104 IBY_FNDCPT_SETUP_PUB.Set_Payer_Attributes
1105 (
1106 1.0,
1107 FND_API.G_FALSE,
1108 FND_API.G_FALSE,
1109 x_return_status,
1110 x_msg_count,
1111 x_msg_data,
1112 p_payer,
1113 l_payer_attribs,
1114 l_payer_id,
1115 l_result
1116 );
1117 IF (l_result.Result_Code <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS) THEN
1118 x_response := l_result;
1119 RETURN;
1120 END IF;
1121 END IF;
1122
1123 -- for the combined query cursor, only 1 query condition should be used,
1124 -- either the assingment id or the (payer id, instr type, instr id)
1125 -- combination
1126 --
1127 IF (NOT p_assignment_attribs.Assignment_Id IS NULL) THEN
1128 l_assign_id := p_assignment_attribs.Assignment_Id;
1129 ELSE
1130 l_instr_id := p_assignment_attribs.Instrument.Instrument_Id;
1131 END IF;
1132
1133 OPEN c_instr_assignment(l_assign_id,l_payer_id,
1134 p_assignment_attribs.Instrument.Instrument_Type,
1135 l_instr_id);
1136 FETCH c_instr_assignment INTO x_assign_id;
1137
1138 IF (c_instr_assignment%NOTFOUND) THEN x_assign_id := NULL; END IF;
1139 CLOSE c_instr_assignment;
1140
1141 -- assignment id passed is non-NULL but no instruments found
1142 IF ((x_assign_id IS NULL) AND (NOT l_assign_id IS NULL)) THEN
1143 x_response.Result_Code := G_RC_INVALID_INSTR_ASSIGN;
1144 iby_fndcpt_common_pub.Prepare_Result
1145 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1146 RETURN;
1147 END IF;
1148
1149 l_priority := GREATEST(NVL(p_assignment_attribs.Priority,1),1);
1150
1151 -- only need to shift instrument priorities if this is a new instrument
1152 -- or if this is an update with a non-NULL priority
1153 IF (x_assign_id IS NULL) OR
1154 ((NOT x_assign_id IS NULL) AND (NOT p_assignment_attribs.Priority IS NULL))
1155 THEN
1156
1157 UPDATE iby_pmt_instr_uses_all
1158 SET order_of_preference = order_of_preference + 1,
1159 last_updated_by = fnd_global.user_id,
1160 last_update_date = SYSDATE,
1161 last_update_login = fnd_global.login_id,
1162 object_version_number = object_version_number + 1
1163 WHERE instrument_payment_use_id IN
1164 (
1165 SELECT instrument_payment_use_id
1166 FROM iby_pmt_instr_uses_all
1167 WHERE (ext_pmt_party_id = l_payer_id)
1168 AND (payment_flow = G_PMT_FLOW_FNDCPT)
1169 START WITH order_of_preference = l_priority
1170 AND (ext_pmt_party_id = l_payer_id)
1171 AND (payment_flow = G_PMT_FLOW_FNDCPT)
1172 CONNECT BY order_of_preference = PRIOR (order_of_preference + 1)
1173 AND (ext_pmt_party_id = PRIOR ext_pmt_party_id)
1174 AND (payment_flow = PRIOR payment_flow)
1175 );
1176
1177 END IF;
1178
1179 l_last_update := SYSDATE;
1180
1181 IF (x_assign_id IS NULL) THEN
1182 SELECT iby_pmt_instr_uses_all_s.nextval
1183 INTO x_assign_id
1184 FROM DUAL;
1185
1186 INSERT INTO iby_pmt_instr_uses_all
1187 (instrument_payment_use_id, ext_pmt_party_id, instrument_type,
1188 instrument_id, payment_function, payment_flow, order_of_preference,
1189 debit_auth_flag, debit_auth_method, debit_auth_reference,
1190 debit_auth_begin, debit_auth_end, start_date, end_date,
1191 created_by, creation_date, last_updated_by, last_update_date,
1192 last_update_login, object_version_number)
1193 VALUES
1194 (x_assign_id, l_payer_id,
1195 p_assignment_attribs.Instrument.Instrument_Type,
1196 p_assignment_attribs.Instrument.Instrument_Id,
1197 p_payer.Payment_Function, G_PMT_FLOW_FNDCPT, l_priority,
1198 null, null, null, null, null,
1199 NVL(p_assignment_attribs.Start_Date,SYSDATE),
1200 p_assignment_attribs.End_Date,
1201 fnd_global.user_id, SYSDATE, fnd_global.user_id, l_last_update,
1202 fnd_global.login_id, 1);
1203
1204 l_op_type := 'I';
1205 ELSE
1206 UPDATE iby_pmt_instr_uses_all
1207 SET
1208 order_of_preference =
1209 NVL(p_assignment_attribs.Priority,order_of_preference),
1210 start_date = NVL(p_assignment_attribs.Start_Date,start_date),
1211 end_date = p_assignment_attribs.End_Date,
1212 last_updated_by = fnd_global.user_id,
1213 last_update_date = l_last_update,
1214 last_update_login = fnd_global.login_id,
1215 object_version_number = object_version_number + 1
1216 WHERE instrument_payment_use_id = x_assign_id;
1217
1218 l_op_type := 'U';
1219 END IF;
1220
1221 iby_debug_pub.add('begin HZ hook',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1222
1223 IF (NOT p_payer.Account_Site_Id IS NULL) THEN
1224 l_parent_type := 'CUST_ACCT_SITE_USE';
1225 l_parent_table := 'HZ_CUST_SITE_USES_ALL';
1226 l_parent_id := p_payer.Account_Site_Id;
1227 ELSIF (NOT p_payer.Cust_Account_Id IS NULL) THEN
1228 l_parent_type := 'CUST_ACCT';
1229 l_parent_table := 'HZ_CUST_ACCOUNTS';
1230 l_parent_id := p_payer.Cust_Account_Id;
1231 END IF;
1232
1233 SELECT instrument_type
1234 INTO l_instr_type
1235 FROM iby_pmt_instr_uses_all
1236 WHERE instrument_payment_use_id = x_assign_id;
1237
1238 IF (l_instr_type = 'BANKACCOUNT') THEN
1239 SELECT party_type INTO l_party_type
1240 FROM hz_parties WHERE party_id = p_payer.Party_Id;
1241
1242 HZ_BES_BO_TRACKING_PVT.Create_Bot
1243 (p_init_msg_list => fnd_api.g_false,
1244 p_child_bo_code => NULL,
1245 p_child_tbl_name => 'IBY_FNDCPT_PAYER_ASSGN_INSTR_V',
1246 p_child_id => x_assign_id,
1247 p_child_opr_flag => l_op_type,
1248 p_child_update_dt => l_last_update,
1249 p_parent_bo_code => l_parent_type,
1250 p_parent_tbl_name => l_parent_table,
1251 p_parent_id => l_parent_id,
1252 p_parent_opr_flag => NULL,
1253 p_gparent_bo_code => l_party_type,
1254 p_gparent_tbl_name => 'HZ_PARTIES',
1255 p_gparent_id => p_payer.Party_Id,
1256 x_return_status => x_return_status,
1257 x_msg_count => x_msg_count,
1258 x_msg_data => x_msg_data
1259 );
1260 END IF;
1261
1262 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
1263 END IF;
1264
1265 IF FND_API.To_Boolean(p_commit) THEN
1266 COMMIT;
1267 END IF;
1268
1269 iby_fndcpt_common_pub.Prepare_Result
1270 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1271
1272 iby_debug_pub.add('Exit',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
1273 EXCEPTION
1274
1275 WHEN FND_API.G_EXC_ERROR THEN
1276 ROLLBACK TO Set_Payer_Instr_Assignment;
1277 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
1278 debug_level => FND_LOG.LEVEL_ERROR,
1279 module => G_DEBUG_MODULE || l_module);
1280 x_return_status := FND_API.G_RET_STS_ERROR ;
1281 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1282 p_data => x_msg_data
1283 );
1284 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1285 ROLLBACK TO Set_Payer_Instr_Assignment;
1286 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
1287 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1288 module => G_DEBUG_MODULE || l_module);
1289 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1290 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1291 p_data => x_msg_data
1292 );
1293 WHEN OTHERS THEN
1294 ROLLBACK TO Set_Payer_Instr_Assignment;
1295 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
1296 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1297 module => G_DEBUG_MODULE || l_module);
1298
1299 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
1300
1301 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1302 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1303 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
1304 END IF;
1305
1306 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1307 p_data => x_msg_data
1308 );
1309 END Set_Payer_Instr_Assignment;
1310
1311
1312 PROCEDURE Get_Payer_Instr_Assignments
1313 (
1314 p_api_version IN NUMBER,
1315 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1316 x_return_status OUT NOCOPY VARCHAR2,
1317 x_msg_count OUT NOCOPY NUMBER,
1318 x_msg_data OUT NOCOPY VARCHAR2,
1319 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
1320 x_assignments OUT NOCOPY PmtInstrAssignment_tbl_type,
1321 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
1322 )
1323 IS
1324 l_api_version CONSTANT NUMBER := 1.0;
1325 l_module CONSTANT VARCHAR2(30) := 'Get_Payer_Instr_Assignments';
1326 l_payer_level VARCHAR2(30);
1327 l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
1328 l_payer_attribs PayerAttributes_rec_type;
1329 l_assign_count NUMBER := 0;
1330 l_prev_msg_count NUMBER;
1331
1332 CURSOR c_instr_assignments
1333 (ci_payer_id IN iby_pmt_instr_uses_all.ext_pmt_party_id%TYPE)
1334 IS
1335 SELECT instrument_payment_use_id, instrument_type, instrument_id,
1336 order_of_preference, start_date, end_date
1337 FROM iby_pmt_instr_uses_all
1338 WHERE (payment_flow = G_PMT_FLOW_FNDCPT)
1339 AND (ext_pmt_party_id = ci_payer_id);
1340
1341 BEGIN
1342
1343 IF (c_instr_assignments%ISOPEN) THEN
1344 CLOSE c_instr_assignments;
1345 END IF;
1346
1347 IF NOT FND_API.Compatible_API_Call (l_api_version,
1348 p_api_version,
1349 l_module,
1350 G_PKG_NAME)
1351 THEN
1352 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
1353 debug_level => FND_LOG.LEVEL_ERROR,
1354 module => G_DEBUG_MODULE || l_module);
1355 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1356 FND_MSG_PUB.Add;
1357 RAISE FND_API.G_EXC_ERROR;
1358 END IF;
1359
1360 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1361 FND_MSG_PUB.initialize;
1362 END IF;
1363 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
1364
1365 Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
1366 l_payer_level,l_payer_id,l_payer_attribs);
1367
1368 IF (l_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
1369 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
1370 ELSE
1371 l_assign_count := 0;
1372 FOR assign_rec IN c_instr_assignments(l_payer_id) LOOP
1373 l_assign_count := l_assign_count + 1;
1374
1375 x_assignments(l_assign_count).Assignment_Id :=
1376 assign_rec.instrument_payment_use_id;
1377 x_assignments(l_assign_count).Instrument.Instrument_Type :=
1378 assign_rec.instrument_type;
1379 x_assignments(l_assign_count).Instrument.Instrument_Id :=
1380 assign_rec.instrument_id;
1381 x_assignments(l_assign_count).Priority := assign_rec.order_of_preference;
1382 x_assignments(l_assign_count).Start_Date := assign_rec.start_date;
1383 x_assignments(l_assign_count).End_Date := assign_rec.end_date;
1384 END LOOP;
1385
1386 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
1387
1388 END IF;
1389
1390 iby_fndcpt_common_pub.Prepare_Result
1391 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1392
1393 EXCEPTION
1394
1395 WHEN FND_API.G_EXC_ERROR THEN
1396
1397 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
1398 debug_level => FND_LOG.LEVEL_ERROR,
1399 module => G_DEBUG_MODULE || l_module);
1400 x_return_status := FND_API.G_RET_STS_ERROR ;
1401 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1402 p_data => x_msg_data
1403 );
1404 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1405
1406 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
1407 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1408 module => G_DEBUG_MODULE || l_module);
1409 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1410 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1411 p_data => x_msg_data
1412 );
1413 WHEN OTHERS THEN
1414
1415 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
1416 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1417 module => G_DEBUG_MODULE || l_module);
1418
1419 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
1420
1421 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1422 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1423 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
1424 END IF;
1425
1426 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1427 p_data => x_msg_data
1428 );
1429 END Get_Payer_Instr_Assignments;
1430
1431
1432 PROCEDURE Get_Payer_All_Instruments
1433 (
1434 p_api_version IN NUMBER,
1435 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1436 x_return_status OUT NOCOPY VARCHAR2,
1437 x_msg_count OUT NOCOPY NUMBER,
1438 x_msg_data OUT NOCOPY VARCHAR2,
1439 p_party_id IN NUMBER,
1440 x_instruments OUT NOCOPY PmtInstrument_tbl_type,
1441 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
1442 )
1443 IS
1444 l_api_version CONSTANT NUMBER := 1.0;
1445 l_module CONSTANT VARCHAR2(30) := 'Get_Payer_All_Assignments';
1446 l_prev_msg_count NUMBER;
1447
1448 l_instr_count NUMBER := 0;
1449
1450 CURSOR c_instr_assignments
1451 (ci_party_id IN iby_external_payers_all.party_id%TYPE)
1452 IS
1453 SELECT DISTINCT u.instrument_type, u.instrument_id
1454 FROM iby_pmt_instr_uses_all u, iby_external_payers_all p
1455 WHERE (u.payment_flow = G_PMT_FLOW_FNDCPT)
1456 AND (u.ext_pmt_party_id = p.ext_payer_id)
1457 AND (p.party_id = ci_party_id);
1458
1459 BEGIN
1460
1461 IF (c_instr_assignments%ISOPEN) THEN
1462 CLOSE c_instr_assignments;
1463 END IF;
1464
1465 IF NOT FND_API.Compatible_API_Call (l_api_version,
1466 p_api_version,
1467 l_module,
1468 G_PKG_NAME)
1469 THEN
1470 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
1471 debug_level => FND_LOG.LEVEL_ERROR,
1472 module => G_DEBUG_MODULE || l_module);
1473 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1474 FND_MSG_PUB.Add;
1475 RAISE FND_API.G_EXC_ERROR;
1476 END IF;
1477
1478 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1479 FND_MSG_PUB.initialize;
1480 END IF;
1481 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
1482
1483 l_instr_count := 0;
1484 FOR assign_rec IN c_instr_assignments(p_party_id) LOOP
1485 l_instr_count := l_instr_count + 1;
1486
1487 x_instruments(l_instr_count).Instrument_Type :=
1488 assign_rec.instrument_type;
1489 x_instruments(l_instr_count).Instrument_Id :=
1490 assign_rec.instrument_id;
1491 END LOOP;
1492
1493 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
1494
1495 iby_fndcpt_common_pub.Prepare_Result
1496 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1497
1498 EXCEPTION
1499
1500 WHEN FND_API.G_EXC_ERROR THEN
1501
1502 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
1503 debug_level => FND_LOG.LEVEL_ERROR,
1504 module => G_DEBUG_MODULE || l_module);
1505 x_return_status := FND_API.G_RET_STS_ERROR ;
1506 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1507 p_data => x_msg_data
1508 );
1509 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1510
1511 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
1512 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1513 module => G_DEBUG_MODULE || l_module);
1514 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1515 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1516 p_data => x_msg_data
1517 );
1518 WHEN OTHERS THEN
1519
1520 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
1521 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1522 module => G_DEBUG_MODULE || l_module);
1523
1524 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
1525
1526 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1527 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1528 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
1529 END IF;
1530
1531 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1532 p_data => x_msg_data
1533 );
1534
1535 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
1536 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1537 module => G_DEBUG_MODULE || l_module);
1538 iby_debug_pub.add(debug_msg => 'Exit Exception',
1539 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1540 module => G_DEBUG_MODULE || l_module);
1541 END Get_Payer_All_Instruments;
1542
1543
1544 PROCEDURE Get_Trxn_Appl_Instr_Assign
1545 (
1546 p_api_version IN NUMBER,
1547 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1548 x_return_status OUT NOCOPY VARCHAR2,
1549 x_msg_count OUT NOCOPY NUMBER,
1550 x_msg_data OUT NOCOPY VARCHAR2,
1551 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
1552 p_payer_equivalency IN VARCHAR2 :=
1553 IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
1554 p_conditions IN IBY_FNDCPT_COMMON_PUB.TrxnContext_rec_type,
1555 p_result_limit IN IBY_FNDCPT_COMMON_PUB.ResultLimit_rec_type,
1556 x_assignments OUT NOCOPY PmtInstrAssignment_tbl_type,
1557 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
1558 )
1559 IS
1560 l_api_version CONSTANT NUMBER := 1.0;
1561 l_module CONSTANT VARCHAR2(30) := 'Get_Trxn_Appl_Instr_Assign';
1562 l_prev_msg_count NUMBER;
1563
1564 l_payer_level VARCHAR2(30);
1565 l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
1566 l_payer_attribs PayerAttributes_rec_type;
1567
1568 l_assign_count NUMBER;
1569
1570 CURSOR c_instr_assigns
1571 (ci_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
1572 ci_payer_level IN VARCHAR2,
1573 ci_payer_equiv IN VARCHAR2,
1574 ci_instrument_type IN VARCHAR2)
1575 IS
1576 SELECT instrument_payment_use_id, instrument_type, instrument_id,
1577 order_of_preference, start_date, end_date
1578 FROM (SELECT instrument_payment_use_id, instrument_type, instrument_id,
1579 order_of_preference, start_date, end_date,
1580 rank() over (partition by instrument_type, instrument_id
1581 order by order_of_preference, instrument_payment_use_id) dup_rank
1582 FROM iby_pmt_instr_uses_all
1583 WHERE (payment_flow = G_PMT_FLOW_FNDCPT)
1584 AND instrument_type = NVL(ci_instrument_type,instrument_type)
1585 AND sysdate >= start_date
1586 AND sysdate < NVL(end_date, sysdate+1)
1587 AND ext_pmt_party_id IN
1588 (SELECT ext_payer_id
1589 FROM iby_external_payers_all
1590 WHERE (payment_function = ci_payer.Payment_Function)
1591 AND (party_id = ci_payer.Party_Id)
1592 AND (IBY_FNDCPT_COMMON_PUB.Compare_Payer
1593 (ci_payer.org_type, ci_payer.org_id,
1594 ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
1595 ci_payer_level,ci_payer_equiv,org_type,org_id,
1596 cust_account_id,acct_site_use_id) = 'T')
1597 )) x
1598 WHERE x.dup_rank = 1
1599 ORDER BY order_of_preference;
1600 BEGIN
1601
1602 IF (c_instr_assigns%ISOPEN) THEN
1603 CLOSE c_instr_assigns;
1604 END IF;
1605
1606 IF NOT FND_API.Compatible_API_Call (l_api_version,
1607 p_api_version,
1608 l_module,
1609 G_PKG_NAME)
1610 THEN
1611 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
1612 debug_level => FND_LOG.LEVEL_ERROR,
1613 module => G_DEBUG_MODULE || l_module);
1614 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1615 FND_MSG_PUB.Add;
1616 RAISE FND_API.G_EXC_ERROR;
1617 END IF;
1618
1619 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1620 FND_MSG_PUB.initialize;
1621 END IF;
1622 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
1623
1624 Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
1625 l_payer_level,l_payer_id,l_payer_attribs);
1626
1627 IF (l_payer_level = IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER) THEN
1628 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_INVALID_PAYER;
1629 ELSE
1630 l_assign_count := 1;
1631 FOR assign_rec IN c_instr_assigns(p_payer,l_payer_level,p_payer_equivalency,p_conditions.payment_instrtype)
1632 LOOP
1633 x_assignments(l_assign_count).Assignment_Id := assign_rec.instrument_payment_use_id;
1634 x_assignments(l_assign_count).Instrument.Instrument_Type := assign_rec.instrument_type;
1635 x_assignments(l_assign_count).Instrument.Instrument_Id := assign_rec.instrument_id;
1636 x_assignments(l_assign_count).Priority := assign_rec.order_of_preference;
1637 x_assignments(l_assign_count).Start_Date := assign_rec.start_date;
1638 x_assignments(l_assign_count).End_Date := assign_rec.end_date;
1639 l_assign_count := l_assign_count + 1;
1640
1641 EXIT WHEN p_result_limit.default_flag='Y';
1642 END LOOP;
1643
1644 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
1645 END IF;
1646
1647 iby_fndcpt_common_pub.Prepare_Result
1648 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1649
1650 EXCEPTION
1651
1652 WHEN FND_API.G_EXC_ERROR THEN
1653
1654 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
1655 debug_level => FND_LOG.LEVEL_ERROR,
1656 module => G_DEBUG_MODULE || l_module);
1657 x_return_status := FND_API.G_RET_STS_ERROR ;
1658 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1659 p_data => x_msg_data
1660 );
1661 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1662
1663 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
1664 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1665 module => G_DEBUG_MODULE || l_module);
1666 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1667 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
1668 p_data => x_msg_data
1669 );
1670 WHEN OTHERS THEN
1671
1672 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
1673 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1674 module => G_DEBUG_MODULE || l_module);
1675
1676 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
1677
1678 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1679 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1680 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
1681 END IF;
1682
1683 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
1684 p_data => x_msg_data
1685 );
1686
1687 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
1688 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1689 module => G_DEBUG_MODULE || l_module);
1690 iby_debug_pub.add(debug_msg => 'Exit Exception',
1691 debug_level => FND_LOG.LEVEL_UNEXPECTED,
1692 module => G_DEBUG_MODULE || l_module);
1693
1694 END Get_Trxn_Appl_Instr_Assign;
1695
1696 PROCEDURE Create_Card_Wrapper
1697 (p_commit IN VARCHAR2,
1698 p_owner_id IN iby_creditcard.card_owner_id%TYPE,
1699 p_holder_name IN iby_creditcard.chname%TYPE,
1700 p_billing_address_id IN iby_creditcard.addressid%TYPE,
1701 p_address_type IN VARCHAR2,
1702 p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
1703 p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
1704 p_card_number IN iby_creditcard.ccnumber%TYPE,
1705 p_expiry_date IN iby_creditcard.expirydate%TYPE,
1706 p_instr_type IN iby_creditcard.instrument_type%TYPE,
1707 p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
1708 p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
1709 p_issuer IN iby_creditcard.card_issuer_code%TYPE,
1710 p_fi_name IN iby_creditcard.finame%TYPE,
1711 p_single_use IN iby_creditcard.single_use_flag%TYPE,
1712 p_info_only IN iby_creditcard.information_only_flag%TYPE,
1713 p_purpose IN iby_creditcard.card_purpose%TYPE,
1714 p_desc IN iby_creditcard.description%TYPE,
1715 p_active_flag IN iby_creditcard.active_flag%TYPE,
1716 p_inactive_date IN iby_creditcard.inactive_date%TYPE,
1717 p_sys_sec_key IN iby_security_pkg.DES3_KEY_TYPE,
1718 p_attribute_category IN iby_creditcard.attribute_category%TYPE,
1719 p_attribute1 IN iby_creditcard.attribute1%TYPE,
1720 p_attribute2 IN iby_creditcard.attribute2%TYPE,
1721 p_attribute3 IN iby_creditcard.attribute3%TYPE,
1722 p_attribute4 IN iby_creditcard.attribute4%TYPE,
1723 p_attribute5 IN iby_creditcard.attribute5%TYPE,
1724 p_attribute6 IN iby_creditcard.attribute6%TYPE,
1725 p_attribute7 IN iby_creditcard.attribute7%TYPE,
1726 p_attribute8 IN iby_creditcard.attribute8%TYPE,
1727 p_attribute9 IN iby_creditcard.attribute9%TYPE,
1728 p_attribute10 IN iby_creditcard.attribute10%TYPE,
1729 p_attribute11 IN iby_creditcard.attribute11%TYPE,
1730 p_attribute12 IN iby_creditcard.attribute12%TYPE,
1731 p_attribute13 IN iby_creditcard.attribute13%TYPE,
1732 p_attribute14 IN iby_creditcard.attribute14%TYPE,
1733 p_attribute15 IN iby_creditcard.attribute15%TYPE,
1734 p_attribute16 IN iby_creditcard.attribute16%TYPE,
1735 p_attribute17 IN iby_creditcard.attribute17%TYPE,
1736 p_attribute18 IN iby_creditcard.attribute18%TYPE,
1737 p_attribute19 IN iby_creditcard.attribute19%TYPE,
1738 p_attribute20 IN iby_creditcard.attribute20%TYPE,
1739 p_attribute21 IN iby_creditcard.attribute21%TYPE,
1740 p_attribute22 IN iby_creditcard.attribute22%TYPE,
1741 p_attribute23 IN iby_creditcard.attribute23%TYPE,
1742 p_attribute24 IN iby_creditcard.attribute24%TYPE,
1743 p_attribute25 IN iby_creditcard.attribute25%TYPE,
1744 p_attribute26 IN iby_creditcard.attribute26%TYPE,
1745 p_attribute27 IN iby_creditcard.attribute27%TYPE,
1746 p_attribute28 IN iby_creditcard.attribute28%TYPE,
1747 p_attribute29 IN iby_creditcard.attribute29%TYPE,
1748 p_attribute30 IN iby_creditcard.attribute30%TYPE,
1749 x_result_code OUT NOCOPY VARCHAR2,
1750 x_return_status OUT NOCOPY VARCHAR2,
1751 x_instr_id OUT NOCOPY iby_creditcard.instrid%TYPE
1752 )
1753 IS
1754 -- create a record type and populate it
1755 x_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1756 x_card_instrument CreditCard_rec_type;
1757 x_msg_count NUMBER;
1758 x_msg_data VARCHAR2(3000);
1759 Begin
1760 x_card_instrument.Owner_Id := p_owner_id;
1761 x_card_instrument.Card_Holder_Name := p_holder_name;
1762 x_card_instrument.Billing_Address_Id := p_billing_address_id;
1763 x_card_instrument.Address_Type := p_address_type;
1764 x_card_instrument.Billing_Postal_Code := p_billing_zip;
1765 x_card_instrument.Billing_Address_Territory := p_billing_country;
1766 x_card_instrument.Card_Number := p_card_number;
1767 x_card_instrument.Expiration_Date := p_expiry_date;
1768 x_card_instrument.Instrument_Type := p_instr_type;
1769 x_card_instrument.PurchaseCard_Flag := p_pcard_flag;
1770 x_card_instrument.PurchaseCard_SubType := p_pcard_type;
1771 x_card_instrument.FI_Name := p_fi_name;
1772 x_card_instrument.Single_Use_Flag := p_single_use;
1773 x_card_instrument.Info_Only_Flag := p_info_only;
1774 x_card_instrument.Card_Purpose := p_purpose;
1775 x_card_instrument.Card_Description := p_desc;
1776 x_card_instrument.Active_Flag := p_active_flag;
1777 x_card_instrument.Inactive_Date := p_inactive_date;
1778 x_card_instrument.card_issuer := p_issuer;
1779 x_card_instrument.attribute_category := p_attribute_category;
1780 x_card_instrument.attribute1 := p_attribute1;
1781 x_card_instrument.attribute2 := p_attribute2;
1782 x_card_instrument.attribute3 := p_attribute3;
1783 x_card_instrument.attribute4 := p_attribute4;
1784 x_card_instrument.attribute5 := p_attribute5;
1785 x_card_instrument.attribute6 := p_attribute6;
1786 x_card_instrument.attribute7 := p_attribute7;
1787 x_card_instrument.attribute8 := p_attribute8;
1788 x_card_instrument.attribute9 := p_attribute9;
1789 x_card_instrument.attribute10 := p_attribute10;
1790 x_card_instrument.attribute11 := p_attribute11;
1791 x_card_instrument.attribute12 := p_attribute12;
1792 x_card_instrument.attribute13 := p_attribute13;
1793 x_card_instrument.attribute14 := p_attribute14;
1794 x_card_instrument.attribute15 := p_attribute15;
1795 x_card_instrument.attribute16 := p_attribute16;
1796 x_card_instrument.attribute17 := p_attribute17;
1797 x_card_instrument.attribute18 := p_attribute18;
1798 x_card_instrument.attribute19 := p_attribute19;
1799 x_card_instrument.attribute20 := p_attribute20;
1800 x_card_instrument.attribute21 := p_attribute21;
1801 x_card_instrument.attribute22 := p_attribute22;
1802 x_card_instrument.attribute23 := p_attribute23;
1803 x_card_instrument.attribute24 := p_attribute24;
1804 x_card_instrument.attribute25 := p_attribute25;
1805 x_card_instrument.attribute26 := p_attribute26;
1806 x_card_instrument.attribute27 := p_attribute27;
1807 x_card_instrument.attribute28 := p_attribute28;
1808 x_card_instrument.attribute29 := p_attribute29;
1809 x_card_instrument.attribute30 := p_attribute30;
1810
1811
1812 -- call Create_Card
1813 Create_Card(1.0,
1814 FND_API.G_FALSE,
1815 p_commit,
1816 x_return_status,
1817 x_msg_count,
1818 x_msg_data,
1819 x_card_instrument,
1820 x_instr_id,
1821 x_response);
1822 -- Map things back
1823 x_result_code := x_response.Result_Code;
1824
1825 End Create_Card_Wrapper;
1826
1827
1828 PROCEDURE Create_Card
1829 (
1830 p_api_version IN NUMBER,
1831 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
1832 p_commit IN VARCHAR2 := FND_API.G_TRUE,
1833 x_return_status OUT NOCOPY VARCHAR2,
1834 x_msg_count OUT NOCOPY NUMBER,
1835 x_msg_data OUT NOCOPY VARCHAR2,
1836 p_card_instrument IN CreditCard_rec_type,
1837 x_card_id OUT NOCOPY NUMBER,
1838 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
1839 )
1840 IS
1841
1842 l_api_version CONSTANT NUMBER := 1.0;
1843 l_module CONSTANT VARCHAR2(30) := 'Create_Card';
1844 l_prev_msg_count NUMBER;
1845
1846 lx_result_code VARCHAR2(30);
1847 lx_result IBY_FNDCPT_COMMON_PUB.Result_rec_type;
1848 lx_card_rec CreditCard_rec_type;
1849
1850 l_info_only iby_creditcard.information_only_flag%TYPE := NULL;
1851 l_sec_mode iby_sys_security_options.cc_encryption_mode%TYPE;
1852 l_cc_reg IBY_INSTRREG_PUB.CreditCardInstr_rec_type;
1853 l_instr_reg IBY_INSTRREG_PUB.PmtInstr_rec_type;
1854
1855 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
1856
1857 CURSOR c_sec_mode
1858 IS
1859 SELECT cc_encryption_mode
1860 FROM iby_sys_security_options;
1861
1862 BEGIN
1863 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
1864
1865 IF NOT FND_API.Compatible_API_Call (l_api_version,
1866 p_api_version,
1867 l_module,
1868 G_PKG_NAME)
1869 THEN
1870 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
1871 debug_level => FND_LOG.LEVEL_ERROR,
1872 module => G_DEBUG_MODULE || l_module);
1873 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
1874 FND_MSG_PUB.Add;
1875 RAISE FND_API.G_EXC_ERROR;
1876 END IF;
1877
1878 IF (c_sec_mode%ISOPEN) THEN CLOSE c_sec_mode; END IF;
1879
1880 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1881 FND_MSG_PUB.initialize;
1882 END IF;
1883 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
1884
1885 --SAVEPOINT Create_Card;
1886
1887 IBY_FNDCPT_SETUP_PUB.Card_Exists
1888 (
1889 1.0,
1890 FND_API.G_FALSE,
1891 x_return_status,
1892 x_msg_count,
1893 x_msg_data,
1894 p_card_instrument.Owner_Id,
1895 p_card_instrument.Card_Number,
1896 lx_card_rec,
1897 lx_result,
1898 NVL(p_card_instrument.Instrument_Type,IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_CREDITCARD)
1899 );
1900
1901 iby_debug_pub.add('fetched card id:='||lx_card_rec.Card_Id,
1902 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1903
1904 IF (lx_card_rec.Card_Id IS NULL) THEN
1905
1906 -- validate billing address information
1907 IF (NOT Validate_CC_Billing(FND_API.G_FALSE,p_card_instrument)) THEN
1908 x_response.Result_Code := iby_creditcard_pkg.G_RC_INVALID_ADDRESS;
1909 iby_fndcpt_common_pub.Prepare_Result
1910 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
1911 RETURN;
1912 END IF;
1913
1914 OPEN c_sec_mode;
1915 FETCH c_sec_mode INTO l_sec_mode;
1916 CLOSE c_sec_mode;
1917
1918 IF (l_sec_mode = iby_security_pkg.G_ENCRYPT_MODE_INSTANT) THEN
1919
1920 iby_debug_pub.add('online registration',
1921 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1922
1923 l_cc_reg.FIName := p_card_instrument.FI_Name;
1924 l_cc_reg.CC_Type := p_card_instrument.Card_Issuer;
1925 l_cc_reg.CC_Num := p_card_instrument.Card_Number;
1926 l_cc_reg.CC_ExpDate := p_card_instrument.Expiration_Date;
1927 l_cc_reg.Instrument_Type := NVL(p_card_instrument.Instrument_Type,IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_CREDITCARD);
1928 l_cc_reg.Owner_Id := p_card_instrument.Owner_Id;
1929 l_cc_reg.CC_HolderName := p_card_instrument.Card_Holder_Name;
1930 l_cc_reg.CC_Desc := p_card_instrument.Card_Description;
1931 l_cc_reg.Billing_Address_Id := p_card_instrument.Billing_Address_Id;
1932 l_cc_reg.Billing_PostalCode := p_card_instrument.Billing_Postal_Code;
1933 l_cc_reg.Billing_Country := p_card_instrument.Billing_Address_Territory;
1934 l_cc_reg.Single_Use_Flag := p_card_instrument.Single_Use_Flag;
1935 l_cc_reg.Info_Only_Flag := p_card_instrument.Info_Only_Flag;
1936 l_cc_reg.Card_Purpose := p_card_instrument.Card_Purpose;
1937 l_cc_reg.CC_Desc := p_card_instrument.Card_Description;
1938 l_cc_reg.Active_Flag := p_card_instrument.Active_Flag;
1939 l_cc_reg.Inactive_Date := p_card_instrument.Inactive_Date;
1940
1941 l_instr_reg.CreditCardInstr := l_cc_reg;
1942 l_instr_reg.InstrumentType := IBY_INSTRREG_PUB.C_INSTRTYPE_CREDITCARD;
1943
1944 IBY_INSTRREG_PUB.OraInstrAdd
1945 (1.0, FND_API.G_FALSE, FND_API.G_FALSE, FND_API.G_VALID_LEVEL_FULL,
1946 l_instr_reg, x_return_status, x_msg_count, x_msg_data,
1947 x_card_id, lx_result
1948 );
1949
1950 -- should not be a validation error at this point
1951 IF ((NVL(x_card_id,-1)<0))
1952 --OR (x_return_status <> FND_API.G_RET_STS_ERROR))
1953 THEN
1954 iby_debug_pub.add('instrument reg failed',
1955 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1956 iby_debug_pub.add('result code:=' || lx_result.Result_Code,
1957 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1958 IF (lx_result.Result_Code IS NULL) THEN
1959 x_response.Result_Code := 'COMMUNICATION_ERROR';
1960 --IBY_FNDCPT_COMMON_PUB.G_RC_GENERIC_SYS_ERROR;
1961 iby_fndcpt_common_pub.Prepare_Result
1962 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,
1963 x_response);
1964 ELSE
1965 x_response.Result_Code := lx_result.Result_Code;
1966
1967 iby_fndcpt_common_pub.Prepare_Result
1968 (IBY_INSTRREG_PUB.G_INTERFACE_CODE,lx_result.Result_Message,
1969 l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,
1970 x_response);
1971 END IF;
1972 RETURN;
1973 END IF;
1974 ELSE
1975 iby_debug_pub.add('database registration',
1976 iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
1977
1978 iby_creditcard_pkg.Create_Card
1979 (FND_API.G_FALSE,
1980 p_card_instrument.Owner_Id, p_card_instrument.Card_Holder_Name,
1981 p_card_instrument.Billing_Address_Id,
1982 p_card_instrument.Address_Type,
1983 p_card_instrument.Billing_Postal_Code,
1984 p_card_instrument.Billing_Address_Territory,
1985 p_card_instrument.Card_Number, p_card_instrument.Expiration_Date,
1986 NVL(p_card_instrument.Instrument_Type,IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_CREDITCARD),
1987 p_card_instrument.PurchaseCard_Flag,
1988 p_card_instrument.PurchaseCard_SubType, p_card_instrument.Card_Issuer,
1989 p_card_instrument.FI_Name, p_card_instrument.Single_Use_Flag,
1990 p_card_instrument.Info_Only_Flag, p_card_instrument.Card_Purpose,
1991 p_card_instrument.Card_Description, p_card_instrument.Active_Flag,
1992 p_card_instrument.Inactive_Date, NULL,
1993 p_card_instrument.attribute_category,
1994 p_card_instrument.attribute1,
1995 p_card_instrument.attribute2,
1996 p_card_instrument.attribute3,
1997 p_card_instrument.attribute4,
1998 p_card_instrument.attribute5,
1999 p_card_instrument.attribute6,
2000 p_card_instrument.attribute7,
2001 p_card_instrument.attribute8,
2002 p_card_instrument.attribute9,
2003 p_card_instrument.attribute10,
2004 p_card_instrument.attribute11,
2005 p_card_instrument.attribute12,
2006 p_card_instrument.attribute13,
2007 p_card_instrument.attribute14,
2008 p_card_instrument.attribute15,
2009 p_card_instrument.attribute16,
2010 p_card_instrument.attribute17,
2011 p_card_instrument.attribute18,
2012 p_card_instrument.attribute19,
2013 p_card_instrument.attribute20,
2014 p_card_instrument.attribute21,
2015 p_card_instrument.attribute22,
2016 p_card_instrument.attribute23,
2017 p_card_instrument.attribute24,
2018 p_card_instrument.attribute25,
2019 p_card_instrument.attribute26,
2020 p_card_instrument.attribute27,
2021 p_card_instrument.attribute28,
2022 p_card_instrument.attribute29,
2023 p_card_instrument.attribute30,
2024 lx_result_code, x_card_id
2025 );
2026 END IF;
2027
2028 ELSE
2029
2030 -- card cannot become info only once this flag is turned off
2031 IF (NOT p_card_instrument.Info_Only_Flag = 'Y') THEN
2032 l_info_only := p_card_instrument.Info_Only_Flag;
2033 END IF;
2034
2035 -- validate billing address information
2036 IF (NOT Validate_CC_Billing(FND_API.G_TRUE,p_card_instrument)) THEN
2037 x_response.Result_Code := iby_creditcard_pkg.G_RC_INVALID_ADDRESS;
2038 iby_fndcpt_common_pub.Prepare_Result
2039 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2040 RETURN;
2041 END IF;
2042 -- validate expiration date
2043 IF (TRUNC(p_card_instrument.Expiration_Date,'DD') < TRUNC(SYSDATE,'DD'))
2044 THEN
2045 x_response.Result_Code := iby_creditcard_pkg.G_RC_INVALID_CCEXPIRY;
2046 iby_fndcpt_common_pub.Prepare_Result
2047 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2048 RETURN;
2049 END IF;
2050
2051 iby_creditcard_pkg.Update_Card
2052 (FND_API.G_FALSE, lx_card_rec.Card_Id, p_card_instrument.Owner_Id,
2053 p_card_instrument.Card_Holder_Name,
2054 p_card_instrument.Billing_Address_Id,
2055 p_card_instrument.Address_Type,
2056 p_card_instrument.Billing_Postal_Code,
2057 p_card_instrument.Billing_Address_Territory,
2058 p_card_instrument.Expiration_Date, p_card_instrument.Instrument_Type,
2059 p_card_instrument.PurchaseCard_Flag, p_card_instrument.PurchaseCard_SubType,
2060 p_card_instrument.FI_Name, p_card_instrument.Single_Use_Flag,
2061 l_info_only, p_card_instrument.Card_Purpose,
2062 p_card_instrument.Card_Description, p_card_instrument.Active_Flag,
2063 NVL(p_card_instrument.Inactive_Date,FND_API.G_MISS_DATE),
2064 p_card_instrument.attribute_category,
2065 p_card_instrument.attribute1, p_card_instrument.attribute2,
2066 p_card_instrument.attribute3, p_card_instrument.attribute4,
2067 p_card_instrument.attribute5, p_card_instrument.attribute6,
2068 p_card_instrument.attribute7, p_card_instrument.attribute8,
2069 p_card_instrument.attribute9, p_card_instrument.attribute10,
2070 p_card_instrument.attribute11, p_card_instrument.attribute12,
2071 p_card_instrument.attribute13, p_card_instrument.attribute14,
2072 p_card_instrument.attribute15, p_card_instrument.attribute16,
2073 p_card_instrument.attribute17, p_card_instrument.attribute18,
2074 p_card_instrument.attribute19, p_card_instrument.attribute20,
2075 p_card_instrument.attribute21, p_card_instrument.attribute22,
2076 p_card_instrument.attribute23, p_card_instrument.attribute24,
2077 p_card_instrument.attribute25, p_card_instrument.attribute26,
2078 p_card_instrument.attribute27, p_card_instrument.attribute28,
2079 p_card_instrument.attribute29, p_card_instrument.attribute30,
2080 lx_result_code);
2081 x_card_id := lx_card_rec.Card_Id;
2082 END IF;
2083
2084 x_response.Result_Code := NVL(lx_result_code,IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS);
2085 iby_fndcpt_common_pub.Prepare_Result
2086 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2087
2088 IF FND_API.To_Boolean(p_commit) THEN
2089 COMMIT;
2090 END IF;
2091
2092 EXCEPTION
2093
2094 WHEN FND_API.G_EXC_ERROR THEN
2095 ROLLBACK TO Create_Card;
2096 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
2097 debug_level => FND_LOG.LEVEL_ERROR,
2098 module => G_DEBUG_MODULE || l_module);
2099 x_return_status := FND_API.G_RET_STS_ERROR ;
2100 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2101 p_data => x_msg_data
2102 );
2103 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2104 --ROLLBACK TO Create_Card;
2105 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
2106 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2107 module => G_DEBUG_MODULE || l_module);
2108 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2109 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2110 p_data => x_msg_data
2111 );
2112 WHEN OTHERS THEN
2113 --ROLLBACK TO Create_Card;
2114 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
2115 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2116 module => G_DEBUG_MODULE || l_module);
2117
2118 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
2119
2120 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2121 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2122 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
2123 END IF;
2124
2125 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2126 p_data => x_msg_data
2127 );
2128
2129 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
2130 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2131 module => G_DEBUG_MODULE || l_module);
2132 iby_debug_pub.add(debug_msg => 'Exit Exception',
2133 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2134 module => G_DEBUG_MODULE || l_module);
2135
2136 END Create_Card;
2137
2138 PROCEDURE Update_Card_Wrapper
2139 (
2140 p_commit IN VARCHAR2,
2141 p_instr_id IN iby_creditcard.instrid%TYPE,
2142 p_owner_id IN iby_creditcard.card_owner_id%TYPE,
2143 p_holder_name IN iby_creditcard.chname%TYPE,
2144 p_billing_address_id IN iby_creditcard.addressid%TYPE,
2145 p_address_type IN VARCHAR2,
2146 p_billing_zip IN iby_creditcard.billing_addr_postal_code%TYPE,
2147 p_billing_country IN iby_creditcard.bill_addr_territory_code%TYPE,
2148 p_expiry_date IN iby_creditcard.expirydate%TYPE,
2149 p_instr_type IN iby_creditcard.instrument_type%TYPE,
2150 p_pcard_flag IN iby_creditcard.purchasecard_flag%TYPE,
2151 p_pcard_type IN iby_creditcard.purchasecard_subtype%TYPE,
2152 p_fi_name IN iby_creditcard.finame%TYPE,
2153 p_single_use IN iby_creditcard.single_use_flag%TYPE,
2154 p_info_only IN iby_creditcard.information_only_flag%TYPE,
2155 p_purpose IN iby_creditcard.card_purpose%TYPE,
2156 p_desc IN iby_creditcard.description%TYPE,
2157 p_active_flag IN iby_creditcard.active_flag%TYPE,
2158 p_inactive_date IN iby_creditcard.inactive_date%TYPE,
2159 p_attribute_category IN iby_creditcard.attribute_category%TYPE,
2160 p_attribute1 IN iby_creditcard.attribute1%TYPE,
2161 p_attribute2 IN iby_creditcard.attribute2%TYPE,
2162 p_attribute3 IN iby_creditcard.attribute3%TYPE,
2163 p_attribute4 IN iby_creditcard.attribute4%TYPE,
2164 p_attribute5 IN iby_creditcard.attribute5%TYPE,
2165 p_attribute6 IN iby_creditcard.attribute6%TYPE,
2166 p_attribute7 IN iby_creditcard.attribute7%TYPE,
2167 p_attribute8 IN iby_creditcard.attribute8%TYPE,
2168 p_attribute9 IN iby_creditcard.attribute9%TYPE,
2169 p_attribute10 IN iby_creditcard.attribute10%TYPE,
2170 p_attribute11 IN iby_creditcard.attribute11%TYPE,
2171 p_attribute12 IN iby_creditcard.attribute12%TYPE,
2172 p_attribute13 IN iby_creditcard.attribute13%TYPE,
2173 p_attribute14 IN iby_creditcard.attribute14%TYPE,
2174 p_attribute15 IN iby_creditcard.attribute15%TYPE,
2175 p_attribute16 IN iby_creditcard.attribute16%TYPE,
2176 p_attribute17 IN iby_creditcard.attribute17%TYPE,
2177 p_attribute18 IN iby_creditcard.attribute18%TYPE,
2178 p_attribute19 IN iby_creditcard.attribute19%TYPE,
2179 p_attribute20 IN iby_creditcard.attribute20%TYPE,
2180 p_attribute21 IN iby_creditcard.attribute21%TYPE,
2181 p_attribute22 IN iby_creditcard.attribute22%TYPE,
2182 p_attribute23 IN iby_creditcard.attribute23%TYPE,
2183 p_attribute24 IN iby_creditcard.attribute24%TYPE,
2184 p_attribute25 IN iby_creditcard.attribute25%TYPE,
2185 p_attribute26 IN iby_creditcard.attribute26%TYPE,
2186 p_attribute27 IN iby_creditcard.attribute27%TYPE,
2187 p_attribute28 IN iby_creditcard.attribute28%TYPE,
2188 p_attribute29 IN iby_creditcard.attribute29%TYPE,
2189 p_attribute30 IN iby_creditcard.attribute30%TYPE,
2190 x_result_code OUT NOCOPY VARCHAR2,
2191 x_return_status OUT NOCOPY VARCHAR2
2192 )
2193 IS
2194 -- create a record type and populate it
2195 x_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
2196 x_card_instrument CreditCard_rec_type;
2197 x_msg_count NUMBER;
2198 x_msg_data VARCHAR2(3000);
2199 Begin
2200 x_card_instrument.Card_Id := p_instr_id;
2201 x_card_instrument.Owner_Id := p_owner_id;
2202 x_card_instrument.Card_Holder_Name := p_holder_name;
2203 x_card_instrument.Billing_Address_Id := p_billing_address_id;
2204 x_card_instrument.Address_Type := p_address_type;
2205 x_card_instrument.Billing_Postal_Code := p_billing_zip;
2206 x_card_instrument.Billing_Address_Territory := p_billing_country;
2207 x_card_instrument.Expiration_Date := p_expiry_date;
2208 x_card_instrument.Instrument_Type := p_instr_type;
2209 x_card_instrument.PurchaseCard_Flag := p_pcard_flag;
2210 x_card_instrument.PurchaseCard_SubType := p_pcard_type;
2211 x_card_instrument.FI_Name := p_fi_name;
2212 x_card_instrument.Single_Use_Flag := p_single_use;
2213 x_card_instrument.Info_Only_Flag := p_info_only;
2214 x_card_instrument.Card_Purpose := p_purpose;
2215 x_card_instrument.Card_Description := p_desc;
2216 x_card_instrument.Active_Flag := p_active_flag;
2217 x_card_instrument.Inactive_Date := p_inactive_date;
2218 x_card_instrument.attribute_category := p_attribute_category;
2219 x_card_instrument.attribute1 := p_attribute1;
2220 x_card_instrument.attribute2 := p_attribute2;
2221 x_card_instrument.attribute3 := p_attribute3;
2222 x_card_instrument.attribute4 := p_attribute4;
2223 x_card_instrument.attribute5 := p_attribute5;
2224 x_card_instrument.attribute6 := p_attribute6;
2225 x_card_instrument.attribute7 := p_attribute7;
2226 x_card_instrument.attribute8 := p_attribute8;
2227 x_card_instrument.attribute9 := p_attribute9;
2228 x_card_instrument.attribute10 := p_attribute10;
2229 x_card_instrument.attribute11 := p_attribute11;
2230 x_card_instrument.attribute12 := p_attribute12;
2231 x_card_instrument.attribute13 := p_attribute13;
2232 x_card_instrument.attribute14 := p_attribute14;
2233 x_card_instrument.attribute15 := p_attribute15;
2234 x_card_instrument.attribute16 := p_attribute16;
2235 x_card_instrument.attribute17 := p_attribute17;
2236 x_card_instrument.attribute18 := p_attribute18;
2237 x_card_instrument.attribute19 := p_attribute19;
2238 x_card_instrument.attribute20 := p_attribute20;
2239 x_card_instrument.attribute21 := p_attribute21;
2240 x_card_instrument.attribute22 := p_attribute22;
2241 x_card_instrument.attribute23 := p_attribute23;
2242 x_card_instrument.attribute24 := p_attribute24;
2243 x_card_instrument.attribute25 := p_attribute25;
2244 x_card_instrument.attribute26 := p_attribute26;
2245 x_card_instrument.attribute27 := p_attribute27;
2246 x_card_instrument.attribute28 := p_attribute28;
2247 x_card_instrument.attribute29 := p_attribute29;
2248 x_card_instrument.attribute30 := p_attribute30;
2249
2250
2251 -- call Update_Card
2252 Update_Card(1.0,
2253 FND_API.G_FALSE,
2254 p_commit,
2255 x_return_status,
2256 x_msg_count,
2257 x_msg_data,
2258 x_card_instrument,
2259 x_response);
2260 -- Map things back
2261 x_result_code := x_response.Result_Code;
2262
2263 END Update_Card_Wrapper;
2264
2265 PROCEDURE Update_Card
2266 (
2267 p_api_version IN NUMBER,
2268 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2269 p_commit IN VARCHAR2 := FND_API.G_TRUE,
2270 x_return_status OUT NOCOPY VARCHAR2,
2271 x_msg_count OUT NOCOPY NUMBER,
2272 x_msg_data OUT NOCOPY VARCHAR2,
2273 p_card_instrument IN CreditCard_rec_type,
2274 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
2275 )
2276 IS
2277
2278 l_api_version CONSTANT NUMBER := 1.0;
2279 l_module CONSTANT VARCHAR2(30) := 'Update_Card';
2280 l_prev_msg_count NUMBER;
2281
2282 lx_result_code VARCHAR2(30);
2283
2284 l_info_only iby_creditcard.information_only_flag%TYPE := NULL;
2285
2286 BEGIN
2287 IF NOT FND_API.Compatible_API_Call (l_api_version,
2288 p_api_version,
2289 l_module,
2290 G_PKG_NAME)
2291 THEN
2292 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
2293 debug_level => FND_LOG.LEVEL_ERROR,
2294 module => G_DEBUG_MODULE || l_module);
2295 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
2296 FND_MSG_PUB.Add;
2297 RAISE FND_API.G_EXC_ERROR;
2298 END IF;
2299
2300 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2301 FND_MSG_PUB.initialize;
2302 END IF;
2303 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
2304
2305 SAVEPOINT Update_Card;
2306
2307 -- card cannot become info only once this flag is turned off
2308 IF (NOT p_card_instrument.Info_Only_Flag = 'Y') THEN
2309 l_info_only := p_card_instrument.Info_Only_Flag;
2310 END IF;
2311 -- validate billing address information
2312 IF (NOT Validate_CC_Billing(FND_API.G_TRUE,p_card_instrument)) THEN
2313 x_response.Result_Code := iby_creditcard_pkg.G_RC_INVALID_ADDRESS;
2314 iby_fndcpt_common_pub.Prepare_Result
2315 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2316 RETURN;
2317 END IF;
2318
2319 iby_creditcard_pkg.Update_Card
2320 (FND_API.G_FALSE, p_card_instrument.Card_Id, p_card_instrument.Owner_Id,
2321 p_card_instrument.Card_Holder_Name,
2322 p_card_instrument.Billing_Address_Id,
2323 p_card_instrument.Address_Type,
2324 p_card_instrument.Billing_Postal_Code,
2325 p_card_instrument.Billing_Address_Territory,
2326 p_card_instrument.Expiration_Date, p_card_instrument.Instrument_Type,
2327 p_card_instrument.PurchaseCard_Flag, p_card_instrument.PurchaseCard_SubType,
2328 p_card_instrument.FI_Name, p_card_instrument.Single_Use_Flag,
2329 l_info_only, p_card_instrument.Card_Purpose,
2330 p_card_instrument.Card_Description, p_card_instrument.Active_Flag,
2331 p_card_instrument.Inactive_Date,
2332 p_card_instrument.attribute_category,
2333 p_card_instrument.attribute1, p_card_instrument.attribute2,
2334 p_card_instrument.attribute3, p_card_instrument.attribute4,
2335 p_card_instrument.attribute5, p_card_instrument.attribute6,
2336 p_card_instrument.attribute7, p_card_instrument.attribute8,
2337 p_card_instrument.attribute9, p_card_instrument.attribute10,
2338 p_card_instrument.attribute11, p_card_instrument.attribute12,
2339 p_card_instrument.attribute13, p_card_instrument.attribute14,
2340 p_card_instrument.attribute15, p_card_instrument.attribute16,
2341 p_card_instrument.attribute17, p_card_instrument.attribute18,
2342 p_card_instrument.attribute19, p_card_instrument.attribute20,
2343 p_card_instrument.attribute21, p_card_instrument.attribute22,
2344 p_card_instrument.attribute23, p_card_instrument.attribute24,
2345 p_card_instrument.attribute25, p_card_instrument.attribute26,
2346 p_card_instrument.attribute27, p_card_instrument.attribute28,
2347 p_card_instrument.attribute29, p_card_instrument.attribute30,
2348 lx_result_code);
2349
2350
2351 x_response.Result_Code :=
2352 NVL(lx_result_code,IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS);
2353 iby_fndcpt_common_pub.Prepare_Result
2354 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2355
2356 IF FND_API.To_Boolean(p_commit) THEN
2357 COMMIT;
2358 END IF;
2359
2360 EXCEPTION
2361
2362 WHEN FND_API.G_EXC_ERROR THEN
2363 ROLLBACK TO Update_Card;
2364 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
2365 debug_level => FND_LOG.LEVEL_ERROR,
2366 module => G_DEBUG_MODULE || l_module);
2367 x_return_status := FND_API.G_RET_STS_ERROR ;
2368 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2369 p_data => x_msg_data
2370 );
2371 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2372 ROLLBACK TO Update_Card;
2373 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
2374 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2375 module => G_DEBUG_MODULE || l_module);
2376 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2377 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2378 p_data => x_msg_data
2379 );
2380 WHEN OTHERS THEN
2381 ROLLBACK TO Update_Card;
2382 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
2383 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2384 module => G_DEBUG_MODULE || l_module);
2385
2386 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
2387
2388 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2389 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2390 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
2391 END IF;
2392
2393 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2394 p_data => x_msg_data
2395 );
2396
2397 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
2398 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2399 module => G_DEBUG_MODULE || l_module);
2400 iby_debug_pub.add(debug_msg => 'Exit Exception',
2401 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2402 module => G_DEBUG_MODULE || l_module);
2403
2404 END Update_Card;
2405
2406 PROCEDURE Get_Card
2407 (
2408 p_api_version IN NUMBER,
2409 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2410 x_return_status OUT NOCOPY VARCHAR2,
2411 x_msg_count OUT NOCOPY NUMBER,
2412 x_msg_data OUT NOCOPY VARCHAR2,
2413 p_card_id NUMBER,
2414 x_card_instrument OUT NOCOPY CreditCard_rec_type,
2415 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
2416 )
2417 IS
2418 l_api_version CONSTANT NUMBER := 1.0;
2419 l_module CONSTANT VARCHAR2(30) := 'Get_Card';
2420 l_prev_msg_count NUMBER;
2421
2422 l_card_count NUMBER;
2423
2424 CURSOR c_card(ci_card_id IN iby_creditcard.instrid%TYPE)
2425 IS
2426 SELECT card_owner_id, chname, addressid, masked_cc_number, expirydate,
2427 instrument_type,
2428 purchasecard_subtype, card_issuer_code, finame, single_use_flag,
2429 information_only_flag, card_purpose, description, inactive_date
2430 FROM iby_creditcard
2431 WHERE (instrid = ci_card_id);
2432 BEGIN
2433 IF (c_card%ISOPEN) THEN
2434 CLOSE c_card;
2435 END IF;
2436
2437 IF NOT FND_API.Compatible_API_Call (l_api_version,
2438 p_api_version,
2439 l_module,
2440 G_PKG_NAME)
2441 THEN
2442 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
2443 debug_level => FND_LOG.LEVEL_ERROR,
2444 module => G_DEBUG_MODULE || l_module);
2445 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
2446 FND_MSG_PUB.Add;
2447 RAISE FND_API.G_EXC_ERROR;
2448 END IF;
2449
2450 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2451 FND_MSG_PUB.initialize;
2452 END IF;
2453 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
2454
2455 OPEN c_card(p_card_id);
2456 FETCH c_card INTO x_card_instrument.Owner_Id, x_card_instrument.Card_Holder_Name,
2457 x_card_instrument.Billing_Address_Id, x_card_instrument.Card_Number,
2458 x_card_instrument.Expiration_Date, x_card_instrument.Instrument_Type,
2459 x_card_instrument.Purchasecard_Subtype, x_card_instrument.Card_Issuer,
2460 x_card_instrument.FI_Name, x_card_instrument.Single_Use_Flag,
2461 x_card_instrument.Info_Only_Flag, x_card_instrument.Card_Purpose,
2462 x_card_instrument.Card_Description, x_card_instrument.Inactive_Date;
2463
2464 IF (c_card%NOTFOUND) THEN
2465 x_response.Result_Code := G_RC_INVALID_INSTRUMENT;
2466 ELSE
2467 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
2468 x_card_instrument.Card_Id := p_card_id;
2469 END IF;
2470
2471 iby_fndcpt_common_pub.Prepare_Result
2472 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2473
2474 EXCEPTION
2475
2476 WHEN FND_API.G_EXC_ERROR THEN
2477
2478 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
2479 debug_level => FND_LOG.LEVEL_ERROR,
2480 module => G_DEBUG_MODULE || l_module);
2481 x_return_status := FND_API.G_RET_STS_ERROR ;
2482 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2483 p_data => x_msg_data
2484 );
2485 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2486
2487 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
2488 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2489 module => G_DEBUG_MODULE || l_module);
2490 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2491 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2492 p_data => x_msg_data
2493 );
2494 WHEN OTHERS THEN
2495
2496 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
2497 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2498 module => G_DEBUG_MODULE || l_module);
2499
2500 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
2501
2502 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2503 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2504 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
2505 END IF;
2506
2507 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2508 p_data => x_msg_data
2509 );
2510
2511 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
2512 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2513 module => G_DEBUG_MODULE || l_module);
2514 iby_debug_pub.add(debug_msg => 'Exit Exception',
2515 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2516 module => G_DEBUG_MODULE || l_module);
2517 END;
2518
2519 PROCEDURE Card_Exists
2520 (
2521 p_api_version IN NUMBER,
2522 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2523 x_return_status OUT NOCOPY VARCHAR2,
2524 x_msg_count OUT NOCOPY NUMBER,
2525 x_msg_data OUT NOCOPY VARCHAR2,
2526 p_owner_id NUMBER,
2527 p_card_number VARCHAR2,
2528 x_card_instrument OUT NOCOPY CreditCard_rec_type,
2529 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type,
2530 p_card_instr_type VARCHAR2 DEFAULT NULL
2531 )
2532 IS
2533 l_api_version CONSTANT NUMBER := 1.0;
2534 l_module CONSTANT VARCHAR2(30) := 'Card_Exists';
2535 l_prev_msg_count NUMBER;
2536
2537 l_card_id iby_creditcard.instrid%TYPE;
2538 l_cc_hash1 iby_creditcard.cc_number_hash1%TYPE;
2539 l_cc_hash2 iby_creditcard.cc_number_hash2%TYPE;
2540 l_char_allowed VARCHAR2(1) := 'N';
2541 lx_return_status VARCHAR2(1);
2542 lx_msg_count NUMBER;
2543 lx_msg_data VARCHAR2(200);
2544 lx_cc_number iby_creditcard.ccnumber%TYPE;
2545 lx_result IBY_FNDCPT_COMMON_PUB.Result_rec_type;
2546
2547 CURSOR c_card
2548 (ci_cc_hash1 IN iby_creditcard.cc_number_hash1%TYPE,
2549 ci_cc_hash2 IN iby_creditcard.cc_number_hash2%TYPE,
2550 ci_card_owner IN iby_creditcard.card_owner_id%TYPE
2551 )
2552 IS
2553 SELECT instrid
2554 FROM iby_creditcard
2555 WHERE (cc_number_hash1 = ci_cc_hash1)
2556 AND (cc_number_hash2 = ci_cc_hash2)
2557 AND ( (card_owner_id = NVL(ci_card_owner,card_owner_id))
2558 OR (card_owner_id IS NULL AND ci_card_owner IS NULL) )
2559 AND (NVL(single_use_flag,'N')='N');
2560 BEGIN
2561
2562 IF (c_card%ISOPEN) THEN
2563 CLOSE c_card;
2564 END IF;
2565
2566 IF NOT FND_API.Compatible_API_Call (l_api_version,
2567 p_api_version,
2568 l_module,
2569 G_PKG_NAME)
2570 THEN
2571 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
2572 debug_level => FND_LOG.LEVEL_ERROR,
2573 module => G_DEBUG_MODULE || l_module);
2574 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
2575 FND_MSG_PUB.Add;
2576 RAISE FND_API.G_EXC_ERROR;
2577 END IF;
2578
2579 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2580 FND_MSG_PUB.initialize;
2581 END IF;
2582 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
2583 IF (nvl(p_card_instr_type,IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_CREDITCARD ) = IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_PAYMENTCARD) THEN
2584 l_char_allowed := 'Y';
2585 END IF;
2586
2587 iby_cc_validate.StripCC
2588 (1.0, FND_API.G_FALSE, p_card_number,
2589 lx_return_status, lx_msg_count, lx_msg_data, lx_cc_number
2590 );
2591
2592 IF (lx_cc_number IS NULL) THEN
2593 x_response.Result_Code := iby_creditcard_pkg.G_RC_INVALID_CCNUMBER;
2594 iby_fndcpt_common_pub.Prepare_Result
2595 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2596 RETURN;
2597 END IF;
2598
2599 l_cc_hash1 := iby_security_pkg.get_hash(lx_cc_number,'F');
2600 l_cc_hash2 := iby_security_pkg.get_hash(lx_cc_number,'T');
2601
2602 OPEN c_card(l_cc_hash1,l_cc_hash2,p_owner_id);
2603 FETCH c_card INTO l_card_id;
2604 CLOSE c_card;
2605
2606 IF (l_card_id IS NULL) THEN
2607 x_response.Result_Code := G_RC_UNKNOWN_CARD;
2608 ELSE
2609 IBY_FNDCPT_SETUP_PUB.Get_Card
2610 (
2611 1.0,
2612 FND_API.G_FALSE,
2613 x_return_status,
2614 x_msg_count,
2615 x_msg_data,
2616 l_card_id,
2617 x_card_instrument,
2618 lx_result
2619 );
2620 x_response.Result_Code := IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS;
2621 END IF;
2622 iby_fndcpt_common_pub.Prepare_Result
2623 (l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2624
2625 EXCEPTION
2626
2627 WHEN FND_API.G_EXC_ERROR THEN
2628
2629 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
2630 debug_level => FND_LOG.LEVEL_ERROR,
2631 module => G_DEBUG_MODULE || l_module);
2632 x_return_status := FND_API.G_RET_STS_ERROR ;
2633 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2634 p_data => x_msg_data
2635 );
2636 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2637
2638 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
2639 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2640 module => G_DEBUG_MODULE || l_module);
2641 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2642 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2643 p_data => x_msg_data
2644 );
2645 WHEN OTHERS THEN
2646
2647 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
2648 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2649 module => G_DEBUG_MODULE || l_module);
2650
2651 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
2652
2653 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2654 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2655 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
2656 END IF;
2657
2658 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2659 p_data => x_msg_data
2660 );
2661
2662 iby_debug_pub.add(debug_msg => 'x_return_status=' || x_return_status,
2663 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2664 module => G_DEBUG_MODULE || l_module);
2665 iby_debug_pub.add(debug_msg => 'Exit Exception',
2666 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2667 module => G_DEBUG_MODULE || l_module);
2668 END Card_Exists;
2669
2670 PROCEDURE Process_Credit_Card
2671 (
2672 p_api_version IN NUMBER,
2673 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
2674 p_commit IN VARCHAR2 := FND_API.G_TRUE,
2675 x_return_status OUT NOCOPY VARCHAR2,
2676 x_msg_count OUT NOCOPY NUMBER,
2677 x_msg_data OUT NOCOPY VARCHAR2,
2678 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
2679 p_credit_card IN CreditCard_rec_type,
2680 p_assignment_attribs IN PmtInstrAssignment_rec_type,
2681 x_assign_id OUT NOCOPY NUMBER,
2682 x_response OUT NOCOPY IBY_FNDCPT_COMMON_PUB.Result_rec_type
2683 )
2684 IS
2685 l_api_version CONSTANT NUMBER := 1.0;
2686 l_module CONSTANT VARCHAR2(30) := 'Process_Credit_Card';
2687 l_prev_msg_count NUMBER;
2688
2689 l_existing_msgs NUMBER;
2690 lx_return_status VARCHAR2(1);
2691 lx_msg_count NUMBER;
2692 lx_msg_data VARCHAR2(2000);
2693
2694 lx_response IBY_FNDCPT_COMMON_PUB.Result_rec_type;
2695 lx_assign_attribs PmtInstrAssignment_rec_type;
2696
2697 l_dbg_mod VARCHAR2(100) := G_DEBUG_MODULE || '.' || l_module;
2698 BEGIN
2699 iby_debug_pub.add('Enter',iby_debug_pub.G_LEVEL_PROCEDURE,l_dbg_mod);
2700
2701 IF NOT FND_API.Compatible_API_Call (l_api_version,
2702 p_api_version,
2703 l_module,
2704 G_PKG_NAME)
2705 THEN
2706 iby_debug_pub.add(debug_msg => 'Incorrect API Version:=' || p_api_version,
2707 debug_level => FND_LOG.LEVEL_ERROR,
2708 module => G_DEBUG_MODULE || l_module);
2709 FND_MESSAGE.SET_NAME('IBY', 'IBY_204400_API_VER_MISMATCH');
2710 FND_MSG_PUB.Add;
2711 RAISE FND_API.G_EXC_ERROR;
2712 END IF;
2713
2714 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2715 FND_MSG_PUB.initialize;
2716 END IF;
2717 l_prev_msg_count := FND_MSG_PUB.Count_Msg;
2718
2719 l_existing_msgs := NVL(x_msg_count,0);
2720
2721 SAVEPOINT Process_Credit_Card;
2722
2723 lx_assign_attribs := p_assignment_attribs;
2724
2725 iby_debug_pub.add('create card',iby_debug_pub.G_LEVEL_INFO,l_dbg_mod);
2726
2727 Create_Card
2728 (1.0, FND_API.G_FALSE, FND_API.G_FALSE, lx_return_status, lx_msg_count,
2729 lx_msg_data, p_credit_card,
2730 lx_assign_attribs.Instrument.Instrument_Id,
2731 lx_response
2732 );
2733
2734 IF (lx_response.Result_Code <> IBY_FNDCPT_COMMON_PUB.G_RC_SUCCESS) THEN
2735 iby_debug_pub.add('rollback',iby_debug_pub.G_LEVEL_ERROR,l_dbg_mod);
2736 ROLLBACK TO Process_Credit_Card;
2737 x_response := lx_response;
2738 ELSE
2739
2740 lx_assign_attribs.Instrument.Instrument_Type :=
2741 IBY_FNDCPT_COMMON_PUB.G_INSTR_TYPE_CREDITCARD;
2742 Set_Payer_Instr_Assignment
2743 (1.0, FND_API.G_FALSE, FND_API.G_FALSE, x_return_status, x_msg_count,
2744 x_msg_data, p_payer, lx_assign_attribs, x_assign_id,
2745 x_response
2746 );
2747 END IF;
2748
2749 iby_fndcpt_common_pub.Prepare_Result
2750 (iby_fndcpt_common_pub.G_INTERFACE_CODE,x_response.Result_Message,
2751 l_prev_msg_count,x_return_status,x_msg_count,x_msg_data,x_response);
2752
2753 IF FND_API.To_Boolean(p_commit) THEN
2754 COMMIT;
2755 END IF;
2756
2757 EXCEPTION
2758
2759 WHEN FND_API.G_EXC_ERROR THEN
2760 ROLLBACK TO Process_Credit_Card;
2761 iby_debug_pub.add(debug_msg => 'In G_EXC_ERROR Exception',
2762 debug_level => FND_LOG.LEVEL_ERROR,
2763 module => G_DEBUG_MODULE || l_module);
2764 x_return_status := FND_API.G_RET_STS_ERROR ;
2765 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2766 p_data => x_msg_data
2767 );
2768 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2769 ROLLBACK TO Process_Credit_Card;
2770 iby_debug_pub.add(debug_msg => 'In G_EXC_UNEXPECTED_ERROR Exception',
2771 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2772 module => G_DEBUG_MODULE || l_module);
2773 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2774 FND_MSG_PUB.Count_And_Get ( p_count => x_msg_count,
2775 p_data => x_msg_data
2776 );
2777 WHEN OTHERS THEN
2778 ROLLBACK TO Process_Credit_Card;
2779 iby_debug_pub.add(debug_msg => 'In OTHERS Exception',
2780 debug_level => FND_LOG.LEVEL_UNEXPECTED,
2781 module => G_DEBUG_MODULE || l_module);
2782
2783 iby_fndcpt_common_pub.Clear_Msg_Stack(l_prev_msg_count);
2784
2785 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2786 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2787 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_module, SUBSTR(SQLERRM,1,100));
2788 END IF;
2789
2790 FND_MSG_PUB.Count_And_Get( p_count => x_msg_count,
2791 p_data => x_msg_data
2792 );
2793
2794 END Process_Credit_Card;
2795
2796 FUNCTION Get_Hash(p_number IN VARCHAR2, p_salt IN VARCHAR2) RETURN VARCHAR2
2797 IS
2798 lx_return_status VARCHAR2(1);
2799 lx_msg_count NUMBER;
2800 lx_msg_data VARCHAR2(200);
2801 lx_cc_number iby_creditcard.ccnumber%TYPE;
2802 BEGIN
2803 iby_cc_validate.StripCC
2804 (1.0, FND_API.G_FALSE, p_number,
2805 lx_return_status, lx_msg_count, lx_msg_data, lx_cc_number
2806 );
2807 RETURN iby_security_pkg.get_hash(lx_cc_number,p_salt);
2808 END Get_Hash;
2809
2810 FUNCTION Get_Hash(p_number IN VARCHAR2, p_salt IN VARCHAR2, p_site_salt IN VARCHAR2)
2811 RETURN VARCHAR2
2812 IS
2813 lx_return_status VARCHAR2(1);
2814 lx_msg_count NUMBER;
2815 lx_msg_data VARCHAR2(200);
2816 lx_cc_number iby_creditcard.ccnumber%TYPE;
2817 BEGIN
2818 iby_cc_validate.StripCC
2819 (1.0, FND_API.G_FALSE, p_number,
2820 lx_return_status, lx_msg_count, lx_msg_data, lx_cc_number
2821 );
2822 RETURN iby_security_pkg.get_hash(lx_cc_number,p_salt,p_site_salt);
2823 END Get_Hash;
2824
2825 PROCEDURE Get_Trxn_Payer_Attributes
2826 (
2827 p_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
2828 p_payer_equivalency IN VARCHAR2
2829 := IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
2830 x_payer_attributes OUT NOCOPY PayerAttributes_rec_type
2831 )
2832 IS
2833
2834 l_payer_level VARCHAR2(30);
2835 l_payer_id iby_external_payers_all.ext_payer_id%TYPE;
2836 l_payer_attribs IBY_FNDCPT_SETUP_PUB.PayerAttributes_rec_type;
2837
2838 CURSOR l_payer_attr_cur (
2839 ci_payer IN IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type,
2840 ci_payer_level IN VARCHAR2,
2841 ci_payer_equiv IN VARCHAR2
2842 )
2843 IS
2844 SELECT bank_charge_bearer_code, dirdeb_instruction_code
2845 FROM iby_external_payers_all p
2846 WHERE p.party_id = ci_payer.Party_Id
2847 AND IBY_FNDCPT_COMMON_PUB.Compare_Payer
2848 (ci_payer.org_type, ci_payer.org_id,
2849 ci_payer.Cust_Account_Id, ci_payer.Account_Site_Id,
2850 ci_payer_level,ci_payer_equiv,p.org_type,p.org_id,
2851 p.cust_account_id,p.acct_site_use_id) = 'T'
2852 ORDER BY p.acct_site_use_id, p.cust_account_id, p.org_id;
2853
2854 BEGIN
2855
2856 IBY_FNDCPT_SETUP_PUB.Get_Payer_Id(p_payer,FND_API.G_VALID_LEVEL_FULL,
2857 l_payer_level,l_payer_id,l_payer_attribs);
2858
2859 FOR l_payer_attr_rec in l_payer_attr_cur(p_payer,l_payer_level,p_payer_equivalency) LOOP
2860 IF (x_payer_attributes.Bank_Charge_Bearer is NULL) THEN
2861 x_payer_attributes.Bank_Charge_Bearer := l_payer_attr_rec.bank_charge_bearer_code;
2862 END IF;
2863
2864 IF (x_payer_attributes.DirectDebit_BankInstruction is NULL) THEN
2865 x_payer_attributes.DirectDebit_BankInstruction := l_payer_attr_rec.dirdeb_instruction_code;
2866 END IF;
2867 END LOOP;
2868
2869 END Get_Trxn_Payer_Attributes;
2870
2871
2872 END IBY_FNDCPT_SETUP_PUB;