[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_PLAN_PRS_TREND_PKG
Source
1 PACKAGE BODY ISC_DBI_PLAN_PRS_TREND_PKG AS
2 /* $Header: ISCRGB4B.pls 120.0 2005/05/25 17:39:55 appldev noship $ */
3
4
5 PROCEDURE GET_SQL(p_param IN BIS_PMV_PAGE_PARAMETER_TBL,x_custom_sql OUT NOCOPY VARCHAR2,
6 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
7
8 l_stmt VARCHAR2(32000);
9 l_plan VARCHAR2(10000);
10 l_plan2 VARCHAR2(10000);
11 l_org VARCHAR2(32000);
12 l_org_where VARCHAR2(32000);
13 l_period_type VARCHAR2(1000);
14 l_time_from DATE;
15 l_time_to DATE;
16 l_cur_start DATE;
17 l_cur_end DATE;
18 l_curr VARCHAR2(10000);
19 l_curr_g VARCHAR2(15) := '''FII_GLOBAL1''';
20 l_curr_g1 VARCHAR2(15) := '''FII_GLOBAL2''';
21 l_curr_suffix VARCHAR2(15);
22 l_period_type_id NUMBER;
23 l_period_id VARCHAR2(30);
24 l_loop NUMBER;
25 l_custom_rec BIS_QUERY_ATTRIBUTES ;
26
27
28
29 BEGIN
30
31 FOR i IN 1..p_param.COUNT
32 LOOP
33 IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT')
34 THEN l_plan := p_param(i).parameter_value;
35 END IF;
36
37 IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2')
38 THEN l_plan2 := p_param(i).parameter_value;
39 END IF;
40
41 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
42 THEN l_org := p_param(i).parameter_value;
43 END IF;
44
45 IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
46 THEN l_curr := p_param(i).parameter_id;
47 END IF;
48
49 IF(p_param(i).parameter_name = 'PERIOD_TYPE') THEN
50 l_period_type := p_param(i).parameter_value;
51 END IF;
52
53 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM') THEN
54 l_time_from := p_param(i).period_date;
55 END IF;
56
57 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_TO') THEN
58 l_time_to := p_param(i).period_date;
59 END IF;
60
61 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM') THEN
62 l_time_from := p_param(i).period_date;
63 END IF;
64
65 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_TO') THEN
66 l_time_to := p_param(i).period_date;
67 END IF;
68
69 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM') THEN
70 l_time_from := p_param(i).period_date;
71 END IF;
72
73 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_TO') THEN
74 l_time_to := p_param(i).period_date;
75 END IF;
76 END LOOP;
77
78
79 IF (l_curr = l_curr_g)
80 THEN
81 l_curr_suffix := '_g';
82 ELSIF (l_curr = l_curr_g1)
83 THEN
84 l_curr_suffix := '_g1';
85 ELSE
86 l_curr_suffix := '';
87 END IF;
88
89 IF ( l_org IS NULL OR l_org = 'All' ) THEN
90 l_org_where := '
91 AND (EXISTS
92 (SELECT 1
93 FROM org_access o
94 WHERE o.responsibility_id = fnd_global.resp_id
95 AND o.resp_application_id = fnd_global.resp_appl_id
96 AND o.organization_id = f.organization_id)
97 OR EXISTS
98 (SELECT 1
99 FROM mtl_parameters org
100 WHERE org.organization_id = f.organization_id
101 AND NOT EXISTS
102 (SELECT 1
103 FROM org_access ora
104 WHERE org.organization_id = ora.organization_id)))';
105
106 ELSE
107 l_org_where := '
108 AND f.organization_id =(&ORGANIZATION+ORGANIZATION)';
109 END IF;
110
111
112 IF l_period_type = 'FII_TIME_ENT_YEAR' THEN
113 l_loop :=3;
114 l_period_type_id := 128;
115 l_period_id :='ent_year_id';
116
117 ELSIF (l_period_type='FII_TIME_ENT_QTR') THEN
118 l_loop := 7;
119 l_period_type_id := 64;
120 l_period_id :='ent_qtr_id';
121
122 ELSE
123 l_loop :=11;
124 l_period_type_id := 32;
125 l_period_id :='ent_period_id';
126
127 END IF;
128
129
130 l_cur_start := l_time_from;
131 l_cur_end := l_time_from;
132
133 FOR i IN 1..l_loop
134 LOOP
135 l_cur_end := FII_TIME_API.next_period_end_date(l_cur_end, l_period_type);
136 END LOOP;
137
138
139 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
140 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
141
142 IF (l_plan IS NULL OR l_plan2 IS NULL)
143 THEN l_stmt := '
144 SELECT 0 VIEWBY,
145 0 ISC_ATTRIBUTE_1,
146 0 ISC_MEASURE_1,
147 0 ISC_MEASURE_2,
148 0 ISC_MEASURE_3,
149 0 ISC_MEASURE_4,
150 0 ISC_MEASURE_5,
151 0 ISC_MEASURE_6,
152 0 ISC_MEASURE_7,
153 0 ISC_MEASURE_8,
154 0 ISC_MEASURE_9,
155 0 ISC_MEASURE_10,
156 0 ISC_MEASURE_11,
157 0 ISC_MEASURE_12,
158 0 ISC_MEASURE_13
159 FROM dual
160 WHERE 1 = 2 /* PLAN_SNAPSHOT dimension has not been populated */';
161 ELSE
162 l_stmt:='
163 SELECT s.period_name VIEWBY,
164 s.period_id ISC_ATTRIBUTE_1,
165 sum(s.plan_rev) ISC_MEASURE_1,
166 sum(s.rev_sf) ISC_MEASURE_2,
167 sum(s.comp_rev_sf) ISC_MEASURE_3,
168 sum(s.rev_sf) - sum(s.comp_rev_sf) ISC_MEASURE_4,
169 sum(s.rev_sf)
170 / decode(sum(s.plan_rev), 0, null,
171 sum(s.plan_rev))
172 * 100 ISC_MEASURE_5,
173 sum(s.rev_sf)
174 / decode(sum(s.plan_rev), 0, null,
175 sum(s.plan_rev))
176 * 100
177 - sum(s.comp_rev_sf)
178 / decode(sum(s.comp_plan_rev), 0, null,
179 sum(s.comp_plan_rev))
180 * 100 ISC_MEASURE_6,
181 sum(s.rev_sf) ISC_MEASURE_7,
182 sum(s.plan_rev) - sum(s.rev_sf) ISC_MEASURE_8,
183 sum(s.rev_sf) - sum(s.cost_sf) ISC_MEASURE_9,
184 sum(s.comp_rev_sf) - sum(s.comp_cost_sf) ISC_MEASURE_10,
185 sum(s.rev_sf) - sum(s.cost_sf)
186 - (sum(s.comp_rev_sf) - sum(s.comp_cost_sf)) ISC_MEASURE_11,
187 (sum(s.rev_sf) - sum(s.cost_sf))
188 / decode(sum(s.rev_sf), 0, null,
189 sum(s.rev_sf))
190 * 100 ISC_MEASURE_12,
191 (sum(s.rev_sf) - sum(s.cost_sf))
192 / decode(sum(s.rev_sf), 0, null,
193 sum(s.rev_sf))
194 * 100 -
195 (sum(s.comp_rev_sf) - sum(s.comp_cost_sf))
196 / decode(sum(s.comp_rev_sf), 0, null,
197 sum(s.comp_rev_sf))
198 * 100 ISC_MEASURE_13
199 FROM (SELECT time.start_date START_DATE,
200 time.name PERIOD_NAME,
201 time.'||l_period_id||' PERIOD_ID,
202 decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
203 f.mds_price'||l_curr_suffix||', 0) PLAN_REV,
204 decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
205 f.mds_price'||l_curr_suffix||', 0) COMP_PLAN_REV,
206 decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
207 f.rev_shortfall'||l_curr_suffix||', 0) REV_SF,
208 decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
209 f.rev_shortfall'||l_curr_suffix||', 0) COMP_REV_SF,
210 decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
211 f.cost_shortfall'||l_curr_suffix||', 0) COST_SF,
212 decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
213 f.cost_shortfall'||l_curr_suffix||', 0) COMP_COST_SF
214 FROM (SELECT start_date,
215 name,
216 '||l_period_id||'
217 FROM '||l_period_type||'
218 WHERE start_date between :ISC_CUR_START and :ISC_CUR_END) time
219 LEFT OUTER JOIN
220 ISC_DBI_PM_0001_MV f
221 ON f.start_date = time.start_date
222 AND f.period_type_id = :ISC_PERIOD_TYPE_ID
223 AND f.union1_flag <> 0
224 AND f.item_cat_flag = 3
225 AND f.snapshot_id in (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)
226 '||l_org_where||') s
227 GROUP BY
228 s.period_name,
229 s.period_id,
230 s.start_date
231 ORDER BY
232 s.start_date';
233 END IF;
234
235 x_custom_sql := l_stmt;
236
237 l_custom_rec.attribute_name := ':ISC_PERIOD_TYPE_ID';
238 l_custom_rec.attribute_value := to_char(l_period_type_id);
239 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
240 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
241 x_custom_output.EXTEND;
242 x_custom_output(1) := l_custom_rec;
243
244 l_custom_rec.attribute_name := ':ISC_CUR_START';
245 l_custom_rec.attribute_value := to_char(l_cur_start,'DD/MM/YYYY');
246 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
247 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
248 x_custom_output.EXTEND;
249 x_custom_output(2) := l_custom_rec;
250
251 l_custom_rec.attribute_name := ':ISC_CUR_END';
252 l_custom_rec.attribute_value := to_char(l_cur_end,'DD/MM/YYYY');
253 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
254 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
255 x_custom_output.EXTEND;
256 x_custom_output(3) := l_custom_rec;
257
258 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
259 l_custom_rec.attribute_value := 'TIME+'||l_period_type;
260 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
261 x_custom_output.EXTEND;
262 x_custom_output(4) := l_custom_rec;
263
264
265 END get_sql;
266
267 END ISC_DBI_PLAN_PRS_TREND_PKG ;
268