[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO hri_dbi_wmv_budget_evts_tmp
( supervisor_id
,effective_start_date
,effective_end_date
,budget_version_id
,budgeted_count)
SELECT DISTINCT org.org_information2, b.version_date_from, b.version_date_to,
b.budget_version_id,0
FROM hr_organization_information org,
hri_mb_budget_v b
WHERE org.org_information_context = 'Organization Name Alias'
AND org.org_information2 IS NOT NULL
AND org.organization_id = b.organization_id
AND b.position_control_flag_code = 'Y'
AND b.version_date_from BETWEEN p_effective_start_date
AND p_effective_end_date;
UPDATE hri_dbi_wmv_budget_evts_tmp hrp
SET hrp.budgeted_count = (SELECT sum(decode(g_abv_type,b.budget_unit1_system_type_cd, b.budget_unit1_value,
b.budget_unit2_system_type_cd, b.budget_unit2_value,
b.budget_unit1_system_type_cd,b.budget_unit3_value ))
FROM HRI_MB_BUDGET_V b,
HR_ORGANIZATION_INFORMATION org
WHERE b.position_control_flag_code = 'Y'
AND b.organization_id = org.organization_id
AND org.org_information_context = 'Organization Name Alias'
AND org.org_information2 = hrp.supervisor_id
AND b.version_date_from = hrp.effective_start_date
AND b.budget_version_id = hrp.budget_version_id);
SELECT hrp.supervisor_id, hrp.effective_start_date, hrp.effective_end_date,
hrp.budgeted_count
FROM hri_dbi_wmv_budget_evts_tmp hrp;
DELETE
FROM hr_ptl_summary_data psum
WHERE psum.sum_information_category = 'HRI_DBI_WMV_BUDGET'
AND psum.summary_context_type = 'SUPERVISOR';
bis_collection_utilities.log('Delete records from the HR_PORTAL_SUMMARY_DATA table',1);
INSERT INTO hr_ptl_summary_data
( summary_data_id
,summary_context_type
,summary_context_id
,effective_date
,created_by
,creation_date
,object_version_number
,sum_information_category
,sum_information1
,sum_information2
,effective_end_date)
VALUES ( hr_ptl_summary_data_s.NEXTVAL
,g_con_context_type
,l_supervisor_id
,l_effective_date
,fnd_global.user_id
,TRUNC(SYSDATE)
,1
,g_con_info_category
,l_location_id
,l_budgeted_headcount
,TRUNC(l_effective_date1));