1 PACKAGE BODY OE_PREPAYMENT_UTIL AS
2 /* $Header: OEXUPPYB.pls 120.15 2010/10/27 17:49:43 amimukhe ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_PrePayment_UTIL';
5
6 PROCEDURE Is_Prepaid(p_application_id IN NUMBER,
7 p_entity_short_name in VARCHAR2,
8 p_validation_entity_short_name in VARCHAR2,
9 p_validation_tmplt_short_name in VARCHAR2,
10 p_record_set_tmplt_short_name in VARCHAR2,
11 p_scope in VARCHAR2,
12 p_result OUT NOCOPY NUMBER ) IS
13
14
15 l_header_id NUMBER ;
16 l_prepaid NUMBER := 0;
17 l_prepayment_flag VARCHAR2(1);
18 l_payment_type_code varchar2(30);
19 l_payment_term_id NUMBER;
20 --
21 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
22 --
23 BEGIN
24 IF l_debug_level > 0 THEN
25 oe_debug_pub.add( 'ENTERING OE_PREPAYMENT_UTIL.IS_PREPAID' ) ;
26 END IF;
27 IF OE_CODE_CONTROL.Get_Code_Release_Level < '110508' THEN
28 IF l_debug_level > 0 THEN
29 oe_debug_pub.add( 'OEXUPPY: BELOW PACKH , PREPAYMENTS NOT ENABLED' ) ;
30 END IF;
31 p_result := 0;
32 RETURN;
33 END IF;
34 --serla begin
35 IF NOT IS_MULTIPLE_PAYMENTS_ENABLED THEN
36 --serla end
37 IF p_validation_entity_short_name = 'HEADER' THEN
38 l_header_id := oe_header_security.g_record.header_id;
39 l_payment_type_code := oe_header_security.g_record.payment_type_code;
40 l_payment_term_id := oe_header_security.g_record.payment_term_id;
41 IF l_debug_level > 0 THEN
42 oe_debug_pub.add( 'L_HEADER_ID: '||L_HEADER_ID ||' :L_PAYMENT_TYPE_CODE='||L_PAYMENT_TYPE_CODE||':L_PAYMENT_TERM_ID='||L_PAYMENT_TERM_ID ) ;
43 END IF;
44 ELSIF p_validation_entity_short_name = 'LINE' THEN
45 l_header_id := oe_line_security.g_record.header_id;
46 END IF;
47
48 IF l_header_id IS NULL OR
49 l_header_id = FND_API.G_MISS_NUM
50 THEN
51 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
52 p_result := 0;
53 END IF;
54
55 IF l_debug_level > 0 THEN
56 oe_debug_pub.add( 'P_VALIDATION_ENTITY_SHORT_NAME: '||P_VALIDATION_ENTITY_SHORT_NAME ) ;
57 END IF;
58 IF p_validation_entity_short_name = 'HEADER' THEN
59 BEGIN
60 SELECT 1
61 INTO l_prepaid
62 FROM oe_payments
63 WHERE header_id = l_header_id
64 AND payment_type_code = 'CREDIT_CARD';
65 EXCEPTION
66 WHEN NO_DATA_FOUND THEN
67 l_prepaid := 0;
68 END;
69 IF l_debug_level > 0 THEN
70 oe_debug_pub.add( 'L_PREPAID: '||L_PREPAID ) ;
71 END IF;
72
73 IF l_prepaid = 1 THEN
74 p_result := 1;
75 ELSE
76 p_result := 0;
77 END IF;
78 ELSIF p_validation_entity_short_name = 'LINE' THEN --validation entity <> 'HEADER'
79 IF Is_Prepaid_order(l_header_id) = 'Y' THEN
80 BEGIN
81 SELECT 1
82 INTO l_prepaid
83 FROM oe_payments
84 WHERE header_id = l_header_id
85 AND payment_type_code = 'CREDIT_CARD';
86 EXCEPTION
87 WHEN NO_DATA_FOUND THEN
88 l_prepaid := 0;
89 END;
90
91 IF l_debug_level > 0 THEN
92 oe_debug_pub.add( 'L_PREPAID: '||L_PREPAID ) ;
93 END IF;
94
95 IF l_prepaid = 1 THEN
96 p_result := 1;
97 ELSE
98 p_result := 0;
99 END IF;
100 ELSE -- Is_Prepaid_Order = 'N'
101 p_result := 0;
102 END IF;
103 END IF;
104 --serla begin
105 ELSE -- multiple payments enabled
106
107 IF p_validation_entity_short_name = 'HEADER' THEN
108 l_header_id := oe_header_security.g_record.header_id;
109 ELSIF p_validation_entity_short_name = 'LINE' THEN
110 l_header_id := oe_line_security.g_record.header_id;
111 END IF;
112
113 IF l_debug_level > 0 THEN
114 oe_debug_pub.add( 'P_VALIDATION_ENTITY_SHORT_NAME: '||P_VALIDATION_ENTITY_SHORT_NAME||':header_id:'||l_header_id ) ;
115 END IF;
116
117 -- 3726282 Added the outer If loop alone
118 IF p_validation_entity_short_name in ('HEADER','LINE') THEN
119 IF l_header_id IS NULL OR
120 l_header_id = FND_API.G_MISS_NUM
121 THEN
122 OE_MSG_PUB.Update_Msg_Context(p_attribute_code => null);
123 p_result := 0;
124 ELSE
125 p_result := 0;
126 -- commented out the following code, as the payment shown
127 -- on order header is always invoice payment when multiple
128 -- payments is enabled, we should allow update. and there
129 -- would not exist any line payment if prepayment exists.
130 /**
131 BEGIN
132 SELECT 1
133 INTO p_result
134 FROM oe_payments
135 WHERE header_id = l_header_id
136 AND payment_set_id is not null
137 AND rownum=1;
138 EXCEPTION
139 WHEN NO_DATA_FOUND THEN
140 p_result := 0;
141 END;
142 ****/
143 END IF;
144 END IF;
145
146 -- 3726282 Start
147 IF p_validation_entity_short_name = 'HEADER_PAYMENT' THEN
148 IF(oe_header_payment_security.g_record.payment_set_id is NOT NULL) THEN
149 p_result := 1;
150 ELSE
151 p_result := 0;
152 END IF;
153 ELSIF p_validation_entity_short_name = 'LINE_PAYMENT' THEN
154 IF ( oe_line_payment_security.g_record.payment_set_id is not NULL) THEN
155 p_result := 1;
156 ELSE
157 p_result := 0;
158 END IF;
159 END IF;
160 -- 3726282 End
161 END IF;
162 --serla end
163 IF l_debug_level > 0 THEN
164 oe_debug_pub.add( 'EXITING OE_PREPAYMENT_UTIL.IS_PREPAID WITH RESULT: '||P_RESULT ) ;
165 END IF;
166 END Is_prepaid;
167
168 PROCEDURE Get_PrePayment_Info
169 ( p_header_id IN NUMBER
170 , x_payment_set_id OUT NOCOPY NUMBER
171
172 , x_prepaid_amount OUT NOCOPY NUMBER
173
174 ) IS
175 l_payment_type_code VARCHAR2(30);
176 l_payment_term_id NUMBER;
177 l_prepayment_flag VARCHAR2(1);
178 --
179 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
180 --
181 BEGIN
182 IF l_debug_level > 0 THEN
183 oe_debug_pub.add( 'ENTERING OE_PAYMENT_UTIL.GET_PREPAYMENT_INFO' ) ;
184 END IF;
185
186 --serla begin
187 IF IS_MULTIPLE_PAYMENTS_ENABLED THEN
188 BEGIN
189 SELECT max(payment_set_id)
190 , sum(nvl(prepaid_amount, 0))
191 INTO x_payment_set_id
192 ,x_prepaid_amount
193 FROM oe_payments
194 WHERE header_id = p_header_id
195 AND payment_set_id IS NOT NULL;
196 EXCEPTION
197 WHEN NO_DATA_FOUND THEN
198 IF l_debug_level > 0 THEN
199 oe_debug_pub.add( 'IN NODATA FOUND - OE_PAYMENT_UTIL.GET_PREPAYMENT_INFO' ) ;
200 END IF;
201 x_payment_set_id := NULL;
202 x_prepaid_amount := NULL;
203 END;
204 ELSE
205 --serla end
206 IF Is_Prepaid_Order(p_header_id) = 'N' THEN
207 x_payment_set_id := NULL;
208 x_prepaid_amount := NULL;
209 IF l_debug_level > 0 THEN
210 oe_debug_pub.add( 'NOT PREPAID TERMS , RETURNING NULL PAYMENT_SET_ID' ) ;
211 END IF;
212 ELSE
213 BEGIN
214 SELECT payment_set_id
215 ,prepaid_amount
216 INTO x_payment_set_id
217 ,x_prepaid_amount
218 FROM oe_payments
219 WHERE header_id = p_header_id
220 AND payment_type_code = 'CREDIT_CARD';
221 IF l_debug_level > 0 THEN
222 oe_debug_pub.add( 'PREPAID ORDER. X_PAYMENT_SET_ID: '||X_PAYMENT_SET_ID||' , X_PREPAID_AMOUNT: '||X_PREPAID_AMOUNT ) ;
223 END IF;
224 EXCEPTION
225 WHEN NO_DATA_FOUND THEN
226 IF l_debug_level > 0 THEN
227 oe_debug_pub.add( 'IN NODATA FOUND - OE_PAYMENT_UTIL.GET_PREPAYMENT_INFO' ) ;
228 END IF;
229 x_payment_set_id := NULL;
230 x_prepaid_amount := NULL;
231 WHEN Too_many_rows THEN
232 IF l_debug_level > 0 THEN
233 oe_debug_pub.add( 'IN TOO MANY ROWS - OE_PAYMENT_UTIL.GET_PREPAYMENT_INFO' ) ;
234 END IF;
235 x_payment_set_id := NULL;
236 x_prepaid_amount := NULL;
237 END;
238 END IF;
239 --serla begin
240 END IF;
241 --serla end
242
243 IF l_debug_level > 0 THEN
244 oe_debug_pub.add( 'EXITING OE_PAYMENT_UTIL.GET_PREPAYMENT_INFO - PAYMENT_SET_ID: '|| X_PAYMENT_SET_ID||' PREPAID_AMOUNT : '||X_PREPAID_AMOUNT ) ;
245 END IF;
246
247 EXCEPTION
248 WHEN OTHERS THEN
249 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
250 THEN
251 FND_MSG_PUB.Add_Exc_Msg
252 ( G_PKG_NAME
253 ,'OE_PAYMENTS_UTIL.Get_PrePayment_Info'
254 );
255 END IF;
256
257 END Get_PrePayment_Info;
258
259 PROCEDURE PendProcessPayments_Conc_Prog
260 ( errbuf OUT NOCOPY VARCHAR2
261
262 ,retcode OUT NOCOPY NUMBER
263 ,p_operating_unit IN NUMBER --MOAC Changes
264 ,p_ppp_hold IN VARCHAR2
265 ,p_epay_failure_hold IN VARCHAR2
266 ,p_epay_server_failure_hold IN VARCHAR2
267 ,p_payment_authorization_hold IN VARCHAR2
268 ,p_order_type_id IN NUMBER
269 ,p_order_number_from IN NUMBER
270 ,p_order_number_to IN NUMBER
271 ,p_customer_number_from IN VARCHAR2
272 ,p_customer_number_to IN VARCHAR2
273 ,p_debug_level IN NUMBER
274 ,p_customer_class_code IN VARCHAR2
275 ,p_credit_card_number IN VARCHAR2
276 ,p_credit_card_type IN VARCHAR2
277 ,p_bill_to_org_id IN NUMBER
278 ,p_booked_date_since IN VARCHAR2
279 )
280 IS
281 l_msg_count NUMBER := 0 ;
282 l_msg_data VARCHAR2(2000):= NULL ;
283 l_message_text VARCHAR2(2000);
284 l_return_status VARCHAR2(30) := NULL ;
285 l_orig_sys_document_ref VARCHAR2(50);
286 l_source_document_id NUMBER;
287 l_order_source_id NUMBER;
288 l_change_sequence VARCHAR2(50);
289 l_source_document_type_id NUMBER;
290
291 l_order_type_id NUMBER := p_order_type_id;
292 l_order_number_from NUMBER := p_order_number_from;
293 l_order_number_to NUMBER := p_order_number_to;
294 l_customer_number_from VARCHAR2(30) := p_customer_number_from;
295 l_customer_number_to VARCHAR2(30) := p_customer_number_to;
296 l_customer_class_code VARCHAR2(30) := p_customer_class_code;
297
298 l_count_header NUMBER := 0;
299 l_count_header_warning NUMBER := 0;
300 l_count_header_success NUMBER := 0;
301 l_count_header_failure NUMBER := 0;
302
303 l_header_id NUMBER;
304 l_request_id NUMBER;
305
306 l_debug_file VARCHAR2(30);
307 l_filename VARCHAR2(100);
308 l_database VARCHAR2(100);
309 l_hold1 NUMBER:= 13;
310 l_hold2 NUMBER:= 14;
311 l_hold3 NUMBER:= 15;
312 l_hold4 NUMBER:= 16; -- credit card authorization hold
313 l_booked_date_since DATE;
314 l_hold_id NUMBER;
315
316 p_process_prepayment VARCHAR2(1) := 'N';
317 p_process_authorization VARCHAR2(1) := 'N';
318
319 --bug4689411
320 l_old_org_id NUMBER;
321
322 -- bug 4967981
323 l_hold_exists VARCHAR2(1) := NULL ;
324
325 -- We may need to have one more cursor to avoid joining to ra_customers
326 -- when all the parameters related to customer (customer_number from/to,
327 -- customer class) are null
328 CURSOR ppp_order_hold_cur IS
329 /****
330 SELECT * MOAC_SQL_CHANGE * oh.header_id
331 ,oh.orig_sys_document_ref
332 ,oh.source_document_id
333 ,oh.order_source_id
334 ,oh.change_sequence
335 ,oh.source_document_type_id
336 FROM oe_order_headers oh
337 ,oe_order_holds_all hd --moac
338 ,oe_hold_sources_all hs --moac
339 ,iby_trxn_extensions_v ite -- ccencryption
340 ,oe_payments op
341 WHERE oh.header_id= hd.header_id
342 AND hd.hold_source_id = hs.hold_source_id
343 AND hs.hold_id in (l_hold1, l_hold2, l_hold3, l_hold4)
344 * (SELECT hold_id -- replace the sql with hardcoded hold_id once it is seeded: Also based on what holds should be processed
345 FROM oe_hold_definitions
346 WHERE type_code='EPAYMENT') *
347 AND hs.hold_entity_code = 'O'
348 AND hs.released_flag = 'N'
349 AND oh.order_type_id = NVL(p_order_type_id, oh.order_type_id)
350 AND oh.org_id = hs.org_id --moac
351 AND hs.org_id = hd.org_id --moac
352 AND oh.sold_to_org_id IN (SELECT cust_account_id
353 FROM hz_cust_accounts
354 WHERE account_number BETWEEN NVL(p_customer_number_from, account_number)
355 AND NVL(p_customer_number_to, account_number)
356 AND nvl(CUSTOMER_CLASS_CODE, 'XXX') = NVL(p_customer_class_code, nvl(CUSTOMER_CLASS_CODE, 'XXX')))
357 AND oh.order_number BETWEEN NVL(p_order_number_from, oh.order_number)
358 AND NVL(p_order_number_to, oh.order_number)
359 AND oh.payment_type_code = 'CREDIT_CARD'
360 -- AND oh.credit_card_number = NVL(p_credit_card_number, oh.credit_card_number)
361 -- AND nvl(oh.credit_card_code, 'XXX') = NVL(p_credit_card_type, nvl(oh.credit_card_code, 'XXX'))
362 AND oh.header_id = ite.order_id
363 AND ite.cc_number_hash1 = DECODE(p_credit_card_number, null, ite.cc_number_hash1, iby_fndcpt_setup_pub.get_hash(p_Credit_Card_Number, 'F'))
364 AND ite.cc_number_hash2 = DECODE(p_credit_card_number, null, ite.cc_number_hash2, iby_fndcpt_setup_pub.get_hash(p_credit_card_number, 'T'))
365 AND nvl(ite.card_issuer_code, 'XXX') = nvl(p_credit_card_type, nvl(ite.card_issuer_code, 'XXX'))
366 AND oh.invoice_to_org_id = NVL(p_bill_to_org_id, oh.invoice_to_org_id)
367 AND oh.booked_date >= NVL(l_booked_date_since, oh.booked_date)
368 AND oh.order_category_code <> 'RETURN'
369 AND oh.header_id = op.header_id
370 AND op.trxn_extension_id = ite.trxn_extension_id
371 -- orders on header level holds for multiple payments
372 UNION
373 ***/
374
375 (SELECT distinct /* MOAC_SQL_CHANGE */ oh.header_id
376 ,oh.orig_sys_document_ref
377 ,oh.source_document_id
378 ,oh.order_source_id
379 ,oh.change_sequence
380 ,oh.source_document_type_id
381 ,oh.org_id --bug4689411
382 FROM oe_order_headers oh
383 ,oe_order_holds_all hd --moac
384 ,oe_hold_sources_all hs --moac
385 ,oe_payments op
386 -- iby_trxn_extensions_v ite -- ccencryption
387 ,IBY_FNDCPT_TX_EXTENSIONS x
388 ,IBY_PMT_INSTR_USES_ALL u
389 ,IBY_CREDITCARD c
390 WHERE oh.header_id= hd.header_id
391 AND hd.hold_source_id = hs.hold_source_id
392 AND hs.hold_id in (l_hold1, l_hold2, l_hold3, l_hold4)
393 /* (SELECT hold_id -- replace the sql with hardcoded hold_id once it is seeded: Also based on what holds should be processed
394 FROM oe_hold_definitions
395 WHERE type_code='EPAYMENT') */
396 AND hs.hold_entity_code = 'O'
397 AND hs.released_flag = 'N'
398 AND oh.order_type_id = NVL(p_order_type_id, oh.order_type_id)
399 AND oh.org_id = hs.org_id -- moac
400 AND hs.org_id = hd.org_id --moac
401 AND oh.sold_to_org_id IN (SELECT cust_account_id
402 FROM hz_cust_accounts
403 WHERE account_number BETWEEN NVL(p_customer_number_from, account_number)
404 AND NVL(p_customer_number_to, account_number)
405 AND nvl(CUSTOMER_CLASS_CODE, 'XXX') = NVL(p_customer_class_code, nvl(CUSTOMER_CLASS_CODE, 'XXX')))
406 AND oh.order_number BETWEEN NVL(p_order_number_from, oh.order_number)
407 AND NVL(p_order_number_to, oh.order_number)
408 AND oh.header_id = op.header_id
409 AND to_char(op.header_id) = x.order_id --Bug#9696998
410 AND c.cc_number_hash1 = DECODE(p_credit_card_number, null, c.cc_number_hash1, iby_fndcpt_setup_pub.get_hash(p_Credit_Card_Number, 'F'))
411 AND c.cc_number_hash2 = DECODE(p_credit_card_number, null, c.cc_number_hash2, iby_fndcpt_setup_pub.get_hash(p_credit_card_number, 'T'))
412 AND nvl(c.card_issuer_code, 'XXX') = nvl(p_credit_card_type, nvl(c.card_issuer_code, 'XXX'))
413 AND op.trxn_extension_id = x.trxn_extension_id
414 AND x.instr_assignment_id = u.instrument_payment_use_id
415 AND u.instrument_id = c.instrid
416 AND op.payment_type_code = 'CREDIT_CARD'
417 AND oh.invoice_to_org_id = NVL(p_bill_to_org_id, oh.invoice_to_org_id)
418 AND oh.booked_date >= NVL(l_booked_date_since, oh.booked_date)
419 AND oh.order_category_code <> 'RETURN'
420
421 -- for CASH, CHECK payment types, no need to join with IBY table.
422 UNION
423 SELECT distinct /* MOAC_SQL_CHANGE */ oh.header_id
424 ,oh.orig_sys_document_ref
425 ,oh.source_document_id
426 ,oh.order_source_id
427 ,oh.change_sequence
428 ,oh.source_document_type_id
429 ,oh.org_id --bug4689411
430 FROM oe_order_headers oh
431 ,oe_order_holds_all hd --moac
432 ,oe_hold_sources_all hs --moac
433 ,oe_payments op
434 WHERE oh.header_id= hd.header_id
435 AND hd.hold_source_id = hs.hold_source_id
436 AND hs.hold_id in (l_hold1, l_hold2, l_hold3)
437 /* (SELECT hold_id -- replace the sql with hardcoded hold_id once it is seeded: Also based on what holds should be processed
438 FROM oe_hold_definitions
439 WHERE type_code='EPAYMENT') */
440 AND hs.hold_entity_code = 'O'
441 AND hs.released_flag = 'N'
442 AND oh.order_type_id = NVL(p_order_type_id, oh.order_type_id)
443 AND oh.org_id = hs.org_id --moac
444 AND hs.org_id = hd.org_id --moac
445 AND oh.sold_to_org_id IN (SELECT cust_account_id
446 FROM hz_cust_accounts
447 WHERE account_number BETWEEN NVL(p_customer_number_from, account_number)
448 AND NVL(p_customer_number_to, account_number)
449 AND nvl(CUSTOMER_CLASS_CODE, 'XXX') = NVL(p_customer_class_code, nvl(CUSTOMER_CLASS_CODE, 'XXX')))
450 AND oh.order_number BETWEEN NVL(p_order_number_from, oh.order_number)
451 AND NVL(p_order_number_to, oh.order_number)
452 AND op.payment_type_code IN('CASH', 'CHECK')
453 AND p_credit_card_number IS NULL
454 AND oh.invoice_to_org_id = NVL(p_bill_to_org_id, oh.invoice_to_org_id)
455 AND oh.booked_date >= NVL(l_booked_date_since, oh.booked_date)
456 AND oh.order_category_code <> 'RETURN'
457 AND oh.header_id = op.header_id
458
459 -- get all orders that have at least one line being on line level
460 -- authorization holds for multiple payments
461 UNION
462 SELECT distinct /* MOAC_SQL_CHANGE */ oh.header_id
463 ,oh.orig_sys_document_ref
464 ,oh.source_document_id
465 ,oh.order_source_id
466 ,oh.change_sequence
467 ,oh.source_document_type_id
468 ,oh.org_id --bug4689411
469 FROM oe_order_lines_all ol --moac
470 ,oe_order_headers oh
471 ,oe_order_holds_all hd --moac
472 ,oe_hold_sources_all hs --moac
473 ,oe_payments op
474 -- ,iby_trxn_extensions_v ite -- ccencryption
475 ,IBY_FNDCPT_TX_EXTENSIONS x
476 ,IBY_PMT_INSTR_USES_ALL u
477 ,IBY_CREDITCARD c
478 WHERE oh.header_id = ol.header_id
479 AND ol.line_id= hd.line_id
480 AND hd.hold_source_id = hs.hold_source_id
481 AND hs.hold_id in (l_hold1, l_hold2, l_hold3, l_hold4)
482 /* (SELECT hold_id -- replace the sql with hardcoded hold_id once it is seeded: Also based on what holds should be processed
483 FROM oe_hold_definitions
484 WHERE type_code='EPAYMENT') */
485 AND hs.hold_entity_code = 'O'
486 AND hs.released_flag = 'N'
487 AND oh.order_type_id = NVL(p_order_type_id, oh.order_type_id)
488 AND oh.org_id = hs.org_id --moac
489 AND hs.org_id = hd.org_id --moac
490 AND oh.sold_to_org_id IN (SELECT cust_account_id
491 FROM hz_cust_accounts
492 WHERE account_number BETWEEN NVL(p_customer_number_from, account_number)
493 AND NVL(p_customer_number_to, account_number)
494 AND nvl(CUSTOMER_CLASS_CODE, 'XXX') = NVL(p_customer_class_code, nvl(CUSTOMER_CLASS_CODE, 'XXX')))
495 AND oh.order_number BETWEEN NVL(p_order_number_from, oh.order_number)
496 AND NVL(p_order_number_to, oh.order_number)
497 AND ol.line_id = op.line_id
498 AND ol.header_id = op.header_id
499 AND op.payment_type_code = 'CREDIT_CARD'
500 AND to_char(ol.header_id) = x.order_id --Bug#9696998
501 AND to_char(ol.line_id) = x.trxn_ref_number1 --Bug#9696998 --order line_id
502 AND c.cc_number_hash1 = DECODE(p_credit_card_number, null, c.cc_number_hash1, iby_fndcpt_setup_pub.get_hash(p_Credit_Card_Number, 'F'))
503 AND c.cc_number_hash2 = DECODE(p_credit_card_number, null, c.cc_number_hash2, iby_fndcpt_setup_pub.get_hash(p_credit_card_number, 'T'))
504 AND nvl(c.card_issuer_code, 'XXX') = nvl(p_credit_card_type,
505 nvl(c.card_issuer_code, 'XXX'))
506 AND oh.invoice_to_org_id = NVL(p_bill_to_org_id, oh.invoice_to_org_id)
507 AND oh.booked_date >= NVL(l_booked_date_since, oh.booked_date)
508 AND oh.order_category_code <> 'RETURN'
509 AND op.trxn_extension_id = x.trxn_extension_id)
510 ORDER BY 7; --bug4689411 Using the column number to order by org_id. Please make sure that org_id is the 7th column when any changes are made to the select clause.
511
512
513 /* -----------------------------------------------------------
514 Messages cursor
515 -----------------------------------------------------------
516 */
517 CURSOR l_msg_cursor IS
518
519 SELECT /*+ INDEX (a,OE_PROCESSING_MSGS_N2)
520 USE_NL (a b) */
521 a.header_id
522 , a.order_source_id
523 , a.original_sys_document_ref
524 , a.source_document_id
525 , a.change_sequence
526 , a.source_document_type_id
527 , b.message_text
528 FROM oe_processing_msgs a, oe_processing_msgs_tl b
529 WHERE a.request_id = l_request_id
530 AND a.transaction_id = b.transaction_id
531 AND b.language = oe_globals.g_lang
532 ORDER BY a.order_source_id, a.original_sys_document_ref, a.header_id;
533
534 --
535 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
536 l_access_mode VARCHAR2(40); --R12 MOAC Changes
537 l_org_id NUMBER;
538 --
539 BEGIN
540
541 IF l_debug_level > 0 THEN
542 oe_debug_pub.add( 'ENTERING OE_PAYMENTS_UTIL.PENDPROCESSPAYMENTS_CONC_PROG' ) ;
543 oe_debug_pub.add('Operating unit passed -->'||p_operating_unit);
544 END IF;
545 --R12 MOAC Changes
546 --MOAC set policy context for single Org
547 IF p_operating_unit IS NOT NULL THEN
548 MO_GLOBAL.set_policy_context('S',p_operating_unit);
549 END IF;
550
551 IF l_debug_level >0 THEN
552 l_org_id := MO_GLOBAL.get_current_org_id;
553 l_access_mode := MO_GLOBAL.get_access_mode;
554 oe_debug_pub.add('Access Mode:'|| l_access_mode);
555 oe_debug_pub.add('Operating unit returned from mo_global-->'|| l_org_id);
556 END IF;
557 --R12 MOAC Changes
558
559 IF OE_CODE_CONTROL.Get_Code_Release_Level < '110508' THEN
560 IF l_debug_level > 0 THEN
561 oe_debug_pub.add( 'OEXUPPY: BELOW PACKH , PREPAYMENTS NOT ENABLED' ) ;
562 END IF;
563 fnd_file.put_line(FND_FILE.OUTPUT, 'Pack H or above must be installed to use this concurrent program');
564 RETURN;
565 END IF;
566 l_return_status := FND_API.G_RET_STS_SUCCESS;
567
568 IF p_ppp_hold = 'N' THEN
569 l_hold1 := 0 ;
570 END IF;
571 IF p_epay_failure_hold = 'N' THEN
572 l_hold2 := 0 ;
573 END IF;
574 IF p_epay_server_failure_hold = 'N' THEN
575 l_hold3 := 0 ;
576 END IF;
577 IF p_payment_authorization_hold = 'N' THEN
578 l_hold4 := 0 ;
579 END IF;
580
581 l_booked_date_since := fnd_date.canonical_to_date(p_booked_date_since);
582
583 /* -----------------------------------------------------------
584 Log Output file
585 -----------------------------------------------------------*/
586 fnd_file.put_line(FND_FILE.OUTPUT, 'Pending Process Payments Concurrent Program');
587 fnd_file.put_line(FND_FILE.OUTPUT, '');
588 fnd_file.put_line(FND_FILE.OUTPUT, 'Concurrent Program Parameters');
589 fnd_file.put_line(FND_FILE.OUTPUT, 'Operating Unit: '|| p_operating_unit); --MOAC Changes
590 fnd_file.put_line(FND_FILE.OUTPUT, 'PPP Hold Selected: '|| p_ppp_hold);
591 fnd_file.put_line(FND_FILE.OUTPUT, 'Epayment Failure Hold Selected: '|| p_epay_failure_hold);
592 fnd_file.put_line(FND_FILE.OUTPUT, 'Epayment Server Failure Hold Selected: '|| p_epay_server_failure_hold);
593 fnd_file.put_line(FND_FILE.OUTPUT, 'Payment Authorizatin Hold Selected: '|| p_payment_authorization_hold);
594 fnd_file.put_line(FND_FILE.OUTPUT, 'Order Type: '|| p_order_type_id);
595 fnd_file.put_line(FND_FILE.OUTPUT, 'Order Number From: '|| p_order_number_from);
596 fnd_file.put_line(FND_FILE.OUTPUT, 'Order Number To: '|| p_order_number_to);
597 fnd_file.put_line(FND_FILE.OUTPUT, 'Customer Number From: '|| p_customer_number_from);
598 fnd_file.put_line(FND_FILE.OUTPUT, 'Customer Number To: '|| p_customer_number_to);
599 fnd_file.put_line(FND_FILE.OUTPUT, 'Customer Class: '|| p_customer_class_code);
600 fnd_file.put_line(FND_FILE.OUTPUT, 'Credit Card Number: '|| p_credit_card_number);
601 fnd_file.put_line(FND_FILE.OUTPUT, 'Card Brand: '|| p_credit_card_type);
602 fnd_file.put_line(FND_FILE.OUTPUT, 'Bill To Org: '|| p_bill_to_org_id);
603 fnd_file.put_line(FND_FILE.OUTPUT, 'Booked Date Since: '|| p_booked_date_since);
604 fnd_file.put_line(FND_FILE.OUTPUT, '');
605
606 -----------------------------------------------------------
607 -- Setting Debug Mode and File
608 -----------------------------------------------------------
609
610 FND_FILE.Put_Line(FND_FILE.OUTPUT,'Debug Level: '||nvl(p_debug_level,1));
611 -- commenting out as debug mode will be set to 'CONC' automatically when
612 -- debug level is > 0 and the debug messages can be seen in the conc program log file
613 /*
614 IF nvl(p_debug_level, 1) > 0 THEN
615 l_filename := OE_DEBUG_PUB.set_debug_mode ('FILE');
616 FND_FILE.Put_Line(FND_FILE.OUTPUT,'Debug File: ' || l_filename);
617 FND_FILE.Put_Line(FND_FILE.OUTPUT, '');
618 END IF;
619
620 l_filename := OE_DEBUG_PUB.set_debug_mode ('CONC');
621 */
622 /* -----------------------------------------------------------
623 Get Concurrent Request Id
624 -----------------------------------------------------------
625 */
626 IF l_debug_level > 0 THEN
627 oe_debug_pub.add( 'BEFORE GETTING REQUEST ID' ) ;
628 END IF;
629
630 FND_PROFILE.Get('CONC_REQUEST_ID', l_request_id);
631
632 IF l_debug_level > 0 THEN
633 oe_debug_pub.add( 'REQUEST ID: '|| TO_CHAR ( L_REQUEST_ID ) ) ;
634 END IF;
635 fnd_file.put_line(FND_FILE.OUTPUT, 'Request Id: '|| to_char(l_request_id));
636 -- fnd_file.put_line(FND_FILE.OUTPUT, 'l_hold1:l_hold2:l_hold3 : '|| l_hold1||':'||l_hold2||':'||l_hold3);
637
638 l_count_header := 0;
639 l_count_header_success := 0;
640 l_count_header_failure := 0;
641 l_old_org_id := -99; --bug4689411
642
643 OPEN ppp_order_hold_cur;
644 LOOP
645 FETCH ppp_order_hold_cur
646 INTO l_header_id
647 , l_orig_sys_document_ref
648 , l_source_document_id
649 , l_order_source_id
650 , l_change_sequence
651 , l_source_document_type_id
652 , l_org_id; --bug4689411
653
654 EXIT WHEN ppp_order_hold_cur%NOTFOUND;
655
656 --bug4689411 If the Operating Unit is not passed to the concurrent program, the context needs to be set for each accessible OU before calling Process_Payments
657 IF p_operating_unit is NULL THEN
658 IF l_org_id <> l_old_org_id THEN
659 MO_GLOBAL.set_policy_context('S',l_org_id);
660 IF l_debug_level > 0 THEN
661 oe_debug_pub.add('Setting the context for org_id ' || l_org_id);
662 END IF;
663 l_old_org_id := l_org_id;
664 END IF;
665 END IF;
666 --bug4689411 end
667
668 fnd_file.put_line(FND_FILE.OUTPUT, 'Processing header_id: '||l_header_id);
669 l_count_header := l_count_header + 1;
670 IF l_count_header = 1 THEN
671 -- Set Message Context
672 OE_MSG_PUB.set_msg_context(
673 p_entity_code => 'HEADER'
674 ,p_entity_id => l_header_id
675 ,p_header_id => l_header_id
676 ,p_line_id => null
677 ,p_orig_sys_document_ref => l_orig_sys_document_ref
678 ,p_orig_sys_document_line_ref => null
679 ,p_change_sequence => l_change_sequence
680 ,p_source_document_id => l_source_document_id
681 ,p_source_document_line_id => null
682 ,p_order_source_id => l_order_source_id
683 ,p_source_document_type_id => l_source_document_type_id
684 );
685 ELSIF l_count_header > 1 THEN
686 -- Update Message Context
687 OE_MSG_PUB.update_msg_context(
688 p_entity_code => 'HEADER'
689 ,p_entity_id => l_header_id
690 ,p_header_id => l_header_id
691 ,p_line_id => null
692 ,p_orig_sys_document_ref => l_orig_sys_document_ref
693 ,p_orig_sys_document_line_ref => null
694 ,p_change_sequence => l_change_sequence
695 ,p_source_document_id => l_source_document_id
696 ,p_source_document_line_id => null
697 ,p_order_source_id => l_order_source_id
698 ,p_source_document_type_id => l_source_document_type_id
699 );
700 END IF;
701
702 IF IS_MULTIPLE_PAYMENTS_ENABLED THEN
703 IF p_payment_authorization_hold = 'Y' THEN
704 p_process_authorization := 'Y';
705 END IF;
706 IF p_ppp_hold ='Y'
707 OR p_epay_failure_hold = 'Y'
708 OR p_epay_server_failure_hold = 'Y' THEN
709 p_process_prepayment := 'Y';
710 END IF;
711 IF l_debug_level > 0 THEN
712 oe_debug_pub.add( 'CALLING PROCESS_PAYMENT FOR HEADER_ID: '||L_HEADER_ID ) ;
713 END IF;
714
715 oe_prepayment_pvt.process_payments(p_header_id => l_header_id
716 ,p_calling_action => NULL
717 ,p_amount => NULL
718 ,p_delayed_request => NULL
719 ,p_process_prepayment => p_process_prepayment
720 ,p_process_authorization => p_process_authorization
721 ,x_msg_count => l_msg_count
722 ,x_msg_data => l_msg_data
723 ,x_return_status => l_return_status);
724
725 IF l_debug_level > 0 THEN
726 oe_debug_pub.add( 'AFTER CALLING PROCESS_PAYMENT, L_RETURN_STATUS: '||L_RETURN_STATUS ) ;
727 END IF;
728 ELSE
729
730 IF l_debug_level > 0 THEN
731 oe_debug_pub.add( 'CALLING VERIFY_PAYMENT FOR HEADER_ID: '||L_HEADER_ID ) ;
732 END IF;
733 OE_Verify_Payment_PUB.Verify_Payment(p_header_id => l_header_id
734 ,p_calling_action => NULL
735 ,p_delayed_request => NULL
736 ,p_msg_count => l_msg_count
737 ,p_msg_data => l_msg_data
738 ,p_return_status => l_return_status);
739 IF l_debug_level > 0 THEN
740 oe_debug_pub.add( 'AFTER CALLING VERIFY_PAYMENT , L_RETURN_STATUS: '||L_RETURN_STATUS ) ;
741 END IF;
742 END IF;
743
744 IF l_return_status = FND_API.G_RET_STS_ERROR OR
745 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR
746 THEN
747 l_count_header_failure := l_count_header_failure + 1;
748 ELSE
749 --bug 4967981 start
750 OE_Prepayment_PVT.Any_Payment_Hold_Exists(p_header_id => l_header_id,
751 p_hold_exists => l_hold_exists);
752 IF l_hold_exists = 'Y' THEN
753 l_count_header_failure := l_count_header_failure + 1;
754 ELSE
755 l_count_header_success := l_count_header_success + 1;
756 END IF;
757 --bug 4967981 end
758 END IF;
759
760 IF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
761 IF l_debug_level > 0 THEN
762 oe_debug_pub.add( 'UNEXPECTED ERROR' ) ;
763 END IF;
764 -- OE_MSG_PUB.Save_Messages(p_request_id => l_request_id);
765 -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
766 ELSIF l_return_status = FND_API.G_RET_STS_ERROR THEN
767 IF l_debug_level > 0 THEN
768 oe_debug_pub.add( 'EXPECTED ERROR' ) ;
769 END IF;
770 -- OE_MSG_PUB.Save_Messages(p_request_id => l_request_id);
771 -- RAISE FND_API.G_EXC_ERROR;
772 END IF;
773
774 -- releasing the holds.
775 -- commented out for bug 4967981
776 /***
777 IF l_return_status = FND_API.G_RET_STS_SUCCESS
778 AND IS_MULTIPLE_PAYMENTS_ENABLED THEN
779
780 IF l_debug_level > 0 THEN
781 oe_debug_pub.add( 'OEXUPPYB: releasing payment hold.' ) ;
782 END IF;
783
784 IF p_ppp_hold = 'Y' THEN
785 OE_Prepayment_PVT.Release_Payment_Hold
786 ( p_header_id => l_header_id
787 , p_hold_id => 13
788 , p_msg_count => l_msg_count
789 , p_msg_data => l_msg_data
790 , p_return_status => l_return_status
791 );
792
793 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
794 RAISE FND_API.G_EXC_ERROR;
795 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
796 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
797 END IF;
798 END IF;
799
800 IF p_epay_failure_hold = 'Y' THEN
801 OE_Prepayment_PVT.Release_Payment_Hold
802 ( p_header_id => l_header_id
803 , p_hold_id => 14
804 , p_msg_count => l_msg_count
805 , p_msg_data => l_msg_data
806 , p_return_status => l_return_status
807 );
808
809 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
810 RAISE FND_API.G_EXC_ERROR;
811 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
812 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
813 END IF;
814 END IF;
815
816 IF p_epay_server_failure_hold = 'Y' THEN
817 OE_Prepayment_PVT.Release_Payment_Hold
818 ( p_header_id => l_header_id
819 , p_hold_id => 15
820 , p_msg_count => l_msg_count
821 , p_msg_data => l_msg_data
822 , p_return_status => l_return_status
823 );
824
825 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
826 RAISE FND_API.G_EXC_ERROR;
827 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
828 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
829 END IF;
830 END IF;
831
832 IF p_payment_authorization_hold = 'Y' THEN
833 OE_Prepayment_PVT.Release_Payment_Hold
834 ( p_header_id => l_header_id
835 , p_hold_id => 16
836 , p_msg_count => l_msg_count
837 , p_msg_data => l_msg_data
838 , p_return_status => l_return_status
839 );
840
841 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
842 RAISE FND_API.G_EXC_ERROR;
843 ELSIF l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
844 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
845 END IF;
846 END IF;
847 END IF;
848 ***/ -- end of bug 4967981
849
850 OE_MSG_PUB.Save_Messages(p_request_id => l_request_id);
851 END LOOP;
852
853 IF l_debug_level > 0 THEN
854 oe_debug_pub.add( 'NO. OF ORDERS FOUND: ' || L_COUNT_HEADER ) ;
855 END IF;
856 IF l_debug_level > 0 THEN
857 oe_debug_pub.add( 'NO. OF ORDERS SUCCESSFUL: '|| L_COUNT_HEADER_SUCCESS ) ;
858 END IF;
859 IF l_debug_level > 0 THEN
860 oe_debug_pub.add( 'NO. OF ORDERS FAILED: ' || L_COUNT_HEADER_FAILURE ) ;
861 END IF;
862
863 fnd_file.put_line(FND_FILE.OUTPUT,'No. of orders found: ' ||l_count_header);
864 fnd_file.put_line(FND_FILE.OUTPUT,'No. of orders processed successfully: '||l_count_header_success);
865 fnd_file.put_line(FND_FILE.OUTPUT,'No. of orders failed: ' ||l_count_header_failure);
866 fnd_file.put_line(FND_FILE.OUTPUT,'');
867
868 /* -----------------------------------------------------------
869 Messages
870 -----------------------------------------------------------
871 */
872 IF l_debug_level > 0 THEN
873 oe_debug_pub.add( 'BEFORE MESSAGES LOOP' ) ;
874 END IF;
875
876 fnd_file.put_line(FND_FILE.OUTPUT,'');
877 fnd_file.put_line(FND_FILE.OUTPUT,'Source/Order/Seq/Line Message');
878 OPEN l_msg_cursor;
879 LOOP
880 FETCH l_msg_cursor
881 INTO l_header_id
882 , l_order_source_id
883 , l_orig_sys_document_ref
884 , l_source_document_id
885 , l_change_sequence
886 , l_source_document_type_id
887 , l_message_text;
888 EXIT WHEN l_msg_cursor%NOTFOUND;
889
890 fnd_file.put_line(FND_FILE.OUTPUT,to_char(l_header_id)
891 ||'/'||to_char(l_order_source_id)
892 ||'/'||l_orig_sys_document_ref
893 ||'/'||l_source_document_id
894 ||'/'||l_change_sequence
895 ||'/'||l_source_document_type_id
896 ||' '||l_message_text);
897 fnd_file.put_line(FND_FILE.OUTPUT,'');
898 END LOOP;
899
900 retcode := 0;
901 IF l_debug_level > 0 THEN
902 oe_debug_pub.add( 'EXITING OE_PAYMENTS_UTIL.PENDPROCESSPAYMENTS_CONC_PROG' ) ;
903 END IF;
904 --rollback;
905 EXCEPTION
906
907 WHEN FND_API.G_EXC_ERROR THEN
908 l_return_status := FND_API.G_RET_STS_ERROR ;
909 IF l_debug_level > 0 THEN
910 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
911 END IF;
912 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
913 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'PendProcessPayments_Conc_Prog');
914 END IF;
915
916 fnd_file.put_line(FND_FILE.OUTPUT,'Unexpected error: ' || sqlerrm);
917 OE_MSG_PUB.Count_And_Get
918 ( p_count => l_msg_count,
919 p_data => l_msg_data
920 );
921
922 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
923 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
924 OE_MSG_PUB.Count_And_Get
925 ( p_count => l_msg_count,
926 p_data => l_msg_data
927 );
928
929 WHEN OTHERS THEN
930 IF l_debug_level > 0 THEN
931 oe_debug_pub.add( 'UNEXPECTED ERROR: '||SQLERRM ) ;
932 END IF;
933 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
934 OE_MSG_PUB.Add_Exc_Msg (G_PKG_NAME, 'PendProcessPayments_Conc_Prog');
935 END IF;
936
937 fnd_file.put_line(FND_FILE.OUTPUT,'Unexpected error: ' || sqlerrm);
938
939 END PendProcessPayments_Conc_Prog;
940
941 /*-------------------------------------------------------------------
942 Function Is_Prepaid_Order
943 Returns 'Y' if the Order is being paid using a Credit Card AND
944 the payment term is of prepayment type.
945 ---------------------------------------------------------------------*/
946 FUNCTION Is_Prepaid_Order
947 ( p_header_rec IN OE_Order_PUB.Header_Rec_Type )
948 RETURN VARCHAR2
949 IS
950 l_prepayment_flag VARCHAR2(1);
951 l_payment_term_rec Payment_Term_Rec_Type;
952 l_count NUMBER;
953 l_header_id NUMBER ;
954 --
955 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
956 --
957 BEGIN
958
959 IF l_debug_level > 0 THEN
960 oe_debug_pub.add( 'OEXUPPYB: IN IS_PREPAID_ORDER' ) ;
961 END IF;
962
963 -- OM code level is below Pack H.
964 IF OE_CODE_CONTROL.Get_Code_Release_Level < '110508' THEN
965 IF l_debug_level > 0 THEN
966 oe_debug_pub.add( 'PREPAYMENT IS NOT SUPPORTED: OM PACK H IS NOT INSTALLED.' , 3 ) ;
967 END IF;
968 RETURN ('N');
969 END IF;
970 IF IS_MULTIPLE_PAYMENTS_ENABLED THEN
971 l_header_id := p_header_rec.header_id;
972 BEGIN
973 SELECT count(*)
974 INTO l_count
975 FROM oe_payments
976 WHERE header_id = l_header_id
977 AND payment_type_code <> 'COMMITMENT'
978 AND nvl(payment_collection_event, 'PREPAY') = 'PREPAY';
979 IF l_count > 0 THEN
980 IF l_debug_level > 0 THEN
981 oe_debug_pub.add( 'There are prepayments for the order-'||l_count , 3 ) ;
982 END IF;
983 RETURN ('Y');
984 ELSE
985 IF l_debug_level > 0 THEN
986 oe_debug_pub.add( 'No prepayments for the order' , 3 ) ;
987 END IF;
988 RETURN ('N');
989 END IF;
990 EXCEPTION
991 WHEN OTHERS THEN
992 IF l_debug_level > 0 THEN
993 oe_debug_pub.add( 'In when others.. returning N' , 3);
994 END IF;
995 RETURN ('N');
996 END;
997 ELSE
998 IF nvl(p_header_rec.payment_type_code, 'NULL') <> 'CREDIT_CARD' THEN
999 IF l_debug_level > 0 THEN
1000 oe_debug_pub.add( 'PREPAYMENT IS NOT SUPPORTED: PAYMENT TYPE IS NOT CREDIT CARD.' , 3 ) ;
1001 END IF;
1002 RETURN ('N');
1003
1004 ELSIF p_header_rec.payment_type_code = 'CREDIT_CARD' THEN
1005 IF g_payment_term_tbl.exists(p_header_rec.payment_term_id) THEN
1006 IF l_debug_level > 0 THEN
1007 oe_debug_pub.add( 'VALUE FOR IS_PREPAID_ORDER IS: ' ||G_PAYMENT_TERM_TBL ( P_HEADER_REC.PAYMENT_TERM_ID ) .IS_PREPAID_ORDER , 3 ) ;
1008 END IF;
1009 RETURN g_payment_term_tbl(p_header_rec.payment_term_id).is_prepaid_order;
1010 ELSE
1011 l_prepayment_flag := AR_PUBLIC_UTILS.Check_Prepay_Payment_Term(
1012 p_header_rec.payment_term_id);
1013
1014 IF nvl(l_prepayment_flag, 'N') = 'Y' THEN
1015
1016 l_payment_term_rec.payment_term_id := p_header_rec.payment_term_id;
1017 l_payment_term_rec.is_prepaid_order := 'Y';
1018
1019 g_payment_term_tbl(p_header_rec.payment_term_id) := l_payment_term_rec;
1020
1021
1022 IF l_debug_level > 0 THEN
1023 oe_debug_pub.add( 'OEXUPPYB: VALUE FOR IS_PREPAID_ORDER IS Y.' , 3 ) ;
1024 END IF;
1025 RETURN ('Y') ;
1026 ELSE
1027 l_payment_term_rec.payment_term_id := p_header_rec.payment_term_id;
1028 l_payment_term_rec.is_prepaid_order := 'N';
1029
1030 g_payment_term_tbl(p_header_rec.payment_term_id) := l_payment_term_rec;
1031
1032 IF l_debug_level > 0 THEN
1033 oe_debug_pub.add( 'OEXUPPYB: VALUE FOR IS_PREPAID_ORDER IS N.' , 3 ) ;
1034 END IF;
1035 RETURN ('N') ;
1036
1037 END IF;
1038 END IF;
1039
1040 END IF;
1041 END IF; -- multiple payments enabled
1042 IF l_debug_level > 0 THEN
1043 oe_debug_pub.add( 'OEXUPPYB: EXITING IS_PREPAID_ORDER.' , 3 ) ;
1044 END IF;
1045
1046 END Is_Prepaid_Order;
1047
1048 -- overloading the function so that it also takes header_id.
1049 FUNCTION Is_Prepaid_Order
1050 ( p_header_id IN NUMBER )
1051 RETURN VARCHAR2
1052 IS
1053 l_prepayment_flag VARCHAR2(1);
1054 l_payment_type_code VARCHAR2(30);
1055 l_payment_term_id NUMBER;
1056 l_payment_term_rec Payment_Term_Rec_Type;
1057 l_count NUMBER;
1058 --
1059 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1060 --
1061 BEGIN
1062
1063 IF l_debug_level > 0 THEN
1064 oe_debug_pub.add( 'OEXUPPYB: IN IS_PREPAID_ORDER' ) ;
1065 END IF;
1066
1067 -- OM code level is below Pack H.
1068 IF OE_CODE_CONTROL.Get_Code_Release_Level < '110508' THEN
1069 IF l_debug_level > 0 THEN
1070 oe_debug_pub.add( 'PREPAYMENT IS NOT SUPPORTED: OM PACK H IS NOT INSTALLED.' , 3 ) ;
1071 END IF;
1072 RETURN ('N');
1073 END IF;
1074
1075 IF IS_MULTIPLE_PAYMENTS_ENABLED THEN
1076 BEGIN
1077 SELECT count(*)
1078 INTO l_count
1079 FROM oe_payments
1080 WHERE header_id = p_header_id
1081 AND payment_type_code <> 'COMMITMENT'
1082 AND nvl(payment_collection_event, 'PREPAY') = 'PREPAY';
1083 IF l_count > 0 THEN
1084 IF l_debug_level > 0 THEN
1085 oe_debug_pub.add( 'There are prepayments for the order-'||l_count, 3 ) ;
1086 END IF;
1087 RETURN ('Y');
1088 ELSE
1089 IF l_debug_level > 0 THEN
1090 oe_debug_pub.add( 'No prepayments for the order' , 3 ) ;
1091 END IF;
1092 RETURN ('N');
1093 END IF;
1094 EXCEPTION
1095 WHEN OTHERS THEN
1096 IF l_debug_level > 0 THEN
1097 oe_debug_pub.add( 'In when others.. returning N' , 3);
1098 END IF;
1099 RETURN ('N');
1100 END;
1101 ELSE
1102 BEGIN
1103 SELECT payment_type_code,payment_term_id
1104 INTO l_payment_type_code, l_payment_term_id
1105 FROM oe_order_headers
1106 WHERE header_id = p_header_id;
1107
1108 EXCEPTION WHEN NO_DATA_FOUND THEN
1109 null;
1110 END;
1111
1112 IF nvl(l_payment_type_code, 'NULL') <> 'CREDIT_CARD' THEN
1113 IF l_debug_level > 0 THEN
1114 oe_debug_pub.add( 'PREPAYMENT IS NOT SUPPORTED: PAYMENT TYPE IS NOT CREDIT CARD.' , 3 ) ;
1115 END IF;
1116 RETURN ('N');
1117
1118 ELSIF l_payment_type_code = 'CREDIT_CARD' THEN
1119
1120 IF g_payment_term_tbl.exists(l_payment_term_id) THEN
1121 IF l_debug_level > 0 THEN
1122 oe_debug_pub.add( 'VALUE FOR IS_PREPAID_ORDER IS: ' ||G_PAYMENT_TERM_TBL ( L_PAYMENT_TERM_ID ) .IS_PREPAID_ORDER , 3 ) ;
1123 END IF;
1124 RETURN g_payment_term_tbl(l_payment_term_id).is_prepaid_order;
1125 ELSE
1126 l_prepayment_flag := AR_PUBLIC_UTILS.Check_Prepay_Payment_Term(
1127 l_payment_term_id);
1128
1129 IF nvl(l_prepayment_flag, 'N') = 'Y' THEN
1130
1131 l_payment_term_rec.payment_term_id := l_payment_term_id;
1132 l_payment_term_rec.is_prepaid_order := 'Y';
1133
1134 g_payment_term_tbl(l_payment_term_id) := l_payment_term_rec;
1135
1136
1137 IF l_debug_level > 0 THEN
1138 oe_debug_pub.add( 'OEXUPPYB: EXITING IS_PREPAID_ORDER IS TRUE' ) ;
1139 END IF;
1140 IF l_debug_level > 0 THEN
1141 oe_debug_pub.add( 'OEXUPPYB: VALUE FOR IS_PREPAID_ORDER IS Y.' , 3 ) ;
1142 END IF;
1143 RETURN ('Y') ;
1144 ELSE
1145 l_payment_term_rec.payment_term_id := l_payment_term_id;
1146 l_payment_term_rec.is_prepaid_order := 'N';
1147
1148 g_payment_term_tbl(l_payment_term_id) := l_payment_term_rec;
1149
1150
1151 IF l_debug_level > 0 THEN
1152 oe_debug_pub.add( 'OEXUPPYB: EXITING IS_PREPAID_ORDER IS FALSE' ) ;
1153 END IF;
1154 IF l_debug_level > 0 THEN
1155 oe_debug_pub.add( 'OEXUPPYB: VALUE FOR IS_PREPAID_ORDER IS N.' , 3 ) ;
1156 END IF;
1157 RETURN ('N') ;
1158
1159 END IF;
1160 END IF;
1161
1162 END IF;
1163 END IF; -- multiple payments enabled
1164 IF l_debug_level > 0 THEN
1165 oe_debug_pub.add( 'OEXUPPYB: EXITING IS_PREPAID_ORDER.' , 3 ) ;
1166 END IF;
1167
1168 END Is_Prepaid_Order; -- end of overloaded function
1169
1170 FUNCTION IS_MULTIPLE_PAYMENTS_ENABLED RETURN BOOLEAN
1171 IS
1172 l_code_release varchar2(30) := NULL;
1173 l_multiple_payments varchar2(1) := NULL;
1174
1175 --
1176 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1177 --
1178 l_lookup_code varchar2(30) := NULL;
1179 BEGIN
1180
1181 IF l_debug_level > 0 THEN
1182 oe_debug_pub.add( 'OEXUPPYB: IN IS_MULTIPLE_PAYMENTS_ENABLED' ) ;
1183 END IF;
1184
1185 /***
1186 -- commented out the following code as this is not needed for R12.
1187 l_code_release := OE_CODE_CONTROL.Get_Code_Release_Level;
1188
1189 IF l_code_release < '110510' THEN
1190 IF l_debug_level > 0 THEN
1191 oe_debug_pub.add( 'LESS THAN PACK J' , 3 ) ;
1192 END IF;
1193 Return False;
1194 END IF;
1195
1196 -- Call AR to see if Multiple Prepayments patch from AR side is already
1197 -- there, by looking at their lookup_type and lookup_code.
1198
1199 -- per AR, the following code is not needed any more, AR prepayment is
1200 -- always enabled in R12.
1201
1202 Begin
1203
1204 SELECT LOOKUP_CODE into l_lookup_code
1205 FROM AR_LOOKUPS
1206 WHERE LOOKUP_TYPE = 'AR_PREPAY_VERSION';
1207
1208 EXCEPTION
1209
1210 WHEN NO_DATA_FOUND THEN
1211 l_lookup_code := NULL;
1212
1213 END;
1214
1215 IF l_lookup_code is NULL THEN
1216
1217 RETURN FALSE;
1218
1219 ELSIF l_lookup_code <> 'V2' THEN
1220
1221 RETURN FALSE;
1222
1223 END IF;
1224
1225 --commenting the following as Multiple payments is always enabled in R12 and returning True always
1226 l_multiple_payments := nvl(OE_Sys_Parameters.Value('MULTIPLE_PAYMENTS'), 'N');
1227
1228 IF l_multiple_payments = 'N' THEN
1229 IF l_debug_level > 0 THEN
1230 oe_debug_pub.add( 'Multiple Payments is not enabled ' , 3 ) ;
1231 END IF;
1232 Return False;
1233 END IF;
1234 ***/
1235
1236
1237 Return True;
1238
1239 EXCEPTION
1240
1241 WHEN OTHERS THEN
1242
1243 Return False;
1244
1245 END IS_MULTIPLE_PAYMENTS_ENABLED;
1246
1247 -- returns 'N' to go to the old code path before multiple payments.
1248 -- returns 'Y' to go to the new code path for multiple payments.
1249 FUNCTION Is_MultiPayments_Order
1250 ( p_header_id IN NUMBER )
1251 RETURN VARCHAR2
1252 IS
1253 l_payment_count NUMBER := 0;
1254 l_multipay_count NUMBER := 0;
1255 --
1256 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1257 --
1258
1259 BEGIN
1260
1261 IF l_debug_level > 0 THEN
1262 oe_debug_pub.add( 'Entering OE_Prepayment_Util.Is_Multipayments_Order. ' , 3 ) ;
1263 END IF;
1264
1265 -- if exists any full prepaid orders.
1266 BEGIN
1267 SELECT count(*)
1268 INTO l_payment_count
1269 FROM oe_payments
1270 WHERE payment_collection_event IS NULL
1271 AND header_id = p_header_id;
1272 EXCEPTION WHEN NO_DATA_FOUND THEN
1273 l_payment_count := 0;
1274 END;
1275
1276 IF l_debug_level > 0 THEN
1277 oe_debug_pub.add( 'l_payment_count is: '||l_payment_count , 3 ) ;
1278 END IF;
1279
1280 IF l_payment_count = 1 THEN
1281
1282 -- this is a full prepaid order.
1283 RETURN 'N';
1284 ELSIF l_payment_count = 0 THEN
1285 BEGIN
1286 SELECT count(*)
1287 INTO l_multipay_count
1288 FROM oe_payments
1289 WHERE header_id = p_header_id;
1290 EXCEPTION WHEN NO_DATA_FOUND THEN
1291 l_multipay_count := 0;
1292 END;
1293
1294 IF l_debug_level > 0 THEN
1295 oe_debug_pub.add( 'l_multipay_count is: '||l_multipay_count , 3 ) ;
1296 END IF;
1297
1298 IF l_multipay_count = 0 THEN
1299 return 'N';
1300 ELSIF l_multipay_count > 0 THEN
1301 return 'Y';
1302 ELSE
1303 return 'N';
1304 END IF;
1305
1306 ELSE
1307 return 'N';
1308 END IF;
1309
1310 IF l_debug_level > 0 THEN
1311 oe_debug_pub.add( 'Entering OE_Prepayment_Util.Is_Multipayments_Order. ' , 3 ) ;
1312 END IF;
1313
1314 END Is_MultiPayments_Order;
1315
1316 Procedure UPLOAD_COMMITMENT(
1317 p_line_id in number,
1318 p_action in varchar2,
1319 x_return_status out nocopy varchar2,
1320 x_msg_count out nocopy number,
1321 x_msg_data out nocopy varchar2) IS
1322 l_return_status varchar2(1) := FND_API.G_RET_STS_SUCCESS;
1323 BEGIN
1324
1325 x_return_status := l_return_status;
1326
1327 END UPLOAD_COMMITMENT;
1328
1329
1330 -- This function is used to get the first installment
1331 -- based on the payment term.
1332 FUNCTION get_downpayment_amount( p_header_id in number,
1333 p_term_id in number,
1334 p_curr_code in varchar2,
1335 p_order_total in number)
1336 return number is
1337
1338 l_installment_tbl ar_prepayments_pub.installment_tbl;
1339 l_downpayment number := 0;
1340 l_term_id number;
1341 l_order_total number;
1342 l_subtotal number;
1343 l_discount number;
1344 l_charges number;
1345 l_tax number;
1346 l_curr_code varchar2(30) := p_curr_code;
1347 l_prepayment_flag VARCHAR2(1) := 'N';
1348 l_msg_count NUMBER := 0;
1349 l_msg_data VARCHAR2(2000) := null;
1350 l_return_status VARCHAR2(1) := NULL;
1351 i number;
1352 BEGIN
1353
1354 /* Call AR to get the payment installment amount */
1355
1356 l_installment_tbl.delete;
1357
1358 IF p_header_id is not null THEN
1359
1360 IF p_term_id is null or l_curr_code is null THEN
1361
1362 Begin
1363 select payment_term_id, transactional_curr_code
1364 into l_term_id, l_curr_code
1365 from oe_order_headers_all
1366 where header_id = p_header_id;
1367 Exception
1368 when no_data_found then
1369 l_term_id := null;
1370 l_curr_code := null;
1371 End;
1372 Else
1373 l_term_id := p_term_id;
1374 l_curr_code := p_curr_code;
1375 END IF; -- if p_term_id is null or l_curr_code is null
1376
1377 /* get prepayment flag for this payment term - l_term_id */
1378
1379 /* comment out for bug 3524195
1380 if l_term_id is not null then
1381 oe_debug_pub.add('term id : ' || l_term_id);
1382
1383 l_prepayment_flag := AR_PUBLIC_UTILS.Check_Prepay_Payment_Term(l_term_id);
1384
1385 oe_debug_pub.add('prepayment_flag is : ' || l_prepayment_flag );
1386 end if;
1387 */
1388
1389 IF p_order_total is null THEN
1390
1391 OE_OE_TOTALS_SUMMARY.Order_Totals
1392 (
1393 p_header_id=>p_header_id,
1394 p_subtotal =>l_subtotal,
1395 p_discount =>l_discount,
1396 p_charges =>l_charges,
1397 p_tax =>l_tax
1398 );
1399
1400 l_order_total := nvl(l_subtotal,0) + nvl(l_charges,0) + nvl(l_tax,0);
1401
1402 oe_debug_pub.add('order total is : ' || l_order_total);
1403
1404 ELSE
1405 l_order_total := p_order_total;
1406 END IF; -- if p_order_total is null
1407
1408
1409 oe_debug_pub.add('currency code is : ' || l_curr_code);
1410
1411 IF l_term_id is not null and
1412 l_order_total is not null and
1413 l_curr_code is not null
1414 -- and nvl(l_prepayment_flag, 'N') = 'Y'
1415 THEN
1416
1417 oe_debug_pub.add('before calling AR');
1418
1419 AR_PREPAYMENTS_PUB.get_installment(
1420 p_term_id => l_term_id,
1421 p_amount => l_order_total,
1422 p_currency_code => l_curr_code,
1423 p_installment_tbl => l_installment_tbl,
1424 x_return_status => l_return_status,
1425 x_msg_count => l_msg_count,
1426 x_msg_data => l_msg_data );
1427
1428 oe_debug_pub.add('after calling AR');
1429
1430 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1431 NULL;
1432 oe_debug_pub.add('return status is failure ');
1433 l_downpayment := 0;
1434 --RAISE FORM_TRIGGER_FAILURE;
1435 ELSE
1436
1437 oe_debug_pub.add('installment api succeeded ');
1438
1439 IF l_installment_tbl.count > 0 THEN
1440 i := l_installment_tbl.first;
1441 while i is not null loop
1442
1443 if l_installment_tbl(i).installment_number = 1 then
1444 l_downpayment := l_installment_tbl(i).installment_amount;
1445 exit;
1446 end if;
1447
1448 i := l_installment_tbl.next(i);
1449
1450 end loop; -- end of while loop
1451 ELSE
1452 oe_debug_pub.add('downpayment is equal to order total ');
1453 l_downpayment := l_order_total;
1454 END IF; -- if l_installment_tbl.count > 0
1455
1456 END IF; -- end of l_return_status
1457
1458 null;
1459 END IF; -- if l_term_id is not null and l_order_total is not null...
1460
1461 END IF; -- if p_header_id is not null
1462
1463 return l_downpayment;
1464
1465 Exception
1466
1467 When others then
1468 oe_debug_pub.add('exception handling ');
1469 l_downpayment := 0;
1470 return l_downpayment;
1471
1472 END get_downpayment_amount;
1473
1474 PROCEDURE Is_Prepaid_for_payment_term(p_application_id IN NUMBER,
1475 p_entity_short_name in VARCHAR2,
1476 p_validation_entity_short_name in VARCHAR2,
1477 p_validation_tmplt_short_name in VARCHAR2,
1478 p_record_set_tmplt_short_name in VARCHAR2,
1479 p_scope in VARCHAR2,
1480 p_result OUT NOCOPY NUMBER ) IS
1481 --
1482 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1483 --
1484 BEGIN
1485 IF l_debug_level > 0 THEN
1486 oe_debug_pub.add( 'ENTERING OE_PREPAYMENT_UTIL.IS_PREPAID_FOR_PAYMENT_TERM' ) ;
1487 END IF;
1488 IF OE_CODE_CONTROL.Get_Code_Release_Level < '110508' THEN
1489 IF l_debug_level > 0 THEN
1490 oe_debug_pub.add( 'OEXUPPY: BELOW PACKH , PREPAYMENTS NOT ENABLED' )
1491 ;
1492 END IF;
1493 p_result := 0;
1494 RETURN;
1495 END IF;
1496 --serla begin
1497 IF IS_MULTIPLE_PAYMENTS_ENABLED THEN
1498 IF l_debug_level > 0 THEN
1499 oe_debug_pub.add( 'Multiple payment enabled. Should not fire the constraint hence setting result to 0', 5);
1500 END IF;
1501 p_result := 0;
1502 ELSE
1503 IF l_debug_level > 0 THEN
1504 oe_debug_pub.add( 'Calling Is_Prepaid', 5);
1505 END IF;
1506 Is_Prepaid(p_application_id => p_application_id,
1507 p_entity_short_name => p_entity_short_name,
1508 p_validation_entity_short_name => p_validation_entity_short_name,
1509 p_validation_tmplt_short_name => p_validation_tmplt_short_name,
1510 p_record_set_tmplt_short_name => p_record_set_tmplt_short_name,
1511 p_scope => p_scope,
1512 p_result => p_result );
1513 IF l_debug_level > 0 THEN
1514 oe_debug_pub.add( 'p_result after call to Is_Prepaid:'||p_result, 5);
1515 END IF;
1516 END IF;
1517 IF l_debug_level > 0 THEN
1518 oe_debug_pub.add( 'EXITING OE_PREPAYMENT_UTIL.IS_PREPAID_FOR_PAYMENT_TERM WITH RESULT: '||P_RESULT ) ;
1519 END IF;
1520
1521 END Is_Prepaid_for_payment_term;
1522
1523 --pnpl start
1524
1525 FUNCTION Get_Installment_Options (p_org_id IN NUMBER DEFAULT NULL) RETURN VARCHAR2 IS
1526 l_installment_options VARCHAR2(30);
1527 BEGIN
1528 l_installment_options := nvl(OE_Sys_Parameters.Value('INSTALLMENT_OPTIONS', p_org_id), 'NONE');
1529
1530 RETURN l_installment_options;
1531
1532 EXCEPTION
1533 WHEN OTHERS THEN
1534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1535 END Get_Installment_Options;
1536
1537 -- This procedure needs to be called only when the system parameter 'Installment Options' is set to 'ENABLE_PAY_NOW'
1538 Function Is_Pay_Now_Line (p_line_id IN NUMBER) RETURN BOOLEAN
1539 IS
1540
1541 l_header_id NUMBER;
1542 l_pay_now_subtotal NUMBER := 0;
1543 l_pay_now_tax NUMBER := 0;
1544 l_pay_now_charges NUMBER := 0;
1545 l_pay_now_total NUMBER := 0;
1546 l_pay_now_commitment NUMBER :=0;
1547 l_msg_count NUMBER;
1548 l_msg_data VARCHAR2(2000);
1549 l_return_status VARCHAR2(30) := FND_API.G_RET_STS_SUCCESS;
1550 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1551
1552 BEGIN
1553 BEGIN
1554 SELECT header_id
1555 INTO l_header_id
1556 FROM oe_order_lines_all
1557 WHERE line_id = p_line_id;
1558 EXCEPTION
1559 WHEN NO_DATA_FOUND THEN
1560 IF l_debug_level > 0 THEN
1561 oe_debug_pub.add('No data found for line_id : '||p_line_id);
1562 END IF;
1563 RETURN FALSE;
1564 END;
1565
1566 OE_Prepayment_PVT.Get_Pay_Now_Amounts
1567 (p_header_id => l_header_id
1568 ,p_line_id => p_line_id
1569 ,x_pay_now_subtotal => l_pay_now_subtotal
1570 ,x_pay_now_tax => l_pay_now_tax
1571 ,x_pay_now_charges => l_pay_now_charges
1572 ,x_pay_now_total => l_pay_now_total
1573 ,x_pay_now_commitment => l_pay_now_commitment
1574 ,x_msg_count => l_msg_count
1575 ,x_msg_data => l_msg_data
1576 ,x_return_status => l_return_status
1577 );
1578
1579 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
1580 IF l_debug_level > 0 THEN
1581 oe_debug_pub.add('OE_Prepayment_PVT.Get_Pay_Now_Amounts returned with errors');
1582 END IF;
1583 RETURN FALSE;
1584 END IF;
1585
1586 IF l_pay_now_total > 0 THEN
1587 -- this is a pay now line
1588 RETURN TRUE;
1589 ELSE
1590 RETURN FALSE;
1591 END IF;
1592
1593 END Is_Pay_Now_Line;
1594 --pnpl end
1595
1596 END OE_PrePayment_UTIL ;
[Home] [Help]