eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.PA_CONTROL_API_PUB dependencies on PA_PROJECT_STATUSES

Line 34: from pa_project_statuses

30: pa_ci_types_b cib,
31: (select
32: project_status_code,
33: project_system_status_code
34: from pa_project_statuses
35: where status_type = 'CONTROL_ITEM') s
36: WHERE ci.ci_id = c_Ci_Id
37: AND ci.ci_type_id = cib.ci_type_id
38: AND ci.status_code = s.project_status_code;

Line 47: FROM pa_project_statuses pps, pa_control_items ci

43: is passed in.
44: */
45: CURSOR Check_Workflow_On_CI (c_Ci_Id NUMBER) IS
46: SELECT ci.ci_id
47: FROM pa_project_statuses pps, pa_control_items ci
48: WHERE pps.status_type = 'CONTROL_ITEM'
49: AND pps.project_status_code = ci.status_code
50: AND pps.enable_wf_flag = 'Y'
51: AND pps.wf_success_status_code is not null

Line 1610: From pa_project_statuses

1606: cursor c_check_Progress_code
1607: is
1608: Select project_status_code,
1609: project_status_name
1610: From pa_project_statuses
1611: where (trunc(sysdate) between nvl(start_date_active, trunc(sysdate))
1612: and nvl(end_date_active, trunc(sysdate))
1613: and status_type = 'PROGRESS')
1614: and project_status_code = p_progress_status_code; --need to clarify whether this is a name or code

Line 1638: pa_project_statuses s

1634: cit.approval_required_flag,
1635: s.next_allowable_status_flag
1636: FROM pa_control_items c,
1637: pa_ci_types_b cit,
1638: pa_project_statuses s
1639: WHERE c.ci_id = p_ci_id
1640: AND c.status_code = s.project_status_code
1641: AND c.ci_type_id =cit.ci_type_id
1642: AND s.status_type = 'CONTROL_ITEM';

Line 1660: l_ci_status_code_1 pa_project_statuses.project_status_code%TYPE;

1656: l_where1 VARCHAR2(2000);
1657: l_cursor NUMBER;
1658: l_rows NUMBER;
1659: l_rows1 NUMBER;
1660: l_ci_status_code_1 pa_project_statuses.project_status_code%TYPE;
1661:
1662: cp c_get_control_item_data%ROWTYPE;
1663: c_get_resolution_codes_rec c_get_resolution_codes%ROWTYPE;
1664: c_check_Progress_code_rec c_check_Progress_code%ROWTYPE;

Line 1719: l_proj_status_code pa_project_statuses.project_system_status_code%TYPE;

1715: l_attribute14 pa_control_items.attribute14%TYPE;
1716: l_attribute15 pa_control_items.attribute15%TYPE;
1717:
1718: l_projectid pa_control_items.project_id%TYPE;
1719: l_proj_status_code pa_project_statuses.project_system_status_code%TYPE;
1720: l_citypeclasscode pa_ci_types_b.ci_type_class_Code%TYPE;
1721:
1722: l_curr_system_status pa_project_statuses.project_system_status_code%TYPE;
1723: l_next_allow_status_flag pa_project_statuses.next_allowable_status_flag%TYPE;

Line 1722: l_curr_system_status pa_project_statuses.project_system_status_code%TYPE;

1718: l_projectid pa_control_items.project_id%TYPE;
1719: l_proj_status_code pa_project_statuses.project_system_status_code%TYPE;
1720: l_citypeclasscode pa_ci_types_b.ci_type_class_Code%TYPE;
1721:
1722: l_curr_system_status pa_project_statuses.project_system_status_code%TYPE;
1723: l_next_allow_status_flag pa_project_statuses.next_allowable_status_flag%TYPE;
1724: l_ci_type_class_code pa_ci_types_b.ci_type_class_code%TYPE;
1725: l_approval_required_flag pa_ci_types_b.approval_required_flag%TYPE;
1726:

Line 1723: l_next_allow_status_flag pa_project_statuses.next_allowable_status_flag%TYPE;

1719: l_proj_status_code pa_project_statuses.project_system_status_code%TYPE;
1720: l_citypeclasscode pa_ci_types_b.ci_type_class_Code%TYPE;
1721:
1722: l_curr_system_status pa_project_statuses.project_system_status_code%TYPE;
1723: l_next_allow_status_flag pa_project_statuses.next_allowable_status_flag%TYPE;
1724: l_ci_type_class_code pa_ci_types_b.ci_type_class_code%TYPE;
1725: l_approval_required_flag pa_ci_types_b.approval_required_flag%TYPE;
1726:
1727: l_enforce_security VARCHAR2(1) := 'Y';

Line 1919: l_from_clause := ' FROM pa_obj_status_lists osl, pa_status_list_items sli, pa_project_statuses ps ';

1915: FETCH c_info INTO l_ci_type_class_code, l_approval_required_flag, l_next_allow_status_flag;
1916: CLOSE c_info;
1917:
1918: l_sel_clause := ' SELECT ps.project_status_code ';
1919: l_from_clause := ' FROM pa_obj_status_lists osl, pa_status_list_items sli, pa_project_statuses ps ';
1920: l_where := ' WHERE osl.status_type = '||'''CONTROL_ITEM'''||
1921: ' AND osl.object_type = '||'''PA_CI_TYPES'''||
1922: ' AND osl.object_id = '||l_ci_type_id||
1923: ' AND osl.status_list_id = sli.status_list_id'||

Line 1931: ' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||

1927: ' AND trunc(sysdate) between nvl(ps.start_date_active, trunc(sysdate)) and nvl(ps.end_date_active, trunc(sysdate))'||
1928: ' AND (('||''''||l_next_allow_status_flag||''''||' = '||'''N'''||' and 1=2)'||
1929: ' OR '||
1930: ' ('||''''||l_next_allow_status_flag||''''||' = '||'''S'''||
1931: ' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||
1932: ' and project_system_status_code in ( select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
1933: ''''||l_curr_status_code||''''||')))'||
1934: ' OR '||
1935: ' ('||''''||l_next_allow_status_flag||''''||' = '||'''U'''||

Line 1940: ' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||

1936: ' and ps.project_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||''''||
1937: l_curr_status_code||''''||'))'||
1938: ' OR '||
1939: ' ('||''''||l_next_allow_status_flag||''''||' = '||'''A'''||
1940: ' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||
1941: ' and project_system_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
1942: ''''||l_curr_system_status||''''||'))))'||
1943: ' AND ps.project_status_code not in (select wf_success_status_code from pa_project_statuses where status_type = '||
1944: '''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||

Line 1943: ' AND ps.project_status_code not in (select wf_success_status_code from pa_project_statuses where status_type = '||

1939: ' ('||''''||l_next_allow_status_flag||''''||' = '||'''A'''||
1940: ' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||
1941: ' and project_system_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
1942: ''''||l_curr_system_status||''''||'))))'||
1943: ' AND ps.project_status_code not in (select wf_success_status_code from pa_project_statuses where status_type = '||
1944: '''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
1945: ' AND ps.project_status_code not in (select wf_failure_status_code from pa_project_statuses where status_type = '||
1946: '''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
1947: ' AND decode(ps.project_system_status_code, '||'''CI_CANCELED'''||

Line 1945: ' AND ps.project_status_code not in (select wf_failure_status_code from pa_project_statuses where status_type = '||

1941: ' and project_system_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
1942: ''''||l_curr_system_status||''''||'))))'||
1943: ' AND ps.project_status_code not in (select wf_success_status_code from pa_project_statuses where status_type = '||
1944: '''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
1945: ' AND ps.project_status_code not in (select wf_failure_status_code from pa_project_statuses where status_type = '||
1946: '''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
1947: ' AND decode(ps.project_system_status_code, '||'''CI_CANCELED'''||
1948: ', nvl(pa_control_items_utils.CheckCIActionAllowed('||'''CONTROL_ITEM'''||', '||''''||l_curr_status_code||''''||', '||
1949: '''CONTROL_ITEM_ALLOW_CANCEL'''||', null),'||'''N'''||' ),'||'''Y'''||' ) = '||'''Y'''||

Line 1961: l_where1 := ' AND ps.project_status_code not in (select project_status_code from pa_project_statuses where status_type = '||

1957: '''Y'''||' ,'||'''N'''||' ),'||'''Y'''||' ) = '||'''Y'''||
1958: ' AND ps.project_status_code = '||''''||p_ci_status_code||'''';
1959:
1960: IF (l_ci_type_class_code = 'ISSUE' AND l_approval_required_flag = 'N') THEN
1961: l_where1 := ' AND ps.project_status_code not in (select project_status_code from pa_project_statuses where status_type = '||
1962: '''CONTROL_ITEM'''||' and enable_wf_flag = '||'''Y'''||
1963: ' and wf_success_status_code is not null and wf_failure_status_code is not null) ';
1964: END IF;
1965:

Line 2428: l_status_code pa_project_statuses.project_status_code%type;

2424: l_ci_type_id pa_ci_types_b.ci_type_id%type;
2425: l_summary pa_control_items.summary%type;
2426: l_ci_number pa_control_items.ci_number%type;
2427: l_description pa_control_items.description%type;
2428: l_status_code pa_project_statuses.project_status_code%type;
2429: l_status pa_project_statuses.project_status_name%type;
2430: l_owner_id pa_control_items.owner_id%type;
2431: l_progress_status_code pa_control_items.progress_status_code%type;
2432: l_progress_as_of_date pa_control_items.progress_as_of_date%type;

Line 2429: l_status pa_project_statuses.project_status_name%type;

2425: l_summary pa_control_items.summary%type;
2426: l_ci_number pa_control_items.ci_number%type;
2427: l_description pa_control_items.description%type;
2428: l_status_code pa_project_statuses.project_status_code%type;
2429: l_status pa_project_statuses.project_status_name%type;
2430: l_owner_id pa_control_items.owner_id%type;
2431: l_progress_status_code pa_control_items.progress_status_code%type;
2432: l_progress_as_of_date pa_control_items.progress_as_of_date%type;
2433: l_status_overview pa_control_items.status_overview%type;

Line 3063: l_status_code pa_project_statuses.project_status_code%type;

3059: l_ci_type_id pa_ci_types_b.ci_type_id%type;
3060: l_summary pa_control_items.summary%type;
3061: l_ci_number pa_control_items.ci_number%type;
3062: l_description pa_control_items.description%type;
3063: l_status_code pa_project_statuses.project_status_code%type;
3064: l_status pa_project_statuses.project_status_name%type;
3065: l_owner_id pa_control_items.owner_id%type;
3066: l_progress_status_code pa_control_items.progress_status_code%type;
3067: l_progress_as_of_date pa_control_items.progress_as_of_date%type;

Line 3064: l_status pa_project_statuses.project_status_name%type;

3060: l_summary pa_control_items.summary%type;
3061: l_ci_number pa_control_items.ci_number%type;
3062: l_description pa_control_items.description%type;
3063: l_status_code pa_project_statuses.project_status_code%type;
3064: l_status pa_project_statuses.project_status_name%type;
3065: l_owner_id pa_control_items.owner_id%type;
3066: l_progress_status_code pa_control_items.progress_status_code%type;
3067: l_progress_as_of_date pa_control_items.progress_as_of_date%type;
3068: l_status_overview pa_control_items.status_overview%type;

Line 3698: l_status_code pa_project_statuses.project_status_code%type;

3694: l_ci_type_id pa_ci_types_b.ci_type_id%type;
3695: l_summary pa_control_items.summary%type;
3696: l_ci_number pa_control_items.ci_number%type;
3697: l_description pa_control_items.description%type;
3698: l_status_code pa_project_statuses.project_status_code%type;
3699: l_status pa_project_statuses.project_status_name%type;
3700: l_owner_id pa_control_items.owner_id%type;
3701: l_progress_status_code pa_control_items.progress_status_code%type;
3702: l_progress_as_of_date pa_control_items.progress_as_of_date%type;

Line 3699: l_status pa_project_statuses.project_status_name%type;

3695: l_summary pa_control_items.summary%type;
3696: l_ci_number pa_control_items.ci_number%type;
3697: l_description pa_control_items.description%type;
3698: l_status_code pa_project_statuses.project_status_code%type;
3699: l_status pa_project_statuses.project_status_name%type;
3700: l_owner_id pa_control_items.owner_id%type;
3701: l_progress_status_code pa_control_items.progress_status_code%type;
3702: l_progress_as_of_date pa_control_items.progress_as_of_date%type;
3703: l_status_overview pa_control_items.status_overview%type;

Line 6646: FROM pa_project_statuses

6642: cp curr_row%rowtype;
6643:
6644: CURSOR c_submit_status (p_curr_status_code VARCHAR2) IS
6645: SELECT enable_wf_flag, wf_success_status_code, wf_failure_status_code
6646: FROM pa_project_statuses
6647: WHERE project_status_code = p_curr_status_code;
6648:
6649: CURSOR c_lkup (p_lookup_type VARCHAR2, p_lookup_code VARCHAR2) IS
6650: SELECT lookup_code

Line 6659: FROM pa_project_statuses

6655: AND lookup_code = p_lookup_code;
6656:
6657: CURSOR c_statuses (p_status_type VARCHAR2, p_project_status_code VARCHAR2) IS
6658: SELECT project_status_code
6659: FROM pa_project_statuses
6660: WHERE status_type = p_status_type
6661: AND project_status_code = p_project_status_code
6662: AND trunc(sysdate) BETWEEN start_date_active AND nvl(end_date_active, trunc(sysdate));
6663:

Line 6712: pa_project_statuses s

6708: cit.approval_required_flag,
6709: s.next_allowable_status_flag
6710: FROM pa_control_items c,
6711: pa_ci_types_b cit,
6712: pa_project_statuses s
6713: WHERE c.ci_id = p_ci_id
6714: AND c.status_code = s.project_status_code
6715: AND c.ci_type_id =cit.ci_type_id
6716: AND s.status_type = 'CONTROL_ITEM';

Line 6734: l_ci_status_code_1 pa_project_statuses.project_status_code%TYPE;

6730: l_where1 VARCHAR2(2000);
6731: l_cursor NUMBER;
6732: l_rows NUMBER;
6733: l_rows1 NUMBER;
6734: l_ci_status_code_1 pa_project_statuses.project_status_code%TYPE;
6735:
6736: l_ROWID ROWID;
6737:
6738: l_enable_wf_flag pa_project_statuses.enable_wf_flag%TYPE;

Line 6738: l_enable_wf_flag pa_project_statuses.enable_wf_flag%TYPE;

6734: l_ci_status_code_1 pa_project_statuses.project_status_code%TYPE;
6735:
6736: l_ROWID ROWID;
6737:
6738: l_enable_wf_flag pa_project_statuses.enable_wf_flag%TYPE;
6739: l_wf_success_status_code pa_project_statuses.wf_success_status_code%TYPE;
6740: l_wf_failure_status_code pa_project_statuses.wf_failure_status_code%TYPE;
6741: l_status_change_flag VARCHAR2(1) := 'N';
6742: l_start_wf VARCHAR2(1) := 'Y';

Line 6739: l_wf_success_status_code pa_project_statuses.wf_success_status_code%TYPE;

6735:
6736: l_ROWID ROWID;
6737:
6738: l_enable_wf_flag pa_project_statuses.enable_wf_flag%TYPE;
6739: l_wf_success_status_code pa_project_statuses.wf_success_status_code%TYPE;
6740: l_wf_failure_status_code pa_project_statuses.wf_failure_status_code%TYPE;
6741: l_status_change_flag VARCHAR2(1) := 'N';
6742: l_start_wf VARCHAR2(1) := 'Y';
6743: l_validate_only VARCHAR2(1) := FND_API.g_false;

Line 6740: l_wf_failure_status_code pa_project_statuses.wf_failure_status_code%TYPE;

6736: l_ROWID ROWID;
6737:
6738: l_enable_wf_flag pa_project_statuses.enable_wf_flag%TYPE;
6739: l_wf_success_status_code pa_project_statuses.wf_success_status_code%TYPE;
6740: l_wf_failure_status_code pa_project_statuses.wf_failure_status_code%TYPE;
6741: l_status_change_flag VARCHAR2(1) := 'N';
6742: l_start_wf VARCHAR2(1) := 'Y';
6743: l_validate_only VARCHAR2(1) := FND_API.g_false;
6744: l_max_msg_count NUMBER := FND_API.G_MISS_NUM;

Line 6752: l_curr_system_status pa_project_statuses.project_system_status_code%TYPE;

6748: l_effort_type VARCHAR2(30) := 'PA_CI_EFFORT_LEVELS';
6749: l_source_type VARCHAR2(30) := 'PA_CI_SOURCE_TYPES';
6750: l_progress_type VARCHAR2(30) := 'PROGRESS';
6751: l_auto_numbers VARCHAR2(1);
6752: l_curr_system_status pa_project_statuses.project_system_status_code%TYPE;
6753: l_new_system_status pa_project_statuses.project_system_status_code%TYPE;
6754: l_next_allow_status_flag pa_project_statuses.next_allowable_status_flag%TYPE;
6755: l_ci_type_class_code pa_ci_types_b.ci_type_class_code%TYPE;
6756: l_approval_required_flag pa_ci_types_b.approval_required_flag%TYPE;

Line 6753: l_new_system_status pa_project_statuses.project_system_status_code%TYPE;

6749: l_source_type VARCHAR2(30) := 'PA_CI_SOURCE_TYPES';
6750: l_progress_type VARCHAR2(30) := 'PROGRESS';
6751: l_auto_numbers VARCHAR2(1);
6752: l_curr_system_status pa_project_statuses.project_system_status_code%TYPE;
6753: l_new_system_status pa_project_statuses.project_system_status_code%TYPE;
6754: l_next_allow_status_flag pa_project_statuses.next_allowable_status_flag%TYPE;
6755: l_ci_type_class_code pa_ci_types_b.ci_type_class_code%TYPE;
6756: l_approval_required_flag pa_ci_types_b.approval_required_flag%TYPE;
6757: l_resolution_check VARCHAR2(10) := 'AMG';

Line 6754: l_next_allow_status_flag pa_project_statuses.next_allowable_status_flag%TYPE;

6750: l_progress_type VARCHAR2(30) := 'PROGRESS';
6751: l_auto_numbers VARCHAR2(1);
6752: l_curr_system_status pa_project_statuses.project_system_status_code%TYPE;
6753: l_new_system_status pa_project_statuses.project_system_status_code%TYPE;
6754: l_next_allow_status_flag pa_project_statuses.next_allowable_status_flag%TYPE;
6755: l_ci_type_class_code pa_ci_types_b.ci_type_class_code%TYPE;
6756: l_approval_required_flag pa_ci_types_b.approval_required_flag%TYPE;
6757: l_resolution_check VARCHAR2(10) := 'AMG';
6758: l_resolution_req VARCHAR2(10) := 'N';

Line 6926: l_from_clause := ' FROM pa_obj_status_lists osl, pa_status_list_items sli, pa_project_statuses ps ';

6922: ELSIF p_ci_status_code IS NOT NULL THEN
6923: l_ci_status_code := p_ci_status_code;
6924:
6925: l_sel_clause := ' SELECT ps.project_status_code ';
6926: l_from_clause := ' FROM pa_obj_status_lists osl, pa_status_list_items sli, pa_project_statuses ps ';
6927: l_where := ' WHERE osl.status_type = '||'''CONTROL_ITEM'''||
6928: ' AND osl.object_type = '||'''PA_CI_TYPES'''||
6929: ' AND osl.object_id = '||l_ci_type_id||
6930: ' AND osl.status_list_id = sli.status_list_id'||

Line 6938: ' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||

6934: ' AND trunc(sysdate) between nvl(ps.start_date_active, trunc(sysdate)) and nvl(ps.end_date_active, trunc(sysdate))'||
6935: ' AND (('||''''||l_next_allow_status_flag||''''||' = '||'''N'''||' and 1=2)'||
6936: ' OR '||
6937: ' ('||''''||l_next_allow_status_flag||''''||' = '||'''S'''||
6938: ' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||
6939: ' and project_system_status_code in ( select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
6940: ''''||l_curr_status_code||''''||')))'||
6941: ' OR '||
6942: ' ('||''''||l_next_allow_status_flag||''''||' = '||'''U'''||

Line 6947: ' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||

6943: ' and ps.project_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||''''||
6944: l_curr_status_code||''''||'))'||
6945: ' OR '||
6946: ' ('||''''||l_next_allow_status_flag||''''||' = '||'''A'''||
6947: ' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||
6948: ' and project_system_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
6949: ''''||l_curr_system_status||''''||'))))'||
6950: ' AND ps.project_status_code not in (select wf_success_status_code from pa_project_statuses where status_type = '||
6951: '''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||

Line 6950: ' AND ps.project_status_code not in (select wf_success_status_code from pa_project_statuses where status_type = '||

6946: ' ('||''''||l_next_allow_status_flag||''''||' = '||'''A'''||
6947: ' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||
6948: ' and project_system_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
6949: ''''||l_curr_system_status||''''||'))))'||
6950: ' AND ps.project_status_code not in (select wf_success_status_code from pa_project_statuses where status_type = '||
6951: '''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
6952: ' AND ps.project_status_code not in (select wf_failure_status_code from pa_project_statuses where status_type = '||
6953: '''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
6954: ' AND decode(ps.project_system_status_code, '||'''CI_CANCELED'''||

Line 6952: ' AND ps.project_status_code not in (select wf_failure_status_code from pa_project_statuses where status_type = '||

6948: ' and project_system_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
6949: ''''||l_curr_system_status||''''||'))))'||
6950: ' AND ps.project_status_code not in (select wf_success_status_code from pa_project_statuses where status_type = '||
6951: '''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
6952: ' AND ps.project_status_code not in (select wf_failure_status_code from pa_project_statuses where status_type = '||
6953: '''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
6954: ' AND decode(ps.project_system_status_code, '||'''CI_CANCELED'''||
6955: ', nvl(pa_control_items_utils.CheckCIActionAllowed('||'''CONTROL_ITEM'''||', '||''''||l_curr_status_code||''''||', '||
6956: '''CONTROL_ITEM_ALLOW_CANCEL'''||', null),'||'''N'''||' ),'||'''Y'''||' ) = '||'''Y'''||

Line 6969: l_where1 := ' AND ps.project_status_code not in (select project_status_code from pa_project_statuses where status_type = '||

6965: ' AND ps.project_status_code = '||''''||p_ci_status_code||'''';
6966:
6967:
6968: IF (l_ci_type_class_code = 'ISSUE' AND l_approval_required_flag = 'N') THEN
6969: l_where1 := ' AND ps.project_status_code not in (select project_status_code from pa_project_statuses where status_type = '||
6970: '''CONTROL_ITEM'''||' and enable_wf_flag = '||'''Y'''||
6971: ' and wf_success_status_code is not null and wf_failure_status_code is not null) ';
6972: END IF;
6973:

Line 7906: FROM pa_project_statuses

7902: cp curr_row%rowtype;
7903:
7904: CURSOR c_submit_status (p_curr_status_code VARCHAR2) IS
7905: SELECT enable_wf_flag, wf_success_status_code, wf_failure_status_code
7906: FROM pa_project_statuses
7907: WHERE project_status_code = p_curr_status_code;
7908:
7909: CURSOR c_lkup (p_lookup_type VARCHAR2, p_lookup_code VARCHAR2) IS
7910: SELECT lookup_code

Line 7919: FROM pa_project_statuses

7915: AND lookup_code = p_lookup_code;
7916:
7917: CURSOR c_statuses (p_status_type VARCHAR2, p_project_status_code VARCHAR2) IS
7918: SELECT project_status_code
7919: FROM pa_project_statuses
7920: WHERE status_type = p_status_type
7921: AND project_status_code = p_project_status_code
7922: AND trunc(sysdate) BETWEEN start_date_active AND nvl(end_date_active, trunc(sysdate));
7923:

Line 7972: pa_project_statuses s

7968: cit.approval_required_flag,
7969: s.next_allowable_status_flag
7970: FROM pa_control_items c,
7971: pa_ci_types_b cit,
7972: pa_project_statuses s
7973: WHERE c.ci_id = p_ci_id
7974: AND c.status_code = s.project_status_code
7975: AND c.ci_type_id =cit.ci_type_id
7976: AND s.status_type = 'CONTROL_ITEM';

Line 7994: l_ci_status_code_1 pa_project_statuses.project_status_code%TYPE;

7990: l_where1 VARCHAR2(2000);
7991: l_cursor NUMBER;
7992: l_rows NUMBER;
7993: l_rows1 NUMBER;
7994: l_ci_status_code_1 pa_project_statuses.project_status_code%TYPE;
7995:
7996: l_ROWID ROWID;
7997:
7998: l_enable_wf_flag pa_project_statuses.enable_wf_flag%TYPE;

Line 7998: l_enable_wf_flag pa_project_statuses.enable_wf_flag%TYPE;

7994: l_ci_status_code_1 pa_project_statuses.project_status_code%TYPE;
7995:
7996: l_ROWID ROWID;
7997:
7998: l_enable_wf_flag pa_project_statuses.enable_wf_flag%TYPE;
7999: l_wf_success_status_code pa_project_statuses.wf_success_status_code%TYPE;
8000: l_wf_failure_status_code pa_project_statuses.wf_failure_status_code%TYPE;
8001: l_status_change_flag VARCHAR2(1) := 'N';
8002: l_start_wf VARCHAR2(1) := 'Y';

Line 7999: l_wf_success_status_code pa_project_statuses.wf_success_status_code%TYPE;

7995:
7996: l_ROWID ROWID;
7997:
7998: l_enable_wf_flag pa_project_statuses.enable_wf_flag%TYPE;
7999: l_wf_success_status_code pa_project_statuses.wf_success_status_code%TYPE;
8000: l_wf_failure_status_code pa_project_statuses.wf_failure_status_code%TYPE;
8001: l_status_change_flag VARCHAR2(1) := 'N';
8002: l_start_wf VARCHAR2(1) := 'Y';
8003: l_validate_only VARCHAR2(1) := FND_API.g_false;

Line 8000: l_wf_failure_status_code pa_project_statuses.wf_failure_status_code%TYPE;

7996: l_ROWID ROWID;
7997:
7998: l_enable_wf_flag pa_project_statuses.enable_wf_flag%TYPE;
7999: l_wf_success_status_code pa_project_statuses.wf_success_status_code%TYPE;
8000: l_wf_failure_status_code pa_project_statuses.wf_failure_status_code%TYPE;
8001: l_status_change_flag VARCHAR2(1) := 'N';
8002: l_start_wf VARCHAR2(1) := 'Y';
8003: l_validate_only VARCHAR2(1) := FND_API.g_false;
8004: l_max_msg_count NUMBER := FND_API.G_MISS_NUM;

Line 8012: l_curr_system_status pa_project_statuses.project_system_status_code%TYPE;

8008: l_effort_type VARCHAR2(30) := 'PA_CI_EFFORT_LEVELS';
8009: l_source_type VARCHAR2(30) := 'PA_CI_SOURCE_TYPES';
8010: l_progress_type VARCHAR2(30) := 'PROGRESS';
8011: l_auto_numbers VARCHAR2(1);
8012: l_curr_system_status pa_project_statuses.project_system_status_code%TYPE;
8013: l_new_system_status pa_project_statuses.project_system_status_code%TYPE;
8014: l_next_allow_status_flag pa_project_statuses.next_allowable_status_flag%TYPE;
8015: l_ci_type_class_code pa_ci_types_b.ci_type_class_code%TYPE;
8016: l_approval_required_flag pa_ci_types_b.approval_required_flag%TYPE;

Line 8013: l_new_system_status pa_project_statuses.project_system_status_code%TYPE;

8009: l_source_type VARCHAR2(30) := 'PA_CI_SOURCE_TYPES';
8010: l_progress_type VARCHAR2(30) := 'PROGRESS';
8011: l_auto_numbers VARCHAR2(1);
8012: l_curr_system_status pa_project_statuses.project_system_status_code%TYPE;
8013: l_new_system_status pa_project_statuses.project_system_status_code%TYPE;
8014: l_next_allow_status_flag pa_project_statuses.next_allowable_status_flag%TYPE;
8015: l_ci_type_class_code pa_ci_types_b.ci_type_class_code%TYPE;
8016: l_approval_required_flag pa_ci_types_b.approval_required_flag%TYPE;
8017: l_resolution_check VARCHAR2(10) := 'AMG';

Line 8014: l_next_allow_status_flag pa_project_statuses.next_allowable_status_flag%TYPE;

8010: l_progress_type VARCHAR2(30) := 'PROGRESS';
8011: l_auto_numbers VARCHAR2(1);
8012: l_curr_system_status pa_project_statuses.project_system_status_code%TYPE;
8013: l_new_system_status pa_project_statuses.project_system_status_code%TYPE;
8014: l_next_allow_status_flag pa_project_statuses.next_allowable_status_flag%TYPE;
8015: l_ci_type_class_code pa_ci_types_b.ci_type_class_code%TYPE;
8016: l_approval_required_flag pa_ci_types_b.approval_required_flag%TYPE;
8017: l_resolution_check VARCHAR2(10) := 'AMG';
8018: l_resolution_req VARCHAR2(10) := 'N';

Line 8188: l_from_clause := ' FROM pa_obj_status_lists osl, pa_status_list_items sli, pa_project_statuses ps ';

8184: ELSIF p_ci_status_code IS NOT NULL THEN
8185: l_ci_status_code := p_ci_status_code;
8186:
8187: l_sel_clause := ' SELECT ps.project_status_code ';
8188: l_from_clause := ' FROM pa_obj_status_lists osl, pa_status_list_items sli, pa_project_statuses ps ';
8189: l_where := ' WHERE osl.status_type = '||'''CONTROL_ITEM'''||
8190: ' AND osl.object_type = '||'''PA_CI_TYPES'''||
8191: ' AND osl.object_id = '||l_ci_type_id||
8192: ' AND osl.status_list_id = sli.status_list_id'||

Line 8200: ' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||

8196: ' AND trunc(sysdate) between nvl(ps.start_date_active, trunc(sysdate)) and nvl(ps.end_date_active, trunc(sysdate))'||
8197: ' AND (('||''''||l_next_allow_status_flag||''''||' = '||'''N'''||' and 1=2)'||
8198: ' OR '||
8199: ' ('||''''||l_next_allow_status_flag||''''||' = '||'''S'''||
8200: ' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||
8201: ' and project_system_status_code in ( select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
8202: ''''||l_curr_status_code||''''||')))'||
8203: ' OR '||
8204: ' ('||''''||l_next_allow_status_flag||''''||' = '||'''U'''||

Line 8209: ' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||

8205: ' and ps.project_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||''''||
8206: l_curr_status_code||''''||'))'||
8207: ' OR '||
8208: ' ('||''''||l_next_allow_status_flag||''''||' = '||'''A'''||
8209: ' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||
8210: ' and project_system_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
8211: ''''||l_curr_system_status||''''||'))))'||
8212: ' AND ps.project_status_code not in (select wf_success_status_code from pa_project_statuses where status_type = '||
8213: '''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||

Line 8212: ' AND ps.project_status_code not in (select wf_success_status_code from pa_project_statuses where status_type = '||

8208: ' ('||''''||l_next_allow_status_flag||''''||' = '||'''A'''||
8209: ' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||
8210: ' and project_system_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
8211: ''''||l_curr_system_status||''''||'))))'||
8212: ' AND ps.project_status_code not in (select wf_success_status_code from pa_project_statuses where status_type = '||
8213: '''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
8214: ' AND ps.project_status_code not in (select wf_failure_status_code from pa_project_statuses where status_type = '||
8215: '''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
8216: ' AND decode(ps.project_system_status_code, '||'''CI_CANCELED'''||

Line 8214: ' AND ps.project_status_code not in (select wf_failure_status_code from pa_project_statuses where status_type = '||

8210: ' and project_system_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
8211: ''''||l_curr_system_status||''''||'))))'||
8212: ' AND ps.project_status_code not in (select wf_success_status_code from pa_project_statuses where status_type = '||
8213: '''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
8214: ' AND ps.project_status_code not in (select wf_failure_status_code from pa_project_statuses where status_type = '||
8215: '''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
8216: ' AND decode(ps.project_system_status_code, '||'''CI_CANCELED'''||
8217: ', nvl(pa_control_items_utils.CheckCIActionAllowed('||'''CONTROL_ITEM'''||', '||''''||l_curr_status_code||''''||', '||
8218: '''CONTROL_ITEM_ALLOW_CANCEL'''||', null),'||'''N'''||' ),'||'''Y'''||' ) = '||'''Y'''||

Line 9163: FROM pa_project_statuses

9159: cp curr_row%rowtype;
9160:
9161: CURSOR c_submit_status (p_curr_status_code VARCHAR2) IS
9162: SELECT enable_wf_flag, wf_success_status_code, wf_failure_status_code
9163: FROM pa_project_statuses
9164: WHERE project_status_code = p_curr_status_code;
9165:
9166: CURSOR c_lkup (p_lookup_type VARCHAR2, p_lookup_code VARCHAR2) IS
9167: SELECT lookup_code

Line 9176: FROM pa_project_statuses

9172: AND lookup_code = p_lookup_code;
9173:
9174: CURSOR c_statuses (p_status_type VARCHAR2, p_project_status_code VARCHAR2) IS
9175: SELECT project_status_code
9176: FROM pa_project_statuses
9177: WHERE status_type = p_status_type
9178: AND project_status_code = p_project_status_code
9179: AND trunc(sysdate) BETWEEN start_date_active AND nvl(end_date_active, trunc(sysdate));
9180:

Line 9229: pa_project_statuses s

9225: cit.approval_required_flag,
9226: s.next_allowable_status_flag
9227: FROM pa_control_items c,
9228: pa_ci_types_b cit,
9229: pa_project_statuses s
9230: WHERE c.ci_id = p_ci_id
9231: AND c.status_code = s.project_status_code
9232: AND c.ci_type_id =cit.ci_type_id
9233: AND s.status_type = 'CONTROL_ITEM';

Line 9251: l_ci_status_code_1 pa_project_statuses.project_status_code%TYPE;

9247: l_where1 VARCHAR2(2000);
9248: l_cursor NUMBER;
9249: l_rows NUMBER;
9250: l_rows1 NUMBER;
9251: l_ci_status_code_1 pa_project_statuses.project_status_code%TYPE;
9252:
9253: l_ROWID ROWID;
9254:
9255: l_enable_wf_flag pa_project_statuses.enable_wf_flag%TYPE;

Line 9255: l_enable_wf_flag pa_project_statuses.enable_wf_flag%TYPE;

9251: l_ci_status_code_1 pa_project_statuses.project_status_code%TYPE;
9252:
9253: l_ROWID ROWID;
9254:
9255: l_enable_wf_flag pa_project_statuses.enable_wf_flag%TYPE;
9256: l_wf_success_status_code pa_project_statuses.wf_success_status_code%TYPE;
9257: l_wf_failure_status_code pa_project_statuses.wf_failure_status_code%TYPE;
9258: l_status_change_flag VARCHAR2(1) := 'N';
9259: l_start_wf VARCHAR2(1) := 'Y';

Line 9256: l_wf_success_status_code pa_project_statuses.wf_success_status_code%TYPE;

9252:
9253: l_ROWID ROWID;
9254:
9255: l_enable_wf_flag pa_project_statuses.enable_wf_flag%TYPE;
9256: l_wf_success_status_code pa_project_statuses.wf_success_status_code%TYPE;
9257: l_wf_failure_status_code pa_project_statuses.wf_failure_status_code%TYPE;
9258: l_status_change_flag VARCHAR2(1) := 'N';
9259: l_start_wf VARCHAR2(1) := 'Y';
9260: l_validate_only VARCHAR2(1) := FND_API.g_false;

Line 9257: l_wf_failure_status_code pa_project_statuses.wf_failure_status_code%TYPE;

9253: l_ROWID ROWID;
9254:
9255: l_enable_wf_flag pa_project_statuses.enable_wf_flag%TYPE;
9256: l_wf_success_status_code pa_project_statuses.wf_success_status_code%TYPE;
9257: l_wf_failure_status_code pa_project_statuses.wf_failure_status_code%TYPE;
9258: l_status_change_flag VARCHAR2(1) := 'N';
9259: l_start_wf VARCHAR2(1) := 'Y';
9260: l_validate_only VARCHAR2(1) := FND_API.g_false;
9261: l_max_msg_count NUMBER := FND_API.G_MISS_NUM;

Line 9269: l_curr_system_status pa_project_statuses.project_system_status_code%TYPE;

9265: l_effort_type VARCHAR2(30) := 'PA_CI_EFFORT_LEVELS';
9266: l_source_type VARCHAR2(30) := 'PA_CI_SOURCE_TYPES';
9267: l_progress_type VARCHAR2(30) := 'PROGRESS';
9268: l_auto_numbers VARCHAR2(1);
9269: l_curr_system_status pa_project_statuses.project_system_status_code%TYPE;
9270: l_new_system_status pa_project_statuses.project_system_status_code%TYPE;
9271: l_next_allow_status_flag pa_project_statuses.next_allowable_status_flag%TYPE;
9272: l_ci_type_class_code pa_ci_types_b.ci_type_class_code%TYPE;
9273: l_approval_required_flag pa_ci_types_b.approval_required_flag%TYPE;

Line 9270: l_new_system_status pa_project_statuses.project_system_status_code%TYPE;

9266: l_source_type VARCHAR2(30) := 'PA_CI_SOURCE_TYPES';
9267: l_progress_type VARCHAR2(30) := 'PROGRESS';
9268: l_auto_numbers VARCHAR2(1);
9269: l_curr_system_status pa_project_statuses.project_system_status_code%TYPE;
9270: l_new_system_status pa_project_statuses.project_system_status_code%TYPE;
9271: l_next_allow_status_flag pa_project_statuses.next_allowable_status_flag%TYPE;
9272: l_ci_type_class_code pa_ci_types_b.ci_type_class_code%TYPE;
9273: l_approval_required_flag pa_ci_types_b.approval_required_flag%TYPE;
9274: l_resolution_check VARCHAR2(10) := 'AMG';

Line 9271: l_next_allow_status_flag pa_project_statuses.next_allowable_status_flag%TYPE;

9267: l_progress_type VARCHAR2(30) := 'PROGRESS';
9268: l_auto_numbers VARCHAR2(1);
9269: l_curr_system_status pa_project_statuses.project_system_status_code%TYPE;
9270: l_new_system_status pa_project_statuses.project_system_status_code%TYPE;
9271: l_next_allow_status_flag pa_project_statuses.next_allowable_status_flag%TYPE;
9272: l_ci_type_class_code pa_ci_types_b.ci_type_class_code%TYPE;
9273: l_approval_required_flag pa_ci_types_b.approval_required_flag%TYPE;
9274: l_resolution_check VARCHAR2(10) := 'AMG';
9275: l_resolution_req VARCHAR2(10) := 'N';

Line 9445: l_from_clause := ' FROM pa_obj_status_lists osl, pa_status_list_items sli, pa_project_statuses ps ';

9441: l_ci_status_code := p_ci_status_code;
9442:
9443:
9444: l_sel_clause := ' SELECT ps.project_status_code ';
9445: l_from_clause := ' FROM pa_obj_status_lists osl, pa_status_list_items sli, pa_project_statuses ps ';
9446: l_where := ' WHERE osl.status_type = '||'''CONTROL_ITEM'''||
9447: ' AND osl.object_type = '||'''PA_CI_TYPES'''||
9448: ' AND osl.object_id = '||l_ci_type_id||
9449: ' AND osl.status_list_id = sli.status_list_id'||

Line 9457: ' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||

9453: ' AND trunc(sysdate) between nvl(ps.start_date_active, trunc(sysdate)) and nvl(ps.end_date_active, trunc(sysdate))'||
9454: ' AND (('||''''||l_next_allow_status_flag||''''||' = '||'''N'''||' and 1=2)'||
9455: ' OR '||
9456: ' ('||''''||l_next_allow_status_flag||''''||' = '||'''S'''||
9457: ' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||
9458: ' and project_system_status_code in ( select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
9459: ''''||l_curr_status_code||''''||')))'||
9460: ' OR '||
9461: ' ('||''''||l_next_allow_status_flag||''''||' = '||'''U'''||

Line 9466: ' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||

9462: ' and ps.project_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||''''||
9463: l_curr_status_code||''''||'))'||
9464: ' OR '||
9465: ' ('||''''||l_next_allow_status_flag||''''||' = '||'''A'''||
9466: ' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||
9467: ' and project_system_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
9468: ''''||l_curr_system_status||''''||'))))'||
9469: ' AND ps.project_status_code not in (select wf_success_status_code from pa_project_statuses where status_type = '||
9470: '''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||

Line 9469: ' AND ps.project_status_code not in (select wf_success_status_code from pa_project_statuses where status_type = '||

9465: ' ('||''''||l_next_allow_status_flag||''''||' = '||'''A'''||
9466: ' and ps.project_status_code in (select project_status_code from pa_project_statuses where status_type = '||'''CONTROL_ITEM'''||
9467: ' and project_system_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
9468: ''''||l_curr_system_status||''''||'))))'||
9469: ' AND ps.project_status_code not in (select wf_success_status_code from pa_project_statuses where status_type = '||
9470: '''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
9471: ' AND ps.project_status_code not in (select wf_failure_status_code from pa_project_statuses where status_type = '||
9472: '''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
9473: ' AND decode(ps.project_system_status_code, '||'''CI_CANCELED'''||

Line 9471: ' AND ps.project_status_code not in (select wf_failure_status_code from pa_project_statuses where status_type = '||

9467: ' and project_system_status_code in (select next_allowable_status_code from pa_next_allow_statuses where status_code = '||
9468: ''''||l_curr_system_status||''''||'))))'||
9469: ' AND ps.project_status_code not in (select wf_success_status_code from pa_project_statuses where status_type = '||
9470: '''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
9471: ' AND ps.project_status_code not in (select wf_failure_status_code from pa_project_statuses where status_type = '||
9472: '''CONTROL_ITEM'''||' and wf_success_status_code is not null and wf_failure_status_code is not null)'||
9473: ' AND decode(ps.project_system_status_code, '||'''CI_CANCELED'''||
9474: ', nvl(pa_control_items_utils.CheckCIActionAllowed('||'''CONTROL_ITEM'''||', '||''''||l_curr_status_code||''''||', '||
9475: '''CONTROL_ITEM_ALLOW_CANCEL'''||', null),'||'''N'''||' ),'||'''Y'''||' ) = '||'''Y'''||