[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Billabliity and MRC Enhancements: Note declaring table of records cannot be used in Bulk updates / bulk inserts
* so 9.2.5.0 version donot support the builk-in-bind of table of records with indexes
* so commenting it out for time being
TYPE mrc_fpRec IS RECORD (
budget_line_id pa_fp_rollup_tmp.budget_line_id%type
,resource_assignment_id pa_fp_rollup_tmp.resource_assignment_id%type
,txn_currency_code pa_fp_rollup_tmp.txn_currency_code%type
,start_date pa_fp_rollup_tmp.start_date%type
,end_date pa_fp_rollup_tmp.start_date%type
,period_name pa_fp_rollup_tmp.period_name%type
,quantity Number
,txn_raw_cost Number
,txn_burdened_cost Number
,txn_revenue Number
,project_currency_code pa_fp_rollup_tmp.txn_currency_code%type
,project_raw_cost Number
,project_burden_cost Number
,project_revenue Number
,projfunc_currency_code pa_fp_rollup_tmp.txn_currency_code%type
,projfunc_raw_cost Number
,projfunc_burden_cost Number
,projfunc_revenue Number
,delete_flag pa_fp_rollup_tmp.delete_flag%type
,Billable_flag pa_fp_rollup_tmp.delete_flag%type
,project_cost_rate_type pa_fp_rollup_tmp.project_cost_rate_type%type
,project_cost_exchange_rate pa_fp_rollup_tmp.project_cost_exchange_rate%type
,project_cost_rate_date_type pa_fp_rollup_tmp.project_cost_rate_date_type%type
,project_cost_rate_date pa_fp_rollup_tmp.project_cost_rate_date%type
,project_rev_rate_type pa_fp_rollup_tmp.project_rev_rate_type%type
,project_rev_exchange_rate pa_fp_rollup_tmp.project_rev_exchange_rate%type
,project_rev_rate_date_type pa_fp_rollup_tmp.project_rev_rate_date_type%type
,project_rev_rate_date pa_fp_rollup_tmp.project_rev_rate_date%type
,projfunc_cost_rate_type pa_fp_rollup_tmp.projfunc_cost_rate_type%type
,projfunc_cost_exchange_rate pa_fp_rollup_tmp.projfunc_cost_exchange_rate%type
,projfunc_cost_rate_date_type pa_fp_rollup_tmp.projfunc_cost_rate_date_type%type
,projfunc_cost_rate_date pa_fp_rollup_tmp.projfunc_cost_rate_date%type
,projfunc_rev_rate_type pa_fp_rollup_tmp.projfunc_rev_rate_type%type
,projfunc_rev_exchange_rate pa_fp_rollup_tmp.projfunc_rev_exchange_rate%type
,projfunc_rev_rate_date_type pa_fp_rollup_tmp.projfunc_rev_rate_date_type%type
,projfunc_rev_rate_date pa_fp_rollup_tmp.projfunc_rev_rate_date%type
);
g_mrc_delete_flag_tab pa_plsql_datatypes.Char1TabTyp;
SELECT nvl(pfo.use_planning_rates_flag,'N') use_planning_rates_flag
,decode(nvl(bv.wp_version_flag,'N'),'Y',NVL(pfo.track_workplan_costs_flag,'N'),'Y') track_workplan_costs_flag
,bv.version_type
,bv.resource_list_id
,bv.approved_rev_plan_type_flag
,nvl(pfo.plan_in_multi_curr_flag,'N') plan_in_multi_curr_flag
,bv.etc_start_date
,nvl(bv.wp_version_flag,'N') wp_version_flag
,decode(bv.version_type,
'COST',NVL(pfo.cost_time_phased_code,'N'),
'REVENUE',NVL(pfo.revenue_time_phased_code,'N'),
NVL(pfo.all_time_phased_code,'N')) time_phased_code
,bv.project_structure_version_id
,bv.project_id
,pp.project_currency_code
,pp.projfunc_currency_code
,pp.segment1 project_Name
,bv.ci_id CiId
/*Bugfix:4272944 */
,NVL(pp.baseline_funding_flag,'N') baseline_funding_flag
,decode(fpt.plan_class_code,'BUDGET'
,decode(bv.wp_version_flag,'Y','WORKPLAN',fpt.plan_class_code),fpt.plan_class_code) Plan_Class_Type
FROM pa_proj_fp_options pfo
,pa_budget_versions bv
,pa_projects_all pp
,pa_fin_plan_types_b fpt
WHERE pfo.fin_plan_version_id = bv.budget_version_id
AND bv.project_id = pp.project_id
AND fpt.fin_plan_type_id = pfo.fin_plan_type_id
AND bv.budget_version_id = p_budget_version_id;
SELECT /*+ INDEX(blavgrt PA_BUDGET_LINES_U1) */
AVG(DECODE((nvl(blavgrt.quantity,0) - nvl(blavgrt.init_quantity,0)),0,NULL
,blavgrt.txn_cost_rate_override)) avg_txn_cost_rate_override
,AVG(DECODE((nvl(blavgrt.quantity,0) - nvl(blavgrt.init_quantity,0)),0,NULL
,blavgrt.burden_cost_rate_override)) avg_burden_cost_rate_override
,AVG(DECODE((nvl(blavgrt.quantity,0) - nvl(blavgrt.init_quantity,0)),0,NULL
,blavgrt.txn_bill_rate_override)) avg_txn_bill_rate_override
/* Bug fix: 5172318 Not required as part of IPM changes
,SUM(nvl(blavgrt.quantity,0) - nvl(blavgrt.init_quantity,0)) sum_etc_Qty
,SUM(nvl(blavgrt.init_quantity,0)) sum_Actual_Qty
,SUM(nvl(blavgrt.quantity,0)) sum_Plan_Qty
*/
/*bug fix:4693839 */
,AVG(NVL(blavgrt.txn_cost_rate_override,blavgrt.txn_standard_cost_rate)) avg_zero_null_cost_rate
,AVG(NVL(blavgrt.burden_cost_rate_override,blavgrt.burden_cost_rate)) avg_zero_null_burden_rate
,AVG(NVL(blavgrt.txn_bill_rate_override,blavgrt.txn_standard_bill_rate)) avg_zero_null_bill_rate
FROM pa_budget_lines blavgrt
WHERE blavgrt.resource_assignment_id = p_resource_asg_id
AND blavgrt.txn_currency_code = p_txn_curr_code
AND ( (p_line_start_date is NULL AND p_line_end_date is NULL )
OR
(p_line_start_date is NOT NULL AND p_line_end_date is NOT NULL
AND blavgrt.start_date BETWEEN p_line_start_date AND p_line_end_date)
);
SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
decode(sum(bl.quantity),0,NULL,sum(bl.quantity)) quantity
,decode( sum(bl.txn_raw_cost),0,NULL,sum(bl.txn_raw_cost)) txn_raw_cost
,decode(sum(bl.txn_burdened_cost),0,NULL,sum(bl.txn_burdened_cost)) txn_burdened_cost
,decode(sum(bl.txn_revenue),0,NULL,sum(bl.txn_revenue)) txn_revenue
-- Actuals for ETC calculation
,decode(sum(bl.init_quantity),0,NULL,sum(bl.init_quantity)) init_quantity
,decode(sum(bl.txn_init_raw_cost),0,NULL,sum(bl.txn_init_raw_cost)) init_raw_cost
,decode(sum(bl.txn_init_burdened_cost),0,NULL,sum(bl.txn_init_burdened_cost)) init_burdened_cost
,decode(sum(bl.txn_init_revenue),0,NULL,sum(bl.txn_init_revenue)) init_revenue
,(sum(decode(p_avg_txn_cost_rate_override,NULL,NULL
,decode((nvl(bl.quantity,0) - nvl(bl.init_quantity,0)),0,NULL
,decode(nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)),0,NULL
,((nvl(bl.quantity,0)- nvl(bl.init_quantity,0)) *
nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)))))))
/ DECODE((sum(decode(p_avg_txn_cost_rate_override,NULL,NULL
,decode((nvl(bl.quantity,0)- nvl(bl.init_quantity,0)),0,NULL
,decode(nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)),0,null
,(nvl(bl.quantity,0)- nvl(bl.init_quantity,0)))))))
,0,NULL,
(sum(decode(p_avg_txn_cost_rate_override,NULL,NULL
,decode((nvl(bl.quantity,0)- nvl(bl.init_quantity,0)),0,NULL
,decode(nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)),0,null
,(nvl(bl.quantity,0)- nvl(bl.init_quantity,0)))))))
)
) etc_cost_rate_override
,(sum(decode(p_avg_burden_rate_override,NULL,NULL
,decode((nvl(bl.quantity,0) - nvl(bl.init_quantity,0)),0,NULL
,decode(nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)),0,NULL
,((nvl(bl.quantity,0)- nvl(bl.init_quantity,0)) *
nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)))))))
/ DECODE((sum(decode(p_avg_burden_rate_override,NULL,NULL
,decode((nvl(bl.quantity,0)- nvl(bl.init_quantity,0)),0,NULL
,decode(nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)),0,NULL
,(nvl(bl.quantity,0)- nvl(bl.init_quantity,0)))))))
,0,NULL,
(sum(decode(p_avg_burden_rate_override,NULL,NULL
,decode((nvl(bl.quantity,0)- nvl(bl.init_quantity,0)),0,NULL
,decode(nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)),0,NULL
,(nvl(bl.quantity,0)- nvl(bl.init_quantity,0)))))))
)
) etc_burden_rate_override
,(sum(decode(p_avg_bill_rate_override,NULL,NULL
, decode((nvl(bl.quantity,0)- nvl(bl.init_quantity,0)),0,NULL
,decode(nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)),0,NULL
,((nvl(bl.quantity,0)- nvl(bl.init_quantity,0)) *
nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)))))))
/ DECODE((sum(decode(p_avg_bill_rate_override,NULL,NULL
,decode((nvl(bl.quantity,0)- nvl(bl.init_quantity,0)),0,NULL
,decode(nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)),0,null
,(nvl(bl.quantity,0)- nvl(bl.init_quantity,0)))))))
,0,NULL,
(sum(decode(p_avg_bill_rate_override,NULL,NULL
,decode((nvl(bl.quantity,0)- nvl(bl.init_quantity,0)),0,NULL
,decode(nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)),0,null
,(nvl(bl.quantity,0)- nvl(bl.init_quantity,0)))))))
)
) etc_bill_rate_override
,(sum(( decode((nvl(bl.quantity,0)- nvl(bl.init_quantity,0)),0,null
,decode(nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)),0,null
,((nvl(bl.quantity,0)- nvl(bl.init_quantity,0)) *
nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)))))))
/ DECODE((sum(decode((nvl(bl.quantity,0)- nvl(bl.init_quantity,0)),0,null
,decode(nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)),0,null
,(nvl(bl.quantity,0)- nvl(bl.init_quantity,0))))))
,0,NULL,
(sum(decode((nvl(bl.quantity,0)- nvl(bl.init_quantity,0)),0,null
,decode(nvl(bl.txn_bill_rate_override,nvl(bl.txn_standard_bill_rate,0)),0
,null,(nvl(bl.quantity,0)- nvl(bl.init_quantity,0))))))
)
) etc_bill_rate
,(sum(( decode((nvl(bl.quantity,0)- nvl(bl.init_quantity,0)),0,null
,decode(nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)),0,null
,((nvl(bl.quantity,0)- nvl(bl.init_quantity,0)) *
nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)))))))
/ DECODE((sum(decode((nvl(bl.quantity,0)- nvl(bl.init_quantity,0)),0,null
,decode(nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)),0,null
,(nvl(bl.quantity,0)- nvl(bl.init_quantity,0))))))
,0,NULL,
(sum(decode((nvl(bl.quantity,0)- nvl(bl.init_quantity,0)),0,null
,decode(nvl(bl.txn_cost_rate_override,nvl(bl.txn_standard_cost_rate,0)),0,null
,(nvl(bl.quantity,0)- nvl(bl.init_quantity,0))))))
)
) etc_cost_rate
,(sum(( decode((nvl(bl.quantity,0)- nvl(bl.init_quantity,0)),0,null
,decode(nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)),0,null
,((nvl(bl.quantity,0)- nvl(bl.init_quantity,0)) *
nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)))))))
/ DECODE((sum(decode((nvl(bl.quantity,0)- nvl(bl.init_quantity,0)),0,null
,decode(nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)),0,null
,(nvl(bl.quantity,0)- nvl(bl.init_quantity,0))))))
,0,NULL,
(sum(decode((nvl(bl.quantity,0)- nvl(bl.init_quantity,0)),0,null
,decode(nvl(bl.burden_cost_rate_override,nvl(bl.burden_cost_rate,0)),0,null
,(nvl(bl.quantity,0)- nvl(bl.init_quantity,0))))))
)
) etc_burden_rate
/* Bug fix:4693839 Currently all the UI page shows null instead of zeros, and when they pass to param value
* will be passed null, representing no change, but some other API like AMG etc, may pass zero in param value.
* In order to avoid changing all the calling api, this api is modified to handle nulls and zeros carefully */
,SUM(bl.quantity) bl_zero_null_quantity
,SUM(bl.txn_raw_cost) bl_zero_null_rawcost
,SUM(bl.txn_burdened_cost) bl_zero_null_burdencost
,SUM(bl.txn_revenue) bl_zero_null_revenue
,SUM(bl.display_quantity) display_quantity --Bug 6429285
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = p_resource_asg_id
AND bl.txn_currency_code = p_txn_curr_code
AND ( (p_line_start_date is NULL AND p_line_end_date is NULL )
OR
(p_line_start_date is NOT NULL AND p_line_end_date is NOT NULL
AND bl.start_date BETWEEN p_line_start_date AND p_line_end_date)
) ;
SELECT decode(rtx.total_quantity,0,null,rtx.total_quantity) quantity
,decode( rtx.total_txn_raw_cost,0,NULL,rtx.total_txn_raw_cost) txn_raw_cost
,decode(rtx.total_txn_burdened_cost,0,NULL,rtx.total_txn_burdened_cost) txn_burdened_cost
,decode(rtx.total_txn_revenue,0,NULL,rtx.total_txn_revenue) txn_revenue
,decode(rtx.total_init_quantity,0,NULL,rtx.total_init_quantity) init_quantity
,decode(rtx.total_txn_init_raw_cost,0,NULL,rtx.total_txn_init_raw_cost) init_raw_cost
,decode(rtx.total_txn_init_burdened_cost,0,NULL,rtx.total_txn_init_burdened_cost) init_burdened_cost
,decode(rtx.total_txn_init_revenue,0,NULL,rtx.total_txn_init_revenue) init_revenue
,rtx.TXN_RAW_COST_RATE_OVERRIDE etc_cost_rate_override
,rtx.TXN_BURDEN_COST_RATE_OVERRIDE etc_burden_rate_override
,rtx.TXN_BILL_RATE_OVERRIDE etc_bill_rate_override
,rtx.TXN_ETC_BILL_RATE etc_bill_rate
,rtx.TXN_ETC_RAW_COST_RATE etc_cost_rate
,rtx.TXN_ETC_BURDEN_COST_RATE etc_burden_rate
,rtx.total_quantity bl_zero_null_quantity
,rtx.total_txn_raw_cost bl_zero_null_rawcost
,rtx.total_txn_burdened_cost bl_zero_null_burdencost
,rtx.total_txn_revenue bl_zero_null_revenue
,rtx.total_display_quantity display_quantity --Bug 6429285
FROM pa_resource_asgn_curr rtx
WHERE rtx.resource_assignment_id = p_resource_asg_id
AND rtx.txn_currency_code = p_txn_curr_code;
INSERT INTO PA_FP_CALCULATE_LOG
(SESSIONID
,SEQ_NUMBER
,LOG_MESSAGE)
VALUES
(userenv('sessionid')
,HR.PAY_US_GARN_FEE_RULES_S.nextval
,substr(P_MSG,1,240)
);
* These error message will be cleared upon any manual update to the ETC Rev amount through calculate process
*/
PROCEDURE clear_etc_rev_other_rejectns
(p_budget_version_id Number
,p_source_context Varchar2
,p_calling_module Varchar2
,p_mode Varchar2
,x_return_status OUT NOCOPY Varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY Varchar2
) IS
CURSOR cur_get_rejLines IS
SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
bl.resource_assignment_id
,bl.txn_currency_code
,sum(bl.txn_revenue)
,decode(p_source_context,'BUDGET_LINE',tmp.start_date,NULL) start_date
,decode(p_source_context,'BUDGET_LINE',tmp.end_date,NULL) end_date
FROM PA_BUDGET_LINES bl
,PA_FP_SPREAD_CALC_TMP tmp
WHERE tmp.budget_version_id = p_budget_version_id
AND bl.resource_assignment_id = tmp.resource_assignment_id
AND bl.txn_currency_code = tmp.txn_currency_code
AND ((p_source_context = 'BUDGET_LINE'
and bl.start_date between tmp.start_date and tmp.end_date )
OR
p_source_context <> 'BUDGET_LINE'
)
AND bl.other_rejection_code is NOT NULL
GROUP BY bl.resource_assignment_id
,bl.txn_currency_code
,decode(p_source_context,'BUDGET_LINE',tmp.start_date,NULL)
,decode(p_source_context,'BUDGET_LINE',tmp.end_date,NULL) ;
g_etcrevrej_raId_tab.delete;
g_etcrevrej_txnCur_tab.delete;
g_etcrevrej_revenue_tab.delete;
g_etcrevrej_start_date_tab.delete;
g_etcrevrej_end_date_tab.delete;
l_etcrevrej_raId_tab.delete;
l_etcrevrej_txnCur_tab.delete;
l_etcrevrej_revenue_tab.delete;
g_etcrevrej_start_date_tab.delete;
g_etcrevrej_end_date_tab.delete;
UPDATE PA_BUDGET_LINES bl
SET bl.other_rejection_code = NULL
WHERE bl.resource_assignment_id = l_etcrevrej_raId_tab(i)
AND bl.txn_currency_code = l_etcrevrej_txnCur_tab(i)
AND nvl(g_etcrevrej_revenue_tab(i),0) <> nvl(l_etcrevrej_revenue_tab(i),0)
AND ((p_source_context = 'BUDGET_LINE'
and bl.start_date between g_etcrevrej_start_date_tab(i) and g_etcrevrej_end_date_tab(i))
OR
p_source_context <> 'BUDGET_LINE'
)
RETURN bl.budget_line_id
BULK COLLECT INTO l_budget_line_id_tab;
l_etcrevrej_raId_tab.delete;
l_etcrevrej_txnCur_tab.delete;
l_etcrevrej_revenue_tab.delete;
l_budget_line_id_tab.delete;
g_etcrevrej_raId_tab.delete;
g_etcrevrej_txnCur_tab.delete;
g_etcrevrej_revenue_tab.delete;
g_etcrevrej_start_date_tab.delete;
g_etcrevrej_end_date_tab.delete;
PROCEDURE delete_raTxn_Tmp IS
BEGIN
DELETE FROM pa_resource_asgn_curr_tmp;
END delete_raTxn_Tmp;
SELECT tmp.resource_assignment_id
,tmp.txn_currency_code
,tmp.start_date
,tmp.end_date
,tmp.quantity
,tmp.txn_raw_cost
,tmp.rw_cost_rate_override
,tmp.txn_burdened_cost
,tmp.burden_cost_rate_override
,tmp.txn_revenue
,tmp.bill_rate_override
,tmp.budget_line_id
FROM pa_fp_rollup_tmp tmp
ORDER BY tmp.resource_assignment_id
,tmp.txn_currency_code;
SELECT tmp.resource_assignment_id
,tmp.txn_currency_code
,tmp.start_date
,tmp.end_date
,tmp.quantity
,tmp.txn_raw_cost
,tmp.cost_rate_override
,tmp.txn_burdened_cost
,tmp.burden_cost_rate_override
,tmp.txn_revenue
,tmp.bill_rate_override
FROM pa_fp_spread_calc_tmp tmp
ORDER BY tmp.resource_assignment_id
,tmp.txn_currency_code;
* and updates the rate overrides
* Logic: When Refresh rates flag = 'Y'
* -- Delete all records from the new entity
* When source context = BUDGET LINE
* -- rollup the amounts in the new entity
* When source context = RESOURCE ASSIGNMENT
* -- create a new record or update existing records with rate overrides
* This API inserts records into temp table based on various combination of input parameters
* and finally make call to maintain_data package
*/
PROCEDURE populate_raTxn_Recs (
p_budget_version_id Number
,p_source_context Varchar2
,p_calling_mode Varchar2
,p_delete_flag Varchar2 := 'N'
,p_delete_raTxn_flag Varchar2 := 'N'
,p_rollup_flag Varchar2 := 'N'
,p_call_raTxn_rollup_flag Varchar2 := 'N'
,p_refresh_rate_flag Varchar2 := 'N'
,p_resource_assignment_id NUMBER := NULL
,p_txn_currency_code Varchar2 := NULL
,p_start_date DATE := NULL
,x_return_status OUT NOCOPY Varchar2
,x_msg_count OUT NOCOPY number
,x_msg_data OUT NOCOPY Varchar2
) IS
l_rowCount Number := 0;
SELECT COPY_ETC_FROM_PLAN_FLAG
FROM PA_PROJ_FP_OPTIONS
WHERE FIN_PLAN_VERSION_ID = P_BUDGET_VERSION_ID;
IF NVL(p_delete_flag,'N') = 'N' Then
IF p_source_context = 'RESOURCE_ASSIGNMENT' Then
If NVL(p_refresh_rate_flag,'N') = 'Y' Then --{
--print_msg('Inserting records into pa_resource_asgn_curr_tmp for Refresh rates action');
INSERT INTO pa_resource_asgn_curr_tmp raTxn
(RA_TXN_ID
,BUDGET_VERSION_ID
,RESOURCE_ASSIGNMENT_ID
,TXN_CURRENCY_CODE
,DELETE_FLAG
)
SELECT /*+ INDEX(RTX PA_RESOURCE_ASGN_CURR_U2) */ 1 ra_txn_id
,p_budget_version_id
,tmp.resource_assignment_id
,tmp.txn_currency_code
,'Y'
FROM pa_fp_spread_calc_tmp tmp
,pa_resource_asgn_curr rtx
WHERE tmp.budget_version_id = p_budget_version_id
AND tmp.resource_assignment_id = rtx.resource_assignment_id
AND tmp.txn_currency_code = rtx.txn_currency_code
/* bug fix: If no budge Lines exists then donot delete the resource when refresh is done */
AND EXISTS ( select /*+ INDEX(RTMP PA_FP_ROLLUP_TMP_N1) */ null
from pa_fp_rollup_tmp rtmp
where rtmp.resource_assignment_id = tmp.resource_assignment_id
and rtmp.txn_currency_code = tmp.txn_currency_code
);
UPDATE /*+ INDEX(RTX PA_RESOURCE_ASGN_CURR_U2) */ pa_resource_asgn_curr rtx
SET rtx.TXN_RAW_COST_RATE_OVERRIDE = null
,rtx.TXN_BURDEN_COST_RATE_OVERRIDE = null
,rtx.TXN_BILL_RATE_OVERRIDE = null
WHERE rtx.budget_version_id = p_budget_version_id
AND EXISTS ( select null
from pa_fp_spread_calc_tmp tmp
where tmp.resource_assignment_id = rtx.resource_assignment_id
and tmp.txn_currency_code = rtx.txn_currency_code
)
AND NOT EXISTS ( select null
from pa_fp_rollup_tmp rtmp
where rtmp.resource_assignment_id = rtx.resource_assignment_id
and rtmp.txn_currency_code = rtx.txn_currency_code
);
--print_msg('Number of records updated directly with null['||sql%rowcount||']');
,P_DELETE_FLAG => 'Y'
,P_ROLLUP_FLAG => 'N'
,P_VERSION_LEVEL_FLAG => 'N'
,X_RETURN_STATUS => x_return_status
,X_MSG_COUNT => x_msg_count
,X_MSG_DATA => x_msg_data
);
INSERT INTO pa_resource_asgn_curr_tmp
(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
,DELETE_FLAG
)
SELECT /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ pa_resource_asgn_curr_s.nextval
,p_budget_version_id
,tmp.resource_assignment_id
,NVL(tmp.txn_curr_code_override,tmp.txn_currency_code)
,decode(p_refresh_rate_flag, 'N', tmp.cost_rate_override, NULL)
,decode(p_refresh_rate_flag, 'N', tmp.burden_cost_rate_override, NULL)
,decode(p_refresh_rate_flag, 'N', tmp.bill_rate_override, NULL)
,p_delete_raTxn_flag
FROM pa_fp_spread_calc_tmp tmp
WHERE tmp.budget_version_id = p_budget_version_id
AND NVL(tmp.RA_RATES_ONLY_CHANGE_FLAG,'N') = 'Y'
OR (NVL(tmp.cost_rate_changed_flag,'N') = 'Y'
OR NVL(tmp.burden_rate_changed_flag,'N') = 'Y'
OR NVL(tmp.bill_rate_changed_flag,'N') = 'Y'
OR NVL(tmp.raw_cost_changed_flag,'N') = 'Y'
OR NVL(tmp.burden_cost_changed_flag,'N') = 'Y'
OR NVL(tmp.revenue_changed_flag,'N') = 'Y'
OR tmp.txn_curr_code_override is NOT NULL
);
/* The following update is required with current logic of the
* new maintain entity which always blows out the records and inserts new records */
--print_msg('Updating pa_resource_asgn_curr_tmp to set rate overrides derived during calculate');
UPDATE pa_resource_asgn_curr_tmp tmp
SET (tmp.TXN_RAW_COST_RATE_OVERRIDE
,tmp.TXN_BURDEN_COST_RATE_OVERRIDE
,tmp.TXN_BILL_RATE_OVERRIDE) =
(SELECT decode(tmp1.cost_rate_g_miss_num_flag,'Y',NULL
,NVL(tmp1.cost_rate_override,rtx.TXN_RAW_COST_RATE_OVERRIDE))
,decode(tmp1.burden_rate_g_miss_num_flag,'Y',NULL
,NVL(tmp1.burden_cost_rate_override,rtx.TXN_BURDEN_COST_RATE_OVERRIDE))
,decode(tmp1.bill_rate_g_miss_num_flag,'Y',NULL
,NVL(tmp1.bill_rate_override,rtx.TXN_BILL_RATE_OVERRIDE))
FROM pa_resource_asgn_curr rtx
,pa_fp_spread_calc_tmp tmp1
WHERE rtx.resource_assignment_id = tmp.resource_assignment_id
AND rtx.txn_currency_code = tmp.txn_currency_code
AND tmp1.resource_assignment_id = tmp.resource_assignment_id
AND NVL(tmp1.txn_curr_code_override,tmp1.txn_currency_code) = tmp.txn_currency_code
)
WHERE tmp.budget_version_id = p_budget_version_id
AND EXISTS (select null from pa_resource_asgn_curr rtx2
,pa_fp_spread_calc_tmp tmp2
where rtx2.resource_assignment_id = tmp.resource_assignment_id
and rtx2.txn_currency_code = tmp.txn_currency_code
AND tmp2.resource_assignment_id = tmp.resource_assignment_id
AND NVL(tmp2.txn_curr_code_override,tmp2.txn_currency_code) = tmp.txn_currency_code
);
--print_msg('Number of records Updated ['||sql%Rowcount||']');
/* If only rates are changed then update the changed override rates on rollup tmp and avoid calling spread
* and call rate api */
IF g_rtChanged_RaId_tab.COUNT > 0 Then
g_stage := 'update pa_resource_asgn_curr_tmp with OvrRts';
UPDATE pa_resource_asgn_curr_tmp tmp
SET tmp.TXN_RAW_COST_RATE_OVERRIDE = decode(NVL(g_rtChanged_cstMisNumFlg_tab(i),'N'),'Y',NULL
,NVL(g_rtChanged_CostRt_Tab(i),tmp.TXN_RAW_COST_RATE_OVERRIDE))
,tmp.TXN_BURDEN_COST_RATE_OVERRIDE = decode(NVL(g_rtChanged_bdMisNumFlag_tab(i),'N'),'Y',NULL
,NVL(g_rtChanged_BurdRt_tab(i),tmp.TXN_BURDEN_COST_RATE_OVERRIDE))
,tmp.TXN_BILL_RATE_OVERRIDE = decode(NVL(g_rtChanged_blMisNumFlag_tab(i),'N'),'Y',NULL
,NVL(g_rtChanged_billRt_tab(i),tmp.TXN_BILL_RATE_OVERRIDE))
WHERE tmp.budget_version_id = p_budget_version_id
AND tmp.resource_assignment_id = g_rtChanged_RaId_tab(i)
AND tmp.txn_currency_code = g_rtChanged_TxnCur_tab(i);
* so whenever override rates are derived and rollup flag is N, directly update the
* new entity with override rates
*/
IF G_call_raTxn_rollup_flag = 'N' Then
--print_msg('Updating new entity with override rates when rollup flag is N');
UPDATE /*+ INDEX(RTX PA_RESOURCE_ASGN_CURR_U2) */ pa_resource_asgn_curr rtx
SET (rtx.TXN_RAW_COST_RATE_OVERRIDE
,rtx.TXN_BURDEN_COST_RATE_OVERRIDE
,rtx.TXN_BILL_RATE_OVERRIDE) =
(SELECT tmp.TXN_RAW_COST_RATE_OVERRIDE
,tmp.TXN_BURDEN_COST_RATE_OVERRIDE
,tmp.TXN_BILL_RATE_OVERRIDE
FROM pa_resource_asgn_curr_tmp tmp
WHERE tmp.resource_assignment_id = rtx.resource_assignment_id
AND tmp.txn_currency_code = rtx.txn_currency_code
)
WHERE rtx.budget_version_id = p_budget_version_id
AND EXISTS ( select null
from pa_resource_asgn_curr_tmp tmp1
where tmp1.resource_assignment_id = rtx.resource_assignment_id
and tmp1.txn_currency_code = rtx.txn_currency_code
);
If NVL(p_calling_mode,'XXX') NOT IN ('CLEAR_CLOSED_PERIOD','RES_ATTRB_CHANGE','DELETE_BL') Then
--print_msg('Inserting records in pa_resource_asgn_curr_tmp from rollup Tmp for BUDGET_LINE context');
INSERT INTO pa_resource_asgn_curr_tmp raTxn
(RA_TXN_ID
,BUDGET_VERSION_ID
,RESOURCE_ASSIGNMENT_ID
,TXN_CURRENCY_CODE
)
SELECT 1 ra_txn_id
,p_budget_version_id
,tmp.resource_assignment_id
,tmp.txn_currency_code
FROM pa_fp_rollup_tmp tmp
WHERE tmp.budget_version_id = p_budget_version_id
GROUP BY p_budget_version_id
,tmp.resource_assignment_id
,tmp.txn_currency_code,1;
IF NVL(p_calling_mode,'XXX') IN ('CLEAR_CLOSED_PERIOD','RES_ATTRB_CHANGE','DELETE_BL') and l_rowCount = 0 Then
--print_msg('Inserting records in pa_resource_asgn_curr_tmp from sptmp for periodic deletion');
INSERT INTO pa_resource_asgn_curr_tmp raTxn
(RA_TXN_ID
,BUDGET_VERSION_ID
,RESOURCE_ASSIGNMENT_ID
,TXN_CURRENCY_CODE
)
SELECT 1 ra_txn_id
,p_budget_version_id
,tmp.resource_assignment_id
,tmp.txn_currency_code
FROM pa_fp_spread_calc_tmp tmp
WHERE tmp.budget_version_id = p_budget_version_id
AND tmp.resource_assignment_id = NVL(p_resource_assignment_id,tmp.resource_assignment_id)
AND tmp.txn_currency_code = NVL(p_txn_currency_code,tmp.txn_currency_code)
AND NVL(tmp.start_date,trunc(sysdate)) = NVL(p_start_date,nvl(tmp.start_date,trunc(sysdate)))
AND ( NVL(tmp.delete_bl_flag,'N') = 'Y'
OR NVL(tmp.sp_curve_change_flag,'N') = 'Y'
OR NVL(tmp.sp_fix_date_change_flag,'N') = 'Y'
OR NVL(tmp.rlm_id_change_flag,'N') = 'Y'
OR NVL(tmp.re_spread_amts_flag,'N') = 'Y'
OR p_calling_mode = 'CLEAR_CLOSED_PERIOD'
OR ((g_wp_version_flag = 'Y' AND NVL(tmp.plan_dates_change_flag,'N') = 'Y')
OR (NVL(tmp.ra_in_multi_cur_flag,'N') = 'N' AND NVL(tmp.plan_dates_change_flag,'N') = 'Y'))
);
/* The following update is required with current logic of the
* new maintain entity which always blows out the records and inserts new records */
UPDATE pa_resource_asgn_curr_tmp tmp
SET (tmp.TXN_RAW_COST_RATE_OVERRIDE
,tmp.TXN_BURDEN_COST_RATE_OVERRIDE
,tmp.TXN_BILL_RATE_OVERRIDE) =
(SELECT rtx.TXN_RAW_COST_RATE_OVERRIDE
,rtx.TXN_BURDEN_COST_RATE_OVERRIDE
,rtx.TXN_BILL_RATE_OVERRIDE
FROM pa_resource_asgn_curr rtx
WHERE rtx.resource_assignment_id = tmp.resource_assignment_id
AND rtx.txn_currency_code = tmp.txn_currency_code
)
WHERE tmp.budget_version_id = p_budget_version_id
AND EXISTS (select null from pa_resource_asgn_curr rtx2
where rtx2.resource_assignment_id = tmp.resource_assignment_id
and rtx2.txn_currency_code = tmp.txn_currency_code
);
IF gl_cl_roll_up_tmp_rowid_tab.COUNT>0 AND g_time_phased_code='N' AND p_calling_mode = 'UPDATE_PLAN_TRANSACTION' THEN
FOR zz IN 1..gl_cl_roll_up_tmp_rowid_tab.COUNT LOOP
SELECT rw_cost_rate_override,
burden_cost_rate_override,
bill_rate_override,
resource_assignment_id,
txn_currency_code
INTO l_rw_cost_rate_override,
l_burden_cost_rate_override,
l_bill_rate_override,
l_ra_id,
l_txn_currency_code
FROM pa_fp_rollup_tmp
WHERE rowid=gl_cl_roll_up_tmp_rowid_tab(zz);
UPDATE pa_resource_asgn_curr_tmp
SET txn_raw_cost_rate_override = NVL(l_rw_cost_rate_override,txn_raw_cost_rate_override),
txn_burden_cost_rate_override = NVL(l_burden_cost_rate_override,txn_burden_cost_rate_override),
txn_bill_rate_override = NVL(l_bill_rate_override,txn_bill_rate_override)
WHERE resource_assignment_id = l_ra_id
AND txn_currency_code = l_txn_currency_code;
gl_cl_roll_up_tmp_rowid_tab.delete;
ElsIf p_delete_flag = 'Y' Then
/* when rollup is not required just delete from tmp table */
DELETE FROM pa_resource_asgn_curr_tmp;
for c in (select tmp.TXN_RAW_COST_RATE_OVERRIDE costRt
,tmp.TXN_BURDEN_COST_RATE_OVERRIDE BurdRt
,tmp.TXN_BILL_RATE_OVERRIDE BillRt
,tmp1.cost_rate_override tmpCostRt
,tmp1.burden_cost_rate_override tmpBurdRt
,tmp1.bill_rate_override tmpBillRt
from pa_resource_asgn_curr_tmp tmp
,pa_fp_spread_calc_tmp tmp1) LOOP
print_msg('Rates in Tmp Table CostRt['|| c.costRt||']BurdRt['||c.burdRt||']BillRt['||c.billRt||']');
/* When override currency is passed, then delete the records from new entity for the old currency */
If p_source_context = 'RESOURCE_ASSIGNMENT' and NVL(p_refresh_rate_flag,'N') = 'N' Then
--print_msg('Inserting records in pa_resource_asgn_curr_tmp from rollup for Override currency code');
INSERT INTO pa_resource_asgn_curr_tmp raTxn
(RA_TXN_ID
,BUDGET_VERSION_ID
,RESOURCE_ASSIGNMENT_ID
,TXN_CURRENCY_CODE
,DELETE_FLAG
)
SELECT 1 ra_txn_id
,p_budget_version_id
,tmp.resource_assignment_id
,tmp.txn_currency_code
,'Y'
FROM pa_fp_spread_calc_tmp tmp
WHERE tmp.budget_version_id = p_budget_version_id
AND tmp.txn_curr_code_override is NOT NULL
AND tmp.txn_curr_code_override <> tmp.txn_currency_code;
UPDATE pa_resource_asgn_curr rtx
SET rtx.TXN_RAW_COST_RATE_OVERRIDE = NULL
,rtx.TXN_BURDEN_COST_RATE_OVERRIDE = NULL
WHERE rtx.budget_version_id = p_budget_version_id;
,P_DELETE_FLAG => 'Y'
,P_ROLLUP_FLAG => 'N'
,P_VERSION_LEVEL_FLAG => 'N'
,X_RETURN_STATUS => x_return_status
,X_MSG_COUNT => x_msg_count
,X_MSG_DATA => x_msg_data
);
,P_DELETE_FLAG => 'N'
,P_ROLLUP_FLAG => 'Y' --p_rollup_flag
,P_VERSION_LEVEL_FLAG => 'N'
,X_RETURN_STATUS => x_return_status
,X_MSG_COUNT => x_msg_count
,X_MSG_DATA => x_msg_data
);
UPDATE pa_fp_rollup_tmp tmp
SET (tmp.rw_cost_rate_override
,tmp.burden_cost_rate_override
,tmp.bill_rate_override ) =
(SELECT /*+ INDEX(RAX PA_RESOURCE_ASGN_CURR_U2) */
decode(nvl(tmp1.cost_rate_g_miss_num_flag,'N'),'Y',NULL,nvl(tmp.rw_cost_rate_override,rax.txn_raw_cost_rate_override))
,decode(nvl(tmp1.burden_rate_g_miss_num_flag,'N'),'Y',NULL
,decode(nvl(tmp1.rate_based_flag,'N'),'Y',nvl(tmp.burden_cost_rate_override,rax.txn_burden_cost_rate_override)
,decode(g_fp_budget_version_type,'REVENUE',nvl(tmp.burden_cost_rate_override,rax.txn_burden_cost_rate_override)
,'COST',nvl(tmp.burden_cost_rate_override,rax.txn_burden_cost_rate_override)
,'ALL'
,decode(tmp1.burden_cost_changed_flag,'Y'
,decode(tmp1.burden_cost_rate_override,0,0
,decode(nvl(tmp.burden_cost_rate_override,rax.txn_burden_cost_rate_override),0,NULL
,nvl(tmp.burden_cost_rate_override,rax.txn_burden_cost_rate_override)))
,decode(tmp1.raw_cost_changed_flag,'Y'
,decode(nvl(tmp.burden_cost_rate_override,rax.txn_burden_cost_rate_override),0,NULL
,nvl(tmp.burden_cost_rate_override,rax.txn_burden_cost_rate_override))
,nvl(tmp.burden_cost_rate_override,rax.txn_burden_cost_rate_override))))))
,decode(nvl(tmp1.bill_rate_g_miss_num_flag,'N'),'Y',NULL
,decode(nvl(tmp1.rate_based_flag,'N'),'Y',nvl(tmp.bill_rate_override,rax.txn_bill_rate_override)
,decode(tmp1.revenue_changed_flag,'Y',nvl(tmp.bill_rate_override,rax.txn_bill_rate_override)
,decode(g_fp_budget_version_type,'COST',tmp.bill_rate_override
,'REVENUE',nvl(tmp.bill_rate_override,rax.txn_bill_rate_override)
,'ALL',decode(tmp1.raw_cost_changed_flag,'Y'
,decode(nvl(tmp.bill_rate_override,rax.txn_bill_rate_override),1,NULL
,0,NULL,nvl(tmp.bill_rate_override,rax.txn_bill_rate_override))
,decode(tmp1.burden_cost_changed_flag,'Y'
,decode(tmp1.txn_raw_cost,tmp1.txn_burdened_cost
,decode(nvl(tmp.bill_rate_override,rax.txn_bill_rate_override),1,NULL
,0,NULL,nvl(tmp.bill_rate_override,rax.txn_bill_rate_override))
,nvl(tmp.bill_rate_override,rax.txn_bill_rate_override))
,nvl(tmp.bill_rate_override,rax.txn_bill_rate_override)))))))
FROM pa_resource_asgn_curr rax
,pa_fp_spread_calc_tmp tmp1
WHERE rax.resource_assignment_id = tmp.resource_assignment_id
AND rax.txn_currency_code = tmp.txn_currency_code
AND tmp1.resource_assignment_id = rax.resource_assignment_id
AND tmp1.txn_currency_code = rax.txn_currency_code
AND (g_source_context = 'RESOURCE_ASSIGNMENT'
OR
(g_source_context = 'BUDGET_LINE'
and tmp.start_date between tmp1.start_date and tmp1.end_date)
)
)
WHERE tmp.budget_version_id = p_budget_version_id
AND EXISTS (select /*+ INDEX(RAX1 PA_RESOURCE_ASGN_CURR_U2) */ null
from pa_resource_asgn_curr rax1
,pa_fp_spread_calc_tmp tmp2
where rax1.resource_assignment_id = tmp.resource_assignment_id
and rax1.txn_currency_code = tmp.txn_currency_code
and tmp2.resource_assignment_id = rax1.resource_assignment_id
and tmp2.txn_currency_code = rax1.txn_currency_code
and (g_source_context = 'RESOURCE_ASSIGNMENT'
OR
(g_source_context = 'BUDGET_LINE'
and tmp.start_date between tmp2.start_date and tmp2.end_date)
)
);
print_msg('Number of rows updated in the rollup tmp with raTxnOvr ['||sql%rowCount||']');
SELECT 'Y'
INTO l_exists_flag
FROM DUAL
WHERE EXISTS (select null
from pa_fp_spread_calc_tmp1 tmp1
where tmp1.budget_version_id = p_budget_version_id
);
SELECT nvl(multi_currency_flag,'N')
INTO G_MRC_INSTALLED_FLAG
FROM fnd_product_groups
WHERE product_group_id=1;
SELECT v.project_id
,v.fin_plan_type_id
,o.projfunc_cost_rate_type
,o.projfunc_cost_rate_date_type
,o.projfunc_cost_rate_date
,o.projfunc_rev_rate_type
,o.projfunc_rev_rate_date_type
,o.projfunc_rev_rate_date
,o.project_cost_rate_type
,o.project_cost_rate_date_type
,o.project_cost_rate_date
,o.project_rev_rate_type
,o.project_rev_rate_date_type
,o.project_rev_rate_date
FROM pa_proj_fp_options o
,pa_budget_versions v
WHERE v.budget_version_id = p_budget_version_id
AND o.project_id = v.project_id
AND nvl(o.fin_plan_type_id,0) = nvl(v.fin_plan_type_id,0)
AND o.fin_plan_version_id = v.budget_version_id;
SELECT segment1
,project_currency_code
,projfunc_currency_code
FROM pa_projects_all
WHERE project_id = p_project_id;
--G_FP_MRC_TAB.DELETE;
g_mrc_budget_line_id_tab.delete;
g_mrc_res_assignment_id_tab.delete;
g_mrc_txn_curr_code_tab.delete;
g_mrc_start_date_tab.delete;
g_mrc_end_date_tab.delete;
g_mrc_period_name_tab.delete;
g_mrc_quantity_tab.delete;
g_mrc_txn_raw_cost_tab.delete;
g_mrc_txn_burden_cost_tab.delete;
g_mrc_txn_revenue_tab.delete;
g_mrc_project_curr_code_tab.delete;
g_mrc_project_raw_cost_tab.delete;
g_mrc_project_burden_cost_tab.delete;
g_mrc_project_revenue_tab.delete;
g_mrc_projfunc_curr_code_tab.delete;
g_mrc_projfunc_raw_cost_tab.delete;
g_mrc_projfunc_burden_cost_tab.delete;
g_mrc_projfunc_revenue_tab.delete;
g_mrc_delete_flag_tab.delete;
g_mrc_Billable_flag_tab.delete;
g_mrc_project_cst_rt_type_tab.delete;
g_mrc_project_cst_exg_rt_tab.delete;
g_mrc_project_cst_dt_type_tab.delete;
g_mrc_project_cst_rt_dt_tab.delete;
g_mrc_project_rev_rt_type_tab.delete;
g_mrc_project_rev_exg_rt_tab.delete;
g_mrc_project_rev_dt_type_tab.delete;
g_mrc_project_rev_rt_dt_tab.delete;
g_mrc_projfunc_cst_rt_type_tab.delete;
g_mrc_projfunc_cst_exg_rt_tab.delete;
g_mrc_projfunc_cst_dt_type_tab.delete;
g_mrc_projfunc_cst_rt_dt_tab.delete;
g_mrc_projfunc_rev_rt_type_tab.delete;
g_mrc_projfunc_rev_exg_rt_tab.delete;
g_mrc_projfunc_rev_dt_type_tab.delete;
g_mrc_projfunc_rev_rt_dt_tab.delete;
is to be deleted or not. Frm AMG flow we will pass N and for
other calls to calculate api it would be yes*/
PROCEDURE Init_SpreadCalc_Tbls(p_del_spread_calc_tmp1_flg IN VARCHAR2 := 'Y')
IS
l_return_status Varchar2(10);
DELETE FROM pa_fp_res_assignments_tmp;
DELETE FROM pa_fp_rollup_tmp;
DELETE FROM pa_fp_spread_calc_tmp;
DELETE FROM pa_resource_asgn_curr_tmp;
DELETE FROM pa_fp_spread_calc_tmp1;
/* Bug fix:4272944: The following new api call is added to insert zero qty budget lines for Funding baseline */
IF (NVL(G_baseline_funding_flag,'N') = 'Y'
AND NVL(g_bv_approved_rev_flag,'N') = 'Y') THEN
If P_PA_DEBUG_MODE = 'Y' Then
print_msg('Calling PA_FP_CALC_UTILS.InsertFunding_ReqdLines API');
PA_FP_CALC_UTILS.InsertFunding_ReqdLines
( p_budget_verson_id => g_budget_version_id
,p_source_context => g_source_context
,p_calling_module => g_calling_module
,p_apply_progress_flag => 'N'
,p_approved_rev_flag => g_bv_approved_rev_flag
,p_autoBaseLine_flag => G_baseline_funding_flag
,x_return_status => l_return_status
);
DELETE FROM pa_fp_spread_calc_tmp1;
DELETE FROM pa_fp_spread_calc_tmp2;
g_sprd_raId_tab.delete;
g_sprd_txn_cur_tab.delete;
g_sprd_sdate_tab.delete;
g_sprd_edate_tab.delete;
g_sprd_plan_sdate_tab.delete;
g_sprd_plan_edate_tab.delete;
g_sprd_txn_rev_tab.delete;
g_sprd_txn_rev_addl_tab.delete;
g_sprd_txn_raw_tab.delete;
g_sprd_txn_raw_addl_tab.delete;
g_sprd_txn_burd_tab.delete;
g_sprd_txn_burd_addl_tab.delete;
g_sprd_qty_tab.delete;
g_sprd_qty_addl_tab.delete;
g_sprd_txn_cur_ovr_tab.delete;
g_sprd_txn_init_rev_tab.delete;
g_sprd_txn_init_raw_tab.delete;
g_sprd_txn_init_burd_tab.delete;
g_sprd_txn_init_qty_tab.delete;
g_sprd_spread_reqd_flag_tab.delete;
g_sprd_costRt_tab.delete;
g_sprd_costRt_Ovr_tab.delete;
g_sprd_burdRt_Tab.delete;
g_sprd_burdRt_Ovr_tab.delete;
g_sprd_billRt_tab.delete;
g_sprd_billRt_Ovr_tab.delete;
g_sprd_ratebase_flag_tab.delete;
g_sprd_projCur_tab.delete;
g_sprd_projfuncCur_tab.delete;
g_sprd_task_id_tab.delete;
g_sprd_rlm_id_tab.delete;
g_sprd_sp_fixed_date_tab.delete;
g_sprd_spcurve_id_tab.delete;
g_sprd_cstRtmissFlag_tab.delete;
g_sprd_bdRtmissFlag_tab.delete;
g_sprd_bilRtmissFlag_tab.delete;
g_sprd_QtymissFlag_tab.delete;
g_sprd_RawmissFlag_tab.delete;
g_sprd_BurdmissFlag_tab.delete;
g_sprd_RevmissFlag_tab.delete;
g_sprd_neg_Qty_Changflag_tab.delete;
g_sprd_neg_Raw_Changflag_tab.delete;
g_sprd_neg_Burd_Changflag_tab.delete;
g_sprd_neg_rev_Changflag_tab.delete;
g_plan_raId_tab.delete;
g_plan_txnCur_Tab.delete;
g_line_sdate_tab.delete;
g_line_edate_tab.delete;
g_Wp_curCode_tab.delete;
g_refresh_rates_tab.delete;
g_refresh_conv_rates_tab.delete;
g_mass_adjust_flag_tab.delete;
g_mfc_cost_refresh_tab.delete;
g_skip_record_tab.delete;
g_process_skip_CstRevrec_tab.delete;
g_mfc_cost_refrsh_Raid_tab.delete;
g_mfc_cost_refrsh_txnCur_tab.delete;
g_rtChanged_Ra_Flag_tab.delete;
g_rtChanged_RaId_tab.delete;
g_rtChanged_TxnCur_tab.delete;
g_rtChanged_sDate_tab.delete;
g_rtChanged_eDate_tab.delete;
g_rtChanged_CostRt_Tab.delete;
g_rtChanged_BurdRt_tab.delete;
g_rtChanged_billRt_tab.delete;
g_rtChanged_cstMisNumFlg_tab.delete;
g_rtChanged_bdMisNumFlag_tab.delete;
g_rtChanged_blMisNumFlag_tab.delete;
g_rtChanged_QtyMisNumFlg_tab.delete;
g_rtChanged_RwMisNumFlag_tab.delete;
g_rtChanged_BrMisNumFlag_tab.delete;
g_rtChanged_RvMisNumFlag_tab.delete;
g_applyProg_refreshRts_tab.delete;
g_applyProg_RaId_tab.delete;
g_applyProg_TxnCur_tab.delete;
g_rep_budget_line_id_tab.delete;
g_rep_res_assignment_id_tab .delete;
g_rep_start_date_tab.delete;
g_rep_end_date_tab.delete;
g_rep_period_name_tab.delete;
g_rep_txn_curr_code_tab.delete;
g_rep_quantity_tab.delete;
g_rep_txn_raw_cost_tab.delete;
g_rep_txn_burdened_cost_tab.delete;
g_rep_txn_revenue_tab.delete;
g_rep_project_curr_code_tab.delete;
g_rep_project_raw_cost_tab.delete;
g_rep_project_burden_cost_tab.delete;
g_rep_project_revenue_tab.delete;
g_rep_projfunc_curr_code_tab.delete;
g_rep_projfunc_raw_cost_tab.delete;
g_rep_projfunc_burden_cost_tab.delete;
g_rep_projfunc_revenue_tab.delete;
g_rep_line_mode_tab.delete;
g_rep_rate_base_flag_tab.delete;
SELECT 'E'
FROM DUAL
WHERE EXISTS (select null
from pa_fp_rollup_tmp tmp
where ( tmp.cost_rejection_code in ('PA_FP_PROJ_NO_TXNCONVRATE'
,'PA_FP_PRJFUNC_CURR_NULL'
,'PA_FP_PRJ_CURR_NULL'
,'PA_FP_ERROR_FROM_RATE_API_CALL' )
OR tmp.revenue_rejection_code in ('PA_FP_PROJ_NO_TXNCONVRATE'
,'PA_FP_PRJFUNC_CURR_NULL'
,'PA_FP_PRJ_CURR_NULL'
,'PA_FP_ERROR_FROM_RATE_API_CALL')
));
,p_delete_flag IN Varchar2 := 'N'
,p_billable_flag IN Varchar2 := 'Y'
,p_project_cost_rate_type IN Varchar2 default NULL
,p_project_cost_exchange_rate IN Number default NULL
,p_project_cost_rate_date_type IN Varchar2 default NULL
,p_project_cost_rate_date IN Date default NULL
,p_project_rev_rate_type IN Varchar2 default NULL
,p_project_rev_exchange_rate IN Number default NULL
,p_project_rev_rate_date_type IN Varchar2 default NULL
,p_project_rev_rate_date IN Date default NULL
,p_projfunc_cost_rate_type IN Varchar2 default NULL
,p_projfunc_cost_exchange_rate IN Number default NULL
,p_projfunc_cost_rate_date_type IN Varchar2 default NULL
,p_projfunc_cost_rate_date IN Date default NULL
,p_projfunc_rev_rate_type IN Varchar2 default NULL
,p_projfunc_rev_exchange_rate IN Number default NULL
,p_projfunc_rev_rate_date_type IN Varchar2 default NULL
,p_projfunc_rev_rate_date IN Date default NULL
,x_msg_data OUT NOCOPY Varchar2
,x_return_status OUT NOCOPY Varchar2
) IS
l_msg_count Number :=0;
g_mrc_delete_flag_tab(NVL( g_mrc_delete_flag_tab.LAST,0)+1) := p_delete_flag;
/* This API inserts mrc plsql table of records into pa_fp_rollup_tmp when mrc conv is required */
PROCEDURE Populate_rollup_WithMrcRecs
(p_budget_version_id IN Number
,x_msg_data OUT NOCOPY Varchar2
,x_return_status OUT NOCOPY Varchar2
) IS
BEGIN
x_return_status := 'S';
--print_msg('Number of records inserting into rollupTmp for MRC processing['||g_mrc_budget_line_id_tab.COUNT||']');
INSERT INTO PA_FP_ROLLUP_TMP tmp
(budget_version_id
,budget_line_id
,resource_assignment_id
,txn_currency_code
,start_date
,end_date
,period_name
,quantity
,txn_raw_cost
,txn_burdened_cost
,txn_revenue
,project_currency_code
,project_raw_cost
,project_burdened_cost
,project_revenue
,project_cost_rate_type
,project_cost_exchange_rate
,project_cost_rate_date_type
,project_cost_rate_date
,project_rev_rate_type
,project_rev_exchange_rate
,project_rev_rate_date_type
,project_rev_rate_date
,projfunc_currency_code
,projfunc_raw_cost
,projfunc_burdened_cost
,projfunc_revenue
,projfunc_cost_rate_type
,projfunc_cost_exchange_rate
,projfunc_cost_rate_date_type
,projfunc_cost_rate_date
,projfunc_rev_rate_type
,projfunc_rev_exchange_rate
,projfunc_rev_rate_date_type
,projfunc_rev_rate_date
,delete_flag
)
SELECT p_budget_version_id
,g_mrc_budget_line_id_tab(i)
,g_mrc_res_assignment_id_tab(i)
,g_mrc_txn_curr_code_tab(i)
,g_mrc_start_date_tab(i)
,g_mrc_end_date_tab(i)
,g_mrc_period_name_tab(i)
,g_mrc_quantity_tab(i)
,g_mrc_txn_raw_cost_tab(i)
,g_mrc_txn_burden_cost_tab(i)
,g_mrc_txn_revenue_tab(i)
,g_mrc_project_curr_code_tab(i)
,g_mrc_project_raw_cost_tab(i)
,g_mrc_project_burden_cost_tab(i)
,g_mrc_project_revenue_tab(i)
,g_mrc_project_cst_rt_type_tab(i)
,g_mrc_project_cst_exg_rt_tab(i)
,g_mrc_project_cst_dt_type_tab(i)
,g_mrc_project_cst_rt_dt_tab(i)
,g_mrc_project_rev_rt_type_tab(i)
,g_mrc_project_rev_exg_rt_tab(i)
,g_mrc_project_rev_dt_type_tab(i)
,g_mrc_project_rev_rt_dt_tab(i)
,g_mrc_projfunc_curr_code_tab(i)
,g_mrc_projfunc_raw_cost_tab(i)
,g_mrc_projfunc_burden_cost_tab(i)
,g_mrc_projfunc_revenue_tab(i)
,g_mrc_projfunc_cst_rt_type_tab(i)
,g_mrc_projfunc_cst_exg_rt_tab(i)
,g_mrc_projfunc_cst_dt_type_tab(i)
,g_mrc_projfunc_cst_rt_dt_tab(i)
,g_mrc_projfunc_rev_rt_type_tab(i)
,g_mrc_projfunc_rev_exg_rt_tab(i)
,g_mrc_projfunc_rev_dt_type_tab(i)
,g_mrc_projfunc_rev_rt_dt_tab(i)
,g_mrc_delete_flag_tab(i)
FROM DUAL
WHERE NOT EXISTS (SELECT NULL
FROM PA_FP_ROLLUP_TMP RLTMP1
WHERE rltmp1.budget_line_id = g_mrc_budget_line_id_tab(i)
);
Delete pa_fp_spread_calc_tmp1;
INSERT INTO pa_fp_spread_calc_tmp1
(budget_line_id
,resource_assignment_id
,start_date
,end_date
,period_name
,txn_currency_code
,quantity
,txn_raw_cost
,txn_burdened_cost
,txn_revenue
,project_currency_code
,project_raw_cost
,project_burdened_cost
,project_revenue
,projfunc_currency_code
,projfunc_raw_cost
,projfunc_burdened_cost
,projfunc_revenue
,system_reference_var1
,budget_version_id
) VALUES
(g_rep_budget_line_id_tab(i)
,g_rep_res_assignment_id_tab(i)
,g_rep_start_date_tab(i)
,g_rep_end_date_tab(i)
,g_rep_period_name_tab(i)
,g_rep_txn_curr_code_tab(i)
,g_rep_quantity_tab(i)
,g_rep_txn_raw_cost_tab(i)
,g_rep_txn_burdened_cost_tab(i)
,g_rep_txn_revenue_tab(i)
,g_rep_project_curr_code_tab(i)
,g_rep_project_raw_cost_tab(i)
,g_rep_project_burden_cost_tab(i)
,g_rep_project_revenue_tab(i)
,g_rep_projfunc_curr_code_tab(i)
,g_rep_projfunc_raw_cost_tab(i)
,g_rep_projfunc_burden_cost_tab(i)
,g_rep_projfunc_revenue_tab(i)
,g_rep_line_mode_tab(i)
,p_budget_version_id
);
UPDATE pa_fp_spread_calc_tmp1 tmp1
SET tmp1.rate_based_flag =
(SELECT decode(nvl(tmp.system_reference_var3,'N'),'Y'
,decode(nvl(tmp1.system_reference_var1,'XXX'),'REVERSAL','N',NULL),NULL)
FROM pa_fp_spread_calc_tmp tmp
WHERE tmp.resource_assignment_id = tmp1.resource_assignment_id
AND ROWNUM = 1
)
WHERE tmp1.budget_version_id = p_budget_version_id
AND EXISTS (select null
FROM pa_fp_spread_calc_tmp otmp
WHERE otmp.resource_assignment_id = tmp1.resource_assignment_id
);
print_msg('Number of rows updated for ratebasedFlag for Pjirollup['||sql%rowcount||']');
SELECT
tmp1.budget_line_id
,tmp1.resource_assignment_id
,tmp1.start_date
,tmp1.end_date
,tmp1.period_name
,tmp1.txn_currency_code
,tmp1.quantity
,tmp1.txn_raw_cost
,tmp1.txn_burdened_cost
,tmp1.txn_revenue
,tmp1.project_currency_code
,tmp1.project_raw_cost
,tmp1.project_burdened_cost
,tmp1.project_revenue
,tmp1.projfunc_currency_code
,tmp1.projfunc_raw_cost
,tmp1.projfunc_burdened_cost
,tmp1.projfunc_revenue
,tmp1.system_reference_var1
,tmp1.rate_based_flag
BULK COLLECT INTO
g_rep_budget_line_id_tab
,g_rep_res_assignment_id_tab
,g_rep_start_date_tab
,g_rep_end_date_tab
,g_rep_period_name_tab
,g_rep_txn_curr_code_tab
,g_rep_quantity_tab
,g_rep_txn_raw_cost_tab
,g_rep_txn_burdened_cost_tab
,g_rep_txn_revenue_tab
,g_rep_project_curr_code_tab
,g_rep_project_raw_cost_tab
,g_rep_project_burden_cost_tab
,g_rep_project_revenue_tab
,g_rep_projfunc_curr_code_tab
,g_rep_projfunc_raw_cost_tab
,g_rep_projfunc_burden_cost_tab
,g_rep_projfunc_revenue_tab
,g_rep_line_mode_tab
,g_rep_rate_base_flag_tab
FROM pa_fp_spread_calc_tmp1 tmp1
ORDER BY tmp1.resource_assignment_id
,tmp1.txn_currency_code;
,p_activity_code IN Varchar2 Default 'UPDATE'
,p_budget_version_id IN Number
,p_budget_line_id IN Number
,p_resource_assignment_id IN Number
,p_start_date IN Date
,p_end_date IN Date
,p_period_name IN Varchar2
,p_txn_currency_code IN Varchar2
,p_quantity IN Number
,p_txn_raw_cost IN Number
,p_txn_burdened_cost IN Number
,p_txn_revenue IN Number
,p_project_currency_code IN Varchar2
,p_project_raw_cost IN Number
,p_project_burdened_cost IN Number
,p_project_revenue IN Number
,p_projfunc_currency_code IN Varchar2
,p_projfunc_raw_cost IN Number
,p_projfunc_burdened_cost IN Number
,p_projfunc_revenue IN Number
,p_rep_line_mode IN Varchar2
,x_msg_data OUT NOCOPY Varchar2
,x_return_status OUT NOCOPY Varchar2
) IS
l_msg_count Number :=0;
print_msg('Failed in PA_FP_PJI_INTG_PKG.update_reporting_lines API ['||sqlcode||sqlerrm);
PROCEDURE Update_PCPFC_rounding_diff(
p_project_id IN pa_budget_versions.project_id%type
,p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
,p_calling_module IN VARCHAR2 DEFAULT NULL
,p_source_context IN pa_fp_res_assignments_tmp.source_context%TYPE
,p_wp_cost_enabled_flag IN varchar2
,p_budget_version_type IN varchar2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2) IS
CURSOR cur_round_discrepancy_lines IS
SELECT resource_assignment_id
,txn_currency_code
,MAX(start_date)
,SUM(quantity)
,SUM(init_quantity)
,SUM(txn_raw_cost)
,SUM(txn_init_raw_cost)
,SUM(txn_burdened_cost)
,SUM(txn_init_burdened_cost)
,SUM(txn_revenue)
,SUM(txn_init_revenue)
,SUM(txn_raw_cost * project_cost_exchange_rate) unround_project_raw_cost
,SUM(txn_burdened_cost * project_cost_exchange_rate) unround_project_burden_cost
,SUM(txn_revenue * project_rev_exchange_rate) unround_project_revenue
,SUM(pa_currency.round_trans_currency_amt1((txn_raw_cost * project_cost_exchange_rate),project_currency_code)) round_project_raw_cost
,SUM(pa_currency.round_trans_currency_amt1((txn_burdened_cost * project_cost_exchange_rate),project_currency_code)) round_project_burden_cost
,SUM(pa_currency.round_trans_currency_amt1((txn_revenue * project_rev_exchange_rate),project_currency_code)) round_project_revenue
,SUM(txn_raw_cost * projfunc_cost_exchange_rate) unround_projfunc_raw_cost
,SUM(txn_burdened_cost * projfunc_cost_exchange_rate) unround_projfunc_burden_cost
,SUM(txn_revenue * projfunc_rev_exchange_rate) unround_projfunc_revenue
,SUM(pa_currency.round_trans_currency_amt1((txn_raw_cost * projfunc_cost_exchange_rate),projfunc_currency_code)) round_projfunc_raw_cost
,SUM(pa_currency.round_trans_currency_amt1((txn_burdened_cost * projfunc_cost_exchange_rate),projfunc_currency_code)) round_projfunc_burden_cost
,SUM(pa_currency.round_trans_currency_amt1((txn_revenue * projfunc_rev_exchange_rate),projfunc_currency_code)) round_projfunc_revenue
,to_number(NULL) diff_proj_raw_cost
,to_number(NULL) diff_proj_burden_cost
,to_number(NULL) diff_proj_revenue
,to_number(NULL) diff_projfunc_raw_cost
,to_number(NULL) diff_projfunc_burden_cost
,to_number(NULL) diff_projfunc_revenue
,project_currency_code
,projfunc_currency_code
FROM pa_fp_rollup_tmp tmp
WHERE tmp.budget_version_id = p_budget_version_id
AND nvl(quantity,0) <> 0
AND ( tmp.txn_currency_code <> tmp.project_currency_code
OR
tmp.txn_currency_code <> tmp.projfunc_currency_code
)
GROUP BY resource_assignment_id
,txn_currency_code
,project_currency_code
,projfunc_currency_code;
pa_debug.init_err_stack('PA_FP_CALC_PLAN_PKG.Update_PCPFCrounding_diff');
g_stage := 'Update_PCPFC_rounding_diff:100';
pa_debug.g_err_stage := 'Entered Update_PCPFCrounding_diff API';
l_resource_assignment_tab.delete;
l_txn_currency_code_tab.delete;
l_start_date_tab.delete;
l_quantity_tab.delete;
l_init_quantity_tab.delete;
l_txn_raw_cost_tab.delete;
l_txn_init_raw_cost_tab.delete;
l_txn_burdened_cost_tab.delete;
l_txn_init_burdened_cost_tab.delete;
l_txn_revenue_tab.delete;
l_txn_init_revenue_tab.delete;
l_unround_proj_raw_cost_tab.delete;
l_unround_proj_burd_cost_tab.delete;
l_unround_proj_rev_tab.delete;
l_round_proj_raw_cost_tab.delete;
l_round_proj_burd_cost_tab.delete;
l_round_proj_rev_tab.delete;
l_unround_pjfc_raw_cost_tab.delete;
l_unround_pjfc_burd_cost_tab.delete;
l_unround_pjfc_rev_tab.delete;
l_round_pjfc_raw_cost_tab.delete;
l_round_pjfc_burd_cost_tab.delete;
l_round_pjfc_rev_tab.delete;
l_diff_proj_raw_cost_tab.delete;
l_diff_proj_burd_cost_tab.delete;
l_diff_proj_rev_tab.delete;
l_diff_pjfc_raw_cost_tab.delete;
l_diff_pjfc_burd_cost_tab.delete;
l_diff_pjfc_rev_tab.delete;
l_proj_currency_code_tab.delete;
l_pjfc_currency_code_tab.delete;
g_stage := 'Update_PCPFC_rounding_diff:101';
g_stage := 'Update_PCPFC_rounding_diff:102';
UPDATE pa_fp_rollup_tmp tmp
SET tmp.attribute14 = decode(tmp.txn_currency_code,tmp.project_currency_code,NULL,'PCPFCRoundingDiscrepancyLine')
/*---Project columns ---*/
,tmp.project_raw_cost = decode(p_budget_version_type,'COST',
decode(tmp.txn_currency_code,tmp.project_currency_code,tmp.project_raw_cost
,decode((nvl(tmp.project_raw_cost,0)+ NVL(l_diff_proj_raw_cost_tab(i),0)),0,NULL,
(nvl(tmp.project_raw_cost,0)+ NVL(l_diff_proj_raw_cost_tab(i),0))))
,'ALL',
decode(tmp.txn_currency_code,tmp.project_currency_code,tmp.project_raw_cost
,decode((nvl(tmp.project_raw_cost,0)+ NVL(l_diff_proj_raw_cost_tab(i),0)),0,NULL,
(nvl(tmp.project_raw_cost,0)+ NVL(l_diff_proj_raw_cost_tab(i),0))))
,'REVENUE',tmp.project_raw_cost)
,tmp.project_burdened_cost =decode(p_budget_version_type,'COST',
decode(tmp.txn_currency_code,tmp.project_currency_code,tmp.project_burdened_cost
,decode((nvl(tmp.project_burdened_cost,0)+ NVL(l_diff_proj_burd_cost_tab(i),0)),0,NULL,
(nvl(tmp.project_burdened_cost,0)+ NVL(l_diff_proj_burd_cost_tab(i),0))))
,'ALL',
decode(tmp.txn_currency_code,tmp.project_currency_code,tmp.project_burdened_cost
,decode((nvl(tmp.project_burdened_cost,0)+ NVL(l_diff_proj_burd_cost_tab(i),0)),0,NULL,
(nvl(tmp.project_burdened_cost,0)+ NVL(l_diff_proj_burd_cost_tab(i),0))))
,'REVENUE',tmp.project_burdened_cost)
,tmp.project_revenue = decode(p_budget_version_type,'COST', tmp.project_revenue
,'ALL',decode(tmp.txn_currency_code,tmp.project_currency_code,tmp.project_revenue
,DECODE(l_null_revenue_amts_flag,'Y',tmp.project_revenue
,decode((nvl(tmp.project_revenue,0)+nvl(l_diff_proj_rev_tab(i),0)),0,NULL,
(nvl(tmp.project_revenue,0)+nvl(l_diff_proj_rev_tab(i),0)))))
,'REVENUE', decode(tmp.txn_currency_code,tmp.project_currency_code,tmp.project_revenue
,decode((nvl(tmp.project_revenue,0)+nvl(l_diff_proj_rev_tab(i),0)),0,NULL,
(nvl(tmp.project_revenue,0)+nvl(l_diff_proj_rev_tab(i),0)))))
/*---ProjFunc columns ---*/
,tmp.projfunc_raw_cost = decode(p_budget_version_type,'COST',
decode(tmp.txn_currency_code,tmp.projfunc_currency_code,tmp.projfunc_raw_cost
,decode((nvl(tmp.projfunc_raw_cost,0)+ NVL(l_diff_pjfc_raw_cost_tab(i),0)),0,NULL,
(nvl(tmp.projfunc_raw_cost,0)+ NVL(l_diff_pjfc_raw_cost_tab(i),0))))
,'ALL',
decode(tmp.txn_currency_code,tmp.projfunc_currency_code,tmp.projfunc_raw_cost
,decode((nvl(tmp.projfunc_raw_cost,0)+ NVL(l_diff_pjfc_raw_cost_tab(i),0)),0,NULL,
(nvl(tmp.projfunc_raw_cost,0)+ NVL(l_diff_pjfc_raw_cost_tab(i),0))))
,'REVENUE',tmp.projfunc_raw_cost)
,tmp.projfunc_burdened_cost = decode(p_budget_version_type,'COST',
decode(tmp.txn_currency_code,tmp.projfunc_currency_code,tmp.projfunc_burdened_cost
,decode((nvl(tmp.projfunc_burdened_cost,0)+ NVL(l_diff_pjfc_burd_cost_tab(i),0)),0,NULL,
(nvl(tmp.projfunc_burdened_cost,0)+ NVL(l_diff_pjfc_burd_cost_tab(i),0))))
,'ALL',
decode(tmp.txn_currency_code,tmp.projfunc_currency_code,tmp.projfunc_burdened_cost
,decode((nvl(tmp.projfunc_burdened_cost,0)+ NVL(l_diff_pjfc_burd_cost_tab(i),0)),0,NULL,
(nvl(tmp.projfunc_burdened_cost,0)+ NVL(l_diff_pjfc_burd_cost_tab(i),0))))
,'REVENUE',tmp.projfunc_burdened_cost)
,tmp.projfunc_revenue = decode(p_budget_version_type,'COST', tmp.projfunc_revenue
,'ALL',decode(tmp.txn_currency_code,tmp.projfunc_currency_code,tmp.projfunc_revenue
,DECODE(l_null_revenue_amts_flag,'Y',tmp.projfunc_revenue
,decode((nvl(tmp.projfunc_revenue,0)+nvl(l_diff_pjfc_rev_tab(i),0)),0,NULL,
(nvl(tmp.projfunc_revenue,0)+nvl(l_diff_pjfc_rev_tab(i),0)))))
,'REVENUE', decode(tmp.txn_currency_code,tmp.projfunc_currency_code,tmp.projfunc_revenue
,decode((nvl(tmp.projfunc_revenue,0)+nvl(l_diff_pjfc_rev_tab(i),0)),0,NULL,
(nvl(tmp.projfunc_revenue,0)+nvl(l_diff_pjfc_rev_tab(i),0)))))
WHERE tmp.resource_assignment_id = l_resource_assignment_tab(i)
AND tmp.txn_currency_code = l_txn_currency_code_tab(i)
AND tmp.start_date = l_start_date_tab(i)
AND (NVL(l_diff_proj_raw_cost_tab(i),0) <> 0
OR NVL(l_diff_proj_burd_cost_tab(i),0) <> 0
OR NVL(l_diff_proj_rev_tab(i),0) <> 0
OR NVL(l_diff_pjfc_raw_cost_tab(i),0) <> 0
OR NVL(l_diff_pjfc_burd_cost_tab(i),0) <> 0
OR NVL(l_diff_pjfc_rev_tab(i),0) <> 0 );
AND tmp.rowid in (select max(rowid)
from pa_fp_rollup_tmp tmp2
where tmp2.resource_assignment_id = tmp.resource_assignment_id
and tmp2.txn_currency_code = tmp.txn_currency_code
and tmp2.start_date = tmp.start_date
);
g_stage := 'Update_PCPFC_rounding_diff:103';
print_msg('End of Update_PCPFCrounding_diff API return Sts['||x_return_status||']');
print_msg('Failed in Update_PCPFCrounding_diff API ['||sqlcode||sqlerrm);
,p_procedure_name => 'Update_PCPFC_rounding_diff' );
print_msg('Failed in Update_PCPFC_rounding_diff substr(SQLERRM,1,240) => '|| substr(SQLERRM,1,240));
END Update_PCPFC_rounding_diff;
* updated with the rounding discrepancy amounts
*/
PROCEDURE Update_rounding_diff(
p_project_id IN pa_budget_versions.project_id%type
,p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
,p_calling_module IN VARCHAR2 DEFAULT NULL
,p_source_context IN pa_fp_res_assignments_tmp.source_context%TYPE
,p_wp_cost_enabled_flag IN varchar2
,p_budget_version_type IN varchar2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2) IS
CURSOR cur_round_discrepancy_lines IS
SELECT resource_assignment_id
,txn_currency_code
,MAX(start_date)
,SUM(quantity)
,SUM(init_quantity)
,SUM(txn_raw_cost)
,SUM(txn_init_raw_cost)
,SUM(txn_burdened_cost)
,SUM(txn_init_burdened_cost)
,SUM(txn_revenue)
,SUM(txn_init_revenue)
,SUM(((NVL(quantity,0) - nvl(init_quantity,0)) * nvl(rw_cost_rate_override,cost_rate))) unrounded_txn_raw_cost
,SUM(((NVL(quantity,0) - nvl(init_quantity,0)) * nvl(burden_cost_rate_override,burden_cost_rate))) unrounded_txn_burdened_cost
,SUM(((NVL(quantity,0) - nvl(init_quantity,0)) * nvl(bill_rate_override,bill_rate))) unrounded_txn_revenue
,SUM(decode(nvl(rw_cost_rate_override,nvl(cost_rate,0)),0,0
,pa_currency.round_trans_currency_amt1((nvl(txn_raw_cost,0) - nvl(txn_init_raw_cost,0)),txn_currency_code))) rounded_txn_raw_cost
,SUM(decode(nvl(burden_cost_rate_override,nvl(burden_cost_rate,0)),0,0
,pa_currency.round_trans_currency_amt1((nvl(txn_burdened_cost,0) - nvl(txn_init_burdened_cost,0)),txn_currency_code))) rounded_txn_burdened_cost
,SUM(decode(nvl(bill_rate_override,nvl(bill_rate,0)),0,0
,pa_currency.round_trans_currency_amt1((nvl(txn_revenue,0) - nvl(txn_init_revenue,0)),txn_currency_code))) rounded_txn_revenue
/**
,SUM(pa_currency.round_trans_currency_amt1(((NVL(quantity,0) - nvl(init_quantity,0)) * nvl(rw_cost_rate_override,cost_rate))
,txn_currency_code)) rounded_txn_raw_cost
,SUM(pa_currency.round_trans_currency_amt1(((NVL(quantity,0) - nvl(init_quantity,0)) * nvl(burden_cost_rate_override,burden_cost_rate))
,txn_currency_code)) rounded_txn_burdened_cost
,SUM(pa_currency.round_trans_currency_amt1(((NVL(quantity,0) - nvl(init_quantity,0)) * nvl(bill_rate_override,bill_rate))
,txn_currency_code)) rounded_txn_revenue
**/
,to_number(NULL) diff_raw_cost
,to_number(NULL) diff_burden_cost
,to_number(NULL) diff_revenue
FROM pa_fp_rollup_tmp tmp
WHERE tmp.budget_version_id = p_budget_version_id
AND nvl(quantity,0) <> 0
GROUP BY resource_assignment_id
,txn_currency_code ;
pa_debug.init_err_stack('PA_FP_CALC_PLAN_PKG.Update_rounding_diff');
g_stage := 'Update_rounding_diff:100';
pa_debug.g_err_stage := 'Entered Update_rounding_diff API';
l_resource_assignment_tab.delete;
l_txn_currency_code_tab.delete;
l_start_date_tab.delete;
l_quantity_tab.delete;
l_init_quantity_tab.delete;
l_txn_raw_cost_tab.delete;
l_txn_init_raw_cost_tab.delete;
l_txn_burdened_cost_tab.delete;
l_txn_init_burdened_cost_tab.delete;
l_txn_revenue_tab.delete;
l_txn_init_revenue_tab.delete;
l_unrounded_txn_raw_cost_tab.delete;
l_unround_txn_burden_cost_tab.delete;
l_unrounded_txn_revenue_tab.delete;
l_rounded_txn_raw_cost_tab.delete;
l_rounded_txn_burden_cost_tab.delete;
l_rounded_txn_revenue_tab.delete ;
l_diff_raw_cost_tab.delete;
l_diff_burden_cost_tab.delete;
l_diff_revenue_tab.delete;
g_stage := 'Update_rounding_diff:101';
g_stage := 'Update_rounding_diff:102';
UPDATE /*+ INDEX(TMP PA_FP_ROLLUP_TMP_N1) */ pa_fp_rollup_tmp tmp
SET tmp.txn_raw_cost = decode(p_budget_version_type,'COST'
,DECODE((nvl(tmp.txn_raw_cost,0)+NVL(l_diff_raw_cost_tab(i),0)),0,NULL
,(nvl(tmp.txn_raw_cost,0)+NVL(l_diff_raw_cost_tab(i),0)))
,'ALL',DECODE((nvl(tmp.txn_raw_cost,0)+NVL(l_diff_raw_cost_tab(i),0)),0,NULL
,(nvl(tmp.txn_raw_cost,0)+NVL(l_diff_raw_cost_tab(i),0)))
,'REVENUE',tmp.txn_raw_cost)
,tmp.txn_burdened_cost = decode(p_budget_version_type,'COST'
,DECODE((nvl(tmp.txn_burdened_cost,0)+NVL(l_diff_burden_cost_tab(i),0)),0,NULL
,(nvl(tmp.txn_burdened_cost,0)+NVL(l_diff_burden_cost_tab(i),0)))
,'ALL',DECODE((nvl(tmp.txn_burdened_cost,0)+NVL(l_diff_burden_cost_tab(i),0)),0,NULL
,(nvl(tmp.txn_burdened_cost,0)+NVL(l_diff_burden_cost_tab(i),0)))
,'REVENUE',tmp.txn_burdened_cost)
,tmp.txn_revenue = decode(p_budget_version_type,'COST',tmp.txn_revenue
,'ALL',DECODE(l_null_revenue_amts_flag,'Y',tmp.txn_revenue
,DECODE((nvl(tmp.txn_revenue,0)+NVL(l_diff_revenue_tab(i),0)),0,NULL
,(nvl(tmp.txn_revenue,0)+NVL(l_diff_revenue_tab(i),0))))
,'REVENUE',DECODE((nvl(tmp.txn_revenue,0)+NVL(l_diff_revenue_tab(i),0)),0,NULL
,(nvl(tmp.txn_revenue,0)+NVL(l_diff_revenue_tab(i),0))))
,tmp.attribute15 = 'RoundingDiscrepancyLine'
WHERE tmp.resource_assignment_id = l_resource_assignment_tab(i)
AND tmp.txn_currency_code = l_txn_currency_code_tab(i)
AND tmp.start_date = l_start_date_tab(i)
AND (NVL(l_diff_raw_cost_tab(i),0) <> 0 OR NVL(l_diff_burden_cost_tab(i),0) <> 0 OR NVL(l_diff_revenue_tab(i),0) <> 0)
;
AND tmp.rowid in (select max(rowid)
from pa_fp_rollup_tmp tmp2
where tmp2.resource_assignment_id = tmp.resource_assignment_id
and tmp2.txn_currency_code = tmp.txn_currency_code
and tmp2.start_date = tmp.start_date
);
g_stage := 'Update_rounding_diff:103';
g_stage := 'Update_rounding_diff:104';
print_msg('End of Update_rounding_diff API return Sts['||x_return_status||']');
print_msg('Failed in Update_rounding_diff API ['||sqlcode||sqlerrm);
,p_procedure_name => 'Update_rounding_diff' );
print_msg('Failed in Update_rounding_diff substr(SQLERRM,1,240) => '|| substr(SQLERRM,1,240));
END Update_rounding_diff;
,p_delete_budget_lines_tab IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type()
,p_spread_amts_flag_tab IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type()
,p_txn_currency_code_tab IN SYSTEM.pa_varchar2_15_tbl_type DEFAULT SYSTEM.pa_varchar2_15_tbl_type()
,p_txn_currency_override_tab IN SYSTEM.pa_varchar2_15_tbl_type DEFAULT SYSTEM.pa_varchar2_15_tbl_type()
,p_total_qty_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_addl_qty_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_total_raw_cost_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_addl_raw_cost_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_total_burdened_cost_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_addl_burdened_cost_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_total_revenue_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_addl_revenue_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_raw_cost_rate_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_rw_cost_rate_override_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_b_cost_rate_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_b_cost_rate_override_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_bill_rate_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_bill_rate_override_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_line_start_date_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
,p_line_end_date_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
/* for enhancement */
,p_spread_curve_id_old_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_spread_curve_id_new_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_sp_fixed_date_old_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
,p_sp_fixed_date_new_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
,p_plan_start_date_old_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
,p_plan_start_date_new_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
,p_plan_end_date_old_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
,p_plan_end_date_new_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
,p_re_spread_flag_tab IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type()
,p_sp_curve_change_flag_tab IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type()
,p_plan_dates_change_flag_tab IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type()
,p_spfix_date_flag_tab IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type()
,p_mfc_cost_change_flag_tab IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type()
,p_mfc_cost_type_id_old_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_mfc_cost_type_id_new_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_rlm_id_change_flag_tab IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type()
,p_fp_task_billable_flag_tab IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type()
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2) IS
l_res_assignment_tab_count NUMBER := 0;
SELECT 'Y'
FROM dual
WHERE EXISTS (select null
from pa_budget_lines bl
Where bl.budget_version_id = p_budget_version_id
AND (nvl(bl.init_quantity,0) <> 0
OR nvl(bl.txn_init_raw_cost,0) <> 0
OR nvl(bl.txn_init_burdened_cost,0) <> 0
OR nvl(bl.txn_init_revenue,0) <> 0
)
);
IF l_return_status = 'S' AND p_delete_budget_lines_tab.COUNT > 0 THEN
IF p_delete_budget_lines_tab.COUNT <> l_res_assignment_tab_count THEN
l_error_msg_code := 'PA_FP_CALC_DEL_CNT';
SELECT sp.spread_curve_id
FROM pa_spread_curves_b sp
WHERE sp.spread_curve_code = 'EVEN'
AND rownum = 1;
UPDATE PA_RESOURCE_ASSIGNMENTS ra
SET ra.spread_curve_id = DECODE(ra.spread_curve_id,6
,decode(sign(nvl(ra.sp_fixed_date,p_etc_start_date) - p_etc_start_date),-1,l_evenSpCurveId,ra.spread_curve_id)
,ra.spread_curve_id)
,ra.sp_fixed_date = DECODE(sign(nvl(ra.sp_fixed_date,p_etc_start_date) - p_etc_start_date),-1,NULL,ra.sp_fixed_date)
/*Bug fix:4122400--,ra.planning_end_date = DECODE(sign(ra.planning_end_date-p_etc_start_date),-1,p_etc_start_date,ra.planning_end_date) */
,ra.planning_end_date = DECODE(ra.spread_curve_id,6
,decode(sign(nvl(ra.sp_fixed_date,p_etc_start_date) - p_etc_start_date),-1,p_etc_start_date,ra.planning_end_date)
,decode(sign(ra.planning_end_date-p_etc_start_date),-1,p_etc_start_date,ra.planning_end_date))
WHERE ra.budget_version_id = p_budget_version_id
AND EXISTS (SELECT null
FROM pa_fp_spread_calc_tmp tmp
WHERE tmp.budget_version_id = ra.budget_version_id
AND tmp.resource_assignment_id = ra.resource_assignment_id);
/* For Non-Time phase budgets, Update the budget lines with planning start and planning end dates */
g_stage :='Reset_Planning_end_date:101';
UPDATE pa_budget_lines bl
SET ( bl.start_date, bl.end_date ) = (select ra.planning_start_date, ra.planning_end_date
From pa_resource_assignments ra
Where ra.resource_assignment_id = bl.resource_assignment_id)
WHERE bl.budget_version_id = p_budget_version_id
AND EXISTS ( SELECT 'Y'
FROM pa_resource_assignments pra
,pa_fp_spread_calc_tmp tmp
WHERE pra.resource_assignment_id = bl.resource_assignment_id
AND pra.budget_version_id = bl.budget_version_id
AND tmp.budget_version_id = pra.budget_version_id
AND tmp.resource_assignment_id = pra.resource_assignment_id
);
/* This api bulk updates the resource assignments table for the fixed date spread curve which are having
* more than one budget line
*/
PROCEDURE PreProcess_BlkProgress_lines
(p_budget_version_id IN Number
,p_etc_start_date IN Date
,p_apply_progress_flag IN Varchar2
,x_return_status OUT NOCOPY Varchar2
,x_msg_data OUT NOCOPY Varchar2
) IS
CURSOR cur_spbl_chk IS
SELECT tmp.resource_assignment_id
FROM pa_fp_spread_calc_tmp tmp
,pa_resource_assignments ra
WHERE tmp.budget_version_id = p_budget_version_id
AND tmp.resource_assignment_id = ra.resource_assignment_id
AND NVL(tmp.sp_curve_change_flag,'N') = 'N'
/* Bug fix: AND ra.sp_fixed_date is NOT NULL this is commented as in some ofthe flows,
* the sp fixed date is null but spread curve is 6 */
AND ra.spread_curve_id = 6
AND EXISTS ( select null
from pa_budget_lines bl
where bl.budget_version_id = p_budget_version_id
and bl.resource_assignment_id = ra.resource_assignment_id
and bl.txn_currency_code = tmp.txn_currency_code
group by bl.resource_assignment_id
,bl.txn_currency_code
having count(*) > 1
);
SELECT sp.spread_curve_id
FROM pa_spread_curves_b sp
WHERE sp.spread_curve_code = 'EVEN'
AND rownum = 1;
SELECT /*+ NO_UNNEST INDEX (BL,PA_BUDGET_LINES_U1) */ bl.budget_line_id -- bug 4873834
,bl.resource_assignment_id
,bl.txn_currency_code
,bl.start_date
,bl.end_date
,bl.period_name
,bl.quantity
,bl.txn_raw_cost
,bl.txn_burdened_cost
,bl.txn_revenue
,bl.project_raw_cost
,bl.project_burdened_cost
,bl.project_revenue
,bl.raw_cost projfunc_raw_cost
,bl.burdened_cost projfunc_burdened_cost
,bl.revenue projfunc_revenue
,bl.project_currency_code
,bl.projfunc_currency_code
,bl.cost_rejection_code
,bl.revenue_rejection_code
,bl.burden_rejection_code
,bl.pfc_cur_conv_rejection_code
,bl.pc_cur_conv_rejection_code
,bl.init_quantity
,bl.txn_init_raw_cost
,bl.txn_init_burdened_cost
,bl.txn_init_revenue
,bl.project_init_raw_cost
,bl.project_init_burdened_cost
,bl.project_init_revenue
,bl.init_raw_cost
,bl.init_burdened_cost
,bl.init_revenue
FROM pa_budget_lines bl
,pa_fp_spread_calc_tmp tmp
WHERE tmp.budget_version_id = p_budget_version_id
AND bl.budget_version_id = tmp.budget_version_id
AND bl.resource_assignment_id = tmp.resource_assignment_id
AND bl.txn_currency_code = tmp.txn_currency_code
AND g_spread_from_date is NOT NULL
AND ((bl.start_date >= g_spread_from_date )
OR (g_spread_from_date BETWEEN bl.start_date AND bl.end_date)
)
AND (NVL(bl.quantity,0) - NVL(bl.init_quantity,0)) < 0 --corrupted data
AND ( NVL(bl.init_quantity,0) <> 0 OR
NVL(bl.txn_init_raw_cost ,0) <> 0 OR
NVL(bl.txn_init_burdened_cost ,0) <> 0 OR
NVL(bl.txn_init_revenue ,0) <> 0
);
l_raId_tab.delete;
/* update the RA and set sp_fixed_date to null so that spread api will spread the amts evenly */
UPDATE PA_RESOURCE_ASSIGNMENTS ra
SET ra.spread_curve_id = l_evenSpCurveId
,ra.sp_fixed_date = NULL
/* bug fix:4122400 for fixed date spread curve, change the plan end date as ETC start date */
/* Bug fix: 4247427 ,ra.planning_end_date = p_etc_start_date
* ETc start date can be before plannig end date, then donot change the end date */
,ra.planning_end_date = decode(sign(ra.planning_end_date-p_etc_start_date),-1,p_etc_start_date,ra.planning_end_date)
WHERE ra.resource_assignment_id = l_raId_tab(i);
l_num_rowsdeleted Number;
SELECT 'Y'
FROM DUAL
WHERE EXISTS (select null
from pa_budget_lines bl
,pa_resource_assignments ra
where ra.resource_assignment_id = p_res_ass_id
and bl.resource_assignment_id = ra.resource_assignment_id
and bl.txn_currency_code = p_txn_currency_code
and ra.sp_fixed_date is NOT NULL
and ra.spread_curve_id = 6
group by bl.resource_assignment_id
,bl.txn_currency_code
having count(*) > 1
);
SELECT sp.spread_curve_id
FROM pa_spread_curves_b sp
WHERE sp.spread_curve_code = 'EVEN'
AND rownum = 1;
/* update the RA and set sp_fixed_date to null so that spread api will spread the amts evenly */
UPDATE PA_RESOURCE_ASSIGNMENTS ra
SET ra.spread_curve_id = l_evenSpCurveId
,ra.sp_fixed_date = NULL
/* bug fix:4122400 for fixed date spread curve, change the plan end date as ETC start date */
,ra.planning_end_date = p_etc_start_date
WHERE ra.resource_assignment_id = p_res_ass_id;
/* call delete budget lines to delete all the lines so that spread will respred the ETC qty */
/* Bug fix: 4142150 When progress is applied, the quantity should be spread based on
* exisitng line distribution method, so donot delete the budget lines
delete_budget_lines
( p_budget_version_id => p_budget_version_id
,p_resource_assignment_id => p_res_ass_id
,p_txn_currency_code => p_txn_currency_code
,p_line_start_date => p_line_start_date
,p_line_end_date => p_line_end_date
,p_source_context => p_source_context
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,x_num_rowsdeleted => l_num_rowsdeleted
);
print_msg('Unexpected error from delete_budget_lines');
SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
bl.budget_line_id
,bl.resource_assignment_id
,bl.txn_currency_code
,bl.start_date
,bl.end_date
,bl.period_name
,bl.quantity
,bl.txn_raw_cost
,bl.txn_burdened_cost
,bl.txn_revenue
,bl.project_raw_cost
,bl.project_burdened_cost
,bl.project_revenue
,bl.raw_cost projfunc_raw_cost
,bl.burdened_cost projfunc_burdened_cost
,bl.revenue projfunc_revenue
,bl.project_currency_code
,bl.projfunc_currency_code
,bl.cost_rejection_code
,bl.revenue_rejection_code
,bl.burden_rejection_code
,bl.pfc_cur_conv_rejection_code
,bl.pc_cur_conv_rejection_code
FROM pa_budget_lines bl
,pa_fp_spread_calc_tmp tmp
WHERE tmp.budget_version_id = p_budget_version_id
AND bl.resource_assignment_id = tmp.resource_assignment_id
AND bl.txn_currency_code = tmp.txn_currency_code
AND bl.start_date < p_etc_start_date
AND bl.end_date < p_etc_start_date
AND ( (NVL(bl.quantity,0) - NVl(bl.init_quantity,0)) <> 0
OR (Nvl(bl.txn_raw_cost,0) - Nvl(bl.txn_init_raw_cost,0)) <> 0
OR (Nvl(bl.txn_burdened_cost,0) - nvl(bl.txn_init_burdened_cost,0)) <> 0
OR (Nvl(bl.txn_revenue,0) - nvl(bl.txn_init_revenue,0)) <> 0
)
AND ( NVL(bl.init_quantity,0) = 0
and NVL(bl.txn_init_raw_cost ,0) = 0
and NVL(bl.txn_init_burdened_cost ,0) = 0
and NVL(bl.txn_init_revenue ,0) = 0
);
SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
bl.budget_line_id
,bl.resource_assignment_id
,bl.txn_currency_code
,bl.start_date
--,rl.alias resource_name
,bl.end_date
,bl.period_name
,bl.quantity
,bl.txn_raw_cost
,bl.txn_burdened_cost
,bl.txn_revenue
,bl.project_raw_cost
,bl.project_burdened_cost
,bl.project_revenue
,bl.raw_cost projfunc_raw_cost
,bl.burdened_cost projfunc_burdened_cost
,bl.revenue projfunc_revenue
,bl.project_currency_code
,bl.projfunc_currency_code
,bl.cost_rejection_code
,bl.revenue_rejection_code
,bl.burden_rejection_code
,bl.pfc_cur_conv_rejection_code
,bl.pc_cur_conv_rejection_code
FROM pa_budget_lines bl
,pa_fp_spread_calc_tmp tmp
WHERE tmp.budget_version_id = p_budget_version_id
AND bl.resource_assignment_id = tmp.resource_assignment_id
AND bl.txn_currency_code = tmp.txn_currency_code
AND bl.start_date < p_etc_start_date
AND bl.end_date < p_etc_start_date
AND ( (NVL(bl.quantity,0) - NVl(bl.init_quantity,0)) <> 0
OR (Nvl(bl.txn_raw_cost,0) - Nvl(bl.txn_init_raw_cost,0)) <> 0
OR (Nvl(bl.txn_burdened_cost,0) - nvl(bl.txn_init_burdened_cost,0)) <> 0
OR (Nvl(bl.txn_revenue,0) - nvl(bl.txn_init_revenue,0)) <> 0
)
AND (bl.init_quantity is NOT NULL
or bl.txn_init_raw_cost is NOT NULL
or bl.txn_init_burdened_cost is NOT NULL
or bl.txn_init_revenue is NOT NULL
);
SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
bl.budget_line_id
,bl.resource_assignment_id
,bl.txn_currency_code
,bl.start_date
,null resource_name --rl.alias resource_name
,bl.end_date
,bl.period_name
,bl.quantity
,bl.txn_raw_cost
,bl.txn_burdened_cost
,bl.txn_revenue
,bl.project_raw_cost
,bl.project_burdened_cost
,bl.project_revenue
,bl.raw_cost projfunc_raw_cost
,bl.burdened_cost projfunc_burdened_cost
,bl.revenue projfunc_revenue
,bl.project_currency_code
,bl.projfunc_currency_code
,bl.cost_rejection_code
,bl.revenue_rejection_code
,bl.burden_rejection_code
,bl.pfc_cur_conv_rejection_code
,bl.pc_cur_conv_rejection_code
FROM pa_budget_lines bl
,pa_resource_assignments ra
,pa_fp_spread_calc_tmp tmp
WHERE tmp.budget_version_id = p_budget_version_id
AND bl.resource_assignment_id = tmp.resource_assignment_id
AND bl.txn_currency_code = tmp.txn_currency_code
AND ra.resource_assignment_id = tmp.resource_assignment_id
AND NVL(ra.rate_based_flag,'N') = 'N'
AND bl.start_date < p_etc_start_date
AND bl.end_date < p_etc_start_date
AND (bl.init_quantity is NOT NULL
or bl.txn_init_raw_cost is NOT NULL
or bl.txn_init_burdened_cost is NOT NULL
or bl.txn_init_revenue is NOT NULL
)
AND ( (g_fp_budget_version_type = 'REVENUE'
and ( nvl(bl.txn_init_raw_cost,0) <> 0
OR (decode(round(bl.init_quantity,2)
,decode(decode(nvl(g_wp_version_flag,'N'),'Y',NVL(G_TRACK_WP_COSTS_FLAG,'N'),'Y'),'N'
,decode(nvl(bl.txn_init_revenue,0),0,round(bl.init_quantity,2),round(bl.txn_init_revenue,2))
,round(bl.txn_init_revenue,2)),'Y','N') <> 'Y'
))
)OR
( g_fp_budget_version_type ='COST'
and ( nvl(bl.txn_init_revenue,0) <> 0
OR (decode(round(bl.init_quantity,2)
,decode(decode(nvl(g_wp_version_flag,'N'),'Y',NVL(G_TRACK_WP_COSTS_FLAG,'N'),'Y'),'N'
,decode(nvl(bl.txn_init_raw_cost,0),0,round(bl.init_quantity,2),round(bl.txn_init_raw_cost,2))
,round(bl.txn_init_raw_cost,2)),'Y','N') <> 'Y' ))
) OR
( g_fp_budget_version_type = 'ALL'
and (decode(round(bl.init_quantity,2)
,decode(decode(nvl(g_wp_version_flag,'N'),'Y',NVL(G_TRACK_WP_COSTS_FLAG,'N'),'Y'),'N'
,decode(nvl(bl.txn_init_raw_cost,0),0,round(bl.init_quantity,2),round(bl.txn_init_raw_cost,2))
,round(bl.txn_init_raw_cost,2)),'Y','N') <> 'Y' )
)
);
l_corrupted_bl_update_rows Number := 0;
l_budget_Line_id_tab.delete;
l_resource_assignment_id_tab.delete;
l_txn_currency_code_tab.delete;
l_start_date_tab.delete;
l_end_date_tab.delete;
l_period_name_tab.delete;
l_quantity_tab.delete;
l_txn_raw_cost_tab.delete;
l_txn_burdened_cost_tab.delete;
l_txn_revenue_tab.delete;
l_project_raw_cost_tab.delete;
l_project_burdened_cost_tab.delete;
l_project_revenue_tab.delete;
l_projfunc_raw_cost_tab.delete;
l_projfunc_burdened_cost_tab.delete;
l_projfunc_revenue_tab.delete;
l_project_curr_code_tab.delete;
l_projfunc_curr_code_tab.delete;
l_cost_rejection_code_tab.delete;
l_revenue_rejection_code_tab.delete;
l_burden_rejection_code_tab.delete;
l_pfc_cur_conv_rej_code_tab.delete;
l_pc_cur_conv_rej_code_tab.delete;
l_upd_budget_Line_id_tab.delete;
l_upd_ra_id_tab.delete;
l_upd_txn_curr_code_tab.delete;
l_upd_start_date_tab.delete;
l_upd_end_date_tab.delete;
l_upd_period_name_tab.delete;
l_upd_quantity_tab.delete;
l_upd_txn_raw_cost_tab.delete;
l_upd_txn_burden_cost_tab.delete;
l_upd_txn_revenue_tab.delete;
l_upd_project_raw_cost_tab.delete;
l_upd_project_burden_cost_tab.delete;
l_upd_project_revenue_tab.delete;
l_upd_projfunc_raw_cost_tab.delete;
l_upd_projfunc_burden_cost_tab.delete;
l_upd_projfunc_revenue_tab.delete;
l_upd_project_curr_code_tab.delete;
l_upd_projfunc_curr_code_tab.delete;
l_upd_cost_rejection_tab.delete;
l_upd_revenue_rejection_tab.delete;
l_upd_burden_rejection_tab.delete;
l_upd_pfc_cur_conv_rej_tab.delete;
l_upd_pc_cur_conv_rej_tab.delete;
/* update the reporting lines before deleteing the budget lines */
Add_Toreporting_Tabls
(p_calling_module => 'CALCULATE_API'
,p_activity_code => 'UPDATE'
,p_budget_version_id => p_budget_version_id
,p_budget_line_id => l_budget_Line_id_tab(i)
,p_resource_assignment_id => l_resource_assignment_id_tab(i)
,p_start_date => l_start_date_tab(i)
,p_end_date => l_end_date_tab(i)
,p_period_name => l_period_name_tab(i)
,p_txn_currency_code => l_txn_currency_code_tab(i)
,p_quantity => l_quantity_tab(i) * -1
,p_txn_raw_cost => l_txn_raw_cost_tab(i) * -1
,p_txn_burdened_cost => l_txn_burdened_cost_tab(i) * -1
,p_txn_revenue => l_txn_revenue_tab(i) * -1
,p_project_currency_code => l_project_curr_code_tab(i)
,p_project_raw_cost => l_project_raw_cost_tab(i) * -1
,p_project_burdened_cost => l_project_burdened_cost_tab(i) * -1
,p_project_revenue => l_project_revenue_tab(i) * -1
,p_projfunc_currency_code => l_projfunc_curr_code_tab(i)
,p_projfunc_raw_cost => l_projfunc_raw_cost_tab(i) * -1
,p_projfunc_burdened_cost => l_projfunc_burdened_cost_tab(i) * -1
,p_projfunc_revenue => l_projfunc_revenue_tab(i) * -1
,p_rep_line_mode => 'REVERSAL'
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
,p_delete_flag => 'Y'
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
* so delete all the budget lines instead of updating it to null
**/
DELETE FROM pa_budget_lines bl
WHERE bl.budget_line_id = l_budget_Line_id_tab(i);
,p_activity_code => 'UPDATE'
,p_budget_version_id => p_budget_version_id
,p_budget_line_id => l_budget_Line_id_tab(i)
,p_resource_assignment_id => l_resource_assignment_id_tab(i)
,p_start_date => l_start_date_tab(i)
,p_end_date => l_end_date_tab(i)
,p_period_name => l_period_name_tab(i)
,p_txn_currency_code => l_txn_currency_code_tab(i)
,p_quantity => l_quantity_tab(i) * -1
,p_txn_raw_cost => l_txn_raw_cost_tab(i) * -1
,p_txn_burdened_cost => l_txn_burdened_cost_tab(i) * -1
,p_txn_revenue => l_txn_revenue_tab(i) * -1
,p_project_currency_code => l_project_curr_code_tab(i)
,p_project_raw_cost => l_project_raw_cost_tab(i) * -1
,p_project_burdened_cost => l_project_burdened_cost_tab(i) * -1
,p_project_revenue => l_project_revenue_tab(i) * -1
,p_projfunc_currency_code => l_projfunc_curr_code_tab(i)
,p_projfunc_raw_cost => l_projfunc_raw_cost_tab(i) * -1
,p_projfunc_burdened_cost => l_projfunc_burdened_cost_tab(i) * -1
,p_projfunc_revenue => l_projfunc_revenue_tab(i) * -1
,p_rep_line_mode => 'REVERSAL'
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
l_corrupted_bl_update_rows := 0;
UPDATE pa_budget_lines bl
SET bl.quantity = bl.init_quantity
-- Bug 15928585
, bl.display_quantity = bl.init_quantity
-- End bug 15928585
,bl.txn_raw_cost = bl.txn_init_raw_cost
,bl.txn_burdened_cost = bl.txn_init_burdened_cost
,bl.txn_revenue = bl.txn_init_revenue
/* Bug fix: 4071198 As discussed with sanjay, for closed periods, instead of setting the Etc rates as null
* derive the plan rates. so that the copy version functionality when copies the budget versions
* proper rates will be copied*/
,bl.txn_standard_cost_rate = DECODE(nvl(bl.init_quantity,0),0,NULL,(bl.txn_init_raw_cost/bl.init_quantity))
,bl.txn_cost_rate_override = DECODE(nvl(bl.init_quantity,0),0,NULL,(bl.txn_init_raw_cost/bl.init_quantity))
,bl.burden_cost_rate = DECODE(nvl(bl.init_quantity,0),0,NULL,(bl.txn_init_burdened_cost/bl.init_quantity))
,bl.burden_cost_rate_override = DECODE(nvl(bl.init_quantity,0),0,NULL,(bl.txn_init_burdened_cost/bl.init_quantity))
,bl.txn_standard_bill_rate = DECODE(nvl(bl.init_quantity,0),0,NULL,(bl.txn_init_revenue/bl.init_quantity))
,bl.txn_bill_rate_override = DECODE(nvl(bl.init_quantity,0),0,NULL,(bl.txn_init_revenue/bl.init_quantity))
,bl.raw_cost = bl.init_raw_cost
,bl.burdened_cost = bl.init_burdened_cost
,bl.revenue = bl.init_revenue
,bl.project_raw_cost = bl.project_init_raw_cost
,bl.project_burdened_cost = bl.project_init_burdened_cost
,bl.project_revenue = bl.project_init_revenue
,bl.projfunc_cost_rate_type = null
,bl.projfunc_cost_exchange_rate = null
,bl.projfunc_cost_rate_date_type = null
,bl.projfunc_cost_rate_date = null
,bl.projfunc_rev_rate_type = null
,bl.projfunc_rev_exchange_rate = null
,bl.projfunc_rev_rate_date_type = null
,bl.projfunc_rev_rate_date = null
,bl.project_cost_rate_type = null
,bl.project_cost_exchange_rate = null
,bl.project_cost_rate_date_type = null
,bl.project_cost_rate_date = null
,bl.project_rev_rate_type = null
,bl.project_rev_exchange_rate = null
,bl.project_rev_rate_date_type = null
,bl.project_rev_rate_date = null
,bl.pfc_cur_conv_rejection_code = null
,bl.pc_cur_conv_rejection_code = null
,bl.cost_rejection_code = null
,bl.burden_rejection_code = null
,bl.revenue_rejection_code = null
,bl.last_update_date = sysdate
,bl.last_updated_by = fnd_global.user_id
,bl.last_update_login = fnd_global.login_id
/* Bug fix:4257059 storing this value just for tracking purpose is causing
* issues in the generation flow
*,bl.other_rejection_code = 'PA_BLAMT_EXISTS_PRIOR_ETC'
*/
WHERE bl.budget_line_id = l_budget_Line_id_tab(i)
/*Bug fix:4257059 Added this returning clause to avoid one-select and one-update*/
RETURNING
bl.budget_line_id
,bl.resource_assignment_id
,bl.txn_currency_code
,bl.start_date
--,rl.alias resource_name
,bl.end_date
,bl.period_name
,bl.quantity
,bl.txn_raw_cost
,bl.txn_burdened_cost
,bl.txn_revenue
,bl.project_raw_cost
,bl.project_burdened_cost
,bl.project_revenue
,bl.raw_cost --projfunc_raw_cost
,bl.burdened_cost --projfunc_burdened_cost
,bl.revenue --projfunc_revenue
,bl.project_currency_code
,bl.projfunc_currency_code
,bl.cost_rejection_code
,bl.revenue_rejection_code
,bl.burden_rejection_code
,bl.pfc_cur_conv_rejection_code
,bl.pc_cur_conv_rejection_code
BULK COLLECT INTO
l_upd_budget_Line_id_tab
,l_upd_ra_id_tab
,l_upd_txn_curr_code_tab
,l_upd_start_date_tab
,l_upd_end_date_tab
,l_upd_period_name_tab
,l_upd_quantity_tab
,l_upd_txn_raw_cost_tab
,l_upd_txn_burden_cost_tab
,l_upd_txn_revenue_tab
,l_upd_project_raw_cost_tab
,l_upd_project_burden_cost_tab
,l_upd_project_revenue_tab
,l_upd_projfunc_raw_cost_tab
,l_upd_projfunc_burden_cost_tab
,l_upd_projfunc_revenue_tab
,l_upd_project_curr_code_tab
,l_upd_projfunc_curr_code_tab
,l_upd_cost_rejection_tab
,l_upd_revenue_rejection_tab
,l_upd_burden_rejection_tab
,l_upd_pfc_cur_conv_rej_tab
,l_upd_pc_cur_conv_rej_tab ;
l_corrupted_bl_update_rows := l_upd_budget_Line_id_tab.COUNT;
/** Bug fix:4257059 adding returning clause in the above update
* no need to hit the db again , so commentingout the code
IF (NVL(l_corrupted_bl_update_rows,0) > 0 AND l_upd_budget_Line_id_tab.COUNT > 0 ) Then --{
BEGIN
g_stage := 'clear_closed_period_etcs:106';
SELECT bl.budget_line_id
,bl.resource_assignment_id
,bl.txn_currency_code
,bl.start_date
--,rl.alias resource_name
,bl.end_date
,bl.period_name
,bl.quantity
,bl.txn_raw_cost
,bl.txn_burdened_cost
,bl.txn_revenue
,bl.project_raw_cost
,bl.project_burdened_cost
,bl.project_revenue
,bl.raw_cost projfunc_raw_cost
,bl.burdened_cost projfunc_burdened_cost
,bl.revenue projfunc_revenue
,bl.project_currency_code
,bl.projfunc_currency_code
,bl.cost_rejection_code
,bl.revenue_rejection_code
,bl.burden_rejection_code
,bl.pfc_cur_conv_rejection_code
,bl.pc_cur_conv_rejection_code
BULK COLLECT INTO
l_upd_budget_Line_id_tab
,l_upd_ra_id_tab
,l_upd_txn_curr_code_tab
,l_upd_start_date_tab
,l_upd_end_date_tab
,l_upd_period_name_tab
,l_upd_quantity_tab
,l_upd_txn_raw_cost_tab
,l_upd_txn_burden_cost_tab
,l_upd_txn_revenue_tab
,l_upd_project_raw_cost_tab
,l_upd_project_burden_cost_tab
,l_upd_project_revenue_tab
,l_upd_projfunc_raw_cost_tab
,l_upd_projfunc_burden_cost_tab
,l_upd_projfunc_revenue_tab
,l_upd_project_curr_code_tab
,l_upd_projfunc_curr_code_tab
,l_upd_cost_rejection_tab
,l_upd_revenue_rejection_tab
,l_upd_burden_rejection_tab
,l_upd_pfc_cur_conv_rej_tab
,l_upd_pc_cur_conv_rej_tab
FROM pa_budget_lines bl
,pa_fp_spread_calc_tmp tmp
WHERE bl.budget_version_id = p_budget_version_id
AND tmp.budget_version_id = bl.budget_version_id
AND bl.resource_assignment_id = tmp.resource_assignment_id
AND bl.txn_currency_code = tmp.txn_currency_code
AND bl.start_date < p_etc_start_date
AND bl.end_date < p_etc_start_date
AND bl.other_rejection_code = 'PA_BLAMT_EXISTS_PRIOR_ETC'
AND (bl.init_quantity is NOT NULL
or bl.txn_init_raw_cost is NOT NULL
or bl.txn_init_burdened_cost is NOT NULL
or bl.txn_init_revenue is NOT NULL
);
IF (NVL(l_corrupted_bl_update_rows,0) > 0 AND l_upd_budget_Line_id_tab.COUNT > 0 ) Then --{
--Now pass the corrected plan amounts to the PJI reporting apis
IF l_upd_budget_Line_id_tab.COUNT > 0 THEN
FOR i IN l_upd_budget_Line_id_tab.FIRST .. l_upd_budget_Line_id_tab.LAST LOOP
IF NVL(g_rollup_required_flag,'N') = 'Y' Then --{
/* bug fix:5031388
IF (l_upd_cost_rejection_tab(i) is NULL AND
l_upd_revenue_rejection_tab(i) is NULL AND
l_upd_burden_rejection_tab(i) is NULL AND
l_upd_pfc_cur_conv_rej_tab(i) is NULL AND
l_upd_pc_cur_conv_rej_tab(i) is NULL ) THEN
*/
--print_msg('Passing +ve of blCorrupt:LnId['||l_upd_budget_Line_id_tab(i)||']Qty['||l_upd_quantity_tab(i)||']');
,p_activity_code => 'UPDATE'
,p_budget_version_id => p_budget_version_id
,p_budget_line_id => l_upd_budget_Line_id_tab(i)
,p_resource_assignment_id => l_upd_ra_id_tab(i)
,p_start_date => l_upd_start_date_tab(i)
,p_end_date => l_upd_end_date_tab(i)
,p_period_name => l_upd_period_name_tab(i)
,p_txn_currency_code => l_upd_txn_curr_code_tab(i)
,p_quantity => l_upd_quantity_tab(i)
,p_txn_raw_cost => l_upd_txn_raw_cost_tab(i)
,p_txn_burdened_cost => l_upd_txn_burden_cost_tab(i)
,p_txn_revenue => l_upd_txn_revenue_tab(i)
,p_project_currency_code => l_upd_project_curr_code_tab(i)
,p_project_raw_cost => l_upd_project_raw_cost_tab(i)
,p_project_burdened_cost => l_upd_project_burden_cost_tab(i)
,p_project_revenue => l_upd_project_revenue_tab(i)
,p_projfunc_currency_code => l_upd_projfunc_curr_code_tab(i)
,p_projfunc_raw_cost => l_upd_projfunc_raw_cost_tab(i)
,p_projfunc_burdened_cost => l_upd_projfunc_burden_cost_tab(i)
,p_projfunc_revenue => l_upd_projfunc_revenue_tab(i)
,p_rep_line_mode => 'POSITIVE_ENTRY'
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
,p_delete_flag => 'N'
,x_msg_data => l_msg_data
,x_return_status => l_return_status
);
/* This API updates the required flags in the spread calc tmp table to process in the bulk mode */
PROCEDURE Upd_spread_calc_tmp(
p_budget_version_id IN NUMBER
,p_source_context IN VARCHAR2
,x_return_status OUT NOCOPY VARCHAR2
) IS
BEGIN
x_return_status := 'S';
print_msg('Number Of rowsUpdated['||g_plan_raId_tab.COUNT||']');
UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ PA_FP_SPREAD_CALC_TMP tmp -- bug 4873834
SET tmp.skip_record_flag = NVL(g_skip_record_tab(i),'N')
,tmp.processed_flag = NVL(g_process_skip_CstRevrec_tab(i),'N')
,tmp.REFRESH_RATES_FLAG = NVL(g_refresh_rates_tab(i),'N')
,tmp.REFRESH_CONV_RATES_FLAG = NVL(g_refresh_conv_rates_tab(i),'N')
,tmp.MASS_ADJUST_FLAG = NVL(g_mass_adjust_flag_tab(i),'N')
,tmp.G_WPRABL_CURRENCY_CODE = g_Wp_curCode_tab(i)
,tmp.RA_RATES_ONLY_CHANGE_FLAG = NVL(g_rtChanged_Ra_Flag_tab(i),'N')
,tmp.SYSTEM_REFERENCE_VAR2 = NVL(g_applyProg_refreshRts_tab(i),'N') /*Bug fix:4295967 */
WHERE tmp.budget_version_id = p_budget_version_id
AND tmp.resource_assignment_id = g_plan_raId_tab(i)
AND tmp.txn_currency_code = g_plan_txnCur_Tab(i)
AND ((p_source_context = 'BUDGET_LINE'
and tmp.start_date = g_line_Sdate_tab(i))
OR
p_source_context <> 'BUDGET_LINE'
);
UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ PA_FP_SPREAD_CALC_TMP tmp
SET tmp.mfc_cost_refresh_flag = 'Y'
,tmp.REFRESH_RATES_FLAG = 'Y'
WHERE tmp.budget_version_id = p_budget_version_id
AND tmp.resource_assignment_id = g_mfc_cost_refrsh_Raid_tab(i)
AND tmp.txn_currency_code = g_mfc_cost_refrsh_txnCur_tab(i) ;
UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ PA_FP_SPREAD_CALC_TMP tmp
SET tmp.cost_rate_override = decode(tmp.COST_RATE_G_MISS_NUM_FLAG,'Y',NULL
,NVL(g_sprd_costRt_Ovr_tab(i),tmp.cost_rate_override))
,tmp.burden_cost_rate_override = decode(tmp.burden_rate_g_miss_num_flag,'Y',NULL
,NVL(g_sprd_burdRt_Ovr_tab(i),tmp.burden_cost_rate_override))
,tmp.bill_rate_override = decode(tmp.bill_rate_g_miss_num_flag,'Y',NULL
,NVL(g_sprd_billRt_Ovr_tab(i),tmp.bill_rate_override))
WHERE tmp.resource_assignment_id = g_sprd_raId_tab(i)
AND tmp.txn_currency_code = g_sprd_txn_cur_tab(i)
AND NVL(g_sprd_qty_addl_tab(i),0) <> 0
AND ((g_source_context <> 'BUDGET_LINE')
OR
((g_source_context = 'BUDGET_LINE')
and tmp.start_date BETWEEN g_sprd_sDate_tab(i) AND g_sprd_eDate_tab(i)
))
AND (NVL(tmp.cost_rate_changed_flag,'N') = 'Y'
OR NVL(tmp.burden_rate_changed_flag,'N') = 'Y'
OR NVL(tmp.bill_rate_changed_flag,'N') = 'Y'
OR NVL(tmp.raw_cost_changed_flag,'N') = 'Y'
OR NVL(tmp.burden_cost_changed_flag,'N') = 'Y'
OR NVL(tmp.revenue_changed_flag,'N') = 'Y'
);
UPDATE /*+ INDEX(TMP PA_FP_SPREAD_CALC_TMP_N1) */ pa_fp_spread_calc_tmp tmp
SET tmp.cost_rate_override = decode(NVL(g_rtChanged_cstMisNumFlg_tab(i),'N'),'Y',NULL
,NVL(g_rtChanged_CostRt_Tab(i),tmp.cost_rate_override))
,tmp.burden_cost_rate_override = decode(NVL(g_rtChanged_bdMisNumFlag_tab(i),'N'),'Y',NULL
,NVL(g_rtChanged_BurdRt_tab(i),tmp.burden_cost_rate_override))
,tmp.bill_rate_override = decode(NVL(g_rtChanged_blMisNumFlag_tab(i),'N'),'Y',NULL
,NVL(g_rtChanged_billRt_tab(i),tmp.bill_rate_override))
WHERE tmp.resource_assignment_id = g_rtChanged_RaId_tab(i)
AND tmp.txn_currency_code = g_rtChanged_TxnCur_tab(i)
AND ((g_source_context <> 'BUDGET_LINE')
OR
((g_source_context = 'BUDGET_LINE')
and tmp.start_date BETWEEN g_rtChanged_sDate_tab(i) AND g_rtChanged_eDate_tab(i)
));
print_msg('Number of rows updated for RateChanges['||sql%rowcount||']');
* as we delete all the budget lines before the spread call. The purpose of thei API is to retain the override rates and
* currency conversion attributes of the periodic line level on the budget lines. updates the rollup tmp table with the override rates
*/
PROCEDURE Update_rollupTmp_OvrRates
( p_budget_version_id IN pa_budget_lines.budget_version_id%type
,p_calling_module IN varchar2
,p_generation_context IN varchar2 default 'SPREAD'
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
) IS
CURSOR periodDetails IS
SELECT gsb.period_set_name period_set_name
,gsb.accounted_period_type accounted_period_type
,pia.pa_period_type pa_period_type
,pbv.version_type version_type
,decode(pbv.version_type,
'COST',ppfo.cost_time_phased_code,
'REVENUE',ppfo.revenue_time_phased_code,
ppfo.all_time_phased_code) time_phase_code
FROM gl_sets_of_books gsb
,pa_implementations_all pia
,pa_projects_all ppa
,pa_budget_versions pbv
,pa_proj_fp_options ppfo
WHERE ppa.project_id = pbv.project_id
AND pbv.budget_version_id = ppfo.fin_plan_version_id
/* MOAC Changes: AND nvl(ppa.org_id,-99) = nvl(pia.org_id,-99) */
AND ppa.org_id = pia.org_id
AND gsb.set_of_books_id = pia.set_of_books_id
AND pbv.budget_version_id = p_budget_version_id;
SELECT tmp.rowid
,tmp.resource_assignment_id
,tmp.txn_currency_code
,tmp.start_date
,tmp.end_date
,fptmp.period_name
,decode(perdRec.version_type ,'ALL', NVL(fptmp.raw_cost_rate,tmp.rw_cost_rate_override)
,'COST',NVL(fptmp.raw_cost_rate,tmp.rw_cost_rate_override)
,decode(p_generation_context,'REVENUE_MARKUP' /* bug fix:4213824 added decode to get the cost rt */
,NVL(fptmp.raw_cost_rate,tmp.rw_cost_rate_override),tmp.rw_cost_rate_override)
/* bgfix:4250089 having this extra default executes always when version type is revenue
,tmp.rw_cost_rate_override */
) cost_rate_override
,decode(perdRec.version_type ,'ALL', NVL(fptmp.burdened_cost_rate,tmp.burden_cost_rate_override)
,'COST',NVL(fptmp.burdened_cost_rate,tmp.burden_cost_rate_override)
,decode(p_generation_context,'REVENUE_MARKUP' /* bug fix:4213824 added decode to get the bdRt */
,NVL(fptmp.burdened_cost_rate,tmp.burden_cost_rate_override),tmp.burden_cost_rate_override)
/* bug fix:4250089 ,tmp.burden_cost_rate_override */
) burden_rate_override
,decode(perdRec.version_type ,'ALL', NVL(fptmp.revenue_bill_rate,tmp.bill_rate_override)
,'REVENUE',NVL(fptmp.revenue_bill_rate,tmp.bill_rate_override)
,tmp.bill_rate_override) bill_rate_override
,tmp.PROJECT_COST_RATE_TYPE
,tmp.PROJECT_COST_EXCHANGE_RATE
,tmp.PROJECT_COST_RATE_DATE_TYPE
,tmp.PROJECT_COST_RATE_DATE
,tmp.PROJECT_REV_RATE_TYPE
,tmp.PROJECT_REV_EXCHANGE_RATE
,tmp.PROJECT_REV_RATE_DATE_TYPE
,tmp.PROJECT_REV_RATE_DATE
,tmp.PROJFUNC_COST_RATE_TYPE
,tmp.PROJFUNC_COST_EXCHANGE_RATE
,tmp.PROJFUNC_COST_RATE_DATE_TYPE
,tmp.PROJFUNC_COST_RATE_DATE
,tmp.PROJFUNC_REV_RATE_TYPE
,tmp.PROJFUNC_REV_EXCHANGE_RATE
,tmp.PROJFUNC_REV_RATE_DATE_TYPE
,tmp.PROJFUNC_REV_RATE_DATE
/* Bug fix:4568011: set a flag based on the bill rate override passed from generation process
* to indicate that revenue should be calculated based on markup or not
* If bill rate override is passed, then do not calculate markup
*/
,decode(p_generation_context,'REVENUE_MARKUP',
decode(fptmp.revenue_bill_rate,NULL,'Y','N'),'Y') markup_calculation_flag
FROM pa_fp_rollup_tmp tmp
,pa_fp_gen_rate_tmp fptmp
,pa_resource_assignments ra
,pa_fp_spread_calc_tmp caltmp
WHERE caltmp.budget_version_id = p_budget_version_id
AND caltmp.resource_assignment_id = ra.resource_assignment_id
AND nvl(caltmp.skip_record_flag,'N') <> 'Y'
AND tmp.resource_assignment_id = ra.resource_assignment_id
AND tmp.txn_currency_code = caltmp.txn_currency_code
AND NVL(tmp.system_reference5,'N') = 'N' /* donot pick already processed lines */
AND fptmp.target_res_asg_id = tmp.resource_assignment_id
AND fptmp.txn_currency_code = tmp.txn_currency_code
AND NVL(tmp.period_name,'SUMMARY') = nvl(fptmp.period_name,NVL(tmp.period_name,'SUMMARY'))
/* Bug fix: 4216423 markup should be calculated for both rate and non-rate base resource
AND ((p_generation_context = 'SPREAD')
OR
( p_generation_context = 'REVENUE_MARKUP'
AND NVL(ra.rate_based_flag,'N') = 'N'
))
*/
;
SELECT tmp.rowid
,fptmp.target_res_asg_id RaId
,fptmp.txn_currency_code CurCode
,tmp.start_date
,tmp.end_date
,fptmp.period_name period_name
,fptmp.raw_cost_rate cost_rate_override
,fptmp.burdened_cost_rate burden_rate_override
,fptmp.revenue_bill_rate bill_rate_override
,decode(p_generation_context,'REVENUE_MARKUP',
decode(fptmp.revenue_bill_rate,NULL,'Y','N'),'Y') markup_calculation_flag
FROM pa_fp_rollup_tmp tmp
,pa_fp_gen_rate_tmp fptmp
,pa_resource_assignments ra
,pa_fp_spread_calc_tmp caltmp
WHERE caltmp.budget_version_id = p_budget_version_id
AND caltmp.resource_assignment_id = ra.resource_assignment_id
AND nvl(caltmp.skip_record_flag,'N') <> 'Y'
AND tmp.resource_assignment_id = ra.resource_assignment_id
AND tmp.txn_currency_code = caltmp.txn_currency_code
AND NVL(tmp.system_reference5,'N') = 'N' /* donot pick already processed lines */
AND fptmp.target_res_asg_id = tmp.resource_assignment_id
AND fptmp.txn_currency_code = tmp.txn_currency_code
AND NVL(tmp.period_name,'SUMMARY') = nvl(fptmp.period_name,NVL(tmp.period_name,'SUMMARY'))
/* Bug fix: 4216423 markup should be calculated for both rate and non-rate base resource
AND ((p_generation_context = 'SPREAD')
OR
( p_generation_context = 'REVENUE_MARKUP'
AND NVL(ra.rate_based_flag,'N') = 'N'
))
*/
;
g_stage :='Update_rollupTmp_OvrRates:100';
g_stage :='Update_rollupTmp_OvrRates:101';
UPDATE pa_fp_rollup_tmp tmp
SET tmp.period_name = (select gp.period_name
from gl_periods gp
where gp.period_set_name = perdRec.period_set_name
and gp.adjustment_period_flag = 'N'
and gp.period_type = decode(perdRec.time_phase_code,'G',perdRec.accounted_period_type
,'P',perdRec.pa_period_type,gp.period_type)
and tmp.start_date between gp.start_date and gp.end_date
and rownum = 1
)
WHERE tmp.period_name is NULL
AND tmp.budget_version_id = p_budget_version_id
AND NVL(tmp.system_reference5,'N') = 'N'
;
g_stage :='Update_rollupTmp_OvrRates:102';
UPDATE pa_fp_rollup_tmp tmp
SET tmp.period_name = NULL
WHERE tmp.period_name is NOT NULL
AND NVL(tmp.system_reference5,'N') = 'N'
AND tmp.budget_version_id = p_budget_version_id;
print_msg('Number of rows updated['||sql%rowcount||']');
l_rowid_tab.delete;
l_resource_assignment_id_tab.delete;
l_txn_currency_code_tab.delete;
l_start_date_tab.delete;
l_end_date_tab.delete;
l_period_name_tab.delete;
l_cost_rate_override_tab.delete;
l_burden_rate_override_tab.delete;
l_bill_rate_override_tab.delete;
l_PROJECT_COST_RATE_TYPE_tab.delete;
l_PROJECT_COST_EXG_RATE_tab.delete;
l_PROJECT_COST_DATE_TYPE_tab.delete;
l_PROJECT_COST_RATE_DATE_tab.delete;
l_PROJECT_REV_RATE_TYPE_tab.delete;
l_PROJECT_REV_EXG_RATE_tab.delete;
l_PROJECT_REV_DATE_TYPE_tab.delete;
l_PROJECT_REV_RATE_DATE_tab.delete;
l_PROJFUNC_COST_RATE_TYPE_tab.delete;
l_PROJFUNC_COST_EXG_RATE_tab.delete;
l_PROJFUNC_COST_DATE_TYPE_tab.delete;
l_PROJFUNC_COST_RATE_DATE_tab.delete;
l_PROJFUNC_REV_RATE_TYPE_tab.delete;
l_PROJFUNC_REV_EXG_RATE_tab.delete;
l_PROJFUNC_REV_DATE_TYPE_tab.delete;
l_PROJFUNC_REV_RATE_DATE_tab.delete;
l_markup_calculation_flag_tab.delete;
g_stage :='Update_rollupTmp_OvrRates:103';
g_stage :='Update_rollupTmp_OvrRates:104';
UPDATE pa_fp_rollup_tmp tmp
SET tmp.rw_cost_rate_override = l_cost_rate_override_tab(i)
,tmp.burden_cost_rate_override = l_burden_rate_override_tab(i)
,tmp.bill_rate_override = l_bill_rate_override_tab(i)
,tmp.PROJECT_COST_RATE_TYPE = nvl(l_PROJECT_COST_RATE_TYPE_tab(i),tmp.PROJECT_COST_RATE_TYPE)
,tmp.PROJECT_COST_EXCHANGE_RATE = nvl(l_PROJECT_COST_EXG_RATE_tab(i),tmp.PROJECT_COST_EXCHANGE_RATE)
,tmp.PROJECT_COST_RATE_DATE_TYPE = nvl(l_PROJECT_COST_DATE_TYPE_tab(i),tmp.PROJECT_COST_RATE_DATE_TYPE)
,tmp.PROJECT_COST_RATE_DATE = nvl(l_PROJECT_COST_RATE_DATE_tab(i),tmp.PROJECT_COST_RATE_DATE)
,tmp.PROJECT_REV_RATE_TYPE = nvl(l_PROJECT_REV_RATE_TYPE_tab(i),tmp.PROJECT_REV_RATE_TYPE)
,tmp.PROJECT_REV_EXCHANGE_RATE = nvl(l_PROJECT_REV_EXG_RATE_tab(i),tmp.PROJECT_REV_EXCHANGE_RATE)
,tmp.PROJECT_REV_RATE_DATE_TYPE = nvl(l_PROJECT_REV_DATE_TYPE_tab(i),tmp.PROJECT_REV_RATE_DATE_TYPE)
,tmp.PROJECT_REV_RATE_DATE = nvl(l_PROJECT_REV_RATE_DATE_tab(i),tmp.PROJECT_REV_RATE_DATE)
,tmp.PROJFUNC_COST_RATE_TYPE = nvl(l_PROJFUNC_COST_RATE_TYPE_tab(i),tmp.PROJFUNC_COST_RATE_TYPE)
,tmp.PROJFUNC_COST_EXCHANGE_RATE = nvl(l_PROJFUNC_COST_EXG_RATE_tab(i),tmp.PROJFUNC_COST_EXCHANGE_RATE)
,tmp.PROJFUNC_COST_RATE_DATE_TYPE = nvl(l_PROJFUNC_COST_DATE_TYPE_tab(i),tmp.PROJFUNC_COST_RATE_DATE_TYPE)
,tmp.PROJFUNC_COST_RATE_DATE = nvl(l_PROJFUNC_COST_RATE_DATE_tab(i),tmp.PROJFUNC_COST_RATE_DATE)
,tmp.PROJFUNC_REV_RATE_TYPE = nvl(l_PROJFUNC_REV_RATE_TYPE_tab(i),tmp.PROJFUNC_REV_RATE_TYPE)
,tmp.PROJFUNC_REV_EXCHANGE_RATE = nvl(l_PROJFUNC_REV_EXG_RATE_tab(i),tmp.PROJFUNC_REV_EXCHANGE_RATE)
,tmp.PROJFUNC_REV_RATE_DATE_TYPE =nvl(l_PROJFUNC_REV_DATE_TYPE_tab(i),tmp.PROJFUNC_REV_RATE_DATE_TYPE)
,tmp.PROJFUNC_REV_RATE_DATE = nvl(l_PROJFUNC_REV_RATE_DATE_tab(i),tmp.PROJFUNC_REV_RATE_DATE)
,tmp.SYSTEM_REFERENCE6 = nvl(l_markup_calculation_flag_tab(i),'Y')
WHERE tmp.rowid = l_rowid_tab(i);
END Update_rollupTmp_OvrRates;
This API is called when source context is BUDGET_LINE and delete_budget_lines flag is Y
Before deleting budget lines, this api calls Pji reporting apis to update the balances
and then it deletes the budget lines only if there are no actuals exists.
and the budget start date is greater than ETC start date
*/
PROCEDURE delete_budget_lines
( p_budget_version_id IN pa_budget_lines.budget_version_id%type
,p_resource_assignment_id IN pa_resource_assignments.resource_assignment_id%TYPE
,p_txn_currency_code IN pa_budget_lines.txn_currency_code%TYPE
,p_line_start_date IN pa_budget_lines.start_date%TYPE
,p_line_end_date IN pa_budget_lines.end_date%TYPE
,p_source_context IN varchar2
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2
,x_num_rowsdeleted OUT NOCOPY Number
) IS
l_debug_mode VARCHAR2(30);
CURSOR get_delete_bl_id IS
SELECT bl.budget_line_id
,bl.resource_assignment_id
,bl.txn_currency_code
,bl.start_date
,bl.end_date
,bl.period_name
,bl.quantity
,bl.txn_raw_cost
,bl.txn_burdened_cost
,bl.txn_revenue
,bl.project_raw_cost
,bl.project_burdened_cost
,bl.project_revenue
,bl.raw_cost projfunc_raw_cost
,bl.burdened_cost projfunc_burdened_cost
,bl.revenue projfunc_revenue
,bl.project_currency_code
,bl.projfunc_currency_code
,bl.cost_rejection_code
,bl.revenue_rejection_code
,bl.burden_rejection_code
,bl.pfc_cur_conv_rejection_code
,bl.pc_cur_conv_rejection_code
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = p_resource_assignment_id
AND bl.txn_currency_code = p_txn_currency_code
AND ((p_line_start_date is NULL AND p_line_end_date is NULL )
OR (p_line_start_date is NOT NULL AND p_line_end_date is NOT NULL
and bl.start_date BETWEEN p_line_start_date AND p_line_end_date
)
)
/* Bug 4344112 -- Actuals can be stamped as null or zero, in both cases
we need to delete the lines. In case of this bug actuals are getting
stamped as ZERO so we are not deleting this line, the spread logic
breaks becoz of the zero in actuals.
Lines with zero actuals can be deleted as lines with NULL actuals. */
/****** AND (bl.init_quantity IS NULL AND
bl.txn_init_raw_cost IS NULL AND
bl.txn_init_burdened_cost IS NULL AND
bl.txn_init_revenue IS NULL); ******/
/* This cursor picks all the budget lines where ETC start date falls and updates the
* plan = Actuals so that remaining ETC will be spread forward
*/
CURSOR cur_UpdBlWithZeroEtc IS
SELECT bl.budget_line_id
,bl.resource_assignment_id
,bl.txn_currency_code
,bl.start_date
,bl.end_date
,bl.period_name
,bl.quantity
,bl.txn_raw_cost
,bl.txn_burdened_cost
,bl.txn_revenue
,bl.project_raw_cost
,bl.project_burdened_cost
,bl.project_revenue
,bl.raw_cost projfunc_raw_cost
,bl.burdened_cost projfunc_burdened_cost
,bl.revenue projfunc_revenue
,bl.project_currency_code
,bl.projfunc_currency_code
,bl.cost_rejection_code
,bl.revenue_rejection_code
,bl.burden_rejection_code
,bl.pfc_cur_conv_rejection_code
,bl.pc_cur_conv_rejection_code
,bl.init_quantity
,bl.txn_init_raw_cost
,bl.txn_init_burdened_cost
,bl.txn_init_revenue
,bl.project_init_raw_cost
,bl.project_init_burdened_cost
,bl.project_init_revenue
,bl.init_raw_cost
,bl.init_burdened_cost
,bl.init_revenue
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = p_resource_assignment_id
AND bl.txn_currency_code = p_txn_currency_code
AND ((p_line_start_date is NULL AND p_line_end_date is NULL )
OR (p_line_start_date is NOT NULL AND p_line_end_date is NOT NULL
and bl.start_date BETWEEN p_line_start_date AND p_line_end_date
)
)
AND g_spread_from_date is NOT NULL
AND ((bl.start_date >= g_spread_from_date )
/* bug fix: 4139354 and (g_spread_from_date BETWEEN bl.start_date AND bl.end_date)*/
OR (g_spread_from_date BETWEEN bl.start_date AND bl.end_date)
)
AND ( NVL(bl.init_quantity,0) <> 0 OR
NVL(bl.txn_init_raw_cost ,0) <> 0 OR
NVL(bl.txn_init_burdened_cost ,0) <> 0 OR
NVL(bl.txn_init_revenue ,0) <> 0
);
g_stage := 'delete_budget_lines:100';
pa_debug.init_err_stack('PA_FP_CALC_PLAN_PKG.delete_budget_lines');
print_msg('Entered PA_FP_CALC_PLAN_PKG.delete_budget_lines ResAssId['||p_resource_assignment_id||']spreadDate['||g_spread_from_date||']');
OPEN get_delete_bl_id ;
FETCH get_delete_bl_id BULK COLLECT INTO
l_budget_Line_id_tab
,l_resource_assignment_id_tab
,l_txn_currency_code_tab
,l_start_date_tab
,l_end_date_tab
,l_period_name_tab
,l_quantity_tab
,l_txn_raw_cost_tab
,l_txn_burdened_cost_tab
,l_txn_revenue_tab
,l_project_raw_cost_tab
,l_project_burdened_cost_tab
,l_project_revenue_tab
,l_projfunc_raw_cost_tab
,l_projfunc_burdened_cost_tab
,l_projfunc_revenue_tab
,l_project_curr_code_tab
,l_projfunc_curr_code_tab
,l_cost_rejection_code_tab
,l_revenue_rejection_code_tab
,l_burden_rejection_code_tab
,l_pfc_cur_conv_rej_code_tab
,l_pc_cur_conv_rej_code_tab;
CLOSE get_delete_bl_id;
g_stage := 'delete_budget_lines:101';
/* update the reporting lines before deleteing the budget lines */
Add_Toreporting_Tabls
(p_calling_module => 'CALCULATE_API'
,p_activity_code => 'UPDATE'
,p_budget_version_id => p_budget_version_id
,p_budget_line_id => l_budget_Line_id_tab(i)
,p_resource_assignment_id => l_resource_assignment_id_tab(i)
,p_start_date => l_start_date_tab(i)
,p_end_date => l_end_date_tab(i)
,p_period_name => l_period_name_tab(i)
,p_txn_currency_code => l_txn_currency_code_tab(i)
,p_quantity => l_quantity_tab(i) * -1
,p_txn_raw_cost => l_txn_raw_cost_tab(i) * -1
,p_txn_burdened_cost => l_txn_burdened_cost_tab(i) * -1
,p_txn_revenue => l_txn_revenue_tab(i) * -1
,p_project_currency_code => l_project_curr_code_tab(i)
,p_project_raw_cost => l_project_raw_cost_tab(i) * -1
,p_project_burdened_cost => l_project_burdened_cost_tab(i) * -1
,p_project_revenue => l_project_revenue_tab(i) * -1
,p_projfunc_currency_code => l_projfunc_curr_code_tab(i)
,p_projfunc_raw_cost => l_projfunc_raw_cost_tab(i) * -1
,p_projfunc_burdened_cost => l_projfunc_burdened_cost_tab(i) * -1
,p_projfunc_revenue => l_projfunc_revenue_tab(i) * -1
,p_rep_line_mode => 'REVERSAL'
,x_msg_data => x_msg_data
,x_return_status => l_return_status
);
/* add the deleted budget lines to mrc plsql tabs */
Populate_MRC_plsqlTabs
(p_calling_module => 'CALCULATE_API'
,p_budget_version_id => p_budget_version_id
,p_budget_line_id => l_budget_Line_id_tab(i)
,p_resource_assignment_id => l_resource_assignment_id_tab(i)
,p_start_date => l_start_date_tab(i)
,p_end_date => l_end_date_tab(i)
,p_period_name => l_period_name_tab(i)
,p_txn_currency_code => l_txn_currency_code_tab(i)
,p_quantity => l_quantity_tab(i)
,p_txn_raw_cost => l_txn_raw_cost_tab(i)
,p_txn_burdened_cost => l_txn_burdened_cost_tab(i)
,p_txn_revenue => l_txn_revenue_tab(i)
,p_project_currency_code => l_project_curr_code_tab(i)
,p_project_raw_cost => l_project_raw_cost_tab(i)
,p_project_burdened_cost => l_project_burdened_cost_tab(i)
,p_project_revenue => l_project_revenue_tab(i)
,p_projfunc_currency_code => l_projfunc_curr_code_tab(i)
,p_projfunc_raw_cost => l_projfunc_raw_cost_tab(i)
,p_projfunc_burdened_cost => l_projfunc_burdened_cost_tab(i)
,p_projfunc_revenue => l_projfunc_revenue_tab(i)
,p_delete_flag => 'Y'
,x_msg_data => x_msg_data
,x_return_status => l_return_status
);
g_stage := 'delete_budget_lines:102';
DELETE FROM pa_budget_lines bl
WHERE bl.budget_line_id = l_budget_Line_id_tab(i) ;
x_num_rowsdeleted := sql%rowcount;
l_budget_Line_id_tab.delete;
l_resource_assignment_id_tab.delete;
l_txn_currency_code_tab.delete;
l_start_date_tab.delete;
l_end_date_tab.delete;
l_period_name_tab.delete;
l_quantity_tab.delete;
l_txn_raw_cost_tab.delete;
l_txn_burdened_cost_tab.delete;
l_txn_revenue_tab.delete;
l_project_raw_cost_tab.delete;
l_project_burdened_cost_tab.delete;
l_project_revenue_tab.delete;
l_projfunc_raw_cost_tab.delete;
l_projfunc_burdened_cost_tab.delete;
l_projfunc_revenue_tab.delete;
l_project_curr_code_tab.delete;
l_projfunc_curr_code_tab.delete;
l_cost_rejection_code_tab.delete;
l_revenue_rejection_code_tab.delete;
l_burden_rejection_code_tab.delete;
l_pfc_cur_conv_rej_code_tab.delete;
l_pc_cur_conv_rej_code_tab.delete;
l_init_quantity_tab.delete;
l_txn_init_raw_cost_tab.delete;
l_txn_init_burdened_cost_tab.delete;
l_txn_init_revenue_tab.delete;
l_pj_init_raw_cost_tab.delete;
l_pj_init_burdened_cost_tab.delete;
l_pj_init_revenue_tab.delete;
l_pjf_init_raw_cost_tab.delete;
l_pjf_init_burdened_cost_tab.delete;
l_pjf_init_revenue_tab.delete;
g_stage := 'delete_budget_lines:103';
g_stage := 'delete_budget_lines:104';
/* update the reporting lines before deleteing the budget lines */
Add_Toreporting_Tabls
(p_calling_module => 'CALCULATE_API'
,p_activity_code => 'UPDATE'
,p_budget_version_id => p_budget_version_id
,p_budget_line_id => l_budget_Line_id_tab(i)
,p_resource_assignment_id => l_resource_assignment_id_tab(i)
,p_start_date => l_start_date_tab(i)
,p_end_date => l_end_date_tab(i)
,p_period_name => l_period_name_tab(i)
,p_txn_currency_code => l_txn_currency_code_tab(i)
,p_quantity => l_quantity_tab(i) * -1
,p_txn_raw_cost => l_txn_raw_cost_tab(i) * -1
,p_txn_burdened_cost => l_txn_burdened_cost_tab(i) * -1
,p_txn_revenue => l_txn_revenue_tab(i) * -1
,p_project_currency_code => l_project_curr_code_tab(i)
,p_project_raw_cost => l_project_raw_cost_tab(i) * -1
,p_project_burdened_cost => l_project_burdened_cost_tab(i) * -1
,p_project_revenue => l_project_revenue_tab(i) * -1
,p_projfunc_currency_code => l_projfunc_curr_code_tab(i)
,p_projfunc_raw_cost => l_projfunc_raw_cost_tab(i) * -1
,p_projfunc_burdened_cost => l_projfunc_burdened_cost_tab(i) * -1
,p_projfunc_revenue => l_projfunc_revenue_tab(i) * -1
,p_rep_line_mode => 'REVERSAL'
,x_msg_data => x_msg_data
,x_return_status => l_return_status
);
g_stage := 'delete_budget_lines:105';
UPDATE pa_budget_lines bl
SET bl.quantity = l_init_quantity_tab(i)
,bl.txn_raw_cost = l_txn_init_raw_cost_tab(i)
,bl.txn_burdened_cost = l_txn_init_burdened_cost_tab(i)
,bl.txn_revenue = l_txn_init_revenue_tab(i)
,bl.project_raw_cost = l_pj_init_raw_cost_tab(i)
,bl.project_burdened_cost = l_pj_init_burdened_cost_tab(i)
,bl.project_revenue = l_pj_init_revenue_tab(i)
,bl.raw_cost = l_pjf_init_raw_cost_tab(i)
,bl.burdened_cost = l_pjf_init_burdened_cost_tab(i)
,bl.revenue = l_pjf_init_revenue_tab(i)
WHERE bl.budget_line_id = l_budget_Line_id_tab(i);
/* Now pass +ve budget line values (Updated values) to PJI reporting api */
l_stage := 7840;
l_budget_Line_id_tab.delete;
l_resource_assignment_id_tab.delete;
l_txn_currency_code_tab.delete;
l_start_date_tab.delete;
l_end_date_tab.delete;
l_period_name_tab.delete;
l_quantity_tab.delete;
l_txn_raw_cost_tab.delete;
l_txn_burdened_cost_tab.delete;
l_txn_revenue_tab.delete;
l_project_raw_cost_tab.delete;
l_project_burdened_cost_tab.delete;
l_project_revenue_tab.delete;
l_projfunc_raw_cost_tab.delete;
l_projfunc_burdened_cost_tab.delete;
l_projfunc_revenue_tab.delete;
l_project_curr_code_tab.delete;
l_projfunc_curr_code_tab.delete;
l_cost_rejection_code_tab.delete;
l_revenue_rejection_code_tab.delete;
l_burden_rejection_code_tab.delete;
l_pfc_cur_conv_rej_code_tab.delete;
l_pc_cur_conv_rej_code_tab.delete;
l_init_quantity_tab.delete;
l_txn_init_raw_cost_tab.delete;
l_txn_init_burdened_cost_tab.delete;
l_txn_init_revenue_tab.delete;
l_pj_init_raw_cost_tab.delete;
l_pj_init_burdened_cost_tab.delete;
l_pj_init_revenue_tab.delete;
l_pjf_init_raw_cost_tab.delete;
l_pjf_init_burdened_cost_tab.delete;
l_pjf_init_revenue_tab.delete;
g_stage := 'delete_budget_lines:106';
g_stage := 'delete_budget_lines:107';
/* update the reporting lines after updating the budget lines */
Add_Toreporting_Tabls
(p_calling_module => 'CALCULATE_API'
,p_activity_code => 'UPDATE'
,p_budget_version_id => p_budget_version_id
,p_budget_line_id => l_budget_Line_id_tab(i)
,p_resource_assignment_id => l_resource_assignment_id_tab(i)
,p_start_date => l_start_date_tab(i)
,p_end_date => l_end_date_tab(i)
,p_period_name => l_period_name_tab(i)
,p_txn_currency_code => l_txn_currency_code_tab(i)
,p_quantity => l_quantity_tab(i)
,p_txn_raw_cost => l_txn_raw_cost_tab(i)
,p_txn_burdened_cost => l_txn_burdened_cost_tab(i)
,p_txn_revenue => l_txn_revenue_tab(i)
,p_project_currency_code => l_project_curr_code_tab(i)
,p_project_raw_cost => l_project_raw_cost_tab(i)
,p_project_burdened_cost => l_project_burdened_cost_tab(i)
,p_project_revenue => l_project_revenue_tab(i)
,p_projfunc_currency_code => l_projfunc_curr_code_tab(i)
,p_projfunc_raw_cost => l_projfunc_raw_cost_tab(i)
,p_projfunc_burdened_cost => l_projfunc_burdened_cost_tab(i)
,p_projfunc_revenue => l_projfunc_revenue_tab(i)
,p_rep_line_mode => 'POSITIVE_ENTRY'
,x_msg_data => x_msg_data
,x_return_status => l_return_status
);
print_msg('Leaving delete_budget_lines');
,p_procedure_name => 'delete_budget_lines' );
END delete_budget_lines;
for i in (select * from pa_fp_spread_calc_tmp ) LOOP
print_msg('IN params ResId['||i.resource_assignment_id||']TxnCur['||i.txn_currency_code||']RefrFlag['||i.refresh_rates_flag||']');
INSERT INTO pa_fp_rollup_tmp (
budget_version_id
,resource_assignment_id
,start_date
,end_date
,period_name
,quantity
,projfunc_raw_cost
,projfunc_burdened_cost
,projfunc_revenue
,cost_rejection_code
,revenue_rejection_code
,burden_rejection_code
,projfunc_currency_code
,projfunc_cost_rate_type
,projfunc_cost_exchange_rate
,projfunc_cost_rate_date_type
,projfunc_cost_rate_date
,projfunc_rev_rate_type
,projfunc_rev_exchange_rate
,projfunc_rev_rate_date_type
,projfunc_rev_rate_date
,project_currency_code
,project_cost_rate_type
,project_cost_exchange_rate
,project_cost_rate_date_type
,project_cost_rate_date
,project_raw_cost
,project_burdened_cost
,project_rev_rate_type
,project_rev_exchange_rate
,project_rev_rate_date_type
,project_rev_rate_date
,project_revenue
,txn_currency_code
,txn_raw_cost
,txn_burdened_cost
,txn_revenue
,budget_line_id
,init_quantity
,txn_init_raw_cost
,txn_init_burdened_cost
,txn_init_revenue
,bill_markup_percentage
,bill_rate
,cost_rate
,rw_cost_rate_override
,burden_cost_rate
,bill_rate_override
,burden_cost_rate_override
,cost_ind_compiled_set_id
,init_raw_cost
,init_burdened_cost
,init_revenue
,project_init_raw_cost
,project_init_burdened_cost
,project_init_revenue
,billable_flag
,rate_based_flag
,system_reference4 -- version all revenue only entered
)
( SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
bl.budget_version_id
,bl.resource_assignment_id
,bl.start_date
,bl.end_date
,bl.period_name
,bl.quantity
/* Bug Fix 4332086
When ever currency is overridden in the workplan flow the exchange rates of old currency
are used to derive the PFC amounts.
This is happening due to the following piece of code.
In the following code we are storing the pa_budget_lines attributes in pa_fp_rollup_tmp
and then we are using the same in the later part of the code.
This is resulting in the bug.
As a fix now we added another decode condition to see if the currency is iverridden.
If it is then we nullify all the currency related attributes, so they can be retrieved
for the latest currency instead of caching the old currency's attrs.
*/
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(ra.rate_based_flag,'N',bl.raw_cost
,decode(tmp.refresh_rates_flag,'Y',decode(bl.quantity,null,bl.raw_cost,NULL)
,'C',decode(bl.quantity,null,bl.raw_cost,NULL)
,'R',bl.raw_cost
,bl.raw_cost
)
),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(ra.rate_based_flag,'N',bl.burdened_cost
,decode(tmp.refresh_rates_flag,'Y',decode(bl.quantity,null,bl.burdened_cost,NULL)
,'C',decode(bl.quantity,null,bl.burdened_cost,NULL)
,'R',bl.burdened_cost
,bl.burdened_cost
)
),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(ra.rate_based_flag,'N',bl.revenue
,decode(tmp.refresh_rates_flag,'Y',decode(bl.quantity,null,bl.revenue,NULL)
,'C',bl.revenue
,'R',decode(bl.quantity,null,bl.revenue,NULL)
,bl.revenue
)
),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(tmp.refresh_rates_flag,'Y' ,decode(bl.quantity,null,bl.cost_rejection_code,NULL)
,'C',decode(bl.quantity,null,bl.cost_rejection_code,NULL)
,'R',bl.cost_rejection_code
,bl.cost_rejection_code
),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(tmp.refresh_rates_flag,'Y',decode(bl.quantity,null,bl.revenue_rejection_code,NULL)
,'C',bl.revenue_rejection_code
,'R',decode(bl.quantity,null,bl.revenue_rejection_code,NULL)
,bl.revenue_rejection_code
),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(tmp.refresh_rates_flag,'Y' ,decode(bl.quantity,null,bl.burden_rejection_code,NULL)
,'C',decode(bl.quantity,null,bl.burden_rejection_code,NULL)
,'R',bl.burden_rejection_code
,bl.burden_rejection_code
),NULL)
,bl.projfunc_currency_code
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(tmp.refresh_conv_rates_flag,'Y'
,decode(g_source_context,'BUDGET_LINE',bl.projfunc_cost_rate_type,NULL),bl.projfunc_cost_rate_type),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(tmp.refresh_conv_rates_flag,'Y'
,decode(g_source_context,'BUDGET_LINE',bl.projfunc_cost_exchange_rate,NULL),bl.projfunc_cost_exchange_rate),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(tmp.refresh_conv_rates_flag,'Y'
,decode(g_source_context,'BUDGET_LINE',bl.projfunc_cost_rate_date_type,NULL),bl.projfunc_cost_rate_date_type),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(tmp.refresh_conv_rates_flag,'Y'
,decode(g_source_context,'BUDGET_LINE',bl.projfunc_cost_rate_date,NULL),bl.projfunc_cost_rate_date),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(tmp.refresh_conv_rates_flag,'Y'
,decode(g_source_context,'BUDGET_LINE',bl.projfunc_rev_rate_type,NULL),bl.projfunc_rev_rate_type),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(tmp.refresh_conv_rates_flag,'Y'
,decode(g_source_context,'BUDGET_LINE',bl.projfunc_rev_exchange_rate,NULL),bl.projfunc_rev_exchange_rate),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(tmp.refresh_conv_rates_flag,'Y'
,decode(g_source_context,'BUDGET_LINE',bl.projfunc_rev_rate_date_type,NULL),bl.projfunc_rev_rate_date_type),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(tmp.refresh_conv_rates_flag,'Y'
,decode(g_source_context,'BUDGET_LINE',bl.projfunc_rev_rate_date,NULL),bl.projfunc_rev_rate_date),NULL)
,bl.project_currency_code
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(tmp.refresh_conv_rates_flag,'Y'
,decode(g_source_context,'BUDGET_LINE',bl.project_cost_rate_type,NULL),bl.project_cost_rate_type),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(tmp.refresh_conv_rates_flag,'Y'
,decode(g_source_context,'BUDGET_LINE',bl.project_cost_exchange_rate,NULL),bl.project_cost_exchange_rate),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(tmp.refresh_conv_rates_flag,'Y'
,decode(g_source_context,'BUDGET_LINE',bl.project_cost_rate_date_type,NULL),bl.project_cost_rate_date_type),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(tmp.refresh_conv_rates_flag,'Y'
,decode(g_source_context,'BUDGET_LINE',bl.project_cost_rate_date,NULL),bl.project_cost_rate_date),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(ra.rate_based_flag,'N',bl.project_raw_cost
,decode(tmp.refresh_rates_flag,'Y',decode(bl.quantity,null,bl.project_raw_cost,NULL)
,'C',decode(bl.quantity,null,bl.project_raw_cost,NULL)
,'R',bl.project_raw_cost
,bl.project_raw_cost
)
),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(ra.rate_based_flag,'N',bl.project_burdened_cost
,decode(tmp.refresh_rates_flag,'Y',decode(bl.quantity,null,bl.project_burdened_cost,NULL)
,'C',decode(bl.quantity,null,bl.project_burdened_cost,NULL)
,'R',bl.project_burdened_cost
,bl.project_burdened_cost
)
),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(tmp.refresh_conv_rates_flag,'Y'
,decode(g_source_context,'BUDGET_LINE',bl.project_rev_rate_type,NULL),bl.project_rev_rate_type),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(tmp.refresh_conv_rates_flag,'Y'
,decode(g_source_context,'BUDGET_LINE',bl.project_rev_exchange_rate,NULL),bl.project_rev_exchange_rate),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(tmp.refresh_conv_rates_flag,'Y'
,decode(g_source_context,'BUDGET_LINE',bl.project_rev_rate_date_type,NULL),bl.project_rev_rate_date_type),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(tmp.refresh_conv_rates_flag,'Y'
,decode(g_source_context,'BUDGET_LINE',bl.project_rev_rate_date,NULL),bl.project_rev_rate_date),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(ra.rate_based_flag,'N',bl.project_revenue
,decode(tmp.refresh_rates_flag,'Y',decode(bl.quantity,null,bl.project_revenue,NULL)
,'C',bl.project_revenue
,'R',decode(bl.quantity,null,bl.project_revenue,NULL)
,bl.project_revenue
)
),NULL)
,bl.txn_currency_code
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(ra.rate_based_flag,'N',bl.txn_raw_cost
,decode(tmp.refresh_rates_flag,'Y',decode(bl.quantity,null,bl.txn_raw_cost,NULL)
,'C',decode(bl.quantity,null,bl.txn_raw_cost,NULL)
,'R',bl.txn_raw_cost
,bl.txn_raw_cost
)
),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(ra.rate_based_flag,'N',bl.txn_burdened_cost
,decode(tmp.refresh_rates_flag,'Y',decode(bl.quantity,null,bl.txn_burdened_cost,NULL)
,'C',decode(bl.quantity,null,bl.txn_burdened_cost,NULL)
,'R',bl.txn_burdened_cost
,bl.txn_burdened_cost
)
),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(ra.rate_based_flag,'N',bl.txn_revenue
,decode(tmp.refresh_rates_flag,'Y',decode(bl.quantity,null,bl.txn_revenue,NULL)
,'C',bl.txn_revenue
,'R',decode(bl.quantity,null,bl.txn_revenue,NULL)
,bl.txn_revenue
)
),NULL)
,bl.budget_line_id
,bl.init_quantity
,bl.txn_init_raw_cost
,bl.txn_init_burdened_cost
,bl.txn_init_revenue
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(NVL(tmp.billable_flag,'N'),'N',bl.txn_markup_percent /* Added for billablity enhancements */
,decode(tmp.refresh_rates_flag,'Y'
/*Bug fix: 4294287 Starts ,decode(bl.quantity,null,bl.txn_markup_percent,NULL) */
,decode(g_fp_budget_version_type ,'REVENUE'
,decode(NVL(bl.txn_markup_percent,0),0,decode(bl.quantity,null,bl.txn_markup_percent,NULL)
,bl.txn_markup_percent),decode(bl.quantity,null,bl.txn_markup_percent,NULL))
/* Bug fix: 4294287 ends */
,'C',bl.txn_markup_percent
,'R'/*Bug fix: 4294287 Starts ,decode(bl.quantity,null,bl.txn_markup_percent,NULL) */
,decode(g_fp_budget_version_type ,'REVENUE'
,decode(NVL(bl.txn_markup_percent,0),0,decode(bl.quantity,null,bl.txn_markup_percent,NULL)
,bl.txn_markup_percent),decode(bl.quantity,null,bl.txn_markup_percent,NULL))
/* Bug fix: 4294287 ends */
,bl.txn_markup_percent
)),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(ra.rate_based_flag,'N',bl.txn_standard_bill_rate
,decode(tmp.refresh_rates_flag,'Y',decode(bl.quantity,null,bl.txn_standard_bill_rate,NULL)
,'C',bl.txn_standard_bill_rate
,'R',decode(bl.quantity,null,bl.txn_standard_bill_rate,NULL)
,bl.txn_standard_bill_rate
)
),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(ra.rate_based_flag,'N',bl.txn_standard_cost_rate
,decode(tmp.refresh_rates_flag,'Y',decode(bl.quantity,null,bl.txn_standard_cost_rate,NULL)
,'C',decode(bl.quantity,null,bl.txn_standard_cost_rate,NULL)
,'R',bl.txn_standard_cost_rate
,bl.txn_standard_cost_rate
)
),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(ra.rate_based_flag,'N',bl.txn_cost_rate_override
,decode(tmp.refresh_rates_flag,'Y',decode(bl.quantity,null,bl.txn_cost_rate_override,NULL)
,'C',decode(bl.quantity,null,bl.txn_cost_rate_override,NULL)
,'R',bl.txn_cost_rate_override
,bl.txn_cost_rate_override
)
),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(ra.rate_based_flag,'N',bl.burden_cost_rate
,decode(tmp.refresh_rates_flag,'Y',decode(bl.quantity,null,bl.burden_cost_rate,NULL)
,'C',decode(bl.quantity,null,bl.burden_cost_rate,NULL)
,'R',bl.burden_cost_rate
,bl.burden_cost_rate
)
),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(NVL(tmp.billable_flag,'N'),'N',bl.txn_bill_rate_override /* Added for billablity enhancements */
,decode(ra.rate_based_flag,'N',
/*Bug:5056986 */
decode(g_fp_budget_version_type,'ALL'
,decode(tmp.refresh_rates_flag,'Y',decode(bl.txn_cost_rate_override,0,bl.txn_bill_rate_override,NULL)
,'R',decode(bl.txn_cost_rate_override,0,bl.txn_bill_rate_override,NULL)
, bl.txn_bill_rate_override),bl.txn_bill_rate_override)
,decode(tmp.refresh_rates_flag,'Y' /*Bug fix: 4294287 Starts decode(bl.quantity,null,bl.txn_bill_rate_override,NULL)*/
,decode(g_fp_budget_version_type ,'REVENUE'
,decode(NVL(bl.txn_markup_percent,0),0,decode(bl.quantity,null,bl.txn_bill_rate_override,NULL)
,bl.txn_bill_rate_override),decode(bl.quantity,null,bl.txn_bill_rate_override,NULL))
/*end bug fix:4294287*/
,'C',bl.txn_bill_rate_override
,'R' /*Bug fix: 4294287 Starts,decode(bl.quantity,null,bl.txn_bill_rate_override,NULL) */
,decode(g_fp_budget_version_type ,'REVENUE'
,decode(NVL(bl.txn_markup_percent,0),0,decode(bl.quantity,null,bl.txn_bill_rate_override,NULL)
,bl.txn_bill_rate_override),decode(bl.quantity,null,bl.txn_bill_rate_override,NULL))
/*end bug fix:4294287*/
,bl.txn_bill_rate_override
)
)),NULL)
/** Bug fix:4119950 reverted back the changes made for if raw cost = burden cost then retain burden overrides
--for non-rate base, if raw cost = burden cost then retain the burden overrides
,decode(tmp.refresh_rates_flag,'Y',decode(bl.quantity,null,bl.burden_cost_rate_override
,decode(ra.rate_based_flag,'Y',NULL,decode(bl.txn_raw_cost,bl.txn_burdened_cost,bl.burden_cost_rate_override,NULL)))
,'C',decode(bl.quantity,null,bl.burden_cost_rate_override
,decode(ra.rate_based_flag,'Y',NULL,decode(bl.txn_raw_cost,bl.txn_burdened_cost,bl.burden_cost_rate_override,NULL)))
,'R',bl.burden_cost_rate_override
,bl.burden_cost_rate_override
)
,decode(tmp.refresh_rates_flag,'Y',decode(bl.quantity,null,bl.cost_ind_compiled_set_id
,decode(ra.rate_based_flag,'Y',NULL,decode(bl.txn_raw_cost,bl.txn_burdened_cost,bl.cost_ind_compiled_set_id,NULL)))
,'C',decode(bl.quantity,null,bl.cost_ind_compiled_set_id
,decode(ra.rate_based_flag,'Y',NULL,decode(bl.txn_raw_cost,bl.txn_burdened_cost,bl.cost_ind_compiled_set_id,NULL)))
,'R',bl.cost_ind_compiled_set_id
,bl.cost_ind_compiled_set_id
)
**/
/* Added this for Bug fix:4119950 Refresh burden rate override always
Note: If user enters a burden cost only, then on refresh user entered burden cost will be lost. This is acceptable
as compared to bug 4119950 not refreshing the burden cost at all
*/
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(tmp.refresh_rates_flag,'Y',decode(bl.quantity,null,bl.burden_cost_rate_override,NULL)
,'C',decode(bl.quantity,null,bl.burden_cost_rate_override,NULL)
,'R',bl.burden_cost_rate_override
,bl.burden_cost_rate_override
),NULL)
,DECODE(tmp.TXN_CURR_CODE_OVERRIDE,NULL
,decode(tmp.refresh_rates_flag,'Y',decode(bl.quantity,null,bl.cost_ind_compiled_set_id,NULL)
,'C',decode(bl.quantity,null,bl.cost_ind_compiled_set_id,NULL)
,'R',bl.cost_ind_compiled_set_id
,bl.cost_ind_compiled_set_id
),NULL)
,bl.init_raw_cost
,bl.init_burdened_cost
,bl.init_revenue
,bl.project_init_raw_cost
,bl.project_init_burdened_cost
,bl.project_init_revenue
,tmp.billable_flag
/* bug fix:4657962 */
,NVL(ra.rate_based_flag,'N')
,decode(g_mass_adjust_flag,'N','N'
,decode(NVL(g_wp_version_flag,'N'),'Y','N'
,decode(nvl(ra.rate_based_flag,'N'),'Y','N'
,'N',decode(bl.txn_cost_rate_override,0,'Y','N'))))
FROM pa_budget_lines bl
,pa_resource_assignments ra
,pa_fp_spread_calc_tmp tmp
WHERE tmp.budget_version_id = p_budget_version_id
AND (NVL(tmp.refresh_rates_flag,'N') in ('Y','R','C')
OR NVL(tmp.refresh_conv_rates_flag,'N') = 'Y'
OR NVL(tmp.mass_adjust_flag,'N') = 'Y'
OR NVL(tmp.mfc_cost_refresh_flag,'N') = 'Y'
OR NVL(tmp.ra_rates_only_change_flag,'N') = 'Y'
OR NVL(tmp.system_reference_var2,'N') = 'Y' /* Bug fix:4295967 to populate rollupt tmp in apply progress mode*/
)
AND NVL(tmp.skip_record_flag,'N') <> 'Y'
AND tmp.resource_assignment_id = ra.resource_assignment_id
AND bl.resource_assignment_id = ra.resource_assignment_id
AND bl.txn_currency_code = tmp.txn_currency_code
AND ((g_spread_from_date IS NULL)
OR (g_spread_from_date IS NOT NULL
AND ((bl.start_date > g_spread_from_date )
OR (g_spread_from_date between bl.start_date and bl.end_date)
)
)
)
AND ( (g_source_context <> 'BUDGET_LINE' )
OR
(g_source_context = 'BUDGET_LINE'
AND tmp.start_date IS NOT NULL AND tmp.end_date IS NOT NULL
AND bl.start_date BETWEEN tmp.start_date AND tmp.end_date
)
)
);
g_stage := 'populate_rollup_tmp:101: Number of rows Inserted['||sql%rowcount||']';
/* Now update rollup tmp override with any override rates passed along with currency code overrides */
IF (NVL(g_refresh_rates_flag,'N') = 'N'
AND NVL(g_mass_adjust_flag,'N') = 'N'
AND NVL(g_refresh_conv_rates_flag,'N') = 'N'
AND g_applyProg_RaId_tab.COUNT = 0 ) Then
g_stage := 'populate_rollup_tmp:102';
UPDATE /*+ INDEX(TMP PA_FP_ROLLUP_TMP_N1) */ pa_fp_rollup_tmp tmp
SET (tmp.rw_cost_rate_override
,tmp.burden_cost_rate_override
,tmp.bill_rate_override) =
(SELECT decode(caltmp.txn_curr_code_override,NULL,decode(caltmp.mfc_cost_change_flag,'Y',caltmp.cost_rate_override
,tmp.rw_cost_rate_override),caltmp.cost_rate_override)
,decode(caltmp.txn_curr_code_override,NULL,decode(caltmp.mfc_cost_change_flag,'Y',caltmp.burden_cost_rate_override
,tmp.burden_cost_rate_override),caltmp.burden_cost_rate_override)
,decode(caltmp.txn_curr_code_override,NULL,decode(caltmp.mfc_cost_change_flag,'Y',caltmp.bill_rate_override
,tmp.bill_rate_override),caltmp.bill_rate_override)
FROM pa_fp_spread_calc_tmp caltmp
WHERE caltmp.budget_version_id = tmp.budget_version_id
AND caltmp.resource_assignment_id = tmp.resource_assignment_id
AND caltmp.txn_currency_code = tmp.txn_currency_code
AND NVL(caltmp.skip_record_flag,'N') <> 'Y'
AND (NVL(g_refresh_rates_flag,'N') = 'N'
AND NVL(caltmp.refresh_conv_rates_flag,'N') = 'N'
AND NVL(caltmp.mass_adjust_flag,'N') = 'N'
AND NVL(caltmp.mfc_cost_refresh_flag,'N') = 'Y'
))
WHERE tmp.budget_version_id = p_budget_version_id
AND EXISTS ( select null
FROM pa_fp_spread_calc_tmp caltmp
WHERE caltmp.budget_version_id = tmp.budget_version_id
AND caltmp.resource_assignment_id = tmp.resource_assignment_id
AND caltmp.txn_currency_code = tmp.txn_currency_code
AND NVL(caltmp.skip_record_flag,'N') <> 'Y'
AND (NVL(g_refresh_rates_flag,'N') = 'N'
AND NVL(caltmp.refresh_conv_rates_flag,'N') = 'N'
AND NVL(caltmp.mass_adjust_flag,'N') = 'N'
AND NVL(caltmp.mfc_cost_refresh_flag,'N') = 'Y'
));
/* If only rates are changed then update the changed override rates on rollup tmp and avoid calling spread
* and call rate api
*/
IF g_rtChanged_RaId_tab.COUNT > 0 Then
g_stage := 'populate_rollup_tmp:103';
UPDATE /*+ INDEX(TMP PA_FP_ROLLUP_TMP_N1) */ pa_fp_rollup_tmp tmp
SET (tmp.rw_cost_rate_override
,tmp.burden_cost_rate_override
,tmp.bill_rate_override ) =
(SELECT decode(NVL(g_rtChanged_cstMisNumFlg_tab(i),'N'),'Y',NULL
,NVL(g_rtChanged_CostRt_Tab(i),tmp.rw_cost_rate_override))
,decode(NVL(g_rtChanged_bdMisNumFlag_tab(i),'N'),'Y',NULL
,decode(tmp.rate_based_flag,'Y',NVL(g_rtChanged_BurdRt_tab(i),tmp.burden_cost_rate_override)
,decode(g_fp_budget_version_type,'ALL'
,decode(caltmp.burden_cost_changed_flag,'Y',NVL(g_rtChanged_BurdRt_tab(i),tmp.burden_cost_rate_override)
,decode(caltmp.raw_cost_changed_flag,'Y'
,decode(tmp.rw_cost_rate_override,NULL,tmp.burden_cost_rate_override
,0,NVL(g_rtChanged_BurdRt_tab(i),tmp.burden_cost_rate_override), tmp.burden_cost_rate_override)
,NVL(g_rtChanged_BurdRt_tab(i),tmp.burden_cost_rate_override)))
,NVL(g_rtChanged_BurdRt_tab(i),tmp.burden_cost_rate_override))))
,decode(NVL(g_rtChanged_blMisNumFlag_tab(i),'N'),'Y',NULL
,NVL(g_rtChanged_billRt_tab(i),tmp.bill_rate_override))
FROM pa_fp_spread_calc_tmp caltmp
WHERE caltmp.resource_assignment_id = tmp.resource_assignment_id
AND caltmp.txn_currency_code = tmp.txn_currency_code
AND ((g_source_context <> 'BUDGET_LINE')
OR
((g_source_context = 'BUDGET_LINE')
and tmp.start_date BETWEEN caltmp.start_date and caltmp.end_date)
)
)
WHERE tmp.budget_version_id = p_budget_version_id
AND tmp.resource_assignment_id = g_rtChanged_RaId_tab(i)
AND tmp.txn_currency_code = g_rtChanged_TxnCur_tab(i)
AND ((g_source_context <> 'BUDGET_LINE')
OR
((g_source_context = 'BUDGET_LINE')
and tmp.start_date IS NOT NULL
and tmp.start_date BETWEEN g_rtChanged_sDate_tab(i) AND g_rtChanged_eDate_tab(i)
)
);
print_msg('Number of rows updated for RateChanges['||sql%rowcount||']');
SELECT count(*)
INTO l_count
FROM pa_fp_rollup_tmp ;
print_msg('Num of rows inserted into rollup Temp['||l_count||']');
SELECT tmp.resource_assignment_id
,tmp.txn_currency_code
,max(tmp.start_date)
,MAX(tmp.budget_line_id)
,NVL(tmp.rate_based_flag,'N') rate_based_flag
,decode(g_wp_version_flag,'Y',(sum(nvl(tmp.old_quantity,0) - nvl(tmp.init_quantity,0)) * ((l_quantity_adj_pct + 100)/100))
, decode(NVL(tmp.rate_based_flag,'N'),'N',0
,((sum(nvl(tmp.old_quantity,0) - nvl(tmp.init_quantity,0)) * ((l_quantity_adj_pct + 100)/100))))) unRndOldQty
,decode(g_wp_version_flag,'Y',(sum(nvl(tmp.quantity,0) - nvl(tmp.init_quantity,0)))
,decode(NVL(tmp.rate_based_flag,'N'),'N',0,(sum(nvl(tmp.quantity,0) - nvl(tmp.init_quantity,0))))) RndNewQty
,to_number(null) Quantity_diff
/* amount adjustments is allowed only for financial side */
,decode(g_wp_version_flag,'Y',0
,decode(NVL(tmp.rate_based_flag,'N'),'Y',0
,((sum(nvl(tmp.old_txn_raw_cost,0) - nvl(tmp.txn_init_raw_cost,0)) * ((l_raw_cost_adj_pct + 100)/100))))) unRndRawCost
,decode(g_wp_version_flag,'Y',0
,decode(NVL(tmp.rate_based_flag,'N'),'Y',0
,(sum(nvl(tmp.txn_raw_cost,0) - nvl(tmp.txn_init_raw_cost,0))))) RndRawCost
,to_number(null) RawCostDiff
,decode(g_wp_version_flag,'Y',0
,decode(NVL(tmp.rate_based_flag,'N'),'Y',0
,((sum(nvl(tmp.old_txn_revenue,0) - nvl(tmp.txn_init_revenue,0)) * ((l_revenue_adj_pct + 100)/100))))) unRndrevenue
,decode(g_wp_version_flag,'Y',0
,decode(NVL(tmp.rate_based_flag,'N'),'Y',0
,(sum(nvl(tmp.txn_revenue,0) - nvl(tmp.txn_init_revenue,0))))) RndRevenue
,to_number(null) RevenueDiff
FROM pa_fp_rollup_tmp tmp
,pa_fp_spread_calc_tmp caltmp
WHERE caltmp.budget_version_id = p_budget_version_id
AND caltmp.resource_assignment_id = tmp.resource_assignment_id
AND tmp.txn_currency_code = caltmp.txn_currency_code
AND nvl(caltmp.skip_record_flag,'N') <> 'Y'
AND nvl(caltmp.mass_adjust_flag,'N') = 'Y'
GROUP BY tmp.resource_assignment_id,tmp.txn_currency_code,tmp.rate_based_flag
ORDER BY tmp.resource_assignment_id,tmp.txn_currency_code;
UPDATE /*+ INDEX(TMP PA_FP_ROLLUP_TMP_N1) */ pa_fp_rollup_tmp tmp
SET tmp.old_quantity = decode(l_quantity_adj_pct,NULL,to_number(NULL),tmp.quantity)
,tmp.quantity = decode(l_quantity_adj_pct,NULL,tmp.quantity
,decode(NVL(tmp.rate_based_flag,'N'),'N'
,(NVL(tmp.init_quantity,0)+ pa_currency.round_trans_currency_amt1 (
((NVL(tmp.quantity,0) - nvl(tmp.init_quantity,0))
* ((l_quantity_adj_pct + 100)/100)),tmp.txn_currency_code))
,(NVL(tmp.init_quantity,0)+ Round(((NVL(tmp.quantity,0) - nvl(tmp.init_quantity,0))
* ((l_quantity_adj_pct + 100)/100)),5)))
)
,tmp.rw_cost_rate_override =
decode(NVL(tmp.rate_based_flag,'N'),'N',tmp.rw_cost_rate_override
,Decode(l_cost_rate_adj_pct,NULL,tmp.rw_cost_rate_override
,(NVL(tmp.rw_cost_rate_override,tmp.cost_rate)
* ((l_cost_rate_adj_pct + 100)/100)))
)
,tmp.burden_cost_rate_override =
Decode(l_burdened_rate_adj_pct,NULL
,Decode(l_cost_rate_adj_pct,NULL,tmp.burden_cost_rate_override
,Decode(tmp.burden_cost_rate_override,NULL,tmp.burden_cost_rate_override
,decode(NVL(tmp.rw_cost_rate_override,nvl(tmp.cost_rate,0)),0,tmp.burden_cost_rate_override
,((tmp.burden_cost_rate_override / NVL(tmp.rw_cost_rate_override,tmp.cost_rate))
* (NVL(tmp.rw_cost_rate_override,tmp.cost_rate)* ((l_cost_rate_adj_pct + 100)/100))))))
, (NVL(tmp.burden_cost_rate_override,tmp.burden_cost_rate)
* ((l_burdened_rate_adj_pct + 100)/100))
)
WHERE tmp.budget_version_id = p_budget_version_id
AND EXISTS ( select null
from pa_fp_spread_calc_tmp caltmp
WHERE caltmp.resource_assignment_id = tmp.resource_assignment_id
AND caltmp.txn_currency_code = tmp.txn_currency_code
AND nvl(caltmp.skip_record_flag,'N') <> 'Y'
AND nvl(caltmp.mass_adjust_flag,'N') = 'Y'
);
g_stage := 'mass_adjust:102: Number of rows Updated['||sql%rowcount||']';
UPDATE /*+ INDEX(TMP PA_FP_ROLLUP_TMP_N1) */ pa_fp_rollup_tmp tmp
SET tmp.old_quantity = decode(NVL(tmp.rate_based_flag,'N'),'N',tmp.quantity
,decode(l_quantity_adj_pct,NULL,to_number(NULL),tmp.quantity))
,tmp.quantity = decode(NVL(tmp.rate_based_flag,'N'),'N',tmp.quantity
,decode(l_quantity_adj_pct,NULL,tmp.quantity
,decode(NVL(tmp.rate_based_flag,'N'),'N'
,(NVL(tmp.init_quantity,0)+ pa_currency.round_trans_currency_amt1 (
((NVL(tmp.quantity,0) - nvl(tmp.init_quantity,0))
* ((l_quantity_adj_pct + 100)/100)),tmp.txn_currency_code))
,(NVL(tmp.init_quantity,0)+ Round(((NVL(tmp.quantity,0) - nvl(tmp.init_quantity,0))
* ((l_quantity_adj_pct + 100)/100)),5)))
))
,tmp.rw_cost_rate_override =
decode(NVL(tmp.rate_based_flag,'N'),'N',tmp.rw_cost_rate_override
,Decode(l_cost_rate_adj_pct,NULL,tmp.rw_cost_rate_override
,(NVL(tmp.rw_cost_rate_override,tmp.cost_rate)
* ((l_cost_rate_adj_pct + 100)/100)))
)
,tmp.burden_cost_rate_override =
Decode(l_burdened_rate_adj_pct,NULL,decode(NVL(tmp.rate_based_flag,'N'),'N',tmp.burden_cost_rate_override
,Decode(l_cost_rate_adj_pct,NULL,tmp.burden_cost_rate_override
,Decode(tmp.burden_cost_rate_override,NULL,tmp.burden_cost_rate_override,1,NULL
,decode(NVL(tmp.rw_cost_rate_override,nvl(tmp.cost_rate,0)),0,tmp.burden_cost_rate_override
,((tmp.burden_cost_rate_override / NVL(tmp.rw_cost_rate_override,tmp.cost_rate))
* (NVL(tmp.rw_cost_rate_override,tmp.cost_rate)* ((l_cost_rate_adj_pct + 100)/100)))))))
, (NVL(tmp.burden_cost_rate_override,tmp.burden_cost_rate)
* ((l_burdened_rate_adj_pct + 100)/100))
)
,tmp.bill_rate_override =
DECODE(l_bill_rate_adj_pct ,NULL,decode(l_cost_rate_adj_pct,NULL,tmp.bill_rate_override
,decode(g_fp_budget_version_type,'COST',tmp.bill_rate_override,'REVENUE',tmp.bill_rate_override
,decode(NVL(tmp.rate_based_flag,'N'),'N',tmp.bill_rate_override
,decode(NVL(tmp.rw_cost_rate_override,nvl(tmp.cost_rate,0)),0,tmp.bill_rate_override
,decode(tmp.bill_rate_override,1,NULL,NULL,NULL
,((tmp.bill_rate_override / NVL(tmp.rw_cost_rate_override,tmp.cost_rate))
* (NVL(tmp.rw_cost_rate_override,tmp.cost_rate)* ((l_cost_rate_adj_pct + 100)/100))))))))
,Decode(g_fp_budget_version_type,'COST',tmp.bill_rate_override
,'REVENUE',Decode(NVL(tmp.rate_based_flag,'N'),'N',tmp.bill_rate_override
,Decode(NVL(tmp.billable_flag,'N'),'Y'
,(NVL(tmp.bill_rate_override,tmp.bill_rate) *
((l_bill_rate_adj_pct + 100)/100))
,(nvl(tmp.bill_rate_override,tmp.bill_rate) * ((l_bill_rate_adj_pct + 100)/100))))
,'ALL',Decode(NVL(tmp.rate_based_flag,'N'),'N',tmp.bill_rate_override
,Decode(NVL(tmp.billable_flag,'N'),'Y'
, (NVL(tmp.bill_rate_override,tmp.bill_rate) *
((l_bill_rate_adj_pct + 100)/100))
,(nvl(tmp.bill_rate_override,tmp.bill_rate) * ((l_bill_rate_adj_pct + 100)/100))))
))
WHERE tmp.budget_version_id = p_budget_version_id
AND EXISTS ( select null
from pa_fp_spread_calc_tmp caltmp
WHERE caltmp.resource_assignment_id = tmp.resource_assignment_id
AND caltmp.txn_currency_code = tmp.txn_currency_code
AND nvl(caltmp.skip_record_flag,'N') <> 'Y'
AND nvl(caltmp.mass_adjust_flag,'N') = 'Y'
);
g_stage := 'mass_adjust:104: Number of rows Updated['||sql%rowcount||']';
UPDATE /*+ INDEX(TMP PA_FP_ROLLUP_TMP_N1) */ pa_fp_rollup_tmp tmp
SET tmp.old_txn_raw_cost =
decode(l_raw_cost_adj_pct,NULL,to_number(null),tmp.txn_raw_cost)
,tmp.old_txn_revenue =
decode(l_revenue_adj_pct,NULL,to_number(null),tmp.txn_revenue)
,tmp.txn_raw_cost =
decode(l_raw_cost_adj_pct,NULL,tmp.txn_raw_cost
,decode(tmp.txn_raw_cost,NULL,NULL,0,0
,(NVL(tmp.txn_init_raw_cost,0)+ pa_currency.round_trans_currency_amt1 (
((NVL(tmp.txn_raw_cost,0) - nvl(tmp.txn_init_raw_cost,0))
* ((l_raw_cost_adj_pct + 100)/100)),tmp.txn_currency_code))))
,tmp.txn_revenue =
decode(l_revenue_adj_pct,NULL,tmp.txn_revenue
,decode(tmp.txn_revenue,NULL,NULL,0,0
,(NVL(tmp.txn_init_revenue,0)+ pa_currency.round_trans_currency_amt1 (
((NVL(tmp.txn_revenue,0) - nvl(tmp.txn_init_revenue,0))
* ((l_revenue_adj_pct + 100)/100)),tmp.txn_currency_code))))
,tmp.quantity = decode(NVL(tmp.rate_based_flag,'N'),'Y',tmp.quantity
,decode(l_raw_cost_adj_pct,NULL
,decode(l_revenue_adj_pct, NULL, tmp.quantity
,decode(g_fp_budget_version_type,'COST',tmp.quantity
,'REVENUE', (NVL(tmp.txn_init_revenue,0)+ pa_currency.round_trans_currency_amt1 (
((NVL(tmp.txn_revenue,0) - nvl(tmp.txn_init_revenue,0))
* ((l_revenue_adj_pct + 100)/100)),tmp.txn_currency_code))
,'ALL',decode(nvl(tmp.rw_cost_rate_override,0),0
,(NVL(tmp.txn_init_revenue,0)+ pa_currency.round_trans_currency_amt1 (
((NVL(tmp.txn_revenue,0) - nvl(tmp.txn_init_revenue,0))
* ((l_revenue_adj_pct + 100)/100)),tmp.txn_currency_code))
,tmp.quantity)))
,decode(g_fp_budget_version_type,'REVENUE',tmp.quantity
,'COST',(NVL(tmp.txn_init_raw_cost,0)+ pa_currency.round_trans_currency_amt1 (
((NVL(tmp.txn_raw_cost,0) - nvl(tmp.txn_init_raw_cost,0))
* ((l_raw_cost_adj_pct + 100)/100)),tmp.txn_currency_code))
,'ALL',decode(nvl(tmp.rw_cost_rate_override,0),0,tmp.quantity
,(NVL(tmp.txn_init_raw_cost,0)+ pa_currency.round_trans_currency_amt1 (
((NVL(tmp.txn_raw_cost,0) - nvl(tmp.txn_init_raw_cost,0))
* ((l_raw_cost_adj_pct + 100)/100)),tmp.txn_currency_code))))))
,tmp.burden_cost_rate_override =
Decode(g_fp_budget_version_type,'REVENUE',tmp.burden_cost_rate_override
,Decode(l_burden_cost_adj_pct,NULL
,Decode(l_raw_cost_adj_pct,NULL,tmp.burden_cost_rate_override
,decode(NVL(tmp.rate_based_flag,'N'),'N',tmp.burden_cost_rate_override
,Decode(tmp.burden_cost_rate_override,NULL,tmp.burden_cost_rate_override,1,NULL
,decode((nvl(tmp.quantity,0) - nvl(tmp.init_quantity,0)),0,tmp.burden_cost_rate_override
,decode(NVL(tmp.rw_cost_rate_override,nvl(tmp.cost_rate,0)),0,tmp.burden_cost_rate_override
,((tmp.burden_cost_rate_override / NVL(tmp.rw_cost_rate_override,tmp.cost_rate))
* (NVL(tmp.rw_cost_rate_override,tmp.cost_rate)* ((l_raw_cost_adj_pct+ 100)/100))))))))
,(NVL(tmp.burden_cost_rate_override,tmp.burden_cost_rate)
* ((l_burden_cost_adj_pct + 100)/100))
))
,tmp.bill_rate_override =
decode(g_fp_budget_version_type,'COST', tmp.bill_rate_override
,'REVENUE', decode(l_revenue_adj_pct,NULL,tmp.bill_rate_override
,decode(NVL(tmp.rate_based_flag,'N'),'N',tmp.bill_rate_override
,(NVL(tmp.bill_rate_override,tmp.bill_rate) * ((l_revenue_adj_pct + 100)/100))))
,'ALL' , Decode(l_raw_cost_adj_pct,NULL
,decode(l_revenue_adj_pct,NULL,tmp.bill_rate_override
,decode(NVL(tmp.rate_based_flag,'N'),'Y'
,(NVL(tmp.bill_rate_override,tmp.bill_rate) * ((l_revenue_adj_pct + 100)/100))
,decode(NVL(tmp.rw_cost_rate_override,0),0,tmp.bill_rate_override
,(NVL(tmp.bill_rate_override,tmp.bill_rate) * ((l_revenue_adj_pct + 100)/100)))))
,decode(NVL(tmp.rate_based_flag,'N'),'N',tmp.bill_rate_override
,decode((nvl(tmp.quantity,0) - nvl(tmp.init_quantity,0)),0,tmp.bill_rate_override
,decode(NVL(tmp.rw_cost_rate_override,nvl(tmp.cost_rate,0)),0,tmp.bill_rate_override
,((tmp.bill_rate_override / NVL(tmp.rw_cost_rate_override,tmp.cost_rate))
* (NVL(tmp.rw_cost_rate_override,tmp.cost_rate)* ((l_raw_cost_adj_pct+ 100)/100)))))))
)
,tmp.rw_cost_rate_override =
decode(l_raw_cost_adj_pct,NULL,tmp.rw_cost_rate_override
,decode((nvl(tmp.quantity,0) - nvl(tmp.init_quantity,0)),0,tmp.rw_cost_rate_override
,decode(NVL(tmp.rate_based_flag,'N'),'N',tmp.rw_cost_rate_override
,Decode(l_raw_cost_adj_pct,NULL,tmp.rw_cost_rate_override
,(NVL(tmp.rw_cost_rate_override,tmp.cost_rate)
* ((l_raw_cost_adj_pct + 100)/100))))))
WHERE tmp.budget_version_id = p_budget_version_id
AND EXISTS ( select null
from pa_fp_spread_calc_tmp caltmp
WHERE caltmp.resource_assignment_id = tmp.resource_assignment_id
AND caltmp.txn_currency_code = tmp.txn_currency_code
AND nvl(caltmp.skip_record_flag,'N') <> 'Y'
AND nvl(caltmp.mass_adjust_flag,'N') = 'Y' );
l_raId_tab.delete;
l_txn_cur_tab.delete;
l_sdate_tab.delete;
l_blId_tab.delete;
l_ratebase_tab.delete;
l_quantity_old_tab.delete;
l_quantity_new_tab.delete;
l_quantity_diff_tab.delete;
l_rawCost_new_tab.delete;
l_rawcost_old_tab.delete;
l_rawcost_diff_tab.delete;
l_revenue_new_tab.delete;
l_revenue_old_tab.delete;
l_revenue_diff_tab.delete;
UPDATE pa_fp_rollup_tmp tmp
SET tmp.quantity = tmp.quantity + nvl(l_quantity_diff_tab(i),0)
WHERE tmp.budget_line_id = l_blId_tab(i)
AND NVL(l_quantity_diff_tab(i),0) <> 0;
UPDATE pa_fp_rollup_tmp tmp
SET tmp.quantity = decode(g_fp_budget_version_type,'REVENUE', tmp.quantity
,'COST', (tmp.txn_raw_cost + nvl(l_rawcost_diff_tab(i),0))
,'ALL',decode(tmp.rw_cost_rate_override,0,tmp.quantity,NULL,tmp.quantity
,(tmp.txn_raw_cost + nvl(l_rawcost_diff_tab(i),0))))
,tmp.txn_raw_cost = decode(g_fp_budget_version_type,'REVENUE', tmp.txn_raw_cost
,'COST', (tmp.txn_raw_cost + nvl(l_rawcost_diff_tab(i),0))
,'ALL',decode(tmp.rw_cost_rate_override,0,tmp.txn_raw_cost,NULL,tmp.txn_raw_cost
,(tmp.txn_raw_cost + nvl(l_rawcost_diff_tab(i),0))))
WHERE tmp.budget_line_id = l_blId_tab(i)
AND NVL(l_rawcost_diff_tab(i),0) <> 0
AND l_ratebase_tab(i) = 'N' ;
UPDATE pa_fp_rollup_tmp tmp
SET tmp.quantity = decode(g_fp_budget_version_type,'COST', tmp.quantity
,'REVENUE', (tmp.txn_revenue + nvl(l_revenue_diff_tab(i),0))
,'ALL',decode(nvl(tmp.rw_cost_rate_override,0),0
,(tmp.txn_revenue + nvl(l_revenue_diff_tab(i),0)),tmp.quantity))
,tmp.txn_revenue = decode(g_fp_budget_version_type,'COST',tmp.txn_revenue
,'REVENUE', (tmp.txn_revenue + nvl(l_revenue_diff_tab(i),0))
,'ALL', decode(nvl(tmp.rw_cost_rate_override,0),0
,(tmp.txn_revenue + nvl(l_revenue_diff_tab(i),0)),tmp.quantity))
WHERE tmp.budget_line_id = l_blId_tab(i)
AND NVL(l_revenue_diff_tab(i),0) <> 0
AND l_ratebase_tab(i) = 'N' ;
SELECT resource_list_member_id
FROM pa_resource_assignments
WHERE resource_assignment_id = p_resource_assignment_id;
SELECT /*+ INDEX(PBL PA_BUDGET_LINES_U1) */
pbl.resource_assignment_id
,DECODE(sum(pbl.quantity),0,NULL,sum(pbl.quantity)) pfc_quantity
,DECODE(sum(pbl.raw_cost),0,NULL,sum(pbl.raw_cost)) pfc_raw_cost
,DECODE(sum(pbl.burdened_cost),0,NULL,sum(pbl.burdened_cost)) pfc_burdened_cost
,DECODE(sum(pbl.revenue),0,NULL,sum(pbl.revenue)) pfc_revenue
,DECODE(sum(pbl.project_raw_cost),0,NULL,sum(pbl.project_raw_cost)) project_raw_cost
,DECODE(sum(pbl.project_burdened_cost),0,NULL,sum(pbl.project_burdened_cost)) project_burdened_cost
,DECODE(sum(pbl.project_revenue),0,NULL,sum(pbl.project_revenue)) project_revenue
,DECODE(sum(pbl.init_quantity),0,NULL,sum(pbl.init_quantity)) pfc_init_quantity
,DECODE(sum(pbl.init_raw_cost),0,NULL,sum(pbl.init_raw_cost)) pfc_init_raw_cost
,DECODE(sum(pbl.init_burdened_cost),0,NULL,sum(pbl.init_burdened_cost)) pfc_init_burdened_cost
,DECODE(sum(pbl.init_revenue),0,NULL,sum(pbl.init_revenue)) pfc_init_revenue
,DECODE(sum(pbl.project_init_raw_cost),0,NULL,sum(pbl.project_init_raw_cost)) project_init_raw_cost
,DECODE(sum(pbl.project_init_burdened_cost),0,NULL,sum(pbl.project_init_burdened_cost)) project_init_burdened_cost
,DECODE(sum(pbl.project_init_revenue),0,NULL,sum(pbl.project_init_revenue)) project_init_revenue
FROM pa_budget_lines pbl
WHERE pbl.budget_version_id = p_budget_version_id
AND EXISTS (SELECT NULL
FROM pa_fp_spread_calc_tmp tmp
WHERE tmp.budget_version_id = p_budget_version_id
AND tmp.resource_assignment_id = pbl.resource_assignment_id
)
GROUP BY pbl.resource_assignment_id;
SELECT
pbl.resource_assignment_id
,DECODE(sum(pbl.quantity),0,NULL,sum(pbl.quantity)) pfc_quantity
,DECODE(sum(pbl.raw_cost),0,NULL,sum(pbl.raw_cost)) pfc_raw_cost
,DECODE(sum(pbl.burdened_cost),0,NULL,sum(pbl.burdened_cost)) pfc_burdened_cost
,DECODE(sum(pbl.revenue),0,NULL,sum(pbl.revenue)) pfc_revenue
,DECODE(sum(pbl.project_raw_cost),0,NULL,sum(pbl.project_raw_cost)) project_raw_cost
,DECODE(sum(pbl.project_burdened_cost),0,NULL,sum(pbl.project_burdened_cost)) project_burdened_cost
,DECODE(sum(pbl.project_revenue),0,NULL,sum(pbl.project_revenue)) project_revenue
,DECODE(sum(pbl.init_quantity),0,NULL,sum(pbl.init_quantity)) pfc_init_quantity
,DECODE(sum(pbl.init_raw_cost),0,NULL,sum(pbl.init_raw_cost)) pfc_init_raw_cost
,DECODE(sum(pbl.init_burdened_cost),0,NULL,sum(pbl.init_burdened_cost)) pfc_init_burdened_cost
,DECODE(sum(pbl.init_revenue),0,NULL,sum(pbl.init_revenue)) pfc_init_revenue
,DECODE(sum(pbl.project_init_raw_cost),0,NULL,sum(pbl.project_init_raw_cost)) project_init_raw_cost
,DECODE(sum(pbl.project_init_burdened_cost),0,NULL,sum(pbl.project_init_burdened_cost)) project_init_burdened_cost
,DECODE(sum(pbl.project_init_revenue),0,NULL,sum(pbl.project_init_revenue)) project_init_revenue
FROM pa_budget_lines pbl
WHERE pbl.budget_version_id = p_budget_version_id
GROUP BY pbl.resource_assignment_id;
UPDATE PA_RESOURCE_ASSIGNMENTS ra
SET ra.total_plan_quantity = null
,ra.total_plan_raw_cost = null
,ra.total_plan_burdened_cost = null
,ra.total_plan_revenue = null
,ra.total_project_raw_cost = null
,ra.total_project_burdened_cost = null
,ra.total_project_revenue = null
,ra.last_updated_by = l_user_id
,ra.last_update_date = sysdate
,ra.last_update_login = l_login_id
WHERE EXISTS (SELECT NULL
FROM pa_fp_spread_calc_tmp tmp
WHERE tmp.budget_version_id = p_budget_version_id
AND tmp.resource_assignment_id = ra.resource_assignment_id
/* Bug fix: 4322568 AND NVL(tmp.skip_record_flag,'N') = 'Y' */
);
UPDATE PA_RESOURCE_ASSIGNMENTS ra
SET ra.total_plan_quantity = l_pfc_quantity_tab(i)
,ra.total_plan_raw_cost = l_pfc_raw_cost_tab(i)
,ra.total_plan_burdened_cost = l_pfc_burden_cost_tab(i)
,ra.total_plan_revenue = l_pfc_revenue_tab(i)
,ra.total_project_raw_cost = l_project_raw_cost_tab(i)
,ra.total_project_burdened_cost = l_project_burden_cost_tab(i)
,ra.total_project_revenue = l_project_revenue_tab(i)
,ra.last_updated_by = l_user_id
,ra.last_update_date = sysdate
,ra.last_update_login = l_login_id
WHERE ra.resource_assignment_id = l_resource_assignment_id_tab(i);
SELECT DECODE(sum(total_plan_revenue),0,NULL,
sum(total_plan_revenue)) total_plan_revenue
,DECODE(sum(total_plan_raw_cost),0,NULL,
sum(total_plan_raw_cost)) total_plan_raw_cost
,DECODE(sum(total_plan_burdened_cost),0,NULL,
sum(total_plan_burdened_cost)) total_plan_burdened_cost
/* Bug fix: 3968340. rollup the labor and equipment effort only if the uom is HOURS
* the following code is commented out and added the new decode
,DECODE(sum(decode(resource_class_code,'PEOPLE',total_plan_quantity,to_number(null))),0,NULL,
sum(decode(resource_class_code,'PEOPLE',total_plan_quantity,to_number(null))))
total_labor_quantity
,DECODE(sum(decode(resource_class_code,'EQUIPMENT',total_plan_quantity,to_number(null))),0,NULL,
sum(decode(resource_class_code,'EQUIPMENT',total_plan_quantity,to_number(null))))
total_equipment_quantity
*/
,DECODE(sum(decode(resource_class_code,'PEOPLE'
,decode(pra.unit_of_measure,'HOURS',total_plan_quantity,to_number(null))
,to_number(null))),0,NULL
,sum(decode(resource_class_code,'PEOPLE'
,decode(pra.unit_of_measure,'HOURS',total_plan_quantity,to_number(null))
,to_number(null))
)) total_labor_quantity
,DECODE(sum(decode(resource_class_code,'EQUIPMENT'
,decode(pra.unit_of_measure,'HOURS',total_plan_quantity,to_number(null))
,to_number(null))),0,NULL
,sum(decode(resource_class_code,'EQUIPMENT'
,decode(pra.unit_of_measure,'HOURS',total_plan_quantity,to_number(null))
,to_number(null))
))total_equipment_quantity
,DECODE(sum(total_project_raw_cost),0,NULL,
sum(total_project_raw_cost)) total_project_raw_cost
,DECODE(sum(total_project_burdened_cost),0,NULL,
sum(total_project_burdened_cost)) total_project_burdened_cost
,DECODE(sum(total_project_revenue),0,NULL,
sum(total_project_revenue)) total_project_revenue
FROM pa_resource_assignments pra
WHERE pra.budget_version_id = p_budget_version_id
AND pra.project_id = g_project_id;
UPDATE pa_budget_versions SET
raw_cost = to_number(NULL)
,burdened_cost = to_number(NULL)
,revenue = to_number(NULL)
,total_project_raw_cost = to_number(NULL)
,total_project_burdened_cost = to_number(NULL)
,total_project_revenue = to_number(NULL)
,labor_quantity = to_number(NULL)
,equipment_quantity = to_number(NULL)
,record_version_number = NVL(record_version_number,0) + 1
,last_update_date = sysdate
,last_updated_by = fnd_global.user_id
,creation_date = sysdate
,created_by = fnd_global.user_id
,last_update_login = fnd_global.login_id
WHERE budget_version_id = p_budget_version_id;
SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH:MI:SS') INTO g_session_time
FROM DUAL;
/*print_msg(to_char(l_stage)||'UPDATE pa_budget_versions with the following:');
UPDATE pa_budget_versions
SET raw_cost = rollup_ra_rec.total_plan_raw_cost
,burdened_cost = rollup_ra_rec.total_plan_burdened_cost
,revenue = rollup_ra_rec.total_plan_revenue
,total_project_raw_cost = rollup_ra_rec.total_project_raw_cost
,total_project_burdened_cost = rollup_ra_rec.total_project_burdened_cost
,total_project_revenue = rollup_ra_rec.total_project_revenue
,labor_quantity = rollup_ra_rec.total_labor_quantity
,equipment_quantity = rollup_ra_rec.total_equipment_quantity
,record_version_number = NVL(record_version_number,0) + 1
,last_update_date = sysdate
,last_updated_by = fnd_global.user_id
,creation_date = sysdate
,created_by = fnd_global.user_id
,last_update_login = fnd_global.login_id
WHERE budget_version_id = p_budget_version_id;
SELECT TO_CHAR(SYSDATE,'DD-MON-YYYY HH:MI:SS') INTO g_session_time
FROM DUAL;
print_msg('Inserting records into fp_res_assignment_tmp table count['||g_sprd_raId_tab.COUNT||']fst['||g_sprd_raId_tab.first||']Lst['||g_sprd_raId_tab.last||']');
INSERT INTO PA_FP_RES_ASSIGNMENTS_TMP
(BUDGET_VERSION_ID
,PROJECT_ID
,TASK_ID
,RESOURCE_LIST_MEMBER_ID
,SPREAD_CURVE_ID
,SP_FIXED_DATE
,SOURCE_CONTEXT
,RATE_BASED_FLAG
,RESOURCE_ASSIGNMENT_ID
,TXN_CURRENCY_CODE
,LINE_START_DATE
,LINE_END_DATE
,PLANNING_START_DATE
,PLANNING_END_DATE
,TXN_REVENUE
,TXN_REVENUE_ADDL
,TXN_RAW_COST
,TXN_RAW_COST_ADDL
,TXN_BURDENED_COST
,TXN_BURDENED_COST_ADDL
,TXN_PLAN_QUANTITY
,TXN_PLAN_QUANTITY_ADDL
,TXN_CURRENCY_CODE_OVERRIDE
,TXN_INIT_REVENUE
,TXN_INIT_RAW_COST
,TXN_INIT_BURDENED_COST
,INIT_QUANTITY
,SPREAD_AMOUNTS_FLAG
,RAW_COST_RATE
,RW_COST_RATE_OVERRIDE
,BURDEN_COST_RATE
,BURDEN_COST_RATE_OVERRIDE
,BILL_RATE
,BILL_RATE_OVERRIDE
,PROJECT_CURRENCY_CODE
,PROJFUNC_CURRENCY_CODE
/* bug fix:5726773 : store the value in the sprd table*/
,NEG_QUANTITY_CHANGE_FLAG --neg_Qty_Change_flag
,NEG_RAWCOST_CHANGE_FLAG --neg_RawCst_Change_flag
,NEG_BURDEN_CHANGE_FALG --neg_BurdCst_Change_flag
,NEG_REVENUE_CHANGE_FLAG --neg_rev_Change_flag
)
VALUES ( p_budget_version_id
,g_project_id
,g_sprd_task_id_tab(i)
,g_sprd_rlm_id_tab(i)
,g_sprd_spcurve_id_tab(i)
,g_sprd_sp_fixed_date_tab(i)
,p_source_context
,NVL(g_sprd_ratebase_flag_tab(i),'N')
,g_sprd_raId_tab(i)
,g_sprd_txn_cur_tab(i)
,g_sprd_sdate_tab(i)
,g_sprd_edate_tab(i)
,g_sprd_plan_sdate_tab(i)
,g_sprd_plan_edate_tab(i)
,g_sprd_txn_rev_tab(i)
,g_sprd_txn_rev_addl_tab(i)
,g_sprd_txn_raw_tab(i)
,g_sprd_txn_raw_addl_tab(i)
,g_sprd_txn_burd_tab(i)
,g_sprd_txn_burd_addl_tab(i)
,g_sprd_qty_tab(i)
,g_sprd_qty_addl_tab(i)
,g_sprd_txn_cur_ovr_tab(i)
,g_sprd_txn_init_rev_tab(i)
,g_sprd_txn_init_raw_tab(i)
,g_sprd_txn_init_burd_tab(i)
,g_sprd_txn_init_qty_tab(i)
,g_sprd_spread_reqd_flag_tab(i)
,g_sprd_costRt_tab(i)
,g_sprd_costRt_Ovr_tab(i)
,g_sprd_burdRt_Tab(i)
,g_sprd_burdRt_Ovr_tab(i)
,g_sprd_billRt_tab(i)
,g_sprd_billRt_Ovr_tab(i)
,g_sprd_projCur_tab(i)
,g_sprd_projfuncCur_tab(i)
/* Bug fix:5463690 */
,g_sprd_neg_Qty_Changflag_tab(i)
,g_sprd_neg_Raw_Changflag_tab(i)
,g_sprd_neg_Burd_Changflag_tab(i)
,g_sprd_neg_rev_Changflag_tab(i)
);
print_msg('Number of SpreadRecords Inserted['||sql%rowcount||']');
Select c.projfunc_cost_exchange_rate
,c.projfunc_rev_exchange_rate
,c.project_cost_exchange_rate
,c.project_rev_exchange_rate
From pa_fp_txn_currencies c
where c.fin_plan_version_id = p_budget_version_id
and c.txn_currency_code = p_txn_currency_code;
select p_budget_line_id
,p_resource_assignment_id
,r.start_date
,r.end_date
,p_txn_currency_code
,nvl(p_txn_raw_cost,0)
,nvl(p_txn_burdened_cost,0)
,nvl(p_txn_revenue,0)
,nvl(r.projfunc_currency_code,pa_fp_multi_currency_pkg.g_projfunc_currency_code)
,nvl(r.projfunc_cost_rate_type,pa_fp_multi_currency_pkg.g_projfunc_cost_rate_type)
,DECODE(r.projfunc_cost_exchange_rate,null,
DECODE(nvl(r.projfunc_cost_rate_type,pa_fp_multi_currency_pkg.g_projfunc_cost_rate_type),'User',
fp_cur_rec.projfunc_cost_exchange_rate,
r.projfunc_cost_exchange_rate),
r.projfunc_cost_exchange_rate)
projfunc_cost_exchange_rate
,DECODE(nvl(r.projfunc_cost_rate_type,pa_fp_multi_currency_pkg.g_projfunc_cost_rate_type),'User',Null,
nvl(r.projfunc_cost_rate_date_type,pa_fp_multi_currency_pkg.g_projfunc_cost_rate_date_type))
,DECODE(nvl(r.projfunc_cost_rate_date_type,
pa_fp_multi_currency_pkg.g_projfunc_cost_rate_date_type),
'START_DATE',r.start_date,
'END_DATE' ,r.end_date,
nvl(r.projfunc_cost_rate_date,pa_fp_multi_currency_pkg.g_projfunc_cost_rate_date))
projfunc_cost_rate_date
,nvl(r.projfunc_rev_rate_type,pa_fp_multi_currency_pkg.g_projfunc_rev_rate_type)
,DECODE(r.projfunc_rev_exchange_rate,null,
DECODE(nvl(r.projfunc_rev_rate_type,pa_fp_multi_currency_pkg.g_projfunc_rev_rate_type),'User',
fp_cur_rec.projfunc_rev_exchange_rate,
r.projfunc_rev_exchange_rate),
r.projfunc_rev_exchange_rate)
projfunc_rev_exchange_rate
,DECODE(nvl(r.projfunc_rev_rate_type,pa_fp_multi_currency_pkg.g_projfunc_rev_rate_type),'User',NULL,
nvl(r.projfunc_rev_rate_date_type,pa_fp_multi_currency_pkg.g_projfunc_rev_rate_date_type))
,DECODE(nvl(r.projfunc_rev_rate_date_type,pa_fp_multi_currency_pkg.g_projfunc_rev_rate_date_type),
'START_DATE',r.start_date,
'END_DATE' ,r.end_date,
nvl(r.projfunc_rev_rate_date,pa_fp_multi_currency_pkg.g_projfunc_rev_rate_date))
projfunc_rev_rate_date
,nvl(r.project_currency_code,pa_fp_multi_currency_pkg.g_proj_currency_code)
,nvl(r.project_cost_rate_type,pa_fp_multi_currency_pkg.g_proj_cost_rate_type)
,DECODE(r.project_cost_exchange_rate,null,
DECODE(nvl(r.project_cost_rate_type,pa_fp_multi_currency_pkg.g_proj_cost_rate_type),'User',
fp_cur_rec.project_cost_exchange_rate,
r.project_cost_exchange_rate),
r.project_cost_exchange_rate)
project_cost_exchange_rate
,DECODE(nvl(r.project_cost_rate_type,pa_fp_multi_currency_pkg.g_proj_cost_rate_type),'User',NULL,
nvl(r.project_cost_rate_date_type,pa_fp_multi_currency_pkg.g_proj_cost_rate_date_type))
,DECODE(nvl(r.project_cost_rate_date_type,pa_fp_multi_currency_pkg.g_proj_cost_rate_date_type),
'START_DATE',r.start_date,
'END_DATE' ,r.end_date,
nvl(r.project_cost_rate_date,pa_fp_multi_currency_pkg.g_proj_cost_rate_date))
project_cost_rate_date
,nvl(r.project_rev_rate_type,pa_fp_multi_currency_pkg.g_proj_rev_rate_type)
,DECODE(r.project_rev_exchange_rate,null,
DECODE(nvl(r.project_rev_rate_type,pa_fp_multi_currency_pkg.g_proj_rev_rate_type),'User',
fp_cur_rec.project_rev_exchange_rate,
r.project_rev_exchange_rate),
r.project_rev_exchange_rate)
project_rev_exchange_rate
,DECODE(nvl(r.project_rev_rate_type,pa_fp_multi_currency_pkg.g_proj_rev_rate_type),'User',NULL,
nvl(r.project_rev_rate_date_type,pa_fp_multi_currency_pkg.g_proj_rev_rate_date_type))
,DECODE(nvl(r.project_rev_rate_date_type,pa_fp_multi_currency_pkg.g_proj_rev_rate_date_type),
'START_DATE',r.start_date,
'END_DATE' ,r.end_date,
nvl(r.project_rev_rate_date,pa_fp_multi_currency_pkg.g_proj_rev_rate_date))
project_rev_rate_date
from pa_fp_rollup_tmp r
where nvl(r.delete_flag,'N') = 'N'
and r.budget_line_id = p_budget_line_id
order by r.resource_assignment_id,
r.start_date,
r.txn_currency_code;
l_bl_id_tab.delete;
l_resource_assignment_id_tab.delete;
l_start_date_tab.delete;
l_end_date_tab.delete;
l_txn_currency_code_tab.delete;
l_txn_raw_cost_tab.delete;
l_txn_burdened_cost_tab.delete;
l_txn_revenue_tab.delete;
l_projfunc_currency_code_tab.delete;
l_projfunc_cost_rate_type_tab.delete;
l_projfunc_cost_rate_tab.delete;
l_projfunc_cost_rt_dt_typ_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_rt_dt_typ_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_currency_code_tab.delete;
l_proj_cost_rate_type_tab.delete;
l_proj_cost_rate_tab.delete;
l_proj_cost_rt_dt_typ_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_rt_dt_typ_tab.delete;
l_proj_rev_rate_date_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_user_validate_flag_tab.delete;
l_status_flag_tab.delete;
select 'Y'
from dual
where EXISTS
(select null
from pa_budget_lines bl
where bl.resource_assignment_id = p_res_ass_id
and (bl.init_quantity is not null
or bl.txn_init_raw_cost is not null
or bl.txn_init_burdened_cost is not null
or bl.txn_init_revenue is not null )
);
/* This API deletes all the budget lines when no quantiy , amounts exists on the budget lines
* retaining these budget lines create issues during the workplan mode
* keeping these budget lines will retain the currency code during the resource assignment updates
*/
PROCEDURE Delete_BL_Where_Nulls
( p_budget_version_id IN NUmber
,p_resource_assignment_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,x_return_status OUT NOCOPY varchar2
,x_msg_data OUT NOCOPY varchar2
) IS
/*Perf Changes: Removed update and added a cusor to fetch the ra+txn currency
*This reduces teh cpu time from 350sec to 11sec tested on pjperf instance
*/
cursor cur_nullBls is
select tmp1.resource_assignment_id
,tmp1.txn_currency_code
from pa_fp_spread_calc_tmp tmp1
,pa_resource_assignments ra
where tmp1.budget_version_id = p_budget_version_id
and ra.resource_assignment_id = tmp1.resource_assignment_id
and NVL(ra.rate_based_flag,'N') = 'N'
and NOT EXISTS ( select /*+ INDEX(BL PA_BUDGET_LINES_U1) */ null
from pa_budget_lines bl
where bl.resource_assignment_id = tmp1.resource_assignment_id
and bl.txn_currency_code = tmp1.txn_currency_code
)
group by tmp1.resource_assignment_id
,tmp1.txn_currency_code;
l_budget_line_id_tab.delete;
print_msg('Bug fix:4272944: DONOT DELETE AUTOBASELINE zero qty budget lines');
print_msg('Delete all the budget lines where Qty and Amounts are not exists');
l_budget_line_id_tab.delete;
l_raid_tab.delete;
l_txn_cur_code_tab.delete;
l_start_date_tab.delete;
l_end_date_tab.delete;
l_period_name_tab.delete;
l_proj_cur_code_tab.delete;
l_projfunc_cur_code_tab.delete;
DELETE FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = p_resource_assignment_tab(i)
AND bl.budget_version_id = p_budget_version_id
AND ( nvl(bl.quantity,0) = 0
and nvl(bl.txn_raw_cost,0) = 0
and nvl(bl.txn_burdened_cost,0) = 0
and nvl(bl.txn_revenue,0) = 0
and nvl(bl.init_quantity,0) = 0
and nvl(bl.txn_init_raw_cost,0) = 0
and nvl(bl.txn_init_burdened_cost,0) = 0
and nvl(bl.txn_init_revenue,0) = 0
)
RETURNING bl.budget_line_id
,bl.resource_assignment_id
,bl.txn_currency_code
,bl.start_date
,bl.end_date
,bl.period_name
,bl.project_currency_code
,bl.projfunc_currency_code
BULK COLLECT INTO
l_budget_line_id_tab
,l_raid_tab
,l_txn_cur_code_tab
,l_start_date_tab
,l_end_date_tab
,l_period_name_tab
,l_proj_cur_code_tab
,l_projfunc_cur_code_tab;
print_msg('Number of rows deleted ['||l_budget_line_id_tab.count||']');
,p_delete_flag => 'Y'
,x_msg_data => x_msg_data
,x_return_status => x_return_status
);
l_raid_tab.delete;
l_txn_cur_code_tab.delete;
UPDATE /*+ INDEX(RTX PA_RESOURCE_ASGN_CURR_U2) */ pa_resource_asgn_curr rtx
SET rtx.TXN_RAW_COST_RATE_OVERRIDE = null
,rtx.TXN_BURDEN_COST_RATE_OVERRIDE = null
,rtx.TXN_BILL_RATE_OVERRIDE = null
WHERE rtx.resource_assignment_id = l_raid_tab(i)
AND rtx.txn_currency_code = l_txn_cur_code_tab(i);
--print_msg('Number of rows updated for setting null rates['||sql%rowcount||']');
* and it updates the passed in the txn currency with the rate scheduel currency.
* while rollup the amounts, the maintain data() api should update the existing RA+TXN currency,
* instead of deleting the old records and creating the new records.
* Fix required: In order to fix this issue, calculate api should keep track of old txn currency and new txn currency
* per planning resource and then call the maintain_data() api by passing old txn currency to delete and create
* record with new txn currency.
* Proposal:
* since the amount of effort reqd to fix is more. adding simple delete of records from new entity
* for workplan context where quantity is null, and rates are null.
* This issue happens only for workplan when PC <> PFC and for initial creation of task assignment
*/
If nvl(g_wp_version_flag,'N') = 'Y' AND g_calling_module NOT IN ('BUDGET_GENERATION','FORECAST_GENERATION') then
If g_source_context = 'RESOURCE_ASSIGNMENT' OR g_time_phased_code = 'N' Then
FOR i IN p_resource_assignment_tab.FIRST .. p_resource_assignment_tab.LAST LOOP
DELETE FROM pa_resource_asgn_curr rbc
WHERE rbc.budget_version_id = p_budget_version_id
AND rbc.resource_assignment_id = p_resource_assignment_tab(i)
AND 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 )
AND rbc.total_quantity is NULL
AND rbc.total_init_quantity is NULL
AND rbc.txn_raw_cost_rate_override is NULL
AND rbc.txn_burden_cost_rate_override is NULL
;
print_msg('Number of rows deleted from new entity['||sql%rowcount||']');
print_msg('Unexpected error occured in Delete_BL_Where_Nulls['||x_msg_data||']');
END Delete_BL_Where_Nulls;
* p_calling_module Required possible values are 'UPDATE_PLAN_TRANSACTION' / 'BUDGET_GENERATION','FORECAST_GENERATION'
* p_activity_code Required possible values are 'CALCULATE'
* If p_mass_adjust_flag is passed as 'Y' then following one-of the params must be passed
* p_quantity_adj_pct
* p_cost_rate_adj_pct
* p_burdened_rate_adj_pct
* p_bill_rate_adj_pct
* The following are the valid values for p_refresh_rates_flag
* 'Y' -- Refresh all Raw cost,burden cost and revenue amounts
* 'C' -- Refresh only Raw cost and burden cost amounts but retain the Revenue amounts
* 'R' -- Refresh only Revenue amounts
* 'N' -- No Refresh
* Note: The values ('C' and 'R' is valid only in calling module = 'BUDGET_GENERATION' and 'FORECAST_GENERATION' )
* the following params are only for internal purposes, the values passed from AMG or page will be ignored
* p_addl_qty_tab,p_addl_raw_cost_tab,p_addl_burdened_cost_tab,p_addl_revenue_tab
*
*/
PROCEDURE calculate ( p_project_id IN pa_projects_all.project_id%TYPE
,p_budget_version_id IN pa_budget_versions.budget_version_id%TYPE
,p_refresh_rates_flag IN VARCHAR2 := 'N'
,p_refresh_conv_rates_flag IN VARCHAR2 := 'N'
,p_spread_required_flag IN VARCHAR2 := 'Y'
,p_conv_rates_required_flag IN VARCHAR2 := 'Y'
,p_rollup_required_flag IN VARCHAR2 := 'Y'
,p_mass_adjust_flag IN VARCHAR2 := 'N'
,p_apply_progress_flag IN VARCHAR2 := 'N'
,p_wp_cost_changed_flag IN VARCHAR2 := 'N'
,p_time_phase_changed_flag IN VARCHAR2 := 'N'
,p_quantity_adj_pct IN NUMBER := NULL
,p_cost_rate_adj_pct IN NUMBER := NULL
,p_burdened_rate_adj_pct IN NUMBER := NULL
,p_bill_rate_adj_pct IN NUMBER := NULL
,p_raw_cost_adj_pct IN NUMBER := NULL
,p_burden_cost_adj_pct IN NUMBER := NULL
,p_revenue_adj_pct IN NUMBER := NULL
,p_source_context IN pa_fp_res_assignments_tmp.source_context%TYPE
,p_calling_module IN VARCHAR2 DEFAULT 'UPDATE_PLAN_TRANSACTION'
,p_activity_code IN VARCHAR2 DEFAULT 'CALCULATE'
,p_resource_assignment_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_delete_budget_lines_tab IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type()
,p_spread_amts_flag_tab IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type()
,p_txn_currency_code_tab IN SYSTEM.pa_varchar2_15_tbl_type DEFAULT SYSTEM.pa_varchar2_15_tbl_type()
,p_txn_currency_override_tab IN SYSTEM.pa_varchar2_15_tbl_type DEFAULT SYSTEM.pa_varchar2_15_tbl_type()
,p_total_qty_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_addl_qty_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_total_raw_cost_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_addl_raw_cost_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_total_burdened_cost_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_addl_burdened_cost_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_total_revenue_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_addl_revenue_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_raw_cost_rate_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_rw_cost_rate_override_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_b_cost_rate_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_b_cost_rate_override_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_bill_rate_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_bill_rate_override_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_line_start_date_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
,p_line_end_date_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
/* Added for Spread enhancements */
,p_mfc_cost_type_id_old_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_mfc_cost_type_id_new_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_spread_curve_id_old_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_spread_curve_id_new_tab IN SYSTEM.pa_num_tbl_type DEFAULT SYSTEM.pa_num_tbl_type()
,p_sp_fixed_date_old_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
,p_sp_fixed_date_new_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
,p_plan_start_date_old_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
,p_plan_start_date_new_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
,p_plan_end_date_old_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
,p_plan_end_date_new_tab IN SYSTEM.pa_date_tbl_type DEFAULT SYSTEM.pa_date_tbl_type()
,p_re_spread_flag_tab IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type()
,p_rlm_id_change_flag_tab IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type()
,p_del_spread_calc_tmp1_flg IN VARCHAR2 := 'Y' /* Bug: 4309290.Added the parameter to identify if
PA_FP_SPREAD_CALC_TMP1 is to be deleted or not. Frm AMG flow
we will pass N and for other calls to calculate api it would
be yes */
,p_fp_task_billable_flag_tab IN SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type() /* default 'D' */
,p_clientExtn_api_call_flag IN VARCHAR2 DEFAULT 'Y'
,p_raTxn_rollup_api_call_flag IN VARCHAR2 DEFAULT 'Y' /* Bug fix:4900436 */
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT VARCHAR2) IS
l_tab_count NUMBER;
l_delete_budget_lines VARCHAR2(1);
l_calling_module Varchar2(240) := NVL(p_calling_module,'UPDATE_PLAN_TRANSACTION');
l_delete_budget_lines_tab SYSTEM.pa_varchar2_1_tbl_type DEFAULT SYSTEM.pa_varchar2_1_tbl_type();
l_num_rowsdeleted Number := 0;
select sum(txn_burdened_cost) l_txn_burdened_cost_total_bl,
sum(txn_raw_cost) l_txn_raw_cost_total_bl
from pa_fp_rollup_tmp
where budget_version_id = p_budget_version_id
and resource_assignment_id = p_resource_asg_id;
SELECT resource_assignment_id
,txn_currency_code
FROM pa_budget_lines bl
WHERE bl.budget_version_id = p_budget_version_id
GROUP BY resource_assignment_id,txn_currency_code;
SELECT nvl(pfo.use_planning_rates_flag,'N') use_planning_rates_flag
,decode(nvl(bv.wp_version_flag,'N'),'Y',NVL(pfo.track_workplan_costs_flag,'N'),'Y') track_workplan_costs_flag
,bv.version_type
,bv.resource_list_id
,bv.approved_rev_plan_type_flag
,nvl(pfo.plan_in_multi_curr_flag,'N') plan_in_multi_curr_flag
,bv.etc_start_date
,nvl(bv.wp_version_flag,'N') wp_version_flag
,decode(bv.version_type,
'COST',NVL(pfo.cost_time_phased_code,'N'),
'REVENUE',NVL(pfo.revenue_time_phased_code,'N'),
NVL(pfo.all_time_phased_code,'N')) time_phased_code
,bv.project_structure_version_id
,bv.project_id
,pp.project_currency_code
,pp.projfunc_currency_code
,pp.segment1 project_Name
,bv.ci_id CiId
/*Bugfix:4272944 */
,NVL(pp.baseline_funding_flag,'N') baseline_funding_flag
FROM pa_proj_fp_options pfo
,pa_budget_versions bv
,pa_projects_all pp
WHERE pfo.fin_plan_version_id = bv.budget_version_id
AND bv.project_id = pp.project_id
AND bv.budget_version_id = p_budget_version_id;
SELECT txn_currency_code
FROM pa_budget_lines
WHERE resource_assignment_id = l_resource_assignment_id
ORDER BY start_date;
SELECT ra.resource_assignment_id
,ra.budget_version_id
,ra.project_id
,ra.task_id
,ra.resource_list_member_id
,ra.planning_start_date
,ra.planning_end_date
,ra.spread_curve_id
,ra.etc_method_code
,ra.resource_class_code
,ra.mfc_cost_type_id
,ra.sp_fixed_date
,ra.rate_based_flag
,rl.alias Resource_Name
FROM pa_resource_assignments ra
,pa_resource_list_members rl
WHERE ra.resource_assignment_id = p_resource_asg_id
AND ra.resource_list_member_id = rl.resource_list_member_id ;
SELECT tmp.rowid
,tmp.budget_line_id
,tmp.resource_assignment_id
,tmp.txn_currency_code
,tmp.start_date
,tmp.end_date
,tmp.period_name
,tmp.quantity
,tmp.txn_raw_cost
,tmp.cost_rate
,tmp.rw_cost_rate_override
,tmp.txn_burdened_cost
,tmp.burden_cost_rate
,tmp.burden_cost_rate_override
,tmp.txn_revenue
,tmp.bill_rate
,tmp.bill_rate_override
,tmp.pm_product_code
,bv.version_type
,ra.resource_list_member_id
,ra.rate_based_flag
,ra.task_id
,rl.resource_id
,tmp.cost_rejection_code
,tmp.burden_rejection_code
,tmp.revenue_rejection_code
-- Bug 6781055
,tmp.init_quantity
,tmp.txn_init_raw_cost
,tmp.txn_init_burdened_cost
,tmp.txn_init_revenue
-- End for 6781055
FROM pa_fp_rollup_tmp tmp
,pa_budget_versions bv
,pa_resource_assignments ra
,pa_resource_list_members rl
WHERE bv.budget_version_id = p_budget_version_id
AND ra.budget_version_id = bv.budget_version_id
AND tmp.resource_assignment_id = ra.resource_assignment_id
AND ra.resource_list_member_id = rl.resource_list_member_id
ORDER BY tmp.resource_assignment_id
,tmp.start_date
,tmp.txn_currency_code;
SELECT pt.name task_name
,prl.alias resource_name
FROM pa_proj_elements pt
,pa_resource_list_members prl
,pa_resource_assignments pra
WHERE pra.resource_assignment_id = p_resource_assignment_id
AND pt.proj_element_id(+) = pra.task_id
AND prl.resource_list_member_id = pra.resource_list_member_id;
/* Bug fix:4263265 the following variables declared to keep track of what is changed before deleteing
* after deleting budget lines. these variables stores the original changed values passed to calculate
* by comparing with the budget lines */
l_org_quantity_changed_flag Varchar2(10) := 'N';
/* declared the following tbl for client extn bulk update */
l_cl_txn_plan_quantity_tab pa_plsql_datatypes.NumTabTyp;
g_ipm_ra_id_tab.delete;
g_ipm_curr_code_tab.delete;
g_ipm_cost_rate_ovr_tab.delete;
g_ipm_bill_rate_ovr_tab.delete;
g_ipm_burden_rate_ovr_tab.delete;
gl_cl_roll_up_tmp_rowid_tab.delete;
l_resource_assignment_tab.delete;
l_delete_budget_lines_tab.delete;
l_spread_amts_flag_tab.delete;
l_txn_currency_code_tab.delete;
l_txn_currency_override_tab.delete;
l_total_qty_tab.delete;
l_addl_qty_tab.delete;
l_total_raw_cost_tab.delete;
l_addl_raw_cost_tab.delete;
l_total_burdened_cost_tab.delete;
l_addl_burdened_cost_tab.delete;
l_total_revenue_tab.delete;
l_addl_revenue_tab.delete;
l_raw_cost_rate_tab.delete;
l_rw_cost_rate_override_tab.delete;
l_b_cost_rate_tab.delete;
l_b_cost_rate_override_tab.delete;
l_bill_rate_tab.delete;
l_bill_rate_override_tab.delete;
l_line_start_date_tab.delete;
l_line_end_date_tab.delete;
g_apply_progress_flag_tab.delete;
l_spread_curve_id_old_tab.delete;
l_spread_curve_id_new_tab.delete;
l_sp_fixed_date_old_tab.delete;
l_sp_fixed_date_new_tab.delete;
l_plan_start_date_old_tab.delete;
l_plan_start_date_new_tab.delete;
l_plan_end_date_old_tab.delete;
l_plan_end_date_new_tab.delete;
l_re_spread_flag_tab.delete;
l_sp_curve_change_flag_tab.delete;
l_plan_dates_change_flag_tab.delete;
l_spfix_date_flag_tab.delete;
l_mfc_cost_change_flag_tab.delete;
l_mfc_cost_type_id_old_tab.delete;
l_mfc_cost_type_id_new_tab.delete;
l_rlm_id_change_flag_tab.delete;
l_plan_sdate_shrunk_flag_tab.delete;
l_plan_edate_shrunk_flag_tab.delete;
l_mfc_cost_refresh_flag_tab.delete;
l_ra_in_multi_cur_flag_tab.delete;
l_quantity_changed_flag_tab.delete;
l_raw_cost_changed_flag_tab.delete;
l_cost_rate_changed_flag_tab.delete;
l_burden_cost_changed_flag_tab.delete;
l_burden_rate_changed_flag_tab.delete;
l_rev_changed_flag_tab.delete;
l_bill_rate_changed_flag_tab.delete;
l_multicur_plan_start_date_tab.delete;
l_multicur_plan_end_date_tab.delete;
l_fp_task_billable_flag_tab.delete;
l_cost_rt_miss_num_flag_tab.delete;
l_burd_rt_miss_num_flag_tab.delete;
l_bill_rt_miss_num_flag_tab.delete;
l_Qty_miss_num_flag_tab.delete;
l_Rw_miss_num_flag_tab.delete;
l_Br_miss_num_flag_tab.delete;
l_Rv_miss_num_flag_tab.delete;
l_rev_only_entry_flag_tab.delete;
l_neg_Qty_Changflag_tab.delete;
l_neg_Raw_Changflag_tab.delete;
l_neg_Burd_Changflag_tab.delete;
l_neg_rev_Changflag_tab.delete;
is to be deleted or not. Frm AMG flow we will pass N and for
other calls to calculate api it would be yes*/
Init_SpreadCalc_Tbls(p_del_spread_calc_tmp1_flg => p_del_spread_calc_tmp1_flg);
,p_delete_budget_lines_tab => p_delete_budget_lines_tab
,p_spread_amts_flag_tab => p_spread_amts_flag_tab
,p_txn_currency_code_tab => p_txn_currency_code_tab
,p_txn_currency_override_tab => p_txn_currency_override_tab
,p_total_qty_tab => p_total_qty_tab
,p_addl_qty_tab => p_addl_qty_tab
,p_total_raw_cost_tab => p_total_raw_cost_tab
,p_addl_raw_cost_tab => p_addl_raw_cost_tab
,p_total_burdened_cost_tab => p_total_burdened_cost_tab
,p_addl_burdened_cost_tab => p_addl_burdened_cost_tab
,p_total_revenue_tab => p_total_revenue_tab
,p_addl_revenue_tab => p_addl_revenue_tab
,p_raw_cost_rate_tab => p_raw_cost_rate_tab
,p_rw_cost_rate_override_tab => p_rw_cost_rate_override_tab
,p_b_cost_rate_tab => p_b_cost_rate_tab
,p_b_cost_rate_override_tab => p_b_cost_rate_override_tab
,p_bill_rate_tab => p_bill_rate_tab
,p_bill_rate_override_tab => p_bill_rate_override_tab
,p_line_start_date_tab => p_line_start_date_tab
,p_line_end_date_tab => p_line_end_date_tab
/* added for enhancements */
,p_spread_curve_id_old_tab => p_spread_curve_id_old_tab
,p_spread_curve_id_new_tab => p_spread_curve_id_new_tab
,p_sp_fixed_date_old_tab => p_sp_fixed_date_old_tab
,p_sp_fixed_date_new_tab => p_sp_fixed_date_new_tab
,p_plan_start_date_old_tab => p_plan_start_date_old_tab
,p_plan_start_date_new_tab => p_plan_start_date_new_tab
,p_plan_end_date_old_tab => p_plan_end_date_old_tab
,p_plan_end_date_new_tab => p_plan_end_date_new_tab
,p_re_spread_flag_tab => p_re_spread_flag_tab
,p_sp_curve_change_flag_tab => l_sp_curve_change_flag_tab
,p_plan_dates_change_flag_tab => l_plan_dates_change_flag_tab
,p_spfix_date_flag_tab => l_spfix_date_flag_tab
,p_mfc_cost_change_flag_tab => l_mfc_cost_change_flag_tab
,p_mfc_cost_type_id_old_tab => p_mfc_cost_type_id_old_tab
,p_mfc_cost_type_id_new_tab => p_mfc_cost_type_id_new_tab
,p_rlm_id_change_flag_tab => p_rlm_id_change_flag_tab
,p_fp_task_billable_flag_tab => p_fp_task_billable_flag_tab
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data);
l_delete_budget_lines_tab := p_delete_budget_lines_tab;
*is removed and added a new api inside the PAFPCL1B.pls insert_spread_calctmp_records
**************End of Bug fix:5309529 *****************************************/
l_pls_end_time := dbms_utility.get_time;
* plan update and plan delete pji api. so set the rollup required flag to N */
/* Bug fix:4189762 discussed with sakthi,saima and sanjay, during apply progress mode no need to call the rollup
* as the progress API is calling plan_delete and plan_create rollup API to improve the perf
*/
IF (p_calling_module IN ('BUDGET_GENERATION','FORECAST_GENERATION')
/* Reverting back:OR NVL(p_apply_progress_flag,'N') = 'Y' as the 4189762 is NOT working properly */
OR NVL(G_AGR_CONV_REQD_FLAG,'N') = 'Y' ) Then
g_rollup_required_flag := 'N';
,x_delete_budget_lines_tab =>l_delete_budget_lines_tab
,x_spread_amts_flag_tab =>l_spread_amts_flag_tab
,x_txn_currency_code_tab =>l_txn_currency_code_tab
,x_txn_currency_override_tab =>l_txn_currency_override_tab
,x_total_qty_tab =>l_total_qty_tab
,x_addl_qty_tab =>l_addl_qty_tab
,x_total_raw_cost_tab =>l_total_raw_cost_tab
,x_addl_raw_cost_tab =>l_addl_raw_cost_tab
,x_total_burdened_cost_tab =>l_total_burdened_cost_tab
,x_addl_burdened_cost_tab =>l_addl_burdened_cost_tab
,x_total_revenue_tab =>l_total_revenue_tab
,x_addl_revenue_tab =>l_addl_revenue_tab
,x_raw_cost_rate_tab =>l_raw_cost_rate_tab
,x_rw_cost_rate_override_tab =>l_rw_cost_rate_override_tab
,x_b_cost_rate_tab =>l_b_cost_rate_tab
,x_b_cost_rate_override_tab =>l_b_cost_rate_override_tab
,x_bill_rate_tab =>l_bill_rate_tab
,x_bill_rate_override_tab =>l_bill_rate_override_tab
,x_line_start_date_tab =>l_line_start_date_tab
,x_line_end_date_tab =>l_line_end_date_tab
,x_apply_progress_flag_tab =>g_apply_progress_flag_tab
,x_spread_curve_id_old_tab =>l_spread_curve_id_old_tab
,x_spread_curve_id_new_tab =>l_spread_curve_id_new_tab
,x_sp_fixed_date_old_tab =>l_sp_fixed_date_old_tab
,x_sp_fixed_date_new_tab =>l_sp_fixed_date_new_tab
,x_plan_start_date_old_tab =>l_plan_start_date_old_tab
,x_plan_start_date_new_tab =>l_plan_start_date_new_tab
,x_plan_end_date_old_tab =>l_plan_end_date_old_tab
,x_plan_end_date_new_tab =>l_plan_end_date_new_tab
,x_re_spread_flag_tab =>l_re_spread_flag_tab
,x_sp_curve_change_flag_tab =>l_sp_curve_change_flag_tab
,x_plan_dates_change_flag_tab =>l_plan_dates_change_flag_tab
,x_spfix_date_flag_tab =>l_spfix_date_flag_tab
,x_mfc_cost_change_flag_tab =>l_mfc_cost_change_flag_tab
,x_mfc_cost_type_id_old_tab =>l_mfc_cost_type_id_old_tab
,x_mfc_cost_type_id_new_tab =>l_mfc_cost_type_id_new_tab
,x_rlm_id_change_flag_tab =>l_rlm_id_change_flag_tab
,x_plan_sdate_shrunk_flag_tab =>l_plan_sdate_shrunk_flag_tab
,x_plan_edate_shrunk_flag_tab =>l_plan_edate_shrunk_flag_tab
,x_mfc_cost_refresh_flag_tab =>l_mfc_cost_refresh_flag_tab
,x_ra_in_multi_cur_flag_tab =>l_ra_in_multi_cur_flag_tab
,x_quantity_changed_flag_tab =>l_quantity_changed_flag_tab
,x_raw_cost_changed_flag_tab =>l_raw_cost_changed_flag_tab
,x_cost_rate_changed_flag_tab =>l_cost_rate_changed_flag_tab
,x_burden_cost_changed_flag_tab =>l_burden_cost_changed_flag_tab
,x_burden_rate_changed_flag_tab =>l_burden_rate_changed_flag_tab
,x_rev_changed_flag_tab =>l_rev_changed_flag_tab
,x_bill_rate_changed_flag_tab =>l_bill_rate_changed_flag_tab
,x_multcur_plan_start_date_tab =>l_multicur_plan_start_date_tab
,x_multcur_plan_end_date_tab =>l_multicur_plan_end_date_tab
,x_fp_task_billable_flag_tab => l_fp_task_billable_flag_tab
,x_cost_rt_miss_num_flag_tab => l_cost_rt_miss_num_flag_tab
,x_burd_rt_miss_num_flag_tab => l_burd_rt_miss_num_flag_tab
,x_bill_rt_miss_num_flag_tab => l_bill_rt_miss_num_flag_tab
,x_Qty_miss_num_flag_tab => l_Qty_miss_num_flag_tab
,x_Rw_miss_num_flag_tab => l_Rw_miss_num_flag_tab
,x_Br_miss_num_flag_tab => l_Br_miss_num_flag_tab
,x_Rv_miss_num_flag_tab => l_Rv_miss_num_flag_tab
,x_rev_only_entry_flag_tab => l_rev_only_entry_flag_tab
/* bug fix:5726773 */
,x_neg_Qty_Changflag_tab => l_neg_Qty_Changflag_tab
,x_neg_Raw_Changflag_tab => l_neg_Raw_Changflag_tab
,x_neg_Burd_Changflag_tab => l_neg_Burd_Changflag_tab
,x_neg_rev_Changflag_tab => l_neg_rev_Changflag_tab
,x_return_status => l_return_status
,x_msg_data => x_msg_data
);
/* update resource assignments set planning end date as etc start date for the fixed date
* spread curves where more than one budget line exists
*/
PreProcess_BlkProgress_lines
(p_budget_version_id => g_budget_version_id
,p_etc_start_date => g_spread_from_date
,p_apply_progress_flag => p_apply_progress_flag
,x_return_status => l_return_status
,x_msg_data => x_msg_data
);
/* IPM changes: Whenever budget lines get deleted, rollup the amts to new entity
* otherwise this causes data corruption of amounts and quantity get doubled during apply progress
*/
IF p_source_context = 'RESOURCE_ASSIGNMENT' AND
NVL(p_raTxn_rollup_api_call_flag,'Y') = 'Y' AND nvl(l_entire_return_status,'S') = 'S' Then
If P_PA_DEBUG_MODE = 'Y' Then
print_msg('Calling populate_raTxn_Recs API for rollup of budgetlines during apply progress');
delete_raTxn_Tmp;
,p_delete_flag => 'N'
,p_delete_raTxn_flag => 'N'
,p_refresh_rate_flag => 'N'
,p_rollup_flag => 'Y'
,p_call_raTxn_rollup_flag => 'Y'
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
Use BEGIN here to allow for the skip record exception if required parameters needed to update
budget lines are all null.
*/
BEGIN
l_stage := 220;
*Select pa_resource_assignment attributes required for the rate API
*/
l_stage := 250;
*Assign local variables values selected from the resource_assignemt cursur
*/
l_stage := 270;
l_delete_budget_lines := NULL;
IF l_delete_budget_lines_tab.EXISTS(i) Then
If NVL(l_delete_budget_lines,'N') = 'N' Then -- this is set 'Y' at override currency changed
--print_msg('l_delete_budget_lines_tab(i) ['||l_delete_budget_lines_tab(i)||']');
l_delete_budget_lines := l_delete_budget_lines_tab(i);
l_delete_budget_lines := 'Y';
l_delete_budget_lines := 'Y';
If NVL(l_delete_budget_lines,'N') <> 'Y' Then
g_mfc_cost_refresh_tab(l_countr) := 'Y';
print_msg('l_rlm_id_change_flag['||l_rlm_id_change_flag||']l_delete_budget_lines['||l_delete_budget_lines||']');
ElsIF (NVL(l_delete_budget_lines,'N') <> 'Y' AND l_quantity_changed_flag = 'N'
and NVL(p_apply_progress_flag,'N') <> 'Y' ) Then
g_mfc_cost_refresh_tab(l_countr) := 'Y';
IF l_delete_budget_lines = 'Y' THEN
If P_PA_DEBUG_MODE = 'Y' Then
print_msg(to_char(l_stage)||' Delete from pa_budget_lines, rollup pfc numbers, and skip record');
pa_fp_calc_plan_pkg.delete_budget_lines
(p_budget_version_id => p_budget_version_id
,p_resource_assignment_id => l_resource_assignment_id
,p_txn_currency_code => l_txn_currency_code
,p_line_start_date => g_line_start_date
,p_line_end_date => g_line_end_date
,p_source_context => g_source_context
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
,x_num_rowsdeleted => l_num_rowsdeleted
);
print_msg('Number of budgetLines deleted['||l_num_rowsdeleted||']retSts['||l_return_status||']');
l_raTxnRec_mode := 'DELETE_BL';
--print_msg('Calling populate_raTxn_Recs API for delete of for Resource attribute changes');
delete_raTxn_Tmp;
,p_delete_flag => 'N'
,p_delete_raTxn_flag => 'N'
,p_refresh_rate_flag => g_refresh_rates_flag
,p_rollup_flag => 'Y'
,p_call_raTxn_rollup_flag => 'Y'
,p_resource_assignment_id => l_resource_assignment_id
,p_txn_currency_code => l_txn_currency_code
,p_start_date => g_line_start_date
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
IF (l_spread_amounts_flag = 'N' OR l_num_rowsdeleted > 0) Then
l_spread_amounts_flag := 'Y';
END IF; --IF l_delete_budget_lines = 'Y'
the ETC qty should be respread, so delete all the budget lines after the etc start date
and respread the given ETC so this shoul go through normal flow.
so set the g_apply_progress_flag to 'N'.
When actuals Exists, by pass the apply precedence rules,
*/
IF g_apply_progress_flag_tab.EXISTS(i) THEN
l_apply_progress_flag := g_apply_progress_flag_tab(i);
SELECT rtx.txn_burden_cost_rate_override
,rtx.txn_bill_rate_override
,tmp.bill_markup_percentage
,rtx.txn_raw_cost_rate_override
INTO l_curr_burden_rate
,l_curr_bill_rate
,l_curr_markup_percentage
,l_curr_cost_rate
FROM pa_resource_asgn_curr rtx
,pa_fp_spread_calc_tmp tmp
WHERE tmp.resource_assignment_id = l_resource_assignment_id
AND tmp.txn_currency_code = l_txn_currency_code
AND rtx.resource_assignment_id = tmp.resource_assignment_id
AND rtx.txn_currency_code = tmp.txn_currency_code;
SELECT AVG(bl.burden_cost_rate_override)
,AVG(bl.txn_bill_rate_override)
,AVG(bl.txn_markup_percent)
,AVG(bl.txn_cost_rate_override)
INTO l_curr_burden_rate
,l_curr_bill_rate
,l_curr_markup_percentage
,l_curr_cost_rate
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = l_resource_assignment_id
AND bl.txn_currency_code = l_txn_currency_code
AND bl.start_date between l_bdgt_line_sDate and l_bdgt_line_eDate;
* copy budget lines to rollup tmp and update the changed rates
* doing this will improve performance by 50%
*/
IF NVL(l_txn_plan_quantity_addl,0) = 0
AND ( (l_raw_cost_changed_flag = 'Y' AND
l_rate_based_flag = 'Y')
OR l_rw_cost_rate_changed_flag = 'Y'
OR l_burden_cost_changed_flag = 'Y'
OR l_b_cost_rate_changed_flag ='Y'
OR l_rev_changed_flag = 'Y'
OR l_bill_rate_changed_flag = 'Y'
OR l_bill_rt_ovr_changed_flag = 'Y' ) Then
g_rtChanged_Ra_Flag_tab(l_countr) := 'Y';
--print_msg('283: set the rate change flag to Y so that it goes throguh refresh mode and updates');
/* If delete flag is passed in budget line context then, spread or refresh should not be called
* just delete the budget lines */
IF p_source_context = 'BUDGET_LINE'
AND l_delete_budget_lines = 'Y' THEN
g_rtChanged_Ra_Flag_tab(l_countr) := 'N';
print_msg(' l_delete_budget_lines['||l_delete_budget_lines||']l_spread_amounts_flag['||l_spread_amounts_flag||']');
print_msg(to_char(l_stage)||' ***ERROR*** Inserting into pa_fp_res_assignments_tmp');
pa_debug.g_err_stage := to_char(l_stage)||': ***ERROR*** Inserting into pa_fp_res_assignments_tmp';
/* Now update the spread_calc tmp table with the required flags to process in bulk */
If P_PA_DEBUG_MODE = 'Y' Then
print_msg('Calling Upd_spread_calc_tmp API');
delete_raTxn_Tmp;
,p_delete_flag => 'N'
,p_delete_raTxn_flag => 'Y'
,p_rollup_flag => 'Y'
,p_call_raTxn_rollup_flag => p_raTxn_rollup_api_call_flag -- Changed from 'Y'
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
print_msg(l_stage||'Calling Update_rollupTmp_OvrRates API with genrationContext['||l_generation_context||']');
Update_rollupTmp_OvrRates
( p_budget_version_id => g_budget_version_id
,p_calling_module => l_calling_module
,p_generation_context => l_generation_context
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
print_plsql_time('End of Update_rollupTmp_OvrRates:Total time :['||(l_pls_end_time-l_pls_start_time)||']');
print_msg('After calling Update_rollupTmp_OvrRates api retSts['||l_return_status||']msgdata['||x_msg_data||']');
delete_raTxn_Tmp;
,p_delete_flag => 'N'
,p_delete_raTxn_flag => 'N'
,p_rollup_flag => 'N'
,p_call_raTxn_rollup_flag => p_raTxn_rollup_api_call_flag --'Y'
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
/*Bug 4224464. This API would be called to insert into pa_fp_rollup_tmp all those budget lines which are not already present in
pa_fp_rollup_tmp. These lines would be the ones with no changes to qty/amnt and rate columns. The lines with changes to
qty/amnt and rate columns would not be processed by this API as earlier call to copy_blattributes would have handled these
lines Also using the same signature for this API as used for copy_blattributes above.*/
/* This call to update_dffcols is required for non AMG flows too but currently doing for AMG flow only
*.as the fix is done for AMG rollup. Will ask Ranga to evaluate this for non-AMG flows and remove the if condition.
*.This can then be put in FP M rollup with extensive QE testing */
IF ( p_calling_module = 'AMG_API' )
THEN
l_pls_start_time := dbms_utility.get_time;
PA_FP_CALC_UTILS.update_dffcols(
p_budget_verson_id => g_budget_version_id
,p_source_context => g_source_context
,p_calling_module => l_calling_module
,p_apply_progress_flag => p_apply_progress_flag
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => x_msg_data
);
print_plsql_time('End of update_diffcols:Total time :['||(l_pls_end_time-l_pls_start_time)||']');
print_msg('returnSts of PA_FP_CALC_UTILS.update_dff['||l_return_status||']');
SELECT decode(nvl(l_return_status,'S'),'E','E'
,'U','U'
,'S',decode(nvl(l_entire_return_status,'S')
,'E','E'
,'U','U'
,'S','S'),'E')
INTO l_return_status
FROM dual;
l_cl_txn_plan_quantity_tab.delete;
l_cl_txn_raw_cost_tab.delete;
l_cl_txn_burdened_cost_tab.delete;
l_cl_txn_revenue_tab.delete;
l_cl_cost_rate_override_tab.delete;
l_cl_burden_rate_override_tab.delete;
l_cl_bill_rate_override_tab.delete;
l_cl_budget_line_id_tab.delete;
l_cl_raw_rejection_code_tab.delete;
l_cl_burd_rejection_code_tab.delete;
l_cl_rev_rejection_code_tab.delete;
/*print_msg(' AFTER client extn Values UPDATE pa_fp_rollup_tmp');
UPDATE pa_fp_rollup_tmp tmp
SET tmp.quantity = l_cl_txn_plan_quantity_tab(i)
,tmp.txn_raw_cost = l_cl_txn_raw_cost_tab(i)
,tmp.txn_burdened_cost = l_cl_txn_burdened_cost_tab(i)
,tmp.txn_revenue = l_cl_txn_revenue_tab(i)
,tmp.rw_cost_rate_override = NVL(l_cl_cost_rate_override_tab(i),tmp.rw_cost_rate_override)
,tmp.burden_cost_rate_override = nvl(l_cl_burden_rate_override_tab(i),tmp.burden_cost_rate_override)
,tmp.bill_rate_override = nvl(l_cl_bill_rate_override_tab(i),tmp.bill_rate_override)
,tmp.cost_rejection_code = l_cl_raw_rejection_code_tab(i)
,tmp.burden_rejection_code = l_cl_burd_rejection_code_tab(i)
,tmp.revenue_rejection_code = l_cl_rev_rejection_code_tab(i)
WHERE tmp.budget_line_id = l_cl_budget_line_id_tab(i);
/* Rounding Enhancements: Update the last rollup tmp line with the rounding discrepancy amounts */
IF NVL(l_entire_return_status,'S') = 'S' AND NVL(g_track_wp_costs_flag,'Y') = 'Y' THEN
l_pls_start_time := dbms_utility.get_time;
Update_rounding_diff(
p_project_id => p_project_id
,p_budget_version_id => g_budget_version_id
,p_calling_module => l_calling_module
,p_source_context => g_source_context
,p_wp_cost_enabled_flag => g_track_wp_costs_flag
,p_budget_version_type => g_fp_budget_version_type
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => l_msg_data --5028631
);
print_plsql_time('End of Update_rounding_diff:Total time :['||(l_pls_end_time-l_pls_start_time)||']');
update pa_fp_rollup_tmp
set txn_burdened_cost = (nvl(txn_burdened_cost,0) + nvl(l_txn_burdened_cost_total,0)-nvl(l_txn_burdened_cost_total_bl,0)),
txn_raw_cost = (nvl(txn_raw_cost,0) + nvl(l_txn_raw_cost_total,0)-nvl(l_txn_raw_cost_total_bl,0))
where budget_version_id = p_budget_version_id and resource_assignment_id = l_resource_assignment_tab(j)
and start_date = (select max(start_date) from pa_fp_rollup_tmp where budget_version_id = p_budget_version_id
and resource_assignment_id = l_resource_assignment_tab(j));
Update_PCPFC_rounding_diff(
p_project_id => p_project_id
,p_budget_version_id => g_budget_version_id
,p_calling_module => l_calling_module
,p_source_context => g_source_context
,p_wp_cost_enabled_flag => g_track_wp_costs_flag
,p_budget_version_type => g_fp_budget_version_type
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => l_msg_data --5028631
);
*table pa_budget_lines is ready to be updated with the values stored in pa_fp_rollup_tmp
*It will update pa_budget_lines with ALL rows stored in pa_fp_rollup_tmp
*/
l_stage := 1100;
/* Bug fix: 4184159 moved to bulk update pa_fp_calc_plan_pkg.update_budget_lines */
l_pls_start_time := dbms_utility.get_time;
PA_FP_CALC_UTILS.BLK_update_budget_lines
( p_budget_version_id => p_budget_version_id
,p_calling_module => l_calling_module -- Added for Bug#5395732
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => l_msg_data --5028631
);
print_plsql_time('End of BLK UpdateBudget Total time :['||(l_pls_end_time-l_pls_start_time)||']');
print_msg('AFter calling update_budget_lines retSTst['||l_return_status||']MsgData['||l_msg_data||']');
delete_raTxn_Tmp;
,p_delete_flag => 'N'
,p_delete_raTxn_flag => 'N'
,p_refresh_rate_flag => g_refresh_rates_flag
,p_rollup_flag => 'Y'
,p_call_raTxn_rollup_flag => 'Y'
,x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => l_msg_data --5028631
);
PA_FP_PJI_INTG_PKG.blk_update_reporting_lines
(p_calling_module => 'CALCULATE_API'
,p_activity_code => 'UPDATE'
,p_budget_version_id => p_budget_version_id
,p_rep_budget_line_id_tab => g_rep_budget_line_id_tab
,p_rep_res_assignment_id_tab => g_rep_res_assignment_id_tab
,p_rep_start_date_tab => g_rep_start_date_tab
,p_rep_end_date_tab => g_rep_end_date_tab
,p_rep_period_name_tab => g_rep_period_name_tab
,p_rep_txn_curr_code_tab => g_rep_txn_curr_code_tab
,p_rep_quantity_tab => g_rep_quantity_tab
,p_rep_txn_raw_cost_tab => g_rep_txn_raw_cost_tab
,p_rep_txn_burdened_cost_tab => g_rep_txn_burdened_cost_tab
,p_rep_txn_revenue_tab => g_rep_txn_revenue_tab
,p_rep_project_curr_code_tab => g_rep_project_curr_code_tab
,p_rep_project_raw_cost_tab => g_rep_project_raw_cost_tab
,p_rep_project_burden_cost_tab => g_rep_project_burden_cost_tab
,p_rep_project_revenue_tab => g_rep_project_revenue_tab
,p_rep_projfunc_curr_code_tab => g_rep_projfunc_curr_code_tab
,p_rep_projfunc_raw_cost_tab => g_rep_projfunc_raw_cost_tab
,p_rep_projfunc_burden_cost_tab => g_rep_projfunc_burden_cost_tab
,p_rep_projfunc_revenue_tab => g_rep_projfunc_revenue_tab
,p_rep_line_mode_tab => g_rep_line_mode_tab
,p_rep_rate_base_flag_tab => g_rep_rate_base_flag_tab
,x_msg_data => l_msg_data --5028631
,x_msg_count => x_msg_count
,x_return_status => l_return_status
);
SELECT lock_flag INTO l_locked
FROM pji_pjp_wbs_header where plan_version_id = p_budget_version_id;
/* Bug fix:4250222: when -100% adjustment of qty is made, the budget lines got deleted but
* RA is not rolled up. so move Delete_BL_Where_Nulls after RA rollup */
l_stage := 1310;
/* Delete all the budget Lines where quantity and amounts are not exists but only the rate
* retaining these budget lines will cause issues in reating the currency for workplan version
* ideally budget lines with null amounts and null quantity with rate make no sense
*/
If P_PA_DEBUG_MODE = 'Y' Then
print_msg(l_stage||'Calling Delete_BL_Where_Nulls API');
--print_plsql_time('Start of Delete_BL_Where_Nulls :['||l_pls_start_time);
Delete_BL_Where_Nulls
( p_budget_version_id => p_budget_version_id
,p_resource_assignment_tab => l_resource_assignment_tab
,x_return_status => l_return_status
,x_msg_data => l_msg_data --5028631
);
print_plsql_time('End of Delete_BL_Where_NullsTotal time :['||(l_pls_end_time-l_pls_start_time)||']');
print_msg('AFter calling Delete_BL_Where_Nulls retSTst['||l_return_status||']MsgData['||l_msg_data||']');
print_msg(to_char(l_stage)||' Calling insert_txn_currency api');
pa_fp_gen_budget_amt_pub.insert_txn_currency
(p_project_id => g_project_id,
p_budget_version_id => g_budget_version_id,
p_fp_cols_rec => l_fp_cols_rec,
x_return_status => l_return_status
,x_msg_count => x_msg_count
,x_msg_data => l_msg_data --5028631
);
print_msg('AFter calling pa_fp_gen_budget_amt_pub.insert_txn_currency API retSts['||l_return_status||']x_msg_date['||l_msg_data||']');
SELECT /*+ INDEX(CALTMP PA_FP_SPREAD_CALC_TMP_N1) */ caltmp.resource_assignment_id
,caltmp.txn_currency_code
,caltmp.start_date
,caltmp.end_date
,caltmp.txn_curr_code_override
,caltmp.G_WPRABL_CURRENCY_CODE
,ra.project_id
,ra.task_id
,ra.budget_version_id
,NVL(ra.rate_based_flag,'N') rate_based_flag
,caltmp.task_name
,caltmp.resource_name
,caltmp.billable_flag
FROM pa_fp_spread_calc_tmp caltmp
,pa_resource_assignments ra
WHERE caltmp.budget_version_id = p_budget_version_id
AND caltmp.resource_assignment_id = ra.resource_assignment_id
AND ( NVL(caltmp.skip_record_flag,'N') <> 'Y'
OR
( NVL(caltmp.skip_record_flag,'N') = 'Y'
and NVL(caltmp.processed_flag,'N') = 'Y'
)
);
SELECT /*+LEADING(TMP) INDEX(TMP PA_FP_ROLLUP_TMP_N1)*/ ra.resource_assignment_id -- bug 4873834
,tmp.txn_currency_code
,tmp.quantity
,tmp.start_date
,tmp.budget_line_id
,tmp.burden_cost_rate_override
,tmp.rw_cost_rate_override
,tmp.bill_rate_override
,tmp.txn_raw_cost
,tmp.txn_burdened_cost
,tmp.txn_revenue
,ra.task_id
,ra.resource_list_member_id
,ra.unit_of_measure
,ra.standard_bill_rate
,ra.wbs_element_version_id
,ra.rbs_element_id
,ra.planning_start_date
,ra.planning_end_date
,ra.spread_curve_id
,ra.etc_method_code
,ra.res_type_code
,ra.fc_res_type_code
,ra.resource_class_code
,ra.organization_id
,ra.job_id
,ra.person_id
,ra.expenditure_type
,ra.expenditure_category
,ra.revenue_category_code
,ra.event_type
,ra.supplier_id
,ra.non_labor_resource
,ra.bom_resource_id
,ra.inventory_item_id
,ra.item_category_id
,ra.billable_percent
,ra.mfc_cost_type_id
,ra.incurred_by_res_flag
,ra.rate_job_id
,ra.rate_expenditure_type
,ra.sp_fixed_date
,ra.person_type_code
,NVL(ra.rate_based_flag,'N') rate_based_flag
,ra.rate_exp_func_curr_code
,ra.rate_expenditure_org_id
,ra.incur_by_res_class_code
,ra.incur_by_role_id
,ra.project_role_id
,ra.resource_class_flag
,ra.named_role
,rl.res_format_id
,tmp.init_quantity
,tmp.txn_init_raw_cost
,tmp.txn_init_burdened_cost
,tmp.txn_init_revenue
/* Bug fix:4294287 */
,tmp.bill_markup_percentage
,NVL(tmp.system_reference6,'Y') markup_calculation_flag
FROM pa_resource_assignments ra
,pa_fp_rollup_tmp tmp
,pa_resource_list_members rl
WHERE tmp.resource_assignment_id = p_resource_assignment_id
AND ra.resource_assignment_id = tmp.resource_assignment_id
AND rl.resource_list_member_id = ra.resource_list_member_id
AND tmp.txn_currency_code = decode(p_txn_curr_code_Ovr,NULL,p_txn_currency_code
,decode(p_txn_curr_code_Ovr,tmp.txn_currency_code,p_txn_curr_code_Ovr,p_txn_currency_code))
/* the decode is added to take care of when override currency changes along with qty the spread api is called
* this will create rollup lines with override curr. when rates changes along with ovrride currency change then
* refresh rates action performed and rollup lines will be created with original txn curr */
AND ((g_source_context <> 'BUDGET_LINE' )
OR
(g_source_context = 'BUDGET_LINE'
and p_line_start_date is NOT NULL and p_line_end_date is NOT NULL
and tmp.start_date BETWEEN p_line_start_date and p_line_end_date)
)
/* added this to avoid processing the same record twice. This may happend due to deriving the final
* currency is moved from update_budget_lines to Rate API.*/
-- commenting out as rate api is called only once AND NVL(tmp.system_reference5,'N') = 'N'
ORDER BY ra.resource_assignment_id,tmp.start_date,tmp.txn_currency_code ;
SELECT decode(nvl(pfo.use_planning_rates_flag,'N'),'N',pfo.res_class_bill_rate_sch_id,
decode(bv.version_type,'REVENUE',pfo.rev_res_class_rate_sch_id,
'ALL' ,pfo.rev_res_class_rate_sch_id,
NULL)) res_class_bill_rate_sch_id
,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',pfo.res_class_raw_cost_sch_id,
decode(bv.version_type,'COST',pfo.cost_res_class_rate_sch_id,
'ALL' ,pfo.cost_res_class_rate_sch_id,
NULL)) res_class_raw_cost_sch_id
,nvl(pfo.use_planning_rates_flag,'N') use_planning_rates_flag
,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',null,
decode(bv.version_type,'REVENUE',pfo.rev_job_rate_sch_id,
'ALL' ,pfo.rev_job_rate_sch_id,
NULL)) rev_job_rate_sch_id
,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',null,
decode(bv.version_type,'COST' ,pfo.cost_job_rate_sch_id,
'ALL' ,pfo.cost_job_rate_sch_id,
NULL)) cost_job_rate_sch_id
,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',null,
decode(bv.version_type,'REVENUE',pfo.rev_emp_rate_sch_id,
'ALL' ,pfo.rev_emp_rate_sch_id,
NULL)) rev_emp_rate_sch_id
,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',null,
decode(bv.version_type,'COST' ,pfo.cost_emp_rate_sch_id,
'ALL' ,pfo.cost_emp_rate_sch_id,
NULL)) cost_emp_rate_sch_id
,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',null,
decode(bv.version_type,'REVENUE',pfo.rev_non_labor_res_rate_sch_id,
'ALL' ,pfo.rev_non_labor_res_rate_sch_id,
NULL)) rev_non_labor_res_rate_sch_id
,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',null,
decode(bv.version_type,'COST' ,pfo.cost_non_labor_res_rate_sch_id,
'ALL' ,pfo.cost_non_labor_res_rate_sch_id,
NULL)) cost_non_labor_res_rate_sch_id
,decode(nvl(pfo.use_planning_rates_flag,'N'),'N',null,
decode(bv.version_type,'COST' ,pfo.cost_burden_rate_sch_id,
'ALL' ,pfo.cost_burden_rate_sch_id,
NULL)) cost_burden_rate_sch_id
,decode(nvl(bv.wp_version_flag,'N'),'Y',NVL(pfo.track_workplan_costs_flag,'N'),'Y') track_workplan_costs_flag
,bv.version_type fp_budget_version_type
,bv.resource_list_id
,nvl(bv.approved_rev_plan_type_flag,'N') approved_rev_plan_type_flag
,nvl(pfo.plan_in_multi_curr_flag,'N') plan_in_multi_curr_flag
,bv.etc_start_date
,nvl(bv.wp_version_flag,'N') wp_version_flag
,pp.assign_precedes_task
,pp.bill_job_group_id
,pp.carrying_out_organization_id
,nvl(pp.multi_currency_billing_flag,'N') multi_currency_billing_flag
,pp.org_id
,pp.non_labor_bill_rate_org_id
,pp.project_currency_code
,pp.non_labor_schedule_discount
,pp.non_labor_schedule_fixed_date
,pp.non_lab_std_bill_rt_sch_id
,pp.project_type
,pp.projfunc_currency_code
,pp.emp_bill_rate_schedule_id
,pp.job_bill_rate_schedule_id
,pp.labor_bill_rate_org_id
,pp.labor_sch_type
,pp.non_labor_sch_type
,bv.project_structure_version_id
,bv.project_id
FROM pa_proj_fp_options pfo
,pa_budget_versions bv
,pa_projects_all pp
WHERE pfo.fin_plan_version_id = bv.budget_version_id
AND bv.budget_version_id = p_budget_version_id
AND pp.project_id = bv.project_id
AND pfo.project_id = pp.project_id;
SELECT non_labor_bill_rate_org_id
,non_labor_schedule_discount
,non_labor_schedule_fixed_date
,non_lab_std_bill_rt_sch_id
,emp_bill_rate_schedule_id
,job_bill_rate_schedule_id
,labor_bill_rate_org_id
,labor_sch_type
,non_labor_sch_type
,top_task_id
FROM pa_tasks t
WHERE t.task_id = p_task_id
AND t.project_id = p_project_id;
/* declared for bulk processing of rollup tmp update */
l_rl_cntr NUMBER := 0;
/* declared for bulk processing of rollup tmp update */
l_rl_cntr := 0;
l_rlt_budget_line_id_tab.delete;
l_rlt_quantity_tab.delete;
l_rlt_bill_rate_tab.delete;
l_rlt_bill_rate_ovr_tab.delete;
l_rlt_cost_rate_tab.delete;
l_rlt_rw_cost_rate_ovr_tab.delete;
l_rlt_burden_cost_rate_tab.delete;
l_rlt_burden_cost_rate_ovr_tab.delete;
l_rlt_raw_cost_tab.delete;
l_rlt_burden_cost_tab.delete;
l_rlt_raw_revenue_tab.delete;
l_rlt_bill_markup_percent_tab.delete;
l_rlt_txn_curr_code_tab.delete;
l_rlt_raw_cost_rejection_tab.delete;
l_rlt_burden_rejection_tab.delete;
l_rlt_revenue_rejection_tab.delete;
l_rlt_projfunc_rejection_tab.delete;
l_rlt_project_rejection_tab.delete;
l_rlt_ind_compiled_set_tab.delete;
* to call an api to update the interface table with the web adi error code
*/
IF l_webAdi_calling_context = 'WEBADI_CALCULATE' THEN
IF l_error_code = 'PA_FP_PROJ_NO_TXNCONVRATE' THEN
-- populating the error tables.
-- calling pa_fp_webadi_pkg.process_errors
--print_msg('ConvErr:Web ADI context collecting errors');
* to call an api to update the interface table with the web adi error code
*/
IF l_webAdi_calling_context = 'WEBADI_CALCULATE' THEN
IF l_error_code = 'PA_FP_PROJ_NO_TXNCONVRATE' THEN
-- populating the error tables.
-- calling pa_fp_webadi_pkg.process_errors
--print_msg('ConvErr:Web ADI context collecting errors');
* to call an api to update the interface table with the web adi error code
*/
IF l_webAdi_calling_context = 'WEBADI_CALCULATE' THEN
IF l_error_code = 'PA_FP_PROJ_NO_TXNCONVRATE' THEN
-- populating the error tables.
-- calling pa_fp_webadi_pkg.process_errors
--print_msg('ConvErr:Web ADI context collecting errors');
* to call an api to update the interface table with the web adi error code
*/
IF l_webAdi_calling_context = 'WEBADI_CALCULATE' THEN
IF l_error_code = 'PA_FP_PROJ_NO_TXNCONVRATE' THEN
-- populating the error tables.
-- calling pa_fp_webadi_pkg.process_errors
--print_msg('ConvErr:Web ADI context collecting errors');
* to call an api to update the interface table with the web adi error code
*/
IF l_webAdi_calling_context = 'WEBADI_CALCULATE' THEN
IF l_error_code = 'PA_FP_PROJ_NO_TXNCONVRATE' THEN
-- populating the error tables.
-- calling pa_fp_webadi_pkg.process_errors
--print_msg('ConvErr:Web ADI context collecting errors');
* to call an api to update the interface table with the web adi error code
*/
IF l_webAdi_calling_context = 'WEBADI_CALCULATE' THEN
IF l_error_code = 'PA_FP_PROJ_NO_TXNCONVRATE' THEN
-- populating the error tables.
-- calling pa_fp_webadi_pkg.process_errors
--print_msg('ConvErr:Web ADI context collecting errors');
print_msg(to_char(l_stage)||'Calling update_rollup_tmp with the following parameters:');
/* Now populate plsql tables for bulk update */
l_rl_cntr := l_rl_cntr + 1;
UPDATE PA_FP_ROLLUP_TMP RL
SET RL.QUANTITY = l_rlt_quantity_tab(i)
,RL.BILL_RATE = l_rlt_bill_rate_tab(i)
,RL.BILL_RATE_OVERRIDE = l_rlt_bill_rate_ovr_tab(i)
,RL.COST_RATE = l_rlt_cost_rate_tab(i)
,RL.RW_COST_RATE_OVERRIDE = l_rlt_rw_cost_rate_ovr_tab(i)
,RL.BURDEN_COST_RATE = l_rlt_burden_cost_rate_tab(i)
,RL.BURDEN_COST_RATE_OVERRIDE = l_rlt_burden_cost_rate_ovr_tab(i)
,RL.TXN_RAW_COST = l_rlt_raw_cost_tab(i)
,RL.TXN_BURDENED_COST = l_rlt_burden_cost_tab(i)
,RL.TXN_REVENUE = l_rlt_raw_revenue_tab(i)
,RL.BILL_MARKUP_PERCENTAGE = l_rlt_bill_markup_percent_tab(i)
,RL.TXN_CURRENCY_CODE = l_rlt_txn_curr_code_tab(i)
,RL.COST_REJECTION_CODE = l_rlt_raw_cost_rejection_tab(i)
,RL.BURDEN_REJECTION_CODE = l_rlt_burden_rejection_tab(i)
,RL.REVENUE_REJECTION_CODE = l_rlt_revenue_rejection_tab(i)
,RL.PFC_CUR_CONV_REJECTION_CODE = l_rlt_projfunc_rejection_tab(i)
,RL.PC_CUR_CONV_REJECTION_CODE = l_rlt_project_rejection_tab(i)
,RL.COST_IND_COMPILED_SET_ID = l_rlt_ind_compiled_set_tab(i)
,RL.SYSTEM_REFERENCE5 = 'Y'
WHERE RL.BUDGET_LINE_ID = l_rlt_budget_line_id_tab(i);
,p_procedure_name => 'Get_Res_Rates:Update_rollupTmp_OvrRates');
Select project option attibutes required for RATE API and assign to local variables
This only needs to be done once during the procedure
*/
ProjFpOptRec := NULL;
for i in (select * from pa_fp_spread_calc_tmp ) LOOP
print_msg('IN params ResId['||i.resource_assignment_id||']TxnCur['||i.txn_currency_code||']RefrFlag['||i.refresh_rates_flag||']');
INSERT INTO pa_fp_rollup_tmp (
budget_version_id
,resource_assignment_id
,start_date
,end_date
,period_name
,quantity
,projfunc_raw_cost
,projfunc_burdened_cost
,projfunc_revenue
,cost_rejection_code
,revenue_rejection_code
,burden_rejection_code
,projfunc_currency_code
,projfunc_cost_rate_type
,projfunc_cost_exchange_rate
,projfunc_cost_rate_date_type
,projfunc_cost_rate_date
,projfunc_rev_rate_type
,projfunc_rev_exchange_rate
,projfunc_rev_rate_date_type
,projfunc_rev_rate_date
,project_currency_code
,project_cost_rate_type
,project_cost_exchange_rate
,project_cost_rate_date_type
,project_cost_rate_date
,project_raw_cost
,project_burdened_cost
,project_rev_rate_type
,project_rev_exchange_rate
,project_rev_rate_date_type
,project_rev_rate_date
,project_revenue
,txn_currency_code
,txn_raw_cost
,txn_burdened_cost
,txn_revenue
,budget_line_id
,init_quantity
,txn_init_raw_cost
,txn_init_burdened_cost
,txn_init_revenue
,bill_markup_percentage
,bill_rate
,cost_rate
,rw_cost_rate_override
,burden_cost_rate
,bill_rate_override
,burden_cost_rate_override
,cost_ind_compiled_set_id
,init_raw_cost
,init_burdened_cost
,init_revenue
,project_init_raw_cost
,project_init_burdened_cost
,project_init_revenue
,billable_flag
)
( SELECT /*+ INDEX(BL PA_BUDGET_LINES_U1) */
bl.budget_version_id
,bl.resource_assignment_id
,bl.start_date
,bl.end_date
,bl.period_name
,bl.quantity
,bl.raw_cost
,bl.burdened_cost
,bl.revenue
,bl.cost_rejection_code
,bl.revenue_rejection_code
,bl.burden_rejection_code
,bl.projfunc_currency_code
,bl.projfunc_cost_rate_type
,bl.projfunc_cost_exchange_rate
,bl.projfunc_cost_rate_date_type
,bl.projfunc_cost_rate_date
,bl.projfunc_rev_rate_type
,bl.projfunc_rev_exchange_rate
,bl.projfunc_rev_rate_date_type
,bl.projfunc_rev_rate_date
,bl.project_currency_code
,bl.project_cost_rate_type
,bl.project_cost_exchange_rate
,bl.project_cost_rate_date_type
,bl.project_cost_rate_date
,bl.project_raw_cost
,bl.project_burdened_cost
,bl.project_rev_rate_type
,bl.project_rev_exchange_rate
,bl.project_rev_rate_date_type
,bl.project_rev_rate_date
,bl.project_revenue
,bl.txn_currency_code
,bl.txn_raw_cost
,bl.txn_burdened_cost
,bl.txn_revenue
,bl.budget_line_id
,bl.init_quantity
,bl.txn_init_raw_cost
,bl.txn_init_burdened_cost
,bl.txn_init_revenue
,bl.txn_markup_percent
,bl.txn_standard_bill_rate
,bl.txn_standard_cost_rate
,bl.txn_cost_rate_override
,bl.burden_cost_rate
,bl.txn_bill_rate_override
,bl.burden_cost_rate_override
,bl.cost_ind_compiled_set_id
,bl.init_raw_cost
,bl.init_burdened_cost
,bl.init_revenue
,bl.project_init_raw_cost
,bl.project_init_burdened_cost
,bl.project_init_revenue
,tmp.billable_flag
FROM pa_budget_lines bl
,pa_resource_assignments ra
,pa_fp_spread_calc_tmp tmp
,pa_fp_rollup_tmp rlp --Bug 5203868
WHERE tmp.budget_version_id = p_budget_version_id
AND tmp.resource_assignment_id = ra.resource_assignment_id
AND bl.resource_assignment_id = tmp.resource_assignment_id
AND bl.txn_currency_code = tmp.txn_currency_code
AND NVL(tmp.skip_record_flag,'N') = 'Y'
AND ((p_calling_mode = 'PROCESS_CST_REV_MIX'
and nvl(tmp.processed_flag,'N') = 'Y' )
OR
p_calling_mode <> 'PROCESS_CST_REV_MIX'
)
AND ((p_source_context = 'BUDGET_LINE'
and bl.start_date BETWEEN tmp.start_date and tmp.end_date)
OR
p_source_context <> 'BUDGET_LINE'
)
--Bug 5203868. Replaced the NOT EXISTS with the following
AND rlp.resource_assignment_id(+)=tmp.resource_assignment_id
AND rlp.txn_currency_code(+)=tmp.txn_currency_code
AND rlp.rowid IS NULL
/* Commenting below code for bug 5203868
AND NOT EXISTS
(select null
from pa_fp_rollup_tmp rl1
Where rl1.resource_assignment_id = tmp.resource_assignment_id
and rl1.txn_currency_code = tmp.txn_currency_code
)
*/
);
print_msg('Process_skipped_records :Number of records inserted['||sql%rowcount||']');