232:
233: CURSOR cur_elem_ver_stru( p_version_id NUMBER )
234: IS
235: SELECT ppevs.version_number, ppevs.name, ppe.element_number, ppe.name, ppe.proj_element_id, ppe.project_id
236: FROM pa_proj_elem_ver_structure ppevs,
237: pa_proj_elements ppe
238: WHERE ppevs.element_version_id = p_version_id
239: AND ppe.proj_element_id = ppevs.proj_element_id
240: AND ppevs.project_id = (select project_id
288: ) RETURN NUMBER IS
289: CURSOR cur_elem_ver_stru
290: IS
291: SELECT element_version_id
292: FROM pa_proj_elem_ver_structure ppevs,
293: pa_proj_structure_types ppst,
294: pa_structure_types pst
295: WHERE ppevs.project_id = p_project_id
296: AND latest_eff_published_flag = 'Y'
307:
308: -- POST K:Added for Shortcut to get the last updated Workplan version
309:
310: Procedure Get_Last_Upd_Working_Wp_Ver(
311: p_project_id IN pa_proj_elem_ver_structure.project_id%TYPE
312: ,x_pev_structure_id OUT NOCOPY pa_proj_elem_ver_structure.pev_structure_id%TYPE -- 4537865
313: ,x_element_version_id OUT NOCOPY pa_proj_elem_ver_structure.element_version_id%TYPE -- 4537865
314: ,x_element_version_name OUT NOCOPY pa_proj_elem_ver_structure.name%TYPE -- 4537865
315: ,x_record_version_number OUT NOCOPY pa_proj_elem_ver_structure.record_version_number%TYPE -- 4537865
308: -- POST K:Added for Shortcut to get the last updated Workplan version
309:
310: Procedure Get_Last_Upd_Working_Wp_Ver(
311: p_project_id IN pa_proj_elem_ver_structure.project_id%TYPE
312: ,x_pev_structure_id OUT NOCOPY pa_proj_elem_ver_structure.pev_structure_id%TYPE -- 4537865
313: ,x_element_version_id OUT NOCOPY pa_proj_elem_ver_structure.element_version_id%TYPE -- 4537865
314: ,x_element_version_name OUT NOCOPY pa_proj_elem_ver_structure.name%TYPE -- 4537865
315: ,x_record_version_number OUT NOCOPY pa_proj_elem_ver_structure.record_version_number%TYPE -- 4537865
316: ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
309:
310: Procedure Get_Last_Upd_Working_Wp_Ver(
311: p_project_id IN pa_proj_elem_ver_structure.project_id%TYPE
312: ,x_pev_structure_id OUT NOCOPY pa_proj_elem_ver_structure.pev_structure_id%TYPE -- 4537865
313: ,x_element_version_id OUT NOCOPY pa_proj_elem_ver_structure.element_version_id%TYPE -- 4537865
314: ,x_element_version_name OUT NOCOPY pa_proj_elem_ver_structure.name%TYPE -- 4537865
315: ,x_record_version_number OUT NOCOPY pa_proj_elem_ver_structure.record_version_number%TYPE -- 4537865
316: ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
317: ,x_msg_count OUT NOCOPY NUMBER -- 4537865
310: Procedure Get_Last_Upd_Working_Wp_Ver(
311: p_project_id IN pa_proj_elem_ver_structure.project_id%TYPE
312: ,x_pev_structure_id OUT NOCOPY pa_proj_elem_ver_structure.pev_structure_id%TYPE -- 4537865
313: ,x_element_version_id OUT NOCOPY pa_proj_elem_ver_structure.element_version_id%TYPE -- 4537865
314: ,x_element_version_name OUT NOCOPY pa_proj_elem_ver_structure.name%TYPE -- 4537865
315: ,x_record_version_number OUT NOCOPY pa_proj_elem_ver_structure.record_version_number%TYPE -- 4537865
316: ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
317: ,x_msg_count OUT NOCOPY NUMBER -- 4537865
318: ,x_msg_data OUT NOCOPY VARCHAR2) -- 4537865
311: p_project_id IN pa_proj_elem_ver_structure.project_id%TYPE
312: ,x_pev_structure_id OUT NOCOPY pa_proj_elem_ver_structure.pev_structure_id%TYPE -- 4537865
313: ,x_element_version_id OUT NOCOPY pa_proj_elem_ver_structure.element_version_id%TYPE -- 4537865
314: ,x_element_version_name OUT NOCOPY pa_proj_elem_ver_structure.name%TYPE -- 4537865
315: ,x_record_version_number OUT NOCOPY pa_proj_elem_ver_structure.record_version_number%TYPE -- 4537865
316: ,x_return_status OUT NOCOPY VARCHAR2 -- 4537865
317: ,x_msg_count OUT NOCOPY NUMBER -- 4537865
318: ,x_msg_data OUT NOCOPY VARCHAR2) -- 4537865
319: AS
329: l_debug_level5 CONSTANT NUMBER := 5;
330: l_module_name VARCHAR2(100) := 'pa.plsql.PA_PROJ_ELEMENTS_UTILS';
331:
332: l_date1 DATE ;
333: l_pev_structure_id1 pa_proj_elem_ver_structure.pev_structure_id%TYPE ;
334: l_element_version_id1 pa_proj_elem_ver_structure.element_version_id%TYPE ;
335: l_element_version_name1 pa_proj_elem_ver_structure.name%TYPE ;
336: l_record_version_number1 pa_proj_elem_ver_structure.record_version_number%TYPE ;
337:
330: l_module_name VARCHAR2(100) := 'pa.plsql.PA_PROJ_ELEMENTS_UTILS';
331:
332: l_date1 DATE ;
333: l_pev_structure_id1 pa_proj_elem_ver_structure.pev_structure_id%TYPE ;
334: l_element_version_id1 pa_proj_elem_ver_structure.element_version_id%TYPE ;
335: l_element_version_name1 pa_proj_elem_ver_structure.name%TYPE ;
336: l_record_version_number1 pa_proj_elem_ver_structure.record_version_number%TYPE ;
337:
338: l_date2 DATE ;
331:
332: l_date1 DATE ;
333: l_pev_structure_id1 pa_proj_elem_ver_structure.pev_structure_id%TYPE ;
334: l_element_version_id1 pa_proj_elem_ver_structure.element_version_id%TYPE ;
335: l_element_version_name1 pa_proj_elem_ver_structure.name%TYPE ;
336: l_record_version_number1 pa_proj_elem_ver_structure.record_version_number%TYPE ;
337:
338: l_date2 DATE ;
339: l_pev_structure_id2 pa_proj_elem_ver_structure.pev_structure_id%TYPE ;
332: l_date1 DATE ;
333: l_pev_structure_id1 pa_proj_elem_ver_structure.pev_structure_id%TYPE ;
334: l_element_version_id1 pa_proj_elem_ver_structure.element_version_id%TYPE ;
335: l_element_version_name1 pa_proj_elem_ver_structure.name%TYPE ;
336: l_record_version_number1 pa_proj_elem_ver_structure.record_version_number%TYPE ;
337:
338: l_date2 DATE ;
339: l_pev_structure_id2 pa_proj_elem_ver_structure.pev_structure_id%TYPE ;
340: l_element_version_id2 pa_proj_elem_ver_structure.element_version_id%TYPE ;
335: l_element_version_name1 pa_proj_elem_ver_structure.name%TYPE ;
336: l_record_version_number1 pa_proj_elem_ver_structure.record_version_number%TYPE ;
337:
338: l_date2 DATE ;
339: l_pev_structure_id2 pa_proj_elem_ver_structure.pev_structure_id%TYPE ;
340: l_element_version_id2 pa_proj_elem_ver_structure.element_version_id%TYPE ;
341: l_element_version_name2 pa_proj_elem_ver_structure.name%TYPE ;
342: l_record_version_number2 pa_proj_elem_ver_structure.record_version_number%TYPE ;
343:
336: l_record_version_number1 pa_proj_elem_ver_structure.record_version_number%TYPE ;
337:
338: l_date2 DATE ;
339: l_pev_structure_id2 pa_proj_elem_ver_structure.pev_structure_id%TYPE ;
340: l_element_version_id2 pa_proj_elem_ver_structure.element_version_id%TYPE ;
341: l_element_version_name2 pa_proj_elem_ver_structure.name%TYPE ;
342: l_record_version_number2 pa_proj_elem_ver_structure.record_version_number%TYPE ;
343:
344: CURSOR cur_elem_ver_stru1
337:
338: l_date2 DATE ;
339: l_pev_structure_id2 pa_proj_elem_ver_structure.pev_structure_id%TYPE ;
340: l_element_version_id2 pa_proj_elem_ver_structure.element_version_id%TYPE ;
341: l_element_version_name2 pa_proj_elem_ver_structure.name%TYPE ;
342: l_record_version_number2 pa_proj_elem_ver_structure.record_version_number%TYPE ;
343:
344: CURSOR cur_elem_ver_stru1
345: IS
338: l_date2 DATE ;
339: l_pev_structure_id2 pa_proj_elem_ver_structure.pev_structure_id%TYPE ;
340: l_element_version_id2 pa_proj_elem_ver_structure.element_version_id%TYPE ;
341: l_element_version_name2 pa_proj_elem_ver_structure.name%TYPE ;
342: l_record_version_number2 pa_proj_elem_ver_structure.record_version_number%TYPE ;
343:
344: CURSOR cur_elem_ver_stru1
345: IS
346: SELECT ppevs.last_update_date,
347: ppevs.pev_structure_id,
348: ppevs.element_version_id,
349: ppevs.name,
350: ppevs.record_version_number
351: FROM pa_proj_elem_ver_structure ppevs,
352: pa_proj_structure_types ppst,
353: pa_structure_types pst
354: WHERE ppevs.project_id = p_project_id
355: AND ppevs.status_code = 'STRUCTURE_WORKING'
362: --Added the below mentioned cursor to take care of
363: --scenerios such as delete,indent,outdent,move,add
364: --etc.The above cursor doesn't take care of these
365: --scenerios as the last_updated_date is not updated
366: --for the structure version in pa_proj_elem_ver_structure
367: --table.The above cursor is though needed when the structure
368: --version infirmation such as description is updated.
369:
370: --Now after the fix ,both the maximum dates from
367: --table.The above cursor is though needed when the structure
368: --version infirmation such as description is updated.
369:
370: --Now after the fix ,both the maximum dates from
371: --pa_proj_elem_ver_structure and pa_proj_elem_ver_schedule
372: --are compared.The structure version info corresponding
373: --to greater last_updated_date is returned.
374:
375: CURSOR cur_elem_ver_stru2
380: c.name,
381: c.record_version_number
382: FROM pa_proj_element_versions b,
383: pa_proj_elem_ver_schedule a,
384: pa_proj_elem_ver_structure c,
385: pa_structure_types d ,
386: pa_proj_structure_types e
387: WHERE a.element_version_id= b.element_version_id
388: AND a.project_id = b.project_id
427: ppevs.pev_structure_id,
428: ppevs.element_version_id,
429: ppevs.name,
430: ppevs.record_version_number
431: FROM pa_proj_elem_ver_structure ppevs
432: WHERE ppevs.project_id = p_project_id
433: AND ppevs.proj_element_id = c_struc_id
434: AND ppevs.element_version_id = c_struc_ver_id;
435: /* SELECT ppevs.last_update_date,
436: ppevs.pev_structure_id,
437: ppevs.element_version_id,
438: ppevs.name,
439: ppevs.record_version_number
440: FROM pa_proj_elem_ver_structure ppevs,
441: pa_proj_structure_types ppst,
442: pa_structure_types pst
443: WHERE ppevs.project_id = p_project_id
444: AND ppevs.proj_element_id = c_struc_id
754: ) return VARCHAR2
755: IS
756: cursor c1 is
757: select '1'
758: from pa_proj_elem_ver_structure
759: where project_id = p_project_id
760: and proj_element_id = p_structure_id
761: and published_date IS NULL;
762: c1_rec c1%rowtype;
824: and f.user_id = p_user_id;
825:
826: cursor get_lock_user(p_person_id NUMBER) IS
827: select '1'
828: from pa_proj_element_versions v, pa_proj_elem_ver_structure s
829: where v.element_version_id = p_parent_structure_ver_id
830: and v.project_id = s.project_id
831: and v.element_version_id = s.element_version_id
832: and (locked_by_person_id IS NULL
2300: and pev2.element_version_id = p_element_version_id;
2301:
2302: CURSOR c2 IS
2303: select str.project_id, str.element_version_id
2304: from pa_proj_elem_ver_structure str,
2305: pa_proj_element_versions pev
2306: where pev.element_version_id = p_parent_structure_version_id
2307: and pev.project_id = str.project_id
2308: and pev.proj_element_id = str.proj_element_id
2346: and pev2.element_version_id = p_element_version_id;
2347:
2348: CURSOR c2 IS
2349: select str.project_id, str.element_version_id
2350: from pa_proj_elem_ver_structure str,
2351: pa_proj_element_versions pev
2352: where pev.element_version_id = p_parent_structure_version_id
2353: and pev.project_id = str.project_id
2354: and pev.proj_element_id = str.proj_element_id
3153: CURSOR c1 is
3154: select 1 from
3155: pa_proj_elements a,
3156: pa_proj_element_versions b,
3157: pa_proj_elem_ver_structure c
3158: where a.proj_element_id = p_task_id
3159: and a.project_id = p_project_id
3160: and a.project_id = b.project_id
3161: and a.proj_element_id = b.proj_element_id
3267: IS
3268: CURSOR c1 IS
3269: select b.project_id, b.proj_element_id
3270: from pa_proj_element_versions a,
3271: pa_proj_elem_ver_structure b
3272: where a.element_version_id = p_task_version_id
3273: and a.parent_structure_version_id = b.element_version_id
3274: and a.project_id = b.project_id;
3275: l_project_id NUMBER;
3286: where relationship_type = 'S'
3287: start with object_id_from1 IN (
3288: select a.element_version_id
3289: from pa_proj_element_versions a,
3290: pa_proj_elem_ver_structure b
3291: where b.project_id = c_project_id
3292: and b.proj_element_id = c_structure_id
3293: and b.status_code <> 'STRUCTURE_PUBLISHED'
3294: and b.element_version_id = a.parent_structure_version_id
3313:
3314: CURSOR c3(c_project_id NUMBER, c_structure_id NUMBER, c_xtra_task_id NUMBER) IS
3315: select ppev.element_version_id, ppev.TASK_UNPUB_VER_STATUS_CODE
3316: from pa_proj_element_Versions ppev,
3317: pa_proj_elem_ver_structure ppevs
3318: where ppev.proj_element_id = c_xtra_task_id
3319: and ppev.project_id = c_project_id
3320: and ppev.parent_structure_version_id = ppevs.element_version_id
3321: and ppevs.project_id = c_project_id
3574: SELECT c.element_version_id
3575: FROM pa_proj_element_versions c,
3576: pa_structure_types a,
3577: pa_proj_structure_types b
3578: ,pa_proj_elem_ver_structure d
3579: WHERE c.project_id = c_project_id
3580: AND a.structure_type_id = b.structure_type_id
3581: AND b.proj_element_id = c.proj_element_id
3582: AND a.structure_type = c_structure_type
3589: SELECT c.element_version_id
3590: FROM pa_proj_element_versions c,
3591: pa_structure_types a,
3592: pa_proj_structure_types b,
3593: pa_proj_elem_ver_structure d
3594: WHERE c.project_id = c_project_id
3595: AND a.structure_type_id = b.structure_type_id
3596: AND b.proj_element_id = c.proj_element_id
3597: AND a.structure_type = c_structure_type