[Home] [Help]
271: ,x_msg_data OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
272: ,x_return_status OUT NOCOPY VARCHAR2 ) IS --File.Sql.39 bug 4440895
273:
274:
275: CURSOR cur_pa_tasks2 IS
276: SELECT distinct top_task_id
277: FROM pa_tasks pt
278: WHERE project_id = p_project_id;
279:
273:
274:
275: CURSOR cur_pa_tasks2 IS
276: SELECT distinct top_task_id
277: FROM pa_tasks pt
278: WHERE project_id = p_project_id;
279:
280: CURSOR cur_pa_tasks( c_top_task_id NUMBER )
281: IS
276: SELECT distinct top_task_id
277: FROM pa_tasks pt
278: WHERE project_id = p_project_id;
279:
280: CURSOR cur_pa_tasks( c_top_task_id NUMBER )
281: IS
282: SELECT task_id, wbs_level, parent_task_id, task_number, task_name, description, carrying_out_organization_id
283: FROM pa_tasks pt
284: WHERE project_id = p_project_id
279:
280: CURSOR cur_pa_tasks( c_top_task_id NUMBER )
281: IS
282: SELECT task_id, wbs_level, parent_task_id, task_number, task_name, description, carrying_out_organization_id
283: FROM pa_tasks pt
284: WHERE project_id = p_project_id
285: START WITH task_id = c_top_task_id
286: CONNECT BY PRIOR task_id = parent_task_id;
287:
290: IS
291: SELECT object_id_from1
292: FROM pa_object_relationships
293: WHERE object_id_to1 = c_task_version_id
294: and object_type_from in ('PA_STRUCTURES','PA_TASKS') -- bug 6429275
295: and object_type_to = 'PA_TASKS'
296: and relationship_type = 'S';
297:
298:
291: SELECT object_id_from1
292: FROM pa_object_relationships
293: WHERE object_id_to1 = c_task_version_id
294: and object_type_from in ('PA_STRUCTURES','PA_TASKS') -- bug 6429275
295: and object_type_to = 'PA_TASKS'
296: and relationship_type = 'S';
297:
298:
299: CURSOR cur_pa_projs
388:
389:
390: l_ref_task_version_id := null;
391:
392: FOR cur_pa_tasks2_rec IN cur_pa_tasks2 LOOP
393:
394: FOR cur_pa_tasks_rec IN cur_pa_tasks( cur_pa_tasks2_rec.top_task_id ) LOOP
395:
396:
390: l_ref_task_version_id := null;
391:
392: FOR cur_pa_tasks2_rec IN cur_pa_tasks2 LOOP
393:
394: FOR cur_pa_tasks_rec IN cur_pa_tasks( cur_pa_tasks2_rec.top_task_id ) LOOP
395:
396:
397:
398: --The task will be created with a different ids if the structure is a WORKPLAN and split from COSTING.
395:
396:
397:
398: --The task will be created with a different ids if the structure is a WORKPLAN and split from COSTING.
399: --Otherwise same ids as in pa_tasks will be used to create tasks in pa_proj_elements if only one default
400: --structure is being created.
401: l_split_flag := null;
402: OPEN cur_pa_projs;
403: FETCH cur_pa_projs INTO l_split_flag;
406: --For each project create 1 task
407: IF p_struc_type = 'WORKPLAN' AND l_split_flag IS NOT NULL
408: THEN
409: --l_proj_element_id := null;
410: SELECT pa_tasks_s.nextval INTO l_proj_element_id
411: FROM sys.dual;
412: l_postfix := '-'||SUBSTR( p_struc_type, 1, 1 );
413: --hsiu added for parent_structure_id column
414: OPEN get_structure(p_project_id, 'WORKPLAN');
415: FETCH get_structure into l_structure_id;
416: CLOSE get_structure;
417: ELSE
418: --hsiu added for parent_structure_id column
419: l_proj_element_id := cur_pa_tasks_rec.task_id;
420: OPEN get_structure(p_project_id, 'FINANCIAL');
421: FETCH get_structure into l_structure_id;
422: CLOSE get_structure;
423: END IF;
424: PA_PROJ_ELEMENTS_PKG.Insert_Row(
425: X_ROW_ID => X_ROW_ID
426: ,X_PROJ_ELEMENT_ID => l_proj_element_id
427: ,X_PROJECT_ID => p_project_id
428: ,X_OBJECT_TYPE => 'PA_TASKS'
429: ,X_ELEMENT_NUMBER => cur_pa_tasks_rec.task_number||l_postfix
430: ,X_NAME => cur_pa_tasks_rec.task_name
431: ,X_DESCRIPTION => cur_pa_tasks_rec.description
432: ,X_STATUS_CODE => '124' --- NOT_STARTED -- 124 bug 2826235
425: X_ROW_ID => X_ROW_ID
426: ,X_PROJ_ELEMENT_ID => l_proj_element_id
427: ,X_PROJECT_ID => p_project_id
428: ,X_OBJECT_TYPE => 'PA_TASKS'
429: ,X_ELEMENT_NUMBER => cur_pa_tasks_rec.task_number||l_postfix
430: ,X_NAME => cur_pa_tasks_rec.task_name
431: ,X_DESCRIPTION => cur_pa_tasks_rec.description
432: ,X_STATUS_CODE => '124' --- NOT_STARTED -- 124 bug 2826235
433: ,X_WF_STATUS_CODE => null
426: ,X_PROJ_ELEMENT_ID => l_proj_element_id
427: ,X_PROJECT_ID => p_project_id
428: ,X_OBJECT_TYPE => 'PA_TASKS'
429: ,X_ELEMENT_NUMBER => cur_pa_tasks_rec.task_number||l_postfix
430: ,X_NAME => cur_pa_tasks_rec.task_name
431: ,X_DESCRIPTION => cur_pa_tasks_rec.description
432: ,X_STATUS_CODE => '124' --- NOT_STARTED -- 124 bug 2826235
433: ,X_WF_STATUS_CODE => null
434: ,X_PM_PRODUCT_CODE => null
427: ,X_PROJECT_ID => p_project_id
428: ,X_OBJECT_TYPE => 'PA_TASKS'
429: ,X_ELEMENT_NUMBER => cur_pa_tasks_rec.task_number||l_postfix
430: ,X_NAME => cur_pa_tasks_rec.task_name
431: ,X_DESCRIPTION => cur_pa_tasks_rec.description
432: ,X_STATUS_CODE => '124' --- NOT_STARTED -- 124 bug 2826235
433: ,X_WF_STATUS_CODE => null
434: ,X_PM_PRODUCT_CODE => null
435: ,X_PM_TASK_REFERENCE => null
435: ,X_PM_TASK_REFERENCE => null
436: ,X_CLOSED_DATE => null
437: ,X_LOCATION_ID => null
438: ,X_MANAGER_PERSON_ID => null
439: ,X_CARRYING_OUT_ORGANIZATION_ID => cur_pa_tasks_rec.carrying_out_organization_id
440: ,X_TYPE_ID => 1
441: ,X_PRIORITY_CODE => null
442: ,X_INC_PROJ_PROGRESS_FLAG => 'N'
443: ,X_REQUEST_ID => null
473: ,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
474: );
475:
476:
477: IF cur_pa_tasks%ROWCOUNT = 1
478: THEN
479:
480: IF cur_pa_tasks2%ROWCOUNT = 1
481: THEN
476:
477: IF cur_pa_tasks%ROWCOUNT = 1
478: THEN
479:
480: IF cur_pa_tasks2%ROWCOUNT = 1
481: THEN
482: OPEN cur_proj_elem_vers;
483: FETCH cur_proj_elem_vers INTO x_struc_version_id;
484: CLOSE cur_proj_elem_vers;
489: l_ref_task_version_id := l_prev_top_task_ver_id;
490: l_peer_or_sub := 'PEER';
491: END IF;
492: ELSE
493: IF cur_pa_tasks_rec.wbs_level > l_old_wbs_level
494: THEN
495: l_peer_or_sub := 'SUB';
496: ELSIF cur_pa_tasks_rec.wbs_level = l_old_wbs_level
497: THEN
492: ELSE
493: IF cur_pa_tasks_rec.wbs_level > l_old_wbs_level
494: THEN
495: l_peer_or_sub := 'SUB';
496: ELSIF cur_pa_tasks_rec.wbs_level = l_old_wbs_level
497: THEN
498: l_peer_or_sub := 'PEER';
499: ELSE
500: OPEN parent_of_previous_task( l_ref_task_version_id );
515: ,x_msg_count => l_msg_count
516: ,x_msg_data => l_msg_data
517: );
518:
519: IF cur_pa_tasks%ROWCOUNT = 1
520: THEN
521: l_prev_top_task_ver_id := x_task_version_id;
522: END IF;
523:
528: raise api_error;
529: END IF;
530:
531: l_ref_task_version_id := x_task_version_id;
532: l_old_wbs_level := cur_pa_tasks_rec.wbs_level;
533:
534: -- IF p_struc_type = 'WORKPLAN'
535:
536: --if it is a workplan structure.
610: --
611: -- 14-DEC-01 MAansari -Created
612: --
613: -- Notes: This api is called from form PAXPREPR.fmb ON-INSERT of tasks block.
614: -- The call is in PA_TASKS_PKG.ON_INSERT API.
615:
616: PROCEDURE create_task_structure(
617: p_calling_module IN VARCHAR2 := 'FORMS'
618: ,p_project_id IN NUMBER
753: IS
754: SELECT element_version_id
755: FROM pa_proj_element_versions
756: WHERE project_id = p_project_id
757: AND object_type = 'PA_TASKS'
758: AND parent_structure_version_id = c_structure_version_id
759: AND ( ( proj_element_id = ( select top_task_id
760: from pa_tasks
761: where task_id = p_ref_task_id ) AND p_structure_type = 'FINANCIAL' )
756: WHERE project_id = p_project_id
757: AND object_type = 'PA_TASKS'
758: AND parent_structure_version_id = c_structure_version_id
759: AND ( ( proj_element_id = ( select top_task_id
760: from pa_tasks
761: where task_id = p_ref_task_id ) AND p_structure_type = 'FINANCIAL' )
762: --OR ( proj_element_id = p_ref_task_id AND p_structure_type = 'WORKPLAN' )
763: )
764: --bug 2852891
765: UNION
766: SELECT element_version_id
767: FROM pa_proj_element_versions
768: WHERE project_id = p_project_id
769: AND object_type = 'PA_TASKS'
770: AND parent_structure_version_id = c_structure_version_id
771: AND p_structure_type = 'WORKPLAN'
772: AND display_sequence = ( SELECT max( display_sequence )
773: FROM pa_proj_element_versions
771: AND p_structure_type = 'WORKPLAN'
772: AND display_sequence = ( SELECT max( display_sequence )
773: FROM pa_proj_element_versions
774: WHERE project_id = p_project_id
775: AND object_type = 'PA_TASKS'
776: AND parent_structure_version_id = c_structure_version_id
777: AND wbs_level = 1 --Since there is no parent task id passed therefore its always created as top task
778: AND display_sequence <= ( SELECT display_sequence
779: FROM pa_proj_element_versions
777: AND wbs_level = 1 --Since there is no parent task id passed therefore its always created as top task
778: AND display_sequence <= ( SELECT display_sequence
779: FROM pa_proj_element_versions
780: WHERE project_id = p_project_id
781: AND object_type = 'PA_TASKS'
782: AND parent_structure_version_id = c_structure_version_id
783: AND proj_element_id = p_ref_task_id )
784: )
785: --bug 2852891
789: IS
790: SELECT element_version_id
791: FROM pa_proj_element_versions
792: WHERE project_id = p_project_id
793: AND object_type = 'PA_TASKS'
794: AND parent_structure_version_id = c_structure_version_id
795: AND proj_element_id = p_ref_task_id
796: ;
797:
800: IS
801: SELECT element_version_id
802: FROM pa_proj_element_versions
803: WHERE project_id = p_project_id
804: AND object_type = 'PA_TASKS'
805: AND parent_structure_version_id = c_structure_version_id
806: and element_version_id in ( SELECT object_id_to1
807: FROM pa_object_relationships
808: WHERE object_id_from1 = c_parent_task_version_id
810: )
811: AND display_sequence = ( SELECT max( display_sequence )
812: FROM pa_proj_element_versions
813: WHERE project_id = p_project_id
814: AND object_type = 'PA_TASKS'
815: AND parent_structure_version_id = c_structure_version_id
816: and element_version_id in ( SELECT object_id_to1
817: FROM pa_object_relationships
818: WHERE object_id_from1 = c_parent_task_version_id
845: FROM pa_proj_element_versions
846: WHERE project_id = p_project_id
847: AND proj_element_id = c_task_id
848: AND parent_structure_version_id = c_structure_version_id
849: AND object_type = 'PA_TASKS';
850:
851: CURSOR cur_child_exists(c_task_ver_id NUMBER)
852: IS
853: select 'Y'
852: IS
853: select 'Y'
854: FROM pa_object_relationships
855: where object_id_from1 = c_task_ver_id
856: AND object_type_from IN ('PA_TASKS', 'PA_STRUCTURES') --Corrected 'PA_TASK' TO 'PA_TASKS' for Bug 4035526
857: and relationship_type = 'S';
858: l_child_exists VARCHAR2(1);
859:
860: -- xxlu added for DFF attributes for calling create_schedule_version
927: CURSOR get_parent_version_id(c_elem_ver_id NUMBER) IS
928: select object_id_from1
929: from pa_object_relationships
930: where object_id_to1 = c_elem_ver_id
931: and object_type_to = 'PA_TASKS'
932: and relationship_type = 'S';
933: l_parent_ver_id NUMBER;
934: l_error_msg_code VARCHAR2(255);
935: l_structure_version_id NUMBER;
1292: --For each project create 1 task
1293: PA_TASK_PUB1.create_task(
1294: p_calling_module => p_calling_module
1295: ,p_init_msg_list => l_init_msg_list
1296: ,p_object_type => 'PA_TASKS'
1297: ,p_project_id => p_project_id
1298: ,p_structure_id => l_structure_id --1 --anything. does not matter since its not gonna useful to create record
1299: --in pa_proj_elements in PA_TASK_PVT1.CREATE_TASK.
1300: ,p_task_number => l_task_number
2264: IS
2265: SELECT record_version_number
2266: FROM pa_proj_elements
2267: WHERE proj_element_id = p_task_id
2268: AND object_type = 'PA_TASKS';
2269:
2270: /* Bug 2680486 -- Performance changes -- Added d.project_id= c.project_id
2271: join in the following cursor*/
2272:
2302: AND d.element_version_id = c.element_version_id
2303: AND d.status_code = 'STRUCTURE_WORKING'
2304: ;
2305:
2306: CURSOR cur_pa_tasks( c_task_id NUMBER )
2307: IS
2308: SELECT parent_task_id
2309: FROM pa_tasks
2310: WHERE project_id = p_project_id
2305:
2306: CURSOR cur_pa_tasks( c_task_id NUMBER )
2307: IS
2308: SELECT parent_task_id
2309: FROM pa_tasks
2310: WHERE project_id = p_project_id
2311: AND task_id = c_task_id;
2312:
2313: CURSOR cur_pa_task_ver( c_structure_version_id NUMBER, c_task_id NUMBER )
2316: FROM pa_proj_element_versions
2317: WHERE project_id = p_project_id
2318: AND proj_element_id = c_task_id
2319: AND parent_structure_version_id = c_structure_version_id
2320: AND object_type = 'PA_TASKS';
2321:
2322: CURSOR cur_pa_peer_task_above( c_structure_version_id NUMBER, c_task_version_id NUMBER )
2323: IS
2324: SELECT element_version_id
2331: WHERE element_version_id = c_task_version_id )
2332: AND wbs_level = ( SELECT wbs_level FROM pa_proj_element_versions
2333: WHERE element_version_id = c_task_version_id )
2334: )
2335: AND object_type = 'PA_TASKS';
2336:
2337: CURSOR cur_proj_sch( c_task_version_id NUMBER )
2338: IS
2339: SELECT pev_schedule_id, record_version_number
2354: SELECT proj_element_id
2355: FROM pa_proj_element_versions
2356: WHERE project_id = p_project_id
2357: AND element_version_id = c_task_version_id
2358: AND object_type = 'PA_TASKS';
2359:
2360: CURSOR cur_pa_proj_elem_vers2( c_task_version_id NUMBER )
2361: IS
2362: SELECT 'x'
2429: CURSOR cur_task_attr IS
2430: SELECT pe.ELEMENT_NUMBER, pe.NAME
2431: FROM pa_proj_elements pe
2432: WHERE pe.proj_element_id = p_task_id
2433: AND pe.object_type = 'PA_TASKS';
2434:
2435: l_task_number pa_proj_elements.element_number%TYPE;
2436: l_task_name pa_proj_elements.name%TYPE;
2437:
2465: element_version_id,planned_effort,actual_start_date,actual_finish_date
2466: from pa_proj_elem_ver_schedule ppevs, pa_proj_elements ppe
2467: where ppevs.project_id = ppe.project_id
2468: and ppevs.proj_element_id = ppe.proj_element_id
2469: and ppe.object_type = 'PA_TASKS'
2470: and ppevs.project_id = p_project_id;
2471: */
2472: SELECT
2473: ppv.element_version_id
2479: ,pa_proj_element_versions ppv
2480: WHERE
2481: ppevs.project_id = ppv.project_id
2482: and ppevs.proj_element_id = ppv.proj_element_id
2483: and ppv.object_type = 'PA_TASKS'
2484: and ppv.element_version_id = ppevs.element_version_id
2485: and ppv.project_id = p_project_id
2486: and ppv.parent_structure_version_id = p_struct_ver_id;
2487:
2791: IF p_structure_type = 'FINANCIAL'
2792: THEN
2793: --get the parent of the tak id
2794: --rtarway, 3666023, replace by l_task_id
2795: --OPEN cur_pa_tasks( p_task_id );
2796: OPEN cur_pa_tasks( l_task_id );
2797: FETCH cur_pa_tasks INTO l_parent_task_id;
2798: CLOSE cur_pa_tasks;
2799:
2792: THEN
2793: --get the parent of the tak id
2794: --rtarway, 3666023, replace by l_task_id
2795: --OPEN cur_pa_tasks( p_task_id );
2796: OPEN cur_pa_tasks( l_task_id );
2797: FETCH cur_pa_tasks INTO l_parent_task_id;
2798: CLOSE cur_pa_tasks;
2799:
2800: --get the parent of the ref tak id
2793: --get the parent of the tak id
2794: --rtarway, 3666023, replace by l_task_id
2795: --OPEN cur_pa_tasks( p_task_id );
2796: OPEN cur_pa_tasks( l_task_id );
2797: FETCH cur_pa_tasks INTO l_parent_task_id;
2798: CLOSE cur_pa_tasks;
2799:
2800: --get the parent of the ref tak id
2801: OPEN cur_pa_tasks( p_ref_task_id );
2794: --rtarway, 3666023, replace by l_task_id
2795: --OPEN cur_pa_tasks( p_task_id );
2796: OPEN cur_pa_tasks( l_task_id );
2797: FETCH cur_pa_tasks INTO l_parent_task_id;
2798: CLOSE cur_pa_tasks;
2799:
2800: --get the parent of the ref tak id
2801: OPEN cur_pa_tasks( p_ref_task_id );
2802: FETCH cur_pa_tasks INTO l_parent_of_ref_task_id;
2797: FETCH cur_pa_tasks INTO l_parent_task_id;
2798: CLOSE cur_pa_tasks;
2799:
2800: --get the parent of the ref tak id
2801: OPEN cur_pa_tasks( p_ref_task_id );
2802: FETCH cur_pa_tasks INTO l_parent_of_ref_task_id;
2803: CLOSE cur_pa_tasks;
2804:
2805: ELSIF p_structure_type = 'WORKPLAN'
2798: CLOSE cur_pa_tasks;
2799:
2800: --get the parent of the ref tak id
2801: OPEN cur_pa_tasks( p_ref_task_id );
2802: FETCH cur_pa_tasks INTO l_parent_of_ref_task_id;
2803: CLOSE cur_pa_tasks;
2804:
2805: ELSIF p_structure_type = 'WORKPLAN'
2806: THEN
2799:
2800: --get the parent of the ref tak id
2801: OPEN cur_pa_tasks( p_ref_task_id );
2802: FETCH cur_pa_tasks INTO l_parent_of_ref_task_id;
2803: CLOSE cur_pa_tasks;
2804:
2805: ELSIF p_structure_type = 'WORKPLAN'
2806: THEN
2807: --get the parent task version id of the task version id
3515: CURSOR cur_task_ver_id IS
3516: SELECT element_version_id, record_version_number
3517: FROM pa_proj_element_versions ppev
3518: WHERE proj_element_id = p_task_id
3519: AND object_type = 'PA_TASKS'
3520: AND parent_structure_version_id = PA_PROJECT_STRUCTURE_UTILS.GET_LATEST_FIN_STRUC_VER_ID( p_project_id ); -- bug 2765115
3521:
3522: CURSOR cur_task_ver_id2 IS
3523: SELECT record_version_number
3522: CURSOR cur_task_ver_id2 IS
3523: SELECT record_version_number
3524: FROM pa_proj_element_versions ppev
3525: WHERE element_version_id = p_task_version_id
3526: AND object_type = 'PA_TASKS';
3527:
3528: -- bug 2765115
3529: CURSOR cur_pa_proj
3530: IS
3535: CURSOR cur_task_ver_id3 IS
3536: SELECT element_version_id, record_version_number
3537: FROM pa_proj_element_versions ppev
3538: WHERE proj_element_id = p_task_id
3539: AND object_type = 'PA_TASKS'
3540: AND project_id = p_project_id;
3541: -- bug 2765115
3542:
3543:
3665: CURSOR cur_task_ver( c_parent_structure_version_id NUMBER )
3666: IS
3667: SELECT rowid, element_version_id
3668: FROM pa_proj_element_versions
3669: WHERE OBJECT_TYPE = 'PA_TASKS'
3670: AND project_id = p_project_id
3671: AND parent_structure_version_id = c_parent_structure_version_id;
3672:
3673: CURSOR cur_proj_elems
4009: SELECT element_version_id
4010: FROM pa_proj_element_versions
4011: WHERE project_id = p_project_id
4012: AND parent_structure_version_id = c_parent_struc_ver_id
4013: AND object_type = 'PA_TASKS';
4014:
4015:
4016: l_structure_type_id NUMBER;
4017: l_proj_structure_type_id NUMBER;
4382: -- 25-MAY-02 MAansari -Created
4383: --
4384: -- Notes: This api is called from PA_PROJECT_PUB.CREATE_PROJECT to clean up the tasks in pa_proj_elements that were created
4385: -- by calling copy structure api. Copy structure api copies template's tasks to pro_elements though there
4386: -- are no pa_TASKs for the new project. However pa_tasks and pa_proj_elements must be in syn. To d this we need to firts remove
4387: -- tasks from pa_proj_elements as they were erroneously created by COPY_STRUCTURE and then call create_default_structure
4388: -- to syn up pa_pro_elements with pa_tasks.
4389: --
4390:
4384: -- Notes: This api is called from PA_PROJECT_PUB.CREATE_PROJECT to clean up the tasks in pa_proj_elements that were created
4385: -- by calling copy structure api. Copy structure api copies template's tasks to pro_elements though there
4386: -- are no pa_TASKs for the new project. However pa_tasks and pa_proj_elements must be in syn. To d this we need to firts remove
4387: -- tasks from pa_proj_elements as they were erroneously created by COPY_STRUCTURE and then call create_default_structure
4388: -- to syn up pa_pro_elements with pa_tasks.
4389: --
4390:
4391: PROCEDURE Clean_unwanted_tasks(
4392: p_project_id IN NUMBER
4417: SELECT element_version_id, proj_element_id
4418: FROM pa_proj_element_versions
4419: WHERE project_id = p_project_id
4420: AND parent_structure_version_id = c_parent_str_ver_id
4421: AND object_type = 'PA_TASKS';
4422:
4423: l_cur_pa_proj_elem_vers_rec cur_pa_proj_elem_vers%ROWTYPE;
4424: BEGIN
4425:
4559: SELECT proj_element_id
4560: FROM pa_proj_elements
4561: WHERE PM_SOURCE_REFERENCE = p_pm_parent_task_reference
4562: AND project_id = p_project_id
4563: AND OBJECT_TYPE = 'PA_TASKS';
4564: BEGIN
4565: x_return_status := FND_API.G_RET_STS_SUCCESS;
4566:
4567: OPEN cur_pa_proj_elems;
5482:
5483: -- use min(start_date) as pseudo original project start
5484: cursor c2 is
5485: select min(start_date) min_start
5486: from pa_tasks
5487: where project_id = x_orig_project_id;
5488:
5489: c2_rec c2%rowtype;
5490: x_delta NUMBER;
5679: FROM pa_proj_element_versions
5680: WHERE project_id = p_project_id
5681: AND proj_element_id = p_task_id
5682: AND parent_structure_version_id = c_parent_struc_ver_id
5683: AND object_type = 'PA_TASKS';
5684:
5685: CURSOR cur_pa_work_ver
5686: IS
5687: SELECT element_version_id
6030: ,P_PM_SOURCE_reference => p_tasks_in(i).pm_task_reference
6031: ,p_location_id => p_tasks_in(i).address_id
6032: ,p_manager_person_id => p_tasks_in(i).task_manager_person_id
6033: ,p_structure_version_id => p_structure_version_id
6034: ,p_create_task_version_only => 'Y' --we dont need to create tasks in pa_tasks or pa_proj_elements for the existing tasks.
6035: --All we need is just to create task versions under the new structure version.
6036: ,x_task_version_id => l_task_version_id
6037: ,x_task_id => l_task_id
6038: ,x_msg_count => x_msg_count
7126: ,'PA_PROJECTS'
7127: FROM pa_task_types ptt,
7128: pa_proj_elements ppe,
7129: pa_proj_element_versions ppev,
7130: pa_tasks pt
7131: WHERE pt.project_id = p_dest_project_id
7132: AND ppe.project_id = p_src_project_id --Added by Sunkalya For the fix of Bug#4600825. This change is done for R12
7133: --Merging of code fix done thru Bug#4589176(115.315) for performance issues.
7134: AND pt.task_number = substrb(rtrim(ppe.element_number),1,25) -- Substrb added for Bug 5152448
7131: WHERE pt.project_id = p_dest_project_id
7132: AND ppe.project_id = p_src_project_id --Added by Sunkalya For the fix of Bug#4600825. This change is done for R12
7133: --Merging of code fix done thru Bug#4589176(115.315) for performance issues.
7134: AND pt.task_number = substrb(rtrim(ppe.element_number),1,25) -- Substrb added for Bug 5152448
7135: AND ptt.object_type = 'PA_TASKS'
7136: AND ppe.type_id = ptt.task_type_id
7137: AND ppe.object_type = 'PA_TASKS'
7138: AND nvl(ppe.link_task_flag,'N') <> 'Y' -- 4348868
7139: AND ppe.parent_structure_id = ppev.proj_element_id
7133: --Merging of code fix done thru Bug#4589176(115.315) for performance issues.
7134: AND pt.task_number = substrb(rtrim(ppe.element_number),1,25) -- Substrb added for Bug 5152448
7135: AND ptt.object_type = 'PA_TASKS'
7136: AND ppe.type_id = ptt.task_type_id
7137: AND ppe.object_type = 'PA_TASKS'
7138: AND nvl(ppe.link_task_flag,'N') <> 'Y' -- 4348868
7139: AND ppe.parent_structure_id = ppev.proj_element_id
7140: AND ppev.element_version_id = l_src_fin_version_id ;
7141:
7215: pa_proj_elements ppe,
7216: pa_proj_element_versions ppev2,
7217: pa_proj_elem_ver_structure ppevs
7218: WHERE ppev.project_id = p_src_project_id
7219: AND ppev.object_type = 'PA_TASKS'
7220: AND ppev.parent_structure_version_id = l_src_fin_version_id
7221: AND ppev.proj_element_id = ppe.attribute15
7222: AND ppe.project_id = p_dest_project_id
7223: AND ppe.object_type = 'PA_TASKS'
7219: AND ppev.object_type = 'PA_TASKS'
7220: AND ppev.parent_structure_version_id = l_src_fin_version_id
7221: AND ppev.proj_element_id = ppe.attribute15
7222: AND ppe.project_id = p_dest_project_id
7223: AND ppe.object_type = 'PA_TASKS'
7224: AND ppev.parent_structure_version_id = ppev2.attribute15
7225: AND ppev2.project_id = p_dest_project_id
7226: AND ppev2.object_type = 'PA_STRUCTURES'
7227: AND ppevs.project_id = p_dest_project_id
7696: SELECT dest.attribute15 src_ppe_id,
7697: dest.proj_element_id dest_ppe_id
7698: FROM pa_proj_elements dest
7699: WHERE dest.project_id = p_dest_project_id
7700: AND dest.object_type = 'PA_TASKS'
7701: AND dest.parent_structure_id = c_dest_parent_structure_id ;
7702:
7703: --Bug 3387963 - Cursor to get destination and source project's organization id
7704: CURSOR cur_get_carrying_out_org IS
8137: ,source_object_id
8138: ,source_object_type
8139: )
8140: SELECT
8141: pa_tasks_s.nextval
8142: ,p_dest_project_id
8143: ,ppe.object_type
8144: ,pa_tasks_s.nextval --Set element_number same as proj_element_id
8145: ,l_name
8140: SELECT
8141: pa_tasks_s.nextval
8142: ,p_dest_project_id
8143: ,ppe.object_type
8144: ,pa_tasks_s.nextval --Set element_number same as proj_element_id
8145: ,l_name
8146: ,ppe.DESCRIPTION
8147: ,ppe.STATUS_CODE
8148: ,ppe.WF_STATUS_CODE
8394: /**** THE FOLLOWING SECTION COPIES/INSERTS WORKPLAN VERSIONS AND TASKS INTO THE DESTINATION PROJECT ****/
8395: ---------------------------------------------------------------------------------------------------------
8396: --IF 'ATLEAST ONE WP VER IS SELECTED' FOR COPYING THEN
8397: -- Copy selected version(s) and tasks belonging to these version(s)
8398: -- Call API to sync up pa_tasks with pa_proj_elements in case of SHARED structure
8399: --ELSE (if no WP ver selected for copying)
8400: -- IF 'SHARED' AND 'FINANCIAL TASKS FLAG IS CHECKED' THEN
8401: -- Copy financial structure version in the source project and tasks belonging to this version
8402: -- ENF IF;
9005: END IF;--If l_shared_make_working_copy IS NOT NULL
9006:
9007: --Create tasks in the selected WP versions
9008: IF 'Y' = l_shared THEN
9009: --If SHARED case, join with PA_TASKS table
9010: INSERT INTO pa_proj_elements(
9011: PROJ_ELEMENT_ID
9012: ,PROJECT_ID
9013: ,OBJECT_TYPE
9074: ,source_object_id
9075: ,source_object_type
9076: )
9077: SELECT
9078: nvl(pt.task_id,pa_tasks_s.nextval)
9079: ,p_dest_project_id
9080: ,ppe.object_type
9081: ,ppe.element_number
9082: ,ppe.name
9141: ,ppe.WF_START_LEAD_DAYS
9142: ,p_dest_project_id
9143: ,'PA_PROJECTS'
9144: FROM pa_task_types ptt,
9145: pa_tasks pt,
9146: pa_proj_elements ppe
9147: WHERE ptt.object_type = 'PA_TASKS'
9148: AND ppe.type_id = ptt.task_type_id
9149: AND nvl(ppe.link_task_flag,'N') <> 'Y' -- 4348868
9143: ,'PA_PROJECTS'
9144: FROM pa_task_types ptt,
9145: pa_tasks pt,
9146: pa_proj_elements ppe
9147: WHERE ptt.object_type = 'PA_TASKS'
9148: AND ppe.type_id = ptt.task_type_id
9149: AND nvl(ppe.link_task_flag,'N') <> 'Y' -- 4348868
9150: AND pt.task_number (+) = substrb(rtrim(ppe.element_number),1,25) -- Substrb added for Bug 5152448
9151: AND pt.project_id (+) = p_dest_project_id
9155: PA_PROJECT_COPY_OPTIONS_TMP ppcot
9156: WHERE ppcot.CONTEXT = 'WORKPLAN'
9157: AND ppev.parent_structure_version_id = ppcot.version_id
9158: AND ppev.project_id = p_src_project_id
9159: AND ppev.object_type = 'PA_TASKS' ) ;
9160: ELSE
9161: --If SPLIT case, there should be no join with PA_TASKS table
9162: INSERT INTO pa_proj_elements(
9163: PROJ_ELEMENT_ID
9157: AND ppev.parent_structure_version_id = ppcot.version_id
9158: AND ppev.project_id = p_src_project_id
9159: AND ppev.object_type = 'PA_TASKS' ) ;
9160: ELSE
9161: --If SPLIT case, there should be no join with PA_TASKS table
9162: INSERT INTO pa_proj_elements(
9163: PROJ_ELEMENT_ID
9164: ,PROJECT_ID
9165: ,OBJECT_TYPE
9226: ,source_object_id
9227: ,source_object_type
9228: )
9229: SELECT
9230: pa_tasks_s.nextval
9231: ,p_dest_project_id
9232: ,ppe.object_type
9233: ,ppe.element_number
9234: ,ppe.name
9294: ,p_dest_project_id
9295: ,'PA_PROJECTS'
9296: FROM pa_task_types ptt,
9297: pa_proj_elements ppe
9298: WHERE ptt.object_type = 'PA_TASKS'
9299: AND ppe.type_id = ptt.task_type_id
9300: AND nvl(ppe.link_task_flag,'N') <> 'Y' -- 4348868
9301: AND ppe.proj_element_id IN
9302: ( SELECT distinct ppev.proj_element_id
9304: PA_PROJECT_COPY_OPTIONS_TMP ppcot
9305: WHERE ppcot.CONTEXT = 'WORKPLAN'
9306: AND ppev.parent_structure_version_id = ppcot.version_id
9307: AND ppev.project_id = p_src_project_id
9308: AND ppev.object_type = 'PA_TASKS' ) ;
9309: END IF;
9310:
9311: --Copy tasks versions for tasks belonging to the selected wp versions
9312: INSERT INTO pa_proj_element_versions(
9384: pa_proj_element_versions ppev,
9385: pa_proj_element_versions ppev2,
9386: pa_proj_elem_ver_structure ppevs
9387: WHERE ppe.project_id = p_dest_project_id
9388: AND ppe.object_type = 'PA_TASKS'
9389: AND ppe.attribute15 = ppev.proj_element_id
9390: AND ppev.project_id = p_src_project_id
9391: AND ppev.object_type = 'PA_TASKS'
9392: AND ppev.parent_structure_version_id = ppev2.attribute15
9387: WHERE ppe.project_id = p_dest_project_id
9388: AND ppe.object_type = 'PA_TASKS'
9389: AND ppe.attribute15 = ppev.proj_element_id
9390: AND ppev.project_id = p_src_project_id
9391: AND ppev.object_type = 'PA_TASKS'
9392: AND ppev.parent_structure_version_id = ppev2.attribute15
9393: AND ppev2.project_id = p_dest_project_id
9394: AND ppev2.object_type = 'PA_STRUCTURES'
9395: AND ppevs.element_version_id = ppev2.element_version_id
9697: FETCH cur_get_shared_ver_id INTO l_shared_dest_ver_id;
9698: CLOSE cur_get_shared_ver_id;
9699:
9700: IF l_debug_mode = 'Y' THEN
9701: Pa_Debug.WRITE(G_PKG_NAME, 'Calling pa_tasks sync up API for struc version :'||l_shared_dest_ver_id,
9702: l_debug_level3);
9703: END IF;
9704:
9705: --Call API to sync up tasks in pa_tasks table, with the new (shared) version [WP version copied from src]
9701: Pa_Debug.WRITE(G_PKG_NAME, 'Calling pa_tasks sync up API for struc version :'||l_shared_dest_ver_id,
9702: l_debug_level3);
9703: END IF;
9704:
9705: --Call API to sync up tasks in pa_tasks table, with the new (shared) version [WP version copied from src]
9706: PA_TASKS_MAINT_PUB.SYNC_UP_WP_TASKS_WITH_FIN(
9707: p_project_id => p_dest_project_id
9708: , p_structure_version_id => l_shared_dest_ver_id
9709: , p_mode => 'ALL'
9702: l_debug_level3);
9703: END IF;
9704:
9705: --Call API to sync up tasks in pa_tasks table, with the new (shared) version [WP version copied from src]
9706: PA_TASKS_MAINT_PUB.SYNC_UP_WP_TASKS_WITH_FIN(
9707: p_project_id => p_dest_project_id
9708: , p_structure_version_id => l_shared_dest_ver_id
9709: , p_mode => 'ALL'
9710: , x_return_status => l_return_status
10108: IF ('Y' = l_min_one_wp_ver_sel AND 'Y' = l_wp_task_attach_flag )
10109: OR ('Y' = l_fin_tasks_flag AND 'Y' = l_fn_task_attach_flag ) THEN
10110: FOR rec_copied_tasks IN cur_get_copied_tasks( l_dest_shared_struc_id ) LOOP
10111: FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS(
10112: 'PA_TASKS',
10113: rec_copied_tasks.src_ppe_id,
10114: null, null, null, null,
10115: 'PA_TASKS',
10116: rec_copied_tasks.dest_ppe_id,
10111: FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS(
10112: 'PA_TASKS',
10113: rec_copied_tasks.src_ppe_id,
10114: null, null, null, null,
10115: 'PA_TASKS',
10116: rec_copied_tasks.dest_ppe_id,
10117: null, null, null, null,
10118: FND_GLOBAL.USER_ID,
10119: FND_GLOBAL.LOGIN_ID,
10124: ELSE
10125: IF 'Y' = l_wp_task_attach_flag THEN
10126: FOR rec_copied_tasks IN cur_get_copied_tasks( l_dest_wp_structure_id ) LOOP
10127: FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS(
10128: 'PA_TASKS',
10129: rec_copied_tasks.src_ppe_id,
10130: null, null, null, null,
10131: 'PA_TASKS',
10132: rec_copied_tasks.dest_ppe_id,
10127: FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS(
10128: 'PA_TASKS',
10129: rec_copied_tasks.src_ppe_id,
10130: null, null, null, null,
10131: 'PA_TASKS',
10132: rec_copied_tasks.dest_ppe_id,
10133: null, null, null, null,
10134: FND_GLOBAL.USER_ID,
10135: FND_GLOBAL.LOGIN_ID,
10140:
10141: IF 'Y' = l_fn_task_attach_flag THEN
10142: FOR rec_copied_tasks IN cur_get_copied_tasks( l_dest_fn_structure_id ) LOOP
10143: FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS(
10144: 'PA_TASKS',
10145: rec_copied_tasks.src_ppe_id,
10146: null, null, null, null,
10147: 'PA_TASKS',
10148: rec_copied_tasks.dest_ppe_id,
10143: FND_ATTACHED_DOCUMENTS2_PKG.COPY_ATTACHMENTS(
10144: 'PA_TASKS',
10145: rec_copied_tasks.src_ppe_id,
10146: null, null, null, null,
10147: 'PA_TASKS',
10148: rec_copied_tasks.dest_ppe_id,
10149: null, null, null, null,
10150: FND_GLOBAL.USER_ID,
10151: FND_GLOBAL.LOGIN_ID,
10769: FROM pa_proj_elements orig, pa_proj_elements new
10770: WHERE orig.project_id = p_src_project_id
10771: AND new.element_number = orig.element_number
10772: AND new.project_id = p_dest_project_id
10773: AND new.object_type = 'PA_TASKS'
10774: AND orig.object_type = 'PA_TASKS'
10775: AND orig.parent_structure_id = c_orig_parent_structure_id
10776: AND new.parent_structure_id = c_new_parent_structure_id
10777: ;
10770: WHERE orig.project_id = p_src_project_id
10771: AND new.element_number = orig.element_number
10772: AND new.project_id = p_dest_project_id
10773: AND new.object_type = 'PA_TASKS'
10774: AND orig.object_type = 'PA_TASKS'
10775: AND orig.parent_structure_id = c_orig_parent_structure_id
10776: AND new.parent_structure_id = c_new_parent_structure_id
10777: ;
10778:
10950: ,WF_PROCESS
10951: ,WF_START_LEAD_DAYS
10952: )
10953: SELECT
10954: pa_tasks_s.nextval
10955: ,p_dest_project_id
10956: ,ppe.object_type
10957: ,ppe.element_number
10958: ,DECODE( ppe.object_type, 'PA_STRUCTURES',l_name, ppe.name )
11082: THEN
11083: FOR cur_pa_struct_rec in cur_pa_struct LOOP
11084: IF cur_pa_struct_rec.structure_type_id = 6 --Financial
11085: THEN
11086: --call PA_PROJECT_CORE2.copy_task to copy PA_TASKSi tasks.
11087:
11088: pa_project_core2.copy_task (
11089: x_orig_project_id => p_src_project_id ,
11090: x_new_project_id => p_dest_project_id,
11231: ,ppe.WF_ITEM_TYPE
11232: ,ppe.WF_PROCESS
11233: ,ppe.WF_START_LEAD_DAYS
11234: FROM pa_proj_elements ppe,
11235: pa_tasks pt,
11236: pa_task_types ptt --bug 2827090
11237: WHERE ppe.project_id = p_src_project_id
11238: AND pt.project_id = p_dest_project_id
11239: AND pt.task_number = substrb(rtrim(ppe.element_number),1,25) -- Substrb added for Bug 5152448
11237: WHERE ppe.project_id = p_src_project_id
11238: AND pt.project_id = p_dest_project_id
11239: AND pt.task_number = substrb(rtrim(ppe.element_number),1,25) -- Substrb added for Bug 5152448
11240: AND ppe.parent_structure_id = cur_pa_struct_rec.attribute15
11241: and ppe.object_type = 'PA_TASKS'
11242: and ppe.type_id = ptt.task_type_id --bug 2827090
11243: ;
11244:
11245: l_financial_structure_id := cur_pa_struct_rec.proj_element_id;
11242: and ppe.type_id = ptt.task_type_id --bug 2827090
11243: ;
11244:
11245: l_financial_structure_id := cur_pa_struct_rec.proj_element_id;
11246: ---update task reference in pa_tasks for all the
11247: update pa_tasks
11248: set PM_TASK_REFERENCE = task_number
11249: where project_id = p_dest_project_id
11250: and pm_task_reference IS NULL
11243: ;
11244:
11245: l_financial_structure_id := cur_pa_struct_rec.proj_element_id;
11246: ---update task reference in pa_tasks for all the
11247: update pa_tasks
11248: set PM_TASK_REFERENCE = task_number
11249: where project_id = p_dest_project_id
11250: and pm_task_reference IS NULL
11251: ;
11258: FETCH c_attach_tasks INTO c_atch ;
11259: if c_attach_tasks%notfound then
11260: exit ;
11261: end if;
11262: fnd_attached_documents2_pkg.copy_attachments('PA_TASKS',
11263: c_atch.orig_task_id,
11264: null, null, null, null,
11265: 'PA_TASKS',
11266: c_atch.new_task_id,
11261: end if;
11262: fnd_attached_documents2_pkg.copy_attachments('PA_TASKS',
11263: c_atch.orig_task_id,
11264: null, null, null, null,
11265: 'PA_TASKS',
11266: c_atch.new_task_id,
11267: null, null, null, null,
11268: FND_GLOBAL.USER_ID,
11269: FND_GLOBAL.LOGIN_ID,
11286: WHERE proj_element_id = l_financial_structure_id
11287: AND project_id = p_dest_project_id
11288: AND PM_SOURCE_CODE IS NULL;
11289:
11290: ---update source reference in pa_tasks for all the
11291: Update pa_tasks
11292: set PM_PRODUCT_CODE = ( SELECT PM_SOURCE_CODE FROM pa_proj_elements
11293: WHERE project_id = p_dest_project_id
11294: AND object_type = 'PA_STRUCTURES'
11287: AND project_id = p_dest_project_id
11288: AND PM_SOURCE_CODE IS NULL;
11289:
11290: ---update source reference in pa_tasks for all the
11291: Update pa_tasks
11292: set PM_PRODUCT_CODE = ( SELECT PM_SOURCE_CODE FROM pa_proj_elements
11293: WHERE project_id = p_dest_project_id
11294: AND object_type = 'PA_STRUCTURES'
11295: AND proj_element_id = cur_pa_struct_rec.proj_element_id
12359: WHERE ppev.project_id = p_src_project_id
12360: and ppe.project_id = p_dest_project_id
12361: and ppev.parent_structure_version_id = l_dest_str_versions_tbl(k).src_str_version_id
12362: and ppev.proj_element_id = ppe.attribute15
12363: and ppe.object_type = 'PA_TASKS'
12364: and ppev.object_type = 'PA_TASKS';
12365:
12366: --Create relationships for structure and tasks
12367:
12360: and ppe.project_id = p_dest_project_id
12361: and ppev.parent_structure_version_id = l_dest_str_versions_tbl(k).src_str_version_id
12362: and ppev.proj_element_id = ppe.attribute15
12363: and ppe.object_type = 'PA_TASKS'
12364: and ppev.object_type = 'PA_TASKS';
12365:
12366: --Create relationships for structure and tasks
12367:
12368: /* Start of changes for bug 6211012 */
13315: WHERE p.project_id = c_project_id;
13316:
13317: --The following cursor will be used when the structure type is
13318: --Financial as some of the attributes have been derived and updated
13319: --in the pa_tasks table and we need to use them for structures processing.
13320: CURSOR cur_pa_tasks(c_project_id pa_projects_all.project_id%TYPE,c_task_id pa_tasks.task_id%TYPE)
13321: IS
13322: SELECT task_number, long_task_name, description,
13323: parent_task_id,carrying_out_organization_id,
13316:
13317: --The following cursor will be used when the structure type is
13318: --Financial as some of the attributes have been derived and updated
13319: --in the pa_tasks table and we need to use them for structures processing.
13320: CURSOR cur_pa_tasks(c_project_id pa_projects_all.project_id%TYPE,c_task_id pa_tasks.task_id%TYPE)
13321: IS
13322: SELECT task_number, long_task_name, description,
13323: parent_task_id,carrying_out_organization_id,
13324: pm_task_reference, address_id, task_manager_person_id
13321: IS
13322: SELECT task_number, long_task_name, description,
13323: parent_task_id,carrying_out_organization_id,
13324: pm_task_reference, address_id, task_manager_person_id
13325: FROM pa_tasks
13326: WHERE project_id = c_project_id
13327: AND task_id = c_task_id;
13328:
13329: l_cur_pa_tasks_rec cur_pa_tasks%ROWTYPE; -- userd only for create task case
13325: FROM pa_tasks
13326: WHERE project_id = c_project_id
13327: AND task_id = c_task_id;
13328:
13329: l_cur_pa_tasks_rec cur_pa_tasks%ROWTYPE; -- userd only for create task case
13330:
13331: --ADUT. This cursor is required to identify if the source is a project / template.
13332: Cursor cur_template_flag(c_proj_id pa_projects_all.project_id%TYPE)
13333: Is
13336: where project_id = c_proj_id;
13337:
13338: -- IUP: Cursors added for changes done to this api so it can be called from update_project
13339: --Cursor to get the record version number of the task record which needs to be updated in pa_proj_elements table
13340: CURSOR cur_rec_ver_num(c_task_id pa_tasks.task_id%TYPE)
13341: IS
13342: SELECT record_version_number
13343: FROM pa_proj_elements
13344: WHERE proj_element_id = c_task_id
13341: IS
13342: SELECT record_version_number
13343: FROM pa_proj_elements
13344: WHERE proj_element_id = c_task_id
13345: AND object_type = 'PA_TASKS';
13346:
13347: --Cursor to get the task version id when task id and structure version id is known
13348: CURSOR cur_pa_task_ver( c_structure_version_id pa_proj_element_versions.parent_structure_version_id%TYPE, c_task_id pa_tasks.task_id%TYPE )
13349: IS
13344: WHERE proj_element_id = c_task_id
13345: AND object_type = 'PA_TASKS';
13346:
13347: --Cursor to get the task version id when task id and structure version id is known
13348: CURSOR cur_pa_task_ver( c_structure_version_id pa_proj_element_versions.parent_structure_version_id%TYPE, c_task_id pa_tasks.task_id%TYPE )
13349: IS
13350: SELECT element_version_id, record_version_number
13351: FROM pa_proj_element_versions
13352: WHERE project_id = p_project_id
13351: FROM pa_proj_element_versions
13352: WHERE project_id = p_project_id
13353: AND proj_element_id = c_task_id
13354: AND parent_structure_version_id = c_structure_version_id
13355: AND object_type = 'PA_TASKS';
13356:
13357: --Cursor to get the parent version id of the task version id
13358: CURSOR cur_parent_object_rel( c_task_version_id pa_proj_elem_ver_schedule.element_version_id%TYPE )
13359: IS
13375:
13376: l_ver_sch_attr_rec cur_ver_sch_attr_rec%ROWTYPE;
13377:
13378: --Cursor to get the task type associated with the task id
13379: CURSOR cur_task_type_id(c_task_id pa_tasks.task_id%TYPE)
13380: IS
13381: SELECT type_id
13382: FROM pa_proj_elements
13383: WHERE proj_element_id = c_task_id
13398: IS
13399: Select element_version_id
13400: from pa_proj_element_versions
13401: where parent_structure_version_id = c_structure_version_id
13402: and object_type = 'PA_TASKS';
13403:
13404: --Cursor added for bug 3057575 :
13405: --Cursor to get the task number from task ver id
13406: CURSOR cur_ver_id_to_task_num( c_task_version_id pa_proj_element_versions.element_version_id%TYPE )
13426: TYPE task_version_id_tbl IS TABLE OF pa_proj_element_versions.element_version_id%TYPE
13427: INDEX BY BINARY_INTEGER;
13428:
13429: ----Table Type added for bug 3057575 :
13430: TYPE task_parent_id_tbl_type IS TABLE OF pa_tasks.task_id%TYPE
13431: INDEX BY BINARY_INTEGER;
13432: ----Table Type added for bug 3057575 :
13433: TYPE task_ver_id_passed_tbl_type IS TABLE OF pa_proj_element_versions.element_version_id%TYPE
13434: INDEX BY BINARY_INTEGER;
13458: -- rec that contains all tasks information.
13459: l_task_in_rec pa_project_pub.task_in_rec_type;
13460:
13461: -- proj element level variables
13462: l_task_id pa_tasks.task_id%TYPE; -- task id of the current task being processed in loop
13463: l_parent_task_id pa_tasks.parent_task_id%TYPE; -- parent task id of the current task being processed in loop
13464: l_task_version_id pa_proj_element_versions.element_version_id%TYPE; -- stores version id of current task being processed
13465:
13466: -- version level variables required only for update task case.
13459: l_task_in_rec pa_project_pub.task_in_rec_type;
13460:
13461: -- proj element level variables
13462: l_task_id pa_tasks.task_id%TYPE; -- task id of the current task being processed in loop
13463: l_parent_task_id pa_tasks.parent_task_id%TYPE; -- parent task id of the current task being processed in loop
13464: l_task_version_id pa_proj_element_versions.element_version_id%TYPE; -- stores version id of current task being processed
13465:
13466: -- version level variables required only for update task case.
13467: l_proj_elem_ver_rvn pa_proj_element_versions.record_version_number%TYPE; -- record version number of proj element table
13545: task_ver_id_passed_tbl task_ver_id_passed_tbl_type;
13546: l_temp_count NUMBER;
13547: l_temp_success VARCHAR2(1);
13548: l_hierarchy_count NUMBER;
13549: -- l_err_task_number pa_tasks.task_number%TYPE; bug 6193314
13550: l_err_task_number pa_proj_elements.element_number%TYPE;
13551: l_shared VARCHAR(1) := 'N'; -- Added for Bug#3451073
13552:
13553: /* Bug # 3420093 - Adding Progress Management modifications to create_project and update_project flow. */
13587: element_version_id,planned_effort,actual_start_date,actual_finish_date
13588: from pa_proj_elem_ver_schedule ppevs, pa_proj_elements ppe
13589: where ppevs.project_id = ppe.project_id
13590: and ppevs.proj_element_id = ppe.proj_element_id
13591: and ppe.object_type = 'PA_TASKS'
13592: and ppevs.project_id = p_project_id;
13593: /** cursor added by aditi for bug 4120380. This is added to verify if the task_number is being modified by AMG Update_project script,
13594: Only then we need to prefix the task_number with a '-', so as to prevent primary key violation, and sucessful update.**/
13595: /*bug#5243018 : Reverting the fix in Bug 4120380
14380: l_debug_level3);
14381: END IF;
14382:
14383: -- This block does following:
14384: -- 1. Populate l_cur_pa_tasks_rec which has task_number, long_task_name, description, parent_task_id
14385: -- carrying_out_organization_id, pm_task_reference, address_id, task_manager_person_id
14386: -- 2. l_parent_task_id
14387: -- 3. l_task_id -- the task that is being processed.
14388: -- l_proj_elem_rvn of record of pa_proj_elements to be updated
14398:
14399: -- Derive the task number for the current task
14400: IF l_task_in_rec.pa_task_number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR OR l_task_in_rec.pa_task_number IS NULL
14401: THEN
14402: l_cur_pa_tasks_rec.task_number := l_task_in_rec.pm_task_reference;
14403: -- ELSE Bug 6193314 need to comment out
14404: /* bug#5243018 : Reverting the fix in Bug 4120380
14405: l_cur_pa_tasks_rec.task_number := '-'||l_task_in_rec.pa_task_number; -- Bug 4120380 aditi l_task_in_rec.pa_task_number; */
14406: -- l_cur_pa_tasks_rec.task_number := l_task_in_rec.pa_task_number; -- Bug 6193314 need to comment out
14401: THEN
14402: l_cur_pa_tasks_rec.task_number := l_task_in_rec.pm_task_reference;
14403: -- ELSE Bug 6193314 need to comment out
14404: /* bug#5243018 : Reverting the fix in Bug 4120380
14405: l_cur_pa_tasks_rec.task_number := '-'||l_task_in_rec.pa_task_number; -- Bug 4120380 aditi l_task_in_rec.pa_task_number; */
14406: -- l_cur_pa_tasks_rec.task_number := l_task_in_rec.pa_task_number; -- Bug 6193314 need to comment out
14407: /* any new task being created will also be prefixed with '-'. This is for the case below:
14408: 1.0
14409: 1.1
14402: l_cur_pa_tasks_rec.task_number := l_task_in_rec.pm_task_reference;
14403: -- ELSE Bug 6193314 need to comment out
14404: /* bug#5243018 : Reverting the fix in Bug 4120380
14405: l_cur_pa_tasks_rec.task_number := '-'||l_task_in_rec.pa_task_number; -- Bug 4120380 aditi l_task_in_rec.pa_task_number; */
14406: -- l_cur_pa_tasks_rec.task_number := l_task_in_rec.pa_task_number; -- Bug 6193314 need to comment out
14407: /* any new task being created will also be prefixed with '-'. This is for the case below:
14408: 1.0
14409: 1.1
14410: 2.0 .
14414: -- Derive the parent task id
14415: IF (l_task_in_rec.pa_parent_task_id IS NOT NULL
14416: AND l_task_in_rec.pa_parent_task_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM)
14417: THEN
14418: l_cur_pa_tasks_rec.parent_task_id := l_task_in_rec.pa_parent_task_id;
14419: ELSIF (l_task_in_rec.pm_parent_task_reference <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
14420: AND l_task_in_rec.pm_parent_task_reference IS NOT NULL)
14421: THEN
14422: --convert pm_parent_task_reference to parent_task_id.
14430:
14431: -- PA_PROJ_TASK_STRUC_PUB.convert_pm_parent_task_ref(
14432: -- p_pm_parent_task_reference => l_task_in_rec.pm_parent_task_reference
14433: -- ,p_project_id => p_project_id
14434: -- ,x_parent_task_id => l_cur_pa_tasks_rec.parent_task_id
14435: -- ,x_return_status => x_return_status
14436: -- );
14437:
14438: PA_PROJECT_PVT.Convert_pm_taskref_to_id_all (
14438: PA_PROJECT_PVT.Convert_pm_taskref_to_id_all (
14439: p_pa_project_id => p_project_id
14440: , p_structure_type => 'WORKPLAN'
14441: -- 3721124 p_pa_task_id will be null in this context
14442: -- , p_pa_task_id => l_cur_pa_tasks_rec.parent_task_id
14443: , p_pm_task_reference => l_task_in_rec.pm_parent_task_reference
14444: , p_out_task_id => l_cur_pa_tasks_rec.parent_task_id
14445: , p_return_status => x_return_status );
14446:
14440: , p_structure_type => 'WORKPLAN'
14441: -- 3721124 p_pa_task_id will be null in this context
14442: -- , p_pa_task_id => l_cur_pa_tasks_rec.parent_task_id
14443: , p_pm_task_reference => l_task_in_rec.pm_parent_task_reference
14444: , p_out_task_id => l_cur_pa_tasks_rec.parent_task_id
14445: , p_return_status => x_return_status );
14446:
14447: px_tasks_out_tbl(i).return_status := x_return_status;
14448: IF x_return_status <> FND_API.G_RET_STS_SUCCESS
14454: ,p_msg_attribute => 'CHANGE'
14455: ,p_resize_flag => 'N'
14456: ,p_msg_context => 'TASK'
14457: ,p_attribute1 => l_amg_segment1
14458: ,p_attribute2 => l_cur_pa_tasks_rec.task_number
14459: ,p_attribute3 => ''
14460: ,p_attribute4 => ''
14461: ,p_attribute5 => '');
14462: END IF;
14463: RAISE Invalid_Arg_Exc_WP;
14464: END IF;
14465: ELSE -- no parent task information provided for the current task.
14466: -- Current task is a top task. parent id is null.
14467: l_cur_pa_tasks_rec.parent_task_id := null;
14468: END IF;
14469:
14470: -- Included the condition during UT to avoid precision errors.
14471: IF l_task_in_rec.carrying_out_organization_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
14468: END IF;
14469:
14470: -- Included the condition during UT to avoid precision errors.
14471: IF l_task_in_rec.carrying_out_organization_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
14472: l_cur_pa_tasks_rec.carrying_out_organization_id := NULL;
14473: ELSE
14474: l_cur_pa_tasks_rec.carrying_out_organization_id := l_task_in_rec.carrying_out_organization_id;
14475: END IF;
14476:
14470: -- Included the condition during UT to avoid precision errors.
14471: IF l_task_in_rec.carrying_out_organization_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
14472: l_cur_pa_tasks_rec.carrying_out_organization_id := NULL;
14473: ELSE
14474: l_cur_pa_tasks_rec.carrying_out_organization_id := l_task_in_rec.carrying_out_organization_id;
14475: END IF;
14476:
14477: -- Included the condition during UT to avoid precision errors.
14478: IF l_task_in_rec.address_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
14475: END IF;
14476:
14477: -- Included the condition during UT to avoid precision errors.
14478: IF l_task_in_rec.address_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
14479: l_cur_pa_tasks_rec.address_id := NULL;
14480: ELSE
14481: l_cur_pa_tasks_rec.address_id := l_task_in_rec.address_id;
14482: END IF;
14483: -- Included the condition during UT to avoid precision errors.
14477: -- Included the condition during UT to avoid precision errors.
14478: IF l_task_in_rec.address_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
14479: l_cur_pa_tasks_rec.address_id := NULL;
14480: ELSE
14481: l_cur_pa_tasks_rec.address_id := l_task_in_rec.address_id;
14482: END IF;
14483: -- Included the condition during UT to avoid precision errors.
14484: IF l_task_in_rec.task_manager_person_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
14485: l_cur_pa_tasks_rec.task_manager_person_id := NULL;
14481: l_cur_pa_tasks_rec.address_id := l_task_in_rec.address_id;
14482: END IF;
14483: -- Included the condition during UT to avoid precision errors.
14484: IF l_task_in_rec.task_manager_person_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
14485: l_cur_pa_tasks_rec.task_manager_person_id := NULL;
14486: ELSE
14487: l_cur_pa_tasks_rec.task_manager_person_id := l_task_in_rec.task_manager_person_id;
14488: END IF;
14489: l_task_id := NULL; -- null for workplan create task case.
14483: -- Included the condition during UT to avoid precision errors.
14484: IF l_task_in_rec.task_manager_person_id = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM THEN
14485: l_cur_pa_tasks_rec.task_manager_person_id := NULL;
14486: ELSE
14487: l_cur_pa_tasks_rec.task_manager_person_id := l_task_in_rec.task_manager_person_id;
14488: END IF;
14489: l_task_id := NULL; -- null for workplan create task case.
14490: l_cur_pa_tasks_rec.pm_task_reference := l_task_in_rec.pm_task_reference;
14491: /*Commented for bug 2982057 l_cur_pa_tasks_rec.long_task_name := l_task_in_rec.long_task_name;*/
14486: ELSE
14487: l_cur_pa_tasks_rec.task_manager_person_id := l_task_in_rec.task_manager_person_id;
14488: END IF;
14489: l_task_id := NULL; -- null for workplan create task case.
14490: l_cur_pa_tasks_rec.pm_task_reference := l_task_in_rec.pm_task_reference;
14491: /*Commented for bug 2982057 l_cur_pa_tasks_rec.long_task_name := l_task_in_rec.long_task_name;*/
14492: /* Code addition for bug 2982057 starts */
14493:
14494: if l_task_in_rec.long_task_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR OR l_task_in_rec.long_task_name IS NULL then
14487: l_cur_pa_tasks_rec.task_manager_person_id := l_task_in_rec.task_manager_person_id;
14488: END IF;
14489: l_task_id := NULL; -- null for workplan create task case.
14490: l_cur_pa_tasks_rec.pm_task_reference := l_task_in_rec.pm_task_reference;
14491: /*Commented for bug 2982057 l_cur_pa_tasks_rec.long_task_name := l_task_in_rec.long_task_name;*/
14492: /* Code addition for bug 2982057 starts */
14493:
14494: if l_task_in_rec.long_task_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR OR l_task_in_rec.long_task_name IS NULL then
14495: l_cur_pa_tasks_rec.long_task_name := l_task_in_rec.task_name;
14491: /*Commented for bug 2982057 l_cur_pa_tasks_rec.long_task_name := l_task_in_rec.long_task_name;*/
14492: /* Code addition for bug 2982057 starts */
14493:
14494: if l_task_in_rec.long_task_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR OR l_task_in_rec.long_task_name IS NULL then
14495: l_cur_pa_tasks_rec.long_task_name := l_task_in_rec.task_name;
14496: else
14497: l_cur_pa_tasks_rec.long_task_name := l_task_in_rec.long_task_name;
14498: end if;
14499:
14493:
14494: if l_task_in_rec.long_task_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR OR l_task_in_rec.long_task_name IS NULL then
14495: l_cur_pa_tasks_rec.long_task_name := l_task_in_rec.task_name;
14496: else
14497: l_cur_pa_tasks_rec.long_task_name := l_task_in_rec.long_task_name;
14498: end if;
14499:
14500: /* Code addition for bug 2982057 ends */
14501:
14498: end if;
14499:
14500: /* Code addition for bug 2982057 ends */
14501:
14502: l_cur_pa_tasks_rec.description := l_task_in_rec.task_description;
14503:
14504: ELSE -- structure type is financial
14505: IF l_debug_mode = 'Y' THEN
14506: pa_debug.g_err_stage:= 'Task Id in out table: '||px_tasks_out_tbl(i).pa_task_id;
14508: l_debug_level3);
14509: END IF;
14510:
14511: l_task_id := px_tasks_out_tbl(i).pa_task_id;
14512: open cur_pa_tasks(p_project_id,l_task_id);
14513: fetch cur_pa_tasks into l_cur_pa_tasks_rec;
14514: close cur_pa_tasks;
14515:
14516: IF l_debug_mode = 'Y' THEN
14509: END IF;
14510:
14511: l_task_id := px_tasks_out_tbl(i).pa_task_id;
14512: open cur_pa_tasks(p_project_id,l_task_id);
14513: fetch cur_pa_tasks into l_cur_pa_tasks_rec;
14514: close cur_pa_tasks;
14515:
14516: IF l_debug_mode = 'Y' THEN
14517: pa_debug.g_err_stage:= 'Task Id in local var '|| l_task_id;
14510:
14511: l_task_id := px_tasks_out_tbl(i).pa_task_id;
14512: open cur_pa_tasks(p_project_id,l_task_id);
14513: fetch cur_pa_tasks into l_cur_pa_tasks_rec;
14514: close cur_pa_tasks;
14515:
14516: IF l_debug_mode = 'Y' THEN
14517: pa_debug.g_err_stage:= 'Task Id in local var '|| l_task_id;
14518: pa_debug.write(l_module_name,pa_debug.g_err_stage,
14522: /* bug#5243018 : Reverting the fix in Bug 4120380
14523: IF l_task_in_rec.pa_task_number IS NOT NULL AND
14524: l_task_in_rec.pa_task_number <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
14525: THEN
14526: l_cur_pa_tasks_rec.task_number := '-'||l_task_in_rec.pa_task_number;
14527: END IF; */
14528: /** Code end for bug 4120380 by aditi **/
14529: END IF;
14530:
14527: END IF; */
14528: /** Code end for bug 4120380 by aditi **/
14529: END IF;
14530:
14531: l_parent_task_id := l_cur_pa_tasks_rec.parent_task_id;
14532:
14533: IF l_debug_mode = 'Y' THEN
14534: pa_debug.g_err_stage:= 'Parent Task Id : '||l_parent_task_id;
14535: pa_debug.write(l_module_name,pa_debug.g_err_stage,
14533: IF l_debug_mode = 'Y' THEN
14534: pa_debug.g_err_stage:= 'Parent Task Id : '||l_parent_task_id;
14535: pa_debug.write(l_module_name,pa_debug.g_err_stage,
14536: l_debug_level3);
14537: pa_debug.g_err_stage:= 'Task Number : '||l_cur_pa_tasks_rec.task_number;
14538: pa_debug.write(l_module_name,pa_debug.g_err_stage,
14539: l_debug_level3);
14540: END IF;
14541:
14550: -- Derive the parent task id
14551: IF (l_task_in_rec.pa_parent_task_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM
14552: AND l_task_in_rec.pa_parent_task_id IS NOT NULL)
14553: THEN
14554: l_cur_pa_tasks_rec.parent_task_id := l_task_in_rec.pa_parent_task_id;
14555:
14556: ELSIF (l_task_in_rec.pm_parent_task_reference <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
14557: AND l_task_in_rec.pm_parent_task_reference IS NOT NULL)
14558: THEN
14567:
14568: --PA_PROJ_TASK_STRUC_PUB.convert_pm_parent_task_ref(
14569: -- p_pm_parent_task_reference => l_task_in_rec.pm_parent_task_reference
14570: -- ,p_project_id => p_project_id
14571: -- ,x_parent_task_id => l_cur_pa_tasks_rec.parent_task_id
14572: -- ,x_return_status => x_return_status
14573: -- );
14574:
14575:
14576: PA_PROJECT_PVT.Convert_pm_taskref_to_id_all (
14577: p_pa_project_id => p_project_id
14578: , p_structure_type => 'WORKPLAN'
14579: -- 3721124 p_pa_task_id will be null in this context
14580: -- , p_pa_task_id => l_cur_pa_tasks_rec.parent_task_id
14581: , p_pm_task_reference => l_task_in_rec.pm_parent_task_reference
14582: , p_out_task_id => l_cur_pa_tasks_rec.parent_task_id
14583: , p_return_status => x_return_status );
14584:
14578: , p_structure_type => 'WORKPLAN'
14579: -- 3721124 p_pa_task_id will be null in this context
14580: -- , p_pa_task_id => l_cur_pa_tasks_rec.parent_task_id
14581: , p_pm_task_reference => l_task_in_rec.pm_parent_task_reference
14582: , p_out_task_id => l_cur_pa_tasks_rec.parent_task_id
14583: , p_return_status => x_return_status );
14584:
14585: px_tasks_out_tbl(i).return_status := x_return_status;
14586: IF x_return_status <> FND_API.G_RET_STS_SUCCESS
14592: ,p_msg_attribute => 'CHANGE'
14593: ,p_resize_flag => 'N'
14594: ,p_msg_context => 'TASK'
14595: ,p_attribute1 => l_amg_segment1
14596: ,p_attribute2 => l_cur_pa_tasks_rec.task_number
14597: ,p_attribute3 => ''
14598: ,p_attribute4 => ''
14599: ,p_attribute5 => '');
14600: END IF;
14601: Raise Invalid_Arg_Exc_WP;
14602: END IF;
14603: ELSIF (l_task_in_rec.pm_parent_task_reference IS NULL)
14604: THEN --Also the values in l_task_in_rec.parent_task_id is null or miss_num so move it as top task
14605: l_cur_pa_tasks_rec.parent_task_id := null;
14606:
14607: ELSE -- when l_task_in_rec.pm_parent_task_reference is miss_char
14608: -- no parent task information provided for the current task.
14609: -- get parent information from database
14627: CLOSE cur_parent_object_rel;
14628:
14629: --Get the parent task id from parent task version id
14630: OPEN cur_elem_ver_to_task_id(l_db_parent_task_ver_id_tmp ) ;
14631: FETCH cur_elem_ver_to_task_id INTO l_cur_pa_tasks_rec.parent_task_id;
14632: CLOSE cur_elem_ver_to_task_id;
14633: END;
14634:
14635: END IF;
14697: END IF;
14698: END;
14699:
14700: ELSE--financial
14701: open cur_pa_tasks(p_project_id,l_task_in_rec.pa_task_id);
14702: fetch cur_pa_tasks into l_cur_pa_tasks_rec;
14703: close cur_pa_tasks;
14704:
14705: -- Bug 2982072. If task number and task name are passed as null then
14698: END;
14699:
14700: ELSE--financial
14701: open cur_pa_tasks(p_project_id,l_task_in_rec.pa_task_id);
14702: fetch cur_pa_tasks into l_cur_pa_tasks_rec;
14703: close cur_pa_tasks;
14704:
14705: -- Bug 2982072. If task number and task name are passed as null then
14706: -- obtain the values from the database and use them in update_task api.
14699:
14700: ELSE--financial
14701: open cur_pa_tasks(p_project_id,l_task_in_rec.pa_task_id);
14702: fetch cur_pa_tasks into l_cur_pa_tasks_rec;
14703: close cur_pa_tasks;
14704:
14705: -- Bug 2982072. If task number and task name are passed as null then
14706: -- obtain the values from the database and use them in update_task api.
14707: IF l_task_in_rec.pa_task_number is null OR
14706: -- obtain the values from the database and use them in update_task api.
14707: IF l_task_in_rec.pa_task_number is null OR
14708: l_task_in_rec.pa_task_number = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
14709: THEN
14710: l_task_in_rec.pa_task_number := l_cur_pa_tasks_rec.task_number;
14711: /* Code added for bug 4120380 **/
14712: /* bug#5243018 : Reverting the fix in Bug 4120380
14713: ELSE
14714: OPEN cur_proj_elements_info(l_task_in_rec.pa_task_id);
14722:
14723: IF l_task_in_rec.long_task_name is null OR
14724: l_task_in_rec.long_task_name = PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
14725: THEN
14726: l_task_in_rec.long_task_name := l_cur_pa_tasks_rec.long_task_name;
14727: /* Redundant but added for clarity -Added the else condition for bug 2982057*/
14728: ELSE
14729: l_task_in_rec.long_task_name := l_task_in_rec.long_task_name;
14730: END IF;
14729: l_task_in_rec.long_task_name := l_task_in_rec.long_task_name;
14730: END IF;
14731:
14732: END IF;
14733: l_parent_task_id := l_cur_pa_tasks_rec.parent_task_id;
14734: l_task_id := l_task_in_rec.pa_task_id;
14735:
14736: END IF; -- before calling create or update TASK
14737:
14757: ,p_validate_only => FND_API.G_FALSE
14758: ,p_calling_module => p_calling_module
14759: ,p_debug_mode => l_debug_mode
14760: ,p_init_msg_list => FND_API.G_FALSE
14761: ,p_object_type => 'PA_TASKS'
14762: ,p_project_id => p_project_id
14763: ,p_structure_id => l_structure_id
14764: ,p_task_number => l_task_in_rec.pa_task_number -- l_cur_pa_tasks_rec.task_number bug 6193314
14765: ,p_task_name => l_cur_pa_tasks_rec.long_task_name
14760: ,p_init_msg_list => FND_API.G_FALSE
14761: ,p_object_type => 'PA_TASKS'
14762: ,p_project_id => p_project_id
14763: ,p_structure_id => l_structure_id
14764: ,p_task_number => l_task_in_rec.pa_task_number -- l_cur_pa_tasks_rec.task_number bug 6193314
14765: ,p_task_name => l_cur_pa_tasks_rec.long_task_name
14766: ,p_task_description => l_cur_pa_tasks_rec.description
14767: ,p_location_id => l_cur_pa_tasks_rec.address_id
14768: ,p_task_manager_id => l_cur_pa_tasks_rec.task_manager_person_id
14761: ,p_object_type => 'PA_TASKS'
14762: ,p_project_id => p_project_id
14763: ,p_structure_id => l_structure_id
14764: ,p_task_number => l_task_in_rec.pa_task_number -- l_cur_pa_tasks_rec.task_number bug 6193314
14765: ,p_task_name => l_cur_pa_tasks_rec.long_task_name
14766: ,p_task_description => l_cur_pa_tasks_rec.description
14767: ,p_location_id => l_cur_pa_tasks_rec.address_id
14768: ,p_task_manager_id => l_cur_pa_tasks_rec.task_manager_person_id
14769: ,p_carrying_out_org_id => l_cur_pa_tasks_rec.carrying_out_organization_id
14762: ,p_project_id => p_project_id
14763: ,p_structure_id => l_structure_id
14764: ,p_task_number => l_task_in_rec.pa_task_number -- l_cur_pa_tasks_rec.task_number bug 6193314
14765: ,p_task_name => l_cur_pa_tasks_rec.long_task_name
14766: ,p_task_description => l_cur_pa_tasks_rec.description
14767: ,p_location_id => l_cur_pa_tasks_rec.address_id
14768: ,p_task_manager_id => l_cur_pa_tasks_rec.task_manager_person_id
14769: ,p_carrying_out_org_id => l_cur_pa_tasks_rec.carrying_out_organization_id
14770: ,p_priority_code => l_task_in_rec.priority_code
14763: ,p_structure_id => l_structure_id
14764: ,p_task_number => l_task_in_rec.pa_task_number -- l_cur_pa_tasks_rec.task_number bug 6193314
14765: ,p_task_name => l_cur_pa_tasks_rec.long_task_name
14766: ,p_task_description => l_cur_pa_tasks_rec.description
14767: ,p_location_id => l_cur_pa_tasks_rec.address_id
14768: ,p_task_manager_id => l_cur_pa_tasks_rec.task_manager_person_id
14769: ,p_carrying_out_org_id => l_cur_pa_tasks_rec.carrying_out_organization_id
14770: ,p_priority_code => l_task_in_rec.priority_code
14771: ,p_TYPE_ID => l_task_in_rec.task_type
14764: ,p_task_number => l_task_in_rec.pa_task_number -- l_cur_pa_tasks_rec.task_number bug 6193314
14765: ,p_task_name => l_cur_pa_tasks_rec.long_task_name
14766: ,p_task_description => l_cur_pa_tasks_rec.description
14767: ,p_location_id => l_cur_pa_tasks_rec.address_id
14768: ,p_task_manager_id => l_cur_pa_tasks_rec.task_manager_person_id
14769: ,p_carrying_out_org_id => l_cur_pa_tasks_rec.carrying_out_organization_id
14770: ,p_priority_code => l_task_in_rec.priority_code
14771: ,p_TYPE_ID => l_task_in_rec.task_type
14772: ,p_status_code => l_task_in_rec.status_code
14765: ,p_task_name => l_cur_pa_tasks_rec.long_task_name
14766: ,p_task_description => l_cur_pa_tasks_rec.description
14767: ,p_location_id => l_cur_pa_tasks_rec.address_id
14768: ,p_task_manager_id => l_cur_pa_tasks_rec.task_manager_person_id
14769: ,p_carrying_out_org_id => l_cur_pa_tasks_rec.carrying_out_organization_id
14770: ,p_priority_code => l_task_in_rec.priority_code
14771: ,p_TYPE_ID => l_task_in_rec.task_type
14772: ,p_status_code => l_task_in_rec.status_code
14773: ,p_inc_proj_progress_flag=> l_task_in_rec.inc_proj_progress_flag
14771: ,p_TYPE_ID => l_task_in_rec.task_type
14772: ,p_status_code => l_task_in_rec.status_code
14773: ,p_inc_proj_progress_flag=> l_task_in_rec.inc_proj_progress_flag
14774: ,p_pm_product_code => p_pm_product_code
14775: ,p_pm_task_reference => l_cur_pa_tasks_rec.pm_task_reference
14776: ,p_closed_date => l_task_in_rec.closed_date
14777: ,p_link_task_flag => l_task_in_rec.link_task_flag
14778: ,P_UOM_CODE => l_task_in_rec.wq_uom_code
14779: ,p_work_item_code => l_task_in_rec.wq_item_code
14787: ,x_msg_data => x_msg_data
14788: );
14789:
14790: -- Added for bug 3057575 :TO show task number while throwing err
14791: l_err_task_number := l_cur_pa_tasks_rec.task_number;
14792:
14793: px_tasks_out_tbl(i).return_status := x_return_status;
14794:
14795: IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
15157: IS
15158: SELECT count(rel.object_id_to1)
15159: FROM PA_OBJECT_RELATIONSHIPS rel, pa_proj_element_versions pev, pa_proj_elements pe, pa_task_types tt
15160: WHERE rel.object_id_from1 = c_parent_element_version_id
15161: AND rel.object_type_to = 'PA_TASKS'
15162: AND rel.relationship_type = 'S'
15163: AND rel.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
15164: AND rel.object_id_to1 = pev.element_version_id
15165: AND pev.proj_element_id = pe.proj_element_id
15159: FROM PA_OBJECT_RELATIONSHIPS rel, pa_proj_element_versions pev, pa_proj_elements pe, pa_task_types tt
15160: WHERE rel.object_id_from1 = c_parent_element_version_id
15161: AND rel.object_type_to = 'PA_TASKS'
15162: AND rel.relationship_type = 'S'
15163: AND rel.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
15164: AND rel.object_id_to1 = pev.element_version_id
15165: AND pev.proj_element_id = pe.proj_element_id
15166: AND pe.type_id = tt.task_type_id
15167: AND tt.object_type = 'PA_TASKS' /* bug 3279978 FP M Enhancement */
15163: AND rel.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
15164: AND rel.object_id_to1 = pev.element_version_id
15165: AND pev.proj_element_id = pe.proj_element_id
15166: AND pe.type_id = tt.task_type_id
15167: AND tt.object_type = 'PA_TASKS' /* bug 3279978 FP M Enhancement */
15168: AND tt.prog_entry_enable_flag = 'Y';
15169:
15170: l_child_task_count NUMBER;
15171: l_parent_version_id pa_proj_element_versions.element_version_id%TYPE;
15383: --Bug 6153503
15384: ELSE -- update task case
15385: DECLARE
15386: --Cursor to get the versioned information of the task
15387: CURSOR cur_proj_element_version_info(c_structure_version_id pa_proj_element_versions.parent_structure_version_id%TYPE, c_task_id pa_tasks.task_id%TYPE )
15388: IS
15389: SELECT rowid,element_version_id,object_type, project_id, proj_element_id, record_version_number,
15390: display_sequence, wbs_number, wbs_level, parent_structure_version_id,
15391: attribute_category, attribute1, attribute2, attribute3, attribute4, attribute5,
15394: FROM pa_proj_element_versions
15395: WHERE project_id = p_project_id
15396: AND proj_element_id = c_task_id
15397: AND parent_structure_version_id = c_structure_version_id
15398: AND object_type = 'PA_TASKS';
15399:
15400: l_cur_proj_elem_ver_info_rec cur_proj_element_version_info%ROWTYPE;
15401:
15402: BEGIN
15748: (
15749: X_ROW_ID => l_proj_elem_ver_rowid
15750: ,X_ELEMENT_VERSION_ID => l_task_version_id
15751: ,X_PROJ_ELEMENT_ID => l_task_id
15752: ,X_OBJECT_TYPE => 'PA_TASKS'
15753: ,X_PROJECT_ID => p_project_id
15754: ,X_PARENT_STRUCTURE_VERSION_ID=> l_structure_version_id
15755: ,X_DISPLAY_SEQUENCE => l_display_sequence_tbl(l_task_id)
15756: ,X_WBS_LEVEL => l_wbs_level_tbl(l_task_id)
15846: PA_PROJ_ELEMENT_VERSIONS_PKG.Update_Row(
15847: X_ROW_ID => l_proj_elem_ver_rowid
15848: ,X_ELEMENT_VERSION_ID => l_task_version_id
15849: ,X_PROJ_ELEMENT_ID => l_task_id
15850: ,X_OBJECT_TYPE => 'PA_TASKS'
15851: ,X_PROJECT_ID => p_project_id
15852: ,X_PARENT_STRUCTURE_VERSION_ID=> l_structure_version_id
15853: ,X_DISPLAY_SEQUENCE => l_display_sequence_tbl(l_task_in_rec.pa_task_id)
15854: ,X_WBS_LEVEL => l_wbs_level_tbl(l_task_in_rec.pa_task_id)
16473: -------------------------------------------------
16474: /** Code change begin for Bug 4120380 **/
16475: /** The code below will update the task_numbers of the tasks passed via update_project script
16476: back to their original value viz. it'll crop the '-' added to aviod the constraint check in
16477: PA_TASKS and PA_PROJ_ELEMENTS. **/
16478: /* bug#5243018 : Reverting the fix in Bug 4120380
16479: IF nvl(p_tasks_in_tbl.last,0) > 0 THEN --IF Added by rtarway for BUG 4336701
16480: FOR i in p_tasks_in_tbl.first..p_tasks_in_tbl.last LOOP
16481: UPDATE pa_proj_elements
16481: UPDATE pa_proj_elements
16482: SET element_number = p_tasks_in_tbl(i).pa_task_number
16483: WHERE element_number = '-'||p_tasks_in_tbl(i).pa_task_number
16484: AND PROJECT_ID = p_project_id;
16485: UPDATE pa_tasks
16486: SET task_number = p_tasks_in_tbl(i).pa_task_number
16487: WHERE task_number = '-'||p_tasks_in_tbl(i).pa_task_number
16488: AND PROJECT_ID = p_project_id;
16489: --dbms_output.put_line(('value of recod updates in pa_proj_elements'||sql%rowcount);
16532: IS
16533: SELECT rel.OBJECT_RELATIONSHIP_ID,tt.prog_entry_enable_flag
16534: FROM PA_OBJECT_RELATIONSHIPS rel, pa_proj_element_versions pev, pa_proj_elements pe, pa_task_types tt
16535: WHERE rel.object_id_from1 = c_parent_element_version_id
16536: AND rel.object_type_to = 'PA_TASKS'
16537: AND rel.relationship_type = 'S'
16538: AND rel.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
16539: AND rel.object_id_to1 = pev.element_version_id
16540: AND pev.proj_element_id = pe.proj_element_id
16534: FROM PA_OBJECT_RELATIONSHIPS rel, pa_proj_element_versions pev, pa_proj_elements pe, pa_task_types tt
16535: WHERE rel.object_id_from1 = c_parent_element_version_id
16536: AND rel.object_type_to = 'PA_TASKS'
16537: AND rel.relationship_type = 'S'
16538: AND rel.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
16539: AND rel.object_id_to1 = pev.element_version_id
16540: AND pev.proj_element_id = pe.proj_element_id
16541: AND tt.object_type = 'PA_TASKS' /* bug 3279978 FP M Enhancement */
16542: AND pe.type_id = tt.task_type_id;
16537: AND rel.relationship_type = 'S'
16538: AND rel.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS')
16539: AND rel.object_id_to1 = pev.element_version_id
16540: AND pev.proj_element_id = pe.proj_element_id
16541: AND tt.object_type = 'PA_TASKS' /* bug 3279978 FP M Enhancement */
16542: AND pe.type_id = tt.task_type_id;
16543:
16544: TYPE obj_rel_id_tbl is table of pa_object_relationships.object_relationship_id%TYPE
16545: INDEX BY BINARY_INTEGER;
17245: IF l_amg_project_csr%ISOPEN THEN
17246: CLOSE l_amg_project_csr;
17247: END IF;
17248:
17249: IF cur_pa_tasks%ISOPEN THEN
17250: CLOSE cur_pa_tasks;
17251: END IF;
17252:
17253: IF cur_template_flag%ISOPEN THEN
17246: CLOSE l_amg_project_csr;
17247: END IF;
17248:
17249: IF cur_pa_tasks%ISOPEN THEN
17250: CLOSE cur_pa_tasks;
17251: END IF;
17252:
17253: IF cur_template_flag%ISOPEN THEN
17254: CLOSE cur_template_flag;
17324: IF l_amg_project_csr%ISOPEN THEN
17325: CLOSE l_amg_project_csr;
17326: END IF;
17327:
17328: IF cur_pa_tasks%ISOPEN THEN
17329: CLOSE cur_pa_tasks;
17330: END IF;
17331:
17332: IF cur_template_flag%ISOPEN THEN
17325: CLOSE l_amg_project_csr;
17326: END IF;
17327:
17328: IF cur_pa_tasks%ISOPEN THEN
17329: CLOSE cur_pa_tasks;
17330: END IF;
17331:
17332: IF cur_template_flag%ISOPEN THEN
17333: CLOSE cur_template_flag;
17405: API_ERROR EXCEPTION;
17406:
17407: CURSOR get_parent_task_id IS
17408: SELECT parent_task_id, top_task_id
17409: FROM pa_tasks
17410: WHERE project_id = p_project_id
17411: AND task_id = p_task_id;
17412:
17413: BEGIN
17500: IS
17501: Select count(element_version_id)
17502: From pa_proj_element_versions
17503: Where project_id = c_project_id
17504: And object_type = 'PA_TASKS'
17505: And parent_structure_version_id = c_structure_version_id;
17506:
17507: --Bug# 5973460
17508: CURSOR c_get_process_code(c_structure_version_id NUMBER) IS
20410: pji_xbs_plans_v pxpv
20411: -- pji_fm_xbs_accum_tmp1 pfxat
20412: where ver.project_id = c_project_id
20413: and ver.parent_structure_version_id = c_structure_version_id
20414: and ver.object_type = 'PA_TASKS'
20415: and ver.element_version_id = rel.object_id_to1
20416: and rel.relationship_type = 'S'
20417: and rel.relationship_subtype in ( 'STRUCTURE_TO_TASK','TASK_TO_TASK')
20418: and sch.element_version_id = ver.element_version_id
20417: and rel.relationship_subtype in ( 'STRUCTURE_TO_TASK','TASK_TO_TASK')
20418: and sch.element_version_id = ver.element_version_id
20419: and sch.project_id = c_project_id
20420: and ele.proj_element_id = ver.proj_element_id
20421: and ptt.object_type = 'PA_TASKS' /* bug 3279978 FP M Enhancement */
20422: and ele.type_id = ptt.task_type_id
20423: and pxpv.project_id (+)= ver.project_id
20424: and pxpv.structure_version_id (+)= ver.parent_structure_version_id
20425: and pxpv.proj_element_id (+)= ver.proj_element_id
20440: pa_proj_elements ele,
20441: pa_task_types ptt
20442: where ver.project_id = c_project_id
20443: and ver.parent_structure_version_id = c_structure_version_id
20444: and ver.object_type = 'PA_TASKS'
20445: and ele.proj_element_id = ver.proj_element_id
20446: and ele.type_id = ptt.task_type_id
20447: and ptt.object_type = 'PA_TASKS' /* bug 3279978 FP M Enhancement */
20448: and nvl(ptt.prog_entry_enable_flag,'N') = 'Y'
20443: and ver.parent_structure_version_id = c_structure_version_id
20444: and ver.object_type = 'PA_TASKS'
20445: and ele.proj_element_id = ver.proj_element_id
20446: and ele.type_id = ptt.task_type_id
20447: and ptt.object_type = 'PA_TASKS' /* bug 3279978 FP M Enhancement */
20448: and nvl(ptt.prog_entry_enable_flag,'N') = 'Y'
20449: for update of ver.record_version_number NOWAIT;
20450: -- End of Cursor Declarations.
20451:
21813: WHEN OTHERS THEN
21814: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
21815: raise;
21816: END;
21817: IF l_object_type='PA_TASKS' THEN
21818: BEGIN
21819: DELETE FROM pa_object_relationships por1
21820: WHERE por1.object_relationship_id IN
21821: (SELECT por.object_relationship_id
21822: FROM pa_object_relationships por
21823: WHERE por.relationship_type = 'D'
21824: AND por.object_id_from1 = p_element_version_id
21825: AND por.object_id_from2 = por.object_id_to2
21826: AND por.object_type_from = 'PA_TASKS'
21827: AND por.object_type_to = 'PA_TASKS');
21828: IF SQL%NOTFOUND THEN
21829: fnd_message.set_name('PA','PA_RECORD_NOT_FOUND');
21830: x_return_status := FND_API.G_RET_STS_ERROR;
21823: WHERE por.relationship_type = 'D'
21824: AND por.object_id_from1 = p_element_version_id
21825: AND por.object_id_from2 = por.object_id_to2
21826: AND por.object_type_from = 'PA_TASKS'
21827: AND por.object_type_to = 'PA_TASKS');
21828: IF SQL%NOTFOUND THEN
21829: fnd_message.set_name('PA','PA_RECORD_NOT_FOUND');
21830: x_return_status := FND_API.G_RET_STS_ERROR;
21831: END IF;
21842: FROM pa_proj_element_versions ppev,
21843: pa_object_relationships por
21844: WHERE relationship_type ='D'
21845: AND ppev.parent_structure_version_id = p_element_version_id
21846: AND ppev.object_type = 'PA_TASKS'
21847: AND por.object_id_from2 = por.object_id_to2
21848: AND por.object_type_from = 'PA_TASKS'
21849: AND por.object_type_to = 'PA_TASKS'
21850: AND por.object_id_from1 = ppev.ELEMENT_VERSION_ID);
21844: WHERE relationship_type ='D'
21845: AND ppev.parent_structure_version_id = p_element_version_id
21846: AND ppev.object_type = 'PA_TASKS'
21847: AND por.object_id_from2 = por.object_id_to2
21848: AND por.object_type_from = 'PA_TASKS'
21849: AND por.object_type_to = 'PA_TASKS'
21850: AND por.object_id_from1 = ppev.ELEMENT_VERSION_ID);
21851: IF SQL%NOTFOUND THEN
21852: fnd_message.set_name('PA','PA_RECORD_NOT_FOUND');
21845: AND ppev.parent_structure_version_id = p_element_version_id
21846: AND ppev.object_type = 'PA_TASKS'
21847: AND por.object_id_from2 = por.object_id_to2
21848: AND por.object_type_from = 'PA_TASKS'
21849: AND por.object_type_to = 'PA_TASKS'
21850: AND por.object_id_from1 = ppev.ELEMENT_VERSION_ID);
21851: IF SQL%NOTFOUND THEN
21852: fnd_message.set_name('PA','PA_RECORD_NOT_FOUND');
21853: x_return_status := FND_API.G_RET_STS_ERROR;