[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
/* Declare variables for Bulk Processing of inserting budget lines */
-- for inserting new budget lines
g_bl_res_assignment_id_tab pa_plsql_datatypes.NumTabTyp;
-- for inserting rollup tmp lines without budget lines
g_rl_res_assignment_id_tab pa_plsql_datatypes.NumTabTyp;
-- for bulk update of rounding diff lines
g_edist_rndiff_quantity pa_plsql_datatypes.NumTabTyp;
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)
);
SELECT sum(nvl(bl.quantity,0))
INTO v_total_quantity
FROM pa_fp_rollup_tmp bl
WHERE bl.RESOURCE_ASSIGNMENT_ID = g_edist_RaId(i)
AND bl.TXN_CURRENCY_CODE = NVL(g_edist_Curcode_ovr(i),g_edist_Curcode(i))
AND bl.START_DATE BETWEEN g_edist_sdate(i) AND g_edist_edate(i)
AND bl.END_DATE BETWEEN g_edist_sdate(i) AND g_edist_edate(i)
AND bl.PERIOD_NAME IS NOT NULL;
SELECT sum(nvl(bl.quantity,0))
INTO v_bl_total_quantity
FROM pa_budget_lines bl
,pa_fp_res_assignments_tmp rtmp
WHERE bl.budget_version_id = p_budget_version_id
AND bl.RESOURCE_ASSIGNMENT_ID = g_edist_RaId(i)
AND bl.TXN_CURRENCY_CODE = g_edist_Curcode(i)
AND bl.budget_version_id = rtmp.budget_version_id
AND rtmp.resource_assignment_id = bl.resource_assignment_id
AND rtmp.txn_currency_code = bl.txn_currency_code
AND ((rtmp.SOURCE_CONTEXT = 'BUDGET_LINE'
AND rtmp.LINE_START_DATE = g_edist_line_start_date(i))
OR
rtmp.SOURCE_CONTEXT <> 'BUDGET_LINE'
)
AND bl.START_DATE BETWEEN decode(rtmp.SOURCE_CONTEXT,'BUDGET_LINE',rtmp.LINE_START_DATE
,decode(sign(bl.START_DATE - rtmp.planning_start_date),-1,bl.START_DATE,rtmp.planning_start_date))
AND decode(rtmp.SOURCE_CONTEXT,'BUDGET_LINE',rtmp.LINE_END_DATE,rtmp.planning_end_date)
AND bl.END_DATE BETWEEN decode(rtmp.SOURCE_CONTEXT,'BUDGET_LINE',rtmp.LINE_START_DATE,rtmp.planning_start_date )
AND decode(rtmp.SOURCE_CONTEXT,'BUDGET_LINE',rtmp.LINE_END_DATE
,decode(sign(bl.END_DATE - rtmp.planning_end_date),1,bl.END_DATE,rtmp.planning_end_date))
AND bl.END_DATE < g_edist_etc_sdate(i)
AND bl.PERIOD_NAME IS NOT NULL;
/* bulk update the rollup tmp with spread rounding diff amount */
IF g_edist_blId.COUNT > 0 THEN
l_stage := 'Finally one bulk update of rollup tmp lines ';
UPDATE PA_FP_ROLLUP_TMP tmp
SET tmp.QUANTITY = decode(NVL(g_edist_txn_quantity_addl(i),0),0,tmp.QUANTITY,(nvl(tmp.QUANTITY,0)+g_edist_rndiff_quantity(i)))
WHERE tmp.budget_version_id = p_budget_version_id
AND tmp.BUDGET_LINE_ID = g_edist_blId(i);
g_bl_res_assignment_id_tab.delete;
g_bl_start_date_tab.delete;
g_bl_end_date_tab.delete;
g_bl_period_name_tab.delete;
g_bl_txn_curr_code_tab.delete;
g_bl_txn_curr_code_ovr_tab.delete;
g_bl_budget_line_id_tab.delete;
g_bl_budget_version_id_tab.delete;
g_bl_proj_curr_code_tab.delete;
g_bl_projfunc_curr_code_tab.delete;
-- for inserting rollup tmp lines without budget lines
g_rl_res_assignment_id_tab.delete;
g_rl_start_date_tab.delete;
g_rl_end_date_tab.delete;
g_rl_period_name_tab.delete;
g_rl_txn_curr_code_tab.delete;
g_rl_txn_curr_code_ovr_tab.delete;
g_rl_budget_line_id_tab.delete;
g_rl_budget_version_id_tab.delete;
g_rl_proj_curr_code_tab.delete;
g_rl_projfunc_curr_code_tab.delete;
g_rl_quantity_tab.delete;
g_rl_txn_raw_cost_tab.delete;
g_rl_txn_cost_rate_tab.delete;
g_rl_txn_cost_rate_ovr_tab.delete;
g_rl_txn_burden_cost_tab.delete;
g_rl_txn_burden_rate_tab.delete;
g_rl_txn_burden_rate_ovr_tab.delete;
g_rl_txn_revenue_tab.delete;
g_rl_txn_bill_rate_tab.delete;
g_rl_txn_bill_rate_ovr_tab.delete;
g_rbl_res_assignment_id_tab.delete;
g_rbl_start_date_tab.delete;
g_rbl_end_date_tab.delete;
g_rbl_period_name_tab.delete;
g_rbl_txn_curr_code_tab.delete;
g_rbl_txn_curr_code_ovr_tab.delete;
g_rbl_budget_line_id_tab.delete;
g_rbl_budget_version_id_tab.delete;
g_rbl_proj_curr_code_tab.delete;
g_rbl_projfunc_curr_code_tab.delete;
g_rbl_quantity_tab.delete;
g_rbl_txn_raw_cost_tab.delete;
g_rbl_txn_cost_rate_tab.delete;
g_rbl_txn_cost_rate_ovr_tab.delete;
g_rbl_txn_burden_cost_tab.delete;
g_rbl_txn_burden_rate_tab.delete;
g_rbl_txn_burden_rate_ovr_tab.delete;
g_rbl_txn_revenue_tab.delete;
g_rbl_txn_bill_rate_tab.delete;
g_rbl_txn_bill_rate_ovr_tab.delete;
-- for bulk update of rollup tmp lines with rounding diff amounts
g_edist_rndiff_quantity.delete;
g_edist_blId.delete;
g_edist_RaId.delete;
g_edist_txn_quantity_addl.delete;
g_edist_txn_plan_quantity.delete;
g_edist_Curcode.delete;
g_edist_Curcode_ovr.delete;
g_edist_sdate.delete;
g_edist_edate.delete;
g_edist_etc_sdate.delete;
g_edist_line_start_date.delete;
g_edist_source_context.delete;
/* This API bulk inserts the budget lines from plsql tables*/
PROCEDURE blkInsertBudgetLines(x_return_status OUT NOCOPY Varchar2) IS
l_stage varchar2(1000);
l_stage := 'Bulk Insert of Budget Lines';
INSERT INTO PA_BUDGET_LINES
(
BUDGET_LINE_ID
,BUDGET_VERSION_ID
,RESOURCE_ASSIGNMENT_ID
,START_DATE
,END_DATE
,PERIOD_NAME
,TXN_CURRENCY_CODE
,PROJECT_CURRENCY_CODE
,PROJFUNC_CURRENCY_CODE
,CREATED_BY
,CREATION_DATE
,LAST_UPDATED_BY
,LAST_UPDATE_DATE
,LAST_UPDATE_LOGIN
,QUANTITY_SOURCE
,RAW_COST_SOURCE
,BURDENED_COST_SOURCE
,REVENUE_SOURCE
)
VALUES (
g_bl_budget_line_id_tab(i)
,g_bl_budget_version_id_tab(i)
,g_bl_res_assignment_id_tab(i)
,g_bl_start_date_tab(i)
,g_bl_end_date_tab(i)
,g_bl_period_name_tab(i)
,NVL(g_bl_txn_curr_code_ovr_tab(i),g_bl_txn_curr_code_tab(i))
,g_bl_proj_curr_code_tab(i)
,g_bl_projfunc_curr_code_tab(i)
,g_user_id
,SYSDATE
,g_user_id
,SYSDATE
,g_login_id
,G_BUDGET_LINE_SOURCE
,G_BUDGET_LINE_SOURCE
,G_BUDGET_LINE_SOURCE
,G_BUDGET_LINE_SOURCE
);
print_msg('Unexpected error in blkInsertBudgetLines['||sqlcode||sqlerrm||']');
p_procedure_name => 'blkInsertBudgetLines'||l_stage);
END blkInsertBudgetLines;
/* This API bulk inserts the lines into pa_fp_rollup_tmp from plsql tables */
PROCEDURE blkInsertFpLines(x_return_status OUT NOCOPY Varchar2) IS
l_stage varchar2(1000);
l_stage := 'Bulk Insert of Fp rollup Tmp lines';
INSERT INTO PA_FP_ROLLUP_TMP
(
BUDGET_LINE_ID
,BUDGET_VERSION_ID
,RESOURCE_ASSIGNMENT_ID
,START_DATE
,END_DATE
,PERIOD_NAME
,TXN_CURRENCY_CODE
,PROJECT_CURRENCY_CODE
,PROJFUNC_CURRENCY_CODE
,QUANTITY
,TXN_RAW_COST
,COST_RATE
,RW_COST_RATE_OVERRIDE
,TXN_BURDENED_COST
,BURDEN_COST_RATE
,BURDEN_COST_RATE_OVERRIDE
,TXN_REVENUE
,BILL_RATE
,BILL_RATE_OVERRIDE
,QUANTITY_SOURCE
,RAW_COST_SOURCE
,BURDENED_COST_SOURCE
,REVENUE_SOURCE
)
VALUES (
g_rl_budget_line_id_tab(i)
,g_rl_budget_version_id_tab(i)
,g_rl_res_assignment_id_tab(i)
,g_rl_start_date_tab(i)
,g_rl_end_date_tab(i)
,g_rl_period_name_tab(i)
,NVL(g_rl_txn_curr_code_ovr_tab(i),g_rl_txn_curr_code_tab(i))
,g_rl_proj_curr_code_tab(i)
,g_rl_projfunc_curr_code_tab(i)
,decode(g_rl_quantity_tab(i),0,NULL,g_rl_quantity_tab(i))
,decode(g_rl_txn_raw_cost_tab(i),0,NULL,g_rl_txn_raw_cost_tab(i))
,decode(g_rl_txn_cost_rate_tab(i),0,NULL,g_rl_txn_cost_rate_tab(i))
/* bug fix:4693839 : removed decode to have 0,NULL for override rates */
--,decode(g_rl_txn_cost_rate_ovr_tab(i),0,NULL,g_rl_txn_cost_rate_ovr_tab(i))
,g_rl_txn_cost_rate_ovr_tab(i)
,decode(g_rl_txn_burden_cost_tab(i),0,NULL,g_rl_txn_burden_cost_tab(i))
,decode(g_rl_txn_burden_rate_tab(i),0,NULL,g_rl_txn_burden_rate_tab(i))
--,decode(g_rl_txn_burden_rate_ovr_tab(i),0,NULL,g_rl_txn_burden_rate_ovr_tab(i))
,g_rl_txn_burden_rate_ovr_tab(i)
,decode(g_rl_txn_revenue_tab(i),0,NULL,g_rl_txn_revenue_tab(i))
,decode(g_rl_txn_bill_rate_tab(i),0,NULL,g_rl_txn_bill_rate_tab(i))
--,decode(g_rl_txn_bill_rate_ovr_tab(i),0,NULL,g_rl_txn_bill_rate_ovr_tab(i))
,g_rl_txn_bill_rate_ovr_tab(i)
,G_BUDGET_LINE_SOURCE
,G_BUDGET_LINE_SOURCE
,G_BUDGET_LINE_SOURCE
,G_BUDGET_LINE_SOURCE
);
print_msg('Unexpected error in blkInsertFpLines['||sqlcode||sqlerrm||']');
p_procedure_name => 'blkInsertFpLines'||l_stage);
END blkInsertFpLines;
/* This API bulk inserts lines into pa_fp_rollup_tmp from pa_budget_lines */
PROCEDURE blkInsertBlFpLines(x_return_status OUT NOCOPY Varchar2) IS
l_stage varchar2(1000);
l_stage := 'Bulk Insert of rollup Tmp with Budget Lines';
in Update Task Details page, the following piece of code gets executed.
This code caches several attributes from pa_budget_lines table and will use them in the
later part of the flow, thus causing the above bug. When ever currency code is overwritten
we need to use the new currency's conversion attributes, but where as this code will use
old currency's conversion attributes.
As a fix the following insert is commented out and a new insert is written with a change
in the select statement of values clause.
INSERT INTO PA_FP_ROLLUP_TMP
(
BUDGET_LINE_ID
,BUDGET_VERSION_ID
,RESOURCE_ASSIGNMENT_ID
,START_DATE
,END_DATE
,PERIOD_NAME
,TXN_CURRENCY_CODE
,PROJECT_CURRENCY_CODE
,PROJFUNC_CURRENCY_CODE
,QUANTITY
,TXN_RAW_COST
,COST_RATE
,RW_COST_RATE_OVERRIDE
,TXN_BURDENED_COST
,BURDEN_COST_RATE
,BURDEN_COST_RATE_OVERRIDE
,TXN_REVENUE
,BILL_RATE
,BILL_RATE_OVERRIDE
,PROJFUNC_RAW_COST
,PROJFUNC_BURDENED_COST
,PROJFUNC_REVENUE
,COST_REJECTION_CODE
,REVENUE_REJECTION_CODE
,BURDEN_REJECTION_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_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
,INIT_QUANTITY
,TXN_INIT_RAW_COST
,TXN_INIT_BURDENED_COST
,TXN_INIT_REVENUE
,BILL_MARKUP_PERCENTAGE
,COST_IND_COMPILED_SET_ID
,QUANTITY_SOURCE
,RAW_COST_SOURCE
,BURDENED_COST_SOURCE
,REVENUE_SOURCE
,INIT_RAW_COST
,INIT_BURDENED_COST
,INIT_REVENUE
,PROJECT_INIT_RAW_COST
,PROJECT_INIT_BURDENED_COST
,PROJECT_INIT_REVENUE
)
SELECT
g_rbl_budget_line_id_tab(i)
,g_rbl_budget_version_id_tab(i)
,g_rbl_res_assignment_id_tab(i)
,g_rbl_start_date_tab(i)
,g_rbl_end_date_tab(i)
,g_rbl_period_name_tab(i)
,NVL(g_rbl_txn_curr_code_ovr_tab(i),g_rbl_txn_curr_code_tab(i))
,g_rbl_proj_curr_code_tab(i)
,g_rbl_projfunc_curr_code_tab(i)
,decode(g_rbl_quantity_tab(i),0,NULL,g_rbl_quantity_tab(i))
,decode(g_rbl_txn_raw_cost_tab(i),0,NULL,g_rbl_txn_raw_cost_tab(i))
,decode(g_rbl_txn_cost_rate_tab(i),0,NULL,g_rbl_txn_cost_rate_tab(i))
,decode(g_rbl_txn_curr_code_ovr_tab(i),NULL,NVL(g_rbl_txn_cost_rate_ovr_tab(i),bl.txn_cost_rate_override)
,g_rbl_txn_cost_rate_ovr_tab(i))
,decode(g_rbl_txn_burden_cost_tab(i),0,NULL,g_rbl_txn_burden_cost_tab(i))
,decode(g_rbl_txn_burden_rate_tab(i),0,NULL,g_rbl_txn_burden_rate_tab(i))
,decode(g_rbl_txn_curr_code_ovr_tab(i),NULL,NVL(g_rbl_txn_burden_rate_ovr_tab(i),bl.burden_cost_rate_override)
,g_rbl_txn_burden_rate_ovr_tab(i))
,decode(g_rbl_txn_revenue_tab(i),0,NULL,g_rbl_txn_revenue_tab(i))
,decode(g_rbl_txn_bill_rate_tab(i),0,NULL,g_rbl_txn_bill_rate_tab(i))
,decode(g_rbl_txn_curr_code_ovr_tab(i),NULL,NVL(g_rbl_txn_bill_rate_ovr_tab(i),bl.txn_bill_rate_override)
,g_rbl_txn_bill_rate_ovr_tab(i))
,bl.RAW_COST
,bl.BURDENED_COST
,bl.REVENUE
,bl.COST_REJECTION_CODE
,bl.REVENUE_REJECTION_CODE
,bl.BURDEN_REJECTION_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_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.INIT_QUANTITY
,bl.TXN_INIT_RAW_COST
,bl.TXN_INIT_BURDENED_COST
,bl.TXN_INIT_REVENUE
,bl.TXN_MARKUP_PERCENT
,bl.COST_IND_COMPILED_SET_ID
,bl.QUANTITY_SOURCE
,bl.RAW_COST_SOURCE
,bl.BURDENED_COST_SOURCE
,bl.REVENUE_SOURCE
,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
FROM PA_BUDGET_LINES bl
WHERE bl.budget_line_id = g_rbl_budget_line_id_tab(i)
End of Bug Fix 4332086.
*/
INSERT INTO PA_FP_ROLLUP_TMP
(
BUDGET_LINE_ID
,BUDGET_VERSION_ID
,RESOURCE_ASSIGNMENT_ID
,START_DATE
,END_DATE
,PERIOD_NAME
,TXN_CURRENCY_CODE
,PROJECT_CURRENCY_CODE
,PROJFUNC_CURRENCY_CODE
,QUANTITY
,TXN_RAW_COST
,COST_RATE
,RW_COST_RATE_OVERRIDE
,TXN_BURDENED_COST
,BURDEN_COST_RATE
,BURDEN_COST_RATE_OVERRIDE
,TXN_REVENUE
,BILL_RATE
,BILL_RATE_OVERRIDE
,PROJFUNC_RAW_COST
,PROJFUNC_BURDENED_COST
,PROJFUNC_REVENUE
,COST_REJECTION_CODE
,REVENUE_REJECTION_CODE
,BURDEN_REJECTION_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_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
,INIT_QUANTITY
,TXN_INIT_RAW_COST
,TXN_INIT_BURDENED_COST
,TXN_INIT_REVENUE
,BILL_MARKUP_PERCENTAGE
,COST_IND_COMPILED_SET_ID
,QUANTITY_SOURCE
,RAW_COST_SOURCE
,BURDENED_COST_SOURCE
,REVENUE_SOURCE
,INIT_RAW_COST
,INIT_BURDENED_COST
,INIT_REVENUE
,PROJECT_INIT_RAW_COST
,PROJECT_INIT_BURDENED_COST
,PROJECT_INIT_REVENUE
)
SELECT
g_rbl_budget_line_id_tab(i)
,g_rbl_budget_version_id_tab(i)
,g_rbl_res_assignment_id_tab(i)
,g_rbl_start_date_tab(i)
,g_rbl_end_date_tab(i)
,g_rbl_period_name_tab(i)
,NVL(g_rbl_txn_curr_code_ovr_tab(i),g_rbl_txn_curr_code_tab(i))
,g_rbl_proj_curr_code_tab(i)
,g_rbl_projfunc_curr_code_tab(i)
,decode(g_rbl_quantity_tab(i),0,NULL,g_rbl_quantity_tab(i))
,decode(g_rbl_txn_raw_cost_tab(i),0,NULL,g_rbl_txn_raw_cost_tab(i))
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,decode(g_rbl_txn_cost_rate_tab(i),0,NULL,g_rbl_txn_cost_rate_tab(i)),NULL)
,decode(g_rbl_txn_curr_code_ovr_tab(i),NULL,NVL(g_rbl_txn_cost_rate_ovr_tab(i),bl.txn_cost_rate_override),g_rbl_txn_cost_rate_ovr_tab(i))
,decode(g_rbl_txn_burden_cost_tab(i),0,NULL,g_rbl_txn_burden_cost_tab(i))
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,decode(g_rbl_txn_burden_rate_tab(i),0,NULL,g_rbl_txn_burden_rate_tab(i)),NULL)
,decode(g_rbl_txn_curr_code_ovr_tab(i),NULL,NVL(g_rbl_txn_burden_rate_ovr_tab(i),bl.burden_cost_rate_override),g_rbl_txn_burden_rate_ovr_tab(i))
,decode(g_rbl_txn_revenue_tab(i),0,NULL,g_rbl_txn_revenue_tab(i))
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,decode(g_rbl_txn_bill_rate_tab(i),0,NULL,g_rbl_txn_bill_rate_tab(i)),NULL)
,decode(g_rbl_txn_curr_code_ovr_tab(i),NULL,NVL(g_rbl_txn_bill_rate_ovr_tab(i),bl.txn_bill_rate_override),g_rbl_txn_bill_rate_ovr_tab(i))
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.RAW_COST,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.BURDENED_COST,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.REVENUE,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.COST_REJECTION_CODE,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.REVENUE_REJECTION_CODE,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.BURDEN_REJECTION_CODE,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJFUNC_COST_RATE_TYPE,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJFUNC_COST_EXCHANGE_RATE,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJFUNC_COST_RATE_DATE_TYPE,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJFUNC_COST_RATE_DATE,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJFUNC_REV_RATE_TYPE,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJFUNC_REV_EXCHANGE_RATE,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJFUNC_REV_RATE_DATE_TYPE,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJFUNC_REV_RATE_DATE,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJECT_COST_RATE_TYPE,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJECT_COST_EXCHANGE_RATE,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJECT_COST_RATE_DATE_TYPE,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJECT_COST_RATE_DATE,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJECT_RAW_COST,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJECT_BURDENED_COST,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJECT_REV_RATE_TYPE,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJECT_REV_EXCHANGE_RATE,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJECT_REV_RATE_DATE_TYPE,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJECT_REV_RATE_DATE,NULL)
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.PROJECT_REVENUE,NULL)
,bl.INIT_QUANTITY
,bl.TXN_INIT_RAW_COST
,bl.TXN_INIT_BURDENED_COST
,bl.TXN_INIT_REVENUE
,bl.TXN_MARKUP_PERCENT
,DECODE(g_rbl_txn_curr_code_ovr_tab(i),NULL,bl.COST_IND_COMPILED_SET_ID,NULL)
,bl.QUANTITY_SOURCE
,bl.RAW_COST_SOURCE
,bl.BURDENED_COST_SOURCE
,bl.REVENUE_SOURCE
,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
FROM PA_BUDGET_LINES bl
WHERE bl.budget_line_id = g_rbl_budget_line_id_tab(i)
/*Perf Bug fix:4251959 AND bl.budget_version_id = g_rbl_budget_version_id_tab(i) */
;
print_msg('Unexpected error in blkInsertBlFpLines['||sqlcode||sqlerrm||']');
p_procedure_name => 'blkInsertBlFpLines'||l_stage);
END blkInsertBlFpLines;
/* This API populates the plsql tables with budget lines for bulk insert */
PROCEDURE insert_budget_line(
p_resource_assignment_id IN pa_budget_lines.RESOURCE_ASSIGNMENT_ID%TYPE
,p_start_date IN pa_budget_lines.START_DATE%TYPE
,p_end_date IN pa_budget_lines.END_DATE%TYPE
,p_period_name IN pa_budget_lines.PERIOD_NAME%TYPE
,p_txn_currency_code IN pa_budget_lines.TXN_CURRENCY_CODE%TYPE
,p_txn_curr_code_ovr IN pa_budget_lines.TXN_CURRENCY_CODE%TYPE
,x_budget_line_id OUT NOCOPY pa_budget_lines.BUDGET_LINE_ID%TYPE
,p_budget_version_id IN pa_budget_lines.BUDGET_VERSION_ID%TYPE
,p_proj_curr_cd IN pa_projects_all.project_currency_code%TYPE
,p_projfunc_curr_cd IN pa_projects_all.projfunc_currency_code%TYPE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2) IS
l_stage VARCHAR2(1000);
l_stage := '1100 :: Entered insert_budget_line()';
SELECT PA_BUDGET_LINES_S.NEXTVAL
INTO l_budget_line_id
FROM DUAL;
p_procedure_name => 'insert_budget_line'||l_stage);
END insert_budget_line;
/* This API populates the plsql tables with rollup tmp lines for bulk insert */
PROCEDURE insert_rollup_tmp(
p_ra_rec IN resource_assignment_rec_type
,p_budget_version_id IN NUMBER
,p_start_date IN pa_fp_rollup_tmp.START_DATE%TYPE
,p_end_date IN pa_fp_rollup_tmp.END_DATE%TYPE
,p_period_name IN pa_fp_rollup_tmp.PERIOD_NAME%TYPE
,p_budget_line_id IN pa_fp_rollup_tmp.BUDGET_LINE_ID%TYPE
,p_quantity IN pa_fp_rollup_tmp.QUANTITY%TYPE
,p_txn_raw_cost IN pa_fp_rollup_tmp.TXN_RAW_COST%TYPE
,p_txn_burdened_cost IN pa_fp_rollup_tmp.TXN_BURDENED_COST%TYPE
,p_txn_revenue IN pa_fp_rollup_tmp.TXN_REVENUE%TYPE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2) IS
l_stage VARCHAR2(1000);
l_stage := '1200::Entered insert_rollup_tmp()';
p_procedure_name => 'insert_rollup_tmp()'||l_stage);
END insert_rollup_tmp;
/* This API populates the plsql tables with rollup tmp lines for bulk insert */
PROCEDURE insert_rollup_tmp_with_bl(
p_ra_rec IN resource_assignment_rec_type
,p_budget_version_id IN NUMBER
,p_start_date IN pa_fp_rollup_tmp.START_DATE%TYPE
,p_end_date IN pa_fp_rollup_tmp.END_DATE%TYPE
,p_period_name IN pa_fp_rollup_tmp.PERIOD_NAME%TYPE
,p_budget_line_id IN pa_fp_rollup_tmp.BUDGET_LINE_ID%TYPE
,p_quantity IN pa_fp_rollup_tmp.QUANTITY%TYPE
,p_txn_raw_cost IN pa_fp_rollup_tmp.TXN_RAW_COST%TYPE
,p_txn_burdened_cost IN pa_fp_rollup_tmp.TXN_BURDENED_COST%TYPE
,p_txn_revenue IN pa_fp_rollup_tmp.TXN_REVENUE%TYPE
,x_return_status OUT NOCOPY VARCHAR2
,x_msg_count OUT NOCOPY NUMBER
,x_msg_data OUT NOCOPY VARCHAR2) IS
l_stage VARCHAR2(1000);
l_stage := '1400:: Entered insert_rollup_tmp_with_bl()';
p_procedure_name => 'insert_rollup_tmp_with_bl()'||l_stage);
END insert_rollup_tmp_with_bl;
-- update 032504 iand
-- OR
-- p_start_period BETWEEN 1 AND x_spread_amounts.COUNT() AND
-- p_end_period = 0 OR
-- p_start_period = 0 AND
-- p_end_period BETWEEN 1 AND x_spread_amounts.COUNT() OR
-- p_start_period = 0 AND p_end_period = 0) THEN
x_return_status := FND_API.G_RET_STS_ERROR;
-- update 032204 iand
IF p_global_start_date IS NOT NULL THEN
l_global_allocation := l_global_actual_periods * time_step;
/* Bug fix: 3961955 : The last period is getting updated with -ve amounts when spread curve weightage is zero
* Logic: The following code is updating the last budget line with the rounding diff amount
* Loop through the periodic budget lines in the reverse order. If the last period line is having zero weightage
* then put the diff amounts in the previous period. If all the periods are zero weightage then put the
* entire amounts/diff amounts in the Last period of the profile
*/
IF k = 1 THEN
IF (p_amount1 - amount_sum) <> 0 Then
IF (p_amount1 - amount_sum) > 0 Then
x_spread_amounts(l_end_period).amount1 := nvl(x_spread_amounts(l_end_period).amount1,0) +
(p_amount1 - amount_sum);
SELECT --gsb.period_set_name /*Start changes for bug 6156873*/
decode(decode(pbv.version_type,
'COST',ppfo.cost_time_phased_code,
'REVENUE',ppfo.revenue_time_phased_code,
ppfo.all_time_phased_code)
,'P', pia.period_set_name
,gsb.period_set_name) period_set_name /*End changes for bug 6156873*/
,gsb.accounted_period_type
,pia.pa_period_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;
1:SELECT STATEMENT :(cost=8,rows=2)
2:SORT ORDER BY :(cost=8,rows=2)
3:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=2)
4:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=2)
**/
CURSOR get_gl_periods_csr IS
SELECT START_DATE, END_DATE, PERIOD_NAME
FROM gl_periods gp
WHERE gp.period_set_name = l_period_set_name
AND gp.period_type = decode(l_time_phase_code,'G',l_accounted_period_type,'P',l_pa_period_type)
AND gp.adjustment_period_flag = 'N'
AND gp.start_date <= l_end_date -- plan end date
AND gp.end_date >= l_start_date -- planning start date
ORDER BY gp.start_date;
1:SELECT STATEMENT :(cost=8,rows=1)
2:FILTER :(cost=,rows=)
3:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=8,rows=1)
4:INDEX RANGE SCAN GL_PERIODS_N1 :(cost=2,rows=1)
5:SORT AGGREGATE :(cost=,rows=1)
6:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=4)
7:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=4)
3:SORT AGGREGATE :(cost=,rows=1)
4:TABLE ACCESS BY INDEX ROWID GL_PERIODS :(cost=4,rows=5)
5:INDEX RANGE SCAN GL_PERIODS_U2 :(cost=2,rows=5)
CURSOR get_gl_periods_csr IS
SELECT START_DATE, END_DATE, PERIOD_NAME
FROM gl_periods gp
WHERE gp.period_set_name = l_period_set_name
AND gp.period_type = decode(l_time_phase_code,'G',l_accounted_period_type,'P',l_pa_period_type)
AND gp.start_date >=
(SELECT MIN(start_date)
FROM gl_periods
WHERE end_date >= l_start_date
AND period_set_name = l_period_set_name
AND period_type =
decode(l_time_phase_code,'G',
l_accounted_period_type,
'P',l_pa_period_type)
AND adjustment_period_flag = 'N')
AND gp.end_date <=
(SELECT MAX(end_date)
FROM gl_periods
WHERE start_date <= l_end_date
AND period_set_name = l_period_set_name
AND period_type =
decode(l_time_phase_code,'G',
l_accounted_period_type,
'P',l_pa_period_type)
AND adjustment_period_flag = 'N')
AND gp.adjustment_period_flag = 'N'
ORDER BY gp.start_date;
SELECT RESOURCE_ASSIGNMENT_ID,
START_DATE,
END_DATE,
PERIOD_NAME,
QUANTITY,
TXN_RAW_COST,
TXN_BURDENED_COST,
TXN_REVENUE,
INIT_QUANTITY,
TXN_INIT_RAW_COST,
TXN_INIT_BURDENED_COST,
TXN_INIT_REVENUE,
TXN_CURRENCY_CODE,
BUDGET_LINE_ID,
BUDGET_VERSION_ID
FROM PA_BUDGET_LINES
WHERE RESOURCE_ASSIGNMENT_ID = v_resource_assignment_id
AND TXN_CURRENCY_CODE = v_txn_currency_code
AND START_DATE BETWEEN l_line_start_date AND l_line_end_date
AND END_DATE BETWEEN l_line_start_date AND l_line_end_date
AND PERIOD_NAME IS NOT NULL
ORDER BY START_DATE;
SELECT RESOURCE_ASSIGNMENT_ID,
START_DATE,
END_DATE,
PERIOD_NAME,
QUANTITY,
TXN_RAW_COST,
TXN_BURDENED_COST,
TXN_REVENUE,
INIT_QUANTITY,
TXN_INIT_RAW_COST,
TXN_INIT_BURDENED_COST,
TXN_INIT_REVENUE,
TXN_CURRENCY_CODE,
BUDGET_LINE_ID,
BUDGET_VERSION_ID
FROM PA_BUDGET_LINES
WHERE RESOURCE_ASSIGNMENT_ID = p_resAsgnId
AND TXN_CURRENCY_CODE = p_txn_cur_code
AND START_DATE BETWEEN p_line_start_date AND p_line_end_date
AND END_DATE BETWEEN p_line_start_date AND p_line_end_date
AND PERIOD_NAME IS NOT NULL
ORDER BY START_DATE;
SELECT BUDGET_LINE_ID
FROM PA_BUDGET_LINES
WHERE RESOURCE_ASSIGNMENT_ID = v_resource_assignment_id
AND TXN_CURRENCY_CODE = v_txn_currency_code
--AND START_DATE = l_plan_start_date bug 6339811
--AND END_DATE = l_plan_end_date bug 6339811
AND PERIOD_NAME IS NULL;
SELECT RESOURCE_ASSIGNMENT_ID
,BUDGET_VERSION_ID
,PROJECT_ID
,TASK_ID
,RESOURCE_LIST_MEMBER_ID
,PLANNING_START_DATE
,PLANNING_END_DATE
,SPREAD_CURVE_ID
,SP_FIXED_DATE
,TXN_CURRENCY_CODE
,TXN_CURRENCY_CODE_OVERRIDE
,PROJECT_CURRENCY_CODE
,PROJFUNC_CURRENCY_CODE
,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
,LINE_START_DATE
,LINE_END_DATE
,SOURCE_CONTEXT
,RAW_COST_RATE
,RW_COST_RATE_OVERRIDE
,BURDEN_COST_RATE
,BURDEN_COST_RATE_OVERRIDE
,BILL_RATE
,BILL_RATE_OVERRIDE
,RATE_BASED_FLAG
,SPREAD_AMOUNTS_FLAG
,INIT_QUANTITY
,TXN_INIT_RAW_COST
,TXN_INIT_BURDENED_COST
,TXN_INIT_REVENUE
/* Bug fix:5726773 : Added the following columns to store the negative quantity/amt change flags*/
,NVL(NEG_QUANTITY_CHANGE_FLAG,'N') neg_Qty_Change_flag
,NVL(NEG_RAWCOST_CHANGE_FLAG,'N') neg_RawCst_Change_flag
,NVL(NEG_BURDEN_CHANGE_FALG,'N') neg_BurdCst_Change_flag
,NVL(NEG_REVENUE_CHANGE_FLAG,'N') neg_rev_Change_flag
FROM PA_FP_RES_ASSIGNMENTS_TMP tmp
WHERE tmp.BUDGET_VERSION_ID = p_budget_version_id
AND (NVL(tmp.TXN_PLAN_QUANTITY_ADDL,0) <> 0
OR NVL(tmp.TXN_RAW_COST_ADDL,0) <> 0
OR NVL(tmp.TXN_BURDENED_COST_ADDL,0) <> 0
OR NVL(tmp.TXN_REVENUE_ADDL,0) <> 0
);
SELECT POINT1,
POINT2,
POINT3,
POINT4,
POINT5,
POINT6,
POINT7,
POINT8,
POINT9,
POINT10,
SPREAD_CURVE_CODE
FROM PA_SPREAD_CURVES_B
WHERE SPREAD_CURVE_ID = l_spread_curve_id;
SELECT ppa.name project_name
,pt.name task_name
,prl.alias resource_name
FROM pa_projects_all ppa
,pa_proj_elements pt
,pa_resource_list_members prl
,pa_resource_assignments pra
WHERE pra.resource_assignment_id = p_resource_assignment_id
AND ppa.project_id = pra.project_id
AND pt.proj_element_id(+) = pra.task_id
AND prl.resource_list_member_id = pra.resource_list_member_id;
SELECT sum(bl.quantity)
,sum(bl.txn_raw_cost)
,sum(bl.txn_burdened_cost)
,sum(bl.txn_revenue)
,min(bl.budget_line_id)
,decode(min(bl.budget_line_id),NULL,0,1) NumOfBudgetLines
,sum(NVL(bl.quantity,0)-NVL(bl.init_quantity,0)) Etc_Quantity
FROM pa_budget_lines bl
WHERE bl.resource_assignment_id = p_resAsgnId
AND bl.txn_currency_code = p_txn_cur_code
AND bl.START_DATE BETWEEN p_start_date AND p_end_date
AND bl.END_DATE BETWEEN p_start_date AND p_end_date ;
-- deleted before calling spread_amounts()
-- 2. if budget line(s) is(are) there, it's not allowed to
-- change the time phase code - from N/R to G/P or from G/P
-- to N/R or from G to P or from P to G etc.
-- 3. line_start/end_date must at begin/end of period
l_stage := 860;
-- updated 030204 Sgoteti
IF l_time_phase_code IN ('P','G')
AND resource_assignment_rec.SOURCE_CONTEXT = 'BUDGET_LINE'
AND ((resource_assignment_rec.LINE_END_DATE <
v_spread_amounts(1).start_date )
OR
( v_spread_amounts(v_spread_amounts.COUNT()).end_date <
resource_assignment_rec.LINE_START_DATE)) THEN
l_stage := 863;
-- updated 030204 Sgoteti
IF l_time_phase_code IN ('P','G') THEN
l_line_start_date := v_spread_amounts(1).start_date;
-- updated 030204 Sgoteti
END IF;
--print_msg('Inserting records into budget line for l_budget_line_time_phase_count = 0');
-- Insert into PA_BUDGET_LINES,
insert_budget_line(
v_resource_assignment_id,
resource_assignment_rec.PLANNING_START_DATE,
resource_assignment_rec.PLANNING_END_DATE,
NULL,
resource_assignment_rec.TXN_CURRENCY_CODE,
resource_assignment_rec.TXN_CURRENCY_CODE_OVERRIDE,
v_budget_line_id,
p_budget_version_id,
l_proj_curr_cd,
l_projfunc_curr_cd,
v_return_status,
v_msg_count,
v_msg_data);
--print_msg('Inserting records into rollup tmp for l_budget_line_time_phase_count = 0');
insert_rollup_tmp(
resource_assignment_rec,
p_budget_version_id,
resource_assignment_rec.PLANNING_START_DATE,
resource_assignment_rec.PLANNING_END_DATE,
NULL,
v_budget_line_id,
resource_assignment_rec.TXN_PLAN_QUANTITY,
resource_assignment_rec.TXN_RAW_COST,
resource_assignment_rec.TXN_BURDENED_COST,
resource_assignment_rec.TXN_REVENUE,
v_return_status,
v_msg_count,
v_msg_data);
--print_msg('Inserting records into rollup tmp for l_budget_line_time_phase_count = 1');
-- Insert Rollup Temporary Table
insert_rollup_tmp_with_bl(
resource_assignment_rec,
p_budget_version_id,
resource_assignment_rec.PLANNING_START_DATE,
resource_assignment_rec.PLANNING_END_DATE,
NULL,
budget_line_time_phase_rec.budget_line_id,
resource_assignment_rec.TXN_PLAN_QUANTITY,
resource_assignment_rec.TXN_RAW_COST,
resource_assignment_rec.TXN_BURDENED_COST,
resource_assignment_rec.TXN_REVENUE,
v_return_status,
v_msg_count,
v_msg_data);
insert_budget_line(
v_resource_assignment_id,
v_spread_amounts(v_spread_amounts.COUNT).start_date,
v_spread_amounts(v_spread_amounts.COUNT).end_date,
v_spread_amounts(v_spread_amounts.COUNT).period_name,
v_txn_currency_code,
resource_assignment_rec.TXN_CURRENCY_CODE_OVERRIDE,
v_budget_line_id,
p_budget_version_id,
l_proj_curr_cd,
l_projfunc_curr_cd,
v_return_status,
v_msg_count,
v_msg_data);
-- print_msg(l_stage||' after insert budget line');
-- Insert into Rollup Temporary Table
insert_rollup_tmp(
resource_assignment_rec,
p_budget_version_id,
v_spread_amounts(v_spread_amounts.COUNT).start_date,
v_spread_amounts(v_spread_amounts.COUNT).end_date,
v_spread_amounts(v_spread_amounts.COUNT).period_name,
v_budget_line_id,
resource_assignment_rec.TXN_PLAN_QUANTITY_ADDL,
resource_assignment_rec.TXN_RAW_COST_ADDL,
resource_assignment_rec.TXN_BURDENED_COST_ADDL,
resource_assignment_rec.TXN_REVENUE_ADDL,
v_return_status,
v_msg_count,
v_msg_data);
insert_rollup_tmp_with_bl(
resource_assignment_rec,
p_budget_version_id,
v_spread_amounts(v_spread_amounts.COUNT).start_date,
v_spread_amounts(v_spread_amounts.COUNT).end_date,
v_spread_amounts(v_spread_amounts.COUNT).period_name,
v_budget_line_id,
nvl(l_quantity,0) + resource_assignment_rec.TXN_PLAN_QUANTITY_ADDL,
nvl(l_txn_raw_cost,0) + resource_assignment_rec.TXN_RAW_COST_ADDL,
nvl(l_txn_burdened_cost,0) + resource_assignment_rec.TXN_BURDENED_COST_ADDL,
nvl(l_txn_revenue,0) + resource_assignment_rec.TXN_REVENUE_ADDL,
v_return_status,
v_msg_count,
v_msg_data);
print_msg(l_stage||' before update loop');
-- Insert into PA_BUDGET_LINES,
insert_budget_line(
v_resource_assignment_id,
v_spread_amounts(i).start_date,
v_spread_amounts(i).end_date,
v_spread_amounts(i).period_name,
v_txn_currency_code,
resource_assignment_rec.TXN_CURRENCY_CODE_OVERRIDE,
v_budget_line_id,
p_budget_version_id,
l_proj_curr_cd,
l_projfunc_curr_cd,
v_return_status,
v_msg_count,
v_msg_data);
-- print_msg(l_stage||' after insert budget line');
-- Insert into Rollup Temporary Table
insert_rollup_tmp(
resource_assignment_rec,
p_budget_version_id,
v_spread_amounts(i).start_date,
v_spread_amounts(i).end_date,
v_spread_amounts(i).period_name,
v_budget_line_id,
v_spread_amounts(i).amount1,
v_spread_amounts(i).amount2,
v_spread_amounts(i).amount3,
v_spread_amounts(i).amount4,
v_return_status,
v_msg_count,
v_msg_data);
-- print_msg(l_stage||' after insert rollup tmp');
insert_rollup_tmp_with_bl(
resource_assignment_rec,
p_budget_version_id,
v_spread_amounts(i).start_date,
v_spread_amounts(i).end_date,
v_spread_amounts(i).period_name,
v_budget_line_id,
l_quantity,
l_txn_raw_cost,
l_txn_burdened_cost,
l_txn_revenue,
v_return_status,
v_msg_count,
v_msg_data);
-- print_msg(l_stage||' after update rollup tmp');
print_msg(l_stage||' after update db');
-- update amounts based on existing distribution
l_last_budget_line_id := budget_line_rec.budget_line_id;
print_msg(' Before insert into insert_rollup_tmp_with_bl');
-- Insert into rollup tmp table
insert_rollup_tmp_with_bl(
resource_assignment_rec,
p_budget_version_id,
budget_line_rec.start_date,
budget_line_rec.end_date,
budget_line_rec.period_name,
budget_line_rec.budget_line_id,
tmp_quantity,
tmp_txn_raw_cost,
tmp_txn_burdened_cost,
tmp_txn_revenue,
v_return_status,
v_msg_count,
v_msg_data);
-- Insert Rollup Temporary Table
IF l_g_start_date IS NOT NULL AND
(budget_line_rec.init_quantity IS NOT NULL OR
budget_line_rec.txn_init_raw_cost IS NOT NULL OR
budget_line_rec.txn_init_burdened_cost IS NOT NULL OR
budget_line_rec.txn_init_revenue IS NOT NULL) THEN
l_stage := 1043;
insert_rollup_tmp_with_bl(
resource_assignment_rec,
p_budget_version_id,
budget_line_rec.start_date,
budget_line_rec.end_date,
budget_line_rec.period_name,
budget_line_rec.budget_line_id,
l_sp_fixed_qty,
l_sp_fixed_cost,
l_sp_fixed_burden,
l_sp_fixed_revenue,
v_return_status,
v_msg_count,
v_msg_data);
insert_rollup_tmp_with_bl(
resource_assignment_rec,
p_budget_version_id,
budget_line_rec.start_date,
budget_line_rec.end_date,
budget_line_rec.period_name,
budget_line_rec.budget_line_id,
(nvl(budget_line_rec.quantity,0) + nvl(l_txn_quantity_addl,0)),
(nvl(budget_line_rec.txn_raw_cost,0) + nvl(l_txn_raw_cost_addl,0)),
(nvl(budget_line_rec.txn_burdened_cost,0) + nvl(l_txn_burdened_cost_addl,0)),
(nvl(budget_line_rec.txn_revenue,0) + nvl(l_txn_revenue_addl,0)),
v_return_status,
v_msg_count,
v_msg_data);
insert_rollup_tmp_with_bl(
resource_assignment_rec,
p_budget_version_id,
budget_line_rec.start_date,
budget_line_rec.end_date,
budget_line_rec.period_name,
budget_line_rec.budget_line_id,
budget_line_rec.init_quantity,
budget_line_rec.txn_init_raw_cost,
budget_line_rec.txn_init_burdened_cost,
budget_line_rec.txn_init_revenue,
v_return_status,
v_msg_count,
v_msg_data);
-- Insert into PA_BUDGET_LINES,
insert_budget_line(
v_resource_assignment_id,
v_spread_amounts(i).start_date,
v_spread_amounts(i).end_date,
v_spread_amounts(i).period_name,
resource_assignment_rec.TXN_CURRENCY_CODE,
resource_assignment_rec.TXN_CURRENCY_CODE_OVERRIDE,
v_budget_line_id,
p_budget_version_id,
l_proj_curr_cd,
l_projfunc_curr_cd,
v_return_status,
v_msg_count,
v_msg_data);
-- Insert into Rollup Temporary Table
-- INSERT INTO PA_FP_ROLLUP_TMP
insert_rollup_tmp(
resource_assignment_rec,
p_budget_version_id,
v_spread_amounts(i).start_date,
v_spread_amounts(i).end_date,
v_spread_amounts(i).period_name,
v_budget_line_id,
l_txn_quantity_addl,
l_txn_raw_cost_addl,
l_txn_burdened_cost_addl,
l_txn_revenue_addl,
v_return_status,
v_msg_count,
v_msg_data);
print_msg(l_stage||' after update db ');
/* Now Bulk insert all the budget lines */
print_msg('Bulk update/Insert of budget and rollup tmp lines');
blkInsertBudgetLines(x_return_status => L_FINAL_RETURN_STATUS);
blkInsertFpLines(x_return_status => L_FINAL_RETURN_STATUS);
blkInsertBlFpLines(x_return_status => L_FINAL_RETURN_STATUS);
/* update the last rollup tmp line with rounding difference amount */
IF g_edist_blId.COUNT > 0 THEN
print_msg('Calling Process_Rounding_Diff API');
FOR i IN ( SELECT tmp.resource_assignment_id resAgnId
,tmp.txn_currency_code Currency
,sum(tmp.quantity) tmpqty
,sum(tmp.txn_raw_cost) tmprawcost
,sum(tmp.txn_burdened_cost) tmpburdencost
,sum(tmp.txn_revenue) tmprevenue
,sum(tmp.init_quantity) initQty
,sum(tmp.txn_init_raw_cost) initraw
,sum(tmp.txn_init_burdened_cost) initbud
,sum(tmp.txn_init_revenue) initrev
,count(*) numrows
FROM pa_fp_rollup_tmp tmp
WHERE tmp.budget_version_id = p_budget_version_id
AND NVL(tmp.system_reference5,'N') = 'N'
GROUP BY tmp.resource_assignment_id,tmp.txn_currency_code ) LOOP
print_msg('Number of Records in rolluptmp after Spread['||i.numrows||']');