[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_PDUE_PRM_TREND_PKG
Source
1 PACKAGE BODY ISC_DBI_PDUE_PRM_TREND_PKG AS
2 /* $Header: ISCRGAAB.pls 120.0 2005/05/25 17:43:22 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_mv VARCHAR2(10000);
21 l_flags_where VARCHAR2(10000);
22 l_curr VARCHAR2(10000);
23 l_curr_g VARCHAR2(15);
24 l_curr_g1 VARCHAR2(15);
25 l_curr_suffix VARCHAR2(120);
26 l_item_cat_flag NUMBER;
27 l_cust_flag NUMBER;
28
29 l_custom_rec BIS_QUERY_ATTRIBUTES;
30
31 BEGIN
32
33 l_curr_g := '''FII_GLOBAL1''';
34 l_curr_g1 := '''FII_GLOBAL2''';
35
36 FOR i IN 1..p_param.COUNT
37 LOOP
38 IF (p_param(i).parameter_name = 'PERIOD_TYPE')
39 THEN l_period_type := p_param(i).parameter_value;
40 END IF;
41
42 IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
43 THEN l_curr := p_param(i).parameter_id;
44 END IF;
45
46 IF (p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
47 THEN l_inv_org := p_param(i).parameter_value;
48 END IF;
49
50 IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT')
51 THEN l_prod_cat := p_param(i).parameter_value;
52 END IF;
53
54 IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
55 THEN l_prod := p_param(i).parameter_value;
56 END IF;
57
58 IF (p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
59 THEN l_cust := p_param(i).parameter_value;
60 END IF;
61 END LOOP;
62
63 IF (l_curr = l_curr_g)
64 THEN l_curr_suffix := 'g';
65 ELSIF (l_curr = l_curr_g1)
66 THEN l_curr_suffix :='g1';
67 ELSE l_curr_suffix := 'f';
68 END IF;
69
70 IF (l_inv_org IS NULL OR l_inv_org = '' OR l_inv_org = 'All')
71 THEN l_inv_org_where := '
72 AND (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 = fact.inv_org_id)
78 OR EXISTS
79 (SELECT 1
80 FROM mtl_parameters org
81 WHERE org.organization_id = fact.inv_org_id
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 := '
87 AND fact.inv_org_id = &ORGANIZATION+ORGANIZATION';
88 END IF;
89
90 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
91 THEN
92 l_prod_cat_from := '';
93 l_prod_cat_where := '';
94 ELSE
95 l_prod_cat_from := ',
96 ENI_DENORM_HIERARCHIES eni_cat,
97 MTL_DEFAULT_CATEGORY_SETS mdcs';
98 l_prod_cat_where := '
99 AND fact.item_category_id = eni_cat.child_id
100 AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
101 AND eni_cat.dbi_flag = ''Y''
102 AND eni_cat.object_type = ''CATEGORY_SET''
103 AND eni_cat.object_id = mdcs.category_set_id
104 AND mdcs.functional_area_id = 11';
105 END IF;
106
107 IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All')
108 THEN l_prod_where := '';
109 ELSE l_prod_where := '
110 AND fact.item_id IN (&ITEM+ENI_ITEM_ORG)';
111 END IF;
112
113 IF (l_cust IS NULL OR l_cust = '' OR l_cust = 'All')
114 THEN
115 l_cust_where := '';
116 l_cust_flag := 1;
117 ELSE
118 l_cust_where := '
119 AND fact.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
120 l_cust_flag := 0;
121 END IF;
122
123 IF (l_prod IS NULL OR l_prod = '' OR l_prod = 'All')
124 THEN
125 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
126 THEN l_item_cat_flag := 3; -- category
127 ELSE l_item_cat_flag := 1; -- all
128 END IF;
129 ELSE
130 l_item_cat_flag := 0; -- product
131 END IF;
132
133 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
134 x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
135
136 IF ((l_prod IS NULL OR l_prod = 'All') AND
137 (l_cust IS NULL OR l_cust = 'All'))
138 THEN
139 IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
140 THEN
141 l_prod_cat_from := '';
142 l_prod_cat_where := '
143 AND fact.top_node_flag = ''Y''';
144 ELSE
145 l_prod_cat_from := '';
146 l_prod_cat_where := '
147 AND fact.parent_id = &ITEM+ENI_ITEM_VBH_CAT';
148 END IF;
149 l_mv := 'ISC_DBI_CFM_012_MV';
150 l_flags_where := '';
151 ELSE
152 l_mv := 'ISC_DBI_CFM_008_MV';
153 l_flags_where := '
154 AND fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
155 AND fact.customer_flag = :ISC_CUST_FLAG';
156 END IF;
157
158 l_stmt := '
159 SELECT fii.name VIEWBY,
160 s.prev_pdue_value ISC_MEASURE_1, -- pdue prior
161 s.curr_pdue_value ISC_MEASURE_2, -- pdue
162 (s.curr_pdue_value - s.prev_pdue_value)
163 / decode(s.prev_pdue_value, 0, NULL,
164 abs(s.prev_pdue_value)) * 100 ISC_MEASURE_3 -- pdue change
165 FROM (SELECT dates.start_date START_DATE,
166 sum(decode(fact.time_snapshot_date_id, dates.curr_day,
167 fact.pdue_amt_'||l_curr_suffix||', NULL)) CURR_PDUE_VALUE,
168 sum(decode(fact.time_snapshot_date_id, dates.prev_day,
169 fact.pdue_amt_'||l_curr_suffix||', NULL)) PREV_PDUE_VALUE
170 FROM (SELECT curr.start_date START_DATE,
171 curr.day CURR_DAY,
172 prev.day PREV_DAY
173 FROM (SELECT start_date,
174 day,
175 rownum ID
176 FROM
177 (SELECT fii.start_date START_DATE,
178 max(fact.time_snapshot_date_id) DAY
179 FROM '||l_period_type||' fii,
180 '||l_mv||' fact
181 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
182 AND &BIS_CURRENT_ASOF_DATE
183 AND fact.time_snapshot_date_id (+) >= fii.start_date
184 AND fact.time_snapshot_date_id (+) <= fii.end_date
185 AND fact.time_snapshot_date_id (+) <= &BIS_CURRENT_ASOF_DATE
186 GROUP BY fii.start_date)
187 ORDER BY start_date DESC) curr,
188 (SELECT start_date,
189 day,
190 rownum ID
191 FROM
192 (SELECT fii.start_date START_DATE,
193 max(fact.time_snapshot_date_id) DAY
194 FROM '||l_period_type||' fii,
195 '||l_mv||' fact
196 WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
197 AND &BIS_PREVIOUS_ASOF_DATE
198 AND fact.time_snapshot_date_id (+) >= fii.start_date
199 AND fact.time_snapshot_date_id (+) <= fii.end_date
200 AND fact.time_snapshot_date_id (+) <= &BIS_PREVIOUS_ASOF_DATE
201 GROUP BY fii.start_date)
202 ORDER BY start_date DESC) prev
203 WHERE curr.id = prev.id(+)) dates,
204 '||l_mv||' fact'||l_prod_cat_from||'
205 WHERE fact.time_snapshot_date_id IN (dates.curr_day, dates.prev_day)
206 AND ((fact.late_promise_flag = 1'
207 ||l_flags_where
208 ||l_inv_org_where
209 ||l_prod_cat_where
210 ||l_prod_where
211 ||l_cust_where
212 ||') OR fact.inv_org_id IS NULL)
213 GROUP BY dates.start_date) s,
214 '||l_period_type||' fii
215 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
216 AND &BIS_CURRENT_ASOF_DATE
217 AND fii.start_date = s.start_date(+)
218 ORDER BY fii.start_date';
219
220 x_custom_sql := l_stmt;
221
222 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.View_By_Value;
223 l_custom_rec.attribute_value := 'TIME+'||l_period_type;
224 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
225 x_custom_output.extend;
226 x_custom_output(1) := l_custom_rec;
227
228 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
229 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
230 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.Bind_Type;
231 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.Integer_Bind;
232 x_custom_output.extend;
233 x_custom_output(2) := l_custom_rec;
234
235 l_custom_rec.attribute_name := ':ISC_CUST_FLAG';
236 l_custom_rec.attribute_value := to_char(l_cust_flag);
237 l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
238 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
239 x_custom_output.extend;
240 x_custom_output(3) := l_custom_rec;
241
242 END Get_Sql;
243
244 END ISC_DBI_PDUE_PRM_TREND_PKG;