[Home] [Help]
948: l_original_baseline_date DATE := NULL;
949: l_orig_baseline_person_id NUMBER;
950: l_original_flag VARCHAR2(1) := 'N';
951: l_struc_ver_number NUMBER;
952: l_change_reason_code PA_PROJ_ELEM_VER_STRUCTURE.CHANGE_REASON_CODE%TYPE;
953: l_dummy VARCHAR2(1);
954: --rtarway, 3655698
955: l_structure_version_desc VARCHAR2(2000);
956:
965: where element_version_id = p_structure_version_id;
966:
967: cursor get_published_ver_num(c_project_id NUMBER, c_proj_element_id NUMBER) IS
968: select nvl(max(version_number),0)+1
969: from pa_proj_elem_ver_structure
970: where project_id = c_project_id
971: and proj_element_id = c_proj_element_id
972: and status_code = 'STRUCTURE_PUBLISHED';
973:
972: and status_code = 'STRUCTURE_PUBLISHED';
973:
974: cursor get_working_ver_num(c_project_id NUMBER, c_proj_element_id NUMBER) IS
975: select nvl(max(version_number),0)+1
976: from pa_proj_elem_ver_structure
977: where project_id = c_project_id
978: and proj_element_id = c_proj_element_id
979: and status_code <> 'STRUCTURE_PUBLISHED';
980:
986: and p.proj_element_id = c_structure_id;
987:
988: CURSOR check_working_ver_exists(c_project_id NUMBER, c_structure_id NUMBER) IS
989: select '1'
990: from pa_proj_elem_ver_structure
991: where project_id = c_project_id
992: and proj_element_id = c_structure_id
993: and status_code <> 'STRUCTURE_PUBLISHED';
994:
1050: l_effective_date := l_published_date;
1051: l_latest_eff_published_flag := 'Y';
1052:
1053: --set others with lastest_eff_published_flag to 'N'
1054: update pa_proj_elem_ver_structure
1055: set latest_eff_published_flag = 'N',
1056: record_version_number = record_version_number + 1
1057: where project_id = l_project_id
1058: and proj_element_id = l_proj_element_id
1064: l_current_flag := 'Y';
1065: l_cur_baseline_person_id := l_published_person_id;
1066:
1067: --clear flags in other versions.
1068: update pa_proj_elem_ver_structure
1069: set current_flag = 'N',
1070: current_baseline_date = NULL,
1071: current_baseline_person_id = NULL,
1072: record_version_number = record_version_number + 1
1104: l_original_flag := 'Y';
1105: l_orig_baseline_person_id := l_published_person_id;
1106:
1107: --clear flags in other versions.
1108: update pa_proj_elem_ver_structure
1109: set original_flag = 'N',
1110: original_baseline_date = NULL,
1111: original_baseline_person_id = NULL,
1112: record_version_number = record_version_number + 1
1134: END IF;
1135: close get_person_id;
1136:
1137: --set others with lastest_eff_published_flag to 'N'
1138: update pa_proj_elem_ver_structure
1139: set latest_eff_published_flag = 'N',
1140: record_version_number = record_version_number + 1
1141: where project_id = l_project_id
1142: and proj_element_id = l_proj_element_id
1148: l_current_flag := 'Y';
1149: l_cur_baseline_person_id := l_published_person_id;
1150:
1151: --clear flags in other versions.
1152: update pa_proj_elem_ver_structure
1153: set current_flag = 'N',
1154: current_baseline_date = NULL,
1155: current_baseline_person_id = NULL,
1156: record_version_number = record_version_number + 1
1253: ELSE
1254: l_structure_version_desc := p_structure_version_desc;
1255: end if;
1256:
1257: PA_PROJ_ELEM_VER_STRUCTURE_PKG.insert_row(
1258: X_ROWID => l_rowid
1259: , X_PEV_STRUCTURE_ID => x_pev_structure_id
1260: , X_ELEMENT_VERSION_ID => p_structure_version_id
1261: , X_VERSION_NUMBER => l_struc_ver_number
1747: )
1748: IS
1749:
1750: l_rowid VARCHAR2(255);
1751: l_structure_version_id PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE;
1752: l_project_id PA_PROJ_ELEM_VER_STRUCTURE.PROJECT_ID%TYPE;
1753: l_proj_element_id PA_PROJ_ELEM_VER_STRUCTURE.PROJ_ELEMENT_ID%TYPE;
1754: l_version_number PA_PROJ_ELEM_VER_STRUCTURE.VERSION_NUMBER%TYPE;
1755: l_name PA_PROJ_ELEM_VER_STRUCTURE.NAME%TYPE;
1748: IS
1749:
1750: l_rowid VARCHAR2(255);
1751: l_structure_version_id PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE;
1752: l_project_id PA_PROJ_ELEM_VER_STRUCTURE.PROJECT_ID%TYPE;
1753: l_proj_element_id PA_PROJ_ELEM_VER_STRUCTURE.PROJ_ELEMENT_ID%TYPE;
1754: l_version_number PA_PROJ_ELEM_VER_STRUCTURE.VERSION_NUMBER%TYPE;
1755: l_name PA_PROJ_ELEM_VER_STRUCTURE.NAME%TYPE;
1756: l_description PA_PROJ_ELEM_VER_STRUCTURE.DESCRIPTION%TYPE;
1749:
1750: l_rowid VARCHAR2(255);
1751: l_structure_version_id PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE;
1752: l_project_id PA_PROJ_ELEM_VER_STRUCTURE.PROJECT_ID%TYPE;
1753: l_proj_element_id PA_PROJ_ELEM_VER_STRUCTURE.PROJ_ELEMENT_ID%TYPE;
1754: l_version_number PA_PROJ_ELEM_VER_STRUCTURE.VERSION_NUMBER%TYPE;
1755: l_name PA_PROJ_ELEM_VER_STRUCTURE.NAME%TYPE;
1756: l_description PA_PROJ_ELEM_VER_STRUCTURE.DESCRIPTION%TYPE;
1757: l_effective_date PA_PROJ_ELEM_VER_STRUCTURE.effective_date%TYPE;
1750: l_rowid VARCHAR2(255);
1751: l_structure_version_id PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE;
1752: l_project_id PA_PROJ_ELEM_VER_STRUCTURE.PROJECT_ID%TYPE;
1753: l_proj_element_id PA_PROJ_ELEM_VER_STRUCTURE.PROJ_ELEMENT_ID%TYPE;
1754: l_version_number PA_PROJ_ELEM_VER_STRUCTURE.VERSION_NUMBER%TYPE;
1755: l_name PA_PROJ_ELEM_VER_STRUCTURE.NAME%TYPE;
1756: l_description PA_PROJ_ELEM_VER_STRUCTURE.DESCRIPTION%TYPE;
1757: l_effective_date PA_PROJ_ELEM_VER_STRUCTURE.effective_date%TYPE;
1758: l_published_date PA_PROJ_ELEM_VER_STRUCTURE.published_date%TYPE;
1751: l_structure_version_id PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE;
1752: l_project_id PA_PROJ_ELEM_VER_STRUCTURE.PROJECT_ID%TYPE;
1753: l_proj_element_id PA_PROJ_ELEM_VER_STRUCTURE.PROJ_ELEMENT_ID%TYPE;
1754: l_version_number PA_PROJ_ELEM_VER_STRUCTURE.VERSION_NUMBER%TYPE;
1755: l_name PA_PROJ_ELEM_VER_STRUCTURE.NAME%TYPE;
1756: l_description PA_PROJ_ELEM_VER_STRUCTURE.DESCRIPTION%TYPE;
1757: l_effective_date PA_PROJ_ELEM_VER_STRUCTURE.effective_date%TYPE;
1758: l_published_date PA_PROJ_ELEM_VER_STRUCTURE.published_date%TYPE;
1759: l_published_by_person_id PA_PROJ_ELEM_VER_STRUCTURE.published_by_person_id%TYPE;
1752: l_project_id PA_PROJ_ELEM_VER_STRUCTURE.PROJECT_ID%TYPE;
1753: l_proj_element_id PA_PROJ_ELEM_VER_STRUCTURE.PROJ_ELEMENT_ID%TYPE;
1754: l_version_number PA_PROJ_ELEM_VER_STRUCTURE.VERSION_NUMBER%TYPE;
1755: l_name PA_PROJ_ELEM_VER_STRUCTURE.NAME%TYPE;
1756: l_description PA_PROJ_ELEM_VER_STRUCTURE.DESCRIPTION%TYPE;
1757: l_effective_date PA_PROJ_ELEM_VER_STRUCTURE.effective_date%TYPE;
1758: l_published_date PA_PROJ_ELEM_VER_STRUCTURE.published_date%TYPE;
1759: l_published_by_person_id PA_PROJ_ELEM_VER_STRUCTURE.published_by_person_id%TYPE;
1760: l_current_baseline_date PA_PROJ_ELEM_VER_STRUCTURE.current_baseline_date%TYPE;
1753: l_proj_element_id PA_PROJ_ELEM_VER_STRUCTURE.PROJ_ELEMENT_ID%TYPE;
1754: l_version_number PA_PROJ_ELEM_VER_STRUCTURE.VERSION_NUMBER%TYPE;
1755: l_name PA_PROJ_ELEM_VER_STRUCTURE.NAME%TYPE;
1756: l_description PA_PROJ_ELEM_VER_STRUCTURE.DESCRIPTION%TYPE;
1757: l_effective_date PA_PROJ_ELEM_VER_STRUCTURE.effective_date%TYPE;
1758: l_published_date PA_PROJ_ELEM_VER_STRUCTURE.published_date%TYPE;
1759: l_published_by_person_id PA_PROJ_ELEM_VER_STRUCTURE.published_by_person_id%TYPE;
1760: l_current_baseline_date PA_PROJ_ELEM_VER_STRUCTURE.current_baseline_date%TYPE;
1761: l_current_flag PA_PROJ_ELEM_VER_STRUCTURE.current_flag%TYPE;
1754: l_version_number PA_PROJ_ELEM_VER_STRUCTURE.VERSION_NUMBER%TYPE;
1755: l_name PA_PROJ_ELEM_VER_STRUCTURE.NAME%TYPE;
1756: l_description PA_PROJ_ELEM_VER_STRUCTURE.DESCRIPTION%TYPE;
1757: l_effective_date PA_PROJ_ELEM_VER_STRUCTURE.effective_date%TYPE;
1758: l_published_date PA_PROJ_ELEM_VER_STRUCTURE.published_date%TYPE;
1759: l_published_by_person_id PA_PROJ_ELEM_VER_STRUCTURE.published_by_person_id%TYPE;
1760: l_current_baseline_date PA_PROJ_ELEM_VER_STRUCTURE.current_baseline_date%TYPE;
1761: l_current_flag PA_PROJ_ELEM_VER_STRUCTURE.current_flag%TYPE;
1762: l_current_baseline_person_id PA_PROJ_ELEM_VER_STRUCTURE.current_baseline_person_id%TYPE;
1755: l_name PA_PROJ_ELEM_VER_STRUCTURE.NAME%TYPE;
1756: l_description PA_PROJ_ELEM_VER_STRUCTURE.DESCRIPTION%TYPE;
1757: l_effective_date PA_PROJ_ELEM_VER_STRUCTURE.effective_date%TYPE;
1758: l_published_date PA_PROJ_ELEM_VER_STRUCTURE.published_date%TYPE;
1759: l_published_by_person_id PA_PROJ_ELEM_VER_STRUCTURE.published_by_person_id%TYPE;
1760: l_current_baseline_date PA_PROJ_ELEM_VER_STRUCTURE.current_baseline_date%TYPE;
1761: l_current_flag PA_PROJ_ELEM_VER_STRUCTURE.current_flag%TYPE;
1762: l_current_baseline_person_id PA_PROJ_ELEM_VER_STRUCTURE.current_baseline_person_id%TYPE;
1763: l_original_baseline_date PA_PROJ_ELEM_VER_STRUCTURE.original_baseline_date%TYPE;
1756: l_description PA_PROJ_ELEM_VER_STRUCTURE.DESCRIPTION%TYPE;
1757: l_effective_date PA_PROJ_ELEM_VER_STRUCTURE.effective_date%TYPE;
1758: l_published_date PA_PROJ_ELEM_VER_STRUCTURE.published_date%TYPE;
1759: l_published_by_person_id PA_PROJ_ELEM_VER_STRUCTURE.published_by_person_id%TYPE;
1760: l_current_baseline_date PA_PROJ_ELEM_VER_STRUCTURE.current_baseline_date%TYPE;
1761: l_current_flag PA_PROJ_ELEM_VER_STRUCTURE.current_flag%TYPE;
1762: l_current_baseline_person_id PA_PROJ_ELEM_VER_STRUCTURE.current_baseline_person_id%TYPE;
1763: l_original_baseline_date PA_PROJ_ELEM_VER_STRUCTURE.original_baseline_date%TYPE;
1764: l_original_flag PA_PROJ_ELEM_VER_STRUCTURE.original_flag%TYPE;
1757: l_effective_date PA_PROJ_ELEM_VER_STRUCTURE.effective_date%TYPE;
1758: l_published_date PA_PROJ_ELEM_VER_STRUCTURE.published_date%TYPE;
1759: l_published_by_person_id PA_PROJ_ELEM_VER_STRUCTURE.published_by_person_id%TYPE;
1760: l_current_baseline_date PA_PROJ_ELEM_VER_STRUCTURE.current_baseline_date%TYPE;
1761: l_current_flag PA_PROJ_ELEM_VER_STRUCTURE.current_flag%TYPE;
1762: l_current_baseline_person_id PA_PROJ_ELEM_VER_STRUCTURE.current_baseline_person_id%TYPE;
1763: l_original_baseline_date PA_PROJ_ELEM_VER_STRUCTURE.original_baseline_date%TYPE;
1764: l_original_flag PA_PROJ_ELEM_VER_STRUCTURE.original_flag%TYPE;
1765: l_original_baseline_person_id PA_PROJ_ELEM_VER_STRUCTURE.original_baseline_person_id%TYPE;
1758: l_published_date PA_PROJ_ELEM_VER_STRUCTURE.published_date%TYPE;
1759: l_published_by_person_id PA_PROJ_ELEM_VER_STRUCTURE.published_by_person_id%TYPE;
1760: l_current_baseline_date PA_PROJ_ELEM_VER_STRUCTURE.current_baseline_date%TYPE;
1761: l_current_flag PA_PROJ_ELEM_VER_STRUCTURE.current_flag%TYPE;
1762: l_current_baseline_person_id PA_PROJ_ELEM_VER_STRUCTURE.current_baseline_person_id%TYPE;
1763: l_original_baseline_date PA_PROJ_ELEM_VER_STRUCTURE.original_baseline_date%TYPE;
1764: l_original_flag PA_PROJ_ELEM_VER_STRUCTURE.original_flag%TYPE;
1765: l_original_baseline_person_id PA_PROJ_ELEM_VER_STRUCTURE.original_baseline_person_id%TYPE;
1766: l_lock_status_code PA_PROJ_ELEM_VER_STRUCTURE.lock_status_code%TYPE;
1759: l_published_by_person_id PA_PROJ_ELEM_VER_STRUCTURE.published_by_person_id%TYPE;
1760: l_current_baseline_date PA_PROJ_ELEM_VER_STRUCTURE.current_baseline_date%TYPE;
1761: l_current_flag PA_PROJ_ELEM_VER_STRUCTURE.current_flag%TYPE;
1762: l_current_baseline_person_id PA_PROJ_ELEM_VER_STRUCTURE.current_baseline_person_id%TYPE;
1763: l_original_baseline_date PA_PROJ_ELEM_VER_STRUCTURE.original_baseline_date%TYPE;
1764: l_original_flag PA_PROJ_ELEM_VER_STRUCTURE.original_flag%TYPE;
1765: l_original_baseline_person_id PA_PROJ_ELEM_VER_STRUCTURE.original_baseline_person_id%TYPE;
1766: l_lock_status_code PA_PROJ_ELEM_VER_STRUCTURE.lock_status_code%TYPE;
1767: l_locked_by_person_id PA_PROJ_ELEM_VER_STRUCTURE.locked_by_person_id%TYPE;
1760: l_current_baseline_date PA_PROJ_ELEM_VER_STRUCTURE.current_baseline_date%TYPE;
1761: l_current_flag PA_PROJ_ELEM_VER_STRUCTURE.current_flag%TYPE;
1762: l_current_baseline_person_id PA_PROJ_ELEM_VER_STRUCTURE.current_baseline_person_id%TYPE;
1763: l_original_baseline_date PA_PROJ_ELEM_VER_STRUCTURE.original_baseline_date%TYPE;
1764: l_original_flag PA_PROJ_ELEM_VER_STRUCTURE.original_flag%TYPE;
1765: l_original_baseline_person_id PA_PROJ_ELEM_VER_STRUCTURE.original_baseline_person_id%TYPE;
1766: l_lock_status_code PA_PROJ_ELEM_VER_STRUCTURE.lock_status_code%TYPE;
1767: l_locked_by_person_id PA_PROJ_ELEM_VER_STRUCTURE.locked_by_person_id%TYPE;
1768: l_locked_date PA_PROJ_ELEM_VER_STRUCTURE.locked_date%TYPE;
1761: l_current_flag PA_PROJ_ELEM_VER_STRUCTURE.current_flag%TYPE;
1762: l_current_baseline_person_id PA_PROJ_ELEM_VER_STRUCTURE.current_baseline_person_id%TYPE;
1763: l_original_baseline_date PA_PROJ_ELEM_VER_STRUCTURE.original_baseline_date%TYPE;
1764: l_original_flag PA_PROJ_ELEM_VER_STRUCTURE.original_flag%TYPE;
1765: l_original_baseline_person_id PA_PROJ_ELEM_VER_STRUCTURE.original_baseline_person_id%TYPE;
1766: l_lock_status_code PA_PROJ_ELEM_VER_STRUCTURE.lock_status_code%TYPE;
1767: l_locked_by_person_id PA_PROJ_ELEM_VER_STRUCTURE.locked_by_person_id%TYPE;
1768: l_locked_date PA_PROJ_ELEM_VER_STRUCTURE.locked_date%TYPE;
1769: l_status_code PA_PROJ_ELEM_VER_STRUCTURE.status_code%TYPE;
1762: l_current_baseline_person_id PA_PROJ_ELEM_VER_STRUCTURE.current_baseline_person_id%TYPE;
1763: l_original_baseline_date PA_PROJ_ELEM_VER_STRUCTURE.original_baseline_date%TYPE;
1764: l_original_flag PA_PROJ_ELEM_VER_STRUCTURE.original_flag%TYPE;
1765: l_original_baseline_person_id PA_PROJ_ELEM_VER_STRUCTURE.original_baseline_person_id%TYPE;
1766: l_lock_status_code PA_PROJ_ELEM_VER_STRUCTURE.lock_status_code%TYPE;
1767: l_locked_by_person_id PA_PROJ_ELEM_VER_STRUCTURE.locked_by_person_id%TYPE;
1768: l_locked_date PA_PROJ_ELEM_VER_STRUCTURE.locked_date%TYPE;
1769: l_status_code PA_PROJ_ELEM_VER_STRUCTURE.status_code%TYPE;
1770: l_wf_status_code PA_PROJ_ELEM_VER_STRUCTURE.wf_status_code%TYPE;
1763: l_original_baseline_date PA_PROJ_ELEM_VER_STRUCTURE.original_baseline_date%TYPE;
1764: l_original_flag PA_PROJ_ELEM_VER_STRUCTURE.original_flag%TYPE;
1765: l_original_baseline_person_id PA_PROJ_ELEM_VER_STRUCTURE.original_baseline_person_id%TYPE;
1766: l_lock_status_code PA_PROJ_ELEM_VER_STRUCTURE.lock_status_code%TYPE;
1767: l_locked_by_person_id PA_PROJ_ELEM_VER_STRUCTURE.locked_by_person_id%TYPE;
1768: l_locked_date PA_PROJ_ELEM_VER_STRUCTURE.locked_date%TYPE;
1769: l_status_code PA_PROJ_ELEM_VER_STRUCTURE.status_code%TYPE;
1770: l_wf_status_code PA_PROJ_ELEM_VER_STRUCTURE.wf_status_code%TYPE;
1771: l_latest_eff_published_flag PA_PROJ_ELEM_VER_STRUCTURE.latest_eff_published_flag%TYPE;
1764: l_original_flag PA_PROJ_ELEM_VER_STRUCTURE.original_flag%TYPE;
1765: l_original_baseline_person_id PA_PROJ_ELEM_VER_STRUCTURE.original_baseline_person_id%TYPE;
1766: l_lock_status_code PA_PROJ_ELEM_VER_STRUCTURE.lock_status_code%TYPE;
1767: l_locked_by_person_id PA_PROJ_ELEM_VER_STRUCTURE.locked_by_person_id%TYPE;
1768: l_locked_date PA_PROJ_ELEM_VER_STRUCTURE.locked_date%TYPE;
1769: l_status_code PA_PROJ_ELEM_VER_STRUCTURE.status_code%TYPE;
1770: l_wf_status_code PA_PROJ_ELEM_VER_STRUCTURE.wf_status_code%TYPE;
1771: l_latest_eff_published_flag PA_PROJ_ELEM_VER_STRUCTURE.latest_eff_published_flag%TYPE;
1772: l_pm_source_code PA_PROJ_ELEM_VER_STRUCTURE.pm_source_code%TYPE;
1765: l_original_baseline_person_id PA_PROJ_ELEM_VER_STRUCTURE.original_baseline_person_id%TYPE;
1766: l_lock_status_code PA_PROJ_ELEM_VER_STRUCTURE.lock_status_code%TYPE;
1767: l_locked_by_person_id PA_PROJ_ELEM_VER_STRUCTURE.locked_by_person_id%TYPE;
1768: l_locked_date PA_PROJ_ELEM_VER_STRUCTURE.locked_date%TYPE;
1769: l_status_code PA_PROJ_ELEM_VER_STRUCTURE.status_code%TYPE;
1770: l_wf_status_code PA_PROJ_ELEM_VER_STRUCTURE.wf_status_code%TYPE;
1771: l_latest_eff_published_flag PA_PROJ_ELEM_VER_STRUCTURE.latest_eff_published_flag%TYPE;
1772: l_pm_source_code PA_PROJ_ELEM_VER_STRUCTURE.pm_source_code%TYPE;
1773: l_pm_source_reference PA_PROJ_ELEM_VER_STRUCTURE.pm_source_reference%TYPE;
1766: l_lock_status_code PA_PROJ_ELEM_VER_STRUCTURE.lock_status_code%TYPE;
1767: l_locked_by_person_id PA_PROJ_ELEM_VER_STRUCTURE.locked_by_person_id%TYPE;
1768: l_locked_date PA_PROJ_ELEM_VER_STRUCTURE.locked_date%TYPE;
1769: l_status_code PA_PROJ_ELEM_VER_STRUCTURE.status_code%TYPE;
1770: l_wf_status_code PA_PROJ_ELEM_VER_STRUCTURE.wf_status_code%TYPE;
1771: l_latest_eff_published_flag PA_PROJ_ELEM_VER_STRUCTURE.latest_eff_published_flag%TYPE;
1772: l_pm_source_code PA_PROJ_ELEM_VER_STRUCTURE.pm_source_code%TYPE;
1773: l_pm_source_reference PA_PROJ_ELEM_VER_STRUCTURE.pm_source_reference%TYPE;
1774: l_change_reason_code VARCHAR2(30);
1767: l_locked_by_person_id PA_PROJ_ELEM_VER_STRUCTURE.locked_by_person_id%TYPE;
1768: l_locked_date PA_PROJ_ELEM_VER_STRUCTURE.locked_date%TYPE;
1769: l_status_code PA_PROJ_ELEM_VER_STRUCTURE.status_code%TYPE;
1770: l_wf_status_code PA_PROJ_ELEM_VER_STRUCTURE.wf_status_code%TYPE;
1771: l_latest_eff_published_flag PA_PROJ_ELEM_VER_STRUCTURE.latest_eff_published_flag%TYPE;
1772: l_pm_source_code PA_PROJ_ELEM_VER_STRUCTURE.pm_source_code%TYPE;
1773: l_pm_source_reference PA_PROJ_ELEM_VER_STRUCTURE.pm_source_reference%TYPE;
1774: l_change_reason_code VARCHAR2(30);
1775:
1768: l_locked_date PA_PROJ_ELEM_VER_STRUCTURE.locked_date%TYPE;
1769: l_status_code PA_PROJ_ELEM_VER_STRUCTURE.status_code%TYPE;
1770: l_wf_status_code PA_PROJ_ELEM_VER_STRUCTURE.wf_status_code%TYPE;
1771: l_latest_eff_published_flag PA_PROJ_ELEM_VER_STRUCTURE.latest_eff_published_flag%TYPE;
1772: l_pm_source_code PA_PROJ_ELEM_VER_STRUCTURE.pm_source_code%TYPE;
1773: l_pm_source_reference PA_PROJ_ELEM_VER_STRUCTURE.pm_source_reference%TYPE;
1774: l_change_reason_code VARCHAR2(30);
1775:
1776:
1769: l_status_code PA_PROJ_ELEM_VER_STRUCTURE.status_code%TYPE;
1770: l_wf_status_code PA_PROJ_ELEM_VER_STRUCTURE.wf_status_code%TYPE;
1771: l_latest_eff_published_flag PA_PROJ_ELEM_VER_STRUCTURE.latest_eff_published_flag%TYPE;
1772: l_pm_source_code PA_PROJ_ELEM_VER_STRUCTURE.pm_source_code%TYPE;
1773: l_pm_source_reference PA_PROJ_ELEM_VER_STRUCTURE.pm_source_reference%TYPE;
1774: l_change_reason_code VARCHAR2(30);
1775:
1776:
1777: l_return_status VARCHAR2(1);
1814: PM_SOURCE_CODE,
1815: PM_SOURCE_REFERENCE,
1816: CHANGE_REASON_CODE,
1817: CURRENT_WORKING_FLAG
1818: from pa_proj_elem_ver_structure
1819: where pev_structure_id = p_pev_structure_id;
1820:
1821: l_current_working_ver_flag VARCHAR2(1); --FPM bug 3301192
1822:
1824:
1825: cursor cur_relationship_ids(c_structure_version_id NUMBER, c_project_id NUMBER)
1826: is select por.object_relationship_id, por.record_version_number
1827: , por2.object_id_from1, por.object_id_from2, por.comments, ppa.name -- Bug # 4556844.
1828: from pa_object_relationships por, pa_proj_element_versions ppev, pa_proj_elem_ver_structure ppevs
1829: , pa_projects_all ppa, pa_object_relationships por2 -- Bug # 4556844.
1830: where ppevs.element_version_id = ppev.parent_structure_version_id
1831: and ppevs.project_id = ppev.project_id
1832: and ppev.element_version_id = por.object_id_to1
1908: DECLARE
1909: l_rowid VARCHAR2(255);
1910: BEGIN
1911: select rowid into l_rowid
1912: from pa_proj_elem_ver_structure
1913: where pev_structure_id = p_pev_structure_id
1914: and record_version_number = p_record_version_number
1915: for update NOWAIT;
1916: EXCEPTION
1958: l_current_baseline_date := sysdate;
1959: l_current_baseline_person_id := l_person_id;
1960:
1961: --clear other flags
1962: update pa_proj_elem_ver_structure
1963: set current_flag = 'N',
1964: current_baseline_date = NULL,
1965: current_baseline_person_id = NULL,
1966: record_version_number = record_version_number + 1
1996: l_original_baseline_date := sysdate;
1997: l_original_baseline_person_id := l_person_id;
1998:
1999: --clear other flags.
2000: update pa_proj_elem_ver_structure
2001: set original_flag = 'N',
2002: original_baseline_date = NULL,
2003: original_baseline_person_id = NULL,
2004: record_version_number = record_version_number + 1
2114: RAISE FND_API.G_EXC_ERROR;
2115: END IF;
2116: */
2117:
2118: PA_PROJ_ELEM_VER_STRUCTURE_PKG.update_row(
2119: X_ROWID => l_rowid
2120: , X_PEV_STRUCTURE_ID => p_pev_structure_id
2121: , X_ELEMENT_VERSION_ID => l_structure_version_id
2122: , X_VERSION_NUMBER => l_version_number
2148: --FPM bug 3301192
2149: --now set the current working flag for the other working versions to 'N'
2150: IF l_current_working_ver_flag = 'Y'
2151: THEN
2152: UPDATE pa_proj_elem_ver_structure
2153: SET current_working_flag = 'N'
2154: where project_id = l_project_id
2155: and proj_element_id = l_proj_element_id
2156: and pev_structure_id <> p_pev_structure_id
2755: --FP M bug 3301192
2756: CURSOR cur_proj_str
2757: IS
2758: SELECT proj_element_id, project_id
2759: FROM pa_proj_elem_ver_structure
2760: WHERE pev_structure_id = p_pev_structure_id;
2761:
2762: l_structure_id NUMBER;
2763: l_project_id NUMBER;
2768: -- 3804437 Added below cursor to retrieve last updated working version
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
2792: IF (p_validate_only <> FND_API.G_TRUE) THEN
2793: BEGIN
2794: --lock
2795: select rowid into l_rowid
2796: from pa_proj_elem_ver_structure
2797: where pev_structure_id = p_pev_structure_id
2798: and record_version_number = p_record_version_number
2799: for update of record_version_number NOWAIT;
2800: EXCEPTION
2817: END;
2818: ELSE
2819: BEGIN
2820: select rowid into l_rowid
2821: from pa_proj_elem_ver_structure
2822: where pev_structure_id = p_pev_structure_id
2823: and record_version_number = p_record_version_number;
2824: EXCEPTION
2825: when NO_DATA_FOUND then
2858:
2859: -- 3804437 end
2860:
2861: --update the latest updated working version as current working version.
2862: UPDATE pa_proj_elem_ver_structure
2863: SET current_working_flag = 'Y'
2864: WHERE element_version_id = l_current_working_ver_id
2865: AND project_id = l_project_id;
2866:
2865: AND project_id = l_project_id;
2866:
2867: --end FPM bug 3301192
2868:
2869: PA_PROJ_ELEM_VER_STRUCTURE_PKG.delete_row(
2870: X_ROWID => l_rowid
2871: );
2872:
2873: IF (p_debug_mode = 'Y') THEN
2961: l_msg_index_out NUMBER;
2962: l_apps_name VARCHAR2(2000) := 'PA';
2963:
2964: l_dummy VARCHAR2(1);
2965: l_dummy_name PA_PROJ_ELEM_VER_STRUCTURE.NAME%TYPE;
2966:
2967: l_project_id PA_PROJ_ELEM_VER_STRUCTURE.PROJECT_ID%TYPE;
2968: l_proj_element_id PA_PROJ_ELEM_VER_STRUCTURE.PROJ_ELEMENT_ID%TYPE;
2969: l_element_version_id PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE;
2963:
2964: l_dummy VARCHAR2(1);
2965: l_dummy_name PA_PROJ_ELEM_VER_STRUCTURE.NAME%TYPE;
2966:
2967: l_project_id PA_PROJ_ELEM_VER_STRUCTURE.PROJECT_ID%TYPE;
2968: l_proj_element_id PA_PROJ_ELEM_VER_STRUCTURE.PROJ_ELEMENT_ID%TYPE;
2969: l_element_version_id PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE;
2970: l_pev_structure_id PA_PROJ_ELEM_VER_STRUCTURE.PEV_STRUCTURE_ID%TYPE;
2971:
2964: l_dummy VARCHAR2(1);
2965: l_dummy_name PA_PROJ_ELEM_VER_STRUCTURE.NAME%TYPE;
2966:
2967: l_project_id PA_PROJ_ELEM_VER_STRUCTURE.PROJECT_ID%TYPE;
2968: l_proj_element_id PA_PROJ_ELEM_VER_STRUCTURE.PROJ_ELEMENT_ID%TYPE;
2969: l_element_version_id PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE;
2970: l_pev_structure_id PA_PROJ_ELEM_VER_STRUCTURE.PEV_STRUCTURE_ID%TYPE;
2971:
2972:
2965: l_dummy_name PA_PROJ_ELEM_VER_STRUCTURE.NAME%TYPE;
2966:
2967: l_project_id PA_PROJ_ELEM_VER_STRUCTURE.PROJECT_ID%TYPE;
2968: l_proj_element_id PA_PROJ_ELEM_VER_STRUCTURE.PROJ_ELEMENT_ID%TYPE;
2969: l_element_version_id PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE;
2970: l_pev_structure_id PA_PROJ_ELEM_VER_STRUCTURE.PEV_STRUCTURE_ID%TYPE;
2971:
2972:
2973: l_new_struct_ver_id NUMBER;
2966:
2967: l_project_id PA_PROJ_ELEM_VER_STRUCTURE.PROJECT_ID%TYPE;
2968: l_proj_element_id PA_PROJ_ELEM_VER_STRUCTURE.PROJ_ELEMENT_ID%TYPE;
2969: l_element_version_id PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE;
2970: l_pev_structure_id PA_PROJ_ELEM_VER_STRUCTURE.PEV_STRUCTURE_ID%TYPE;
2971:
2972:
2973: l_new_struct_ver_id NUMBER;
2974: -- added for Bug Fix: 4537865
2973: l_new_struct_ver_id NUMBER;
2974: -- added for Bug Fix: 4537865
2975: l_tmp_struct_ver_id NUMBER;
2976: -- added for Bug fix: 4537865
2977: l_new_pev_structure_id PA_PROJ_ELEM_VER_STRUCTURE.PEV_STRUCTURE_ID%TYPE;
2978: l_new_pev_schedule_id PA_PROJ_ELEM_VER_SCHEDULE.PEV_SCHEDULE_ID%TYPE;
2979: l_new_obj_rel_id PA_OBJECT_RELATIONSHIPS.OBJECT_RELATIONSHIP_ID%TYPE;
2980:
2981: l_original_baseline_flag VARCHAR2(1);
2980:
2981: l_original_baseline_flag VARCHAR2(1);
2982: l_current_baseline_flag VARCHAR2(1);
2983:
2984: l_new_struct_ver_name PA_PROJ_ELEM_VER_STRUCTURE.name%TYPE;
2985: l_new_struct_ver_desc PA_PROJ_ELEM_VER_STRUCTURE.description%TYPE;
2986:
2987: -- anlee
2988: -- Dates changes
2981: l_original_baseline_flag VARCHAR2(1);
2982: l_current_baseline_flag VARCHAR2(1);
2983:
2984: l_new_struct_ver_name PA_PROJ_ELEM_VER_STRUCTURE.name%TYPE;
2985: l_new_struct_ver_desc PA_PROJ_ELEM_VER_STRUCTURE.description%TYPE;
2986:
2987: -- anlee
2988: -- Dates changes
2989: l_scheduled_start_date DATE;
3063:
3064: cursor get_struc_ver_name IS
3065: select pevs.name, pevs.project_id, pevs.proj_element_id,
3066: pevs.element_version_id, pevs.pev_structure_id
3067: from pa_proj_elem_ver_structure pevs,
3068: pa_proj_element_versions pev
3069: where pev.element_version_id = p_structure_version_id
3070: and pevs.project_id = pev.project_id
3071: and pevs.element_version_id = pev.element_version_id;
3077: l_struc_ver_rec get_structure_ver_csr%ROWTYPE;
3078:
3079: CURSOR get_structure_ver_attr_csr(c_structure_version_id NUMBER, c_project_id NUMBER) IS
3080: SELECT *
3081: FROM PA_PROJ_ELEM_VER_STRUCTURE
3082: WHERE ELEMENT_VERSION_ID = c_structure_version_id
3083: AND project_id = c_project_id;
3084: l_struc_ver_attr_rec get_structure_ver_attr_csr%ROWTYPE;
3085:
3139: cursor sel_other_structure_ver(c_keep_struc_ver_id NUMBER) IS
3140: select b.element_version_id, b.record_version_number
3141: from pa_proj_element_versions a,
3142: pa_proj_element_versions b,
3143: pa_proj_elem_ver_structure c
3144: where a.element_version_id = c_keep_struc_ver_id
3145: and a.project_id = b.project_id
3146: and a.proj_element_id = b.proj_element_id
3147: and b.element_version_id <> c_keep_struc_ver_id
3492: -- End of Bug Fix 4727737
3493:
3494: -- error_msg('dummy_name after'||l_dummy_name);
3495: -- update_name
3496: update PA_PROJ_ELEM_VER_STRUCTURE
3497: set name = l_dummy_name,
3498: current_working_flag = 'Y'
3499: where pev_structure_id = l_pev_structure_id;
3500:
5042: OPEN get_all_new_childs(l_new_struct_ver_id);
5043: FETCH get_all_new_childs bulk collect into l_task_ver_ids_tbl;
5044: CLOSE get_all_new_childs;
5045:
5046: update pa_proj_elem_ver_structure
5047: set status_code = 'STRUCTURE_WORKING',
5048: LOCKED_BY_PERSON_ID = (select locked_by_person_id
5049: from pa_proj_elem_ver_structure
5050: where project_id = l_project_id
5045:
5046: update pa_proj_elem_ver_structure
5047: set status_code = 'STRUCTURE_WORKING',
5048: LOCKED_BY_PERSON_ID = (select locked_by_person_id
5049: from pa_proj_elem_ver_structure
5050: where project_id = l_project_id
5051: and element_version_id = p_structure_version_id),
5052: LOCK_STATUS_CODE = 'LOCKED'
5053: where project_id = l_project_id and element_version_id = l_new_struct_ver_id;
5311: --bug 4019845
5312: /*
5313: --bug 3830932
5314: --moving before copy project dates so that latest structure version will be selected in the API
5315: update pa_proj_elem_ver_structure
5316: set status_code = 'STRUCTURE_PUBLISHED',
5317: LOCKED_BY_PERSON_ID = NULL,
5318: LOCK_STATUS_CODE = 'UNLOCKED'
5319: where project_id = l_project_id and element_version_id = l_new_struct_ver_id;
5464: --end changes for task status
5465: */
5466:
5467: --Change the status of the working version to 'STRUCTURE_WORKING'
5468: update PA_PROJ_ELEM_VER_STRUCTURE
5469: set status_code = 'STRUCTURE_WORKING',
5470: record_version_number = nvl(record_version_number,0)+1
5471: where pev_structure_id = l_pev_structure_id;
5472:
5956:
5957: cursor get_latest_struc_ver(c_struc_ver_id NUMBER) IS
5958: select pevs.element_version_id
5959: from pa_proj_element_versions pev,
5960: pa_proj_elem_ver_structure pevs
5961: where pev.element_version_id = c_struc_ver_id
5962: and pev.project_id = pevs.project_id
5963: and pev.proj_element_id = pevs.proj_element_id
5964: and pevs.latest_eff_published_flag = 'Y';
5967: select pev2.element_version_id task_version_id,
5968: pev2.parent_structure_version_id parent_structure_version_id
5969: from pa_proj_element_versions pev,
5970: pa_proj_element_versions pev1,
5971: pa_proj_elem_ver_structure pevs,
5972: pa_proj_element_versions pev2
5973: where pev.element_version_id = c_task_ver_id
5974: and pev.parent_structure_version_id = pev1.element_version_id
5975: and pev1.project_id = pevs.project_id
5980: and pev2.parent_structure_version_id = pevs.element_version_id;
5981:
5982: cursor can_update(c_element_version_id NUMBER) IS
5983: select '1'
5984: from pa_proj_elem_ver_structure pevs,
5985: pa_proj_element_versions pev
5986: where pev.element_version_id = c_element_version_id
5987: and pev.parent_structure_version_id = pevs.element_version_id
5988: and pev.project_id = pevs.project_id
6202:
6203: CURSOR l_get_structure_ver_attr_csr(c_structure_version_id NUMBER)
6204: IS
6205: SELECT a.*
6206: FROM PA_PROJ_ELEM_VER_STRUCTURE a,
6207: PA_PROJ_ELEMENT_VERSIONS b
6208: WHERE b.element_version_id = c_structure_version_id
6209: AND b.project_id = a.project_id
6210: AND b.element_version_id = a.project_id;
6280: WHERE ppev.element_version_id = c_structure_version_id
6281: AND EXISTS
6282: (SELECT 'Y'
6283: FROM PA_PROJ_ELEMENT_VERSIONS ppev2,
6284: PA_PROJ_ELEM_VER_STRUCTURE ppevs
6285: WHERE ppev2.proj_element_id = ppev.proj_element_id
6286: AND ppev2.project_id = ppev.project_id
6287: AND ppevs.project_id = ppev2.project_id
6288: AND ppevs.element_version_id = ppev2.element_version_id
7071: --This cursor will either get all the working versions or the latest published version
7072: -- CURSOR l_get_structure_versions_csr(c_project_id NUMBER, c_structure_id NUMBER, c_pub_status VARCHAR2)
7073: -- IS
7074: -- SELECT b.element_version_id
7075: -- FROM PA_PROJ_ELEM_VER_STRUCTURE b
7076: -- WHERE b.proj_element_id = c_structure_id
7077: -- AND b.project_id = c_project_id
7078: -- AND (b.STATUS_CODE = 'STRUCTURE_PUBLISHED' AND b.LATEST_EFF_PUBLISHED_FLAG = 'Y' AND c_pub_status = 'Y')
7079: -- UNION
7077: -- AND b.project_id = c_project_id
7078: -- AND (b.STATUS_CODE = 'STRUCTURE_PUBLISHED' AND b.LATEST_EFF_PUBLISHED_FLAG = 'Y' AND c_pub_status = 'Y')
7079: -- UNION
7080: -- SELECT b.element_version_id
7081: -- FROM PA_PROJ_ELEM_VER_STRUCTURE b
7082: -- WHERE b.proj_element_id = c_structure_id
7083: -- AND b.project_id = c_project_id
7084: -- AND (b.STATUS_CODE <> 'STRUCTURE_PUBLISHED' AND c_pub_status = 'N');
7085:
7098: --commented by hsiu for advanced structure changes
7099: -- CURSOR l_get_structure_ver_attr_csr(c_structure_version_id NUMBER, c_pub_status VARCHAR2)
7100: -- IS
7101: -- SELECT a.*
7102: -- FROM PA_PROJ_ELEM_VER_STRUCTURE a,
7103: -- PA_PROJ_ELEMENT_VERSIONS b
7104: -- WHERE b.element_version_id = c_structure_version_id
7105: -- AND b.element_version_id = a.element_version_id
7106: -- AND b.project_id = a.project_id
7106: -- AND b.project_id = a.project_id
7107: -- AND (a.STATUS_CODE <> 'STRUCTURE_PUBLISHED' AND c_pub_status = 'N')
7108: -- UNION
7109: -- SELECT a.*
7110: -- FROM PA_PROJ_ELEM_VER_STRUCTURE a,
7111: -- PA_PROJ_ELEMENT_VERSIONS b
7112: -- WHERE b.element_version_id = c_structure_version_id
7113: -- AND b.element_version_id = a.element_version_id
7114: -- AND b.project_id = a.project_id
7116:
7117: CURSOR l_get_structure_ver_attr_csr(c_structure_version_id NUMBER)
7118: IS
7119: SELECT a.*
7120: FROM PA_PROJ_ELEM_VER_STRUCTURE a,
7121: PA_PROJ_ELEMENT_VERSIONS b
7122: WHERE b.element_version_id = c_structure_version_id
7123: AND b.element_version_id = a.element_version_id
7124: AND b.proj_element_id = a.proj_element_id
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
7296: --return the first selected row in this API.
7297: AND b.element_version_id = PA_PROJECT_STRUCTURE_UTILS.GET_LAST_UPDATED_WORKING_VER(c_structure_id)
7298: -- AND b.last_update_date = (
7299: -- SELECT MAX(c.last_update_date)
7300: -- FROM pa_proj_elem_ver_structure c
7301: -- WHERE c.project_id = a.project_id
7302: -- AND c.proj_element_id = a.proj_element_id
7303: -- AND c.status_code <> 'STRUCTURE_PUBLISHED')
7304: AND 1 = (c_option)
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
8815: and wp_auto_publish_flag = 'Y';
8816:
8817: CURSOR get_wp_info IS
8818: select name, description
8819: from pa_proj_elem_ver_structure
8820: where project_Id = p_project_id
8821: and element_version_id = p_structure_version_id;
8822:
8823: /* Bug 2683138 */
9010: END LOOP;
9011: CLOSE sel_other_structure_ver;
9012: */
9013: --Update to submit status
9014: UPDATE PA_PROJ_ELEM_VER_STRUCTURE
9015: set status_code = 'STRUCTURE_SUBMITTED',
9016: lock_status_code = 'UNLOCKED',
9017: locked_by_person_id = NULL,
9018: locked_date = NULL
9155:
9156: --
9157: BEGIN
9158: select pev_structure_id into l_pev_struc_id
9159: from pa_proj_elem_ver_structure
9160: where project_id = p_project_id
9161: and element_version_id = p_structure_version_id
9162: and record_version_number = p_record_version_number
9163: for update of record_version_number NOWAIT;
9201: END IF;
9202: CLOSE get_status_code;
9203: --end validation
9204:
9205: update pa_proj_elem_ver_structure
9206: set status_code = p_status_code,
9207: record_version_number = record_version_number + 1
9208: where pev_structure_id = l_pev_struc_id;
9209:
9541: and b.structure_type_class_code = 'WORKPLAN';
9542:
9543: cursor sel_latest_pub_ver(c_structure_id NUMBER) IS
9544: select element_version_id
9545: from pa_proj_elem_ver_structure
9546: where proj_element_id = c_structure_id
9547: and project_id = p_project_id
9548: and status_code = 'STRUCTURE_PUBLISHED'
9549: and LATEST_EFF_PUBLISHED_FLAG = 'Y';
9578: and b.proj_element_id = c_struc_id;
9579:
9580: cursor sel_struc_ver_attr_rvn(c_struc_ver_id NUMBER) IS
9581: select PEV_STRUCTURE_ID, record_version_number
9582: from pa_proj_elem_ver_structure
9583: where project_id = p_project_id
9584: and element_version_id = c_struc_ver_id;
9585:
9586: cursor sel_proj_workplan_attr(c_struc_id NUMBER) is
10009: l_effective_date := sysdate;
10010: END IF;
10011:
10012: --Change status
10013: UPDATE pa_proj_elem_ver_structure
10014: set status_code = l_status_code,
10015: current_flag = 'N',
10016: current_baseline_date = NULL,
10017: current_baseline_person_id = NULL,
10462: CLOSE sel_struc_ver_attr_rvn;
10463:
10464: --bug 3010538
10465: --added process_update_wbs_flag
10466: UPDATE pa_proj_elem_ver_structure
10467: set status_code = l_status_code,
10468: current_flag = l_baseline_flag,
10469: current_baseline_date = l_effective_date,
10470: current_baseline_person_id = NULL,
10701: l_effective_date := sysdate;
10702: END IF;
10703:
10704: --Change status
10705: UPDATE pa_proj_elem_ver_structure
10706: set status_code = l_status_code,
10707: current_flag = 'N',
10708: current_baseline_date = NULL,
10709: current_baseline_person_id = NULL,
10959: --end bug 3125813
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
10976: and b.proj_element_id = p_proj_element_id;
10977:
10978: cursor sel_struc_ver_attr_rvn(c_struc_ver_id NUMBER) IS
10979: select a.PEV_STRUCTURE_ID, a.record_version_number
10980: from pa_proj_elem_ver_structure a,
10981: pa_proj_element_versions b
10982: where b.project_id = a.project_id
10983: and b.element_version_id = c_struc_ver_id
10984: and a.element_version_id = b.element_version_id;
11135: FETCH get_curr_wp_flag into l_curr_wp_ver_flag;
11136: CLOSE get_curr_wp_flag;
11137:
11138: IF ( l_curr_wp_ver_flag ='N' AND l_template_flag = 'N' ) THEN
11139: UPDATE pa_proj_elem_ver_structure
11140: SET current_working_flag = 'N'
11141: WHERE element_version_id = l_struc_ver_id
11142: and project_id = l_project_id;
11143: END IF;
11206: CLOSE sel_struc_ver_attr_rvn;
11207:
11208: --set status to latest published and baselined
11209:
11210: UPDATE pa_proj_elem_ver_structure
11211: set status_code = 'STRUCTURE_PUBLISHED',
11212: published_date = sysdate,
11213: current_flag = 'Y',
11214: current_baseline_date = sysdate,
11291: l_opt VARCHAR2(1);
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'
11306: and ppe.project_id = p_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'
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
11913: pev.proj_element_id = pe.proj_element_id and
11914: pev.project_id = pevs.project_id and
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;
11944: l_pev_schedule_id PA_PROJ_ELEM_VER_SCHEDULE.PEV_SCHEDULE_ID%TYPE;
11945: l_pevsh_rvn PA_PROJ_ELEM_VER_SCHEDULE.RECORD_VERSION_NUMBER%TYPE;
11946: l_pevsh_rowid VARCHAR2(255);
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;
11944: l_pev_schedule_id PA_PROJ_ELEM_VER_SCHEDULE.PEV_SCHEDULE_ID%TYPE;
11945: l_pevsh_rvn PA_PROJ_ELEM_VER_SCHEDULE.RECORD_VERSION_NUMBER%TYPE;
11946: l_pevsh_rowid VARCHAR2(255);
11947:
12265: IS PRAGMA AUTONOMOUS_TRANSACTION;
12266: CURSOR get_struct_ver_info IS
12267: select a.name, a.version_number, c.scheduled_start_date,
12268: c.scheduled_finish_date, b.project_id
12269: from pa_proj_elem_ver_structure a,
12270: pa_proj_element_versions b,
12271: pa_proj_elem_ver_schedule c
12272: where b.element_version_id = p_structure_version_id
12273: and b.project_id = a.project_id
12924:
12925: CURSOR l_get_structure_ver_attr_csr(c_structure_version_id NUMBER)
12926: IS
12927: SELECT a.*
12928: FROM PA_PROJ_ELEM_VER_STRUCTURE a,
12929: PA_PROJ_ELEMENT_VERSIONS b
12930: WHERE b.element_version_id = c_structure_version_id
12931: AND b.project_id = a.project_id
12932: AND b.element_version_id = a.project_id;
12970: WHERE ppev.element_version_id = c_structure_version_id
12971: AND EXISTS
12972: (SELECT 'Y'
12973: FROM PA_PROJ_ELEMENT_VERSIONS ppev2,
12974: PA_PROJ_ELEM_VER_STRUCTURE ppevs
12975: WHERE ppev2.proj_element_id = ppev.proj_element_id
12976: AND ppev2.project_id = ppev.project_id
12977: AND ppevs.project_id = ppev2.project_id
12978: AND ppevs.element_version_id = ppev2.element_version_id
13734:
13735: --Bug No 3634334
13736: --Commented the following query for performance reason and rewritten the same.
13737: --Getting the project_id from pa_proj_element_versions table instead of getting
13738: --it from pa_proj_elem_ver_structure table, in this optimizer will be using the
13739: --unique index on element_version_id and functionally the process is fetching
13740: --project_id
13741: /* CURSOR get_proj_id(cp_structure_version_id NUMBER) IS
13742: SELECT project_id
13739: --unique index on element_version_id and functionally the process is fetching
13740: --project_id
13741: /* CURSOR get_proj_id(cp_structure_version_id NUMBER) IS
13742: SELECT project_id
13743: FROM pa_proj_elem_ver_structure
13744: WHERE element_version_id = cp_structure_version_id;*/
13745:
13746: CURSOR get_proj_id(cp_structure_version_id NUMBER) IS
13747: SELECT project_id
13766: OPEN get_proj_id(p_structure_version_id);
13767: FETCH get_proj_id INTO l_proj_id;
13768: CLOSE get_proj_id;
13769: --
13770: UPDATE pa_proj_elem_ver_structure
13771: SET SCHEDULE_DIRTY_FLAG = l_dirty_flag
13772: WHERE element_version_id = p_structure_version_id
13773: AND project_id = l_proj_id;
13774: ELSE
13771: SET SCHEDULE_DIRTY_FLAG = l_dirty_flag
13772: WHERE element_version_id = p_structure_version_id
13773: AND project_id = l_proj_id;
13774: ELSE
13775: UPDATE pa_proj_elem_ver_structure
13776: SET SCHEDULE_DIRTY_FLAG = l_dirty_flag
13777: WHERE element_version_id = p_structure_version_id
13778: AND project_id = p_project_id;
13779: END IF;
13900: and b.structure_type_class_code = 'WORKPLAN';
13901:
13902: cursor sel_latest_pub_ver(c_structure_id NUMBER) IS
13903: select element_version_id
13904: from pa_proj_elem_ver_structure
13905: where proj_element_id = c_structure_id
13906: and project_id = p_project_id
13907: and status_code = 'STRUCTURE_PUBLISHED'
13908: and LATEST_EFF_PUBLISHED_FLAG = 'Y';
13940: and b.proj_element_id = c_struc_id;
13941:
13942: cursor sel_struc_ver_attr_rvn(c_struc_ver_id NUMBER) IS
13943: select PEV_STRUCTURE_ID, record_version_number
13944: from pa_proj_elem_ver_structure
13945: where project_id = p_project_id
13946: and element_version_id = c_struc_ver_id;
13947:
13948: cursor sel_proj_workplan_attr(c_struc_id NUMBER) is
14089: from pa_object_relationships por1
14090: , pa_object_relationships por2
14091: , pa_projects_all ppa
14092: , pa_proj_element_versions ppev
14093: , pa_proj_elem_ver_structure ppevs
14094: where por1.object_id_to2 = c_project_id
14095: and por1.relationship_type in ('LW', 'LF')
14096: and por1.object_id_from1 = por2.object_id_to1
14097: and por2.relationship_type = 'S'
14100: and por2.object_id_from1 = ppev.element_version_id
14101: -- Bug Fix 4868867
14102: -- Ram Namburi
14103: -- adding the following additional project id join to avoid the Full Table Scan on
14104: -- pa_proj_elem_ver_structure table and to use N1.
14105: and ppev.project_id = ppevs.project_id
14106: and ppev.parent_structure_version_id = ppevs.element_version_id
14107: and ppevs.current_working_flag = 'Y'
14108: union
14117: from pa_object_relationships por1
14118: , pa_object_relationships por2
14119: , pa_projects_all ppa
14120: , pa_proj_element_versions ppev
14121: , pa_proj_elem_ver_structure ppevs
14122: where por1.object_id_from2 = c_project_id
14123: and por1.relationship_type in ('LW', 'LF')
14124: and por1.object_id_from1 = por2.object_id_to1
14125: and por2.relationship_type = 'S'
14128: and por2.object_id_from1 = ppev.element_version_id
14129: -- Bug Fix 4868867
14130: -- Ram Namburi
14131: -- adding the following additional project id join to avoid the Full Table Scan on
14132: -- pa_proj_elem_ver_structure table and to use N1.
14133: and ppev.project_id = ppevs.project_id
14134: and ppev.parent_structure_version_id = ppevs.element_version_id
14135: and ppevs.current_working_flag = 'Y';
14136:
15242: ,x_msg_data => x_msg_data);
15243:
15244: -- Set the process update flag for the source structure version to 'Y'.
15245:
15246: update pa_proj_elem_ver_structure ppevs
15247: set ppevs.process_update_wbs_flag = 'Y'
15248: where ppevs.element_version_id = l_cur_links_rec.src_str_ver_id
15249: and ppevs.project_id = l_cur_links_rec.src_proj_id;
15250:
15735: ,x_msg_data => x_msg_data);
15736:
15737: -- Set the process update flag for the source structure version to 'Y'.
15738:
15739: update pa_proj_elem_ver_structure ppevs
15740: set ppevs.process_update_wbs_flag = 'Y'
15741: where ppevs.element_version_id = l_cur_links_rec.src_str_ver_id
15742: and ppevs.project_id = l_cur_links_rec.src_proj_id;
15743:
16151: where proj_element_id = l_structure_id;
16152:
16153: IF (l_template_flag <> 'Y') THEN
16154: --project
16155: update pa_proj_elem_ver_structure
16156: set status_code = 'STRUCTURE_PUBLISHED',
16157: latest_eff_published_flag = 'Y',
16158: published_date = sysdate
16159: where project_id = p_project_id
16617: where proj_element_id = l_structure_id;
16618:
16619: IF (l_template_flag <> 'Y') THEN
16620: --project
16621: update pa_proj_elem_ver_structure
16622: set status_code = 'STRUCTURE_PUBLISHED',
16623: latest_eff_published_flag = 'Y',
16624: published_date = sysdate
16625: where project_id = p_project_id
17154: -- If the project is version disabled, then baseline the structure version.
17155:
17156: if (PA_PROJ_TASK_STRUC_PUB.IS_WP_VERSIONING_ENABLED(p_project_id) = 'N') then
17157:
17158: update pa_proj_elem_ver_structure ppevs
17159: set ppevs.current_flag = 'Y'
17160: , ppevs.original_flag = 'Y'
17161: , ppevs.record_version_number = (ppevs.record_version_number+1)
17162: where ppevs.project_id = p_project_id
17545: -- If the project is version disabled, then baseline the structure version.
17546:
17547: if (PA_PROJ_TASK_STRUC_PUB.IS_WP_VERSIONING_ENABLED(p_project_id) = 'N') then
17548:
17549: update pa_proj_elem_ver_structure ppevs
17550: set ppevs.current_flag = 'Y'
17551: , ppevs.original_flag = 'Y'
17552: , ppevs.record_version_number = (ppevs.record_version_number+1)
17553: where ppevs.project_id = p_project_id