1 PACKAGE CAC_AVLBLTY_PUB AUTHID CURRENT_USER AS
2 /* $Header: caccabs.pls 120.2 2008/01/09 12:51:33 lokumar ship $ */
3
4 /*******************************************************************************
5 ** Datatypes used in APIs
6 *******************************************************************************/
7 /**
8 CREATE OR REPLACE TYPE CAC_AVLBLTY_TIME_BAND AS
9 OBJECT( START_DATE_TIME DATE -- The start date and time of the period detail
10 , END_DATE_TIME DATE -- The end date and time of the period detail
11 , MIN_BREAK_MINS NUMBER -- Minimum number of minutes available for break
12 , MAX_BREAK_MINS NUMBER -- Maximum number of minutes available for break
13 , CORE_FLAG VARCHAR2(1) -- Flag indicating if the period detail is a core one or not
14 , PERIOD_DTL_TYPE_ID NUMBER -- Type of the period detail,
15 , PERIOD_DTL_TYPE_NAME VARCHAR2(2000) -- like 'Core working hours', 'Flexible lunch' etc.
16 );
17
18 CREATE OR REPLACE TYPE CAC_AVLBLTY_TIME_BAND_VARRAY AS
19 VARRAY(100000) OF CAC_AVLBLTY_TIME_BAND;
20
21 CREATE OR REPLACE TYPE CAC_AVLBLTY_TIME AS
22 OBJECT( PERIOD_NAME VARCHAR2(2000) -- Name of the shift
23 , START_DATE_TIME DATE -- The start date and time of the period
24 , END_DATE_TIME DATE -- The end date and time of the period
25 , DURATION_MS NUMBER -- Duration of the period in milliseconds.
26 -- This could be less than end date-start date if the period is of flexible type
27 , PERIOD_CATEGORY_ID NUMBER -- Category Id of the shift
28 , PERIOD_CATEGORY_NAME VARCHAR2(2000) -- Category Name of the shift
29 , FREE_BUSY_TYPE VARCHAR2(30) -- FREE / BUSY / BUSY_TENTATIVE associated with the period category
30 , DISPLAY_COLOR VARCHAR2(30) -- Display color associated with the period category
31 , SHIFT_BANDS CAC_AVLBLTY_TIME_BAND_VARRAY -- An array containing shift bands.
32 -- It's populated only the if category of the period allows period details
33 , SUPER_OBJECT_INDEX NUMBER -- This will contain the index of the period in the VARRRAY
34 -- that was overwritten by the current one.
35 -- For example, in the case of a holiday on a regular working day,
36 -- the super object will have the period as defined by the schedule
37 -- and the current object will be that of the holiday.
38 -- This could go up to any level
39 , NEXT_OBJECT_INDEX NUMBER -- This will contain the index of the period in the VARRRAY
40 -- that comes after this one.
41 -- This should be used to browse the VARRAY
42 );
43
44 CREATE OR REPLACE TYPE CAC_AVLBLTY_TIME_VARRAY AS
45 VARRAY(100000) OF CAC_AVLBLTY_TIME;
46
47 CREATE OR REPLACE TYPE CAC_AVLBLTY_DAY_TIME AS
48 OBJECT( START_TIME_MS NUMBER -- Start time of this record in milliseconds
49 -- This is calculated since the start of day (00:00)
50 , END_TIME_MS NUMBER -- End time of this record in milliseconds
51 -- This is calculated since the start of day (00:00)
52 );
53
54 CREATE OR REPLACE TYPE CAC_AVLBLTY_DAY_TIME_VARRAY AS
55 VARRAY(100000) OF CAC_AVLBLTY_DAY_TIME;
56
57 CREATE OR REPLACE TYPE CAC_AVLBLTY_DETAIL AS
58 OBJECT( TOTAL_TIME_MS NUMBER -- Total time of this line
59 , PERIOD_CATEGORY_ID NUMBER -- Category Id of the shift
60 , PERIOD_CATEGORY_NAME VARCHAR2(2000) -- Category Name of the shift
61 , FREE_BUSY_TYPE VARCHAR2(30) -- FREE / BUSY / BUSY_TENTATIVE associated with the period category
62 , DISPLAY_COLOR VARCHAR2(30) -- Display color associated with the period category
63 , DAY_TIMES CAC_AVLBLTY_DAY_TIME_VARRAY -- Times of the day where this line is applicable
64 -- for example, a person can be free at 09:00 to 12:00
65 -- and then again at 13:00 to 17:00.
66 -- This attribute will have two rows in that case
67 );
68
69 CREATE OR REPLACE TYPE CAC_AVLBLTY_DETAIL_VARRAY AS
70 VARRAY(100000) OF CAC_AVLBLTY_DETAIL;
71
72 CREATE OR REPLACE TYPE CAC_AVLBLTY_SUMMARY AS
73 OBJECT( SUMMARY_DATE DATE -- The day (date) for which summary was calculated
74 , SUMMARY_LINES CAC_AVLBLTY_DETAIL_VARRAY -- Detail lines for the summary day.
75 -- This will contain different categories in case
76 -- person is working on different shifts.
77 -- For each category there will be a row
78 );
79
80 CREATE OR REPLACE TYPE CAC_AVLBLTY_SUMMARY_VARRAY AS
81 VARRAY(100000) OF CAC_AVLBLTY_SUMMARY;
82
83 **/
84
85
86 /*******************************************************************************
87 ** Public APIs
88 *******************************************************************************/
89
90 PROCEDURE GET_SCHEDULE
91 /*******************************************************************************
92 ** GET_SCHEDULE
93 **
94 ** Roughly translates to JTF_CALENDAR_PUB_24HR.Get_Resource_Shifts API.
95 ** It will return a list of periods for which the given Object is considered
96 ** to be available. The algorithme used is as follows:
97 **
98 ** 24*7*365 (full availability if no constraints are defined)
99 ** Schedule (if a schedule was defined we'll use it)
100 ** Holidays (if Holidays are defined in HR we'll honor them)
101 ** Exceptions - (Resource level Exceptions will be honored)
102 ** --------------
103 ** Schedule
104 **
105 *******************************************************************************/
106 ( p_api_version IN NUMBER -- API version you coded against
107 , p_init_msg_list IN VARCHAR2 -- Create a new error stack?
108 , p_Object_Type IN VARCHAR2 -- JTF OBJECTS type of the Object being queried
109 , p_Object_ID IN NUMBER -- JTF OBJECTS select ID of the Object Instance being queried
110 , p_Start_Date_Time IN DATE -- start date and time of period of interest
111 , p_End_Date_Time IN DATE -- end date and time of period of interest
112 , p_Schedule_Category IN VARCHAR2 -- Schedule Category of the schedule instance we'll look at
113 , p_Include_Exception IN VARCHAR2 -- 'T' or 'F' depending on whether the exceptions be included or not
114 , p_Busy_Tentative IN VARCHAR2 -- How to treat periods with FREEBUSYTYPE = BUSY TENTATIVE?
115 -- FREE: BUSY TENTATIVE means FREE
116 -- BUSY: BUSY TENTATIVE means BUSY
117 -- NULL: leave the interpretation to caller
118 , x_Schedule OUT NOCOPY CAC_AVLBLTY_TIME_VARRAY
119 -- return schedule
120 , x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
121 -- 'E': API completed with recoverable errors; explanation on errorstack
122 -- 'U': API completed with UN recoverable errors: error message on error stack
123 , x_msg_count OUT NOCOPY NUMBER -- Number of messages on the errorstack
124 , x_msg_data OUT NOCOPY VARCHAR2 -- contains message if x_msg_count = 1
125 );
126
127
128 PROCEDURE GET_SCHEDULE_SUMMARY
129 /*******************************************************************************
130 ** GET_SCHEDULE_SUMMARY
131 **
132 ** This API will return summary of schedule on day by day basis
133 ** The algorithme used is as follows:
134 **
135 ** 24*7*365 (full availability if no constraints are defined)
136 ** Schedule (if a schedule was defined we'll use it)
137 ** Holidays (if Holidays are defined in HR we'll honor them)
138 ** Exceptions - (Resource level Exceptions will be honored)
139 ** --------------
140 ** Schedule
141 **
142 *******************************************************************************/
143 ( p_api_version IN NUMBER -- API version you coded against
144 , p_init_msg_list IN VARCHAR2 -- Create a new error stack?
145 , p_Object_Type IN VARCHAR2 -- JTF OBJECTS type of the Object being queried
146 , p_Object_ID IN NUMBER -- JTF OBJECTS select ID of the Object Instance being queried
147 , p_Start_Date IN DATE -- start date of period of interest
148 , p_End_Date IN DATE -- end date of period of interest
149 , p_Schedule_Category IN VARCHAR2 -- Schedule Category of the schedule instance we'll look at
150 , p_Include_Exception IN VARCHAR2 -- 'T' or 'F' depending on whether the exceptions be included or not
151 , p_Busy_Tentative IN VARCHAR2 -- How to treat periods with FREEBUSYTYPE = BUSY TENTATIVE?
152 -- FREE: BUSY TENTATIVE means FREE
153 -- BUSY: BUSY TENTATIVE means BUSY
154 -- NULL: leave the interpretation to caller
155 , x_Schedule_Summary OUT NOCOPY CAC_AVLBLTY_SUMMARY_VARRAY
156 -- return schedule summary
157 , x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
158 -- 'E': API completed with recoverable errors; explanation on errorstack
159 -- 'U': API completed with UN recoverable errors: error message on error stack
160 , x_msg_count OUT NOCOPY NUMBER -- Number of messages on the errorstack
161 , x_msg_data OUT NOCOPY VARCHAR2 -- contains message if x_msg_count = 1
162 );
163
164
165 PROCEDURE IS_AVAILABLE
166 /*****************************************************************************
167 ** Method IS_AVAILABLE
168 **
169 ** Roughly translates to JTF_CALENDAR_PUB_24HR. Is_Res_Available API.
170 ** It will return:
171 ** - 'T' if the resource is available for the given period
172 ** - 'F' if the resource is unavailable for the given period
173 **
174 *******************************************************************************/
175 ( p_api_version IN NUMBER -- API version you coded against
176 , p_init_msg_list IN VARCHAR2 -- Create a new error stack?
177 , p_Object_Type IN VARCHAR2 -- JTF OBJECTS type of the Object being queried
178 , p_Object_ID IN NUMBER -- JTF OBJECTS select ID of the Object Instance being queried
179 , p_Start_Date_Time IN DATE -- start date and time of period of interest
180 , p_End_Date_Time IN DATE -- end date and time of period of interest
181 , p_Schedule_Category IN VARCHAR2 -- Schedule Category of the schedule instance we'll look at
182 , p_Busy_Tentative IN VARCHAR2 -- How to treat periods with FREEBUSYTYPE = BUSY TENTATIVE?
183 -- FREE: BUSY TENTATIVE means FREE
184 -- BUSY: BUSY TENTATIVE means BUSY
185 -- NULL: leave the interpretation to caller
186 , p_task_assignment_id IN NUMBER DEFAULT NULL -- specifies the task assignment id to be ignored while checking availability
187 -- Added by lokumar for bug#6345516
188 , x_Available OUT NOCOPY VARCHAR2 -- 'T' or 'F'
189 , x_return_status OUT NOCOPY VARCHAR2 -- 'S': API completed without errors
190 -- 'E': API completed with recoverable errors; explanation on errorstack
191 -- 'U': API completed with UN recoverable errors: error message on error stack
192 , x_msg_count OUT NOCOPY NUMBER -- Number of messages on the errorstack
193 , x_msg_data OUT NOCOPY VARCHAR2 -- contains message if x_msg_count = 1
194 );
195
196
197 END CAC_AVLBLTY_PUB;
[Home] [Help]