[Home] [Help]
PACKAGE BODY: APPS.OZF_NET_ACCRUAL_ENGINE_PVT
Source
1 PACKAGE BODY ozf_net_accrual_engine_pvt AS
2 /* $Header: ozfvnaeb.pls 120.24.12020000.16 2013/03/21 09:50:28 nepanda ship $ */
3
4 G_DEBUG_LOW BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_low);
5 TYPE number_tbl_type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
6
7 FUNCTION validate_customer( p_invoice_to_org_id IN NUMBER,
8 p_ship_to_org_id IN NUMBER,
9 p_sold_to_org_id IN NUMBER)
10 RETURN VARCHAR2
11 IS
12
13 -- Segment and buying group has no acct info. use party_id for validation
14 CURSOR c_party_id IS
15 SELECT party_id
16 FROM hz_cust_accounts
17 WHERE cust_account_id = p_sold_to_org_id;
18
19 CURSOR c_customer_qualified(p_party_id NUMBER) IS
20 SELECT 'Y'
21 FROM ozf_na_customers_temp
22 WHERE (
23 (site_use_id = p_invoice_to_org_id AND site_use_code = 'BILL_TO') OR
24 (site_use_id = p_ship_to_org_id AND site_use_code = 'SHIP_TO') OR
25 (party_id = p_party_id AND site_use_code IS NULL) OR
26 (party_id = -1)
27 )
28 AND ROWNUM = 1;
29
30 CURSOR c_cust_acct_qualified(p_party_id NUMBER) IS
31 SELECT 'Y'
32 FROM ozf_na_customers_temp
33 WHERE (
34 (cust_account_id = p_sold_to_org_id) OR
35 (party_id = p_party_id AND site_use_code IS NULL) OR
36 (party_id = -1)
37 )
38 AND ROWNUM = 1;
39
40 l_customer_qualified VARCHAR2(1) := 'N';
41 l_party_id NUMBER;
42
43 BEGIN
44
45 OPEN c_party_id;
46 FETCH c_party_id INTO l_party_id;
47 CLOSE c_party_id;
48
49 IF p_invoice_to_org_id IS NULL AND p_ship_to_org_id IS NULL
50 THEN
51 --
52 OPEN c_cust_acct_qualified(l_party_id);
53 FETCH c_cust_acct_qualified INTO l_customer_qualified;
54 CLOSE c_cust_acct_qualified;
55 --
56 ELSE
57 --
58 OPEN c_customer_qualified(l_party_id);
59 FETCH c_customer_qualified INTO l_customer_qualified;
60 CLOSE c_customer_qualified;
61 --
62 END IF;
63
64 RETURN l_customer_qualified;
65
66 END validate_customer;
67
68
69 -- Used for retrocative Offer Adjustment
70
71 FUNCTION validate_customer( p_invoice_to_org_id IN NUMBER,
72 p_ship_to_org_id IN NUMBER,
73 p_sold_to_org_id IN NUMBER,
74 p_qp_list_header_id IN NUMBER)
75 RETURN VARCHAR2
76 IS
77 CURSOR c_party_id IS -- segment and buying group has no acct info. use party_id for validation
78 SELECT party_id
79 FROM hz_cust_accounts
80 WHERE cust_account_id = p_sold_to_org_id;
81
82 CURSOR c_customer_qualified(p_party_id NUMBER) IS
83 SELECT 'Y'
84 FROM ozf_activity_customers
85 WHERE (
86 (site_use_id = p_invoice_to_org_id AND site_use_code = 'BILL_TO') OR
87 (site_use_id = p_ship_to_org_id AND site_use_code = 'SHIP_TO') OR
88 (party_id = p_party_id AND site_use_code IS NULL) OR
89 (party_id = -1)
90 )
91 AND object_class = 'OFFR'
92 AND object_id = p_qp_list_header_id
93 AND ROWNUM = 1;
94
95 CURSOR c_cust_acct_qualified(p_party_id NUMBER) IS
96 SELECT 'Y'
97 FROM ozf_activity_customers
98 WHERE (
99 (cust_account_id = p_sold_to_org_id) OR
100 (party_id = p_party_id AND site_use_code IS NULL) OR
101 (party_id = -1)
102 )
103 AND object_class = 'OFFR'
104 AND object_id = p_qp_list_header_id
105 AND ROWNUM = 1;
106
107 l_customer_qualified VARCHAR2(1) := 'N';
108 l_party_id NUMBER;
109 BEGIN
110 --
111 OPEN c_party_id;
112 FETCH c_party_id INTO l_party_id;
113 CLOSE c_party_id;
114
115 IF p_invoice_to_org_id IS NULL AND p_ship_to_org_id IS NULL
116 THEN
117 --
118 OPEN c_cust_acct_qualified(l_party_id);
119 FETCH c_cust_acct_qualified INTO l_customer_qualified;
120 CLOSE c_cust_acct_qualified;
121 --
122 ELSE
123 --
124 OPEN c_customer_qualified(l_party_id);
125 FETCH c_customer_qualified INTO l_customer_qualified;
126 CLOSE c_customer_qualified;
127 --
128 END IF;
129
130 IF l_customer_qualified = 'Y' THEN
131 RETURN 'Y';
132 ELSE
133 RETURN 'N';
134 END IF;
135 END validate_customer;
136
137
138 FUNCTION validate_prm_customer( p_offer_id IN NUMBER,
139 p_country_code IN VARCHAR2
140 )
141 RETURN VARCHAR2
142 IS
143
144 -- Partner Referral Net Accrual Offers
145 -- Will always have Territory as a qualifier
146 CURSOR c_terr_id IS
147 SELECT qualifier_attr_value terr_id
148 FROM ozf_offer_qualifiers
149 WHERE offer_id = p_offer_id;
150
151 CURSOR c_terr_qual_id(p_terr_id NUMBER) IS
152 SELECT terr_qual_id
153 FROM jtf_terr_qual_all
154 WHERE terr_id = p_terr_id;
155
156 CURSOR c_country_count(p_terr_qual_id NUMBER) IS
157 SELECT COUNT(1)
158 FROM jtf_terr_values_all
159 WHERE low_value_char = p_country_code
160 AND terr_qual_id = p_terr_qual_id;
161
162 l_customer_qualified VARCHAR2(1);
163 l_country_count NUMBER;
164
165 BEGIN
166
167 FOR l_terr_id IN c_terr_id
168 LOOP
169 --
170 l_customer_qualified := 'Y';
171
172 FOR l_terr_qual_id IN c_terr_qual_id(l_terr_id.terr_id) LOOP
173 l_country_count := 0;
174
175 OPEN c_country_count(l_terr_qual_id.terr_qual_id);
176 FETCH c_country_count INTO l_country_count;
177 CLOSE c_country_count;
178
179 IF l_country_count = 0 THEN
180 l_customer_qualified := 'N';
181 EXIT;
182 END IF;
183 END LOOP;
184
185 IF l_customer_qualified = 'Y' THEN
186 EXIT;
187 END IF;
188 END LOOP;
189
190 RETURN l_customer_qualified;
191 END validate_prm_customer;
192
193 -- Called from Offer Product Backdated Adjustment
194
195 FUNCTION validate_product( p_inventory_item_id IN NUMBER,
196 p_qp_list_header_id IN NUMBER)
197 RETURN VARCHAR2
198 IS
199 CURSOR c_product_qualified IS
200 SELECT 'Y'
201 FROM DUAL
202 WHERE EXISTS(SELECT 1
203 FROM ozf_activity_products
204 WHERE item = p_inventory_item_id
205 AND item_type = 'PRICING_ATTRIBUTE1'
206 AND object_class = 'OFFR'
207 AND object_id = p_qp_list_header_id);
208
209 l_product_qualified VARCHAR2(1);
210
211 BEGIN
212 --
213 OPEN c_product_qualified;
214 FETCH c_product_qualified INTO l_product_qualified;
215 CLOSE c_product_qualified;
216
217 IF l_product_qualified = 'Y' THEN
218 RETURN 'Y';
219 ELSE
220 RETURN 'N';
221 END IF;
222 --
223 END validate_product;
224
225
226 PROCEDURE refresh_parties( p_offer_id IN NUMBER,
227 p_calling_from_den IN VARCHAR2,
228 x_return_status OUT NOCOPY VARCHAR2,
229 x_msg_count OUT NOCOPY NUMBER,
230 x_msg_data OUT NOCOPY VARCHAR2,
231 x_party_stmt OUT NOCOPY VARCHAR2
232 )
233 IS
234
235 CURSOR c_no_groups IS
236 SELECT COUNT(*)
237 FROM ozf_offer_qualifiers
238 WHERE offer_id = p_offer_id
239 AND active_flag = 'Y';
240
241 CURSOR c_groups IS
242 SELECT qualifier_id
243 FROM ozf_offer_qualifiers
244 WHERE offer_id = p_offer_id
245 AND active_flag = 'Y';
246
247 CURSOR c_qualifiers(p_qualifier_id NUMBER) IS
248 SELECT NVL(qualifier_context,
249 DECODE(qualifier_attribute,
250 'BUYER', 'CUSTOMER_GROUP',
251 'CUSTOMER_BILL_TO', 'CUSTOMER',
252 'CUSTOMER', 'CUSTOMER',
253 'LIST', 'CUSTOMER_GROUP',
254 'SEGMENT', 'CUSTOMER_GROUP',
255 'TERRITORY', 'TERRITORY',
256 'SHIP_TO', 'CUSTOMER')) qualifier_context,
257 DECODE(qualifier_attribute,
258 'BUYER', 'QUALIFIER_ATTRIBUTE3',
259 'CUSTOMER_BILL_TO', 'QUALIFIER_ATTRIBUTE14',
260 'CUSTOMER', 'QUALIFIER_ATTRIBUTE2',
261 'LIST', 'QUALIFIER_ATTRIBUTE1',
262 'SEGMENT', 'QUALIFIER_ATTRIBUTE2',
263 'TERRITORY', 'QUALIFIER_ATTRIBUTE1',
264 'SHIP_TO', 'QUALIFIER_ATTRIBUTE11',
265 qualifier_attribute) qualifier_attribute,
266 qualifier_attr_value,
267 '=' comparison_operator_code
268 FROM ozf_offer_qualifiers
269 WHERE qualifier_id = p_qualifier_id;
270
271 l_api_name CONSTANT VARCHAR2(30) := 'refresh_parties';
272 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
273
274 l_stmt_temp VARCHAR2(32000) := NULL;
275 l_no_query_flag VARCHAR2(1) := 'N';
276 l_no_groups NUMBER;
277 l_no_lines NUMBER;
278 l_group_index NUMBER;
279 l_line_index NUMBER;
280
281 BEGIN
282
283 x_return_status := FND_API.g_ret_sts_success;
284
285 OPEN c_no_groups;
286 FETCH c_no_groups INTO l_no_groups;
287 CLOSE c_no_groups;
288
289 IF G_DEBUG_LOW
290 THEN
291 ozf_utility_pvt.write_conc_log('Number of Market Eligibilites: '||l_no_groups);
292 END IF;
293
294 IF l_no_groups > 0
295 THEN
296 --
297 l_group_index := 1;
298
299 FOR i IN c_groups
300 LOOP
301 --
302 l_line_index := 1;
303 -- Currently NA qualifiers does not support grouping, each group has only 1 line
304 l_no_lines := 1;
305 --
306 FND_DSQL.add_text('(');
307 --
308 FOR j IN c_qualifiers(i.qualifier_id)
309 LOOP
310 --
311 l_stmt_temp := NULL;
312 l_stmt_temp := ozf_offr_elig_prod_denorm_pvt.get_sql(p_context => j.qualifier_context,
313 p_attribute => j.qualifier_attribute,
314 p_attr_value_from => j.qualifier_attr_value,
315 p_attr_value_to => NULL,--j.qualifier_attr_value_to,
316 p_comparison => j.comparison_operator_code,
317 p_type => 'ELIG');
318 IF l_stmt_temp IS NULL
319 THEN
320 --
321 l_no_query_flag := 'Y';
322 EXIT;
323 --
324 ELSE
325 --
326 IF l_line_index < l_no_lines
327 THEN
328 --
329 FND_DSQL.add_text(' INTERSECT ');
330 l_line_index := l_line_index + 1;
331 --
332 END IF;
333 --
334 END IF;
335 --
336 END LOOP; -- c_qualifiers
337 --
338 FND_DSQL.add_text(')');
339 --
340 IF l_group_index < l_no_groups
341 THEN
342 --
343 FND_DSQL.add_text(' UNION ');
344 l_group_index := l_group_index + 1;
345 --
346 END IF;
347 --
348 END LOOP; -- c_groups
349 --
350 ELSE
351 --
352 -- FND_DSQL.add_text('(SELECT -1 party_id,-1 cust_account_id, -1 cust_acct_site_id, -1 site_use_id,'' '' site_use_code FROM DUAL)');
353 FND_DSQL.add_text('(SELECT -1 qp_qualifier_id, -1 qp_qualifier_group, -1 party_id,-1 cust_account_id, -1 cust_acct_site_id, -1 site_use_id,'' '' site_use_code FROM DUAL)');
354 --
355 END IF;
356
357 IF p_calling_from_den = 'N' OR l_no_query_flag = 'N'
358 THEN
359 --
360 x_party_stmt := FND_DSQL.get_text(FALSE);
361 --
362 ELSE
363 --
364 x_party_stmt := NULL;
365 --
366 END IF;
367
368 IF G_DEBUG_LOW
369 THEN
370 --
371 ozf_utility_pvt.write_conc_log('1:'||substr(x_party_stmt,945,250));
372 ozf_utility_pvt.write_conc_log('2:'||substr(x_party_stmt,1195,250));
373 ozf_utility_pvt.write_conc_log('3:'||substr(x_party_stmt,1445,250));
374 --
375 END IF;
376
377 EXCEPTION
378 WHEN OTHERS THEN
379 x_return_status := FND_API.g_ret_sts_unexp_error;
380
381 FND_MESSAGE.set_name('OZF', 'OZF_OFFER_PARTY_STMT_FAILED');
382 FND_MESSAGE.set_token('ERR_MSG',SQLERRM);
383 FND_MSG_PUB.add;
384
385 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
386 THEN
387 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
388 END IF;
389
390 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
391 p_count => x_msg_count,
392 p_data => x_msg_data);
393 END refresh_parties;
394
395
396 PROCEDURE populate_customers( p_offer_id IN NUMBER
397 ,x_return_status OUT NOCOPY VARCHAR2
398 ,x_msg_count OUT NOCOPY NUMBER
399 ,x_msg_data OUT NOCOPY VARCHAR2)
400 IS
401 --
402 l_api_name CONSTANT VARCHAR2(30) := 'populate_customers';
403 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name ||'.'|| l_api_name;
404
405 l_stmt_denorm VARCHAR2(32000) := NULL;
406 l_stmt_offer VARCHAR2(32000) := NULL;
407 l_stmt_debug VARCHAR2(32000) := NULL;
408 l_denorm_csr NUMBER;
409 l_ignore NUMBER;
410 --
411 BEGIN
412 --
413 x_return_status := FND_API.g_ret_sts_success;
414
415 -- denorm parties
416 FND_DSQL.init;
417 FND_DSQL.add_text('INSERT INTO ozf_na_customers_temp(');
418 FND_DSQL.add_text('creation_date,created_by,last_update_date,last_updated_by,');
419 FND_DSQL.add_text('last_update_login,confidential_flag,');
420 FND_DSQL.add_text('object_id,object_type,object_status,object_class,object_desc,parent_id,parent_class,');
421 FND_DSQL.add_text('parent_desc,ask_for_flag,active_flag,source_code,marketing_medium_id,start_date,end_date,');
422 FND_DSQL.add_text('party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code,');
423 FND_DSQL.add_text('qualifier_attribute,qualifier_context) ');
424 FND_DSQL.add_text('SELECT SYSDATE,FND_GLOBAL.user_id,SYSDATE,');
425 FND_DSQL.add_text('FND_GLOBAL.user_id,FND_GLOBAL.conc_login_id,NULL,');
426 FND_DSQL.add_bind(p_offer_id);
427 FND_DSQL.add_text(',NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL');
428 FND_DSQL.add_text(',party_id,cust_account_id,cust_acct_site_id,site_use_id,site_use_code, ');
429 FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''QUALIFIER_ATTRIBUTE14'',''SHIP_TO'',''QUALIFIER_ATTRIBUTE11'',substr(site_use_code,INSTR(site_use_code,'':'')+1)) qualifier_attribute,');
430 FND_DSQL.add_text(' decode(site_use_code,''BILL_TO'',''CUSTOMER'',''SHIP_TO'',''CUSTOMER'',substr(site_use_code,0,INSTR(site_use_code,'':'')-1)) qualifier_context');
431 FND_DSQL.add_text(' FROM (');
432
433
434 ozf_utility_pvt.write_conc_log('-- Refresh_Parties (+)');
435
436 /* refresh parties would get all the parties for the offer_id and add to FND_DSQL*/
437 refresh_parties(p_offer_id => p_offer_id,
438 p_calling_from_den => 'Y',
439 x_return_status => x_return_status,
440 x_msg_count => x_msg_count,
441 x_msg_data => x_msg_data,
442 x_party_stmt => l_stmt_offer);
443
444 ozf_utility_pvt.write_conc_log('-- Refresh_Parties (-) With Status: ' || x_return_status );
445
446 IF x_return_status = FND_API.g_ret_sts_unexp_error
447 THEN
448 RAISE FND_API.g_exc_unexpected_error;
449 END IF;
450
451 IF l_stmt_offer IS NOT NULL
452 THEN
453 --
454 -- FND_DSQL.add_text(' UNION select -1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
455 FND_DSQL.add_text(' UNION select -1 qp_qualifier_id, -1 qp_qualifier_group, -1 party_id, -1 cust_account_id, -1 cust_acct_site_id, ');
456 FND_DSQL.add_text(' to_number(qualifier_attr_value) site_use_id, ');
457 FND_DSQL.add_text(' qualifier_context||'':''||qualifier_attribute site_use_code ');
458 FND_DSQL.add_text(' FROM ozf_offer_qualifiers WHERE offer_id = ');
459 FND_DSQL.add_bind(p_offer_id);
460 FND_DSQL.add_text(' and qualifier_context||'':''||qualifier_attribute not in ');
461 FND_DSQL.add_text(' (''CUSTOMER:PRICING_ATTRIBUTE11'',''CUSTOMER:QUALIFIER_ATTRIBUTE14'')');
462 FND_DSQL.add_text(' and qualifier_context not in (''MODLIST'') ');
463 FND_DSQL.add_text(' and qualifier_attribute < ''A'' ');
464 FND_DSQL.add_text(')');
465
466 l_denorm_csr := DBMS_SQL.open_cursor;
467 FND_DSQL.set_cursor(l_denorm_csr);
468 l_stmt_debug := FND_DSQL.get_text(TRUE);
469 l_stmt_denorm := FND_DSQL.get_text(FALSE);
470 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
471 FND_DSQL.do_binds;
472 l_ignore := DBMS_SQL.execute(l_denorm_csr);
473 dbms_sql.close_cursor(l_denorm_csr);
474 --
475 END IF;
476
477 EXCEPTION
478 WHEN OTHERS THEN
479 x_return_status := FND_API.g_ret_sts_unexp_error;
480
481 ozf_utility_pvt.write_conc_log(l_stmt_debug);
482 ozf_utility_pvt.write_conc_log(SQLERRM);
483
484 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
485 THEN
486 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
487 END IF;
488
489 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
490 p_count => x_msg_count,
491 p_data => x_msg_data);
492 END populate_customers;
493
494
495 FUNCTION get_func_area(p_category_id IN NUMBER) RETURN NUMBER
496 IS
497 --
498 CURSOR c_func_area IS
499 SELECT a.functional_area_id
500 FROM mtl_default_category_sets a,
501 mtl_category_sets_b b,
502 mtl_categories c
503 WHERE a.functional_area_id in (7,11)
504 AND a.category_set_id = b.category_set_id
505 AND c.structure_id = b.structure_id
506 AND c.category_id = p_category_id;
507 --
508 l_func_area_id NUMBER;
509 BEGIN
510
511 OPEN c_func_area;
512 FETCH c_func_area INTO l_func_area_id;
513 CLOSE c_func_area;
514
515 RETURN l_func_area_id;
516 END;
517
518 PROCEDURE populate_prod_line( p_offer_id IN NUMBER
519 ,x_return_status OUT NOCOPY VARCHAR2
520 ,x_msg_count OUT NOCOPY NUMBER
521 ,x_msg_data OUT NOCOPY VARCHAR2)
522 IS
523 --
524 CURSOR c_product IS
525 SELECT product_id,
526 product_level,
527 off_discount_product_id,
528 offer_discount_line_id,
529 NVL(uom_code, 'NA') uom_code
530 FROM ozf_offer_discount_products
531 WHERE excluder_flag = 'N'
532 AND offer_id = p_offer_id;
533
534 CURSOR c_exclusion(p_off_discount_product_id NUMBER) IS
535 SELECT product_level,
536 product_id
537 FROM ozf_offer_discount_products
538 WHERE parent_off_disc_prod_id = p_off_discount_product_id
539 AND excluder_flag = 'Y';
540
541 CURSOR c_discount(p_offer_discount_line_id NUMBER) IS
542 SELECT discount,
543 discount_type,
544 NVL(volume_from,0),
545 volume_to,
546 DECODE(volume_type, 'PRICING_ATTRIBUTE12', 'AMT', 'PRICING_ATTRIBUTE10', 'QTY', NULL, 'NA')
547 FROM ozf_offer_discount_lines
548 WHERE offer_discount_line_id = p_offer_discount_line_id;
549
550
551 l_api_name CONSTANT VARCHAR2(30) := 'populate_prod_line';
552 l_discount NUMBER;
553 l_discount_type VARCHAR2(30);
554 l_volume_from NUMBER;
555 l_volume_to NUMBER;
556 l_volume_type VARCHAR2(30);
557 l_org_id NUMBER;
558 l_denorm_csr NUMBER;
559 l_ignore NUMBER;
560 l_func_area_id NUMBER;
561 l_stmt_denorm VARCHAR2(32000) := NULL;
562 l_stmt_debug VARCHAR2(32000) := NULL;
563 l_excl_func_area_id NUMBER;
564
565 BEGIN
566 x_return_status := FND_API.g_ret_sts_success;
567
568 l_org_id := fnd_profile.value('QP_ORGANIZATION_ID');
569
570 FOR l_product IN c_product
571 LOOP
572 --
573 OPEN c_discount(l_product.offer_discount_line_id);
574 FETCH c_discount INTO l_discount,
575 l_discount_type,
576 l_volume_from,
577 l_volume_to,
578 l_volume_type;
579 CLOSE c_discount;
580
581 IF l_product.product_level = 'FAMILY'
582 THEN
583 --
584 l_func_area_id := get_func_area(l_product.product_id);
585
586 IF G_DEBUG_LOW THEN
587 --
588 ozf_utility_pvt.write_conc_log('Functional Area for category: ' || l_func_area_id);
589 ozf_utility_pvt.write_conc_log('Off_Discount_Product_Id:' || l_product.off_discount_product_id);
590 --
591 END IF;
592 --
593 END IF;
594
595 FND_DSQL.init;
596 FND_DSQL.add_text('INSERT INTO ozf_na_products_temp(inventory_item_id,product_level,discount,discount_type,volume_from,volume_to,volume_type,uom) ');
597 FND_DSQL.add_text('SELECT inventory_item_id,');
598 FND_DSQL.add_bind(l_product.product_level);
599 FND_DSQL.add_text(',');
600 FND_DSQL.add_bind(l_discount);
601 FND_DSQL.add_text(',');
602 FND_DSQL.add_bind(l_discount_type);
603 FND_DSQL.add_text(',');
604 FND_DSQL.add_bind(l_volume_from);
605 FND_DSQL.add_text(',');
606 FND_DSQL.add_bind(l_volume_to);
607 FND_DSQL.add_text(',');
608 FND_DSQL.add_bind(l_volume_type);
609 FND_DSQL.add_text(',');
610 FND_DSQL.add_bind(l_product.uom_code);
611 FND_DSQL.add_text(' FROM (');
612
613 IF l_product.product_level = 'FAMILY'
614 THEN
615 --
616 IF l_func_area_id = 11
617 THEN
618 -- Functional Area is PRFA.
619 --
620 FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
621 FND_DSQL.add_bind(l_org_id);
622 FND_DSQL.add_text(' AND epdhv.parent_id = ');
623 FND_DSQL.add_bind(l_product.product_id);
624 --
625 ELSE
626 -- Functional Area id OMFA
627 FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
628 FND_DSQL.add_bind(l_org_id);
629 FND_DSQL.add_text(' AND category_id = ');
630 FND_DSQL.add_bind(l_product.product_id);
631 --
632 END IF;
633 --
634 ELSIF l_product.product_level = 'PRODUCT'
635 THEN
636 --
637 FND_DSQL.add_text('SELECT ');
638 FND_DSQL.add_bind(l_product.product_id);
639 FND_DSQL.add_text(' inventory_item_id FROM DUAL');
640 --
641 END IF;
642
643 FOR l_exclusion IN c_exclusion(l_product.off_discount_product_id)
644 LOOP
645 --
646 ozf_utility_pvt.write_conc_log('Exclusion Loop Start');
647 ozf_utility_pvt.write_conc_log('l_exclusion.product_level: ' || l_exclusion.product_level);
648 ozf_utility_pvt.write_conc_log('l_exclusion.product_id: ' || l_exclusion.product_id);
649
650 --kdass added for bug 13510229 - get functional area of the excluded product for the excluded product query
651 IF l_product.product_level = 'FAMILY' THEN
652 --
653 l_excl_func_area_id := get_func_area(l_exclusion.product_id);
654
655 ozf_utility_pvt.write_conc_log('l_excl_func_area_id: ' || l_excl_func_area_id);
656 ozf_utility_pvt.write_conc_log('l_func_area_id2: ' || l_func_area_id);
657 --
658 END IF;
659
660 FND_DSQL.add_text(' MINUS ');
661
662 IF l_exclusion.product_level = 'PRODUCT'
663 THEN
664 --
665 FND_DSQL.add_text('SELECT ');
666 FND_DSQL.add_bind(l_exclusion.product_id);
667 FND_DSQL.add_text(' inventory_item_id FROM DUAL');
668 --
669 ELSIF l_exclusion.product_level = 'FAMILY'
670 THEN
671 --
672
673 --IF l_func_area_id = 11 --kdass bug 13510229 - get functional area of the excluded product for the excluded product query
674 IF l_excl_func_area_id = 11
675 THEN
676 -- Functional Area is PRFA.
677 --
678 FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
679 FND_DSQL.add_bind(l_org_id);
680 FND_DSQL.add_text(' AND epdhv.parent_id = ');
681 FND_DSQL.add_bind(l_exclusion.product_id);
682 --
683 ozf_utility_pvt.write_conc_log('l_excl_func_area_id1: ' || l_excl_func_area_id);
684 ELSE
685 -- Functional Area id OMFA
686 FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
687 FND_DSQL.add_bind(l_org_id);
688 FND_DSQL.add_text(' AND category_id = ');
689 FND_DSQL.add_bind(l_exclusion.product_id);
690 --
691 ozf_utility_pvt.write_conc_log('l_excl_func_area_id2: ' || l_excl_func_area_id);
692 END IF;
693 --
694 END IF;
695 --
696 END LOOP;
697
698 FND_DSQL.add_text(')');
699
700 l_denorm_csr := DBMS_SQL.open_cursor;
701 FND_DSQL.set_cursor(l_denorm_csr);
702 l_stmt_debug := FND_DSQL.get_text(TRUE);
703 l_stmt_denorm := FND_DSQL.get_text(FALSE);
704 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
705 FND_DSQL.do_binds;
706 l_ignore := DBMS_SQL.execute(l_denorm_csr);
707 dbms_sql.close_cursor(l_denorm_csr);
708 --
709 END LOOP;
710
711 EXCEPTION
712 WHEN OTHERS THEN
713 x_return_status := FND_API.g_ret_sts_unexp_error;
714
715 ozf_utility_pvt.write_conc_log(l_stmt_debug);
716 ozf_utility_pvt.write_conc_log(SQLERRM);
717
718 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
719 THEN
720 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
721 END IF;
722
723 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
724 p_count => x_msg_count,
725 p_data => x_msg_data);
726 END populate_prod_line;
727
728
729 PROCEDURE populate_prod_tier( p_offer_id IN NUMBER
730 ,x_return_status OUT NOCOPY VARCHAR2
731 ,x_msg_count OUT NOCOPY NUMBER
732 ,x_msg_data OUT NOCOPY VARCHAR2)
733 IS
734 --
735 CURSOR c_product IS
736 SELECT product_id,
737 product_level,
738 off_discount_product_id,
739 NVL(uom_code, 'NA') uom_code
740 FROM ozf_offer_discount_products
741 WHERE excluder_flag = 'N'
742 AND offer_id = p_offer_id;
743
744 --amitamku - Added for Bug fix 13510229
745 CURSOR c_exclusion(p_off_discount_product_id NUMBER) IS
746 SELECT product_level,
747 product_id
748 FROM ozf_offer_discount_products
749 WHERE parent_off_disc_prod_id = p_off_discount_product_id
750 AND excluder_flag = 'Y';
751 ----amitamku - End of - Added for Bug fix 13510229
752
753 CURSOR c_discount IS
754 SELECT discount,
755 discount_type,
756 NVL(volume_from,0) volume_from,
757 volume_to,
758 DECODE(volume_type, 'PRICING_ATTRIBUTE12', 'AMT', 'PRICING_ATTRIBUTE10', 'QTY', NULL, 'NA') volume_type
759 FROM ozf_offer_discount_lines
760 WHERE offer_id = p_offer_id;
761
762 l_api_name CONSTANT VARCHAR2(30) := 'populate_prod_tier';
763 l_discount NUMBER;
764 l_discount_type VARCHAR2(30);
765 l_volume_from NUMBER;
766 l_volume_to NUMBER;
767 l_volume_type VARCHAR2(30);
768 l_org_id NUMBER;
769 l_denorm_csr NUMBER;
770 l_ignore NUMBER;
771 l_func_area_id NUMBER;
772 l_stmt_denorm VARCHAR2(32000) := NULL;
773 l_stmt_debug VARCHAR2(32000) := NULL;
774
775 l_excl_func_area_id NUMBER;
776
777 BEGIN
778 x_return_status := FND_API.g_ret_sts_success;
779
780 l_org_id := fnd_profile.value('QP_ORGANIZATION_ID');
781 ozf_utility_pvt.write_conc_log(l_api_name);
782
783 ozf_utility_pvt.write_conc_log('l_org_id: ' || l_org_id);
784
785 FOR l_product IN c_product
786 LOOP
787 --
788 ozf_utility_pvt.write_conc_log('l_product.product_level: ' || l_product.product_level);
789 ozf_utility_pvt.write_conc_log('l_product.product_id: ' || l_product.product_id);
790
791 IF l_product.product_level = 'FAMILY'
792 THEN
793 --
794
795
796 l_func_area_id := get_func_area(l_product.product_id);
797 --
798 ozf_utility_pvt.write_conc_log('l_func_area_id: ' || l_func_area_id);
799
800 END IF;
801
802 FOR l_discount IN c_discount
803 LOOP
804 --
805 -- IF G_DEBUG_LOW THEN
806 --
807 ozf_utility_pvt.write_conc_log('off_discount_product_id:' || l_product.off_discount_product_id);
808 ozf_utility_pvt.write_conc_log('Functional Area for category: ' || l_func_area_id);
809 --
810 -- END IF;
811
812 FND_DSQL.init;
813 FND_DSQL.add_text('INSERT INTO ozf_na_products_temp(inventory_item_id,product_level,discount,discount_type,volume_from,volume_to,volume_type,uom) ');
814 FND_DSQL.add_text('SELECT inventory_item_id,');
815 FND_DSQL.add_bind(l_product.product_level);
816 FND_DSQL.add_text(',');
817 FND_DSQL.add_bind(l_discount.discount);
818 FND_DSQL.add_text(',');
819 FND_DSQL.add_bind(l_discount.discount_type);
820 FND_DSQL.add_text(',');
821 FND_DSQL.add_bind(l_discount.volume_from);
822 FND_DSQL.add_text(',');
823 FND_DSQL.add_bind(l_discount.volume_to);
824 FND_DSQL.add_text(',');
825 FND_DSQL.add_bind(l_discount.volume_type);
826 FND_DSQL.add_text(',');
827 FND_DSQL.add_bind(l_product.uom_code);
828 FND_DSQL.add_text(' FROM (');
829
830 IF l_product.product_level = 'FAMILY'
831 THEN
832 --
833 IF l_func_area_id = 11
834 THEN
835 -- Functional Area is PRFA.
836 --
837 FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
838 FND_DSQL.add_bind(l_org_id);
839 FND_DSQL.add_text(' AND epdhv.parent_id = ');
840 FND_DSQL.add_bind(l_product.product_id);
841 --
842 ELSE
843 -- Functional Area id OMFA
844 FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
845 FND_DSQL.add_bind(l_org_id);
846 FND_DSQL.add_text(' AND category_id = ');
847 FND_DSQL.add_bind(l_product.product_id);
848 --
849 END IF;
850 --
851 ELSIF l_product.product_level = 'PRODUCT'
852 THEN
853 --
854 FND_DSQL.add_text('SELECT ');
855 FND_DSQL.add_bind(l_product.product_id);
856 FND_DSQL.add_text(' inventory_item_id FROM DUAL');
857 --
858 END IF;
859
860 --amitamku - Added for Bug fix 13510229
861 FOR l_exclusion IN c_exclusion(l_product.off_discount_product_id)
862 LOOP
863 --
864
865 ozf_utility_pvt.write_conc_log('Exclusion Loop Start');
866 ozf_utility_pvt.write_conc_log('l_exclusion.product_level: ' || l_exclusion.product_level);
867 ozf_utility_pvt.write_conc_log('l_exclusion.product_id: ' || l_exclusion.product_id);
868
869 --kdass added for bug 13510229 - get functional area of the excluded product for the excluded product query
870 IF l_product.product_level = 'FAMILY' THEN
871 --
872 l_excl_func_area_id := get_func_area(l_exclusion.product_id);
873
874 ozf_utility_pvt.write_conc_log('l_excl_func_area_id: ' || l_excl_func_area_id);
875 ozf_utility_pvt.write_conc_log('l_func_area_id2: ' || l_func_area_id);
876 --
877 END IF;
878
879 FND_DSQL.add_text(' MINUS ');
880
881 IF l_exclusion.product_level = 'PRODUCT'
882 THEN
883 --
884 FND_DSQL.add_text('SELECT ');
885 FND_DSQL.add_bind(l_exclusion.product_id);
886 FND_DSQL.add_text(' inventory_item_id FROM DUAL');
887 --
888 ELSIF l_exclusion.product_level = 'FAMILY'
889 THEN
890 --
891 --IF l_func_area_id = 11 --kdass bug 13510229 - get functional area of the excluded product for the excluded product query
892 IF l_excl_func_area_id = 11
893 THEN
894 -- Functional Area is PRFA.
895 --
896 FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories mtl,eni_prod_denorm_hrchy_v epdhv WHERE mtl.category_set_id = epdhv.category_set_id AND mtl.category_id = epdhv.child_id AND mtl.organization_id = ');
897 FND_DSQL.add_bind(l_org_id);
898 FND_DSQL.add_text(' AND epdhv.parent_id = ');
899 FND_DSQL.add_bind(l_exclusion.product_id);
900 --
901 ozf_utility_pvt.write_conc_log('l_excl_func_area_id1: ' || l_excl_func_area_id);
902 ELSE
903 -- Functional Area id OMFA
904 FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
905 FND_DSQL.add_bind(l_org_id);
906 FND_DSQL.add_text(' AND category_id = ');
907 FND_DSQL.add_bind(l_exclusion.product_id);
908 --
909 ozf_utility_pvt.write_conc_log('l_excl_func_area_id2: ' || l_excl_func_area_id);
910 END IF;
911 --
912 END IF;
913 --
914 END LOOP;
915 --amitamku - End of - Added for Bug fix 13510229
916
917 FND_DSQL.add_text(')');
918
919 l_denorm_csr := DBMS_SQL.open_cursor;
920 FND_DSQL.set_cursor(l_denorm_csr);
921 l_stmt_debug := FND_DSQL.get_text(TRUE);
922 l_stmt_denorm := FND_DSQL.get_text(FALSE);
923 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
924 FND_DSQL.do_binds;
925 l_ignore := DBMS_SQL.execute(l_denorm_csr);
926 dbms_sql.close_cursor(l_denorm_csr);
927
928
929
930 --
931 END LOOP; -- end of discount tiers
932 --
933 END LOOP; -- end of products
934
935
936
937
938 EXCEPTION
939 WHEN OTHERS THEN
940 x_return_status := FND_API.g_ret_sts_unexp_error;
941
942 ozf_utility_pvt.write_conc_log(l_stmt_debug);
943 ozf_utility_pvt.write_conc_log(SQLERRM);
944
945 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
946 THEN
947 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
948 END IF;
949
950 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
951 p_count => x_msg_count,
952 p_data => x_msg_data);
953
954 END populate_prod_tier;
955
956
957 FUNCTION get_accrualed_amount(p_product_id IN NUMBER
958 ,p_line_amt IN NUMBER
959 ,p_quantity IN NUMBER
960 ,p_uom IN VARCHAR2)
961 RETURN NUMBER
962 IS
963 --
964 CURSOR c_disc_for_item_count IS
965 SELECT COUNT(*)
966 FROM ozf_na_products_temp
967 WHERE inventory_item_id = p_product_id
968 AND product_level = 'PRODUCT';
969
970 CURSOR c_discount_for_cat IS
971 SELECT discount,
972 discount_type,
973 volume_type,
974 volume_from,
975 volume_to,
976 uom
977 FROM ozf_na_products_temp
978 WHERE inventory_item_id = p_product_id
979 AND product_level = 'FAMILY';
980
981 CURSOR c_discount_for_item IS
982 SELECT discount,
983 discount_type,
984 volume_type,
985 volume_from,
986 volume_to, uom
987 FROM ozf_na_products_temp
988 WHERE inventory_item_id = p_product_id
989 AND product_level = 'PRODUCT';
990
991 l_max_accrual NUMBER;
992 l_line_accrual NUMBER;
993 l_disc_for_item_count NUMBER;
994 l_volume_qualified VARCHAR2(1);
995
996 BEGIN
997 --
998 OPEN c_disc_for_item_count;
999 FETCH c_disc_for_item_count INTO l_disc_for_item_count;
1000 CLOSE c_disc_for_item_count;
1001
1002 l_max_accrual := 0;
1003
1004 IF l_disc_for_item_count = 0
1005 THEN
1006 --
1007 FOR l_discount_for_cat IN c_discount_for_cat
1008 LOOP
1009 --
1010 l_line_accrual := 0;
1011 l_volume_qualified := 'N';
1012
1013 -- check if order satisfies amt/qty requirement
1014 IF ( l_discount_for_cat.volume_type = 'AMT' )
1015 THEN
1016 --
1017 IF ( p_line_amt >= l_discount_for_cat.volume_from
1018 AND
1019 p_line_amt <= l_discount_for_cat.volume_to )
1020 THEN
1021 --
1022 l_volume_qualified := 'Y';
1023 --
1024 ELSE
1025 --
1026 l_volume_qualified := 'N';
1027 --
1028 END IF;
1029 --
1030 ELSIF ( l_discount_for_cat.volume_type = 'QTY' )
1031 THEN
1032 --
1033 IF ( p_quantity >= l_discount_for_cat.volume_from
1034 AND
1035 p_quantity <= l_discount_for_cat.volume_to )
1036 THEN
1037 --
1038 l_volume_qualified := 'Y';
1039 --
1040 ELSE
1041 --
1042 l_volume_qualified := 'N';
1043 --
1044 END IF;
1045 --
1046 ELSIF ( l_discount_for_cat.volume_type = 'NA' )
1047 THEN
1048 --
1049 l_volume_qualified := 'Y';
1050 --
1051 END IF;
1052
1053
1054
1055 IF l_volume_qualified = 'Y'
1056 THEN
1057 --
1058 IF l_discount_for_cat.discount_type = '%'
1059 THEN
1060 --
1061 l_line_accrual := p_line_amt * l_discount_for_cat.discount / 100;
1062 --
1063 ELSE
1064 --
1065 l_line_accrual := l_discount_for_cat.discount;
1066 --
1067 END IF;
1068 --
1069 END IF;
1070
1071 -- Memorizes larger accrual amount
1072 -- nepanda : Fix for Bug 09204988
1073 IF abs(l_line_accrual) > abs(l_max_accrual) --IF l_line_accrual > l_max_accrual
1074 THEN
1075 l_max_accrual:= l_line_accrual;
1076 END IF;
1077
1078 -- IF G_DEBUG_LOW THEN
1079 ozf_utility_pvt.write_conc_log('Product belongs to a Category on the Offer');
1080 ozf_utility_pvt.write_conc_log('ItmId/Qty/Amt/VolType/DiscType/disc/VolQual?');
1081 ozf_utility_pvt.write_conc_log(p_product_id || '/' ||
1082 p_quantity || '/' ||
1083 p_line_amt || '/' ||
1084 l_discount_for_cat.volume_type || '/' ||
1085 l_discount_for_cat.discount_type || '/' ||
1086 l_discount_for_cat.discount || '/' ||
1087 l_volume_qualified );
1088 -- END IF;
1089 --
1090 END LOOP;
1091 --
1092 ELSE
1093 -- discount for the item exists. take this value as accrualed discount
1094 FOR l_discount_for_item IN c_discount_for_item
1095 LOOP
1096 ozf_utility_pvt.write_conc_log('l_volume_qualified '||l_volume_qualified);
1097 ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_from '|| l_discount_for_item.volume_from);
1098 ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_to '|| l_discount_for_item.volume_to);
1099 ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_type '|| l_discount_for_item.volume_type);
1100 ozf_utility_pvt.write_conc_log('p_line_amt '|| p_line_amt);
1101 ozf_utility_pvt.write_conc_log('l_discount_for_item.discount '|| l_discount_for_item.discount);
1102 ozf_utility_pvt.write_conc_log('l_discount_for_item.discount_type '|| l_discount_for_item.discount_type);
1103 --
1104 l_line_accrual := 0;
1105 l_volume_qualified := 'N';
1106 -- check if order satisfies amt/qty requirement
1107 IF ( l_discount_for_item.volume_type = 'AMT' )
1108 THEN
1109 --
1110 IF ( p_line_amt >= l_discount_for_item.volume_from
1111 AND
1112 p_line_amt <= l_discount_for_item.volume_to )
1113 THEN
1114 --
1115 l_volume_qualified := 'Y';
1116 --
1117 ELSE
1118 --
1119 l_volume_qualified := 'N';
1120 --
1121 END IF;
1122 --
1123 ELSIF ( l_discount_for_item.volume_type = 'QTY' )
1124 THEN
1125 --
1126 IF ( p_quantity >= l_discount_for_item.volume_from
1127 AND
1128 p_quantity <= l_discount_for_item.volume_to )
1129 THEN
1130 --
1131 l_volume_qualified := 'Y';
1132 --
1133 ELSE
1134 --
1135 l_volume_qualified := 'N';
1136 --
1137 END IF;
1138 --
1139 ELSIF ( l_discount_for_item.volume_type = 'NA' )
1140 THEN
1141 --
1142 l_volume_qualified := 'Y';
1143 --
1144 END IF;
1145
1146 -- Calculate Accrual Amount
1147 IF l_volume_qualified = 'Y'
1148 THEN
1149 --
1150 IF ( l_discount_for_item.discount_type = '%' )
1151 THEN
1152 --
1153 l_line_accrual := p_line_amt * l_discount_for_item.discount / 100;
1154 --
1155 ELSE
1156 --
1157 l_line_accrual := l_discount_for_item.discount * p_quantity; -- give discount based on quantity
1158 --
1159 END IF;
1160 --
1161 END IF;
1162 -- memorizes larger accrual amount
1163 -- nepanda : Fix for Bug 09204988
1164 IF abs(l_line_accrual) > abs(l_max_accrual) -- IF l_line_accrual > l_max_accrual
1165 THEN
1166 --
1167 l_max_accrual:= l_line_accrual;
1168 --
1169 END IF;
1170
1171 IF G_DEBUG_LOW THEN
1172 ozf_utility_pvt.write_conc_log('ItmId/Qty/Amt/VolType/DiscType/disc/VolQual?/MaxAccr');
1173 ozf_utility_pvt.write_conc_log(p_product_id || '/' ||
1174 p_quantity || '/' ||
1175 p_line_amt || '/' ||
1176 l_discount_for_item.volume_type || '/' ||
1177 l_discount_for_item.discount_type || '/' ||
1178 l_discount_for_item.discount || '/' ||
1179 l_volume_qualified || '/' ||
1180 l_max_accrual );
1181 END IF;
1182 --
1183 END LOOP;
1184 --
1185 END IF;
1186
1187 RETURN l_max_accrual;
1188
1189 END get_accrualed_amount;
1190
1191
1192 FUNCTION get_pv_accrual_amount(p_product_id IN NUMBER
1193 ,p_line_amt IN NUMBER
1194 ,p_offer_id IN NUMBER
1195 ,p_org_id IN NUMBER
1196 ,p_list_hdr_id IN NUMBER
1197 ,p_referral_id IN NUMBER
1198 ,p_order_hdr_id IN NUMBER)
1199 RETURN NUMBER
1200 IS
1201 -- given category, find max compensation from referral tables
1202 CURSOR c_maximum_compensation(p_category_id NUMBER) IS
1203 SELECT b.maximum_compensation
1204 FROM pv_ge_benefits_vl a, pv_benft_products b
1205 WHERE a.benefit_id = b.benefit_id
1206 AND a.benefit_type_code = 'PVREFFRL'
1207 AND a.additional_info_1 = p_offer_id
1208 AND b.product_category_id = p_category_id;
1209
1210 -- find accruals already made by the referral
1211 CURSOR c_existing_accruals IS
1212 SELECT NVL(DECODE(gl_posted_flag, 'Y', plan_curr_amount), 0) line_amount, product_id
1213 FROM ozf_funds_utilized_all_b
1214 WHERE reference_type = 'LEAD_REFERRAL'
1215 AND reference_id = p_referral_id
1216 AND plan_type = 'OFFR'
1217 AND plan_id = p_list_hdr_id
1218 AND object_type = 'ORDER'
1219 AND object_id = p_order_hdr_id;
1220
1221 l_discount NUMBER;
1222 l_category_id NUMBER;
1223 l_discount_temp NUMBER;
1224 l_category_id_temp NUMBER; -- temperorily store category id for accrualed items
1225 l_max_compensation NUMBER;
1226 l_accrualed_amount NUMBER := 0;
1227 l_acc_amt_order NUMBER := 0;
1228 l_return_value NUMBER := 0;
1229 l_stmt VARCHAR2(2000);
1230 BEGIN
1231 --
1232 l_stmt := 'SELECT';
1233 l_stmt := l_stmt || ' DISTINCT FIRST_VALUE(a.discount) OVER (PARTITION BY epdhv.child_id ORDER BY c.category_level_num DESC NULLS LAST) discount, ' ;
1234 l_stmt := l_stmt || ' FIRST_VALUE(b.product_id) OVER (PARTITION BY epdhv.child_id ORDER BY c.category_level_num DESC NULLS LAST) product_id ';
1235 l_stmt := l_stmt || ' FROM ozf_offer_discount_lines a, ';
1236 l_stmt := l_stmt || ' ozf_offer_discount_products b, ';
1237 l_stmt := l_stmt || ' eni_prod_den_hrchy_parents_v c, ';
1238 l_stmt := l_stmt || ' mtl_item_categories mic, ';
1239 l_stmt := l_stmt || ' eni_prod_denorm_hrchy_v epdhv ';
1240 l_stmt := l_stmt || ' WHERE a.offer_discount_line_id = b.offer_discount_line_id ';
1241 l_stmt := l_stmt || ' AND a.offer_id = :1 ';
1242 l_stmt := l_stmt || ' AND mic.inventory_item_id = :2 ';
1243 l_stmt := l_stmt || ' AND mic.category_set_id = epdhv.category_set_id ';
1244 l_stmt := l_stmt || ' AND mic.category_id = epdhv.child_id ';
1245 l_stmt := l_stmt || ' AND mic.organization_id = :3 ';
1246 l_stmt := l_stmt || ' AND b.product_id = epdhv.parent_id ';
1247 l_stmt := l_stmt || ' AND epdhv.parent_id = c.category_id';
1248
1249 IF G_DEBUG_LOW THEN
1250 ozf_utility_pvt.write_conc_log('Statement is : ' || l_stmt);
1251 ozf_utility_pvt.write_conc_log('Bind var is : ' || p_product_id);
1252 END IF;
1253
1254 EXECUTE IMMEDIATE l_stmt INTO l_discount, l_category_id USING p_offer_id, p_product_id, p_org_id;
1255
1256 IF G_DEBUG_LOW THEN
1257 ozf_utility_pvt.write_conc_log('Discount : ' || l_discount);
1258 ozf_utility_pvt.write_conc_log('Category_Id : ' || l_category_id);
1259 END IF;
1260
1261 IF ( l_discount IS NOT NULL AND l_category_id IS NOT NULL )
1262 THEN
1263 -- Discount rule exists
1264 l_acc_amt_order := p_line_amt * l_discount / 100;
1265
1266 OPEN c_maximum_compensation(l_category_id);
1267 FETCH c_maximum_compensation INTO l_max_compensation;
1268 CLOSE c_maximum_compensation;
1269
1270 IF G_DEBUG_LOW THEN
1271 ozf_utility_pvt.write_conc_log('accrual for order line:'||l_acc_amt_order);
1272 ozf_utility_pvt.write_conc_log('max compensation:'||l_max_compensation);
1273 END IF;
1274
1275 FOR i IN c_existing_accruals
1276 LOOP
1277 --
1278 EXECUTE IMMEDIATE l_stmt INTO l_discount_temp, l_category_id_temp USING p_offer_id, i.product_id, p_org_id;
1279 --
1280 IF l_category_id_temp = l_category_id
1281 THEN
1282 -- Other item from same category found
1283 l_accrualed_amount := l_accrualed_amount + i.line_amount;
1284 --
1285 END IF;
1286 --
1287 END LOOP;
1288
1289 IF ( l_max_compensation IS NULL OR
1290 (l_max_compensation - l_accrualed_amount >= l_acc_amt_order)
1291 )
1292 THEN
1293 --
1294 l_return_value := l_acc_amt_order;
1295 --
1296 ELSE
1297 --
1298 l_return_value := l_max_compensation - l_accrualed_amount;
1299 --
1300 END IF;
1301 --
1302 END IF;
1303 --
1304 RETURN l_return_value;
1305 --
1306 END get_pv_accrual_amount;
1307
1308 PROCEDURE log_exception(p_act_budgets_rec IN ozf_actbudgets_pvt.act_budgets_rec_type,
1309 p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type)
1310 IS
1311 CURSOR c_na_conc_exception_id IS
1312 SELECT ozf_na_conc_exceptions_s.NEXTVAL
1313 FROM DUAL;
1314
1315 CURSOR c_pk_exist(p_na_conc_exception_id NUMBER) IS
1316 SELECT 1
1317 FROM DUAL
1318 WHERE EXISTS (SELECT 1
1319 FROM ozf_na_conc_exceptions
1320 WHERE na_conc_exception_id = p_na_conc_exception_id);
1321
1322 l_na_conc_exception_id NUMBER;
1323 l_pk_exist NUMBER;
1324 BEGIN
1325 ozf_utility_pvt.write_conc_log('Writing exception log for offer ' || p_act_budgets_rec.act_budget_used_by_id);
1326 LOOP
1327 l_pk_exist := NULL;
1328
1329 OPEN c_na_conc_exception_id;
1330 FETCH c_na_conc_exception_id INTO l_na_conc_exception_id;
1331 CLOSE c_na_conc_exception_id;
1332
1333 OPEN c_pk_exist(l_na_conc_exception_id);
1334 FETCH c_pk_exist INTO l_pk_exist;
1335 CLOSE c_pk_exist;
1336
1337 EXIT WHEN l_pk_exist IS NULL;
1338 END LOOP;
1339
1340 INSERT INTO ozf_na_conc_exceptions(na_conc_exception_id
1341 ,act_budget_used_by_id
1342 ,arc_act_budget_used_by
1343 ,budget_source_type
1344 ,budget_source_id
1345 ,request_amount
1346 ,request_currency
1347 ,request_date
1348 ,status_code
1349 ,approved_amount
1350 ,approved_in_currency
1351 ,approval_date
1352 ,approver_id
1353 ,transfer_type
1354 ,requester_id
1355 ,object_type
1356 ,object_id
1357 ,product_level_type
1358 ,product_id
1359 ,cust_account_id
1360 ,utilization_type
1361 ,adjustment_date
1362 ,gl_date
1363 ,billto_cust_account_id
1364 ,reference_type
1365 ,reference_id
1366 ,order_line_id
1367 ,org_id)
1368 VALUES(l_na_conc_exception_id
1369 ,p_act_budgets_rec.act_budget_used_by_id
1370 ,p_act_budgets_rec.arc_act_budget_used_by
1371 ,p_act_budgets_rec.budget_source_type
1372 ,p_act_budgets_rec.budget_source_id
1373 ,p_act_budgets_rec.request_amount
1374 ,p_act_budgets_rec.request_currency
1375 ,p_act_budgets_rec.request_date
1376 ,p_act_budgets_rec.status_code
1377 ,p_act_budgets_rec.approved_amount
1378 ,p_act_budgets_rec.approved_in_currency
1379 ,p_act_budgets_rec.approval_date
1380 ,p_act_budgets_rec.approver_id
1381 ,p_act_budgets_rec.transfer_type
1382 ,p_act_budgets_rec.requester_id
1383 ,p_act_util_rec.object_type
1384 ,p_act_util_rec.object_id
1385 ,p_act_util_rec.product_level_type
1386 ,p_act_util_rec.product_id
1387 ,p_act_util_rec.cust_account_id
1388 ,p_act_util_rec.utilization_type
1389 ,p_act_util_rec.adjustment_date
1390 ,p_act_util_rec.gl_date
1391 ,p_act_util_rec.billto_cust_account_id
1392 ,p_act_util_rec.reference_type
1393 ,p_act_util_rec.reference_id
1394 ,p_act_util_rec.order_line_id
1395 ,p_act_util_rec.org_id);
1396 END log_exception;
1397
1398
1399 PROCEDURE process_exceptions IS
1400
1401 CURSOR c_exception_rec IS
1402 SELECT *
1403 FROM ozf_na_conc_exceptions;
1404
1405 l_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type;
1406 l_act_util_rec ozf_actbudgets_pvt.act_util_rec_type;
1407 l_return_status VARCHAR2(1);
1408 l_msg_count NUMBER;
1409 l_msg_data VARCHAR2(2000);
1410 l_act_budget_id NUMBER;
1411 l_utilized_amount NUMBER := 0;
1412
1413 BEGIN
1414 --
1415 FOR l_exception_rec IN c_exception_rec
1416 LOOP
1417 --
1418 IF G_DEBUG_LOW
1419 THEN
1420 ozf_utility_pvt.write_conc_log('Processing exception Id = ' || l_exception_rec.na_conc_exception_id);
1421 END IF;
1422
1423 l_return_status := FND_API.g_ret_sts_success; -- bug 3655853
1424
1425 l_act_budgets_rec.act_budget_used_by_id := l_exception_rec.act_budget_used_by_id;
1426 l_act_budgets_rec.arc_act_budget_used_by := l_exception_rec.arc_act_budget_used_by;
1427 l_act_budgets_rec.budget_source_type := l_exception_rec.budget_source_type;
1428 l_act_budgets_rec.budget_source_id := l_exception_rec.budget_source_id;
1429 l_act_budgets_rec.request_amount := l_exception_rec.request_amount;
1430 l_act_budgets_rec.request_currency := l_exception_rec.request_currency;
1431 l_act_budgets_rec.request_date := l_exception_rec.request_date;
1432 l_act_budgets_rec.status_code := l_exception_rec.status_code;
1433 l_act_budgets_rec.approved_amount := l_exception_rec.approved_amount;
1434 l_act_budgets_rec.approved_in_currency := l_exception_rec.approved_in_currency;
1435 l_act_budgets_rec.approval_date := l_exception_rec.approval_date;
1436 l_act_budgets_rec.approver_id := l_exception_rec.approver_id;
1437 l_act_budgets_rec.transfer_type := l_exception_rec.transfer_type;
1438 l_act_budgets_rec.requester_id := l_exception_rec.requester_id;
1439
1440 l_act_util_rec.object_type := l_exception_rec.object_type;
1441 l_act_util_rec.object_id := l_exception_rec.object_id;
1442 l_act_util_rec.product_level_type := l_exception_rec.product_level_type;
1443 l_act_util_rec.product_id := l_exception_rec.product_id;
1444 l_act_util_rec.cust_account_id := l_exception_rec.cust_account_id;
1445 l_act_util_rec.utilization_type := l_exception_rec.utilization_type;
1446 l_act_util_rec.adjustment_date := l_exception_rec.adjustment_date;
1447 l_act_util_rec.gl_date := l_exception_rec.gl_date;
1448 l_act_util_rec.billto_cust_account_id := l_exception_rec.billto_cust_account_id;
1449 l_act_util_rec.reference_type := l_exception_rec.reference_type;
1450 l_act_util_rec.reference_id := l_exception_rec.reference_id;
1451 l_act_util_rec.order_line_id := l_exception_rec.order_line_id;
1452 l_act_util_rec.org_id := l_exception_rec.org_id;
1453
1454 ozf_fund_adjustment_pvt.process_act_budgets(x_return_status => l_return_status
1455 ,x_msg_count => l_msg_count
1456 ,x_msg_data => l_msg_data
1457 ,p_act_budgets_rec => l_act_budgets_rec
1458 ,p_act_util_rec => l_act_util_rec
1459 ,x_act_budget_id => l_act_budget_id
1460 ,x_utilized_amount => l_utilized_amount);
1461
1462 IF G_DEBUG_LOW
1463 THEN
1464 ozf_utility_pvt.write_conc_log('Exception_id - Status: ' || l_exception_rec.na_conc_exception_id
1465 || ' - '
1466 || l_return_status);
1467 ozf_utility_pvt.write_conc_log('Utilization Amount Created: ' || l_utilized_amount);
1468 END IF;
1469
1470 IF l_return_status = FND_API.g_ret_sts_success
1471 THEN
1472 --
1473 DELETE FROM ozf_na_conc_exceptions
1474 WHERE na_conc_exception_id = l_exception_rec.na_conc_exception_id;
1475 --
1476 END IF;
1477 l_utilized_amount := 0;
1478 --
1479 END LOOP; -- Done Processing exception records
1480
1481 END process_exceptions;
1482
1483
1484 --------------------
1485 -- Main Procedure
1486 --------------------
1487
1488 PROCEDURE net_accrual_engine( ERRBUF OUT NOCOPY VARCHAR2,
1489 RETCODE OUT NOCOPY VARCHAR2,
1490 p_as_of_date IN VARCHAR2,
1491 p_offer_id IN NUMBER DEFAULT NULL)
1492 IS
1493 --
1494 CURSOR c_net_accrual_offers IS
1495 SELECT ozf.offer_id,
1496 ozf.qp_list_header_id,
1497 ozf.latest_na_completion_date,
1498 ozf.custom_setup_id,
1499 ozf.tier_level,
1500 NVL(ozf.transaction_currency_code, ozf.fund_request_curr_code) fund_request_curr_code,
1501 transaction_currency_code,
1502 ozf.qualifier_id,
1503 ozf.na_rule_header_id,
1504 ozf.owner_id,
1505 TRUNC(qp.start_date_active) start_date_active,
1506 TRUNC(qp.end_date_active + 1) - (1/86400) end_date_active,
1507 qp.orig_org_id,
1508 qp_tl.description offer_name,
1509 ozf.sales_method_flag,
1510 NVL(ozf.resale_line_id_processed, 0) resale_line_id_processed
1511 FROM ozf_offers ozf,
1512 qp_list_headers_all_b qp,
1513 qp_list_headers_tl qp_tl
1514 WHERE ozf.offer_type = 'NET_ACCRUAL'
1515 AND ozf.status_code = 'ACTIVE'
1516 AND ozf.offer_id = NVL(p_offer_id, ozf.offer_id)
1517 AND ozf.qp_list_header_id = qp.list_header_id
1518 AND qp.list_header_id = qp_tl.list_header_id
1519 -- AND qp.orig_org_id = TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10))
1520 AND qp_tl.language = USERENV('LANG');
1521
1522 CURSOR c_na_rule_lines(p_na_rule_header_id NUMBER) IS
1523 SELECT na_deduction_rule_id
1524 FROM ozf_na_rule_lines
1525 WHERE na_rule_header_id = p_na_rule_header_id
1526 AND active_flag = 'Y';
1527
1528 CURSOR c_na_deduction_rule(p_deduction_rule_id NUMBER) IS
1529 SELECT a.na_deduction_rule_id,
1530 a.transaction_source_code,
1531 a.transaction_type_code,
1532 a.deduction_identifier_id,
1533 a.deduction_identifier_org_id,
1534 b.name
1535 FROM ozf_na_deduction_rules_b a,
1536 ozf_na_deduction_rules_tl b
1537 WHERE a.na_deduction_rule_id = b.na_deduction_rule_id
1538 AND b.language = USERENV('LANG')
1539 AND a.na_deduction_rule_id = p_deduction_rule_id;
1540
1541 l_na_deduction_rule c_na_deduction_rule%ROWTYPE;
1542
1543 -- BUG 11889816 start
1544 -- Removed org_id condition for PRM offers, changed Index Hint,
1545 -- replaced table OZF_NA_PRODUCTS_TEMP by OZF_NA_UNIQUE_PRODUCTS_TEMP
1546 -- kdass - bug 14777031 - split the query into 2 similar to c_order_line
1547 CURSOR c_order_line_prm ( p_start_date DATE
1548 , p_end_date DATE
1549 ) IS
1550 SELECT /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
1551 ol.header_id,
1552 ol.line_id,
1553 ol.invoice_to_org_id,
1554 ol.ship_to_org_id,
1555 ol.sold_to_org_id,
1556 ol.inventory_item_id,
1557 ol.shipped_quantity,
1558 ol.fulfilled_quantity,
1559 ol.invoiced_quantity,
1560 ol.pricing_quantity,
1561 ol.pricing_quantity_uom,
1562 ol.unit_selling_price,
1563 ol.org_id,
1564 ol.fulfillment_date conv_date,
1565 oh.transactional_curr_code,
1566 ol.shipping_quantity, -- Catch Weight ER
1567 ol.shipping_quantity_uom, -- Catch Weight ER
1568 ol.shipping_quantity2, -- Catch Weight ER
1569 ol.shipping_quantity_uom2, -- Catch Weight ER
1570 ol.fulfillment_base -- Catch Weight ER
1571 FROM ( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP ) na,
1572 oe_order_lines_all ol,
1573 oe_order_headers_all oh
1574 WHERE ol.inventory_item_id = na.inventory_item_id
1575 --AND OL.FLOW_STATUS_CODE IN ('SHIPPED','CLOSED') --kdass - commented for bug 14777031
1576 AND OL.CANCELLED_FLAG = 'N' AND OL.LINE_CATEGORY_CODE <> 'RETURN'
1577 AND OL.ACTUAL_SHIPMENT_DATE IS NOT NULL
1578 AND (OL.ACTUAL_SHIPMENT_DATE BETWEEN p_start_date AND p_end_date)
1579 --AND ( NVL(OL.ACTUAL_SHIPMENT_DATE, OL.FULFILLMENT_DATE) BETWEEN p_start_date AND p_end_date)
1580 AND OL.HEADER_ID = OH.HEADER_ID
1581 UNION ALL
1582 SELECT /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
1583 ol.header_id,
1584 ol.line_id,
1585 ol.invoice_to_org_id,
1586 ol.ship_to_org_id,
1587 ol.sold_to_org_id,
1588 ol.inventory_item_id,
1589 ol.shipped_quantity,
1590 ol.fulfilled_quantity,
1591 ol.invoiced_quantity,
1592 ol.pricing_quantity,
1593 ol.pricing_quantity_uom,
1594 ol.unit_selling_price,
1595 ol.org_id,
1596 ol.fulfillment_date conv_date,
1597 oh.transactional_curr_code,
1598 ol.shipping_quantity, -- Catch Weight ER
1599 ol.shipping_quantity_uom, -- Catch Weight ER
1600 ol.shipping_quantity2, -- Catch Weight ER
1601 ol.shipping_quantity_uom2, -- Catch Weight ER
1602 ol.fulfillment_base -- Catch Weight ER
1603 FROM ( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP ) na,
1604 oe_order_lines_all ol,
1605 oe_order_headers_all oh
1606 WHERE ol.inventory_item_id = na.inventory_item_id
1607 --AND OL.FLOW_STATUS_CODE IN ('SHIPPED','CLOSED') --kdass - commented for bug 14777031
1608 AND OL.CANCELLED_FLAG = 'N' AND OL.LINE_CATEGORY_CODE <> 'RETURN'
1609 AND OL.ACTUAL_SHIPMENT_DATE IS NULL
1610 AND (OL.FULFILLMENT_DATE BETWEEN p_start_date AND p_end_date)
1611 --AND ( NVL(OL.ACTUAL_SHIPMENT_DATE, OL.FULFILLMENT_DATE) BETWEEN p_start_date AND p_end_date)
1612 AND OL.HEADER_ID = OH.HEADER_ID;
1613
1614 --Bug 8263942 - Modified c_order_line cursor:
1615 --Split the query into 2 based on ACTUAL_SHIPMENT_DATE, changed Index Hint,
1616 --replaced table OZF_NA_PRODUCTS_TEMP by OZF_NA_UNIQUE_PRODUCTS_TEMP
1617 CURSOR c_order_line (p_start_date DATE,
1618 p_end_date DATE,
1619 p_offer_org_id NUMBER) IS
1620 SELECT /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
1621 OL.HEADER_ID, OL.LINE_ID, OL.INVOICE_TO_ORG_ID, OL.SHIP_TO_ORG_ID,
1622 OL.SOLD_TO_ORG_ID, OL.INVENTORY_ITEM_ID, OL.SHIPPED_QUANTITY,
1623 OL.FULFILLED_QUANTITY, OL.INVOICED_QUANTITY, OL.PRICING_QUANTITY,
1624 OL.PRICING_QUANTITY_UOM, OL.UNIT_SELLING_PRICE, OL.ORG_ID,
1625 OL.FULFILLMENT_DATE CONV_DATE, OH.TRANSACTIONAL_CURR_CODE,
1626 OL.SHIPPING_QUANTITY, -- Catch Weight ER
1627 OL.SHIPPING_QUANTITY_UOM, -- Catch Weight ER
1628 OL.SHIPPING_QUANTITY2, -- Catch Weight ER
1629 OL.SHIPPING_QUANTITY_UOM2, -- Catch Weight ER
1630 OL.FULFILLMENT_BASE -- Catch Weight ER
1631 FROM
1632 ( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP ) NA,
1633 OE_ORDER_LINES_ALL OL, OE_ORDER_HEADERS_ALL OH
1634 WHERE
1635 OL.INVENTORY_ITEM_ID = NA.INVENTORY_ITEM_ID
1636 --AND OL.FLOW_STATUS_CODE IN ('SHIPPED','CLOSED') --kdass - commented for bug 14777031
1637 AND OL.CANCELLED_FLAG = 'N' AND OL.LINE_CATEGORY_CODE <>'RETURN'
1638 AND OL.ACTUAL_SHIPMENT_DATE is not null
1639 AND (OL.ACTUAL_SHIPMENT_DATE BETWEEN p_start_date AND p_end_date)
1640 AND OL.ORG_ID = p_offer_org_id
1641 AND OL.HEADER_ID = OH.HEADER_ID
1642 UNION ALL
1643 SELECT /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
1644 OL.HEADER_ID, OL.LINE_ID, OL.INVOICE_TO_ORG_ID, OL.SHIP_TO_ORG_ID,
1645 OL.SOLD_TO_ORG_ID, OL.INVENTORY_ITEM_ID, OL.SHIPPED_QUANTITY,
1646 OL.FULFILLED_QUANTITY, OL.INVOICED_QUANTITY, OL.PRICING_QUANTITY,
1647 OL.PRICING_QUANTITY_UOM, OL.UNIT_SELLING_PRICE, OL.ORG_ID,
1648 OL.FULFILLMENT_DATE CONV_DATE, OH.TRANSACTIONAL_CURR_CODE,
1649 OL.SHIPPING_QUANTITY, -- Catch Weight ER
1650 OL.SHIPPING_QUANTITY_UOM, -- Catch Weight ER
1651 OL.SHIPPING_QUANTITY2, -- Catch Weight ER
1652 OL.SHIPPING_QUANTITY_UOM2, -- Catch Weight ER
1653 OL.FULFILLMENT_BASE -- Catch Weight ER
1654 FROM
1655 ( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP ) NA,
1656 OE_ORDER_LINES_ALL OL, OE_ORDER_HEADERS_ALL OH
1657 WHERE
1658 OL.INVENTORY_ITEM_ID = NA.INVENTORY_ITEM_ID
1659 --AND OL.FLOW_STATUS_CODE IN ('SHIPPED','CLOSED') --kdass - commented for bug 14777031
1660 AND OL.CANCELLED_FLAG = 'N' AND OL.LINE_CATEGORY_CODE <>'RETURN'
1661 AND OL.ACTUAL_SHIPMENT_DATE is NULL
1662 AND (OL.FULFILLMENT_DATE BETWEEN p_start_date AND p_end_date)
1663 AND OL.ORG_ID = p_offer_org_id
1664 AND OL.HEADER_ID = OH.HEADER_ID;
1665
1666 -- nepanda : bug # 14277687
1667 -- this cursor is for global offers where org_id is null for the offer.
1668 CURSOR c_order_line_global (p_start_date DATE,
1669 p_end_date DATE) IS
1670 SELECT /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
1671 OL.HEADER_ID, OL.LINE_ID, OL.INVOICE_TO_ORG_ID, OL.SHIP_TO_ORG_ID,
1672 OL.SOLD_TO_ORG_ID, OL.INVENTORY_ITEM_ID, OL.SHIPPED_QUANTITY,
1673 OL.FULFILLED_QUANTITY, OL.INVOICED_QUANTITY, OL.PRICING_QUANTITY,
1674 OL.PRICING_QUANTITY_UOM, OL.UNIT_SELLING_PRICE, OL.ORG_ID,
1675 OL.FULFILLMENT_DATE CONV_DATE, OH.TRANSACTIONAL_CURR_CODE,
1676 OL.SHIPPING_QUANTITY, -- Catch Weight ER
1677 OL.SHIPPING_QUANTITY_UOM, -- Catch Weight ER
1678 OL.SHIPPING_QUANTITY2, -- Catch Weight ER
1679 OL.SHIPPING_QUANTITY_UOM2, -- Catch Weight ER
1680 OL.FULFILLMENT_BASE -- Catch Weight ER
1681 FROM
1682 ( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP ) NA,
1683 OE_ORDER_LINES_ALL OL, OE_ORDER_HEADERS_ALL OH
1684 WHERE
1685 OL.INVENTORY_ITEM_ID = NA.INVENTORY_ITEM_ID
1686 --AND OL.FLOW_STATUS_CODE IN ('SHIPPED','CLOSED') --kdass - commented for bug 14777031
1687 AND OL.CANCELLED_FLAG = 'N' AND OL.LINE_CATEGORY_CODE <>'RETURN'
1688 AND OL.ACTUAL_SHIPMENT_DATE is not null
1689 AND (OL.ACTUAL_SHIPMENT_DATE BETWEEN p_start_date AND p_end_date)
1690 AND OL.HEADER_ID = OH.HEADER_ID
1691 UNION ALL
1692 SELECT /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
1693 OL.HEADER_ID, OL.LINE_ID, OL.INVOICE_TO_ORG_ID, OL.SHIP_TO_ORG_ID,
1694 OL.SOLD_TO_ORG_ID, OL.INVENTORY_ITEM_ID, OL.SHIPPED_QUANTITY,
1695 OL.FULFILLED_QUANTITY, OL.INVOICED_QUANTITY, OL.PRICING_QUANTITY,
1696 OL.PRICING_QUANTITY_UOM, OL.UNIT_SELLING_PRICE, OL.ORG_ID,
1697 OL.FULFILLMENT_DATE CONV_DATE, OH.TRANSACTIONAL_CURR_CODE,
1698 OL.SHIPPING_QUANTITY, -- Catch Weight ER
1699 OL.SHIPPING_QUANTITY_UOM, -- Catch Weight ER
1700 OL.SHIPPING_QUANTITY2, -- Catch Weight ER
1701 OL.SHIPPING_QUANTITY_UOM2, -- Catch Weight ER
1702 OL.FULFILLMENT_BASE -- Catch Weight ER
1703 FROM
1704 ( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP ) NA,
1705 OE_ORDER_LINES_ALL OL, OE_ORDER_HEADERS_ALL OH
1706 WHERE
1707 OL.INVENTORY_ITEM_ID = NA.INVENTORY_ITEM_ID
1708 --AND OL.FLOW_STATUS_CODE IN ('SHIPPED','CLOSED') --kdass - commented for bug 14777031
1709 AND OL.CANCELLED_FLAG = 'N' AND OL.LINE_CATEGORY_CODE <>'RETURN'
1710 AND OL.ACTUAL_SHIPMENT_DATE is NULL
1711 AND (OL.FULFILLMENT_DATE BETWEEN p_start_date AND p_end_date)
1712 AND OL.HEADER_ID = OH.HEADER_ID;
1713 CURSOR c_idsm_line (p_offer_start_date DATE,
1714 p_offer_end_date DATE,
1715 p_offer_org_id NUMBER,
1716 p_resale_line_id NUMBER) IS
1717 SELECT resale_header_id header_id,
1718 resale_line_id line_id,
1719 date_ordered actual_shipment_date,
1720 NULL fulfillment_date,
1721 bill_to_site_use_id invoice_to_org_id,
1722 ship_to_site_use_id ship_to_org_id,
1723 bill_to_cust_account_id sold_to_org_id,
1724 inventory_item_id,
1725 quantity shipped_quantity,
1726 quantity fulfilled_quantity,
1727 quantity invoiced_quantity,
1728 quantity pricing_quantity,
1729 uom_code pricing_quantity_uom,
1730 selling_price unit_selling_price,
1731 org_id,
1732 NVL(exchange_rate_date, date_ordered) conv_date,
1733 currency_code transactional_curr_code
1734 FROM ozf_resale_lines_all
1735 WHERE inventory_item_id IN ( SELECT na.inventory_item_id
1736 FROM ozf_na_products_temp na)
1737 -- AND ol.flow_status_code IN ('SHIPPED','CLOSED')
1738 -- AND ol.cancelled_flag = 'N'
1739 -- AND ol.line_category_code <> 'RETURN'
1740 AND TRUNC(date_ordered) >= TRUNC(p_offer_start_date)
1741 AND TRUNC(date_ordered) <= TRUNC(NVL(p_offer_end_date, SYSDATE))
1742 AND org_id = NVL(p_offer_org_id, org_id)
1743 AND quantity > 0
1744 AND resale_header_id > p_resale_line_id
1745 ORDER BY resale_line_id;
1746
1747 CURSOR c_ar_trx_line_details( p_cust_trx_type_id NUMBER,
1748 p_start_date DATE,
1749 p_end_date DATE,
1750 p_org_id NUMBER
1751 ) IS
1752 SELECT NVL(a.extended_amount, 0) extended_amount,
1753 a.inventory_item_id,
1754 a.quantity_credited,
1755 a.quantity_invoiced,
1756 a.uom_code,
1757 b.sold_to_customer_id,
1758 b.bill_to_site_use_id,
1759 b.ship_to_site_use_id,
1760 b.invoice_currency_code,
1761 b.customer_trx_id,
1762 b.complete_flag,
1763 b.trx_date conv_date,
1764 a.customer_trx_line_id
1765 FROM ra_customer_trx_lines_all a,
1766 ra_customer_trx_all b
1767 WHERE a.inventory_item_id IN ( SELECT na.inventory_item_id
1768 FROM ozf_na_products_temp na)
1769 AND a.line_type = 'LINE'
1770 AND a.customer_trx_id = b.customer_trx_id
1771 AND b.complete_flag = 'Y'
1772 AND b.cust_trx_type_id = p_cust_trx_type_id
1773 AND b.trx_date BETWEEN TRUNC(p_start_date) AND TRUNC(p_end_date)
1774 AND b.org_id = p_org_id;
1775
1776
1777 -- Bug 11889816 - changed Index Hint, replaced table OZF_NA_PRODUCTS_TEMP by OZF_NA_UNIQUE_PRODUCTS_TEMP
1778 -- CURSOR c_return_line (p_order_type_id NUMBER,
1779 -- p_start_date DATE,
1780 -- p_end_date DATE) IS
1781 -- SELECT /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
1782 -- ol.header_id,
1783 -- ol.line_id,
1784 -- ol.actual_shipment_date,
1785 -- ol.fulfillment_date,
1786 -- ol.invoice_to_org_id,
1787 -- ol.ship_to_org_id,
1788 -- ol.sold_to_org_id,
1789 -- ol.inventory_item_id,
1790 -- ol.shipped_quantity,
1791 -- ol.fulfilled_quantity,
1792 -- ol.invoiced_quantity,
1793 -- ol.pricing_quantity,
1794 -- ol.pricing_quantity_uom,
1795 -- ol.unit_selling_price,
1796 -- ol.org_id,
1797 -- NVL(ol.actual_arrival_date,ol.fulfillment_date) conv_date,
1798 -- oh.transactional_curr_code
1799 -- FROM ( SELECT /*+ no_merge */ DISTINCT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP ) NA,
1800 -- oe_order_lines_all ol,
1801 -- oe_order_headers_all oh
1802 -- WHERE ol.inventory_item_id = na.inventory_item_id
1803 -- AND ol.open_flag = 'N'
1804 -- AND ol.cancelled_flag = 'N'
1805 -- AND ol.line_category_code = 'RETURN'
1806 -- AND ( NVL(ol.actual_arrival_date,ol.fulfillment_date)
1807 -- BETWEEN p_start_date AND p_end_date
1808 -- )
1809 -- AND ol.header_id = oh.header_id
1810 -- AND oh.order_type_id = p_order_type_id;
1811
1812 -- Changed cursor c_return_line to remove NVL(ol.actual_arrival_date,ol.fulfillment_date),
1813 CURSOR c_return_line (p_order_type_id NUMBER,
1814 p_start_date DATE,
1815 p_end_date DATE) IS
1816 SELECT
1817 /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
1818 OL.HEADER_ID,
1819 OL.LINE_ID,
1820 OL.INVOICE_TO_ORG_ID,
1821 OL.SHIP_TO_ORG_ID,
1822 OL.SOLD_TO_ORG_ID,
1823 OL.INVENTORY_ITEM_ID,
1824 OL.SHIPPED_QUANTITY,
1825 OL.FULFILLED_QUANTITY,
1826 OL.INVOICED_QUANTITY,
1827 OL.PRICING_QUANTITY,
1828 OL.PRICING_QUANTITY_UOM,
1829 OL.UNIT_SELLING_PRICE,
1830 OL.ORG_ID,
1831 OL.FULFILLMENT_DATE CONV_DATE,
1832 OH.TRANSACTIONAL_CURR_CODE
1833 FROM
1834 ( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP) NA,
1835 OE_ORDER_LINES_ALL OL,
1836 OE_ORDER_HEADERS_ALL OH
1837 WHERE OL.INVENTORY_ITEM_ID = NA.INVENTORY_ITEM_ID
1838 AND OL.OPEN_FLAG = 'N'
1839 AND OL.CANCELLED_FLAG = 'N'
1840 AND OL.LINE_CATEGORY_CODE = 'RETURN'
1841 AND OL.ACTUAL_ARRIVAL_DATE IS NOT NULL
1842 AND ( OL.ACTUAL_ARRIVAL_DATE BETWEEN p_start_date AND p_end_date )
1843 AND OL.HEADER_ID = OH.HEADER_ID
1844 AND OH.ORDER_TYPE_ID = p_order_type_id
1845
1846 UNION ALL
1847
1848 SELECT
1849 /*+ leading(OL) use_nl(OL NA OH) INDEX(NA OZF_NA_UNIQUE_PRODUCTS_TEMP_N1)*/
1850 OL.HEADER_ID,
1851 OL.LINE_ID,
1852 OL.INVOICE_TO_ORG_ID,
1853 OL.SHIP_TO_ORG_ID,
1854 OL.SOLD_TO_ORG_ID,
1855 OL.INVENTORY_ITEM_ID,
1856 OL.SHIPPED_QUANTITY,
1857 OL.FULFILLED_QUANTITY,
1858 OL.INVOICED_QUANTITY,
1859 OL.PRICING_QUANTITY,
1860 OL.PRICING_QUANTITY_UOM,
1861 OL.UNIT_SELLING_PRICE,
1862 OL.ORG_ID,
1863 OL.FULFILLMENT_DATE CONV_DATE,
1864 OH.TRANSACTIONAL_CURR_CODE
1865 FROM
1866 ( SELECT INVENTORY_ITEM_ID FROM OZF_NA_UNIQUE_PRODUCTS_TEMP) NA,
1867 OE_ORDER_LINES_ALL OL,
1868 OE_ORDER_HEADERS_ALL OH
1869 WHERE OL.INVENTORY_ITEM_ID = NA.INVENTORY_ITEM_ID
1870 AND OL.OPEN_FLAG = 'N'
1871 AND OL.CANCELLED_FLAG = 'N'
1872 AND OL.LINE_CATEGORY_CODE = 'RETURN'
1873 AND OL.ACTUAL_ARRIVAL_DATE IS NULL
1874 AND ( OL.FULFILLMENT_DATE BETWEEN p_start_date AND p_end_date )
1875 AND OL.HEADER_ID = OH.HEADER_ID
1876 AND OH.ORDER_TYPE_ID = p_order_type_id;
1877
1878 /* Indexes on utilization table
1879 Not Unique Index: OZF_FUNDS_UTILIZED_ALL_B_N14
1880 REFERENCE_TYPE TRX_LINE
1881 REFERENCE_ID customer_trx_line_id
1882
1883 Not Unique Index: OZF_FUNDS_UTILIZED_ALL_B_N19
1884 OBJECT_TYPE CM OR DM
1885 OBJECT_ID customer_trx_id
1886
1887 Not Unique Index: OZF_FUNDS_UTILIZED_ALL_B_N9
1888 PRODUCT_ID inventory_item_id
1889 PRODUCT_LEVEL_TYPE PRODUCT
1890 */
1891 -- nepanda : Fix for Bug 09204988
1892 /* CURSOR c_tm_lines(p_activity_media_id NUMBER,
1893 p_start_date DATE,
1894 p_end_date DATE,
1895 p_qp_list_header_id NUMBER) IS
1896 SELECT NVL(DECODE(a.gl_posted_flag, 'Y', a.plan_curr_amount), 0) line_amount,
1897 a.cust_account_id,
1898 a.adjustment_date conv_date,
1899 a.currency_code,
1900 a.org_id --Added for bug 7030415
1901 FROM ozf_funds_utilized_all_b a,
1902 ozf_offers b,
1903 ozf_na_products_temp c
1904 WHERE a.plan_type = 'OFFR'
1905 AND a.plan_id = b.qp_list_header_id
1906 AND b.qp_list_header_id <> p_qp_list_header_id
1907 AND a.adjustment_date BETWEEN p_start_date and p_end_date
1908 AND a.utilization_type IN ('ACCRUAL','ADJUSTMENT')
1909 AND b.activity_media_id = p_activity_media_id
1910 AND a.product_id = c.inventory_item_id
1911 AND a.product_level_type = 'PRODUCT';*/
1912
1913 -- nepanda : Fix for Bug 09204988
1914 -- nepanda : fix for bug 14291900 : changed the order by to product_id, product_level_type
1915 -- CURSOR c_tm_lines(p_activity_media_id NUMBER,
1916 -- p_start_date DATE,
1917 -- p_end_date DATE,
1918 -- p_qp_list_header_id NUMBER) IS
1919 -- SELECT utilization_id,
1920 -- NVL(DECODE(a.gl_posted_flag, 'Y', a.plan_curr_amount), 0) line_amount,
1921 -- a.cust_account_id,
1922 -- a.adjustment_date conv_date,
1923 -- a.currency_code,
1924 -- a.object_type,
1925 -- a.reference_type,
1926 -- a.reference_id,
1927 -- a.org_id --Added for bug 7030415
1928 -- FROM ozf_funds_utilized_all_b a,
1929 -- ozf_offers b,
1930 -- ozf_na_products_temp c
1931 -- WHERE a.plan_type = 'OFFR'
1932 -- AND a.plan_id = b.qp_list_header_id
1933 -- AND b.qp_list_header_id <> p_qp_list_header_id
1934 -- AND a.adjustment_date BETWEEN p_start_date and p_end_date
1935 -- AND a.utilization_type IN ('ACCRUAL','ADJUSTMENT')
1936 -- AND b.activity_media_id = p_activity_media_id
1937 -- AND a.product_id = c.inventory_item_id
1938 -- AND a.product_level_type = 'PRODUCT'
1939 -- ORDER BY a.order_line_id; -- added for bug # 10379136
1940 -- Catch Weight ER - start
1941 CURSOR c_tm_lines(p_activity_media_id NUMBER,
1942 p_start_date DATE,
1943 p_end_date DATE,
1944 p_qp_list_header_id NUMBER) IS
1945 SELECT a.order_line_id,
1946 --SUM(NVL(DECODE(a.gl_posted_flag, 'Y', a.plan_curr_amount), 0)) line_amount,
1947 NVL(DECODE(a.gl_posted_flag, 'Y', a.plan_curr_amount), 0) line_amount, -- Fix for Bug 16301672
1948 a.cust_account_id,
1949 a.adjustment_date conv_date,
1950 a.currency_code,
1951 a.object_type,
1952 a.reference_type,
1953 a.reference_id,
1954 a.org_id --Added for bug 7030415
1955 FROM ozf_funds_utilized_all_b a,
1956 ozf_offers b,
1957 ozf_na_products_temp c
1958 WHERE a.plan_type = 'OFFR'
1959 AND a.plan_id = b.qp_list_header_id
1960 AND b.qp_list_header_id <> p_qp_list_header_id
1961 AND a.adjustment_date BETWEEN p_start_date and p_end_date
1962 AND a.utilization_type IN ('ACCRUAL','ADJUSTMENT')
1963 AND b.activity_media_id = p_activity_media_id
1964 AND a.product_id = c.inventory_item_id
1965 AND a.product_level_type = 'PRODUCT'
1966 ORDER BY a.product_id, a.product_level_type, a.order_line_id; -- Fix for Bug 16301672
1967 /*GROUP BY a.order_line_id,
1968 a.cust_account_id,
1969 a.adjustment_date,
1970 a.currency_code,
1971 a.object_type,
1972 a.reference_type,
1973 a.reference_id,
1974 a.org_id;*/
1975 -- Catch Weight ER - end
1976
1977 CURSOR c_get_order_details(p_order_line_id NUMBER) IS
1978 SELECT o.inventory_item_id inventory_item_id,
1979 NVL(o.shipping_quantity, o.pricing_quantity) pricing_quantity, -- Catch Weight ER
1980 o.pricing_quantity_uom pricing_quantity_uom,
1981 o.line_id order_line_id,
1982 o.org_id,
1983 o.shipping_quantity, -- Catch Weight ER
1984 o.shipping_quantity_uom, -- Catch Weight ER
1985 o.shipping_quantity2, -- Catch Weight ER
1986 o.shipping_quantity_uom2, -- Catch Weight ER
1987 o.fulfillment_base -- Catch Weight ER
1988 FROM oe_order_lines_all o
1989 WHERE o.line_id = p_order_line_id;
1990
1991 CURSOR c_get_credit_memo_details(p_cust_trx_line_id NUMBER) IS
1992 SELECT inventory_item_id,
1993 quantity_credited,
1994 uom_code,
1995 org_id -- added for bug # 10379136
1996 FROM ra_customer_trx_lines_all
1997 WHERE customer_trx_line_id = p_cust_trx_line_id;
1998
1999 CURSOR c_get_debit_memo_details(p_cust_trx_line_id NUMBER) IS
2000 SELECT inventory_item_id,
2001 quantity_invoiced,
2002 uom_code,
2003 org_id -- added for bug # 10379136
2004 FROM ra_customer_trx_lines_all
2005 WHERE customer_trx_line_id = p_cust_trx_line_id;
2006
2007
2008 CURSOR c_get_util_amt(p_customer_trx_line_id NUMBER,
2009 p_inventory_item_id NUMBER,
2010 p_qp_list_header_id NUMBER) IS
2011 SELECT NVL(SUM(plan_curr_amount),0)
2012 FROM ozf_funds_utilized_all_b
2013 WHERE reference_type = 'TRX_LINE'
2014 AND reference_id = p_customer_trx_line_id
2015 AND product_id = p_inventory_item_id
2016 AND product_level_type = 'PRODUCT'
2017 AND plan_type = 'OFFR'
2018 AND plan_id = p_qp_list_header_id;
2019
2020 -- Added for bug 7030415
2021 CURSOR c_get_conversion_type( p_org_id IN NUMBER) IS
2022 SELECT exchange_rate_type
2023 FROM ozf_sys_parameters_all
2024 WHERE org_id = p_org_id;
2025
2026 --ninarasi fix for bug 14798341
2027 CURSOR c_gl_period(p_org_id IN NUMBER,
2028 p_gl_date IN DATE) IS
2029 SELECT 1
2030 FROM gl_period_statuses gl, ozf_sys_parameters_all sp
2031 WHERE gl.application_id = 101
2032 AND sp.org_id = p_org_id
2033 AND sp.set_of_books_id = gl.set_of_books_id
2034 AND gl.adjustment_period_flag = 'N'
2035 AND p_gl_date BETWEEN gl.start_date AND gl.end_date
2036 AND gl.closing_status IN ('O', 'F');
2037 --ninarasi fix for bug 15991204
2038 --Added this cursor to fetch the cust_account_id to be populated into billto_cust_account_id column
2039 CURSOR c_cust_number (p_invoice_to_org_id IN NUMBER) IS
2040 SELECT cust.cust_account_id
2041 FROM hz_cust_acct_sites_all acct_site,
2042 hz_cust_site_uses_all site_use,
2043 hz_cust_accounts cust
2044 WHERE acct_site.cust_acct_site_id = site_use.cust_acct_site_id
2045 AND acct_site.cust_account_id = cust.cust_account_id
2046 AND site_use.site_use_id = p_invoice_to_org_id ;
2047
2048 l_exchange_rate_type VARCHAR2(30) := FND_API.G_MISS_CHAR;
2049 l_order_line_tbl t_order_line_tbl;
2050 l_ar_trx_line_tbl t_ar_trx_line_tbl;
2051 l_return_line_tbl t_return_line_tbl; -- catch Weight ER
2052 -- Bug 11889816
2053 l_idsm_line_tbl t_idsm_line_tbl ; --t_order_line_tbl;
2054 l_batch_size NUMBER := 1000;
2055
2056 l_return_status VARCHAR2(1);
2057 l_msg_count NUMBER;
2058 l_msg_data VARCHAR2(2000);
2059
2060 l_latest_comp_date DATE;
2061 l_as_of_date DATE;
2062 l_ar_start_date DATE;
2063 l_start_date DATE;
2064 l_end_date DATE;
2065 l_sysdate DATE;
2066
2067 l_customer_qualified VARCHAR2(1);
2068 l_product_qualified VARCHAR2(1);
2069
2070 l_line_amount NUMBER;
2071 l_line_acc_amount NUMBER;
2072
2073 l_accrual_amount NUMBER;
2074 l_existing_util_amt NUMBER;
2075 l_ar_dedu_line_amt NUMBER;
2076 l_ar_dedu_amount NUMBER;
2077
2078 l_om_dedu_line_amt NUMBER;
2079 l_om_dedu_amount NUMBER;
2080
2081 l_tm_dedu_line_amt NUMBER;
2082 l_tm_dedu_amount NUMBER;
2083
2084 l_batch_mode VARCHAR2(10);
2085 l_orig_batch_mode VARCHAR2(10);
2086 l_order_curr_code VARCHAR2(30);
2087 l_org_id NUMBER; -- Inventory Org
2088 l_offer_org_id NUMBER; -- Org in Which the offer was created
2089 l_is_prm_offer BOOLEAN; -- nepanda : bug # 14277687
2090
2091 l_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type;
2092 l_act_util_rec ozf_actbudgets_pvt.act_util_rec_type;
2093 l_act_budget_id NUMBER;
2094 l_referral_id NUMBER;
2095 l_beneficiary_id NUMBER;
2096 l_utilization_type VARCHAR2(30);
2097 l_reference_type VARCHAR2(30);
2098 l_sign NUMBER;
2099 l_quantity NUMBER;
2100 l_utilized_amount NUMBER := 0;
2101
2102
2103 l_rate NUMBER;
2104 --nirprasa,12.2
2105 l_new_line_acc_amount NUMBER;
2106 l_new_existing_util_amt NUMBER;
2107 l_new_ar_dedu_line_amt NUMBER;
2108 l_new_ar_dedu_amount NUMBER;
2109
2110 -- nepanda : Fix for Bug 09204988
2111 l_order_line_id NUMBER;
2112
2113 -- Fix for Bug 16301672 start
2114 l_order_line_id_old NUMBER;
2115 l_line_acc_amt_old NUMBER;
2116 l_reduce_line_amount BOOLEAN;
2117 l_tm_dedu_line_amt_old NUMBER;
2118 l_line_amount_old NUMBER;
2119 -- Fix for Bug 16301672 end
2120
2121 l_inventory_item_id_old NUMBER;
2122 l_index NUMBER;
2123 l_parent_src_apprvd_amt NUMBER;
2124 l_inventory_item_id NUMBER;
2125 l_pricing_quantity NUMBER;
2126 l_pricing_quantity_uom VARCHAR2(10);
2127 l_order_org_id NUMBER;
2128
2129 -- Bug 11889816
2130 l_tot_products NUMBER;
2131 l_tot_customers NUMBER;
2132
2133 TYPE act_budgets_rec_tbl_type IS TABLE OF ozf_actbudgets_pvt.act_budgets_rec_type INDEX BY BINARY_INTEGER;
2134 l_act_budgets_rec_tbl act_budgets_rec_tbl_type;
2135
2136 TYPE act_util_rec_tbl_type IS TABLE OF ozf_actbudgets_pvt.act_util_rec_type INDEX BY BINARY_INTEGER;
2137 l_act_util_rec_tbl act_util_rec_tbl_type;
2138
2139 -- Used to Validate country code for PRM Net Accrual Offer
2140
2141 CURSOR c_terr_countries ( p_offer_id IN NUMBER) IS
2142 SELECT terr_val.low_value_char
2143 FROM ozf_offer_qualifiers offer_qual,
2144 jtf_terr_qual_all terr_qual,
2145 jtf_terr_values_all terr_val
2146 WHERE offer_qual.offer_id = p_offer_id
2147 AND offer_qual.qualifier_attr_value = terr_qual.terr_id
2148 AND terr_qual.qual_usg_id = -1065 -- Pick Country Qualifier only
2149 AND terr_qual.terr_qual_id = terr_val.terr_qual_id;
2150
2151 l_terr_countries_tbl terr_countries_tbl;
2152
2153 CURSOR c_country_code(p_site_use_id NUMBER) IS
2154 SELECT hzloc.country
2155 FROM hz_cust_site_uses_all hzcsua,
2156 hz_cust_acct_sites_all hzcasa,
2157 hz_locations hzloc,
2158 hz_party_sites hzps
2159 WHERE hzcsua.cust_acct_site_id = hzcasa.cust_acct_site_id
2160 AND hzcasa.party_site_id = hzps.party_site_id
2161 AND hzps.location_id = hzloc.location_id
2162 AND hzcsua.status = 'A'
2163 AND hzcsua.site_use_id = p_site_use_id;
2164
2165 -- Catch Weight ER - start
2166 CURSOR c_offer_uom_code(l_offer_id NUMBER) IS
2167 SELECT uom_code
2168 FROM ozf_offer_discount_lines
2169 WHERE offer_id = l_offer_id;
2170
2171 l_offer_uom_code VARCHAR2(10);
2172 l_ship_quantity NUMBER;
2173 l_cw_quantity NUMBER;
2174 l_cw_quantity_uom VARCHAR2(10);
2175 l_shipping_quantity NUMBER;
2176 l_shipping_quantity_uom VARCHAR2(10);
2177 l_shipping_quantity2 NUMBER;
2178 l_shipping_quantity_uom2 VARCHAR2(10);
2179 l_fulfillment_base VARCHAR2(1);
2180 -- Catch Weight ER - end
2181
2182 l_country_code VARCHAR2(60);
2183 l_new_amount NUMBER;
2184 l_date_from_input DATE;
2185 l_idsm_line_processed NUMBER := 0;
2186 --
2187
2188 --bug 7577311
2189 l_status VARCHAR2(5);
2190 l_industry VARCHAR2(5);
2191 l_schema VARCHAR2(30);
2192 l_return BOOLEAN;
2193 l_gldate DATE; --ninarasi fix for bug 14798341
2194 l_gl_date_count NUMBER;
2195 l_tm_deduction_org_id NUMBER; --14798341
2196 l_counter NUMBER := 0;
2197
2198 BEGIN
2199 -- Standard Start of API savepoint
2200 --SAVEPOINT net_accrual_engine;
2201
2202 RETCODE := '0';
2203
2204 --bug 7577311 - get schema name
2205 l_return := fnd_installation.get_app_info('OZF', l_status, l_industry, l_schema);
2206
2207 l_sysdate := TRUNC(SYSDATE); --nepanda : fix for bug 8766564 .added variable for sysdate and initialized it to the date the net accrual engine has started.
2208
2209 -- initialize multi org
2210 MO_GLOBAL.init('OZF');
2211 MO_GLOBAL.set_policy_context('M',null);
2212
2213 ozf_utility_pvt.write_conc_log('-- Start Processing : ' || to_char(sysdate,'MM/DD/YYYY:HH:MM:SS'));
2214
2215 ozf_utility_pvt.write_conc_log('-- Process_Exceptions (+) ');
2216 --
2217 process_exceptions();
2218 --
2219 ozf_utility_pvt.write_conc_log('-- Process_Exceptions (-) ');
2220 --
2221 l_date_from_input := TRUNC(TO_DATE(p_as_of_date, 'YYYY/MM/DD HH24:MI:SS'));
2222 ozf_utility_pvt.write_conc_log('-- Date Converted : ' || l_date_from_input);
2223
2224 IF (l_date_from_input IS NULL)
2225 OR
2226 (TRUNC(l_date_from_input) >= TRUNC(SYSDATE))
2227 THEN
2228 l_as_of_date := SYSDATE;
2229 ELSE
2230 -- Set end time to 23:59:59 of the day.
2231 l_as_of_date := TRUNC(l_date_from_input + 1) - 1/86400;
2232 END IF;
2233
2234 l_orig_batch_mode := fnd_profile.value('OZF_PROCESS_NA_BATCH_MODE');
2235 l_org_id := fnd_profile.value('QP_ORGANIZATION_ID');
2236
2237 --ninarasi fix for bug 14798341 - if end date is not passed then use sysdate as gl date
2238 l_gldate := NVL(l_date_from_input, TRUNC(SYSDATE));
2239
2240 ozf_utility_pvt.write_conc_log('gl date: ' || l_gldate);
2241
2242 IF l_orig_batch_mode IS NULL
2243 THEN
2244 l_orig_batch_mode := 'NO';
2245 END IF;
2246
2247 ozf_utility_pvt.write_conc_log('OZF: Process Net Accrual In Batch Mode: '||l_orig_batch_mode);
2248 ozf_utility_pvt.write_conc_log('QP: Item Validation Organization: '||l_org_id);
2249
2250 ozf_utility_pvt.write_conc_log('-- Start Processing Net Accrual Offers (+) ');
2251
2252 ----------------------------------------------------
2253 FOR l_net_accrual_offers IN c_net_accrual_offers
2254 LOOP
2255
2256 --AMITAMKU bug 14692296 - Moved truncate before SAVEPOINT net_accrual_offer to avoid AUTO-COMMIT, in case of failure of a net accrual offer
2257 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.ozf_na_customers_temp';
2258 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.ozf_na_products_temp';
2259 -- Bug 11889816 - Added TRUNCATE for OZF_NA_UNIQUE_PRODUCTS_TEMP
2260 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.OZF_NA_UNIQUE_PRODUCTS_TEMP';
2261
2262
2263 --AMITAMKU bug 14692296 - added BEGIN/EXCEPTION/END block for each net accrual offer so that program can continue for next offer if one offer fails
2264 BEGIN
2265 SAVEPOINT net_accrual_offer;
2266 --
2267 l_return_status := FND_API.g_ret_sts_success;
2268
2269 ozf_utility_pvt.write_conc_log('-----------------------------------------');
2270 ozf_utility_pvt.write_conc_log('--');
2271 ozf_utility_pvt.write_conc_log('-------- Processing Offer: '|| l_net_accrual_offers.offer_name);
2272 ozf_utility_pvt.write_conc_log(' Offer_Id / List_Header_Id / Custom_Setup_Id / Orig_Org_Id: '
2273 || l_net_accrual_offers.offer_id || ' / '
2274 || l_net_accrual_offers.qp_list_header_id || ' / '
2275 || l_net_accrual_offers.custom_setup_id || ' / '
2276 || l_net_accrual_offers.orig_org_id );
2277 ozf_utility_pvt.write_conc_log('--');
2278
2279 -------- Derive Program Start and End Date Range ----------
2280 l_latest_comp_date := l_net_accrual_offers.latest_na_completion_date;
2281 l_start_date := l_net_accrual_offers.start_date_active;
2282 l_end_date := l_net_accrual_offers.end_date_active;
2283 l_idsm_line_processed := l_net_accrual_offers.resale_line_id_processed;
2284
2285 IF l_latest_comp_date IS NOT NULL
2286 THEN
2287 l_start_date := l_latest_comp_date;
2288 END IF;
2289
2290 IF l_end_date IS NULL OR l_end_date > l_as_of_date
2291 THEN
2292 l_end_date := l_as_of_date;
2293 END IF;
2294
2295 ozf_utility_pvt.write_conc_log('Accrual Start Period: '||to_char(l_start_date,'MM/DD/YY HH:MI:SS AM'));
2296 ozf_utility_pvt.write_conc_log('Accrual End Date: ' ||to_char(l_end_date,'MM/DD/YY HH:MI:SS AM'));
2297 ozf_utility_pvt.write_conc_log('Resale Line Processed: ' || l_idsm_line_processed);
2298
2299 IF l_start_date > l_end_date
2300 THEN
2301 -- This offer has been completely processed. Skip OM, Continue Process IDSM.
2302 ozf_utility_pvt.write_conc_log('This Offer has been completely processed for OM. Skipping to IDSM. ');
2303 GOTO IDSM;
2304 END IF;
2305 --------------------------------------------------------------
2306
2307 IF l_net_accrual_offers.custom_setup_id = 105
2308 THEN
2309 -- The batch mode profile does not apply for PRM offers
2310 l_batch_mode := 'NO';
2311 l_offer_org_id := NULL;
2312 l_is_prm_offer := TRUE ; -- nepanda : bug # 14277687
2313 ELSE
2314 l_batch_mode := l_orig_batch_mode;
2315 l_offer_org_id := l_net_accrual_offers.orig_org_id;
2316 l_is_prm_offer := FALSE ; -- nepanda : bug # 14277687
2317 END IF;
2318
2319 ----------------- Denrom Customers ------------------
2320 IF l_net_accrual_offers.custom_setup_id = 105
2321 THEN
2322 --
2323 -- For PRM Offers, populate local table with all qualifying countries
2324 -- once for each offer in a local PL/SQL table
2325 -- No need to use LIMIT clause since # of countries will be limited for a terr
2326 --
2327 l_terr_countries_tbl.delete;
2328
2329 OPEN c_terr_countries(l_net_accrual_offers.offer_id);
2330 FETCH c_terr_countries BULK COLLECT INTO l_terr_countries_tbl;
2331 CLOSE c_terr_countries;
2332
2333 IF l_terr_countries_tbl.FIRST IS NULL
2334 THEN
2335 -- No countries defined for a PRM Offer
2336 -- No point processing this offer. Skip it offer
2337 -- If implementation is correct, this will never happen
2338 ozf_utility_pvt.write_conc_log('-- No country qualifiers provided for PRM Offer. Not Processing it ..');
2339 GOTO NEXT_OFFER;
2340 END IF;
2341 --
2342 IF G_DEBUG_LOW
2343 THEN
2344 --
2345 FOR c IN l_terr_countries_tbl.FIRST..l_terr_countries_tbl.LAST
2346 LOOP
2347 ozf_utility_pvt.write_conc_log('Country Code: '|| l_terr_countries_tbl(c) );
2348 END LOOP;
2349 --
2350 END IF;
2351 --
2352 ELSE
2353 --
2354 -- For all other Offers, populate the ozf_na_customers_temp denom table
2355 --
2356
2357 ozf_utility_pvt.write_conc_log('Populate_Customers (+)');
2358
2359 populate_customers(l_net_accrual_offers.offer_id
2360 ,l_return_status
2361 ,l_msg_count
2362 ,l_msg_data);
2363
2364 ozf_utility_pvt.write_conc_log('Populate_Customers (-) With Status: ' ||l_return_status);
2365
2366 IF l_return_status = Fnd_Api.g_ret_sts_error
2367 THEN
2368 RAISE Fnd_Api.g_exc_error;
2369 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
2370 THEN
2371 RAISE Fnd_Api.g_exc_unexpected_error;
2372 END IF;
2373 --
2374 END IF;
2375 ------------------------------------------------------
2376
2377 --------------- Denorm Products ----------------------
2378 IF l_net_accrual_offers.tier_level = 'LINE'
2379 THEN
2380 --
2381 ozf_utility_pvt.write_conc_log('Populate_Prod_Line (+)');
2382
2383 populate_prod_line(l_net_accrual_offers.offer_id
2384 ,l_return_status
2385 ,l_msg_count
2386 ,l_msg_data);
2387
2388 ozf_utility_pvt.write_conc_log('Populate_Prod_Line (-) With Status: '||l_return_status);
2389 --
2390 ELSIF l_net_accrual_offers.tier_level = 'HEADER'
2391 THEN
2392 --
2393 ozf_utility_pvt.write_conc_log('Populate_Prod_Tier (+)');
2394
2395 populate_prod_tier(l_net_accrual_offers.offer_id
2396 ,l_return_status
2397 ,l_msg_count
2398 ,l_msg_data);
2399
2400 ozf_utility_pvt.write_conc_log('Populate_Prod_Tier (-) With Status: '||l_return_status);
2401 --
2402 END IF;
2403 ---------------------------------------------------------
2404
2405 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
2406 RAISE Fnd_Api.g_exc_error;
2407 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
2408 RAISE Fnd_Api.g_exc_unexpected_error;
2409 END IF;
2410
2411 --Bug 11889816 : start
2412 -- Insert distinct products to OZF_NA_UNIQUE_PRODUCTS_TEMP table to be used in Order cursors
2413 INSERT INTO OZF_NA_UNIQUE_PRODUCTS_TEMP(inventory_item_id)
2414 SELECT DISTINCT inventory_item_id FROM ozf_na_products_temp;
2415
2416 SELECT count(*) INTO l_tot_products
2417 FROM OZF_NA_UNIQUE_PRODUCTS_TEMP;
2418
2419 ozf_utility_pvt.write_conc_log('Total Number of Unique Products: '||l_tot_products);
2420
2421 SELECT count(*) INTO l_tot_customers
2422 FROM ozf_na_customers_temp;
2423
2424 ozf_utility_pvt.write_conc_log('Total Number of Customers: '||l_tot_customers);
2425
2426 --Bug 11889816 : end
2427
2428 -- Catch Weight ER - start
2429 ozf_utility_pvt.write_conc_log('Catch Weight ER b4 c_offer_uom_code l_net_accrual_offers.offer_id : '||l_net_accrual_offers.offer_id);
2430 OPEN c_offer_uom_code(l_net_accrual_offers.offer_id);
2431 FETCH c_offer_uom_code INTO l_offer_uom_code;
2432 CLOSE c_offer_uom_code;
2433 ozf_utility_pvt.write_conc_log('Catch Weight ER l_offer_uom_code : '||l_offer_uom_code);
2434 -- Catch Weight ER - end
2435 --------------- Start Processing Orders ------------------------
2436 ozf_utility_pvt.write_conc_log('-- Start Processing Orders -- ');
2437
2438 --------------- Start Processing OM lines ------------------------
2439 IF l_net_accrual_offers.sales_method_flag IS NULL OR l_net_accrual_offers.sales_method_flag = 'D' THEN
2440 --
2441 ozf_utility_pvt.write_conc_log('Processing OM lines');
2442 ozf_utility_pvt.write_conc_log('l_start_date '|| l_start_date);
2443 ozf_utility_pvt.write_conc_log('l_end_date '|| l_end_date);
2444 ozf_utility_pvt.write_conc_log('l_offer_org_id '|| l_offer_org_id);
2445 l_order_line_tbl.delete;
2446 l_accrual_amount := 0;
2447
2448 --Bugfix: 11889816 - Added l_offer_org_id check
2449 IF l_offer_org_id IS NULL THEN
2450 -- nepanda : bug # 14277687
2451 IF l_is_prm_offer THEN
2452 OPEN c_order_line_prm(l_start_date, l_end_date);
2453 ELSE
2454 OPEN c_order_line_global(l_start_date, l_end_date);
2455 END IF;
2456 ELSE
2457 OPEN c_order_line(l_start_date, l_end_date, l_offer_org_id);
2458 END IF;
2459
2460 LOOP
2461 -- nepanda - Bug 13376173 : start
2462 IF l_offer_org_id IS NULL THEN
2463 IF l_is_prm_offer THEN
2464 FETCH c_order_line_prm BULK COLLECT INTO l_order_line_tbl LIMIT l_batch_size;
2465 ELSE
2466 FETCH c_order_line_global BULK COLLECT INTO l_order_line_tbl LIMIT l_batch_size; -- nepanda : bug # 14277687
2467 END IF;
2468 ELSE
2469 FETCH c_order_line BULK COLLECT INTO l_order_line_tbl LIMIT l_batch_size;
2470 END IF; -- nepanda - Bug 13376173 - end
2471 --
2472 -- To handle NO DATA FOUND for c_order_line CURSOR
2473 IF l_order_line_tbl.FIRST IS NULL
2474 THEN
2475 --
2476 ozf_utility_pvt.write_conc_log('No Data found in c_order_line CURSOR');
2477 EXIT;
2478 --
2479 END IF;
2480 --
2481 -- Logic to exit after all the record have been processed
2482 -- is just before the END LOOP EXIT WHEN c_order_line%NOTFOUND;
2483
2484 ---------------------------------------------------------
2485 FOR i IN l_order_line_tbl.FIRST .. l_order_line_tbl.LAST
2486 LOOP
2487 ---------------------------------------------------------
2488
2489 --ninarasi fix for bug 14798341
2490 l_gl_date_count := 0;
2491 OPEN c_gl_period(l_order_line_tbl(i).org_id, l_gldate);
2492 FETCH c_gl_period INTO l_gl_date_count;
2493 CLOSE c_gl_period;
2494
2495 IF l_gl_date_count = 0 THEN
2496 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
2497 FND_MESSAGE.set_name('OZF', 'OZF_SETL_GLDATE_INVALID');
2498 FND_MSG_PUB.add;
2499 END IF;
2500 ozf_utility_pvt.write_conc_log;
2501 RAISE FND_API.G_EXC_ERROR;
2502 END IF;
2503
2504 l_return_status := FND_API.g_ret_sts_success;
2505
2506 IF G_DEBUG_LOW THEN
2507 ozf_utility_pvt.write_conc_log('Order Line_Id: '||l_order_line_tbl(i).line_id);
2508 END IF;
2509
2510 l_line_amount := ( NVL(l_order_line_tbl(i).shipped_quantity,l_order_line_tbl(i).fulfilled_quantity)
2511 * l_order_line_tbl(i).unit_selling_price );
2512 --
2513 ------------- Qualify Customer on the Order line ------------------------------
2514 --
2515
2516 IF l_net_accrual_offers.custom_setup_id = 105
2517 THEN
2518 ----- For PV Net Accrual Offers, do not look at denorm -------
2519 ----- Get Country code from the Identifying addresss of the Customer
2520 OPEN c_country_code(l_order_line_tbl(i).invoice_to_org_id);
2521 FETCH c_country_code INTO l_country_code;
2522 CLOSE c_country_code;
2523
2524 -- l_terr_countries_tbl has all the countries eligible for this offer
2525 -- This table is populated in the 'Denorm Customers' section for each PV NA Offer
2526 l_customer_qualified := 'N';
2527
2528 FOR j IN l_terr_countries_tbl.FIRST .. l_terr_countries_tbl.LAST
2529 LOOP
2530 --
2531 IF l_country_code = l_terr_countries_tbl(j)
2532 THEN
2533 l_customer_qualified := 'Y';
2534 EXIT;
2535 END IF;
2536 --
2537 END LOOP;
2538
2539 IF l_customer_qualified = 'N' THEN
2540 -- sold_to not qualified. try ship_to
2541 OPEN c_country_code(l_order_line_tbl(i).ship_to_org_id);
2542 FETCH c_country_code INTO l_country_code;
2543 CLOSE c_country_code;
2544
2545 FOR j IN l_terr_countries_tbl.FIRST .. l_terr_countries_tbl.LAST
2546 LOOP
2547 --
2548 IF l_country_code = l_terr_countries_tbl(j)
2549 THEN
2550 l_customer_qualified := 'Y';
2551 EXIT;
2552 END IF;
2553 --
2554 END LOOP;
2555 --
2556 END IF;
2557 --
2558 ELSE
2559 ----- For all other Net Accrual offers, look at denorm -------
2560 l_customer_qualified := validate_customer(p_invoice_to_org_id => l_order_line_tbl(i).invoice_to_org_id
2561 ,p_ship_to_org_id => l_order_line_tbl(i).ship_to_org_id
2562 ,p_sold_to_org_id => l_order_line_tbl(i).sold_to_org_id);
2563 --
2564 END IF; -- Done qualfiying the customer
2565
2566 IF G_DEBUG_LOW THEN
2567 ozf_utility_pvt.write_conc_log('Did Customer qualify: '||l_customer_qualified);
2568 END IF;
2569
2570 -- Fetch Currency Code on the Order
2571 l_order_curr_code := l_order_line_tbl(i).transactional_curr_code ;
2572 ozf_utility_pvt.write_conc_log('l_order_curr_code: '|| l_order_curr_code);
2573 ozf_utility_pvt.write_conc_log('l_net_accrual_offers.fund_request_curr_code: '|| l_net_accrual_offers.fund_request_curr_code);
2574
2575 IF l_customer_qualified = 'Y'
2576 THEN
2577 --
2578 IF l_net_accrual_offers.fund_request_curr_code <> l_order_curr_code
2579 THEN
2580 --
2581 l_new_amount := 0;
2582
2583 --Added for bug 7030415
2584 ozf_utility_pvt.write_conc_log('l_order_line_tbl(i).org_id: '|| l_order_line_tbl(i).org_id);
2585 IF l_batch_mode = 'NO' THEN
2586 OPEN c_get_conversion_type(l_order_line_tbl(i).org_id);
2587 FETCH c_get_conversion_type INTO l_exchange_rate_type;
2588 CLOSE c_get_conversion_type;
2589 ozf_utility_pvt.write_conc_log('l_exchange_rate_type: '|| l_exchange_rate_type);
2590 ozf_utility_pvt.write_conc_log('l_line_amount: '|| l_line_amount);
2591 END IF;
2592
2593 ozf_utility_pvt.convert_currency(x_return_status => l_return_status
2594 ,p_from_currency => l_order_curr_code
2595 ,p_to_currency => l_net_accrual_offers.fund_request_curr_code
2596 ,p_conv_type => l_exchange_rate_type
2597 --,p_conv_date => l_order_line_tbl(i).conv_date
2598 ,p_conv_date => sysdate
2599 ,p_from_amount => l_line_amount
2600 ,x_to_amount => l_new_amount
2601 ,x_rate => l_rate);
2602 --nirprasa,12.2 nirprasa ER 8399135. Use the amount in order currency for batch mode NO
2603 --Converted amount will be used later for batch mode YES or Arrow's case when
2604 --offer and order currencies are different.
2605 IF l_batch_mode = 'YES' OR l_net_accrual_offers.transaction_currency_code IS NOT NULL THEN
2606 l_line_amount := l_new_amount;
2607 END IF;
2608 ozf_utility_pvt.write_conc_log('l_line_amount converted : '|| l_line_amount);
2609
2610 IF l_return_status = Fnd_Api.g_ret_sts_error
2611 THEN
2612 ozf_utility_pvt.write_conc_log('Exp Error from Convert_Currency: ' || l_return_status);
2613 RAISE Fnd_Api.g_exc_error;
2614 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
2615 THEN
2616 ozf_utility_pvt.write_conc_log('Unexp Error from Convert_Currency: ' || l_return_status);
2617 RAISE Fnd_Api.g_exc_unexpected_error;
2618 END IF;
2619 --
2620 END IF;
2621
2622 ------------------------------ Derive Benificiary -----------------------
2623 IF l_net_accrual_offers.custom_setup_id = 105
2624 THEN
2625 --
2626 IF G_DEBUG_LOW THEN
2627 ozf_utility_pvt.write_conc_log('Pv_Referral_Comp_Pub.Get_Beneficiary (+)');
2628 END IF;
2629 pv_referral_comp_pub.get_beneficiary (p_api_version => 1.0,
2630 p_init_msg_list => FND_API.g_true,
2631 p_commit => FND_API.g_false,
2632 p_validation_level => FND_API.g_valid_level_full,
2633 p_order_header_id => l_order_line_tbl(i).header_id,
2634 p_order_line_id => l_order_line_tbl(i).line_id,
2635 p_offer_id => l_net_accrual_offers.offer_id,
2636 x_beneficiary_id => l_beneficiary_id,
2637 x_referral_id => l_referral_id,
2638 x_return_status => l_return_status,
2639 x_msg_count => l_msg_count,
2640 x_msg_data => l_msg_data);
2641 IF G_DEBUG_LOW THEN
2642 ozf_utility_pvt.write_conc_log('Pv_Referral_Comp_Pub.Get_Beneficiary (-) With Status: '||l_return_status);
2643 ozf_utility_pvt.write_conc_log('l_benificiary_id / l_referral_id: '||l_beneficiary_id || ' / ' || l_referral_id);
2644 END IF;
2645
2646 IF l_return_status = Fnd_Api.g_ret_sts_error
2647 THEN
2648 ozf_utility_pvt.write_conc_log('Exp Error from Get_Beneficiary: ' || l_return_status);
2649 RAISE Fnd_Api.g_exc_error;
2650 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
2651 THEN
2652 ozf_utility_pvt.write_conc_log('Unexp Error from Get_Beneficiary: ' || l_return_status);
2653 RAISE Fnd_Api.g_exc_unexpected_error;
2654 END IF;
2655 --
2656
2657 IF ( l_beneficiary_id IS NOT NULL )
2658 THEN
2659 --------------------------- Derive Accrual Amount -------------------------
2660 IF G_DEBUG_LOW THEN
2661 ozf_utility_pvt.write_conc_log('Get_Pv_Accrual_Amount (+)');
2662 END IF;
2663
2664 l_line_acc_amount := get_pv_accrual_amount(p_product_id => l_order_line_tbl(i).inventory_item_id
2665 ,p_line_amt => l_line_amount
2666 ,p_offer_id => l_net_accrual_offers.offer_id
2667 ,p_org_id => l_org_id
2668 ,p_list_hdr_id => l_net_accrual_offers.qp_list_header_id
2669 ,p_referral_id => l_referral_id
2670 ,p_order_hdr_id => l_order_line_tbl(i).header_id);
2671 IF G_DEBUG_LOW THEN
2672 ozf_utility_pvt.write_conc_log('Get_Pv_Accrual_Amount (-) With l_line_acc_amount: '|| l_line_acc_amount);
2673 END IF;
2674 --
2675 ELSE
2676 --
2677 ozf_utility_pvt.write_conc_log('No Beneficiary derived from PV_Referral_Comp_Pub. Utilization will not be created');
2678 --
2679 END IF;
2680 --
2681 l_utilization_type := 'LEAD_ACCRUAL';
2682 l_reference_type := 'LEAD_REFERRAL';
2683 --
2684 ELSE
2685 --
2686 --------------------------- Derive Accrual Amount -------------------------
2687 -- IF G_DEBUG_LOW THEN
2688 ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (+)');
2689 ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).inventory_item_id = '|| l_order_line_tbl(i).inventory_item_id);
2690 ozf_utility_pvt.write_conc_log('Catch Weight - l_line_amount = '|| l_line_amount);
2691 ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).pricing_quantity = '|| l_order_line_tbl(i).pricing_quantity);
2692 ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).pricing_quantity_uom = '|| l_order_line_tbl(i).pricing_quantity_uom);
2693 --ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).shipping_quantity = '|| l_order_line_tbl(i).shipping_quantity);
2694 --ozf_utility_pvt.write_conc_log('Catch Weight - l_order_line_tbl(i).shipping_quantity_uom = '|| l_order_line_tbl(i).shipping_quantity_uom);
2695 ozf_utility_pvt.write_conc_log('Catch Weight - l_offer_uom_code = '|| l_offer_uom_code);
2696 -- END IF;
2697 --[ Catch Weight Example
2698 -- Order Booked For 3 Case = 36 Ea = 360 Pounds
2699 -- Shipped with Catch Weight = 320 Pounds
2700 -- Net Accrual Offers For Order Lines : Created utilizations similar to Accrual offer for the orders booked between start_date and end_date of Net Accrual offer
2701 -- NE_CW_NETACC5 -- Discount - 10% : UOM - CS -- Accrual = 10% * line_amount = 10% * 266.67 = 26.67
2702 -- NE_CW_NETACC6 -- Discount - 10% : UOM - Pounds -- Accrual = 10% * line_amount = 10% * 266.67 = 26.67
2703 -- NE_CW_NETACC7 -- Discount - 10 AMT : UOM - CS -- Accrual = 10 AMT per Case = 10 * (3/360)* 320 = 26.67
2704 -- NE_CW_NETACC8 -- Discount - 10 AMT : UOM - Pounds -- Accrual = 10 AMOT per Pound = 10 * 320 = 3200]
2705 -- Catch Weight ER - start
2706 /* OZF_UTILITY_PVT.get_catch_weight_quantity (
2707 p_inventory_item_id => l_order_line_tbl(i).inventory_item_id,
2708 p_order_line_id => l_order_line_tbl(i).line_id,
2709 x_return_status => l_return_status,
2710 x_cw_quantity => l_cw_quantity,
2711 x_cw_quantity_uom => l_cw_quantity_uom );*/
2712
2713 -- getting fulfillment_base from OE API. In case of performance issue can think of using fulfillment_base from existing cursor
2714 l_order_line_tbl(i).fulfillment_base := OE_DUAL_UOM_UTIL.get_fulfillment_base(l_order_line_tbl(i).line_id) ;
2715
2716 IF l_order_line_tbl(i).fulfillment_base = 'S' THEN
2717 l_cw_quantity := l_order_line_tbl(i).shipping_quantity;
2718 l_cw_quantity_uom := l_order_line_tbl(i).shipping_quantity_uom;
2719 ELSE
2720 l_cw_quantity := NVL(l_order_line_tbl(i).shipping_quantity2, l_order_line_tbl(i).shipping_quantity);
2721
2722 -- Fix for Bug 16301672 : For non-catch weight items shipping_qty2 is coming 0 instead of null
2723 IF l_cw_quantity = 0 THEN
2724 l_cw_quantity := l_order_line_tbl(i).shipping_quantity;
2725 END IF;
2726
2727 l_cw_quantity_uom := NVL(l_order_line_tbl(i).shipping_quantity_uom2, l_order_line_tbl(i).shipping_quantity_uom);
2728 END IF;
2729
2730 IF l_cw_quantity_uom IS NOT NULL AND l_cw_quantity_uom <> l_offer_uom_code THEN
2731 l_ship_quantity :=
2732 inv_convert.inv_um_convert(l_order_line_tbl(i).inventory_item_id -- item_id
2733 ,NULL -- precision
2734 ,l_cw_quantity -- from_quantity
2735 ,l_cw_quantity_uom -- from_unit
2736 ,l_offer_uom_code -- to_unit
2737 ,NULL -- from_name
2738 ,NULL -- to_name
2739 );
2740 IF (l_ship_quantity = -99999) THEN
2741 ozf_utility_pvt.write_conc_log ('Error in UOM conversion');
2742 END IF;
2743 ELSE
2744 l_ship_quantity := l_cw_quantity ;
2745 END IF;
2746 ozf_utility_pvt.write_conc_log('Catch Weight - l_ship_quantity = '|| l_ship_quantity);
2747 -- Catch Weight ER - end
2748 l_line_acc_amount := get_accrualed_amount(p_product_id => l_order_line_tbl(i).inventory_item_id
2749 ,p_line_amt => l_line_amount
2750 ,p_quantity => l_ship_quantity -- Catch Weight ER : Passing the shipping quantity instead of pricing quantity
2751 ,p_uom => l_order_line_tbl(i).pricing_quantity_uom); -- even if passed, pricing_quantity_uom is not used, hence not changing.
2752 -- IF G_DEBUG_LOW THEN
2753 ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (-) With l_line_acc_amount: '|| l_line_acc_amount);
2754 -- END IF;
2755 --
2756
2757 --
2758 l_utilization_type := 'ACCRUAL';
2759 l_reference_type := NULL;
2760 l_beneficiary_id := l_net_accrual_offers.qualifier_id;
2761 l_referral_id := NULL;
2762 --
2763 END IF; -- End custom_setup_id 105
2764
2765 IF l_batch_mode = 'NO'
2766 THEN
2767 --
2768 IF ( l_beneficiary_id IS NULL
2769 OR
2770 l_beneficiary_id = fnd_api.g_miss_num )
2771 THEN
2772 --
2773 -- Benificiay Id can be NULL only for PV Net Accrual Offers
2774 -- If PV decides not to accrue for this customer, it returns NULL
2775 --
2776 NULL;
2777 ELSE
2778 --
2779 --ninarasi fix for bug 15991204
2780 OPEN c_cust_number (l_order_line_tbl(i).invoice_to_org_id);
2781 FETCH c_cust_number INTO l_act_util_rec.billto_cust_account_id;
2782 CLOSE c_cust_number;
2783 l_act_budgets_rec.act_budget_used_by_id := l_net_accrual_offers.qp_list_header_id;
2784 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
2785 l_act_budgets_rec.budget_source_type := 'OFFR';
2786 l_act_budgets_rec.budget_source_id := l_net_accrual_offers.qp_list_header_id;
2787 l_act_budgets_rec.request_amount := l_line_acc_amount;
2788 --nirprasa,12.2 ER 8399135.
2789 --l_act_budgets_rec.request_currency := l_net_accrual_offers.fund_request_curr_code;
2790 IF l_net_accrual_offers.transaction_currency_code IS NULL THEN
2791 l_act_budgets_rec.request_currency := l_order_line_tbl(i).transactional_curr_code;
2792 ELSE
2793 l_act_budgets_rec.request_currency := l_net_accrual_offers.fund_request_curr_code;
2794 END IF;
2795 l_act_budgets_rec.request_date := l_sysdate;--nepanda : fix for bug 8766564
2796 l_act_budgets_rec.status_code := 'APPROVED';
2797 l_act_budgets_rec.approved_amount := l_line_acc_amount;
2798 --nirprasa,12.2 ER 8399135.
2799 --l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
2800 l_act_budgets_rec.approved_in_currency := l_act_budgets_rec.request_currency;
2801 l_act_budgets_rec.approval_date := l_sysdate;--nepanda : fix for bug 8766564
2802 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
2803 l_act_budgets_rec.justification := 'NA: ' || TO_CHAR(l_sysdate, 'MM/DD/YYYY');
2804 l_act_budgets_rec.transfer_type := 'UTILIZED';
2805 l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
2806
2807 l_act_util_rec.object_type := 'ORDER';
2808 l_act_util_rec.object_id := l_order_line_tbl(i).header_id;
2809 l_act_util_rec.product_level_type := 'PRODUCT';
2810 l_act_util_rec.product_id := l_order_line_tbl(i).inventory_item_id;
2811 l_act_util_rec.cust_account_id := l_beneficiary_id;
2812 l_act_util_rec.utilization_type := l_utilization_type;
2813 l_act_util_rec.adjustment_date := l_sysdate;--nepanda : fix for bug 8766564
2814 --ninarasi fix for bug 14798341 - assigned gl date
2815 l_act_util_rec.gl_date := l_gldate;--nepanda : fix for bug 8766564
2816 --ninarasi fix for bug 15991204
2817 --l_act_util_rec.billto_cust_account_id := l_order_line_tbl(i).invoice_to_org_id;
2818 l_act_util_rec.reference_type := l_reference_type;
2819 l_act_util_rec.reference_id := l_referral_id;
2820 l_act_util_rec.order_line_id := l_order_line_tbl(i).line_id;
2821 l_act_util_rec.org_id := l_order_line_tbl(i).org_id;
2822 --nirprasa,12.2 ER 8399135.
2823 l_act_util_rec.plan_currency_code := l_act_budgets_rec.request_currency;
2824 l_act_util_rec.fund_request_currency_code := l_net_accrual_offers.fund_request_curr_code;
2825 --nirprasa,12.2
2826
2827 -- Bug 3463302. Do not create utilization if amount is zero
2828 IF l_act_budgets_rec.request_amount <> 0
2829 THEN
2830 --
2831 ozf_fund_adjustment_pvt.process_act_budgets(x_return_status => l_return_status
2832 ,x_msg_count => l_msg_count
2833 ,x_msg_data => l_msg_data
2834 ,p_act_budgets_rec => l_act_budgets_rec
2835 ,p_act_util_rec => l_act_util_rec
2836 ,x_act_budget_id => l_act_budget_id
2837 ,x_utilized_amount => l_utilized_amount);
2838 --
2839 IF G_DEBUG_LOW THEN
2840 ozf_utility_pvt.write_conc_log('Req Curr/Req Amt/Util Amt: ' || l_act_budgets_rec.request_currency || '/' || l_act_budgets_rec.request_amount || '/' || l_utilized_amount);
2841 END IF;
2842
2843 IF l_return_status = Fnd_Api.g_ret_sts_error
2844 THEN
2845 ozf_utility_pvt.write_conc_log('Exp Error: 111 Process_Act_Budgets: line_id ( '||l_order_line_tbl(i).line_id
2846 || ' ) Error: '||l_msg_data);
2847 log_exception(l_act_budgets_rec, l_act_util_rec);
2848 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
2849 THEN
2850 ozf_utility_pvt.write_conc_log('UnExp Error: 222 Process_Act_Budgets: line_id ( '||l_order_line_tbl(i).line_id
2851 || ' ) Error: '||l_msg_data);
2852 log_exception(l_act_budgets_rec, l_act_util_rec);
2853 END IF;
2854
2855 l_utilized_amount := 0;
2856 --
2857 END IF; -- end amount <> 0
2858
2859 l_act_budgets_rec := NULL;
2860 l_act_util_rec := NULL;
2861 --
2862 END IF; -- End beneficiary is Not Null
2863
2864 -- End Batch Mode = NO
2865 ELSE
2866 -- If Batch Mode = YES, accumulate accrual.
2867 l_accrual_amount := l_accrual_amount + l_line_acc_amount;
2868 --
2869 END IF; -- End Batch Mode Check
2870 --
2871 END IF; -- Customer Qualfied = 'Y'
2872
2873 -----------------------------------------------------
2874 END LOOP; -- l_order_line_tbl
2875 -----------------------------------------------------
2876 --
2877 -- Bug 11889816 : added org id check to exit from respective cursor : nepanda - Bug 13376173 - start
2878 IF l_offer_org_id IS NULL THEN
2879 IF l_is_prm_offer THEN
2880 EXIT WHEN c_order_line_prm%NOTFOUND;
2881 ELSE
2882 EXIT WHEN c_order_line_global%NOTFOUND; -- nepanda : bug # 14277687
2883 END IF;
2884 ELSE
2885 EXIT WHEN c_order_line%NOTFOUND;
2886 END IF;
2887 -- nepanda - Bug 13376173 - end
2888 END LOOP; -- Order lines Cursor
2889
2890 -- Bug 11889816 : added org id check to close respective cursor - nepanda - Bug 13376173 - start
2891 IF l_offer_org_id IS NULL THEN
2892 IF l_is_prm_offer THEN
2893 CLOSE c_order_line_prm;
2894 ELSE
2895 CLOSE c_order_line_global; -- nepanda : bug # 14277687
2896 END IF;
2897 ELSE
2898 CLOSE c_order_line;
2899 END IF; -- nepanda - Bug 13376173 - end
2900
2901 IF l_batch_mode = 'YES'
2902 THEN
2903 --
2904 IF l_accrual_amount <> 0
2905 THEN
2906 --
2907 l_beneficiary_id := l_net_accrual_offers.qualifier_id;
2908 l_utilization_type := 'ACCRUAL';
2909 l_reference_type := NULL;
2910 l_referral_id := NULL;
2911
2912 IF l_beneficiary_id IS NULL OR l_beneficiary_id = fnd_api.g_miss_num
2913 THEN
2914 -- This condition will never occur.
2915 -- For PV offers, the Batch Mode is always NO and Beneficiary is always required
2916 -- for a Net Accrual Offer.
2917 NULL;
2918 --
2919 ELSE
2920 --
2921 l_act_budgets_rec.act_budget_used_by_id := l_net_accrual_offers.qp_list_header_id;
2922 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
2923 l_act_budgets_rec.budget_source_type := 'OFFR';
2924 l_act_budgets_rec.budget_source_id := l_net_accrual_offers.qp_list_header_id;
2925 l_act_budgets_rec.request_amount := l_accrual_amount;
2926 l_act_budgets_rec.request_currency := l_net_accrual_offers.fund_request_curr_code;
2927 l_act_budgets_rec.request_date := l_sysdate;--nepanda : fix for bug 8766564
2928 l_act_budgets_rec.status_code := 'APPROVED';
2929 l_act_budgets_rec.approved_amount := l_accrual_amount;
2930 l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
2931 l_act_budgets_rec.approval_date := l_sysdate;--nepanda : fix for bug 8766564
2932 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
2933 l_act_budgets_rec.justification := 'NA: ' || TO_CHAR(l_sysdate, 'MM/DD/YYYY');
2934 l_act_budgets_rec.transfer_type := 'UTILIZED';
2935 l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
2936
2937 l_act_util_rec.cust_account_id := l_beneficiary_id;
2938 l_act_util_rec.utilization_type := l_utilization_type;
2939 l_act_util_rec.adjustment_date := l_sysdate;--nepanda : fix for bug 8766564
2940 --ninarasi fix for bug 14798341 - assigned gl date
2941 l_act_util_rec.gl_date := l_gldate;--nepanda : fix for bug 8766564
2942 l_act_util_rec.reference_type := l_reference_type;
2943 l_act_util_rec.reference_id := l_referral_id;
2944 --nirprasa,12.2 ER 8399135.
2945 l_act_util_rec.plan_currency_code := l_net_accrual_offers.fund_request_curr_code;
2946 l_act_util_rec.fund_request_amount := l_accrual_amount;
2947 l_act_util_rec.fund_request_amount_remaining := l_accrual_amount;
2948 l_act_util_rec.fund_request_currency_code := l_net_accrual_offers.fund_request_curr_code;
2949 --nirprasa,12.2
2950
2951 ozf_fund_adjustment_pvt.process_act_budgets(x_return_status => l_return_status
2952 ,x_msg_count => l_msg_count
2953 ,x_msg_data => l_msg_data
2954 ,p_act_budgets_rec => l_act_budgets_rec
2955 ,p_act_util_rec => l_act_util_rec
2956 ,x_act_budget_id => l_act_budget_id
2957 ,x_utilized_amount => l_utilized_amount);
2958
2959 IF G_DEBUG_LOW THEN
2960 ozf_utility_pvt.write_conc_log('Req Curr/Req Amt/Util Amt: ' || l_act_budgets_rec.request_currency || '/' || l_act_budgets_rec.request_amount || '/' || l_utilized_amount);
2961 END IF;
2962
2963 IF l_return_status = Fnd_Api.g_ret_sts_error
2964 THEN
2965 ozf_utility_pvt.write_conc_log('Exp Error: 333 Process_Act_Budgets Error: '||l_msg_data );
2966 ozf_utility_pvt.write_conc_log('Exp Error: 333 Process_Act_Budgets Error: '|| SQLERRM );
2967 log_exception(l_act_budgets_rec, l_act_util_rec);
2968 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
2969 THEN
2970 ozf_utility_pvt.write_conc_log('UnExp 444 Error: Process_Act_Budgets Error: '||l_msg_data );
2971 log_exception(l_act_budgets_rec, l_act_util_rec);
2972 END IF;
2973
2974 l_utilized_amount := 0;
2975 l_act_budgets_rec := NULL;
2976 l_act_util_rec := NULL;
2977 --
2978 END IF; -- End check beneficiary id
2979 --
2980 END IF; -- end l_accrual_amount <> 0
2981 --
2982 END IF; -- end l_batch_mode = 'YES'
2983 --
2984 END IF; -- End OM lines
2985
2986 ozf_utility_pvt.write_conc_log('-- Done Processing Orders -- ');
2987
2988 --------------- Done Processing Orders ------------------------
2989
2990 ozf_utility_pvt.write_conc_log('-- Start Processing Deduction Rules -- ');
2991
2992 FOR l_na_rule_line IN c_na_rule_lines(l_net_accrual_offers.na_rule_header_id)
2993 LOOP
2994 --
2995 l_return_status := FND_API.g_ret_sts_success; -- bug 3655853
2996
2997 OPEN c_na_deduction_rule(l_na_rule_line.na_deduction_rule_id);
2998 FETCH c_na_deduction_rule INTO l_na_deduction_rule;
2999 CLOSE c_na_deduction_rule;
3000
3001 ozf_utility_pvt.write_conc_log('Name / Type / Id / Org : '||
3002 l_na_deduction_rule.name || ' / ' ||
3003 l_na_deduction_rule.transaction_type_code || ' / ' ||
3004 l_na_deduction_rule.deduction_identifier_id || ' / ' ||
3005 l_na_deduction_rule.deduction_identifier_org_id );
3006
3007 ozf_utility_pvt.write_conc_log('l_na_deduction_rule.transaction_source_code : ' || l_na_deduction_rule.transaction_source_code);
3008
3009
3010
3011 ---------------------------------------------------------------
3012 IF l_na_deduction_rule.transaction_source_code = 'AR' THEN
3013 ---------------------------------------------------------------
3014 --
3015 l_ar_dedu_amount := 0;
3016 l_ar_trx_line_tbl.delete;
3017
3018 l_return_status := FND_API.g_ret_sts_success; -- bug 3655853
3019
3020 -- Always set Start Date to offer start date for AR transactions because
3021 -- A transaction "A" can be created on Date1
3022 -- Net Accrual Engine could have run on Date2.It will not pick "A"
3023 -- Transaction "A" could have been completed on Date3
3024 -- Net Accrual Engine is run on Date4. It will still not pick "A" because Date1 is before Date3
3025
3026 -- So, always pick all the completed transaction during the Offer period.
3027 -- Check utilizations table if it has been already processed
3028
3029 l_ar_start_date := l_net_accrual_offers.start_date_active;
3030
3031 OPEN c_ar_trx_line_details(l_na_deduction_rule.deduction_identifier_id,
3032 l_ar_start_date,
3033 l_end_date,
3034 l_na_deduction_rule.deduction_identifier_org_id );
3035
3036 LOOP
3037 --
3038 FETCH c_ar_trx_line_details BULK COLLECT INTO l_ar_trx_line_tbl LIMIT l_batch_size;
3039 --
3040 -- To handle NO DATA FOUND for c_ar_trx_line CURSOR
3041 IF l_ar_trx_line_tbl.FIRST IS NULL
3042 THEN
3043 --
3044 ozf_utility_pvt.write_conc_log('No Data found in c_ar_trx_line_details CURSOR');
3045 EXIT;
3046 --
3047 END IF;
3048 -- Exit after finishing processing is before END LOOP
3049 --
3050 ---------------------------------------------------------
3051 FOR i IN l_ar_trx_line_tbl.FIRST .. l_ar_trx_line_tbl.LAST
3052 LOOP
3053 --
3054
3055 --ninarasi fix for bug 14798341 - gl period is checked first time since deduction_identifier_org_id is same inside loop
3056 IF i = 1 THEN
3057 l_gl_date_count := 0;
3058 OPEN c_gl_period(l_na_deduction_rule.deduction_identifier_org_id,l_gldate);
3059 FETCH c_gl_period INTO l_gl_date_count;
3060 CLOSE c_gl_period;
3061
3062 IF l_gl_date_count = 0 THEN
3063 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3064 FND_MESSAGE.set_name('OZF', 'OZF_SETL_GLDATE_INVALID');
3065 FND_MSG_PUB.add;
3066 END IF;
3067 ozf_utility_pvt.write_conc_log;
3068 RAISE FND_API.G_EXC_ERROR;
3069 END IF;
3070 END IF;
3071
3072 l_customer_qualified := validate_customer(l_ar_trx_line_tbl(i).bill_to_site_use_id,
3073 l_ar_trx_line_tbl(i).ship_to_site_use_id,
3074 l_ar_trx_line_tbl(i).sold_to_customer_id);
3075
3076 ozf_utility_pvt.write_conc_log('Cust_Trx_Line_Id / Customer Qualifier ? : ' ||
3077 l_ar_trx_line_tbl(i).customer_trx_line_id || '/' ||l_customer_qualified );
3078
3079 IF l_customer_qualified = 'Y'
3080 THEN
3081 --
3082
3083 --
3084 IF ( l_ar_trx_line_tbl(i).invoice_currency_code
3085 <> l_net_accrual_offers.fund_request_curr_code)
3086 THEN
3087 --
3088 l_new_amount := 0;
3089 --Added for bug 7030415
3090 --only those records are picked for which the org_id=l_na_deduction_rule.deduction_identifier_org_id
3091 OPEN c_get_conversion_type(l_na_deduction_rule.deduction_identifier_org_id);
3092 FETCH c_get_conversion_type INTO l_exchange_rate_type;
3093 CLOSE c_get_conversion_type;
3094 ozf_utility_pvt.convert_currency(
3095 x_return_status => l_return_status
3096 ,p_from_currency => l_ar_trx_line_tbl(i).invoice_currency_code
3097 ,p_to_currency => l_net_accrual_offers.fund_request_curr_code
3098 ,p_conv_type => l_exchange_rate_type
3099 --,p_conv_date => l_ar_trx_line_tbl(i).conv_date
3100 ,p_conv_date => sysdate
3101 ,p_from_amount => l_ar_trx_line_tbl(i).extended_amount
3102 ,x_to_amount => l_new_amount
3103 ,x_rate => l_rate);
3104 --nirprasa,12.2 ER 8399135. Use the amount in order currency for batch mode NO
3105 --Comment out the assignment only. Converted amount will be used
3106 --later for batch mode YES
3107 --nirprasa,12.2
3108 IF l_net_accrual_offers.transaction_currency_code IS NOT NULL
3109 OR l_batch_mode = 'YES' THEN
3110 l_ar_trx_line_tbl(i).extended_amount := l_new_amount;
3111 END IF;
3112
3113 IF l_return_status = Fnd_Api.g_ret_sts_error
3114 THEN
3115 ozf_utility_pvt.write_conc_log('Convert currency ' || l_return_status);
3116 RAISE Fnd_Api.g_exc_error;
3117 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
3118 THEN
3119 ozf_utility_pvt.write_conc_log('Convert currency ' || l_return_status);
3120 RAISE Fnd_Api.g_exc_unexpected_error;
3121 END IF;
3122 --
3123 END IF;
3124
3125 IF ( l_na_deduction_rule.transaction_type_code = 'CM' )
3126 THEN
3127 -- Old calculation
3128 -- l_sign := -1;
3129 -- l_quantity := -1 * NVL(l_ar_trx_line_tbl(i).quantity_credited, -1);
3130
3131 -- New Calculation
3132 -- Record the Sign of the Credit Memo
3133 l_sign := SIGN(l_ar_trx_line_tbl(i).extended_amount);
3134
3135 -- Always send positive value for the quantity, for calculation
3136 l_quantity := NVL(ABS(l_ar_trx_line_tbl(i).quantity_credited), 1);
3137 --
3138 ELSIF ( l_na_deduction_rule.transaction_type_code = 'DM' )
3139 THEN
3140 --
3141 l_sign := SIGN(l_ar_trx_line_tbl(i).extended_amount); --1;
3142 l_quantity := NVL(ABS(l_ar_trx_line_tbl(i).quantity_invoiced), 1); --l_ar_trx_line_tbl(i).quantity_invoiced;
3143 --
3144 END IF;
3145
3146 IF G_DEBUG_LOW
3147 THEN
3148 ozf_utility_pvt.write_conc_log('Sign of the Credit Memo : '||l_sign);
3149 ozf_utility_pvt.write_conc_log('quantity_credited : '||l_ar_trx_line_tbl(i).quantity_credited );
3150 END IF;
3151 -- Catch Weight ER - start
3152 IF l_ar_trx_line_tbl(i).uom_code IS NOT NULL AND l_ar_trx_line_tbl(i).uom_code <> l_offer_uom_code THEN
3153 l_quantity :=
3154 inv_convert.inv_um_convert(l_ar_trx_line_tbl(i).inventory_item_id -- item_id
3155 ,NULL -- precision
3156 ,l_quantity -- from_quantity
3157 ,l_ar_trx_line_tbl(i).uom_code -- from_unit
3158 ,l_offer_uom_code -- to_unit
3159 ,NULL -- from_name
3160 ,NULL -- to_name
3161 );
3162 IF (l_quantity = -99999) THEN
3163 ozf_utility_pvt.write_conc_log ('Error in UOM conversion');
3164 END IF;
3165 END IF;
3166 ozf_utility_pvt.write_conc_log('Catch Weight - l_shipping_quantity = '|| l_shipping_quantity);
3167 -- Catch Weight ER - end
3168
3169 -- Always send positive values for calculation
3170 l_ar_dedu_line_amt := get_accrualed_amount(
3171 p_product_id => l_ar_trx_line_tbl(i).inventory_item_id
3172 ,p_line_amt => l_sign * l_ar_trx_line_tbl(i).extended_amount
3173 ,p_quantity => l_quantity
3174 ,p_uom => l_ar_trx_line_tbl(i).uom_code);
3175
3176 -- Convert the accrual amount back to the actual CM sign
3177 l_ar_dedu_line_amt := l_sign * l_ar_dedu_line_amt;
3178
3179
3180 -- Check if a utilization has already been created for this transaction
3181 -- for this Offer
3182 -- If Yes, then
3183 -- Check if the existing accrual and current accrual are the same
3184 -- If not, post the difference
3185 -- If No, Create utilization
3186
3187 OPEN c_get_util_amt (l_ar_trx_line_tbl(i).customer_trx_line_id,
3188 l_ar_trx_line_tbl(i).inventory_item_id,
3189 l_net_accrual_offers.qp_list_header_id);
3190 FETCH c_get_util_amt INTO l_existing_util_amt;
3191 CLOSE c_get_util_amt;
3192
3193 -- l_existing_util_amt will return as 0 if no utilziations already exist
3194 -- since the cursor c_get_util_amt has a NVL
3195
3196 IF G_DEBUG_LOW THEN
3197 ozf_utility_pvt.write_conc_log('l_ar_dedu_line_amt (A) : '||l_ar_dedu_line_amt);
3198 ozf_utility_pvt.write_conc_log('l_existing_util_amt (B) : '||l_existing_util_amt);
3199 ozf_utility_pvt.write_conc_log('(A) - (B) : '|| (l_ar_dedu_line_amt - l_existing_util_amt));
3200 END IF;
3201
3202 -- If utilizations do not exist l_existing_util_amt will be 0
3203 -- A - B will be = A
3204 -- If utilzations do exist for the same customer_trx_line_id
3205 -- A - B will be 0 in case of no change. Utilzation will not be created
3206 -- OR
3207 -- A - B will be the correct utilzation amount
3208
3209 l_ar_dedu_line_amt := l_ar_dedu_line_amt - l_existing_util_amt;
3210 --nirprasa,12.2 ER 8399135. Moved the condition here since it was restricting the conversion
3211 --only for batch mode NO
3212 IF l_batch_mode = 'NO' THEN
3213
3214 l_act_budgets_rec.act_budget_used_by_id := l_net_accrual_offers.qp_list_header_id;
3215 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
3216 l_act_budgets_rec.budget_source_type := 'OFFR';
3217 l_act_budgets_rec.budget_source_id := l_net_accrual_offers.qp_list_header_id;
3218 l_act_budgets_rec.request_amount := l_ar_dedu_line_amt;
3219 --nirprasa,12.2 ER 8399135. l_act_budgets_rec.request_currency := l_net_accrual_offers.fund_request_curr_code;
3220 IF l_net_accrual_offers.transaction_currency_code is NULL THEN
3221 l_act_budgets_rec.request_currency := l_ar_trx_line_tbl(i).invoice_currency_code;
3222 ELSE
3223 l_act_budgets_rec.request_currency := l_net_accrual_offers.transaction_currency_code;
3224 END IF;
3225 l_act_budgets_rec.request_date := l_sysdate;--nepanda : fix for bug 8766564
3226 l_act_budgets_rec.status_code := 'APPROVED';
3227 l_act_budgets_rec.approved_amount := l_ar_dedu_line_amt;
3228 --nirprasa,12.2 ER 8399135.l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
3229 l_act_budgets_rec.approved_in_currency := l_act_budgets_rec.request_currency;
3230 l_act_budgets_rec.approval_date := l_sysdate;--nepanda : fix for bug 8766564
3231 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
3232 l_act_budgets_rec.justification := 'NA: AR DEDUCTION' || TO_CHAR(l_sysdate, 'MM/DD/YYYY');
3233 l_act_budgets_rec.transfer_type := 'UTILIZED';
3234 l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
3235
3236 l_act_util_rec.object_type := l_na_deduction_rule.transaction_type_code;
3237 l_act_util_rec.object_id := l_ar_trx_line_tbl(i).customer_trx_id;
3238 l_act_util_rec.product_level_type := 'PRODUCT';
3239 l_act_util_rec.product_id := l_ar_trx_line_tbl(i).inventory_item_id;
3240 l_act_util_rec.cust_account_id := l_net_accrual_offers.qualifier_id;
3241 l_act_util_rec.utilization_type := 'ACCRUAL';
3242 l_act_util_rec.adjustment_date := l_sysdate;--nepanda : fix for bug 8766564
3243 --ninarasi fix for bug 14798341 - assigned gl date
3244 l_act_util_rec.gl_date := l_gldate;--nepanda : fix for bug 8766564
3245 l_act_util_rec.reference_type := 'TRX_LINE';
3246 l_act_util_rec.reference_id := l_ar_trx_line_tbl(i).customer_trx_line_id;
3247 --nirprasa,12.2 ER 8399135.
3248 l_act_util_rec.plan_currency_code := l_act_budgets_rec.request_currency;
3249 l_act_util_rec.fund_request_currency_code := l_net_accrual_offers.fund_request_curr_code;
3250 --nirprasa,12.2
3251 l_act_util_rec.org_id := l_na_deduction_rule.deduction_identifier_org_id; -- nepanda : Forward Port
3252
3253 -- Bug 3463302. dont create utilization if zero amount
3254 IF ( l_act_budgets_rec.request_amount <> 0 )
3255 THEN
3256 --
3257 ozf_fund_adjustment_pvt.process_act_budgets(
3258 x_return_status => l_return_status
3259 ,x_msg_count => l_msg_count
3260 ,x_msg_data => l_msg_data
3261 ,p_act_budgets_rec => l_act_budgets_rec
3262 ,p_act_util_rec => l_act_util_rec
3263 ,x_act_budget_id => l_act_budget_id
3264 ,x_utilized_amount => l_utilized_amount);
3265
3266 IF G_DEBUG_LOW THEN
3267 ozf_utility_pvt.write_conc_log('Req Curr/Req Amt/Util Amt: ' || l_act_budgets_rec.request_currency || '/' || l_act_budgets_rec.request_amount || '/' || l_utilized_amount);
3268 END IF;
3269
3270 l_utilized_amount := 0;
3271
3272 IF l_return_status = Fnd_Api.g_ret_sts_error
3273 THEN
3274 ozf_utility_pvt.write_conc_log('Exceptin : Msg from Budget API 3 : '||l_msg_data);
3275 log_exception(l_act_budgets_rec, l_act_util_rec);
3276 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
3277 THEN
3278 ozf_utility_pvt.write_conc_log('UnException : Msg from Budget API 3 : '||l_msg_data);
3279 log_exception(l_act_budgets_rec, l_act_util_rec);
3280 END IF;
3281 --
3282 END IF; -- End Amount <> 0
3283
3284 l_act_budgets_rec := NULL;
3285 l_act_util_rec := NULL;
3286 --
3287 ELSE
3288 --
3289 l_ar_dedu_amount := l_ar_dedu_amount + l_ar_dedu_line_amt;
3290 --
3291 END IF; -- End Batch Mode
3292 --
3293 END IF; -- End Customer Qualified
3294 --
3295 END LOOP; -- End l_ar_trx_line_tbl
3296 ----------------------------------------
3297 EXIT WHEN c_ar_trx_line_details%NOTFOUND;
3298 ----------------------------------------
3299 END LOOP; -- AR Trx Lines Cursor
3300
3301 CLOSE c_ar_trx_line_details;
3302
3303 IF l_batch_mode = 'YES'
3304 THEN
3305 --
3306 IF l_ar_dedu_amount <> 0
3307 THEN
3308 --
3309 l_act_budgets_rec.act_budget_used_by_id := l_net_accrual_offers.qp_list_header_id;
3310 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
3311 l_act_budgets_rec.budget_source_type := 'OFFR';
3312 l_act_budgets_rec.budget_source_id := l_net_accrual_offers.qp_list_header_id;
3313 l_act_budgets_rec.request_amount := l_ar_dedu_amount;
3314 l_act_budgets_rec.request_currency := l_net_accrual_offers.fund_request_curr_code;
3315 l_act_budgets_rec.request_date := l_sysdate;--nepanda : fix for bug 8766564
3316 l_act_budgets_rec.status_code := 'APPROVED';
3317 l_act_budgets_rec.approved_amount := l_ar_dedu_amount;
3318 l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
3319 l_act_budgets_rec.approval_date := l_sysdate;--nepanda : fix for bug 8766564
3320 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
3321 l_act_budgets_rec.justification := 'NA: AR DEDUCTION' || TO_CHAR(l_sysdate, 'MM/DD/YYYY');
3322 l_act_budgets_rec.transfer_type := 'UTILIZED';
3323 l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
3324
3325 l_act_util_rec.cust_account_id := l_net_accrual_offers.qualifier_id;
3326 l_act_util_rec.utilization_type := 'ACCRUAL';
3327 l_act_util_rec.adjustment_date := l_sysdate;--nepanda : fix for bug 8766564
3328 --ninarasi fix for bug 14798341 - assigned gl date
3329 l_act_util_rec.gl_date := l_gldate;--nepanda : fix for bug 8766564
3330 --nirprasa,12.2 ER 8399135.
3331 l_act_util_rec.plan_currency_code := l_net_accrual_offers.fund_request_curr_code;
3332 l_act_util_rec.fund_request_amount := l_ar_dedu_amount;
3333 l_act_util_rec.fund_request_amount_remaining := l_ar_dedu_amount;
3334 l_act_util_rec.fund_request_currency_code := l_net_accrual_offers.fund_request_curr_code;
3335 --nirprasa,12.2
3336
3337 IF G_DEBUG_LOW THEN
3338 ozf_utility_pvt.write_conc_log('Accrual log: AR Deduction BATCH_MODE = Y');
3339 ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
3340 ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
3341 ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
3342 ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
3343 ozf_utility_pvt.write_conc_log('Cust Acct Id: '||l_act_util_rec.cust_account_id);
3344 END IF;
3345
3346 ozf_fund_adjustment_pvt.process_act_budgets(
3347 x_return_status => l_return_status
3348 ,x_msg_count => l_msg_count
3349 ,x_msg_data => l_msg_data
3350 ,p_act_budgets_rec => l_act_budgets_rec
3351 ,p_act_util_rec => l_act_util_rec
3352 ,x_act_budget_id => l_act_budget_id
3353 ,x_utilized_amount => l_utilized_amount);
3354
3355 IF G_DEBUG_LOW THEN
3356 ozf_utility_pvt.write_conc_log('Req Curr/Req Amt/Util Amt: ' || l_act_budgets_rec.request_currency || '/' || l_act_budgets_rec.request_amount || '/' || l_utilized_amount);
3357 END IF;
3358
3359 l_utilized_amount := 0;
3360
3361 IF l_return_status = Fnd_Api.g_ret_sts_error
3362 THEN
3363 ozf_utility_pvt.write_conc_log('Exception : Msg from Budget API 4 : '||l_msg_data);
3364 log_exception(l_act_budgets_rec, l_act_util_rec);
3365 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3366 ozf_utility_pvt.write_conc_log('UnException : Msg from Budget API 4 : '||l_msg_data);
3367 log_exception(l_act_budgets_rec, l_act_util_rec);
3368 END IF;
3369
3370 l_act_budgets_rec := NULL;
3371 l_act_util_rec := NULL;
3372 --
3373 END IF; -- end amount <> 0
3374 --
3375 END IF; -- end batch mode = Y
3376 --
3377 -----------------------------------------------------------------
3378 ELSIF l_na_deduction_rule.transaction_source_code = 'OM' THEN
3379 -----------------------------------------------------------------
3380 --
3381 l_om_dedu_amount := 0;
3382 l_return_line_tbl.delete;
3383
3384 OPEN c_return_line( l_na_deduction_rule.deduction_identifier_id,
3385 l_start_date,
3386 l_end_date);
3387
3388
3389 LOOP
3390 --
3391 FETCH c_return_line BULK COLLECT INTO l_return_line_tbl LIMIT l_batch_size;
3392 --
3393 -- To handle NO DATA FOUND for c_return_line CURSOR
3394 IF l_return_line_tbl.FIRST IS NULL
3395 THEN
3396 --
3397 ozf_utility_pvt.write_conc_log('No Data found in c_return_line CURSOR');
3398 EXIT;
3399 --
3400 END IF;
3401 --
3402 ---------------------------------------------------------
3403 FOR i IN l_return_line_tbl.FIRST .. l_return_line_tbl.LAST
3404 LOOP
3405 ---------------------------------------------------------
3406 --
3407 --ninarasi fix for bug 14798341 -- check gl date for first run since order_type_id's org_id will be same across all RMA orders
3408 IF i = 1 THEN
3409 l_gl_date_count := 0;
3410 OPEN c_gl_period(l_return_line_tbl(i).org_id,l_gldate);
3411 FETCH c_gl_period INTO l_gl_date_count;
3412 CLOSE c_gl_period;
3413
3414 IF l_gl_date_count = 0 THEN
3415 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3416 FND_MESSAGE.set_name('OZF', 'OZF_SETL_GLDATE_INVALID');
3417 FND_MSG_PUB.add;
3418 END IF;
3419 ozf_utility_pvt.write_conc_log;
3420 RAISE FND_API.G_EXC_ERROR;
3421 END IF;
3422 END IF;
3423
3424 l_return_status := FND_API.g_ret_sts_success; -- bug 3655853
3425
3426 -- Original value is negtive
3427 l_return_line_tbl(i).invoiced_quantity := -1 * l_return_line_tbl(i).invoiced_quantity;
3428 l_line_amount := l_return_line_tbl(i).invoiced_quantity * l_return_line_tbl(i).unit_selling_price;
3429
3430 IF l_net_accrual_offers.custom_setup_id = 105
3431 THEN
3432 ----- For PV Net Accrual Offers, do not look at denorm -------
3433 ----- Get Country code from the Identifying addresss of the Customer
3434 OPEN c_country_code(l_return_line_tbl(i).invoice_to_org_id);
3435 FETCH c_country_code INTO l_country_code;
3436 CLOSE c_country_code;
3437
3438 -- l_terr_countries_tbl has all the countries eligible for this offer
3439 -- This table is populated in the 'Denorm Customers' section for each PV NA Offer
3440 l_customer_qualified := 'N';
3441
3442 FOR j IN l_terr_countries_tbl.FIRST .. l_terr_countries_tbl.LAST
3443 LOOP
3444 --
3445 IF l_country_code = l_terr_countries_tbl(j)
3446 THEN
3447 l_customer_qualified := 'Y';
3448 EXIT;
3449 END IF;
3450 --
3451 END LOOP;
3452
3453 IF l_customer_qualified = 'N' THEN
3454 -- sold_to not qualified. try ship_to
3455 OPEN c_country_code(l_return_line_tbl(i).ship_to_org_id);
3456 FETCH c_country_code INTO l_country_code;
3457 CLOSE c_country_code;
3458
3459 FOR j IN l_terr_countries_tbl.FIRST .. l_terr_countries_tbl.LAST
3460 LOOP
3461 --
3462 IF l_country_code = l_terr_countries_tbl(j)
3463 THEN
3464 l_customer_qualified := 'Y';
3465 EXIT;
3466 END IF;
3467 --
3468 END LOOP;
3469 --
3470 END IF;
3471 --
3472 ELSE
3473 --
3474 l_customer_qualified := validate_customer(
3475 p_invoice_to_org_id => l_return_line_tbl(i).invoice_to_org_id
3476 ,p_ship_to_org_id => l_return_line_tbl(i).ship_to_org_id
3477 ,p_sold_to_org_id => l_return_line_tbl(i).sold_to_org_id);
3478 END IF;
3479
3480 l_order_curr_code := l_return_line_tbl(i).transactional_curr_code;
3481
3482 IF l_customer_qualified = 'Y'
3483 THEN
3484 --
3485 IF l_net_accrual_offers.fund_request_curr_code <> l_order_curr_code
3486 THEN
3487 --
3488 l_new_amount := 0;
3489
3490 --Added for bug 7030415
3491 OPEN c_get_conversion_type(l_return_line_tbl(i).org_id);
3492 FETCH c_get_conversion_type INTO l_exchange_rate_type;
3493 CLOSE c_get_conversion_type;
3494
3495 ozf_utility_pvt.convert_currency(
3496 x_return_status => l_return_status
3497 ,p_from_currency => l_order_curr_code
3498 ,p_to_currency => l_net_accrual_offers.fund_request_curr_code
3499 ,p_conv_type => l_exchange_rate_type
3500 --,p_conv_date => l_return_line_tbl(i).conv_date
3501 ,p_conv_date => sysdate
3502 ,p_from_amount => l_line_amount
3503 ,x_to_amount => l_new_amount
3504 ,x_rate => l_rate);
3505 --nirprasa,12.2 ER 8399135.
3506 IF l_net_accrual_offers.transaction_currency_code IS NOT NULL
3507 OR l_batch_mode = 'YES' THEN
3508 l_line_amount := l_new_amount;
3509 END IF;
3510
3511 IF l_return_status = Fnd_Api.g_ret_sts_error
3512 THEN
3513 ozf_utility_pvt.write_conc_log('Convert currency ' || l_return_status);
3514 RAISE Fnd_Api.g_exc_error;
3515 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
3516 THEN
3517 ozf_utility_pvt.write_conc_log('Convert currency ' || l_return_status);
3518 RAISE Fnd_Api.g_exc_unexpected_error;
3519 END IF;
3520 --
3521 END IF;
3522 --
3523 -- Catch Weight ER - start
3524 IF l_return_line_tbl(i).pricing_quantity_uom IS NOT NULL AND l_return_line_tbl(i).pricing_quantity_uom <> l_offer_uom_code THEN
3525 l_return_line_tbl(i).invoiced_quantity :=
3526 inv_convert.inv_um_convert(l_order_line_tbl(i).inventory_item_id -- item_id
3527 ,NULL -- precision
3528 ,l_return_line_tbl(i).invoiced_quantity -- from_quantity
3529 ,l_return_line_tbl(i).pricing_quantity_uom -- from_unit
3530 ,l_offer_uom_code -- to_unit
3531 ,NULL -- from_name
3532 ,NULL -- to_name
3533 );
3534 IF (l_return_line_tbl(i).invoiced_quantity = -99999) THEN
3535 ozf_utility_pvt.write_conc_log ('Error in UOM conversion');
3536 END IF;
3537 END IF;
3538 ozf_utility_pvt.write_conc_log('Catch Weight - l_shipping_quantity = '|| l_shipping_quantity);
3539 -- Catch Weight ER - end
3540 l_om_dedu_line_amt := get_accrualed_amount(
3541 p_product_id => l_return_line_tbl(i).inventory_item_id
3542 ,p_line_amt => l_line_amount
3543 ,p_quantity => l_return_line_tbl(i).invoiced_quantity
3544 ,p_uom => l_return_line_tbl(i).pricing_quantity_uom);
3545
3546 -- return needs to be deducted, make it negative
3547 l_om_dedu_line_amt := -1 * l_om_dedu_line_amt;
3548
3549 IF l_batch_mode = 'NO'
3550 THEN
3551 --
3552 IF l_net_accrual_offers.custom_setup_id = 105
3553 THEN
3554 --
3555 pv_referral_comp_pub.get_beneficiary (p_api_version => 1.0,
3556 p_init_msg_list => FND_API.g_false,
3557 p_commit => FND_API.g_false,
3558 p_validation_level => FND_API.g_valid_level_full,
3559 p_order_header_id => l_return_line_tbl(i).header_id,
3560 p_order_line_id => l_return_line_tbl(i).line_id,
3561 p_offer_id => l_net_accrual_offers.offer_id,
3562 x_beneficiary_id => l_beneficiary_id,
3563 x_referral_id => l_referral_id,
3564 x_return_status => l_return_status,
3565 x_msg_count => l_msg_count,
3566 x_msg_data => l_msg_data);
3567
3568 l_utilization_type := 'LEAD_ACCRUAL';
3569 l_reference_type := 'LEAD_REFERRAL';
3570 --
3571 ELSE
3572 --
3573 l_beneficiary_id := l_net_accrual_offers.qualifier_id;
3574 l_utilization_type := 'ACCRUAL';
3575 l_reference_type := NULL;
3576 l_referral_id := NULL;
3577 --
3578 END IF;
3579
3580 IF l_beneficiary_id IS NULL OR l_beneficiary_id = fnd_api.g_miss_num THEN
3581 NULL;
3582 ELSE
3583 --ninarasi fix for bug 15991204
3584 OPEN c_cust_number (l_return_line_tbl(i).invoice_to_org_id);
3585 FETCH c_cust_number INTO l_act_util_rec.billto_cust_account_id;
3586 CLOSE c_cust_number;
3587 l_act_budgets_rec.act_budget_used_by_id := l_net_accrual_offers.qp_list_header_id;
3588 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
3589 l_act_budgets_rec.budget_source_type := 'OFFR';
3590 l_act_budgets_rec.budget_source_id := l_net_accrual_offers.qp_list_header_id;
3591 l_act_budgets_rec.request_amount := l_om_dedu_line_amt;
3592 --nirprasa,12.2 ER 8399135. l_act_budgets_rec.request_currency := l_net_accrual_offers.fund_request_curr_code;
3593 IF l_net_accrual_offers.transaction_currency_code IS NULL THEN
3594 l_act_budgets_rec.request_currency := l_order_curr_code;
3595 ELSE
3596 l_act_budgets_rec.request_currency := l_net_accrual_offers.transaction_currency_code;
3597 END IF;
3598 l_act_budgets_rec.request_currency := l_order_curr_code;
3599 l_act_budgets_rec.request_date := l_sysdate;--nepanda : fix for bug 8766564
3600 l_act_budgets_rec.status_code := 'APPROVED';
3601 l_act_budgets_rec.approved_amount := l_om_dedu_line_amt;
3602 --nirprasa,12.2 ER 8399135. l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
3603 l_act_budgets_rec.approved_in_currency := l_act_budgets_rec.request_currency;
3604 l_act_budgets_rec.approval_date := l_sysdate;--nepanda : fix for bug 8766564
3605 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
3606 l_act_budgets_rec.justification := 'NA: OM Deduction' || TO_CHAR(l_sysdate, 'MON-DD-YYYY');
3607 l_act_budgets_rec.transfer_type := 'UTILIZED';
3608 l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
3609
3610 l_act_util_rec.object_type := 'ORDER';
3611 l_act_util_rec.object_id := l_return_line_tbl(i).header_id;
3612 l_act_util_rec.product_level_type := 'PRODUCT';
3613 l_act_util_rec.product_id := l_return_line_tbl(i).inventory_item_id;
3614 l_act_util_rec.cust_account_id := l_beneficiary_id;
3615 l_act_util_rec.utilization_type := l_utilization_type;
3616 l_act_util_rec.adjustment_date := l_sysdate;--nepanda : fix for bug 8766564
3617 --ninarasi fix for bug 14798341 - assigned gl date
3618 l_act_util_rec.gl_date := l_gldate;--nepanda : fix for bug 8766564
3619 --l_act_util_rec.billto_cust_account_id := l_return_line_tbl(i).invoice_to_org_id;
3620 l_act_util_rec.reference_type := l_reference_type;
3621 l_act_util_rec.reference_id := l_referral_id;
3622 l_act_util_rec.order_line_id := l_return_line_tbl(i).line_id;
3623 l_act_util_rec.org_id := l_return_line_tbl(i).org_id;
3624 --nirprasa,12.2 ER 8399135.
3625 l_act_util_rec.plan_currency_code := l_act_budgets_rec.request_currency;
3626 l_act_util_rec.fund_request_currency_code := l_net_accrual_offers.fund_request_curr_code;
3627 --nirprasa,12.2
3628
3629 IF l_act_budgets_rec.request_amount <> 0
3630 THEN
3631 -- bug 3463302. dont create utilization if zero amount
3632 ozf_fund_adjustment_pvt.process_act_budgets(
3633 x_return_status => l_return_status
3634 ,x_msg_count => l_msg_count
3635 ,x_msg_data => l_msg_data
3636 ,p_act_budgets_rec => l_act_budgets_rec
3637 ,p_act_util_rec => l_act_util_rec
3638 ,x_act_budget_id => l_act_budget_id
3639 ,x_utilized_amount => l_utilized_amount);
3640
3641 IF G_DEBUG_LOW THEN
3642 ozf_utility_pvt.write_conc_log('Req Curr/Req Amt/Util Amt: ' || l_act_budgets_rec.request_currency || '/' || l_act_budgets_rec.request_amount || '/' || l_utilized_amount);
3643 END IF;
3644
3645 l_utilized_amount := 0;
3646
3647 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
3648 ozf_utility_pvt.write_conc_log('Exception : Msg from Budget API 5 : '||l_msg_data);
3649 log_exception(l_act_budgets_rec, l_act_util_rec);
3650 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3651 ozf_utility_pvt.write_conc_log('UnException : Msg from Budget API 5 : '||l_msg_data);
3652 log_exception(l_act_budgets_rec, l_act_util_rec);
3653 END IF;
3654 --
3655 END IF; -- end amount <> 0
3656
3657 l_act_budgets_rec := NULL;
3658 l_act_util_rec := NULL;
3659 END IF;
3660 --
3661 ELSE
3662 --
3663 l_om_dedu_amount := l_om_dedu_amount + l_om_dedu_line_amt;
3664 --
3665 END IF; -- end batch mode
3666 --
3667 END IF; -- end validate customer
3668 --
3669 ---------------------------------------
3670 END LOOP; -- end return order lines
3671 ---------------------------------------
3672 --
3673 EXIT WHEN c_return_line%NOTFOUND;
3674 --
3675 END LOOP; -- Return lines Cursor
3676
3677 CLOSE c_return_line;
3678
3679 IF l_batch_mode = 'YES'
3680 THEN
3681 --
3682 IF l_om_dedu_amount <> 0
3683 THEN
3684 --
3685 l_act_budgets_rec.act_budget_used_by_id := l_net_accrual_offers.qp_list_header_id;
3686 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
3687 l_act_budgets_rec.budget_source_type := 'OFFR';
3688 l_act_budgets_rec.budget_source_id := l_net_accrual_offers.qp_list_header_id;
3689 l_act_budgets_rec.request_amount := l_om_dedu_amount;
3690 l_act_budgets_rec.request_currency := l_net_accrual_offers.fund_request_curr_code;
3691 l_act_budgets_rec.request_date := l_sysdate;--nepanda : fix for bug 8766564
3692 l_act_budgets_rec.status_code := 'APPROVED';
3693 l_act_budgets_rec.approved_amount := l_om_dedu_amount;
3694 l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
3695 l_act_budgets_rec.approval_date := l_sysdate;--nepanda : fix for bug 8766564
3696 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
3697 l_act_budgets_rec.justification := 'NA: ' || TO_CHAR(l_sysdate, 'MON-DD-YYYY');
3698 l_act_budgets_rec.transfer_type := 'UTILIZED';
3699 l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
3700
3701 l_act_util_rec.cust_account_id := l_net_accrual_offers.qualifier_id;
3702 l_act_util_rec.utilization_type := 'ACCRUAL';
3703 l_act_util_rec.adjustment_date := l_sysdate;--nepanda : fix for bug 8766564
3704 --ninarasi fix for bug 14798341 - assigned gl date
3705 l_act_util_rec.gl_date := l_gldate;--nepanda : fix for bug 8766564
3706 --nirprasa,12.2 ER 8399135.
3707 l_act_util_rec.plan_currency_code := l_net_accrual_offers.fund_request_curr_code;
3708 l_act_util_rec.fund_request_amount := l_om_dedu_amount;
3709 l_act_util_rec.fund_request_amount_remaining := l_om_dedu_amount;
3710 l_act_util_rec.fund_request_currency_code := l_net_accrual_offers.fund_request_curr_code;
3711 --nirprasa,12.2
3712
3713 ozf_fund_adjustment_pvt.process_act_budgets(
3714 x_return_status => l_return_status
3715 ,x_msg_count => l_msg_count
3716 ,x_msg_data => l_msg_data
3717 ,p_act_budgets_rec => l_act_budgets_rec
3718 ,p_act_util_rec => l_act_util_rec
3719 ,x_act_budget_id => l_act_budget_id
3720 ,x_utilized_amount => l_utilized_amount);
3721
3722 IF G_DEBUG_LOW THEN
3723 ozf_utility_pvt.write_conc_log('Req Curr/Req Amt/Util Amt: ' || l_act_budgets_rec.request_currency || '/' || l_act_budgets_rec.request_amount || '/' || l_utilized_amount);
3724 END IF;
3725
3726 l_utilized_amount := 0;
3727
3728 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
3729 ozf_utility_pvt.write_conc_log('Exception : Msg from Budget API 6 : '||l_msg_data);
3730 log_exception(l_act_budgets_rec, l_act_util_rec);
3731 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3732 ozf_utility_pvt.write_conc_log('UnException : Msg from Budget API 6 : '||l_msg_data);
3733 log_exception(l_act_budgets_rec, l_act_util_rec);
3734 END IF;
3735
3736 l_act_budgets_rec := NULL;
3737 l_act_util_rec := NULL;
3738 --
3739 END IF; -- end l_om_dedu_amount > 0
3740 --
3741 END IF; -- end l_batch_mode = 'YES'
3742 --
3743 --------------------------------------------------------------
3744 ELSIF l_na_deduction_rule.transaction_source_code = 'TM' THEN
3745 --------------------------------------------------------------
3746
3747 -- Bug 3483348 julou validate market and product eligibility for tm deduction
3748 l_tm_dedu_amount := 0; -- total of tm deduction
3749 l_index := 0;
3750 l_inventory_item_id_old := null; --AMITAMKU fix for bug 14692296
3751 -- Fix for Bug 16301672
3752 l_tm_dedu_line_amt_old := NULL;
3753 l_reduce_line_amount := FALSE ;
3754 l_line_amount_old := NULL ;
3755
3756 IF G_DEBUG_LOW THEN
3757 ozf_utility_pvt.write_conc_log('l_na_deduction_rule.deduction_identifier_id: ' || l_na_deduction_rule.deduction_identifier_id);
3758 ozf_utility_pvt.write_conc_log('l_start_date ' || l_start_date);
3759 ozf_utility_pvt.write_conc_log('l_end_date ' || l_end_date);
3760 ozf_utility_pvt.write_conc_log('l_net_accrual_offers.qp_list_header_id ' || l_net_accrual_offers.qp_list_header_id);
3761 END IF;
3762
3763 l_counter := 0;
3764 FOR l_tm_line IN c_tm_lines(l_na_deduction_rule.deduction_identifier_id
3765 ,l_start_date
3766 ,l_end_date
3767 ,l_net_accrual_offers.qp_list_header_id)
3768 LOOP
3769 --
3770 l_return_status := FND_API.g_ret_sts_success; -- bug 3655853
3771
3772 l_customer_qualified := validate_customer(NULL, NULL, l_tm_line.cust_account_id);
3773
3774 IF l_customer_qualified = 'Y'
3775 THEN
3776
3777 -- nepanda : Fix for Bug 09204988
3778 -- Fix for 8772550 : calling get_accrualed_amount to apply the Net Acrrual Offers discount
3779 IF G_DEBUG_LOW THEN
3780 ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (+)');
3781 ozf_utility_pvt.write_conc_log('l_tm_line.utilization_id: ' || l_tm_line.order_line_id);
3782 ozf_utility_pvt.write_conc_log('l_tm_line.line_amount: ' || l_tm_line.line_amount);
3783 ozf_utility_pvt.write_conc_log('l_tm_line.object_type: ' || l_tm_line.object_type);
3784 END IF;
3785 -- Fix for 9296109
3786 l_inventory_item_id := null;
3787 l_pricing_quantity := null;
3788 l_pricing_quantity_uom := null;
3789 l_order_line_id := null;
3790 l_order_org_id := null;
3791
3792 IF l_tm_line.object_type = 'CM' THEN
3793 OPEN c_get_credit_memo_details(l_tm_line.reference_id);
3794 FETCH c_get_credit_memo_details INTO l_inventory_item_id, l_pricing_quantity, l_pricing_quantity_uom, l_order_org_id; -- added org_id for bug # 10379136
3795 CLOSE c_get_credit_memo_details;
3796 l_pricing_quantity := NVL(ABS(l_pricing_quantity), 1);
3797 ELSIF l_tm_line.object_type = 'DM' THEN
3798 OPEN c_get_debit_memo_details(l_tm_line.reference_id);
3799 FETCH c_get_debit_memo_details INTO l_inventory_item_id, l_pricing_quantity, l_pricing_quantity_uom, l_order_org_id; -- added org_id for bug # 10379136
3800 CLOSE c_get_debit_memo_details;
3801 l_pricing_quantity := NVL(ABS(l_pricing_quantity), 1);
3802 ELSE
3803 OPEN c_get_order_details(l_tm_line.order_line_id);
3804 FETCH c_get_order_details INTO l_inventory_item_id, l_pricing_quantity, l_pricing_quantity_uom, l_order_line_id, l_order_org_id,
3805 l_shipping_quantity, l_shipping_quantity_uom, l_shipping_quantity2, l_shipping_quantity_uom2, l_fulfillment_base; -- catch weight ER
3806 CLOSE c_get_order_details;
3807 -- Catch Weight ER - start
3808 /*OZF_UTILITY_PVT.get_catch_weight_quantity (
3809 p_inventory_item_id => l_inventory_item_id,
3810 p_order_line_id => l_order_line_id,
3811 x_return_status => l_return_status,
3812 x_cw_quantity => l_cw_quantity,
3813 x_cw_quantity_uom => l_cw_quantity_uom );*/
3814
3815 -- getting fulfillment_base from OE API. In case of performance issue can think of using fulfillment_base from existing cursor
3816 l_fulfillment_base := OE_DUAL_UOM_UTIL.get_fulfillment_base(l_order_line_id) ;
3817
3818 IF l_fulfillment_base = 'S' THEN
3819 l_cw_quantity := l_shipping_quantity;
3820 l_cw_quantity_uom := l_shipping_quantity_uom;
3821 ELSE
3822 l_cw_quantity := NVL(l_shipping_quantity2, l_shipping_quantity);
3823 -- Fix for Bug 16301672
3824 IF l_cw_quantity = 0 THEN
3825 l_cw_quantity := l_shipping_quantity ;
3826 END IF;
3827 l_cw_quantity_uom := NVL(l_shipping_quantity_uom2, l_shipping_quantity_uom);
3828 END IF;
3829
3830 IF l_cw_quantity_uom IS NOT NULL AND l_cw_quantity_uom <> l_offer_uom_code THEN
3831 l_pricing_quantity :=
3832 inv_convert.inv_um_convert(l_inventory_item_id -- item_id
3833 ,NULL -- precision
3834 ,l_cw_quantity -- from_quantity
3835 ,l_cw_quantity_uom -- from_unit
3836 ,l_offer_uom_code -- to_unit
3837 ,NULL -- from_name
3838 ,NULL -- to_name
3839 );
3840 IF (l_shipping_quantity = -99999) THEN
3841 ozf_utility_pvt.write_conc_log ('Error in UOM conversion');
3842 END IF;
3843 ELSE
3844 l_pricing_quantity := l_cw_quantity ;
3845 END IF;
3846 ozf_utility_pvt.write_conc_log('Catch Weight - l_pricing_quantity = '|| l_pricing_quantity);
3847 -- Catch Weight ER - end
3848 END IF; --IF l_tm_line.object_type = 'CM'
3849
3850 IF l_net_accrual_offers.orig_org_id IS NULL THEN
3851 l_tm_deduction_org_id := l_order_org_id;
3852 ELSE
3853 l_tm_deduction_org_id := l_net_accrual_offers.orig_org_id;
3854 END IF;
3855
3856 --ninarasi fix for bug 14798341
3857 --check gl date first time only if net accrual offer is org specific
3858 IF ((l_net_accrual_offers.orig_org_id IS NULL) OR (l_net_accrual_offers.orig_org_id IS NOT NULL AND l_counter = 0)) THEN
3859 l_counter := 1;
3860 l_gl_date_count := 0;
3861 OPEN c_gl_period(l_tm_deduction_org_id,l_gldate);
3862 FETCH c_gl_period INTO l_gl_date_count;
3863 CLOSE c_gl_period;
3864
3865 IF l_gl_date_count = 0 THEN
3866 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
3867 FND_MESSAGE.set_name('OZF', 'OZF_SETL_GLDATE_INVALID');
3868 FND_MSG_PUB.add;
3869 END IF;
3870 ozf_utility_pvt.write_conc_log;
3871 RAISE FND_API.G_EXC_ERROR;
3872 END IF;
3873 END IF;
3874
3875 IF G_DEBUG_LOW THEN
3876 ozf_utility_pvt.write_conc_log('l_inventory_item_id: ' || l_inventory_item_id);
3877 ozf_utility_pvt.write_conc_log('l_pricing_quantity: ' || l_pricing_quantity);
3878 ozf_utility_pvt.write_conc_log('l_pricing_quantity_uom: ' || l_pricing_quantity_uom);
3879 ozf_utility_pvt.write_conc_log('l_order_line_id: ' || l_order_line_id);
3880 ozf_utility_pvt.write_conc_log('l_order_org_id ' || l_order_org_id);
3881 END IF;
3882
3883 -- Fix for Bug 16301672
3884 IF l_order_line_id_old IS NOT NULL THEN
3885 IF l_order_line_id = l_order_line_id_old THEN
3886 l_tm_line.line_amount := l_tm_line.line_amount + l_line_amount_old ;
3887 l_reduce_line_amount := TRUE;
3888 END IF;
3889 END IF;
3890 l_line_amount_old := l_tm_line.line_amount ;
3891
3892 --[ Catch Weight Example
3893 -- Order Booked For 3 Case = 36 Ea = 360 Pounds
3894 -- Shipped with Catch Weight = 320 Pounds
3895 -- Accrual Offer : Discount - 10 Amount : UOM - Pounds
3896 -- Accrual for Accrual Offer = 3600 - 400 = 3200
3897 -- Net Accrual Offers For Deduction Rule:
3898 -- NE_CW_NETACC5 -- Discount - 10% : UOM - CS -- Accrual = 10% of 3200 = -320
3899 -- NE_CW_NETACC6 -- Discount - 10% : UOM - Pounds -- Accrual = 10% of 3200 = -320
3900 -- NE_CW_NETACC7 -- Discount - 10 AMT : UOM - CS -- Accrual = 10 AMT per Case = 10 * (3/360)* 320 = -26.67
3901 -- NE_CW_NETACC8 -- Discount - 10 AMT : UOM - Pounds -- Accrual = 10 AMOT per Pound = 10 * 320 = -3200]
3902
3903 l_line_acc_amount := get_accrualed_amount(p_product_id => l_inventory_item_id
3904 ,p_line_amt => l_tm_line.line_amount
3905 ,p_quantity => l_pricing_quantity
3906 ,p_uom => l_pricing_quantity_uom);
3907 /*l_line_acc_amount := get_accrualed_amount(p_product_id => l_tm_line.inventory_item_id
3908 ,p_line_amt => l_tm_line.line_amount
3909 ,p_quantity => l_tm_line.pricing_quantity
3910 ,p_uom => l_tm_line.pricing_quantity_uom);*/
3911
3912 IF G_DEBUG_LOW THEN
3913 ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (-) With l_line_acc_amount: '|| l_line_acc_amount);
3914 END IF;
3915
3916 l_tm_line.line_amount := l_line_acc_amount;
3917
3918 --
3919 IF l_net_accrual_offers.fund_request_curr_code <> l_tm_line.currency_code
3920 THEN
3921
3922 l_new_amount := 0;
3923 --Added for bug 7030415
3924 OPEN c_get_conversion_type(l_tm_line.org_id);
3925 FETCH c_get_conversion_type INTO l_exchange_rate_type;
3926 CLOSE c_get_conversion_type;
3927 ozf_utility_pvt.convert_currency(
3928 x_return_status => l_return_status
3929 ,p_from_currency => l_tm_line.currency_code
3930 ,p_to_currency => l_net_accrual_offers.fund_request_curr_code
3931 ,p_conv_type => l_exchange_rate_type
3932 --,p_conv_date => l_tm_line.conv_date
3933 ,p_conv_date => sysdate
3934 ,p_from_amount => l_tm_line.line_amount
3935 ,x_to_amount => l_tm_dedu_line_amt
3936 ,x_rate => l_rate);
3937 --
3938 IF l_return_status = Fnd_Api.g_ret_sts_error
3939 THEN
3940 --
3941 ozf_utility_pvt.write_conc_log('Convert currency ' || l_return_status);
3942 RAISE Fnd_Api.g_exc_error;
3943 --
3944 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
3945 THEN
3946 --
3947 ozf_utility_pvt.write_conc_log('Convert currency ' || l_return_status);
3948 RAISE Fnd_Api.g_exc_unexpected_error;
3949 --
3950 END IF;
3951 --added for bug 8688281
3952 ELSE
3953 l_tm_dedu_line_amt := l_tm_line.line_amount;
3954 END IF; --IF l_net_accrual_offers.fund_request_curr_code <> l_tm_line.currency_code
3955
3956 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
3957 RAISE Fnd_Api.g_exc_error;
3958 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3959 RAISE Fnd_Api.g_exc_unexpected_error;
3960 END IF;
3961
3962 -- Fix for Bug 16301672
3963 IF l_reduce_line_amount THEN
3964 l_tm_dedu_amount := l_tm_dedu_line_amt - l_tm_dedu_line_amt_old ;
3965 l_reduce_line_amount := FALSE ;
3966 ELSE
3967 l_tm_dedu_amount := l_tm_dedu_line_amt;
3968 END IF;
3969 l_tm_dedu_line_amt_old := l_tm_dedu_line_amt ;
3970
3971 IF l_order_line_id IS NOT NULL THEN
3972 l_order_line_id_old := l_order_line_id;
3973 END IF;
3974
3975 --
3976 IF G_DEBUG_LOW THEN
3977 ozf_utility_pvt.write_conc_log('l_tm_dedu_amount ' || l_tm_dedu_amount);
3978 ozf_utility_pvt.write_conc_log('l_inventory_item_id_old ' || l_inventory_item_id_old);
3979 ozf_utility_pvt.write_conc_log('l_inventory_item_id ' || l_inventory_item_id);
3980 ozf_utility_pvt.write_conc_log('l_index ' || l_index);
3981 END IF;
3982
3983 IF l_tm_dedu_amount <> 0 THEN
3984
3985 --if the new record is same as earlier one, then add the amounts
3986 -- nepanda : fix for bug 14291900 : Grouping the accruals for same products
3987 IF l_inventory_item_id_old IS NOT NULL AND l_inventory_item_id = l_inventory_item_id_old THEN
3988 l_act_budgets_rec_tbl(l_index-1).request_amount := l_act_budgets_rec_tbl(l_index-1).request_amount + (-1 * l_tm_dedu_amount);
3989 l_act_budgets_rec_tbl(l_index-1).approved_amount := l_act_budgets_rec_tbl(l_index-1).approved_amount + (-1 * l_tm_dedu_amount);
3990
3991 IF l_act_budgets_rec_tbl(l_index-1).parent_src_curr IS NOT NULL THEN
3992 l_act_budgets_rec_tbl(l_index-1).parent_src_apprvd_amt := l_act_budgets_rec_tbl(l_index-1).request_amount;
3993
3994 IF l_act_budgets_rec_tbl(l_index-1).parent_src_curr <> l_act_budgets_rec_tbl(l_index-1).request_currency THEN
3995 l_parent_src_apprvd_amt := 0;
3996
3997 ozf_utility_pvt.convert_currency (x_return_status => l_return_status
3998 ,p_from_currency => l_act_budgets_rec_tbl(l_index-1).request_currency
3999 ,p_to_currency => l_act_budgets_rec_tbl(l_index-1).parent_src_curr
4000 ,p_conv_date => l_sysdate
4001 ,p_from_amount => l_tm_dedu_amount
4002 ,x_to_amount => l_parent_src_apprvd_amt
4003 );
4004 l_act_budgets_rec_tbl(l_index-1).parent_src_apprvd_amt := l_act_budgets_rec_tbl(l_index-1).parent_src_apprvd_amt + (-1 * l_parent_src_apprvd_amt);
4005
4006 IF G_DEBUG_LOW THEN
4007 ozf_utility_pvt.write_conc_log('request_currency '|| l_act_budgets_rec_tbl(l_index).request_currency);
4008 END IF;
4009 END IF;
4010 END IF;
4011
4012 ELSE --IF l_inventory_item_id IS NOT NULL AND l_inventory_item_id = l_inventory_item_id_old THEN
4013
4014 --
4015 l_act_budgets_rec_tbl(l_index).act_budget_used_by_id := l_net_accrual_offers.qp_list_header_id;
4016 l_act_budgets_rec_tbl(l_index).arc_act_budget_used_by := 'OFFR';
4017 l_act_budgets_rec_tbl(l_index).budget_source_type := 'OFFR';
4018 l_act_budgets_rec_tbl(l_index).budget_source_id := l_net_accrual_offers.qp_list_header_id;
4019 l_act_budgets_rec_tbl(l_index).request_amount := -1 * l_tm_dedu_amount;
4020 l_act_budgets_rec_tbl(l_index).request_currency := l_net_accrual_offers.fund_request_curr_code;
4021 l_act_budgets_rec_tbl(l_index).request_date := l_sysdate;--nepanda : fix for bug 8766564
4022 l_act_budgets_rec_tbl(l_index).status_code := 'APPROVED';
4023 l_act_budgets_rec_tbl(l_index).approved_amount := -1 * l_tm_dedu_amount;
4024 l_act_budgets_rec_tbl(l_index).approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
4025 l_act_budgets_rec_tbl(l_index).approval_date := l_sysdate;--nepanda : fix for bug 8766564
4026 l_act_budgets_rec_tbl(l_index).approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
4027 l_act_budgets_rec_tbl(l_index).justification := 'NA: TM DEDUCTION' || TO_CHAR(l_sysdate, 'MON-DD-YYYY');
4028 l_act_budgets_rec_tbl(l_index).transfer_type := 'UTILIZED';
4029 l_act_budgets_rec_tbl(l_index).requester_id := l_net_accrual_offers.owner_id;
4030 IF l_net_accrual_offers.orig_org_id IS NULL THEN
4031 l_act_util_rec_tbl(l_index).org_id := l_order_org_id ;
4032 ELSE
4033 l_act_util_rec_tbl(l_index).org_id := l_net_accrual_offers.orig_org_id;
4034 END IF;
4035
4036
4037 l_act_util_rec_tbl(l_index).product_id := l_inventory_item_id; --l_tm_line.inventory_item_id;
4038 l_act_util_rec_tbl(l_index).order_line_id := l_order_line_id;
4039
4040 l_index := l_index + 1;
4041
4042 END IF; --IF l_inventory_item_id_old IS NOT NULL AND l_inventory_item_id = l_inventory_item_id_old THEN
4043
4044 -- nepanda : Fix for bug 14291900
4045 IF l_inventory_item_id IS NOT NULL THEN
4046 l_inventory_item_id_old := l_inventory_item_id;
4047 END IF;
4048
4049 END IF; --IF l_tm_dedu_amount <> 0 THEN
4050 END IF; --IF l_customer_qualified = 'Y'
4051
4052 END LOOP;--FOR l_tm_line IN c_tm_lines
4053
4054 IF G_DEBUG_LOW THEN
4055 ozf_utility_pvt.write_conc_log('l_act_budgets_rec_tbl.count ' || l_act_budgets_rec_tbl.count);
4056 END IF;
4057
4058 IF l_act_budgets_rec_tbl.count > 0 THEN
4059 FOR c IN l_act_budgets_rec_tbl.FIRST..l_act_budgets_rec_tbl.LAST
4060 LOOP
4061
4062 IF l_act_budgets_rec_tbl(c).request_amount <> 0 THEN
4063
4064 l_act_budgets_rec.act_budget_used_by_id := l_act_budgets_rec_tbl(c).act_budget_used_by_id;
4065 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
4066 l_act_budgets_rec.budget_source_type := 'OFFR';
4067 l_act_budgets_rec.budget_source_id := l_act_budgets_rec_tbl(c).budget_source_id;
4068 l_act_budgets_rec.request_amount := l_act_budgets_rec_tbl(c).request_amount;
4069
4070 IF G_DEBUG_LOW THEN
4071 ozf_utility_pvt.write_conc_log('l_act_budgets_rec.request_amount '|| l_act_budgets_rec.request_amount);
4072 ozf_utility_pvt.write_conc_log('l_act_budgets_rec_tbl(c).request_amount '|| l_act_budgets_rec_tbl(c).request_amount);
4073 ozf_utility_pvt.write_conc_log('l_act_budgets_rec.budget_source_id '|| l_act_budgets_rec.budget_source_id);
4074 ozf_utility_pvt.write_conc_log('l_act_budgets_rec.act_budget_used_by_id '|| l_act_budgets_rec.act_budget_used_by_id);
4075 END IF;
4076
4077 l_act_budgets_rec.request_currency := l_act_budgets_rec_tbl(c).request_currency;
4078 l_act_budgets_rec.request_date := l_sysdate;
4079 l_act_budgets_rec.status_code := 'APPROVED';
4080 l_act_budgets_rec.approved_amount := l_act_budgets_rec_tbl(c).approved_amount;
4081 l_act_budgets_rec.approved_in_currency := l_act_budgets_rec_tbl(c).approved_in_currency;
4082 l_act_budgets_rec.approval_date := l_sysdate;
4083 l_act_budgets_rec.approver_id := l_act_budgets_rec_tbl(c).approver_id;
4084 l_act_budgets_rec.justification := 'NA: TM DEDUCTION' || TO_CHAR(l_sysdate, 'MON-DD-YYYY');
4085 l_act_budgets_rec.transfer_type := 'UTILIZED';
4086 l_act_budgets_rec.requester_id := l_act_budgets_rec_tbl(c).requester_id;
4087 l_act_budgets_rec.parent_source_id := l_act_budgets_rec_tbl(c).parent_source_id;
4088 l_act_budgets_rec.parent_src_curr := l_act_budgets_rec_tbl(c).parent_src_curr;
4089 l_act_budgets_rec.parent_src_apprvd_amt := l_act_budgets_rec_tbl(c).parent_src_apprvd_amt;
4090
4091 l_act_util_rec.object_type := l_na_deduction_rule.transaction_type_code; -- OFFR
4092 l_act_util_rec.object_id := l_na_deduction_rule.deduction_identifier_id; -- activity_media_id
4093 l_act_util_rec.cust_account_id := l_net_accrual_offers.qualifier_id;
4094 l_act_util_rec.utilization_type := 'ACCRUAL';
4095 l_act_util_rec.adjustment_date := l_sysdate;--nepanda : fix for bug 8766564
4096 --ninarasi fix for bug 14798341 - assigned gl date
4097 l_act_util_rec.gl_date := l_gldate;--nepanda : fix for bug 8766564
4098
4099 --added for bug 8772550 otherwise CURSOR c_tm_lines will not pick up this utilization record
4100 l_act_util_rec.product_level_type := 'PRODUCT';
4101 l_act_util_rec.product_id := l_act_util_rec_tbl(c).product_id;
4102 l_act_util_rec.order_line_id := l_act_util_rec_tbl(c).order_line_id;
4103 l_act_util_rec.org_id := l_act_util_rec_tbl(c).org_id; --- nepanda forward port
4104
4105
4106 ozf_utility_pvt.write_conc_log('Accrual log: TM Deduction BATCH_MODE = Y');
4107 ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
4108 ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
4109 ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
4110 ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
4111 ozf_utility_pvt.write_conc_log('Cust Acct Id: '||l_act_util_rec.cust_account_id);
4112 --nirprasa,12.2 ER 8399135.
4113 l_act_util_rec.plan_currency_code := l_net_accrual_offers.fund_request_curr_code;
4114 l_act_util_rec.fund_request_currency_code := l_net_accrual_offers.fund_request_curr_code;
4115 l_act_util_rec.fund_request_amount := -1 * l_tm_dedu_amount;
4116 l_act_util_rec.fund_request_amount_remaining := -1 * l_tm_dedu_amount;
4117 --nirprasa,12.2
4118
4119
4120 ozf_fund_adjustment_pvt.process_act_budgets(x_return_status => l_return_status
4121 ,x_msg_count => l_msg_count
4122 ,x_msg_data => l_msg_data
4123 ,p_act_budgets_rec => l_act_budgets_rec
4124 ,p_act_util_rec => l_act_util_rec
4125 ,x_act_budget_id => l_act_budget_id
4126 ,x_utilized_amount => l_utilized_amount);
4127
4128 IF G_DEBUG_LOW THEN
4129 --ozf_utility_pvt.write_conc_log('Req Curr/Req Amt/Util Amt: ' || l_act_budgets_rec.request_currency || '/' || l_act_budgets_rec.request_amount || '/' || l_utilized_amount);
4130 ozf_utility_pvt.write_conc_log('Req Curr/Req Amt/Util Amt: ' || l_act_budgets_rec_tbl(c).request_currency || '/' || l_act_budgets_rec_tbl(c).request_amount || '/' || l_utilized_amount);
4131 END IF;
4132
4133 l_utilized_amount := 0;
4134
4135 ozf_utility_pvt.write_conc_log('Msg from Budget API : '||l_msg_data);
4136
4137 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
4138 ozf_utility_pvt.write_conc_log('Exception : Msg from Budget API 7 : '||l_msg_data);
4139 log_exception(l_act_budgets_rec, l_act_util_rec);
4140 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
4141 ozf_utility_pvt.write_conc_log('UnException : Msg from Budget API 7 : '||l_msg_data);
4142 log_exception(l_act_budgets_rec, l_act_util_rec);
4143 END IF;
4144
4145 END IF; --IF l_act_budgets_rec_tbl(c).request_amount <> 0 THEN
4146
4147 END LOOP; --FOR c IN l_act_budgets_rec_tbl.FIRST..l_act_budgets_rec_tbl.LAST
4148
4149 END IF; --IF l_act_budgets_rec_tbl.count > 0 THEN
4150
4151 -- nepanda added for bug # 10261022
4152 l_act_budgets_rec_tbl.DELETE;
4153
4154
4155 IF G_DEBUG_LOW THEN
4156 ozf_utility_pvt.write_conc_log('Accrual log: TM Deduction BATCH_MODE = Y');
4157 ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
4158 ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
4159 ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
4160 ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
4161 ozf_utility_pvt.write_conc_log('Cust Acct Id: '||l_act_util_rec.cust_account_id);
4162 END IF; -- end amount <> 0
4163
4164 l_act_budgets_rec := NULL;
4165 l_act_util_rec := NULL;
4166
4167 END IF; --ELSIF l_na_deduction_rule.transaction_source_code = 'TM' THEN
4168
4169 END LOOP; -- end l_na_rule_line
4170
4171 ozf_utility_pvt.write_conc_log('-- Done Processing Deduction Rules -- ');
4172 ozf_utility_pvt.write_conc_log('--------------------------------------');
4173
4174 IF l_net_accrual_offers.latest_na_completion_date IS NULL OR l_net_accrual_offers.latest_na_completion_date < l_as_of_date THEN
4175 UPDATE ozf_offers
4176 SET latest_na_completion_date = l_as_of_date
4177 WHERE offer_id = l_net_accrual_offers.offer_id;
4178 END IF;
4179
4180 <<IDSM>>
4181 --------------- Start Processing IDSM lines ------------------------
4182 IF l_net_accrual_offers.sales_method_flag IS NULL OR l_net_accrual_offers.sales_method_flag = 'I' THEN
4183 --
4184 ozf_utility_pvt.write_conc_log('Start Processing IDSM Lines');
4185 l_idsm_line_tbl.delete;
4186 l_accrual_amount := 0;
4187
4188 OPEN c_idsm_line(l_net_accrual_offers.start_date_active, l_net_accrual_offers.end_date_active, l_offer_org_id, l_net_accrual_offers.resale_line_id_processed);
4189
4190 LOOP
4191 --
4192 FETCH c_idsm_line BULK COLLECT INTO l_idsm_line_tbl LIMIT l_batch_size;
4193 --
4194 -- To handle NO DATA FOUND for c_idsm_line CURSOR
4195 IF l_idsm_line_tbl.FIRST IS NULL
4196 THEN
4197 --
4198 ozf_utility_pvt.write_conc_log('No Data found in c_idsm_line CURSOR');
4199 EXIT;
4200 --
4201 END IF;
4202 --
4203 -- Logic to exit after all the record have been processed
4204 -- is just before the END LOOP EXIT WHEN c_idsm_line%NOTFOUND;
4205
4206 l_counter := 0;
4207 ---------------------------------------------------------
4208 FOR i IN l_idsm_line_tbl.FIRST .. l_idsm_line_tbl.LAST
4209 LOOP
4210 ---------------------------------------------------------
4211 --
4212
4213 --ninarasi fix for bug 14798341
4214 --check gl date first time only if net accrual offer is org specific
4215 IF ((l_offer_org_id IS NULL) OR (l_offer_org_id IS NOT NULL AND l_counter = 0)) THEN
4216 l_counter := 1;
4217 l_gl_date_count := 0;
4218 OPEN c_gl_period(l_idsm_line_tbl(i).org_id, l_gldate);
4219 FETCH c_gl_period INTO l_gl_date_count;
4220 CLOSE c_gl_period;
4221
4222 IF l_gl_date_count = 0 THEN
4223 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_error) THEN
4224 FND_MESSAGE.set_name('OZF', 'OZF_SETL_GLDATE_INVALID');
4225 FND_MSG_PUB.add;
4226 END IF;
4227 ozf_utility_pvt.write_conc_log;
4228 RAISE FND_API.G_EXC_ERROR;
4229 END IF;
4230 END IF;
4231
4232 l_return_status := FND_API.g_ret_sts_success;
4233
4234 l_idsm_line_processed := l_idsm_line_tbl(i).line_id;
4235
4236 IF G_DEBUG_LOW THEN
4237 ozf_utility_pvt.write_conc_log('Resale Line_Id: ' || l_idsm_line_tbl(i).line_id);
4238 END IF;
4239
4240 l_line_amount := ( NVL(l_idsm_line_tbl(i).shipped_quantity,l_idsm_line_tbl(i).fulfilled_quantity)
4241 * l_idsm_line_tbl(i).unit_selling_price );
4242 --
4243 ------------- Qualify Customer on the IDSM line ------------------------------
4244 --
4245
4246 IF l_net_accrual_offers.custom_setup_id = 105
4247 THEN
4248 ----- For PV Net Accrual Offers, do not look at denorm -------
4249 ----- Get Country code from the Identifying addresss of the Customer
4250 OPEN c_country_code(l_idsm_line_tbl(i).invoice_to_org_id);
4251 FETCH c_country_code INTO l_country_code;
4252 CLOSE c_country_code;
4253
4254 -- l_terr_countries_tbl has all the countries eligible for this offer
4255 -- This table is populated in the 'Denorm Customers' section for each PV NA Offer
4256 l_customer_qualified := 'N';
4257
4258 FOR j IN l_terr_countries_tbl.FIRST .. l_terr_countries_tbl.LAST
4259 LOOP
4260 --
4261 IF l_country_code = l_terr_countries_tbl(j)
4262 THEN
4263 l_customer_qualified := 'Y';
4264 EXIT;
4265 END IF;
4266 --
4267 END LOOP;
4268
4269 IF l_customer_qualified = 'N' THEN
4270 -- sold_to not qualified. try ship_to
4271 OPEN c_country_code(l_idsm_line_tbl(i).ship_to_org_id);
4272 FETCH c_country_code INTO l_country_code;
4273 CLOSE c_country_code;
4274
4275 FOR j IN l_terr_countries_tbl.FIRST .. l_terr_countries_tbl.LAST
4276 LOOP
4277 --
4278 IF l_country_code = l_terr_countries_tbl(j)
4279 THEN
4280 l_customer_qualified := 'Y';
4281 EXIT;
4282 END IF;
4283 --
4284 END LOOP;
4285 --
4286 END IF;
4287 --
4288 ELSE
4289 ----- For all other Net Accrual offers, look at denorm -------
4290 l_customer_qualified := validate_customer(p_invoice_to_org_id => l_idsm_line_tbl(i).invoice_to_org_id
4291 ,p_ship_to_org_id => l_idsm_line_tbl(i).ship_to_org_id
4292 ,p_sold_to_org_id => l_idsm_line_tbl(i).sold_to_org_id);
4293 --
4294 END IF; -- Done qualfiying the customer
4295
4296 IF G_DEBUG_LOW THEN
4297 ozf_utility_pvt.write_conc_log('Did Customer qualify: ' || l_customer_qualified);
4298 END IF;
4299
4300 -- Fetch Currency Code on the IDSM
4301 l_order_curr_code := l_idsm_line_tbl(i).transactional_curr_code ;
4302
4303 IF l_customer_qualified = 'Y'
4304 THEN
4305 --
4306 IF l_net_accrual_offers.fund_request_curr_code <> l_order_curr_code
4307 THEN
4308 --
4309 l_new_amount := 0;
4310 --Added for bug 7030415
4311 OPEN c_get_conversion_type(l_idsm_line_tbl(i).org_id);
4312 FETCH c_get_conversion_type INTO l_exchange_rate_type;
4313 CLOSE c_get_conversion_type;
4314 ozf_utility_pvt.convert_currency(x_return_status => l_return_status
4315 ,p_from_currency => l_order_curr_code
4316 ,p_to_currency => l_net_accrual_offers.fund_request_curr_code
4317 ,p_conv_type => l_exchange_rate_type
4318 --,p_conv_date => l_idsm_line_tbl(i).conv_date
4319 ,p_conv_date => sysdate
4320 ,p_from_amount => l_line_amount
4321 ,x_to_amount => l_new_amount
4322 ,x_rate => l_rate);
4323 --nirprasa,12.2 ER 8399135.
4324 IF l_net_accrual_offers.transaction_currency_code IS NOT NULL
4325 OR l_batch_mode = 'YES' THEN
4326 l_line_amount := l_new_amount;
4327 END IF;
4328
4329 IF l_return_status = Fnd_Api.g_ret_sts_error
4330 THEN
4331 ozf_utility_pvt.write_conc_log('Exp Error from Convert_Currency: ' || l_return_status);
4332 RAISE Fnd_Api.g_exc_error;
4333 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
4334 THEN
4335 ozf_utility_pvt.write_conc_log('Unexp Error from Convert_Currency: ' || l_return_status);
4336 RAISE Fnd_Api.g_exc_unexpected_error;
4337 END IF;
4338 --
4339 END IF;
4340
4341 ------------------------------ Derive Benificiary -----------------------
4342 IF l_net_accrual_offers.custom_setup_id = 105
4343 THEN
4344 --
4345 IF G_DEBUG_LOW THEN
4346 ozf_utility_pvt.write_conc_log('Pv_Referral_Comp_Pub.Get_Beneficiary (+)');
4347 END IF;
4348 pv_referral_comp_pub.get_beneficiary (p_api_version => 1.0,
4349 p_init_msg_list => FND_API.g_true,
4350 p_commit => FND_API.g_false,
4351 p_validation_level => FND_API.g_valid_level_full,
4352 p_order_header_id => l_idsm_line_tbl(i).header_id,
4353 p_order_line_id => l_idsm_line_tbl(i).line_id,
4354 p_offer_id => l_net_accrual_offers.offer_id,
4355 x_beneficiary_id => l_beneficiary_id,
4356 x_referral_id => l_referral_id,
4357 x_return_status => l_return_status,
4358 x_msg_count => l_msg_count,
4359 x_msg_data => l_msg_data);
4360 IF G_DEBUG_LOW THEN
4361 ozf_utility_pvt.write_conc_log('Pv_Referral_Comp_Pub.Get_Beneficiary (-) With Status: ' || l_return_status);
4362 ozf_utility_pvt.write_conc_log('l_benificiary_id / l_referral_id: ' || l_beneficiary_id || ' / ' || l_referral_id);
4363 END IF;
4364
4365 IF l_return_status = Fnd_Api.g_ret_sts_error
4366 THEN
4367 ozf_utility_pvt.write_conc_log('Exp Error from Get_Beneficiary: ' || l_return_status);
4368 RAISE Fnd_Api.g_exc_error;
4369 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
4370 THEN
4371 ozf_utility_pvt.write_conc_log('Unexp Error from Get_Beneficiary: ' || l_return_status);
4372 RAISE Fnd_Api.g_exc_unexpected_error;
4373 END IF;
4374 --
4375
4376 IF ( l_beneficiary_id IS NOT NULL )
4377 THEN
4378 --------------------------- Derive Accrual Amount -------------------------
4379 IF G_DEBUG_LOW THEN
4380 ozf_utility_pvt.write_conc_log('Get_Pv_Accrual_Amount (+)');
4381 END IF;
4382
4383 l_line_acc_amount := get_pv_accrual_amount(p_product_id => l_idsm_line_tbl(i).inventory_item_id
4384 ,p_line_amt => l_line_amount
4385 ,p_offer_id => l_net_accrual_offers.offer_id
4386 ,p_org_id => l_org_id
4387 ,p_list_hdr_id => l_net_accrual_offers.qp_list_header_id
4388 ,p_referral_id => l_referral_id
4389 ,p_order_hdr_id => l_idsm_line_tbl(i).header_id);
4390 IF G_DEBUG_LOW THEN
4391 ozf_utility_pvt.write_conc_log('Get_Pv_Accrual_Amount (-) With l_line_acc_amount: ' || l_line_acc_amount);
4392 END IF;
4393 --
4394 ELSE
4395 --
4396 ozf_utility_pvt.write_conc_log('No Beneficiary derived from PV_Referral_Comp_Pub. Utilization will not be created');
4397 --
4398 END IF;
4399 --
4400 l_utilization_type := 'LEAD_ACCRUAL';
4401 l_reference_type := 'LEAD_REFERRAL';
4402 --
4403 ELSE
4404 --
4405 --------------------------- Derive Accrual Amount -------------------------
4406 IF G_DEBUG_LOW THEN
4407 ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (+)');
4408 END IF;
4409 l_line_acc_amount := get_accrualed_amount(p_product_id => l_idsm_line_tbl(i).inventory_item_id
4410 ,p_line_amt => l_line_amount
4411 ,p_quantity => l_idsm_line_tbl(i).pricing_quantity
4412 ,p_uom => l_idsm_line_tbl(i).pricing_quantity_uom);
4413 IF G_DEBUG_LOW THEN
4414 ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (-) With l_line_acc_amount: ' || l_line_acc_amount);
4415 END IF;
4416 --
4417
4418 --
4419 l_utilization_type := 'ACCRUAL';
4420 l_reference_type := NULL;
4421 l_beneficiary_id := l_net_accrual_offers.qualifier_id;
4422 l_referral_id := NULL;
4423 --
4424 END IF; -- End custom_setup_id 105
4425
4426 IF l_batch_mode = 'NO'
4427 THEN
4428 --
4429 IF ( l_beneficiary_id IS NULL
4430 OR
4431 l_beneficiary_id = fnd_api.g_miss_num )
4432 THEN
4433 --
4434 -- Benificiay Id can be NULL only for PV Net Accrual Offers
4435 -- If PV decides not to accrue for this customer, it returns NULL
4436 --
4437 NULL;
4438 ELSE
4439 --
4440 l_act_budgets_rec.act_budget_used_by_id := l_net_accrual_offers.qp_list_header_id;
4441 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
4442 l_act_budgets_rec.budget_source_type := 'OFFR';
4443 l_act_budgets_rec.budget_source_id := l_net_accrual_offers.qp_list_header_id;
4444 l_act_budgets_rec.request_amount := l_line_acc_amount;
4445 --nirprasa,12.2 ER 8399135. l_act_budgets_rec.request_currency := l_net_accrual_offers.fund_request_curr_code;
4446 IF l_net_accrual_offers.transaction_currency_code IS NULL THEN
4447 l_act_budgets_rec.request_currency := l_idsm_line_tbl(i).transactional_curr_code;
4448 ELSE
4449 l_act_budgets_rec.request_currency := l_net_accrual_offers.transaction_currency_code;
4450 END IF;
4451 l_act_util_rec.plan_currency_code := l_act_budgets_rec.request_currency;
4452 l_act_util_rec.fund_request_currency_code := l_net_accrual_offers.fund_request_curr_code;
4453 --nirprasa,12.2 ER 8399135.
4454 l_act_budgets_rec.request_date := l_sysdate;--nepanda : fix for bug 8766564
4455 l_act_budgets_rec.status_code := 'APPROVED';
4456 l_act_budgets_rec.approved_amount := l_line_acc_amount;
4457 l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
4458 l_act_budgets_rec.approval_date := l_sysdate;--nepanda : fix for bug 8766564
4459 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
4460 l_act_budgets_rec.justification := 'NA: ' || TO_CHAR(l_sysdate, 'MM/DD/YYYY');
4461 l_act_budgets_rec.transfer_type := 'UTILIZED';
4462 l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
4463
4464 l_act_util_rec.object_type := 'TP_ORDER';
4465 l_act_util_rec.object_id := l_idsm_line_tbl(i).line_id;
4466 l_act_util_rec.product_level_type := 'PRODUCT';
4467 l_act_util_rec.product_id := l_idsm_line_tbl(i).inventory_item_id;
4468 l_act_util_rec.cust_account_id := l_beneficiary_id;
4469 l_act_util_rec.utilization_type := l_utilization_type;
4470 l_act_util_rec.adjustment_date := l_sysdate;--nepanda : fix for bug 8766564
4471 --ninarasi fix for bug 14798341 - assigned gl date
4472 l_act_util_rec.gl_date := l_gldate;--nepanda : fix for bug 8766564
4473 --ninarasi fix for bug 15991204. Changed invoice_to_org_id to sold_to_org_id
4474 l_act_util_rec.billto_cust_account_id := l_idsm_line_tbl(i).sold_to_org_id;
4475 l_act_util_rec.reference_type := l_reference_type;
4476 l_act_util_rec.reference_id := l_referral_id;
4477 l_act_util_rec.order_line_id := l_idsm_line_tbl(i).line_id;
4478 l_act_util_rec.org_id := l_idsm_line_tbl(i).org_id;
4479
4480 -- Bug 3463302. Do not create utilization if amount is zero
4481 IF l_act_budgets_rec.request_amount <> 0
4482 THEN
4483 --
4484 ozf_fund_adjustment_pvt.process_act_budgets(x_return_status => l_return_status
4485 ,x_msg_count => l_msg_count
4486 ,x_msg_data => l_msg_data
4487 ,p_act_budgets_rec => l_act_budgets_rec
4488 ,p_act_util_rec => l_act_util_rec
4489 ,x_act_budget_id => l_act_budget_id
4490 ,x_utilized_amount => l_utilized_amount);
4491 --
4492 IF G_DEBUG_LOW THEN
4493 ozf_utility_pvt.write_conc_log('Req Curr/Req Amt/Util Amt: ' || l_act_budgets_rec.request_currency || '/' || l_act_budgets_rec.request_amount || '/' || l_utilized_amount);
4494 END IF;
4495
4496 IF l_return_status = Fnd_Api.g_ret_sts_error
4497 THEN
4498 ozf_utility_pvt.write_conc_log('Exp Error 8 : Process_Act_Budgets: Resale line_id ( ' || l_idsm_line_tbl(i).line_id
4499 || ' ) Error: ' || l_msg_data);
4500 log_exception(l_act_budgets_rec, l_act_util_rec);
4501 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
4502 THEN
4503 ozf_utility_pvt.write_conc_log('UnExp Error 8 : Process_Act_Budgets: Resale line_id ( ' || l_idsm_line_tbl(i).line_id
4504 || ' ) Error: ' || l_msg_data);
4505 log_exception(l_act_budgets_rec, l_act_util_rec);
4506 END IF;
4507
4508 l_utilized_amount := 0;
4509 --
4510 END IF; -- end amount <> 0
4511
4512 l_act_budgets_rec := NULL;
4513 l_act_util_rec := NULL;
4514 --
4515 END IF; -- End beneficiary is Not Null
4516
4517 -- End Batch Mode = NO
4518 ELSE
4519 -- If Batch Mode = YES, accumulate accrual.
4520 l_accrual_amount := l_accrual_amount + l_line_acc_amount;
4521 --
4522 END IF; -- End Batch Mode Check
4523 --
4524 END IF; -- Customer Qualfied = 'Y'
4525
4526 -----------------------------------------------------
4527 END LOOP; -- l_idsm_line_tbl
4528 -----------------------------------------------------
4529 --
4530 EXIT WHEN c_idsm_line%NOTFOUND;
4531 --
4532 END LOOP; -- IDSM lines Cursor
4533
4534 CLOSE c_idsm_line;
4535
4536 IF l_batch_mode = 'YES'
4537 THEN
4538 --
4539 IF l_accrual_amount <> 0
4540 THEN
4541 --
4542 l_beneficiary_id := l_net_accrual_offers.qualifier_id;
4543 l_utilization_type := 'ACCRUAL';
4544 l_reference_type := NULL;
4545 l_referral_id := NULL;
4546
4547 IF l_beneficiary_id IS NULL OR l_beneficiary_id = fnd_api.g_miss_num
4548 THEN
4549 -- This condition will never occur.
4550 -- For PV offers, the Batch Mode is always NO and Beneficiary is always required
4551 -- for a Net Accrual Offer.
4552 NULL;
4553 --
4554 ELSE
4555 --
4556 l_act_budgets_rec.act_budget_used_by_id := l_net_accrual_offers.qp_list_header_id;
4557 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
4558 l_act_budgets_rec.budget_source_type := 'OFFR';
4559 l_act_budgets_rec.budget_source_id := l_net_accrual_offers.qp_list_header_id;
4560 l_act_budgets_rec.request_amount := l_accrual_amount;
4561 l_act_budgets_rec.request_currency := l_net_accrual_offers.fund_request_curr_code;
4562 l_act_budgets_rec.request_date := l_sysdate;--nepanda : fix for bug 8766564
4563 l_act_budgets_rec.status_code := 'APPROVED';
4564 l_act_budgets_rec.approved_amount := l_accrual_amount;
4565 l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
4566 l_act_budgets_rec.approval_date := l_sysdate;--nepanda : fix for bug 8766564
4567 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
4568 l_act_budgets_rec.justification := 'NA: ' || TO_CHAR(l_sysdate, 'MM/DD/YYYY');
4569 l_act_budgets_rec.transfer_type := 'UTILIZED';
4570 l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
4571
4572 l_act_util_rec.cust_account_id := l_beneficiary_id;
4573 l_act_util_rec.utilization_type := l_utilization_type;
4574 l_act_util_rec.adjustment_date := l_sysdate;--nepanda : fix for bug 8766564
4575 --ninarasi fix for bug 14798341 - assigned gl date
4576 l_act_util_rec.gl_date := l_gldate;--nepanda : fix for bug 8766564
4577 l_act_util_rec.reference_type := l_reference_type;
4578 l_act_util_rec.reference_id := l_referral_id;
4579 --nirprasa,12.2 ER 8399135.
4580 l_act_util_rec.plan_currency_code := l_net_accrual_offers.fund_request_curr_code;
4581 l_act_util_rec.fund_request_amount := l_accrual_amount;
4582 l_act_util_rec.fund_request_amount_remaining := l_accrual_amount;
4583 l_act_util_rec.fund_request_currency_code := l_net_accrual_offers.fund_request_curr_code;
4584 --nirprasa,12.2
4585
4586 ozf_fund_adjustment_pvt.process_act_budgets(x_return_status => l_return_status
4587 ,x_msg_count => l_msg_count
4588 ,x_msg_data => l_msg_data
4589 ,p_act_budgets_rec => l_act_budgets_rec
4590 ,p_act_util_rec => l_act_util_rec
4591 ,x_act_budget_id => l_act_budget_id
4592 ,x_utilized_amount => l_utilized_amount);
4593
4594 IF G_DEBUG_LOW THEN
4595 ozf_utility_pvt.write_conc_log('Req Curr/Req Amt/Util Amt: ' || l_act_budgets_rec.request_currency || '/' || l_act_budgets_rec.request_amount || '/' || l_utilized_amount);
4596 END IF;
4597
4598 IF l_return_status = Fnd_Api.g_ret_sts_error
4599 THEN
4600 ozf_utility_pvt.write_conc_log('Exp Error 9 : Process_Act_Budgets Error: ' || l_msg_data );
4601 log_exception(l_act_budgets_rec, l_act_util_rec);
4602 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
4603 THEN
4604 ozf_utility_pvt.write_conc_log('UnExp Error 9 : Process_Act_Budgets Error: ' || l_msg_data );
4605 log_exception(l_act_budgets_rec, l_act_util_rec);
4606 END IF;
4607
4608 l_utilized_amount := 0;
4609 l_act_budgets_rec := NULL;
4610 l_act_util_rec := NULL;
4611 --
4612 END IF; -- End check beneficiary id
4613 --
4614 END IF; -- end l_accrual_amount <> 0
4615 --
4616 END IF; -- end l_batch_mode = 'YES'
4617
4618 UPDATE ozf_offers
4619 SET resale_line_id_processed = l_idsm_line_processed
4620 WHERE offer_id = l_net_accrual_offers.offer_id;
4621 --
4622 END IF; -- End IDSM lines
4623
4624
4625
4626 --AMITAMKU bug 14692296 - added BEGIN/EXCEPTION/END block for each net accrual offer so that program can continue for next offer if one offer fails
4627 EXCEPTION
4628
4629 WHEN FND_API.G_EXC_ERROR THEN
4630 ROLLBACK TO net_accrual_offer;
4631 -- Standard call to get message count and if count=1, get the message
4632 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4633 p_count => l_msg_count,
4634 p_data => l_msg_data);
4635
4636 ERRBUF := l_msg_data;
4637 RETCODE := '1';
4638
4639 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4640 ROLLBACK TO net_accrual_offer;
4641 -- Standard call to get message count and if count=1, get the message
4642 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4643 p_count => l_msg_count,
4644 p_data => l_msg_data);
4645
4646 ERRBUF := l_msg_data;
4647 RETCODE := '1';
4648
4649 WHEN OTHERS THEN
4650 ROLLBACK TO net_accrual_offer;
4651 -- Standard call to get message count and if count=1, get the message
4652 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4653 p_count => l_msg_count,
4654 p_data => l_msg_data);
4655 --ERRBUF := l_msg_data;
4656 ERRBUF := SQLERRM;
4657 RETCODE := '1';
4658 END; --End of BEGIN/EXCEPTION/END Block
4659
4660 <<NEXT_OFFER>>
4661 ozf_utility_pvt.write_conc_log('-- NEXT_OFFER -- ');
4662 END LOOP; --FOR l_net_accrual_offers IN c_net_accrual_offers
4663
4664 --AMITAMKU bug 14692296 - moved truncate out of the loop to end of the program
4665 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.ozf_na_customers_temp';
4666 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.ozf_na_products_temp';
4667 EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || l_schema || '.OZF_NA_UNIQUE_PRODUCTS_TEMP';
4668
4669 ozf_utility_pvt.write_conc_log('-- Done -- ' || to_char(sysdate,'MM/DD/YYYY:HH:MM:SS'));
4670
4671 Fnd_Msg_Pub.Count_AND_Get(p_count => l_msg_count,
4672 p_data => l_msg_data,
4673 p_encoded => Fnd_Api.G_FALSE);
4674
4675 EXCEPTION
4676
4677 WHEN OZF_Utility_PVT.resource_locked THEN
4678 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
4679
4680 WHEN FND_API.G_EXC_ERROR THEN
4681 --ROLLBACK TO net_accrual_engine;
4682 ROLLBACK;
4683 -- Standard call to get message count and if count=1, get the message
4684 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4685 p_count => l_msg_count,
4686 p_data => l_msg_data);
4687
4688 ERRBUF := l_msg_data;
4689 RETCODE := '2';
4690
4691 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4692 ROLLBACK ;
4693 -- Standard call to get message count and if count=1, get the message
4694 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4695 p_count => l_msg_count,
4696 p_data => l_msg_data);
4697
4698 ERRBUF := l_msg_data;
4699 RETCODE := '2';
4700
4701 WHEN OTHERS THEN
4702 ROLLBACK ;
4703 -- Standard call to get message count and if count=1, get the message
4704 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
4705 p_count => l_msg_count,
4706 p_data => l_msg_data);
4707 --ERRBUF := l_msg_data;
4708 ERRBUF := SQLERRM;
4709 RETCODE := '2';
4710
4711 END net_accrual_engine;
4712
4713
4714 /****
4715 -- Redundate procedure. Remove the call from the accrual engine
4716 ****/
4717
4718 PROCEDURE retroactive_offer_adj(
4719 p_api_version IN NUMBER
4720 ,p_init_msg_list IN VARCHAR2
4721 ,p_commit IN VARCHAR2
4722 ,x_return_status OUT NOCOPY VARCHAR2
4723 ,x_msg_count OUT NOCOPY NUMBER
4724 ,x_msg_data OUT NOCOPY VARCHAR2
4725 ,p_offer_id IN NUMBER
4726 ,p_start_date IN DATE
4727 ,p_end_date IN DATE
4728 ,x_order_line_tbl OUT NOCOPY order_line_tbl_type)
4729 IS
4730 --
4731 CURSOR c_offer_type IS
4732 SELECT offer_type,
4733 tier_level,
4734 qp_list_header_id,
4735 custom_setup_id
4736 FROM ozf_offers
4737 WHERE offer_id = p_offer_id;
4738
4739 CURSOR c_order_line_detail1 IS
4740 SELECT a.*
4741 FROM oe_order_lines_all a
4742 WHERE TRUNC(NVL(a.actual_shipment_date,a.fulfillment_date))
4743 BETWEEN TRUNC(p_start_date) AND TRUNC(p_end_date)
4744 AND a.flow_status_code IN ('SHIPPED','CLOSED')
4745 AND a.cancelled_flag = 'N'
4746 AND a.line_category_code <> 'RETURN';
4747
4748 l_offer_type VARCHAR2(30);
4749 l_tier_level VARCHAR2(30);
4750 l_country_code VARCHAR2(60);
4751 l_qp_list_header_id NUMBER;
4752 l_customer_qualified VARCHAR2(1);
4753 l_product_qualified VARCHAR2(1);
4754 l_tbl_index NUMBER := 1;
4755 l_api_name CONSTANT VARCHAR2(30) := 'retroactive_offer_adj';
4756 l_custom_setup_id NUMBER;
4757
4758 BEGIN
4759
4760 SAVEPOINT retroactive_offer_adj;
4761
4762 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
4763 Fnd_Msg_Pub.initialize;
4764 END IF;
4765
4766 x_return_status := Fnd_Api.g_ret_sts_success;
4767
4768 OPEN c_offer_type;
4769 FETCH c_offer_type INTO l_offer_type, l_tier_level, l_qp_list_header_id, l_custom_setup_id;
4770 CLOSE c_offer_type;
4771
4772 FOR l_order_line_detail1 IN c_order_line_detail1 LOOP
4773 l_customer_qualified := validate_customer(p_invoice_to_org_id => l_order_line_detail1.invoice_to_org_id
4774 ,p_ship_to_org_id => l_order_line_detail1.ship_to_org_id
4775 ,p_sold_to_org_id => l_order_line_detail1.sold_to_org_id
4776 ,p_qp_list_header_id => l_qp_list_header_id);
4777
4778 l_product_qualified := validate_product(p_inventory_item_id => l_order_line_detail1.inventory_item_id
4779 ,p_qp_list_header_id => l_qp_list_header_id);
4780
4781 IF l_customer_qualified = 'Y' AND l_product_qualified = 'Y' THEN
4782 x_order_line_tbl(l_tbl_index).order_header_id := l_order_line_detail1.header_id;
4783 x_order_line_tbl(l_tbl_index).order_line_id := l_order_line_detail1.line_id;
4784 l_tbl_index := l_tbl_index + 1;
4785 END IF;
4786 END LOOP;
4787
4788 EXCEPTION
4789 WHEN Fnd_Api.G_EXC_ERROR THEN
4790 x_return_status := Fnd_Api.g_ret_sts_error;
4791 ROLLBACK TO retroactive_offer_adj;
4792 Fnd_Msg_Pub.Count_AND_Get
4793 ( p_count => x_msg_count,
4794 p_data => x_msg_data,
4795 p_encoded => Fnd_Api.G_FALSE
4796 );
4797 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
4798 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
4799 ROLLBACK TO retroactive_offer_adj;
4800 Fnd_Msg_Pub.Count_AND_Get
4801 ( p_count => x_msg_count,
4802 p_data => x_msg_data,
4803 p_encoded => Fnd_Api.G_FALSE
4804 );
4805 WHEN OTHERS THEN
4806 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
4807 ROLLBACK TO retroactive_offer_adj;
4808 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
4809 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4810 END IF;
4811 Fnd_Msg_Pub.Count_AND_Get
4812 ( p_count => x_msg_count,
4813 p_data => x_msg_data,
4814 p_encoded => Fnd_Api.G_FALSE
4815 );
4816
4817 END retroactive_offer_adj;
4818
4819
4820 PROCEDURE offer_adj_new_product(
4821 p_api_version IN NUMBER
4822 ,p_init_msg_list IN VARCHAR2
4823 ,p_commit IN VARCHAR2
4824 ,x_return_status OUT NOCOPY VARCHAR2
4825 ,x_msg_count OUT NOCOPY NUMBER
4826 ,x_msg_data OUT NOCOPY VARCHAR2
4827 ,p_offer_id IN NUMBER
4828 ,p_product_id IN NUMBER
4829 ,p_start_date IN DATE
4830 ,p_end_date IN DATE
4831 ,x_order_line_tbl OUT NOCOPY order_line_tbl_type)
4832 IS
4833
4834 l_header_id_tbl number_tbl_type;
4835 l_line_id_tbl number_tbl_type;
4836 l_invoice_to_org_id_tbl number_tbl_type;
4837 l_ship_to_org_id_tbl number_tbl_type;
4838 l_sold_to_org_id_tbl number_tbl_type;
4839
4840 CURSOR c_offer_type IS
4841 SELECT offer_type, tier_level, qp_list_header_id, custom_setup_id
4842 FROM ozf_offers
4843 WHERE offer_id = p_offer_id;
4844
4845 CURSOR c_order_line IS
4846 SELECT a.header_id,
4847 a.line_id,
4848 a.invoice_to_org_id,
4849 a.ship_to_org_id,
4850 a.sold_to_org_id
4851 FROM oe_order_lines_all a
4852 WHERE (NVL(a.actual_shipment_date,a.fulfillment_date)) BETWEEN p_start_date AND p_end_date
4853 -- AND a.flow_status_code IN ('SHIPPED','CLOSED')
4854 AND a.booked_flag = 'Y'
4855 AND a.cancelled_flag = 'N'
4856 AND a.line_category_code <> 'RETURN'
4857 AND a.inventory_item_id = p_product_id;
4858
4859 l_order_line_tbl t_order_line_tbl;
4860 l_batch_size NUMBER := 1000;
4861
4862 l_offer_type VARCHAR2(30);
4863 l_tier_level VARCHAR2(30);
4864 l_qp_list_header_id NUMBER;
4865 l_return_status VARCHAR2(1);
4866 l_msg_count NUMBER;
4867 l_msg_data VARCHAR2(2000);
4868 l_customer_qualified VARCHAR2(1);
4869 l_product_qualified VARCHAR2(1);
4870 l_tbl_index NUMBER := 1;
4871 l_api_name CONSTANT VARCHAR2(30) := 'offer_adj_new_product';
4872 l_custom_setup_id NUMBER;
4873
4874 BEGIN
4875 SAVEPOINT offer_adj_new_product;
4876
4877 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
4878 Fnd_Msg_Pub.initialize;
4879 END IF;
4880
4881 x_return_status := Fnd_Api.g_ret_sts_success;
4882
4883 OPEN c_offer_type;
4884 FETCH c_offer_type INTO l_offer_type,
4885 l_tier_level,
4886 l_qp_list_header_id,
4887 l_custom_setup_id;
4888 CLOSE c_offer_type;
4889
4890 OPEN c_order_line;
4891
4892 LOOP
4893 --
4894 l_header_id_tbl.delete ;
4895 l_line_id_tbl.delete;
4896 l_invoice_to_org_id_tbl.delete;
4897 l_ship_to_org_id_tbl.delete;
4898 l_sold_to_org_id_tbl.delete;
4899
4900 FETCH c_order_line BULK COLLECT INTO l_header_id_tbl ,
4901 l_line_id_tbl,
4902 l_invoice_to_org_id_tbl,
4903 l_ship_to_org_id_tbl,
4904 l_sold_to_org_id_tbl
4905 LIMIT l_batch_size;
4906 --
4907
4908 IF l_line_id_tbl.FIRST IS NULL
4909 THEN
4910 --
4911 EXIT;
4912 --
4913 END IF;
4914
4915 FOR i IN l_line_id_tbl.FIRST .. l_line_id_tbl.LAST
4916 LOOP
4917 --
4918 l_customer_qualified := validate_customer(p_invoice_to_org_id => l_invoice_to_org_id_tbl(i)
4919 ,p_ship_to_org_id => l_ship_to_org_id_tbl(i)
4920 ,p_sold_to_org_id => l_sold_to_org_id_tbl(i)
4921 ,p_qp_list_header_id => l_qp_list_header_id);
4922
4923 IF l_customer_qualified = 'Y'
4924 THEN
4925 --
4926 x_order_line_tbl(l_tbl_index).order_header_id := l_header_id_tbl(i);
4927 x_order_line_tbl(l_tbl_index).order_line_id := l_line_id_tbl(i);
4928 l_tbl_index := l_tbl_index + 1;
4929 --
4930 END IF;
4931 END LOOP; -- l_order_line_detail1
4932 --
4933 EXIT WHEN c_order_line%NOTFOUND;
4934 --
4935 END LOOP;
4936
4937 EXCEPTION
4938 WHEN Fnd_Api.G_EXC_ERROR THEN
4939 x_return_status := Fnd_Api.g_ret_sts_error;
4940 ROLLBACK TO offer_adj_new_product;
4941 Fnd_Msg_Pub.Count_AND_Get
4942 ( p_count => x_msg_count,
4943 p_data => x_msg_data,
4944 p_encoded => Fnd_Api.G_FALSE
4945 );
4946 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
4947 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
4948 ROLLBACK TO offer_adj_new_product;
4949 Fnd_Msg_Pub.Count_AND_Get
4950 ( p_count => x_msg_count,
4951 p_data => x_msg_data,
4952 p_encoded => Fnd_Api.G_FALSE
4953 );
4954 WHEN OTHERS THEN
4955 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
4956 ROLLBACK TO offer_adj_new_product;
4957 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
4958 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
4959 END IF;
4960 Fnd_Msg_Pub.Count_AND_Get
4961 ( p_count => x_msg_count,
4962 p_data => x_msg_data,
4963 p_encoded => Fnd_Api.G_FALSE
4964 );
4965
4966 END offer_adj_new_product;
4967
4968 END ozf_net_accrual_engine_pvt;