[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
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 NVL(c.cost_rate_flag,'N') Cost_rate_flag
,Unit_Of_measure
FROM pa_expenditure_types c
WHERE c.expenditure_type = p_exp_type;
SELECT primary_uom_code
FROM mtl_system_items_b items
WHERE items.inventory_item_id = p_item_id
AND items.organization_id = p_organization_id
AND rownum = 1;
SELECT DISTINCT NVL(bl.cost_rejection_flag,'N')
,NVL(bl.burden_rejection_flag,'N')
,NVL(bl.revenue_rejection_flag,'N')
,NVL(bl.pc_conv_rejection_flag,'N')
,NVL(bl.pfc_conv_rejection_flag,'N')
,NVL(bl.other_rejection_flag,'N')
FROM pa_fp_budget_line_rejections_v bl
WHERE bl.budget_version_id = p_budget_version_id
AND bl.resource_assignment_id = p_resource_assignment_id
AND bl.txn_currency_code = p_txn_currency_code
AND (bl.cost_rejection_code is NOT NULL
OR bl.burden_rejection_code is NOT NULL
OR bl.revenue_rejection_code is NOT NULL
OR bl.pc_cur_conv_rejection_code is NOT NULL
OR bl.pfc_cur_conv_rejection_code is NOT NULL
OR bl.other_rejection_code is NOT NULL -- Bug 5203622
);
SELECT DISTINCT NVL(bl.period_cost_rejection_flag,'N')
,NVL(bl.period_burden_rejection_flag,'N')
,NVL(bl.period_revenue_rejection_flag,'N')
,NVL(bl.period_pc_conv_rejection_flag,'N')
,NVL(bl.period_pfc_conv_rejection_flag,'N')
,NVL(bl.period_other_rejection_flag,'N')
FROM pa_fp_budget_line_rejections_v bl
WHERE bl.budget_version_id = p_budget_version_id
AND bl.resource_assignment_id = p_resource_assignment_id
AND bl.txn_currency_code = p_txn_currency_code
AND bl.start_date between trunc(p_start_date) AND trunc(p_end_date)
AND bl.end_date between trunc(p_start_date) AND trunc(p_end_date)
AND (bl.cost_rejection_code is NOT NULL
OR bl.burden_rejection_code is NOT NULL
OR bl.revenue_rejection_code is NOT NULL
OR bl.pc_cur_conv_rejection_code is NOT NULL
OR bl.pfc_cur_conv_rejection_code is NOT NULL
OR bl.other_rejection_code is NOT NULL -- Bug 5203622
);
SELECT bl.project_id
,bl.budget_version_id
,bl.resource_assignment_id
,bl.period_name
,bl.start_date
,bl.end_date
,bl.cost_rejection_code
,bl.burden_rejection_code
,bl.revenue_rejection_code
,bl.pc_cur_conv_rejection_code
,bl.pfc_cur_conv_rejection_code
,bl.other_rejection_code
,decode(bl.cost_rejection_code,NULL,NULL,
bl.period_name||':'||(substr(bl.cost_rejection_msg_data,instr(bl.cost_rejection_msg_data,';',-1)+1)))
SELECT fp_resource_list_id
,fp_resource_list_type
FROM pa_fp_options_Reslists_v
WHERE project_id = p_project_id
AND fin_plan_type_id = p_fin_plan_type_id ;
select fp.project_id
,1 default_level --just for ordering purpose
,NVL(fp.cost_resource_list_id,fp.all_resource_list_id) Resource_list_id
from pa_proj_fp_options fp
,pa_fin_plan_types_b typ
where fp.project_id = p_project_id
and fp.fin_plan_type_id = typ.fin_plan_type_id
and typ.plan_class_code = 'BUDGET'
and fp.fin_plan_option_level_code = 'PLAN_TYPE'
and NVL(fp.cost_resource_list_id,fp.all_resource_list_id) is NOT NULL
and fp.approved_cost_plan_type_flag = 'Y'
and rownum = 1
UNION
select bv.project_id
,2 default_level
,bv.resource_list_id
from pa_budget_versions bv
,pa_budget_types bt
where bv.fin_plan_type_id is Null
and bv.budget_type_code = bt.budget_type_code
and bt.budget_amount_code = 'C'
and bv.current_flag = 'Y'
and bv.project_id = p_project_id
and rownum =1
UNION
Select pp.project_id
,3 Default_level
,rla.resource_list_id
from pa_resource_list_assignments rla
,pa_resource_list_uses rlu
,pa_projects_all pp
where pp.project_id = p_project_id
and rla.project_id = pp.project_id
and rlu.resource_list_assignment_id = rla.resource_list_assignment_id
and rlu.default_flag = 'Y'
and rownum = 1
ORDER BY 1,2;
select name
into l_resource_list_name
from pa_resource_lists_tl
where resource_list_id = l_resource_list_id
and language = userenv('LANG');
select name
into l_resource_list_name
from pa_resource_lists_v
where resource_list_id = l_resource_list_id;
SELECT bl.project_id
,bl.task_id
,bl.budget_version_id
,bl.budget_line_id
,bl.resource_assignment_id
,bl.resource_list_member_name
,bl.txn_currency_code
,bl.period_name
,bl.start_date
,bl.end_date
,bl.cost_rejection_code
,bl.burden_rejection_code
,bl.revenue_rejection_code
,bl.pc_cur_conv_rejection_code
,bl.pfc_cur_conv_rejection_code
,bl.other_rejection_code
,decode(bl.cost_rejection_code,NULL,NULL,
msg1.message_text||':'||bl.project_id||'; '||msg2.message_text||':'||bl.task_id||'; '
x_budget_line_id_tab.delete;
x_cost_rejection_data_tab.delete;
x_burden_rejection_data_tab.delete;
x_revenue_rejection_data_tab.delete;
x_pc_conv_rejection_data_tab.delete;
x_pfc_conv_rejection_data_tab.delete;
x_other_rejection_data_tab.delete;
* This api validates the currency conversion parameters and updates the pa_budget_lines table
* if there is any changes in the currency conversion attributes, it calls calculate api ()
* THIS API SHOULD NOT BE CALLED FROM ANY OTHER SOURCE, IF SO ALL THE PARAMETER VALUES MUST BE PASSED
* This api will not default any parameter values. This is similar to a table handler
*/
PROCEDURE validateAndUpdateBdgtLine(
p_budget_line_id IN Number
,p_BDGT_VERSION_ID IN Number
,p_RES_ASSIGNMENT_ID IN Number
,p_TXN_CURRENCY_CODE IN Varchar2
,p_START_DATE IN Date
,p_END_DATE IN Date
,P_CALLING_CONTEXT IN Varchar2
,P_ORG_ID IN Number
,p_PLAN_VERSION_TYPE IN Varchar2
,p_PROJFUNC_CURRENCY_CODE IN Varchar2
,p_PROJFUNC_COST_RATE_TYPE IN Varchar2
,p_PROJFUNC_COST_EXCHANGE_RATE IN Number
,p_PROJFUNC_COST_RATE_DATE_TYPE IN Varchar2
,p_PROJFUNC_COST_RATE_DATE IN Date
,p_PROJFUNC_REV_RATE_TYPE IN Varchar2
,p_PROJFUNC_REV_EXCHANGE_RATE IN Number
,p_PROJFUNC_REV_RATE_DATE_TYPE IN Varchar2
,p_PROJFUNC_REV_RATE_DATE IN Date
,p_PROJECT_CURRENCY_CODE IN Varchar2
,p_PROJECT_COST_RATE_TYPE IN Varchar2
,p_PROJECT_COST_EXCHANGE_RATE IN Number
,p_PROJECT_COST_RATE_DATE_TYPE IN Varchar2
,p_PROJECT_COST_RATE_DATE IN Date
,p_PROJECT_REV_RATE_TYPE IN Varchar2
,p_PROJECT_REV_EXCHANGE_RATE IN Number
,p_PROJECT_REV_RATE_DATE_TYPE IN Varchar2
,p_PROJECT_REV_RATE_DATE IN Date
,p_CHANGE_REASON_CODE IN Varchar2
,p_DESCRIPTION IN Varchar2
,p_ATTRIBUTE_CATEGORY IN Varchar2
,p_ATTRIBUTE1 IN Varchar2
,p_ATTRIBUTE2 IN Varchar2
,p_ATTRIBUTE3 IN Varchar2
,p_ATTRIBUTE4 IN Varchar2
,p_ATTRIBUTE5 IN Varchar2
,p_ATTRIBUTE6 IN Varchar2
,p_ATTRIBUTE7 IN Varchar2
,p_ATTRIBUTE8 IN Varchar2
,p_ATTRIBUTE9 IN Varchar2
,p_ATTRIBUTE10 IN Varchar2
,p_ATTRIBUTE11 IN Varchar2
,p_ATTRIBUTE12 IN Varchar2
,p_ATTRIBUTE13 IN Varchar2
,p_ATTRIBUTE14 IN Varchar2
,p_ATTRIBUTE15 IN Varchar2
,p_CI_ID IN Number
,x_return_status OUT NOCOPY Varchar2
,x_msg_data OUT NOCOPY Varchar2
,x_msg_count OUT NOCOPY Number
) IS
cursor check_bdgtLine_changed IS
SELECT project_currency_code
,project_cost_rate_type
,project_cost_rate_date
,project_cost_rate_date_type
,project_cost_exchange_rate
,project_rev_rate_type
,project_rev_rate_date
,project_rev_rate_date_type
,project_rev_exchange_rate
,projfunc_currency_code
,projfunc_cost_rate_type
,projfunc_cost_rate_date
,projfunc_cost_rate_date_type
,projfunc_cost_exchange_rate
,projfunc_rev_rate_type
,projfunc_rev_rate_date
,projfunc_rev_rate_date_type
,projfunc_rev_exchange_rate
FROM pa_budget_lines
Where budget_line_id = p_budget_line_id;
Select bv.project_id
,bv.etc_start_date
,bv.version_type
From pa_budget_versions bv
Where bv.budget_version_id = p_bdgt_version_id;
l_proc_name varchar2(100) := 'validateAndUpdateBdgtLine';
Select substr(msg.message_text,instr(msg.message_text,';',-1)+1) message_text
PA_DEBUG.init_err_stack ('PA_FIN_PLAN_UTILS2.validateAndUpdateBdgtLine');
l_stage := 'Inside validateAndUpdateBdgtLine API BdgtLineId['||p_budget_line_id||']ResAssingID['||p_res_assignment_id||
']txnCurrCode['||p_txn_currency_code||']StartDate['||p_start_date||']EndDate['||p_end_date||']BdgtVerId['||p_BDGT_VERSION_ID||
']PlanVerType['||p_plan_version_type||']Callingcontext['||p_calling_context||']';
l_stage := 'Updateing the budget lines with currency attributes';
UPDATE pa_budget_lines bl
SET bl.description = nvl(p_description,bl.description)
,bl.change_reason_code = nvl(p_change_reason_code,bl.change_reason_code)
,bl.attribute_category = nvl(p_attribute_category,bl.attribute_category)
,bl.attribute1 = nvl(p_attribute1,bl.attribute1)
,bl.attribute2 = nvl(p_attribute2,bl.attribute2)
,bl.attribute3 = nvl(p_attribute3,bl.attribute3)
,bl.attribute4 = nvl(p_attribute4,bl.attribute4)
,bl.attribute5 = nvl(p_attribute5,bl.attribute5)
,bl.attribute6 = nvl(p_attribute6,bl.attribute6)
,bl.attribute7 = nvl(p_attribute7,bl.attribute7)
,bl.attribute8 = nvl(p_attribute8,bl.attribute8)
,bl.attribute9 = nvl(p_attribute9,bl.attribute9)
,bl.attribute10 = nvl(p_attribute10,bl.attribute10)
,bl.attribute11 = nvl(p_attribute11,bl.attribute11)
,bl.attribute12 = nvl(p_attribute12,bl.attribute12)
,bl.attribute13 = nvl(p_attribute13,bl.attribute13)
,bl.attribute14 = nvl(p_attribute14,bl.attribute14)
,bl.attribute15 = nvl(p_attribute15,bl.attribute15)
---- cost attributes
,bl.project_cost_rate_type = decode(p_txn_currency_code,p_project_currency_code,bl.project_cost_rate_type
,decode(l_plan_version_type,'COST'
,l_project_cost_rate_type
,'ALL',l_project_cost_rate_type
,bl.project_cost_rate_type))
,bl.project_cost_rate_date_type = decode(p_txn_currency_code,p_project_currency_code,
bl.project_cost_rate_date_type,decode(l_plan_version_type,'COST'
,l_project_cost_rate_date_type
,'ALL',l_project_cost_rate_date_type
,bl.project_cost_rate_date_type))
,bl.project_cost_rate_date = decode(p_txn_currency_code,p_project_currency_code,
bl.project_cost_rate_date,decode(l_plan_version_type,'COST'
,l_project_cost_rate_date
,'ALL',l_project_cost_rate_date
,bl.project_cost_rate_date ))
,bl.project_cost_exchange_rate = decode(p_txn_currency_code,p_project_currency_code,
bl.project_cost_exchange_rate,decode(l_plan_version_type,'COST'
,l_project_cost_exchange_rate
,'ALL',l_project_cost_exchange_rate
,bl.project_cost_exchange_rate))
,bl.projfunc_cost_rate_type = decode(p_txn_currency_code,p_projfunc_currency_code,
bl.projfunc_cost_rate_type,decode(l_plan_version_type,'COST'
,l_projfunc_cost_rate_type
,'ALL',l_projfunc_cost_rate_type
,bl.projfunc_cost_rate_type))
,bl.projfunc_cost_rate_date_type = decode(p_txn_currency_code,p_projfunc_currency_code,
bl.projfunc_cost_rate_date_type,decode(l_plan_version_type,'COST'
,l_projfunc_cost_rate_date_type
,'ALL',l_projfunc_cost_rate_date_type
,bl.projfunc_cost_rate_date_type))
,bl.projfunc_cost_rate_date = decode(p_txn_currency_code,p_projfunc_currency_code,
bl.projfunc_cost_rate_date,decode(l_plan_version_type,'COST'
,l_projfunc_cost_rate_date
,'ALL',l_projfunc_cost_rate_date
,bl.projfunc_cost_rate_date ))
,bl.projfunc_cost_exchange_rate = decode(p_txn_currency_code,p_projfunc_currency_code,
bl.projfunc_cost_exchange_rate,decode(l_plan_version_type,'COST'
,l_projfunc_cost_exchange_rate
,'ALL',l_projfunc_cost_exchange_rate
,bl.projfunc_cost_exchange_rate))
--revenue attributes
,bl.project_rev_rate_type = decode(p_txn_currency_code,p_project_currency_code,
bl.project_rev_rate_type,decode(l_plan_version_type,'REVENUE'
,l_project_rev_rate_type
,'ALL',l_project_rev_rate_type
,bl.project_rev_rate_type))
,bl.project_rev_rate_date_type = decode(p_txn_currency_code,p_project_currency_code,
bl.project_rev_rate_date_type,decode(l_plan_version_type,'REVENUE'
,l_project_rev_rate_date_type
,'ALL',l_project_rev_rate_date_type
,bl.project_rev_rate_date_type))
,bl.project_rev_rate_date = decode(p_txn_currency_code,p_project_currency_code,
bl.project_rev_rate_date,decode(l_plan_version_type,'REVENUE'
,l_project_rev_rate_date
,'ALL',l_project_rev_rate_date
,bl.project_rev_rate_date ))
,bl.project_rev_exchange_rate = decode(p_txn_currency_code,p_project_currency_code,
bl.project_rev_exchange_rate,decode(l_plan_version_type,'REVENUE'
,l_project_rev_exchange_rate
,'ALL',l_project_rev_exchange_rate
,bl.project_rev_exchange_rate))
,bl.projfunc_rev_rate_type = decode(p_txn_currency_code,p_projfunc_currency_code,
bl.projfunc_rev_rate_type,decode(l_plan_version_type,'REVENUE'
,l_projfunc_rev_rate_type
,'ALL',l_projfunc_rev_rate_type
,bl.projfunc_rev_rate_type))
,bl.projfunc_rev_rate_date_type = decode(p_txn_currency_code,p_projfunc_currency_code,
bl.projfunc_rev_rate_date_type,decode(l_plan_version_type,'REVENUE'
,l_projfunc_rev_rate_date_type
,'ALL',l_projfunc_rev_rate_date_type
,bl.projfunc_rev_rate_date_type))
,bl.projfunc_rev_rate_date = decode(p_txn_currency_code,p_projfunc_currency_code,
bl.projfunc_rev_rate_date,decode(l_plan_version_type,'REVENUE'
,l_projfunc_rev_rate_date
,'ALL',l_projfunc_rev_rate_date
,bl.projfunc_rev_rate_date ))
,bl.projfunc_rev_exchange_rate = decode(p_txn_currency_code,p_projfunc_currency_code,
bl.projfunc_rev_exchange_rate,decode(l_plan_version_type,'REVENUE'
,l_projfunc_rev_exchange_rate
,'ALL',l_projfunc_rev_exchange_rate
,bl.projfunc_rev_exchange_rate))
WHERE bl.resource_assignment_id = p_res_assignment_id
AND bl.txn_currency_code = p_txn_currency_code
AND bl.start_date BETWEEN trunc(p_start_date) and trunc(p_end_date)
AND bl.end_date BETWEEN trunc(p_start_date) and trunc(p_end_date)
AND (l_etc_start_date is NULL
OR (l_etc_start_date is NOT NULL
and ((l_etc_start_date between bl.start_date and bl.end_date)
or (bl.start_date > l_etc_start_date))
)
);
l_stage := 'Number of BudgetLines Updated['||l_rowcount||']';
l_stage := 'End of validateAndUpdateBdgtLine API msgCt['||x_msg_count||']RetSts['||x_return_status||']';
,p_procedure_name => 'validateAndUpdateBdgtLine');
END validateAndUpdateBdgtLine;
SELECT 'Y'
FROM DUAL
WHERE EXISTS
(SELECT null
FROM pa_budget_lines bl
WHERE bl.budget_version_id = l_budget_version_id
AND (bl.cost_rejection_code is NOT NULL
OR bl.burden_rejection_code is NOT NULL
OR bl.revenue_rejection_code is NOT NULL
OR bl.pc_cur_conv_rejection_code is NOT NULL
OR bl.pfc_cur_conv_rejection_code is NOT NULL)
);
SELECT 'Y'
FROM DUAL
WHERE EXISTS
(SELECT null
FROM pa_budget_lines bl
,pa_resource_assignments ra
WHERE ra.budget_version_id = l_budget_version_id
and ra.wbs_element_version_id = p_task_str_version_id
and bl.resource_assignment_id = ra.resource_assignment_id
and bl.cost_rejection_code is NOT NULL
and bl.start_date between NVL(p_start_date,bl.start_date)
AND NVL(p_end_date,bl.end_date)
and bl.end_date between NVL(p_start_date,bl.start_date)
AND NVL(p_end_date,bl.end_date)
);
SELECT 'Y'
FROM DUAL
WHERE EXISTS
(SELECT null
FROM pa_budget_lines bl
,pa_resource_assignments ra
WHERE ra.budget_version_id = l_budget_version_id
and ra.wbs_element_version_id = p_task_str_version_id
and bl.resource_assignment_id = ra.resource_assignment_id
and bl.burden_rejection_code is NOT NULL
and bl.start_date between NVL(p_start_date,bl.start_date)
AND NVL(p_end_date,bl.end_date)
and bl.end_date between NVL(p_start_date,bl.start_date)
AND NVL(p_end_date,bl.end_date)
);
SELECT 'Y'
FROM DUAL
WHERE EXISTS
(SELECT null
FROM pa_budget_lines bl
,pa_resource_assignments ra
WHERE ra.budget_version_id = l_budget_version_id
and ra.wbs_element_version_id = p_task_str_version_id
and bl.resource_assignment_id = ra.resource_assignment_id
and bl.revenue_rejection_code is NOT NULL
and bl.start_date between NVL(p_start_date,bl.start_date)
AND NVL(p_end_date,bl.end_date)
and bl.end_date between NVL(p_start_date,bl.start_date)
AND NVL(p_end_date,bl.end_date)
);
SELECT 'Y'
FROM DUAL
WHERE EXISTS
(SELECT null
FROM pa_budget_lines bl
,pa_resource_assignments ra
WHERE ra.budget_version_id = l_budget_version_id
and ra.wbs_element_version_id = p_task_str_version_id
and bl.resource_assignment_id = ra.resource_assignment_id
and bl.pc_cur_conv_rejection_code is NOT NULL
and bl.start_date between NVL(p_start_date,bl.start_date)
AND NVL(p_end_date,bl.end_date)
and bl.end_date between NVL(p_start_date,bl.start_date)
AND NVL(p_end_date,bl.end_date)
);
SELECT 'Y'
FROM DUAL
WHERE EXISTS
(SELECT null
FROM pa_budget_lines bl
,pa_resource_assignments ra
WHERE ra.budget_version_id = l_budget_version_id
and ra.wbs_element_version_id = p_task_str_version_id
and bl.resource_assignment_id = ra.resource_assignment_id
and bl.pfc_cur_conv_rejection_code is NOT NULL
and bl.start_date between NVL(p_start_date,bl.start_date)
AND NVL(p_end_date,bl.end_date)
and bl.end_date between NVL(p_start_date,bl.start_date)
AND NVL(p_end_date,bl.end_date)
);
select bv.etc_start_date
from pa_budget_versions bv
where bv.budget_version_id = p_budget_version_id
and Exists ( select null
from pa_budget_lines bl
where bl.resource_assignment_id = p_resource_assignment_id
and bl.budget_version_id = bv.budget_version_id
) ;
select MIN(bl.start_date)
,MAX(bl.end_date)
from pa_budget_lines bl
where bl.resource_assignment_id = p_resource_assignment_id
and bl.budget_version_id = p_budget_version_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
);
SELECT bv.agreement_id
,bv.ci_id
,bv.version_type
,bv.version_name
FROM pa_budget_versions bv
WHERE bv.budget_version_id = p_budget_version_id;
SELECT agr.agreement_currency_code
FROM pa_agreements_all agr
WHERE agr.agreement_id = p_agr_id;
SELECT NVL(t.billable_flag,'N')
FROM pa_tasks t
WHERE t.task_id = p_task_id
AND t.project_id = p_project_id;
G_FpTaskBillable_Tab.Delete;
INSERT INTO pa_fp_res_assignments_tmp
(project_id
,budget_version_id
,resource_assignment_id
,resource_list_member_id
,unit_of_measure
,resource_class_code
,organization_id
,job_id
,person_id
,expenditure_type
,expenditure_category
,non_labor_resource
,bom_resource_id
,inventory_item_id
,item_category_id
,mfc_cost_type_id
--,rate_job_id
,rate_expenditure_type
,rate_based_flag
,rate_expenditure_org_id
--,res_format_id
,project_type
,org_id
,rbs_element_id
) VALUES
(p_project_id
,NVL(p_budget_version_id,-9999)
,-9999 --raid
,p_resource_list_member_Id_tab(i)
,l_unit_of_measure_tbl(i)
,l_resource_class_code_tbl(i)
,l_organization_id_tbl(i)
,l_job_id_tbl(i)
,l_person_id_tbl(i)
,l_expenditure_type_tbl(i)
,l_expenditure_category_tbl(i)
,l_non_labor_resource_tbl(i)
,l_bom_resource_id_tbl(i)
,l_inventory_item_id_tbl(i)
,l_item_category_id_tbl(i)
,l_mfc_cost_type_id_tbl(i)
--,l_rate_job_id_tbl(i)
,l_rate_expenditure_type_tbl(i)
,l_rate_based_flag_tbl(i)
,l_org_id_tbl(i) --l_rate_expenditure_org_id_tbl(i)
--,to_number(null) --l_res_format_id_tbl(i)
,p_project_type
,l_org_id_tbl(i)
,p_plsql_index_tab(i)
)
;
/* Now Update the tmp table with rates and currencys passed */
FORALL i IN p_resource_list_member_Id_tab.FIRST .. p_resource_list_member_Id_tab.LAST
UPDATE pa_fp_res_assignments_tmp TMP
SET tmp.txn_currency_code = p_txn_currency_code_tab(i)
,txn_currency_code_override = p_txn_currency_code_ovr_tab(i)
,rw_cost_rate_override = p_cost_rate_override_tab(i)
,burden_cost_rate_override = p_burden_rate_override_tab(i)
,bill_rate_override = p_bill_rate_override_tab(i)
,task_id = p_task_id_tab(i)
,txn_plan_quantity = p_quantity_tab(i)
,line_start_date = NVL(p_ra_date_tab(i),trunc(sysdate))
WHERE tmp.budget_version_id = NVL(p_budget_version_id,-9999)
AND tmp.resource_list_member_id = p_resource_list_member_Id_tab(i);
print_msg(p_pa_debug_mode,'populate_ra_details','bulk insert to res_assignments_tmp table');
INSERT INTO pa_fp_res_assignments_tmp
(project_id
,budget_version_id
,resource_assignment_id
,resource_list_member_id
,line_start_date
,txn_currency_code
,txn_currency_code_override
,rw_cost_rate_override
,burden_cost_rate_override
,bill_rate_override
,task_id
,unit_of_measure
,resource_class_code
,organization_id
,job_id
,person_id
,expenditure_type
,expenditure_category
,non_labor_resource
,bom_resource_id
,inventory_item_id
,item_category_id
,mfc_cost_type_id
,rate_job_id
,rate_expenditure_type
,rate_based_flag
,rate_expenditure_org_id
,project_type
,rbs_element_id
) SELECT
p_project_id
,NVL(p_budget_version_id,-9999)
,p_resource_assignment_id_tab(i)
,ra.resource_list_member_id
,NVL(p_ra_date_tab(i),trunc(sysdate))
,p_txn_currency_code_tab(i)
,p_txn_currency_code_ovr_tab(i)
,p_cost_rate_override_tab(i)
,p_burden_rate_override_tab(i)
,p_bill_rate_override_tab(i)
,ra.task_id
,ra.unit_of_measure
,ra.resource_class_code
,ra.organization_id
,ra.job_id
,ra.person_id
,ra.expenditure_type
,ra.expenditure_category
,ra.non_labor_resource
,ra.bom_resource_id
,ra.inventory_item_id
,ra.item_category_id
,ra.mfc_cost_type_id
,ra.rate_job_id
,ra.rate_expenditure_type
,NVL(ra.rate_based_flag,'N') rate_based_flag
,ra.rate_expenditure_org_id
,p_project_type
,p_plsql_index_tab(i)
FROM pa_resource_assignments ra
WHERE ra.resource_assignment_id = p_resource_assignment_id_tab(i);
SELECT to_number(null) res_class_bill_rate_sch_id
,to_number(null) res_class_raw_cost_sch_id
,'N' use_planning_rates_flag
,to_number(null) rev_job_rate_sch_id
,to_number(null) cost_job_rate_sch_id
,to_number(null) rev_emp_rate_sch_id
,to_number(null) cost_emp_rate_sch_id
,to_number(null) rev_non_labor_res_rate_sch_id
,to_number(null) cost_non_labor_res_rate_sch_id
,to_number(null) cost_burden_rate_sch_id
,'Y' track_workplan_costs_flag
,'COST' fp_budget_version_type
,to_number(null) resource_list_id
,'N' approved_rev_plan_type_flag
,'N' plan_in_multi_curr_flag
,to_date(null) etc_start_date
,'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
,to_number(null) project_structure_version_id
,pp.project_id
,pp.segment1 project_name
FROM pa_projects_all pp
WHERE pp.project_id = p_project_id;
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
,pp.segment1 project_name
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 tmp.rowid
,tmp.rbs_element_id
,tmp.resource_assignment_id
,tmp.txn_currency_code
,NVL(tmp.txn_plan_quantity,1) quantity
,tmp.line_start_date start_date
,tmp.burden_cost_rate_override
,tmp.rw_cost_rate_override
,tmp.bill_rate_override
,tmp.txn_currency_code_override
,tmp.task_id
,tmp.resource_list_member_id
,tmp.unit_of_measure
,tmp.resource_class_code
,tmp.organization_id
,tmp.job_id
,tmp.person_id
,tmp.expenditure_type
,tmp.expenditure_category
,tmp.revenue_category_code
,tmp.event_type
,tmp.supplier_id
,tmp.non_labor_resource
,tmp.bom_resource_id
,tmp.inventory_item_id
,tmp.item_category_id
,tmp.billable_percent
,tmp.mfc_cost_type_id
,tmp.incurred_by_res_flag
,tmp.rate_job_id
,tmp.rate_expenditure_type
,tmp.sp_fixed_date
,tmp.person_type_code
,NVL(tmp.rate_based_flag,'N') rate_based_flag
,tmp.rate_exp_func_curr_code
,tmp.rate_expenditure_org_id
,tmp.incur_by_res_class_code
,tmp.project_role_id
,tmp.resource_class_flag
,to_number(null) res_format_id --tmp.res_format_id
,tmp.task_bill_rate_org_id non_labor_bill_rate_org_id
,tmp.task_sch_discount non_labor_schedule_discount
,tmp.task_sch_date non_labor_schedule_fixed_date
,tmp.task_std_bill_rate_sch non_lab_std_bill_rt_sch_id
,tmp.emp_bill_rate_schedule_id
,tmp.job_bill_rate_schedule_id
,tmp.labor_bill_rate_org_id
,tmp.labor_sch_type
,tmp.non_labor_sch_type
,tmp.top_task_id
,NVL(tmp.billable_flag,'N') billable_flag
,to_number(null) budget_line_id
,tmp.task_name
,tmp.resource_name
FROM pa_fp_res_assignments_tmp tmp
WHERE tmp.budget_version_id = NVL(p_budget_version_id,-9999)
ORDER BY tmp.rbs_element_id ; /* added this to ensure that In and Out plsql table indexes are mapped */
DELETE FROM PA_FP_RES_ASSIGNMENTS_TMP;
DELETE FROM PA_FP_ROLLUP_TMP;
l_stage := '15: Update tmp table with task level details';
/* update the task details */
UPDATE pa_fp_res_assignments_tmp tmp
SET (tmp.task_bill_rate_org_id ---non_labor_bill_rate_org_id
,tmp.task_sch_discount ---non_labor_schedule_discount
,tmp.task_sch_date ---non_labor_schedule_fixed_date
,tmp.task_std_bill_rate_sch ---non_lab_std_bill_rt_sch_id
,tmp.emp_bill_rate_schedule_id
,tmp.job_bill_rate_schedule_id
,tmp.labor_bill_rate_org_id
,tmp.labor_sch_type
,tmp.non_labor_sch_type
,tmp.top_task_id
,tmp.billable_flag
,tmp.task_name ) =
(SELECT t.non_labor_bill_rate_org_id
,t.non_labor_schedule_discount
,t.non_labor_schedule_fixed_date
,t.non_lab_std_bill_rt_sch_id
,t.emp_bill_rate_schedule_id
,t.job_bill_rate_schedule_id
,t.labor_bill_rate_org_id
,t.labor_sch_type
,t.non_labor_sch_type
,t.top_task_id
,NVL(t.billable_flag,'Y')
,t.task_name
FROM pa_tasks t
WHERE t.task_id = tmp.task_id
AND t.project_id = p_project_id
)
WHERE tmp.budget_version_id = p_budget_version_id
AND (tmp.task_id is NOT NULL OR tmp.task_id <> 0 )
AND EXISTS (select null
from pa_tasks t1
Where t1.task_id = tmp.task_id
and t1.project_id = p_project_id
);
l_stage := 'Inserting rate api values into rollup tmp table';
INSERT INTO pa_fp_rollup_tmp tmp
(resource_assignment_id
,system_reference1
,txn_currency_code
,cost_rate
,rw_cost_rate_override
,burden_cost_rate
,burden_cost_rate_override
,bill_rate
,bill_rate_override
,burden_multiplier
,bill_markup_percentage
,cost_txn_curr_code
,rev_txn_curr_code
,cost_ind_compiled_set_id
,cost_rejection_code
,burden_rejection_code
,revenue_rejection_code
) VALUES
(x_resource_assignment_id_tab(i)
,x_resource_list_member_id_tab(i)
,x_txn_currency_code_tab(i)
,x_raw_cost_rate_tab(i)
,x_rw_cost_rate_override_tab(i)
,x_burden_cost_rate_tab(i)
,x_burden_rate_override_tab(i)
,x_bill_rate_tab(i)
,x_bill_rate_override_tab(i)
,x_burden_multiplier_tab(i)
,x_markup_percent_tab(i)
,x_cost_txn_curr_code_tab(i)
,x_rev_txn_curr_code_tab(i)
,x_ind_compiled_set_id_tab(i)
,x_cost_rejection_code_tab(i)
,x_burden_rejection_code_tab(i)
,x_revenue_rejection_code_tab(i)
);
SELECT
budget_line_id
,pa_budget_lines_s.nextval
FROM
pa_budget_lines
WHERE
budget_version_id = p_source_fin_plan_version_id ;
DELETE FROM PA_FP_BL_MAP_TMP;
l_source_budget_line_id_tbl.delete;
l_target_budget_line_id_tbl.delete;
INSERT INTO PA_FP_BL_MAP_TMP
( source_budget_line_id
,target_budget_line_id
)
VALUES ( l_source_budget_line_id_tbl(i)
,l_target_budget_line_id_tbl(i)
);