[Home] [Help]
PACKAGE BODY: APPS.OZF_ADJUSTMENT_EXT_PVT
Source
1 PACKAGE BODY ozf_adjustment_ext_pvt AS
2 /*$Header: ozfvadeb.pls 120.55.12010000.2 2008/08/01 06:40:19 nirprasa ship $*/
3
4 -----------------------------------------------------------
5 -- PACKAGE
6 -- OZF_Adjustment_EXT_PVT
7 --
8 -- PROCEDURES
9 -- adjust_backdated_offer
10 -- process_offer_product
11 -- HISTORY
12 -- 4/18/2002 Mumu Pande Create.
13 -- 10/17/2003 Ying Zhao fix bug 3197570 - BACKDATED ADJUSTMENTS FOR AMOUNT TYPE DICOUNT RULE
14 -- 12/14/2003 kdass changed table name from ams_temp_eligibility to ozf_temp_eligibility
15 -- 02/09/2004 yzhao fix bug MASS1R1011510:REOP:VOLUME OFFER DISCOUNT LEVL NOT CHANGING EVEN AFTER REACHG VOL
16 -- offer notes object should remain in AMS_OFFR, not OZF_OFFR
17 -- 07/08/2004 kdass changed the dynamic cursors in perform_adjustment to static cursors
18 -- 07/19/2004 kdass fix for 11.5.9 bug 3742174
19 -- 16/11/2004 Ribha Fix for bug 4013141 - Volume offer adjustment should get applied only when there is a tier-change.
20 -- 17/11/2004 Ribha Fix for bug 4015372 - Backdated adjustments should not get closed if not applied.
21 -- 01/05/2005 kdass fix for 11.5.9 bug 4033558 - handle volume offer adjustments for RMA order
22 -- 01/31/2005 kdass fix for 11.5.10 bug 4129759 - handle backdated adjustments for multi-tier discounts
23 -- 05/05/2005 Ribha fix for bug 4309014
24 -- 05/11/2005 Ribha fix for bug 4357772
25 -- 05/11/2005 kdass fix for 11.5.10 bug 4362575 - for all types of volume offers - offinvoice or accrual,
26 -- consider list price instead of selling price
27 -- 08/16/2005 feliu fix backdated adjustment for third party accrual.
28 -- Third party accrual support following offers:
29 -- Accrual, off-invoice, trade deal.
30 -- 12/09/2005 kdass fix for bug 4872799
31 -- 02/28/2006 kdass fixed bug 5059735
32 -- 03/31/2006 kdass fixed bug 5101720
33 -- 05/05/2006 kdass fixed bugs 5205721, 5198547
34 -- 06/21/2006 kdass fixed bug 5337761
35 -- 07/31/2006 kpatro fixed bug 5375224 for SQL ID# 19125146
36 -- 08/04/2006 kdass fixed bug 5446622
37 -- 08/24/2006 kdass fixed bug 5485172
38 -- 09/11/2006 kdass fixed bug 5497876
39 -- 12/04/2006 feliu fixed bug 5675871,5671169,and 5689866
40 -- 02/24/2007 kdass fixed bug 5610124 - retroactive adjustments for volume offer before offer start date
41 -- 04/04/2007 nirprasa fix for bug 5944862
42 -- 04/13/2007 nirprasa fixed bug 5975203
43 -- 04/13/2007 nirprasa fixed bug 5767748
44 -- 04/13/2007 nirprasa fixed bug 5979971
45 -- 05/11/2007 nirprasa fixed bug 6021635 - added volume_offer_util_adjustment for utilized amount and
46 -- changed adjustment_volume_retro for booked orders.
47 -- 05/21/2007 kdass fixed bug 6059036
48 -- 05/28/2007 nirprasa fixed bug 6077042
49 -- 06/27/2007 nirprasa fixed bug 6021538
50 -- 08/16/2007 nirprasa fixed bug 6345305
51 -- 08/16/2007 nirprasa fixed bug 6369218
52 -- 04/21/2008 psomyaju Bugfix 6278466 - FP:11510-R12 6051298 - FUNDS EARNED NOT RECOGNISED AS ELIGBLE FOR
53 -- 08/01/2008 nirprasa fixed bug 7030415
54 ------------------------------------------------------------
55
56 g_pkg_name CONSTANT VARCHAR2 (30) := 'OZF_Adjustment_Ext_PVT';
57 g_recal_flag CONSTANT VARCHAR2(1) := NVL(fnd_profile.value('OZF_BUDGET_ADJ_ALLOW_RECAL'),'N');
58 g_order_gl_phase CONSTANT VARCHAR2 (15) :=NVL(fnd_profile.VALUE ('OZF_ORDER_GLPOST_PHASE'), 'SHIPPED');
59 g_debug_flag VARCHAR2 (1) := 'N';
60 G_DEBUG BOOLEAN := FND_MSG_PUB.check_msg_level(FND_MSG_PUB.g_msg_lvl_debug_high);
61 g_bulk_limit CONSTANT NUMBER := 5000;
62
63 TYPE amountTbl IS TABLE OF ozf_funds_utilized_all_b.amount%TYPE;
64 TYPE glDateTbl IS TABLE OF ozf_funds_utilized_all_b.gl_date%TYPE;
65 TYPE objectTypeTbl IS TABLE OF ozf_funds_utilized_all_b.object_type%TYPE;
66 TYPE objectIdTbl IS TABLE OF ozf_funds_utilized_all_b.object_id%TYPE;
67 TYPE priceAdjustmentIDTbl IS TABLE OF ozf_funds_utilized_all_b.price_adjustment_id%TYPE;
68 TYPE glPostedFlagTbl IS TABLE OF ozf_funds_utilized_all_b.gl_posted_flag%TYPE;
69 TYPE orderLineIdTbl IS TABLE OF ozf_funds_utilized_all_b.order_line_id%TYPE;
70 TYPE utilizationIdTbl IS TABLE OF ozf_funds_utilized_all_b.utilization_id%TYPE; --Added for bug 7030415
71
72 TYPE order_line_rec_type IS RECORD(order_header_id NUMBER
73 ,order_line_id NUMBER
74 ,inventory_item_id NUMBER
75 ,unit_list_price NUMBER
76 ,quantity NUMBER
77 ,transactional_curr_code oe_order_headers_all.transactional_curr_code%TYPE
78 ,line_category_code oe_order_lines_all.line_category_code%TYPE
79 ,reference_line_id NUMBER
80 ,order_number NUMBER
81 ,group_nos VARCHAR2(256)
82 );
83
84 TYPE order_line_tbl_type IS TABLE OF order_line_rec_type INDEX BY BINARY_INTEGER;
85 TYPE offer_id_tbl IS TABLE OF NUMBER index by binary_integer;
86 TYPE product_attr_val_cursor_type is ref cursor;
87 g_offer_id_tbl offer_id_tbl;
88
89
90 -------------------------------------------------------------------
91 -- PROCEDURE
92 -- process_offer_product
93 -- PURPOSE
94 --
95 -- PARAMETERS
96 -- p_offer_adjustment_id IN NUMBER
97 -- History
98 -- 4/18/2002 Mumu Pande Create.
99 ----------------------------------------------------------------
100 PROCEDURE process_offer_product (
101 p_offer_adjustment_id IN NUMBER,
102 x_return_status OUT NOCOPY VARCHAR2
103 );
104
105 ---------------------------------------------------------------------
106 -- PROCEDURE
107 -- perform_adjustment
108 --
109 -- PURPOSE
110 --
111 -- PARAMETERS
112 -- p_from_date IN DATE
113 -- p_to_Date IN DATE
114 -- p_qp_list_header_id IN NUMBER
115 -- NOTES
116 -- HISTORY
117 -- 4/18/2002 Mumu Pande Create.
118 ----------------------------------------------------------------------
119 PROCEDURE perform_adjustment (
120 p_from_date IN DATE,
121 p_to_date IN DATE,
122 p_qp_list_header_id IN NUMBER,
123 p_offer_adjustment_id IN NUMBER,
124 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
125 p_commit IN VARCHAR2 := fnd_api.g_false,
126 x_return_status OUT NOCOPY VARCHAR2,
127 x_msg_count OUT NOCOPY NUMBER,
128 x_msg_data OUT NOCOPY VARCHAR2
129 );
130
131
132 ------------------------------------------------------------------------------
133 -- Procedure Name
134 -- write_con_log
135 -- Purpose
136 -- to write some debug message in the log file
137 -- History
138 -- 7/22/2002 mpande Created
139 -- 10/21/2002 mpande Changed for GSCC warnings
140 ------------------------------------------------------------------------------
141 PROCEDURE write_conc_log ( p_text IN VARCHAR2)
142 IS
143 BEGIN
144 IF g_debug_flag = 'Y' THEN
145 ozf_utility_pvt.write_conc_log (p_text);
146 --ozf_utility_pvt.debug_message(p_text);
147 END IF;
148 END;
149
150 ---------------------------------------------------------------------
151 -- PROCEDURE
152 -- get_orders
153 -- PURPOSE
154 -- returns qualified orders (copy of ozf_net_accrual_engine_pvt.offer_adj_new_product)
155 -- HISTORY
156 -- 12/30/2005 kdass Created
157 ----------------------------------------------------------------------
158 PROCEDURE get_orders(
159 p_api_version IN NUMBER
160 ,p_init_msg_list IN VARCHAR2
161 ,p_commit IN VARCHAR2
162 ,x_return_status OUT NOCOPY VARCHAR2
163 ,x_msg_count OUT NOCOPY NUMBER
164 ,x_msg_data OUT NOCOPY VARCHAR2
165 ,p_list_header_id IN NUMBER
166 ,p_list_line_id IN VARCHAR2
167 ,p_start_date IN DATE
168 ,p_end_date IN DATE
169 ,x_order_line_tbl OUT NOCOPY order_line_tbl_type)
170 IS
171
172 --kdass 05-MAY-2006 bug 5198547 - split cursor c_order_line into 2 for using hints suggested by perf team
173 CURSOR c_order_line IS
174 SELECT /*+ leading(temp) use_nl(temp line header) */
175 line.header_id, line.line_id, line.inventory_item_id, line.unit_list_price,
176 NVL(line.shipped_quantity, NVL(line.ordered_quantity, 0)) quantity,
177 header.transactional_curr_code, line.invoice_to_org_id,
178 line.sold_to_org_id, line.ship_to_org_id,line.line_category_code, line.reference_line_id,
179 header.order_number
180 FROM oe_order_lines_all line, oe_order_headers_all header,
181 (SELECT DISTINCT eligibility_id FROM ozf_temp_eligibility) temp
182 WHERE trunc(NVL(line.pricing_date, NVL(line.actual_shipment_date, line.fulfillment_date)))
183 BETWEEN p_start_date AND p_end_date
184 AND line.booked_flag = 'Y'
185 AND line.cancelled_flag = 'N'
186 --AND line.line_category_code <> 'RETURN'
187 AND line.inventory_item_id = temp.eligibility_id
188 AND line.header_id = header.header_id;
189
190 CURSOR c_order_line1 IS
191 SELECT /*+ parallel(line) */
192 line.header_id, line.line_id, line.inventory_item_id, line.unit_list_price,
193 NVL(line.shipped_quantity, NVL(line.ordered_quantity, 0)) quantity,
194 header.transactional_curr_code, line.invoice_to_org_id,
195 line.sold_to_org_id, line.ship_to_org_id,line.line_category_code, line.reference_line_id,
196 header.order_number
197 FROM oe_order_lines_all line, oe_order_headers_all header,
198 (SELECT DISTINCT eligibility_id FROM ozf_temp_eligibility) temp
199 WHERE trunc(NVL(line.pricing_date, NVL(line.actual_shipment_date, line.fulfillment_date)))
200 BETWEEN p_start_date AND p_end_date
201 AND line.booked_flag = 'Y'
202 AND line.cancelled_flag = 'N'
203 --AND line.line_category_code <> 'RETURN'
204 AND line.inventory_item_id = temp.eligibility_id
205 AND line.header_id = header.header_id;
206
207 CURSOR c_count_temp IS
208 SELECT COUNT(DISTINCT eligibility_id)
209 FROM ozf_temp_eligibility;
210
211 -- Segment and buying group has no acct info. use party_id for validation
212 CURSOR c_party_id(p_sold_to_org_id IN NUMBER) IS
213 SELECT party_id
214 FROM hz_cust_accounts
215 WHERE cust_account_id = p_sold_to_org_id;
216
217 CURSOR c_customer_qualified(p_invoice_to_org_id IN NUMBER, p_ship_to_org_id IN NUMBER, p_party_id NUMBER) IS
218 SELECT 'Y', object_type, qp_qualifier_group
219 FROM ozf_activity_customers
220 WHERE (
221 (site_use_id = p_invoice_to_org_id AND site_use_code = 'BILL_TO') OR
222 (site_use_id = p_ship_to_org_id AND site_use_code = 'SHIP_TO') OR
223 (party_id = p_party_id AND site_use_code IS NULL) OR
224 (party_id = -1)
225 )
226 AND object_class = 'OFFR'
227 AND object_id = p_list_header_id
228 AND ROWNUM = 1;
229
230 CURSOR c_cust_acct_qualified(p_sold_to_org_id IN NUMBER, p_party_id NUMBER) IS
231 SELECT 'Y', object_type, qp_qualifier_group
232 FROM ozf_activity_customers
233 WHERE (
234 (cust_account_id = p_sold_to_org_id) OR
235 (party_id = p_party_id AND site_use_code IS NULL) OR
236 (party_id = -1)
237 )
238 AND object_class = 'OFFR'
239 AND object_id = p_list_header_id
240 AND ROWNUM = 1;
241
242 TYPE numberTbl IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
243 TYPE orderCurrTbl IS TABLE OF oe_order_headers_all.transactional_curr_code%TYPE;
244 TYPE lineCatCodeTbl IS TABLE OF oe_order_lines_all.line_category_code%TYPE;
245 TYPE groupNosTbl IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER;
246
247 l_headerIdTbl numberTbl;
248 l_lineIdTbl numberTbl;
249 l_inventoryItemIdTbl numberTbl;
250 l_unitListPriceTbl numberTbl;
251 l_quantityTbl numberTbl;
252 l_orderCurrTbl orderCurrTbl;
253 l_invoiceToOrgIdTbl numberTbl;
254 l_soldToOrgIdTbl numberTbl;
255 l_shipToOrgIdTbl numberTbl;
256 l_lineCatCodeTbl lineCatCodeTbl;
257 l_refLineIdTbl numberTbl;
258 l_orderNumberTbl numberTbl;
259 l_group_nos groupNosTbl;
260
261 l_party_id NUMBER;
262 l_customer_qualified VARCHAR2(1);
263 l_tbl_index NUMBER := 1;
264 l_api_name CONSTANT VARCHAR2(30) := 'get_orders';
265
266 l_stmt_denorm VARCHAR2(32000) := NULL;
267 l_denorm_csr NUMBER;
268 l_ignore NUMBER;
269 l_product_stmt VARCHAR2(32000) := NULL;
270 l_count_temp NUMBER;
271 l_object_type VARCHAR2(20);
272 l_group NUMBER;
273 l_group_string VARCHAR2(256);
274
275 BEGIN
276
277 IF Fnd_Api.to_boolean(p_init_msg_list) THEN
278 Fnd_Msg_Pub.initialize;
279 END IF;
280
281 x_return_status := Fnd_Api.g_ret_sts_success;
282
283 --kdass 28-FEB-2006 fixed bug 5059735 - denorm offer's product eligibility to handle all types of product levels
284 EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
285
286 FND_DSQL.init;
287 FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, eligibility_id) ');
288 FND_DSQL.add_text('(SELECT ''OFFR'', product_id ' );
289 FND_DSQL.add_text(' FROM ( ');
290
291 /*kdass 05-MAY-2006 bug 5205721 - use refresh_products() as it considers excluded items
292 l_temp_sql := ozf_offr_elig_prod_denorm_pvt.get_sql(p_context => 'ITEM'
293 ,p_attribute => p_product_attr
294 ,p_attr_value_from => p_product
295 ,p_attr_value_to => NULL
296 ,p_comparison => NULL
297 ,p_type => 'PROD'
298 );
299 */
300
301 --kdass 21-JUN-2006 bug 5337761 - added exception handling code
302 BEGIN
303 SAVEPOINT refresh_prod;
304
305 ozf_offr_elig_prod_denorm_pvt.refresh_products(p_api_version => p_api_version
306 ,p_init_msg_list => p_init_msg_list
307 ,p_commit => p_commit
308 ,p_list_header_id => p_list_header_id
309 ,p_calling_from_den => 'N'
310 ,x_return_status => x_return_status
311 ,x_msg_count => x_msg_count
312 ,x_msg_data => x_msg_data
313 ,x_product_stmt => l_product_stmt
314 ,p_lline_id => p_list_line_id
315 );
316
317 FND_DSQL.add_text('))');
318
319 write_conc_log ('l_product_stmt: ' || l_product_stmt);
320
321 l_denorm_csr := DBMS_SQL.open_cursor;
322 FND_DSQL.set_cursor(l_denorm_csr);
323 l_stmt_denorm := FND_DSQL.get_text(FALSE);
324 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
325 FND_DSQL.do_binds;
326 l_ignore := DBMS_SQL.execute(l_denorm_csr);
327
328 EXCEPTION
329 WHEN OTHERS THEN
330 ROLLBACK TO refresh_prod;
331 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
332 write_conc_log ('unexpected exception in refresh_products');
333 END;
334
335 IF x_return_status <> fnd_api.g_ret_sts_success THEN
336 RETURN;
337 END IF;
338
339 l_count_temp := 0;
340
341 OPEN c_count_temp;
342 FETCH c_count_temp INTO l_count_temp;
343 CLOSE c_count_temp;
344
345 IF l_count_temp < 6 THEN
346 OPEN c_order_line;
347 ELSE
348 OPEN c_order_line1;
349 END IF;
350
351 LOOP
352
353 IF l_count_temp < 6 THEN
354 FETCH c_order_line BULK COLLECT INTO l_headerIdTbl, l_lineIdTbl, l_inventoryItemIdTbl,
355 l_unitListPriceTbl, l_quantityTbl, l_orderCurrTbl,
356 l_invoiceToOrgIdTbl, l_soldToOrgIdTbl, l_shipToOrgIdTbl,
357 l_lineCatCodeTbl, l_refLineIdTbl, l_orderNumberTbl
358 LIMIT g_bulk_limit;
359 ELSE
360 FETCH c_order_line1 BULK COLLECT INTO l_headerIdTbl, l_lineIdTbl, l_inventoryItemIdTbl,
361 l_unitListPriceTbl, l_quantityTbl, l_orderCurrTbl,
362 l_invoiceToOrgIdTbl, l_soldToOrgIdTbl, l_shipToOrgIdTbl,
363 l_lineCatCodeTbl, l_refLineIdTbl, l_orderNumberTbl
364 LIMIT g_bulk_limit;
365 END IF;
366
367 IF l_lineIdTbl.FIRST IS NULL THEN
368 EXIT;
369 END IF;
370
371 FOR i IN l_lineIdTbl.FIRST .. l_lineIdTbl.LAST
372 LOOP
373 OPEN c_party_id (l_soldToOrgIdTbl(i));
374 FETCH c_party_id INTO l_party_id;
375 CLOSE c_party_id;
376
377 l_customer_qualified := 'N';
378
379 l_group := NULL;
380 l_group_string := NULL;
381
382 IF l_invoiceToOrgIdTbl(i) IS NULL AND l_shipToOrgIdTbl(i) IS NULL THEN
383
384 --kdass bug 5610124
385 OPEN c_cust_acct_qualified(l_soldToOrgIdTbl(i), l_party_id);
386 LOOP
387 FETCH c_cust_acct_qualified INTO l_customer_qualified, l_object_type, l_group;
388 EXIT WHEN c_cust_acct_qualified%NOTFOUND;
389
390 IF l_object_type = 'VOLUME_OFFER' AND l_group IS NOT NULL THEN
391 l_group_string := l_group_string || ',' || l_group;
392 END IF;
393
394 END LOOP;
395 CLOSE c_cust_acct_qualified;
396
397 ELSE
398
399 --kdass bug 5610124
400 OPEN c_customer_qualified(l_invoiceToOrgIdTbl(i), l_shipToOrgIdTbl(i), l_party_id);
401 LOOP
402 FETCH c_customer_qualified INTO l_customer_qualified, l_object_type, l_group;
403 EXIT WHEN c_customer_qualified%NOTFOUND;
404
405 IF l_object_type = 'VOLUME_OFFER' AND l_group IS NOT NULL THEN
406 l_group_string := l_group_string || ',' || l_group;
407 END IF;
408
409 END LOOP;
410 CLOSE c_customer_qualified;
411
412 END IF;
413
414 IF l_group_string IS NOT NULL THEN
415 l_group_nos(i) := substr(l_group_string,2); --remove first comma
416 END IF;
417
418 write_conc_log ('order_line_id: ' || l_lineIdTbl(i));
419 write_conc_log ('l_customer_qualified: ' || l_customer_qualified);
420
421 IF l_customer_qualified = 'Y' THEN
422 x_order_line_tbl(l_tbl_index).order_header_id := l_headerIdTbl(i);
423 x_order_line_tbl(l_tbl_index).order_line_id := l_lineIdTbl(i);
424 x_order_line_tbl(l_tbl_index).inventory_item_id := l_inventoryItemIdTbl(i);
425 x_order_line_tbl(l_tbl_index).unit_list_price := l_unitListPriceTbl(i);
426 x_order_line_tbl(l_tbl_index).quantity := l_quantityTbl(i);
427 x_order_line_tbl(l_tbl_index).transactional_curr_code := l_orderCurrTbl(i);
428 x_order_line_tbl(l_tbl_index).line_category_code := l_lineCatCodeTbl(i);
429 x_order_line_tbl(l_tbl_index).reference_line_id := l_refLineIdTbl(i);
430 x_order_line_tbl(l_tbl_index).order_number := l_orderNumberTbl(i);
431
432 IF l_group_string IS NOT NULL THEN
433 x_order_line_tbl(l_tbl_index).group_nos := l_group_nos(i);
434 END IF;
435
436 l_tbl_index := l_tbl_index + 1;
437 END IF;
438 END LOOP; --FOR i IN l_line_id_tbl.FIRST .. l_line_id_tbl.LAST
439
440 IF l_count_temp < 6 THEN
441 EXIT WHEN c_order_line%NOTFOUND;
442 ELSE
443 EXIT WHEN c_order_line1%NOTFOUND;
444 END IF;
445
446 END LOOP;
447
448 IF l_count_temp < 6 THEN
449 CLOSE c_order_line;
450 ELSE
451 CLOSE c_order_line1;
452 END IF;
453
454 END get_orders;
455
456 ---------------------------------------------------------------------
457 -- PROCEDURE
458 -- adjustment_net_accrual
459 -- PURPOSE
460 -- adjustment for new product and retroactive adjustment before offer start date
461 -- HISTORY
462 -- 4/22/2004 kdass Created
463 ----------------------------------------------------------------------
464 PROCEDURE adjustment_net_accrual (p_api_version IN NUMBER
465 ,p_offer_type IN VARCHAR2
466 ,p_original_discount IN NUMBER
467 ,p_modified_discount IN NUMBER
468 ,p_arithmetic_operator IN VARCHAR2
469 ,p_start_date IN DATE
470 ,p_end_date IN DATE
471 ,p_list_header_id IN NUMBER
472 ,p_list_line_id IN VARCHAR2
473 ,p_offer_adjustment_id IN NUMBER
474 ,p_type IN VARCHAR2
475 ,x_return_status IN OUT NOCOPY VARCHAR2
476 ,x_msg_count IN OUT NOCOPY NUMBER
477 ,x_msg_data IN OUT NOCOPY VARCHAR2
478 )
479 IS
480
481 CURSOR c_offer_info IS
482 SELECT nvl(transaction_currency_code,fund_request_curr_code) transaction_currency_code
483 , beneficiary_account_id,autopay_party_attr,autopay_party_id -- Added for bug 7030415, correct org_id in accrual records
484 FROM ozf_offers
485 WHERE qp_list_header_id = p_list_header_id;
486
487 CURSOR c_cust_number (p_header_id IN NUMBER) IS
488 SELECT cust.cust_account_id
489 FROM hz_cust_acct_sites_all acct_site,
490 hz_cust_site_uses_all site_use,
491 hz_cust_accounts cust,
492 oe_order_headers_all header
493 WHERE header.header_id = p_header_id
494 AND acct_site.cust_acct_site_id = site_use.cust_acct_site_id
495 AND acct_site.cust_account_id = cust.cust_account_id
496 AND site_use.site_use_id = header.invoice_to_org_id ;
497
498 CURSOR c_order_adjustment_amt (p_object_id IN NUMBER, p_order_line_id IN NUMBER, p_prod_id IN NUMBER) IS
499 SELECT SUM(amount)
500 FROM ozf_funds_utilized_all_b
501 WHERE plan_type = 'OFFR'
502 AND plan_id = p_list_header_id
503 AND object_type = 'ORDER'
504 AND object_id = p_object_id
505 AND order_line_id = p_order_line_id
506 AND product_level_type = 'PRODUCT'
507 AND product_id = p_prod_id
508 AND utilization_type NOT IN ('REQUEST', 'TRANSFER'); --kdass 29-MAR-2006 bug 5120491
509 --AND utilization_type = 'ADJUSTMENT';
510
511 CURSOR c_orig_order_adj_amt (p_order_line_id IN NUMBER) IS
512 SELECT SUM(amount)
513 FROM ozf_funds_utilized_all_b
514 WHERE plan_type = 'OFFR'
515 AND plan_id = p_list_header_id
516 AND order_line_id = p_order_line_id
517 AND utilization_type NOT IN ('REQUEST', 'TRANSFER');
518
519 CURSOR c_order_line (p_order_line_id IN NUMBER) IS
520 SELECT NVL(invoiced_quantity, NVL(shipped_quantity, 0)) quantity,
521 ship_to_org_id, invoice_to_org_id
522 FROM oe_order_lines_all
523 WHERE line_id = p_order_line_id;
524
525 l_order_org_id NUMBER;
526 l_exchange_rate_type VARCHAR2(30) := FND_API.G_MISS_CHAR;
527 l_autopay_party_id NUMBER;
528 l_autopay_party_attr VARCHAR2(30);
529 l_org_id NUMBER; -- site's lorg id
530
531 -- Added for bug 7030415. get order's org_id
532 CURSOR c_order_org_id (p_line_id IN NUMBER) IS
533 SELECT header.org_id
534 FROM oe_order_lines_all line, oe_order_headers_all header
535 WHERE line_id = p_line_id
536 AND line.header_id = header.header_id;
537
538 -- get conversion type
539 CURSOR c_get_conversion_type( p_org_id IN NUMBER) IS
540 SELECT exchange_rate_type
541 FROM ozf_sys_parameters_all
542 WHERE org_id = p_org_id;
543
544 -- get sites org id type
545 CURSOR c_org_id (p_site_use_id IN NUMBER) IS
546 SELECT org_id
547 FROM hz_cust_site_uses_all
548 WHERE site_use_id = p_site_use_id;
549
550 CURSOR c_offer_type (p_offer_id IN NUMBER) IS
551 SELECT autopay_party_attr,autopay_party_id
552 FROM ozf_offers
553 WHERE qp_list_header_id = p_offer_id;
554
555 l_offer_info c_offer_info%ROWTYPE;
556
557 l_util_amount NUMBER;
558 l_rate NUMBER;
559 l_act_budget_id NUMBER;
560 l_total_price NUMBER;
561 l_cust_number NUMBER;
562 l_qp_list_header_id NUMBER;
563 l_error_location NUMBER;
564 l_line_ctr NUMBER := 1;
565 l_adj_amount NUMBER := 0;
566
567 l_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type;
568 l_act_util_rec ozf_actbudgets_pvt.act_util_rec_type;
569 l_modifier_list_rec ozf_offer_pvt.modifier_list_rec_type;
570 l_modifier_line_tbl ozf_offer_pvt.modifier_line_tbl_type;
571 l_order_line_tbl order_line_tbl_type;
572
573 l_api_name VARCHAR2(50) := 'adjustment_net_accrual';
574 l_full_name CONSTANT VARCHAR2(90) := g_pkg_name || '.' || l_api_name;
575 l_justification VARCHAR2(50);
576 l_conv_util_amount NUMBER;
577 l_orig_util_amount NUMBER;
578 l_orig_order_qty NUMBER;
579 l_ship_to_org_id NUMBER;
580 l_invoice_to_org_id NUMBER;
581
582 BEGIN
583
584 OPEN c_offer_info;
585 FETCH c_offer_info INTO l_offer_info;
586 CLOSE c_offer_info;
587
588 write_conc_log ('p_type: ' || p_type);
589 write_conc_log ('offer_id: ' || p_list_header_id);
590 write_conc_log ('p_start_date: ' || p_start_date);
591 write_conc_log ('p_end_date: ' || p_end_date);
592 write_conc_log ('p_offer_adjustment_id: ' || p_offer_adjustment_id);
593 write_conc_log ('p_list_line_id: ' || p_list_line_id);
594
595 --get the qualified orders
596 get_orders(p_api_version => p_api_version
597 ,p_init_msg_list => FND_API.G_FALSE
598 ,p_commit => FND_API.G_FALSE
599 ,x_return_status => x_return_status
600 ,x_msg_count => x_msg_count
601 ,x_msg_data => x_msg_data
602 ,p_list_header_id => p_list_header_id
603 /*kdass 05-MAY-2006 bug 5205721
604 ,p_product => p_product
605 ,p_product_attr => p_product_attr
606 */
607 ,p_list_line_id => p_list_line_id
608 ,p_start_date => p_start_date
609 ,p_end_date => p_end_date
610 ,x_order_line_tbl => l_order_line_tbl
611 );
612
613 write_conc_log ('x_return_status: ' || x_return_status);
614 write_conc_log ('number of orders: ' || l_order_line_tbl.count);
615
616 /*kdass 04-AUG-2006 fixed bug 5446622
617 IF x_return_status = fnd_api.g_ret_sts_error THEN
618 RAISE fnd_api.g_exc_error;
619 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
620 RAISE fnd_api.g_exc_unexpected_error;
621 END IF;
622 */
623 IF x_return_status <> fnd_api.g_ret_sts_success THEN
624 RETURN;
625 END IF;
626
627 IF p_type = 'product' THEN
628 write_conc_log ('adjustment for new product');
629 l_justification := 'Offer adjustment for new product';
630
631 /*removed code for future dated adjustments since offers team will be taking care of this.
632 original code in version 120.19
633 */
634 ELSE
635 write_conc_log (l_full_name || ' adjustment before offer start date');
636 l_justification := 'Offer adjustment before offer start date';
637 END IF;
638
639 IF l_order_line_tbl.count > 0 THEN
640
641 l_act_budgets_rec.act_budget_used_by_id := p_list_header_id;
642 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
643 l_act_budgets_rec.budget_source_type := 'OFFR';
644 l_act_budgets_rec.budget_source_id := p_list_header_id;
645 l_act_budgets_rec.request_currency := l_offer_info.transaction_currency_code;
646 l_act_budgets_rec.request_date := SYSDATE;
647 l_act_budgets_rec.status_code := 'APPROVED';
648 l_act_budgets_rec.user_status_id := ozf_Utility_Pvt.get_default_user_status (
649 'OZF_BUDGETSOURCE_STATUS', l_act_budgets_rec.status_code);
650 l_act_budgets_rec.approved_in_currency := l_offer_info.transaction_currency_code;
651 l_act_budgets_rec.approval_date := SYSDATE;
652 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id (fnd_global.user_id);
653 l_act_budgets_rec.justification := l_justification;
654 l_act_budgets_rec.transfer_type := 'UTILIZED';
655
656 l_act_util_rec.utilization_type :='ADJUSTMENT';
657 l_act_util_rec.product_level_type := 'PRODUCT';
658 l_act_util_rec.adjustment_date := SYSDATE;
659 l_act_util_rec.cust_account_id := l_offer_info.beneficiary_account_id;
660
661 FOR j IN l_order_line_tbl.first .. l_order_line_tbl.last
662 LOOP
663
664 write_conc_log('order header id: ' || l_order_line_tbl(j).order_header_id);
665 write_conc_log('order line id: ' || l_order_line_tbl(j).order_line_id);
666 write_conc_log('inventory item id: ' || l_order_line_tbl(j).inventory_item_id);
667
668 l_act_util_rec.product_id := l_order_line_tbl(j).inventory_item_id;
669 l_act_util_rec.object_type :='ORDER';
670 l_act_util_rec.object_id := l_order_line_tbl(j).order_header_id;
671 l_act_util_rec.order_line_id := l_order_line_tbl(j).order_line_id;
672
673 OPEN c_cust_number (l_order_line_tbl(j).order_header_id);
674 FETCH c_cust_number INTO l_cust_number;
675 CLOSE c_cust_number;
676
677 l_act_util_rec.billto_cust_account_id := l_cust_number;
678
679 IF l_offer_info.beneficiary_account_id IS NULL THEN
680 l_act_util_rec.cust_account_id := l_cust_number;
681 END IF;
682
683 write_conc_log ('billto_cust_account_id: ' || l_act_util_rec.billto_cust_account_id);
684 write_conc_log ('cust_account_id: ' || l_act_util_rec.cust_account_id);
685 write_conc_log ('unit_list_price: ' || l_order_line_tbl(j).unit_list_price);
686 write_conc_log ('quantity: ' || l_order_line_tbl(j).quantity);
687 write_conc_log ('p_modified_discount: ' || p_modified_discount);
688 write_conc_log ('p_original_discount: ' || p_original_discount);
689 write_conc_log ('p_arithmetic_operator: ' || p_arithmetic_operator);
690
691 /*
692 If you enter 5 for discount, then the following would result for the various discount types
693 Amount = $5.00 off the price per unit
694 Percent = 5% off the price per unit
695 New Price = the new price per unit is $5.00
696 Lumpsum = a flat $5.00 off an order for that product regardless of quantity
697 */
698 l_total_price := l_order_line_tbl(j).unit_list_price * l_order_line_tbl(j).quantity;
699
700 -- 7030415 , get the order's org_id to get the exchange rate.
701 OPEN c_order_org_id(l_order_line_tbl(j).order_line_id);
702 FETCH c_order_org_id INTO l_order_org_id;
703 CLOSE c_order_org_id;
704
705 OPEN c_offer_type(p_list_header_id);
706 FETCH c_offer_type INTO l_autopay_party_attr,l_autopay_party_id;
707 CLOSE c_offer_type;
708
709 write_conc_log ('l_order_org_id: ' || l_order_org_id);
710 l_act_util_rec.org_id := l_order_org_id;
711
712 IF l_act_util_rec.cust_account_id IS NULL THEN
713 IF l_offer_info.beneficiary_account_id IS NOT NULL THEN
714 IF l_autopay_party_attr <> 'CUSTOMER' AND l_autopay_party_attr IS NOT NULL THEN
715 --Added c_org_id for bugfix 6278466
716 OPEN c_org_id (l_autopay_party_id);
717 FETCH c_org_id INTO l_org_id;
718 CLOSE c_org_id;
719 l_act_util_rec.org_id := l_org_id;
720 END IF;
721 END IF;
722 END IF;
723
724 IF p_arithmetic_operator = 'AMT' THEN
725 l_util_amount := p_modified_discount * l_order_line_tbl(j).quantity;
726 ELSIF p_arithmetic_operator = '%' THEN
727 l_util_amount := p_modified_discount * l_total_price / 100;
728
729 --kdass 31-MAR-2006 bug 5101720 convert from order currency to offer currency
730 IF l_offer_info.transaction_currency_code <> l_order_line_tbl(j).transactional_curr_code THEN
731
732 ozf_utility_pvt.write_conc_log('order curr: ' || l_order_line_tbl(j).transactional_curr_code);
733 ozf_utility_pvt.write_conc_log('offer curr: ' || l_offer_info.transaction_currency_code);
734 ozf_utility_pvt.write_conc_log('l_util_amount: ' || l_util_amount);
735
736
737 OPEN c_get_conversion_type(l_act_util_rec.org_id);
738 FETCH c_get_conversion_type INTO l_exchange_rate_type;
739 CLOSE c_get_conversion_type;
740
741
742 ozf_utility_pvt.convert_currency (x_return_status => x_return_status
743 ,p_from_currency => l_order_line_tbl(j).transactional_curr_code
744 ,p_to_currency => l_offer_info.transaction_currency_code
745 ,p_conv_type => l_exchange_rate_type
746 ,p_from_amount => l_util_amount
747 ,x_to_amount => l_conv_util_amount
748 ,x_rate => l_rate
749 );
750
751 ozf_utility_pvt.write_conc_log('x_return_status: ' || x_return_status);
752 IF x_return_status <> fnd_api.g_ret_sts_success THEN
753 RETURN;
754 END IF;
755
756 l_util_amount := l_conv_util_amount;
757
758 write_conc_log ('util amt after currency conversion: ' || l_util_amount);
759
760 END IF;
761 ELSIF p_arithmetic_operator = 'NEWPRICE' THEN
762 l_util_amount := (l_order_line_tbl(j).unit_list_price - p_modified_discount) * l_order_line_tbl(j).quantity;
763 ELSIF p_arithmetic_operator = 'LUMPSUM' THEN
764 l_util_amount := p_modified_discount;
765 END IF;
766
767 write_conc_log ('adjustment amount: ' || l_util_amount);
768
769 l_ship_to_org_id := NULL;
770 l_invoice_to_org_id := NULL;
771
772 -- handle RMA order to fix bug 5147399.
773 IF l_order_line_tbl(j).line_category_code ='RETURN' THEN
774 IF l_order_line_tbl(j).reference_line_id is NOT NULL THEN
775 OPEN c_orig_order_adj_amt (l_order_line_tbl(j).reference_line_id);
776 FETCH c_orig_order_adj_amt INTO l_orig_util_amount;
777 CLOSE c_orig_order_adj_amt;
778
779 --kdass 24-AUG-2006 fix for bug 5485172
780 OPEN c_order_line (l_order_line_tbl(j).reference_line_id);
781 FETCH c_order_line INTO l_orig_order_qty, l_ship_to_org_id, l_invoice_to_org_id;
782 CLOSE c_order_line;
783
784 write_conc_log ('l_orig_util_amount: ' || l_orig_util_amount);
785 write_conc_log ('l_orig_order_qty: ' || l_orig_order_qty);
786
787 IF l_orig_order_qty = 0 THEN
788 write_conc_log ('l_orig_order_qty is 0, exit loop');
789 GOTO l_endoforderloop;
790 END IF;
791
792 --calculate utilization amount in proportion of the number of items returned
793 l_util_amount := l_orig_util_amount / l_orig_order_qty * l_order_line_tbl(j).quantity;
794
795 write_conc_log ('l_util_amount: ' || l_util_amount);
796
797 IF l_util_amount > l_orig_util_amount THEN
798 l_util_amount := l_orig_util_amount;
799 write_conc_log ('greater than orig amount - l_util_amount: ' || l_util_amount);
800 END IF;
801
802 END IF;
803
804 l_util_amount := - l_util_amount;
805 write_conc_log ('adjustment amount for RMA: ' || l_util_amount);
806
807 END IF; -- l_order_line_tbl(j).line_category_code ='RETURN'
808
809 IF l_ship_to_org_id IS NULL THEN
810 OPEN c_order_line (l_order_line_tbl(j).order_line_id);
811 FETCH c_order_line INTO l_orig_order_qty, l_ship_to_org_id, l_invoice_to_org_id;
812 CLOSE c_order_line;
813 END IF;
814
815 l_act_util_rec.ship_to_site_use_id := l_ship_to_org_id;
816 l_act_util_rec.bill_to_site_use_id := l_invoice_to_org_id;
817
818 --kdass 20-JUL-05 Bug 4489233 - gets the previous adjusted amount for the order line
819 OPEN c_order_adjustment_amt (l_order_line_tbl(j).order_header_id, l_order_line_tbl(j).order_line_id, l_order_line_tbl(j).inventory_item_id);
820 FETCH c_order_adjustment_amt INTO l_adj_amount;
821 CLOSE c_order_adjustment_amt;
822
823 l_util_amount := NVL(l_util_amount,0) - NVL(l_adj_amount,0);
824
825 write_conc_log ('remaining adjustment amount: ' || l_util_amount);
826
827 IF l_util_amount > 0 THEN
828 l_act_util_rec.adjustment_type :='STANDARD'; -- Seeded Data for Backdated Positive Adj
829 l_act_util_rec.adjustment_type_id := -5; -- Seeded Data for Backdated Positive Adj
830 ELSE
831 l_act_util_rec.adjustment_type :='DECREASE_EARNED'; -- Seeded Data for Backdated Negative Adj
832 l_act_util_rec.adjustment_type_id := -4; -- Seeded Data for Backdated Negative Adj
833 END IF;
834
835 l_act_budgets_rec.request_amount := l_util_amount;
836 l_act_budgets_rec.approved_amount := l_util_amount;
837
838 IF l_util_amount <> 0 THEN
839
840 write_conc_log(l_full_name || ': ozf_fund_adjustment_pvt.process_act_budgets');
841
842 ozf_fund_adjustment_pvt.process_act_budgets(x_return_status => x_return_status
843 ,x_msg_count => x_msg_count
844 ,x_msg_data => x_msg_data
845 ,p_act_budgets_rec => l_act_budgets_rec
846 ,p_act_util_rec => l_act_util_rec
847 ,x_act_budget_id => l_act_budget_id
848 );
849
850 write_conc_log('process_act_budgets returns: ' || x_return_status);
851
852 IF x_return_status <> fnd_api.g_ret_sts_success THEN
853 RETURN;
854 END IF;
855
856 END IF;
857
858 <<l_endoforderloop>>
859 write_conc_log('adjustment_net_accrual returns: ' || x_return_status);
860
861 END LOOP;
862 END IF;
863
864 END adjustment_net_accrual;
865
866
867 ---------------------------------------------------------------------
868 -- PROCEDURE
869 -- adjustment_volume_retro
870 -- PURPOSE
871 -- adjustment for retroactive adjustment before offer start date for volume offer
872 -- HISTORY
873 -- 2/16/2007 kdass Created for bug 5610124
874 ----------------------------------------------------------------------
875 PROCEDURE adjustment_volume_retro(p_api_version IN NUMBER
876 ,p_start_date IN DATE
877 ,p_end_date IN DATE
878 ,p_list_header_id IN NUMBER
879 ,p_offer_adjustment_id IN NUMBER
880 ,x_return_status IN OUT NOCOPY VARCHAR2
881 ,x_msg_count IN OUT NOCOPY NUMBER
882 ,x_msg_data IN OUT NOCOPY VARCHAR2
883 )
884 IS
885
886 --query to retrieve list_line_id
887 CURSOR c_list_line (p_offer_id IN NUMBER, p_product_id IN VARCHAR2) IS
888 SELECT oq.list_line_id, op.product_attribute, op.product_attr_value
889 FROM ozf_offer_discount_products op, ozf_qp_discounts oq
890 WHERE (op.product_attr_value = p_product_id OR op.product_attr_value = 'ALL')
891 AND op.offer_id = p_offer_id
892 AND op.offer_discount_line_id = oq.offer_discount_line_id
893 AND rownum = 1;
894
895 CURSOR c_order_line_details (p_line_id IN NUMBER) IS
896 SELECT actual_shipment_date, shipped_quantity, flow_status_code, invoice_interface_status_code,
897 invoiced_quantity, sold_to_org_id, invoice_to_org_id, ship_to_org_id, shipping_quantity_uom,
898 order_quantity_uom, unit_selling_price, org_id, ordered_quantity
899 FROM oe_order_lines_all
900 WHERE line_id = p_line_id;
901
902 CURSOR c_invoice_date(p_line_id IN NUMBER, p_order_number IN VARCHAR2) IS
903 SELECT cust.trx_date -- transaction(invoice) date
904 FROM ra_customer_trx_all cust
905 , ra_customer_trx_lines_all cust_lines
906 WHERE cust.customer_trx_id = cust_lines.customer_trx_id
907 AND cust_lines.sales_order = p_order_number -- added condition for partial index for bug fix 3917556
908 AND cust_lines.interface_line_attribute6 = TO_CHAR(p_line_id);
909
910 CURSOR party_id_csr(p_cust_account_id IN NUMBER) IS
911 SELECT party_id
912 FROM hz_cust_accounts
913 WHERE cust_account_id = p_cust_account_id;
914
915 CURSOR party_site_id_csr(p_account_site_id IN NUMBER) IS
916 SELECT a.party_site_id
917 FROM hz_cust_acct_sites_all a,
918 hz_cust_site_uses_all b
919 WHERE b.site_use_id = p_account_site_id
920 AND b.cust_acct_site_id = a.cust_acct_site_id;
921
922 CURSOR sales_transation_csr(p_line_id IN NUMBER) IS
923 SELECT 1 FROM DUAL WHERE EXISTS
924 ( SELECT 1
925 FROM ozf_sales_transactions_all trx
926 WHERE trx.line_id = p_line_id
927 AND source_code = 'OM');
928
929 CURSOR c_adjustment_exists (p_list_header_id IN NUMBER, p_order_line_id IN NUMBER) IS
930 SELECT 1
931 FROM ozf_funds_utilized_all_b
932 WHERE plan_id = p_list_header_id
933 AND plan_type = 'OFFR'
934 AND order_line_id = p_order_line_id;
935
936 CURSOR c_offer_info (p_list_header_id IN NUMBER) IS
937 SELECT nvl(transaction_currency_code,fund_request_curr_code) transaction_currency_code
938 , beneficiary_account_id, offer_id
939 FROM ozf_offers
940 WHERE qp_list_header_id = p_list_header_id;
941
942 CURSOR c_cust_number (p_header_id IN NUMBER) IS
943 SELECT cust.cust_account_id
944 FROM hz_cust_acct_sites_all acct_site,
945 hz_cust_site_uses_all site_use,
946 hz_cust_accounts cust,
947 oe_order_headers_all header
948 WHERE header.header_id = p_header_id
949 AND acct_site.cust_acct_site_id = site_use.cust_acct_site_id
950 AND acct_site.cust_account_id = cust.cust_account_id
951 AND site_use.site_use_id = header.invoice_to_org_id ;
952
953 CURSOR c_apply_discount(p_offer_id IN NUMBER,p_product_id IN VARCHAR2) IS
954 SELECT NVL(apply_discount_flag,'N')
955 FROM ozf_offer_discount_products
956 WHERE offer_id = p_offer_id
957 AND product_attr_value = p_product_id;
958
959 CURSOR c_get_items_type(p_list_header_id number,p_inventory_item_id IN NUMBER) IS
960 select item_type, ITEMS_CATEGORY
961 from ozf_activity_products
962 where object_id = p_list_header_id
963 and item=p_inventory_item_id;
964
965
966 CURSOR c_get_cond_id_column(p_prod_attr varchar2) IS
967 select condition_id_column
968 from ozf_denorm_queries
969 where context='ITEM'
970 and attribute =p_prod_attr and rownum = 1;
971
972
973 CURSOR c_discount_header(p_discount_line_id IN NUMBER) IS
974 SELECT discount_type,volume_type
975 FROM ozf_offer_discount_lines
976 WHERE offer_discount_line_id = p_discount_line_id
977 AND tier_type = 'PBH';
978
979 CURSOR c_get_group(p_order_line_id IN NUMBER,p_list_header_id IN NUMBER) IS
980 SELECT group_no,pbh_line_id,include_volume_flag
981 FROM ozf_order_group_prod
982 WHERE order_line_id = p_order_line_id
983 AND qp_list_header_id = p_list_header_id;
984
985 CURSOR c_market_option(p_list_header_id IN NUMBER, p_group_id IN NUMBER) IS
986 SELECT opt.retroactive_flag
987 FROM ozf_offr_market_options opt
988 WHERE opt.GROUP_NUMBER= p_group_id
989 AND opt.qp_list_header_id = p_list_header_id;
990
991 CURSOR c_current_discount(p_volume IN NUMBER, p_parent_discount_id IN NUMBER) IS
992 SELECT discount
993 FROM ozf_offer_discount_lines
994 WHERE p_volume > volume_from
995 AND p_volume <= volume_to
996 AND parent_discount_line_id = p_parent_discount_id;
997
998 CURSOR c_get_tier_limits (p_parent_discount_id IN NUMBER) IS
999 SELECT MIN(volume_from),MAX(volume_to)
1000 FROM ozf_offer_discount_lines
1001 WHERE parent_discount_line_id = p_parent_discount_id;
1002
1003 CURSOR c_get_max_tier (p_max_volume_to IN NUMBER,p_parent_discount_id IN NUMBER) IS
1004 SELECT discount
1005 FROM ozf_offer_discount_lines
1006 WHERE volume_to =p_max_volume_to
1007 AND parent_discount_line_id = p_parent_discount_id;
1008
1009
1010 CURSOR c_order_adjustment_amt (p_object_id IN NUMBER, p_order_line_id IN NUMBER, p_prod_id IN NUMBER) IS
1011 SELECT SUM(amount)
1012 FROM ozf_funds_utilized_all_b
1013 WHERE plan_type = 'OFFR'
1014 AND plan_id = p_list_header_id
1015 AND object_type = 'ORDER'
1016 AND object_id = p_object_id
1017 AND order_line_id = p_order_line_id
1018 AND product_level_type = 'PRODUCT'
1019 AND product_id = p_prod_id
1020 AND utilization_type NOT IN ('REQUEST', 'TRANSFER');
1021
1022
1023
1024 CURSOR c_order_adj_amount ( p_prod_id IN NUMBER) IS
1025 SELECT SUM(amount)
1026 FROM ozf_funds_utilized_all_b
1027 WHERE plan_type = 'OFFR'
1028 AND plan_id = p_list_header_id
1029 AND object_type = 'ORDER'
1030 -- AND object_id = p_object_id
1031 AND product_level_type = 'PRODUCT'
1032 AND product_id = p_prod_id
1033 AND utilization_type NOT IN ('REQUEST', 'TRANSFER');
1034
1035 CURSOR c_prior_tiers(p_parent_discount_id IN NUMBER, p_volume IN NUMBER ) IS
1036 SELECT offer_discount_line_id ,volume_from ,volume_to, discount
1037 FROM ozf_offer_discount_lines
1038 WHERE parent_discount_line_id = p_parent_discount_id
1039 AND p_volume >= volume_from
1040 ORDER BY volume_from DESC;
1041
1042 CURSOR c_preset_tier(p_pbh_line_id IN NUMBER, p_qp_list_header_id IN NUMBER,p_group_id IN NUMBER) IS
1043 SELECT a.discount
1044 FROM ozf_offer_discount_lines a, ozf_market_preset_tiers b, ozf_offr_market_options c
1045 WHERE a.offer_discount_line_id = b.dis_offer_discount_id
1046 AND b.pbh_offer_discount_id = p_pbh_line_id
1047 AND b.offer_market_option_id = c.offer_market_option_id
1048 AND c.qp_list_header_id = p_qp_list_header_id
1049 AND c.group_number = p_group_id;
1050
1051
1052
1053
1054
1055 l_offer_info c_offer_info%ROWTYPE;
1056 l_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type;
1057 l_act_util_rec ozf_actbudgets_pvt.act_util_rec_type;
1058 l_cust_number NUMBER;
1059 l_order_line_tbl order_line_tbl_type;
1060 l_req_line_attrs_tbl qp_runtime_source.accum_req_line_attrs_tbl;
1061 l_index NUMBER := 1;
1062 l_dummy NUMBER;
1063 l_list_line_id NUMBER;
1064 l_order_header_id NUMBER;
1065 l_order_line_id NUMBER;
1066 l_string VARCHAR2(1024);
1067 l_first_pos NUMBER := 1;
1068 l_last_pos NUMBER := 0;
1069 l_value VARCHAR2(1024);
1070 l_cntr NUMBER := 0;
1071 l_num_chars VARCHAR2(1024);
1072 l_sales_transaction_rec OZF_SALES_TRANSACTIONS_PVT.SALES_TRANSACTION_REC_TYPE;
1073 l_order_gl_phase CONSTANT VARCHAR2 (15) := NVL(fnd_profile.VALUE ('OZF_ORDER_GLPOST_PHASE'), 'SHIPPED');
1074 l_sales_transaction_id NUMBER;
1075 l_gl_date DATE;
1076 l_shipment_date DATE;
1077 l_shipped_qty NUMBER;
1078 l_flow_status_code VARCHAR2(30);
1079 l_invoice_status_code VARCHAR2(30);
1080 l_invoiced_quantity NUMBER;
1081 l_order_number NUMBER;
1082 l_sold_to_org_id NUMBER;
1083 l_invoice_to_org_id NUMBER;
1084 l_ship_to_org_id NUMBER;
1085 l_shipping_quantity_uom VARCHAR2(30);
1086 l_order_quantity_uom VARCHAR2(30);
1087 l_unit_selling_price NUMBER;
1088 l_org_id NUMBER;
1089 l_sales_trans NUMBER;
1090 l_adjustment_exists NUMBER;
1091 l_act_budget_id NUMBER;
1092 l_apply_discount VARCHAR2(1);
1093
1094 l_prod_attr VARCHAR2(50);
1095 l_prod_attr_val VARCHAR2(20);
1096 l_item VARCHAR2(240);
1097 l_cond_id_column varchar2(240) := null;
1098 l_product_val_cursor product_attr_val_cursor_type;
1099 l_category_id NUMBER;
1100 l_stmt VARCHAR2(3000);
1101
1102
1103 l_group_id NUMBER;
1104 l_pbh_line_id NUMBER;
1105 l_included_vol_flag VARCHAR2(1);
1106 l_retroactive VARCHAR2(1) ;
1107 l_discount_type VARCHAR2(30);
1108 l_volume_type VARCHAR2(30);
1109 l_return_status VARCHAR2 (20) := fnd_api.g_ret_sts_success;
1110 l_msg_count NUMBER;
1111 l_msg_data VARCHAR2 (2000) := NULL;
1112 l_source_code VARCHAR2(30);
1113 l_volume NUMBER;
1114 l_ordered_qty NUMBER;
1115 l_utilization_amount NUMBER;
1116 l_new_discount NUMBER;
1117 l_min_tier NUMBER;
1118 l_max_tier NUMBER;
1119 l_adj_amount NUMBER;
1120
1121
1122 l_current_offer_tier_id NUMBER;
1123 y1 NUMBER; -- Initial Adjsutment
1124 l_current_max_tier NUMBER;
1125 l_current_min_tier NUMBER;
1126 l_current_tier_value NUMBER;
1127 l_previous_tier_max NUMBER;
1128 l_preset_tier NUMBER;
1129
1130 BEGIN
1131
1132 write_conc_log ('in adjustment_volume_retro');
1133
1134 --get the qualified orders
1135 get_orders(p_api_version => p_api_version
1136 ,p_init_msg_list => FND_API.G_FALSE
1137 ,p_commit => FND_API.G_FALSE
1138 ,x_return_status => x_return_status
1139 ,x_msg_count => x_msg_count
1140 ,x_msg_data => x_msg_data
1141 ,p_list_header_id => p_list_header_id
1142 ,p_list_line_id => NULL
1143 ,p_start_date => p_start_date
1144 ,p_end_date => p_end_date
1145 ,x_order_line_tbl => l_order_line_tbl
1146 );
1147
1148 write_conc_log ('x_return_status: ' || x_return_status);
1149 write_conc_log ('number of orders: ' || l_order_line_tbl.count);
1150
1151 l_volume:=0;
1152 l_utilization_amount := 0;
1153
1154
1155 IF l_order_line_tbl.count > 0 THEN
1156
1157 FOR j IN l_order_line_tbl.first .. l_order_line_tbl.last
1158 LOOP
1159
1160
1161
1162 write_conc_log ('==============');
1163 write_conc_log ('order number: ' || l_order_line_tbl(j).order_number);
1164 write_conc_log ('order line: ' || l_order_line_tbl(j).order_line_id);
1165 write_conc_log ('==============');
1166
1167
1168 --fix for bug # 5944862
1169 OPEN c_offer_info (p_list_header_id);
1170 FETCH c_offer_info INTO l_offer_info;
1171 CLOSE c_offer_info;
1172 -- -----
1173 write_conc_log('p_list_header_id: '||p_list_header_id);
1174 write_conc_log('l_order_line_tbl(j).inventory_item_id '||l_order_line_tbl(j).inventory_item_id);
1175
1176 OPEN c_get_items_type(p_list_header_id,l_order_line_tbl(j).inventory_item_id);
1177 FETCH c_get_items_type INTO l_prod_attr, l_prod_attr_val;
1178 CLOSE c_get_items_type;
1179
1180 write_conc_log('l_prod_attr: '||l_prod_attr);
1181 write_conc_log('l_prod_attr_val: '||l_prod_attr_val);
1182
1183 OPEN c_get_cond_id_column(l_prod_attr);
1184 FETCH c_get_cond_id_column INTO l_cond_id_column;
1185 CLOSE c_get_cond_id_column;
1186
1187 write_conc_log('l_cond_id_column: '||l_cond_id_column);
1188 -- fix for bug 5767748
1189
1190 IF l_prod_attr_val IS NULL THEN -- if not item category
1191
1192 l_prod_attr_val :=l_order_line_tbl(j).inventory_item_id;
1193 IF l_cond_id_column IS NOT NULL THEN --if product context
1194
1195 l_stmt := 'select ' || l_cond_id_column ||
1196 ' from mtl_system_items where ORGANIZATION_ID = FND_PROFILE.VALUE(''QP_ORGANIZATION_ID'') and inventory_item_id =:1 and rownum = 1';
1197 write_conc_log(l_stmt);
1198
1199 OPEN l_product_val_cursor FOR l_stmt using l_prod_attr_val;
1200 LOOP
1201 FETCH l_product_val_cursor INTO l_prod_attr_val;
1202 EXIT WHEN l_product_val_cursor%NOTFOUND;
1203 END LOOP;
1204
1205 --ELSE -- if inventory item
1206 --l_prod_attr_val :=l_order_line_tbl(j).inventory_item_id;
1207 END IF;
1208 END IF;
1209
1210 write_conc_log('l_prod_attr_val: '||l_prod_attr_val);
1211 write_conc_log('l_offer_info.offer_id: '||l_offer_info.offer_id);
1212
1213
1214 OPEN c_list_line (l_offer_info.offer_id,l_prod_attr_val);
1215 FETCH c_list_line INTO l_list_line_id,l_prod_attr,l_prod_attr_val;
1216 CLOSE c_list_line;
1217
1218
1219 write_conc_log('l_list_line_id: '||l_list_line_id);
1220 write_conc_log('l_prod_attr: '||l_prod_attr);
1221 write_conc_log('ll_prod_attr_val: '||l_prod_attr_val);
1222 -- -----
1223
1224
1225 l_index := 1;
1226 -- product
1227 l_req_line_attrs_tbl(l_index).line_index := 1;
1228 l_req_line_attrs_tbl(l_index).attribute_type := 'PRODUCT';
1229 l_req_line_attrs_tbl(l_index).context := NULL;
1230 l_req_line_attrs_tbl(l_index).attribute := l_prod_attr;
1231 l_req_line_attrs_tbl(l_index).value := l_prod_attr_val; -- inventory_item_id
1232 l_req_line_attrs_tbl(l_index).grouping_no := NULL;
1233
1234
1235 l_cntr := 0;
1236 l_last_pos := 0;
1237 l_first_pos := 1;
1238 l_num_chars := 0;
1239
1240 write_conc_log ('l_order_line_tbl(j).group_nos: ' || l_order_line_tbl(j).group_nos);
1241
1242 IF l_order_line_tbl(j).group_nos IS NOT NULL THEN
1243
1244 --loop to get individual group number from the comma seperated list
1245 l_string := l_order_line_tbl(j).group_nos;
1246 LOOP
1247 l_last_pos := INSTR(l_string,',',1,l_cntr+1);
1248 l_num_chars := l_last_pos - l_first_pos;
1249 IF l_last_pos = 0 THEN
1250 l_value := SUBSTR(l_string, l_first_pos);
1251 ELSE
1252 l_value := substr(l_string, l_first_pos,l_num_chars);
1253 l_first_pos := l_last_pos + 1;
1254 END IF;
1255 l_cntr := l_cntr + 1;
1256
1257 -- qualifier
1258 l_index := l_index + 1;
1259 l_req_line_attrs_tbl(l_index).line_index := 1;
1260 l_req_line_attrs_tbl(l_index).attribute_type := 'QUALIFIER';
1261 l_req_line_attrs_tbl(l_index).context := NULL;
1262 l_req_line_attrs_tbl(l_index).attribute := NULL;
1263 l_req_line_attrs_tbl(l_index).value := NULL;
1264 l_req_line_attrs_tbl(l_index).grouping_no := l_value;
1265
1266 write_conc_log('group no: ' || l_value);
1267
1268 IF l_last_pos = 0 THEN
1269 EXIT;
1270 END IF;
1271 END LOOP;
1272
1273 END IF;
1274
1275
1276
1277 write_conc_log('l_list_line_id: ' || l_list_line_id);
1278 write_conc_log('calling OZF_VOLUME_CALCULATION_PUB.get_numeric_attribute_value');
1279
1280 --simulation of pricing engine call while booking order
1281 l_dummy := OZF_VOLUME_CALCULATION_PUB.get_numeric_attribute_value
1282 (p_list_line_id => l_list_line_id
1283 ,p_list_line_no => NULL
1284 ,p_order_header_id => l_order_line_tbl(j).order_header_id
1285 ,p_order_line_id => l_order_line_tbl(j).order_line_id
1286 ,p_price_effective_date => NULL
1287 ,p_req_line_attrs_tbl => l_req_line_attrs_tbl
1288 ,p_accum_rec => NULL
1289 );
1290
1291 write_conc_log('calling OZF_VOLUME_CALCULATION_PUB.get_numeric_attribute_value returns: ' || l_dummy);
1292
1293 l_gl_date := NULL;
1294
1295 OPEN c_order_line_details (l_order_line_tbl(j).order_line_id);
1296 FETCH c_order_line_details into l_shipment_date, l_shipped_qty, l_flow_status_code, l_invoice_status_code,
1297 l_invoiced_quantity, l_sold_to_org_id, l_invoice_to_org_id, l_ship_to_org_id,
1298 l_shipping_quantity_uom, l_order_quantity_uom, l_unit_selling_price, l_org_id, l_ordered_qty;
1299 CLOSE c_order_line_details;
1300
1301 write_conc_log ('order org: ' || l_org_id);
1302
1303 IF ( l_order_gl_phase = 'SHIPPED' AND l_order_line_tbl(j).line_category_code <> 'RETURN' AND
1304 NVL(l_shipped_qty,0) <> 0 AND l_flow_status_code = 'SHIPPED') THEN
1305
1306 l_gl_date := l_shipment_date;
1307 l_sales_transaction_rec.quantity := l_shipped_qty;
1308 l_sales_transaction_rec.transfer_type := 'IN';
1309
1310 write_conc_log('gl date is shipment date: ' || l_gl_date);
1311
1312 END IF;
1313
1314 IF l_gl_date IS NULL THEN
1315 IF (l_invoice_status_code = 'YES' OR NVL(l_invoiced_quantity,0) <> 0) THEN
1316 OPEN c_invoice_date(l_order_line_tbl(j).order_line_id, l_order_line_tbl(j).order_number);
1317 FETCH c_invoice_date INTO l_gl_date;
1318 CLOSE c_invoice_date;
1319
1320 write_conc_log('gl date is invoice date: ' || l_gl_date);
1321
1322 IF l_gl_date IS NULL THEN
1323 l_gl_date := sysdate;
1324 write_conc_log('gl date is sysdate: ' || l_gl_date);
1325 END IF;
1326
1327 l_sales_transaction_rec.quantity := l_invoiced_quantity;
1328
1329 END IF;
1330 END IF;
1331
1332 write_conc_log('gl date: ' || l_gl_date);
1333 write_conc_log('line id: ' || l_order_line_tbl(j).order_line_id);
1334
1335 IF l_gl_date IS NOT NULL THEN
1336 OPEN sales_transation_csr(l_order_line_tbl(j).order_line_id);
1337 FETCH sales_transation_csr INTO l_sales_trans;
1338 CLOSE sales_transation_csr;
1339
1340 write_conc_log('l_sales_trans: ' || l_sales_trans);
1341
1342 l_sales_transaction_rec.sold_to_cust_account_id := l_sold_to_org_id;
1343
1344 OPEN party_id_csr(l_sales_transaction_rec.sold_to_cust_account_id);
1345 FETCH party_id_csr INTO l_sales_transaction_rec.sold_to_party_id;
1346 CLOSE party_id_csr;
1347
1348 OPEN party_site_id_csr(l_invoice_to_org_id);
1349 FETCH party_site_id_csr INTO l_sales_transaction_rec.sold_to_party_site_id;
1350 CLOSE party_site_id_csr;
1351
1352 l_sales_transaction_rec.ship_to_site_use_id := l_ship_to_org_id;
1353 l_sales_transaction_rec.bill_to_site_use_id := l_invoice_to_org_id;
1354 l_sales_transaction_rec.uom_code:= NVL(l_shipping_quantity_uom, l_order_quantity_uom);
1355 l_sales_transaction_rec.amount := l_unit_selling_price * l_sales_transaction_rec.quantity;
1356 l_sales_transaction_rec.currency_code := l_order_line_tbl(j).transactional_curr_code;
1357 l_sales_transaction_rec.inventory_item_id := l_order_line_tbl(j).inventory_item_id;
1358 l_sales_transaction_rec.header_id := l_order_line_tbl(j).order_header_id;
1359 l_sales_transaction_rec.line_id := l_order_line_tbl(j).order_line_id;
1360 l_sales_transaction_rec.source_code := 'OM';
1361
1362 IF l_order_line_tbl(j).line_category_code <> 'RETURN' THEN
1363 l_sales_transaction_rec.transfer_type := 'IN';
1364 ELSE
1365 l_sales_transaction_rec.transfer_type := 'OUT';
1366 END IF;
1367
1368 l_sales_transaction_rec.transaction_date := l_gl_date;
1369 l_sales_transaction_rec.org_id := l_org_id;
1370 l_sales_transaction_rec.qp_list_header_id := p_list_header_id;
1371
1372 write_conc_log('calling Create_Transaction');
1373
1374 OZF_SALES_TRANSACTIONS_PVT.Create_Transaction(p_api_version => 1.0
1375 ,p_init_msg_list => FND_API.G_FALSE
1376 ,p_commit => FND_API.G_FALSE
1377 ,p_validation_level => FND_API.G_VALID_LEVEL_FULL
1378 ,p_transaction_rec => l_sales_transaction_rec
1379 ,x_sales_transaction_id => l_sales_transaction_id
1380 ,x_return_status => x_return_status
1381 ,x_msg_data => x_msg_data
1382 ,x_msg_count => x_msg_count
1383 );
1384
1385 write_conc_log('Create_Transaction returns: ' || x_return_status);
1386 write_conc_log('l_sales_transaction_id: ' || l_sales_transaction_id);
1387
1388 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1389 RETURN;
1390 END IF;
1391
1392 END IF; --IF l_gl_date IS NOT NULL THEN
1393
1394 -- As booked orders are also considered so closed the "IF l_gl_date IS NOT NULL THEN" condition here
1395 -- fix for bug 6021635
1396
1397 --OPEN c_apply_discount(l_offer_info.offer_id, l_order_line_tbl(j).inventory_item_id);
1398 OPEN c_apply_discount(l_offer_info.offer_id, l_prod_attr_val);
1399 FETCH c_apply_discount INTO l_apply_discount;
1400 CLOSE c_apply_discount;
1401
1402 IF l_apply_discount = 'N' THEN
1403 write_conc_log('no discount since apply discount flag is unchecked: '|| l_order_line_tbl(j).inventory_item_id);
1404 GOTO l_endofOrderloop;
1405 END IF;
1406
1407 OPEN c_adjustment_exists (p_list_header_id, l_order_line_tbl(j).order_line_id);
1408 FETCH c_adjustment_exists INTO l_adjustment_exists;
1409 CLOSE c_adjustment_exists;
1410
1411 l_adjustment_exists := 0;
1412
1413 -- create adjustment record for the order line if it doesn't exists, otherwise
1414 -- volume_offer_adjustment will not consider this order line
1415 -- IF NVL(l_adjustment_exists,0) <> 1 THEN
1416
1417 l_act_budgets_rec.act_budget_used_by_id := p_list_header_id;
1418 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
1419 l_act_budgets_rec.budget_source_type := 'OFFR';
1420 l_act_budgets_rec.budget_source_id := p_list_header_id;
1421 l_act_budgets_rec.request_currency := l_offer_info.transaction_currency_code;
1422 l_act_budgets_rec.request_date := SYSDATE;
1423 l_act_budgets_rec.status_code := 'APPROVED';
1424 l_act_budgets_rec.user_status_id := ozf_Utility_Pvt.get_default_user_status (
1425 'OZF_BUDGETSOURCE_STATUS', l_act_budgets_rec.status_code);
1426 l_act_budgets_rec.approved_in_currency := l_offer_info.transaction_currency_code;
1427 l_act_budgets_rec.approval_date := SYSDATE;
1428 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id (fnd_global.user_id);
1429 l_act_budgets_rec.justification := 'Offer adjustment before offer start date';
1430 l_act_budgets_rec.transfer_type := 'UTILIZED';
1431
1432 l_act_util_rec.utilization_type :='ADJUSTMENT';
1433 l_act_util_rec.product_level_type := 'PRODUCT';
1434 l_act_util_rec.adjustment_date := SYSDATE;
1435 l_act_util_rec.cust_account_id := l_offer_info.beneficiary_account_id;
1436 l_act_util_rec.ship_to_site_use_id := l_sales_transaction_rec.ship_to_site_use_id;
1437 l_act_util_rec.bill_to_site_use_id := l_sales_transaction_rec.bill_to_site_use_id;
1438
1439 l_act_util_rec.product_id := l_order_line_tbl(j).inventory_item_id;
1440 l_act_util_rec.object_type :='ORDER';
1441 l_act_util_rec.object_id := l_order_line_tbl(j).order_header_id;
1442 l_act_util_rec.order_line_id := l_order_line_tbl(j).order_line_id;
1443 l_act_util_rec.price_adjustment_id := -1;
1444 l_act_util_rec.org_id := l_org_id; --nirprasa, added for bug 7030415
1445
1446
1447 OPEN c_cust_number (l_order_line_tbl(j).order_header_id);
1448 FETCH c_cust_number INTO l_cust_number;
1449 CLOSE c_cust_number;
1450
1451 l_act_util_rec.billto_cust_account_id := l_cust_number;
1452
1453 IF l_offer_info.beneficiary_account_id IS NULL THEN
1454 l_act_util_rec.cust_account_id := l_cust_number;
1455 END IF;
1456
1457 l_act_util_rec.adjustment_type :='STANDARD'; -- Seeded Data for Backdated Positive Adj
1458 l_act_util_rec.adjustment_type_id := -5; -- Seeded Data for Backdated Positive Adj
1459
1460
1461 --For booked orders get the total volume and the discount based on the
1462 --tiers then create the utilization
1463
1464
1465 OPEN c_get_group(l_order_line_tbl(j).order_line_id,p_list_header_id);
1466 FETCH c_get_group INTO l_group_id,l_pbh_line_id,l_included_vol_flag;
1467 CLOSE c_get_group;
1468
1469 IF G_DEBUG THEN
1470 ozf_utility_pvt.debug_message(' l_group_id: '|| l_group_id );
1471 ozf_utility_pvt.debug_message(' l_pbh_line_id: '|| l_pbh_line_id );
1472 ozf_utility_pvt.debug_message(' l_included_vol_flag: '|| l_included_vol_flag );
1473 END IF;
1474 write_conc_log(' l_group_id: '|| l_group_id );
1475 write_conc_log(' l_pbh_line_id: '|| l_pbh_line_id );
1476 write_conc_log(' l_included_vol_flag: '|| l_included_vol_flag );
1477
1478 IF l_group_id is NULL OR l_pbh_line_id is NULL THEN
1479 GOTO l_endofOrderloop;
1480 END IF;
1481
1482 OPEN c_market_option(p_list_header_id,l_group_id);
1483 FETCH c_market_option INTO l_retroactive;
1484 CLOSE c_market_option;
1485
1486 OPEN c_discount_header(l_pbh_line_id);
1487 FETCH c_discount_header INTO l_discount_type,l_volume_type;
1488 CLOSE c_discount_header;
1489
1490 write_conc_log('l_retroactive: '||l_retroactive);
1491 write_conc_log('p_qp_list_header_id: '||p_list_header_id);
1492 write_conc_log('l_order_line_tbl(j).order_line_id: '||l_order_line_tbl(j).order_line_id);
1493
1494 l_volume:=l_volume+NVL(l_ordered_qty,0);
1495
1496 OPEN c_order_adjustment_amt (l_order_line_tbl(j).order_header_id, l_order_line_tbl(j).order_line_id, l_order_line_tbl(j).inventory_item_id);
1497 FETCH c_order_adjustment_amt INTO l_adj_amount;
1498 CLOSE c_order_adjustment_amt;
1499
1500 write_conc_log('l_volume: '||l_volume);
1501 write_conc_log('l_adj_amount : '||l_adj_amount);
1502
1503
1504 IF l_retroactive = 'Y' THEN
1505
1506 OPEN c_current_discount(l_volume,l_pbh_line_id);
1507 FETCH c_current_discount INTO l_new_discount;
1508 CLOSE c_current_discount;
1509 write_conc_log('l_new_discount 111: '||l_new_discount);
1510
1511 IF l_new_discount is NULL THEN
1512 OPEN c_get_tier_limits(l_pbh_line_id);
1513 FETCH c_get_tier_limits INTO l_min_tier,l_max_tier;
1514 CLOSE c_get_tier_limits;
1515 IF l_volume < l_min_tier THEN
1516 l_new_discount := 0;
1517 ELSE
1518 OPEN c_get_max_tier(l_max_tier,l_pbh_line_id);
1519 FETCH c_get_max_tier INTO l_new_discount;
1520 CLOSE c_get_max_tier;
1521 END IF;
1522 IF G_DEBUG THEN
1523 ozf_utility_pvt.debug_message(' l_new_discount: '|| l_new_discount );
1524 END IF;
1525 write_conc_log(' l_new_discount: '|| l_new_discount );
1526 END IF;
1527
1528 l_preset_tier := NULL;
1529
1530 OPEN c_preset_tier(l_pbh_line_id,p_list_header_id,l_group_id);
1531 FETCH c_preset_tier INTO l_preset_tier;
1532 CLOSE c_preset_tier;
1533
1534 write_conc_log( ' l_preset_tier=' || l_preset_tier);
1535 write_conc_log( ' l_new_discount=' || l_new_discount);
1536
1537 IF l_preset_tier is NOT NULL AND l_preset_tier > l_new_discount THEN
1538 l_new_discount := l_preset_tier;
1539 IF G_DEBUG THEN
1540 ozf_utility_pvt.debug_message('not reach preset tier: ');
1541 END IF;
1542 write_conc_log(' not reach preset tier:');
1543 END IF;
1544
1545
1546 write_conc_log(' l_new_discount: '|| l_new_discount );
1547 IF l_discount_type = '%' THEN
1548 IF l_volume_type = 'PRICING_ATTRIBUTE12' THEN
1549 l_utilization_amount := l_ordered_qty * l_new_discount / 100;
1550 ELSE -- % is for unit price. need to multiple when range in quantity.
1551 l_utilization_amount := l_ordered_qty * l_unit_selling_price * l_new_discount / 100;
1552 END IF;
1553 ELSIF l_discount_type = 'AMT' THEN
1554 IF l_volume_type = 'PRICING_ATTRIBUTE12' THEN
1555 -- amt is for unit pirce. need to divide when range in amount.
1556 l_utilization_amount :=l_ordered_qty / l_unit_selling_price * l_new_discount ;
1557 ELSE
1558 l_utilization_amount :=l_ordered_qty * l_new_discount ;
1559 END IF;
1560 END IF;
1561
1562 --end
1563
1564 END IF; -- end of IF l_retroactive = 'Y' THEN
1565
1566
1567
1568 --for non retro same as volume offer adjustment
1569
1570
1571 IF NVL(l_retroactive, 'N') = 'N' THEN
1572
1573 l_utilization_amount:=0;
1574
1575 IF l_included_vol_flag = 'Y' THEN
1576 l_previous_tier_max := l_volume;
1577 ELSE
1578 /*
1579 logic here is to add current order line's volume to offer's volume for adjustment.
1580 eg: offer's volume=2.
1581 order line's volume = 5, then total volume = 7.
1582 */
1583 l_previous_tier_max := l_volume + l_ordered_qty;
1584 END IF;
1585
1586 IF G_DEBUG THEN
1587 ozf_utility_pvt.debug_message( ' l_ordered_qty=' || l_ordered_qty);
1588 END IF;
1589 write_conc_log( ' l_value=' || l_ordered_qty);
1590 l_preset_tier := NULL;
1591
1592 OPEN c_prior_tiers(l_pbh_line_id, l_volume);
1593 LOOP
1594 FETCH c_prior_tiers INTO l_current_offer_tier_id,l_current_min_tier,l_current_max_tier,l_current_tier_value;
1595 EXIT WHEN c_prior_tiers%NOTFOUND;
1596
1597 write_conc_log( ' l_current_offer_tier_id=' || l_current_offer_tier_id);
1598
1599
1600
1601 OPEN c_preset_tier(l_pbh_line_id,p_list_header_id,l_group_id);
1602 FETCH c_preset_tier INTO l_preset_tier;
1603 CLOSE c_preset_tier;
1604
1605
1606 write_conc_log( ' l_preset_tier=' || l_preset_tier);
1607 write_conc_log( ' l_current_tier_value=' || l_current_tier_value);
1608
1609 IF l_preset_tier is NOT NULL AND l_preset_tier > l_current_tier_value THEN
1610 l_current_tier_value := l_preset_tier;
1611 IF G_DEBUG THEN
1612 ozf_utility_pvt.debug_message('not reach preset tier: ');
1613 END IF;
1614 write_conc_log(' not reach preset tier:');
1615 END IF;
1616
1617
1618 y1 := LEAST((l_previous_tier_max-l_current_min_tier),l_ordered_qty) ;
1619 l_ordered_qty := l_ordered_qty - y1;
1620 IF l_discount_type = '%' THEN
1621 IF l_volume_type = 'PRICING_ATTRIBUTE12' THEN
1622 l_utilization_amount := l_utilization_amount + y1* l_current_tier_value / 100;
1623 ELSE
1624 l_utilization_amount := l_utilization_amount + y1* l_unit_selling_price * l_current_tier_value / 100;
1625 END IF;
1626 ELSIF l_discount_type = 'AMT' THEN
1627 IF l_volume_type = 'PRICING_ATTRIBUTE12' THEN
1628 l_utilization_amount := l_utilization_amount + y1 / l_unit_selling_price * l_current_tier_value ;
1629 ELSE
1630 l_utilization_amount := l_utilization_amount + y1* l_current_tier_value ;
1631 END IF;
1632 END IF;
1633
1634 --l_previous_tier_max := l_current_min_tier - 1 ;
1635 l_previous_tier_max := l_current_min_tier;
1636
1637 IF G_DEBUG THEN
1638 ozf_utility_pvt.debug_message(' retroactive flag is N, computing for prior tier id=' || l_current_offer_tier_id
1639 || ' y1=' || y1 || ' tier_min=' || l_current_min_tier
1640 || ' tier_max=' || l_current_max_tier || ' l_previous_tier_max: ' || l_previous_tier_max
1641 || ' l_new_utilization: ' || l_utilization_amount);
1642 END IF;
1643 write_conc_log(' retroactive flag is N, computing for prior tier id=' || l_current_offer_tier_id
1644 || ' y1=' || y1 || ' tier_min=' || l_current_min_tier
1645 || ' tier_max=' || l_current_max_tier || ' l_previous_tier_max: ' || l_previous_tier_max
1646 || ' l_new_utilization: ' || l_utilization_amount);
1647
1648 EXIT WHEN l_ordered_qty <= 0;
1649
1650 END LOOP; -- end of loop for c_prior_tiers
1651 CLOSE c_prior_tiers;
1652
1653 END IF; -- IF NVL(l_retroactive, 'N') = 'N' THEN
1654
1655
1656 write_conc_log('l_utilization_amount : '||l_utilization_amount);
1657
1658
1659 l_utilization_amount := NVL(l_utilization_amount,0) - NVL(l_adj_amount,0);
1660
1661 l_act_budgets_rec.request_amount := l_utilization_amount;
1662 l_act_budgets_rec.approved_amount := l_utilization_amount;
1663
1664
1665 ----end of booked orders
1666
1667 write_conc_log('calling ozf_fund_adjustment_pvt.process_act_budgets');
1668
1669 ozf_fund_adjustment_pvt.process_act_budgets(x_return_status => x_return_status
1670 ,x_msg_count => x_msg_count
1671 ,x_msg_data => x_msg_data
1672 ,p_act_budgets_rec => l_act_budgets_rec
1673 ,p_act_util_rec => l_act_util_rec
1674 ,x_act_budget_id => l_act_budget_id
1675 );
1676
1677 write_conc_log('process_act_budgets returns: ' || x_return_status);
1678
1679 IF x_return_status <> fnd_api.g_ret_sts_success THEN
1680 RETURN;
1681 END IF;
1682
1683 --END IF; --IF NVL(l_adjustment_exists,0) <> 1 THEN
1684
1685 -- END IF; --IF l_gl_date IS NOT NULL THEN
1686
1687 <<l_endofOrderloop>>
1688 NULL;
1689
1690 END LOOP; --FOR j IN l_order_line_tbl.first .. l_order_line_tbl.last
1691
1692 END IF; --IF l_order_line_tbl.count > 0 THEN
1693
1694 END adjustment_volume_retro;
1695 ---------------------------------------------------------------------
1696 -- PROCEDURE
1697 -- adjust_backdated_offer
1698 --
1699 -- PURPOSE
1700 -- This API is called from the concurrent process Post Backdated Adjusted Offer
1701 -- PARAMETERS
1702 -- x_errbuf OUT NOCOPY VARCHAR2 STANDARD OUT NOCOPY PARAMETER
1703 -- x_retcode OUT NOCOPY NUMBER STANDARD OUT NOCOPY PARAMETER
1704 -- NOTES
1705 -- HISTORY
1706 -- 4/18/2002 Mumu Pande Create.
1707 -- 07/05/2005 feliu fix following issues
1708 -- 1. update discount in QP only when sysdate pass effective date.
1709 ----------------------------------------------------------------------
1710 PROCEDURE adjust_backdated_offer (x_errbuf OUT NOCOPY VARCHAR2,
1711 x_retcode OUT NOCOPY NUMBER,
1712 p_debug IN VARCHAR2 := 'N' ) IS
1713 l_return_status VARCHAR2 (20);
1714 l_msg_count NUMBER;
1715 l_msg_data VARCHAR2 (2000) := NULL;
1716 l_api_name VARCHAR2 (50) := 'adjust_backdated_offer';
1717 l_full_name CONSTANT VARCHAR2 (90) := g_pkg_name
1718 || '.'
1719 || l_api_name;
1720 l_api_version NUMBER := 1;
1721 l_index NUMBER := 1;
1722
1723 CURSOR c_adjusted_offer_cur IS
1724 SELECT offer_adjustment_id,
1725 list_header_id,
1726 effective_date,
1727 approved_date
1728 FROM ozf_offer_adjustments_b
1729 WHERE status_code = 'ACTIVE'
1730 AND NVL(budget_adjusted_flag,'N') = 'N'
1731 AND effective_date < approved_date; --query only backdated adjustments
1732
1733 --get the products for an adjustment
1734 CURSOR c_adjusted_line_cur (p_offer_adjustment_id IN NUMBER) IS
1735 SELECT adj.original_discount, adj.modified_discount, lines.arithmetic_operator,
1736 adj.created_from_adjustments, lines.list_line_id, rltd.to_list_line_id
1737 FROM ozf_offer_adjustment_lines adj, qp_list_lines lines, ozf_offer_adj_rltd_lines rltd
1738 WHERE adj.offer_adjustment_id = p_offer_adjustment_id
1739 AND lines.list_line_type_code = 'DIS'
1740 AND lines.list_line_id = adj.list_line_id
1741 AND rltd.from_list_line_id = adj.list_line_id
1742 AND rltd.offer_adjustment_id = adj.offer_adjustment_id;
1743
1744 CURSOR c_offer_info (p_list_header_id IN NUMBER) IS
1745 SELECT off.offer_id, qp.description, qp.NAME,
1746 nvl(off.transaction_currency_code,fund_request_curr_code) transaction_currency_code,
1747 off.reusable, off.offer_type,
1748 --kdass 09-DEC-2005 fix for bug 4872799
1749 trunc(off.start_date) start_date
1750 ,off.volume_offer_type
1751 FROM qp_list_headers_all qp, ozf_offers off
1752 WHERE qp.list_header_id = p_list_header_id
1753 AND qp.list_header_id = off.qp_list_header_id;
1754
1755 l_offer_info c_offer_info%ROWTYPE;
1756 l_adjusted_line_cur c_adjusted_line_cur%ROWTYPE;
1757 l_end_date DATE;
1758 l_type VARCHAR2(7) := NULL;
1759
1760 TYPE offerAdjustmentIdTbl IS TABLE OF ozf_offer_adjustments_b.offer_adjustment_id%TYPE;
1761 TYPE listHeaderIdTbl IS TABLE OF ozf_offer_adjustments_b.list_header_id%TYPE;
1762 TYPE effectiveDateTbl IS TABLE OF ozf_offer_adjustments_b.effective_date%TYPE;
1763 TYPE approvedDateTbl IS TABLE OF ozf_offer_adjustments_b.approved_date%TYPE;
1764
1765 l_offerAdjustmentIdTbl offerAdjustmentIdTbl;
1766 l_listHeaderIdTbl listHeaderIdTbl;
1767 l_effectiveDateTbl effectiveDateTbl;
1768 l_approvedDateTbl approvedDateTbl;
1769
1770
1771 BEGIN
1772 g_debug_flag := p_debug ;
1773 write_conc_log (' /*************************** ADJUST BD START *************************/');
1774 fnd_msg_pub.initialize;
1775 SAVEPOINT adjust_backdated_offer;
1776 --Get All Active Backdated Offer where budget adjusted flag = 'N'
1777 g_offer_id_tbl.delete;
1778
1779 OPEN c_adjusted_offer_cur;
1780 LOOP
1781
1782 FETCH c_adjusted_offer_cur BULK COLLECT INTO l_offerAdjustmentIdTbl, l_listHeaderIdTbl,
1783 l_effectiveDateTbl, l_approvedDateTbl
1784 LIMIT g_bulk_limit;
1785
1786 FOR i IN NVL(l_offerAdjustmentIdTbl.FIRST, 1) .. NVL(l_offerAdjustmentIdTbl.LAST, 0) LOOP
1787
1788 SAVEPOINT new_adjustment;
1789
1790 --get the offer id
1791 OPEN c_offer_info (l_listHeaderIdTbl(i));
1792 FETCH c_offer_info INTO l_offer_info;
1793 CLOSE c_offer_info;
1794
1795 write_conc_log (
1796 '/******** '
1797 || 'Begin Adjusting For Offer NAME '''
1798 || l_offer_info.description
1799 || ''' SOURCE CODE '''
1800 || l_offer_info.NAME
1801 || ''' ******/'
1802 );
1803
1804 /*removed code for future dated adjustments since offers team will be taking care of this.
1805 original code in version 120.19 */
1806 l_return_status :=fnd_api.g_ret_sts_success;
1807 write_conc_log ( l_full_name || ' : ' || 'Back Dated Adjusting ' || l_listHeaderIdTbl(i));
1808 -- Perform Adjustments for the already executed offer (for the orders raised between Effective Date and Approved Date)
1809 -- Fixed 10/23/2002 mpande
1810 IF l_offer_info.offer_type <> 'VOLUME_OFFER' THEN
1811 perform_adjustment(p_from_date=> l_effectiveDateTbl(i)
1812 ,p_to_date=> l_approvedDateTbl(i)
1813 ,p_qp_list_header_id=> l_listHeaderIdTbl(i)
1814 ,p_offer_adjustment_id=> l_offerAdjustmentIdTbl(i)
1815 ,x_return_status=> l_return_status
1816 ,x_msg_count=> l_msg_count
1817 ,x_msg_data=> l_msg_data
1818 );
1819
1820
1821 write_conc_log ( l_full_name || ' : ' || 'Return Status For perform_adjustment ' || l_return_status);
1822
1823 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1824 ozf_utility_pvt.write_conc_log ('/****** '
1825 || 'Offer Adjustment Failed For Offer'
1826 || l_offer_info.description
1827 || ' SOURCE CODE '
1828 || l_offer_info.NAME
1829 || '" Offer Adjustment Id "'
1830 || l_offerAdjustmentIdTbl(i)
1831 || ' with the following Errors *******/'
1832 );
1833 ozf_utility_pvt.write_conc_log;
1834 fnd_msg_pub.initialize;
1835 ROLLBACK TO new_adjustment;
1836 GOTO l_endofloop;
1837 END IF;
1838 END IF;
1839
1840 --adjust orders between adjustment effective date and approval date
1841
1842 ozf_utility_pvt.write_conc_log ('adjusted_rec.offer_adjustment_id: ' || l_offerAdjustmentIdTbl(i));
1843
1844 OPEN c_adjusted_line_cur (l_offerAdjustmentIdTbl(i));
1845 LOOP
1846 FETCH c_adjusted_line_cur INTO l_adjusted_line_cur;
1847 EXIT WHEN c_adjusted_line_cur%NOTFOUND;
1848
1849 ozf_utility_pvt.write_conc_log ('l_adjusted_line_cur.original_discount: ' || l_adjusted_line_cur.original_discount);
1850
1851 l_type := NULL;
1852
1853 --if new product
1854 IF l_adjusted_line_cur.created_from_adjustments = 'Y' THEN
1855
1856 /*kdass 20-JUL-05 Bug 4489233
1857 For new product with:
1858 1) adjustment effective date < adjustment approval date
1859 adjust orders between adjustment effective date and adjustment approval date
1860 2) adjustment approval date <= effective date <= sysdate
1861 adjust orders between adjustment effective date and sysdate and set QP to new discount
1862 */
1863 l_end_date := l_approvedDateTbl(i);
1864 l_type := 'product';
1865
1866 --not a new product
1867 ELSIF (l_effectiveDateTbl(i) <= l_offer_info.start_date) THEN
1868
1869 --l_end_date := l_offer_info.start_date - 1;
1870 l_end_date := l_offer_info.start_date;
1871 l_type := 'retro';
1872
1873 END IF;
1874
1875 IF l_type IS NOT NULL AND l_offer_info.offer_type <> 'VOLUME_OFFER' THEN
1876 --retroactive adjustment before offer start date or adjustment for new product
1877 adjustment_net_accrual(p_api_version => l_api_version
1878 ,p_offer_type => l_offer_info.offer_type
1879 /*kdass 05-MAY-2006 bug 5205721
1880 ,p_product => l_adjusted_line_cur.product_attr_value
1881 ,p_product_attr => l_adjusted_line_cur.product_attribute
1882 */
1883 ,p_original_discount => l_adjusted_line_cur.original_discount
1884 ,p_modified_discount => l_adjusted_line_cur.modified_discount
1885 ,p_arithmetic_operator => l_adjusted_line_cur.arithmetic_operator
1886 ,p_start_date => l_effectiveDateTbl(i)
1887 ,p_end_date => l_end_date
1888 ,p_list_header_id => l_listHeaderIdTbl(i)
1889 ,p_list_line_id => l_adjusted_line_cur.to_list_line_id
1890 ,p_offer_adjustment_id => l_offerAdjustmentIdTbl(i)
1891 ,p_type => l_type
1892 ,x_return_status => l_return_status
1893 ,x_msg_count => l_msg_count
1894 ,x_msg_data => l_msg_data
1895 );
1896
1897 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1898 write_conc_log (' /*************************** DEBUG MESSAGE END *************************/');
1899 ozf_utility_pvt.write_conc_log (' /****** '
1900 || 'Backdated Offer Adjustment Failed For Offer'
1901 || l_offer_info.description
1902 || ' SOURCE CODE '
1903 || l_offer_info.NAME
1904 || '" Offer Adjustment Id "'
1905 || l_offerAdjustmentIdTbl(i)
1906 || ' with the following Errors *******/'
1907 );
1908 ozf_utility_pvt.write_conc_log;
1909 fnd_msg_pub.initialize;
1910 ROLLBACK TO new_adjustment;
1911 --kdass 21-JUN-2006 bug 5337761 - closed cursor on error
1912 CLOSE c_adjusted_line_cur;
1913 GOTO l_endofloop;
1914 END IF;
1915 END IF;
1916
1917 END LOOP;
1918 CLOSE c_adjusted_line_cur;
1919
1920 --END IF;
1921 -- if every thing goes correct then commit this adjustment
1922 -- only close the adjustment whose effective date less than sysdate to fix bug 4015372
1923
1924 IF l_return_status = fnd_api.g_ret_sts_success THEN --AND TRUNC(SYSDATE) >= l_effectiveDateTbl(i) THEN
1925
1926 IF l_offer_info.offer_type = 'VOLUME_OFFER' THEN
1927
1928 --kdass bug 5610124 - retroactive adjustments for volume offer before offer start date
1929 adjustment_volume_retro(p_api_version => l_api_version
1930 ,p_start_date => l_effectiveDateTbl(i)
1931 ,p_end_date => l_offer_info.start_date
1932 ,p_list_header_id => l_listHeaderIdTbl(i)
1933 ,p_offer_adjustment_id => l_offerAdjustmentIdTbl(i)
1934 ,x_return_status => l_return_status
1935 ,x_msg_count => l_msg_count
1936 ,x_msg_data => l_msg_data
1937 );
1938
1939 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1940 ozf_utility_pvt.write_conc_log (' /****** '
1941 || 'Backdated Offer Adjustment Failed For Offer'
1942 || l_offer_info.description
1943 || ' SOURCE CODE '
1944 || l_offer_info.NAME
1945 || '" Offer Adjustment Id "'
1946 || l_offerAdjustmentIdTbl(i)
1947 || ' with the following Errors *******/'
1948 );
1949 ozf_utility_pvt.write_conc_log;
1950 fnd_msg_pub.initialize;
1951 ROLLBACK TO new_adjustment;
1952 GOTO l_endofloop;
1953 END IF;
1954
1955 volume_offer_adjustment(p_qp_list_header_id=> l_listHeaderIdTbl(i)
1956 ,p_vol_off_type =>l_offer_info.volume_offer_type
1957 ,x_return_status=> l_return_status
1958 ,x_msg_count=> l_msg_count
1959 ,x_msg_data=> l_msg_data
1960 );
1961
1962 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1963 ozf_utility_pvt.write_conc_log ('volume adjustment Failed'
1964 || l_offer_info.description
1965 || ' SOURCE CODE '
1966 || l_offer_info.NAME
1967 || '" Offer Adjustment Id "'
1968 || l_offerAdjustmentIdTbl(i)
1969 || ' with the following Errors /'
1970 );
1971 ozf_utility_pvt.write_conc_log;
1972 fnd_msg_pub.initialize;
1973 ROLLBACK TO new_adjustment;
1974 GOTO l_endofloop;
1975 END IF;
1976
1977
1978 ------------
1979 volume_offer_util_adjustment(p_qp_list_header_id=> l_listHeaderIdTbl(i)
1980 ,x_return_status=> l_return_status
1981 ,x_msg_count=> l_msg_count
1982 ,x_msg_data=> l_msg_data
1983 );
1984
1985 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1986 ozf_utility_pvt.write_conc_log ('volume utilization adjustment Failed'
1987 || l_offer_info.description
1988 || ' SOURCE CODE '
1989 || l_offer_info.NAME
1990 || '" Offer Adjustment Id "'
1991 || l_offerAdjustmentIdTbl(i)
1992 || ' with the following Errors /'
1993 );
1994 ozf_utility_pvt.write_conc_log;
1995 fnd_msg_pub.initialize;
1996 ROLLBACK TO new_adjustment;
1997 GOTO l_endofloop;
1998 END IF;
1999 ------------
2000
2001 g_offer_id_tbl(l_index) := l_listHeaderIdTbl(i);
2002 l_index := l_index + 1;
2003
2004 ozf_utility_pvt.write_conc_log ('after calling volume_offer_adjustment: ' || l_return_status);
2005
2006 END IF; -- l_offer_info.offer_type = 'VOLUME_OFFER' THEN
2007
2008 UPDATE ozf_offer_adjustments_b
2009 SET budget_adjusted_flag = 'Y',
2010 object_version_number = object_version_number + 1,
2011 status_code = 'CLOSED'
2012 WHERE offer_adjustment_id = l_offerAdjustmentIdTbl(i);
2013
2014 x_retcode := 0;
2015 x_errbuf := l_msg_data;
2016 COMMIT;
2017
2018 END IF; -- end of l_return_status = fnd_api.g_ret_sts_success
2019
2020 <<l_endofloop>>
2021
2022 write_conc_log( 'Return Status After Adjustment' || l_return_status);
2023
2024 END LOOP; -- FOR i IN NVL(l_offerAdjustmentIdTbl.FIRST, 1) .. NVL(l_offerAdjustmentIdTbl.LAST, 0) LOOP
2025
2026 EXIT WHEN c_adjusted_offer_cur%NOTFOUND;
2027
2028 END LOOP; -- bulk fetch loop for c_adjusted_offer_cur
2029 write_conc_log (' /*************************** ADJUST BD END *************************/');
2030 EXCEPTION
2031 WHEN OTHERS THEN
2032 ROLLBACK TO adjust_backdated_offer;
2033 x_retcode := 1;
2034 x_errbuf := l_msg_data;
2035 ozf_utility_pvt.write_conc_log;
2036 ozf_utility_pvt.write_conc_log (x_errbuf);
2037 END adjust_backdated_offer;
2038
2039
2040 ---------------------------------------------------------------------
2041 -- PROCEDURE
2042 --
2043 --
2044 -- PURPOSE
2045 --
2046 -- PARAMETERS
2047 -- p_from_date IN DATE
2048 -- p_to_Date IN DATE
2049 -- p_qp_list_header_id IN NUMBER
2050 -- NOTES
2051 -- HISTORY
2052 -- 4/18/2002 Mumu Pande Create.
2053 -- 11/11/2002 mkothari Updated to handle adjustments for
2054 -- Multi Tier (Accrual and Off Invoice)
2055 -- Prom Goods,Order Value,Volume Offer
2056 -- and Trade Deal.
2057 -- 07/05/2005 feliu fix following issues
2058 -- 1. change logic to calculate for adjustment. calculate the total adjustment based on
2059 -- new discount and original discount when utilization is created from accrual engine.
2060 -- 2. change the adjusmtent calculation for NEWPRICE.
2061 -- 3. Add adjustment for promotional offer.
2062 ----------------------------------------------------------------------
2063 PROCEDURE perform_adjustment (
2064 p_from_date IN DATE,
2065 p_to_date IN DATE,
2066 p_qp_list_header_id IN NUMBER,
2067 p_offer_adjustment_id IN NUMBER,
2068 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
2069 p_commit IN VARCHAR2 := fnd_api.g_false,
2070 x_return_status OUT NOCOPY VARCHAR2,
2071 x_msg_count OUT NOCOPY NUMBER,
2072 x_msg_data OUT NOCOPY VARCHAR2
2073 ) IS
2074 l_act_budget_id NUMBER;
2075 l_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type;
2076 l_act_util_rec ozf_actbudgets_pvt.act_util_rec_type;
2077 l_return_status VARCHAR2 (1);
2078 l_util_amount NUMBER;
2079 l_api_name VARCHAR2 (50) := 'perform_adjustment';
2080 l_full_name CONSTANT VARCHAR2 (90) := g_pkg_name
2081 || '.'
2082 || l_api_name;
2083 l_to_date DATE := p_to_date + 0.99999;
2084 l_org_id NUMBER; -- := TO_NUMBER (SUBSTRB (USERENV ('CLIENT_INFO'), 1, 10)) ;
2085
2086 --kpatro 31-JUL-2006 bug 5375224 SQL ID# 19125146 - removed trunc from adjustment_date
2087 --for all cursors and added it to index OZF_FUNDS_UTILIZED_ALL_B_N24 to decrease the cost
2088 --and shared memory of the queries
2089 CURSOR c_bdadj_all_types IS
2090 SELECT util.utilization_id,
2091 util.object_type,
2092 util.object_id,
2093 util.order_line_id,
2094 util.product_id,
2095 util.billto_cust_account_id,
2096 util.cust_account_id,
2097 util.fund_id,
2098 util.currency_code,
2099 util.price_adjustment_id,
2100 --NULL,
2101 DECODE (oe.arithmetic_operator,
2102 -- julou 03/30/2007 fix bug 5849584 "original discount = 0" causes "divisor is 0" exception
2103 'NEWPRICE', DECODE(oe.adjusted_amount_per_pqty, 0, (ol.unit_selling_price - adjl.modified_discount) * ol.pricing_quantity, ((oe.operand - adjl.modified_discount) * amount /-oe.adjusted_amount_per_pqty)),
2104 '%', DECODE(oe.operand, 0, adjl.modified_discount * ol.unit_selling_price * ol.pricing_quantity / 100, (adjl.modified_discount - oe.operand) * amount / oe.operand),
2105 'AMT', DECODE(oe.operand, 0, adjl.modified_discount * ol.pricing_quantity, (adjl.modified_discount - oe.operand) * amount / oe.operand),
2106 'LUMPSUM', DECODE(oe.operand, 0, adjl.modified_discount, (adjl.modified_discount - oe.operand) * amount / oe.operand)
2107 -- 'NEWPRICE', ((oe.operand - adjl.modified_discount) * amount /-oe.adjusted_amount_per_pqty),
2108 -- ((adjl.modified_discount - oe.operand) * amount / oe.operand)
2109 ) amount
2110 ,util.org_id
2111 ,util.ship_to_site_use_id
2112 ,util.bill_to_site_use_id
2113 ,util.reference_type
2114 ,util.reference_id
2115 FROM ozf_funds_utilized_all_b util,
2116 ozf_temp_eligibility temp,
2117 ozf_offer_adjustment_lines adjl,
2118 oe_order_lines_all ol,
2119 oe_price_adjustments oe
2120 WHERE util.plan_type = 'OFFR'
2121 AND product_id IS NOT NULL
2122 AND util.plan_id = p_qp_list_header_id
2123 AND util.product_id = DECODE (temp.eligibility_id, -1, util.product_id, temp.eligibility_id)
2124 AND temp.offer_adjustment_line_id = adjl.offer_adjustment_line_id
2125 -- yzhao 01/13/2006 fix bug 4939453 offer adjustment creates new list_line_id
2126 -- AND adjl.list_line_id = oe.list_line_id
2127 AND oe.list_line_id IN (SELECT from_list_line_id
2128 FROM ozf_offer_adj_rltd_lines adjr
2129 START WITH adjr.from_list_line_id = adjl.list_line_id
2130 AND adjr.offer_adjustment_id = adjl.offer_adjustment_id
2131 CONNECT BY PRIOR adjr.from_list_line_id = adjr.to_list_line_id
2132 )
2133 AND adjl.offer_adjustment_id = p_offer_adjustment_id
2134 AND util.object_type = 'ORDER'
2135 AND util.price_adjustment_id = oe.price_adjustment_id
2136 AND oe.list_line_type_code <> 'PBH'
2137 AND adjustment_date BETWEEN p_from_date AND l_to_date
2138 AND util.utilization_type NOT IN('ADJUSTMENT','LEAD_ADJUSTMENT')
2139 AND ol.line_id = oe.line_id
2140
2141 UNION ALL
2142 --for third party accrual.
2143 SELECT util.utilization_id,
2144 util.object_type,
2145 util.object_id,
2146 util.order_line_id,
2147 util.product_id,
2148 util.billto_cust_account_id,
2149 util.cust_account_id,
2150 util.fund_id,
2151 util.currency_code,
2152 util.price_adjustment_id,
2153 --NULL,
2154 DECODE (oe.operand_calculation_code,
2155 'NEWPRICE', ((oe.operand - adjl.modified_discount) * amount /-oe.operand),
2156 ((adjl.modified_discount - oe.operand) * amount / oe.operand)
2157 ) amount
2158 ,util.org_id
2159 ,util.ship_to_site_use_id
2160 ,util.bill_to_site_use_id
2161 ,util.reference_type
2162 ,util.reference_id
2163 FROM ozf_funds_utilized_all_b util,
2164 ozf_temp_eligibility temp,
2165 ozf_offer_adjustment_lines adjl,
2166 OZF_RESALE_ADJUSTMENTS_ALL oe
2167 WHERE util.plan_type = 'OFFR'
2168 AND product_id IS NOT NULL
2169 AND util.plan_id = p_qp_list_header_id
2170 AND util.product_id = DECODE (temp.eligibility_id, -1, util.product_id, temp.eligibility_id)
2171 AND temp.offer_adjustment_line_id = adjl.offer_adjustment_line_id
2172 AND adjl.list_line_id = oe.list_line_id
2173 AND util.price_adjustment_id = oe.resale_adjustment_id
2174 AND adjustment_date BETWEEN p_from_date AND l_to_date
2175 AND util.utilization_type NOT IN('ADJUSTMENT','LEAD_ADJUSTMENT')
2176 -- yzhao 01/13/2006 fix bug 4939453 offer adjustment creates new list_line_id
2177 AND adjl.offer_adjustment_id = p_offer_adjustment_id
2178 AND util.object_type = 'TP_ORDER';
2179 -- kdass 01/31/2005 fix for bug 4129759 - handle backdated adjustments for multi-tier discounts
2180 /*
2181 UNION ALL
2182 SELECT util.utilization_id,
2183 util.object_type,
2184 util.object_id,
2185 util.order_line_id,
2186 util.product_id,
2187 util.billto_cust_account_id,
2188 util.cust_account_id,
2189 util.fund_id,
2190 util.currency_code,
2191 assocs.price_adjustment_id,
2192 --NULL,
2193 DECODE (oe.arithmetic_operator,
2194 'NEWPRICE', ((oe.operand - adjl.modified_discount) * amount /-oe.adjusted_amount_per_pqty),
2195 ((adjl.modified_discount - oe.operand) * amount / oe.operand)
2196 ) amount
2197 FROM ozf_funds_utilized_all_b util,
2198 oe_price_adj_assocs assocs,
2199 oe_price_adjustments oe,
2200 ozf_offer_adjustment_lines adjl,
2201 ozf_temp_eligibility temp
2202 WHERE util.plan_id = p_qp_list_header_id
2203 AND util.product_id = DECODE (temp.eligibility_id, -1, util.product_id, temp.eligibility_id)
2204 AND temp.offer_adjustment_line_id = adjl.offer_adjustment_line_id
2205 AND util.plan_type = 'OFFR'
2206 AND util.price_adjustment_id = assocs.price_adjustment_id
2207 AND oe.price_adjustment_id = assocs.rltd_price_adj_id
2208 AND oe.adjusted_amount IS NOT NULL
2209 AND oe.list_line_id = adjl.list_line_id
2210 AND oe.operand <> adjl.modified_discount
2211 AND adjustment_date BETWEEN p_from_date AND l_to_date
2212 AND util.utilization_type NOT IN('ADJUSTMENT','LEAD_ADJUSTMENT');
2213 --- (-1) is inserted in ozf_temp_eligibility for 'ALL' items, refer query above and query below
2214 */
2215 CURSOR c_bdadj_trade_deal IS
2216 SELECT util.utilization_id,
2217 util.object_type,
2218 util.object_id,
2219 util.order_line_id,
2220 util.product_id,
2221 util.billto_cust_account_id, -- yzhao: 11.5.10 added billto_cust_account_id
2222 util.cust_account_id,
2223 util.fund_id,
2224 util.currency_code,
2225 util.price_adjustment_id,
2226 --NULL,
2227 DECODE (oe.arithmetic_operator,
2228 -- julou 03/30/2007 fix bug 5849584 "original discount = 0" causes "divisor is 0" exception
2229 'NEWPRICE', DECODE(oe.adjusted_amount_per_pqty, 0, (ol.unit_selling_price - adjl.modified_discount) * ol.pricing_quantity, ((oe.operand - adjl.modified_discount) * amount /-oe.adjusted_amount_per_pqty)),
2230 '%', DECODE(oe.operand, 0, adjl.modified_discount * ol.unit_selling_price * ol.pricing_quantity / 100, (adjl.modified_discount - oe.operand) * amount / oe.operand),
2231 'AMT', DECODE(oe.operand, 0, adjl.modified_discount * ol.pricing_quantity, (adjl.modified_discount - oe.operand) * amount / oe.operand),
2232 'LUMPSUM', DECODE(oe.operand, 0, adjl.modified_discount, (adjl.modified_discount - oe.operand) * amount / oe.operand)
2233 -- 'NEWPRICE', ((oe.operand - adjl.modified_discount) * amount /-oe.adjusted_amount_per_pqty),
2234 -- ((adjl.modified_discount - oe.operand) * amount / oe.operand)
2235 ) amount
2236 ,util.org_id
2237 ,util.ship_to_site_use_id
2238 ,util.bill_to_site_use_id
2239 ,util.reference_type
2240 ,util.reference_id
2241 FROM ozf_funds_utilized_all_b util,
2242 ozf_temp_eligibility temp,
2243 ozf_offer_adjustment_lines adjl,
2244 oe_order_lines_all ol,
2245 oe_price_adjustments oe
2246 WHERE util.plan_type = 'OFFR'
2247 AND product_id IS NOT NULL
2248 AND util.plan_id = p_qp_list_header_id
2249 AND util.product_id = DECODE (temp.eligibility_id, -1, util.product_id, temp.eligibility_id)
2250 AND temp.offer_adjustment_line_id = adjl.offer_adjustment_line_id
2251 -- kdass 31-MAR-2006 fix bug 5101720 offer adjustment creates new list_line_id
2252 -- AND adjl.list_line_id = oe.list_line_id
2253 AND oe.list_line_id IN (SELECT from_list_line_id
2254 FROM ozf_offer_adj_rltd_lines adjr
2255 START WITH adjr.from_list_line_id = adjl.list_line_id
2256 AND adjr.offer_adjustment_id = adjl.offer_adjustment_id
2257 CONNECT BY PRIOR adjr.from_list_line_id = adjr.to_list_line_id
2258 )
2259 AND adjl.offer_adjustment_id = p_offer_adjustment_id
2260 AND util.object_type = 'ORDER'
2261 AND util.price_adjustment_id = oe.price_adjustment_id
2262 AND adjustment_date BETWEEN p_from_date AND l_to_date
2263 AND util.utilization_type NOT IN('ADJUSTMENT','LEAD_ADJUSTMENT')
2264 AND ol.line_id = oe.line_id
2265 UNION ALL
2266 --for accrual in third party accrual.
2267 SELECT util.utilization_id,
2268 util.object_type,
2269 util.object_id,
2270 util.order_line_id,
2271 util.product_id,
2272 util.billto_cust_account_id,
2273 util.cust_account_id,
2274 util.fund_id,
2275 util.currency_code,
2276 util.price_adjustment_id,
2277 --NULL,
2278 DECODE (oe.operand_calculation_code,
2279 'NEWPRICE', ((oe.operand - adjl.modified_discount) * amount /-oe.operand),
2280 ((adjl.modified_discount - oe.operand) * amount / oe.operand)
2281 ) amount
2282 ,util.org_id
2283 ,util.ship_to_site_use_id
2284 ,util.bill_to_site_use_id
2285 ,util.reference_type
2286 ,util.reference_id
2287 FROM ozf_funds_utilized_all_b util,
2288 ozf_temp_eligibility temp,
2289 ozf_offer_adjustment_lines adjl,
2290 OZF_RESALE_ADJUSTMENTS_ALL oe
2291 WHERE util.plan_type = 'OFFR'
2292 AND product_id IS NOT NULL
2293 AND util.plan_id = p_qp_list_header_id
2294 AND util.product_id = DECODE (temp.eligibility_id, -1, util.product_id, temp.eligibility_id)
2295 AND temp.offer_adjustment_line_id = adjl.offer_adjustment_line_id
2296 AND adjl.list_line_id = oe.list_line_id
2297 AND util.price_adjustment_id = oe.resale_adjustment_id
2298 AND adjustment_date BETWEEN p_from_date AND l_to_date
2299 AND util.utilization_type NOT IN('ADJUSTMENT','LEAD_ADJUSTMENT')
2300 -- yzhao 01/13/2006 fix bug 4939453 offer adjustment creates new list_line_id
2301 AND adjl.offer_adjustment_id = p_offer_adjustment_id
2302 AND util.object_type = 'TP_ORDER'
2303
2304 UNION -- for off invoice in direct sales
2305 SELECT util.utilization_id,
2306 util.object_type,
2307 util.object_id,
2308 util.order_line_id,
2309 util.product_id,
2310 util.billto_cust_account_id, -- yzhao: 11.5.10 added billto_cust_account_id
2311 util.cust_account_id,
2312 util.fund_id,
2313 util.currency_code,
2314 util.price_adjustment_id,
2315 --NULL ,
2316 DECODE (oe.arithmetic_operator,
2317 -- julou 03/30/2007 fix bug 5849584 "original discount = 0" causes "divisor is 0" exception
2318 'NEWPRICE', DECODE(oe.adjusted_amount_per_pqty, 0, (ol.unit_selling_price - adjl.modified_discount) * ol.pricing_quantity, ((oe.operand - adjl.modified_discount) * amount /-oe.adjusted_amount_per_pqty)),
2319 '%', DECODE(oe.operand, 0, adjl.modified_discount * ol.unit_selling_price * ol.pricing_quantity / 100, (adjl.modified_discount - oe.operand) * amount / oe.operand),
2320 'AMT', DECODE(oe.operand, 0, adjl.modified_discount * ol.pricing_quantity, (adjl.modified_discount - oe.operand) * amount / oe.operand),
2321 'LUMPSUM', DECODE(oe.operand, 0, adjl.modified_discount, (adjl.modified_discount - oe.operand) * amount / oe.operand)
2322 -- 'NEWPRICE', ((oe.operand - adjl.modified_discount) * amount /-oe.adjusted_amount_per_pqty),
2323 -- ((adjl.modified_discount_td - oe.operand) * amount / oe.operand)
2324 ) amount
2325 ,util.org_id
2326 ,util.ship_to_site_use_id
2327 ,util.bill_to_site_use_id
2328 ,util.reference_type
2329 ,util.reference_id
2330 FROM ozf_funds_utilized_all_b util,
2331 ozf_temp_eligibility temp,
2332 ozf_offer_adjustment_lines adjl,
2333 oe_order_lines_all ol,
2334 oe_price_adjustments oe
2335 WHERE util.plan_type = 'OFFR'
2336 AND product_id IS NOT NULL
2337 AND util.plan_id = p_qp_list_header_id
2338 AND util.product_id = DECODE (temp.eligibility_id, -1, util.product_id, temp.eligibility_id)
2339 AND temp.offer_adjustment_line_id = adjl.offer_adjustment_line_id
2340 -- kdass 31-MAR-2006 fix bug 5101720 offer adjustment creates new list_line_id
2341 -- AND adjl.list_line_id = oe.list_line_id
2342 AND oe.list_line_id IN (SELECT from_list_line_id
2343 FROM ozf_offer_adj_rltd_lines adjr
2344 START WITH adjr.from_list_line_id = adjl.list_line_id
2345 AND adjr.offer_adjustment_id = adjl.offer_adjustment_id
2346 CONNECT BY PRIOR adjr.from_list_line_id = adjr.to_list_line_id
2347 )
2348 AND adjl.offer_adjustment_id = p_offer_adjustment_id
2349 AND util.object_type = 'ORDER'
2350 AND util.price_adjustment_id = oe.price_adjustment_id
2351 AND adjustment_date BETWEEN p_from_date AND l_to_date
2352 AND util.utilization_type NOT IN('ADJUSTMENT','LEAD_ADJUSTMENT')
2353 AND ol.line_id = oe.line_id
2354
2355 UNION
2356 --for off invoice in third party accrual.
2357 SELECT util.utilization_id,
2358 util.object_type,
2359 util.object_id,
2360 util.order_line_id,
2361 util.product_id,
2362 util.billto_cust_account_id,
2363 util.cust_account_id,
2364 util.fund_id,
2365 util.currency_code,
2366 util.price_adjustment_id,
2367 --NULL,
2368 DECODE (oe.operand_calculation_code,
2369 'NEWPRICE', ((oe.operand - adjl.modified_discount) * amount /-oe.operand),
2370 ((adjl.modified_discount - oe.operand) * amount / oe.operand)
2371 ) amount
2372 ,util.org_id
2373 ,util.ship_to_site_use_id
2374 ,util.bill_to_site_use_id
2375 ,util.reference_type
2376 ,util.reference_id
2377 FROM ozf_funds_utilized_all_b util,
2378 ozf_temp_eligibility temp,
2379 ozf_offer_adjustment_lines adjl,
2380 OZF_RESALE_ADJUSTMENTS_ALL oe
2381 WHERE util.plan_type = 'OFFR'
2382 AND product_id IS NOT NULL
2383 AND util.plan_id = p_qp_list_header_id
2384 AND util.product_id = DECODE (temp.eligibility_id, -1, util.product_id, temp.eligibility_id)
2385 AND temp.offer_adjustment_line_id = adjl.offer_adjustment_line_id
2386 AND adjl.list_line_id_td = oe.list_line_id
2387 AND util.price_adjustment_id = oe.resale_adjustment_id
2388 AND adjustment_date BETWEEN p_from_date AND l_to_date
2389 AND util.utilization_type NOT IN('ADJUSTMENT','LEAD_ADJUSTMENT')
2390 -- yzhao 01/13/2006 fix bug 4939453 offer adjustment creates new list_line_id
2391 AND adjl.offer_adjustment_id = p_offer_adjustment_id
2392 AND util.object_type = 'TP_ORDER';
2393
2394
2395 CURSOR c_bdadj_order_value IS
2396 SELECT util.utilization_id,
2397 util.object_type,
2398 util.object_id,
2399 util.order_line_id,
2400 util.product_id,
2401 util.billto_cust_account_id, -- yzhao: 11.5.10 added billto_cust_account_id
2402 util.cust_account_id,
2403 util.fund_id,
2404 util.currency_code,
2405 util.price_adjustment_id,
2406 --NULL,
2407 DECODE(oe.operand, 0, adjl.modified_discount * ol.pricing_quantity, (adjl.modified_discount - oe.operand) * amount / oe.operand) amount
2408 ,util.org_id
2409 ,util.ship_to_site_use_id
2410 ,util.bill_to_site_use_id
2411 ,util.reference_type
2412 ,util.reference_id
2413 FROM ozf_funds_utilized_all_b util,
2414 ozf_offer_adjustment_lines adjl,
2415 oe_order_lines_all ol,
2416 oe_price_adjustments oe
2417 WHERE util.plan_type = 'OFFR'
2418 AND util.plan_id = p_qp_list_header_id
2419 -- kdass 31-MAR-2006 fix bug 5101720 offer adjustment creates new list_line_id
2420 -- AND adjl.list_line_id = oe.list_line_id
2421 AND oe.list_line_id IN (SELECT from_list_line_id
2422 FROM ozf_offer_adj_rltd_lines adjr
2423 START WITH adjr.from_list_line_id = adjl.list_line_id
2424 AND adjr.offer_adjustment_id = adjl.offer_adjustment_id
2425 CONNECT BY PRIOR adjr.from_list_line_id = adjr.to_list_line_id
2426 )
2427 AND adjl.offer_adjustment_id = p_offer_adjustment_id
2428 AND util.object_type = 'ORDER'
2429 AND util.price_adjustment_id = oe.price_adjustment_id
2430 AND adjustment_date BETWEEN p_from_date AND l_to_date
2431 AND oe.line_id = ol.line_id
2432 AND util.utilization_type NOT IN('ADJUSTMENT','LEAD_ADJUSTMENT');
2433
2434 CURSOR c_bdadj_promotion_value IS
2435 SELECT util.utilization_id,
2436 util.object_type,
2437 util.object_id,
2438 util.order_line_id,
2439 util.product_id,
2440 util.billto_cust_account_id,
2441 util.cust_account_id,
2442 util.fund_id,
2443 util.currency_code,
2444 util.price_adjustment_id,
2445 --NULL,
2446 DECODE (oe.arithmetic_operator,
2447 -- julou 03/30/2007 fix bug 5849584 "original discount = 0" causes "divisor is 0" exception
2448 'NEWPRICE', DECODE(oe.adjusted_amount_per_pqty, 0, (ol.unit_selling_price - adjl.modified_discount) * ol.pricing_quantity, ((oe.operand - adjl.modified_discount) * amount /-oe.adjusted_amount_per_pqty)),
2449 '%', DECODE(oe.operand, 0, adjl.modified_discount * ol.unit_selling_price * oe.range_break_quantity / 100, (adjl.modified_discount - oe.operand) * amount / oe.operand),
2450 'AMT', DECODE(oe.operand, 0, adjl.modified_discount * oe.range_break_quantity, (adjl.modified_discount - oe.operand) * amount / oe.operand),
2451 'LUMPSUM', DECODE(oe.operand, 0, adjl.modified_discount, (adjl.modified_discount - oe.operand) * amount / oe.operand)
2452 -- 'NEWPRICE', ((oe.operand - adjl.modified_discount) * amount /-oe.adjusted_amount_per_pqty),
2453 -- (adjl.modified_discount * oe.range_break_quantity - oe.operand * oe.range_break_quantity) * amount / (oe.operand *oe.range_break_quantity)
2454 ) amount
2455 ,util.org_id
2456 ,util.ship_to_site_use_id
2457 ,util.bill_to_site_use_id
2458 ,util.reference_type
2459 ,util.reference_id
2460 FROM ozf_funds_utilized_all_b util,
2461 ozf_temp_eligibility temp,
2462 ozf_offer_adjustment_lines adjl,
2463 oe_order_lines_all ol,
2464 oe_price_adjustments oe
2465 WHERE util.plan_type = 'OFFR'
2466 AND product_id IS NOT NULL
2467 AND util.plan_id = p_qp_list_header_id
2468 AND util.product_id = DECODE (temp.eligibility_id, -1, util.product_id, temp.eligibility_id)
2469 AND temp.offer_adjustment_line_id = adjl.offer_adjustment_line_id
2470 -- kdass 31-MAR-2006 fix bug 5101720 offer adjustment creates new list_line_id
2471 -- AND adjl.list_line_id = oe.list_line_id
2472 AND oe.list_line_id IN (SELECT from_list_line_id
2473 FROM ozf_offer_adj_rltd_lines adjr
2474 START WITH adjr.from_list_line_id = adjl.list_line_id
2475 AND adjr.offer_adjustment_id = adjl.offer_adjustment_id
2476 CONNECT BY PRIOR adjr.from_list_line_id = adjr.to_list_line_id
2477 )
2478 AND adjl.offer_adjustment_id = p_offer_adjustment_id
2479 AND util.object_type = 'ORDER'
2480 AND util.price_adjustment_id = oe.price_adjustment_id
2481 AND adjustment_date BETWEEN p_from_date AND l_to_date
2482 AND oe.line_id = ol.line_id
2483 AND util.utilization_type NOT IN('ADJUSTMENT','LEAD_ADJUSTMENT');
2484
2485 TYPE backdate_adj_rec_type IS RECORD
2486 (
2487 utilization_id NUMBER,
2488 object_type VARCHAR2(20),
2489 object_id NUMBER,
2490 order_line_id NUMBER,
2491 product_id NUMBER,
2492 billto_cust_account_id NUMBER,
2493 cust_account_id NUMBER,
2494 fund_id NUMBER,
2495 currency_code VARCHAR2(150),
2496 price_adjustment_id NUMBER,
2497 --volume_offer_tiers_id NUMBER,
2498 amount NUMBER := 0,
2499 org_id NUMBER
2500 ,ship_to_site_use_id NUMBER
2501 ,bill_to_site_use_id NUMBER
2502 ,reference_type VARCHAR2(20)
2503 ,reference_id NUMBER
2504 );
2505
2506 TYPE backdate_adj_rec_tbl IS TABLE OF backdate_adj_rec_type INDEX BY BINARY_INTEGER;
2507
2508 backdate_adj_rec backdate_adj_rec_tbl;
2509
2510 CURSOR c_offer_info IS
2511 SELECT nvl(transaction_currency_code,fund_request_curr_code) transaction_currency_code,
2512 reusable,
2513 offer_type
2514 FROM ozf_offers
2515 WHERE qp_list_header_id = p_qp_list_header_id;
2516
2517 -- added by feliu to fix bug 4451500 and 4015372.
2518 CURSOR c_adj_amount(p_utilization_id IN NUMBER) IS
2519 SELECT sum(amount) adj_amt
2520 FROM ozf_funds_utilized_all_b
2521 --12/16/2005 changed by Feng
2522 WHERE orig_utilization_id = p_utilization_id
2523 --WHERE price_adjustment_id = p_price_adj_id
2524 --AND fund_id = p_fund_id
2525 --AND utilization_type ='ADJUSTMENT'
2526 AND utilization_type IN ('ADJUSTMENT', 'LEAD_ADJUSTMENT')
2527 AND adjustment_type_id in(-4,-5,-1);
2528
2529 --Added for bugfix 6278466
2530 CURSOR c_org_id (p_utilization_id IN NUMBER) IS
2531 SELECT org_id
2532 FROM ozf_funds_utilized_all_b
2533 WHERE utilization_id = p_utilization_id;
2534
2535 l_offer_info c_offer_info%ROWTYPE;
2536 l_index NUMBER := 1;
2537 l_rate NUMBER;
2538 l_arithmetic_operator VARCHAR2 (30);
2539 l_adj_amt NUMBER;
2540
2541 BEGIN
2542 write_conc_log ( l_full_name
2543 || ' : '
2544 || 'Adjusting From Date '
2545 || p_from_date
2546 || 'Adjusting To Date'
2547 || l_to_date);
2548 SAVEPOINT perform_adjustment;
2549 x_return_status := fnd_api.g_ret_sts_success;
2550 IF G_DEBUG THEN
2551 ozf_utility_pvt.debug_message (': begin ');
2552 END IF;
2553 IF fnd_api.to_boolean (p_init_msg_list) THEN
2554 fnd_msg_pub.initialize;
2555 END IF;
2556 OPEN c_offer_info;
2557 FETCH c_offer_info INTO l_offer_info;
2558 CLOSE c_offer_info;
2559
2560 write_conc_log ( l_full_name
2561 || ' : '
2562 || 'Before Processing Product For Offer Adjustment Id '
2563 || p_offer_adjustment_id);
2564
2565 ----DBMS_OUTPUT.put_line ( 'Before Process Product' || p_offer_adjustment_id);
2566 --process_offer_product
2567 process_offer_product (p_offer_adjustment_id => p_offer_adjustment_id, x_return_status => l_return_status);
2568 write_conc_log ( l_full_name
2569 || ' : '
2570 || 'After Process Product Return Status'
2571 || l_return_status);
2572 ----DBMS_OUTPUT.put_line ( 'After Process Product' || l_return_status);
2573
2574 IF l_return_status = fnd_api.g_ret_sts_error THEN
2575 RAISE fnd_api.g_exc_error;
2576 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2577 RAISE fnd_api.g_exc_unexpected_error;
2578 END IF;
2579 --IF l_source_from_par_flag = 'N' THEN
2580
2581 -- updated by mkothari : 10-28-2002
2582 -- enhancement for 11.5.9: added BD adjustment for multi-tier accrual and off-invoice AND
2583 -- order_value(ORDER), trade deal(DEAL), promotional goods(OID) and volume offer(VOLUME_OFFER)
2584
2585 IF l_offer_info.offer_type = 'DEAL' THEN --(for 'TRADE DEAL' , use a different cursor)
2586 write_conc_log ('Processing TRADE DEAL offer ...');
2587 OPEN c_bdadj_trade_deal;
2588 ELSIF l_offer_info.offer_type = 'ORDER' THEN --(for 'ORDER VALUE' , use a different cursor)
2589 write_conc_log ('Processing ORDER VALUE offer ...');
2590 OPEN c_bdadj_order_value;
2591 ELSIF l_offer_info.offer_type = 'OID' THEN --(for 'promotional OFFER' , use a different cursor)
2592 write_conc_log ('Processing promotional OFFER offer ...');
2593 OPEN c_bdadj_promotion_value;
2594 ELSE
2595 write_conc_log ('Processing OID or ACCRUAL or OFF_INVOICE or Multi_Tier offer ...');
2596 OPEN c_bdadj_all_types;
2597 END IF;
2598
2599 LOOP
2600 IF l_offer_info.offer_type = 'DEAL' THEN
2601 FETCH c_bdadj_trade_deal BULK COLLECT INTO backdate_adj_rec LIMIT g_bulk_limit;
2602 ELSIF l_offer_info.offer_type = 'ORDER' THEN
2603 FETCH c_bdadj_order_value BULK COLLECT INTO backdate_adj_rec LIMIT g_bulk_limit;
2604 ELSIF l_offer_info.offer_type = 'OID' THEN
2605 FETCH c_bdadj_promotion_value BULK COLLECT INTO backdate_adj_rec LIMIT g_bulk_limit;
2606 ELSE
2607 FETCH c_bdadj_all_types BULK COLLECT INTO backdate_adj_rec LIMIT g_bulk_limit;
2608 END IF;
2609
2610 FOR i IN NVL(backdate_adj_rec.FIRST, 1) .. NVL(backdate_adj_rec.LAST, 0) LOOP
2611
2612 write_conc_log ( 'backdate_adj_rec.price_adjustment_id'
2613 || ' : ' || backdate_adj_rec(i).price_adjustment_id);
2614
2615 -- added by feliu on 06/30/2005 .
2616 --OPEN c_adj_amount (backdate_adj_rec(i).price_adjustment_id,backdate_adj_rec(i).fund_id);
2617 OPEN c_adj_amount (backdate_adj_rec(i).utilization_id);
2618 FETCH c_adj_amount INTO l_adj_amt;
2619 CLOSE c_adj_amount;
2620
2621 --Added for bugfix 6278466
2622 OPEN c_org_id(backdate_adj_rec(i).utilization_id);
2623 FETCH c_org_id INTO l_org_id;
2624 CLOSE c_org_id;
2625
2626 l_util_amount := ozf_utility_pvt.currround(backdate_adj_rec(i).amount,backdate_adj_rec(i).currency_code); -- in fund currency
2627
2628 -- new utilization amount minus existing utilization amount.
2629 l_util_amount := l_util_amount - NVL(l_adj_amt,0);
2630
2631 write_conc_log ( l_full_name
2632 || ' : '
2633 || 'Inside LOOP, Util Amount '
2634 || l_util_amount);
2635 --DBMS_OUTPUT.put_line ( 'In Backdated Adjustment Fund LOOP ' || l_util_amount);
2636
2637 IF l_util_amount <> 0 THEN
2638 l_act_budgets_rec := NULL;
2639 l_act_util_rec := NULL;
2640 l_act_budgets_rec.request_amount := l_util_amount;
2641 l_act_budgets_rec.approved_amount := l_act_budgets_rec.request_amount;
2642 l_act_budgets_rec.parent_src_apprvd_amt := l_util_amount;
2643 write_conc_log (l_full_name || ' : '
2644 || 'In Process Ozf_Act_budgets offer id'
2645 || p_qp_list_header_id);
2646 --dbms_output.put_line ( 'In Process Ozf_Act_budgets' || p_qp_list_header_id);
2647 l_act_budgets_rec.act_budget_used_by_id := p_qp_list_header_id;
2648 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
2649 l_act_budgets_rec.budget_source_type := 'OFFR';
2650 l_act_budgets_rec.budget_source_id := p_qp_list_header_id;
2651 --dbms_output.put_line ( 'offer currency code ' || l_offer_info.transaction_currency_code);
2652 l_act_budgets_rec.request_currency := l_offer_info.transaction_currency_code;
2653 l_act_budgets_rec.request_date := SYSDATE;
2654 l_act_budgets_rec.status_code := 'APPROVED';
2655 l_act_budgets_rec.user_status_id := ozf_Utility_Pvt.get_default_user_status (
2656 'OZF_BUDGETSOURCE_STATUS', l_act_budgets_rec.status_code);
2657 l_act_budgets_rec.transfer_type := 'UTILIZED';
2658 l_act_budgets_rec.approval_date := SYSDATE;
2659 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id (fnd_global.user_id);
2660 write_conc_log (l_full_name || ' : '
2661 || 'resourceid '
2662 || l_act_budgets_rec.approver_id);
2663 --dbms_output.put_line ( 'resourceid ' || l_act_budgets_rec.approver_id);
2664 l_act_budgets_rec.justification := fnd_message.get_string ('OZF', 'OZF_BACKDATE_AMOUNT_ADJUSTMENT');
2665 --dbms_output.put_line ( 'budget id '|| backdate_adj_rec(i).fund_id);
2666 --dbms_output.put_line ( 'budget curr ' || backdate_adj_rec(i).currency_code);
2667 l_act_budgets_rec.parent_source_id := backdate_adj_rec(i).fund_id;
2668 l_act_budgets_rec.parent_src_curr := backdate_adj_rec(i).currency_code;
2669 l_act_util_rec.utilization_type :='ADJUSTMENT';
2670
2671 IF l_util_amount > 0 THEN
2672 l_act_util_rec.adjustment_type :='STANDARD'; -- Seeded Data for Backdated Positive Adj
2673 l_act_util_rec.adjustment_type_id := -5; -- Seeded Data for Backdated Positive Adj
2674 ELSE
2675 l_act_util_rec.adjustment_type :='DECREASE_EARNED'; -- Seeded Data for Backdated Negative Adj
2676 l_act_util_rec.adjustment_type_id := -4; -- Seeded Data for Backdated Negative Adj
2677 END IF;
2678
2679 l_act_util_rec.product_level_type := 'PRODUCT';
2680 l_act_util_rec.product_id := backdate_adj_rec(i).product_id;
2681 -- yzhao: 02/23/2004 11.5.10 added billto_cust_account_id
2682 l_act_util_rec.billto_cust_account_id := backdate_adj_rec(i).billto_cust_account_id;
2683 l_act_util_rec.cust_account_id := backdate_adj_rec(i).cust_account_id;
2684 l_act_util_rec.org_id := l_org_id; -- Added for bugfix 6278466
2685 l_act_util_rec.price_adjustment_id := backdate_adj_rec(i).price_adjustment_id;
2686 --l_act_util_rec.volume_offer_tiers_id := backdate_adj_rec(i).volume_offer_tiers_id;
2687 l_act_util_rec.adjustment_date := SYSDATE;
2688 -- yzhao: 07/06/2004 11.5.10 populate order id, line id, original utilization id
2689 l_act_util_rec.object_type := backdate_adj_rec(i).object_type;
2690 l_act_util_rec.object_id := backdate_adj_rec(i).object_id;
2691 l_act_util_rec.order_line_id := backdate_adj_rec(i).order_line_id;
2692 l_act_util_rec.orig_utilization_id := backdate_adj_rec(i).utilization_id;
2693 l_act_util_rec.org_id := backdate_adj_rec(i).org_id;
2694 l_act_util_rec.ship_to_site_use_id := backdate_adj_rec(i).ship_to_site_use_id;
2695 l_act_util_rec.bill_to_site_use_id := backdate_adj_rec(i).bill_to_site_use_id;
2696 l_act_util_rec.reference_type := backdate_adj_rec(i).reference_type;
2697 l_act_util_rec.reference_id := backdate_adj_rec(i).reference_id;
2698 -- l_act_util_rec.gl_date := SYSDATE;
2699
2700 ozf_fund_adjustment_pvt.process_act_budgets(x_return_status => l_return_status
2701 ,x_msg_count => x_msg_count
2702 ,x_msg_data => x_msg_data
2703 ,p_act_budgets_rec => l_act_budgets_rec
2704 ,p_act_util_rec => l_act_util_rec
2705 ,x_act_budget_id => l_act_budget_id
2706 );
2707 END IF;
2708 write_conc_log (l_full_name || ' : '
2709 || 'Message :'
2710 || x_msg_data
2711 || 'Msg count'
2712 || x_msg_count
2713 || 'Return Status'
2714 || l_return_status
2715 );
2716 --DBMS_OUTPUT.put_line ( 'MESSAGE 11.5.9 (perform_adjustment) - BEGIN 2 :' || x_msg_data || 'msg count'|| x_msg_count || l_return_status);
2717 /* FOR_DEBUGGING
2718 -- IF l_return_status <> 'S' THEN
2719 IF(x_msg_count > 0)THEN
2720 FOR I IN 1 .. x_msg_count LOOP
2721 fnd_msg_pub.GET
2722 (p_msg_index => FND_MSG_PUB.G_NEXT,
2723 p_encoded => FND_API.G_FALSE,
2724 p_data => x_msg_data,
2725 p_msg_index_out => l_index);
2726 --ozf_utility_pvt.write_conc_log(l_full_name||' : '||i||x_msg_data);
2727 DBMS_OUTPUT.put_line('****(PA):'||x_msg_data);
2728 END LOOP;
2729 fnd_msg_pub.initialize;
2730 END IF;
2731 -- END IF;
2732 END FOR_DEBUGGING */
2733 --DBMS_OUTPUT.put_line ( 'MESSAGE 11.5.9 (perform_adjustment) - END 2 :' || x_msg_data || 'msg count'|| x_msg_count || l_return_status);
2734
2735 IF l_return_status = fnd_api.g_ret_sts_error THEN
2736 RAISE fnd_api.g_exc_error;
2737 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2738 RAISE fnd_api.g_exc_unexpected_error;
2739 END IF;
2740
2741 END LOOP; --FOR i IN NVL(backdate_adj_rec.FIRST, 1) .. NVL(backdate_adj_rec.LAST, 0) LOOP
2742
2743 IF l_offer_info.offer_type = 'DEAL' THEN
2744 EXIT WHEN c_bdadj_trade_deal%NOTFOUND;
2745 ELSIF l_offer_info.offer_type = 'ORDER' THEN
2746 EXIT WHEN c_bdadj_order_value%NOTFOUND;
2747 ELSIF l_offer_info.offer_type = 'OID' THEN
2748 EXIT WHEN c_bdadj_promotion_value%NOTFOUND;
2749 ELSE
2750 EXIT WHEN c_bdadj_all_types%NOTFOUND;
2751 END IF;
2752
2753 END LOOP;
2754
2755 IF l_offer_info.offer_type = 'DEAL' THEN
2756 CLOSE c_bdadj_trade_deal;
2757 ELSIF l_offer_info.offer_type = 'ORDER' THEN
2758 CLOSE c_bdadj_order_value;
2759 ELSIF l_offer_info.offer_type = 'OID' THEN
2760 CLOSE c_bdadj_promotion_value;
2761 ELSE
2762 CLOSE c_bdadj_all_types;
2763 END IF;
2764
2765 EXCEPTION
2766 WHEN fnd_api.g_exc_error THEN
2767 ROLLBACK TO perform_adjustment;
2768 x_return_status := fnd_api.g_ret_sts_error;
2769 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2770 WHEN fnd_api.g_exc_unexpected_error THEN
2771 ROLLBACK TO perform_adjustment;
2772 x_return_status := fnd_api.g_ret_sts_unexp_error;
2773 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2774 WHEN OTHERS THEN
2775 ROLLBACK TO perform_adjustment;
2776 x_return_status := fnd_api.g_ret_sts_unexp_error;
2777 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
2778 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2779 END IF;
2780 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
2781 END perform_adjustment;
2782
2783 -------------------------------------------------------------------
2784 -- NAME
2785 -- process_offer_product
2786 -- PURPOSE
2787 --
2788 -- History
2789 -- 4/18/2002 Mumu Pande Create.
2790 -- 05/09/2003 feliu use bind variable for dynamic sql.
2791 ----------------------------------------------------------------
2792 PROCEDURE process_offer_product (p_offer_adjustment_id IN NUMBER, x_return_status OUT NOCOPY VARCHAR2) IS
2793 l_adjustment_product_sql VARCHAR2 (32000) := NULL;
2794 l_temp_sql VARCHAR2 (32000) := NULL;
2795 l_return_status VARCHAR2 (20);
2796 l_msg_count NUMBER;
2797 l_msg_data VARCHAR2 (2000) := NULL;
2798 l_api_name VARCHAR2 (60) := 'process_offer_product';
2799 l_full_name VARCHAR2 (100) := g_pkg_name
2800 || '.process_offer_product';
2801 l_denorm_csr NUMBER;
2802 l_ignore NUMBER;
2803 l_stmt_denorm VARCHAR2(32000) := NULL;
2804
2805 -- get budget's product id and product family id
2806 CURSOR c_off_adj_lines IS
2807 SELECT offer_adjustment_line_id,
2808 qppa.product_attribute,
2809 qppa.product_attr_value
2810 FROM ozf_offer_adjustment_lines adjl, qp_pricing_attributes qppa
2811 WHERE adjl.offer_adjustment_id = p_offer_adjustment_id AND adjl.list_line_id = qppa.list_line_id;
2812 BEGIN
2813 -- ozf_utility_pvt.debug_message('enter validate_product_budget obj_id=' || p_object_id || ' budget_id=' || p_budget_id);
2814
2815 x_return_status := fnd_api.g_ret_sts_success;
2816 EXECUTE IMMEDIATE 'DELETE FROM ozf_temp_eligibility';
2817 write_conc_log ( l_full_name
2818 || ' : '
2819 || 'In Process Product '
2820 || p_offer_adjustment_id);
2821 ----DBMS_output.put_line ( 'In Process Product' || p_offer_adjustment_id);
2822 -- Get all product qualifiers for 'FUND'
2823 FOR product_rec IN c_off_adj_lines
2824 LOOP
2825
2826 FND_DSQL.init;
2827 FND_DSQL.add_text('INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id,offer_adjustment_line_id) ');
2828 FND_DSQL.add_text('(SELECT ''FUND'', ''N'', product_id,' );
2829 FND_DSQL.add_text(product_rec.offer_adjustment_line_id );
2830 FND_DSQL.add_text(' FROM ( ');
2831
2832 -- For ALL items do a special processing , we donot eant to populate ozf_temp_wligibility
2833 -- with all items but insert -1 instead
2834 ----DBMS_output.put_line ( 'product_rec.product_attribute ' || product_rec.product_attribute);
2835 ----DBMS_output.put_line ( 'product_rec.product_attr_value ' || product_rec.product_attr_value);
2836 IF product_rec.product_attribute = 'PRICING_ATTRIBUTE3' AND product_rec.product_attr_value = 'ALL' THEN
2837 --l_temp_sql := 'SELECT -1 product_id FROM DUAL';
2838 FND_DSQL.add_text('SELECT -1 product_id FROM DUAL');
2839 ----DBMS_output.put_line (' IN ALL products ');
2840 ELSE
2841 l_temp_sql := ozf_offr_elig_prod_denorm_pvt.get_sql (
2842 p_context=> 'ITEM',
2843 p_attribute=> product_rec.product_attribute,
2844 p_attr_value_from=> product_rec.product_attr_value, -- product_id
2845 -- p_attr_value_from=> 199,
2846 p_attr_value_to=> NULL,
2847 p_comparison=> NULL,
2848 p_type=> 'PROD'
2849 );
2850 END IF;
2851 FND_DSQL.add_text('))');
2852 write_conc_log ( l_full_name
2853 || ' : '
2854 || 'Get Sql Returns'
2855 || l_temp_sql);
2856 ----DBMS_output.put_line ( 'get sql returns' || l_temp_sql);
2857 /*
2858 l_adjustment_product_sql := 'INSERT INTO ozf_temp_eligibility(object_type, exclude_flag, eligibility_id,offer_adjustment_line_id) '
2859 || '(SELECT ''FUND'', ''N'', product_id,'
2860 || product_rec.offer_adjustment_line_id
2861 || ' FROM ( '
2862 || l_temp_sql
2863 || '))';
2864 */
2865
2866 l_denorm_csr := DBMS_SQL.open_cursor;
2867 FND_DSQL.set_cursor(l_denorm_csr);
2868 l_stmt_denorm := FND_DSQL.get_text(FALSE);
2869 --ozf_utility_pvt.debug_message('offer query: '|| l_stmt_denorm);
2870 write_conc_log ( l_full_name
2871 || ' : '
2872 || 'Insert Sql'
2873 || l_stmt_denorm);
2874 ----DBMS_output.put_line ( 'sql' || l_adjustment_product_sql);
2875 --EXECUTE IMMEDIATE l_adjustment_product_sql;
2876 DBMS_SQL.parse(l_denorm_csr, l_stmt_denorm, DBMS_SQL.native);
2877 FND_DSQL.do_binds;
2878 l_ignore := DBMS_SQL.execute(l_denorm_csr);
2879
2880 END LOOP;
2881 EXCEPTION
2882 WHEN OTHERS THEN
2883 x_return_status := fnd_api.g_ret_sts_error;
2884 END process_offer_product;
2885 ---------------------------------------------------------------------
2886 -- PROCEDURE
2887 -- PROCESS_ACCRUAL
2888 --
2889 -- PURPOSE
2890 --
2891 -- PARAMETERS
2892 -- p_earned_amt IN NUMBER -- in offer currency
2893 -- p_qp_list_header_id IN NUMBER -- Offer Id
2894 -- NOTES
2895 -- HISTORY
2896 -- 4/18/2002 Mumu Pande Create.
2897 ----------------------------------------------------------------------
2898 PROCEDURE process_accrual (
2899 p_earned_amt IN NUMBER,
2900 p_qp_list_header_id IN NUMBER,
2901 p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type,
2902 p_act_budgets_rec IN ozf_actbudgets_pvt.act_budgets_rec_type:= NULL,
2903 x_return_status OUT NOCOPY VARCHAR2,
2904 x_msg_count OUT NOCOPY NUMBER,
2905 x_msg_data OUT NOCOPY VARCHAR2);
2906
2907 ---------------------------------------------------------------------
2908 -- PROCEDURE
2909 -- ADJUST_VOLUME_OFFER
2910 --
2911 -- PURPOSE
2912 --
2913 -- PARAMETERS
2914 -- x_errbuf OUT NOCOPY VARCHAR2 STANDARD OUT NOCOPY PARAMETER
2915 -- x_retcode OUT NOCOPY NUMBER STANDARD OUT NOCOPY PARAMETER
2916 -- NOTES
2917 -- HISTORY
2918 -- 7/30/2002 Mumu Pande Create.
2919 -- 06/02/2005 Feliu rewrite to fix following issue:
2920 -- 1. First tier starts from 0.
2921 -- 2. The calculation for tier amount is based on unit selling price, not on unit list price.
2922 -- 3. Partial shipment for volume offer.
2923 -- 4. Returned order for volume offer.
2924 -- 5. Order quantity is over max tier amount or quantity.
2925
2926 ----------------------------------------------------------------------
2927
2928 PROCEDURE adjust_volume_offer(
2929 x_errbuf OUT NOCOPY VARCHAR2
2930 ,x_retcode OUT NOCOPY NUMBER
2931 ,p_debug IN VARCHAR2 := 'N')
2932 IS
2933
2934 CURSOR c_volume_off IS
2935 SELECT qp_list_header_id, volume_offer_type
2936 FROM ozf_offers
2937 WHERE offer_type = 'VOLUME_OFFER'
2938 AND status_code = 'ACTIVE';
2939
2940 l_api_name CONSTANT VARCHAR2(30) := 'adjust_volume_offer';
2941 l_full_name VARCHAr2(70):= g_pkg_name ||'.'||l_api_name ||' : ';
2942 l_api_version CONSTANT NUMBER := 1.0;
2943 l_return_status VARCHAR2 (20);
2944 l_msg_count NUMBER;
2945 l_msg_data VARCHAR2 (2000) := NULL;
2946
2947 TYPE qpListHeaderIdTbl IS TABLE OF ozf_offers.qp_list_header_id%TYPE;
2948 TYPE volumeOfferTypeTbl IS TABLE OF ozf_offers.volume_offer_type%TYPE;
2949
2950 l_qpListHeaderIdTbl qpListHeaderIdTbl;
2951 l_volumeOfferTypeTbl volumeOfferTypeTbl;
2952
2953 BEGIN
2954
2955 write_conc_log (' /*************************** DEBUG MESSAGE START *************************/' || l_api_name);
2956 SAVEPOINT adjust_volume_offer;
2957
2958 --Get All Active Volume Offer, volume_type
2959 OPEN c_volume_off;
2960 LOOP
2961 FETCH c_volume_off BULK COLLECT INTO l_qpListHeaderIdTbl, l_volumeOfferTypeTbl
2962 LIMIT g_bulk_limit;
2963
2964 FOR i IN NVL(l_qpListHeaderIdTbl.FIRST, 1) .. NVL(l_qpListHeaderIdTbl.LAST, 0) LOOP
2965
2966 FND_MSG_PUB.initialize;
2967 l_msg_count:= 0;
2968 l_msg_data := NULL;
2969 l_return_status := FND_API.g_ret_sts_success;
2970
2971 write_conc_log(l_full_name ||'VOLUME OFFER ID'|| l_qpListHeaderIdTbl(i));
2972
2973 BEGIN
2974 SAVEPOINT volume_offer;
2975
2976 volume_offer_adjustment(p_qp_list_header_id => l_qpListHeaderIdTbl(i)
2977 ,p_vol_off_type => l_volumeOfferTypeTbl(i)
2978 ,p_init_msg_list => fnd_api.g_false
2979 ,p_commit => fnd_api.g_false
2980 ,x_return_status => l_return_status
2981 ,x_msg_count => l_msg_count
2982 ,x_msg_data => l_msg_data
2983 );
2984
2985 write_conc_log(l_full_name ||'x_return_status'|| l_return_status);
2986
2987 IF l_return_status = fnd_api.g_ret_sts_error THEN
2988 RAISE fnd_api.g_exc_error;
2989 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
2990 RAISE fnd_api.g_exc_unexpected_error;
2991 END IF;
2992
2993 ----------------------------------------
2994 volume_offer_util_adjustment(p_qp_list_header_id => l_qpListHeaderIdTbl(i)
2995 ,x_return_status => l_return_status
2996 ,x_msg_count => l_msg_count
2997 ,x_msg_data => l_msg_data
2998 );
2999
3000 write_conc_log(l_full_name ||'x_return_status'|| l_return_status);
3001
3002 IF l_return_status = fnd_api.g_ret_sts_error THEN
3003 RAISE fnd_api.g_exc_error;
3004 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
3005 RAISE fnd_api.g_exc_unexpected_error;
3006 END IF;
3007 ----------------------------------------
3008
3009 EXCEPTION
3010 WHEN FND_API.G_EXC_ERROR THEN
3011 ROLLBACK TO VOLUME_OFFER;
3012 OZF_UTILITY_PVT.write_conc_log(l_full_name ||' Volume Offer Adjustment Failed EX ==>'||'VOLUME OFFER '|| l_qpListHeaderIdTbl(i));
3013 OZF_UTILITY_PVT.write_conc_log;
3014 OZF_UTILITY_PVT.write_conc_log(' ');
3015
3016 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3017 ROLLBACK TO VOLUME_OFFER;
3018 OZF_UTILITY_PVT.write_conc_log(l_full_name ||' Volume Offer Adjustment Failed UNEX ==>'||'VOLUME OFFER '|| l_qpListHeaderIdTbl(i));
3019 OZF_UTILITY_PVT.write_conc_log;
3020 OZF_UTILITY_PVT.write_conc_log(' ');
3021
3022 WHEN OTHERS THEN
3023 ROLLBACK TO VOLUME_OFFER;
3024 OZF_UTILITY_PVT.write_conc_log(l_full_name ||' Volume Offer Adjustment Failed OT ==>'||'VOLUME OFFER '|| l_qpListHeaderIdTbl(i));
3025 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3026 OZF_UTILITY_PVT.write_conc_log;
3027 OZF_UTILITY_PVT.write_conc_log(' ');
3028 END;
3029
3030 END LOOP; --FOR i IN NVL(l_qpListHeaderIdTbl.FIRST, 1) .. NVL(l_qpListHeaderIdTbl.LAST, 0) LOOP
3031
3032 EXIT WHEN c_volume_off%NOTFOUND;
3033
3034 END LOOP; -- end volume offer bulk fetch loop
3035
3036 write_conc_log (' /*************************** DEBUG MESSAGE END *************************/' || l_api_name );
3037 EXCEPTION
3038 WHEN fnd_api.g_exc_error THEN
3039 ROLLBACK TO adjust_volume_offer;
3040 OZF_UTILITY_PVT.write_conc_log;
3041 x_ERRBUF := l_msg_data;
3042 x_RETCODE := 1;
3043
3044 WHEN fnd_api.g_exc_unexpected_error THEN
3045 ROLLBACK TO adjust_volume_offer;
3046 OZF_UTILITY_PVT.write_conc_log;
3047 x_ERRBUF := l_msg_data;
3048 x_RETCODE := 1;
3049
3050 WHEN OTHERS THEN
3051 ROLLBACK TO adjust_volume_offer;
3052 OZF_UTILITY_PVT.write_conc_log;
3053 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3054 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3055 END IF;
3056 x_ERRBUF := l_msg_data;
3057 x_RETCODE := 1;
3058
3059 END adjust_volume_offer;
3060
3061 ---------------------------------------------------------------------
3062 -- PROCEDURE
3063 -- PROCESS_ACCRUAL
3064 --
3065 -- PURPOSE
3066 --
3067 -- PARAMETERS
3068 -- p_earned_amt IN NUMBER -- in offer currency
3069 -- p_qp_list_header_id IN NUMBER -- Offer Id
3070 -- NOTES
3071 -- HISTORY
3072 -- 7/31/2002 Mumu Pande Create.
3073 ----------------------------------------------------------------------
3074 PROCEDURE process_accrual (
3075 p_earned_amt IN NUMBER,
3076 p_qp_list_header_id IN NUMBER,
3077 p_act_util_rec IN ozf_actbudgets_pvt.act_util_rec_type,
3078 p_act_budgets_rec IN ozf_actbudgets_pvt.act_budgets_rec_type := NULL,
3079 x_return_status OUT NOCOPY VARCHAR2,
3080 x_msg_count OUT NOCOPY NUMBER,
3081 x_msg_data OUT NOCOPY VARCHAR2
3082 ) IS
3083 l_fund_amt_tbl ozf_accrual_engine.ozf_fund_amt_tbl_type;
3084 l_api_name VARCHAR2(30):= 'process_accrual';
3085 l_full_name VARCHAR2(60):= g_pkg_name ||'.'||l_api_name||' : ' ;
3086 l_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type := p_act_budgets_rec;
3087 l_act_util_rec ozf_actbudgets_pvt.act_util_rec_type := p_act_util_rec;
3088 l_earned_amount NUMBER;
3089 l_old_earned_amount NUMBER;
3090 l_header_id NUMBER; -- order or invoice id
3091 l_line_id NUMBER; -- order or invoice id
3092 l_remaining_amt NUMBER;
3093 l_count NUMBER := 1;
3094 l_rate NUMBER;
3095 l_util_curr VARCHAR2 (30);
3096 l_adj_amount NUMBER;
3097 l_converted_adj_amount NUMBER;
3098 j NUMBER; --loop counter
3099 l_off_name VARCHAR2(240);
3100 l_off_description VARCHAR2(2000);
3101 l_act_budget_id NUMBER;
3102 l_earned_amount NUMBER;
3103
3104 CURSOR c_offer_info (p_list_header_id IN NUMBER) IS
3105 ----- fix bug 5675871
3106 SELECT qp.description, qp.name ,nvl(ofr.transaction_currency_code, ofr.fund_request_curr_code)
3107 FROM qp_list_headers_vl qp, ozf_offers ofr
3108 WHERE qp.list_header_id = p_list_header_id
3109 AND qp.list_header_id = ofr.qp_list_header_id;
3110 /*
3111 SELECT description, name ,currency_code
3112 FROM qp_list_headers_vl
3113 WHERE list_header_id = p_list_header_id;
3114 */
3115 CURSOR c_adj_info (p_price_adj_id IN NUMBER,p_object_type VARCHAR2,p_order_line_id IN NUMBER) IS
3116 SELECT distinct billto_cust_account_id, cust_account_id,product_id,object_id,object_type,org_id
3117 ,ship_to_site_use_id,bill_to_site_use_id,exchange_rate_type --Added for bug 7030415
3118 FROM ozf_funds_utilized_all_b
3119 WHERE price_adjustment_id = p_price_adj_id
3120 AND object_type = p_object_type
3121 AND order_line_id = p_order_line_id;
3122
3123 CURSOR c_tp_adj_info (p_price_adj_id IN NUMBER,p_object_type VARCHAR2) IS
3124 SELECT distinct billto_cust_account_id, cust_account_id,product_id,object_id,object_type,org_id
3125 ,ship_to_site_use_id,bill_to_site_use_id,exchange_rate_type --Added for bug 7030415
3126 FROM ozf_funds_utilized_all_b
3127 WHERE price_adjustment_id = p_price_adj_id
3128 AND object_type = p_object_type;
3129
3130 -- Added for bug 7030415, cursor for currency conversion type.
3131 CURSOR c_get_conversion_type( p_org_id IN NUMBER) IS
3132 SELECT exchange_rate_type
3133 FROM ozf_sys_parameters_all
3134 WHERE org_id = p_org_id;
3135
3136 l_exchange_rate_type VARCHAR2(30) := FND_API.G_MISS_CHAR;
3137
3138
3139
3140 l_adj_info c_adj_info%ROWTYPE;
3141
3142 BEGIN
3143 x_return_status := fnd_api.g_ret_sts_success;
3144 SAVEPOINT process_accrual;
3145 IF G_DEBUG THEN
3146 ozf_utility_pvt.debug_message (' Start'|| g_pkg_name||'.'||l_api_name);
3147 END IF;
3148
3149 IF l_act_util_rec.object_type = 'TP_ORDER' THEN
3150 OPEN c_tp_adj_info (l_act_util_rec.price_adjustment_id,l_act_util_rec.object_type);
3151 FETCH c_tp_adj_info INTO l_adj_info;
3152 CLOSE c_tp_adj_info;
3153 ELSE
3154 OPEN c_adj_info (l_act_util_rec.price_adjustment_id,l_act_util_rec.object_type,l_act_util_rec.order_line_id);
3155 FETCH c_adj_info INTO l_adj_info;
3156 CLOSE c_adj_info;
3157 END IF;
3158
3159 ozf_accrual_engine.calculate_accrual_amount (
3160 x_return_status=> x_return_status,
3161 p_src_id=> p_qp_list_header_id,
3162 p_earned_amt=> p_earned_amt,
3163 -- yzhao: 02/23/2004 11.5.10 added following 3 parameters to return customer-product qualified budgets only
3164 -- if none budget qualifies, then post to all budgets
3165 p_cust_account_type => 'BILL_TO',
3166 p_cust_account_id => l_adj_info.billto_cust_account_id,
3167 p_product_item_id => l_adj_info.product_id,
3168 x_fund_amt_tbl=> l_fund_amt_tbl
3169 );
3170
3171 --dbms_output.put_line(' cal Status '||x_return_status);
3172 write_conc_log(l_full_name ||'Calculate Accrual Amt Return Status ' ||x_return_status);
3173 IF G_DEBUG THEN
3174 ozf_utility_pvt.debug_message (l_full_name ||'Return Status' ||x_return_status);
3175 END IF;
3176 -- fetch offer info
3177 OPEN c_offer_info ( p_qp_list_header_id);
3178 FETCH c_offer_info INTO l_off_description, l_off_name,l_util_curr ;
3179 CLOSE c_offer_info;
3180
3181 --- if this is not funded by a parent campaign or any budget the error out saying no budgte found
3182 IF l_fund_amt_tbl.COUNT = 0 OR x_return_status <> 'S' THEN
3183 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_error) THEN
3184 fnd_message.set_name ('OZF', 'OZF_FUND_NO_BUDGET_FOUND');
3185 fnd_message.set_token ('OFFER_NAME', l_off_name);
3186 fnd_msg_pub.ADD;
3187 END IF;
3188 --dbms_output.put_line(' In error ');
3189 IF x_return_status = fnd_api.g_ret_sts_error THEN
3190 RAISE fnd_api.g_exc_error;
3191 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3192 RAISE fnd_api.g_exc_unexpected_error;
3193 END IF;
3194 ELSE
3195 --if some row is returned to adjust then
3196 IF G_DEBUG THEN
3197 ozf_utility_pvt.debug_message ('Begin Processing For Offer Adjustment: '||l_off_name);
3198 END IF;
3199 IF (l_fund_amt_tbl.COUNT > 0) AND x_return_status = fnd_api.g_ret_sts_success THEN
3200 l_adj_amount := 0; -- in offer currency
3201 l_remaining_amt := ozf_utility_pvt.currround (
3202 p_earned_amt,
3203 l_util_curr ); -- in offer currency
3204
3205 <<earned_adj_loop>>
3206 FOR j IN l_fund_amt_tbl.FIRST .. l_fund_amt_tbl.LAST
3207 LOOP
3208 IF g_recal_flag = 'N' THEN
3209 IF l_fund_amt_tbl (j).earned_amount = 0 THEN
3210 IF G_DEBUG THEN
3211 ozf_utility_pvt.debug_message (' D: 0 earned amount' );
3212 END IF;
3213 GOTO l_endofearadjloop;
3214 END IF;
3215 END IF;
3216 IF ABS(l_remaining_amt) >= l_fund_amt_tbl (j).earned_amount THEN
3217 l_adj_amount := l_fund_amt_tbl (j).earned_amount; -- this is in offer and order currency
3218 ELSE
3219 l_adj_amount := l_remaining_amt;
3220 END IF;
3221 l_adj_amount := ozf_utility_pvt.currround (
3222 l_adj_amount,
3223 l_util_curr ); -- in offer currency
3224
3225 l_remaining_amt := l_remaining_amt - l_adj_amount;
3226 -- conver the adjustment amount from offer currency to fund currency
3227 --use l_adj_info
3228
3229
3230
3231 IF l_util_curr <> l_fund_amt_tbl (j).budget_currency THEN
3232 ozf_utility_pvt.convert_currency (
3233 x_return_status=> x_return_status,
3234 p_from_currency=> l_util_curr,
3235 p_to_currency=> l_fund_amt_tbl (j).budget_currency,
3236 p_conv_type=> l_adj_info.exchange_rate_type, --Added for bug 7030415
3237 p_from_amount=> l_adj_amount,
3238 x_to_amount=> l_converted_adj_amount,
3239 x_rate=> l_rate
3240 );
3241 END IF;
3242 IF G_DEBUG THEN
3243 ozf_utility_pvt.debug_message( ' Adj amount coverted '|| l_converted_adj_amount
3244 || ' l_adj amount' || l_adj_amount);
3245 END IF;
3246
3247 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3248 IF G_DEBUG THEN
3249 -- ozf_utility_pvt.error_message( ' Convert Currency '||x_return_status);
3250 ozf_utility_pvt.debug_message( ' Convert Currency '||x_return_status);
3251 END IF;
3252 IF x_return_status = fnd_api.g_ret_sts_error THEN
3253 RAISE fnd_api.g_exc_error;
3254 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3255 RAISE fnd_api.g_exc_unexpected_error;
3256 END IF;
3257 END IF;
3258 IF x_return_status = fnd_api.g_ret_sts_success THEN
3259 IF l_util_curr = l_fund_amt_tbl (j).budget_currency THEN
3260 l_act_budgets_rec.parent_src_apprvd_amt :=
3261 ozf_utility_pvt.currround (
3262 l_adj_amount,
3263 l_util_curr );
3264 ELSE
3265 IF G_DEBUG THEN
3266 ozf_utility_pvt.debug_message('in not equal currency');
3267 END IF;
3268 l_act_budgets_rec.parent_src_apprvd_amt :=
3269 ozf_utility_pvt.currround (
3270 l_converted_adj_amount,
3271 l_fund_amt_tbl (j).budget_currency );
3272 END IF;
3273
3274 l_act_util_rec.product_id := l_adj_info.product_id;
3275 l_act_util_rec.object_type := l_adj_info.object_type;
3276 l_act_util_rec.object_id := l_adj_info.object_id;
3277 l_act_util_rec.product_level_type := 'PRODUCT';
3278 -- yzhao: 11.5.10 02/23/2004 added billto_cust_account_id
3279 l_act_util_rec.billto_cust_account_id := l_adj_info.billto_cust_account_id;
3280 l_act_util_rec.cust_account_id := l_adj_info.cust_account_id;
3281 l_act_util_rec.utilization_type := 'ADJUSTMENT';
3282 l_act_util_rec.org_id := l_adj_info.org_id;
3283 l_act_util_rec.ship_to_site_use_id := l_adj_info.ship_to_site_use_id;
3284 l_act_util_rec.bill_to_site_use_id := l_adj_info.bill_to_site_use_id;
3285
3286 l_act_budgets_rec.justification := fnd_message.get_string ('OZF', 'OZF_ACR_VOL_BDADJ');
3287 l_act_budgets_rec.transfer_type := 'UTILIZED';
3288 l_act_budgets_rec.request_date := SYSDATE;
3289 l_act_budgets_rec.status_code := 'APPROVED';
3290 l_act_budgets_rec.user_status_id :=
3291 ozf_utility_pvt.get_default_user_status (
3292 'OZF_BUDGETSOURCE_STATUS',
3293 l_act_budgets_rec.status_code );
3294 l_act_budgets_rec.budget_source_type := 'OFFR';
3295 l_act_budgets_rec.budget_source_id := p_qp_list_header_id;
3296 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
3297 l_act_budgets_rec.act_budget_used_by_id := p_qp_list_header_id;
3298 l_act_budgets_rec.parent_src_curr := l_fund_amt_tbl (j).budget_currency;
3299 l_act_budgets_rec.parent_source_id := l_fund_amt_tbl (j).ofr_src_id;
3300 l_act_budgets_rec.request_amount :=
3301 ozf_utility_pvt.currround (l_adj_amount, l_util_curr);
3302 l_act_budgets_rec.request_currency := l_util_curr;
3303 l_act_budgets_rec.approved_amount := l_act_budgets_rec.request_amount;
3304 l_act_budgets_rec.approved_in_currency := l_util_curr;
3305
3306 IF l_adj_amount > 0 THEN
3307 l_act_util_rec.adjustment_type :='STANDARD'; -- Seeded Data for Backdated Positive Adj
3308 l_act_util_rec.adjustment_type_id := -7; -- Seeded Data for Backdated Positive Adj
3309 ELSE
3310 l_act_util_rec.adjustment_type :='DECREASE_EARNED'; -- Seeded Data for Backdated Negative Adj
3311 l_act_util_rec.adjustment_type_id := -6; -- Seeded Data for Backdated Negative Adj
3312 END IF;
3313
3314 IF l_act_budgets_rec.request_amount <> 0 THEN -- fix bug 4720113
3315 ozf_fund_Adjustment_pvt.process_Act_budgets(
3316 x_return_status=> x_return_status,
3317 x_msg_count=> x_msg_count,
3318 x_msg_data=> x_msg_data,
3319 p_act_budgets_rec=> l_act_budgets_rec,
3320 p_act_util_rec=> l_act_util_rec,
3321 x_act_budget_id=> l_act_budget_id
3322 );
3323 write_conc_log(l_full_name ||'Process Act Budget' ||x_return_status);
3324 IF G_DEBUG THEN
3325 ozf_utility_pvt.debug_message('create utlization '|| x_return_status);
3326 END IF;
3327
3328 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3329 IF x_return_status = fnd_api.g_ret_sts_error THEN
3330 RAISE fnd_api.g_exc_error;
3331 ELSIF x_return_status = fnd_api.g_ret_sts_unexp_error THEN
3332 RAISE fnd_api.g_exc_unexpected_error;
3333 END IF;
3334 END IF;
3335 END IF; --l_act_budgets_rec.request_amount <> 0
3336 --- quit when the total earned amount is adjusted
3337 END IF;
3338 <<l_endofearadjloop>>
3339 IF G_DEBUG THEN
3340 ozf_utility_pvt.debug_message ( 'l_remaining_amt ' || l_remaining_amt
3341 || 'l_adj amount' || l_adj_amount || 'fund_id '|| l_fund_amt_tbl (j).ofr_src_id );
3342 END IF;
3343 EXIT WHEN l_remaining_amt = 0;
3344 END LOOP earned_adj_loop;
3345 END IF; --end of check for table count >0
3346 END IF; -- end of check for count
3347 EXCEPTION
3348 WHEN fnd_api.g_exc_error THEN
3349 ROLLBACK TO process_accrual;
3350 x_return_status := fnd_api.g_ret_sts_error;
3351 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3352 WHEN fnd_api.g_exc_unexpected_error THEN
3353 ROLLBACK TO process_accrual;
3354 x_return_status := fnd_api.g_ret_sts_unexp_error;
3355 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3356 WHEN OTHERS THEN
3357 ROLLBACK TO process_accrual;
3358 x_return_status := fnd_api.g_ret_sts_unexp_error;
3359 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3360 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3361 END IF;
3362 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
3363
3364 END process_accrual;
3365
3366 ---------------------------------------------------------------------
3367 -- PROCEDURE
3368 --
3369 --
3370 -- PURPOSE
3371 --
3372 -- PARAMETERS
3373 -- p_from_date IN DATE
3374 -- p_qp_list_header_id IN NUMBER
3375 -- NOTES
3376 -- HISTORY
3377 -- 4/18/2002 Mumu Pande Create.
3378 ----------------------------------------------------------------------
3379 PROCEDURE process_claim_autopay (
3380 p_from_date IN DATE,
3381 p_qp_list_header_id IN NUMBER,
3382 x_return_status OUT NOCOPY VARCHAR2,
3383 x_msg_count OUT NOCOPY NUMBER,
3384 x_msg_data OUT NOCOPY VARCHAR2
3385 ) /*
3386 select product_id , cust_account_id , fund_id , sum(DECODE(adjl.arithmetic_operator,'AMOUNT', (adjl.modified_discount - adjl.original_discount),
3387 (( adjl.modified_discount - adjl.original_discount)* amount/adjl.original_discount))) AMount
3388 from ozf_funds_utilized_all_vl util , ozf_temp_eligibility temp,
3389 ozf_offer_adjustment_lines adjl
3390 where util.plan_type = 'OFFR'
3391 and product_id IS NOT NULL and util.plan_id = 7909
3392 and util.product_id = temp.eligibility_id
3393 and temp.offer_adjustment_line_id = adjl.offer_adjustment_line_id
3394 and adjustment_date BETWEEN from_date and to_date
3395 group by util.fund_id, util.product_id, util.fund_id,util.cust_account_id
3396 */ IS
3397 /******************Also pass the adjsutment_type_id */
3398 BEGIN
3399 NULL;
3400 /*
3401 get the last autppay date
3402 get the amount cursor from the from_date to the last autopay date
3403 Call claims Autopay API to settle the claim with proper date
3404 Perform Error Handling
3405 */
3406 END process_claim_autopay;
3407
3408
3409 ---------------------------------------------------------------------
3410 -- FUNCTION
3411 -- get_order_amount_quantity
3412 --
3413 -- PURPOSE -- Called from Offers UI
3414 --
3415 -- PARAMETERS
3416 -- p_list_header_id IN NUMBER,
3417 -- x_order_amount OUT NOCOPY NUMBER,
3418 -- NOTES
3419 -- HISTORY
3420 -- 10/18/2002 Mumu Pande Create.
3421 ----------------------------------------------------------------------
3422
3423 FUNCTION get_order_amount_quantity( p_list_header_id IN NUMBER
3424 )
3425 RETURN NUMBER
3426 IS
3427 l_new_discount NUMBER;
3428 l_new_operator VARCHAR2(30);
3429 l_old_discount NUMBER;
3430 l_old_operator VARCHAR2(30);
3431 l_return_status VARCHAR2(1);
3432 l_order_amount_quantity NUMBER;
3433 l_volume_type VARCHAR2(30);
3434
3435 BEGIN
3436
3437 l_order_amount_quantity:= get_order_amount_quantity(
3438 p_list_header_id => p_list_header_id ,
3439 x_order_amount_quantity => l_order_amount_quantity,
3440 x_new_discount =>l_new_discount,
3441 x_new_operator => l_new_operator,
3442 x_old_discount =>l_old_discount,
3443 x_old_operator =>l_old_operator,
3444 x_volume_type =>l_volume_type,
3445 x_return_status =>l_return_status
3446 );
3447 RETURN l_order_amount_quantity ;
3448 EXCEPTION
3449 WHEN OTHERS THEN
3450 NULL;
3451 RETURN 0;
3452 END;
3453
3454 ---------------------------------------------------------------------
3455 -- FUNCTION
3456 -- get_order_amount_quntity
3457 --
3458 -- PURPOSE
3459 --
3460 -- PARAMETERS
3461 -- p_list_header_id IN NUMBER,
3462 -- x_order_amount OUT NOCOPY NUMBER,
3463 -- x_new_discount OUT NOCOPY NUMBER,
3464 -- x_new_operator OUT NOCOPY VARCHAR2,
3465 -- x_old_discount OUT NOCOPY NUMBER,
3466 -- x_old_operator OUT NOCOPY VARCHAR2,
3467 -- x_return_status OUT NOCOPY VARCHAR2
3468 -- NOTES
3469 -- HISTORY
3470 -- 8/6/2002 Mumu Pande Create.
3471 -- 06/08/2005 feliu change cursor to handle case when g_order_gl_phase ='INVOICED';
3472 ----------------------------------------------------------------------
3473
3474 FUNCTION get_order_amount_quantity( p_list_header_id IN NUMBER,
3475 x_order_amount_quantity OUT NOCOPY NUMBER,
3476 x_new_discount OUT NOCOPY NUMBER,
3477 x_new_operator OUT NOCOPY VARCHAR2,
3478 x_old_discount OUT NOCOPY NUMBER,
3479 x_old_operator OUT NOCOPY VARCHAR2,
3480 x_volume_type OUT NOCOPY VARCHAR2,
3481 x_return_status OUT NOCOPY VARCHAR2
3482 ) RETURN NUMBER
3483 IS
3484 l_api_name VARCHAr2(30):= 'get_order_amount';
3485
3486 /*
3487 kdass 19-JUL-2004 Fix for 11.5.9 Bug 3742174
3488 Currently volume offer adjustment only considers shipped quantity. If total shipped quantity reaches new tier,
3489 the tier is adjusted. However for 'bill-only' order, there is no shipment involved, so shipped quantity is null,
3490 tier is never adjusted.
3491 To handle all possible scenarios,
3492 A) If profile 'OZF: Create GL Entries for Orders' is set to 'Shipped'
3493 1) if order line status is 'CLOSED' or 'INVOICED',
3494 a) if invoiced_quantity is not null, use invoiced_quantity
3495 b) else if shipped_quantity is not null, use shipped_quantity
3496 c) else, use nvl(ordered_quantity, 0)
3497 2) if order line status is 'SHIPPED',
3498 a) shipped_quantity is not null, use shipped_quantity
3499 b) else, use nvl(ordered_quantity, 0)
3500 B) A) If profile 'OZF: Create GL Entries for Orders' is set to 'Invoiced'
3501 1) if order line status is 'CLOSED' or 'INVOICED',
3502 a) if invoiced_quantity is not null, use invoiced_quantity
3503 b) else if shipped_quantity is not null, use shipped_quantity
3504 c) else, use nvl(ordered_quantity, 0)
3505 This cursor returns the order amount or order quantity depending on the value of the parameter p_amt_qty
3506 */
3507
3508 CURSOR c_order_amount_qty (p_list_header_id IN NUMBER, p_amt_qty IN VARCHAR2) IS
3509 --kdass 11-MAY-2005 Bug 4362575 changed unit_selling_price to unit_list_price
3510 SELECT SUM(DECODE(p_amt_qty, 'amt', line.unit_list_price, 1)*
3511 NVL(line.invoiced_quantity, NVL(line.shipped_quantity, NVL(line.ordered_quantity, 0)))
3512 ), header.transactional_curr_code
3513 FROM oe_order_lines_all line, oe_price_Adjustments adj, oe_order_headers_all header
3514 WHERE line.line_id = adj.line_id
3515 AND line.header_id = header.header_id
3516 AND line.header_id = adj.header_id
3517 AND adj.list_header_id = p_list_header_id
3518 AND adj.applied_flag = 'Y'
3519 AND line.cancelled_flag = 'N'
3520 AND line.booked_flag = 'Y'
3521 GROUP BY header.transactional_curr_code;
3522 --AND flow_status_code in ('CLOSED','INVOICED','SHIPPED');
3523
3524 -- For g_order_gl_phase ='INVOICED', only calculate order amount when flow_status_code = 'INVOICED'.
3525 -- and 'CLOSED').
3526 CURSOR c_invoice_amount_qty (p_list_header_id IN NUMBER, p_amt_qty IN VARCHAR2) IS
3527 --kdass 11-MAY-2005 Bug 4362575 changed unit_selling_price to unit_list_price
3528 SELECT SUM(DECODE(p_amt_qty, 'amt', line.unit_list_price, 1)*
3529 NVL(line.invoiced_quantity, NVL(line.shipped_quantity, NVL(line.ordered_quantity, 0)))
3530 ), header.transactional_curr_code
3531 FROM oe_order_lines_all line, oe_price_Adjustments adj, oe_order_headers_all header
3532 WHERE line.line_id = adj.line_id
3533 AND line.header_id = header.header_id
3534 AND line.header_id = adj.header_id
3535 AND adj.list_header_id = p_list_header_id
3536 AND adj.applied_flag = 'Y'
3537 AND line.cancelled_flag = 'N'
3538 AND line.booked_flag = 'Y'
3539 AND line.flow_status_code in ('CLOSED','INVOICED')
3540 GROUP BY header.transactional_curr_code;
3541
3542 /* remove it since we need to query tier in calling procedure. by feliu on 06/08/2005.
3543 CURSOR c_current_discount (p_list_header_id IN NUMBER,
3544 p_order_amount IN NUMBER) IS
3545 SELECT discount,
3546 discount_type_code
3547 FROM ozf_volume_offer_tiers
3548 WHERE p_order_amount BETWEEN
3549 tier_value_from AND tier_value_to
3550 AND qp_list_header_id = p_list_header_id;
3551 */
3552 CURSOR c_old_discount (p_list_header_id IN NUMBER) IS
3553 SELECT distinct operand,
3554 arithmetic_operator
3555 FROM qp_modifier_summary_v qp
3556 WHERE list_header_id = p_list_header_id;
3557
3558 CURSOR c_volume_type (p_list_header_id IN NUMBER) IS
3559 SELECT distinct volume_type
3560 FROM ozf_volume_offer_tiers tier
3561 WHERE qp_list_header_id = p_list_header_id;
3562
3563 CURSOR c_offer_curr IS
3564 SELECT nvl(transaction_currency_code,fund_request_curr_code)
3565 FROM ozf_offers
3566 WHERE qp_list_header_id = p_list_header_id;
3567
3568 l_volume_type VARCHAR2(30);
3569 l_offer_curr VARCHAR2(30);
3570 l_order_curr VARCHAR2(30);
3571 l_conv_amount NUMBER;
3572 l_rate NUMBER;
3573
3574 --profile: 'OZF: Create GL Entries for Orders' -- change as global constant by feliu on 06/21/2005
3575 -- l_order_gl_phase CONSTANT VARCHAR2 (15) := NVL(fnd_profile.VALUE ('OZF_ORDER_GLPOST_PHASE'), 'SHIPPED');
3576
3577 BEGIN
3578 x_return_status := fnd_api.g_ret_sts_success;
3579
3580 OPEN c_volume_type(p_list_header_id);
3581 FETCH c_volume_type INTO l_volume_type;
3582 CLOSE c_volume_type;
3583 x_volume_type := l_volume_type;
3584
3585 OPEN c_offer_curr;
3586 FETCH c_offer_curr INTO l_offer_curr;
3587 CLOSE c_offer_curr;
3588
3589 -- pricing_attr12 = AMOUNT
3590 IF l_volume_type = 'PRICING_ATTRIBUTE12' THEN
3591 IF g_order_gl_phase ='SHIPPED' THEN
3592 OPEN c_order_amount_qty(p_list_header_id, 'amt');
3593 FETCH c_order_amount_qty INTO x_order_amount_quantity, l_order_curr;
3594 CLOSE c_order_amount_qty;
3595 ELSE
3596 OPEN c_invoice_amount_qty(p_list_header_id, 'amt');
3597 FETCH c_invoice_amount_qty INTO x_order_amount_quantity, l_order_curr;
3598 CLOSE c_invoice_amount_qty;
3599 END IF;
3600
3601 --kdass 31-MAR-2006 bug 5101720 convert from order currency to offer currency
3602 IF l_offer_curr <> l_order_curr THEN
3603
3604 ozf_utility_pvt.write_conc_log('order curr: ' || l_order_curr);
3605 ozf_utility_pvt.write_conc_log('offer curr: ' || l_offer_curr);
3606 ozf_utility_pvt.write_conc_log('order amount: ' || x_order_amount_quantity);
3607
3608 ozf_utility_pvt.convert_currency (x_return_status => x_return_status
3609 ,p_from_currency => l_order_curr
3610 ,p_to_currency => l_offer_curr
3611 ,p_from_amount => x_order_amount_quantity
3612 ,x_to_amount => l_conv_amount
3613 ,x_rate => l_rate
3614 );
3615
3616 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3617 ozf_utility_pvt.write_conc_log('x_return_status: ' || x_return_status);
3618 RETURN NULL;
3619 END IF;
3620
3621 x_order_amount_quantity := l_conv_amount;
3622 write_conc_log ('order amount after currency conversion: ' || x_order_amount_quantity);
3623
3624 END IF;
3625
3626 ELSE -- quantity
3627 IF g_order_gl_phase ='SHIPPED' THEN
3628 OPEN c_order_amount_qty(p_list_header_id, 'qty');
3629 FETCH c_order_amount_qty INTO x_order_amount_quantity, l_order_curr;
3630 CLOSE c_order_amount_qty;
3631 ELSE
3632 OPEN c_invoice_amount_qty(p_list_header_id, 'qty');
3633 FETCH c_invoice_amount_qty INTO x_order_amount_quantity, l_order_curr;
3634 CLOSE c_invoice_amount_qty;
3635 END IF;
3636 END IF;
3637
3638 /*
3639 OPEN c_current_discount(p_list_header_id,x_order_amount_quantity);
3640 FETCH c_current_discount INTO x_new_discount,x_new_operator;
3641 CLOSE c_current_discount;
3642 */
3643 --Get the existing Tier % or amount value executing in QP x2 .
3644 OPEN c_old_discount(p_list_header_id);
3645 FETCH c_old_discount INTO x_old_discount,x_old_operator;
3646 CLOSE c_old_discount;
3647
3648 RETURN x_order_amount_quantity;
3649
3650
3651 EXCEPTION
3652 WHEN OTHERS THEN
3653 x_return_status := fnd_api.g_ret_sts_error;
3654 RETURN NULL;
3655 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
3656 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3657 END IF;
3658 END ;
3659
3660
3661 ---------------------------------------------------------------------
3662 -- PROCEDURE
3663 -- volume_offer_adjustment
3664 --
3665 -- PURPOSE
3666 -- adjustment for volume offer.
3667
3668 -- PARAMETERS
3669 -- p_qp_list_header_id IN NUMBER
3670 -- p_offer_adjustment_id IN NUMBER,
3671 -- p_retroactive IN VARCHAR2,
3672 -- p_vol_off_type IN VARCHAR2
3673
3674 -- NOTES
3675 -- HISTORY
3676 -- 6/10/2005 feliu Create.
3677 -- for backdated adjustment, only make volume adjustment for these orders after effective date.
3678 ----------------------------------------------------------------------
3679
3680 PROCEDURE volume_offer_adjustment (
3681 p_qp_list_header_id IN NUMBER,
3682 p_vol_off_type IN VARCHAR2,
3683 p_init_msg_list IN VARCHAR2 := fnd_api.g_false,
3684 p_commit IN VARCHAR2 := fnd_api.g_false,
3685 x_return_status OUT NOCOPY VARCHAR2,
3686 x_msg_count OUT NOCOPY NUMBER,
3687 x_msg_data OUT NOCOPY VARCHAR2
3688 ) IS
3689
3690 CURSOR c_old_price_Adj(p_list_header_id IN NUMBER) IS
3691 SELECT old_Adj_amt,order_line_id, price_adjustment_id,gl_date,object_type
3692 ,object_id, gl_posted_flag, utilization_id FROM
3693 ( SELECT sum(plan_curr_amount) old_Adj_amt
3694 , order_line_id
3695 ,min(price_adjustment_id) price_adjustment_id
3696 ,min(gl_date) gl_date
3697 ,object_type
3698 ,object_id
3699 ,'Y' gl_posted_flag
3700 ,min(utilization_id) utilization_id
3701 FROM ozf_funds_utilized_all_b
3702 WHERE plan_id = p_list_header_id
3703 AND plan_type = 'OFFR'
3704 -- AND gl_date is not NULL -- only process shipped or invoiced order.
3705 AND gl_posted_flag IN('Y','F')
3706 AND utilization_type IN ( 'ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
3707 AND price_adjustment_id IS NOT NULL
3708 GROUP BY order_line_id,object_type,object_id
3709 UNION ALL
3710 SELECT sum(plan_curr_amount) old_Adj_amt
3711 , order_line_id
3712 ,min(price_adjustment_id) price_adjustment_id
3713 ,min(gl_date) gl_date
3714 ,object_type
3715 ,object_id
3716 ,'X' gl_posted_flag
3717 ,min(utilization_id) utilization_id
3718 FROM ozf_funds_utilized_all_b
3719 WHERE plan_id = p_list_header_id
3720 AND plan_type = 'OFFR'
3721 AND gl_posted_flag = 'X'
3722 AND utilization_type IN ('SALES_ACCRUAL','ADJUSTMENT','ACCRUAL')
3723 AND price_adjustment_id IS NOT NULL
3724 GROUP BY order_line_id,object_type,object_id
3725 UNION ALL
3726 SELECT sum(plan_curr_amount) old_Adj_amt
3727 , order_line_id
3728 ,min(price_adjustment_id) price_adjustment_id
3729 ,min(gl_date) gl_date
3730 ,object_type
3731 ,object_id
3732 ,NULL gl_posted_flag
3733 ,min(utilization_id) utilization_id
3734 FROM ozf_funds_utilized_all_b
3735 WHERE plan_id = p_list_header_id
3736 AND plan_type = 'OFFR'
3737 AND gl_posted_flag IS NULL
3738 AND utilization_type IN ('UTILIZED','ADJUSTMENT')
3739 AND price_adjustment_id IS NOT NULL
3740 GROUP BY order_line_id,object_type,object_id)
3741 ORDER BY gl_date;
3742
3743 CURSOR c_order_line_info(p_order_line_id IN NUMBER) IS
3744 SELECT DECODE(line.line_category_code,'ORDER',line.ordered_quantity,
3745 'RETURN', -line.ordered_quantity) ordered_quantity,
3746 DECODE(line.line_category_code,'ORDER',NVL(line.shipped_quantity,line.ordered_quantity),
3747 'RETURN', line.invoiced_quantity,
3748 line.ordered_quantity) shipped_quantity,
3749 line.invoiced_quantity,
3750 line.unit_list_price,
3751 line.line_id,
3752 line.actual_shipment_date,
3753 line.fulfillment_date, -- invoiced date ?????
3754 line.inventory_item_id,
3755 header.transactional_curr_code
3756 FROM oe_order_lines_all line, oe_order_headers_all header
3757 WHERE line.line_id = p_order_line_id
3758 AND line.header_id = header.header_id;
3759
3760
3761 CURSOR c_resale_line_info(p_resale_line_id IN NUMBER, p_adj_id IN NUMBER) IS
3762 /* SELECT quantity ordered_quantity ,
3763 quantity shipped_quantity,
3764 quantity invoiced_quantity,
3765 purchase_price unit_list_price,
3766 resale_line_id line_id,
3767 NVL(date_shipped, date_ordered) actual_shipment_date,
3768 NVL(date_shipped, date_ordered) fulfillment_date, -- invoiced date ?????
3769 inventory_item_id,
3770 currency_code --dummy column
3771 FROM OZF_RESALE_LINES_ALL
3772 WHERE resale_line_id = p_resale_line_id;
3773 */
3774 ----- fix bug 5671169
3775 SELECT line.quantity ordered_quantity ,
3776 line.quantity shipped_quantity,
3777 line.quantity invoiced_quantity,
3778 adj.priced_unit_price unit_list_price,
3779 line.resale_line_id line_id,
3780 NVL(line.date_shipped, line.date_ordered) actual_shipment_date,
3781 NVL(line.date_shipped, line.date_ordered) fulfillment_date, -- invoiced date ?????
3782 line.inventory_item_id,
3783 line.currency_code --dummy column
3784 FROM OZF_RESALE_LINES_ALL line,ozf_resale_adjustments_all adj
3785 WHERE line.resale_line_id = p_resale_line_id
3786 AND adj.resale_adjustment_id = p_adj_id
3787 AND line.resale_line_id = adj.resale_line_id;
3788
3789
3790 CURSOR c_prior_tiers(p_parent_discount_id IN NUMBER, p_volume IN NUMBER ) IS
3791 SELECT offer_discount_line_id ,volume_from ,volume_to, discount
3792 FROM ozf_offer_discount_lines
3793 WHERE parent_discount_line_id = p_parent_discount_id
3794 AND p_volume >= volume_from
3795 ORDER BY volume_from DESC;
3796
3797
3798 CURSOR c_discount_header(p_discount_line_id IN NUMBER) IS
3799 SELECT discount_type,volume_type
3800 FROM ozf_offer_discount_lines
3801 WHERE offer_discount_line_id = p_discount_line_id
3802 AND tier_type = 'PBH';
3803
3804 CURSOR c_get_group(p_order_line_id IN NUMBER,p_list_header_id IN NUMBER) IS
3805 SELECT group_no,pbh_line_id,include_volume_flag
3806 FROM ozf_order_group_prod
3807 WHERE order_line_id = p_order_line_id
3808 AND qp_list_header_id = p_list_header_id;
3809
3810 CURSOR c_market_option(p_list_header_id IN NUMBER, p_group_id IN NUMBER) IS
3811 SELECT opt.retroactive_flag
3812 FROM ozf_offr_market_options opt
3813 WHERE opt.GROUP_NUMBER= p_group_id
3814 AND opt.qp_list_header_id = p_list_header_id;
3815
3816 --fix for bug 5975203
3817 CURSOR c_current_discount(p_volume IN NUMBER, p_parent_discount_id IN NUMBER) IS
3818 SELECT discount
3819 FROM ozf_offer_discount_lines
3820 WHERE p_volume > volume_from
3821 AND p_volume <= volume_to
3822 AND parent_discount_line_id = p_parent_discount_id;
3823
3824 /* CURSOR c_max_volume(p_order_line_id IN NUMBER, p_qp_list_header_id IN NUMBER,p_source_code IN VARCHAR2) IS
3825 SELECT summ.individual_volume
3826 FROM ozf_volume_detail det,ozf_volume_summary summ
3827 WHERE det.order_line_id = p_order_line_id
3828 AND det.qp_list_header_id = p_qp_list_header_id
3829 AND det.volume_track_type = summ.individual_type
3830 AND det.qp_list_header_id = summ.qp_list_header_id
3831 AND det.source_code = p_source_code;
3832 */
3833 CURSOR c_preset_tier(p_pbh_line_id IN NUMBER, p_qp_list_header_id IN NUMBER,p_group_id IN NUMBER) IS
3834 SELECT a.discount
3835 FROM ozf_offer_discount_lines a, ozf_market_preset_tiers b, ozf_offr_market_options c
3836 WHERE a.offer_discount_line_id = b.dis_offer_discount_id
3837 AND b.pbh_offer_discount_id = p_pbh_line_id
3838 AND b.offer_market_option_id = c.offer_market_option_id
3839 AND c.qp_list_header_id = p_qp_list_header_id
3840 AND c.group_number = p_group_id;
3841
3842 CURSOR c_sales_accrual(p_list_header_id IN NUMBER) IS
3843 SELECT 'X' from ozf_funds_all_b
3844 WHERE plan_id= p_list_header_id
3845 AND accrual_basis = 'SALES'
3846 UNION
3847 SELECT 'X' from ozf_funds_all_b
3848 WHERE plan_id = p_list_header_id
3849 AND accrual_basis = 'CUSTOMER'
3850 AND liability_flag = 'N';
3851
3852 CURSOR c_unit_discount(p_order_line_id IN NUMBER, p_price_adjust_id NUMBER) IS
3853 SELECT SUM(adjusted_amount_per_pqty)
3854 FROM oe_price_adjustments
3855 WHERE line_id = p_order_line_id
3856 AND accrual_flag = 'N'
3857 AND applied_flag = 'Y'
3858 AND list_line_type_code IN ('DIS', 'SUR', 'PBH', 'FREIGHT_CHARGE')
3859 and pricing_group_sequence <
3860 (SELECT pricing_group_sequence FROM oe_price_adjustments
3861 WHERE price_Adjustment_id = p_price_adjust_id) ;
3862
3863 CURSOR c_discount(p_order_line_id IN NUMBER, p_price_adjust_id NUMBER) IS
3864 SELECT SUM(adjusted_amount_per_pqty)
3865 FROM oe_price_adjustments
3866 WHERE line_id = p_order_line_id
3867 AND accrual_flag = 'N'
3868 AND applied_flag = 'Y'
3869 AND list_line_type_code IN ('DIS', 'SUR', 'PBH', 'FREIGHT_CHARGE');
3870
3871 CURSOR c_get_tier_limits (p_parent_discount_id IN NUMBER) IS
3872 SELECT MIN(volume_from),MAX(volume_to)
3873 FROM ozf_offer_discount_lines
3874 WHERE parent_discount_line_id = p_parent_discount_id;
3875
3876 CURSOR c_get_max_tier (p_max_volume_to IN NUMBER,p_parent_discount_id IN NUMBER) IS
3877 SELECT discount
3878 FROM ozf_offer_discount_lines
3879 WHERE volume_to =p_max_volume_to
3880 AND parent_discount_line_id = p_parent_discount_id;
3881
3882 CURSOR c_offer_curr IS
3883 SELECT nvl(transaction_currency_code,fund_request_curr_code), offer_id
3884 FROM ozf_offers
3885 WHERE qp_list_header_id = p_qp_list_header_id;
3886
3887 --22-FEB-2007 kdass bug 5759350 - changed datatype of p_product_id from NUMBER to VARCHAR2 based on Feng's suggestion
3888 --fix for bug 5979971
3889 CURSOR c_apply_discount(p_offer_id IN NUMBER,p_line_id IN NUMBER) IS
3890 SELECT NVL(apply_discount_flag,'N')
3891 FROM ozf_order_group_prod
3892 WHERE offer_id = p_offer_id
3893 AND order_line_id = p_line_id;
3894
3895
3896 l_api_name CONSTANT VARCHAR2(30) := 'volume_offer_adjustment';
3897 l_full_name VARCHAR2(70):= g_pkg_name ||'.'||l_api_name ||' : ';
3898 l_api_version CONSTANT NUMBER := 1.0;
3899 l_return_status VARCHAR2 (20) := fnd_api.g_ret_sts_success;
3900 l_msg_count NUMBER;
3901 l_msg_data VARCHAR2 (2000) := NULL;
3902 l_volume_offer_tier_id NUMBER;
3903 l_current_offer_tier_id NUMBER;
3904 l_order_amount NUMBER;
3905 l_old_discount NUMBER;
3906 l_new_discount NUMBER;--
3907 l_new_operator VARCHAR2(30);
3908 l_old_operator VARCHAR2(30);
3909 y1 NUMBER; -- Initial Adjsutment
3910 l_current_max_tier NUMBER;
3911 l_current_min_tier NUMBER;
3912 l_act_util_rec ozf_actbudgets_pvt.act_util_rec_type ;
3913 l_adj_amount NUMBER;
3914 l_volume_type VARCHAR2(30);
3915 l_current_tier_value NUMBER;
3916 l_total NUMBER;
3917 l_value NUMBER;
3918 l_previous_tier_max NUMBER;
3919 l_new_utilization NUMBER;
3920 l_total_order NUMBER;
3921 l_total_amount NUMBER;
3922 l_returned_flag BOOLEAN := false;
3923 l_qp_list_header_id NUMBER := p_qp_list_header_id;
3924 l_retroactive VARCHAR2(1) ;
3925 l_trx_date DATE;
3926 l_volume NUMBER;
3927 l_group_id NUMBER;
3928 l_pbh_line_id NUMBER;
3929 l_discount_type VARCHAR2(30);
3930 l_source_code VARCHAR2(30);
3931 l_preset_tier NUMBER;
3932 l_order_line_info c_order_line_info%ROWTYPE;
3933 l_order_line_id NUMBER;
3934 l_order_type VARCHAR2(30);
3935 l_sales_accrual_flag VARCHAR2 (3);
3936 l_volume_offer_type VARCHAR2(30) := p_vol_off_type;
3937 l_selling_price NUMBER;
3938 l_unit_discount NUMBER;
3939 l_min_tier NUMBER;
3940 l_max_tier NUMBER;
3941 l_offer_curr VARCHAR2(30);
3942 l_conv_price NUMBER;
3943 l_rate NUMBER;
3944 l_included_vol_flag VARCHAR2(1);
3945 l_amountTbl amountTbl ;
3946 l_glDateTbl glDateTbl ;
3947 l_objectTypeTbl objectTypeTbl ;
3948 l_objectIdTbl objectIdTbl;
3949 l_priceAdjustmentIDTbl priceAdjustmentIDTbl ;
3950 l_glPostedFlagTbl glPostedFlagTbl;
3951 l_orderLineIdTbl orderLineIdTbl;
3952
3953
3954 l_offer_id NUMBER;
3955 l_apply_discount VARCHAR2(1) ;
3956
3957 -- julou bug 6348078. cursor to get transaction_date for IDSM line.
3958 CURSOR c_trx_date(p_line_id NUMBER) IS
3959 SELECT transaction_date
3960 FROM ozf_sales_transactions
3961 WHERE source_code = 'IS'
3962 AND line_id = p_line_id;
3963
3964 --Added for bug 7030415
3965 l_utilizationIdTbl utilizationIdTbl;
3966 CURSOR c_utilization_details(l_utilization_id IN NUMBER) IS
3967 SELECT exchange_rate_type, org_id
3968 FROM ozf_funds_utilized_all_b
3969 WHERE utilization_id=l_utilization_id;
3970
3971 l_conv_type ozf_funds_utilized_all_b.exchange_rate_type%TYPE;
3972 l_org_id NUMBER;
3973
3974
3975
3976 BEGIN
3977 IF G_DEBUG THEN
3978 ozf_utility_pvt.debug_message(' /*************************** DEBUG MESSAGE START *************************/' || l_api_name);
3979 END IF;
3980 write_conc_log(' /*************************** DEBUG MESSAGE START *************************/' || l_api_name);
3981
3982 SAVEPOINT volume_offer_adjustment;
3983
3984 IF g_offer_id_tbl.FIRST IS NOT NULL THEN
3985 FOR i IN g_offer_id_tbl.FIRST .. g_offer_id_tbl.LAST
3986 LOOP
3987 IF g_offer_id_tbl(i) = l_qp_list_header_id THEN
3988 write_conc_log (' no adjustment for offer: ' || l_qp_list_header_id);
3989 GOTO l_endoffloop;
3990 END IF;
3991 END LOOP;
3992 END IF;
3993
3994 OPEN c_sales_accrual(l_qp_list_header_id);
3995 FETCH c_sales_accrual INTO l_sales_accrual_flag;
3996 CLOSE c_sales_accrual;
3997
3998 IF l_sales_accrual_flag is NOT NULL THEN
3999 l_order_type := 'SHIPPED'; --'BOOKED'; -- set to shipped for sales accrual untill decision has been made.
4000 ELSIF g_order_gl_phase ='SHIPPED' AND l_volume_offer_type = 'ACCRUAL' THEN
4001 l_order_type := 'SHIPPED';
4002 ELSIF g_order_gl_phase ='INVOICED' AND l_volume_offer_type = 'ACCRUAL' THEN
4003 l_order_type := 'INVOICED';
4004 ELSIF l_volume_offer_type = 'OFF_INVOICE' THEN
4005 l_order_type := 'INVOICED';
4006 END IF;
4007
4008 l_total_order := 0; -- total ordered amount for offer.
4009 l_total_amount := 0; --- total utilization amount for offer.
4010
4011 OPEN c_old_price_adj(l_qp_list_header_id);
4012 --FOR l_old_price_adj IN c_old_price_adj(l_qp_list_header_id)
4013 LOOP
4014 FETCH c_old_price_adj BULK COLLECT INTO l_amountTbl, l_orderLineIdTbl
4015 , l_priceAdjustmentIDTbl, l_glDateTbl
4016 , l_objectTypeTbl, l_objectIdTbl, l_glPostedFlagTbl, l_utilizationIdTbl --Added for bug 7030415
4017 LIMIT g_bulk_limit;
4018 FOR i IN NVL(l_priceAdjustmentIDTbl.FIRST, 1) .. NVL(l_priceAdjustmentIDTbl.LAST, 0) LOOP
4019 IF l_objectTypeTbl(i) ='ORDER' THEN
4020 IF G_DEBUG THEN
4021 ozf_utility_pvt.debug_message(' order_line_id: '|| l_orderLineIdTbl(i) );
4022 END IF;
4023 write_conc_log(' order_line_id: '|| l_orderLineIdTbl(i) );
4024
4025 l_source_code := 'OM';
4026 l_order_line_id := l_orderLineIdTbl(i);
4027 OPEN c_order_line_info(l_order_line_id);
4028 FETCH c_order_line_info INTO l_order_line_info;
4029 CLOSE c_order_line_info;
4030
4031 IF l_priceAdjustmentIDTbl(i) = -1 THEN
4032 OPEN c_discount(l_order_line_id,l_priceAdjustmentIDTbl(i));
4033 FETCH c_discount INTO l_unit_discount;
4034 CLOSE c_discount;
4035 ELSE
4036 OPEN c_unit_discount(l_order_line_id,l_priceAdjustmentIDTbl(i));
4037 FETCH c_unit_discount INTO l_unit_discount;
4038 CLOSE c_unit_discount;
4039 END IF;
4040
4041 write_conc_log(' l_unit_discount: '|| l_unit_discount);
4042
4043 ELSE
4044 IF G_DEBUG THEN
4045 ozf_utility_pvt.debug_message(' resale_line_id: '|| l_objectIdTbl(i) );
4046 END IF;
4047 write_conc_log(' resale_line_id: '|| l_objectIdTbl(i));
4048
4049 l_source_code := 'IS';
4050 l_order_line_id := l_objectIdTbl(i);
4051 OPEN c_resale_line_info(l_order_line_id,l_priceAdjustmentIDTbl(i));
4052 FETCH c_resale_line_info INTO l_order_line_info;
4053 CLOSE c_resale_line_info;
4054 END IF;
4055
4056 l_selling_price := l_order_line_info.unit_list_price + NVL(l_unit_discount,0); -- discount is negative
4057 write_conc_log(' l_selling_price: '|| l_selling_price);
4058
4059 OPEN c_offer_curr;
4060 FETCH c_offer_curr INTO l_offer_curr, l_offer_id;
4061 CLOSE c_offer_curr;
4062
4063 IF l_amountTbl(i) = 0 THEN -- fix bug 5689866
4064 --21-MAY-07 kdass fixed bug 6059036 - added condition for direct and indirect orders
4065 IF l_objectTypeTbl(i) ='ORDER' THEN
4066 OPEN c_apply_discount(l_offer_id, l_orderLineIdTbl(i));
4067 FETCH c_apply_discount INTO l_apply_discount;
4068 CLOSE c_apply_discount;
4069 ELSE
4070 OPEN c_apply_discount(l_offer_id, l_objectIdTbl(i));
4071 FETCH c_apply_discount INTO l_apply_discount;
4072 CLOSE c_apply_discount;
4073 END IF;
4074
4075 write_conc_log('l_apply_discount: ' || l_apply_discount);
4076
4077 IF l_apply_discount ='N' THEN
4078 IF G_DEBUG THEN
4079 ozf_utility_pvt.debug_message('not apply discount: ' || l_order_line_info.inventory_item_id);
4080 END IF;
4081 write_conc_log(' not apply discount:'|| l_order_line_info.inventory_item_id);
4082 GOTO l_endoffloop;
4083 END IF;
4084 END IF; -- bug 5689866
4085
4086 --Added for bug 7030415
4087 OPEN c_utilization_details(l_utilizationIdTbl(i));
4088 FETCH c_utilization_details INTO l_conv_type,l_org_id;
4089 CLOSE c_utilization_details;
4090
4091 l_act_util_rec.org_id := l_org_id;
4092
4093
4094
4095 --kdass 31-MAR-2006 bug 5101720 convert from order currency to offer currency
4096 IF l_offer_curr <> l_order_line_info.transactional_curr_code THEN
4097
4098 ozf_utility_pvt.write_conc_log('order curr: ' || l_order_line_info.transactional_curr_code);
4099 ozf_utility_pvt.write_conc_log('offer curr: ' || l_offer_curr);
4100 ozf_utility_pvt.write_conc_log('selling price: ' || l_selling_price);
4101
4102
4103
4104 ozf_utility_pvt.write_conc_log('l_conv_type: ' || l_conv_type);
4105
4106
4107 ozf_utility_pvt.convert_currency (x_return_status => l_return_status
4108 ,p_conv_type => l_conv_type --7030415
4109 ,p_conv_date => l_order_line_info.actual_shipment_date
4110 ,p_from_currency => l_order_line_info.transactional_curr_code
4111 ,p_to_currency => l_offer_curr
4112 ,p_from_amount => l_selling_price
4113 ,x_to_amount => l_conv_price
4114 ,x_rate => l_rate
4115 );
4116
4117 IF l_return_status = fnd_api.g_ret_sts_error THEN
4118 RAISE fnd_api.g_exc_error;
4119 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4120 RAISE fnd_api.g_exc_unexpected_error;
4121 END IF;
4122
4123 l_selling_price := l_conv_price;
4124 write_conc_log ('selling price after currency conversion: ' || l_selling_price);
4125
4126 END IF;
4127
4128 /* IF g_order_gl_phase = 'SHIPPED' THEN
4129 l_trx_date := l_order_line_info.actual_shipment_date;
4130 ELSE
4131 l_trx_date := l_order_line_info.fulfillment_date;
4132 END IF;
4133 */
4134 -- for testing
4135 /* IF l_volume = 0 THEN
4136
4137 l_volume := get_order_amount_quantity( l_qp_list_header_id ,
4138 l_order_amount,
4139 l_new_discount,
4140 l_new_operator,
4141 l_old_discount, -- discount in QP.
4142 l_old_operator,
4143 l_volume_type,
4144 l_return_status
4145 );
4146 write_conc_log(' l_volume from test: '|| l_volume );
4147
4148 END IF;
4149 */
4150
4151 OPEN c_get_group(l_order_line_id,l_qp_list_header_id);
4152 FETCH c_get_group INTO l_group_id,l_pbh_line_id,l_included_vol_flag;
4153 CLOSE c_get_group;
4154
4155 IF G_DEBUG THEN
4156 ozf_utility_pvt.debug_message(' l_group_id: '|| l_group_id );
4157 ozf_utility_pvt.debug_message(' l_pbh_line_id: '|| l_pbh_line_id );
4158 ozf_utility_pvt.debug_message(' l_included_vol_flag: '|| l_included_vol_flag );
4159 END IF;
4160 write_conc_log(' l_group_id: '|| l_group_id );
4161 write_conc_log(' l_pbh_line_id: '|| l_pbh_line_id );
4162 write_conc_log(' l_included_vol_flag: '|| l_included_vol_flag );
4163
4164 IF l_group_id is NULL OR l_pbh_line_id is NULL THEN
4165 GOTO l_endoffloop;
4166 END IF;
4167
4168 OPEN c_market_option(l_qp_list_header_id,l_group_id);
4169 FETCH c_market_option INTO l_retroactive;
4170 CLOSE c_market_option;
4171
4172 OPEN c_discount_header(l_pbh_line_id);
4173 FETCH c_discount_header INTO l_discount_type,l_volume_type;
4174 CLOSE c_discount_header;
4175
4176 /* IF l_retroactive = 'Y' THEN -- for retroactive, always takes the max volume.
4177 OPEN c_max_volume(l_order_line_id,l_qp_list_header_id,l_source_code);
4178 FETCH c_max_volume INTO l_volume;
4179 CLOSE c_max_volume;-- not work for non-include volume product since query return null. 12/11/06 by feliu
4180 ELSE
4181 */
4182
4183 IF l_retroactive = 'Y' THEN
4184 ozf_volume_calculation_pub.get_volume
4185 (p_init_msg_list =>fnd_api.g_false
4186 ,p_api_version =>1.0
4187 ,p_commit =>fnd_api.g_false
4188 ,x_return_status =>l_return_status
4189 ,x_msg_count => l_msg_count
4190 ,x_msg_data => l_msg_data
4191 ,p_qp_list_header_id => l_qp_list_header_id
4192 ,p_order_line_id =>l_order_line_id
4193 ,p_trx_date =>sysdate+1
4194 ,p_source_code => l_source_code
4195 ,x_acc_volume => l_volume
4196 );
4197 ELSE
4198 -- julou bug 6348078. can't use gl_date for IDSM line. it's different from transaction_date
4199 IF l_source_code = 'IS' THEN
4200 write_conc_log('calculating transaction_date for non-retro offer, IS line_id: ' || l_order_line_id);
4201 OPEN c_trx_date(l_order_line_id);
4202 FETCH c_trx_date INTO l_trx_date;
4203 CLOSE c_trx_date;
4204 ELSE
4205 l_trx_date := l_glDateTbl(i);
4206 END IF;
4207 write_conc_log('transaction_date after conversion: ' || TO_CHAR(l_trx_date, 'YYYY-MM-DD HH:MI:SS'));
4208 ozf_volume_calculation_pub.get_volume
4209 (p_init_msg_list =>fnd_api.g_false
4210 ,p_api_version =>1.0
4211 ,p_commit =>fnd_api.g_false
4212 ,x_return_status =>l_return_status
4213 ,x_msg_count => l_msg_count
4214 ,x_msg_data => l_msg_data
4215 ,p_qp_list_header_id => l_qp_list_header_id
4216 ,p_order_line_id =>l_order_line_id
4217 --,p_trx_date =>l_glDateTbl(i)
4218 ,p_trx_date => l_trx_date
4219 ,p_source_code => l_source_code
4220 ,x_acc_volume => l_volume
4221 );
4222 END IF;
4223
4224 IF l_return_status = fnd_api.g_ret_sts_error THEN
4225 RAISE fnd_api.g_exc_error;
4226 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4227 RAISE fnd_api.g_exc_unexpected_error;
4228 END IF;
4229 -- END IF; --l_retroactive = 'Y'
4230
4231 IF G_DEBUG THEN
4232 ozf_utility_pvt.debug_message(' l_volume: '|| l_volume );
4233 END IF;
4234 write_conc_log(' l_volume: '|| l_volume );
4235
4236 -- l_new_discount := 0;
4237 OPEN c_current_discount(l_volume,l_pbh_line_id);
4238 FETCH c_current_discount INTO l_new_discount;
4239 CLOSE c_current_discount;
4240
4241 -- fix bug 5055425 by feliu on 02/23/2006
4242 IF l_new_discount is NULL THEN
4243 OPEN c_get_tier_limits(l_pbh_line_id);
4244 FETCH c_get_tier_limits INTO l_min_tier,l_max_tier;
4245 CLOSE c_get_tier_limits;
4246 IF l_volume < l_min_tier THEN
4247 l_new_discount := 0;
4248 ELSE
4249 OPEN c_get_max_tier(l_max_tier,l_pbh_line_id);
4250 FETCH c_get_max_tier INTO l_new_discount;
4251 CLOSE c_get_max_tier;
4252 END IF;
4253 IF G_DEBUG THEN
4254 ozf_utility_pvt.debug_message(' l_new_discount: '|| l_new_discount );
4255 END IF;
4256 write_conc_log(' l_new_discount: '|| l_new_discount );
4257 END IF;
4258
4259 l_preset_tier := NULL;
4260 OPEN c_preset_tier(l_pbh_line_id,l_qp_list_header_id,l_group_id);
4261 FETCH c_preset_tier INTO l_preset_tier;
4262 CLOSE c_preset_tier;
4263
4264 write_conc_log( ' l_preset_tier=' || l_preset_tier);
4265 write_conc_log( ' l_new_discount=' || l_new_discount);
4266
4267
4268 IF l_preset_tier is NOT NULL AND l_preset_tier > l_new_discount THEN
4269 l_new_discount := l_preset_tier;
4270 IF G_DEBUG THEN
4271 ozf_utility_pvt.debug_message('not reach preset tier: ');
4272 END IF;
4273 write_conc_log(' not reach preset tier:');
4274 END IF;
4275
4276 l_new_utilization := 0;
4277 l_value :=0;
4278 l_adj_amount := 0;
4279
4280 IF l_volume_type = 'PRICING_ATTRIBUTE12' THEN -- volume type = AMOUNT
4281 IF l_order_type = 'SHIPPED' THEN
4282 l_value := l_order_line_info.shipped_quantity * l_selling_price ;
4283 ELSIF l_order_type = 'INVOICED' THEN
4284 l_value := l_order_line_info.invoiced_quantity * l_selling_price ;
4285 ELSE
4286 l_value := l_order_line_info.ordered_quantity * l_selling_price ;
4287 END IF;
4288 ELSE
4289 IF l_order_type = 'SHIPPED' THEN
4290 l_value := l_order_line_info.shipped_quantity ;
4291 ELSIF l_order_type = 'INVOICED' THEN
4292 l_value := l_order_line_info.invoiced_quantity ;
4293 ELSE
4294 l_value := l_order_line_info.ordered_quantity ;
4295 END IF;
4296 END IF;
4297
4298 --For retroactive volume offer.need to make adjustment for all of orders in this offer.
4299 IF l_retroactive = 'Y' THEN
4300 IF l_discount_type = '%' THEN
4301 IF l_volume_type = 'PRICING_ATTRIBUTE12' THEN
4302 l_new_utilization := l_value* l_new_discount / 100;
4303 ELSE -- % is for unit price. need to multiple when range in quantity.
4304 l_new_utilization := l_value* l_selling_price * l_new_discount / 100;
4305 END IF;
4306 ELSIF l_discount_type = 'AMT' THEN
4307 IF l_volume_type = 'PRICING_ATTRIBUTE12' THEN
4308 -- amt is for unit pirce. need to divide when range in amount.
4309 l_new_utilization :=l_value / l_selling_price * l_new_discount ;
4310 ELSE
4311 l_new_utilization :=l_value * l_new_discount ;
4312 END IF;
4313 END IF;
4314
4315 l_adj_amount := l_new_utilization - l_amountTbl(i);
4316
4317 IF G_DEBUG THEN
4318 ozf_utility_pvt.debug_message(l_full_name ||' retroactive flag is Y. ' || ' l_volume_type=' || l_volume_type
4319 || ' l_new_discount=' || l_new_discount
4320 || ' l_new_utilization=' || l_new_utilization
4321 || ' l_amountTbl=' || l_amountTbl(i)
4322 || ' l_adj_amount=' || l_adj_amount);
4323 END IF;
4324 write_conc_log(l_full_name ||' retroactive flag is Y. ' || ' l_volume_type=' || l_volume_type
4325 || ' l_new_discount=' || l_new_discount
4326 || ' l_new_utilization=' || l_new_utilization
4327 || ' l_amountTbl=' || l_amountTbl(i)
4328 || ' l_adj_amount=' || l_adj_amount);
4329
4330 END IF; --l_retroactive
4331
4332 --For non-retroactive volume offer.
4333 -- adjusment need to be make for all of orders when considering returned order.
4334 IF NVL(l_retroactive, 'N') = 'N' THEN
4335 IF l_included_vol_flag = 'Y' THEN
4336 l_previous_tier_max := l_volume;
4337 ELSE
4338 /*
4339 logic here is to add current order line's volume to offer's volume for adjustment.
4340 eg: offer's volume=2.
4341 order line's volume = 5, then total volume = 7.
4342 */
4343 l_previous_tier_max := l_volume + l_value;
4344 END IF;
4345
4346 /*
4347 1-10 1
4348 10-20 2
4349 20-30 3
4350
4351 l_volume = 25
4352 l_value = 10
4353
4354 1st loop: l_previous_tier_max = 25, y1= 5 l_value = 5 l_new_utilization = 5 * 3 = 15 l_previous_tier_max = 20
4355 2st loop: l_previous_tier_max = 20, y1= 5 l_value = 0 l_new_utilization = 5 * 2 = 10 l_previous_tier_max = 10
4356 by feliu on 12/14/06. pre_qualify tier is only for retroactive. not for non-retroactive.
4357 */
4358 IF G_DEBUG THEN
4359 ozf_utility_pvt.debug_message( ' l_value=' || l_value);
4360 END IF;
4361
4362 --fix for bug 6021538
4363 IF l_max_tier IS NULL THEN
4364 OPEN c_get_tier_limits(l_pbh_line_id);
4365 FETCH c_get_tier_limits INTO l_min_tier,l_max_tier;
4366 CLOSE c_get_tier_limits;
4367 END IF;
4368
4369 write_conc_log( ' l_value=' || l_value);
4370 write_conc_log( ' l_volume=' || l_volume);
4371 write_conc_log( ' l_max_tier=' || l_max_tier);
4372
4373
4374 IF l_volume > l_max_tier THEN
4375 l_value:= l_max_tier -l_volume + l_value;
4376 IF l_value<0 THEN
4377 l_value:=0;
4378 END IF;
4379 END IF;
4380
4381 --end bug 6021538
4382 l_preset_tier := NULL;
4383 OPEN c_prior_tiers(l_pbh_line_id, l_volume);
4384 LOOP
4385 FETCH c_prior_tiers INTO l_current_offer_tier_id,l_current_min_tier,l_current_max_tier,l_current_tier_value;
4386 EXIT WHEN c_prior_tiers%NOTFOUND;
4387
4388 write_conc_log( ' l_current_offer_tier_id=' || l_current_offer_tier_id);
4389
4390 -- handle over tier cap. not applicable for R12.
4391 /* IF l_current_max_tier < l_previous_tier_max THEN
4392 l_previous_tier_max := l_current_max_tier;
4393 END IF;
4394 */
4395
4396 OPEN c_preset_tier(l_pbh_line_id,l_qp_list_header_id,l_group_id);
4397 FETCH c_preset_tier INTO l_preset_tier;
4398 CLOSE c_preset_tier;
4399
4400 write_conc_log( ' l_preset_tier=' || l_preset_tier);
4401 write_conc_log( ' l_current_tier_value=' || l_current_tier_value);
4402
4403
4404 IF l_preset_tier is NOT NULL AND l_preset_tier > l_current_tier_value THEN
4405 l_current_tier_value := l_preset_tier;
4406 IF G_DEBUG THEN
4407 ozf_utility_pvt.debug_message('not reach preset tier: ');
4408 END IF;
4409 write_conc_log(' not reach preset tier:');
4410 END IF;
4411
4412 -- logic here is:
4413 -- start from top tier, calculate amount in each tier, until order amount has been calculated.
4414 -- y1 := LEAST((l_previous_tier_max-l_current_min_tier + 1),l_value) ;
4415 y1 := LEAST((l_previous_tier_max-l_current_min_tier),l_value) ;
4416 l_value := l_value - y1;
4417 IF l_discount_type = '%' THEN
4418 IF l_volume_type = 'PRICING_ATTRIBUTE12' THEN
4419 l_new_utilization := l_new_utilization + y1* l_current_tier_value / 100;
4420 ELSE
4421 l_new_utilization := l_new_utilization + y1* l_selling_price * l_current_tier_value / 100;
4422 END IF;
4423 ELSIF l_discount_type = 'AMT' THEN
4424 IF l_volume_type = 'PRICING_ATTRIBUTE12' THEN
4425 l_new_utilization := l_new_utilization + y1 / l_selling_price * l_current_tier_value ;
4426 ELSE
4427 l_new_utilization := l_new_utilization + y1* l_current_tier_value ;
4428 END IF;
4429 END IF;
4430
4431 --l_previous_tier_max := l_current_min_tier - 1 ;
4432 l_previous_tier_max := l_current_min_tier;
4433
4434 IF G_DEBUG THEN
4435 ozf_utility_pvt.debug_message(' retroactive flag is N, computing for prior tier id=' || l_current_offer_tier_id
4436 || ' y1=' || y1 || ' tier_min=' || l_current_min_tier
4437 || ' tier_max=' || l_current_max_tier || ' l_previous_tier_max: ' || l_previous_tier_max
4438 || ' l_new_utilization: ' || l_new_utilization);
4439 END IF;
4440 write_conc_log(' retroactive flag is N, computing for prior tier id=' || l_current_offer_tier_id
4441 || ' y1=' || y1 || ' tier_min=' || l_current_min_tier
4442 || ' tier_max=' || l_current_max_tier || ' l_previous_tier_max: ' || l_previous_tier_max
4443 || ' l_new_utilization: ' || l_new_utilization);
4444
4445 EXIT WHEN l_value <= 0;
4446
4447 END LOOP; -- end of loop for c_prior_tiers
4448 CLOSE c_prior_tiers;
4449 -- For R12, returned order for different customers. ????????
4450 --For returned order, create positive record for return line, then
4451 -- make adjustment based on the difference of total utilization for previous orders.
4452 IF l_returned_flag = true THEN
4453 l_total_amount := l_total_amount + l_amountTbl(i) ;
4454 l_adj_amount := l_new_utilization - l_total_amount;
4455 ELSE -- for non-returned order, make adjustment based on difference of total utilization for specified price adjustment id.
4456 l_adj_amount := l_new_utilization - l_amountTbl(i);
4457 l_total_amount := l_total_amount + l_amountTbl(i) + l_adj_amount;
4458 END IF;
4459
4460 END IF; -- end of non-retroactive adjustment.
4461
4462 l_act_util_rec.price_Adjustment_id := l_priceAdjustmentIDTbl(i);
4463 l_act_util_rec.order_line_id := l_orderLineIdTbl(i);
4464 l_act_util_rec.gl_posted_flag := l_glPostedFlagTbl(i);
4465 l_act_util_rec.object_type := l_objectTypeTbl(i);
4466
4467 IF NVL(l_adj_amount,0) <> 0 THEN
4468 process_accrual (
4469 p_earned_amt =>l_adj_amount,
4470 p_qp_list_header_id =>l_qp_list_header_id,
4471 p_act_util_rec =>l_act_util_rec,
4472 x_return_status =>l_return_status,
4473 x_msg_count =>l_msg_count,
4474 x_msg_data =>l_msg_data );
4475
4476 IF l_return_status = fnd_api.g_ret_sts_error THEN
4477 RAISE fnd_api.g_exc_error;
4478 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4479 RAISE fnd_api.g_exc_unexpected_error;
4480 END IF;
4481 END IF;
4482
4483 IF G_DEBUG THEN
4484 ozf_utility_pvt.debug_message(
4485 l_full_name ||' Process Accrual Msg count '||l_msg_count||' Msg data'||l_msg_data||' Return status'||l_return_status
4486 );
4487 END IF;
4488 write_conc_log(
4489 l_full_name ||' Process Accrual Msg count '||l_msg_count||' Msg data'||l_msg_data||' Return status'||l_return_status
4490 );
4491
4492 <<l_endoffloop>>
4493 NULL;
4494 END LOOP; -- loop for For
4495 EXIT WHEN c_old_price_adj%NOTFOUND;
4496 END LOOP; -- end price adj loop
4497 CLOSE c_old_price_adj;
4498
4499 IF l_return_status = fnd_api.g_ret_sts_error THEN
4500 RAISE fnd_api.g_exc_error;
4501 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
4502 RAISE fnd_api.g_exc_unexpected_error;
4503 END IF;
4504
4505 <<l_endoffloop>>
4506 NULL;
4507
4508 IF G_DEBUG THEN
4509 ozf_utility_pvt.debug_message(' /*************************** DEBUG MESSAGE END *************************/' || l_api_name );
4510 END IF;
4511 write_conc_log(' /*************************** DEBUG MESSAGE END *************************/' || l_api_name );
4512
4513 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4514
4515 x_return_status := l_return_status;
4516
4517 EXCEPTION
4518 WHEN fnd_api.g_exc_error THEN
4519 ROLLBACK TO volume_offer_adjustment;
4520 x_return_status := fnd_api.g_ret_sts_error;
4521 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4522 WHEN fnd_api.g_exc_unexpected_error THEN
4523 ROLLBACK TO volume_offer_adjustment;
4524 x_return_status := fnd_api.g_ret_sts_unexp_error;
4525 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4526 WHEN OTHERS THEN
4527 ROLLBACK TO volume_offer_adjustment;
4528 x_return_status := fnd_api.g_ret_sts_unexp_error;
4529 IF fnd_msg_pub.check_msg_level (fnd_msg_pub.g_msg_lvl_unexp_error) THEN
4530 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4531 END IF;
4532 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data, p_encoded => fnd_api.g_false);
4533
4534 END volume_offer_adjustment;
4535
4536
4537 ---------------------------------------------------------------------
4538 -- PROCEDURE
4539 -- volume_offer_util_adjustment
4540 -- PURPOSE
4541 -- adjustment of utilization amount for backdated adjustments and split orders.
4542 -- HISTORY
4543 -- 2/16/2007 nirprasa Created for bug 6021635
4544 ----------------------------------------------------------------------
4545
4546
4547 PROCEDURE volume_offer_util_adjustment(
4548 p_qp_list_header_id IN NUMBER,
4549 x_return_status OUT NOCOPY VARCHAR2,
4550 x_msg_count OUT NOCOPY NUMBER,
4551 x_msg_data OUT NOCOPY VARCHAR2
4552 ) IS
4553
4554 CURSOR c_old_price_Adj(p_list_header_id IN NUMBER) IS
4555 SELECT sum(plan_curr_amount) old_Adj_amt
4556 , order_line_id
4557 ,min(price_adjustment_id) price_adjustment_id
4558 ,object_type
4559 ,object_id
4560 ,min(gl_date) gl_date
4561 ,min(utilization_id) utilization_id
4562 FROM ozf_funds_utilized_all_b
4563 WHERE plan_id = p_list_header_id
4564 AND plan_type = 'OFFR'
4565 AND utilization_type IN ( 'ACCRUAL','SALES_ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
4566 AND price_adjustment_id IS NOT NULL
4567 GROUP BY order_line_id,object_type,object_id
4568 ORDER BY gl_date;
4569
4570
4571
4572
4573
4574 /* CURSOR c_order_line_qty(p_order_line_id IN NUMBER) IS
4575 SELECT DECODE(line.line_category_code,'ORDER',line.ordered_quantity,
4576 'RETURN', -line.ordered_quantity) ordered_quantity
4577 FROM oe_order_lines_all line
4578 WHERE line.line_id = p_order_line_id;*/
4579
4580
4581 /* CURSOR c_all_orders (p_list_header_id IN NUMBER) IS
4582 select sum(ordered_quantity)
4583 FROM (
4584 select sum(ordered_quantity) ordered_quantity from oe_order_lines_all
4585 where line_id IN
4586 (SELECT order_line_id FROM ozf_funds_utilized_all_b
4587 WHERE plan_id = p_list_header_id
4588 AND plan_type = 'OFFR'
4589 AND utilization_type IN ( 'ACCRUAL','SALES_ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
4590 AND price_adjustment_id IS NOT NULL
4591 )
4592 UNION
4593 select sum(quantity) ordered_quantity from OZF_RESALE_LINES_INT_ALL
4594 where resale_batch_id IN
4595 (SELECT reference_id FROM ozf_funds_utilized_all_b
4596 WHERE plan_id = p_list_header_id
4597 AND plan_type = 'OFFR'
4598 AND utilization_type IN ( 'ACCRUAL','SALES_ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
4599 AND price_adjustment_id IS NOT NULL
4600 )
4601 );*/
4602
4603
4604 CURSOR c_all_orders (p_list_header_id IN NUMBER) IS
4605 select sum(ordered_quantity)
4606 FROM (
4607 SELECT SUM(DECODE(line_category_code,'ORDER',ordered_quantity,
4608 'RETURN', -ordered_quantity)) ordered_quantity
4609 from oe_order_lines_all oe,
4610 (SELECT distinct order_line_id FROM ozf_funds_utilized_all_b
4611 WHERE plan_id = p_list_header_id
4612 AND plan_type = 'OFFR'
4613 AND utilization_type IN ( 'ACCRUAL','SALES_ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
4614 AND price_adjustment_id IS NOT NULL
4615 ) orders
4616 where oe.line_id = orders.order_line_id
4617 UNION
4618 select sum(quantity) ordered_quantity from OZF_RESALE_LINES_INT_ALL ol ,
4619 (SELECT distinct reference_id FROM ozf_funds_utilized_all_b
4620 WHERE plan_id = p_list_header_id
4621 AND plan_type = 'OFFR'
4622 AND utilization_type IN ( 'ACCRUAL','SALES_ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
4623 AND price_adjustment_id IS NOT NULL
4624 ) orders
4625 where ol.resale_batch_id = orders.reference_id
4626
4627 );
4628
4629
4630 CURSOR c_discount_header(p_discount_line_id IN NUMBER) IS
4631 SELECT discount_type,volume_type
4632 FROM ozf_offer_discount_lines
4633 WHERE offer_discount_line_id = p_discount_line_id
4634 AND tier_type = 'PBH';
4635
4636 CURSOR c_get_group(p_order_line_id IN NUMBER,p_list_header_id IN NUMBER) IS
4637 SELECT group_no,pbh_line_id,include_volume_flag
4638 FROM ozf_order_group_prod
4639 WHERE order_line_id = p_order_line_id
4640 AND qp_list_header_id = p_list_header_id;
4641
4642 CURSOR c_market_option(p_list_header_id IN NUMBER, p_group_id IN NUMBER) IS
4643 SELECT opt.retroactive_flag
4644 FROM ozf_offr_market_options opt
4645 WHERE opt.GROUP_NUMBER= p_group_id
4646 AND opt.qp_list_header_id = p_list_header_id;
4647
4648 CURSOR c_current_discount(p_volume IN NUMBER, p_parent_discount_id IN NUMBER) IS
4649 SELECT discount
4650 FROM ozf_offer_discount_lines
4651 WHERE p_volume > volume_from
4652 AND p_volume <= volume_to
4653 AND parent_discount_line_id = p_parent_discount_id;
4654
4655 CURSOR c_get_tier_limits (p_parent_discount_id IN NUMBER) IS
4656 SELECT MIN(volume_from),MAX(volume_to)
4657 FROM ozf_offer_discount_lines
4658 WHERE parent_discount_line_id = p_parent_discount_id;
4659
4660 CURSOR c_get_max_tier (p_max_volume_to IN NUMBER,p_parent_discount_id IN NUMBER) IS
4661 SELECT discount
4662 FROM ozf_offer_discount_lines
4663 WHERE volume_to =p_max_volume_to
4664 AND parent_discount_line_id = p_parent_discount_id;
4665
4666 CURSOR c_order_line_info(p_order_line_id IN NUMBER) IS
4667 SELECT DECODE(line.line_category_code,'ORDER',line.ordered_quantity,
4668 'RETURN', -line.ordered_quantity) ordered_quantity,
4669 DECODE(line.line_category_code,'ORDER',NVL(line.shipped_quantity,line.ordered_quantity),
4670 'RETURN', line.invoiced_quantity,
4671 line.ordered_quantity) shipped_quantity,
4672 line.invoiced_quantity,
4673 line.unit_selling_price,
4674 line.line_id,
4675 line.actual_shipment_date,
4676 line.fulfillment_date, -- invoiced date ?????
4677 line.inventory_item_id,
4678 header.transactional_curr_code,
4679 header.header_id
4680 FROM oe_order_lines_all line, oe_order_headers_all header
4681 WHERE line.line_id = p_order_line_id
4682 AND line.header_id = header.header_id;
4683
4684
4685 CURSOR c_resale_line_info(p_resale_line_id IN NUMBER, p_adj_id IN NUMBER) IS
4686 SELECT line.quantity ordered_quantity ,
4687 line.quantity shipped_quantity,
4688 line.quantity invoiced_quantity,
4689 adj.priced_unit_price unit_list_price,
4690 line.resale_line_id line_id,
4691 NVL(line.date_shipped, line.date_ordered) actual_shipment_date,
4692 NVL(line.date_shipped, line.date_ordered) fulfillment_date, -- invoiced date ?????
4693 line.inventory_item_id,
4694 line.currency_code, --dummy column
4695 line.resale_header_id
4696 FROM OZF_RESALE_LINES_ALL line,ozf_resale_adjustments_all adj
4697 WHERE line.resale_line_id = p_resale_line_id
4698 AND adj.resale_adjustment_id = p_adj_id
4699 AND line.resale_line_id = adj.resale_line_id;
4700
4701 CURSOR c_offer_curr IS
4702 SELECT nvl(transaction_currency_code,fund_request_curr_code), offer_id
4703 FROM ozf_offers
4704 WHERE qp_list_header_id = p_qp_list_header_id;
4705
4706 --22-FEB-2007 kdass bug 5759350 - changed datatype of p_product_id from NUMBER to VARCHAR2 based on Feng's suggestion
4707 --fix for bug 5979971
4708 CURSOR c_apply_discount(p_offer_id IN NUMBER,p_line_id IN NUMBER) IS
4709 SELECT NVL(apply_discount_flag,'N')
4710 FROM ozf_order_group_prod
4711 WHERE offer_id = p_offer_id
4712 AND order_line_id = p_line_id;
4713
4714 CURSOR c_offer_info (p_list_header_id IN NUMBER) IS
4715 SELECT nvl(transaction_currency_code,fund_request_curr_code) transaction_currency_code
4716 , beneficiary_account_id, offer_id
4717 FROM ozf_offers
4718 WHERE qp_list_header_id = p_list_header_id;
4719
4720 CURSOR c_order_line_details (p_line_id IN NUMBER) IS
4721 SELECT invoice_to_org_id, ship_to_org_id
4722 FROM oe_order_lines_all
4723 WHERE line_id = p_line_id;
4724
4725 CURSOR c_cust_number (p_header_id IN NUMBER) IS
4726 SELECT cust.cust_account_id
4727 FROM hz_cust_acct_sites_all acct_site,
4728 hz_cust_site_uses_all site_use,
4729 hz_cust_accounts cust,
4730 oe_order_headers_all header
4731 WHERE header.header_id = p_header_id
4732 AND acct_site.cust_acct_site_id = site_use.cust_acct_site_id
4733 AND acct_site.cust_account_id = cust.cust_account_id
4734 AND site_use.site_use_id = header.invoice_to_org_id ;
4735
4736
4737 CURSOR c_prior_tiers(p_parent_discount_id IN NUMBER, p_volume IN NUMBER ) IS
4738 SELECT offer_discount_line_id ,volume_from ,volume_to, discount
4739 FROM ozf_offer_discount_lines
4740 WHERE parent_discount_line_id = p_parent_discount_id
4741 AND p_volume >= volume_from
4742 ORDER BY volume_from DESC;
4743
4744 CURSOR c_preset_tier(p_pbh_line_id IN NUMBER, p_qp_list_header_id IN NUMBER,p_group_id IN NUMBER) IS
4745 SELECT a.discount
4746 FROM ozf_offer_discount_lines a, ozf_market_preset_tiers b, ozf_offr_market_options c
4747 WHERE a.offer_discount_line_id = b.dis_offer_discount_id
4748 AND b.pbh_offer_discount_id = p_pbh_line_id
4749 AND b.offer_market_option_id = c.offer_market_option_id
4750 AND c.qp_list_header_id = p_qp_list_header_id
4751 AND c.group_number = p_group_id;
4752
4753
4754 CURSOR c_volume_detail (p_order_line_id IN NUMBER,p_source_code IN VARCHAR2) IS
4755 SELECT billto_cust_account_id, bill_to_site_use_id, ship_to_site_use_id
4756 FROM ozf_funds_utilized_all_b
4757 WHERE (p_source_code = 'OM' AND object_type = 'ORDER' AND order_line_id = p_order_line_id)
4758 OR (p_source_code = 'IS' AND object_type = 'TP_ORDER' AND object_id = p_order_line_id);
4759
4760 CURSOR c_all_cust_orders (p_list_header_id IN NUMBER, p_cust_account_id IN NUMBER) IS
4761 select sum(ordered_quantity)
4762 FROM (
4763 SELECT SUM (DECODE(line_category_code,'ORDER',ordered_quantity,
4764 'RETURN', -ordered_quantity)) ordered_quantity
4765 from oe_order_lines_all oe,
4766 (SELECT distinct order_line_id FROM ozf_funds_utilized_all_b
4767 WHERE plan_id = p_list_header_id
4768 AND plan_type = 'OFFR'
4769 AND utilization_type IN ( 'ACCRUAL','SALES_ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
4770 AND price_adjustment_id IS NOT NULL
4771 AND cust_account_id = p_cust_account_id
4772 ) orders
4773 where oe.line_id = orders.order_line_id
4774 UNION
4775 select sum(quantity) ordered_quantity from OZF_RESALE_LINES_INT_ALL ol ,
4776 (SELECT distinct reference_id FROM ozf_funds_utilized_all_b
4777 WHERE plan_id = p_list_header_id
4778 AND plan_type = 'OFFR'
4779 AND utilization_type IN ( 'ACCRUAL','SALES_ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
4780 AND price_adjustment_id IS NOT NULL
4781 AND cust_account_id = p_cust_account_id
4782 ) orders
4783 where ol.resale_batch_id = orders.reference_id
4784
4785 );
4786
4787
4788 CURSOR c_all_cust_orders2 (p_list_header_id IN NUMBER, p_cust_account_id IN NUMBER, p_transaction_date IN DATE) IS
4789 select sum(ordered_quantity)
4790 FROM (
4791 SELECT SUM (DECODE(line_category_code,'ORDER',ordered_quantity,
4792 'RETURN', -ordered_quantity)) ordered_quantity
4793 from oe_order_lines_all oe,
4794 (SELECT distinct order_line_id FROM ozf_funds_utilized_all_b
4795 WHERE plan_id = p_list_header_id
4796 AND plan_type = 'OFFR'
4797 AND utilization_type IN ( 'ACCRUAL','SALES_ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
4798 AND price_adjustment_id IS NOT NULL
4799 AND cust_account_id = p_cust_account_id
4800 AND gl_date <= p_transaction_date
4801 ) orders
4802 where oe.line_id = orders.order_line_id
4803 UNION
4804 select sum(quantity) ordered_quantity from OZF_RESALE_LINES_INT_ALL ol ,
4805 (SELECT distinct reference_id FROM ozf_funds_utilized_all_b
4806 WHERE plan_id = p_list_header_id
4807 AND plan_type = 'OFFR'
4808 AND utilization_type IN ( 'ACCRUAL','SALES_ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
4809 AND price_adjustment_id IS NOT NULL
4810 AND cust_account_id = p_cust_account_id
4811 AND gl_date <= p_transaction_date
4812 ) orders
4813 where ol.resale_batch_id = orders.reference_id
4814
4815 );
4816
4817 CURSOR c_is_util_correct(p_list_header_id IN NUMBER) IS
4818 SELECT 1 FROM DUAL WHERE EXISTS
4819 ( SELECT 1
4820 FROM
4821 ( SELECT sum(plan_curr_amount) old_Adj_amt
4822 , order_line_id
4823 ,min(price_adjustment_id) price_adjustment_id
4824 ,object_type
4825 ,object_id
4826 ,min(gl_date) gl_date
4827 FROM ozf_funds_utilized_all_b
4828 WHERE plan_id = p_list_header_id
4829 AND plan_type = 'OFFR'
4830 AND utilization_type IN ( 'ACCRUAL','SALES_ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
4831 AND price_adjustment_id IS NOT NULL
4832 AND NVL(gl_posted_flag,'Y')='Y'
4833 GROUP BY order_line_id,object_type,object_id
4834 ORDER BY gl_date) earned,
4835 ( SELECT sum(plan_curr_amount) old_Adj_amt
4836 , order_line_id
4837 ,min(price_adjustment_id) price_adjustment_id
4838 ,object_type
4839 ,object_id
4840 ,min(gl_date) gl_date
4841 FROM ozf_funds_utilized_all_b
4842 WHERE plan_id = p_list_header_id
4843 AND plan_type = 'OFFR'
4844 AND utilization_type IN ( 'ACCRUAL','SALES_ACCRUAL','LEAD_ACCRUAL','UTILIZED', 'ADJUSTMENT', 'LEAD_ADJUSTMENT')
4845 AND price_adjustment_id IS NOT NULL
4846 -- AND gl_posted_flag in ('Y','N')
4847 GROUP BY order_line_id,object_type,object_id
4848 ORDER BY gl_date) utilized
4849
4850 WHERE utilized.old_Adj_amt <> earned.old_Adj_amt
4851 AND utilized.order_line_id=earned.order_line_id
4852 );
4853
4854
4855
4856 l_api_name CONSTANT VARCHAR2(30) := 'volume_offer_util_adjustment';
4857 l_retroactive VARCHAR2(1) ;
4858 l_total_ordered_qty NUMBER;
4859 l_line_ordered_qty NUMBER;
4860 l_volume NUMBER;
4861 l_group_id NUMBER;
4862 l_pbh_line_id NUMBER;
4863 l_value NUMBER;
4864 l_included_vol_flag VARCHAR2(1);
4865 l_discount_type VARCHAR2(30);
4866 l_volume_type VARCHAR2(30);
4867 l_adj_amount NUMBER;
4868 l_utilization_amount NUMBER;
4869 l_min_tier NUMBER;
4870 l_max_tier NUMBER;
4871 l_new_discount NUMBER;
4872 l_offer_curr VARCHAR2(30);
4873 l_offer_id NUMBER;
4874 l_selling_price NUMBER;
4875 l_apply_discount VARCHAR2(1) ;
4876 l_return_status VARCHAR2 (20) := fnd_api.g_ret_sts_success;
4877 l_conv_price NUMBER;
4878 l_rate NUMBER;
4879 l_new_utilization NUMBER;
4880 l_invoice_to_org_id NUMBER;
4881 l_ship_to_org_id NUMBER;
4882 l_cust_number NUMBER;
4883 l_act_budget_id NUMBER;
4884 l_act_budgets_rec ozf_actbudgets_pvt.act_budgets_rec_type;
4885 l_act_util_rec ozf_actbudgets_pvt.act_util_rec_type;
4886 l_offer_info c_offer_info%ROWTYPE;
4887 l_order_line_info c_order_line_info%ROWTYPE;
4888 l_amountTbl amountTbl ;
4889 l_objectTypeTbl objectTypeTbl ;
4890 l_objectIdTbl objectIdTbl;
4891 l_priceAdjustmentIDTbl priceAdjustmentIDTbl ;
4892 l_orderLineIdTbl orderLineIdTbl;
4893 l_glDateTbl glDateTbl;
4894 l_order_line_id NUMBER;
4895
4896 --Added for bug 7030415
4897 l_utilizationIdTbl utilizationIdTbl;
4898 CURSOR c_utilization_details(l_utilization_id IN NUMBER) IS
4899 SELECT exchange_rate_type, org_id
4900 FROM ozf_funds_utilized_all_b
4901 WHERE utilization_id=l_utilization_id;
4902
4903 l_conv_type ozf_funds_utilized_all_b.exchange_rate_type%TYPE;
4904 l_org_id NUMBER;
4905
4906
4907
4908 l_current_offer_tier_id NUMBER;
4909 y1 NUMBER; -- Initial Adjsutment
4910 l_current_max_tier NUMBER;
4911 l_current_min_tier NUMBER;
4912 l_current_tier_value NUMBER;
4913 l_previous_tier_max NUMBER;
4914 l_preset_tier NUMBER;
4915 l_cust_account_id NUMBER;
4916 l_bill_to NUMBER;
4917 l_ship_to NUMBER;
4918 l_source_code VARCHAR2(30);
4919 l_util_correct NUMBER;
4920
4921
4922 BEGIN
4923
4924 write_conc_log(' /*************************** DEBUG MESSAGE START *************************/' || l_api_name);
4925 write_conc_log(' p_qp_list_header_id: ' || p_qp_list_header_id);
4926
4927 OPEN c_all_orders (p_qp_list_header_id);
4928 FETCH c_all_orders INTO l_total_ordered_qty;
4929 CLOSE c_all_orders;
4930
4931 write_conc_log(' l_total_ordered_qty: ' || l_total_ordered_qty);
4932
4933 l_volume:=0;
4934 l_new_utilization := 0;
4935
4936 OPEN c_is_util_correct(p_qp_list_header_id);
4937 FETCH c_is_util_correct INTO l_util_correct;
4938 CLOSE c_is_util_correct;
4939
4940 write_conc_log(' l_util_correct: ' || l_util_correct);
4941
4942 IF NVL(l_util_correct,0)<>0 THEN
4943 OPEN c_old_price_adj(p_qp_list_header_id);
4944 LOOP
4945 FETCH c_old_price_adj BULK COLLECT INTO l_amountTbl, l_orderLineIdTbl
4946 , l_priceAdjustmentIDTbl
4947 , l_objectTypeTbl, l_objectIdTbl, l_glDateTbl, l_utilizationIdTbl
4948 LIMIT g_bulk_limit;
4949
4950 FOR i IN NVL(l_priceAdjustmentIDTbl.FIRST, 1) .. NVL(l_priceAdjustmentIDTbl.LAST, 0) LOOP
4951
4952 write_conc_log(' l_objectTypeTbl(i): ' || l_objectTypeTbl(i));
4953
4954
4955 IF l_objectTypeTbl(i) ='ORDER' THEN
4956 IF G_DEBUG THEN
4957 ozf_utility_pvt.debug_message(' order_line_id: '|| l_orderLineIdTbl(i) );
4958 END IF;
4959 write_conc_log(' order_line_id: '|| l_orderLineIdTbl(i) );
4960
4961 l_order_line_id:=l_orderLineIdTbl(i);
4962
4963 OPEN c_order_line_info(l_orderLineIdTbl(i));
4964 FETCH c_order_line_info INTO l_order_line_info;
4965 CLOSE c_order_line_info;
4966
4967 ELSE
4968 IF G_DEBUG THEN
4969 ozf_utility_pvt.debug_message(' resale_line_id: '|| l_objectIdTbl(i) );
4970 END IF;
4971 write_conc_log(' resale_line_id: '|| l_objectIdTbl(i));
4972
4973 l_order_line_id:=l_objectIdTbl(i);
4974
4975 OPEN c_resale_line_info(l_objectIdTbl(i),l_priceAdjustmentIDTbl(i));
4976 FETCH c_resale_line_info INTO l_order_line_info;
4977 CLOSE c_resale_line_info;
4978 END IF;
4979
4980 l_selling_price := NVL(l_order_line_info.unit_selling_price,0) ; -- discount is negative
4981
4982 write_conc_log(' l_selling_price: '|| l_selling_price);
4983
4984 OPEN c_offer_curr;
4985 FETCH c_offer_curr INTO l_offer_curr, l_offer_id;
4986 CLOSE c_offer_curr;
4987
4988
4989 IF l_objectTypeTbl(i) ='ORDER' THEN
4990 l_source_code := 'OM';
4991 ELSE
4992 l_source_code := 'IS';
4993 END IF;
4994
4995 IF l_amountTbl(i) = 0 THEN -- fix bug 5689866
4996 -- OPEN c_apply_discount(l_offer_id,l_order_line_info.inventory_item_id);
4997 IF l_objectTypeTbl(i) ='ORDER' THEN
4998
4999 OPEN c_apply_discount(l_offer_id, l_orderLineIdTbl(i));
5000 FETCH c_apply_discount INTO l_apply_discount;
5001 CLOSE c_apply_discount;
5002 ELSE
5003 OPEN c_apply_discount(l_offer_id, l_objectIdTbl(i));
5004 FETCH c_apply_discount INTO l_apply_discount;
5005 CLOSE c_apply_discount;
5006 END IF;
5007
5008 IF l_apply_discount ='N' THEN
5009 IF G_DEBUG THEN
5010 ozf_utility_pvt.debug_message('not apply discount: ' || l_order_line_info.inventory_item_id);
5011 END IF;
5012 write_conc_log(' not apply discount:'|| l_order_line_info.inventory_item_id);
5013 GOTO l_endoffloop;
5014 END IF;
5015 END IF; -- bug 5689866
5016
5017 --kdass 31-MAR-2006 bug 5101720 convert from order currency to offer currency
5018 IF l_offer_curr <> l_order_line_info.transactional_curr_code THEN
5019
5020 ozf_utility_pvt.write_conc_log('order curr: ' || l_order_line_info.transactional_curr_code);
5021 ozf_utility_pvt.write_conc_log('offer curr: ' || l_offer_curr);
5022 ozf_utility_pvt.write_conc_log('selling price: ' || l_selling_price);
5023
5024 -- Added for bug 7030415
5025 OPEN c_utilization_details(l_utilizationIdTbl(i));
5026 FETCH c_utilization_details INTO l_conv_type, l_org_id;
5027 CLOSE c_utilization_details;
5028 l_act_util_rec.org_id := l_org_id;
5029
5030 ozf_utility_pvt.write_conc_log('l_conv_type: ' || l_conv_type);
5031
5032 ozf_utility_pvt.convert_currency (x_return_status => l_return_status
5033 ,p_conv_type => l_conv_type -- 7030415
5034 ,p_conv_date => l_order_line_info.actual_shipment_date
5035 ,p_from_currency => l_order_line_info.transactional_curr_code
5036 ,p_to_currency => l_offer_curr
5037 ,p_from_amount => l_selling_price
5038 ,x_to_amount => l_conv_price
5039 ,x_rate => l_rate
5040 );
5041
5042 IF l_return_status = fnd_api.g_ret_sts_error THEN
5043 RAISE fnd_api.g_exc_error;
5044 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5045 RAISE fnd_api.g_exc_unexpected_error;
5046 END IF;
5047
5048 l_selling_price := NVL(l_conv_price,0);
5049 write_conc_log ('selling price after currency conversion: ' || l_selling_price);
5050
5051 END IF;
5052 /*ozf_utility_pvt.write_conc_log('l_orderLineIdTbl(i): ' || l_orderLineIdTbl(i));
5053 OPEN c_order_line_qty(l_orderLineIdTbl(i));
5054 FETCH c_order_line_qty INTO l_line_ordered_qty;
5055 CLOSE c_order_line_qty;*/
5056
5057 l_line_ordered_qty := l_order_line_info.ordered_quantity;
5058
5059 ozf_utility_pvt.write_conc_log('l_line_ordered_qty: ' || l_line_ordered_qty);
5060 ozf_utility_pvt.write_conc_log('l_orderLineIdTbl(i): ' || l_orderLineIdTbl(i));
5061 ozf_utility_pvt.write_conc_log('p_qp_list_header_id: ' || p_qp_list_header_id);
5062
5063
5064
5065
5066 OPEN c_get_group(l_orderLineIdTbl(i),p_qp_list_header_id);
5067 FETCH c_get_group INTO l_group_id,l_pbh_line_id,l_included_vol_flag;
5068 CLOSE c_get_group;
5069
5070 IF G_DEBUG THEN
5071 ozf_utility_pvt.debug_message(' l_group_id: '|| l_group_id );
5072 ozf_utility_pvt.debug_message(' l_pbh_line_id: '|| l_pbh_line_id );
5073 ozf_utility_pvt.debug_message(' l_included_vol_flag: '|| l_included_vol_flag );
5074 END IF;
5075
5076 write_conc_log(' l_group_id: '|| l_group_id );
5077 write_conc_log(' l_pbh_line_id: '|| l_pbh_line_id );
5078 write_conc_log(' l_included_vol_flag: '|| l_included_vol_flag );
5079
5080 IF l_group_id is NULL OR l_pbh_line_id is NULL THEN
5081 GOTO l_endoffloop;
5082 END IF;
5083
5084 OPEN c_market_option(p_qp_list_header_id,l_group_id);
5085 FETCH c_market_option INTO l_retroactive;
5086 CLOSE c_market_option;
5087
5088 write_conc_log(' l_retroactive: '|| l_retroactive );
5089
5090
5091
5092 --if retroactive
5093 IF l_retroactive = 'Y' THEN
5094 OPEN c_volume_detail(l_orderLineIdTbl(i),l_source_code);
5095 FETCH c_volume_detail INTO l_cust_account_id,l_ship_to,l_bill_to;
5096 CLOSE c_volume_detail;
5097
5098 OPEN c_all_cust_orders(p_qp_list_header_id,l_cust_account_id);
5099 FETCH c_all_cust_orders INTO l_total_ordered_qty;
5100 CLOSE c_all_cust_orders;
5101
5102 l_volume:=NVL(l_total_ordered_qty,0);
5103
5104 ELSE
5105
5106 OPEN c_volume_detail(l_orderLineIdTbl(i),l_source_code);
5107 FETCH c_volume_detail INTO l_cust_account_id,l_ship_to,l_bill_to;
5108 CLOSE c_volume_detail;
5109
5110 OPEN c_all_cust_orders2(p_qp_list_header_id,l_cust_account_id,l_glDateTbl(i));
5111 FETCH c_all_cust_orders2 INTO l_total_ordered_qty;
5112 CLOSE c_all_cust_orders2;
5113
5114 --l_volume:=NVL(l_volume,0)+NVL(l_line_ordered_qty,0);
5115
5116 l_volume:=NVL(l_total_ordered_qty,0);
5117
5118
5119 END IF;
5120
5121 write_conc_log(' l_volume: '|| l_volume );
5122
5123 OPEN c_discount_header(l_pbh_line_id);
5124 FETCH c_discount_header INTO l_discount_type,l_volume_type;
5125 CLOSE c_discount_header;
5126
5127 -- fix for bug 6345305
5128 IF l_volume_type = 'PRICING_ATTRIBUTE12' THEN
5129 l_volume := l_volume * l_selling_price;
5130 END IF;
5131
5132 OPEN c_current_discount(l_volume,l_pbh_line_id);
5133 FETCH c_current_discount INTO l_new_discount;
5134 CLOSE c_current_discount;
5135
5136 write_conc_log(' l_volume_type: '|| l_volume_type );
5137 write_conc_log(' l_discount_type: '|| l_discount_type );
5138 write_conc_log(' l_new_discount: '|| l_new_discount );
5139
5140 IF l_new_discount is NULL THEN
5141 OPEN c_get_tier_limits(l_pbh_line_id);
5142 FETCH c_get_tier_limits INTO l_min_tier,l_max_tier;
5143 CLOSE c_get_tier_limits;
5144 write_conc_log(' l_min_tier: '|| l_min_tier );
5145 write_conc_log(' l_max_tier: '|| l_max_tier );
5146 write_conc_log(' l_volume: '|| l_volume );
5147 IF l_volume < l_min_tier THEN
5148 l_new_discount := 0;
5149 ELSE
5150 OPEN c_get_max_tier(l_max_tier,l_pbh_line_id);
5151 FETCH c_get_max_tier INTO l_new_discount;
5152 CLOSE c_get_max_tier;
5153
5154 END IF;
5155 IF G_DEBUG THEN
5156 ozf_utility_pvt.debug_message(' l_new_discount: '|| l_new_discount );
5157 END IF;
5158 write_conc_log(' l_new_discount: '|| l_new_discount );
5159 END IF;
5160
5161
5162 write_conc_log(' l_selling_price: '|| l_selling_price );
5163 IF l_volume_type = 'PRICING_ATTRIBUTE12' THEN -- volume type = AMOUNT
5164 l_value := NVL(l_line_ordered_qty,0) * l_selling_price ;
5165 ELSE
5166 l_value := NVL(l_line_ordered_qty,0) ;
5167 END IF;
5168
5169 write_conc_log(' l_value: '|| l_value );
5170 write_conc_log(' l_retroactive: '|| l_retroactive );
5171 write_conc_log(' l_volume_type: '|| l_volume_type );
5172 write_conc_log(' l_discount_type: '|| l_discount_type );
5173 write_conc_log(' l_selling_price: '|| l_selling_price );
5174
5175 l_preset_tier := NULL;
5176
5177 OPEN c_preset_tier(l_pbh_line_id,p_qp_list_header_id,l_group_id);
5178 FETCH c_preset_tier INTO l_preset_tier;
5179 CLOSE c_preset_tier;
5180
5181 write_conc_log( ' l_preset_tier=' || l_preset_tier);
5182 write_conc_log( ' l_new_discount=' || l_new_discount);
5183
5184
5185 IF l_preset_tier is NOT NULL AND l_preset_tier > l_new_discount THEN
5186 l_new_discount := l_preset_tier;
5187 IF G_DEBUG THEN
5188 ozf_utility_pvt.debug_message('not reach preset tier: ');
5189 END IF;
5190 write_conc_log(' not reach preset tier:');
5191 END IF;
5192
5193
5194 IF l_retroactive = 'Y' THEN
5195 IF l_discount_type = '%' THEN
5196 IF l_volume_type = 'PRICING_ATTRIBUTE12' THEN
5197 l_new_utilization := l_value* l_new_discount / 100;
5198 ELSE -- % is for unit price. need to multiple when range in quantity.
5199 l_new_utilization := l_value* l_selling_price * l_new_discount / 100;
5200 END IF;
5201 ELSIF l_discount_type = 'AMT' THEN
5202 IF l_volume_type = 'PRICING_ATTRIBUTE12' THEN
5203 -- amt is for unit pirce. need to divide when range in amount.
5204 l_new_utilization :=l_value / l_selling_price * l_new_discount ;
5205 ELSE
5206 l_new_utilization :=l_value * l_new_discount ;
5207 END IF;
5208 END IF;
5209 END IF; --l_retroactive
5210
5211
5212 IF NVL(l_retroactive, 'N') = 'N' THEN
5213
5214 l_new_utilization := 0;
5215
5216 IF l_included_vol_flag = 'Y' THEN
5217 l_previous_tier_max := l_volume;
5218 ELSE
5219 /*
5220 logic here is to add current order line's volume to offer's volume for adjustment.
5221 eg: offer's volume=2.
5222 order line's volume = 5, then total volume = 7.
5223 */
5224 l_previous_tier_max :=l_line_ordered_qty + l_volume ;
5225 END IF;
5226
5227 IF G_DEBUG THEN
5228 ozf_utility_pvt.debug_message( ' l_line_ordered_qty=' || l_line_ordered_qty);
5229 END IF;
5230 write_conc_log( ' l_line_ordered_qty=' || l_line_ordered_qty);
5231 --fix for bug 6021538
5232
5233 IF l_max_tier IS NULL THEN
5234 OPEN c_get_tier_limits(l_pbh_line_id);
5235 FETCH c_get_tier_limits INTO l_min_tier,l_max_tier;
5236 CLOSE c_get_tier_limits;
5237 END IF;
5238
5239 write_conc_log( ' l_value=' || l_value);
5240 write_conc_log( ' l_volume=' || l_volume);
5241 write_conc_log( ' l_max_tier=' || l_max_tier);
5242
5243
5244 IF l_volume > l_max_tier THEN
5245 l_line_ordered_qty:= l_max_tier -l_volume + l_line_ordered_qty;
5246 IF l_line_ordered_qty<0 THEN
5247 l_line_ordered_qty:=0;
5248 END IF;
5249 END IF;
5250
5251 --end bug 6021538
5252
5253 l_preset_tier := NULL;
5254
5255
5256
5257 OPEN c_prior_tiers(l_pbh_line_id, l_volume);
5258 LOOP
5259 FETCH c_prior_tiers INTO l_current_offer_tier_id,l_current_min_tier,l_current_max_tier,l_current_tier_value;
5260 EXIT WHEN c_prior_tiers%NOTFOUND;
5261
5262 write_conc_log( ' l_current_offer_tier_id=' || l_current_offer_tier_id);
5263
5264
5265 OPEN c_preset_tier(l_pbh_line_id,p_qp_list_header_id,l_group_id);
5266 FETCH c_preset_tier INTO l_preset_tier;
5267 CLOSE c_preset_tier;
5268
5269 write_conc_log( ' l_preset_tier=' || l_preset_tier);
5270 write_conc_log( ' l_current_tier_value=' || l_current_tier_value);
5271
5272
5273 IF l_preset_tier is NOT NULL AND l_preset_tier > l_current_tier_value THEN
5274 l_current_tier_value := l_preset_tier;
5275 IF G_DEBUG THEN
5276 ozf_utility_pvt.debug_message('not reach preset tier: ');
5277 END IF;
5278 write_conc_log(' not reach preset tier:');
5279 END IF;
5280
5281
5282
5283 y1 := LEAST((l_previous_tier_max-l_current_min_tier),l_line_ordered_qty) ;
5284 l_line_ordered_qty := l_line_ordered_qty - y1;
5285 IF l_discount_type = '%' THEN
5286 IF l_volume_type = 'PRICING_ATTRIBUTE12' THEN
5287 l_new_utilization := l_new_utilization + y1* l_current_tier_value / 100;
5288 ELSE
5289 l_new_utilization := l_new_utilization + y1* l_selling_price * l_current_tier_value / 100;
5290 END IF;
5291 ELSIF l_discount_type = 'AMT' THEN
5292 IF l_volume_type = 'PRICING_ATTRIBUTE12' THEN
5293 l_new_utilization := l_new_utilization + y1 / l_selling_price * l_current_tier_value ;
5294 ELSE
5295 l_new_utilization := l_new_utilization + y1* l_current_tier_value ;
5296 END IF;
5297 END IF;
5298
5299 --l_previous_tier_max := l_current_min_tier - 1 ;
5300 l_previous_tier_max := l_current_min_tier;
5301
5302 IF G_DEBUG THEN
5303 ozf_utility_pvt.debug_message(' retroactive flag is N, computing for prior tier id=' || l_current_offer_tier_id
5304 || ' y1=' || y1 || ' tier_min=' || l_current_min_tier
5305 || ' tier_max=' || l_current_max_tier || ' l_previous_tier_max: ' || l_previous_tier_max
5306 || ' l_new_utilization: ' || l_utilization_amount);
5307 END IF;
5308 write_conc_log(' retroactive flag is N, computing for prior tier id=' || l_current_offer_tier_id
5309 || ' y1=' || y1 || ' tier_min=' || l_current_min_tier
5310 || ' tier_max=' || l_current_max_tier || ' l_previous_tier_max: ' || l_previous_tier_max
5311 || ' l_new_utilization: ' || l_utilization_amount);
5312
5313 EXIT WHEN l_line_ordered_qty <= 0;
5314
5315 END LOOP; -- end of loop for c_prior_tiers
5316 CLOSE c_prior_tiers;
5317
5318 END IF; -- IF NVL(l_retroactive, 'N') = 'N' THEN
5319
5320
5321 write_conc_log(' l_amountTbl(i): '|| l_amountTbl(i) );
5322 write_conc_log(' l_new_utilization: '|| l_new_utilization );
5323
5324 --IF l_amountTbl(i)<= l_new_utilization THEN
5325
5326 l_adj_amount := l_new_utilization - l_amountTbl(i);
5327
5328 write_conc_log(' l_adj_amount: '|| l_adj_amount );
5329
5330 OPEN c_offer_info (p_qp_list_header_id);
5331 FETCH c_offer_info INTO l_offer_info;
5332 CLOSE c_offer_info;
5333
5334 OPEN c_order_line_details (l_orderLineIdTbl(i));
5335 FETCH c_order_line_details into l_invoice_to_org_id, l_ship_to_org_id;
5336 CLOSE c_order_line_details;
5337
5338 write_conc_log(' l_invoice_to_org_id: '|| l_invoice_to_org_id );
5339 write_conc_log(' l_ship_to_org_id: '|| l_ship_to_org_id );
5340
5341
5342
5343 --create records
5344 l_act_budgets_rec.act_budget_used_by_id := p_qp_list_header_id;
5345 l_act_budgets_rec.arc_act_budget_used_by := 'OFFR';
5346 l_act_budgets_rec.budget_source_type := 'OFFR';
5347 l_act_budgets_rec.budget_source_id := p_qp_list_header_id;
5348 l_act_budgets_rec.request_currency := l_offer_info.transaction_currency_code;
5349 l_act_budgets_rec.request_date := SYSDATE;
5350 l_act_budgets_rec.status_code := 'APPROVED';
5351 l_act_budgets_rec.user_status_id := ozf_Utility_Pvt.get_default_user_status (
5352 'OZF_BUDGETSOURCE_STATUS', l_act_budgets_rec.status_code);
5353 l_act_budgets_rec.approved_in_currency := l_offer_info.transaction_currency_code;
5354 l_act_budgets_rec.approval_date := SYSDATE;
5355 l_act_budgets_rec.approver_id := ozf_utility_pvt.get_resource_id (fnd_global.user_id);
5356 l_act_budgets_rec.justification := 'Offer adjustment before offer start date';
5357 l_act_budgets_rec.transfer_type := 'UTILIZED';
5358
5359 l_act_util_rec.utilization_type :='ADJUSTMENT';
5360 l_act_util_rec.product_level_type := 'PRODUCT';
5361 l_act_util_rec.adjustment_date := SYSDATE;
5362 l_act_util_rec.cust_account_id := l_offer_info.beneficiary_account_id;
5363 l_act_util_rec.ship_to_site_use_id := l_ship_to_org_id;
5364 l_act_util_rec.bill_to_site_use_id := l_invoice_to_org_id;
5365
5366 l_act_util_rec.product_id := l_order_line_info.inventory_item_id;
5367 l_act_util_rec.object_type :='ORDER';
5368 l_act_util_rec.object_id := l_order_line_info.header_id;
5369 l_act_util_rec.order_line_id := l_order_line_id;
5370 l_act_util_rec.price_adjustment_id := -1;
5371 l_act_util_rec.orig_utilization_id:= -1;
5372
5373
5374 write_conc_log(' l_order_line_info.header_id: '|| l_order_line_info.header_id );
5375 OPEN c_cust_number (l_order_line_info.header_id);
5376 FETCH c_cust_number INTO l_cust_number;
5377 CLOSE c_cust_number;
5378
5379 l_act_util_rec.billto_cust_account_id := l_cust_number;
5380
5381 IF l_offer_info.beneficiary_account_id IS NULL THEN
5382 l_act_util_rec.cust_account_id := l_cust_number;
5383 END IF;
5384
5385
5386 -- this adjustment is to adjust utilized amount in all cases so its not backdated adjustment
5387 -- also it is not for earned so it is not volume offer adjustment either.
5388 -- set to backdated until decision is made.
5389 IF l_adj_amount > 0 THEN
5390 l_act_util_rec.adjustment_type :='STANDARD'; -- Seeded Data for Backdated Positive Adj
5391 l_act_util_rec.adjustment_type_id := -5; -- Seeded Data for Backdated Positive Adj
5392 ELSE
5393 l_act_util_rec.adjustment_type :='DECREASE_EARNED'; -- Seeded Data for Backdated Negative Adj
5394 l_act_util_rec.adjustment_type_id := -4; -- Seeded Data for Backdated Negative Adj
5395 END IF;
5396
5397 l_act_util_rec.gl_posted_flag:= 'N';
5398
5399 l_act_budgets_rec.request_amount := l_adj_amount;
5400 l_act_budgets_rec.approved_amount := l_adj_amount;
5401
5402 ----------------------
5403
5404
5405 IF NVL(l_adj_amount,0) <> 0 THEN
5406 ozf_fund_adjustment_pvt.process_act_budgets(x_return_status => x_return_status
5407 ,x_msg_count => x_msg_count
5408 ,x_msg_data => x_msg_data
5409 ,p_act_budgets_rec => l_act_budgets_rec
5410 ,p_act_util_rec => l_act_util_rec
5411 ,x_act_budget_id => l_act_budget_id
5412 );
5413
5414 write_conc_log('process_act_budgets returns: ' || x_return_status);
5415 IF l_return_status = fnd_api.g_ret_sts_error THEN
5416 RAISE fnd_api.g_exc_error;
5417 ELSIF l_return_status = fnd_api.g_ret_sts_unexp_error THEN
5418 RAISE fnd_api.g_exc_unexpected_error;
5419 END IF;
5420 END IF;
5421 -- END IF;
5422
5423 <<l_endoffloop>>
5424 NULL;
5425 END LOOP; -- loop for For
5426 EXIT WHEN c_old_price_adj%NOTFOUND;
5427 END LOOP; -- end price adj loop
5428
5429 CLOSE c_old_price_adj;
5430 END IF;
5431
5432 END volume_offer_util_adjustment;
5433
5434
5435
5436
5437 END ozf_adjustment_ext_pvt;
5438