1 PACKAGE BODY PO_PERIODS_SV as
2 -- $Header: POXCOPEB.pls 115.7 2003/12/23 00:27:39 spangulu ship $
3
4
5
6
7 -----------------------------------------------------------------------------
8 -- Declare private package types.
9 -----------------------------------------------------------------------------
10
11 /* Bug 3292931: A table of ROWID is not allowed in an 8i database. Instead,
12 * use a table of VARCHAR2 and use the ROWIDTOCHAR() and CHARTOROWID() functions.
13 * A ROWID is compatible with a VARCHAR2 of size 18.
14 */
15
16 --TYPE g_tbl_rowid IS TABLE OF ROWID;
17 TYPE g_tbl_rowid IS TABLE OF VARCHAR2(18);
18
19
20
21
22 -----------------------------------------------------------------------------
23 -- Declare private package variables.
24 -----------------------------------------------------------------------------
25
26 -- Debugging
27
28 g_pkg_name CONSTANT
29 VARCHAR2(30)
30 := 'PO_PERIODS_SV'
31 ;
32 g_log_head CONSTANT
33 VARCHAR2(50)
34 := 'po.plsql.' || g_pkg_name || '.'
35 ;
36
37 g_debug_stmt
38 BOOLEAN
39 ;
40 g_debug_unexp
41 BOOLEAN
42 ;
43
44
45
46
47 -----------------------------------------------------------------------------
48 -- Define procedures.
49 -----------------------------------------------------------------------------
50
51
52
53
54 -------------------------------------------------------------------------------
55 --Start of Comments
56 --Name: get_period_info
57 --Pre-reqs:
58 -- None.
59 --Modifies:
60 -- None.
61 --Locks:
62 -- None.
63 --Function:
64 -- Retrieves the GL period info for the given dates.
65 -- Period information will only be found if
66 -- the date falls within a usable period (valid for both GL and PO).
67 -- If a usable period is not found for any of the given dates,
68 -- the x_invalid_period_flag will be FND_API.g_TRUE,
69 -- and the other out parameters corresponding to the date will be NULL.
70 --Parameters:
71 --IN:
72 --p_roll_logic
73 -- Intended to be flexible to use roll-forward/roll-backward logic
74 -- for encumbrance. Currently not supported.
75 -- Use NULL.
76 --p_set_of_books_id
77 -- If the set of books is not passed, it will be derived from
78 -- FINANCIALS_SYSTEM_PARAMETERS.
79 --p_date_tbl
80 -- Dates of which to find the periods.
81 --OUT:
82 -- These correspond to the dates in p_date_tbl.
83 --x_period_name_tbl
84 --x_period_year_tbl
85 --x_period_num_tbl
86 --x_quarter_num_tbl
87 --
88 --x_invalid_period_flag
89 -- Indicates whether or not usable periods were found for each of the
90 -- given dates.
91 -- FND_API.g_TRUE a usable period was not found for at least one date
92 -- FND_API.g_FALSE usable periods were found for each date
93 --Testing:
94 --
95 --End of Comments
96 -------------------------------------------------------------------------------
97 PROCEDURE get_period_info(
98 p_roll_logic IN VARCHAR2
99 , p_set_of_books_id IN NUMBER
100 , p_date_tbl IN po_tbl_date
101 , x_period_name_tbl OUT NOCOPY po_tbl_varchar30
102 , x_period_year_tbl OUT NOCOPY po_tbl_number
103 , x_period_num_tbl OUT NOCOPY po_tbl_number
104 , x_quarter_num_tbl OUT NOCOPY po_tbl_number
105 , x_invalid_period_flag OUT NOCOPY VARCHAR2
106 )
107 IS
108
109 l_log_head CONSTANT VARCHAR2(100) := g_log_head||'GET_PERIOD_INFO';
110 l_progress VARCHAR2(3) := '000';
111
112 l_set_of_books_id NUMBER;
113
114 l_date_key NUMBER;
115 l_rowid_tbl g_tbl_rowid;
116
117 l_no_dates_exc EXCEPTION;
118
119 BEGIN
120
121 IF g_debug_stmt THEN
122 PO_DEBUG.debug_begin(l_log_head);
123 PO_DEBUG.debug_var(l_log_head,l_progress,'p_roll_logic', p_roll_logic);
124 PO_DEBUG.debug_var(l_log_head,l_progress,'p_set_of_books_id', p_set_of_books_id);
125 PO_DEBUG.debug_var(l_log_head,l_progress,'p_date_tbl', p_date_tbl);
126 END IF;
127
128 l_progress := '010';
129
130 IF (p_date_tbl IS NULL) THEN
131 RAISE l_no_dates_exc;
132 ELSIF (p_date_tbl.COUNT = 0) THEN
133 RAISE l_no_dates_exc;
134 END IF;
135
136 l_progress := '015';
137
138 -- Get the set of books id.
139
140 IF (p_set_of_books_id IS NULL) THEN
141
142 l_progress := '020';
143 IF g_debug_stmt THEN
144 PO_DEBUG.debug_stmt(l_log_head,l_progress,'set of books is NULL');
145 END IF;
146
147 SELECT FSP.set_of_books_id
148 INTO l_set_of_books_id
149 FROM FINANCIALS_SYSTEM_PARAMETERS FSP
150 ;
151
152 l_progress := '030';
153
154 ELSE
155
156 l_progress := '040';
157 IF g_debug_stmt THEN
158 PO_DEBUG.debug_stmt(l_log_head,l_progress,'set of books passed');
159 END IF;
160
161 l_set_of_books_id := p_set_of_books_id;
162
163 l_progress := '050';
164
165 END IF;
166
167 l_progress := '060';
168
169 -- Use the scratchpad to avoid PL/SQL limitations.
170
171 SELECT PO_SESSION_GT_S.nextval
172 INTO l_date_key
173 FROM DUAL
174 ;
175
176 l_progress := '070';
177
178 -----------------------------------------
179 -- PO_SESSION_GT column mapping
180 --
181 -- date1 GL date
182 -- char1 period_name
183 -- num1 period_year
184 -- num2 period_num
185 -- num3 quarter_num
186 -----------------------------------------
187
188 /* Bug 3292931: A table of ROWID is not allowed in an 8i database. Instead,
189 * use a table of VARCHAR2 and use the ROWIDTOCHAR() and CHARTOROWID() functions.
190 */
191
192 FORALL i IN 1 .. p_date_tbl.COUNT
193 INSERT INTO PO_SESSION_GT ( key, date1 )
194 VALUES ( l_date_key, p_date_tbl(i) )
195 RETURNING ROWIDTOCHAR(rowid)
196 BULK COLLECT INTO l_rowid_tbl
197 ;
198
199 l_progress := '080';
200
201 /* Bug 3292931: A table of ROWID is not allowed in an 8i database. Instead,
202 * use a table of VARCHAR2 and use the ROWIDTOCHAR() and CHARTOROWID() functions.
203 * Changes made below to how l_rowid_tbl is interpreted.
204 */
205
206 FORALL i IN 1 .. l_rowid_tbl.COUNT
207 UPDATE PO_SESSION_GT SES
208 SET
209 ( char1 -- period_name
210 , num1 -- period_year
211 , num2 -- period_num
212 , num3 -- quarter_num
213 )
214 =
215 ( SELECT
216 GL_PS.period_name
217 , GL_PS.period_year
218 , GL_PS.period_num
219 , GL_PS.quarter_num
220 FROM
221 GL_PERIOD_STATUSES GL_PS
222 , GL_PERIOD_STATUSES PO_PS
223 , GL_SETS_OF_BOOKS SOB
224 WHERE SOB.set_of_books_id = l_set_of_books_id
225 AND GL_PS.application_id = 101
226 AND PO_PS.application_id = 201
227 AND GL_PS.set_of_books_id = SOB.set_of_books_id --JOIN
228 AND PO_PS.set_of_books_id = SOB.set_of_books_id --JOIN
229 AND GL_PS.period_name = PO_PS.period_name --JOIN
230 -- GL period conditions
231 AND GL_PS.closing_status IN ('O','F') -- open or future-enterable
232 AND GL_PS.adjustment_period_flag = 'N' -- not an adjusting period
233 AND GL_PS.period_year <= SOB.latest_encumbrance_year
234 -- PO period conditions
235 AND PO_PS.closing_status = 'O' -- open
236 AND PO_PS.adjustment_period_flag = 'N' -- not an adjusting period
237 -- Date logic (to include roll-forward, roll-backward?)
238 -- See PO_ENCUMBRANCE_PREPROCESSING.find_open_period
239 AND TRUNC(SES.date1)
240 BETWEEN TRUNC(GL_PS.start_date) AND TRUNC(GL_PS.end_date)
241 )
242 WHERE SES.rowid = CHARTOROWID(l_rowid_tbl(i))
243 RETURNING
244 SES.char1 -- period_name
245 , SES.num1 -- period_year
246 , SES.num2 -- period_num
247 , SES.num3 -- quarter_num
248 BULK COLLECT INTO
249 x_period_name_tbl
250 , x_period_year_tbl
251 , x_period_num_tbl
252 , x_quarter_num_tbl
253 ;
254
255 l_progress := '100';
256
257 -- Figure out if any periods were not found.
258
259 x_invalid_period_flag := FND_API.G_FALSE;
260
261 BEGIN
262
263 l_progress := '110';
264
265 SELECT FND_API.G_TRUE
266 INTO x_invalid_period_flag
267 FROM PO_SESSION_GT SES
268 WHERE SES.key = l_date_key
269 AND SES.char1 IS NULL
270 AND rownum = 1
271 ;
272
273 l_progress := '120';
274
275 EXCEPTION
276 WHEN NO_DATA_FOUND THEN
277 l_progress := '130';
278 END;
279
280 IF g_debug_stmt THEN
281 PO_DEBUG.debug_var(l_log_head,l_progress,'x_invalid_period_flag',x_invalid_period_flag);
282 END IF;
283
284 l_progress := '900';
285
286 IF g_debug_stmt THEN
287 PO_DEBUG.debug_var(l_log_head,l_progress,'x_period_name_tbl', x_period_name_tbl);
288 PO_DEBUG.debug_var(l_log_head,l_progress,'x_period_year_tbl', x_period_year_tbl);
289 PO_DEBUG.debug_var(l_log_head,l_progress,'x_period_num_tbl', x_period_num_tbl);
290 PO_DEBUG.debug_var(l_log_head,l_progress,'x_quarter_num_tbl', x_quarter_num_tbl);
291 PO_DEBUG.debug_var(l_log_head,l_progress,'x_invalid_period_flag',x_invalid_period_flag);
292 PO_DEBUG.debug_end(l_log_head);
293 END IF;
294
295 EXCEPTION
296
297 WHEN l_no_dates_exc THEN
298 IF g_debug_stmt THEN
299 PO_DEBUG.debug_stmt(l_log_head,l_progress,'l_no_dates_exc: Empty date table.');
300 END IF;
301
302 l_progress := '910';
303
304 x_period_name_tbl := po_tbl_varchar30();
305 x_period_year_tbl := po_tbl_number();
306 x_period_num_tbl := po_tbl_number();
307 x_quarter_num_tbl := po_tbl_number();
308 x_invalid_period_flag := FND_API.g_FALSE;
309
310 l_progress := '912';
311
312 IF g_debug_stmt THEN
313 PO_DEBUG.debug_var(l_log_head,l_progress,'x_period_name_tbl', x_period_name_tbl);
314 PO_DEBUG.debug_var(l_log_head,l_progress,'x_period_year_tbl', x_period_year_tbl);
315 PO_DEBUG.debug_var(l_log_head,l_progress,'x_period_num_tbl', x_period_num_tbl);
316 PO_DEBUG.debug_var(l_log_head,l_progress,'x_quarter_num_tbl', x_quarter_num_tbl);
317 PO_DEBUG.debug_var(l_log_head,l_progress,'x_invalid_period_flag',x_invalid_period_flag);
318 PO_DEBUG.debug_end(l_log_head);
319 END IF;
320
321 WHEN OTHERS THEN
322 IF g_debug_unexp THEN
323 PO_DEBUG.debug_exc(l_log_head,l_progress);
324 END IF;
325 RAISE;
326
327 END get_period_info;
328
329
330
331
332 -------------------------------------------------------------------------------
333 --Start of Comments
334 --Name: get_period_name
335 --Pre-reqs:
336 -- None.
337 --Modifies:
338 -- None.
339 --Locks:
340 -- None.
341 --Function:
342 -- Retrieves the GL period name for the given date,
343 -- if the date is in a usable period (valid for GL and PO).
344 --Parameters:
345 --IN:
346 --x_sob_id
347 -- Set of books.
348 --x_gl_date
349 -- Date for which to find the period name.
350 --OUT:
351 --x_gl_period
352 -- The period name corresponding to the given date.
353 --Notes:
354 -- This procedure was refactored in FPJ to call the more generalized
355 -- procedure get_period_info. However, the parameter names were
356 -- not changed to meet standards, as that may have impacted calling code.
357 --Testing:
358 --
359 --End of Comments
360 -------------------------------------------------------------------------------
361 PROCEDURE get_period_name(
362 x_sob_id IN NUMBER
363 , x_gl_date IN DATE
364 , x_gl_period OUT NOCOPY VARCHAR2
365 )
366 IS
367
368 l_log_head CONSTANT VARCHAR2(100) := g_log_head||'GET_PERIOD_NAME';
369 l_progress VARCHAR2(3) := '000';
370
371 l_period_name_tbl po_tbl_varchar30;
372 l_period_year_tbl po_tbl_number;
373 l_period_num_tbl po_tbl_number;
374 l_quarter_num_tbl po_tbl_number;
375 l_invalid_period_flag VARCHAR2(1);
376
377 BEGIN
378
379 IF g_debug_stmt THEN
380 PO_DEBUG.debug_begin(l_log_head);
381 PO_DEBUG.debug_var(l_log_head,l_progress,'x_sob_id',x_sob_id);
382 PO_DEBUG.debug_var(l_log_head,l_progress,'x_gl_date',x_gl_date);
383 END IF;
384
385 l_progress := '010';
386
387 get_period_info(
388 p_roll_logic => NULL
389 , p_set_of_books_id => x_sob_id
390 , p_date_tbl => po_tbl_date( x_gl_date )
391 , x_period_name_tbl => l_period_name_tbl
392 , x_period_year_tbl => l_period_year_tbl
393 , x_period_num_tbl => l_period_num_tbl
394 , x_quarter_num_tbl => l_quarter_num_tbl
395 , x_invalid_period_flag => l_invalid_period_flag
396 );
397
398 l_progress := '020';
399
400 x_gl_period := l_period_name_tbl(1);
401
402 IF g_debug_stmt THEN
403 PO_DEBUG.debug_var(l_log_head,l_progress,'x_gl_period',x_gl_period);
404 PO_DEBUG.debug_end(l_log_head);
405 END IF;
406
407 EXCEPTION
408 WHEN OTHERS THEN
409 IF g_debug_unexp THEN
410 PO_DEBUG.debug_exc(l_log_head,l_progress);
411 END IF;
412 RAISE;
413
414 END get_period_name;
415
416
417
418
419 -----------------------------------------------------------------------------
420 -- Initialize package variables.
421 -----------------------------------------------------------------------------
422
423 BEGIN
424
425 g_debug_stmt := PO_DEBUG.is_debug_stmt_on;
426 g_debug_unexp := PO_DEBUG.is_debug_unexp_on;
427
428
429 END PO_PERIODS_SV;
[Home] [Help]