[Home] [Help]
PACKAGE BODY: APPS.BIM_BUDGETS_DENORM_PKG
Source
1 PACKAGE BODY BIM_BUDGETs_denorm_pkg AS
2 /*$Header: bimbgtdb.pls 120.4 2005/10/17 07:40:48 sbassi noship $*/
3
4 g_pkg_name CONSTANT VARCHAR2(20) := 'BIM_BUDGETS_DENORM';
5 g_file_name CONSTANT VARCHAR2(20) := 'bimbgtdb.pls';
6
7 PROCEDURE COMMON_UTILITIES
8 ( l_global_start_date OUT NOCOPY DATE
9 ,l_period_from OUT NOCOPY DATE
10 ,l_period_to OUT NOCOPY DATE
11 ,l_temp_start_date OUT NOCOPY DATE
12 ,l_start_date OUT NOCOPY DATE
13 ,l_end_date OUT NOCOPY DATE
14 ) IS
15 l_global_date CONSTANT DATE := bis_common_parameters.get_global_start_date;
16
17 BEGIN
18 l_global_start_date := l_global_date;
19
20 BEGIN
21 /* Set up the Object */
22 IF NOT bis_collection_utilities.setup('BIM_BUDGET_DENORM') THEN
23 bis_collection_utilities.log('Object Not Setup Properly ');
24 END IF;
25
26 bis_collection_utilities.get_last_refresh_dates('BIM_BUDGET_DENORM'
27 ,l_start_date,l_end_date,l_period_from,l_period_to);
28
29 EXCEPTION
30 WHEN NO_DATA_FOUND THEN
31 l_end_date := NULL;
32 bis_collection_utilities.log('First time running the concurrent program ');
33 WHEN OTHERS THEN
34 bis_collection_utilities.log('program '|| sqlerrm(sqlcode));
35 END;
36
37 /* End of the code for checking the data will be loaded for the first time or not. */
38
39 IF l_period_to IS NULL THEN
40 l_temp_start_date := sysdate-5000;
41 ELSE
42 l_temp_start_date := l_period_to;
43 END IF;
44
45 END COMMON_UTILITIES;
46
47 PROCEDURE POPULATE
48 (ERRBUF OUT NOCOPY VARCHAR2
49 ,RETCODE OUT NOCOPY NUMBER
50 ,p_api_version_number IN NUMBER
51 ,p_proc_num IN NUMBER
52 ,p_load_type IN VARCHAR2
53 ) IS
54
55 l_api_version_number CONSTANT NUMBER := 1.0;
56 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE';
57 x_msg_count NUMBER;
58 x_msg_data VARCHAR2(240);
59 x_return_status VARCHAR2(1) ;
60 l_init_msg_list VARCHAR2(10) := FND_API.G_FALSE;
61
62 BEGIN
63
64 -- Standard call to check for call compatibility.
65 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
66 p_api_version_number,
67 l_api_name,
68 g_pkg_name)
69 THEN
70 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
71 END IF;
72
73 POPULATE_DENORM
74 (p_api_version_number => 1.0
75 ,x_msg_Count => x_msg_count
76 ,x_msg_Data => x_msg_data
77 ,x_return_status => x_return_status
78 ,p_proc_num => p_proc_num
79 ,p_load_type => p_load_type
80 );
81
82 IF x_return_status = FND_API.g_ret_sts_error
83 THEN
84 RAISE FND_API.g_exc_error;
85 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
86 RAISE FND_API.g_exc_unexpected_error;
87 END IF;
88
89 IF x_return_status = FND_API.g_ret_sts_error
90 THEN
91 RAISE FND_API.g_exc_error;
92 ELSIF x_return_status = FND_API.g_ret_sts_unexp_error THEN
93 RAISE FND_API.g_exc_unexpected_error;
94 END IF;
95
96 EXCEPTION
97 WHEN FND_API.G_EXC_ERROR THEN
98 x_return_status := FND_API.g_ret_sts_error ;
99 FND_MSG_PUB.count_and_get (p_encoded => FND_API.G_FALSE,
100 p_count => x_msg_count,
101 p_data => x_msg_data);
102 ERRBUF := x_msg_data;
103 RETCODE := 2;
104
105 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
106 x_return_status := FND_API.g_ret_sts_unexp_error ;
107 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
108 p_count => x_msg_count,
109 p_data => x_msg_data);
110 ERRBUF := x_msg_data;
111 RETCODE := 2;
112
113 WHEN OTHERS THEN
114 x_return_status := FND_API.g_ret_sts_unexp_error ;
115 FND_MSG_PUB.Count_And_Get (p_encoded => FND_API.G_FALSE,
116 p_count => x_msg_count,
117 p_data => x_msg_data);
118 ERRBUF := sqlerrm(sqlcode);
119 RETCODE := sqlcode;
120
121 END POPULATE;
122
123 PROCEDURE POPULATE_DENORM
124 (p_api_version_number IN NUMBER
125 ,p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
126 ,p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL
127 ,p_commit IN VARCHAR2 := FND_API.G_FALSE
128 ,x_msg_Count OUT NOCOPY NUMBER
129 ,x_msg_Data OUT NOCOPY VARCHAR2
130 ,x_return_status OUT NOCOPY VARCHAR2
131 ,p_proc_num IN NUMBER
132 ,p_load_type IN VARCHAR2
133 ) IS
134
135 --l_date DATE := bis_common_parameters.get_global_start_date;
136 l_date DATE;
137 l_temp_start_date DATE;
138 l_temp_end_date DATE;
139 l_api_version_number CONSTANT NUMBER := 1.0;
140 l_api_name CONSTANT VARCHAR2(30) := 'POPULATE_DENORM';
141 l_init_msg_list VARCHAR2(10) := FND_API.G_FALSE;
142
143 l_start_date DATE;
144 l_end_date DATE;
145 l_period_from DATE;
146 l_period_to DATE;
147
148 l_status VARCHAR2(5);
149 l_industry VARCHAR2(5);
150 l_schema VARCHAR2(30);
151 l_return BOOLEAN;
152
153 BEGIN
154
155
156 IF p_load_type = 'F' THEN
157
158 --it is a call for First/Inital load, then truncate the denorm table first
159 bis_collection_utilities.log('Truncating the Budget Denorm Table ');
160
161 l_return := fnd_installation.get_app_info('BIM', l_status, l_industry, l_schema);
162
163 Execute Immediate 'Truncate Table '||l_schema||'.bim_i_budgets_denorm';
164
165 BIS_COLLECTION_UTILITIES.deleteLogForObject('BIM_BUDGET_DENORM');
166
167 ELSE
168 /* This piece of code is for the budgets that have changed from one parent to another. */
169 DELETE bim_i_budgets_denorm
170 WHERE object_id IN
171 (SELECT fund_id
172 FROM ozf_funds_all_b a
173 WHERE last_update_date > l_temp_start_date
174 AND NOT EXISTS
175 (SELECT 1
176 FROM bim_i_budgets_denorm b
177 WHERE b.object_id = a.fund_id
178 AND b.object_type='BUDGET'
179 AND b.parent_object_id = a.parent_fund_id)
180 );
181
182 DELETE bim_i_budgets_denorm
183 WHERE object_id IN
184 (SELECT category_id
185 FROM ams_categories_b a
186 WHERE arc_category_created_for='FUND'
187 AND last_update_date > l_temp_start_date
188 AND NOT EXISTS
189 (SELECT 1
190 FROM bim_i_budgets_denorm b
191 WHERE b.object_id = a.category_id
192 AND b.object_type='CATEGORY'
193 AND b.parent_object_id = a.parent_category_id)
194 );
195 END IF;
196
197
198 COMMON_UTILITIES
199 ( l_date
200 ,l_period_from
201 ,l_period_to
202 ,l_temp_start_date
203 ,l_start_date
204 ,l_end_date
205 );
206
207 INSERT INTO bim_i_budgets_denorm
208 (object_id
209 ,child_denorm_id
210 ,object_type
211 ,child_denorm_type
212 ,object_level
213 ,immediate_child_flag
214 ,parent_object_id
215 ,creation_date
216 ,last_update_date
217 ,created_by
218 ,last_updated_by
219 ,last_update_login
220 ,object_sub_type
221 ,object_sub_cat
222 ,leaf_node_flag)
223 SELECT
224 fund_id object_id
225 ,TO_NUMBER(NVL(SUBSTR(SYS_CONNECT_BY_PATH(fund_id,'/'),2,
226 INSTR(SYS_CONNECT_BY_PATH(fund_id,'/'),'/',2) -2),fund_id)) AS child_denorm_id
227 ,'BUDGET'
228 ,'BUDGET'
229 ,LEVEL
230 ,decode(level,2,'Y',decode(parent_fund_id,NULL,'N','Y')) immediate_child_flag
231 ,parent_fund_id
232 ,sysdate
233 ,sysdate
234 ,-1
235 ,-1
236 ,-1
237 ,fund_type
238 ,category_id
239 ,'N'
240 FROM ozf_funds_all_b a
241 WHERE
242 NOT EXISTS
243 (SELECT 1
244 FROM bim_i_budgets_denorm b
245 WHERE b.object_id = a.fund_id
246 AND b.object_type ='BUDGET'
247 AND nvl(b.parent_object_id,1) = nvl(a.parent_fund_id,1))
248 CONNECT BY PRIOR parent_fund_id = fund_id
249 UNION ALL
250 SELECT category_id
251 ,TO_NUMBER(NVL(SUBSTR(SYS_CONNECT_BY_PATH(category_id,'/'),2,
252 INSTR(SYS_CONNECT_BY_PATH(category_id,'/'),'/',2) -2),category_id)) AS child_denorm_id
253 ,'CATEGORY'
254 ,'CATEGORY'
255 ,LEVEL
256 ,decode(level,2,'Y',decode(parent_category_id,NULL,'N','Y')) immediate_child_flag
257 ,parent_category_id
258 ,sysdate
259 ,sysdate
260 ,-1
261 ,-1
262 ,-1
263 ,'CATEGORY'
264 ,0
265 ,'N'
266 FROM ams_categories_b a
267 WHERE arc_category_created_for='FUND'
268 AND NOT EXISTS
269 (SELECT 1
270 FROM bim_i_budgets_denorm b
271 WHERE b.object_id = a.category_id
272 AND b.object_type ='CATEGORY'
273 AND nvl(b.parent_object_id,1) = nvl(a.parent_category_id,1))
274 connect by prior parent_category_id = category_id ;
275 commit;
276 update bim_i_budgets_denorm bd
277 set leaf_node_flag='Y'
278 where (object_id,object_type) in(
279 select object_id, object_type from bim_i_budgets_denorm a
280 where child_denorm_id =object_id
281 and not exists (select 1
282 from bim_i_budgets_denorm b
283 where a.object_id = b.parent_object_id
284 and a.object_type=b.object_type
285 ));
286 COMMIT;
287 bis_collection_utilities.log('Budget Denorm Concurrent Program Completed Succesfully ');
288 bis_collection_utilities.wrapup(p_status => TRUE
289 ,p_count => sql%rowcount
290 ,p_period_from => l_temp_start_date
291 ,p_period_to => sysdate
292 );
293 EXCEPTION
294 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
295 ROLLBACK TO LOAD_ADMIN_RECORDS;
296 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
297 -- Standard call to get message count and if count=1, get the message
298 FND_MSG_PUB.Count_And_Get (
299 p_encoded => FND_API.G_FALSE,
300 p_count => x_msg_count,
301 p_data => x_msg_data
302 );
303 WHEN FND_API.g_exc_error THEN
304 x_return_status := FND_API.g_ret_sts_error;
305 FND_MSG_PUB.count_and_get(
306 p_encoded => FND_API.g_false,
307 p_count => x_msg_count,
308 p_data => x_msg_data
309 );
310 WHEN OTHERS THEN
311 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
312 IF FND_MSG_PUB.Check_Msg_Level ( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
313 THEN
314 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME,l_api_name);
315 END IF;
316 -- Standard call to get message count and if count=1, get the message
317 FND_MSG_PUB.Count_And_Get (
318 p_encoded => FND_API.G_FALSE,
319 p_count => x_msg_count,
320 p_data => x_msg_data
321 );
322 END POPULATE_DENORM;
323 END BIM_BUDGETS_DENORM_PKG;