[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
l_program_update_date DATE := pa_rep_util_glob.G_who_columns.G_last_update_date;
PROCEDURE insert_act_into_tmp_table
IS
BEGIN
PA_DEBUG.Set_Curr_Function( p_function => 'insert_act_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;
PA_DEBUG.g_err_stage := '210: After inserting recs into Temp Table';
END insert_act_into_tmp_table;
l_records_inserted PLS_INTEGER;
l_records_updated PLS_INTEGER;
* None of the options are selected
*/
PA_DEBUG.Reset_Curr_Function;
insert_act_into_tmp_PAGLGE;
insert_act_into_tmp_PAGL;
insert_act_into_tmp_PAGE;
insert_act_into_tmp_PA;
insert_act_into_tmp_GLGE;
insert_act_into_tmp_GL;
insert_act_into_tmp_GE;
insert_act_into_tmp_table;
* Check if ANY records have been inserted into the temporary table.
* If NO records are inserted, getout of the loop.
*/
IF l_debug ='Y'THEN -- bug 2674619
PA_DEBUG.g_err_stage := '220 : After Calling the INSERT_PROC_[PA][GL][GE]';
l_records_inserted := SQL%ROWCOUNT;
PA_DEBUG.g_err_stage := '225 : Records Inserted in Temp tab : '||l_records_inserted;
IF (l_records_inserted = 0 AND l_capacity_summarized = 1) THEN
IF l_debug ='Y'THEN -- bug 2674619
PA_DEBUG.G_Err_Stage := '757 : EXITING since l_records_inserted = 0 AND l_capacity_summarized = 1';
* Update CDL with util_summarized_flag = 'S' with the
* rowids available in the temp0 table.
* The local rowid plsql table collects all the rowids
* for which updation went thro' fine.
*/
IF l_debug ='Y'THEN -- bug 2674619
PA_DEBUG.g_err_stage := '250 : Before Updating PA_CDL to UTIL_SUMM_FLAG to S';
UPDATE pa_cost_distribution_lines_all
SET util_summarized_flag = 'S'
WHERE util_summarized_flag = 'N' AND
NVL(org_id,-99) = l_org_id
AND ROWID IN (
SELECT row_id
FROM pa_rep_util_summ0_tmp
)
RETURNING ROWID BULK COLLECT INTO l_cdl_rowid_tab;
l_records_updated := SQL%ROWCOUNT;
* records in temp0 have been updated with util_summarized_flag = NULL
* if YES, process_method = 'A' ( ALL) ELSE process_method = 'F'
* (FILTER - based on the PA_REP_UTIL_SUMM0_TMP.delete_flag).
*
* The delete flag in pa_rep_util_summ0_tmp is initialized
* to 'Y' when inserted. But if updation of util_summarized_flag = 'S'
* goes through fine for those records, then those records
* are updated to delete_flag = 'N' - meaning that, those
* records SHOULD processed and hence to be considered as
* NOT deleted.
*/
IF l_debug ='Y'THEN -- bug 2674619
PA_DEBUG.g_err_stage := '300 : After Updating PA_CDL to UTIL_SUMM_FLAG to S';
PA_DEBUG.g_err_stage := '325 : Records Updated in PA_CDL : '||l_records_updated;
IF (l_records_updated < l_records_inserted AND l_cdl_rowid_tab.COUNT > 0 ) THEN /* Added second condition 2084888 */
l_process_method := 'F';
UPDATE pa_rep_util_summ0_tmp tmp1
SET tmp1.delete_flag = 'N'
WHERE tmp1.row_id = l_cdl_rowid_tab(i);
* Delete the rowid plsql table.
*/
l_cdl_rowid_tab.DELETE;
* If the process_method is 'F' (Filter), update only those records
* in cdl with util_summarized_flag = NULL which got successfully
* updated to 'S' (delte_flag in temp0 is 'N.
* If process_method is 'A' (All), update all records in cdl with
* util_summarized_flag = 'S' to NULL.
*/
IF l_debug ='Y'THEN -- bug 2674619
PA_DEBUG.g_err_stage := '450: After calling PA_REP_UTILS_SUMM_PKG';
UPDATE pa_cost_distribution_lines_all
-- SET util_summarized_flag = 'Y'
SET util_summarized_flag = NULL
,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_flag = 'S' -- Do we require this?
AND NVL(org_id,-99) = l_org_id -- Do we require this?
AND ROWID IN ( SELECT row_id
FROM pa_rep_util_summ0_tmp
WHERE delete_flag = 'N'
);
UPDATE pa_cost_distribution_lines_all
-- SET util_summarized_flag = 'Y'
SET util_summarized_flag = NULL
,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_flag = 'S' -- Do we require this?
AND NVL(org_id,-99) = l_org_id -- Do we require this?
AND ROWID IN ( SELECT row_id
FROM pa_rep_util_summ0_tmp
);
* delete the processed data from the intitial temporary workspace
*/
DELETE FROM pa_rep_util_summ00_tmp
WHERE row_id IN (SELECT row_id FROM pa_rep_util_summ0_tmp);
* Update pa_utilization_options with the dates for which
* Balances exist.
*/
/*
* Bug 1628557
* 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_actuals_thru_date, l_ac_end_date -1) < l_ac_end_date THEN
* code for update
* END IF;
UPDATE pa_utilization_options_all
SET actuals_thru_date = l_ac_end_date
, actuals_last_run_date = sysdate
WHERE NVL(org_id,-99) = l_org_id;
PROCEDURE insert_act_into_tmp_PA
IS
BEGIN
PA_DEBUG.Set_Curr_Function( p_function => 'Insert_act_into_tmp_PA');
PA_DEBUG.g_err_stage := '205: Before inserting recs into Temp Table';
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_Num
,Pa_Period_Name
,Pa_Quarter_Number
,Pa_Period_Year
,Gl_Period_Num
,Gl_Period_Name
,Gl_Quarter_Number
,Gl_Period_Year
,Global_Exp_Period_End_Date
,Global_Exp_Month_Number
,Global_Exp_Year
,Total_Hours
,Total_Wghted_Hours_Org
,Total_Wghted_Hours_People
,Reduce_Capacity
,Total_Prov_Hours
,Prov_Wghted_Hours_Org
,Prov_Wghted_Hours_People
,Delete_flag
)
SELECT
cdl.ROWID row_id
,NULL parent_row_id
,NVL(ei.override_to_organization_id, exp.incurred_by_organization_id) exp_organization_id
,exp.incurred_by_person_id person_id
,-1 assignment_id
,cdl.work_type_id work_type_id --bug 2980483
,wt.org_util_category_id org_util_category_id
,wt.res_util_category_id res_util_category_id
,ei.expenditure_type exp_type
,ei.system_linkage_function exp_type_class
,(pglp.period_year*10000 + pglp.period_num) pa_period_number
,pglp.period_name pa_period_name
,pglp.quarter_num pa_qtr_num
,pglp.period_year pa_period_year
,NULL gl_period_number
,NULL gl_period_name
,NULL gl_qtr_num
,NULL gl_period_year
,NULL ge_end_date
,NULL ge_month_number
,NULL ge_period_year
,NVL(cdl.quantity, 0) work_qty
,ROUND(((NVL(cdl.quantity, 0)* wt.org_utilization_percentage)/100),2) org_weighted_qty
,ROUND(((NVL(cdl.quantity, 0)* wt.res_utilization_percentage)/100),2) res_weighted_qty
,DECODE(wt.reduce_capacity_flag, 'Y',
NVL(cdl.quantity, 0), 0) reduce_cpcty
,0 tot_prov_hrs
,0 prov_wghtd_hrs_org
,0 prov_wghtd_hrs_people
,'Y' delete_flag
FROM
pa_expenditures_all exp
,pa_expenditure_items_all ei
,pa_cost_distribution_lines_all cdl
,gl_periods pglp
,pa_work_types_b wt
,pa_resources_denorm res
WHERE NVL(cdl.org_id, -99) = NVL(ei.org_id, -99)
AND NVL(exp.org_id, -99) = NVL(ei.org_id, -99)
-- AND pglp.period_set_name = l_period_set_name
AND pglp.period_set_name = l_pa_period_set_name -- bug 3434019
AND pglp.period_name = cdl.pa_period_name
AND ei.expenditure_item_date BETWEEN l_ac_start_date AND TRUNC(l_ac_end_date)+0.99999 /* BUG# 3118592 */
AND ei.expenditure_item_date BETWEEN res.resource_effective_start_date AND NVL(TRUNC(res.resource_effective_end_date)+0.99999, ei.expenditure_item_date) /* BUG# 3118592 */
AND NVL(res.utilization_flag, 'N') = 'Y'
AND res.person_id = exp.incurred_by_person_id
AND res.resource_organization_id = nvl(ei.override_to_organization_id,
exp.incurred_by_organization_id )
AND exp.expenditure_id = ei.expenditure_id
AND ei.system_linkage_function IN ('ST', 'OT')
AND ei.cost_distributed_flag = 'Y'
AND ei.expenditure_item_id = cdl.expenditure_item_id
AND cdl.work_type_id = wt.work_type_id --bug 2980483
AND cdl.line_type = 'R'
AND cdl.util_summarized_flag = 'N'
AND NVL(cdl.org_id,-99) = l_org_id;
PA_DEBUG.g_err_stage := '210: After inserting recs into Temp Table';
END insert_act_into_tmp_PA;
PROCEDURE insert_act_into_tmp_GL
IS
BEGIN
PA_DEBUG.Set_Curr_Function( p_function => 'Insert_act_into_tmp_GL');
PA_DEBUG.g_err_stage := '205: Before inserting recs into Temp Table';
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_Num
,Pa_Period_Name
,Pa_Quarter_Number
,Pa_Period_Year
,Gl_Period_Num
,Gl_Period_Name
,Gl_Quarter_Number
,Gl_Period_Year
,Global_Exp_Period_End_Date
,Global_Exp_Month_Number
,Global_Exp_Year
,Total_Hours
,Total_Wghted_Hours_Org
,Total_Wghted_Hours_People
,Reduce_Capacity
,Total_Prov_Hours
,Prov_Wghted_Hours_Org
,Prov_Wghted_Hours_People
,Delete_flag
)
SELECT
cdl.ROWID row_id
,NULL parent_row_id
,NVL(ei.override_to_organization_id, exp.incurred_by_organization_id) exp_organization_id
,exp.incurred_by_person_id person_id
,-1 assignment_id
,cdl.work_type_id work_type_id --bug 2980483
,wt.org_util_category_id org_util_category_id
,wt.res_util_category_id res_util_category_id
,ei.expenditure_type exp_type
,ei.system_linkage_function exp_type_class
,NULL pa_period_number
,NULL pa_period_name
,NULL pa_qtr_num
,NULL pa_period_year
,(gglp.period_year*10000 + gglp.period_num) gl_period_number
,gglp.period_name gl_period_name
,gglp.quarter_num gl_qtr_num
,gglp.period_year gl_period_year
,NULL ge_end_date
,NULL ge_month_number
,NULL ge_period_year
,NVL(cdl.quantity, 0) work_qty
,ROUND(((NVL(cdl.quantity, 0)* wt.org_utilization_percentage)/100),2) org_weighted_qty
,ROUND(((NVL(cdl.quantity, 0)* wt.res_utilization_percentage)/100),2) res_weighted_qty
,DECODE(wt.reduce_capacity_flag, 'Y',
NVL(cdl.quantity, 0), 0) reduce_cpcty
,0 tot_prov_hrs
,0 prov_wghtd_hrs_org
,0 prov_wghtd_hrs_people
,'Y' delete_flag
FROM
pa_expenditures_all exp
,pa_expenditure_items_all ei
,pa_cost_distribution_lines_all cdl
,gl_periods gglp
,pa_work_types_b wt
,pa_resources_denorm res
WHERE NVL(cdl.org_id, -99) = NVL(ei.org_id, -99)
AND NVL(exp.org_id, -99) = NVL(ei.org_id, -99)
-- AND gglp.period_set_name = l_period_set_name
AND gglp.period_set_name = l_gl_period_set_name -- bug 3434019
AND gglp.period_name = cdl.gl_period_name
AND ei.expenditure_item_date BETWEEN l_ac_start_date AND TRUNC(l_ac_end_date)+0.99999 /* BUG# 3118592 */
AND ei.expenditure_item_date BETWEEN res.resource_effective_start_date AND NVL(TRUNC(res.resource_effective_end_date)+0.99999, ei.expenditure_item_date) /* BUG# 3118592 */
AND NVL(res.utilization_flag, 'N') = 'Y'
AND res.person_id = exp.incurred_by_person_id
AND res.resource_organization_id = nvl(ei.override_to_organization_id,
exp.incurred_by_organization_id )
AND exp.expenditure_id = ei.expenditure_id
AND ei.system_linkage_function IN ('ST', 'OT')
AND ei.cost_distributed_flag = 'Y'
AND ei.expenditure_item_id = cdl.expenditure_item_id
AND cdl.work_type_id = wt.work_type_id --bug 2980483
AND cdl.line_type = 'R'
AND cdl.util_summarized_flag = 'N'
AND NVL(cdl.org_id,-99) = l_org_id;
PA_DEBUG.g_err_stage := '210: After inserting recs into Temp Table';
END insert_act_into_tmp_GL;
PROCEDURE insert_act_into_tmp_PAGL
IS
BEGIN
PA_DEBUG.Set_Curr_Function( p_function => 'Insert_act_into_tmp_PAGL');
PA_DEBUG.g_err_stage := '205: Before inserting recs into Temp Table';
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_Num
,Pa_Period_Name
,Pa_Quarter_Number
,Pa_Period_Year
,Gl_Period_Num
,Gl_Period_Name
,Gl_Quarter_Number
,Gl_Period_Year
,Global_Exp_Period_End_Date
,Global_Exp_Month_Number
,Global_Exp_Year
,Total_Hours
,Total_Wghted_Hours_Org
,Total_Wghted_Hours_People
,Reduce_Capacity
,Total_Prov_Hours
,Prov_Wghted_Hours_Org
,Prov_Wghted_Hours_People
,Delete_flag
)
SELECT
cdl.ROWID row_id
,NULL parent_row_id
,NVL(ei.override_to_organization_id, exp.incurred_by_organization_id) exp_organization_id
,exp.incurred_by_person_id person_id
,-1 assignment_id
,cdl.work_type_id work_type_id --bug 2980483
,wt.org_util_category_id org_util_category_id
,wt.res_util_category_id res_util_category_id
,ei.expenditure_type exp_type
,ei.system_linkage_function exp_type_class
,(pglp.period_year*10000 + pglp.period_num) pa_period_number
,pglp.period_name pa_period_name
,pglp.quarter_num pa_qtr_num
,pglp.period_year pa_period_year
,(gglp.period_year*10000 + gglp.period_num) gl_period_number
,gglp.period_name gl_period_name
,gglp.quarter_num gl_qtr_num
,gglp.period_year gl_period_year
,NULL ge_end_date
,NULL ge_month_number
,NULL ge_period_year
,NVL(cdl.quantity, 0) work_qty
,ROUND(((NVL(cdl.quantity, 0)* wt.org_utilization_percentage)/100),2) org_weighted_qty
,ROUND(((NVL(cdl.quantity, 0)* wt.res_utilization_percentage)/100),2) res_weighted_qty
,DECODE(wt.reduce_capacity_flag, 'Y',
NVL(cdl.quantity, 0), 0) reduce_cpcty
,0 tot_prov_hrs
,0 prov_wghtd_hrs_org
,0 prov_wghtd_hrs_people
,'Y' delete_flag
FROM
gl_periods pglp
,gl_periods gglp
,pa_work_types_b wt
,pa_resources_denorm res
,pa_expenditures_all exp
,pa_expenditure_items_all ei
,pa_cost_distribution_lines_all cdl
WHERE NVL(cdl.org_id, -99) = NVL(ei.org_id, -99)
AND NVL(exp.org_id, -99) = NVL(ei.org_id, -99)
-- AND pglp.period_set_name = l_period_set_name
AND pglp.period_set_name = l_pa_period_set_name -- bug 3434019
AND pglp.period_name = cdl.pa_period_name
-- AND gglp.period_set_name = l_period_set_name
AND gglp.period_set_name = l_gl_period_set_name -- bug 3434019
AND gglp.period_name = cdl.gl_period_name
AND ei.expenditure_item_date BETWEEN l_ac_start_date AND TRUNC(l_ac_end_date)+0.99999 /* BUG# 3118592 */
AND ei.expenditure_item_date BETWEEN res.resource_effective_start_date AND NVL(TRUNC(res.resource_effective_end_date)+0.99999, ei.expenditure_item_date) /* BUG# 3118592 */
AND NVL(res.utilization_flag, 'N') = 'Y'
AND res.person_id = exp.incurred_by_person_id
AND res.resource_organization_id = nvl(ei.override_to_organization_id,
exp.incurred_by_organization_id )
AND exp.expenditure_id = ei.expenditure_id
AND ei.system_linkage_function IN ('ST', 'OT')
AND ei.cost_distributed_flag = 'Y'
AND ei.expenditure_item_id = cdl.expenditure_item_id
AND cdl.work_type_id = wt.work_type_id --bug 2980483
AND cdl.line_type = 'R'
AND cdl.util_summarized_flag = 'N'
AND NVL(cdl.org_id,-99) = l_org_id;
PA_DEBUG.g_err_stage := '210: After inserting recs into Temp Table';
END insert_act_into_tmp_PAGL;
PROCEDURE insert_act_into_tmp_PAGE
IS
BEGIN
PA_DEBUG.Set_Curr_Function( p_function => 'Insert_act_into_tmp_PAGE');
PA_DEBUG.g_err_stage := '205: Before inserting recs into Temp Table';
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_Num
,Pa_Period_Name
,Pa_Quarter_Number
,Pa_Period_Year
,Gl_Period_Num
,Gl_Period_Name
,Gl_Quarter_Number
,Gl_Period_Year
,Global_Exp_Period_End_Date
,Global_Exp_Month_Number
,Global_Exp_Year
,Total_Hours
,Total_Wghted_Hours_Org
,Total_Wghted_Hours_People
,Reduce_Capacity
,Total_Prov_Hours
,Prov_Wghted_Hours_Org
,Prov_Wghted_Hours_People
,Delete_flag
)
SELECT
cdl.ROWID row_id
,NULL parent_row_id
,NVL(ei.override_to_organization_id, exp.incurred_by_organization_id) exp_organization_id
,exp.incurred_by_person_id person_id
,-1 assignment_id
,cdl.work_type_id work_type_id --bug 2980483
,wt.org_util_category_id org_util_category_id
,wt.res_util_category_id res_util_category_id
,ei.expenditure_type exp_type
,ei.system_linkage_function exp_type_class
,(pglp.period_year*10000 + pglp.period_num) pa_period_number
,pglp.period_name pa_period_name
,pglp.quarter_num pa_qtr_num
,pglp.period_year pa_period_year
,NULL gl_period_number
,NULL gl_period_name
,NULL gl_qtr_num
,NULL gl_period_year
,TRUNC(NEXT_DAY(ei.expenditure_item_date,
l_global_week_start_day) - 1) ge_end_date
,TO_NUMBER(TO_CHAR((NEXT_DAY(ei.expenditure_item_date,
l_global_week_start_day
) - 1), 'MM')) ge_month_number
,TO_NUMBER(TO_CHAR((NEXT_DAY(ei.expenditure_item_date,
l_global_week_start_day
) - 1), 'YYYY')) ge_period_year
,NVL(cdl.quantity, 0) work_qty
,ROUND(((NVL(cdl.quantity, 0)* wt.org_utilization_percentage)/100),2) org_weighted_qty
,ROUND(((NVL(cdl.quantity, 0)* wt.res_utilization_percentage)/100),2) res_weighted_qty
,DECODE(wt.reduce_capacity_flag, 'Y',
NVL(cdl.quantity, 0), 0) reduce_cpcty
,0 tot_prov_hrs
,0 prov_wghtd_hrs_org
,0 prov_wghtd_hrs_people
,'Y' delete_flag
FROM
pa_expenditures_all exp
,pa_expenditure_items_all ei
,pa_cost_distribution_lines_all cdl
,gl_periods pglp
,pa_work_types_b wt
,pa_resources_denorm res
WHERE NVL(cdl.org_id, -99) = NVL(ei.org_id, -99)
AND NVL(exp.org_id, -99) = NVL(ei.org_id, -99)
-- AND pglp.period_set_name = l_period_set_name
AND pglp.period_set_name = l_pa_period_set_name -- bug 3434019
AND pglp.period_name = cdl.pa_period_name
AND ei.expenditure_item_date BETWEEN l_ac_start_date AND TRUNC(l_ac_end_date)+0.99999 /* BUG# 3118592 */
AND ei.expenditure_item_date BETWEEN res.resource_effective_start_date AND NVL(TRUNC(res.resource_effective_end_date)+0.99999, ei.expenditure_item_date) /* BUG# 3118592 */
AND NVL(res.utilization_flag, 'N') = 'Y'
AND res.person_id = exp.incurred_by_person_id
AND res.resource_organization_id = nvl(ei.override_to_organization_id,
exp.incurred_by_organization_id )
AND exp.expenditure_id = ei.expenditure_id
AND ei.system_linkage_function IN ('ST', 'OT')
AND ei.cost_distributed_flag = 'Y'
AND ei.expenditure_item_id = cdl.expenditure_item_id
AND cdl.work_type_id = wt.work_type_id --bug 2980483
AND cdl.line_type = 'R'
AND cdl.util_summarized_flag = 'N'
AND NVL(cdl.org_id,-99) = l_org_id;
PA_DEBUG.g_err_stage := '210: After inserting recs into Temp Table';
END insert_act_into_tmp_PAGE;
PROCEDURE insert_act_into_tmp_GE
IS
BEGIN
PA_DEBUG.Set_Curr_Function( p_function => 'Insert_act_into_tmp_GE') ;
PA_DEBUG.g_err_stage := '205: Before inserting recs into Temp Table';
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_Num
,Pa_Period_Name
,Pa_Quarter_Number
,Pa_Period_Year
,Gl_Period_Num
,Gl_Period_Name
,Gl_Quarter_Number
,Gl_Period_Year
,Global_Exp_Period_End_Date
,Global_Exp_Month_Number
,Global_Exp_Year
,Total_Hours
,Total_Wghted_Hours_Org
,Total_Wghted_Hours_People
,Reduce_Capacity
,Total_Prov_Hours
,Prov_Wghted_Hours_Org
,Prov_Wghted_Hours_People
,Delete_flag
)
SELECT
cdl.ROWID row_id
,NULL parent_row_id
,NVL(ei.override_to_organization_id, exp.incurred_by_organization_id) exp_organization_id
,exp.incurred_by_person_id person_id
,-1 assignment_id
,cdl.work_type_id work_type_id --bug 2980483
,wt.org_util_category_id org_util_category_id
,wt.res_util_category_id res_util_category_id
,ei.expenditure_type exp_type
,ei.system_linkage_function exp_type_class
,NULL pa_period_number
,NULL pa_period_name
,NULL pa_qtr_num
,NULL pa_period_year
,NULL gl_period_number
,NULL gl_period_name
,NULL gl_qtr_num
,NULL gl_period_year
,TRUNC(NEXT_DAY(ei.expenditure_item_date,
l_global_week_start_day) - 1) ge_end_date
,TO_NUMBER(TO_CHAR((NEXT_DAY(ei.expenditure_item_date,
l_global_week_start_day
) - 1), 'MM')) ge_month_number
,TO_NUMBER(TO_CHAR((NEXT_DAY(ei.expenditure_item_date,
l_global_week_start_day
) - 1), 'YYYY')) ge_period_year
,NVL(cdl.quantity, 0) work_qty
,ROUND(((NVL(cdl.quantity, 0)* wt.org_utilization_percentage)/100),2) org_weighted_qty
,ROUND(((NVL(cdl.quantity, 0)* wt.res_utilization_percentage)/100),2) res_weighted_qty
,DECODE(wt.reduce_capacity_flag, 'Y',
NVL(cdl.quantity, 0), 0) reduce_cpcty
,0 tot_prov_hrs
,0 prov_wghtd_hrs_org
,0 prov_wghtd_hrs_people
,'Y' delete_flag
FROM
pa_work_types_b wt
,pa_resources_denorm res
,pa_expenditures_all exp
,pa_expenditure_items_all ei
,pa_cost_distribution_lines_all cdl
WHERE NVL(cdl.org_id, -99) = NVL(ei.org_id, -99)
AND NVL(exp.org_id, -99) = NVL(exp.org_id, -99)
AND ei.expenditure_item_date BETWEEN l_ac_start_date AND TRUNC(l_ac_end_date)+0.99999 /* BUG# 3118592 */
AND ei.expenditure_item_date BETWEEN res.resource_effective_start_date AND NVL(TRUNC(res.resource_effective_end_date)+0.99999, ei.expenditure_item_date) /* BUG# 3118592 */
AND NVL(res.utilization_flag, 'N') = 'Y'
AND res.person_id = exp.incurred_by_person_id
AND res.resource_organization_id = nvl(ei.override_to_organization_id,
exp.incurred_by_organization_id )
AND exp.expenditure_id = ei.expenditure_id
AND ei.system_linkage_function IN ('ST', 'OT')
AND ei.cost_distributed_flag = 'Y'
AND ei.expenditure_item_id = cdl.expenditure_item_id
AND cdl.work_type_id = wt.work_type_id --bug 2980483
AND cdl.line_type = 'R'
AND cdl.util_summarized_flag = 'N'
AND NVL(cdl.org_id,-99) = l_org_id;
PA_DEBUG.g_err_stage := '210: After inserting recs into Temp Table';
END insert_act_into_tmp_GE;
PROCEDURE insert_act_into_tmp_PAGLGE
IS
BEGIN
PA_DEBUG.Set_Curr_Function( p_function => 'Insert_act_into_tmp_PAGLGE');
PA_DEBUG.g_err_stage := '205: Before inserting recs into Temp Table';
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_Num
,Pa_Period_Name
,Pa_Quarter_Number
,Pa_Period_Year
,Gl_Period_Num
,Gl_Period_Name
,Gl_Quarter_Number
,Gl_Period_Year
,Global_Exp_Period_End_Date
,Global_Exp_Month_Number
,Global_Exp_Year
,Total_Hours
,Total_Wghted_Hours_Org
,Total_Wghted_Hours_People
,Reduce_Capacity
,Total_Prov_Hours
,Prov_Wghted_Hours_Org
,Prov_Wghted_Hours_People
,Delete_flag
)
SELECT
cdl.ROWID row_id
,NULL parent_row_id
,NVL(ei.override_to_organization_id, exp.incurred_by_organization_id) exp_organization_id
,exp.incurred_by_person_id person_id
,-1 assignment_id
,cdl.work_type_id work_type_id --bug 2980483
,wt.org_util_category_id org_util_category_id
,wt.res_util_category_id res_util_category_id
,ei.expenditure_type exp_type
,ei.system_linkage_function exp_type_class
,(pglp.period_year*10000 + pglp.period_num) pa_period_number
,pglp.period_name pa_period_name
,pglp.quarter_num pa_qtr_num
,pglp.period_year pa_period_year
,(gglp.period_year*10000 + gglp.period_num) gl_period_number
,gglp.period_name gl_period_name
,gglp.quarter_num gl_qtr_num
,gglp.period_year gl_period_year
,TRUNC(NEXT_DAY(ei.expenditure_item_date,
l_global_week_start_day) - 1) ge_end_date
,TO_NUMBER(TO_CHAR((NEXT_DAY(ei.expenditure_item_date,
l_global_week_start_day
) - 1), 'MM')) ge_month_number
,TO_NUMBER(TO_CHAR((NEXT_DAY(ei.expenditure_item_date,
l_global_week_start_day
) - 1), 'YYYY')) ge_period_year
,NVL(cdl.quantity, 0) work_qty
,ROUND(((NVL(cdl.quantity, 0)* wt.org_utilization_percentage)/100),2) org_weighted_qty
,ROUND(((NVL(cdl.quantity, 0)* wt.res_utilization_percentage)/100),2) res_weighted_qty
,DECODE(wt.reduce_capacity_flag, 'Y',
NVL(cdl.quantity, 0), 0) reduce_cpcty
,0 tot_prov_hrs
,0 prov_wghtd_hrs_org
,0 prov_wghtd_hrs_people
,'Y' delete_flag
FROM
gl_periods pglp
,gl_periods gglp
,pa_work_types_b wt
,pa_resources_denorm res
,pa_expenditures_all exp
,pa_expenditure_items_all ei
,pa_cost_distribution_lines_all cdl
WHERE NVL(cdl.org_id, -99) = NVL(ei.org_id, -99)
AND NVL(exp.org_id, -99) = NVL(ei.org_id, -99)
-- AND pglp.period_set_name = l_period_set_name
AND pglp.period_set_name = l_pa_period_set_name -- bug 3434019
AND pglp.period_name = cdl.pa_period_name
-- AND gglp.period_set_name = l_period_set_name
AND gglp.period_set_name = l_gl_period_set_name -- bug 3434019
AND gglp.period_name = cdl.gl_period_name
AND ei.expenditure_item_date BETWEEN l_ac_start_date AND TRUNC(l_ac_end_date)+0.99999 /* BUG# 3118592 */
AND ei.expenditure_item_date BETWEEN res.resource_effective_start_date AND NVL(TRUNC(res.resource_effective_end_date)+0.99999, ei.expenditure_item_date) /* BUG# 3118592 */
AND NVL(res.utilization_flag, 'N') = 'Y'
AND res.person_id = exp.incurred_by_person_id
AND res.resource_organization_id = nvl(ei.override_to_organization_id,
exp.incurred_by_organization_id )
AND exp.expenditure_id = ei.expenditure_id
AND ei.system_linkage_function IN ('ST', 'OT')
AND ei.cost_distributed_flag = 'Y'
AND ei.expenditure_item_id = cdl.expenditure_item_id
AND cdl.work_type_id = wt.work_type_id --bug 2980483
AND cdl.line_type = 'R'
AND cdl.util_summarized_flag = 'N'
AND NVL(cdl.org_id,-99) = l_org_id;
PA_DEBUG.g_err_stage := '210: After inserting recs into Temp Table';
END insert_act_into_tmp_PAGLGE;
PROCEDURE insert_act_into_tmp_GLGE
IS
BEGIN
PA_DEBUG.Set_Curr_Function( p_function => 'Insert_act_into_tmp_GLGE');
PA_DEBUG.g_err_stage := '205: Before inserting recs into Temp Table';
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_Num
,Pa_Period_Name
,Pa_Quarter_Number
,Pa_Period_Year
,Gl_Period_Num
,Gl_Period_Name
,Gl_Quarter_Number
,Gl_Period_Year
,Global_Exp_Period_End_Date
,Global_Exp_Month_Number
,Global_Exp_Year
,Total_Hours
,Total_Wghted_Hours_Org
,Total_Wghted_Hours_People
,Reduce_Capacity
,Total_Prov_Hours
,Prov_Wghted_Hours_Org
,Prov_Wghted_Hours_People
,Delete_flag
)
SELECT
cdl.ROWID row_id
,NULL parent_row_id
,NVL(ei.override_to_organization_id, exp.incurred_by_organization_id) exp_organization_id
,exp.incurred_by_person_id person_id
,-1 assignment_id
,cdl.work_type_id work_type_id --bug 2980483
,wt.org_util_category_id org_util_category_id
,wt.res_util_category_id res_util_category_id
,ei.expenditure_type exp_type
,ei.system_linkage_function exp_type_class
,NULL pa_period_number
,NULL pa_period_name
,NULL pa_qtr_num
,NULL pa_period_year
,(gglp.period_year*10000 + gglp.period_num) gl_period_number
,gglp.period_name gl_period_name
,gglp.quarter_num gl_qtr_num
,gglp.period_year gl_period_year
,TRUNC(NEXT_DAY(ei.expenditure_item_date,
l_global_week_start_day) - 1) ge_end_date
,TO_NUMBER(TO_CHAR((NEXT_DAY(ei.expenditure_item_date,
l_global_week_start_day
) - 1), 'MM')) ge_month_number
,TO_NUMBER(TO_CHAR((NEXT_DAY(ei.expenditure_item_date,
l_global_week_start_day
) - 1), 'YYYY')) ge_period_year
,NVL(cdl.quantity, 0) work_qty
,ROUND(((NVL(cdl.quantity, 0)* wt.org_utilization_percentage)/100),2) org_weighted_qty
,ROUND(((NVL(cdl.quantity, 0)* wt.res_utilization_percentage)/100),2) res_weighted_qty
,DECODE(wt.reduce_capacity_flag, 'Y',
NVL(cdl.quantity, 0), 0) reduce_cpcty
,0 tot_prov_hrs
,0 prov_wghtd_hrs_org
,0 prov_wghtd_hrs_people
,'Y' delete_flag
FROM
gl_periods gglp
,pa_work_types_b wt
,pa_resources_denorm res
,pa_expenditures_all exp
,pa_expenditure_items_all ei
,pa_cost_distribution_lines_all cdl
WHERE NVL(cdl.org_id, -99) = NVL(ei.org_id, -99)
AND NVL(exp.org_id, -99) = NVL(ei.org_id, -99)
-- AND gglp.period_set_name = l_period_set_name
AND gglp.period_set_name = l_gl_period_set_name -- bug 3434019
AND gglp.period_name = cdl.gl_period_name
AND ei.expenditure_item_date BETWEEN l_ac_start_date AND TRUNC(l_ac_end_date)+0.99999 /* BUG# 3118592 */
AND ei.expenditure_item_date BETWEEN res.resource_effective_start_date AND NVL(TRUNC(res.resource_effective_end_date)+0.99999, ei.expenditure_item_date) /* BUG# 3118592 */
AND NVL(res.utilization_flag, 'N') = 'Y'
AND res.person_id = exp.incurred_by_person_id
AND res.resource_organization_id = nvl(ei.override_to_organization_id,
exp.incurred_by_organization_id )
AND exp.expenditure_id = ei.expenditure_id
AND ei.system_linkage_function IN ('ST', 'OT')
AND ei.cost_distributed_flag = 'Y'
AND ei.expenditure_item_id = cdl.expenditure_item_id
AND cdl.work_type_id = wt.work_type_id --bug 2980483
AND cdl.line_type = 'R'
AND cdl.util_summarized_flag = 'N'
AND NVL(cdl.org_id,-99) = l_org_id;
PA_DEBUG.g_err_stage := '210: After inserting recs into Temp Table';
END insert_act_into_tmp_GLGE;