PACKAGE BODY: APPS.ISC_DBI_PLAN_ORGS_PKG

Source

1 PACKAGE BODY ISC_DBI_PLAN_ORGS_PKG AS
2 /* $Header: ISCRGALB.pls 115.2 2004/01/30 07:56:20 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_lang			VARCHAR2(10);
12   l_custom_rec			BIS_QUERY_ATTRIBUTES;
13 
14 BEGIN
15 
16   FOR i IN 1..p_param.COUNT
17   LOOP
18     IF (p_param(i).parameter_name = 'PLAN_SNAPSHOT+PLAN_SNAPSHOT')
19       THEN l_plan := p_param(i).parameter_value;
20     END IF;
21   END LOOP;
22 
23   l_lang := USERENV('LANG');
24 
25   l_custom_rec := BIS_PMV_PARAMETERS_PUB.Initialize_Query_Type;
26   x_custom_output := BIS_QUERY_ATTRIBUTES_TBL();
27 
28   IF (l_plan IS NULL)
29     THEN l_stmt := '
30 SELECT	0	ISC_ATTRIBUTE_1
31   FROM	dual
32  WHERE	1 = 2 /* PLAN_SNAPSHOT dimension has not been populated */';
33     ELSE
34 
35   l_stmt := '
36 SELECT	org.name	ISC_ATTRIBUTE_1 -- Planned Organizations
37   FROM	ISC_DBI_PLAN_ORG_SNAPSHOTS	f,
38 	HR_ALL_ORGANIZATION_UNITS_TL	org
39  WHERE	f.organization_id = org.organization_id
40    AND	org.language = :ISC_LANG
41    AND	f.snapshot_id IN (&PLAN_SNAPSHOT+PLAN_SNAPSHOT)
42 &ORDER_BY_CLAUSE NULLS LAST';
43 
44   END IF;
45 
46   x_custom_sql := l_stmt;
47 
48   l_custom_rec.attribute_name := ':ISC_LANG';
49   l_custom_rec.attribute_value := l_lang;
50   l_custom_rec.attribute_type := bis_pmv_parameters_pub.bind_type;
51   l_custom_rec.attribute_data_type := bis_pmv_parameters_pub.varchar2_bind;
52   x_custom_output.extend;
53   x_custom_output(1) := l_custom_rec;
54 
55 END Get_Sql;
56 
57 END ISC_DBI_PLAN_ORGS_PKG ;
58