1 PACKAGE csf_util_pvt AS
2 /* $Header: CSFVUTLS.pls 120.4 2006/05/22 11:28:16 venjayar noship $ */
3
4 g_timing_activated BOOLEAN;
5 g_logging_activated BOOLEAN;
6
7 /**
8 * Returns the Address of a Location given the Location ID.
9 * <br>
10 * Supports two formats of Addresses - Default being Complete
11 * 1. Complete Address - Address 1, 2, 3, 4, ZIP, City, State, Country
12 * 2. Short Address - ZIP, City, State / Province
13 *
14 * @param p_location_id Location ID corresponding to the Address desired
15 * @param p_small_flag Short ('Y') / Complete ('N') Address (Optional)
16 */
17 FUNCTION get_address(p_location_id NUMBER, p_small_flag VARCHAR2 DEFAULT NULL)
18 RETURN VARCHAR2;
19
20 /**
21 * Adds a Timing Mark in the CSR_TIMERS_B so that we can assess the performance of
22 * each operation.
23 *
24 * @param p_seq Sequence Number of the Timer
25 * @param p_name Name of the Timing Mark Logged
26 * @param p_type
27 * @param p_descr Description of the Timing Mark Logged for better information.
28 */
29 PROCEDURE add_timer(p_seq NUMBER, p_name VARCHAR2, p_type NUMBER, p_descr VARCHAR2);
30
31 /**
32 * Checks whether the given Territory ID is one among the Selected Territories
33 * of the given user. If no user is no given, then it checks using the signed
34 * in user (FND_GLOBAL.USER_ID).
35 *
36 * @param p_terr_id Territory ID of the Territory to be checked.
37 * @param p_user_id User ID of the user whose list of Territories is used.
38 */
39 FUNCTION is_terr_selected(p_terr_id IN NUMBER, p_user_id IN NUMBER DEFAULT NULL)
40 RETURN NUMBER;
41
42 /**
43 * Retuns the list of Territories added to the given User.
44 *
45 * @param p_user_id User ID of the user to get the list of User's Territories
46 */
47 FUNCTION get_selected_terr(p_user_id NUMBER DEFAULT NULL)
48 RETURN VARCHAR2;
49
50 /**
51 * Gets the List of Territories selected as a PLSQL Table
52 *
53 * @param p_user_id User ID of the user to get the list of User's Territories
54 */
55 FUNCTION get_selected_terr_table(p_user_id NUMBER DEFAULT NULL)
56 RETURN jtf_number_table;
57
58 /**
59 * Sets the List of Territories which will be valid for the User.
60 *
61 * @param p_selected_terr List of Territories
62 * @param p_user_id User ID of the user to set the new list of Territories
63 */
64 PROCEDURE set_selected_terr(
65 p_selected_terr IN VARCHAR2 DEFAULT NULL
66 , p_user_id IN NUMBER DEFAULT NULL
67 );
68
69 /**
70 * Returns the Object Name given the Object Type Code and Object ID.
71 * <p>
72 * This procedure is very useful so that the TABLE NAME is not hardcoded to get
73 * the Object Name for a given Object ID. Rather it uses the table JTF_OBJECTS
74 * to get the SQL that should be used and forms a Dynamic SQL to get the Object
75 * Name
76 *
77 * @param p_object_type_code Type Code of the Object whose Name is required
78 * @param p_object_id Identifier of the Object whose Name is required
79 */
80 FUNCTION get_object_name(p_object_type_code IN VARCHAR2, p_object_id IN NUMBER)
81 RETURN VARCHAR2;
82
83 /**
84 * Utility Function to return FND_API.G_MISS_NUM - FND API Constant
85 * to return INVALID / MISSING NUMBER (9.99E125).
86 *
87 * @return FND_API.G_MISS_NUM
88 */
89 FUNCTION get_miss_num RETURN NUMBER;
90
91 /**
92 * Utility Function to return FND_API.G_MISS_CHAR - FND API Constant
93 * to return INVALID / MISSING CHARACTER (CHR(0))
94 *
95 * @return FND_API.G_MISS_CHAR
96 */
97 FUNCTION get_miss_char RETURN VARCHAR2;
98
99 /**
100 * Utility Function to return FND_API.G_MISS_DATE - FND API Constant
101 * to return INVALID / MISSING DATE (TO_DATE('1','j')).
102 *
103 * @return FND_API.G_MISS_DATE
104 */
105 FUNCTION get_miss_date RETURN DATE;
106
107 /**
108 * Utility Function to return FND_API.G_VALID_LEVEL_NONE - FND API Constant
109 * to denote NO VALIDATION.
110 *
111 * @return FND_API.G_VALID_LEVEL_NONE
112 */
113 FUNCTION get_valid_level_none RETURN NUMBER;
114
115 /**
116 * Utility Function to return FND_API.G_VALID_LEVEL_FULL - FND API Constant
117 * to denote FULL VALIDATION.
118 *
119 * @return FND_API.G_VALID_LEVEL_FULL
120 */
121 FUNCTION get_valid_level_full RETURN NUMBER;
122
123 /**
124 * Utility Function to return FND_API.G_RET_STS_SUCCESS - FND API Constant for
125 * Return Status being SUCCESS ('S').
126 *
127 * @return FND_API.G_RET_STS_SUCCESS
128 */
129 FUNCTION get_ret_sts_success RETURN VARCHAR2;
130
131 /**
132 * Utility Function to return FND_API.G_RET_STS_ERROR - FND API Constant for
133 * Return Status being ERROR ('E').
134 *
135 * @return FND_API.G_RET_STS_ERROR
136 */
137 FUNCTION get_ret_sts_error RETURN VARCHAR2;
138
139 /**
140 * Utility Function to return FND_API.G_RET_STS_UNEXP_ERROR - FND API Constant for
141 * Return Status being UNEXPECTED ERROR ('U').
142 *
143 * @return FND_API.G_RET_STS_UNEXP_ERROR
144 */
145 FUNCTION get_ret_sts_unexp_error RETURN VARCHAR2;
146
147 /**
148 * Utility Function to return FND_API.G_TRUE - FND API Constant for
149 * TRUE ('T').
150 *
151 * @return FND_API.G_TRUE
152 */
153 FUNCTION get_true RETURN VARCHAR2;
154
155 /**
156 * Utility Function to return FND_API.G_FALSE - FND API Constant for
157 * FALSE ('F').
158 *
159 * @return FND_API.G_FALSE
160 */
161 FUNCTION get_false RETURN VARCHAR2;
162
163 /**
164 * Utility Function to return FND_MSG_PUB.G_FIRST - FND Message Constant for
165 * First Message (-1)
166 *
167 * @return FND_MSG_PUB.G_FIRST
168 */
169 FUNCTION get_first RETURN NUMBER;
170
171 /**
172 * Utility Function to return FND_MSG_PUB.G_NEXT - FND Message Constant for
173 * Next Message (-2)
174 *
175 * @return FND_MSG_PUB.G_NEXT
176 */
177 FUNCTION get_next RETURN NUMBER;
178
179 /**
180 * Utility Function to return FND_MSG_PUB.G_LAST - FND Message Constant for
181 * Last Message (-3)
182 *
183 * @return FND_MSG_PUB.G_LAST
184 */
185 FUNCTION get_last RETURN NUMBER;
186
187 /**
188 * Utility Function to return FND_MSG_PUB.G_PREVIOUS - FND Message Constant for
189 * Previous Message (-1)
190 *
191 * @return FND_MSG_PUB.G_PREVIOUS
192 */
193 FUNCTION get_previous RETURN NUMBER;
194
195 /**
196 * Utility Function to return FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR.
197 * FND Message Constant for to get Unexpected Error Message Level (60)
198 *
199 * @return FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
200 */
201 FUNCTION get_msg_lvl_unexp_error RETURN NUMBER;
202
203 /**
204 * Utility Function to return FND_MSG_PUB.G_MSG_LVL_ERROR.
205 * FND Message Constant for to get Error Message Level (50)
206 *
207 * @return FND_MSG_PUB.G_MSG_LVL_ERROR
208 */
209 FUNCTION get_msg_lvl_error RETURN NUMBER;
210
211 /**
212 * Utility Function to return FND_MSG_PUB.G_MSG_LVL_SUCCESS.
213 * FND Message Constant for to get Success Error Message Level (40)
214 *
215 * @return FND_MSG_PUB.G_MSG_LVL_SUCCESS
216 */
217 FUNCTION get_msg_lvl_success RETURN NUMBER;
218
219 /**
220 * Utility Function to return FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH.
221 * FND Message Constant for to get High Priority Debug Message Level (30)
222 *
223 * @return FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH
224 */
225 FUNCTION get_msg_lvl_debug_high RETURN NUMBER;
226
227 /**
228 * Utility Function to return FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM.
229 * FND Message Constant for to get Medium Priority Debug Message Level (20)
230 *
231 * @return FND_MSG_PUB.G_MSG_LVL_DEBUG_MEDIUM
232 */
233 FUNCTION get_msg_lvl_debug_medium RETURN NUMBER;
234
235 /**
236 * Utility Function to return FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW.
237 * FND Message Constant for to get Low Priority Debug Message Level (10)
238 *
239 * @return FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW
240 */
241 FUNCTION get_msg_lvl_debug_low RETURN NUMBER;
242
243 /**
244 * Returns the UOM Description / Name for the given UOM Code
245 *
246 * @param p_code Code of the UOM for which Name is desired.
247 */
248 FUNCTION get_uom(p_code VARCHAR2)
249 RETURN VARCHAR2;
250
251 /**
252 * Checks whether the given UOM is valid
253 *
254 * @return TRUE/FALSE depending on whether UOM is valid/invalid.
255 */
256 FUNCTION is_uom_valid(p_value VARCHAR2)
257 RETURN VARCHAR2;
258
259 /**
260 * Returns the Default UOM (in Minutes) profile by reading the profile
261 * "CSF: The unit of measure for minutes" (CSF_UOM_MINUTES).
262 *
263 * @return Value of "CSF: The unit of measure for minutes" (CSF_UOM_MINUTES)
264 */
265 FUNCTION get_uom_minutes RETURN VARCHAR2;
266
267 /**
268 * Converts the given Duration in the given Duration UOM to the UOM as defined by the
269 * profile "CSF: The unit of measure for minutes" (CSF_UOM_MINUTES) there by
270 * converting the value to Minutes.
271 *
272 * @param p_duration Duration to be converted to Minutes UOM
273 * @param p_duration_uom Source UOM
274 */
275 FUNCTION convert_to_minutes(p_duration IN NUMBER, p_duration_uom IN VARCHAR2)
276 RETURN NUMBER;
277
278 /**
279 * Gets the Task Effort along with the UOM after converting the effort so
280 * as to represent it in the Default UOM "CSF: Default Effort UOM".
281 * <br>
282 * Its better for this API to be called only for Child Tasks so that they
283 * are appropriately represented in a better UOM rather than the UOM used by
284 * Scheduler (Minutes) to create the Child Task.
285 * For Parent Tasks / Normal Tasks, the effort and its UOM should not be
286 * converted as they are entered by the Teleservice Operators.
287 * <br>
288 * Suppose the effort cannot be represented as a Whole Number in the Default
289 * UOM then the effort will be represented as a combination of many UOMs.
290 * <br>
291 * Examples
292 * --------
293 * CSF: Default Effort UOM - HR.
294 *
295 * -------------------------------------------------------
296 * | Input Effort | Input UOM | Output |
297 * -------------------------------------------------------
298 * | | | |
299 * | 50 | MIN | 50 Minute |
300 * | 60 | MIN | 1 Hour |
301 * | 70 | MIN | 1 Hour 10 Minute |
302 * | 1500 | MIN | 25 Hour |
303 * | 2 | HR | 2 Hour |
304 * | 2 | DAY | 48 Hour |
305 * -------------------------------------------------------
306 *
307 * <br>
308 * @param p_effort Effort which needs to be converted
309 * @param p_effort_uom Effort UOM of the above Task Effort
310 *
311 * @return Effort appro converted to Default UOM followed by "UOM Full Form"
312 */
313 FUNCTION get_effort_in_default_uom(p_effort NUMBER, p_effort_uom VARCHAR2)
314 RETURN VARCHAR2;
315
316 /**
317 * (<b>Deprecated</b>) Retained because Service Team is still using it.
318 * Returns the Qualifier Table having the list of valid Qualifiers
319 * based on the Task Information of the given Task ID.
320 *
321 * @deprecated Use CSF_RESOURCE_PUB.GET_RES_QUALIFIER_TABLE
322 */
323 FUNCTION get_qualifier_table ( p_task_id NUMBER )
324 RETURN csf_resource_pub.resource_qualifier_tbl_type;
325
326 /**
327 * Returns the WHERE CLAUSE of the given Query ID properly augmented
328 * with Owner Restrictions if Owner Restriction is Enabled for the Current User
329 * and the given Query.
330 *
331 * @param p_query_id Query ID for which Where Clause has to be returned
332 *
333 * @return Where Clause of the Desired Query
334 */
335 FUNCTION get_query_where ( p_query_id NUMBER )
336 RETURN VARCHAR2;
337
338 END csf_util_pvt;