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_BACKLOG_PDUE_TREND_PKG

Source


1 PACKAGE BODY ISC_DBI_BACKLOG_PDUE_TREND_PKG AS
2 /* $Header: ISCRGA3B.pls 120.0 2005/05/25 17:45:07 appldev noship $ */
3 
4 
5 
6 PROCEDURE Get_Sql (	p_param		IN		BIS_PMV_PAGE_PARAMETER_TBL,
7 			x_custom_sql	OUT NOCOPY	VARCHAR2,
8 			x_custom_output	OUT NOCOPY	BIS_QUERY_ATTRIBUTES_TBL) IS
9 
10   l_stmt			VARCHAR2(32000);
11   l_mv1				VARCHAR2(100);
12   l_mv2				VARCHAR2(100);
13   l_mv_book			VARCHAR2(100);
14   l_mv_fulf			VARCHAR2(100);
15   l_flags_where			VARCHAR2(1000);
16   l_flags_where2		VARCHAR2(1000);
17   l_period_type			VARCHAR2(10000);
18   l_inv_org			VARCHAR2(10000);
19   l_inv_org_where		VARCHAR2(10000);
20   l_prod			VARCHAR2(10000);
21   l_prod_where			VARCHAR2(10000);
22   l_prod_cat			VARCHAR2(10000);
23   l_prod_cat_from		VARCHAR2(10000);
24   l_prod_cat_where		VARCHAR2(10000);
25   l_cust			VARCHAR2(10000);
26   l_cust_where			VARCHAR2(10000);
27   l_curr			VARCHAR2(10000);
28   l_curr_g			VARCHAR2(15);
29   l_curr_g1			VARCHAR2(15);
30   l_curr_suffix			VARCHAR2(120);
31   l_item_cat_flag		NUMBER;
32   l_cust_flag			NUMBER;
33 
34   l_custom_rec			BIS_QUERY_ATTRIBUTES;
35 
36 BEGIN
37 
38   l_curr_g			:= '''FII_GLOBAL1''';
39   l_curr_g1			:= '''FII_GLOBAL2''';
40 
41   FOR i IN 1..p_param.COUNT
42   LOOP
43     IF (p_param(i).parameter_name = 'PERIOD_TYPE')
44       THEN l_period_type := p_param(i).parameter_value;
45     END IF;
46 
47     IF (p_param(i).parameter_name = 'CURRENCY+FII_CURRENCIES')
48       THEN l_curr := p_param(i).parameter_id;
49     END IF;
50 
51     IF (p_param(i).parameter_name = 'ORGANIZATION+ORGANIZATION')
52       THEN l_inv_org := p_param(i).parameter_value;
53     END IF;
54 
55     IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_VBH_CAT')
56       THEN l_prod_cat := p_param(i).parameter_value;
57     END IF;
58 
59     IF (p_param(i).parameter_name = 'ITEM+ENI_ITEM_ORG')
60       THEN l_prod := p_param(i).parameter_value;
61     END IF;
62 
63     IF (p_param(i).parameter_name = 'CUSTOMER+FII_CUSTOMERS')
64       THEN l_cust := p_param(i).parameter_value;
65     END IF;
66   END LOOP;
67 
68   IF (l_curr = l_curr_g)
69     THEN l_curr_suffix := 'g';
70   ELSIF (l_curr = l_curr_g1)
71     THEN l_curr_suffix :='g1';
72     ELSE l_curr_suffix := 'f';
73   END IF;
74 
75   IF (l_inv_org IS NULL OR l_inv_org = 'All')
76     THEN l_inv_org_where := '
77 	WHERE (EXISTS
78 		(SELECT 1
79 		FROM org_access o
80 		WHERE o.responsibility_id = fnd_global.resp_id
81 		AND o.resp_application_id = fnd_global.resp_appl_id
82 		AND o.organization_id = inv_org)
83 	OR EXISTS
84 		(SELECT 1
85 		FROM mtl_parameters org
86 		WHERE org.organization_id = inv_org
87 		AND NOT EXISTS
88 			(SELECT 1
89 			FROM org_access ora
90 			WHERE org.organization_id = ora.organization_id)))';
91     ELSE l_inv_org_where := '
92 	WHERE inv_org = &ORGANIZATION+ORGANIZATION';
93   END IF;
94 
95   IF (l_prod_cat IS NULL OR l_prod_cat = 'All')
96     THEN
97       l_prod_cat_from := '';
98       l_prod_cat_where := '';
99     ELSE
100       IF (l_prod IS NULL OR l_prod = 'All')
101         THEN
102 	  l_prod_cat_from := ',
103 		ENI_DENORM_HIERARCHIES		eni_cat,
104 		MTL_DEFAULT_CATEGORY_SETS	mdcs';
105 	  l_prod_cat_where := '
106 	    AND fact.item_category_id = eni_cat.child_id
107 	    AND eni_cat.parent_id IN (&ITEM+ENI_ITEM_VBH_CAT)
108 	    AND	eni_cat.dbi_flag = ''Y''
109 	    AND eni_cat.object_type = ''CATEGORY_SET''
110 	    AND eni_cat.object_id = mdcs.category_set_id
111 	    AND	mdcs.functional_area_id = 11';
112         ELSE
113 	  l_prod_cat_from := '';
114 	  l_prod_cat_where := '';
115       END IF;
116   END IF;
117 
118   IF (l_prod IS NULL OR l_prod = 'All')
119     THEN l_prod_where := '';
120     ELSE l_prod_where := '
121 	    AND fact.item_id IN (&ITEM+ENI_ITEM_ORG)';
122   END IF;
123 
124   IF (l_cust IS NULL OR l_cust = 'All')
125     THEN
126       l_cust_where := '';
127       l_cust_flag := 1;
128     ELSE
129       l_cust_where := '
130 	    AND fact.customer_id in (&CUSTOMER+FII_CUSTOMERS)';
131       l_cust_flag := 0;
132   END IF;
133 
134   IF (l_prod IS NULL OR l_prod = 'All')
135     THEN
136       IF (l_prod_cat IS NULL OR l_prod_cat = 'All')
137         THEN l_item_cat_flag := 3; -- category
138         ELSE l_item_cat_flag := 1; -- all
139       END IF;
140     ELSE
141       l_item_cat_flag := 0; -- product
142   END IF;
143 
144   l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
145   x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
146 
147   IF ((l_prod IS NULL OR l_prod = 'All') AND
148       (l_cust IS NULL OR l_cust = 'All'))
149     THEN
150         IF (l_prod_cat IS NULL OR l_prod_cat = '' OR l_prod_cat = 'All')
151 	  THEN
152 	    l_prod_cat_from := '';
153 	    l_prod_cat_where := '
154 	    	    AND	fact.top_node_flag = ''Y''';
155 	  ELSE
156 	    l_prod_cat_from := '';
157 	    l_prod_cat_where := '
158 	    	    AND fact.parent_id = &ITEM+ENI_ITEM_VBH_CAT';
159         END IF;
160 	l_mv1 := 'ISC_DBI_CFM_001_MV';
161 	l_mv_book := 'ISC_DBI_CFM_009_MV';
162 	l_mv_fulf := 'ISC_DBI_CFM_011_MV';
163 	l_mv2 := 'ISC_DBI_CFM_012_MV';
164 	l_flags_where := '';
165 	l_flags_where2 := '
166 	    AND	fact.inv_org_flag = 0';
167     ELSE
168 	l_mv1 := 'ISC_DBI_CFM_010_MV';
169 	l_mv_book := 'ISC_DBI_CFM_000_MV';
170 	l_mv_fulf := 'ISC_DBI_CFM_002_MV';
171 	l_mv2 := 'ISC_DBI_CFM_008_MV';
172 	l_flags_where := '
173 	    AND	fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
174 	    AND fact.customer_flag = :ISC_CUST_FLAG';
175 	l_flags_where2 := '
176 	    AND	fact.item_cat_flag = :ISC_ITEM_CAT_FLAG
177 	    AND fact.customer_flag = :ISC_CUST_FLAG';
178   END IF;
179 
180   l_stmt := '
181  SELECT	fii.name					VIEWBY,
182 	s.prev_bklg_value				ISC_MEASURE_1, -- bklg prior
183 	s.curr_bklg_value				ISC_MEASURE_2, -- bklg
184 	(s.curr_bklg_value - s.prev_bklg_value)
185 	  / decode(s.prev_bklg_value, 0, NULL,
186 		   abs(s.prev_bklg_value)) * 100	ISC_MEASURE_3, -- bklg change
187 	s.prev_pdue_value				ISC_MEASURE_4, -- pdue prior
188 	s.curr_pdue_value				ISC_MEASURE_5, -- pdue
189 	(s.curr_pdue_value - s.prev_pdue_value)
190 	  / decode(s.prev_pdue_value, 0, NULL,
191 		   abs(s.prev_pdue_value)) * 100	ISC_MEASURE_6  -- pdue change
192    FROM	(SELECT	start_date			START_DATE,
193 		sum(c_y_bklg) + sum(c_book_ytd)
194 		  - sum(c_fulf_ytd)		CURR_BKLG_VALUE,
195 		sum(p_y_bklg) + sum(p_book_ytd)
196 		  - sum(p_fulf_ytd)		PREV_BKLG_VALUE,
197 		sum(curr_pdue_value)		CURR_PDUE_VALUE,
198 		sum(prev_pdue_value)		PREV_PDUE_VALUE
199 	   FROM /* Compute year backlog balance */
200 	(SELECT	dates.start_date		START_DATE,
201 		fact.inv_org_id			INV_ORG,
202 		decode(dates.period, ''C'',
203 		       fact.bklg_amt_'||l_curr_suffix||', 0)	C_Y_BKLG,
204 		decode(dates.period, ''P'',
205 		       fact.bklg_amt_'||l_curr_suffix||', 0)	P_Y_BKLG,
206 		0				C_BOOK_YTD,
207 		0				P_BOOK_YTD,
208 		0				C_FULF_YTD,
209 		0				P_FULF_YTD,
210 		NULL				CURR_PDUE_VALUE,
211 		NULL				PREV_PDUE_VALUE
212 	   FROM	(SELECT	fii.start_date					START_DATE,
213 			''C''						PERIOD,
214 			least(fii.end_date, &BIS_CURRENT_ASOF_DATE)	REPORT_DATE
215 		   FROM	'||l_period_type||'	fii
216 		  WHERE	fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
217 					   AND &BIS_CURRENT_ASOF_DATE
218 		UNION ALL
219 		 SELECT	p2.start_date					START_DATE,
220 			''P''						PERIOD,
221 			p1.report_date					REPORT_DATE
222 		   FROM	(SELECT	least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE)	REPORT_DATE,
223 				rownum						ID
224 			   FROM	'||l_period_type||'	fii
225 			  WHERE	fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
226 						   AND &BIS_PREVIOUS_ASOF_DATE
227 			  ORDER BY fii.start_date DESC) p1,
228 			(SELECT	fii.start_date					START_DATE,
229 				rownum						ID
230 			   FROM	'||l_period_type||'	fii
231 			  WHERE	fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
232 						   AND &BIS_CURRENT_ASOF_DATE
233 			  ORDER BY fii.start_date DESC) p2
234 		  WHERE	p1.id(+) = p2.id)			dates,
235 		'||l_mv1||' 				fact,
236 		FII_TIME_DAY					day'||l_prod_cat_from||'
237 	  WHERE	day.report_date = dates.report_date
238 	    AND day.ent_year_start_date = fact.time_snapshot_date_id'
239 	    	||l_flags_where
240 		||l_prod_cat_where
241 		||l_prod_where
242 		||l_cust_where||'
243 	UNION ALL /* Computer YTD net Booking */
244 	 SELECT	dates.start_date		START_DATE,
245 		fact.inv_org_id			INV_ORG,
246 		0				C_Y_BKLG,
247 		0				P_Y_BKLG,
248 		decode(dates.period, ''C'',
249 		       fact.booked_amt2_'||l_curr_suffix||', 0)	C_BOOK_YTD,
250 		decode(dates.period, ''P'',
251 		       fact.booked_amt2_'||l_curr_suffix||', 0)	P_BOOK_YTD,
252 		0				C_FULF_YTD,
253 		0				P_FULF_YTD,
254 		NULL				CURR_PDUE_VALUE,
255 		NULL				PREV_PDUE_VALUE
256 	   FROM	(SELECT	fii.start_date					START_DATE,
257 			''C''						PERIOD,
258 			least(fii.end_date, &BIS_CURRENT_ASOF_DATE)	REPORT_DATE
259 		   FROM	'||l_period_type||'	fii
260 		  WHERE	fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
261 					   AND &BIS_CURRENT_ASOF_DATE
262 		UNION ALL
263 		 SELECT	p2.start_date					START_DATE,
264 			''P''						PERIOD,
265 			p1.report_date					REPORT_DATE
266 		   FROM	(SELECT	least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE)	REPORT_DATE,
267 				rownum						ID
268 			   FROM	'||l_period_type||'	fii
269 			  WHERE	fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
270 						   AND &BIS_PREVIOUS_ASOF_DATE
271 			  ORDER BY fii.start_date DESC) p1,
272 			(SELECT	fii.start_date					START_DATE,
273 				rownum						ID
274 			   FROM	'||l_period_type||'	fii
275 			  WHERE	fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
276 						   AND &BIS_CURRENT_ASOF_DATE
277 			  ORDER BY fii.start_date DESC) p2
278 		  WHERE	p1.id(+) = p2.id)			dates,
279 		'||l_mv_book||' 			fact,
280 		FII_TIME_RPT_STRUCT_V				cal'||l_prod_cat_from||'
281 	  WHERE	cal.report_date = dates.report_date
282 	    AND fact.time_id = cal.time_id
283 	    AND fact.period_type_id = cal.period_type_id
284 	    AND bitand(cal.record_type_id, 119) = cal.record_type_id'
285 	    	||l_flags_where2
286 		||l_prod_cat_where
287 		||l_prod_where
288 		||l_cust_where||'
289 	UNION ALL /* Computer YTD net fulfillment */
290 	 SELECT	dates.start_date		START_DATE,
291 		fact.inv_org_id			INV_ORG,
292 		0				C_Y_BKLG,
293 		0				P_Y_BKLG,
294 		0				C_BOOK_YTD,
295 		0				P_BOOK_YTD,
296 		decode(dates.period, ''C'',
297 		       fact.booked_amt_'||l_curr_suffix||', 0)	C_FULF_YTD,
298 		decode(dates.period, ''P'',
299 		       fact.booked_amt_'||l_curr_suffix||', 0)	P_FULF_YTD,
300 		NULL				CURR_PDUE_VALUE,
301 		NULL				PREV_PDUE_VALUE
302 	   FROM	(SELECT	fii.start_date					START_DATE,
303 			''C''						PERIOD,
304 			least(fii.end_date, &BIS_CURRENT_ASOF_DATE)	REPORT_DATE
305 		   FROM	'||l_period_type||'	fii
306 		  WHERE	fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
307 					   AND &BIS_CURRENT_ASOF_DATE
308 		UNION ALL
309 		 SELECT	p2.start_date					START_DATE,
310 			''P''						PERIOD,
311 			p1.report_date					REPORT_DATE
312 		   FROM	(SELECT	least(fii.end_date, &BIS_PREVIOUS_ASOF_DATE)	REPORT_DATE,
313 				rownum						ID
314 			   FROM	'||l_period_type||'	fii
315 			  WHERE	fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
316 						   AND &BIS_PREVIOUS_ASOF_DATE
317 			  ORDER BY fii.start_date DESC) p1,
318 			(SELECT	fii.start_date					START_DATE,
319 				rownum						ID
320 			   FROM	'||l_period_type||'	fii
321 			  WHERE	fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
322 						   AND &BIS_CURRENT_ASOF_DATE
323 			  ORDER BY fii.start_date DESC) p2
324 		  WHERE	p1.id(+) = p2.id)			dates,
325 		'||l_mv_fulf||' 			fact,
326 		FII_TIME_RPT_STRUCT_V				cal'||l_prod_cat_from||'
327 	  WHERE	cal.report_date = dates.report_date
328 	    AND fact.time_id = cal.time_id
329 	    AND fact.period_type_id = cal.period_type_id
330 	    AND bitand(cal.record_type_id, 119) = cal.record_type_id'
331 		||l_flags_where2
332 		||l_prod_cat_where
333 		||l_prod_where
334 		||l_cust_where||'
335 	UNION ALL
336 	 SELECT	dates.start_date		START_DATE,
337 		fact.inv_org_id			INV_ORG,
338 		0				C_Y_BKLG,
339 		0				P_Y_BKLG,
340 		0				C_BOOK_YTD,
341 		0				P_BOOK_YTD,
342 		0				C_FULF_YTD,
343 		0				P_FULF_YTD,
344 		decode(fact.time_snapshot_date_id, dates.curr_day,
345 		       fact.pdue_amt_'||l_curr_suffix||', NULL)	CURR_PDUE_VALUE,
346 		decode(fact.time_snapshot_date_id, dates.prev_day,
347 		       fact.pdue_amt_'||l_curr_suffix||', NULL)	PREV_PDUE_VALUE
348 	   FROM	(SELECT	curr.start_date	START_DATE,
349 			curr.day	CURR_DAY,
350 			prev.day	PREV_DAY
351 		   FROM	(SELECT start_date,
352 				day,
353 				rownum	ID
354 			   FROM
355 			(SELECT	fii.start_date					START_DATE,
356 				max(fact.time_snapshot_date_id)			DAY
357 			   FROM	'||l_period_type||'		fii,
358 				'||l_mv2||'		fact
359 			  WHERE	fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
360 						   AND &BIS_CURRENT_ASOF_DATE
361 			    AND	fact.time_snapshot_date_id (+) >= fii.start_date
362 			    AND	fact.time_snapshot_date_id (+) <= fii.end_date
363 			    AND	fact.time_snapshot_date_id (+) <= &BIS_CURRENT_ASOF_DATE
364 			GROUP BY fii.start_date)
365 			ORDER BY start_date DESC)		curr,
366 			(SELECT start_date,
367 				day,
368 				rownum	ID
369 			   FROM
370 			(SELECT	fii.start_date					START_DATE,
371 				max(fact.time_snapshot_date_id)			DAY
372 			   FROM	'||l_period_type||'		fii,
373 				'||l_mv2||'		fact
374 			  WHERE	fii.start_date BETWEEN &BIS_PREVIOUS_REPORT_START_DATE
375 						   AND &BIS_PREVIOUS_ASOF_DATE
376 			    AND	fact.time_snapshot_date_id (+) >= fii.start_date
377 			    AND	fact.time_snapshot_date_id (+) <= fii.end_date
378 			    AND	fact.time_snapshot_date_id (+) <= &BIS_PREVIOUS_ASOF_DATE
379 			GROUP BY fii.start_date)
385 	    	||l_flags_where
380 			ORDER BY start_date DESC)		prev
381 		  WHERE	curr.id = prev.id(+))			dates,
382 		'||l_mv2||' 				fact'||l_prod_cat_from||'
383 	  WHERE	fact.time_snapshot_date_id IN (dates.curr_day, dates.prev_day)
384 	    AND	fact.late_schedule_flag = 1'
386 		||l_prod_cat_where
387 		||l_prod_where
388 		||l_cust_where||')'
389 	||l_inv_org_where||'
390 	GROUP BY start_date)		s,
391 	'||l_period_type||'		fii
392   WHERE	fii.start_date BETWEEN &BIS_CURRENT_REPORT_START_DATE
393 			   AND &BIS_CURRENT_ASOF_DATE
394     AND	fii.start_date = s.start_date(+)
395 ORDER BY fii.start_date';
396 
397   x_custom_sql := l_stmt;
398 
399   l_custom_rec.attribute_name := BIS_PMV_PARAMETERS_PUB.View_By_Value;
400   l_custom_rec.attribute_value := 'TIME+'||l_period_type;
401   l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.View_By_Type;
402   x_custom_output.extend;
403   x_custom_output(1) := l_custom_rec;
404 
405   l_custom_rec.attribute_name := ':ISC_ITEM_CAT_FLAG';
406   l_custom_rec.attribute_value := to_char(l_item_cat_flag);
407   l_custom_rec.attribute_type := BIS_PMV_PARAMETERS_PUB.Bind_Type;
408   l_custom_rec.attribute_data_type := BIS_PMV_PARAMETERS_PUB.Integer_Bind;
409   x_custom_output.extend;
410   x_custom_output(2) := l_custom_rec;
411 
412   l_custom_rec.attribute_name := ':ISC_CUST_FLAG';
413   l_custom_rec.attribute_value := to_char(l_cust_flag);
414   l_custom_Rec.attribute_type := bis_pmv_parameters_pub.bind_type;
415   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.integer_bind;
416   x_custom_output.extend;
417   x_custom_output(3) := l_custom_rec;
418 
419 END Get_Sql;
420 
421 END ISC_DBI_BACKLOG_PDUE_TREND_PKG;