[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.11.12010000.3 2008/08/04 09:00:57 nirprasa 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',
442 x_party_stmt => l_stmt_offer);
439 x_return_status => x_return_status,
440 x_msg_count => x_msg_count,
441 x_msg_data => x_msg_data,
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
564 BEGIN
565 x_return_status := FND_API.g_ret_sts_success;
566
567 l_org_id := fnd_profile.value('QP_ORGANIZATION_ID');
568
569 FOR l_product IN c_product
570 LOOP
571 --
572 OPEN c_discount(l_product.offer_discount_line_id);
573 FETCH c_discount INTO l_discount,
574 l_discount_type,
575 l_volume_from,
576 l_volume_to,
577 l_volume_type;
578 CLOSE c_discount;
579
580 IF l_product.product_level = 'FAMILY'
584
581 THEN
582 --
583 l_func_area_id := get_func_area(l_product.product_id);
585 IF G_DEBUG_LOW THEN
586 --
587 ozf_utility_pvt.write_conc_log('Functional Area for category: ' || l_func_area_id);
588 ozf_utility_pvt.write_conc_log('Off_Discount_Product_Id:' || l_product.off_discount_product_id);
589 --
590 END IF;
591 --
592 END IF;
593
594 FND_DSQL.init;
595 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) ');
596 FND_DSQL.add_text('SELECT inventory_item_id,');
597 FND_DSQL.add_bind(l_product.product_level);
598 FND_DSQL.add_text(',');
599 FND_DSQL.add_bind(l_discount);
600 FND_DSQL.add_text(',');
601 FND_DSQL.add_bind(l_discount_type);
602 FND_DSQL.add_text(',');
603 FND_DSQL.add_bind(l_volume_from);
604 FND_DSQL.add_text(',');
605 FND_DSQL.add_bind(l_volume_to);
606 FND_DSQL.add_text(',');
607 FND_DSQL.add_bind(l_volume_type);
608 FND_DSQL.add_text(',');
609 FND_DSQL.add_bind(l_product.uom_code);
610 FND_DSQL.add_text(' FROM (');
611
612 IF l_product.product_level = 'FAMILY'
613 THEN
614 --
615 IF l_func_area_id = 11
616 THEN
617 -- Functional Area is PRFA.
618 --
619 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 = ');
620 FND_DSQL.add_bind(l_org_id);
621 FND_DSQL.add_text(' AND epdhv.parent_id = ');
622 FND_DSQL.add_bind(l_product.product_id);
623 --
624 ELSE
625 -- Functional Area id OMFA
626 FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
627 FND_DSQL.add_bind(l_org_id);
628 FND_DSQL.add_text(' AND category_id = ');
629 FND_DSQL.add_bind(l_product.product_id);
630 --
631 END IF;
632 --
633 ELSIF l_product.product_level = 'PRODUCT'
634 THEN
635 --
636 FND_DSQL.add_text('SELECT ');
637 FND_DSQL.add_bind(l_product.product_id);
638 FND_DSQL.add_text(' inventory_item_id FROM DUAL');
639 --
640 END IF;
641
642 FOR l_exclusion IN c_exclusion(l_product.off_discount_product_id)
643 LOOP
644 --
645 FND_DSQL.add_text(' MINUS ');
646
647 IF l_exclusion.product_level = 'PRODUCT'
648 THEN
649 --
650 FND_DSQL.add_text('SELECT ');
651 FND_DSQL.add_bind(l_exclusion.product_id);
652 FND_DSQL.add_text(' inventory_item_id FROM DUAL');
653 --
654 ELSIF l_exclusion.product_level = 'FAMILY'
655 THEN
656 --
657 IF l_func_area_id = 11
658 THEN
659 -- Functional Area is PRFA.
660 --
661 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 = ');
662 FND_DSQL.add_bind(l_org_id);
663 FND_DSQL.add_text(' AND epdhv.parent_id = ');
664 FND_DSQL.add_bind(l_exclusion.product_id);
665 --
666 ELSE
667 -- Functional Area id OMFA
668 FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
669 FND_DSQL.add_bind(l_org_id);
670 FND_DSQL.add_text(' AND category_id = ');
671 FND_DSQL.add_bind(l_exclusion.product_id);
672 --
673 END IF;
674 --
675 END IF;
676 --
677 END LOOP;
678
679 FND_DSQL.add_text(')');
680
681 l_denorm_csr := DBMS_SQL.open_cursor;
682 FND_DSQL.set_cursor(l_denorm_csr);
683 l_stmt_debug := FND_DSQL.get_text(TRUE);
684 l_stmt_denorm := FND_DSQL.get_text(FALSE);
685 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
686 FND_DSQL.do_binds;
687 l_ignore := DBMS_SQL.execute(l_denorm_csr);
688 dbms_sql.close_cursor(l_denorm_csr);
689 --
690 END LOOP;
691
692 EXCEPTION
693 WHEN OTHERS THEN
694 x_return_status := FND_API.g_ret_sts_unexp_error;
695
696 ozf_utility_pvt.write_conc_log(l_stmt_debug);
697 ozf_utility_pvt.write_conc_log(SQLERRM);
698
699 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
700 THEN
701 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
702 END IF;
703
704 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
705 p_count => x_msg_count,
706 p_data => x_msg_data);
707 END populate_prod_line;
708
709
710 PROCEDURE populate_prod_tier( p_offer_id IN NUMBER
711 ,x_return_status OUT NOCOPY VARCHAR2
712 ,x_msg_count OUT NOCOPY NUMBER
713 ,x_msg_data OUT NOCOPY VARCHAR2)
714 IS
715 --
716 CURSOR c_product IS
720 NVL(uom_code, 'NA') uom_code
717 SELECT product_id,
718 product_level,
719 off_discount_product_id,
721 FROM ozf_offer_discount_products
722 WHERE excluder_flag = 'N'
723 AND offer_id = p_offer_id;
724
725 CURSOR c_discount IS
726 SELECT discount,
727 discount_type,
728 NVL(volume_from,0) volume_from,
729 volume_to,
730 DECODE(volume_type, 'PRICING_ATTRIBUTE12', 'AMT', 'PRICING_ATTRIBUTE10', 'QTY', NULL, 'NA') volume_type
731 FROM ozf_offer_discount_lines
732 WHERE offer_id = p_offer_id;
733
734 l_api_name CONSTANT VARCHAR2(30) := 'populate_prod_tier';
735 l_discount NUMBER;
736 l_discount_type VARCHAR2(30);
737 l_volume_from NUMBER;
738 l_volume_to NUMBER;
739 l_volume_type VARCHAR2(30);
740 l_org_id NUMBER;
741 l_denorm_csr NUMBER;
742 l_ignore NUMBER;
743 l_func_area_id NUMBER;
744 l_stmt_denorm VARCHAR2(32000) := NULL;
745 l_stmt_debug VARCHAR2(32000) := NULL;
746
747 BEGIN
748 x_return_status := FND_API.g_ret_sts_success;
749
750 l_org_id := fnd_profile.value('QP_ORGANIZATION_ID');
751 ozf_utility_pvt.write_conc_log(l_api_name);
752
753 FOR l_product IN c_product
754 LOOP
755 --
756
757 IF l_product.product_level = 'FAMILY'
758 THEN
759 --
760 l_func_area_id := get_func_area(l_product.product_id);
761 --
762 END IF;
763
764 FOR l_discount IN c_discount
765 LOOP
766 --
767 -- IF G_DEBUG_LOW THEN
768 --
769 ozf_utility_pvt.write_conc_log('off_discount_product_id:' || l_product.off_discount_product_id);
770 ozf_utility_pvt.write_conc_log('Functional Area for category: ' || l_func_area_id);
771 --
772 -- END IF;
773
774 FND_DSQL.init;
775 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) ');
776 FND_DSQL.add_text('SELECT inventory_item_id,');
777 FND_DSQL.add_bind(l_product.product_level);
778 FND_DSQL.add_text(',');
779 FND_DSQL.add_bind(l_discount.discount);
780 FND_DSQL.add_text(',');
781 FND_DSQL.add_bind(l_discount.discount_type);
782 FND_DSQL.add_text(',');
783 FND_DSQL.add_bind(l_discount.volume_from);
784 FND_DSQL.add_text(',');
785 FND_DSQL.add_bind(l_discount.volume_to);
786 FND_DSQL.add_text(',');
787 FND_DSQL.add_bind(l_discount.volume_type);
788 FND_DSQL.add_text(',');
789 FND_DSQL.add_bind(l_product.uom_code);
790 FND_DSQL.add_text(' FROM (');
791
792 IF l_product.product_level = 'FAMILY'
793 THEN
794 --
795 IF l_func_area_id = 11
796 THEN
797 -- Functional Area is PRFA.
798 --
799 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 = ');
800 FND_DSQL.add_bind(l_org_id);
801 FND_DSQL.add_text(' AND epdhv.parent_id = ');
802 FND_DSQL.add_bind(l_product.product_id);
803 --
804 ELSE
805 -- Functional Area id OMFA
806 FND_DSQL.add_text('SELECT inventory_item_id FROM mtl_item_categories WHERE organization_id = ');
807 FND_DSQL.add_bind(l_org_id);
808 FND_DSQL.add_text(' AND category_id = ');
809 FND_DSQL.add_bind(l_product.product_id);
810 --
811 END IF;
812 --
813 ELSIF l_product.product_level = 'PRODUCT'
814 THEN
815 --
816 FND_DSQL.add_text('SELECT ');
817 FND_DSQL.add_bind(l_product.product_id);
818 FND_DSQL.add_text(' inventory_item_id FROM DUAL');
819 --
820 END IF;
821
822 FND_DSQL.add_text(')');
823
824 l_denorm_csr := DBMS_SQL.open_cursor;
825 FND_DSQL.set_cursor(l_denorm_csr);
826 l_stmt_debug := FND_DSQL.get_text(TRUE);
827 l_stmt_denorm := FND_DSQL.get_text(FALSE);
828 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
829 FND_DSQL.do_binds;
830 l_ignore := DBMS_SQL.execute(l_denorm_csr);
831 dbms_sql.close_cursor(l_denorm_csr);
832
833
834
835 --
836 END LOOP; -- end of discount tiers
837 --
838 END LOOP; -- end of products
839
840
841
842
843 EXCEPTION
844 WHEN OTHERS THEN
845 x_return_status := FND_API.g_ret_sts_unexp_error;
846
847 ozf_utility_pvt.write_conc_log(l_stmt_debug);
848 ozf_utility_pvt.write_conc_log(SQLERRM);
849
850 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_unexp_error)
851 THEN
852 FND_MSG_PUB.add_exc_msg(g_pkg_name, l_api_name);
853 END IF;
854
855 FND_MSG_PUB.count_and_get(p_encoded => FND_API.g_false,
856 p_count => x_msg_count,
857 p_data => x_msg_data);
858
859 END populate_prod_tier;
860
861
865 ,p_uom IN VARCHAR2)
862 FUNCTION get_accrualed_amount(p_product_id IN NUMBER
863 ,p_line_amt IN NUMBER
864 ,p_quantity IN NUMBER
866 RETURN NUMBER
867 IS
868 --
869 CURSOR c_disc_for_item_count IS
870 SELECT COUNT(*)
871 FROM ozf_na_products_temp
872 WHERE inventory_item_id = p_product_id
873 AND product_level = 'PRODUCT';
874
875 CURSOR c_discount_for_cat IS
876 SELECT discount,
877 discount_type,
878 volume_type,
879 volume_from,
880 volume_to,
881 uom
882 FROM ozf_na_products_temp
883 WHERE inventory_item_id = p_product_id
884 AND product_level = 'FAMILY';
885
886 CURSOR c_discount_for_item IS
887 SELECT discount,
888 discount_type,
889 volume_type,
890 volume_from,
891 volume_to, uom
892 FROM ozf_na_products_temp
893 WHERE inventory_item_id = p_product_id
894 AND product_level = 'PRODUCT';
895
896 l_max_accrual NUMBER;
897 l_line_accrual NUMBER;
898 l_disc_for_item_count NUMBER;
899 l_volume_qualified VARCHAR2(1);
900
901 BEGIN
902 --
903 OPEN c_disc_for_item_count;
904 FETCH c_disc_for_item_count INTO l_disc_for_item_count;
905 CLOSE c_disc_for_item_count;
906
907 l_max_accrual := 0;
908
909 IF l_disc_for_item_count = 0
910 THEN
911 --
912 FOR l_discount_for_cat IN c_discount_for_cat
913 LOOP
914 --
915 l_line_accrual := 0;
916 l_volume_qualified := 'N';
917
918 -- check if order satisfies amt/qty requirement
919 IF ( l_discount_for_cat.volume_type = 'AMT' )
920 THEN
921 --
922 IF ( p_line_amt >= l_discount_for_cat.volume_from
923 AND
924 p_line_amt <= l_discount_for_cat.volume_to )
925 THEN
926 --
927 l_volume_qualified := 'Y';
928 --
929 ELSE
930 --
931 l_volume_qualified := 'N';
932 --
933 END IF;
934 --
935 ELSIF ( l_discount_for_cat.volume_type = 'QTY' )
936 THEN
937 --
938 IF ( p_quantity >= l_discount_for_cat.volume_from
939 AND
940 p_quantity <= l_discount_for_cat.volume_to )
941 THEN
942 --
943 l_volume_qualified := 'Y';
944 --
945 ELSE
946 --
947 l_volume_qualified := 'N';
948 --
949 END IF;
950 --
951 ELSIF ( l_discount_for_cat.volume_type = 'NA' )
952 THEN
953 --
954 l_volume_qualified := 'Y';
955 --
956 END IF;
957
958
959
960 IF l_volume_qualified = 'Y'
961 THEN
962 --
963 IF l_discount_for_cat.discount_type = '%'
964 THEN
965 --
966 l_line_accrual := p_line_amt * l_discount_for_cat.discount / 100;
967 --
968 ELSE
969 --
970 l_line_accrual := l_discount_for_cat.discount;
971 --
972 END IF;
973 --
974 END IF;
975
976 -- Memorizes larger accrual amount
977 IF l_line_accrual > l_max_accrual
978 THEN
979 l_max_accrual:= l_line_accrual;
980 END IF;
981
982 -- IF G_DEBUG_LOW THEN
983 ozf_utility_pvt.write_conc_log('Product belongs to a Category on the Offer');
984 ozf_utility_pvt.write_conc_log('ItmId/Qty/Amt/VolType/DiscType/disc/VolQual?');
985 ozf_utility_pvt.write_conc_log(p_product_id || '/' ||
986 p_quantity || '/' ||
987 p_line_amt || '/' ||
988 l_discount_for_cat.volume_type || '/' ||
989 l_discount_for_cat.discount_type || '/' ||
990 l_discount_for_cat.discount || '/' ||
991 l_volume_qualified );
992 -- END IF;
993 --
994 END LOOP;
995 --
996 ELSE
997 -- discount for the item exists. take this value as accrualed discount
998 FOR l_discount_for_item IN c_discount_for_item
999 LOOP
1000 ozf_utility_pvt.write_conc_log('l_volume_qualified '||l_volume_qualified);
1001 ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_from '|| l_discount_for_item.volume_from);
1002 ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_to '|| l_discount_for_item.volume_to);
1003 ozf_utility_pvt.write_conc_log('l_discount_for_item.volume_type '|| l_discount_for_item.volume_type);
1004 ozf_utility_pvt.write_conc_log('p_line_amt '|| p_line_amt);
1005 ozf_utility_pvt.write_conc_log('l_discount_for_item.discount '|| l_discount_for_item.discount);
1009 l_volume_qualified := 'N';
1006 ozf_utility_pvt.write_conc_log('l_discount_for_item.discount_type '|| l_discount_for_item.discount_type);
1007 --
1008 l_line_accrual := 0;
1010 -- check if order satisfies amt/qty requirement
1011 IF ( l_discount_for_item.volume_type = 'AMT' )
1012 THEN
1013 --
1014 IF ( p_line_amt >= l_discount_for_item.volume_from
1015 AND
1016 p_line_amt <= l_discount_for_item.volume_to )
1017 THEN
1018 --
1019 l_volume_qualified := 'Y';
1020 --
1021 ELSE
1022 --
1023 l_volume_qualified := 'N';
1024 --
1025 END IF;
1026 --
1027 ELSIF ( l_discount_for_item.volume_type = 'QTY' )
1028 THEN
1029 --
1030 IF ( p_quantity >= l_discount_for_item.volume_from
1031 AND
1032 p_quantity <= l_discount_for_item.volume_to )
1033 THEN
1034 --
1035 l_volume_qualified := 'Y';
1036 --
1037 ELSE
1038 --
1039 l_volume_qualified := 'N';
1040 --
1041 END IF;
1042 --
1043 ELSIF ( l_discount_for_item.volume_type = 'NA' )
1044 THEN
1045 --
1046 l_volume_qualified := 'Y';
1047 --
1048 END IF;
1049
1050 -- Calculate Accrual Amount
1051 IF l_volume_qualified = 'Y'
1052 THEN
1053 --
1054 IF ( l_discount_for_item.discount_type = '%' )
1055 THEN
1056 --
1057 l_line_accrual := p_line_amt * l_discount_for_item.discount / 100;
1058 --
1059 ELSE
1060 --
1061 l_line_accrual := l_discount_for_item.discount * p_quantity; -- give discount based on quantity
1062 --
1063 END IF;
1064 --
1065 END IF;
1066 -- memorizes larger accrual amount
1067 IF l_line_accrual > l_max_accrual
1068 THEN
1069 --
1070 l_max_accrual:= l_line_accrual;
1071 --
1072 END IF;
1073
1074 IF G_DEBUG_LOW THEN
1075 ozf_utility_pvt.write_conc_log('ItmId/Qty/Amt/VolType/DiscType/disc/VolQual?/MaxAccr');
1076 ozf_utility_pvt.write_conc_log(p_product_id || '/' ||
1077 p_quantity || '/' ||
1078 p_line_amt || '/' ||
1079 l_discount_for_item.volume_type || '/' ||
1080 l_discount_for_item.discount_type || '/' ||
1081 l_discount_for_item.discount || '/' ||
1082 l_volume_qualified || '/' ||
1083 l_max_accrual );
1084 END IF;
1085 --
1086 END LOOP;
1087 --
1088 END IF;
1089
1090 RETURN l_max_accrual;
1091
1092 END get_accrualed_amount;
1093
1094
1095 FUNCTION get_pv_accrual_amount(p_product_id IN NUMBER
1096 ,p_line_amt IN NUMBER
1097 ,p_offer_id IN NUMBER
1098 ,p_org_id IN NUMBER
1099 ,p_list_hdr_id IN NUMBER
1100 ,p_referral_id IN NUMBER
1101 ,p_order_hdr_id IN NUMBER)
1102 RETURN NUMBER
1103 IS
1104 -- given category, find max compensation from referral tables
1105 CURSOR c_maximum_compensation(p_category_id NUMBER) IS
1106 SELECT b.maximum_compensation
1107 FROM pv_ge_benefits_vl a, pv_benft_products b
1108 WHERE a.benefit_id = b.benefit_id
1109 AND a.benefit_type_code = 'PVREFFRL'
1110 AND a.additional_info_1 = p_offer_id
1111 AND b.product_category_id = p_category_id;
1112
1113 -- find accruals already made by the referral
1114 CURSOR c_existing_accruals IS
1115 SELECT NVL(DECODE(gl_posted_flag, 'Y', plan_curr_amount), 0) line_amount, product_id
1116 FROM ozf_funds_utilized_all_b
1117 WHERE reference_type = 'LEAD_REFERRAL'
1118 AND reference_id = p_referral_id
1119 AND plan_type = 'OFFR'
1120 AND plan_id = p_list_hdr_id
1121 AND object_type = 'ORDER'
1122 AND object_id = p_order_hdr_id;
1123
1124 l_discount NUMBER;
1125 l_category_id NUMBER;
1126 l_discount_temp NUMBER;
1127 l_category_id_temp NUMBER; -- temperorily store category id for accrualed items
1128 l_max_compensation NUMBER;
1129 l_accrualed_amount NUMBER := 0;
1130 l_acc_amt_order NUMBER := 0;
1131 l_return_value NUMBER := 0;
1132 l_stmt VARCHAR2(2000);
1133 BEGIN
1134 --
1135 l_stmt := 'SELECT';
1136 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, ' ;
1137 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 ';
1138 l_stmt := l_stmt || ' FROM ozf_offer_discount_lines a, ';
1142 l_stmt := l_stmt || ' eni_prod_denorm_hrchy_v epdhv ';
1139 l_stmt := l_stmt || ' ozf_offer_discount_products b, ';
1140 l_stmt := l_stmt || ' eni_prod_den_hrchy_parents_v c, ';
1141 l_stmt := l_stmt || ' mtl_item_categories mic, ';
1143 l_stmt := l_stmt || ' WHERE a.offer_discount_line_id = b.offer_discount_line_id ';
1144 l_stmt := l_stmt || ' AND a.offer_id = :1 ';
1145 l_stmt := l_stmt || ' AND mic.inventory_item_id = :2 ';
1146 l_stmt := l_stmt || ' AND mic.category_set_id = epdhv.category_set_id ';
1147 l_stmt := l_stmt || ' AND mic.category_id = epdhv.child_id ';
1148 l_stmt := l_stmt || ' AND mic.organization_id = :3 ';
1149 l_stmt := l_stmt || ' AND b.product_id = epdhv.parent_id ';
1150 l_stmt := l_stmt || ' AND epdhv.parent_id = c.category_id';
1151
1152 IF G_DEBUG_LOW THEN
1153 ozf_utility_pvt.write_conc_log('Statement is : ' || l_stmt);
1154 ozf_utility_pvt.write_conc_log('Bind var is : ' || p_product_id);
1155 END IF;
1156
1157 EXECUTE IMMEDIATE l_stmt INTO l_discount, l_category_id USING p_offer_id, p_product_id, p_org_id;
1158
1159 IF G_DEBUG_LOW THEN
1160 ozf_utility_pvt.write_conc_log('Discount : ' || l_discount);
1161 ozf_utility_pvt.write_conc_log('Category_Id : ' || l_category_id);
1162 END IF;
1163
1164 IF ( l_discount IS NOT NULL AND l_category_id IS NOT NULL )
1165 THEN
1166 -- Discount rule exists
1167 l_acc_amt_order := p_line_amt * l_discount / 100;
1168
1169 OPEN c_maximum_compensation(l_category_id);
1170 FETCH c_maximum_compensation INTO l_max_compensation;
1171 CLOSE c_maximum_compensation;
1172
1173 IF G_DEBUG_LOW THEN
1174 ozf_utility_pvt.write_conc_log('accrual for order line:'||l_acc_amt_order);
1175 ozf_utility_pvt.write_conc_log('max compensation:'||l_max_compensation);
1176 END IF;
1177
1178 FOR i IN c_existing_accruals
1179 LOOP
1180 --
1181 EXECUTE IMMEDIATE l_stmt INTO l_discount_temp, l_category_id_temp USING p_offer_id, i.product_id, p_org_id;
1182 --
1183 IF l_category_id_temp = l_category_id
1184 THEN
1185 -- Other item from same category found
1186 l_accrualed_amount := l_accrualed_amount + i.line_amount;
1187 --
1188 END IF;
1189 --
1190 END LOOP;
1191
1192 IF ( l_max_compensation IS NULL OR
1193 (l_max_compensation - l_accrualed_amount >= l_acc_amt_order)
1194 )
1195 THEN
1196 --
1197 l_return_value := l_acc_amt_order;
1198 --
1199 ELSE
1200 --
1201 l_return_value := l_max_compensation - l_accrualed_amount;
1202 --
1203 END IF;
1204 --
1205 END IF;
1206 --
1207 RETURN l_return_value;
1208 --
1209 END get_pv_accrual_amount;
1210
1211 PROCEDURE log_exception(p_act_budgets_rec IN ozf_actbudgets_pvt.act_budgets_rec_type,
1212 p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type)
1213 IS
1214 CURSOR c_na_conc_exception_id IS
1215 SELECT ozf_na_conc_exceptions_s.NEXTVAL
1216 FROM DUAL;
1217
1218 CURSOR c_pk_exist(p_na_conc_exception_id NUMBER) IS
1219 SELECT 1
1220 FROM DUAL
1221 WHERE EXISTS (SELECT 1
1222 FROM ozf_na_conc_exceptions
1223 WHERE na_conc_exception_id = p_na_conc_exception_id);
1224
1225 l_na_conc_exception_id NUMBER;
1226 l_pk_exist NUMBER;
1227 BEGIN
1228 ozf_utility_pvt.write_conc_log('Writing exception log for offer ' || p_act_budgets_rec.act_budget_used_by_id);
1229 LOOP
1230 l_pk_exist := NULL;
1231
1232 OPEN c_na_conc_exception_id;
1233 FETCH c_na_conc_exception_id INTO l_na_conc_exception_id;
1234 CLOSE c_na_conc_exception_id;
1235
1236 OPEN c_pk_exist(l_na_conc_exception_id);
1237 FETCH c_pk_exist INTO l_pk_exist;
1238 CLOSE c_pk_exist;
1239
1240 EXIT WHEN l_pk_exist IS NULL;
1241 END LOOP;
1242
1243 INSERT INTO ozf_na_conc_exceptions(na_conc_exception_id
1244 ,act_budget_used_by_id
1245 ,arc_act_budget_used_by
1246 ,budget_source_type
1247 ,budget_source_id
1248 ,request_amount
1249 ,request_currency
1250 ,request_date
1251 ,status_code
1252 ,approved_amount
1253 ,approved_in_currency
1254 ,approval_date
1255 ,approver_id
1256 ,transfer_type
1257 ,requester_id
1258 ,object_type
1259 ,object_id
1260 ,product_level_type
1261 ,product_id
1262 ,cust_account_id
1263 ,utilization_type
1264 ,adjustment_date
1265 ,gl_date
1266 ,billto_cust_account_id
1267 ,reference_type
1268 ,reference_id
1269 ,order_line_id
1273 ,p_act_budgets_rec.arc_act_budget_used_by
1270 ,org_id)
1271 VALUES(l_na_conc_exception_id
1272 ,p_act_budgets_rec.act_budget_used_by_id
1274 ,p_act_budgets_rec.budget_source_type
1275 ,p_act_budgets_rec.budget_source_id
1276 ,p_act_budgets_rec.request_amount
1277 ,p_act_budgets_rec.request_currency
1278 ,p_act_budgets_rec.request_date
1279 ,p_act_budgets_rec.status_code
1280 ,p_act_budgets_rec.approved_amount
1281 ,p_act_budgets_rec.approved_in_currency
1282 ,p_act_budgets_rec.approval_date
1283 ,p_act_budgets_rec.approver_id
1284 ,p_act_budgets_rec.transfer_type
1285 ,p_act_budgets_rec.requester_id
1286 ,p_act_util_rec.object_type
1287 ,p_act_util_rec.object_id
1288 ,p_act_util_rec.product_level_type
1289 ,p_act_util_rec.product_id
1290 ,p_act_util_rec.cust_account_id
1291 ,p_act_util_rec.utilization_type
1292 ,p_act_util_rec.adjustment_date
1293 ,p_act_util_rec.gl_date
1294 ,p_act_util_rec.billto_cust_account_id
1295 ,p_act_util_rec.reference_type
1296 ,p_act_util_rec.reference_id
1297 ,p_act_util_rec.order_line_id
1298 ,p_act_util_rec.org_id);
1299 END log_exception;
1300
1301
1302 PROCEDURE process_exceptions IS
1303
1304 CURSOR c_exception_rec IS
1305 SELECT *
1306 FROM ozf_na_conc_exceptions;
1307
1308 l_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type;
1309 l_act_util_rec ozf_actbudgets_pvt.act_util_rec_type;
1310 l_return_status VARCHAR2(1);
1311 l_msg_count NUMBER;
1312 l_msg_data VARCHAR2(2000);
1313 l_act_budget_id NUMBER;
1314 l_utilized_amount NUMBER := 0;
1315
1316 BEGIN
1317 --
1318 FOR l_exception_rec IN c_exception_rec
1319 LOOP
1320 --
1321 IF G_DEBUG_LOW
1322 THEN
1323 ozf_utility_pvt.write_conc_log('Processing exception Id = ' || l_exception_rec.na_conc_exception_id);
1324 END IF;
1325
1326 l_return_status := FND_API.g_ret_sts_success; -- bug 3655853
1327
1328 l_act_budgets_rec.act_budget_used_by_id := l_exception_rec.act_budget_used_by_id;
1329 l_act_budgets_rec.arc_act_budget_used_by := l_exception_rec.arc_act_budget_used_by;
1330 l_act_budgets_rec.budget_source_type := l_exception_rec.budget_source_type;
1331 l_act_budgets_rec.budget_source_id := l_exception_rec.budget_source_id;
1332 l_act_budgets_rec.request_amount := l_exception_rec.request_amount;
1333 l_act_budgets_rec.request_currency := l_exception_rec.request_currency;
1334 l_act_budgets_rec.request_date := l_exception_rec.request_date;
1335 l_act_budgets_rec.status_code := l_exception_rec.status_code;
1336 l_act_budgets_rec.approved_amount := l_exception_rec.approved_amount;
1337 l_act_budgets_rec.approved_in_currency := l_exception_rec.approved_in_currency;
1338 l_act_budgets_rec.approval_date := l_exception_rec.approval_date;
1339 l_act_budgets_rec.approver_id := l_exception_rec.approver_id;
1340 l_act_budgets_rec.transfer_type := l_exception_rec.transfer_type;
1341 l_act_budgets_rec.requester_id := l_exception_rec.requester_id;
1342
1343 l_act_util_rec.object_type := l_exception_rec.object_type;
1344 l_act_util_rec.object_id := l_exception_rec.object_id;
1345 l_act_util_rec.product_level_type := l_exception_rec.product_level_type;
1346 l_act_util_rec.product_id := l_exception_rec.product_id;
1347 l_act_util_rec.cust_account_id := l_exception_rec.cust_account_id;
1348 l_act_util_rec.utilization_type := l_exception_rec.utilization_type;
1349 l_act_util_rec.adjustment_date := l_exception_rec.adjustment_date;
1350 l_act_util_rec.gl_date := l_exception_rec.gl_date;
1351 l_act_util_rec.billto_cust_account_id := l_exception_rec.billto_cust_account_id;
1352 l_act_util_rec.reference_type := l_exception_rec.reference_type;
1353 l_act_util_rec.reference_id := l_exception_rec.reference_id;
1354 l_act_util_rec.order_line_id := l_exception_rec.order_line_id;
1355 l_act_util_rec.org_id := l_exception_rec.org_id;
1356
1357 ozf_fund_adjustment_pvt.process_act_budgets(x_return_status => l_return_status
1358 ,x_msg_count => l_msg_count
1359 ,x_msg_data => l_msg_data
1360 ,p_act_budgets_rec => l_act_budgets_rec
1361 ,p_act_util_rec => l_act_util_rec
1362 ,x_act_budget_id => l_act_budget_id
1363 ,x_utilized_amount => l_utilized_amount);
1364
1365 IF G_DEBUG_LOW
1366 THEN
1367 ozf_utility_pvt.write_conc_log('Exception_id - Status: ' || l_exception_rec.na_conc_exception_id
1368 || ' - '
1372
1369 || l_return_status);
1370 ozf_utility_pvt.write_conc_log('Utilization Amount Created: ' || l_utilized_amount);
1371 END IF;
1373 IF l_return_status = FND_API.g_ret_sts_success
1374 THEN
1375 --
1376 DELETE FROM ozf_na_conc_exceptions
1377 WHERE na_conc_exception_id = l_exception_rec.na_conc_exception_id;
1378 --
1379 END IF;
1380 l_utilized_amount := 0;
1381 --
1382 END LOOP; -- Done Processing exception records
1383
1384 END process_exceptions;
1385
1386
1387 --------------------
1388 -- Main Procedure
1389 --------------------
1390
1391 PROCEDURE net_accrual_engine( ERRBUF OUT NOCOPY VARCHAR2,
1392 RETCODE OUT NOCOPY VARCHAR2,
1393 p_as_of_date IN VARCHAR2,
1394 p_offer_id IN NUMBER DEFAULT NULL)
1395 IS
1396 --
1397 CURSOR c_net_accrual_offers IS
1398 SELECT ozf.offer_id,
1399 ozf.qp_list_header_id,
1400 ozf.latest_na_completion_date,
1401 ozf.custom_setup_id,
1402 ozf.tier_level,
1403 NVL(ozf.transaction_currency_code, ozf.fund_request_curr_code) fund_request_curr_code,
1404 ozf.qualifier_id,
1405 ozf.na_rule_header_id,
1406 ozf.owner_id,
1407 TRUNC(qp.start_date_active) start_date_active,
1408 TRUNC(qp.end_date_active + 1) - (1/86400) end_date_active,
1409 qp.orig_org_id,
1410 qp_tl.description offer_name,
1411 ozf.sales_method_flag,
1412 NVL(ozf.resale_line_id_processed, 0) resale_line_id_processed
1413 FROM ozf_offers ozf,
1414 qp_list_headers_b qp,
1415 qp_list_headers_tl qp_tl
1416 WHERE ozf.offer_type = 'NET_ACCRUAL'
1417 AND ozf.status_code = 'ACTIVE'
1418 AND ozf.offer_id = NVL(p_offer_id, ozf.offer_id)
1419 AND ozf.qp_list_header_id = qp.list_header_id
1420 AND qp.list_header_id = qp_tl.list_header_id
1421 -- AND qp.orig_org_id = TO_NUMBER(SUBSTRB(USERENV('CLIENT_INFO'), 1, 10))
1422 AND qp_tl.language = USERENV('LANG');
1423
1424 CURSOR c_na_rule_lines(p_na_rule_header_id NUMBER) IS
1425 SELECT na_deduction_rule_id
1426 FROM ozf_na_rule_lines
1427 WHERE na_rule_header_id = p_na_rule_header_id
1428 AND active_flag = 'Y';
1429
1430 CURSOR c_na_deduction_rule(p_deduction_rule_id NUMBER) IS
1431 SELECT a.na_deduction_rule_id,
1432 a.transaction_source_code,
1433 a.transaction_type_code,
1434 a.deduction_identifier_id,
1435 a.deduction_identifier_org_id,
1436 b.name
1437 FROM ozf_na_deduction_rules_b a,
1438 ozf_na_deduction_rules_tl b
1439 WHERE a.na_deduction_rule_id = b.na_deduction_rule_id
1440 AND b.language = USERENV('LANG')
1441 AND a.na_deduction_rule_id = p_deduction_rule_id;
1442
1443 l_na_deduction_rule c_na_deduction_rule%ROWTYPE;
1444
1445
1446
1447 CURSOR c_order_line (p_start_date DATE,
1448 p_end_date DATE,
1449 p_offer_org_id NUMBER) IS
1450 SELECT /*+ ordered use_hash(OL) full(OL) use_nl(OH) */
1451 ol.header_id,
1452 ol.line_id,
1453 ol.actual_shipment_date,
1454 ol.fulfillment_date,
1455 ol.invoice_to_org_id,
1456 ol.ship_to_org_id,
1457 ol.sold_to_org_id,
1458 ol.inventory_item_id,
1459 ol.shipped_quantity,
1460 ol.fulfilled_quantity,
1461 ol.invoiced_quantity,
1462 ol.pricing_quantity,
1463 ol.pricing_quantity_uom,
1464 ol.unit_selling_price,
1465 ol.org_id,
1466 NVL(ol.actual_shipment_date,ol.fulfillment_date) conv_date,
1467 oh.transactional_curr_code
1468 FROM ( SELECT /*+ no_merge */ DISTINCT INVENTORY_ITEM_ID FROM OZF_NA_PRODUCTS_TEMP ) na,
1469 oe_order_lines_all ol,
1470 oe_order_headers_all oh
1471 WHERE ol.inventory_item_id = na.inventory_item_id
1472 AND ol.flow_status_code IN ('SHIPPED','CLOSED')
1473 AND ol.cancelled_flag = 'N'
1474 AND ol.line_category_code <> 'RETURN'
1475 AND ( NVL(ol.actual_shipment_date,ol.fulfillment_date)
1476 BETWEEN p_start_date AND p_end_date
1477 )
1478 AND ol.org_id = NVL(p_offer_org_id, ol.org_id)
1479 AND ol.header_id = oh.header_id;
1480
1481 CURSOR c_idsm_line (p_offer_start_date DATE,
1482 p_offer_end_date DATE,
1483 p_offer_org_id NUMBER,
1484 p_resale_line_id NUMBER) IS
1485 SELECT resale_header_id header_id,
1486 resale_line_id line_id,
1487 date_ordered actual_shipment_date,
1488 NULL fulfillment_date,
1489 bill_to_site_use_id invoice_to_org_id,
1490 ship_to_site_use_id ship_to_org_id,
1491 bill_to_cust_account_id sold_to_org_id,
1492 inventory_item_id,
1493 quantity shipped_quantity,
1494 quantity fulfilled_quantity,
1495 quantity invoiced_quantity,
1496 quantity pricing_quantity,
1497 uom_code pricing_quantity_uom,
1498 selling_price unit_selling_price,
1499 org_id,
1500 NVL(exchange_rate_date, date_ordered) conv_date,
1501 currency_code transactional_curr_code
1502 FROM ozf_resale_lines_all
1506 -- AND ol.cancelled_flag = 'N'
1503 WHERE inventory_item_id IN ( SELECT na.inventory_item_id
1504 FROM ozf_na_products_temp na)
1505 -- AND ol.flow_status_code IN ('SHIPPED','CLOSED')
1507 -- AND ol.line_category_code <> 'RETURN'
1508 AND TRUNC(date_ordered) >= TRUNC(p_offer_start_date)
1509 AND TRUNC(date_ordered) <= TRUNC(NVL(p_offer_end_date, SYSDATE))
1510 AND org_id = NVL(p_offer_org_id, org_id)
1511 AND quantity > 0
1512 AND resale_header_id > p_resale_line_id
1513 ORDER BY resale_line_id;
1514
1515 CURSOR c_ar_trx_line_details( p_cust_trx_type_id NUMBER,
1516 p_start_date DATE,
1517 p_end_date DATE,
1518 p_org_id NUMBER
1519 ) IS
1520 SELECT NVL(a.extended_amount, 0) extended_amount,
1521 a.inventory_item_id,
1522 a.quantity_credited,
1523 a.quantity_invoiced,
1524 a.uom_code,
1525 b.sold_to_customer_id,
1526 b.bill_to_site_use_id,
1527 b.ship_to_site_use_id,
1528 b.invoice_currency_code,
1529 b.customer_trx_id,
1530 b.complete_flag,
1531 b.trx_date conv_date,
1532 a.customer_trx_line_id
1533 FROM ra_customer_trx_lines_all a,
1534 ra_customer_trx_all b
1535 WHERE a.inventory_item_id IN ( SELECT na.inventory_item_id
1536 FROM ozf_na_products_temp na)
1537 AND a.line_type = 'LINE'
1538 AND a.customer_trx_id = b.customer_trx_id
1539 AND b.complete_flag = 'Y'
1540 AND b.cust_trx_type_id = p_cust_trx_type_id
1541 AND b.trx_date BETWEEN TRUNC(p_start_date) AND TRUNC(p_end_date)
1542 AND b.org_id = p_org_id;
1543
1544
1545 CURSOR c_return_line (p_order_type_id NUMBER,
1546 p_start_date DATE,
1547 p_end_date DATE) IS
1548 SELECT /*+ ordered use_hash(OL) full(OL) use_nl(OH) */
1549 ol.header_id,
1550 ol.line_id,
1551 ol.actual_shipment_date,
1552 ol.fulfillment_date,
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 NVL(ol.actual_arrival_date,ol.fulfillment_date) conv_date,
1565 oh.transactional_curr_code
1566 FROM ( SELECT /*+ no_merge */ DISTINCT INVENTORY_ITEM_ID FROM OZF_NA_PRODUCTS_TEMP ) NA,
1567 oe_order_lines_all ol,
1568 oe_order_headers_all oh
1569 WHERE ol.inventory_item_id = na.inventory_item_id
1570 AND ol.open_flag = 'N'
1571 AND ol.cancelled_flag = 'N'
1572 AND ol.line_category_code = 'RETURN'
1573 AND ( NVL(ol.actual_arrival_date,ol.fulfillment_date)
1574 BETWEEN p_start_date AND p_end_date
1575 )
1576 AND ol.header_id = oh.header_id
1577 AND oh.order_type_id = p_order_type_id;
1578
1579 /* Indexes on utilization table
1580 Not Unique Index: OZF_FUNDS_UTILIZED_ALL_B_N14
1581 REFERENCE_TYPE TRX_LINE
1582 REFERENCE_ID customer_trx_line_id
1583
1584 Not Unique Index: OZF_FUNDS_UTILIZED_ALL_B_N19
1585 OBJECT_TYPE CM OR DM
1586 OBJECT_ID customer_trx_id
1587
1588 Not Unique Index: OZF_FUNDS_UTILIZED_ALL_B_N9
1589 PRODUCT_ID inventory_item_id
1590 PRODUCT_LEVEL_TYPE PRODUCT
1591 */
1592 CURSOR c_tm_lines(p_activity_media_id NUMBER,
1593 p_start_date DATE,
1594 p_end_date DATE,
1595 p_qp_list_header_id NUMBER) IS
1596 SELECT NVL(DECODE(a.gl_posted_flag, 'Y', a.plan_curr_amount), 0) line_amount,
1597 a.cust_account_id,
1598 a.adjustment_date conv_date,
1599 a.currency_code,
1600 a.org_id --Added for bug 7030415
1601 FROM ozf_funds_utilized_all_b a,
1602 ozf_offers b,
1603 ozf_na_products_temp c
1604 WHERE a.plan_type = 'OFFR'
1605 AND a.plan_id = b.qp_list_header_id
1606 AND b.qp_list_header_id <> p_qp_list_header_id
1607 AND a.adjustment_date BETWEEN p_start_date and p_end_date
1608 AND a.utilization_type IN ('ACCRUAL','ADJUSTMENT')
1609 AND b.activity_media_id = p_activity_media_id
1610 AND a.product_id = c.inventory_item_id
1611 AND a.product_level_type = 'PRODUCT';
1612
1613 CURSOR c_get_util_amt(p_customer_trx_line_id NUMBER,
1614 p_inventory_item_id NUMBER,
1615 p_qp_list_header_id NUMBER) IS
1616 SELECT NVL(SUM(plan_curr_amount),0)
1617 FROM ozf_funds_utilized_all_b
1618 WHERE reference_type = 'TRX_LINE'
1619 AND reference_id = p_customer_trx_line_id
1620 AND product_id = p_inventory_item_id
1621 AND product_level_type = 'PRODUCT'
1622 AND plan_type = 'OFFR'
1623 AND plan_id = p_qp_list_header_id;
1624
1625 -- Added for bug 7030415
1626 CURSOR c_get_conversion_type( p_org_id IN NUMBER) IS
1627 SELECT exchange_rate_type
1628 FROM ozf_sys_parameters_all
1629 WHERE org_id = p_org_id;
1630
1631
1632 l_exchange_rate_type VARCHAR2(30) := FND_API.G_MISS_CHAR;
1633 l_order_line_tbl t_order_line_tbl;
1634 l_ar_trx_line_tbl t_ar_trx_line_tbl;
1638
1635 l_return_line_tbl t_order_line_tbl;
1636 l_idsm_line_tbl t_order_line_tbl;
1637 l_batch_size NUMBER := 1000;
1639 l_return_status VARCHAR2(1);
1640 l_msg_count NUMBER;
1641 l_msg_data VARCHAR2(2000);
1642
1643 l_latest_comp_date DATE;
1644 l_as_of_date DATE;
1645 l_ar_start_date DATE;
1646 l_start_date DATE;
1647 l_end_date DATE;
1648
1649 l_customer_qualified VARCHAR2(1);
1650 l_product_qualified VARCHAR2(1);
1651
1652 l_line_amount NUMBER;
1653 l_line_acc_amount NUMBER;
1654
1655 l_accrual_amount NUMBER;
1656 l_existing_util_amt NUMBER;
1657 l_ar_dedu_line_amt NUMBER;
1658 l_ar_dedu_amount NUMBER;
1659
1660 l_om_dedu_line_amt NUMBER;
1661 l_om_dedu_amount NUMBER;
1662
1663 l_tm_dedu_line_amt NUMBER;
1664 l_tm_dedu_amount NUMBER;
1665
1666 l_batch_mode VARCHAR2(10);
1667 l_orig_batch_mode VARCHAR2(10);
1668 l_order_curr_code VARCHAR2(30);
1669 l_org_id NUMBER; -- Inventory Org
1670 l_offer_org_id NUMBER; -- Org in Which the offer was created
1671
1672 l_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type;
1673 l_act_util_rec ozf_actbudgets_pvt.act_util_rec_type;
1674 l_act_budget_id NUMBER;
1675 l_referral_id NUMBER;
1676 l_beneficiary_id NUMBER;
1677 l_utilization_type VARCHAR2(30);
1678 l_reference_type VARCHAR2(30);
1679 l_sign NUMBER;
1680 l_quantity NUMBER;
1681 l_utilized_amount NUMBER := 0;
1682
1683 l_rate NUMBER;
1684
1685
1686 -- Used to Validate country code for PRM Net Accrual Offer
1687
1688 CURSOR c_terr_countries ( p_offer_id IN NUMBER) IS
1689 SELECT terr_val.low_value_char
1690 FROM ozf_offer_qualifiers offer_qual,
1691 jtf_terr_qual_all terr_qual,
1692 jtf_terr_values_all terr_val
1693 WHERE offer_qual.offer_id = p_offer_id
1694 AND offer_qual.qualifier_attr_value = terr_qual.terr_id
1695 AND terr_qual.qual_usg_id = -1065 -- Pick Country Qualifier only
1696 AND terr_qual.terr_qual_id = terr_val.terr_qual_id;
1697
1698 l_terr_countries_tbl terr_countries_tbl;
1699
1700 CURSOR c_country_code(p_site_use_id NUMBER) IS
1701 SELECT hzloc.country
1702 FROM hz_cust_site_uses_all hzcsua,
1703 hz_cust_acct_sites_all hzcasa,
1704 hz_locations hzloc,
1705 hz_party_sites hzps
1706 WHERE hzcsua.cust_acct_site_id = hzcasa.cust_acct_site_id
1707 AND hzcasa.party_site_id = hzps.party_site_id
1708 AND hzps.location_id = hzloc.location_id
1709 AND hzcsua.status = 'A'
1710 AND hzcsua.site_use_id = p_site_use_id;
1711
1712 l_country_code VARCHAR2(60);
1713 l_new_amount NUMBER;
1714 l_date_from_input DATE;
1715 l_idsm_line_processed NUMBER := 0;
1716 --
1717
1718 BEGIN
1719 -- Standard Start of API savepoint
1720 SAVEPOINT net_accrual_engine;
1721
1722 RETCODE := '0';
1723
1724 -- initialize multi org
1725 MO_GLOBAL.init('OZF');
1726 MO_GLOBAL.set_policy_context('M',null);
1727
1728 ozf_utility_pvt.write_conc_log('-- Start Processing : ' || to_char(sysdate,'MM/DD/YYYY:HH:MM:SS'));
1729
1730 ozf_utility_pvt.write_conc_log('-- Process_Exceptions (+) ');
1731 --
1732 process_exceptions();
1733 --
1734 ozf_utility_pvt.write_conc_log('-- Process_Exceptions (-) ');
1735 --
1736 l_date_from_input := TRUNC(TO_DATE(p_as_of_date, 'YYYY/MM/DD HH24:MI:SS'));
1737 ozf_utility_pvt.write_conc_log('-- Date Converted : ' || l_date_from_input);
1738
1739 IF (l_date_from_input IS NULL)
1740 OR
1741 (TRUNC(l_date_from_input) >= TRUNC(SYSDATE))
1742 THEN
1743 l_as_of_date := SYSDATE;
1744 ELSE
1745 -- Set end time to 23:59:59 of the day.
1746 l_as_of_date := TRUNC(l_date_from_input + 1) - 1/86400;
1747 END IF;
1748
1749 l_orig_batch_mode := fnd_profile.value('OZF_PROCESS_NA_BATCH_MODE');
1750 l_org_id := fnd_profile.value('QP_ORGANIZATION_ID');
1751
1752 IF l_orig_batch_mode IS NULL
1753 THEN
1754 l_orig_batch_mode := 'NO';
1755 END IF;
1756
1757 ozf_utility_pvt.write_conc_log('OZF: Process Net Accrual In Batch Mode: '||l_orig_batch_mode);
1758 ozf_utility_pvt.write_conc_log('QP: Item Validation Organization: '||l_org_id);
1759
1760 ozf_utility_pvt.write_conc_log('-- Start Processing Net Accrual Offers (+) ');
1761
1762 ----------------------------------------------------
1763 FOR l_net_accrual_offers IN c_net_accrual_offers
1764 LOOP
1765 --
1766 l_return_status := FND_API.g_ret_sts_success;
1767
1768 ozf_utility_pvt.write_conc_log('-----------------------------------------');
1769 ozf_utility_pvt.write_conc_log('--');
1770 ozf_utility_pvt.write_conc_log('-------- Processing Offer: '|| l_net_accrual_offers.offer_name);
1771 ozf_utility_pvt.write_conc_log(' Offer_Id / List_Header_Id / Custom_Setup_Id / Orig_Org_Id: '
1772 || l_net_accrual_offers.offer_id || ' / '
1773 || l_net_accrual_offers.qp_list_header_id || ' / '
1774 || l_net_accrual_offers.custom_setup_id || ' / '
1775 || l_net_accrual_offers.orig_org_id );
1776 ozf_utility_pvt.write_conc_log('--');
1777
1778 -------- Derive Program Start and End Date Range ----------
1782 l_idsm_line_processed := l_net_accrual_offers.resale_line_id_processed;
1779 l_latest_comp_date := l_net_accrual_offers.latest_na_completion_date;
1780 l_start_date := l_net_accrual_offers.start_date_active;
1781 l_end_date := l_net_accrual_offers.end_date_active;
1783
1784 IF l_latest_comp_date IS NOT NULL
1785 THEN
1786 l_start_date := l_latest_comp_date;
1787 END IF;
1788
1789 IF l_end_date IS NULL OR l_end_date > l_as_of_date
1790 THEN
1791 l_end_date := l_as_of_date;
1792 END IF;
1793
1794 ozf_utility_pvt.write_conc_log('Accrual Start Period: '||to_char(l_start_date,'MM/DD/YY HH:MI:SS AM'));
1795 ozf_utility_pvt.write_conc_log('Accrual End Date: ' ||to_char(l_end_date,'MM/DD/YY HH:MI:SS AM'));
1796 ozf_utility_pvt.write_conc_log('Resale Line Processed: ' || l_idsm_line_processed);
1797
1798 IF l_start_date > l_end_date
1799 THEN
1800 -- This offer has been completely processed. Skip OM, Continue Process IDSM.
1801 ozf_utility_pvt.write_conc_log('This Offer has been completely processed for OM. Skipping to IDSM. ');
1802 GOTO IDSM;
1803 END IF;
1804 --------------------------------------------------------------
1805
1806 IF l_net_accrual_offers.custom_setup_id = 105
1807 THEN
1808 -- The batch mode profile does not apply for PRM offers
1809 l_batch_mode := 'NO';
1810 l_offer_org_id := NULL;
1811 ELSE
1812 l_batch_mode := l_orig_batch_mode;
1813 l_offer_org_id := l_net_accrual_offers.orig_org_id;
1814 END IF;
1815
1816 DELETE FROM ozf_na_customers_temp;
1817 DELETE FROM ozf_na_products_temp;
1818
1819 ----------------- Denrom Customers ------------------
1820 IF l_net_accrual_offers.custom_setup_id = 105
1821 THEN
1822 --
1823 -- For PRM Offers, populate local table with all qualifying countries
1824 -- once for each offer in a local PL/SQL table
1825 -- No need to use LIMIT clause since # of countries will be limited for a terr
1826 --
1827 l_terr_countries_tbl.delete;
1828
1829 OPEN c_terr_countries(l_net_accrual_offers.offer_id);
1830 FETCH c_terr_countries BULK COLLECT INTO l_terr_countries_tbl;
1831 CLOSE c_terr_countries;
1832
1833 IF l_terr_countries_tbl.FIRST IS NULL
1834 THEN
1835 -- No countries defined for a PRM Offer
1836 -- No point processing this offer. Skip it offer
1837 -- If implementation is correct, this will never happen
1838 ozf_utility_pvt.write_conc_log('-- No country qualifiers provided for PRM Offer. Not Processing it ..');
1839 GOTO NEXT_OFFER;
1840 END IF;
1841 --
1842 IF G_DEBUG_LOW
1843 THEN
1844 --
1845 FOR c IN l_terr_countries_tbl.FIRST..l_terr_countries_tbl.LAST
1846 LOOP
1847 ozf_utility_pvt.write_conc_log('Country Code: '|| l_terr_countries_tbl(c) );
1848 END LOOP;
1849 --
1850 END IF;
1851 --
1852 ELSE
1853 --
1854 -- For all other Offers, populate the ozf_na_customers_temp denom table
1855 --
1856
1857 ozf_utility_pvt.write_conc_log('Populate_Customers (+)');
1858
1859 populate_customers(l_net_accrual_offers.offer_id
1860 ,l_return_status
1861 ,l_msg_count
1862 ,l_msg_data);
1863
1864 ozf_utility_pvt.write_conc_log('Populate_Customers (-) With Status: ' ||l_return_status);
1865
1866 IF l_return_status = Fnd_Api.g_ret_sts_error
1867 THEN
1868 RAISE Fnd_Api.g_exc_error;
1869 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
1870 THEN
1871 RAISE Fnd_Api.g_exc_unexpected_error;
1872 END IF;
1873 --
1874 END IF;
1875 ------------------------------------------------------
1876
1877 --------------- Denorm Products ----------------------
1878 IF l_net_accrual_offers.tier_level = 'LINE'
1879 THEN
1880 --
1881 ozf_utility_pvt.write_conc_log('Populate_Prod_Line (+)');
1882
1883 populate_prod_line(l_net_accrual_offers.offer_id
1884 ,l_return_status
1885 ,l_msg_count
1886 ,l_msg_data);
1887
1888 ozf_utility_pvt.write_conc_log('Populate_Prod_Line (-) With Status: '||l_return_status);
1889 --
1890 ELSIF l_net_accrual_offers.tier_level = 'HEADER'
1891 THEN
1892 --
1893 ozf_utility_pvt.write_conc_log('Populate_Prod_Tier (+)');
1894
1895 populate_prod_tier(l_net_accrual_offers.offer_id
1896 ,l_return_status
1897 ,l_msg_count
1898 ,l_msg_data);
1899
1900 ozf_utility_pvt.write_conc_log('Populate_Prod_Tier (-) With Status: '||l_return_status);
1901 --
1902 END IF;
1903 ---------------------------------------------------------
1904
1905 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
1906 RAISE Fnd_Api.g_exc_error;
1907 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
1908 RAISE Fnd_Api.g_exc_unexpected_error;
1909 END IF;
1910
1911 --------------- Start Processing Orders ------------------------
1912 ozf_utility_pvt.write_conc_log('-- Start Processing Orders -- ');
1913
1917 ozf_utility_pvt.write_conc_log('Processing OM lines');
1914 --------------- Start Processing OM lines ------------------------
1915 IF l_net_accrual_offers.sales_method_flag IS NULL OR l_net_accrual_offers.sales_method_flag = 'D' THEN
1916 --
1918 l_order_line_tbl.delete;
1919 l_accrual_amount := 0;
1920
1921 OPEN c_order_line(l_start_date, l_end_date, l_offer_org_id);
1922
1923 LOOP
1924 --
1925 FETCH c_order_line BULK COLLECT INTO l_order_line_tbl LIMIT l_batch_size;
1926 --
1927 -- To handle NO DATA FOUND for c_order_line CURSOR
1928 IF l_order_line_tbl.FIRST IS NULL
1929 THEN
1930 --
1931 ozf_utility_pvt.write_conc_log('No Data found in c_order_line CURSOR');
1932 EXIT;
1933 --
1934 END IF;
1935 --
1936 -- Logic to exit after all the record have been processed
1937 -- is just before the END LOOP EXIT WHEN c_order_line%NOTFOUND;
1938
1939 ---------------------------------------------------------
1940 FOR i IN l_order_line_tbl.FIRST .. l_order_line_tbl.LAST
1941 LOOP
1942 ---------------------------------------------------------
1943 --
1944
1945 l_return_status := FND_API.g_ret_sts_success;
1946
1947 IF G_DEBUG_LOW THEN
1948 ozf_utility_pvt.write_conc_log('Order Line_Id: '||l_order_line_tbl(i).line_id);
1949 END IF;
1950
1951 l_line_amount := ( NVL(l_order_line_tbl(i).shipped_quantity,l_order_line_tbl(i).fulfilled_quantity)
1952 * l_order_line_tbl(i).unit_selling_price );
1953 --
1954 ------------- Qualify Customer on the Order line ------------------------------
1955 --
1956
1957 IF l_net_accrual_offers.custom_setup_id = 105
1958 THEN
1959 ----- For PV Net Accrual Offers, do not look at denorm -------
1960 ----- Get Country code from the Identifying addresss of the Customer
1961 OPEN c_country_code(l_order_line_tbl(i).invoice_to_org_id);
1962 FETCH c_country_code INTO l_country_code;
1963 CLOSE c_country_code;
1964
1965 -- l_terr_countries_tbl has all the countries eligible for this offer
1966 -- This table is populated in the 'Denorm Customers' section for each PV NA Offer
1967 l_customer_qualified := 'N';
1968
1969 FOR j IN l_terr_countries_tbl.FIRST .. l_terr_countries_tbl.LAST
1970 LOOP
1971 --
1972 IF l_country_code = l_terr_countries_tbl(j)
1973 THEN
1974 l_customer_qualified := 'Y';
1975 EXIT;
1976 END IF;
1977 --
1978 END LOOP;
1979
1980 IF l_customer_qualified = 'N' THEN
1981 -- sold_to not qualified. try ship_to
1982 OPEN c_country_code(l_order_line_tbl(i).ship_to_org_id);
1983 FETCH c_country_code INTO l_country_code;
1984 CLOSE c_country_code;
1985
1986 FOR j IN l_terr_countries_tbl.FIRST .. l_terr_countries_tbl.LAST
1987 LOOP
1988 --
1989 IF l_country_code = l_terr_countries_tbl(j)
1990 THEN
1991 l_customer_qualified := 'Y';
1992 EXIT;
1993 END IF;
1994 --
1995 END LOOP;
1996 --
1997 END IF;
1998 --
1999 ELSE
2000 ----- For all other Net Accrual offers, look at denorm -------
2001 l_customer_qualified := validate_customer(p_invoice_to_org_id => l_order_line_tbl(i).invoice_to_org_id
2002 ,p_ship_to_org_id => l_order_line_tbl(i).ship_to_org_id
2003 ,p_sold_to_org_id => l_order_line_tbl(i).sold_to_org_id);
2004 --
2005 END IF; -- Done qualfiying the customer
2006
2007 IF G_DEBUG_LOW THEN
2008 ozf_utility_pvt.write_conc_log('Did Customer qualify: '||l_customer_qualified);
2009 END IF;
2010
2011 -- Fetch Currency Code on the Order
2012 l_order_curr_code := l_order_line_tbl(i).transactional_curr_code ;
2013 ozf_utility_pvt.write_conc_log('l_order_curr_code: '|| l_order_curr_code);
2014 ozf_utility_pvt.write_conc_log('l_net_accrual_offers.fund_request_curr_code: '|| l_net_accrual_offers.fund_request_curr_code);
2015
2016 IF l_customer_qualified = 'Y'
2017 THEN
2018 --
2019 IF l_net_accrual_offers.fund_request_curr_code <> l_order_curr_code
2020 THEN
2021 --
2022 l_new_amount := 0;
2023
2024 --Added for bug 7030415
2025 ozf_utility_pvt.write_conc_log('l_order_line_tbl(i).org_id: '|| l_order_line_tbl(i).org_id);
2026 IF l_batch_mode = 'YES' THEN
2027 OPEN c_get_conversion_type(l_order_line_tbl(i).org_id);
2028 FETCH c_get_conversion_type INTO l_exchange_rate_type;
2029 CLOSE c_get_conversion_type;
2030 ozf_utility_pvt.write_conc_log('l_exchange_rate_type: '|| l_exchange_rate_type);
2031 ozf_utility_pvt.write_conc_log('l_line_amount: '|| l_line_amount);
2032 END IF;
2033
2037 ,p_conv_type => l_exchange_rate_type
2034 ozf_utility_pvt.convert_currency(x_return_status => l_return_status
2035 ,p_from_currency => l_order_curr_code
2036 ,p_to_currency => l_net_accrual_offers.fund_request_curr_code
2038 ,p_conv_date => l_order_line_tbl(i).conv_date
2039 ,p_from_amount => l_line_amount
2040 ,x_to_amount => l_new_amount
2041 ,x_rate => l_rate);
2042 l_line_amount := l_new_amount;
2043 ozf_utility_pvt.write_conc_log('l_line_amount converted : '|| l_line_amount);
2044
2045 IF l_return_status = Fnd_Api.g_ret_sts_error
2046 THEN
2047 ozf_utility_pvt.write_conc_log('Exp Error from Convert_Currency: ' || l_return_status);
2048 RAISE Fnd_Api.g_exc_error;
2049 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
2050 THEN
2051 ozf_utility_pvt.write_conc_log('Unexp Error from Convert_Currency: ' || l_return_status);
2052 RAISE Fnd_Api.g_exc_unexpected_error;
2053 END IF;
2054 --
2055 END IF;
2056
2057 ------------------------------ Derive Benificiary -----------------------
2058 IF l_net_accrual_offers.custom_setup_id = 105
2059 THEN
2060 --
2061 IF G_DEBUG_LOW THEN
2062 ozf_utility_pvt.write_conc_log('Pv_Referral_Comp_Pub.Get_Beneficiary (+)');
2063 END IF;
2064 pv_referral_comp_pub.get_beneficiary (p_api_version => 1.0,
2065 p_init_msg_list => FND_API.g_true,
2066 p_commit => FND_API.g_false,
2067 p_validation_level => FND_API.g_valid_level_full,
2068 p_order_header_id => l_order_line_tbl(i).header_id,
2069 p_order_line_id => l_order_line_tbl(i).line_id,
2070 p_offer_id => l_net_accrual_offers.offer_id,
2071 x_beneficiary_id => l_beneficiary_id,
2072 x_referral_id => l_referral_id,
2073 x_return_status => l_return_status,
2074 x_msg_count => l_msg_count,
2075 x_msg_data => l_msg_data);
2076 IF G_DEBUG_LOW THEN
2077 ozf_utility_pvt.write_conc_log('Pv_Referral_Comp_Pub.Get_Beneficiary (-) With Status: '||l_return_status);
2078 ozf_utility_pvt.write_conc_log('l_benificiary_id / l_referral_id: '||l_beneficiary_id || ' / ' || l_referral_id);
2079 END IF;
2080
2081 IF l_return_status = Fnd_Api.g_ret_sts_error
2082 THEN
2083 ozf_utility_pvt.write_conc_log('Exp Error from Get_Beneficiary: ' || l_return_status);
2084 RAISE Fnd_Api.g_exc_error;
2085 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
2086 THEN
2087 ozf_utility_pvt.write_conc_log('Unexp Error from Get_Beneficiary: ' || l_return_status);
2088 RAISE Fnd_Api.g_exc_unexpected_error;
2089 END IF;
2090 --
2091
2092 IF ( l_beneficiary_id IS NOT NULL )
2093 THEN
2094 --------------------------- Derive Accrual Amount -------------------------
2095 IF G_DEBUG_LOW THEN
2096 ozf_utility_pvt.write_conc_log('Get_Pv_Accrual_Amount (+)');
2097 END IF;
2098
2099 l_line_acc_amount := get_pv_accrual_amount(p_product_id => l_order_line_tbl(i).inventory_item_id
2100 ,p_line_amt => l_line_amount
2101 ,p_offer_id => l_net_accrual_offers.offer_id
2102 ,p_org_id => l_org_id
2103 ,p_list_hdr_id => l_net_accrual_offers.qp_list_header_id
2104 ,p_referral_id => l_referral_id
2105 ,p_order_hdr_id => l_order_line_tbl(i).header_id);
2106 IF G_DEBUG_LOW THEN
2107 ozf_utility_pvt.write_conc_log('Get_Pv_Accrual_Amount (-) With l_line_acc_amount: '|| l_line_acc_amount);
2108 END IF;
2109 --
2110 ELSE
2111 --
2112 ozf_utility_pvt.write_conc_log('No Beneficiary derived from PV_Referral_Comp_Pub. Utilization will not be created');
2113 --
2114 END IF;
2115 --
2116 l_utilization_type := 'LEAD_ACCRUAL';
2117 l_reference_type := 'LEAD_REFERRAL';
2118 --
2119 ELSE
2120 --
2124 -- END IF;
2121 --------------------------- Derive Accrual Amount -------------------------
2122 -- IF G_DEBUG_LOW THEN
2123 ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (+)');
2125 l_line_acc_amount := get_accrualed_amount(p_product_id => l_order_line_tbl(i).inventory_item_id
2126 ,p_line_amt => l_line_amount
2127 ,p_quantity => l_order_line_tbl(i).pricing_quantity
2128 ,p_uom => l_order_line_tbl(i).pricing_quantity_uom);
2129 -- IF G_DEBUG_LOW THEN
2130 ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (-) With l_line_acc_amount: '|| l_line_acc_amount);
2131 -- END IF;
2132 --
2133
2134 --
2135 l_utilization_type := 'ACCRUAL';
2136 l_reference_type := NULL;
2137 l_beneficiary_id := l_net_accrual_offers.qualifier_id;
2138 l_referral_id := NULL;
2139 --
2140 END IF; -- End custom_setup_id 105
2141
2142 IF l_batch_mode = 'NO'
2143 THEN
2144 --
2145 IF ( l_beneficiary_id IS NULL
2146 OR
2147 l_beneficiary_id = fnd_api.g_miss_num )
2148 THEN
2149 --
2150 -- Benificiay Id can be NULL only for PV Net Accrual Offers
2151 -- If PV decides not to accrue for this customer, it returns NULL
2152 --
2153 NULL;
2154 ELSE
2155 --
2156 l_act_budgets_rec.act_budget_used_by_id := l_net_accrual_offers.qp_list_header_id;
2157 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
2158 l_act_budgets_rec.budget_source_type := 'OFFR';
2159 l_act_budgets_rec.budget_source_id := l_net_accrual_offers.qp_list_header_id;
2160 l_act_budgets_rec.request_amount := l_line_acc_amount;
2161 l_act_budgets_rec.request_currency := l_net_accrual_offers.fund_request_curr_code;
2162 l_act_budgets_rec.request_date := SYSDATE;
2163 l_act_budgets_rec.status_code := 'APPROVED';
2164 l_act_budgets_rec.approved_amount := l_line_acc_amount;
2165 l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
2166 l_act_budgets_rec.approval_date := SYSDATE;
2167 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
2168 l_act_budgets_rec.justification := 'NA: ' || TO_CHAR(SYSDATE, 'MM/DD/YYYY');
2169 l_act_budgets_rec.transfer_type := 'UTILIZED';
2170 l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
2171
2172 l_act_util_rec.object_type := 'ORDER';
2173 l_act_util_rec.object_id := l_order_line_tbl(i).header_id;
2174 l_act_util_rec.product_level_type := 'PRODUCT';
2175 l_act_util_rec.product_id := l_order_line_tbl(i).inventory_item_id;
2176 l_act_util_rec.cust_account_id := l_beneficiary_id;
2177 l_act_util_rec.utilization_type := l_utilization_type;
2178 l_act_util_rec.adjustment_date := SYSDATE;
2179 l_act_util_rec.gl_date := SYSDATE;
2180 l_act_util_rec.billto_cust_account_id := l_order_line_tbl(i).invoice_to_org_id;
2181 l_act_util_rec.reference_type := l_reference_type;
2182 l_act_util_rec.reference_id := l_referral_id;
2183 l_act_util_rec.order_line_id := l_order_line_tbl(i).line_id;
2184 l_act_util_rec.org_id := l_order_line_tbl(i).org_id;
2185
2186 -- Bug 3463302. Do not create utilization if amount is zero
2187 IF l_act_budgets_rec.request_amount <> 0
2188 THEN
2189 --
2190 ozf_fund_adjustment_pvt.process_act_budgets(x_return_status => l_return_status
2191 ,x_msg_count => l_msg_count
2192 ,x_msg_data => l_msg_data
2193 ,p_act_budgets_rec => l_act_budgets_rec
2194 ,p_act_util_rec => l_act_util_rec
2195 ,x_act_budget_id => l_act_budget_id
2196 ,x_utilized_amount => l_utilized_amount);
2197 --
2198 IF G_DEBUG_LOW THEN
2199 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);
2200 END IF;
2201
2202 IF l_return_status = Fnd_Api.g_ret_sts_error
2203 THEN
2204 ozf_utility_pvt.write_conc_log('Exp Error: Process_Act_Budgets: line_id ( '||l_order_line_tbl(i).line_id
2208 THEN
2205 || ' ) Error: '||l_msg_data);
2206 log_exception(l_act_budgets_rec, l_act_util_rec);
2207 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
2209 ozf_utility_pvt.write_conc_log('UnExp Error: Process_Act_Budgets: line_id ( '||l_order_line_tbl(i).line_id
2210 || ' ) Error: '||l_msg_data);
2211 log_exception(l_act_budgets_rec, l_act_util_rec);
2212 END IF;
2213
2214 l_utilized_amount := 0;
2215 --
2216 END IF; -- end amount <> 0
2217
2218 l_act_budgets_rec := NULL;
2219 l_act_util_rec := NULL;
2220 --
2221 END IF; -- End beneficiary is Not Null
2222
2223 -- End Batch Mode = NO
2224 ELSE
2225 -- If Batch Mode = YES, accumulate accrual.
2226 l_accrual_amount := l_accrual_amount + l_line_acc_amount;
2227 --
2228 END IF; -- End Batch Mode Check
2229 --
2230 END IF; -- Customer Qualfied = 'Y'
2231
2232 -----------------------------------------------------
2233 END LOOP; -- l_order_line_tbl
2234 -----------------------------------------------------
2235 --
2236 EXIT WHEN c_order_line%NOTFOUND;
2237 --
2238 END LOOP; -- Order lines Cursor
2239
2240 CLOSE c_order_line;
2241
2242 IF l_batch_mode = 'YES'
2243 THEN
2244 --
2245 IF l_accrual_amount <> 0
2246 THEN
2247 --
2248 l_beneficiary_id := l_net_accrual_offers.qualifier_id;
2249 l_utilization_type := 'ACCRUAL';
2250 l_reference_type := NULL;
2251 l_referral_id := NULL;
2252
2253 IF l_beneficiary_id IS NULL OR l_beneficiary_id = fnd_api.g_miss_num
2254 THEN
2255 -- This condition will never occur.
2256 -- For PV offers, the Batch Mode is always NO and Beneficiary is always required
2257 -- for a Net Accrual Offer.
2258 NULL;
2259 --
2260 ELSE
2261 --
2262 l_act_budgets_rec.act_budget_used_by_id := l_net_accrual_offers.qp_list_header_id;
2263 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
2264 l_act_budgets_rec.budget_source_type := 'OFFR';
2265 l_act_budgets_rec.budget_source_id := l_net_accrual_offers.qp_list_header_id;
2266 l_act_budgets_rec.request_amount := l_accrual_amount;
2267 l_act_budgets_rec.request_currency := l_net_accrual_offers.fund_request_curr_code;
2268 l_act_budgets_rec.request_date := SYSDATE;
2269 l_act_budgets_rec.status_code := 'APPROVED';
2270 l_act_budgets_rec.approved_amount := l_accrual_amount;
2271 l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
2272 l_act_budgets_rec.approval_date := SYSDATE;
2273 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
2274 l_act_budgets_rec.justification := 'NA: ' || TO_CHAR(SYSDATE, 'MM/DD/YYYY');
2275 l_act_budgets_rec.transfer_type := 'UTILIZED';
2276 l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
2277
2278 l_act_util_rec.cust_account_id := l_beneficiary_id;
2279 l_act_util_rec.utilization_type := l_utilization_type;
2280 l_act_util_rec.adjustment_date := SYSDATE;
2281 l_act_util_rec.gl_date := SYSDATE;
2282 l_act_util_rec.reference_type := l_reference_type;
2283 l_act_util_rec.reference_id := l_referral_id;
2284
2285 ozf_fund_adjustment_pvt.process_act_budgets(x_return_status => l_return_status
2286 ,x_msg_count => l_msg_count
2287 ,x_msg_data => l_msg_data
2288 ,p_act_budgets_rec => l_act_budgets_rec
2289 ,p_act_util_rec => l_act_util_rec
2290 ,x_act_budget_id => l_act_budget_id
2291 ,x_utilized_amount => l_utilized_amount);
2292
2293 IF G_DEBUG_LOW THEN
2294 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);
2295 END IF;
2296
2297 IF l_return_status = Fnd_Api.g_ret_sts_error
2298 THEN
2299 ozf_utility_pvt.write_conc_log('Exp Error: Process_Act_Budgets Error: '||l_msg_data );
2300 log_exception(l_act_budgets_rec, l_act_util_rec);
2301 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
2302 THEN
2303 ozf_utility_pvt.write_conc_log('UnExp Error: Process_Act_Budgets Error: '||l_msg_data );
2304 log_exception(l_act_budgets_rec, l_act_util_rec);
2305 END IF;
2306
2307 l_utilized_amount := 0;
2311 END IF; -- End check beneficiary id
2308 l_act_budgets_rec := NULL;
2309 l_act_util_rec := NULL;
2310 --
2312 --
2313 END IF; -- end l_accrual_amount <> 0
2314 --
2315 END IF; -- end l_batch_mode = 'YES'
2316 --
2317 END IF; -- End OM lines
2318
2319 ozf_utility_pvt.write_conc_log('-- Done Processing Orders -- ');
2320
2321 --------------- Done Processing Orders ------------------------
2322
2323 ozf_utility_pvt.write_conc_log('-- Start Processing Deduction Rules -- ');
2324
2325 FOR l_na_rule_line IN c_na_rule_lines(l_net_accrual_offers.na_rule_header_id)
2326 LOOP
2327 --
2328 l_return_status := FND_API.g_ret_sts_success; -- bug 3655853
2329
2330 OPEN c_na_deduction_rule(l_na_rule_line.na_deduction_rule_id);
2331 FETCH c_na_deduction_rule INTO l_na_deduction_rule;
2332 CLOSE c_na_deduction_rule;
2333
2334 ozf_utility_pvt.write_conc_log('Name / Type / Id / Org : '||
2335 l_na_deduction_rule.name || ' / ' ||
2336 l_na_deduction_rule.transaction_type_code || ' / ' ||
2337 l_na_deduction_rule.deduction_identifier_id || ' / ' ||
2338 l_na_deduction_rule.deduction_identifier_org_id );
2339
2340 ---------------------------------------------------------------
2341 IF l_na_deduction_rule.transaction_source_code = 'AR' THEN
2342 ---------------------------------------------------------------
2343 --
2344 l_ar_dedu_amount := 0;
2345 l_ar_trx_line_tbl.delete;
2346
2347 l_return_status := FND_API.g_ret_sts_success; -- bug 3655853
2348
2349 -- Always set Start Date to offer start date for AR transactions because
2350 -- A transaction "A" can be created on Date1
2351 -- Net Accrual Engine could have run on Date2.It will not pick "A"
2352 -- Transaction "A" could have been completed on Date3
2353 -- Net Accrual Engine is run on Date4. It will still not pick "A" because Date1 is before Date3
2354
2355 -- So, always pick all the completed transaction during the Offer period.
2356 -- Check utilizations table if it has been already processed
2357
2358 l_ar_start_date := l_net_accrual_offers.start_date_active;
2359
2360 OPEN c_ar_trx_line_details(l_na_deduction_rule.deduction_identifier_id,
2361 l_ar_start_date,
2362 l_end_date,
2363 l_na_deduction_rule.deduction_identifier_org_id );
2364
2365 LOOP
2366 --
2367 FETCH c_ar_trx_line_details BULK COLLECT INTO l_ar_trx_line_tbl LIMIT l_batch_size;
2368 --
2369 -- To handle NO DATA FOUND for c_ar_trx_line CURSOR
2370 IF l_ar_trx_line_tbl.FIRST IS NULL
2371 THEN
2372 --
2373 ozf_utility_pvt.write_conc_log('No Data found in c_ar_trx_line_details CURSOR');
2374 EXIT;
2375 --
2376 END IF;
2377 -- Exit after finishing processing is before END LOOP
2378 --
2379 ---------------------------------------------------------
2380 FOR i IN l_ar_trx_line_tbl.FIRST .. l_ar_trx_line_tbl.LAST
2381 LOOP
2382 --
2383 l_customer_qualified := validate_customer(l_ar_trx_line_tbl(i).bill_to_site_use_id,
2384 l_ar_trx_line_tbl(i).ship_to_site_use_id,
2385 l_ar_trx_line_tbl(i).sold_to_customer_id);
2386
2387 ozf_utility_pvt.write_conc_log('Cust_Trx_Line_Id / Customer Qualifier ? : ' ||
2388 l_ar_trx_line_tbl(i).customer_trx_line_id || '/' ||l_customer_qualified );
2389
2390 IF l_customer_qualified = 'Y'
2391 THEN
2392 --
2393 IF l_batch_mode = 'NO'
2394 THEN
2395 --
2396 IF ( l_ar_trx_line_tbl(i).invoice_currency_code
2397 <> l_net_accrual_offers.fund_request_curr_code)
2398 THEN
2399 --
2400 l_new_amount := 0;
2401 --Added for bug 7030415
2402 --only those records are picked for which the org_id=l_na_deduction_rule.deduction_identifier_org_id
2403 OPEN c_get_conversion_type(l_na_deduction_rule.deduction_identifier_org_id);
2404 FETCH c_get_conversion_type INTO l_exchange_rate_type;
2405 CLOSE c_get_conversion_type;
2406 ozf_utility_pvt.convert_currency(
2407 x_return_status => l_return_status
2408 ,p_from_currency => l_ar_trx_line_tbl(i).invoice_currency_code
2409 ,p_to_currency => l_net_accrual_offers.fund_request_curr_code
2410 ,p_conv_type => l_exchange_rate_type
2411 ,p_conv_date => l_ar_trx_line_tbl(i).conv_date
2412 ,p_from_amount => l_ar_trx_line_tbl(i).extended_amount
2413 ,x_to_amount => l_new_amount
2417 IF l_return_status = Fnd_Api.g_ret_sts_error
2414 ,x_rate => l_rate);
2415 l_ar_trx_line_tbl(i).extended_amount := l_new_amount;
2416
2418 THEN
2419 ozf_utility_pvt.write_conc_log('Convert currency ' || l_return_status);
2420 RAISE Fnd_Api.g_exc_error;
2421 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
2422 THEN
2423 ozf_utility_pvt.write_conc_log('Convert currency ' || l_return_status);
2424 RAISE Fnd_Api.g_exc_unexpected_error;
2425 END IF;
2426 --
2427 END IF;
2428
2429 IF ( l_na_deduction_rule.transaction_type_code = 'CM' )
2430 THEN
2431 -- Old calculation
2432 -- l_sign := -1;
2433 -- l_quantity := -1 * NVL(l_ar_trx_line_tbl(i).quantity_credited, -1);
2434
2435 -- New Calculation
2436 -- Record the Sign of the Credit Memo
2437 l_sign := SIGN(l_ar_trx_line_tbl(i).extended_amount);
2438
2439 -- Always send positive value for the quantity, for calculation
2440 l_quantity := NVL(ABS(l_ar_trx_line_tbl(i).quantity_credited), 1);
2441 --
2442 ELSIF ( l_na_deduction_rule.transaction_type_code = 'DM' )
2443 THEN
2444 --
2445 l_sign := 1;
2446 l_quantity := l_ar_trx_line_tbl(i).quantity_invoiced;
2447 --
2448 END IF;
2449
2450 IF G_DEBUG_LOW
2451 THEN
2452 ozf_utility_pvt.write_conc_log('Sign of the Credit Memo : '||l_sign);
2453 ozf_utility_pvt.write_conc_log('quantity_credited : '||l_ar_trx_line_tbl(i).quantity_credited );
2454 END IF;
2455
2456 -- Always send positive values for calculation
2457 l_ar_dedu_line_amt := get_accrualed_amount(
2458 p_product_id => l_ar_trx_line_tbl(i).inventory_item_id
2459 ,p_line_amt => l_sign * l_ar_trx_line_tbl(i).extended_amount
2460 ,p_quantity => l_quantity
2461 ,p_uom => l_ar_trx_line_tbl(i).uom_code);
2462
2463 -- Convert the accrual amount back to the actual CM sign
2464 l_ar_dedu_line_amt := l_sign * l_ar_dedu_line_amt;
2465
2466
2467 -- Check if a utilization has already been created for this transaction
2468 -- for this Offer
2469 -- If Yes, then
2470 -- Check if the existing accrual and current accrual are the same
2471 -- If not, post the difference
2472 -- If No, Create utilization
2473
2474 OPEN c_get_util_amt (l_ar_trx_line_tbl(i).customer_trx_line_id,
2475 l_ar_trx_line_tbl(i).inventory_item_id,
2476 l_net_accrual_offers.qp_list_header_id);
2477 FETCH c_get_util_amt INTO l_existing_util_amt;
2478 CLOSE c_get_util_amt;
2479
2480 -- l_existing_util_amt will return as 0 if no utilziations already exist
2481 -- since the cursor c_get_util_amt has a NVL
2482
2483 IF G_DEBUG_LOW THEN
2484 ozf_utility_pvt.write_conc_log('l_ar_dedu_line_amt (A) : '||l_ar_dedu_line_amt);
2485 ozf_utility_pvt.write_conc_log('l_existing_util_amt (B) : '||l_existing_util_amt);
2486 ozf_utility_pvt.write_conc_log('(A) - (B) : '|| (l_ar_dedu_line_amt - l_existing_util_amt));
2487 END IF;
2488
2489 -- If utilizations do not exist l_existing_util_amt will be 0
2490 -- A - B will be = A
2491 -- If utilzations do exist for the same customer_trx_line_id
2492 -- A - B will be 0 in case of no change. Utilzation will not be created
2493 -- OR
2494 -- A - B will be the correct utilzation amount
2495
2496 l_ar_dedu_line_amt := l_ar_dedu_line_amt - l_existing_util_amt;
2497
2498 l_act_budgets_rec.act_budget_used_by_id := l_net_accrual_offers.qp_list_header_id;
2499 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
2500 l_act_budgets_rec.budget_source_type := 'OFFR';
2501 l_act_budgets_rec.budget_source_id := l_net_accrual_offers.qp_list_header_id;
2502 l_act_budgets_rec.request_amount := l_ar_dedu_line_amt;
2503 l_act_budgets_rec.request_currency := l_net_accrual_offers.fund_request_curr_code;
2504 l_act_budgets_rec.request_date := SYSDATE;
2505 l_act_budgets_rec.status_code := 'APPROVED';
2506 l_act_budgets_rec.approved_amount := l_ar_dedu_line_amt;
2510 l_act_budgets_rec.justification := 'NA: AR DEDUCTION' || TO_CHAR(SYSDATE, 'MM/DD/YYYY');
2507 l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
2508 l_act_budgets_rec.approval_date := SYSDATE;
2509 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
2511 l_act_budgets_rec.transfer_type := 'UTILIZED';
2512 l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
2513
2514 l_act_util_rec.object_type := l_na_deduction_rule.transaction_type_code;
2515 l_act_util_rec.object_id := l_ar_trx_line_tbl(i).customer_trx_id;
2516 l_act_util_rec.product_level_type := 'PRODUCT';
2517 l_act_util_rec.product_id := l_ar_trx_line_tbl(i).inventory_item_id;
2518 l_act_util_rec.cust_account_id := l_net_accrual_offers.qualifier_id;
2519 l_act_util_rec.utilization_type := 'ACCRUAL';
2520 l_act_util_rec.adjustment_date := SYSDATE;
2521 l_act_util_rec.gl_date := SYSDATE;
2522 l_act_util_rec.reference_type := 'TRX_LINE';
2523 l_act_util_rec.reference_id := l_ar_trx_line_tbl(i).customer_trx_line_id;
2524
2525 -- Bug 3463302. dont create utilization if zero amount
2526 IF ( l_act_budgets_rec.request_amount <> 0 )
2527 THEN
2528 --
2529 ozf_fund_adjustment_pvt.process_act_budgets(
2530 x_return_status => l_return_status
2531 ,x_msg_count => l_msg_count
2532 ,x_msg_data => l_msg_data
2533 ,p_act_budgets_rec => l_act_budgets_rec
2534 ,p_act_util_rec => l_act_util_rec
2535 ,x_act_budget_id => l_act_budget_id
2536 ,x_utilized_amount => l_utilized_amount);
2537
2538 IF G_DEBUG_LOW THEN
2539 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);
2540 END IF;
2541
2542 l_utilized_amount := 0;
2543
2544 IF l_return_status = Fnd_Api.g_ret_sts_error
2545 THEN
2546 ozf_utility_pvt.write_conc_log('Msg from Budget API: '||l_msg_data);
2547 log_exception(l_act_budgets_rec, l_act_util_rec);
2548 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
2549 THEN
2550 ozf_utility_pvt.write_conc_log('Msg from Budget API: '||l_msg_data);
2551 log_exception(l_act_budgets_rec, l_act_util_rec);
2552 END IF;
2553 --
2554 END IF; -- End Amount <> 0
2555
2556 l_act_budgets_rec := NULL;
2557 l_act_util_rec := NULL;
2558 --
2559 ELSE
2560 --
2561 l_ar_dedu_amount := l_ar_dedu_amount + l_ar_dedu_line_amt;
2562 --
2563 END IF; -- End Batch Mode
2564 --
2565 END IF; -- End Customer Qualified
2566 --
2567 END LOOP; -- End l_ar_trx_line_tbl
2568 ----------------------------------------
2569 EXIT WHEN c_ar_trx_line_details%NOTFOUND;
2570 ----------------------------------------
2571 END LOOP; -- AR Trx Lines Cursor
2572
2573 CLOSE c_ar_trx_line_details;
2574
2575 IF l_batch_mode = 'YES'
2576 THEN
2577 --
2578 IF l_ar_dedu_amount <> 0
2579 THEN
2580 --
2581 l_act_budgets_rec.act_budget_used_by_id := l_net_accrual_offers.qp_list_header_id;
2582 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
2583 l_act_budgets_rec.budget_source_type := 'OFFR';
2584 l_act_budgets_rec.budget_source_id := l_net_accrual_offers.qp_list_header_id;
2585 l_act_budgets_rec.request_amount := l_ar_dedu_amount;
2586 l_act_budgets_rec.request_currency := l_net_accrual_offers.fund_request_curr_code;
2587 l_act_budgets_rec.request_date := SYSDATE;
2588 l_act_budgets_rec.status_code := 'APPROVED';
2589 l_act_budgets_rec.approved_amount := l_ar_dedu_amount;
2590 l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
2591 l_act_budgets_rec.approval_date := SYSDATE;
2592 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
2593 l_act_budgets_rec.justification := 'NA: AR DEDUCTION' || TO_CHAR(SYSDATE, 'MM/DD/YYYY');
2597 l_act_util_rec.cust_account_id := l_net_accrual_offers.qualifier_id;
2594 l_act_budgets_rec.transfer_type := 'UTILIZED';
2595 l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
2596
2598 l_act_util_rec.utilization_type := 'ACCRUAL';
2599 l_act_util_rec.adjustment_date := SYSDATE;
2600 l_act_util_rec.gl_date := SYSDATE;
2601
2602 IF G_DEBUG_LOW THEN
2603 ozf_utility_pvt.write_conc_log('Accrual log: AR Deduction BATCH_MODE = Y');
2604 ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
2605 ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
2606 ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
2607 ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
2608 ozf_utility_pvt.write_conc_log('Cust Acct Id: '||l_act_util_rec.cust_account_id);
2609 END IF;
2610
2611 ozf_fund_adjustment_pvt.process_act_budgets(
2612 x_return_status => l_return_status
2613 ,x_msg_count => l_msg_count
2614 ,x_msg_data => l_msg_data
2615 ,p_act_budgets_rec => l_act_budgets_rec
2616 ,p_act_util_rec => l_act_util_rec
2617 ,x_act_budget_id => l_act_budget_id
2618 ,x_utilized_amount => l_utilized_amount);
2619
2620 IF G_DEBUG_LOW THEN
2621 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);
2622 END IF;
2623
2624 l_utilized_amount := 0;
2625
2626 IF l_return_status = Fnd_Api.g_ret_sts_error
2627 THEN
2628 log_exception(l_act_budgets_rec, l_act_util_rec);
2629 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
2630 log_exception(l_act_budgets_rec, l_act_util_rec);
2631 END IF;
2632
2633 l_act_budgets_rec := NULL;
2634 l_act_util_rec := NULL;
2635 --
2636 END IF; -- end amount <> 0
2637 --
2638 END IF; -- end batch mode = Y
2639 --
2640 -----------------------------------------------------------------
2641 ELSIF l_na_deduction_rule.transaction_source_code = 'OM' THEN
2642 -----------------------------------------------------------------
2643 --
2644 l_om_dedu_amount := 0;
2645 l_return_line_tbl.delete;
2646
2647 OPEN c_return_line( l_na_deduction_rule.deduction_identifier_id,
2648 l_start_date,
2649 l_end_date);
2650
2651 LOOP
2652 --
2653 FETCH c_return_line BULK COLLECT INTO l_return_line_tbl LIMIT l_batch_size;
2654 --
2655 -- To handle NO DATA FOUND for c_return_line CURSOR
2656 IF l_return_line_tbl.FIRST IS NULL
2657 THEN
2658 --
2659 ozf_utility_pvt.write_conc_log('No Data found in c_return_line CURSOR');
2660 EXIT;
2661 --
2662 END IF;
2663 --
2664 ---------------------------------------------------------
2665 FOR i IN l_return_line_tbl.FIRST .. l_return_line_tbl.LAST
2666 LOOP
2667 ---------------------------------------------------------
2668 --
2669 l_return_status := FND_API.g_ret_sts_success; -- bug 3655853
2670
2671 -- Original value is negtive
2672 l_return_line_tbl(i).invoiced_quantity := -1 * l_return_line_tbl(i).invoiced_quantity;
2673 l_line_amount := l_return_line_tbl(i).invoiced_quantity * l_return_line_tbl(i).unit_selling_price;
2674
2675 IF l_net_accrual_offers.custom_setup_id = 105
2676 THEN
2677 ----- For PV Net Accrual Offers, do not look at denorm -------
2678 ----- Get Country code from the Identifying addresss of the Customer
2679 OPEN c_country_code(l_return_line_tbl(i).invoice_to_org_id);
2680 FETCH c_country_code INTO l_country_code;
2681 CLOSE c_country_code;
2682
2683 -- l_terr_countries_tbl has all the countries eligible for this offer
2684 -- This table is populated in the 'Denorm Customers' section for each PV NA Offer
2685 l_customer_qualified := 'N';
2686
2687 FOR j IN l_terr_countries_tbl.FIRST .. l_terr_countries_tbl.LAST
2688 LOOP
2689 --
2690 IF l_country_code = l_terr_countries_tbl(j)
2691 THEN
2695 --
2692 l_customer_qualified := 'Y';
2693 EXIT;
2694 END IF;
2696 END LOOP;
2697
2698 IF l_customer_qualified = 'N' THEN
2699 -- sold_to not qualified. try ship_to
2700 OPEN c_country_code(l_return_line_tbl(i).ship_to_org_id);
2701 FETCH c_country_code INTO l_country_code;
2702 CLOSE c_country_code;
2703
2704 FOR j IN l_terr_countries_tbl.FIRST .. l_terr_countries_tbl.LAST
2705 LOOP
2706 --
2707 IF l_country_code = l_terr_countries_tbl(j)
2708 THEN
2709 l_customer_qualified := 'Y';
2710 EXIT;
2711 END IF;
2712 --
2713 END LOOP;
2714 --
2715 END IF;
2716 --
2717 ELSE
2718 --
2719 l_customer_qualified := validate_customer(
2720 p_invoice_to_org_id => l_return_line_tbl(i).invoice_to_org_id
2721 ,p_ship_to_org_id => l_return_line_tbl(i).ship_to_org_id
2722 ,p_sold_to_org_id => l_return_line_tbl(i).sold_to_org_id);
2723 END IF;
2724
2725 l_order_curr_code := l_return_line_tbl(i).transactional_curr_code;
2726
2727 IF l_customer_qualified = 'Y'
2728 THEN
2729 --
2730 IF l_net_accrual_offers.fund_request_curr_code <> l_order_curr_code
2731 THEN
2732 --
2733 l_new_amount := 0;
2734
2735 --Added for bug 7030415
2736 OPEN c_get_conversion_type(l_return_line_tbl(i).org_id);
2737 FETCH c_get_conversion_type INTO l_exchange_rate_type;
2738 CLOSE c_get_conversion_type;
2739
2740 ozf_utility_pvt.convert_currency(
2741 x_return_status => l_return_status
2742 ,p_from_currency => l_order_curr_code
2743 ,p_to_currency => l_net_accrual_offers.fund_request_curr_code
2744 ,p_conv_type => l_exchange_rate_type
2745 ,p_conv_date => l_return_line_tbl(i).conv_date
2746 ,p_from_amount => l_line_amount
2747 ,x_to_amount => l_new_amount
2748 ,x_rate => l_rate);
2749 l_line_amount := l_new_amount;
2750
2751 IF l_return_status = Fnd_Api.g_ret_sts_error
2752 THEN
2753 ozf_utility_pvt.write_conc_log('Convert currency ' || l_return_status);
2754 RAISE Fnd_Api.g_exc_error;
2755 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
2756 THEN
2757 ozf_utility_pvt.write_conc_log('Convert currency ' || l_return_status);
2758 RAISE Fnd_Api.g_exc_unexpected_error;
2759 END IF;
2760 --
2761 END IF;
2762 --
2763 l_om_dedu_line_amt := get_accrualed_amount(
2764 p_product_id => l_return_line_tbl(i).inventory_item_id
2765 ,p_line_amt => l_line_amount
2766 ,p_quantity => l_return_line_tbl(i).invoiced_quantity
2767 ,p_uom => l_return_line_tbl(i).pricing_quantity_uom);
2768
2769 -- return needs to be deducted, make it negative
2770 l_om_dedu_line_amt := -1 * l_om_dedu_line_amt;
2771
2772 IF l_batch_mode = 'NO'
2773 THEN
2774 --
2775 IF l_net_accrual_offers.custom_setup_id = 105
2776 THEN
2777 --
2778 pv_referral_comp_pub.get_beneficiary (p_api_version => 1.0,
2779 p_init_msg_list => FND_API.g_false,
2780 p_commit => FND_API.g_false,
2781 p_validation_level => FND_API.g_valid_level_full,
2782 p_order_header_id => l_return_line_tbl(i).header_id,
2783 p_order_line_id => l_return_line_tbl(i).line_id,
2784 p_offer_id => l_net_accrual_offers.offer_id,
2785 x_beneficiary_id => l_beneficiary_id,
2786 x_referral_id => l_referral_id,
2787 x_return_status => l_return_status,
2791 l_utilization_type := 'LEAD_ACCRUAL';
2788 x_msg_count => l_msg_count,
2789 x_msg_data => l_msg_data);
2790
2792 l_reference_type := 'LEAD_REFERRAL';
2793 --
2794 ELSE
2795 --
2796 l_beneficiary_id := l_net_accrual_offers.qualifier_id;
2797 l_utilization_type := 'ACCRUAL';
2798 l_reference_type := NULL;
2799 l_referral_id := NULL;
2800 --
2801 END IF;
2802
2803 IF l_beneficiary_id IS NULL OR l_beneficiary_id = fnd_api.g_miss_num THEN
2804 NULL;
2805 ELSE
2806 l_act_budgets_rec.act_budget_used_by_id := l_net_accrual_offers.qp_list_header_id;
2807 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
2808 l_act_budgets_rec.budget_source_type := 'OFFR';
2809 l_act_budgets_rec.budget_source_id := l_net_accrual_offers.qp_list_header_id;
2810 l_act_budgets_rec.request_amount := l_om_dedu_line_amt;
2811 l_act_budgets_rec.request_currency := l_net_accrual_offers.fund_request_curr_code;
2812 l_act_budgets_rec.request_date := SYSDATE;
2813 l_act_budgets_rec.status_code := 'APPROVED';
2814 l_act_budgets_rec.approved_amount := l_om_dedu_line_amt;
2815 l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
2816 l_act_budgets_rec.approval_date := SYSDATE;
2817 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
2818 l_act_budgets_rec.justification := 'NA: OM Deduction' || TO_CHAR(SYSDATE, 'MON-DD-YYYY');
2819 l_act_budgets_rec.transfer_type := 'UTILIZED';
2820 l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
2821
2822 l_act_util_rec.object_type := 'ORDER';
2823 l_act_util_rec.object_id := l_return_line_tbl(i).header_id;
2824 l_act_util_rec.product_level_type := 'PRODUCT';
2825 l_act_util_rec.product_id := l_return_line_tbl(i).inventory_item_id;
2826 l_act_util_rec.cust_account_id := l_beneficiary_id;
2827 l_act_util_rec.utilization_type := l_utilization_type;
2828 l_act_util_rec.adjustment_date := SYSDATE;
2829 l_act_util_rec.gl_date := SYSDATE;
2830 l_act_util_rec.billto_cust_account_id := l_return_line_tbl(i).invoice_to_org_id;
2831 l_act_util_rec.reference_type := l_reference_type;
2832 l_act_util_rec.reference_id := l_referral_id;
2833 l_act_util_rec.order_line_id := l_return_line_tbl(i).line_id;
2834 l_act_util_rec.org_id := l_return_line_tbl(i).org_id;
2835
2836 IF l_act_budgets_rec.request_amount <> 0
2837 THEN
2838 -- bug 3463302. dont create utilization if zero amount
2839 ozf_fund_adjustment_pvt.process_act_budgets(
2840 x_return_status => l_return_status
2841 ,x_msg_count => l_msg_count
2842 ,x_msg_data => l_msg_data
2843 ,p_act_budgets_rec => l_act_budgets_rec
2844 ,p_act_util_rec => l_act_util_rec
2845 ,x_act_budget_id => l_act_budget_id
2846 ,x_utilized_amount => l_utilized_amount);
2847
2848 IF G_DEBUG_LOW THEN
2849 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);
2850 END IF;
2851
2852 l_utilized_amount := 0;
2853
2854 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
2855 ozf_utility_pvt.write_conc_log('Msg from Budget API: '||l_msg_data);
2856 log_exception(l_act_budgets_rec, l_act_util_rec);
2857 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
2858 ozf_utility_pvt.write_conc_log('Msg from Budget API: '||l_msg_data);
2859 log_exception(l_act_budgets_rec, l_act_util_rec);
2860 END IF;
2861 --
2865 l_act_util_rec := NULL;
2862 END IF; -- end amount <> 0
2863
2864 l_act_budgets_rec := NULL;
2866 END IF;
2867 --
2868 ELSE
2869 --
2870 l_om_dedu_amount := l_om_dedu_amount + l_om_dedu_line_amt;
2871 --
2872 END IF; -- end batch mode
2873 --
2874 END IF; -- end validate customer
2875 --
2876 ---------------------------------------
2877 END LOOP; -- end return order lines
2878 ---------------------------------------
2879 --
2880 EXIT WHEN c_return_line%NOTFOUND;
2881 --
2882 END LOOP; -- Return lines Cursor
2883
2884 CLOSE c_return_line;
2885
2886 IF l_batch_mode = 'YES'
2887 THEN
2888 --
2889 IF l_om_dedu_amount <> 0
2890 THEN
2891 --
2892 l_act_budgets_rec.act_budget_used_by_id := l_net_accrual_offers.qp_list_header_id;
2893 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
2894 l_act_budgets_rec.budget_source_type := 'OFFR';
2895 l_act_budgets_rec.budget_source_id := l_net_accrual_offers.qp_list_header_id;
2896 l_act_budgets_rec.request_amount := l_om_dedu_amount;
2897 l_act_budgets_rec.request_currency := l_net_accrual_offers.fund_request_curr_code;
2898 l_act_budgets_rec.request_date := SYSDATE;
2899 l_act_budgets_rec.status_code := 'APPROVED';
2900 l_act_budgets_rec.approved_amount := l_om_dedu_amount;
2901 l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
2902 l_act_budgets_rec.approval_date := SYSDATE;
2903 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
2904 l_act_budgets_rec.justification := 'NA: ' || TO_CHAR(SYSDATE, 'MON-DD-YYYY');
2905 l_act_budgets_rec.transfer_type := 'UTILIZED';
2906 l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
2907
2908 l_act_util_rec.cust_account_id := l_net_accrual_offers.qualifier_id;
2909 l_act_util_rec.utilization_type := 'ACCRUAL';
2910 l_act_util_rec.adjustment_date := SYSDATE;
2911 l_act_util_rec.gl_date := SYSDATE;
2912
2913 ozf_fund_adjustment_pvt.process_act_budgets(
2914 x_return_status => l_return_status
2915 ,x_msg_count => l_msg_count
2916 ,x_msg_data => l_msg_data
2917 ,p_act_budgets_rec => l_act_budgets_rec
2918 ,p_act_util_rec => l_act_util_rec
2919 ,x_act_budget_id => l_act_budget_id
2920 ,x_utilized_amount => l_utilized_amount);
2921
2922 IF G_DEBUG_LOW THEN
2923 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);
2924 END IF;
2925
2926 l_utilized_amount := 0;
2927
2928 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
2929 ozf_utility_pvt.write_conc_log('Msg from Budget API: '||l_msg_data);
2930 log_exception(l_act_budgets_rec, l_act_util_rec);
2931 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
2932 ozf_utility_pvt.write_conc_log('Msg from Budget API: '||l_msg_data);
2933 log_exception(l_act_budgets_rec, l_act_util_rec);
2934 END IF;
2935
2936 l_act_budgets_rec := NULL;
2937 l_act_util_rec := NULL;
2938 --
2939 END IF; -- end l_om_dedu_amount > 0
2940 --
2941 END IF; -- end l_batch_mode = 'YES'
2942 --
2943 --------------------------------------------------------------
2944 ELSIF l_na_deduction_rule.transaction_source_code = 'TM' THEN
2945 --------------------------------------------------------------
2946
2947 -- Bug 3483348 julou validate market and product eligibility for tm deduction
2948 l_tm_dedu_amount := 0; -- total of tm deduction
2949
2950 FOR l_tm_line IN c_tm_lines(l_na_deduction_rule.deduction_identifier_id
2951 ,l_start_date
2952 ,l_end_date
2953 ,l_net_accrual_offers.qp_list_header_id)
2954 LOOP
2955 --
2956 l_return_status := FND_API.g_ret_sts_success; -- bug 3655853
2957
2958 l_customer_qualified := validate_customer(NULL, NULL, l_tm_line.cust_account_id);
2959
2960 IF l_customer_qualified = 'Y'
2961 THEN
2962 --
2963 IF l_net_accrual_offers.fund_request_curr_code <> l_tm_line.currency_code
2964 THEN
2965
2966 l_new_amount := 0;
2970 CLOSE c_get_conversion_type;
2967 --Added for bug 7030415
2968 OPEN c_get_conversion_type(l_tm_line.org_id);
2969 FETCH c_get_conversion_type INTO l_exchange_rate_type;
2971 ozf_utility_pvt.convert_currency(
2972 x_return_status => l_return_status
2973 ,p_from_currency => l_tm_line.currency_code
2974 ,p_to_currency => l_net_accrual_offers.fund_request_curr_code
2975 ,p_conv_type => l_exchange_rate_type
2976 ,p_conv_date => l_tm_line.conv_date
2977 ,p_from_amount => l_tm_line.line_amount
2978 ,x_to_amount => l_tm_dedu_line_amt
2979 ,x_rate => l_rate);
2980 --
2981 IF l_return_status = Fnd_Api.g_ret_sts_error
2982 THEN
2983 --
2984 ozf_utility_pvt.write_conc_log('Convert currency ' || l_return_status);
2985 RAISE Fnd_Api.g_exc_error;
2986 --
2987 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
2988 THEN
2989 --
2990 ozf_utility_pvt.write_conc_log('Convert currency ' || l_return_status);
2991 RAISE Fnd_Api.g_exc_unexpected_error;
2992 --
2993 END IF;
2994 --
2995 END IF;
2996
2997 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
2998 RAISE Fnd_Api.g_exc_error;
2999 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3000 RAISE Fnd_Api.g_exc_unexpected_error;
3001 END IF;
3002
3003 IF l_tm_dedu_line_amt <> 0
3004 THEN
3005 --
3006 l_tm_dedu_amount := l_tm_dedu_amount + l_tm_dedu_line_amt; -- add up total tm deduction
3007 --
3008 END IF;
3009 --
3010 END IF; -- end cust acct qualified
3011
3012 END LOOP;
3013
3014 IF l_tm_dedu_amount <> 0
3015 THEN
3016 --
3017 l_act_budgets_rec.act_budget_used_by_id := l_net_accrual_offers.qp_list_header_id;
3018 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
3019 l_act_budgets_rec.budget_source_type := 'OFFR';
3020 l_act_budgets_rec.budget_source_id := l_net_accrual_offers.qp_list_header_id;
3021 l_act_budgets_rec.request_amount := -1 * l_tm_dedu_amount;
3022 l_act_budgets_rec.request_currency := l_net_accrual_offers.fund_request_curr_code;
3023 l_act_budgets_rec.request_date := SYSDATE;
3024 l_act_budgets_rec.status_code := 'APPROVED';
3025 l_act_budgets_rec.approved_amount := -1 * l_tm_dedu_amount;
3026 l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
3027 l_act_budgets_rec.approval_date := SYSDATE;
3028 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
3029 l_act_budgets_rec.justification := 'NA: TM DEDUCTION' || TO_CHAR(SYSDATE, 'MON-DD-YYYY');
3030 l_act_budgets_rec.transfer_type := 'UTILIZED';
3031 l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
3032
3033 l_act_util_rec.object_type := l_na_deduction_rule.transaction_type_code; -- OFFR
3034 l_act_util_rec.object_id := l_na_deduction_rule.deduction_identifier_id; -- activity_media_id
3035 l_act_util_rec.cust_account_id := l_net_accrual_offers.qualifier_id;
3036 l_act_util_rec.utilization_type := 'ACCRUAL';
3037 l_act_util_rec.adjustment_date := SYSDATE;
3038 l_act_util_rec.gl_date := SYSDATE;
3039
3040 ozf_utility_pvt.write_conc_log('Accrual log: TM Deduction BATCH_MODE = Y');
3041 ozf_utility_pvt.write_conc_log('Offer PK: '||l_net_accrual_offers.qp_list_header_id);
3042 ozf_utility_pvt.write_conc_log('Custom Setup Id: '||l_net_accrual_offers.custom_setup_id);
3043 ozf_utility_pvt.write_conc_log('Deduction Curr Code: '||l_net_accrual_offers.fund_request_curr_code);
3044 ozf_utility_pvt.write_conc_log('Deduction Amount: '||l_act_budgets_rec.request_amount);
3045 ozf_utility_pvt.write_conc_log('Cust Acct Id: '||l_act_util_rec.cust_account_id);
3046
3047 IF l_act_budgets_rec.request_amount <> 0
3048 THEN -- bug 3463302. dont create utilization if zero amount
3049 ozf_fund_adjustment_pvt.process_act_budgets(x_return_status => l_return_status
3050 ,x_msg_count => l_msg_count
3051 ,x_msg_data => l_msg_data
3052 ,p_act_budgets_rec => l_act_budgets_rec
3053 ,p_act_util_rec => l_act_util_rec
3054 ,x_act_budget_id => l_act_budget_id
3055 ,x_utilized_amount => l_utilized_amount);
3056
3057 IF G_DEBUG_LOW THEN
3061 ozf_utility_pvt.write_conc_log('Msg from Budget API: '||l_msg_data);
3058 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);
3059 END IF;
3060 l_utilized_amount := 0;
3062 IF l_return_status = Fnd_Api.g_ret_sts_error THEN
3063 log_exception(l_act_budgets_rec, l_act_util_rec);
3064 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
3065 log_exception(l_act_budgets_rec, l_act_util_rec);
3066 END IF;
3067 END IF; -- end amount <> 0
3068
3069 l_act_budgets_rec := NULL;
3070 l_act_util_rec := NULL;
3071 END IF;
3072 END IF;
3073 END LOOP; -- end l_na_rule_line
3074
3075 ozf_utility_pvt.write_conc_log('-- Done Processing Deduction Rules -- ');
3076 ozf_utility_pvt.write_conc_log('--------------------------------------');
3077
3078 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
3079 UPDATE ozf_offers
3080 SET latest_na_completion_date = l_as_of_date
3081 WHERE offer_id = l_net_accrual_offers.offer_id;
3082 END IF;
3083
3084 <<IDSM>>
3085 --------------- Start Processing IDSM lines ------------------------
3086 IF l_net_accrual_offers.sales_method_flag IS NULL OR l_net_accrual_offers.sales_method_flag = 'I' THEN
3087 --
3088 ozf_utility_pvt.write_conc_log('Start Processing IDSM Lines');
3089 l_idsm_line_tbl.delete;
3090 l_accrual_amount := 0;
3091
3092 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);
3093
3094 LOOP
3095 --
3096 FETCH c_idsm_line BULK COLLECT INTO l_idsm_line_tbl LIMIT l_batch_size;
3097 --
3098 -- To handle NO DATA FOUND for c_idsm_line CURSOR
3099 IF l_idsm_line_tbl.FIRST IS NULL
3100 THEN
3101 --
3102 ozf_utility_pvt.write_conc_log('No Data found in c_idsm_line CURSOR');
3103 EXIT;
3104 --
3105 END IF;
3106 --
3107 -- Logic to exit after all the record have been processed
3108 -- is just before the END LOOP EXIT WHEN c_idsm_line%NOTFOUND;
3109
3110 ---------------------------------------------------------
3111 FOR i IN l_idsm_line_tbl.FIRST .. l_idsm_line_tbl.LAST
3112 LOOP
3113 ---------------------------------------------------------
3114 --
3115
3116 l_return_status := FND_API.g_ret_sts_success;
3117
3118 l_idsm_line_processed := l_idsm_line_tbl(i).line_id;
3119
3120 IF G_DEBUG_LOW THEN
3121 ozf_utility_pvt.write_conc_log('Resale Line_Id: ' || l_idsm_line_tbl(i).line_id);
3122 END IF;
3123
3124 l_line_amount := ( NVL(l_idsm_line_tbl(i).shipped_quantity,l_idsm_line_tbl(i).fulfilled_quantity)
3125 * l_idsm_line_tbl(i).unit_selling_price );
3126 --
3127 ------------- Qualify Customer on the IDSM line ------------------------------
3128 --
3129
3130 IF l_net_accrual_offers.custom_setup_id = 105
3131 THEN
3132 ----- For PV Net Accrual Offers, do not look at denorm -------
3133 ----- Get Country code from the Identifying addresss of the Customer
3134 OPEN c_country_code(l_idsm_line_tbl(i).invoice_to_org_id);
3135 FETCH c_country_code INTO l_country_code;
3136 CLOSE c_country_code;
3137
3138 -- l_terr_countries_tbl has all the countries eligible for this offer
3139 -- This table is populated in the 'Denorm Customers' section for each PV NA Offer
3140 l_customer_qualified := 'N';
3141
3142 FOR j IN l_terr_countries_tbl.FIRST .. l_terr_countries_tbl.LAST
3143 LOOP
3144 --
3145 IF l_country_code = l_terr_countries_tbl(j)
3146 THEN
3147 l_customer_qualified := 'Y';
3148 EXIT;
3149 END IF;
3150 --
3151 END LOOP;
3152
3153 IF l_customer_qualified = 'N' THEN
3154 -- sold_to not qualified. try ship_to
3155 OPEN c_country_code(l_idsm_line_tbl(i).ship_to_org_id);
3156 FETCH c_country_code INTO l_country_code;
3157 CLOSE c_country_code;
3158
3159 FOR j IN l_terr_countries_tbl.FIRST .. l_terr_countries_tbl.LAST
3160 LOOP
3161 --
3162 IF l_country_code = l_terr_countries_tbl(j)
3163 THEN
3164 l_customer_qualified := 'Y';
3165 EXIT;
3166 END IF;
3167 --
3168 END LOOP;
3169 --
3170 END IF;
3171 --
3172 ELSE
3173 ----- For all other Net Accrual offers, look at denorm -------
3174 l_customer_qualified := validate_customer(p_invoice_to_org_id => l_idsm_line_tbl(i).invoice_to_org_id
3178 END IF; -- Done qualfiying the customer
3175 ,p_ship_to_org_id => l_idsm_line_tbl(i).ship_to_org_id
3176 ,p_sold_to_org_id => l_idsm_line_tbl(i).sold_to_org_id);
3177 --
3179
3180 IF G_DEBUG_LOW THEN
3181 ozf_utility_pvt.write_conc_log('Did Customer qualify: ' || l_customer_qualified);
3182 END IF;
3183
3184 -- Fetch Currency Code on the IDSM
3185 l_order_curr_code := l_idsm_line_tbl(i).transactional_curr_code ;
3186
3187 IF l_customer_qualified = 'Y'
3188 THEN
3189 --
3190 IF l_net_accrual_offers.fund_request_curr_code <> l_order_curr_code
3191 THEN
3192 --
3193 l_new_amount := 0;
3194 --Added for bug 7030415
3195 OPEN c_get_conversion_type(l_idsm_line_tbl(i).org_id);
3196 FETCH c_get_conversion_type INTO l_exchange_rate_type;
3197 CLOSE c_get_conversion_type;
3198 ozf_utility_pvt.convert_currency(x_return_status => l_return_status
3199 ,p_from_currency => l_order_curr_code
3200 ,p_to_currency => l_net_accrual_offers.fund_request_curr_code
3201 ,p_conv_type => l_exchange_rate_type
3202 ,p_conv_date => l_idsm_line_tbl(i).conv_date
3203 ,p_from_amount => l_line_amount
3204 ,x_to_amount => l_new_amount
3205 ,x_rate => l_rate);
3206 l_line_amount := l_new_amount;
3207
3208 IF l_return_status = Fnd_Api.g_ret_sts_error
3209 THEN
3210 ozf_utility_pvt.write_conc_log('Exp Error from Convert_Currency: ' || l_return_status);
3211 RAISE Fnd_Api.g_exc_error;
3212 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
3213 THEN
3214 ozf_utility_pvt.write_conc_log('Unexp Error from Convert_Currency: ' || l_return_status);
3215 RAISE Fnd_Api.g_exc_unexpected_error;
3216 END IF;
3217 --
3218 END IF;
3219
3220 ------------------------------ Derive Benificiary -----------------------
3221 IF l_net_accrual_offers.custom_setup_id = 105
3222 THEN
3223 --
3224 IF G_DEBUG_LOW THEN
3225 ozf_utility_pvt.write_conc_log('Pv_Referral_Comp_Pub.Get_Beneficiary (+)');
3226 END IF;
3227 pv_referral_comp_pub.get_beneficiary (p_api_version => 1.0,
3228 p_init_msg_list => FND_API.g_true,
3229 p_commit => FND_API.g_false,
3230 p_validation_level => FND_API.g_valid_level_full,
3231 p_order_header_id => l_idsm_line_tbl(i).header_id,
3232 p_order_line_id => l_idsm_line_tbl(i).line_id,
3233 p_offer_id => l_net_accrual_offers.offer_id,
3234 x_beneficiary_id => l_beneficiary_id,
3235 x_referral_id => l_referral_id,
3236 x_return_status => l_return_status,
3237 x_msg_count => l_msg_count,
3238 x_msg_data => l_msg_data);
3239 IF G_DEBUG_LOW THEN
3240 ozf_utility_pvt.write_conc_log('Pv_Referral_Comp_Pub.Get_Beneficiary (-) With Status: ' || l_return_status);
3241 ozf_utility_pvt.write_conc_log('l_benificiary_id / l_referral_id: ' || l_beneficiary_id || ' / ' || l_referral_id);
3242 END IF;
3243
3244 IF l_return_status = Fnd_Api.g_ret_sts_error
3245 THEN
3246 ozf_utility_pvt.write_conc_log('Exp Error from Get_Beneficiary: ' || l_return_status);
3247 RAISE Fnd_Api.g_exc_error;
3248 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
3249 THEN
3250 ozf_utility_pvt.write_conc_log('Unexp Error from Get_Beneficiary: ' || l_return_status);
3251 RAISE Fnd_Api.g_exc_unexpected_error;
3252 END IF;
3253 --
3254
3255 IF ( l_beneficiary_id IS NOT NULL )
3256 THEN
3257 --------------------------- Derive Accrual Amount -------------------------
3258 IF G_DEBUG_LOW THEN
3259 ozf_utility_pvt.write_conc_log('Get_Pv_Accrual_Amount (+)');
3260 END IF;
3261
3262 l_line_acc_amount := get_pv_accrual_amount(p_product_id => l_idsm_line_tbl(i).inventory_item_id
3263 ,p_line_amt => l_line_amount
3264 ,p_offer_id => l_net_accrual_offers.offer_id
3265 ,p_org_id => l_org_id
3269 IF G_DEBUG_LOW THEN
3266 ,p_list_hdr_id => l_net_accrual_offers.qp_list_header_id
3267 ,p_referral_id => l_referral_id
3268 ,p_order_hdr_id => l_idsm_line_tbl(i).header_id);
3270 ozf_utility_pvt.write_conc_log('Get_Pv_Accrual_Amount (-) With l_line_acc_amount: ' || l_line_acc_amount);
3271 END IF;
3272 --
3273 ELSE
3274 --
3275 ozf_utility_pvt.write_conc_log('No Beneficiary derived from PV_Referral_Comp_Pub. Utilization will not be created');
3276 --
3277 END IF;
3278 --
3279 l_utilization_type := 'LEAD_ACCRUAL';
3280 l_reference_type := 'LEAD_REFERRAL';
3281 --
3282 ELSE
3283 --
3284 --------------------------- Derive Accrual Amount -------------------------
3285 IF G_DEBUG_LOW THEN
3286 ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (+)');
3287 END IF;
3288 l_line_acc_amount := get_accrualed_amount(p_product_id => l_idsm_line_tbl(i).inventory_item_id
3289 ,p_line_amt => l_line_amount
3290 ,p_quantity => l_idsm_line_tbl(i).pricing_quantity
3291 ,p_uom => l_idsm_line_tbl(i).pricing_quantity_uom);
3292 IF G_DEBUG_LOW THEN
3293 ozf_utility_pvt.write_conc_log('Get_Accrualed_Amount (-) With l_line_acc_amount: ' || l_line_acc_amount);
3294 END IF;
3295 --
3296
3297 --
3298 l_utilization_type := 'ACCRUAL';
3299 l_reference_type := NULL;
3300 l_beneficiary_id := l_net_accrual_offers.qualifier_id;
3301 l_referral_id := NULL;
3302 --
3303 END IF; -- End custom_setup_id 105
3304
3305 IF l_batch_mode = 'NO'
3306 THEN
3307 --
3308 IF ( l_beneficiary_id IS NULL
3309 OR
3310 l_beneficiary_id = fnd_api.g_miss_num )
3311 THEN
3312 --
3313 -- Benificiay Id can be NULL only for PV Net Accrual Offers
3314 -- If PV decides not to accrue for this customer, it returns NULL
3315 --
3316 NULL;
3317 ELSE
3318 --
3319 l_act_budgets_rec.act_budget_used_by_id := l_net_accrual_offers.qp_list_header_id;
3320 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
3321 l_act_budgets_rec.budget_source_type := 'OFFR';
3322 l_act_budgets_rec.budget_source_id := l_net_accrual_offers.qp_list_header_id;
3323 l_act_budgets_rec.request_amount := l_line_acc_amount;
3324 l_act_budgets_rec.request_currency := l_net_accrual_offers.fund_request_curr_code;
3325 l_act_budgets_rec.request_date := SYSDATE;
3326 l_act_budgets_rec.status_code := 'APPROVED';
3327 l_act_budgets_rec.approved_amount := l_line_acc_amount;
3328 l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
3329 l_act_budgets_rec.approval_date := SYSDATE;
3330 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
3331 l_act_budgets_rec.justification := 'NA: ' || TO_CHAR(SYSDATE, 'MM/DD/YYYY');
3332 l_act_budgets_rec.transfer_type := 'UTILIZED';
3333 l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
3334
3335 l_act_util_rec.object_type := 'TP_ORDER';
3336 l_act_util_rec.object_id := l_idsm_line_tbl(i).line_id;
3337 l_act_util_rec.product_level_type := 'PRODUCT';
3338 l_act_util_rec.product_id := l_idsm_line_tbl(i).inventory_item_id;
3339 l_act_util_rec.cust_account_id := l_beneficiary_id;
3340 l_act_util_rec.utilization_type := l_utilization_type;
3341 l_act_util_rec.adjustment_date := SYSDATE;
3342 l_act_util_rec.gl_date := SYSDATE;
3343 l_act_util_rec.billto_cust_account_id := l_idsm_line_tbl(i).invoice_to_org_id;
3344 l_act_util_rec.reference_type := l_reference_type;
3345 l_act_util_rec.reference_id := l_referral_id;
3346 l_act_util_rec.order_line_id := l_idsm_line_tbl(i).line_id;
3347 l_act_util_rec.org_id := l_idsm_line_tbl(i).org_id;
3348
3349 -- Bug 3463302. Do not create utilization if amount is zero
3350 IF l_act_budgets_rec.request_amount <> 0
3351 THEN
3352 --
3356 ,p_act_budgets_rec => l_act_budgets_rec
3353 ozf_fund_adjustment_pvt.process_act_budgets(x_return_status => l_return_status
3354 ,x_msg_count => l_msg_count
3355 ,x_msg_data => l_msg_data
3357 ,p_act_util_rec => l_act_util_rec
3358 ,x_act_budget_id => l_act_budget_id
3359 ,x_utilized_amount => l_utilized_amount);
3360 --
3361 IF G_DEBUG_LOW THEN
3362 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);
3363 END IF;
3364
3365 IF l_return_status = Fnd_Api.g_ret_sts_error
3366 THEN
3367 ozf_utility_pvt.write_conc_log('Exp Error: Process_Act_Budgets: Resale line_id ( ' || l_idsm_line_tbl(i).line_id
3368 || ' ) Error: ' || l_msg_data);
3369 log_exception(l_act_budgets_rec, l_act_util_rec);
3370 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
3371 THEN
3372 ozf_utility_pvt.write_conc_log('UnExp Error: Process_Act_Budgets: Resale line_id ( ' || l_idsm_line_tbl(i).line_id
3373 || ' ) Error: ' || l_msg_data);
3374 log_exception(l_act_budgets_rec, l_act_util_rec);
3375 END IF;
3376
3377 l_utilized_amount := 0;
3378 --
3379 END IF; -- end amount <> 0
3380
3381 l_act_budgets_rec := NULL;
3382 l_act_util_rec := NULL;
3383 --
3384 END IF; -- End beneficiary is Not Null
3385
3386 -- End Batch Mode = NO
3387 ELSE
3388 -- If Batch Mode = YES, accumulate accrual.
3389 l_accrual_amount := l_accrual_amount + l_line_acc_amount;
3390 --
3391 END IF; -- End Batch Mode Check
3392 --
3393 END IF; -- Customer Qualfied = 'Y'
3394
3395 -----------------------------------------------------
3396 END LOOP; -- l_idsm_line_tbl
3397 -----------------------------------------------------
3398 --
3399 EXIT WHEN c_idsm_line%NOTFOUND;
3400 --
3401 END LOOP; -- IDSM lines Cursor
3402
3403 CLOSE c_idsm_line;
3404
3405 IF l_batch_mode = 'YES'
3406 THEN
3407 --
3408 IF l_accrual_amount <> 0
3409 THEN
3410 --
3411 l_beneficiary_id := l_net_accrual_offers.qualifier_id;
3412 l_utilization_type := 'ACCRUAL';
3413 l_reference_type := NULL;
3414 l_referral_id := NULL;
3415
3416 IF l_beneficiary_id IS NULL OR l_beneficiary_id = fnd_api.g_miss_num
3417 THEN
3418 -- This condition will never occur.
3419 -- For PV offers, the Batch Mode is always NO and Beneficiary is always required
3420 -- for a Net Accrual Offer.
3421 NULL;
3422 --
3423 ELSE
3424 --
3425 l_act_budgets_rec.act_budget_used_by_id := l_net_accrual_offers.qp_list_header_id;
3426 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
3427 l_act_budgets_rec.budget_source_type := 'OFFR';
3428 l_act_budgets_rec.budget_source_id := l_net_accrual_offers.qp_list_header_id;
3429 l_act_budgets_rec.request_amount := l_accrual_amount;
3430 l_act_budgets_rec.request_currency := l_net_accrual_offers.fund_request_curr_code;
3431 l_act_budgets_rec.request_date := SYSDATE;
3432 l_act_budgets_rec.status_code := 'APPROVED';
3433 l_act_budgets_rec.approved_amount := l_accrual_amount;
3434 l_act_budgets_rec.approved_in_currency := l_net_accrual_offers.fund_request_curr_code;
3435 l_act_budgets_rec.approval_date := SYSDATE;
3436 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id(FND_GLOBAL.user_id);
3437 l_act_budgets_rec.justification := 'NA: ' || TO_CHAR(SYSDATE, 'MM/DD/YYYY');
3438 l_act_budgets_rec.transfer_type := 'UTILIZED';
3439 l_act_budgets_rec.requester_id := l_net_accrual_offers.owner_id;
3440
3441 l_act_util_rec.cust_account_id := l_beneficiary_id;
3442 l_act_util_rec.utilization_type := l_utilization_type;
3443 l_act_util_rec.adjustment_date := SYSDATE;
3444 l_act_util_rec.gl_date := SYSDATE;
3445 l_act_util_rec.reference_type := l_reference_type;
3446 l_act_util_rec.reference_id := l_referral_id;
3447
3451 ,p_act_budgets_rec => l_act_budgets_rec
3448 ozf_fund_adjustment_pvt.process_act_budgets(x_return_status => l_return_status
3449 ,x_msg_count => l_msg_count
3450 ,x_msg_data => l_msg_data
3452 ,p_act_util_rec => l_act_util_rec
3453 ,x_act_budget_id => l_act_budget_id
3454 ,x_utilized_amount => l_utilized_amount);
3455
3456 IF G_DEBUG_LOW THEN
3457 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);
3458 END IF;
3459
3460 IF l_return_status = Fnd_Api.g_ret_sts_error
3461 THEN
3462 ozf_utility_pvt.write_conc_log('Exp Error: Process_Act_Budgets Error: ' || l_msg_data );
3463 log_exception(l_act_budgets_rec, l_act_util_rec);
3464 ELSIF l_return_status = Fnd_Api.g_ret_sts_unexp_error
3465 THEN
3466 ozf_utility_pvt.write_conc_log('UnExp Error: Process_Act_Budgets Error: ' || l_msg_data );
3467 log_exception(l_act_budgets_rec, l_act_util_rec);
3468 END IF;
3469
3470 l_utilized_amount := 0;
3471 l_act_budgets_rec := NULL;
3472 l_act_util_rec := NULL;
3473 --
3474 END IF; -- End check beneficiary id
3475 --
3476 END IF; -- end l_accrual_amount <> 0
3477 --
3478 END IF; -- end l_batch_mode = 'YES'
3479
3480 UPDATE ozf_offers
3481 SET resale_line_id_processed = l_idsm_line_processed
3482 WHERE offer_id = l_net_accrual_offers.offer_id;
3483 --
3484 END IF; -- End IDSM lines
3485
3486 <<NEXT_OFFER>>
3487
3488 DELETE FROM ozf_na_customers_temp;
3489 DELETE FROM ozf_na_products_temp;
3490
3491 END LOOP;
3492
3493 ozf_utility_pvt.write_conc_log('-- Done -- ' || to_char(sysdate,'MM/DD/YYYY:HH:MM:SS'));
3494
3495
3496 Fnd_Msg_Pub.Count_AND_Get(p_count => l_msg_count,
3497 p_data => l_msg_data,
3498 p_encoded => Fnd_Api.G_FALSE);
3499
3500 EXCEPTION
3501
3502 WHEN OZF_Utility_PVT.resource_locked THEN
3503 OZF_Utility_PVT.Error_Message(p_message_name => 'OZF_API_RESOURCE_LOCKED');
3504
3505 WHEN FND_API.G_EXC_ERROR THEN
3506 ROLLBACK TO net_accrual_engine;
3507 -- Standard call to get message count and if count=1, get the message
3508 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3509 p_count => l_msg_count,
3510 p_data => l_msg_data);
3511
3512 ERRBUF := l_msg_data;
3513 RETCODE := '2';
3514
3515 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3516 ROLLBACK TO net_accrual_engine;
3517 -- Standard call to get message count and if count=1, get the message
3518 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3519 p_count => l_msg_count,
3520 p_data => l_msg_data);
3521
3522 ERRBUF := l_msg_data;
3523 RETCODE := '2';
3524
3525 WHEN OTHERS THEN
3526 ROLLBACK TO net_accrual_engine;
3527 -- Standard call to get message count and if count=1, get the message
3528 FND_MSG_PUB.Count_And_Get(p_encoded => FND_API.G_FALSE,
3529 p_count => l_msg_count,
3530 p_data => l_msg_data);
3531 --ERRBUF := l_msg_data;
3532 ERRBUF := SQLERRM;
3533 RETCODE := '2';
3534
3535 END net_accrual_engine;
3536
3537
3538 /****
3539 -- Redundate procedure. Remove the call from the accrual engine
3540 ****/
3541
3542 PROCEDURE retroactive_offer_adj(
3543 p_api_version IN NUMBER
3544 ,p_init_msg_list IN VARCHAR2
3545 ,p_commit IN VARCHAR2
3546 ,x_return_status OUT NOCOPY VARCHAR2
3547 ,x_msg_count OUT NOCOPY NUMBER
3548 ,x_msg_data OUT NOCOPY VARCHAR2
3549 ,p_offer_id IN NUMBER
3550 ,p_start_date IN DATE
3551 ,p_end_date IN DATE
3552 ,x_order_line_tbl OUT NOCOPY order_line_tbl_type)
3553 IS
3554 --
3555 CURSOR c_offer_type IS
3556 SELECT offer_type,
3557 tier_level,
3558 qp_list_header_id,
3559 custom_setup_id
3560 FROM ozf_offers
3561 WHERE offer_id = p_offer_id;
3562
3563 CURSOR c_order_line_detail1 IS
3564 SELECT a.*
3565 FROM oe_order_lines_all a
3566 WHERE TRUNC(NVL(a.actual_shipment_date,a.fulfillment_date))
3567 BETWEEN TRUNC(p_start_date) AND TRUNC(p_end_date)
3568 AND a.flow_status_code IN ('SHIPPED','CLOSED')
3569 AND a.cancelled_flag = 'N'
3570 AND a.line_category_code <> 'RETURN';
3571
3572 l_offer_type VARCHAR2(30);
3573 l_tier_level VARCHAR2(30);
3574 l_country_code VARCHAR2(60);
3578 l_tbl_index NUMBER := 1;
3575 l_qp_list_header_id NUMBER;
3576 l_customer_qualified VARCHAR2(1);
3577 l_product_qualified VARCHAR2(1);
3579 l_api_name CONSTANT VARCHAR2(30) := 'retroactive_offer_adj';
3580 l_custom_setup_id NUMBER;
3581
3582 BEGIN
3583
3584 SAVEPOINT retroactive_offer_adj;
3585
3586 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
3587 Fnd_Msg_Pub.initialize;
3588 END IF;
3589
3590 x_return_status := Fnd_Api.g_ret_sts_success;
3591
3592 OPEN c_offer_type;
3593 FETCH c_offer_type INTO l_offer_type, l_tier_level, l_qp_list_header_id, l_custom_setup_id;
3594 CLOSE c_offer_type;
3595
3596 FOR l_order_line_detail1 IN c_order_line_detail1 LOOP
3597 l_customer_qualified := validate_customer(p_invoice_to_org_id => l_order_line_detail1.invoice_to_org_id
3598 ,p_ship_to_org_id => l_order_line_detail1.ship_to_org_id
3599 ,p_sold_to_org_id => l_order_line_detail1.sold_to_org_id
3600 ,p_qp_list_header_id => l_qp_list_header_id);
3601
3602 l_product_qualified := validate_product(p_inventory_item_id => l_order_line_detail1.inventory_item_id
3603 ,p_qp_list_header_id => l_qp_list_header_id);
3604
3605 IF l_customer_qualified = 'Y' AND l_product_qualified = 'Y' THEN
3606 x_order_line_tbl(l_tbl_index).order_header_id := l_order_line_detail1.header_id;
3607 x_order_line_tbl(l_tbl_index).order_line_id := l_order_line_detail1.line_id;
3608 l_tbl_index := l_tbl_index + 1;
3609 END IF;
3610 END LOOP;
3611
3612 EXCEPTION
3613 WHEN Fnd_Api.G_EXC_ERROR THEN
3614 x_return_status := Fnd_Api.g_ret_sts_error;
3615 ROLLBACK TO retroactive_offer_adj;
3616 Fnd_Msg_Pub.Count_AND_Get
3617 ( p_count => x_msg_count,
3618 p_data => x_msg_data,
3619 p_encoded => Fnd_Api.G_FALSE
3620 );
3621 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
3622 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
3623 ROLLBACK TO retroactive_offer_adj;
3624 Fnd_Msg_Pub.Count_AND_Get
3625 ( p_count => x_msg_count,
3626 p_data => x_msg_data,
3627 p_encoded => Fnd_Api.G_FALSE
3628 );
3629 WHEN OTHERS THEN
3630 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
3631 ROLLBACK TO retroactive_offer_adj;
3632 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
3633 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3634 END IF;
3635 Fnd_Msg_Pub.Count_AND_Get
3636 ( p_count => x_msg_count,
3637 p_data => x_msg_data,
3638 p_encoded => Fnd_Api.G_FALSE
3639 );
3640
3641 END retroactive_offer_adj;
3642
3643
3644 PROCEDURE offer_adj_new_product(
3645 p_api_version IN NUMBER
3646 ,p_init_msg_list IN VARCHAR2
3647 ,p_commit IN VARCHAR2
3648 ,x_return_status OUT NOCOPY VARCHAR2
3649 ,x_msg_count OUT NOCOPY NUMBER
3650 ,x_msg_data OUT NOCOPY VARCHAR2
3651 ,p_offer_id IN NUMBER
3652 ,p_product_id IN NUMBER
3653 ,p_start_date IN DATE
3654 ,p_end_date IN DATE
3655 ,x_order_line_tbl OUT NOCOPY order_line_tbl_type)
3656 IS
3657
3658 l_header_id_tbl number_tbl_type;
3659 l_line_id_tbl number_tbl_type;
3660 l_invoice_to_org_id_tbl number_tbl_type;
3661 l_ship_to_org_id_tbl number_tbl_type;
3662 l_sold_to_org_id_tbl number_tbl_type;
3663
3664 CURSOR c_offer_type IS
3665 SELECT offer_type, tier_level, qp_list_header_id, custom_setup_id
3666 FROM ozf_offers
3667 WHERE offer_id = p_offer_id;
3668
3669 CURSOR c_order_line IS
3670 SELECT a.header_id,
3671 a.line_id,
3672 a.invoice_to_org_id,
3673 a.ship_to_org_id,
3674 a.sold_to_org_id
3675 FROM oe_order_lines_all a
3676 WHERE (NVL(a.actual_shipment_date,a.fulfillment_date)) BETWEEN p_start_date AND p_end_date
3677 -- AND a.flow_status_code IN ('SHIPPED','CLOSED')
3678 AND a.booked_flag = 'Y'
3679 AND a.cancelled_flag = 'N'
3680 AND a.line_category_code <> 'RETURN'
3681 AND a.inventory_item_id = p_product_id;
3682
3683 l_order_line_tbl t_order_line_tbl;
3684 l_batch_size NUMBER := 1000;
3685
3686 l_offer_type VARCHAR2(30);
3687 l_tier_level VARCHAR2(30);
3688 l_qp_list_header_id NUMBER;
3689 l_return_status VARCHAR2(1);
3690 l_msg_count NUMBER;
3691 l_msg_data VARCHAR2(2000);
3692 l_customer_qualified VARCHAR2(1);
3693 l_product_qualified VARCHAR2(1);
3694 l_tbl_index NUMBER := 1;
3695 l_api_name CONSTANT VARCHAR2(30) := 'offer_adj_new_product';
3696 l_custom_setup_id NUMBER;
3697
3698 BEGIN
3699 SAVEPOINT offer_adj_new_product;
3700
3701 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
3702 Fnd_Msg_Pub.initialize;
3703 END IF;
3704
3705 x_return_status := Fnd_Api.g_ret_sts_success;
3706
3707 OPEN c_offer_type;
3708 FETCH c_offer_type INTO l_offer_type,
3709 l_tier_level,
3710 l_qp_list_header_id,
3711 l_custom_setup_id;
3712 CLOSE c_offer_type;
3713
3714 OPEN c_order_line;
3715
3716 LOOP
3717 --
3718 l_header_id_tbl.delete ;
3719 l_line_id_tbl.delete;
3720 l_invoice_to_org_id_tbl.delete;
3721 l_ship_to_org_id_tbl.delete;
3722 l_sold_to_org_id_tbl.delete;
3723
3724 FETCH c_order_line BULK COLLECT INTO l_header_id_tbl ,
3725 l_line_id_tbl,
3726 l_invoice_to_org_id_tbl,
3727 l_ship_to_org_id_tbl,
3728 l_sold_to_org_id_tbl
3729 LIMIT l_batch_size;
3730 --
3731
3732 IF l_line_id_tbl.FIRST IS NULL
3733 THEN
3734 --
3735 EXIT;
3736 --
3737 END IF;
3738
3739 FOR i IN l_line_id_tbl.FIRST .. l_line_id_tbl.LAST
3740 LOOP
3741 --
3742 l_customer_qualified := validate_customer(p_invoice_to_org_id => l_invoice_to_org_id_tbl(i)
3743 ,p_ship_to_org_id => l_ship_to_org_id_tbl(i)
3744 ,p_sold_to_org_id => l_sold_to_org_id_tbl(i)
3745 ,p_qp_list_header_id => l_qp_list_header_id);
3746
3747 IF l_customer_qualified = 'Y'
3748 THEN
3749 --
3750 x_order_line_tbl(l_tbl_index).order_header_id := l_header_id_tbl(i);
3751 x_order_line_tbl(l_tbl_index).order_line_id := l_line_id_tbl(i);
3752 l_tbl_index := l_tbl_index + 1;
3753 --
3754 END IF;
3755 END LOOP; -- l_order_line_detail1
3756 --
3757 EXIT WHEN c_order_line%NOTFOUND;
3758 --
3759 END LOOP;
3760
3761 EXCEPTION
3762 WHEN Fnd_Api.G_EXC_ERROR THEN
3763 x_return_status := Fnd_Api.g_ret_sts_error;
3764 ROLLBACK TO offer_adj_new_product;
3765 Fnd_Msg_Pub.Count_AND_Get
3766 ( p_count => x_msg_count,
3767 p_data => x_msg_data,
3768 p_encoded => Fnd_Api.G_FALSE
3769 );
3770 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
3771 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
3772 ROLLBACK TO offer_adj_new_product;
3773 Fnd_Msg_Pub.Count_AND_Get
3774 ( p_count => x_msg_count,
3775 p_data => x_msg_data,
3776 p_encoded => Fnd_Api.G_FALSE
3777 );
3778 WHEN OTHERS THEN
3779 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
3780 ROLLBACK TO offer_adj_new_product;
3781 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR ) THEN
3782 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
3783 END IF;
3784 Fnd_Msg_Pub.Count_AND_Get
3785 ( p_count => x_msg_count,
3786 p_data => x_msg_data,
3787 p_encoded => Fnd_Api.G_FALSE
3788 );
3789
3790 END offer_adj_new_product;
3791
3792 END ozf_net_accrual_engine_pvt;