1 PACKAGE BODY QPR_SR_UTIL AS
2 /* $Header: QPRUTILB.pls 120.6 2008/03/12 12:57:35 amjha ship $ */
3
4 g_ref_uom VARCHAR2(10);
5 g_ref_item_id NUMBER;
6 g_ref_uom_conv_rate NUMBER;
7 g_ods_from_uom varchar2(30);
8 g_ods_to_uom varchar2(30);
9 g_ods_ref_uom_crate number;
10 g_ods_uom_inst_id number;
11 g_uom_conv_in_ods varchar2(1);
12 g_curr_conv_in_ods varchar2(10);
13 g_ods_curr_inst_id number;
14 g_from_currency_code varchar2(30);
15 g_ods_rate_type varchar2(40);
16 g_to_currency_code varchar2(30);
17 g_ods_curr_date date;
18 g_ods_curr_rate number;
19 g_ods_ref_item_id number;
20 g_ref_curr varchar2(30);
21 g_ref_curr_date date;
22 g_ref_global_curr_code varchar2(30);
23 g_curr_conv_rate number;
24 g_global_currency_code varchar2(40);
25 g_global_rate_type varchar2(40);
26 g_ref_cust_attr varchar2(30);
27 g_base_item number;
28 g_uom_instance number;
29 g_ref_base_uom varchar2(10);
30 g_master_uom varchar2(10);
31 g_load_curr_no_conv varchar2(1);
32 g_load_uom_no_conv varchar2(1) ;
33 g_master_org number;
34 g_b_cust_attr_read number := 0;
35
36 /* Public Constants */
37 NULL_VALUE_CONST constant varchar2(10) := 'Other';
38 NULL_VALUE_PK_CONST constant number := -777;
39 ALL_CHANNELS_CONST constant varchar(20) := 'All Channels';
40 ALL_CHANNELS_PK_CONST constant number := -5 ;
41 ALL_GEOGRAPHY_CONST constant varchar(20) := 'All Geography';
42 ALL_GEOGRAPHY_PK_CONST constant number := -3 ;
43 ALL_ORGANIZATIONS_CONST constant varchar(20) := 'All Organizations';
44 ALL_ORGANIZATIONS_PK_CONST constant number := -2 ;
45 ALL_PRODUCTS_CONST constant varchar(20) := 'All Products';
46 ALL_PRODUCTS_PK_CONST constant number := -1 ;
47 ALL_SALESREP_CONST constant varchar(20) := 'All Sales Rep';
48 ALL_SALESREP_PK_CONST constant number := -4 ;
49
50 ALL_ORDER_CONST constant varchar(20) := 'All Orders ';
51 ALL_ORDER_PK_CONST constant number := -6 ;
52 ALL_ADJUSTMENT_CONST constant varchar(20) := 'All Adjustments ';
53 ALL_ADJUSTMENT_PK_CONST constant number := -7 ;
54 ALL_CUSTOMER_CONST constant varchar(20) := 'All Customers ';
55 ALL_CUSTOMER_PK_CONST constant number := -8 ;
56
57 ALL_VOL_BAND_CONST constant varchar(20) := 'All Volume Bands ';
58 ALL_VOL_BAND_PK_CONST constant number := -11 ;
59 ALL_DSC_BAND_CONST constant varchar(20) := 'All Discount Bands ';
60 ALL_DSC_BAND_PK_CONST constant number := -12 ;
61 ALL_MRG_BAND_CONST constant varchar(20) := 'All Margin Bands ';
62 ALL_MRG_BAND_PK_CONST constant number := -13 ;
63
64 ALL_OFFADJ_CONST constant varchar(30) := 'All Off Invoice Adjustments';
65 ALL_OFFADJ_PK_CONST constant number := -14 ;
66 ALL_COST_CONST constant varchar(20) := 'All Costs';
67 ALL_COST_PK_CONST constant number := -15 ;
68
69 ALL_PSG_CONST constant varchar(30) := 'All Pricing Segments';
70 ALL_PSG_PK_CONST constant number := -17 ;
71
72 ALL_YEAR_CONST constant varchar2(30) := 'All Years';
73 ALL_YEAR_PK_CONST constant number := -18;
74
75 OAD_OM_GROUP_CONST constant varchar2(30) := 'SERVICES';
76 OAD_AR_GROUP_CONST constant varchar2(30) := 'PROMOTIONS';
77 OAD_OM_TYPE_CONST constant varchar2(30) := 'SHIPPING';
78 OAD_AR_CM_TYPE_CONST constant varchar2(30) := 'REBATE';
79 OAD_AR_CD_TYPE_CONST constant varchar2(30) := 'PAYMENT';
80
81 C_YES constant number := 1;
82 C_NO constant number := 2;
83
84
85 /* BOM ITEM TYPE */
86 C_MODEL constant number := 1;
87 C_OPTION_CLASS constant number := 2;
88 C_PLANNING constant number := 3;
89 C_STANDARD constant number := 4;
90 C_PRODUCT_FAMILY constant number := 5;
91
92
93 -- Public Functions
94
95 function get_null_pk return number IS
96 BEGIN
97 return NULL_VALUE_PK_CONST;
98 END;
99
100 function get_null_desc return VARCHAR2 IS
101 BEGIN
102 return get_dimension_desc('QPR_DIM_ALL_DESC','OTH');
103 END;
104
105 function get_all_scs_pk return number IS
106 BEGIN
107 return ALL_CHANNELS_PK_CONST ;
108 END;
109
110 function get_all_scs_desc return VARCHAR2 IS
111 BEGIN
112 return get_dimension_desc('QPR_DIM_ALL_DESC','CHN');
113 END;
114
115 function get_all_cus_pk return number IS
116 BEGIN
117 return ALL_CUSTOMER_PK_CONST ;
118 END;
119
120 function get_all_cus_desc return VARCHAR2 IS
121 BEGIN
122 return get_dimension_desc('QPR_DIM_ALL_DESC','CUS');
123 END;
124
125 function get_all_geo_pk return number IS
126 BEGIN
127 return ALL_GEOGRAPHY_PK_CONST ;
128 END;
129
130 function get_all_geo_desc return VARCHAR2 IS
131 BEGIN
132 return get_dimension_desc('QPR_DIM_ALL_DESC','GEO');
133 END;
134
135 function get_all_org_pk return number IS
136 BEGIN
137 return ALL_ORGANIZATIONS_PK_CONST ;
138 END;
139
140 function get_all_org_desc return VARCHAR2 IS
141 BEGIN
142 return get_dimension_desc('QPR_DIM_ALL_DESC','ORG');
143 END;
144
145 function get_all_prd_pk return number IS
146 BEGIN
147 return ALL_PRODUCTS_PK_CONST ;
148 END;
149
150 function get_all_prd_desc return VARCHAR2 IS
151 BEGIN
152 return get_dimension_desc('QPR_DIM_ALL_DESC','PRD');
153 END;
154
155 function get_all_rep_pk return number IS
156 BEGIN
157 return ALL_SALESREP_PK_CONST ;
158 END;
159
160 function get_all_rep_desc return VARCHAR2 IS
161 BEGIN
162 return get_dimension_desc('QPR_DIM_ALL_DESC','REP');
163 END;
164
165 function get_all_ord_pk return number IS
166 BEGIN
167 return ALL_ORDER_PK_CONST ;
168 END;
169
170 function get_all_ord_desc return VARCHAR2 IS
171 BEGIN
172 return get_dimension_desc('QPR_DIM_ALL_DESC','ORD');
173 END;
174
175 function get_all_adj_pk return number IS
176 BEGIN
177 return ALL_ADJUSTMENT_PK_CONST ;
178 END;
179
180 function get_all_adj_desc return VARCHAR2 IS
181 BEGIN
182 return get_dimension_desc('QPR_DIM_ALL_DESC','ADJ');
183 END;
184
185 function get_all_dsb_pk return number IS
186 BEGIN
187 return ALL_DSC_BAND_PK_CONST ;
188 END;
189
190 function get_all_vlb_pk return number IS
191 BEGIN
192 return ALL_VOL_BAND_PK_CONST ;
193 END;
194
195 function get_all_oad_pk return number IS
196 BEGIN
197 return ALL_OFFADJ_PK_CONST ;
198 END;
199
200 function get_all_cos_pk return number IS
201 BEGIN
202 return ALL_COST_PK_CONST ;
203 END;
204
205 function get_all_mgb_pk return number IS
206 BEGIN
207 return ALL_MRG_BAND_PK_CONST ;
208 END;
209
210 function get_all_psg_pk return number IS
211 BEGIN
212 return ALL_PSG_PK_CONST ;
213 END;
214
215 function get_all_year_pk return number IS
216 BEGIN
217 return ALL_YEAR_PK_CONST ;
218 END;
219
220 function get_all_dsb_desc return VARCHAR2 IS
221 BEGIN
222 return get_dimension_desc('QPR_DIM_ALL_DESC','DSB');
223 END;
224
225 function get_all_vlb_desc return VARCHAR2 IS
226 BEGIN
227 return get_dimension_desc('QPR_DIM_ALL_DESC','VLB');
228 END;
229
230 function get_all_mgb_desc return VARCHAR2 IS
231 BEGIN
232 return get_dimension_desc('QPR_DIM_ALL_DESC','MGB');
233 END;
234
235 function get_all_oad_desc return VARCHAR2 IS
236 BEGIN
237 return get_dimension_desc('QPR_DIM_ALL_DESC','OAD');
238 END;
239
240 function get_all_cos_desc return VARCHAR2 IS
241 BEGIN
242 return get_dimension_desc('QPR_DIM_ALL_DESC','COS');
243 END;
244
245 function get_all_psg_desc return VARCHAR2 IS
246 BEGIN
247 return get_dimension_desc('QPR_DIM_ALL_DESC','PSG');
248 END;
249
250 function get_all_year_desc return VARCHAR2 IS
251 BEGIN
252 return get_dimension_desc('QPR_DIM_ALL_DESC','TIM');
253 END;
254
255 function get_cost_type_desc return varchar2 IS
256 msg varchar2(2000);
257 BEGIN
258 FND_MESSAGE.SET_NAME('QPR', 'DEFAULT_COST_TYPE');
259 msg := FND_MESSAGE.GET;
260 return(msg);
261 END;
262
263 function get_oad_om_group_pk return varchar2 is
264 begin
265 return OAD_OM_GROUP_CONST;
266 end;
267
268 function get_oad_om_group_desc return varchar2 is
269 begin
270 return get_oad_group_desc(OAD_OM_GROUP_CONST);
271 end;
272
273 function get_oad_ar_group_pk return varchar2 is
274 begin
275 return OAD_AR_GROUP_CONST;
276 end;
277
278 function get_oad_ar_group_desc return varchar2 is
279 begin
280 return get_oad_group_desc(OAD_AR_GROUP_CONST);
281 end;
282
283 function get_oad_om_type_pk return varchar2 is
284 begin
285 return OAD_OM_TYPE_CONST;
286 end;
287
288 function get_oad_om_type_desc return varchar2 is
289 begin
290 return get_oad_type_desc(OAD_OM_TYPE_CONST);
291 end;
292
293 function get_oad_ar_cm_type_pk return varchar2 is
294 begin
295 return OAD_AR_CM_TYPE_CONST;
296 end;
297
298 function get_oad_ar_cm_type_desc return varchar2 is
299 begin
300 return get_oad_type_desc(OAD_AR_CM_TYPE_CONST);
301 end;
302
303 function get_oad_ar_cd_type_pk return varchar2 is
304 begin
305 return OAD_AR_CD_TYPE_CONST;
306 end;
307
308 function get_oad_ar_cd_type_desc return varchar2 is
309 begin
310 return get_oad_type_desc(OAD_AR_CD_TYPE_CONST);
311 end;
312
313 function get_oad_group_desc (p_code varchar2) return varchar2 is
314 l_group_desc varchar2(240) := null;
315 begin
316 select meaning into l_group_desc
317 from qpr_lookups
318 where lookup_type = 'QPR_OAD_GROUPS'
319 and lookup_code = p_code;
320
321 return l_group_desc;
322 end get_oad_group_desc;
323
324 function get_oad_type_desc (p_code varchar2) return varchar2 is
325 l_type_desc varchar2(240) := null;
326 begin
327 select meaning into l_type_desc
328 from qpr_lookups
329 where lookup_type = 'QPR_TERM_TYPE'
330 and lookup_code = p_code;
331
332 return l_type_desc;
333 end get_oad_type_desc;
334
335
336 FUNCTION get_dimension_desc(p_type varchar2,
337 p_code varchar2) return VARCHAR2 IS
338
339
340 CURSOR c_dim_desc(l_type varchar2, l_code varchar2) IS
341 select meaning from fnd_lookup_values
342 where lookup_type = l_type
343 and language = userenv('LANG')
344 and lookup_code = l_code;
345
349
346 l_dim_desc varchar2(240) := NULL;
347
348 BEGIN
350 OPEN c_dim_desc(p_type, p_code);
351 FETCH c_dim_desc INTO l_dim_desc;
352 CLOSE c_dim_desc;
353
354 IF l_dim_desc is NULL THEN
355 RETURN p_code;
356 ELSE
357 RETURN l_dim_desc;
358 END IF;
359
360 END get_dimension_desc;
361
362
363 function set_customer_attr( p_profile_name IN VARCHAR2,
364 p_profile_value IN VARCHAR2,
365 p_profile_Level IN VARCHAR2) return number is
366 x_ret_value boolean;
367 begin
368 x_ret_value := fnd_profile.save(p_profile_name,p_profile_value,p_profile_Level);
369 if x_ret_value then
370 return 0;
371 else
372 return 2;
373 end if;
374 end;
375
376 FUNCTION get_customer_id( p_party_id IN NUMBER) return NUMBER is
377
378 x_customer_id number ;
379
380
381 cursor c_customer_id is
382 select decode( decode(nvl(lower(fnd_profile.value('QPR_CUSTOMER_ATTRIBUTE')), '1'), '1', '1',
383 'attribute1', cust.attribute1,
384 'attribute2', cust.attribute2,
385 'attribute3', cust.attribute3,
386 'attribute4', cust.attribute4,
387 'attribute5', cust.attribute5,
388 'attribute6', cust.attribute6,
389 'attribute7', cust.attribute7,
390 'attribute8', cust.attribute8,
391 'attribute9', cust.attribute9,
392 'attribute10', cust.attribute10,
393 'attribute11', cust.attribute11,
394 'attribute12', cust.attribute12,
395 'attribute13',cust.attribute13,
396 'attribute14', cust.attribute14,
397 'attribute15', cust.attribute15, '2')
398 , '1', nvl(cust.cust_account_id,
399 qpr_sr_util.get_null_pk),
400 qpr_sr_util.get_null_pk)
401 from
402 hz_cust_accounts cust
403 where cust.party_id = p_party_id
404 order by cust.cust_account_id ASC;
405
406
407 BEGIN
408
409 open c_customer_id ;
410 fetch c_customer_id into x_customer_id ;
411 close c_customer_id ;
412
413 if( x_customer_id is not null) then
414 return x_customer_id ;
415 else
416 return NULL_VALUE_PK_CONST ;
417 end if ;
418
419 EXCEPTION
420 when others then return NULL_VALUE_PK_CONST ;
421 end get_customer_id ;
422
423 function get_internal_customers_desc return VARCHAR2 is
424 BEGIN
425 return get_dimension_desc('QPR_DIM_ALL_DESC','ICUS');
426 END;
427
428 function read_parameter(p_para_name in varchar2) return varchar2 as
429 begin
430 return(fnd_profile.value(p_para_name));
431 end read_parameter;
432
433 function get_base_uom(p_item_id in number,
434 p_instance_id in number default null) return varchar2 as
435 s_master_uom VARCHAR2(3);
436 l_master_org number;
437 l_sql varchar2(1000);
438 s_tbl_name varchar2(200);
439 begin
440
441 if g_base_item = p_item_id and
442 nvl(g_uom_instance, 0) = nvl(p_instance_id, 0) then
443 return(g_ref_base_uom);
444 end if;
445
446 if g_master_org is null then
447 l_master_org := to_number(nvl(read_parameter('QPR_MASTER_ORG'), '0'));
448 g_master_org := l_master_org;
449 else
450 l_master_org := g_master_org;
451 end if;
452
453 if p_instance_id is null then
454 SELECT nvl(primary_uom_code, 'Ea')
455 INTO s_master_uom
456 FROM mtl_system_items
457 WHERE inventory_item_id = p_item_id
458 AND organization_id = l_master_org;
459 else
460 s_tbl_name := 'mtl_system_items' || get_dblink(p_instance_id);
461 l_sql := 'SELECT nvl(primary_uom_code, ''Ea'') from ' || s_tbl_name;
462 l_sql := l_sql || ' WHERE inventory_item_id = ' || p_item_id;
463 l_sql := l_sql || ' AND organization_id = ' || l_master_org;
464 execute immediate l_sql into s_master_uom;
465 end if;
466
467 g_base_item := p_item_id;
468 g_uom_instance := p_instance_id;
469 g_ref_base_uom := s_master_uom;
470
471 return(s_master_uom);
472 exception
473 when OTHERS then
474 return null;
475 end get_base_uom;
476
477
478 FUNCTION uom_conv(p_uom_code in VARCHAR2, p_item_id in NUMBER,
479 p_master_uom varchar2
480 ) RETURN NUMBER AS
481 l_conv_rate NUMBER := 1;
482 s_master_uom VARCHAR2(3);
483 BEGIN
484 --** Conversion between between two UOMS.
485 --**
486 --** 1. The conversion always starts from the conversion defined, if exists,
487 --** for an specified item.
488 --** 2. If the conversion id not defined for that specific item, then the
489 --** standard conversion, which is defined for all items, is used.
490
491 IF g_ref_uom = p_uom_code AND g_ref_item_id = p_item_id
492 AND nvl(g_master_uom,'*') = nvl(p_master_uom,'*')
493 THEN
494 RETURN(g_ref_uom_conv_rate);
495 END IF;
496
497 if g_load_uom_no_conv is null then
498 g_load_uom_no_conv := nvl(read_parameter('QPR_LOAD_NO_UOM_CON'), 'N');
499 end if;
500
501 if p_master_uom is null then
502 s_master_uom := get_base_uom(p_item_id);
503 else
504 s_master_uom:= p_master_uom;
505 end if;
506
507 l_conv_rate := inv_convert.inv_um_convert(p_item_id, NULL, NULL,
511 l_conv_rate := 1;
508 p_uom_code, s_master_uom, NULL, NULL);
509
510 IF(l_conv_rate = -99999 and g_load_uom_no_conv = 'Y') THEN
512 END IF;
513
514 g_ref_uom := p_uom_code;
515 g_ref_item_id := p_item_id;
516 g_master_uom := p_master_uom;
517 g_ref_uom_conv_rate := l_conv_rate;
518
519 RETURN l_conv_rate;
520
521 EXCEPTION
522 WHEN others THEN
523 RETURN 1;
524 END uom_conv;
525
526 function convert_global_amt(p_curr_code in varchar2,
527 p_date in date,
528 from_ind_flag in varchar2 default 'Y',
529 p_global_curr_code in varchar2 default null)
530 return number is
531 l_ret number;
532
533 begin
534 if g_ref_curr = p_curr_code and g_ref_curr_date = p_date
535 and nvl(g_ref_global_curr_code, '*') = nvl(p_global_curr_code,'*')
536 then
537 return(g_curr_conv_rate);
538 end if;
539
540 if g_load_curr_no_conv is null then
541 g_load_curr_no_conv := nvl(read_parameter('QPR_LOAD_NO_CURR_CON'), 'N');
542 end if;
543
544 if p_global_curr_code is not null then
545 g_global_currency_code := p_global_curr_code;
546 end if;
547
548 if g_global_currency_code is null then
549 g_global_currency_code := read_parameter('QPR_CURRENCY_CODE');
550 end if;
551
552 if g_global_rate_type is null then
553 g_global_rate_type := read_parameter('QPR_CONVERSION_TYPE');
554 end if;
555
556 if (p_curr_code = g_global_currency_code) then
557 l_ret := 1;
558 elsif p_curr_code is null or g_global_currency_code is null then
559 l_ret := 1;
560 else
561 if from_ind_flag = 'Y' then
562 l_ret := GL_CURRENCY_API.convert_amount_sql(p_curr_code,
563 g_global_currency_code,
564 p_date,
565 g_global_rate_type, 1);
566 else
567 l_ret := GL_CURRENCY_API.convert_amount_sql(g_global_currency_code,
568 p_curr_code,
569 p_date,
570 g_global_rate_type, 1);
571
572 end if;
573 end if;
574 if l_ret < 0 and g_load_curr_no_conv = 'Y' then
575 -- api returns -1(rate not found) and -2 (currency not found)
576 l_ret := 1;
577 end if;
578 g_ref_curr := p_curr_code;
579 g_ref_curr_date := p_date;
580 g_ref_global_curr_code := p_global_curr_code;
581 g_curr_conv_rate := l_ret;
582
583 return l_ret;
584
585 EXCEPTION
586 when OTHERS then
587 return NULL;
588 END convert_global_amt;
589
590 function get_customer_attribute return varchar2 is
591 s_cust_attr varchar2(30);
592 begin
593
594 if g_ref_cust_attr is null and g_b_cust_attr_read = 0 then
595 s_cust_attr := lower(read_parameter('QPR_CUSTOMER_ATTRIBUTE'));
596 g_ref_cust_attr := s_cust_attr;
597 g_b_cust_attr_read := 1;
598 else
599 s_cust_attr := g_ref_cust_attr;
600 end if;
601
602 return (s_cust_attr);
603 exception
604 when OTHERS then
605 return NULL;
606 end get_customer_attribute;
607
608 function get_dblink(p_instance_id in number) return varchar2 is
609 db_link varchar2(150) := '';
610 begin
611 select decode(DBLINK,null, '','@'||DBLINK) into db_link
612 from QPR_INSTANCES
613 where instance_id= p_instance_id;
614 return(db_link);
615 exception
616 when NO_DATA_FOUND then
617 fnd_file.put_line(fnd_file.log, 'ERROR READING INSTANCE DATA...');
618 fnd_file.put_line(fnd_file.log, DBMS_UTILITY.FORMAT_ERROR_BACKTRACE);
619 raise;
620 end get_dblink;
621
622 FUNCTION ods_uom_conv(p_item_id in NUMBER, p_from_uom_code in VARCHAR2,
623 p_to_uom_code in varchar2,
624 p_instance_id in number default null,
625 p_precision in number default null
626 ) RETURN NUMBER AS
627 l_conv_rate NUMBER := 1;
628
629 STD_INV_PRECISION number := 5;
630
631 BEGIN
632 --** Conversion between between two UOMS.
633 --**
634 --** 1. The conversion always starts from the conversion defined, if exists,
635 --** for an specified item.
636 --** 2. If the conversion id not defined for that specific item, then the
637 --** standard conversion, which is defined for all items, is used.
638
639 IF g_ods_from_uom = p_from_uom_code AND g_ods_ref_item_id = p_item_id
640 and g_ods_to_uom = p_to_uom_code and g_ods_uom_inst_id = p_instance_id
641 THEN
642 RETURN(g_ods_ref_uom_crate);
643 END IF;
644
645 if g_load_uom_no_conv is null then
646 g_load_uom_no_conv := nvl(read_parameter('QPR_LOAD_NO_UOM_CON'), 'N');
647 end if;
648
649 if g_uom_conv_in_ods is null then
650 g_uom_conv_in_ods := nvl(read_parameter('QPR_PULL_UOM_CONV_TO_ODS'), 'N');
651 end if;
652
653 if p_from_uom_code = p_to_uom_code then
654 l_conv_rate := 1;
655 elsif g_uom_conv_in_ods = 'Y' then
656 begin
657 select conv_rate into l_conv_rate
658 from (
659 select 1/(nvl(conversion_rate, 1) ) conv_rate
660 from qpr_uom_conversions
661 where from_uom_code = p_from_uom_code
662 and to_uom_code = p_to_uom_code
663 and instance_id = p_instance_id
664 and item_key in (to_char(p_item_id), '0')
665 order by item_key desc)
669 begin
666 where rownum < 2;
667 exception
668 when no_data_found then
670 select conv_rate into l_conv_rate
671 from(
672 select (nvl(conversion_rate, 1) ) conv_rate
673 from qpr_uom_conversions
674 where from_uom_code = p_to_uom_code
675 and to_uom_code = p_from_uom_code
676 and instance_id = p_instance_id
677 and item_key in (to_char(p_item_id), '0')
678 order by item_key desc)
679 where rownum < 2;
680 exception
681 when no_data_found then
682 l_conv_rate:= -99999;
683 end;
684 end;
685 l_conv_rate := round(l_conv_rate, nvl(p_precision, STD_INV_PRECISION));
686 else
687 l_conv_rate := inv_convert.inv_um_convert(p_item_id, p_precision, NULL,
688 p_from_uom_code, p_to_uom_code, NULL, NULL);
689 end if;
690
691 IF(l_conv_rate = -99999) and g_load_uom_no_conv='Y' THEN
692 l_conv_rate := 1;
693 END IF;
694
695 g_ods_from_uom := p_from_uom_code;
696 g_ods_to_uom := p_to_uom_code;
697 g_ods_ref_item_id := p_item_id;
698 g_ods_ref_uom_crate := l_conv_rate;
699 g_ods_uom_inst_id := p_instance_id;
700
701 return(l_conv_rate);
702
703 EXCEPTION
704 WHEN others THEN
705 RETURN -1;
706 END ods_uom_conv;
707
708
709 function ods_curr_conversion(p_from_curr_code in varchar2 default null,
710 p_to_curr_code in varchar2,
711 p_conv_type in varchar2 default null,
712 p_date in date,
713 p_instance_id in number)
714 return number is
715 l_ret number;
716 begin
717 if p_from_curr_code = p_to_curr_code then
718 return(1);
719 end if;
720 if g_from_currency_code = nvl(p_from_curr_code, g_from_currency_code) and
721 g_ods_curr_inst_id = p_instance_id and
722 g_to_currency_code = p_to_curr_code and
723 g_ods_rate_type = nvl(p_conv_type, g_ods_rate_type) and
724 g_ods_curr_date = p_date then
725 return(g_ods_curr_rate);
726 end if;
727
728 if g_load_curr_no_conv is null then
729 g_load_curr_no_conv := nvl(read_parameter('QPR_LOAD_NO_CURR_CON'), 'N');
730 end if;
731
732 if g_curr_conv_in_ods is null then
733 g_curr_conv_in_ods := nvl(read_parameter('QPR_PULL_CURR_CONV_TO_ODS'), 'N');
734 end if;
735
736 if p_from_curr_code is null then
737 if g_from_currency_code is null then
738 g_from_currency_code := read_parameter('QPR_CURRENCY_CODE');
739 end if;
740 else
741 g_from_currency_code := p_from_curr_code;
742 end if;
743
744 if p_conv_type is null then
745 if g_ods_rate_type is null then
746 g_ods_rate_type := read_parameter('QPR_CONVERSION_TYPE');
747 end if;
748 else
749 g_ods_rate_type := p_conv_type;
750 end if;
751
752 if g_from_currency_code is null or p_to_curr_code is null then
753 l_ret := -1;
754 elsif g_curr_conv_in_ods = 'Y' then
755 l_ret := qpr_convert_amount(p_instance_id,
756 g_from_currency_code,
757 p_to_curr_code,
758 p_date,
759 null, 1);
760 else
761 l_ret := GL_CURRENCY_API.convert_amount_sql(g_from_currency_code,
762 p_to_curr_code,
763 p_date,
764 g_ods_rate_type, 1);
765 end if;
766
767 if l_ret < 0 and g_load_curr_no_conv = 'Y' then
768 l_ret := 1;
769 end if;
770
771 g_ods_curr_inst_id := p_instance_id;
772 g_to_currency_code := p_to_curr_code;
773 g_ods_curr_date := p_date;
774 g_ods_curr_rate := l_ret;
775
776 return(l_ret);
777
778 exception
779 when OTHERS then
780 return(-1);
781 end ods_curr_conversion;
782
783
784 function qpr_convert_amount(p_instance_id in number,
785 p_from_currency in varchar2,
786 p_to_currency in varchar2,
787 p_conversion_date in date,
788 p_conversion_type in varchar2 default null,
789 p_amount in number) return number as
790 s_to_type varchar2(8);
791 s_from_type varchar2(8);
792 s_euro_code varchar2(20);
793 l_to_rate number;
794 l_from_rate number;
795 l_from_mau number;
796 l_to_mau number;
797 l_converted_amount number;
798 l_other_rate number;
799
800 INVALID_CURRENCY exception;
801
802 procedure get_info(
803 p_currency in varchar2,
804 p_eff_date in date,
805 p_conversion_rate in out nocopy number,
806 p_mau in out nocopy number,
807 p_currency_type in out nocopy varchar2 ) is
808 begin
809 -- Get currency information from FND_CURRENCIES table
810 select decode( derive_type,
811 'EURO', 'EURO',
812 'EMU', decode( sign(trunc(p_eff_date) - trunc(derive_effective)),
813 -1, 'OTHER','EMU'),
814 'OTHER'),
815 decode( derive_type,
816 'EURO', 1,
817 'EMU', derive_factor,
818 'OTHER', -1 ),
819 nvl( minimum_accountable_unit, power( 10, (-1 * precision)))
823 exception
820 into p_currency_type, p_conversion_rate, p_mau
821 from FND_CURRENCIES
822 where currency_code = p_currency;
824 when NO_DATA_FOUND then
825 raise INVALID_CURRENCY;
826 end get_info;
827
828 function get_euro_code return varchar2 is
829 s_euro_code varchar2(20);
830 begin
831 -- Get currency code of the EURO currency
832 select currency_code into s_euro_code
833 from FND_CURRENCIES
834 where derive_type = 'EURO';
835
836 return( s_euro_code );
837 exception
838 when NO_DATA_FOUND then
839 raise INVALID_CURRENCY;
840 end get_euro_code;
841
842 function get_fixed_conv_rate(p_instance_id in number,
843 p_from_currency in out nocopy varchar2,
844 p_to_currency in out nocopy varchar2,
845 p_conversion_date date) return number is
846 l_fixed_conv_rate number;
847 l_direct_from_fix_rate number := 1;
848 l_inverse_to_fix_rate number := 1;
849
850 cursor c_is_there_fixed_rate is
851 select from_currency,
852 to_currency,
853 conversion_rate
854 from qpr_currency_rates
855 where from_currency in (p_from_currency, p_to_currency)
856 and conversion_date <= p_conversion_date
857 and conversion_class = 'FIXED'
858 and instance_id = p_instance_id;
859 begin
860 if (p_from_currency = p_to_currency) then
861 l_fixed_conv_rate := 1;
862 end if;
863
864 /* ********************************************************************+
865 | This routine should check whether there is a fixed rate relationship|
866 | exist between the from currency and the to_currency in the |
867 | GL_FIXED_CONV_RATES table. |
868 | Some EUROPEAN countries are getting rid of ending zero's |
869 | from their currency. In this case those countries define |
870 | a fixed rate relationship between the old currency and the new |
871 | replacement currency starting from an effective date. |
872 | A few possible different scenarios |
873 | The rate may be calculated between two currencies as follows |
874 | |
875 | 1) Old Currency to French Franks |
876 | Old Currency -> New Currency from the GL_FIXED_CONV_RATES table |
877 | New Currency -> EURO from the GL_DAILY_RATES |
878 | EURO -> FRENCH FRANK fixed rate from the FND_CURRENCIES |
879 | |
880 | 2) USD to New Currency |
881 | USD -> New CURRENCY from the GL Daily Rates Table. |
882 | |
883 | 3) USD to Old Currency |
884 | USD -> New CURRENCY from the GL Daily Rates Table. |
885 | New curency -> Old Currency fixed rate |
886 | from GL Fixed Conv Rates |
887 | |
888 | 4) Old Currency to CAD |
889 | Old Currency -> New Currency from |
890 | the GL_FIXED_CONV_RATES table |
891 | New Currency -> CAD from the GL_DAILY_RATES |
892 | |
893 |***********************************************************************/
894
895 -- max. of 2 records will be fetched in this cursor if both the from and
896 -- to currencies have fixed rates defined.
897 for r_fix_rate in c_is_there_fixed_rate loop
898 if (p_from_currency = r_fix_rate.from_currency) then
899 l_direct_from_fix_rate:= r_fix_rate.conversion_rate;
900 p_from_currency := r_fix_rate.to_currency;
901 end if;
902
903 if (p_to_currency = r_fix_rate.from_currency) then
904 l_inverse_to_fix_rate := 1/r_fix_rate.conversion_rate;
905 p_to_currency := r_fix_rate.to_currency;
906 end if;
907 end loop;
908
909 l_fixed_conv_rate := l_direct_from_fix_rate* l_inverse_to_fix_rate;
910
911 return(l_fixed_conv_rate);
912 exception
913 when OTHERS then
914 return(-1);
915 end get_fixed_conv_rate;
916
917 function get_other_rate (p_instance_id in number,
918 p_from_currency in varchar2,
919 p_to_currency in varchar2,
920 p_conversion_date in date,
921 p_conversion_type in varchar2 default null)
922 return number is
923 s_from_currency VARCHAR2(15);
924 s_to_currency VARCHAR2(15);
925 l_fix_rate NUMBER;
926 l_rate NUMBER;
927 begin
928 s_from_currency := p_from_currency;
929 s_to_currency := p_to_currency;
930 -- NOTE:UNCOMMENT THE FOLLOWING CODE WHEN FIXED CONV. RATE IS SUPPORTED
931 -- Get the Fixed conversion rate if there exists one.
932 -- the from and to currencies can be replaced with new currencies in this call
933 /* l_fix_rate := get_fixed_conv_rate(p_instance_id,
934 s_from_currency,
935 s_to_currency,
936 p_conversion_date);
937 */
938 l_fix_rate := 1;
939 if (s_from_currency = s_to_currency) then
940 l_rate := 1;
941 else
942 -- Get conversion rate between the two currencies from GL_DAILY_RATES
943 select conversion_rate into l_rate
947 and from_currency = s_from_currency
944 from qpr_currency_rates
945 where instance_id = p_instance_id
946 and conversion_class = 'DAILY'
948 and to_currency = s_to_currency
949 and conversion_date = p_conversion_date
950 and rownum < 2;
951 end if;
952
953 return(l_fix_rate * l_rate );
954 exception
955 when NO_DATA_FOUND then
956 return(-1);
957 end get_other_rate;
958
959 begin
960 if p_from_currency = p_to_currency then
961 return(p_amount);
962 end if;
963
964 -- Get currency information from the from_currency
965 get_info ( p_from_currency, p_conversion_date, l_from_rate, l_from_mau,
966 s_from_type );
967
968 -- Get currency information from the to_currency
969 get_info ( p_to_currency, p_conversion_date, l_to_rate, l_to_mau, s_to_type );
970
971 if (s_from_type = 'EMU') then
972 if (s_to_type = 'EMU') then
973 l_converted_amount := ( p_amount / l_from_rate ) * l_to_rate;
974
975 elsif (s_to_type = 'EURO') then
976 l_converted_amount := p_amount / l_from_rate;
977
978 elsif (s_to_type = 'OTHER') then
979 -- Find out conversion rate from EURO to x_to_currency
980 -- Get conversion amt by converting EMU -> EURO -> OTHER
981 s_euro_code := get_euro_code;
982 l_other_rate := get_other_rate( p_instance_id, s_euro_code, p_to_currency,
983 p_conversion_date,
984 p_conversion_type);
985 l_converted_amount := ( p_amount / l_from_rate ) * l_other_rate;
986 end if;
987 elsif (s_from_type = 'EURO') then
988 -- if to currency is also EURO the amount is returned
989 if (s_to_type = 'EMU') then
990 l_converted_amount := p_amount * l_to_rate;
991 elsif (s_to_type = 'OTHER' ) then
992 l_other_rate := get_other_rate( p_instance_id,
993 p_from_currency, p_to_currency,
994 p_conversion_date,
995 p_conversion_type );
996 l_converted_amount := p_amount * l_other_rate;
997 end if;
998
999 elsif (s_from_type = 'OTHER' ) then
1000 if (s_to_type = 'EMU' ) then
1001 -- Get conversion amt by converting OTHER -> EURO -> EMU
1002 s_euro_code := get_euro_code;
1003 l_other_rate := get_other_rate( p_instance_id, p_from_currency,
1004 s_euro_code,
1005 p_conversion_date,
1006 p_conversion_type );
1007 l_converted_amount := ( p_amount * l_other_rate ) * l_to_rate;
1008 elsif ( s_to_type = 'EURO' ) then
1009 l_other_rate := get_other_rate( p_instance_id, p_from_currency,
1010 p_to_currency,
1011 p_conversion_date,
1012 p_conversion_type );
1013 l_converted_amount := p_amount * l_other_rate;
1014 elsif ( s_to_type = 'OTHER' ) then
1015 l_other_rate := get_other_rate( p_instance_id, p_from_currency,
1016 p_to_currency,
1017 p_conversion_date,
1018 p_conversion_type );
1019 l_converted_amount := p_amount * l_other_rate;
1020 end if;
1021 end if;
1022
1023 -- Rounding to the correct precision and minumum accountable units
1024 return( round( l_converted_amount / l_to_mau ) * l_to_mau );
1025 exception
1026 when OTHERS then
1027 return(-1);
1028 end qpr_convert_amount;
1029
1030 function dm_parameters_ok return boolean
1031 is
1032 l_count number;
1033 begin
1034 if (fnd_profile.value('QPR_CURRENCY_CODE') is null or
1035 fnd_profile.value('QPR_CATEGORY_SET_NAME') is null or
1036 fnd_profile.value('QPR_MASTER_ORG') is null) then
1037 return false;
1038 else
1039 return true;
1040 end if;
1041 exception
1042 when others then return(false);
1043 end;
1044
1045 function get_max_date(p_date1 in date, p_date2 in date) return date is
1046 s_max_date date;
1047 begin
1048 if p_date1 is null then
1049 return(p_date2);
1050 elsif p_date2 is null then
1051 return(p_date1);
1052 else
1053 if sign(p_date1-p_date2) = -1 then
1054 return(p_date2);
1055 else
1056 return(p_date1);
1057 end if;
1058 end if;
1059 exception
1060 when others then return(null);
1061 end;
1062
1063 procedure purge_base_tables_data (p_price_plan_id in number)
1064 is
1065 begin
1066 delete from QPR_MEASURE_GROUPS where price_plan_id = p_price_plan_id;
1067 delete from QPR_LVL_ATTRIBUTES where price_plan_id = p_price_plan_id;
1068 delete from QPR_HIER_LEVELS where price_plan_id = p_price_plan_id;
1069 delete from QPR_HIERARCHIES where price_plan_id = p_price_plan_id;
1070 delete from QPR_DIM_ATTRIBUTES where price_plan_id = p_price_plan_id;
1071 delete from QPR_SET_LEVELS where price_plan_id = p_price_plan_id;
1072 delete from QPR_MEAS_AGGRS where price_plan_id = p_price_plan_id;
1073 delete from QPR_MEASURES where price_plan_id = p_price_plan_id;
1074 delete from QPR_CUBE_DIMS where price_plan_id = p_price_plan_id;
1075 delete from QPR_CUBES where price_plan_id = p_price_plan_id;
1076 delete from QPR_DIMENSIONS where price_plan_id = p_price_plan_id;
1077 delete from QPR_PRICE_PLANS_TL where price_plan_id = p_price_plan_id;
1078 delete from QPR_PRICE_PLANS_B where price_plan_id = p_price_plan_id;
1079 end;
1080
1081 END QPR_SR_UTIL;
[Home] [Help]