[Home] [Help]
PACKAGE BODY: APPS.OZF_SD_BATCH_PVT
Source
1 PACKAGE BODY OZF_SD_BATCH_PVT AS
2 /* $Header: ozfvsdbb.pls 120.101.12020000.2 2012/07/21 12:38:20 sariff ship $ */
3
4 -- Start of Comments
5 -- Package name : OZF_SD_BATCH_PVT
6 -- Purpose : This package contains procedures and functions for batch creation concurrent program
7 -- Also contains executable procedure for auto claim concurrent program
8 -- Author : MBHATT
9 -- Created : 11/16/2007 2:39:16 PM
10 -- History :
11 -- 16-FEB-2008 JMAHENDR - Modified GET_BATCH_CURRENCY_AMOUNT to use
12 -- OZF_OFFERS.TRANSACTION_CURRENCY_CODE
13 -- - Insert of claim_amount_currency_code in CREATE_BATCH_LINES
14 -- 22-FEB-2008 JMAHENDR - Change filter condition on batch line create to pick accruals that are !=0
15 -- - Change to use PO_VENDOR_CONTACTS instead of AP_SUPPLIER_CONTACTS
16 -- - Use of tradeprofile (TP) thresholds only if TP currency is set
17 -- 25-SEP-2008 - ANNSRINI - removed product check in create_batch_lines and getting org_id from request lines instead of funds_utilised table
18 -- 26-SEP-2008 - ANNSRINI - Modified p_start_date and p_end_date to varchar instead of DATE in create_batch_main
19 -- added l_start_date and l_end_date in create_batch_main and passing these in call to create_batch_sub
20 -- - Introduced the same code removed for product check except for org_id in create_batch_lines proc.
21 -- 29-JAN-2009 - ANNSRINI - Introduced NVL for oel.shipping_quantity_uom, oel.order_quantity_uom
22 -- 10-FEB-2009 - ANNSRINI - Populating l_supplier_contact_name in CREATE_BATCH_HEADER API
23 -- 11-FEB-2009 - JMAHENDR - Change to RA_CUSTOMER_TRX_LINES_ALL sql for RMA
24 -- 23-FEB-2009 - ANNSRINI - Added debug messages to get the timings of cursor execution in create_batch_lines, insertion, invoking claim API
25 -- 25-FEB-2009 - ANNSRINI - Change w.r.t RMA and negative accrual
26 -- 06-MAR-2009 - ANNSRINI - Changed the sequence of p_fund_id and p_product_id in create_batch_main proc
27 -- 20-APR-2009 - ANNSRINI - Changes w.r.t cost_basis, quantity in case of RMA, invoice and product info
28 -- 06-MAY-2009 - JMAHENDR - bug fix 8489965 - use absolute value of accrual
29 -- 19-JUN-2009 - ANNSRINI - 3 APIs added - PROCESS_SD_PENDING_CLM_BATCHES, PROCESS_SUPPLIER_SITES and INVOKE_CLAIM
30 -- 30-JUN-2009 - ANNSRINI - If claim process is unsuccessful, then update batch header status as PENDING_CLAIM
31 -- 20-JUL-2009 - ANNSRINI - Adjustment related changes
32 -- 03-NOV-2009 - ANNSRINI - fix for bug 8890852 (added resource_busy exception)
33 -- 07-DEC-2009 - ANNSRINI - changes w.r.t multicurrency
34 -- 07-SEP-2010 - RSATYAVA - Changes For Bug# 10011106: bobfwd: fp12.1.3:multiple batch lines for the same utilization entry
35 -- 07-SEP-2010 - RSATYAVA - Changes For Bug#10047209: bobfwd: fp12.1.4 9798553: snd process not creating negative claim
36 -- NOTE :
37 -- End of Comments
38
39 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_SD_BATCH_PVT';
40 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvsdbb.pls';
41 OZF_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
42 OZF_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
43 OZF_ERROR_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error);
44 g_currency VARCHAR2(30) := null;
45 g_ret_level NUMBER := 0;
46
47 type c_batch_header is REF CURSOR;
48 --Start:Added for Bug#9447673 Fix
49 l_idsm_inv_org NUMBER :=fnd_profile.value('AMS_ITEM_ORGANIZATION_ID');
50 --End:Added for Bug#9447673 Fix
51
52
53 --Start: Added for Bug#10011106 fix
54
55 FUNCTION CONV_AMT_TO_OFFER_CURR_AMOUNT(p_offer_currency VARCHAR2,
56 p_amt_val_currency VARCHAR2,
57 p_amt_val number,
58 p_fu_exchange_rate date,
59 p_exchange_rate_type VARCHAR2) RETURN number is
60 x_return_status VARCHAR2(100) := NULL;
61 l_conv_date DATE;
62 x_to_amount NUMBER;
63 x_rate NUMBER;
64 BEGIN
65
66 IF p_amt_val IS not null THEN
67 IF p_amt_val_currency <> p_offer_currency then
68 ozf_utility_pvt.convert_currency (x_return_status => x_return_status
69 ,p_from_currency => p_amt_val_currency
70 ,p_to_currency => p_offer_currency
71 ,p_conv_type => p_exchange_rate_type
72 ,p_conv_date => p_fu_exchange_rate
73 ,p_from_amount => p_amt_val
74 ,x_to_amount => x_to_amount
75 ,x_rate => x_rate
76 );
77
78 ELSE
79 x_to_amount := OZF_SD_BATCH_PVT.CURR_ROUND_EXT_PREC(p_amt_val, p_offer_currency);
80 END IF;
81 ELSE
82 x_to_amount := p_amt_val;
83 END IF;
84
85 RETURN x_to_amount;
86
87 EXCEPTION
88
89 WHEN OTHERS then
90 FND_FILE.PUT_LINE(FND_FILE.LOG,
91 'OZF_UTILITY_PVT.Convert_Currency(x_return_status,' ||
92 x_return_status);
93
94 FND_FILE.PUT_LINE(FND_FILE.LOG,
95 'Start exception block of CONV_AMT_TO_OFFER_CURR_AMOUNT(p_amt_val number:-,' ||
96 p_amt_val || ' p_offer_currency VARCHAR2) ' ||
97 p_offer_currency || ' : x_to_amount :=' ||
98 x_to_amount || SQLERRM);
99 RAISE FND_API.g_exc_error;
100
101 END;
102 --End:Added for Bug#10011106 fix
103
104
105 -- Start of comments
106 -- API name : GET_BATCH_CURRENCY_AMOUNT
107 -- Type : Private
108 -- Pre-reqs : None.
109 -- Function : Converts batch line currency into corresponding header currency
110 -- Conversion is not made if FROM and TO currency are the same.
111 -- Parameters :
112 -- IN : p_func_currency IN VARCHAR2 Required
113 -- p_batch_currency IN VARCHAR2 Required
114 -- p_batch_id IN NUMBER Required
115 -- p_acctd_amount IN NUMBER Required
116 -- p_conv_type IN VARCHAR2
117 -- p_conv_rate IN NUMBER
118 -- p_date IN DATE
119 -- OUT : Converted currency value.
120 -- End of comments
121 FUNCTION GET_BATCH_CURRENCY_AMOUNT(p_func_currency VARCHAR2,
122 p_batch_currency VARCHAR2,
123 p_acctd_amount NUMBER,
124 p_conv_type VARCHAR2,
125 p_conv_rate NUMBER,
126 p_date DATE) RETURN number is
127 x_return_status VARCHAR2(100) := NULL;
128 x_rate NUMBER;
129 l_from_amount NUMBER;
130 x_to_amount NUMBER;
131
132 BEGIN
133
134 OZF_UTILITY_PVT.Convert_Currency(p_from_currency => p_func_currency,
135 p_to_currency => p_batch_currency,
136 p_conv_type => p_conv_type,
137 p_conv_rate => NULL,
138 p_conv_date => p_date,
139 p_from_amount => p_acctd_amount,
140 x_return_status => x_return_status,
141 x_to_amount => x_to_amount,
142 x_rate => x_rate);
143
144 RETURN x_to_amount;
145
146 EXCEPTION
147
148 WHEN OTHERS then
149 FND_FILE.PUT_LINE(FND_FILE.LOG,
150 'OZF_UTILITY_PVT.Convert_Currency(x_return_status,' ||
151 x_return_status);
152
153 FND_FILE.PUT_LINE(FND_FILE.LOG,
154 'Start exception block of GET_BATCH_CURRENCY_AMOUNT(p_acctd_amount :-,' ||
155 p_acctd_amount || ' p_batch_currency VARCHAR2) ' ||
156 p_batch_currency || ' : x_to_amount :=' ||
157 x_to_amount || SQLERRM);
158 RAISE FND_API.g_exc_error;
159
160 END;
161
162 -- Start of comments
163 -- API name : CONV_DISC_TO_OFFER_CURR_AMOUNT
164 -- Type : Private
165 -- Pre-reqs : None.
166 -- Function : Converts discount value from discount value currency to offer currency
167 -- Conversion is not made if FROM and TO currency are the same.
168 -- Parameters :
169 -- IN : p_offer_currency IN NUMBER Required
170 -- p_discount_val_currency IN VARCHAR2 Required
171 -- p_discount_val IN NUMBER Required
172 -- p_batch_id IN NUMBER Required
173 -- OUT : Converted currency value.
174 -- End of comments
175
176 FUNCTION CONV_DISC_TO_OFFER_CURR_AMOUNT(p_offer_currency VARCHAR2,
177 p_discount_val_currency VARCHAR2,
178 p_discount_val number,
179 p_date date) RETURN number is
180 x_return_status VARCHAR2(100) := NULL;
181 l_conv_date DATE;
182 x_to_amount NUMBER;
183
184 BEGIN
185 /*
186 SELECT OZF_SD_BATCH_HEADERS_ALL.Creation_Date
187 INTO l_conv_date
188 FROM OZF_SD_BATCH_HEADERS_ALL
189 WHERE batch_id = p_batch_id;
190 */
191
192 IF p_discount_val_currency <> p_offer_currency then
193 OZF_UTILITY_PVT.Convert_Currency(x_return_status,
194 p_discount_val_currency,
195 p_offer_currency,
196 p_date,
197 p_discount_val,
198 x_to_amount);
199 ELSE
200 x_to_amount := p_discount_val;
201 END IF;
202
203 RETURN x_to_amount;
204
205 EXCEPTION
206
207 WHEN OTHERS then
208 FND_FILE.PUT_LINE(FND_FILE.LOG,
209 'OZF_UTILITY_PVT.Convert_Currency(x_return_status,' ||
210 x_return_status);
211
212 FND_FILE.PUT_LINE(FND_FILE.LOG,
213 'Start exception block of CONV_DISC_TO_OFFER_CURR_AMOUNT(p_discount_val number:-,' ||
214 p_discount_val || ' p_offer_currency VARCHAR2) ' ||
215 p_offer_currency || ' : x_to_amount :=' ||
216 x_to_amount || SQLERRM);
217 RAISE FND_API.g_exc_error;
218
219 END;
220
221 -- Start of comments
222 -- API name : CURR_ROUND_EXT_PREC
223 -- Type : Private
224 -- Pre-reqs : None.
225 -- Function : Rounds the amount based on extended precision for currency
226 -- Parameters :
227 -- IN : p_amount IN NUMBER Required
228 -- p_currency_code IN VARCHAR2 Required
229 --
230 -- OUT : Rounded amount based on extended precision for currency.
231 -- End of comments
232
233 FUNCTION CURR_ROUND_EXT_PREC(
234 p_amount IN NUMBER,
235 p_currency_code IN VARCHAR2
236 )
237 RETURN NUMBER IS
238
239 precision NUMBER; /* number of digits to right of decimal*/
240 ext_precision NUMBER; /* precision where more precision is needed*/
241 min_acct_unit NUMBER; /* minimum value by which amt can vary */
242
243 BEGIN
244
245 FND_CURRENCY.get_info(p_currency_code, precision, ext_precision, min_acct_unit);
246
247 IF min_acct_unit IS NOT NULL THEN
248 RETURN( ROUND( p_amount / min_acct_unit) * min_acct_unit );
249 ELSE
250 RETURN( ROUND( p_amount, ext_precision ));
251 END IF;
252
253 END CURR_ROUND_EXT_PREC;
254
255
256 -- Start of comments
257 -- API name : GET_VENDOR_ITEM_ID
258 -- Type : Private
259 -- Pre-reqs : None.
260 -- Function : Returns the mapped vendor product id for a given distributor
261 -- product id based on trade profile for vendor site.
262 -- Parameters :
263 -- IN : p_product_id IN NUMBER Required
264 -- p_supplier_site_id IN NUMBER Required
265 -- OUT : Vendor product code.
266 -- End of comments
267 FUNCTION GET_VENDOR_ITEM_ID(p_product_id number,
268 p_supplier_site_id number) RETURN varchar2 is
269
270 l_vendor_product_id varchar2(240) := null;
271 l_internal_code varchar2(240) := null;
272
273 BEGIN
274 l_internal_code := p_product_id;
275
276 SELECT code.external_code
277 INTO l_vendor_product_id
278 FROM OZF_SUPP_CODE_CONVERSIONS_ALL code, OZF_SUPP_TRD_PRFLS_ALL prf
279 WHERE internal_code = l_internal_code and
280 code.supp_trade_profile_id = prf.supp_trade_profile_id and
281 prf.supplier_site_id = p_supplier_site_id and
282 trunc(sysdate) between code.start_date_active and
283 nvl(code.end_date_active, sysdate + 1);
284
285 RETURN l_vendor_product_id;
286
287 END get_vendor_item_id;
288
289
290
291 -- Start of comments
292 -- API name : create_batch_main
293 -- Type : Private
294 -- Pre-reqs : None.
295 -- Function : Executable target for concurrent program
296 -- Executable Name "OZFSDBPREX"
297 -- Loops over availabe operating units and invokes create_batch_sub
298 -- Parameters :
299 -- IN : p_org_id IN NUMBER
300 -- p_supplier_id IN NUMBER REQUIRED
301 -- p_supplier_site_id IN NUMBER
302 -- p_product_id IN NUMBER
303 -- p_request_id IN NUMBER
304 -- p_fund_id IN NUMBER
305 -- p_start_date IN DATE
306 -- p_end_date IN DATE
307 -- p_period IN VARCHAR2
308 -- p_attribute1 IN VARCHAR2
309 -- p_attribute2 IN VARCHAR2
310 -- p_attribute3 IN VARCHAR2
311 -- p_attribute4 IN VARCHAR2
312 -- p_attribute5 IN VARCHAR2
313 -- p_attribute6 IN VARCHAR2
314 -- p_attribute7 IN VARCHAR2
315 -- p_attribute8 IN VARCHAR2
316 -- p_attribute9 IN VARCHAR2
317 -- p_attribute10 IN VARCHAR2
318 -- p_attribute11 IN VARCHAR2
319 -- p_attribute12 IN VARCHAR2
320 -- p_attribute13 IN VARCHAR2
321 -- p_attribute14 IN VARCHAR2
322 -- p_attribute15 IN VARCHAR2
323 -- End of comments
324 PROCEDURE create_batch_main(errbuf OUT nocopy VARCHAR2,
325 retcode OUT nocopy NUMBER,
326 p_org_id IN NUMBER,
327 p_supplier_id IN NUMBER,
328 p_supplier_site_id IN NUMBER,
329 --p_category_id IN NUMBER,
330 p_fund_id IN NUMBER,
331 p_request_id IN NUMBER,
332 p_product_id IN NUMBER,
333 p_start_date IN VARCHAR2,
334 p_end_date IN VARCHAR2,
335 p_period IN VARCHAR2,
336 p_owner IN NUMBER,
337 p_attribute1 IN VARCHAR2 := NULL,
338 p_attribute2 IN VARCHAR2 := NULL,
339 p_attribute3 IN VARCHAR2 := NULL,
340 p_attribute4 IN VARCHAR2 := NULL,
341 p_attribute5 IN VARCHAR2 := NULL,
342 p_attribute6 IN VARCHAR2 := NULL,
343 p_attribute7 IN VARCHAR2 := NULL,
344 p_attribute8 IN VARCHAR2 := NULL,
345 p_attribute9 IN VARCHAR2 := NULL,
346 p_attribute10 IN VARCHAR2 := NULL,
347 p_attribute11 IN VARCHAR2 := NULL,
348 p_attribute12 IN VARCHAR2 := NULL,
349 p_attribute13 IN VARCHAR2 := NULL,
350 p_attribute14 IN VARCHAR2 := NULL,
351 p_attribute15 IN VARCHAR2 := NULL
352 ) IS
353
354 CURSOR operating_unit_csr IS
355 SELECT ou.organization_id org_id
356 FROM hr_operating_units ou
357 WHERE mo_global.check_access(ou.organization_id) = 'Y';
358
359 m NUMBER := 0;
360 l_org_id OZF_UTILITY_PVT.operating_units_tbl;
361
362 l_start_date date;
363 l_end_date date;
364 --Start:Added for fixing the bug#9447673
365 l_order_source_type VARCHAR2(30) := null;
366 l_batch_types BatchTypes;
367 --End:Added for fixing the bug#9447673
368
369 /* resource_busy EXCEPTION;
370 PRAGMA EXCEPTION_INIT (resource_busy, -54); */
371
372 BEGIN
373
374 g_ret_level := 0;
375
376
377 IF OZF_DEBUG_LOW_ON THEN
378
379 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start CREATE_BATCH_MAIN');
380 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Start Parameter List ---');
381 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_org_id: ' || p_org_id);
382 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_supplier_id: ' || p_supplier_id);
383 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_supplier_site_id: ' || p_supplier_site_id);
384 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_product_id: ' || p_product_id);
385 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_request_id: ' || p_request_id);
386 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_fund_id: ' || p_fund_id);
387 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_start_date: ' || p_start_date);
388 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_end_date: ' || p_end_date);
389 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_period: ' || p_period);
390 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute1: ' || p_attribute1);
391 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute2: ' || p_attribute2);
392 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute3: ' || p_attribute3);
393 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute4: ' || p_attribute4);
394 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute5: ' || p_attribute5);
395 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute6: ' || p_attribute6);
396 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute7: ' || p_attribute7);
397 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute8: ' || p_attribute8);
398 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute9: ' || p_attribute9);
399 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute10: ' || p_attribute10);
400 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute11: ' || p_attribute11);
401 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute12: ' || p_attribute12);
402 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute13: ' || p_attribute13);
403 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute14: ' || p_attribute14);
404 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute15: ' || p_attribute15);
405 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- End Parameter List ---');
406 END IF;
407 MO_GLOBAL.init('OZF');
408
409 IF p_org_id IS NULL THEN
410 MO_GLOBAL.set_policy_context('M', null);
411 OPEN operating_unit_csr;
412 LOOP
413 FETCH operating_unit_csr
414 INTO l_org_id(m);
415 m := m + 1;
416 EXIT WHEN operating_unit_csr%NOTFOUND;
417 END LOOP;
418 CLOSE operating_unit_csr;
419 ELSE
420 l_org_id(m) := p_org_id;
421 END IF;
422
423 IF (l_org_id.COUNT > 0) THEN
424 FOR m IN l_org_id.FIRST .. l_org_id.LAST LOOP
425 BEGIN
426 IF OZF_DEBUG_LOW_ON THEN
427 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Start Fetch of Organization ids ---');
428 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_org_id ' || to_char(l_org_id(m)));
429 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- End Fetch of Organization ids ---');
430 END IF;
431
432 MO_GLOBAL.set_policy_context('S', l_org_id(m));
433
434 /* Added For Fixing the bug#944763
435 Based on the Profile option OZF:Supplier Ship and Debit on Indirect Sales
436 batch types array is populated
437 */
438
439 l_order_source_type :=fnd_profile.value('OZF_SSD_ON_IDSM');
440 IF l_order_source_type = 'Y' THEN
441 l_batch_types := BatchTypes('OM','IDSM');
442 ELSE
443 l_batch_types := BatchTypes('OM');
444
445 END IF;
446
447 IF OZF_DEBUG_HIGH_ON THEN
448 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Processing for Operating Unit: ' ||
449 MO_GLOBAL.get_ou_name(l_org_id(m)));
450 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Profile option OZF: Supplier Ship and Debit on Indirect Sales value is: ' ||l_order_source_type );
451 END IF;
452
453
454 IF p_start_date IS NOT NULL THEN
455 l_start_date := to_date(p_start_date,'YYYY/MM/DD HH24:MI:SS');
456 END IF ;
457
458 IF p_end_date IS NOT NULL THEN
459 l_end_date := to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
460 END IF ;
461
462
463 create_batch_sub(l_org_id(m),
464 p_supplier_id,
465 p_supplier_site_id,
466 --p_category_id ,
467 p_product_id,
468 p_request_id,
469 p_fund_id,
470 l_start_date,
471 l_end_date,
472 p_period,
473 p_owner,
474 FND_API.g_true,
475 p_attribute1,
476 p_attribute2,
477 p_attribute3,
478 p_attribute4,
479 p_attribute5,
480 p_attribute6,
481 p_attribute7,
482 p_attribute8,
483 p_attribute9,
484 p_attribute10,
485 p_attribute11,
486 p_attribute12,
487 p_attribute13,
488 p_attribute14,
489 p_attribute15,
490 l_batch_types);
491 EXCEPTION
492
493 WHEN OTHERS THEN
494 IF OZF_DEBUG_HIGH_ON THEN
495 FND_FILE.PUT_LINE(FND_FILE.LOG,
496 'Exception in CREATE_BATCH_MAIN : ' ||
497 SQLERRM);
498
499 END IF;
500 --Code added for bug#6971836
501 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Exception in CREATE_BATCH_MAIN : ' ||SQLERRM);
502
503 errbuf := 'Error in CREATE_BATCH_MAIN ' || SQLERRM;
504 retcode := 2;
505 END;
506 END LOOP;
507
508 END IF;
509
510 IF g_ret_level = 1 THEN
511 errbuf := 'Warning in CREATE_BATCH_MAIN ' || SQLERRM;
512 retcode := 1;
513 END IF;
514
515 END create_batch_main;
516
517
518
519 -- Start of comments
520 -- API name : create_batch_sub
521 -- Type : Private
522 -- Pre-reqs : None.
523 -- Function : Invokes creation of Batch looping for Supplier Site and Currency.
524 -- Parameters :
525 -- IN : p_org_id IN NUMBER REQUIRED
526 -- p_supplier_id IN NUMBER REQUIRED
527 -- p_supplier_site_id IN NUMBER
528 -- p_product_id IN NUMBER
529 -- p_request_id IN NUMBER
530 -- p_fund_id IN NUMBER
531 -- p_start_date IN DATE
532 -- p_end_date IN DATE
533 -- p_period IN VARCHAR2
534 -- p_commit IN VARCHAR2
535 -- p_attribute1 IN VARCHAR2
536 -- p_attribute2 IN VARCHAR2
537 -- p_attribute3 IN VARCHAR2
538 -- p_attribute4 IN VARCHAR2
539 -- p_attribute5 IN VARCHAR2
540 -- p_attribute6 IN VARCHAR2
541 -- p_attribute7 IN VARCHAR2
542 -- p_attribute8 IN VARCHAR2
543 -- p_attribute9 IN VARCHAR2
544 -- p_attribute10 IN VARCHAR2
545 -- p_attribute11 IN VARCHAR2
546 -- p_attribute12 IN VARCHAR2
547 -- p_attribute13 IN VARCHAR2
548 -- p_attribute14 IN VARCHAR2
549 -- p_attribute15 IN VARCHAR2
550 -- p_batch_types IN BatchTypes
551 -- End of comments
552 PROCEDURE create_batch_sub(p_org_id IN NUMBER,
553 p_supplier_id IN NUMBER,
554 p_supplier_site_id IN NUMBER,
555 --p_category_id IN NUMBER,
556 p_product_id IN NUMBER,
557 p_request_id IN NUMBER,
558 p_fund_id IN NUMBER,
559 p_start_date IN DATE,
560 p_end_date IN DATE,
561 p_period IN VARCHAR2,
562 p_owner IN NUMBER,
563 p_commit IN VARCHAR2 := FND_API.g_false,
564 p_attribute1 IN VARCHAR2 := NULL,
565 p_attribute2 IN VARCHAR2 := NULL,
566 p_attribute3 IN VARCHAR2 := NULL,
567 p_attribute4 IN VARCHAR2 := NULL,
568 p_attribute5 IN VARCHAR2 := NULL,
569 p_attribute6 IN VARCHAR2 := NULL,
570 p_attribute7 IN VARCHAR2 := NULL,
571 p_attribute8 IN VARCHAR2 := NULL,
572 p_attribute9 IN VARCHAR2 := NULL,
573 p_attribute10 IN VARCHAR2 := NULL,
574 p_attribute11 IN VARCHAR2 := NULL,
575 p_attribute12 IN VARCHAR2 := NULL,
576 p_attribute13 IN VARCHAR2 := NULL,
577 p_attribute14 IN VARCHAR2 := NULL,
578 p_attribute15 IN VARCHAR2 := NULL,
579 p_batch_types IN BatchTypes) IS
580
581 l_empty_batch VARCHAR2(10);
582 l_supplier_id NUMBER;
583 l_supplier_site_id NUMBER;
584 l_org_id NUMBER := NULL; --Code added for bug#6867618
585
586 --l_category_id NUMBER;
587 l_product_id NUMBER;
588
589 -- after checking frequency
590 l_freq NUMBER;
591 l_last_run_date DATE := NULL;
592 l_fund_id NUMBER := NULL;
593 l_start_date DATE := NULL;
594 l_end_date DATE := NULL;
595 l_period VARCHAR2(100) := NULL;
596 l_currency_code VARCHAR2(15) := NULL;
597 l_query VARCHAR2(2000) := NULL;
598 l_currency VARCHAR2(15) := NULL;
599 l_freq_unit VARCHAR2(100) := NULL;
600 type r_cursor is REF CURSOR;
601 c_currency r_cursor;
602
603 l_supplier_name VARCHAR2(240);
604 l_sup_site_name VARCHAR2(15);
605
606 --org id Code added for bug#6867618
607 CURSOR get_sites(c_vendor_id NUMBER,c_org_id NUMBER) IS
608 SELECT sites.vendor_site_id
609 FROM ap_supplier_sites_all sites,
610 ozf_supp_trd_prfls_all trprf
611 WHERE sites.vendor_id = c_vendor_id AND
612 sites.org_id = c_org_id AND
613 nvl(sites.inactive_date, sysdate) >= trunc(sysdate) AND
614 trprf.cust_account_id is not null AND
615 sites.vendor_id=trprf.supplier_id AND
616 sites.vendor_site_id=trprf.supplier_site_id;
617
618 CURSOR trade_profile_values(c_supplier_site_id NUMBER,c_org_id NUMBER) IS
619 SELECT claim_currency_code
620 FROM ozf_supp_trd_prfls_all
621 WHERE supplier_site_id = c_supplier_site_id AND
622 org_id = c_org_id;
623
624 CURSOR get_freq_and_date(c_supplier_site_id NUMBER,c_org_id NUMBER) IS
625 SELECT claim_frequency, claim_frequency_unit, last_paid_date
626 FROM ozf_supp_trd_prfls_all
627 WHERE supplier_site_id = c_supplier_site_id AND
628 org_id = c_org_id;
629
630 BEGIN
631 IF OZF_DEBUG_LOW_ON THEN
632 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start create_batch_sub for org id : ' || p_org_id);
633 END IF;
634
635 --Code added for bug#6867618
636 l_org_id := p_org_id;
637
638 l_supplier_id := p_supplier_id;
639 l_supplier_site_id := p_supplier_site_id;
640 --l_category_id := p_category_id;
641 l_product_id := p_product_id;
642
643 --Code added for output log supplier name
644 Select vendor_name
645 into l_supplier_name
646 From ap_suppliers
647 Where vendor_id = l_supplier_id;
648
649 IF l_supplier_site_id IS NOT NULL THEN
650 --if site is an input parameter
651 IF OZF_DEBUG_LOW_ON THEN
652 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_supplier_site_id IS NOT NULL');
653 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Supplier Site =' || p_supplier_site_id);
654
655 END IF;
656
657 --Code added for output log supplier name
658 Select vendor_site_code
659 into l_sup_site_name
660 From ap_supplier_sites_all
661 Where vendor_site_id = l_supplier_site_id;
662
663 --Code added for bug#6971836
664 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Supplier Name =' || l_supplier_name||'('||l_supplier_id||
665 ') Site Code '||l_sup_site_name||'('||l_supplier_site_id||')');
666
667 OPEN trade_profile_values(p_supplier_site_id,l_org_id);
668 FETCH trade_profile_values
669 INTO l_currency_code;
670 CLOSE trade_profile_values;
671
672 g_currency := l_currency_code;
673
674 IF OZF_DEBUG_LOW_ON THEN
675 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Trade Profile Currency Code = ' ||
676 to_char(NVL(l_currency_code, 'Not Set')));
677
678 END IF;
679
680 --Code added for bug#6971836
681 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Trade Profile Currency Code = ' ||
682 to_char(NVL(l_currency_code, 'Not Set.')));
683
684 IF l_currency_code IS NULL then
685 --Code added for bug#6867618
686 l_query := 'SELECT distinct request_currency_code FROM OZF_SD_REQUEST_HEADERS_ALL_B ' ||
687 ' WHERE supplier_id=' || p_supplier_id ||
688 ' AND supplier_site_id=' || p_supplier_site_id;
689 ELSE
690 l_query := 'SELECT claim_currency_code FROM OZF_SUPP_TRD_PRFLS_ALL ' ||
691 ' WHERE supplier_site_id =' || p_supplier_site_id;
692
693 END IF;
694
695 IF OZF_DEBUG_LOW_ON THEN
696 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Start Query Text ---');
697 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_query = ' || to_char(l_query));
698 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- End Query Text ---');
699 END IF;
700
701 OPEN c_currency for l_query;
702 LOOP
703 FETCH c_currency INTO l_currency;
704
705 EXIT WHEN c_currency%notfound;
706
707 IF OZF_DEBUG_LOW_ON THEN
708 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Processing for Currency ---');
709 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_currency = ' || to_char(l_currency));
710 END IF;
711
712 OPEN get_freq_and_date(l_supplier_site_id,l_org_id);
713 FETCH get_freq_and_date
714 INTO l_freq, l_freq_unit, l_last_run_date;
715 CLOSE get_freq_and_date;
716
717 IF OZF_DEBUG_LOW_ON THEN
718 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Trade Profile Frequency ---');
719 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_freq = ' || l_freq);
720 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_freq_unit = ' || l_freq_unit);
721 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_last_paid_date = ' || l_last_run_date);
722 END IF;
723
724 IF l_freq_unit = 'DAYS' then
725 l_last_run_date := l_last_run_date + l_freq;
726 ELSIF l_freq_unit = 'MONTHS' then
727 SELECT add_months(l_last_run_date, l_freq)
728 INTO l_last_run_date
729 FROM dual;
730 ELSIF l_freq_unit = 'YEAR' then
731 SELECT add_months(l_last_run_date, l_freq * 12)
732 INTO l_last_run_date
733 FROM dual;
734 END IF;
735
736 IF OZF_DEBUG_LOW_ON THEN
737 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_last_run_date :' || to_char(l_last_run_date));
738 END IF;
739
740 IF sysdate >= nvl(l_last_run_date, trunc(sysdate)) THEN
741 IF OZF_DEBUG_HIGH_ON THEN
742 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch Frequency Threshold met');
743 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invoking CREATE BATCH');
744 END IF;
745
746 --Code added for Bug#6971836
747 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Batch Frequency Threshold met.');
748 FOR i in p_batch_types.FIRST .. p_batch_types.LAST LOOP
749
750 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'VALUE OF i'||i);
751 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'VALUE OF batch type'|| p_batch_types(i));
752 create_batch(l_empty_batch,
753 l_supplier_id,
754 l_supplier_site_id,
755 p_org_id,
756 --l_category_id,
757 l_product_id,
758 p_request_id,
759 p_fund_id,
760 p_start_date,
761 p_end_date,
762 p_period,
763 l_currency,
764 p_owner,
765 p_attribute1,
766 p_attribute2,
767 p_attribute3,
768 p_attribute4,
769 p_attribute5,
770 p_attribute6,
771 p_attribute7,
772 p_attribute8,
773 p_attribute9,
774 p_attribute10,
775 p_attribute11,
776 p_attribute12,
777 p_attribute13,
778 p_attribute14,
779 p_attribute15,
780 p_batch_types(i));
781 END LOOP;
782
783 ELSE
784 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch Frequency Threshold not met');
785
786 --Code added for Bug#6971836
787 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Batch Frequency Threshold not met.');
788
789 END IF;
790
791 END LOOP;
792 CLOSE c_currency;
793 -- after currency loop
794 -- IF l_empty_batch = 'N' THEN
795
796 UPDATE ozf_supp_trd_prfls_all
797 SET last_paid_date = sysdate
798 WHERE supplier_site_id = l_supplier_site_id;
799
800 IF fnd_api.To_Boolean(p_commit) THEN
801 COMMIT;
802 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Transaction is commited');
803 FND_FILE.PUT_LINE(FND_FILE.LOG, ' supplier_site_id= ' || l_supplier_site_id);
804 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_currency= ' || l_currency);
805 END IF;
806
807 ELSE
808 --if l_supplier_site is null
809 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_supplier_site_id IS NULL');
810
811 --Code added for bug#6971836
812 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Supplier Name =' || l_supplier_name||'('||l_supplier_id||
813 '), Supplier Site Not Provided. ');
814
815 --if site is null then create a batch for each site
816
817 FOR site_rec IN get_sites(l_supplier_id,l_org_id) LOOP
818 IF OZF_DEBUG_HIGH_ON THEN
819 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Looping for Supplier Site ID = ' ||
820 site_rec.vendor_site_id);
821 END IF;
822
823 --Code modified to get supplier site id
824 OPEN trade_profile_values(site_rec.vendor_site_id,l_org_id);
825 FETCH trade_profile_values
826 INTO l_currency_code;
827 CLOSE trade_profile_values;
828
829 --Code added for bug#6867618
830 g_currency := l_currency_code;
831
832 IF OZF_DEBUG_HIGH_ON THEN
833 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Currenty Code = ' ||l_currency_code);
834 END IF;
835
836 --Code added for Bug#6971836
837 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Currenty Code = ' ||l_currency_code);
838
839 IF l_currency_code IS NULL then
840 --Code added for bug#6867618
841 l_query := 'SELECT distinct request_currency_code FROM OZF_SD_REQUEST_HEADERS_ALL_B ' ||
842 ' WHERE supplier_site_id=' || site_rec.vendor_site_id;
843 ELSE
844 l_query := 'SELECT claim_currency_code FROM OZF_SUPP_TRD_PRFLS_ALL ' ||
845 ' WHERE supplier_site_id =' || site_rec.vendor_site_id;
846
847 END IF;
848
849 OPEN c_currency for l_query;
850 LOOP
851 FETCH c_currency
852 INTO l_currency;
853 EXIT WHEN c_currency%notfound;
854
855 IF OZF_DEBUG_LOW_ON THEN
856 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Processing for Currency ---');
857 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_currency = ' || to_char(l_currency));
858 END IF;
859
860 OPEN get_freq_and_date(site_rec.vendor_site_id,l_org_id);
861 FETCH get_freq_and_date
862 INTO l_freq, l_freq_unit, l_last_run_date;
863 CLOSE get_freq_and_date;
864
865 IF OZF_DEBUG_LOW_ON THEN
866 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Trade Profile Frequency ---');
867 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_freq = ' || l_freq);
868 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_freq_unit = ' || l_freq_unit);
869 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_last_paid_date = ' || l_last_run_date);
870 END IF;
871
872 IF l_freq_unit = 'DAYS' then
873 l_last_run_date := l_last_run_date + l_freq;
874 ELSIF l_freq_unit = 'MONTHS' then
875 SELECT add_months(l_last_run_date, l_freq)
876 INTO l_last_run_date
877 FROM dual;
878 ELSIF l_freq_unit = 'YEAR' then
879 SELECT add_months(l_last_run_date, l_freq * 12)
880 INTO l_last_run_date
881 FROM dual;
882 END IF;
883
884 FND_FILE.PUT_LINE(FND_FILE.LOG,'SuppSite Loop: l_last_run_date post calculation : ' || l_last_run_date);
885
886 IF sysdate >= NVL(l_last_run_date, TRUNC(SYSDATE)) THEN
887
888 FND_FILE.PUT_LINE(FND_FILE.LOG, 'SuppSite Loop: Batch Create Freq Requirement Met');
889
890 --Code added for Bug#6971836
891 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Batch Create Freq Requirement Met.');
892 -- parameters start date, end date, fund_id and period changed for bug 13789136
893 FOR i in p_batch_types.FIRST .. p_batch_types.LAST LOOP
894
895 create_batch(l_empty_batch,
896 l_supplier_id,
897 site_rec.vendor_site_id,
898 p_org_id,
899 --l_category_id,
900 l_product_id,
901 p_request_id,
902 p_fund_id,
903 p_start_date,
904 p_end_date,
905 p_period,
906 l_currency,
907 p_owner,
908 p_attribute1,
909 p_attribute2,
910 p_attribute3,
911 p_attribute4,
912 p_attribute5,
913 p_attribute6,
914 p_attribute7,
915 p_attribute8,
916 p_attribute9,
917 p_attribute10,
918 p_attribute11,
919 p_attribute12,
920 p_attribute13,
921 p_attribute14,
922 p_attribute15,
923 p_batch_types(i));
924 END LOOP;
925
926 ELSE
927 FND_FILE.PUT_LINE(FND_FILE.LOG, 'SuppSite Loop: Batch Create Freq Requirement not met ');
928
929 --Code added for Bug#6971836
930 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Batch Create Freq Requirement not met. ');
931
932 END IF;
933
934 END LOOP;
935
936 UPDATE ozf_supp_trd_prfls_all
937 SET last_paid_date = sysdate
938 WHERE supplier_site_id = l_supplier_site_id;
939
940 IF fnd_api.To_Boolean(p_commit) THEN
941 COMMIT;
942 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Transaction is commited');
943 FND_FILE.PUT_LINE(FND_FILE.LOG, ' supplier_site_id= ' || l_supplier_site_id);
944 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_currency= ' || l_currency);
945 END IF;
946
947 END LOOP; -- supplier site loop
948 END IF; -- end l_supplier site condition
949
950 END create_batch_sub;
951
952
953
954
955 -- Start of comments
956 -- API name : CREATE_BATCH
957 -- Type : Private
958 -- Pre-reqs : None.
959 -- Function : Creates Batch Header and Batch Lines.
960 -- Checks for existance of batch line and batch threshold.
961 -- Rollsback header is the above is not met.
962 -- Invokes claim api and updates header if Trade Profile for Auto Calim is set
963 -- Parameters :
964 -- IN : p_empty_batch OUT VARCHAR2
965 -- p_supplier_id IN NUMBER REQUIRED
966 -- p_supplier_site_id IN NUMBER REQUIRED
967 -- p_org_id IN NUMBER REQUIRED
968 -- --p_category_id IN NUMBER
969 -- p_product_id IN NUMBER
970 -- p_request_id IN NUMBER
971 -- p_fund_id IN NUMBER
972 -- p_start_date IN DATE
973 -- p_end_date IN DATE
974 -- p_period IN VARCHAR2
975 -- p_currency_code IN VARCHAR2 REQUIRED
976 -- p_attribute1 IN VARCHAR2
977 -- p_attribute2 IN VARCHAR2
978 -- p_attribute3 IN VARCHAR2
979 -- p_attribute4 IN VARCHAR2
980 -- p_attribute5 IN VARCHAR2
981 -- p_attribute6 IN VARCHAR2
982 -- p_attribute7 IN VARCHAR2
983 -- p_attribute8 IN VARCHAR2
984 -- p_attribute9 IN VARCHAR2
985 -- p_attribute10 IN VARCHAR2
986 -- p_attribute11 IN VARCHAR2
987 -- p_attribute12 IN VARCHAR2
988 -- p_attribute13 IN VARCHAR2
989 -- p_attribute14 IN VARCHAR2
990 -- p_attribute15 IN VARCHAR2
991 -- p_batch_type IN VARCHAR2
992 -- End of comments
993 PROCEDURE CREATE_BATCH(p_empty_batch OUT NOCOPY VARCHAR2,
994 p_supplier_id IN NUMBER,
995 p_supplier_site_id IN NUMBER,
996 p_org_id IN NUMBER,
997 --p_category_id IN NUMBER,
998 p_product_id IN NUMBER,
999 p_request_id IN NUMBER,
1000 p_fund_id IN NUMBER,
1001 p_start_date IN DATE,
1002 p_end_date IN DATE,
1003 p_period IN VARCHAR2,
1004 p_currency_code IN VARCHAR2,
1005 p_owner IN NUMBER,
1006 p_attribute1 IN VARCHAR2 := NULL,
1007 p_attribute2 IN VARCHAR2 := NULL,
1008 p_attribute3 IN VARCHAR2 := NULL,
1009 p_attribute4 IN VARCHAR2 := NULL,
1010 p_attribute5 IN VARCHAR2 := NULL,
1011 p_attribute6 IN VARCHAR2 := NULL,
1012 p_attribute7 IN VARCHAR2 := NULL,
1013 p_attribute8 IN VARCHAR2 := NULL,
1014 p_attribute9 IN VARCHAR2 := NULL,
1015 p_attribute10 IN VARCHAR2 := NULL,
1016 p_attribute11 IN VARCHAR2 := NULL,
1017 p_attribute12 IN VARCHAR2 := NULL,
1018 p_attribute13 IN VARCHAR2 := NULL,
1019 p_attribute14 IN VARCHAR2 := NULL,
1020 p_attribute15 IN VARCHAR2 := NULL,
1021 p_batch_type IN VARCHAR2) is
1022
1023 CURSOR trade_profile_values(c_supplier_site_id NUMBER) IS
1024 SELECT min_claim_amt, min_claim_amt_line_lvl, auto_debit
1025 FROM ozf_supp_trd_prfls_all
1026 WHERE supplier_site_id = c_supplier_site_id;
1027
1028 l_batch_id NUMBER;
1029 l_empty_batch VARCHAR2(15) := 'Y';
1030 l_auto_claim VARCHAR(1) := 'N';
1031
1032 l_batch_threshold NUMBER := NULL;
1033 l_line_threshold NUMBER := NULL;
1034 l_currency_code VARCHAR2(30);
1035
1036 l_claim_id NUMBER := NULL;
1037 l_claim_ret_status VARCHAR2(15) := NULL;
1038 l_claim_msg_count NUMBER := NULL;
1039 l_claim_msg_data VARCHAR2(500) := NULL;
1040 l_claim_type VARCHAR2(20) := 'SUPPLIER';
1041 l_batch_sum NUMBER := NULL;
1042 l_return_status VARCHAR2(15) := NULL;
1043
1044 resource_busy EXCEPTION;
1045 PRAGMA EXCEPTION_INIT (resource_busy, -54);
1046
1047 --Start: Fix for Bug 10047209
1048 l_custom_setup_id NUMBER := NULL;
1049 l_claim_rec OZF_Claim_PVT.claim_rec_type := NULL;
1050 l_clam_def_rec_type ozf_claim_def_rule_pvt.clam_def_rec_type := NULL;
1051 l_claim_number VARCHAR2(30) := NULL;
1052 l_split_claim_id NUMBER := NULL;
1053 l_msg_count NUMBER := NULL;
1054 l_msg_data VARCHAR2(100) := NULL;
1055 l_msg_count2 NUMBER := NULL;
1056 l_msg_data2 VARCHAR2(100) := NULL;
1057 l_return_status2 VARCHAR2(15) := NULL;
1058 --End: Fix for Bug 10047209
1059
1060 BEGIN
1061
1062 OPEN trade_profile_values(p_supplier_site_id);
1063 FETCH trade_profile_values
1064 INTO l_batch_threshold, l_line_threshold, l_auto_claim;
1065 CLOSE trade_profile_values;
1066
1067 SAVEPOINT BATCHHEADER;
1068
1069 IF OZF_DEBUG_LOW_ON THEN
1070 FND_FILE.PUT_LINE(FND_FILE.LOG, '---Start CREATE_BATCH ---');
1071 END IF;
1072
1073 CREATE_BATCH_HEADER(p_supplier_id,
1074 p_supplier_site_id,
1075 p_org_id,
1076 l_batch_threshold,
1077 l_line_threshold,
1078 p_currency_code,
1079 'N',
1080 'NEW',
1081 NULL, --claim_number will be generated in CREATE_BATCH_HEADER
1082 NULL, --claim_minor_version will be initialized in CREATE_BATCH_HEADER
1083 NULL, --parent_batch_id will be NULL for a new batch
1084 l_batch_id,
1085 p_batch_type,
1086 p_owner);
1087
1088 IF OZF_DEBUG_LOW_ON THEN
1089 FND_FILE.PUT_LINE(FND_FILE.LOG,
1090 'Invoked CREATE_BATCH_HEADER Successfully. Batch_ID=' ||
1091 to_char(l_batch_id));
1092 END IF;
1093
1094 --Code added for Bug#6971836
1095 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Batch ' ||to_char(l_batch_id)||' created.');
1096
1097 IF (p_batch_type='OM') THEN
1098 CREATE_OM_BATCH_LINES(l_batch_id,
1099 p_supplier_id,
1100 p_supplier_site_id,
1101 p_org_id,
1102 l_line_threshold,
1103 p_currency_code,
1104 --p_category_id,
1105 p_product_id,
1106 p_request_id,
1107 p_fund_id,
1108 p_start_date,
1109 p_end_date,
1110 p_period,
1111 l_empty_batch,
1112 p_attribute1,
1113 p_attribute2,
1114 p_attribute3,
1115 p_attribute4,
1116 p_attribute5,
1117 p_attribute6,
1118 p_attribute7,
1119 p_attribute8,
1120 p_attribute9,
1121 p_attribute10,
1122 p_attribute11,
1123 p_attribute12,
1124 p_attribute13,
1125 p_attribute14,
1126 p_attribute15,
1127 p_batch_type);
1128
1129 IF OZF_DEBUG_HIGH_ON THEN
1130 FND_FILE.PUT_LINE(FND_FILE.LOG,
1131 'Invoked CREATE_OM_BATCH_LINES Successfully. l_empty_batch=' ||
1132 to_char(l_empty_batch));
1133 END IF;
1134 ELSE IF(p_batch_type='IDSM') THEN
1135 CREATE_IDSM_BATCH_LINES(l_batch_id,
1136 p_supplier_id,
1137 p_supplier_site_id,
1138 p_org_id,
1139 l_line_threshold,
1140 p_currency_code,
1141 --p_category_id,
1142 p_product_id,
1143 p_request_id,
1144 p_fund_id,
1145 p_start_date,
1146 p_end_date,
1147 p_period,
1148 l_empty_batch,
1149 p_attribute1,
1150 p_attribute2,
1151 p_attribute3,
1152 p_attribute4,
1153 p_attribute5,
1154 p_attribute6,
1155 p_attribute7,
1156 p_attribute8,
1157 p_attribute9,
1158 p_attribute10,
1159 p_attribute11,
1160 p_attribute12,
1161 p_attribute13,
1162 p_attribute14,
1163 p_attribute15,
1164 p_batch_type);
1165
1166 IF OZF_DEBUG_HIGH_ON THEN
1167 FND_FILE.PUT_LINE(FND_FILE.LOG,
1168 'Invoked CREATE_IDSM_BATCH_LINES Successfully. l_empty_batch=' ||
1169 to_char(l_empty_batch));
1170 END IF;
1171 END IF;
1172
1173 END IF;
1174
1175
1176
1177
1178
1179 IF NVL(l_empty_batch, 'Y') = 'N' THEN
1180
1181 --Code added for Bug#6971836
1182 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Batch Lines created.');
1183
1184 SELECT sum(batch_curr_claim_amount)
1185 INTO l_batch_sum
1186 FROM ozf_sd_batch_lines_all
1187 WHERE batch_id = l_batch_id;
1188
1189 -- Check for Batch Amount Threshold
1190
1191 IF OZF_DEBUG_LOW_ON THEN
1192 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Check for Batch Amount Threshold ---');
1193 FND_FILE.PUT_LINE(FND_FILE.LOG,
1194 'l_batch_threshold=' ||
1195 to_char(l_batch_threshold) || ' :: l_batch_sum=' ||
1196 to_char(l_batch_sum));
1197
1198 END IF;
1199
1200 IF NVL(l_batch_threshold, l_batch_sum - 1) > l_batch_sum
1201 AND g_currency IS NOT NULL THEN
1202
1203 IF OZF_DEBUG_HIGH_ON THEN
1204 FND_FILE.PUT_LINE(FND_FILE.LOG, '---Batch Amount Threshold Violated ---');
1205 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch beign Rolledback. Batch_ID = ' ||
1206 to_char(l_batch_id));
1207 END IF;
1208
1209 --Code added for Bug#6971836
1210 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Batch Amount Threshold Violated, Batch rolled back.');
1211
1212 ROLLBACK TO SAVEPOINT BATCHHEADER;
1213 RETURN;
1214 END IF;
1215
1216 UPDATE_AMOUNTS(l_batch_id, l_batch_threshold);
1217
1218 OZF_SD_UTIL_PVT.SD_RAISE_EVENT(l_batch_id, 'CREATE', l_return_status); -- Raising lifecycle event for create
1219 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1220 RAISE FND_API.g_exc_error;
1221 END IF;
1222
1223 --Start:Fix for Bug#10047209 - Create a Claim with new custom setup for -ve amount
1224 --to get claim number
1225 IF OZF_DEBUG_HIGH_ON THEN
1226 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_batch_sum = ' || l_batch_sum);
1227 END IF;
1228
1229 IF (l_batch_sum < 0) THEN
1230 l_claim_rec.claim_class := 'CHARGE';
1231 l_claim_rec.source_object_class := 'SD_SUPPLIER';
1232 OZF_CLAIM_DEF_RULE_PVT.get_clam_def_rule(p_claim_rec => l_claim_rec,
1233 x_clam_def_rec_type => l_clam_def_rec_type,
1234 x_return_status => l_return_status,
1235 x_msg_count => l_msg_count,
1236 x_msg_data => l_msg_data);
1237 l_custom_setup_id := l_clam_def_rec_type.custom_setup_id;
1238
1239 IF OZF_DEBUG_LOW_ON THEN
1240 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_custom_setup_id' || to_char(l_custom_setup_id));
1241 END IF;
1242
1243 OZF_CLAIM_PVT.Get_Claim_Number(l_split_claim_id,
1244 l_custom_setup_id,
1245 l_claim_number,
1246 l_msg_data2,
1247 l_msg_count2,
1248 l_return_status2);
1249 IF OZF_DEBUG_HIGH_ON THEN
1250 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Negative Claim Number = ' || to_char(l_claim_number));
1251 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_batch_id = ' || l_batch_id);
1252 END IF;
1253 UPDATE ozf_sd_batch_headers_all
1254 SET claim_number = l_claim_number
1255 WHERE batch_id = l_batch_id;
1256
1257 END IF;
1258
1259 --End:Fix for Bug#10047209
1260
1261 ELSE
1262
1263 IF OZF_DEBUG_HIGH_ON THEN
1264 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch has no lines, Batch id being rolled back. Batch_ID = ' ||
1265 to_char(l_batch_id));
1266 END IF;
1267
1268 --Code added for Bug#6971836
1269 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Batch has no lines or Line threshold not met '||
1270 to_char(l_batch_id)||', Batch id being rolled back.' );
1271
1272 ROLLBACK TO SAVEPOINT BATCHHEADER;
1273 RETURN;
1274
1275 END IF;
1276
1277 -- end transaction
1278
1279 IF l_auto_claim = 'Y' then
1280
1281 IF OZF_DEBUG_HIGH_ON THEN
1282 FND_FILE.PUT_LINE(FND_FILE.LOG, 'AutoClaim Flag : True.');
1283 END IF;
1284
1285 UPDATE ozf_sd_batch_headers_all
1286 SET status_code = 'APPROVED'
1287 WHERE batch_id = l_batch_id;
1288 COMMIT;
1289
1290 --Fix for Bug#10047209 - Allow batch for -ve amount
1291 IF l_batch_sum <> 0 THEN
1292 IF OZF_DEBUG_HIGH_ON THEN
1293 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch Sum > 0 : Invoking Claim API');
1294 END IF;
1295
1296 IF OZF_DEBUG_LOW_ON THEN
1297 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Claim API Invoke Start time in create_batch ' || to_char(sysdate,'dd-mm-yyyy hh:mi:ss') );
1298 END IF;
1299
1300 OZF_CLAIM_ACCRUAL_PVT.Initiate_SD_Payment(1,
1301 FND_API.g_false,
1302 FND_API.g_true,
1303 FND_API.g_valid_level_full,
1304 l_claim_ret_status,
1305 l_claim_msg_count,
1306 l_claim_msg_data,
1307 l_batch_id,
1308 l_claim_type,
1309 l_claim_id);
1310
1311 IF OZF_DEBUG_LOW_ON THEN
1312 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Claim API Invoke End time in create_batch ' || to_char(sysdate,'dd-mm-yyyy hh:mi:ss') );
1313 END IF;
1314
1315 IF OZF_DEBUG_HIGH_ON THEN
1316 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invoked Claim ....' );
1317 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Batch ID ' || to_char(l_batch_id));
1318 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Claim ID ' || to_char(l_claim_id) );
1319 END IF;
1320
1321 IF OZF_ERROR_ON THEN
1322 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_claim_ret_status ' || l_claim_ret_status );
1323 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_claim_msg_count ' || l_claim_msg_count );
1324 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_claim_msg_data ' || l_claim_msg_data );
1325 FOR I IN 1..l_claim_msg_count LOOP
1326 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Msg from Claim API in Batch Create ' || SUBSTR(FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F'), 1, 254) );
1327 END LOOP;
1328 END IF;
1329
1330 IF l_claim_ret_status = FND_API.G_RET_STS_SUCCESS THEN
1331
1332 --Code added for Bug#6971836
1333 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Claim created for batch.');
1334
1335 UPDATE ozf_sd_batch_headers_all
1336 SET status_code = 'CLOSED',
1337 claim_id = l_claim_id,
1338 last_update_date = sysdate,
1339 last_updated_by = FND_GLOBAL.USER_ID,
1340 object_version_number = object_version_number + 1
1341 WHERE batch_id = l_batch_id;
1342 ELSE
1343 UPDATE ozf_sd_batch_headers_all
1344 SET status_code = 'PENDING_CLAIM',
1345 last_update_date = sysdate,
1346 last_updated_by = FND_GLOBAL.USER_ID,
1347 object_version_number = object_version_number + 1
1348 WHERE batch_id = l_batch_id;
1349
1350 IF OZF_DEBUG_HIGH_ON THEN
1351 FND_FILE.PUT_LINE(FND_FILE.LOG,
1352 'Claim process returned errors, could not update batch with ID :' || l_batch_id);
1353 END IF;
1354 --Code added for Bug#6971836
1355 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Claim process failed.');
1356 END IF;
1357
1358 END IF; --BATCH_SUM > 0
1359
1360
1361 OZF_SD_UTIL_PVT.SD_RAISE_EVENT(l_batch_id, 'CLAIM', l_return_status); -- Raising lifecycle event for claim
1362 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1363 RAISE FND_API.g_exc_error;
1364 END IF;
1365
1366 END IF; --AUTO_CLAIM TRUE
1367 p_empty_batch := l_empty_batch;
1368
1369 EXCEPTION
1370 WHEN resource_busy THEN
1371
1372 g_ret_level := 1 ;
1373
1374
1375 IF OZF_DEBUG_HIGH_ON THEN
1376 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Warning !!!! CREATE_BATCH : Accruals for supplier site : ' || p_supplier_site_id || ' are currently being processed by another request.');
1377 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch_ID : ' || to_char(l_batch_id) || ' is rolled back.' );
1378 END IF;
1379
1380 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Warning !!!! CREATE_BATCH : Accruals for supplier site : ' || p_supplier_site_id || ' are currently being processed by another request.');
1381 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Batch_ID : ' || to_char(l_batch_id) || ' is rolled back.' );
1382
1383 ROLLBACK TO SAVEPOINT BATCHHEADER;
1384
1385 -- RAISE;
1386
1387 END Create_Batch;
1388
1389
1390 -- Start of comments
1391 -- API name : CREATE_BATCH_HEADER
1392 -- Type : Private
1393 -- Pre-reqs : None.
1394 -- Function : Creates Batch Header record.
1395 -- Parameters :
1396 -- IN : p_supplier_id NUMBER REQUIRED
1397 -- p_supplier_site_id NUMBER REQUIRED
1398 -- p_org_id NUMBER REQUIRED
1399 -- p_batch_threshold NUMBER REQUIRED
1400 -- p_line_threshold NUMBER REQUIRED
1401 -- p_batch_currency VARCHAR2 REQUIRED
1402 -- p_batch_new VARCHAR2 REQUIRED
1403 -- p_batch_status VARCHAR2 REQUIRED
1404 -- p_claim_number VARCHAR2
1405 -- p_claim_minor_version IN NUMBER
1406 -- p_parent_batch_id NUMBER
1407 -- p_batch_type IN VARCHAR2
1408 -- OUT : p_batch_id NUMBER
1409 -- End of comments
1410 PROCEDURE CREATE_BATCH_HEADER(p_supplier_id IN NUMBER,
1411 p_supplier_site_id IN NUMBER,
1412 p_org_id IN NUMBER,
1413 p_batch_threshold IN NUMBER,
1414 p_line_threshold IN NUMBER,
1415 p_batch_currency IN VARCHAR2,
1416 p_batch_new IN VARCHAR2,
1417 p_batch_status IN VARCHAR2,
1418 p_claim_number IN VARCHAR2,
1419 p_claim_minor_version IN NUMBER,
1420 p_parent_batch_id IN NUMBER,
1421 p_batch_id OUT NOCOPY NUMBER,
1422 p_batch_type IN VARCHAR2,
1423 p_owner IN NUMBER) is
1424
1425 l_supplier_id NUMBER := NULL;
1426 l_supplier_site_id NUMBER := NULL;
1427 l_batch_id NUMBER := NULL;
1428 l_org_id NUMBER := NULL;
1429 l_supplier_contact_email VARCHAR2(100) := NULL;
1430 l_supplier_contact_id NUMBER := NULL;
1431 l_supplier_contact_phone VARCHAR2(60) := NULL;
1432 l_supplier_contact_fax VARCHAR2(60) := NULL;
1433 l_batch_threshold NUMBER := NULL;
1434 l_line_threshold NUMBER := NULL;
1435 l_batch_currency VARCHAR2(15) := NULL;
1436 l_supplier_contact_name VARCHAR2(240) := NULL;
1437 l_batch_new VARCHAR2(1) := NULL;
1438
1439 --claim number variables
1440
1441 l_return_status VARCHAR2(15) := NULL;
1442 l_msg_count NUMBER := NULL;
1443 l_msg_data VARCHAR2(100) := NULL;
1444 l_return_status2 VARCHAR2(15) := NULL;
1445 l_msg_count2 NUMBER := NULL;
1446 l_msg_data2 VARCHAR2(100) := NULL;
1447 l_custom_setup_id NUMBER := NULL;
1448 l_claim_rec OZF_Claim_PVT.claim_rec_type := NULL;
1449 l_clam_def_rec_type ozf_claim_def_rule_pvt.clam_def_rec_type := NULL;
1450 l_claim_number VARCHAR2(30) := NULL;
1451 l_claim_minor_version NUMBER := NULL;
1452 l_split_claim_id NUMBER := NULL;
1453
1454 l_ssd_imd_claim_flag VARCHAR2(1) := NULL; -- added for ER 13245462
1455
1456 CURSOR get_contact_details(c_supplier_site_id NUMBER) IS
1457 SELECT cont.vendor_contact_id,
1458 decode(cont.last_name,null,null,'','',cont.last_name || ', ') || nvl(cont.middle_name, '')|| ' '|| cont.first_name fullname,
1459 cont.email_address,
1460 decode(cont.phone ,NULL, NULL, cont.area_code || '-' || cont.phone) phone,
1461 decode(cont.fax,NULL, NULL, cont.fax_area_code || '-' || cont.fax) fax
1462 FROM PO_VENDOR_CONTACTS cont
1463 WHERE cont.vendor_site_id = c_supplier_site_id
1464 AND NVL(inactive_date, sysdate+1) > sysdate;
1465
1466 -- added for ER 13245462
1467 CURSOR stp_partial_appr_flag (c_supplier_site_id NUMBER,c_org_id NUMBER) IS
1468 select SSD_IMD_CLAIM_FLAG
1469 from OZF_SUPP_TRD_PRFLS_ALL
1470 where supplier_site_id = c_supplier_site_id
1471 and org_id = c_org_id;
1472
1473 -- added for ER 13245462
1474 CURSOR spa_partial_appr_flag (c_org_id NUMBER) IS
1475 select SSD_IMD_CLAIM_FLAG
1476 from OZF_SYS_PARAMETERS_ALL
1477 where org_id = c_org_id;
1478
1479 BEGIN
1480
1481 l_supplier_id := p_supplier_id;
1482 l_supplier_site_id := p_supplier_site_id;
1483 l_batch_new := p_batch_new;
1484
1485 SELECT ozf_sd_batch_headers_all_s.nextval INTO l_batch_id FROM dual;
1486
1487 l_org_id := p_org_id;
1488 p_batch_id := l_batch_id;
1489 l_batch_threshold := p_batch_threshold;
1490 l_line_threshold := p_line_threshold;
1491 l_batch_currency := p_batch_currency;
1492
1493 IF OZF_DEBUG_LOW_ON THEN
1494 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start CREATE_BATCH_HEADER');
1495 END IF;
1496
1497 -- select contact and contact email from suppliers tables
1498 OPEN get_contact_details(l_supplier_site_id);
1499 FETCH get_contact_details
1500 INTO l_supplier_contact_id, l_supplier_contact_name, l_supplier_contact_email, l_supplier_contact_phone, l_supplier_contact_fax;
1501 CLOSE get_contact_details;
1502
1503 IF OZF_DEBUG_LOW_ON THEN
1504 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_supplier_contact_id' || to_char(l_supplier_contact_id));
1505 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_supplier_contact_name' || l_supplier_contact_name);
1506 END IF;
1507
1508 IF l_batch_new = 'N' THEN
1509 l_claim_minor_version := 1;
1510
1511 --to get claim number
1512 l_claim_rec.claim_class := 'CLAIM';
1513 l_claim_rec.source_object_class := 'SD_SUPPLIER';
1514 OZF_CLAIM_DEF_RULE_PVT.get_clam_def_rule(p_claim_rec => l_claim_rec,
1515 x_clam_def_rec_type => l_clam_def_rec_type,
1516 x_return_status => l_return_status,
1517 x_msg_count => l_msg_count,
1518 x_msg_data => l_msg_data);
1519 l_custom_setup_id := l_clam_def_rec_type.custom_setup_id;
1520
1521 IF OZF_DEBUG_LOW_ON THEN
1522 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_custom_setup_id' || to_char(l_custom_setup_id));
1523 END IF;
1524
1525 OZF_CLAIM_PVT.Get_Claim_Number(l_split_claim_id,
1526 l_custom_setup_id,
1527 l_claim_number,
1528 l_msg_data2,
1529 l_msg_count2,
1530 l_return_status2);
1531 ELSIF l_batch_new = 'F' THEN
1532
1533 l_claim_number := p_claim_number ;
1534 l_claim_minor_version := p_claim_minor_version;
1535 END IF;
1536
1537 IF OZF_DEBUG_HIGH_ON THEN
1538 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Claim Number = ' || to_char(l_claim_number));
1539 END IF;
1540
1541 -- Two cursors added for ER 13245462
1542 OPEN stp_partial_appr_flag(l_supplier_site_id,l_org_id);
1543 FETCH stp_partial_appr_flag INTO l_ssd_imd_claim_flag;
1544 CLOSE stp_partial_appr_flag;
1545
1546 If nvl(l_ssd_imd_claim_flag,'X') = 'X' then
1547 OPEN spa_partial_appr_flag(l_org_id);
1548 FETCH spa_partial_appr_flag INTO l_ssd_imd_claim_flag;
1549 CLOSE spa_partial_appr_flag;
1550 End if;
1551
1552 IF OZF_DEBUG_LOW_ON THEN
1553 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_ssd_imd_claim_flag : ' || l_ssd_imd_claim_flag);
1554 END IF;
1555
1556 INSERT INTO ozf_sd_batch_headers_all
1557 (batch_id,
1558 object_version_number,
1559 batch_number,
1560 claim_number,
1561 claim_minor_version,
1562 vendor_id,
1563 vendor_site_id,
1564 vendor_contact_id,
1565 vendor_contact_name,
1566 vendor_email,
1567 vendor_phone,
1568 vendor_fax,
1569 batch_line_amount_threshold,
1570 batch_amount_threshold,
1571 currency_code,
1572 credit_code,
1573 status_code,
1574 creation_date,
1575 last_update_date,
1576 last_updated_by,
1577 request_id,
1578 created_by,
1579 created_from,
1580 last_update_login,
1581 program_application_id,
1582 program_update_date,
1583 program_id,
1584 transfer_type,
1585 org_id,
1586 parent_batch_id,
1587 batch_type,
1588 owner_id,
1589 imd_claim_flag)
1590 VALUES
1591 (l_batch_id,
1592 1,
1593 l_batch_id,
1594 l_claim_number,
1595 l_claim_minor_version,
1596 l_supplier_id, --supplier_party_id
1597 l_supplier_site_id, --supplier site
1598 l_supplier_contact_id,
1599 l_supplier_contact_name,
1600 l_supplier_contact_email,
1601 l_supplier_contact_phone,
1602 l_supplier_contact_fax,
1603 l_batch_threshold, -- From TP
1604 l_line_threshold, -- From TP
1605 l_batch_currency, -- From TP
1606 'D', -- Value can be Debit or Credit. defaulted to Credit
1607 p_batch_status, --NEW for new batch and APPROVED for child batch
1608 sysdate,
1609 sysdate,
1610 FND_GLOBAL.USER_ID, --las_updated_by
1611 FND_GLOBAL.CONC_REQUEST_ID, --? conc program id
1612 FND_GLOBAL.USER_ID, --created by
1613 null, --created from --??
1614 FND_GLOBAL.CONC_LOGIN_ID, -- last_update_login
1615 FND_GLOBAL.PROG_APPL_ID, -- program app id
1616 sysdate,
1617 FND_GLOBAL.CONC_PROGRAM_ID, --program id
1618 null, --l_transfer_type to be updated when batch is exported
1619 l_org_id, --default batch Org ID
1620 p_parent_batch_id,
1621 p_batch_type,
1622 NVL(p_owner,OZF_UTILITY_PVT.get_resource_id(FND_GLOBAL.USER_ID)),
1623 l_ssd_imd_claim_flag -- added for ER 13245462
1624 );
1625 END Create_Batch_Header;
1626
1627 PROCEDURE CREATE_OM_BATCH_LINES(p_batch_id IN NUMBER,
1628 p_supplier_id IN NUMBER,
1629 p_supplier_site_id IN NUMBER,
1630 p_org_id IN NUMBER,
1631 p_thresh_line_limit IN NUMBER,
1632 p_batch_currency IN VARCHAR2,
1633 --p_category_id IN NUMBER,
1634 p_product_id IN NUMBER,
1635 p_request_id IN NUMBER,
1636 p_fund_id IN NUMBER,
1637 p_start_date IN DATE,
1638 p_end_date IN DATE,
1639 p_period IN VARCHAR2,
1640 p_empty_batch OUT NOCOPY VARCHAR2,
1641 p_attribute1 IN VARCHAR2 := NULL,
1642 p_attribute2 IN VARCHAR2 := NULL,
1643 p_attribute3 IN VARCHAR2 := NULL,
1644 p_attribute4 IN VARCHAR2 := NULL,
1645 p_attribute5 IN VARCHAR2 := NULL,
1646 p_attribute6 IN VARCHAR2 := NULL,
1647 p_attribute7 IN VARCHAR2 := NULL,
1648 p_attribute8 IN VARCHAR2 := NULL,
1649 p_attribute9 IN VARCHAR2 := NULL,
1650 p_attribute10 IN VARCHAR2 := NULL,
1651 p_attribute11 IN VARCHAR2 := NULL,
1652 p_attribute12 IN VARCHAR2 := NULL,
1653 p_attribute13 IN VARCHAR2 := NULL,
1654 p_attribute14 IN VARCHAR2 := NULL,
1655 p_attribute15 IN VARCHAR2 := NULL,
1656 p_order_source IN VARCHAR2) is
1657
1658 l_batch_id NUMBER := NULL;
1659 l_supplier_id NUMBER := NULL;
1660 l_supplier_site_id NUMBER := NULL;
1661 l_org_id NUMBER := NULL;
1662 l_inv_org_id NUMBER := NULL;
1663 l_count NUMBER := 0;
1664
1665 --for cursor execution
1666 l_lines_csr NUMBER := NULL;
1667 l_lines_sql VARCHAR2(10000) := NULL;
1668 l_ignore NUMBER;
1669
1670 -- for define columns
1671 l_batch_line_id NUMBER := NULL;
1672 l_batch_line_number NUMBER := NULL;
1673 l_utilization_id NUMBER := NULL;
1674 l_agreement_number VARCHAR2(100) := NULL;
1675 l_ship_to_org_id NUMBER := NULL;
1676 l_ship_to_contact_id NUMBER := NULL;
1677 l_ship_to_customer_site_id NUMBER := NULL;
1678 l_sold_to_customer_id NUMBER := NULL;
1679 l_SOLD_TO_CONTACT_ID NUMBER := NULL;
1680 l_SOLD_TO_SITE_USE_ID NUMBER := NULL;
1681 l_end_customer_id NUMBER := NULL;
1682 l_end_customer_contact_id NUMBER := NULL;
1683 l_end_customer_site_id NUMBER := NULL;
1684 l_order_header_id NUMBER := NULL;
1685 l_order_line_number NUMBER := NULL;
1686 l_invoice_number NUMBER := NULL;
1687 l_invoice_line_number NUMBER := NULL;
1688 l_resale_price_currency_code VARCHAR2(15) := NULL;
1689 l_resales_price NUMBER := NULL;
1690 l_list_price_currency_code VARCHAR2(15) := NULL;
1691 l_list_price NUMBER := NULL;
1692 l_agreement_currency_code VARCHAR2(15) := NULL;
1693 l_agreement_price NUMBER := NULL;
1694
1695 l_claim_amount NUMBER := NULL;
1696 l_batch_curr_claim_amount NUMBER := NULL;
1697
1698 l_orig_claim_amount NUMBER := NULL;
1699 l_batch_curr_orig_claim_amount NUMBER := NULL;
1700
1701 l_item_id NUMBER := NULL;
1702 l_vendor_item_id NUMBER := NULL;
1703 l_shipped_quantity_uom VARCHAR2(100) := NULL;
1704 l_quantity_shipped NUMBER := NULL;
1705 l_order_date DATE := NULL;
1706 l_claim_amount_currency_code VARCHAR2(15) := NULL;
1707 l_acct_amount_remaining NUMBER := NULL;
1708 l_univ_curr_amount_remaining NUMBER := NULL;
1709 l_fund_req_amount_remaining NUMBER := NULL;
1710 l_amount_remaining NUMBER := NULL;
1711 l_ozf_gl_entries VARCHAR2(15) := NULL;
1712 l_approved_discount_type VARCHAR2(30) := NULL;
1713 l_approved_discount_value NUMBER := NULL;
1714 l_approved_discount_currency VARCHAR2(15) := NULL;
1715 l_adjustment_type_id NUMBER := NULL;
1716
1717 --from trade profile
1718 l_thresh_line_limit NUMBER := NULL;
1719 l_batch_currency VARCHAR2(15) := NULL;
1720
1721 --parameters
1722 l_fund_id NUMBER := NULL;
1723 l_start_date DATE;
1724 l_end_date DATE := NULL;
1725 l_period VARCHAR2(50) := NULL;
1726 l_period_start DATE := NULL;
1727 l_period_end DATE := NULL;
1728 l_func_call_string VARCHAR2(2000) := NULL;
1729 l_cost_price_string VARCHAR2(2000) := NULL;
1730 l_rounded_cost_price VARCHAR2(2000) := NULL;
1731 l_agmt_price_string VARCHAR2(2000) := NULL;
1732 l_shipped_qty_string VARCHAR2(2000) := NULL;
1733
1734 -- to get functional currency
1735 l_func_currency VARCHAR2(15) := NULL;
1736 l_date DATE;
1737
1738 --Start:Added for Bug#10011106 fix
1739 l_cost_price_offer_string VARCHAR2(2000) := NULL;
1740 --End:Added for Bug#10011106 fix
1741
1742 resource_busy EXCEPTION;
1743 PRAGMA EXCEPTION_INIT (resource_busy, -54);
1744
1745 --cursor for period param
1746 CURSOR get_period_limits(c_period VARCHAR2) IS
1747 SELECT start_date, end_date
1748 FROM gl_periods
1749 WHERE period_name = c_period and
1750 period_set_name =
1751 fnd_profile.value('AMS_CAMPAIGN_DEFAULT_CALENDER');
1752
1753 BEGIN
1754 l_batch_id := p_batch_id;
1755 l_org_id := p_org_id;
1756 l_thresh_line_limit := p_thresh_line_limit;
1757 l_batch_currency := p_batch_currency;
1758
1759 l_fund_id := p_fund_id;
1760 l_start_date := p_start_date;
1761 l_end_date := p_end_date;
1762 l_period := p_period;
1763 l_ozf_gl_entries := fnd_profile.value('OZF_ORDER_GLPOST_PHASE');
1764 l_date := sysdate;
1765
1766 IF l_ozf_gl_entries is null then
1767 l_ozf_gl_entries := 'SHIPPED';
1768 END IF;
1769
1770 SELECT gs.currency_code
1771 INTO l_func_currency
1772 FROM gl_sets_of_books gs,
1773 ozf_sys_parameters_all org,
1774 ozf_sd_batch_headers_all bh
1775 WHERE org.set_of_books_id = gs.set_of_books_id
1776 AND org.org_id = bh.org_id
1777 AND bh.batch_id = p_batch_id;
1778
1779 FND_DSQL.init;
1780
1781 FND_DSQL.add_text('SELECT ');
1782 FND_DSQL.add_text('OZF_SD_BATCH_LINES_ALL_S.NEXTVAL , ');
1783 FND_DSQL.add_text('FU.UTILIZATION_ID, ');
1784 FND_DSQL.add_text('RH.AUTHORIZATION_NUMBER, ');
1785 FND_DSQL.add_text('OEL.SHIP_TO_ORG_ID, ');
1786 FND_DSQL.add_text('OEL.SHIP_TO_CONTACT_ID, ');
1787 FND_DSQL.add_text('HZCA.PARTY_ID, ');
1788 FND_DSQL.add_text('OEH.SOLD_TO_CONTACT_ID, ');
1789 FND_DSQL.add_text('OEH.SOLD_TO_SITE_USE_ID, ');
1790
1791 FND_DSQL.add_text('OEL.END_CUSTOMER_ID, ');
1792 FND_DSQL.add_text('OEL.END_CUSTOMER_CONTACT_ID, ');
1793
1794
1795 FND_DSQL.add_text('OEL.HEADER_ID, ');
1796 FND_DSQL.add_text('OEL.LINE_ID, ');
1797
1798
1799 FND_DSQL.add_text('(SELECT CTLA.CUSTOMER_TRX_ID FROM RA_CUSTOMER_TRX_LINES_ALL CTLA
1800 WHERE CTLA.INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(OEL.LINE_ID)
1801 AND CTLA.LINE_TYPE = ''LINE''
1802 AND CTLA.INTERFACE_LINE_CONTEXT = ''ORDER ENTRY''
1803 AND ROWNUM = 1) TRX_NUMBER,');
1804
1805 FND_DSQL.add_text('(SELECT CTLA.CUSTOMER_TRX_LINE_ID FROM RA_CUSTOMER_TRX_LINES_ALL CTLA
1806 WHERE CTLA.INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(OEL.LINE_ID)
1807 AND CTLA.LINE_TYPE = ''LINE''
1808 AND CTLA.INTERFACE_LINE_CONTEXT = ''ORDER ENTRY''
1809 AND ROWNUM = 1) LINE_NUMBER, ');
1810
1811 --Compute Resale Price
1812 FND_DSQL.add_text('OEH.TRANSACTIONAL_CURR_CODE, ');
1813 FND_DSQL.add_text('OEL.UNIT_SELLING_PRICE, ');
1814
1815 --Compute cost price
1816 FND_DSQL.add_text('DECODE(FU.DISCOUNT_TYPE, ''AMT'', null, FU.PLAN_CURRENCY_CODE), ');
1817
1818 -- Call CONV_AMT_TO_OFFER_CURR_AMOUNT where
1819 -- To Currency Offer CURRENCY_CODE
1820 -- From Currency FU.DISCOUNT_AMOUNT_CURRENCY_CODE
1821 -- From Amount FU.DISCOUNT_AMOUNT
1822 -- Date Of conversion FU.EXCHANGE_RATE_DATE
1823 -- Type of Conversion FU.EXCHANGE_RATE_TYPE
1824
1825 l_cost_price_offer_string := 'decode(FU.DISCOUNT_TYPE,
1826 ''AMT'', NULL,
1827 OZF_SD_BATCH_PVT.CONV_AMT_TO_OFFER_CURR_AMOUNT(FU.PLAN_CURRENCY_CODE ,FU.COST_PRICE_CURRENCY_CODE, FU.COST_PRICE, FU.EXCHANGE_RATE_DATE, FU.EXCHANGE_RATE_TYPE ))' ;
1828 l_func_call_string := 'OZF_SD_BATCH_PVT.CONV_AMT_TO_OFFER_CURR_AMOUNT(FU.PLAN_CURRENCY_CODE ,FU.DISCOUNT_AMOUNT_CURRENCY_CODE, FU.DISCOUNT_AMOUNT, FU.EXCHANGE_RATE_DATE, FU.EXCHANGE_RATE_TYPE )' ;
1829 l_shipped_qty_string := 'DECODE(OEL.LINE_CATEGORY_CODE, ''RETURN'' , -1 * abs(NVL(oel.shipped_quantity, NVL(oel.invoiced_quantity, NVL(oel.ordered_quantity, 1)))) ,
1830 NVL(oel.shipped_quantity, NVL(oel.invoiced_quantity, NVL(oel.ordered_quantity, 1))) ) ' ;
1831
1832 IF l_ozf_gl_entries = 'SHIPPED' then
1833 l_cost_price_string:= ('NVL( '|| l_cost_price_offer_string ||',OZF_SD_BATCH_PVT.CURR_ROUND_EXT_PREC(decode (FU.DISCOUNT_TYPE,
1834 ''%'', ( FU.PLAN_CURR_AMOUNT_REMAINING/' || l_shipped_qty_string || ' * (100/FU.DISCOUNT_AMOUNT) ),
1835 ''AMT'', null,
1836 ''NEWPRICE'', '|| l_func_call_string ||' + FU.PLAN_CURR_AMOUNT_REMAINING/ ' || l_shipped_qty_string || ' ),FU.PLAN_CURRENCY_CODE ) )');
1837 END IF;
1838
1839 IF l_ozf_gl_entries = 'INVOICED' then
1840 l_cost_price_string:= ('NVL( '|| l_cost_price_offer_string ||', OZF_SD_BATCH_PVT.CURR_ROUND_EXT_PREC(decode (FU.DISCOUNT_TYPE,
1841 ''%'', ( FU.PLAN_CURR_AMOUNT_REMAINING/NVL(oel.invoiced_quantity, NVL('|| l_shipped_qty_string ||',1)) * (100/FU.DISCOUNT_AMOUNT) ),
1842 ''AMT'', null,
1843 ''NEWPRICE'', '|| l_func_call_string ||' + FU.PLAN_CURR_AMOUNT_REMAINING/NVL(oel.invoiced_quantity, NVL('|| l_shipped_qty_string || ' , 1)) ),FU.PLAN_CURRENCY_CODE) )');
1844 END IF;
1845
1846 FND_DSQL.add_text(l_cost_price_string || 'COST_PRICE' );
1847
1848 FND_DSQL.add_text(', DECODE(FU.DISCOUNT_TYPE, ''AMT'', null, FU.PLAN_CURRENCY_CODE), ');
1849
1850 l_agmt_price_string:= ('decode (FU.DISCOUNT_TYPE,
1851 ''%'', ('|| l_cost_price_string ||' * (1 - FU.DISCOUNT_AMOUNT/100)),
1852 ''AMT'', null,
1853 ''NEWPRICE'', '|| l_func_call_string || ', ' ||
1854 l_func_call_string || ') ');
1855
1856 l_agmt_price_string:= 'OZF_SD_BATCH_PVT.CURR_ROUND_EXT_PREC('||l_agmt_price_string||', FU.PLAN_CURRENCY_CODE)';
1857
1858 FND_DSQL.add_text(l_agmt_price_string || 'APPROVED_DISCOUNT_VALUE' );
1859
1860 FND_DSQL.add_text(', FU.PLAN_CURR_AMOUNT_REMAINING, '); -- for claim_amount
1861
1862 FND_DSQL.add_text(' CASE WHEN (FU.PLAN_CURRENCY_CODE = ');
1863 FND_DSQL.add_bind(''||p_batch_currency||'' );
1864 FND_DSQL.add_text(' ) THEN FU.PLAN_CURR_AMOUNT_REMAINING WHEN (');
1865 FND_DSQL.add_bind(''||l_func_currency||'' );
1866 FND_DSQL.add_text('=');
1867 FND_DSQL.add_bind(''||p_batch_currency||'' );
1868 FND_DSQL.add_text(' ) THEN FU.ACCTD_AMOUNT_REMAINING ELSE OZF_SD_BATCH_PVT.GET_BATCH_CURRENCY_AMOUNT(');
1869 FND_DSQL.add_bind(''||l_func_currency||'' );
1870 FND_DSQL.add_text(',');
1871 FND_DSQL.add_bind(''||p_batch_currency||'' );
1872 FND_DSQL.add_text(', FU.ACCTD_AMOUNT_REMAINING, FU.EXCHANGE_RATE_TYPE, NULL,');
1873 FND_DSQL.add_bind(''||l_date||'' );
1874 FND_DSQL.add_text(') END BATCH_CURR_CLAIM_AMOUNT '); --for batch_curr_claim_amount
1875
1876 FND_DSQL.add_text(', FU.PRODUCT_ID, ');
1877
1878 FND_DSQL.add_text(' OEL.ORDER_QUANTITY_UOM, ');
1879
1880 IF l_ozf_gl_entries = 'SHIPPED' THEN
1881 --If accrual is negative for shipped profile, set the quantity as negative. This is required for RMA orders
1882 FND_DSQL.add_text(l_shipped_qty_string || ' , ');
1883 END IF;
1884
1885 IF l_ozf_gl_entries = 'INVOICED' THEN
1886 FND_DSQL.add_text('NVL(oel.invoiced_quantity, NVL('|| l_shipped_qty_string || ' ,1)), ');
1887 END IF;
1888
1889 FND_DSQL.add_text('OEH.ordered_date, ');
1890
1891 FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, ');
1892 FND_DSQL.add_text('FU.ACCTD_AMOUNT_REMAINING, ');
1893 FND_DSQL.add_text('FU.UNIV_CURR_AMOUNT_REMAINING, ');
1894 FND_DSQL.add_text('FU.FUND_REQUEST_AMOUNT_REMAINING, ');
1895 FND_DSQL.add_text('FU.AMOUNT_REMAINING, ');
1896 FND_DSQL.add_text('OEL.SHIP_FROM_ORG_ID, '); --OEL.SHIP_FROM_ORG_ID holds Inventory org id.
1897 FND_DSQL.add_text('FU.DISCOUNT_TYPE, ');
1898
1899 --if % or Amount use qpll.operand, for NewPrice use rounded agreeement price.
1900 FND_DSQL.add_text('DECODE(FU.DISCOUNT_TYPE,
1901 ''NEWPRICE'', ' || l_agmt_price_string || ',
1902 ''AMT'', '|| l_func_call_string ||',
1903 ''%'', FU.DISCOUNT_AMOUNT ) APPROVED_DISCOUNT_VALUE, ');
1904
1905 FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, ');
1906 FND_DSQL.add_text('ospa.SSD_DEC_ADJ_TYPE_ID ');
1907
1908 --BEGIN FROM CLAUSE
1909 FND_DSQL.add_text('FROM OZF_FUNDS_UTILIZED_ALL_B FU, ');
1910 FND_DSQL.add_text('OE_ORDER_HEADERS_ALL OEH, ');
1911 FND_DSQL.add_text('OE_ORDER_LINES_ALL OEL, ');
1912 FND_DSQL.add_text('OZF_SD_REQUEST_HEADERS_ALL_B RH, ');
1913 FND_DSQL.add_text('HZ_CUST_SITE_USES_ALL HZCSU, ');
1914 FND_DSQL.add_text('HZ_CUST_ACCT_SITES_ALL HZCAS, ');
1915 FND_DSQL.add_text('HZ_CUST_ACCOUNTS HZCA, ');
1916 FND_DSQL.add_text('OZF_SYS_PARAMETERS_ALL ospa ');
1917
1918 --BEGIN WHERE CLAUSE
1919 FND_DSQL.add_text('WHERE FU.PLAN_ID = RH.OFFER_ID AND ');
1920 FND_DSQL.add_text('FU.AMOUNT_REMAINING <> 0 AND ');
1921 FND_DSQL.add_text('FU.PLAN_TYPE = ''OFFR'' AND ');
1922 FND_DSQL.add_text('OEL.HEADER_ID = OEH.HEADER_ID AND ');
1923 FND_DSQL.add_text('RH.SUPPLIER_SITE_ID = ' );
1924 FND_DSQL.add_bind(p_supplier_site_id);
1925 FND_DSQL.add_text(' AND RH.ORG_ID = ' );
1926 FND_DSQL.add_bind(l_org_id);
1927
1928 FND_DSQL.add_text(' AND ospa.ORG_ID = ' );
1929 FND_DSQL.add_bind(l_org_id);
1930
1931 FND_DSQL.add_text(' AND OEH.invoice_to_org_id = HZCSU.SITE_USE_ID AND ');
1932 FND_DSQL.add_text('HZCSU.CUST_ACCT_SITE_ID = HZCAS.CUST_ACCT_SITE_ID AND ');
1933 FND_DSQL.add_text('HZCAS.CUST_ACCOUNT_ID = HZCA.CUST_ACCOUNT_ID AND ');
1934 FND_DSQL.add_text('FU.ORDER_LINE_ID = OEL.LINE_ID AND ');
1935 FND_DSQL.add_text('FU.GL_POSTED_FLAG = ''Y'' AND ');
1936
1937 IF l_ozf_gl_entries = 'SHIPPED' THEN
1938 FND_DSQL.add_text('oel.cancelled_flag = ''N'' AND oel.booked_flag = ''Y'' AND ');
1939 END IF;
1940
1941 IF l_ozf_gl_entries = 'INVOICED' THEN
1942 FND_DSQL.add_text('oel.cancelled_flag = ''N'' AND oel.booked_flag = ''Y'' AND oel.flow_status_code in (''CLOSED'',''INVOICED'') AND ');
1943 END IF;
1944
1945 FND_DSQL.add_text(' RH.OFFER_TYPE= ''ACCRUAL'' ');
1946 -- FND_DSQL.add_text(' RL.ORG_ID =' || l_org_id); -- AND '); --this was commented already
1947
1948 IF g_currency IS NULL THEN
1949 -- currency not set on trade profile
1950 FND_DSQL.add_text(' AND RH.REQUEST_CURRENCY_CODE = ' );
1951 FND_DSQL.add_bind('' || l_batch_currency || '');
1952 END IF;
1953
1954 --request ID filter
1955 IF p_request_id is not null then
1956 FND_DSQL.add_text(' AND RH.REQUEST_HEADER_ID = ' );
1957 FND_DSQL.add_bind(p_request_id);
1958 END IF;
1959
1960 --product filter
1961 IF p_product_id is not null then
1962 FND_DSQL.add_text(' AND FU.PRODUCT_ID = ' );
1963 FND_DSQL.add_bind(p_product_id);
1964 END IF;
1965
1966 -- offer filter
1967 IF p_fund_id is not null then
1968 FND_DSQL.add_text(' AND FU.fund_id = ' );
1969 FND_DSQL.add_bind(p_fund_id);
1970 END IF;
1971
1972 --start date filter
1973 IF p_start_date is not null then
1974 FND_DSQL.add_text(' AND FU.creation_date >=' );
1975 FND_DSQL.add_bind('' || p_start_date || '');
1976 END IF;
1977
1978 --end date filter
1979 IF p_end_date is not null then
1980 FND_DSQL.add_text(' AND TRUNC(FU.creation_date) <=' );
1981 FND_DSQL.add_bind('' || p_end_date || '');
1982 END IF;
1983
1984 --period filter
1985 IF p_period is not null then
1986 open get_period_limits(p_period);
1987 FETCH get_period_limits
1988 INTO l_period_start, l_period_end;
1989 CLOSE get_period_limits;
1990
1991 IF l_period_start is not null then
1992 FND_DSQL.add_text(' AND FU.creation_date >= ');
1993 FND_DSQL.add_bind(''||to_date(to_char(l_period_start,'DD-MM-YYYY'),'DD-MM-YYYY')||'');
1994 END IF;
1995
1996 IF l_period_end is not null then
1997 FND_DSQL.add_text(' AND FU.creation_date <= ');
1998 FND_DSQL.add_bind(''||to_date(to_char(l_period_end,'DD-MM-YYYY'),'DD-MM-YYYY')||'');
1999 END IF;
2000
2001 END IF;
2002
2003 -- attribute1 filter
2004 IF p_attribute1 is not null then
2005 FND_DSQL.add_text(' AND FU.ATTRIBUTE1 =' );
2006 FND_DSQL.add_bind('' || p_attribute1 || '');
2007 END IF;
2008
2009 -- attribute2 filter
2010 IF p_attribute2 is not null then
2011 FND_DSQL.add_text(' AND FU.ATTRIBUTE2 =' );
2012 FND_DSQL.add_bind('' || p_attribute2 || '');
2013 END IF;
2014
2015 -- attribute3 filter
2016 IF p_attribute3 is not null then
2017 FND_DSQL.add_text(' AND FU.ATTRIBUTE3 =' );
2018 FND_DSQL.add_bind('' || p_attribute3 || '');
2019 END IF;
2020
2021 -- attribute4 filter
2022 IF p_attribute4 is not null then
2023 FND_DSQL.add_text(' AND FU.ATTRIBUTE4 =' );
2024 FND_DSQL.add_bind('' || p_attribute4 || '');
2025 END IF;
2026
2027 -- attribute5 filter
2028 IF p_attribute5 is not null then
2029 FND_DSQL.add_text(' AND FU.ATTRIBUTE5 =' );
2030 FND_DSQL.add_bind('' || p_attribute5 || '');
2031 END IF;
2032
2033 -- attribute6 filter
2034 IF p_attribute6 is not null then
2035 FND_DSQL.add_text(' AND FU.ATTRIBUTE6 =' );
2036 FND_DSQL.add_bind('' || p_attribute6 || '');
2037 END IF;
2038
2039 -- attribute7 filter
2040 IF p_attribute7 is not null then
2041 FND_DSQL.add_text(' AND FU.ATTRIBUTE7 =' );
2042 FND_DSQL.add_bind('' || p_attribute7 || '');
2043 END IF;
2044
2045 -- attribute8 filter
2046 IF p_attribute8 is not null then
2047 FND_DSQL.add_text(' AND FU.ATTRIBUTE8 =' );
2048 FND_DSQL.add_bind('' || p_attribute8 || '');
2049 END IF;
2050
2051 -- attribute9 filter
2052 IF p_attribute9 is not null then
2053 FND_DSQL.add_text(' AND FU.ATTRIBUTE9 =' );
2054 FND_DSQL.add_bind('' || p_attribute9 || '');
2055 END IF;
2056
2057 -- attribute10 filter
2058 IF p_attribute10 is not null then
2059 FND_DSQL.add_text(' AND FU.ATTRIBUTE10 =' );
2060 FND_DSQL.add_bind('' || p_attribute10 || '');
2061 END IF;
2062
2063 -- attribute11 filter
2064 IF p_attribute11 is not null then
2065 FND_DSQL.add_text(' AND FU.ATTRIBUTE11 =' );
2066 FND_DSQL.add_bind('' || p_attribute11 || '');
2067 END IF;
2068
2069 -- attribute12 filter
2070 IF p_attribute12 is not null then
2071 FND_DSQL.add_text(' AND FU.ATTRIBUTE12 =' );
2072 FND_DSQL.add_bind('' || p_attribute12 || '');
2073 END IF;
2074
2075 -- attribute13 filter
2076 IF p_attribute13 is not null then
2077 FND_DSQL.add_text(' AND FU.ATTRIBUTE13 =' );
2078 FND_DSQL.add_bind('' || p_attribute13 || '');
2079 END IF;
2080
2081 -- attribute14 filter
2082 IF p_attribute14 is not null then
2083 FND_DSQL.add_text(' AND FU.ATTRIBUTE14 =' );
2084 FND_DSQL.add_bind('' || p_attribute14 || '');
2085 END IF;
2086
2087 -- attribute15 filter
2088 IF p_attribute15 is not null then
2089 FND_DSQL.add_text(' AND FU.ATTRIBUTE15 =' );
2090 FND_DSQL.add_bind('' || p_attribute15 || '');
2091 END IF;
2092
2093
2094 FND_DSQL.add_text(' FOR UPDATE OF FU.PLAN_CURR_AMOUNT_REMAINING NOWAIT' );
2095
2096
2097 --creating cursor
2098 l_lines_csr := DBMS_SQL.open_cursor;
2099 l_lines_sql := FND_DSQL.get_text(FALSE); -- Get SQL query built above
2100
2101 IF OZF_DEBUG_LOW_ON THEN
2102 FND_FILE.PUT_LINE(FND_FILE.LOG, '---Start Query Text ---');
2103 FND_FILE.PUT_LINE(FND_FILE.LOG,' l_lines_sql = ' || l_lines_sql);
2104 FND_FILE.PUT_LINE(FND_FILE.LOG, '---End Query Text ---');
2105 END IF;
2106
2107 IF OZF_DEBUG_LOW_ON THEN
2108 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '--- Values for Binds ---' );
2109 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' p_batch_currency = ' || p_batch_currency || ' p_batch_id = ' || p_batch_id || ' p_supplier_site_id = ' || p_supplier_site_id || ' l_org_id = ' || l_org_id );
2110 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' l_batch_currency = ' || l_batch_currency || ' p_request_id = ' || p_request_id || ' p_product_id = ' || p_product_id || ' p_fund_id = ' || p_fund_id );
2111 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' p_start_date = ' || p_start_date || ' p_end_date = ' || p_end_date || ' l_period_start = ' || l_period_start || ' l_period_end = ' || l_period_end );
2112 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' p_attribute1 = ' || p_attribute1 || ' p_attribute2 = ' || p_attribute2 || ' p_attribute3 = ' || p_attribute3 || ' p_attribute4 = ' || p_attribute4 || ' p_attribute5 = ' || p_attribute5 );
2113 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' p_attribute6 = ' || p_attribute6 || ' p_attribute7 = ' || p_attribute7 || ' p_attribute8 = ' || p_attribute8 || ' p_attribute9 = ' || p_attribute9 || ' p_attribute10 = ' || p_attribute10 );
2114 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' p_attribute11 = ' || p_attribute11 || ' p_attribute12 = ' || p_attribute12 || ' p_attribute13 = ' || p_attribute13 || ' p_attribute14 = ' || p_attribute14 || ' p_attribute15 = ' || p_attribute15 );
2115 END IF;
2116
2117 IF OZF_DEBUG_LOW_ON THEN
2118 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' --Start Query Text--');
2119 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' l_lines_sql = ' || l_lines_sql);
2120 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' --End Query Text --');
2121 END IF;
2122
2123 FND_DSQL.set_cursor(l_lines_csr);
2124 DBMS_SQL.parse(l_lines_csr, l_lines_sql, DBMS_SQL.native);
2125 FND_DSQL.do_binds;
2126
2127 --define columns
2128
2129 DBMS_SQL.define_column(l_lines_csr, 1, l_batch_line_id);
2130 DBMS_SQL.define_column(l_lines_csr, 2, l_utilization_id);
2131 DBMS_SQL.define_column(l_lines_csr, 3, l_agreement_number, 100);
2132 DBMS_SQL.define_column(l_lines_csr, 4, l_ship_to_org_id);
2133 DBMS_SQL.define_column(l_lines_csr, 5, l_ship_to_contact_id);
2134
2135 DBMS_SQL.define_column(l_lines_csr, 6, l_sold_to_customer_id);
2136 DBMS_SQL.define_column(l_lines_csr, 7, l_SOLD_TO_CONTACT_ID);
2137 DBMS_SQL.define_column(l_lines_csr, 8, l_SOLD_TO_SITE_USE_ID);
2138 DBMS_SQL.define_column(l_lines_csr, 9, l_end_customer_id);
2139 DBMS_SQL.define_column(l_lines_csr, 10, l_end_customer_contact_id);
2140
2141 DBMS_SQL.define_column(l_lines_csr, 11, l_order_header_id);
2142 DBMS_SQL.define_column(l_lines_csr, 12, l_order_line_number);
2143
2144 DBMS_SQL.define_column(l_lines_csr, 13, l_invoice_number);
2145 DBMS_SQL.define_column(l_lines_csr, 14, l_invoice_line_number);
2146
2147 DBMS_SQL.define_column(l_lines_csr, 15, l_resale_price_currency_code, 15);
2148 DBMS_SQL.define_column(l_lines_csr, 16, l_resales_price);
2149 DBMS_SQL.define_column(l_lines_csr, 17, l_list_price_currency_code, 15);
2150 DBMS_SQL.define_column(l_lines_csr, 18, l_list_price);
2151 DBMS_SQL.define_column(l_lines_csr, 19, l_agreement_currency_code, 15);
2152 DBMS_SQL.define_column(l_lines_csr, 20, l_agreement_price);
2153 DBMS_SQL.define_column(l_lines_csr, 21, l_claim_amount);
2154 DBMS_SQL.define_column(l_lines_csr, 22, l_batch_curr_claim_amount);
2155 DBMS_SQL.define_column(l_lines_csr, 23, l_item_id);
2156
2157 DBMS_SQL.define_column(l_lines_csr, 24, l_shipped_quantity_uom, 100);
2158 DBMS_SQL.define_column(l_lines_csr, 25, l_quantity_shipped);
2159 DBMS_SQL.define_column(l_lines_csr, 26, l_order_date);
2160 DBMS_SQL.define_column(l_lines_csr, 27, l_claim_amount_currency_code, 15);
2161 DBMS_SQL.define_column(l_lines_csr, 28, l_acct_amount_remaining);
2162 DBMS_SQL.define_column(l_lines_csr, 29, l_univ_curr_amount_remaining);
2163 DBMS_SQL.define_column(l_lines_csr, 30, l_fund_req_amount_remaining);
2164 DBMS_SQL.define_column(l_lines_csr, 31, l_amount_remaining);
2165 DBMS_SQL.define_column(l_lines_csr, 32, l_inv_org_id);
2166
2167 DBMS_SQL.define_column(l_lines_csr, 33, l_approved_discount_type,30);
2168 DBMS_SQL.define_column(l_lines_csr, 34, l_approved_discount_value);
2169 DBMS_SQL.define_column(l_lines_csr, 35, l_approved_discount_currency,15);
2170 DBMS_SQL.define_column(l_lines_csr, 36, l_adjustment_type_id);
2171
2172 --execute cursor
2173
2174 IF OZF_DEBUG_LOW_ON THEN
2175 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Cursor Execute Start time' || to_char(sysdate,'dd-mm-yyyy hh:mi:ss') );
2176 END IF;
2177
2178 l_ignore := DBMS_SQL.execute(l_lines_csr);
2179
2180 IF OZF_DEBUG_LOW_ON THEN
2181 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Cursor Execute End time' || to_char(sysdate,'dd-mm-yyyy hh:mi:ss') );
2182 END IF;
2183
2184 p_empty_batch := 'Y'; -- to check if any lines were created
2185 l_batch_line_number := 1;
2186
2187 LOOP
2188
2189 IF OZF_DEBUG_LOW_ON THEN
2190 FND_FILE.PUT_LINE(FND_FILE.LOG, 'DBMS_SQL.FETCH_ROWS loop - Before Fetch');
2191 END IF;
2192
2193 EXIT WHEN DBMS_SQL.FETCH_ROWS(l_lines_csr) = 0;
2194
2195 IF OZF_DEBUG_HIGH_ON THEN
2196 FND_FILE.PUT_LINE(FND_FILE.LOG, 'DBMS_SQL.FETCH_ROWS loop - After Fetch');
2197 END IF;
2198
2199 DBMS_SQL.column_value(l_lines_csr, 1, l_batch_line_id);
2200 DBMS_SQL.column_value(l_lines_csr, 2, l_utilization_id);
2201 DBMS_SQL.column_value(l_lines_csr, 3, l_agreement_number);
2202 DBMS_SQL.column_value(l_lines_csr, 4, l_ship_to_org_id);
2203 DBMS_SQL.column_value(l_lines_csr, 5, l_ship_to_contact_id);
2204
2205 DBMS_SQL.column_value(l_lines_csr, 6, l_sold_to_customer_id);
2206 DBMS_SQL.column_value(l_lines_csr, 7, l_SOLD_TO_CONTACT_ID);
2207 DBMS_SQL.column_value(l_lines_csr, 8, l_SOLD_TO_SITE_USE_ID);
2208 DBMS_SQL.column_value(l_lines_csr, 9, l_end_customer_id);
2209 DBMS_SQL.column_value(l_lines_csr, 10, l_end_customer_contact_id);
2210
2211 DBMS_SQL.column_value(l_lines_csr, 11, l_order_header_id);
2212 DBMS_SQL.column_value(l_lines_csr, 12, l_order_line_number);
2213
2214 DBMS_SQL.column_value(l_lines_csr, 13, l_invoice_number);
2215 DBMS_SQL.column_value(l_lines_csr, 14, l_invoice_line_number);
2216 DBMS_SQL.column_value(l_lines_csr, 15, l_resale_price_currency_code);
2217 DBMS_SQL.column_value(l_lines_csr, 16, l_resales_price);
2218 DBMS_SQL.column_value(l_lines_csr, 17, l_list_price_currency_code);
2219 DBMS_SQL.column_value(l_lines_csr, 18, l_list_price);
2220 DBMS_SQL.column_value(l_lines_csr, 19, l_agreement_currency_code);
2221 DBMS_SQL.column_value(l_lines_csr, 20, l_agreement_price);
2222 DBMS_SQL.column_value(l_lines_csr, 21, l_claim_amount);
2223 DBMS_SQL.column_value(l_lines_csr, 22, l_batch_curr_claim_amount);
2224 DBMS_SQL.column_value(l_lines_csr, 23, l_item_id);
2225
2226 DBMS_SQL.column_value(l_lines_csr, 24, l_shipped_quantity_uom);
2227 DBMS_SQL.column_value(l_lines_csr, 25, l_quantity_shipped);
2228 DBMS_SQL.column_value(l_lines_csr, 26, l_order_date);
2229 DBMS_SQL.column_value(l_lines_csr, 27, l_claim_amount_currency_code);
2230 DBMS_SQL.column_value(l_lines_csr, 28, l_acct_amount_remaining);
2231 DBMS_SQL.column_value(l_lines_csr, 29, l_univ_curr_amount_remaining);
2232 DBMS_SQL.column_value(l_lines_csr, 30, l_fund_req_amount_remaining);
2233 DBMS_SQL.column_value(l_lines_csr, 31, l_amount_remaining);
2234 DBMS_SQL.column_value(l_lines_csr, 32, l_inv_org_id);
2235 DBMS_SQL.column_value(l_lines_csr, 33, l_approved_discount_type);
2236 DBMS_SQL.column_value(l_lines_csr, 34, l_approved_discount_value);
2237 DBMS_SQL.column_value(l_lines_csr, 35, l_approved_discount_currency);
2238 DBMS_SQL.column_value(l_lines_csr, 36, l_adjustment_type_id);
2239
2240 IF OZF_DEBUG_HIGH_ON THEN
2241 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Check for Line Amount Threshold ---');
2242 FND_FILE.PUT_LINE(FND_FILE.LOG,
2243 'l_thresh_line_limit = ' ||
2244 to_char(l_thresh_line_limit) ||
2245 'claim amount: = ' ||
2246 to_char(l_batch_curr_claim_amount));
2247 END IF;
2248 IF ( nvl(l_thresh_line_limit, l_batch_curr_claim_amount - 1) <
2249 l_batch_curr_claim_amount AND
2250 g_currency IS NOT NULL ) OR
2251 g_currency IS NULL OR
2252 l_batch_curr_claim_amount < 0 THEN
2253
2254 p_empty_batch := 'N';
2255 IF OZF_DEBUG_LOW_ON THEN
2256 FND_FILE.PUT_LINE(FND_FILE.LOG,
2257 '--- Values fetched for Batch Line ---');
2258 FND_FILE.PUT_LINE(FND_FILE.LOG,
2259 'values being fetched from SQL' ||
2260 to_char(l_batch_line_id) || '*' || -- line sequence.nextval
2261 to_char(1) || '*' || to_char(l_batch_id) || '*' ||
2262 to_char(l_batch_line_number) || '*' ||
2263 to_char(l_utilization_id) || '*' ||
2264 to_char(l_agreement_number) || '*' ||
2265 to_char(l_ship_to_org_id) || '*' ||
2266 to_char(l_ship_to_contact_id) || '*' ||
2267 to_char(l_sold_to_customer_id) || '*' ||
2268 to_char(l_SOLD_TO_CONTACT_ID) || '*' ||
2269 to_char(l_SOLD_TO_SITE_USE_ID) || '*' ||
2270 to_char(l_end_customer_id) || '*' ||
2271 to_char(l_end_customer_contact_id) || '*' ||
2272
2273 to_char(l_order_header_id) || '*' ||
2274 to_char(l_order_line_number) || '*' ||
2275
2276 to_char(l_invoice_number) || '*' ||
2277 to_char(l_invoice_line_number) || '*' ||
2278 to_char(l_resale_price_currency_code) || '*' ||
2279 to_char(l_resales_price) || '*' ||
2280 to_char(l_list_price_currency_code) || '*' ||
2281 to_char(l_list_price) || '*' ||
2282 to_char(l_agreement_currency_code) || '*' ||
2283 to_char(l_agreement_price) || '*' ||
2284 to_char('NEW') || '*' || -- status is 'new'
2285
2286 to_char(l_claim_amount) || '*' ||
2287 to_char(l_batch_curr_claim_amount) || '*' ||
2288 to_char(l_item_id) || '*' ||
2289 to_char(l_batch_curr_claim_amount) || '*' ||
2290 to_char(l_shipped_quantity_uom) || '*' ||
2291 to_char(l_quantity_shipped) || '*' ||
2292 to_char(l_claim_amount_currency_code) || '*' ||
2293 to_char(l_acct_amount_remaining) || '*' ||
2294 to_char(l_univ_curr_amount_remaining) || '*' ||
2295 to_char(l_fund_req_amount_remaining) || '*' ||
2296 to_char(l_amount_remaining) || '*' ||
2297 to_char('Y') || '*' || to_char(l_order_date) || '*' ||
2298 to_char(sysdate) || '*' || to_char(sysdate) || '*' ||
2299 to_char(FND_GLOBAL.USER_ID) || '*' ||
2300 to_char(FND_GLOBAL.CONC_REQUEST_ID) || '*' ||
2301 to_char(FND_GLOBAL.USER_ID) || '*' ||
2302 to_char(FND_GLOBAL.CONC_LOGIN_ID) || '*' ||
2303 to_char(FND_GLOBAL.PROG_APPL_ID) || '*' ||
2304 to_char(null) || '*' ||
2305 to_char(FND_GLOBAL.CONC_PROGRAM_ID) || '*' ||
2306 to_char(l_org_id) || '*' ||
2307 to_char(l_inv_org_id) || '*' ||
2308 to_char(l_approved_discount_type) || '*' ||
2309 to_char(l_approved_discount_value) || '*' ||
2310 to_char(l_approved_discount_currency) || '*' ||
2311 to_char(l_adjustment_type_id) || '*' ||
2312 p_order_source);
2313 END IF;
2314
2315 IF OZF_DEBUG_LOW_ON THEN
2316 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting INTO ozf_sd_batch_lines_all');
2317 END IF;
2318
2319 IF OZF_DEBUG_LOW_ON THEN
2320 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert into batch lines: start time:' || to_char(sysdate,'dd-mm-yyyy hh:mi:ss') );
2321 END IF;
2322
2323 INSERT INTO OZF_SD_BATCH_LINES_ALL
2324 (batch_line_id,
2325 object_version_number,
2326 batch_id,
2327 batch_line_number,
2328 utilization_id,
2329 agreement_number,
2330 ship_to_org_id,
2331 ship_to_contact_id,
2332
2333 sold_to_customer_id,
2334 sold_to_contact_id,
2335 sold_to_site_use_id,
2336 end_customer_id,
2337 end_customer_contact_id,
2338
2339 order_header_id,
2340 order_line_id,
2341
2342 invoice_number,
2343 invoice_line_number,
2344 resale_price_currency_code,
2345 resales_price,
2346 list_price_currency_code,
2347 list_price,
2348 agreement_currency_code,
2349 agreement_price,
2350 status_code,
2351
2352 claim_amount,
2353 claim_amount_currency_code,
2354 batch_curr_claim_amount,
2355
2356 original_claim_amount,
2357 batch_curr_orig_claim_amount,
2358
2359 item_id,
2360 vendor_item_id,
2361 shipped_quantity_uom,
2362 last_sub_claim_amount,
2363 acctd_amount_remaining,
2364 univ_curr_amount_remaining,
2365 fund_request_amount_remaining,
2366 amount_remaining,
2367 quantity_shipped,
2368 purge_flag,
2369 order_date,
2370 creation_date,
2371 last_update_date,
2372 last_updated_by,
2373 request_id,
2374 created_by,
2375
2376 last_update_login,
2377 program_application_id,
2378 program_update_date,
2379 program_id,
2380 org_id,
2381 transmit_flag,
2382
2383 discount_type,
2384 discount_value,
2385 discount_currency_code,
2386 adjustment_type_id,
2387 order_source
2388 )
2389 VALUES
2390 (l_batch_line_id,
2391 1,
2392 l_batch_id,
2393 l_batch_line_number,
2394 l_utilization_id,
2395 l_agreement_number,
2396 l_ship_to_org_id,
2397 l_ship_to_contact_id,
2398
2399 l_sold_to_customer_id,
2400 l_SOLD_TO_CONTACT_ID,
2401 l_SOLD_TO_SITE_USE_ID,
2402 l_end_customer_id,
2403 l_end_customer_contact_id,
2404
2405 l_order_header_id,
2406 l_order_line_number,
2407
2408 l_invoice_number,
2409 l_invoice_line_number,
2410
2411 l_resale_price_currency_code, -- from orders
2412 l_resales_price,
2413 l_list_price_currency_code, --purchase price from sdr
2414 l_list_price,
2415 l_agreement_currency_code, --agreement price from sdr
2416 l_agreement_price,
2417 'NEW',
2418
2419 l_claim_amount, --claim amount from funds accrual
2420 l_claim_amount_currency_code,
2421 l_batch_curr_claim_amount,
2422
2423 l_claim_amount, -- for original_claim_amount
2424 l_batch_curr_claim_amount, -- for batch_curr_orig_claim_amount
2425
2426 l_item_id,
2427 get_vendor_item_id(l_item_id, p_supplier_site_id),
2428
2429 l_shipped_quantity_uom,
2430 null,
2431 l_acct_amount_remaining,
2432 l_univ_curr_amount_remaining,
2433 l_fund_req_amount_remaining,
2434 l_amount_remaining,
2435 l_quantity_shipped,
2436
2437 'N', -- l_active_flag
2438 l_order_date, -- from OE order lines/header
2439 sysdate, --l_creation_date,
2440 sysdate, --l_last_update_date,
2441 FND_GLOBAL.USER_ID, --l_last_updated_by,
2442 FND_GLOBAL.CONC_REQUEST_ID, --l_request_id,
2443 FND_GLOBAL.USER_ID, --l_created_by,
2444 --l_created_from,
2445 FND_GLOBAL.CONC_LOGIN_ID, --l_last_update_login,
2446 FND_GLOBAL.PROG_APPL_ID, --l_program_application_id,
2447 null, --l_program_update_date,
2448 FND_GLOBAL.CONC_PROGRAM_ID, --l_program_id,
2449 l_inv_org_id,
2450 'Y',
2451
2452 l_approved_discount_type,
2453 l_approved_discount_value,
2454 l_approved_discount_currency,
2455
2456 l_adjustment_type_id ,
2457 p_order_source
2458 );
2459
2460 IF OZF_DEBUG_LOW_ON THEN
2461 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert into batch lines: end time:' || to_char(sysdate,'dd-mm-yyyy hh:mi:ss') );
2462 END IF;
2463
2464 IF OZF_DEBUG_LOW_ON THEN
2465 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserted INTO ozf_sd_batch_lines_all');
2466 END IF;
2467
2468 l_batch_line_number := l_batch_line_number + 1;
2469 END IF;
2470
2471 END LOOP;
2472 EXCEPTION
2473
2474 WHEN resource_busy THEN
2475
2476 IF OZF_DEBUG_HIGH_ON THEN
2477 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in CREATE_OM_BATCH_LINES : ' || SQLERRM);
2478 END IF;
2479
2480 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Exception in CREATE_OM_BATCH_LINES : ' || SQLERRM);
2481
2482 RAISE ;
2483
2484
2485 WHEN OTHERS THEN
2486
2487 IF OZF_DEBUG_HIGH_ON THEN
2488 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in CREATE_OM_BATCH_LINES : ' || SQLERRM);
2489 END IF;
2490
2491 RAISE FND_API.g_exc_error;
2492
2493 END CREATE_OM_BATCH_LINES;
2494
2495
2496 PROCEDURE CREATE_IDSM_BATCH_LINES(p_batch_id IN NUMBER,
2497 p_supplier_id IN NUMBER,
2498 p_supplier_site_id IN NUMBER,
2499 p_org_id IN NUMBER,
2500 p_thresh_line_limit IN NUMBER,
2501 p_batch_currency IN VARCHAR2,
2502 p_product_id IN NUMBER,
2503 p_request_id IN NUMBER,
2504 p_fund_id IN NUMBER,
2505 p_start_date IN DATE,
2506 p_end_date IN DATE,
2507 p_period IN VARCHAR2,
2508 p_empty_batch OUT NOCOPY VARCHAR2,
2509 p_attribute1 IN VARCHAR2 := NULL,
2510 p_attribute2 IN VARCHAR2 := NULL,
2511 p_attribute3 IN VARCHAR2 := NULL,
2512 p_attribute4 IN VARCHAR2 := NULL,
2513 p_attribute5 IN VARCHAR2 := NULL,
2514 p_attribute6 IN VARCHAR2 := NULL,
2515 p_attribute7 IN VARCHAR2 := NULL,
2516 p_attribute8 IN VARCHAR2 := NULL,
2517 p_attribute9 IN VARCHAR2 := NULL,
2518 p_attribute10 IN VARCHAR2 := NULL,
2519 p_attribute11 IN VARCHAR2 := NULL,
2520 p_attribute12 IN VARCHAR2 := NULL,
2521 p_attribute13 IN VARCHAR2 := NULL,
2522 p_attribute14 IN VARCHAR2 := NULL,
2523 p_attribute15 IN VARCHAR2 := NULL,
2524 p_order_source IN VARCHAR2) is
2525
2526 l_batch_id NUMBER := NULL;
2527 l_supplier_id NUMBER := NULL;
2528 l_supplier_site_id NUMBER := NULL;
2529 l_org_id NUMBER := NULL;
2530 l_inv_org_id NUMBER := NULL;
2531 l_count NUMBER := 0;
2532
2533 --for cursor execution
2534 l_lines_csr NUMBER := NULL;
2535 l_lines_sql VARCHAR2(10000) := NULL;
2536 l_ignore NUMBER;
2537
2538 -- for define columns
2539 l_batch_line_id NUMBER := NULL;
2540 l_batch_line_number NUMBER := NULL;
2541 l_utilization_id NUMBER := NULL;
2542 l_agreement_number VARCHAR2(100) := NULL;
2543 l_ship_to_org_id NUMBER := NULL;
2544 l_ship_to_contact_id NUMBER := NULL;
2545 l_ship_to_customer_site_id NUMBER := NULL;
2546 l_sold_to_customer_id NUMBER := NULL;
2547 l_SOLD_TO_CONTACT_ID NUMBER := NULL;
2548 l_SOLD_TO_SITE_USE_ID NUMBER := NULL;
2549 l_end_customer_id NUMBER := NULL;
2550 l_end_customer_contact_id NUMBER := NULL;
2551 l_end_customer_site_id NUMBER := NULL;
2552 l_order_header_id NUMBER := NULL;
2553 l_order_line_number NUMBER := NULL;
2554 l_invoice_number NUMBER := NULL;
2555 l_invoice_line_number NUMBER := NULL;
2556 l_resale_price_currency_code VARCHAR2(15) := NULL;
2557 l_resales_price NUMBER := NULL;
2558 l_list_price_currency_code VARCHAR2(15) := NULL;
2559 l_list_price NUMBER := NULL;
2560 l_agreement_currency_code VARCHAR2(15) := NULL;
2561 l_agreement_price NUMBER := NULL;
2562
2563 l_claim_amount NUMBER := NULL;
2564 l_batch_curr_claim_amount NUMBER := NULL;
2565
2566 l_orig_claim_amount NUMBER := NULL;
2567 l_batch_curr_orig_claim_amount NUMBER := NULL;
2568
2569 l_item_id NUMBER := NULL;
2570 l_vendor_item_id NUMBER := NULL;
2571 l_shipped_quantity_uom VARCHAR2(100) := NULL;
2572 l_quantity_shipped NUMBER := NULL;
2573 l_order_date DATE := NULL;
2574 l_claim_amount_currency_code VARCHAR2(15) := NULL;
2575 l_acct_amount_remaining NUMBER := NULL;
2576 l_univ_curr_amount_remaining NUMBER := NULL;
2577 l_fund_req_amount_remaining NUMBER := NULL;
2578 l_amount_remaining NUMBER := NULL;
2579 l_ozf_gl_entries VARCHAR2(15) := NULL;
2580 l_approved_discount_type VARCHAR2(30) := NULL;
2581 l_approved_discount_value NUMBER := NULL;
2582 l_approved_discount_currency VARCHAR2(15) := NULL;
2583 l_adjustment_type_id NUMBER := NULL;
2584
2585 --from trade profile
2586 l_thresh_line_limit NUMBER := NULL;
2587 l_batch_currency VARCHAR2(15) := NULL;
2588
2589 --parameters
2590 l_fund_id NUMBER := NULL;
2591 l_start_date DATE;
2592 l_end_date DATE := NULL;
2593 l_period VARCHAR2(50) := NULL;
2594 l_period_start DATE := NULL;
2595 l_period_end DATE := NULL;
2596 l_discount_amount_string VARCHAR2(2000) := NULL;
2597 l_qty_string VARCHAR2(2000) := NULL;
2598 l_cost_price_string VARCHAR2(2000) := NULL;
2599 l_rounded_cost_price VARCHAR2(2000) := NULL;
2600 l_agmt_price_string VARCHAR2(2000) := NULL;
2601 l_shipped_qty_string VARCHAR2(2000) := NULL;
2602
2603 -- to get functional currency
2604 l_func_currency VARCHAR2(15) := NULL;
2605 l_date DATE;
2606
2607 --Start:Added for Bug#10011106 fix
2608 l_cost_price_offer_string VARCHAR2(2000) := NULL;
2609 --End:Added for Bug#10011106 fix
2610
2611 resource_busy EXCEPTION;
2612 PRAGMA EXCEPTION_INIT (resource_busy, -54);
2613
2614 --cursor for period param
2615 CURSOR get_period_limits(c_period VARCHAR2) IS
2616 SELECT start_date, end_date
2617 FROM gl_periods
2618 WHERE period_name = c_period and
2619 period_set_name =
2620 fnd_profile.value('AMS_CAMPAIGN_DEFAULT_CALENDER');
2621
2622 BEGIN
2623 l_batch_id := p_batch_id;
2624 l_org_id := p_org_id;
2625 l_thresh_line_limit := p_thresh_line_limit;
2626 l_batch_currency := p_batch_currency;
2627
2628 l_fund_id := p_fund_id;
2629 l_start_date := p_start_date;
2630 l_end_date := p_end_date;
2631 l_period := p_period;
2632 l_ozf_gl_entries := fnd_profile.value('OZF_ORDER_GLPOST_PHASE');
2633 l_date := sysdate;
2634
2635 IF l_ozf_gl_entries is null then
2636 l_ozf_gl_entries := 'SHIPPED';
2637 END IF;
2638
2639 SELECT gs.currency_code
2640 INTO l_func_currency
2641 FROM gl_sets_of_books gs,
2642 ozf_sys_parameters_all org,
2643 ozf_sd_batch_headers_all bh
2644 WHERE org.set_of_books_id = gs.set_of_books_id
2645 AND org.org_id = bh.org_id
2646 AND bh.batch_id = p_batch_id;
2647
2648 FND_DSQL.init;
2649
2650 FND_DSQL.add_text('SELECT ');
2651 FND_DSQL.add_text('OZF_SD_BATCH_LINES_ALL_S.NEXTVAL , ');
2652 FND_DSQL.add_text('FU.UTILIZATION_ID, ');
2653 FND_DSQL.add_text('RH.AUTHORIZATION_NUMBER, ');
2654 FND_DSQL.add_text('NULL, '); --SHIP_TO_ORG_ID
2655 FND_DSQL.add_text('NULL, '); -- SHIP_TO_CONTACT_ID
2656 FND_DSQL.add_text('ORL.BILL_TO_PARTY_ID, '); --SOLD_TO_CUSTOMER_ID,
2657 FND_DSQL.add_text('NULL, '); --SOLD_TO_CONTACT_ID
2658 FND_DSQL.add_text('NULL, ');--SOLD_TO_SITE_USE_ID
2659 FND_DSQL.add_text('ORL.END_CUST_PARTY_ID, '); --END_CUSTOMER_ID
2660 FND_DSQL.add_text('NULL, '); -- END_CUSTOMER_CONTACT_ID
2661 FND_DSQL.add_text('ORL.resale_header_id, '); -- ORDER_HEADER_ID
2662 FND_DSQL.add_text('ORL.resale_line_id, '); -- ORDER_LINE_ID
2663 FND_DSQL.add_text('null TRX_NUMBER,'); --INVOICE_NUMBER
2664 FND_DSQL.add_text('NULL LINE_NUMBER, '); --INVOICE_LINE_NUMBER
2665
2666 --Compute Resale Price
2667 FND_DSQL.add_text('ORL.currency_code, '); --TRANSACTIONAL_CURR_CODE
2668 /* Resale Price Computation logic
2669 For direct resale order, OZF_RESALE_LINES_ALL.SELLING_PRICE
2670 For indirect resale order,if OZF_RESALE_LINES_ALL.purchase_uom_code and OZF_RESALE_LINES_ALL .uom_code are same then ORL.purchase_price
2671 For indirect resale order,if OZF_RESALE_LINES_ALL.purchase_uom_code and OZF_RESALE_LINES_ALL .uom_code are different
2672 then ORL.purchase_price should be converted
2673 Inventory Utility function for UOM conversion:
2674 number inv_um_convert(item_id, precision, from_quantity,from_unit, to_unit,from_name, to_name)
2675 */
2676 FND_DSQL.add_text('CASE WHEN (ORB.DIRECT_ORDER_FLAG = ''Y'') THEN ORL.selling_price
2677 WHEN ORB.DIRECT_ORDER_FLAG <> ''Y'' AND ORL.UOM_CODE = ORL.PURCHASE_UOM_CODE THEN ORL.purchase_price
2678 WHEN (ORB.DIRECT_ORDER_FLAG <> ''Y'' AND ORL.UOM_CODE <> ORL.PURCHASE_UOM_CODE) THEN
2679 (NVL(ORL.purchase_price,0))/NVL(inv_convert.inv_um_convert(ORL.inventory_item_id, null,
2680 orl.quantity,ORL.PURCHASE_UOM_CODE, ORL.UOM_CODE,null,null),1)
2681 END unit_selling_price, '); --UNIT_SELLING_PRICE
2682
2683 --Compute cost price
2684 FND_DSQL.add_text('DECODE(FU.DISCOUNT_TYPE, ''AMT'', null, FU.PLAN_CURRENCY_CODE), '); --LIST_PRICE_CURRENCY_CODE
2685
2686 -- Call CONV_AMT_TO_OFFER_CURR_AMOUNT where
2687 -- To Currency Offer CURRENCY_CODE
2688 -- From Currency FU.DISCOUNT_AMOUNT_CURRENCY_CODE
2689 -- From Amount FU.DISCOUNT_AMOUNT
2690 -- Date Of conversion FU.EXCHANGE_RATE_DATE
2691 -- Type of Conversion FU.EXCHANGE_RATE_TYPE
2692
2693 l_cost_price_offer_string := 'decode(FU.DISCOUNT_TYPE, ''AMT'', NULL,
2694 OZF_SD_BATCH_PVT.CONV_AMT_TO_OFFER_CURR_AMOUNT(FU.PLAN_CURRENCY_CODE ,
2695 FU.COST_PRICE_CURRENCY_CODE,
2696 FU.COST_PRICE,
2697 FU.EXCHANGE_RATE_DATE,
2698 FU.EXCHANGE_RATE_TYPE ))';
2699 FND_DSQL.add_text(l_cost_price_offer_string || 'COST_PRICE' );
2700
2701 l_discount_amount_string := 'OZF_SD_BATCH_PVT.CONV_AMT_TO_OFFER_CURR_AMOUNT(FU.PLAN_CURRENCY_CODE ,
2702 FU.DISCOUNT_AMOUNT_CURRENCY_CODE,
2703 FU.DISCOUNT_AMOUNT,
2704 FU.EXCHANGE_RATE_DATE,
2705 FU.EXCHANGE_RATE_TYPE )' ;
2706 l_qty_string := 'decode(ORL. resale_transfer_type, ''BN'', -1 * ABS(nvl(orl. Quantity,1) ),nvl(orl. Quantity,1))' ;
2707
2708 FND_DSQL.add_text(', DECODE(FU.DISCOUNT_TYPE, ''AMT'', null, FU.PLAN_CURRENCY_CODE), '); --agreement_price_currency_code
2709
2710 l_agmt_price_string:= ('decode (FU.DISCOUNT_TYPE,
2711 ''%'', ('|| l_cost_price_string ||' * (1 - FU.DISCOUNT_AMOUNT/100)),
2712 ''AMT'', null,
2713 ''NEWPRICE'', '|| l_discount_amount_string || ', ' || l_discount_amount_string || ') ');
2714
2715 l_agmt_price_string:= ('decode (FU.DISCOUNT_TYPE,
2716 ''%'', ('|| l_cost_price_offer_string ||' * (1 - FU.DISCOUNT_AMOUNT/100)),
2717 ''AMT'', null,
2718 ''NEWPRICE'', '|| l_discount_amount_string || ', ' ||
2719 l_discount_amount_string || ') ');
2720
2721 l_agmt_price_string:= 'OZF_SD_BATCH_PVT.CURR_ROUND_EXT_PREC('||l_agmt_price_string||', FU.PLAN_CURRENCY_CODE)';
2722
2723 FND_DSQL.add_text(l_agmt_price_string || 'AGREEMENT_PRICE' );
2724
2725 FND_DSQL.add_text(', FU.PLAN_CURR_AMOUNT_REMAINING, '); -- for claim_amount
2726
2727 FND_DSQL.add_text(' CASE WHEN (FU.PLAN_CURRENCY_CODE = ');
2728 FND_DSQL.add_bind(''||p_batch_currency||'' );
2729 FND_DSQL.add_text(' ) THEN FU.PLAN_CURR_AMOUNT_REMAINING WHEN (');
2730 FND_DSQL.add_bind(''||l_func_currency||'' );
2731 FND_DSQL.add_text('=');
2732 FND_DSQL.add_bind(''||p_batch_currency||'' );
2733 FND_DSQL.add_text(' ) THEN FU.ACCTD_AMOUNT_REMAINING ELSE OZF_SD_BATCH_PVT.GET_BATCH_CURRENCY_AMOUNT(');
2734 FND_DSQL.add_bind(''||l_func_currency||'' );
2735 FND_DSQL.add_text(',');
2736 FND_DSQL.add_bind(''||p_batch_currency||'' );
2737 FND_DSQL.add_text(', FU.ACCTD_AMOUNT_REMAINING, FU.EXCHANGE_RATE_TYPE, NULL,');
2738 FND_DSQL.add_bind(''||l_date||'' );
2739 FND_DSQL.add_text(') END BATCH_CURR_CLAIM_AMOUNT '); --for batch_curr_claim_amount
2740
2741 FND_DSQL.add_text(', FU.PRODUCT_ID, ');
2742
2743 FND_DSQL.add_text('ORL.UOM_CODE, ');
2744
2745 FND_DSQL.add_text(l_qty_string || ' , ');
2746
2747 FND_DSQL.add_text(' ORL.DATE_ORDERED, '); --ORL.DATE_ORDERED will pick from resale table on the fly
2748
2749
2750 FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, ');
2751 FND_DSQL.add_text('FU.ACCTD_AMOUNT_REMAINING, ');
2752 FND_DSQL.add_text('FU.UNIV_CURR_AMOUNT_REMAINING, ');
2753 FND_DSQL.add_text('FU.FUND_REQUEST_AMOUNT_REMAINING, ');
2754 FND_DSQL.add_text('FU.AMOUNT_REMAINING, ');
2755 FND_DSQL.add_text(l_idsm_inv_org ||' , '); --OEL.SHIP_FROM_ORG_ID holds Inventory org id.The profile AMS_ITEM_ORGANIZATION_ID is used for inventory org in IDSM
2756 FND_DSQL.add_text('FU.DISCOUNT_TYPE, ');
2757
2758 --if % or Amount use qpll.operand, for NewPrice use rounded agreeement price.
2759 FND_DSQL.add_text('DECODE(FU.DISCOUNT_TYPE,''NEWPRICE'', ' || l_agmt_price_string || ',
2760 ''AMT'', '|| l_discount_amount_string ||',
2761 ''%'', FU.DISCOUNT_AMOUNT ) APPROVED_DISCOUNT_VALUE, ');
2762
2763 FND_DSQL.add_text('FU.PLAN_CURRENCY_CODE, '); --approved discount currency
2764 FND_DSQL.add_text('ospa.SSD_DEC_ADJ_TYPE_ID ');
2765
2766 --BEGIN FROM CLAUSE
2767 FND_DSQL.add_text('FROM OZF_FUNDS_UTILIZED_ALL_B FU, ');
2768 FND_DSQL.add_text('OZF_RESALE_LINES_ALL ORL, ');
2769 FND_DSQL.add_text('OZF_RESALE_BATCH_LINE_MAPS_ALL ORBMP, ');
2770 FND_DSQL.add_text('OZF_RESALE_BATCHES_ALL ORB, ');
2771 FND_DSQL.add_text('OZF_SD_REQUEST_HEADERS_ALL_B RH, ');
2772 FND_DSQL.add_text('OZF_SYS_PARAMETERS_ALL ospa ');
2773
2774 --BEGIN WHERE CLAUSE
2775 FND_DSQL.add_text('WHERE FU.PLAN_ID = RH.OFFER_ID AND ');
2776 FND_DSQL.add_text('FU.AMOUNT_REMAINING <> 0 AND ');
2777 FND_DSQL.add_text('FU.PLAN_TYPE = ''OFFR'' AND ');
2778 FND_DSQL.add_text('FU.OBJECT_ID = ORL.RESALE_LINE_ID AND ');
2779 FND_DSQL.add_text(' ORL.RESALE_LINE_ID = ORBMP.RESALE_LINE_ID AND ');
2780 FND_DSQL.add_text(' ORBMP.RESALE_BATCH_ID = ORB.RESALE_BATCH_ID AND ');
2781 FND_DSQL.add_text('FU.OBJECT_TYPE = ''TP_ORDER'' AND ');
2782 FND_DSQL.add_text('RH.SUPPLIER_SITE_ID = ' );
2783 FND_DSQL.add_bind(p_supplier_site_id);
2784 FND_DSQL.add_text(' AND RH.ORG_ID = ' );
2785 FND_DSQL.add_bind(l_org_id);
2786
2787 FND_DSQL.add_text(' AND ospa.ORG_ID = ' );
2788 FND_DSQL.add_bind(l_org_id);
2789 FND_DSQL.add_text(' AND FU.GL_POSTED_FLAG = ''Y'' ');
2790
2791 IF g_currency IS NULL THEN
2792 -- currency not set on trade profile
2793 FND_DSQL.add_text(' AND RH.REQUEST_CURRENCY_CODE = ' );
2794 FND_DSQL.add_bind('' || l_batch_currency || '');
2795 END IF;
2796
2797 --request ID filter
2798 IF p_request_id is not null then
2799 FND_DSQL.add_text(' AND RH.REQUEST_HEADER_ID = ' );
2800 FND_DSQL.add_bind(p_request_id);
2801 END IF;
2802
2803 --product filter
2804 IF p_product_id is not null then
2805 FND_DSQL.add_text(' AND FU.PRODUCT_ID = ' );
2806 FND_DSQL.add_bind(p_product_id);
2807 END IF;
2808
2809 -- offer filter
2810 IF p_fund_id is not null then
2811 FND_DSQL.add_text(' AND FU.fund_id = ' );
2812 FND_DSQL.add_bind(p_fund_id);
2813 END IF;
2814
2815 --start date filter
2816 IF p_start_date is not null then
2817 FND_DSQL.add_text(' AND FU.creation_date >=' );
2818 FND_DSQL.add_bind('' || p_start_date || '');
2819 END IF;
2820
2821 --end date filter
2822 IF p_end_date is not null then
2823 FND_DSQL.add_text(' AND TRUNC(FU.creation_date) <=' );
2824 FND_DSQL.add_bind('' || p_end_date || '');
2825 END IF;
2826
2827 --period filter
2828 IF p_period is not null then
2829 open get_period_limits(p_period);
2830 FETCH get_period_limits
2831 INTO l_period_start, l_period_end;
2832 CLOSE get_period_limits;
2833
2834 IF l_period_start is not null then
2835 FND_DSQL.add_text(' AND FU.creation_date >= ');
2836 FND_DSQL.add_bind(''||to_date(to_char(l_period_start,'DD-MM-YYYY'),'DD-MM-YYYY')||'');
2837 END IF;
2838
2839 IF l_period_end is not null then
2840 FND_DSQL.add_text(' AND FU.creation_date <= ');
2841 FND_DSQL.add_bind(''||to_date(to_char(l_period_end,'DD-MM-YYYY'),'DD-MM-YYYY')||'');
2842 END IF;
2843
2844 END IF;
2845
2846 -- attribute1 filter
2847 IF p_attribute1 is not null then
2848 FND_DSQL.add_text(' AND FU.ATTRIBUTE1 =' );
2849 FND_DSQL.add_bind('' || p_attribute1 || '');
2850 END IF;
2851
2852 -- attribute2 filter
2853 IF p_attribute2 is not null then
2854 FND_DSQL.add_text(' AND FU.ATTRIBUTE2 =' );
2855 FND_DSQL.add_bind('' || p_attribute2 || '');
2856 END IF;
2857
2858 -- attribute3 filter
2859 IF p_attribute3 is not null then
2860 FND_DSQL.add_text(' AND FU.ATTRIBUTE3 =' );
2861 FND_DSQL.add_bind('' || p_attribute3 || '');
2862 END IF;
2863
2864 -- attribute4 filter
2865 IF p_attribute4 is not null then
2866 FND_DSQL.add_text(' AND FU.ATTRIBUTE4 =' );
2867 FND_DSQL.add_bind('' || p_attribute4 || '');
2868 END IF;
2869
2870 -- attribute5 filter
2871 IF p_attribute5 is not null then
2872 FND_DSQL.add_text(' AND FU.ATTRIBUTE5 =' );
2873 FND_DSQL.add_bind('' || p_attribute5 || '');
2874 END IF;
2875
2876 -- attribute6 filter
2877 IF p_attribute6 is not null then
2878 FND_DSQL.add_text(' AND FU.ATTRIBUTE6 =' );
2879 FND_DSQL.add_bind('' || p_attribute6 || '');
2880 END IF;
2881
2882 -- attribute7 filter
2883 IF p_attribute7 is not null then
2884 FND_DSQL.add_text(' AND FU.ATTRIBUTE7 =' );
2885 FND_DSQL.add_bind('' || p_attribute7 || '');
2886 END IF;
2887
2888 -- attribute8 filter
2889 IF p_attribute8 is not null then
2890 FND_DSQL.add_text(' AND FU.ATTRIBUTE8 =' );
2891 FND_DSQL.add_bind('' || p_attribute8 || '');
2892 END IF;
2893
2894 -- attribute9 filter
2895 IF p_attribute9 is not null then
2896 FND_DSQL.add_text(' AND FU.ATTRIBUTE9 =' );
2897 FND_DSQL.add_bind('' || p_attribute9 || '');
2898 END IF;
2899
2900 -- attribute10 filter
2901 IF p_attribute10 is not null then
2902 FND_DSQL.add_text(' AND FU.ATTRIBUTE10 =' );
2903 FND_DSQL.add_bind('' || p_attribute10 || '');
2904 END IF;
2905
2906 -- attribute11 filter
2907 IF p_attribute11 is not null then
2908 FND_DSQL.add_text(' AND FU.ATTRIBUTE11 =' );
2909 FND_DSQL.add_bind('' || p_attribute11 || '');
2910 END IF;
2911
2912 -- attribute12 filter
2913 IF p_attribute12 is not null then
2914 FND_DSQL.add_text(' AND FU.ATTRIBUTE12 =' );
2915 FND_DSQL.add_bind('' || p_attribute12 || '');
2916 END IF;
2917
2918 -- attribute13 filter
2919 IF p_attribute13 is not null then
2920 FND_DSQL.add_text(' AND FU.ATTRIBUTE13 =' );
2921 FND_DSQL.add_bind('' || p_attribute13 || '');
2922 END IF;
2923
2924 -- attribute14 filter
2925 IF p_attribute14 is not null then
2926 FND_DSQL.add_text(' AND FU.ATTRIBUTE14 =' );
2927 FND_DSQL.add_bind('' || p_attribute14 || '');
2928 END IF;
2929
2930 -- attribute15 filter
2931 IF p_attribute15 is not null then
2932 FND_DSQL.add_text(' AND FU.ATTRIBUTE15 =' );
2933 FND_DSQL.add_bind('' || p_attribute15 || '');
2934 END IF;
2935
2936
2937 FND_DSQL.add_text(' FOR UPDATE OF FU.PLAN_CURR_AMOUNT_REMAINING NOWAIT' );
2938
2939
2940 --creating cursor
2941 l_lines_csr := DBMS_SQL.open_cursor;
2942 l_lines_sql := FND_DSQL.get_text(FALSE); -- Get SQL query built above
2943
2944 IF OZF_DEBUG_LOW_ON THEN
2945 FND_FILE.PUT_LINE(FND_FILE.LOG, '---Start Query Text ---');
2946 FND_FILE.PUT_LINE(FND_FILE.LOG,' l_lines_sql = ' || l_lines_sql);
2947 FND_FILE.PUT_LINE(FND_FILE.LOG, '---End Query Text ---');
2948 END IF;
2949
2950 IF OZF_DEBUG_LOW_ON THEN
2951 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '--- Values for Binds ---' );
2952 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' p_batch_currency = ' || p_batch_currency || ' p_batch_id = ' || p_batch_id || ' p_supplier_site_id = ' || p_supplier_site_id || ' l_org_id = ' || l_org_id );
2953 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' l_batch_currency = ' || l_batch_currency || ' p_request_id = ' || p_request_id || ' p_product_id = ' || p_product_id || ' p_fund_id = ' || p_fund_id );
2954 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' p_start_date = ' || p_start_date || ' p_end_date = ' || p_end_date || ' l_period_start = ' || l_period_start || ' l_period_end = ' || l_period_end );
2955 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' p_attribute1 = ' || p_attribute1 || ' p_attribute2 = ' || p_attribute2 || ' p_attribute3 = ' || p_attribute3 || ' p_attribute4 = ' || p_attribute4 || ' p_attribute5 = ' || p_attribute5 );
2956 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' p_attribute6 = ' || p_attribute6 || ' p_attribute7 = ' || p_attribute7 || ' p_attribute8 = ' || p_attribute8 || ' p_attribute9 = ' || p_attribute9 || ' p_attribute10 = ' || p_attribute10 );
2957 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' p_attribute11 = ' || p_attribute11 || ' p_attribute12 = ' || p_attribute12 || ' p_attribute13 = ' || p_attribute13 || ' p_attribute14 = ' || p_attribute14 || ' p_attribute15 = ' || p_attribute15 );
2958 END IF;
2959
2960 IF OZF_DEBUG_LOW_ON THEN
2961 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' --Start Query Text--');
2962 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' l_lines_sql = ' || l_lines_sql);
2963 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' --End Query Text --');
2964 END IF;
2965
2966 FND_DSQL.set_cursor(l_lines_csr);
2967 DBMS_SQL.parse(l_lines_csr, l_lines_sql, DBMS_SQL.native);
2968 FND_DSQL.do_binds;
2969
2970 --define columns
2971
2972 DBMS_SQL.define_column(l_lines_csr, 1, l_batch_line_id);
2973 DBMS_SQL.define_column(l_lines_csr, 2, l_utilization_id);
2974 DBMS_SQL.define_column(l_lines_csr, 3, l_agreement_number, 100);
2975 DBMS_SQL.define_column(l_lines_csr, 4, l_ship_to_org_id);
2976 DBMS_SQL.define_column(l_lines_csr, 5, l_ship_to_contact_id);
2977
2978 DBMS_SQL.define_column(l_lines_csr, 6, l_sold_to_customer_id);
2979 DBMS_SQL.define_column(l_lines_csr, 7, l_SOLD_TO_CONTACT_ID);
2980 DBMS_SQL.define_column(l_lines_csr, 8, l_SOLD_TO_SITE_USE_ID);
2981 DBMS_SQL.define_column(l_lines_csr, 9, l_end_customer_id);
2982 DBMS_SQL.define_column(l_lines_csr, 10, l_end_customer_contact_id);
2983
2984 DBMS_SQL.define_column(l_lines_csr, 11, l_order_header_id);
2985 DBMS_SQL.define_column(l_lines_csr, 12, l_order_line_number);
2986
2987 DBMS_SQL.define_column(l_lines_csr, 13, l_invoice_number);
2988 DBMS_SQL.define_column(l_lines_csr, 14, l_invoice_line_number);
2989
2990 DBMS_SQL.define_column(l_lines_csr, 15, l_resale_price_currency_code, 15);
2991 DBMS_SQL.define_column(l_lines_csr, 16, l_resales_price);
2992 DBMS_SQL.define_column(l_lines_csr, 17, l_list_price_currency_code, 15);
2993 DBMS_SQL.define_column(l_lines_csr, 18, l_list_price);
2994 DBMS_SQL.define_column(l_lines_csr, 19, l_agreement_currency_code, 15);
2995 DBMS_SQL.define_column(l_lines_csr, 20, l_agreement_price);
2996 DBMS_SQL.define_column(l_lines_csr, 21, l_claim_amount);
2997 DBMS_SQL.define_column(l_lines_csr, 22, l_batch_curr_claim_amount);
2998 DBMS_SQL.define_column(l_lines_csr, 23, l_item_id);
2999
3000 DBMS_SQL.define_column(l_lines_csr, 24, l_shipped_quantity_uom, 100);
3001 DBMS_SQL.define_column(l_lines_csr, 25, l_quantity_shipped);
3002 DBMS_SQL.define_column(l_lines_csr, 26, l_order_date);
3003 DBMS_SQL.define_column(l_lines_csr, 27, l_claim_amount_currency_code, 15);
3004 DBMS_SQL.define_column(l_lines_csr, 28, l_acct_amount_remaining);
3005 DBMS_SQL.define_column(l_lines_csr, 29, l_univ_curr_amount_remaining);
3006 DBMS_SQL.define_column(l_lines_csr, 30, l_fund_req_amount_remaining);
3007 DBMS_SQL.define_column(l_lines_csr, 31, l_amount_remaining);
3008 DBMS_SQL.define_column(l_lines_csr, 32, l_inv_org_id);
3009
3010 DBMS_SQL.define_column(l_lines_csr, 33, l_approved_discount_type,30);
3011 DBMS_SQL.define_column(l_lines_csr, 34, l_approved_discount_value);
3012 DBMS_SQL.define_column(l_lines_csr, 35, l_approved_discount_currency,15);
3013 DBMS_SQL.define_column(l_lines_csr, 36, l_adjustment_type_id);
3014
3015 --execute cursor
3016
3017 IF OZF_DEBUG_LOW_ON THEN
3018 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Cursor Execute Start time' || to_char(sysdate,'dd-mm-yyyy hh:mi:ss') );
3019 END IF;
3020
3021 l_ignore := DBMS_SQL.execute(l_lines_csr);
3022
3023 IF OZF_DEBUG_LOW_ON THEN
3024 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Cursor Execute End time' || to_char(sysdate,'dd-mm-yyyy hh:mi:ss') );
3025 END IF;
3026
3027 p_empty_batch := 'Y'; -- to check if any lines were created
3028 l_batch_line_number := 1;
3029
3030 LOOP
3031
3032 IF OZF_DEBUG_LOW_ON THEN
3033 FND_FILE.PUT_LINE(FND_FILE.LOG, 'DBMS_SQL.FETCH_ROWS loop - Before Fetch');
3034 END IF;
3035
3036 EXIT WHEN DBMS_SQL.FETCH_ROWS(l_lines_csr) = 0;
3037
3038 IF OZF_DEBUG_HIGH_ON THEN
3039 FND_FILE.PUT_LINE(FND_FILE.LOG, 'DBMS_SQL.FETCH_ROWS loop - After Fetch');
3040 END IF;
3041
3042 DBMS_SQL.column_value(l_lines_csr, 1, l_batch_line_id);
3043 DBMS_SQL.column_value(l_lines_csr, 2, l_utilization_id);
3044 DBMS_SQL.column_value(l_lines_csr, 3, l_agreement_number);
3045 DBMS_SQL.column_value(l_lines_csr, 4, l_ship_to_org_id);
3046 DBMS_SQL.column_value(l_lines_csr, 5, l_ship_to_contact_id);
3047
3048 DBMS_SQL.column_value(l_lines_csr, 6, l_sold_to_customer_id);
3049 DBMS_SQL.column_value(l_lines_csr, 7, l_SOLD_TO_CONTACT_ID);
3050 DBMS_SQL.column_value(l_lines_csr, 8, l_SOLD_TO_SITE_USE_ID);
3051 DBMS_SQL.column_value(l_lines_csr, 9, l_end_customer_id);
3052 DBMS_SQL.column_value(l_lines_csr, 10, l_end_customer_contact_id);
3053
3054 DBMS_SQL.column_value(l_lines_csr, 11, l_order_header_id);
3055 DBMS_SQL.column_value(l_lines_csr, 12, l_order_line_number);
3056
3057 DBMS_SQL.column_value(l_lines_csr, 13, l_invoice_number);
3058 DBMS_SQL.column_value(l_lines_csr, 14, l_invoice_line_number);
3059 DBMS_SQL.column_value(l_lines_csr, 15, l_resale_price_currency_code);
3060 DBMS_SQL.column_value(l_lines_csr, 16, l_resales_price);
3061 DBMS_SQL.column_value(l_lines_csr, 17, l_list_price_currency_code);
3062 DBMS_SQL.column_value(l_lines_csr, 18, l_list_price);
3063 DBMS_SQL.column_value(l_lines_csr, 19, l_agreement_currency_code);
3064 DBMS_SQL.column_value(l_lines_csr, 20, l_agreement_price);
3065 DBMS_SQL.column_value(l_lines_csr, 21, l_claim_amount);
3066 DBMS_SQL.column_value(l_lines_csr, 22, l_batch_curr_claim_amount);
3067 DBMS_SQL.column_value(l_lines_csr, 23, l_item_id);
3068
3069 DBMS_SQL.column_value(l_lines_csr, 24, l_shipped_quantity_uom);
3070 DBMS_SQL.column_value(l_lines_csr, 25, l_quantity_shipped);
3071 DBMS_SQL.column_value(l_lines_csr, 26, l_order_date);
3072 DBMS_SQL.column_value(l_lines_csr, 27, l_claim_amount_currency_code);
3073 DBMS_SQL.column_value(l_lines_csr, 28, l_acct_amount_remaining);
3074 DBMS_SQL.column_value(l_lines_csr, 29, l_univ_curr_amount_remaining);
3075 DBMS_SQL.column_value(l_lines_csr, 30, l_fund_req_amount_remaining);
3076 DBMS_SQL.column_value(l_lines_csr, 31, l_amount_remaining);
3077 DBMS_SQL.column_value(l_lines_csr, 32, l_inv_org_id);
3078 DBMS_SQL.column_value(l_lines_csr, 33, l_approved_discount_type);
3079 DBMS_SQL.column_value(l_lines_csr, 34, l_approved_discount_value);
3080 DBMS_SQL.column_value(l_lines_csr, 35, l_approved_discount_currency);
3081 DBMS_SQL.column_value(l_lines_csr, 36, l_adjustment_type_id);
3082
3083 IF OZF_DEBUG_HIGH_ON THEN
3084 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Check for Line Amount Threshold ---');
3085 FND_FILE.PUT_LINE(FND_FILE.LOG,
3086 'l_thresh_line_limit = ' ||
3087 to_char(l_thresh_line_limit) ||
3088 'claim amount: = ' ||
3089 to_char(l_batch_curr_claim_amount));
3090 END IF;
3091 IF ( nvl(l_thresh_line_limit, l_batch_curr_claim_amount - 1) <
3092 l_batch_curr_claim_amount AND
3093 g_currency IS NOT NULL ) OR
3094 g_currency IS NULL OR
3095 l_batch_curr_claim_amount < 0 THEN
3096
3097 p_empty_batch := 'N';
3098 IF OZF_DEBUG_LOW_ON THEN
3099 FND_FILE.PUT_LINE(FND_FILE.LOG,
3100 '--- Values fetched for Batch Line ---');
3101 FND_FILE.PUT_LINE(FND_FILE.LOG,
3102 'values being fetched from SQL' ||
3103 to_char(l_batch_line_id) || '*' || -- line sequence.nextval
3104 to_char(1) || '*' || to_char(l_batch_id) || '*' ||
3105 to_char(l_batch_line_number) || '*' ||
3106 to_char(l_utilization_id) || '*' ||
3107 to_char(l_agreement_number) || '*' ||
3108 to_char(l_ship_to_org_id) || '*' ||
3109 to_char(l_ship_to_contact_id) || '*' ||
3110 to_char(l_sold_to_customer_id) || '*' ||
3111 to_char(l_SOLD_TO_CONTACT_ID) || '*' ||
3112 to_char(l_SOLD_TO_SITE_USE_ID) || '*' ||
3113 to_char(l_end_customer_id) || '*' ||
3114 to_char(l_end_customer_contact_id) || '*' ||
3115
3116 to_char(l_order_header_id) || '*' ||
3117 to_char(l_order_line_number) || '*' ||
3118
3119 to_char(l_invoice_number) || '*' ||
3120 to_char(l_invoice_line_number) || '*' ||
3121 to_char(l_resale_price_currency_code) || '*' ||
3122 to_char(l_resales_price) || '*' ||
3123 to_char(l_list_price_currency_code) || '*' ||
3124 to_char(l_list_price) || '*' ||
3125 to_char(l_agreement_currency_code) || '*' ||
3126 to_char(l_agreement_price) || '*' ||
3127 to_char('NEW') || '*' || -- status is 'new'
3128
3129 to_char(l_claim_amount) || '*' ||
3130 to_char(l_batch_curr_claim_amount) || '*' ||
3131 to_char(l_item_id) || '*' ||
3132 to_char(l_batch_curr_claim_amount) || '*' ||
3133 to_char(l_shipped_quantity_uom) || '*' ||
3134 to_char(l_quantity_shipped) || '*' ||
3135 to_char(l_claim_amount_currency_code) || '*' ||
3136 to_char(l_acct_amount_remaining) || '*' ||
3137 to_char(l_univ_curr_amount_remaining) || '*' ||
3138 to_char(l_fund_req_amount_remaining) || '*' ||
3139 to_char(l_amount_remaining) || '*' ||
3140 to_char('Y') || '*' || to_char(l_order_date) || '*' ||
3141 to_char(sysdate) || '*' || to_char(sysdate) || '*' ||
3142 to_char(FND_GLOBAL.USER_ID) || '*' ||
3143 to_char(FND_GLOBAL.CONC_REQUEST_ID) || '*' ||
3144 to_char(FND_GLOBAL.USER_ID) || '*' ||
3145 to_char(FND_GLOBAL.CONC_LOGIN_ID) || '*' ||
3146 to_char(FND_GLOBAL.PROG_APPL_ID) || '*' ||
3147 to_char(null) || '*' ||
3148 to_char(FND_GLOBAL.CONC_PROGRAM_ID) || '*' ||
3149 to_char(l_org_id) || '*' ||
3150 to_char(l_inv_org_id) || '*' ||
3151 to_char(l_approved_discount_type) || '*' ||
3152 to_char(l_approved_discount_value) || '*' ||
3153 to_char(l_approved_discount_currency) || '*' ||
3154 to_char(l_adjustment_type_id) || '*'||
3155 p_order_source);
3156 END IF;
3157
3158 IF OZF_DEBUG_LOW_ON THEN
3159 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting INTO ozf_sd_batch_lines_all');
3160 END IF;
3161
3162 IF OZF_DEBUG_LOW_ON THEN
3163 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert into batch lines: start time:' || to_char(sysdate,'dd-mm-yyyy hh:mi:ss') );
3164 END IF;
3165
3166 INSERT INTO OZF_SD_BATCH_LINES_ALL
3167 (batch_line_id,
3168 object_version_number,
3169 batch_id,
3170 batch_line_number,
3171 utilization_id,
3172 agreement_number,
3173 ship_to_org_id,
3174 ship_to_contact_id,
3175
3176 sold_to_customer_id,
3177 sold_to_contact_id,
3178 sold_to_site_use_id,
3179 end_customer_id,
3180 end_customer_contact_id,
3181
3182 order_header_id,
3183 order_line_id,
3184
3185 invoice_number,
3186 invoice_line_number,
3187 resale_price_currency_code,
3188 resales_price,
3189 list_price_currency_code,
3190 list_price,
3191 agreement_currency_code,
3192 agreement_price,
3193 status_code,
3194
3195 claim_amount,
3196 claim_amount_currency_code,
3197 batch_curr_claim_amount,
3198
3199 original_claim_amount,
3200 batch_curr_orig_claim_amount,
3201
3202 item_id,
3203 vendor_item_id,
3204 shipped_quantity_uom,
3205 last_sub_claim_amount,
3206 acctd_amount_remaining,
3207 univ_curr_amount_remaining,
3208 fund_request_amount_remaining,
3209 amount_remaining,
3210 quantity_shipped,
3211 purge_flag,
3212 order_date,
3213 creation_date,
3214 last_update_date,
3215 last_updated_by,
3216 request_id,
3217 created_by,
3218
3219 last_update_login,
3220 program_application_id,
3221 program_update_date,
3222 program_id,
3223 org_id,
3224 transmit_flag,
3225
3226 discount_type,
3227 discount_value,
3228 discount_currency_code,
3229 adjustment_type_id,
3230 order_source
3231 )
3232 VALUES
3233 (l_batch_line_id,
3234 1,
3235 l_batch_id,
3236 l_batch_line_number,
3237 l_utilization_id,
3238 l_agreement_number,
3239 l_ship_to_org_id,
3240 l_ship_to_contact_id,
3241
3242 l_sold_to_customer_id,
3243 l_SOLD_TO_CONTACT_ID,
3244 l_SOLD_TO_SITE_USE_ID,
3245 l_end_customer_id,
3246 l_end_customer_contact_id,
3247
3248 l_order_header_id,
3249 l_order_line_number,
3250
3251 l_invoice_number,
3252 l_invoice_line_number,
3253
3254 l_resale_price_currency_code, -- from orders
3255 l_resales_price,
3256 l_list_price_currency_code, --purchase price from sdr
3257 l_list_price,
3258 l_agreement_currency_code, --agreement price from sdr
3259 l_agreement_price,
3260 'NEW',
3261
3262 l_claim_amount, --claim amount from funds accrual
3263 l_claim_amount_currency_code,
3264 l_batch_curr_claim_amount,
3265
3266 l_claim_amount, -- for original_claim_amount
3267 l_batch_curr_claim_amount, -- for batch_curr_orig_claim_amount
3268
3269 l_item_id,
3270 get_vendor_item_id(l_item_id, p_supplier_site_id),
3271
3272 l_shipped_quantity_uom,
3273 null,
3274 l_acct_amount_remaining,
3275 l_univ_curr_amount_remaining,
3276 l_fund_req_amount_remaining,
3277 l_amount_remaining,
3278 l_quantity_shipped,
3279
3280 'N', -- l_active_flag
3281 l_order_date, -- from OE order lines/header
3282 sysdate, --l_creation_date,
3283 sysdate, --l_last_update_date,
3284 FND_GLOBAL.USER_ID, --l_last_updated_by,
3285 FND_GLOBAL.CONC_REQUEST_ID, --l_request_id,
3286 FND_GLOBAL.USER_ID, --l_created_by,
3287 --l_created_from,
3288 FND_GLOBAL.CONC_LOGIN_ID, --l_last_update_login,
3289 FND_GLOBAL.PROG_APPL_ID, --l_program_application_id,
3290 null, --l_program_update_date,
3291 FND_GLOBAL.CONC_PROGRAM_ID, --l_program_id,
3292 l_inv_org_id,
3293 'Y',
3294
3295 l_approved_discount_type,
3296 l_approved_discount_value,
3297 l_approved_discount_currency,
3298
3299 l_adjustment_type_id ,
3300 p_order_source
3301 );
3302
3303 IF OZF_DEBUG_LOW_ON THEN
3304 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Insert into batch lines: end time:' || to_char(sysdate,'dd-mm-yyyy hh:mi:ss') );
3305 END IF;
3306
3307 IF OZF_DEBUG_LOW_ON THEN
3308 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserted INTO ozf_sd_batch_lines_all');
3309 END IF;
3310
3311 l_batch_line_number := l_batch_line_number + 1;
3312 END IF;
3313
3314 END LOOP;
3315 EXCEPTION
3316
3317 WHEN resource_busy THEN
3318
3319 IF OZF_DEBUG_HIGH_ON THEN
3320 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in CREATE_IDSM_BATCH_LINES : ' || SQLERRM);
3321 END IF;
3322
3323 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Exception in CREATE_IDSM_BATCH_LINES : ' || SQLERRM);
3324
3325 RAISE ;
3326
3327
3328 WHEN OTHERS THEN
3329
3330 IF OZF_DEBUG_HIGH_ON THEN
3331 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in CREATE_IDSM_BATCH_LINES : ' || SQLERRM);
3332 END IF;
3333
3334 RAISE FND_API.g_exc_error;
3335
3336 END Create_IDSM_Batch_Lines;
3337
3338
3339
3340
3341 -- Start of comments
3342 -- API name : UPDATE_AMOUNTS
3343 -- Type : Private
3344 -- Pre-reqs : None.
3345 -- Function : Updates ozf_funds_utilized_all table setting amount remaining to zero
3346 -- Parameters :
3347 -- IN : p_batch_id IN NUMBER REQUIRED
3348 -- p_batch_threshold IN NUMBER
3349 -- End of comments
3350 PROCEDURE UPDATE_AMOUNTS(p_batch_id IN NUMBER,
3351 p_batch_threshold IN NUMBER) is
3352
3353 l_batch_id NUMBER;
3354 l_batch_sum NUMBER;
3355 l_batch_threshold NUMBER;
3356 BEGIN
3357 l_batch_threshold := p_batch_threshold;
3358 l_batch_id := p_batch_id;
3359
3360 IF OZF_DEBUG_LOW_ON THEN
3361 FND_FILE.PUT_LINE(FND_FILE.LOG,
3362 '--- Start of UPDATE_AMOUNTS ---');
3363 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_batch_id = ' || p_batch_id);
3364 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_batch_threshold = ' || p_batch_threshold);
3365 END IF;
3366
3367 UPDATE ozf_funds_utilized_all_b
3368 SET amount_remaining = 0,
3369 acctd_amount_remaining = 0,
3370 plan_curr_amount_remaining = 0,
3371 univ_curr_amount_remaining = 0,
3372 fund_request_amount_remaining = 0,
3373 last_update_date = sysdate,
3374 last_updated_by = FND_GLOBAL.USER_ID,
3375 object_version_number = object_version_number + 1
3376 WHERE utilization_id in
3377 (SELECT utilization_id
3378 FROM ozf_sd_batch_lines_all
3379 WHERE batch_id = l_batch_id);
3380 EXCEPTION
3381 WHEN OTHERS THEN
3382 IF OZF_DEBUG_HIGH_ON THEN
3383 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in UPDATE_AMOUNTS:' || SQLERRM);
3384 END IF;
3385
3386 RAISE FND_API.g_exc_error;
3387
3388 END UPDATE_AMOUNTS;
3389
3390
3391
3392 -- Start of comments
3393 -- API name : INVOKE_BATCH_AUTO_CLAIM
3394 -- Type : Private
3395 -- Pre-reqs : None.
3396 -- Function : Executable target for concurrent program
3397 -- Executable Name "OZFSDBACEX"
3398 -- Parameters :
3399 -- IN : p_batch_id IN NUMBER
3400 -- p_vendor_id IN NUMBER
3401 -- p_vendor_site_id IN NUMBER
3402 -- End of comments
3403 PROCEDURE INVOKE_BATCH_AUTO_CLAIM(errbuf OUT nocopy VARCHAR2,
3404 retcode OUT nocopy NUMBER,
3405 p_batch_id NUMBER,
3406 p_vendor_id NUMBER,
3407 p_vendor_site_id NUMBER) is
3408
3409 CURSOR get_freq_and_date(c_supplier_site_id NUMBER) IS
3410 SELECT days_before_claiming_debit
3411 FROM ozf_supp_trd_prfls_all
3412 WHERE supplier_site_id = c_supplier_site_id;
3413
3414 l_claim_id NUMBER := NULL; -- Incase auto claim is run
3415 l_claim_ret_status VARCHAR2(15) := NULL;
3416 l_claim_msg_count NUMBER := NULL;
3417 l_claim_msg_data VARCHAR2(500) := NULL;
3418 l_claim_type VARCHAR2(20) := 'SUPPLIER'; --always defaulted to external claim
3419 l_batch_id NUMBER;
3420 l_duration NUMBER;
3421 l_freq NUMBER;
3422 l_freq_unit VARCHAR2(40);
3423 l_sql VARCHAR2(2000) := NULL;
3424 l_supplier_site_id NUMBER := null;
3425 v_batch_header c_batch_header;
3426 l_last_run_date DATE;
3427 l_batch_submission_date DATE;
3428 l_return_status VARCHAR2(15) := NULL;
3429 BEGIN
3430
3431 IF OZF_DEBUG_LOW_ON THEN
3432 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Start INVOKE_BATCH_AUTO_CLAIM ---');
3433 END IF;
3434
3435 l_sql := 'SELECT HDR.BATCH_ID, HDR.vendor_site_id, HDR.BATCH_SUBMISSION_DATE '
3436 || ' FROM ozf_sd_batch_headers_all HDR, ozf_sd_batch_lines_all BLN '
3437 || ' WHERE HDR.batch_id = BLN.batch_id'
3438 || ' AND HDR.status_code = ''SUBMITTED'' ';
3439
3440
3441 IF p_vendor_site_id IS NOT NULL THEN
3442 l_sql := l_sql || ' AND HDR.vendor_site_id =' || p_vendor_site_id;
3443
3444 END IF;
3445
3446 IF p_batch_id IS NOT NULL THEN
3447 l_sql := l_sql || ' AND HDR.batch_id =' || p_batch_id;
3448
3449 END IF;
3450
3451 IF p_vendor_id IS NOT NULL THEN
3452 l_sql := l_sql || ' AND HDR.vendor_id =' || p_vendor_id;
3453
3454 END IF;
3455 --Fix for Bug#10047209
3456 l_sql := l_sql || ' GROUP BY HDR.BATCH_ID, HDR.vendor_site_id, HDR.BATCH_SUBMISSION_DATE HAVING sum(BLN.batch_curr_claim_amount) <> 0 ' ;
3457
3458 OPEN v_batch_header for l_sql;
3459 LOOP
3460 FETCH v_batch_header
3461 INTO l_batch_id, l_supplier_site_id, l_batch_submission_date;
3462 EXIT WHEN v_batch_header%notfound;
3463
3464 BEGIN
3465 OPEN get_freq_and_date(l_supplier_site_id);
3466 FETCH get_freq_and_date
3467 INTO l_freq;
3468 CLOSE get_freq_and_date;
3469
3470 IF OZF_DEBUG_LOW_ON THEN
3471 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch ID ' || to_char(l_batch_id));
3472 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch Submission Date ' || to_char(l_batch_submission_date));
3473 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Trade Profile Frequency = ' || to_char(l_freq));
3474 END IF;
3475
3476 l_batch_submission_date := l_batch_submission_date + l_freq;
3477
3478 IF NVL(l_batch_submission_date, sysdate + 1) < sysdate THEN
3479
3480 IF OZF_DEBUG_HIGH_ON THEN
3481 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invoking Claim for Batch ID ' || to_char(l_batch_id));
3482 END IF;
3483
3484 --Code added for Bug#6971836
3485 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Initiates claim for batch '||to_char(l_batch_id));
3486
3487 IF OZF_DEBUG_LOW_ON THEN
3488 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Claim API Invoke Start time in invoke_batch auto claim' || to_char(sysdate,'dd-mm-yyyy hh:mi:ss') );
3489 END IF;
3490
3491 OZF_CLAIM_ACCRUAL_PVT.Initiate_SD_Payment(1,
3492 FND_API.g_false,
3493 FND_API.g_true,
3494 FND_API.g_valid_level_full,
3495 l_claim_ret_status,
3496 l_claim_msg_count,
3497 l_claim_msg_data,
3498 l_batch_id,
3499 l_claim_type,
3500 l_claim_id);
3501
3502 IF OZF_DEBUG_LOW_ON THEN
3503 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Claim API Invoke End time in invoke batch auto claim' || to_char(sysdate,'dd-mm-yyyy hh:mi:ss') );
3504 END IF;
3505
3506 IF OZF_DEBUG_HIGH_ON THEN
3507 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invoked Claim ....' );
3508 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Batch ID ' || to_char(l_batch_id));
3509 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Claim ID ' || to_char(l_claim_id) );
3510 END IF;
3511
3512 IF OZF_ERROR_ON THEN
3513 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_claim_ret_status ' || l_claim_ret_status );
3514 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_claim_msg_count ' || l_claim_msg_count );
3515 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_claim_msg_data ' || l_claim_msg_data );
3516 FOR I IN 1..l_claim_msg_count LOOP
3517 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Msg from Claim API while invoking batch for Auto Claim ' || SUBSTR(FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F'), 1, 254) );
3518 END LOOP;
3519 END IF;
3520
3521 IF l_claim_ret_status = FND_API.G_RET_STS_SUCCESS THEN
3522
3523 --Code added for Bug#6971836
3524 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Claim created for batch.');
3525
3526 UPDATE OZF_SD_BATCH_HEADERS_ALL
3527 SET status_code = 'CLOSED',
3528 claim_id = l_claim_id,
3529 last_update_date = sysdate,
3530 last_updated_by = FND_GLOBAL.USER_ID,
3531 object_version_number = object_version_number + 1
3532 WHERE batch_id = l_batch_id;
3533
3534 OZF_SD_UTIL_PVT.SD_RAISE_EVENT(l_batch_id, 'CLAIM', l_return_status); -- Raising lifecycle event for claim
3535 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
3536 RAISE FND_API.g_exc_error;
3537 END IF;
3538
3539
3540
3541 COMMIT;
3542
3543 END IF;
3544 ELSE
3545 --Code added for Bug#6971836
3546 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Claim process failed.');
3547 END IF;
3548
3549 EXCEPTION
3550 WHEN OTHERS THEN
3551 IF OZF_DEBUG_HIGH_ON THEN
3552 FND_FILE.PUT_LINE(FND_FILE.LOG,
3553 'Exception occured in INVOKE_BATCH_AUTO_CLAIM :=' ||
3554 SQLERRM);
3555 errbuf := 'Exception occured in INVOKE_BATCH_AUTO_CLAIM ' ||
3556 SQLERRM;
3557 retcode := 2;
3558 END IF;
3559 END;
3560 END LOOP;
3561
3562 CLOSE v_batch_header;
3563
3564 END INVOKE_BATCH_AUTO_CLAIM;
3565
3566 -- Start of comments
3567 -- API name : PROCESS_SD_PENDING_CLM_BATCHES
3568 -- Type : Private
3569 -- Pre-reqs : None.
3570 -- Function : Executable target for concurrent program
3571 -- : Executable Name "OZFSDPABEX"
3572 -- : Loops over availabe operating units and invokes GET_SUPPLIER_SITES
3573 -- Parameters :
3574 -- IN : p_org_id IN NUMBER
3575 -- p_vendor_id IN NUMBER
3576 -- p_vendor_site_id IN NUMBER
3577 -- p_batch_id IN NUMBER
3578 -- End of comments
3579 PROCEDURE PROCESS_SD_PENDING_CLM_BATCHES(errbuf OUT nocopy VARCHAR2,
3580 retcode OUT nocopy NUMBER,
3581 p_org_id NUMBER,
3582 p_vendor_id NUMBER,
3583 p_vendor_site_id NUMBER,
3584 p_batch_id NUMBER) IS
3585
3586 CURSOR operating_unit_csr IS
3587 SELECT ou.organization_id org_id
3588 FROM hr_operating_units ou
3589 WHERE mo_global.check_access(ou.organization_id) = 'Y';
3590
3591 m NUMBER := 0;
3592 l_org_id OZF_UTILITY_PVT.operating_units_tbl;
3593 l_return_status VARCHAR2(15) := NULL;
3594 BEGIN
3595
3596 IF OZF_DEBUG_LOW_ON THEN
3597 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Start PROCESS_SD_PENDING_CLM_BATCHES ---');
3598 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Start Parameter List ---');
3599 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_org_id: ' || p_org_id);
3600 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_vendor_id: ' || p_vendor_id);
3601 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_vendor_site_id: ' || p_vendor_site_id);
3602 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_batch_id: ' || p_batch_id);
3603 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- End Parameter List ---');
3604 END IF;
3605
3606 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'p_org_id: ' || p_org_id);
3607 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'p_vendor_id: ' || p_vendor_id);
3608 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'p_vendor_site_id: ' || p_vendor_site_id);
3609 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'p_batch_id: ' || p_batch_id);
3610
3611 MO_GLOBAL.init('OZF');
3612
3613 IF p_org_id IS NULL THEN
3614 MO_GLOBAL.set_policy_context('M', null);
3615 OPEN operating_unit_csr;
3616 LOOP
3617 FETCH operating_unit_csr
3618 INTO l_org_id(m);
3619 m := m + 1;
3620 EXIT WHEN operating_unit_csr%NOTFOUND;
3621 END LOOP;
3622 CLOSE operating_unit_csr;
3623 ELSE
3624 l_org_id(m) := p_org_id;
3625 END IF;
3626
3627 BEGIN
3628 IF p_org_id is NOT NULL OR p_vendor_id is NOT NULL OR p_vendor_site_id is NOT NULL OR p_batch_id is NOT NULL THEN --atleast if one parameter is given
3629
3630 PROCESS_SUPPLIER_SITES(p_org_id, p_vendor_id , p_vendor_site_id, p_batch_id);
3631
3632 ELSIF (l_org_id.COUNT > 0) AND p_org_id is NULL AND p_vendor_id IS NULL AND p_vendor_site_id IS NULL AND p_batch_id IS NULL THEN --if all 4 parameters are not passed
3633 FOR m IN l_org_id.FIRST .. l_org_id.LAST LOOP
3634
3635 IF OZF_DEBUG_LOW_ON THEN
3636 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Start Fetch of Organization ids ---');
3637 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_org_id ' || to_char(l_org_id(m)));
3638 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- End Fetch of Organization ids ---');
3639 END IF;
3640
3641 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'l_org_id ' || to_char(l_org_id(m)));
3642
3643 MO_GLOBAL.set_policy_context('S', l_org_id(m));
3644
3645 IF OZF_DEBUG_HIGH_ON THEN
3646 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Processing for Operating Unit: ' ||
3647 MO_GLOBAL.get_ou_name(l_org_id(m)));
3648 END IF;
3649
3650 PROCESS_SUPPLIER_SITES(l_org_id(m) , p_vendor_id , p_vendor_site_id, p_batch_id);
3651 END LOOP;
3652 END IF;
3653
3654 EXCEPTION
3655 WHEN OTHERS THEN
3656 IF OZF_DEBUG_HIGH_ON THEN
3657 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in PROCESS_SD_PENDING_CLM_BATCHES : ' || SQLERRM);
3658 END IF;
3659
3660 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Exception in PROCESS_SD_PENDING_CLM_BATCHES : ' ||SQLERRM);
3661
3662 errbuf := 'Error in PROCESS_SD_PENDING_CLM_BATCHES ' || SQLERRM;
3663 retcode := 2;
3664 END;
3665
3666 END PROCESS_SD_PENDING_CLM_BATCHES;
3667
3668
3669 -- Start of comments
3670 -- API name : PROCESS_SUPPLIER_SITES
3671 -- Type : Private
3672 -- Pre-reqs : None.
3673 -- Function : Looping over Supplier Site .
3674 -- Parameters :
3675 -- IN : p_org_id IN NUMBER
3676 -- : p_vendor_id IN NUMBER
3677 -- : p_vendor_site_id IN NUMBER
3678 -- : p_batch_id IN NUMBER
3679 -- End of comments
3680
3681 PROCEDURE PROCESS_SUPPLIER_SITES(p_org_id IN NUMBER,
3682 p_vendor_id IN NUMBER,
3683 p_vendor_site_id IN NUMBER,
3684 p_batch_id IN NUMBER) IS
3685
3686 l_org_id NUMBER := NULL;
3687 l_supplier_id NUMBER;
3688 l_supplier_site_id NUMBER;
3689 l_batch_id NUMBER;
3690
3691
3692 CURSOR get_supplier_sites(c_org_id NUMBER,c_vendor_id NUMBER,c_vendor_site_id NUMBER) IS
3693 SELECT sites.vendor_id, sites.vendor_site_id
3694 FROM ap_supplier_sites_all sites,
3695 ozf_supp_trd_prfls_all trprf
3696 WHERE sites.org_id = NVL(c_org_id,sites.org_id) AND
3697 sites.vendor_id = NVL(c_vendor_id,sites.vendor_id) AND
3698 sites.vendor_site_id = NVL(c_vendor_site_id,sites.vendor_site_id) AND
3699 nvl(sites.inactive_date, sysdate) >= trunc(sysdate) AND
3700 trprf.cust_account_id is not null AND
3701 sites.vendor_id=trprf.supplier_id AND
3702 sites.vendor_site_id=trprf.supplier_site_id;
3703
3704 BEGIN
3705
3706 l_org_id := p_org_id;
3707 l_supplier_id := p_vendor_id;
3708 l_supplier_site_id := p_vendor_site_id;
3709 l_batch_id := p_batch_id;
3710
3711 IF l_batch_id IS NULL THEN
3712 FOR site_rec IN get_supplier_sites(l_org_id,l_supplier_id,l_supplier_site_id) LOOP
3713 IF OZF_DEBUG_HIGH_ON THEN
3714 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Looping for Supplier ID = '|| site_rec.vendor_id || ' and Supplier Site ID = ' || site_rec.vendor_site_id);
3715 END IF;
3716
3717 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Looping for Supplier ID = '|| site_rec.vendor_id || ' and Supplier Site ID = ' || site_rec.vendor_site_id);
3718
3719 INVOKE_CLAIM(p_org_id, p_vendor_id , site_rec.vendor_site_id, p_batch_id);
3720 END LOOP; -- for site_rec
3721
3722 ELSE
3723 INVOKE_CLAIM(p_org_id, p_vendor_id , p_vendor_site_id, p_batch_id);
3724
3725 END IF;
3726
3727 EXCEPTION
3728 WHEN OTHERS THEN
3729 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in PROCESS_SUPPLIER_SITES' || sqlerrm);
3730 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Exception in PROCESS_SUPPLIER_SITES' || sqlerrm);
3731 END; -- PROCESS_SUPPLIER_SITES;
3732
3733
3734 -- Start of comments
3735 -- API name : INVOKE_CLAIM
3736 -- Type : Private
3737 -- Pre-reqs : None.
3738 -- Function : Invokes claim creation of Batches that are Pending Claim.
3739 -- Parameters :
3740 -- IN : p_org_id IN NUMBER
3741 -- : p_vendor_id IN NUMBER
3742 -- : p_vendor_site_id IN NUMBER
3743 -- : p_batch_id IN NUMBER
3744 -- End of comments
3745
3746 PROCEDURE INVOKE_CLAIM(p_org_id IN NUMBER,
3747 p_vendor_id IN NUMBER,
3748 p_vendor_site_id IN NUMBER,
3749 p_batch_id IN NUMBER) IS
3750
3751 l_org_id NUMBER := NULL;
3752 l_supplier_id NUMBER;
3753 l_supplier_site_id NUMBER;
3754 l_batch_id NUMBER;
3755
3756 l_claim_id NUMBER := NULL;
3757 l_ret_status VARCHAR2(15) := NULL;
3758 l_msg_count NUMBER := NULL;
3759 l_msg_data VARCHAR2(5000) := NULL;
3760 l_claim_type VARCHAR2(20) := 'SUPPLIER';
3761 l_func_currency VARCHAR2(15);
3762
3763 CURSOR get_sd_batches_pending_claim(c_org_id NUMBER,c_vendor_id NUMBER,c_vendor_site_id NUMBER,c_batch_id NUMBER) IS
3764 SELECT HDR.BATCH_ID batch_id, HDR.CURRENCY_CODE currency_code
3765 FROM ozf_sd_batch_headers_all HDR , ozf_sd_batch_lines_all BLN
3766 WHERE HDR.batch_id = BLN.batch_id
3767 AND HDR.status_code = 'PENDING_CLAIM'
3768 AND HDR.org_id = NVL(c_org_id,HDR.org_id)
3769 AND HDR.vendor_id = NVL(c_vendor_id,HDR.vendor_id)
3770 AND HDR.vendor_site_id = NVL(c_vendor_site_id,HDR.vendor_site_id)
3771 AND HDR.batch_id = NVL(c_batch_id,HDR.batch_id)
3772 GROUP BY HDR.BATCH_ID, HDR.CURRENCY_CODE
3773 HAVING sum(BLN.batch_curr_claim_amount) > 0;
3774
3775 BEGIN
3776
3777 l_org_id := p_org_id;
3778 l_supplier_id := p_vendor_id;
3779 l_supplier_site_id := p_vendor_site_id;
3780 l_batch_id := p_batch_id;
3781
3782 FOR batch_rec IN get_sd_batches_pending_claim(l_org_id,l_supplier_id,l_supplier_site_id,l_batch_id) LOOP
3783
3784 SELECT gs.currency_code
3785 INTO l_func_currency
3786 FROM gl_sets_of_books gs,
3787 ozf_sys_parameters_all org,
3788 ozf_sd_batch_headers_all bh
3789 WHERE org.set_of_books_id = gs.set_of_books_id
3790 AND org.org_id = bh.org_id
3791 AND bh.batch_id = batch_rec.BATCH_ID;
3792
3793 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Initiating claim for batch '|| batch_rec.BATCH_ID);
3794 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Initiating claim for batch '|| batch_rec.BATCH_ID);
3795
3796 SAVEPOINT BEFORE_INVOKE_CLAIM;
3797
3798 UPDATE ozf_sd_batch_lines_all BLN
3799 SET batch_curr_claim_amount = (
3800 CASE
3801
3802 WHEN ((BLN.claim_amount_currency_code = l_func_currency) AND (l_func_currency <> batch_rec.currency_code)) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(BLN.claim_amount_currency_code,
3803 batch_rec.currency_code,
3804 l_func_currency,
3805 (SELECT fu.exchange_rate_type
3806 FROM ozf_funds_utilized_all_b fu
3807 WHERE fu.utilization_id = BLN.utilization_id
3808 AND BLN.batch_id = batch_rec.batch_id),
3809 NULL,
3810 sysdate,
3811 BLN.CLAIM_AMOUNT)
3812
3813 WHEN ((BLN.claim_amount_currency_code <> l_func_currency) AND (l_func_currency <> batch_rec.currency_code)) THEN OZF_SD_UTIL_PVT.GET_CONVERTED_CURRENCY(BLN.claim_amount_currency_code,
3814 batch_rec.currency_code,
3815 l_func_currency,
3816 (SELECT fu.exchange_rate_type
3817 FROM ozf_funds_utilized_all_b fu
3818 WHERE fu.utilization_id = BLN.utilization_id
3819 AND BLN.batch_id = batch_rec.batch_id),
3820 NULL,
3821 (SELECT fu.exchange_rate_date
3822 FROM ozf_funds_utilized_all_b fu
3823 WHERE fu.utilization_id = BLN.utilization_id
3824 AND BLN.batch_id = batch_rec.batch_id),
3825 BLN.CLAIM_AMOUNT)
3826 END
3827 ),
3828 object_version_number = object_version_number + 1,
3829 last_update_date = sysdate,
3830 last_updated_by = fnd_global.user_id
3831 WHERE batch_id = batch_rec.batch_id
3832 AND batch_line_number IN (SELECT BLN.BATCH_LINE_NUMBER
3833 FROM ozf_sd_batch_lines_all BLN,
3834 ozf_funds_utilized_all_b FU
3835 WHERE BLN.batch_id = batch_rec.BATCH_ID
3836 AND BLN.utilization_id = FU.utilization_id
3837 AND ((batch_rec.currency_code <> BLN.claim_amount_currency_code) AND (batch_rec.currency_code <> l_func_currency))
3838 );
3839
3840
3841 OZF_CLAIM_ACCRUAL_PVT.Initiate_SD_Payment(1,
3842 FND_API.g_false,
3843 FND_API.g_true,
3844 FND_API.g_valid_level_full,
3845 l_ret_status,
3846 l_msg_count,
3847 l_msg_data,
3848 batch_rec.BATCH_ID,
3849 l_claim_type,
3850 l_claim_id);
3851
3852 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'After Claim Initiation for batch '|| batch_rec.BATCH_ID);
3853
3854 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' l_ret_status ' || l_ret_status );
3855 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' l_msg_count ' || l_msg_count );
3856 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' l_msg_data ' || l_msg_data );
3857 FOR I IN 1..l_msg_count LOOP
3858 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' Msg from Claim API while invoking claim for batch ' || SUBSTR(FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F'), 1, 254) );
3859 END LOOP;
3860
3861 IF OZF_DEBUG_HIGH_ON THEN
3862 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invoked Claim ....' );
3863 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Batch ID ' || batch_rec.BATCH_ID);
3864 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Claim ID ' || to_char(l_claim_id) );
3865 END IF;
3866
3867 IF OZF_ERROR_ON THEN
3868 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_ret_status ' || l_ret_status );
3869 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_msg_count ' || l_msg_count );
3870 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_msg_data ' || l_msg_data );
3871 FOR I IN 1..l_msg_count LOOP
3872 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Msg from Claim API while invoking claim for batch ' || SUBSTR(FND_MSG_PUB.GET(P_MSG_INDEX => I, P_ENCODED => 'F'), 1, 254) );
3873 END LOOP;
3874 END IF;
3875
3876 IF l_ret_status = FND_API.G_RET_STS_SUCCESS THEN -- If claim is successful
3877
3878 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Claim created for batch.' || batch_rec.BATCH_ID || 'is :' || to_char(l_claim_id));
3879 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Claim created for batch.' || batch_rec.BATCH_ID || 'is :' || to_char(l_claim_id));
3880
3881 OZF_SD_UTIL_PVT.CREATE_ADJUSTMENT(batch_rec.BATCH_ID, 'F', l_ret_status, l_msg_count, l_msg_data);
3882
3883 IF l_ret_status = FND_API.G_RET_STS_SUCCESS THEN -- If adjustment is successful
3884
3885 UPDATE OZF_SD_BATCH_HEADERS_ALL
3886 SET status_code = 'CLOSED',
3887 claim_id = l_claim_id,
3888 last_update_date = sysdate,
3889 last_updated_by = FND_GLOBAL.USER_ID,
3890 object_version_number = object_version_number + 1
3891 WHERE batch_id = batch_rec.BATCH_ID
3892 AND status_code = 'PENDING_CLAIM';
3893
3894 OZF_SD_UTIL_PVT.SD_RAISE_EVENT(batch_rec.BATCH_ID, 'CLAIM', l_ret_status); -- Raising lifecycle event for claim
3895
3896 ELSE -- If adjustment is not successful
3897
3898 ROLLBACK TO BEFORE_INVOKE_CLAIM;
3899 UPDATE ozf_sd_batch_headers_all
3900 SET status_code = 'PENDING_CLAIM',
3901 last_update_date = sysdate,
3902 last_updated_by = fnd_global.user_id,
3903 object_version_number = object_version_number + 1
3904 WHERE batch_id = batch_rec.BATCH_ID;
3905 END IF;
3906
3907 ELSE -- If claim is not successful
3908
3909 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Claim process failed.');
3910 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Claim process failed.');
3911
3912 ROLLBACK TO BEFORE_INVOKE_CLAIM;
3913 UPDATE ozf_sd_batch_headers_all
3914 SET status_code = 'PENDING_CLAIM',
3915 last_update_date = sysdate,
3916 last_updated_by = fnd_global.user_id,
3917 object_version_number = object_version_number + 1
3918 WHERE batch_id = batch_rec.BATCH_ID;
3919 END IF;
3920
3921 COMMIT;
3922
3923 END LOOP; -- for batch_rec
3924
3925 EXCEPTION
3926 WHEN OTHERS THEN
3927 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in INVOKE_CLAIM' || sqlerrm);
3928 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Exception in INVOKE_CLAIM' || sqlerrm);
3929 END; -- INVOKE_CLAIM;
3930
3931 END OZF_SD_BATCH_PVT;