[Home] [Help]
PACKAGE BODY: APPS.ARP_PROCESS_PAYINFO
Source
1 PACKAGE BODY ARP_PROCESS_PAYINFO AS
2 /*$Header: ARPPYMTSB.pls 120.14 2010/06/22 06:44:38 vpusulur ship $ */
3
4 /*=======================================================================+
5 | Package Global Constants
6 +=======================================================================*/
7 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ARP_PROCESS_PAYINFO';
8
9 /*=======================================================================+
10 | Package Global Constants
11 +=======================================================================*/
12 pg_debug varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
13
14 /*========================================================================
15 | Prototype Declarations Procedures
16 *=======================================================================*/
17 --
18 --
19 --
20 --
21 --
22
23 /*========================================================================
24 | Procedure copy_payment_ext_id()
25 |
26 | DESCRIPTION
27 | Process Invoices from AutoInvoice batch
28 |
29 | PSEUDO CODE/LOGIC
30 |
31 | PARAMETERS
32 |
33 |
34 | RETURNS
35 | nothing
36 |
37 | KNOWN ISSUES
38 |
39 |
40 |
41 | NOTES
42 |
43 |
44 |
45 | MODIFICATION HISTORY
46 | Date Author Description of Changes
47 | 25-Aug-2005 Ramakant Alat Created
48 | 14-MAY-2008 M Raymond 7039838 - FT performance tuning
49 |
50 *=======================================================================*/
51 PROCEDURE copy_payment_ext_id(p_error_count IN OUT NOCOPY NUMBER) AS
52
53 --
54 -- Get all invoices / debit memos which need payment processing
55 --
56 CURSOR c01 IS
57 SELECT
58 inv.trx_number,
59 inv.customer_trx_id customer_trx_id,
60 cus.party_id,
61 inv.paying_customer_id,
62 inv.paying_site_use_id,
63 inv.org_id
64 FROM
65 ra_customer_trx inv, hz_cust_accounts cus
66 WHERE
67 inv.request_id = fnd_global.conc_request_id
68 AND inv.payment_attributes IS NOT NULL
69 AND inv.paying_customer_id = cus.cust_account_id
70 AND NOT EXISTS (
71 SELECT /*+ leading(L) use_nl_with_index(E, RA_INTERFACE_ERRORS_N1) */ 1
72 FROM ra_customer_trx_lines l, ra_interface_errors e
73 WHERE l.customer_trx_id = inv.customer_trx_id
74 AND l.customer_trx_line_id = e.interface_line_id
75 AND l.request_id = FND_GLOBAL.CONC_REQUEST_ID); -- 7039838
76
77
78 l_ext_entity_tab IBY_FNDCPT_COMMON_PUB.Id_tbl_type;
79 l_msg RA_INTERFACE_ERRORS.MESSAGE_TEXT%TYPE;
80 l_payer IBY_FNDCPT_COMMON_PUB.PayerContext_rec_type;
81 l_trxn_attribs IBY_FNDCPT_TRXN_PUB.TrxnExtension_rec_type;
82 l_result IBY_FNDCPT_COMMON_PUB.Result_rec_type;
83
84 l_extension_id NUMBER;
85
86
87 l_return_status VARCHAR2(100);
88 l_msg_count NUMBER:=0;
89 l_msg_data VARCHAR2(20000):= NULL;
90
91 BEGIN
92 --
93 IF PG_DEBUG in ('Y', 'C') THEN
94 arp_standard.debug('arp_process_payinfo.copy_payment_ext_id()+ ');
95 arp_standard.debug('Req Id : [' || arp_global.request_id || ']');
96 arp_standard.debug('FND Req Id : [' || fnd_global.conc_request_id || ']');
97 END IF;
98 --
99 l_payer.Org_Type := null;
100 l_payer.Org_id := null;
101 l_trxn_attribs.Originating_Application_Id := 222; --- Receivables
102 p_error_count := 0;
103 --
104 -- Check if there are any Invoices to process in the list
105 --
106 <<inv_loop>>
107 FOR c01_rec IN c01 LOOP
108 --
109 IF PG_DEBUG in ('Y', 'C') THEN
110 arp_standard.debug('INV Customer Trx ID [' || c01_rec.customer_trx_id || ']');
111 END IF;
112 --
113 -- Get Payment extension entity IDs for given transaction
114 --
115 SELECT
116 DISTINCT line.payment_trxn_extension_id
117 BULK COLLECT INTO
118 l_ext_entity_tab
119 FROM
120 ra_customer_trx_lines line
121 WHERE
122 line.customer_trx_id = c01_rec.customer_trx_id
123 AND line.payment_trxn_extension_id IS NOT NULL -- 9274573
124 AND line.request_id = FND_GLOBAL.CONC_REQUEST_ID; -- 7039838
125
126 --
127 -- Copy Extension entity
128 --
129 IF PG_DEBUG in ('Y', 'C') THEN
130 arp_standard.debug('copying Transaction Extension....');
131 FOR i IN l_ext_entity_tab.FIRST..l_ext_entity_tab.LAST LOOP
132 arp_standard.debug('[' || i || '] :<' || l_ext_entity_tab(i)|| '>');
133 END LOOP;
134 END IF;
135 --
136 l_payer.Payment_Function := 'CUSTOMER_PAYMENT';
137 l_payer.Party_Id := c01_rec.party_id;
138 l_payer.Cust_Account_Id := c01_rec.paying_customer_id;
139 l_trxn_attribs.order_id := c01_rec.trx_number;
140 l_trxn_attribs.trxn_ref_number1 := 'TRANSACTION';
141 l_trxn_attribs.trxn_ref_number2 := c01_rec.customer_trx_id;
142
143 --
144 IF PG_DEBUG in ('Y', 'C') THEN
145 arp_standard.debug('l_payer.payment_function :<' ||
146 l_payer.payment_function || '>');
147 arp_standard.debug('l_payer.Party_Id :<' || l_payer.Party_Id || '>');
148 arp_standard.debug('l_payer.Org_Type :<' || l_payer.Org_Type || '>');
149 arp_standard.debug('l_payer.Org_id :<' || l_payer.Org_id || '>');
150 arp_standard.debug('l_payer.Cust_Account_Id :<' ||
151 l_payer.Cust_Account_Id || '>');
152 arp_standard.debug('l_trxn_attribs.Originating_Application_Id :<'
153 || l_trxn_attribs.Originating_Application_Id || '>');
154 arp_standard.debug('l_trxn_attribs.Instrument_Security_Code :<'
155 || l_trxn_attribs.Instrument_Security_Code || '>');
156 arp_standard.debug('l_trxn_attribs.order_id :<'
157 || l_trxn_attribs.order_id || '>');
158 END IF;
159 --
160 IBY_FNDCPT_TRXN_PUB.Copy_Transaction_Extension
161 (
162 p_api_version =>1.0,
163 p_init_msg_list =>FND_API.G_TRUE,
164 p_commit =>FND_API.G_FALSE,
165 x_return_status =>l_return_status,
166 x_msg_count =>l_msg_count,
167 x_msg_data =>l_msg_data,
168 p_payer =>l_payer,
169 p_payer_equivalency =>IBY_FNDCPT_COMMON_PUB.G_PAYER_EQUIV_DOWNWARD,
170 p_entities =>l_ext_entity_tab,
171 p_trxn_attribs =>l_trxn_attribs,
172 x_entity_id =>l_extension_id,
173 x_response =>l_result
174 );
175
176 --
177 -- Print out variables
178 --
179 IF PG_DEBUG in ('Y', 'C') THEN
180 --
181 arp_standard.debug('x_return_status :<' || l_return_status || '>');
182 arp_standard.debug('x_entity_id :<' || l_extension_id || '>');
183 arp_standard.debug('x_msg_count :<' || l_msg_count || '>');
184 --
185 END IF;
186
187 <<msg_loop>>
188 FOR i IN 1..l_msg_count LOOP
189 --
190 l_msg := SUBSTR(fnd_msg_pub.get(p_msg_index => i,
191 p_encoded => FND_API.G_FALSE),1,150);
192 --
193 IF PG_DEBUG in ('Y', 'C') THEN
194 arp_standard.debug('x_msg #' || TO_CHAR(i) || ' = <' ||
195 l_msg || '>');
196 END IF;
197 --
198 -- If unable to copy ext entities then insert into
199 -- AutoInvoice errors table to reject transation
200 --
201 IF l_return_status <> 'S' THEN
202 --
203 IF PG_DEBUG in ('Y', 'C') THEN
204 arp_standard.debug('Inserting into errors...');
205 END IF;
206 --
207 INSERT INTO ra_interface_errors
208 (
209 org_id,
210 interface_line_id,
211 message_text,
212 invalid_value
213 )
214 SELECT
215 org_id,
216 customer_trx_line_id,
217 l_msg,
218 payment_trxn_extension_id
219 FROM
220 ra_customer_trx_lines lines
221 WHERE
222 lines.customer_trx_id = c01_rec.customer_trx_id ;
223
224 p_error_count := SQL%ROWCOUNT;
225
226 --
227 END IF;
228 END LOOP msg_loop;
229 --
230 -- If tranaction extension entity was copied
231 -- then update the transaction header with it.
232 --
233 IF l_return_status = 'S' THEN
234 --
235 UPDATE
236 ra_customer_trx
237 SET
238 payment_trxn_extension_id = l_extension_id
239 WHERE
240 customer_trx_id = c01_rec.customer_trx_id;
241 --
242 END IF;
243 --
244 --
245 --
246 END LOOP inv_loop;
247 --
248 IF PG_DEBUG in ('Y', 'C') THEN
249 arp_standard.debug('arp_process_payinfo.copy_payment_ext_id()- ');
250 END IF;
251 EXCEPTION
252 WHEN OTHERS THEN
253 arp_standard.debug('EXCEPTION : arp_process_payinfo.copy_payment_ext_id ' ||': ' || SQLERRM(SQLCODE));
254 RAISE;
255 END copy_payment_ext_id;
256
257 /*========================================================================
258 | Procedure default_payment_attributes()
259 |
260 | DESCRIPTION
261 | This procedure deaults the payment_attributes for transaction
262 | grouping
263 |
264 | PSEUDO CODE/LOGIC
265 |
266 | PARAMETERS
267 |
268 | RETURNS
269 | nothing
270 |
271 | KNOWN ISSUES
272 |
273 |
274 |
275 | NOTES
276 |
277 |
278 |
279 | MODIFICATION HISTORY
280 | Date Author Description of Changes
281 | 28-Aug-2005 Ramakant Alat Created
282 |
283 *=======================================================================*/
284 PROCEDURE default_payment_attributes AS
285
286
287 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
288 l_msg_count NUMBER;
289 l_msg_data VARCHAR2(2000);
290 BEGIN
291 IF PG_DEBUG in ('Y', 'C') THEN
292 arp_standard.debug('arp_process_payinfo.default_payment_attributes()+ ');
293 arp_standard.debug('Req Id : [' || arp_global.request_id || ']');
294 arp_standard.debug('FND Req Id : [' || fnd_global.conc_request_id || ']');
295 END IF;
296 --
297 -- Update transaction header with payment attributes for grouping
298 --
299 /* 7039838 - replaced hint for FT performance tuning initialitive */
300
301 UPDATE ra_interface_lines il
302 SET payment_attributes =
303 (SELECT
304 CASE
305 WHEN authorized_flag = 'Y' THEN
306 CASE
307 WHEN SUM(decode(auth.settle_req_auth_flag, 'Y', 1, 0)) > 0 THEN
308 'PERSISTENT-AUTH~' || ext.instr_assignment_id || '~' || ext.trxn_extension_id
309 ELSE
310 'NON-PERSISTENT-AUTH~' || ext.instr_assignment_id || '~'
311 END
312 ELSE
313 'NO-AUTH~' || ext.instr_assignment_id || '~'
314 END
315 FROM iby_fndcpt_tx_extensions ext,
316 (SELECT /*+ leading(op) use_nl(summ,seft,ueft) */
317 op.trxn_extension_id,
318 decode(decode(summ.status, 0, 'Y', 100, 'Y', NULL), 'N', 'Y') authorized_flag,
319 nvl(seft.settle_require_vrfy_flag, 'N') settle_req_auth_flag
320 FROM iby_trxn_summaries_all summ,
321 iby_fndcpt_tx_operations op,
322 iby_fndcpt_sys_eft_pf_b seft,
323 iby_fndcpt_user_eft_pf_b ueft
324 WHERE(summ.transactionid = op.transactionid)
325 AND(reqtype = 'ORAPMTREQ')
326 AND(trxntypeid IN(2, 3, 20))
327 AND(decode(instrtype, 'BANKACCOUNT', summ.process_profile_code, NULL) = ueft.user_eft_profile_code(+))
328 AND(ueft.sys_eft_profile_code = seft.sys_eft_profile_code(+)))
329 auth,
330 fnd_application a
331 WHERE ext.trxn_extension_id = il.payment_trxn_extension_id
332 AND ext.trxn_extension_id = auth.trxn_extension_id(+)
333 AND auth.settle_req_auth_flag(+) = 'Y'
334 AND ext.origin_application_id = a.application_id
335 GROUP BY auth.authorized_flag,
336 ext.instr_assignment_id,
337 ext.trxn_extension_id)
338 WHERE request_id = fnd_global.conc_request_id
339 AND payment_trxn_extension_id IS NOT NULL;
340 --
341 IF PG_DEBUG in ('Y', 'C') THEN
342 arp_standard.debug('arp_process_payinfo.default_payment_attributes()- ');
343 END IF;
344 EXCEPTION
345 WHEN OTHERS THEN
346 arp_standard.debug('EXCEPTION : arp_process_payinfo.default_payment_attributes : ' || SQLERRM(SQLCODE));
347 RAISE;
348 END default_payment_attributes;
349 /*========================================================================
350 | Procedure validate_payment_ext_id()
351 |
352 | DESCRIPTION
353 | This procedure validate ext id
354 | grouping
355 |
356 | PSEUDO CODE/LOGIC
357 |
358 | PARAMETERS
359 |
360 | RETURNS
361 | nothing
362 |
363 | KNOWN ISSUES
364 |
365 |
366 |
367 | NOTES
368 |
369 |
370 |
371 | MODIFICATION HISTORY
372 | Date Author Description of Changes
373 | 28-Aug-2005 Ramakant Alat Created
374 |
375 *=======================================================================*/
376 PROCEDURE validate_payment_ext_id AS
377
378
379 l_return_status VARCHAR2(1) := FND_API.G_RET_STS_SUCCESS;
380 l_msg_count NUMBER;
381 l_msg_data VARCHAR2(2000);
382 BEGIN
383 IF PG_DEBUG in ('Y', 'C') THEN
384 arp_standard.debug('arp_process_payinfo.validate_payment_ext_id()+ ');
385 arp_standard.debug('Req Id : [' || arp_global.request_id || ']');
386 arp_standard.debug('FND Req Id : [' || fnd_global.conc_request_id || ']');
387 END IF;
388 --
389 -- Validate payment_trxn_ext_id
390 --
391 -- 7039838 - perf tuning
392 INSERT INTO RA_INTERFACE_ERRORS
393 (INTERFACE_LINE_ID,
394 MESSAGE_TEXT,
395 INVALID_VALUE,
396 ORG_ID)
397 SELECT /*+ cardinality(L,10) leading(L) use_nl(rm,rc, P,X) */
398 L.INTERFACE_LINE_ID,
399 CASE
400 WHEN l.receipt_method_id IS NULL THEN
401 arp_standard.fnd_message('AR_REC_MTHD_REQD_FOR_EXT_ID')
402 WHEN rc.creation_method_code NOT IN ('AUTOMATIC', 'BR') THEN
403 arp_standard.fnd_message('AR_RECEIPT_METHOD_AUTO_OR_BR')
404 WHEN NVL(rm.payment_channel_code, 'BILLS_RECEIVABLE')
405 <> p.payment_channel_code THEN
406 arp_standard.fnd_message('AR_PMT_CHNL_MISMTCH_REC_MTHD')
407 ELSE
408 'VALIDATE_EXT_ID:NO_MSG'
409 END,
410 L.PAYMENT_TRXN_EXTENSION_ID,
411 L.ORG_ID
412 FROM RA_INTERFACE_LINES_GT L,
413 AR_RECEIPT_METHODS rm,
414 AR_RECEIPT_CLASSES rc,
415 IBY_FNDCPT_PMT_CHNNLS_B P,
416 IBY_FNDCPT_TX_EXTENSIONS X
417 WHERE L.REQUEST_ID = fnd_global.conc_request_id
418 AND L.CUSTOMER_TRX_ID IS NOT NULL
419 AND NVL(L.INTERFACE_STATUS, '~') <> 'P'
420 AND l.receipt_method_id = rm.receipt_method_id (+)
421 AND rm.receipt_class_id = rc.receipt_class_id (+)
422 AND L.PAYMENT_TRXN_EXTENSION_ID IS NOT NULL
423 AND (l.receipt_method_id IS NULL OR rc.creation_method_code NOT IN ('AUTOMATIC', 'BR') OR
424 NVL(rm.payment_channel_code, 'BILLS_RECEIVABLE') <> p.payment_channel_code )
425 AND l.PAYMENT_TRXN_EXTENSION_ID = X.TRXN_EXTENSION_ID
426 AND x.payment_channel_code = p.payment_channel_code;
427 IF PG_DEBUG in ('Y', 'C') THEN
428 arp_standard.debug('arp_process_payinfo.validate_payment_ext_id()- ');
429 END IF;
430 EXCEPTION
431 WHEN OTHERS THEN
432 arp_standard.debug('EXCEPTION : arp_process_payinfo.validate_payment_ext_id : ' || SQLERRM(SQLCODE));
433 RAISE;
434 END validate_payment_ext_id;
435
436
437 END arp_process_payinfo;