1 PACKAGE BODY PO_PERIODS_SV as
2 -- $Header: POXCOPEB.pls 120.3 2011/12/08 00:02:43 ajunnikr ship $
3
4 -----------------------------------------------------------------------------
5 -- Declare private package types.
6 -----------------------------------------------------------------------------
7
8 /* Bug 3292931: A table of ROWID is not allowed in an 8i database. Instead,
9 * use a table of VARCHAR2 and use the ROWIDTOCHAR() and CHARTOROWID() functions.
10 * A ROWID is compatible with a VARCHAR2 of size 18.
11 */
12
13 --TYPE g_tbl_rowid IS TABLE OF ROWID;
14 TYPE g_tbl_rowid IS TABLE OF VARCHAR2(18);
15
16 -----------------------------------------------------------------------------
17 -- Declare private package variables.
18 -----------------------------------------------------------------------------
19
20 -- Debugging
21
22 g_pkg_name CONSTANT VARCHAR2(30) := 'PO_PERIODS_SV';
23 g_log_head CONSTANT VARCHAR2(50) := 'po.plsql.' || g_pkg_name || '.';
24
25 g_debug_stmt BOOLEAN;
26 g_debug_unexp BOOLEAN;
27
28 -----------------------------------------------------------------------------
29 -- Define procedures.
30 -----------------------------------------------------------------------------
31
32 -------------------------------------------------------------------------------
33 --Start of Comments
34 --Name: get_period_info
35 --Pre-reqs:
36 -- None.
37 --Modifies:
38 -- None.
39 --Locks:
40 -- None.
41 --Function:
42 -- Retrieves the GL period info for the given dates.
43 -- Period information will only be found if
44 -- the date falls within a usable period (valid for both GL and PO).
45 -- If a usable period is not found for any of the given dates,
46 -- the x_invalid_period_flag will be FND_API.g_TRUE,
47 -- and the other out parameters corresponding to the date will be NULL.
48 --Parameters:
49 --IN:
50 --p_roll_logic
51 -- Intended to be flexible to use roll-forward/roll-backward logic
52 -- for encumbrance. Currently not supported.
53 -- Use NULL.
54 --p_set_of_books_id
55 -- If the set of books is not passed, it will be derived from
56 -- FINANCIALS_SYSTEM_PARAMETERS.
57 --p_date_tbl
58 -- Dates of which to find the periods.
59 --OUT:
60 -- These correspond to the dates in p_date_tbl.
61 --x_period_name_tbl
62 --x_period_year_tbl
63 --x_period_num_tbl
64 --x_quarter_num_tbl
65 --
66 --x_invalid_period_flag
67 -- Indicates whether or not usable periods were found for each of the
68 -- given dates.
69 -- FND_API.g_TRUE a usable period was not found for at least one date
70 -- FND_API.g_FALSE usable periods were found for each date
71 --Testing:
72 --
73 --End of Comments
74 -------------------------------------------------------------------------------
75 PROCEDURE get_period_info(p_roll_logic IN VARCHAR2,
76 p_set_of_books_id IN NUMBER,
77 p_date_tbl IN po_tbl_date,
78 x_period_name_tbl OUT NOCOPY po_tbl_varchar30,
79 x_period_year_tbl OUT NOCOPY po_tbl_number,
80 x_period_num_tbl OUT NOCOPY po_tbl_number,
81 x_quarter_num_tbl OUT NOCOPY po_tbl_number,
82 x_invalid_period_flag OUT NOCOPY VARCHAR2) IS
83
84 l_log_head CONSTANT VARCHAR2(100) := g_log_head || 'GET_PERIOD_INFO';
85 l_progress VARCHAR2(3) := '000';
86
87 l_set_of_books_id NUMBER;
88
89 l_date_key NUMBER;
90 l_rowid_tbl g_tbl_rowid;
91
92 l_no_dates_exc EXCEPTION;
93
94 -- bug 5498063 <R12 GL PERIOD VALIDATION>
95 l_validate_gl_period VARCHAR2(1);
96
97 BEGIN
98
99 IF g_debug_stmt THEN
100 PO_DEBUG.debug_begin(l_log_head);
101 PO_DEBUG.debug_var(l_log_head,
102 l_progress,
103 'p_roll_logic',
104 p_roll_logic);
105 PO_DEBUG.debug_var(l_log_head,
106 l_progress,
107 'p_set_of_books_id',
108 p_set_of_books_id);
109 PO_DEBUG.debug_var(l_log_head, l_progress, 'p_date_tbl', p_date_tbl);
110 END IF;
111
112 l_progress := '010';
113
114 IF (p_date_tbl IS NULL) THEN
115 RAISE l_no_dates_exc;
116 ELSIF (p_date_tbl.COUNT = 0) THEN
117 RAISE l_no_dates_exc;
118 END IF;
119
120 l_progress := '015';
121
122 -- Get the set of books id.
123
124 IF (p_set_of_books_id IS NULL) THEN
125
126 l_progress := '020';
127 IF g_debug_stmt THEN
128 PO_DEBUG.debug_stmt(l_log_head, l_progress, 'set of books is NULL');
129 END IF;
130
131 SELECT FSP.set_of_books_id
132 INTO l_set_of_books_id
133 FROM FINANCIALS_SYSTEM_PARAMETERS FSP;
134
135 l_progress := '030';
136
137 ELSE
138
139 l_progress := '040';
140 IF g_debug_stmt THEN
141 PO_DEBUG.debug_stmt(l_log_head, l_progress, 'set of books passed');
142 END IF;
143
144 l_set_of_books_id := p_set_of_books_id;
145
146 l_progress := '050';
147
148 END IF;
149
150 l_progress := '060';
151
152 -- Use the scratchpad to avoid PL/SQL limitations.
153
154 SELECT PO_SESSION_GT_S.nextval INTO l_date_key FROM DUAL;
155
156 l_progress := '070';
157
158 -----------------------------------------
159 -- PO_SESSION_GT column mapping
160 --
161 -- date1 GL date
162 -- char1 period_name
163 -- num1 period_year
164 -- num2 period_num
165 -- num3 quarter_num
166 -----------------------------------------
167
168 /* Bug 3292931: A table of ROWID is not allowed in an 8i database. Instead,
169 * use a table of VARCHAR2 and use the ROWIDTOCHAR() and CHARTOROWID() functions.
170 */
171
172 FORALL i IN 1 .. p_date_tbl.COUNT
173 INSERT INTO PO_SESSION_GT
174 (key, date1)
175 VALUES
176 (l_date_key, p_date_tbl(i))
177 RETURNING ROWIDTOCHAR
178 (rowid) BULK COLLECT INTO l_rowid_tbl;
179
180 l_progress := '080';
181
182 /* Bug 3292931: A table of ROWID is not allowed in an 8i database. Instead,
183 * use a table of VARCHAR2 and use the ROWIDTOCHAR() and CHARTOROWID() functions.
184 * Changes made below to how l_rowid_tbl is interpreted.
185 */
186
187 -- bug 5206339 <11.5.10 GL PERIOD VALIDATION>
188 l_validate_gl_period := nvl(FND_PROFILE.VALUE('PO_VALIDATE_GL_PERIOD'),
189 'Y');
190
191 FORALL i IN 1 .. l_rowid_tbl.COUNT
192 UPDATE PO_SESSION_GT SES
193 SET (char1 -- period_name
194 , num1 -- period_year
195 , num2 -- period_num
196 , num3 -- quarter_num
197 ) = (SELECT GL_PS.period_name,
198 GL_PS.period_year,
199 GL_PS.period_num,
200 GL_PS.quarter_num
201 FROM GL_PERIOD_STATUSES GL_PS,
202 GL_PERIOD_STATUSES PO_PS,
203 GL_SETS_OF_BOOKS SOB
204 WHERE SOB.set_of_books_id = l_set_of_books_id
205 AND GL_PS.application_id = 101
206 AND PO_PS.application_id = 201
207 AND GL_PS.set_of_books_id = SOB.set_of_books_id --JOIN
208 AND PO_PS.set_of_books_id = SOB.set_of_books_id --JOIN
209 AND GL_PS.period_name = PO_PS.period_name --JOIN
210 -- GL period conditions
211 -- bug 5498063 <R12 GL PERIOD VALIDATION>
212 -- AND GL_PS.closing_status IN ('O','F') open or future-enterable
213 AND ((l_validate_gl_period IN ('Y','R') and
214 GL_PS.closing_status IN ('O', 'F')) OR
215 (l_validate_gl_period = 'N'))
216 -- bug 5498063 <R12 GL PERIOD VALIDATION>
217 AND GL_PS.adjustment_period_flag = 'N' -- not an adjusting period
218 AND GL_PS.period_year <= SOB.latest_encumbrance_year
219 -- PO period conditions
220 AND PO_PS.closing_status = 'O' -- open
221 AND PO_PS.adjustment_period_flag = 'N' -- not an adjusting period
222 -- Date logic (to include roll-forward, roll-backward?)
223 -- See PO_ENCUMBRANCE_PREPROCESSING.find_open_period
224 AND TRUNC(SES.date1) BETWEEN TRUNC(GL_PS.start_date) AND
225 TRUNC(GL_PS.end_date))
226 WHERE SES.rowid = CHARTOROWID(l_rowid_tbl(i))
227 RETURNING SES.char1 -- period_name
228 , SES.num1 -- period_year
229 , SES.num2 -- period_num
230 , SES.num3 -- quarter_num
231 BULK COLLECT INTO x_period_name_tbl, x_period_year_tbl, x_period_num_tbl, x_quarter_num_tbl;
232
233 l_progress := '100';
234
235 -- Figure out if any periods were not found.
236
237 x_invalid_period_flag := FND_API.G_FALSE;
238
239 BEGIN
240
241 l_progress := '110';
242
243 SELECT FND_API.G_TRUE
244 INTO x_invalid_period_flag
245 FROM PO_SESSION_GT SES
246 WHERE SES.key = l_date_key
247 AND SES.char1 IS NULL
248 AND rownum = 1;
249
250 l_progress := '120';
251
252 EXCEPTION
253 WHEN NO_DATA_FOUND THEN
254 l_progress := '130';
255 END;
256
257 IF g_debug_stmt THEN
258 PO_DEBUG.debug_var(l_log_head,
259 l_progress,
260 'x_invalid_period_flag',
261 x_invalid_period_flag);
262 END IF;
263
264 l_progress := '900';
265
266 IF g_debug_stmt THEN
267 PO_DEBUG.debug_var(l_log_head,
268 l_progress,
269 'x_period_name_tbl',
270 x_period_name_tbl);
271 PO_DEBUG.debug_var(l_log_head,
272 l_progress,
273 'x_period_year_tbl',
274 x_period_year_tbl);
275 PO_DEBUG.debug_var(l_log_head,
276 l_progress,
277 'x_period_num_tbl',
278 x_period_num_tbl);
279 PO_DEBUG.debug_var(l_log_head,
280 l_progress,
281 'x_quarter_num_tbl',
282 x_quarter_num_tbl);
283 PO_DEBUG.debug_var(l_log_head,
284 l_progress,
285 'x_invalid_period_flag',
286 x_invalid_period_flag);
287 PO_DEBUG.debug_end(l_log_head);
288 END IF;
289
290 EXCEPTION
291
292 WHEN l_no_dates_exc THEN
293 IF g_debug_stmt THEN
294 PO_DEBUG.debug_stmt(l_log_head,
295 l_progress,
296 'l_no_dates_exc: Empty date table.');
297 END IF;
298
299 l_progress := '910';
300
301 x_period_name_tbl := po_tbl_varchar30();
302 x_period_year_tbl := po_tbl_number();
303 x_period_num_tbl := po_tbl_number();
304 x_quarter_num_tbl := po_tbl_number();
305 x_invalid_period_flag := FND_API.g_FALSE;
306
307 l_progress := '912';
308
309 IF g_debug_stmt THEN
310 PO_DEBUG.debug_var(l_log_head,
311 l_progress,
312 'x_period_name_tbl',
313 x_period_name_tbl);
314 PO_DEBUG.debug_var(l_log_head,
315 l_progress,
316 'x_period_year_tbl',
317 x_period_year_tbl);
318 PO_DEBUG.debug_var(l_log_head,
319 l_progress,
320 'x_period_num_tbl',
321 x_period_num_tbl);
322 PO_DEBUG.debug_var(l_log_head,
323 l_progress,
324 'x_quarter_num_tbl',
325 x_quarter_num_tbl);
326 PO_DEBUG.debug_var(l_log_head,
327 l_progress,
328 'x_invalid_period_flag',
329 x_invalid_period_flag);
330 PO_DEBUG.debug_end(l_log_head);
331 END IF;
332
333 WHEN OTHERS THEN
334 IF g_debug_unexp THEN
335 PO_DEBUG.debug_exc(l_log_head, l_progress);
336 END IF;
337 RAISE;
338
339 END get_period_info;
340
341 -------------------------------------------------------------------------------
342 --Start of Comments
343 --Name: get_period_name
344 --Pre-reqs:
345 -- None.
346 --Modifies:
347 -- None.
348 --Locks:
349 -- None.
350 --Function:
351 -- Retrieves the GL period name for the given date,
352 -- if the date is in a usable period (valid for GL and PO).
353 --Parameters:
354 --IN:
355 --x_sob_id
356 -- Set of books.
357 --x_gl_date
358 -- Date for which to find the period name.
359 --OUT:
360 --x_gl_period
361 -- The period name corresponding to the given date.
362 --Notes:
363 -- This procedure was refactored in FPJ to call the more generalized
364 -- procedure get_period_info. However, the parameter names were
365 -- not changed to meet standards, as that may have impacted calling code.
366 --Testing:
367 --
368 --End of Comments
369 -------------------------------------------------------------------------------
370 PROCEDURE get_period_name(x_sob_id IN NUMBER,
371 x_gl_date IN DATE,
372 x_gl_period OUT NOCOPY VARCHAR2) IS
373
374 l_log_head CONSTANT VARCHAR2(100) := g_log_head || 'GET_PERIOD_NAME';
375 l_progress VARCHAR2(3) := '000';
376
377 l_period_name_tbl po_tbl_varchar30;
378 l_period_year_tbl po_tbl_number;
379 l_period_num_tbl po_tbl_number;
380 l_quarter_num_tbl po_tbl_number;
381 l_invalid_period_flag VARCHAR2(1);
382
383 BEGIN
384
385 IF g_debug_stmt THEN
386 PO_DEBUG.debug_begin(l_log_head);
387 PO_DEBUG.debug_var(l_log_head, l_progress, 'x_sob_id', x_sob_id);
388 PO_DEBUG.debug_var(l_log_head, l_progress, 'x_gl_date', x_gl_date);
389 END IF;
390
391 l_progress := '010';
392
393 get_period_info(p_roll_logic => NULL,
394 p_set_of_books_id => x_sob_id,
395 p_date_tbl => po_tbl_date(x_gl_date),
396 x_period_name_tbl => l_period_name_tbl,
397 x_period_year_tbl => l_period_year_tbl,
398 x_period_num_tbl => l_period_num_tbl,
399 x_quarter_num_tbl => l_quarter_num_tbl,
400 x_invalid_period_flag => l_invalid_period_flag);
401
402 l_progress := '020';
403
404 x_gl_period := l_period_name_tbl(1);
405
406 IF g_debug_stmt THEN
407 PO_DEBUG.debug_var(l_log_head,
408 l_progress,
409 'x_gl_period',
410 x_gl_period);
411 PO_DEBUG.debug_end(l_log_head);
412 END IF;
413
414 EXCEPTION
415 WHEN OTHERS THEN
416 IF g_debug_unexp THEN
417 PO_DEBUG.debug_exc(l_log_head, l_progress);
418 END IF;
419 RAISE;
420
421 END get_period_name;
422
423 -------------------------------------------------------------------------------
424 -- GL Date Project#Start:
425 --Start of Comments
426 --Name: build_GL_Encumbered_Date
427 --Pre-reqs:
428 -- None.
429 --Modifies:
430 -- None.
431 --Locks:
432 -- None.
433 --Function:
434 -- Derive proper GL date, when the profile PO: Validate GL Period has been
435 -- set to Redefault.
436 -- 1st preference need to be given to the GL date that has been passed (i.e.
437 -- GL date at the distribution level.
438 -- 2nd preference need to be given to the System date.
439 -- 3rd preference need to be given to the earliest open period.
440 --Parameters:
441 --IN:
442 --l_sob_id
443 -- Set of books.
444 --IN OUT:
445 -- x_gl_date
446 -- Date, which needs to be replaced with a correct date, if not open.
447 -- OUT:
448 -- x_gl_period
449 -- Period, derived based on the GL date derived.
450 --Notes:
451 -- This procedure was written for GL Date (CLM Phase 2) project.
452 -- The requirement is to derive a Latest Open Period's GL Date, if the
453 -- entered/present GL date is not in Open period.
454 --Testing:
455 --
456 --End of Comments
457 -------------------------------------------------------------------------------
458 PROCEDURE build_GL_Encumbered_Date(l_sob_id IN NUMBER,
459 x_gl_date IN OUT NOCOPY DATE,
460 x_gl_period OUT NOCOPY VARCHAR2)
461
462 IS
463 d_mod CONSTANT VARCHAR2(100) := 'D_build_gl_encumbered_date';
464 l_log_head CONSTANT VARCHAR2(100) := g_log_head ||
465 'build_GL_Encumbered_Date';
466 l_validate_gl_period VARCHAR2(1) := nvl(FND_PROFILE.VALUE('PO_VALIDATE_GL_PERIOD'),
467 'Y');
468 Begin
469
470 IF PO_LOG.d_proc THEN
471 PO_LOG.proc_begin(d_mod, 'l_sob_id', l_sob_id);
472 PO_LOG.proc_begin(d_mod, 'x_gl_date', x_gl_date);
473 PO_LOG.proc_begin(d_mod, 'x_gl_period', x_gl_period);
474 END IF;
475
476 begin
477 -- 1st Check: Find out whether passed GL date (distribution level) belongs
478 -- to Open Period or not.
479 SELECT GL_PS.period_name
480 into x_gl_period
481 FROM GL_PERIOD_STATUSES GL_PS,
482 GL_PERIOD_STATUSES PO_PS,
483 GL_SETS_OF_BOOKS SOB
484 WHERE SOB.set_of_books_id = l_sob_id
485 AND GL_PS.application_id = 101
486 AND PO_PS.application_id = 201
487 AND GL_PS.set_of_books_id = SOB.set_of_books_id --JOIN
488 AND PO_PS.set_of_books_id = SOB.set_of_books_id --JOIN
489 AND GL_PS.period_name = PO_PS.period_name --JOIN
490 AND ((l_validate_gl_period IN ('Y','R')
491 and GL_PS.closing_status IN ('O', 'F')) OR (l_validate_gl_period = 'N'))
492 AND GL_PS.adjustment_period_flag = 'N' -- not an adjusting period
493 AND GL_PS.period_year <= SOB.latest_encumbrance_year
494 AND PO_PS.closing_status = 'O' -- open
495 AND PO_PS.adjustment_period_flag = 'N' -- not an adjusting period
496 AND TRUNC(Nvl(x_gl_date, SYSDATE)) BETWEEN TRUNC(GL_PS.start_date) AND
497 TRUNC(GL_PS.end_date)
498 and rownum = 1;
499
500 exception
501 when no_data_found THEN
502 -- No Data found means the passed GL Date is not in an open period
503 BEGIN
504 -- 2nd Check: Find out whether System date belongs to Open Period or not.
505 SELECT GL_PS.period_name
506 into x_gl_period
507 FROM GL_PERIOD_STATUSES GL_PS,
508 GL_PERIOD_STATUSES PO_PS,
509 GL_SETS_OF_BOOKS SOB
510 WHERE SOB.set_of_books_id = l_sob_id
511 AND GL_PS.application_id = 101
512 AND PO_PS.application_id = 201
513 AND GL_PS.set_of_books_id = SOB.set_of_books_id --JOIN
514 AND PO_PS.set_of_books_id = SOB.set_of_books_id --JOIN
515 AND GL_PS.period_name = PO_PS.period_name --JOIN
516 AND ((l_validate_gl_period IN ('Y','R')
517 and GL_PS.closing_status IN ('O', 'F')) OR (l_validate_gl_period = 'N'))
518 AND GL_PS.adjustment_period_flag = 'N' -- not an adjusting period
519 AND GL_PS.period_year <= SOB.latest_encumbrance_year
520 AND PO_PS.closing_status = 'O' -- open
521 AND PO_PS.adjustment_period_flag = 'N' -- not an adjusting period
522 AND TRUNC(sysdate) BETWEEN TRUNC(GL_PS.start_date) AND
523 TRUNC(GL_PS.end_date)
524 and rownum = 1;
525
526 x_gl_date := sysdate;
527
528 IF po_log.d_proc THEN
529 PO_LOG.proc_begin(l_log_head, 'x_gl_date', x_gl_date);
530 PO_LOG.proc_begin(l_log_head, 'x_gl_period', x_gl_period);
531 END IF;
532
533 exception
534 when no_data_found then
535 -- No Data found means Sysdate is not in an open period
536 BEGIN
537 -- 3rd Check: Find out the Earliest Open Period date, which
538 -- falls in near Future period.
539 SELECT latest_period_name, latest_open_date
540 into x_gl_period, x_gl_date
541 FROM (
542 SELECT GL_PS.period_year,
543 GL_PS.period_num,
544 GL_PS.quarter_num,
545 gl_ps.period_name latest_period_name,
546 TRUNC(GL_PS.start_date) latest_open_date
547 FROM GL_PERIOD_STATUSES GL_PS,
548 GL_PERIOD_STATUSES PO_PS,
549 GL_SETS_OF_BOOKS SOB
550 WHERE SOB.set_of_books_id = l_sob_id
551 AND GL_PS.application_id = 101
552 AND PO_PS.application_id = 201
553 AND GL_PS.set_of_books_id = SOB.set_of_books_id --JOIN
554 AND PO_PS.set_of_books_id = SOB.set_of_books_id --JOIN
555 AND GL_PS.period_name = PO_PS.period_name --JOIN
556 AND ((l_validate_gl_period IN ('Y','R')
557 AND GL_PS.closing_status IN ('O', 'F')) OR (l_validate_gl_period = 'N'))
558 AND GL_PS.adjustment_period_flag = 'N' -- not an adjusting period
559 AND GL_PS.period_year <= SOB.latest_encumbrance_year
560 AND PO_PS.closing_status = 'O' -- open
561 AND PO_PS.adjustment_period_flag = 'N' -- not an adjusting period
562 AND TRUNC(GL_PS.start_date) >=
563 Trunc(Nvl(x_gl_date, SYSDATE))
564 ORDER BY GL_PS.period_year ASC,
565 GL_PS.period_num ASC,
566 GL_PS.quarter_num ASC)
567 where ROWNUM = 1;
568 -- Call custom hook to get the Ct's preferred GL Date.
569 PO_CUSTOM_FUNDS_PKG.gl_date(x_gl_date, x_gl_period);
570
571 IF po_log.d_proc THEN
572 PO_LOG.proc_begin(l_log_head, 'x_gl_date', x_gl_date);
573 PO_LOG.proc_begin(l_log_head, 'x_gl_period', x_gl_period);
574 END IF;
575
576 -- Call custom hook to get the Ct's preferred GL Date.
577 PO_CUSTOM_FUNDS_PKG.gl_date(x_gl_date, x_gl_period);
578 exception
579 when no_data_found then
580 -- No Data found means there exists no Earliest Open Period date,
581 -- which falls in near Future period.
582 BEGIN
583 -- 4th Check: Find out the Earliest Open Period date, which
584 -- falls in near Past period.
585 SELECT latest_period_name, latest_open_date
586 into x_gl_period, x_gl_date
587 FROM (
588
589 SELECT GL_PS.period_year,
590 GL_PS.period_num,
591 GL_PS.quarter_num,
592 gl_ps.period_name latest_period_name,
593 TRUNC(GL_PS.start_date) latest_open_date
594 FROM GL_PERIOD_STATUSES GL_PS,
595 GL_PERIOD_STATUSES PO_PS,
596 GL_SETS_OF_BOOKS SOB
597 WHERE SOB.set_of_books_id = l_sob_id
598 AND GL_PS.application_id = 101
599 AND PO_PS.application_id = 201
600 AND GL_PS.set_of_books_id = SOB.set_of_books_id --JOIN
601 AND PO_PS.set_of_books_id = SOB.set_of_books_id --JOIN
602 AND GL_PS.period_name = PO_PS.period_name --JOIN
603 AND GL_PS.adjustment_period_flag = 'N' -- not an adjusting period
604 AND ((l_validate_gl_period IN ('Y','R')
605 AND GL_PS.closing_status IN ('O', 'F')) OR (l_validate_gl_period = 'N'))
606 AND GL_PS.period_year <=
607 SOB.latest_encumbrance_year
608 AND PO_PS.closing_status = 'O' -- open
609 AND PO_PS.adjustment_period_flag = 'N' -- not an adjusting period
610 AND TRUNC(GL_PS.start_date) <
611 Trunc(Nvl(x_gl_date, SYSDATE))
612 ORDER BY GL_PS.period_year desc,
613 GL_PS.period_num desc,
614 GL_PS.quarter_num DESC)
615 where ROWNUM = 1;
616 -- Call custom hook to get the Ct's preferred GL Date.
617 PO_CUSTOM_FUNDS_PKG.gl_date(x_gl_date, x_gl_period);
618
619 IF po_log.d_proc THEN
620 PO_LOG.proc_begin(l_log_head, 'x_gl_date', x_gl_date);
621 PO_LOG.proc_begin(l_log_head,
622 'x_gl_period',
623 x_gl_period);
624 END IF;
625 exception
626 when others then
627 null;
628 end; -- 4th Check
629 end; -- 3rd Check
630 end; -- 2nd Check
631 END; -- 1st Check
632 end build_GL_Encumbered_Date;
633
634 -- GL Date Project#Start:
635 --Start of Comments
636 --Name: get_gl_date
637 --Pre-reqs:
638 -- None.
639 --Modifies:
640 -- None.
641 --Locks:
642 -- None.
643 --Function:
644 -- Derive proper GL date, when the profile PO: Validate GL Period has been
645 -- set to Redefault.
646 --Parameters:
647 --IN:
648 --x_sob_id
649 -- Set of books.
650 ----IN OUT:
651 --x_gl_date
652 -- Date, which needs to be replaced with a correct date, if not open.
653 --Notes:
654 -- This procedure was written for GL Date (CLM Phase 2) project.
655 -- The requirement is to derive a Latest Open Period's GL Date, if the
656 -- entered/present GL date is not in Open period. Present procedures
657 -- are not helpful the derive GL date for a set of distributions during
658 -- PDOI flow.
659 --Testing:
660 --
661 --End of Comments
662 -------------------------------------------------------------------------------
663 PROCEDURE get_gl_date(x_sob_id IN NUMBER,
664 x_gl_date IN OUT NOCOPY po_tbl_date) IS
665
666 l_log_head CONSTANT VARCHAR2(100) := g_log_head || ' GET_GL_DATE';
667 l_progress VARCHAR2(3) := '000';
668 l_gl_period VARCHAR2(15);
669 BEGIN
670
671 IF g_debug_stmt THEN
672 PO_DEBUG.debug_begin(l_log_head);
673 PO_DEBUG.debug_var(l_log_head, l_progress, 'x_sob_id', x_sob_id);
674 PO_DEBUG.debug_var(l_log_head, l_progress, 'x_gl_date', x_gl_date);
675 END IF;
676
677 FOR i IN 1 .. x_gl_date.COUNT LOOP
678 l_progress := '010';
679 build_GL_Encumbered_Date(l_sob_id => x_sob_id,
680 x_gl_date => x_gl_date(i),
681 x_gl_period => l_gl_period);
682 END LOOP;
683
684 l_progress := '020';
685
686 IF g_debug_stmt THEN
687 PO_DEBUG.debug_var(l_log_head, l_progress, 'x_gl_date', x_gl_date);
688 PO_DEBUG.debug_end(l_log_head);
689 END IF;
690
691 EXCEPTION
692 WHEN OTHERS THEN
693 IF g_debug_unexp THEN
694 PO_DEBUG.debug_exc(l_log_head, l_progress);
695 END IF;
696 RAISE;
697
698 END get_gl_date;
699 -- GL Date Project#End
700
701
702 -----------------------------------------------------------------------------
703 -- Initialize package variables.
704 -----------------------------------------------------------------------------
705
706 BEGIN
707
708 g_debug_stmt := PO_DEBUG.is_debug_stmt_on;
709 g_debug_unexp := PO_DEBUG.is_debug_unexp_on;
710
711 END PO_PERIODS_SV;
712
[Home] [Help]