[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT project_currency_code,projfunc_currency_code, name
INTO l_pc_code , l_pfc_code, l_project_name
FROM pa_projects_all
WHERE project_id = p_project_id;
SELECT prlm.alias, nvl(ra.task_id,0)
INTO l_resource_name, l_task_id
FROM pa_resource_list_members prlm, pa_Resource_assignments ra
WHERE ra.resource_assignment_id = p_res_asg_id
AND ra.resource_list_member_id = prlm.resource_list_member_id;
SELECT task_name
INTO l_task_name
FROM pa_tasks
WHERE task_id = l_task_id;
l_res_asn_id_tab.delete;
l_start_date_tab.delete;
l_end_date_tab.delete;
l_txn_currency_code_tab.delete;
l_txn_rev_tab.delete;
l_txn_rw_cost_tab.delete;
l_txn_burdend_cost_tab.delete;
l_projfunc_currency_code_tab.delete;
l_projfunc_cost_rate_type_tab.delete;
l_projfunc_cost_rate_tab.delete;
l_projfunc_cost_rate_date_tab.delete;
l_projfunc_rev_rate_type_tab.delete;
l_projfunc_rev_rate_tab.delete;
l_projfunc_rev_rate_date_tab.delete;
l_projfunc_raw_cost_tab.delete;
l_projfunc_burdened_cost_tab.delete;
l_projfunc_revenue_tab.delete;
l_projfunc_rejection_tab.delete;
l_proj_raw_cost_tab.delete;
l_proj_burdened_cost_tab.delete;
l_proj_revenue_tab.delete;
l_proj_rejection_tab.delete;
l_proj_currency_code_tab.delete;
l_proj_cost_rate_type_tab.delete;
l_proj_cost_rate_tab.delete;
l_proj_cost_rate_date_tab.delete;
l_proj_rev_rate_type_tab.delete;
l_proj_rev_rate_tab.delete;
l_proj_rev_rate_date_tab.delete;
l_user_validate_flag_tab.delete;
SELECT PROJECT_REV_RATE_TYPE,
DECODE(opt.PROJECT_REV_RATE_TYPE,
'User', NULL,
DECODE(opt.PROJECT_REV_RATE_DATE_TYPE,
'START_DATE',P_START_DATE,
'END_DATE' ,P_END_DATE,
opt.PROJECT_REV_RATE_DATE)),
DECODE(opt.PROJECT_REV_RATE_TYPE,
'User', tc.PROJECT_REV_EXCHANGE_RATE,
NULL),
PROJFUNC_REV_RATE_TYPE,
DECODE(opt.PROJFUNC_REV_RATE_TYPE,
'User', NULL,
DECODE(opt.PROJFUNC_REV_RATE_DATE_TYPE,
'START_DATE',P_START_DATE,
'END_DATE' ,P_END_DATE,
opt.PROJFUNC_REV_RATE_DATE)),
DECODE(opt.PROJFUNC_REV_RATE_TYPE,
'User', tc.PROJFUNC_REV_EXCHANGE_RATE,
NULL)
INTO l_proj_rev_rate_type_tab(1),
l_proj_rev_rate_date_tab(1),
l_proj_rev_rate_tab(1),
l_projfunc_rev_rate_type_tab(1),
l_projfunc_rev_rate_date_tab(1),
l_projfunc_rev_rate_tab(1)
FROM pa_proj_fp_options opt,
pa_fp_txn_currencies tc
WHERE opt.fin_plan_version_id = P_BUDGET_VERSION_ID
--AND opt.fin_plan_version_id = tc.fin_plan_version_id(+)
AND opt.proj_fp_options_id = tc.proj_fp_options_id(+) /* Added for Bug 5112436 */
AND tc.txn_currency_code(+) = p_currency_code;
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_BILLING_AMOUNTS.GEN_BILLING_AMOUNTS';
SELECT /*+ INDEX(TMP,PA_RES_LIST_MAP_TMP4_N2)*/
P.RESOURCE_ASSIGNMENT_ID,
V.BILL_TRANS_CURRENCY_CODE,
PAP.PERIOD_NAME,
PAP.START_DATE,
PAP.END_DATE,
SUM(DECODE(ET.EVENT_TYPE_CLASSIFICATION,
'WRITE OFF', -1 * NVL(V.BILL_TRANS_REV_AMOUNT,0),
'REALIZED_LOSSES', -1 * NVL(V.BILL_TRANS_REV_AMOUNT,0),
NVL(V.BILL_TRANS_REV_AMOUNT,0)))
FROM PA_EVENTS_DELIVERABLE_V V,
PA_EVENT_TYPES ET,
PA_RES_LIST_MAP_TMP4 TMP,
PA_RESOURCE_ASSIGNMENTS P,
PA_PERIODS PAP
WHERE TMP.TXN_SOURCE_ID = V.EVENT_ID
AND V.EVENT_TYPE = ET.EVENT_TYPE
AND V.PROJECT_ID = P_PROJECT_ID
AND P.RESOURCE_ASSIGNMENT_ID = TMP.TXN_RESOURCE_ASSIGNMENT_ID
AND P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND c_tphase = 'P'
AND V.EVENT_DATE >= NVL(c_etc_start_date, V.EVENT_DATE)
AND V.EVENT_DATE BETWEEN PAP.START_DATE AND PAP.END_DATE
GROUP BY P.RESOURCE_ASSIGNMENT_ID,
V.BILL_TRANS_CURRENCY_CODE,
PAP.PERIOD_NAME,
PAP.START_DATE,
PAP.END_DATE
UNION ALL
SELECT /*+ INDEX(TMP,PA_RES_LIST_MAP_TMP4_N2)*/
P.RESOURCE_ASSIGNMENT_ID,
V.BILL_TRANS_CURRENCY_CODE,
GLP.PERIOD_NAME,
GLP.START_DATE,
GLP.END_DATE,
SUM(DECODE(ET.EVENT_TYPE_CLASSIFICATION,
'WRITE OFF', -1 * NVL(V.BILL_TRANS_REV_AMOUNT,0),
'REALIZED_LOSSES', -1 * NVL(V.BILL_TRANS_REV_AMOUNT,0),
NVL(V.BILL_TRANS_REV_AMOUNT,0)))
FROM PA_EVENTS_DELIVERABLE_V V,
PA_EVENT_TYPES ET,
PA_RES_LIST_MAP_TMP4 TMP,
PA_RESOURCE_ASSIGNMENTS P,
GL_PERIOD_STATUSES GLP
WHERE TMP.TXN_SOURCE_ID = V.EVENT_ID
AND V.EVENT_TYPE = ET.EVENT_TYPE
AND V.PROJECT_ID = P_PROJECT_ID
AND P.RESOURCE_ASSIGNMENT_ID = TMP.TXN_RESOURCE_ASSIGNMENT_ID
AND P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND c_tphase = 'G'
AND V.EVENT_DATE >= NVL(c_etc_start_date, V.EVENT_DATE)
AND V.EVENT_DATE BETWEEN GLP.START_DATE AND GLP.END_DATE
AND GLP.APPLICATION_ID = c_appl_id
AND GLP.SET_OF_BOOKS_ID = c_set_of_books_id
AND GLP.ADJUSTMENT_PERIOD_FLAG = 'N'
GROUP BY P.RESOURCE_ASSIGNMENT_ID,
V.BILL_TRANS_CURRENCY_CODE,
GLP.PERIOD_NAME,
GLP.START_DATE,
GLP.END_DATE
UNION ALL
SELECT /*+ INDEX(TMP,PA_RES_LIST_MAP_TMP4_N2)*/
P.RESOURCE_ASSIGNMENT_ID,
V.BILL_TRANS_CURRENCY_CODE,
TO_CHAR(NULL),
GET_EVENT_DATE(V.EVENT_DATE,c_etc_start_date,c_plan_class_code),
GET_EVENT_DATE(V.EVENT_DATE,c_etc_start_date,c_plan_class_code),
SUM(DECODE(ET.EVENT_TYPE_CLASSIFICATION,
'WRITE OFF', -1 * NVL(V.BILL_TRANS_REV_AMOUNT,0),
'REALIZED_LOSSES', -1 * NVL(V.BILL_TRANS_REV_AMOUNT,0),
NVL(V.BILL_TRANS_REV_AMOUNT,0)))
FROM PA_EVENTS_DELIVERABLE_V V,
PA_EVENT_TYPES ET,
PA_RES_LIST_MAP_TMP4 TMP,
PA_RESOURCE_ASSIGNMENTS P
WHERE TMP.TXN_SOURCE_ID = V.EVENT_ID
AND V.EVENT_TYPE = ET.EVENT_TYPE
AND V.PROJECT_ID = P_PROJECT_ID
AND V.EVENT_DATE >= NVL(c_etc_start_date, V.EVENT_DATE)
AND P.RESOURCE_ASSIGNMENT_ID = TMP.TXN_RESOURCE_ASSIGNMENT_ID
AND P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND c_tphase = 'N'
GROUP BY P.RESOURCE_ASSIGNMENT_ID,
V.BILL_TRANS_CURRENCY_CODE,
TO_CHAR(null),
GET_EVENT_DATE(V.EVENT_DATE,c_etc_start_date,c_plan_class_code),
GET_EVENT_DATE(V.EVENT_DATE,c_etc_start_date,c_plan_class_code);
l_last_updated_by NUMBER := FND_GLOBAL.user_id;
l_last_update_login NUMBER := FND_GLOBAL.login_id;
l_DELETED_RES_ASG_ID_TAB PA_PLSQL_DATATYPES.IdTabTyp;
DELETE FROM PA_RES_LIST_MAP_TMP1;
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 PROJECT_ID,
nvl(TASK_ID,0),
EVENT_ID,
EVENT_TYPE,
'BILLING_EVENTS',
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
event_date,
event_date,
DECODE(EVENT_TYPE,null,NULL,'EVENT_TYPE'),
'FINANCIAL_ELEMENTS'
BULK COLLECT
INTO l_project_id_tab,
l_TXN_TASK_ID_tab,
l_TXN_SOURCE_ID_tab,
l_EVENT_TYPE_tab,
l_TXN_SOURCE_TYPE_CODE_tab,
l_ORGANIZATION_ID_tab,
l_INVENTORY_ITEM_ID_tab,
l_TXN_PLAN_START_DATE_tab,
l_TXN_PLAN_END_DATE_tab,
l_FC_RES_TYPE_CODE_tab,
l_RESOURCE_CLASS_CODE_tab
FROM PA_EVENTS_DELIVERABLE_V
WHERE PROJECT_ID = P_PROJECT_ID;
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;
/* Calling the API to update the tmp4
table with resource_assignment_id */
IF p_pa_debug_mode = 'Y' THEN
pa_fp_gen_amount_utils.fp_debug
(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 => 'BILLING_EVENTS',
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);
pa_fp_gen_budget_amt_pub.update_res_asg'
||x_return_status,
p_module_name => l_module_name,
p_log_level => 5);
/*dbms_output.put_line('Status of update res asg api:
'||X_RETURN_STATUS);*/
PX_DELETED_RES_ASG_ID_TAB => l_DELETED_RES_ASG_ID_TAB,
X_RETURN_STATUS => X_RETURN_STATUS,
X_MSG_COUNT => X_MSG_COUNT,
X_MSG_DATA => X_MSG_DATA);
PX_GEN_RES_ASG_ID_TAB.delete;
l_res_asg_id.delete;
l_currency_code.delete;
l_tphase.delete;
l_billstart_date.delete;
l_billend_date.delete;
l_rev_sum.delete;
SELECT NVL(approved_rev_plan_type_flag,'N')
INTO l_approved_rev_plan_type_flag
FROM pa_budget_versions
WHERE budget_version_id = p_budget_version_id;
SELECT BUDGET_LINE_ID,
TXN_RAW_COST - NVL(TXN_INIT_RAW_COST,0)
INTO l_budget_line_id,
l_txn_raw_cost
FROM PA_BUDGET_LINES BL
WHERE BL.RESOURCE_ASSIGNMENT_ID = l_resource_asg_id
AND BL.TXN_CURRENCY_CODE = l_curr_code;
SELECT BUDGET_LINE_ID,
TXN_RAW_COST
INTO l_budget_line_id,
l_txn_raw_cost
FROM PA_BUDGET_LINES BL
WHERE BL.RESOURCE_ASSIGNMENT_ID = l_resource_asg_id
AND BL.TXN_CURRENCY_CODE = l_curr_code
AND BL.START_DATE = l_start_date;
/* if the record does not exist then insert
the record into the pa_budget_lines table */
-- dbms_output.put_line('inside insert bl');
INSERT INTO PA_BUDGET_LINES (
RESOURCE_ASSIGNMENT_ID,
START_DATE,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
END_DATE,
TXN_CURRENCY_CODE,
TXN_REVENUE,
BUDGET_LINE_ID,
BUDGET_VERSION_ID,
PROJECT_CURRENCY_CODE,
PROJFUNC_CURRENCY_CODE,
QUANTITY,
TXN_BILL_RATE_OVERRIDE,
TXN_COST_RATE_OVERRIDE, -- Added for Bug 5059327
BURDEN_COST_RATE_OVERRIDE, -- Added for Bug 5059327
PERIOD_NAME )
VALUES (
l_resource_asg_id,
l_start_date,
l_sysdate,
l_last_updated_by,
l_sysdate,
l_last_updated_by,
l_last_update_login,
l_end_date,
l_curr_code,
l_bill_trans_rev_amount,
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_bill_trans_rev_amount,
1,
decode(p_fp_cols_rec.x_version_type,'ALL',0,null), -- Added for Bug 5059327
decode(p_fp_cols_rec.x_version_type,'ALL',0,null), -- Added for Bug 5059327
l_time_phase );
/* if the record does exist then update
the record in the pa_budget_lines table */
/* dbms_output.put_line('inside update bl');
dbms_output.put_line('budget line id in update '||
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
, TXN_REVENUE = NVL(TXN_REVENUE,0) + l_bill_trans_rev_amount
, quantity = nvl(quantity,0) + l_bill_trans_rev_amount
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
, TXN_REVENUE = NVL(TXN_REVENUE,0) + l_bill_trans_rev_amount
, txn_bill_rate_override =
decode(p_fp_cols_rec.x_time_phased_code,'N',
decode((nvl(quantity,0)-nvl(init_quantity,0)),0,null,
(NVL(TXN_REVENUE,0) + l_bill_trans_rev_amount)
/(nvl(quantity,0)-nvl(init_quantity,0)) ),
decode( nvl(quantity,0),0,null,
(NVL(TXN_REVENUE,0) + l_bill_trans_rev_amount)/quantity ))
--, quantity = nvl(quantity,0) + l_bill_trans_rev_amount
WHERE BUDGET_LINE_ID = l_budget_line_id;
/*dbms_output.put_line('No.of records inserted into
bdgt lines table: '||l_icount);
dbms_output.put_line('No.of records updated into
bdgt lines table: '||l_ucount);*/
pa_fp_gen_budget_amt_pub.insert_txn_currency',
p_module_name => l_module_name,
p_log_level => 5);
PA_FP_GEN_BUDGET_AMT_PUB.INSERT_TXN_CURRENCY
(P_PROJECT_ID => P_PROJECT_ID,
P_BUDGET_VERSION_ID => P_BUDGET_VERSION_ID,
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);
pa_fp_gen_budget_amt_pub.insert_txn_currency'
||x_return_status,
p_module_name => l_module_name,
p_log_level => 5);
/* dbms_output.put_line('Status of insert txn currency api:
'||X_RETURN_STATUS);*/
DELETE pa_fp_rollup_tmp;
DELETE pa_resource_asgn_curr_tmp;
INSERT INTO pa_fp_rollup_tmp (
RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE )
VALUES (
l_res_asg_id(i),
l_currency_code(i) );
INSERT INTO pa_resource_asgn_curr_tmp (
RESOURCE_ASSIGNMENT_ID,
TXN_CURRENCY_CODE,
TXN_RAW_COST_RATE_OVERRIDE,
TXN_BURDEN_COST_RATE_OVERRIDE,
TXN_BILL_RATE_OVERRIDE )
SELECT bl.resource_assignment_id,
bl.txn_currency_code,
0,
0,
1
FROM pa_budget_lines bl,
(SELECT DISTINCT resource_assignment_id, txn_currency_code
FROM pa_fp_rollup_tmp) tmp
WHERE bl.resource_assignment_id = tmp.resource_assignment_id
AND bl.txn_currency_code = tmp.txn_currency_code
AND bl.budget_version_id = p_budget_version_id
AND bl.cost_rejection_code is null
AND bl.revenue_rejection_code is null
AND bl.burden_rejection_code is null
AND bl.other_rejection_code is null
AND bl.pc_cur_conv_rejection_code is null
AND bl.pfc_cur_conv_rejection_code is null
GROUP BY bl.resource_assignment_id,
bl.txn_currency_code
HAVING NVL(sum(txn_raw_cost)-nvl(sum(txn_init_raw_cost),0),0) = 0;
P_ROLLUP_FLAG => 'N', -- 'N' indicates Insert
--P_CALLED_MODE => p_called_mode,
X_RETURN_STATUS => x_return_status,
X_MSG_COUNT => x_msg_count,
X_MSG_DATA => x_msg_data );
SELECT etc_start_date
INTO l_etc_start_date
FROM pa_budget_versions
WHERE budget_version_id = P_BUDGET_VERSION_ID;
SELECT V.EVENT_DATE,
V.BILL_TRANS_CURRENCY_CODE,
SUM(DECODE(ET.EVENT_TYPE_CLASSIFICATION,
'WRITE OFF', -1 * NVL(V.BILL_TRANS_REV_AMOUNT,0),
'REALIZED_LOSSES', -1 * NVL(V.BILL_TRANS_REV_AMOUNT,0),
NVL(V.BILL_TRANS_REV_AMOUNT,0))),
SUM(DECODE(ET.EVENT_TYPE_CLASSIFICATION,
'WRITE OFF', -1 * NVL(V.PROJFUNC_REVENUE_AMOUNT,0),
'REALIZED_LOSSES', -1 * NVL(V.PROJFUNC_REVENUE_AMOUNT,0),
NVL(V.PROJFUNC_REVENUE_AMOUNT,0))),
SUM(DECODE(ET.EVENT_TYPE_CLASSIFICATION,
'WRITE OFF', -1 * NVL(V.PROJECT_REVENUE_AMOUNT,0),
'RZED_LOSSES', -1 * NVL(V.PROJECT_REVENUE_AMOUNT,0),
NVL(V.PROJECT_REVENUE_AMOUNT,0)))
BULK COLLECT
INTO l_event_date_tab,
l_txn_currency_code_tab,
l_txn_rev_amt_tab,
l_projfunc_rev_amt_tab,
l_project_rev_amt_tab
FROM PA_EVENTS_DELIVERABLE_V V,
PA_EVENT_TYPES ET
WHERE V.PROJECT_ID = P_PROJECT_ID
AND V.EVENT_DATE >= NVL(l_etc_start_date, V.EVENT_DATE)
AND V.EVENT_TYPE = ET.EVENT_TYPE
AND NVL(V.BILL_TRANS_REV_AMOUNT,0) <> 0
GROUP BY V.EVENT_DATE,
V.BILL_TRANS_CURRENCY_CODE;
SELECT /*+ INDEX(TMP,PA_RES_LIST_MAP_TMP4_N2)*/
V.EVENT_DATE,
V.BILL_TRANS_CURRENCY_CODE,
SUM(DECODE(ET.EVENT_TYPE_CLASSIFICATION,
'WRITE OFF', -1 * NVL(V.BILL_TRANS_REV_AMOUNT,0),
'REALIZED_LOSSES', -1 * NVL(V.BILL_TRANS_REV_AMOUNT,0),
NVL(V.BILL_TRANS_REV_AMOUNT,0))),
SUM(DECODE(ET.EVENT_TYPE_CLASSIFICATION,
'WRITE OFF', -1 * NVL(V.PROJFUNC_REVENUE_AMOUNT,0),
'REALIZED_LOSSES', -1 * NVL(V.PROJFUNC_REVENUE_AMOUNT,0),
NVL(V.PROJFUNC_REVENUE_AMOUNT,0))),
SUM(DECODE(ET.EVENT_TYPE_CLASSIFICATION,
'WRITE OFF', -1 * NVL(V.PROJECT_REVENUE_AMOUNT,0),
'RZED_LOSSES', -1 * NVL(V.PROJECT_REVENUE_AMOUNT,0),
NVL(V.PROJECT_REVENUE_AMOUNT,0)))
BULK COLLECT
INTO l_event_date_tab,
l_txn_currency_code_tab,
l_txn_rev_amt_tab,
l_projfunc_rev_amt_tab,
l_project_rev_amt_tab
FROM PA_EVENTS_DELIVERABLE_V V,
PA_EVENT_TYPES ET,
PA_RES_LIST_MAP_TMP4 TMP
--,PA_RESOURCE_ASSIGNMENTS RA
WHERE V.PROJECT_ID = P_PROJECT_ID
AND V.EVENT_DATE >= NVL(l_etc_start_date, V.EVENT_DATE)
AND V.EVENT_TYPE = ET.EVENT_TYPE
AND NVL(V.BILL_TRANS_REV_AMOUNT,0) <> 0
AND TMP.TXN_SOURCE_ID = V.EVENT_ID
--AND RA.RESOURCE_ASSIGNMENT_ID = TMP.TXN_RESOURCE_ASSIGNMENT_ID
--AND RA.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
GROUP BY V.EVENT_DATE,
V.BILL_TRANS_CURRENCY_CODE;
DELETE FROM PA_RES_LIST_MAP_TMP1;
DELETE FROM PA_RES_LIST_MAP_TMP4;
SELECT PROJECT_ID,
nvl(TASK_ID,0),
EVENT_ID,
EVENT_TYPE,
'BILLING_EVENTS',
ORGANIZATION_ID,
INVENTORY_ITEM_ID,
event_date,
event_date,
DECODE(EVENT_TYPE,null,NULL,'EVENT_TYPE'),
'FINANCIAL_ELEMENTS'
BULK COLLECT
INTO l_project_id_tab,
l_TXN_TASK_ID_tab,
l_TXN_SOURCE_ID_tab,
l_EVENT_TYPE_tab,
l_TXN_SOURCE_TYPE_CODE_tab,
l_ORGANIZATION_ID_tab,
l_INVENTORY_ITEM_ID_tab,
l_TXN_PLAN_START_DATE_tab,
l_TXN_PLAN_END_DATE_tab,
l_FC_RES_TYPE_CODE_tab,
l_RESOURCE_CLASS_CODE_tab
FROM PA_EVENTS_DELIVERABLE_V
WHERE PROJECT_ID = P_PROJECT_ID;
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;
* This procedure updates PA_RES_LIST_TMP4 records with the proper
* txn_resource_assignment_id. Additionally, if the Retain Manually
* Added Plan Lines option is enabled, then records for manually
* added resources are deleted from the tmp4 table.
*
* The logic for this procedure has been taken directly from the
* UPDATE_RES_ASG API (PAFPGAMB.pls version 115.90).
*
* This API has been created for the GET_BILLING_EVENT_AMT_IN_PFC
* API to address bug 4067836.
*
* Note: parameter P_WP_STRUCTURE_VER_ID has Default value of Null.
*/
PROCEDURE UPD_TMP4_TXN_RA_ID_AND_ML
(P_PROJECT_ID IN PA_PROJ_FP_OPTIONS.PROJECT_ID%TYPE,
P_BUDGET_VERSION_ID IN PA_BUDGET_VERSIONS.BUDGET_VERSION_ID%TYPE,
P_FP_COLS_REC IN PA_FP_GEN_AMOUNT_UTILS.FP_COLS,
P_GEN_SRC_CODE IN PA_PROJ_FP_OPTIONS.GEN_ALL_SRC_CODE%TYPE,
P_WP_STRUCTURE_VER_ID IN PA_BUDGET_VERSIONS.PROJECT_STRUCTURE_VERSION_ID%TYPE,
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_BILLING_AMOUNTS.UPD_TMP4_TXN_RA_ID_AND_ML';
SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
distinct P.RESOURCE_ASSIGNMENT_ID,
P.RESOURCE_LIST_MEMBER_ID
BULK COLLECT
INTO l_res_assgn_id_tab,
l_rlm_id_tab
FROM PA_RESOURCE_ASSIGNMENTS P,
PA_RES_LIST_MAP_TMP4 T
WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND NVL(P.TASK_ID,0) = 0
AND P.PROJECT_ASSIGNMENT_ID = -1
AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID;
UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
PA_RES_LIST_MAP_TMP4
SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i);
SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
P.RESOURCE_ASSIGNMENT_ID,
P.RESOURCE_LIST_MEMBER_ID,
NVL(T.TXN_TASK_ID,0)
BULK COLLECT
INTO l_res_assgn_id_tab,
l_rlm_id_tab,
l_txn_task_id_tab
FROM PA_RESOURCE_ASSIGNMENTS P,
PA_RES_LIST_MAP_TMP4 T
WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND NVL(P.TASK_ID,0) = NVL(T.TXN_TASK_ID,0)
AND P.PROJECT_ASSIGNMENT_ID = -1
AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID;
UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
PA_RES_LIST_MAP_TMP4
SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i)
AND NVL(TXN_TASK_ID,0) = l_txn_task_id_tab(i);
SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
P.RESOURCE_ASSIGNMENT_ID,
P.RESOURCE_LIST_MEMBER_ID,
NVL(P.TASK_ID,0),
NVL(T.TXN_TASK_ID,0)
BULK COLLECT
INTO l_res_assgn_id_tab,
l_rlm_id_tab,
l_txn_top_task_id_tab,
l_txn_sub_task_id_tab
FROM PA_RESOURCE_ASSIGNMENTS P,
PA_RES_LIST_MAP_TMP4 T,
PA_TASKS TS
WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND TS.TASK_ID(+) = NVL(T.TXN_TASK_ID,0)
AND NVL(P.TASK_ID,0) = NVL(TS.TOP_TASK_ID,0)
AND P.PROJECT_ASSIGNMENT_ID = -1
AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID;
UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
PA_RES_LIST_MAP_TMP4 tmp4
SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i)
AND NVL(TXN_TASK_ID,0) = l_txn_sub_task_id_tab(i);
SELECT resource_assignment_id,
resource_list_member_id,
txn_task_id,
mapped_fin_task_id
BULK COLLECT INTO
l_res_assgn_id_tab,
l_rlm_id_tab,
l_txn_task_id_tab,
l_mapped_task_id_tab
FROM
(
SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
P.RESOURCE_ASSIGNMENT_ID resource_assignment_id,
P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
NVL(T.TXN_TASK_ID,0) txn_task_id ,
NVL(V.MAPPED_FIN_TASK_ID,0) mapped_fin_task_id
FROM PA_RESOURCE_ASSIGNMENTS P,
PA_RES_LIST_MAP_TMP4 T,
PA_MAP_WP_TO_FIN_TASKS_V V
WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND V.PARENT_STRUCTURE_VERSION_ID = P_WP_STRUCTURE_VER_ID
AND NVL(T.TXN_TASK_ID,0) = NVL(V.PROJ_ELEMENT_ID,0)
AND P.PROJECT_ASSIGNMENT_ID = -1
AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
AND NVL(P.TASK_ID,0) = NVL(V.MAPPED_FIN_TASK_ID,0)
AND NVL(T.TXN_TASK_ID,0) > 0
union
SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
P.RESOURCE_ASSIGNMENT_ID resource_assignment_id,
P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
0 txn_task_id,
0 mapped_fin_task_id
FROM PA_RESOURCE_ASSIGNMENTS P,
PA_RES_LIST_MAP_TMP4 T
WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND P.PROJECT_ASSIGNMENT_ID = -1
AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
AND NVL(P.TASK_ID,0) = 0 );
(p_msg => 'within update when share partial and planning at lowest task i:'
||i||'; ra id in cursor:'||l_res_assgn_id_tab(i)
UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
PA_RES_LIST_MAP_TMP4
SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i)
AND NVL(TXN_TASK_ID,0) = l_txn_task_id_tab(i);
SELECT resource_assignment_id,
resource_list_member_id,
txn_task_id,
mapped_fin_task_id
BULK COLLECT INTO
l_res_assgn_id_tab,
l_rlm_id_tab,
l_txn_task_id_tab,
l_mapped_task_id_tab
FROM
(
SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
P.RESOURCE_ASSIGNMENT_ID resource_assignment_id,
P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
NVL(T.TXN_TASK_ID,0) txn_task_id,
NVL(V.MAPPED_FIN_TASK_ID,0) mapped_fin_task_id
FROM PA_RESOURCE_ASSIGNMENTS P,
PA_RES_LIST_MAP_TMP4 T,
PA_MAP_WP_TO_FIN_TASKS_V V,
PA_TASKS TS
WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND V.PARENT_STRUCTURE_VERSION_ID = P_WP_STRUCTURE_VER_ID
AND t.txn_task_id = v.PROJ_ELEMENT_ID
AND NVL(TS.top_TASK_ID,0) = NVL(p.task_id,0)
AND TS.TASK_ID(+) = NVL(V.MAPPED_FIN_TASK_ID,0)
AND P.PROJECT_ASSIGNMENT_ID = -1
AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
AND NVL(T.TXN_TASK_ID,0) > 0
union
SELECT /*+ INDEX(T,PA_RES_LIST_MAP_TMP4_N1)*/
DISTINCT P.RESOURCE_ASSIGNMENT_ID resource_assignment_id,
P.RESOURCE_LIST_MEMBER_ID resource_list_member_id,
0 txn_task_id,
0 mapped_fin_task_id
FROM PA_RESOURCE_ASSIGNMENTS P,
PA_RES_LIST_MAP_TMP4 T
WHERE P.BUDGET_VERSION_ID = P_BUDGET_VERSION_ID
AND P.PROJECT_ASSIGNMENT_ID = -1
AND T.RESOURCE_LIST_MEMBER_ID = P.RESOURCE_LIST_MEMBER_ID
AND NVL(P.TASK_ID,0) = 0
AND NVL(T.TXN_TASK_ID,0) = NVL(P.TASK_ID,0) );
UPDATE /*+ INDEX(PA_RES_LIST_MAP_TMP4,PA_RES_LIST_MAP_TMP4_N1)*/
PA_RES_LIST_MAP_TMP4
SET TXN_RESOURCE_ASSIGNMENT_ID = l_res_assgn_id_tab(i)
WHERE RESOURCE_LIST_MEMBER_ID = l_rlm_id_tab(i)
AND NVL(TXN_TASK_ID,0) = l_txn_task_id_tab(i);
DELETE FROM pa_res_list_map_tmp4 tmp
WHERE EXISTS
( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id
AND ra.resource_assignment_id = tmp.txn_resource_assignment_id
AND ra.transaction_source_code IS NULL
AND EXISTS
( SELECT 1
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = ra.resource_assignment_id
AND rownum = 1 ));
DELETE FROM pa_res_list_map_tmp4 tmp
WHERE EXISTS
( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id
AND ra.resource_assignment_id = tmp.txn_resource_assignment_id
AND ra.transaction_source_code IS NULL
AND EXISTS
( SELECT 1
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = ra.resource_assignment_id
AND bl.start_date >= l_etc_start_date
AND rownum = 1 ));
DELETE FROM pa_res_list_map_tmp4 tmp
WHERE EXISTS
( SELECT /*+ INDEX(tmp,PA_RES_LIST_MAP_TMP4_N2)*/ 1
FROM pa_resource_assignments ra
WHERE ra.budget_version_id = p_budget_version_id
AND ra.resource_assignment_id = tmp.txn_resource_assignment_id
AND ra.transaction_source_code IS NULL
AND EXISTS
( SELECT 1
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = ra.resource_assignment_id
AND NVL(quantity,0) <> NVL(init_quantity,0)
AND rownum = 1 ));