1 PACKAGE OTA_TIMEZONE_UTIL AUTHID CURRENT_USER as
2 /* $Header: ottznutl.pkh 120.0.12000000.1 2007/01/18 05:29:11 appldev noship $ */
3
4 CURSOR get_class_info(p_class_id NUMBER) IS
5 SELECT ctu.online_flag, evt.timezone
6 FROM ota_events evt
7 ,ota_offerings ofn
8 ,ota_category_usages ctu
9 WHERE evt.parent_offering_id = ofn.offering_id
10 AND ctu.category_usage_id = ofn.delivery_mode_id
11 AND ctu.type = 'DM'
12 AND evt.event_id = p_class_id;
13
14 CURSOR get_resource_booking_info(p_resource_booking_id NUMBER) IS
15 SELECT ctu.online_flag
16 ,trb.timezone_code
17 ,ftl.name timezone
18 FROM ota_resource_bookings trb
19 , ota_events evt
20 , ota_category_usages ctu
21 , ota_offerings ofn
22 , fnd_timezones_tl ftl
23 WHERE evt.parent_offering_id = ofn.offering_id
24 AND evt.event_id = trb.event_id
25 AND ctu.category_usage_id = ofn.delivery_mode_id
26 AND ctu.type = 'DM'
27 AND trb.resource_booking_id = p_resource_booking_id
28 AND ftl.timezone_code = trb.timezone_code
29 AND ftl.LANGUAGE = USERENV('LANG');
30
31 -- Returns the HZ Time zone Id corresponding to the Time zone code
32 -- It is used for 11i code.
33 FUNCTION Get_Timezone_ID(p_timezone_code in varchar2)
34 RETURN NUMBER;
35
36 -- Returns the server time zone code set by the profile "Server Timezone"
37 FUNCTION get_server_timezone_code
38 RETURN VARCHAR2;
39
40 -- Returns the Client Time Zone code and its name
41 PROCEDURE get_client_timezone_vals(
42 p_timezone_code OUT NOCOPY varchar2
43 ,p_timezone_name OUT NOCOPY varchar2);
44
45 PROCEDURE get_event_timezone_vals(
46 p_event_id IN NUMBER
47 ,p_timezone_code OUT NOCOPY VARCHAR2
48 ,p_timezone_name OUT NOCOPY VARCHAR2);
49
50 -- Returns the time zone name corresponding to the time zone code
51 FUNCTION get_timezone_name(p_timezone_code in varchar2)
52 RETURN varchar2;
53
54 -- Converts date from one time zone to another.
55 FUNCTION convert_date(p_datevalue in DATE
56 ,p_timevalue IN VARCHAR2
57 ,p_src_timezone_code IN VARCHAR2
58 ,p_dest_timezone_code IN VARCHAR2)
59 RETURN DATE;
60
61 -- Returns the date and time converted from one time zone to another
62 FUNCTION convert_dateDT_time(p_datevalue in DATE
63 ,p_timevalue IN VARCHAR2
64 ,p_src_timezone_code IN VARCHAR2
65 ,p_dest_timezone_code IN VARCHAR2)
66 RETURN VARCHAR2;
67
68 /*
69 FUNCTION convert_date_fnd(p_date in DATE
70 ,p_src_timezone_code IN VARCHAR2
71 ,p_dest_timezone_code IN VARCHAR2)
72 RETURN DATE;
73
74 FUNCTION convert_date_hz(p_date in DATE
75 ,p_src_timezone_code IN VARCHAR2
76 ,p_dest_timezone_code IN VARCHAR2)
77 RETURN DATE;
78 */
79
80 -- Returns the converted DATE
81 FUNCTION get_DateDT(
82 p_datevalue IN DATE
83 ,p_timevalue IN VARCHAR2
84 ,p_online_flag IN VARCHAR2
85 ,p_src_timezone IN VARCHAR2)
86 RETURN DATE;
87
88
89 FUNCTION get_Class_DateDT(
90 p_datevalue IN DATE
91 ,p_timevalue IN VARCHAR2
92 ,p_event_id IN NUMBER)
93 RETURN DATE;
94
95
96 FUNCTION get_resource_bookingDT(
97 p_datevalue IN DATE
98 ,p_timevalue IN VARCHAR2
99 ,p_resource_booking_id IN NUMBER)
100 RETURN DATE;
101
102 -- Returns the converted Time
103 FUNCTION get_dateDT_Time(
104 p_datevalue IN DATE
105 ,p_timevalue IN VARCHAR2
106 ,p_online_flag IN VARCHAR2
107 ,p_src_timezone IN VARCHAR2)
108 RETURN VARCHAR2;
109
110 FUNCTION get_date_time(
111 p_datevalue IN DATE
112 ,p_timevalue IN VARCHAR2
113 ,p_online_flag IN VARCHAR2
114 ,p_src_timezone IN VARCHAR2
115 ,p_time_format IN VARCHAR2 default 'HH24:MI')
116 RETURN VARCHAR2;
117
118 end ota_timezone_util;
119
[Home] [Help]