1 PACKAGE BODY WIP_DATETIMES AS
2 /* $Header: wipdateb.pls 115.9 2003/10/31 22:37:26 rlohani ship $ */
3
4 DATE_FMT CONSTANT VARCHAR2(11) := WIP_CONSTANTS.DATE_FMT;
5 DATETIME_FMT CONSTANT VARCHAR2(22) := WIP_CONSTANTS.DATETIME_FMT;
6
7 /* canonical date varchar to user display date varchar */
8 FUNCTION Cchar_to_Uchar(Cchar IN VARCHAR2)
9 RETURN VARCHAR2
10 IS
11 BEGIN
12 RETURN (fnd_date.date_to_chardate(to_date(Cchar, DATE_FMT)));
13 END Cchar_to_Uchar;
14
15 /* canonical datetime varchar to user display datetime varchar */
16 FUNCTION CcharDT_to_Uchar(CcharDT IN VARCHAR2)
17 RETURN VARCHAR2
18 IS
19 BEGIN
20 RETURN (fnd_date.date_to_charDT(to_date(CcharDT, DATETIME_FMT)));
21 END CcharDT_to_Uchar;
22
23 /* canonical date varchar to date[time] varchar in Ofmt_mask format mask */
24 FUNCTION Cchar_to_char(Cchar IN VARCHAR2, Ofmt_mask IN VARCHAR2)
25 RETURN VARCHAR2
26 IS
27 BEGIN
28 RETURN (to_char(to_date(Cchar, DATE_FMT), Ofmt_mask));
29 END Cchar_to_char;
30
31 /* canonical datetime varchar to date[time] varchar in Ofmt_mask format mask */
32 FUNCTION CcharDT_to_char(CcharDT IN VARCHAR2, Ofmt_mask IN VARCHAR2)
33 RETURN VARCHAR2
34 IS
35 BEGIN
36 RETURN (to_char(to_date(CcharDT, DATETIME_FMT), Ofmt_mask));
37 END CcharDT_to_char;
38
39 /* canonical datetime varchar to date */
40 FUNCTION CcharDT_to_date(CcharDT IN VARCHAR2)
41 RETURN DATE
42 IS
43 BEGIN
44 RETURN (to_date(CcharDT, DATETIME_FMT));
45 END CcharDT_to_date;
46
47 /* takes two datetimes and returns their difference in minutes*/
48 FUNCTION datetime_diff_to_mins(dt1 DATE, dt2 DATE)
49 RETURN NUMBER
50 IS
51 BEGIN
52 -- dt1 - dt2 gives the difference in days. That * 1440 gives the diff in mins.
53 RETURN to_number(round((dt1 - dt2)*1440));
54
55 END datetime_diff_to_mins;
56
57 /* this function takes a date and a number (seconds to represent the
58 time since 00:00:00 of this date) and return a date */
59 FUNCTION Date_Timenum_to_DATE(dt dATE, time number)
60 RETURN DATE
61 IS
62 BEGIN
63 return float_to_DT(DT_to_float(dt) + time/86400);
64 END Date_Timenum_to_DATE;
65
66 /* this function returns the julian date in floating point format */
67 FUNCTION DT_to_float(dt DATE)
68 RETURN NUMBER
69 IS
70 BEGIN
71 RETURN to_number(dt - to_date(1,'J'))+1;
72
73 END DT_to_float;
74
75 /* this function takes a julian date in a floating point format and returns a date */
76 FUNCTION float_to_DT(fdt NUMBER)
77 RETURN DATE
78 IS
79 BEGIN
80 RETURN to_date(1,'J')+(fdt-1);
81
82 END float_to_DT;
83
84 /* this function takes a in a date only value in LE Timezone, appends 23:59:59
85 and then returns date in server timezone */
86
87 FUNCTION le_date_to_server(p_le_date DATE,
88 p_org_id NUMBER) RETURN DATE
89 IS
90 l_le_tz_code VARCHAR2(50);
91 l_le_tz_id NUMBER;
92 l_ret_date DATE := NULL;
93 l_return_status VARCHAR2(30) ;
94 l_msg_count NUMBER ;
95 l_msg_data VARCHAR2(2000) ;
96 CURSOR c_tz_id(p_tz_code VARCHAR2) IS
97 SELECT upgrade_tz_id
98 FROM fnd_timezones_vl
99 WHERE timezone_code = p_tz_code;
100
101 BEGIN
102 l_le_tz_code := inv_le_timezone_pub.get_le_tz_code_for_inv_org(p_org_id);
103 if (l_le_tz_code IS NOT NULL AND
104 NVL(fnd_profile.value('ENABLE_TIMEZONE_CONVERSIONS'),'N') = 'Y') THEN
105 OPEN c_tz_id(l_le_tz_code);
106 FETCH c_tz_id INTO l_le_tz_id;
107 CLOSE c_tz_id;
108 --
109 -- Call the get_time API to convert the server timezone date
110 -- to the LE timezone
111 HZ_TIMEZONE_PUB.Get_Time
112 ( p_api_version => 1.0
113 , p_init_msg_list => FND_API.G_FALSE
114 , p_source_tz_id => l_le_tz_id
115 , p_dest_tz_id => fnd_profile.value('SERVER_TIMEZONE_ID')
116 , p_source_day_time => (p_le_date + 1 - 1/24/60/60)
117 , x_dest_day_time => l_ret_date
118 , x_return_status => l_return_status
119 , x_msg_count => l_msg_count
120 , x_msg_data => l_msg_data ) ;
121
122 -- if any error occurs propagate as unexpected error
123 IF l_return_status = FND_API.G_RET_STS_ERROR OR
124 l_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
125 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
126 END IF ;
127 else
128 l_ret_date := p_le_date + 1 - 1/24/60/60;
129 end if;
130 RETURN l_ret_date;
131 END le_date_to_server;
132
133
134 END WIP_DATETIMES;
[Home] [Help]