[Home] [Help]
PACKAGE BODY: APPS.PSB_LD_POS_DEFAULTS_PVT
Source
1 PACKAGE BODY PSB_LD_POS_DEFAULTS_PVT AS
2 /* $Header: PSBVLDRB.pls 120.2 2004/12/08 06:30:36 maniskum ship $ */
3
4 --
5 -- Global Variables
6
7 g_pkg_name CONSTANT VARCHAR2(30):= 'PSB_LD_POS_DEFAULTS_PVT';
8 g_debug VARCHAR2(2000);
9
10 /*----------------------- Table HANDler Procedures ----------------------- */
11
12 PROCEDURE Create_LD_Default_Assignments
13 ( p_api_version IN NUMBER,
14 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
15 p_commit IN VARCHAR2 := FND_API.G_FALSE,
16 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
17 p_return_status OUT NOCOPY VARCHAR2,
18 p_msg_count OUT NOCOPY NUMBER,
19 p_msg_data OUT NOCOPY VARCHAR2,
20 p_worksheet_id IN NUMBER := FND_API.G_MISS_NUM,
21 p_data_extract_id IN NUMBER
22
23 ) IS
24
25 l_api_name CONSTANT VARCHAR2(30) := 'Create_Default_LD_Assignments';
26 l_api_version CONSTANT NUMBER := 1.0;
27 --
28 l_position_id NUMBER;
29 l_position_start_date DATE;
30 l_position_end_date DATE;
31 l_distr_start_date DATE;
32 l_distr_end_date DATE;
33 l_worksheet_id NUMBER;
34 l_default_rule_id NUMBER;
35 l_distribution_id NUMBER;
36 l_percentage NUMBER := 0;
37 l_distribution_percentage NUMBER := 0;
38 l_count NUMBER;
39 --
40 l_rowid VARCHAR2(100);
41 l_msg_data VARCHAR2(2000);
42 l_msg_count NUMBER;
43 l_return_status VARCHAR2(1);
44 --
45
46 CURSOR l_positions_csr is
47 SELECT position_id,
48 business_group_id,
49 vacant_position_flag,
50 effective_start_date,
51 effective_end_date
52 FROM PSB_POSITIONS
53 WHERE data_extract_id = p_data_extract_id
54 AND (vacant_position_flag is NULL
55 or vacant_position_flag = 'N');
56
57 CURSOR l_dates_csr is
58 SELECT effective_start_date,
59 effective_END_date
60 FROM PSB_POSITIONS
61 WHERE position_id = l_position_id;
62
63
64 --to check what rules were already applied AND the total distribution
65 --percentage is 100 or less
66
67 CURSOR l_dist_csr is
68 SELECT distribution_id,
69 position_id,
70 data_extract_id,
71 effective_start_date,
72 effective_END_date,
73 chart_of_accounts_id,
74 code_combination_id,
75 distribution_percent,
76 distribution_default_rule_id
77 FROM PSB_POSITION_PAY_DISTRIBUTIONS
78 WHERE data_extract_id = p_data_extract_id
79 AND (((l_position_END_date is not NULL)
80 AND (((effective_start_date <= l_position_end_date)
81 AND (effective_END_date is NULL))
82 OR ((effective_start_date between l_position_start_date
83 AND l_position_END_date)
84 OR (effective_END_date between l_position_start_date
85 AND l_position_END_date)
86 OR ((effective_start_date < l_position_start_date)
87 AND (effective_END_date > l_position_END_date)))))
88 OR ((l_position_END_date is NULL)
89 AND (nvl(effective_END_date, l_position_start_date)
90 >= l_position_start_date)))
91 AND position_id = l_position_id
92 AND code_combination_id IS NOT NULL;
93
94 -- pick the effective start date and end date for the local default rule which
95 -- will be applied
96 CURSOR l_eff_dates_csr is
97 SELECT effective_start_date, effective_end_date
98 FROM PSB_POSITION_PAY_DISTRIBUTIONS
99 WHERE position_id=l_position_id
100 AND project_id IS NOT NULL
101 AND code_combination_id IS NULL;
102
103 --pick local default rule to apply LD salary distribution
104 CURSOR l_priority_csr is
105 SELECT a.default_rule_id,
106 a.priority
107 FROM PSB_DEFAULTS a,
108 PSB_SET_RELATIONS b,
109 PSB_BUDGET_POSITIONS c
110 WHERE exists
111 (SELECT 1
112 FROM PSB_DEFAULT_ACCOUNT_DISTRS d
113 WHERE d.default_rule_id = a.default_rule_id)
114 AND a.priority is not NULL
115 AND (a.global_default_flag = 'N'
116 or a.global_default_flag IS NULL)
117 AND a.default_rule_id = b.default_rule_id
118 AND b.account_position_set_id = c.account_position_set_id
119 AND c.data_extract_id = p_data_extract_id
120 AND c.position_id = l_position_id
121 ORDER BY a.priority;
122
123 CURSOR l_ld_dist_csr is
124 SELECT chart_of_accounts_id,
125 code_combination_id,
126 distribution_percent
127 FROM PSB_DEFAULT_ACCOUNT_DISTRS
128 WHERE default_rule_id = l_default_rule_id
129 AND code_combination_id IS NOT NULL;
130
131
132 BEGIN
133
134 -- Standard call to check FOR call compatibility.
135
136 IF NOT FND_API.Compatible_API_Call (l_api_version,
137 p_api_version,
138 l_api_name,
139 G_PKG_NAME)
140 THEN
141 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
142 END IF;
143
144 SAVEPOINT LD_Default_Assignments;
145
146 FOR l_positions_rec IN l_positions_csr
147 LOOP
148
149 --check what positions have pay distribution percentage less than 100
150 l_position_id := l_positions_rec.position_id;
151
152 FOR l_dates_rec in l_dates_csr
153 LOOP
154 l_position_start_date := l_dates_rec.effective_start_date;
155 l_position_end_date := l_dates_rec.effective_end_date;
156 END LOOP;--end of l_dates_rec
157
158 l_distribution_percentage := 0;
159 l_percentage := 0;
160
161 --effective dates for the distribution
162
163 l_distr_start_date := null;
164 l_distr_end_date := null;
165
166 FOR l_eff_dates_rec in l_eff_dates_csr
167 LOOP
168 l_distr_start_date := l_eff_dates_rec.effective_start_date;
169 l_distr_end_date := l_eff_dates_rec.effective_end_date;
170 END LOOP;--end of l_eff_dates csr
171
172 FOR l_dist_rec in l_dist_csr
173 LOOP
174
175 l_distribution_percentage :=
176 l_dist_rec.distribution_percent+l_distribution_percentage;
177
178 if (l_distr_start_date is null) then
179 l_distr_start_date := l_dist_rec.effective_start_date;
180 l_distr_end_date := l_dist_rec.effective_end_date;
181 end if;
182
183 END LOOP;--end of l_dist_rec
184
185 if (l_distr_start_date is null) then
186 l_distr_start_date := l_position_start_date;
187 l_distr_end_date := l_position_end_date;
188 end if;
189
190 IF l_distribution_percentage <100 THEN
191 l_percentage := 100-l_distribution_percentage;
192
193 BEGIN
194
195 IF p_worksheet_id = FND_API.G_MISS_NUM THEN
196 l_worksheet_id := NULL;
197 ELSE
198 l_worksheet_id := p_worksheet_id;
199 END IF;
200
201 FOR l_priority_rec in l_priority_csr
202 LOOP
203
204 --The default rule with least priority is applied FOR
205 --remaining percentage of salary distribution
206 --initializing the count;
207
208 l_count := 1;
209
210 IF l_count = 1 THEN
211
212 l_default_rule_id := l_priority_rec.default_rule_id;
213
214
215 FOR l_ld_dist_rec in l_ld_dist_csr
216 LOOP
217
218 --Apply this default rule FOR only the distribution percent which
219 --is not accounted FOR
220
221 l_distribution_percentage :=
222 l_percentage *0.01*l_ld_dist_rec.distribution_percent;
223 PSB_POSITION_PAY_DISTR_PVT.Modify_Distribution
224 (p_api_version => 1.0,
225 p_init_msg_list => FND_API.G_FALSE,
226 p_commit => FND_API.G_FALSE,
227 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
228 p_return_status => l_return_status,
229 p_msg_count => l_msg_count,
230 p_msg_data => l_msg_data,
231 p_distribution_id => l_distribution_id,
232 p_position_id => l_position_id,
233 p_data_extract_id => p_data_extract_id,
234 p_effective_start_date => l_distr_start_date,
235 p_effective_end_date => l_distr_end_date,
236 p_chart_of_accounts_id => l_ld_dist_rec.chart_of_accounts_id,
237 p_code_combination_id => l_ld_dist_rec.code_combination_id,
238 p_distribution_percent => l_distribution_percentage,
239 p_global_default_flag => 'N',
240 p_distribution_default_rule_id => l_default_rule_id,
241 p_rowid => l_rowid,
242 p_mode => 'R');
243
244 IF l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
245 RAISE FND_API.G_EXC_ERROR;
246 END IF;
247
248 END LOOP;--end of l_ld_dist_rec
249
250 l_count := l_count+1;
251
252 END IF;
253 END LOOP;--end of l_priority_rec
254
255 END;
256
257 END IF; --end of check for percentage
258
259 END LOOP; --end of l_positions_rec
260
261 -- Initialize API return status to success
262
263 p_return_status := FND_API.G_RET_STS_SUCCESS;
264
265 -- StANDard check of p_commit.
266
267 IF FND_API.to_Boolean (p_commit) THEN
268 commit work;
269 END IF;
270
271 -- StANDard call to get message count AND IF count is 1, get message info.
272
273 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
274 p_data => p_msg_data);
275
276
277 EXCEPTION
278
279 WHEN FND_API.G_EXC_ERROR THEN
280
281 ROLLBACK to LD_Default_Assignments;
282
283 p_return_status := FND_API.G_RET_STS_ERROR;
284
285 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
286 p_data => p_msg_data);
287
288 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
289
290 ROLLBACK to LD_Default_Assignments;
291
292 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
293
294 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
295 p_data => p_msg_data);
296
297 WHEN OTHERS THEN
298
299 ROLLBACK to LD_Default_Assignments;
300
301 p_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
302
303 IF FND_MSG_PUB.Check_Msg_Level
304 (p_message_level => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
305 THEN FND_MSG_PUB.Add_Exc_Msg
306 (p_pkg_name => G_PKG_NAME,p_procedure_name => l_api_name);
307 END IF;
308
309 FND_MSG_PUB.Count_And_Get (p_count => p_msg_count,
310 p_data => p_msg_data);
311
312 END Create_LD_Default_Assignments;
313
314
315 /*===========================================================================+
316 | PROCEDURE Assign_LD_Pos_Defaults_CP |
317 +===========================================================================*/
318 --
319 -- This is the execution file for the concurrent program 'Assign LD Position
320 -- Defaults'
321 --
322 PROCEDURE Assign_LD_Pos_Defaults_CP
323 (
324 errbuf OUT NOCOPY VARCHAR2 ,
325 retcode OUT NOCOPY VARCHAR2 ,
326 p_data_extract_id IN NUMBER
327 )
328 IS
329 --
330 l_api_name CONSTANT VARCHAR2(30) := 'Assign_LD_Pos_Defaults_CP';
331 l_api_version CONSTANT NUMBER := 1.0 ;
332 --
333 l_data_extract_name VARCHAR2(30);
334 l_error_api_name VARCHAR2(2000);
335 l_return_status VARCHAR2(1) ;
336 l_msg_count NUMBER ;
337 l_msg_data VARCHAR2(2000) ;
338 l_msg_index_out NUMBER;
339
340 BEGIN
341
342 Select data_extract_name
343 into l_data_extract_name
344 from psb_data_extracts
345 where data_extract_id = p_data_extract_id;
346
347 FND_FILE.Put_Line( FND_FILE.OUTPUT,
348 'Assigning position defaults for data extract id : ' ||
349 p_data_extract_id );
350
351 PSB_BUDGET_POSITION_PVT.Populate_Budget_Positions
352 (p_api_version => 1.0,
353 p_commit => FND_API.G_TRUE,
354 p_return_status => l_return_status,
355 p_msg_count => l_msg_count,
356 p_msg_data => l_msg_data,
357 p_data_extract_id => p_data_extract_id);
358
359 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
360 raise FND_API.G_EXC_ERROR;
361 end if;
362
363 PSB_CONCURRENCY_CONTROL_PUB.Enforce_Concurrency_Control
364 (p_api_version => 1.0,
365 p_return_status => l_return_status,
366 p_msg_count => l_msg_count,
367 p_msg_data => l_msg_data,
368 p_concurrency_class => 'MAINTENANCE',
369 p_concurrency_entity_name => 'DATA_EXTRACT',
370 p_concurrency_entity_id => p_data_extract_id);
371
372 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
373 raise FND_API.G_EXC_ERROR;
374 end if;
375
376 PSB_LD_POS_DEFAULTS_PVT.Create_LD_Default_Assignments(
377 p_api_version => 1.0,
378 p_init_msg_list => FND_API.G_TRUE,
379 p_commit => FND_API.G_TRUE,
380 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
381 p_return_status => l_return_status,
382 p_msg_count => l_msg_count,
383 p_msg_data => l_msg_data,
384 p_data_extract_id => p_data_extract_id) ;
385
386 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
387 raise FND_API.G_EXC_ERROR;
388 END IF;
389 --
390
391 -- This is the execution file for the concurrent program 'Release Concurrency
392 -- Control '
393
394 PSB_CONCURRENCY_CONTROL_PUB.Release_Concurrency_Control
395 (p_api_version => 1.0,
396 p_return_status => l_return_status,
397 p_msg_count => l_msg_count,
398 p_msg_data => l_msg_data,
399 p_concurrency_class => 'MAINTENANCE',
400 p_concurrency_entity_name => 'DATA_EXTRACT',
401 p_concurrency_entity_id => p_data_extract_id);
402
403 if l_return_status <> FND_API.G_RET_STS_SUCCESS THEN
404 raise FND_API.G_EXC_ERROR;
405 end if;
406 /* Start Bug No. 2322856 */
407 -- PSB_MESSAGE_S.Print_Success;
408 /* End Bug No. 2322856 */
409 retcode := 0 ;
410 --
411 COMMIT WORK;
412
413 EXCEPTION
414
415 WHEN FND_API.G_EXC_ERROR THEN
416 --
417 PSB_CONCURRENCY_CONTROL_PUB.Release_Concurrency_Control
418 (p_api_version => 1.0,
419 p_return_status => l_return_status,
420 p_msg_count => l_msg_count,
421 p_msg_data => l_msg_data,
422 p_concurrency_class => 'MAINTENANCE',
423 p_concurrency_entity_name => 'DATA_EXTRACT',
424 p_concurrency_entity_id => p_data_extract_id);
425
426 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
427 p_print_header => FND_API.G_TRUE );
428 retcode := 2 ;
429 COMMIT WORK ;
430 --
431 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
432 --
433 PSB_CONCURRENCY_CONTROL_PUB.Release_Concurrency_Control
434 (p_api_version => 1.0,
435 p_return_status => l_return_status,
436 p_msg_count => l_msg_count,
437 p_msg_data => l_msg_data,
438 p_concurrency_class => 'MAINTENANCE',
439 p_concurrency_entity_name => 'DATA_EXTRACT',
440 p_concurrency_entity_id => p_data_extract_id);
441
442 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
443 p_print_header => FND_API.G_TRUE );
444 retcode := 2 ;
445 COMMIT WORK ;
446
447 WHEN OTHERS THEN
448
449 PSB_CONCURRENCY_CONTROL_PUB.Release_Concurrency_Control
450 (p_api_version => 1.0,
451 p_return_status => l_return_status,
452 p_msg_count => l_msg_count,
453 p_msg_data => l_msg_data,
454 p_concurrency_class => 'MAINTENANCE',
455 p_concurrency_entity_name => 'DATA_EXTRACT',
456 p_concurrency_entity_id => p_data_extract_id);
457
458 --
459 IF FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR ) THEN
460 --
461 FND_MSG_PUB.Add_Exc_Msg( G_PKG_NAME ,
462 l_api_name ) ;
463 END IF ;
464 --
465 PSB_MESSAGE_S.Print_Error ( p_mode => FND_FILE.LOG ,
466 p_print_header => FND_API.G_TRUE );
467 --
468 retcode := 2 ;
469 COMMIT WORK ;
470 --
471 END Assign_LD_Pos_Defaults_CP;
472
473 /* ----------------------------------------------------------------------- */
474
475 -- Get Debug Information
476
477 -- This Module is used to retrieve Debug Information FOR Funds Checker. It
478 -- prints Debug InFORmation WHEN run as a Batch Process FROM SQL*Plus. For
479 -- the Debug InFORmation to be printed on the Screen, the SQL*Plus parameter
480 -- 'Serveroutput' should be set to 'ON'
481
482 FUNCTION get_debug RETURN VARCHAR2 IS
483
484 BEGIN
485
486 return(g_debug);
487
488 END get_debug;
489
490
491 END PSB_LD_POS_DEFAULTS_PVT;