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