[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_PLAN_PRS_REASON_PKG
Source
1 PACKAGE BODY ISC_DBI_PLAN_PRS_REASON_PKG AS
2 /* $Header: ISCRGB5B.pls 120.0 2005/05/25 17:18:52 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_inner_sql VARCHAR2(32000);
12 l_org VARCHAR2(32000);
13 l_org_where VARCHAR2(32000);
14 l_period_type VARCHAR2(1000);
15 l_item VARCHAR2(32000);
16 l_item_from VARCHAR2(32000);
17 l_item_where VARCHAR2(32000);
18 l_sup VARCHAR2(32000);
19 l_sup_where VARCHAR2(32000);
20 l_res VARCHAR2(32000);
21 l_res_where VARCHAR2(32000);
22 l_res_org VARCHAR2(32000);
23 l_res_org_where VARCHAR2(32000);
24 l_curr VARCHAR2(10000);
25 l_curr_g VARCHAR2(15) := '''FII_GLOBAL1''';
26 l_curr_g1 VARCHAR2(15) := '''FII_GLOBAL2''';
27 l_curr_suffix VARCHAR2(15);
28 l_time_from DATE;
29 l_period_type_id NUMBER;
30 l_lang varchar2(10);
31 l_custom_rec BIS_QUERY_ATTRIBUTES ;
32
33
34
35 BEGIN
36
37 FOR i IN 1..p_param.COUNT
38 LOOP
39 IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT')
40 THEN l_plan := p_param(i).parameter_value;
41 END IF;
42
43 IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2')
44 THEN l_plan2 := p_param(i).parameter_value;
45 END IF;
46
47 IF(p_param(i).parameter_name = 'PERIOD_TYPE') THEN
48 l_period_type := p_param(i).parameter_value;
49 END IF;
50
51 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM') THEN
52 l_time_from := p_param(i).period_date;
53 END IF;
54
55 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM') THEN
56 l_time_from := p_param(i).period_date;
57 END IF;
58
59 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM') THEN
60 l_time_from := p_param(i).period_date;
61 END IF;
62
63 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION+ORGANIZAT_D1')
64 THEN l_org := p_param(i).parameter_value;
65 END IF;
66
67 IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
68 THEN l_curr := p_param(i).parameter_id;
69 END IF;
70
71 IF(p_param(i).parameter_name = 'SUPPLIER+POA_SUPPLIERS') THEN
72 l_sup := p_param(i).parameter_value;
73 END IF;
74
75 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG') THEN
76 l_item := p_param(i).parameter_value;
77 END IF;
78
79 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION+BIS_ORGANIZATION')
80 THEN l_res_org := p_param(i).parameter_value;
81 END IF;
82
83 IF(p_param(i).parameter_name = 'RESOURCE+ENI_RESOURCE') THEN
84 l_res := p_param(i).parameter_value;
85 END IF;
86 END LOOP;
87
88 IF (l_curr = l_curr_g)
89 THEN
90 l_curr_suffix := '_g';
91 ELSIF (l_curr = l_curr_g1)
92 THEN
93 l_curr_suffix := '_g1' ;
94 ELSE
95 l_curr_suffix := '';
96 END IF;
97
98 IF ( l_org IS NULL OR l_org = 'All' ) THEN
99 l_org_where := '
100 AND (EXISTS
101 (SELECT 1
102 FROM org_access o
103 WHERE o.responsibility_id = fnd_global.resp_id
104 AND o.resp_application_id = fnd_global.resp_appl_id
105 AND o.organization_id = f.organization_id)
106 OR EXISTS
107 (SELECT 1
108 FROM mtl_parameters org
109 WHERE org.organization_id = f.organization_id
110 AND NOT EXISTS
111 (SELECT 1
112 FROM org_access ora
113 WHERE org.organization_id = ora.organization_id)))';
114
115 ELSE
116 l_org_where := '
117 AND f.organization_id =(&ORGANIZATION+ORGANIZATION+ORGANIZAT_D1)';
118 END IF;
119
120
121 IF ( l_sup IS NULL OR l_sup = 'All' ) THEN
122 l_sup_where :='';
123 ELSE
124 l_sup_where := '
125 AND f.r_supplier_id in (&SUPPLIER+POA_SUPPLIERS)';
126 END IF;
127
128
129 IF ( l_item IS NULL OR l_item = 'All' )
130 THEN l_item_from := '';
131 l_item_where := '';
132 ELSE l_item_from := ',
133 ENI_OLTP_ITEM_STAR star';
134 l_item_where := '
135 AND star.inventory_item_id = f.r_item_id
136 AND star.organization_id = f.r_org_id
137 AND star.id in (&ITEM+ENI_ITEM_ORG)';
138 END IF;
139
140 IF ( l_res IS NULL OR l_res = 'All' )
141 THEN l_res_where := '';
142 ELSE l_res_where := '
143 AND f.r_resource_id in (&RESOURCE+ENI_RESOURCE)';
144 END IF;
145
146 IF ( l_res_org IS NULL OR l_res_org = 'All' ) THEN
147 l_res_org_where :='';
148 ELSE
149 l_res_org_where := '
150 AND f.r_org_id in (&ORGANIZATION+ORGANIZATION+BIS_ORGANIZATION)';
151 END IF;
152
153 l_lang := USERENV('LANG');
154
155
156 IF l_period_type = 'FII_TIME_ENT_YEAR' THEN
157 l_period_type_id := 128;
158
159 ELSIF (l_period_type='FII_TIME_ENT_QTR') THEN
160 l_period_type_id := 64;
161
162 ELSE
163 l_period_type_id := 32;
164
165 END IF;
166
167
168
169 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
170 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
171
172 IF (l_plan IS NULL OR l_plan2 IS NULL)
173 THEN l_stmt := '
174 SELECT 0 ISC_ATTRIBUTE_1,
175 0 ISC_ATTRIBUTE_2,
176 0 ISC_ATTRIBUTE_3,
177 0 ISC_ATTRIBUTE_4,
178 0 ISC_MEASURE_1,
179 0 ISC_MEASURE_2,
180 0 ISC_MEASURE_3,
181 0 ISC_MEASURE_4,
182 0 ISC_MEASURE_5,
183 0 ISC_MEASURE_6,
184 0 ISC_MEASURE_7,
185 0 ISC_MEASURE_8,
186 0 ISC_MEASURE_9,
187 0 ISC_MEASURE_10,
188 0 ISC_MEASURE_11
189 FROM dual
190 WHERE 1 = 2 /* PLAN_SNAPSHOT dimension has not been populated */';
191 ELSE
192 l_stmt := '
193 SELECT decode(a.type, ''ITEM'', items.value,
194 ''RESOURCE'', res.value,
195 ''TRANSPORT'', items.value,
196 ''UNASSIGNED'', null) ISC_ATTRIBUTE_1,
197 type.meaning ISC_ATTRIBUTE_2,
198 decode(a.type, ''ITEM'', sp.value,
199 ''RESOURCE'', org.name,
200 ''TRANSPORT'', org.name,
201 ''UNASSIGNED'', null) ISC_ATTRIBUTE_3,
202 decode(a.type, ''ITEM'', sps.value,
203 ''RESOURCE'', resd.value,
204 ''TRANSPORT'', null,
205 ''UNASSIGNED'', null) ISC_ATTRIBUTE_4,
206 ISC_MEASURE_1, ISC_MEASURE_2, ISC_MEASURE_3, ISC_MEASURE_4, ISC_MEASURE_5,
207 ISC_MEASURE_6, ISC_MEASURE_7, ISC_MEASURE_8, ISC_MEASURE_9, ISC_MEASURE_10,
208 ISC_MEASURE_11
209 FROM (select (rank() over (&ORDER_BY_CLAUSE nulls last, r_item_id, r_item_org_id, r_resource_id, r_supplier_id,
210 r_supplier_site_id, r_org_id, r_department_id)) - 1 rnk,
211 r_item_id,
212 r_item_org_id,
213 r_supplier_id,
214 r_supplier_site_id,
215 r_resource_id,
216 r_org_id,
217 r_department_id,
218 type,
219 ISC_MEASURE_1, ISC_MEASURE_2, ISC_MEASURE_3, ISC_MEASURE_4, ISC_MEASURE_5,
220 ISC_MEASURE_6, ISC_MEASURE_7, ISC_MEASURE_8, ISC_MEASURE_9, ISC_MEASURE_10,
221 ISC_MEASURE_11
222 FROM (SELECT c.r_item_id,
223 c.r_item_org_id,
224 c.r_supplier_id,
225 c.r_supplier_site_id,
226 c.r_resource_id,
227 c.r_org_id,
228 c.r_department_id,
229 c.type,
230 c.rev_sf ISC_MEASURE_1,
231 c.comp_rev_sf ISC_MEASURE_2,
232 c.rev_sf - c.comp_rev_sf ISC_MEASURE_3,
233 sum(c.rev_sf) over () ISC_MEASURE_4,
234 sum(c.rev_sf) over () - sum(c.comp_rev_sf) over () ISC_MEASURE_5,
235 c.rev_sf
236 / decode(sum(c.rev_sf) over (), 0, null,
237 sum(c.rev_sf) over ())
238 * 100 ISC_MEASURE_6,
239 sum(c.rev_sf) over ()
240 / decode(sum(c.rev_sf) over (), 0, null,
241 sum(c.rev_sf) over ())
242 * 100 ISC_MEASURE_7,
243 c.rev_sf - c.cost_sf ISC_MEASURE_8,
244 sum(c.rev_sf) over () - sum(c.cost_sf) over () ISC_MEASURE_9,
245 (c.rev_sf - c.cost_sf)
246 / decode(c.rev_sf, 0, null,
247 c.rev_sf)
248 * 100 ISC_MEASURE_10,
249 (sum(c.rev_sf) over () - sum(c.cost_sf) over ())
250 / decode(sum(rev_sf) over (), 0, null,
251 sum(rev_sf) over ())
252 * 100 ISC_MEASURE_11
253 FROM (select r_item_id,
254 r_item_org_id,
255 r_supplier_id,
256 r_supplier_site_id,
257 r_resource_id,
258 r_org_id,
259 r_department_id,
260 type,
261 sum(rev_sf) REV_SF,
262 sum(cost_sf) COST_SF,
263 sum(comp_rev_sf) COMP_REV_SF,
264 sum(comp_cost_sf) COMP_COST_SF
265 FROM (SELECT decode(f.reason_type, 1, f.r_item_id, 3, f.r_item_id, -1, null) r_item_id,
266 decode(f.reason_type, 1, f.r_org_id, 3, f.r_org_id, -1, null) r_item_org_id,
267 decode(f.reason_type, 1, f.r_supplier_id, 3, null, -1, null) r_supplier_id,
268 decode(f.reason_type, 1, f.r_supplier_site_id, 3, null, -1, null) r_supplier_site_id,
269 null r_resource_id,
270 null r_org_id,
271 null r_department_id,
272 decode(f.reason_type, 1, ''ITEM'', 3, ''TRANSPORT'', -1, ''UNASSIGNED'') type,
273 decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
274 f.rev_shortfall'||l_curr_suffix||', 0) rev_sf,
275 decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
276 f.cost_shortfall'||l_curr_suffix||', 0) cost_sf,
277 decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
278 f.rev_shortfall'||l_curr_suffix||', 0) comp_rev_sf,
279 decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
280 f.cost_shortfall'||l_curr_suffix||', 0) comp_cost_sf
281 FROM ISC_DBI_SHORTFALL_SNAPSHOTS f'||l_item_from||'
282 WHERE f.start_date = :ISC_CUR_START
283 AND f.period_type_id = :ISC_PERIOD_TYPE_ID
284 AND f.reason_type in (1,3,-1)
285 AND f.snapshot_id in (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)
286 '||l_org_where||l_item_where||l_sup_where||'
287 UNION ALL
288 SELECT null r_item_id,
289 null r_item_org_id,
290 null r_supplier_id,
291 null r_supplier_site_id,
292 f.r_resource_id r_resource_id,
293 f.r_org_id r_org_id,
294 f.r_department_id r_department_id,
295 ''RESOURCE'' type,
296 decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
297 f.rev_shortfall'||l_curr_suffix||', 0) rev_sf,
298 decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
299 f.cost_shortfall'||l_curr_suffix||', 0) cost_sf,
300 decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
301 f.rev_shortfall'||l_curr_suffix||', 0) comp_rev_sf,
302 decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
303 f.cost_shortfall'||l_curr_suffix||', 0) comp_cost_sf
304 FROM ISC_DBI_PM_0004_MV f
305 WHERE f.start_date = :ISC_CUR_START
306 AND f.period_type_id = :ISC_PERIOD_TYPE_ID
307 AND f.snapshot_id in (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)
308 '||l_org_where||l_res_where||l_res_org_where||')
309 GROUP BY
310 r_item_id,
311 r_item_org_id,
312 r_supplier_id,
313 r_supplier_site_id,
314 r_resource_id,
315 r_org_id,
316 r_department_id,
317 type) c)) a,
318 HR_ALL_ORGANIZATION_UNITS_TL org,
319 ENI_ITEM_ORG_V items,
320 POA_SUPPLIERS_V sp,
321 POA_SUPPLIER_SITES_V sps,
322 ENI_RESOURCE_V res,
323 ENI_RESOURCE_DEPARTMENT_V resd,
324 FND_LOOKUPS type
325 WHERE org.organization_id (+)= a.r_org_id
326 AND org.language (+)= :ISC_LANG
327 AND items.inventory_item_id(+) = a.r_item_id
328 AND items.organization_id(+) = a.r_item_org_id
329 AND sp.id (+)= a.r_supplier_id
330 AND sps.id (+)= a.r_supplier_site_id
331 AND res.id (+)= to_char(a.r_resource_id)
332 AND resd.id (+)= to_char(a.r_department_id)
333 AND type.lookup_type = ''ISC_DBI_PLAN_PRS_REASON_TYPE''
334 AND type.lookup_code = a.type
335 AND ((a.rnk between &START_INDEX and &END_INDEX) OR &END_INDEX=-1)
336 ORDER BY rnk';
337
338 END IF;
339
340 x_custom_sql := l_stmt;
341
342 l_custom_rec.attribute_name := ':ISC_PERIOD_TYPE_ID';
343 l_custom_rec.attribute_value := to_char(l_period_type_id);
344 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
345 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
346 x_custom_output.EXTEND;
347 x_custom_output(1) := l_custom_rec;
348
349 l_custom_rec.attribute_name := ':ISC_CUR_START';
350 l_custom_rec.attribute_value := to_char(l_time_from,'DD/MM/YYYY');
351 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
352 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
353 x_custom_output.EXTEND;
354 x_custom_output(2) := l_custom_rec;
355
356 l_custom_rec.attribute_name := ':ISC_LANG';
357 l_custom_rec.attribute_value := l_lang;
358 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
359 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.VARCHAR2_BIND;
360 x_custom_output.EXTEND;
361 x_custom_output(3) := l_custom_rec;
362
363
364 END get_sql;
365
366 END ISC_DBI_PLAN_PRS_REASON_PKG ;
367