[Home] [Help]
33:
34: --needed to get the field values associated to a AMG message
35:
36: CURSOR l_amg_project_csr
37: (p_pa_project_id pa_projects.project_id%type)
38: IS
39: SELECT segment1
40: FROM pa_projects p
41: WHERE p.project_id = p_pa_project_id;
36: CURSOR l_amg_project_csr
37: (p_pa_project_id pa_projects.project_id%type)
38: IS
39: SELECT segment1
40: FROM pa_projects p
41: WHERE p.project_id = p_pa_project_id;
42:
43: l_amg_segment1 VARCHAR2(25);
44: l_amg_task_number VARCHAR2(50);
375: ,X_ATTRIBUTE14 => null
376: ,X_ATTRIBUTE15 => null
377: ,X_TASK_UNPUB_VER_STATUS_CODE => null
378: ,X_SOURCE_OBJECT_ID => p_dest_project_id
379: ,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
380: );
381: -- added for Bug fix: 4537865
382: x_struc_version_id := l_new_struc_version_id;
383: -- added for Bug fix: 4537865
423: , X_CHANGE_REASON_CODE => NULL
424: , X_RECORD_VERSION_NUMBER => 1
425: , X_CURRENT_WORKING_FLAG => 'N' /* Not applicable to default financial str. bug 3301192 */
426: , X_SOURCE_OBJECT_ID => p_dest_project_id
427: , X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
428: );
429:
430:
431: IF NVL( p_struc_type, 'WORKPLAN' ) = 'WORKPLAN'
514:
515: CURSOR cur_pa_projs
516: IS
517: SELECT 'x'
518: FROM pa_projects_all
519: WHERE split_cost_from_workplan_flag = 'Y'
520: AND project_id = p_project_id;
521:
522: CURSOR cur_pa_proj_elem_vers2( c_strcu_ver_id NUMBER )
685: ,X_PARENT_STRUCTURE_ID => l_structure_id
686: ,X_PHASE_CODE => NULL
687: ,X_PHASE_VERSION_ID => NULL
688: ,X_SOURCE_OBJECT_ID => p_project_id
689: ,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
690: );
691:
692:
693: IF cur_pa_tasks%ROWCOUNT = 1
955:
956: --hsiu added for task version status
957: CURSOR cur_proj_is_template
958: IS select 'Y'
959: from pa_projects_all
960: where project_id = p_project_id
961: and template_flag = 'Y';
962: l_template_flag VARCHAR2(1);
963: l_task_unpub_ver_status_code PA_PROJ_ELEMENT_VERSIONS.task_unpub_ver_status_code%TYPE;
2471:
2472:
2473: CURSOR cur_proj_is_template
2474: IS select 'Y'
2475: from pa_projects_all
2476: where project_id = p_project_id
2477: and template_flag = 'Y';
2478: l_template_flag VARCHAR2(1);
2479:
3748:
3749: -- bug 2765115
3750: CURSOR cur_pa_proj
3751: IS
3752: SELECT template_flag from pa_projects_all where project_id = p_project_id;
3753:
3754: l_template_flag VARCHAR2(1) := 'N';
3755:
3756: CURSOR cur_task_ver_id3 IS
4081: ) RETURN VARCHAR2 IS
4082: CURSOR cur_proj_strs
4083: IS
4084: SELECT 'X'
4085: FROM pa_projects_all
4086: WHERE project_id = p_project_id
4087: AND split_cost_from_workplan_flag = 'N';
4088:
4089: CURSOR cur_pub_versions is
4191:
4192: CURSOR cur_projects
4193: IS
4194: SELECT split_cost_from_workplan_flag
4195: FROM pa_projects_all
4196: WHERE project_id = p_project_id;
4197:
4198: CURSOR cur_pa_proj_elems
4199: IS
4258: OPEN cur_projects;
4259: FETCH cur_projects INTO l_split_flag;
4260: CLOSE cur_projects;
4261:
4262: UPDATE pa_projects_all
4263: SET split_cost_from_workplan_flag = p_split_workplan
4264: WHERE project_id = p_project_id;
4265:
4266: IF p_split_workplan = l_split_flag
4932: l_msg_count NUMBER;
4933: l_msg_data VARCHAR2(2000);
4934: l_error_message_code VARCHAR2(30);
4935:
4936: l_project_name PA_PROJECTS_ALL.name%TYPE;
4937: l_project_number PA_PROJECTS_ALL.segment1%TYPE;
4938:
4939: l_suffix VARCHAR2(250);
4940: l_name VARCHAR2(240);
4933: l_msg_data VARCHAR2(2000);
4934: l_error_message_code VARCHAR2(30);
4935:
4936: l_project_name PA_PROJECTS_ALL.name%TYPE;
4937: l_project_number PA_PROJECTS_ALL.segment1%TYPE;
4938:
4939: l_suffix VARCHAR2(250);
4940: l_name VARCHAR2(240);
4941: l_number VARCHAR2(25);
4949: l_new_structure_version_id NUMBER;
4950: -- added for Bug Fix: 4537865
4951:
4952: CURSOR c1 IS
4953: select segment1, name from pa_projects_all
4954: where project_id = p_project_id;
4955:
4956: CURSOR cur_elem_ver_seq IS
4957: SELECT pa_proj_element_versions_s.nextval
5067: ,X_ATTRIBUTE14 => null
5068: ,X_ATTRIBUTE15 => null
5069: ,X_TASK_UNPUB_VER_STATUS_CODE => null
5070: ,X_SOURCE_OBJECT_ID => p_project_id
5071: ,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
5072: );
5073: -- added for Bug Fix: 4537865
5074: x_structure_version_id := l_new_structure_version_id;
5075: -- added for Bug Fix: 4537865
5125: , X_RECORD_VERSION_NUMBER => 1
5126: , X_CHANGE_REASON_CODE => NULL
5127: , X_CURRENT_WORKING_FLAG => l_current_working_ver_flag /* bug 3301192 */
5128: , X_SOURCE_OBJECT_ID => p_project_id
5129: , X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
5130: );
5131:
5132: PA_TASK_PUB1.Create_Schedule_Version(
5133: p_element_version_id => x_structure_version_id
5339: ,X_ATTRIBUTE14 => null
5340: ,X_ATTRIBUTE15 => null
5341: ,X_TASK_UNPUB_VER_STATUS_CODE => null
5342: ,X_SOURCE_OBJECT_ID => p_project_id
5343: ,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
5344: );
5345: -- added for Bug fix: 4537865
5346: x_structure_version_id := l_new_structure_version_id;
5347: -- added for Bug fix: 4537865
5385: , X_RECORD_VERSION_NUMBER => 1
5386: , X_CHANGE_REASON_CODE => NULL
5387: , X_CURRENT_WORKING_FLAG => 'Y' /* Since this is a shared + versioned senario and only one struc version then it should be CWV. bug 3301192 */
5388: , X_SOURCE_OBJECT_ID => p_project_id
5389: , X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
5390: );
5391:
5392: PA_TASK_PUB1.Create_Schedule_Version(
5393: p_element_version_id => x_structure_version_id
5474: ,X_ATTRIBUTE14 => null
5475: ,X_ATTRIBUTE15 => null
5476: ,X_TASK_UNPUB_VER_STATUS_CODE => null
5477: ,X_SOURCE_OBJECT_ID => p_project_id
5478: ,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
5479: );
5480: -- added for Bug fix: 4537865
5481: x_structure_version_id := l_new_structure_version_id;
5482: -- added for Bug fix: 4537865
5529: , X_RECORD_VERSION_NUMBER => 1
5530: , X_CHANGE_REASON_CODE => NULL
5531: , X_CURRENT_WORKING_FLAG => l_current_working_ver_flag /* bug 3301192 */
5532: , X_SOURCE_OBJECT_ID => p_project_id
5533: , X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
5534: );
5535:
5536: PA_TASK_PUB1.Create_Schedule_Version(
5537: p_element_version_id => x_structure_version_id
5697: RETURN NUMBER IS
5698:
5699: cursor c1 is
5700: select start_date
5701: from pa_projects
5702: where project_id = x_orig_project_id;
5703:
5704: -- use min(start_date) as pseudo original project start
5705: cursor c2 is
6082: --Added by rbruno bug 9544348
6083: l_actual_start_date DATE;
6084: l_actual_finish_date DATE;
6085:
6086: CURSOR cur_get_existing_task_id (c_project_id pa_projects_all.project_id%TYPE,
6087: c_pm_source_reference pa_proj_elements.pm_source_reference%TYPE) IS
6088: SELECT proj_element_id
6089: FROM pa_proj_elements pelem
6090: WHERE project_id = c_project_id
6802: ,ppev.ATTRIBUTE14
6803: ,ppev.TASK_UNPUB_VER_STATUS_CODE
6804: ,ppev.FINANCIAL_TASK_FLAG
6805: ,p_dest_project_id
6806: ,'PA_PROJECTS'
6807: FROM pa_proj_element_versions ppev
6808: WHERE ppev.project_id = p_src_project_id
6809: AND ppev.element_version_id = l_src_fin_version_id ;
6810:
6912: ,ppevs.process_update_wbs_flag
6913: ,decode(p_shared,'N',ppevs.current_working_flag,'Y') --CURRENT_WROKING_FLAG
6914: --If SPLIT and fin enabled case, copy cw from source, else populate it as Y
6915: ,p_dest_project_id
6916: ,'PA_PROJECTS'
6917: FROM pa_proj_elem_ver_structure ppevs,
6918: pa_proj_element_versions ppev
6919: WHERE ppevs.project_id = p_src_project_id
6920: AND ppevs.element_version_id = l_src_fin_version_id
7057: --Bug 3387963 - Cursor to get destination and source project's organization id
7058: CURSOR cur_get_carrying_out_org IS
7059: SELECT old.carrying_out_organization_id,
7060: new.carrying_out_organization_id
7061: FROM pa_projects_all old,
7062: pa_projects_all new
7063: WHERE old.project_id = p_src_project_id
7064: AND new.project_id = p_dest_project_id;
7065:
7058: CURSOR cur_get_carrying_out_org IS
7059: SELECT old.carrying_out_organization_id,
7060: new.carrying_out_organization_id
7061: FROM pa_projects_all old,
7062: pa_projects_all new
7063: WHERE old.project_id = p_src_project_id
7064: AND new.project_id = p_dest_project_id;
7065:
7066: l_old_proj_org_id NUMBER;
7371: ,ppe.BASELINE_DURATION
7372: ,ppe.PHASE_CODE
7373: ,ppe.PHASE_VERSION_ID
7374: ,p_dest_project_id
7375: ,'PA_PROJECTS'
7376: FROM pa_task_types ptt,
7377: pa_proj_elements ppe,
7378: pa_proj_element_versions ppev,
7379: pa_tasks pt
7458: ,ppev.ATTRIBUTE14
7459: ,decode( ppevs.status_code, 'STRUCTURE_PUBLISHED', 'PUBLISHED', 'WORKING' )
7460: ,ppev.FINANCIAL_TASK_FLAG
7461: ,p_dest_project_id
7462: ,'PA_PROJECTS'
7463: FROM pa_proj_element_versions ppev,
7464: pa_proj_elements ppe,
7465: pa_proj_element_versions ppev2,
7466: pa_proj_elem_ver_structure ppevs
7738: ,ppevs.ATTRIBUTE13
7739: ,ppevs.ATTRIBUTE14
7740: ,ppevs.ATTRIBUTE15
7741: ,p_dest_project_id
7742: ,'PA_PROJECTS'
7743: FROM pa_proj_elem_ver_schedule ppevs,
7744: pa_proj_element_versions ppev1,
7745: pa_proj_element_versions ppev2
7746: WHERE ppev1.project_id = p_dest_project_id
7952: --Bug 3387963 - Cursor to get destination and source project's organization id
7953: CURSOR cur_get_carrying_out_org IS
7954: SELECT old.carrying_out_organization_id,
7955: new.carrying_out_organization_id
7956: FROM pa_projects_all old,
7957: pa_projects_all new
7958: WHERE old.project_id = p_src_project_id
7959: AND new.project_id = p_dest_project_id;
7960:
7953: CURSOR cur_get_carrying_out_org IS
7954: SELECT old.carrying_out_organization_id,
7955: new.carrying_out_organization_id
7956: FROM pa_projects_all old,
7957: pa_projects_all new
7958: WHERE old.project_id = p_src_project_id
7959: AND new.project_id = p_dest_project_id;
7960:
7961: /*Included by avaithia for Bug 3922019*/
7960:
7961: /*Included by avaithia for Bug 3922019*/
7962: CURSOR c_template_flag(l_prj_id IN NUMBER) IS
7963: SELECT template_flag
7964: FROM pa_projects_all
7965: WHERE project_id = l_prj_id ;
7966:
7967: CURSOR c_only_wp_ver_id(l_proj_id IN NUMBER) IS
7968: SELECT ppv.element_version_id
7974: AND pps.structure_type_id = pst.structure_type_id
7975: AND pst.structure_type = 'WORKPLAN'
7976: AND ppv.project_id = l_proj_id ;
7977:
7978: l_template_flag PA_PROJECTS_ALL.template_flag%TYPE ;
7979: l_only_wp_ver_id PA_PROJ_ELEMENT_VERSIONS.element_version_id%TYPE ;
7980:
7981: /*End of declaration included by avaithia for Bug 3922019*/
7982:
8462: ,ppe.WF_ITEM_TYPE
8463: ,ppe.WF_PROCESS
8464: ,ppe.WF_START_LEAD_DAYS
8465: ,p_dest_project_id
8466: ,'PA_PROJECTS'
8467: FROM pa_proj_elements ppe
8468: WHERE ppe.project_id = p_src_project_id
8469: AND ppe.proj_element_id IN
8470: ( SELECT ppe2.proj_element_id
8590: -- ,ppwa.PROGRAM_FLAG
8591: ,ppwa.AUTO_ROLLUP_SUBPROJ_FLAG
8592: ,ppwa.THIRD_PARTY_SCHEDULE_CODE
8593: ,ppe.PROJECT_ID
8594: ,'PA_PROJECTS'
8595: -- gboomina added for bug 8586393 - start
8596: ,ppwa.use_task_schedule_flag
8597: -- gboomina added for bug 8586393 - end
8598: FROM pa_proj_elements ppe,
8780: ,NULL --bug 4261419
8781: ,NULL --bug 4261419
8782: ,NULL --bug 4261419
8783: ,p_dest_project_id
8784: ,'PA_PROJECTS'
8785: FROM pa_proj_element_versions ppev,
8786: pa_project_copy_options_tmp ppcot
8787: WHERE ppcot.context = 'WORKPLAN'
8788: AND ppev.element_version_id = ppcot.version_id
9089: ,ppevs.process_update_wbs_flag
9090: ,decode(l_versioning_enabled,'N','Y',l_dest_current_working_flag)
9091: --If versioning is disabled, both LATEST PUBLISHED AND CURRENT WORKING FLAGS are 'Y'
9092: ,p_dest_project_id
9093: ,'PA_PROJECTS'
9094: FROM pa_proj_elem_ver_structure ppevs,
9095: pa_proj_elements ppe
9096: WHERE ppevs.project_id = p_src_project_id
9097: AND ppevs.element_version_id = rec_dest_wp_struc_versions.attribute15
9210: ,ppev.ATTRIBUTE14
9211: ,ppev.TASK_UNPUB_VER_STATUS_CODE
9212: ,ppev.FINANCIAL_TASK_FLAG
9213: ,ppev.project_id
9214: ,'PA_PROJECTS'
9215: FROM pa_proj_element_versions ppev
9216: WHERE ppev.project_id = p_dest_project_id
9217: AND ppev.element_version_id = l_shared_make_working_copy ;
9218:
9285: ,ppevs.change_reason_code
9286: ,ppevs.process_update_wbs_flag
9287: ,'Y'
9288: ,ppevs.project_id
9289: ,'PA_PROJECTS'
9290: FROM pa_proj_elem_ver_structure ppevs,
9291: pa_proj_elements ppe
9292: WHERE ppevs.project_id = p_dest_project_id
9293: AND ppevs.element_version_id = l_shared_make_working_copy
9431: ,ppe.WF_ITEM_TYPE
9432: ,ppe.WF_PROCESS
9433: ,ppe.WF_START_LEAD_DAYS
9434: ,p_dest_project_id
9435: ,'PA_PROJECTS'
9436: FROM pa_task_types ptt,
9437: pa_tasks pt,
9438: pa_proj_elements ppe
9439: WHERE ptt.object_type = 'PA_TASKS'
9583: ,ppe.WF_ITEM_TYPE
9584: ,ppe.WF_PROCESS
9585: ,ppe.WF_START_LEAD_DAYS
9586: ,p_dest_project_id
9587: ,'PA_PROJECTS'
9588: FROM pa_task_types ptt,
9589: pa_proj_elements ppe
9590: WHERE ptt.object_type = 'PA_TASKS'
9591: AND ppe.type_id = ptt.task_type_id
9670: ,ppev.ATTRIBUTE14
9671: ,decode( ppevs.status_code, 'STRUCTURE_PUBLISHED', 'PUBLISHED', 'WORKING' )
9672: ,ppev.FINANCIAL_TASK_FLAG --Check if this is correct ?
9673: ,p_dest_project_id
9674: ,'PA_PROJECTS'
9675: FROM pa_proj_elements ppe,
9676: pa_proj_element_versions ppev,
9677: pa_proj_element_versions ppev2,
9678: pa_proj_elem_ver_structure ppevs
9963: ,ppevs.ATTRIBUTE13
9964: ,ppevs.ATTRIBUTE14
9965: ,ppevs.ATTRIBUTE15
9966: ,p_dest_PROJECT_ID
9967: ,'PA_PROJECTS'
9968: ,ppevs.DEF_SCH_TOOL_TSK_TYPE_CODE --Bug 9378153
9969: ,CONSTRAINT_TYPE_CODE --Bug 3762437
9970: ,CONSTRAINT_DATE
9971: ,FREE_SLACK
10111: ,'0'
10112: ,1
10113: ,'N'
10114: ,p_dest_project_id
10115: ,'PA_PROJECTS'
10116: );
10117:
10118: OPEN cur_get_working_ver_num(p_dest_project_id, l_dest_wp_structure_id);
10119: FETCH cur_get_working_ver_num INTO l_struc_ver_number;
10177: ,1
10178: ,1
10179: ,'Y'
10180: ,p_dest_project_id
10181: ,'PA_PROJECTS'
10182: );
10183:
10184: --bug 3912603, insert records into pa_proj_elem_ver_Schedule, Satish, start
10185: PA_TASK_PUB1.Create_Schedule_Version(
10323: )
10324: WHERE project_id = p_dest_project_id;
10325:
10326: --Updating Projects Dates
10327: UPDATE pa_projects_all
10328: SET ( BASELINE_START_DATE ,BASELINE_FINISH_DATE, baseline_duration, baseline_as_of_date ) =
10329: ( SELECT BASELINE_START_DATE ,BASELINE_FINISH_DATE, baseline_duration,
10330: decode( BASELINE_START_DATE, null, null, sysdate)
10331: FROM pa_proj_elements ppe, pa_proj_structure_types ppst
10339: --Get the latest published version id in the destination project
10340: l_dest_latest_pub_ver_id := PA_PROJ_ELEMENTS_UTILS.latest_published_ver_id( p_dest_project_id, 'WORKPLAN' ) ;
10341:
10342: IF l_dest_latest_pub_ver_id IS NOT NULL THEN
10343: UPDATE pa_projects_all
10344: SET ( scheduled_START_DATE ,scheduled_FINISH_DATE, scheduled_duration, scheduled_as_of_date ) =
10345: ( SELECT SCHEDULED_START_DATE ,SCHEDULED_FINISH_DATE, duration,
10346: decode( SCHEDULED_START_DATE, null, null, sysdate )
10347: FROM pa_proj_elem_ver_schedule
10363: OPEN c_only_wp_ver_id(p_dest_project_id) ;
10364: FETCH c_only_wp_ver_id INTO l_only_wp_ver_id ;
10365: CLOSE c_only_wp_ver_id ;
10366:
10367: UPDATE pa_projects_all
10368: SET ( scheduled_START_DATE ,scheduled_FINISH_DATE, scheduled_duration, scheduled_as_of_date ) =
10369: ( SELECT SCHEDULED_START_DATE ,SCHEDULED_FINISH_DATE, duration,
10370: decode( SCHEDULED_START_DATE, null, null, sysdate )
10371: FROM pa_proj_elem_ver_schedule
10514:
10515: DECLARE
10516: CURSOR cur_get_str_shr_code IS
10517: SELECT structure_sharing_code
10518: FROM pa_projects_all
10519: WHERE project_id = p_dest_project_id;
10520:
10521: src_versions_tab SYSTEM.pa_num_tbl_type;
10522: dest_versions_tab SYSTEM.pa_num_tbl_type;
11124: l_publish_person_id NUMBER;
11125: --bug 2838547
11126:
11127: --Bug 2955589.
11128: l_structure_ver_name pa_projects_all.name%TYPE;
11129: l_working_ver_count NUMBER;
11130: l_structure_version_id pa_proj_element_versions.element_version_id%TYPE;
11131: -- The following two flags will indicate if the respective version has to be
11132: -- created when versioning is enabled and no working version exists for the
11322: ,ppe.BASELINE_DURATION
11323: ,ppe.PHASE_CODE
11324: ,ppe.PHASE_VERSION_ID
11325: ,p_dest_project_id
11326: ,'PA_PROJECTS'
11327: ,ppe.PROGRESS_WEIGHT --Bug 3616964
11328: ,ppe.BASE_PERCENT_COMP_DERIV_CODE
11329: ,ppe.FUNCTION_CODE
11330: ,ppe.ENABLE_WF_FLAG
11537: ,ppe.BASELINE_DURATION
11538: ,ppe.PHASE_CODE
11539: ,ppe.PHASE_VERSION_ID
11540: ,p_dest_project_id
11541: ,'PA_PROJECTS'
11542: ,ppe.PROGRESS_WEIGHT --Bug 3616964
11543: ,ppe.BASE_PERCENT_COMP_DERIV_CODE
11544: ,ppe.FUNCTION_CODE
11545: ,'N'
11695: ,ppev.ATTRIBUTE13
11696: ,ppev.ATTRIBUTE14
11697: ,ppev.TASK_UNPUB_VER_STATUS_CODE
11698: ,p_dest_project_id
11699: ,'PA_PROJECTS'
11700: /*from pa_proj_element_versions ppev,
11701: pa_proj_elements ppe
11702: where ppev.project_id = p_src_project_id
11703: and ppe.project_id = p_dest_project_id
11816: ,ppevs.change_reason_code
11817: ,'N'
11818: ,ppevs.current_working_flag /* FPM bug 3301192 */
11819: ,p_dest_project_id
11820: ,'PA_PROJECTS'
11821: /*FROM PA_PROJ_ELEM_VER_STRUCTURE ppevs, pa_proj_elements ppe
11822: WHERE ppevs.project_id = p_src_project_id
11823: AND ppe.project_id = p_dest_project_id
11824: and ppevs.proj_element_id = ppe.attribute15
11894: ,ppwa.ALLOW_LOWEST_TSK_DEP_FLAG
11895: ,ppwa.AUTO_ROLLUP_SUBPROJ_FLAG
11896: ,ppwa.THIRD_PARTY_SCHEDULE_CODE
11897: ,ppe.PROJECT_ID
11898: ,'PA_PROJECTS'
11899: -- gboomina Bug 8586393 - start
11900: ,ppwa.use_task_schedule_flag
11901: -- gboomina Bug 8586393 - end
11902: FROM pa_proj_elements ppe,
12388: ,ppwa.ALLOW_LOWEST_TSK_DEP_FLAG
12389: ,ppwa.AUTO_ROLLUP_SUBPROJ_FLAG
12390: ,ppwa.THIRD_PARTY_SCHEDULE_CODE
12391: ,ppe.PROJECT_ID
12392: ,'PA_PROJECTS'
12393: -- gboomina bug 8586393 - start
12394: ,ppwa.use_task_schedule_flag
12395: -- gboomina bug 8586393 - end
12396: /*FROM pa_proj_elements ppe,
12590: ,ppev.ATTRIBUTE13
12591: ,ppev.ATTRIBUTE14
12592: ,ppev.TASK_UNPUB_VER_STATUS_CODE
12593: ,p_dest_project_id
12594: ,'PA_PROJECTS'
12595: FROM pa_proj_element_versions ppev,
12596: pa_proj_elements ppe
12597: WHERE ppev.project_id = p_src_project_id
12598: and ppe.project_id = p_dest_project_id
12678: ,ppev.ATTRIBUTE13
12679: ,ppev.ATTRIBUTE14
12680: ,decode( l_dest_str_versions_tbl(k).dest_str_status, 'STRUCTURE_PUBLISHED', 'PUBLISHED', 'WORKING' )
12681: ,p_dest_project_id
12682: ,'PA_PROJECTS'
12683: ,ppev.financial_task_flag --Bug no 3651255
12684: FROM pa_proj_element_versions ppev,
12685: pa_proj_elements ppe
12686: WHERE ppev.project_id = p_src_project_id
12930: ,ppevs.change_reason_code
12931: ,'N'
12932: ,ppevs.current_working_flag /* FPM bug 3301192 */
12933: ,p_dest_project_id
12934: ,'PA_PROJECTS'
12935: FROM PA_PROJ_ELEM_VER_STRUCTURE ppevs, pa_proj_elements ppe
12936: WHERE ppevs.project_id = p_src_project_id
12937: AND ppe.project_id = p_dest_project_id
12938: and ppevs.element_version_id = l_dest_str_versions_tbl(k).src_str_version_id
13068: ,ppevs.ATTRIBUTE13
13069: ,ppevs.ATTRIBUTE14
13070: ,ppevs.ATTRIBUTE15
13071: ,p_dest_PROJECT_ID
13072: ,'PA_PROJECTS'
13073: ,ppevs.DEF_SCH_TOOL_TSK_TYPE_CODE --Bug 9378153
13074: ,CONSTRAINT_TYPE_CODE --Bug 3762437
13075: ,CONSTRAINT_DATE
13076: ,FREE_SLACK
13133: -- WHERE proj_element_id = ppe.proj_element_id
13134: -- AND structure_type_id = 1 );
13135:
13136: --updating projects dates
13137: update pa_projects_all
13138: set ( BASELINE_START_DATE ,BASELINE_FINISH_DATE, baseline_duration, baseline_as_of_date) =
13139: ( SELECT BASELINE_START_DATE ,BASELINE_FINISH_DATE, baseline_duration,
13140: decode(BASELINE_START_DATE, null, null, sysdate)
13141: FROM pa_proj_elements ppe, pa_proj_structure_types ppst
13146: where project_id = p_dest_project_id;
13147:
13148: IF l_latest_pub_str_ver_id IS NOT NULL
13149: THEN
13150: update pa_projects_all
13151: set ( scheduled_START_DATE ,scheduled_FINISH_DATE, scheduled_duration, scheduled_as_of_date ) =
13152: ( SELECT SCHEDULED_START_DATE ,SCHEDULED_FINISH_DATE,duration,
13153: decode(SCHEDULED_START_DATE, null, null, sysdate)
13154: FROM pa_proj_elem_ver_schedule
13607: ( p_api_version_number IN NUMBER
13608: ,p_commit IN VARCHAR2
13609: ,p_init_msg_list IN VARCHAR2
13610: ,p_calling_module IN VARCHAR2
13611: ,p_project_id IN pa_projects_all.project_id%TYPE
13612: ,p_source_project_id IN pa_projects_all.project_id%TYPE
13613: ,p_pm_product_code IN pa_projects_all.pm_product_code%TYPE
13614: ,p_structure_type IN pa_structure_types.structure_type_class_code%TYPE
13615: ,p_tasks_in_tbl IN pa_project_pub.task_in_tbl_type
13608: ,p_commit IN VARCHAR2
13609: ,p_init_msg_list IN VARCHAR2
13610: ,p_calling_module IN VARCHAR2
13611: ,p_project_id IN pa_projects_all.project_id%TYPE
13612: ,p_source_project_id IN pa_projects_all.project_id%TYPE
13613: ,p_pm_product_code IN pa_projects_all.pm_product_code%TYPE
13614: ,p_structure_type IN pa_structure_types.structure_type_class_code%TYPE
13615: ,p_tasks_in_tbl IN pa_project_pub.task_in_tbl_type
13616: ,p_create_task_version_only IN VARCHAR2
13609: ,p_init_msg_list IN VARCHAR2
13610: ,p_calling_module IN VARCHAR2
13611: ,p_project_id IN pa_projects_all.project_id%TYPE
13612: ,p_source_project_id IN pa_projects_all.project_id%TYPE
13613: ,p_pm_product_code IN pa_projects_all.pm_product_code%TYPE
13614: ,p_structure_type IN pa_structure_types.structure_type_class_code%TYPE
13615: ,p_tasks_in_tbl IN pa_project_pub.task_in_tbl_type
13616: ,p_create_task_version_only IN VARCHAR2
13617: ,p_wp_str_exists IN VARCHAR2
13632: -- Cursors Definition
13633:
13634: -- This cursor is used to get the auto publish flag on creation for a given project_id
13635: /* Bug 2955589. This cursor is no longer used.
13636: CURSOR cur_auto_pub_flag(c_project_id pa_projects_all.project_id%TYPE)
13637: IS
13638: SELECT AUTO_PUB_UPON_CREATION_FLAG
13639: FROM pa_proj_workplan_attr
13640: WHERE project_id = c_project_id;
13639: FROM pa_proj_workplan_attr
13640: WHERE project_id = c_project_id;
13641: */
13642: -- This cursor is used to get the structure id given the structure type and project_id
13643: CURSOR get_structure(c_project_id pa_projects_all.project_id%TYPE, c_struc_type pa_structure_types.structure_type_class_code%TYPE) IS
13644: select pelem.proj_element_id
13645: from pa_proj_elements pelem,
13646: pa_proj_structure_types ppstr,
13647: pa_structure_types pstr
13650: and pstr.structure_type_id = ppstr.structure_type_id
13651: and ppstr.proj_element_id = pelem.proj_element_id;
13652:
13653: -- This cursor is used to get the project number given the project_id
13654: CURSOR l_amg_project_csr(c_project_id pa_projects.project_id%type)
13655: IS
13656: SELECT segment1
13657: FROM pa_projects p
13658: WHERE p.project_id = c_project_id;
13653: -- This cursor is used to get the project number given the project_id
13654: CURSOR l_amg_project_csr(c_project_id pa_projects.project_id%type)
13655: IS
13656: SELECT segment1
13657: FROM pa_projects p
13658: WHERE p.project_id = c_project_id;
13659:
13660: --The following cursor will be used when the structure type is
13661: --Financial as some of the attributes have been derived and updated
13659:
13660: --The following cursor will be used when the structure type is
13661: --Financial as some of the attributes have been derived and updated
13662: --in the pa_tasks table and we need to use them for structures processing.
13663: CURSOR cur_pa_tasks(c_project_id pa_projects_all.project_id%TYPE,c_task_id pa_tasks.task_id%TYPE)
13664: IS
13665: SELECT task_number, long_task_name, description,
13666: parent_task_id,carrying_out_organization_id,
13667: pm_task_reference, address_id, task_manager_person_id
13671:
13672: l_cur_pa_tasks_rec cur_pa_tasks%ROWTYPE; -- userd only for create task case
13673:
13674: --ADUT. This cursor is required to identify if the source is a project / template.
13675: Cursor cur_template_flag(c_proj_id pa_projects_all.project_id%TYPE)
13676: Is
13677: select NVL(template_flag,'N')
13678: from pa_projects
13679: where project_id = c_proj_id;
13674: --ADUT. This cursor is required to identify if the source is a project / template.
13675: Cursor cur_template_flag(c_proj_id pa_projects_all.project_id%TYPE)
13676: Is
13677: select NVL(template_flag,'N')
13678: from pa_projects
13679: where project_id = c_proj_id;
13680:
13681: -- IUP: Cursors added for changes done to this api so it can be called from update_project
13682: --Cursor to get the record version number of the task record which needs to be updated in pa_proj_elements table
13789: l_add_task_security VARCHAR2(1);
13790: l_default_task_type_id pa_proj_elements.type_id%TYPE;
13791: l_update_task_security VARCHAR2(1);
13792: l_str_status_code pa_proj_elem_ver_structure.status_code%TYPE;
13793: l_amg_segment1 pa_projects_all.segment1%TYPE;
13794:
13795: -- Bug 3075609
13796: l_maintain_weightages VARCHAR2(1); -- We need not maintain weightages during code flow always. This var will track it.
13797: l_existing_task_set_max_wt VARCHAR2(1); -- If a task is indented/ moved under a parent with no other progressible task, this
13913: l_add_end_date SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
13914: l_pm_product_code SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
13915: l_add_plan_task_id NUMBER;
13916:
13917: CURSOR cur_wp_task_csr_duplicate (c_project_id pa_projects_all.project_id%TYPE,
13918: c_pm_source_reference pa_proj_elements.pm_source_reference%TYPE) IS
13919: SELECT proj_element_id
13920: FROM pa_proj_elements pelem
13921: WHERE project_id = c_project_id
14014: ),
14015: Decode(ppwa.wp_enable_version_flag, 'Y', 'PUBLISH', 'WORKING')) est_remaining_effort,
14016: Nvl(ppr.completed_percentage, ppr.eff_rollup_percent_comp) completed_percentage
14017: FROM
14018: pa_projects_all ppa,
14019: pa_proj_elements ppe,
14020: pa_proj_element_versions ppv,
14021: pa_proj_elem_ver_structure ppvs,
14022: pa_proj_workplan_attr ppwa,
14085: ),
14086: 'WORKING') etc_effort,
14087: Nvl(ppr.completed_percentage, ppr.eff_rollup_percent_comp) physical_percent_complete
14088: FROM
14089: pa_projects_all ppa,
14090: pa_proj_elements ppe,
14091: pa_proj_element_versions ppv,
14092: pa_proj_elem_ver_structure ppvs,
14093: pa_progress_rollup ppr,
14120: l_prev_percent_comp Number;
14121: l_update_task_det_flag varchar2(1);
14122:
14123: -- Bug 6717386
14124: CURSOR cur_struc_ver_wp(c_project_id pa_projects_all.project_id%TYPE, c_structure_type pa_structure_types.structure_type%TYPE)
14125: IS
14126: SELECT c.element_version_id
14127: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
14128: ,pa_proj_elem_ver_structure d
14723: DECLARE
14724: /* Bug 3588013 - Added the exists clause for this bug to get proj_element_id for the correct structure
14725: Task Reference should be unique with in structure.
14726: */
14727: CURSOR cur_wp_task_csr (c_project_id pa_projects_all.project_id%TYPE,
14728: c_pm_source_reference pa_proj_elements.pm_source_reference%TYPE) IS
14729: SELECT proj_element_id
14730: FROM pa_proj_elements pelem
14731: WHERE project_id = c_project_id
16350: ,X_ATTRIBUTE14 => null
16351: ,X_ATTRIBUTE15 => null
16352: ,X_TASK_UNPUB_VER_STATUS_CODE => l_task_unpub_ver_status_code
16353: ,X_SOURCE_OBJECT_ID => p_project_id
16354: ,X_SOURCE_OBJECT_TYPE => 'PA_PROJECTS'
16355: --,P_Financial_Task_Flag => PA_PROJ_ELEMENTS_UTILS.CHECK_IS_FINANCIAL_TASK(l_task_id)
16356: --commented and added following for bug 4059962 reopen, rtarway
16357: ,P_Financial_Task_Flag => l_financial_task_flag
16358: );
18217: ( p_api_version_number IN NUMBER := 1.0
18218: ,p_commit IN VARCHAR2 := FND_API.G_FALSE
18219: ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
18220: ,p_calling_context IN VARCHAR2 := 'UPDATE'
18221: ,p_project_id IN pa_projects_all.project_id%TYPE
18222: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
18223: ,p_pub_struc_ver_id IN NUMBER := NULL
18224: ,p_pub_prog_flag IN VARCHAR2 := 'Y' --bug 4019845
18225: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
18229:
18230: -- Cursors used in this Procedure.
18231:
18232: -- This cursor obtains the count of the task versions in the structure version.
18233: CURSOR cur_task_count(c_project_id pa_projects_all.project_id%TYPE,
18234: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE)
18235: IS
18236: Select count(element_version_id)
18237: From pa_proj_element_versions
18478: ( p_api_version_number IN NUMBER := 1.0
18479: ,p_commit IN VARCHAR2 := FND_API.G_FALSE
18480: ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
18481: ,p_calling_context IN VARCHAR2 := 'UPDATE'
18482: ,p_project_id IN pa_projects_all.project_id%TYPE
18483: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
18484: ,p_pub_struc_ver_id IN NUMBER := NULL
18485: ,p_pub_prog_flag IN VARCHAR2 := 'Y' --bug 4019845
18486: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
18492:
18493: -- This cursor is used to decide if the structure version corresponds to a particular
18494: -- Structure type. Depending on this the plannable tasks api and the task weightage calculation
18495: -- api will be called.
18496: Cursor cur_structure_type(c_project_id pa_projects_all.project_id%TYPE,
18497: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE,
18498: c_structure_type pa_structure_types.structure_type%TYPE)
18499: IS
18500: select 'Y'
18548:
18549: Cursor cur_proj_ou
18550: IS
18551: select org_id
18552: from pa_projects_all
18553: where project_id = p_project_id;
18554:
18555: l_org_id NUMBER;
18556:
18848: PROCEDURE PROCESS_WBS_UPDATES_CONC
18849: ( errbuf OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
18850: ,retcode OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
18851: ,p_calling_context IN VARCHAR2 := 'UPDATE'
18852: ,p_project_id IN pa_projects_all.project_id%TYPE
18853: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
18854: ,p_pub_struc_ver_id IN NUMBER := NULL
18855: ,p_pub_prog_flag IN VARCHAR2 := 'Y' --bug 4019845
18856: ,p_rerun_flag IN VARCHAR2 := null --bug 4589289
19229: ( p_api_version_number IN NUMBER := 1.0
19230: ,p_commit IN VARCHAR2 := FND_API.G_FALSE
19231: ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
19232: ,p_calling_context IN VARCHAR2 := 'UPDATE'
19233: ,p_project_id IN pa_projects_all.project_id%TYPE
19234: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
19235: ,p_pub_struc_ver_id IN NUMBER := NULL
19236: ,p_pub_prog_flag IN VARCHAR2 := 'Y' --bug 4019845
19237: ,p_rerun_flag IN VARCHAR2 := null --bug 4589289
19244:
19245: -- This cursor is used to decide if the structure version corresponds to a particular
19246: -- Structure type. Depending on this the plannable tasks api and the task weightage calculation
19247: -- api will be called.
19248: CURSOR cur_structure_type(c_project_id pa_projects_all.project_id%TYPE,
19249: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE,
19250: c_structure_type pa_structure_types.structure_type%TYPE)
19251: IS
19252: SELECT 'Y'
19304:
19305: CURSOR get_proj_rec_ver_number(c_project_id NUMBER)
19306: IS
19307: SELECT record_version_number
19308: FROM pa_projects_all
19309: WHERE project_id = c_project_id;
19310:
19311: CURSOR get_struc_id(c_project_id NUMBER, c_struc_ver_id NUMBER) IS
19312: select pevs.proj_element_id
20035: IF (l_financial_type = 'Y') THEN
20036:
20037: SELECT start_date, completion_date
20038: INTO l_proj_start_date, l_proj_completion_date
20039: FROM pa_projects_all
20040: WHERE project_id = l_project_id;
20041:
20042: --Call sync-up API
20043:
21135: ( p_api_version_number IN NUMBER
21136: ,p_commit IN VARCHAR2
21137: ,p_init_msg_list IN VARCHAR2
21138: ,p_calling_context IN VARCHAR2
21139: ,p_project_id IN pa_projects_all.project_id%TYPE
21140: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
21141: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
21142: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
21143: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
21185: -- Cursor Declarations.
21186: -- This cursor fetches the duration, effort, relationship id between the task and its parent
21187: -- and progress entry enable flag for the task type associated with the task version. This
21188: -- info is necessary for calculating the task weightage of the tasks.
21189: CURSOR cur_task_info(c_project_id pa_projects_all.project_id%TYPE,
21190: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE
21191: ,c_plan_version_id NUMBER --bug 4726889
21192: )
21193: IS
21226: ;
21227:
21228: -- TWUT
21229: -- This cursor is when fetched will lock all the task versions of the structure version.
21230: CURSOR cur_task_ver_lock(c_project_id pa_projects_all.project_id%TYPE,
21231: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE)
21232: IS
21233: select 'X'
21234: from pa_proj_element_versions ver,
21680: ( p_api_version_number IN NUMBER
21681: ,p_commit IN VARCHAR2
21682: ,p_init_msg_list IN VARCHAR2
21683: ,p_calling_context IN VARCHAR2
21684: ,p_project_id IN pa_projects_all.project_id%TYPE
21685: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
21686: ,p_update_wbs_flag IN pa_proj_elem_ver_structure.process_update_wbs_flag%TYPE
21687: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
21688: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
21824: -- package level constants FND_API.G_TRUE , FND_API.G_FLASE
21825: -- can not be reffered .
21826:
21827: PROCEDURE PROCESS_WBS_UPDATES_WRP_FORM
21828: ( p_project_id IN pa_projects_all.project_id%TYPE
21829: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
21830: ,p_pub_struc_ver_id IN NUMBER := NULL
21831: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
21832: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
22246: --check security if user id is not -1
22247: IF (p_user_id <> -1) THEN
22248: IF 'F' = PA_SECURITY_PVT.check_user_privilege(
22249: 'PA_PAXPREPR_OPT_WORKPLAN_STR',
22250: 'PA_PROJECTS',
22251: l_project_id
22252: ) THEN
22253: PA_UTILS.ADD_MESSAGE
22254: (p_app_short_name => 'PA',
22583: ) RETURN VARCHAR2
22584: IS
22585: Cursor get_proj_share_type
22586: IS
22587: SELECT proj.structure_sharing_code FROM pa_projects_all proj where proj.project_id = P_Project_ID;
22588:
22589: l_share_code VARCHAR2(250);
22590:
22591: Begin