[Home] [Help]
PACKAGE BODY: APPS.HRI_DBI_WMV_BUDGET
Source
1 PACKAGE BODY hri_dbi_wmv_budget AS
2 /* $Header: hribdgco.pkb 120.0 2005/05/29 07:01:54 appldev noship $ */
3 --
4 --**********************************************
5 --* Define global constants *
6 --**********************************************
7 --
8 g_con_context_type CONSTANT VARCHAR2(30) := 'SUPERVISOR';
9 g_con_info_category CONSTANT VARCHAR2(30) := 'HRI_DBI_WMV_BUDGET';
10 g_object_name CONSTANT VARCHAR2(30) := 'HRI_DBI_WMV_BUDGET';
11 --
12 --***********************************************
13 --* Define global parameters *
14 --***********************************************
15 --
16 g_abv_type VARCHAR2(30);
17 --
18 --**********************************************************************************
19 --* Calculate events between p_effective_start_date and p_effective_end_date*
20 --**********************************************************************************
21 --
22 PROCEDURE calc_events ( p_effective_start_date IN DATE
23 ,p_effective_end_date IN DATE) IS
24 BEGIN
25 INSERT INTO hri_dbi_wmv_budget_evts_tmp
26 ( supervisor_id
27 ,effective_start_date
28 ,effective_end_date
29 ,budget_version_id
30 ,budgeted_count)
31 SELECT DISTINCT org.org_information2, b.version_date_from, b.version_date_to,
32 b.budget_version_id,0
33 FROM hr_organization_information org,
34 hri_mb_budget_v b
35 WHERE org.org_information_context = 'Organization Name Alias'
36 AND org.org_information2 IS NOT NULL
37 AND org.organization_id = b.organization_id
38 AND b.position_control_flag_code = 'Y'
39 AND b.version_date_from BETWEEN p_effective_start_date
40 AND p_effective_end_date;
41 COMMIT;
42 --
43 bis_collection_utilities.log('Events Population Done',1);
44 --
45 END calc_events;
46 --
47 --*******************************
48 --* Calculate budgeted headcount*
49 --*******************************
50 --
51 PROCEDURE get_budgeted_headcount IS
52
53 BEGIN
54 UPDATE hri_dbi_wmv_budget_evts_tmp hrp
55 SET hrp.budgeted_count = (SELECT sum(decode(g_abv_type,b.budget_unit1_system_type_cd, b.budget_unit1_value,
56 b.budget_unit2_system_type_cd, b.budget_unit2_value,
57 b.budget_unit1_system_type_cd,b.budget_unit3_value ))
58 FROM HRI_MB_BUDGET_V b,
59 HR_ORGANIZATION_INFORMATION org
60 WHERE b.position_control_flag_code = 'Y'
61 AND b.organization_id = org.organization_id
62 AND org.org_information_context = 'Organization Name Alias'
63 AND org.org_information2 = hrp.supervisor_id
64 AND b.version_date_from = hrp.effective_start_date
65 AND b.budget_version_id = hrp.budget_version_id);
66
67 COMMIT;
68 --
69 bis_collection_utilities.log('Budgeted Headcount Calculation Done',1);
70 --
71 END get_budgeted_headcount;
72 --
73 --*******************************
74 --* Compare Dates *
75 --*******************************
76 --
77 FUNCTION comp_date(p_effective_start_date IN DATE,p_effective_end_date IN DATE) RETURN VARCHAR2 IS
78
79 ret_val VARCHAR2(10);
80 BEGIN
81 IF p_effective_start_date > p_effective_end_date then
82 ret_val := 'Y';
83 ELSE
84 ret_val := 'N';
85 END IF;
86 return ret_val;
87 END comp_date;
88
89 --
90 -- ***********************************************************************
91 -- * Fully refresh all summary data for the budgeted headcount *
92 -- * within the specified time period *
93 -- ***********************************************************************
94 --
95 PROCEDURE full_refresh( errbuf OUT NOCOPY VARCHAR2
96 ,retcode OUT NOCOPY NUMBER
97 ,p_effective_start_date IN VARCHAR2
98 ,p_effective_end_date IN VARCHAR2) IS
99
100 l_supervisor_id NUMBER(32);
101 l_location_id NUMBER(32);
102 l_budgeted_headcount NUMBER;
103 l_effective_date DATE;
104 l_effective_date1 DATE;
105 l_effective_start_date DATE;
106 l_effective_end_date DATE;
107
108 CURSOR c_events IS
109 SELECT hrp.supervisor_id, hrp.effective_start_date, hrp.effective_end_date,
110 hrp.budgeted_count
111 FROM hri_dbi_wmv_budget_evts_tmp hrp;
112 BEGIN
113
114 bis_collection_utilities.log('********************************',1);
115 bis_collection_utilities.log('*HRI_DBI_WMV_BUDGET.FULL_REFRESH*',1);
116 bis_collection_utilities.log('********************************',1);
117
118 DELETE
119 FROM hr_ptl_summary_data psum
120 WHERE psum.sum_information_category = 'HRI_DBI_WMV_BUDGET'
121 AND psum.summary_context_type = 'SUPERVISOR';
122 COMMIT;
123 --
124 bis_collection_utilities.log('Delete records from the HR_PORTAL_SUMMARY_DATA table',1);
125 --
126 --set dates
127 l_effective_start_date := trunc(fnd_date.canonical_to_date(p_effective_start_date));
128 l_effective_end_date := trunc(fnd_date.canonical_to_date(p_effective_end_date));
129
130 --
131 bis_collection_utilities.log('Effective Start Date : '||TO_CHAR(l_effective_start_date),1);
132 bis_collection_utilities.log('Effective End Date : '||TO_CHAR(l_effective_end_date),1);
133 --
134 --set global parameters
135 --
136 g_abv_type := fnd_profile.value('BIS_WORKFORCE_MEASUREMENT_TYPE');
137 --
138 bis_collection_utilities.log('Workforce Measurement Value: '||g_abv_type,1);
139 --
140 --events population
141 hri_dbi_wmv_budget.calc_events(l_effective_start_date,l_effective_end_date);
142 --
143 --calculate budgeted headcount
144 hri_dbi_wmv_budget.get_budgeted_headcount;
145
146 OPEN c_events;
147 LOOP
148 FETCH c_events INTO l_supervisor_id, l_effective_date, l_effective_date1, l_budgeted_headcount;
149 EXIT WHEN c_events%NOTFOUND;
150
151 l_location_id := null;
152
153 INSERT INTO hr_ptl_summary_data
154 ( summary_data_id
155 ,summary_context_type
156 ,summary_context_id
157 ,effective_date
158 ,created_by
159 ,creation_date
160 ,object_version_number
161 ,sum_information_category
162 ,sum_information1
163 ,sum_information2
164 ,effective_end_date)
165 VALUES ( hr_ptl_summary_data_s.NEXTVAL
166 ,g_con_context_type
167 ,l_supervisor_id
168 ,l_effective_date
169 ,fnd_global.user_id
170 ,TRUNC(SYSDATE)
171 ,1
172 ,g_con_info_category
173 ,l_location_id
174 ,l_budgeted_headcount
175 ,TRUNC(l_effective_date1));
176 END LOOP;
177 CLOSE c_events;
178 COMMIT;
179 --
180 bis_collection_utilities.log('Full Refresh Complete',1);
181 --
182 EXCEPTION
183 WHEN OTHERS THEN
184 errbuf := SQLERRM;
185 retcode := SQLCODE;
186 END full_refresh;
187 --
188 -- **********************************************************************
189 -- * Refresh the summary data for the employee budgeted count *
190 -- **********************************************************************
191 --
192 PROCEDURE refresh_from_deltas ( errbuf OUT NOCOPY VARCHAR2
193 ,retcode OUT NOCOPY NUMBER ) IS
194
195 l_effective_from_date VARCHAR2(25);
196 l_effective_to_date VARCHAR2(25);
197
198 BEGIN
199 l_effective_from_date := fnd_date.date_to_canonical(TRUNC(bis_common_parameters.get_global_start_date));
200 l_effective_to_date := fnd_date.date_to_canonical(TRUNC(sysdate));
201
202 hri_dbi_wmv_budget.full_refresh(errbuf,retcode,l_effective_from_date,l_effective_to_date);
203
204 END refresh_from_deltas;
205 --
206 -- **********************************************************************
207 -- * Refresh the materialized view for the employee budgeted count *
208 -- **********************************************************************
209 --
210 PROCEDURE refresh_mvs( errbuf OUT NOCOPY VARCHAR2
211 ,retcode OUT NOCOPY NUMBER) IS
212 BEGIN
213 --
214 bis_collection_utilities.log('****************************',1);
215 bis_collection_utilities.log('*Refresh Materialized Views*',1);
216 bis_collection_utilities.log('****************************',1);
217 --
218 dbms_mview.refresh('HRI_DBI_WMV_BUDGET_MV','C');
219 --
220 bis_collection_utilities.log('HRI_DBI_WMV_BUDGET_MV Materialized View refreshed',1);
221 --
222 fnd_stats.gather_table_stats('APPS','HRI_DBI_WMV_BUDGET_MV');
223 --
224 bis_collection_utilities.log('HRI_DBI_WMV_BUDGET_MV view statastics gathered',1);
225 --
226 EXCEPTION
227 WHEN OTHERS THEN
228 errbuf := SQLERRM;
229 retcode := SQLCODE;
230 END refresh_mvs;
231 END hri_dbi_wmv_budget;