[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT GLS.START_DATE,
GLE.END_DATE
FROM FPA_AW_PC_INFO_V PC, GL_PERIODS GLS, GL_PERIODS GLE
WHERE PC.CALENDAR_NAME = GLS.PERIOD_SET_NAME
AND PC.PERIOD_TYPE = GLS.PERIOD_TYPE
AND PC.CALENDAR_NAME = GLE.PERIOD_SET_NAME
AND PC.PERIOD_TYPE = GLE.PERIOD_TYPE
AND PC.FUNDING_PERIOD_FROM = GLS.PERIOD_NAME
AND PC.FUNDING_PERIOD_TO = GLE.PERIOD_NAME
AND PC.PLANNING_CYCLE = PC_ID;
SELECT 'T'
FROM PA_PROJECTS_ALL P, PA_BUDGET_VERSIONS V,
PA_BUDGET_LINES L
WHERE
V.BUDGET_VERSION_ID = L.BUDGET_VERSION_ID
AND V.BUDGET_STATUS_CODE = 'B'
AND V.CURRENT_FLAG = 'Y'
AND V.PROJECT_ID = P.PROJECT_ID
AND P.PROJECT_ID = P_PROJECT_ID
AND V.FIN_PLAN_TYPE_ID = P_PLAN_TYPE
AND ((P_START_DATE BETWEEN L.START_DATE AND L.END_DATE)
OR (P_END_DATE BETWEEN L.START_DATE AND L.END_DATE));
select
fnd_profile.value('PJP_FINANCIAL_PLAN_TYPE_COST'),
fnd_profile.value('PJP_FINANCIAL_PLAN_TYPE_BENEFIT')
into l_cost_plan_type, l_benefit_plan_type from dual;
SELECT
C.VALIDATION_ID,
B.VALIDATION_ID
INTO
L_COST_VLINE_ID, L_BENEFIT_VLINE_ID
FROM
FPA_VALIDATION_LINES S,
FPA_VALIDATION_LINES P,
FPA_VALIDATION_LINES C,
FPA_VALIDATION_LINES B
WHERE
S.VALIDATION_ID = P_SCEN_VLINE_ID
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 P.OBJECT_ID = P_PROJECT_ID;
DELETE FROM FPA_VALIDATION_LINES
WHERE VALIDATION_ID IN (L_COST_VLINE_ID, L_BENEFIT_VLINE_ID);
PROCEDURE Update_Proj_Budget_Versions
(
p_api_version IN NUMBER,
p_init_msg_list IN VARCHAR2,
p_validation_set IN VARCHAR2,
p_scen_vline_id IN NUMBER,
p_project_id IN NUMBER,
p_cost_bversion_id IN NUMBER,
p_benefit_bversion_id IN NUMBER,
x_return_status OUT NOCOPY VARCHAR2,
x_msg_count OUT NOCOPY NUMBER,
x_msg_data OUT NOCOPY VARCHAR2
) IS
-- standard parameters
l_return_status VARCHAR2(1);
l_api_name CONSTANT VARCHAR2(30) := 'Update_Proj_Budget_Versions';
SELECT
C.VALIDATION_ID,
B.VALIDATION_ID
INTO
L_COST_VLINE_ID, L_BENEFIT_VLINE_ID
FROM
FPA_VALIDATION_LINES S,
FPA_VALIDATION_LINES P,
FPA_VALIDATION_LINES C,
FPA_VALIDATION_LINES B
WHERE
S.VALIDATION_ID = P_SCEN_VLINE_ID
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 P.OBJECT_ID = P_PROJECT_ID;
UPDATE
FPA_VALIDATION_LINES
SET
OBJECT_ID = P_COST_BVERSION_ID,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE
VALIDATION_ID = L_COST_VLINE_ID;
UPDATE
FPA_VALIDATION_LINES
SET
OBJECT_ID = P_BENEFIT_BVERSION_ID,
LAST_UPDATED_BY = FND_GLOBAL.USER_ID,
LAST_UPDATE_DATE = SYSDATE,
LAST_UPDATE_LOGIN = FND_GLOBAL.LOGIN_ID
WHERE
VALIDATION_ID = L_BENEFIT_VLINE_ID;
END Update_Proj_Budget_Versions;
SELECT
S.PROJECT PROJECT,
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
FROM
PA_BUDGET_VERSIONS C, PA_BUDGET_VERSIONS B,
FPA_AW_PROJ_INFO_V S
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 S.PROJECT = C.PROJECT_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 S.PROJECT = B.PROJECT_ID (+)
AND S.SCENARIO = P_SCENARIO_ID;
SELECT SCENARIO INTO L_SCENARIO_ID
FROM FPA_AW_SCE_INFO_V
WHERE IS_INITIAL_SCENARIO = 1 AND PLANNING_CYCLE = P_HEADER_OBJECT_ID;
SELECT VALIDATION_ID
FROM FPA_VALIDATION_LINES
WHERE OBJECT_TYPE = 'BUDGET_VERSIONS_SCENARIO' AND
OBJECT_ID = sce_id AND
HEADER_ID IS NULL AND
VALIDATION_TYPE = validation_set;
SELECT SCENARIO INTO L_SCENARIO_ID
FROM FPA_AW_SCE_INFO_V
WHERE IS_INITIAL_SCENARIO = 1 AND PLANNING_CYCLE = P_HEADER_OBJECT_ID;
SELECT VALIDATION_ID
INTO L_SCEN_VALIDATION_ID
FROM FPA_VALIDATION_LINES
WHERE OBJECT_TYPE = 'BUDGET_VERSIONS_SCENARIO' AND
OBJECT_ID = L_SCENARIO_ID AND
HEADER_ID IS NULL AND
VALIDATION_TYPE = P_VALIDATION_SET;
l_sql_str := '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 '
|| '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 = :1';
'Entering loop for insert or updates into FPA_VALIDATION_LINES. Operation to be performed is: ' || l_type);
ELSIF(l_type = 'UPDATE') THEN
IF FND_LOG.LEVEL_PROCEDURE >= FND_LOG.G_CURRENT_RUNTIME_LEVEL THEN
fnd_log.string ( FND_LOG.LEVEL_PROCEDURE,
'fpa.sql.FPA_VALIDATION_PROCESS_PVT.Budget_Version_Validations 2.',
'Calling procedure Update_Proj_Budget_Versions.');
Update_Proj_Budget_Versions(
p_api_version => l_api_version,
p_init_msg_list => l_init_msg_list,
p_validation_set => p_validation_set,
p_scen_vline_id => l_scen_validation_id,
p_project_id => p_line_projects_tbl(i),
p_cost_bversion_id => l_cost_bversion_id,
p_benefit_bversion_id => l_benefit_bversion_id,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
'Checking return status inside loop for inserting creating FPA_VALIDATION_LINES.');
'Finished loop for inserting updating FPA_VALIDATION_LINES.');
SELECT
PROJECT
FROM FPA_AW_PROJS_V
WHERE PLANNING_CYCLE = P_PC_ID;
SELECT 'T'
FROM PA_PROJECTS_ALL P, PA_BUDGET_VERSIONS V,
PA_BUDGET_LINES L
WHERE
V.BUDGET_VERSION_ID = L.BUDGET_VERSION_ID
AND V.BUDGET_STATUS_CODE = 'B'
AND V.CURRENT_FLAG = 'Y'
AND V.PROJECT_ID = P.PROJECT_ID
AND P.PROJECT_ID = P_PROJECT_ID
AND V.FIN_PLAN_TYPE_ID = P_PLAN_TYPE;
SELECT
FND_PROFILE.VALUE('PJP_FINANCIAL_PLAN_TYPE_COST'),
FND_PROFILE.VALUE('PJP_FINANCIAL_PLAN_TYPE_BENEFIT')
INTO L_COST_PLAN_TYPE, L_BENEFIT_PLAN_TYPE FROM DUAL;
SELECT PLANNING_CYCLE INTO L_PC_ID
FROM FPA_AW_SCES_V WHERE SCENARIO = P_SCENARIO_ID;
SELECT NAME INTO L_OBJECT_NAME
FROM PA_PROJECTS_ALL WHERE PROJECT_ID = P_OBJECT_ID;
SELECT NAME INTO L_OBJECT_NAME
FROM FPA_OBJECTS_TL WHERE ID = P_OBJECT_ID
AND LANGUAGE = USERENV('LANG'); -- Bug Ref # 6327682;