[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_PLAN_OT_SHIP_TREND_PKG
Source
1 PACKAGE BODY ISC_DBI_PLAN_OT_SHIP_TREND_PKG AS
2 /* $Header: ISCRGATB.pls 115.4 2004/04/24 01:38:05 scheung 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_inv_cat VARCHAR2(32000);
15 l_item VARCHAR2(32000);
16 l_inv_cat_where VARCHAR2(32000);
17 l_item_where VARCHAR2(32000);
18 l_item_cat_flag NUMBER; -- 0 for item, 1 for inv.cat, 3 for all
19 l_time_from DATE;
23 l_period_type_id NUMBER;
20 l_time_to DATE;
21 l_cur_start DATE;
22 l_cur_end DATE;
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 = 'PERIOD_TYPE') THEN
46 l_period_type := p_param(i).parameter_value;
47 END IF;
48
49 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT') THEN
50 l_inv_cat := p_param(i).parameter_value;
51 END IF;
52
53 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG') THEN
54 l_item := p_param(i).parameter_value;
55 END IF;
56
57 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_FROM') THEN
58 l_time_from := p_param(i).period_date;
59 END IF;
60
61 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_PERIOD_TO') THEN
62 l_time_to := p_param(i).period_date;
63 END IF;
64
65 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_FROM') THEN
66 l_time_from := p_param(i).period_date;
67 END IF;
68
69 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_QTR_TO') THEN
70 l_time_to := p_param(i).period_date;
71 END IF;
72
73 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_FROM') THEN
74 l_time_from := p_param(i).period_date;
75 END IF;
76
77 IF(p_param(i).parameter_name = 'TIME+FII_TIME_ENT_YEAR_TO') THEN
78 l_time_to := p_param(i).period_date;
79 END IF;
80
81
82 END LOOP;
83
84
85 IF ( l_org IS NULL OR l_org = 'All' ) THEN
86 l_org_where := '
87 AND (EXISTS
88 (SELECT 1
89 FROM org_access o
90 WHERE o.responsibility_id = fnd_global.resp_id
91 AND o.resp_application_id = fnd_global.resp_appl_id
92 AND o.organization_id = f.organization_id)
93 OR EXISTS
94 (SELECT 1
95 FROM mtl_parameters org
96 WHERE org.organization_id = f.organization_id
97 AND NOT EXISTS
101
98 (SELECT 1
99 FROM org_access ora
100 WHERE org.organization_id = ora.organization_id)))';
102 ELSE
103 l_org_where := '
104 AND f.organization_id =(&ORGANIZATION+ORGANIZATION)';
105 END IF;
106
107
108 IF ( l_inv_cat IS NULL OR l_inv_cat = 'All' ) THEN
109 l_inv_cat_where :='';
110 ELSE
111 l_inv_cat_where := '
112 AND f.inv_category_id in (&ITEM+ENI_ITEM_INV_CAT)';
113 END IF;
114
115
116 IF ( l_item IS NULL OR l_item = 'All' )
117 THEN l_item_where := '';
118 ELSE l_item_where := '
119 AND f.item_id in (&ITEM+ENI_ITEM_ORG)';
120 END IF;
121
122 IF((l_inv_cat IS NULL OR l_inv_cat = 'All' ) AND ( l_item IS NULL OR l_item = 'All'))
123 THEN l_item_cat_flag := 3; -- no grouping on item dimension
124
125 ELSE
126 IF (l_item IS NULL OR l_item = 'All')
127 THEN l_item_cat_flag := 1; -- inv, category
128 ELSE l_item_cat_flag := 0; -- item is needed
129 END IF;
130 END IF;
131
132 IF l_period_type = 'FII_TIME_ENT_YEAR' THEN
133 l_loop :=3;
134 l_period_type_id := 128;
135
136 ELSIF (l_period_type='FII_TIME_ENT_QTR') THEN
137 l_loop := 7;
138 l_period_type_id := 64;
139
140 ELSE
141 l_loop :=11;
142 l_period_type_id := 32;
143
144 END IF;
145
146
147 l_cur_start := l_time_from;
148 l_cur_end := l_time_from;
149
150 FOR i IN 1..l_loop
151 LOOP
152 l_cur_end := FII_TIME_API.next_period_end_date(l_cur_end, l_period_type);
153 END LOOP;
154
155
156 l_custom_rec := BIS_PMV_PARAMETERS_PUB.INITIALIZE_QUERY_TYPE;
157 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
158
159
160 IF (l_plan IS NULL OR l_plan2 IS NULL)
161 THEN l_stmt := '
162 SELECT 0 ISC_MEASURE_1,
163 0 ISC_MEASURE_2,
164 0 ISC_MEASURE_3
165 FROM dual
169 l_stmt:='SELECT s.period_name VIEWBY,
166 WHERE 1 = 2 /* PLAN_SNAPSHOT dimension has not been populated */';
167 ELSE
168
170 sum(s.plan_ontime_lines)/decode(sum(s.plan_total_lines),
171 0,null,sum(s.plan_total_lines))*100 ISC_MEASURE_1,
172 sum(s.comp_ontime_lines)/decode(sum(s.comp_total_lines),
173 0,null,sum(s.comp_total_lines))*100 ISC_MEASURE_2,
174 (sum(s.plan_ontime_lines)/decode(sum(s.plan_total_lines),
175 0,null,sum(s.plan_total_lines)))*100-
176 (sum(s.comp_ontime_lines)/decode(sum(s.comp_total_lines)
177 ,0,null,sum(s.comp_total_lines)))*100 ISC_MEASURE_3
178 FROM(
179 SELECT time.start_date PERIOD_ID,
180 time.name PERIOD_NAME,
181 decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
182 sum(f.total_lines)-sum(f.late_lines),null) plan_ontime_lines,
183 decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,
184 sum(f.total_lines),null) plan_total_lines,
185 decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
186 sum(f.total_lines)-sum(f.late_lines),null) comp_ontime_lines,
187 decode(f.snapshot_id,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2,
188 sum(f.total_lines),null) comp_total_lines
189 FROM
190 (SELECT start_date,name
191 FROM '||l_period_type||'
192 WHERE start_date between :ISC_CUR_START and :ISC_CUR_END) time
193 LEFT OUTER JOIN
194 ISC_DBI_PM_0001_MV f
195 ON f.start_date = time.start_date
196 AND f.period_type_id = :ISC_PERIOD_TYPE_ID
197 AND f.union2_flag <> 0
198 AND f.item_cat_flag = :ISC_ITEM_CAT_FLAG
199 AND f.snapshot_id in (&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT,&PLAN_SNAPSHOT+PLAN_SNAPSHOT+PLAN_SNAPSHOT_2)
200 '||l_org_where||l_inv_cat_where||l_item_where||
201 'GROUP BY time.name,time.start_date,f.snapshot_id) s
202 GROUP BY s.period_name,s.period_id
203 ORDER BY s.period_id';
204
205 END IF;
206
207 x_custom_sql := l_stmt;
208
209 l_custom_rec.attribute_name := ':ISC_PERIOD_TYPE_ID';
210 l_custom_rec.attribute_value := to_char(l_period_type_id);
211 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
212 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
213 x_custom_output.EXTEND;
214 x_custom_output(1) := l_custom_rec;
215
216 l_custom_rec.attribute_name := ':ISC_CUR_START';
217 l_custom_rec.attribute_value := to_char(l_cur_start,'DD/MM/YYYY');
218 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
219 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
220 x_custom_output.EXTEND;
221 x_custom_output(2) := l_custom_rec;
222
223 l_custom_rec.attribute_name := ':ISC_CUR_END';
224 l_custom_rec.attribute_value := to_char(l_cur_end,'DD/MM/YYYY');
225 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
226 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.DATE_BIND;
230 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
227 x_custom_output.EXTEND;
228 x_custom_output(3) := l_custom_rec;
229
231 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
232 l_custom_Rec.attribute_type := BIS_PMV_PARAMETERS_PUB.BIND_TYPE;
233 l_custom_Rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.INTEGER_BIND;
234 x_custom_output.EXTEND;
235 x_custom_output(4) := l_custom_rec;
236
237 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.VIEW_BY_VALUE;
238 l_custom_rec.attribute_value := 'TIME+'||l_period_type;
239 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.VIEW_BY_TYPE;
240 x_custom_output.EXTEND;
241 x_custom_output(5) := l_custom_rec;
242
243
244 END get_sql;
245
246 END ISC_DBI_PLAN_OT_SHIP_TREND_PKG ;
247