PACKAGE BODY: APPS.ISC_DBI_PLAN_DETAILS_PKG

Source

1 PACKAGE BODY ISC_DBI_PLAN_DETAILS_PKG AS
2 /* $Header: ISCRGAKB.pls 115.2 2004/01/30 07:56:09 chu 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_plan			VARCHAR2(10000);
11   l_custom_rec			BIS_QUERY_ATTRIBUTES;
12 
13 BEGIN
14 
15   FOR i IN 1..p_param.COUNT
16   LOOP
17     IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT')
18       THEN l_plan := p_param(i).parameter_value;
19     END IF;
20   END LOOP;
21 
22   l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
23   x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
24 
25   IF (l_plan IS NULL)
26     THEN l_stmt := '
27 SELECT	0	ISC_ATTRIBUTE_1,
28 	0	ISC_ATTRIBUTE_2,
29 	0	ISC_ATTRIBUTE_3,
30 	0	ISC_ATTRIBUTE_4,
31 	0 	ISC_ATTRIBUTE_5,
32 	0	ISC_ATTRIBUTE_6,
33 	0 	ISC_MEASURE_1,
34 	0	ISC_MEASURE_2
35   FROM	dual
36  WHERE	1 = 2 /* PLAN_SNAPSHOT dimension has not been populated */';
37     ELSE
38 
39   l_stmt := '
40 SELECT	f.compile_designator		ISC_ATTRIBUTE_1, -- Plan name
41 	f.description			ISC_ATTRIBUTE_2, -- Plan Description
42 	to_char(f.data_start_date)
43 	   ||'' - ''
44 	   || to_char(f.cutoff_date)	ISC_ATTRIBUTE_3, -- Plan Horizon
45 	lkup.meaning			ISC_ATTRIBUTE_4, -- Plan Type
46 	f.data_start_date		ISC_ATTRIBUTE_5, -- Run Date
47 	f.snapshot_date			ISC_ATTRIBUTE_6, -- Snapshot Date
48 	f.org_cnt			ISC_MEASURE_1, -- Planned Organizations
49 	f.snapshot_id			ISC_MEASURE_2 -- Snapshot ID
50   FROM	ISC_DBI_PLAN_SNAPSHOTS	f,
51 	MFG_LOOKUPS		lkup
52  WHERE 	lkup.lookup_type = ''MSC_PLAN_TYPE_LONG''
53    AND	lkup.lookup_code = f.curr_plan_type
54    AND	enabled_flag = ''Y''
55    AND	f.snapshot_id IN (&PLAN_SNAPSHOT+PLAN_SNAPSHOT)
56 &ORDER_BY_CLAUSE NULLS LAST';
57 
58 END IF;
59 
60   x_custom_sql := l_stmt;
61 
62 
63 END Get_Sql;
64 
65 END ISC_DBI_PLAN_DETAILS_PKG ;
66