[Home] [Help]
PACKAGE BODY: APPS.QP_CROSS_ORDER_VOLUME_LOAD
Source
1 PACKAGE BODY QP_Cross_Order_Volume_Load AS
2 /* $Header: QPXCOVLB.pls 120.7 2006/10/03 12:13:33 nirmkuma ship $ */
3
4
5 G_LOAD_EFFECTIVE_DATE DATE;
6 G_ORG_ID NUMBER;
7
8
9 /*================================================================================
10 function get_uom_code
11 description
12 Retrieves primary unit of measure for an item.
13 =============================================================================== */
14
15 FUNCTION get_uom_code(pitem_id NUMBER,porg_id NUMBER) RETURN VARCHAR2 IS
16
17 CURSOR Cur_getuom(citem_id NUMBER,corg_id NUMBER) IS
18 SELECT primary_uom_code
19 FROM mtl_system_items
20 WHERE inventory_item_id = citem_id
21 AND organization_id = corg_id;
22
23 l_uom_code VARCHAR2(3);
24
25 BEGIN
26 OPEN Cur_getuom(pitem_id,porg_id);
27 FETCH Cur_getuom INTO l_uom_code;
28 CLOSE Cur_getuom;
29 RETURN(l_uom_code);
30
31 EXCEPTION
32 WHEN OTHERS THEN RAISE;
33
34 END get_uom_code;
35
36 FUNCTION convert_to_base_curr(p_trans_amount NUMBER, p_from_currency VARCHAR2,
37 p_to_currency VARCHAR2, p_conversion_date DATE,
38 p_conversion_rate NUMBER, p_conversion_type VARCHAR2)
39 RETURN NUMBER IS
40
41 l_conversion_type VARCHAR2(30);
42 l_conversion_date DATE;
43 l_rate_exists VARCHAR2(1);
44 l_converted_amount NUMBER;
45 l_max_roll_days NUMBER;
46 l_denominator NUMBER;
47 l_numerator NUMBER;
48 l_rate NUMBER;
49 No_User_Defined_Rate EXCEPTION;
50 x_trans_amount NUMBER;
51 x_return_status VARCHAR2(1);
52 NO_RATE EXCEPTION;
53 INVALID_CURRENCY EXCEPTION;
54
55 BEGIN
56 x_trans_amount := p_trans_amount;
57 l_max_roll_days := 300;
58 l_conversion_type := NVL(p_conversion_type, 'Corporate');
59 l_conversion_date := NVL(p_conversion_date,g_load_effective_date);
60
61 IF x_trans_amount = 0
62 THEN
63 return(0);
64
65 ELSIF (GL_CURRENCY_API.Is_Fixed_Rate( p_from_currency,
66 p_to_currency, p_conversion_date) = 'Y')
67 THEN
68 x_trans_amount := GL_CURRENCY_API.convert_amount(
69 p_from_currency,
70 p_to_currency,
71 l_conversion_date,
72 l_conversion_type,
73 p_trans_amount
74 );
75 return(x_trans_amount);
76 ELSIF (l_conversion_type = 'User')
77 THEN
78 IF (p_conversion_rate IS NOT NULL) THEN
79 x_trans_amount := p_trans_amount * p_conversion_rate;
80 return(x_trans_amount);
81 ELSE
82 RAISE No_User_Defined_Rate;
83 END IF;
84 ELSE
85 l_rate_exists := GL_CURRENCY_API.Rate_Exists(
86 x_from_currency => p_from_currency,
87 x_to_currency => p_to_currency,
88 x_conversion_date => l_conversion_date,
89 x_conversion_type => l_conversion_type
90 );
91 IF (l_rate_exists = 'Y') THEN
92 x_trans_amount := GL_CURRENCY_API.convert_amount(
93 p_from_currency,
94 p_to_currency,
95 l_conversion_date,
96 l_conversion_type,
97 p_trans_amount
98 );
99 return(x_trans_amount);
100 ELSE
101 BEGIN
102 GL_CURRENCY_API.convert_closest_amount(
103 x_from_currency => p_from_currency,
104 x_to_currency => p_to_currency,
105 x_conversion_date => l_conversion_date,
106 x_conversion_type => l_conversion_type,
107 x_user_rate => p_conversion_rate,
108 x_amount => p_trans_amount,
109 x_max_roll_days => l_max_roll_days,
110 x_converted_amount=> l_converted_amount,
111 x_denominator => l_denominator,
112 x_numerator => l_numerator,
113 x_rate => l_rate);
114 EXCEPTION
115 WHEN OTHERS THEN
116 if (l_numerator >0) AND (l_denominator >0)
117 THEN
118 return(0);
119 ELSIF (l_numerator =-2) OR (l_denominator =-2)
120 THEN
121 RAISE INVALID_CURRENCY;
122 ELSE
123 RAISE NO_RATE;
124 END IF;
125 END;
126 x_trans_amount := l_converted_amount;
127 return(x_trans_amount);
128 END IF;
129
130 END IF;
131
132 EXCEPTION
133 WHEN No_User_Defined_Rate THEN
134 return(0);
135 WHEN NO_RATE THEN
136 fnd_file.put_line(FND_FILE.LOG,'No rate is defined');
137 RAISE;
138 WHEN INVALID_CURRENCY THEN
139 fnd_file.put_line(FND_FILE.LOG,'Invalid currency');
140 RAISE;
141 WHEN OTHERS THEN
142 RAISE;
143
144 END convert_to_base_curr;
145
146 /*================================================================================
147 function get_converted_qty
148 description
149 Converts order unit of measure quantity to primary unit of measure qty.
150 ================================================================================= */
151
152 FUNCTION get_converted_qty(pitem_id NUMBER,porg_id NUMBER,pordr_qty NUMBER,porduom VARCHAR2) RETURN NUMBER IS
153 l_uom_code VARCHAR2(3);
154 l_item_rate NUMBER;
155 l_converted_qty NUMBER;
156 l_error_message VARCHAR2(200);
157
158 BEGIN
159 l_uom_code := get_uom_code(pitem_id,porg_id);
160 IF l_uom_code <> porduom
161 THEN
162 l_converted_qty := inv_convert.inv_um_convert(pitem_id,NULL,pordr_qty,porduom,l_uom_code,NULL,NULL);
163
164 -- Check conversion found
165 IF l_converted_qty = -99999
166 THEN
167 -- Log message
168 FND_MESSAGE.SET_NAME('QP','QP_XORD_UOM_CONVERSION');
169 FND_MESSAGE.SET_TOKEN('ITEM',pitem_id);
170 FND_MESSAGE.SET_TOKEN('ORG',porg_id);
171 FND_MESSAGE.SET_TOKEN('FROM_UOM',porduom);
172 FND_MESSAGE.SET_TOKEN('TO_UOM',l_uom_code);
173 l_error_message := FND_MESSAGE.GET;
174 fnd_file.put_line(FND_FILE.LOG,l_error_message);
175
176 RETURN(0);
177 ELSE
178 RETURN(l_converted_qty);
179 END IF;
180
181 ELSE
182 RETURN(pordr_qty);
183 END IF;
184 END get_converted_qty;
185
186 /*================================================================================
187 function get_value
188 description
189 Evaluates a condition and if it is true then return first value else second value.
190 ================================================================================= */
191
192 FUNCTION get_value(req_date DATE,perd_val NUMBER,p_inval NUMBER,p_invaltwo NUMBER) RETURN NUMBER IS
193 BEGIN
194 IF (trunc(req_date) <= (trunc(g_load_effective_date))) THEN
195 IF (trunc(g_load_effective_date) - trunc(req_date) <= perd_val) THEN
196 RETURN(p_inval);
197 ELSE
198 RETURN(p_invaltwo);
199 END IF;
200 ELSE
201 RETURN(p_invaltwo);
202 END IF;
203 END get_value;
204
205 /*================================================================================
206 function multi_org_install
207 description
208 returns true if install is multi-org, otherwise returns false.
209 ================================================================================= */
210
211 FUNCTION multi_org_install RETURN BOOLEAN IS
212 l_is_multiorg_enabled VARCHAR2(1) := 'N';
213 BEGIN
214 /*commented for moac
215 IF SUBSTRB(USERENV('CLIENT_INFO'),1,1) is null THEN
216 RETURN FALSE;
217 ELSE
218 RETURN TRUE;
219 END IF;
220 */
221 --added for MOAC
222 l_is_multiorg_enabled := MO_GLOBAL.is_multi_org_enabled;
223 IF l_is_multiorg_enabled = 'Y' THEN
224 RETURN TRUE;
225 ELSE
226 RETURN FALSE;
227 END IF;
228 EXCEPTION
229 WHEN OTHERS THEN
230 RETURN FALSE;
231 END multi_org_install;
232
233 /*================================================================================
234 function create_crossordvol_brk
235 description
236 Calculates customer volumes for an item.
237 ================================================================================== */
238
239
240
241 PROCEDURE create_crossordvol_brk
242 (err_buff out NOCOPY /* file.sql.39 change */ VARCHAR2,
243 retcode out NOCOPY /* file.sql.39 change */ NUMBER,
244 x_org_id in NUMBER,
245 x_load_effective_date in VARCHAR2) IS
246
247 l_ordr_vol_perd1 NUMBER DEFAULT 0;
248 l_ordr_vol_perd2 NUMBER DEFAULT 0;
249 l_ordr_vol_perd3 NUMBER DEFAULT 0;
250 l_converted_qty1 NUMBER DEFAULT 0;
251 l_converted_qty2 NUMBER DEFAULT 0;
252 l_converted_qty3 NUMBER DEFAULT 0;
253 l_period1_amount NUMBER DEFAULT 0;
254 l_period2_amount NUMBER DEFAULT 0;
255 l_period3_amount NUMBER DEFAULT 0;
256 l_sob_id NUMBER DEFAULT 0;
257 l_sob_currency VARCHAR2(15);
258 l_period1_item_qty_attr VARCHAR2(30);
259 l_period2_item_qty_attr VARCHAR2(30);
260 l_period3_item_qty_attr VARCHAR2(30);
261 l_period1_item_amt_attr VARCHAR2(30);
262 l_period2_item_amt_attr VARCHAR2(30);
263 l_period3_item_amt_attr VARCHAR2(30);
264 l_multi_org_install BOOLEAN DEFAULT TRUE;
265
266 -- Cursor to get set of books id.
267
268 CURSOR Cur_get_sob_currency(psob_id NUMBER) IS
269 SELECT gsob.currency_code
270 FROM gl_sets_of_books gsob
271 WHERE gsob.set_of_books_id = psob_id;
272
273 -- Cursor to get all items which have cross order volume pricing attributes defined
274
275 CURSOR Cur_get_items IS
276 SELECT distinct to_number(qpa.product_attr_value) c_inventory_item_id
277 FROM qp_list_headers qlh,
278 qp_list_lines qpl,
279 qp_pricing_attributes qpa
280 WHERE qlh.list_header_id = qpl.list_header_id
281 AND qpl.list_line_id = qpa.list_line_id
282 AND qlh.active_flag = 'Y'
283 AND qpa.product_attribute_context = 'ITEM'
284 AND qpa.product_attribute = qp_util.get_attribute_name('QP',
285 'QP_ATTR_DEFNS_PRICING','ITEM','INVENTORY_ITEM_ID')
286 AND qpa.pricing_attribute_context = 'VOLUME'
287 AND ( qpa.pricing_attribute = l_period1_item_qty_attr
288 OR qpa.pricing_attribute = l_period2_item_qty_attr
289 OR qpa.pricing_attribute = l_period3_item_qty_attr
290 OR qpa.pricing_attribute = l_period1_item_amt_attr
291 OR qpa.pricing_attribute = l_period2_item_amt_attr
292 OR qpa.pricing_attribute = l_period3_item_amt_attr);
293
294 BEGIN
295
296 l_multi_org_install := multi_org_install;
297
298 -- Set Variables
299 G_load_effective_date := NVL(fnd_date.canonical_to_date(x_load_effective_date),sysdate);
300
301 IF l_multi_org_install
302 THEN
303
304 G_org_id := x_org_id;
305
306 ELSE
307
308 -- Get Master Organization
309
310 G_org_id := oe_sys_parameters.value('MASTER_ORGANIZATION_ID',g_org_id);
311
312 -- Get Set of Books currency
313
314 --added for moac to call Oe_sys_params only if org_id is not null
315 IF G_org_id IS NOT NULL THEN
316 l_sob_id := oe_sys_parameters.value('SET_OF_BOOKS_ID',g_org_id);
317 ELSE
318 l_sob_id := null;
319 END IF;--if g_org_id
320
321 OPEN Cur_get_sob_currency(l_sob_id);
322 FETCH Cur_get_sob_currency INTO l_sob_currency;
323 CLOSE Cur_get_sob_currency;
324
325 END IF;
326
327 -- Get profile values
328 l_ordr_vol_perd1 := to_number(FND_PROFILE.VALUE('QP_CROSS_ORDER_VOLUME_PERIOD1'));
329 l_ordr_vol_perd2 := to_number(FND_PROFILE.VALUE('QP_CROSS_ORDER_VOLUME_PERIOD2'));
330 l_ordr_vol_perd3 := to_number(FND_PROFILE.VALUE('QP_CROSS_ORDER_VOLUME_PERIOD3'));
331
332 -- Get Attribute columns for Cross Order Pricing Attributes
333 l_period1_item_qty_attr := qp_util.get_attribute_name ('QP',
334 'QP_ATTR_DEFNS_PRICING','VOLUME','PERIOD1_ITEM_QUANTITY');
335 l_period2_item_qty_attr := qp_util.get_attribute_name ('QP',
336 'QP_ATTR_DEFNS_PRICING','VOLUME','PERIOD2_ITEM_QUANTITY');
337 l_period3_item_qty_attr := qp_util.get_attribute_name ('QP',
338 'QP_ATTR_DEFNS_PRICING','VOLUME','PERIOD3_ITEM_QUANTITY');
339 l_period1_item_amt_attr := qp_util.get_attribute_name ('QP',
340 'QP_ATTR_DEFNS_PRICING','VOLUME','PERIOD1_ITEM_AMOUNT');
341 l_period2_item_amt_attr := qp_util.get_attribute_name ('QP',
342 'QP_ATTR_DEFNS_PRICING','VOLUME','PERIOD2_ITEM_AMOUNT');
343 l_period3_item_amt_attr := qp_util.get_attribute_name ('QP',
344 'QP_ATTR_DEFNS_PRICING','VOLUME','PERIOD3_ITEM_AMOUNT');
345
346
347 IF (g_org_id IS NULL) OR (NOT l_multi_org_install)
348 THEN
349
350 DELETE FROM OE_ITEM_CUST_VOLS_ALL;
351
352 ELSE
353
354 DELETE FROM OE_ITEM_CUST_VOLS_ALL
355 WHERE ORG_ID = g_org_id;
356
357 END IF;
358
359 /* Fix for Bug# 1798953. Instead of using lines.org, used oe_sys_parameters.value('MASTER_ORGANIZATION_ID',lines.org_id) to use the master organization in get_uom_code and get_converted_qty functions */
360
361 /* Fix for Bug# 3558790. Instead of using lines.pricing_quantity for calculating item amounts, used lines.ordered_quantity */
362 FOR i IN Cur_get_items
363 LOOP
364
365 IF l_multi_org_install THEN
366
367 INSERT INTO OE_ITEM_CUST_VOLS_ALL
368 (org_id,
369 inventory_item_id,
370 sold_to_org_id,
371 primary_uom_code,
372 period1_ordered_quantity,
373 period2_ordered_quantity,
374 period3_ordered_quantity,
375 period1_total_amount,
376 period2_total_amount,
377 period3_total_amount,
378 creation_date,
379 created_by,
380 last_update_date,
381 last_updated_by,
382 last_update_login,
383 program_application_id,
384 program_id,
385 program_update_date,
386 request_id)
387 (SELECT lines.org_id,
388 lines.inventory_item_id,
389 lines.sold_to_org_id,
390 get_uom_code(lines.inventory_item_id,oe_sys_parameters.value('MASTER_ORGANIZATION_ID',lines.org_id)),
391 sum(get_value(hdrs.ordered_date,l_ordr_vol_perd1,
392 get_converted_qty(lines.inventory_item_id,oe_sys_parameters.value('MASTER_ORGANIZATION_ID',lines.org_id),
393 lines.ordered_quantity,lines.order_quantity_uom),0)),
394 sum(get_value(hdrs.ordered_date,l_ordr_vol_perd2,
395 get_converted_qty(lines.inventory_item_id,oe_sys_parameters.value('MASTER_ORGANIZATION_ID',lines.org_id),
396 lines.ordered_quantity,lines.order_quantity_uom),0)),
397 sum(get_value(hdrs.ordered_date,l_ordr_vol_perd3,
398 get_converted_qty(lines.inventory_item_id,oe_sys_parameters.value('MASTER_ORGANIZATION_ID',lines.org_id),
399 lines.ordered_quantity,lines.order_quantity_uom),0)),
400 sum(decode(hdrs.transactional_curr_code,gsob.currency_code,
401 get_value(hdrs.ordered_date,l_ordr_vol_perd1,
402 lines.ordered_quantity*lines.unit_list_price,0),
403 convert_to_base_curr(get_value(hdrs.ordered_date,
404 l_ordr_vol_perd1,lines.ordered_quantity*lines.unit_list_price,0),
405 hdrs.transactional_curr_code,gsob.currency_code,hdrs.conversion_rate_date,
406 hdrs.conversion_rate,hdrs.conversion_type_code))),
407 sum(decode(hdrs.transactional_curr_code,gsob.currency_code,
408 get_value(hdrs.ordered_date,l_ordr_vol_perd2,
409 lines.ordered_quantity*lines.unit_list_price,0),
410 convert_to_base_curr(get_value(hdrs.ordered_date,
411 l_ordr_vol_perd2,lines.ordered_quantity*lines.unit_list_price,0),
412 hdrs.transactional_curr_code,gsob.currency_code,hdrs.conversion_rate_date,
413 hdrs.conversion_rate,hdrs.conversion_type_code))),
414 sum(decode(hdrs.transactional_curr_code,gsob.currency_code,
415 get_value(hdrs.ordered_date,l_ordr_vol_perd3,
416 lines.ordered_quantity*lines.unit_list_price,0),
417 convert_to_base_curr(get_value(hdrs.ordered_date,
418 l_ordr_vol_perd3,lines.ordered_quantity*lines.unit_list_price,0),
419 hdrs.transactional_curr_code,gsob.currency_code,hdrs.conversion_rate_date,
420 hdrs.conversion_rate,hdrs.conversion_type_code))),
421 sysdate,
422 p_created_by,
423 sysdate,
424 p_user_id,
425 p_login_id,
426 P_program_appl_id,
427 P_conc_program_id,
428 sysdate,
429 p_request_id
430 FROM oe_order_headers_all hdrs,
431 oe_order_lines_all lines,
432 hr_operating_units hou,
433 gl_sets_of_books gsob
434 WHERE hdrs.header_id = lines.header_id
435 AND lines.org_id = hou.organization_id
436 AND hou.set_of_books_id = gsob.set_of_books_id
437 AND lines.inventory_item_id = i.c_inventory_item_id
438 AND lines.line_category_code <> 'RETURN'
439 AND lines.org_id = nvl(g_org_id,lines.org_id)
440 AND lines.sold_to_org_id is not null
441 AND lines.booked_flag = 'Y'
442 AND nvl(lines.cancelled_flag,'N') = 'N'
443 AND lines.charge_periodicity_code is null -- added for recurring charges Bug 4465168
444 GROUP BY lines.inventory_item_id,lines.org_id,0,lines.sold_to_org_id);
445
446 ELSE /* not multiple sets of books */
447
448 INSERT INTO OE_ITEM_CUST_VOLS_ALL
449 (org_id,
450 inventory_item_id,
451 sold_to_org_id,
452 primary_uom_code,
453 period1_ordered_quantity,
454 period2_ordered_quantity,
455 period3_ordered_quantity,
456 period1_total_amount,
457 period2_total_amount,
458 period3_total_amount,
459 creation_date,
460 created_by,
461 last_update_date,
462 last_updated_by,
463 last_update_login,
464 program_application_id,
465 program_id,
466 program_update_date,
467 request_id)
468 (SELECT lines.org_id,
469 lines.inventory_item_id,
470 lines.sold_to_org_id,
471 get_uom_code(lines.inventory_item_id,g_org_id),
472 sum(get_value(hdrs.ordered_date,l_ordr_vol_perd1,
473 get_converted_qty(lines.inventory_item_id,g_org_id,
474 lines.ordered_quantity,lines.order_quantity_uom),0)),
475 sum(get_value(hdrs.ordered_date,l_ordr_vol_perd2,
476 get_converted_qty(lines.inventory_item_id,g_org_id,
477 lines.ordered_quantity,lines.order_quantity_uom),0)),
478 sum(get_value(hdrs.ordered_date,l_ordr_vol_perd3,
479 get_converted_qty(lines.inventory_item_id,g_org_id,
480 lines.ordered_quantity,lines.order_quantity_uom),0)),
481 sum(decode(hdrs.transactional_curr_code,l_sob_currency,
482 get_value(hdrs.ordered_date,l_ordr_vol_perd1,
483 lines.ordered_quantity*lines.unit_list_price,0),
484 convert_to_base_curr(get_value(hdrs.ordered_date,
485 l_ordr_vol_perd1,lines.ordered_quantity*lines.unit_list_price,0),
486 hdrs.transactional_curr_code,l_sob_currency,hdrs.conversion_rate_date,
487 hdrs.conversion_rate,hdrs.conversion_type_code))),
488 sum(decode(hdrs.transactional_curr_code,l_sob_currency,
489 get_value(hdrs.ordered_date,l_ordr_vol_perd2,
490 lines.ordered_quantity*lines.unit_list_price,0),
491 convert_to_base_curr(get_value(hdrs.ordered_date,
492 l_ordr_vol_perd2,lines.ordered_quantity*lines.unit_list_price,0),
493 hdrs.transactional_curr_code,l_sob_currency,hdrs.conversion_rate_date,
494 hdrs.conversion_rate,hdrs.conversion_type_code))),
495 sum(decode(hdrs.transactional_curr_code,l_sob_currency,
496 get_value(hdrs.ordered_date,l_ordr_vol_perd3,
497 lines.ordered_quantity*lines.unit_list_price,0),
498 convert_to_base_curr(get_value(hdrs.ordered_date,
499 l_ordr_vol_perd3,lines.ordered_quantity*lines.unit_list_price,0),
500 hdrs.transactional_curr_code,l_sob_currency,hdrs.conversion_rate_date,
501 hdrs.conversion_rate,hdrs.conversion_type_code))),
502 sysdate,
503 p_created_by,
504 sysdate,
505 p_user_id,
506 p_login_id,
507 P_program_appl_id,
508 P_conc_program_id,
509 sysdate,
510 p_request_id
511 FROM oe_order_headers_all hdrs,
512 oe_order_lines_all lines
513 WHERE hdrs.header_id = lines.header_id
514 AND lines.inventory_item_id = i.c_inventory_item_id
515 AND lines.line_category_code <> 'RETURN'
516 AND lines.sold_to_org_id is not null
517 AND lines.booked_flag = 'Y'
518 AND nvl(lines.cancelled_flag,'N') = 'N'
519 AND lines.charge_periodicity_code is null -- added for recurring charges Bug 4465168
520 GROUP BY lines.inventory_item_id,lines.org_id,0,lines.sold_to_org_id);
521
522 END IF;
523
524 END LOOP;
525
526 -- This routine calculates customer total order amounts for the three periods.
527 get_customer_total_amnts(l_ordr_vol_perd1,l_ordr_vol_perd2,l_ordr_vol_perd3,l_sob_currency);
528
529 COMMIT;
530
531 -- set return status
532 err_buff := '';
533 retcode := 0;
534
535 EXCEPTION
536 WHEN OTHERS THEN
537 fnd_file.put_line(FND_FILE.LOG,substr(sqlerrm,1,300));
538 retcode := 2;
539
540 END create_crossordvol_brk;
541
542 /*================================================================================
543 function get_customer_total_amnts
544 description
545 Calculates the total volume for a customer.
546 ================================================================================= */
547
548
549 PROCEDURE get_customer_total_amnts(x_cross_ordr_vol_perd1 NUMBER,
550 x_cross_ordr_vol_perd2 NUMBER,
551 x_cross_ordr_vol_perd3 NUMBER,
552 x_sob_currency VARCHAR2) IS
553
554
555 l_period1_order_amt_attr VARCHAR2(30);
556 l_period2_order_amt_attr VARCHAR2(30);
557 l_period3_order_amt_attr VARCHAR2(30);
558 l_cross_order_qualifiers VARCHAR2(1) DEFAULT 'N';
559 l_ordr_vol_perd1 NUMBER := x_cross_ordr_vol_perd1;
560 l_ordr_vol_perd2 NUMBER := x_cross_ordr_vol_perd2;
561 l_ordr_vol_perd3 NUMBER := x_cross_ordr_vol_perd3;
562 l_sob_currency VARCHAR2(15) := x_sob_currency;
563
564 CURSOR Cur_cross_order_qualifiers IS
565 SELECT 'Y'
566 FROM qp_list_headers qlh,
567 qp_qualifiers qq
568 WHERE qq.list_header_id = qlh.list_header_id
569 AND qlh.active_flag = 'Y'
570 AND qq.qualifier_context = 'VOLUME'
571 AND ( qualifier_attribute = l_period1_order_amt_attr
572 OR qualifier_attribute = l_period2_order_amt_attr
573 OR qualifier_attribute = l_period3_order_amt_attr);
574
575
576 BEGIN
577
578 IF (g_org_id IS NULL) OR (NOT multi_org_install)
579 THEN
580
581 DELETE FROM OE_CUST_TOTAL_AMTS_ALL;
582
583 ELSE
584
585
586 DELETE FROM OE_CUST_TOTAL_AMTS_ALL
587 WHERE ORG_ID = g_org_id;
588
589 END IF;
590
591 -- Get Attribute columns for Cross Order Qualifier Attributes
592 l_period1_order_amt_attr := qp_util.get_attribute_name ('QP',
593 'QP_ATTR_DEFNS_QUALIFIER','VOLUME','PERIOD1_ORDER_AMOUNT');
594 l_period2_order_amt_attr := qp_util.get_attribute_name ('QP',
595 'QP_ATTR_DEFNS_QUALIFIER','VOLUME','PERIOD2_ORDER_AMOUNT');
596 l_period3_order_amt_attr := qp_util.get_attribute_name ('QP',
597 'QP_ATTR_DEFNS_QUALIFIER','VOLUME','PERIOD3_ORDER_AMOUNT');
598
599 -- Check to see if Cross Order Volume Qualifiers have been used on Active Lists
600
601 OPEN Cur_cross_order_qualifiers;
602 FETCH Cur_cross_order_qualifiers INTO l_cross_order_qualifiers;
603 CLOSE Cur_cross_order_qualifiers;
604
605
606 IF l_cross_order_qualifiers = 'Y'
607 THEN
608
609 IF multi_org_install THEN
610
611 INSERT INTO OE_CUST_TOTAL_AMTS_ALL
612 (org_id,
613 sold_to_org_id,
614 period1_total_amount,
615 period2_total_amount,
616 period3_total_amount,
617 creation_date,
618 created_by,
619 last_update_date,
620 last_updated_by,
621 last_update_login,
622 program_application_id,
623 program_id,
624 program_update_date,
625 request_id)
626 (SELECT lines.org_id,
627 lines.sold_to_org_id,
628 sum(decode(hdrs.transactional_curr_code,gsob.currency_code,
629 get_value(hdrs.ordered_date,l_ordr_vol_perd1,
630 lines.ordered_quantity*lines.unit_list_price,0),
631 convert_to_base_curr(get_value(hdrs.ordered_date,
632 l_ordr_vol_perd1,lines.ordered_quantity*lines.unit_list_price,0),
633 hdrs.transactional_curr_code,gsob.currency_code,hdrs.conversion_rate_date,
634 hdrs.conversion_rate,hdrs.conversion_type_code))),
635 sum(decode(hdrs.transactional_curr_code,gsob.currency_code,
636 get_value(hdrs.ordered_date,l_ordr_vol_perd2,
637 lines.ordered_quantity*lines.unit_list_price,0),
638 convert_to_base_curr(get_value(hdrs.ordered_date,
639 l_ordr_vol_perd2,lines.ordered_quantity*lines.unit_list_price,0),
640 hdrs.transactional_curr_code,gsob.currency_code,hdrs.conversion_rate_date,
641 hdrs.conversion_rate,hdrs.conversion_type_code))),
642 sum(decode(hdrs.transactional_curr_code,gsob.currency_code,
643 get_value(hdrs.ordered_date,l_ordr_vol_perd3,
644 lines.ordered_quantity*lines.unit_list_price,0),
645 convert_to_base_curr(get_value(hdrs.ordered_date,
646 l_ordr_vol_perd3,lines.ordered_quantity*lines.unit_list_price,0),
647 hdrs.transactional_curr_code,gsob.currency_code,hdrs.conversion_rate_date,
648 hdrs.conversion_rate,hdrs.conversion_type_code))),
649 sysdate,
650 p_user_id,
651 sysdate,
652 P_user_id,
653 P_login_id,
654 P_program_appl_id,
655 P_conc_program_id,
656 sysdate,
657 P_request_id
658 FROM oe_order_headers_all hdrs,
659 oe_order_lines_all lines,
660 hr_operating_units hou,
661 gl_sets_of_books gsob
662 WHERE hdrs.header_id = lines.header_id
663 AND lines.org_id = hou.organization_id
664 AND hou.set_of_books_id = gsob.set_of_books_id
665 AND lines.line_category_code <> 'RETURN'
666 AND lines.org_id = NVL(g_org_id,lines.org_id)
667 AND lines.sold_to_org_id is not null
668 AND lines.booked_flag = 'Y'
669 AND nvl(lines.cancelled_flag,'N') = 'N'
670 AND lines.charge_periodicity_code is null -- added for recurring charges Bug 4465168
671 GROUP BY lines.org_id,lines.sold_to_org_id);
672
673 ELSE
674
675 INSERT INTO OE_CUST_TOTAL_AMTS_ALL
676 (org_id,
677 sold_to_org_id,
678 period1_total_amount,
679 period2_total_amount,
680 period3_total_amount,
681 creation_date,
682 created_by,
683 last_update_date,
684 last_updated_by,
685 last_update_login,
686 program_application_id,
687 program_id,
688 program_update_date,
689 request_id)
690 (SELECT lines.org_id,
691 lines.sold_to_org_id,
692 sum(decode(hdrs.transactional_curr_code,l_sob_currency,
693 get_value(hdrs.ordered_date,l_ordr_vol_perd1,
694 lines.ordered_quantity*lines.unit_list_price,0),
695 convert_to_base_curr(get_value(hdrs.ordered_date,
696 l_ordr_vol_perd1,lines.ordered_quantity*lines.unit_list_price,0),
697 hdrs.transactional_curr_code,l_sob_currency,hdrs.conversion_rate_date,
698 hdrs.conversion_rate,hdrs.conversion_type_code))),
699 sum(decode(hdrs.transactional_curr_code,l_sob_currency,
700 get_value(hdrs.ordered_date,l_ordr_vol_perd2,
701 lines.ordered_quantity*lines.unit_list_price,0),
702 convert_to_base_curr(get_value(hdrs.ordered_date,
703 l_ordr_vol_perd2,lines.ordered_quantity*lines.unit_list_price,0),
704 hdrs.transactional_curr_code,l_sob_currency,hdrs.conversion_rate_date,
705 hdrs.conversion_rate,hdrs.conversion_type_code))),
706 sum(decode(hdrs.transactional_curr_code,l_sob_currency,
707 get_value(hdrs.ordered_date,l_ordr_vol_perd3,
708 lines.ordered_quantity*lines.unit_list_price,0),
709 convert_to_base_curr(get_value(hdrs.ordered_date,
710 l_ordr_vol_perd3,lines.ordered_quantity*lines.unit_list_price,0),
711 hdrs.transactional_curr_code,l_sob_currency,hdrs.conversion_rate_date,
712 hdrs.conversion_rate,hdrs.conversion_type_code))),
713 sysdate,
714 p_user_id,
715 sysdate,
716 P_user_id,
717 P_login_id,
718 P_program_appl_id,
719 P_conc_program_id,
720 sysdate,
721 P_request_id
722 FROM oe_order_headers_all hdrs,
723 oe_order_lines_all lines
724 WHERE hdrs.header_id = lines.header_id
725 AND lines.line_category_code <> 'RETURN'
726 AND lines.sold_to_org_id is not null
727 AND lines.booked_flag = 'Y'
728 AND nvl(lines.cancelled_flag,'N') = 'N'
729 AND lines.charge_periodicity_code is null -- added for recurring charges Bug 4465168
730 GROUP BY lines.org_id,lines.sold_to_org_id);
731
732 END IF; /* Mulit org. Install */
733
734 END IF; /* Cross Order Qualifiers */
735
736 EXCEPTION
737 WHEN OTHERS THEN
738 fnd_file.put_line(FND_FILE.LOG,substr(sqlerrm,1,300));
739 retcode := 2;
740
741 END get_customer_total_amnts;
742 END QP_Cross_Order_Volume_Load;