[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_SELECTION_CATEGORY VARCHAR2(200) := FND_PROFILE.VALUE('PJP_PORTFOLIO_CLASS_CATEGORY');
SELECT
'T'
FROM PA_PROJECTS_ALL
WHERE PROJECT_ID = P_PROJECT_ID
AND FUNDING_APPROVAL_STATUS_CODE IN
('FUNDING_PROPOSED','FUNDING_ONHOLD','FUNDING_APPROVED');
SELECT
ORG_STRUCTURE_VERSION_ID
FROM
PER_ORG_STRUCTURE_VERSIONS
WHERE
ORGANIZATION_STRUCTURE_ID = G_PJP_ORGS_HIER
AND (TRUNC(SYSDATE) BETWEEN TRUNC(DATE_FROM) AND TRUNC(NVL(DATE_TO,
SYSDATE)));
SELECT
PTF.PORTFOLIO,
PAC.CLASS_CODE,
PTF.PORTFOLIO_ORGANIZATION,
PA.CARRYING_OUT_ORGANIZATION_ID
FROM
PA_PROJECT_CLASSES PAC,
PA_PROJECTS_ALL PA,
PA_CLASS_CODES PCC,
FPA_AW_PORTF_HEADERS_V PTF
WHERE
PTF.PORTFOLIO_CLASS_CODE = PCC.CLASS_CODE_ID
AND PAC.CLASS_CODE = PCC.CLASS_CODE
AND PAC.CLASS_CATEGORY = PCC.CLASS_CATEGORY
AND PCC.CLASS_CATEGORY = G_SELECTION_CATEGORY
AND PAC.PROJECT_ID = PA.PROJECT_ID
AND PA.PROJECT_ID = P_PROJECT_ID
AND (PTF.PORTFOLIO_ORGANIZATION IS NULL
OR (PTF.PORTFOLIO_ORGANIZATION IS NOT NULL
AND PA.CARRYING_OUT_ORGANIZATION_ID IN
(
SELECT
ORGANIZATION_ID_CHILD
FROM
PER_ORG_STRUCTURE_ELEMENTS
WHERE
ORG_STRUCTURE_VERSION_ID = P_PJP_ORG_VERSION_ID
CONNECT BY PRIOR ORGANIZATION_ID_CHILD = ORGANIZATION_ID_PARENT
AND PRIOR ORG_STRUCTURE_VERSION_ID = P_PJP_ORG_VERSION_ID
START WITH ORGANIZATION_ID_PARENT = PTF.PORTFOLIO_ORGANIZATION
OR ORGANIZATION_ID_CHILD = PTF.PORTFOLIO_ORGANIZATION
UNION
SELECT PTF.PORTFOLIO_ORGANIZATION FROM dual --added for bug 6086945
))); -- IN, OR , AND
SELECT
PC.PLANNING_CYCLE,
CC.CLASS_CODE_ID
FROM
FPA_AW_PC_INFO_V PC, PA_CLASS_CATEGORIES PCC,
PA_PROJECT_CLASSES PAC, PA_CLASS_CODES CC
WHERE
PC.PC_STATUS IN ('COLLECTING', 'ANALYSIS')
AND PC.PC_CATEGORY = PCC.CLASS_CATEGORY_ID
AND PAC.CLASS_CATEGORY = PCC.CLASS_CATEGORY
AND PCC.CLASS_CATEGORY = CC.CLASS_CATEGORY
AND CC.CLASS_CODE = PAC.CLASS_CODE
AND PC.PORTFOLIO = P_PORTFOLIO_ID
AND PAC.PROJECT_ID = P_PROJECT_ID;
select project
from fpa_aw_proj_info_v
where scenario = p_scenario_id;
select p_project_id
from dual;
SELECT
DECODE(C.BUDGET_VERSION_ID,NULL,-1,C.BUDGET_VERSION_ID) COST_BUDGET_VERSN_ID,
DECODE(B.BUDGET_VERSION_ID,NULL,-1,B.BUDGET_VERSION_ID) BENF_BUDGET_VERSN_ID
INTO
L_NEW_COST_VERSION_ID, L_NEW_BENEFIT_VERSION_ID
FROM
PA_PROJECTS_ALL P, PA_BUDGET_VERSIONS C, PA_BUDGET_VERSIONS B
WHERE
'B' = C.BUDGET_STATUS_CODE (+) AND 'Y' = C.CURRENT_FLAG (+)
AND fnd_profile.value('PJP_FINANCIAL_PLAN_TYPE_COST') = C.FIN_PLAN_TYPE_ID (+)
AND 'B' = B.BUDGET_STATUS_CODE (+) AND 'Y' = B.CURRENT_FLAG (+)
AND fnd_profile.value('PJP_FINANCIAL_PLAN_TYPE_BENEFIT') = B.FIN_PLAN_TYPE_ID (+)
AND P.PROJECT_ID = C.PROJECT_ID (+) AND P.PROJECT_ID = B.PROJECT_ID (+)
AND P.PROJECT_ID = new_projs(i).project_id;
SELECT
C.OBJECT_ID BUDGET_VERSION_COST,
B.OBJECT_ID BUDGET_VERSION_BENEFIT
INTO
L_COST_VERSION_ID, L_BENEFIT_VERSION_ID
FROM
FPA_VALIDATION_LINES S,
FPA_VALIDATION_LINES P,
FPA_VALIDATION_LINES C,
FPA_VALIDATION_LINES B
WHERE
S.OBJECT_TYPE = 'BUDGET_VERSIONS_SCENARIO'
AND S.HEADER_ID IS NULL
AND P.OBJECT_TYPE = 'BUDGET_VERSIONS_PROJ'
AND P.HEADER_ID = S.VALIDATION_ID
AND C.OBJECT_TYPE = 'BUDGET_VERSION_COST'
AND C.HEADER_ID = P.VALIDATION_ID
AND C.VALIDATION_TYPE = 'FPA_V_PROJ_COST_VERSION'
AND B.OBJECT_TYPE = 'BUDGET_VERSION_BENEFIT'
AND B.HEADER_ID = P.VALIDATION_ID
AND B.VALIDATION_TYPE = 'FPA_V_PROJ_BENEFIT_VERSION'
AND S.OBJECT_ID = P_SCENARIO_ID
AND P.OBJECT_ID = new_projs(i).project_id;
dbms_aw.execute('UPDATE');
p_delete_project_id => null,
x_return_status => x_return_status,
x_msg_count => x_msg_count,
x_msg_data => x_msg_data);
SELECT PLANNING_CYCLE INTO l_pc_id
FROM FPA_AW_SCES_V WHERE SCENARIO = p_scenario_id;
select page_id, pers_function_name
from pa_page_layouts
where page_id = 51;
select pap.page_id, obj.pers_function_name
from pa_page_layouts pap, pa_object_page_layouts obj
where obj.OBJECT_TYPE = 'PA_PROJECTS'
and obj.object_id = p_planning_cycle_id
and pap.page_id = obj.page_id;
select pap.page_id
from pa_page_layouts pap, pa_object_page_layouts obj
where obj.OBJECT_TYPE = 'PA_PROJECTS'
and obj.object_id = p_planning_cycle_id
and pap.page_id = obj.page_id;
select page_id, pers_function_name
from pa_page_layouts
where page_id = 51;
select pap.page_id, obj.pers_function_name
from pa_page_layouts pap, pa_object_page_layouts obj
where obj.OBJECT_TYPE = 'PA_PROJECTS'
and obj.object_id = p_planning_cycle_id
and pap.page_id = obj.page_id;
PROCEDURE UPDATE_PROJ_FUNDING_STATUS
( p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_commit IN VARCHAR2,
p_appr_scenario_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2) IS
cursor c_approved_projects IS
select ppa.rowid, project_id, recommended_funding_status
from pa_projects_all ppa, fpa_aw_proj_info_v sceproj
where scenario = p_appr_scenario_id and sceproj.project = ppa.project_id;
'fpa.sql.FPA_PROJECT_PVT.UPDATE_PROJ_FUNDING_STATUS.begin',
'Entering FPA_PROJECT_PVT.UPDATE_PROJ_FUNDING_STATUS'
);
UPDATE PA_PROJECTS_ALL
SET FUNDING_APPROVAL_STATUS_CODE = l_approved_projects.recommended_funding_status
WHERE ROWID = l_approved_projects.rowid;
'fpa.sql.FPA_PROJECT_PVT.UPDATE_PROJ_FUNDING_STATUS.end',
'Entering FPA_PROJECT_PVT.UPDATE_PROJ_FUNDING_STATUS'
);
p_procedure_name => 'UPDATE_PROJ_FUNDING_STATUS',
p_error_text => SUBSTRB(SQLERRM,1,240));
p_procedure_name => 'UPDATE_PROJ_FUNDING_STATUS',
p_error_text => SUBSTRB(SQLERRM,1,240));
END UPDATE_PROJ_FUNDING_STATUS;