[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT PERIOD_NAME, END_DATE
INTO x_period_name,l_end_date
FROM PA_PERIODS_ALL
WHERE ORG_ID = l_fp_cols_rec.x_org_id
AND STATUS = 'C'
AND END_DATE = (SELECT MAX(END_DATE)
FROM PA_PERIODS_ALL
WHERE ORG_ID = l_fp_cols_rec.x_org_id
AND END_DATE < TRUNC(SYSDATE)
AND STATUS = 'C');
SELECT PERIOD_NAME, END_DATE
INTO x_period_name,l_end_date
FROM GL_PERIOD_STATUSES
WHERE APPLICATION_ID = PA_PERIOD_PROCESS_PKG.Application_id
AND SET_OF_BOOKS_ID = l_fp_cols_rec.x_set_of_books_id
AND ADJUSTMENT_PERIOD_FLAG = 'N'
AND CLOSING_STATUS = 'C'
AND END_DATE = (SELECT MAX(END_DATE)
FROM GL_PERIOD_STATUSES
WHERE APPLICATION_ID = PA_PERIOD_PROCESS_PKG.Application_id
AND SET_OF_BOOKS_ID = l_fp_cols_rec.x_set_of_books_id
AND ADJUSTMENT_PERIOD_FLAG = 'N'
AND END_DATE < TRUNC(SYSDATE)
AND CLOSING_STATUS = 'C');
SELECT period_name, end_date
INTO x_period_name,l_end_date
FROM pa_periods_all
WHERE end_date =
(SELECT max(end_date)
FROM pa_periods_all
WHERE org_id = l_fp_cols_rec.x_org_id
AND end_date <
(SELECT end_date
FROM pa_periods_all
WHERE trunc(sysdate) between start_date and end_date
AND org_id = l_fp_cols_rec.x_org_id) )
AND org_id = l_fp_cols_rec.x_org_id;
SELECT period_name, end_date
INTO x_period_name, l_end_date
FROM gl_period_statuses
WHERE end_date =
(SELECT max(end_date)
FROM gl_period_statuses
WHERE application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND set_of_books_id = l_fp_cols_rec.x_set_of_books_id
AND adjustment_period_flag = 'N'
AND end_date <
(SELECT end_date
FROM gl_period_statuses
WHERE trunc(sysdate) between start_date and end_date
AND APPLICATION_ID = PA_PERIOD_PROCESS_PKG.Application_id
AND SET_OF_BOOKS_ID = l_fp_cols_rec.x_set_of_books_id
AND ADJUSTMENT_PERIOD_FLAG = 'N'))
AND APPLICATION_ID = PA_PERIOD_PROCESS_PKG.Application_id
AND SET_OF_BOOKS_ID = l_fp_cols_rec.x_set_of_books_id
AND ADJUSTMENT_PERIOD_FLAG = 'N';
SELECT period_name, end_date
INTO x_period_name, l_end_date2
FROM pa_periods_all
WHERE org_id = l_fp_cols_rec.x_org_id
AND l_end_date between start_date and end_date;
SELECT period_name, end_date
INTO x_period_name, l_end_date2
FROM gl_period_statuses
WHERE APPLICATION_ID = PA_PERIOD_PROCESS_PKG.Application_id
AND set_of_books_id = l_fp_cols_rec.x_set_of_books_id
AND adjustment_period_flag = 'N'
AND l_end_date between start_date and end_date;
SELECT end_date into l_end_date1
FROM gl_period_statuses
WHERE l_end_date between start_date and end_date
AND APPLICATION_ID = PA_PERIOD_PROCESS_PKG.Application_id
AND SET_OF_BOOKS_ID = l_fp_cols_rec.x_set_of_books_id
AND ADJUSTMENT_PERIOD_FLAG = 'N';
SELECT end_date into l_end_date1
FROM pa_periods_all
WHERE l_end_date between start_date and end_date
AND org_id = l_fp_cols_rec.x_org_id;
SELECT end_date
INTO l_end_date
FROM pa_periods_all
WHERE org_id = l_fp_cols_rec.x_org_id
AND trunc(sysdate) between start_date and end_date;
SELECT end_date
INTO l_end_date
FROM gl_period_statuses
WHERE APPLICATION_ID = PA_PERIOD_PROCESS_PKG.Application_id
AND set_of_books_id = l_fp_cols_rec.x_set_of_books_id
AND adjustment_period_flag = 'N'
AND trunc(sysdate) between start_date and end_date;
SELECT p.period_name
INTO l_period_name
FROM pa_periods_all p, pa_projects_all proj
WHERE p.org_id = l_fp_cols_rec.x_org_id
AND proj.project_id = l_fp_cols_rec.x_project_id
AND proj.start_date between p.start_date and p.end_date;
SELECT g.period_name
INTO l_period_name
FROM gl_period_statuses g, pa_projects_all proj
WHERE g.application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND g.set_of_books_id = l_fp_cols_rec.x_set_of_books_id
AND g.adjustment_period_flag = 'N'
AND proj.project_id = l_fp_cols_rec.x_project_id
AND proj.start_date between g.start_date and g.end_date;
SELECT period_name
INTO l_act_to_period_name
FROM pa_periods_all
WHERE org_id = l_fp_cols_rec.x_org_id
AND l_act_to_period_date BETWEEN start_date AND end_date
AND start_date >= (SELECT start_date -- bug6142328 added one more select query for comparing the start date of act_to_period with act_from_period
FROM pa_periods_all
WHERE period_name = l_act_from_period_name
AND org_id = l_fp_cols_rec.x_org_id);
SELECT period_name
INTO l_act_to_period_name
FROM gl_period_statuses
WHERE application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND set_of_books_id = l_fp_cols_rec.x_set_of_books_id
AND adjustment_period_flag = 'N'
AND l_act_to_period_date BETWEEN start_date AND end_date
AND start_date >= (SELECT start_date FROM gl_period_statuses -- bug6142328 added one more select query for comparing the start date of act_to_period with act_from_period
WHERE application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND set_of_books_id = l_fp_cols_rec.x_set_of_books_id
AND period_name = l_act_from_period_name);
SELECT period_name
INTO l_etc_from_period_name
FROM pa_periods_all
WHERE org_id = l_fp_cols_rec.x_org_id
AND l_etc_from_period_date between start_date and end_date;
SELECT period_name
INTO l_etc_from_period_name
FROM gl_period_statuses
WHERE application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND set_of_books_id = l_fp_cols_rec.x_set_of_books_id
AND adjustment_period_flag = 'N'
AND l_etc_from_period_date between start_date and end_date;
SELECT p.period_name, NVL(proj.completion_date, trunc(SYSDATE))
INTO l_etc_to_period, l_proj_comp_date
FROM pa_periods_all p, pa_projects_all proj
WHERE NVL(proj.completion_date, trunc(SYSDATE)) between p.start_date and p.end_date
AND p.org_id = l_fp_cols_rec.x_org_id
AND proj.project_id = l_fp_cols_rec.x_project_id;
SELECT g.period_name, NVL(proj.completion_date, trunc(SYSDATE))
INTO l_etc_to_period, l_proj_comp_date
FROM gl_period_statuses g, pa_projects_all proj
WHERE g.application_id = PA_PERIOD_PROCESS_PKG.Application_id
AND g.set_of_books_id = l_fp_cols_rec.x_set_of_books_id
AND g.adjustment_period_flag = 'N'
AND proj.project_id = l_fp_cols_rec.x_project_id
AND NVL(proj.completion_date, trunc(SYSDATE)) between g.start_date and g.end_date;
SELECT VERSION_TYPE
INTO X_VERSION_TYPE
FROM PA_BUDGET_VERSIONS
WHERE BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
SELECT GEN_ETC_SOURCE_CODE,TASK_NAME
FROM PA_TASKS T
WHERE PROJECT_ID = P_PROJECT_ID;
l_last_updated_by PA_RESOURCE_ASSIGNMENTS.LAST_UPDATED_BY%TYPE;
l_last_update_login PA_RESOURCE_ASSIGNMENTS.LAST_UPDATE_LOGIN%TYPE;
SELECT COPY_ETC_FROM_PLAN_FLAG
FROM PA_PROJ_FP_OPTIONS
WHERE FIN_PLAN_VERSION_ID = P_BUDGET_VERSION_ID;
SELECT record_version_number
INTO l_record_version_number
FROM pa_budget_versions
WHERE budget_version_id = p_budget_version_id;
DELETE FROM pa_budget_lines
WHERE budget_version_id = P_BUDGET_VERSION_ID;
DELETE FROM pa_resource_assignments
WHERE budget_version_id = P_BUDGET_VERSION_ID;
P_DELETE_FLAG => 'Y',
P_VERSION_LEVEL_FLAG => 'Y',
--P_CALLED_MODE => p_called_mode,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data );
UPDATE pa_resource_assignments
SET transaction_source_code = NULL
WHERE budget_version_id = p_budget_version_id AND
transaction_source_code = 'NONE';
SELECT resource_assignment_id
BULK COLLECT INTO
l_res_asg_id_del_tab
FROM PA_RESOURCE_ASSIGNMENTS
WHERE budget_version_id = p_budget_version_id AND
transaction_source_code IS NOT NULL;
DELETE FROM PA_BUDGET_LINES
WHERE resource_assignment_id = l_res_asg_id_del_tab(i);
UPDATE PA_RESOURCE_ASSIGNMENTS
SET transaction_source_code = null
WHERE resource_assignment_id = l_res_asg_id_del_tab(j);
DELETE pa_resource_asgn_curr_tmp;
INSERT INTO pa_resource_asgn_curr_tmp (
RESOURCE_ASSIGNMENT_ID,
DELETE_FLAG )
VALUES (
l_res_asg_id_del_tab(k),
'Y' );
P_DELETE_FLAG => 'Y',
P_VERSION_LEVEL_FLAG => 'N',
--P_CALLED_MODE => p_called_mode,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data );
DELETE FROM pa_budget_lines
WHERE budget_version_id = p_budget_version_id
AND start_date <= p_actuals_thru_date;
UPDATE pa_budget_lines
SET quantity = quantity - NVL(init_quantity,0),
raw_cost = raw_cost - NVL(init_raw_cost,0),
burdened_cost = burdened_cost - NVL(init_burdened_cost,0),
revenue = revenue - NVL(init_revenue,0),
project_raw_cost = project_raw_cost - NVL(project_init_raw_cost,0),
project_burdened_cost = project_burdened_cost - NVL(project_init_burdened_cost,0),
project_revenue = project_revenue - NVL(project_init_revenue,0),
txn_raw_cost = txn_raw_cost - NVL(txn_init_raw_cost,0),
txn_burdened_cost = txn_burdened_cost - NVL(txn_init_burdened_cost,0),
txn_revenue = txn_revenue - NVL(txn_init_revenue,0)
WHERE budget_version_id = p_budget_version_id;
l_last_updated_by := FND_GLOBAL.USER_ID;
l_last_update_login := FND_GLOBAL.LOGIN_ID;
UPDATE pa_budget_lines
SET init_quantity = null,
init_raw_cost = null,
init_burdened_cost = null,
init_revenue = null,
project_init_raw_cost = null,
project_init_burdened_cost = null,
project_init_revenue = null,
txn_init_raw_cost = null,
txn_init_burdened_cost = null,
txn_init_revenue = null,
last_update_date = l_sysdate,
last_updated_by = l_last_updated_by,
last_update_login = l_last_update_login
WHERE budget_version_id = p_budget_version_id;
UPDATE PA_PROJ_FP_OPTIONS
SET GEN_COST_INCL_UNSPENT_AMT_FLAG = P_UNSPENT_AMT_FLAG,
GEN_COST_INCL_CHANGE_DOC_FLAG = P_INCL_CHG_DOC_FLAG,
GEN_COST_INCL_OPEN_COMM_FLAG = P_INCL_OPEN_CMT_FLAG,
GEN_COST_RET_MANUAL_LINE_FLAG = P_RET_MANUAL_LNS_FLAG,
GEN_SRC_COST_PLAN_TYPE_ID = P_ETC_PLAN_TYPE_ID,
GEN_SRC_COST_PLAN_VERSION_ID = l_ETC_PLAN_VERSION_ID,
GEN_SRC_COST_WP_VERSION_ID = l_wp_version_id
WHERE FIN_PLAN_VERSION_ID = P_BUDGET_VERSION_ID;
UPDATE PA_PROJ_FP_OPTIONS
SET
GEN_REV_INCL_CHANGE_DOC_FLAG = P_INCL_CHG_DOC_FLAG,
GEN_REV_INCL_BILL_EVENT_FLAG = P_INCL_BILL_EVT_FLAG,
GEN_REV_RET_MANUAL_LINE_FLAG = P_RET_MANUAL_LNS_FLAG,
GEN_SRC_REV_PLAN_TYPE_ID = P_ETC_PLAN_TYPE_ID,
GEN_SRC_REV_PLAN_VERSION_ID = l_ETC_PLAN_VERSION_ID,
GEN_SRC_REV_WP_VERSION_ID = l_wp_version_id
WHERE FIN_PLAN_VERSION_ID = P_BUDGET_VERSION_ID;
UPDATE PA_PROJ_FP_OPTIONS
SET GEN_ALL_INCL_UNSPENT_AMT_FLAG = P_UNSPENT_AMT_FLAG,
GEN_ALL_INCL_CHANGE_DOC_FLAG = P_INCL_CHG_DOC_FLAG,
GEN_ALL_INCL_OPEN_COMM_FLAG = P_INCL_OPEN_CMT_FLAG,
GEN_ALL_INCL_BILL_EVENT_FLAG = P_INCL_BILL_EVT_FLAG,
GEN_ALL_RET_MANUAL_LINE_FLAG = P_RET_MANUAL_LNS_FLAG,
GEN_SRC_ALL_PLAN_TYPE_ID = P_ETC_PLAN_TYPE_ID,
GEN_SRC_ALL_PLAN_VERSION_ID = l_ETC_PLAN_VERSION_ID,
GEN_SRC_ALL_WP_VERSION_ID = l_wp_version_id
WHERE FIN_PLAN_VERSION_ID = P_BUDGET_VERSION_ID;
UPDATE PA_BUDGET_VERSIONS
SET ACTUAL_AMTS_THRU_PERIOD = P_ACTUALS_THRU_PERIOD
WHERE BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
* updates to the budget version so that we pass the most current
* information to lower level APIs via the l_fp_cols_rec parameter. */
IF p_pa_debug_mode = 'Y' THEN
pa_fp_gen_amount_utils.fp_debug
( p_msg => 'Before calling
pa_fp_gen_amount_utils.get_plan_version_dtls',
p_module_name => l_module_name,
p_log_level => 5 );
SELECT END_DATE
INTO X_END_DATE
FROM PA_PERIODS_ALL
WHERE ORG_ID = p_fp_cols_rec.x_org_id
AND PERIOD_NAME = p_period_name;
SELECT END_DATE
INTO X_END_DATE
FROM GL_PERIOD_STATUSES
WHERE APPLICATION_ID = PA_PERIOD_PROCESS_PKG.Application_id
AND SET_OF_BOOKS_ID = p_fp_cols_rec.x_set_of_books_id
AND ADJUSTMENT_PERIOD_FLAG = 'N'
AND PERIOD_NAME = p_period_name;
SELECT bv.budget_version_id
INTO l_plan_version_id
FROM pa_budget_versions bv
WHERE bv.project_id = p_project_id
AND bv.fin_plan_type_id = p_plan_type_id
AND bv.version_name = p_plan_version_name
AND bv.version_type in ('COST','ALL');