[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_DAYS_FULF_TREND_PKG
Source
1 PACKAGE BODY ISC_DBI_DAYS_FULF_TREND_PKG AS
2 /* $Header: ISCRG72B.pls 120.0 2005/05/25 17:18:02 appldev noship $ */
3
4 PROCEDURE Get_Sql ( p_param IN BIS_PMV_PAGE_PARAMETER_TBL,
5 x_custom_sql OUT NOCOPY VARCHAR2,
6 x_custom_output OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS
7
8 l_stmt VARCHAR2(10000);
9 l_period_type VARCHAR2(10000);
10 l_inv_org VARCHAR2(10000);
11 l_inv_org_where VARCHAR2(10000);
12 l_prod VARCHAR2(10000);
13 l_prod_where VARCHAR2(10000);
14 l_prod_cat VARCHAR2(10000);
15 l_prod_cat_from VARCHAR2(10000);
16 l_prod_cat_where VARCHAR2(10000);
17 l_cust VARCHAR2(10000);
18 l_cust_where VARCHAR2(10000);
19 l_mv VARCHAR2(10000);
20 l_flags_where VARCHAR2(10000);
21 l_item_cat_flag NUMBER;
22 l_cust_flag NUMBER;
23
24 l_custom_rec BIS_QUERY_ATTRIBUTES;
25
26 BEGIN
27
28 FOR i IN 1..p_param.COUNT
29 LOOP
30 IF (p_param(i).parameter_name = 'PERIOD_TYPE')
31 THEN l_period_type := p_param(i).parameter_value;
32 END IF;
33
34 IF (p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
35 THEN l_inv_org := p_param(i).parameter_value;
36 END IF;
37
38 IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT')
39 THEN l_prod_cat := p_param(i).parameter_value;
40 END IF;
41
42 IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
43 THEN l_prod := p_param(i).parameter_value;
44 END IF;
45
46 IF (p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
47 THEN l_cust := p_param(i).parameter_value;
48 END IF;
49 END LOOP;
50
51 IF (l_inv_org IS NULL OR l_inv_org = '' OR l_inv_org = 'All')
52 THEN l_inv_org_where := '
53 AND (EXISTS
54 (SELECT 1
55 FROM org_access o
56 WHERE o.responsibility_id = fnd_global.resp_id
57 AND o.resp_application_id = fnd_global.resp_appl_id
58 AND o.organization_id = fact.inv_org_id)
59 OR EXISTS
60 (SELECT 1
61 FROM mtl_parameters org
62 WHERE org.organization_id = fact.inv_org_id
63 AND NOT EXISTS
64 (SELECT 1
65 FROM org_access ora
66 WHERE org.organization_id = ora.organization_id)))';
67 ELSE l_inv_org_where := '
68 AND fact.inv_org_id = &ORGANIZATION+ORGANIZATION';
69 END IF;
70
71 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
72 THEN
73 l_prod_cat_from := '';
74 l_prod_cat_where := '';
75 ELSE
76 l_prod_cat_from := ',
77 ENI_DENORM_HIERARCHIES eni_cat,
78 MTL_DEFAULT_CATEGORY_SETS mdcs';
79 l_prod_cat_where := '
80 AND fact.item_category_id = eni_cat.child_id
81 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
82 AND eni_cat.dbi_flag = ''Y''
83 AND eni_cat.object_type = ''CATEGORY_SET''
84 AND eni_cat.object_id = mdcs.category_set_id
85 AND mdcs.functional_area_id = 11';
86 END IF;
87
88 IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All')
89 THEN l_prod_where := '';
90 ELSE l_prod_where := '
91 AND fact.item_id IN (&ITEM+ENI_ITEM_ORG)';
92 END IF;
93
94 IF (l_cust IS NULL OR l_cust = '' OR l_cust = 'All')
95 THEN
96 l_cust_where := '';
97 l_cust_flag := 1;
98 ELSE
99 l_cust_where := '
100 AND fact.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
101 l_cust_flag := 0;
102 END IF;
103
104 IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All')
105 THEN
106 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
107 THEN l_item_cat_flag := 3; -- category
108 ELSE l_item_cat_flag := 1; -- all
109 END IF;
110 ELSE
111 l_item_cat_flag := 0; -- product
112 END IF;
113
114 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
115 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
116
117 IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All') AND
118 (l_cust IS NULL OR l_cust = '' OR l_cust = 'All')
119 THEN
120 l_mv := 'ISC_DBI_CFM_011_MV';
121 l_flags_where := '
122 AND fact.inv_org_flag = 0';
123 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
124 THEN
125 l_prod_cat_from := '';
126 l_prod_cat_where := '
127 AND fact.top_node_flag = ''Y''';
128 ELSE
129 l_prod_cat_from := '';
130 l_prod_cat_where := '
131 AND fact.parent_id = &ITEM+ENI_ITEM_VBH_CAT';
132 END IF;
133 ELSE
134 l_mv := 'ISC_DBI_CFM_002_MV';
135 l_flags_where := '
136 AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
137 AND fact.customer_flag = :ISC_CUST_FLAG';
138 END IF;
139
140 l_stmt := '
141 SELECT fii.name VIEWBY,
142 s.prev_days_fulf
143 / decode(s.prev_days_cnt, 0, NULL,
144 s.prev_days_cnt) ISC_MEASURE_1, -- days fulf prior
145 s.curr_days_fulf
146 / decode(s.curr_days_cnt, 0, NULL,
147 s.curr_days_cnt) ISC_MEASURE_2, -- days fulf
148 s.curr_days_fulf
149 / decode(s.curr_days_cnt, 0, NULL,
150 s.curr_days_cnt) -
151 s.prev_days_fulf
152 / decode(s.prev_days_cnt, 0, NULL,
153 s.prev_days_cnt) ISC_MEASURE_3, -- days fulf change
154 NULL CURRENCY, -- obsoleted from 5.0
155 NULL FND_CATEGORY, -- obsoleted from 5.0
156 NULL FND_PRODUCT, -- obsoleted from 5.0
157 NULL ISC_MEASURE_4, -- obsoleted from 5.0
158 NULL ISC_ATTRIBUTE_2 -- obsoleted from 5.0
159 FROM (SELECT dates.start_date START_DATE,
160 sum(decode(dates.period, ''C'',
161 nvl(fact.book_to_fulfill_days,0), 0)) CURR_DAYS_FULF,
162 sum(decode(dates.period, ''P'',
163 nvl(fact.book_to_fulfill_days,0), 0)) PREV_DAYS_FULF,
164 sum(decode(dates.period, ''C'',
165 nvl(fact.book_to_fulfill_cnt,0), 0)) CURR_DAYS_CNT,
166 sum(decode(dates.period, ''P'',
167 nvl(fact.book_to_fulfill_cnt,0), 0)) PREV_DAYS_CNT
168 FROM (SELECT fii.start_date START_DATE,
169 ''C'' PERIOD,
170 least(fii.end_date, &BIS_CURRENT_ASOF_DATE) REPORT_DATE
171 FROM '||l_period_type||' fii
172 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
173 AND &BIS_CURRENT_ASOF_DATE
174 UNION ALL
175 SELECT p2.start_date START_DATE,
176 ''P'' PERIOD,
177 p1.report_date REPORT_DATE
178 FROM (SELECT least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE) REPORT_DATE,
179 rownum ID
180 FROM '||l_period_type||' fii
181 WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
182 AND &BIS_PREVIOUS_ASOF_DATE
183 ORDER BY fii.start_date DESC) p1,
184 (SELECT fii.start_date START_DATE,
185 rownum ID
186 FROM '||l_period_type||' fii
187 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
188 AND &BIS_CURRENT_ASOF_DATE
189 ORDER BY fii.start_date DESC) p2
190 WHERE p1.id(+) = p2.id) dates,
191 '||l_mv||' fact,
192 FII_TIME_RPT_STRUCT_V cal'||l_prod_cat_from||'
193 WHERE cal.report_date = dates.report_date
194 AND fact.time_id = cal.time_id
195 AND fact.period_type_id = cal.period_type_id
196 AND fact.return_flag = 0'||l_flags_where||'
197 AND bitand(cal.record_type_id, &BIS_NESTED_PATTERN) = cal.record_type_id'
198 ||l_inv_org_where
199 ||l_prod_cat_where
200 ||l_prod_where
201 ||l_cust_where||'
202 GROUP BY dates.start_date) s,
203 '||l_period_type||' fii
204 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
205 AND &BIS_CURRENT_ASOF_DATE
206 AND fii.start_date = s.start_date(+)
207 ORDER BY fii.start_date';
208
209 x_custom_sql := l_stmt;
210
211 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.View_By_Value;
212 l_custom_rec.attribute_value := 'TIME+'||l_period_type;
213 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
214 x_custom_output.extend;
215 x_custom_output(1) := l_custom_rec;
216
217 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
218 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
219 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.Bind_Type;
220 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.Integer_Bind;
221 x_custom_output.extend;
222 x_custom_output(2) := l_custom_rec;
223
224 l_custom_rec.attribute_name := ':ISC_CUST_FLAG';
225 l_custom_rec.attribute_value := to_char(l_cust_flag);
226 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
227 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
228 x_custom_output.extend;
229 x_custom_output(3) := l_custom_rec;
230
231 END Get_Sql;
232
233 END ISC_DBI_DAYS_FULF_TREND_PKG;