[Home] [Help]
107: l_return_status VARCHAR2(2);
108: l_error_message_code VARCHAR2(250);
109:
110: l_rowid VARCHAR2(255);
111: l_proj_element_id PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE;
112: l_structure_type_id PA_STRUCTURE_TYPES.STRUCTURE_TYPE_ID%TYPE;
113: l_proj_structure_type_id PA_PROJ_STRUCTURE_TYPES.PROJ_STRUCTURE_TYPE_ID%TYPE;
114: l_structure_type PA_STRUCTURE_TYPES.STRUCTURE_TYPE_CLASS_CODE%TYPE;
115:
331: END IF;
332: RAISE FND_API.G_EXC_ERROR;
333: END IF;
334:
335: --Insert into pa_proj_elements
336: select PA_TASKS_S.NEXTVAL into l_proj_element_id from sys.dual;
337:
338: PA_PROJ_ELEMENTS_PKG.insert_row(
339: X_ROW_ID => l_rowid
334:
335: --Insert into pa_proj_elements
336: select PA_TASKS_S.NEXTVAL into l_proj_element_id from sys.dual;
337:
338: PA_PROJ_ELEMENTS_PKG.insert_row(
339: X_ROW_ID => l_rowid
340: ,X_PROJ_ELEMENT_ID => l_proj_element_id
341: ,X_PROJECT_ID => p_project_id
342: ,X_OBJECT_TYPE => 'PA_STRUCTURES'
660: l_attribute15 VARCHAR2(150) := NULL;
661:
662: l_dummy number;
663:
664: CURSOR getid is select project_id from pa_proj_elements
665: where proj_element_id = p_structure_id;
666:
667: CURSOR cur_elem_ver_seq IS
668: SELECT pa_proj_element_versions_s.nextval
929: IS
930:
931: l_rowid VARCHAR2(255);
932: l_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE;
933: l_proj_element_id PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE;
934: l_return_status VARCHAR2(1);
935: l_msg_count NUMBER;
936: l_msg_data VARCHAR2(250);
937:
1422: IS
1423:
1424: l_rowid VARCHAR2(255);
1425: l_project_id PA_PROJECTS_ALL.PROJECT_ID%TYPE;
1426: l_proj_element_id PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE;
1427: l_msg_count NUMBER;
1428: l_msg_data VARCHAR2(250);
1429:
1430:
1427: l_msg_count NUMBER;
1428: l_msg_data VARCHAR2(250);
1429:
1430:
1431: l_element_number PA_PROJ_ELEMENTS.element_number%TYPE;
1432: l_name PA_PROJ_ELEMENTS.name%TYPE;
1433: l_description PA_PROJ_ELEMENTS.description%TYPE;
1434: l_attribute_category VARCHAR2(30);
1435: l_attribute1 VARCHAR2(150);
1428: l_msg_data VARCHAR2(250);
1429:
1430:
1431: l_element_number PA_PROJ_ELEMENTS.element_number%TYPE;
1432: l_name PA_PROJ_ELEMENTS.name%TYPE;
1433: l_description PA_PROJ_ELEMENTS.description%TYPE;
1434: l_attribute_category VARCHAR2(30);
1435: l_attribute1 VARCHAR2(150);
1436: l_attribute2 VARCHAR2(150);
1429:
1430:
1431: l_element_number PA_PROJ_ELEMENTS.element_number%TYPE;
1432: l_name PA_PROJ_ELEMENTS.name%TYPE;
1433: l_description PA_PROJ_ELEMENTS.description%TYPE;
1434: l_attribute_category VARCHAR2(30);
1435: l_attribute1 VARCHAR2(150);
1436: l_attribute2 VARCHAR2(150);
1437: l_attribute3 VARCHAR2(150);
1470: attribute12,
1471: attribute13,
1472: attribute14,
1473: attribute15
1474: from pa_proj_elements
1475: where proj_element_id = p_structure_id;
1476: BEGIN
1477: IF (p_debug_mode = 'Y') THEN
1478: pa_debug.debug('PA_PROJECT_STRUCTURE_PVT1.UPDATE_STRUCTURE begin');
1607: END IF;
1608: RAISE FND_API.G_EXC_ERROR;
1609: END IF;
1610:
1611: PA_PROJ_ELEMENTS_PKG.UPDATE_ROW(
1612: X_ROW_ID => l_rowid
1613: ,X_PROJ_ELEMENT_ID => p_structure_id
1614: ,X_PROJECT_ID => l_project_id
1615: ,X_OBJECT_TYPE => 'PA_STRUCTURES'
2301: -- Begin fix for Bug # 4506308.
2302:
2303: cursor l_cur_projects_all(c_structure_id NUMBER) is
2304: select ppa.project_id, ppa.record_version_number
2305: from pa_projects_all ppa, pa_proj_elements ppe
2306: where ppa.project_id = ppe.project_id
2307: and ppe.proj_element_id = c_structure_id;
2308:
2309: l_project_id NUMBER;
2363: IF (p_validate_only <> FND_API.G_TRUE) THEN
2364: BEGIN
2365: --lock
2366: select rowid into l_rowid
2367: from pa_proj_elements
2368: where proj_element_id = p_structure_id
2369: and record_version_number = p_record_version_number
2370: for update of record_version_number NOWAIT;
2371: EXCEPTION
2388: END;
2389: ELSE
2390: BEGIN
2391: select rowid into l_rowid
2392: from pa_proj_elements
2393: where proj_element_id = p_structure_id
2394: and record_version_number = p_record_version_number;
2395: EXCEPTION
2396: when NO_DATA_FOUND then
2411: raise FND_API.G_EXC_ERROR;
2412: end if;
2413:
2414:
2415: PA_PROJ_ELEMENTS_PKG.DELETE_ROW(
2416: X_ROW_ID => l_rowid
2417: );
2418:
2419: x_return_status := FND_API.G_RET_STS_SUCCESS;
2769: CURSOR cur_last_working_ver( p_structure_id NUMBER )
2770: IS
2771: select str.element_version_id
2772: from pa_proj_elem_ver_structure str,
2773: pa_proj_elements ppe
2774: where ppe.proj_element_id = p_structure_id
2775: and ppe.project_id = str.project_id
2776: and ppe.proj_element_id = str.proj_element_id
2777: and str.status_code = 'STRUCTURE_WORKING'
3107:
3108: Cursor get_linking_tasks IS
3109: select a.element_version_id
3110: from pa_proj_element_versions a,
3111: pa_proj_elements b
3112: where a.proj_element_id = b.proj_element_id
3113: and a.parent_structure_version_id = p_structure_version_id
3114: and b.link_task_flag = 'Y';
3115: l_linking_task_rec get_linking_tasks%ROWTYPE;
3510: x_msg_data := 'PA_PS_STRUC_VER_NAM_UNIQUE';
3511: RAISE FND_API.G_EXC_ERROR;
3512: END IF;
3513: --Bug No 3450684 Smukka Checking for deliverable type
3514: IF PA_PROJ_ELEMENTS_UTILS.check_sharedstruct_deliv(p_structure_version_id) = 'Y' THEN
3515: PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
3516: p_msg_name => 'PA_PS_CHK_DEL_FAIL_PUB_STR');
3517: RAISE FND_API.G_EXC_ERROR;
3518: END IF;
3862: EXIT WHEN get_task_versions_csr%NOTFOUND;
3863:
3864: --hsiu added for task version status
3865: --Check if this task can be deleted.
3866: --Call PA_PROJ_ELEMENTS_UTILS.Check_Del_all_task_Ver_Ok
3867: --If ok to be deleted, move on to the next task and don't add this task
3868: -- (goto l_endofloop);
3869: --otherwise add this task and set status to 'CANCELLED'
3870: l_create_task_ver_flag := 'Y';
3881: and object_type_to = 'PA_TASKS';
3882:
3883: --If it is financial task, check if this task should be created
3884:
3885: -- PA_PROJ_ELEMENTS_UTILS.Check_create_subtask_ok(
3886: -- p_parent_task_ver_id => l_parent_ver_id
3887: -- ,x_return_status => l_return_status
3888: -- ,x_error_message_code => l_error_message_code);
3889:
3888: -- ,x_error_message_code => l_error_message_code);
3889:
3890: --bug: 2805602
3891: --hsiu: commented because this has been done in check_txn_on_summary_tasks
3892: -- If (PA_PROJ_ELEMENTS_UTILS.CHECK_IS_FINANCIAL_TASK(l_parent_id) = 'Y') THEN
3893: -- PA_TASK_UTILS.CHECK_CREATE_SUBTASK_OK(x_task_id => l_parent_id,
3894: -- x_err_code => l_err_code,
3895: -- x_err_stack => l_err_stack,
3896: -- x_err_stage => l_err_stage
3905: -- END IF;
3906:
3907: IF (l_task_versions_rec.TASK_UNPUB_VER_STATUS_CODE = 'TO_BE_DELETED') THEN
3908:
3909: PA_PROJ_ELEMENTS_UTILS.Check_Del_all_task_Ver_Ok(
3910: p_project_id => l_task_versions_rec.project_id
3911: ,p_task_version_id => l_task_versions_rec.element_version_id
3912: ,p_parent_structure_ver_id=> l_task_versions_rec.parent_structure_version_id
3913: ,x_return_status => l_return_status
3950: END IF;
3951: END IF;
3952: END IF;
3953: --set task status to cancelled.
3954: UPDATE pa_proj_elements
3955: set status_code = '128',
3956: RECORD_VERSION_NUMBER = NVL(RECORD_VERSION_NUMBER,1)+1,
3957: LAST_UPDATE_DATE = SYSDATE,
3958: LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
4692: ,l_project_id
4693: ,'PA_PROJECTS'
4694: ,ppev.financial_task_flag
4695: FROM ( SELECT * from pa_proj_element_versions ppev2
4696: --,pa_proj_elements ppe --bug 4573340 commenting out this for bug 4578813
4697: WHERE --bug#3094283 ppev2.project_id = l_project_id
4698: ppev2.parent_structure_version_id = p_structure_version_id
4699: and ppev2.object_type = 'PA_TASKS'
4700: /*
5407: --hsiu: prorate tasks for tasks that have to be deleted peer tasks
5408: l_parent_ver_id := l_parent_tbl.FIRST;
5409: FOR i IN 1..l_parent_tbl.COUNT LOOP
5410: --if it has child then prorate
5411: -- IF (PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(l_parent_ver_id) = 'N') THEN
5412: OPEN is_summary_elem(l_parent_ver_id);
5413: FETCH is_summary_elem INTO l_dummy;
5414: IF is_summary_elem%FOUND THEN
5415: PA_TASK_PVT1.RECALC_TASKS_WEIGHTING(
7061:
7062: CURSOR l_get_structure_csr(c_project_id NUMBER)
7063: IS
7064: SELECT *
7065: FROM PA_PROJ_ELEMENTS
7066: WHERE project_id = c_project_id
7067: AND object_type = 'PA_STRUCTURES';
7068:
7069: l_structure_rec l_get_structure_csr%ROWTYPE;
7130: SELECT a.element_version_id, a.proj_element_id, a.display_sequence, a.wbs_level,
7131: b.object_id_from1 parent_element_version_id
7132: FROM PA_PROJ_ELEMENT_VERSIONS a,
7133: PA_OBJECT_RELATIONSHIPS b,
7134: pa_proj_elements c
7135: WHERE a.object_type = 'PA_TASKS'
7136: AND a.parent_structure_version_id = c_structure_version_id
7137: AND a.element_version_id = b.object_id_to1
7138: AND b.relationship_type = 'S'
7179:
7180: CURSOR l_get_tasks_csr(c_project_id NUMBER, c_proj_element_id NUMBER)
7181: IS
7182: SELECT a.*, b.task_number PA_TASK_NUMBER
7183: FROM PA_PROJ_ELEMENTS a,
7184: PA_TASKS b
7185: WHERE a.object_type = 'PA_TASKS'
7186: AND a.project_id = c_project_id
7187: AND a.proj_element_id = c_proj_element_id
7203: --Bug No 3634334 Commented for performance reasons and rewritten the query.
7204: /*CURSOR l_linking_tasks_csr(c_src_project_id NUMBER, c_dest_project_id NUMBER)
7205: IS
7206: select a.task_id
7207: from pa_tasks a, pa_tasks b, pa_proj_elements c
7208: where a.project_id = c_dest_project_id
7209: and b.project_id = c_src_project_id
7210: and a.task_number = b.task_number
7211: and b.task_id = c.proj_element_id
7213:
7214: CURSOR l_linking_tasks_csr(c_src_project_id NUMBER, c_dest_project_id NUMBER)
7215: IS
7216: select /*+ INDEX(a PA_TASKS_U2) */ a.task_id --Bug No 3634334
7217: from pa_tasks a, pa_tasks b, pa_proj_elements c
7218: where a.project_id = c_dest_project_id
7219: and b.project_id = c_src_project_id
7220: and a.task_number = b.task_number
7221: and c.project_id = c_src_project_id --Bug No 3634334
7283: CURSOR l_get_structure_versions_csr(c_project_id NUMBER, c_structure_id NUMBER,
7284: c_option NUMBER)
7285: IS
7286: SELECT distinct(b.element_version_id)
7287: FROM pa_proj_elements a, pa_proj_elem_ver_structure b
7288: WHERE a.project_id = c_project_id
7289: AND a.proj_element_id = c_structure_id
7290: AND a.project_id = b.project_id
7291: AND a.proj_element_id = b.proj_element_id
7303: -- AND c.status_code <> 'STRUCTURE_PUBLISHED')
7304: AND 1 = (c_option)
7305: UNION
7306: SELECT distinct(b.element_version_id)
7307: FROM pa_proj_elements a, pa_proj_elem_ver_structure b
7308: WHERE a.project_id = c_project_id
7309: AND a.proj_element_id = c_structure_id
7310: AND a.project_id = b.project_id
7311: AND a.proj_element_id = b.proj_element_id
7318: (b.status_code <> 'STRUCTURE_PUBLISHED'))
7319: AND 2 = (c_option)
7320: UNION
7321: SELECT distinct(b.element_version_id)
7322: FROM pa_proj_elements a, pa_proj_elem_ver_structure b
7323: WHERE a.project_id = c_project_id
7324: AND a.proj_element_id = c_structure_id
7325: AND a.project_id = b.project_id
7326: AND a.proj_element_id = b.proj_element_id
8006: OPEN get_init_task_stat(l_tasks_rec.TYPE_ID);
8007: FETCH get_init_task_stat into l_init_status_code;
8008: CLOSE get_init_task_stat;
8009:
8010: PA_PROJ_ELEMENTS_PKG.Insert_Row(
8011: X_ROW_ID => l_rowid
8012: ,X_PROJ_ELEMENT_ID => l_task_id
8013: ,X_PROJECT_ID => p_dest_project_id
8014: ,X_OBJECT_TYPE => 'PA_TASKS'
8451: INTO l_project_id
8452: FROM PA_PROJ_ELEMENT_VERSIONS
8453: WHERE ELEMENT_VERSION_ID = p_structure_version_id;
8454:
8455: UPDATE PA_PROJ_ELEMENTS
8456: SET BASELINE_START_DATE= NULL ,
8457: BASELINE_FINISH_DATE= NULL ,
8458: RECORD_VERSION_NUMBER=NVL(RECORD_VERSION_NUMBER,0) + 1
8459: WHERE PROJECT_ID = l_project_id;
8488: RAISE FND_API.G_EXC_ERROR;
8489: END IF;
8490: */
8491:
8492: UPDATE PA_PROJ_ELEMENTS
8493: SET BASELINE_START_DATE=c1_rec.scheduled_start_date,
8494: BASELINE_FINISH_DATE=c1_rec.scheduled_finish_date, -- pa
8495: BASELINE_DURATION=c1_rec.duration,
8496: RECORD_VERSION_NUMBER = NVL(RECORD_VERSION_NUMBER,0) + 1
8580: )
8581: IS
8582: cursor c1 IS
8583: select a.proj_element_id
8584: from pa_proj_elements a,
8585: pa_proj_structure_types b,
8586: pa_structure_types c
8587: where a.project_id = p_project_id
8588: and a.object_type = 'PA_STRUCTURES'
9521:
9522: CURSOR get_wp_attr_rvn IS
9523: select b.proj_element_id, a.record_version_number
9524: from pa_proj_workplan_attr a,
9525: pa_proj_elements b,
9526: pa_proj_structure_types c,
9527: pa_structure_types d
9528: where a.project_id = b.project_id
9529: and a.proj_element_id = b.proj_element_id
9549: and LATEST_EFF_PUBLISHED_FLAG = 'Y';
9550:
9551: cursor sel_wp_structure_id IS
9552: select a.proj_element_id
9553: from pa_proj_elements a,
9554: pa_proj_structure_types b,
9555: pa_structure_types c
9556: where a.project_id = p_project_id
9557: and a.object_type = 'PA_STRUCTURES'
9571:
9572: cursor sel_all_wp_structure_ver(c_struc_id NUMBER) IS
9573: select a.element_version_id, a.record_version_number
9574: from pa_proj_element_versions a,
9575: pa_proj_elements b
9576: where a.proj_element_id = b.proj_element_id
9577: and a.project_id = b.project_id
9578: and b.proj_element_id = c_struc_id;
9579:
9598: l_proj_progress_attr_rec sel_proj_progress_attr%ROWTYPE;
9599:
9600: cursor sel_fin_structure_id IS
9601: select a.proj_element_id
9602: from pa_proj_elements a,
9603: pa_proj_structure_types b,
9604: pa_structure_types c
9605: where a.project_id = p_project_id
9606: and a.object_type = 'PA_STRUCTURES'
9884: LOOP
9885: FETCH get_top_tasks into l_task_ver_id;
9886: EXIT WHEN get_top_tasks%NOTFOUND;
9887:
9888: PA_PROJ_ELEMENTS_UTILS.check_del_all_task_ver_ok(
9889: p_project_id => p_project_id
9890: ,p_task_version_id => l_task_ver_id
9891: ,p_parent_structure_ver_id => l_del_struc_ver_id
9892: ,x_return_status => l_return_status
9926: END LOOP;
9927: CLOSE sel_other_structure_ver;
9928:
9929: --NULL all baseline dates
9930: UPDATE pa_proj_elements
9931: SET baseline_start_date = NULL,
9932: baseline_finish_date = NULL,
9933: record_version_number = record_version_number+1
9934: WHERE project_id = p_project_id;
10044: LOOP
10045: FETCH get_top_tasks into l_task_ver_id;
10046: EXIT WHEN get_top_tasks%NOTFOUND;
10047:
10048: PA_PROJ_ELEMENTS_UTILS.check_del_all_task_ver_ok(
10049: p_project_id => p_project_id
10050: ,p_task_version_id => l_task_ver_id
10051: ,p_parent_structure_ver_id => l_del_struc_ver_id
10052: ,x_return_status => l_return_status
10154: LOOP
10155: FETCH get_top_tasks into l_task_ver_id;
10156: EXIT WHEN get_top_tasks%NOTFOUND;
10157:
10158: PA_PROJ_ELEMENTS_UTILS.check_del_all_task_ver_ok(
10159: p_project_id => p_project_id
10160: ,p_task_version_id => l_task_ver_id
10161: ,p_parent_structure_ver_id => l_del_struc_ver_id
10162: ,x_return_status => l_return_status
10332:
10333: --If it is lowest task, get dates
10334: --hsiu: commented for performance
10335: -- IF (l_object_type = 'PA_TASKS' AND
10336: -- PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(l_element_version_id) = 'Y') THEN
10337: -- OPEN sel_task_dates(l_task_id);
10338: -- FETCH sel_task_dates into l_start_date, l_completion_date;
10339: -- CLOSE sel_task_dates;
10340:
10395: SYSDATE,
10396: FND_GLOBAL.USER_ID,
10397: SYSDATE,
10398: FND_GLOBAL.USER_ID,
10399: DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', decode(PT.COMPLETION_DATE, NULL, trunc(l_target_start_date), trunc(PT.START_DATE))),
10400: DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', NVL(trunc(PT.COMPLETION_DATE), trunc(l_target_finish_date))),
10401: 'N',
10402: 'N',
10403: l_calendar_id,
10396: FND_GLOBAL.USER_ID,
10397: SYSDATE,
10398: FND_GLOBAL.USER_ID,
10399: DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', decode(PT.COMPLETION_DATE, NULL, trunc(l_target_start_date), trunc(PT.START_DATE))),
10400: DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', NVL(trunc(PT.COMPLETION_DATE), trunc(l_target_finish_date))),
10401: 'N',
10402: 'N',
10403: l_calendar_id,
10404: 0,
10576: LOOP
10577: FETCH get_top_tasks into l_task_ver_id;
10578: EXIT WHEN get_top_tasks%NOTFOUND;
10579:
10580: PA_PROJ_ELEMENTS_UTILS.check_del_all_task_ver_ok(
10581: p_project_id => p_project_id
10582: ,p_task_version_id => l_task_ver_id
10583: ,p_parent_structure_ver_id => l_del_struc_ver_id
10584: ,x_return_status => l_return_status
10618: END LOOP;
10619: CLOSE sel_other_structure_ver;
10620:
10621: --NULL all baseline dates
10622: UPDATE pa_proj_elements
10623: SET baseline_start_date = NULL,
10624: baseline_finish_date = NULL,
10625: record_version_number = record_version_number+1
10626: WHERE project_id = p_project_id;
10960:
10961: cursor sel_latest_pub_ver(c_structure_id NUMBER) IS
10962: select a.element_version_id
10963: from pa_proj_elem_ver_structure a,
10964: pa_proj_elements b
10965: where b.proj_element_id = c_structure_id
10966: and b.project_id = a.project_id
10967: and b.proj_element_id = a.proj_element_id
10968: and a.status_code = 'STRUCTURE_PUBLISHED'
10970:
10971: CURSOR get_template_flag IS
10972: select a.template_flag
10973: from pa_projects_all a,
10974: pa_proj_elements b
10975: where a.project_id = b.project_id
10976: and b.proj_element_id = p_proj_element_id;
10977:
10978: cursor sel_struc_ver_attr_rvn(c_struc_ver_id NUMBER) IS
10995:
10996: --bug 3125813
10997: cursor sel_one_struc_ver(c_structure_id NUMBER) IS
10998: select b.project_id, b.element_version_id
10999: from pa_proj_elements a,
11000: pa_proj_element_versions b
11001: where a.proj_element_id = c_structure_id
11002: and a.project_id = b.project_id
11003: and a.proj_element_id = b.proj_element_id;
11292:
11293: cursor get_wp_versions IS
11294: select ppevs.element_version_id
11295: from pa_proj_elem_ver_structure ppevs,
11296: pa_proj_elements ppe,
11297: pa_proj_structure_types ppst,
11298: pa_structure_types pst
11299: where ppevs.status_code <> 'STRUCTURE_PUBLISHED'
11300: and ppevs.project_id = ppe.project_id
11307: and '1' = l_opt
11308: union all
11309: select ppevs.element_version_id
11310: from pa_proj_elem_ver_structure ppevs,
11311: pa_proj_elements ppe,
11312: pa_proj_structure_types ppst,
11313: pa_structure_types pst
11314: where ppevs.status_code = 'STRUCTURE_PUBLISHED'
11315: and ppevs.project_id = ppe.project_id
11904: pev.element_version_id, pev.record_version_number,
11905: pevs.pev_structure_id, pevs.record_version_number,
11906: pevsh.pev_schedule_id, pevsh.record_version_number,
11907: pevsh.rowid
11908: from pa_proj_elements pe,
11909: pa_proj_element_versions pev,
11910: pa_proj_elem_ver_structure pevs,
11911: pa_proj_elem_ver_schedule pevsh
11912: where pev.element_version_id = p_structure_version_id and
11934: from pa_proj_workplan_attr
11935: where proj_element_id = c_proj_element_id;
11936:
11937: l_project_id PA_PROJ_ELEMENT_VERSIONS.PROJECT_ID%TYPE;
11938: l_proj_element_id PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE;
11939: l_pe_rvn PA_PROJ_ELEMENTS.RECORD_VERSION_NUMBER%TYPE;
11940: l_element_version_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
11941: l_pev_rvn PA_PROJ_ELEMENT_VERSIONS.RECORD_VERSION_NUMBER%TYPE;
11942: l_pev_structure_id PA_PROJ_ELEM_VER_STRUCTURE.PEV_STRUCTURE_ID%TYPE;
11935: where proj_element_id = c_proj_element_id;
11936:
11937: l_project_id PA_PROJ_ELEMENT_VERSIONS.PROJECT_ID%TYPE;
11938: l_proj_element_id PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE;
11939: l_pe_rvn PA_PROJ_ELEMENTS.RECORD_VERSION_NUMBER%TYPE;
11940: l_element_version_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
11941: l_pev_rvn PA_PROJ_ELEMENT_VERSIONS.RECORD_VERSION_NUMBER%TYPE;
11942: l_pev_structure_id PA_PROJ_ELEM_VER_STRUCTURE.PEV_STRUCTURE_ID%TYPE;
11943: l_pevs_rvn PA_PROJ_ELEM_VER_STRUCTURE.RECORD_VERSION_NUMBER%TYPE;
12188:
12189: END IF;
12190:
12191: select record_version_number into l_pe_rvn
12192: from pa_proj_elements where proj_element_id = l_proj_element_id;
12193: PA_PROJECT_STRUCTURE_PVT1.Delete_Structure(
12194: p_commit => p_commit,
12195: p_debug_mode => p_debug_mode,
12196: p_structure_id => l_proj_element_id,
12707: CURSOR check_progress_allowed(c_element_version_id NUMBER) IS
12708: SELECT ptt.prog_entry_enable_flag
12709: FROM pa_task_types ptt
12710: , pa_proj_element_versions ppev
12711: , pa_proj_elements ppe
12712: WHERE ppev.element_version_id = c_element_version_id
12713: AND ppev.proj_element_id = ppe.proj_element_id
12714: AND ptt.object_type ='PA_TASKS' /* bug 3279978 FP M Enhancement */
12715: AND ppe.TYPE_ID = ptt.task_type_id;
12726: SELECT sum(ppevs.duration)
12727: FROM pa_proj_elem_ver_schedule ppevs
12728: , pa_object_relationships por
12729: , pa_proj_element_versions ppev
12730: , pa_proj_elements ppe
12731: , pa_task_types ptt
12732: WHERE por.object_id_from1 = c_parent_element_version_id
12733: AND por.object_type_to = 'PA_TASKS'
12734: AND por.relationship_type = 'S'
13325: ,ppev.element_version_id
13326: ,l_project_id
13327: ,'PA_PROJECTS'
13328: FROM ( SELECT ppev2.* from pa_proj_element_versions ppev2
13329: ,pa_proj_elements ppe --bug 4573340
13330: WHERE -- bug#3094283 ppev2.project_id = l_project_id
13331: ppev2.parent_structure_version_id = p_structure_version_id
13332: --bug 4573340
13333: and ppe.project_id = ppev2.project_id
13880:
13881: CURSOR get_wp_attr_rvn IS
13882: select b.proj_element_id, a.record_version_number
13883: from pa_proj_workplan_attr a,
13884: pa_proj_elements b,
13885: pa_proj_structure_types c,
13886: pa_structure_types d
13887: where a.project_id = b.project_id
13888: and a.proj_element_id = b.proj_element_id
13910: --bug 4054587, replace literal with bind variable
13911: l_wp_structure_code VARCHAR2(10) := 'WORKPLAN';
13912: cursor sel_wp_structure_id IS
13913: select a.proj_element_id
13914: from pa_proj_elements a,
13915: pa_proj_structure_types b,
13916: pa_structure_types c
13917: where a.project_id = p_project_id
13918: and a.object_type = 'PA_STRUCTURES'
13933:
13934: cursor sel_all_wp_structure_ver(c_struc_id NUMBER) IS
13935: select a.element_version_id, a.record_version_number
13936: from pa_proj_element_versions a,
13937: pa_proj_elements b
13938: where a.proj_element_id = b.proj_element_id
13939: and a.project_id = b.project_id
13940: and b.proj_element_id = c_struc_id;
13941:
13962: --bug 4054587, replace literal with bind variable
13963: l_fin_structure_code VARCHAR2(10) := 'FINANCIAL';
13964: cursor sel_fin_structure_id IS
13965: select a.proj_element_id
13966: from pa_proj_elements a,
13967: pa_proj_structure_types b,
13968: pa_structure_types c
13969: where a.project_id = p_project_id
13970: and a.object_type = 'PA_STRUCTURES'
15101: SYSDATE,
15102: FND_GLOBAL.USER_ID,
15103: SYSDATE,
15104: FND_GLOBAL.USER_ID,
15105: DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', NVL(trunc(PT.START_DATE), trunc(l_proj_start_date))),
15106: DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', NVL(trunc(PT.COMPLETION_DATE), trunc(l_proj_completion_date))),
15107: 'N',
15108: 'N',
15109: NULL,
15102: FND_GLOBAL.USER_ID,
15103: SYSDATE,
15104: FND_GLOBAL.USER_ID,
15105: DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', NVL(trunc(PT.START_DATE), trunc(l_proj_start_date))),
15106: DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', NVL(trunc(PT.COMPLETION_DATE), trunc(l_proj_completion_date))),
15107: 'N',
15108: 'N',
15109: NULL,
15110: 0,
15403: LOOP
15404: FETCH get_top_tasks into l_task_ver_id;
15405: EXIT WHEN get_top_tasks%NOTFOUND;
15406:
15407: PA_PROJ_ELEMENTS_UTILS.check_del_all_task_ver_ok(
15408: p_project_id => p_project_id
15409: ,p_task_version_id => l_task_ver_id
15410: ,p_parent_structure_ver_id => l_del_struc_ver_id
15411: ,x_return_status => l_return_status
15606:
15607: -- Hence ,the structure split will not be allowed as "name not unique error will be thrown"
15608: -- To avoid this Problem , We are resetting the structure name before Sharing
15609:
15610: UPDATE pa_proj_elements
15611: SET name = l_name
15612: ,element_number = l_name
15613: WHERE proj_element_id = l_structure_id ;
15614:
15655: where parent_structure_version_id = l_structure_version_id
15656: and object_type = 'PA_TASKS'
15657: and proj_element_id NOT IN
15658: (select proj_element_id
15659: from pa_proj_elements
15660: where project_id = p_project_id
15661: and object_type = 'PA_TASKS'
15662: and link_task_flag = 'Y');
15663: END IF;
15980: LOOP
15981: FETCH get_top_tasks into l_task_ver_id;
15982: EXIT WHEN get_top_tasks%NOTFOUND;
15983:
15984: PA_PROJ_ELEMENTS_UTILS.check_del_all_task_ver_ok(
15985: p_project_id => p_project_id
15986: ,p_task_version_id => l_task_ver_id
15987: ,p_parent_structure_ver_id => l_del_struc_ver_id
15988: ,x_return_status => l_return_status
16094: x_msg_count=>l_msg_count,
16095: x_msg_data=>l_msg_data);
16096:
16097: --delete elements
16098: delete from pa_proj_elements ppe
16099: where proj_element_ID in (
16100: select proj_element_id
16101: from pa_proj_element_versions
16102: where parent_structure_version_id = l_keep_structure_ver_id
16273: OPEN sel_wp_structure_id;
16274: FETCH sel_wp_structure_id INTO l_structure_id;
16275: CLOSE sel_wp_structure_id;
16276:
16277: update pa_proj_elements
16278: set name = l_wp_name,
16279: element_number = l_wp_name
16280: where proj_element_id = l_structure_id;
16281:
16351: where parent_structure_version_id = l_structure_version_id
16352: and object_type = 'PA_TASKS'
16353: and proj_element_id NOT IN
16354: (select proj_element_id
16355: from pa_proj_elements
16356: where project_id = p_project_id
16357: and object_type = 'PA_TASKS'
16358: and link_task_flag = 'Y');
16359:
16515: LOOP
16516: FETCH get_top_tasks into l_task_ver_id;
16517: EXIT WHEN get_top_tasks%NOTFOUND;
16518:
16519: PA_PROJ_ELEMENTS_UTILS.check_del_all_task_ver_ok(
16520: p_project_id => p_project_id
16521: ,p_task_version_id => l_task_ver_id
16522: ,p_parent_structure_ver_id => l_del_struc_ver_id
16523: ,x_return_status => l_return_status
16738: OPEN sel_wp_structure_id;
16739: FETCH sel_wp_structure_id INTO l_structure_id;
16740: CLOSE sel_wp_structure_id;
16741:
16742: update pa_proj_elements
16743: set name = l_wp_name,
16744: element_number = l_wp_name
16745: where proj_element_id = l_structure_id;
16746:
16850: LOOP
16851: FETCH get_top_tasks into l_task_ver_id;
16852: EXIT WHEN get_top_tasks%NOTFOUND;
16853:
16854: PA_PROJ_ELEMENTS_UTILS.check_del_all_task_ver_ok(
16855: p_project_id => p_project_id
16856: ,p_task_version_id => l_task_ver_id
16857: ,p_parent_structure_ver_id => l_del_struc_ver_id
16858: ,x_return_status => l_return_status
16944: LOOP
16945: FETCH get_top_tasks into l_task_ver_id;
16946: EXIT WHEN get_top_tasks%NOTFOUND;
16947:
16948: PA_PROJ_ELEMENTS_UTILS.check_del_all_task_ver_ok(
16949: p_project_id => p_project_id
16950: ,p_task_version_id => l_task_ver_id
16951: ,p_parent_structure_ver_id => l_del_struc_ver_id
16952: ,x_return_status => l_return_status
17090: SYSDATE,
17091: FND_GLOBAL.USER_ID,
17092: SYSDATE,
17093: FND_GLOBAL.USER_ID,
17094: DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', NVL(trunc(PT.START_DATE), trunc(l_proj_start_date))),
17095: DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', NVL(trunc(PT.COMPLETION_DATE), trunc(l_proj_completion_date))),
17096: 'N',
17097: 'N',
17098: NULL,
17091: FND_GLOBAL.USER_ID,
17092: SYSDATE,
17093: FND_GLOBAL.USER_ID,
17094: DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', NVL(trunc(PT.START_DATE), trunc(l_proj_start_date))),
17095: DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', NVL(trunc(PT.COMPLETION_DATE), trunc(l_proj_completion_date))),
17096: 'N',
17097: 'N',
17098: NULL,
17099: 0,
17244: LOOP
17245: FETCH get_top_tasks into l_task_ver_id;
17246: EXIT WHEN get_top_tasks%NOTFOUND;
17247:
17248: PA_PROJ_ELEMENTS_UTILS.check_del_all_task_ver_ok(
17249: p_project_id => p_project_id
17250: ,p_task_version_id => l_task_ver_id
17251: ,p_parent_structure_ver_id => l_del_struc_ver_id
17252: ,x_return_status => l_return_status
17336: LOOP
17337: FETCH get_top_tasks into l_task_ver_id;
17338: EXIT WHEN get_top_tasks%NOTFOUND;
17339:
17340: PA_PROJ_ELEMENTS_UTILS.check_del_all_task_ver_ok(
17341: p_project_id => p_project_id
17342: ,p_task_version_id => l_task_ver_id
17343: ,p_parent_structure_ver_id => l_del_struc_ver_id
17344: ,x_return_status => l_return_status
17482: SYSDATE,
17483: FND_GLOBAL.USER_ID,
17484: SYSDATE,
17485: FND_GLOBAL.USER_ID,
17486: DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', NVL(trunc(PT.START_DATE), trunc(l_proj_start_date))),
17487: DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', NVL(trunc(PT.COMPLETION_DATE), trunc(l_proj_completion_date))),
17488: 'N',
17489: 'N',
17490: NULL,
17483: FND_GLOBAL.USER_ID,
17484: SYSDATE,
17485: FND_GLOBAL.USER_ID,
17486: DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', NVL(trunc(PT.START_DATE), trunc(l_proj_start_date))),
17487: DECODE(PA_PROJ_ELEMENTS_UTILS.IS_LOWEST_TASK(PPEV.element_version_id), 'N', trunc(SYSDATE), 'Y', NVL(trunc(PT.COMPLETION_DATE), trunc(l_proj_completion_date))),
17488: 'N',
17489: 'N',
17490: NULL,
17491: 0,