[Home] [Help]
PACKAGE BODY: APPS.INV_MGD_POS_BUCKET_MDTR
Source
1 PACKAGE BODY INV_MGD_POS_BUCKET_MDTR AS
2 /* $Header: INVMPBKB.pls 115.4 2003/04/10 12:31:50 ghurli ship $ */
3 --+=======================================================================+
4 --| Copyright (c) 2000 Oracle Corporation |
5 --| Redwood Shores, CA, USA |
6 --| All rights reserved. |
7 --+=======================================================================+
8 --| FILENAME |
9 --| INVMPBKB.pls |
10 --| |
11 --| DESCRIPTION |
12 --| Inventory Position View and Export: Time Bucket Mediator |
13 --| HISTORY |
14 --| 09/07/2000 Paolo Juvara Created |
15 --+======================================================================*/
16
17 --===================
18 -- CONSTANTS
19 --===================
20 G_PKG_NAME CONSTANT VARCHAR2(30):= 'INV_MGD_POS_BUCKET_MDTR';
21
22
23 --===================
24 -- TYPES
25 --===================
26
27 TYPE g_context_rec_type IS RECORD
28 ( period_set_name VARCHAR2(15)
29 , period_type VARCHAR2(15)
30 , organization_id NUMBER
31 , bucket_size VARCHAR2(30)
32 );
33
34 TYPE g_period_rec_type IS RECORD
35 ( name VARCHAR2(15)
36 , start_date DATE
37 , end_date DATE
38 );
39
40
41 --===================
42 -- CURSOR
43 --===================
44
45 CURSOR g_period_crsr
46 ( p_period_set_name IN VARCHAR2
47 , p_period_type IN VARCHAR2
48 , p_date_from IN DATE
49 )
50 IS
51 SELECT
52 period_name
53 , start_date
54 , end_date
55 , start_date
56 FROM gl_periods
57 WHERE period_set_name = p_period_set_name
58 AND period_type = p_period_type
59 AND adjustment_period_flag = 'N'
60 AND end_date >= p_date_from
61 ORDER BY end_date;
62
63
64 --===================
65 -- PROCEDURES AND FUNCTIONS
66 --===================
67
68
69 --========================================================================
70 -- PROCEDURE : Get_Context PRIVATE
71 -- PARAMETERS: p_organization_id organization holding the calendar
72 -- p_bucket_size bucket size
73 -- x_context_rec context
74 -- COMMENT : retrieves context information
75 --========================================================================
76 PROCEDURE Get_Context
77 ( p_organization_id IN NUMBER
78 , p_bucket_size IN VARCHAR2
79 , x_context_rec OUT NOCOPY g_context_rec_type
80 )
81 IS
82
83 l_api_name CONSTANT VARCHAR2(30):= 'Get_Context';
84
85 BEGIN
86
87 INV_MGD_POS_UTIL.Log
88 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
89 , p_msg => '> '||G_PKG_NAME||'.'||l_api_name
90 );
91
92 x_context_rec.organization_id := p_organization_id;
93 x_context_rec.bucket_size := p_bucket_size;
94 SELECT
95 gsob.period_set_name
96 , gsob.accounted_period_type
97 INTO
98 x_context_rec.period_set_name
99 , x_context_rec.period_type
100 FROM gl_sets_of_books gsob
101 , org_organization_definitions ood
102 WHERE gsob.set_of_books_id = ood.set_of_books_id
103 AND organization_id = p_organization_id;
104
105 INV_MGD_POS_UTIL.Log
106 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
107 , p_msg => '< '||G_PKG_NAME||'.'||l_api_name
108 );
109
110 END Get_Context;
111
112 --========================================================================
113 -- PROCEDURE : Open_Cursor PRIVATE
114 -- PARAMETERS: p_context_rec context
115 -- p_date_from date from
116 -- COMMENT : open cursors
117 --========================================================================
118 PROCEDURE Open_Cursor
119 ( p_context_rec IN g_context_rec_type
120 , p_date_from IN DATE
121 )
122 IS
123
124 l_api_name CONSTANT VARCHAR2(30):= 'Open_Cursor';
125
126 BEGIN
127
128 INV_MGD_POS_UTIL.Log
129 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
130 , p_msg => '> '||G_PKG_NAME||'.'||l_api_name
131 );
132
133 IF p_context_rec.bucket_size = 'PERIOD' THEN
134 OPEN g_period_crsr
135 ( p_period_set_name => p_context_rec.period_set_name
136 , p_period_type => p_context_rec.period_type
137 , p_date_from => p_date_from
138 );
139 END IF;
140
141 INV_MGD_POS_UTIL.Log
142 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
143 , p_msg => '< '||G_PKG_NAME||'.'||l_api_name
144 );
145
146 END Open_Cursor;
147
148 --========================================================================
149 -- PROCEDURE : Close_Cursor PRIVATE
150 -- COMMENT : close cursors
151 --========================================================================
152 PROCEDURE Close_Cursor
153 IS
154
155 l_api_name CONSTANT VARCHAR2(30):= 'Close_Cursor';
156
157 BEGIN
158
159 INV_MGD_POS_UTIL.Log
160 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
161 , p_msg => '> '||G_PKG_NAME||'.'||l_api_name
162 );
163
164 IF g_period_crsr%ISOPEN THEN
165 CLOSE g_period_crsr;
166 END IF;
167
168 INV_MGD_POS_UTIL.Log
169 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
170 , p_msg => '< '||G_PKG_NAME||'.'||l_api_name
171 );
172
173 END Close_Cursor;
174
175
176 --========================================================================
177 -- FUNCTION : Get_Increment PRIVATE
178 -- PARAMETERS: p_bucket_size PERIOD, WEEK, DAY or HOUR
179 -- COMMENT : returns increment value in days (null if bucket is PERIOD
180 --========================================================================
181 FUNCTION Get_Increment
182 ( p_bucket_size IN VARCHAR2
183 ) RETURN NUMBER
184 IS
185
186 l_api_name CONSTANT VARCHAR2(30):= 'Get_Increment';
187 l_increment NUMBER;
188
189 BEGIN
190
191 INV_MGD_POS_UTIL.Log
192 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
193 , p_msg => '> '||G_PKG_NAME||'.'||l_api_name
194 );
195
196 IF p_bucket_size = 'HOUR' THEN
197 l_increment := TO_NUMBER
198 ( TO_DATE('20000101 01:00:00', 'YYYYMMDD HH24:MI:SS') -
199 TO_DATE('20000101 00:00:00', 'YYYYMMDD HH24:MI:SS')
200 );
201 ELSIF p_bucket_size = 'DAY' THEN
202 l_increment := 1;
203 ELSIF p_bucket_size = 'WEEK' THEN
204 l_increment := 7;
205 ELSIF p_bucket_size = 'PERIOD' THEN
206 l_increment := NULL;
207 END IF;
208
209 INV_MGD_POS_UTIL.Log
210 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
211 , p_msg => '< '||G_PKG_NAME||'.'||l_api_name
212 );
213
214 RETURN l_Increment;
215
216 END Get_Increment;
217
218 --========================================================================
219 -- PROCEDURE : Get_Start_Info PRIVATE
220 -- PARAMETERS: p_context_rec context
221 -- p_date_from date range from
222 -- x_start_date start date
223 -- x_period_rec period info
224 -- COMMENT : determines the start bucket information
225 --========================================================================
226 PROCEDURE Get_Start_Info
227 ( p_context_rec IN g_context_rec_type
228 , p_date_from IN DATE
229 , x_start_date OUT NOCOPY DATE
230 , x_period_rec OUT NOCOPY g_period_rec_type
231 )
232 IS
233
234 l_api_name CONSTANT VARCHAR2(30):= 'Get_Start_Info';
235
236 BEGIN
237
238 INV_MGD_POS_UTIL.Log
239 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
240 , p_msg => '> '||G_PKG_NAME||'.'||l_api_name
241 );
242
243 IF p_context_rec.bucket_size = 'HOUR' THEN
244 x_start_date := TRUNC(p_date_from, 'HH');
245 ELSIF p_context_rec.bucket_size = 'DAY' THEN
246 x_start_date := TRUNC(p_date_from, 'DD');
247 ELSIF p_context_rec.bucket_size = 'WEEK' THEN
248 x_start_date := TRUNC(p_date_from, 'IW');
249 ELSIF p_context_rec.bucket_size = 'PERIOD' THEN
250 FETCH g_period_crsr
251 INTO
252 x_period_rec.name
253 , x_period_rec.start_date
254 , x_period_rec.end_date
255 , x_start_date;
256 END IF;
257
258 INV_MGD_POS_UTIL.Log
259 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
260 , p_msg => '< '||G_PKG_NAME||'.'||l_api_name
261 );
262
263 END Get_Start_Info;
264
265 --========================================================================
266 -- PROCEDURE : Get_Bucket PRIVATE
267 -- PARAMETERS: p_context context
268 -- p_start_date bucket begin
269 -- p_period_rec period info (only for PERIOD)
270 -- x_bucket_rec bucket
271 -- COMMENT : build a bucket
272 --========================================================================
273 PROCEDURE Get_Bucket
274 ( p_context_rec IN g_context_rec_type
275 , p_start_date IN DATE
276 , p_period_rec IN g_period_rec_type
277 , x_bucket_rec OUT NOCOPY INV_MGD_POS_UTIL.bucket_rec_type
278 )
279 IS
280
281 l_api_name CONSTANT VARCHAR2(30):= 'Get_Bucket';
282
283 BEGIN
284
285 INV_MGD_POS_UTIL.Log
286 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
287 , p_msg => '> '||G_PKG_NAME||'.'||l_api_name
288 );
289
290 IF p_context_rec.bucket_size = 'HOUR' THEN
291 x_bucket_rec.name := FND_DATE.date_to_chardate(p_start_date) ||
292 ' - ' ||
293 TO_CHAR(p_start_date, 'HH24');
294 x_bucket_rec.start_date := p_start_date;
295 x_bucket_rec.end_date := p_start_date +
296 Get_Increment(p_context_rec.bucket_size);
297 ELSIF p_context_rec.bucket_size = 'DAY' THEN
298 x_bucket_rec.name := FND_DATE.date_to_chardate(p_start_date);
299 x_bucket_rec.start_date := p_start_date;
300 x_bucket_rec.end_date := p_start_date +
301 Get_Increment(p_context_rec.bucket_size);
302 ELSIF p_context_rec.bucket_size = 'WEEK' THEN
303 x_bucket_rec.name := TO_CHAR(p_start_date, 'IW');
304 x_bucket_rec.start_date := p_start_date;
305 x_bucket_rec.end_date := p_start_date +
306 Get_Increment(p_context_rec.bucket_size);
307 ELSIF p_context_rec.bucket_size = 'PERIOD' THEN
308 x_bucket_rec.name := p_period_rec.name;
309 x_bucket_rec.start_date := p_period_rec.start_date;
310 x_bucket_rec.end_date := p_period_rec.end_date + 1; /*2872802*/
311 END IF;
312 x_bucket_rec.bucket_size := p_context_rec.bucket_size;
313
314 INV_MGD_POS_UTIL.Log
315 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
316 , p_msg => '< '||G_PKG_NAME||'.'||l_api_name
317 );
318
319 END Get_Bucket;
320
321 --========================================================================
322 -- PROCEDURE : Increment_Start_Info PRIVATE
323 -- PARAMETERS: p_context_rec PERIOD, WEEK, DAY or HOUR
324 -- x_start_date new bucket begin
325 -- x_period_rec period info (only for PERIOD)
326 -- COMMENT : increment the start information for the next bucket
327 --========================================================================
328 PROCEDURE Increment_Start_Info
329 ( p_context_rec IN g_context_rec_type
330 , x_start_date IN OUT NOCOPY DATE
331 , x_period_rec IN OUT NOCOPY g_period_rec_type
332 )
333 IS
334
335 l_api_name CONSTANT VARCHAR2(30):= 'Increment_Start_Info';
336
337 BEGIN
338
339 INV_MGD_POS_UTIL.Log
340 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
341 , p_msg => '> '||G_PKG_NAME||'.'||l_api_name
342 );
343
344 IF p_context_rec.bucket_size = 'PERIOD' THEN
345 FETCH g_period_crsr
346 INTO
347 x_period_rec.name
348 , x_period_rec.start_date
349 , x_period_rec.end_date
350 , x_start_date;
351 INV_MGD_POS_UTIL.Log
352 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
353 , p_msg => 'start date:'||TO_CHAR(x_period_rec.start_date, 'YYYY/MM/DD HH24:MI:SS')
354 );
355 ELSE
356 x_start_date := x_start_date + Get_Increment(p_context_rec.bucket_size);
357 END IF;
358
359 INV_MGD_POS_UTIL.Log
360 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
361 , p_msg => '< '||G_PKG_NAME||'.'||l_api_name
362 );
363
364 END Increment_Start_Info;
365
366
367 --========================================================================
368 -- PROCEDURE : Build_Bucket_List PUBLIC
369 -- PARAMETERS: p_init_msg_list FND_API.G_TRUE to reset list
370 -- x_return_status return status
371 -- x_msg_count number of messages in the list
372 -- x_msg_data text of messages
373 -- p_organization_id organization holding the calendar
374 -- p_date_from date range from
375 -- p_date_to date range to
376 -- p_bucket_size PERIOD, WEEK, DAY or HOUR
377 -- x_bucket_tbl list of buckets
378 -- COMMENT : Builds the list of buckets in the given date range
379 -- PRE-COND : p_date_to > p_date_from
380 -- POST-COND : x_bucket_tbl is not empty
381 --========================================================================
382 PROCEDURE Build_Bucket_List
383 ( p_organization_id IN NUMBER
384 , p_date_from IN DATE
385 , p_date_to IN DATE
386 , p_bucket_size IN VARCHAR2
387 , x_bucket_tbl IN OUT NOCOPY INV_MGD_POS_UTIL.bucket_tbl_type
388 )
389 IS
390
391 l_api_name CONSTANT VARCHAR2(30):= 'Build_Bucket_List';
392 l_context_rec g_context_rec_type;
393 l_start_date DATE;
394 l_period_rec g_period_rec_type;
395 l_bucket_rec INV_MGD_POS_UTIL.bucket_rec_type;
396
397 BEGIN
398
399 INV_MGD_POS_UTIL.Log
400 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
401 , p_msg => '> '||G_PKG_NAME||'.'||l_api_name
402 );
403
404 -- Initialize organization list
405 x_bucket_tbl.DELETE;
406
407 -- retrieve context info
408 Get_Context
409 ( p_organization_id => p_organization_id
410 , p_bucket_size => p_bucket_size
411 , x_context_rec => l_context_rec
412 );
413
414 -- open period cursor
415 Open_Cursor
416 ( p_context_rec => l_context_rec
417 , p_date_from => p_date_from
418 );
419
420 -- determine begin first bucket
421 Get_Start_Info
422 ( p_context_rec => l_context_rec
423 , p_date_from => p_date_from
424 , x_start_date => l_start_date
425 , x_period_rec => l_period_rec
426 );
427
428 INV_MGD_POS_UTIL.Log
429 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
430 , p_msg => 'initial start date:'||TO_CHAR(l_start_date, 'YYYY/MM/DD HH24:MI:SS')
431 );
432
433 LOOP
434
435 Get_Bucket
436 ( p_context_rec => l_context_rec
437 , p_start_date => l_start_date
438 , p_period_rec => l_period_rec
439 , x_bucket_rec => l_bucket_rec
440 );
441
442 x_bucket_tbl(x_bucket_tbl.COUNT + 1) := l_bucket_rec;
443
444 INV_MGD_POS_UTIL.Log
445 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
446 , p_msg => 'bucket_rec.name:'||l_bucket_rec.name
447 );
448
449 Increment_Start_Info
450 ( p_context_rec => l_context_rec
451 , x_start_date => l_start_date
452 , x_period_rec => l_period_rec
453 );
454
455 INV_MGD_POS_UTIL.Log
456 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
457 , p_msg => 'new start date:'||TO_CHAR(l_start_date, 'YYYY/MM/DD HH24:MI:SS')
458 );
459
460 EXIT WHEN l_start_date > p_date_to;
461
462 END LOOP;
463
464 -- close period cursor
465 Close_Cursor;
466
467 INV_MGD_POS_UTIL.Log
468 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
469 , p_msg => '< '||G_PKG_NAME||'.'||l_api_name
470 );
471
472 END Build_Bucket_List;
473
474
475 END INV_MGD_POS_BUCKET_MDTR;