[Home] [Help]
PACKAGE BODY: APPS.OZF_VOLUME_CALCULATION_PUB
Source
1 PACKAGE BODY OZF_VOLUME_CALCULATION_PUB AS
2 /* $Header: ozfpvocb.pls 120.22.12010000.3 2008/11/24 17:06:03 nirprasa ship $ */
3 --
4 -- NAME
5 -- OZF_VOLUME_CALCULATION_PUB
6 --
7 -- HISTORY
8 -- 02/23/2007 kdass fixed bug 5754500 - if the order_line_id is being passed as a result
9 -- of a split, then use split_from_line_id in the call to the cursor.
10 -- 04/20/2007 inanaiah fixed bug 5975678 - handled creation/update of ozf_order_group_prod records for Splits
11 -- 05/10/2007 nirprasa fixed bug 6021635 - changed cursor c_offer_id_om for backdated adjustments created for booked orders.
12 -- 05/14/2007 kdass fixed bug 6008340
13 -- 06/22/2007 nirprasa fixed bug 6140749 - changed OE_ORDER_LINES to OE_ORDER_LINES_ALL
14 -- 09/19/2008 nirprasa fixed bug 6998502 - VOLUME OFFERS ARE NOT APPLIED CORRECTLY ON A SALES ORDER
15 -- 09/19/2008 nirprasa fixed bug 7353241 - VOLUME OFFER CALCULATIONS INCORRECT FOR SINGLE UNIT ACCRUALS
16 -- 11/24/2008 nirprasa fixed bug 7030415 - R12SIP WE CAN'T SETUP CURRENY CONVERSION TYPE FOR SPECIFIC OPERATING UNIT
17 ------------------------------------------------------------------------------
18
19 G_PKG_NAME CONSTANT VARCHAR2(30):='OZF_VOLUME_CALCULATION_PUB';
20 G_FILE_NAME CONSTANT VARCHAR2(12):='ozfpvocb.pls';
21
22
23 PROCEDURE get_group_pbh_prod
24 (
25 p_offer_id IN NUMBER
26 ,p_list_header_id IN NUMBER
27 ,p_list_line_id IN NUMBER
28 ,p_req_line_attrs_tbl IN QP_RUNTIME_SOURCE.accum_req_line_attrs_tbl
29 ,p_order_line_id IN NUMBER
30 ,x_group_no OUT NOCOPY NUMBER
31 ,x_vol_track_type OUT NOCOPY VARCHAR2
32 ,x_combine_schedule OUT NOCOPY VARCHAR2
33 ,x_pbh_line_id OUT NOCOPY NUMBER
34 ,x_pord_attribute OUT NOCOPY VARCHAR2
35 ,x_prod_attr_value OUT NOCOPY VARCHAR2
36 ,x_indirect_flag OUT NOCOPY VARCHAR2
37 )
38 IS
39 CURSOR c_precedence(p_group_no NUMBER) IS
40 SELECT precedence, offer_market_option_id
41 FROM ozf_offr_market_options
42 WHERE group_number = p_group_no
43 AND qp_list_header_id = p_list_header_id;
44
45 CURSOR c_market_options(p_offer_market_option_id NUMBER) IS
46 SELECT combine_schedule_flag, volume_tracking_level_code
47 FROM ozf_offr_market_options
48 WHERE offer_id = p_offer_id
49 AND offer_market_option_id = p_offer_market_option_id;
50 -- above cursors can be combined. offer_id is not needed as qp_list_header_id is present in table
51 CURSOR c_pbh_line_id IS
52 SELECT offer_discount_line_id
53 FROM ozf_qp_discounts
54 WHERE list_line_id = p_list_line_id;
55
56 CURSOR c_existing_values(p_indirect_flag VARCHAR2,l_order_line_id NUMBER) IS
57 SELECT group_no, volume_track_type, combine_schedule_yn, pbh_line_id, prod_attribute, prod_attr_value
58 FROM ozf_order_group_prod
59 WHERE order_line_id = l_order_line_id
60 AND indirect_flag = p_indirect_flag
61 AND offer_id = p_offer_id;
62
63 l_precedence NUMBER := fnd_api.g_miss_num;
64 l_dummy1 NUMBER;
65 l_dummy2 NUMBER;
66 l_offer_market_option_id NUMBER;
67 l_vol_track_type VARCHAR2(30);
68 l_combine_schedule VARCHAR2(1);
69 l_split_from_line_id NUMBER;
70 l_group_prod_order_line_id NUMBER;
71 l_api_name CONSTANT VARCHAR2(30) := 'get_group_pbh_prod';
72 BEGIN
73 SAVEPOINT get_group_pbh_prod;
74
75 IF ozf_order_price_pvt.g_resale_line_tbl.COUNT = 0 THEN
76 x_indirect_flag := 'O';
77 ELSE
78 IF ozf_order_price_pvt.g_resale_line_tbl(1).resale_table_type = 'IFACE' THEN
79 x_indirect_flag := 'I';
80 ELSIF ozf_order_price_pvt.g_resale_line_tbl(1).resale_table_type = 'RESALE' THEN
81 x_indirect_flag := 'R';
82 END IF;
83 END IF;
84
85 x_group_no := -9999;
86
87 --kdass fixed bug 6008340
88 l_group_prod_order_line_id := p_order_line_id;
89
90 IF x_indirect_flag = 'O' THEN
91 select split_from_line_id into l_split_from_line_id from OE_ORDER_LINES_ALL where line_id = p_order_line_id;
92 IF (l_split_from_line_id IS NOT NULL) THEN
93 l_group_prod_order_line_id := l_split_from_line_id;
94 END IF;
95 END IF;
96
97 IF p_req_line_attrs_tbl.COUNT = 0 THEN
98 OPEN c_existing_values(x_indirect_flag,l_group_prod_order_line_id);
99 FETCH c_existing_values INTO x_group_no, x_vol_track_type, x_combine_schedule, x_pbh_line_id, x_pord_attribute, x_prod_attr_value;
100 CLOSE c_existing_values;
101 ELSE
102 FOR i IN p_req_line_attrs_tbl.FIRST..p_req_line_attrs_tbl.LAST LOOP
103 IF p_req_line_attrs_tbl(i).attribute_type = 'PRODUCT' THEN
104 x_pord_attribute := p_req_line_attrs_tbl(i).attribute;
105 x_prod_attr_value := p_req_line_attrs_tbl(i).value;
106 ELSIF p_req_line_attrs_tbl(i).attribute_type = 'QUALIFIER' THEN
107 OPEN c_precedence(p_req_line_attrs_tbl(i).grouping_no);
108 FETCH c_precedence INTO l_dummy1, l_dummy2;
109 CLOSE c_precedence;
110
111 IF l_dummy1 < l_precedence THEN
112 l_precedence := l_dummy1;
113 l_offer_market_option_id := l_dummy2;
114 x_group_no := p_req_line_attrs_tbl(i).grouping_no;
115 END IF;
116 /*
117 IF p_req_line_attrs_tbl(i).attribute IN ('DISTRIBUTOR', 'DISTRIBUTOR_LIST', 'DISTRIBUTOR_SEGMENT', 'DISTRIBUTOR_TERRITORY') OR p_req_line_attrs_tbl(i).value = 'INDIRECT' THEN
118 x_indirect_flag := 'Y';
119 END IF;*/
120 END IF;
121 END LOOP;
122
123 IF x_group_no = -9999 THEN
124 x_combine_schedule := 'N';
125 x_vol_track_type := 'ACCOUNT';
126 ELSE
127 OPEN c_market_options(l_offer_market_option_id);
128 FETCH c_market_options INTO x_combine_schedule, x_vol_track_type;
129 CLOSE c_market_options;
130 END IF;
131
132 OPEN c_pbh_line_id;
133 FETCH c_pbh_line_id INTO x_pbh_line_id;
134 CLOSE c_pbh_line_id;
135 END IF;
136
137 EXCEPTION
138 WHEN OTHERS THEN
139 ROLLBACK TO get_group_pbh_prod;
140 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
141 THEN
142 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
143 END IF;
144
145 END get_group_pbh_prod;
146
147
148 PROCEDURE insert_volume(
149 p_init_msg_list IN VARCHAR2
150 ,p_api_version IN NUMBER
151 ,p_commit IN VARCHAR2
152 ,x_return_status OUT NOCOPY VARCHAR2
153 ,x_msg_count OUT NOCOPY NUMBER
154 ,x_msg_data OUT NOCOPY VARCHAR2
155 ,p_volume_detail_rec IN ozf_sales_transactions_pvt.sales_transaction_rec_type
156 ,p_qp_list_header_id IN NUMBER
157 ,p_offer_id IN NUMBER
158 ,p_indirect_flag IN VARCHAR2
159 ,p_sign IN NUMBER)
160 IS
161 CURSOR c_group_prod(p_offer_id NUMBER, p_line_id NUMBER, p_indirect_flag VARCHAR2) IS
162 SELECT group_no, volume_track_type, combine_schedule_yn, pbh_line_id, volume_type, include_volume_flag
163 FROM ozf_order_group_prod
164 WHERE offer_id = p_offer_id
165 AND order_line_id = p_line_id
166 AND indirect_flag = p_indirect_flag;
167
168 CURSOR c_group_volume_exists(p_offer_id NUMBER, p_group_no NUMBER, p_pbh_lind_id NUMBER) IS
169 SELECT 'Y'
170 FROM ozf_volume_summary
171 WHERE offer_id = p_offer_id
172 AND group_no = p_group_no
173 AND pbh_line_id = p_pbh_lind_id;
174
175 CURSOR c_individual_volume_exists(p_offer_id NUMBER, p_individual_type VARCHAR2, p_individual_id NUMBER, p_pbh_line_id NUMBER) IS
176 SELECT 'Y'
177 FROM ozf_volume_summary
178 WHERE offer_id = p_offer_id
179 AND individual_type = p_individual_type
180 AND individual_id = p_individual_id
181 AND pbh_line_id = p_pbh_line_id;
182
183 CURSOR c_pbh_lines(p_offer_id NUMBER) IS
184 SELECT offer_discount_line_id
185 FROM ozf_offer_discount_lines
186 WHERE offer_id = p_offer_id
187 AND tier_type = 'PBH';
188
189 CURSOR c_line_processed(p_offer_id NUMBER, p_source_code VARCHAR2, p_line_id NUMBER) IS
190 SELECT 'Y'
191 FROM ozf_volume_detail
192 WHERE offer_id = p_offer_id
193 AND source_code = p_source_code
194 AND order_line_id = p_line_id;
195
196 CURSOR c_currency_code(p_offer_id NUMBER) IS
197 SELECT NVL(transaction_currency_code, fund_request_curr_code)
198 FROM ozf_offers
199 WHERE offer_id = p_offer_id;
200
201 CURSOR c_order_line_type(p_line_id NUMBER) IS
202 SELECT reference_header_id, reference_line_id, line_category_code, return_context, return_attribute1, return_attribute2
203 FROM oe_order_lines_all
204 WHERE line_id = p_line_id;
205 l_order_line_type c_order_line_type%ROWTYPE;
206
207 CURSOR c_rma_ref_line_detail(p_offer_id NUMBER, p_line_id NUMBER, p_indirect_flag VARCHAR2) IS
208 SELECT offer_id, qp_list_header_id, group_no, volume_track_type, combine_schedule_yn, pbh_line_id, volume_type, prod_attribute, prod_attr_value, apply_discount_flag, include_volume_flag, indirect_flag
209 FROM ozf_order_group_prod
210 WHERE offer_id = p_offer_id
211 AND order_line_id = p_line_id
212 AND indirect_flag = p_indirect_flag;
213 l_rma_ref_line_detail c_rma_ref_line_detail%ROWTYPE;
214
215 --Added for bug 7030415
216 CURSOR c_get_conversion_type( p_org_id IN NUMBER) IS
217 SELECT exchange_rate_type
218 FROM ozf_sys_parameters_all
219 WHERE org_id = p_org_id;
220
221 l_group_no NUMBER;
222 l_volume_track_type VARCHAR2(30);
223 l_combine_schedule_yn VARCHAR2(1);
224 l_pbh_line_id NUMBER;
225 l_split_from_line_id NUMBER;
226 l_volume_type VARCHAR2(30);
227 l_include_volume VARCHAR2(1);
228 l_current_volume NUMBER;
229 l_volume_exists VARCHAR2(1);
230 l_line_processed VARCHAR2(1) := 'N';
231 l_api_name CONSTANT VARCHAR2(30) := 'insert_volume';
232 l_currency_code VARCHAR2(15);
233 l_convert_amt NUMBER;
234 l_return_status VARCHAR2(1);
235 l_exchange_rate_type VARCHAR2(30) := FND_API.G_MISS_CHAR;
236 l_rate NUMBER;
237 BEGIN
238 SAVEPOINT create_volume;
239 x_return_status := Fnd_Api.g_ret_sts_success;
240 ozf_utility_pvt.write_conc_log('========================= Insert Volume =========================');
241 ozf_utility_pvt.write_conc_log('offer_id: ' || p_offer_id);
242
243 IF p_offer_id IS NOT NULL THEN
244 OPEN c_line_processed(p_offer_id, p_volume_detail_rec.source_code, p_volume_detail_rec.line_id);
245 FETCH c_line_processed INTO l_line_processed;
246 CLOSE c_line_processed;
247 ozf_utility_pvt.write_conc_log('line processed ' || l_line_processed);
248 IF l_line_processed = 'N' THEN
249 IF p_volume_detail_rec.source_code = 'OM' THEN
250 OPEN c_order_line_type(p_volume_detail_rec.line_id);
251 FETCH c_order_line_type INTO l_order_line_type;
252 CLOSE c_order_line_type;
253
254 IF l_order_line_type.line_category_code = 'RETURN' AND l_order_line_type.reference_line_id IS NOT NULL THEN -- return order with reference SO#
255 OPEN c_rma_ref_line_detail(p_offer_id, l_order_line_type.reference_line_id, p_indirect_flag);
256 FETCH c_rma_ref_line_detail INTO l_rma_ref_line_detail;
257 CLOSE c_rma_ref_line_detail;
258
259 INSERT INTO ozf_order_group_prod
260 (
261 order_group_prod_id
262 ,creation_date
263 ,created_by
264 ,last_update_date
265 ,last_updated_by
266 ,last_update_login
267 ,order_line_id
268 ,offer_id
269 ,qp_list_header_id
270 ,group_no
271 ,volume_track_type
272 ,combine_schedule_yn
273 ,pbh_line_id
274 ,volume_type
275 ,prod_attribute
276 ,prod_attr_value
277 ,apply_discount_flag
278 ,include_volume_flag
279 ,indirect_flag
280 )
281 VALUES
282 ( ozf_order_group_prod_s.NEXTVAL
283 ,SYSDATE
284 ,FND_GLOBAL.user_id
285 ,SYSDATE
286 ,FND_GLOBAL.user_id
287 ,FND_GLOBAL.conc_login_id
288 ,p_volume_detail_rec.line_id
289 ,l_rma_ref_line_detail.offer_id
290 ,l_rma_ref_line_detail.qp_list_header_id
291 ,l_rma_ref_line_detail.group_no
292 ,l_rma_ref_line_detail.volume_track_type
293 ,l_rma_ref_line_detail.combine_schedule_yn
294 ,l_rma_ref_line_detail.pbh_line_id
295 ,l_rma_ref_line_detail.volume_type
296 ,l_rma_ref_line_detail.prod_attribute
297 ,l_rma_ref_line_detail.prod_attr_value
298 ,l_rma_ref_line_detail.apply_discount_flag
299 ,l_rma_ref_line_detail.include_volume_flag
300 ,l_rma_ref_line_detail.indirect_flag
301 );
302 END IF;
303 END IF;
304
305 --ozf_utility_pvt.write_conc_log('offer_id: ' || p_offer_id);
306
307 --kdass fixed bug 6008340
308 OPEN c_group_prod(p_offer_id, p_volume_detail_rec.line_id, p_indirect_flag);
309 FETCH c_group_prod INTO l_group_no, l_volume_track_type, l_combine_schedule_yn, l_pbh_line_id, l_volume_type, l_include_volume;
310 CLOSE c_group_prod;
311
312 IF p_indirect_flag = 'O' THEN
313 select split_from_line_id into l_split_from_line_id from OE_ORDER_LINES_ALL where line_id = p_volume_detail_rec.line_id;
314 IF (l_split_from_line_id IS NOT NULL) THEN
315 ozf_utility_pvt.write_conc_log('split_from_line_id: ' || l_split_from_line_id);
316 OPEN c_group_prod(p_offer_id, l_split_from_line_id, p_indirect_flag);
317 FETCH c_group_prod INTO l_group_no, l_volume_track_type, l_combine_schedule_yn, l_pbh_line_id, l_volume_type, l_include_volume;
318 CLOSE c_group_prod;
319 END IF;
320 END IF;
321
322 ozf_utility_pvt.write_conc_log('group_no: ' || l_group_no);
323 ozf_utility_pvt.write_conc_log('volume_track_type ' || l_volume_track_type);
324 ozf_utility_pvt.write_conc_log('combine_schedule_yn ' || l_combine_schedule_yn);
325 ozf_utility_pvt.write_conc_log('pbh_line_id ' || l_pbh_line_id);
326 ozf_utility_pvt.write_conc_log('volume_type ' || l_volume_type);
327 ozf_utility_pvt.write_conc_log('include_volume ' || l_include_volume);
328 ozf_utility_pvt.write_conc_log('p_sign ' || p_sign);
329 ozf_utility_pvt.write_conc_log('quantity ' || p_volume_detail_rec.quantity);
330 ozf_utility_pvt.write_conc_log('amount ' || p_volume_detail_rec.amount);
331
332 IF l_volume_type = 'PRICING_ATTRIBUTE10' THEN
333 IF p_volume_detail_rec.source_code = 'IS' THEN
334 l_current_volume := p_volume_detail_rec.quantity;
335 ELSE
336 l_current_volume := p_sign * p_volume_detail_rec.quantity;
337 END IF;
338 ELSIF l_volume_type = 'PRICING_ATTRIBUTE12' THEN
339 OPEN c_currency_code(p_offer_id);
340 FETCH c_currency_code INTO l_currency_code;
341 CLOSE c_currency_code;
342
343 IF l_currency_code <> p_volume_detail_rec.currency_code THEN
344 --Added for bug 7030415
345 OPEN c_get_conversion_type(p_volume_detail_rec.org_id);
346 FETCH c_get_conversion_type INTO l_exchange_rate_type;
347 CLOSE c_get_conversion_type;
348
349 ozf_utility_pvt.convert_currency(x_return_status => l_return_status
350 ,p_from_currency => p_volume_detail_rec.currency_code
351 ,p_to_currency => l_currency_code
352 ,p_conv_type => l_exchange_rate_type --nirprasa, Added for bug 7030415
353 ,p_conv_date => p_volume_detail_rec.transaction_date
354 ,p_from_amount => p_volume_detail_rec.amount
355 ,x_to_amount => l_convert_amt
356 ,x_rate => l_rate); --7030415
357
358 IF l_return_status <> fnd_api.g_ret_sts_success THEN
359 ozf_utility_pvt.write_conc_log('Convert Currency failed');
360 RAISE Fnd_Api.g_exc_unexpected_error;
361 END IF;
362
363 IF p_volume_detail_rec.source_code = 'OM' THEN
364 l_current_volume := l_convert_amt; -- for OM return order, amount is already negative
365 ELSE
366 l_current_volume := p_sign * l_convert_amt; -- for IDSM amount is always positive. need to convert for retrun order
367 END IF;
368 ELSE
369 IF p_volume_detail_rec.source_code = 'OM' THEN
370 l_current_volume := p_volume_detail_rec.amount; -- for OM return order, amount is already negative
371 ELSE
372 l_current_volume := p_sign * p_volume_detail_rec.amount; -- for IDSM amount is always positive. need to convert for retrun order
373 END IF;
374 END IF;
375 END IF;
376 ozf_utility_pvt.write_conc_log('l_current_volume ' || l_current_volume);
377 IF l_include_volume = 'Y' THEN
378 -- process volume detail
379 INSERT INTO ozf_volume_detail
380 (
381 volume_detail_id
382 ,creation_date
383 ,created_by
384 ,last_update_date
385 ,last_updated_by
386 ,last_update_login
387 ,offer_id
388 ,qp_list_header_id
389 ,distributor_acct_id
390 ,cust_account_id
391 ,bill_to_site_use_id
392 ,ship_to_site_use_id
393 ,inventory_item_id
394 ,volume_type
395 ,uom_code
396 ,currency_code
397 ,volume
398 ,group_no
399 ,volume_track_type
400 ,order_line_id
401 ,transaction_date
402 ,pbh_line_id
403 ,include_volume_flag
404 ,source_code
405 )
406 VALUES
407 (
408 ozf_volume_detail_s.NEXTVAL
409 ,SYSDATE
410 ,FND_GLOBAL.user_id
411 ,SYSDATE
412 ,FND_GLOBAL.user_id
413 ,FND_GLOBAL.conc_login_id
414 ,p_offer_id
415 ,p_qp_list_header_id
416 ,p_volume_detail_rec.sold_from_cust_account_id
417 ,p_volume_detail_rec.sold_to_cust_account_id
418 ,p_volume_detail_rec.bill_to_site_use_id
419 ,p_volume_detail_rec.ship_to_site_use_id
420 ,p_volume_detail_rec.inventory_item_id
421 ,l_volume_type
422 ,p_volume_detail_rec.uom_code
423 ,l_currency_code
424 ,l_current_volume
425 ,l_group_no
426 ,l_volume_track_type
427 ,p_volume_detail_rec.line_id
428 ,p_volume_detail_rec.transaction_date
429 ,l_pbh_line_id
430 ,l_include_volume
431 ,p_volume_detail_rec.source_code
432 );
433
434 -- process volume summary
435 -- 1. group's volume
436 IF l_volume_track_type = 'GROUP' THEN
437 l_volume_exists := 'N';
438 OPEN c_group_volume_exists(p_offer_id, l_group_no, l_pbh_line_id);
439 FETCH c_group_volume_exists INTO l_volume_exists;
440 CLOSE c_group_volume_exists;
441 ozf_utility_pvt.write_conc_log('group ' || l_volume_exists);
442 IF l_volume_exists = 'Y' THEN -- update group's volume
443 IF l_combine_schedule_yn = 'Y' THEN -- update all pbh lines
444 UPDATE ozf_volume_summary
445 SET group_volume = group_volume + l_current_volume,
446 last_update_date = SYSDATE,
447 last_updated_by = FND_GLOBAL.user_id,
448 last_update_login = FND_GLOBAL.conc_login_id
449 WHERE offer_id = p_offer_id
450 AND group_no = l_group_no;
451 ELSE -- update one pbh line only
452 UPDATE ozf_volume_summary
453 SET group_volume = group_volume + l_current_volume,
454 last_update_date = SYSDATE,
455 last_updated_by = FND_GLOBAL.user_id,
456 last_update_login = FND_GLOBAL.conc_login_id
457 WHERE offer_id = p_offer_id
458 AND group_no = l_group_no
459 AND pbh_line_id = l_pbh_line_id;
460 END IF;
461 ELSE -- insert group's volume
462 IF l_combine_schedule_yn = 'Y' THEN -- insert all pbh lines
463 FOR l_pbh_line IN c_pbh_lines(p_offer_id) LOOP
464 INSERT INTO ozf_volume_summary
465 (
466 volume_summary_id
467 ,creation_date
468 ,created_by
469 ,last_update_date
470 ,last_updated_by
471 ,last_update_login
472 ,offer_id
473 ,qp_list_header_id
474 ,group_no
475 ,group_volume
476 ,pbh_line_id
477 )
478 VALUES
479 (
480 ozf_volume_summary_s.NEXTVAL
481 ,SYSDATE
482 ,FND_GLOBAL.user_id
483 ,SYSDATE
484 ,FND_GLOBAL.user_id
485 ,FND_GLOBAL.conc_login_id
486 ,p_offer_id
487 ,p_qp_list_header_id
488 ,l_group_no
489 ,l_current_volume
490 ,l_pbh_line.offer_discount_line_id
491 );
492 END LOOP;
493 ELSE -- insert one pbh line
494 INSERT INTO ozf_volume_summary
495 (
496 volume_summary_id
497 ,creation_date
498 ,created_by
499 ,last_update_date
500 ,last_updated_by
501 ,last_update_login
502 ,offer_id
503 ,qp_list_header_id
504 ,group_no
505 ,group_volume
506 ,pbh_line_id
507 )
508 VALUES
509 (
510 ozf_volume_summary_s.NEXTVAL
511 ,SYSDATE
512 ,FND_GLOBAL.user_id
513 ,SYSDATE
514 ,FND_GLOBAL.user_id
515 ,FND_GLOBAL.conc_login_id
516 ,p_offer_id
517 ,p_qp_list_header_id
518 ,l_group_no
519 ,l_current_volume
520 ,l_pbh_line_id
521 );
522 END IF; -- end combine schedule
523 END IF; -- end l_volume_exists
524 END IF; -- end l_volume_track_type = 'GROUP'
525
526 -- 2. distributor's volume
527 --IF p_volume_detail_rec.sold_from_cust_account_id IS NOT NULL AND p_volume_detail_rec.sold_from_cust_account_id <> fnd_api.g_miss_num THEN
528 IF p_indirect_flag = 'R' THEN -- indirect sales
529 l_volume_exists := 'N';
530 OPEN c_individual_volume_exists(p_offer_id, 'DISTRIBUTOR', p_volume_detail_rec.sold_from_cust_account_id, l_pbh_line_id);
531 FETCH c_individual_volume_exists INTO l_volume_exists;
532 CLOSE c_individual_volume_exists;
533 ozf_utility_pvt.write_conc_log('distributor ' || l_volume_exists);
534 IF l_volume_exists = 'Y' THEN -- update distributor's volume
535 UPDATE ozf_volume_summary
536 SET individual_volume = individual_volume + l_current_volume,
537 last_update_date = SYSDATE,
538 last_updated_by = FND_GLOBAL.user_id,
539 last_update_login = FND_GLOBAL.conc_login_id
540 WHERE offer_id = p_offer_id
541 AND individual_type = 'DISTRIBUTOR'
542 AND individual_id = p_volume_detail_rec.sold_from_cust_account_id
543 AND pbh_line_id = l_pbh_line_id;
544 ELSE -- insert distributor's volume
545 INSERT INTO ozf_volume_summary
546 (
547 volume_summary_id
548 ,creation_date
549 ,created_by
550 ,last_update_date
551 ,last_updated_by
552 ,last_update_login
553 ,offer_id
554 ,qp_list_header_id
555 ,individual_type
556 ,individual_id
557 ,individual_volume
558 ,pbh_line_id
559 )
560 VALUES
561 (
562 ozf_volume_summary_s.NEXTVAL
563 ,SYSDATE
564 ,FND_GLOBAL.user_id
565 ,SYSDATE
566 ,FND_GLOBAL.user_id
567 ,FND_GLOBAL.conc_login_id
568 ,p_offer_id
569 ,p_qp_list_header_id
570 ,'DISTRIBUTOR'
571 ,p_volume_detail_rec.sold_from_cust_account_id
572 ,l_current_volume
573 ,l_pbh_line_id
574 );
575 END IF;
576 END IF; -- end distributor's volume
577
578 -- 3. customer's volume
579 l_volume_exists := 'N';
580 OPEN c_individual_volume_exists(p_offer_id, 'ACCOUNT', p_volume_detail_rec.sold_to_cust_account_id, l_pbh_line_id);
581 FETCH c_individual_volume_exists INTO l_volume_exists;
582 CLOSE c_individual_volume_exists;
583 ozf_utility_pvt.write_conc_log('account ' || l_volume_exists);
584 IF l_volume_exists = 'Y' THEN -- update customer's volume
585 UPDATE ozf_volume_summary
586 SET individual_volume = individual_volume + l_current_volume,
587 last_update_date = SYSDATE,
588 last_updated_by = FND_GLOBAL.user_id,
589 last_update_login = FND_GLOBAL.conc_login_id
590 WHERE offer_id = p_offer_id
591 AND individual_type = 'ACCOUNT'
592 AND individual_id = p_volume_detail_rec.sold_to_cust_account_id
593 AND pbh_line_id = l_pbh_line_id;
594 ELSE -- insert customer's volume
595 INSERT INTO ozf_volume_summary
596 (
597 volume_summary_id
598 ,creation_date
599 ,created_by
600 ,last_update_date
601 ,last_updated_by
602 ,last_update_login
603 ,offer_id
604 ,qp_list_header_id
605 ,individual_type
606 ,individual_id
607 ,individual_volume
608 ,pbh_line_id
609 )
610 VALUES
611 (
612 ozf_volume_summary_s.NEXTVAL
613 ,SYSDATE
614 ,FND_GLOBAL.user_id
615 ,SYSDATE
616 ,FND_GLOBAL.user_id
617 ,FND_GLOBAL.conc_login_id
618 ,p_offer_id
619 ,p_qp_list_header_id
620 ,'ACCOUNT'
621 ,p_volume_detail_rec.sold_to_cust_account_id
622 ,l_current_volume
623 ,l_pbh_line_id
624 );
625 END IF;
626
627 -- 4. bill_to's volume
628 l_volume_exists := 'N';
629 OPEN c_individual_volume_exists(p_offer_id, 'BILL_TO', p_volume_detail_rec.bill_to_site_use_id, l_pbh_line_id);
630 FETCH c_individual_volume_exists INTO l_volume_exists;
631 CLOSE c_individual_volume_exists;
632 ozf_utility_pvt.write_conc_log('bill_to ' || l_volume_exists);
633 IF l_volume_exists = 'Y' THEN -- update bill_to's volume
634 UPDATE ozf_volume_summary
635 SET individual_volume = individual_volume + l_current_volume,
636 last_update_date = SYSDATE,
637 last_updated_by = FND_GLOBAL.user_id,
638 last_update_login = FND_GLOBAL.conc_login_id
639 WHERE offer_id = p_offer_id
640 AND individual_type = 'BILL_TO'
641 AND individual_id = p_volume_detail_rec.bill_to_site_use_id
642 AND pbh_line_id = l_pbh_line_id;
643 ELSE -- insert bill_to's volume
644 INSERT INTO ozf_volume_summary
645 (
646 volume_summary_id
647 ,creation_date
648 ,created_by
649 ,last_update_date
650 ,last_updated_by
651 ,last_update_login
652 ,offer_id
653 ,qp_list_header_id
654 ,individual_type
655 ,individual_id
656 ,individual_volume
657 ,pbh_line_id
658 )
659 VALUES
660 (
661 ozf_volume_summary_s.NEXTVAL
662 ,SYSDATE
663 ,FND_GLOBAL.user_id
664 ,SYSDATE
665 ,FND_GLOBAL.user_id
666 ,FND_GLOBAL.conc_login_id
667 ,p_offer_id
668 ,p_qp_list_header_id
669 ,'BILL_TO'
670 ,p_volume_detail_rec.bill_to_site_use_id
671 ,l_current_volume
672 ,l_pbh_line_id
673 );
674 END IF;
675
676 -- 5. ship_to's volume
677 l_volume_exists := 'N';
678 OPEN c_individual_volume_exists(p_offer_id, 'SHIP_TO', p_volume_detail_rec.ship_to_site_use_id, l_pbh_line_id);
679 FETCH c_individual_volume_exists INTO l_volume_exists;
680 CLOSE c_individual_volume_exists;
681 ozf_utility_pvt.write_conc_log('ship to ' || l_volume_exists);
682 IF l_volume_exists = 'Y' THEN -- update ship_to's volume
683 UPDATE ozf_volume_summary
684 SET individual_volume = individual_volume + l_current_volume,
685 last_update_date = SYSDATE,
686 last_updated_by = FND_GLOBAL.user_id,
687 last_update_login = FND_GLOBAL.conc_login_id
688 WHERE offer_id = p_offer_id
689 AND individual_type = 'SHIP_TO'
690 AND individual_id = p_volume_detail_rec.ship_to_site_use_id
691 AND pbh_line_id = l_pbh_line_id;
692 ELSE -- insert ship_to's volume
693 INSERT INTO ozf_volume_summary
694 (
695 volume_summary_id
696 ,creation_date
697 ,created_by
698 ,last_update_date
699 ,last_updated_by
700 ,last_update_login
701 ,offer_id
702 ,qp_list_header_id
703 ,individual_type
704 ,individual_id
705 ,individual_volume
706 ,pbh_line_id
707 )
708 VALUES
709 (
710 ozf_volume_summary_s.NEXTVAL
711 ,SYSDATE
712 ,FND_GLOBAL.user_id
713 ,SYSDATE
714 ,FND_GLOBAL.user_id
715 ,FND_GLOBAL.conc_login_id
716 ,p_offer_id
717 ,p_qp_list_header_id
718 ,'SHIP_TO'
719 ,p_volume_detail_rec.ship_to_site_use_id
720 ,l_current_volume
721 ,l_pbh_line_id
722 );
723 END IF;
724 END IF; -- end l_include_volume = 'Y'
725 END IF; -- end line_processed = 'N'
726 END IF; -- end offer_id is null
727
728 EXCEPTION
729 WHEN OTHERS THEN
730 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
731 ROLLBACK TO insert_volume;
732 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
733 THEN
734 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
735 END IF;
736 Fnd_Msg_Pub.Count_AND_Get
737 ( p_count => x_msg_count,
738 p_data => x_msg_data,
739 p_encoded => Fnd_Api.G_FALSE
740 );
741 END insert_volume;
742
743
744 PROCEDURE create_volume
745 (
746 p_init_msg_list IN VARCHAR2
747 ,p_api_version IN NUMBER
748 ,p_commit IN VARCHAR2
749 ,x_return_status OUT NOCOPY VARCHAR2
750 ,x_msg_count OUT NOCOPY NUMBER
751 ,x_msg_data OUT NOCOPY VARCHAR2
752 ,p_volume_detail_rec IN ozf_sales_transactions_pvt.sales_transaction_rec_type
753 ,p_qp_list_header_id IN NUMBER
754 ,x_apply_discount OUT NOCOPY VARCHAR2
755 )
756 IS
757 CURSOR c_offer_id_om(p_order_line_id NUMBER, p_object_type VARCHAR2) IS
758 SELECT off.offer_id, off.qp_list_header_id
759 FROM ozf_offers off, ozf_funds_utilized_all_b utl
760 WHERE off.qp_list_header_id = utl.plan_id
761 AND off.offer_type = 'VOLUME_OFFER'
762 AND utl.plan_type = 'OFFR'
763 AND utl.order_line_id = p_order_line_id
764 --AND ((off.volume_offer_type = 'ACCRUAL' AND utl.utilization_type IN ('ACCRUAL', 'SALES_ACCRUAL'))
765 -- Need to consider the backdated adjustment created for booked orders
766 --changed for bug 6021635
767 AND ((off.volume_offer_type = 'ACCRUAL' AND (utl.utilization_type IN ('ACCRUAL', 'SALES_ACCRUAL') or (utl.utilization_type IN ('ACCRUAL', 'SALES_ACCRUAL','ADJUSTMENT')
768 AND utl.price_adjustment_id=-1)))
769 OR (off.volume_offer_type = 'OFF_INVOICE' AND utl.utilization_type = 'UTILIZED'))
770 -- AND utl.utilization_type = DECODE(off.volume_offer_type, 'ACCRUAL', 'ACCRUAL', 'OFF_INVOICE', 'UTILIZED')
771 AND utl.object_type = p_object_type;
772
773 CURSOR c_offer_id_is(p_order_line_id NUMBER, p_object_type VARCHAR2) IS
774 SELECT off.offer_id, off.qp_list_header_id
775 FROM ozf_offers off, ozf_funds_utilized_all_b utl
776 WHERE off.qp_list_header_id = utl.plan_id
777 AND off.offer_type = 'VOLUME_OFFER'
778 AND utl.plan_type = 'OFFR'
779 AND utl.object_id = p_order_line_id
780 AND ((off.volume_offer_type = 'ACCRUAL' AND utl.utilization_type IN ('ACCRUAL', 'SALES_ACCRUAL'))
781 OR (off.volume_offer_type = 'OFF_INVOICE' AND utl.utilization_type = 'UTILIZED'))
782 -- AND utl.utilization_type = DECODE(off.volume_offer_type, 'ACCRUAL', 'ACCRUAL', 'OFF_INVOICE', 'UTILIZED')
783 AND utl.object_type = p_object_type;
784
785 CURSOR c_offer_id IS
786 SELECT offer_id, qp_list_header_id
787 FROM ozf_offers
788 WHERE offer_type = 'VOLUME_OFFER'
789 AND qp_list_header_id = p_qp_list_header_id;
790
791 l_offer_id NUMBER;
792 l_qp_list_header_id NUMBER;
793 l_object_type VARCHAR2(30);
794 l_indirect_flag VARCHAR2(1);
795 l_sign NUMBER;
796 l_api_name CONSTANT VARCHAR2(30) := 'create_volume';
797
798 BEGIN
799 SAVEPOINT create_volume;
800 x_return_status := Fnd_Api.g_ret_sts_success;
801 ozf_utility_pvt.write_conc_log('========================= Create Volume =========================');
802 ozf_utility_pvt.write_conc_log('enter creat_volume : ' || p_volume_detail_rec.line_id);
803 ozf_utility_pvt.write_conc_log('source_code ' || p_volume_detail_rec.source_code);
804 ozf_utility_pvt.write_conc_log('transfer_type ' || p_volume_detail_rec.transfer_type);
805 ozf_utility_pvt.write_conc_log('sold_from_cust_account_id ' || p_volume_detail_rec.sold_from_cust_account_id);
806 ozf_utility_pvt.write_conc_log('sold_to_cust_account_id ' || p_volume_detail_rec.sold_to_cust_account_id);
807 ozf_utility_pvt.write_conc_log('bill_to_site_use_id ' || p_volume_detail_rec.bill_to_site_use_id);
808 ozf_utility_pvt.write_conc_log('ship_to_site_use_id ' || p_volume_detail_rec.ship_to_site_use_id);
809 ozf_utility_pvt.write_conc_log('inventory_item_id ' || p_volume_detail_rec.inventory_item_id);
810 ozf_utility_pvt.write_conc_log('qp_list_header_id ' || p_qp_list_header_id);
811
812 IF p_volume_detail_rec.source_code = 'OM' THEN
813 l_object_type := 'ORDER';
814 l_indirect_flag := 'O';
815
816 IF p_volume_detail_rec.transfer_type = 'IN' THEN -- for OM, IN = sales OUT = return
817 l_sign := 1;
818 ELSIF p_volume_detail_rec.transfer_type = 'OUT' THEN
819 l_sign := -1;
820 END IF;
821
822 IF p_qp_list_header_id IS NULL OR p_qp_list_header_id = fnd_api.g_miss_num THEN
823 FOR l_offer_id_om IN c_offer_id_om(p_volume_detail_rec.line_id, l_object_type) loop
824 ozf_utility_pvt.write_conc_log('OM offer_id 1: ' || l_offer_id_om.offer_id);
825 insert_volume(
826 p_init_msg_list => p_init_msg_list
827 ,p_api_version => p_api_version
828 ,p_commit => p_commit
829 ,x_return_status => x_return_status
830 ,x_msg_count => x_msg_count
831 ,x_msg_data => x_msg_data
832 ,p_volume_detail_rec => p_volume_detail_rec
833 ,p_qp_list_header_id => l_offer_id_om.qp_list_header_id
834 ,p_offer_id => l_offer_id_om.offer_id
835 ,p_indirect_flag => l_indirect_flag
836 ,p_sign => l_sign);
837 END LOOP;
838 ELSE
839 OPEN c_offer_id;
840 FETCH c_offer_id INTO l_offer_id, l_qp_list_header_id;
841 CLOSE c_offer_id;
842
843 ozf_utility_pvt.write_conc_log('OM offer_id 2: ' || l_offer_id);
844 insert_volume(
845 p_init_msg_list => p_init_msg_list
846 ,p_api_version => p_api_version
847 ,p_commit => p_commit
848 ,x_return_status => x_return_status
849 ,x_msg_count => x_msg_count
850 ,x_msg_data => x_msg_data
851 ,p_volume_detail_rec => p_volume_detail_rec
852 ,p_qp_list_header_id => l_qp_list_header_id
853 ,p_offer_id => l_offer_id
854 ,p_indirect_flag => l_indirect_flag
855 ,p_sign => l_sign);
856 END IF;
857 ELSIF p_volume_detail_rec.source_code = 'IS' THEN
858 l_object_type := 'TP_ORDER';
859 l_indirect_flag := 'R';
860
861 IF p_volume_detail_rec.transfer_type = 'IN' THEN -- for IS, IN = return OUT = sales
862 l_sign := -1;
863 ELSIF p_volume_detail_rec.transfer_type = 'OUT' THEN
864 l_sign := 1;
865 END IF;
866
867 IF p_qp_list_header_id IS NULL OR p_qp_list_header_id = fnd_api.g_miss_num THEN
868 FOR l_offer_id_is IN c_offer_id_is(p_volume_detail_rec.line_id, l_object_type) LOOP
869 ozf_utility_pvt.write_conc_log('IS offer_id 1: ' || l_offer_id_is.offer_id);
870 insert_volume(
871 p_init_msg_list => p_init_msg_list
872 ,p_api_version => p_api_version
873 ,p_commit => p_commit
874 ,x_return_status => x_return_status
875 ,x_msg_count => x_msg_count
876 ,x_msg_data => x_msg_data
877 ,p_volume_detail_rec => p_volume_detail_rec
878 ,p_qp_list_header_id => l_offer_id_is.qp_list_header_id
879 ,p_offer_id => l_offer_id_is.offer_id
880 ,p_indirect_flag => l_indirect_flag
881 ,p_sign => l_sign);
882 END LOOP;
883 ELSE
884 OPEN c_offer_id;
885 FETCH c_offer_id INTO l_offer_id, l_qp_list_header_id;
886 CLOSE c_offer_id;
887
888 ozf_utility_pvt.write_conc_log('IS offer_id 2: ' || l_offer_id);
889 insert_volume(
890 p_init_msg_list => p_init_msg_list
891 ,p_api_version => p_api_version
892 ,p_commit => p_commit
893 ,x_return_status => x_return_status
894 ,x_msg_count => x_msg_count
895 ,x_msg_data => x_msg_data
896 ,p_volume_detail_rec => p_volume_detail_rec
897 ,p_qp_list_header_id => l_qp_list_header_id
898 ,p_offer_id => l_offer_id
899 ,p_indirect_flag => l_indirect_flag
900 ,p_sign => l_sign);
901 END IF;
902 END IF;
903 ozf_utility_pvt.write_conc_log('indirect_flag ' || l_indirect_flag);
904
905 EXCEPTION
906 WHEN OTHERS THEN
907 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
908 ROLLBACK TO create_volume;
909 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
910 THEN
911 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
912 END IF;
913 Fnd_Msg_Pub.Count_AND_Get
914 ( p_count => x_msg_count,
915 p_data => x_msg_data,
916 p_encoded => Fnd_Api.G_FALSE
917 );
918 END create_volume;
919
920
921 PROCEDURE get_as_of_date_volume
922 (
923 p_offer_id IN NUMBER
924 ,p_distributor_acct_id IN NUMBER
925 ,p_cust_account_id IN NUMBER
926 ,p_bill_to IN NUMBER
927 ,p_ship_to IN NUMBER
928 ,p_group_no IN NUMBER
929 ,p_combine_schedule IN VARCHAR2
930 ,p_volume_track_type IN VARCHAR2
931 ,p_pbh_line_id IN NUMBER
932 ,p_transaction_date IN DATE
933 ,p_order_line_id IN NUMBER
934 ,p_source_code IN VARCHAR2
935 ,x_acc_volume OUT NOCOPY NUMBER
936 )
937 IS
938 -- julou bug 6348078. volume before trx_date
939 CURSOR c_group_volume(p_volume_track_id NUMBER) IS
940 SELECT NVL(SUM(volume), 0)
941 FROM ozf_volume_detail
942 WHERE include_volume_flag = 'Y'
943 AND offer_id = p_offer_id
944 AND group_no = p_volume_track_id
945 AND pbh_line_id = p_pbh_line_id
946 AND transaction_date < p_transaction_date;
947
948 CURSOR c_dist_volume(p_volume_track_id NUMBER) IS
949 SELECT NVL(SUM(volume), 0)
950 FROM ozf_volume_detail
951 WHERE include_volume_flag = 'Y'
952 AND offer_id = p_offer_id
953 AND distributor_acct_id = p_volume_track_id
954 AND pbh_line_id = p_pbh_line_id
955 AND transaction_date < p_transaction_date;
956
957 CURSOR c_customer_volume(p_volume_track_id NUMBER) IS
958 SELECT NVL(SUM(volume), 0)
959 FROM ozf_volume_detail
960 WHERE include_volume_flag = 'Y'
961 AND offer_id = p_offer_id
962 AND cust_account_id = p_volume_track_id
963 AND pbh_line_id = p_pbh_line_id
964 AND transaction_date < p_transaction_date;
965
966 CURSOR c_billto_volume(p_volume_track_id NUMBER) IS
967 SELECT NVL(SUM(volume), 0)
968 FROM ozf_volume_detail
969 WHERE include_volume_flag = 'Y'
970 AND offer_id = p_offer_id
971 AND bill_to_site_use_id = p_volume_track_id
972 AND pbh_line_id = p_pbh_line_id
973 AND transaction_date < p_transaction_date;
974
975 CURSOR c_shipto_volume(p_volume_track_id NUMBER) IS
976 SELECT NVL(SUM(volume), 0)
977 FROM ozf_volume_detail
978 WHERE include_volume_flag = 'Y'
979 AND offer_id = p_offer_id
980 AND ship_to_site_use_id = p_volume_track_id
981 AND pbh_line_id = p_pbh_line_id
982 AND transaction_date < p_transaction_date;
983
984 CURSOR c_combine_group_volume(p_volume_track_id NUMBER) IS
985 SELECT NVL(SUM(volume), 0)
986 FROM ozf_volume_detail
987 WHERE include_volume_flag = 'Y'
988 AND offer_id = p_offer_id
989 AND group_no = p_volume_track_id
990 AND transaction_date < p_transaction_date;
991
992 CURSOR c_combine_dist_volume(p_volume_track_id NUMBER) IS
993 SELECT NVL(SUM(volume), 0)
994 FROM ozf_volume_detail
995 WHERE include_volume_flag = 'Y'
996 AND offer_id = p_offer_id
997 AND distributor_acct_id = p_volume_track_id
998 AND transaction_date < p_transaction_date;
999
1000 CURSOR c_combine_customer_volume(p_volume_track_id NUMBER) IS
1001 SELECT NVL(SUM(volume), 0)
1002 FROM ozf_volume_detail
1003 WHERE include_volume_flag = 'Y'
1004 AND offer_id = p_offer_id
1005 AND cust_account_id = p_volume_track_id
1006 AND transaction_date < p_transaction_date;
1007
1008 CURSOR c_combine_billto_volume(p_volume_track_id NUMBER) IS
1009 SELECT NVL(SUM(volume), 0)
1010 FROM ozf_volume_detail
1011 WHERE include_volume_flag = 'Y'
1012 AND offer_id = p_offer_id
1013 AND bill_to_site_use_id = p_volume_track_id
1014 AND transaction_date < p_transaction_date;
1015
1016 CURSOR c_combine_shipto_volume(p_volume_track_id NUMBER) IS
1017 SELECT NVL(SUM(volume), 0)
1018 FROM ozf_volume_detail
1019 WHERE include_volume_flag = 'Y'
1020 AND offer_id = p_offer_id
1021 AND ship_to_site_use_id = p_volume_track_id
1022 AND transaction_date < p_transaction_date;
1023
1024 CURSOR c_trx_date_volume_pk IS -- PK of volume rec for given order_line_id.
1025 SELECT volume_detail_id
1026 FROM ozf_volume_detail
1027 WHERE include_volume_flag = 'Y'
1028 AND offer_id = p_offer_id
1029 AND source_code = p_source_code
1030 AND order_line_id = p_order_line_id;
1031
1032 l_pk NUMBER := NULL;
1033
1034 -- volume of trx_date. if multiple entries found, sum volume by primary key.
1035 CURSOR c_group_volume2(p_volume_track_id NUMBER, p_volume_detail_id NUMBER) IS
1036 SELECT NVL(SUM(volume), 0)
1037 FROM ozf_volume_detail
1038 WHERE include_volume_flag = 'Y'
1039 AND offer_id = p_offer_id
1040 AND group_no = p_volume_track_id
1041 AND pbh_line_id = p_pbh_line_id
1042 AND transaction_date = p_transaction_date
1043 AND volume_detail_id <= p_volume_detail_id;
1044
1045 CURSOR c_dist_volume2(p_volume_track_id NUMBER, p_volume_detail_id NUMBER) IS
1046 SELECT NVL(SUM(volume), 0)
1047 FROM ozf_volume_detail
1048 WHERE include_volume_flag = 'Y'
1049 AND offer_id = p_offer_id
1050 AND distributor_acct_id = p_volume_track_id
1051 AND pbh_line_id = p_pbh_line_id
1052 AND transaction_date = p_transaction_date
1053 AND volume_detail_id <= p_volume_detail_id;
1054
1055 CURSOR c_customer_volume2(p_volume_track_id NUMBER, p_volume_detail_id NUMBER) IS
1056 SELECT NVL(SUM(volume), 0)
1057 FROM ozf_volume_detail
1058 WHERE include_volume_flag = 'Y'
1059 AND offer_id = p_offer_id
1060 AND cust_account_id = p_volume_track_id
1061 AND pbh_line_id = p_pbh_line_id
1062 AND transaction_date = p_transaction_date
1063 AND volume_detail_id <= p_volume_detail_id;
1064
1065 -- fix for bug 7353241
1066 /*The trunc function is needed by off-invoice volume offeras transaction_date in
1067 ozf_volume_detail is 00:00:00.
1068 The input parameter p_transaction_date has to be truncated before comparing with table value.
1069 On the other hand, trunc screws accrual incentive. As you know the calculation has two parts,
1070 one for transactions before the day, the other for transactions on the day.
1071 This is mainly for IDSM transactions as transactions may not come in the order of time.
1072 So we need 2 cursors to handle two types of incentive of volume offer.*/
1073
1074 CURSOR c_customer_volume3(p_volume_track_id NUMBER, p_volume_detail_id NUMBER) IS
1075 SELECT NVL(SUM(volume), 0)
1076 FROM ozf_volume_detail
1077 WHERE include_volume_flag = 'Y'
1078 AND offer_id = p_offer_id
1079 AND cust_account_id = p_volume_track_id
1080 AND pbh_line_id = p_pbh_line_id
1081 AND trunc(transaction_date) = trunc(p_transaction_date)
1082 AND volume_detail_id <= p_volume_detail_id;
1083
1084 CURSOR c_billto_volume2(p_volume_track_id NUMBER, p_volume_detail_id NUMBER) IS
1085 SELECT NVL(SUM(volume), 0)
1086 FROM ozf_volume_detail
1087 WHERE include_volume_flag = 'Y'
1088 AND offer_id = p_offer_id
1089 AND bill_to_site_use_id = p_volume_track_id
1090 AND pbh_line_id = p_pbh_line_id
1091 AND transaction_date = p_transaction_date
1092 AND volume_detail_id <= p_volume_detail_id;
1093
1094 CURSOR c_shipto_volume2(p_volume_track_id NUMBER, p_volume_detail_id NUMBER) IS
1095 SELECT NVL(SUM(volume), 0)
1096 FROM ozf_volume_detail
1097 WHERE include_volume_flag = 'Y'
1098 AND offer_id = p_offer_id
1099 AND ship_to_site_use_id = p_volume_track_id
1100 AND pbh_line_id = p_pbh_line_id
1101 AND transaction_date = p_transaction_date
1102 AND volume_detail_id <= p_volume_detail_id;
1103
1104 CURSOR c_combine_group_volume2(p_volume_track_id NUMBER, p_volume_detail_id NUMBER) IS
1105 SELECT NVL(SUM(volume), 0)
1106 FROM ozf_volume_detail
1107 WHERE include_volume_flag = 'Y'
1108 AND offer_id = p_offer_id
1109 AND group_no = p_volume_track_id
1110 AND transaction_date = p_transaction_date
1111 AND volume_detail_id <= p_volume_detail_id;
1112
1113 CURSOR c_combine_dist_volume2(p_volume_track_id NUMBER, p_volume_detail_id NUMBER) IS
1114 SELECT NVL(SUM(volume), 0)
1115 FROM ozf_volume_detail
1116 WHERE include_volume_flag = 'Y'
1117 AND offer_id = p_offer_id
1118 AND distributor_acct_id = p_volume_track_id
1119 AND transaction_date = p_transaction_date
1120 AND volume_detail_id <= p_volume_detail_id;
1121
1122 CURSOR c_combine_customer_volume2(p_volume_track_id NUMBER, p_volume_detail_id NUMBER) IS
1123 SELECT NVL(SUM(volume), 0)
1124 FROM ozf_volume_detail
1125 WHERE include_volume_flag = 'Y'
1126 AND offer_id = p_offer_id
1127 AND cust_account_id = p_volume_track_id
1128 AND transaction_date = p_transaction_date
1129 AND volume_detail_id <= p_volume_detail_id;
1130
1131 CURSOR c_combine_billto_volume2(p_volume_track_id NUMBER, p_volume_detail_id NUMBER) IS
1132 SELECT NVL(SUM(volume), 0)
1133 FROM ozf_volume_detail
1134 WHERE include_volume_flag = 'Y'
1135 AND offer_id = p_offer_id
1136 AND bill_to_site_use_id = p_volume_track_id
1137 AND transaction_date = p_transaction_date
1138 AND volume_detail_id <= p_volume_detail_id;
1139
1140 CURSOR c_combine_shipto_volume2(p_volume_track_id NUMBER, p_volume_detail_id NUMBER) IS
1141 SELECT NVL(SUM(volume), 0)
1142 FROM ozf_volume_detail
1143 WHERE include_volume_flag = 'Y'
1144 AND offer_id = p_offer_id
1145 AND ship_to_site_use_id = p_volume_track_id
1146 AND transaction_date = p_transaction_date
1147 AND volume_detail_id <= p_volume_detail_id;
1148
1149 CURSOR c_volume_offer_type IS
1150 SELECT volume_offer_type
1151 FROM ozf_offers
1152 WHERE offer_id = p_offer_id;
1153
1154 l_volume_offer_type VARCHAR2(30);
1155 l_volume_b4_trx_date NUMBER;
1156 l_volume_of_trx_date NUMBER;
1157 BEGIN
1158 ozf_utility_pvt.write_conc_log('==================== get_as_of_date_volume ====================');
1159
1160 OPEN c_trx_date_volume_pk;
1161 FETCH c_trx_date_volume_pk INTO l_pk;
1162 CLOSE c_trx_date_volume_pk;
1163 ozf_utility_pvt.write_conc_log('PK is ' || l_pk);
1164
1165 IF p_combine_schedule = 'N' THEN
1166 IF p_volume_track_type = 'GROUP' THEN
1167 OPEN c_group_volume(p_group_no);
1168 FETCH c_group_volume INTO l_volume_b4_trx_date;
1169 CLOSE c_group_volume;
1170
1171 OPEN c_group_volume2(p_group_no, l_pk);
1172 FETCH c_group_volume2 INTO l_volume_of_trx_date;
1173 CLOSE c_group_volume2;
1174 ELSIF p_volume_track_type = 'DISTRIBUTOR' THEN
1175 OPEN c_dist_volume(p_distributor_acct_id);
1176 FETCH c_dist_volume INTO l_volume_b4_trx_date;
1177 CLOSE c_dist_volume;
1178
1179 OPEN c_dist_volume2(p_distributor_acct_id, l_pk);
1180 FETCH c_dist_volume2 INTO l_volume_of_trx_date;
1181 CLOSE c_dist_volume2;
1182 ELSIF p_volume_track_type = 'ACCOUNT' THEN
1183 OPEN c_customer_volume(p_cust_account_id);
1184 FETCH c_customer_volume INTO l_volume_b4_trx_date;
1185 CLOSE c_customer_volume;
1186
1187 --Fix for bug 7353241
1188
1189 OPEN c_volume_offer_type;
1190 FETCH c_volume_offer_type INTO l_volume_offer_type;
1191 CLOSE c_volume_offer_type;
1192
1193 IF l_volume_offer_type = 'ACCRUAL' THEN
1194 OPEN c_customer_volume2(p_cust_account_id, l_pk);
1195 FETCH c_customer_volume2 INTO l_volume_of_trx_date;
1196 CLOSE c_customer_volume2;
1197 ELSIF l_volume_offer_type = 'OFF_INVOICE' THEN
1198 OPEN c_customer_volume3(p_cust_account_id, l_pk);
1199 FETCH c_customer_volume3 INTO l_volume_of_trx_date;
1200 CLOSE c_customer_volume3;
1201 END IF;
1202 ELSIF p_volume_track_type = 'BILL_TO' THEN
1203 OPEN c_billto_volume(p_bill_to);
1204 FETCH c_billto_volume INTO l_volume_b4_trx_date;
1205 CLOSE c_billto_volume;
1206
1207 OPEN c_billto_volume2(p_bill_to, l_pk);
1208 FETCH c_billto_volume2 INTO l_volume_of_trx_date;
1209 CLOSE c_billto_volume2;
1210 ELSIF p_volume_track_type = 'SHIP_TO' THEN
1211 OPEN c_shipto_volume(p_ship_to);
1212 FETCH c_shipto_volume INTO l_volume_b4_trx_date;
1213 CLOSE c_shipto_volume;
1214
1215 OPEN c_shipto_volume2(p_ship_to, l_pk);
1216 FETCH c_shipto_volume2 INTO l_volume_of_trx_date;
1217 CLOSE c_shipto_volume2;
1218 END IF;
1219 ELSE
1220 IF p_volume_track_type = 'GROUP' THEN
1221 OPEN c_combine_group_volume(p_group_no);
1222 FETCH c_combine_group_volume INTO l_volume_b4_trx_date;
1223 CLOSE c_combine_group_volume;
1224
1225 OPEN c_combine_group_volume2(p_group_no, l_pk);
1226 FETCH c_combine_group_volume2 INTO l_volume_of_trx_date;
1227 CLOSE c_combine_group_volume2;
1228 ELSIF p_volume_track_type = 'DISTRIBUTOR' THEN
1229 OPEN c_combine_dist_volume(p_distributor_acct_id);
1230 FETCH c_combine_dist_volume INTO l_volume_b4_trx_date;
1231 CLOSE c_combine_dist_volume;
1232
1233 OPEN c_combine_dist_volume2(p_distributor_acct_id, l_pk);
1234 FETCH c_combine_dist_volume2 INTO l_volume_of_trx_date;
1235 CLOSE c_combine_dist_volume2;
1236 ELSIF p_volume_track_type = 'ACCOUNT' THEN
1237 OPEN c_combine_customer_volume(p_cust_account_id);
1238 FETCH c_combine_customer_volume INTO l_volume_b4_trx_date;
1239 CLOSE c_combine_customer_volume;
1240
1241 OPEN c_combine_customer_volume2(p_cust_account_id, l_pk);
1242 FETCH c_combine_customer_volume2 INTO l_volume_of_trx_date;
1243 CLOSE c_combine_customer_volume2;
1244 ELSIF p_volume_track_type = 'BILL_TO' THEN
1245 OPEN c_combine_billto_volume(p_bill_to);
1246 FETCH c_combine_billto_volume INTO l_volume_b4_trx_date;
1247 CLOSE c_combine_billto_volume;
1248
1249 OPEN c_combine_billto_volume2(p_bill_to, l_pk);
1250 FETCH c_combine_billto_volume2 INTO l_volume_of_trx_date;
1251 CLOSE c_combine_billto_volume2;
1252 ELSIF p_volume_track_type = 'SHIP_TO' THEN
1253 OPEN c_combine_shipto_volume(p_ship_to);
1254 FETCH c_combine_shipto_volume INTO l_volume_b4_trx_date;
1255 CLOSE c_combine_shipto_volume;
1256
1257 OPEN c_combine_shipto_volume2(p_ship_to, l_pk);
1258 FETCH c_combine_shipto_volume2 INTO l_volume_of_trx_date;
1259 CLOSE c_combine_shipto_volume2;
1260 END IF;
1261 END IF;
1262
1263 x_acc_volume := l_volume_b4_trx_date + l_volume_of_trx_date;
1264
1265 ozf_utility_pvt.write_conc_log('volume b4 trx_date ' || l_volume_b4_trx_date);
1266 ozf_utility_pvt.write_conc_log('volume of trx_date ' || l_volume_of_trx_date);
1267 ozf_utility_pvt.write_conc_log('as_of_date_volume ' || x_acc_volume);
1268
1269 IF x_acc_volume IS NULL THEN
1270 x_acc_volume := 0;
1271 END IF;
1272 END get_as_of_date_volume;
1273
1274 PROCEDURE get_volume -- overload version 1, used by pricing
1275 (
1276 p_offer_id IN NUMBER
1277 ,p_cust_acct_id IN NUMBER
1278 ,p_bill_to IN NUMBER
1279 ,p_ship_to IN NUMBER
1280 ,p_group_no IN NUMBER
1281 ,p_vol_track_type IN VARCHAR2
1282 ,p_pbh_line_id IN NUMBER
1283 ,p_combine_schedule IN VARCHAR2
1284 ,x_acc_volume OUT NOCOPY NUMBER
1285 )
1286 IS
1287 CURSOR c_group_volume IS
1288 SELECT group_volume
1289 FROM ozf_volume_summary
1290 WHERE offer_id = p_offer_id
1291 AND group_no = p_group_no
1292 AND pbh_line_id = p_pbh_line_id;
1293
1294 CURSOR c_individual_volume(p_volume_track_type VARCHAR2, p_volume_track_id NUMBER) IS
1295 SELECT individual_volume
1296 FROM ozf_volume_summary
1297 WHERE offer_id = p_offer_id
1298 AND individual_type = p_vol_track_type
1299 AND individual_id = p_volume_track_id
1300 AND pbh_line_id = p_pbh_line_id;
1301
1302 CURSOR c_combine_individual_volume(p_volume_track_type VARCHAR2, p_volume_track_id NUMBER) IS
1303 SELECT SUM(individual_volume)
1304 FROM ozf_volume_summary
1305 WHERE offer_id = p_offer_id
1306 AND individual_type = p_vol_track_type
1307 AND individual_id = p_volume_track_id;
1308
1309 l_volume_track_type VARCHAR2(30);
1310 l_volume_track_id NUMBER;
1311 l_api_name CONSTANT VARCHAR2(30) := 'get_volume';
1312 BEGIN
1313 SAVEPOINT get_volume;
1314
1315 IF p_vol_track_type = 'GROUP' THEN
1316 OPEN c_group_volume;
1317 FETCH c_group_volume INTO x_acc_volume;
1318 CLOSE c_group_volume;
1319 ELSE
1320 IF p_vol_track_type = 'ACCOUNT' THEN
1321 l_volume_track_id := p_cust_acct_id;
1322 ELSIF p_vol_track_type = 'BILL_TO' THEN
1323 l_volume_track_id := p_bill_to;
1324 ELSIF p_vol_track_type = 'SHIP_TO' THEN
1325 l_volume_track_id := p_ship_to;
1326 END IF;
1327
1328 IF p_combine_schedule = 'N' THEN
1329 OPEN c_individual_volume(p_vol_track_type, l_volume_track_id);
1330 FETCH c_individual_volume INTO x_acc_volume;
1331 CLOSE c_individual_volume;
1332 ELSE
1333 OPEN c_combine_individual_volume(p_vol_track_type, l_volume_track_id);
1334 FETCH c_combine_individual_volume INTO x_acc_volume;
1335 CLOSE c_combine_individual_volume;
1336 END IF;
1337 END IF;
1338
1339 IF x_acc_volume IS NULL THEN
1340 x_acc_volume := 0;
1341 END IF;
1342
1343 EXCEPTION
1344 WHEN OTHERS THEN
1345 ROLLBACK TO get_volume;
1346 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
1347 THEN
1348 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1349 END IF;
1350 END get_volume;
1351
1352
1353 PROCEDURE get_volume -- overload version 2, used by budget
1354 (
1355 p_init_msg_list IN VARCHAR2
1356 ,p_api_version IN NUMBER
1357 ,p_commit IN VARCHAR2
1358 ,x_return_status OUT NOCOPY VARCHAR2
1359 ,x_msg_count OUT NOCOPY NUMBER
1360 ,x_msg_data OUT NOCOPY VARCHAR2
1361 ,p_qp_list_header_id IN NUMBER
1362 ,p_order_line_id IN NUMBER
1363 ,p_source_code IN VARCHAR2 -- OM or IS
1364 ,p_trx_date IN DATE
1365 ,x_acc_volume OUT NOCOPY NUMBER
1366 )
1367 IS
1368 CURSOR c_volume_detail IS
1369 SELECT billto_cust_account_id, bill_to_site_use_id, ship_to_site_use_id
1370 FROM ozf_funds_utilized_all_b
1371 WHERE (p_source_code = 'OM' AND object_type = 'ORDER' AND order_line_id = p_order_line_id)
1372 OR (p_source_code = 'IS' AND object_type = 'TP_ORDER' AND object_id = p_order_line_id);
1373
1374 CURSOR c_dist_acct_id IS
1375 SELECT sold_from_cust_account_id
1376 FROM ozf_resale_lines_all
1377 WHERE resale_line_id = p_order_line_id;
1378
1379 CURSOR c_combine_schedule(l_qp_list_header_id NUMBER, l_order_line_id NUMBER) IS
1380 SELECT offer_id, combine_schedule_yn, apply_discount_flag, group_no, volume_track_type, pbh_line_id
1381 FROM ozf_order_group_prod
1382 WHERE qp_list_header_id = l_qp_list_header_id
1383 AND order_line_id = l_order_line_id
1384 AND indirect_flag = DECODE(p_source_code, 'OM', 'O', 'IS', 'R');
1385
1386 CURSOR c_preset_volume(p_offer_id NUMBER, p_group_no NUMBER, p_pbh_line_id NUMBER) IS
1387 SELECT a.volume_from
1388 FROM ozf_offer_discount_lines a, ozf_market_preset_tiers b, ozf_offr_market_options c
1389 WHERE a.offer_discount_line_id = b.dis_offer_discount_id
1390 AND b. pbh_offer_discount_id = p_pbh_line_id
1391 AND b.offer_market_option_id = c.offer_market_option_id
1392 AND c.offer_id = p_offer_id
1393 AND c.group_number = p_group_no;
1394
1395 l_offer_id NUMBER;
1396 l_distributor_acct_id NUMBER;
1397 l_cust_account_id NUMBER;
1398 l_bill_to NUMBER;
1399 l_ship_to NUMBER;
1400 l_group_no NUMBER;
1401 l_volume_track_type VARCHAR2(30);
1402 l_pbh_line_id NUMBER;
1403 l_combine_schedule VARCHAR2(1);
1404 l_apply_discount VARCHAR2(1);
1405 l_acc_volume NUMBER;
1406 l_split_from_line_id NUMBER;
1407 l_preset_volume NUMBER;
1408 l_trx_date DATE;
1409 l_api_name CONSTANT VARCHAR2(30) := 'get_volume_2';
1410 BEGIN
1411 SAVEPOINT get_volume_2;
1412 x_return_status := Fnd_Api.g_ret_sts_success;
1413
1414 OPEN c_volume_detail;
1415 FETCH c_volume_detail INTO l_cust_account_id, l_bill_to, l_ship_to;
1416 CLOSE c_volume_detail;
1417
1418 IF p_source_code = 'IS' THEN
1419 OPEN c_dist_acct_id;
1420 FETCH c_dist_acct_id INTO l_distributor_acct_id;
1421 CLOSE c_dist_acct_id;
1422 ELSIF p_source_code = 'OM' THEN
1423 l_distributor_acct_id := NULL;
1424 END IF;
1425
1426 --kdass fixed bug 6008340
1427 OPEN c_combine_schedule(p_qp_list_header_id, p_order_line_id);
1428 FETCH c_combine_schedule INTO l_offer_id, l_combine_schedule, l_apply_discount, l_group_no, l_volume_track_type, l_pbh_line_id;
1429 CLOSE c_combine_schedule;
1430
1431 IF p_source_code = 'OM' THEN
1432 SELECT split_from_line_id into l_split_from_line_id from OE_ORDER_LINES_ALL where line_id = p_order_line_id;
1433 IF (l_split_from_line_id IS NOT NULL) then
1434 OPEN c_combine_schedule(p_qp_list_header_id, l_split_from_line_id);
1435 FETCH c_combine_schedule INTO l_offer_id, l_combine_schedule, l_apply_discount, l_group_no, l_volume_track_type, l_pbh_line_id;
1436 CLOSE c_combine_schedule;
1437 END IF;
1438 END IF;
1439
1440 l_trx_date := p_trx_date;
1441
1442 ozf_utility_pvt.write_conc_log('in api ' || l_api_name || ' -- ready to call get_as_of_date_volume');
1443 ozf_utility_pvt.write_conc_log('apply discount ' || l_apply_discount);
1444 ozf_utility_pvt.write_conc_log('other values');
1445 ozf_utility_pvt.write_conc_log('l_offer_id/l_qp_list_header_id/l_cust_account_id/l_bill_to/l_ship_to');
1446 ozf_utility_pvt.write_conc_log(l_offer_id || '/' || p_qp_list_header_id || '/' || l_cust_account_id || '/' || l_bill_to || '/' || l_ship_to);
1447 ozf_utility_pvt.write_conc_log('l_group_no/l_combine_schedule/l_volume_track_type/l_pbh_line_id');
1448 ozf_utility_pvt.write_conc_log(l_group_no || '/' || l_combine_schedule || '/' || l_volume_track_type || '/' || l_pbh_line_id);
1449 ozf_utility_pvt.write_conc_log('p_source_code/p_order_line_id/l_trx_date');
1450 ozf_utility_pvt.write_conc_log(p_source_code || '/' || p_order_line_id || '/' || to_char(l_trx_date, 'YYYY-MM-DD HH:MI:SS'));
1451
1452 IF l_apply_discount = 'N' THEN
1453 x_acc_volume := 0;
1454 ELSE
1455 get_as_of_date_volume
1456 (
1457 p_offer_id => l_offer_id
1458 ,p_distributor_acct_id => l_distributor_acct_id
1459 ,p_cust_account_id => l_cust_account_id
1460 ,p_bill_to => l_bill_to
1461 ,p_ship_to => l_ship_to
1462 ,p_group_no => l_group_no
1463 ,p_combine_schedule => l_combine_schedule
1464 ,p_volume_track_type => l_volume_track_type
1465 ,p_pbh_line_id => l_pbh_line_id
1466 ,p_transaction_date => l_trx_date
1467 ,p_order_line_id => p_order_line_id
1468 ,p_source_code => p_source_code
1469 ,x_acc_volume => l_acc_volume
1470 );
1471 ozf_utility_pvt.write_conc_log('calculated volume: ' || l_acc_volume);
1472 IF l_acc_volume IS NULL THEN
1473 l_acc_volume := 0;
1474 END IF;
1475
1476 OPEN c_preset_volume(l_offer_id, l_group_no, l_pbh_line_id);
1477 FETCH c_preset_volume INTO l_preset_volume;
1478 CLOSE c_preset_volume;
1479 ozf_utility_pvt.write_conc_log('preset volume: ' || l_preset_volume);
1480 IF l_preset_volume IS NULL THEN
1481 l_preset_volume := 0;
1482 END IF;
1483
1484 IF l_acc_volume > l_preset_volume THEN
1485 x_acc_volume := l_acc_volume;
1486 ELSE
1487 x_acc_volume := l_preset_volume;
1488 END IF;
1489 END IF;
1490
1491 EXCEPTION
1492 WHEN OTHERS THEN
1493 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
1494 ROLLBACK TO get_volume_2;
1495 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
1496 THEN
1497 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1498 END IF;
1499 Fnd_Msg_Pub.Count_AND_Get
1500 ( p_count => x_msg_count,
1501 p_data => x_msg_data,
1502 p_encoded => Fnd_Api.G_FALSE
1503 );
1504 END get_volume;
1505
1506
1507 FUNCTION get_numeric_attribute_value
1508 (
1509 p_list_line_id IN NUMBER
1510 ,p_list_line_no IN VARCHAR2
1511 ,p_order_header_id IN NUMBER
1512 ,p_order_line_id IN NUMBER
1513 ,p_price_effective_date IN DATE
1514 ,p_req_line_attrs_tbl IN qp_runtime_source.accum_req_line_attrs_tbl
1515 ,p_accum_rec IN qp_runtime_source.accum_record_type
1516 )
1517 RETURN NUMBER IS
1518 CURSOR c_offer_id IS
1519 SELECT o.qp_list_header_id, o.offer_id
1520 FROM ozf_offers o, qp_list_lines q
1521 WHERE o.qp_list_header_id = q.list_header_id
1522 AND q.list_line_id = p_list_line_id;
1523
1524 CURSOR c_order_detail IS
1525 SELECT unit_selling_price, pricing_quantity, sold_to_org_id, ship_to_org_id, invoice_to_org_id, actual_shipment_date
1526 FROM oe_order_lines_all
1527 WHERE line_id = p_order_line_id;
1528
1529 CURSOR c_resale_detail IS
1530 SELECT quantity, amount, sold_from_cust_account_id, sold_to_cust_account_id, ship_to_site_use_id, bill_to_site_use_id, transaction_date
1531 FROM ozf_sales_transactions
1532 WHERE line_id = p_order_line_id;
1533
1534 CURSOR c_interface_detail IS
1535 SELECT quantity, quantity * selling_price, sold_from_cust_account_id, bill_to_cust_account_id, ship_to_site_use_id, bill_to_site_use_id, date_ordered
1536 FROM ozf_resale_lines_int_all
1537 WHERE resale_line_int_id = p_order_line_id;
1538
1539 CURSOR c_discount_volume(p_offer_id NUMBER, p_prod_attribute VARCHAR2, p_prod_attr_value VARCHAR2) IS
1540 SELECT apply_discount_flag, include_volume_flag
1541 FROM ozf_offer_discount_products
1542 WHERE product_context = 'ITEM'
1543 AND product_attribute = p_prod_attribute
1544 AND product_attr_value = p_prod_attr_value
1545 AND offer_id = p_offer_id;
1546
1547 CURSOR c_order_group_prod_id(p_offer_id NUMBER, p_line_id NUMBER, p_indirect_flag VARCHAR2) IS
1548 SELECT order_group_prod_id
1549 FROM ozf_order_group_prod
1550 WHERE order_line_id = p_line_id
1551 AND offer_id = p_offer_id
1552 AND indirect_flag = p_indirect_flag;
1553
1554 CURSOR c_volume_type(p_pbh_line_id NUMBER) IS
1555 SELECT volume_type
1556 FROM ozf_offer_discount_lines
1557 WHERE offer_discount_line_id = p_pbh_line_id;
1558
1559 CURSOR c_preset_volume(p_offer_id NUMBER, p_group_no NUMBER, p_pbh_line_id NUMBER) IS
1560 SELECT a.volume_from
1561 FROM ozf_offer_discount_lines a, ozf_market_preset_tiers b, ozf_offr_market_options c
1562 WHERE a.offer_discount_line_id = b.dis_offer_discount_id
1563 AND b. pbh_offer_discount_id = p_pbh_line_id
1564 AND b.offer_market_option_id = c.offer_market_option_id
1565 AND c.offer_id = p_offer_id
1566 AND c.group_number = p_group_no;
1567
1568 l_list_header_id NUMBER;
1569 l_offer_id NUMBER;
1570 l_group_no NUMBER;
1571 l_pbh_line_id NUMBER;
1572 l_prod_attribute VARCHAR2(30);
1573 l_prod_attr_value VARCHAR2(240);
1574 l_indirect_flag VARCHAR2(1);
1575 l_price NUMBER;
1576 l_quantity NUMBER;
1577 l_amount NUMBER;
1578 l_distributor NUMBER;
1579 l_sold_to NUMBER;
1580 l_ship_to NUMBER;
1581 l_bill_to NUMBER;
1582 l_trx_date DATE;
1583 l_split_from_line_id NUMBER;
1584 l_group_prod_order_line_id NUMBER;
1585 l_apply_discount VARCHAR2(1);
1586 l_include_volume VARCHAR2(1);
1587 l_id NUMBER;
1588 l_volume_type VARCHAR2(30);
1589 l_vol_track_type VARCHAR2(30);
1590 l_combine_schedule VARCHAR2(1);
1591 l_current_volume NUMBER;
1592 l_preset_volume NUMBER;
1593 l_acc_volume NUMBER;
1594 l_volume NUMBER;
1595 l_api_name CONSTANT VARCHAR2(30) := 'get_numeric_attribute_value';
1596 -- l_rec_count number;
1597 BEGIN
1598 /*
1599 INSERT INTO om_qp_temp(id, rec_req_type, access_date, line_index)
1600 VALUES(om_qp_temp_s.nextval, 'START', SYSDATE, -9999);
1601
1602 l_rec_count := p_req_line_attrs_tbl.COUNT;
1603
1604 INSERT INTO om_qp_temp(
1605 id,
1606 rec_context,
1607 rec_attr,
1608 access_date,
1609 line_index,
1610 group_num,
1611 order_line_id,
1612 list_line_id)
1613 VALUES(om_qp_temp_s.nextval,
1614 p_accum_rec.context,
1615 p_accum_rec.attribute,
1616 sysdate,
1617 0,
1618 l_rec_count,
1619 p_order_line_id,
1620 p_list_line_id);
1621
1622 FOR i IN 1..p_req_line_attrs_tbl.COUNT LOOP
1623 INSERT INTO om_qp_temp(
1624 id,
1625 access_date,
1626 line_index,
1627 attr_type,
1628 context,
1629 attr,
1630 attr_value,
1631 group_num,
1632 order_line_id,
1633 list_line_id)
1634 VALUES(
1635 om_qp_temp_s.nextval,
1636 sysdate,
1637 p_req_line_attrs_tbl(i).line_index,
1638 p_req_line_attrs_tbl(i).attribute_type,
1639 p_req_line_attrs_tbl(i).context,
1640 p_req_line_attrs_tbl(i).attribute,
1641 p_req_line_attrs_tbl(i).value,
1642 p_req_line_attrs_tbl(i).grouping_no,
1643 p_order_line_id,
1644 p_list_line_id);
1645 END LOOP;
1646 */
1647 SAVEPOINT get_numeric_attribute_value;
1648
1649 IF OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL.COUNT > 0 THEN
1650 IF OZF_ORDER_PRICE_PVT.G_RESALE_LINE_TBL(1).batch_type <> 'TP_ACCRUAL' THEN
1651 RETURN 0;
1652 END IF;
1653 END IF;
1654
1655 OPEN c_offer_id;
1656 FETCH c_offer_id INTO l_list_header_id, l_offer_id;
1657 CLOSE c_offer_id;
1658
1659 get_group_pbh_prod
1660 (
1661 p_offer_id => l_offer_id
1662 ,p_list_header_id => l_list_header_id
1663 ,p_list_line_id => p_list_line_id
1664 ,p_req_line_attrs_tbl => p_req_line_attrs_tbl
1665 ,p_order_line_id => p_order_line_id
1666 ,x_group_no => l_group_no
1667 ,x_vol_track_type => l_vol_track_type
1668 ,x_combine_schedule => l_combine_schedule
1669 ,x_pbh_line_id => l_pbh_line_id
1670 ,x_pord_attribute => l_prod_attribute
1671 ,x_prod_attr_value => l_prod_attr_value
1672 ,x_indirect_flag => l_indirect_flag
1673 );
1674
1675 OPEN c_volume_type(l_pbh_line_id);
1676 FETCH c_volume_type INTO l_volume_type;
1677 CLOSE c_volume_type;
1678
1679 OPEN c_discount_volume(l_offer_id, l_prod_attribute, l_prod_attr_value);
1680 FETCH c_discount_volume INTO l_apply_discount, l_include_volume;
1681 CLOSE c_discount_volume;
1682
1683 --kdass fixed bug 6008340
1684 l_group_prod_order_line_id := p_order_line_id;
1685
1686 IF l_indirect_flag = 'O' THEN
1687 select split_from_line_id into l_split_from_line_id from OE_ORDER_LINES_ALL where line_id = p_order_line_id;
1688 IF (l_split_from_line_id IS NOT NULL) THEN
1689 --l_group_prod_order_line_id := l_split_from_line_id;
1690 -- inanaiah: Added for bug 5975678 fix
1691 IF (p_order_line_id = l_split_from_line_id) THEN
1692 -- This happens when SO is split, the first child split has the same line_id as the parent SO
1693 l_group_prod_order_line_id := l_split_from_line_id;
1694 ELSE
1695 -- For the second, third,...splits, the selected split records need to be created/updated in ozf_order_group_prod
1696 -- In the earlier version the l_split_from_line_id record of ozf_order_group_prod was updated with
1697 -- order_line_id = p_order_line_id
1698 -- resulting in losing the l_split_from_line_id record in ozf_order_group_prod.
1699 l_group_prod_order_line_id := p_order_line_id;
1700 END IF;
1701 END IF;
1702 END IF;
1703
1704 OPEN c_order_group_prod_id(l_offer_id, l_group_prod_order_line_id, l_indirect_flag);
1705 FETCH c_order_group_prod_id INTO l_id;
1706 CLOSE c_order_group_prod_id;
1707
1708 IF l_id IS NULL THEN
1709 INSERT INTO ozf_order_group_prod
1710 (
1711 order_group_prod_id
1712 ,creation_date
1713 ,created_by
1714 ,last_update_date
1715 ,last_updated_by
1716 ,last_update_login
1717 ,order_line_id
1718 ,offer_id
1719 ,qp_list_header_id
1720 ,group_no
1721 ,volume_track_type
1722 ,combine_schedule_yn
1723 ,pbh_line_id
1724 ,volume_type
1725 ,prod_attribute
1726 ,prod_attr_value
1727 ,apply_discount_flag
1728 ,include_volume_flag
1729 ,indirect_flag
1730 )
1731 VALUES
1732 ( ozf_order_group_prod_s.NEXTVAL
1733 ,SYSDATE
1734 ,FND_GLOBAL.user_id
1735 ,SYSDATE
1736 ,FND_GLOBAL.user_id
1737 ,FND_GLOBAL.conc_login_id
1738 ,p_order_line_id
1739 ,l_offer_id
1740 ,l_list_header_id
1741 ,l_group_no
1742 ,l_vol_track_type
1743 ,l_combine_schedule
1744 ,l_pbh_line_id
1745 ,l_volume_type
1746 ,l_prod_attribute
1747 ,l_prod_attr_value
1748 ,l_apply_discount
1749 ,l_include_volume
1750 ,l_indirect_flag
1751 );
1752 ELSE
1753 UPDATE ozf_order_group_prod
1754 SET last_update_date = SYSDATE,
1755 last_updated_by = FND_GLOBAL.user_id,
1756 last_update_login = FND_GLOBAL.conc_login_id,
1757 order_line_id = p_order_line_id,
1758 offer_id = l_offer_id,
1759 qp_list_header_id = l_list_header_id,
1760 group_no = l_group_no,
1761 volume_track_type = l_vol_track_type,
1762 combine_schedule_yn = l_combine_schedule,
1763 pbh_line_id = l_pbh_line_id,
1764 volume_type = l_volume_type,
1765 prod_attribute = l_prod_attribute,
1766 prod_attr_value = l_prod_attr_value,
1767 apply_discount_flag = l_apply_discount,
1768 include_volume_flag = l_include_volume,
1769 indirect_flag = l_indirect_flag
1770 WHERE order_group_prod_id = l_id;
1771 END IF;
1772
1773 IF l_indirect_flag = 'O' THEN -- from OM, call overload version 1
1774 OPEN c_order_detail;
1775 FETCH c_order_detail INTO l_price, l_quantity, l_sold_to, l_ship_to, l_bill_to, l_trx_date;
1776 CLOSE c_order_detail;
1777
1778 IF l_volume_type = 'PRICING_ATTRIBUTE10' THEN
1779 l_current_volume := l_quantity;
1780 ELSIF l_volume_type = 'PRICING_ATTRIBUTE12' THEN
1781 l_current_volume := l_price * l_quantity;
1782 END IF;
1783
1784 get_volume
1785 (
1786 p_offer_id => l_offer_id
1787 ,p_cust_acct_id => l_sold_to
1788 ,p_bill_to => l_bill_to
1789 ,p_ship_to => l_ship_to
1790 ,p_group_no => l_group_no
1791 ,p_vol_track_type => l_vol_track_type
1792 ,p_pbh_line_id => l_pbh_line_id
1793 ,p_combine_schedule => l_combine_schedule
1794 ,x_acc_volume => l_acc_volume
1795 );
1796 ELSE -- from IDSM, call as of date volume
1797 IF l_indirect_flag = 'R' THEN
1798 OPEN c_resale_detail;
1799 FETCH c_resale_detail INTO l_quantity, l_amount, l_distributor, l_sold_to, l_ship_to, l_bill_to, l_trx_date;
1800 CLOSE c_resale_detail;
1801 ELSIF l_indirect_flag = 'I' THEN
1802 OPEN c_interface_detail;
1803 FETCH c_interface_detail INTO l_quantity, l_amount, l_distributor, l_sold_to, l_ship_to, l_bill_to, l_trx_date;
1804 CLOSE c_interface_detail;
1805 END IF;
1806
1807 IF l_volume_type = 'PRICING_ATTRIBUTE10' THEN
1808 l_current_volume := l_quantity;
1809 ELSIF l_volume_type = 'PRICING_ATTRIBUTE12' THEN
1810 l_current_volume := l_amount;
1811 END IF;
1812
1813 get_as_of_date_volume
1814 (
1815 p_offer_id => l_offer_id
1816 ,p_distributor_acct_id => l_distributor
1817 ,p_cust_account_id => l_sold_to
1818 ,p_bill_to => l_bill_to
1819 ,p_ship_to => l_ship_to
1820 ,p_group_no => l_group_no
1821 ,p_combine_schedule => l_combine_schedule
1822 ,p_volume_track_type => l_vol_track_type
1823 ,p_pbh_line_id => l_pbh_line_id
1824 ,p_transaction_date => l_trx_date
1825 ,p_order_line_id => p_order_line_id
1826 ,p_source_code => 'IS'
1827 ,x_acc_volume => l_acc_volume
1828 );
1829 END IF;
1830
1831 IF l_acc_volume IS NULL THEN
1832 l_acc_volume := 0;
1833 END IF;
1834
1835 OPEN c_preset_volume(l_offer_id, l_group_no, l_pbh_line_id);
1836 FETCH c_preset_volume INTO l_preset_volume;
1837 CLOSE c_preset_volume;
1838
1839 IF l_preset_volume IS NULL THEN
1840 l_preset_volume := 0;
1841 END IF;
1842
1843 IF l_acc_volume > l_preset_volume THEN
1844 l_volume := l_acc_volume;
1845 ELSE
1846 l_volume := l_preset_volume;
1847 END IF;
1848
1849 /* commenting out so that both includevolume Y and N the l_volume is returned */
1850 /*
1851 IF l_include_volume = 'N' THEN
1852 l_volume := l_volume - l_current_volume;
1853 END IF;
1854 */
1855 RETURN l_volume;
1856
1857 EXCEPTION
1858 WHEN OTHERS THEN
1859 ROLLBACK TO get_numeric_attribute_value;
1860 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
1861 THEN
1862 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1863 END IF;
1864 END get_numeric_attribute_value;
1865
1866
1867 FUNCTION get_beneficiary
1868 (
1869 p_offer_id IN NUMBER
1870 ,p_cust_account_id IN NUMBER
1871 )
1872 RETURN NUMBER
1873 IS
1874 CURSOR c_group_no IS
1875 SELECT group_no
1876 FROM ozf_volume_detail
1877 WHERE offer_id = p_offer_id
1878 AND cust_account_id = p_cust_account_id
1879 AND transaction_date =
1880 (
1881 SELECT MAX(transaction_date)
1882 FROM ozf_volume_detail
1883 WHERE cust_account_id = p_cust_account_id
1884 AND offer_id = p_offer_id
1885 );
1886 /*
1887 CURSOR c_beneficiary(p_group_no NUMBER) IS
1888 SELECT a.beneficiary_party_id
1889 FROM ozf_offr_market_options a, ozf_offr_market_options b
1890 WHERE a.offer_market_option_id = b.offer_market_option_id
1891 AND b.offer_id = p_offer_id
1892 AND b.group_number = p_group_no;
1893 */
1894 CURSOR c_beneficiary(p_group_no NUMBER) IS
1895 SELECT beneficiary_party_id
1896 FROM ozf_offr_market_options
1897 WHERE offer_id = p_offer_id
1898 AND group_number = p_group_no;
1899
1900 l_group_no NUMBER;
1901 l_beneficiary NUMBER;
1902 BEGIN
1903 OPEN c_group_no;
1904 FETCH c_group_no INTO l_group_no;
1905 CLOSE c_group_no;
1906
1907 OPEN c_beneficiary(l_group_no);
1908 FETCH c_beneficiary INTO l_beneficiary;
1909 CLOSE c_beneficiary;
1910
1911 IF l_beneficiary IS NULL THEN
1912 l_beneficiary := p_cust_account_id;
1913 END IF;
1914
1915 RETURN l_beneficiary;
1916 END get_beneficiary;
1917
1918
1919 PROCEDURE update_tracking_line
1920 (
1921 p_init_msg_list IN VARCHAR2
1922 ,p_api_version IN NUMBER
1923 ,p_commit IN VARCHAR2
1924 ,x_return_status OUT NOCOPY VARCHAR2
1925 ,x_msg_count OUT NOCOPY NUMBER
1926 ,x_msg_data OUT NOCOPY VARCHAR2
1927 ,p_list_header_id IN NUMBER
1928 ,p_interface_line_id IN NUMBER
1929 ,p_resale_line_id IN NUMBER
1930 )
1931 IS
1932 l_api_name CONSTANT VARCHAR2(30) := 'update_tracking_line';
1933 BEGIN
1934 SAVEPOINT update_tracking_line;
1935
1936 x_return_status := Fnd_Api.g_ret_sts_success;
1937
1938 UPDATE ozf_order_group_prod
1939 SET order_line_id = p_resale_line_id,
1940 indirect_flag = 'R'
1941 WHERE qp_list_header_id = p_list_header_id
1942 AND order_line_id = p_interface_line_id
1943 AND indirect_flag = 'I';
1944
1945 EXCEPTION
1946 WHEN OTHERS THEN
1947 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
1948 ROLLBACK TO update_tracking_line;
1949 IF Fnd_Msg_Pub.Check_Msg_Level ( Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR )
1950 THEN
1951 Fnd_Msg_Pub.Add_Exc_Msg( G_PKG_NAME,l_api_name);
1952 END IF;
1953 Fnd_Msg_Pub.Count_AND_Get
1954 ( p_count => x_msg_count,
1955 p_data => x_msg_data,
1956 p_encoded => Fnd_Api.G_FALSE
1957 );
1958 END update_tracking_line;
1959
1960
1961 --------------------------
1962 -- Used by Volume Tracking
1963 -- Will return a value only if tracking by GROUP.
1964 --------------------------
1965 FUNCTION get_group_volume
1966 (
1967 p_offer_id IN NUMBER
1968 ,p_group_number IN NUMBER
1969 ,p_pbh_line_id IN NUMBER
1970 )
1971 RETURN NUMBER
1972 IS
1973 l_group_volume NUMBER;
1974 BEGIN
1975
1976 select nvl(group_volume,0) into l_group_volume from ozf_volume_summary
1977 where offer_id = p_offer_id
1978 and group_no = p_group_number
1979 and pbh_line_id = p_pbh_line_id;
1980
1981 return l_group_volume;
1982 END get_group_volume;
1983
1984 FUNCTION get_product_volume
1985 (
1986 p_offer_id IN NUMBER
1987 ,p_pbh_line_id IN NUMBER
1988 ,p_cust_account_id IN NUMBER
1989 ,p_bill_to_id IN NUMBER
1990 ,p_ship_to_id IN NUMBER
1991 )
1992 RETURN NUMBER
1993 IS
1994 l_product_volume NUMBER;
1995 BEGIN
1996
1997 select sum(volume) customer_volume
1998 into l_product_volume
1999 from ozf_volume_detail
2000 where offer_id = p_offer_id
2001 and cust_account_id = p_cust_account_id
2002 and pbh_line_id = p_pbh_line_id
2003 and bill_to_site_use_id = nvl(p_bill_to_id, bill_to_site_use_id)
2004 and ship_to_site_use_id = nvl(p_ship_to_id, ship_to_site_use_id);
2005
2006 return l_product_volume;
2007 END;
2008
2009 FUNCTION get_actual_tier
2010 (
2011 p_offer_id IN NUMBER
2012 ,p_inventory_item_id IN NUMBER
2013 ,p_pbh_line_id IN NUMBER
2014 ,p_cust_account_id IN NUMBER
2015 ,p_bill_to_id IN NUMBER
2016 ,p_ship_to_id IN NUMBER
2017 )
2018 RETURN VARCHAR2
2019 IS
2020 l_volume_range VARCHAR2(30);
2021 l_volume NUMBER;
2022 BEGIN
2023
2024 l_volume := get_product_volume(p_offer_id,p_pbh_line_id,p_cust_account_id,p_bill_to_id,p_ship_to_id);
2025
2026 select volume_from ||'-' || volume_to into l_volume_range
2027 from ozf_offer_discount_lines
2028 where offer_id = p_offer_id
2029 and parent_discount_line_id = p_pbh_line_id
2030 and l_volume between volume_from and volume_to;
2031
2032 return l_volume_range;
2033
2034 END get_actual_tier;
2035
2036 FUNCTION get_actual_discount
2037 (
2038 p_offer_id IN NUMBER
2039 ,p_inventory_item_id IN NUMBER
2040 ,p_pbh_line_id IN NUMBER
2041 ,p_cust_account_id IN NUMBER
2042 ,p_bill_to_id IN NUMBER
2043 ,p_ship_to_id IN NUMBER
2044 )
2045 RETURN VARCHAR2
2046 IS
2047 l_actual_discount NUMBER;
2048 l_volume NUMBER;
2049 BEGIN
2050
2051 l_volume := get_product_volume(p_offer_id,p_pbh_line_id,p_cust_account_id,p_bill_to_id,p_ship_to_id);
2052
2053 select discount into l_actual_discount
2054 from ozf_offer_discount_lines
2055 where offer_id = p_offer_id
2056 and parent_discount_line_id = p_pbh_line_id
2057 and l_volume between volume_from and volume_to;
2058
2059 return l_actual_discount;
2060
2061 END get_actual_discount;
2062
2063
2064 FUNCTION get_preset_tier
2065 (
2066 p_offer_id IN NUMBER
2067 ,p_pbh_line_id IN NUMBER
2068 ,p_group_no IN NUMBER
2069 )
2070 RETURN VARCHAR2
2071 IS
2072 l_volume_range VARCHAR2(30);
2073 l_volume NUMBER;
2074 BEGIN
2075
2076 select c.volume_from ||'-' || c.volume_to into l_volume_range
2077 from ozf_offr_market_options a,
2078 ozf_market_preset_tiers b,
2079 ozf_offer_discount_lines c
2080 where a.offer_id = p_offer_id
2081 and b.offer_market_option_id = a.offer_market_option_id
2082 and a.group_number = p_group_no
2083 and b.pbh_offer_discount_id = p_pbh_line_id
2084 and c.offer_discount_line_id = b.dis_offer_discount_id;
2085
2086 return l_volume_range;
2087
2088 END get_preset_tier;
2089
2090 FUNCTION get_preset_discount
2091 (
2092 p_offer_id IN NUMBER
2093 ,p_pbh_line_id IN NUMBER
2094 ,p_group_no IN NUMBER
2095 )
2096 RETURN VARCHAR2
2097 IS
2098 l_actual_discount NUMBER;
2099 l_volume NUMBER;
2100 BEGIN
2101
2102 select c.discount into l_actual_discount
2103 from ozf_offr_market_options a,
2104 ozf_market_preset_tiers b,
2105 ozf_offer_discount_lines c
2106 where a.offer_id = p_offer_id
2107 and b.offer_market_option_id = a.offer_market_option_id
2108 and a.group_number = p_group_no
2109 and b.pbh_offer_discount_id = p_pbh_line_id
2110 and c.offer_discount_line_id = b.dis_offer_discount_id;
2111
2112 return l_actual_discount;
2113
2114 END get_preset_discount;
2115
2116 FUNCTION get_payout_accrual
2117 (
2118 p_offer_id IN NUMBER
2119 ,p_item_id IN NUMBER
2120 ,p_cust_account_id IN NUMBER
2121 ,p_bill_to_id IN NUMBER
2122 ,p_ship_to_id IN NUMBER
2123 )
2124 RETURN VARCHAR2
2125 IS
2126 l_payout_accrual NUMBER;
2127 l_qp_list_header_id NUMBER;
2128 BEGIN
2129
2130 select qp_list_header_id into l_qp_list_header_id from ozf_offers where offer_id = p_offer_id;
2131
2132 SELECT SUM(uti.plan_curr_amount) into l_payout_accrual
2133 FROM ozf_funds_utilized_all_b uti
2134 WHERE uti.utilization_type IN ('UTILIZED','ACCRUAL','ADJUSTMENT','CHARGEBACK','LEAD_ACCRUAL')
2135 AND plan_type = 'OFFR'
2136 AND plan_id = l_qp_list_header_id
2137 and product_id = p_item_id
2138 and cust_account_id = p_cust_account_id
2139 and bill_to_site_use_id = nvl(p_bill_to_id, bill_to_site_use_id)
2140 and ship_to_site_use_id = nvl(p_ship_to_id, ship_to_site_use_id)
2141 AND gl_posted_flag NOT in('N','F');
2142
2143 return l_payout_accrual;
2144
2145 END;
2146
2147 FUNCTION get_approx_actual_accrual
2148 (
2149 p_offer_id IN NUMBER
2150 ,p_pbh_line_id IN NUMBER
2151 ,p_group_no IN NUMBER
2152 ,p_item_id IN NUMBER
2153 ,p_cust_account_id IN NUMBER
2154 ,p_bill_to_id IN NUMBER
2155 ,p_ship_to_id IN NUMBER
2156 )
2157 RETURN VARCHAR2
2158 IS
2159 l_actual_accrual NUMBER;
2160 l_actual_discount NUMBER;
2161 l_preset_discount NUMBER;
2162 l_payout_accrual NUMBER;
2163
2164 BEGIN
2165
2166 l_actual_discount := get_actual_discount(p_offer_id,p_item_id,p_pbh_line_id,p_cust_account_id, p_bill_to_id, p_ship_to_id);
2167 l_preset_discount := get_preset_discount(p_offer_id,p_pbh_line_id,p_group_no);
2168 l_payout_accrual := get_payout_accrual(p_offer_id, p_item_id,p_cust_account_id, p_bill_to_id, p_ship_to_id);
2169
2170 if (l_preset_discount > l_actual_discount) then
2171 l_actual_accrual := (((l_payout_accrual * 100)/l_preset_discount)*l_actual_discount)/100;
2172 else
2173 l_actual_accrual := l_payout_accrual;
2174 end if;
2175
2176 return l_actual_accrual;
2177
2178 END;
2179
2180
2181 END OZF_VOLUME_CALCULATION_PUB;