[Home] [Help]
PACKAGE BODY: APPS.OZF_SD_BATCH_PVT
Source
1 PACKAGE BODY OZF_SD_BATCH_PVT AS
2 /* $Header: ozfvsdbb.pls 120.48.12010000.15 2009/02/11 12:34:15 annsrini 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 --
25 -- NOTE :
26 -- End of Comments
27
28 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OZF_SD_BATCH_PVT';
29 G_FILE_NAME CONSTANT VARCHAR2(12) := 'ozfvsdbb.pls';
30 OZF_DEBUG_HIGH_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
31 OZF_DEBUG_LOW_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
32 OZF_ERROR_ON CONSTANT BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error);
33 g_currency VARCHAR2(30) := null;
34
35 type c_batch_header is REF CURSOR;
36
37
38 -- Start of comments
39 -- API name : GET_BATCH_CURRENCY_AMOUNT
40 -- Type : Private
41 -- Pre-reqs : None.
42 -- Function : Converts batch line currency into corresponding header currency
43 -- Conversion is not made if FROM and TO currency are the same.
44 -- Parameters :
45 -- IN : p_UTILIZATION_ID IN NUMBER Required
46 -- p_batch_currency IN VARCHAR2 Required
47 -- p_batch_id IN NUMBER Required
48 -- OUT : Converted currency value.
49 -- End of comments
50 FUNCTION GET_BATCH_CURRENCY_AMOUNT(p_UTILIZATION_ID number,
51 p_batch_currency VARCHAR2,
52 p_batch_id number) RETURN number is
53 x_return_status VARCHAR2(100) := NULL;
54 l_from_currency VARCHAR2(15) := NULL;
55 l_to_currency VARCHAR2(15) := NULL;
56 l_conv_date DATE;
57 l_from_amount NUMBER;
58 x_to_amount NUMBER;
59 l_util_id NUMBER;
60 BEGIN
61
62 SELECT OZF_SD_BATCH_HEADERS_ALL.Creation_Date
63 INTO l_conv_date
64 FROM OZF_SD_BATCH_HEADERS_ALL
65 WHERE batch_id = p_batch_id;
66
67 SELECT OZF_FUNDS_UTILIZED_ALL_B.PLAN_CURR_AMOUNT_REMAINING,
68 OZF_OFFERS.TRANSACTION_CURRENCY_CODE
69 INTO l_from_amount, l_from_currency
70 FROM OZF_FUNDS_UTILIZED_ALL_B, OZF_OFFERS
71 WHERE utilization_id = p_UTILIZATION_ID and
72 plan_id = OZF_OFFERS.QP_LIST_HEADER_ID;
73 IF l_from_currency <> p_batch_currency then
74 OZF_UTILITY_PVT.Convert_Currency(x_return_status,
75 l_from_currency,
76 p_batch_currency,
77 l_conv_date,
78 l_from_amount,
79 x_to_amount);
80 ELSE
81 x_to_amount := l_from_amount;
82 END IF;
83
84 RETURN x_to_amount;
85
86 EXCEPTION
87
88 WHEN OTHERS then
89 FND_FILE.PUT_LINE(FND_FILE.LOG,
90 'OZF_UTILITY_PVT.Convert_Currency(x_return_status,' ||
91 x_return_status);
92
93 FND_FILE.PUT_LINE(FND_FILE.LOG,
94 'Start exception block of GET_BATCH_CURRENCY_AMOUNT(p_UTILIZATION_ID number:-,' ||
95 p_UTILIZATION_ID || ' p_batch_currency VARCHAR2) ' ||
96 p_batch_currency || ' : x_to_amount :=' ||
97 x_to_amount || SQLERRM);
98 RAISE FND_API.g_exc_error;
99
100 END;
101
102 -- Start of comments
103 -- API name : CONV_DISC_TO_OFFER_CURR_AMOUNT
104 -- Type : Private
105 -- Pre-reqs : None.
106 -- Function : Converts discount value from discount value currency to offer currency
107 -- Conversion is not made if FROM and TO currency are the same.
108 -- Parameters :
109 -- IN : p_offer_currency IN NUMBER Required
110 -- p_discount_val_currency IN VARCHAR2 Required
111 -- p_discount_val IN NUMBER Required
112 -- p_batch_id IN NUMBER Required
113 -- OUT : Converted currency value.
114 -- End of comments
115
116 FUNCTION CONV_DISC_TO_OFFER_CURR_AMOUNT(p_offer_currency VARCHAR2,
117 p_discount_val_currency VARCHAR2,
118 p_discount_val number,
119 p_batch_id number) RETURN number is
120 x_return_status VARCHAR2(100) := NULL;
121 l_conv_date DATE;
122 x_to_amount NUMBER;
123
124 BEGIN
125
126 SELECT OZF_SD_BATCH_HEADERS_ALL.Creation_Date
127 INTO l_conv_date
128 FROM OZF_SD_BATCH_HEADERS_ALL
129 WHERE batch_id = p_batch_id;
130
131
132 IF p_discount_val_currency <> p_offer_currency then
133 OZF_UTILITY_PVT.Convert_Currency(x_return_status,
134 p_discount_val_currency,
135 p_offer_currency,
136 l_conv_date,
137 p_discount_val,
138 x_to_amount);
139 ELSE
140 x_to_amount := p_discount_val;
141 END IF;
142
143 RETURN x_to_amount;
144
145 EXCEPTION
146
147 WHEN OTHERS then
148 FND_FILE.PUT_LINE(FND_FILE.LOG,
149 'OZF_UTILITY_PVT.Convert_Currency(x_return_status,' ||
150 x_return_status);
151
152 FND_FILE.PUT_LINE(FND_FILE.LOG,
153 'Start exception block of CONV_DISC_TO_OFFER_CURR_AMOUNT(p_discount_val number:-,' ||
154 p_discount_val || ' p_offer_currency VARCHAR2) ' ||
155 p_offer_currency || ' : x_to_amount :=' ||
156 x_to_amount || SQLERRM);
157 RAISE FND_API.g_exc_error;
158
159 END;
160
161
162 -- Start of comments
163 -- API name : GET_VENDOR_ITEM_ID
164 -- Type : Private
165 -- Pre-reqs : None.
166 -- Function : Returns the mapped vendor product id for a given distributor
167 -- product id based on trade profile for vendor site.
168 -- Parameters :
169 -- IN : p_product_id IN NUMBER Required
170 -- p_supplier_site_id IN NUMBER Required
171 -- OUT : Vendor product code.
172 -- End of comments
173 FUNCTION GET_VENDOR_ITEM_ID(p_product_id number,
174 p_supplier_site_id number) RETURN varchar2 is
175
176 l_vendor_product_id varchar2(240) := null;
177 l_internal_code varchar2(240) := null;
178
179 BEGIN
180 l_internal_code := p_product_id;
181
182 SELECT code.external_code
183 INTO l_vendor_product_id
184 FROM OZF_SUPP_CODE_CONVERSIONS_ALL code, OZF_SUPP_TRD_PRFLS_ALL prf
185 WHERE internal_code = l_internal_code and
186 code.supp_trade_profile_id = prf.supp_trade_profile_id and
187 prf.supplier_site_id = p_supplier_site_id and
188 trunc(sysdate) between code.start_date_active and
189 nvl(code.end_date_active, sysdate + 1);
190
191 RETURN l_vendor_product_id;
192
193 END get_vendor_item_id;
194
195
196
197 -- Start of comments
198 -- API name : create_batch_main
199 -- Type : Private
200 -- Pre-reqs : None.
201 -- Function : Executable target for concurrent program
202 -- Executable Name "OZFSDBPREX"
203 -- Loops over availabe operating units and invokes create_batch_sub
204 -- Parameters :
205 -- IN : p_org_id IN NUMBER
206 -- p_supplier_id IN NUMBER REQUIRED
207 -- p_supplier_site_id IN NUMBER
208 -- p_product_id IN NUMBER
209 -- p_request_id IN NUMBER
210 -- p_fund_id IN NUMBER
211 -- p_start_date IN DATE
212 -- p_end_date IN DATE
213 -- p_period IN VARCHAR2
214 -- p_attribute1 IN VARCHAR2
215 -- p_attribute2 IN VARCHAR2
216 -- p_attribute3 IN VARCHAR2
217 -- p_attribute4 IN VARCHAR2
218 -- p_attribute5 IN VARCHAR2
219 -- p_attribute6 IN VARCHAR2
220 -- p_attribute7 IN VARCHAR2
221 -- p_attribute8 IN VARCHAR2
222 -- p_attribute9 IN VARCHAR2
223 -- p_attribute10 IN VARCHAR2
224 -- p_attribute11 IN VARCHAR2
225 -- p_attribute12 IN VARCHAR2
226 -- p_attribute13 IN VARCHAR2
227 -- p_attribute14 IN VARCHAR2
228 -- p_attribute15 IN VARCHAR2
229 -- End of comments
230 PROCEDURE create_batch_main(errbuf OUT nocopy VARCHAR2,
231 retcode OUT nocopy NUMBER,
232 p_org_id IN NUMBER,
233 p_supplier_id IN NUMBER,
234 p_supplier_site_id IN NUMBER,
235 --p_category_id IN NUMBER,
236 p_product_id IN NUMBER,
237 p_request_id IN NUMBER,
238 p_fund_id IN NUMBER,
239 p_start_date IN VARCHAR2,
240 p_end_date IN VARCHAR2,
241 p_period IN VARCHAR2,
242 p_attribute1 IN VARCHAR2 := NULL,
243 p_attribute2 IN VARCHAR2 := NULL,
244 p_attribute3 IN VARCHAR2 := NULL,
245 p_attribute4 IN VARCHAR2 := NULL,
246 p_attribute5 IN VARCHAR2 := NULL,
247 p_attribute6 IN VARCHAR2 := NULL,
248 p_attribute7 IN VARCHAR2 := NULL,
249 p_attribute8 IN VARCHAR2 := NULL,
250 p_attribute9 IN VARCHAR2 := NULL,
251 p_attribute10 IN VARCHAR2 := NULL,
252 p_attribute11 IN VARCHAR2 := NULL,
253 p_attribute12 IN VARCHAR2 := NULL,
254 p_attribute13 IN VARCHAR2 := NULL,
255 p_attribute14 IN VARCHAR2 := NULL,
256 p_attribute15 IN VARCHAR2 := NULL) IS
257
258 CURSOR operating_unit_csr IS
259 SELECT ou.organization_id org_id
260 FROM hr_operating_units ou
261 WHERE mo_global.check_access(ou.organization_id) = 'Y';
262
263 m NUMBER := 0;
264 l_org_id OZF_UTILITY_PVT.operating_units_tbl;
265
266 l_start_date date;
267 l_end_date date;
268
269 BEGIN
270 IF OZF_DEBUG_LOW_ON THEN
271
272 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start CREATE_BATCH_MAIN');
273 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Start Parameter List ---');
274 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_org_id: ' || p_org_id);
275 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_supplier_id: ' || p_supplier_id);
276 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_supplier_site_id: ' || p_supplier_site_id);
277 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_product_id: ' || p_product_id);
278 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_request_id: ' || p_request_id);
279 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_fund_id: ' || p_fund_id);
280 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_start_date: ' || p_start_date);
281 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_end_date: ' || p_end_date);
282 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_period: ' || p_period);
283 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute1: ' || p_attribute1);
284 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute2: ' || p_attribute2);
285 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute3: ' || p_attribute3);
286 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute4: ' || p_attribute4);
287 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute5: ' || p_attribute5);
288 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute6: ' || p_attribute6);
289 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute7: ' || p_attribute7);
290 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute8: ' || p_attribute8);
291 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute9: ' || p_attribute9);
292 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute10: ' || p_attribute10);
293 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute11: ' || p_attribute11);
294 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute12: ' || p_attribute12);
295 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute13: ' || p_attribute13);
296 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute14: ' || p_attribute14);
297 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_attribute15: ' || p_attribute15);
298 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- End Parameter List ---');
299 END IF;
300 MO_GLOBAL.init('OZF');
301
302 IF p_org_id IS NULL THEN
303 MO_GLOBAL.set_policy_context('M', null);
304 OPEN operating_unit_csr;
305 LOOP
306 FETCH operating_unit_csr
307 INTO l_org_id(m);
308 m := m + 1;
309 EXIT WHEN operating_unit_csr%NOTFOUND;
310 END LOOP;
311 CLOSE operating_unit_csr;
312 ELSE
313 l_org_id(m) := p_org_id;
314 END IF;
315
316 IF (l_org_id.COUNT > 0) THEN
317 FOR m IN l_org_id.FIRST .. l_org_id.LAST LOOP
318 BEGIN
319 IF OZF_DEBUG_LOW_ON THEN
320 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Start Fetch of Organization ids ---');
321 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_org_id ' || to_char(l_org_id(m)));
322 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- End Fetch of Organization ids ---');
323 END IF;
324
325 MO_GLOBAL.set_policy_context('S', l_org_id(m));
326
327 IF OZF_DEBUG_HIGH_ON THEN
328 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Processing for Operating Unit: ' ||
329 MO_GLOBAL.get_ou_name(l_org_id(m)));
330 END IF;
331
332
333 IF p_start_date IS NOT NULL THEN
334 l_start_date := to_date(p_start_date,'YYYY/MM/DD HH24:MI:SS');
335 END IF ;
336
337 IF p_end_date IS NOT NULL THEN
338 l_end_date := to_date(p_end_date, 'YYYY/MM/DD HH24:MI:SS');
339 END IF ;
340
341 create_batch_sub(l_org_id(m),
342 p_supplier_id,
343 p_supplier_site_id,
344 --p_category_id ,
345 p_product_id,
346 p_request_id,
347 p_fund_id,
348 l_start_date,
349 l_end_date,
350 p_period,
351 FND_API.g_true,
352 p_attribute1,
353 p_attribute2,
354 p_attribute3,
355 p_attribute4,
356 p_attribute5,
357 p_attribute6,
358 p_attribute7,
359 p_attribute8,
360 p_attribute9,
361 p_attribute10,
362 p_attribute11,
363 p_attribute12,
364 p_attribute13,
365 p_attribute14,
366 p_attribute15);
367 EXCEPTION
368 WHEN OTHERS THEN
369 IF OZF_DEBUG_HIGH_ON THEN
370 FND_FILE.PUT_LINE(FND_FILE.LOG,
371 'Exception in CREATE_BATCH_MAIN : ' ||
372 SQLERRM);
373
374 END IF;
375 --Code added for bug#6971836
376 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Exception in CREATE_BATCH_MAIN : ' ||SQLERRM);
377
378 errbuf := 'Error in CREATE_BATCH_MAIN ' || SQLERRM;
379 retcode := 2;
380 END;
381 END LOOP;
382
383 END IF;
384 END create_batch_main;
385
386
387
388 -- Start of comments
389 -- API name : create_batch_sub
390 -- Type : Private
391 -- Pre-reqs : None.
392 -- Function : Invokes creation of Batch looping for Supplier Site and Currency.
393 -- Parameters :
394 -- IN : p_org_id IN NUMBER REQUIRED
395 -- p_supplier_id IN NUMBER REQUIRED
396 -- p_supplier_site_id IN NUMBER
397 -- p_product_id IN NUMBER
398 -- p_request_id IN NUMBER
399 -- p_fund_id IN NUMBER
400 -- p_start_date IN DATE
401 -- p_end_date IN DATE
402 -- p_period IN VARCHAR2
403 -- p_commit IN VARCHAR2
404 -- p_attribute1 IN VARCHAR2
405 -- p_attribute2 IN VARCHAR2
406 -- p_attribute3 IN VARCHAR2
407 -- p_attribute4 IN VARCHAR2
408 -- p_attribute5 IN VARCHAR2
409 -- p_attribute6 IN VARCHAR2
410 -- p_attribute7 IN VARCHAR2
411 -- p_attribute8 IN VARCHAR2
412 -- p_attribute9 IN VARCHAR2
413 -- p_attribute10 IN VARCHAR2
414 -- p_attribute11 IN VARCHAR2
415 -- p_attribute12 IN VARCHAR2
416 -- p_attribute13 IN VARCHAR2
417 -- p_attribute14 IN VARCHAR2
418 -- p_attribute15 IN VARCHAR2
419 -- End of comments
420 PROCEDURE create_batch_sub(p_org_id IN NUMBER,
421 p_supplier_id IN NUMBER,
422 p_supplier_site_id IN NUMBER,
423 --p_category_id IN NUMBER,
424 p_product_id IN NUMBER,
425 p_request_id IN NUMBER,
426 p_fund_id IN NUMBER,
427 p_start_date IN DATE,
428 p_end_date IN DATE,
429 p_period IN VARCHAR2,
430 p_commit IN VARCHAR2 := FND_API.g_false,
431 p_attribute1 IN VARCHAR2 := NULL,
432 p_attribute2 IN VARCHAR2 := NULL,
433 p_attribute3 IN VARCHAR2 := NULL,
434 p_attribute4 IN VARCHAR2 := NULL,
435 p_attribute5 IN VARCHAR2 := NULL,
436 p_attribute6 IN VARCHAR2 := NULL,
437 p_attribute7 IN VARCHAR2 := NULL,
438 p_attribute8 IN VARCHAR2 := NULL,
439 p_attribute9 IN VARCHAR2 := NULL,
440 p_attribute10 IN VARCHAR2 := NULL,
441 p_attribute11 IN VARCHAR2 := NULL,
442 p_attribute12 IN VARCHAR2 := NULL,
443 p_attribute13 IN VARCHAR2 := NULL,
444 p_attribute14 IN VARCHAR2 := NULL,
445 p_attribute15 IN VARCHAR2 := NULL) IS
446
447 l_empty_batch VARCHAR2(10);
448 l_supplier_id NUMBER;
449 l_supplier_site_id NUMBER;
450 l_org_id NUMBER := NULL; --Code added for bug#6867618
451
452 --l_category_id NUMBER;
453 l_product_id NUMBER;
454
455 -- after checking frequency
456 l_freq NUMBER;
457 l_last_run_date DATE := NULL;
458 l_fund_id NUMBER := NULL;
459 l_start_date DATE := NULL;
460 l_end_date DATE := NULL;
461 l_period VARCHAR2(100) := NULL;
462 l_currency_code VARCHAR2(15) := NULL;
463 l_query VARCHAR2(2000) := NULL;
464 l_currency VARCHAR2(15) := NULL;
465 l_freq_unit VARCHAR2(100) := NULL;
466 type r_cursor is REF CURSOR;
467 c_currency r_cursor;
468
469 l_supplier_name VARCHAR2(240);
470 l_sup_site_name VARCHAR2(15);
471
472 --org id Code added for bug#6867618
473 CURSOR get_sites(c_vendor_id NUMBER,c_org_id NUMBER) IS
474 SELECT sites.vendor_site_id
475 FROM ap_supplier_sites_all sites,
476 ozf_supp_trd_prfls_all trprf
477 WHERE sites.vendor_id = c_vendor_id AND
478 sites.org_id = c_org_id AND
479 nvl(sites.inactive_date, sysdate) >= trunc(sysdate) AND
480 trprf.cust_account_id is not null AND
481 sites.vendor_id=trprf.supplier_id AND
482 sites.vendor_site_id=trprf.supplier_site_id;
483
484 CURSOR trade_profile_values(c_supplier_site_id NUMBER,c_org_id NUMBER) IS
485 SELECT claim_currency_code
486 FROM ozf_supp_trd_prfls_all
487 WHERE supplier_site_id = c_supplier_site_id AND
488 org_id = c_org_id;
489
490 CURSOR get_freq_and_date(c_supplier_site_id NUMBER,c_org_id NUMBER) IS
491 SELECT claim_frequency, claim_frequency_unit, last_paid_date
492 FROM ozf_supp_trd_prfls_all
493 WHERE supplier_site_id = c_supplier_site_id AND
494 org_id = c_org_id;
495
496 BEGIN
497 IF OZF_DEBUG_LOW_ON THEN
498 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start create_batch_sub for org id : ' || p_org_id);
499 END IF;
500
501 --Code added for bug#6867618
502 l_org_id := p_org_id;
503
504 l_supplier_id := p_supplier_id;
505 l_supplier_site_id := p_supplier_site_id;
506 --l_category_id := p_category_id;
507 l_product_id := p_product_id;
508
509 --Code added for output log supplier name
510 Select vendor_name
511 into l_supplier_name
512 From ap_suppliers
513 Where vendor_id = l_supplier_id;
514
515 IF l_supplier_site_id IS NOT NULL THEN
516 --if site is an input parameter
517 IF OZF_DEBUG_LOW_ON THEN
518 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_supplier_site_id IS NOT NULL');
519 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Supplier Site =' || p_supplier_site_id);
520
521 END IF;
522
523 --Code added for output log supplier name
524 Select vendor_site_code
525 into l_sup_site_name
526 From ap_supplier_sites_all
527 Where vendor_site_id = l_supplier_site_id;
528
529 --Code added for bug#6971836
530 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Supplier Name =' || l_supplier_name||'('||l_supplier_id||
531 ') Site Code '||l_sup_site_name||'('||l_supplier_site_id||')');
532
533 OPEN trade_profile_values(p_supplier_site_id,l_org_id);
534 FETCH trade_profile_values
535 INTO l_currency_code;
536 CLOSE trade_profile_values;
537
538 g_currency := l_currency_code;
539
540 IF OZF_DEBUG_LOW_ON THEN
541 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Trade Profile Currency Code = ' ||
542 to_char(NVL(l_currency_code, 'Not Set')));
543
544 END IF;
545
546 --Code added for bug#6971836
547 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Trade Profile Currency Code = ' ||
548 to_char(NVL(l_currency_code, 'Not Set.')));
549
550 IF l_currency_code IS NULL then
551 --Code added for bug#6867618
552 l_query := 'SELECT distinct request_currency_code FROM OZF_SD_REQUEST_HEADERS_ALL_B ' ||
553 ' WHERE supplier_id=' || p_supplier_id ||
554 ' AND supplier_site_id=' || p_supplier_site_id;
555 ELSE
556 l_query := 'SELECT claim_currency_code FROM OZF_SUPP_TRD_PRFLS_ALL ' ||
557 ' WHERE supplier_site_id =' || p_supplier_site_id;
558
559 END IF;
560
561 IF OZF_DEBUG_LOW_ON THEN
562 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Start Query Text ---');
563 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_query = ' || to_char(l_query));
564 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- End Query Text ---');
565 END IF;
566
567 OPEN c_currency for l_query;
568 LOOP
569 FETCH c_currency INTO l_currency;
570
571 EXIT WHEN c_currency%notfound;
572
573 IF OZF_DEBUG_LOW_ON THEN
574 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Processing for Currency ---');
575 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_currency = ' || to_char(l_currency));
576 END IF;
577
578 OPEN get_freq_and_date(l_supplier_site_id,l_org_id);
579 FETCH get_freq_and_date
580 INTO l_freq, l_freq_unit, l_last_run_date;
581 CLOSE get_freq_and_date;
582
583 IF OZF_DEBUG_LOW_ON THEN
584 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Trade Profile Frequency ---');
585 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_freq = ' || l_freq);
586 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_freq_unit = ' || l_freq_unit);
587 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_last_paid_date = ' || l_last_run_date);
588 END IF;
589
590 IF l_freq_unit = 'DAYS' then
591 l_last_run_date := l_last_run_date + l_freq;
592 ELSIF l_freq_unit = 'MONTHS' then
593 SELECT add_months(l_last_run_date, l_freq)
594 INTO l_last_run_date
595 FROM dual;
596 ELSIF l_freq_unit = 'YEAR' then
597 SELECT add_months(l_last_run_date, l_freq * 12)
598 INTO l_last_run_date
599 FROM dual;
600 END IF;
601
602 IF OZF_DEBUG_LOW_ON THEN
603 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_last_run_date :' || to_char(l_last_run_date));
604 END IF;
605
606 IF sysdate >= nvl(l_last_run_date, trunc(sysdate)) THEN
607 IF OZF_DEBUG_HIGH_ON THEN
608 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch Frequency Threshold met');
609 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invoking CREATE BATCH');
610 END IF;
611
612 --Code added for Bug#6971836
613 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Batch Frequency Threshold met.');
614
615 create_batch(l_empty_batch,
616 l_supplier_id,
617 l_supplier_site_id,
618 p_org_id,
619 --l_category_id,
620 l_product_id,
621 p_request_id,
622 p_fund_id,
623 p_start_date,
624 p_end_date,
625 p_period,
626 l_currency,
627 p_attribute1,
628 p_attribute2,
629 p_attribute3,
630 p_attribute4,
631 p_attribute5,
632 p_attribute6,
633 p_attribute7,
634 p_attribute8,
635 p_attribute9,
636 p_attribute10,
637 p_attribute11,
638 p_attribute12,
639 p_attribute13,
640 p_attribute14,
641 p_attribute15);
642
643 ELSE
644 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch Frequency Threshold not met');
645
646 --Code added for Bug#6971836
647 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Batch Frequency Threshold not met.');
648
649 END IF;
650
651 END LOOP;
652 CLOSE c_currency;
653 -- after currency loop
654 -- IF l_empty_batch = 'N' THEN
655
656 UPDATE ozf_supp_trd_prfls_all
657 SET last_paid_date = sysdate
658 WHERE supplier_site_id = l_supplier_site_id;
659
660 IF fnd_api.To_Boolean(p_commit) THEN
661 COMMIT;
662 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Transaction is commited');
663 FND_FILE.PUT_LINE(FND_FILE.LOG, ' supplier_site_id= ' || l_supplier_site_id);
664 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_currency= ' || l_currency);
665 END IF;
666
667 ELSE
668 --if l_supplier_site is null
669 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_supplier_site_id IS NULL');
670
671 --Code added for bug#6971836
672 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Supplier Name =' || l_supplier_name||'('||l_supplier_id||
673 '), Supplier Site Not Provided. ');
674
675 --if site is null then create a batch for each site
676
677 FOR site_rec IN get_sites(l_supplier_id,l_org_id) LOOP
678 IF OZF_DEBUG_HIGH_ON THEN
679 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Looping for Supplier Site ID = ' ||
680 site_rec.vendor_site_id);
681 END IF;
682
683 --Code modified to get supplier site id
684 OPEN trade_profile_values(site_rec.vendor_site_id,l_org_id);
685 FETCH trade_profile_values
686 INTO l_currency_code;
687 CLOSE trade_profile_values;
688
689 --Code added for bug#6867618
690 g_currency := l_currency_code;
691
692 IF OZF_DEBUG_HIGH_ON THEN
693 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Currenty Code = ' ||l_currency_code);
694 END IF;
695
696 --Code added for Bug#6971836
697 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Currenty Code = ' ||l_currency_code);
698
699 IF l_currency_code IS NULL then
700 --Code added for bug#6867618
701 l_query := 'SELECT distinct request_currency_code FROM OZF_SD_REQUEST_HEADERS_ALL_B ' ||
702 ' WHERE supplier_site_id=' || site_rec.vendor_site_id;
703 ELSE
704 l_query := 'SELECT claim_currency_code FROM OZF_SUPP_TRD_PRFLS_ALL ' ||
705 ' WHERE supplier_site_id =' || site_rec.vendor_site_id;
706
707 END IF;
708
709 OPEN c_currency for l_query;
710 LOOP
711 FETCH c_currency
712 INTO l_currency;
713 EXIT WHEN c_currency%notfound;
714
715 IF OZF_DEBUG_LOW_ON THEN
716 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Processing for Currency ---');
717 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_currency = ' || to_char(l_currency));
718 END IF;
719
720 OPEN get_freq_and_date(site_rec.vendor_site_id,l_org_id);
721 FETCH get_freq_and_date
722 INTO l_freq, l_freq_unit, l_last_run_date;
723 CLOSE get_freq_and_date;
724
725 IF OZF_DEBUG_LOW_ON THEN
726 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Trade Profile Frequency ---');
727 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_freq = ' || l_freq);
728 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_freq_unit = ' || l_freq_unit);
729 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_last_paid_date = ' || l_last_run_date);
730 END IF;
731
732 IF l_freq_unit = 'DAYS' then
733 l_last_run_date := l_last_run_date + l_freq;
734 ELSIF l_freq_unit = 'MONTHS' then
735 SELECT add_months(l_last_run_date, l_freq)
736 INTO l_last_run_date
737 FROM dual;
738 ELSIF l_freq_unit = 'YEAR' then
739 SELECT add_months(l_last_run_date, l_freq * 12)
740 INTO l_last_run_date
741 FROM dual;
742 END IF;
743
744 FND_FILE.PUT_LINE(FND_FILE.LOG,'SuppSite Loop: l_last_run_date post calculation : ' || l_last_run_date);
745
746 IF sysdate >= NVL(l_last_run_date, TRUNC(SYSDATE)) THEN
747
748 FND_FILE.PUT_LINE(FND_FILE.LOG, 'SuppSite Loop: Batch Create Freq Requirement Met');
749
750 --Code added for Bug#6971836
751 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Batch Create Freq Requirement Met.');
752
753 create_batch(l_empty_batch,
754 l_supplier_id,
755 site_rec.vendor_site_id,
756 p_org_id,
757 --l_category_id,
758 l_product_id,
759 p_request_id,
760 l_fund_id,
761 l_start_date,
762 l_end_date,
763 l_period,
764 l_currency,
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
781 ELSE
782 FND_FILE.PUT_LINE(FND_FILE.LOG, 'SuppSite Loop: Batch Create Freq Requirement not met ');
783
784 --Code added for Bug#6971836
785 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Batch Create Freq Requirement not met. ');
786
787 END IF;
788
789 END LOOP;
790
791 UPDATE ozf_supp_trd_prfls_all
792 SET last_paid_date = sysdate
793 WHERE supplier_site_id = l_supplier_site_id;
794
795 IF fnd_api.To_Boolean(p_commit) THEN
796 COMMIT;
797 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Transaction is commited');
798 FND_FILE.PUT_LINE(FND_FILE.LOG, ' supplier_site_id= ' || l_supplier_site_id);
799 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_currency= ' || l_currency);
800 END IF;
801
802 END LOOP; -- supplier site loop
803 END IF; -- end l_supplier site condition
804
805 END create_batch_sub;
806
807
808
809
810 -- Start of comments
811 -- API name : CREATE_BATCH
812 -- Type : Private
813 -- Pre-reqs : None.
814 -- Function : Creates Batch Header and Batch Lines.
815 -- Checks for existance of batch line and batch threshold.
816 -- Rollsback header is the above is not met.
817 -- Invokes claim api and updates header if Trade Profile for Auto Calim is set
818 -- Parameters :
819 -- IN : p_empty_batch OUT VARCHAR2
820 -- p_supplier_id IN NUMBER REQUIRED
821 -- p_supplier_site_id IN NUMBER REQUIRED
822 -- p_org_id IN NUMBER REQUIRED
823 -- --p_category_id IN NUMBER
824 -- p_product_id IN NUMBER
825 -- p_request_id IN NUMBER
826 -- p_fund_id IN NUMBER
827 -- p_start_date IN DATE
828 -- p_end_date IN DATE
829 -- p_period IN VARCHAR2
830 -- p_currency_code IN VARCHAR2 REQUIRED
831 -- p_attribute1 IN VARCHAR2
832 -- p_attribute2 IN VARCHAR2
833 -- p_attribute3 IN VARCHAR2
834 -- p_attribute4 IN VARCHAR2
835 -- p_attribute5 IN VARCHAR2
836 -- p_attribute6 IN VARCHAR2
837 -- p_attribute7 IN VARCHAR2
838 -- p_attribute8 IN VARCHAR2
839 -- p_attribute9 IN VARCHAR2
840 -- p_attribute10 IN VARCHAR2
841 -- p_attribute11 IN VARCHAR2
842 -- p_attribute12 IN VARCHAR2
843 -- p_attribute13 IN VARCHAR2
844 -- p_attribute14 IN VARCHAR2
845 -- p_attribute15 IN VARCHAR2
846 -- End of comments
847 PROCEDURE CREATE_BATCH(p_empty_batch OUT NOCOPY VARCHAR2,
848 p_supplier_id IN NUMBER,
849 p_supplier_site_id IN NUMBER,
850 p_org_id IN NUMBER,
851 --p_category_id IN NUMBER,
852 p_product_id IN NUMBER,
853 p_request_id IN NUMBER,
854 p_fund_id IN NUMBER,
855 p_start_date IN DATE,
856 p_end_date IN DATE,
857 p_period IN VARCHAR2,
858 p_currency_code IN VARCHAR2,
859 p_attribute1 IN VARCHAR2 := NULL,
860 p_attribute2 IN VARCHAR2 := NULL,
861 p_attribute3 IN VARCHAR2 := NULL,
862 p_attribute4 IN VARCHAR2 := NULL,
863 p_attribute5 IN VARCHAR2 := NULL,
864 p_attribute6 IN VARCHAR2 := NULL,
865 p_attribute7 IN VARCHAR2 := NULL,
866 p_attribute8 IN VARCHAR2 := NULL,
867 p_attribute9 IN VARCHAR2 := NULL,
868 p_attribute10 IN VARCHAR2 := NULL,
869 p_attribute11 IN VARCHAR2 := NULL,
870 p_attribute12 IN VARCHAR2 := NULL,
871 p_attribute13 IN VARCHAR2 := NULL,
872 p_attribute14 IN VARCHAR2 := NULL,
873 p_attribute15 IN VARCHAR2 := NULL) is
874
875 CURSOR trade_profile_values(c_supplier_site_id NUMBER) IS
876 SELECT min_claim_amt, min_claim_amt_line_lvl, auto_debit
877 FROM ozf_supp_trd_prfls_all
878 WHERE supplier_site_id = c_supplier_site_id;
879
880 l_batch_id NUMBER;
881 l_empty_batch VARCHAR2(15) := 'Y';
882 l_auto_claim VARCHAR(1) := 'N';
883
884 l_batch_threshold NUMBER := NULL;
885 l_line_threshold NUMBER := NULL;
886 l_currency_code VARCHAR2(30);
887
888 l_claim_id NUMBER := NULL;
889 l_claim_ret_status VARCHAR2(15) := NULL;
890 l_claim_msg_count NUMBER := NULL;
891 l_claim_msg_data VARCHAR2(500) := NULL;
892 l_claim_type VARCHAR2(20) := 'SUPPLIER';
893 l_batch_sum NUMBER := NULL;
894 l_return_status VARCHAR2(15) := NULL;
895 BEGIN
896
897 OPEN trade_profile_values(p_supplier_site_id);
898 FETCH trade_profile_values
899 INTO l_batch_threshold, l_line_threshold, l_auto_claim;
900 CLOSE trade_profile_values;
901
902 SAVEPOINT BATCHHEADER;
903
904 IF OZF_DEBUG_LOW_ON THEN
905 FND_FILE.PUT_LINE(FND_FILE.LOG, '---Start CREATE_BATCH ---');
906 END IF;
907
908 CREATE_BATCH_HEADER(p_supplier_id,
909 p_supplier_site_id,
910 p_org_id,
911 l_batch_threshold,
912 l_line_threshold,
913 p_currency_code,
914 'N',
915 l_batch_id);
916
917 IF OZF_DEBUG_LOW_ON THEN
918 FND_FILE.PUT_LINE(FND_FILE.LOG,
919 'Invoked CREATE_BATCH_HEADER Successfully. Batch_ID=' ||
920 to_char(l_batch_id));
921 END IF;
922
923 --Code added for Bug#6971836
924 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Batch ' ||to_char(l_batch_id)||' created.');
925
926 CREATE_BATCH_LINES(l_batch_id,
927 p_supplier_id,
928 p_supplier_site_id,
929 p_org_id,
930 l_line_threshold,
931 p_currency_code,
932 --p_category_id,
933 p_product_id,
934 p_request_id,
935 p_fund_id,
936 p_start_date,
937 p_end_date,
938 p_period,
939 l_empty_batch,
940 p_attribute1,
941 p_attribute2,
942 p_attribute3,
943 p_attribute4,
944 p_attribute5,
945 p_attribute6,
946 p_attribute7,
947 p_attribute8,
948 p_attribute9,
949 p_attribute10,
950 p_attribute11,
951 p_attribute12,
952 p_attribute13,
953 p_attribute14,
954 p_attribute15);
955
956 IF OZF_DEBUG_HIGH_ON THEN
957 FND_FILE.PUT_LINE(FND_FILE.LOG,
958 'Invoked CREATE_BATCH_LINES Successfully. l_empty_batch=' ||
959 to_char(l_empty_batch));
960 END IF;
961
962
963 IF NVL(l_empty_batch, 'Y') = 'N' THEN
964
965 --Code added for Bug#6971836
966 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Batch Lines created.');
967
968 SELECT sum(batch_curr_claim_amount)
969 INTO l_batch_sum
970 FROM ozf_sd_batch_lines_all
971 WHERE batch_id = l_batch_id;
972
973 -- Check for Batch Amount Threshold
974
975 IF OZF_DEBUG_LOW_ON THEN
976 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Check for Batch Amount Threshold ---');
977 FND_FILE.PUT_LINE(FND_FILE.LOG,
978 'l_batch_threshold=' ||
979 to_char(l_batch_threshold) || ' :: l_batch_sum=' ||
980 to_char(l_batch_sum));
981
982 END IF;
983
984 IF NVL(l_batch_threshold, l_batch_sum - 1) > l_batch_sum
985 AND g_currency IS NOT NULL THEN
986
987 IF OZF_DEBUG_HIGH_ON THEN
988 FND_FILE.PUT_LINE(FND_FILE.LOG, '---Batch Amount Threshold Violated ---');
989 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch beign Rolledback. Batch_ID = ' ||
990 to_char(l_batch_id));
991 END IF;
992
993 --Code added for Bug#6971836
994 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Batch Amount Threshold Violated, Batch rolled back.');
995
996 ROLLBACK TO SAVEPOINT BATCHHEADER;
997 RETURN;
998 END IF;
999
1000 UPDATE_AMOUNTS(l_batch_id, l_batch_threshold);
1001
1002 OZF_SD_UTIL_PVT.SD_RAISE_EVENT(l_batch_id, 'CREATE', l_return_status); -- Raising lifecycle event for create
1003 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1004 RAISE FND_API.g_exc_error;
1005 END IF;
1006
1007 ELSE
1008
1009 IF OZF_DEBUG_HIGH_ON THEN
1010 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch has no lines, Batch id being rolled back. Batch_ID = ' ||
1011 to_char(l_batch_id));
1012 END IF;
1013
1014 --Code added for Bug#6971836
1015 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Batch has no lines or Line threshold not met '||
1016 to_char(l_batch_id)||', Batch id being rolled back.' );
1017
1018 ROLLBACK TO SAVEPOINT BATCHHEADER;
1019 RETURN;
1020
1021 END IF;
1022
1023 -- end transaction
1024
1025 IF l_auto_claim = 'Y' then
1026
1027 IF OZF_DEBUG_HIGH_ON THEN
1028 FND_FILE.PUT_LINE(FND_FILE.LOG, 'AutoClaim Flag : True.');
1029 END IF;
1030
1031 UPDATE ozf_sd_batch_headers_all
1032 SET status_code = 'APPROVED'
1033 WHERE batch_id = l_batch_id;
1034 COMMIT;
1035
1036 IF l_batch_sum > 0 THEN
1037 IF OZF_DEBUG_HIGH_ON THEN
1038 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch Sum > 0 : Invoking Claim API');
1039 END IF;
1040
1041
1042 OZF_CLAIM_ACCRUAL_PVT.Initiate_SD_Payment(1,
1043 FND_API.g_false,
1044 FND_API.g_true,
1045 FND_API.g_valid_level_full,
1046 l_claim_ret_status,
1047 l_claim_msg_count,
1048 l_claim_msg_data,
1049 l_batch_id,
1050 l_claim_type,
1051 l_claim_id);
1052
1053 IF OZF_DEBUG_HIGH_ON THEN
1054 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invoked Claim ....' );
1055 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Batch ID ' || to_char(l_batch_id));
1056 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Claim ID ' || to_char(l_claim_id) );
1057 END IF;
1058
1059 IF OZF_ERROR_ON THEN
1060 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_claim_ret_status ' || l_claim_ret_status );
1061 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_claim_msg_count ' || l_claim_msg_count );
1062 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_claim_msg_data ' || l_claim_msg_data );
1063 FOR I IN 1..l_claim_msg_count LOOP
1064 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) );
1065 END LOOP;
1066 END IF;
1067
1068 IF l_claim_ret_status = FND_API.G_RET_STS_SUCCESS THEN
1069
1070 --Code added for Bug#6971836
1071 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Claim created for batch.');
1072
1073 UPDATE ozf_sd_batch_headers_all
1074 SET status_code = 'CLOSED',
1075 claim_id = l_claim_id,
1076 last_update_date = sysdate,
1077 last_updated_by = FND_GLOBAL.USER_ID,
1078 object_version_number = object_version_number + 1
1079 WHERE batch_id = l_batch_id;
1080 ELSE
1081 IF OZF_DEBUG_HIGH_ON THEN
1082 FND_FILE.PUT_LINE(FND_FILE.LOG,
1083 'Claim process returned errors, could not update batch with ID :' || l_batch_id);
1084 END IF;
1085 --Code added for Bug#6971836
1086 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Claim process failed.');
1087 END IF;
1088
1089 END IF; --BATCH_SUM > 0
1090
1091
1092 OZF_SD_UTIL_PVT.SD_RAISE_EVENT(l_batch_id, 'CLAIM', l_return_status); -- Raising lifecycle event for claim
1093 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
1094 RAISE FND_API.g_exc_error;
1095 END IF;
1096
1097 END IF; --AUTO_CLAIM TRUE
1098 p_empty_batch := l_empty_batch;
1099
1100 END Create_Batch;
1101
1102
1103 -- Start of comments
1104 -- API name : CREATE_BATCH_HEADER
1105 -- Type : Private
1106 -- Pre-reqs : None.
1107 -- Function : Creates Batch Header record.
1108 -- Parameters :
1109 -- IN : p_supplier_id NUMBER REQUIRED
1110 -- p_supplier_site_id NUMBER REQUIRED
1111 -- p_org_id NUMBER REQUIRED
1112 -- p_batch_threshold NUMBER REQUIRED
1113 -- p_line_threshold NUMBER REQUIRED
1114 -- p_batch_currency VARCHAR2 REQUIRED
1115 -- p_batch_new VARCHAR2 REQUIRED
1116 -- OUT : p_batch_id NUMBER
1117 -- End of comments
1118 PROCEDURE CREATE_BATCH_HEADER(p_supplier_id IN NUMBER,
1119 p_supplier_site_id IN NUMBER,
1120 p_org_id IN NUMBER,
1121 p_batch_threshold IN NUMBER,
1122 p_line_threshold IN NUMBER,
1123 p_batch_currency IN VARCHAR2,
1124 p_batch_new IN VARCHAR2,
1125 p_batch_id OUT NOCOPY NUMBER ) is
1126
1127 l_supplier_id NUMBER := NULL;
1128 l_supplier_site_id NUMBER := NULL;
1129 l_batch_id NUMBER := NULL;
1130 l_org_id NUMBER := NULL;
1131 l_supplier_contact_email VARCHAR2(100) := NULL;
1132 l_supplier_contact_id NUMBER := NULL;
1133 l_supplier_contact_phone VARCHAR2(60) := NULL;
1134 l_supplier_contact_fax VARCHAR2(60) := NULL;
1135 l_batch_threshold NUMBER := NULL;
1136 l_line_threshold NUMBER := NULL;
1137 l_batch_currency VARCHAR2(15) := NULL;
1138 l_supplier_contact_name VARCHAR2(240) := NULL;
1139 l_batch_new VARCHAR2(1) := NULL;
1140
1141 --claim number variables
1142
1143 l_return_status VARCHAR2(15) := NULL;
1144 l_msg_count NUMBER := NULL;
1145 l_msg_data VARCHAR2(100) := NULL;
1146 l_return_status2 VARCHAR2(15) := NULL;
1147 l_msg_count2 NUMBER := NULL;
1148 l_msg_data2 VARCHAR2(100) := NULL;
1149 l_custom_setup_id NUMBER := NULL;
1150 l_claim_rec OZF_Claim_PVT.claim_rec_type := NULL;
1151 l_clam_def_rec_type ozf_claim_def_rule_pvt.clam_def_rec_type := NULL;
1152 l_claim_number VARCHAR2(30) := NULL;
1153 l_split_claim_id NUMBER := NULL;
1154
1155 CURSOR get_contact_details(c_supplier_site_id NUMBER) IS
1156 SELECT cont.vendor_contact_id,
1157 decode(cont.last_name,null,null,'','',cont.last_name || ', ') || nvl(cont.middle_name, '')|| ' '|| cont.first_name fullname,
1158 cont.email_address,
1159 decode(cont.phone ,NULL, NULL, cont.area_code || '-' || cont.phone) phone,
1160 decode(cont.fax,NULL, NULL, cont.fax_area_code || '-' || cont.fax) fax
1161 FROM PO_VENDOR_CONTACTS cont
1162 WHERE cont.vendor_site_id = c_supplier_site_id
1163 AND NVL(inactive_date, sysdate+1) > sysdate;
1164
1165 BEGIN
1166
1167 l_supplier_id := p_supplier_id;
1168 l_supplier_site_id := p_supplier_site_id;
1169 l_batch_new := p_batch_new;
1170
1171 SELECT ozf_sd_batch_headers_all_s.nextval INTO l_batch_id FROM dual;
1172
1173 l_org_id := p_org_id;
1174 p_batch_id := l_batch_id;
1175 l_batch_threshold := p_batch_threshold;
1176 l_line_threshold := p_line_threshold;
1177 l_batch_currency := p_batch_currency;
1178
1179 IF OZF_DEBUG_LOW_ON THEN
1180 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Start CREATE_BATCH_HEADER');
1181 END IF;
1182
1183 -- select contact and contact email from suppliers tables
1184 OPEN get_contact_details(l_supplier_site_id);
1185 FETCH get_contact_details
1186 INTO l_supplier_contact_id, l_supplier_contact_name, l_supplier_contact_email, l_supplier_contact_phone, l_supplier_contact_fax;
1187 CLOSE get_contact_details;
1188
1189 IF OZF_DEBUG_LOW_ON THEN
1190 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_supplier_contact_id' || to_char(l_supplier_contact_id));
1191 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_supplier_contact_name' || l_supplier_contact_name);
1192 END IF;
1193
1194 IF l_batch_new = 'N' THEN
1195
1196 --to get claim number
1197 l_claim_rec.claim_class := 'CLAIM';
1198 l_claim_rec.source_object_class := 'SD_SUPPLIER';
1199 OZF_CLAIM_DEF_RULE_PVT.get_clam_def_rule(p_claim_rec => l_claim_rec,
1200 x_clam_def_rec_type => l_clam_def_rec_type,
1201 x_return_status => l_return_status,
1202 x_msg_count => l_msg_count,
1203 x_msg_data => l_msg_data);
1204 l_custom_setup_id := l_clam_def_rec_type.custom_setup_id;
1205
1206 IF OZF_DEBUG_LOW_ON THEN
1207 FND_FILE.PUT_LINE(FND_FILE.LOG, 'l_custom_setup_id' || to_char(l_custom_setup_id));
1208 END IF;
1209
1210 OZF_CLAIM_PVT.Get_Claim_Number(l_split_claim_id,
1211 l_custom_setup_id,
1212 l_claim_number,
1213 l_msg_data2,
1214 l_msg_count2,
1215 l_return_status2);
1216
1217 END IF;
1218
1219 IF OZF_DEBUG_HIGH_ON THEN
1220 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Claim Number = ' || to_char(l_claim_number));
1221 END IF;
1222
1223 INSERT INTO ozf_sd_batch_headers_all
1224 (batch_id,
1225 object_version_number,
1226 batch_number,
1227 claim_number,
1228 claim_minor_version,
1229 vendor_id,
1230 vendor_site_id,
1231 vendor_contact_id,
1232 vendor_contact_name,
1233 vendor_email,
1234 vendor_phone,
1235 vendor_fax,
1236 batch_line_amount_threshold,
1237 batch_amount_threshold,
1238 currency_code,
1239 credit_code,
1240 status_code,
1241 creation_date,
1242 last_update_date,
1243 last_updated_by,
1244 request_id,
1245 created_by,
1246 created_from,
1247 last_update_login,
1248 program_application_id,
1249 program_update_date,
1250 program_id,
1251 transfer_type,
1252 org_id)
1253 VALUES
1254 (l_batch_id,
1255 1,
1256 l_batch_id,
1257 l_claim_number,
1258 1,
1259 l_supplier_id, --supplier_party_id
1260 l_supplier_site_id, --supplier site
1261 l_supplier_contact_id,
1262 l_supplier_contact_name,
1263 l_supplier_contact_email,
1264 l_supplier_contact_phone,
1265 l_supplier_contact_fax,
1266 l_batch_threshold, -- From TP
1267 l_line_threshold, -- From TP
1268 l_batch_currency, -- From TP
1269 'D', -- Value can be Debit or Credit. defaulted to Credit
1270 'NEW', --default status for new batches
1271 sysdate,
1272 sysdate,
1273 FND_GLOBAL.USER_ID, --las_updated_by
1274 FND_GLOBAL.CONC_REQUEST_ID, --? conc program id
1275 FND_GLOBAL.USER_ID, --created by
1276 null, --created from --??
1277 FND_GLOBAL.CONC_LOGIN_ID, -- last_update_login
1278 FND_GLOBAL.PROG_APPL_ID, -- program app id
1279 sysdate,
1280 FND_GLOBAL.CONC_PROGRAM_ID, --program id
1281 null, --l_transfer_type to be updated when batch is exported
1282 l_org_id --default batch Org ID
1283 );
1284 END Create_Batch_Header;
1285
1286 PROCEDURE CREATE_BATCH_LINES(p_batch_id IN NUMBER,
1287 p_supplier_id IN NUMBER,
1288 p_supplier_site_id IN NUMBER,
1289 p_org_id IN NUMBER,
1290 p_thresh_line_limit IN NUMBER,
1291 p_batch_currency IN VARCHAR2,
1292 --p_category_id IN NUMBER,
1293 p_product_id IN NUMBER,
1294 p_request_id IN NUMBER,
1295 p_fund_id IN NUMBER,
1296 p_start_date IN DATE,
1297 p_end_date IN DATE,
1298 p_period IN VARCHAR2,
1299 p_empty_batch OUT NOCOPY VARCHAR2,
1300 p_attribute1 IN VARCHAR2 := NULL,
1301 p_attribute2 IN VARCHAR2 := NULL,
1302 p_attribute3 IN VARCHAR2 := NULL,
1303 p_attribute4 IN VARCHAR2 := NULL,
1304 p_attribute5 IN VARCHAR2 := NULL,
1305 p_attribute6 IN VARCHAR2 := NULL,
1306 p_attribute7 IN VARCHAR2 := NULL,
1307 p_attribute8 IN VARCHAR2 := NULL,
1308 p_attribute9 IN VARCHAR2 := NULL,
1309 p_attribute10 IN VARCHAR2 := NULL,
1310 p_attribute11 IN VARCHAR2 := NULL,
1311 p_attribute12 IN VARCHAR2 := NULL,
1312 p_attribute13 IN VARCHAR2 := NULL,
1313 p_attribute14 IN VARCHAR2 := NULL,
1314 p_attribute15 IN VARCHAR2 := NULL) is
1315
1316 l_batch_id NUMBER := NULL;
1317 l_supplier_id NUMBER := NULL;
1318 l_supplier_site_id NUMBER := NULL;
1319 l_org_id NUMBER := NULL;
1320 l_inv_org_id NUMBER := NULL;
1321 l_count NUMBER := 0;
1322
1323 --for cursor execution
1324 l_lines_csr NUMBER := NULL;
1325 l_lines_sql VARCHAR2(4000) := NULL;
1326 l_ignore NUMBER;
1327
1328 -- for define columns
1329 l_batch_line_id NUMBER := NULL;
1330 l_batch_line_number NUMBER := NULL;
1331 l_utilization_id NUMBER := NULL;
1332 l_agreement_number VARCHAR2(100) := NULL;
1333 l_ship_to_org_id NUMBER := NULL;
1334 l_ship_to_contact_id NUMBER := NULL;
1335 l_ship_to_customer_site_id NUMBER := NULL;
1336 l_sold_to_customer_id NUMBER := NULL;
1337 l_SOLD_TO_CONTACT_ID NUMBER := NULL;
1338 l_SOLD_TO_SITE_USE_ID NUMBER := NULL;
1339 l_end_customer_id NUMBER := NULL;
1340 l_end_customer_contact_id NUMBER := NULL;
1341 l_end_customer_site_id NUMBER := NULL;
1342 l_order_header_id NUMBER := NULL;
1343 l_order_line_number NUMBER := NULL;
1344 l_invoice_number NUMBER := NULL;
1345 l_invoice_line_number NUMBER := NULL;
1346 l_resale_price_currency_code VARCHAR2(15) := NULL;
1347 l_resales_price NUMBER := NULL;
1348 l_list_price_currency_code VARCHAR2(15) := NULL;
1349 l_list_price NUMBER := NULL;
1350 l_agreement_currency_code VARCHAR2(15) := NULL;
1351 l_agreement_price NUMBER := NULL;
1352 l_claim_amount NUMBER := NULL;
1353 l_batch_curr_claim_amount NUMBER := NULL;
1354 l_item_id NUMBER := NULL;
1355 l_vendor_item_id NUMBER := NULL;
1356 l_shipped_quantity_uom VARCHAR2(100) := NULL;
1357 l_quantity_shipped NUMBER := NULL;
1358 l_order_date DATE := NULL;
1359 l_claim_amount_currency_code VARCHAR2(15) := NULL;
1360 l_acct_amount_remaining NUMBER := NULL;
1361 l_univ_curr_amount_remaining NUMBER := NULL;
1362 l_amount_remaining NUMBER := NULL;
1363 l_ozf_gl_entries VARCHAR2(15) := NULL;
1364
1365 --from trade profile
1366 l_thresh_line_limit NUMBER := NULL;
1367 l_batch_currency VARCHAR2(15) := NULL;
1368
1369 --parameters
1370 l_fund_id NUMBER := NULL;
1371 l_start_date DATE;
1372 l_end_date DATE := NULL;
1373 l_period VARCHAR2(50) := NULL;
1374 l_period_start DATE := NULL;
1375 l_period_end DATE := NULL;
1376 l_func_call_string VARCHAR2(200) := NULL;
1377 --cursor for period param
1378 CURSOR get_period_limits(c_period VARCHAR2) IS
1379 SELECT start_date, end_date
1380 FROM gl_periods
1381 WHERE period_name = c_period and
1382 period_set_name =
1383 fnd_profile.value('AMS_CAMPAIGN_DEFAULT_CALENDER');
1384
1385 BEGIN
1386 l_batch_id := p_batch_id;
1387 l_org_id := p_org_id;
1388 l_thresh_line_limit := p_thresh_line_limit;
1389 l_batch_currency := p_batch_currency;
1390
1391 l_fund_id := p_fund_id;
1392 l_start_date := p_start_date;
1393 l_end_date := p_end_date;
1394 l_period := p_period;
1395 l_ozf_gl_entries := fnd_profile.value('OZF_ORDER_GLPOST_PHASE');
1396
1397 FND_DSQL.init;
1398
1399 FND_DSQL.add_text('SELECT ');
1400 FND_DSQL.add_text('OZF_SD_BATCH_LINES_ALL_S.NEXTVAL , ');
1401 FND_DSQL.add_text('FU.UTILIZATION_ID, ');
1402 FND_DSQL.add_text('RH.AUTHORIZATION_NUMBER, ');
1403 FND_DSQL.add_text('OEL.SHIP_TO_ORG_ID, ');
1404 FND_DSQL.add_text('OEL.SHIP_TO_CONTACT_ID, ');
1405 FND_DSQL.add_text('HZCA.PARTY_ID, ');
1406 FND_DSQL.add_text('OEH.SOLD_TO_CONTACT_ID, ');
1407 FND_DSQL.add_text('OEH.SOLD_TO_SITE_USE_ID, ');
1408
1409 FND_DSQL.add_text('OEL.END_CUSTOMER_ID, ');
1410 FND_DSQL.add_text('OEL.END_CUSTOMER_CONTACT_ID, ');
1411
1412 FND_DSQL.add_text('OEL.HEADER_ID, ');
1413 FND_DSQL.add_text('OEL.LINE_ID, ');
1414
1415
1416 FND_DSQL.add_text('(SELECT CTLA.CUSTOMER_TRX_ID FROM RA_CUSTOMER_TRX_LINES_ALL CTLA WHERE CTLA.INTERFACE_LINE_ATTRIBUTE1 = TO_CHAR(OEH.ORDER_NUMBER)
1417 AND CTLA.SALES_ORDER_LINE = TO_CHAR(OEL.LINE_NUMBER)
1418 AND CTLA.INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(OEL.LINE_ID)
1419 AND CTLA.LINE_TYPE = ''LINE''
1420 AND ROWNUM = 1) TRX_NUMBER,');
1421
1422 FND_DSQL.add_text('(SELECT CTLA.SALES_ORDER_LINE FROM RA_CUSTOMER_TRX_LINES_ALL CTLA WHERE CTLA.INTERFACE_LINE_ATTRIBUTE1 = TO_CHAR(OEH.ORDER_NUMBER)
1423 AND CTLA.SALES_ORDER_LINE = TO_CHAR(OEL.LINE_NUMBER)
1424 AND CTLA.INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(OEL.LINE_ID)
1425 AND CTLA.LINE_TYPE = ''LINE''
1426 AND ROWNUM = 1) LINE_NUMBER, ');
1427
1428
1429 FND_DSQL.add_text('OEH.TRANSACTIONAL_CURR_CODE, ');
1430
1431 -- resale price = order.unit_selling price - ( fu.plan_currency_amt_remaining / order qty (based on profile))
1432 IF l_ozf_gl_entries = 'SHIPPED' then
1433 FND_DSQL.add_text('OEL.UNIT_SELLING_PRICE-(FU.PLAN_CURR_AMOUNT_REMAINING / NVL(oel.shipped_quantity, NVL(oel.invoiced_quantity, NVL(oel.ordered_quantity, 1)))), ');
1434 END IF;
1435
1436 IF l_ozf_gl_entries = 'INVOICED' THEN
1437 FND_DSQL.add_text('OEL.UNIT_SELLING_PRICE-(FU.PLAN_CURR_AMOUNT_REMAINING / NVL(oel.invoiced_quantity, NVL(oel.shipped_quantity, NVL(oel.ordered_quantity, 1)))), ');
1438 END IF;
1439
1440 FND_DSQL.add_text('RH.REQUEST_CURRENCY_CODE, ');
1441 FND_DSQL.add_text('OEL.UNIT_LIST_PRICE, ');
1442 FND_DSQL.add_text('RH.REQUEST_CURRENCY_CODE, ');
1443
1444 -- Call CONV_DISC_TO_OFFER_CURR_AMOUNT where
1445 -- To Currency OFRS.TRANSACTION_CURRENCY_CODE
1446 -- From Currency RL. APPROVED_DISCOUNT_CURRENCY
1447 -- From Amount RL.APPROVED_DISCOUNT_VALUE
1448 -- Batch ID p_batch_id
1449
1450 l_func_call_string := 'OZF_SD_BATCH_PVT.CONV_DISC_TO_OFFER_CURR_AMOUNT(OFRS.TRANSACTION_CURRENCY_CODE,RL. APPROVED_DISCOUNT_CURRENCY, RL.APPROVED_DISCOUNT_VALUE, ' || p_batch_id || ' )';
1451
1452 FND_DSQL.add_text('decode (rl.APPROVED_DISCOUNT_TYPE,
1453 ''%'', (OEL.UNIT_LIST_PRICE * (1 - RL.APPROVED_DISCOUNT_VALUE/100)),
1454 ''AMT'', (OEL.UNIT_LIST_PRICE - ' || l_func_call_string || '),
1455 ''NEWPRICE'', '|| l_func_call_string || ', ' ||
1456 l_func_call_string || ') APPROVED_DISCOUNT_VALUE, ');
1457
1458 FND_DSQL.add_text('FU.PLAN_CURR_AMOUNT_REMAINING, ');
1459 FND_DSQL.add_text('OZF_SD_BATCH_PVT.GET_BATCH_CURRENCY_AMOUNT(FU.UTILIZATION_ID,' );
1460 FND_DSQL.add_bind(''||p_batch_currency||'' );
1461 FND_DSQL.add_text(',');
1462 FND_DSQL.add_bind(p_batch_id);
1463 FND_DSQL.add_text('),');
1464
1465 FND_DSQL.add_text('FU.PRODUCT_ID, ');
1466
1467 FND_DSQL.add_text('NVL(OEL.SHIPPING_QUANTITY_UOM, OEL.ORDER_QUANTITY_UOM), ');
1468
1469 IF l_ozf_gl_entries is null then
1470 l_ozf_gl_entries := 'SHIPPED';
1471 END IF;
1472
1473 IF l_ozf_gl_entries = 'SHIPPED' THEN
1474 FND_DSQL.add_text('NVL(oel.shipped_quantity, NVL(oel.invoiced_quantity, NVL(oel.ordered_quantity, 1))), ');
1475 END IF;
1476
1477 IF l_ozf_gl_entries = 'INVOICED' THEN
1478 FND_DSQL.add_text('NVL(oel.invoiced_quantity, NVL(oel.shipped_quantity, NVL(oel.ordered_quantity, 1))), ');
1479 END IF;
1480
1481 FND_DSQL.add_text('OEH.ordered_date, ');
1482
1483
1484 FND_DSQL.add_text('OFRS.TRANSACTION_CURRENCY_CODE, ');
1485 FND_DSQL.add_text('FU.ACCTD_AMOUNT_REMAINING, ');
1486 FND_DSQL.add_text('FU.UNIV_CURR_AMOUNT_REMAINING, ');
1487 FND_DSQL.add_text('FU.AMOUNT_REMAINING, ');
1488 FND_DSQL.add_text('RL.ORG_ID '); -- Inventory Org ID
1489
1490 --BEGIN FROM CLAUSE
1491 FND_DSQL.add_text('FROM OZF_FUNDS_UTILIZED_ALL_B FU, ');
1492 FND_DSQL.add_text('OE_ORDER_HEADERS_ALL OEH, ');
1493 FND_DSQL.add_text('OE_ORDER_LINES_ALL OEL, ');
1494 FND_DSQL.add_text('OZF_SD_REQUEST_HEADERS_ALL_B RH, ');
1495 FND_DSQL.add_text('OZF_SD_REQUEST_LINES_ALL RL , ');
1496
1497 FND_DSQL.add_text('HZ_CUST_SITE_USES_ALL HZCSU, ');
1498 FND_DSQL.add_text('HZ_CUST_ACCT_SITES_ALL HZCAS, ');
1499 FND_DSQL.add_text('HZ_CUST_ACCOUNTS HZCA, ');
1500 FND_DSQL.add_text('OZF_OFFERS OFRS ');
1501
1502 --BEGIN WHERE CLAUSE
1503
1504 FND_DSQL.add_text('WHERE RH.OFFER_ID = FU.PLAN_ID AND ');
1505 FND_DSQL.add_text('OFRS.QP_LIST_HEADER_ID = RH.OFFER_ID AND ');
1506 FND_DSQL.add_text('FU.OBJECT_ID = OEH.HEADER_ID AND ');
1507 FND_DSQL.add_text('FU.AMOUNT_REMAINING <> 0 AND ');
1508 FND_DSQL.add_text('FU.OBJECT_TYPE = ''ORDER'' AND ');
1509 FND_DSQL.add_text('OEL.HEADER_ID = OEH.HEADER_ID AND ');
1510 FND_DSQL.add_text('RH.REQUEST_HEADER_ID = RL.REQUEST_HEADER_ID AND ');
1511
1512 FND_DSQL.add_text('RH.SUPPLIER_SITE_ID = ' );
1513 FND_DSQL.add_bind(p_supplier_site_id);
1514 FND_DSQL.add_text(' AND RH.ORG_ID = ' );
1515 FND_DSQL.add_bind(l_org_id);
1516
1517 FND_DSQL.add_text(' AND OEH.invoice_to_org_id = HZCSU.SITE_USE_ID AND ');
1518 FND_DSQL.add_text('HZCSU.CUST_ACCT_SITE_ID = HZCAS.CUST_ACCT_SITE_ID AND ');
1519 FND_DSQL.add_text('HZCAS.CUST_ACCOUNT_ID = HZCA.CUST_ACCOUNT_ID AND ');
1520
1521 FND_DSQL.add_text('((FU.PRODUCT_ID = RL.INVENTORY_ITEM_ID ) OR ');
1522 FND_DSQL.add_text('FU.PRODUCT_ID IN
1523 (SELECT MIC.INVENTORY_ITEM_ID
1524 FROM MTL_ITEM_CATEGORIES MIC,
1525 ENI_PROD_DEN_HRCHY_PARENTS_V P,
1526 ENI_PROD_DENORM_HRCHY_V H,
1527 MTL_SYSTEM_ITEMS_B_KFV B
1528 WHERE P.CATEGORY_ID = MIC.CATEGORY_ID AND
1529 MIC.ORGANIZATION_ID = B.ORGANIZATION_ID AND
1530 P.CATEGORY_SET_ID = MIC.CATEGORY_SET_ID AND
1531 MIC.CATEGORY_SET_ID = H.CATEGORY_SET_ID AND
1532 MIC.CATEGORY_ID = H.CHILD_ID AND
1533 (P.DISABLE_DATE is null OR P.DISABLE_DATE > SYSDATE)
1534 AND
1535 H.PARENT_ID = RL.prod_catg_id )) AND ');
1536
1537 FND_DSQL.add_text('FU.ORDER_LINE_ID = OEL.LINE_ID AND ');
1538 FND_DSQL.add_text('FU.GL_POSTED_FLAG = ''Y'' AND ');
1539
1540
1541 IF l_ozf_gl_entries = 'SHIPPED' THEN
1542 FND_DSQL.add_text('oel.cancelled_flag = ''N'' AND oel.booked_flag = ''Y'' AND ');
1543 END IF;
1544 IF l_ozf_gl_entries = 'INVOICED' THEN
1545 FND_DSQL.add_text('oel.cancelled_flag = ''N'' AND oel.booked_flag = ''Y'' AND oel.flow_status_code in (''CLOSED'',''INVOICED'') AND ');
1546 END IF;
1547
1548 FND_DSQL.add_text(' RH.OFFER_TYPE= ''ACCRUAL'' ');
1549 -- FND_DSQL.add_text(' RL.ORG_ID =' || l_org_id); -- AND '); --this was commented already
1550
1551 IF g_currency IS NULL THEN
1552 -- currency not set on trade profile
1553 FND_DSQL.add_text(' AND RH.REQUEST_CURRENCY_CODE = ' );
1554 FND_DSQL.add_bind('' || l_batch_currency || '');
1555 END IF;
1556
1557 --request ID filter
1558 IF p_request_id is not null then
1559 FND_DSQL.add_text(' AND RH.REQUEST_HEADER_ID = ' );
1560 FND_DSQL.add_bind(p_request_id);
1561 END IF;
1562
1563 --product filter
1564 IF p_product_id is not null then
1565 FND_DSQL.add_text(' AND FU.PRODUCT_ID = ' );
1566 FND_DSQL.add_bind(p_product_id);
1567 END IF;
1568
1569 -- offer filter
1570 IF p_fund_id is not null then
1571 FND_DSQL.add_text(' AND FU.fund_id = ' );
1572 FND_DSQL.add_bind(p_fund_id);
1573 END IF;
1574
1575 --start date filter
1576 IF p_start_date is not null then
1577 FND_DSQL.add_text(' AND FU.creation_date >=' );
1578 FND_DSQL.add_bind('' || p_start_date || '');
1579 END IF;
1580
1581 --end date filter
1582 IF p_end_date is not null then
1583 FND_DSQL.add_text(' AND FU.creation_date <=' );
1584 FND_DSQL.add_bind('' || p_end_date || '');
1585 END IF;
1586
1587 --period filter
1588 IF p_period is not null then
1589 open get_period_limits(p_period);
1590 FETCH get_period_limits
1591 INTO l_period_start, l_period_end;
1592 CLOSE get_period_limits;
1593
1594 IF l_period_start is not null then
1595 FND_DSQL.add_text(' AND FU.creation_date >= ');
1596 FND_DSQL.add_bind(''||to_date(to_char(l_period_start,'DD-MM-YYYY'),'DD-MM-YYYY')||'');
1597 END IF;
1598
1599 IF l_period_end is not null then
1600 FND_DSQL.add_text(' AND FU.creation_date <= ');
1601 FND_DSQL.add_bind(''||to_date(to_char(l_period_end,'DD-MM-YYYY'),'DD-MM-YYYY')||'');
1602 END IF;
1603
1604 END IF;
1605
1606 -- attribute1 filter
1607 IF p_attribute1 is not null then
1608 FND_DSQL.add_text(' AND FU.ATTRIBUTE1 =' );
1609 FND_DSQL.add_bind('' || p_attribute1 || '');
1610 END IF;
1611
1612 -- attribute2 filter
1613 IF p_attribute2 is not null then
1614 FND_DSQL.add_text(' AND FU.ATTRIBUTE2 =' );
1615 FND_DSQL.add_bind('' || p_attribute2 || '');
1616 END IF;
1617
1618 -- attribute3 filter
1619 IF p_attribute3 is not null then
1620 FND_DSQL.add_text(' AND FU.ATTRIBUTE3 =' );
1621 FND_DSQL.add_bind('' || p_attribute3 || '');
1622 END IF;
1623
1624 -- attribute4 filter
1625 IF p_attribute4 is not null then
1626 FND_DSQL.add_text(' AND FU.ATTRIBUTE4 =' );
1627 FND_DSQL.add_bind('' || p_attribute4 || '');
1628 END IF;
1629
1630 -- attribute5 filter
1631 IF p_attribute5 is not null then
1632 FND_DSQL.add_text(' AND FU.ATTRIBUTE5 =' );
1633 FND_DSQL.add_bind('' || p_attribute5 || '');
1634 END IF;
1635
1636 -- attribute6 filter
1637 IF p_attribute6 is not null then
1638 FND_DSQL.add_text(' AND FU.ATTRIBUTE6 =' );
1639 FND_DSQL.add_bind('' || p_attribute6 || '');
1640 END IF;
1641
1642 -- attribute7 filter
1643 IF p_attribute7 is not null then
1644 FND_DSQL.add_text(' AND FU.ATTRIBUTE7 =' );
1645 FND_DSQL.add_bind('' || p_attribute7 || '');
1646 END IF;
1647
1648 -- attribute8 filter
1649 IF p_attribute8 is not null then
1650 FND_DSQL.add_text(' AND FU.ATTRIBUTE8 =' );
1651 FND_DSQL.add_bind('' || p_attribute8 || '');
1652 END IF;
1653
1654 -- attribute9 filter
1655 IF p_attribute9 is not null then
1656 FND_DSQL.add_text(' AND FU.ATTRIBUTE9 =' );
1657 FND_DSQL.add_bind('' || p_attribute9 || '');
1658 END IF;
1659
1660 -- attribute10 filter
1661 IF p_attribute10 is not null then
1662 FND_DSQL.add_text(' AND FU.ATTRIBute10 =' );
1663 FND_DSQL.add_bind('' || p_attribute10 || '');
1664 END IF;
1665
1666 -- attribute11 filter
1667 IF p_attribute11 is not null then
1668 FND_DSQL.add_text(' AND FU.ATTRIBute11 =' );
1669 FND_DSQL.add_bind('' || p_attribute11 || '');
1670 END IF;
1671
1672 -- attribute12 filter
1673 IF p_attribute12 is not null then
1674 FND_DSQL.add_text(' AND FU.ATTRIBute12 =' );
1675 FND_DSQL.add_bind('' || p_attribute12 || '');
1676 END IF;
1677
1678 -- attribute13 filter
1679 IF p_attribute13 is not null then
1680 FND_DSQL.add_text(' AND FU.ATTRIBute13 =' );
1681 FND_DSQL.add_bind('' || p_attribute13 || '');
1682 END IF;
1683
1684 -- attribute14 filter
1685 IF p_attribute14 is not null then
1686 FND_DSQL.add_text(' AND FU.ATTRIBute14 =' );
1687 FND_DSQL.add_bind('' || p_attribute14 || '');
1688 END IF;
1689
1690 -- attribute15 filter
1691 IF p_attribute15 is not null then
1692 FND_DSQL.add_text(' AND FU.ATTRIBute15 =' );
1693 FND_DSQL.add_bind('' || p_attribute15 || '');
1694 END IF;
1695
1696 --creating cursor
1697 l_lines_csr := DBMS_SQL.open_cursor;
1698 l_lines_sql := FND_DSQL.get_text(FALSE); -- Get SQL query built above
1699
1700 IF OZF_DEBUG_LOW_ON THEN
1701 FND_FILE.PUT_LINE(FND_FILE.LOG, '---Start Query Text ---');
1702 FND_FILE.PUT_LINE(FND_FILE.LOG,' l_lines_sql = ' || l_lines_sql);
1703 FND_FILE.PUT_LINE(FND_FILE.LOG, '---End Query Text ---');
1704 END IF;
1705
1706 IF OZF_DEBUG_LOW_ON THEN
1707 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, '--- Values for Binds ---' );
1708 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 );
1709 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 );
1710 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 );
1711 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 );
1712 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 );
1713 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 );
1714 END IF;
1715
1716 IF OZF_DEBUG_LOW_ON THEN
1717 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' --Start Query Text--');
1718 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' l_lines_sql = ' || l_lines_sql);
1719 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, ' --End Query Text --');
1720 END IF;
1721
1722 FND_DSQL.set_cursor(l_lines_csr);
1723 DBMS_SQL.parse(l_lines_csr, l_lines_sql, DBMS_SQL.native);
1724 FND_DSQL.do_binds;
1725
1726 --define columns
1727
1728 DBMS_SQL.define_column(l_lines_csr, 1, l_batch_line_id);
1729 DBMS_SQL.define_column(l_lines_csr, 2, l_utilization_id);
1730 DBMS_SQL.define_column(l_lines_csr, 3, l_agreement_number, 100);
1731 DBMS_SQL.define_column(l_lines_csr, 4, l_ship_to_org_id);
1732 DBMS_SQL.define_column(l_lines_csr, 5, l_ship_to_contact_id);
1733
1734 DBMS_SQL.define_column(l_lines_csr, 6, l_sold_to_customer_id);
1735 DBMS_SQL.define_column(l_lines_csr, 7, l_SOLD_TO_CONTACT_ID);
1736 DBMS_SQL.define_column(l_lines_csr, 8, l_SOLD_TO_SITE_USE_ID);
1737 DBMS_SQL.define_column(l_lines_csr, 9, l_end_customer_id);
1738 DBMS_SQL.define_column(l_lines_csr, 10, l_end_customer_contact_id);
1739
1740 DBMS_SQL.define_column(l_lines_csr, 11, l_order_header_id);
1741 DBMS_SQL.define_column(l_lines_csr, 12, l_order_line_number);
1742 DBMS_SQL.define_column(l_lines_csr, 13, l_invoice_number);
1743 DBMS_SQL.define_column(l_lines_csr, 14, l_invoice_line_number);
1744 DBMS_SQL.define_column(l_lines_csr, 15, l_resale_price_currency_code, 15);
1745 DBMS_SQL.define_column(l_lines_csr, 16, l_resales_price);
1746 DBMS_SQL.define_column(l_lines_csr, 17, l_list_price_currency_code, 15);
1747 DBMS_SQL.define_column(l_lines_csr, 18, l_list_price);
1748 DBMS_SQL.define_column(l_lines_csr, 19, l_agreement_currency_code, 15);
1749 DBMS_SQL.define_column(l_lines_csr, 20, l_agreement_price);
1750 DBMS_SQL.define_column(l_lines_csr, 21, l_claim_amount);
1751 DBMS_SQL.define_column(l_lines_csr, 22, l_batch_curr_claim_amount);
1752 DBMS_SQL.define_column(l_lines_csr, 23, l_item_id);
1753
1754 DBMS_SQL.define_column(l_lines_csr, 24, l_shipped_quantity_uom, 100);
1755 DBMS_SQL.define_column(l_lines_csr, 25, l_quantity_shipped);
1756 DBMS_SQL.define_column(l_lines_csr, 26, l_order_date);
1757 DBMS_SQL.define_column(l_lines_csr, 27, l_claim_amount_currency_code, 15);
1758 DBMS_SQL.define_column(l_lines_csr, 28, l_acct_amount_remaining);
1759 DBMS_SQL.define_column(l_lines_csr, 29, l_univ_curr_amount_remaining);
1760 DBMS_SQL.define_column(l_lines_csr, 30, l_amount_remaining);
1761 DBMS_SQL.define_column(l_lines_csr, 31, l_inv_org_id);
1762
1763 --execute cursor
1764
1765 l_ignore := DBMS_SQL.execute(l_lines_csr);
1766 p_empty_batch := 'Y'; -- to check if any lines were created
1767 l_batch_line_number := 1;
1768
1769 LOOP
1770
1771 IF OZF_DEBUG_LOW_ON THEN
1772 FND_FILE.PUT_LINE(FND_FILE.LOG, 'DBMS_SQL.FETCH_ROWS loop - Before Fetch');
1773 END IF;
1774
1775 EXIT WHEN DBMS_SQL.FETCH_ROWS(l_lines_csr) = 0;
1776
1777 IF OZF_DEBUG_HIGH_ON THEN
1778 FND_FILE.PUT_LINE(FND_FILE.LOG, 'DBMS_SQL.FETCH_ROWS loop - After Fetch');
1779 END IF;
1780
1781 DBMS_SQL.column_value(l_lines_csr, 1, l_batch_line_id);
1782 DBMS_SQL.column_value(l_lines_csr, 2, l_utilization_id);
1783 DBMS_SQL.column_value(l_lines_csr, 3, l_agreement_number);
1784 DBMS_SQL.column_value(l_lines_csr, 4, l_ship_to_org_id);
1785 DBMS_SQL.column_value(l_lines_csr, 5, l_ship_to_contact_id);
1786
1787 DBMS_SQL.column_value(l_lines_csr, 6, l_sold_to_customer_id);
1788 DBMS_SQL.column_value(l_lines_csr, 7, l_SOLD_TO_CONTACT_ID);
1789 DBMS_SQL.column_value(l_lines_csr, 8, l_SOLD_TO_SITE_USE_ID);
1790 DBMS_SQL.column_value(l_lines_csr, 9, l_end_customer_id);
1791 DBMS_SQL.column_value(l_lines_csr, 10, l_end_customer_contact_id);
1792
1793 DBMS_SQL.column_value(l_lines_csr, 11, l_order_header_id);
1794 DBMS_SQL.column_value(l_lines_csr, 12, l_order_line_number);
1795 DBMS_SQL.column_value(l_lines_csr, 13, l_invoice_number);
1796 DBMS_SQL.column_value(l_lines_csr, 14, l_invoice_line_number);
1797 DBMS_SQL.column_value(l_lines_csr, 15, l_resale_price_currency_code);
1798 DBMS_SQL.column_value(l_lines_csr, 16, l_resales_price);
1799 DBMS_SQL.column_value(l_lines_csr, 17, l_list_price_currency_code);
1800 DBMS_SQL.column_value(l_lines_csr, 18, l_list_price);
1801 DBMS_SQL.column_value(l_lines_csr, 19, l_agreement_currency_code);
1802 DBMS_SQL.column_value(l_lines_csr, 20, l_agreement_price);
1803 DBMS_SQL.column_value(l_lines_csr, 21, l_claim_amount);
1804 DBMS_SQL.column_value(l_lines_csr, 22, l_batch_curr_claim_amount);
1805 DBMS_SQL.column_value(l_lines_csr, 23, l_item_id);
1806
1807 DBMS_SQL.column_value(l_lines_csr, 24, l_shipped_quantity_uom);
1808 DBMS_SQL.column_value(l_lines_csr, 25, l_quantity_shipped);
1809 DBMS_SQL.column_value(l_lines_csr, 26, l_order_date);
1810 DBMS_SQL.column_value(l_lines_csr, 27, l_claim_amount_currency_code);
1811 DBMS_SQL.column_value(l_lines_csr, 28, l_acct_amount_remaining);
1812 DBMS_SQL.column_value(l_lines_csr, 29, l_univ_curr_amount_remaining);
1813 DBMS_SQL.column_value(l_lines_csr, 30, l_amount_remaining);
1814 DBMS_SQL.column_value(l_lines_csr, 31, l_inv_org_id);
1815
1816 IF OZF_DEBUG_HIGH_ON THEN
1817 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Check for Line Amount Threshold ---');
1818 FND_FILE.PUT_LINE(FND_FILE.LOG,
1819 'l_thresh_line_limit = ' ||
1820 to_char(l_thresh_line_limit) ||
1821 'claim amount: = ' ||
1822 to_char(l_batch_curr_claim_amount));
1823 END IF;
1824 IF ( nvl(l_thresh_line_limit, l_batch_curr_claim_amount - 1) <
1825 l_batch_curr_claim_amount AND
1826 g_currency IS NOT NULL ) OR
1827 g_currency IS NULL THEN
1828
1829 p_empty_batch := 'N';
1830 IF OZF_DEBUG_LOW_ON THEN
1831 FND_FILE.PUT_LINE(FND_FILE.LOG,
1832 '--- Values fetched for Batch Line ---');
1833 FND_FILE.PUT_LINE(FND_FILE.LOG,
1834 'values being fetched from SQL' ||
1835 to_char(l_batch_line_id) || '*' || -- line sequence.nextval
1836 to_char(1) || '*' || to_char(l_batch_id) || '*' ||
1837 to_char(l_batch_line_number) || '*' ||
1838 to_char(l_utilization_id) || '*' ||
1839 to_char(l_agreement_number) || '*' ||
1840 to_char(l_ship_to_org_id) || '*' ||
1841 to_char(l_ship_to_contact_id) || '*' ||
1842 to_char(l_sold_to_customer_id) || '*' ||
1843 to_char(l_SOLD_TO_CONTACT_ID) || '*' ||
1844 to_char(l_SOLD_TO_SITE_USE_ID) || '*' ||
1845 to_char(l_end_customer_id) || '*' ||
1846 to_char(l_end_customer_contact_id) || '*' ||
1847 to_char(l_order_header_id) || '*' ||
1848 to_char(l_order_line_number) || '*' ||
1849 to_char(l_invoice_number) || '*' ||
1850 to_char(l_invoice_line_number) || '*' ||
1851 to_char(l_resale_price_currency_code) || '*' ||
1852 to_char(l_resales_price) || '*' ||
1853 to_char(l_list_price_currency_code) || '*' ||
1854 to_char(l_list_price) || '*' ||
1855 to_char(l_agreement_currency_code) || '*' ||
1856 to_char(l_agreement_price) || '*' ||
1857 to_char('NEW') || '*' || -- status is 'new'
1858
1859 to_char(l_claim_amount) || '*' ||
1860 to_char(l_batch_curr_claim_amount) || '*' ||
1861 to_char(l_item_id) || '*' ||
1862 to_char(l_batch_curr_claim_amount) || '*' ||
1863 to_char(l_shipped_quantity_uom) || '*' ||
1864 to_char(l_quantity_shipped) || '*' ||
1865 to_char(l_claim_amount_currency_code) || '*' ||
1866 to_char(l_acct_amount_remaining) || '*' ||
1867 to_char(l_univ_curr_amount_remaining) || '*' ||
1868 to_char(l_amount_remaining) || '*' ||
1869 to_char('Y') || '*' || to_char(l_order_date) || '*' ||
1870 to_char(sysdate) || '*' || to_char(sysdate) || '*' ||
1871 to_char(FND_GLOBAL.USER_ID) || '*' ||
1872 to_char(FND_GLOBAL.CONC_REQUEST_ID) || '*' ||
1873 to_char(FND_GLOBAL.USER_ID) || '*' ||
1874 to_char(FND_GLOBAL.CONC_LOGIN_ID) || '*' ||
1875 to_char(FND_GLOBAL.PROG_APPL_ID) || '*' ||
1876 to_char(null) || '*' ||
1877 to_char(FND_GLOBAL.CONC_PROGRAM_ID) || '*' ||
1878 to_char(l_org_id) || '*' ||
1879 to_char(l_inv_org_id));
1880 END IF;
1881
1882 IF OZF_DEBUG_LOW_ON THEN
1883 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting INTO ozf_sd_batch_lines_all');
1884 END IF;
1885
1886 INSERT INTO OZF_SD_BATCH_LINES_ALL
1887 (batch_line_id,
1888 object_version_number,
1889 batch_id,
1890 batch_line_number,
1891 utilization_id,
1892 agreement_number,
1893 ship_to_org_id,
1894 ship_to_contact_id,
1895
1896 sold_to_customer_id,
1897 sold_to_contact_id,
1898 sold_to_site_use_id,
1899 end_customer_id,
1900 end_customer_contact_id,
1901
1902 order_header_id,
1903 order_line_id,
1904 invoice_number,
1905 invoice_line_number,
1906 resale_price_currency_code,
1907 resales_price,
1908 list_price_currency_code,
1909 list_price,
1910 agreement_currency_code,
1911 agreement_price,
1912 status_code,
1913
1914 claim_amount,
1915 claim_amount_currency_code,
1916 batch_curr_claim_amount,
1917 item_id,
1918 vendor_item_id,
1919 shipped_quantity_uom,
1920 last_sub_claim_amount,
1921 acctd_amount_remaining,
1922 univ_curr_amount_remaining,
1923 amount_remaining,
1924 quantity_shipped,
1925 purge_flag,
1926 order_date,
1927 creation_date,
1928 last_update_date,
1929 last_updated_by,
1930 request_id,
1931 created_by,
1932
1933 last_update_login,
1934 program_application_id,
1935 program_update_date,
1936 program_id,
1937 org_id,
1938 transmit_flag
1939 )
1940 VALUES
1941 (l_batch_line_id,
1942 1,
1943 l_batch_id,
1944 l_batch_line_number,
1945 l_utilization_id,
1946 l_agreement_number,
1947 l_ship_to_org_id,
1948 l_ship_to_contact_id,
1949
1950 l_sold_to_customer_id,
1951 l_SOLD_TO_CONTACT_ID,
1952 l_SOLD_TO_SITE_USE_ID,
1953 l_end_customer_id,
1954 l_end_customer_contact_id,
1955
1956 l_order_header_id,
1957 l_order_line_number,
1958 l_invoice_number,
1959 l_invoice_line_number,
1960 l_resale_price_currency_code, -- from orders
1961 l_resales_price,
1962 l_list_price_currency_code, --purchase price from sdr
1963 l_list_price,
1964 l_agreement_currency_code, --agreement price from sdr
1965 l_agreement_price,
1966 'NEW',
1967
1968 l_claim_amount, --claim amount from funds accrual
1969 l_claim_amount_currency_code,
1970 l_batch_curr_claim_amount,
1971 l_item_id,
1972 get_vendor_item_id(l_item_id, p_supplier_site_id),
1973
1974 l_shipped_quantity_uom,
1975 null,
1976 l_acct_amount_remaining,
1977 l_univ_curr_amount_remaining,
1978 l_amount_remaining,
1979 l_quantity_shipped,
1980
1981 'N', -- l_active_flag
1982 l_order_date, -- from OE order lines/header
1983 sysdate, --l_creation_date,
1984 sysdate, --l_last_update_date,
1985 FND_GLOBAL.USER_ID, --l_last_updated_by,
1986 FND_GLOBAL.CONC_REQUEST_ID, --l_request_id,
1987 FND_GLOBAL.USER_ID, --l_created_by,
1988 --l_created_from,
1989 FND_GLOBAL.CONC_LOGIN_ID, --l_last_update_login,
1990 FND_GLOBAL.PROG_APPL_ID, --l_program_application_id,
1991 null, --l_program_update_date,
1992 FND_GLOBAL.CONC_PROGRAM_ID, --l_program_id,
1993 l_inv_org_id,
1994 'Y');
1995
1996 IF OZF_DEBUG_LOW_ON THEN
1997 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserted INTO ozf_sd_batch_lines_all');
1998 END IF;
1999
2000 l_batch_line_number := l_batch_line_number + 1;
2001 END IF;
2002
2003 END LOOP;
2004 EXCEPTION
2005 WHEN OTHERS THEN
2006
2007 IF OZF_DEBUG_HIGH_ON THEN
2008 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in CREATE_BATCH_LINES : ' || SQLERRM);
2009 END IF;
2010
2011 RAISE FND_API.g_exc_error;
2012
2013 END Create_Batch_Lines;
2014
2015
2016
2017 -- Start of comments
2018 -- API name : UPDATE_AMOUNTS
2019 -- Type : Private
2020 -- Pre-reqs : None.
2021 -- Function : Updates ozf_funds_utilized_all table setting amount remaining to zero
2022 -- Parameters :
2023 -- IN : p_batch_id IN NUMBER REQUIRED
2024 -- p_batch_threshold IN NUMBER
2025 -- End of comments
2026 PROCEDURE UPDATE_AMOUNTS(p_batch_id IN NUMBER,
2027 p_batch_threshold IN NUMBER) is
2028
2029 l_batch_id NUMBER;
2030 l_batch_sum NUMBER;
2031 l_batch_threshold NUMBER;
2032 BEGIN
2033 l_batch_threshold := p_batch_threshold;
2034 l_batch_id := p_batch_id;
2035
2036 IF OZF_DEBUG_LOW_ON THEN
2037 FND_FILE.PUT_LINE(FND_FILE.LOG,
2038 '--- Start of UPDATE_AMOUNTS ---');
2039 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_batch_id = ' || p_batch_id);
2040 FND_FILE.PUT_LINE(FND_FILE.LOG, 'p_batch_threshold = ' || p_batch_threshold);
2041 END IF;
2042
2043 UPDATE ozf_funds_utilized_all_b
2044 SET amount_remaining = 0,
2045 acctd_amount_remaining = 0,
2046 plan_curr_amount_remaining = 0,
2047 univ_curr_amount_remaining = 0,
2048 last_update_date = sysdate,
2049 last_updated_by = FND_GLOBAL.USER_ID,
2050 object_version_number = object_version_number + 1
2051 WHERE utilization_id in
2052 (SELECT utilization_id
2053 FROM ozf_sd_batch_lines_all
2054 WHERE batch_id = l_batch_id);
2055 EXCEPTION
2056 WHEN OTHERS THEN
2057 IF OZF_DEBUG_HIGH_ON THEN
2058 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in UPDATE_AMOUNTS:' || SQLERRM);
2059 END IF;
2060
2061 RAISE FND_API.g_exc_error;
2062
2063 END UPDATE_AMOUNTS;
2064
2065
2066
2067 -- Start of comments
2068 -- API name : INVOKE_BATCH_AUTO_CLAIM
2069 -- Type : Private
2070 -- Pre-reqs : None.
2071 -- Function : Executable target for concurrent program
2072 -- Executable Name "OZFSDBACEX"
2073 -- Parameters :
2074 -- IN : p_batch_id IN NUMBER
2075 -- p_vendor_id IN NUMBER
2076 -- p_vendor_site_id IN NUMBER
2077 -- End of comments
2078 PROCEDURE INVOKE_BATCH_AUTO_CLAIM(errbuf OUT nocopy VARCHAR2,
2079 retcode OUT nocopy NUMBER,
2080 p_batch_id NUMBER,
2081 p_vendor_id NUMBER,
2082 p_vendor_site_id NUMBER) is
2083
2084 CURSOR get_freq_and_date(c_supplier_site_id NUMBER) IS
2085 SELECT days_before_claiming_debit
2086 FROM ozf_supp_trd_prfls_all
2087 WHERE supplier_site_id = c_supplier_site_id;
2088
2089 l_claim_id NUMBER := NULL; -- Incase auto claim is run
2090 l_claim_ret_status VARCHAR2(15) := NULL;
2091 l_claim_msg_count NUMBER := NULL;
2092 l_claim_msg_data VARCHAR2(500) := NULL;
2093 l_claim_type VARCHAR2(20) := 'SUPPLIER'; --always defaulted to external claim
2094 l_batch_id NUMBER;
2095 l_duration NUMBER;
2096 l_freq NUMBER;
2097 l_freq_unit VARCHAR2(40);
2098 l_sql VARCHAR2(2000) := NULL;
2099 l_supplier_site_id NUMBER := null;
2100 v_batch_header c_batch_header;
2101 l_last_run_date DATE;
2102 l_batch_submission_date DATE;
2103 l_return_status VARCHAR2(15) := NULL;
2104 BEGIN
2105
2106 IF OZF_DEBUG_LOW_ON THEN
2107 FND_FILE.PUT_LINE(FND_FILE.LOG, '--- Start INVOKE_BATCH_AUTO_CLAIM ---');
2108 END IF;
2109
2110 l_sql := 'SELECT HDR.BATCH_ID, HDR.vendor_site_id, HDR.BATCH_SUBMISSION_DATE '
2111 || ' FROM ozf_sd_batch_headers_all HDR, ozf_sd_batch_lines_all BLN '
2112 || ' WHERE HDR.batch_id = BLN.batch_id'
2113 || ' AND HDR.status_code = ''SUBMITTED'' ';
2114
2115
2116 IF p_vendor_site_id IS NOT NULL THEN
2117 l_sql := l_sql || ' AND HDR.vendor_site_id =' || p_vendor_site_id;
2118
2119 END IF;
2120
2121 IF p_batch_id IS NOT NULL THEN
2122 l_sql := l_sql || ' AND HDR.batch_id =' || p_batch_id;
2123
2124 END IF;
2125
2126 IF p_vendor_id IS NOT NULL THEN
2127 l_sql := l_sql || ' AND HDR.vendor_id =' || p_vendor_id;
2128
2129 END IF;
2130
2131 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 ' ;
2132
2133 OPEN v_batch_header for l_sql;
2134 LOOP
2135 FETCH v_batch_header
2136 INTO l_batch_id, l_supplier_site_id, l_batch_submission_date;
2137 EXIT WHEN v_batch_header%notfound;
2138
2139 BEGIN
2140 OPEN get_freq_and_date(l_supplier_site_id);
2141 FETCH get_freq_and_date
2142 INTO l_freq;
2143 CLOSE get_freq_and_date;
2144
2145 IF OZF_DEBUG_LOW_ON THEN
2146 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch ID ' || to_char(l_batch_id));
2147 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Batch Submission Date ' || to_char(l_batch_submission_date));
2148 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Trade Profile Frequency = ' || to_char(l_freq));
2149 END IF;
2150
2151 l_batch_submission_date := l_batch_submission_date + l_freq;
2152
2153 IF NVL(l_batch_submission_date, sysdate + 1) < sysdate THEN
2154
2155 IF OZF_DEBUG_HIGH_ON THEN
2156 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invoking Claim for Batch ID ' || to_char(l_batch_id));
2157 END IF;
2158
2159 --Code added for Bug#6971836
2160 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Initiates claim for batch '||to_char(l_batch_id));
2161
2162 OZF_CLAIM_ACCRUAL_PVT.Initiate_SD_Payment(1,
2163 FND_API.g_false,
2164 FND_API.g_true,
2165 FND_API.g_valid_level_full,
2166 l_claim_ret_status,
2167 l_claim_msg_count,
2168 l_claim_msg_data,
2169 l_batch_id,
2170 l_claim_type,
2171 l_claim_id);
2172
2173
2174 IF OZF_DEBUG_HIGH_ON THEN
2175 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invoked Claim ....' );
2176 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Batch ID ' || to_char(l_batch_id));
2177 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Claim ID ' || to_char(l_claim_id) );
2178 END IF;
2179
2180 IF OZF_ERROR_ON THEN
2181 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_claim_ret_status ' || l_claim_ret_status );
2182 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_claim_msg_count ' || l_claim_msg_count );
2183 FND_FILE.PUT_LINE(FND_FILE.LOG, ' l_claim_msg_data ' || l_claim_msg_data );
2184 FOR I IN 1..l_claim_msg_count LOOP
2185 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) );
2186 END LOOP;
2187 END IF;
2188
2189 IF l_claim_ret_status = FND_API.G_RET_STS_SUCCESS THEN
2190
2191 --Code added for Bug#6971836
2192 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Claim created for batch.');
2193
2194 UPDATE OZF_SD_BATCH_HEADERS_ALL
2195 SET status_code = 'CLOSED',
2196 claim_id = l_claim_id,
2197 last_update_date = sysdate,
2198 last_updated_by = FND_GLOBAL.USER_ID,
2199 object_version_number = object_version_number + 1
2200 WHERE batch_id = l_batch_id;
2201
2202 OZF_SD_UTIL_PVT.SD_RAISE_EVENT(l_batch_id, 'CLAIM', l_return_status); -- Raising lifecycle event for claim
2203 IF l_return_status = FND_API.G_RET_STS_ERROR THEN
2204 RAISE FND_API.g_exc_error;
2205 END IF;
2206
2207
2208
2209 COMMIT;
2210
2211 END IF;
2212 ELSE
2213 --Code added for Bug#6971836
2214 FND_FILE.PUT_LINE(FND_FILE.OUTPUT, 'Claim process failed.');
2215 END IF;
2216
2217 EXCEPTION
2218 WHEN OTHERS THEN
2219 IF OZF_DEBUG_HIGH_ON THEN
2220 FND_FILE.PUT_LINE(FND_FILE.LOG,
2221 'Exception occured in INVOKE_BATCH_AUTO_CLAIM :=' ||
2222 SQLERRM);
2223 errbuf := 'Exception occured in INVOKE_BATCH_AUTO_CLAIM ' ||
2224 SQLERRM;
2225 retcode := 2;
2226 END IF;
2227 END;
2228 END LOOP;
2229
2230 CLOSE v_batch_header;
2231
2232 END INVOKE_BATCH_AUTO_CLAIM;
2233
2234
2235 END OZF_SD_BATCH_PVT;