[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
G_PVT_DELETE CONSTANT VARCHAR2(30) := 'DELETE_TABLE_RECORDS';
G_PVT_INSERT CONSTANT VARCHAR2(30) := 'INSERT_TABLE_RECORDS';
G_UPDATE_PLAN_TRANSACTION,
G_WORKPLAN,
G_AMG_API,
G_WEBADI,
G_CHANGE_MGT,
G_COPY_PLAN,
G_UPGRADE );
G_PVT_DELETE,
G_PVT_COPY,
G_PVT_INSERT,
G_PVT_ROLLUP );
P_DELETE_FLAG IN VARCHAR2 DEFAULT NULL,
P_COPY_FLAG IN VARCHAR2 DEFAULT NULL,
P_SRC_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE DEFAULT NULL,
P_COPY_MODE IN VARCHAR2 DEFAULT NULL,
P_ROLLUP_FLAG IN VARCHAR2 DEFAULT NULL,
P_VERSION_LEVEL_FLAG IN VARCHAR2 DEFAULT NULL,
P_CALLED_MODE IN VARCHAR2 DEFAULT NULL );
PROCEDURE DELETE_TABLE_RECORDS
( P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
P_CALLING_MODULE IN VARCHAR2,
P_VERSION_LEVEL_FLAG IN VARCHAR2 DEFAULT 'N',
P_CALLED_MODE IN VARCHAR2 DEFAULT 'SELF_SERVICE',
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2);
PROCEDURE INSERT_TABLE_RECORDS
( P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
P_CALLING_MODULE IN VARCHAR2,
P_VERSION_LEVEL_FLAG IN VARCHAR2 DEFAULT 'N',
P_CALLED_MODE IN VARCHAR2 DEFAULT 'SELF_SERVICE',
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2);
P_DELETE_FLAG IN VARCHAR2,
P_COPY_FLAG IN VARCHAR2,
P_SRC_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
P_COPY_MODE IN VARCHAR2,
P_ROLLUP_FLAG IN VARCHAR2,
P_VERSION_LEVEL_FLAG IN VARCHAR2,
P_CALLED_MODE IN VARCHAR2,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2 )
IS
l_module_name VARCHAR2(100) := 'pa.plsql.PA_RES_ASG_CURRENCY_PUB.MAINTAIN_DATA';
P_DELETE_FLAG => p_delete_flag,
P_COPY_FLAG => p_copy_flag,
P_SRC_VERSION_ID => p_src_version_id,
P_COPY_MODE => p_copy_mode,
P_ROLLUP_FLAG => p_rollup_flag,
P_VERSION_LEVEL_FLAG => p_version_level_flag,
P_CALLED_MODE => p_called_mode );
IF NOT is_valid_flag(p_delete_flag) THEN
IF p_pa_debug_mode = 'Y' THEN
PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG
( P_MSG => 'Invalid p_delete_flag value: '
|| p_delete_flag,
P_CALLED_MODE => p_called_mode,
P_MODULE_NAME => l_module_name,
P_LOG_LEVEL => l_log_level );
END IF; -- p_delete_flag validation
IF p_delete_flag = 'Y' THEN
IF p_pa_debug_mode = 'Y' THEN
PA_FP_GEN_AMOUNT_UTILS.FP_DEBUG(
P_MSG => 'Before calling PA_RES_ASG_CURRENCY_PUB.' ||
'DELETE_TABLE_RECORDS',
P_CALLED_MODE => p_called_mode,
P_MODULE_NAME => l_module_name);
PA_RES_ASG_CURRENCY_PUB.DELETE_TABLE_RECORDS
( P_FP_COLS_REC => p_fp_cols_rec,
P_CALLING_MODULE => p_calling_module,
P_VERSION_LEVEL_FLAG => p_version_level_flag,
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_TABLE_RECORDS: '||x_return_status,
P_CALLED_MODE => p_called_mode,
P_MODULE_NAME => l_module_name);
END IF; -- p_delete_flag = 'Y'
DELETE FROM pa_resource_asgn_curr_tmp
WHERE NVL(delete_flag,'N') = 'Y';
SELECT resource_assignment_id,
NULL,
count(*)
BULK COLLECT
INTO l_ra_id_tab,
l_txn_currency_code_tab,
l_duplicate_count_tab
FROM pa_resource_asgn_curr_tmp
GROUP BY resource_assignment_id
HAVING count(*) > 1;
SELECT resource_assignment_id,
txn_currency_code,
count(*)
BULK COLLECT
INTO l_ra_id_tab,
l_txn_currency_code_tab,
l_duplicate_count_tab
FROM pa_resource_asgn_curr_tmp
GROUP BY resource_assignment_id, txn_currency_code
HAVING count(*) > 1;
SELECT count(*)
INTO l_null_record_count
FROM pa_resource_asgn_curr_tmp
WHERE resource_assignment_id IS NULL;
SELECT count(*)
INTO l_null_record_count
FROM pa_resource_asgn_curr_tmp
WHERE txn_currency_code IS NULL;
'INSERT_TABLE_RECORDS',
P_CALLED_MODE => p_called_mode,
P_MODULE_NAME => l_module_name);
PA_RES_ASG_CURRENCY_PUB.INSERT_TABLE_RECORDS
( P_FP_COLS_REC => p_fp_cols_rec,
P_CALLING_MODULE => p_calling_module,
P_VERSION_LEVEL_FLAG => p_version_level_flag,
P_CALLED_MODE => p_called_mode,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data );
'INSERT_TABLE_RECORDS: '||x_return_status,
P_CALLED_MODE => p_called_mode,
P_MODULE_NAME => l_module_name);
P_DELETE_FLAG IN VARCHAR2,
P_COPY_FLAG IN VARCHAR2,
P_SRC_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
P_COPY_MODE IN VARCHAR2,
P_ROLLUP_FLAG IN VARCHAR2,
P_VERSION_LEVEL_FLAG IN VARCHAR2,
P_CALLED_MODE IN VARCHAR2 )
IS
l_module_name VARCHAR2(100) := 'pa.plsql.PA_RES_ASG_CURRENCY_PUB.PRINT_INPUT_PARAMS';
( p_msg => 'P_DELETE_FLAG:['||P_DELETE_FLAG||']',
p_called_mode => p_called_mode,
p_module_name => l_module_name,
p_log_level => l_log_level );
l_dbg_delete_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp;
SELECT resource_assignment_id,
txn_currency_code,
txn_raw_cost_rate_override,
txn_burden_cost_rate_override,
txn_bill_rate_override,
delete_flag
BULK COLLECT
INTO l_dbg_ra_id_tab,
l_dbg_txn_currency_code_tab,
l_dbg_rc_rate_override_tab,
l_dbg_bc_rate_override_tab,
l_dbg_bill_rate_override_tab,
l_dbg_delete_flag_tab
FROM pa_resource_asgn_curr_tmp
ORDER BY resource_assignment_id, txn_currency_code;
|| 'delete_flag:['||l_dbg_delete_flag_tab(i)||']',
p_called_mode => p_called_mode,
p_module_name => l_module_name,
p_log_level => l_log_level );
PROCEDURE DELETE_TABLE_RECORDS
( P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
P_CALLING_MODULE IN VARCHAR2,
P_VERSION_LEVEL_FLAG IN VARCHAR2,
P_CALLED_MODE IN VARCHAR2,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2)
IS
l_module_name VARCHAR2(100) := 'pa.plsql.PA_RES_ASG_CURRENCY_PUB.DELETE_TABLE_RECORDS';
( p_function => 'DELETE_TABLE_RECORDS',
p_debug_mode => p_pa_debug_mode );
( P_CALLING_API => G_PVT_DELETE,
P_FP_COLS_REC => p_fp_cols_rec,
P_CALLING_MODULE => p_calling_module,
P_VERSION_LEVEL_FLAG => p_version_level_flag,
P_CALLED_MODE => p_called_mode );
DELETE FROM pa_resource_asgn_curr rbc
WHERE rbc.budget_version_id = p_fp_cols_rec.x_budget_version_id;
DELETE FROM pa_resource_asgn_curr rbc
WHERE rbc.budget_version_id = p_fp_cols_rec.x_budget_version_id
AND EXISTS ( SELECT null
FROM pa_resource_asgn_curr_tmp tmp
WHERE NVL(tmp.delete_flag,'N') = 'Y'
AND rbc.resource_assignment_id = tmp.resource_assignment_id
AND rbc.txn_currency_code =
NVL(tmp.txn_currency_code,rbc.txn_currency_code) );
/* replaced the delete statement with the one below for bug 14040849
DELETE FROM pa_resource_asgn_curr rbc
WHERE rbc.budget_version_id = p_fp_cols_rec.x_budget_version_id
AND EXISTS ( SELECT null
FROM pa_resource_asgn_curr_tmp tmp
WHERE rbc.resource_assignment_id = tmp.resource_assignment_id
AND rbc.txn_currency_code =
NVL(tmp.txn_currency_code,rbc.txn_currency_code) );
DELETE FROM PA_RESOURCE_ASGN_CURR RBC
where rbc.rowid in (
select /*+ leading(tmp) cardinality(tmp,1) INDEX(RBC1 PA_RESOURCE_ASGN_CURR_U2) */ rbc1.rowid
from PA_RESOURCE_ASGN_CURR RBC1,
PA_RESOURCE_ASGN_CURR_TMP TMP
where RBC1.BUDGET_VERSION_ID = p_fp_cols_rec.x_budget_version_id
AND RBC1.RESOURCE_ASSIGNMENT_ID = TMP.RESOURCE_ASSIGNMENT_ID
AND RBC1.TXN_CURRENCY_CODE = NVL(TMP.TXN_CURRENCY_CODE,RBC1.TXN_CURRENCY_CODE));
p_procedure_name => 'DELETE_TABLE_RECORDS',
p_error_text => substr(sqlerrm,1,240));
END DELETE_TABLE_RECORDS;
l_last_updated_by NUMBER := FND_GLOBAL.user_id;
l_last_update_login NUMBER := FND_GLOBAL.login_id;
SELECT tgt_ra.RESOURCE_ASSIGNMENT_ID,
src_rbc.TXN_CURRENCY_CODE,
src_rbc.TOTAL_QUANTITY,
src_rbc.TOTAL_INIT_QUANTITY,
src_rbc.TXN_RAW_COST_RATE_OVERRIDE,
src_rbc.TXN_BURDEN_COST_RATE_OVERRIDE,
src_rbc.TXN_BILL_RATE_OVERRIDE,
src_rbc.TXN_AVERAGE_RAW_COST_RATE,
src_rbc.TXN_AVERAGE_BURDEN_COST_RATE,
src_rbc.TXN_AVERAGE_BILL_RATE,
src_rbc.TXN_ETC_RAW_COST_RATE,
src_rbc.TXN_ETC_BURDEN_COST_RATE,
src_rbc.TXN_ETC_BILL_RATE,
src_rbc.TOTAL_TXN_RAW_COST,
src_rbc.TOTAL_TXN_BURDENED_COST,
src_rbc.TOTAL_TXN_REVENUE,
src_rbc.TOTAL_TXN_INIT_RAW_COST,
src_rbc.TOTAL_TXN_INIT_BURDENED_COST,
src_rbc.TOTAL_TXN_INIT_REVENUE,
src_rbc.TOTAL_PROJECT_RAW_COST,
src_rbc.TOTAL_PROJECT_BURDENED_COST,
src_rbc.TOTAL_PROJECT_REVENUE,
src_rbc.TOTAL_PROJECT_INIT_RAW_COST,
src_rbc.TOTAL_PROJECT_INIT_BD_COST,
src_rbc.TOTAL_PROJECT_INIT_REVENUE,
src_rbc.TOTAL_PROJFUNC_RAW_COST,
src_rbc.TOTAL_PROJFUNC_BURDENED_COST,
src_rbc.TOTAL_PROJFUNC_REVENUE,
src_rbc.TOTAL_PROJFUNC_INIT_RAW_COST,
src_rbc.TOTAL_PROJFUNC_INIT_BD_COST,
src_rbc.TOTAL_PROJFUNC_INIT_REVENUE,
src_rbc.TOTAL_DISPLAY_QUANTITY
FROM pa_resource_assignments tgt_ra,
pa_resource_assignments src_ra,
pa_resource_asgn_curr src_rbc
WHERE src_rbc.budget_version_id = c_src_version_id
AND src_ra.resource_assignment_id = src_rbc.resource_assignment_id
AND nvl(tgt_ra.task_id,0) = nvl(src_ra.task_id,0)
AND tgt_ra.resource_list_member_id = src_ra.resource_list_member_id
AND nvl(tgt_ra.cbs_element_id,-1) = nvl(src_ra.cbs_element_id,-1) --bug#16791711
AND tgt_ra.budget_version_id = c_tgt_version_id;
SELECT tgt_ra.RESOURCE_ASSIGNMENT_ID,
src_rbc.TXN_CURRENCY_CODE,
src_rbc.TXN_RAW_COST_RATE_OVERRIDE,
src_rbc.TXN_BURDEN_COST_RATE_OVERRIDE,
src_rbc.TXN_BILL_RATE_OVERRIDE
FROM pa_resource_assignments tgt_ra,
pa_resource_assignments src_ra,
pa_resource_asgn_curr src_rbc
WHERE src_rbc.budget_version_id = c_src_version_id
AND src_ra.resource_assignment_id = src_rbc.resource_assignment_id
AND nvl(tgt_ra.task_id,0) = nvl(src_ra.task_id,0)
AND tgt_ra.resource_list_member_id = src_ra.resource_list_member_id
AND nvl(tgt_ra.cbs_element_id,-1) = nvl(src_ra.cbs_element_id,-1) --bug#16791711
AND tgt_ra.budget_version_id = c_tgt_version_id;
SELECT DISTINCT /*+ ORDERED */
tmp.RESOURCE_ASSIGNMENT_ID,
src_rbc.TXN_CURRENCY_CODE,
src_rbc.TXN_RAW_COST_RATE_OVERRIDE,
src_rbc.TXN_BURDEN_COST_RATE_OVERRIDE,
src_rbc.TXN_BILL_RATE_OVERRIDE
FROM pa_resource_asgn_curr src_rbc,
pa_resource_assignments src_ra,
pa_resource_assignments tgt_ra,
pa_resource_asgn_curr_tmp tmp
WHERE tgt_ra.budget_version_id = c_tgt_version_id
AND src_ra.budget_version_id = c_src_version_id
AND tgt_ra.resource_assignment_id = tmp.resource_assignment_id
AND nvl(src_ra.task_id,0) = nvl(tgt_ra.task_id,0)
AND src_ra.resource_list_member_id = tgt_ra.resource_list_member_id
AND nvl(tgt_ra.cbs_element_id,-1) = nvl(src_ra.cbs_element_id,-1) --bug#16791711
AND src_rbc.resource_assignment_id = src_ra.resource_assignment_id
AND src_rbc.budget_version_id = src_ra.budget_version_id;
SELECT nvl(wp_version_flag,'N')
INTO l_wp_version_flag
FROM pa_budget_versions
WHERE budget_version_id = p_fp_cols_rec.x_budget_version_id;
l_null_NumTabTyp.delete;
DELETE PA_RESOURCE_ASGN_CURR_TMP;
INSERT INTO PA_RESOURCE_ASGN_CURR_TMP (
RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE )
VALUES (
l_ra_id_tab(i),
l_txn_currency_code_tab(i) );
|| 'DELETE_TABLE_RECORDS',
P_CALLED_MODE => p_called_mode,
P_MODULE_NAME => l_module_name,
P_LOG_LEVEL => l_log_level );
PA_RES_ASG_CURRENCY_PUB.DELETE_TABLE_RECORDS
( P_FP_COLS_REC => p_fp_cols_rec,
P_CALLING_MODULE => G_PVT_COPY,
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_TABLE_RECORDS: ' || x_return_status,
P_CALLED_MODE => p_called_mode,
P_MODULE_NAME => l_module_name,
P_LOG_LEVEL => l_log_level );
INSERT INTO PA_RESOURCE_ASGN_CURR (
RA_TXN_ID,
BUDGET_VERSION_ID,
RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
TOTAL_QUANTITY,
TOTAL_INIT_QUANTITY,
TXN_RAW_COST_RATE_OVERRIDE,
TXN_BURDEN_COST_RATE_OVERRIDE,
TXN_BILL_RATE_OVERRIDE,
TXN_AVERAGE_RAW_COST_RATE,
TXN_AVERAGE_BURDEN_COST_RATE,
TXN_AVERAGE_BILL_RATE,
TXN_ETC_RAW_COST_RATE,
TXN_ETC_BURDEN_COST_RATE,
TXN_ETC_BILL_RATE,
TOTAL_TXN_RAW_COST,
TOTAL_TXN_BURDENED_COST,
TOTAL_TXN_REVENUE,
TOTAL_TXN_INIT_RAW_COST,
TOTAL_TXN_INIT_BURDENED_COST,
TOTAL_TXN_INIT_REVENUE,
TOTAL_PROJECT_RAW_COST,
TOTAL_PROJECT_BURDENED_COST,
TOTAL_PROJECT_REVENUE,
TOTAL_PROJECT_INIT_RAW_COST,
TOTAL_PROJECT_INIT_BD_COST,
TOTAL_PROJECT_INIT_REVENUE,
TOTAL_PROJFUNC_RAW_COST,
TOTAL_PROJFUNC_BURDENED_COST,
TOTAL_PROJFUNC_REVENUE,
TOTAL_PROJFUNC_INIT_RAW_COST,
TOTAL_PROJFUNC_INIT_BD_COST,
TOTAL_PROJFUNC_INIT_REVENUE,
TOTAL_DISPLAY_QUANTITY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
RECORD_VERSION_NUMBER )
VALUES (
pa_resource_asgn_curr_s.nextval,
p_fp_cols_rec.x_budget_version_id,
l_ra_id_tab(i),
l_txn_currency_code_tab(i),
l_total_quantity_tab(i),
l_total_init_quantity_tab(i),
l_raw_cost_rate_override_tab(i),
l_brdn_cost_rate_override_tab(i),
l_bill_rate_override_tab(i),
l_avg_raw_cost_rate_tab(i),
l_avg_burden_cost_rate_tab(i),
l_avg_bill_rate_tab(i),
l_etc_raw_cost_rate_tab(i),
l_etc_burden_cost_rate_tab(i),
l_etc_bill_rate_tab(i),
l_txn_raw_cost_tab(i),
l_txn_burdened_cost_tab(i),
l_txn_revenue_tab(i),
l_txn_init_raw_cost_tab(i),
l_txn_init_burdened_cost_tab(i),
l_txn_init_revenue_tab(i),
l_pc_raw_cost_tab(i),
l_pc_burdened_cost_tab(i),
l_pc_revenue_tab(i),
l_pc_init_raw_cost_tab(i),
l_pc_init_burdened_cost_tab(i),
l_pc_init_revenue_tab(i),
l_pfc_raw_cost_tab(i),
l_pfc_burdened_cost_tab(i),
l_pfc_revenue_tab(i),
l_pfc_init_raw_cost_tab(i),
l_pfc_init_burdened_cost_tab(i),
l_pfc_init_revenue_tab(i),
l_display_quantity_tab(i),
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_record_version_number );
INSERT INTO PA_RESOURCE_ASGN_CURR (
RA_TXN_ID,
BUDGET_VERSION_ID,
RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
TXN_RAW_COST_RATE_OVERRIDE,
TXN_BURDEN_COST_RATE_OVERRIDE,
TXN_BILL_RATE_OVERRIDE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
RECORD_VERSION_NUMBER )
VALUES (
pa_resource_asgn_curr_s.nextval,
p_fp_cols_rec.x_budget_version_id,
l_ra_id_tab(i),
l_txn_currency_code_tab(i),
l_raw_cost_rate_override_tab(i),
l_brdn_cost_rate_override_tab(i),
l_bill_rate_override_tab(i),
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_record_version_number );
PROCEDURE INSERT_TABLE_RECORDS
( P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
P_CALLING_MODULE IN VARCHAR2,
P_VERSION_LEVEL_FLAG IN VARCHAR2,
P_CALLED_MODE IN VARCHAR2,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2)
IS
l_module_name VARCHAR2(100) := 'pa.plsql.PA_RES_ASG_CURRENCY_PUB.INSERT_TABLE_RECORDS';
l_last_updated_by NUMBER := FND_GLOBAL.user_id;
l_last_update_login NUMBER := FND_GLOBAL.login_id;
( p_function => 'INSERT_TABLE_RECORDS',
p_debug_mode => p_pa_debug_mode );
( P_CALLING_API => G_PVT_INSERT,
P_FP_COLS_REC => p_fp_cols_rec,
P_CALLING_MODULE => p_calling_module,
P_VERSION_LEVEL_FLAG => p_version_level_flag,
P_CALLED_MODE => p_called_mode );
SELECT DISTINCT
bl.resource_assignment_id,
bl.txn_currency_code
BULK COLLECT
INTO l_ra_id_tab,
l_txn_currency_code_tab
FROM pa_budget_lines bl,
pa_resource_assignments ra
WHERE ra.budget_version_id = p_fp_cols_rec.x_budget_version_id
AND bl.resource_assignment_id = ra.resource_assignment_id
AND NOT EXISTS (SELECT null
FROM pa_resource_asgn_curr rbc
WHERE rbc.resource_assignment_id = bl.resource_assignment_id
AND rbc.txn_currency_code = bl.txn_currency_code);
-- Insert records with values for the following columns:
-- (ra_txn_id, budget_version_id, resource_assignment_id, txn_currency_code).
-- All of the remaining columns, including the rate overrides,
-- but excluding the who columns, will be Null in this case.
FORALL i IN 1..l_ra_id_tab.count
INSERT INTO pa_resource_asgn_curr (
RA_TXN_ID,
BUDGET_VERSION_ID,
RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
RECORD_VERSION_NUMBER )
VALUES (
pa_resource_asgn_curr_s.nextval,
p_fp_cols_rec.x_budget_version_id,
l_ra_id_tab(i),
l_txn_currency_code_tab(i),
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_record_version_number );
|| 'DELETE_TABLE_RECORDS',
P_CALLED_MODE => p_called_mode,
P_MODULE_NAME => l_module_name,
P_LOG_LEVEL => l_log_level );
PA_RES_ASG_CURRENCY_PUB.DELETE_TABLE_RECORDS
( P_FP_COLS_REC => p_fp_cols_rec,
P_CALLING_MODULE => G_PVT_INSERT,
P_VERSION_LEVEL_FLAG => p_version_level_flag,
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_TABLE_RECORDS: ' || x_return_status,
P_CALLED_MODE => p_called_mode,
P_MODULE_NAME => l_module_name,
P_LOG_LEVEL => l_log_level );
INSERT INTO pa_resource_asgn_curr
( RA_TXN_ID,
BUDGET_VERSION_ID,
RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
TXN_RAW_COST_RATE_OVERRIDE,
TXN_BURDEN_COST_RATE_OVERRIDE,
TXN_BILL_RATE_OVERRIDE,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
RECORD_VERSION_NUMBER,
expenditure_type )--for EnC
SELECT pa_resource_asgn_curr_s.nextval,
p_fp_cols_rec.x_budget_version_id,
tmp.resource_assignment_id,
tmp.txn_currency_code,
tmp.txn_raw_cost_rate_override,
tmp.txn_burden_cost_rate_override,
tmp.txn_bill_rate_override,
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_record_version_number,
expenditure_type --for EnC
FROM pa_resource_asgn_curr_tmp tmp;
p_procedure_name => 'INSERT_TABLE_RECORDS',
p_error_text => substr(sqlerrm,1,240));
END INSERT_TABLE_RECORDS;
SELECT bl.resource_assignment_id, --RESOURCE_ASSIGNMENT_ID
bl.txn_currency_code, --TXN_CURRENCY_CODE
case when sum(nvl(bl.quantity,0)) = 0 and sum(nvl(bl.init_quantity,0)) = 0 then null else sum(nvl(bl.quantity,0)) end, --TOTAL_QUANTITY
decode(sum(nvl(bl.init_quantity,0)),
0,null,sum(nvl(bl.init_quantity,0))), --TOTAL_INIT_QUANTITY
rbc.txn_raw_cost_rate_override, --TXN_RAW_COST_RATE_OVERRIDE
rbc.txn_burden_cost_rate_override, --TXN_BURDEN_COST_RATE_OVERRIDE
rbc.txn_bill_rate_override, --TXN_BILL_RATE_OVERRIDE
/* bug fix 5523038 : modified Avg/ETC rate calculation logic to check
* for rejection codes instead of relying on override/standard rates */
( sum(decode(cost_rejection_code
,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
* nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)))
+ nvl(bl.txn_init_raw_cost,0)
,null))
/ DECODE(sum(decode(cost_rejection_code,null,nvl(bl.quantity,0),null))
,0,NULL
,sum(decode(cost_rejection_code,null,nvl(bl.quantity,0),null)))
) avg_cost_rate, --TXN_AVERAGE_RAW_COST_RATE
( sum(decode(burden_rejection_code
,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
* nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)))
+ nvl(bl.txn_init_burdened_cost,0)
,null))
/ DECODE(sum(decode(burden_rejection_code,null,nvl(bl.quantity,0),null))
,0,NULL
,sum(decode(burden_rejection_code,null,nvl(bl.quantity,0),null)))
) avg_burden_rate, --TXN_AVERAGE_BURDEN_COST_RATE
( sum(decode(revenue_rejection_code
,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
* nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)))
+ nvl(bl.txn_init_revenue,0)
,null))
/ DECODE(sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0),null))
,0,NULL
,sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0),null)))
) avg_bill_rate, --TXN_AVERAGE_BILL_RATE
( sum(decode(cost_rejection_code
,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
* nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)))
,null))
/ DECODE(sum(decode(cost_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null))
,0,NULL
,sum(decode(cost_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null)))
) etc_cost_rate, --TXN_ETC_RAW_COST_RATE
( sum(decode(burden_rejection_code
,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
* nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)))
,null))
/ DECODE(sum(decode(burden_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null))
,0,NULL
,sum(decode(burden_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null)))
) etc_burden_rate, --TXN_ETC_BURDEN_COST_RATE
( sum(decode(revenue_rejection_code
,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
* nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)))
,null))
/ DECODE(sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null))
,0,NULL
,sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null)))
) etc_bill_rate, --TXN_ETC_BILL_RATE
/* end bug fix 5523038 */
case when sum(nvl(bl.txn_raw_cost,0)) = 0 and sum(nvl(bl.txn_init_raw_cost,0)) = 0 then null else sum(nvl(bl.txn_raw_cost,0)) end, --TOTAL_TXN_RAW_COST
case when sum(nvl(bl.txn_burdened_cost,0)) = 0 and sum(nvl(bl.txn_init_burdened_cost,0)) = 0 then null else sum(nvl(bl.txn_burdened_cost,0)) end, --TOTAL_TXN_BURDENED_COST
/* decode(sum(nvl(bl.txn_revenue,0)),
0,null,sum(nvl(bl.txn_revenue,0))) */
case when sum(nvl(bl.txn_revenue,0)) = 0 and sum(nvl(bl.txn_init_revenue,0)) = 0 then null else sum(nvl(bl.txn_revenue,0)) end, --TOTAL_TXN_REVENUE
decode(sum(nvl(bl.txn_init_raw_cost,0)),
0,null,sum(nvl(bl.txn_init_raw_cost,0))), --TOTAL_TXN_INIT_RAW_COST
decode(sum(nvl(bl.txn_init_burdened_cost,0)),
0,null,sum(nvl(bl.txn_init_burdened_cost,0))), --TOTAL_TXN_INIT_BURDENED_COST
decode(sum(nvl(bl.txn_init_revenue,0)),
0,null,sum(nvl(bl.txn_init_revenue,0))), --TOTAL_TXN_INIT_REVENUE
decode(sum(nvl(bl.project_raw_cost,0)),
0,null,sum(nvl(bl.project_raw_cost,0))), --TOTAL_PROJECT_RAW_COST
decode(sum(nvl(bl.project_burdened_cost,0)),
0,null,sum(nvl(bl.project_burdened_cost,0))), --TOTAL_PROJECT_BURDENED_COST
decode(sum(nvl(bl.project_revenue,0)),
0,null,sum(nvl(bl.project_revenue,0))), --TOTAL_PROJECT_REVENUE
decode(sum(nvl(bl.project_init_raw_cost,0)),
0,null,sum(nvl(bl.project_init_raw_cost,0))), --TOTAL_PROJECT_INIT_RAW_COST
decode(sum(nvl(bl.project_init_burdened_cost,0)),
0,null,sum(nvl(bl.project_init_burdened_cost,0))), --TOTAL_PROJECT_INIT_BD_COST
decode(sum(nvl(bl.project_init_revenue,0)),
0,null,sum(nvl(bl.project_init_revenue,0))), --TOTAL_PROJECT_INIT_REVENUE
decode(sum(nvl(bl.raw_cost,0)),
0,null,sum(nvl(bl.raw_cost,0))), --TOTAL_PROJFUNC_RAW_COST
decode(sum(nvl(bl.burdened_cost,0)),
0,null,sum(nvl(bl.burdened_cost,0))), --TOTAL_PROJFUNC_BURDENED_COST
decode(sum(nvl(bl.revenue,0)),
0,null,sum(nvl(bl.revenue,0))), --TOTAL_PROJFUNC_REVENUE
decode(sum(nvl(bl.init_raw_cost,0)),
0,null,sum(nvl(bl.init_raw_cost,0))), --TOTAL_PROJFUNC_INIT_RAW_COST
decode(sum(nvl(bl.init_burdened_cost,0)),
0,null,sum(nvl(bl.init_burdened_cost,0))), --TOTAL_PROJFUNC_INIT_BD_COST
decode(sum(nvl(bl.init_revenue,0)),
0,null,sum(nvl(bl.init_revenue,0))), --TOTAL_PROJFUNC_INIT_REVENUE
decode(sum(nvl(bl.display_quantity,0)),
0,null,sum(nvl(bl.display_quantity,0))), --TOTAL_DISPLAY_QUANTITY
rbc.expenditure_type --Expenditure type for Enc
FROM pa_resource_assignments ra,
pa_budget_lines bl,
pa_resource_asgn_curr rbc
WHERE bl.resource_assignment_id = rbc.resource_assignment_id (+)
AND bl.txn_currency_code = rbc.txn_currency_code (+)
AND ra.budget_version_id = c_budget_version_id
AND ra.project_id = c_project_id
AND bl.resource_assignment_id = ra.resource_assignment_id
GROUP BY bl.resource_assignment_id,
bl.txn_currency_code,
rbc.txn_raw_cost_rate_override,
rbc.txn_burden_cost_rate_override,
rbc.txn_bill_rate_override,
rbc.expenditure_type --cklee 6/16/2009
UNION ALL
SELECT rbc.resource_assignment_id, --RESOURCE_ASSIGNMENT_ID
rbc.txn_currency_code, --TXN_CURRENCY_CODE
null, --TOTAL_QUANTITY
null, --TOTAL_INIT_QUANTITY
rbc.txn_raw_cost_rate_override, --TXN_RAW_COST_RATE_OVERRIDE
rbc.txn_burden_cost_rate_override, --TXN_BURDEN_COST_RATE_OVERRIDE
rbc.txn_bill_rate_override, --TXN_BILL_RATE_OVERRIDE
null, --TXN_AVERAGE_RAW_COST_RATE
null, --TXN_AVERAGE_BURDEN_COST_RATE
null, --TXN_AVERAGE_BILL_RATE
null, --TXN_ETC_RAW_COST_RATE
null, --TXN_ETC_BURDEN_COST_RATE
null, --TXN_ETC_BILL_RATE
null, --TOTAL_TXN_RAW_COST
null, --TOTAL_TXN_BURDENED_COST
null, --TOTAL_TXN_REVENUE
null, --TOTAL_TXN_INIT_RAW_COST
null, --TOTAL_TXN_INIT_BURDENED_COST
null, --TOTAL_TXN_INIT_REVENUE
null, --TOTAL_PROJECT_RAW_COST
null, --TOTAL_PROJECT_BURDENED_COST
null, --TOTAL_PROJECT_REVENUE
null, --TOTAL_PROJECT_INIT_RAW_COST
null, --TOTAL_PROJECT_INIT_BD_COST
null, --TOTAL_PROJECT_INIT_REVENUE
null, --TOTAL_PROJFUNC_RAW_COST
null, --TOTAL_PROJFUNC_BURDENED_COST
null, --TOTAL_PROJFUNC_REVENUE
null, --TOTAL_PROJFUNC_INIT_RAW_COST
null, --TOTAL_PROJFUNC_INIT_BD_COST
null, --TOTAL_PROJFUNC_INIT_REVENUE
null, --TOTAL_DISPLAY_QUANTITY
rbc.expenditure_type --Expenditure type for Enc
FROM pa_resource_asgn_curr rbc
WHERE rbc.budget_version_id = c_budget_version_id
AND NOT EXISTS (SELECT null
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = rbc.resource_assignment_id
AND bl.txn_currency_code = rbc.txn_currency_code );
SELECT /*+ leading(RBC) cardinality(RBC,1) */ -- added hint for bug 14040849
rbc.resource_assignment_id, --RESOURCE_ASSIGNMENT_ID
rbc.txn_currency_code, --TXN_CURRENCY_CODE
case when sum(nvl(bl.quantity,0)) = 0 and sum(nvl(bl.init_quantity,0)) = 0 then null else sum(nvl(bl.quantity,0)) end, --TOTAL_QUANTITY
decode(sum(nvl(bl.init_quantity,0)),
0,null,sum(nvl(bl.init_quantity,0))), --TOTAL_INIT_QUANTITY
rbc.txn_raw_cost_rate_override, --TXN_RAW_COST_RATE_OVERRIDE
rbc.txn_burden_cost_rate_override, --TXN_BURDEN_COST_RATE_OVERRIDE
rbc.txn_bill_rate_override, --TXN_BILL_RATE_OVERRIDE
/* bug fix 5523038 : modified Avg/ETC rate calculation logic to check
* for rejection codes instead of relying on override/standard rates */
( sum(decode(cost_rejection_code
,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
* nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)))
+ nvl(bl.txn_init_raw_cost,0)
,null))
/ DECODE(sum(decode(cost_rejection_code,null,nvl(bl.quantity,0),null))
,0,NULL
,sum(decode(cost_rejection_code,null,nvl(bl.quantity,0),null)))
) avg_cost_rate, --TXN_AVERAGE_RAW_COST_RATE
( sum(decode(burden_rejection_code
,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
* nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)))
+ nvl(bl.txn_init_burdened_cost,0)
,null))
/ DECODE(sum(decode(burden_rejection_code,null,nvl(bl.quantity,0),null))
,0,NULL
,sum(decode(burden_rejection_code,null,nvl(bl.quantity,0),null)))
) avg_burden_rate, --TXN_AVERAGE_BURDEN_COST_RATE
( sum(decode(revenue_rejection_code
,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
* nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)))
+ nvl(bl.txn_init_revenue,0)
,null))
/ DECODE(sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0),null))
,0,NULL
,sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0),null)))
) avg_bill_rate, --TXN_AVERAGE_BILL_RATE
( sum(decode(cost_rejection_code
,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
* nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)))
,null))
/ DECODE(sum(decode(cost_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null))
,0,NULL
,sum(decode(cost_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null)))
) etc_cost_rate, --TXN_ETC_RAW_COST_RATE
( sum(decode(burden_rejection_code
,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
* nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)))
,null))
/ DECODE(sum(decode(burden_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null))
,0,NULL
,sum(decode(burden_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null)))
) etc_burden_rate, --TXN_ETC_BURDEN_COST_RATE
( sum(decode(revenue_rejection_code
,null,((nvl(bl.quantity,0)-nvl(bl.init_quantity,0))
* nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)))
,null))
/ DECODE(sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null))
,0,NULL
,sum(decode(revenue_rejection_code,null,nvl(bl.quantity,0)-nvl(bl.init_quantity,0),null)))
) etc_bill_rate, --TXN_ETC_BILL_RATE
/* end bug fix 5523038 */
case when sum(nvl(bl.txn_raw_cost,0)) = 0 and sum(nvl(bl.txn_init_raw_cost,0)) = 0 then null else sum(nvl(bl.txn_raw_cost,0)) end, --TOTAL_TXN_RAW_COST
case when sum(nvl(bl.txn_burdened_cost,0)) = 0 and sum(nvl(bl.txn_init_burdened_cost,0)) = 0 then null else sum(nvl(bl.txn_burdened_cost,0)) end, --TOTAL_TXN_BURDENED_COST
case when sum(nvl(bl.txn_revenue,0)) = 0 and sum(nvl(bl.txn_init_revenue,0)) = 0 then null else sum(nvl(bl.txn_revenue,0)) end, --TOTAL_TXN_REVENUE
decode(sum(nvl(bl.txn_init_raw_cost,0)),
0,null,sum(nvl(bl.txn_init_raw_cost,0))), --TOTAL_TXN_INIT_RAW_COST
decode(sum(nvl(bl.txn_init_burdened_cost,0)),
0,null,sum(nvl(bl.txn_init_burdened_cost,0))), --TOTAL_TXN_INIT_BURDENED_COST
decode(sum(nvl(bl.txn_init_revenue,0)),
0,null,sum(nvl(bl.txn_init_revenue,0))), --TOTAL_TXN_INIT_REVENUE
decode(sum(nvl(bl.project_raw_cost,0)),
0,null,sum(nvl(bl.project_raw_cost,0))), --TOTAL_PROJECT_RAW_COST
decode(sum(nvl(bl.project_burdened_cost,0)),
0,null,sum(nvl(bl.project_burdened_cost,0))), --TOTAL_PROJECT_BURDENED_COST
decode(sum(nvl(bl.project_revenue,0)),
0,null,sum(nvl(bl.project_revenue,0))), --TOTAL_PROJECT_REVENUE
decode(sum(nvl(bl.project_init_raw_cost,0)),
0,null,sum(nvl(bl.project_init_raw_cost,0))), --TOTAL_PROJECT_INIT_RAW_COST
decode(sum(nvl(bl.project_init_burdened_cost,0)),
0,null,sum(nvl(bl.project_init_burdened_cost,0))), --TOTAL_PROJECT_INIT_BD_COST
decode(sum(nvl(bl.project_init_revenue,0)),
0,null,sum(nvl(bl.project_init_revenue,0))), --TOTAL_PROJECT_INIT_REVENUE
decode(sum(nvl(bl.raw_cost,0)),
0,null,sum(nvl(bl.raw_cost,0))), --TOTAL_PROJFUNC_RAW_COST
decode(sum(nvl(bl.burdened_cost,0)),
0,null,sum(nvl(bl.burdened_cost,0))), --TOTAL_PROJFUNC_BURDENED_COST
decode(sum(nvl(bl.revenue,0)),
0,null,sum(nvl(bl.revenue,0))), --TOTAL_PROJFUNC_REVENUE
decode(sum(nvl(bl.init_raw_cost,0)),
0,null,sum(nvl(bl.init_raw_cost,0))), --TOTAL_PROJFUNC_INIT_RAW_COST
decode(sum(nvl(bl.init_burdened_cost,0)),
0,null,sum(nvl(bl.init_burdened_cost,0))), --TOTAL_PROJFUNC_INIT_BD_COST
decode(sum(nvl(bl.init_revenue,0)),
0,null,sum(nvl(bl.init_revenue,0))), --TOTAL_PROJFUNC_INIT_REVENUE
decode(sum(nvl(bl.display_quantity,0)),
0,null,sum(nvl(bl.display_quantity,0))), --TOTAL_DISPLAY_QUANTITY
rbc.expenditure_type --Direct cost expenditure type for EnC
FROM pa_budget_lines bl,
pa_resource_asgn_curr_TMP rbc
WHERE bl.resource_assignment_id (+) = rbc.resource_assignment_id
AND bl.txn_currency_code (+) = rbc.txn_currency_code
--Bug 6160759
AND bl.budget_version_id(+)= p_fp_cols_rec.x_budget_version_id
AND bl.budget_version_id(+)= p_fp_cols_rec.x_budget_version_id
GROUP BY rbc.resource_assignment_id,
rbc.txn_currency_code,
rbc.txn_raw_cost_rate_override,
rbc.txn_burden_cost_rate_override,
rbc.txn_bill_rate_override,
rbc.expenditure_type;
l_last_updated_by NUMBER := FND_GLOBAL.user_id;
l_last_update_login NUMBER := FND_GLOBAL.login_id;
SELECT nvl(wp_version_flag,'N')
INTO l_wp_version_flag
FROM pa_budget_versions
WHERE budget_version_id = p_fp_cols_rec.x_budget_version_id;
l_null_NumTabTyp.delete;
|| 'DELETE_TABLE_RECORDS',
P_CALLED_MODE => p_called_mode,
P_MODULE_NAME => l_module_name,
P_LOG_LEVEL => l_log_level );
PA_RES_ASG_CURRENCY_PUB.DELETE_TABLE_RECORDS
( P_FP_COLS_REC => p_fp_cols_rec,
P_CALLING_MODULE => G_PVT_ROLLUP,
P_VERSION_LEVEL_FLAG => p_version_level_flag,
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_TABLE_RECORDS: ' || x_return_status,
P_CALLED_MODE => p_called_mode,
P_MODULE_NAME => l_module_name,
P_LOG_LEVEL => l_log_level );
INSERT INTO PA_RESOURCE_ASGN_CURR (
RA_TXN_ID,
BUDGET_VERSION_ID,
RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
TOTAL_QUANTITY,
TOTAL_INIT_QUANTITY,
TXN_RAW_COST_RATE_OVERRIDE,
TXN_BURDEN_COST_RATE_OVERRIDE,
TXN_BILL_RATE_OVERRIDE,
TXN_AVERAGE_RAW_COST_RATE,
TXN_AVERAGE_BURDEN_COST_RATE,
TXN_AVERAGE_BILL_RATE,
TXN_ETC_RAW_COST_RATE,
TXN_ETC_BURDEN_COST_RATE,
TXN_ETC_BILL_RATE,
TOTAL_TXN_RAW_COST,
TOTAL_TXN_BURDENED_COST,
TOTAL_TXN_REVENUE,
TOTAL_TXN_INIT_RAW_COST,
TOTAL_TXN_INIT_BURDENED_COST,
TOTAL_TXN_INIT_REVENUE,
TOTAL_PROJECT_RAW_COST,
TOTAL_PROJECT_BURDENED_COST,
TOTAL_PROJECT_REVENUE,
TOTAL_PROJECT_INIT_RAW_COST,
TOTAL_PROJECT_INIT_BD_COST,
TOTAL_PROJECT_INIT_REVENUE,
TOTAL_PROJFUNC_RAW_COST,
TOTAL_PROJFUNC_BURDENED_COST,
TOTAL_PROJFUNC_REVENUE,
TOTAL_PROJFUNC_INIT_RAW_COST,
TOTAL_PROJFUNC_INIT_BD_COST,
TOTAL_PROJFUNC_INIT_REVENUE,
TOTAL_DISPLAY_QUANTITY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
RECORD_VERSION_NUMBER,
expenditure_type )
VALUES (
pa_resource_asgn_curr_s.nextval,
p_fp_cols_rec.x_budget_version_id,
l_ra_id_tab(i),
l_txn_currency_code_tab(i),
l_total_quantity_tab(i),
l_total_init_quantity_tab(i),
l_raw_cost_rate_override_tab(i),
l_brdn_cost_rate_override_tab(i),
l_bill_rate_override_tab(i),
l_avg_raw_cost_rate_tab(i),
l_avg_burden_cost_rate_tab(i),
l_avg_bill_rate_tab(i),
l_etc_raw_cost_rate_tab(i),
l_etc_burden_cost_rate_tab(i),
l_etc_bill_rate_tab(i),
l_txn_raw_cost_tab(i),
l_txn_burdened_cost_tab(i),
l_txn_revenue_tab(i),
l_txn_init_raw_cost_tab(i),
l_txn_init_burdened_cost_tab(i),
l_txn_init_revenue_tab(i),
l_pc_raw_cost_tab(i),
l_pc_burdened_cost_tab(i),
l_pc_revenue_tab(i),
l_pc_init_raw_cost_tab(i),
l_pc_init_burdened_cost_tab(i),
l_pc_init_revenue_tab(i),
l_pfc_raw_cost_tab(i),
l_pfc_burdened_cost_tab(i),
l_pfc_revenue_tab(i),
l_pfc_init_raw_cost_tab(i),
l_pfc_init_burdened_cost_tab(i),
l_pfc_init_revenue_tab(i),
l_display_quantity_tab(i),
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_record_version_number,
l_direct_expenditure_type_tab(i) --for EnC
);
DELETE FROM pa_resource_asgn_curr prac
WHERE EXISTS
(SELECT 1
FROM pa_resource_asgn_curr a
where a.budget_version_id = p_fp_cols_rec.x_budget_version_id
AND a.resource_assignment_id = l_ra_id_tab(j)
AND prac.resource_assignment_id = a.resource_assignment_id
AND ( a.total_quantity IS NOT NULL
OR a.total_init_quantity IS NOT NULL
OR a.txn_raw_cost_rate_override IS NOT NULL
OR a.txn_burden_cost_rate_override IS NOT NULL
OR a.txn_bill_rate_override IS NOT NULL
OR a.txn_average_raw_cost_rate IS NOT NULL
OR a.txn_average_burden_cost_rate IS NOT NULL
OR a.txn_average_bill_rate IS NOT NULL
OR a.txn_etc_raw_cost_rate IS NOT NULL
OR a.txn_etc_burden_cost_rate IS NOT NULL
OR a.txn_etc_bill_rate IS NOT NULL
OR a.total_txn_raw_cost IS NOT NULL
OR a.total_txn_burdened_cost IS NOT NULL
OR a.total_txn_revenue IS NOT NULL
OR a.total_txn_init_raw_cost IS NOT NULL
OR a.total_txn_init_burdened_cost IS NOT NULL
OR a.total_txn_init_revenue IS NOT NULL
OR a.total_project_raw_cost IS NOT NULL
OR a.total_project_burdened_cost IS NOT NULL
OR a.total_project_revenue IS NOT NULL
OR a.total_project_init_raw_cost IS NOT NULL
OR a.total_project_init_bd_cost IS NOT NULL
OR a.total_project_init_revenue IS NOT NULL
OR a.total_projfunc_raw_cost IS NOT NULL
OR a.total_projfunc_burdened_cost IS NOT NULL
OR a.total_projfunc_revenue IS NOT NULL
OR a.total_projfunc_init_raw_cost IS NOT NULL
OR a.total_projfunc_init_bd_cost IS NOT NULL
OR a.total_projfunc_init_revenue IS NOT NULL
OR a.total_display_quantity IS NOT NULL ))
AND prac.budget_version_id = p_fp_cols_rec.x_budget_version_id
AND prac.resource_assignment_id = l_ra_id_tab(j)
AND prac.total_quantity IS NULL
AND prac.total_init_quantity IS NULL
AND prac.txn_raw_cost_rate_override IS NULL
AND prac.txn_burden_cost_rate_override IS NULL
AND prac.txn_bill_rate_override IS NULL
AND prac.txn_average_raw_cost_rate IS NULL
AND prac.txn_average_burden_cost_rate IS NULL
AND prac.txn_average_bill_rate IS NULL
AND prac.txn_etc_raw_cost_rate IS NULL
AND prac.txn_etc_burden_cost_rate IS NULL
AND prac.txn_etc_bill_rate IS NULL
AND prac.total_txn_raw_cost IS NULL
AND prac.total_txn_burdened_cost IS NULL
AND prac.total_txn_revenue IS NULL
AND prac.total_txn_init_raw_cost IS NULL
AND prac.total_txn_init_burdened_cost IS NULL
AND prac.total_txn_init_revenue IS NULL
AND prac.total_project_raw_cost IS NULL
AND prac.total_project_burdened_cost IS NULL
AND prac.total_project_revenue IS NULL
AND prac.total_project_init_raw_cost IS NULL
AND prac.total_project_init_bd_cost IS NULL
AND prac.total_project_init_revenue IS NULL
AND prac.total_projfunc_raw_cost IS NULL
AND prac.total_projfunc_burdened_cost IS NULL
AND prac.total_projfunc_revenue IS NULL
AND prac.total_projfunc_init_raw_cost IS NULL
AND prac.total_projfunc_init_bd_cost IS NULL
AND prac.total_projfunc_init_revenue IS NULL
AND prac.total_display_quantity IS NULL
;