[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
2133: RAISE FND_API.G_EXC_ERROR;
2134: END IF;
2135: */
2136:
2137: PA_PROJ_ELEM_VER_STRUCTURE_PKG.update_row(
2138: X_ROWID => l_rowid
2139: , X_PEV_STRUCTURE_ID => p_pev_structure_id
2140: , X_ELEMENT_VERSION_ID => l_structure_version_id
2141: , X_VERSION_NUMBER => l_version_number
2167: --FPM bug 3301192
2168: --now set the current working flag for the other working versions to 'N'
2169: IF l_current_working_ver_flag = 'Y'
2170: THEN
2171: UPDATE pa_proj_elem_ver_structure
2172: SET current_working_flag = 'N'
2173: where project_id = l_project_id
2174: and proj_element_id = l_proj_element_id
2175: and pev_structure_id <> p_pev_structure_id
2774: --FP M bug 3301192
2775: CURSOR cur_proj_str
2776: IS
2777: SELECT proj_element_id, project_id
2778: FROM pa_proj_elem_ver_structure
2779: WHERE pev_structure_id = p_pev_structure_id;
2780:
2781: l_structure_id NUMBER;
2782: l_project_id NUMBER;
2787: -- 3804437 Added below cursor to retrieve last updated working version
2788: CURSOR cur_last_working_ver( p_structure_id NUMBER )
2789: IS
2790: select str.element_version_id
2791: from pa_proj_elem_ver_structure str,
2792: pa_proj_elements ppe
2793: where ppe.proj_element_id = p_structure_id
2794: and ppe.project_id = str.project_id
2795: and ppe.proj_element_id = str.proj_element_id
2811: IF (p_validate_only <> FND_API.G_TRUE) THEN
2812: BEGIN
2813: --lock
2814: select rowid into l_rowid
2815: from pa_proj_elem_ver_structure
2816: where pev_structure_id = p_pev_structure_id
2817: and record_version_number = p_record_version_number
2818: for update of record_version_number NOWAIT;
2819: EXCEPTION
2836: END;
2837: ELSE
2838: BEGIN
2839: select rowid into l_rowid
2840: from pa_proj_elem_ver_structure
2841: where pev_structure_id = p_pev_structure_id
2842: and record_version_number = p_record_version_number;
2843: EXCEPTION
2844: when NO_DATA_FOUND then
2877:
2878: -- 3804437 end
2879:
2880: --update the latest updated working version as current working version.
2881: UPDATE pa_proj_elem_ver_structure
2882: SET current_working_flag = 'Y'
2883: WHERE element_version_id = l_current_working_ver_id
2884: AND project_id = l_project_id;
2885:
2886: --end FPM bug 3301192
2887:
2888: /* Added for bug 8708651 */
2889: if PJI_PA_DEL_MAIN.g_from_conc is null then
2890: PA_PROJ_ELEM_VER_STRUCTURE_PKG.delete_row(
2891: X_ROWID => l_rowid
2892: );
2893: else
2894: update PA_PROJ_ELEM_VER_STRUCTURE
2890: PA_PROJ_ELEM_VER_STRUCTURE_PKG.delete_row(
2891: X_ROWID => l_rowid
2892: );
2893: else
2894: update PA_PROJ_ELEM_VER_STRUCTURE
2895: set PURGED_FLAG = 'Y',
2896: last_update_date = sysdate,
2897: last_updated_by = FND_GLOBAL.USER_ID,
2898: conc_request_id = FND_GLOBAL.CONC_REQUEST_ID /* Added for bug 9049425 */
2991: l_msg_index_out NUMBER;
2992: l_apps_name VARCHAR2(2000) := 'PA';
2993:
2994: l_dummy VARCHAR2(1);
2995: l_dummy_name PA_PROJ_ELEM_VER_STRUCTURE.NAME%TYPE;
2996:
2997: l_project_id PA_PROJ_ELEM_VER_STRUCTURE.PROJECT_ID%TYPE;
2998: l_proj_element_id PA_PROJ_ELEM_VER_STRUCTURE.PROJ_ELEMENT_ID%TYPE;
2999: l_element_version_id PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE;
2993:
2994: l_dummy VARCHAR2(1);
2995: l_dummy_name PA_PROJ_ELEM_VER_STRUCTURE.NAME%TYPE;
2996:
2997: l_project_id PA_PROJ_ELEM_VER_STRUCTURE.PROJECT_ID%TYPE;
2998: l_proj_element_id PA_PROJ_ELEM_VER_STRUCTURE.PROJ_ELEMENT_ID%TYPE;
2999: l_element_version_id PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE;
3000: l_pev_structure_id PA_PROJ_ELEM_VER_STRUCTURE.PEV_STRUCTURE_ID%TYPE;
3001:
2994: l_dummy VARCHAR2(1);
2995: l_dummy_name PA_PROJ_ELEM_VER_STRUCTURE.NAME%TYPE;
2996:
2997: l_project_id PA_PROJ_ELEM_VER_STRUCTURE.PROJECT_ID%TYPE;
2998: l_proj_element_id PA_PROJ_ELEM_VER_STRUCTURE.PROJ_ELEMENT_ID%TYPE;
2999: l_element_version_id PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE;
3000: l_pev_structure_id PA_PROJ_ELEM_VER_STRUCTURE.PEV_STRUCTURE_ID%TYPE;
3001:
3002:
2995: l_dummy_name PA_PROJ_ELEM_VER_STRUCTURE.NAME%TYPE;
2996:
2997: l_project_id PA_PROJ_ELEM_VER_STRUCTURE.PROJECT_ID%TYPE;
2998: l_proj_element_id PA_PROJ_ELEM_VER_STRUCTURE.PROJ_ELEMENT_ID%TYPE;
2999: l_element_version_id PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE;
3000: l_pev_structure_id PA_PROJ_ELEM_VER_STRUCTURE.PEV_STRUCTURE_ID%TYPE;
3001:
3002:
3003: l_new_struct_ver_id NUMBER;
2996:
2997: l_project_id PA_PROJ_ELEM_VER_STRUCTURE.PROJECT_ID%TYPE;
2998: l_proj_element_id PA_PROJ_ELEM_VER_STRUCTURE.PROJ_ELEMENT_ID%TYPE;
2999: l_element_version_id PA_PROJ_ELEM_VER_STRUCTURE.ELEMENT_VERSION_ID%TYPE;
3000: l_pev_structure_id PA_PROJ_ELEM_VER_STRUCTURE.PEV_STRUCTURE_ID%TYPE;
3001:
3002:
3003: l_new_struct_ver_id NUMBER;
3004: -- added for Bug Fix: 4537865
3003: l_new_struct_ver_id NUMBER;
3004: -- added for Bug Fix: 4537865
3005: l_tmp_struct_ver_id NUMBER;
3006: -- added for Bug fix: 4537865
3007: l_new_pev_structure_id PA_PROJ_ELEM_VER_STRUCTURE.PEV_STRUCTURE_ID%TYPE;
3008: l_new_pev_schedule_id PA_PROJ_ELEM_VER_SCHEDULE.PEV_SCHEDULE_ID%TYPE;
3009: l_new_obj_rel_id PA_OBJECT_RELATIONSHIPS.OBJECT_RELATIONSHIP_ID%TYPE;
3010:
3011: l_original_baseline_flag VARCHAR2(1);
3010:
3011: l_original_baseline_flag VARCHAR2(1);
3012: l_current_baseline_flag VARCHAR2(1);
3013:
3014: l_new_struct_ver_name PA_PROJ_ELEM_VER_STRUCTURE.name%TYPE;
3015: l_new_struct_ver_desc PA_PROJ_ELEM_VER_STRUCTURE.description%TYPE;
3016:
3017: -- anlee
3018: -- Dates changes
3011: l_original_baseline_flag VARCHAR2(1);
3012: l_current_baseline_flag VARCHAR2(1);
3013:
3014: l_new_struct_ver_name PA_PROJ_ELEM_VER_STRUCTURE.name%TYPE;
3015: l_new_struct_ver_desc PA_PROJ_ELEM_VER_STRUCTURE.description%TYPE;
3016:
3017: -- anlee
3018: -- Dates changes
3019: l_scheduled_start_date DATE;
3093:
3094: cursor get_struc_ver_name IS
3095: select pevs.name, pevs.project_id, pevs.proj_element_id,
3096: pevs.element_version_id, pevs.pev_structure_id
3097: from pa_proj_elem_ver_structure pevs,
3098: pa_proj_element_versions pev
3099: where pev.element_version_id = p_structure_version_id
3100: and pevs.project_id = pev.project_id
3101: and pevs.element_version_id = pev.element_version_id;
3107: l_struc_ver_rec get_structure_ver_csr%ROWTYPE;
3108:
3109: CURSOR get_structure_ver_attr_csr(c_structure_version_id NUMBER, c_project_id NUMBER) IS
3110: SELECT *
3111: FROM PA_PROJ_ELEM_VER_STRUCTURE
3112: WHERE ELEMENT_VERSION_ID = c_structure_version_id
3113: AND project_id = c_project_id;
3114: l_struc_ver_attr_rec get_structure_ver_attr_csr%ROWTYPE;
3115:
3169: cursor sel_other_structure_ver(c_keep_struc_ver_id NUMBER) IS
3170: select b.element_version_id, b.record_version_number
3171: from pa_proj_element_versions a,
3172: pa_proj_element_versions b,
3173: pa_proj_elem_ver_structure c
3174: where a.element_version_id = c_keep_struc_ver_id
3175: and a.project_id = b.project_id
3176: and a.proj_element_id = b.proj_element_id
3177: and b.element_version_id <> c_keep_struc_ver_id
3287:
3288: l_debug_mode VARCHAR2(1);
3289:
3290: -- 9072357
3291: l_structure_id pa_proj_elem_ver_structure.proj_element_id%TYPE;
3292:
3293: CURSOR get_structure_id(c_structure_version_id pa_proj_elem_ver_structure.element_version_id%TYPE)
3294: IS
3295: SELECT proj_element_id
3289:
3290: -- 9072357
3291: l_structure_id pa_proj_elem_ver_structure.proj_element_id%TYPE;
3292:
3293: CURSOR get_structure_id(c_structure_version_id pa_proj_elem_ver_structure.element_version_id%TYPE)
3294: IS
3295: SELECT proj_element_id
3296: FROM pa_proj_elem_ver_structure
3297: WHERE element_version_id = c_structure_version_id;
3292:
3293: CURSOR get_structure_id(c_structure_version_id pa_proj_elem_ver_structure.element_version_id%TYPE)
3294: IS
3295: SELECT proj_element_id
3296: FROM pa_proj_elem_ver_structure
3297: WHERE element_version_id = c_structure_version_id;
3298:
3299: BEGIN
3300:
3552: -- End of Bug Fix 4727737
3553:
3554: -- error_msg('dummy_name after'||l_dummy_name);
3555: -- update_name
3556: update PA_PROJ_ELEM_VER_STRUCTURE
3557: set name = l_dummy_name,
3558: current_working_flag = 'Y'
3559: where pev_structure_id = l_pev_structure_id;
3560:
5102: OPEN get_all_new_childs(l_new_struct_ver_id);
5103: FETCH get_all_new_childs bulk collect into l_task_ver_ids_tbl;
5104: CLOSE get_all_new_childs;
5105:
5106: update pa_proj_elem_ver_structure
5107: set status_code = 'STRUCTURE_WORKING',
5108: LOCKED_BY_PERSON_ID = (select locked_by_person_id
5109: from pa_proj_elem_ver_structure
5110: where project_id = l_project_id
5105:
5106: update pa_proj_elem_ver_structure
5107: set status_code = 'STRUCTURE_WORKING',
5108: LOCKED_BY_PERSON_ID = (select locked_by_person_id
5109: from pa_proj_elem_ver_structure
5110: where project_id = l_project_id
5111: and element_version_id = p_structure_version_id),
5112: LOCK_STATUS_CODE = 'LOCKED'
5113: where project_id = l_project_id and element_version_id = l_new_struct_ver_id;
5371: --bug 4019845
5372: /*
5373: --bug 3830932
5374: --moving before copy project dates so that latest structure version will be selected in the API
5375: update pa_proj_elem_ver_structure
5376: set status_code = 'STRUCTURE_PUBLISHED',
5377: LOCKED_BY_PERSON_ID = NULL,
5378: LOCK_STATUS_CODE = 'UNLOCKED'
5379: where project_id = l_project_id and element_version_id = l_new_struct_ver_id;
5524: --end changes for task status
5525: */
5526:
5527: --Change the status of the working version to 'STRUCTURE_WORKING'
5528: update PA_PROJ_ELEM_VER_STRUCTURE
5529: set status_code = 'STRUCTURE_WORKING',
5530: record_version_number = nvl(record_version_number,0)+1
5531: where pev_structure_id = l_pev_structure_id;
5532:
6016:
6017: cursor get_latest_struc_ver(c_struc_ver_id NUMBER) IS
6018: select pevs.element_version_id
6019: from pa_proj_element_versions pev,
6020: pa_proj_elem_ver_structure pevs
6021: where pev.element_version_id = c_struc_ver_id
6022: and pev.project_id = pevs.project_id
6023: and pev.proj_element_id = pevs.proj_element_id
6024: and pevs.latest_eff_published_flag = 'Y';
6027: select pev2.element_version_id task_version_id,
6028: pev2.parent_structure_version_id parent_structure_version_id
6029: from pa_proj_element_versions pev,
6030: pa_proj_element_versions pev1,
6031: pa_proj_elem_ver_structure pevs,
6032: pa_proj_element_versions pev2
6033: where pev.element_version_id = c_task_ver_id
6034: and pev.parent_structure_version_id = pev1.element_version_id
6035: and pev1.project_id = pevs.project_id
6040: and pev2.parent_structure_version_id = pevs.element_version_id;
6041:
6042: cursor can_update(c_element_version_id NUMBER) IS
6043: select '1'
6044: from pa_proj_elem_ver_structure pevs,
6045: pa_proj_element_versions pev
6046: where pev.element_version_id = c_element_version_id
6047: and pev.parent_structure_version_id = pevs.element_version_id
6048: and pev.project_id = pevs.project_id
6262:
6263: CURSOR l_get_structure_ver_attr_csr(c_structure_version_id NUMBER)
6264: IS
6265: SELECT a.*
6266: FROM PA_PROJ_ELEM_VER_STRUCTURE a,
6267: PA_PROJ_ELEMENT_VERSIONS b
6268: WHERE b.element_version_id = c_structure_version_id
6269: AND b.project_id = a.project_id
6270: AND b.element_version_id = a.project_id;
6340: WHERE ppev.element_version_id = c_structure_version_id
6341: AND EXISTS
6342: (SELECT 'Y'
6343: FROM PA_PROJ_ELEMENT_VERSIONS ppev2,
6344: PA_PROJ_ELEM_VER_STRUCTURE ppevs
6345: WHERE ppev2.proj_element_id = ppev.proj_element_id
6346: AND ppev2.project_id = ppev.project_id
6347: AND ppevs.project_id = ppev2.project_id
6348: AND ppevs.element_version_id = ppev2.element_version_id
7131: --This cursor will either get all the working versions or the latest published version
7132: -- CURSOR l_get_structure_versions_csr(c_project_id NUMBER, c_structure_id NUMBER, c_pub_status VARCHAR2)
7133: -- IS
7134: -- SELECT b.element_version_id
7135: -- FROM PA_PROJ_ELEM_VER_STRUCTURE b
7136: -- WHERE b.proj_element_id = c_structure_id
7137: -- AND b.project_id = c_project_id
7138: -- AND (b.STATUS_CODE = 'STRUCTURE_PUBLISHED' AND b.LATEST_EFF_PUBLISHED_FLAG = 'Y' AND c_pub_status = 'Y')
7139: -- UNION
7137: -- AND b.project_id = c_project_id
7138: -- AND (b.STATUS_CODE = 'STRUCTURE_PUBLISHED' AND b.LATEST_EFF_PUBLISHED_FLAG = 'Y' AND c_pub_status = 'Y')
7139: -- UNION
7140: -- SELECT b.element_version_id
7141: -- FROM PA_PROJ_ELEM_VER_STRUCTURE b
7142: -- WHERE b.proj_element_id = c_structure_id
7143: -- AND b.project_id = c_project_id
7144: -- AND (b.STATUS_CODE <> 'STRUCTURE_PUBLISHED' AND c_pub_status = 'N');
7145:
7158: --commented by hsiu for advanced structure changes
7159: -- CURSOR l_get_structure_ver_attr_csr(c_structure_version_id NUMBER, c_pub_status VARCHAR2)
7160: -- IS
7161: -- SELECT a.*
7162: -- FROM PA_PROJ_ELEM_VER_STRUCTURE a,
7163: -- PA_PROJ_ELEMENT_VERSIONS b
7164: -- WHERE b.element_version_id = c_structure_version_id
7165: -- AND b.element_version_id = a.element_version_id
7166: -- AND b.project_id = a.project_id
7166: -- AND b.project_id = a.project_id
7167: -- AND (a.STATUS_CODE <> 'STRUCTURE_PUBLISHED' AND c_pub_status = 'N')
7168: -- UNION
7169: -- SELECT a.*
7170: -- FROM PA_PROJ_ELEM_VER_STRUCTURE a,
7171: -- PA_PROJ_ELEMENT_VERSIONS b
7172: -- WHERE b.element_version_id = c_structure_version_id
7173: -- AND b.element_version_id = a.element_version_id
7174: -- AND b.project_id = a.project_id
7176:
7177: CURSOR l_get_structure_ver_attr_csr(c_structure_version_id NUMBER)
7178: IS
7179: SELECT a.*
7180: FROM PA_PROJ_ELEM_VER_STRUCTURE a,
7181: PA_PROJ_ELEMENT_VERSIONS b
7182: WHERE b.element_version_id = c_structure_version_id
7183: AND b.element_version_id = a.element_version_id
7184: AND b.proj_element_id = a.proj_element_id
7343: CURSOR l_get_structure_versions_csr(c_project_id NUMBER, c_structure_id NUMBER,
7344: c_option NUMBER)
7345: IS
7346: SELECT distinct(b.element_version_id)
7347: FROM pa_proj_elements a, pa_proj_elem_ver_structure b
7348: WHERE a.project_id = c_project_id
7349: AND a.proj_element_id = c_structure_id
7350: AND a.project_id = b.project_id
7351: AND a.proj_element_id = b.proj_element_id
7356: --return the first selected row in this API.
7357: AND b.element_version_id = PA_PROJECT_STRUCTURE_UTILS.GET_LAST_UPDATED_WORKING_VER(c_structure_id)
7358: -- AND b.last_update_date = (
7359: -- SELECT MAX(c.last_update_date)
7360: -- FROM pa_proj_elem_ver_structure c
7361: -- WHERE c.project_id = a.project_id
7362: -- AND c.proj_element_id = a.proj_element_id
7363: -- AND c.status_code <> 'STRUCTURE_PUBLISHED')
7364: AND 1 = (c_option)
7363: -- AND c.status_code <> 'STRUCTURE_PUBLISHED')
7364: AND 1 = (c_option)
7365: UNION
7366: SELECT distinct(b.element_version_id)
7367: FROM pa_proj_elements a, pa_proj_elem_ver_structure b
7368: WHERE a.project_id = c_project_id
7369: AND a.proj_element_id = c_structure_id
7370: AND a.project_id = b.project_id
7371: AND a.proj_element_id = b.proj_element_id
7378: (b.status_code <> 'STRUCTURE_PUBLISHED'))
7379: AND 2 = (c_option)
7380: UNION
7381: SELECT distinct(b.element_version_id)
7382: FROM pa_proj_elements a, pa_proj_elem_ver_structure b
7383: WHERE a.project_id = c_project_id
7384: AND a.proj_element_id = c_structure_id
7385: AND a.project_id = b.project_id
7386: AND a.proj_element_id = b.proj_element_id
9187: and wp_auto_publish_flag = 'Y';
9188:
9189: CURSOR get_wp_info IS
9190: select name, description
9191: from pa_proj_elem_ver_structure
9192: where project_Id = p_project_id
9193: and element_version_id = p_structure_version_id;
9194:
9195: /* Bug 2683138 */
9382: END LOOP;
9383: CLOSE sel_other_structure_ver;
9384: */
9385: --Update to submit status
9386: UPDATE PA_PROJ_ELEM_VER_STRUCTURE
9387: set status_code = 'STRUCTURE_SUBMITTED',
9388: lock_status_code = 'UNLOCKED',
9389: locked_by_person_id = NULL,
9390: locked_date = NULL
9527:
9528: --
9529: BEGIN
9530: select pev_structure_id into l_pev_struc_id
9531: from pa_proj_elem_ver_structure
9532: where project_id = p_project_id
9533: and element_version_id = p_structure_version_id
9534: and record_version_number = p_record_version_number
9535: for update of record_version_number NOWAIT;
9573: END IF;
9574: CLOSE get_status_code;
9575: --end validation
9576:
9577: update pa_proj_elem_ver_structure
9578: set status_code = p_status_code,
9579: record_version_number = record_version_number + 1
9580: where pev_structure_id = l_pev_struc_id;
9581:
9913: and b.structure_type_class_code = 'WORKPLAN';
9914:
9915: cursor sel_latest_pub_ver(c_structure_id NUMBER) IS
9916: select element_version_id
9917: from pa_proj_elem_ver_structure
9918: where proj_element_id = c_structure_id
9919: and project_id = p_project_id
9920: and status_code = 'STRUCTURE_PUBLISHED'
9921: and LATEST_EFF_PUBLISHED_FLAG = 'Y';
9950: and b.proj_element_id = c_struc_id;
9951:
9952: cursor sel_struc_ver_attr_rvn(c_struc_ver_id NUMBER) IS
9953: select PEV_STRUCTURE_ID, record_version_number
9954: from pa_proj_elem_ver_structure
9955: where project_id = p_project_id
9956: and element_version_id = c_struc_ver_id;
9957:
9958: cursor sel_proj_workplan_attr(c_struc_id NUMBER) is
10381: l_effective_date := sysdate;
10382: END IF;
10383:
10384: --Change status
10385: UPDATE pa_proj_elem_ver_structure
10386: set status_code = l_status_code,
10387: current_flag = 'N',
10388: current_baseline_date = NULL,
10389: current_baseline_person_id = NULL,
10834: CLOSE sel_struc_ver_attr_rvn;
10835:
10836: --bug 3010538
10837: --added process_update_wbs_flag
10838: UPDATE pa_proj_elem_ver_structure
10839: set status_code = l_status_code,
10840: current_flag = l_baseline_flag,
10841: current_baseline_date = l_effective_date,
10842: current_baseline_person_id = NULL,
11073: l_effective_date := sysdate;
11074: END IF;
11075:
11076: --Change status
11077: UPDATE pa_proj_elem_ver_structure
11078: set status_code = l_status_code,
11079: current_flag = 'N',
11080: current_baseline_date = NULL,
11081: current_baseline_person_id = NULL,
11331: --end bug 3125813
11332:
11333: cursor sel_latest_pub_ver(c_structure_id NUMBER) IS
11334: select a.element_version_id
11335: from pa_proj_elem_ver_structure a,
11336: pa_proj_elements b
11337: where b.proj_element_id = c_structure_id
11338: and b.project_id = a.project_id
11339: and b.proj_element_id = a.proj_element_id
11348: and b.proj_element_id = p_proj_element_id;
11349:
11350: cursor sel_struc_ver_attr_rvn(c_struc_ver_id NUMBER) IS
11351: select a.PEV_STRUCTURE_ID, a.record_version_number
11352: from pa_proj_elem_ver_structure a,
11353: pa_proj_element_versions b
11354: where b.project_id = a.project_id
11355: and b.element_version_id = c_struc_ver_id
11356: and a.element_version_id = b.element_version_id;
11507: FETCH get_curr_wp_flag into l_curr_wp_ver_flag;
11508: CLOSE get_curr_wp_flag;
11509:
11510: IF ( l_curr_wp_ver_flag ='N' AND l_template_flag = 'N' ) THEN
11511: UPDATE pa_proj_elem_ver_structure
11512: SET current_working_flag = 'N'
11513: WHERE element_version_id = l_struc_ver_id
11514: and project_id = l_project_id;
11515: END IF;
11578: CLOSE sel_struc_ver_attr_rvn;
11579:
11580: --set status to latest published and baselined
11581:
11582: UPDATE pa_proj_elem_ver_structure
11583: set status_code = 'STRUCTURE_PUBLISHED',
11584: published_date = sysdate,
11585: current_flag = 'Y',
11586: current_baseline_date = sysdate,
11663: l_opt VARCHAR2(1);
11664:
11665: cursor get_wp_versions IS
11666: select ppevs.element_version_id
11667: from pa_proj_elem_ver_structure ppevs,
11668: pa_proj_elements ppe,
11669: pa_proj_structure_types ppst,
11670: pa_structure_types pst
11671: where ppevs.status_code <> 'STRUCTURE_PUBLISHED'
11678: and ppe.project_id = p_project_id
11679: and '1' = l_opt
11680: union all
11681: select ppevs.element_version_id
11682: from pa_proj_elem_ver_structure ppevs,
11683: pa_proj_elements ppe,
11684: pa_proj_structure_types ppst,
11685: pa_structure_types pst
11686: where ppevs.status_code = 'STRUCTURE_PUBLISHED'
12278: pevsh.pev_schedule_id, pevsh.record_version_number,
12279: pevsh.rowid
12280: from pa_proj_elements pe,
12281: pa_proj_element_versions pev,
12282: pa_proj_elem_ver_structure pevs,
12283: pa_proj_elem_ver_schedule pevsh
12284: where pev.element_version_id = p_structure_version_id and
12285: pev.proj_element_id = pe.proj_element_id and
12286: pev.project_id = pevs.project_id and
12310: l_proj_element_id PA_PROJ_ELEMENTS.PROJ_ELEMENT_ID%TYPE;
12311: l_pe_rvn PA_PROJ_ELEMENTS.RECORD_VERSION_NUMBER%TYPE;
12312: l_element_version_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
12313: l_pev_rvn PA_PROJ_ELEMENT_VERSIONS.RECORD_VERSION_NUMBER%TYPE;
12314: l_pev_structure_id PA_PROJ_ELEM_VER_STRUCTURE.PEV_STRUCTURE_ID%TYPE;
12315: l_pevs_rvn PA_PROJ_ELEM_VER_STRUCTURE.RECORD_VERSION_NUMBER%TYPE;
12316: l_pev_schedule_id PA_PROJ_ELEM_VER_SCHEDULE.PEV_SCHEDULE_ID%TYPE;
12317: l_pevsh_rvn PA_PROJ_ELEM_VER_SCHEDULE.RECORD_VERSION_NUMBER%TYPE;
12318: l_pevsh_rowid VARCHAR2(255);
12311: l_pe_rvn PA_PROJ_ELEMENTS.RECORD_VERSION_NUMBER%TYPE;
12312: l_element_version_id PA_PROJ_ELEMENT_VERSIONS.ELEMENT_VERSION_ID%TYPE;
12313: l_pev_rvn PA_PROJ_ELEMENT_VERSIONS.RECORD_VERSION_NUMBER%TYPE;
12314: l_pev_structure_id PA_PROJ_ELEM_VER_STRUCTURE.PEV_STRUCTURE_ID%TYPE;
12315: l_pevs_rvn PA_PROJ_ELEM_VER_STRUCTURE.RECORD_VERSION_NUMBER%TYPE;
12316: l_pev_schedule_id PA_PROJ_ELEM_VER_SCHEDULE.PEV_SCHEDULE_ID%TYPE;
12317: l_pevsh_rvn PA_PROJ_ELEM_VER_SCHEDULE.RECORD_VERSION_NUMBER%TYPE;
12318: l_pevsh_rowid VARCHAR2(255);
12319:
12637: IS PRAGMA AUTONOMOUS_TRANSACTION;
12638: CURSOR get_struct_ver_info IS
12639: select a.name, a.version_number, c.scheduled_start_date,
12640: c.scheduled_finish_date, b.project_id
12641: from pa_proj_elem_ver_structure a,
12642: pa_proj_element_versions b,
12643: pa_proj_elem_ver_schedule c
12644: where b.element_version_id = p_structure_version_id
12645: and b.project_id = a.project_id
13296:
13297: CURSOR l_get_structure_ver_attr_csr(c_structure_version_id NUMBER)
13298: IS
13299: SELECT a.*
13300: FROM PA_PROJ_ELEM_VER_STRUCTURE a,
13301: PA_PROJ_ELEMENT_VERSIONS b
13302: WHERE b.element_version_id = c_structure_version_id
13303: AND b.project_id = a.project_id
13304: AND b.element_version_id = a.project_id;
13342: WHERE ppev.element_version_id = c_structure_version_id
13343: AND EXISTS
13344: (SELECT 'Y'
13345: FROM PA_PROJ_ELEMENT_VERSIONS ppev2,
13346: PA_PROJ_ELEM_VER_STRUCTURE ppevs
13347: WHERE ppev2.proj_element_id = ppev.proj_element_id
13348: AND ppev2.project_id = ppev.project_id
13349: AND ppevs.project_id = ppev2.project_id
13350: AND ppevs.element_version_id = ppev2.element_version_id
14106:
14107: --Bug No 3634334
14108: --Commented the following query for performance reason and rewritten the same.
14109: --Getting the project_id from pa_proj_element_versions table instead of getting
14110: --it from pa_proj_elem_ver_structure table, in this optimizer will be using the
14111: --unique index on element_version_id and functionally the process is fetching
14112: --project_id
14113: /* CURSOR get_proj_id(cp_structure_version_id NUMBER) IS
14114: SELECT project_id
14111: --unique index on element_version_id and functionally the process is fetching
14112: --project_id
14113: /* CURSOR get_proj_id(cp_structure_version_id NUMBER) IS
14114: SELECT project_id
14115: FROM pa_proj_elem_ver_structure
14116: WHERE element_version_id = cp_structure_version_id;*/
14117:
14118: CURSOR get_proj_id(cp_structure_version_id NUMBER) IS
14119: SELECT project_id
14138: OPEN get_proj_id(p_structure_version_id);
14139: FETCH get_proj_id INTO l_proj_id;
14140: CLOSE get_proj_id;
14141: --
14142: UPDATE pa_proj_elem_ver_structure
14143: SET SCHEDULE_DIRTY_FLAG = l_dirty_flag
14144: WHERE element_version_id = p_structure_version_id
14145: AND project_id = l_proj_id;
14146: ELSE
14143: SET SCHEDULE_DIRTY_FLAG = l_dirty_flag
14144: WHERE element_version_id = p_structure_version_id
14145: AND project_id = l_proj_id;
14146: ELSE
14147: UPDATE pa_proj_elem_ver_structure
14148: SET SCHEDULE_DIRTY_FLAG = l_dirty_flag
14149: WHERE element_version_id = p_structure_version_id
14150: AND project_id = p_project_id;
14151: END IF;
14274: and b.structure_type_class_code = 'WORKPLAN';
14275:
14276: cursor sel_latest_pub_ver(c_structure_id NUMBER) IS
14277: select element_version_id
14278: from pa_proj_elem_ver_structure
14279: where proj_element_id = c_structure_id
14280: and project_id = p_project_id
14281: and status_code = 'STRUCTURE_PUBLISHED'
14282: and LATEST_EFF_PUBLISHED_FLAG = 'Y';
14314: and b.proj_element_id = c_struc_id;
14315:
14316: cursor sel_struc_ver_attr_rvn(c_struc_ver_id NUMBER) IS
14317: select PEV_STRUCTURE_ID, record_version_number
14318: from pa_proj_elem_ver_structure
14319: where project_id = p_project_id
14320: and element_version_id = c_struc_ver_id;
14321:
14322: cursor sel_proj_workplan_attr(c_struc_id NUMBER) is
14463: from pa_object_relationships por1
14464: , pa_object_relationships por2
14465: , pa_projects_all ppa
14466: , pa_proj_element_versions ppev
14467: , pa_proj_elem_ver_structure ppevs
14468: where por1.object_id_to2 = c_project_id
14469: and por1.relationship_type in ('LW', 'LF')
14470: and por1.object_id_from1 = por2.object_id_to1
14471: and por2.relationship_type = 'S'
14474: and por2.object_id_from1 = ppev.element_version_id
14475: -- Bug Fix 4868867
14476: -- Ram Namburi
14477: -- adding the following additional project id join to avoid the Full Table Scan on
14478: -- pa_proj_elem_ver_structure table and to use N1.
14479: and ppev.project_id = ppevs.project_id
14480: and ppev.parent_structure_version_id = ppevs.element_version_id
14481: and ppevs.current_working_flag = 'Y'
14482: union
14491: from pa_object_relationships por1
14492: , pa_object_relationships por2
14493: , pa_projects_all ppa
14494: , pa_proj_element_versions ppev
14495: , pa_proj_elem_ver_structure ppevs
14496: where por1.object_id_from2 = c_project_id
14497: and por1.relationship_type in ('LW', 'LF')
14498: and por1.object_id_from1 = por2.object_id_to1
14499: and por2.relationship_type = 'S'
14502: and por2.object_id_from1 = ppev.element_version_id
14503: -- Bug Fix 4868867
14504: -- Ram Namburi
14505: -- adding the following additional project id join to avoid the Full Table Scan on
14506: -- pa_proj_elem_ver_structure table and to use N1.
14507: and ppev.project_id = ppevs.project_id
14508: and ppev.parent_structure_version_id = ppevs.element_version_id
14509: and ppevs.current_working_flag = 'Y';
14510:
15618: ,x_msg_data => x_msg_data);
15619:
15620: -- Set the process update flag for the source structure version to 'Y'.
15621:
15622: update pa_proj_elem_ver_structure ppevs
15623: set ppevs.process_update_wbs_flag = 'Y'
15624: where ppevs.element_version_id = l_cur_links_rec.src_str_ver_id
15625: and ppevs.project_id = l_cur_links_rec.src_proj_id;
15626:
16111: ,x_msg_data => x_msg_data);
16112:
16113: -- Set the process update flag for the source structure version to 'Y'.
16114:
16115: update pa_proj_elem_ver_structure ppevs
16116: set ppevs.process_update_wbs_flag = 'Y'
16117: where ppevs.element_version_id = l_cur_links_rec.src_str_ver_id
16118: and ppevs.project_id = l_cur_links_rec.src_proj_id;
16119:
16527: where proj_element_id = l_structure_id;
16528:
16529: IF (l_template_flag <> 'Y') THEN
16530: --project
16531: update pa_proj_elem_ver_structure
16532: set status_code = 'STRUCTURE_PUBLISHED',
16533: latest_eff_published_flag = 'Y',
16534: published_date = sysdate
16535: where project_id = p_project_id
16993: where proj_element_id = l_structure_id;
16994:
16995: IF (l_template_flag <> 'Y') THEN
16996: --project
16997: update pa_proj_elem_ver_structure
16998: set status_code = 'STRUCTURE_PUBLISHED',
16999: latest_eff_published_flag = 'Y',
17000: published_date = sysdate
17001: where project_id = p_project_id
17530: -- If the project is version disabled, then baseline the structure version.
17531:
17532: if (PA_PROJ_TASK_STRUC_PUB.IS_WP_VERSIONING_ENABLED(p_project_id) = 'N') then
17533:
17534: update pa_proj_elem_ver_structure ppevs
17535: set ppevs.current_flag = 'Y'
17536: , ppevs.original_flag = 'Y'
17537: , ppevs.record_version_number = (ppevs.record_version_number+1)
17538: where ppevs.project_id = p_project_id
17921: -- If the project is version disabled, then baseline the structure version.
17922:
17923: if (PA_PROJ_TASK_STRUC_PUB.IS_WP_VERSIONING_ENABLED(p_project_id) = 'N') then
17924:
17925: update pa_proj_elem_ver_structure ppevs
17926: set ppevs.current_flag = 'Y'
17927: , ppevs.original_flag = 'Y'
17928: , ppevs.record_version_number = (ppevs.record_version_number+1)
17929: where ppevs.project_id = p_project_id