1 PACKAGE BODY INV_LE_TIMEZONE_PUB as
2 /*$Header: INVTZLEB.pls 120.2 2006/01/24 16:54:39 fdubois noship $ */
3
4 --=============================================================================
5 -- GLOBAL VARIABLES
6 --=============================================================================
7 -- G_ENABLE_LE_TIMEZONE VARCHAR2(1) := NULL ;
8 -- G_SERVER_TZ_CODE VARCHAR2(50) := NULL ;
9 -- G_SERVER_TZ_ID NUMBER := NULL ;
10
11
12 --=============================================================================
13 -- PROCEDURES AND FUNCTIONS
14 --=============================================================================
15
16 /*===========================================================================+
17 | Function |
18 | GET_LE_SYSDATE_FOR_OU |
19 | |
20 | DESCRIPTION |
21 | This function selects SYSDATE, converts it to the legal |
22 | entity timezone associated to the operating unit and removes|
23 | the timestamp returning the date with 00:00:00 for the time.|
24 | If the legal entity timezone is not setup then |
25 | TRUNC(SYSDATE) is returned. |
26 | |
27 | SCOPE - PUBLIC |
28 | |
29 | |
30 | ARGUMENTS : IN: p_ou_id |
31 | OUT: |
32 | |
33 | RETURNS : DATE |
34 | |
35 | NOTES |
36 | |
37 | MODIFICATION HISTORY |
38 | fdubois 29-sep-03 Created |
39 | rbasker 14-sep-05 Modified to call the XLE API. For R12, timezone |
40 | information comes from the new LE datamodel. |
41 | |
42 +===========================================================================*/
43 FUNCTION Get_Le_Sysdate_For_Ou
44 (p_ou_id IN NUMBER
45 )
46 RETURN DATE
47 IS
48 l_le_sysdate DATE := NULL;
49 BEGIN
50
51 l_le_sysdate := XLE_LE_TIMEZONE_GRP.Get_Le_Sysdate_For_Ou(p_ou_id) ;
52 RETURN l_le_sysdate;
53 null ;
54
55 END Get_Le_Sysdate_For_Ou;
56
57
58 /*===========================================================================+
59 | Function |
60 | GET_LE_DAY_FOR_INV_ORG |
61 | |
62 | DESCRIPTION |
63 | The function accepts a transaction datetime in the server |
64 | timezone and and an inventory organization ID, finds the |
65 | legal entity for the inventory organization, converts the |
66 | datetime to the legal entity timezone, truncates the |
67 | timestamps and return the date. |
68 | If legal entity timezone is not setup then no conversion |
69 | occurs. |
70 | |
71 | SCOPE - PUBLIC |
72 | |
73 | |
74 | ARGUMENTS : IN : p_trxn_date |
75 | p_inv_org_id |
76 | OUT : |
77 | |
78 | RETURNS : DATE |
79 | |
80 | NOTES |
81 | |
82 | MODIFICATION HISTORY |
83 | fdubois 29-sep-03 Created |
84 | rbasker 14-sep-05 Modified to call the XLE API. For R12, timezone |
85 | information comes from the new LE datamodel. |
86 | |
87 +===========================================================================*/
88 FUNCTION Get_Le_Day_For_Inv_org
89 (p_trxn_date IN DATE
90 ,p_inv_org_id IN NUMBER
91 )
92 RETURN DATE
93 IS
94 l_le_day_for_inv DATE := NULL;
95 l_return_status VARCHAR2(30) ;
96 l_msg_count NUMBER ;
97 l_msg_data VARCHAR2(2000) ;
98
99 BEGIN
100
101 l_le_day_for_inv := XLE_LE_TIMEZONE_GRP.Get_Le_Day_For_Inv_org(
102 p_trxn_date
103 ,p_inv_org_id) ;
104 -- Return value
105 RETURN l_le_day_for_inv;
106
107 END Get_Le_Day_For_Inv_org ;
108
109
110 /*===========================================================================+
111 | Function |
112 | GET_LE_DAY_FOR_OU |
113 | |
114 | DESCRIPTION |
115 | The function accepts a transaction datetime in the server |
116 | timezone and an operating unit ID. It finds the legal |
117 | entity for the operating unit and converts the |
118 | datetime to the legal entity timezone, truncates the |
119 | timestamps and return the date. |
120 | If legal entity timezone is not setup then no conversion |
121 | occurs. |
122 | |
123 | SCOPE - PUBLIC |
124 | |
125 | |
126 | ARGUMENTS : IN : p_trxn_date |
127 | p_ou_id |
128 | OUT: |
129 | |
130 | RETURNS : DATE |
131 | |
132 | NOTES |
133 | |
134 | MODIFICATION HISTORY |
135 | fdubois 29-sep-03 Created |
136 | rbasker 14-sep-05 Modified to call the XLE API. For R12, timezone |
137 | information comes from the new LE datamodel. |
138 | fdubois 24-jan-06 incorrect xle API called. |
139 | |
140 +===========================================================================*/
141 FUNCTION Get_Le_Day_For_Ou
142 (p_trxn_date IN DATE
143 ,p_ou_id IN NUMBER
144 )
145 RETURN DATE
146 IS
147
148 BEGIN
149
150 RETURN XLE_LE_TIMEZONE_GRP.Get_Le_Day_For_Ou ( p_trxn_date
151 , p_ou_id ) ;
152
153 END Get_Le_Day_For_Ou ;
154
155
156 /*===========================================================================+
157 | Function |
158 | GET_LE_DAY_TIME_FOR_OU |
159 | |
160 | DESCRIPTION |
161 | The function accepts a transaction datetime in the server |
162 | timezone and an operating unit ID. It finds the legal |
163 | entity for the operating unit and converts the |
164 | datetime to the legal entity timezone. |
165 | If legal entity timezone is not setup then no conversion |
166 | occurs. |
167 | |
168 | SCOPE - PUBLIC |
169 | |
170 | |
171 | ARGUMENTS : IN : p_trxn_date |
172 | : p_ou_id |
173 | OUT: |
174 | |
175 | RETURNS : DATE |
176 | |
177 | NOTES |
178 | |
179 | MODIFICATION HISTORY |
180 | fdubois 29-sep-03 Created |
181 | rbasker 14-sep-05 Modified to call the XLE API. For R12, timezone |
182 | information comes from the new LE datamodel. |
183 | |
184 +===========================================================================*/
185 FUNCTION Get_Le_Day_Time_For_Ou
186 (p_trxn_date IN DATE
187 ,p_ou_id IN NUMBER
188 )
189 RETURN DATE
190 IS
191
192 l_le_day_time_for_ou DATE := NULL;
193
194 BEGIN
195
196 l_le_day_time_for_ou := XLE_LE_TIMEZONE_GRP.Get_Le_Day_Time_For_Ou(
197 p_trxn_date
198 , p_ou_id);
199
200
201 -- Return value
202 RETURN l_le_day_time_for_ou ;
203
204 END Get_Le_Day_Time_For_Ou ;
205
206
207 /*===========================================================================+
208 | Function |
209 | GET_LE_TZ_CODE_FOR_INV_ORG |
210 | |
211 | DESCRIPTION |
212 | The function accepts an inventory organization ID and finds |
213 | timezone code for the legal entity. |
214 | If legal entity timezone is not setup then NULL is |
215 | returned. |
216 | |
217 | SCOPE - PUBLIC |
218 | |
219 | |
220 | ARGUMENTS : IN : p_inv_org_id |
221 | OUT: |
222 | |
223 | RETURNS : VARCHAR2 |
224 | |
225 | NOTES |
226 | |
227 | MODIFICATION HISTORY |
228 | fdubois 29-sep-03 Created |
229 | rbasker 14-sep-05 Modified to call the XLE API. For R12, timezone |
230 | information comes from the new LE datamodel. |
231 | |
232 +===========================================================================*/
233 FUNCTION Get_Le_Tz_Code_For_Inv_Org
234 (p_inv_org_id IN NUMBER
235 )
236 RETURN VARCHAR2
237 IS
238
239 l_timezone_code VARCHAR2(50) := NULL;
240 l_timezone_id NUMBER := NULL;
241
242 BEGIN
243
244 l_timezone_code := XLE_LE_TIMEZONE_GRP.Get_Le_Tz_Code_For_Inv_Org(
245 p_inv_org_id );
246
247 -- Return value
248 RETURN l_timezone_code;
249
250 END Get_Le_Tz_Code_For_Inv_Org ;
251
252
253 /*===========================================================================+
254 | Function |
255 | GET_LE_TZ_CODE_FOR_OU |
259 | timezone code for the legal entity. |
256 | |
257 | DESCRIPTION |
258 | The function accepts an operating unit id and finds |
260 | If legal entity timezone is not setup then NULL is |
261 | returned. |
262 | |
263 | SCOPE - PUBLIC |
264 | |
265 | |
266 | ARGUMENTS : IN : p_ou_id |
267 | OUT: |
268 | |
269 | RETURNS : VARCHAR2 |
270 | |
271 | NOTES |
272 | |
273 | MODIFICATION HISTORY |
274 | fdubois 29-sep-03 Created |
275 | rbasker 14-sep-05 Modified to call the XLE API. For R12, timezone |
276 | information comes from the new LE datamodel. |
277 | |
278 +===========================================================================*/
279 FUNCTION Get_Le_Tz_Code_For_Ou
280 (p_ou_id IN NUMBER
281 )
282 RETURN VARCHAR2
283 IS
284
285 l_timezone_code VARCHAR2(50) := NULL;
286 l_timezone_id NUMBER := NULL;
287
288 BEGIN
289
290
291 l_timezone_code := XLE_LE_TIMEZONE_GRP.Get_Le_Tz_Code_For_Ou(p_ou_id) ;
292 -- Return value
293 RETURN l_timezone_code;
294
295 END Get_Le_Tz_Code_For_Ou ;
296
297
298 /*===========================================================================+
299 | Function |
300 | GET_SERVER_DAY_TIME_FOR_LE |
301 | |
302 | DESCRIPTION |
303 | The function accepts a legal entity ID and a legal entity |
304 | datetime parameters and converts it to the server timezone. |
305 | If Legal entity timezone is not setup then no conversion |
306 | occurs. |
307 | |
308 | SCOPE - PUBLIC |
309 | |
310 | |
311 | ARGUMENTS : IN : p_le_date |
312 | : p_le_id |
313 | OUT: |
314 | |
315 | RETURNS : DATE |
316 | |
317 | NOTES |
318 | |
319 | MODIFICATION HISTORY |
320 | fdubois 29-sep-03 Created |
321 | rbasker 14-sep-05 Modified to call the XLE API. For R12, timezone |
322 | information comes from the new LE datamodel. |
323 | |
324 +===========================================================================*/
325 FUNCTION Get_Server_Day_Time_For_Le
326 (p_le_date IN DATE
327 ,p_le_id IN NUMBER
328 )
329 RETURN DATE
330 IS
331
332 l_srv_day_time DATE := NULL;
333
334 BEGIN
335
336 l_srv_day_time := XLE_LE_TIMEZONE_GRP.Get_Server_Day_Time_For_Le(
337 p_le_date
338 ,p_le_id) ;
339 -- Return value
340 RETURN l_srv_day_time ;
341
342 END Get_Server_Day_Time_For_Le ;
343
344
345 /*===========================================================================+
346 | Function |
347 | GET_LE_DAY_FOR_SERVER |
348 | |
349 | DESCRIPTION |
350 | The function accepts a transaction datetime in the server |
351 | timezone and the legal entity id, then converts the |
352 | datetime to the legal entity timezone, truncates the |
353 | timestamps and return the date. |
357 | SCOPE - PUBLIC |
354 | If legal entity timezone is not setup then no conversion |
355 | occurs. |
356 | |
358 | |
359 | |
360 | ARGUMENTS : IN : p_trxn_date |
361 | p_inv_org_id |
362 | OUT : |
363 | |
364 | RETURNS : DATE |
365 | |
366 | NOTES |
367 | |
368 | MODIFICATION HISTORY |
369 | fdubois 29-sep-03 Created |
370 | rbasker 14-sep-05 Modified to call the XLE API. For R12, timezone |
371 | information comes from the new LE datamodel. |
372 | |
373 +===========================================================================*/
374 FUNCTION Get_Le_Day_For_Server
375 (p_trxn_date IN DATE
376 ,p_le_id IN NUMBER
377 )
378 RETURN DATE
379 IS
380
381 l_le_day_time DATE := NULL;
382
383 BEGIN
384
385 l_le_day_time := XLE_LE_TIMEZONE_GRP.Get_Le_Day_For_Server(p_trxn_date
386 , p_le_id) ;
387 -- Return value
388 RETURN l_le_day_time ;
389
390 END Get_Le_Day_For_Server ;
391
392
393 -- This section is commented out and replaced by equivalent initialization
394 -- in $XLE_TOP/patch/115/sql/xlegltzb.pls
395
396 --BEGIN
397
398 -- Package initialization. Use to get/cache the Server timezone code,
399 -- and other profile values
400
401 -- check if Legal Entity Timezone conversion is enabled
402 --G_ENABLE_LE_TIMEZONE := NVL(fnd_profile.value(
403 -- 'XLE_ENABLE_LEGAL_ENTITY_TIMEZONE'),'N') ;
404
405 -- If LE Timezone is enabled Get the server timezone code and timezone id
406 --IF G_ENABLE_LE_TIMEZONE = 'Y' THEN
407 -- SELECT timezone_code ,
408 -- upgrade_tz_id
409 -- INTO G_SERVER_TZ_CODE ,
410 -- G_SERVER_TZ_ID
411 -- FROM fnd_timezones_b
412 -- WHERE upgrade_tz_id =
413 -- to_number( fnd_profile.value_specific('SERVER_TIMEZONE_ID')) ;
414
415 -- END IF ;
416
417
418 END INV_LE_TIMEZONE_PUB;