1 PACKAGE BODY gl_arch_prg_from_prd_pkg AS
2 /* $Header: glfapfpb.pls 120.5 2005/05/05 02:03:57 kvora ship $ */
3 PROCEDURE get_from_prd_bal_act_arch(
4 x_appl_id IN NUMBER,
5 x_ledger_id IN NUMBER,
6 x_data_type IN VARCHAR2,
7 x_actual_flag IN VARCHAR2,
8 x_from_period IN OUT NOCOPY VARCHAR2,
9 x_closing_status IN OUT NOCOPY VARCHAR2,
10 x_from_period_eff_num IN OUT NOCOPY NUMBER
11 ) IS
12
13 -- The following SQL statement retrieves the first non-never opened period
14 -- beyond the latest archived period (if there is one). If the closing_status
15 -- of the returned row is not permanently closed or if there is no row returned
16 -- an appropriate error message will be displayed.
17
18 CURSOR get_from_period IS
19 SELECT
20 PS.closing_status,
21 PS.period_name,
22 PS.effective_period_num
23 FROM
24 GL_PERIOD_STATUSES PS
25 WHERE
26 PS.application_id = x_appl_id
27 AND PS.ledger_id = x_ledger_id
28 AND PS.closing_status <> 'N'
29 AND PS.effective_period_num >
30 (SELECT
31 NVL(MAX(AH.last_archived_eff_period_num), 0)
32 FROM
33 GL_ARCHIVE_HISTORY AH
34 WHERE
35 AH.ledger_id = x_ledger_id
36 AND AH.data_type = x_data_type
37 AND AH.actual_flag = x_actual_flag)
38 ORDER BY PS.effective_period_num ASC;
39
40 BEGIN
41
42 OPEN get_from_period;
43 FETCH get_from_period
44 INTO x_closing_status,
45 x_from_period,
46 x_from_period_eff_num;
47
48 IF get_from_period%FOUND THEN
49 CLOSE get_from_period;
50 IF (x_closing_status <> 'P') THEN
51 fnd_message.set_name('SQLGL', 'GL_PERIOD_NOT_PERM_CLOSED');
52 app_exception.raise_exception;
53 END IF;
54 ELSE
55 x_closing_status := 'N';
56 CLOSE get_from_period;
57 FND_message.set_name('SQLGL', 'GL_NO_PERIODS_FOUND');
58 app_exception.raise_exception;
59 END IF;
60
61 EXCEPTION
62 WHEN app_exceptions.application_exception THEN
63 RAISE;
64 WHEN OTHERS THEN
65 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
66 fnd_message.set_token('PROCEDURE',
67 'gl_arch_prg_from_prd_pkg.get_from_prd_bal_act_arch');
68 RAISE;
69 END get_from_prd_bal_act_arch;
70
71
72 PROCEDURE get_from_prd_bal_act_purg(
73 x_appl_id IN NUMBER,
74 x_ledger_id IN NUMBER,
75 x_data_type IN VARCHAR2,
76 x_actual_flag IN VARCHAR2,
77 x_from_period IN OUT NOCOPY VARCHAR2,
78 x_closing_status IN OUT NOCOPY VARCHAR2,
79 x_from_period_eff_num IN OUT NOCOPY NUMBER
80 ) IS
81
82 -- This procedure retrieves the earliest never purged period. This period must have
83 -- been archived before.
84
85 CURSOR get_from_period IS
86 SELECT
87 PS.closing_status,
88 PS.period_name,
89 PS.effective_period_num
90 FROM
91 GL_PERIOD_STATUSES PS
92 WHERE
93 PS.application_id = x_appl_id
94 AND PS.ledger_id = x_ledger_id
95 AND PS.closing_status <> 'N'
96 AND PS.effective_period_num >
97 (SELECT
98 NVL(MAX(AH.last_purged_eff_period_num), 0)
99 FROM
100 GL_ARCHIVE_HISTORY AH
101 WHERE
102 AH.ledger_id = x_ledger_id
103 AND AH.actual_flag = x_actual_flag
104 AND AH.data_type = x_data_type )
105 AND PS.effective_period_num <=
106 (SELECT
107 NVL(MAX(AH.last_archived_eff_period_num), -1)
108 FROM
109 GL_ARCHIVE_HISTORY AH
110 WHERE
111 AH.ledger_id = x_ledger_id
112 AND AH.data_type = x_data_type
113 AND AH.actual_flag = x_actual_flag)
114 ORDER BY PS.effective_period_num ASC;
115
116 BEGIN
117
118 OPEN get_from_period;
119 FETCH get_from_period
120 INTO x_closing_status,
121 x_from_period,
122 x_from_period_eff_num;
123
124 IF get_from_period%FOUND THEN
125 CLOSE get_from_period;
126 IF (x_closing_status <> 'P') THEN
127 fnd_message.set_name('SQLGL', 'GL_PERIOD_NOT_PERM_CLOSED');
128 app_exception.raise_exception;
129 END IF;
130 ELSE
131 CLOSE get_from_period;
132 x_closing_status := 'N';
133 FND_message.set_name('SQLGL', 'GL_NO_PERIODS_FOUND');
134 app_exception.raise_exception;
135 END IF;
136
137 EXCEPTION
138 WHEN app_exceptions.application_exception THEN
139 RAISE;
140 WHEN OTHERS THEN
141 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
142 fnd_message.set_token('PROCEDURE',
143 'gl_arch_prg_from_prd_pkg.get_from_prd_bal_act_purg');
144 RAISE;
145 END get_from_prd_bal_act_purg;
146
147 PROCEDURE get_from_prd_bal_act_both(
148 x_appl_id IN NUMBER,
149 x_ledger_id IN NUMBER,
150 x_data_type IN VARCHAR2,
151 x_actual_flag IN VARCHAR2,
152 x_from_period IN OUT NOCOPY VARCHAR2,
153 x_closing_status IN OUT NOCOPY VARCHAR2,
154 x_from_period_eff_num IN OUT NOCOPY NUMBER
155 ) IS
156
157 -- This procedure retrieves the earliest never purged period. This period must have
158 -- been archived before.
159
160 CURSOR get_from_period IS
161 SELECT
162 PS.closing_status,
163 PS.period_name,
164 PS.effective_period_num
165 FROM
166 GL_PERIOD_STATUSES PS
167 WHERE
168 PS.application_id = x_appl_id
169 AND PS.ledger_id = x_ledger_id
170 AND PS.closing_status <> 'N'
171 AND PS.effective_period_num >
172 (SELECT
173 NVL(MAX(AH.last_purged_eff_period_num), 0)
174 FROM
175 GL_ARCHIVE_HISTORY AH
176 WHERE
177 AH.ledger_id = x_ledger_id
178 AND AH.data_type = x_data_type
179 AND AH.actual_flag = x_actual_flag)
180 ORDER BY PS.effective_period_num ASC;
181
182 BEGIN
183
184 OPEN get_from_period;
185 FETCH get_from_period
186 INTO x_closing_status,
187 x_from_period,
188 x_from_period_eff_num;
189
190 IF get_from_period%FOUND THEN
191 CLOSE get_from_period;
192 IF (x_closing_status <> 'P') THEN
193 fnd_message.set_name('SQLGL', 'GL_PERIOD_NOT_PERM_CLOSED');
194 app_exception.raise_exception;
195 END IF;
196 ELSE
197 x_closing_status := 'N';
198 CLOSE get_from_period;
199 FND_message.set_name('SQLGL', 'GL_NO_PERIODS_FOUND');
200 app_exception.raise_exception;
201 END IF;
202
203 EXCEPTION
204 WHEN app_exceptions.application_exception THEN
205 RAISE;
206 WHEN OTHERS THEN
207 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
208 fnd_message.set_token('PROCEDURE',
209 'gl_arch_prg_from_prd_pkg.get_from_prd_bal_act_both');
210 RAISE;
211 END get_from_prd_bal_act_both;
212
213 PROCEDURE get_from_prd_bal_bud_arch(
214 x_appl_id IN NUMBER,
215 x_ledger_id IN NUMBER,
216 x_data_type IN VARCHAR2,
217 x_actual_flag IN VARCHAR2,
218 x_budget_version_id IN NUMBER,
219 x_from_period IN OUT NOCOPY VARCHAR2,
220 x_closing_status IN OUT NOCOPY VARCHAR2,
221 x_from_period_eff_num IN OUT NOCOPY NUMBER
222 ) IS
223
224 -- This procedure retrieves the earliest never archived period for the chosen budget.
225 -- The budget year must be open.
226
227 CURSOR get_from_period IS
228 SELECT
229 BPR.start_period_name,
230 ( BPR.period_year * 10000 + BPR.start_period_num )
231 FROM
232 GL_BUDGET_PERIOD_RANGES BPR
233 WHERE
234 BPR.budget_version_id = x_budget_version_id
235 AND BPR.open_flag = 'O'
236 AND ( BPR.period_year * 10000 + BPR.start_period_num ) >
237 (SELECT
238 NVL(MAX(AH.last_archived_eff_period_num), 0)
239 FROM
240 GL_ARCHIVE_HISTORY AH
241 WHERE
242 AH.ledger_id = x_ledger_id
243 AND AH.data_type = x_data_type
244 AND AH.actual_flag = x_actual_flag
245 AND AH.budget_version_id = x_budget_version_id )
246 ORDER BY ( BPR.period_year * 10000 + BPR.start_period_num ) ASC;
247
248 BEGIN
249
250 OPEN get_from_period;
251 FETCH get_from_period
252 INTO x_from_period,
253 x_from_period_eff_num;
254
255 IF get_from_period%FOUND THEN
256 CLOSE get_from_period;
257 x_closing_status := 'Y';
258 ELSE
259 x_closing_status := 'N';
260 CLOSE get_from_period;
261 FND_message.set_name('SQLGL', 'GL_NO_PERIODS_FOUND');
262 app_exception.raise_exception;
263 END IF;
264
265 EXCEPTION
266 WHEN app_exceptions.application_exception THEN
267 RAISE;
268 WHEN OTHERS THEN
269 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
270 fnd_message.set_token('PROCEDURE',
271 'gl_arch_prg_from_prd_pkg.get_from_prd_bal_bud_arch');
272 RAISE;
273 END get_from_prd_bal_bud_arch;
274
275
276 PROCEDURE get_from_prd_bal_bud_purg(
277 x_appl_id IN NUMBER,
278 x_ledger_id IN NUMBER,
279 x_data_type IN VARCHAR2,
280 x_actual_flag IN VARCHAR2,
281 x_budget_version_id IN NUMBER,
282 x_from_period IN OUT NOCOPY VARCHAR2,
283 x_closing_status IN OUT NOCOPY VARCHAR2,
284 x_from_period_eff_num IN OUT NOCOPY NUMBER
285 ) IS
286
287 -- This procedure retrieves the earliest never purged period for the chosen budget.
288 -- This period must have been archived before.
289
290 CURSOR get_from_period IS
291 SELECT
292 BPR.start_period_name,
293 ( BPR.period_year * 10000 + BPR.start_period_num )
294 FROM
295 GL_BUDGET_PERIOD_RANGES BPR
296 WHERE
297 BPR.budget_version_id = x_budget_version_id
298 AND BPR.open_flag = 'O'
299 AND ( BPR.period_year * 10000 + BPR.start_period_num ) >
300 (SELECT
301 NVL(MAX(AH.last_purged_eff_period_num), 0)
302 FROM
303 GL_ARCHIVE_HISTORY AH
304 WHERE
305 AH.ledger_id = x_ledger_id
306 AND AH.data_type = x_data_type
307 AND AH.actual_flag = x_actual_flag
308 AND AH.budget_version_id = x_budget_version_id )
309 AND ( BPR.period_year * 10000 + BPR.start_period_num ) <=
310 (SELECT
311 NVL(MAX(AH.last_archived_eff_period_num), 0)
312 FROM
313 GL_ARCHIVE_HISTORY AH
314 WHERE
315 AH.ledger_id = x_ledger_id
316 AND AH.data_type = x_data_type
317 AND AH.actual_flag = x_actual_flag
318 AND AH.budget_version_id = x_budget_version_id )
319 ORDER BY ( BPR.period_year * 10000 + BPR.start_period_num ) ASC;
320
321 BEGIN
322
323 OPEN get_from_period;
324 FETCH get_from_period
325 INTO x_from_period,
326 x_from_period_eff_num;
327
328 IF get_from_period%FOUND THEN
329 CLOSE get_from_period;
330 x_closing_status := 'Y';
331 ELSE
332 x_closing_status := 'N';
333 CLOSE get_from_period;
334 FND_message.set_name('SQLGL', 'GL_NO_PERIODS_FOUND');
335 app_exception.raise_exception;
336 END IF;
337
338 EXCEPTION
339 WHEN app_exceptions.application_exception THEN
340 RAISE;
341 WHEN OTHERS THEN
342 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
343 fnd_message.set_token('PROCEDURE',
344 'gl_arch_prg_from_prd_pkg.get_from_prd_bal_bud_arch');
345 RAISE;
346 END get_from_prd_bal_bud_purg;
347
348 PROCEDURE get_from_prd_bal_bud_both(
349 x_appl_id IN NUMBER,
350 x_ledger_id IN NUMBER,
351 x_data_type IN VARCHAR2,
352 x_actual_flag IN VARCHAR2,
353 x_budget_version_id IN NUMBER,
354 x_from_period IN OUT NOCOPY VARCHAR2,
355 x_closing_status IN OUT NOCOPY VARCHAR2,
356 x_from_period_eff_num IN OUT NOCOPY NUMBER
357 ) IS
358
359 -- This procedure retrieves the earliest never purged period for the chosen budget.
360 -- This budget year must be open.
361
362 CURSOR get_from_period IS
363 SELECT
364 BPR.start_period_name,
365 ( BPR.period_year * 10000 + BPR.start_period_num )
366 FROM
367 GL_BUDGET_PERIOD_RANGES BPR
368 WHERE
369 BPR.budget_version_id = x_budget_version_id
370 AND BPR.open_flag = 'O'
371 AND ( BPR.period_year * 10000 + BPR.start_period_num ) >
372 (SELECT
373 NVL(MAX(AH.last_purged_eff_period_num), 0)
374 FROM
375 GL_ARCHIVE_HISTORY AH
376 WHERE
377 AH.ledger_id = x_ledger_id
378 AND AH.data_type = x_data_type
379 AND AH.actual_flag = x_actual_flag
380 AND AH.budget_version_id = x_budget_version_id )
381 ORDER BY ( BPR.period_year * 10000 + BPR.start_period_num ) ASC;
382
383 BEGIN
384
385 OPEN get_from_period;
386 FETCH get_from_period
387 INTO x_from_period,
388 x_from_period_eff_num;
389
390 IF get_from_period%FOUND THEN
391 CLOSE get_from_period;
392 x_closing_status := 'Y';
393 ELSE
394 x_closing_status := 'N';
395 CLOSE get_from_period;
396 FND_message.set_name('SQLGL', 'GL_NO_PERIODS_FOUND');
397 app_exception.raise_exception;
398 END IF;
399
400 EXCEPTION
401 WHEN app_exceptions.application_exception THEN
402 RAISE;
403 WHEN OTHERS THEN
404 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
405 fnd_message.set_token('PROCEDURE',
406 'gl_arch_prg_from_prd_pkg.get_from_prd_bal_bud_arch');
407 RAISE;
408 END get_from_prd_bal_bud_both;
409
410 PROCEDURE get_to_prd_trn_both(
411 x_appl_id IN NUMBER,
412 x_ledger_id IN NUMBER,
413 x_data_type IN VARCHAR2,
414 x_actual_flag IN VARCHAR2,
415 x_budget_version_id IN NUMBER,
416 x_currency IN VARCHAR2,
417 x_to_period IN OUT NOCOPY VARCHAR2,
418 x_closing_status IN OUT NOCOPY VARCHAR2,
419 x_to_period_eff_num IN OUT NOCOPY NUMBER
420 ) IS
421
422 -- This procedure retrieves the latest ever translated period.
423
424 CURSOR get_to_period IS
425 SELECT
426 PS.period_name,
427 PS.closing_status,
431 GL_TRANSLATION_TRACKING TT
428 PS.effective_period_num
429 FROM
430 GL_PERIOD_STATUSES PS,
432 WHERE
433 PS.application_id = x_appl_id
434 AND PS.ledger_id = x_ledger_id
435 AND TT.ledger_id = x_ledger_id
436 AND TT.target_currency = x_currency
437 AND TT.actual_flag = x_actual_flag
438 AND NVL(TT.target_budget_version_id,-1) = NVL(x_budget_version_id,-1)
439 AND PS.effective_period_num >=
440 (TT.earliest_ever_period_year * 10000 + TT.earliest_ever_period_num)
441 AND PS.effective_period_num <
442 (TT.earliest_never_period_year * 10000 + TT.earliest_never_period_num)
443 ORDER BY PS.effective_period_num DESC;
444
445 BEGIN
446
447 OPEN get_to_period;
448 FETCH get_to_period
449 INTO x_to_period,
450 x_closing_status,
451 x_to_period_eff_num;
452
453 IF get_to_period%FOUND THEN
454 CLOSE get_to_period;
455 x_closing_status := 'Y';
456 ELSE
457 x_closing_status := 'N';
458 CLOSE get_to_period;
459 FND_message.set_name('SQLGL', 'GL_NO_PERIODS_FOUND');
460 app_exception.raise_exception;
461 END IF;
462
463 EXCEPTION
464 WHEN app_exceptions.application_exception THEN
465 RAISE;
466 WHEN OTHERS THEN
467 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
468 fnd_message.set_token('PROCEDURE',
469 'gl_arch_prg_from_prd_pkg.get_to_prd_trn_both');
470 RAISE;
471 END get_to_prd_trn_both;
472
473 PROCEDURE get_to_prd_trn_standard(
474 x_appl_id IN NUMBER,
475 x_ledger_id IN NUMBER,
476 x_data_type IN VARCHAR2,
477 x_actual_flag IN VARCHAR2,
478 x_budget_version_id IN NUMBER,
479 x_currency IN VARCHAR2,
480 x_to_period IN OUT NOCOPY VARCHAR2,
481 x_closing_status IN OUT NOCOPY VARCHAR2,
482 x_to_period_eff_num IN OUT NOCOPY NUMBER
483 ) IS
484
485 -- This procedure retrieves the latest ever translated period.
486
487 CURSOR get_to_period IS
488 SELECT
489 PS.period_name,
490 PS.closing_status,
491 PS.effective_period_num
492 FROM
493 GL_PERIOD_STATUSES PS,
494 GL_TRANSLATION_TRACKING TT
495 WHERE
496 PS.application_id = x_appl_id
497 AND PS.ledger_id = x_ledger_id
498 AND TT.ledger_id = x_ledger_id
499 AND TT.target_currency = x_currency
500 AND TT.average_translation_flag = 'N'
501 AND TT.actual_flag = x_actual_flag
502 AND NVL(TT.target_budget_version_id,-1) = NVL(x_budget_version_id,-1)
503 AND PS.effective_period_num >=
504 (TT.earliest_ever_period_year * 10000 + TT.earliest_ever_period_num)
505 AND PS.effective_period_num <
506 (TT.earliest_never_period_year * 10000 + TT.earliest_never_period_num)
507 ORDER BY PS.effective_period_num DESC;
508
509 BEGIN
510
511 OPEN get_to_period;
512 FETCH get_to_period
513 INTO x_to_period,
514 x_closing_status,
515 x_to_period_eff_num;
516
517 IF get_to_period%FOUND THEN
518 CLOSE get_to_period;
519 x_closing_status := 'Y';
520 ELSE
521 x_closing_status := 'N';
522 CLOSE get_to_period;
523 FND_message.set_name('SQLGL', 'GL_NO_PERIODS_FOUND');
524 app_exception.raise_exception;
525 END IF;
526
527 EXCEPTION
528 WHEN app_exceptions.application_exception THEN
529 RAISE;
530 WHEN OTHERS THEN
531 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
532 fnd_message.set_token('PROCEDURE',
533 'gl_arch_prg_from_prd_pkg.get_to_prd_trn_standard');
534 RAISE;
535 END get_to_prd_trn_standard;
536
537 PROCEDURE get_to_prd_trn_average(
538 x_appl_id IN NUMBER,
539 x_ledger_id IN NUMBER,
540 x_data_type IN VARCHAR2,
541 x_actual_flag IN VARCHAR2,
542 x_budget_version_id IN NUMBER,
543 x_currency IN VARCHAR2,
544 x_to_period IN OUT NOCOPY VARCHAR2,
545 x_closing_status IN OUT NOCOPY VARCHAR2,
546 x_to_period_eff_num IN OUT NOCOPY NUMBER
547 ) IS
548
549 -- This procedure retrieves the latest ever translated period.
550
551 CURSOR get_to_period IS
552 SELECT
553 PS.period_name,
554 PS.closing_status,
555 PS.effective_period_num
556 FROM
557 GL_PERIOD_STATUSES PS,
558 GL_TRANSLATION_TRACKING TT
559 WHERE
560 PS.application_id = x_appl_id
561 AND PS.ledger_id = x_ledger_id
562 AND TT.ledger_id = x_ledger_id
563 AND TT.target_currency = x_currency
564 AND TT.average_translation_flag = 'Y'
565 AND TT.actual_flag = x_actual_flag
566 AND NVL(TT.target_budget_version_id,-1) = NVL(x_budget_version_id,-1)
567 AND PS.effective_period_num >=
568 (TT.earliest_ever_period_year * 10000 + TT.earliest_ever_period_num)
569 AND PS.effective_period_num <
570 (TT.earliest_never_period_year * 10000 + TT.earliest_never_period_num)
571 ORDER BY PS.effective_period_num DESC;
572
573 BEGIN
574
575 OPEN get_to_period;
576 FETCH get_to_period
577 INTO x_to_period,
578 x_closing_status,
579 x_to_period_eff_num;
580
581 IF get_to_period%FOUND THEN
582 CLOSE get_to_period;
583 x_closing_status := 'Y';
584 ELSE
585 x_closing_status := 'N';
586 CLOSE get_to_period;
587 FND_message.set_name('SQLGL', 'GL_NO_PERIODS_FOUND');
588 app_exception.raise_exception;
589 END IF;
590
591 EXCEPTION
592 WHEN app_exceptions.application_exception THEN
593 RAISE;
594 WHEN OTHERS THEN
595 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
596 fnd_message.set_token('PROCEDURE',
597 'gl_arch_prg_from_prd_pkg.get_to_prd_trn_average');
598 RAISE;
599 END get_to_prd_trn_average;
600
601 END gl_arch_prg_from_prd_pkg;
[Home] [Help]