[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_FULF_PERF_TREND_PKG
Source
1 PACKAGE BODY ISC_DBI_FULF_PERF_TREND_PKG AS
2 /* $Header: ISCRG98B.pls 120.0 2005/05/25 17:17:02 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_mv1 VARCHAR2(100);
12 l_mv2 VARCHAR2(100);
13 l_flags_where VARCHAR2(1000);
14 l_inv_org VARCHAR2(10000);
15 l_inv_org_where VARCHAR2(10000);
16 l_prod VARCHAR2(10000);
17 l_prod_where VARCHAR2(10000);
18 l_prod_cat VARCHAR2(10000);
19 l_prod_cat_from VARCHAR2(10000);
20 l_prod_cat_where VARCHAR2(10000);
21 l_cust VARCHAR2(10000);
22 l_cust_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_009_MV';
139 l_mv2 := 'ISC_DBI_CFM_011_MV';
140 l_flags_where := '
141 AND fact.inv_org_flag = 0';
142 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
143 THEN
144 l_prod_cat_from := '';
145 l_prod_cat_where := '
146 AND fact.top_node_flag = ''Y''';
147 ELSE
148 l_prod_cat_from := '';
149 l_prod_cat_where := '
150 AND fact.parent_id = &ITEM+ENI_ITEM_VBH_CAT';
151 END IF;
152 ELSE
153 l_mv1 := 'ISC_DBI_CFM_000_MV';
154 l_mv2 := 'ISC_DBI_CFM_002_MV';
155 l_flags_where := '
156 AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
157 AND fact.customer_flag = :ISC_CUST_FLAG';
158 END IF;
159
160 l_stmt := '
161 SELECT fii.name VIEWBY,
162 nvl(s.prev_booked_value, 0) ISC_MEASURE_1, -- book prior
163 nvl(s.curr_booked_value, 0) ISC_MEASURE_2, -- book
164 (s.curr_booked_value-s.prev_booked_value)
165 / decode(s.prev_booked_value, 0, NULL,
166 abs(s.prev_booked_value)) * 100 ISC_MEASURE_3, -- book change
167 nvl(s.prev_fulfill_value, 0) ISC_MEASURE_4, -- fulf prior
168 nvl(s.curr_fulfill_value, 0) ISC_MEASURE_5, -- fulf
169 (s.curr_fulfill_value-s.prev_fulfill_value)
170 / decode(s.prev_fulfill_value, 0, NULL,
171 abs(s.prev_fulfill_value)) * 100 ISC_MEASURE_6, -- fulf change
172 s.prev_booked_value
173 / decode(s.prev_fulfill_value, 0, NULL,
174 s.prev_fulfill_value) ISC_MEASURE_7, -- book to fulf r prior
175 s.curr_booked_value
176 / decode(s.curr_fulfill_value, 0, NULL,
177 s.curr_fulfill_value) ISC_MEASURE_8, -- book to fulf r
178 s.curr_booked_value
179 / decode(s.curr_fulfill_value, 0, NULL,
180 s.curr_fulfill_value) -
181 s.prev_booked_value
182 / decode(s.prev_fulfill_value, 0, NULL,
183 s.prev_fulfill_value) ISC_MEASURE_9 -- book to fulf r change
184 FROM (SELECT start_date START_DATE,
185 sum(curr_booked_value) CURR_BOOKED_VALUE,
186 sum(prev_booked_value) PREV_BOOKED_VALUE,
187 sum(curr_fulfill_value) CURR_FULFILL_VALUE,
188 sum(prev_fulfill_value) PREV_FULFILL_VALUE
189 FROM
190 (SELECT dates.start_date START_DATE,
191 fact.inv_org_id INV_ORG,
192 decode(dates.period, ''C'',
193 nvl(fact.booked_amt_'||l_curr_suffix||',0), 0) CURR_BOOKED_VALUE,
194 decode(dates.period, ''P'',
195 nvl(fact.booked_amt_'||l_curr_suffix||',0), 0) PREV_BOOKED_VALUE,
196 0 CURR_FULFILL_VALUE,
197 0 PREV_FULFILL_VALUE
198 FROM (SELECT fii.start_date START_DATE,
199 ''C'' PERIOD,
200 least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
201 FROM '||l_period_type||' fii
202 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
203 AND &BIS_CURRENT_ASOF_DATE
204 UNION ALL
205 SELECT p2.start_date START_DATE,
206 ''P'' PERIOD,
207 p1.report_date REPORT_DATE
208 FROM (SELECT least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE,
209 rownum ID
210 FROM '||l_period_type||' fii
211 WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
212 AND &BIS_PREVIOUS_ASOF_DATE
213 ORDER BY fii.start_date DESC) p1,
214 (SELECT fii.start_date START_DATE,
215 rownum ID
216 FROM '||l_period_type||' fii
217 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
218 AND &BIS_CURRENT_ASOF_DATE
219 ORDER BY fii.start_date DESC) p2
220 WHERE p1.id(+) = p2.id) dates,
221 '||l_mv1||' fact,
222 FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
223 WHERE cal.report_date = dates.report_date
224 AND fact.time_id = cal.time_id
225 AND fact.period_type_id = cal.period_type_id'
226 ||l_flags_where||'
227 AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
228 ||l_prod_cat_where
229 ||l_prod_where
230 ||l_cust_where||'
231 UNION ALL
232 SELECT dates.start_date START_DATE,
233 fact.inv_org_id INV_ORG,
234 0 CURR_BOOKED_VALUE,
235 0 PREV_BOOKED_VALUE,
236 decode(dates.period, ''C'',
237 nvl(fact.fulfilled_amt_'||l_curr_suffix||',0), 0) CURR_FULFILL_VALUE,
238 decode(dates.period, ''P'',
239 nvl(fact.fulfilled_amt_'||l_curr_suffix||',0), 0) PREV_FULFILL_VALUE
240 FROM (SELECT fii.start_date START_DATE,
241 ''C'' PERIOD,
242 least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
243 FROM '||l_period_type||' fii
244 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
245 AND &BIS_CURRENT_ASOF_DATE
246 UNION ALL
247 SELECT p2.start_date START_DATE,
248 ''P'' PERIOD,
249 p1.report_date REPORT_DATE
250 FROM (SELECT least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE,
251 rownum ID
252 FROM '||l_period_type||' fii
253 WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
254 AND &BIS_PREVIOUS_ASOF_DATE
255 ORDER BY fii.start_date DESC) p1,
256 (SELECT fii.start_date START_DATE,
257 rownum ID
258 FROM '||l_period_type||' fii
259 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
260 AND &BIS_CURRENT_ASOF_DATE
261 ORDER BY fii.start_date DESC) p2
262 WHERE p1.id(+) = p2.id) dates,
263 '||l_mv2||' fact,
264 FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
265 WHERE cal.report_date = dates.report_date
266 AND fact.time_id = cal.time_id
267 AND fact.period_type_id = cal.period_type_id'
268 ||l_flags_where||'
269 AND fact.return_flag = 0
270 AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
271 ||l_prod_cat_where
272 ||l_prod_where
273 ||l_cust_where||')
274 WHERE '||l_inv_org_where||'
275 GROUP BY start_date) s,
276 '||l_period_type||' fii
277 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
278 AND &BIS_CURRENT_ASOF_DATE
279 AND fii.start_date = s.start_date(+)
280 ORDER BY fii.start_date';
281
282 x_custom_sql := l_stmt;
283
284 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.View_By_Value;
285 l_custom_rec.attribute_value := 'TIME+'||l_period_type;
286 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
287 x_custom_output.extend;
288 x_custom_output(1) := l_custom_rec;
289
290 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
291 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
292 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.Bind_Type;
293 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.Integer_Bind;
294 x_custom_output.extend;
295 x_custom_output(2) := l_custom_rec;
296
297 l_custom_rec.attribute_name := ':ISC_CUST_FLAG';
298 l_custom_rec.attribute_value := to_char(l_cust_flag);
299 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
300 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
301 x_custom_output.extend;
302 x_custom_output(3) := l_custom_rec;
303
304 END Get_Sql;
305
306 END ISC_DBI_FULF_PERF_TREND_PKG;