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.PA_DDC_API

Source


1 PACKAGE BODY PA_DDC_API AS
2 /* $Header: PAXDDC0B.pls 120.2 2005/08/19 17:13:17 mwasowic ship $ */
3 
4 FUNCTION Check_Alias (x_alias IN VARCHAR2
5 			, x_folder_code IN VARCHAR2) RETURN VARCHAR2 IS
6 
7 	dummy NUMBER	:= 0;
8 BEGIN
9 	SELECT count(1)
10 	INTO	dummy
11 	FROM 	pa_status_column_setup
12 	WHERE	folder_code = x_folder_code
13 	AND	INSTR(column_name,x_alias) > 0;
14 
15 	IF (dummy >= 1) THEN
16 		RETURN ('Y');
17 	ELSE
18 		RETURN ('N');
19 	END IF;
20 
21 END Check_Alias;
22 
23 
24 PROCEDURE create_psi_generic_views
25 		(x_view_name	IN VARCHAR2
26 		 , x_err_stage	IN OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
27 		 , x_err_code	IN OUT NOCOPY NUMBER) --File.Sql.39 bug 4440895
28 IS
29 
30    ddl_stmt          	VARCHAR2(5000);
31    key_fields_1       	VARCHAR2(3000);
32    key_fields_2        	VARCHAR2(3000);
33    stmt               	VARCHAR2(4000);
34    from_clause        	VARCHAR2(1000);
35    where_clause       	VARCHAR2(1000);
36 
37    cid 			INTEGER;
38 
39 
40     CURSOR p1 IS
41 		SELECT format_code, column_name, currency_format_flag
42 		FROM pa_status_column_setup
43 		WHERE folder_code = 'P'
44 		order by folder_code, format_code, column_order;
45 
46     CURSOR t1 IS
47 		SELECT format_code, column_name, currency_format_flag
48 		FROM pa_status_column_setup
49 		WHERE folder_code = 'T'
50 		order by folder_code, format_code, column_order;
51 
52      CURSOR r1 IS
53 		SELECT format_code, column_name, currency_format_flag
54 		FROM pa_status_column_setup
55 		WHERE folder_code = 'R'
56 		order by folder_code, format_code, column_order;
57 
58 
59 BEGIN
60 
61 	x_err_code	:= 0;
62 
63 	IF (x_view_name = 'PA_STATUS_PROJ_GENERIC_V') THEN
64 		x_err_stage  :=  'Creating PA_STATUS_PROJ_GENERIC_V';
65 	ELSIF  (x_view_name= 'PA_STATUS_TASK_GENERIC_V') THEN
66 		x_err_stage  :=  'Creating PA_STATUS_TASK_GENERIC_V';
67 	ELSIF  (x_view_name= 'PA_STATUS_TASK_GENERIC_V') THEN
68 		x_err_stage  :=  'Creating PA_STATUS_TASK_GENERIC_V';
69 	ELSE
70 		x_err_stage  :=  'Invalid Argument:  '|| x_view_name;
71 	END IF;
72 
73 --
74 -- Build SQL Strings by View
75 --
76 
77 IF (x_view_name= 'PA_STATUS_PROJ_GENERIC_V') THEN
78 
79 ddl_stmt := 'CREATE OR REPLACE FORCE VIEW   PA_STATUS_PROJ_GENERIC_V
80 (	PROJECT_ID
81 	, VIEW_LABOR_COSTS_ALLOWED
82 	, COST_BUDGET_TYPE_CODE
83 	, REV_BUDGET_TYPE_CODE
84 	, DUMMY1
85 	, DUMMY2
86 	, DUMMY3
87 	, DUMMY4
88 	, DUMMY5
89 	, DUMMY6
90 	, COLUMN1
91 	, COLUMN2
92 	, COLUMN3
93 	, COLUMN4
94 	, COLUMN5
95 	, COLUMN6
96 	, COLUMN7
97 	, COLUMN8
98 	, COLUMN9
99 	, COLUMN10
100 	, COLUMN11
101 	, COLUMN12
102 	, COLUMN13
103 	, COLUMN14
104 	, COLUMN15
105 	, COLUMN16
106 	, COLUMN17
107 	, COLUMN18
108 	, COLUMN19
109 	, COLUMN20
110 	, COLUMN21
111 	, COLUMN22
112 	, COLUMN23
113 	, COLUMN24
114 	, COLUMN25
115 	, COLUMN26
116 	, COLUMN27
117 	, COLUMN28
118 	, COLUMN29
119 	, COLUMN30
120 	, COLUMN31
121 	, COLUMN32
122 	, COLUMN33
123 )  as SELECT
124 	p.project_id
125 	, SUBSTR(pa_security.view_labor_costs(p.project_id),1,1)
126 	, c.budget_type_code
127 	, r.budget_type_code
128 	, 1
129 	, 2
130 	, 3
131 	, 4
132 	, 5
133 	, 6';
134 
135 
136 from_clause := 'pa_projects p,pa_project_accum_headers pah,pa_status_proj_bgt_rev_v r,pa_status_proj_bgt_cost_v c';
137 
138 /* Fix for bug# 1461358 */
139 where_clause := '''Y''' ||' in (select pa_security.allow_query(pah.project_id) from sys.dual)  AND p.project_id = pah.project_id AND pah.task_id = 0 AND pah.resource_list_id = 0
140 		AND pah.project_id = r.project_id AND pah.project_id = c.project_id';
141 
142 
143 	IF (PA_DDC_API.Check_Alias('A.','P') = 'Y') THEN
144 
145 		from_clause := from_clause||',pa_project_accum_actuals a';
146 
147 		where_clause := where_clause||' AND pah.project_accum_id = a.project_accum_id (+)';
148 
149 	END IF;
150 
151 	IF (PA_DDC_API.Check_Alias('M.','P') = 'Y') THEN
152 
153 		from_clause := from_clause||',pa_project_accum_commitments m';
154 
155 		where_clause := where_clause||' AND pah.project_accum_id = m.project_accum_id (+)';
156 
157 	END IF;
158 
159 
160 
161 	FOR p1rec IN p1 LOOP
162 		IF (p1rec.format_code = 'C') THEN
163 
164 			IF (p1rec.column_name IS NULL) THEN
165 				stmt := stmt||',null';
166 			ELSE
167 				stmt := stmt||','||p1rec.column_name;
168 			END IF;
169 		ELSE
170 
171 			IF (p1rec.column_name IS NULL) THEN
172 				stmt := stmt||',0';
173 			ELSE
174 			   IF (p1rec.currency_format_flag IS NULL) THEN
175 				stmt := stmt||','||p1rec.column_name;
176 			  ELSE
177 			      	stmt :=  stmt||',('||p1rec.column_name||')/(PA_STATUS.Get_factor)';
178 			  END IF;
179 			END IF;
180 		END IF;
181 
182 
183 	END LOOP;
184 
185 ELSIF (x_view_name= 'PA_STATUS_TASK_GENERIC_V') THEN
186 
187 ddl_stmt := 'CREATE OR REPLACE FORCE VIEW   PA_STATUS_TASK_GENERIC_V
188 (	PROJECT_ID
189 	, TASK_ID
190 	, PARENT_TASK_ID
191 	, WBS_LEVEL
192 	, COST_BUDGET_TYPE_CODE
193 	, REV_BUDGET_TYPE_CODE
194         , CHILD_EXIST_FLAG
195 	, COLUMN1
196 	, COLUMN2
197 	, COLUMN3
198 	, COLUMN4
199 	, COLUMN5
200 	, COLUMN6
201 	, COLUMN7
202 	, COLUMN8
203 	, COLUMN9
204 	, COLUMN10
205 	, COLUMN11
206 	, COLUMN12
207 	, COLUMN13
208 	, COLUMN14
209 	, COLUMN15
210 	, COLUMN16
211 	, COLUMN17
212 	, COLUMN18
213 	, COLUMN19
214 	, COLUMN20
215 	, COLUMN21
216 	, COLUMN22
217 	, COLUMN23
218 	, COLUMN24
219 	, COLUMN25
220 	, COLUMN26
221 	, COLUMN27
222 	, COLUMN28
223 	, COLUMN29
224 	, COLUMN30
225 	, COLUMN31
226 	, COLUMN32
227 	, COLUMN33
228 )  as SELECT
229 	t.project_id
230 	, t.task_id
231 	, t.parent_task_id
232 	, t.wbs_level
233 	, c.budget_type_code
234 	, r.budget_type_code
235         , decode(pa_task_utils.check_child_exists(t.task_id),1,''+'',0,'' '') ';
236 
237 
238 
239 from_clause := 'pa_tasks t,pa_status_task_bgt_cost_high_v c,pa_status_task_bgt_rev_high_v r';
240 
241 
242 where_clause :='t.project_id = PA_STATUS.GetProjId AND t.task_id = c.task_id (+) AND t.task_id = r.task_id (+)';
243 
244 	IF (PA_DDC_API.Check_Alias('A.','T') = 'Y') THEN
245 
246 		from_clause := from_clause||', pa_status_task_act_v a';
247 
248 		where_clause := where_clause||' AND t.task_id = a.task_id (+)';
249 
250 	END IF;
251 
252 	IF (PA_DDC_API.Check_Alias('M.','T') = 'Y') THEN
253 
254 		from_clause := from_clause||', pa_status_task_cmt_v  m';
255 
256 		where_clause := where_clause||' AND t.task_id = m.task_id (+)';
257 
258 	END IF;
259 
260 	FOR t1rec IN t1 LOOP
261 
262 		IF (t1rec.format_code = 'C') THEN
263 
264 			IF (t1rec.column_name IS NULL) THEN
265 				stmt := stmt||',null';
266 			ELSE
267 				stmt := stmt||','||t1rec.column_name;
268 			END IF;
269 		ELSE
270 
271 			IF (t1rec.column_name IS NULL) THEN
272 				stmt := stmt||',0';
273 			ELSE
274 			  IF (t1rec.currency_format_flag IS NULL) THEN
275 				stmt := stmt||','||t1rec.column_name;
276 			  ELSE
277 			      	stmt :=  stmt||',('||t1rec.column_name||')/(PA_STATUS.Get_factor)';
278 			  END IF;
279 			END IF;
280 		END IF;
281 
282 	END LOOP;
283 
284 
285 ELSE
286 
287 ddl_stmt := 'CREATE OR REPLACE FORCE VIEW   PA_STATUS_RSRC_GENERIC_V
288 (	PROJECT_ID
289 	, RESOURCE_LIST_MEMBER_ID
290 	, PARENT_MEMBER_ID
291 	, MEMBER_LEVEL
292 	, SORT_ORDER
293 	, TASK_ID
294 	, RESOURCE_LIST_ID
295 	, RESOURCE_LIST_ASSIGNMENT_ID
296 	, PROJECT_LEVEL_FLAG
297         , CHILD_EXIST_FLAG
298 	, COLUMN1
299 	, COLUMN2
300 	, COLUMN3
301 	, COLUMN4
302 	, COLUMN5
303 	, COLUMN6
304 	, COLUMN7
305 	, COLUMN8
306 	, COLUMN9
307 	, COLUMN10
308 	, COLUMN11
309 	, COLUMN12
310 	, COLUMN13
311 	, COLUMN14
312 	, COLUMN15
313 	, COLUMN16
314 	, COLUMN17
315 	, COLUMN18
316 	, COLUMN19
317 	, COLUMN20
318 	, COLUMN21
319 	, COLUMN22
320 	, COLUMN23
321 	, COLUMN24
322 	, COLUMN25
323 	, COLUMN26
324 	, COLUMN27
325 	, COLUMN28
326 	, COLUMN29
327 	, COLUMN30
328 	, COLUMN31
329 	, COLUMN32
330 	, COLUMN33
331 )  as SELECT
332 	pah.project_id
333 	, pah.resource_list_member_id
334 	, rlm1.parent_member_id
335 	, rlm1.member_level
336 	, rlm1.sort_order
337 	, pah.task_id
338 	, pah.resource_list_id
339 	, pah.resource_list_assignment_id
340 	, decode(pah.task_id, 0, ''Y'',''N'')
341         , decode(pa_get_resource.child_resource_exists(pah.resource_list_member_id,
342           pah.task_id,pah.project_id),''Y'',''+'',''N'','' '')';
343 
344 
345 
346 from_clause := 'pa_status_proj_accum_headers_v pah,pa_resource_list_members rlm1';
347 
348 
349 where_clause :='pah.project_id = PA_STATUS.GetProjId AND pah.resource_list_id = PA_STATUS.GetRsrcListId AND pah.task_id = PA_STATUS.GetTaskId AND pah.resource_list_member_id = rlm1.resource_list_member_id';
350 
351 	IF (PA_DDC_API.Check_Alias('RES.','R') = 'Y') THEN
352 
353 		from_clause := from_clause||',pa_resources res';
354 
355 		where_clause := where_clause||' AND rlm1.resource_id = res.resource_id';
356 
357 	END IF;
358 
359 
360 	IF (PA_DDC_API.Check_Alias('A.','R') = 'Y') THEN
361 
362 		from_clause := from_clause||',pa_status_rsrc_act_high_v	a';
363 
364 		where_clause := where_clause||' AND pah.resource_list_member_id = a.resource_list_member_id (+)';
365 
366 	END IF;
367 
368 
369 	IF (PA_DDC_API.Check_Alias('M.','R') = 'Y') THEN
370 
371 		from_clause := from_clause||',pa_status_rsrc_cmt_high_v m';
372 
373 		where_clause := where_clause||' AND pah.resource_list_member_id = m.resource_list_member_id (+)';
374 
375 	END IF;
376 
377 
378 	IF (PA_DDC_API.Check_Alias('C.','R') = 'Y') THEN
379 
380 		from_clause := from_clause||',pa_status_rsrc_bgt_cost_high_v c';
381 
382 		where_clause := where_clause||' AND pah.resource_list_member_id = c.resource_list_member_id (+)';
383 
384 	END IF;
385 
386 
387 	IF (PA_DDC_API.Check_Alias('R.','R') = 'Y') THEN
388 
389 		from_clause := from_clause||',pa_status_rsrc_bgt_rev_high_v r';
390 
391 		where_clause := where_clause||' AND pah.resource_list_member_id = r.resource_list_member_id (+)';
392 
393 	END IF;
394 
395 
396 	FOR r1rec IN r1 LOOP
397 
398 		IF (r1rec.format_code = 'C') THEN
399 
400 			IF (r1rec.column_name IS NULL) THEN
401 				stmt := stmt||',null';
402 			ELSE
403 				stmt := stmt||','||r1rec.column_name;
404 			END IF;
405 		ELSE
406 			IF (r1rec.column_name IS NULL) THEN
407 				stmt := stmt||',0';
408 			ELSE
409 			  IF (r1rec.currency_format_flag IS NULL) THEN
410 				stmt := stmt||','||r1rec.column_name;
411 			  ELSE
412 			      	stmt :=  stmt||',('||r1rec.column_name||')/(PA_STATUS.Get_factor)';
413 			  END IF;
414 		              END IF;
415 		END IF;
416 
417 	END LOOP;
418 
419 END IF;
420 
421 
422    ddl_stmt := ddl_stmt||stmt;
423    ddl_stmt := ddl_stmt||' FROM '||from_clause;
424    ddl_stmt := ddl_stmt||' WHERE '||where_clause;
425 
426 cid := dbms_sql.open_cursor;
427 
428 dbms_sql.parse(cid,ddl_stmt,dbms_sql.v7);
429 
430 END create_psi_generic_views;
431 
432 
433 END PA_DDC_API;