[Home] [Help]
62: and ppev2.proj_element_id = ppst.proj_element_id
63: and ppst.structure_type_id = pst.structure_type_id
64: and pst.structure_type_class_code = 'WORKPLAN'
65: and ppev1.proj_element_id = p_task_id
66: and ppev1.object_type = 'PA_TASKS';
67: return x_task_version_id;
68: exception when others then
69: return -999;
70: END;
337: and task_id = l_task_id
338: and published_flag = 'Y'
339: and structure_type = 'WORKPLAN'
340: and date_computed < l_as_of_date
341: and object_type = 'PA_TASKS'
342: order by date_computed desc;*/
343:
344: ---- bug 5042445
345: CURSOR c_get_prior_percent_complete(l_task_id NUMBER, l_project_id NUMBER, l_as_of_date DATE) IS
346: select nvl(completed_percentage,eff_rollup_percent_comp)
347: from pa_progress_rollup
348: where project_id = l_project_id
349: and object_id = l_task_id
350: and object_type in ('PA_STRUCTURES', 'PA_TASKS')
351: and structure_Type = 'WORKPLAN'
352: and structure_version_id is null
353: and as_of_date < l_as_of_date
354: order by as_of_date desc;
382: FUNCTION GET_LATEST_AS_OF_DATE(
383: p_task_id NUMBER
384: ,p_project_id NUMBER := null -- FPM Development Bug 3420093
385: ,p_object_id NUMBER := null -- FPM Development Bug 3420093
386: , p_object_type VARCHAR2 := 'PA_TASKS'-- FPM Development Bug 3420093
387: ,p_structure_type VARCHAR2 := 'WORKPLAN' -- FPM Development Bug 3420093
388: ,p_cbs_element_id NUMBER := null -- Added for CBS phase 2
389: ) RETURN DATE IS
390:
568: FUNCTION as_of_date(
569: X_Project_ID IN NUMBER ,
570: X_Object_id IN NUMBER ,
571: X_Billing_Cycle_ID IN NUMBER DEFAULT NULL ,
572: X_Object_type IN VARCHAR2 DEFAULT 'PA_TASKS' ,-- FPM Development Bug 3420093
573: X_structure_type IN VARCHAR2 DEFAULT 'WORKPLAN' ,-- FPM Development Bug 3420093
574: X_proj_element_id IN NUMBER := null /* Amit : Modified for IB4 Progress CR. */
575: ) RETURN DATE IS
576:
611: WHERE project_id = X_Project_ID
612: AND current_flag = 'Y'
613: AND published_flag = 'Y'
614: AND ((object_type = 'PA_ASSIGNMENTS' and object_id = x_object_id)
615: or (object_type = 'PA_TASKS' and object_id = X_proj_element_id))
616: AND structure_type = X_structure_type
617: AND task_id = X_proj_element_id
618: ;
619:
649: SELECT max( date_computed )
650: FROM pa_percent_completes
651: WHERE project_id = X_Project_ID
652: AND ((object_id = x_object_id and object_type = 'PA_DELIVERABLES') or
653: (object_id = c_task_id and object_type = 'PA_TASKS'))
654: AND current_flag = 'Y'
655: AND published_flag = 'Y'
656: AND structure_type = X_structure_type
657: ;
669: CURSOR c_get_max_rollup_dt IS
670: SELECT max(as_of_date)
671: FROM pa_progress_rollup
672: where project_id = X_Project_ID
673: and object_type IN ('PA_TASKS', 'PA_STRUCTURES')
674: and object_id = x_object_id
675: and structure_type = 'WORKPLAN'
676: and structure_version_id is null;
677:
694: -- 4535784 Begin
695: -- To Show 30 dates in case rollup records exists after 5 cycle dates
696: -- Note that j_task is checked against 6 because each run is for 10 in which we get 5 future date and 5 null date(previous date implementaion, obsoleted now)
697: -- So we need to get 60 rows for 30 dates
698: IF ((x_object_type IN ('PA_TASKS', 'PA_STRUCTURES') and X_structure_type = 'WORKPLAN') and (j_task >=6 OR g_task_id <> x_object_id or PA_PROGRESS_UTILS.project_id <> X_Project_ID)) THEN
699: j_task := 0;
700: g_task_id := x_object_id;
701: OPEN c_get_max_rollup_dt;
702: FETCH c_get_max_rollup_dt INTO g_max_rollup_dt;
703: CLOSE c_get_max_rollup_dt;
704: END IF;
705: -- 4535784 End
706: -- Bug 3974627 Begin
707: IF x_object_type IN ('PA_TASKS', 'PA_STRUCTURES') THEN
708: -- 4535784 Begin
709: j_task := j_task+1;
710: IF j_task > 1 AND X_structure_type = 'WORKPLAN' and g_task_id = x_object_id AND trunc(nvl(g_max_rollup_dt,sysdate)) > trunc(PA_PROGRESS_UTILS.x_bill_thru_date) THEN
711: PA_PROGRESS_UTILS.l_last_progress_date := PA_PROGRESS_UTILS.x_bill_thru_date;
798:
799: FUNCTION CHECK_VALID_AS_OF_DATE(p_as_of_date IN DATE
800: , p_project_id IN NUMBER
801: , p_object_id NUMBER
802: , p_object_type VARCHAR2 := 'PA_TASKS'
803: , p_cbs_element_id NUMBER := null -- Added for CBS phase 2 16200605
804: , p_proj_element_id IN NUMBER := null /* Amit : Modified for IB4 Progress CR. */)
805: RETURN VARCHAR2 IS
806:
846: AND ppp.structure_type (+) = 'WORKPLAN'
847: and ppe.project_id= p_project_id
848: and ppe.proj_element_id = p_object_id
849: and ppe.object_type = p_object_type
850: and ((ppe.object_type in ('PA_TASKS', 'PA_STRUCTURES') and rownum <61) or (ppe.object_type = 'PA_DELIVERABLES' and rownum <11)) -- Bug 4535784
851: --and rownum <11
852: ;
853:
854:
934: IS
935: SELECT nvl(ppe.WQ_ACTUAL_ENTRY_CODE,ptt.ACTUAL_WQ_ENTRY_CODE)
936: FROM pa_proj_elements ppe, pa_task_types ptt
937: WHERE ppe.type_id = ptt.task_type_id(+)
938: AND ptt.object_type = 'PA_TASKS' /* bug 3279978 FP M Enhancement */
939: AND ppe.proj_element_id = p_task_id ;
940:
941: --Bug fix 2679612
942:
1054: FUNCTION get_next_progress_cycle(
1055: p_project_id NUMBER,
1056: p_task_id NUMBER,
1057: p_object_id NUMBER := null, -- FPM Development Bug 3420093
1058: p_object_type VARCHAR2 := 'PA_TASKS', -- FPM Development Bug 3420093
1059: p_structure_type VARCHAR2 := 'WORKPLAN', -- FPM Development Bug 3420093
1060: p_start_date DATE := to_date(null) -- FPM Development Bug 3420093
1061: ,p_cbs_element_id NUMBER := null -- Added for CBS phase 2 16200605
1062: ) RETURN DATE IS
1097: FROM pa_percent_completes
1098: WHERE project_id = p_Project_ID
1099: AND current_flag = 'Y'
1100: AND published_flag = 'Y'
1101: AND object_type IN ('PA_ASSIGNMENTS' ,'PA_TASKS')
1102: AND structure_type = p_structure_type
1103: AND task_id = p_task_id
1104: ;
1105:
1122: WHERE project_id = p_Project_ID
1123: AND ((c_task_id IS NULL AND object_id =p_object_id) OR (c_task_id IS NOT NULL AND object_id IN (c_task_id, p_object_id)))
1124: AND current_flag = 'Y'
1125: AND published_flag = 'Y'
1126: AND object_type IN ('PA_DELIVERABLES' ,'PA_TASKS')
1127: AND structure_type = p_structure_type
1128: AND NVL(task_id, -1) = NVL(p_task_id, NVL(task_id, -1))
1129: ;
1130:
1173: --FETCH cur_pa_ppc INTO l_last_progress_date;
1174: --CLOSE cur_pa_ppc;
1175:
1176: -- Bug 3974627 Begin
1177: IF p_object_type IN ('PA_TASKS', 'PA_STRUCTURES') THEN
1178: OPEN cur_pa_ppc_str_task;
1179: FETCH cur_pa_ppc_str_task INTO l_last_progress_date;
1180: CLOSE cur_pa_ppc_str_task;
1181: ELSIF p_object_type = 'PA_ASSIGNMENTS' THEN
1240: FUNCTION get_prog_dt_closest_to_sys_dt(
1241: p_project_id NUMBER,
1242: p_task_id NUMBER, -- From deliverables it will be passed as null
1243: p_object_id NUMBER := null, -- FPM Development Bug 3420093
1244: p_object_type VARCHAR2 := 'PA_TASKS', -- FPM Development Bug 3420093
1245: p_structure_type VARCHAR2 := 'WORKPLAN'
1246: ,p_cbs_element_id NUMBER := null -- Added for CBS phase 2 16200605
1247: ) RETURN DATE IS
1248: l_last_progress_date DATE;
1304: WHERE project_id = p_Project_ID
1305: AND current_flag = 'Y'
1306: AND published_flag = 'Y'
1307: AND ((object_type = 'PA_ASSIGNMENTS' and object_id = p_object_id AND NVL(cbs_element_id,-1) = NVL(p_cbs_element_id,-1)) --Added for CBS phase 2 16200605
1308: or (object_type = 'PA_TASKS' and object_id = p_task_id))
1309: AND structure_type = p_structure_type
1310: AND task_id = p_task_id
1311: ;
1312:
1340: SELECT max( date_computed )
1341: FROM pa_percent_completes
1342: WHERE project_id = p_Project_ID
1343: AND ((object_id = p_object_id and object_type = 'PA_DELIVERABLES') or
1344: (object_id = p_task_id and object_type = 'PA_TASKS'))
1345: AND current_flag = 'Y'
1346: AND published_flag = 'Y'
1347: AND structure_type = p_structure_type
1348: ;
1388: OPEN cur_pa_ppc_w;
1389: FETCH cur_pa_ppc_w INTO l_last_progress_date;
1390: CLOSE cur_pa_ppc_w;
1391:
1392: IF p_object_type = 'PA_TASKS' THEN -- This is Done to avoid the impact
1393: l_valid_as_of_date := CHECK_VALID_AS_OF_DATE ( l_last_progress_date, p_project_id, p_task_id);
1394: ELSE
1395: l_valid_as_of_date := CHECK_VALID_AS_OF_DATE ( l_last_progress_date, p_project_id, p_object_id, p_object_type,p_task_id/* Amit : Modified for IB4 Progress CR. */,p_cbs_element_id );
1396: END IF;
1411: --FETCH cur_pa_ppc INTO l_last_progress_date;
1412: --CLOSE cur_pa_ppc;
1413:
1414: -- Bug 3974627 Begin
1415: IF p_object_type IN ('PA_TASKS', 'PA_STRUCTURES') THEN
1416: OPEN cur_pa_ppc_str_task;
1417: FETCH cur_pa_ppc_str_task INTO l_last_progress_date;
1418: CLOSE cur_pa_ppc_str_task;
1419: ELSIF p_object_type = 'PA_ASSIGNMENTS' THEN
1776: x_return_status := FND_API.G_RET_STS_SUCCESS;
1777:
1778: IF p_structure_version_id IS NULL
1779: THEN
1780: IF p_object_type = 'PA_TASKS'
1781: THEN
1782: l_proj_element_id := p_object_id;
1783: UPDATE pa_proj_elements
1784: SET progress_outdated_flag = 'N'
1800: WHERE proj_element_id in ( SELECT proj_element_id
1801: FROM pa_proj_element_versions
1802: WHERE project_id = p_project_id
1803: AND parent_structure_version_id = p_structure_version_id
1804: AND object_type in ( 'PA_STRUCTURES', 'PA_TASKS' ) )
1805: AND object_type in ( 'PA_STRUCTURES', 'PA_TASKS' )
1806: AND project_id = p_project_id
1807: ;
1808: END IF;
1801: FROM pa_proj_element_versions
1802: WHERE project_id = p_project_id
1803: AND parent_structure_version_id = p_structure_version_id
1804: AND object_type in ( 'PA_STRUCTURES', 'PA_TASKS' ) )
1805: AND object_type in ( 'PA_STRUCTURES', 'PA_TASKS' )
1806: AND project_id = p_project_id
1807: ;
1808: END IF;
1809:
1920: decode(ptt.REMAIN_EFFORT_ENABLE_FLAG, 'Y', decode(pppa.REMAIN_EFFORT_ENABLE_FLAG, 'Y', 'Y', 'N'), 'N')),
1921: NVL( ppe.base_percent_comp_deriv_code, ptt.base_percent_comp_deriv_code),
1922: decode(ppe.object_type,'PA_STRUCTURES',pppa.PERCENT_COMP_ENABLE_FLAG,
1923: decode(ptt.PERCENT_COMP_ENABLE_FLAG, 'Y', decode(pppa.PERCENT_COMP_ENABLE_FLAG, 'Y', 'Y','N'), 'N')),
1924: decode(ppe.object_type,'PA_TASKS',ptt.PROG_ENTRY_ENABLE_FLAG,'Y'),
1925: pppa.ALLOW_COLLAB_PROG_ENTRY,
1926: pppa.ALLOW_PHY_PRCNT_CMP_OVERRIDES,
1927: pppa.TASK_WEIGHT_BASIS_CODE
1928: FROM pa_proj_elements ppe, pa_task_types ptt, pa_proj_progress_attr pppa, pa_proj_elem_ver_structure ppvs, pa_proj_structure_types ppst
1935: AND ppe.project_id = ppvs.project_id
1936: AND ppvs.latest_eff_published_flag = 'Y'
1937: AND ppvs.proj_element_id = pppa.object_id(+)
1938: AND ppvs.proj_element_id = ppst.proj_element_id
1939: AND ptt.object_type(+) = 'PA_TASKS' /* bug 3279978 FP M Enhancement */ --bug 4330450 added outer join
1940: AND ppst.structure_type_id =1;
1941:
1942: BEGIN
1943: OPEN cur_prg_deflts;
2153: is
2154: select 'X' from pa_task_types
2155: where ( initial_status_code = p_status_code
2156: or initial_progress_status_code = p_status_code)
2157: AND object_type = 'PA_TASKS' /* bug 3279978 FP M Enhancement */
2158: AND rownum <= 1;
2159:
2160: l_return_value boolean := FALSE;
2161: l_dummy varchar2(1);
2262: WHERE object_id_to1 = p_object_version_id
2263: AND ppev.element_version_id = por.object_id_from1
2264: AND ppev.proj_element_id = ppe.proj_element_id
2265: AND ppev.object_type = ppe.object_type
2266: AND ppe.object_type = 'PA_TASKS'
2267: AND ppe.status_code = pps.project_status_code
2268: AND pps.project_system_status_code = 'ON_HOLD';
2269:
2270: l_status_code VARCHAR2(150);
2283:
2284: FUNCTION get_task_status(
2285: p_project_id NUMBER
2286: ,p_object_id NUMBER
2287: , p_object_type VARCHAR2 := 'PA_TASKS' -- FPM Development Bug 3420093
2288: ) RETURN VARCHAR2 IS
2289:
2290: CURSOR cur_pa_proj_elems
2291: IS
2305:
2306:
2307: FUNCTION get_system_task_status(
2308: p_status_code VARCHAR2
2309: ,p_object_type VARCHAR2 := 'PA_TASKS' -- FPM Development Bug 3420093
2310: ) RETURN VARCHAR2 IS
2311:
2312: CURSOR cur_sys_status
2313: IS
2313: IS
2314: SELECT project_system_status_code
2315: FROM pa_project_statuses pps -----, fnd_lookup_values flv
2316: WHERE project_status_code = p_status_code
2317: AND status_type = decode(p_object_type, 'PA_TASKS', 'TASK', 'PA_DELIVERABLES', 'DELIVERABLE');
2318: /* bug 4699567 flv was not used in the query anyways
2319: AND lookup_type = decode(p_object_type, 'PA_TASKS', 'TASK_SYSTEM_STATUS', 'PA_DELIVERABLES', 'DELIVERABLE_SYSTEM_STATUS')
2320: AND language = 'US'; */
2321:
2315: FROM pa_project_statuses pps -----, fnd_lookup_values flv
2316: WHERE project_status_code = p_status_code
2317: AND status_type = decode(p_object_type, 'PA_TASKS', 'TASK', 'PA_DELIVERABLES', 'DELIVERABLE');
2318: /* bug 4699567 flv was not used in the query anyways
2319: AND lookup_type = decode(p_object_type, 'PA_TASKS', 'TASK_SYSTEM_STATUS', 'PA_DELIVERABLES', 'DELIVERABLE_SYSTEM_STATUS')
2320: AND language = 'US'; */
2321:
2322: l_status_code VARCHAR2(150);
2323: BEGIN
2383: ) return date is
2384: l_rollup_date date;
2385: begin
2386:
2387: /* -- FPM Dev CR 4 : Added condition for PA_TASKS and structure_version_id
2388: if (((p_object_type = 'PA_TASKS') AND (nvl(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(p_task_version_id => p_object_version_id ),'N') = 'N')) OR p_structure_version_id is not null) then
2389:
2390: select max(as_of_date)
2391: into l_rollup_date
2384: l_rollup_date date;
2385: begin
2386:
2387: /* -- FPM Dev CR 4 : Added condition for PA_TASKS and structure_version_id
2388: if (((p_object_type = 'PA_TASKS') AND (nvl(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(p_task_version_id => p_object_version_id ),'N') = 'N')) OR p_structure_version_id is not null) then
2389:
2390: select max(as_of_date)
2391: into l_rollup_date
2392: from pa_progress_rollup
2563: IS
2564: SELECT rel.object_id_from1
2565: FROM pa_object_relationships rel
2566: WHERE rel.object_id_to1 = c_element_version_id
2567: AND rel.object_type_to = 'PA_TASKS'
2568: AND rel.relationship_type = 'S'
2569: AND rel.object_type_from = 'PA_TASKS';
2570:
2571: CURSOR l_get_element_version_id(c_proj_element_id NUMBER, c_parent_structure_version_id NUMBER)
2565: FROM pa_object_relationships rel
2566: WHERE rel.object_id_to1 = c_element_version_id
2567: AND rel.object_type_to = 'PA_TASKS'
2568: AND rel.relationship_type = 'S'
2569: AND rel.object_type_from = 'PA_TASKS';
2570:
2571: CURSOR l_get_element_version_id(c_proj_element_id NUMBER, c_parent_structure_version_id NUMBER)
2572: IS
2573: SELECT ppev.element_version_id
2667: l_system_status_code VARCHAR2(30);
2668: BEGIN
2669: if (p_object_type = 'PA_STRUCTURES' or p_object_type = 'PA_ASSIGNMENTS') then
2670: l_return_value := 'Y';
2671: elsif (p_object_type = 'PA_TASKS') then
2672:
2673: select status_code
2674: into l_status_code
2675: from pa_proj_elements
2810: FROM pa_proj_elements ppe,
2811: pa_object_relationships por,
2812: pa_task_types ttype
2813: WHERE
2814: ppe.object_type = 'PA_TASKS'
2815: and ppe.proj_element_id = por.object_id_from2
2816: and por.object_type_from = 'PA_TASKS'
2817: and por.object_type_to = 'PA_DELIVERABLES'
2818: and por.relationship_type = 'A'
2812: pa_task_types ttype
2813: WHERE
2814: ppe.object_type = 'PA_TASKS'
2815: and ppe.proj_element_id = por.object_id_from2
2816: and por.object_type_from = 'PA_TASKS'
2817: and por.object_type_to = 'PA_DELIVERABLES'
2818: and por.relationship_type = 'A'
2819: and por.relationship_subtype = 'TASK_TO_DELIVERABLE'
2820: and nvl(ppe.base_percent_comp_deriv_code,ttype.base_percent_comp_deriv_code)='DELIVERABLE'
5772:
5773: FUNCTION Get_EARLY_PROGRESS_ENTRY_DATE(
5774: p_project_id NUMBER
5775: ,p_object_id NUMBER
5776: ,p_object_type VARCHAR2 := 'PA_TASKS'
5777: ,p_structure_type VARCHAR2 := 'WORKPLAN'
5778: ,p_task_id NUMBER := null /* Modified for IB4 Progress CR. */
5779: ) RETURN DATE
5780: IS
5806:
5807: FUNCTION Get_LATEST_PROGRESS_ENTRY_DATE(
5808: p_project_id NUMBER
5809: ,p_object_id NUMBER
5810: ,p_object_type VARCHAR2 := 'PA_TASKS'
5811: ,p_structure_type VARCHAR2 := 'WORKPLAN'
5812: ,p_task_id NUMBER := null /* Amit : Modified for IB4 Progress CR. */
5813: ,p_cbs_element_id NUMBER := null -- Added for CBS phase 2 16200605
5814: ) RETURN DATE
5861: FUNCTION check_object_has_prog(
5862: p_project_id IN NUMBER -- FPM Dev CR 7 : Removed defaulting
5863: ,p_proj_element_id IN NUMBER := null /* Modified for IB4 Progress CR. */
5864: ,p_object_id IN NUMBER -- FPM Dev CR 7 : Removed defaulting
5865: ,p_object_type IN VARCHAR2:='PA_TASKS'
5866: ,p_structure_type IN VARCHAR2:='WORKPLAN'
5867: ,p_progress_status IN VARCHAR2:='ANY'
5868: ) RETURN VARCHAR2
5869: IS
5971: Procedure REDEFAULT_BASE_PC (
5972: p_Project_ID IN NUMBER
5973: ,p_Proj_element_id IN NUMBER
5974: ,p_Structure_type IN VARCHAR2 DEFAULT 'WORKPLAN'
5975: ,p_object_type IN VARCHAR2 DEFAULT 'PA_TASKS'
5976: ,p_As_Of_Date IN DATE
5977: ,p_structure_version_id IN NUMBER DEFAULT null
5978: ,p_structure_status IN VARCHAR2 DEFAULT null
5979: ,p_calling_context IN VARCHAR2 DEFAULT 'PROGRESS'
6442: CLOSE cur_baselined_dates;
6443:
6444: -- Calculate schedule at completion.
6445:
6446: x_Sch_At_Completion := pa_progress_utils.return_start_end_date(l_sch_start_date,l_base_start_date,p_project_id,p_proj_element_id,'PA_TASKS','S')
6447: +((pa_progress_utils.return_start_end_date(l_sch_finish_date,l_base_finish_date,p_project_id,p_proj_element_id,'PA_TASKS','E')
6448: -pa_progress_utils.return_start_end_date(l_sch_start_date,l_base_start_date,p_project_id,p_proj_element_id,'PA_TASKS','S'))
6449: /l_SCH_Performance_Index);
6450:
6443:
6444: -- Calculate schedule at completion.
6445:
6446: x_Sch_At_Completion := pa_progress_utils.return_start_end_date(l_sch_start_date,l_base_start_date,p_project_id,p_proj_element_id,'PA_TASKS','S')
6447: +((pa_progress_utils.return_start_end_date(l_sch_finish_date,l_base_finish_date,p_project_id,p_proj_element_id,'PA_TASKS','E')
6448: -pa_progress_utils.return_start_end_date(l_sch_start_date,l_base_start_date,p_project_id,p_proj_element_id,'PA_TASKS','S'))
6449: /l_SCH_Performance_Index);
6450:
6451: end if;
6444: -- Calculate schedule at completion.
6445:
6446: x_Sch_At_Completion := pa_progress_utils.return_start_end_date(l_sch_start_date,l_base_start_date,p_project_id,p_proj_element_id,'PA_TASKS','S')
6447: +((pa_progress_utils.return_start_end_date(l_sch_finish_date,l_base_finish_date,p_project_id,p_proj_element_id,'PA_TASKS','E')
6448: -pa_progress_utils.return_start_end_date(l_sch_start_date,l_base_start_date,p_project_id,p_proj_element_id,'PA_TASKS','S'))
6449: /l_SCH_Performance_Index);
6450:
6451: end if;
6452:
6937: procedure set_prog_as_of_Date(p_project_id IN NUMBER,
6938: p_task_id IN NUMBER,
6939: p_as_of_date IN DATE default to_date(null),
6940: p_object_id IN NUMBER := null, -- Bug 3974627
6941: p_object_type IN VARCHAR2 := 'PA_TASKS' -- Bug 3974627
6942: ) IS
6943: BEGIN
6944: if p_as_of_date is null then
6945: IF p_object_type = 'PA_TASKS' THEN
6941: p_object_type IN VARCHAR2 := 'PA_TASKS' -- Bug 3974627
6942: ) IS
6943: BEGIN
6944: if p_as_of_date is null then
6945: IF p_object_type = 'PA_TASKS' THEN
6946: G_prog_as_of_date := get_prog_dt_closest_to_sys_dt(p_project_id => p_project_id,
6947: p_task_id => p_task_id);
6948: ELSE -- Bug 3974627 : Added ELSE and call of get_prog_dt_closest_to_sys_dt
6949: G_prog_as_of_date := get_prog_dt_closest_to_sys_dt(p_project_id => p_project_id,
6988:
6989: function check_workplan_cost ( p_project_id IN NUMBER,
6990: p_task_id IN NUMBER := NULL,
6991: p_object_id IN NUMBER := NULL,
6992: p_object_type IN VARCHAR2 := 'PA_TASKS',
6993: p_structure_version_id IN NUMBER := NULL
6994: ) RETURN VARCHAR2 IS
6995:
6996: l_workplan_cost VARCHAR2(1) := 'Y';
7046: THEN
7047: return l_labor_cost_flag;
7048: END IF;
7049:
7050: IF p_object_type = 'PA_TASKS'
7051: THEN
7052: OPEN c_assgmt_count;
7053: FETCH c_assgmt_count INTO l_tot_assgmts;
7054: CLOSE c_assgmt_count;
7412: cursor c2(p_fin_task_ver_id NUMBER) is
7413: select object_id_from1
7414: from pa_object_relationships
7415: where relationship_type='M'
7416: and object_type_from='PA_TASKS'
7417: and object_type_to='PA_TASKS'
7418: and object_id_to1 = p_fin_task_ver_id;
7419:
7420: cursor c3(p_project_id NUMBER) is
7413: select object_id_from1
7414: from pa_object_relationships
7415: where relationship_type='M'
7416: and object_type_from='PA_TASKS'
7417: and object_type_to='PA_TASKS'
7418: and object_id_to1 = p_fin_task_ver_id;
7419:
7420: cursor c3(p_project_id NUMBER) is
7421: select structure_sharing_code
8425: l_ATTRIBUTE29_tbl SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
8426: l_ATTRIBUTE30_tbl SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
8427: --End of variables for Variable for Resource Attributes
8428:
8429: l_task_id pa_tasks.task_id%TYPE := P_task_id; --bug 3860575, Satish
8430: l_top_task_id pa_tasks.task_id%TYPE;
8431: l_bill_job_group_id pa_projects_all.bill_job_group_id%TYPE;
8432: l_project_type pa_projects_all.project_type%TYPE;
8433: l_expenditure_type pa_resource_assignments.expenditure_type%TYPE;
8426: l_ATTRIBUTE30_tbl SYSTEM.PA_VARCHAR2_150_TBL_TYPE := SYSTEM.PA_VARCHAR2_150_TBL_TYPE();
8427: --End of variables for Variable for Resource Attributes
8428:
8429: l_task_id pa_tasks.task_id%TYPE := P_task_id; --bug 3860575, Satish
8430: l_top_task_id pa_tasks.task_id%TYPE;
8431: l_bill_job_group_id pa_projects_all.bill_job_group_id%TYPE;
8432: l_project_type pa_projects_all.project_type%TYPE;
8433: l_expenditure_type pa_resource_assignments.expenditure_type%TYPE;
8434: l_org_id pa_projects_all.org_id%TYPE;
8980: -- 3686920 : using l_use_planning_rates_flag to call the API GET_PLAN_VERSION_DTLS
8981: -- also when l_use_planning_rates_flag is N then we should be using ACTUAL RATES so passing ACTUAL_RATES
8982: -- to costing API
8983: -- We should not be calling Get_Default_Sch_Ids as it will not retun actual values
8984: -- If at all we need this, then we should get this value from pa_tasks and then from pa_projects
8985: --bug 3733606 moved from below
8986:
8987: BEGIN
8988: l_plan_version_id := Pa_Fp_wp_gen_amt_utils.get_wp_version_id(
10036: p_scheduled_date DATE := NULL
10037: ,p_baselined_date DATE := NULL
10038: ,p_project_id NUMBER
10039: ,p_proj_element_id NUMBER
10040: ,p_object_type VARCHAR2 := 'PA_TASKS'
10041: ,p_start_end_flag VARCHAR2 := 'S'
10042: ) return date
10043:
10044: is
11078:
11079: FUNCTION check_prog_exists_and_delete(
11080: p_project_id NUMBER
11081: ,p_task_id NUMBER
11082: ,p_object_type VARCHAR2 := 'PA_TASKS'
11083: ,p_object_id NUMBER := null
11084: ,p_structure_type VARCHAR2 := 'WORKPLAN'
11085: ,p_delete_progress_flag VARCHAR2 := 'Y' -- Fix for Bug # 4140984.
11086: ,p_cbs_element_id NUMBER := null -- Added for CBS phase 2 16598322
11214: end if;
11215:
11216: -- 3). If progress check and deletion is requested for all the assignments of a task.
11217:
11218: elsif (p_object_type = 'PA_TASKS') then
11219:
11220: -- 3.1). If published progress exists for any of the assignments for the task then
11221: -- return 'Y'.
11222: -- 4469270 : Removed the check for ppc records
11643: ,p_proj_element_id NUMBER
11644: ,p_structure_version_id NUMBER
11645: ,p_include_sub_tasks_flag VARCHAR2 := 'Y'
11646: ,p_structure_type VARCHAR2 := 'WORKPLAN'
11647: ,p_object_type VARCHAR2 := 'PA_TASKS'
11648: ,p_as_of_date DATE := null
11649: ,p_program_flag VARCHAR2 := 'Y' -- 4392189 : Program Reporting Changes - Phase 2
11650: )
11651: RETURN NUMBER
12452: END IF;
12453:
12454: IF l_need_fetch = 'Y' THEN
12455:
12456: IF PA_PROGRESS_UTILS.check_assignment_exists(p_project_id, p_object_version_id, 'PA_TASKS') = 'Y' THEN
12457: l_actual_effort := null;
12458: l_actual_cost := null;
12459: l_etc_effort := null;
12460: l_etc_cost := null;
12990: and ppev1.element_version_id=ppevs.element_version_id
12991: and ppev1.element_version_id = por.object_id_to1
12992: and ppev2.element_version_id = por.object_id_from1
12993: and por.relationship_type = 'S'
12994: and por.object_type_to = 'PA_TASKS'
12995: and ppe.object_type = 'PA_TASKS'
12996: order by ppev1.wbs_level desc;
12997:
12998: cursor c1(p_structure_version_id number) is
12991: and ppev1.element_version_id = por.object_id_to1
12992: and ppev2.element_version_id = por.object_id_from1
12993: and por.relationship_type = 'S'
12994: and por.object_type_to = 'PA_TASKS'
12995: and ppe.object_type = 'PA_TASKS'
12996: order by ppev1.wbs_level desc;
12997:
12998: cursor c1(p_structure_version_id number) is
12999: select 'Y'
13289: -- Cursor is based on the VO - ProgressAsOfDatesVO which is used to populate the 'as of date' poplist
13290: FUNCTION get_def_as_of_date_prog_report(
13291: p_project_id pa_progress_rollup.project_id%TYPE,
13292: p_proj_element_id pa_progress_rollup.proj_element_id%TYPE,
13293: p_object_type pa_progress_rollup.object_type%TYPE := 'PA_TASKS'
13294: ) RETURN DATE IS
13295: l_return_date DATE;
13296:
13297: CURSOR cur_get_def_as_of_date
13307: AND ppp.structure_type (+) = 'WORKPLAN'
13308: AND ppe.project_id = p_project_id
13309: AND ppe.proj_element_id = p_proj_element_id
13310: AND ppe.object_type = p_object_type
13311: AND ((ppe.object_type IN ('PA_TASKS', 'PA_STRUCTURES')
13312: AND rownum <61)
13313: OR (ppe.object_type = 'PA_DELIVERABLES'
13314: AND rownum <11))
13315: MINUS
13346: /** bpottipa added this method for bug 10137611.
13347: * This will be used in Team Member Home page > Update Progress >
13348: * Assignments tab view to get the actual effort this period
13349: * value. The logic in this method is similar to the one used in
13350: * pa_tasks_assigns_progress_v to derive actual effort to this period.
13351: */
13352: FUNCTION get_assgn_act_effort_period (p_as_of_date IN DATE
13353: ,p_project_id IN NUMBER
13354: ,p_object_id IN NUMBER