[Home] [Help]
PACKAGE BODY: APPS.ISC_DBI_PAST_DUE_TREND_PKG
Source
1 PACKAGE BODY ISC_DBI_PAST_DUE_TREND_PKG AS
2 /* $Header: ISCRG75B.pls 120.1 2006/06/26 06:26:43 abhdixi 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_sql_stmt VARCHAR2(10000);
10 l_period_type VARCHAR2(10000);
11 l_inv_org VARCHAR2(10000);
12 l_inv_org_where VARCHAR2(10000);
13 l_item VARCHAR2(10000);
14 l_item_where VARCHAR2(10000);
15 l_inv_cat VARCHAR2(10000);
16 l_inv_cat_where VARCHAR2(10000);
17 l_customer VARCHAR2(10000);
18 l_customer_where VARCHAR2(10000);
19 l_item_cat_flag NUMBER; -- 0 for item, 1 for inv category
20 l_customer_flag NUMBER; -- 0 for customer level, 1 for no-customer level
21
22 l_custom_rec BIS_QUERY_ATTRIBUTES;
23
24 BEGIN
25
26 FOR i IN 1..p_param.COUNT
27 LOOP
28
29 IF(p_param(i).parameter_name = 'PERIOD_TYPE')
30 THEN l_period_type := p_param(i).parameter_value;
31 END IF;
32
33 IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
34 THEN l_inv_org := p_param(i).parameter_value;
35 END IF;
36
37 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT')
38 THEN l_inv_cat := p_param(i).parameter_value;
39 END IF;
40
41 IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
42 THEN l_item := p_param(i).parameter_value;
43 END IF;
44
45 IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
46 THEN l_customer := p_param(i).parameter_value;
47 END IF;
48
49 END LOOP;
50
51 IF(l_inv_org IS NULL 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 = mv.inv_org_id)
59 OR EXISTS
60 (SELECT 1
61 FROM mtl_parameters org
62 WHERE org.organization_id = mv.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 inv_org_id = &ORGANIZATION+ORGANIZATION';
69 END IF;
70
71 IF (l_customer IS NULL OR l_customer = 'All')
72 THEN l_customer_where :='';
73 l_customer_flag := 1; -- do not need customer id
74 ELSE l_customer_where :='
75 AND mv.customer_id IN (&CUSTOMER+FII_CUSTOMERS)';
76 l_customer_flag := 0; -- customer level
77 END IF;
78
79 IF(l_inv_cat IS NULL OR l_inv_cat = 'All')
80 THEN l_inv_cat_where := '';
81 ELSE l_inv_cat_where := '
82 AND item_category_id IN (&ITEM+ENI_ITEM_INV_CAT)';
83 END IF;
84
85 IF(l_item IS NULL OR l_item = 'All')
86 THEN l_item_where := '';
87 ELSE l_item_where := '
88 AND item_id IN (&ITEM+ENI_ITEM_ORG)';
89 END IF;
90
91 IF((l_inv_cat IS NULL OR l_inv_cat = 'All') AND (l_item IS NULL OR l_item = 'All'))
92 THEN l_item_cat_flag := 3; -- no grouping on item dimension
93 ELSE
94 IF (l_item IS NULL OR l_item = 'All')
95 THEN l_item_cat_flag := 1; -- inventory category
96 ELSE l_item_cat_flag := 0; -- item
97 END IF;
98 END IF;
99
100 l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
101 x_custom_output := bis_query_attributes_tbl();
102
103 l_sql_stmt := '
104 SELECT fii.name VIEWBY,
105 fii.name ISC_ATTRIBUTE_2,
106 s.prev_pdue ISC_MEASURE_2,
107 s.curr_pdue ISC_MEASURE_1,
108 (s.curr_pdue - s.prev_pdue)
109 / decode( s.prev_pdue, 0, NULL,
110 abs(s.prev_pdue)) * 100
111 ISC_MEASURE_4,
112 null ISC_MEASURE_3, -- obsolete from DBI 5.0
113 null ISC_MEASURE_5, -- obsolete from DBI 5.0
114 null CURRENCY -- obsolete from DBI 5.0
115 FROM (SELECT dates.start_date START_DATE,
116 sum(decode(mv.time_snapshot_date_id, dates.curr_day,
117 mv.pdue_line_cnt, NULL)) CURR_PDUE,
118 sum(decode(mv.time_snapshot_date_id, dates.prev_day,
119 mv.pdue_line_cnt, NULL)) PREV_PDUE
120 FROM (SELECT curr.start_date START_DATE,
121 curr.day CURR_DAY,
122 prev.day PREV_DAY
123 FROM (SELECT start_date,
124 day,
125 rownum ID
126 FROM
127 (SELECT fii.start_date START_DATE,
128 max(mv.time_snapshot_date_id) DAY
129 FROM '||l_period_type||' fii,
130 ISC_DBI_FM_0006_MV mv
131 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
132 AND &BIS_CURRENT_ASOF_DATE
133 AND mv.time_snapshot_date_id (+) >= fii.start_date
134 AND mv.time_snapshot_date_id (+) <= fii.end_date
135 AND mv.time_snapshot_date_id (+) <= &BIS_CURRENT_ASOF_DATE
136 GROUP BY fii.start_date)
137 ORDER BY start_date DESC) curr,
138 (SELECT start_date,
139 day,
140 rownum ID
141 FROM
142 (SELECT fii.start_date START_DATE,
143 max(mv.time_snapshot_date_id) DAY
144 FROM '||l_period_type||' fii,
145 ISC_DBI_FM_0006_MV mv
146 WHERE fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
147 AND &BIS_PREVIOUS_ASOF_DATE
148 AND mv.time_snapshot_date_id (+) >= fii.start_date
149 AND mv.time_snapshot_date_id (+) <= fii.end_date
150 AND mv.time_snapshot_date_id (+) <= &BIS_PREVIOUS_ASOF_DATE
151 GROUP BY fii.start_date)
152 ORDER BY start_date DESC) prev
153 WHERE curr.id = prev.id(+)) dates,
154 ISC_DBI_FM_0006_MV mv
155 WHERE mv.time_snapshot_date_id IN (dates.curr_day, dates.prev_day)
156 AND ((mv.item_cat_flag = :ISC_ITEM_CAT_FLAG
157 AND mv.customer_flag = :ISC_CUSTOMER_FLAG'
158 ||l_inv_org_where
159 ||l_inv_cat_where
160 ||l_item_where
161 ||l_customer_where
162 ||')
163 OR mv.inv_org_id IS NULL) -- snapshot taken but no data
164 GROUP BY dates.start_date) s,
165 '||l_period_type||' fii
166 WHERE fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
167 AND &BIS_CURRENT_ASOF_DATE
168 AND fii.start_date = s.start_date(+)
169 ORDER BY fii.start_date';
170
171 x_custom_sql := l_sql_stmt;
172
173 l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.View_By_Value;
174 l_custom_rec.attribute_value := 'TIME+'||l_period_type;
175 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
176 x_custom_output.extend;
177 x_custom_output(1) := l_custom_rec;
178
179 l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
180 l_custom_rec.attribute_value := to_char(l_item_cat_flag);
181 l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.Bind_Type;
182 l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.Integer_Bind;
183 x_custom_output.extend;
184 x_custom_output(2) := l_custom_rec;
185
186 l_custom_rec.attribute_name := ':ISC_CUSTOMER_FLAG';
187 l_custom_rec.attribute_value := to_char(l_customer_flag);
188 l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
189 l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
190 x_custom_output.extend;
191 x_custom_output(3) := l_custom_rec;
192
193 END Get_Sql;
194
195 END ISC_DBI_PAST_DUE_TREND_PKG ;
196