[Home] [Help]
46: where lookup_type='PM_PRODUCT_CODE'
47: and lookup_code = p_pm_product_code;
48:
49: --FP.M Changes by rtarway for Mapping and Set Financial Task
50: --This cursor gets the record version number from pa_tasks for the passed task version id
51: CURSOR c_get_pa_record_version_number (l_task_version_id NUMBER , l_project_id NUMBER)
52: IS
53: SELECT
54: allTasks.record_version_number
53: SELECT
54: allTasks.record_version_number
55:
56: FROM
57: PA_TASKS allTasks,
58: pa_proj_element_versions elever
59: WHERE
60: elever.element_version_id = l_task_version_id
61: AND
4467: );
4468:
4469: --Project Connect 4.0
4470:
4471: --syns up pa_proj_elements with pa_tasks
4472: /* not required this anymore
4473:
4474: --Clean up first the unwanted tasks that are created by copy_structure in pa_proj_elements
4475: --although they had not been created in pa_tasks.
4471: --syns up pa_proj_elements with pa_tasks
4472: /* not required this anymore
4473:
4474: --Clean up first the unwanted tasks that are created by copy_structure in pa_proj_elements
4475: --although they had not been created in pa_tasks.
4476:
4477: PA_PROJ_TASK_STRUC_PUB.Clean_unwanted_tasks(
4478: p_project_id => l_project_id
4479: ,x_msg_count => l_msg_count
4525:
4526: IF l_bug_wp_enabled = 'N' AND l_bug_fp_enabled = 'N' AND p_tasks_in.exists(i)
4527: THEN
4528: PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA',
4529: p_msg_name =>'PA_WRONG_STR_TYPE_AMG'); -- 'PA_TASKS_NOT_ALLOWD_AMG' -- It is decided to use the same message in this case too
4530: -- Workplan or Financial Structures must be enabled at source project/template level in order to enter Tasks.
4531: RAISE FND_API.G_EXC_ERROR ;
4532: ELSIF l_bug_wp_enabled = 'Y' AND l_bug_fp_enabled = 'N' AND p_tasks_in.exists(i) AND l_structure_in_rec.structure_type = 'FINANCIAL'
4533: THEN
5212: l_task_in_rec := p_tasks_in(i);
5213: l_patask_record_version_number := NULL;
5214: --No Function Security Check required
5215:
5216: --Get record version number from pa_tasks table
5217: OPEN c_get_pa_record_version_number ( p_tasks_out(i).task_version_id , l_project_id);
5218: FETCH c_get_pa_record_version_number INTO l_patask_record_version_number;
5219: CLOSE c_get_pa_record_version_number;
5220:
5229: l_task_in_rec.financial_task_flag <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
5230: )
5231: THEN
5232:
5233: PA_TASKS_MAINT_PUB.SET_FINANCIAL_FLAG_WRAPPER
5234: (
5235: p_api_version => p_api_version_number
5236: , p_init_msg_list => FND_API.G_FALSE
5237: , p_commit => FND_API.G_FALSE--BUG 3766967
5759: l_project_type_rec pa_project_types%rowtype;
5760: l_project_rec pa_projects%rowtype;
5761: l_task_in_rec task_in_rec_type;
5762: l_project_id pa_projects.project_id%type;
5763: l_task_id pa_tasks.task_id%type;
5764: l_msg_count NUMBER ;
5765: l_msg_data VARCHAR2(2000);
5766: l_function_allowed VARCHAR2(1);
5767: l_resp_id NUMBER := 0;
5782:
5783: --Added for bug 3280610
5784: --needed to get task_id of a task using project_id and task_reference.
5785: CURSOR l_task_ref_to_id_csr
5786: (cp_project_id pa_tasks.project_id%TYPE
5787: ,cp_pm_task_reference pa_tasks.pm_task_reference%TYPE)
5788: IS
5789: SELECT t.task_id
5790: FROM pa_tasks t
5783: --Added for bug 3280610
5784: --needed to get task_id of a task using project_id and task_reference.
5785: CURSOR l_task_ref_to_id_csr
5786: (cp_project_id pa_tasks.project_id%TYPE
5787: ,cp_pm_task_reference pa_tasks.pm_task_reference%TYPE)
5788: IS
5789: SELECT t.task_id
5790: FROM pa_tasks t
5791: WHERE t.project_id = cp_project_id
5786: (cp_project_id pa_tasks.project_id%TYPE
5787: ,cp_pm_task_reference pa_tasks.pm_task_reference%TYPE)
5788: IS
5789: SELECT t.task_id
5790: FROM pa_tasks t
5791: WHERE t.project_id = cp_project_id
5792: AND t.pm_task_reference = cp_pm_task_reference;
5793:
5794: --Bug 3279981. Fetch project information
5867:
5868: -- End fix for Bug # 4373055.
5869:
5870: --Added for Bug 6026370
5871: CURSOR l_default_parent_task_csr (c_pa_parent_task_id pa_tasks.task_id%type) IS
5872: SELECT allow_cross_charge_flag,
5873: cc_process_labor_flag,
5874: labor_tp_schedule_id,
5875: labor_tp_fixed_date,
5875: labor_tp_fixed_date,
5876: cc_process_nl_flag,
5877: nl_tp_schedule_id,
5878: nl_tp_fixed_date
5879: FROM pa_tasks
5880: WHERE task_id = c_pa_parent_task_id;
5881:
5882:
5883:
7005:
7006: --we need to update to pm_product_code because it will be inherited
7007: --from the project in add_task_round_one
7008:
7009: UPDATE pa_tasks
7010: SET pm_product_code = p_pm_product_code
7011: WHERE task_id = l_task_id;
7012:
7013:
7355: CLOSE c_get_pa_record_version_number;
7356: IF
7357: (p_financial_task_flag IS NOT NULL AND p_financial_task_flag <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
7358: THEN
7359: PA_TASKS_MAINT_PUB.SET_FINANCIAL_FLAG_WRAPPER
7360: (
7361: p_api_version => p_api_version_number
7362: , p_init_msg_list => FND_API.G_FALSE
7363: , p_commit => FND_API.G_FALSE--bug 3766967
9848: AND d.distribution_rule = l_dist_rule;
9849:
9850: CURSOR l_min_task_start_date_csr (l_project_id NUMBER) IS
9851: SELECT MIN(start_date)
9852: FROM pa_tasks
9853: WHERE project_id = l_project_id
9854: AND start_date IS NOT NULL;
9855:
9856: CURSOR l_max_task_end_date_csr (l_project_id NUMBER) IS
9854: AND start_date IS NOT NULL;
9855:
9856: CURSOR l_max_task_end_date_csr (l_project_id NUMBER) IS
9857: SELECT MAX(completion_date)
9858: FROM pa_tasks
9859: WHERE project_id = l_project_id
9860: AND completion_date IS NOT NULL;
9861:
9862: --needed to get the project_type_class_code and service_type_code associated
9928: AND class_category = l_class_category;
9929:
9930: CURSOR l_get_task_csr (l_project_id NUMBER,l_pm_task_reference VARCHAR2) IS
9931: SELECT task_id FROM
9932: pa_tasks
9933: WHERE project_id = l_project_id
9934: AND pm_task_reference = l_pm_task_reference;
9935:
9936: --needed to get the data of the project manager that might conflict with this new project manager
10464: -- through this variable.
10465: l_create_task_versions_only VARCHAR2(1) := 'N';
10466: -- Bug 6248841
10467: l_b_cc_tax_task_id VARCHAR2(1) := 'N';
10468: l_cc_tax_task_numb PA_TASKS.TASK_NUMBER%TYPE;
10469:
10470: /* Bug 3077676 */
10471: l_cust_acc_rel_code pa_implementations.cust_acc_rel_code%TYPE;
10472: CURSOR get_cust_acc_rel_code IS
10877: FROM pa_project_statuses pps
10878: WHERE pps.project_status_code = nvl(c_status_code,' ');
10879:
10880: /*Adding new cursor for bug 10043448 */
10881: CURSOR cur_inv_date(x_task_id pa_tasks.task_id%type) IS
10882: SELECT MAX(EXPENDITURE_ITEM_DATE)
10883: FROM ap_invoice_distributions_all aid
10884: WHERE project_id = p_project_in.pa_project_id AND
10885: aid.task_id IN (SELECT p.task_id
10882: SELECT MAX(EXPENDITURE_ITEM_DATE)
10883: FROM ap_invoice_distributions_all aid
10884: WHERE project_id = p_project_in.pa_project_id AND
10885: aid.task_id IN (SELECT p.task_id
10886: FROM pa_tasks p
10887: START WITH p.task_id= x_task_id
10888: CONNECT BY PRIOR p.task_id = p.parent_task_id) ;
10889:
10890: x_si_date ap_invoices_all.invoice_date%TYPE ;
11494: IF PA_PROJECT_PUB.G_Published_version_exists = 'Y'
11495: AND PA_PROJECT_PUB.G_IS_WP_SEPARATE_FROM_FN = 'N'
11496: AND PA_PROJECT_PUB.G_IS_WP_VERSIONING_ENABLED = 'Y'
11497: AND p_structure_in.structure_type = 'FINANCIAL'
11498: AND p_pm_product_code <> 'WORKPLAN' --This parameter should be checked bcoz for publishing we want it to syn up with pa_tasks . For publishing p_pm_product_code will have 'WORKPLAN'
11499: THEN
11500: --Change the flow to WORKPLAN
11501: l_structure_type := 'WORKPLAN';
11502:
15710: THEN
15711:
15712: BEGIN
15713: SELECT task_number INTO l_cc_tax_task_numb
15714: FROM pa_tasks
15715: WHERE project_id = l_project_id
15716: AND task_id = p_project_in.cc_tax_task_id;
15717:
15718: l_statement := l_statement || ' CC_TAX_TASK_ID = :prj_cc_tax_task_id,';
17746:
17747: /** Code change begin by aditi for Bug 4120380 **/
17748: /** The code below will update the task_numbers of the tasks passed via update_project script
17749: back to their original value viz. it'll crop the '-' added to aviod the constraint check in
17750: PA_TASKS and PA_PROJ_ELEMENTS. **/
17751:
17752: /* bug #5243018: Reverting the fix in bug 4120380.
17753: i := 1;
17754: IF p_tasks_in.exists(i)
17753: i := 1;
17754: IF p_tasks_in.exists(i)
17755: THEN
17756: WHILE i IS NOT NULL LOOP
17757: UPDATE pa_tasks
17758: SET task_number = p_tasks_in(i).pa_task_number
17759: WHERE task_number = '-'||p_tasks_in(i).pa_task_number
17760: AND project_id = l_project_id; -- BUG 4174041, rtarway
17761: ----dbms_output.put_line('value of sql%rowcount'||sql%rowcount);
17935: -- Bug 7277840 : Update PM_PROJECT_CODE
17936: IF p_pm_product_code <> 'WORKPLAN' AND p_pm_product_code IS NOT NULL
17937: AND p_pm_product_code <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
17938: THEN
17939: UPDATE pa_tasks
17940: SET pm_product_code = p_pm_product_code
17941: WHERE task_id = l_task_id_tbl(i).pa_task_id;
17942: END IF;
17943:
18043: --The following code should be executed when a new task is added
18044: --to a working version( with already existing a published ver ) and
18045: --then its published.
18046: --In other words the code is executed while publishing to sync up with
18047: --pa_tasks. In regular flow for adding new tasks we call this api
18048: --from the wrapper process_task_structure_bulk api.
18049: --
18050: IF l_structure_type = 'FINANCIAL' AND
18051: p_pm_product_code = 'WORKPLAN'
19823: end if;
19824: -- Bug 5622539
19825:
19826: IF hghst_ctr_cust_id IS NOT null THEN
19827: --This API call will update pa_project_customers and pa_tasks
19828: pa_top_task_cust_invoice_pvt.enbl_disbl_cust_at_top_task(
19829: P_API_VERSION => 1.0
19830: , P_INIT_MSG_LIST => 'T'
19831: , P_COMMIT => 'F'
19933: FETCH cur_get_def_top_task_cust INTO cust_id;
19934: CLOSE cur_get_def_top_task_cust;
19935:
19936: IF hghst_ctr_cust_id IS NOT NULL THEN
19937: --This API call will update pa_project_customers and pa_tasks
19938: pa_top_task_cust_invoice_pvt.enbl_disbl_cust_at_top_task(
19939: P_API_VERSION => 1.0
19940: , P_INIT_MSG_LIST => 'T'
19941: , P_COMMIT => 'F'
21367: , x_msg_count => l_msg_count
21368: , x_msg_data => l_msg_data );
21369:
21370: IF hghst_ctr_cust_id IS NOT null THEN
21371: --This API call will update pa_project_customers and pa_tasks
21372: pa_top_task_cust_invoice_pvt.enbl_disbl_cust_at_top_task(
21373: P_API_VERSION => 1.0
21374: , P_INIT_MSG_LIST => 'T'
21375: , P_COMMIT => 'F'
21435: FETCH cur_get_def_top_task_cust INTO cust_id;
21436: CLOSE cur_get_def_top_task_cust;
21437:
21438: IF hghst_ctr_cust_id IS NOT NULL THEN
21439: --This API call will update pa_project_customers and pa_tasks
21440: pa_top_task_cust_invoice_pvt.enbl_disbl_cust_at_top_task(
21441: P_API_VERSION => 1.0
21442: , P_INIT_MSG_LIST => 'T'
21443: , P_COMMIT => 'F'
21946: --Initialize the return status to success
21947: p_tasks_out(i).return_status := FND_API.G_RET_STS_SUCCESS;
21948: l_task_in_rec := p_tasks_in(i);
21949: --No Function Security Check required
21950: --Get record version number from pa_tasks table
21951: OPEN c_get_pa_record_version_number ( p_tasks_out(i).task_version_id , l_project_id);
21952: FETCH c_get_pa_record_version_number INTO l_patask_record_version_number;
21953: CLOSE c_get_pa_record_version_number;
21954:
21958: AND
21959: l_task_in_rec.financial_task_flag <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR
21960: )
21961: THEN
21962: PA_TASKS_MAINT_PUB.SET_FINANCIAL_FLAG_WRAPPER
21963: (
21964: p_api_version => p_api_version_number
21965: , p_init_msg_list => FND_API.G_FALSE
21966: , p_commit => FND_API.G_FALSE--bug 3766967
22823: SELECT customer_name from PA_CUSTOMERS_V
22824: WHERE customer_id = l_customer_id;
22825: -- Added for bug 5242015
22826: CURSOR cur_top_task_customer_id (l_task_id NUMBER ) IS
22827: SELECT customer_id FROM pa_tasks
22828: WHERE task_id=l_task_id;
22829:
22830: Cursor c_adj_on_std_inv (p_adj_on_std_inv IN VARCHAR2)
22831: Is
22937: and proj_element_id = l_proj_element_id
22938: and parent_structure_version_id = l_parent_str_ver_id;
22939:
22940: --BUG 4106154, rtarway
22941: l_update_pa_tasks VARCHAR2(1);
22942:
22943: l_tasks_out pa_project_pub.task_out_tbl_type; --bug 4199694
22944:
22945: --BUG 4081329, rtarway
22943: l_tasks_out pa_project_pub.task_out_tbl_type; --bug 4199694
22944:
22945: --BUG 4081329, rtarway
22946: cursor cur_get_child_task_dates (l_project_id NUMBER, l_task_id NUMBER)
22947: IS select task_id, start_date, completion_date, parent_task_id from pa_tasks --Fix for 7427388
22948: where project_id = l_project_id
22949: and completion_date is null
22950: start with parent_task_id = l_task_id
22951: connect by parent_task_id = prior task_id
22952: and project_id = l_project_id;
22953:
22954: --Bug#7015228
22955: cursor cur_get_child_task_start_dates (l_project_id NUMBER, l_task_id NUMBER)
22956: IS select task_id, start_date, completion_date from pa_tasks
22957: where project_id = l_project_id
22958: and start_date is null
22959: start with parent_task_id = l_task_id
22960: connect by parent_task_id = prior task_id
22959: start with parent_task_id = l_task_id
22960: connect by parent_task_id = prior task_id
22961: and project_id = l_project_id;
22962:
22963: type l_task_id_tbl_type is table of pa_tasks.task_id%type index by binary_integer;
22964: type l_start_date_tbl_type is table of pa_tasks.start_date%type index by binary_integer;
22965: type l_completion_date_tbl_type is table of pa_tasks.completion_date%type index by binary_integer;
22966:
22967: l_task_id_tbl l_task_id_tbl_type;
22960: connect by parent_task_id = prior task_id
22961: and project_id = l_project_id;
22962:
22963: type l_task_id_tbl_type is table of pa_tasks.task_id%type index by binary_integer;
22964: type l_start_date_tbl_type is table of pa_tasks.start_date%type index by binary_integer;
22965: type l_completion_date_tbl_type is table of pa_tasks.completion_date%type index by binary_integer;
22966:
22967: l_task_id_tbl l_task_id_tbl_type;
22968: l_start_date_tbl l_start_date_tbl_type;
22961: and project_id = l_project_id;
22962:
22963: type l_task_id_tbl_type is table of pa_tasks.task_id%type index by binary_integer;
22964: type l_start_date_tbl_type is table of pa_tasks.start_date%type index by binary_integer;
22965: type l_completion_date_tbl_type is table of pa_tasks.completion_date%type index by binary_integer;
22966:
22967: l_task_id_tbl l_task_id_tbl_type;
22968: l_start_date_tbl l_start_date_tbl_type;
22969: l_completion_date_tbl l_completion_date_tbl_type;
22968: l_start_date_tbl l_start_date_tbl_type;
22969: l_completion_date_tbl l_completion_date_tbl_type;
22970:
22971: -- Fix for 7427388
22972: type l_parent_task_id_tbl_type is table of pa_tasks.parent_task_id%type index by binary_integer;
22973: l_parent_task_id_tbl l_parent_task_id_tbl_type;
22974: l_parent_task_date DATE;
22975:
22976: --BUG 4081329, rtarway
23042:
23043: if (p_pa_task_id <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM) AND
23044: (p_pa_task_id IS NOT NULL) then
23045:
23046: select parent_task_id,top_task_id into l_parent_task_id,l_top_task_id from pa_tasks where task_id = p_pa_task_id and project_id = p_pa_project_id;
23047:
23048: else
23049: select parent_task_id ,top_task_id into l_parent_task_id,l_top_task_id from pa_tasks where task_number = p_task_number and project_id = p_pa_project_id;
23050:
23045:
23046: select parent_task_id,top_task_id into l_parent_task_id,l_top_task_id from pa_tasks where task_id = p_pa_task_id and project_id = p_pa_project_id;
23047:
23048: else
23049: select parent_task_id ,top_task_id into l_parent_task_id,l_top_task_id from pa_tasks where task_number = p_task_number and project_id = p_pa_project_id;
23050:
23051: end if;
23052:
23053:
23060: end if;
23061:
23062: If l_adj_on_std_inv is not null then
23063:
23064: update pa_tasks set adj_on_std_inv =p_adj_on_std_inv where task_id =p_pa_task_id;
23065: end if;
23066: if p_pa_task_Id = l_top_task_id
23067: then
23068:
23065: end if;
23066: if p_pa_task_Id = l_top_task_id
23067: then
23068:
23069: update pa_tasks set adj_on_std_inv = p_adj_on_std_inv where top_task_id = p_pa_task_Id;
23070: end if;
23071:
23072:
23073: /* Added for 12.2Payroll billing ER 11899223 */
23177: AND p_pm_product_code <> 'WORKPLAN'
23178: THEN
23179: --Change the flow to WORKPLAN
23180: l_structure_type := 'WORKPLAN';
23181: l_update_pa_tasks := 'Y'; --BUG 4106154, rtarway
23182:
23183: --BUG 3919800, in case of workplan, we must derive structure version id and pass it further.
23184: OPEN cur_get_working_struc_ver(l_project_id, l_structure_type);
23185: FETCH cur_get_working_struc_ver into l_structure_version_id;
23370: --Else get it from project invoice method.
23371: BEGIN
23372: SELECT invoice_method
23373: INTO l_invoice_method
23374: FROM pa_tasks
23375: WHERE project_id = l_project_id
23376: and task_id = l_pa_task_id
23377: and l_invoice_method is not null;
23378:
23510: --IF 'N' = l_proj_top_task_customer_flag
23511: l_customer_id := NULL;
23512: END IF;
23513:
23514: /*UPDATE PA_TASKS
23515: SET invoice_method = l_invoice_method
23516: ,customer_id = l_customer_id
23517: WHERE project_id = l_project_id
23518: AND task_id = p_pa_task_id;*/
23865: l_completion_date_tbl;
23866: close cur_get_child_task_dates;
23867: if l_task_id_tbl is not null and l_task_id_tbl.count > 0 then
23868: FORALL i in l_task_id_tbl.first..l_task_id_tbl.last
23869: update pa_tasks set completion_date = p_task_completion_date where task_id = l_task_id_tbl(i)
23870: and project_id = l_project_id;
23871: end if;
23872: end if;*/
23873: -- Added one more condition to check rollup flag to defer the date updates 14556729
23882: FOR i in l_task_id_tbl.first..l_task_id_tbl.last LOOP
23883: IF l_parent_task_id_tbl(i) IS NOT NULL AND l_start_date_tbl(i) is NULL THEN
23884: SELECT start_date
23885: INTO l_parent_task_date
23886: FROM pa_tasks
23887: WHERE task_id = l_parent_task_id_tbl(i);
23888:
23889: UPDATE pa_tasks
23890: SET completion_date = p_task_completion_date,
23885: INTO l_parent_task_date
23886: FROM pa_tasks
23887: WHERE task_id = l_parent_task_id_tbl(i);
23888:
23889: UPDATE pa_tasks
23890: SET completion_date = p_task_completion_date,
23891: start_date = l_parent_task_date
23892: WHERE task_id = l_task_id_tbl(i);
23893:
23891: start_date = l_parent_task_date
23892: WHERE task_id = l_task_id_tbl(i);
23893:
23894: ELSE
23895: UPDATE pa_tasks
23896: SET completion_date = p_task_completion_date
23897: WHERE task_id = l_task_id_tbl(i);
23898: END IF;
23899: END LOOP;
23915: l_completion_date_tbl;
23916: close cur_get_child_task_start_dates;
23917: if l_task_id_tbl is not null and l_task_id_tbl.count > 0 then
23918: FORALL i in l_task_id_tbl.first..l_task_id_tbl.last
23919: update pa_tasks set start_date = p_task_start_date where task_id = l_task_id_tbl(i)
23920: and project_id = l_project_id;
23921: end if;
23922: end if;
23923:
24152: -- Bug 3106457. This is not null.
24153: --Commented by rtarway for BUG 3919800
24154: --l_structure_version_id := p_structure_version_id;
24155: --Added by rtarway for BUG 4106154
24156: if (l_update_pa_tasks = 'Y' AND
24157: --Added by rtarway for bug 4321313
24158: PA_PROJ_ELEMENTS_UTILS.CHECK_IS_FINANCIAL_TASK(l_pa_task_id) = 'Y'
24159: ) then
24160:
24157: --Added by rtarway for bug 4321313
24158: PA_PROJ_ELEMENTS_UTILS.CHECK_IS_FINANCIAL_TASK(l_pa_task_id) = 'Y'
24159: ) then
24160:
24161: --In case of Shared Structures, we do not need to update dff attributes in pa_tasks, instead
24162: --dff fields of pa_proj_elem_ver_schedule are updated
24163:
24164: PA_PROJECT_PVT.Update_One_Task
24165: (
24438: FETCH c_get_pa_record_version_number INTO l_patask_record_version_number;
24439: CLOSE c_get_pa_record_version_number;
24440: IF ( p_financial_task_flag IS NOT NULL AND p_financial_task_flag <> PA_INTERFACE_UTILS_PUB.G_PA_MISS_CHAR)
24441: THEN
24442: PA_TASKS_MAINT_PUB.SET_FINANCIAL_FLAG_WRAPPER
24443: (
24444: p_api_version => p_api_version_number
24445: , p_init_msg_list => p_init_msg_list
24446: , p_commit => FND_API.G_FALSE--bug 3766967
35921: -- work for both split and shared projects.
35922: /*
35923:
35924: CURSOR l_amg_task_csr
35925: (p_pa_task_id pa_tasks.task_id%type)
35926: IS
35927: SELECT task_number
35928: FROM pa_tasks p
35929: WHERE p.task_id = p_pa_task_id;
35924: CURSOR l_amg_task_csr
35925: (p_pa_task_id pa_tasks.task_id%type)
35926: IS
35927: SELECT task_number
35928: FROM pa_tasks p
35929: WHERE p.task_id = p_pa_task_id;
35930:
35931: */
35932:
35931: */
35932:
35933: -- The Following cursor has been commented for Performance Bug 4878722 SQL ID 14909071
35934: -- The purpose of this cursor per usage in this API is to verify whether the task exists in any of the previously published versions
35935: -- cursor l_amg_task_csr (p_pa_task_id pa_tasks.task_id%type) is
35936: -- select ppe.element_number
35937: -- from pa_proj_elements ppe, pa_proj_elem_ver_structure ppevs
35938: -- where ppe.proj_element_id = p_pa_task_id
35939: -- and ppe.parent_structure_id = ppevs.proj_element_id
35967: The correct way of doing this is to get the task status directly from the element versions table itself as shown below
35968: and modify the logic to use the above column value.
35969:
35970:
35971: cursor l_amg_task_csr (p_pa_task_id pa_tasks.task_id%type) is
35972: select 1 from dual
35973: where exists (select ppe.element_number
35974: from pa_proj_elements ppe, pa_proj_elem_ver_structure ppevs
35975: where ppe.proj_element_id = p_pa_task_id
35972: select 1 from dual
35973: where exists (select ppe.element_number
35974: from pa_proj_elements ppe, pa_proj_elem_ver_structure ppevs
35975: where ppe.proj_element_id = p_pa_task_id
35976: and ppe.object_type = 'PA_TASKS'
35977: and ppe.parent_structure_id = ppevs.proj_element_id
35978: and ppe.project_id = ppevs.project_id
35979: and ppevs.status_code = 'STRUCTURE_PUBLISHED');
35980: */
35978: and ppe.project_id = ppevs.project_id
35979: and ppevs.status_code = 'STRUCTURE_PUBLISHED');
35980: */
35981:
35982: cursor l_amg_task_csr (p_pa_task_id pa_tasks.task_id%type) is
35983: select 1 from dual
35984: where exists(
35985: select pev.task_unpub_ver_status_code
35986: from pa_proj_element_versions pev
35984: where exists(
35985: select pev.task_unpub_ver_status_code
35986: from pa_proj_element_versions pev
35987: where pev.proj_element_id = p_pa_task_id
35988: and pev.object_type = 'PA_TASKS'
35989: and pev.task_unpub_ver_status_code in ('PUBLISHED','TO_BE_DELETED'));
35990: -- End fix for Bug 5263429
35991: -- End fix for Bug # 4096218 : 09-AUG-2005.
35992:
36199: IF l_amg_task_number IS NULL -- It means ,task is only in current working version,not in any previous pub. versions
36200: THEN
36201: -- It can be deleted, there is no need to mark it for deletion.
36202: l_check_task_mfd_flag := 'Y';
36203: ELSE -- Task exists in prior published version and it has entry in pa_tasks.
36204: -- So, Mark it for deletion ,so that it can be deleted in publishing flow
36205:
36206: /* This API call marks the task for deletion in OP
36207: PA_TASK_PVT1.update_task_ver_delete_status(
38637: cursor c_newly_added_tasks(l_published_version IN NUMBER,l_prev_published_version IN NUMBER) is
38638: select proj_element_id
38639: from pa_proj_element_versions
38640: where project_id = P_PA_PROJECT_ID
38641: and object_type = 'PA_TASKS'
38642: and PARENT_STRUCTURE_VERSION_ID = l_published_version
38643: MINUS
38644: select proj_element_id
38645: from pa_proj_element_versions
38643: MINUS
38644: select proj_element_id
38645: from pa_proj_element_versions
38646: where project_id = P_PA_PROJECT_ID
38647: and object_type = 'PA_TASKS'
38648: and PARENT_STRUCTURE_VERSION_ID = l_prev_published_version;
38649:
38650:
38651: BEGIN
38659: FOR rec IN c_newly_added_tasks(l_published_version, l_prev_published_version) LOOP
38660: FOR i IN p_task_id_tbl.FIRST .. p_task_id_tbl.LAST LOOP
38661: IF p_task_id_tbl(i) = rec.proj_element_id THEN
38662:
38663: UPDATE pa_tasks
38664: SET service_type_code = nvl(p_service_type_code_tbl(i),service_type_code), -- 7525628
38665: chargeable_flag = nvl(p_chargeable_flag_tbl(i),chargeable_flag), -- 7525628
38666: billable_flag = nvl(p_billable_flag_tbl(i),billable_flag) -- 7525628
38667: WHERE task_id = p_task_id_tbl(i);
38731: CURSOR get_task_details_csr (c_project_id NUMBER) IS
38732: SELECT element_version_id,proj_element_id,display_sequence,wbs_level,wbs_number
38733: FROM pa_proj_element_versions
38734: WHERE project_id = p_project_id
38735: AND object_type = 'PA_TASKS'
38736: ORDER BY display_sequence;
38737:
38738: l_project_details_rec get_project_details_csr%ROWTYPE;
38739: l_project_in_rec pa_project_pub.project_in_rec_type;
39042: WHERE project_id = c_project_id;
39043:
39044: CURSOR check_tasks_exists_csr(c_project_id NUMBER) IS
39045: SELECT 'Y'
39046: FROM pa_tasks
39047: WHERE project_id = c_project_id
39048: AND ROWNUM = 1;
39049:
39050: l_project_details_rec get_project_details_csr%ROWTYPE;
39967: CURSOR get_parent_version_id(c_elem_ver_id NUMBER) IS
39968: SELECT object_id_from1
39969: FROM pa_object_relationships
39970: WHERE object_id_to1 = c_elem_ver_id
39971: AND object_type_to = 'PA_TASKS'
39972: AND relationship_type = 'S';
39973:
39974: CURSOR cur_obj_rel( p_child_version_id NUMBER ) IS
39975: SELECT object_id_from1
39986: SELECT a.object_id_from1
39987: , a.weighting_percentage
39988: FROM pa_object_relationships a
39989: WHERE a.object_id_to1 = c_task_version_id
39990: AND a.object_type_to = 'PA_TASKS'
39991: AND a.relationship_type = 'S'
39992: AND a.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS');
39993:
39994: CURSOR get_sub_tasks(c_task_version_id NUMBER) IS
39988: FROM pa_object_relationships a
39989: WHERE a.object_id_to1 = c_task_version_id
39990: AND a.object_type_to = 'PA_TASKS'
39991: AND a.relationship_type = 'S'
39992: AND a.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS');
39993:
39994: CURSOR get_sub_tasks(c_task_version_id NUMBER) IS
39995: SELECT '1'
39996: FROM dual
39997: WHERE EXISTS
39998: (SELECT 'xyz'
39999: FROM pa_object_relationships
40000: WHERE object_id_from1 = c_task_version_id
40001: AND object_type_from IN ('PA_TASKS', 'PA_STRUCTURES')
40002: AND relationship_type = 'S'
40003: );
40004:
40005: CURSOR check_progress_allowed(c_element_version_id NUMBER) IS
40008: , pa_proj_element_versions ppev,
40009: pa_proj_elements ppe
40010: WHERE ppev.element_version_id = c_element_version_id
40011: AND ppev.proj_element_id = ppe.proj_element_id
40012: AND ptt.object_type = 'PA_TASKS'
40013: AND ppe.TYPE_ID = ptt.task_type_id;
40014:
40015:
40016:
40096: CURSOR get_wbs_number(c_elem_ver_id NUMBER) is
40097: select wbs_number
40098: from pa_proj_element_versions
40099: where element_version_id = c_elem_ver_id
40100: and object_type ='PA_TASKS';
40101:
40102: l_delete_project_allowed VARCHAR2(1);
40103: l_update_proj_num_allowed VARCHAR2(1);
40104: l_update_proj_name_allowed VARCHAR2(1);
40140: l_top_task_id NUMBER;
40141: l_old_top_task_id NUMBER; --bug 2967204
40142:
40143: CURSOR get_parent_task_id( c_task_id NUMBER, c_project_id NUMBER ) IS
40144: SELECT parent_task_id, top_task_id FROM pa_tasks
40145: WHERE project_id = c_project_id and task_id = c_task_id;
40146:
40147: l_wp_type VARCHAR2(1);
40148: l_weighting_basis_Code VARCHAR2(30);
40157: Cursor get_top_task_ver_id(c_task_ver_id NUMBER) IS
40158: select object_id_to1
40159: from pa_object_relationships
40160: where relationshiP_type = 'S'
40161: and object_type_to = 'PA_TASKS'
40162: start with object_id_to1 = c_task_ver_id
40163: and object_type_to = 'PA_TASKS'
40164: and relationship_type = 'S'
40165: connect by prior object_id_from1 = object_id_to1
40159: from pa_object_relationships
40160: where relationshiP_type = 'S'
40161: and object_type_to = 'PA_TASKS'
40162: start with object_id_to1 = c_task_ver_id
40163: and object_type_to = 'PA_TASKS'
40164: and relationship_type = 'S'
40165: connect by prior object_id_from1 = object_id_to1
40166: and prior object_type_from = object_type_to
40167: and prior relationship_type = relationship_type
40187: FROM PA_PROJ_ELEMENT_VERSIONS
40188: WHERE parent_structure_version_id = p_structure_version_id
40189: AND project_id = p_project_id
40190: AND wbs_level = c_src_wbs_level
40191: AND object_type = 'PA_TASKS'
40192: AND display_sequence < src_seq_number
40193: AND display_sequence =
40194: (SELECT max (display_sequence)
40195: FROM pa_proj_element_versions
40196: WHERE project_id = p_project_id
40197: AND parent_structure_version_id = p_structure_version_id
40198: AND wbs_level = c_src_wbs_level
40199: AND display_sequence < src_seq_number
40200: AND object_type = 'PA_TASKS');
40201:
40202: l_ref_parent_task_id NUMBER;
40203: message varchar2(2000);
40204: t_str varchar2(1);
40384: FROM PA_PROJ_ELEMENT_VERSIONS
40385: WHERE display_sequence = src_seq_number - 1
40386: AND parent_structure_version_id = p_structure_version_id
40387: AND project_id = p_project_id
40388: AND object_type = 'PA_TASKS';
40389:
40390: IF src_wbs_level > ref_wbs_level then
40391: PA_UTILS.ADD_MESSAGE( p_app_short_name => 'PA'
40392: ,p_msg_name => 'PA_PS_CANNOT_INDENT');
40406: FROM PA_PROJ_ELEMENT_VERSIONS
40407: WHERE parent_structure_version_id = p_structure_version_id
40408: AND project_id = p_project_id
40409: AND wbs_level = src_wbs_level+1
40410: AND object_type = 'PA_TASKS'
40411: AND display_sequence < src_seq_number
40412: AND display_sequence =
40413: (SELECT max (display_sequence)
40414: FROM pa_proj_element_versions
40415: WHERE project_id = p_project_id
40416: AND parent_structure_version_id = p_structure_version_id
40417: AND wbs_level = src_wbs_level+1
40418: AND display_sequence < src_seq_number
40419: AND object_type = 'PA_TASKS');
40420:
40421: OPEN cur_get_ref_tsk_ver_id(src_wbs_level);
40422: FETCH cur_get_ref_tsk_ver_id INTO l_ref_tsk_version_id;
40423: CLOSE cur_get_ref_tsk_ver_id;
40532: If PA_PROJECT_STRUCTURE_UTILS.get_Structure_sharing_code(p_project_id)
40533: IN ('SHARE_PARTIAL')
40534: Then
40535:
40536: PA_TASKS_MAINT_UTILS.CHECK_MOVE_FINANCIAL_TASK_OK (
40537: p_task_version_id => p_task_version_id
40538: , p_ref_task_version_id => ref_elem_ver_id
40539: , x_return_status => x_return_status
40540: , x_msg_count => x_msg_count
40598: pa_proj_element_versions
40599: WHERE
40600: project_id = p_project_id
40601: AND parent_structure_version_id = p_structure_version_id
40602: AND object_type = 'PA_TASKS'
40603: AND(
40604: element_version_id = p_task_version_id -- Source task itself
40605: OR (element_version_id IN -- All tasks below the source task with wbs_level >= src_wbs_level-1
40606: (select object_id_to1
40605: OR (element_version_id IN -- All tasks below the source task with wbs_level >= src_wbs_level-1
40606: (select object_id_to1
40607: from pa_object_relationships
40608: where relationship_type = 'S'
40609: and object_type_to = 'PA_TASKS'
40610: start with object_id_from1 = l_ref_parent_ver_id
40611: connect by object_id_from1 = PRIOR object_id_to1
40612: and relationship_type = PRIOR relationship_type
40613: and relationship_type = 'S')
40617: */
40618: select proj_element_id INTO l_ref_parent_task_id from pa_proj_element_versions
40619: where element_version_id = l_ref_parent_ver_id and project_id= p_project_id;
40620:
40621: insert into pa_tasks_tmp (task_id)
40622: SELECT task_id
40623: FROM pa_tasks
40624: WHERE project_id = p_project_id
40625: START WITH parent_task_id = l_ref_parent_task_id
40619: where element_version_id = l_ref_parent_ver_id and project_id= p_project_id;
40620:
40621: insert into pa_tasks_tmp (task_id)
40622: SELECT task_id
40623: FROM pa_tasks
40624: WHERE project_id = p_project_id
40625: START WITH parent_task_id = l_ref_parent_task_id
40626: CONNECT BY parent_task_id = prior task_id;
40627:
40624: WHERE project_id = p_project_id
40625: START WITH parent_task_id = l_ref_parent_task_id
40626: CONNECT BY parent_task_id = prior task_id;
40627:
40628: insert into pa_tasks_tmp (task_id)
40629: select proj_element_id
40630: from pa_proj_element_versions
40631: where ELEMENT_VERSION_ID = p_task_version_id and project_id = p_project_id and not exists (select null from pa_tasks_tmp where task_id = proj_element_id);
40632:
40627:
40628: insert into pa_tasks_tmp (task_id)
40629: select proj_element_id
40630: from pa_proj_element_versions
40631: where ELEMENT_VERSION_ID = p_task_version_id and project_id = p_project_id and not exists (select null from pa_tasks_tmp where task_id = proj_element_id);
40632:
40633: SELECT /*+ ordered index(ev1 pa_proj_element_versions_n1) */
40634: element_version_id, proj_element_id, object_type,
40635: project_id, parent_structure_version_id,
40640: l_project_id_tab, l_parent_str_version_id_tab,
40641: l_display_sequence_tab, l_wbs_level_tab,
40642: l_wbs_number_tab, l_record_version_tab,
40643: l_changed_flag_tab
40644: FROM pa_tasks_tmp t1 ,
40645: pa_proj_element_versions ev1
40646: WHERE
40647: project_id = p_project_id
40648: AND parent_structure_version_id = p_structure_version_id
40645: pa_proj_element_versions ev1
40646: WHERE
40647: project_id = p_project_id
40648: AND parent_structure_version_id = p_structure_version_id
40649: AND object_type = 'PA_TASKS'
40650: AND proj_element_id = t1.task_id
40651: and display_sequence >= src_seq_number
40652: ORDER BY display_sequence ;
40653:
40785: -- Sridhar Huawei 18-Sept add above end if
40786:
40787: -- Update Relationship
40788:
40789: IF ref_object_type = 'PA_TASKS'
40790: THEN
40791: IF l_peer_or_sub = 'PEER'
40792: THEN
40793: OPEN cur_obj_rel( ref_elem_ver_id );
40811: l_relationship_subtype := 'TASK_TO_TASK';
40812:
40813:
40814: END IF;
40815: END IF; -- ref_object_type = 'PA_TASKS'
40816: -- No need to check for PA_STRUCTURES as this is indent and you can not indent below a structure
40817:
40818:
40819: --update relatonship of the task version p_task_version.
40970: l_project_id);
40971: l_shared := PA_PROJECT_STRUCTURE_UTILS.Check_Sharing_Enabled(
40972: l_project_id);
40973:
40974: l_fin_task_flag := PA_Proj_Elements_Utils.CHECK_IS_FINANCIAL_TASK(src_proj_element_id); --indent in corresponding task in pa_tasks
40975:
40976: IF (NVL( l_published_version, 'N' ) = 'N' AND l_fin_task_flag = 'Y' ) OR
40977: (l_published_version = 'Y' AND l_versioned = 'N' AND l_shared = 'Y' AND l_fin_task_flag = 'Y' )
40978: OR ((l_published_version = 'Y') AND (l_shared = 'N') AND (l_fin_task_flag = 'Y')) -- Bug # 5064340. -- To accomodate split financial tasks.
40979: THEN
40980:
40981: SELECT ppev.proj_element_id, pt.record_version_number, ppa.wbs_record_version_number, ppev.project_id
40982: INTO l_task_id, l_task_record_version_number, l_wbs_record_version_number, l_project_id
40983: FROM PA_TASKS pt,
40984: pa_proj_elem_ver_structure ppa,
40985: PA_PROJ_ELEMENT_VERSIONS ppev
40986: WHERE ppev.element_version_id = p_task_version_id
40987: AND ppev.parent_structure_version_id = ppa.element_version_id
40991: SELECT record_version_number, parent_task_id --get old parent id for bug 2947492 (indent )
40992: ,top_task_id --bug 2967204
40993: INTO l_task_record_version_number, l_old_parent_task_id
40994: ,l_old_top_task_id --bug 2967204
40995: FROM pa_tasks
40996: WHERE task_id = l_task_id
40997: AND project_id = l_project_id;
40998:
40999:
40996: WHERE task_id = l_task_id
40997: AND project_id = l_project_id;
40998:
40999:
41000: PA_TASKS_MAINT_PUB.Edit_Task_Structure(
41001: p_project_id => l_project_id
41002: ,p_task_id => l_task_id
41003: ,p_edit_mode => 'INDENT'
41004: ,p_record_version_number => l_task_record_version_number
41378: CURSOR get_parent_version_id(c_elem_ver_id NUMBER) IS
41379: SELECT object_id_from1
41380: FROM pa_object_relationships
41381: WHERE object_id_to1 = c_elem_ver_id
41382: AND object_type_to = 'PA_TASKS'
41383: AND relationship_type = 'S';
41384:
41385: CURSOR cur_obj_rel( p_child_version_id NUMBER ) IS
41386: SELECT object_id_from1
41397: SELECT a.object_id_from1
41398: , a.weighting_percentage
41399: FROM pa_object_relationships a
41400: WHERE a.object_id_to1 = c_task_version_id
41401: AND a.object_type_to = 'PA_TASKS'
41402: AND a.relationship_type = 'S'
41403: AND a.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS');
41404:
41405: CURSOR get_sub_tasks(c_task_version_id NUMBER) IS
41399: FROM pa_object_relationships a
41400: WHERE a.object_id_to1 = c_task_version_id
41401: AND a.object_type_to = 'PA_TASKS'
41402: AND a.relationship_type = 'S'
41403: AND a.object_type_from IN ('PA_STRUCTURES', 'PA_TASKS');
41404:
41405: CURSOR get_sub_tasks(c_task_version_id NUMBER) IS
41406: SELECT '1'
41407: FROM dual
41408: WHERE EXISTS
41409: (SELECT 'xyz'
41410: FROM pa_object_relationships
41411: WHERE object_id_from1 = c_task_version_id
41412: AND object_type_from IN ('PA_TASKS', 'PA_STRUCTURES')
41413: AND relationship_type = 'S'
41414: );
41415:
41416: CURSOR check_progress_allowed(c_element_version_id NUMBER) IS
41426: SELECT b.object_id_to1 object_id_to1
41427: FROM pa_object_relationships a
41428: , pa_object_relationships b
41429: WHERE a.object_id_to1 = p_task_version_id
41430: AND a.object_type_to = 'PA_TASKS'
41431: AND a.object_id_from1 = b.object_id_from1
41432: AND a.object_type_from = b.object_type_from
41433: AND b.object_type_to = 'PA_TASKS'
41434: AND b.object_id_to1 <> p_task_version_id
41429: WHERE a.object_id_to1 = p_task_version_id
41430: AND a.object_type_to = 'PA_TASKS'
41431: AND a.object_id_from1 = b.object_id_from1
41432: AND a.object_type_from = b.object_type_from
41433: AND b.object_type_to = 'PA_TASKS'
41434: AND b.object_id_to1 <> p_task_version_id
41435: AND a.relationship_type = 'S'
41436: AND b.relationship_type = 'S';
41437:
41543: CURSOR get_wbs_number(c_elem_ver_id NUMBER) is
41544: select wbs_number
41545: from pa_proj_element_versions
41546: where element_version_id = c_elem_ver_id
41547: and object_type ='PA_TASKS';
41548:
41549: ref_parent_branch_mask VARCHAR2(1000);
41550:
41551: -- hyau Bug 2852753
41596:
41597: CURSOR get_parent_task_id( c_task_id NUMBER, c_project_id NUMBER )
41598: IS
41599: SELECT parent_task_id, top_task_id
41600: FROM pa_tasks
41601: WHERE project_id = c_project_id
41602: AND task_id = c_task_id;
41603:
41604: CURSOR cur_new_child_task( c_project_id NUMBER, c_wbs_level NUMBER,
41604: CURSOR cur_new_child_task( c_project_id NUMBER, c_wbs_level NUMBER,
41605: c_task_id NUMBER , c_parent_task_id NUMBER )
41606: IS
41607: SELECT pt.task_id, pt.top_task_id, pt.parent_task_id
41608: FROM pa_tasks pt, pa_proj_element_versions ppev
41609: WHERE pt.wbs_level = c_wbs_level
41610: AND parent_task_id = c_parent_task_id
41611: AND pt.project_id = c_project_id
41612: AND pt.task_id = ppev.proj_element_id
41611: AND pt.project_id = c_project_id
41612: AND pt.task_id = ppev.proj_element_id
41613: AND ppev.display_sequence > ( SELECT display_sequence FROM pa_proj_element_versions
41614: WHERE project_id = c_project_id
41615: AND object_type = 'PA_TASKS'
41616: AND proj_element_id = c_task_id );
41617:
41618: --For financial tasks we can directly join with pa_proj-element_version using proj_element_id
41619: --bcoz this api will be called as long as there is only one version for financial otherwise
41632: Cursor get_top_task_ver_id(c_task_ver_id NUMBER) IS
41633: select object_id_to1
41634: from pa_object_relationships
41635: where relationshiP_type = 'S'
41636: and object_type_to = 'PA_TASKS'
41637: start with object_id_to1 = c_task_ver_id
41638: and object_type_to = 'PA_TASKS'
41639: and relationship_type = 'S'
41640: connect by prior object_id_from1 = object_id_to1
41634: from pa_object_relationships
41635: where relationshiP_type = 'S'
41636: and object_type_to = 'PA_TASKS'
41637: start with object_id_to1 = c_task_ver_id
41638: and object_type_to = 'PA_TASKS'
41639: and relationship_type = 'S'
41640: connect by prior object_id_from1 = object_id_to1
41641: and prior object_type_from = object_type_to
41642: and prior relationship_type = relationship_type
41823: FROM PA_PROJ_ELEMENT_VERSIONS
41824: WHERE parent_structure_version_id = p_structure_version_id
41825: AND project_id = p_project_id
41826: AND (wbs_level = src_wbs_level-1)
41827: AND object_type = 'PA_TASKS'
41828: AND display_sequence =
41829: (SELECT max (display_sequence)
41830: FROM pa_proj_element_versions
41831: WHERE project_id = p_project_id
41831: WHERE project_id = p_project_id
41832: AND parent_structure_version_id = p_structure_version_id
41833: AND (wbs_level = src_wbs_level-1)
41834: AND display_sequence < src_seq_number
41835: AND object_type = 'PA_TASKS');
41836: EXCEPTION
41837: WHEN OTHERS THEN
41838: raise;
41839: -- It will never return NO_DATA_FOUND as there will always at least one task with wbs_level-1
41923: If PA_PROJECT_STRUCTURE_UTILS.get_Structure_sharing_code(p_project_id)
41924: IN ('SHARE_PARTIAL')
41925: Then
41926:
41927: PA_TASKS_MAINT_UTILS.CHECK_MOVE_FINANCIAL_TASK_OK (
41928: p_task_version_id => p_task_version_id
41929: , p_ref_task_version_id => ref_elem_ver_id
41930: , x_return_status => x_return_status
41931: , x_msg_count => x_msg_count
41980: pa_proj_element_versions
41981: WHERE
41982: project_id = p_project_id
41983: AND parent_structure_version_id = p_structure_version_id
41984: AND object_type = 'PA_TASKS'
41985: AND(
41986: element_version_id = p_task_version_id -- Source task itself
41987: OR element_version_id IN -- All tasks below the source task
41988: (select object_id_to1
41987: OR element_version_id IN -- All tasks below the source task
41988: (select object_id_to1
41989: from pa_object_relationships
41990: where relationship_type = 'S'
41991: and object_type_to = 'PA_TASKS'
41992: start with object_id_from1 = l_ref_parent_ver_id
41993: connect by object_id_from1 = PRIOR object_id_to1
41994: and relationship_type = prior relationship_type
41995: and relationship_type = 'S')
41997: ORDER BY display_sequence ;
41998: */
41999: select proj_element_id INTO l_ref_parent_task_id from pa_proj_element_versions where element_version_id = l_ref_parent_ver_id and project_id= p_project_id;
42000:
42001: insert into pa_tasks_tmp (task_id)
42002: SELECT task_id
42003: FROM pa_tasks
42004: WHERE project_id = p_project_id
42005: START WITH parent_task_id = l_ref_parent_task_id
41999: select proj_element_id INTO l_ref_parent_task_id from pa_proj_element_versions where element_version_id = l_ref_parent_ver_id and project_id= p_project_id;
42000:
42001: insert into pa_tasks_tmp (task_id)
42002: SELECT task_id
42003: FROM pa_tasks
42004: WHERE project_id = p_project_id
42005: START WITH parent_task_id = l_ref_parent_task_id
42006: CONNECT BY parent_task_id = prior task_id;
42007:
42004: WHERE project_id = p_project_id
42005: START WITH parent_task_id = l_ref_parent_task_id
42006: CONNECT BY parent_task_id = prior task_id;
42007:
42008: insert into pa_tasks_tmp (task_id)
42009: select proj_element_id
42010: from pa_proj_element_versions
42011: where ELEMENT_VERSION_ID = p_task_version_id and project_id = p_project_id and not exists (select null from pa_tasks_tmp where task_id = proj_element_id);
42012:
42007:
42008: insert into pa_tasks_tmp (task_id)
42009: select proj_element_id
42010: from pa_proj_element_versions
42011: where ELEMENT_VERSION_ID = p_task_version_id and project_id = p_project_id and not exists (select null from pa_tasks_tmp where task_id = proj_element_id);
42012:
42013: SELECT /*+ ordered index(ev1 pa_proj_element_versions_n1) */
42014: element_version_id, proj_element_id, object_type,
42015: project_id, parent_structure_version_id,
42020: l_project_id_tab, l_parent_str_version_id_tab,
42021: l_display_sequence_tab, l_wbs_level_tab,
42022: l_wbs_number_tab, l_record_version_tab,
42023: l_changed_flag_tab
42024: FROM pa_tasks_tmp t1 ,
42025: pa_proj_element_versions ev1
42026: WHERE
42027: project_id = p_project_id
42028: AND parent_structure_version_id = p_structure_version_id
42025: pa_proj_element_versions ev1
42026: WHERE
42027: project_id = p_project_id
42028: AND parent_structure_version_id = p_structure_version_id
42029: AND object_type = 'PA_TASKS'
42030: AND proj_element_id = t1.task_id
42031: ORDER BY display_sequence ;
42032:
42033: l_old_wbs_level_tab := l_wbs_level_tab;
42226: -- Sridhar Huawei 18-Sept add above end if
42227:
42228: -- Update Relationship
42229:
42230: IF ref_object_type = 'PA_TASKS'
42231: THEN
42232: IF l_peer_or_sub = 'PEER'
42233: THEN
42234: OPEN cur_obj_rel( ref_elem_ver_id );
42262: THEN
42263: l_struc_version_from := ref_elem_ver_id;
42264: l_task_version_from := null;
42265: l_relationship_subtype := 'STRUCTURE_TO_TASK';
42266: END IF; -- ref_object_type = 'PA_TASKS'
42267:
42268:
42269: --update relatonship of the task version p_task_version.
42270: --set structure_version_from and task_version_from accordingly.
42487: l_project_id);
42488: l_shared := PA_PROJECT_STRUCTURE_UTILS.Check_Sharing_Enabled(
42489: l_project_id);
42490:
42491: l_fin_task_flag := PA_Proj_Elements_Utils.CHECK_IS_FINANCIAL_TASK(src_proj_element_id); --outdent in corresponding task in pa_tasks
42492:
42493: IF (NVL( l_published_version, 'N' ) = 'N' AND l_fin_task_flag = 'Y' ) OR
42494: (l_published_version = 'Y' AND l_versioned = 'N' AND l_shared = 'Y' AND l_fin_task_flag = 'Y' )
42495: OR ((l_published_version = 'Y') AND (l_shared = 'N') AND (l_fin_task_flag = 'Y')) -- Bug # 5064340. -- To accomodate split financial tasks.
42496: THEN
42497:
42498: SELECT ppev.proj_element_id, pt.record_version_number, ppa.wbs_record_version_number, ppev.project_id
42499: INTO l_task_id, l_task_record_version_number, l_wbs_record_version_number, l_project_id
42500: FROM PA_TASKS pt,
42501: pa_proj_elem_ver_structure ppa,
42502: PA_PROJ_ELEMENT_VERSIONS ppev
42503: WHERE ppev.element_version_id = p_task_version_id
42504: AND ppev.parent_structure_version_id = ppa.element_version_id
42508: SELECT record_version_number, parent_task_id, wbs_level --get old parent id for bug 2947492 (outdent )
42509: ,top_task_id
42510: INTO l_task_record_version_number, l_old_parent_task_id, l_old_wbs_level
42511: ,l_old_top_task_id
42512: FROM pa_tasks
42513: WHERE task_id = l_task_id
42514: AND project_id = l_project_id;
42515:
42516: PA_TASKS_MAINT_PUB.Edit_Task_Structure(
42512: FROM pa_tasks
42513: WHERE task_id = l_task_id
42514: AND project_id = l_project_id;
42515:
42516: PA_TASKS_MAINT_PUB.Edit_Task_Structure(
42517: p_project_id => l_project_id
42518: ,p_task_id => l_task_id
42519: ,p_edit_mode => 'OUTDENT'
42520: ,p_record_version_number => l_task_record_version_number
43312: and element_number=c_number;
43313:
43314: Cursor C3(c_proj_id Number , c_number varchar2) IS
43315: Select Task_id
43316: From pa_Tasks
43317: Where project_id=c_proj_id
43318: and task_number=c_number;
43319:
43320: Cursor check_task_exists_wp(c_proj_id Number ,c_task_id Number) IS
43324: and proj_element_id=c_task_id;
43325:
43326: Cursor check_task_exists_fp(c_proj_id Number ,c_task_id Number) IS
43327: Select Count(1)
43328: From pa_Tasks
43329: Where project_id=c_proj_id
43330: and task_id=c_task_id;
43331:
43332: Cursor Check_cost_code_valid(c_proj_id Number ,c_cbs_lem_id Number) IS
43610: and element_number=c_number;
43611:
43612: Cursor C3(c_proj_id Number , c_number varchar2) IS
43613: Select Task_id
43614: From pa_Tasks
43615: Where project_id=c_proj_id
43616: and task_number=c_number;
43617:
43618: Cursor check_task_exists_wp(c_proj_id Number ,c_task_id Number) IS
43622: and proj_element_id=c_task_id;
43623:
43624: Cursor check_task_exists_fp(c_proj_id Number ,c_task_id Number) IS
43625: Select Count(1)
43626: From pa_Tasks
43627: Where project_id=c_proj_id
43628: and task_id=c_task_id;
43629:
43630: Cursor Check_cost_code_valid(c_proj_id Number ,c_cbs_lem_id Number) IS