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