1 PACKAGE BODY XLE_LE_TIMEZONE_GRP AS
2 -- $Header: xlegltzb.pls 120.6.12010000.3 2008/10/17 07:25:43 srampure ship $
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 G_PREV_OU_ID NUMBER := -1 ;
11 G_PREV_INV_ORG_ID NUMBER := -1 ;
12 G_PREV_TZ_ID_FOR_OU NUMBER := -1 ;
13 G_PREV_TZ_CODE_FOR_OU VARCHAR2(50) := NULL ;
14 G_PREV_TZ_ID_FOR_INV_ORG NUMBER := -1 ;
15 G_PREV_TZ_CODE_FOR_INV_ORG VARCHAR2(50) := NULL ;
16 G_PREV_LE_ID NUMBER := -1 ;
17 G_PREV_TZ_ID_FOR_LE NUMBER := -1 ;
18 G_PREV_TZ_CODE_FOR_LE VARCHAR2(50) := NULL ;
19
20 --=============================================================================
21 -- PROCEDURES AND FUNCTIONS
22 --=============================================================================
23
24 -- ===========================================================================
25 -- Function
26 -- Get_LegalEntity_ID
27 --
28 -- DESCRIPTION
29 -- This function selects the legal entity associated to the
30 -- attribute passed to it. The attribute can be operating unit
31 -- or Inventory Org or a Legal Entity.
32 --
33 -- SCOPE - PRIVATE
34 --
35 --
36 -- ARGUMENTS : IN: P_ATTRIBUTE
37 -- P_ATTRIBUTE_VALUE
38 -- OUT:
39 --
40 -- RETURNS : NUMBER
41 --
42 -- NOTES
43 --
44 -- MODIFICATION HISTORY
45 -- rbasker 29-Jun-05 Created
46 --
47 -- ===========================================================================
48
49 FUNCTION Get_LegalEntity_ID(
50 P_ATTRIBUTE IN VARCHAR2,
51 P_ATTRIBUTE_VALUE IN VARCHAR2
52
53 ) RETURN NUMBER IS
54 /* Local Variable */
55 l_ou_le_info XLE_BUSINESSINFO_GRP.OU_LE_Tbl_Type;
56 l_le_info XLE_UTILITIES_GRP.LegalEntity_Rec;
57 l_inv_le_info XLE_BUSINESSINFO_GRP.Inv_Org_Rec_Type;
58 l_legal_entity_id XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID%TYPE;
59 l_return_status VARCHAR2(30) ;
60 l_msg_count NUMBER ;
61 l_msg_data VARCHAR2(2000) ;
62
63 BEGIN
64
65 /* If OPERATING UNIT ID is passed in the attribute */
66
67 IF P_ATTRIBUTE = 'OPERATING_UNIT_ID' THEN
68 XLE_BUSINESSINFO_GRP.Get_OperatingUnit_Info(
69 x_return_status => l_return_status
70 ,x_msg_data => l_msg_data
71 ,p_operating_unit => TO_NUMBER(P_ATTRIBUTE_VALUE)
72 ,p_legal_entity_id => NULL
73 ,p_party_id => NULL
74 ,x_ou_le_info => l_ou_le_info);
75
76 l_legal_entity_id := l_ou_le_info(1).legal_entity_id;
77
78 END IF;
79
80 /* If LE ID is passed in the attribute */
81
82 IF P_ATTRIBUTE = 'LEGAL_ENTITY_ID' THEN
83 XLE_UTILITIES_GRP.Get_LegalEntity_Info(
84 x_return_status => l_return_status,
85 x_msg_count => l_msg_count,
86 x_msg_data => l_msg_data,
87 p_party_id => null,
88 p_legalentity_id => TO_NUMBER(P_ATTRIBUTE_VALUE),
89 x_legalentity_info => l_le_info);
90
91 l_legal_entity_id := l_le_info.legal_entity_id;
92
93 END IF;
94
95 /* If INVENTORY ORG ID is passed in the attribute */
96
97 IF P_ATTRIBUTE = 'INVENTORY_ORG_ID' THEN
98 XLE_BUSINESSINFO_GRP.Get_InvOrg_Info(
99 x_return_status => l_return_status,
100 x_msg_data => l_msg_data,
101 P_InvOrg_ID => TO_NUMBER(P_ATTRIBUTE_VALUE),
102 P_Le_ID => NULL,
103 P_Party_ID => NULL,
104 x_Inv_Le_info => l_inv_le_info);
105
106 l_legal_entity_id := l_inv_le_info(1).legal_entity_id;
107 END IF;
108 RETURN l_legal_entity_id;
109 EXCEPTION
110
111 WHEN NO_DATA_FOUND THEN
112 RETURN -1;
113
114 WHEN OTHERS THEN
115 RETURN -1;
116
117 END Get_LegalEntity_ID;
118
119 -- ===========================================================================
120 -- PROCEDURE
121 -- Get_LE_TimeZone_Info
122 --
123 -- DESCRIPTION
124 -- This procedure gets the timezone info of the Legal Entity involved.
125 --
126 -- SCOPE - PRIVATE
127 --
128 --
129 -- ARGUMENTS : IN:
130 -- p_api_version
131 -- p_init_msg_list
132 -- p_commit
133 -- p_legalentity_id
134 -- OUT: x_return_status
135 -- x_msg_count
136 -- x_msg_data
137 -- x_timezone_code
138 -- x_timezone_id
139 -- IN/ OUT:
140 --
141 -- RETURNS : NONE
142 --
143 -- NOTES
144 --
145 -- MODIFICATION HISTORY
146 -- rbasker 29-JUN-2005 Created
147 --
148 -- ===========================================================================
149
150 PROCEDURE Get_LE_TimeZone_Info(
151 p_api_version IN NUMBER,
152 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
153 p_commit IN VARCHAR2 := FND_API.G_FALSE,
154 x_return_status OUT NOCOPY VARCHAR2,
155 x_msg_count OUT NOCOPY NUMBER,
156 x_msg_data OUT NOCOPY VARCHAR2,
157 p_legalentity_id IN XLE_ENTITY_PROFILES.legal_entity_id%TYPE,
158 x_timezone_code OUT NOCOPY VARCHAR2,
159 x_timezone_id OUT NOCOPY NUMBER
160 )
161 IS
162 l_api_name CONSTANT VARCHAR2(30):= 'Get_LE_TimeZone_Info';
163 l_api_version CONSTANT NUMBER:= 1.0;
164 l_init_msg_list VARCHAR2(100);
165 l_commit VARCHAR2(100);
166 BEGIN
167
168 -- Initialize API return status to success
169 x_return_status := FND_API.G_RET_STS_SUCCESS;
170
171 IF p_legalentity_id IS NOT NULL THEN
172
173 -- Initialize the out variables to NULL
174 x_timezone_code := NULL;
175 x_timezone_id := NULL;
176
177 SELECT hrl.timezone_code ,
178 ftb.upgrade_tz_id
179 INTO x_timezone_code,
180 x_timezone_id
181 FROM XLE_ENTITY_PROFILES xlep,
182 XLE_REGISTRATIONS reg,
183 HR_LOCATIONS_ALL hrl,
184 fnd_timezones_b ftb
185 WHERE xlep.legal_entity_id = reg.source_id
186 AND reg.source_table = 'XLE_ENTITY_PROFILES'
187 AND reg.identifying_flag = 'Y'
188 AND nvl(reg.effective_from,sysdate) <= sysdate
189 AND nvl(reg.effective_to, sysdate) >= sysdate
190 AND reg.location_id = hrl.location_id
191 AND xlep.legal_entity_id = p_legalentity_id
192 AND ftb.timezone_code = hrl.timezone_code ;
193
194 ELSE
195 x_return_status := FND_API.G_RET_STS_ERROR;
196 x_msg_data := 'Missing mandatory arguments.';
197 END IF;
198
199 EXCEPTION
200 /* WHEN NO_DATA_FOUND THEN
201 x_return_status := FND_API.G_RET_STS_ERROR ;
202 x_msg_data := 'Could not find an Timezone Information for the Legal Entity : ' || p_legalentity_id;
203 RAISE FND_API.G_EXC_ERROR; */
204 WHEN NO_DATA_FOUND THEN
205 NULL;
206 WHEN TOO_MANY_ROWS THEN
207 x_return_status := FND_API.G_RET_STS_ERROR ;
208 x_msg_data := 'The Legal Entity : ' || p_legalentity_id || ' is associated with more than one Country';
209 RAISE FND_API.G_EXC_ERROR;
210
211 END Get_LE_TimeZone_Info;
212
213 -- ===========================================================================
214 -- Function
215 -- GET_LE_SYSDATE_FOR_OU
216 --
217 -- DESCRIPTION
218 -- This function selects SYSDATE, converts it to the legal
219 -- entity timezone associated to the operating unit and removes
220 -- the timestamp returning the date with 00:00:00 for the time.
221 -- If the legal entity timezone is not setup then
222 -- TRUNC(SYSDATE) is returned.
223 --
224 -- SCOPE PUBLIC
225 --
226 --
227 -- ARGUMENTS : IN: p_ou_id
228 -- OUT:
229 --
230 -- RETURNS : DATE
231 --
232 -- NOTES
233 --
234 -- MODIFICATION HISTORY
235 -- rbasker 29-JUN-2005 Created
236 --
237 -- ===========================================================================
238 FUNCTION Get_Le_Sysdate_For_Ou
239 (p_ou_id IN NUMBER
240 )
241 RETURN DATE
242 IS
243
244 l_timezone_code VARCHAR2(50) := NULL;
245 l_timezone_id NUMBER := NULL;
246 l_le_sysdate DATE := NULL;
247 l_return_status VARCHAR2(30) ;
248 l_msg_count NUMBER ;
249 l_msg_data VARCHAR2(2000) ;
250 l_return_status1 VARCHAR2(30) ;
251 l_msg_count1 NUMBER ;
252 l_msg_data1 VARCHAR2(2000) ;
253 l_legal_entity_id XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID%TYPE;
254
255 BEGIN
256
257 -- check if Legal Entity Timezone is enabled and p_ou_id is NOT NULL
258 IF G_ENABLE_LE_TIMEZONE = 'Y' AND p_ou_id IS NOT NULL THEN
259 -- for performance, verify if result is in cache
260 -- If not, run the cursor.
261 IF p_ou_id = G_PREV_OU_ID THEN
262 l_timezone_code := G_PREV_TZ_CODE_FOR_OU ;
263 l_timezone_id := G_PREV_TZ_ID_FOR_OU ;
264 ELSE
265 -- Get the Legal Entity associated for the passed OU
266 l_legal_entity_id := Get_LegalEntity_ID( P_ATTRIBUTE => 'OPERATING_UNIT_ID',
267 P_ATTRIBUTE_VALUE => p_ou_id);
268
269 -- Get the timezone code of the legal entity
270 -- Call the Get_LE_TimeZone_Info API to get the timezone information
271 Get_LE_TimeZone_Info (
272 p_api_version => 1.0
273 , p_init_msg_list => FND_API.G_FALSE
274 , p_commit => FND_API.G_FALSE
275 , x_return_status => l_return_status
276 , x_msg_count => l_msg_count
277 , x_msg_data => l_msg_data
278 , p_legalentity_id => l_legal_entity_id
279 , x_timezone_code => l_timezone_code
280 , x_timezone_id => l_timezone_id) ;
281
282 -- if any error occurs propagate as unexpected error
283 IF l_return_status = FND_API.G_RET_STS_ERROR OR
284 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
285 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
286 END IF ;
287 END IF;
288 -- If NO Timezone code was associated to the Location of the LE
289 -- OR the server and LE timezone are identycal then return the sysdate
290 IF l_timezone_code IS NULL
291 OR l_timezone_code = G_SERVER_TZ_CODE THEN
292 l_le_sysdate := SYSDATE ;
293 ELSE
294
295 -- Call the get_time API to convert the server timezone date
296 -- to the LE timezone
297 HZ_TIMEZONE_PUB.Get_Time
298 ( p_api_version => 1.0
299 , p_init_msg_list => FND_API.G_FALSE
300 , p_source_tz_id => G_SERVER_TZ_ID
301 , p_dest_tz_id => l_timezone_id
302 , p_source_day_time => SYSDATE
303 , x_dest_day_time => l_le_sysdate
304 , x_return_status => l_return_status1
305 , x_msg_count => l_msg_count1
306 , x_msg_data => l_msg_data1 ) ;
307
308 -- if any error occurs propagate as unexpected error
309 IF l_return_status1 = FND_API.G_RET_STS_ERROR OR
310 l_return_status1 = FND_API.G_RET_STS_UNEXP_ERROR THEN
311 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
312 END IF ;
313
314 END IF ;
315
316 -- cache the current values
317 G_PREV_OU_ID := p_ou_id ;
318 G_PREV_TZ_ID_FOR_OU := l_timezone_id ;
319 G_PREV_TZ_CODE_FOR_OU := l_timezone_code ;
320
321 -- If Legal Entity timezone is not supported or p_ou_id is NULL
322 -- SYSDATE is defaulted
323 ELSE
324 l_le_sysdate := SYSDATE ;
325 END IF ;
326
327 -- Return value
328 RETURN TRUNC(l_le_sysdate);
329 null;
330
331 END Get_Le_Sysdate_For_Ou;
332
333
334 -- ===========================================================================
335 -- Function
336 -- GET_LE_SYSDATE_FOR_INV_ORG
337 --
338 -- DESCRIPTION
339 -- This function selects SYSDATE, converts it to the legal
340 -- entity timezone associated to the operating unit and removes
341 -- the timestamp returning the date with 00:00:00 for the time.
342 -- If the legal entity timezone is not setup then
343 -- TRUNC(SYSDATE) is returned.
344 --
345 -- SCOPE PUBLIC
346 --
347 --
348 -- ARGUMENTS : IN: p_ou_id
349 -- OUT:
350 --
351 -- RETURNS : DATE
352 --
353 -- NOTES
354 --
355 -- MODIFICATION HISTORY
356 -- vdobrev 16-JAN-2006 Created
357 --
358 -- ===========================================================================
359 FUNCTION Get_Le_Sysdate_For_Inv_Org
360 (p_inv_org_id IN NUMBER
361 )
362 RETURN DATE
363 IS
364
365 l_timezone_code VARCHAR2(50) := NULL;
366 l_timezone_id NUMBER := NULL;
367 l_le_sysdate DATE := NULL;
368 l_return_status VARCHAR2(30) ;
369 l_msg_count NUMBER ;
370 l_msg_data VARCHAR2(2000) ;
371 l_return_status1 VARCHAR2(30) ;
372 l_msg_count1 NUMBER ;
373 l_msg_data1 VARCHAR2(2000) ;
374 l_legal_entity_id XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID%TYPE;
375
376 BEGIN
377
378 -- check if Legal Entity Timezone is enabled and p_inv_org_id is NOT NULL
379 IF G_ENABLE_LE_TIMEZONE = 'Y' AND p_inv_org_id IS NOT NULL THEN
380 -- for performance, verify if result is in cache
381 -- If not, run the cursor.
382 IF p_inv_org_id = G_PREV_INV_ORG_ID THEN
383 l_timezone_code := G_PREV_TZ_CODE_FOR_INV_ORG ;
384 l_timezone_id := G_PREV_TZ_ID_FOR_INV_ORG ;
385 ELSE
386 -- Get the Legal Entity associated for the passed Inv Org
387 l_legal_entity_id := Get_LegalEntity_ID( P_ATTRIBUTE => 'INVENTORY_ORG_ID',
388 P_ATTRIBUTE_VALUE => p_inv_org_id);
389
390 -- Get the timezone code of the legal entity
391 -- Call the Get_LE_TimeZone_Info API to get the timezone information
392 Get_LE_TimeZone_Info (
393 p_api_version => 1.0
394 , p_init_msg_list => FND_API.G_FALSE
395 , p_commit => FND_API.G_FALSE
396 , x_return_status => l_return_status
397 , x_msg_count => l_msg_count
398 , x_msg_data => l_msg_data
399 , p_legalentity_id => l_legal_entity_id
400 , x_timezone_code => l_timezone_code
401 , x_timezone_id => l_timezone_id) ;
402
403 -- if any error occurs propagate as unexpected error
404 IF l_return_status = FND_API.G_RET_STS_ERROR OR
405 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
406 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
407 END IF ;
408 END IF;
409 -- If NO Timezone code was associated to the Location of the LE
410 -- OR the server and LE timezone are identycal then return the sysdate
411 IF l_timezone_code IS NULL
412 OR l_timezone_code = G_SERVER_TZ_CODE THEN
413 l_le_sysdate := SYSDATE ;
414 ELSE
415
416 -- Call the get_time API to convert the server timezone date
417 -- to the LE timezone
418 HZ_TIMEZONE_PUB.Get_Time
419 ( p_api_version => 1.0
420 , p_init_msg_list => FND_API.G_FALSE
421 , p_source_tz_id => G_SERVER_TZ_ID
422 , p_dest_tz_id => l_timezone_id
423 , p_source_day_time => SYSDATE
424 , x_dest_day_time => l_le_sysdate
425 , x_return_status => l_return_status1
426 , x_msg_count => l_msg_count1
427 , x_msg_data => l_msg_data1 ) ;
428
429 -- if any error occurs propagate as unexpected error
430 IF l_return_status1 = FND_API.G_RET_STS_ERROR OR
431 l_return_status1 = FND_API.G_RET_STS_UNEXP_ERROR THEN
432 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
433 END IF ;
434
435 END IF ;
436
437 -- cache the current values
438 G_PREV_INV_ORG_ID := p_inv_org_id ;
439 G_PREV_TZ_ID_FOR_INV_ORG := l_timezone_id ;
440 G_PREV_TZ_CODE_FOR_INV_ORG := l_timezone_code ;
441
442 -- If Legal Entity timezone is not supported or p_inv_org_id is NULL
443 -- SYSDATE is defaulted
444 ELSE
445 l_le_sysdate := SYSDATE ;
446 END IF ;
447
448 -- Return value
449 RETURN TRUNC(l_le_sysdate);
450 null;
451
452 END Get_Le_Sysdate_For_Inv_Org;
453
454
455
456 -- ===========================================================================
457 -- Function
458 -- GET_LE_DAY_FOR_INV_ORG
459 --
460 -- DESCRIPTION
461 -- The function accepts a transaction datetime in the server
462 -- timezone and and an inventory organization ID, finds the
463 -- legal entity for the inventory organization, converts the
464 -- datetime to the legal entity timezone, truncates the
465 -- timestamps and return the date.
466 -- If legal entity timezone is not setup then no conversion
467 -- occurs.
468 --
469 -- SCOPE - PUBLIC
470 --
471 --
472 -- ARGUMENTS : IN : p_trxn_date
473 -- p_inv_org_id
474 -- OUT :
475 --
476 -- RETURNS : DATE
477 --
478 -- NOTES
479 --
480 -- MODIFICATION HISTORY
481 -- rbasker 29-Jun-2005 Created
482 --
483 -- ===========================================================================
484 FUNCTION Get_Le_Day_For_Inv_org
485 (p_trxn_date IN DATE
486 ,p_inv_org_id IN NUMBER
487 )
488 RETURN DATE
489 IS
490
491 l_timezone_code VARCHAR2(50) := NULL;
492 l_timezone_id NUMBER := NULL;
493 l_le_day_for_inv DATE := NULL;
494 l_return_status VARCHAR2(30) ;
495 l_msg_count NUMBER ;
496 l_msg_data VARCHAR2(2000) ;
497 l_return_status1 VARCHAR2(30) ;
498 l_msg_count1 NUMBER ;
499 l_msg_data1 VARCHAR2(2000) ;
500 l_legal_entity_id XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID%TYPE;
501
502 BEGIN
503
504 -- check if Legal Entity Timezone is enabled and p_inv_org_id is NOT NULL
505 IF G_ENABLE_LE_TIMEZONE = 'Y' AND p_inv_org_id IS NOT NULL THEN
506 -- for performance, verify if result is in cache
507 -- If not, run the cursor.
508 IF p_inv_org_id = G_PREV_INV_ORG_ID THEN
509 l_timezone_code := G_PREV_TZ_CODE_FOR_INV_ORG ;
510 l_timezone_id := G_PREV_TZ_ID_FOR_INV_ORG ;
511 ELSE
512
513 -- Get the Legal Entity associated for the passed inv org
514 l_legal_entity_id := Get_LegalEntity_ID( P_ATTRIBUTE => 'INVENTORY_ORG_ID',
515 P_ATTRIBUTE_VALUE => p_inv_org_id);
516
517 -- Get the timezone code of the legal entity
518
519 -- Call the Get_LE_TimeZone_Info API to get the timezone informatio
520 Get_LE_TimeZone_Info (
521 p_api_version => 1.0
522 , p_init_msg_list => FND_API.G_FALSE
523 , p_commit => FND_API.G_FALSE
524 , x_return_status => l_return_status
525 , x_msg_count => l_msg_count
526 , x_msg_data => l_msg_data
527 , p_legalentity_id => l_legal_entity_id
528 , x_timezone_code => l_timezone_code
529 , x_timezone_id => l_timezone_id) ;
530
531 -- if any error occurs propagate as unexpected error
532 IF l_return_status = FND_API.G_RET_STS_ERROR OR
533 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
534 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
535 END IF ;
536 END IF ;
537
538 -- If NO Timezone code was associated to the Location of the LE
542 l_le_day_for_inv := p_trxn_date ;
539 -- OR the server and LE timezone are identycal then return p_trxn_date
540 IF l_timezone_code IS NULL
541 OR l_timezone_code = G_SERVER_TZ_CODE THEN
543 ELSE
544
545 -- Call the get_time API to convert the server timezone date
546 -- to the LE timezone
547 HZ_TIMEZONE_PUB.Get_Time
548 ( p_api_version => 1.0
549 , p_init_msg_list => FND_API.G_FALSE
550 , p_source_tz_id => G_SERVER_TZ_ID
551 , p_dest_tz_id => l_timezone_id
552 , p_source_day_time => p_trxn_date
553 , x_dest_day_time => l_le_day_for_inv
554 , x_return_status => l_return_status1
555 , x_msg_count => l_msg_count1
556 , x_msg_data => l_msg_data1 ) ;
557
558 -- if any error occurs propagate as unexpected error
559 IF l_return_status1 = FND_API.G_RET_STS_ERROR OR
560 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
561 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
562 END IF ;
563
564 END IF ;
565
566 -- cache the current values
567 G_PREV_INV_ORG_ID := p_inv_org_id ;
568 G_PREV_TZ_ID_FOR_INV_ORG := l_timezone_id ;
569 G_PREV_TZ_CODE_FOR_INV_ORG := l_timezone_code ;
570
571 -- If Legal Entity timezone is not supported or p_inv_org_id IS NULL
572 -- is p_trxn_date defaulted
573 ELSE
574 l_le_day_for_inv := p_trxn_date ;
575 END IF ;
576
577 -- Return value
578 RETURN TRUNC(l_le_day_for_inv);
579
580 END Get_Le_Day_For_Inv_org ;
581
582 /*===========================================================================+
583 | Function |
584 | GET_LE_DAY_FOR_OU |
585 | |
586 | DESCRIPTION |
587 | The function accepts a transaction datetime in the server |
588 | timezone and an operating unit ID. It finds the legal |
589 | entity for the operating unit and converts the |
590 | datetime to the legal entity timezone, truncates the |
591 | timestamps and return the date. |
592 | If legal entity timezone is not setup then no conversion |
593 | occurs. |
594 | |
595 | SCOPE - PUBLIC |
596 | |
597 | |
598 | ARGUMENTS : IN : p_trxn_date |
599 | p_ou_id |
600 | OUT: |
601 | |
602 | RETURNS : DATE |
603 | |
604 | NOTES |
605 | |
606 | MODIFICATION HISTORY |
607 | rbasker 29-Jun-2005 Created |
608 | |
609 +===========================================================================*/
610 FUNCTION Get_Le_Day_For_Ou
611 (p_trxn_date IN DATE
612 ,p_ou_id IN NUMBER
613 )
614 RETURN DATE
615 IS
616
617 BEGIN
618
619 RETURN TRUNC(XLE_LE_TIMEZONE_GRP.Get_Le_Day_Time_For_Ou ( p_trxn_date ,
620 p_ou_id )) ;
621
622 END Get_Le_Day_For_Ou ;
623
624 /*===========================================================================+
625 | Function |
626 | GET_LE_DAY_TIME_FOR_OU |
627 | |
628 | DESCRIPTION |
629 | The function accepts a transaction datetime in the server |
630 | timezone and an operating unit ID. It finds the legal |
631 | entity for the operating unit and converts the |
632 | datetime to the legal entity timezone. |
633 | If legal entity timezone is not setup then no conversion |
634 | occurs. |
635 | |
636 | SCOPE - PUBLIC |
637 | |
638 | |
642 | |
639 | ARGUMENTS : IN : p_trxn_date |
640 | : p_ou_id |
641 | OUT: |
643 | RETURNS : DATE |
644 | |
645 | NOTES |
646 | |
647 | MODIFICATION HISTORY |
648 | rbasker 29-Jun-2005 Created |
649 | |
650 +===========================================================================*/
651 FUNCTION Get_Le_Day_Time_For_Ou
652 (p_trxn_date IN DATE
653 ,p_ou_id IN NUMBER
654 )
655 RETURN DATE
656 IS
657
658 l_timezone_code VARCHAR2(50) := NULL;
659 l_timezone_id NUMBER := NULL;
660 l_le_day_time_for_ou DATE := NULL;
661 l_return_status VARCHAR2(30) ;
662 l_msg_count NUMBER ;
663 l_msg_data VARCHAR2(2000) ;
664 l_return_status1 VARCHAR2(30) ;
665 l_msg_count1 NUMBER ;
666 l_msg_data1 VARCHAR2(2000) ;
667 l_legal_entity_id XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID%TYPE;
668
669 BEGIN
670
671 -- check if Legal Entity Timezone is enabled and p_ou_id is NOT NULL
672 IF G_ENABLE_LE_TIMEZONE = 'Y' AND p_ou_id IS NOT NULL THEN
673 -- for performance, verify if result is in cache
674 -- If not, run the cursor.
675 IF p_ou_id = G_PREV_OU_ID THEN
676 l_timezone_code := G_PREV_TZ_CODE_FOR_OU ;
677 l_timezone_id := G_PREV_TZ_ID_FOR_OU ;
678 ELSE
679 -- Get the Legal Entity associated for the passed OU
680 l_legal_entity_id := Get_LegalEntity_ID( P_ATTRIBUTE => 'OPERATING_UNIT_ID',
681 P_ATTRIBUTE_VALUE => p_ou_id);
682
683 -- Get the timezone code of the legal entity
684
685 -- Call the Get_LE_TimeZone_Info API to get the timezone informatio
686 Get_LE_TimeZone_Info (
687 p_api_version => 1.0
688 , p_init_msg_list => FND_API.G_FALSE
689 , p_commit => FND_API.G_FALSE
690 , x_return_status => l_return_status
691 , x_msg_count => l_msg_count
692 , x_msg_data => l_msg_data
693 , p_legalentity_id => l_legal_entity_id
694 , x_timezone_code => l_timezone_code
695 , x_timezone_id => l_timezone_id) ;
696
697 -- if any error occurs propagate as unexpected error
698 IF l_return_status = FND_API.G_RET_STS_ERROR OR
699 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
700 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
701 END IF ;
702 END IF ;
703
704 -- If NO Timezone code was associated to the Location of the LE
705 -- OR the server and LE timezone are identycal then return the p_trxn_date
706 IF l_timezone_code IS NULL
707 OR l_timezone_code = G_SERVER_TZ_CODE THEN
708 l_le_day_time_for_ou := p_trxn_date ;
709 ELSE
710
711 -- Call the get_time API to convert the server timezone date
712 -- to the LE timezone
713 HZ_TIMEZONE_PUB.Get_Time
714 ( p_api_version => 1.0
715 , p_init_msg_list => FND_API.G_FALSE
716 , p_source_tz_id => G_SERVER_TZ_ID
717 , p_dest_tz_id => l_timezone_id
718 , p_source_day_time => p_trxn_date
719 , x_dest_day_time => l_le_day_time_for_ou
720 , x_return_status => l_return_status1
721 , x_msg_count => l_msg_count1
722 , x_msg_data => l_msg_data1 ) ;
723
724 -- if any error occurs propagate as unexpected error
725 IF l_return_status1 = FND_API.G_RET_STS_ERROR OR
726 l_return_status1 = FND_API.G_RET_STS_UNEXP_ERROR THEN
727 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
728 END IF ;
729
730 END IF ;
731
732 -- cache the current values
733 G_PREV_OU_ID := p_ou_id ;
734 G_PREV_TZ_ID_FOR_OU := l_timezone_id ;
735 G_PREV_TZ_CODE_FOR_OU := l_timezone_code ;
736
737 -- If Legal Entity timezone is not supported or p_ou_id IS NULL,
738 -- p_trxn_date is defaulted
739 ELSE
740 l_le_day_time_for_ou := p_trxn_date ;
741 END IF ;
742
743 -- Return value
744 RETURN l_le_day_time_for_ou ;
745
746 END Get_Le_Day_Time_For_Ou ;
747
748 /*===========================================================================+
749 | Function |
750 | GET_LE_TZ_CODE_FOR_INV_ORG |
751 | |
752 | DESCRIPTION |
753 | The function accepts an inventory organization ID and finds |
754 | timezone code for the legal entity. |
755 | If legal entity timezone is not setup then NULL is |
759 | |
756 | returned. |
757 | |
758 | SCOPE - PUBLIC |
760 | |
761 | ARGUMENTS : IN : p_inv_org_id |
762 | OUT: |
763 | |
764 | RETURNS : VARCHAR2 |
765 | |
766 | NOTES |
767 | |
768 | MODIFICATION HISTORY |
769 | rbasker 29-Jun-2005 Created |
770 | |
771 +===========================================================================*/
772 FUNCTION Get_Le_Tz_Code_For_Inv_Org
773 (p_inv_org_id IN NUMBER
774 )
775 RETURN VARCHAR2
776 IS
777
778 l_timezone_code VARCHAR2(50) := NULL;
779 l_timezone_id NUMBER := NULL;
780 l_return_status VARCHAR2(30) ;
781 l_msg_count NUMBER ;
782 l_msg_data VARCHAR2(2000) ;
783 l_legal_entity_id XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID%TYPE;
784
785 BEGIN
786
787 -- check if Legal Entity Timezone is enabled and p_inv_org_id IS NOT NULL
788 IF G_ENABLE_LE_TIMEZONE = 'Y' AND p_inv_org_id IS NOT NULL THEN
789 -- for performance, verify if result is in cache
790 -- If not, run the cursor.
791 IF p_inv_org_id = G_PREV_INV_ORG_ID THEN
792 l_timezone_code := G_PREV_TZ_CODE_FOR_INV_ORG ;
793 l_timezone_id := G_PREV_TZ_ID_FOR_INV_ORG ;
794 ELSE
795 -- Get the Legal Entity associated for the passed inv org
796 l_legal_entity_id := Get_LegalEntity_ID( P_ATTRIBUTE => 'INVENTORY_ORG_ID',
797 P_ATTRIBUTE_VALUE => p_inv_org_id);
798
799 -- Call the Get_LE_TimeZone_Info API to get the timezone information of the Legal Entity
800 Get_LE_TimeZone_Info (
801 p_api_version => 1.0
802 , p_init_msg_list => FND_API.G_FALSE
803 , p_commit => FND_API.G_FALSE
804 , x_return_status => l_return_status
805 , x_msg_count => l_msg_count
806 , x_msg_data => l_msg_data
807 , p_legalentity_id => l_legal_entity_id
808 , x_timezone_code => l_timezone_code
809 , x_timezone_id => l_timezone_id) ;
810
811 -- if any error occurs propagate as unexpected error
812 IF l_return_status = FND_API.G_RET_STS_ERROR OR
813 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
814 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
815 END IF ;
816 END IF ;
817
818 END IF ;
819
820 -- cache the current values
821 G_PREV_INV_ORG_ID := p_inv_org_id ;
822 G_PREV_TZ_ID_FOR_INV_ORG := l_timezone_id ;
823 G_PREV_TZ_CODE_FOR_INV_ORG := l_timezone_code ;
824
825 -- If Legal Entity timezone is not supported or p_inv_org_id IS NULL
826 -- or no Timezone code is associated to the LE, NULL
827 -- ( deafult value of return value) is returned
828
829 -- Return value
830 RETURN l_timezone_code;
831
832 END Get_Le_Tz_Code_For_Inv_Org ;
833
834 /*===========================================================================+
835 | Function |
836 | GET_LE_TZ_CODE_FOR_OU |
837 | |
838 | DESCRIPTION |
839 | The function accepts an operating unit id and finds |
840 | timezone code for the legal entity. |
841 | If legal entity timezone is not setup then NULL is |
842 | returned. |
843 | |
844 | SCOPE - PUBLIC |
845 | |
846 | |
847 | ARGUMENTS : IN : p_ou_id |
848 | OUT: |
849 | |
850 | RETURNS : VARCHAR2 |
851 | |
852 | NOTES |
853 | |
854 | MODIFICATION HISTORY |
858 FUNCTION Get_Le_Tz_Code_For_Ou
855 | rbasker 29-Jun-2005 Created |
856 | |
857 +===========================================================================*/
859 (p_ou_id IN NUMBER
860 )
861 RETURN VARCHAR2
862 IS
863
864 l_timezone_code VARCHAR2(50) := NULL;
865 l_timezone_id NUMBER := NULL;
866 l_return_status VARCHAR2(30) ;
867 l_msg_count NUMBER ;
868 l_msg_data VARCHAR2(2000) ;
869 l_legal_entity_id XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID%TYPE;
870
871 BEGIN
872
873 -- check if Legal Entity Timezone is enabled AND p_ou_id IS NOT NULL
874 IF G_ENABLE_LE_TIMEZONE = 'Y' AND p_ou_id IS NOT NULL THEN
875 -- for performance, verify if result is in cache
876 -- If not, run the cursor.
877 IF p_ou_id = G_PREV_OU_ID THEN
878 l_timezone_code := G_PREV_TZ_CODE_FOR_OU ;
879 l_timezone_id := G_PREV_TZ_ID_FOR_OU ;
880 ELSE
881 -- Get the Legal Entity associated for the passed OU
882 l_legal_entity_id := Get_LegalEntity_ID( P_ATTRIBUTE => 'OPERATING_UNIT_ID',
883 P_ATTRIBUTE_VALUE => p_ou_id);
884
885 -- Call the Get_LE_TimeZone_Info API to get the timezone information of the legal entity
886 Get_LE_TimeZone_Info (
887 p_api_version => 1.0
888 , p_init_msg_list => FND_API.G_FALSE
889 , p_commit => FND_API.G_FALSE
890 , x_return_status => l_return_status
891 , x_msg_count => l_msg_count
892 , x_msg_data => l_msg_data
893 , p_legalentity_id => l_legal_entity_id
894 , x_timezone_code => l_timezone_code
895 , x_timezone_id => l_timezone_id) ;
896
897 -- if any error occurs propagate as unexpected error
898 IF l_return_status = FND_API.G_RET_STS_ERROR OR
899 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
900 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
901 END IF ;
902 END IF ;
903
904 END IF ;
905
906 -- cache the current values
907 G_PREV_OU_ID := p_ou_id ;
908 G_PREV_TZ_ID_FOR_OU := l_timezone_id ;
909 G_PREV_TZ_CODE_FOR_OU := l_timezone_code ;
910
911 -- If Legal Entity timezone is not supported or p_ou_id IS NULL
912 -- or no Timezone code is associated to the LE, NULL
913 -- ( deafult value of return value) is returned
914
915 -- Return value
916 RETURN l_timezone_code;
917
918 END Get_Le_Tz_Code_For_Ou ;
919
920 /*===========================================================================+
921 | Function |
922 | GET_SERVER_DAY_TIME_FOR_LE |
923 | |
924 | DESCRIPTION |
925 | The function accepts a legal entity ID and a legal entity |
926 | datetime parameters and converts it to the server timezone. |
927 | If Legal entity timezone is not setup then no conversion |
928 | occurs. |
929 | |
930 | SCOPE - PUBLIC |
931 | |
932 | |
933 | ARGUMENTS : IN : p_le_date |
934 | : p_le_id |
935 | OUT: |
936 | |
937 | RETURNS : DATE |
938 | |
939 | NOTES |
940 | |
941 | MODIFICATION HISTORY |
942 | rbasker 29-Jun-2005 Created |
943 | |
944 +===========================================================================*/
945 FUNCTION Get_Server_Day_Time_For_Le
946 (p_le_date IN DATE
947 ,p_le_id IN NUMBER
948 )
949 RETURN DATE
950 IS
951
952 l_timezone_code VARCHAR2(50) := NULL;
953 l_timezone_id NUMBER := NULL;
954 l_srv_day_time DATE := NULL;
955 l_return_status VARCHAR2(30) ;
956 l_msg_count NUMBER ;
957 l_msg_data VARCHAR2(2000) ;
958 l_return_status1 VARCHAR2(30) ;
959 l_msg_count1 NUMBER ;
960 l_msg_data1 VARCHAR2(2000) ;
961 l_legal_entity_id XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID%TYPE;
962
963 BEGIN
964
965 -- check if Legal Entity Timezone is enabled AND p_le_id IS NOT NULL
966 IF G_ENABLE_LE_TIMEZONE = 'Y' AND p_le_id IS NOT NULL THEN
970 l_timezone_code := G_PREV_TZ_CODE_FOR_LE ;
967 -- for performance, verify if result is in cache
968 -- If not, run the cursor.
969 IF p_le_id = G_PREV_LE_ID THEN
971 l_timezone_id := G_PREV_TZ_ID_FOR_LE ;
972 ELSE
973
974 -- Get the timezone code and id for the passed LE
975 l_legal_entity_id := Get_LegalEntity_ID( P_ATTRIBUTE => 'LEGAL_ENTITY_ID',
976 P_ATTRIBUTE_VALUE => p_le_id );
977
978 -- Call the Get_LE_TimeZone_Info API to get the timezone information for LE
979 Get_LE_TimeZone_Info (
980 p_api_version => 1.0
981 , p_init_msg_list => FND_API.G_FALSE
982 , p_commit => FND_API.G_FALSE
983 , x_return_status => l_return_status
984 , x_msg_count => l_msg_count
985 , x_msg_data => l_msg_data
986 , p_legalentity_id => l_legal_entity_id
987 , x_timezone_code => l_timezone_code
988 , x_timezone_id => l_timezone_id) ;
989
990 -- if any error occurs propagate as unexpected error
991 IF l_return_status = FND_API.G_RET_STS_ERROR OR
992 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
993 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
994 END IF ;
995
996 END IF ;
997
998 -- If NO Timezone code was associated to the Location of the LE
999 -- OR the server and LE timezone are identycal then return the p_le_date
1000 IF l_timezone_code IS NULL
1001 OR l_timezone_code = G_SERVER_TZ_CODE THEN
1002 l_srv_day_time := p_le_date ;
1003 ELSE
1004
1005 -- Call the get_time API to convert the server timezone date
1006 -- to the LE timezone
1007 HZ_TIMEZONE_PUB.Get_Time
1008 ( p_api_version => 1.0
1009 , p_init_msg_list => FND_API.G_FALSE
1010 , p_source_tz_id => l_timezone_id
1011 , p_dest_tz_id => G_SERVER_TZ_ID
1012 , p_source_day_time => p_le_date
1013 , x_dest_day_time => l_srv_day_time
1014 , x_return_status => l_return_status1
1015 , x_msg_count => l_msg_count1
1016 , x_msg_data => l_msg_data1 ) ;
1017
1018 -- if any error occurs propagate as unexpected error
1019 IF l_return_status1 = FND_API.G_RET_STS_ERROR OR
1020 l_return_status1 = FND_API.G_RET_STS_UNEXP_ERROR THEN
1021 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1022 END IF ;
1023
1024 END IF ;
1025
1026 -- cache the current values
1027 G_PREV_LE_ID := p_le_id ;
1028 G_PREV_TZ_ID_FOR_LE := l_timezone_id ;
1029 G_PREV_TZ_CODE_FOR_LE := l_timezone_code ;
1030
1031 -- If Legal Entity timezone is not supported or p_le_id IS NULL
1032 -- p_le_date is defaulted
1033 ELSE
1034 l_srv_day_time := p_le_date ;
1035 END IF ;
1036
1037
1038 -- Return value
1039 RETURN l_srv_day_time ;
1040
1041 END Get_Server_Day_Time_For_Le ;
1042
1043 /*===========================================================================+
1044 | Function |
1045 | GET_LE_DAY_FOR_SERVER |
1046 | |
1047 | DESCRIPTION |
1048 | The function accepts a transaction datetime in the server |
1049 | timezone and the legal entity id, then converts the |
1050 | datetime to the legal entity timezone, truncates the |
1051 | timestamps and return the date. |
1052 | If legal entity timezone is not setup then no conversion |
1053 | occurs. |
1054 | |
1055 | SCOPE - PUBLIC |
1056 | |
1057 | |
1058 | ARGUMENTS : IN : p_trxn_date |
1059 | p_inv_org_id |
1060 | OUT : |
1061 | |
1062 | RETURNS : DATE |
1063 | |
1064 | NOTES |
1065 | |
1066 | MODIFICATION HISTORY |
1067 | rbasker 29-Jun-2005 Created |
1068 | |
1069 +===========================================================================*/
1070 FUNCTION Get_Le_Day_For_Server
1071 (p_trxn_date IN DATE
1072 ,p_le_id IN NUMBER
1073 )
1074 RETURN DATE
1075 IS
1076
1080 l_return_status VARCHAR2(30) ;
1077 l_timezone_code VARCHAR2(50) := NULL;
1078 l_timezone_id NUMBER := NULL;
1079 l_le_day_time DATE := NULL;
1081 l_msg_count NUMBER ;
1082 l_msg_data VARCHAR2(2000) ;
1083 l_return_status1 VARCHAR2(30) ;
1084 l_msg_count1 NUMBER ;
1085 l_msg_data1 VARCHAR2(2000) ;
1086 l_legal_entity_id XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID%TYPE;
1087
1088 BEGIN
1089
1090 -- check if Legal Entity Timezone is enabled AND p_le_id is NOT NULL
1091 IF G_ENABLE_LE_TIMEZONE = 'Y' AND p_le_id IS NOT NULL THEN
1092 -- for performance, verify if result is in cache
1093 -- If not, run the cursor.
1094 IF p_le_id = G_PREV_LE_ID THEN
1095 l_timezone_code := G_PREV_TZ_CODE_FOR_LE ;
1096 l_timezone_id := G_PREV_TZ_ID_FOR_LE ;
1097 ELSE
1098 -- Get the timezone code and id for the passed LE
1099 l_legal_entity_id := Get_LegalEntity_ID( P_ATTRIBUTE => 'LEGAL_ENTITY_ID',
1100 P_ATTRIBUTE_VALUE => p_le_id );
1101
1102 -- Call the Get_LE_TimeZone_Info API to get the timezone information for LE
1103 Get_LE_TimeZone_Info (
1104 p_api_version => 1.0
1105 , p_init_msg_list => FND_API.G_FALSE
1106 , p_commit => FND_API.G_FALSE
1107 , x_return_status => l_return_status
1108 , x_msg_count => l_msg_count
1109 , x_msg_data => l_msg_data
1110 , p_legalentity_id => l_legal_entity_id
1111 , x_timezone_code => l_timezone_code
1112 , x_timezone_id => l_timezone_id) ;
1113
1114 -- if any error occurs propagate as unexpected error
1115 IF l_return_status = FND_API.G_RET_STS_ERROR OR
1116 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1117 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1118 END IF ;
1119
1120 END IF ;
1121
1122 -- If NO Timezone code was associated to the Location of the LE
1123 -- OR the server and LE timezone are identycal then return the p_trxn_date
1124 IF l_timezone_code IS NULL
1125 OR l_timezone_code = G_SERVER_TZ_CODE THEN
1126 l_le_day_time := p_trxn_date ;
1127 ELSE
1128
1129 -- Call the get_time API to convert the server timezone date
1130 -- to the LE timezone
1131 HZ_TIMEZONE_PUB.Get_Time
1132 ( p_api_version => 1.0
1133 , p_init_msg_list => FND_API.G_FALSE
1134 , p_source_tz_id => G_SERVER_TZ_ID
1135 , p_dest_tz_id => l_timezone_id
1136 , p_source_day_time => p_trxn_date
1137 , x_dest_day_time => l_le_day_time
1138 , x_return_status => l_return_status1
1139 , x_msg_count => l_msg_count1
1140 , x_msg_data => l_msg_data1 ) ;
1141
1142 -- if any error occurs propagate as unexpected error
1143 IF l_return_status1 = FND_API.G_RET_STS_ERROR OR
1144 l_return_status1 = FND_API.G_RET_STS_UNEXP_ERROR THEN
1145 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1146 END IF ;
1147
1148 END IF ;
1149
1150 -- cache the current values
1151 G_PREV_LE_ID := p_le_id ;
1152 G_PREV_TZ_ID_FOR_LE := l_timezone_id ;
1153 G_PREV_TZ_CODE_FOR_LE := l_timezone_code ;
1154
1155 -- If Legal Entity timezone is not supported or p_le_id IS NULL
1156 -- p_le_date is defaulted
1157 ELSE
1158 l_le_day_time := p_trxn_date ;
1159 END IF ;
1160
1161 -- Return value
1162 RETURN TRUNC(l_le_day_time) ;
1163
1164 END Get_Le_Day_For_Server ;
1165
1166 /*===========================================================================+
1167 | Function |
1168 | GET_LE_DAY_TIME_FOR_SERVER |
1169 | |
1170 | DESCRIPTION |
1171 | The function accepts a transaction datetime in the server |
1172 | timezone and the legal entity id, then converts the |
1173 | datetime to the legal entity timezone and returns the date |
1174 | and the time. |
1175 | If legal entity timezone is not setup then no conversion |
1176 | occurs. |
1177 | |
1178 | SCOPE - PUBLIC |
1179 | |
1180 | |
1181 | ARGUMENTS : IN : p_trxn_date |
1182 | p_le_id |
1183 | OUT : |
1184 | |
1185 | RETURNS : DATE |
1186 | |
1187 | NOTES |
1188 | |
1189 | MODIFICATION HISTORY |
1190 | JMARY 06-NOV-2006 Created |
1191 | |
1192 +===========================================================================*/
1193 FUNCTION Get_Le_Day_Time_For_Server
1194 (p_trxn_date IN DATE
1195 ,p_le_id IN NUMBER
1196 )
1197 RETURN DATE
1198 IS
1199
1200 l_timezone_code VARCHAR2(50) := NULL;
1201 l_timezone_id NUMBER := NULL;
1202 l_le_day_time DATE := NULL;
1203 l_return_status VARCHAR2(30) ;
1204 l_msg_count NUMBER ;
1205 l_msg_data VARCHAR2(2000) ;
1206 l_return_status1 VARCHAR2(30) ;
1207 l_msg_count1 NUMBER ;
1208 l_msg_data1 VARCHAR2(2000) ;
1209 l_legal_entity_id XLE_ENTITY_PROFILES.LEGAL_ENTITY_ID%TYPE;
1210
1211 BEGIN
1212
1213 -- check if Legal Entity Timezone is enabled AND p_le_id is NOT NULL
1214 IF G_ENABLE_LE_TIMEZONE = 'Y' AND p_le_id IS NOT NULL THEN
1215 -- for performance, verify if result is in cache
1216 -- If not, run the cursor.
1217 IF p_le_id = G_PREV_LE_ID THEN
1218 l_timezone_code := G_PREV_TZ_CODE_FOR_LE ;
1219 l_timezone_id := G_PREV_TZ_ID_FOR_LE ;
1220 ELSE
1221 -- Get the timezone code and id for the passed LE
1222 l_legal_entity_id := Get_LegalEntity_ID( P_ATTRIBUTE => 'LEGAL_ENTITY_ID',
1223 P_ATTRIBUTE_VALUE => p_le_id );
1224
1225 -- Call the Get_LE_TimeZone_Info API to get the timezone information for LE
1226 Get_LE_TimeZone_Info (
1227 p_api_version => 1.0
1228 , p_init_msg_list => FND_API.G_FALSE
1229 , p_commit => FND_API.G_FALSE
1230 , x_return_status => l_return_status
1231 , x_msg_count => l_msg_count
1232 , x_msg_data => l_msg_data
1233 , p_legalentity_id => l_legal_entity_id
1234 , x_timezone_code => l_timezone_code
1235 , x_timezone_id => l_timezone_id) ;
1236
1237 -- if any error occurs propagate as unexpected error
1238 IF l_return_status = FND_API.G_RET_STS_ERROR OR
1239 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
1240 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1241 END IF ;
1242
1243 END IF ;
1244
1245 -- If NO Timezone code was associated to the Location of the LE
1246 -- OR the server and LE timezone are identycal then return the p_trxn_date
1247 IF l_timezone_code IS NULL
1248 OR l_timezone_code = G_SERVER_TZ_CODE THEN
1249 l_le_day_time := p_trxn_date ;
1250 ELSE
1251
1252 -- Call the get_time API to convert the server timezone date
1253 -- to the LE timezone
1254 HZ_TIMEZONE_PUB.Get_Time
1255 ( p_api_version => 1.0
1256 , p_init_msg_list => FND_API.G_FALSE
1257 , p_source_tz_id => G_SERVER_TZ_ID
1258 , p_dest_tz_id => l_timezone_id
1259 , p_source_day_time => p_trxn_date
1260 , x_dest_day_time => l_le_day_time
1261 , x_return_status => l_return_status1
1262 , x_msg_count => l_msg_count1
1263 , x_msg_data => l_msg_data1 ) ;
1264
1265 -- if any error occurs propagate as unexpected error
1266 IF l_return_status1 = FND_API.G_RET_STS_ERROR OR
1267 l_return_status1 = FND_API.G_RET_STS_UNEXP_ERROR THEN
1268 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1269 END IF ;
1270
1271 END IF ;
1272
1273 -- cache the current values
1274 G_PREV_LE_ID := p_le_id ;
1275 G_PREV_TZ_ID_FOR_LE := l_timezone_id ;
1276 G_PREV_TZ_CODE_FOR_LE := l_timezone_code ;
1277
1278 -- If Legal Entity timezone is not supported or p_le_id IS NULL
1279 -- p_le_date is defaulted
1280 ELSE
1281 l_le_day_time := p_trxn_date ;
1282 END IF ;
1283
1284 -- Return value
1285 RETURN l_le_day_time ;
1286
1287 END Get_Le_Day_Time_For_Server ;
1288
1289
1290
1291
1292
1293
1294 BEGIN
1295
1296 -- Package initialization. Use to get/cache the Server timezone code,
1297 -- and other profile values
1298
1299 -- check if Legal Entity Timezone conversion is enabled
1300 G_ENABLE_LE_TIMEZONE := NVL(fnd_profile.value('XLE_ENABLE_LEGAL_ENTITY_TIMEZONE'),'N') ;
1301 G_ENABLE_LE_TIMEZONE := 'N';
1302 -- If LE Timezone is enabled Get the server timezone code and timezone id
1303 IF G_ENABLE_LE_TIMEZONE = 'Y' THEN
1304 SELECT timezone_code ,
1305 upgrade_tz_id
1306 INTO G_SERVER_TZ_CODE ,
1307 G_SERVER_TZ_ID
1308 FROM fnd_timezones_b
1309 WHERE upgrade_tz_id =
1310 to_number( fnd_profile.value_specific('SERVER_TIMEZONE_ID')) ;
1311
1312 END IF ;
1313
1314 END XLE_LE_TIMEZONE_GRP;
[Home] [Help]