[Home] [Help]
298:
299: CURSOR cur_pa_projs
300: IS
301: SELECT 'x'
302: FROM pa_projects_all
303: WHERE split_cost_from_workplan_flag = 'Y'
304: AND project_id = p_project_id;
305:
306: CURSOR cur_pa_proj_elem_vers2( c_strcu_ver_id NUMBER )
739:
740: --hsiu added for task version status
741: CURSOR cur_proj_is_template
742: IS select 'Y'
743: from pa_projects_all
744: where project_id = p_project_id
745: and template_flag = 'Y';
746: l_template_flag VARCHAR2(1);
747: l_task_unpub_ver_status_code PA_PROJ_ELEMENT_VERSIONS.task_unpub_ver_status_code%TYPE;
2254:
2255:
2256: CURSOR cur_proj_is_template
2257: IS select 'Y'
2258: from pa_projects_all
2259: where project_id = p_project_id
2260: and template_flag = 'Y';
2261: l_template_flag VARCHAR2(1);
2262:
3527:
3528: -- bug 2765115
3529: CURSOR cur_pa_proj
3530: IS
3531: SELECT template_flag from pa_projects_all where project_id = p_project_id;
3532:
3533: l_template_flag VARCHAR2(1) := 'N';
3534:
3535: CURSOR cur_task_ver_id3 IS
3860: ) RETURN VARCHAR2 IS
3861: CURSOR cur_proj_strs
3862: IS
3863: SELECT 'X'
3864: FROM pa_projects_all
3865: WHERE project_id = p_project_id
3866: AND split_cost_from_workplan_flag = 'N';
3867:
3868: CURSOR cur_pub_versions is
3970:
3971: CURSOR cur_projects
3972: IS
3973: SELECT split_cost_from_workplan_flag
3974: FROM pa_projects_all
3975: WHERE project_id = p_project_id;
3976:
3977: CURSOR cur_pa_proj_elems
3978: IS
4037: OPEN cur_projects;
4038: FETCH cur_projects INTO l_split_flag;
4039: CLOSE cur_projects;
4040:
4041: UPDATE pa_projects_all
4042: SET split_cost_from_workplan_flag = p_split_workplan
4043: WHERE project_id = p_project_id;
4044:
4045: IF p_split_workplan = l_split_flag
4711: l_msg_count NUMBER;
4712: l_msg_data VARCHAR2(2000);
4713: l_error_message_code VARCHAR2(30);
4714:
4715: l_project_name PA_PROJECTS_ALL.name%TYPE;
4716: l_project_number PA_PROJECTS_ALL.segment1%TYPE;
4717:
4718: l_suffix VARCHAR2(250);
4719: l_name VARCHAR2(240);
4712: l_msg_data VARCHAR2(2000);
4713: l_error_message_code VARCHAR2(30);
4714:
4715: l_project_name PA_PROJECTS_ALL.name%TYPE;
4716: l_project_number PA_PROJECTS_ALL.segment1%TYPE;
4717:
4718: l_suffix VARCHAR2(250);
4719: l_name VARCHAR2(240);
4720: l_number VARCHAR2(25);
4728: l_new_structure_version_id NUMBER;
4729: -- added for Bug Fix: 4537865
4730:
4731: CURSOR c1 IS
4732: select segment1, name from pa_projects_all
4733: where project_id = p_project_id;
4734:
4735: CURSOR cur_elem_ver_seq IS
4736: SELECT pa_proj_element_versions_s.nextval
5858: l_task_id NUMBER;
5859: --Added by rtarway for BUG 3990873
5860: l_pa_task_id NUMBER;
5861:
5862: CURSOR cur_get_existing_task_id (c_project_id pa_projects_all.project_id%TYPE,
5863: c_pm_source_reference pa_proj_elements.pm_source_reference%TYPE) IS
5864: SELECT proj_element_id
5865: FROM pa_proj_elements pelem
5866: WHERE project_id = c_project_id
6808: --Bug 3387963 - Cursor to get destination and source project's organization id
6809: CURSOR cur_get_carrying_out_org IS
6810: SELECT old.carrying_out_organization_id,
6811: new.carrying_out_organization_id
6812: FROM pa_projects_all old,
6813: pa_projects_all new
6814: WHERE old.project_id = p_src_project_id
6815: AND new.project_id = p_dest_project_id;
6816:
6809: CURSOR cur_get_carrying_out_org IS
6810: SELECT old.carrying_out_organization_id,
6811: new.carrying_out_organization_id
6812: FROM pa_projects_all old,
6813: pa_projects_all new
6814: WHERE old.project_id = p_src_project_id
6815: AND new.project_id = p_dest_project_id;
6816:
6817: l_old_proj_org_id NUMBER;
7703: --Bug 3387963 - Cursor to get destination and source project's organization id
7704: CURSOR cur_get_carrying_out_org IS
7705: SELECT old.carrying_out_organization_id,
7706: new.carrying_out_organization_id
7707: FROM pa_projects_all old,
7708: pa_projects_all new
7709: WHERE old.project_id = p_src_project_id
7710: AND new.project_id = p_dest_project_id;
7711:
7704: CURSOR cur_get_carrying_out_org IS
7705: SELECT old.carrying_out_organization_id,
7706: new.carrying_out_organization_id
7707: FROM pa_projects_all old,
7708: pa_projects_all new
7709: WHERE old.project_id = p_src_project_id
7710: AND new.project_id = p_dest_project_id;
7711:
7712: /*Included by avaithia for Bug 3922019*/
7711:
7712: /*Included by avaithia for Bug 3922019*/
7713: CURSOR c_template_flag(l_prj_id IN NUMBER) IS
7714: SELECT template_flag
7715: FROM pa_projects_all
7716: WHERE project_id = l_prj_id ;
7717:
7718: CURSOR c_only_wp_ver_id(l_proj_id IN NUMBER) IS
7719: SELECT ppv.element_version_id
7725: AND pps.structure_type_id = pst.structure_type_id
7726: AND pst.structure_type = 'WORKPLAN'
7727: AND ppv.project_id = l_proj_id ;
7728:
7729: l_template_flag PA_PROJECTS_ALL.template_flag%TYPE ;
7730: l_only_wp_ver_id PA_PROJ_ELEMENT_VERSIONS.element_version_id%TYPE ;
7731:
7732: /*End of declaration included by avaithia for Bug 3922019*/
7733:
10029: )
10030: WHERE project_id = p_dest_project_id;
10031:
10032: --Updating Projects Dates
10033: UPDATE pa_projects_all
10034: SET ( BASELINE_START_DATE ,BASELINE_FINISH_DATE, baseline_duration, baseline_as_of_date ) =
10035: ( SELECT BASELINE_START_DATE ,BASELINE_FINISH_DATE, baseline_duration,
10036: decode( BASELINE_START_DATE, null, null, sysdate)
10037: FROM pa_proj_elements ppe, pa_proj_structure_types ppst
10045: --Get the latest published version id in the destination project
10046: l_dest_latest_pub_ver_id := PA_PROJ_ELEMENTS_UTILS.latest_published_ver_id( p_dest_project_id, 'WORKPLAN' ) ;
10047:
10048: IF l_dest_latest_pub_ver_id IS NOT NULL THEN
10049: UPDATE pa_projects_all
10050: SET ( scheduled_START_DATE ,scheduled_FINISH_DATE, scheduled_duration, scheduled_as_of_date ) =
10051: ( SELECT SCHEDULED_START_DATE ,SCHEDULED_FINISH_DATE, duration,
10052: decode( SCHEDULED_START_DATE, null, null, sysdate )
10053: FROM pa_proj_elem_ver_schedule
10069: OPEN c_only_wp_ver_id(p_dest_project_id) ;
10070: FETCH c_only_wp_ver_id INTO l_only_wp_ver_id ;
10071: CLOSE c_only_wp_ver_id ;
10072:
10073: UPDATE pa_projects_all
10074: SET ( scheduled_START_DATE ,scheduled_FINISH_DATE, scheduled_duration, scheduled_as_of_date ) =
10075: ( SELECT SCHEDULED_START_DATE ,SCHEDULED_FINISH_DATE, duration,
10076: decode( SCHEDULED_START_DATE, null, null, sysdate )
10077: FROM pa_proj_elem_ver_schedule
10220:
10221: DECLARE
10222: CURSOR cur_get_str_shr_code IS
10223: SELECT structure_sharing_code
10224: FROM pa_projects_all
10225: WHERE project_id = p_dest_project_id;
10226:
10227: src_versions_tab SYSTEM.pa_num_tbl_type;
10228: dest_versions_tab SYSTEM.pa_num_tbl_type;
10809: l_publish_person_id NUMBER;
10810: --bug 2838547
10811:
10812: --Bug 2955589.
10813: l_structure_ver_name pa_projects_all.name%TYPE;
10814: l_working_ver_count NUMBER;
10815: l_structure_version_id pa_proj_element_versions.element_version_id%TYPE;
10816: -- The following two flags will indicate if the respective version has to be
10817: -- created when versioning is enabled and no working version exists for the
12804: -- WHERE proj_element_id = ppe.proj_element_id
12805: -- AND structure_type_id = 1 );
12806:
12807: --updating projects dates
12808: update pa_projects_all
12809: set ( BASELINE_START_DATE ,BASELINE_FINISH_DATE, baseline_duration, baseline_as_of_date) =
12810: ( SELECT BASELINE_START_DATE ,BASELINE_FINISH_DATE, baseline_duration,
12811: decode(BASELINE_START_DATE, null, null, sysdate)
12812: FROM pa_proj_elements ppe, pa_proj_structure_types ppst
12817: where project_id = p_dest_project_id;
12818:
12819: IF l_latest_pub_str_ver_id IS NOT NULL
12820: THEN
12821: update pa_projects_all
12822: set ( scheduled_START_DATE ,scheduled_FINISH_DATE, scheduled_duration, scheduled_as_of_date ) =
12823: ( SELECT SCHEDULED_START_DATE ,SCHEDULED_FINISH_DATE,duration,
12824: decode(SCHEDULED_START_DATE, null, null, sysdate)
12825: FROM pa_proj_elem_ver_schedule
13264: ( p_api_version_number IN NUMBER
13265: ,p_commit IN VARCHAR2
13266: ,p_init_msg_list IN VARCHAR2
13267: ,p_calling_module IN VARCHAR2
13268: ,p_project_id IN pa_projects_all.project_id%TYPE
13269: ,p_source_project_id IN pa_projects_all.project_id%TYPE
13270: ,p_pm_product_code IN pa_projects_all.pm_product_code%TYPE
13271: ,p_structure_type IN pa_structure_types.structure_type_class_code%TYPE
13272: ,p_tasks_in_tbl IN pa_project_pub.task_in_tbl_type
13265: ,p_commit IN VARCHAR2
13266: ,p_init_msg_list IN VARCHAR2
13267: ,p_calling_module IN VARCHAR2
13268: ,p_project_id IN pa_projects_all.project_id%TYPE
13269: ,p_source_project_id IN pa_projects_all.project_id%TYPE
13270: ,p_pm_product_code IN pa_projects_all.pm_product_code%TYPE
13271: ,p_structure_type IN pa_structure_types.structure_type_class_code%TYPE
13272: ,p_tasks_in_tbl IN pa_project_pub.task_in_tbl_type
13273: ,p_create_task_version_only IN VARCHAR2
13266: ,p_init_msg_list IN VARCHAR2
13267: ,p_calling_module IN VARCHAR2
13268: ,p_project_id IN pa_projects_all.project_id%TYPE
13269: ,p_source_project_id IN pa_projects_all.project_id%TYPE
13270: ,p_pm_product_code IN pa_projects_all.pm_product_code%TYPE
13271: ,p_structure_type IN pa_structure_types.structure_type_class_code%TYPE
13272: ,p_tasks_in_tbl IN pa_project_pub.task_in_tbl_type
13273: ,p_create_task_version_only IN VARCHAR2
13274: ,p_wp_str_exists IN VARCHAR2
13289: -- Cursors Definition
13290:
13291: -- This cursor is used to get the auto publish flag on creation for a given project_id
13292: /* Bug 2955589. This cursor is no longer used.
13293: CURSOR cur_auto_pub_flag(c_project_id pa_projects_all.project_id%TYPE)
13294: IS
13295: SELECT AUTO_PUB_UPON_CREATION_FLAG
13296: FROM pa_proj_workplan_attr
13297: WHERE project_id = c_project_id;
13296: FROM pa_proj_workplan_attr
13297: WHERE project_id = c_project_id;
13298: */
13299: -- This cursor is used to get the structure id given the structure type and project_id
13300: CURSOR get_structure(c_project_id pa_projects_all.project_id%TYPE, c_struc_type pa_structure_types.structure_type_class_code%TYPE) IS
13301: select pelem.proj_element_id
13302: from pa_proj_elements pelem,
13303: pa_proj_structure_types ppstr,
13304: pa_structure_types pstr
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
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
13334: select NVL(template_flag,'N')
13335: from pa_projects
13336: where project_id = c_proj_id;
13442: l_add_task_security VARCHAR2(1);
13443: l_default_task_type_id pa_proj_elements.type_id%TYPE;
13444: l_update_task_security VARCHAR2(1);
13445: l_str_status_code pa_proj_elem_ver_structure.status_code%TYPE;
13446: l_amg_segment1 pa_projects_all.segment1%TYPE;
13447:
13448: -- Bug 3075609
13449: l_maintain_weightages VARCHAR2(1); -- We need not maintain weightages during code flow always. This var will track it.
13450: l_existing_task_set_max_wt VARCHAR2(1); -- If a task is indented/ moved under a parent with no other progressible task, this
13566: l_add_end_date SYSTEM.pa_date_tbl_type := SYSTEM.pa_date_tbl_type();
13567: l_pm_product_code SYSTEM.PA_VARCHAR2_30_TBL_TYPE := SYSTEM.PA_VARCHAR2_30_TBL_TYPE();
13568: l_add_plan_task_id NUMBER;
13569:
13570: CURSOR cur_wp_task_csr_duplicate (c_project_id pa_projects_all.project_id%TYPE,
13571: c_pm_source_reference pa_proj_elements.pm_source_reference%TYPE) IS
13572: SELECT proj_element_id
13573: FROM pa_proj_elements pelem
13574: WHERE project_id = c_project_id
13651: l_prev_percent_comp Number;
13652: l_update_task_det_flag varchar2(1);
13653:
13654: -- Bug 6717386
13655: CURSOR cur_struc_ver_wp(c_project_id pa_projects_all.project_id%TYPE, c_structure_type pa_structure_types.structure_type%TYPE)
13656: IS
13657: SELECT c.element_version_id
13658: FROM pa_proj_element_versions c, pa_structure_types a, pa_proj_structure_types b
13659: ,pa_proj_elem_ver_structure d
14218: DECLARE
14219: /* Bug 3588013 - Added the exists clause for this bug to get proj_element_id for the correct structure
14220: Task Reference should be unique with in structure.
14221: */
14222: CURSOR cur_wp_task_csr (c_project_id pa_projects_all.project_id%TYPE,
14223: c_pm_source_reference pa_proj_elements.pm_source_reference%TYPE) IS
14224: SELECT proj_element_id
14225: FROM pa_proj_elements pelem
14226: WHERE project_id = c_project_id
17482: ( p_api_version_number IN NUMBER := 1.0
17483: ,p_commit IN VARCHAR2 := FND_API.G_FALSE
17484: ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
17485: ,p_calling_context IN VARCHAR2 := 'UPDATE'
17486: ,p_project_id IN pa_projects_all.project_id%TYPE
17487: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
17488: ,p_pub_struc_ver_id IN NUMBER := NULL
17489: ,p_pub_prog_flag IN VARCHAR2 := 'Y' --bug 4019845
17490: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
17494:
17495: -- Cursors used in this Procedure.
17496:
17497: -- This cursor obtains the count of the task versions in the structure version.
17498: CURSOR cur_task_count(c_project_id pa_projects_all.project_id%TYPE,
17499: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE)
17500: IS
17501: Select count(element_version_id)
17502: From pa_proj_element_versions
17742: ( p_api_version_number IN NUMBER := 1.0
17743: ,p_commit IN VARCHAR2 := FND_API.G_FALSE
17744: ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
17745: ,p_calling_context IN VARCHAR2 := 'UPDATE'
17746: ,p_project_id IN pa_projects_all.project_id%TYPE
17747: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
17748: ,p_pub_struc_ver_id IN NUMBER := NULL
17749: ,p_pub_prog_flag IN VARCHAR2 := 'Y' --bug 4019845
17750: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
17756:
17757: -- This cursor is used to decide if the structure version corresponds to a particular
17758: -- Structure type. Depending on this the plannable tasks api and the task weightage calculation
17759: -- api will be called.
17760: Cursor cur_structure_type(c_project_id pa_projects_all.project_id%TYPE,
17761: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE,
17762: c_structure_type pa_structure_types.structure_type%TYPE)
17763: IS
17764: select 'Y'
17812:
17813: Cursor cur_proj_ou
17814: IS
17815: select org_id
17816: from pa_projects_all
17817: where project_id = p_project_id;
17818:
17819: l_org_id NUMBER;
17820:
18112: PROCEDURE PROCESS_WBS_UPDATES_CONC
18113: ( errbuf OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
18114: ,retcode OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
18115: ,p_calling_context IN VARCHAR2 := 'UPDATE'
18116: ,p_project_id IN pa_projects_all.project_id%TYPE
18117: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
18118: ,p_pub_struc_ver_id IN NUMBER := NULL
18119: ,p_pub_prog_flag IN VARCHAR2 := 'Y' --bug 4019845
18120: ,p_rerun_flag IN VARCHAR2 := null --bug 4589289
18436: ( p_api_version_number IN NUMBER := 1.0
18437: ,p_commit IN VARCHAR2 := FND_API.G_FALSE
18438: ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
18439: ,p_calling_context IN VARCHAR2 := 'UPDATE'
18440: ,p_project_id IN pa_projects_all.project_id%TYPE
18441: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
18442: ,p_pub_struc_ver_id IN NUMBER := NULL
18443: ,p_pub_prog_flag IN VARCHAR2 := 'Y' --bug 4019845
18444: ,p_rerun_flag IN VARCHAR2 := null --bug 4589289
18451:
18452: -- This cursor is used to decide if the structure version corresponds to a particular
18453: -- Structure type. Depending on this the plannable tasks api and the task weightage calculation
18454: -- api will be called.
18455: CURSOR cur_structure_type(c_project_id pa_projects_all.project_id%TYPE,
18456: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE,
18457: c_structure_type pa_structure_types.structure_type%TYPE)
18458: IS
18459: SELECT 'Y'
18511:
18512: CURSOR get_proj_rec_ver_number(c_project_id NUMBER)
18513: IS
18514: SELECT record_version_number
18515: FROM pa_projects_all
18516: WHERE project_id = c_project_id;
18517:
18518: CURSOR get_struc_id(c_project_id NUMBER, c_struc_ver_id NUMBER) IS
18519: select pevs.proj_element_id
19242: IF (l_financial_type = 'Y') THEN
19243:
19244: SELECT start_date, completion_date
19245: INTO l_proj_start_date, l_proj_completion_date
19246: FROM pa_projects_all
19247: WHERE project_id = l_project_id;
19248:
19249: --Call sync-up API
19250:
20340: ( p_api_version_number IN NUMBER
20341: ,p_commit IN VARCHAR2
20342: ,p_init_msg_list IN VARCHAR2
20343: ,p_calling_context IN VARCHAR2
20344: ,p_project_id IN pa_projects_all.project_id%TYPE
20345: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
20346: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
20347: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
20348: ,x_msg_data OUT NOCOPY VARCHAR2) --File.Sql.39 bug 4440895
20390: -- Cursor Declarations.
20391: -- This cursor fetches the duration, effort, relationship id between the task and its parent
20392: -- and progress entry enable flag for the task type associated with the task version. This
20393: -- info is necessary for calculating the task weightage of the tasks.
20394: CURSOR cur_task_info(c_project_id pa_projects_all.project_id%TYPE,
20395: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE
20396: ,c_plan_version_id NUMBER --bug 4726889
20397: )
20398: IS
20431: ;
20432:
20433: -- TWUT
20434: -- This cursor is when fetched will lock all the task versions of the structure version.
20435: CURSOR cur_task_ver_lock(c_project_id pa_projects_all.project_id%TYPE,
20436: c_structure_version_id pa_proj_element_versions.element_version_id%TYPE)
20437: IS
20438: select 'X'
20439: from pa_proj_element_versions ver,
20885: ( p_api_version_number IN NUMBER
20886: ,p_commit IN VARCHAR2
20887: ,p_init_msg_list IN VARCHAR2
20888: ,p_calling_context IN VARCHAR2
20889: ,p_project_id IN pa_projects_all.project_id%TYPE
20890: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
20891: ,p_update_wbs_flag IN pa_proj_elem_ver_structure.process_update_wbs_flag%TYPE
20892: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
20893: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
21028: -- package level constants FND_API.G_TRUE , FND_API.G_FLASE
21029: -- can not be reffered .
21030:
21031: PROCEDURE PROCESS_WBS_UPDATES_WRP_FORM
21032: ( p_project_id IN pa_projects_all.project_id%TYPE
21033: ,p_structure_version_id IN pa_proj_element_versions.element_version_id%TYPE
21034: ,p_pub_struc_ver_id IN NUMBER := NULL
21035: ,x_return_status OUT NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
21036: ,x_msg_count OUT NOCOPY NUMBER --File.Sql.39 bug 4440895
21768: ) RETURN VARCHAR2
21769: IS
21770: Cursor get_proj_share_type
21771: IS
21772: SELECT proj.structure_sharing_code FROM pa_projects_all proj where proj.project_id = P_Project_ID;
21773:
21774: l_share_code VARCHAR2(250);
21775:
21776: Begin