[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_last_updated_by NUMBER(15) := PA_REP_UTIL_GLOB.G_who_columns.G_last_updated_by;
l_last_update_date DATE := PA_REP_UTIL_GLOB.G_who_columns.G_last_update_date;
l_program_update_date DATE := PA_REP_UTIL_GLOB.G_who_columns.G_last_update_date;
PROCEDURE insert_fct_into_tmp_table
IS
BEGIN
PA_DEBUG.Set_Curr_Function( p_function => 'insert_fct_into_tmp_table');
PA_DEBUG.g_err_stage := '205: Before inserting recs into Temp Table';
INSERT
INTO pa_rep_util_summ0_tmp
( row_id
, parent_row_id
, expenditure_organization_id
, person_id
, assignment_id
, work_type_id
, org_util_category_id
, res_util_category_id
, expenditure_type
, expenditure_type_class
, pa_period_name
, pa_period_num
, pa_period_year
, pa_quarter_number
, gl_period_name
, gl_period_num
, gl_period_year
, gl_quarter_number
, global_exp_period_end_date
, global_exp_year
, global_exp_month_number
, total_hours
, total_prov_hours
, total_wghted_hours_people
, total_wghted_hours_org
, prov_wghted_hours_people
, prov_wghted_hours_org
, reduce_capacity
, delete_flag )
SELECT
row_id
, parent_row_id
, expenditure_organization_id
, person_id
, assignment_id
, work_type_id
, org_util_category_id
, res_util_category_id
, expenditure_type
, expenditure_type_class
, pa_period_name
, pa_period_num
, pa_period_year
, pa_quarter_number
, gl_period_name
, gl_period_num
, gl_period_year
, gl_quarter_number
, global_exp_period_end_date
, global_exp_year
, global_exp_month_number
, total_hours
, total_prov_hours
, total_wghted_hours_people
, total_wghted_hours_org
, prov_wghted_hours_people
, prov_wghted_hours_org
, reduce_capacity
, delete_flag
FROM pa_rep_util_summ00_tmp
WHERE rownum <= l_fetch_size;
END insert_fct_into_tmp_table;
l_records_inserted PLS_INTEGER;
l_records_updated PLS_INTEGER;
l_records_inserted1 PLS_INTEGER;
Insert_Fcst_Into_Tmp_PAGLGE;
Insert_Fcst_Into_Tmp_PAGL;
Insert_Fcst_Into_Tmp_PAGE;
Insert_Fcst_Into_Tmp_PA;
Insert_Fcst_Into_Tmp_GLGE;
Insert_Fcst_Into_Tmp_GL;
Insert_Fcst_Into_Tmp_GE;
PA_DEBUG.G_Err_Stage := '700 : After Calling the INSERT_PROC_[PA][GL][GE]';
insert_fct_into_tmp_table;
l_records_inserted := SQL%ROWCOUNT;
PA_DEBUG.G_Err_Stage := '750 : Records Inserted in Temp tab : ' || l_records_inserted;
IF (l_records_inserted = 0 AND l_capacity_summarized= 1) THEN
IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
PA_DEBUG.G_Err_Stage := '757 : EXITING since l_records_inserted = 0 AND l_capacity_summarized= 1';
UPDATE pa_forecast_item_details A
SET util_summarized_code = 'S'
WHERE util_summarized_code = 'N'
AND exists ( SELECT row_id
FROM pa_rep_util_summ0_tmp B
WHERE A.ROWID = B.ROW_ID -- bug 3132246
)
RETURNING ROWID BULK COLLECT INTO l_fid_rowid_tab;
l_records_updated := SQL%ROWCOUNT;
PA_DEBUG.G_Err_Stage := '800 : Records Updated in PA_FORECAST_ITEMS_DETAILS : '|| l_records_updated;
IF (l_records_updated < l_records_inserted AND l_fid_rowid_tab.COUNT > 0) THEN /* added second condition 2084888 */
l_process_method := 'F';
UPDATE pa_rep_util_summ0_tmp tmp
SET tmp.delete_flag = 'N'
WHERE tmp.row_id = l_fid_rowid_tab(i);
l_fid_rowid_tab.DELETE;
PA_DEBUG.G_Err_Stage := '1000 : Records Deleted from PA_FORECAST_ITEM_DETAILS: ' || to_char(SQL%ROWCOUNT);
UPDATE pa_forecast_item_details
SET util_summarized_code = NULL
,last_update_date = l_last_update_date
,last_updated_by = l_last_updated_by
,request_id = l_request_id
,program_application_id = l_program_application_id
,program_id = l_program_id
,program_update_date = l_program_update_date
WHERE util_summarized_code = 'S' -- Do we require this?
AND ROWID IN (SELECT row_id
FROM pa_rep_util_summ0_tmp
WHERE delete_flag = 'N'
);
PA_DEBUG.G_Err_Stage := '1100 : Records Updated in PA_FORECAST_ITEM_DETAILS: ' || to_char(SQL%ROWCOUNT);
** The delete statement for pa_forecast_items is removed.
*/
--
ELSIF l_process_method = 'A' THEN
IF P_DEBUG_MODE = 'Y' THEN /* Added Debug Profile Option Check for bug#2674619 */
/* Commented for Bug 2984871
PA_DEBUG.G_Err_Stage := '1300 : Records Deleted from PA_FORECAST_ITEM_DETAILS: ' || to_char(SQL%ROWCOUNT);
UPDATE pa_forecast_item_details
SET util_summarized_code = NULL
,last_update_date = l_last_update_date
,last_updated_by = l_last_updated_by
,request_id = l_request_id
,program_application_id = l_program_application_id
,program_id = l_program_id
,program_update_date = l_program_update_date
WHERE util_summarized_code = 'S' -- Do we require this?
AND ROWID IN (SELECT row_id
FROM pa_rep_util_summ0_tmp
);
PA_DEBUG.G_Err_Stage := '1400 : Records Updated in PA_FORECAST_ITEM_DETAILS: ' || to_char(SQL%ROWCOUNT);
** The delete statement for pa_forecast_items is removed.
*/
--
END IF;
delete from pa_rep_util_summ00_tmp
where row_id in (select row_id from pa_rep_util_summ0_tmp)
;
* Put the code for update outside of the if loop so that the thru date
* is updated with the end date of the current run (it would no longer
* reflect the furthest out date till which summarization was ever run,
* as was the case earlier)
* IF NVL(l_forecast_thru_date, l_fc_end_date -1) < l_fc_end_date THEN
* code for update
* END IF;
UPDATE pa_utilization_options_all
SET forecast_thru_date = l_fc_end_date
, forecast_last_run_date = sysdate
WHERE NVL(org_id, -99) = l_org_id;
* The delete logic is modified to delete all the forecast items
* which are processed (util_summarized_code is null).
*/
/*
** Bug 2263074
** The delete statement for pa_forecast_item_details is removed.
*/
COMMIT;
PROCEDURE Insert_Fcst_Into_Tmp_PA
IS
BEGIN
-- Set the error stack
PA_DEBUG.Set_Curr_Function( p_function => 'Insert_Fcst_Into_Tmp_PA');
PA_DEBUG.G_Err_Stage := '2000 : Before Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for PA';
INSERT INTO pa_rep_util_summ00_tmp
( row_id
,parent_row_id
,expenditure_organization_id
,person_id
,assignment_id
,work_type_id
,org_util_category_id
,res_util_category_id
,expenditure_type
,expenditure_type_class
,pa_period_name
,pa_period_num
,pa_period_year
,pa_quarter_number
,gl_period_name
,gl_period_num
,gl_period_year
,gl_quarter_number
,global_exp_period_end_date
,global_exp_year
,global_exp_month_number
,total_hours
,total_prov_hours
,total_wghted_hours_people
,total_wghted_hours_org
,prov_wghted_hours_people
,prov_wghted_hours_org
,reduce_capacity
,delete_flag
)
SELECT fid.rowid row_id
,fi.rowid parent_row_id
,fi.expenditure_organization_id
,fi.person_id
,fi.assignment_id
,fid.work_type_id
,fid.org_util_category_id
,fid.resource_util_category_id
,fi.expenditure_type
,fi.expenditure_type_class
,fi.pvdr_pa_period_name pa_period_name
,(pp.period_year * 10000) + pp.period_num pa_period_num
,pp.period_year pa_period_year
,pp.quarter_num pa_quarter_number
,NULL gl_period_name
,NULL gl_period_num
,NULL gl_period_year
,NULL gl_quarter_number
,NULL global_exp_period_end_date
,NULL global_exp_year
,NULL global_exp_month_number
,NVL(fid.item_quantity, 0) total_hours
,NVL(fid.item_quantity, 0) * decode(fid.provisional_flag, 'Y', 1, 0) total_prov_hours
,NVL(fid.resource_util_weighted, 0) total_wghted_hours_people
,NVL(fid.org_util_weighted, 0) total_wghted_hours_org
,NVL(fid.resource_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_people
,NVL(fid.org_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_org
,DECODE(fid.reduce_capacity_flag, 'Y', 1, 'N', 0) * NVL(fid.item_quantity, 0) reduce_capacity
,'Y' delete_flag
FROM pa_forecast_items fi
,pa_forecast_item_details fid
,gl_periods pp
WHERE fi.forecast_item_id = fid.forecast_item_id
AND fi.expenditure_org_id = l_org_id
AND fid.expenditure_org_id = l_org_id
AND fi.forecast_item_type IN ('A', 'U')
AND fid.util_summarized_code = 'N'
--AND fid.person_billable_flag = 'Y'
AND fid.amount_type_id = l_quantity_id
-- AND pp.period_set_name = l_period_set_name
AND pp.period_set_name = l_pa_period_set_name -- bug 3434019
AND pp.period_type = l_pa_period_type
--AND fi.pvdr_period_set_name = pp.period_set_name /* commented for bug 3488229 */
AND fi.pvdr_pa_period_name = pp.period_name
AND fi.item_date BETWEEN l_fc_start_date AND TRUNC(l_fc_end_date)+0.99999; /* BUG# 3118592 */
PA_DEBUG.G_Err_Stage := '2050 : After Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for PA';
END Insert_Fcst_Into_Tmp_PA;
PROCEDURE Insert_Fcst_Into_Tmp_GL
IS
BEGIN
-- Set the error satack
PA_DEBUG.Set_Curr_Function( p_function => 'Insert_Fcst_Into_Tmp_GL');
PA_DEBUG.G_Err_Stage := '2100 : Before Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for GL';
INSERT INTO pa_rep_util_summ00_tmp
( row_id
,parent_row_id
,expenditure_organization_id
,person_id
,assignment_id
,work_type_id
,org_util_category_id
,res_util_category_id
,expenditure_type
,expenditure_type_class
,pa_period_name
,pa_period_num
,pa_period_year
,pa_quarter_number
,gl_period_name
,gl_period_num
,gl_period_year
,gl_quarter_number
,global_exp_period_end_date
,global_exp_year
,global_exp_month_number
,total_hours
,total_prov_hours
,total_wghted_hours_people
,total_wghted_hours_org
,prov_wghted_hours_people
,prov_wghted_hours_org
,reduce_capacity
,delete_flag
)
SELECT fid.rowid row_id
,fi.rowid parent_row_id
,fi.expenditure_organization_id
,fi.person_id
,fi.assignment_id
,fid.work_type_id
,fid.org_util_category_id
,fid.resource_util_category_id
,fi.expenditure_type
,fi.expenditure_type_class
,NULL pa_period_name
,NULL pa_period_num
,NULL pa_period_year
,NULL pa_quarter_number
,fi.pvdr_gl_period_name gl_period_name
,(gp.period_year * 10000) + gp.period_num gl_period_num
,gp.period_year gl_period_year
,gp.quarter_num gl_quarter_number
,NULL global_exp_period_end_date
,NULL global_exp_year
,NULL global_exp_month_number
,NVL(fid.item_quantity, 0) total_hours
,NVL(fid.item_quantity, 0) * decode(fid.provisional_flag, 'Y', 1, 0) total_prov_hours
,NVL(fid.resource_util_weighted, 0) total_wghted_hours_people
,NVL(fid.org_util_weighted, 0) total_wghted_hours_org
,NVL(fid.resource_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_people
,NVL(fid.org_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_org
,DECODE(fid.reduce_capacity_flag, 'Y', 1, 'N', 0) * NVL(fid.item_quantity, 0) reduce_capacity
,'Y' delete_flag
FROM pa_forecast_items fi
,pa_forecast_item_details fid
,gl_periods gp
WHERE fi.forecast_item_id = fid.forecast_item_id
AND fi.expenditure_org_id = l_org_id
AND fid.expenditure_org_id = l_org_id
AND fi.forecast_item_type IN ('A', 'U')
AND fid.util_summarized_code = 'N'
--AND fid.person_billable_flag = 'Y'
AND fid.amount_type_id = l_quantity_id
-- AND gp.period_set_name = l_period_set_name
AND gp.period_set_name = l_gl_period_set_name -- bug 3322360
AND gp.period_type = l_gl_period_type
--AND fi.pvdr_period_set_name = gp.period_set_name /* commented for bug 3488229 */
AND fi.pvdr_gl_period_name = gp.period_name
AND fi.item_date BETWEEN l_fc_start_date AND TRUNC(l_fc_end_date)+0.99999 ; /* BUG# 3118592 */
PA_DEBUG.G_Err_Stage := '2150 : After Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for GL';
END Insert_Fcst_Into_Tmp_GL;
PROCEDURE Insert_Fcst_Into_Tmp_GE
IS
BEGIN
-- Set the error satack
PA_DEBUG.Set_Curr_Function( p_function => 'Insert_Fcst_Into_Tmp_GE');
PA_DEBUG.G_Err_Stage := '2200 : Before Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for GE';
INSERT INTO pa_rep_util_summ00_tmp
( row_id
,parent_row_id
,expenditure_organization_id
,person_id
,assignment_id
,work_type_id
,org_util_category_id
,res_util_category_id
,expenditure_type
,expenditure_type_class
,pa_period_name
,pa_period_num
,pa_period_year
,pa_quarter_number
,gl_period_name
,gl_period_num
,gl_period_year
,gl_quarter_number
,global_exp_period_end_date
,global_exp_year
,global_exp_month_number
,total_hours
,total_prov_hours
,total_wghted_hours_people
,total_wghted_hours_org
,prov_wghted_hours_people
,prov_wghted_hours_org
,reduce_capacity
,delete_flag
)
SELECT fid.rowid row_id
,fi.rowid parent_row_id
,fi.expenditure_organization_id
,fi.person_id
,fi.assignment_id
,fid.work_type_id
,fid.org_util_category_id
,fid.resource_util_category_id
,fi.expenditure_type
,fi.expenditure_type_class
,NULL pa_period_name
,NULL pa_period_num
,NULL pa_period_year
,NULL pa_quarter_number
,NULL gl_period_name
,NULL gl_period_num
,NULL gl_period_year
,NULL gl_quarter_number
,trunc(fi.global_exp_period_end_date) global_exp_period_end_date
,to_number(to_char(fi.global_exp_period_end_date, 'YYYY')) global_exp_year
,to_number(to_char(fi.global_exp_period_end_date, 'MM')) global_exp_month_number
,NVL(fid.item_quantity, 0) total_hours
,NVL(fid.item_quantity, 0) * decode(fid.provisional_flag, 'Y', 1, 0) total_prov_hours
,NVL(fid.resource_util_weighted, 0) total_wghted_hours_people
,NVL(fid.org_util_weighted, 0) total_wghted_hours_org
,NVL(fid.resource_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_people
,NVL(fid.org_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_org
,DECODE(fid.reduce_capacity_flag, 'Y', 1, 'N', 0) * NVL(fid.item_quantity, 0) reduce_capacity
,'Y' delete_flag
FROM pa_forecast_items fi
,pa_forecast_item_details fid
WHERE fi.forecast_item_id = fid.forecast_item_id
AND fi.expenditure_org_id = l_org_id
AND fid.expenditure_org_id = l_org_id
AND fi.forecast_item_type IN ('A', 'U')
AND fid.util_summarized_code = 'N'
--AND fid.person_billable_flag = 'Y'
AND fid.amount_type_id = l_quantity_id
AND fi.item_date BETWEEN l_fc_start_date AND TRUNC(l_fc_end_date)+0.99999; /* BUG# 3118592 */
PA_DEBUG.G_Err_Stage := '2250 : After Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for GE';
END Insert_Fcst_Into_Tmp_GE;
PROCEDURE Insert_Fcst_Into_Tmp_PAGL
IS
BEGIN
-- Set the error satack
PA_DEBUG.Set_Curr_Function( p_function => 'Insert_Fcst_Into_Tmp_PAGL');
PA_DEBUG.G_Err_Stage := '2300 : Before Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for PA and GL';
INSERT INTO pa_rep_util_summ00_tmp
( row_id
,parent_row_id
,expenditure_organization_id
,person_id
,assignment_id
,work_type_id
,org_util_category_id
,res_util_category_id
,expenditure_type
,expenditure_type_class
,pa_period_name
,pa_period_num
,pa_period_year
,pa_quarter_number
,gl_period_name
,gl_period_num
,gl_period_year
,gl_quarter_number
,global_exp_period_end_date
,global_exp_year
,global_exp_month_number
,total_hours
,total_prov_hours
,total_wghted_hours_people
,total_wghted_hours_org
,prov_wghted_hours_people
,prov_wghted_hours_org
,reduce_capacity
,delete_flag
)
SELECT fid.rowid row_id
,fi.rowid parent_row_id
,fi.expenditure_organization_id
,fi.person_id
,fi.assignment_id
,fid.work_type_id
,fid.org_util_category_id
,fid.resource_util_category_id
,fi.expenditure_type
,fi.expenditure_type_class
,fi.pvdr_pa_period_name pa_period_name
,(pp.period_year * 10000) + pp.period_num pa_period_num
,pp.period_year pa_period_year
,pp.quarter_num pa_quarter_number
,fi.pvdr_gl_period_name gl_period_name
,(gp.period_year * 10000) + gp.period_num gl_period_num
,gp.period_year gl_period_year
,gp.quarter_num gl_quarter_number
,NULL global_exp_period_end_date
,NULL global_exp_year
,NULL global_exp_month_number
,NVL(fid.item_quantity, 0) total_hours
,NVL(fid.item_quantity, 0) * decode(fid.provisional_flag, 'Y', 1, 0) total_prov_hours
,NVL(fid.resource_util_weighted, 0) total_wghted_hours_people
,NVL(fid.org_util_weighted, 0) total_wghted_hours_org
,NVL(fid.resource_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_people
,NVL(fid.org_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_org
,DECODE(fid.reduce_capacity_flag, 'Y', 1, 'N', 0) * NVL(fid.item_quantity, 0) reduce_capacity
,'Y' delete_flag
FROM pa_forecast_items fi
,pa_forecast_item_details fid
,gl_periods pp
,gl_periods gp
WHERE fi.forecast_item_id = fid.forecast_item_id
AND fi.expenditure_org_id = l_org_id
AND fid.expenditure_org_id = l_org_id
AND fi.forecast_item_type IN ('A', 'U')
AND fid.util_summarized_code = 'N'
--AND fid.person_billable_flag = 'Y'
AND fid.amount_type_id = l_quantity_id
-- AND pp.period_set_name = l_period_set_name
AND pp.period_set_name = l_pa_period_set_name -- bug 3434019
AND pp.period_type = l_pa_period_type
--AND fi.pvdr_period_set_name = pp.period_set_name /* commented for bug 3488229 */
AND fi.pvdr_pa_period_name = pp.period_name
-- AND gp.period_set_name = l_period_set_name
AND gp.period_set_name = l_gl_period_set_name -- bug 3434019
AND gp.period_type = l_gl_period_type
--AND fi.pvdr_period_set_name = gp.period_set_name /* commented for bug 3488229 */
AND fi.pvdr_gl_period_name = gp.period_name
AND fi.item_date BETWEEN l_fc_start_date AND TRUNC(l_fc_end_date)+0.99999 ; /* BUG# 3118592 */
PA_DEBUG.G_Err_Stage := '2350 : After Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for PA and GL';
END Insert_Fcst_Into_Tmp_PAGL;
PROCEDURE Insert_Fcst_Into_Tmp_PAGE
IS
BEGIN
-- Set the error satack
PA_DEBUG.Set_Curr_Function( p_function => 'Insert_Fcst_Into_Tmp_PAGE');
PA_DEBUG.G_Err_Stage := '2400 : Before Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for PA and GE';
INSERT INTO pa_rep_util_summ00_tmp
( row_id
,parent_row_id
,expenditure_organization_id
,person_id
,assignment_id
,work_type_id
,org_util_category_id
,res_util_category_id
,expenditure_type
,expenditure_type_class
,pa_period_name
,pa_period_num
,pa_period_year
,pa_quarter_number
,gl_period_name
,gl_period_num
,gl_period_year
,gl_quarter_number
,global_exp_period_end_date
,global_exp_year
,global_exp_month_number
,total_hours
,total_prov_hours
,total_wghted_hours_people
,total_wghted_hours_org
,prov_wghted_hours_people
,prov_wghted_hours_org
,reduce_capacity
,delete_flag
)
SELECT fid.rowid row_id
,fi.rowid parent_row_id
,fi.expenditure_organization_id
,fi.person_id
,fi.assignment_id
,fid.work_type_id
,fid.org_util_category_id
,fid.resource_util_category_id
,fi.expenditure_type
,fi.expenditure_type_class
,fi.pvdr_pa_period_name pa_period_name
,(pp.period_year * 10000) + pp.period_num pa_period_num
,pp.period_year pa_period_year
,pp.quarter_num pa_quarter_number
,NULL gl_period_name
,NULL gl_period_num
,NULL gl_period_year
,NULL gl_quarter_number
,trunc(fi.global_exp_period_end_date) global_exp_period_end_date
,to_number(to_char(fi.global_exp_period_end_date, 'YYYY')) global_exp_year
,to_number(to_char(fi.global_exp_period_end_date, 'MM')) global_exp_month_number
,NVL(fid.item_quantity, 0) total_hours
,NVL(fid.item_quantity, 0) * decode(fid.provisional_flag, 'Y', 1, 0) total_prov_hours
,NVL(fid.resource_util_weighted, 0) total_wghted_hours_people
,NVL(fid.org_util_weighted, 0) total_wghted_hours_org
,NVL(fid.resource_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_people
,NVL(fid.org_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_org
,DECODE(fid.reduce_capacity_flag, 'Y', 1, 'N', 0) * NVL(fid.item_quantity, 0) reduce_capacity
,'Y' delete_flag
FROM pa_forecast_items fi
,pa_forecast_item_details fid
,gl_periods pp
WHERE fi.forecast_item_id = fid.forecast_item_id
AND fi.expenditure_org_id = l_org_id
AND fid.expenditure_org_id = l_org_id
AND fi.forecast_item_type IN ('A', 'U')
AND fid.util_summarized_code = 'N'
--AND fid.person_billable_flag = 'Y'
AND fid.amount_type_id = l_quantity_id
-- AND pp.period_set_name = l_period_set_name
AND pp.period_set_name = l_pa_period_set_name -- bug 3434019
AND pp.period_type = l_pa_period_type
--AND fi.pvdr_period_set_name = pp.period_set_name /* commented for bug 3488229 */
AND fi.pvdr_pa_period_name = pp.period_name
AND fi.item_date BETWEEN l_fc_start_date AND TRUNC(l_fc_end_date)+0.99999 ; /* BUG# 3118592 */
PA_DEBUG.G_Err_Stage := '2450 : After Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for PA and GE';
END Insert_Fcst_Into_Tmp_PAGE;
PROCEDURE Insert_Fcst_Into_Tmp_GLGE
IS
BEGIN
-- Set the error satack
PA_DEBUG.Set_Curr_Function( p_function => 'Insert_Fcst_Into_Tmp_GLGE');
PA_DEBUG.G_Err_Stage := '2500 : Before Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for GL and GE';
INSERT INTO pa_rep_util_summ00_tmp
( row_id
,parent_row_id
,expenditure_organization_id
,person_id
,assignment_id
,work_type_id
,org_util_category_id
,res_util_category_id
,expenditure_type
,expenditure_type_class
,pa_period_name
,pa_period_num
,pa_period_year
,pa_quarter_number
,gl_period_name
,gl_period_num
,gl_period_year
,gl_quarter_number
,global_exp_period_end_date
,global_exp_year
,global_exp_month_number
,total_hours
,total_prov_hours
,total_wghted_hours_people
,total_wghted_hours_org
,prov_wghted_hours_people
,prov_wghted_hours_org
,reduce_capacity
,delete_flag
)
SELECT fid.rowid row_id
,fi.rowid parent_row_id
,fi.expenditure_organization_id
,fi.person_id
,fi.assignment_id
,fid.work_type_id
,fid.org_util_category_id
,fid.resource_util_category_id
,fi.expenditure_type
,fi.expenditure_type_class
,NULL pa_period_name
,NULL pa_period_num
,NULL pa_period_year
,NULL pa_quarter_number
,fi.pvdr_gl_period_name gl_period_name
,(gp.period_year * 10000) + gp.period_num gl_period_num
,gp.period_year gl_period_year
,gp.quarter_num gl_quarter_number
,trunc(fi.global_exp_period_end_date) global_exp_period_end_date
,to_number(to_char(fi.global_exp_period_end_date, 'YYYY')) global_exp_year
,to_number(to_char(fi.global_exp_period_end_date, 'MM')) global_exp_month_number
,NVL(fid.item_quantity, 0) total_hours
,NVL(fid.item_quantity, 0) * decode(fid.provisional_flag, 'Y', 1, 0) total_prov_hours
,NVL(fid.resource_util_weighted, 0) total_wghted_hours_people
,NVL(fid.org_util_weighted, 0) total_wghted_hours_org
,NVL(fid.resource_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_people
,NVL(fid.org_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_org
,DECODE(fid.reduce_capacity_flag, 'Y', 1, 'N', 0) * NVL(fid.item_quantity, 0) reduce_capacity
,'Y' delete_flag
FROM pa_forecast_items fi
,pa_forecast_item_details fid
,gl_periods gp
WHERE fi.forecast_item_id = fid.forecast_item_id
AND fi.expenditure_org_id = l_org_id
AND fid.expenditure_org_id = l_org_id
AND fi.forecast_item_type IN ('A', 'U')
AND fid.util_summarized_code = 'N'
--AND fid.person_billable_flag = 'Y'
AND fid.amount_type_id = l_quantity_id
-- AND gp.period_set_name = l_period_set_name
AND gp.period_set_name = l_gl_period_set_name -- bug 3434019
AND gp.period_type = l_gl_period_type
--AND fi.pvdr_period_set_name = gp.period_set_name /* commented for bug 3488229 */
AND fi.pvdr_gl_period_name = gp.period_name
AND fi.item_date BETWEEN l_fc_start_date AND TRUNC(l_fc_end_date)+0.99999; /* BUG# 3118592 */
PA_DEBUG.G_Err_Stage := '2550 : After Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for GL and GE';
END Insert_Fcst_Into_Tmp_GLGE;
PROCEDURE Insert_Fcst_Into_Tmp_PAGLGE
IS
BEGIN
-- Set the error satack
PA_DEBUG.Set_Curr_Function( p_function => 'Insert_Fcst_Into_Tmp_PAGLGE');
PA_DEBUG.G_Err_Stage := '2600 : Before Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for PA, GL and GE';
INSERT INTO pa_rep_util_summ00_tmp
( row_id
,parent_row_id
,expenditure_organization_id
,person_id
,assignment_id
,work_type_id
,org_util_category_id
,res_util_category_id
,expenditure_type
,expenditure_type_class
,pa_period_name
,pa_period_num
,pa_period_year
,pa_quarter_number
,gl_period_name
,gl_period_num
,gl_period_year
,gl_quarter_number
,global_exp_period_end_date
,global_exp_year
,global_exp_month_number
,total_hours
,total_prov_hours
,total_wghted_hours_people
,total_wghted_hours_org
,prov_wghted_hours_people
,prov_wghted_hours_org
,reduce_capacity
,delete_flag
)
SELECT fid.rowid row_id
,fi.rowid parent_row_id
,fi.expenditure_organization_id
,fi.person_id
,fi.assignment_id
,fid.work_type_id
,fid.org_util_category_id
,fid.resource_util_category_id
,fi.expenditure_type
,fi.expenditure_type_class
,fi.pvdr_pa_period_name pa_period_name
,(pp.period_year * 10000) + pp.period_num pa_period_num
,pp.period_year pa_period_year
,pp.quarter_num pa_quarter_number
,fi.pvdr_gl_period_name gl_period_name
,(gp.period_year * 10000) + gp.period_num gl_period_num
,gp.period_year gl_period_year
,gp.quarter_num gl_quarter_number
,trunc(fi.global_exp_period_end_date) global_exp_period_end_date
,to_number(to_char(fi.global_exp_period_end_date, 'YYYY')) global_exp_year
,to_number(to_char(fi.global_exp_period_end_date, 'MM')) global_exp_month_number
,NVL(fid.item_quantity, 0) total_hours
,NVL(fid.item_quantity, 0) * decode(fid.provisional_flag, 'Y', 1, 0) total_prov_hours
,NVL(fid.resource_util_weighted, 0) total_wghted_hours_people
,NVL(fid.org_util_weighted, 0) total_wghted_hours_org
,NVL(fid.resource_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_people
,NVL(fid.org_util_weighted, 0) * decode(fid.provisional_flag, 'Y', 1, 0) prov_wghted_hours_org
,DECODE(fid.reduce_capacity_flag, 'Y', 1, 'N', 0) * NVL(fid.item_quantity, 0) reduce_capacity
,'Y' delete_flag
FROM pa_forecast_items fi
,pa_forecast_item_details fid
,gl_periods pp
,gl_periods gp
WHERE fi.forecast_item_id = fid.forecast_item_id
AND fi.expenditure_org_id = l_org_id
AND fid.expenditure_org_id = l_org_id
AND fi.forecast_item_type IN ('A', 'U')
AND fid.util_summarized_code = 'N'
--AND fid.person_billable_flag = 'Y'
AND fid.amount_type_id = l_quantity_id
-- AND pp.period_set_name = l_period_set_name
AND pp.period_set_name = l_pa_period_set_name -- bug 3434019
AND pp.period_type = l_pa_period_type
--AND fi.pvdr_period_set_name = pp.period_set_name /* commented for bug 3488229 */
AND fi.pvdr_pa_period_name = pp.period_name
-- AND gp.period_set_name = l_period_set_name
AND gp.period_set_name = l_gl_period_set_name -- bug 3434019
AND gp.period_type = l_gl_period_type
--AND fi.pvdr_period_set_name = gp.period_set_name /* commented for bug 3488229 */
AND fi.pvdr_gl_period_name = gp.period_name
AND fi.item_date BETWEEN l_fc_start_date AND TRUNC(l_fc_end_date)+0.99999 ; /* BUG# 3118592 */
PA_DEBUG.G_Err_Stage := '2650 : After Inserting Forecast data into PA_REP_UTIL_SUMM0_TMP for PA, GL and GE';
END Insert_Fcst_Into_Tmp_PAGLGE;