[Home] [Help]
PACKAGE BODY: APPS.PA_FP_PERIOD_MASKS_UTILS
Source
1 PACKAGE body PA_FP_PERIOD_MASKS_UTILS as
2 /* $Header: PAFPPMUB.pls 120.0 2005/06/03 13:47:08 appldev noship $ */
3 /*********************************************************************
4 Important : The appropriate procedures that make a call to the below
5 procedures must make a call to FND_MSG_PUB.initialize.
6 **********************************************************************/
7 P_PA_DEBUG_MODE varchar2(1) := NVL(FND_PROFILE.value('PA_DEBUG_MODE'), 'N');
8
9 PROCEDURE NAME_VALIDATION
10 (p_name IN pa_period_masks_tl.name%TYPE,
11 p_period_mask_id IN pa_period_masks_b.period_mask_id%TYPE,
12 p_init_msg_flag IN VARCHAR2,
13 x_return_status OUT NOCOPY VARCHAR2,
14 x_msg_count OUT NOCOPY NUMBER,
15 x_msg_data OUT NOCOPY VARCHAR2) IS
16 l_module_name VARCHAR2(100) := 'pa.plsql.PA_FP_PERIOD_MASKS_UTILS.NAME_VALIDATION';
17 l_exists VARCHAR2(1);
18 l_msg_count NUMBER;
19 l_msg_index_out NUMBER;
20 l_data VARCHAR2(2000);
21 l_msg_data VARCHAR2(2000);
22 l_name_exists boolean:=false;
23 BEGIN
24 IF P_PA_DEBUG_MODE = 'Y' THEN
25 pa_debug.set_curr_function( p_function => 'NAME_VALIDATION',
26 p_debug_mode => p_pa_debug_mode );
27 END IF;
28
29 x_return_status := FND_API.G_RET_STS_SUCCESS;
30 IF p_init_msg_flag = 'Y' THEN
31 FND_MSG_PUB.Initialize;
32 END IF;
33
34 x_msg_count := 0;
35 BEGIN
36 SELECT 'Y'
37 INTO l_exists
38 FROM pa_period_masks_tl
39 WHERE UPPER(name) = UPPER(p_name)
40 AND p_period_mask_id <> period_mask_id
41 AND ROWNUM < 2;
42 --DBMS_OUTPUT.PUT_LINE(p_name || ' Already Exists');
43 --Duplicate Name should not be entered
44 x_return_status := FND_API.G_RET_STS_ERROR;
45 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
46 p_msg_name => 'PA_ALL_UNIQUE_NAME_EXISTS');
47 l_name_exists:=TRUE;
48 EXCEPTION
49 WHEN NO_DATA_FOUND THEN
50 --DBMS_OUTPUT.PUT_LINE(p_name || ' not duplicated');
51 NULL;
52 END;
53 l_msg_count := FND_MSG_PUB.count_msg;
54 IF l_msg_count > 0 THEN
55 IF l_msg_count = 1 THEN
56 PA_INTERFACE_UTILS_PUB.get_messages
57 (p_encoded => FND_API.G_TRUE,
58 p_msg_index => 1,
59 p_msg_count => l_msg_count,
60 p_msg_data => l_msg_data,
61 p_data => l_data,
62 p_msg_index_out => l_msg_index_out);
63 x_msg_data := l_data;
64 x_msg_count := l_msg_count;
65 ELSE
66 x_msg_count := l_msg_count;
67 END IF;
68 END IF;
69 IF P_PA_DEBUG_MODE = 'Y' THEN
70 PA_DEBUG.RESET_CURR_FUNCTION;
71 END IF;
72
73 RETURN;
74 EXCEPTION
75 WHEN OTHERS THEN
76 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
77 x_msg_count := 1;
78 x_msg_data := SQLERRM;
79 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_PERIOD_MASKS_UTILS',
80 p_procedure_name => 'name_validation');
81 IF P_PA_DEBUG_MODE = 'Y' THEN
82 pa_fp_gen_amount_utils.fp_debug
83 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
84 p_module_name => l_module_name,
85 p_log_level => 5);
86 PA_DEBUG.RESET_CURR_FUNCTION;
87 END IF;
88
89 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
90 -- DBMS_OUTPUT.PUT_LINE('ERROR MSG FROM WHEN OTHER EXCEPTION');
91 END NAME_VALIDATION;
92
93
94 PROCEDURE NOP_VALIDATION
95 (p_num_of_periods IN PA_NUM_1000_NUM,
96 p_init_msg_flag IN VARCHAR2,
97 p_error_flag_tab IN OUT NOCOPY PA_VC_1000_150,
98 x_return_status OUT NOCOPY VARCHAR2,
99 x_msg_count OUT NOCOPY NUMBER,
100 x_msg_data OUT NOCOPY VARCHAR2) IS
101 l_module_name VARCHAR2(100) := 'pa.plsql.PA_FP_PERIOD_MASKS_UTILS.NOP_VALIDATION';
102 l_num_of_periods PA_NUM_1000_NUM;
103 l_invalid_num_exists BOOLEAN := FALSE;
104 l_msg_count NUMBER;
105 l_msg_index_out NUMBER;
106 l_data VARCHAR2(2000);
107 l_msg_data VARCHAR2(2000);
108 l_err_msg_flag VARCHAR2(1):='Y';
109 BEGIN
110 IF P_PA_DEBUG_MODE = 'Y' THEN
111 pa_debug.set_curr_function( p_function => 'NOP_VALIDATION',
112 p_debug_mode => p_pa_debug_mode );
113 END IF;
114 x_return_status := FND_API.G_RET_STS_SUCCESS;
115 IF p_init_msg_flag = 'Y' THEN
116 FND_MSG_PUB.Initialize;
117 END IF;
118 x_msg_count := 0;
119 /* SELECT num_of_periods
120 BULK COLLECT INTO l_num_of_periods
121 FROM pa_fp_period_mask_tmp; */
122 if p_num_of_periods.count = 0 then
123 x_return_status := FND_API.G_RET_STS_ERROR;
124 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
125 p_msg_name => 'PA_FP_NO_PM_DTLS');
126 IF P_PA_DEBUG_MODE = 'Y' THEN
127 PA_DEBUG.RESET_CURR_FUNCTION;
128 END IF;
129 RETURN;
130 end if;
131 l_num_of_periods := p_num_of_periods;
132 FOR j in l_num_of_periods.FIRST..l_num_of_periods.LAST LOOP
133 --Setting default value before starting each iteration
134 IF (l_num_of_periods(j) < 0) OR (l_num_of_periods(j) > 1000) OR
135 ((l_num_of_periods(j) > 0) and (l_num_of_periods(j)<> ROUND(l_num_of_periods(j),0))) THEN
136 p_error_flag_tab(j) := 'Y';
137 IF l_err_msg_flag = 'Y' THEN
138 PA_UTILS.ADD_MESSAGE(
139 p_app_short_name => 'PA',
140 p_msg_name => 'PA_FP_INVALID_NUM_HDR');
141 l_err_msg_flag := 'N';
142
143 END IF;
144 x_return_status := FND_API.G_RET_STS_ERROR;
145 PA_UTILS.ADD_MESSAGE(p_app_short_name => 'PA',
146 p_msg_name => 'PA_FP_INVALID_NUM',
147 p_token1 => 'INVNUM',
148 p_value1 => l_num_of_periods(j));
149 END IF;
150 --Invalid Numbers should not be entered
151 END LOOP;
152 /* l_msg_count := FND_MSG_PUB.count_msg;
153 IF (l_msg_count > 0) THEN
154 IF (l_msg_count = 1) THEN
155 PA_INTERFACE_UTILS_PUB.get_messages
156 (p_encoded => FND_API.G_TRUE,
157 p_msg_index => 1,
158 p_msg_count => l_msg_count,
159 p_msg_data => l_msg_data,
160 p_data => l_data,
161 p_msg_index_out => l_msg_index_out);
162 x_msg_data := l_data;
163 x_msg_count := l_msg_count;
164 ELSE
165 x_msg_count := l_msg_count;
166 END IF;
167 END IF; */
168 IF P_PA_DEBUG_MODE = 'Y' THEN
169 PA_DEBUG.RESET_CURR_FUNCTION;
170 END IF;
171 RETURN;
172 EXCEPTION
173 WHEN OTHERS THEN
174 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
175 x_msg_count := 1;
176 x_msg_data := SQLERRM;
177 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_PERIOD_MASKS_UTILS',
178 p_procedure_name => 'nop_validation');
179 IF P_PA_DEBUG_MODE = 'Y' THEN
180 pa_fp_gen_amount_utils.fp_debug
181 (p_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
182 p_module_name => l_module_name,
183 p_log_level => 5);
184 PA_DEBUG.RESET_CURR_FUNCTION;
185 END IF;
186 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
187 -- DBMS_OUTPUT.PUT_LINE('ERROR MSG FROM WHEN OTHER EXCEPTION');
188 END NOP_VALIDATION;
189
190
191
192 /* PROCEDURE MAINTAIN_PERIOD_MASK_DTLS
193 (p_period_mask_id IN PA_PERIOD_MASKS_B.PERIOD_MASK_ID%TYPE,
194 p_num_of_periods IN PA_NUM_1000_NUM,
195 p_anchor_period_flag IN PA_VC_1000_150,
196 p_from_anchor_position IN PA_NUM_1000_NUM,
197 p_init_msg_flag IN VARCHAR2,
198 p_commit_flag IN VARCHAR2,
199 x_return_status OUT NOCOPY VARCHAR2,
200 x_msg_count OUT NOCOPY NUMBER,
201 x_msg_data OUT NOCOPY VARCHAR2) IS
202
203 l_module_name VARCHAR2(100) := 'pa.plsql.PA_FP_PERIOD_MASKS_UTILS.MAINTAIN_PERIOD_MASK_DTLS';
204 CURSOR succ_pd IS
205 SELECT from_anchor_position,num_of_periods FROM
206 pa_fp_period_mask_tmp WHERE from_anchor_position > 0 ORDER BY from_anchor_position;
207 CURSOR prec_pd IS
208 SELECT from_anchor_position,num_of_periods FROM
209 pa_fp_period_mask_tmp WHERE from_anchor_position < 0 ORDER BY from_anchor_position;
210 l_from_anchor_position NUMBER;
211 l_first_no_of_pds NUMBER;
212 l_start_temp NUMBER;
213 l_tmp NUMBER;
214 l_tmp_end NUMBER;
215 l_initial_flag VARCHAR2(1);
216 l_anchor_period_flag PA_VC_1000_150;
217 l_flag_exists BOOLEAN := FALSE;
218 l_count number;
219 BEGIN
220 IF P_PA_DEBUG_MODE = 'Y' THEN
221 pa_debug.set_curr_function( p_function => 'MAINTAIN_PERIOD_MASK_DTLS',
222 p_debug_mode => p_pa_debug_mode );
223 END IF;
224
225 x_return_status := FND_API.G_RET_STS_SUCCESS;
226 IF p_init_msg_flag = 'Y' THEN
227 FND_MSG_PUB.Initialize;
228 END IF;
229
230 x_msg_count := 0;
231 DELETE FROM pa_fp_period_mask_tmp;
232 IF P_PA_DEBUG_MODE = 'Y' THEN
233 PA_DEBUG.g_err_stage := 'Before inserting into temporary table';
234 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
235 END IF;
236 FORALL i in p_num_of_periods.FIRST..p_num_of_periods.LAST
237 INSERT INTO pa_fp_period_mask_tmp(NUM_OF_PERIODS,ANCHOR_PERIOD_FLAG,FROM_ANCHOR_POSITION)
238 VALUES ( p_num_of_periods(i),p_anchor_period_flag(i),p_from_anchor_position(i));
239 IF P_PA_DEBUG_MODE = 'Y' THEN
240 PA_DEBUG.g_err_stage := 'After inserting into temporary table';
241 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
242 END IF;
243 select count(*) into l_count
244 from pa_fp_period_mask_tmp
245 where anchor_period_flag = 'Y';
246 if l_count = 0 then
247 update pa_fp_period_mask_tmp set
248 anchor_period_flag = 'Y' where
249 from_anchor_position = 1;
250 end if;
251 SELECT from_anchor_position,num_of_periods
252 INTO l_from_anchor_position,l_first_no_of_pds
253 FROM pa_fp_period_mask_tmp
254 WHERE anchor_period_flag = 'Y';
255 UPDATE pa_fp_period_mask_tmp
256 SET from_anchor_position = from_anchor_position - l_from_anchor_position;
257
258 UPDATE pa_fp_period_mask_tmp
259 SET from_anchor_start = 0, from_anchor_end = num_of_periods - 1
260 WHERE anchor_period_flag = 'Y';
261 l_initial_flag := 'Y';
262 l_tmp := l_first_no_of_pds;
263 l_tmp_end := l_first_no_of_pds - 1;
264 IF P_PA_DEBUG_MODE = 'Y' THEN
265 PA_DEBUG.g_err_stage := 'Before succeeding period';
266 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
267 END IF;
268 FOR succ_pd_rec IN succ_pd LOOP
269 l_tmp_end := l_tmp_end + succ_pd_rec.num_of_periods;
270 UPDATE pa_fp_period_mask_tmp
271 SET from_anchor_start = l_tmp, from_anchor_end = l_tmp_end
272 WHERE from_anchor_position = succ_pd_rec.from_anchor_position;
273
274 l_tmp := l_tmp + succ_pd_rec.num_of_periods;
275 END LOOP;
276 IF P_PA_DEBUG_MODE = 'Y' THEN
277 PA_DEBUG.g_err_stage := 'After succeeding period';
278 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
279 END IF;
280 l_tmp := 0;
281 l_tmp_end := -1;
282 IF P_PA_DEBUG_MODE = 'Y' THEN
283 PA_DEBUG.g_err_stage := 'Before preceeding period';
284 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
285 END IF;
286 FOR prec_pd_rec IN prec_pd LOOP
287 l_tmp := l_tmp - prec_pd_rec.num_of_periods;
288 UPDATE pa_fp_period_mask_tmp
289 SET from_anchor_start = l_tmp, from_anchor_end = l_tmp_end
290 WHERE from_anchor_position = prec_pd_rec.from_anchor_position;
291
292 l_tmp_end := l_tmp_end - prec_pd_rec.num_of_periods;
293 END LOOP;
294
295 IF P_PA_DEBUG_MODE = 'Y' THEN
296 PA_DEBUG.g_err_stage := 'After preceeding period';
297 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
298 END IF;
299 BEGIN
300 IF P_PA_DEBUG_MODE = 'Y' THEN
301 PA_DEBUG.g_err_stage := 'Before inserting additional records in the temporary table';
302 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
303 END IF;
304 INSERT INTO pa_fp_period_mask_tmp(num_of_periods,
305 anchor_period_flag,
306 from_anchor_start,from_anchor_end,from_anchor_position)
307 VALUES (0,'N',-99999,-99999,-99999);
308 INSERT INTO pa_fp_period_mask_tmp(num_of_periods,
309 anchor_period_flag,from_anchor_start,from_anchor_end,from_anchor_position)
310 VALUES (0,'N',99999,99999,99999);
311 IF P_PA_DEBUG_MODE = 'Y' THEN
312 PA_DEBUG.g_err_stage := 'After inserting additional records in the temporary table';
313 PA_DEBUG.Log_Message(p_message => PA_DEBUG.g_err_stage);
314 END IF;
315 INSERT INTO pa_period_mask_details(PERIOD_MASK_ID,
316 num_of_periods,
317 anchor_period_flag,
318 from_anchor_start,
319 from_anchor_end,
320 from_anchor_position)
321 (SELECT p_period_mask_id,
322 trunc(num_of_periods),
323 anchor_period_flag,
324 from_anchor_start,
325 from_anchor_end,
326 from_anchor_position FROM pa_fp_period_mask_tmp);
327 IF (SQL%NOTFOUND) THEN
328 RAISE no_data_found;
329 END IF;
330 END;
331 IF p_commit_flag = 'Y' THEN
332 COMMIT;
333 END IF;
334 IF P_PA_DEBUG_MODE = 'Y' THEN
335 PA_DEBUG.RESET_CURR_FUNCTION;
336 END IF;
337
338 IF P_PA_DEBUG_MODE = 'Y' THEN
339 PA_DEBUG.RESET_CURR_FUNCTION;
340 END IF;
341
342 EXCEPTION
343 WHEN OTHERS THEN
344 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
345 x_msg_count := 1;
346 x_msg_data := SQLERRM;
347 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_FP_PERIOD_MASKS_UTILS',
348 p_procedure_name => 'MAINTAIN_PERIOD_MASK_DTLS');
349 IF P_PA_DEBUG_MODE = 'Y' THEN
350 pa_debug.write_log(
351 x_module => l_module_name,
352 x_msg => 'Unexpected Error'||substr(sqlerrm, 1, 240),
353 x_log_level => 5);
354 PA_DEBUG.RESET_CURR_FUNCTION;
355 END IF;
356 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
357 END MAINTAIN_PERIOD_MASK_DTLS;
358
359 */
360
361 FUNCTION IS_DELETE_ALLOWED
362 (p_period_mask_id IN pa_period_masks_b.period_mask_id%TYPE)
363 RETURN VARCHAR2 is
364 l_count NUMBER := 0;
365 BEGIN
366
367 begin
368 select 1
369 into l_count
370 from dual
371 where exists(
372 SELECT 1
373 FROM pa_proj_fp_options
374 WHERE cost_period_mask_id = p_period_mask_id);
375 IF l_count > 0 THEN
376 RETURN 'N';
377 END IF;
378 exception
379 when no_data_found then
380 l_count := 0;
381 end;
382
383 l_count := 0;
384
385 begin
386 select 1
387 into l_count
388 from dual
389 where exists(
390 SELECT 1
391 FROM pa_proj_fp_options
392 WHERE rev_period_mask_id = p_period_mask_id);
393 IF l_count > 0 THEN
394 RETURN 'N';
395 END IF;
396 exception
397 when no_data_found then
398 l_count := 0;
399 end;
400
401 l_count := 0;
402
403 begin
404 select 1
405 into l_count
406 from dual
407 where exists(
408 SELECT 1
409 FROM pa_proj_fp_options
410 WHERE all_period_mask_id = p_period_mask_id);
411 IF l_count > 0 THEN
412 RETURN 'N';
413 END IF;
414 exception
415 when no_data_found then
416 l_count := 0;
417 end;
418
419 RETURN 'Y';
420
421 EXCEPTION
422 WHEN OTHERS THEN
423 RETURN 'N';
424 END IS_DELETE_ALLOWED;
425
426 END PA_FP_PERIOD_MASKS_UTILS;