eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.ISC_DBI_BACKORDER_TREND_PKG

Source


1 PACKAGE BODY ISC_DBI_BACKORDER_TREND_PKG AS
2 /* $Header: ISCRGAWB.pls 120.2 2006/06/26 06:31:37 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 
20   l_custom_rec			BIS_QUERY_ATTRIBUTES;
21   l_att_2                VARCHAR2(255);
22 BEGIN
23 
24   FOR i IN 1..p_param.COUNT
25   LOOP
26 
27     IF(p_param(i).parameter_name = 'PERIOD_TYPE')
28       THEN l_period_type := p_param(i).parameter_value;
29     END IF;
30 
31     IF(p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
32       THEN l_inv_org := p_param(i).parameter_value;
33     END IF;
34 
35     IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_INV_CAT')
36       THEN l_inv_cat := p_param(i).parameter_value;
37     END IF;
38 
39     IF(p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
40       THEN l_item := p_param(i).parameter_value;
41     END IF;
42 
43     IF(p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
44       THEN l_customer :=  p_param(i).parameter_value;
45     END IF;
46 
47   END LOOP;
48 
49   IF(l_inv_org IS NULL OR l_inv_org = 'All')
50     THEN l_inv_org_where := '
51 	AND (EXISTS
52 		(SELECT 1
53 		FROM org_access o
54 		WHERE o.responsibility_id = fnd_global.resp_id
55 		AND o.resp_application_id = fnd_global.resp_appl_id
56 		AND o.organization_id = mv.inv_org_id)
57 	     OR EXISTS
58 		(SELECT 1
59 		FROM mtl_parameters org
60 		WHERE org.organization_id = mv.inv_org_id
61 		AND NOT EXISTS
62 			(SELECT 1
63 			FROM org_access ora
64 			WHERE org.organization_id = ora.organization_id)))';
65     ELSE l_inv_org_where := '
66 	    AND inv_org_id = &ORGANIZATION+ORGANIZATION';
67   END IF;
68 
69   IF (l_customer IS NULL OR l_customer = 'All')
70     THEN l_customer_where :='';
71     ELSE l_customer_where :='
72 	    AND mv.customer_id IN (&CUSTOMER+FII_CUSTOMERS)';
73   END IF;
74 
75   IF(l_inv_cat IS NULL OR l_inv_cat = 'All')
76     THEN l_inv_cat_where := '';
77     ELSE l_inv_cat_where := '
78 	    AND item_category_id IN (&ITEM+ENI_ITEM_INV_CAT)';
79   END IF;
80 
81   IF(l_item IS NULL OR l_item = 'All')
82     THEN l_item_where := '';
83     ELSE l_item_where := '
84 	    AND item_id IN (&ITEM+ENI_ITEM_ORG)';
85   END IF;
86 
87   l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
88   x_custom_output := bis_query_attributes_tbl();
89 
90   If l_period_type = 'FII_TIME_WEEK' then
91      l_att_2 := '''AS_OF_DATE=''||'|| 'to_char(fii1.end_date,''DD/MM/YYYY'')' || '||''&pFunctionName=ISC_DBI_BACKORDER_TREND&TIME+FII_TIME_DAY=TIME+FII_TIME_DAY&pParameters=pParamIds@Y''';
92   else
93      l_att_2 := 'NULL ';
94   end if;
95 
96   If l_period_type = 'FII_TIME_DAY' then
97 	l_sql_stmt := 'SELECT	fii1.start_date  VIEWBY,';
98 	l_sql_stmt := l_sql_stmt || l_att_2 ||  ' ISC_ATTRIBUTE_2,
99 	s.prev_bkord_lines	ISC_MEASURE_2, -- Backordered Lines - prior
100 	s.curr_bkord_lines	ISC_MEASURE_1, -- Backordered Lines
101 	(s.curr_bkord_lines - s.prev_bkord_lines)
102 	  / decode( s.prev_bkord_lines, 0, NULL,
103 		    abs(s.prev_bkord_lines)) * 100
104 				ISC_MEASURE_3, -- (Backordered Lines) Change
105 	s.prev_bkord_items	ISC_MEASURE_5, -- Backordered Items - prior
106 	s.curr_bkord_items	ISC_MEASURE_4, -- Backordered Items
107 	(s.curr_bkord_items - s.prev_bkord_items)
108 	  / decode( s.prev_bkord_items, 0, NULL,
109 		    abs(s.prev_bkord_items)) * 100
110 				ISC_MEASURE_6 -- (Backordered Items) Change
111    FROM	(SELECT	dates.start_date					START_DATE,
112 		sum(decode(mv.time_snapshot_date_id, dates.curr_day,
113 			   mv.backorder_line_cnt, NULL))		CURR_BKORD_LINES,
114 		sum(decode(mv.time_snapshot_date_id, dates.prev_day,
115 			   mv.backorder_line_cnt, NULL))		PREV_BKORD_LINES,
116 		count(distinct(decode(mv.time_snapshot_date_id, dates.curr_day,
117 				      decode(mv.item_id,''-'',null,mv.item_id),
118 				      null)))				CURR_BKORD_ITEMS,
119 		count(distinct(decode(mv.time_snapshot_date_id, dates.prev_day,
120 				      decode(mv.item_id,''-'',null,mv.item_id),
121 				      null)))				PREV_BKORD_ITEMS
122 	   FROM	(SELECT	curr.start_date	START_DATE,
123 			curr.day	CURR_DAY,
124 			prev.day	PREV_DAY
125 		   FROM	(SELECT start_date,
126 				day,
127 				rownum	ID
128 			   FROM
129 			(SELECT	fii1.start_date				START_DATE,
130 				     fii1.start_date			DAY
131 			   FROM	'||l_period_type||'		fii1
132 			  WHERE	fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
133 						   AND &BIS_CURRENT_ASOF_DATE
134 			   )
135 			ORDER BY start_date DESC)		curr,
136 			(SELECT start_date,
137 				day,
138 				rownum	ID
139 			   FROM
140 			(SELECT	fii1.start_date				START_DATE,
141 				     fii1.start_date			DAY
142 			   FROM	'||l_period_type||'		fii1
143 			  WHERE	fii1.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
144 						   AND &BIS_PREVIOUS_ASOF_DATE)
145 			ORDER BY start_date DESC)		prev
146 		  WHERE	curr.id = prev.id(+))			dates,
147 		ISC_DBI_FM_0007_MV 				mv
148 	  WHERE	mv.time_snapshot_date_id IN (dates.curr_day, dates.prev_day)
149 	    AND ((1=1'
150 		||l_inv_org_where
151 		||l_inv_cat_where
152 		||l_item_where
153 		||l_customer_where
154 		||')
155 	     OR	mv.inv_org_id IS NULL) -- snapshot taken but no data
156 	GROUP BY dates.start_date)	s,
157 	'||l_period_type||'		fii1
158      WHERE	fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
159 			   AND &BIS_CURRENT_ASOF_DATE
160      AND	fii1.start_date = s.start_date(+)
161      ORDER BY fii1.start_date';
162   else
163 	l_sql_stmt := 'SELECT	fii1.NAME   VIEWBY,';
164 	l_sql_stmt := l_sql_stmt || l_att_2 ||  ' ISC_ATTRIBUTE_2,
165 	s.prev_bkord_lines	ISC_MEASURE_2, -- Backordered Lines - prior
166 	s.curr_bkord_lines	ISC_MEASURE_1, -- Backordered Lines
167 	(s.curr_bkord_lines - s.prev_bkord_lines)
168 	  / decode( s.prev_bkord_lines, 0, NULL,
169 		    abs(s.prev_bkord_lines)) * 100
170 				ISC_MEASURE_3, -- (Backordered Lines) Change
171 	s.prev_bkord_items	ISC_MEASURE_5, -- Backordered Items - prior
172 	s.curr_bkord_items	ISC_MEASURE_4, -- Backordered Items
173 	(s.curr_bkord_items - s.prev_bkord_items)
174 	  / decode( s.prev_bkord_items, 0, NULL,
175 		    abs(s.prev_bkord_items)) * 100
176 				ISC_MEASURE_6 -- (Backordered Items) Change
177    FROM	(SELECT	dates.start_date					START_DATE,
178 		sum(decode(mv.time_snapshot_date_id, dates.curr_day,
179 			   mv.backorder_line_cnt, NULL))		CURR_BKORD_LINES,
180 		sum(decode(mv.time_snapshot_date_id, dates.prev_day,
181 			   mv.backorder_line_cnt, NULL))		PREV_BKORD_LINES,
182 		count(distinct(decode(mv.time_snapshot_date_id, dates.curr_day,
183 				      decode(mv.item_id,''-'',null,mv.item_id),
184 				      null)))				CURR_BKORD_ITEMS,
185 		count(distinct(decode(mv.time_snapshot_date_id, dates.prev_day,
186 				      decode(mv.item_id,''-'',null,mv.item_id),
187 				      null)))				PREV_BKORD_ITEMS
188 	   FROM	(SELECT	curr.start_date	START_DATE,
189 			curr.day	CURR_DAY,
190 			prev.day	PREV_DAY
191 		   FROM	(SELECT start_date,
192 				day,
193 				rownum	ID
194 			   FROM
195 			(SELECT	fii1.start_date				START_DATE,
196 				max(mv.time_snapshot_date_id)		DAY
197 			   FROM	'||l_period_type||'		fii1,
198 				ISC_DBI_FM_0007_MV		mv
199 			  WHERE	fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
200 						   AND &BIS_CURRENT_ASOF_DATE
201 			    AND	mv.time_snapshot_date_id (+) >= fii1.start_date
202 			    AND	mv.time_snapshot_date_id (+) <= fii1.end_date
203 			    AND	mv.time_snapshot_date_id (+) <= &BIS_CURRENT_ASOF_DATE
204 			GROUP BY fii1.start_date)
205 			ORDER BY start_date DESC)		curr,
206 			(SELECT start_date,
207 				day,
208 				rownum	ID
209 			   FROM
210 			(SELECT	fii1.start_date				START_DATE,
211 				max(mv.time_snapshot_date_id)		DAY
212 			   FROM	'||l_period_type||'		fii1,
213 				ISC_DBI_FM_0007_MV		mv
214 			  WHERE	fii1.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
215 						   AND &BIS_PREVIOUS_ASOF_DATE
216 			    AND	mv.time_snapshot_date_id (+) >= fii1.start_date
217 			    AND	mv.time_snapshot_date_id (+) <= fii1.end_date
218 			    AND	mv.time_snapshot_date_id (+) <= &BIS_PREVIOUS_ASOF_DATE
219 			GROUP BY fii1.start_date)
220 			ORDER BY start_date DESC)		prev
221 		  WHERE	curr.id = prev.id(+))			dates,
222 		ISC_DBI_FM_0007_MV 				mv
223 	  WHERE	mv.time_snapshot_date_id IN (dates.curr_day, dates.prev_day)
224 	    AND ((1=1'
225 		||l_inv_org_where
226 		||l_inv_cat_where
227 		||l_item_where
228 		||l_customer_where
229 		||')
230 	     OR	mv.inv_org_id IS NULL) -- snapshot taken but no data
231 	GROUP BY dates.start_date)	s,
232 	'||l_period_type||'		fii1
233      WHERE	fii1.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
234 			   AND &BIS_CURRENT_ASOF_DATE
235      AND	fii1.start_date = s.start_date(+)
236      ORDER BY fii1.start_date';
237 end if;
238 
239   x_custom_sql := l_sql_stmt;
240 
241   l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.View_By_Value;
242   l_custom_rec.attribute_value := 'TIME+'||l_period_type;
243   l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
244   x_custom_output.extend;
245   x_custom_output(1) := l_custom_rec;
246 
247 END Get_Sql;
248 
249 END ISC_DBI_BACKORDER_TREND_PKG ;
250