[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_PLAN_CB_SUM_TREND_PKG
Source
1 PACKAGE BODY ISC_DBI_PLAN_CB_SUM_TREND_PKG AS
2 /* $Header: ISCRGANB.pls 120.0 2005/05/25 17:22:51 appldev noship $ */
3
4
5 PROCEDURE Get_Sql ( p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
6 x_custom_sql OUT NOCOPY VARCHAR2,
7 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
8
9 l_stmt VARCHAR2(10000);
10 l_plan VARCHAR2(10000);
11 l_plan2 VARCHAR2(10000);
12 l_org VARCHAR2(10000);
13 l_org_where VARCHAR2(10000);
14 l_item VARCHAR2(10000);
15 l_item_where VARCHAR2(10000);
16 l_inv_cat VARCHAR2(10000);
17 l_inv_cat_where VARCHAR2(10000);
18 l_period_type VARCHAR2(10000);
19 l_period_type_id NUMBER;
20 l_time_from DATE;
21 l_rpt_end_date DATE;
22 l_loop NUMBER;
23 l_item_cat_flag NUMBER;
24 l_custom_rec BIS_QUERY_ATTRIBUTES;
25 l_curr VARCHAR2(10000);
26 l_curr_g VARCHAR2(15) := '''FII_GLOBAL1''';
27 l_curr_g1 VARCHAR2(15) := '''FII_GLOBAL2''';
28 l_col1 VARCHAR2(100);
29 l_col2 VARCHAR2(100);
30 l_col3 VARCHAR2(100);
31
32
33 BEGIN
34
35 FOR i IN 1..p_param.COUNT
36 LOOP
37 IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT')
38 THEN l_plan := p_param(i).parameter_value;
39 END IF;
40
41 IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2')
42 THEN l_plan2 := p_param(i).parameter_value;
43 END IF;
44
45 IF(p_param(i).parameter_name = 'PERIOD_TYPE')
46 THEN l_period_type := p_param(i).parameter_value;
47 END IF;
48
49 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM')
50 THEN l_time_from := p_param(i).period_date;
51 END IF;
52
53 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM')
54 THEN l_time_from := p_param(i).period_date;
55 END IF;
56
57 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM')
58 THEN l_time_from := p_param(i).period_date;
59 END IF;
60
61 IF (p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
62 THEN l_org := p_param(i).parameter_value;
63 END IF;
64
65 IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT')
66 THEN l_inv_cat := p_param(i).parameter_value;
67 END IF;
68
69 IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
70 THEN l_item := p_param(i).parameter_value;
71 END IF;
72
73 IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
74 THEN l_curr := p_param(i).parameter_id;
75 END IF;
76
77 END LOOP;
78
79 IF (l_curr = l_curr_g)
80 THEN
81 l_col1 := 'pro_cost_g';
82 l_col2 := 'carrying_cost_g';
83 l_col3 := 'pur_cost_g';
84 ELSIF (l_curr = l_curr_g1)
85 THEN
86 l_col1 := 'pro_cost_g1';
87 l_col2 := 'carrying_cost_g1';
88 l_col3 := 'pur_cost_g1';
89 ELSE
90 l_col1 := 'production_cost';
91 l_col2 := 'carrying_cost';
92 l_col3 := 'purchasing_cost';
93 END IF;
94
95 IF (l_org IS NULL OR l_org = 'All')
96 THEN l_org_where := '
97 AND (EXISTS
98 (SELECT 1
99 FROM org_access o
100 WHERE o.responsibility_id = fnd_global.resp_id
101 AND o.resp_application_id = fnd_global.resp_appl_id
102 AND o.organization_id = f.organization_id)
103 OR EXISTS
104 (SELECT 1
105 FROM mtl_parameters org
106 WHERE org.organization_id = f.organization_id
107 AND NOT EXISTS
108 (SELECT 1
109 FROM org_access ora
110 WHERE org.organization_id = ora.organization_id)))';
111 ELSE l_org_where := '
112 AND f.organization_id = &ORGANIZATION+ORGANIZATION';
113 END IF;
114
115 IF(l_inv_cat IS NULL OR l_inv_cat = 'All')
116 THEN l_inv_cat_where := '';
117 ELSE l_inv_cat_where := '
118 AND f.inv_category_id IN (&ITEM+ENI_ITEM_INV_CAT)';
119 END IF;
120
121 IF(l_item IS NULL OR l_item = 'All')
122 THEN l_item_where := '';
123 ELSE l_item_where := '
124 AND f.item_id IN (&ITEM+ENI_ITEM_ORG)';
125 END IF;
126
127 IF((l_inv_cat IS NULL OR l_inv_cat = 'All') AND (l_item IS NULL OR l_item = 'All'))
128 THEN l_item_cat_flag := 3; -- no grouping on item dimension
129 ELSE
130 IF (l_item IS NULL OR l_item = 'All')
131 THEN l_item_cat_flag := 1; -- inventory category
132 ELSE l_item_cat_flag := 0; -- item
133 END IF;
134 END IF;
135
136 IF (l_period_type = 'FII_TIME_ENT_PERIOD') THEN
137 l_period_type_id := 32;
138 l_loop := 11;
139 ELSIF (l_period_type = 'FII_TIME_ENT_QTR') THEN
140 l_period_type_id := 64;
141 l_loop := 7;
142 ELSE -- l_period_type = 'FII_TIME_ENT_YEAR'
143 l_period_type_id := 128;
144 l_loop := 3;
145 END IF;
146
147 -- Get report end date
148 l_rpt_end_date := l_time_from;
149
150 FOR i IN 1..l_loop
151 LOOP
152 l_rpt_end_date := FII_TIME_API.next_period_end_date(l_rpt_end_date, l_period_type);
153 END LOOP;
154
155 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
156 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
157
158 IF (l_plan IS NULL OR l_plan2 IS NULL)
159 THEN l_stmt := '
160 SELECT 0 ISC_MEASURE_1,
161 0 ISC_MEASURE_2,
162 0 ISC_MEASURE_3,
163 0 ISC_MEASURE_4,
164 0 ISC_MEASURE_5,
165 0 ISC_MEASURE_6,
166 0 ISC_MEASURE_7,
167 0 ISC_MEASURE_8,
168 0 ISC_MEASURE_9,
169 0 ISC_MEASURE_10,
170 0 ISC_MEASURE_11,
171 0 ISC_MEASURE_12
172 FROM dual
173 WHERE 1 = 2 /* PLAN_SNAPSHOT dimension has not been populated */';
174 ELSE
175
176 l_stmt := '
177 SELECT c.period_name VIEWBY,
178 nvl(c.prod,0) ISC_MEASURE_1, -- Production Cost
179 nvl(c.comp_prod,0) ISC_MEASURE_2, -- Compare Plan (Production Cost)
180 nvl((c.prod - c.comp_prod),0) ISC_MEASURE_3, -- Variance (Production Cost)
181 nvl(c.carry,0) ISC_MEASURE_4, -- Carrying Cost
182 nvl(c.comp_carry,0) ISC_MEASURE_5, -- Compare Plan (Carrying Cost)
183 nvl((c.carry - c.comp_carry),0) ISC_MEASURE_6, -- Variance (Carrying Cost)
184 nvl(c.purch,0) ISC_MEASURE_7, -- Purchasing Cost
185 nvl(c.comp_purch,0) ISC_MEASURE_8, -- Compare Plan (Purchasing Cost)
186 nvl((c.purch - c.comp_purch),0) ISC_MEASURE_9, -- Variance (Purchasing Cost)
187 nvl((c.prod + c.carry + c.purch),0)
188 ISC_MEASURE_10, -- Combined Cost
189 nvl((c.comp_prod + c.comp_carry + c.comp_purch),0)
190 ISC_MEASURE_11, -- Compare Plan (Combined Cost)
191 nvl(((c.prod + c.carry + c.purch)
192 - (c.comp_prod + c.comp_carry + c.comp_purch)),0)
193 ISC_MEASURE_12 -- Variance (Combined Cost)
194 FROM (SELECT time.start_date PERIOD_ID,
195 time.name PERIOD_NAME,
196 sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
197 f.'||l_col1||', 0)) PROD,
198 decode(&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2, -1, null,
199 sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
200 f.'||l_col1||', 0))) COMP_PROD,
201 sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
202 f.'||l_col2||', 0)) CARRY,
203 decode(&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2, -1, null,
204 sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
205 f.'||l_col2||', 0))) COMP_CARRY,
206 sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
207 f.'||l_col3||', 0)) PURCH,
208 decode(&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2, -1, null,
209 sum(decode(f.snapshot_id, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
210 f.'||l_col3||', 0))) COMP_PURCH
211 FROM (SELECT start_date, name
212 FROM '||l_period_type||'
213 WHERE start_date BETWEEN :ISC_TIME_FROM AND :ISC_RPT_END_DATE
214 ) time
215 LEFT OUTER JOIN
216 ISC_DBI_PM_0001_MV f
217 ON f.start_date = time.start_date
218 AND f.period_type_id = :ISC_PERIOD_TYPE_ID
219 AND f.item_cat_flag = :ISC_ITEM_CAT_FLAG
220 AND f.union1_flag <> 0
221 AND f.snapshot_id IN (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT, &PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)'
222 ||l_org_where||l_inv_cat_where||l_item_where||'
223 GROUP BY time.name, time.start_date) c
224 ORDER BY c.period_id';
225
226 END IF;
227
228 x_custom_sql := l_stmt;
229
230 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
231 l_custom_rec.attribute_value := 'TIME+'||l_period_type;
232 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
233 x_custom_output.extend;
234 x_custom_output(1) := l_custom_rec;
235
236 l_custom_rec.attribute_name := ':ISC_PERIOD_TYPE_ID';
237 l_custom_rec.attribute_value := to_char(l_period_type_id);
238 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
239 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
240 x_custom_output.extend;
241 x_custom_output(2) := l_custom_rec;
242
243 l_custom_rec.attribute_name := ':ISC_TIME_FROM';
244 l_custom_rec.attribute_value := to_char(l_time_from,'DD/MM/YYYY');
245 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
246 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
247 x_custom_output.extend;
248 x_custom_output(3) := l_custom_rec;
249
250 l_custom_rec.attribute_name := ':ISC_RPT_END_DATE';
251 l_custom_rec.attribute_value := to_char(l_rpt_end_date,'DD/MM/YYYY');
252 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
253 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.date_bind;
254 x_custom_output.extend;
255 x_custom_output(4) := l_custom_rec;
256
257 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
258 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
259 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
260 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
261 x_custom_output.extend;
262 x_custom_output(5) := l_custom_rec;
263
264 END Get_Sql;
265
266 END ISC_DBI_PLAN_CB_SUM_TREND_PKG ;
267