[Home] [Help]
PACKAGE BODY: APPS.INV_MGD_POS_BUCKET_MDTR
Source
4 --| Copyright (c) 2000 Oracle Corporation |
1 PACKAGE BODY INV_MGD_POS_BUCKET_MDTR AS
2 /* $Header: INVMPBKB.pls 120.1 2011/02/01 07:09:23 ksivasa ship $ */
3 --+=======================================================================+
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
99 , x_context_rec.period_type
96 , gsob.accounted_period_type
97 INTO
98 x_context_rec.period_set_name
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
262
259 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
260 , p_msg => '< '||G_PKG_NAME||'.'||l_api_name
261 );
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 --#11703554 Character date international calendar support Added calendar_aware in date function arguments
291 IF p_context_rec.bucket_size = 'HOUR' THEN
292 x_bucket_rec.name := FND_DATE.date_to_chardate(p_start_date,calendar_aware=>FND_DATE.calendar_aware_alt) ||
293 ' - ' ||
294 TO_CHAR(p_start_date, 'HH24');
295 x_bucket_rec.start_date := p_start_date;
296 x_bucket_rec.end_date := p_start_date +
297 Get_Increment(p_context_rec.bucket_size);
298 ELSIF p_context_rec.bucket_size = 'DAY' THEN
299 x_bucket_rec.name := FND_DATE.date_to_chardate(p_start_date,calendar_aware=>FND_DATE.calendar_aware_alt);
300 x_bucket_rec.start_date := p_start_date;
301 x_bucket_rec.end_date := p_start_date +
302 Get_Increment(p_context_rec.bucket_size);
303 ELSIF p_context_rec.bucket_size = 'WEEK' THEN
304 x_bucket_rec.name := TO_CHAR(p_start_date, 'IW');
305 x_bucket_rec.start_date := p_start_date;
306 x_bucket_rec.end_date := p_start_date +
307 Get_Increment(p_context_rec.bucket_size);
308 ELSIF p_context_rec.bucket_size = 'PERIOD' THEN
309 x_bucket_rec.name := p_period_rec.name;
310 x_bucket_rec.start_date := p_period_rec.start_date;
311 x_bucket_rec.end_date := p_period_rec.end_date + 1; /*2872802*/
312 END IF;
313 x_bucket_rec.bucket_size := p_context_rec.bucket_size;
314
315 INV_MGD_POS_UTIL.Log
316 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
317 , p_msg => '< '||G_PKG_NAME||'.'||l_api_name
318 );
319
320 END Get_Bucket;
321
322 --========================================================================
323 -- PROCEDURE : Increment_Start_Info PRIVATE
324 -- PARAMETERS: p_context_rec PERIOD, WEEK, DAY or HOUR
325 -- x_start_date new bucket begin
326 -- x_period_rec period info (only for PERIOD)
327 -- COMMENT : increment the start information for the next bucket
328 --========================================================================
329 PROCEDURE Increment_Start_Info
330 ( p_context_rec IN g_context_rec_type
331 , x_start_date IN OUT NOCOPY DATE
332 , x_period_rec IN OUT NOCOPY g_period_rec_type
333 )
334 IS
335
336 l_api_name CONSTANT VARCHAR2(30):= 'Increment_Start_Info';
337
338 BEGIN
339
340 INV_MGD_POS_UTIL.Log
341 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
342 , p_msg => '> '||G_PKG_NAME||'.'||l_api_name
343 );
344
345 IF p_context_rec.bucket_size = 'PERIOD' THEN
346 FETCH g_period_crsr
347 INTO
348 x_period_rec.name
349 , x_period_rec.start_date
350 , x_period_rec.end_date
351 , x_start_date;
352 INV_MGD_POS_UTIL.Log
353 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
354 , p_msg => 'start date:'||TO_CHAR(x_period_rec.start_date, 'YYYY/MM/DD HH24:MI:SS')
355 );
356 ELSE
357 x_start_date := x_start_date + Get_Increment(p_context_rec.bucket_size);
358 END IF;
359
360 INV_MGD_POS_UTIL.Log
361 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
362 , p_msg => '< '||G_PKG_NAME||'.'||l_api_name
363 );
364
365 END Increment_Start_Info;
366
367
368 --========================================================================
372 -- x_msg_count number of messages in the list
369 -- PROCEDURE : Build_Bucket_List PUBLIC
370 -- PARAMETERS: p_init_msg_list FND_API.G_TRUE to reset list
371 -- x_return_status return status
373 -- x_msg_data text of messages
374 -- p_organization_id organization holding the calendar
375 -- p_date_from date range from
376 -- p_date_to date range to
377 -- p_bucket_size PERIOD, WEEK, DAY or HOUR
378 -- x_bucket_tbl list of buckets
379 -- COMMENT : Builds the list of buckets in the given date range
380 -- PRE-COND : p_date_to > p_date_from
381 -- POST-COND : x_bucket_tbl is not empty
382 --========================================================================
383 PROCEDURE Build_Bucket_List
384 ( p_organization_id IN NUMBER
385 , p_date_from IN DATE
386 , p_date_to IN DATE
387 , p_bucket_size IN VARCHAR2
388 , x_bucket_tbl IN OUT NOCOPY INV_MGD_POS_UTIL.bucket_tbl_type
389 )
390 IS
391
392 l_api_name CONSTANT VARCHAR2(30):= 'Build_Bucket_List';
393 l_context_rec g_context_rec_type;
394 l_start_date DATE;
395 l_period_rec g_period_rec_type;
396 l_bucket_rec INV_MGD_POS_UTIL.bucket_rec_type;
397
398 BEGIN
399
400 INV_MGD_POS_UTIL.Log
401 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
402 , p_msg => '> '||G_PKG_NAME||'.'||l_api_name
403 );
404
405 -- Initialize organization list
406 x_bucket_tbl.DELETE;
407
408 -- retrieve context info
409 Get_Context
410 ( p_organization_id => p_organization_id
411 , p_bucket_size => p_bucket_size
412 , x_context_rec => l_context_rec
413 );
414
415 -- open period cursor
416 Open_Cursor
417 ( p_context_rec => l_context_rec
418 , p_date_from => p_date_from
419 );
420
421 -- determine begin first bucket
422 Get_Start_Info
423 ( p_context_rec => l_context_rec
424 , p_date_from => p_date_from
425 , x_start_date => l_start_date
426 , x_period_rec => l_period_rec
427 );
428
429 INV_MGD_POS_UTIL.Log
430 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
431 , p_msg => 'initial start date:'||TO_CHAR(l_start_date, 'YYYY/MM/DD HH24:MI:SS')
432 );
433
434 LOOP
435
436 Get_Bucket
437 ( p_context_rec => l_context_rec
438 , p_start_date => l_start_date
439 , p_period_rec => l_period_rec
440 , x_bucket_rec => l_bucket_rec
441 );
442
443 x_bucket_tbl(x_bucket_tbl.COUNT + 1) := l_bucket_rec;
444
445 INV_MGD_POS_UTIL.Log
446 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
447 , p_msg => 'bucket_rec.name:'||l_bucket_rec.name
448 );
449
450 Increment_Start_Info
451 ( p_context_rec => l_context_rec
452 , x_start_date => l_start_date
453 , x_period_rec => l_period_rec
454 );
455
456 INV_MGD_POS_UTIL.Log
457 ( p_priority => INV_MGD_POS_UTIL.G_LOG_STATEMENT
458 , p_msg => 'new start date:'||TO_CHAR(l_start_date, 'YYYY/MM/DD HH24:MI:SS')
459 );
460
461 EXIT WHEN l_start_date > p_date_to;
462
463 END LOOP;
464
465 -- close period cursor
466 Close_Cursor;
467
468 INV_MGD_POS_UTIL.Log
469 ( p_priority => INV_MGD_POS_UTIL.G_LOG_PROCEDURE
470 , p_msg => '< '||G_PKG_NAME||'.'||l_api_name
471 );
472
473 END Build_Bucket_List;
474
475
476 END INV_MGD_POS_BUCKET_MDTR;