1 PACKAGE AHL_LTP_RESRC_LEVL_PVT AUTHID CURRENT_USER AS
2 /* $Header: AHLVRLGS.pls 115.15 2003/11/06 00:55:31 ssurapan noship $ */
3 --
4 ---------------------------------------------------------------------
5 -- Define Record Types for record structures needed by the APIs --
6 ---------------------------------------------------------------------
7 -- Record of task hours and times
8 TYPE Visit_Task_Times_Rec_Type IS RECORD (
9 VISIT_TASK_ID NUMBER := NULL, -- Id of the visit task
10 MR_ROUTE_ID NUMBER := NULL, -- Id of the associated route
11 PARENT_TASK_ID NUMBER := NULL, -- Id of the latest ending parent (can be null)
12 TASK_START_HOUR NUMBER := NULL, -- Normalized start hour for this task (w.r.t visit)
13 TASK_DURATION NUMBER := NULL, -- Duration of the visit
14 TASK_END_HOUR NUMBER := NULL, -- Normalized end hour for this task
15 TASK_START_TIME DATE := NULL, -- Actual start time of this task
16 TASK_END_TIME DATE := NULL -- Actual end time of this task
17 );
18
19 -- Record of Plan resources
20 TYPE Plan_Rsrc_Rec_Type IS RECORD (
21 ASO_BOM_TYPE VARCHAR2(30) := NULL, -- ASO or BOM type of Resource
22 RESOURCE_ID NUMBER := NULL, -- ASO resource id or BOM resource id
23 RESOURCE_TYPE NUMBER := NULL, -- Resource type code
24 RESOURCE_TYPE_MEANING VARCHAR2(30) := NULL, -- Meaning of Resource type code
25 RESOURCE_NAME VARCHAR2(30) := NULL, -- Name of the resource
26 RESOURCE_DESCRIPTION VARCHAR2(240) := NULL, -- Description of the resource
27 CAPACITY_UNITS NUMBER,
28 REQUIRED_UNITS NUMBER := NULL, ------- Required Units of Resource
29 AVAILABLE_UNITS NUMBER := NULL,------ -- Available Units of Resource
30 DEPT_DESCRIPTION VARCHAR2(240) := NULL,
31 PERIOD_STRING VARCHAR2(80) := NULL, -- Display String of Period --new
32 PERIOD_START DATE := NULL, -- Period Start Date/Time -- new
33 PERIOD_END DATE := NULL, -- Period End Date/Time -- new
34 DEPT_ID NUMBER := null
35 );
36
37 -- Record of Resource Requirement and Availability over a Period
38 TYPE Period_Rsrc_Req_Rec_Type IS RECORD (
39 PERIOD_STRING VARCHAR2(80) := NULL, -- Display String of Period
40 PERIOD_START DATE := NULL, -- Period Start Date/Time
41 PERIOD_END DATE := NULL, -- Period End Date/Time
42 RESOURCE_ID NUMBER := NULL, -- ASO resource id or BOM resource id
43 RESOURCE_TYPE NUMBER := NULL,
44 DEPARTMENT_ID NUMBER := NULL,
45 DEPT_DESCRIPTION VARCHAR2(240) := NULL,
46 RESOURCE_TYPE_MEANING VARCHAR2(30) := NULL,
47 RESOURCE_NAME VARCHAR2(30) := NULL,
48 RESOURCE_DESCRIPTION VARCHAR2(240) := NULL,
49 CAPACITY_UNITS NUMBER := NULL,
50 REQUIRED_UNITS NUMBER := NULL, -- Required Units of Resource
51 AVAILABLE_UNITS NUMBER := NULL -- Available Units of Resource
52 );
53
54 -- Record of Plan resources
55 TYPE Task_Requirement_Rec_Type IS RECORD (
56 VISIT_ID NUMBER := NULL, -- Visit Id
57 TASK_ID NUMBER := NULL, -- Task Id
58 VISIT_NAME VARCHAR2(80) := NULL, -- Visit Name
59 VISIT_NUMBER NUMBER := NULL, -- Visit Number
60 VISIT_TASK_NAME VARCHAR2(80) := NULL, -- Task Name
61 TASK_TYPE_CODE VARCHAR2(30) ,
62 DEPT_NAME VARCHAR2(80) ,
63 REQUIRED_UNITS NUMBER := NULL, -- Required Units of Resource
64 AVAILABLE_UNITS NUMBER := NULL, -- Available Units of Resource
65 QUANTITY NUMBER := NULL -- Quantity Required
66 );
67
68 -- Record of Resource details
69 TYPE Task_Resource_Rec_Type IS RECORD (
70 TASK_ID NUMBER := NULL, -- Id of the visit task
71 PER_COMPETENCE_ID NUMBER := NULL, -- Id of the resource's competence
72 PERRATING_LEVEL_ID NUMBER := NULL, -- Id of the Resource's rating level
73 PER_QUALIFICATION_ID NUMBER := NULL, -- Id of the Resource's qualification
74 BOM_RESOURCE_ID NUMBER := NULL, -- The resource's BOM id
75 TIMESPAN NUMBER := NULL -- The resource's duration/timespan
76 );
77
78 -- Record of skillset
79 TYPE Skillset_Rec_Type IS RECORD (
80 PER_COMPETENCE_ID NUMBER := NULL, -- Id of the resource's competence
81 PER_COMPETENCE_NAME VARCHAR2(240) := NULL, -- Name of the competence
82 PERRATING_LEVEL_ID NUMBER := NULL, -- Id of the Resource's rating level
83 PER_RATING_LEVEL_NAME VARCHAR2(80) := NULL, -- Name of the rating level
84 PER_QUALIFICATION_ID NUMBER := NULL, -- Id of the Resource's qualification
85 PER_QUALIFICATION_NAME VARCHAR2(100) := NULL -- Name of the qualification
86 );
87
88
89 ----------------------------------------------
90 -- Define Table Type for records structures --
91 ----------------------------------------------
92 TYPE Visit_Task_Times_Tbl_Type IS TABLE OF Visit_Task_Times_Rec_Type INDEX BY BINARY_INTEGER;
93
94 TYPE Plan_Rsrc_Tbl_Type IS TABLE OF Plan_Rsrc_Rec_Type INDEX BY BINARY_INTEGER;
95
96 TYPE Period_Rsrc_Req_Tbl_Type IS TABLE OF Period_Rsrc_Req_Rec_Type INDEX BY BINARY_INTEGER;
97
98 TYPE Task_Requirement_Tbl_Type IS TABLE OF Task_Requirement_Rec_Type INDEX BY BINARY_INTEGER;
99
100 TYPE Task_Resource_Tbl_Type IS TABLE OF Task_Resource_Rec_Type INDEX BY BINARY_INTEGER;
101
102 TYPE Skillset_Tbl_Type IS TABLE OF Skillset_Rec_Type INDEX BY BINARY_INTEGER;
103
104 ------------------------
105 -- Declare Procedures --
106 ------------------------
107
108 -- Start of Comments --
109 -- Procedure name : Derive_Visit_Task_Times
110 -- Type : Private
111 -- Function : Derive the start and end times/hours of tasks associated with a visit
112 -- Pre-reqs :
113 -- Parameters :
114 --
115 -- Standard IN Parameters :
116 -- p_api_version IN NUMBER Required
117 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
118 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
119 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
120 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
121 -- p_module_type IN VARCHAR2 Default NULL.
122 --
123 -- Standard OUT Parameters :
124 -- x_return_status OUT VARCHAR2 Required
125 -- x_msg_count OUT NUMBER Required
126 -- x_msg_data OUT VARCHAR2 Required
127 --
128 -- Derive_Visit_Task_Times Parameters:
129 -- p_visit_id IN NUMBER Required
130 -- The id of the visit whose associated tasks' start and end times or hours
131 -- need to be derived
132 -- p_start_time IN DATE DEFAULT NULL
133 -- Start time filter for tasks. Only tasks that are in progress at or after this
134 -- time will be considered. Tasks that end before this time will be ignored.
135 -- If null, no filtering will be done
136 -- p_end_time IN DATE DEFAULT NULL
137 -- End time filter for tasks. Only tasks that are in progress at or before this
138 -- time will be considered. Tasks that start after this time will be ignored.
139 -- If null, no filtering will be done
140 -- x_visit_start_time OUT DATE
141 -- The start time of the visit
142 -- x_visit_end_time OUT DATE
143 -- The derived end time of the visit
144 -- x_visit_end_hour OUT NUMBER
145 -- The derived end hour (normalized) of the visit
146 -- x_visit_task_times_tbl OUT AHL_LTP_RESRC_LEVL_PVT.Visit_Task_Times_Tbl_Type
147 -- The table containing details about the tasks associated with this visit
148 --
149 -- Version :
150 -- Initial Version 1.0
151 --
152 -- End of Comments.
153
154 PROCEDURE Derive_Visit_Task_Times
155 (
156 p_api_version IN NUMBER,
157 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
158 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
159 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
160 p_default IN VARCHAR2 := Fnd_Api.G_TRUE,
161 p_module_type IN VARCHAR2 := NULL,
162 p_visit_id IN NUMBER,
163 p_start_time IN DATE := NULL,
164 p_end_time IN DATE := NULL,
165 x_visit_start_time OUT NOCOPY DATE,
166 x_visit_end_time OUT NOCOPY DATE,
167 x_visit_end_hour OUT NOCOPY NUMBER,
168 x_visit_task_times_tbl OUT NOCOPY Ahl_Ltp_Resrc_Levl_Pvt.Visit_Task_Times_Tbl_Type,
169 x_return_status OUT NOCOPY VARCHAR2,
170 x_msg_count OUT NOCOPY NUMBER,
171 x_msg_data OUT NOCOPY VARCHAR2
172 );
173
174 -- Start of Comments --
175 -- Procedure name : Get_Plan_Resources
176 -- Type : Private
177 -- Function : Gets the distinct Resources (Name, Type, Code) required by a given
178 -- department during a given period for a given Plan
179 -- Pre-reqs :
180 -- Parameters :
181 --
182 -- Standard IN Parameters :
183 -- p_api_version IN NUMBER Required
184 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
185 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
186 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
187 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
188 -- p_module_type IN VARCHAR2 Default NULL.
189 --
190 -- Standard OUT Parameters :
191 -- x_return_status OUT VARCHAR2 Required
192 -- x_msg_count OUT NUMBER Required
193 -- x_msg_data OUT VARCHAR2 Required
194 --
195 -- Get_Plan_Resources Parameters:
196 -- p_dept_id IN NUMBER REQUIRED
197 -- The department that is to be searched for resources
198 -- p_dept_name IN VARCHAR2 REQUIRED
199 -- The name of the department (will be mapped to Id if p_dept_id is not given)
200 -- p_org_name IN VARCHAR2 REQUIRED
201 -- The name of the organization where the department is
202 -- p_plan_id IN NUMBER REQUIRED
203 -- The id of the plan for which to get the resources
204 -- p_start_time IN DATE REQUIRED
205 -- Start time filter for tasks. Only tasks that are in progress at or after this
206 -- time will be considered. Tasks that end before this time will be ignored.
207 -- p_end_time IN DATE REQUIRED
208 -- End time filter for tasks. Only tasks that are in progress at or before this
209 -- time will be considered. Tasks that start after this time will be ignored.
210 -- x_plan_rsrc_tbl OUT AHL_LTP_RESRC_LEVL_PVT.Plan_Rsrc_Tbl_Type
211 -- The table containing the distinct resources required.
212 --
213 -- Version :
214 -- Initial Version 1.0
215 --
216 -- End of Comments.
217 PROCEDURE Get_Plan_Resources
218 (
219 p_api_version IN NUMBER,
220 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
221 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
222 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
223 p_default IN VARCHAR2 := Fnd_Api.G_TRUE,
224 p_module_type IN VARCHAR2 := NULL,
225 p_dept_id IN NUMBER,
226 p_dept_name IN VARCHAR2,
227 p_org_name IN VARCHAR2,
228 p_plan_id IN NUMBER,
229 p_start_time IN DATE,
230 p_end_time IN DATE,
231 x_plan_rsrc_tbl OUT NOCOPY Ahl_Ltp_Resrc_Levl_Pvt.Plan_Rsrc_Tbl_Type,
232 x_return_status OUT NOCOPY VARCHAR2,
233 x_msg_count OUT NOCOPY NUMBER,
234 x_msg_data OUT NOCOPY VARCHAR2
235 );
236
237 -- Start of Comments --
238 -- Procedure name : Get_Rsrc_Req_By_Period
239 -- Type : Private
240 -- Function : Gets the Requirements and Availability of a Resource
241 -- by periods for a given department, during a given period,
242 -- for a given Plan
243 -- Pre-reqs :
244 -- Parameters :
245 --
246 -- Standard IN Parameters :
247 -- p_api_version IN NUMBER Required
248 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
249 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
250 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
251 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
252 -- p_module_type IN VARCHAR2 Default NULL.
253 --
254 -- Standard OUT Parameters :
255 -- x_return_status OUT VARCHAR2 Required
256 -- x_msg_count OUT NUMBER Required
257 -- x_msg_data OUT VARCHAR2 Required
258 --
259 -- Get_Plan_Resources Parameters:
260 -- p_dept_id IN NUMBER REQUIRED
261 -- The department that is to be searched for resources
262 -- p_dept_name IN VARCHAR2 REQUIRED
263 -- The name of the department (will be mapped to Id if p_dept_id is not given)
264 -- p_org_name IN VARCHAR2 REQUIRED
265 -- The name of the organization where the department is
266 -- p_plan_id IN NUMBER REQUIRED
267 -- The id of the plan for which to get the resources
268 -- p_start_time IN DATE REQUIRED
269 -- Start time filter for tasks. Only tasks that are in progress at or after this
270 -- time will be considered. Tasks that end before this time will be ignored.
271 -- p_end_time IN DATE REQUIRED
272 -- End time filter for tasks. Only tasks that are in progress before this
273 -- time will be considered. Tasks that start at or after this time will be ignored.
274 -- p_UOM_id IN NUMBER REQUIRED
275 -- The id of the Period's unit of Measure (Days, Weeks, Months etc.)
276 -- p_resource_id IN NUMBER REQUIRED
280 -- x_per_rsrc_tbl OUT AHL_LTP_RESRC_LEVL_PVT.Period_Rsrc_Req_Tbl_Type
277 -- The id of the Resource whose requirements/Availabilities are to be derived
278 -- p_aso_bom_rsrc_type IN VARCHAR2 REQUIRED
279 -- The type of the resource (ASORESOURCE or BOMRESOURCE)
281 -- The table containing the distinct resources required.
282 --
283 -- Version :
284 -- Initial Version 1.0
285 --
286 -- End of Comments.
287 PROCEDURE Get_Rsrc_Req_By_Period
288 (
289 p_api_version IN NUMBER,
290 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
291 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
292 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
293 p_default IN VARCHAR2 := Fnd_Api.G_TRUE,
294 p_module_type IN VARCHAR2 := NULL,
295 p_dept_id IN NUMBER,
296 p_dept_name IN VARCHAR2,
297 p_org_name IN VARCHAR2,
298 p_plan_id IN NUMBER,
299 p_start_time IN DATE,
300 p_end_time IN DATE,
301 p_UOM_code IN VARCHAR2,
302 p_required_capacity IN NUMBER,
303 x_per_rsrc_tbl OUT NOCOPY Ahl_Ltp_Resrc_Levl_Pvt.Period_Rsrc_Req_Tbl_Type,
304 x_return_status OUT NOCOPY VARCHAR2,
305 x_msg_count OUT NOCOPY NUMBER,
306 x_msg_data OUT NOCOPY VARCHAR2
307 );
308
309 -- Start of Comments --
310 -- Procedure name : Get_Task_Requirements
311 -- Type : Private
312 -- Function : Gets the Requirements of a Resource by Visit/Task
313 -- for a given department, during a given period,
314 -- for a given Plan
315 -- Pre-reqs :
316 -- Parameters :
317 --
318 -- Standard IN Parameters :
319 -- p_api_version IN NUMBER Required
320 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
321 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
322 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
323 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
324 -- p_module_type IN VARCHAR2 Default NULL.
325 --
326 -- Standard OUT Parameters :
327 -- x_return_status OUT VARCHAR2 Required
328 -- x_msg_count OUT NUMBER Required
329 -- x_msg_data OUT VARCHAR2 Required
330 --
331 -- Get_Plan_Resources Parameters:
332 -- p_dept_id IN NUMBER REQUIRED
333 -- The department that is to be searched for resources
334 -- p_dept_name IN VARCHAR2 REQUIRED
335 -- The name of the department (will be mapped to Id if p_dept_id is not given)
336 -- p_org_name IN VARCHAR2 REQUIRED
337 -- The name of the organization where the department is
338 -- p_plan_id IN NUMBER REQUIRED
339 -- The id of the plan for which to get the resources
340 -- p_start_time IN DATE REQUIRED
341 -- Start time filter for tasks. Only tasks that are in progress at or after this
342 -- time will be considered. Tasks that end before this time will be ignored.
343 -- p_end_time IN DATE REQUIRED
344 -- End time filter for tasks. Only tasks that are in progress before this
345 -- time will be considered. Tasks that start at or after this time will be ignored.
346 -- p_resource_id IN NUMBER REQUIRED
347 -- The id of the Resource whose requirements/Availabilities are to be derived
348 -- p_aso_bom_rsrc_type IN VARCHAR2 REQUIRED
349 -- The type of the resource (ASORESOURCE or BOMRESOURCE)
350 -- x_task_req_tbl OUT AHL_LTP_RESRC_LEVL_PVT.Task_Requirement_Tbl_Type
351 -- The table containing the resource requirements.
352 --
353 -- Version :
354 -- Initial Version 1.0
355 --
356 -- End of Comments.
357 PROCEDURE Get_Task_Requirements
358 (
359 p_api_version IN NUMBER,
360 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
361 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
362 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
363 p_default IN VARCHAR2 := Fnd_Api.G_TRUE,
364 p_module_type IN VARCHAR2 := NULL,
365 p_dept_id IN NUMBER,
366 p_dept_name IN VARCHAR2,
367 p_org_name IN VARCHAR2,
368 p_plan_id IN NUMBER,
369 p_start_time IN DATE,
370 p_end_time IN DATE,
371 p_dstart_time IN DATE,
372 p_dend_time IN DATE,
373 p_resource_id IN NUMBER,
374 p_aso_bom_rsrc_type IN VARCHAR2,
375 x_task_req_tbl OUT NOCOPY Ahl_Ltp_Resrc_Levl_Pvt.Task_Requirement_Tbl_Type,
376 x_return_status OUT NOCOPY VARCHAR2,
377 x_msg_count OUT NOCOPY NUMBER,
378 x_msg_data OUT NOCOPY VARCHAR2
379 );
380
381 /*
382 --//@@@@@ TO BE REMOVED
383 PROCEDURE Dump_Working_dates_tbl
384 (
385 p_start_date IN DATE := TO_DATE('15-May-2002'),
386 p_dept_id IN NUMBER := 1,
387 p_num_days IN NUMBER := 100
388 );
389 */
390
391 END Ahl_Ltp_Resrc_Levl_Pvt;
[Home] [Help]