[Home] [Help]
PACKAGE BODY: APPS.AR_PREPAYMENTS_PUB
Source
1 PACKAGE BODY AR_PREPAYMENTS_PUB AS
2 /* $Header: ARPPAYAB.pls 120.19 2010/06/02 05:08:47 dgaurab ship $ */
3
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AR_PREPAYMENTS_PUB';
8 G_MSG_UERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR;
9 G_MSG_ERROR CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_ERROR;
10 G_MSG_SUCCESS CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_SUCCESS;
11 G_MSG_HIGH CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH;
12 G_MSG_MEDIUM CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM;
13 G_MSG_LOW CONSTANT NUMBER := FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW;
14
15 /*========================================================================
16 | Prototype Declarations Procedures
17 *=======================================================================*/
18
19 PG_DEBUG varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
20
21 /*========================================================================
22 | Prototype Declarations Functions
23 *=======================================================================*/
24 /*=======================================================================
25 | PUBLIC Procedure Create_Prepayment
26 |
27 | DESCRIPTION
28 | Create prepayment receipt and put it on prepayment
29 | ----------------------------------------
30 |
31 | PSEUDO CODE/LOGIC
32 |
33 | PARAMETERS
34 |
35 |
36 | RETURNS
37 | nothing
38 |
39 | KNOWN ISSUES
40 |
41 |
42 |
43 | NOTES
44 |
45 |
46 |
47 | MODIFICATION HISTORY
48 | Date Author Description of Changes
49 | 10-SEP-2001 S Nambiar Created
50 | 10-MAR-2002 S Nambiar Bug 2315864 - Validate customer
51 | bank account id,raise error if
52 | NULL is passed
53 | 09-DEC-2003 J Pandey Bug3230122 forward port of 3220078
54 *=======================================================================*/
55 PROCEDURE Create_Prepayment(
56 -- Standard API parameters.
57 p_api_version IN NUMBER,
58 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
59 p_commit IN VARCHAR2 := FND_API.G_FALSE,
60 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
61 x_return_status OUT NOCOPY VARCHAR2,
62 x_msg_count OUT NOCOPY NUMBER,
63 x_msg_data OUT NOCOPY VARCHAR2,
64 -- Receipt info. parameters
65 p_usr_currency_code IN VARCHAR2 DEFAULT NULL, --the translated currency code
66 p_currency_code IN ar_cash_receipts.currency_code%TYPE DEFAULT NULL,
67 p_usr_exchange_rate_type IN VARCHAR2 DEFAULT NULL,
68 p_exchange_rate_type IN ar_cash_receipts.exchange_rate_type%TYPE DEFAULT NULL,
69 p_exchange_rate IN ar_cash_receipts.exchange_rate%TYPE DEFAULT NULL,
70 p_exchange_rate_date IN ar_cash_receipts.exchange_date%TYPE DEFAULT NULL,
71 p_amount IN ar_cash_receipts.amount%TYPE,
72 p_factor_discount_amount IN ar_cash_receipts.factor_discount_amount%TYPE DEFAULT NULL,
73
74 -------Multiple Prepay project: Receipt Number should be IN OUT
75 p_receipt_number IN OUT NOCOPY ar_cash_receipts.receipt_number%TYPE ,
76
77 p_receipt_date IN ar_cash_receipts.receipt_date%TYPE DEFAULT NULL,
78 p_gl_date IN ar_cash_receipt_history.gl_date%TYPE DEFAULT NULL,
79 p_maturity_date IN DATE DEFAULT NULL,
80 p_postmark_date IN DATE DEFAULT NULL,
81 p_customer_id IN ar_cash_receipts.pay_from_customer%TYPE DEFAULT NULL,
82 p_customer_name IN hz_parties.party_name%TYPE DEFAULT NULL,
83 p_customer_number IN hz_cust_accounts.account_number%TYPE DEFAULT NULL,
84 p_customer_bank_account_id IN ar_cash_receipts.customer_bank_account_id%TYPE DEFAULT NULL,
85 p_customer_bank_account_num IN ap_bank_accounts.bank_account_num%TYPE DEFAULT NULL,
86 p_customer_bank_account_name IN ap_bank_accounts.bank_account_name%TYPE DEFAULT NULL,
87 p_location IN hz_cust_site_uses.location%TYPE DEFAULT NULL,
88 p_customer_site_use_id IN hz_cust_site_uses.site_use_id%TYPE DEFAULT NULL,
89 p_customer_receipt_reference IN ar_cash_receipts.customer_receipt_reference%TYPE DEFAULT NULL,
90 p_override_remit_account_flag IN ar_cash_receipts.override_remit_account_flag%TYPE DEFAULT NULL,
91 p_remittance_bank_account_id IN ar_cash_receipts.remit_bank_acct_use_id%type DEFAULT NULL,
92 p_remittance_bank_account_num IN ce_bank_accounts.bank_account_num%TYPE DEFAULT NULL,
93 p_remittance_bank_account_name IN ce_bank_accounts.bank_account_name%TYPE DEFAULT NULL,
94 p_deposit_date IN ar_cash_receipts.deposit_date%TYPE DEFAULT NULL,
95 p_receipt_method_id IN ar_cash_receipts.receipt_method_id%TYPE DEFAULT NULL,
96 p_receipt_method_name IN ar_receipt_methods.name%TYPE DEFAULT NULL,
97 p_doc_sequence_value IN NUMBER DEFAULT NULL,
98 p_ussgl_transaction_code IN ar_cash_receipts.ussgl_transaction_code%TYPE DEFAULT NULL,
99 p_anticipated_clearing_date IN ar_cash_receipts.anticipated_clearing_date%TYPE DEFAULT NULL,
100 p_called_from IN VARCHAR2 DEFAULT NULL,
101 p_attribute_rec IN ar_receipt_api_pub.attribute_rec_type
102 DEFAULT ar_receipt_api_pub.attribute_rec_const,
103 -- ******* Global Flexfield parameters *******
104 p_global_attribute_rec IN ar_receipt_api_pub.global_attribute_rec_type
105 DEFAULT ar_receipt_api_pub.global_attribute_rec_const,
106 p_receipt_comments IN VARCHAR2 DEFAULT NULL,
107 -- *** Notes Receivable Additional Information ***
108 p_issuer_name IN ar_cash_receipts.issuer_name%TYPE DEFAULT NULL,
109 p_issue_date IN ar_cash_receipts.issue_date%TYPE DEFAULT NULL,
110 p_issuer_bank_branch_id IN ar_cash_receipts.issuer_bank_branch_id%TYPE DEFAULT NULL,
111 -- ** OUT NOCOPY variables for Creating receipt
112 p_cr_id OUT NOCOPY ar_cash_receipts.cash_receipt_id%TYPE,
113 -- Receipt application parameters
114 p_applied_payment_schedule_id IN ar_payment_schedules.payment_schedule_id%TYPE DEFAULT NULL,
115 p_amount_applied IN ar_receivable_applications.amount_applied%TYPE DEFAULT NULL,
116 p_application_ref_type IN VARCHAR2 DEFAULT NULL,
117 p_application_ref_id IN OUT NOCOPY NUMBER ,
118 p_application_ref_num IN OUT NOCOPY VARCHAR2 ,
119 p_secondary_application_ref_id IN OUT NOCOPY NUMBER ,
120 p_receivable_trx_id IN ar_receivable_applications.receivables_trx_id%TYPE DEFAULT NULL,
121 p_amount_applied_from IN ar_receivable_applications.amount_applied_from%TYPE DEFAULT NULL,
122 p_apply_date IN ar_receivable_applications.apply_date%TYPE DEFAULT NULL,
123 p_apply_gl_date IN ar_receivable_applications.gl_date%TYPE DEFAULT NULL,
124 app_ussgl_transaction_code IN ar_receivable_applications.ussgl_transaction_code%TYPE DEFAULT NULL,
125 p_show_closed_invoices IN VARCHAR2 DEFAULT 'FALSE',
126 p_move_deferred_tax IN VARCHAR2 DEFAULT 'Y',
127 app_attribute_rec IN ar_receipt_api_pub.attribute_rec_type
128 DEFAULT ar_receipt_api_pub.attribute_rec_const,
129 -- ******* Global Flexfield parameters *******
130 app_global_attribute_rec IN ar_receipt_api_pub.global_attribute_rec_type
131 DEFAULT ar_receipt_api_pub.global_attribute_rec_const,
132 app_comments IN ar_receivable_applications.comments%TYPE DEFAULT NULL,
133 -- processor such as iPayments
134 p_payment_server_order_num IN OUT NOCOPY ar_cash_receipts.payment_server_order_num%TYPE,
135 p_approval_code IN OUT NOCOPY ar_cash_receipts.approval_code%TYPE,
136
137 ---Bug 3220078 Change the parameter to TRUE
138 p_call_payment_processor IN VARCHAR2 DEFAULT FND_API.G_TRUE,
139
140 p_payment_response_error_code OUT NOCOPY VARCHAR2,
141 -- OUT NOCOPY parameter for the Application
142 p_receivable_application_id OUT NOCOPY ar_receivable_applications.receivable_application_id%TYPE,
143 p_payment_set_id IN OUT NOCOPY NUMBER ,
144 p_org_id IN NUMBER DEFAULT NULL,
145 p_payment_trxn_extension_id IN ar_cash_receipts.payment_trxn_extension_id%TYPE
146 ) IS
147
148
149 l_cash_receipt_id NUMBER(15);
150 l_create_return_status VARCHAR2(1);
151 l_remit_return_status VARCHAR2(1);
152 l_payment_set_id NUMBER;
153 l_cc_return_status VARCHAR2(1); -- credit card return status
154 l_activity_return_status VARCHAR2(1); -- credit card return status
155 l_cc_check VARCHAR2(1);
156 l_receivables_trx_id NUMBER;
157 l_response_error_code VARCHAR2(80);
158
159 --Bug 3220078 --
160 l_cash_receipt_status ar_receipt_classes.creation_status%type;
161 l_payment_type ar_receipt_methods.payment_type_code%type;
162
163 l_api_name CONSTANT VARCHAR2(20) := 'Create_Prepayment';
164 l_api_version CONSTANT NUMBER := 1.0;
165 l_org_return_status VARCHAR2(1);
166 l_org_id NUMBER;
167
168 -- payment uptake
169 CURSOR rct_info_cur IS
170 SELECT cr.receipt_number,
171 cr.amount,
172 cr.currency_code,
173 rm.PAYMENT_CHANNEL_CODE, /* NEW ADDED */
174 rc.creation_status, /* AR USE */
175 cr.org_id,
176 cr.payment_trxn_extension_id,
177 party.party_id,
178 cr.pay_from_customer,
179 cr.customer_site_use_id,
180 pr.home_country
181 FROM ar_cash_receipts_all cr,
182 ar_receipt_methods rm,
183 ar_receipt_classes rc,
184 hz_cust_accounts hca,
185 hz_parties party,
186 /* Need to pass country code for SEPA specific receipts */
187 ce_bank_acct_uses bau,
188 ce_bank_accounts cba,
189 hz_parties bank,
190 hz_organization_profiles pr
191 WHERE cr.cash_receipt_id = l_cash_receipt_id
192 AND hca.party_id = party.party_id
193 AND hca.cust_account_id = cr.pay_from_customer
194 AND cr.receipt_method_id = rm.receipt_method_id
195 AND rm.receipt_class_id = rc.receipt_class_id
196 AND bau.bank_acct_use_id = cr.remit_bank_acct_use_id
197 AND cba.bank_account_id = bau.bank_account_id
198 AND bank.party_id = cba.bank_id
199 AND pr.party_id = bank.party_id;
200
201 rct_info rct_info_cur%ROWTYPE;
202 l_cr_rec ar_cash_receipts_all%ROWTYPE;
203 l_org_type HR_ALL_ORGANIZATION_UNITS.TYPE%TYPE;
204 l_action VARCHAR2(80);
205 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
206 l_msg_count NUMBER;
207 l_msg_data VARCHAR2(2000);
208 l_iby_msg_data VARCHAR2(2000);
209 l_vend_msg_data VARCHAR2(2000);
210 l_payment_trxn_extension_id ar_cash_receipts.payment_trxn_extension_id%TYPE;
211
212 /* DECLARE the variables required for the payment engine (CPY ) all the REC TYPES */
213 p_trxn_entity_id NUMBER;
214 lc_trxn_entity_id IBY_FNDCPT_COMMON_PUB.Id_tbl_type;
215 l_auth_flag VARCHAR2(1);
216 l_auth_id NUMBER;
217
218 /* END DECLARE the variables required for the payment engine (CPY ) all the REC TYPES */
219 /* DECLARE the variables required for the payment engine (CPY AND AUTH) all the REC TYPES */
220
221 l_payer_rec IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
222 l_payee_rec IBY_FNDCPT_TRXN_PUB.PayeeContext_rec_type;
223 l_trxn_entity_id NUMBER;
224 l_auth_attribs_rec IBY_FNDCPT_TRXN_PUB.AuthAttribs_rec_type;
225 l_trxn_attribs_rec IBY_FNDCPT_TRXN_PUB.TrxnExtension_rec_type;
226 l_amount_rec IBY_FNDCPT_TRXN_PUB.Amount_rec_type;
227 l_authresult_rec IBY_FNDCPT_TRXN_PUB.AuthResult_rec_type; /* OUT AUTH RESULT STRUCTURE */
228 l_response_rec IBY_FNDCPT_COMMON_PUB.Result_rec_type; /* OUT RESPONSE STRUCTURE */
229 l_entity_id NUMBER; -- OUT FROM COPY
230 /* END DECLARE the variables required for the payment engine (AUTH) all the REC TYPES */
231
232
233
234 BEGIN
235 IF PG_DEBUG in ('Y', 'C') THEN
236 arp_standard.debug('ar_prepayments_pub.Create_Prepayment ()+'|| p_org_id);
237 arp_util.debug('Create_Prepayment: payment_trxn_extension_id' || to_char(p_payment_trxn_extension_id) );
238 arp_util.debug('Create_Prepayment receipt method id' || to_char(p_receipt_method_id) );
239 END IF;
240
241 -- first reinitialize ARP_GLOBAL
242 arp_global.init_global;
243
244 /*------------------------------------+
245 | Standard start of API savepoint |
246 +------------------------------------*/
247 SAVEPOINT Create_Prepayment_PVT;
248
249 /*-----------------------------------------+
250 | Initialize return status to SUCCESS |
251 +-----------------------------------------*/
252
253 x_return_status := FND_API.G_RET_STS_SUCCESS;
254
255 /*--------------------------------------------------+
256 | Standard call to check for call compatibility |
257 +--------------------------------------------------*/
258
259 IF NOT FND_API.Compatible_API_Call(
260 l_api_version,
261 p_api_version,
262 l_api_name,
263 G_PKG_NAME
264 )
265 THEN
266 x_return_status := FND_API.G_RET_STS_ERROR;
267 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
268 END IF;
269
270 /*--------------------------------------------------------------+
271 | Initialize message list if p_init_msg_list is set to TRUE |
272 +--------------------------------------------------------------*/
273 IF FND_API.to_Boolean( p_init_msg_list )
274 THEN
275 FND_MSG_PUB.initialize;
276 END IF;
277
278
279
280
281 /* SSA change */
282 l_org_id := p_org_id;
283 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
284 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
285 p_return_status =>l_org_return_status);
286 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
287 x_return_status := FND_API.G_RET_STS_ERROR;
288 ELSE
289 ----Multiple prepayments allow payment types other than credit card
290 /*--------------------------------------------------------------------
291 IF p_receipt_method_id is not null THEN
292 BEGIN
293 SELECT 'Y' into l_cc_check
294 FROM ar_receipt_methods
295 WHERE receipt_method_id=p_receipt_method_id
296 AND payment_type_code = 'CREDIT_CARD';
297 EXCEPTION
298 WHEN no_data_found THEN
299 IF PG_DEBUG in ('Y', 'C') THEN
300 arp_util.debug('Create_Prepayment: ' || 'Prepayment only allowed for credit card');
301 END IF;
302 FND_MESSAGE.SET_NAME('AR','AR_RAPI_PREPAY_ONLYFOR_CC');
303 FND_MSG_PUB.Add;
304 x_return_status := FND_API.G_RET_STS_ERROR;
305 RETURN;
306 END;
307
308 END IF;
309 ---------------------------------------------------------------------*/
310 /* PAYMENT UPTAKE COMMENTED BY BICHATTE
311 --Check whether customer bank account details are passed
312 IF ((p_customer_bank_account_id is null) AND
313 (p_customer_bank_account_num is null) AND
314 (p_customer_bank_account_name is null)) THEN
315
316 IF PG_DEBUG in ('Y', 'C') THEN
317 arp_util.debug('Create_Prepayment: ' || 'For Prepayment, customer banks account details must be passed');
318 END IF;
319 FND_MESSAGE.SET_NAME('AR','AR_RAPI_CUS_BK_AC_2_INVALID');
320 FND_MSG_PUB.Add;
321 x_return_status := FND_API.G_RET_STS_ERROR;
322 RETURN;
323 END IF;
324
325 */
326 l_receivables_trx_id := p_receivable_trx_id;
327
328 arp_util.debug('Create_Prepayment: payment_trxn_extension_id' || to_char(p_payment_trxn_extension_id) );
329
330
331 --calling the internal create_cash routine
332 ar_receipt_api_pub.Create_Cash(
333 p_api_version => p_api_version,
334 p_init_msg_list => p_init_msg_list,
335 p_commit => FND_API.G_FALSE,
336 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
337 x_return_status => l_create_return_status,
338 x_msg_count => x_msg_count,
339 x_msg_data => x_msg_data,
340 -- Receipt info. parameters
341 p_usr_currency_code => p_usr_currency_code,
342 p_currency_code => p_currency_code,
343 p_usr_exchange_rate_type => p_usr_exchange_rate_type,
344 p_exchange_rate_type => p_exchange_rate_type,
345 p_exchange_rate => p_exchange_rate,
346 p_exchange_rate_date => p_exchange_rate_date,
347 p_amount => p_amount,
348 p_factor_discount_amount => p_factor_discount_amount,
349 p_receipt_number => p_receipt_number,
350 p_receipt_date => p_receipt_date,
351 p_gl_date => p_gl_date,
352 p_maturity_date => p_maturity_date,
353 p_postmark_date => p_postmark_date,
354 p_customer_id => p_customer_id,
355 p_customer_name => p_customer_name,
356 p_customer_number => p_customer_number,
357 p_customer_bank_account_id => p_customer_bank_account_id,
358 p_customer_bank_account_num => p_customer_bank_account_num,
359 p_customer_bank_account_name => p_customer_bank_account_name,
360 p_payment_trxn_extension_id => p_payment_trxn_extension_id,
361 p_location => p_location,
362 p_customer_site_use_id => p_customer_site_use_id,
363 p_customer_receipt_reference => p_customer_receipt_reference,
364 p_override_remit_account_flag => p_override_remit_account_flag,
365 p_remittance_bank_account_id => p_remittance_bank_account_id,
366 p_remittance_bank_account_num => p_remittance_bank_account_num,
367 p_remittance_bank_account_name => p_remittance_bank_account_name,
368 p_deposit_date => p_deposit_date,
369 p_receipt_method_id => p_receipt_method_id,
370 p_receipt_method_name => p_receipt_method_name,
371 p_doc_sequence_value => p_doc_sequence_value,
372 p_ussgl_transaction_code => p_ussgl_transaction_code,
373 p_anticipated_clearing_date => p_anticipated_clearing_date,
374 p_called_from => p_called_from,
375 p_attribute_rec => p_attribute_rec,
376 p_global_attribute_rec => p_global_attribute_rec ,
377 p_comments => p_receipt_comments,
378 p_issuer_name => p_issuer_name,
379 p_issue_date => p_issue_date,
380 p_issuer_bank_branch_id => p_issuer_bank_branch_id,
381 p_org_id => p_org_id,
382 p_cr_id => l_cash_receipt_id --out variable
383 );
384
385 IF PG_DEBUG in ('Y', 'C') THEN
386 arp_util.debug('Create_Prepayment: ' || 'Receipt create_return_status '||l_create_return_status);
387 END IF;
388
389 --IF the receipt creation part returns no errors then
390 --call the application routine.
391
392 IF l_create_return_status = FND_API.G_RET_STS_SUCCESS THEN
393
394 IF x_msg_count = 1 THEN
395 /* If one message, like warning, then put this back on stack as the
396 Create routine must have removed it from the stack and put it on x_msg_data
397 */
398 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
399 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','Create_Prepayment : '||x_msg_data);
400 FND_MSG_PUB.Add;
401 END IF;
402 --While applying to prepayment, generate payment_set_id from the
403 --sequence and pass to apply routine.
404 --IF payment set id is passed, then do not generate.
405 IF p_payment_set_id is null THEN
406 BEGIN
407 SELECT ar_receivable_applications_s1.nextval
408 INTO l_payment_set_id
409 FROM dual;
410 EXCEPTION
411 WHEN others THEN
412 IF PG_DEBUG in ('Y', 'C') THEN
413 arp_util.debug('Create_Prepayment: ' || 'Payment set id sequence generation failed'||sqlerrm);
414 END IF;
415 FND_MESSAGE.SET_NAME('AR','AR_RAPI_PREPAY_SEQ_FAILED');
416 FND_MSG_PUB.Add;
417 x_return_status := FND_API.G_RET_STS_ERROR;
418 RETURN;
419 END;
420 ELSE
421 l_payment_set_id := p_payment_set_id;
422 END IF;
423
424 --Default receivable_trx_id for prepayment .
425
426 ar_receipt_lib_pvt.Default_prepay_cc_activity(
427 'PREPAYMENT',
428 l_receivables_trx_id,
429 l_activity_return_status);
430
431 IF l_activity_return_status <> FND_API.G_RET_STS_SUCCESS THEN
432 ROLLBACK TO Create_Prepayment_PVT;
433 x_return_status := l_activity_return_status;
434
435 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
436 p_count => x_msg_count,
437 p_data => x_msg_data);
438
439 RETURN; -- exit back to caller
440 END IF;
441 /* Notes -- bichatte we dont need the check for p_call_payment_processor */
442
443
444 SELECT payment_channel_code
445 INTO l_payment_type
446 FROM ar_receipt_methods
447 WHERE receipt_method_id = p_receipt_method_id;
448
449
450 l_payment_trxn_extension_id := p_payment_trxn_extension_id;
451
452 IF ((l_payment_trxn_extension_id IS NULL) AND ( l_payment_type in ( 'CREDIT_CARD', 'BANK_ACCT_XFER'))) THEN
453
454 IF PG_DEBUG in ('Y', 'C') THEN
455 arp_util.debug('ERROR: payment_trxn_extension is NULL ' ||nvl(l_payment_trxn_extension_id,-9999));
456 END IF;
457
458 FND_MESSAGE.set_name('AR', 'AR_PAY_PROCESS_INVALID_STATUS');
459 FND_MSG_PUB.Add;
460
461 x_return_status := FND_API.G_RET_STS_ERROR; -- should never happen
462
463 RETURN;
464 END IF;
465
466 /* bichatte start */
467 -- Payment uptake
468 -- i) here we will check if the l_payment_trxn_extension_id is already authorised or not
469 -- accordingly call Auth
470 -- ii) pass the l_payment_trxn_extension_id to create_cash so that its gets copied and stamped
471 -- on the cash receipt.
472
473
474
475 IF l_payment_trxn_extension_id IS NOT NULL THEN
476 IF PG_DEBUG in ('Y', 'C') THEN
477 arp_util.debug('Process_Credit_Card: ' || 'COPY the trx_extension_id');
478 END IF;
479 IF PG_DEBUG in ('Y', 'C') THEN
480 arp_standard.debug( 'Entering payment processing...');
481 END IF;
482
483
484 OPEN rct_info_cur;
485 FETCH rct_info_cur INTO rct_info;
486
487 IF rct_info_cur%FOUND THEN
488
489 -- Step 1: (always performed):
490
491 -- set up payee record:
492 l_payee_rec.org_id := rct_info.org_id; -- receipt's org_id
493 l_payee_rec.org_type := 'OPERATING_UNIT' ; -- ( HR_ORGANIZATION_UNITS )
494 l_payee_rec.Int_Bank_Country_Code := rct_info.home_country; --Bug9706412
495
496
497 -- set up payer (=customer) record:
498
499 l_payer_rec.Payment_Function := 'CUSTOMER_PAYMENT';
500 l_payer_rec.Party_Id := rct_info.party_id; -- receipt customer party id mandatory
501 l_payer_rec.org_id := rct_info.org_id ;
502 l_payer_rec.org_type := 'OPERATING_UNIT';
503 l_payer_rec.Cust_Account_Id :=rct_info.pay_from_customer; -- receipt customer account_id
504 l_payer_rec.Account_Site_Id :=rct_info.customer_site_use_id; -- receipt customer site_id
505
506
507
508
509 -- set up trx_entity record: /* NOTE HERE we have to call Copy_Transaction_Extension */
510
511
512 -- set up auth_attribs record:
513 l_auth_attribs_rec.RiskEval_Enable_Flag := 'N';
514 -- set up trxn_attribs record:
515 l_trxn_attribs_rec.Originating_Application_Id := arp_standard.application_id;
516 l_trxn_attribs_rec.order_id := rct_info.receipt_number;
517 l_trxn_attribs_rec.Trxn_Ref_Number1 := 'RECEIPT';
518 l_trxn_attribs_rec.Trxn_Ref_Number2 := l_cash_receipt_id;
519
520 -- set up amounts
521
522 l_amount_rec.value := rct_info.amount;
523 l_amount_rec.currency_code := rct_info.currency_code;
524
525 IF PG_DEBUG in ('Y', 'C') THEN
526 arp_standard.debug( 'check and then call Auth');
527 END IF;
528
529 -- determine whether to AUTHORIZE
530
531 -- assign the value for payment_trxn_extension record the copied value
532
533 l_trxn_entity_id := rct_info.payment_trxn_extension_id;
534
535 IF PG_DEBUG in ('Y', 'C') THEN
536 arp_standard.debug( 'Calling get auth for pmt_trxn_extn_id ');
537 arp_standard.debug( 'l_trxn_entity_id ' || to_char(l_trxn_entity_id ) );
538
539 END IF;
540
541 Begin
542 SELECT decode(summ.status, NULL, 'N', 'Y') AUTHORIZED_FLAG
543 into l_auth_flag
544 FROM iby_trxn_summaries_all summ,
545 iby_fndcpt_tx_operations op
546 WHERE summ.transactionid = op.transactionid
547 AND reqtype = 'ORAPMTREQ'
548 AND status IN(0, 100)
549 AND trxntypeid IN(2, 3, 20)
550 AND op.trxn_extension_id = l_trxn_entity_id
551 AND summ.trxnmid =
552 (SELECT MAX(trxnmid)
553 FROM iby_trxn_summaries_all
554 WHERE transactionid = summ.transactionid
555 AND reqtype = 'ORAPMTREQ'
556 AND status IN(0, 100)
557 AND trxntypeid IN(2, 3, 20));
558 Exception
559 when others then
560 l_auth_flag := 'N';
561 End;
562
563 arp_standard.debug ( 'the value of auth_flag is = ' || l_auth_flag);
564
565 If l_auth_flag = 'Y' then
566
567 select AUTHORIZATION_ID
568 into l_auth_id
569 from IBY_TRXN_EXT_AUTHS_V
570 where TRXN_EXTENSION_ID = p_payment_trxn_extension_id
571 and AUTHORIZATION_STATUS = 0;
572
573 arp_standard.debug ( 'the value of auth_id is = ' || l_auth_id);
574
575 ARP_CASH_RECEIPTS_PKG.set_to_dummy(l_cr_rec);
576
577 l_cr_rec.approval_code := 'AR'||to_char(l_auth_Id);
578
579 ARP_CASH_RECEIPTS_PKG.update_p(l_cr_rec, l_cash_receipt_id);
580
581 arp_standard.debug('CR rec updated with auth_id and auth code ');
582 end if;
583
584
585
586
587 IF l_auth_flag <> 'Y' then
588 arp_standard.debug('auth needs to called');
589
590 IF PG_DEBUG in ('Y', 'C') THEN
591 arp_standard.debug( 'Calling get auth for pmt_trxn_extn_id ');
592 arp_standard.debug( ' l_payee_rec.org_id ' || to_char(l_payee_rec.org_id) );
593 arp_standard.debug( ' l_payee_rec.org_type ' || to_char( l_payee_rec.org_type) );
594 arp_standard.debug( ' l_payee_rec.Int_Bank_Country_Code ' || to_char(l_payee_rec.Int_Bank_Country_Code) );
595 arp_standard.debug( ' l_payer_rec.Payment_Function ' || to_char( l_payer_rec.Payment_Function) );
596 arp_standard.debug( ' l_payer_rec.Party_Id ' || to_char( l_payer_rec.Party_Id) );
597 arp_standard.debug( ' l_payer_rec.org_id ' || to_char(l_payer_rec.org_id) );
598 arp_standard.debug( ' l_payer_rec.org_type ' || to_char( l_payer_rec.org_type) );
599 arp_standard.debug( 'l_payer_rec.Cust_Account_Id ' || to_char(l_payer_rec.Cust_Account_Id) );
600 arp_standard.debug( 'l_payer_rec.Account_Site_Id ' || to_char(l_payer_rec.Account_Site_Id) );
601 arp_standard.debug( 'l_trxn_entity_id ' || to_char(l_trxn_entity_id ) );
602 arp_standard.debug( 'l_amount_rec.value: ' || to_char(l_amount_rec.value) );
603 arp_standard.debug( 'l_amount_rec.currency_code: ' || l_amount_rec.currency_code );
604
605 arp_standard.debug( 'Calling get_auth for pmt_trxn_extn_id ');
606 END IF;
607
608 IBY_FNDCPT_TRXN_PUB.Create_Authorization(
609 p_api_version => 1.0,
610 p_init_msg_list => FND_API.G_TRUE,
611 x_return_status => l_return_status,
612 x_msg_count => l_msg_count,
613 x_msg_data => l_msg_data,
614 p_payer => l_payer_rec,
615 p_payer_equivalency => IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_UPWARD,
616 p_payee => l_payee_rec,
617 p_trxn_entity_id => l_trxn_entity_id,
618 p_auth_attribs => l_auth_attribs_rec,
619 p_amount => l_amount_rec,
620 x_auth_result => l_authresult_rec, -- out auth result struct
621 x_response => l_response_rec ); -- out response struct
622
623
624 x_msg_count := l_msg_count;
625 x_msg_data := l_msg_data;
626
627 arp_standard.debug('x_return_status :<' || l_return_status || '>');
628 arp_standard.debug('x_msg_count :<' || l_msg_count || '>');
629
630 FOR i IN 1..l_msg_count LOOP
631 arp_standard.debug('x_msg #' || TO_CHAR(i) || ' = <' ||
632 SUBSTR(fnd_msg_pub.get(p_msg_index => i,p_encoded => FND_API.G_FALSE),1,150) || '>');
633 END LOOP;
634
635 IF PG_DEBUG in ('Y', 'C') THEN
636 arp_standard.debug( '-------------------------------------');
637 arp_standard.debug( 'l_response_rec.Result_Code: ' || l_response_rec.Result_Code);
638 arp_standard.debug( 'l_response_rec.Result_Category: ' || l_response_rec.Result_Category);
639 arp_standard.debug( 'l_response_rec.Result_message : ' || l_response_rec.Result_message );
640 arp_standard.debug( 'l_authresult_rec.Auth_Id: ' || l_authresult_rec.Auth_Id);
641 arp_standard.debug( 'l_authresult_rec.Auth_Date: ' || l_authresult_rec.Auth_Date);
642 arp_standard.debug( 'l_authresult_rec.Auth_Code: ' || l_authresult_rec.Auth_Code);
643 arp_standard.debug( 'l_authresult_rec.AVS_Code: ' || l_authresult_rec.AVS_Code);
644 arp_standard.debug( 'l_authresult_rec.Instr_SecCode_Check:'|| l_authresult_rec.Instr_SecCode_Check);
645 arp_standard.debug( 'l_authresult_rec.PaymentSys_Code: ' || l_authresult_rec.PaymentSys_Code);
646 arp_standard.debug( 'l_authresult_rec.PaymentSys_Msg: ' || l_authresult_rec.PaymentSys_Msg);
647 -- arp_standard.debug( 'l_authresult_rec.Risk_Result: ' || l_authresult_rec.Risk_Result);
648
649 END IF;
650
651 IF (l_return_status = FND_API.G_RET_STS_SUCCESS) THEN
652 -- update cash receipt with authorization code and
653
654 ARP_CASH_RECEIPTS_PKG.set_to_dummy(l_cr_rec);
655 l_cr_rec.approval_code := l_authresult_rec.Auth_code ||'AR'||to_char(l_authresult_rec.Auth_Id);
656
657 ARP_CASH_RECEIPTS_PKG.update_p(l_cr_rec, l_cash_receipt_id);
658
659 IF PG_DEBUG in ('Y', 'C') THEN
660 arp_standard.debug('CR rec updated with auth_id and auth code ');
661 END IF;
662
663 END IF;
664
665 -- check if call was successful
666 --Add message to message stack only it it is called from iReceivables
667 --if not pass the message stack received from iPayment
668
669 IF (NVL(p_called_from,'NONE') = 'IREC') THEN
670 IF PG_DEBUG in ('Y', 'C') THEN
671 arp_standard.debug( 'l_MSG_COUNT=>'||to_char(l_MSG_COUNT));
672 END IF;
673 fnd_msg_pub.dump_list;
674 IF PG_DEBUG in ('Y', 'C') THEN
675 arp_standard.debug( 'Errors: ');
676 END IF;
677 IF(l_MSG_COUNT=1) THEN
678 IF PG_DEBUG in ('Y', 'C') THEN
679 arp_standard.debug( l_MSG_DATA);
680 END IF;
681 ELSIF(l_MSG_COUNT>1)THEN
682 LOOP
683 l_MSG_DATA:=FND_MSG_PUB.GET(p_encoded=>FND_API.G_FALSE);
684 IF (l_MSG_DATA IS NULL)THEN
685 EXIT;
686 END IF;
687 IF PG_DEBUG in ('Y', 'C') THEN
688 arp_standard.debug( l_MSG_DATA);
689 END IF;
690 END LOOP;
691 END IF;
692 END IF;
693
694 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS)
695 AND (NVL(p_called_from,'NONE') = 'IREC') then
696
697 ROLLBACK TO Create_Prepayment_Pvt;
698 FND_MESSAGE.set_name('AR', 'AR_PAY_PROCESS_AUTHFAILURE');
699 FND_MSG_PUB.Add;
700 x_return_status := l_return_status;
701 RETURN;
702
703 ELSIF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
704
705 ROLLBACK TO Create_Prepayment_Pvt;
706 arp_standard.debug('create_cash_126');
707 FND_MESSAGE.set_name('AR', 'AR_CC_AUTH_FAILED');
708 FND_MSG_PUB.Add;
709
710 IF l_response_rec.Result_Code is NOT NULL THEN
711
712 ---Raise the PAYMENT error code concatenated with the message
713
714 l_iby_msg_data := substrb( l_response_rec.Result_Code || ': '||
715 l_response_rec.Result_Message , 1, 240);
716
717 arp_standard.debug( 'l_iby_msg_data: ' || l_iby_msg_data);
718 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
719 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',l_iby_msg_data);
720
721 FND_MSG_PUB.Add;
722
723 END IF;
724
725 IF l_authresult_rec.PaymentSys_Code is not null THEN
726
727 ---Raise the VENDOR error code concatenated with the message
728
729 l_vend_msg_data := substrb(l_authresult_rec.PaymentSys_Code || ': '||
730 l_authresult_rec.PaymentSys_Msg , 1, 240 );
731
732 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
733 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT',l_vend_msg_data);
734
735 FND_MSG_PUB.Add;
736
737 END IF;
738
739
740 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
741 p_count => x_msg_count,
742 p_data => x_msg_data );
743
744 x_return_status := l_return_status;
745 RETURN;
746
747 END IF; /* End the error handling CREATE */
748
749 END IF; /* END if of auth flag N */
750
751
752 END IF; -- rct_info_cur%FOUND
753
754 END IF; --- l_payment_trxn_extension_id is not null.
755
756 /* bichatte end */
757
758
759 ar_receipt_api_pub.Apply_other_account(
760 -- Standard API parameters.
761 p_api_version => p_api_version,
762 p_init_msg_list => FND_API.G_FALSE, --message stack is not initialized
763 p_commit => p_commit,
764 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
765 x_return_status => l_create_return_status,
766 x_msg_count => x_msg_count,
767 x_msg_data => x_msg_data,
768 p_receivable_application_id => p_receivable_application_id,
769 --Receipt application parameters.
770 p_cash_receipt_id => l_cash_receipt_id,
771 p_receipt_number => p_receipt_number,
772 p_amount_applied => p_amount_applied,
773 p_receivables_trx_id => l_receivables_trx_id,
774 p_applied_payment_schedule_id => p_applied_payment_schedule_id,
775 p_apply_date => p_apply_date,
776 p_apply_gl_date => p_apply_gl_date,
777 p_ussgl_transaction_code => app_ussgl_transaction_code,
778 p_application_ref_type => p_application_ref_type,
779 p_application_ref_id => p_application_ref_id,
780 p_application_ref_num => p_application_ref_num,
781 p_secondary_application_ref_id => p_secondary_application_ref_id,
782 p_org_id => p_org_id,
783 p_payment_set_id => l_payment_set_id,
784 p_attribute_rec => app_attribute_rec,
785 -- ******* Global Flexfield parameters *******
786 p_global_attribute_rec => app_global_attribute_rec,
787 p_comments => app_comments
788 );
789
790 --If the application fails then we need to rollback all the changes
791 --made in the create() routine also.
792 IF l_create_return_status <> FND_API.G_RET_STS_SUCCESS THEN
793 ROLLBACK TO Create_Prepayment_Pvt;
794 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
795 p_count => x_msg_count,
796 p_data => x_msg_data);
797 x_return_status := l_create_return_status;
798 RETURN;
799 ELSE
800
801 ------Mult. Prepay remittance based on receipt class setup---
802 --Prepayment Receipt needs to be remitted eventhough the receipt
803 -- class status does not require remittance immediatly.
804
805 --Bug 3220078 no need to remit the receipts from 'CONFIRMED'
806 --status but ipayment calls need to be made acc. to receipt
807 --class setup
808
809
810 ----get the status and payment method of of the receipt -----
811 SELECT rc.creation_status , nvl(rm.payment_channel_code, 'CHECK')
812 INTO l_cash_receipt_status, l_payment_type
813 FROM ar_cash_receipts cr, ar_receipt_classes rc,
814 ar_receipt_methods rm
815 WHERE cr.cash_receipt_id = l_cash_receipt_id
816 AND cr.receipt_method_id = rm.receipt_method_id
817 AND rm.receipt_class_id = rc.receipt_class_id;
818
819
820 -- charge credit card if needed. Note: this only
821 -- happens if the receipt and application creation
822 -- was successful. All relevant information for the
823 -- payment can be derived from the cash receipt.
824
825 --for CONFIRMED auth only
826 --REMITTED,CLEARED auth and capture
827
828 IF ( (l_payment_type = 'CREDIT_CARD') AND
829 l_cash_receipt_status IN ('CONFIRMED', 'REMITTED', 'CLEARED')
830 )
831 THEN
832
833 IF PG_DEBUG in ('Y', 'C') THEN
834 arp_standard.debug('Create_Prepayment: ' || 'Checking p_call_payment_processor: ' || p_call_payment_processor);
835 END IF;
836 /* if (p_call_payment_processor = FND_API.G_TRUE) then
837
838 Process_Credit_Card(
839 p_cash_receipt_id => l_cash_receipt_id,
840 p_payment_server_order_num =>
841 p_payment_server_order_num,
842 p_auth_code => p_approval_code,
843 p_response_error_code => l_response_error_code,
844 x_msg_count => x_msg_count,
845 x_msg_data => x_msg_data,
846 x_return_status => l_cc_return_status);
847
848
849 -- If the payment processor call fails, then we
850 -- need to rollback all the changes
851 -- made in the create() and apply() routines also.
852
853 IF l_cc_return_status <> FND_API.G_RET_STS_SUCCESS THEN
854 x_return_status := l_cc_return_status;
855 p_payment_response_error_code := l_response_error_code;
856
857 ROLLBACK TO Create_Prepayment_PVT;
858 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
859 p_count => x_msg_count,
860 p_data => x_msg_data);
861
862 RETURN; -- exit back to caller
863 END IF;
864
865 end if; -- if p_call_payment_processor = fnd_api.g_true */
866 END IF; --l_payment_type
867
868 -- need to pass back cr_id
869 p_cr_id := l_cash_receipt_id;
870 p_payment_set_id := l_payment_set_id;
871
872 END IF;
873 ELSE --after create_cash success status
874 x_return_status := l_create_return_status;
875 END IF;
876 END IF;
877
878 /*--------------------------------+
879 | Standard check of p_commit |
880 +--------------------------------*/
881
882 IF FND_API.To_Boolean( p_commit )
883 THEN
884 IF PG_DEBUG in ('Y', 'C') THEN
885 arp_util.debug('Create_Prepayments: ' || 'committing');
886 END IF;
887 Commit;
888 END IF;
889
890 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
891 p_count => x_msg_count,
892 p_data => x_msg_data);
893
894 IF PG_DEBUG in ('Y', 'C') THEN
895 arp_util.debug('ar_prepayments_pub.Create_Prepayment ()-');
896 END IF;
897
898 EXCEPTION
899 WHEN OTHERS THEN
900 IF (SQLCODE = -20001) THEN
901 ROLLBACK TO Create_Prepayment_PVT;
902
903 -- Display_Parameters;
904 x_return_status := FND_API.G_RET_STS_ERROR ;
905 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
906 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','CREATE_PREPAYMENT : '||SQLERRM);
907 FND_MSG_PUB.Add;
908
909 FND_MSG_PUB.Count_And_Get( p_encoded => FND_API.G_FALSE,
910 p_count => x_msg_count,
911 p_data => x_msg_data
912 );
913 RETURN;
914 ELSE
915 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
916 FND_MESSAGE.SET_NAME ('AR','GENERIC_MESSAGE');
917 FND_MESSAGE.SET_TOKEN('GENERIC_TEXT','CREATE_PREPAYMENT : '||SQLERRM);
918 FND_MSG_PUB.Add;
919 END IF;
920
921 IF PG_DEBUG in ('Y', 'C') THEN
922 arp_util.debug('Create_Prepayment: ' || SQLCODE, G_MSG_ERROR);
923 arp_util.debug('Create_Prepayment: ' || SQLERRM, G_MSG_ERROR);
924 END IF;
925
926 ROLLBACK TO Create_Prepayment_PVT;
927
928 IF FND_MSG_PUB.Check_Msg_Level THEN
929 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,
930 l_api_name
931 );
932 END IF;
933
934 -- Display_Parameters;
935 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
936 p_count => x_msg_count,
937 p_data => x_msg_data);
938
939
940 IF PG_DEBUG in ('Y', 'C') THEN
941 arp_util.debug('ar_prepayments_pub.Create_Prepayment ()-');
942 END IF;
943
944 END Create_Prepayment;
945
946 /*=======================================================================
947 | PUBLIC Procedure Get_Installment
948 |
949 | DESCRIPTION
950 | Gets the installment number and amount for a payment term
951 | ----------------------------------------------------------
952 |
953 | PSEUDO CODE/LOGIC
954 |
955 | PARAMETERS
956 |
957 |
958 | RETURNS
959 | nothing
960 |
961 | KNOWN ISSUES
962 |
963 |
964 |
965 | NOTES
966 |
967 |
968 |
969 | MODIFICATION HISTORY
970 | Date Author Description of Changes
971 | 12-DEC-2003 Jyoti Pandey Bug 3248093 Add get_installment
972 | procedure in this package
973 *=======================================================================*/
974
975 PROCEDURE get_installment(
976 p_term_id IN NUMBER,
977 p_amount IN NUMBER,
978 p_currency_code IN VARCHAR2,
979 p_org_id IN NUMBER DEFAULT NULL,
980 p_installment_tbl OUT NOCOPY ar_prepayments_pub.installment_tbl,
981 x_return_status OUT NOCOPY VARCHAR2,
982 x_msg_count OUT NOCOPY NUMBER,
983 x_msg_data OUT NOCOPY VARCHAR2) IS
984
985 l_installment_tbl installment_tbl;
986 l_return_status VARCHAR2(1);
987 l_msg_count NUMBER;
988 l_msg_data VARCHAR2(255);
989 l_org_return_status VARCHAR2(1);
990 l_org_id NUMBER;
991
992 BEGIN
993
994 /* SSA change */
995 l_org_id := p_org_id;
996 l_org_return_status := FND_API.G_RET_STS_SUCCESS;
997 ar_mo_cache_utils.set_org_context_in_api(p_org_id =>l_org_id,
998 p_return_status =>l_org_return_status);
999 IF l_org_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1000 x_return_status := FND_API.G_RET_STS_ERROR;
1001 ELSE
1002
1003 AR_PREPAYMENTS.get_installment(
1004 p_term_id,
1005 p_amount,
1006 p_currency_code,
1007 p_installment_tbl,
1008 x_return_status,
1009 x_msg_count,
1010 x_msg_data);
1011 END IF;
1012
1013 END get_installment;
1014
1015
1016 END AR_PREPAYMENTS_PUB;