1 PACKAGE BODY OKE_PA_CHECKS_PUB AS
2 /* $Header: OKEPPACB.pls 120.2 2007/12/20 07:52:52 neerakum ship $ */
3
4 --G_PKG_NAME CONSTANT VARCHAR2(30) := 'OKE_PA_CHECKS_PUB';
5 g_api_type CONSTANT VARCHAR2(4) := '_PUB';
6
7 --
8 -- Name : Project_Used
9 -- Function : This function checks if a certain project is used by OKE
10 --
11 -- Parameters :
12 -- IN : Project_ID NUMBER
13 -- OUT : None
14 --
15 -- Returns : VARCHAR2 ( 'Y' 'N' )
16 --
17
18
19 PROCEDURE Project_Used
20 ( p_api_version IN NUMBER
21 , p_commit IN VARCHAR2 := FND_API.G_FALSE
22 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
23 , x_msg_count OUT NOCOPY NUMBER
24 , x_msg_data OUT NOCOPY VARCHAR2
25 , x_return_status OUT NOCOPY VARCHAR2
26 , Project_ID IN NUMBER
27 , X_Result OUT NOCOPY VARCHAR2
28 ) IS
29 l_project_id NUMBER:=Project_ID;
30
31 CURSOR c_used IS
32 SELECT 'Y' FROM (
33 SELECT project_id FROM oke_k_headers
34 UNION ALL
35 SELECT project_id FROM oke_k_lines
36 UNION ALL
37 SELECT project_id FROM oke_k_fund_allocations
38 UNION ALL
39 SELECT project_id FROM oke_k_deliverables_b
40 UNION ALL
41 SELECT bill_project_id FROM oke_k_billing_events
42 ) WHERE project_id=l_project_id;
43
44
45 BEGIN
46
47 X_Result:='N';
48 OPEN c_used;
49 FETCH c_used INTO X_Result;
50 CLOSE c_used;
51
52 END Project_Used;
53
54
55
56 --
57 -- Name : Task_Used
58 -- Function : This function checks if a certain task is used by OKE
59 --
60 -- Parameters :
61 -- IN : Task_ID NUMBER
62 -- OUT : None
63 --
64 -- Returns : VARCHAR2 ( 'Y' 'N' )
65 --
66
67 PROCEDURE Task_Used
68 ( p_api_version IN NUMBER
69 , p_commit IN VARCHAR2 := FND_API.G_FALSE
70 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
71 , x_msg_count OUT NOCOPY NUMBER
72 , x_msg_data OUT NOCOPY VARCHAR2
73 , x_return_status OUT NOCOPY VARCHAR2
74 , Task_ID IN NUMBER
75 , X_Result OUT NOCOPY VARCHAR2
76 ) IS
77
78 l_task_id NUMBER:=Task_ID;
79
80 CURSOR c_used IS
81 SELECT 'Y' FROM (
82 SELECT task_id FROM oke_k_lines
83 UNION ALL
84 SELECT task_id FROM oke_k_fund_allocations
85 UNION ALL
86 SELECT task_id FROM oke_k_deliverables_b
87 UNION ALL
88 SELECT bill_task_id FROM oke_k_billing_events
89 ) WHERE task_id=l_task_id;
90
91 BEGIN
92
93 X_Result:='N';
94 OPEN c_used;
95 FETCH c_used INTO X_Result;
96 CLOSE c_used;
97
98 END Task_Used;
99
100 PROCEDURE Get_Parent_Proj_Task (
101 p_head_id IN NUMBER
102 , p_line_id IN NUMBER
103 , p_proj_id OUT NOCOPY NUMBER
104 , p_task_id OUT NOCOPY NUMBER
105 ) IS
106
107 CURSOR c_plines IS
108 SELECT PROJECT_ID, TASK_ID
109 FROM OKE_K_LINES E, OKC_ANCESTRYS A
110 WHERE K_LINE_ID = CLE_ID_ASCENDANT AND CLE_ID = P_LINE_ID AND PROJECT_ID IS NOT NULL
111 ORDER BY LEVEL_SEQUENCE desc
112 ;
113 CURSOR c_line(cp_line_id NUMBER) IS
114 SELECT PROJECT_ID, TASK_ID, parent_line_id
115 FROM OKE_K_LINES
116 WHERE K_LINE_ID = CP_LINE_ID AND PROJECT_ID IS NOT NULL
117 ;
118 CURSOR c_head IS
119 SELECT PROJECT_ID FROM oke_k_headers WHERE k_header_id = p_head_id
120 ;
121 l_line_id NUMBER := p_line_id;
122
123 BEGIN
124
125 p_proj_id:=NULL;
126 p_task_id:=NULL;
127 WHILE (l_line_id IS NOT NULL AND p_proj_id IS NULL) LOOP
128 OPEN c_line(l_line_id);
129 FETCH c_line INTO p_proj_id,p_task_id,l_line_id;
130 CLOSE c_line;
131 END LOOP;
132
133 IF (p_proj_id IS NULL) THEN
134 OPEN c_head;
135 FETCH c_head INTO p_proj_id;
136 CLOSE c_head;
137 END IF;
138
139 END Get_Parent_Proj_Task;
140
141 --
142 -- Name : is_Hierarchy_Valid
143 -- Function : This function checks if there is a valid project hierarchy
144 -- from (p_from_proj,p_from_task) to p_to_proj
145 -- avoiding direct link from p_delf_proj+p_delf_task to p_delt_proj
146 --
147 -- IN Parameters :
148 -- p_from_proj NUMBER
149 -- p_from_task NUMBER
150 -- p_to_proj NUMBER
151 -- p_delf_proj NUMBER from-project to delete
152 -- p_delf_task NUMBER from-task to delete
153 -- p_delt_proj NUMBER to-project to delete
154 -- Returns : BOOLEAN
155 FUNCTION is_Hierarchy_Valid( p_from_proj NUMBER, p_from_task NUMBER, p_to_proj NUMBER,
156 p_delf_proj NUMBER, p_delf_task NUMBER, p_delt_proj NUMBER)
157 RETURN BOOLEAN IS
158 CURSOR check_prj_hier IS
159 SELECT 'x' FROM dual
160 WHERE p_to_proj IN (
161 SELECT object_id_to1 prj_id FROM (
162 SELECT *
163 FROM pa_object_relationships
164 WHERE NOT (object_id_from2=p_delf_proj AND object_id_from1=p_delf_task AND object_id_to1=p_delt_proj)
165 )
166 WHERE object_type_from = 'PA_TASKS'
167 AND object_type_to = 'PA_PROJECTS'
168 AND relationship_type = 'H'
169 START WITH (object_id_from2, object_id_from1)
170 IN (SELECT p_from_proj, task_id FROM pa_tasks
171 WHERE project_id = p_from_proj
172 AND top_task_id = nvl(p_from_task, top_task_id))
173 CONNECT BY object_id_from2 = PRIOR object_id_to1
174 UNION ALL
175 SELECT object_id_to2 prj_id FROM (
176 SELECT *
177 FROM pa_object_relationships p
178 WHERE NOT (object_id_to2=p_delt_proj AND object_type_from = 'PA_TASKS'
179 AND object_id_from1 IN (SELECT ppev.element_version_id
180 FROM pa_tasks pt, pa_proj_element_versions ppev, pa_proj_elem_ver_structure ppevs
181 WHERE ppev.proj_element_id = pt.task_id
182 AND pt.top_task_id = nvl(p_delf_task, pt.top_task_id)
183 AND pt.task_id = pt.top_task_id
184 AND pt.project_id = p_delf_proj
185 AND ppev.project_id = ppevs.project_id
186 AND ppev.parent_structure_version_id = ppevs.element_version_id
187 AND ppevs.status_code = 'STRUCTURE_PUBLISHED'
188 AND ppevs.latest_eff_published_flag = 'Y' ))
189 )
190 START WITH object_type_from = 'PA_TASKS' AND object_id_from1
191 IN (SELECT ppev.element_version_id
192 FROM pa_tasks pt, pa_proj_element_versions ppev, pa_proj_elem_ver_structure ppevs
193 WHERE ppev.proj_element_id = pt.task_id
194 AND pt.top_task_id = nvl(p_from_task, pt.top_task_id)
195 AND pt.task_id = pt.top_task_id
196 AND pt.project_id = p_from_proj
197 AND ppev.project_id = ppevs.project_id
198 AND ppev.parent_structure_version_id = ppevs.element_version_id
199 AND ppevs.status_code = 'STRUCTURE_PUBLISHED'
200 AND ppevs.latest_eff_published_flag = 'Y' )
201 CONNECT BY object_id_from1 = PRIOR object_id_to1 AND relationship_type IN ('S','LF')
202 )
203 ;
204 l_result VARCHAR2(1):='?';
205 BEGIN
206 IF p_from_proj=p_delf_proj AND p_from_task=p_delf_task AND p_to_proj=p_delt_proj THEN
207 RETURN FALSE;
208 ELSE
209 IF p_from_proj=p_to_proj THEN
210 RETURN TRUE;
211 ELSE
212 -- check hierarchy
213 OPEN check_prj_hier;
214 FETCH check_prj_hier INTO l_result;
215 CLOSE check_prj_hier;
216 RETURN l_result='x';
217 END IF;
218 END IF;
219 END is_Hierarchy_Valid;
220
221
222 --
223 -- Name : Disassociation_Allowed
224 -- Function : This function checks if a certain project(To_Project_ID)
225 -- can be disassociatied from
226 -- a task(From_Project_ID,From_Task_ID)
227 --
228 -- Parameters :
229 -- IN : From_Project_ID NUMBER
230 -- From_Task_ID NUMBER
231 -- To_Project_ID NUMBER
232 -- OUT : None
233 --
234 -- Returns : VARCHAR2 ( 'Y' 'N' )
235 --
236
237
238 PROCEDURE Disassociation_Allowed
239 ( p_api_version IN NUMBER
240 , p_commit IN VARCHAR2 := FND_API.G_FALSE
241 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
242 , x_msg_count OUT NOCOPY NUMBER
243 , x_msg_data OUT NOCOPY VARCHAR2
244 , x_return_status OUT NOCOPY VARCHAR2
245 , From_Project_ID IN NUMBER
246 , From_Task_ID IN NUMBER
247 , To_Project_ID IN NUMBER
248 , X_Result OUT NOCOPY VARCHAR2
249 ) IS
250
251
252 CURSOR used_refs(p_project_ID NUMBER) IS
253 SELECT header_id, line_id, project_id FROM (
254 SELECT dnz_chr_id header_id, c.cle_id line_id, project_id
255 FROM oke_k_lines e, okc_k_lines_b c
256 WHERE e.k_line_id=c.id
257 UNION ALL
258 SELECT object_id header_id, k_line_id, project_id FROM oke_k_fund_allocations
259 UNION ALL
260 SELECT k_header_id header_id, k_line_id, project_id FROM oke_k_deliverables_b
261 UNION ALL
262 SELECT k_header_id header_id, NULL, bill_project_id FROM oke_k_billing_events
263 ) WHERE project_id IN (
264 SELECT to_number(object_id_to1) project_id
265 FROM pa_object_relationships
266 WHERE object_type_from = 'PA_TASKS'
267 AND object_type_to = 'PA_PROJECTS'
268 AND relationship_type = 'H'
269 START WITH object_id_from2 = p_project_ID
270 CONNECT BY object_id_from2 = PRIOR object_id_to1
271 UNION ALL
272 SELECT TO_NUMBER(OBJECT_ID_TO2) PROJECT_ID
273 FROM PA_OBJECT_RELATIONSHIPS p
274 START WITH OBJECT_TYPE_FROM = 'PA_STRUCTURES' AND object_id_from1 IN
275 (SELECT element_version_id
276 FROM pa_proj_elem_ver_structure ppevs
277 WHERE ppevs.project_id=p_project_ID
278 AND status_code = 'STRUCTURE_PUBLISHED'
279 AND ppevs.latest_eff_published_flag = 'Y')
280 CONNECT BY object_id_from1 = PRIOR object_id_to1
281 AND relationship_type IN('S','LF')
282 UNION ALL
283 SELECT p_project_ID FROM DUAL
284 ) ORDER BY project_id, line_id
285 ;
286
287 lp_proj NUMBER;
288 lp_task NUMBER;
289 l_return_status VARCHAR2(1) := OKE_API.G_RET_STS_SUCCESS;
290 l_result VARCHAR2(1) := 'Y';
291
292 l_api_name CONSTANT VARCHAR2(30) := 'DISASSOCIATION_ALLOWED';
293 l_api_version NUMBER := 1.0;
294
295 BEGIN
296
297 l_return_status := OKE_API.START_ACTIVITY(
298 p_api_name => l_api_name,
299 p_pkg_name => g_pkg_name,
300 p_init_msg_list => p_init_msg_list,
301 l_api_version => l_api_version,
302 p_api_version => p_api_version,
303 p_api_type => g_api_type,
304 x_return_status => l_return_status);
305
306 -- check if activity started successfully
307 IF (l_return_status = OKE_API.G_RET_STS_UNEXP_ERROR) THEN
308 RAISE OKE_API.G_EXCEPTION_UNEXPECTED_ERROR;
309 ELSIF (l_return_status = OKE_API.G_RET_STS_ERROR) THEN
310 RAISE OKE_API.G_EXCEPTION_ERROR;
311 END IF;
312
313 -- initialize return status
314 x_return_status := OKE_API.G_RET_STS_SUCCESS;
315
316 -- get parent header/lines for all objects where subhierarchy of To_Project_ID is used
317 -- i.e. building a list of restricting objects
318 FOR c IN used_refs(To_Project_ID) LOOP
319 -- get parent Proj/Task
320 Get_Parent_Proj_Task( c.header_id, c.line_id, lp_proj, lp_task );
321
322 -- verify if exist a hierarchy bw lp_proj+lp_task and c.project_id w/o from_project_ID+from_task_ID
323 IF NOT is_Hierarchy_Valid( lp_proj, lp_task, c.project_id,
324 from_project_ID, from_task_ID, To_Project_ID)
325 THEN
326 -- if not - skip rest
327 l_result := 'N';
328 EXIT;
329 END IF;
330 END LOOP;
331
332 X_Result := l_result;
333
334 OKE_API.END_ACTIVITY( x_msg_count => x_msg_count, x_msg_data => x_msg_data);
335
336 EXCEPTION
337 WHEN OKE_API.G_EXCEPTION_ERROR THEN
338 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
339 p_api_name => l_api_name,
340 p_pkg_name => g_pkg_name,
341 p_exc_name => 'OKE_API.G_RET_STS_ERROR',
342 x_msg_count => x_msg_count,
343 x_msg_data => x_msg_data,
344 p_api_type => g_api_type);
345
346 WHEN OKE_API.G_EXCEPTION_UNEXPECTED_ERROR THEN
347 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
348 p_api_name => l_api_name,
349 p_pkg_name => g_pkg_name,
350 p_exc_name => 'OKE_API.G_RET_STS_UNEXP_ERROR',
351 x_msg_count => x_msg_count,
352 x_msg_data => x_msg_data,
353 p_api_type => g_api_type);
354
355 WHEN OTHERS THEN
356 x_return_status := OKE_API.HANDLE_EXCEPTIONS(
357 p_api_name => l_api_name,
358 p_pkg_name => g_pkg_name,
359 p_exc_name => 'OTHERS',
360 x_msg_count => x_msg_count,
361 x_msg_data => x_msg_data,
362 p_api_type => g_api_type);
363
364 END Disassociation_Allowed;
365
366
367
368
369
370 END OKE_PA_CHECKS_PUB;
[Home] [Help]