[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
p_select_criteria IN VARCHAR2 default '00',
p_project_flag IN VARCHAR2 default NULL,
p_project_id IN NUMBER default NULL,
p_assignment_id IN NUMBER default NULL,
P_ORGANIZATION_FLAG IN VARCHAR2 default NULL,
p_organization_id IN NUMBER default NULL,
P_Start_Organization_Flag IN VARCHAR2 default NULL,
p_start_organization_id IN NUMBER default NULL,
p_debug_mode IN VARCHAR2 default 'N',
p_gen_report_flag IN VARCHAR2 default 'N'
) IS
CURSOR Org_Hierarchy(c_organization_id NUMBER) IS
SELECT
Org.CHILD_ORGANIZATION_ID organization_id
FROM
pa_org_hierarchy_denorm org,
pa_implementations imp
WHERE Org.PA_ORG_USE_TYPE='REPORTING' and
Org.PARENT_ORGANIZATION_ID=c_organization_Id and
/* Bug fix: 4367847 NVL(org.ORG_ID,-99)=NVL(imp.ORG_ID,-99) and */
org.ORG_ID = imp.ORG_ID and
Org.ORG_HIERARCHY_VERSION_ID=imp.ORG_STRUCTURE_VERSION_ID
ORDER BY
Org.CHILD_ORGANIZATION_ID;
PA_DEBUG.g_err_stage := 'Select Criteria :'||p_select_criteria;
SELECT NVL(org_id,-99) INTO
l_excep_org_id FROM PA_IMPLEMENTATIONS;
( p_run_mode = 'P' AND p_select_criteria IN ( '02' , '01' ) ) THEN
IF P_PA_DEBUG_MODE = 'Y' THEN
PA_DEBUG.g_err_stage := 'Inside Full or Incremental call or specific Org call';
p_select_criteria => p_select_criteria,
p_project_id => p_project_id,
p_assignment_id => p_assignment_id,
p_organization_id => p_organization_id,
p_debug_mode => p_debug_mode );
ELSIF p_run_mode = 'P' AND p_select_criteria = '03' THEN
IF P_PA_DEBUG_MODE = 'Y' THEN
PA_DEBUG.g_err_stage := 'Inside Org hierarchy call';
p_select_criteria => p_select_criteria,
p_project_id => p_project_id,
p_assignment_id => p_assignment_id,
p_organization_id => Org_Rec.organization_id,
p_debug_mode => p_debug_mode );
SELECT NVL(org_id,-99) INTO
l_excep_org_id FROM PA_IMPLEMENTATIONS;
argument2 => p_select_criteria,
argument3 => p_project_flag,
argument4 => p_project_id,
argument5 => p_assignment_id,
argument6 => P_ORGANIZATION_FLAG,
argument7 => p_organization_id,
argument8 => P_Start_Organization_Flag,
argument9 => p_start_organization_id);
p_select_criteria IN VARCHAR2 default '00',
p_project_id IN NUMBER default NULL,
p_assignment_id IN NUMBER default NULL,
p_organization_id IN NUMBER default NULL,
p_debug_mode IN VARCHAR2 default 'N'
) IS
l_count NUMBER := 5;
CURSOR fcst_item_All(c_start_date DATE) IS SELECT
forecast_item_id,
forecast_item_type,
EXPENDITURE_ORG_ID,
EXPENDITURE_ORGANIZATION_ID,
PROJECT_ORG_ID,
PROJECT_ORGANIZATION_ID,
PROJECT_ID,
PROJECT_TYPE_CLASS,
PERSON_ID,
RESOURCE_ID,
NVL(ASSIGNMENT_ID,-9999),
ITEM_DATE,
ITEM_UOM,
ITEM_QUANTITY,
PVDR_PA_PERIOD_NAME,
RCVR_PA_PERIOD_NAME,
EXPENDITURE_TYPE,
EXPENDITURE_TYPE_CLASS,
Tp_Amount_Type,
Delete_Flag
FROM
Pa_Forecast_Items
WHERE Error_Flag = 'N' AND Item_Date >= c_start_date
ORDER BY PROJECT_ID,ASSIGNMENT_ID;
CURSOR fcst_item_Inc IS SELECT
forecast_item_id,
forecast_item_type,
EXPENDITURE_ORG_ID,
EXPENDITURE_ORGANIZATION_ID,
PROJECT_ORG_ID,
PROJECT_ORGANIZATION_ID,
PROJECT_ID,
PROJECT_TYPE_CLASS,
PERSON_ID,
RESOURCE_ID,
NVL(ASSIGNMENT_ID,-9999),
ITEM_DATE,
ITEM_UOM,
ITEM_QUANTITY,
PVDR_PA_PERIOD_NAME,
RCVR_PA_PERIOD_NAME,
EXPENDITURE_TYPE,
EXPENDITURE_TYPE_CLASS,
Tp_Amount_Type,
Delete_Flag
FROM
Pa_Forecast_Items WHERE
forecast_amt_calc_flag = 'N' AND
Error_Flag = 'N'
ORDER BY PROJECT_ID,ASSIGNMENT_ID;
CURSOR fcst_item_Prj(c_project_id NUMBER) IS SELECT
forecast_item_id,
forecast_item_type,
EXPENDITURE_ORG_ID,
EXPENDITURE_ORGANIZATION_ID,
PROJECT_ORG_ID,
PROJECT_ORGANIZATION_ID,
PROJECT_ID,
PROJECT_TYPE_CLASS,
PERSON_ID,
RESOURCE_ID,
NVL(ASSIGNMENT_ID,-9999),
ITEM_DATE,
ITEM_UOM,
ITEM_QUANTITY,
PVDR_PA_PERIOD_NAME,
RCVR_PA_PERIOD_NAME,
EXPENDITURE_TYPE,
EXPENDITURE_TYPE_CLASS,
Tp_Amount_Type,
Delete_Flag
FROM
Pa_Forecast_Items WHERE
Project_Id = c_project_id AND
Error_Flag = 'N'
ORDER BY PROJECT_ID,ASSIGNMENT_ID;
c_assignment_id NUMBER) IS SELECT
forecast_item_id,
forecast_item_type,
EXPENDITURE_ORG_ID,
EXPENDITURE_ORGANIZATION_ID,
PROJECT_ORG_ID,
PROJECT_ORGANIZATION_ID,
PROJECT_ID,
PROJECT_TYPE_CLASS,
PERSON_ID,
RESOURCE_ID,
NVL(ASSIGNMENT_ID,-9999),
ITEM_DATE,
ITEM_UOM,
ITEM_QUANTITY,
PVDR_PA_PERIOD_NAME,
RCVR_PA_PERIOD_NAME,
EXPENDITURE_TYPE,
EXPENDITURE_TYPE_CLASS,
Tp_Amount_Type,
Delete_Flag
FROM
Pa_Forecast_Items WHERE
Project_Id = c_project_id AND
Assignment_Id = c_assignment_id AND
Error_Flag = 'N';
SELECT
forecast_item_id,
forecast_item_type,
EXPENDITURE_ORG_ID,
EXPENDITURE_ORGANIZATION_ID,
PROJECT_ORG_ID,
PROJECT_ORGANIZATION_ID,
PROJECT_ID,
PROJECT_TYPE_CLASS,
PERSON_ID,
RESOURCE_ID,
Assignment_Id,
ITEM_DATE,
ITEM_UOM,
ITEM_QUANTITY,
PVDR_PA_PERIOD_NAME,
RCVR_PA_PERIOD_NAME,
EXPENDITURE_TYPE,
EXPENDITURE_TYPE_CLASS,
Tp_Amount_Type,
Delete_Flag
FROM
(
SELECT
forecast_item_id,
forecast_item_type,
EXPENDITURE_ORG_ID,
EXPENDITURE_ORGANIZATION_ID,
PROJECT_ORG_ID,
PROJECT_ORGANIZATION_ID,
PROJECT_ID,
PROJECT_TYPE_CLASS,
PERSON_ID,
RESOURCE_ID,
NVL(ASSIGNMENT_ID,-9999) Assignment_Id,
ITEM_DATE,
ITEM_UOM,
ITEM_QUANTITY,
PVDR_PA_PERIOD_NAME,
RCVR_PA_PERIOD_NAME,
EXPENDITURE_TYPE,
EXPENDITURE_TYPE_CLASS,
Tp_Amount_Type,
Delete_Flag
FROM
Pa_Forecast_Items WHERE
EXPENDITURE_ORGANIZATION_ID = c_organization_id AND
Error_Flag = 'N'
UNION
SELECT
forecast_item_id,
forecast_item_type,
EXPENDITURE_ORG_ID,
EXPENDITURE_ORGANIZATION_ID,
PROJECT_ORG_ID,
PROJECT_ORGANIZATION_ID,
PROJECT_ID,
PROJECT_TYPE_CLASS,
PERSON_ID,
RESOURCE_ID,
NVL(ASSIGNMENT_ID,-9999) ASSIGNMENT_ID,
ITEM_DATE,
ITEM_UOM,
ITEM_QUANTITY,
PVDR_PA_PERIOD_NAME,
RCVR_PA_PERIOD_NAME,
EXPENDITURE_TYPE,
EXPENDITURE_TYPE_CLASS,
Tp_Amount_Type,
Delete_Flag
FROM
Pa_Forecast_Items WHERE
PROJECT_ORGANIZATION_ID = c_organization_id AND
Error_Flag = 'N' ) DUAL
ORDER BY Project_Id,Assignment_Id;
SELECT
NVL(imp.Org_Id,-99) Org_Id,
NVL(fcst.ORG_FCST_PERIOD_TYPE,'AaBb') ORG_FCST_PERIOD_TYPE,
fcst.START_PERIOD_NAME START_PERIOD_NAME,
imp.Pa_Period_Type Pa_Period_Type,
sob.PERIOD_SET_NAME PERIOD_SET_NAME,
sob.ACCOUNTED_PERIOD_TYPE ACCOUNTED_PERIOD_TYPE
FROM Pa_Forecasting_Options_All fcst,
Pa_Implementations_All imp,
Gl_Sets_Of_Books sob
WHERE /* Bug fix:4367847 NVL(imp.Org_Id,-99) = NVL(fcst.Org_Id,-99) AND */
imp.Org_Id = NVL(fcst.Org_Id,-99) AND
sob.SET_OF_BOOKS_ID = imp.SET_OF_BOOKS_ID;
Select distinct assignment_id from PA_FORECAST_ITEMS
Where request_id = c_request_id and Forecast_amt_calc_flag = 'Y'
and assignment_id is NOT NULL;
l_fi_delete_flag_tab PA_PLSQL_DATATYPES.Char1TabTyp;
l_last_updated_by NUMBER := FND_GLOBAL.USER_ID;
l_last_update_date DATE := l_creation_date;
l_last_update_login NUMBER := FND_GLOBAL.LOGIN_ID;
SELECT Start_Date INTO l_curr_process_start_date FROM Gl_Periods
WHERE Period_Set_Name = opt_rec.Period_Set_Name AND
Period_Type = opt_rec.Pa_Period_Type AND
Period_Name = opt_rec.START_PERIOD_NAME AND
ADJUSTMENT_PERIOD_FLAG = 'N';
SELECT Start_Date INTO l_curr_process_start_date FROM Gl_Periods
WHERE Period_Set_Name = opt_rec.Period_Set_Name AND
Period_Type = opt_rec.Accounted_Period_Type AND
Period_Name = opt_rec.START_PERIOD_NAME AND
ADJUSTMENT_PERIOD_FLAG = 'N';
ELSIF p_run_mode = 'P' AND p_select_criteria = '01' AND
p_project_id IS NOT NULL AND p_assignment_id IS NULL THEN
IF P_PA_DEBUG_MODE = 'Y' THEN
PA_DEBUG.g_err_stage := 'Opening Fcst_Item_Prj cursor';
ELSIF p_run_mode = 'P' AND p_select_criteria = '01' AND
p_project_id IS NOT NULL AND p_assignment_id IS NOT NULL THEN
IF P_PA_DEBUG_MODE = 'Y' THEN
PA_DEBUG.g_err_stage := 'Opening Fcst_Item_Prj_Asg cursor';
ELSIF p_run_mode = 'P' AND p_select_criteria in ( '02','03') THEN
IF P_PA_DEBUG_MODE = 'Y' THEN
PA_DEBUG.g_err_stage := 'Opening Fcst_Ftem_Organization cursor';
l_fi_id_tab.delete;
l_fi_item_type_tab.delete;
l_fi_exp_orgid_tab.delete;
l_fi_exp_organizationid_tab.delete;
l_fi_proj_orgid_tab.delete;
l_fi_proj_organizationid_tab.delete;
l_fi_projid_tab.delete;
l_fi_proj_type_class_tab.delete;
l_fi_personid_tab.delete;
l_fi_resid_tab.delete;
l_fi_asgid_tab.delete;
l_fi_date_tab.delete;
l_fi_uom_tab.delete;
l_fi_qty_tab.delete;
l_fi_pvdr_papd_tab.delete;
l_fi_rcvr_papd_tab.delete;
l_fi_exptype_tab.delete;
l_fi_exptypeclass_tab.delete;
l_fi_amount_type_tab.delete;
l_fi_process_flag_tab.delete;
l_fi_rev_rejct_reason_tab.delete;
l_fi_cst_rejct_reason_tab.delete;
l_fi_bd_rejct_reason_tab.delete;
l_fi_others_rejct_reason_tab.delete;
l_fi_tp_rejct_reason_tab.delete;
l_fi_delete_flag_tab.delete;
l_prj_type_tab.delete;
l_distribution_rule_tab.delete;
l_bill_job_group_id_tab.delete;
l_cost_job_group_id_tab.delete;
l_job_bill_rate_sch_id_tab.delete;
l_emp_bill_rate_sch_id_tab.delete;
l_prj_curr_code_tab.delete;
l_prj_rate_date_tab.delete;
l_prj_rate_type_tab.delete;
l_prj_bil_rate_dt_code_tab.delete;
l_prj_bil_rate_type_tab.delete;
l_prj_bil_rate_date_tab.delete;
l_prj_bil_ex_rate_tab.delete;
l_prjfunc_curr_code_tab.delete;
l_prjfunc_cost_rt_type_tab.delete;
l_prjfunc_cost_rt_dt_tab.delete;
l_prjfunc_bil_rt_dt_code_tab.delete;
l_prjfunc_bil_rate_type_tab.delete;
l_prjfunc_bil_rate_date_tab.delete;
l_prjfunc_bil_ex_rate_tab.delete;
l_labor_sch_discount_tab.delete;
l_asg_precedes_task_tab.delete;
l_labor_bill_rate_orgid_tab.delete;
l_labor_std_bill_rate_sch_tab.delete;
l_labor_sch_fixed_dt_tab.delete;
l_labor_sch_type_tab.delete;
l_prj_cost_rate_schid_tab.delete;
l_asg_fcst_jobid_tab.delete;
l_asg_fcst_jobgroupid_tab.delete;
l_cc_sys_link_tab.delete;
l_cc_taskid_tab.delete;
l_cc_expitemid_tab.delete;
l_cc_transsource_tab.delete;
l_cc_NLOrgzid_tab.delete;
l_cc_prvdreid_tab.delete;
l_cc_recvreid_tab.delete;
lx_cc_status_tab.delete;
lx_cc_type_tab.delete;
lx_cc_code_tab.delete;
lx_cc_prvdr_orgzid_tab.delete;
lx_cc_recvr_orgzid_tab.delete;
lx_cc_recvr_orgid_tab.delete;
lx_cc_prvdr_orgid_tab.delete;
l_tp_exp_category.delete;
l_tp_exp_itemid.delete;
l_tp_labor_nl_flag.delete;
l_tp_taskid.delete;
l_tp_scheduleid.delete;
l_tp_denom_currcode.delete;
l_tp_rev_distributed_flag.delete;
l_tp_compute_flag.delete;
l_tp_fixed_date.delete;
l_tp_denom_raw_cost.delete;
l_tp_denom_bd_cost.delete;
l_tp_raw_revenue.delete;
l_tp_nl_resource.delete;
l_tp_nl_resource_orgzid.delete;
l_tp_pa_date.delete;
l_tp_asg_precedes_task_tab.delete; -- Added for bug 3260017
lx_proj_tp_rate_type.delete;
lx_proj_tp_rate_date.delete;
lx_proj_tp_exchange_rate.delete;
lx_proj_tp_amt.delete;
lx_projfunc_tp_rate_type.delete;
lx_projfunc_tp_rate_date.delete;
lx_projfunc_tp_exchange_rate.delete;
lx_projfunc_tp_amt.delete;
lx_denom_tp_currcode.delete;
lx_denom_tp_amt.delete;
lx_expfunc_tp_rate_type.delete;
lx_expfunc_tp_rate_date.delete;
lx_expfunc_tp_exchange_rate.delete;
lx_expfunc_tp_amt.delete;
lx_cc_markup_basecode.delete;
lx_tp_ind_compiled_setid.delete;
lx_tp_bill_rate.delete;
lx_tp_base_amount.delete;
lx_tp_bill_markup_percent.delete;
lx_tp_sch_line_percent.delete;
lx_tp_rule_percent.delete;
lx_tp_job_id.delete;
lx_tp_error_code.delete;
l_fia_cost_txn_curr_code.delete;
l_fia_rev_txn_curr_code.delete;
l_fia_txn_raw_cost.delete;
l_fia_txn_bd_cost.delete;
l_fia_txn_revenue.delete;
l_fia_expfunc_curr_code.delete;
l_fia_expfunc_raw_cost.delete;
l_fia_expfunc_bd_cost.delete;
l_fia_projfunc_raw_cost.delete;
l_fia_projfunc_bd_cost.delete;
l_fia_projfunc_revenue.delete;
l_fia_proj_raw_cost.delete;
l_fia_proj_bd_cost.delete;
l_fia_proj_revenue.delete;
l_fia_proj_cost_rate_type.delete;
l_fia_proj_cost_rate_date.delete;
l_fia_proj_cost_ex_rate.delete;
l_fia_proj_rev_rate_type.delete;
l_fia_proj_rev_rate_date.delete;
l_fia_proj_rev_ex_rate.delete;
l_fia_expfunc_cost_rate_type.delete;
l_fia_expfunc_cost_rate_date.delete;
l_fia_expfunc_cost_ex_rate.delete;
l_fia_projfunc_cost_rate_type.delete;
l_fia_projfunc_cost_rate_date.delete;
l_fia_projfunc_cost_ex_rate.delete;
l_fia_projfunc_rev_rate_type.delete;
l_fia_projfunc_rev_rate_date.delete;
l_fia_projfunc_rev_ex_rate.delete;
l_fid_fcst_itemid.delete;
l_fid_line_num.delete;
l_fid_item_date.delete;
l_fid_item_uom.delete;
l_fid_item_qty.delete;
l_fid_reversed_flag.delete;
l_fid_net_zero_flag.delete;
l_fid_line_num_reversed.delete;
l_fid_cost_txn_curr_code.delete;
l_fid_rev_txn_curr_code.delete;
l_fid_txn_raw_cost.delete;
l_fid_txn_bd_cost.delete;
l_fid_txn_revenue.delete;
l_fid_expfunc_curr_code.delete;
l_fid_expfunc_raw_cost.delete;
l_fid_expfunc_bd_cost.delete;
l_fid_projfunc_curr_code.delete;
l_fid_projfunc_raw_cost.delete;
l_fid_projfunc_bd_cost.delete;
l_fid_projfunc_revenue.delete;
l_fid_proj_curr_code.delete;
l_fid_proj_raw_cost.delete;
l_fid_proj_bd_cost.delete;
l_fid_proj_revenue.delete;
l_fid_proj_cost_rate_type.delete;
l_fid_proj_cost_rate_date.delete;
l_fid_proj_cost_ex_rate.delete;
l_fid_proj_rev_rate_type.delete;
l_fid_proj_rev_rate_date.delete;
l_fid_proj_rev_ex_rate.delete;
l_fid_expfunc_cost_rate_type.delete;
l_fid_expfunc_cost_rate_date.delete;
l_fid_expfunc_cost_ex_rate.delete;
l_fid_projfunc_cost_rate_type.delete;
l_fid_projfunc_cost_rate_date.delete;
l_fid_projfunc_cost_ex_rate.delete;
l_fid_projfunc_rev_rate_type.delete;
l_fid_projfunc_rev_rate_date.delete;
l_fid_projfunc_rev_ex_rate.delete;
l_fid_proj_tp_rate_type.delete;
l_fid_proj_tp_rate_date.delete;
l_fid_proj_tp_ex_rate.delete;
l_fid_proj_tp_amt.delete;
l_fid_projfunc_tp_rate_type.delete;
l_fid_projfunc_tp_rate_date.delete;
l_fid_projfunc_tp_ex_rate.delete;
l_fid_projfunc_tp_amt.delete;
l_fid_denom_tp_currcode.delete;
l_fid_denom_tp_amt.delete;
l_fid_expfunc_tp_rate_type.delete;
l_fid_expfunc_tp_rate_date.delete;
l_fid_expfunc_tp_ex_rate.delete;
l_fid_expfunc_tp_amt.delete;
l_fi_delete_flag_tab LIMIT l_fetch_size;
l_fi_delete_flag_tab LIMIT l_fetch_size;
ELSIF p_run_mode = 'P' AND p_select_criteria = '01' AND
p_project_id IS NOT NULL AND p_assignment_id IS NULL THEN
IF P_PA_DEBUG_MODE = 'Y' THEN
PA_DEBUG.g_err_stage := 'Fetching Fcst_Item_Prj';
l_fi_delete_flag_tab LIMIT l_fetch_size;
ELSIF p_run_mode = 'P' AND p_select_criteria = '01' AND
p_project_id IS NOT NULL AND p_assignment_id IS NOT NULL THEN
IF P_PA_DEBUG_MODE = 'Y' THEN
PA_DEBUG.g_err_stage := 'Fetching Fcst_Item_Prj_Asg';
l_fi_delete_flag_tab LIMIT l_fetch_size;
ELSIF p_run_mode = 'P' AND p_select_criteria in ( '02','03') THEN
IF P_PA_DEBUG_MODE = 'Y' THEN
PA_DEBUG.g_err_stage := 'Fetching fcst_item_Organization';
l_fi_delete_flag_tab LIMIT l_fetch_size;
DELETE FROM Pa_Fi_Amount_Dtls_Tmp;
/* Forecast Item records should be locked to prevent Update from
Forecast Item generation Process */
FORALL l_fi_lck_idx IN 1 .. l_fi_id_tab.COUNT
UPDATE Pa_Forecast_Items SET Forecast_Amt_Calc_Flag = 'P'
WHERE
Forecast_Item_Id = l_fi_id_tab(l_fi_lck_idx);
dbms_output.put_line('del fg:'||l_fi_delete_flag_tab(i) ); */
IF l_fi_delete_flag_tab(i) = 'Y' OR l_fi_qty_tab(i) <= 0 THEN
l_fi_process_flag_tab(i) := 'N';
SELECT Project_Type,
DISTRIBUTION_RULE,
BILL_JOB_GROUP_ID,
COST_JOB_GROUP_ID,
JOB_BILL_RATE_SCHEDULE_ID,
EMP_BILL_RATE_SCHEDULE_ID,
PROJECT_CURRENCY_CODE,
PROJECT_RATE_DATE,
PROJECT_RATE_TYPE,
PROJECT_BIL_RATE_DATE_CODE,
PROJECT_BIL_RATE_TYPE,
PROJECT_BIL_RATE_DATE,
PROJECT_BIL_EXCHANGE_RATE,
PROJFUNC_CURRENCY_CODE,
PROJFUNC_COST_RATE_TYPE,
PROJFUNC_COST_RATE_DATE,
PROJFUNC_BIL_RATE_DATE_CODE,
PROJFUNC_BIL_RATE_TYPE,
PROJFUNC_BIL_RATE_DATE,
PROJFUNC_BIL_EXCHANGE_RATE,
LABOR_TP_SCHEDULE_ID,
LABOR_TP_FIXED_DATE,
LABOR_SCHEDULE_DISCOUNT,
ASSIGN_PRECEDES_TASK,
NVL(LABOR_BILL_RATE_ORG_ID,-99),
LABOR_STD_BILL_RATE_SCHDL,
LABOR_SCHEDULE_FIXED_DATE,
LABOR_SCH_TYPE
INTO
l_prj_type,
l_distribution_rule,
l_bill_job_group_id,
l_cost_job_group_id,
l_job_bill_rate_sch_id,
l_emp_bill_rate_sch_id,
l_prj_curr_code,
l_prj_rate_date,
l_prj_rate_type,
l_prj_bil_rate_date_code,
l_prj_bil_rate_type,
l_prj_bil_rate_date,
l_prj_bil_ex_rate,
l_prjfunc_curr_code,
l_prjfunc_cost_rate_type,
l_prjfunc_cost_rate_date,
l_prjfunc_bil_rate_date_code,
l_prjfunc_bil_rate_type,
l_prjfunc_bil_rate_date,
l_prjfunc_bil_ex_rate,
l_labor_tp_schedule_id,
l_labor_tp_fixed_date,
l_labor_sch_discount,
l_asg_precedes_task,
l_labor_bill_rate_orgid,
l_labor_std_bill_rate_sch,
l_labor_sch_fixed_dt,
l_labor_sch_type
FROM Pa_Projects_All P
WHERE P.Project_Id = l_prev_project_id;
SELECT JOB_COST_RATE_SCHEDULE_ID INTO
l_fcst_cost_rate_schid
FROM PA_FORECASTING_OPTIONS_ALL
WHERE NVL(ORG_ID,-99) = l_prev_proj_orgid AND
JOB_COST_RATE_SCHEDULE_ID IS NOT NULL;
SELECT Fcst_Job_Id,
Fcst_Job_Group_Id,
Project_Role_Id,
MARKUP_PERCENT,
BILL_RATE_OVERRIDE,
BILL_RATE_CURR_OVERRIDE,
MARKUP_PERCENT_OVERRIDE,
TP_RATE_OVERRIDE,
TP_CURRENCY_OVERRIDE,
TP_CALC_BASE_CODE_OVERRIDE,
TP_PERCENT_APPLIED_OVERRIDE,
ASSIGNMENT_TYPE,
STATUS_CODE
INTO
l_asg_fcst_job_id,
l_asg_fcst_job_group_id,
l_asg_project_role_id,
l_asg_markup_percent,
l_asg_bill_rate_override,
l_asg_bill_rate_curr_override,
l_asg_markup_percent_override,
l_asg_tp_rate_override,
l_asg_tp_curr_override,
l_asg_tp_calc_base_code_ovr,
l_asg_tp_percent_applied_ovr,
l_prj_assignment_type,
l_prj_status_code
FROM PA_PROJECT_ASSIGNMENTS P
WHERE P.Assignment_Id = l_prev_asg_id;
SELECT PR.DEFAULT_JOB_ID,
PJ.JOB_GROUP_ID
INTO
l_asg_fcst_job_id,
l_asg_fcst_job_group_id
FROM PA_PROJECT_ROLE_TYPES PR,
PER_JOBS PJ
WHERE
PR.PROJECT_ROLE_ID = l_asg_project_role_id AND
PJ.JOB_ID = PR.DEFAULT_JOB_ID;
SELECT EXPENDITURE_CATEGORY
INTO l_cc_exp_category
FROM pa_expenditure_types WHERE
EXPENDITURE_TYPE = l_prev_exp_type;
SELECT
TP_AMOUNT_TYPE
INTO
l_fi_amount_type_tab(i)
FROM Pa_Forecast_Item_Details
WHERE
FORECAST_ITEM_ID = l_fi_id_tab(i) AND
Line_Num = ( SELECT MAX(Line_Num) FROM
Pa_Forecast_Item_Details WHERE
Forecast_Item_Id = l_fi_id_tab(i) AND
Net_Zero_Flag = 'N' );
l_fi_delete_flag_tab(i) );
l_rt_fi_id_tab.delete;
l_rt_start_date_tab.delete;
l_rt_qty_tab.delete;
l_rt_exp_org_id_tab.delete;
l_rt_exp_organization_id_tab.delete;
l_rt_system_linkage_tab.delete;
lx_rt_others_rejct_reason_tab.delete;
l_rt_pfunc_rev_rt_date_tab.delete;
l_rt_pfunc_rev_rt_type_tab.delete;
l_rt_pfunc_rev_ex_rt_tab.delete;
l_rt_pfunc_cost_rt_date_tab.delete;
l_rt_pfunc_cost_rt_type_tab.delete;
l_rt_proj_cost_rt_date_tab.delete;
l_rt_proj_cost_rt_type_tab.delete;
l_rt_proj_rev_rt_date_tab.delete;
l_rt_proj_rev_rt_type_tab.delete;
l_rt_proj_rev_ex_rt_tab.delete;
l_rt_proj_rev_rt_dt_code_tab.delete;
l_rt_pfunc_rev_rt_dt_code_tab.delete;
l_rt_fi_id_tab.delete;
l_rt_start_date_tab.delete;
l_rt_qty_tab.delete;
l_rt_system_linkage_tab.delete;
l_rt_exp_org_id_tab.delete;
l_rt_exp_organization_id_tab.delete;
lx_rt_others_rejct_reason_tab.delete;
l_rt_pfunc_rev_rt_date_tab.delete;
l_rt_pfunc_rev_rt_type_tab.delete;
l_rt_pfunc_rev_ex_rt_tab.delete;
l_rt_pfunc_cost_rt_date_tab.delete;
l_rt_pfunc_cost_rt_type_tab.delete;
l_rt_proj_cost_rt_date_tab.delete;
l_rt_proj_cost_rt_type_tab.delete;
l_rt_proj_rev_rt_date_tab.delete;
l_rt_proj_rev_rt_type_tab.delete;
l_rt_proj_rev_ex_rt_tab.delete;
l_rt_proj_rev_rt_dt_code_tab.delete;
l_rt_pfunc_rev_rt_dt_code_tab.delete;
SELECT End_Date INTO l_tp_pa_date(l_temp)
FROM Pa_Periods_All WHERE
PERIOD_NAME = l_fi_pvdr_papd_tab(l_temp) AND
-- begin:bug:5938943: NVL function has been removed to achieve the performance gain
Org_Id = l_fi_exp_orgid_tab(l_temp);
l_tp_asgid.delete;
INSERT INTO Pa_Fi_Amount_Dtls_Tmp(
FORECAST_ITEM_ID ,
ITEM_DATE ,
ITEM_UOM ,
ITEM_QUANTITY ,
COST_TXN_CURRENCY_CODE ,
REVENUE_TXN_CURRENCY_CODE ,
TXN_RAW_COST ,
TXN_BURDENED_COST ,
TXN_REVENUE ,
TP_TXN_CURRENCY_CODE_IN ,
TP_TXN_CURRENCY_CODE_OUT ,
TXN_TRANSFER_PRICE ,
PROJECT_CURRENCY_CODE ,
PROJECT_COST_RATE_DATE ,
PROJECT_COST_RATE_TYPE ,
PROJECT_COST_EXCHANGE_RATE ,
PROJECT_RAW_COST ,
PROJECT_BURDENED_COST ,
PROJECT_REVENUE_RATE_DATE ,
PROJECT_REVENUE_RATE_TYPE ,
PROJECT_REVENUE_EXCHANGE_RATE,
PROJECT_REVENUE ,
PROJECT_TP_RATE_DATE ,
PROJECT_TP_RATE_TYPE ,
PROJECT_TP_EXCHANGE_RATE ,
PROJECT_TRANSFER_PRICE ,
PROJFUNC_CURRENCY_CODE ,
PROJFUNC_COST_RATE_DATE ,
PROJFUNC_COST_RATE_TYPE ,
PROJFUNC_COST_EXCHANGE_RATE ,
PROJFUNC_RAW_COST ,
PROJFUNC_BURDENED_COST ,
PROJFUNC_REVENUE_RATE_DATE ,
PROJFUNC_REVENUE_RATE_TYPE ,
PROJFUNC_REVENUE_EXCHANGE_RATE,
PROJFUNC_REVENUE ,
PROJFUNC_TP_RATE_DATE ,
PROJFUNC_TP_RATE_TYPE ,
PROJFUNC_TP_EXCHANGE_RATE ,
PROJFUNC_TRANSFER_PRICE ,
EXPFUNC_CURRENCY_CODE ,
EXPFUNC_COST_RATE_DATE ,
EXPFUNC_COST_RATE_TYPE ,
EXPFUNC_COST_EXCHANGE_RATE ,
EXPFUNC_RAW_COST ,
EXPFUNC_BURDENED_COST ,
EXPFUNC_TP_RATE_DATE ,
EXPFUNC_TP_RATE_TYPE ,
EXPFUNC_TP_EXCHANGE_RATE ,
EXPFUNC_TRANSFER_PRICE ,
CC_PRVDR_ORG_ID ,
CC_PRVDR_ORGANIZITION_ID ,
CC_RECVR_ORG_ID ,
CC_RECVR_ORGANIZITION_ID ,
EXPENDITURE_ORGANIZATION_ID ,
EXPENDITURE_TYPE ,
EXPENDITURE_TYPE_CLASS ,
EXPENDITURE_CATEGORY ,
TP_LABOR_NL_FLAG ,
TP_TASK_ID ,
TP_SCHEDULE_ID ,
TP_REV_DISTRIBUTED_FLAG ,
TP_COMPUTE_FLAG ,
TP_FIXED_DATE ,
PROJECT_ID ,
PERSON_ID ,
FORECAST_JOB_ID ,
TP_NL_RESOURCE ,
TP_NL_RESOURCE_ORGZ_ID ,
TP_PA_DATE ,
TP_AMOUNT_TYPE ,
assignment_id ,
fi_process_flag ,
delete_flag ,
tp_error_code ,
COST_REJECTION_CODE ,
REV_REJECTION_CODE ,
BURDEN_REJECTION_CODE ,
OTHER_REJECTION_CODE ,
TP_DENOM_RAW_COST ,
TP_DENOM_BURDENED_COST ,
TP_RAW_REVENUE ,
tp_ind_compiled_setid ,
tp_bill_rate ,
tp_base_amount ,
tp_bill_markup_percent ,
tp_sch_line_percent ,
tp_rule_percent ,
tp_job_id ,
cc_markup_basecode )
VALUES(
l_fi_id_tab(b_tmp),
l_fi_date_tab(b_tmp),
l_fi_uom_tab(b_tmp),
l_fi_qty_tab(b_tmp),
l_fia_cost_txn_curr_code(b_tmp),
l_fia_rev_txn_curr_code(b_tmp),
l_fia_txn_raw_cost(b_tmp),
l_fia_txn_bd_cost(b_tmp),
l_fia_txn_revenue(b_tmp),
l_tp_denom_currcode(b_tmp), -- tp txn curr code
lx_denom_tp_currcode(b_tmp),
null, -- TXN_TRANSFER_PRICE
l_prj_curr_code_tab(b_tmp),
l_fia_proj_cost_rate_date(b_tmp),
l_fia_proj_cost_rate_type(b_tmp),
l_fia_proj_cost_ex_rate(b_tmp),
l_fia_proj_raw_cost(b_tmp),
l_fia_proj_bd_cost(b_tmp),
l_fia_proj_rev_rate_date(b_tmp),
l_fia_proj_rev_rate_type(b_tmp),
l_fia_proj_rev_ex_rate(b_tmp),
l_fia_proj_revenue(b_tmp),
null, -- PROJECT_TP_RATE_DATE
null, -- PROJECT_TP_RATE_TYPE
null, -- PROJECT_TP_EXCHANGE_RATE
null, -- PROJECT_TRANSFER_PRICE
l_prjfunc_curr_code_tab(b_tmp),
l_fia_projfunc_cost_rate_date(b_tmp),
l_fia_projfunc_cost_rate_type(b_tmp),
l_fia_projfunc_cost_ex_rate(b_tmp),
l_fia_projfunc_raw_cost(b_tmp),
l_fia_projfunc_bd_cost(b_tmp),
l_fia_projfunc_rev_rate_date (b_tmp),
l_fia_projfunc_rev_rate_type(b_tmp),
l_fia_projfunc_rev_ex_rate (b_tmp),
l_fia_projfunc_revenue(b_tmp),
null, -- PROJFUNC_TP_RATE_DATE
null, -- PROJFUNC_TP_RATE_TYPE
null, -- PROJFUNC_TP_EXCHANGE_RATE
null, -- PROJFUNC_TRANSFER_PRICE
l_fia_expfunc_curr_code(b_tmp),
l_fia_expfunc_cost_rate_date(b_tmp),
l_fia_expfunc_cost_rate_type(b_tmp),
l_fia_expfunc_cost_ex_rate(b_tmp),
l_fia_expfunc_raw_cost(b_tmp),
l_fia_expfunc_bd_cost(b_tmp),
null, -- EXPFUNC_TP_RATE_DATE
null, -- EXPFUNC_TP_RATE_TYPE
null, -- EXPFUNC_TP_EXCHANGE_RATE
null, -- EXPFUNC_TRANSFER_PRICE
lx_cc_prvdr_orgid_tab(b_tmp),
lx_cc_prvdr_orgzid_tab(b_tmp),
lx_cc_recvr_orgid_tab(b_tmp),
lx_cc_recvr_orgzid_tab(b_tmp),
l_fi_exp_organizationid_tab(b_tmp),
l_fi_exptype_tab(b_tmp),
l_fi_exptypeclass_tab(b_tmp),
l_tp_exp_category(b_tmp),
l_tp_labor_nl_flag(b_tmp),
l_tp_taskid(b_tmp),
l_tp_scheduleid(b_tmp),
l_tp_rev_distributed_flag(b_tmp),
l_tp_compute_flag(b_tmp),
l_tp_fixed_date(b_tmp),
l_fi_projid_tab(b_tmp),
l_fi_personid_tab(b_tmp),
l_asg_fcst_jobid_tab(b_tmp),
l_tp_nl_resource(b_tmp),
l_tp_nl_resource_orgzid(b_tmp),
l_tp_pa_date(b_tmp),
l_fi_amount_type_tab(b_tmp),
l_tp_asgid(b_tmp),
l_fi_process_flag_tab(b_tmp),
l_fi_delete_flag_tab(b_tmp),
lx_tp_error_code(b_tmp),
l_fi_cst_rejct_reason_tab(b_tmp),
l_fi_rev_rejct_reason_tab(b_tmp),
l_fi_bd_rejct_reason_tab(b_tmp),
l_fi_others_rejct_reason_tab(b_tmp),
l_tp_denom_raw_cost(b_tmp),
l_tp_denom_bd_cost(b_tmp),
l_tp_raw_revenue(b_tmp) ,
lx_tp_ind_compiled_setid(b_tmp),
lx_tp_bill_rate(b_tmp) ,
lx_tp_base_amount(b_tmp) ,
lx_tp_bill_markup_percent(b_tmp) ,
lx_tp_sch_line_percent(b_tmp) ,
lx_tp_rule_percent(b_tmp) ,
lx_tp_job_id(b_tmp) ,
lx_cc_markup_basecode(b_tmp) );
l_fi_id_tab.delete;
l_fi_date_tab.delete;
l_fi_uom_tab.delete;
l_fi_qty_tab.delete;
l_fia_cost_txn_curr_code.delete;
l_fia_rev_txn_curr_code.delete;
l_fia_txn_raw_cost.delete;
l_fia_txn_bd_cost.delete;
l_fia_txn_revenue.delete;
l_tp_denom_currcode.delete; -- tp txn curr code
lx_denom_tp_currcode.delete; -- tp txn curr code
lx_denom_tp_amt.delete; -- TXN_TRANSFER_PRICE
l_prj_curr_code_tab.delete;
l_fia_proj_cost_rate_date.delete;
l_fia_proj_cost_rate_type.delete;
l_fia_proj_cost_ex_rate.delete;
l_fia_proj_raw_cost.delete;
l_fia_proj_bd_cost.delete;
l_fia_proj_rev_rate_date.delete;
l_fia_proj_rev_rate_type.delete;
l_fia_proj_rev_ex_rate.delete;
l_fia_proj_revenue.delete;
lx_proj_tp_rate_date.delete; -- PROJECT_TP_RATE_DATE
lx_proj_tp_rate_type.delete; -- PROJECT_TP_RATE_TYPE
lx_proj_tp_exchange_rate.delete; -- PROJECT_TP_EXCHANGE_RATE
lx_proj_tp_amt.delete; -- PROJECT_TRANSFER_PRICE
l_prjfunc_curr_code_tab.delete;
l_fia_projfunc_cost_rate_date.delete;
l_fia_projfunc_cost_rate_type.delete;
l_fia_projfunc_cost_ex_rate.delete;
l_fia_projfunc_raw_cost.delete;
l_fia_projfunc_bd_cost.delete;
l_fia_projfunc_rev_rate_date .delete;
l_fia_projfunc_rev_rate_type.delete;
l_fia_projfunc_rev_ex_rate .delete;
l_fia_projfunc_revenue.delete;
lx_projfunc_tp_rate_date.delete; -- PROJFUNC_TP_RATE_DATE
lx_projfunc_tp_rate_type.delete; -- PROJFUNC_TP_RATE_TYPE
lx_projfunc_tp_exchange_rate.delete; -- PROJFUNC_TP_EXCHANGE_RATE
lx_projfunc_tp_amt.delete; -- PROJFUNC_TRANSFER_PRICE
l_fia_expfunc_curr_code.delete;
l_fia_expfunc_cost_rate_date.delete;
l_fia_expfunc_cost_rate_type.delete;
l_fia_expfunc_cost_ex_rate.delete;
l_fia_expfunc_raw_cost.delete;
l_fia_expfunc_bd_cost.delete;
lx_expfunc_tp_rate_date.delete; -- EXPFUNC_TP_RATE_DATE
lx_expfunc_tp_rate_type.delete; -- EXPFUNC_TP_RATE_TYPE
lx_expfunc_tp_exchange_rate.delete; -- EXPFUNC_TP_EXCHANGE_RATE
lx_expfunc_tp_amt.delete; -- EXPFUNC_TRANSFER_PRICE
lx_cc_prvdr_orgid_tab.delete;
lx_cc_prvdr_orgzid_tab.delete;
lx_cc_recvr_orgid_tab.delete;
lx_cc_recvr_orgzid_tab.delete;
l_fi_exp_organizationid_tab.delete;
l_fi_exptype_tab.delete;
l_fi_exptypeclass_tab.delete;
l_tp_exp_category.delete;
l_tp_labor_nl_flag.delete;
l_tp_taskid.delete;
l_tp_scheduleid.delete;
l_tp_rev_distributed_flag.delete;
l_tp_compute_flag.delete;
l_tp_fixed_date.delete;
l_tp_asg_precedes_task_tab.delete; -- Added for bug 3260017
l_fi_projid_tab.delete;
l_fi_personid_tab.delete;
l_asg_fcst_jobid_tab.delete;
l_tp_nl_resource.delete;
l_tp_nl_resource_orgzid.delete;
l_tp_pa_date.delete;
l_fi_amount_type_tab.delete;
l_tp_asgid.delete;
l_fi_process_flag_tab.delete;
l_fi_delete_flag_tab.delete;
lx_tp_error_code.delete;
lx_tp_ind_compiled_setid.delete;
lx_tp_bill_rate.delete;
lx_tp_base_amount.delete;
lx_tp_bill_markup_percent.delete;
lx_tp_sch_line_percent.delete;
lx_tp_rule_percent.delete;
lx_tp_job_id.delete;
lx_cc_markup_basecode.delete;
l_tp_denom_raw_cost.delete;
l_tp_denom_bd_cost.delete;
l_tp_raw_revenue.delete;
l_fi_cst_rejct_reason_tab.delete;
l_fi_rev_rejct_reason_tab.delete;
l_fi_bd_rejct_reason_tab.delete;
l_fi_others_rejct_reason_tab.delete;
SELECT
FORECAST_ITEM_ID ,
ITEM_DATE ,
ITEM_UOM ,
ITEM_QUANTITY ,
COST_TXN_CURRENCY_CODE ,
REVENUE_TXN_CURRENCY_CODE ,
TXN_RAW_COST ,
TXN_BURDENED_COST ,
TXN_REVENUE ,
TP_TXN_CURRENCY_CODE_IN ,
TP_TXN_CURRENCY_CODE_OUT ,
TXN_TRANSFER_PRICE ,
PROJECT_CURRENCY_CODE ,
PROJECT_COST_RATE_DATE ,
PROJECT_COST_RATE_TYPE ,
PROJECT_COST_EXCHANGE_RATE ,
PROJECT_RAW_COST ,
PROJECT_BURDENED_COST ,
PROJECT_REVENUE_RATE_DATE ,
PROJECT_REVENUE_RATE_TYPE ,
PROJECT_REVENUE_EXCHANGE_RATE,
PROJECT_REVENUE ,
PROJECT_TP_RATE_DATE ,
PROJECT_TP_RATE_TYPE ,
PROJECT_TP_EXCHANGE_RATE ,
PROJECT_TRANSFER_PRICE ,
PROJFUNC_CURRENCY_CODE ,
PROJFUNC_COST_RATE_DATE ,
PROJFUNC_COST_RATE_TYPE ,
PROJFUNC_COST_EXCHANGE_RATE ,
PROJFUNC_RAW_COST ,
PROJFUNC_BURDENED_COST ,
PROJFUNC_REVENUE_RATE_DATE ,
PROJFUNC_REVENUE_RATE_TYPE ,
PROJFUNC_REVENUE_EXCHANGE_RATE,
PROJFUNC_REVENUE ,
PROJFUNC_TP_RATE_DATE ,
PROJFUNC_TP_RATE_TYPE ,
PROJFUNC_TP_EXCHANGE_RATE ,
PROJFUNC_TRANSFER_PRICE ,
EXPFUNC_CURRENCY_CODE ,
EXPFUNC_COST_RATE_DATE ,
EXPFUNC_COST_RATE_TYPE ,
EXPFUNC_COST_EXCHANGE_RATE ,
EXPFUNC_RAW_COST ,
EXPFUNC_BURDENED_COST ,
EXPFUNC_TP_RATE_DATE ,
EXPFUNC_TP_RATE_TYPE ,
EXPFUNC_TP_EXCHANGE_RATE ,
EXPFUNC_TRANSFER_PRICE ,
CC_PRVDR_ORG_ID ,
CC_PRVDR_ORGANIZITION_ID ,
CC_RECVR_ORG_ID ,
CC_RECVR_ORGANIZITION_ID ,
EXPENDITURE_ORGANIZATION_ID ,
EXPENDITURE_TYPE ,
EXPENDITURE_TYPE_CLASS ,
EXPENDITURE_CATEGORY ,
TP_LABOR_NL_FLAG ,
TP_TASK_ID ,
TP_SCHEDULE_ID ,
TP_REV_DISTRIBUTED_FLAG ,
TP_COMPUTE_FLAG ,
TP_FIXED_DATE ,
PROJECT_ID ,
PERSON_ID ,
FORECAST_JOB_ID ,
TP_NL_RESOURCE ,
TP_NL_RESOURCE_ORGZ_ID ,
TP_PA_DATE ,
TP_AMOUNT_TYPE ,
assignment_id ,
fi_process_flag ,
delete_flag ,
tp_error_code ,
COST_REJECTION_CODE ,
REV_REJECTION_CODE ,
BURDEN_REJECTION_CODE ,
OTHER_REJECTION_CODE ,
TP_DENOM_RAW_COST ,
TP_DENOM_BURDENED_COST ,
TP_RAW_REVENUE ,
tp_ind_compiled_setid ,
tp_bill_rate ,
tp_base_amount ,
tp_bill_markup_percent ,
tp_sch_line_percent ,
tp_rule_percent ,
tp_job_id ,
cc_markup_basecode BULK COLLECT INTO
l_fi_id_tab,
l_fi_date_tab,
l_fi_uom_tab,
l_fi_qty_tab,
l_fia_cost_txn_curr_code,
l_fia_rev_txn_curr_code,
l_fia_txn_raw_cost,
l_fia_txn_bd_cost,
l_fia_txn_revenue,
l_tp_denom_currcode, -- tp txn curr code
lx_denom_tp_currcode,
lx_denom_tp_amt,
l_prj_curr_code_tab,
l_fia_proj_cost_rate_date,
l_fia_proj_cost_rate_type,
l_fia_proj_cost_ex_rate,
l_fia_proj_raw_cost,
l_fia_proj_bd_cost,
l_fia_proj_rev_rate_date,
l_fia_proj_rev_rate_type,
l_fia_proj_rev_ex_rate,
l_fia_proj_revenue,
lx_proj_tp_rate_date,
lx_proj_tp_rate_type,
lx_proj_tp_exchange_rate,
lx_proj_tp_amt,
l_prjfunc_curr_code_tab,
l_fia_projfunc_cost_rate_date,
l_fia_projfunc_cost_rate_type,
l_fia_projfunc_cost_ex_rate,
l_fia_projfunc_raw_cost,
l_fia_projfunc_bd_cost,
l_fia_projfunc_rev_rate_date ,
l_fia_projfunc_rev_rate_type,
l_fia_projfunc_rev_ex_rate ,
l_fia_projfunc_revenue,
lx_projfunc_tp_rate_date,
lx_projfunc_tp_rate_type,
lx_projfunc_tp_exchange_rate,
lx_projfunc_tp_amt,
l_fia_expfunc_curr_code,
l_fia_expfunc_cost_rate_date,
l_fia_expfunc_cost_rate_type,
l_fia_expfunc_cost_ex_rate,
l_fia_expfunc_raw_cost,
l_fia_expfunc_bd_cost,
lx_expfunc_tp_rate_date,
lx_expfunc_tp_rate_type,
lx_expfunc_tp_exchange_rate,
lx_expfunc_tp_amt,
lx_cc_prvdr_orgid_tab,
lx_cc_prvdr_orgzid_tab,
lx_cc_recvr_orgid_tab,
lx_cc_recvr_orgzid_tab,
l_fi_exp_organizationid_tab,
l_fi_exptype_tab,
l_fi_exptypeclass_tab,
l_tp_exp_category,
l_tp_labor_nl_flag,
l_tp_taskid,
l_tp_scheduleid,
l_tp_rev_distributed_flag,
l_tp_compute_flag,
l_tp_fixed_date,
l_fi_projid_tab,
l_fi_personid_tab,
l_asg_fcst_jobid_tab,
l_tp_nl_resource,
l_tp_nl_resource_orgzid,
l_tp_pa_date,
l_fi_amount_type_tab,
l_tp_asgid,
l_fi_process_flag_tab,
l_fi_delete_flag_tab,
lx_tp_error_code,
l_fi_cst_rejct_reason_tab,
l_fi_rev_rejct_reason_tab,
l_fi_bd_rejct_reason_tab,
l_fi_others_rejct_reason_tab,
l_tp_denom_raw_cost,
l_tp_denom_bd_cost,
l_tp_raw_revenue ,
lx_tp_ind_compiled_setid,
lx_tp_bill_rate,
lx_tp_base_amount,
lx_tp_bill_markup_percent,
lx_tp_sch_line_percent,
lx_tp_rule_percent,
lx_tp_job_id,
lx_cc_markup_basecode FROM Pa_Fi_Amount_Dtls_Tmp WHERE
Tp_Error_Code IS NULL;
l_tp_asg_precedes_task_tab.delete;
UPDATE Pa_Fi_Amount_Dtls_Tmp SET
TP_TXN_CURRENCY_CODE_out = lx_denom_tp_currcode(l_trf_upd),
TXN_TRANSFER_PRICE = lx_denom_tp_amt(l_trf_upd),
PROJECT_TP_RATE_DATE = lx_proj_tp_rate_date(l_trf_upd),
PROJECT_TP_RATE_TYPE = lx_proj_tp_rate_type(l_trf_upd),
PROJECT_TP_EXCHANGE_RATE = lx_proj_tp_exchange_rate(l_trf_upd),
PROJECT_TRANSFER_PRICE = lx_proj_tp_amt(l_trf_upd),
PROJFUNC_TP_RATE_DATE = lx_projfunc_tp_rate_date(l_trf_upd),
PROJFUNC_TP_RATE_TYPE = lx_projfunc_tp_rate_type(l_trf_upd),
PROJFUNC_TP_EXCHANGE_RATE = lx_projfunc_tp_exchange_rate(l_trf_upd),
PROJFUNC_TRANSFER_PRICE = lx_projfunc_tp_amt(l_trf_upd),
EXPFUNC_TP_RATE_DATE = lx_expfunc_tp_rate_date(l_trf_upd),
EXPFUNC_TP_RATE_TYPE = lx_expfunc_tp_rate_type(l_trf_upd),
EXPFUNC_TP_EXCHANGE_RATE = lx_expfunc_tp_exchange_rate(l_trf_upd),
EXPFUNC_TRANSFER_PRICE = lx_expfunc_tp_amt(l_trf_upd),
TP_ERROR_CODE = lx_tp_error_code(l_trf_upd)
WHERE Forecast_Item_Id = l_fi_id_tab(l_trf_upd);
l_fi_id_tab.delete;
l_fi_date_tab.delete;
l_fi_uom_tab.delete;
l_fi_qty_tab.delete;
l_fia_cost_txn_curr_code.delete;
l_fia_rev_txn_curr_code.delete;
l_fia_txn_raw_cost.delete;
l_fia_txn_bd_cost.delete;
l_fia_txn_revenue.delete;
l_tp_denom_currcode.delete; -- tp txn curr code
lx_denom_tp_currcode.delete; -- tp txn curr code
lx_denom_tp_amt.delete; -- TXN_TRANSFER_PRICE
l_prj_curr_code_tab.delete;
l_fia_proj_cost_rate_date.delete;
l_fia_proj_cost_rate_type.delete;
l_fia_proj_cost_ex_rate.delete;
l_fia_proj_raw_cost.delete;
l_fia_proj_bd_cost.delete;
l_fia_proj_rev_rate_date.delete;
l_fia_proj_rev_rate_type.delete;
l_fia_proj_rev_ex_rate.delete;
l_fia_proj_revenue.delete;
lx_proj_tp_rate_date.delete; -- PROJECT_TP_RATE_DATE
lx_proj_tp_rate_type.delete; -- PROJECT_TP_RATE_TYPE
lx_proj_tp_exchange_rate.delete; -- PROJECT_TP_EXCHANGE_RATE
lx_proj_tp_amt.delete; -- PROJECT_TRANSFER_PRICE
l_prjfunc_curr_code_tab.delete;
l_fia_projfunc_cost_rate_date.delete;
l_fia_projfunc_cost_rate_type.delete;
l_fia_projfunc_cost_ex_rate.delete;
l_fia_projfunc_raw_cost.delete;
l_fia_projfunc_bd_cost.delete;
l_fia_projfunc_rev_rate_date .delete;
l_fia_projfunc_rev_rate_type.delete;
l_fia_projfunc_rev_ex_rate .delete;
l_fia_projfunc_revenue.delete;
lx_projfunc_tp_rate_date.delete; -- PROJFUNC_TP_RATE_DATE
lx_projfunc_tp_rate_type.delete; -- PROJFUNC_TP_RATE_TYPE
lx_projfunc_tp_exchange_rate.delete; -- PROJFUNC_TP_EXCHANGE_RATE
lx_projfunc_tp_amt.delete; -- PROJFUNC_TRANSFER_PRICE
l_fia_expfunc_curr_code.delete;
l_fia_expfunc_cost_rate_date.delete;
l_fia_expfunc_cost_rate_type.delete;
l_fia_expfunc_cost_ex_rate.delete;
l_fia_expfunc_raw_cost.delete;
l_fia_expfunc_bd_cost.delete;
lx_expfunc_tp_rate_date.delete; -- EXPFUNC_TP_RATE_DATE
lx_expfunc_tp_rate_type.delete; -- EXPFUNC_TP_RATE_TYPE
lx_expfunc_tp_exchange_rate.delete; -- EXPFUNC_TP_EXCHANGE_RATE
lx_expfunc_tp_amt.delete; -- EXPFUNC_TRANSFER_PRICE
lx_cc_prvdr_orgid_tab.delete;
lx_cc_prvdr_orgzid_tab.delete;
lx_cc_recvr_orgid_tab.delete;
lx_cc_recvr_orgzid_tab.delete;
l_fi_exp_organizationid_tab.delete;
l_fi_exptype_tab.delete;
l_fi_exptypeclass_tab.delete;
l_tp_exp_category.delete;
l_tp_labor_nl_flag.delete;
l_tp_taskid.delete;
l_tp_scheduleid.delete;
l_tp_rev_distributed_flag.delete;
l_tp_compute_flag.delete;
l_tp_fixed_date.delete;
l_fi_projid_tab.delete;
l_fi_personid_tab.delete;
l_asg_fcst_jobid_tab.delete;
l_tp_nl_resource.delete;
l_tp_nl_resource_orgzid.delete;
l_tp_pa_date.delete;
l_fi_amount_type_tab.delete;
l_tp_asgid.delete;
l_fi_process_flag_tab.delete;
l_fi_delete_flag_tab.delete;
lx_tp_error_code.delete;
lx_tp_ind_compiled_setid.delete;
lx_tp_bill_rate.delete;
lx_tp_base_amount.delete;
lx_tp_bill_markup_percent.delete;
lx_tp_sch_line_percent.delete;
lx_tp_rule_percent.delete;
lx_tp_job_id.delete;
lx_cc_markup_basecode.delete;
l_tp_denom_raw_cost.delete;
l_tp_denom_bd_cost.delete;
l_tp_raw_revenue.delete;
l_fi_cst_rejct_reason_tab.delete;
l_fi_rev_rejct_reason_tab.delete;
l_fi_bd_rejct_reason_tab.delete;
l_fi_others_rejct_reason_tab.delete;
SELECT
FORECAST_ITEM_ID ,
ITEM_DATE ,
ITEM_UOM ,
ITEM_QUANTITY ,
COST_TXN_CURRENCY_CODE ,
REVENUE_TXN_CURRENCY_CODE ,
TXN_RAW_COST ,
TXN_BURDENED_COST ,
TXN_REVENUE ,
TP_TXN_CURRENCY_CODE_IN ,
TP_TXN_CURRENCY_CODE_OUT ,
TXN_TRANSFER_PRICE ,
PROJECT_CURRENCY_CODE ,
PROJECT_COST_RATE_DATE ,
PROJECT_COST_RATE_TYPE ,
PROJECT_COST_EXCHANGE_RATE ,
PROJECT_RAW_COST ,
PROJECT_BURDENED_COST ,
PROJECT_REVENUE_RATE_DATE ,
PROJECT_REVENUE_RATE_TYPE ,
PROJECT_REVENUE_EXCHANGE_RATE,
PROJECT_REVENUE ,
PROJECT_TP_RATE_DATE ,
PROJECT_TP_RATE_TYPE ,
PROJECT_TP_EXCHANGE_RATE ,
PROJECT_TRANSFER_PRICE ,
PROJFUNC_CURRENCY_CODE ,
PROJFUNC_COST_RATE_DATE ,
PROJFUNC_COST_RATE_TYPE ,
PROJFUNC_COST_EXCHANGE_RATE ,
PROJFUNC_RAW_COST ,
PROJFUNC_BURDENED_COST ,
PROJFUNC_REVENUE_RATE_DATE ,
PROJFUNC_REVENUE_RATE_TYPE ,
PROJFUNC_REVENUE_EXCHANGE_RATE,
PROJFUNC_REVENUE ,
PROJFUNC_TP_RATE_DATE ,
PROJFUNC_TP_RATE_TYPE ,
PROJFUNC_TP_EXCHANGE_RATE ,
PROJFUNC_TRANSFER_PRICE ,
EXPFUNC_CURRENCY_CODE ,
EXPFUNC_COST_RATE_DATE ,
EXPFUNC_COST_RATE_TYPE ,
EXPFUNC_COST_EXCHANGE_RATE ,
EXPFUNC_RAW_COST ,
EXPFUNC_BURDENED_COST ,
EXPFUNC_TP_RATE_DATE ,
EXPFUNC_TP_RATE_TYPE ,
EXPFUNC_TP_EXCHANGE_RATE ,
EXPFUNC_TRANSFER_PRICE ,
CC_PRVDR_ORG_ID ,
CC_PRVDR_ORGANIZITION_ID ,
CC_RECVR_ORG_ID ,
CC_RECVR_ORGANIZITION_ID ,
EXPENDITURE_ORGANIZATION_ID ,
EXPENDITURE_TYPE ,
EXPENDITURE_TYPE_CLASS ,
EXPENDITURE_CATEGORY ,
TP_LABOR_NL_FLAG ,
TP_TASK_ID ,
TP_SCHEDULE_ID ,
TP_REV_DISTRIBUTED_FLAG ,
TP_COMPUTE_FLAG ,
TP_FIXED_DATE ,
PROJECT_ID ,
PERSON_ID ,
FORECAST_JOB_ID ,
TP_NL_RESOURCE ,
TP_NL_RESOURCE_ORGZ_ID ,
TP_PA_DATE ,
TP_AMOUNT_TYPE ,
assignment_id ,
fi_process_flag ,
delete_flag ,
tp_error_code ,
COST_REJECTION_CODE ,
REV_REJECTION_CODE ,
BURDEN_REJECTION_CODE ,
OTHER_REJECTION_CODE ,
TP_DENOM_RAW_COST ,
TP_DENOM_BURDENED_COST ,
TP_RAW_REVENUE ,
tp_ind_compiled_setid ,
tp_bill_rate ,
tp_base_amount ,
tp_bill_markup_percent ,
tp_sch_line_percent ,
tp_rule_percent ,
tp_job_id ,
cc_markup_basecode BULK COLLECT INTO
l_fi_id_tab,
l_fi_date_tab,
l_fi_uom_tab,
l_fi_qty_tab,
l_fia_cost_txn_curr_code,
l_fia_rev_txn_curr_code,
l_fia_txn_raw_cost,
l_fia_txn_bd_cost,
l_fia_txn_revenue,
l_tp_denom_currcode, -- tp txn curr code
lx_denom_tp_currcode,
lx_denom_tp_amt,
l_prj_curr_code_tab,
l_fia_proj_cost_rate_date,
l_fia_proj_cost_rate_type,
l_fia_proj_cost_ex_rate,
l_fia_proj_raw_cost,
l_fia_proj_bd_cost,
l_fia_proj_rev_rate_date,
l_fia_proj_rev_rate_type,
l_fia_proj_rev_ex_rate,
l_fia_proj_revenue,
lx_proj_tp_rate_date,
lx_proj_tp_rate_type,
lx_proj_tp_exchange_rate,
lx_proj_tp_amt,
l_prjfunc_curr_code_tab,
l_fia_projfunc_cost_rate_date,
l_fia_projfunc_cost_rate_type,
l_fia_projfunc_cost_ex_rate,
l_fia_projfunc_raw_cost,
l_fia_projfunc_bd_cost,
l_fia_projfunc_rev_rate_date ,
l_fia_projfunc_rev_rate_type,
l_fia_projfunc_rev_ex_rate ,
l_fia_projfunc_revenue,
lx_projfunc_tp_rate_date,
lx_projfunc_tp_rate_type,
lx_projfunc_tp_exchange_rate,
lx_projfunc_tp_amt,
l_fia_expfunc_curr_code,
l_fia_expfunc_cost_rate_date,
l_fia_expfunc_cost_rate_type,
l_fia_expfunc_cost_ex_rate,
l_fia_expfunc_raw_cost,
l_fia_expfunc_bd_cost,
lx_expfunc_tp_rate_date,
lx_expfunc_tp_rate_type,
lx_expfunc_tp_exchange_rate,
lx_expfunc_tp_amt,
lx_cc_prvdr_orgid_tab,
lx_cc_prvdr_orgzid_tab,
lx_cc_recvr_orgid_tab,
lx_cc_recvr_orgzid_tab,
l_fi_exp_organizationid_tab,
l_fi_exptype_tab,
l_fi_exptypeclass_tab,
l_tp_exp_category,
l_tp_labor_nl_flag,
l_tp_taskid,
l_tp_scheduleid,
l_tp_rev_distributed_flag,
l_tp_compute_flag,
l_tp_fixed_date,
l_fi_projid_tab,
l_fi_personid_tab,
l_asg_fcst_jobid_tab,
l_tp_nl_resource,
l_tp_nl_resource_orgzid,
l_tp_pa_date,
l_fi_amount_type_tab,
l_tp_asgid,
l_fi_process_flag_tab,
l_fi_delete_flag_tab,
lx_tp_error_code,
l_fi_cst_rejct_reason_tab,
l_fi_rev_rejct_reason_tab,
l_fi_bd_rejct_reason_tab,
l_fi_others_rejct_reason_tab,
l_tp_denom_raw_cost,
l_tp_denom_bd_cost,
l_tp_raw_revenue ,
lx_tp_ind_compiled_setid,
lx_tp_bill_rate,
lx_tp_base_amount,
lx_tp_bill_markup_percent,
lx_tp_sch_line_percent,
lx_tp_rule_percent,
lx_tp_job_id,
lx_cc_markup_basecode FROM Pa_Fi_Amount_Dtls_Tmp;
l_fi_delete_flag_tab(d) = 'Y' OR
l_fi_qty_tab(d) <= 0 THEN
BEGIN
SELECT
LINE_NUM ,
ITEM_QUANTITY ,
COST_TXN_CURRENCY_CODE ,
REVENUE_TXN_CURRENCY_CODE ,
TXN_RAW_COST ,
TXN_BURDENED_COST ,
TXN_REVENUE ,
TXN_TRANSFER_PRICE ,
TP_TXN_CURRENCY_CODE ,
PROJECT_CURRENCY_CODE ,
PROJECT_RAW_COST ,
PROJECT_BURDENED_COST ,
PROJECT_COST_RATE_DATE ,
PROJECT_COST_RATE_TYPE ,
PROJECT_COST_EXCHANGE_RATE ,
PROJECT_REVENUE_RATE_DATE ,
PROJECT_REVENUE_RATE_TYPE ,
PROJECT_REVENUE_EXCHANGE_RATE,
PROJECT_REVENUE ,
PROJECT_TP_RATE_DATE ,
PROJECT_TP_RATE_TYPE ,
PROJECT_TP_EXCHANGE_RATE ,
PROJECT_TRANSFER_PRICE ,
PROJFUNC_CURRENCY_CODE ,
PROJFUNC_COST_RATE_DATE ,
PROJFUNC_COST_RATE_TYPE ,
PROJFUNC_COST_EXCHANGE_RATE ,
PROJFUNC_RAW_COST ,
PROJFUNC_BURDENED_COST ,
PROJFUNC_REVENUE_RATE_DATE ,
PROJFUNC_REVENUE_RATE_TYPE ,
PROJFUNC_REVENUE_EXCHANGE_RATE,
PROJFUNC_REVENUE ,
PROJFUNC_TP_RATE_DATE ,
PROJFUNC_TP_RATE_TYPE ,
PROJFUNC_TP_EXCHANGE_RATE ,
PROJFUNC_TRANSFER_PRICE ,
EXPFUNC_CURRENCY_CODE ,
EXPFUNC_COST_RATE_DATE ,
EXPFUNC_COST_RATE_TYPE ,
EXPFUNC_COST_EXCHANGE_RATE ,
EXPFUNC_RAW_COST ,
EXPFUNC_BURDENED_COST ,
EXPFUNC_TP_RATE_DATE ,
EXPFUNC_TP_RATE_TYPE ,
EXPFUNC_TP_EXCHANGE_RATE ,
EXPFUNC_TRANSFER_PRICE
INTO
l_t_LINE_NUM ,
l_t_ITEM_QUANTITY ,
l_t_COST_TXN_CURR_CODE ,
l_t_REV_TXN_CURR_CODE ,
l_t_TXN_RAW_COST ,
l_t_TXN_BD_COST ,
l_t_TXN_REVENUE ,
l_t_TXN_TRANSFER_PRICE ,
l_t_TP_TXN_CURR_CODE ,
l_t_PROJ_CURR_CODE ,
l_t_PROJ_RAW_COST ,
l_t_PROJ_BD_COST ,
l_t_PROJ_COST_RATE_DATE ,
l_t_PROJ_COST_RATE_TYPE ,
l_t_PROJ_COST_EX_RATE ,
l_t_PROJ_REV_RATE_DATE ,
l_t_PROJ_REV_RATE_TYPE ,
l_t_PROJ_REV_EX_RATE ,
l_t_PROJ_REVENUE ,
l_t_PROJ_TP_RATE_DATE ,
l_t_PROJ_TP_RATE_TYPE ,
l_t_PROJ_TP_EX_RATE ,
l_t_PROJ_TRANSFER_PRICE ,
l_t_PFUNC_CURR_CODE ,
l_t_PFUNC_COST_RATE_DATE ,
l_t_PFUNC_COST_RATE_TYPE ,
l_t_PFUNC_COST_EX_RATE ,
l_t_PFUNC_RAW_COST ,
l_t_PFUNC_BD_COST ,
l_t_PFUNC_REV_RATE_DATE ,
l_t_PFUNC_REV_RATE_TYPE ,
l_t_PFUNC_REV_EX_RATE ,
l_t_PFUNC_REVENUE ,
l_t_PFUNC_TP_RATE_DATE ,
l_t_PFUNC_TP_RATE_TYPE ,
l_t_PFUNC_TP_EX_RATE ,
l_t_PFUNC_TRANSFER_PRICE ,
l_t_EFUNC_CURR_CODE ,
l_t_EFUNC_COST_RATE_DATE ,
l_t_EFUNC_COST_RATE_TYPE ,
l_t_EFUNC_COST_EX_RATE ,
l_t_EFUNC_RAW_COST ,
l_t_EFUNC_BD_COST ,
l_t_EFUNC_TP_RATE_DATE ,
l_t_EFUNC_TP_RATE_TYPE ,
l_t_EFUNC_TP_EX_RATE ,
l_t_EFUNC_TRANSFER_PRICE
FROM Pa_Fi_Amount_Details WHERE
Forecast_Item_Id = l_fi_id_tab(d) AND
Line_Num = ( SELECT MAX(LINE_NUM) FROM
Pa_Fi_Amount_Details WHERE
Forecast_Item_Id = l_fi_id_tab(d) );
dbms_output.put_line('bef inserting FI amount dtls :'); */
PA_DEBUG.g_err_stage := 'Bef inserting FI Amt Dtls';
INSERT INTO Pa_Fi_Amount_Details(
FORECAST_ITEM_ID,
LINE_NUM,
ITEM_DATE,
ITEM_UOM,
ITEM_QUANTITY,
NET_ZERO_FLAG,
REVERSED_FLAG,
LINE_NUM_REVERSED,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
LAST_UPDATE_LOGIN,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE,
COST_TXN_CURRENCY_CODE,
REVENUE_TXN_CURRENCY_CODE,
TXN_RAW_COST,
TXN_BURDENED_COST,
TXN_REVENUE,
TXN_TRANSFER_PRICE,
TP_TXN_CURRENCY_CODE,
PROJECT_CURRENCY_CODE,
PROJECT_RAW_COST,
PROJECT_BURDENED_COST,
PROJECT_COST_RATE_DATE,
PROJECT_COST_RATE_TYPE,
PROJECT_COST_EXCHANGE_RATE,
PROJECT_REVENUE_RATE_DATE,
PROJECT_REVENUE_RATE_TYPE,
PROJECT_REVENUE_EXCHANGE_RATE,
PROJECT_REVENUE,
PROJECT_TP_RATE_DATE,
PROJECT_TP_RATE_TYPE,
PROJECT_TP_EXCHANGE_RATE,
PROJECT_TRANSFER_PRICE,
PROJFUNC_CURRENCY_CODE,
PROJFUNC_COST_RATE_DATE,
PROJFUNC_COST_RATE_TYPE,
PROJFUNC_COST_EXCHANGE_RATE,
PROJFUNC_RAW_COST,
PROJFUNC_BURDENED_COST,
PROJFUNC_REVENUE_RATE_DATE,
PROJFUNC_REVENUE_RATE_TYPE,
PROJFUNC_REVENUE_EXCHANGE_RATE,
PROJFUNC_REVENUE,
PROJFUNC_TP_RATE_DATE,
PROJFUNC_TP_RATE_TYPE,
PROJFUNC_TP_EXCHANGE_RATE,
PROJFUNC_TRANSFER_PRICE,
EXPFUNC_CURRENCY_CODE,
EXPFUNC_COST_RATE_DATE,
EXPFUNC_COST_RATE_TYPE,
EXPFUNC_COST_EXCHANGE_RATE,
EXPFUNC_RAW_COST,
EXPFUNC_BURDENED_COST,
EXPFUNC_TP_RATE_DATE,
EXPFUNC_TP_RATE_TYPE,
EXPFUNC_TP_EXCHANGE_RATE,
EXPFUNC_TRANSFER_PRICE )
VALUES(
l_fid_fcst_itemid(b),
l_fid_line_num(b),
l_fid_item_date(b),
l_fid_item_uom(b),
l_fid_item_qty(b),
l_fid_net_zero_flag(b),
l_fid_reversed_flag(b),
l_fid_line_num_reversed(b),
l_creation_date,
l_created_by,
l_last_update_date,
l_last_updated_by,
l_last_update_login,
l_request_id,
l_program_application_id,
l_program_id,
l_last_update_date,
l_fid_cost_txn_curr_code(b),
l_fid_rev_txn_curr_code(b),
l_fid_txn_raw_cost(b),
l_fid_txn_bd_cost(b),
l_fid_txn_revenue(b),
l_fid_denom_tp_amt(b),
l_fid_denom_tp_currcode(b),
l_fid_proj_curr_code(b),
l_fid_proj_raw_cost(b),
l_fid_proj_bd_cost(b),
l_fid_proj_cost_rate_date(b),
l_fid_proj_cost_rate_type(b),
l_fid_proj_cost_ex_rate(b),
l_fid_proj_rev_rate_date(b),
l_fid_proj_rev_rate_type(b),
l_fid_proj_rev_ex_rate(b),
l_fid_proj_revenue(b),
l_fid_proj_tp_rate_date(b),
l_fid_proj_tp_rate_type(b),
l_fid_proj_tp_ex_rate(b),
l_fid_proj_tp_amt(b),
l_fid_projfunc_curr_code(b),
l_fid_projfunc_cost_rate_date(b),
l_fid_projfunc_cost_rate_type(b),
l_fid_projfunc_cost_ex_rate(b),
l_fid_projfunc_raw_cost(b),
l_fid_projfunc_bd_cost(b),
l_fid_projfunc_rev_rate_date(b),
l_fid_projfunc_rev_rate_type(b),
l_fid_projfunc_rev_ex_rate(b),
l_fid_projfunc_revenue(b),
l_fid_projfunc_tp_rate_date(b),
l_fid_projfunc_tp_rate_type(b),
l_fid_projfunc_tp_ex_rate(b),
l_fid_projfunc_tp_amt(b),
l_fid_expfunc_curr_code(b),
l_fid_expfunc_cost_rate_date(b),
l_fid_expfunc_cost_rate_type(b),
l_fid_expfunc_cost_ex_rate(b),
l_fid_expfunc_raw_cost(b),
l_fid_expfunc_bd_cost(b),
l_fid_expfunc_tp_rate_date(b),
l_fid_expfunc_tp_rate_type(b),
l_fid_expfunc_tp_ex_rate(b),
l_fid_expfunc_tp_amt(b) );
UPDATE Pa_FI_Amount_Details SET
Reversed_flag = 'Y',
Net_Zero_Flag = 'Y',
LAST_UPDATE_DATE = l_last_update_date,
LAST_UPDATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login,
REQUEST_ID = l_request_id,
PROGRAM_APPLICATION_ID = l_program_application_id,
PROGRAM_ID = l_program_id,
PROGRAM_UPDATE_DATE = l_last_update_date
WHERE
Forecast_Item_Id = l_fid_upd_fcst_itemid(l_upd_idx) AND
Line_Num = l_fid_upd_line_num(l_upd_idx);
IF l_fi_delete_flag_tab(l_tupd) = 'Y' OR l_fi_qty_tab(l_tupd) <= 0 THEN
l_fi_process_flag_tab(l_tupd) := 'X';
l_fi_delete_flag_tab(l_tupd) = 'Y' OR
l_fi_qty_tab(l_tupd) <= 0 THEN
l_fia_cost_txn_curr_code(l_tupd) := NULL;
UPDATE Pa_Forecast_Items SET
FORECAST_AMT_CALC_FLAG = l_fi_process_flag_tab(l_fi_upd_index),
TP_AMOUNT_TYPE = l_fi_amount_type_tab(l_fi_upd_index),
COST_TXN_CURRENCY_CODE = l_fia_cost_txn_curr_code(l_fi_upd_index),
REVENUE_TXN_CURRENCY_CODE = l_fia_rev_txn_curr_code(l_fi_upd_index),
TXN_RAW_COST = l_fia_txn_raw_cost(l_fi_upd_index),
TXN_BURDENED_COST = l_fia_txn_bd_cost(l_fi_upd_index),
TXN_REVENUE = l_fia_txn_revenue(l_fi_upd_index),
TP_TXN_CURRENCY_CODE = lx_denom_tp_currcode(l_fi_upd_index),
TXN_TRANSFER_PRICE = lx_denom_tp_amt(l_fi_upd_index),
EXPFUNC_CURRENCY_CODE = l_fia_expfunc_curr_code(l_fi_upd_index),
EXPFUNC_RAW_COST = l_fia_expfunc_raw_cost(l_fi_upd_index),
EXPFUNC_BURDENED_COST = l_fia_expfunc_bd_cost(l_fi_upd_index),
EXPFUNC_TRANSFER_PRICE = lx_expfunc_tp_amt(l_fi_upd_index),
PROJFUNC_CURRENCY_CODE = l_prjfunc_curr_code_tab(l_fi_upd_index),
PROJFUNC_RAW_COST = l_fia_projfunc_raw_cost(l_fi_upd_index),
PROJFUNC_BURDENED_COST = l_fia_projfunc_bd_cost(l_fi_upd_index),
PROJFUNC_REVENUE = l_fia_projfunc_revenue(l_fi_upd_index),
PROJFUNC_TRANSFER_PRICE = lx_projfunc_tp_amt(l_fi_upd_index),
PROJECT_CURRENCY_CODE = l_prj_curr_code_tab(l_fi_upd_index),
PROJECT_RAW_COST = l_fia_proj_raw_cost(l_fi_upd_index),
PROJECT_BURDENED_COST = l_fia_proj_bd_cost(l_fi_upd_index),
PROJECT_REVENUE = l_fia_proj_revenue(l_fi_upd_index),
PROJECT_TRANSFER_PRICE = lx_proj_tp_amt(l_fi_upd_index),
COST_REJECTION_CODE = l_fi_cst_rejct_reason_tab(l_fi_upd_index),
REV_REJECTION_CODE = l_fi_rev_rejct_reason_tab(l_fi_upd_index),
TP_REJECTION_CODE = lx_tp_error_code(l_fi_upd_index),
BURDEN_REJECTION_CODE = l_fi_bd_rejct_reason_tab(l_fi_upd_index),
OTHER_REJECTION_CODE = l_fi_others_rejct_reason_tab(l_fi_upd_index),
LAST_UPDATE_DATE = l_last_update_date,
LAST_UPDATED_BY = l_last_updated_by,
LAST_UPDATE_LOGIN = l_last_update_login,
REQUEST_ID = l_request_id,
PROGRAM_APPLICATION_ID = l_program_application_id,
PROGRAM_ID = l_program_id,
PROGRAM_UPDATE_DATE = l_last_update_date
WHERE Forecast_Item_Id = l_fi_id_tab(l_fi_upd_index);
/* dbms_output.put_line('records updated in FI :'||sql%rowcount ); */
Select sum(PROJFUNC_TRANSFER_PRICE), Sum(ITEM_QUANTITY)
INTO l_sum_transfer_price, l_sum_item_quantity
From PA_FORECAST_ITEMS
Where assignment_id = l_assignment_ID and delete_flag = 'N'
and error_flag = 'N' And forecast_amt_Calc_flag ='Y';
PA_ASSIGNMENTS_PVT.Update_Transfer_Price
(
p_assignment_id => l_assignment_id
,p_debug_mode => p_debug_mode
,p_transfer_price_rate => NULL
,p_transfer_pr_rate_curr=> NULL
,x_return_status => l_return_status
);
PA_ASSIGNMENTS_PVT.Update_Transfer_Price
(
p_assignment_id => l_assignment_id
,p_debug_mode => p_debug_mode
,p_transfer_price_rate => l_average_transfer_price_rate
,p_transfer_pr_rate_curr=> l_prjfunc_curr_code
,x_return_status => l_return_status
);
ELSIF p_run_mode = 'P' AND p_select_criteria = '01' AND
p_project_id IS NOT NULL AND p_assignment_id IS NULL THEN
IF P_PA_DEBUG_MODE = 'Y' THEN
PA_DEBUG.g_err_stage := 'Closing Fcst_Item_Prj and returning';
ELSIF p_run_mode = 'P' AND p_select_criteria = '01' AND
p_project_id IS NOT NULL AND p_assignment_id IS NOT NULL THEN
IF P_PA_DEBUG_MODE = 'Y' THEN
PA_DEBUG.g_err_stage := 'Closing Fcst_Item_Prj_Asg and returning';
ELSIF p_run_mode = 'P' AND p_select_criteria in ( '02','03') THEN
IF P_PA_DEBUG_MODE = 'Y' THEN
PA_DEBUG.g_err_stage := 'Closing Fcst_Item_Organization and returning';