[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_FULF_PERF_T_TREND_PKG
Source
1 PACKAGE BODY ISC_DBI_FULF_PERF_T_TREND_PKG AS
2 /* $Header: ISCRGA0B.pls 120.0 2005/05/25 17:22:35 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_period_type VARCHAR2(10000);
11 l_inv_org VARCHAR2(10000);
12 l_inv_org_where VARCHAR2(10000);
13 l_prod VARCHAR2(10000);
14 l_prod_where VARCHAR2(10000);
15 l_prod_cat VARCHAR2(10000);
16 l_prod_cat_from VARCHAR2(10000);
17 l_prod_cat_where VARCHAR2(10000);
18 l_cust VARCHAR2(10000);
19 l_cust_where VARCHAR2(10000);
20 l_mv1 VARCHAR2(10000);
21 l_mv2 VARCHAR2(10000);
22 l_flags_where VARCHAR2(10000);
23 l_curr VARCHAR2(10000);
24 l_curr_g VARCHAR2(15);
25 l_curr_g1 VARCHAR2(15);
26 l_curr_suffix VARCHAR2(120);
27 l_item_cat_flag NUMBER;
28 l_cust_flag NUMBER;
29
30 l_custom_rec BIS_QUERY_ATTRIBUTES;
31
32 BEGIN
33
34 l_curr_g := '''FII_GLOBAL1''';
35 l_curr_g1 := '''FII_GLOBAL2''';
36
37 FOR i IN 1..p_param.COUNT
38 LOOP
39 IF (p_param(i).parameter_name = 'PERIOD_TYPE')
40 THEN l_period_type := p_param(i).parameter_value;
41 END IF;
42
43 IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
44 THEN l_curr := p_param(i).parameter_id;
45 END IF;
46
47 IF (p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
48 THEN l_inv_org := p_param(i).parameter_value;
49 END IF;
50
51 IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT')
52 THEN l_prod_cat := p_param(i).parameter_value;
53 END IF;
54
55 IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
56 THEN l_prod := p_param(i).parameter_value;
57 END IF;
58
59 IF (p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
60 THEN l_cust := p_param(i).parameter_value;
61 END IF;
62 END LOOP;
63
64 IF (l_curr = l_curr_g)
65 THEN l_curr_suffix := 'g';
66 ELSIF (l_curr = l_curr_g1)
67 THEN l_curr_suffix :='g1';
68 ELSE l_curr_suffix := 'f';
69 END IF;
70
71 IF (l_inv_org IS NULL OR l_inv_org = '' OR l_inv_org = 'All')
72 THEN l_inv_org_where := '(EXISTS
73 (SELECT 1
74 FROM org_access o
75 WHERE o.responsibility_id = fnd_global.resp_id
76 AND o.resp_application_id = fnd_global.resp_appl_id
77 AND o.organization_id = inv_org)
78 OR EXISTS
79 (SELECT 1
80 FROM mtl_parameters org
81 WHERE org.organization_id = inv_org
82 AND NOT EXISTS
83 (SELECT 1
84 FROM org_access ora
85 WHERE org.organization_id = ora.organization_id)))';
86 ELSE l_inv_org_where := 'inv_org = &ORGANIZATION+ORGANIZATION';
87 END IF;
88
89 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
90 THEN
91 l_prod_cat_from := '';
92 l_prod_cat_where := '';
93 ELSE
94 l_prod_cat_from := ',
95 ENI_DENORM_HIERARCHIES eni_cat,
96 MTL_DEFAULT_CATEGORY_SETS mdcs';
97 l_prod_cat_where := '
98 AND fact.item_category_id = eni_cat.child_id
99 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
100 AND eni_cat.dbi_flag = ''Y''
101 AND eni_cat.object_type = ''CATEGORY_SET''
102 AND eni_cat.object_id = mdcs.category_set_id
103 AND mdcs.functional_area_id = 11';
104 END IF;
105
106 IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All')
107 THEN l_prod_where := '';
108 ELSE l_prod_where := '
109 AND fact.item_id IN (&ITEM+ENI_ITEM_ORG)';
110 END IF;
111
112 IF (l_cust IS NULL OR l_cust = '' OR l_cust = 'All')
113 THEN
114 l_cust_where := '';
115 l_cust_flag := 1;
116 ELSE
117 l_cust_where := '
118 AND fact.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
119 l_cust_flag := 0;
120 END IF;
121
122 IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All')
123 THEN
124 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
125 THEN l_item_cat_flag := 3; -- category
126 ELSE l_item_cat_flag := 1; -- all
127 END IF;
128 ELSE
129 l_item_cat_flag := 0; -- product
130 END IF;
131
132 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
133 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
134
135 IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All') AND
136 (l_cust IS NULL OR l_cust = '' OR l_cust = 'All')
137 THEN
138 l_mv1 := 'ISC_DBI_CFM_016_MV';
139 l_mv2 := 'ISC_DBI_CFM_017_MV';
140 l_flags_where := '';
141 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
142 THEN
143 l_prod_cat_from := '';
144 l_prod_cat_where := '
145 AND fact.top_node_flag = ''Y''';
146 ELSE
147 l_prod_cat_from := '';
148 l_prod_cat_where := '
149 AND fact.parent_id = &ITEM+ENI_ITEM_VBH_CAT';
150 END IF;
151 ELSE
152 l_mv1 := 'ISC_DBI_CFM_004_MV';
153 l_mv2 := 'ISC_DBI_CFM_005_MV';
154 l_flags_where := '
155 AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
156 AND fact.customer_flag = :ISC_CUST_FLAG';
157 END IF;
158
159 l_stmt := '
160 SELECT fii.name VIEWBY,
161 nvl(s.prev_booked_value, 0) ISC_MEASURE_1, -- book prior
162 nvl(s.curr_booked_value, 0) ISC_MEASURE_2, -- book
163 (s.curr_booked_value-s.prev_booked_value)
164 / decode(s.prev_booked_value, 0, NULL,
165 abs(s.prev_booked_value)) * 100 ISC_MEASURE_3, -- book change
166 nvl(s.prev_fulfill_value, 0) ISC_MEASURE_4, -- fulf prior
167 nvl(s.curr_fulfill_value, 0) ISC_MEASURE_5, -- fulf
168 (s.curr_fulfill_value-s.prev_fulfill_value)
169 / decode(s.prev_fulfill_value, 0, NULL,
170 abs(s.prev_fulfill_value)) * 100 ISC_MEASURE_6, -- fulf change
171 s.prev_booked_value
172 / decode(s.prev_fulfill_value, 0, NULL,
173 s.prev_fulfill_value) ISC_MEASURE_7, -- book to fulf r prior
174 s.curr_booked_value
175 / decode(s.curr_fulfill_value, 0, NULL,
176 s.curr_fulfill_value) ISC_MEASURE_8, -- book to fulf r
177 s.curr_booked_value
178 / decode(s.curr_fulfill_value, 0, NULL,
179 s.curr_fulfill_value) -
180 s.prev_booked_value
181 / decode(s.prev_fulfill_value, 0, NULL,
182 s.prev_fulfill_value) ISC_MEASURE_9 -- book to fulf r change
183 FROM (SELECT start_date START_DATE,
184 sum(curr_booked_value) CURR_BOOKED_VALUE,
185 sum(prev_booked_value) PREV_BOOKED_VALUE,
186 sum(curr_fulfill_value) CURR_FULFILL_VALUE,
187 sum(prev_fulfill_value) PREV_FULFILL_VALUE
188 FROM
189 (SELECT dates.start_date START_DATE,
190 fact.inv_org_id INV_ORG,
191 decode(dates.period, ''C'',
192 nvl(fact.booked_amt_'||l_curr_suffix||',0), 0) CURR_BOOKED_VALUE,
193 decode(dates.period, ''P'',
194 nvl(fact.booked_amt_'||l_curr_suffix||',0), 0) PREV_BOOKED_VALUE,
195 0 CURR_FULFILL_VALUE,
196 0 PREV_FULFILL_VALUE
197 FROM (SELECT fii.start_date START_DATE,
198 ''C'' PERIOD,
199 least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
200 FROM '||l_period_type||' fii
201 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
202 AND &BIS_CURRENT_ASOF_DATE
203 UNION ALL
204 SELECT p2.start_date START_DATE,
205 ''P'' PERIOD,
206 p1.report_date REPORT_DATE
207 FROM (SELECT least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE,
208 rownum ID
209 FROM '||l_period_type||' fii
210 WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
211 AND &BIS_PREVIOUS_ASOF_DATE
212 ORDER BY fii.start_date DESC) p1,
213 (SELECT fii.start_date START_DATE,
214 rownum ID
215 FROM '||l_period_type||' fii
216 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
217 AND &BIS_CURRENT_ASOF_DATE
218 ORDER BY fii.start_date DESC) p2
219 WHERE p1.id(+) = p2.id) dates,
220 '||l_mv1||' fact,
221 FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
222 WHERE cal.report_date = dates.report_date
223 AND fact.time_id = cal.time_id
224 AND fact.period_type_id = cal.period_type_id'||l_flags_where||'
225 AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
226 ||l_prod_cat_where
227 ||l_prod_where
228 ||l_cust_where||'
229 UNION ALL
230 SELECT dates.start_date START_DATE,
231 fact.inv_org_id INV_ORG,
232 0 CURR_BOOKED_VALUE,
233 0 PREV_BOOKED_VALUE,
234 decode(dates.period, ''C'',
235 nvl(fact.fulfilled_amt_'||l_curr_suffix||',0), 0) CURR_FULFILL_VALUE,
236 decode(dates.period, ''P'',
237 nvl(fact.fulfilled_amt_'||l_curr_suffix||',0), 0) PREV_FULFILL_VALUE
238 FROM (SELECT fii.start_date START_DATE,
239 ''C'' PERIOD,
240 least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
241 FROM '||l_period_type||' fii
242 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
243 AND &BIS_CURRENT_ASOF_DATE
244 UNION ALL
245 SELECT p2.start_date START_DATE,
246 ''P'' PERIOD,
247 p1.report_date REPORT_DATE
248 FROM (SELECT least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE,
249 rownum ID
250 FROM '||l_period_type||' fii
251 WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
252 AND &BIS_PREVIOUS_ASOF_DATE
253 ORDER BY fii.start_date DESC) p1,
254 (SELECT fii.start_date START_DATE,
255 rownum ID
256 FROM '||l_period_type||' fii
257 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
258 AND &BIS_CURRENT_ASOF_DATE
259 ORDER BY fii.start_date DESC) p2
260 WHERE p1.id(+) = p2.id) dates,
261 '||l_mv2||' fact,
262 FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
263 WHERE cal.report_date = dates.report_date
264 AND fact.time_id = cal.time_id
265 AND fact.period_type_id = cal.period_type_id
266 AND fact.return_flag = 0
267 AND fact.internal_flag = 0'||l_flags_where||'
268 AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
269 ||l_prod_cat_where
270 ||l_prod_where
271 ||l_cust_where||')
272 WHERE '||l_inv_org_where||'
273 GROUP BY start_date) s,
274 '||l_period_type||' fii
275 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
276 AND &BIS_CURRENT_ASOF_DATE
277 AND fii.start_date = s.start_date(+)
278 ORDER BY fii.start_date';
279
280 x_custom_sql := l_stmt;
281
282 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.View_By_Value;
283 l_custom_rec.attribute_value := 'TIME+'||l_period_type;
284 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
285 x_custom_output.extend;
286 x_custom_output(1) := l_custom_rec;
287
288 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
289 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
290 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.Bind_Type;
291 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.Integer_Bind;
292 x_custom_output.extend;
293 x_custom_output(2) := l_custom_rec;
294
295 l_custom_rec.attribute_name := ':ISC_CUST_FLAG';
296 l_custom_rec.attribute_value := to_char(l_cust_flag);
297 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
298 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
299 x_custom_output.extend;
300 x_custom_output(3) := l_custom_rec;
301
302 END Get_Sql;
303
304 END ISC_DBI_FULF_PERF_T_TREND_PKG;