[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PX_DELETED_RES_ASG_ID_TAB IN OUT NOCOPY PA_PLSQL_DATATYPES.IdTabTyp,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2) IS
l_module_name VARCHAR2(200) := 'pa.plsql.PA_FP_GEN_COMMITMENT_AMOUNTS.GEN_COMMITMENT_AMOUNTS';
SELECT /*+ INDEX(TMP,PA_RES_LIST_MAP_TMP4_N2)*/
P.RESOURCE_ASSIGNMENT_ID
,DECODE(c_multi_curr_flag, 'Y', CT.DENOM_CURRENCY_CODE,CT.PROJECT_CURRENCY_CODE) currency_code
,NVL(CT.CMT_NEED_BY_DATE,CT.EXPENDITURE_ITEM_DATE)
,NVL(CT.CMT_NEED_BY_DATE,CT.EXPENDITURE_ITEM_DATE)
,DECODE(c_multi_curr_flag, 'Y', NVL(CT.DENOM_RAW_COST,0), NVL(CT.PROJ_RAW_COST,0)) tot_raw_cost
,DECODE(c_multi_curr_flag, 'Y', NVL(CT.DENOM_BURDENED_COST,0), NVL(CT.PROJ_BURDENED_COST,0)) tot_burdened_cost
,NVL(CT.PROJ_RAW_COST,0) tot_proj_raw_cost
,NVL(CT.PROJ_BURDENED_COST,0) tot_proj_burdened_cost
,NVL(CT.ACCT_RAW_COST,0) tot_projfunc_raw_cost
,NVL(CT.ACCT_BURDENED_COST,0) tot_projfunc_burdened_cost
,NVL(CT.TOT_CMT_QUANTITY,0) tot_quantity
FROM PA_COMMITMENT_TXNS CT,
PA_RES_LIST_MAP_TMP4 TMP,
PA_RESOURCE_ASSIGNMENTS P
WHERE TMP.TXN_SOURCE_ID = CT.CMT_LINE_ID
AND CT.PROJECT_ID = P_PROJECT_ID
AND NVL(CT.generation_error_flag,'N') = 'N'
AND P.RESOURCE_ASSIGNMENT_ID = TMP.TXN_RESOURCE_ASSIGNMENT_ID
AND P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID;
l_DELETED_RES_ASG_ID_TAB PA_PLSQL_DATATYPES.IdTabTyp;
l_last_updated_by NUMBER := FND_GLOBAL.user_id;
l_last_update_login NUMBER := FND_GLOBAL.login_id;
SELECT MAX(budget_line_id) INTO l_bl_id_counter
FROM pa_fp_rollup_tmp;
DELETE FROM PA_RES_LIST_MAP_TMP1;
DELETE FROM PA_RES_LIST_MAP_TMP2;
DELETE FROM PA_RES_LIST_MAP_TMP3;
DELETE FROM PA_RES_LIST_MAP_TMP4;
SELECT RESOURCE_CLASS_ID
INTO l_resource_class_id
FROM PA_RESOURCE_CLASSES_B
WHERE RESOURCE_CLASS_CODE = 'FINANCIAL_ELEMENTS';
SELECT ct.CMT_LINE_ID,
'OPEN_COMMITMENTS',
ct.ORGANIZATION_ID,
ct.VENDOR_ID,
ct.EXPENDITURE_TYPE,
ct.REVENUE_CATEGORY,
ct.TASK_ID
,NVL(ct.CMT_NEED_BY_DATE, ct.EXPENDITURE_ITEM_DATE)
,NVL(ct.CMT_NEED_BY_DATE, ct.EXPENDITURE_ITEM_DATE)
,SYSTEM_LINKAGE_FUNCTION
,INVENTORY_ITEM_ID
,DECODE(EXPENDITURE_TYPE,null,
DECODE(EXPENDITURE_CATEGORY,null,NULL,
'EXPENDITURE_CATEGORY'),'EXPENDITURE_TYPE'),
NVL(ct.RESOURCE_CLASS,'FINANCIAL_ELEMENTS')
,ct.cbs_element_id --bug#16827157
BULK COLLECT
INTO l_TXN_SOURCE_ID_tab,
l_TXN_SOURCE_TYPE_CODE_tab,
l_ORGANIZATION_ID_tab,
l_VENDOR_ID_tab,
l_EXPENDITURE_TYPE_tab,
l_REVENUE_CATEGORY_CODE_tab,
l_TXN_TASK_ID_tab,
l_TXN_PLAN_START_DATE_tab,
l_TXN_PLAN_END_DATE_tab,
l_SYS_LINK_FUNCTION_tab,
l_INVENTORY_ITEM_ID_tab,
l_FC_RES_TYPE_CODE_tab,
l_RESOURCE_CLASS_CODE_tab,
l_cbs_element_id_tab --bug#16827157
FROM PA_COMMITMENT_TXNS ct, PA_RESOURCE_CLASSES_B rc
WHERE ct.PROJECT_ID = P_PROJECT_ID
AND NVL(CT.generation_error_flag,'N') = 'N'
AND ct.RESOURCE_CLASS = rc.RESOURCE_CLASS_CODE(+);
SELECT /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
count(*) INTO l_count1
FROM PA_RES_LIST_MAP_TMP4
WHERE RESOURCE_LIST_MEMBER_ID IS NULL and rownum=1;
(p_msg => 'Before calling pa_fp_gen_budget_amt_pub.update_res_asg',
p_module_name => l_module_name,
p_log_level => 5);
PA_FP_GEN_BUDGET_AMT_PUB.UPDATE_RES_ASG
(P_PROJECT_ID => P_PROJECT_ID,
P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
P_STRU_SHARING_CODE => l_stru_sharing_code,
P_GEN_SRC_CODE => 'OPEN_COMMITMENTS',
P_FP_COLS_REC => P_FP_COLS_REC,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
(p_msg => 'Status after calling pa_fp_gen_budget_amt_pub.update_res_asg'
||x_return_status,
p_module_name => l_module_name,
p_log_level => 5);
SELECT NVL(rate_based_flag,'N') INTO l_rate_based_flag
FROM pa_resource_assignments
WHERE
resource_assignment_id = l_res_asg_id(i);
SELECT pap.start_date
,pap.end_date
,pap.period_name
INTO l_bl_start_date
,l_bl_end_date
,l_bl_period_name
FROM pa_periods_all pap
--,pa_implementations imp /* Bug 4884718; SQL ID 14901776 */
SELECT PERIOD.start_date,
PERIOD.end_date,
PERIOD.period_name
INTO l_bl_start_date
,l_bl_end_date
,l_bl_period_name
FROM GL_PERIOD_STATUSES PERIOD
WHERE PERIOD.application_id = pa_period_process_pkg.application_id
AND PERIOD.set_of_books_id = p_fp_cols_rec.x_set_of_books_id
AND PERIOD.adjustment_period_flag = 'N'
AND l_reference_start_date BETWEEN
PERIOD.start_date AND PERIOD.end_date;
SELECT START_DATE,
COST_REJECTION_CODE,
BURDEN_REJECTION_CODE,
PC_CUR_CONV_REJECTION_CODE,
PFC_CUR_CONV_REJECTION_CODE
BULK COLLECT
INTO l_rej_start_date_tab,
l_cost_rej_code_tab,
l_burden_rej_code_tab,
l_pc_cur_conv_rej_code_tab,
l_pfc_cur_conv_rej_code_tab
FROM pa_budget_lines
WHERE resource_assignment_id = l_res_asg_id(i)
AND txn_currency_code = l_currency_code(i)
AND start_date = l_bl_start_date
AND ( cost_rejection_code is not null OR
burden_rejection_code is not null OR
pc_cur_conv_rejection_code is not null OR
pfc_cur_conv_rejection_code is not null );
SELECT START_DATE,
COST_REJECTION_CODE,
BURDEN_REJECTION_CODE,
PC_CUR_CONV_REJECTION_CODE,
PFC_CUR_CONV_REJECTION_CODE
BULK COLLECT
INTO l_rej_start_date_tab,
l_cost_rej_code_tab,
l_burden_rej_code_tab,
l_pc_cur_conv_rej_code_tab,
l_pfc_cur_conv_rej_code_tab
FROM pa_budget_lines
WHERE resource_assignment_id = l_res_asg_id(i)
AND txn_currency_code = l_currency_code(i)
AND ( cost_rejection_code is not null OR
burden_rejection_code is not null OR
pc_cur_conv_rejection_code is not null OR
pfc_cur_conv_rejection_code is not null );
SELECT DISTINCT
start_date,
cost_rejection_code,
burden_rejection_code,
pc_cur_conv_rejection_code,
pfc_cur_conv_rejection_code
BULK COLLECT
INTO l_rej_start_date_tab,
l_cost_rej_code_tab,
l_burden_rej_code_tab,
l_pc_cur_conv_rej_code_tab,
l_pfc_cur_conv_rej_code_tab
FROM pa_fp_rollup_tmp
WHERE resource_assignment_id = l_res_asg_id(i)
AND txn_currency_code = l_currency_code(i)
AND start_date = l_bl_start_date
AND ( cost_rejection_code is not null OR
burden_rejection_code is not null OR
pc_cur_conv_rejection_code is not null OR
pfc_cur_conv_rejection_code is not null );
SELECT MIN(start_date),
cost_rejection_code,
burden_rejection_code,
pc_cur_conv_rejection_code,
pfc_cur_conv_rejection_code
BULK COLLECT
INTO l_rej_start_date_tab,
l_cost_rej_code_tab,
l_burden_rej_code_tab,
l_pc_cur_conv_rej_code_tab,
l_pfc_cur_conv_rej_code_tab
FROM pa_fp_rollup_tmp
WHERE resource_assignment_id = l_res_asg_id(i)
AND txn_currency_code = l_currency_code(i)
AND ( cost_rejection_code is not null OR
burden_rejection_code is not null OR
pc_cur_conv_rejection_code is not null OR
pfc_cur_conv_rejection_code is not null )
GROUP BY cost_rejection_code,
burden_rejection_code,
pc_cur_conv_rejection_code,
pfc_cur_conv_rejection_code;
SELECT p.name, ta.task_number, rlm.alias
INTO l_project_name, l_task_number, l_resource_name
FROM pa_resource_assignments ra,
pa_projects_all p,
pa_tasks ta,
pa_resource_list_members rlm
WHERE ra.resource_assignment_id = l_res_asg_id(i)
AND p.project_id = ra.project_id
AND ta.task_id (+) = ra.task_id
AND rlm.resource_list_member_id = ra.resource_list_member_id;
SELECT BUDGET_LINE_ID,
QUANTITY,
TXN_RAW_COST,
TXN_BURDENED_COST,
PROJECT_RAW_COST,
PROJECT_BURDENED_COST,
RAW_COST,
BURDENED_COST
INTO l_budget_line_id,
l_bl_quantity,
l_bl_txn_raw_cost,
l_bl_txn_burdened_cost,
l_bl_project_raw_cost,
l_bl_project_burdened_cost,
l_bl_pfc_raw_cost,
l_bl_pfc_burdened_cost
FROM PA_BUDGET_LINES BL
WHERE BL.RESOURCE_ASSIGNMENT_ID = l_res_asg_id(i)
AND BL.TXN_CURRENCY_CODE = l_currency_code(i)
AND BL.START_DATE = l_bl_start_date;
SELECT BUDGET_LINE_ID,
START_DATE,
END_DATE,
QUANTITY,
TXN_RAW_COST,
TXN_BURDENED_COST,
PROJECT_RAW_COST,
PROJECT_BURDENED_COST,
RAW_COST,
BURDENED_COST,
NVL(INIT_QUANTITY,0),
NVL(TXN_INIT_RAW_COST,0),
NVL(TXN_INIT_BURDENED_COST,0),
NVL(PROJECT_INIT_RAW_COST,0),
NVL(INIT_RAW_COST,0),
NVL(INIT_BURDENED_COST,0)
INTO l_budget_line_id,
l_bl_start_date,
l_bl_end_date,
l_bl_quantity,
l_bl_txn_raw_cost,
l_bl_txn_burdened_cost,
l_bl_project_raw_cost,
l_bl_project_burdened_cost,
l_bl_pfc_raw_cost,
l_bl_pfc_burdened_cost,
l_bl_init_quantity,
l_bl_txn_init_raw_cost,
l_bl_txn_init_burdened_cost,
l_bl_project_init_raw_cost,
l_bl_pfc_init_raw_cost,
l_bl_pfc_init_burdened_cost
FROM PA_BUDGET_LINES BL
WHERE BL.RESOURCE_ASSIGNMENT_ID = l_res_asg_id(i)
AND BL.TXN_CURRENCY_CODE = l_currency_code(i);
SELECT BUDGET_LINE_ID,
START_DATE,
END_DATE,
QUANTITY,
TXN_RAW_COST,
TXN_BURDENED_COST,
PROJECT_RAW_COST,
PROJECT_BURDENED_COST,
RAW_COST,
BURDENED_COST
INTO l_budget_line_id,
l_bl_start_date,
l_bl_end_date,
l_bl_quantity,
l_bl_txn_raw_cost,
l_bl_txn_burdened_cost,
l_bl_project_raw_cost,
l_bl_project_burdened_cost,
l_bl_pfc_raw_cost,
l_bl_pfc_burdened_cost
FROM PA_BUDGET_LINES BL
WHERE BL.RESOURCE_ASSIGNMENT_ID = l_res_asg_id(i)
AND BL.TXN_CURRENCY_CODE = l_currency_code(i);
SELECT NVL(billable_flag,'Y') INTO l_billable_flag
FROM pa_tasks ta,
pa_resource_assignments ra
WHERE ra.resource_assignment_id = l_res_asg_id(i)
AND ra.task_id = ta.task_id (+);
INSERT INTO pa_fp_rollup_tmp(
RESOURCE_ASSIGNMENT_ID,
START_DATE,
END_DATE,
PERIOD_NAME,
QUANTITY,
TXN_CURRENCY_CODE,
TXN_RAW_COST,
TXN_BURDENED_COST,
PROJECT_RAW_COST,
PROJECT_BURDENED_COST,
PROJFUNC_RAW_COST,
PROJFUNC_BURDENED_COST,
BUDGET_LINE_ID,
BILLABLE_FLAG )
VALUES(
l_res_asg_id(i),
l_bl_start_date,
l_bl_end_date,
l_bl_period_name,
l_quantity_sum_tab(i),
l_currency_code(i),
l_raw_cost_sum(i),
l_burdened_cost_sum(i),
l_proj_raw_cost_sum(i),
l_proj_burdened_cost_sum(i),
l_projfunc_raw_cost_sum(i),
l_projfunc_burdened_cost_sum(i),
l_bl_id_counter,
l_billable_flag );
| Insert fresh Budget Lines. |
| |
| Bug 4251148: Modified this logic. If the Target is GL/PA timephased, we now also |
| check budget line start date when checking for budget line existence. |
| If Calculate API call is required, then check the Target time phase: |
| PA/GL: Populate the l_cal_ tables for further processing. In this case, we |
| will call Calculate at the Budget Line level, which requires that |
| we do not have budget lines. Therefore, bypass budget line Insert. |
| None: Populate the l_cal_ tables with just Resource Assignment Id and the |
| Currency Code (amounts unneccessary). In this case, we will call the |
| Calculate API at the Resource Assignment level with Partial Refresh |
| of Revenue amounts. This requires that we have budget lines populated.|
| Therefore, still do the Insert. |
+====================================================================================*/
-- Initialize rate overrides and exchange rates
l_txn_cost_rate_override := NULL;
INSERT INTO PA_BUDGET_LINES(RESOURCE_ASSIGNMENT_ID,
START_DATE,
END_DATE,
PERIOD_NAME,
TXN_CURRENCY_CODE,
TXN_RAW_COST,
TXN_BURDENED_COST,
PROJECT_RAW_COST,
PROJECT_BURDENED_COST,
RAW_COST,
BURDENED_COST,
QUANTITY,
BUDGET_LINE_ID,
BUDGET_VERSION_ID,
PROJECT_CURRENCY_CODE,
PROJFUNC_CURRENCY_CODE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
TXN_COST_RATE_OVERRIDE,
BURDEN_COST_RATE_OVERRIDE,
PROJECT_COST_EXCHANGE_RATE,
PROJFUNC_COST_EXCHANGE_RATE,
PROJECT_COST_RATE_TYPE,
PROJFUNC_COST_RATE_TYPE
)
VALUES(l_res_asg_id(i),
l_bl_start_date,
l_bl_end_date,
l_bl_period_name,
l_currency_code(i),
l_raw_cost_sum(i),
l_burdened_cost_sum(i),
l_proj_raw_cost_sum(i),
l_proj_burdened_cost_sum(i),
l_projfunc_raw_cost_sum(i),
l_projfunc_burdened_cost_sum(i),
l_quantity_sum_tab(i),
pa_budget_lines_s.nextval,
p_budget_version_id,
p_FP_COLS_REC.X_PROJECT_CURRENCY_CODE,
p_FP_COLS_REC.X_PROJFUNC_CURRENCY_CODE,
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_txn_cost_rate_override,
l_burden_cost_rate_override,
l_proj_cost_exchange_rate,
l_projfunc_cost_exchange_rate,
'User',
'User'
);
| If budget lines exist for the Resource Assignment Id and Currency Code, do Update |
| |
| Bug 4251148: Modified this logic. If the Target is GL/PA timephased, we now also |
| check budget line start date when checking for budget line existence. |
| If Calculate API call is required, then check the Target time phase: |
| PA/GL: Populate the l_cal_ tables for further processing. In this case, we |
| will call Calculate at the Budget Line level, which requires that |
| we do not have budget lines. Therefore, Updating the budget line is |
| not needed. In fact, we track all existing budget lines and DELETE |
| them later (before calling Calculate). |
| None: Populate the l_cal_ tables with just Resource Assignment Id and the |
| Currency Code (amounts unneccessary). In this case, we will call the |
| Calculate API at the Resource Assignment level with Partial Refresh |
| of Revenue amounts. This requires that we have budget lines populated.|
| Therefore, still do the Update. |
+====================================================================================*/
---if the record does exist then update the record in the pa_budget_lines table
IF l_calc_api_required_flag = 'Y' AND
p_fp_cols_rec.x_time_phased_code IN ('P','G') THEN
-- These budget lines will be deleted before calling Calculate
bl_index := bl_index + 1;
UPDATE PA_BUDGET_LINES
SET LAST_UPDATE_DATE = l_sysdate
, LAST_UPDATED_BY = l_last_updated_by
, LAST_UPDATE_LOGIN = l_last_update_login
-- , START_DATE = l_bl_start_date
-- , END_DATE = l_bl_end_date
, QUANTITY = l_upd_quantity
, TXN_RAW_COST = l_upd_txn_raw_cost
, TXN_BURDENED_COST = l_upd_txn_burdened_cost
, PROJECT_RAW_COST = l_upd_project_raw_cost
, PROJECT_BURDENED_COST = l_upd_project_burdened_cost
, RAW_COST = l_upd_pfc_raw_cost
, BURDENED_COST = l_upd_pfc_burdened_cost
, PROJECT_COST_RATE_TYPE = 'User'
, PROJFUNC_COST_RATE_TYPE = 'User'
, txn_cost_rate_override = l_txn_cost_rate_override
, burden_cost_rate_override = l_burden_cost_rate_override
, project_cost_exchange_rate = l_proj_cost_exchange_rate
, projfunc_cost_exchange_rate = l_projfunc_cost_exchange_rate
WHERE BUDGET_LINE_ID = l_budget_line_id;
UPDATE PA_BUDGET_LINES
SET LAST_UPDATE_DATE = l_sysdate
, LAST_UPDATED_BY = l_last_updated_by
, LAST_UPDATE_LOGIN = l_last_update_login
, START_DATE = l_bl_start_date
, END_DATE = l_bl_end_date
, QUANTITY = l_upd_quantity
, TXN_RAW_COST = l_upd_txn_raw_cost
, TXN_BURDENED_COST = l_upd_txn_burdened_cost
, PROJECT_RAW_COST = l_upd_project_raw_cost
, PROJECT_BURDENED_COST = l_upd_project_burdened_cost
, RAW_COST = l_upd_pfc_raw_cost
, BURDENED_COST = l_upd_pfc_burdened_cost
, PROJECT_COST_RATE_TYPE = 'User'
, PROJFUNC_COST_RATE_TYPE = 'User'
, txn_cost_rate_override = l_txn_cost_rate_override
, burden_cost_rate_override = l_burden_cost_rate_override
, project_cost_exchange_rate = l_proj_cost_exchange_rate
, projfunc_cost_exchange_rate = l_projfunc_cost_exchange_rate
WHERE BUDGET_LINE_ID = l_budget_line_id;
END IF; -- update
DELETE PA_FP_ROLLUP_TMP;
INSERT INTO PA_FP_ROLLUP_TMP (
ROLLUP_ID, -- l_cal_ table index value
RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
START_DATE,
END_DATE,
QUANTITY,
TXN_RAW_COST,
TXN_BURDENED_COST )
VALUES (
l_index_tab(i),
l_cal_tgt_res_asg_id_tab(i),
l_cal_txn_currency_code_tab(i),
l_cal_line_start_date_tab(i),
l_cal_line_end_date_tab(i),
l_cal_cmt_quantity_tab(i),
l_cal_cmt_raw_cost_tab(i),
l_cal_cmt_brdn_cost_tab(i) );
l_index_tab.delete;
SELECT RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
START_DATE,
max(ROLLUP_ID), -- l_cal_ table index value
max(END_DATE),
sum(nvl(QUANTITY,0)),
sum(nvl(TXN_RAW_COST,0)),
sum(nvl(TXN_BURDENED_COST,0)),
NULL, -- revenue
NULL, -- cost rate override
NULL, -- burden cost rate override
NULL -- bill rate override
BULK COLLECT
INTO l_tgt_res_asg_id_tab,
l_txn_currency_code_tab,
l_line_start_date_tab,
l_index_tab, -- l_cal_ table index value
l_line_end_date_tab,
l_src_quantity_tab,
l_src_raw_cost_tab,
l_src_brdn_cost_tab,
l_src_revenue_tab,
l_cost_rate_override_tab,
l_b_cost_rate_override_tab,
l_bill_rate_override_tab
FROM PA_FP_ROLLUP_TMP
GROUP BY RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
START_DATE;
DELETE PA_FP_ROLLUP_TMP;
INSERT INTO PA_FP_ROLLUP_TMP (
RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE ) -- txn_currency_code
VALUES (
l_cal_tgt_res_asg_id_tab(i),
l_cal_txn_currency_code_tab(i) );
SELECT DISTINCT
RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE
BULK COLLECT
INTO l_tgt_res_asg_id_tab,
l_txn_currency_code_tab
FROM PA_FP_ROLLUP_TMP;
DELETE PA_FP_ROLLUP_TMP;
INSERT INTO PA_FP_ROLLUP_TMP (
RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE )
VALUES (
l_tgt_res_asg_id_tab(i),
l_txn_currency_code_tab(i) );
SELECT src_ra.RESOURCE_ASSIGNMENT_ID,
tgt_ra.RESOURCE_ASSIGNMENT_ID,
cmt.TXN_CURRENCY_CODE
BULK COLLECT
INTO l_sr_src_ra_id_tab,
l_sr_tgt_ra_id_tab,
l_sr_txn_currency_code_tab
FROM pa_resource_assignments src_ra,
pa_resource_assignments tgt_ra,
pa_tasks ta,
pa_fp_rollup_tmp cmt
WHERE tgt_ra.resource_assignment_id = cmt.resource_assignment_id
AND ta.task_id (+) = NVL(tgt_ra.task_id,0) -- A. check billability
AND NVL(ta.billable_flag,'Y') = 'Y' -- A. check billability
AND src_ra.task_id = tgt_ra.task_id
AND src_ra.resource_list_member_id = tgt_ra.resource_list_member_id
AND tgt_ra.budget_version_id = p_budget_version_id
AND src_ra.budget_version_id = l_fp_src_plan_ver_id
AND tgt_ra.project_id = p_project_id
AND src_ra.project_id = p_project_id
AND NOT EXISTS ( SELECT null -- B. check for existing gen_tmp records
FROM pa_fp_gen_rate_tmp gen_tmp
WHERE gen_tmp.TARGET_RES_ASG_ID = tgt_ra.resource_assignment_id
AND gen_tmp.txn_currency_code = cmt.txn_currency_code );
SELECT tmp1.RESOURCE_ASSIGNMENT_ID,
tmp1.TARGET_RES_ASG_ID,
cmt.TXN_CURRENCY_CODE
BULK COLLECT
INTO l_sr_src_ra_id_tab,
l_sr_tgt_ra_id_tab,
l_sr_txn_currency_code_tab
FROM pa_fp_calc_amt_tmp1 tmp1,
pa_tasks ta,
pa_fp_rollup_tmp cmt
WHERE tmp1.transaction_source_code = 'FINANCIAL_PLAN' -- C. check finplan
AND tmp1.target_res_asg_id = cmt.resource_assignment_id
AND ta.task_id (+) = NVL(tmp1.task_id,0) -- A. check billability
AND NVL(ta.billable_flag,'Y') = 'Y' -- A. check billability
AND NOT EXISTS ( SELECT null -- B. check for existing gen_tmp records
FROM pa_fp_gen_rate_tmp gen_tmp
WHERE gen_tmp.target_res_asg_id = tmp1.target_res_asg_id
AND gen_tmp.txn_currency_code = cmt.txn_currency_code );
DELETE FROM PA_BUDGET_LINES
WHERE budget_line_id = l_budget_line_id_tab(i);
UPDATE pa_fp_gen_rate_tmp
SET raw_cost_rate = null,
burdened_cost_rate = null;