[Home] [Help]
PACKAGE BODY: APPS.PA_EXT_ATTRIBUTE_UTILS
Source
1 PACKAGE BODY Pa_ext_attribute_utils AS
2 /* $Header: PAEXTUTB.pls 120.1 2007/03/06 12:07:39 apangana ship $ */
3
4 Function get_attribute_groups
5 (
6 p_classfication_code IN VARCHAR2
7 ) RETURN VARCHAR2 is
8 l_ret VARCHAR2(4000);
9 l_size NUMBER := 0;
10 l_new_size NUMBER :=0;
11
12 CURSOR
13 get_attr_groups
14 IS
15 SELECT
16 attr_group_disp_name
17 FROM
18 EGO_OBJ_ATTR_GRP_ASSOCS_V AGV
19 WHERE AGV.classification_code = p_classfication_code;
20
21
22 BEGIN
23 l_ret := NULL;
24
25 FOR rec IN get_attr_groups LOOP
26
27 l_size := Length(l_ret);
28 l_new_size := 2 + Length(rec.attr_group_disp_name);
29 IF l_new_size > 4000 THEN
30
31 l_ret := Substr(l_ret || ', ' || rec.attr_group_disp_name, 1, 4000);
32 RETURN l_ret;
33
34 END IF;
35
36 IF l_ret IS NULL then
37 l_ret := l_ret || rec.attr_group_disp_name;
38 ELSE
39
40 l_ret := l_ret || ', ' || rec.attr_group_disp_name;
41 END IF;
42
43 END LOOP;
44
45
46
47
48
49 RETURN l_ret;
50
51 END get_attribute_groups;
52
53 Function get_page_regions
54 (
55 p_classfication_code IN VARCHAR2
56 ) RETURN VARCHAR2 IS
57 l_ret VARCHAR2(4000);
58 l_size NUMBER := 0;
59 l_new_size NUMBER :=0;
60
61
62 CURSOR get_page_regions
63 IS
64 select
65 display_name
66 From EGO_PAGES_V
67 where classification_code = p_classfication_code;
68
69 BEGIN
70
71 l_ret := NULL;
72
73 FOR rec IN get_page_regions LOOP
74
75 l_size := Length(l_ret);
76 l_new_size := 2 + Length(rec.display_name);
77 IF l_new_size > 4000 THEN
78
79 l_ret := Substr(l_ret || ', ' || rec.display_name, 1, 4000);
80
81
82 RETURN l_ret;
83
84 END IF;
85
86 IF l_ret IS NULL then
87 l_ret := l_ret || rec.display_name;
88 else
89 l_ret := l_ret || ', ' || rec.display_name;
90 END IF;
91
92 END LOOP;
93
94
95
96
97
98 RETURN l_ret;
99 END get_page_regions;
100
101 FUNCTION check_object_page_region
102 (
103 p_object_type VARCHAR2,
104 p_object_id NUMBER,
105 p_page_id NUMBER
106 ) RETURN VARCHAR2 IS
107
108 l_ret VARCHAR2(1):= 'F';
109 l_page_id NUMBER;
110 l_project_id NUMBER;
111
112 CURSOR
113 check_project_page
114 IS
115 SELECT
116 epv.page_id
117 FROM PA_PROJECT_DRIVERS_V pprv,
118 ego_pages_v epv,
119 fnd_objects fo
120 where pprv.project_id = p_object_id
121 AND fo.obj_name = 'PA_PROJECTS'
122 and epv.object_id = fo.object_id and epv.object_name = 'PA_PROJECTS'
123 and pprv.driver_code = epv.classification_code
124 and epv.data_level_int_name = 'PROJECT_LEVEL'
125 AND epv.page_id = p_page_id;
126
127 CURSOR get_project_id
128 IS
129 select project_id from pa_proj_elements
130 where proj_element_id = p_object_id
131 AND object_type = 'PA_TASKS'
132 ;
133
134 CURSOR
135 check_task_page
136 IS
137 SELECT
138 epv.page_id
139 FROM
140 PA_PROJECTS_ALL PPA,
141 PA_PROJECT_TYPES_ALL PPT,
142 ego_pages_v epv,
143 fnd_objects fo
144 where ppa.project_id = l_project_id
145 AND PPA.PROJECT_TYPE = PPT.PROJECT_TYPE
146 AND PPA.ORG_ID = PPT.ORG_ID -- Bug 5900445
147 AND fo.obj_name = 'PA_PROJECTS'
148 and epv.object_id = fo.object_id and epv.object_name = 'PA_PROJECTS'
149 and 'PROJECT_TYPE:' || PPT.PROJECT_TYPE_ID = epv.classification_code
150 and epv.data_level_int_name = 'TASK_LEVEL'
151 AND epv.page_id = p_page_id
152 union
153 SELECT
154 epv.page_id
155 FROM
156 PA_PROJECT_CLASSES PPC,
157 PA_CLASS_CATEGORIES PCC,
158 ego_pages_v epv,
159 fnd_objects fo
160 where
161 PPC.PROJECT_ID = l_project_id
162 AND PPC.CLASS_CATEGORY = PCC.CLASS_CATEGORY
163 AND fo.obj_name = 'PA_PROJECTS'
164 and epv.object_id = fo.object_id and epv.object_name = 'PA_PROJECTS'
165 and 'CLASS_CATEGORY:' || PCC.CLASS_CATEGORY_ID = epv.classification_code
166 and epv.data_level_int_name = 'TASK_LEVEL'
167 AND epv.page_id = p_page_id
168 union
169 SELECT
170 epv.page_id
171 FROM
172 PA_PROJECT_CLASSES PPC,
173 PA_CLASS_CODES pcc,
174 ego_pages_v epv,
175 fnd_objects fo
176 where
177 PPC.PROJECT_ID = l_project_id
178 AND PPC.CLASS_CATEGORY = PCC.CLASS_CATEGORY
179 AND PPC.CLASS_CODE = PCC.CLASS_CODE
180 AND fo.obj_name = 'PA_PROJECTS'
181 and epv.object_id = fo.object_id and epv.object_name = 'PA_PROJECTS'
182 and 'CLASS_CODE:' || PCC.CLASS_CODE_ID = epv.classification_code
183 and epv.data_level_int_name = 'TASK_LEVEL'
184 AND epv.page_id = p_page_id;
185
186
187 CURSOR
188 check_task_page_for_task_type
189 IS
190 SELECT
191 epv.page_id
192 FROM PA_TASK_TYPES TT,
193 PA_LOOKUPS pl,
194 ego_pages_v epv,
195 fnd_objects fo
196 WHERE
197 PL.LOOKUP_TYPE = 'PA_EXT_DRIVER_TYPE'
198 AND PL.LOOKUP_CODE = 'TASK_TYPE'
199 AND tt.task_type_id =
200 (
201 select type_id from pa_proj_elements
202 where proj_element_id = p_object_id
203 AND object_type = 'PA_TASKS'
204 )
205 AND fo.obj_name = 'PA_PROJECTS'
206 and epv.object_id = fo.object_id and epv.object_name = 'PA_PROJECTS'
207 and 'TASK_TYPE:' || TT.TASK_TYPE_ID = epv.classification_code
208 and epv.data_level_int_name = 'TASK_LEVEL'
209 AND epv.page_id = p_page_id;
210
211 BEGIN
212
213 IF p_object_type = 'PA_PROJECTS' THEN
214 OPEN check_project_page;
215 FETCH check_project_page INTO l_page_id;
216 IF check_project_page%found THEN
217 l_ret := 'T';
218 END IF;
219 CLOSE check_project_page;
220 ELSIF p_object_type = 'PA_TASKS' THEN
221
222 OPEN get_project_id;
223 FETCH get_project_id INTO l_project_id;
224 IF get_project_id%notfound THEN
225 CLOSE get_project_id;
226 RETURN 'F';
227 END IF;
228
229 CLOSE get_project_id;
230
231 OPEN check_task_page;
232 FETCH check_task_page INTO l_page_id;
233
234 IF check_task_page%found THEN
235 l_ret := 'T';
236 ELSE
237 OPEN check_task_page_for_task_type;
238 FETCH check_task_page_for_task_type INTO l_page_id;
239 IF check_task_page_for_task_type%found THEN
240 l_ret := 'T';
241 END IF;
242 CLOSE check_task_page_for_task_type;
243 END IF;
244 CLOSE check_task_page;
245
246 ELSE
247 NULL;
248 END IF;
249
250 RETURN l_ret;
251
252 END check_object_page_region;
253
254
255
256 END Pa_ext_attribute_utils;