[Home] [Help]
PACKAGE BODY: APPS.ASO_BI_QOT_UTIL_PVT
Source
1 PACKAGE BODY ASO_BI_QOT_UTIL_PVT AS
2 /* $Header: asovbiutlb.pls 120.1 2005/12/15 04:19:28 jmahendr noship $ */
3
4 -- This function returns the record_type_id using the period type from the
5 -- parameter portlet.
6
7 FUNCTION GET_RECORD_TYPE_ID(p_period_type IN VARCHAR2)
8 RETURN NUMBER IS
9 l_record_type_id NUMBER;
10 BEGIN
11
12 IF(p_period_type = 'FII_TIME_ENT_YEAR') THEN
13 l_record_type_id := 119;
14 ELSIF(p_period_type = 'FII_TIME_ENT_QTR') THEN
15 l_record_type_id := 55;
16 ELSIF(p_period_type = 'FII_TIME_ENT_PERIOD') THEN
17 l_record_type_id := 23;
18 ELSE
19 l_record_type_id := 11;
20 END IF;
21
22 RETURN l_record_type_id;
23
24 END GET_RECORD_TYPE_ID;
25
26 -- This function returns the rate of convertion from the primary to the
27 -- user functional currency.
28 FUNCTION GET_CUR_CONV_RATE(p_currency_code IN VARCHAR2,p_asof_date IN DATE) RETURN NUMBER IS
29
30 l_conv_rate NUMBER := NULL;
31 l_primary_curr VARCHAR2(3200);
32 l_jtf_curr_profile VARCHAR2(3200);
33 l_asof_date DATE;
34 BEGIN
35
36 IF p_currency_code IS NULL THEN
37 RETURN l_conv_rate;
38 END IF;
39
40 l_asof_date := p_asof_date;
41
42 IF ('EUR' = p_currency_code AND TO_DATE('01/01/1999','DD/MM/YYYY') > l_asof_date) THEN
43 l_asof_date := TO_DATE('01/01/1999','DD/MM/YYYY');
44 END IF;
45
46 l_jtf_curr_profile := FND_PROFILE.Value('JTF_PROFILE_DEFAULT_CURRENCY');
47
48 IF INSTR(p_currency_code,'FII_GLOBAL1') > 0 THEN
49 l_conv_rate := 1;
50 ELSIF INSTR(p_currency_code,l_jtf_curr_profile) > 0 THEN
51
52 l_primary_curr := BIS_COMMON_PARAMETERS.Get_currency_code;
53
54 IF (l_primary_curr = l_jtf_curr_profile) THEN
55 RETURN 1;
56 ELSE
57 -- Api requires : From curr, to curr, as of date, conversion rate type
58 l_conv_rate := GL_CURRENCY_API.get_rate_sql(
59 l_primary_curr,
60 l_jtf_curr_profile,
61 l_asof_date,
62 fnd_profile.value('AS_MC_DAILY_CONVERSION_TYPE'));
63 END IF;
64
65 END IF;
66
67 IF(l_conv_rate < 0) THEN
68 l_conv_rate := NULL;
69 END IF;
70
71 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
72 FND_LOG.STRING( LOG_LEVEL => FND_LOG.LEVEL_STATEMENT ,
73 MODULE => 'ASO_BI_QOT_UTIL_PVT.GET_CUR_CONV_RATE ',
74 MESSAGE =>' l_conv_rate '||l_conv_rate||' l_primary_currency '||l_primary_curr||
75 ' p_currency_code '||p_currency_code||' p_asof_date '||l_asof_date);
76 END IF;
77
78 RETURN l_conv_rate;
79
80 EXCEPTION
81 WHEN OTHERS THEN
82 RETURN l_conv_rate;
83
84 END GET_CUR_CONV_RATE;
85
86 -- Used for defaulting in the Parameter portlet
87 FUNCTION GET_DBI_PARAMS(p_region_id IN VARCHAR2)
88 RETURN VARCHAR2
89 IS
90
91 l_as_of_date VARCHAR2(150);
92 l_sg_id VARCHAR2(150);
93
94 BEGIN
95
96 BEGIN
97
98 l_sg_id := JTF_RS_DBI_CONC_PUB.GET_SG_ID();
99
100 EXCEPTION
101 WHEN OTHERS THEN
102 l_sg_id := '-1111';
103 END;
104
105 l_as_of_date := TO_CHAR(TRUNC(SYSDATE),'DD-MON-YYYY') ;
106
107 RETURN '&AS_OF_DATE='|| l_as_of_date ||
108 '&ASO_YEARLY=TIME_COMPARISON_TYPE+YEARLY&ASO_CURRENCY=FII_GLOBAL1'||
109 '&ASO_QTR_ID=TIME+FII_TIME_ENT_QTR'||
110 '&ASO_DIMENSION1=2'||
111 '&JTF_ORG_SALES_GROUP='|| l_sg_id;
112
113 END GET_DBI_PARAMS;
114
115 PROCEDURE PARSE_SALES_GROUP_ID(
116 p_sg_id IN VARCHAR2,
117 x_salesgroup_id OUT NOCOPY NUMBER,
118 x_resource_id OUT NOCOPY NUMBER
119 ) AS
120
121 l_sg_id VARCHAR2(20);
122 l_resource_id VARCHAR2(20);
123 l_location NUMBER;
124 BEGIN
125
126 IF(INSTR(p_sg_id, '.') > 0) then
127
128 l_location := INSTR(p_sg_id,'.');
129 x_salesgroup_id := TO_NUMBER(REPLACE(SUBSTR(p_sg_id, l_location + 1),''''));
130 l_resource_id := REPLACE(SUBSTR(p_sg_id,1, l_location - 1),'''');
131 x_resource_id := TO_NUMBER(REPLACE(l_resource_id,'''',''));
132
133 ELSE
134 x_salesgroup_id := TO_NUMBER(REPLACE(p_sg_id, ''''));
135 END IF;
136
137 END PARSE_SALES_GROUP_ID;
138
139 Procedure GET_PAGE_PARAMS(p_pmv_parameters IN BIS_PMV_PAGE_PARAMETER_TBL,
140 x_conv_rate OUT NOCOPY NUMBER,
141 x_record_type_id OUT NOCOPY NUMBER,
142 x_sysdate OUT NOCOPY DATE,
143 x_sg_id OUT NOCOPY NUMBER,
144 x_sr_id OUT NOCOPY NUMBER,
145 x_asof_date OUT NOCOPY DATE,
146 x_priorasof_date OUT NOCOPY DATE,
147 x_fdcp_date OUT NOCOPY DATE,
148 x_fdpp_date OUT NOCOPY DATE,
149 x_period_type OUT NOCOPY VARCHAR2,
150 x_comparision_type OUT NOCOPY VARCHAR2,
151 x_orderBy OUT NOCOPY VARCHAR2,
152 x_sortBy OUT NOCOPY VARCHAR2,
153 x_viewby OUT NOCOPY VARCHAR2,
154 x_prodcat_id OUT NOCOPY VARCHAR2,
155 x_product_id OUT NOCOPY VARCHAR2)
156
157 AS
158 l_parameter_name VARCHAR2(3200);
159 l_currency_type VARCHAR2(3200);
160 l_sg_id VARCHAR2(3200);
161 l_params VARCHAR2(3200);
162 l_order VARCHAR2(3200);
163 Begin
164
165 FOR i IN p_pmv_parameters.FIRST..p_pmv_parameters.LAST
166 LOOP
167 l_parameter_name := p_pmv_parameters(i).parameter_name ;
168 IF('BIS_CURRENT_ASOF_DATE' = l_parameter_name)
169 THEN
170 x_asof_date := p_pmv_parameters(i).PERIOD_DATE;
171 ELSIF( l_parameter_name = 'CURRENCY+FII_CURRENCIES')
172 THEN
173 l_currency_type := p_pmv_parameters(i).parameter_id;
174 ELSIF( l_parameter_name = 'PERIOD_TYPE')
175 THEN
176 x_period_type := p_pmv_parameters(i).parameter_value ;
177 ELSIF( l_parameter_name = 'TIME_COMPARISON_TYPE')
178 THEN
179 x_comparision_type := p_pmv_parameters(i).parameter_value;
180 ELSIF( l_parameter_name = 'ORGANIZATION+JTF_ORG_SALES_GROUP')
181 THEN
182 l_sg_id := p_pmv_parameters(i).parameter_id;
183 ELSIF( l_parameter_name = 'BIS_PREVIOUS_ASOF_DATE')
184 THEN
185 x_priorasof_date := p_pmv_parameters(i).PERIOD_DATE;
186 ELSIF ('BIS_CURRENT_EFFECTIVE_START_DATE' = l_parameter_name)
187 THEN
188 x_fdcp_date := p_pmv_parameters(i).PERIOD_DATE;
189 ELSIF ('BIS_PREVIOUS_EFFECTIVE_START_DATE' = l_parameter_name)
190 THEN
191 x_fdpp_date := p_pmv_parameters(i).PERIOD_DATE;
192 ELSIF ('ORDERBY' = l_parameter_name)
193 THEN
194 l_order := TRIM(p_pmv_parameters(i).parameter_value);
195 x_orderBy := TRIM(SUBSTR(l_order,0,INSTR(l_order,' ')));
196 x_sortBy := SUBSTR(l_order,INSTR(l_order,' '));
197 ELSIF ('VIEW_BY' = l_parameter_name)
198 THEN
199 x_viewby := p_pmv_parameters(i).parameter_value;
200 ELSIF ('ITEM+ENI_ITEM_VBH_CAT' = l_parameter_name)
201 THEN
202 x_prodcat_id := p_pmv_parameters(i).parameter_id;
203 ELSIF ('ITEM+ENI_ITEM' = l_parameter_name)
204 THEN
205 x_product_id := p_pmv_parameters(i).parameter_id;
206 END IF;
207
208 END LOOP;
209
210 -- Get the Conversion rate
211
212 -- commented for DBI7.0 Rup1 10-aug-2004
213 -- x_conv_rate := ASO_BI_QOT_UTIL_PVT.GET_CUR_CONV_RATE(
214 --- l_currency_type, x_asof_date);
215
216 -- 7.0 rup1 changes - secondary Currency uptake. --
217 IF INSTR(l_currency_type,'FII_GLOBAL1') > 0
218 THEN
219 /* For Primary Currency */
220 x_conv_rate := 1;
221 ELSIF INSTR(l_currency_type,'FII_GLOBAL2') > 0
222 THEN
223 /* For Secondary Currency */
224 x_conv_rate := 0;
225 END IF;
226
227 -- Get the Sales group
228 PARSE_SALES_GROUP_ID(
229 l_sg_id , x_salesgroup_id => x_sg_id, x_resource_id => x_sr_id);
230
231 --Get the record type
232 x_record_type_id := ASO_BI_QOT_UTIL_PVT.GET_RECORD_TYPE_ID(x_period_type);
233
234 --Get the Sysdate from BIS_SYSTEM_DATE table
235 x_sysdate := BIS_COMMON_PARAMETERS.GET_CURRENT_DATE_ID;
236
237 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
238
239 l_params := ' BIS_CURRENT_ASOF_DATE '|| x_asof_date ||
240 ' CURRENCY+FII_CURRENCIES '|| l_currency_type||
241 ' PERIOD_TYPE '|| x_period_type||
242 ' TIME_COMPARISON_TYPE '|| x_comparision_type||
243 ' ORGANIZATION+JTF_ORG_SALES_GROUP '|| x_sg_id||
244 ' BIS_PREVIOUS_ASOF_DATE '|| x_priorasof_date||
245 ' BIS_CURRENT_EFFECTIVE_START_DATE '|| x_fdcp_date||
246 ' Record Type Id '|| x_record_type_id||
247 ' BIS_PREVIOUS_EFFECTIVE_START_DATE '|| x_fdpp_date||
248 ' ORDERBY '|| x_orderBy||
249 ' Conv Rate '|| x_conv_rate||
250 ' p_sg_id_num '|| x_sg_id||
251 ' p_sr_id_num '|| x_sr_id ||
252 ' p_sortBy ' || x_sortBy;
253
254 FND_LOG.STRING( LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
255 MODULE => 'ASO_BI_QOT_UTIL_PVT.GET_PAGE_PARAMS',
256 MESSAGE => l_params);
257 END IF;
258
259 END GET_PAGE_PARAMS;
260
261 -- Splits the long queries into different Logging statement
262 PROCEDURE write_query (p_query IN VARCHAR2, p_module IN VARCHAR2)
263 AS
264 ind NUMBER := 1;
265 l_length NUMBER := 0;
266 BEGIN
267
268 l_length := length(p_query);
269
270 WHILE ind < l_length LOOP
271 IF(FND_LOG.LEVEL_STATEMENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
272 FND_LOG.STRING( LOG_LEVEL => FND_LOG.LEVEL_STATEMENT,
273 MODULE => 'ASO_BI_QOT_UTIL_PVT.'||p_module,
274 MESSAGE => substr(p_query, ind+1, 3000));
275 END IF;
276
277 ind := ind + 3000;
278 END LOOP;
279
280 EXCEPTION
281 WHEN OTHERS THEN
282 NULL;
283
284 END write_query;
285
286 END ASO_BI_QOT_UTIL_PVT;