1 PACKAGE BODY AHL_LTP_RESRC_LEVL_PVT AS
2 /* $Header: AHLVRLGB.pls 120.8 2008/02/25 11:35:34 rnahata ship $ */
3 --
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AHL_LTP_RESRC_LEVL_PVT';
5 G_OPER_ASSOC_TYPE CONSTANT VARCHAR2(30) := 'OPERATION';
6 G_RT_ASSOC_TYPE CONSTANT VARCHAR2(30) := 'ROUTE';
7 G_SECS_IN_DAY CONSTANT NUMBER := 86400;
8 G_SECS_IN_HOUR CONSTANT NUMBER := 3600;
9 G_HOLIDAY_TYPE CONSTANT NUMBER := 2;
10
11 -- yazhou 17Aug2005 starts
12 -- bug fix #4559462
13 /*
14 G_MACHINE_RESOURCE CONSTANT NUMBER := 1;
15 G_MACHINE_RES_NAME CONSTANT VARCHAR2(10) := 'Machine';
16 G_LABOR_RESOURCE CONSTANT NUMBER := 2;
17 G_LABOR_RES_NAME CONSTANT VARCHAR2(10) := 'Labor';
18 */
19 -- yazhou 17Aug2005 ends
20
21 G_JSP_MODULE_TYPE CONSTANT VARCHAR2(3) := 'JSP';
22 G_ASO_RESOURCE CONSTANT VARCHAR2(20) := 'ASORESOURCE';
23 G_BOM_RESOURCE CONSTANT VARCHAR2(20) := 'BOMRESOURCE';
24 G_STATUS_PLANNING CONSTANT VARCHAR2(20) := 'PLANNING';
25 G_PLAN_TYPE_PRIMARY CONSTANT VARCHAR2(1) := 'Y';
26 G_UOM_HOUR CONSTANT VARCHAR2(3) := 'HR';
27 G_UOM_DAY CONSTANT VARCHAR2(10) := 'DAYS';
28 G_UOM_WEEK CONSTANT VARCHAR2(10) := 'WEEKS';
29 G_UOM_MONTH CONSTANT VARCHAR2(10) := 'MONTHS';
30
31
32 -- Package level Global Variables used in time calculations
33 G_ZERO_TIME DATE := NULL;
34 TYPE Dates_Tbl_Type IS TABLE OF DATE INDEX BY BINARY_INTEGER;
35 G_WORKING_DATES_TBL Dates_Tbl_Type;
36 G_EXCEPTION_DATES_TBL Dates_Tbl_Type;
37 G_CALENDAR_CODE VARCHAR2(10) := NULL;
38 G_SHIFT_NUM NUMBER := NULL;
39 G_SHIFT_START NUMBER := NULL;
40 G_SHIFT_END NUMBER := NULL;
41 G_DAYS_ON NUMBER := NULL;
42 G_DAYS_OFF NUMBER := NULL;
43 G_CAL_START DATE := NULL;
44 G_CAL_END DATE := NULL;
45 G_MAX_CAL_DAY NUMBER := -1;
46 G_SHIFT_DURATION_HRS NUMBER := 0;
47
48 -- yazhou 24Aug2005 starts
49 -- Resource Leveling Re-design
50
51 l_log_current_level NUMBER := fnd_log.g_current_runtime_level;
52 l_log_statement NUMBER := fnd_log.level_statement;
53 l_log_procedure NUMBER := fnd_log.level_procedure;
54 l_log_error NUMBER := fnd_log.level_error;
55 l_log_unexpected NUMBER := fnd_log.level_unexpected;
56
57 -- Record of Operation Details
58 TYPE Time_Period_Details_Rec_Type IS RECORD (
59 VISIT_ID NUMBER := NULL, -- Visit Id
60 TASK_ID NUMBER := NULL, -- Task Id
61 ROUTE_ID NUMBER := NULL, -- Route Id
62 MR_ROUTE_ID NUMBER := NULL, --ADDED ENAHNCEMENTS
63 OPERATION_ID NUMBER := NULL, -- Operation Id (Null for Route Level)
64 STEP NUMBER := NULL, -- Operation sequence within Task
65 START_HOUR NUMBER := NULL, -- Start Hour of the task/operation
66 END_HOUR NUMBER := NULL, -- End Hour of the task/operation
67 MAX_DURATION NUMBER := NULL, -- Max duration required for this task/op.
68 START_TIME DATE := NULL, -- Start time of task/operation
69 END_TIME DATE := NULL, -- End time of task/operation
70 QUANTITY NUMBER := NULL, -- Quantity of a resource
71 TASK_TYPE_CODE VARCHAR2(30) ,
72 REQUIRED_UNITS NUMBER := NULL,
73 AVAILABLE_UNITS NUMBER := NULL,
74 ASO_RESOURCE_ID NUMBER := NULL,
75 DEPARTMENT_ID NUMBER := NULL,
76 BOM_RESOURCE_ID NUMBER := NULL
77 );
78
79 TYPE Resource_Tbl_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
80
81 -- yazhou 24Aug2005 ends
82
83 -- Record of event and quantity changes
84 TYPE Qty_Change_Rec_Type IS RECORD (
85 EVENT_TIME DATE,
86 QTY_CHANGE NUMBER
87 );
88
89 TYPE Time_Period_Details_Tbl_Type IS TABLE OF Time_Period_Details_Rec_Type INDEX BY BINARY_INTEGER;
90
91 TYPE Qty_Change_Tbl_Type IS TABLE OF Qty_Change_Rec_Type INDEX BY BINARY_INTEGER;
92
93 TYPE Person_Tbl_Type IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
94
95
96
97 -------------------------------
98 -- Declare Local Procedures --
99 -------------------------------
100 -- yazhou 24Aug2005 starts
101 -- Resource Leveling Re-design
102
103 -- Gets all the tasks and their times for all visits for a given plan and department during a given period
104 -- If current plan is a simulation and the p_inc_primary flag is 'Y', visits in primary plan are also included.
105 PROCEDURE Get_Plan_Tasks(
106 p_dept_id IN NUMBER,
107 p_plan_id IN NUMBER,
108 p_start_time IN DATE,
109 p_end_time IN DATE,
110 p_inc_primary_flag IN BOOLEAN := FALSE,
111 x_tp_dtls_tbl OUT NOCOPY Time_Period_Details_Tbl_Type,
112 x_visit_task_times_tbl OUT NOCOPY Visit_Task_Times_Tbl_Type);
113
114 -- Gets the task and operation times for a given visit. Also builds the
115 -- Time Period table if required
116 PROCEDURE Derive_Task_Op_Times(
117 p_visit_id IN NUMBER,
118 p_start_time IN DATE,
119 p_end_time IN DATE,
120 p_department_id IN NUMBER,
121 p_x_tp_dtls_tbl IN OUT NOCOPY Time_Period_Details_Tbl_Type,
122 x_visit_start_time OUT NOCOPY DATE,
123 x_visit_end_time OUT NOCOPY DATE,
124 x_visit_end_hour OUT NOCOPY NUMBER,
125 x_visit_task_times_tbl OUT NOCOPY Visit_Task_Times_Tbl_Type);
126
127 -- yazhou 24Aug2005 ends
128
129 -- Gets the resources required by the given task
130 PROCEDURE Get_Task_Resources(
131 p_required_capacity IN NUMBER ,
132 p_task_id IN NUMBER,
133 p_tstart_date IN DATE,
134 p_tend_date IN DATE,
135 p_distinct_flag IN BOOLEAN, -- If true, duplicate resource are not added to table
136 p_x_task_rsrc_tbl IN OUT NOCOPY Plan_Rsrc_Tbl_Type);
137
138 -- Gets the details of a resource
139 PROCEDURE Get_Resource_Details(
140 p_aso_resource_id IN NUMBER,
141 p_x_task_rsrc_tbl IN OUT NOCOPY Plan_Rsrc_Tbl_Type);
142 -- Gets the details of a recource and capacity units
143 PROCEDURE Get_Resource_Details
144 (
145 p_required_capacity IN NUMBER,
146 p_aso_resource_id IN NUMBER,
147 p_bom_resource_id IN NUMBER,
148 p_bom_department_id IN NUMBER,
149 p_start_date IN DATE,
150 p_end_date IN DATE,
151 p_x_task_rsrc_tbl IN OUT NOCOPY Plan_Rsrc_Tbl_Type);
152
153 -- yazhou 24Aug2005 starts
154 -- Resource Leveling Re-design
155
156 -- Populates a Time Period table with a task's requirement' start time and end time
157 -- (If the task's route has operations and if ressources are not associated at route level)
158 -- or with the task's start time and end time
159 -- Also populate the table with resource id
160
161 PROCEDURE Get_Operation_Details(
162 p_task_dtls IN Visit_Task_Times_Rec_Type,
163 p_visit_id IN NUMBER,
164 p_department_id IN NUMBER,
165 p_organization_id IN NUMBER,
166 p_start_time IN DATE,
167 p_end_time IN DATE,
168 p_x_tp_dtls_tbl IN OUT NOCOPY Time_Period_Details_Tbl_Type);
169
170 -- yazhou 24Aug2005 ends
171
172 -- Filters out tasks/operations that don't require the resource
173 -- and gets quantity of resource required by the task/operation
174 PROCEDURE Filter_By_Resource(
175 p_resource_id IN NUMBER,
176 p_start_time IN DATE,
177 p_end_time IN DATE,
178 p_x_tp_dtls_tbl IN OUT NOCOPY Time_Period_Details_Tbl_Type);
179 --
180 /* Commented by rnahata since its not being used
181 PROCEDURE get_qty_By_Resource(
182 p_resource_id IN NUMBER,
183 p_aso_bom_type IN VARCHAR2,
184 p_x_tp_dtls_tbl IN OUT NOCOPY Time_Period_Details_Tbl_Type);
185 */
186
187 -- Creates Timeperiods from the start time and end time based on the UOM
188 -- and populates the table
189 PROCEDURE Create_Time_Periods(
190 p_start_time IN DATE,
191 p_end_time IN DATE,
192 p_UOM_code IN VARCHAR2,
193 p_org_id IN NUMBER,
194 p_dept_id IN NUMBER,
195 x_per_rsrc_tbl OUT NOCOPY Period_Rsrc_Req_Tbl_Type);
196
197 -- Sorts the Quantity Change table based on event time
198 PROCEDURE Sort_Qty_Change_Table(
199 p_x_qty_change_tbl IN OUT NOCOPY Qty_Change_Tbl_Type);
200
201 -- Sorts the Time_Period_Details_Tbl By Visit/Task
202 PROCEDURE Sort_By_Visit_Task(
203 p_x_tp_tbl IN OUT NOCOPY Time_Period_Details_Tbl_Type);
204
205 -- Aggregates Task Quantities and gets Task and Visit Names
206 PROCEDURE Aggregate_Task_Quantities(
207 P_resource_id IN NUMBER,
208 p_org_name IN VARCHAR2,
209 p_dept_name IN VARCHAR2,
210 p_tp_dtls_table IN Time_Period_Details_Tbl_Type,
211 x_task_req_tbl OUT NOCOPY Task_Requirement_Tbl_Type);
212
213 -- Gets the duration of a Route from its timespan column
214 PROCEDURE Get_Route_Duration(
215 p_route_id IN NUMBER,
216 x_duration OUT NOCOPY NUMBER);
217
218 -- Gets the duration of a Route from its resource requirements (directly or through the route's operations)
219 PROCEDURE Get_Rt_Ops_Duration(
220 p_route_id IN NUMBER,
221 x_duration OUT NOCOPY NUMBER);
222
223 -- Gets the duration of an operation from its resource requirements
224 PROCEDURE Get_Oper_Max_Duration(
225 p_operation_id IN NUMBER,
226 x_duration OUT NOCOPY NUMBER);
227
228 -- Gets the duration of a route directly from its resource requirements
229 PROCEDURE Get_Rt_Max_Duration(
230 p_route_id IN NUMBER,
231 x_duration OUT NOCOPY NUMBER);
232
233 -- Initializes the calendar based variables
234 PROCEDURE Init_Time_Vars(
235 p_visit_start_date IN DATE,
236 p_department_id IN NUMBER);
237 -- Gets the Nth Working Day
238 FUNCTION Get_Nth_Day(p_day_index NUMBER) RETURN DATE;
239
240 -- Determines if a specific date is a holiday
241 FUNCTION IS_DEPT_Holiday(l_curr_date DATE) RETURN BOOLEAN;
242
243 -- Determines if a specific resource is available at a specific time
244 FUNCTION Resource_In_Duty(p_resource_id NUMBER, p_start_date_time DATE) RETURN BOOLEAN;
245
246 -- Determines if the Resource is already present in the given table
247 FUNCTION Is_Resource_Present(p_aso_resource_id NUMBER,
248 p_task_rsrc_tbl Plan_Rsrc_Tbl_Type) RETURN BOOLEAN;
249 -- Function to determine if two time periods overlap
250 -- Note that If the end time of one period coincides with the
251 -- start time of the other time period, the timeperiods DON'T overlap
252 FUNCTION Periods_Overlap(p1_start_time DATE,
253 p1_end_time DATE,
254 p2_start_time DATE,
255 p2_end_time DATE) RETURN BOOLEAN;
256
257 -- Calculates the maximum required quantity during a given period
258 FUNCTION Get_Required_Quantity(p_start_time DATE,
259 p_end_time DATE,
260 p_tp_dtls_tbl Time_Period_Details_Tbl_Type) RETURN NUMBER;
261 -- Compares a visit/task combination with another
262 -- Returns +1, -1 or 0 depending on whether first visit task is greater, lesser
263 -- or equal to the second visit task
264 FUNCTION Compare_Visit_Tasks(p_visit_1 NUMBER,
265 p_task_1 NUMBER,
266 p_visit_2 NUMBER,
267 p_task_2 NUMBER) RETURN NUMBER;
268
269 -- Function to determine if a specific date is not a holiday in the given department
270 FUNCTION Not_A_Holiday(p_curr_date DATE, p_dept_id NUMBER) RETURN BOOLEAN;
271 -- Get wip job requirements
272 -- AnRaj: Obsoleted Procedure
273 /*
274 PROCEDURE GET_WIP_DISC_REQ_UNITS(p_org_id IN NUMBER,
275 p_bom_dept_id IN NUMBER,
276 p_bom_resource_id IN NUMBER,
277 p_start_date IN DATE,
278 p_end_date IN DATE,
279 x_assigned_units OUT NOCOPY NUMBER);
280 */
281 -- yazhou 24Aug2005 starts
282 -- Resource Leveling Re-design
283
284 FUNCTION Get_Available_Units(p_resource_id NUMBER,
285 p_dept_id NUMBER) RETURN NUMBER;
286
287
288 PROCEDURE Get_Used_Resources
289 (
290 p_dept_id IN NUMBER,
291 p_start_date IN DATE,
292 p_end_date IN DATE,
293 p_tp_dtls_tbl IN Time_Period_Details_Tbl_Type,
294 x_resources_tbl OUT NOCOPY Resource_Tbl_Type
295 );
296
297 PROCEDURE Append_WIP_Requirements
298 (
299 p_org_id IN NUMBER,
300 p_dept_id IN NUMBER,
301 p_start_date IN DATE,
302 p_end_date IN DATE,
303 p_resource_id IN NUMBER,
304 p_x_tp_dtls_tbl IN OUT NOCOPY Time_Period_Details_Tbl_Type
305 );
306 -- yazhou 24Aug2005 ends
307
308 -------------------------------------
309 -- End Local Procedures Declaration--
310 -------------------------------------
311
312 -- Start of Comments --
313 -- Procedure name : Derive_Visit_Task_Times
314 -- Type : Private
315 -- Function : Derive the start and end times/hours of task associated with a visit
316 -- Pre-reqs :
317 -- Parameters :
318 --
319 -- Standard IN Parameters :
320 -- p_api_version IN NUMBER Required
321 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
322 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
323 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
324 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
325 -- p_module_type IN VARCHAR2 Default NULL.
326 --
327 -- Standard OUT Parameters :
328 -- x_return_status OUT VARCHAR2 Required
329 -- x_msg_count OUT NUMBER Required
330 -- x_msg_data OUT VARCHAR2 Required
331 --
332 -- Derive_Visit_Task_Times Parameters:
333 -- p_visit_id IN NUMBER Required
334 -- The id of the visit whose associated tasks' start and end times or hours
335 -- need to be derived
336 -- p_start_time IN DATE DEFAULT NULL
337 -- Start time filter for tasks. Only tasks that start after this time will be returned.
338 -- If null, no filtering will be done
339 -- p_end_time IN DATE DEFAULT NULL
340 -- End time filter for tasks. Only tasks that start before this time will be returned.
341 -- If null, no filtering will be done
342 -- x_visit_start_time OUT DATE
343 -- The start time of the visit
344 -- x_visit_end_time OUT DATE
345 -- The derived end time of the visit
346 -- x_visit_end_hour OUT NUMBER
347 -- The derived end hour (normalized) of the visit
348 -- x_visit_task_times_tbl OUT Visit_Task_Times_Tbl_Type
349 -- The table containing details about the tasks associated with this visit
350 --
351 -- Version :
352 -- Initial Version 1.0
353 --
354 -- End of Comments.
355
356 --
357 -- This API has been deprecated -yazhou 24Aug2005
358 --
359
360 PROCEDURE Derive_Visit_Task_Times
361 (
362 p_api_version IN NUMBER,
363 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
364 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
365 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
366 p_default IN VARCHAR2 := Fnd_Api.G_TRUE,
367 p_module_type IN VARCHAR2 := NULL,
368 p_visit_id IN NUMBER,
369 p_start_time IN DATE := NULL,
370 p_end_time IN DATE := NULL,
371 x_visit_start_time OUT NOCOPY DATE,
372 x_visit_end_time OUT NOCOPY DATE,
373 x_visit_end_hour OUT NOCOPY NUMBER,
374 x_visit_task_times_tbl OUT NOCOPY AHL_LTP_RESRC_LEVL_PVT.Visit_Task_Times_Tbl_Type,
375 x_return_status OUT NOCOPY VARCHAR2,
376 x_msg_count OUT NOCOPY NUMBER,
377 x_msg_data OUT NOCOPY VARCHAR2
378 ) IS
379
380 CURSOR l_validate_visit_csr(p_visit_id IN NUMBER) IS
381 SELECT 'x' FROM ahl_visits_b
382 WHERE VISIT_ID = p_visit_id;
383
384 l_api_version CONSTANT NUMBER := 1.0;
385 l_api_name CONSTANT VARCHAR2(30) := 'Derive_Visit_Task_Times';
386 l_dummy_table Time_Period_Details_Tbl_Type;
387 l_junk VARCHAR2(1);
388
389 BEGIN
390 -- dbms_output.put_line('Entering Derive_Visit_Task_Times');
391 -- Standard start of API savepoint
392 SAVEPOINT Derive_Visit_Task_Times_pvt;
393
394 -- Standard call to check for call compatibility
395 IF NOT Fnd_Api.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
396 G_PKG_NAME) THEN
397 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
398 END IF;
399
400 -- Initialize message list if p_init_msg_list is set to TRUE
401 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
402 Fnd_Msg_Pub.Initialize;
403 END IF;
404
405 -- Initialize API return status to success
406 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
407
408 -- Begin Processing
409
410 -- Enable Debug (optional)
411 Ahl_Debug_Pub.enable_debug;
412
413 -- ASSUMPTIONS:
414 -- A Task can appear only once for a given visit (ahl_visit_tasks_b)
415 -- A Department can have only one shift (ahl_department_shifts)
416 -- Shift_Num is unique in bom_shift_times
417
418 -- Validate Visit Id
419 IF (p_visit_id IS NULL) THEN
420 Fnd_Message.Set_Name('AHL','AHL_LTP_VISIT_ID_NULL');
421 Fnd_Msg_Pub.ADD;
422 RAISE Fnd_Api.G_EXC_ERROR;
423 ELSE
424 OPEN l_validate_visit_csr(p_visit_id);
425 FETCH l_validate_visit_csr INTO l_junk;
426 IF (l_validate_visit_csr%NOTFOUND) THEN
427 Fnd_Message.Set_Name('AHL','AHL_LTP_VISIT_ID_INVALID');
428 Fnd_Message.Set_Token('VISIT_ID', p_visit_id);
429 Fnd_Msg_Pub.ADD;
430 CLOSE l_validate_visit_csr;
431 RAISE Fnd_Api.G_EXC_ERROR;
432 END IF;
433 CLOSE l_validate_visit_csr;
434 END IF;
435
436 -- Validate Dates
437 IF (p_start_time IS NOT NULL AND p_end_time IS NOT NULL AND p_start_time > p_end_time) THEN
438 Fnd_Message.Set_Name('AHL','AHL_LTP_STIME_GT_ETIME');
439 Fnd_Message.Set_Token('STIME', p_start_time);
440 Fnd_Message.Set_Token('ETIME', p_end_time);
441 Fnd_Msg_Pub.ADD;
442 RAISE Fnd_Api.G_EXC_ERROR;
443 END IF;
444
445 -- Call helper method to do the actual processing
446 Ahl_Debug_Pub.debug('Beginning Processing for Visit: ' || p_visit_id, 'LTP:Derive_Visit_Task_Times');
447 Derive_Task_Op_Times(
448 p_visit_id => p_visit_id,
449 p_start_time => p_start_time,
450 p_end_time => p_end_time,
451 p_department_id => NULL, -- dummy for compliation purpose
452 p_x_tp_dtls_tbl => l_dummy_table, -- Dummy Table for Operation Time Periods
453 x_visit_start_time => x_visit_start_time,
454 x_visit_end_time => x_visit_end_time,
455 x_visit_end_hour => x_visit_end_hour,
456 x_visit_task_times_tbl => x_visit_task_times_tbl);
457
458 Ahl_Debug_Pub.debug('Completed Processing. Checking for errors', 'LTP:Derive_Visit_Task_Times');
459 -- Check Error Message stack.
460 x_msg_count := Fnd_Msg_Pub.count_msg;
461 IF x_msg_count > 0 THEN
462 RAISE Fnd_Api.G_EXC_ERROR;
463 END IF;
464
465 -- Standard check of p_commit
466 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
467 COMMIT WORK;
468 END IF;
469
470 -- Standard call to get message count and if count is 1, get message info
471 Fnd_Msg_Pub.Count_And_Get
472 ( p_count => x_msg_count,
473 p_data => x_msg_data,
474 p_encoded => Fnd_Api.g_false
475 );
476
477 -- Disable debug (if enabled)
478 Ahl_Debug_Pub.disable_debug;
479 -- dbms_output.put_line('Exiting LTP:Derive_Visit_Task_Times');
480
481 EXCEPTION
482 WHEN Fnd_Api.G_EXC_ERROR THEN
483 ROLLBACK TO Derive_Visit_Task_Times_pvt;
484 x_return_status := Fnd_Api.G_RET_STS_ERROR;
485 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
486 p_data => x_msg_data,
487 p_encoded => Fnd_Api.g_false);
488 --AHL_UTIL_PKG.Err_Mesg_To_Table(x_err_mesg_tbl);
489
490
491 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
492 ROLLBACK TO Derive_Visit_Task_Times_pvt;
493 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
494 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
495 p_data => x_msg_data,
496 p_encoded => Fnd_Api.g_false);
497 --AHL_UTIL_PKG.Err_Mesg_To_Table(x_err_mesg_tbl);
498
499 WHEN OTHERS THEN
500 ROLLBACK TO Derive_Visit_Task_Times_pvt;
501 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
502 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
503 Fnd_Msg_Pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
504 p_procedure_name => 'Derive_Visit_Task_Times',
505 p_error_text => SUBSTR(SQLERRM,1,240));
506 END IF;
507 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
508 p_data => x_msg_data,
509 p_encoded => Fnd_Api.g_false);
510 --AHL_UTIL_PKG.Err_Mesg_To_Table(x_err_mesg_tbl);
511
512 END Derive_Visit_Task_Times;
513
514 -- Start of Comments --
515 -- Procedure name : Get_Plan_Resources
516 -- Type : Private
517 -- Function : Gets the distinct Resources (Name, Type, Code) required by a given
518 -- department during a given period for a given Plan
519 -- Pre-reqs :
520 -- Parameters :
521 --
522 -- Standard IN Parameters :
523 -- p_api_version IN NUMBER Required
524 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
525 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
526 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
527 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
528 -- p_module_type IN VARCHAR2 Default NULL.
529 --
530 -- Standard OUT Parameters :
531 -- x_return_status OUT VARCHAR2 Required
532 -- x_msg_count OUT NUMBER Required
533 -- x_msg_data OUT VARCHAR2 Required
534 --
535 -- Get_Plan_Resources Parameters:
536 -- p_dept_id IN NUMBER REQUIRED
537 -- The department that is to be searched for resources
538 -- p_dept_name IN VARCHAR2 REQUIRED
539 -- The name of the department (will be mapped to Id if p_dept_id is not given)
540 -- p_org_name IN VARCHAR2 REQUIRED
541 -- The name of the organization where the department is
542 -- p_plan_id IN NUMBER REQUIRED
543 -- The id of the plan for which to get the resources
544 -- p_start_time IN DATE REQUIRED
545 -- Start time filter for tasks. Only tasks that are in progress at or after this
546 -- time will be considered. Tasks that end before this time will be ignored.
547 -- p_end_time IN DATE REQUIRED
548 -- End time filter for tasks. Only tasks that are in progress at or before this
549 -- time will be considered. Tasks that start after this time will be ignored.
550 -- x_plan_rsrc_tbl OUT Plan_Rsrc_Tbl_Type
551 -- The table containing the distinct resources required.
552 --
553 -- Version :
554 -- Initial Version 1.0
555 --
556 -- End of Comments.
557 --
558 -- This API has been deprecated -yazhou 24Aug2005
559 --
560
561 PROCEDURE Get_Plan_Resources
562 (
563 p_api_version IN NUMBER,
564 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
565 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
566 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
567 p_default IN VARCHAR2 := Fnd_Api.G_TRUE,
568 p_module_type IN VARCHAR2 := NULL,
569 p_dept_id IN NUMBER,
570 p_dept_name IN VARCHAR2,
571 p_org_name IN VARCHAR2,
572 p_plan_id IN NUMBER,
573 p_start_time IN DATE,
574 p_end_time IN DATE,
575 x_plan_rsrc_tbl OUT NOCOPY AHL_LTP_RESRC_LEVL_PVT.Plan_Rsrc_Tbl_Type,
576 x_return_status OUT NOCOPY VARCHAR2,
577 x_msg_count OUT NOCOPY NUMBER,
578 x_msg_data OUT NOCOPY VARCHAR2
579 ) IS
580
581 CURSOR l_dept_id_csr(p_dept_name IN VARCHAR2, p_org_name IN VARCHAR2) IS
582 SELECT department_id FROM bom_departments dept, hr_all_organization_units org
583 WHERE org.name = p_org_name AND
584 dept.ORGANIZATION_ID = org.ORGANIZATION_ID AND
585 dept.description = p_dept_name;
586
587 CURSOR l_validate_dept_csr(l_dept_id IN NUMBER) IS
588 SELECT 'x' FROM bom_departments WHERE
589 department_id = l_dept_id;
590
591 CURSOR l_validate_plan_csr(l_plan_id IN NUMBER) IS
592 SELECT 'x' FROM ahl_simulation_plans_b WHERE
593 simulation_plan_id = l_plan_id;
594
595 l_api_version CONSTANT NUMBER := 1.0;
596 l_api_name CONSTANT VARCHAR2(30) := 'Get_Plan_Resources';
597
598 l_dept_id NUMBER := NULL;
599 l_junk VARCHAR2(1);
600 l_task_times_tbl Visit_Task_Times_Tbl_Type;
601 l_temp_rsrc_tbl Plan_Rsrc_Tbl_Type;
602 l_dummy_table Time_Period_Details_Tbl_Type;
603 l_required_capacity NUMBER;
604 BEGIN
605 -- dbms_output.put_line('Entering Get_Plan_Resources');
606 -- Standard start of API savepoint
607 SAVEPOINT Get_Plan_Resources_pvt;
608
609 -- Standard call to check for call compatibility
610 IF NOT Fnd_Api.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
611 G_PKG_NAME) THEN
612 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
613 END IF;
614
615 -- Initialize message list if p_init_msg_list is set to TRUE
616 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
617 Fnd_Msg_Pub.Initialize;
618 END IF;
619
620 -- Initialize API return status to success
621 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
622
623 -- Enable Debug (optional)
624 Ahl_Debug_Pub.enable_debug;
625
626 -- Begin Processing
627 -- dbms_output.put_line('Begin Processing');
628 -- Map Dept Name To Dept Id
629 IF (p_module_type = G_JSP_MODULE_TYPE) THEN
630 -- dbms_output.put_line('JSP Module: Doing Value to ID Conversion');
631 OPEN l_dept_id_csr(p_dept_name, p_org_name);
632 FETCH l_dept_id_csr INTO l_dept_id;
633 IF (l_dept_id_csr%NOTFOUND) THEN
634 Fnd_Message.Set_Name('AHL','AHL_LTP_DEPT_INVALID');
635 Fnd_Message.Set_Token('DEPT', p_dept_name);
636 Fnd_Msg_Pub.ADD;
637 CLOSE l_dept_id_csr;
638 RAISE Fnd_Api.G_EXC_ERROR;
639 END IF;
640 CLOSE l_dept_id_csr;
641 -- dbms_output.put_line('l_dept_id = ' || l_dept_id);
642 ELSE
643 -- Validate Dept Id
644 IF (p_dept_id IS NULL) THEN
645 Fnd_Message.Set_Name('AHL','AHL_LTP_DEPT_NULL');
646 Fnd_Msg_Pub.ADD;
647 RAISE Fnd_Api.G_EXC_ERROR;
648 ELSE
649 OPEN l_validate_dept_csr(p_dept_id);
650 FETCH l_validate_dept_csr INTO l_junk;
651 IF (l_validate_dept_csr%NOTFOUND) THEN
652 Fnd_Message.Set_Name('AHL','AHL_LTP_DEPT_ID_INVALID');
653 Fnd_Message.Set_Token('DEPT', p_dept_id);
654 Fnd_Msg_Pub.ADD;
655 CLOSE l_validate_dept_csr;
656 RAISE Fnd_Api.G_EXC_ERROR;
657 END IF;
658 CLOSE l_validate_dept_csr;
659 END IF;
660 l_dept_id := p_dept_id;
661 -- dbms_output.put_line('l_dept_id = ' || l_dept_id);
662 END IF;
663
664 -- Validate Plan Id
665 IF (p_plan_id IS NULL) THEN
666 Fnd_Message.Set_Name('AHL','AHL_LTP_PLAN_ID_NULL');
667 Fnd_Msg_Pub.ADD;
668 RAISE Fnd_Api.G_EXC_ERROR;
669 ELSE
670 OPEN l_validate_plan_csr(p_plan_id);
671 FETCH l_validate_plan_csr INTO l_junk;
672 IF (l_validate_plan_csr%NOTFOUND) THEN
673 Fnd_Message.Set_Name('AHL','AHL_LTP_PLAN_ID_INVALID');
674 Fnd_Message.Set_Token('PLAN', p_plan_id);
675 Fnd_Msg_Pub.ADD;
676 CLOSE l_validate_plan_csr;
677 RAISE Fnd_Api.G_EXC_ERROR;
678 END IF;
679 CLOSE l_validate_plan_csr;
680 END IF;
681 -- dbms_output.put_line('p_plan_id = ' || p_plan_id);
682
683 -- Validate Dates
684 IF (p_start_time IS NOT NULL AND p_end_time IS NOT NULL AND p_start_time > p_end_time) THEN
685 Fnd_Message.Set_Name('AHL','AHL_LTP_STIME_GT_ETIME');
686 Fnd_Message.Set_Token('STIME', p_start_time);
687 Fnd_Message.Set_Token('ETIME', p_end_time);
688 Fnd_Msg_Pub.ADD;
689 RAISE Fnd_Api.G_EXC_ERROR;
690 END IF;
691
692 -- dbms_output.put_line('Completed Mapping. Calling Get_Plan_Tasks');
693 -- Get all the tasks for the given dept/plan
694 -- that are in progress during the given timeframe
695 Get_Plan_Tasks(
696 p_dept_id => p_dept_id,
697 p_plan_id => p_plan_id,
698 p_start_time => p_start_time,
699 p_end_time => p_end_time,
700 p_inc_primary_flag => FALSE, -- No need to include primary plan tasks even if this plan is a simulation
701 -- p_op_times_flag => FALSE, -- No need to get Operation Time Period details
702 x_tp_dtls_tbl => l_dummy_table, -- Dummy Table for Operation Time Period details
703 x_visit_task_times_tbl => l_task_times_tbl);
704
705 -- dbms_output.put_line('Completed Get_Plan_Tasks. Calling Get_Task_Resources');
706 -- dbms_output.put_line('l_task_times_tbl.Count = ' || l_task_times_tbl.COUNT);
707 -- Get Distinct Resources for all valid visits/tasks
708 IF (l_task_times_tbl.COUNT > 0) THEN
709 FOR i IN l_task_times_tbl.FIRST .. l_task_times_tbl.LAST LOOP
710 Get_Task_Resources(
711 p_required_capacity => l_required_capacity,
712 p_task_id => l_task_times_tbl(i).VISIT_TASK_ID,
713 p_tstart_date => 'SYSDATE',
714 p_tend_date => 'SYSDATE',
715 p_distinct_flag => TRUE,
716 p_x_task_rsrc_tbl => l_temp_rsrc_tbl);
717 END LOOP;
718 END IF;
719
720 -- Assign output parameters with locally generated table
721 x_plan_rsrc_tbl := l_temp_rsrc_tbl;
722
723 Ahl_Debug_Pub.debug('Completed Processing. Checking for errors', 'LTP');
724 -- Check Error Message stack.
725 x_msg_count := Fnd_Msg_Pub.count_msg;
726 IF x_msg_count > 0 THEN
727 RAISE Fnd_Api.G_EXC_ERROR;
728 END IF;
729
730 -- Standard check of p_commit
731 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
732 COMMIT WORK;
733 END IF;
734
735 -- Standard call to get message count and if count is 1, get message info
736 Fnd_Msg_Pub.Count_And_Get
737 ( p_count => x_msg_count,
738 p_data => x_msg_data,
739 p_encoded => Fnd_Api.g_false
740 );
741
742 -- Disable debug (if enabled)
743 Ahl_Debug_Pub.disable_debug;
744
745 EXCEPTION
746 WHEN Fnd_Api.G_EXC_ERROR THEN
747 ROLLBACK TO Get_Plan_Resources_pvt;
748 x_return_status := Fnd_Api.G_RET_STS_ERROR;
749 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
750 p_data => x_msg_data,
751 p_encoded => Fnd_Api.g_false);
752
753 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
754 ROLLBACK TO Get_Plan_Resources_pvt;
755 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
756 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
757 p_data => x_msg_data,
758 p_encoded => Fnd_Api.g_false);
759
760 WHEN OTHERS THEN
761 ROLLBACK TO Get_Plan_Resources_pvt;
762 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
763 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
764 Fnd_Msg_Pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
765 p_procedure_name => 'Get_Plan_Resources',
766 p_error_text => SUBSTR(SQLERRM,1,240));
767 END IF;
768 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
769 p_data => x_msg_data,
770 p_encoded => Fnd_Api.g_false);
771
772 END Get_Plan_Resources;
773
774 -- Start of Comments --
775 -- Procedure name : Get_Rsrc_Req_By_Period
776 -- Type : Private
777 -- Function : Gets the Requirements and Availability of a Resource
778 -- by periods for a given department, during a given period,
779 -- for a given Plan
780 -- Pre-reqs :
781 -- Parameters :
782 --
783 -- Standard IN Parameters :
784 -- p_api_version IN NUMBER Required
785 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
786 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
787 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
788 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
789 -- p_module_type IN VARCHAR2 Default NULL.
790 --
791 -- Standard OUT Parameters :
792 -- x_return_status OUT VARCHAR2 Required
793 -- x_msg_count OUT NUMBER Required
794 -- x_msg_data OUT VARCHAR2 Required
795 --
796 -- Get_Plan_Resources Parameters:
797 -- p_dept_id IN NUMBER REQUIRED
798 -- The department that is to be searched for resources
799 -- p_dept_name IN VARCHAR2 REQUIRED
800 -- The name of the department (will be mapped to Id if p_dept_id is not given)
801 -- p_org_name IN VARCHAR2 REQUIRED
802 -- The name of the organization where the department is
803 -- p_plan_id IN NUMBER REQUIRED
804 -- The id of the plan for which to get the resources
805 -- p_start_time IN DATE REQUIRED
806 -- Start time filter for tasks. Only tasks that are in progress at or after this
807 -- time will be considered. Tasks that end before this time will be ignored.
808 -- p_end_time IN DATE REQUIRED
809 -- End time filter for tasks. Only tasks that are in progress at or before this
810 -- time will be considered. Tasks that start after this time will be ignored.
811 -- p_UOM_id IN NUMBER REQUIRED
812 -- The id of the Period's unit of Measure (Days, Weeks, Months etc.)
813 -- p_resource_id IN NUMBER REQUIRED
814 -- The id of the Resource whose requirements/Availabilities are to be derived
815 -- p_aso_bom_rsrc_type IN VARCHAR2 REQUIRED
816 -- The type of the resource (ASORESOURCE or BOMRESOURCE)
817 -- x_per_rsrc_tbl OUT Period_Rsrc_Req_Tbl_Type
818 -- The table containing the distinct resources required.
819 --
820 -- Version :
821 -- Initial Version 1.0
822 --
823 -- End of Comments.
824
825 PROCEDURE Get_Rsrc_Req_By_Period
826 (
827 p_api_version IN NUMBER,
828 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
829 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
830 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
831 p_default IN VARCHAR2 := Fnd_Api.G_TRUE,
832 p_module_type IN VARCHAR2 := NULL,
833 p_dept_id IN NUMBER,
834 p_dept_name IN VARCHAR2,
835 p_org_name IN VARCHAR2,
836 p_plan_id IN NUMBER,
837 p_start_time IN DATE,
838 p_end_time IN DATE,
839 p_UOM_code IN VARCHAR2,
840 p_required_capacity IN NUMBER,
841 x_per_rsrc_tbl OUT NOCOPY Ahl_Ltp_Resrc_Levl_Pvt.Period_Rsrc_Req_Tbl_Type,
842 x_return_status OUT NOCOPY VARCHAR2,
843 x_msg_count OUT NOCOPY NUMBER,
844 x_msg_data OUT NOCOPY VARCHAR2
845 ) IS
846
847 /* Renamed l_dept_id_csr0 to l_dept_id_csr by mpothuku on 01/18/05
848 Also added the exists condition to retrieve only departments with shifts
849 */
850
851 -- yazhou 24Aug2005 starts
852 -- Resource Leveling Re-design
853 -- Added department name as Select field
854
855 CURSOR l_dept_id_csr(p_dept_name IN VARCHAR2, p_org_name IN VARCHAR2) IS
856 SELECT department_id, org.organization_id, dept.description dept_name
857 FROM bom_departments dept, hr_all_organization_units org
858 WHERE org.name = p_org_name AND
859 dept.ORGANIZATION_ID = org.ORGANIZATION_ID AND
860 EXISTS ( SELECT 'x' FROM AHL_DEPARTMENT_SHIFTS WHERE DEPARTMENT_ID = dept.DEPARTMENT_ID) AND
861 ( (p_dept_name is not null and dept.description = p_dept_name)
862 or p_dept_name is null ) ;
863
864 -- yazhou 24Aug2005 ends
865
866 /* Added the exists condition to retrieve only departments with shifts */
867 CURSOR l_validate_dept_csr(l_dept_id IN NUMBER) IS
868 SELECT description FROM bom_departments WHERE
869 department_id = l_dept_id and exists ( SELECT 'x' FROM AHL_DEPARTMENT_SHIFTS WHERE DEPARTMENT_ID = bom_departments.DEPARTMENT_ID);
870 --
871 CURSOR l_validate_org_csr(l_org_name IN VARCHAR2) IS
872 SELECT name FROM hr_all_organization_units WHERE
873 name = l_org_name;
874 --
875 CURSOR l_validate_plan_csr(l_plan_id IN NUMBER) IS
876 SELECT 'x' FROM ahl_simulation_plans_b WHERE
877 simulation_plan_id = l_plan_id;
878 --
879
880 -- yazhou 24Aug2005 starts
881 -- Resource Leveling Re-design
882
883 CURSOR l_resource_dtl_csr (l_resource_id IN NUMBER) IS
884 SELECT d.resource_code,
885 d.description,
886 d.resource_type,
887 m.meaning resource_type_mean
888 FROM bom_resources d, mfg_lookups m
889 WHERE d.resource_id = l_resource_id
890 AND d.resource_type = m.lookup_code
891 AND m.lookup_type = 'BOM_RESOURCE_TYPE';
892
893 -- yazhou 24Aug2005 ends
894
895 l_api_version CONSTANT NUMBER := 1.0;
896 l_api_name CONSTANT VARCHAR2(30) := 'Get_Rsrc_Req_By_Period';
897 l_return_status VARCHAR2(1);
898 l_msg_data VARCHAR2(200);
899 l_msg_count NUMBER;
900
901 -- yazhou 24Aug2005 starts
902 -- Resource Leveling Re-design
903
904 l_available_quantity NUMBER;
905 l_required_quantity NUMBER;
906 l_unused_capacity NUMBER;
907 l_dept_id NUMBER := NULL;
908 l_org_id NUMBER := NULL;
909 l_department_name bom_departments.description%type;
910 l_period_start_time DATE;
911 l_period_end_time DATE;
912
913 l_idx NUMBER := 0;
914
915 l_task_times_tbl Visit_Task_Times_Tbl_Type;
916 l_tp_dtls_table Time_Period_Details_Tbl_Type;
917 l_tp_dtls_table_dept Time_Period_Details_Tbl_Type;
918 l_tp_dtls_table_resc Time_Period_Details_Tbl_Type;
919 l_per_rsrc_tbl Period_Rsrc_Req_Tbl_Type;
920 l_resc_tbl Resource_Tbl_Type;
921
922 l_resource_dtl_rec l_resource_dtl_csr%rowtype;
923 -- yazhou 24Aug2005 ends
924
925 l_name HR_ALL_ORGANIZATION_UNITS.NAME%TYPE;
926 l_deprt_id NUMBER;
927 l_orga_id NUMBER;
928 l_junk VARCHAR2(1);
929 l_dept_name bom_departments.description%type;
930
931 BEGIN
932 -- Standard start of API savepoint
933 SAVEPOINT Get_Rsrc_Req_By_Period_pvt;
934
935 -- Standard call to check for call compatibility
936 IF NOT Fnd_Api.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
937 G_PKG_NAME) THEN
938 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
939 END IF;
940
941 -- Initialize message list if p_init_msg_list is set to TRUE
942 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
943 Fnd_Msg_Pub.Initialize;
944 END IF;
945
946 -- Initialize API return status to success
947 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
948
949 -- Enable Debug (optional)
950 Ahl_Debug_Pub.enable_debug;
951
952 -- Begin Processing
953
954 -- yazhou 24Aug2005 starts
955 -- Resource Leveling Re-design
956 -- Added Organization, start and end date mandatory check
957
958 IF (l_log_statement >= l_log_current_level) THEN
959 fnd_log.string
960 (
961 fnd_log.level_statement,
962 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
963 'p_plan_id:'||p_plan_id
964 );
965 fnd_log.string
966 (
967 fnd_log.level_statement,
968 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
969 'p_start_time:'||TO_CHAR( p_start_time, 'DD-MON-YYYY hh24:mi')
970 );
971 fnd_log.string
972 (
973 fnd_log.level_statement,
974 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
975 'p_end_time:'||TO_CHAR( p_end_time, 'DD-MON-YYYY hh24:mi')
976 );
977 fnd_log.string
978 (
979 fnd_log.level_statement,
980 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
981 'p_UOM_code:'||p_UOM_code
982 );
983 fnd_log.string
984 (
985 fnd_log.level_statement,
986 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
987 'p_org_name:'||p_org_name
988 );
989 fnd_log.string
990 (
991 fnd_log.level_statement,
992 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
993 'p_dept_name:'||p_dept_name
994 );
995 fnd_log.string
996 (
997 fnd_log.level_statement,
998 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
999 'p_required_capacity:'||p_required_capacity
1000 );
1001 END IF;
1002
1003 IF (p_org_name IS NULL) THEN
1004 Fnd_Message.Set_Name('AHL','AHL_LTP_ORG_MAN_JSP');
1005 Fnd_Msg_Pub.ADD;
1006 RAISE Fnd_Api.G_EXC_ERROR;
1007 END IF;
1008
1009 IF (p_start_time IS NULL) THEN
1010 Fnd_Message.Set_Name('AHL','AHL_LTP_STARTDATE_MAN_JSP');
1011 Fnd_Msg_Pub.ADD;
1012 RAISE Fnd_Api.G_EXC_ERROR;
1013 END IF;
1014
1015 IF (p_end_time IS NULL) THEN
1016 Fnd_Message.Set_Name('AHL','AHL_LTP_ENDDATE_MAN_JSP');
1017 Fnd_Msg_Pub.ADD;
1018 RAISE Fnd_Api.G_EXC_ERROR;
1019 END IF;
1020
1021 IF (TRUNC(p_start_time) < TRUNC(SYSDATE) ) THEN
1022 Fnd_Message.Set_Name('AHL','AHL_LTP_INVALID_START_DATE');
1023 Fnd_Msg_Pub.ADD;
1024 RAISE Fnd_Api.G_EXC_ERROR;
1025 END IF;
1026
1027 IF (TRUNC(p_end_time) < TRUNC(SYSDATE) ) THEN
1028 Fnd_Message.Set_Name('AHL','AHL_LTP_INVALID_END_DATE');
1029 Fnd_Msg_Pub.ADD;
1030 RAISE Fnd_Api.G_EXC_ERROR;
1031 END IF;
1032 -- yazhou 24Aug2005 ends
1033
1034 -- Map Dept Name To Dept Id
1035 IF (p_module_type = G_JSP_MODULE_TYPE) THEN
1036 -- dbms_output.put_line('JSP MODULE: Doing Value TO ID Conversion');
1037 -- validate org name
1038 OPEN l_validate_org_csr(p_org_name);
1039 FETCH l_validate_org_csr INTO l_name;
1040 IF (l_validate_org_csr%NOTFOUND) THEN
1041 Fnd_Message.Set_Name('AHL','AHL_LTP_ORG_ID_INVALID');
1042 Fnd_Message.Set_Token('ORG', p_org_name);
1043 Fnd_Msg_Pub.ADD;
1044 CLOSE l_validate_org_csr;
1045 RAISE Fnd_Api.G_EXC_ERROR;
1046 END IF;
1047 --
1048 CLOSE l_validate_org_csr;
1049 -- Validate dept name
1050 OPEN l_dept_id_csr(p_dept_name,p_org_name);
1051 FETCH l_dept_id_csr INTO l_deprt_id,l_orga_id, l_department_name;
1052 IF (l_dept_id_csr%NOTFOUND) THEN
1053 Fnd_Message.Set_Name('AHL','AHL_LTP_DEPT_INV_OR_NO_SFT');
1054 Fnd_Message.Set_Token('DEPT', p_dept_name);
1055 Fnd_Msg_Pub.ADD;
1056 CLOSE l_dept_id_csr;
1057 RAISE Fnd_Api.G_EXC_ERROR;
1058 END IF;
1059 CLOSE l_dept_id_csr;
1060 --
1061 l_dept_name := p_dept_name;
1062
1063 ELSE
1064 -- yazhou 24Aug2005 starts
1065 -- Resource Leveling Re-design
1066 -- removed the code to throw exception when p_dept_id is null
1067
1068 -- Validate Dept Id
1069 IF (p_dept_id IS NOT NULL) THEN
1070 OPEN l_validate_dept_csr(p_dept_id);
1071 FETCH l_validate_dept_csr INTO l_dept_name;
1072 IF (l_validate_dept_csr%NOTFOUND) THEN
1073 Fnd_Message.Set_Name('AHL','AHL_LTP_DEPT_INV_OR_NO_SFT');
1074 Fnd_Message.Set_Token('DEPT', p_dept_id);
1075 Fnd_Msg_Pub.ADD;
1076 CLOSE l_validate_dept_csr;
1077 RAISE Fnd_Api.G_EXC_ERROR;
1078 END IF;
1079 END IF;
1080
1081 -- yazhou 24Aug2005 ends
1082
1083 END IF;
1084
1085 -- yazhou 24Aug2005 starts
1086 -- Resource Leveling Re-design
1087 -- Moved out of department loop
1088
1089 -- Validate Plan Id
1090 IF (p_plan_id IS NULL) THEN
1091 Fnd_Message.Set_Name('AHL','AHL_LTP_PLAN_ID_NULL');
1092 Fnd_Msg_Pub.ADD;
1093 RAISE Fnd_Api.G_EXC_ERROR;
1094 ELSE
1095 OPEN l_validate_plan_csr(p_plan_id);
1096 FETCH l_validate_plan_csr INTO l_junk;
1097 IF (l_validate_plan_csr%NOTFOUND) THEN
1098 Fnd_Message.Set_Name('AHL','AHL_LTP_PLAN_ID_INVALID');
1099 Fnd_Message.Set_Token('PLAN', p_plan_id);
1100 Fnd_Msg_Pub.ADD;
1101 CLOSE l_validate_plan_csr;
1102 RAISE Fnd_Api.G_EXC_ERROR;
1103 END IF;
1104 CLOSE l_validate_plan_csr;
1105 END IF;
1106
1107 -- dbms_output.put_line('p_plan_id = ' || p_plan_id);
1108 Ahl_Debug_Pub.debug('p_plan_id = ' || p_plan_id);
1109
1110 -- Validate Dates
1111 IF (p_start_time IS NOT NULL AND p_end_time IS NOT NULL AND p_start_time > p_end_time) THEN
1112 Fnd_Message.Set_Name('AHL','AHL_LTP_STIME_GT_ETIME');
1113 Fnd_Message.Set_Token('STIME', p_start_time);
1114 Fnd_Message.Set_Token('ETIME', p_end_time);
1115 Fnd_Msg_Pub.ADD;
1116 RAISE Fnd_Api.G_EXC_ERROR;
1117 END IF;
1118
1119 -- yazhou 24Aug2005 ends
1120
1121 --
1122 -- Check for each department in the given organization
1123 --
1124 FOR var in l_dept_id_csr( l_dept_name , p_org_name) LOOP
1125
1126 l_dept_id := var.department_id;
1127 l_org_id := var.organization_id;
1128
1129 -- yazhou 24Aug2005 starts
1130 -- Resource Leveling Re-design
1131 l_department_name := var.dept_name;
1132 -- yazhou 24Aug2005 ends
1133
1134 IF (l_log_statement >= l_log_current_level) THEN
1135 fnd_log.string
1136 (
1137 fnd_log.level_statement,
1138 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1139 'l_org_id:'||l_org_id
1140 );
1141 fnd_log.string
1142 (
1143 fnd_log.level_statement,
1144 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1145 'l_dept_id:'||l_dept_id
1146 );
1147 fnd_log.string
1148 (
1149 fnd_log.level_statement,
1150 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1151 'l_department_name:'||l_department_name
1152 );
1153 END IF;
1154
1155 -- yazhou 24Aug2005 starts
1156 -- Resource Leveling Re-design
1157
1158 -- Get all the tasks for the given dept/plan
1159 -- that are in progress during the given timeframe
1160 -- since p_end_date is surfixed with "00:00", to include resources for the end date
1161 -- increase the date by 1
1162 Get_Plan_Tasks(
1163 p_dept_id => l_dept_id,
1164 p_plan_id => p_plan_id,
1165 p_start_time => p_start_time,
1166 p_end_time => p_end_time+1,
1167 p_inc_primary_flag => TRUE, -- Need to include primary plan tasks if this plan is a simulation
1168 -- p_op_times_flag => TRUE, -- Need to get Operation Time Period details
1169 x_tp_dtls_tbl => l_tp_dtls_table, -- Table for Operation Time Period details
1170 x_visit_task_times_tbl => l_task_times_tbl);
1171
1172 IF (l_log_statement >= l_log_current_level) THEN
1173 fnd_log.string
1174 (
1175 fnd_log.level_statement,
1176 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1177 'After calling Get_Plan_Tasks'
1178 );
1179 fnd_log.string
1180 (
1181 fnd_log.level_statement,
1182 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1183 'l_tp_dtls_table.COUNT:'||l_tp_dtls_table.COUNT
1184 );
1185 fnd_log.string
1186 (
1187 fnd_log.level_statement,
1188 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1189 'l_task_times_tbl.COUNT:'||l_task_times_tbl.COUNT
1190 );
1191 END IF;
1192
1193 -- continue only if there are requirements for this department
1194 IF l_tp_dtls_table.count>0 THEN
1195 -- Create the timeperiods table for output
1196 -- since p_end_date is surfixed with "00:00", to create time periods that includes
1197 -- the end date, increase the date by 1
1198 Create_Time_Periods(
1199 p_start_time => p_start_time,
1200 p_end_time => p_end_time+1,
1201 p_UOM_code => p_UOM_code,
1202 p_org_id => l_org_id,
1203 p_dept_id => l_dept_id,
1204 x_per_rsrc_tbl => l_per_rsrc_tbl);
1205
1206 IF (l_log_statement >= l_log_current_level) THEN
1207 fnd_log.string
1208 (
1209 fnd_log.level_statement,
1210 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1211 'After calling Create_Time_Periods'
1212 );
1213 fnd_log.string
1214 (
1215 fnd_log.level_statement,
1216 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1217 'l_per_rsrc_tbl.COUNT:'||l_per_rsrc_tbl.COUNT
1218 );
1219 END IF;
1220
1221 -- For each time period, get all the resources required for the given department
1222
1223 IF l_per_rsrc_tbl.COUNT > 0 THEN
1224 FOR i IN l_per_rsrc_tbl.FIRST..l_per_rsrc_tbl.LAST LOOP
1225
1226 l_period_start_time := l_per_rsrc_tbl(i).period_start;
1227 l_period_end_time := l_per_rsrc_tbl(i).period_end;
1228
1229 l_tp_dtls_table_dept := l_tp_dtls_table;
1230
1231 IF (l_log_statement >= l_log_current_level) THEN
1232 fnd_log.string
1233 (
1234 fnd_log.level_statement,
1235 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1236 'l_per_rsrc_tbl('||i||').period_start:'||TO_CHAR( l_period_start_time, 'DD-MON-YYYY hh24:mi')
1237 );
1238 fnd_log.string
1239 (
1240 fnd_log.level_statement,
1241 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1242 'l_per_rsrc_tbl('||i||').period_end:'||TO_CHAR( l_period_end_time, 'DD-MON-YYYY hh24:mi')
1243 );
1244 END IF;
1245
1246 -- Get all the resources that are used in the given period for the given department
1247 -- by tasks in Planning
1248 Get_Used_Resources(
1249 p_dept_id => l_dept_id,
1250 p_start_date => l_period_start_time,
1251 p_end_date => l_period_end_time,
1252 p_tp_dtls_tbl => l_tp_dtls_table_dept,
1253 x_resources_tbl => l_resc_tbl);
1254
1255 IF (l_log_statement >= l_log_current_level) THEN
1256 fnd_log.string
1257 (
1258 fnd_log.level_statement,
1259 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1260 'After calling Get_Used_Resources'
1261 );
1262 fnd_log.string
1263 (
1264 fnd_log.level_statement,
1265 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1266 'l_resc_tbl.COUNT:'||l_resc_tbl.COUNT
1267 );
1268 END IF;
1269
1270 -- For each resource, check for required quantity and calculate availability capacity
1271 IF l_resc_tbl.COUNT > 0 THEN
1272 FOR j IN l_resc_tbl.FIRST..l_resc_tbl.LAST LOOP
1273
1274 l_tp_dtls_table_resc := l_tp_dtls_table_dept;
1275
1276 IF (l_log_statement >= l_log_current_level) THEN
1277 fnd_log.string
1278 (
1279 fnd_log.level_statement,
1280 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1281 'l_resc_tbl('||j||'):' || l_resc_tbl(j)
1282 );
1283 END IF;
1284
1285 -- Filter resource requirements table to get only those for the given resource,
1286 -- given period and given department
1287 Filter_By_Resource(
1288 p_resource_id =>l_resc_tbl(j),
1289 p_start_time =>l_period_start_time,
1290 p_end_time =>l_period_end_time,
1291 p_x_tp_dtls_tbl =>l_tp_dtls_table_resc);
1292
1293 IF (l_log_statement >= l_log_current_level) THEN
1294 fnd_log.string
1295 (
1296 fnd_log.level_statement,
1297 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1298 'After Calling Filter_By_Resource'
1299 );
1300 fnd_log.string
1301 (
1302 fnd_log.level_statement,
1303 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1304 'l_tp_dtls_table_resc.count: ' ||l_tp_dtls_table_resc.count
1305 );
1306 END IF;
1307
1308 -- Populate output table only if there are resource requirements by tasks
1309 IF l_tp_dtls_table_resc.COUNT>0 THEN
1310
1311 -- Add resource requirements by WIP job for the same department, resource, period
1312 Append_WIP_Requirements(
1313 p_org_id => l_org_id,
1314 p_dept_id => l_dept_id,
1315 p_start_date => l_period_start_time,
1316 p_end_date => l_period_end_time,
1317 p_resource_id => l_resc_tbl(j),
1318 p_x_tp_dtls_tbl => l_tp_dtls_table_resc);
1319
1320 IF (l_log_statement >= l_log_current_level) THEN
1321 fnd_log.string
1322 (
1323 fnd_log.level_statement,
1324 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1325 'After Calling Append_WIP_Requirements'
1326 );
1327 fnd_log.string
1328 (
1329 fnd_log.level_statement,
1330 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1331 'l_tp_dtls_table_resc.count: ' ||l_tp_dtls_table_resc.count
1332 );
1333 END IF;
1334
1335 l_required_quantity := Get_Required_Quantity(
1336 p_start_time =>l_period_start_time,
1337 p_end_time =>l_period_end_time,
1338 p_tp_dtls_tbl =>l_tp_dtls_table_resc);
1339
1340 IF (l_log_statement >= l_log_current_level) THEN
1341 fnd_log.string
1342 (
1343 fnd_log.level_statement,
1344 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1345 'l_required_quantity:'||l_required_quantity
1346 );
1347 END IF;
1348
1349 l_available_quantity := Get_Available_Units(
1350 p_resource_id =>l_resc_tbl(j),
1351 p_dept_id =>l_dept_id);
1352
1353 IF (l_log_statement >= l_log_current_level) THEN
1354 fnd_log.string
1355 (
1356 fnd_log.level_statement,
1357 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1358 'l_available_quantity:'||l_available_quantity
1359 );
1360 END IF;
1361
1362 IF (l_available_quantity >0 AND l_required_quantity>0 ) THEN
1363
1364 l_unused_capacity := (1-(l_required_quantity/l_available_quantity))*100;
1365
1366 IF (l_log_statement >= l_log_current_level) THEN
1367 fnd_log.string
1368 (
1369 fnd_log.level_statement,
1370 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1371 'l_unused_capacity:'||l_unused_capacity
1372 );
1373 END IF;
1374
1375 IF l_unused_capacity <= p_required_capacity THEN
1376
1377 x_per_rsrc_tbl(l_idx).resource_id := l_resc_tbl(j);
1378 x_per_rsrc_tbl(l_idx).capacity_units := round(l_unused_capacity);
1379 x_per_rsrc_tbl(l_idx).period_start := l_period_start_time;
1380 x_per_rsrc_tbl(l_idx).period_end := l_period_end_time;
1381 x_per_rsrc_tbl(l_idx).period_string := l_per_rsrc_tbl(i).period_string;
1382 x_per_rsrc_tbl(l_idx).DEPARTMENT_ID := l_dept_id;
1383 x_per_rsrc_tbl(l_idx).REQUIRED_UNITS := l_required_quantity;
1384 x_per_rsrc_tbl(l_idx).AVAILABLE_UNITS := l_available_quantity;
1385 x_per_rsrc_tbl(l_idx).dept_description := l_department_name;
1386
1387 -- Populate resource name, type code, type meaning and description
1388 OPEN l_resource_dtl_csr(l_resc_tbl(j));
1389 FETCH l_resource_dtl_csr into l_resource_dtl_rec;
1390 CLOSE l_resource_dtl_csr;
1391
1392 x_per_rsrc_tbl(l_idx).resource_type := l_resource_dtl_rec.RESOURCE_TYPE;
1393 x_per_rsrc_tbl(l_idx).resource_type_meaning := l_resource_dtl_rec.RESOURCE_TYPE_MEAN;
1394 x_per_rsrc_tbl(l_idx).resource_name := l_resource_dtl_rec.RESOURCE_CODE;
1395 x_per_rsrc_tbl(l_idx).resource_description := l_resource_dtl_rec.DESCRIPTION;
1396
1397 l_idx := l_idx +1;
1398
1399 IF (l_log_statement >= l_log_current_level) THEN
1400 fnd_log.string
1401 (
1402 fnd_log.level_statement,
1403 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1404 'l_idx:'||l_idx
1405 );
1406 END IF;
1407
1408 END IF;--l_unused_capacity <= p_required_capacity
1409
1410 END IF; -- l_available_quantity >0 AND l_required_quantity>0
1411 END IF; --l_tp_dtls_table_resc.COUNT>0
1412 END LOOP; --l_resc_tbl.LOOP
1413 END IF; -- l_resc_tbl.count
1414 END LOOP; --l_per_rsrc_tbl.LOOP
1415 END IF; -- l_per_rsrc_tbl.count
1416 END IF; --l_tp_dtls_table.count>0
1417
1418 -- yazhou 24Aug2005 ends
1419
1420 END LOOP; --dept id
1421
1422 -- Check Error Message stack.
1423
1424 x_msg_count := Fnd_Msg_Pub.count_msg;
1425 IF x_msg_count > 0 THEN
1426 RAISE Fnd_Api.G_EXC_ERROR;
1427 END IF;
1428
1429
1430 -- Standard check of p_commit
1431 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1432 COMMIT WORK;
1433 END IF;
1434
1435 -- Standard call to get message count and if count is 1, get message info
1436 Fnd_Msg_Pub.Count_And_Get
1437 ( p_count => x_msg_count,
1438 p_data => x_msg_data,
1439 p_encoded => Fnd_Api.g_false
1440 );
1441
1442 -- Disable debug (if enabled)
1443 Ahl_Debug_Pub.disable_debug;
1444
1445 EXCEPTION
1446 WHEN Fnd_Api.G_EXC_ERROR THEN
1447 ROLLBACK TO Get_Rsrc_Req_By_Period_pvt;
1448 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1449 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
1450 p_data => x_msg_data,
1451 p_encoded => Fnd_Api.g_false);
1452
1453 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1454 ROLLBACK TO Get_Rsrc_Req_By_Period_pvt;
1455 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1456 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
1457 p_data => x_msg_data,
1458 p_encoded => Fnd_Api.g_false);
1459
1460 WHEN OTHERS THEN
1461 ROLLBACK TO Get_Rsrc_Req_By_Period_pvt;
1462 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1463 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
1464 Fnd_Msg_Pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1465 p_procedure_name => 'Get_Rsrc_Req_By_Period',
1466 p_error_text => SUBSTR(SQLERRM,1,240));
1467 END IF;
1468 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
1469 p_data => x_msg_data,
1470 p_encoded => Fnd_Api.g_false);
1471
1472 END Get_Rsrc_Req_By_Period;
1473
1474
1475 -- Start of Comments --
1476 -- Procedure name : Get_Task_Requirements
1477 -- Type : Private
1478 -- Function : Gets the Requirements of a Resource by Visit/Task
1479 -- for a given department, during a given period,
1480 -- for a given Plan
1481 -- Pre-reqs :
1482 -- Parameters :
1483 --
1484 -- Standard IN Parameters :
1485 -- p_api_version IN NUMBER Required
1486 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1487 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1488 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1489 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
1490 -- p_module_type IN VARCHAR2 Default NULL.
1491 --
1492 -- Standard OUT Parameters :
1493 -- x_return_status OUT VARCHAR2 Required
1494 -- x_msg_count OUT NUMBER Required
1495 -- x_msg_data OUT VARCHAR2 Required
1496 --
1497 -- Get_Plan_Resources Parameters:
1498 -- p_dept_id IN NUMBER REQUIRED
1499 -- The department that is to be searched for resources
1500 -- p_dept_name IN VARCHAR2 REQUIRED
1501 -- The name of the department (will be mapped to Id if p_dept_id is not given)
1502 -- p_org_name IN VARCHAR2 REQUIRED
1503 -- The name of the organization where the department is
1504 -- p_plan_id IN NUMBER REQUIRED
1505 -- The id of the plan for which to get the resources
1506 -- p_start_time IN DATE REQUIRED
1507 -- Start time filter for tasks. Only tasks that are in progress at or after this
1508 -- time will be considered. Tasks that end before this time will be ignored.
1509 -- p_end_time IN DATE REQUIRED
1510 -- End time filter for tasks. Only tasks that are in progress before this
1511 -- time will be considered. Tasks that start at or after this time will be ignored.
1512 -- p_resource_id IN NUMBER REQUIRED
1513 -- The id of the Resource whose requirements/Availabilities are to be derived
1514 -- p_aso_bom_rsrc_type IN VARCHAR2 REQUIRED
1515 -- The type of the resource (ASORESOURCE or BOMRESOURCE)
1516 -- x_task_req_tbl OUT Task_Requirement_Tbl_Type
1517 -- The table containing the resource requirements.
1518 --
1519 -- Version :
1520 -- Initial Version 1.0
1521 --
1522 -- End of Comments.
1523 PROCEDURE Get_Task_Requirements
1524 (
1525 p_api_version IN NUMBER,
1526 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
1527 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
1528 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
1529 p_default IN VARCHAR2 := Fnd_Api.G_TRUE,
1530 p_module_type IN VARCHAR2 := NULL,
1531 p_dept_id IN NUMBER,
1532 p_dept_name IN VARCHAR2,
1533 p_org_name IN VARCHAR2,
1534 p_plan_id IN NUMBER,
1535 p_start_time IN DATE,
1536 p_end_time IN DATE,
1537 p_dstart_time IN DATE,
1538 p_dend_time IN DATE,
1539 p_resource_id IN NUMBER,
1540 p_aso_bom_rsrc_type IN VARCHAR2,
1541 x_task_req_tbl OUT NOCOPY AHL_LTP_RESRC_LEVL_PVT.Task_Requirement_Tbl_Type,
1542 x_return_status OUT NOCOPY VARCHAR2,
1543 x_msg_count OUT NOCOPY NUMBER,
1544 x_msg_data OUT NOCOPY VARCHAR2
1545 ) IS
1546
1547
1548 /* Modified l_dept_id_csr by mpothuku on 01/18/05
1549 to add the exists condition to retrieve only department with shifts
1550 */
1551
1552 CURSOR l_dept_id_csr(p_dept_name IN VARCHAR2, p_org_name IN VARCHAR2) IS
1553 SELECT department_id,org.organization_id FROM bom_departments dept, hr_all_organization_units org
1554 WHERE org.name = p_org_name AND
1555 dept.ORGANIZATION_ID = org.ORGANIZATION_ID AND
1556 dept.description = p_dept_name and exists ( SELECT 'x' FROM AHL_DEPARTMENT_SHIFTS WHERE DEPARTMENT_ID = dept.DEPARTMENT_ID);
1557
1558 /* Added the exists condition to retrieve only departments with shifts */
1559 CURSOR l_validate_dept_csr(l_dept_id IN NUMBER) IS
1560 SELECT 'x' FROM bom_departments WHERE
1561 department_id = l_dept_id and exists ( SELECT 'x' FROM AHL_DEPARTMENT_SHIFTS WHERE DEPARTMENT_ID = bom_departments.DEPARTMENT_ID);
1562
1563 CURSOR l_validate_plan_csr(l_plan_id IN NUMBER) IS
1564 SELECT 'x' FROM ahl_simulation_plans_b WHERE
1565 simulation_plan_id = l_plan_id;
1566
1567
1568 l_api_version CONSTANT NUMBER := 1.0;
1569 l_api_name CONSTANT VARCHAR2(30) := 'Get_Task_Requirements';
1570
1571 l_dept_id NUMBER := NULL;
1572 l_org_id NUMBER;
1573 l_junk VARCHAR2(1);
1574 l_task_times_tbl Visit_Task_Times_Tbl_Type;
1575 l_temp_rsrc_tbl Plan_Rsrc_Tbl_Type;
1576 l_tp_dtls_table Time_Period_Details_Tbl_Type;
1577 l_start_time DATE;
1578 l_end_time DATE;
1579 l_aso_resource_id NUMBER;
1580 BEGIN
1581 -- dbms_output.put_line('Entering Get_Task_Requirements');
1582 -- Standard start of API savepoint
1583 SAVEPOINT Get_Task_Requirements_pvt;
1584
1585 -- Standard call to check for call compatibility
1586 IF NOT Fnd_Api.Compatible_API_Call(l_api_version, p_api_version, l_api_name,
1587 G_PKG_NAME) THEN
1588 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1589 END IF;
1590
1591 -- Initialize message list if p_init_msg_list is set to TRUE
1592 IF Fnd_Api.To_Boolean(p_init_msg_list) THEN
1593 Fnd_Msg_Pub.Initialize;
1594 END IF;
1595
1596 -- Initialize API return status to success
1597 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
1598
1599 -- Enable Debug (optional)
1600 Ahl_Debug_Pub.enable_debug;
1601
1602 -- Begin Processing
1603 -- dbms_output.put_line('BEGIN Processing');
1604 -- Map Dept Name To Dept Id
1605 IF (p_module_type = G_JSP_MODULE_TYPE) THEN
1606 -- dbms_output.put_line('JSP MODULE: Doing Value TO ID Conversion');
1607 OPEN l_dept_id_csr(p_dept_name, p_org_name);
1608 FETCH l_dept_id_csr INTO l_dept_id,l_org_id;
1609 IF (l_dept_id_csr%NOTFOUND) THEN
1610 Fnd_Message.Set_Name('AHL','AHL_LTP_DEPT_INV_OR_NO_SFT');
1611 Fnd_Message.Set_Token('DEPT', p_dept_name);
1612 Fnd_Msg_Pub.ADD;
1613 CLOSE l_dept_id_csr;
1614 RAISE Fnd_Api.G_EXC_ERROR;
1615 END IF;
1616 CLOSE l_dept_id_csr;
1617 -- dbms_output.put_line('l_dept_id = ' || l_dept_id);
1618 ELSE
1619 -- Validate Dept Id
1620 IF (p_dept_id IS NULL) THEN
1621 Fnd_Message.Set_Name('AHL','AHL_LTP_DEPT_NULL');
1622 Fnd_Msg_Pub.ADD;
1623 RAISE Fnd_Api.G_EXC_ERROR;
1624 ELSE
1625 OPEN l_validate_dept_csr(p_dept_id);
1626 FETCH l_validate_dept_csr INTO l_junk;
1627 IF (l_validate_dept_csr%NOTFOUND) THEN
1628 Fnd_Message.Set_Name('AHL','AHL_LTP_DEPT_INV_OR_NO_SFT');
1629 Fnd_Message.Set_Token('DEPT', p_dept_id);
1630 Fnd_Msg_Pub.ADD;
1631 CLOSE l_validate_dept_csr;
1632 RAISE Fnd_Api.G_EXC_ERROR;
1633 END IF;
1634 CLOSE l_validate_dept_csr;
1635 END IF;
1636 l_dept_id := p_dept_id;
1637 -- dbms_output.put_line('l_dept_id = ' || l_dept_id);
1638 END IF;
1639
1640 -- Validate Plan Id
1641 IF (p_plan_id IS NULL) THEN
1642 Fnd_Message.Set_Name('AHL','AHL_LTP_PLAN_ID_NULL');
1643 Fnd_Msg_Pub.ADD;
1644 RAISE Fnd_Api.G_EXC_ERROR;
1645 ELSE
1646 OPEN l_validate_plan_csr(p_plan_id);
1647 FETCH l_validate_plan_csr INTO l_junk;
1648 IF (l_validate_plan_csr%NOTFOUND) THEN
1649 Fnd_Message.Set_Name('AHL','AHL_LTP_PLAN_ID_INVALID');
1650 Fnd_Message.Set_Token('PLAN', p_plan_id);
1651 Fnd_Msg_Pub.ADD;
1652 CLOSE l_validate_plan_csr;
1653 RAISE Fnd_Api.G_EXC_ERROR;
1654 END IF;
1655 CLOSE l_validate_plan_csr;
1656 END IF;
1657 -- dbms_output.put_line('p_plan_id = ' || p_plan_id);
1658
1659 -- Validate Dates
1660 IF (p_start_time IS NOT NULL AND p_end_time IS NOT NULL AND p_start_time > p_end_time) THEN
1661 Fnd_Message.Set_Name('AHL','AHL_LTP_STIME_GT_ETIME');
1662 Fnd_Message.Set_Token('STIME', p_start_time);
1663 Fnd_Message.Set_Token('ETIME', p_end_time);
1664 Fnd_Msg_Pub.ADD;
1665 RAISE Fnd_Api.G_EXC_ERROR;
1666 END IF;
1667
1668 -- dbms_output.put_line('Completed Mapping. Calling Get_Plan_Tasks');
1669 -- l_start_time := trunc(p_start_time);
1670 -- l_end_time := trunc(p_start_time) + 1;
1671
1672 -- Get all the tasks for the given dept/plan
1673 -- that are in progress during the given timeframe
1674 Get_Plan_Tasks(
1675 p_dept_id => p_dept_id,
1676 p_plan_id => p_plan_id,
1677 p_start_time => p_start_time,
1678 p_end_time => p_end_time,
1679 p_inc_primary_flag => TRUE, -- Need to include primary plan tasks if this plan is a simulation
1680 -- p_op_times_flag => TRUE, -- Need to get Operation Time Period details
1681 x_tp_dtls_tbl => l_tp_dtls_table, -- Table for Operation Time Period details
1682 x_visit_task_times_tbl => l_task_times_tbl);
1683
1684 -- dbms_output.put_line('Completed Get_Plan_Tasks. Calling Filter_By_Resource');
1685 Ahl_Debug_Pub.debug('before call filter l_tp_dtls_table.COUNT = ' || l_tp_dtls_table.COUNT);
1686 Ahl_Debug_Pub.debug('p_resource_id = ' || p_resource_id);
1687 Ahl_Debug_Pub.debug('p_strat_time = ' || p_start_time);
1688 Ahl_Debug_Pub.debug('p_end_time = ' || p_end_time);
1689
1690 -- Filter by the required resource and get quantity
1691 Filter_By_Resource(
1692 p_resource_id => p_resource_id,
1693 p_start_time => p_dstart_time,
1694 p_end_time => p_dend_time,
1695 p_x_tp_dtls_tbl => l_tp_dtls_table);
1696
1697 -- dbms_output.put_line('l_tp_dtls_table.COUNT = ' || l_tp_dtls_table.COUNT);
1698
1699 Ahl_Debug_Pub.debug('l_tp_dtls_tablafter filter = ' || l_tp_dtls_table.COUNT);
1700
1701 -- Sort By Visit, Task
1702 Sort_By_Visit_Task(l_tp_dtls_table);
1703
1704 Ahl_Debug_Pub.debug('after sort by visit = ' || l_tp_dtls_table.COUNT);
1705
1706 -- Aggregate task quantities and get visit and task names into output table
1707 Aggregate_Task_Quantities(p_resource_id,p_org_name, p_dept_name, l_tp_dtls_table, x_task_req_tbl);
1708
1709 Ahl_Debug_Pub.debug('x_task_req_tbl.COUNT = ' || x_task_req_tbl.COUNT);
1710
1711 -- dbms_output.put_line('x_task_req_tbl.COUNT = ' || x_task_req_tbl.COUNT);
1712 Ahl_Debug_Pub.debug('Completed Processing. Checking FOR errors', 'LTP');
1713 -- Check Error Message stack.
1714 x_msg_count := Fnd_Msg_Pub.count_msg;
1715 IF x_msg_count > 0 THEN
1716 RAISE Fnd_Api.G_EXC_ERROR;
1717 END IF;
1718
1719 -- Standard check of p_commit
1720 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1721 COMMIT WORK;
1722 END IF;
1723
1724 -- Standard call to get message count and if count is 1, get message info
1725 Fnd_Msg_Pub.Count_And_Get
1726 ( p_count => x_msg_count,
1727 p_data => x_msg_data,
1728 p_encoded => Fnd_Api.g_false
1729 );
1730
1731 -- Disable debug (if enabled)
1732 Ahl_Debug_Pub.disable_debug;
1733
1734 EXCEPTION
1735 WHEN Fnd_Api.G_EXC_ERROR THEN
1736 ROLLBACK TO Get_Task_Requirements_pvt;
1737 x_return_status := Fnd_Api.G_RET_STS_ERROR;
1738 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
1739 p_data => x_msg_data,
1740 p_encoded => Fnd_Api.g_false);
1741
1742 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
1743 ROLLBACK TO Get_Task_Requirements_pvt;
1744 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1745 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
1746 p_data => x_msg_data,
1747 p_encoded => Fnd_Api.g_false);
1748
1749 WHEN OTHERS THEN
1750 ROLLBACK TO Get_Task_Requirements_pvt;
1751 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1752 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR) THEN
1753 Fnd_Msg_Pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
1754 p_procedure_name => 'Get_Task_Requirements',
1755 p_error_text => SUBSTR(SQLERRM,1,240));
1756 END IF;
1757 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
1758 p_data => x_msg_data,
1759 p_encoded => Fnd_Api.g_false);
1760
1761 END Get_Task_Requirements;
1762
1763 ----------------------------------------
1764 -- Local Procedure Definitions follow --
1765 ----------------------------------------
1766 ----------------------------------------
1767 -- Gets all the tasks and their times for all visits
1768 -- for a given plan and department during a given period
1769 -- If current plan is a simulation and the p_inc_primary flag is 'Y'
1770 -- Visits in primary plan are also included.
1771 ----------------------------------------
1772
1773 -- yazhou 24Aug2005 starts
1774 -- Resource Leveling Re-design
1775 -- changed x_visit_task_times_tbl from in out parameter to out parameter only
1776 -- removed p_op_times_flag
1777 PROCEDURE Get_Plan_Tasks
1778 (
1779 p_dept_id IN NUMBER,
1780 p_plan_id IN NUMBER,
1781 p_start_time IN DATE,
1782 p_end_time IN DATE,
1783 p_inc_primary_flag IN BOOLEAN := FALSE,
1784 x_tp_dtls_tbl OUT NOCOPY Time_Period_Details_Tbl_Type,
1785 x_visit_task_times_tbl OUT NOCOPY Visit_Task_Times_Tbl_Type
1786 ) IS
1787
1788 -- Should include visits with tasks in given department as well as
1789 -- those visits with given department
1790 CURSOR l_get_plan_visits(p_dept_id IN NUMBER,
1791 p_plan_id IN NUMBER,
1792 p_start_time IN DATE,
1793 p_end_time IN DATE) IS
1794 SELECT VISIT_ID
1795 FROM ahl_visits_b v
1796 WHERE (DEPARTMENT_ID = p_dept_id OR
1797 (DEPARTMENT_ID is not null and exists (select 1
1798 FROM ahl_visit_tasks_b
1799 where visit_id =v.visit_id
1800 AND department_id =p_dept_id
1801 AND nvl(status_code,'x') ='PLANNING'))) AND
1802 SIMULATION_PLAN_ID = p_plan_id AND
1803 STATUS_CODE IN ('PLANNING','PARTIALLY RELEASED') AND
1804 START_DATE_TIME IS NOT NULL AND
1805 trunc(START_DATE_TIME) <= p_end_time AND
1806 AHL_VWP_TIMES_PVT.get_visit_end_time(visit_id) >=p_start_time AND
1807 SIMULATION_DELETE_FLAG = 'N';
1808
1809 CURSOR l_get_primary_visits(p_dept_id IN NUMBER,
1810 p_plan_id IN NUMBER,
1811 p_start_time IN DATE,
1812 p_end_time IN DATE) IS
1813 SELECT VISIT_ID
1814 FROM ahl_visits_b a
1815 WHERE (DEPARTMENT_ID = p_dept_id OR
1816 (DEPARTMENT_ID is not null and exists (select 1
1817 FROM ahl_visit_tasks_b
1818 where visit_id =a.visit_id
1819 AND department_id =p_dept_id
1820 AND nvl(status_code,'x') ='PLANNING'))) AND
1821 SIMULATION_PLAN_ID IN (SELECT SIMULATION_PLAN_ID FROM ahl_simulation_plans_b WHERE
1822 PRIMARY_PLAN_FLAG = G_PLAN_TYPE_PRIMARY) AND
1823 NOT EXISTS (SELECT 1 FROM ahl_visits_b WHERE SIMULATION_PLAN_ID = p_plan_id
1824 AND ASSO_PRIMARY_VISIT_ID = a.VISIT_ID) AND
1825 STATUS_CODE IN ('PLANNING','PARTIALLY RELEASED') AND
1826 START_DATE_TIME IS NOT NULL AND
1827 trunc(START_DATE_TIME) <= p_end_time AND
1828 AHL_VWP_TIMES_PVT.get_visit_end_time(visit_id) >=p_start_time;
1829
1830 -- yazhou 24Aug2005 ends
1831
1832 CURSOR l_get_plan_type_csr(p_plan_id IN NUMBER) IS
1833 SELECT PRIMARY_PLAN_FLAG FROM ahl_simulation_plans_b
1834 WHERE SIMULATION_PLAN_ID = p_plan_id;
1835
1836 l_api_version CONSTANT NUMBER := 1.0;
1837 l_api_name CONSTANT VARCHAR2(30) := 'Get_Plan_Tasks';
1838
1839 l_temp_times_tbl Visit_Task_Times_Tbl_Type;
1840 l_final_times_tbl Visit_Task_Times_Tbl_Type;
1841 l_temp_index NUMBER := 1;
1842 l_temp_visit NUMBER;
1843 l_temp_sttime DATE;
1844 l_temp_endtime DATE;
1845 l_return_status VARCHAR2(1);
1846 l_msg_count NUMBER;
1847 l_msg_data VARCHAR2(1000);
1848 l_temp_num1 NUMBER;
1849 l_plan_type VARCHAR2(30);
1850 l_end_time DATE := trunc(p_end_time);
1851
1852 BEGIN
1853
1854 IF (l_log_statement >= l_log_current_level) THEN
1855 fnd_log.string
1856 (
1857 fnd_log.level_statement,
1858 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1859 'p_start_time:'||p_start_time
1860 );
1861 fnd_log.string
1862 (
1863 fnd_log.level_statement,
1864 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1865 'l_end_time:'||l_end_time
1866 );
1867 fnd_log.string
1868 (
1869 fnd_log.level_statement,
1870 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1871 'p_dept_id:'||p_dept_id
1872 );
1873
1874 END IF;
1875
1876 OPEN l_get_plan_visits(p_dept_id, p_plan_id, p_start_time,l_end_time);
1877 LOOP
1878 FETCH l_get_plan_visits INTO l_temp_visit;
1879 EXIT WHEN l_get_plan_visits%NOTFOUND;
1880
1881 IF (l_log_statement >= l_log_current_level) THEN
1882 fnd_log.string
1883 (
1884 fnd_log.level_statement,
1885 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1886 'l_temp_visit:'||l_temp_visit
1887 );
1888 END IF;
1889
1890 -- yazhou 24Aug2005 starts
1891 -- Resource Leveling Re-design
1892
1893 -- removed the logic to check for mr route ID
1894 --since now that's done inside Derive_Task_Op_Times already
1895
1896 -- Call helper method to do the actual processing
1897 Derive_Task_Op_Times(
1898 p_visit_id => l_temp_visit,
1899 p_start_time => p_start_time,
1900 p_end_time => p_end_time,
1901 p_department_id => p_dept_id,
1902 p_x_tp_dtls_tbl => x_tp_dtls_tbl, -- Table for Operation Time Periods
1903 x_visit_start_time => l_temp_sttime,
1904 x_visit_end_time => l_temp_endtime,
1905 x_visit_end_hour => l_temp_num1,
1906 x_visit_task_times_tbl => l_final_times_tbl);
1907
1908 IF (l_log_statement >= l_log_current_level) THEN
1909 fnd_log.string
1910 (
1911 fnd_log.level_statement,
1912 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1913 'After calling Derive_Task_Op_Times'
1914 );
1915 fnd_log.string
1916 (
1917 fnd_log.level_statement,
1918 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1919 'l_final_times_tbl.count: '|| l_final_times_tbl.count
1920 );
1921 END IF;
1922
1923 /*
1924 -- Collect the task info for the current visit into the master tasks table
1925 IF (l_temp_times_tbl.COUNT > 0) THEN
1926
1927 l_temp_num1 := l_temp_times_tbl.FIRST;
1928
1929 WHILE l_temp_num1 IS NOT NULL LOOP
1930 IF l_temp_times_tbl(l_temp_num1).mr_route_id IS NOT NULL
1931 THEN
1932 l_final_times_tbl(l_temp_index) := l_temp_times_tbl(l_temp_num1);
1933 l_temp_index := l_temp_index + 1;
1934
1935 END IF;
1936 l_temp_num1 := l_temp_times_tbl.NEXT(l_temp_num1);
1937 END LOOP; -- All valid tasks for current visit
1938 END IF;
1939
1940 */
1941 -- yazhou 24Aug2005 ends
1942
1943 END LOOP; -- All valid visits for current dept
1944 CLOSE l_get_plan_visits;
1945
1946
1947 IF (p_inc_primary_flag) THEN
1948 -- Need to include primary plan visits also
1949 -- dbms_output.put_line('Getting PRIMARY Plans Visits');
1950 OPEN l_get_plan_type_csr(p_plan_id);
1951 FETCH l_get_plan_type_csr INTO l_plan_type;
1952 CLOSE l_get_plan_type_csr;
1953
1954 IF (l_log_statement >= l_log_current_level) THEN
1955 fnd_log.string
1956 (
1957 fnd_log.level_statement,
1958 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1959 'l_plan_type: '|| l_plan_type
1960 );
1961 END IF;
1962
1963 IF (l_plan_type <> G_PLAN_TYPE_PRIMARY) THEN
1964 -- Current Plan is a simulation: Include primary plan's visit tasks
1965 OPEN l_get_primary_visits(p_dept_id, p_plan_id, p_start_time, l_end_time);
1966 LOOP
1967 FETCH l_get_primary_visits INTO l_temp_visit;
1968 EXIT WHEN l_get_primary_visits%NOTFOUND;
1969
1970 IF (l_log_statement >= l_log_current_level) THEN
1971 fnd_log.string
1972 (
1973 fnd_log.level_statement,
1974 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1975 'l_temp_visit:'||l_temp_visit
1976 );
1977 END IF;
1978
1979 -- Call helper method to do the actual processing
1980 Derive_Task_Op_Times(
1981 p_visit_id => l_temp_visit,
1982 p_start_time => p_start_time,
1983 p_end_time => p_end_time,
1984 p_department_id => p_dept_id,
1985 p_x_tp_dtls_tbl => x_tp_dtls_tbl, -- Table for Operation Time Periods
1986 x_visit_start_time => l_temp_sttime,
1987 x_visit_end_time => l_temp_endtime,
1988 x_visit_end_hour => l_temp_num1,
1989 x_visit_task_times_tbl => l_temp_times_tbl);
1990
1991 IF (l_log_statement >= l_log_current_level) THEN
1992 fnd_log.string
1993 (
1994 fnd_log.level_statement,
1995 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
1996 'After calling Derive_Task_Op_Times'
1997 );
1998 fnd_log.string
1999 (
2000 fnd_log.level_statement,
2001 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2002 'l_temp_times_tbl.count: '|| l_temp_times_tbl.count
2003 );
2004 END IF;
2005
2006
2007 -- yazhou 24Aug2005 starts
2008 -- Resource Leveling Re-design
2009
2010 -- removed the logic to check for mr route ID
2011 --since now that's done inside Derive_Task_Op_Times already
2012
2013 -- Collect the task info for the current visit into the master tasks table
2014 IF (l_temp_times_tbl.COUNT > 0) THEN
2015 l_temp_num1 := l_temp_times_tbl.FIRST;
2016 l_temp_index := l_final_times_tbl.LAST +1;
2017
2018 WHILE l_temp_num1 IS NOT NULL LOOP
2019
2020 -- IF l_temp_times_tbl(l_temp_num1).mr_route_id IS NOT NULL THEN
2021 l_final_times_tbl(l_temp_index) := l_temp_times_tbl(l_temp_num1);
2022 l_temp_index := l_temp_index + 1;
2023
2024 -- END IF;
2025 l_temp_num1 := l_temp_times_tbl.NEXT(l_temp_num1);
2026
2027
2028 END LOOP; -- All valid tasks for current visit
2029 END IF;
2030
2031 -- yazhou 24Aug2005 ends
2032
2033 END LOOP; -- All valid primary plan visits for current dept
2034 CLOSE l_get_primary_visits;
2035 END IF;
2036 END IF;
2037
2038 x_visit_task_times_tbl := l_final_times_tbl;
2039
2040 IF (l_log_statement >= l_log_current_level) THEN
2041 fnd_log.string
2042 (
2043 fnd_log.level_statement,
2044 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2045 'x_visit_task_times_tbl.count: '|| x_visit_task_times_tbl.count
2046 );
2047 END IF;
2048
2049 IF x_visit_task_times_tbl.COUNT >0 THEN
2050 FOR i IN x_visit_task_times_tbl.FIRST..x_visit_task_times_tbl.LAST
2051 LOOP
2052 --
2053 -- Ahl_Debug_Pub.debug('Exiting GET_PLAN_TASKS MR ID:'||x_visit_task_times_tbl(i).mr_route_id);
2054 -- Ahl_Debug_Pub.debug('Exiting GET_PLAN_TASKS VTID:'||x_visit_task_times_tbl(i).visit_task_id);
2055 -- Ahl_Debug_Pub.debug('Exiting GET_PLAN_TASKS STIME:'||x_visit_task_times_tbl(i).task_start_time);
2056 -- Ahl_Debug_Pub.debug('Exiting GET_PLAN_TASKS ETIME:'||x_visit_task_times_tbl(i).task_end_time);
2057 null;
2058 --
2059 END LOOP;
2060 END IF;
2061 --
2062 IF x_tp_dtls_tbl.COUNT >0 THEN
2063 FOR i IN x_tp_dtls_tbl.FIRST..x_tp_dtls_tbl.LAST
2064 LOOP
2065
2066 IF (l_log_statement >= l_log_current_level) THEN
2067 fnd_log.string
2068 (
2069 fnd_log.level_statement,
2070 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2071 'x_tp_dtls_tbl('||i||').visit_id: '|| x_tp_dtls_tbl(i).visit_id
2072 );
2073 fnd_log.string
2074 (
2075 fnd_log.level_statement,
2076 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2077 'x_tp_dtls_tbl('||i||').route_id: '|| x_tp_dtls_tbl(i).route_id
2078 );
2079 fnd_log.string
2080 (
2081 fnd_log.level_statement,
2082 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2083 'x_tp_dtls_tbl('||i||').mr_route_id: '|| x_tp_dtls_tbl(i).mr_route_id
2084 );
2085 fnd_log.string
2086 (
2087 fnd_log.level_statement,
2088 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2089 'x_tp_dtls_tbl('||i||').operation_id: '|| x_tp_dtls_tbl(i).operation_id
2090 );
2091 fnd_log.string
2092 (
2093 fnd_log.level_statement,
2094 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2095 'x_tp_dtls_tbl('||i||').task_id: '|| x_tp_dtls_tbl(i).task_id
2096 );
2097 fnd_log.string
2098 (
2099 fnd_log.level_statement,
2100 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2101 'x_tp_dtls_tbl('||i||').start_time: '|| x_tp_dtls_tbl(i).start_time
2102 );
2103 fnd_log.string
2104 (
2105 fnd_log.level_statement,
2106 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2107 'x_tp_dtls_tbl('||i||').end_time: '|| x_tp_dtls_tbl(i).end_time
2108 );
2109 fnd_log.string
2110 (
2111 fnd_log.level_statement,
2112 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2113 'x_tp_dtls_tbl('||i||').bom_resource_id: '|| x_tp_dtls_tbl(i).bom_resource_id
2114 );
2115 fnd_log.string
2116 (
2117 fnd_log.level_statement,
2118 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2119 'x_tp_dtls_tbl('||i||').department_id: '|| x_tp_dtls_tbl(i).department_id
2120 );
2121 fnd_log.string
2122 (
2123 fnd_log.level_statement,
2124 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2125 'x_tp_dtls_tbl('||i||').quantity: '|| x_tp_dtls_tbl(i).quantity
2126 );
2127
2128 END IF;
2129 END LOOP;
2130 END IF;
2131
2132 --
2133 END Get_Plan_Tasks;
2134
2135 ----------------------------------------
2136 -- Gets the task and operation times for a given visit
2137 -- Also builds the Time Period table if required
2138 ----------------------------------------
2139 -- yazhou 24Aug2005 starts
2140 -- Resource Leveling Re-design
2141
2142 -- The start/end time calulation is no longer required
2143 -- since now task time is calculated and stored in VWP
2144
2145 -- added p_department_id as input parameter
2146 -- and removed default values for start/end time and p_op_times_flag
2147
2148 -- Return only time for tasks with MR route associated
2149
2150 PROCEDURE Derive_Task_Op_Times
2151 (
2152 p_visit_id IN NUMBER,
2153 p_start_time IN DATE,
2154 p_end_time IN DATE,
2155 p_department_id IN NUMBER,
2156 p_x_tp_dtls_tbl IN OUT NOCOPY Time_Period_Details_Tbl_Type,
2157 x_visit_start_time OUT NOCOPY DATE,
2158 x_visit_end_time OUT NOCOPY DATE,
2159 x_visit_end_hour OUT NOCOPY NUMBER,
2160 x_visit_task_times_tbl OUT NOCOPY Visit_Task_Times_Tbl_Type
2161 ) IS
2162
2163 -- Find all the tasks with route associated in the given department
2164
2165 CURSOR l_tasks_csr(p_visit_id IN NUMBER, p_dept_id IN NUMBER) IS
2166 SELECT vt.visit_task_id, mr_route_id,
2167 NVL(start_from_hour, 0) start_from_hour,
2168 NVL(duration, 0) duration,
2169 start_date_time,
2170 end_date_time
2171 FROM ahl_visit_tasks_b vt
2172 WHERE vt.visit_id = p_visit_id AND
2173 status_code = 'PLANNING' AND
2174 (department_id = p_dept_id OR (department_id is NULL AND
2175 p_dept_id = (select department_id from ahl_visits_b
2176 where visit_id = p_visit_id))) AND
2177 mr_route_id is not null
2178 ORDER BY vt.visit_task_id;
2179
2180
2181 -- Added organization_id as select field and removed template_flag
2182
2183 CURSOR l_visit_details_csr(p_visit_id IN NUMBER) IS
2184 SELECT start_date_time,department_id,close_date_time, organization_id
2185 FROM ahl_visits_b
2186 WHERE visit_id = p_visit_id;
2187
2188
2189 -- yazhou 24Aug2005 ends
2190
2191 l_task_times_tbl visit_task_times_Tbl_Type;
2192 l_visit_dtls_rec l_visit_details_csr%ROWTYPE;
2193
2194 l_dept_id NUMBER;
2195 l_temp_num1 NUMBER;
2196 l_index NUMBER;
2197
2198 -- yazhou 24Aug2005 starts
2199 -- Resource Leveling Re-design
2200 l_api_name CONSTANT VARCHAR2(30) := 'Derive_Task_Op_Times';
2201 l_org_id NUMBER;
2202 l_tasks_rec l_tasks_csr%ROWTYPE;
2203 -- yazhou 24Aug2005 ends
2204
2205 BEGIN
2206
2207 IF (l_log_statement >= l_log_current_level) THEN
2208 fnd_log.string
2209 (
2210 fnd_log.level_statement,
2211 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2212 'p_start_time:'||p_start_time
2213 );
2214 fnd_log.string
2215 (
2216 fnd_log.level_statement,
2217 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2218 'p_end_time:'||p_end_time
2219 );
2220 fnd_log.string
2221 (
2222 fnd_log.level_statement,
2223 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2224 'p_department_id:'||p_department_id
2225 );
2226 fnd_log.string
2227 (
2228 fnd_log.level_statement,
2229 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2230 'p_visit_id:'||p_visit_id
2231 );
2232 fnd_log.string
2233 (
2234 fnd_log.level_statement,
2235 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2236 'p_x_tp_dtls_tbl.count:'||p_x_tp_dtls_tbl.count
2237 );
2238
2239 END IF;
2240 -- Get the visit details
2241 OPEN l_visit_details_csr(p_visit_id);
2242
2243 -- yazhou 24Aug2005 starts
2244 -- Resource Leveling Re-design
2245 -- Added organization_id as select field
2246 FETCH l_visit_details_csr INTO x_visit_start_time, l_dept_id,
2247 x_visit_end_time,l_org_id;
2248 -- yazhou 24Aug2005 ends
2249
2250 IF(l_visit_details_csr%NOTFOUND) THEN
2251 Fnd_Message.Set_Name('AHL','AHL_LTP_VISIT_ID_NULL');
2252 Fnd_Msg_Pub.ADD;
2253 CLOSE l_visit_details_csr;
2254 Ahl_Debug_Pub.debug('Invalid visit Id', 'LTP: Derive_Visit_Task_Times');
2255 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2256 END IF;
2257 CLOSE l_visit_details_csr;
2258
2259 -- yazhou 24Aug2005 starts
2260 -- Resource Leveling Re-design
2261
2262 -- x_visit_end_time null check is not required for resource leveling
2263
2264 IF x_visit_end_time IS NOT NULL THEN
2265 x_visit_end_hour := to_number(to_char(x_visit_end_time,'HH24'));
2266 END IF;
2267
2268 -- Get task start/end time from VWP directly
2269 OPEN l_tasks_csr(p_visit_id,p_department_id);
2270 LOOP
2271 FETCH l_tasks_csr INTO l_tasks_rec;
2272 EXIT WHEN l_tasks_csr%NOTFOUND;
2273 l_index := l_tasks_rec.visit_task_id;
2274 l_task_times_tbl(l_index).VISIT_TASK_ID := l_index;
2275 l_task_times_tbl(l_index).MR_ROUTE_ID := l_tasks_rec.mr_route_id;
2276 Get_Route_Duration(l_tasks_rec.mr_route_id, l_task_times_tbl(l_index).TASK_DURATION);
2277 l_task_times_tbl(l_index).TASK_START_TIME := l_tasks_rec.start_date_time;
2278 l_task_times_tbl(l_index).TASK_END_TIME := l_tasks_rec.end_date_time;
2279 l_task_times_tbl(l_index).TASK_START_HOUR := to_number(to_char(l_tasks_rec.start_date_time,'HH24'));
2280 l_task_times_tbl(l_index).TASK_END_HOUR := to_number(to_char(l_tasks_rec.end_date_time,'HH24'));
2281
2282 END LOOP;
2283 CLOSE l_tasks_csr;
2284
2285 -- yazhou: remove the IF condition since template check is no longer required
2286 -- and p_start_time/p_end_time both cannot be null
2287
2288 -- Filter based on time period: Currenlty supporting filtering only if
2289 -- both: start time as well as end time are given
2290
2291 -- IF (l_template_flag = 'Y' OR p_start_time IS NULL OR p_end_time IS NULL) THEN
2292 -- x_visit_task_times_tbl := l_task_times_tbl;
2293 -- ELSE
2294
2295 IF (l_log_statement >= l_log_current_level) THEN
2296 fnd_log.string
2297 (
2298 fnd_log.level_statement,
2299 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2300 'l_task_times_tbl.COUNT:'||l_task_times_tbl.COUNT
2301 );
2302 END IF;
2303
2304 IF l_task_times_tbl.COUNT > 0 THEN
2305
2306 -- To set all the global constants about the Department.
2307 INIT_TIME_VARS(x_visit_start_time, p_department_id);
2308
2309 l_index := l_task_times_tbl.FIRST;
2310 l_temp_num1 := 1;
2311 WHILE l_index IS NOT NULL LOOP
2312
2313 -- Requirement date should be based on operation time if requirements
2314 -- are defined for operaion, so should not only use task time to decide
2315 -- whether Get_Operation_Details should be called for this task.
2316
2317 IF (Periods_Overlap (l_task_times_tbl(l_index).TASK_START_TIME,
2318 l_task_times_tbl(l_index).TASK_END_TIME,
2319 p_start_time,
2320 p_end_time)) THEN
2321 x_visit_task_times_tbl(l_temp_num1) := l_task_times_tbl(l_index);
2322 l_temp_num1 := l_temp_num1 + 1;
2323 END IF;
2324
2325 Get_Operation_Details(p_task_dtls => l_task_times_tbl(l_index),
2326 p_visit_id => p_visit_id,
2327 p_department_id => p_department_id,
2328 p_organization_id => l_org_id,
2329 p_start_time => p_start_time,
2330 p_end_time => p_end_time,
2331 p_x_tp_dtls_tbl => p_x_tp_dtls_tbl);
2332
2333 IF (l_log_statement >= l_log_current_level) THEN
2334 fnd_log.string
2335 (
2336 fnd_log.level_statement,
2337 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2338 'l_index-('||l_index||') After Calling Get_Operation_Details'
2339 );
2340 fnd_log.string
2341 (
2342 fnd_log.level_statement,
2343 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2344 'p_x_tp_dtls_tbl.COUNT:'||p_x_tp_dtls_tbl.COUNT
2345 );
2346 END IF;
2347
2348 l_index := l_task_times_tbl.NEXT(l_index);
2349 END LOOP;
2350 END IF;
2351 -- END IF;
2352
2353 IF (l_log_statement >= l_log_current_level) THEN
2354 fnd_log.string
2355 (
2356 fnd_log.level_statement,
2357 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2358 'l_task_times_tbl.COUNT:'||l_task_times_tbl.COUNT
2359 );
2360 END IF;
2361
2362 -- yazhou 24Aug2005 ends
2363
2364
2365 END Derive_Task_Op_Times;
2366
2367 ----------------------------------------
2368 -- Gets the resources required by the given task
2369 ----------------------------------------
2370 PROCEDURE Get_Task_Resources
2371 (
2372 p_required_capacity IN NUMBER ,
2373 p_task_id IN NUMBER,
2374 p_tstart_date IN DATE,
2375 p_tend_date IN DATE,
2376 p_distinct_flag IN BOOLEAN,
2377 p_x_task_rsrc_tbl IN OUT NOCOPY Plan_Rsrc_Tbl_Type
2378 ) IS
2379
2380 CURSOR l_get_route_csr(p_task_id IN NUMBER) IS
2381 SELECT a.MR_ROUTE_ID,B.route_id
2382 FROM ahl_visit_tasks_b a, ahl_mr_routes_app_v B
2383 WHERE VISIT_TASK_ID = p_task_id
2384 AND a.mr_route_id = B.mr_route_id;
2385
2386 CURSOR l_get_dept_csr(p_task_id IN NUMBER) IS
2387 SELECT vt.DEPARTMENT_ID,v.department_id,v.organization_id
2388 FROM ahl_visits_b v, ahl_visit_tasks_b vt
2389 WHERE vt.VISIT_TASK_ID = p_task_id
2390 AND v.visit_id = vt.visit_id;
2391
2392 CURSOR l_get_rt_resources_csr(c_route_id IN NUMBER) IS
2393 SELECT ASO_RESOURCE_ID
2394 FROM ahl_rt_oper_resources WHERE
2395 ASSOCIATION_TYPE_CODE = G_RT_ASSOC_TYPE
2396 /*LTP CHANGES FOR NONSCHEDULED RESOURCE REQUIREMENTS - sowsubra*/
2397 AND NVL(SCHEDULED_TYPE_ID,1) <> 2
2398 AND OBJECT_ID = c_route_id;
2399
2400 CURSOR l_get_oper_resources_csr(c_route_id IN NUMBER) IS
2401 SELECT ASO_RESOURCE_ID
2402 FROM ahl_rt_oper_resources WHERE
2403 ASSOCIATION_TYPE_CODE = G_OPER_ASSOC_TYPE
2404 /*LTP CHANGES FOR NONSCHEDULED RESOURCE REQUIREMENTS - sowsubra*/
2405 AND NVL(SCHEDULED_TYPE_ID,1) <> 2
2406 AND OBJECT_ID IN (SELECT OPERATION_ID FROM ahl_route_operations WHERE ROUTE_ID = c_route_id);
2407
2408 CURSOR l_get_bom_resources_csr(c_aso_resource_id IN NUMBER,
2409 c_org_id IN NUMBER) IS
2410 SELECT BOM_RESOURCE_ID
2411 FROM ahl_resource_mappings WHERE
2412 ASO_RESOURCE_ID = c_aso_resource_id
2413 AND bom_org_id = c_org_id;
2414
2415 l_mr_route_id NUMBER := NULL;
2416 l_route_id NUMBER := NULL;
2417 l_vdept_id NUMBER := NULL;
2418 l_vtdept_id NUMBER := NULL;
2419 l_dept_id NUMBER;
2420 l_org_id NUMBER;
2421 l_bom_resource_id NUMBER;
2422 l_bom_org_id NUMBER;
2423 l_rt_resource_rec l_get_rt_resources_csr%ROWTYPE;
2424 l_oper_resource_rec l_get_oper_resources_csr%ROWTYPE;
2425 l_qualified BOOLEAN;
2426 l_time_period_details_tbl Time_Period_Details_Tbl_Type;
2427 BEGIN
2428 -- dbms_output.put_line('Entering Get_Task_Resources, p_task_id = ' || p_task_id);
2429 Ahl_Debug_Pub.debug('Entering Get_Task_Resources, p_task_id = ' || p_task_id);
2430 Ahl_Debug_Pub.debug('Get_Task_Resources, p_x_task_rsrc_tbl.count = ' || p_x_task_rsrc_tbl.count);
2431 Ahl_Debug_Pub.debug('Get_Task_Resources, p_x_task_rsrc_tbl.STIME = ' || p_x_task_rsrc_tbl.count);
2432
2433 OPEN l_get_route_csr(p_task_id);
2434 FETCH l_get_route_csr INTO l_mr_route_id,l_route_id;
2435 IF l_get_route_csr%NOTFOUND OR l_route_id IS NULL THEN
2436 Fnd_Message.Set_Name('AHL','AHL_LTP_NO_ROUTE_FOR_TASK');
2437 Fnd_Message.Set_Token('TASK_ID', p_task_id);
2438 Fnd_Msg_Pub.ADD;
2439 CLOSE l_get_route_csr;
2440 RETURN;
2441 END IF;
2442 CLOSE l_get_route_csr;
2443
2444 OPEN l_get_dept_csr(p_task_id);
2445 FETCH l_get_dept_csr INTO l_vtdept_id,l_vdept_id,l_org_id;
2446 IF l_get_dept_csr%NOTFOUND THEN
2447 Fnd_Message.Set_Name('AHL','AHL_LTP_NO_DEPT_FOR_TASK');
2448 Fnd_Message.Set_Token('TASK_ID', p_task_id);
2449 Fnd_Msg_Pub.ADD;
2450 CLOSE l_get_dept_csr;
2451 RETURN;
2452 END IF;
2453 l_dept_id := NVL(l_vtdept_id,l_vdept_id);
2454 CLOSE l_get_dept_csr;
2455
2456 Ahl_Debug_Pub.debug('Get_Task_Resources, route id = ' || l_route_id);
2457 --
2458 OPEN l_get_rt_resources_csr(l_route_id);
2459 FETCH l_get_rt_resources_csr INTO l_rt_resource_rec;
2460 --
2461 IF (l_get_rt_resources_csr%FOUND) THEN
2462 -- Resources assigned at Route Level itself
2463 LOOP
2464 l_qualified := TRUE;
2465 -- Get Route Resource Details
2466 IF (l_qualified) THEN
2467
2468 Ahl_Debug_Pub.debug('Get_Task_Resources, aso resource id = ' || l_rt_resource_rec.ASO_RESOURCE_ID);
2469 -- Get bom resource id for the corresponding aso resource
2470 OPEN l_get_bom_resources_csr(l_rt_resource_rec.ASO_RESOURCE_ID,l_org_id);
2471 FETCH l_get_bom_resources_csr INTO l_bom_resource_id;
2472 IF l_get_bom_resources_csr%NOTFOUND THEN
2473 Fnd_Message.Set_Name('AHL','AHL_LTP_NO_BOM_RESRC_ID');
2474 Fnd_Msg_Pub.ADD;
2475 CLOSE l_get_bom_resources_csr;
2476 RAISE Fnd_Api.G_EXC_ERROR;
2477 -- RETURN;
2478 END IF;
2479 CLOSE l_get_bom_resources_csr;
2480 --
2481 Ahl_Debug_Pub.debug('ROUTE LEVEL, l_bom_resource_id = ' || l_bom_resource_id);
2482 Ahl_Debug_Pub.debug('ROUTE LEVEL, l_bom_org_id = ' || l_org_id);
2483 --
2484 Get_Resource_Details(
2485 p_required_capacity => p_required_capacity,
2486 p_aso_resource_id => l_rt_resource_Rec.ASO_RESOURCE_ID,
2487 p_bom_resource_id => l_bom_resource_id,
2488 p_bom_department_id => l_dept_id,
2489 p_start_date => p_tstart_date,
2490 p_end_date => p_tend_date,
2491 p_x_task_rsrc_tbl => p_x_task_rsrc_tbl
2492 );
2493 Ahl_Debug_Pub.debug('Number of records for resources = ' ||p_x_task_rsrc_tbl.count);
2494 END IF;
2495 FETCH l_get_rt_resources_csr INTO l_rt_resource_rec;
2496 EXIT WHEN l_get_rt_resources_csr%NOTFOUND;
2497 END LOOP;
2498 ELSE
2499
2500 Ahl_Debug_Pub.debug('Get_Task_Resources, operation level route id = ' || l_route_id);
2501
2502 -- Get resources from operation level
2503 OPEN l_get_oper_resources_csr(l_route_id);
2504 LOOP
2505 FETCH l_get_oper_resources_csr INTO l_oper_resource_rec;
2506 EXIT WHEN l_get_oper_resources_csr%NOTFOUND;
2507 l_qualified := TRUE;
2508 -- Get Route Resource Details
2509 IF (l_qualified) THEN
2510
2511 Ahl_Debug_Pub.debug('Get_Task_Resources, aso resource id oper = ' || l_oper_resource_rec.ASO_RESOURCE_ID);
2512 Ahl_Debug_Pub.debug('inside, l_org_id = ' || l_org_id);
2513
2514 -- Get bom resource id for the corresponding aso resource
2515 OPEN l_get_bom_resources_csr(l_oper_resource_rec.ASO_RESOURCE_ID,l_org_id);
2516 FETCH l_get_bom_resources_csr INTO l_bom_resource_id;
2517
2518 Ahl_Debug_Pub.debug('OPERATION LEVEL, bom resource id oper = ' || l_bom_resource_id);
2519
2520 IF l_get_bom_resources_csr%NOTFOUND THEN
2521 Fnd_Message.Set_Name('AHL','AHL_LTP_NO_BOM_RESRC_ID');
2522 Fnd_Msg_Pub.ADD;
2523 CLOSE l_get_bom_resources_csr;
2524 RAISE Fnd_Api.G_EXC_ERROR;
2525 -- RETURN;
2526 END IF;
2527 CLOSE l_get_bom_resources_csr;
2528 --
2529 Get_Resource_Details(
2530 p_required_capacity => p_required_capacity,
2531 p_aso_resource_id => l_rt_resource_Rec.ASO_RESOURCE_ID,
2532 p_bom_resource_id => l_bom_resource_id,
2533 p_bom_department_id => l_dept_id,
2534 p_start_date => p_tstart_date,
2535 p_end_date => p_tend_date,
2536 p_x_task_rsrc_tbl => p_x_task_rsrc_tbl
2537 );
2538 Ahl_Debug_Pub.debug('Number of records for resources = ' ||p_x_task_rsrc_tbl.count);
2539 END IF;
2540 END LOOP;
2541 CLOSE l_get_oper_resources_csr;
2542 END IF;
2543 CLOSE l_get_rt_resources_csr;
2544 -- dbms_output.put_line('Exiting Get_Task_Resources');
2545 END Get_Task_Resources;
2546
2547 ----------------------------------------
2548 -- Gets the details of a resource
2549 ----------------------------------------
2550 PROCEDURE Get_Resource_Details
2551 ( p_required_capacity IN NUMBER,
2552 p_aso_resource_id IN NUMBER,
2553 p_bom_resource_id IN NUMBER,
2554 p_bom_department_id IN NUMBER,
2555 p_start_date IN DATE,
2556 p_end_date IN DATE,
2557 p_x_task_rsrc_tbl IN OUT NOCOPY Plan_Rsrc_Tbl_Type
2558 ) IS
2559
2560 -- yazhou 17Aug2005 starts
2561 -- bug fix #4559462
2562
2563 CURSOR l_get_bom_rsrc_dtls_csr(p_bom_resource_id IN NUMBER,
2564 p_bom_department_id IN NUMBER) IS
2565 SELECT a.RESOURCE_TYPE,a.RESOURCE_CODE,a.DESCRIPTION,
2566 B.CAPACITY_UNITS, M.meaning resource_type_mean
2567 FROM bom_resources A,
2568 bom_department_resources B,
2569 mfg_lookups M
2570 WHERE a.resource_id = B.resource_id
2571 AND B.resource_id = p_bom_resource_id
2572 AND B.department_id = p_bom_department_id
2573 AND A.resource_type = M.lookup_code
2574 AND M.lookup_type = 'BOM_RESOURCE_TYPE';
2575
2576 -- yazhou 17Aug2005 ends
2577
2578 -- Gets shift capacity
2579 CURSOR l_get_shift_dtls_csr(p_bom_resource_id IN NUMBER,
2580 p_bom_department_id IN NUMBER) IS
2581 SELECT SHIFT_NUM,
2582 CAPACITY_UNITS SHIFT_CAPACITY
2583 FROM bom_resource_shifts
2584 WHERE resource_id = p_bom_resource_id
2585 AND department_id = p_bom_department_id;
2586
2587 --
2588 CURSOR l_get_dept_desc_cur (p_bom_department_id IN NUMBER) IS
2589 SELECT department_code,description
2590 FROM bom_departments
2591 WHERE department_id = p_bom_department_id;
2592 --
2593 l_shift_num NUMBER;
2594 l_shift_capacity NUMBER;
2595 l_department_code VARCHAR2(30);
2596 --
2597 l_table_index NUMBER := p_x_task_rsrc_tbl.COUNT + 1; -- 1 based index
2598
2599 BEGIN
2600 Ahl_Debug_Pub.debug('enter get resource details p_bom_resource_id = ' || p_bom_resource_id);
2601 Ahl_Debug_Pub.debug('enter get resource details p_bom_department_id = ' || p_bom_department_id);
2602 Ahl_Debug_Pub.debug('get resource details l_table_index: = ' || l_table_index);
2603
2604 IF (p_bom_resource_id IS NOT NULL) THEN
2605 -- BOM Resource
2606 p_x_task_rsrc_tbl(l_table_index).ASO_BOM_TYPE := G_BOM_RESOURCE;
2607 p_x_task_rsrc_tbl(l_table_index).RESOURCE_ID := p_bom_resource_id;
2608 -- Get the type, name and description for this BOM Resource
2609 OPEN l_get_bom_rsrc_dtls_csr(p_bom_resource_id,p_bom_department_id);
2610
2611 -- yazhou 17Aug2005 starts
2612 -- bug fix #4559462
2613 FETCH l_get_bom_rsrc_dtls_csr INTO p_x_task_rsrc_tbl(l_table_index).RESOURCE_TYPE,
2614 p_x_task_rsrc_tbl(l_table_index).RESOURCE_NAME,
2615 p_x_task_rsrc_tbl(l_table_index).RESOURCE_DESCRIPTION,
2616 p_x_task_rsrc_tbl(l_table_index).CAPACITY_UNITS,
2617 p_x_task_rsrc_tbl(l_table_index).RESOURCE_TYPE_MEANING;
2618 -- yazhou 17Aug2005 ends
2619
2620 IF (l_get_bom_rsrc_dtls_csr%NOTFOUND) THEN
2621 Fnd_Message.Set_Name('AHL','AHL_LTP_BOM_RSRC_ID_INVALID');
2622 Fnd_Message.Set_Token('ASO_RSRC_ID', p_bom_resource_id);
2623 Fnd_Msg_Pub.ADD;
2624 CLOSE l_get_bom_rsrc_dtls_csr;
2625 RAISE Fnd_Api.G_EXC_ERROR;
2626 END IF;
2627 -- Check for shift capacity
2628 OPEN l_get_shift_dtls_csr(p_bom_resource_id,p_bom_department_id);
2629 FETCH l_get_shift_dtls_csr INTO l_shift_num,l_shift_capacity;
2630 CLOSE l_get_shift_dtls_csr;
2631 --
2632 IF l_shift_capacity IS NOT NULL THEN
2633 p_x_task_rsrc_tbl(l_table_index).CAPACITY_UNITS := (p_x_task_rsrc_tbl(l_table_index).CAPACITY_UNITS + l_shift_capacity);
2634 ELSE
2635 p_x_task_rsrc_tbl(l_table_index).CAPACITY_UNITS := p_x_task_rsrc_tbl(l_table_index).CAPACITY_UNITS ;
2636 END IF;
2637
2638 Ahl_Debug_Pub.debug('get resource details p_capacity_units = ' || p_x_task_rsrc_tbl(l_table_index).CAPACITY_UNITS);
2639 p_x_task_rsrc_tbl(l_table_index).RESOURCE_ID := p_aso_resource_id;
2640 --
2641 OPEN l_get_dept_desc_cur(p_bom_department_id);
2642 FETCH l_get_dept_desc_cur INTO l_department_code,
2643 p_x_task_rsrc_tbl(l_table_index).dept_description;
2644 CLOSE l_get_dept_desc_cur;
2645 --
2646 --Assign department id
2647 p_x_task_rsrc_tbl(l_table_index).dept_id := p_bom_department_id;
2648 p_x_task_rsrc_tbl(l_table_index).period_start := p_start_date;
2649 p_x_task_rsrc_tbl(l_table_index).period_end := p_end_date;
2650
2651 --
2652 -- yazhou 17Aug2005 starts
2653 -- bug fix #4559462
2654 /*
2655 --
2656 -- //@@@@@ Resolve Hardcoding
2657 IF (p_x_task_rsrc_tbl(l_table_index).RESOURCE_TYPE = G_MACHINE_RESOURCE) THEN
2658 p_x_task_rsrc_tbl(l_table_index).RESOURCE_TYPE_MEANING := G_MACHINE_RES_NAME;
2659 ELSIF (p_x_task_rsrc_tbl(l_table_index).RESOURCE_TYPE = G_LABOR_RESOURCE) THEN
2660 p_x_task_rsrc_tbl(l_table_index).RESOURCE_TYPE_MEANING := G_LABOR_RES_NAME;
2661 ELSE
2662 -- Unknown Resource Type
2663 NULL;
2664 END IF;
2665 */
2666
2667 CLOSE l_get_bom_rsrc_dtls_csr;
2668 END IF;
2669 END Get_Resource_Details;
2670
2671 ----------------------------------------
2672 -- Gets the details of a resource
2673 ----------------------------------------
2674 PROCEDURE Get_Resource_Details
2675 (
2676 p_aso_resource_id IN NUMBER,
2677 p_x_task_rsrc_tbl IN OUT NOCOPY Plan_Rsrc_Tbl_Type
2678 ) IS
2679
2680 CURSOR l_get_aso_rsrc_dtls_csr(p_rsrc_id IN NUMBER) IS
2681 SELECT NAME, DESCRIPTION FROM ahl_resources WHERE
2682 resource_id = p_rsrc_id;
2683
2684 -- yazhou 17Aug2005 starts
2685 -- bug fix #4559462
2686 CURSOR l_get_bom_rsrc_dtls_csr(p_rsrc_id IN NUMBER) IS
2687 SELECT b.RESOURCE_TYPE, b.RESOURCE_CODE, b.DESCRIPTION, m.meaning
2688 FROM bom_resources b, mfg_lookups m
2689 WHERE b.resource_type = m.lookup_code
2690 AND m.lookup_type = 'BOM_RESOURCE_TYPE'
2691 AND resource_id = p_rsrc_id;
2692 -- yazhou 17Aug2005 ends
2693
2694 l_table_index NUMBER := p_x_task_rsrc_tbl.COUNT + 1; -- 1 based index
2695
2696 BEGIN
2697 IF (p_aso_resource_id IS NOT NULL) THEN
2698 -- BOM Resource
2699 p_x_task_rsrc_tbl(l_table_index).ASO_BOM_TYPE := G_BOM_RESOURCE;
2700 p_x_task_rsrc_tbl(l_table_index).RESOURCE_ID := p_aso_resource_id;
2701 -- Get the type, name and description for this BOM Resource
2702 OPEN l_get_bom_rsrc_dtls_csr(p_aso_resource_id);
2703
2704 -- yazhou 17Aug2005 starts
2705 -- bug fix #4559462
2706 FETCH l_get_bom_rsrc_dtls_csr INTO p_x_task_rsrc_tbl(l_table_index).RESOURCE_TYPE,
2707 p_x_task_rsrc_tbl(l_table_index).RESOURCE_NAME,
2708 p_x_task_rsrc_tbl(l_table_index).RESOURCE_DESCRIPTION,
2709 p_x_task_rsrc_tbl(l_table_index).RESOURCE_TYPE_MEANING;
2710 -- yazhou 17Aug2005 ends
2711
2712 IF (l_get_bom_rsrc_dtls_csr%NOTFOUND) THEN
2713 Fnd_Message.Set_Name('AHL','AHL_LTP_BOM_RSRC_ID_INVALID');
2714 Fnd_Message.Set_Token('ASO_RSRC_ID', p_aso_resource_id);
2715 Fnd_Msg_Pub.ADD;
2716 CLOSE l_get_bom_rsrc_dtls_csr;
2717 RAISE Fnd_Api.G_EXC_ERROR;
2718 END IF;
2719
2720 -- yazhou 17Aug2005 starts
2721 -- bug fix #4559462
2722 /*
2723 -- //@@@@@ Resolve Hardcoding
2724 IF (p_x_task_rsrc_tbl(l_table_index).RESOURCE_TYPE = G_MACHINE_RESOURCE) THEN
2725 p_x_task_rsrc_tbl(l_table_index).RESOURCE_TYPE_MEANING := G_MACHINE_RES_NAME;
2726 ELSIF (p_x_task_rsrc_tbl(l_table_index).RESOURCE_TYPE = G_LABOR_RESOURCE) THEN
2727 p_x_task_rsrc_tbl(l_table_index).RESOURCE_TYPE_MEANING := G_LABOR_RES_NAME;
2728 ELSE
2729 -- Unknown Resource Type
2730 NULL;
2731 END IF;
2732 */
2733
2734 -- yazhou 17Aug2005 ends
2735 CLOSE l_get_bom_rsrc_dtls_csr;
2736 END IF;
2737 END Get_Resource_Details;
2738
2739
2740 ----------------------------------------
2741 -- Populates a Time Period table with a task's operations' start time and end time
2742 -- (If the task's route has operations AND IF ressources are NOT associated AT route LEVEL)
2743 -- or with the task's start time and end time
2744 ----------------------------------------
2745
2746 -- yazhou 24Aug2005 starts
2747 -- Resource Leveling Re-design
2748
2749 -- Return all resource requirement for the given department and period
2750 -- Use requirements defined for the routes if there are any, otherwise use those for operations
2751 -- Added department_id and bom_resource_id in the output table
2752 -- And populate required quantity as well as start/end dates
2753 -- Also change to call AHL_VWP_TIMES_PVT.compute_date to calculate operation dates
2754
2755 PROCEDURE Get_Operation_Details
2756 (
2757 p_task_dtls IN Visit_Task_Times_Rec_Type,
2758 p_visit_id IN NUMBER,
2759 p_department_id IN NUMBER,
2760 p_organization_id IN NUMBER,
2761 p_start_time IN DATE,
2762 p_end_time IN DATE,
2763 p_x_tp_dtls_tbl IN OUT NOCOPY Time_Period_Details_Tbl_Type
2764 ) IS
2765
2766 -- get only operations with latest revision
2767 CURSOR l_get_ops_count(p_route_id IN NUMBER) IS
2768 SELECT COUNT(ro.operation_id)
2769 FROM ahl_route_operations ro, AHL_OPERATIONS_VL OP
2770 WHERE OP.operation_id=RO.operation_id
2771 AND ro.ROUTE_ID = p_route_id
2772 AND OP.revision_number IN
2773 ( SELECT MAX(OP1.revision_number)
2774 FROM AHL_OPERATIONS_B_KFV OP1
2775 WHERE OP1.concatenated_segments=OP.concatenated_segments
2776 AND OP1.revision_status_code='COMPLETE'
2777 AND TRUNC(SYSDATE) BETWEEN TRUNC(OP1.start_date_active) AND
2778 TRUNC(NVL(OP1.end_date_active,SYSDATE+1))
2779 );
2780
2781 CURSOR l_get_rt_rsrc_count(p_route_id IN NUMBER) IS
2782 SELECT COUNT(*) FROM ahl_rt_oper_resources
2783 WHERE OBJECT_ID = p_route_id AND
2784 ASSOCIATION_TYPE_CODE = G_RT_ASSOC_TYPE;
2785
2786 -- get only operations with latest revision
2787 CURSOR l_get_rt_oper_csr(p_route_id IN NUMBER) IS
2788 SELECT RO.OPERATION_ID, RO.STEP
2789 FROM ahl_route_operations ro, AHL_OPERATIONS_VL OP
2790 WHERE OP.operation_id=RO.operation_id
2791 AND ro.ROUTE_ID = p_route_id
2792 AND OP.revision_number IN
2793 ( SELECT MAX(OP1.revision_number)
2794 FROM AHL_OPERATIONS_B_KFV OP1
2795 WHERE OP1.concatenated_segments=OP.concatenated_segments
2796 AND OP1.revision_status_code='COMPLETE'
2797 AND TRUNC(SYSDATE) BETWEEN TRUNC(OP1.start_date_active) AND
2798 TRUNC(NVL(OP1.end_date_active,SYSDATE+1))
2799 )
2800 ORDER BY RO.STEP;
2801
2802 CURSOR l_get_route_id(p_mr_route_id IN NUMBER) IS
2803 SELECT ROUTE_ID FROM ahl_mr_routes_app_v
2804 WHERE MR_ROUTE_ID = p_mr_route_id;
2805
2806 -- get resources associated to route
2807 CURSOR l_rt_resource_dtl_csr (l_route_id IN NUMBER,
2808 l_dept_id IN NUMBER,
2809 l_org_id IN NUMBER) IS
2810 SELECT a.aso_resource_id,
2811 a.quantity,
2812 b.bom_resource_id
2813 FROM ahl_rt_oper_resources a,
2814 ahl_resource_mappings b,
2815 bom_department_resources c
2816 WHERE a.aso_resource_id = b. aso_resource_id
2817 AND a.object_id = l_route_id
2818 AND b.bom_resource_id = c.resource_id
2819 AND b.bom_org_id = l_org_id
2820 AND c.department_id = l_dept_id
2821 /*B6459500 - LTP CHANGES FOR NONSCHEDULED RESOURCE REQUIREMENTS - sowsubra*/
2822 AND nvl(scheduled_type_id,1) <> 2
2823 AND ASSOCIATION_TYPE_CODE = G_RT_ASSOC_TYPE;
2824
2825 -- get resources associated to operations
2826 CURSOR l_oper_resource_dtl_csr (l_operation_id IN NUMBER,
2827 l_dept_id IN NUMBER,
2828 l_org_id IN NUMBER) IS
2829 SELECT a.aso_resource_id,
2830 a.quantity,
2831 b.bom_resource_id
2832 FROM ahl_rt_oper_resources a,
2833 ahl_resource_mappings b,
2834 bom_department_resources c
2835 WHERE a.object_id = l_operation_id
2836 AND a.ASSOCIATION_TYPE_CODE = 'OPERATION'
2837 AND a.aso_resource_id = b. aso_resource_id
2838 AND b.bom_resource_id = c.resource_id
2839 AND c.department_id = l_dept_id
2840 /*B6459500 - LTP CHANGES FOR NONSCHEDULED RESOURCE REQUIREMENTS - sowsubra*/
2841 AND nvl(scheduled_type_id,1) <> 2
2842 AND b.bom_org_id = l_org_id;
2843
2844 l_dept_id NUMBER := NULL;
2845 l_operation_id NUMBER;
2846 l_operation_step NUMBER;
2847 l_table_index NUMBER := p_x_tp_dtls_tbl.COUNT + 1; -- 1 based
2848 l_temp_count NUMBER := 0;
2849 l_temp_duration NUMBER;
2850
2851 l_route_id NUMBER;
2852 l_api_name CONSTANT VARCHAR2(30) := 'Get_Operation_Details';
2853
2854 l_rt_resource_dtl_rec l_rt_resource_dtl_csr%rowtype;
2855 l_oper_resource_dtl_rec l_oper_resource_dtl_csr%rowtype;
2856 l_oper_START_TIME DATE;
2857 l_oper_END_TIME DATE;
2858
2859 BEGIN
2860
2861 IF (l_log_statement >= l_log_current_level) THEN
2862 fnd_log.string
2863 (
2864 fnd_log.level_statement,
2865 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2866 'p_start_time:'||p_start_time
2867 );
2868 fnd_log.string
2869 (
2870 fnd_log.level_statement,
2871 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2872 'p_end_time:'||p_end_time
2873 );
2874 fnd_log.string
2875 (
2876 fnd_log.level_statement,
2877 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2878 'p_department_id:'||p_department_id
2879 );
2880 fnd_log.string
2881 (
2882 fnd_log.level_statement,
2883 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2884 'p_visit_id:'||p_visit_id
2885 );
2886 fnd_log.string
2887 (
2888 fnd_log.level_statement,
2889 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2890 'p_organization_id:'||p_organization_id
2891 );
2892 fnd_log.string
2893 (
2894 fnd_log.level_statement,
2895 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2896 'p_task_dtls.MR_ROUTE_ID:'||p_task_dtls.MR_ROUTE_ID
2897 );
2898 fnd_log.string
2899 (
2900 fnd_log.level_statement,
2901 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2902 'p_task_dtls.VISIT_TASK_ID:'||p_task_dtls.VISIT_TASK_ID
2903 );
2904 END IF;
2905
2906 IF (p_task_dtls.MR_ROUTE_ID IS NULL) THEN
2907 -- no need to include this task
2908 RETURN;
2909 END IF;
2910
2911 -- Find the route ID with MR_route_id associated to task
2912
2913 OPEN l_get_route_id(p_task_dtls.MR_ROUTE_ID);
2914 FETCH l_get_route_id INTO l_route_id;
2915 CLOSE l_get_route_id;
2916
2917 IF (l_log_statement >= l_log_current_level) THEN
2918 fnd_log.string
2919 (
2920 fnd_log.level_statement,
2921 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2922 'l_route_id:'||l_route_id
2923 );
2924 END IF;
2925
2926 -- Check to see if route has any resource requirements defined
2927 OPEN l_get_rt_rsrc_count(l_route_id);
2928 FETCH l_get_rt_rsrc_count INTO l_temp_count;
2929 CLOSE l_get_rt_rsrc_count;
2930
2931 IF (l_log_statement >= l_log_current_level) THEN
2932 fnd_log.string
2933 (
2934 fnd_log.level_statement,
2935 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2936 'number of resources associated to route:'||l_temp_count
2937 );
2938 END IF;
2939
2940 IF l_temp_count >0 THEN
2941
2942 -- Add resource requirements to output table only if task start/end date
2943 -- overlaps with the given period
2944
2945 IF (Periods_Overlap (p_task_dtls.TASK_START_TIME,p_task_dtls.TASK_END_TIME,
2946 p_start_time, p_end_time)) THEN
2947
2948 -- There are resource requirements defined for the route
2949 OPEN l_rt_resource_dtl_csr(l_route_id,p_department_id,p_organization_id);
2950 LOOP
2951 FETCH l_rt_resource_dtl_csr INTO l_rt_resource_dtl_rec;
2952 EXIT WHEN l_rt_resource_dtl_csr%NOTFOUND;
2953
2954 p_x_tp_dtls_tbl(l_table_index).VISIT_ID := p_visit_id;
2955 p_x_tp_dtls_tbl(l_table_index).TASK_ID := p_task_dtls.VISIT_TASK_ID;
2956 p_x_tp_dtls_tbl(l_table_index).ROUTE_ID := l_route_id;
2957 p_x_tp_dtls_tbl(l_table_index).MR_ROUTE_ID := p_task_dtls.MR_ROUTE_ID;
2958 p_x_tp_dtls_tbl(l_table_index).OPERATION_ID := NULL;
2959 p_x_tp_dtls_tbl(l_table_index).STEP := NULL;
2960 p_x_tp_dtls_tbl(l_table_index).START_HOUR := p_task_dtls.TASK_START_HOUR;
2961 p_x_tp_dtls_tbl(l_table_index).END_HOUR := p_task_dtls.TASK_END_HOUR;
2962 p_x_tp_dtls_tbl(l_table_index).MAX_DURATION := p_task_dtls.TASK_DURATION;
2963 p_x_tp_dtls_tbl(l_table_index).START_TIME := p_task_dtls.TASK_START_TIME;
2964 p_x_tp_dtls_tbl(l_table_index).END_TIME := p_task_dtls.TASK_END_TIME;
2965 p_x_tp_dtls_tbl(l_table_index).QUANTITY := l_rt_resource_dtl_rec.quantity;
2966 p_x_tp_dtls_tbl(l_table_index).ASO_RESOURCE_ID := l_rt_resource_dtl_rec.ASO_RESOURCE_ID;
2967 p_x_tp_dtls_tbl(l_table_index).BOM_RESOURCE_ID := l_rt_resource_dtl_rec.BOM_RESOURCE_ID;
2968 p_x_tp_dtls_tbl(l_table_index).DEPARTMENT_ID := p_department_id;
2969
2970 IF (l_log_statement >= l_log_current_level) THEN
2971 fnd_log.string
2972 (
2973 fnd_log.level_statement,
2974 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2975 'p_x_tp_dtls_tbl('||l_table_index||').start_time: '|| TO_CHAR(p_x_tp_dtls_tbl(l_table_index).start_time, 'DD-MON-YYYY hh24:mi')
2976 );
2977 fnd_log.string
2978 (
2979 fnd_log.level_statement,
2980 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2981 'p_x_tp_dtls_tbl('||l_table_index||').end_time: '|| TO_CHAR( p_x_tp_dtls_tbl(l_table_index).end_time, 'DD-MON-YYYY hh24:mi')
2982 );
2983 fnd_log.string
2984 (
2985 fnd_log.level_statement,
2986 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2987 'p_x_tp_dtls_tbl('||l_table_index||').bom_resource_id: '|| p_x_tp_dtls_tbl(l_table_index).bom_resource_id
2988 );
2989 fnd_log.string
2990 (
2991 fnd_log.level_statement,
2992 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
2993 'p_x_tp_dtls_tbl('||l_table_index||').quantity: '|| p_x_tp_dtls_tbl(l_table_index).quantity
2994 );
2995
2996 END IF;
2997
2998 l_table_index := l_table_index + 1;
2999
3000 END LOOP;-- loop through all resources
3001 CLOSE l_rt_resource_dtl_csr;
3002 END IF; -- period overlap
3003
3004 ELSE -- Check for operation resources
3005
3006 -- Check if route has any operations
3007 OPEN l_get_ops_count(l_route_id);
3008 FETCH l_get_ops_count INTO l_temp_count;
3009 CLOSE l_get_ops_count;
3010
3011 IF (l_log_statement >= l_log_current_level) THEN
3012 fnd_log.string
3013 (
3014 fnd_log.level_statement,
3015 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3016 'number of operations associated to route:'||l_temp_count
3017 );
3018 END IF;
3019
3020 IF (l_temp_count = 0) THEN
3021 -- No operations
3022 RETURN;
3023 END IF;
3024
3025 -- l_next_start_hour := p_task_dtls.TASK_START_HOUR; -- Start hour of first operation w.r.t visit
3026 l_oper_END_TIME :=p_task_dtls.TASK_START_TIME;
3027
3028 IF (l_log_statement >= l_log_current_level) THEN
3029 fnd_log.string
3030 (
3031 fnd_log.level_statement,
3032 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3033 'p_task_dtls.TASK_START_TIME:'||TO_CHAR(p_task_dtls.TASK_START_TIME, 'DD-MON-YYYY hh24:mi')
3034 );
3035 END IF;
3036
3037 OPEN l_get_rt_oper_csr(l_route_id);
3038 LOOP
3039 FETCH l_get_rt_oper_csr INTO l_operation_id, l_operation_step;
3040 EXIT WHEN l_get_rt_oper_csr%NOTFOUND;
3041
3042 -- Ahl_Debug_Pub.debug('l_operation_id:'||l_operation_id);
3043 l_temp_duration := 0;
3044
3045 IF (l_log_statement >= l_log_current_level) THEN
3046 fnd_log.string
3047 (
3048 fnd_log.level_statement,
3049 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3050 'l_operation_id:'||l_operation_id
3051 );
3052 fnd_log.string
3053 (
3054 fnd_log.level_statement,
3055 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3056 'l_operation_step:'||l_operation_step
3057 );
3058 END IF;
3059
3060 -- Get the duration of the operation
3061 Get_Oper_Max_Duration(l_operation_id, l_temp_duration);
3062
3063 l_oper_START_TIME := l_oper_END_TIME;
3064 l_oper_END_TIME := AHL_VWP_TIMES_PVT.COMPUTE_DATE(l_oper_START_TIME,p_department_id,l_temp_duration);
3065
3066 IF (l_log_statement >= l_log_current_level) THEN
3067 fnd_log.string
3068 (
3069 fnd_log.level_statement,
3070 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3071 'operation duration:'||l_temp_duration
3072 );
3073 fnd_log.string
3074 (
3075 fnd_log.level_statement,
3076 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3077 'l_oper_START_TIME:'||TO_CHAR(l_oper_START_TIME, 'DD-MON-YYYY hh24:mi')
3078 );
3079 fnd_log.string
3080 (
3081 fnd_log.level_statement,
3082 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3083 'l_oper_END_TIME:'||TO_CHAR(l_oper_END_TIME, 'DD-MON-YYYY hh24:mi')
3084 );
3085 END IF;
3086
3087 -- Add resource requirements to output table only if operation start/end date
3088 -- overlaps with the given period
3089
3090 IF (Periods_Overlap(l_oper_START_TIME,l_oper_END_TIME,p_start_time, p_end_time)) THEN
3091
3092 OPEN l_oper_resource_dtl_csr(l_operation_id,p_department_id,p_organization_id);
3093 LOOP
3094 FETCH l_oper_resource_dtl_csr INTO l_oper_resource_dtl_rec;
3095 EXIT WHEN l_oper_resource_dtl_csr%NOTFOUND;
3096
3097 p_x_tp_dtls_tbl(l_table_index).VISIT_ID := p_visit_id;
3098 p_x_tp_dtls_tbl(l_table_index).TASK_ID := p_task_dtls.VISIT_TASK_ID;
3099 p_x_tp_dtls_tbl(l_table_index).ROUTE_ID := l_route_id;
3100 p_x_tp_dtls_tbl(l_table_index).MR_ROUTE_ID := p_task_dtls.MR_ROUTE_ID;
3101 p_x_tp_dtls_tbl(l_table_index).OPERATION_ID := l_operation_id;
3102 p_x_tp_dtls_tbl(l_table_index).STEP := l_operation_step;
3103
3104 p_x_tp_dtls_tbl(l_table_index).START_HOUR := to_number(to_char(l_oper_START_TIME,'HH24'));
3105 p_x_tp_dtls_tbl(l_table_index).MAX_DURATION := l_temp_duration;
3106 p_x_tp_dtls_tbl(l_table_index).END_HOUR := to_number(to_char(l_oper_END_TIME,'HH24'));
3107 p_x_tp_dtls_tbl(l_table_index).START_TIME := l_oper_START_TIME;
3108 p_x_tp_dtls_tbl(l_table_index).END_TIME := l_oper_END_TIME;
3109
3110 p_x_tp_dtls_tbl(l_table_index).QUANTITY := l_oper_resource_dtl_rec.quantity;
3111 p_x_tp_dtls_tbl(l_table_index).ASO_RESOURCE_ID := l_oper_resource_dtl_rec.ASO_RESOURCE_ID;
3112 p_x_tp_dtls_tbl(l_table_index).BOM_RESOURCE_ID := l_oper_resource_dtl_rec.BOM_RESOURCE_ID;
3113 p_x_tp_dtls_tbl(l_table_index).DEPARTMENT_ID := p_department_id;
3114
3115 IF (l_log_statement >= l_log_current_level) THEN
3116 fnd_log.string
3117 (
3118 fnd_log.level_statement,
3119 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3120 'p_x_tp_dtls_tbl('||l_table_index||').OPERATION_ID: '|| p_x_tp_dtls_tbl(l_table_index).OPERATION_ID
3121 );
3122 fnd_log.string
3123 (
3124 fnd_log.level_statement,
3125 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3126 'p_x_tp_dtls_tbl('||l_table_index||').start_time: '|| TO_CHAR(p_x_tp_dtls_tbl(l_table_index).start_time, 'DD-MON-YYYY hh24:mi')
3127 );
3128 fnd_log.string
3129 (
3130 fnd_log.level_statement,
3131 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3132 'p_x_tp_dtls_tbl('||l_table_index||').end_time: '|| TO_CHAR( p_x_tp_dtls_tbl(l_table_index).end_time, 'DD-MON-YYYY hh24:mi')
3133 );
3134 fnd_log.string
3135 (
3136 fnd_log.level_statement,
3137 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3138 'p_x_tp_dtls_tbl('||l_table_index||').bom_resource_id: '|| p_x_tp_dtls_tbl(l_table_index).bom_resource_id
3139 );
3140 fnd_log.string
3141 (
3142 fnd_log.level_statement,
3143 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3144 'p_x_tp_dtls_tbl('||l_table_index||').quantity: '|| p_x_tp_dtls_tbl(l_table_index).quantity
3145 );
3146 END IF;
3147
3148 l_table_index := l_table_index + 1;
3149
3150 END LOOP; -- Loop through operation resources
3151 CLOSE l_oper_resource_dtl_csr;
3152
3153 END IF; -- period overlap
3154
3155 END LOOP; --loop through all operations
3156 CLOSE l_get_rt_oper_csr;
3157
3158 END IF; -- l_temp_count >0 (route resource number >0)
3159
3160 END Get_Operation_Details;
3161 -- yazhou 24Aug2005 ends
3162
3163 -- yazhou 24Aug2005 starts
3164 -- Resource Leveling Re-design
3165
3166 ----------------------------------------
3167 -- Filters out requirements that don't require the given resource for the given period
3168 ----------------------------------------
3169 PROCEDURE Filter_By_Resource
3170 (
3171 p_resource_id IN NUMBER,
3172 p_start_time IN DATE,
3173 p_end_time IN DATE,
3174 p_x_tp_dtls_tbl IN OUT NOCOPY Time_Period_Details_Tbl_Type
3175 ) IS
3176
3177 l_temp_table Time_Period_Details_Tbl_Type;
3178 l_table_index NUMBER;
3179
3180 l_api_name CONSTANT VARCHAR2(30) := 'Filter_By_Resource';
3181
3182 BEGIN
3183
3184 IF (p_resource_id IS NULL) THEN
3185 -- Error
3186 FND_MESSAGE.Set_Name('AHL','AHL_LTP_RSRC_ID_NULL');
3187 FND_MSG_PUB.ADD;
3188 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3189 END IF;
3190
3191 IF (l_log_statement >= l_log_current_level) THEN
3192 fnd_log.string
3193 (
3194 fnd_log.level_statement,
3195 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3196 'p_resource_id: '|| p_resource_id
3197 );
3198 fnd_log.string
3199 (
3200 fnd_log.level_statement,
3201 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3202 'p_start_time: '|| TO_CHAR( p_start_time, 'DD-MON-YYYY hh24:mi')
3203 );
3204 fnd_log.string
3205 (
3206 fnd_log.level_statement,
3207 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3208 'p_end_time: '|| TO_CHAR( p_end_time, 'DD-MON-YYYY hh24:mi')
3209 );
3210
3211 END IF;
3212
3213 IF (p_x_tp_dtls_tbl.COUNT > 0) THEN
3214
3215 l_table_index := 0;
3216
3217 FOR i in p_x_tp_dtls_tbl.FIRST .. p_x_tp_dtls_tbl.LAST LOOP
3218
3219 -- keep the requirement only if it's for the given resource
3220 -- and falls in the given period
3221 IF p_x_tp_dtls_tbl(i).bom_resource_id = p_resource_id AND
3222 (Periods_Overlap (p_x_tp_dtls_tbl(i).START_TIME,p_x_tp_dtls_tbl(i).END_TIME,
3223 p_start_time, p_end_time)) THEN
3224
3225 l_temp_table(l_table_index) := p_x_tp_dtls_tbl(i);
3226
3227 l_table_index := l_table_index + 1;
3228
3229 END IF; -- l_rsrc_exist_cur%FOUND
3230
3231 END LOOP;
3232 END IF;
3233
3234 -- Assign Local table to Output Parameter
3235 p_x_tp_dtls_tbl := l_temp_table;
3236
3237 IF p_x_tp_dtls_tbl.COUNT >0 THEN
3238 FOR i IN p_x_tp_dtls_tbl.FIRST..p_x_tp_dtls_tbl.LAST
3239 LOOP
3240
3241 IF (l_log_statement >= l_log_current_level) THEN
3242 fnd_log.string
3243 (
3244 fnd_log.level_statement,
3245 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3246 'p_x_tp_dtls_tbl('||i||').visit_id: '|| p_x_tp_dtls_tbl(i).visit_id
3247 );
3248 fnd_log.string
3249 (
3250 fnd_log.level_statement,
3251 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3252 'p_x_tp_dtls_tbl('||i||').route_id: '|| p_x_tp_dtls_tbl(i).route_id
3253 );
3254 fnd_log.string
3255 (
3256 fnd_log.level_statement,
3257 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3258 'p_x_tp_dtls_tbl('||i||').mr_route_id: '|| p_x_tp_dtls_tbl(i).mr_route_id
3259 );
3260 fnd_log.string
3261 (
3262 fnd_log.level_statement,
3263 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3264 'p_x_tp_dtls_tbl('||i||').operation_id: '|| p_x_tp_dtls_tbl(i).operation_id
3265 );
3266 fnd_log.string
3267 (
3268 fnd_log.level_statement,
3269 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3270 'p_x_tp_dtls_tbl('||i||').task_id: '|| p_x_tp_dtls_tbl(i).task_id
3271 );
3272 fnd_log.string
3273 (
3274 fnd_log.level_statement,
3275 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3276 'p_x_tp_dtls_tbl('||i||').start_time: '|| p_x_tp_dtls_tbl(i).start_time
3277 );
3278 fnd_log.string
3279 (
3280 fnd_log.level_statement,
3281 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3282 'p_x_tp_dtls_tbl('||i||').end_time: '|| p_x_tp_dtls_tbl(i).end_time
3283 );
3284
3285 fnd_log.string
3286 (
3287 fnd_log.level_statement,
3288 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3289 'p_x_tp_dtls_tbl('||i||').quantity: '|| p_x_tp_dtls_tbl(i).quantity
3290 );
3291
3292 END IF;
3293 END LOOP;
3294 END IF;
3295
3296
3297 END Filter_By_Resource;
3298
3299 -- yazhou 24Aug2005 ends
3300
3301
3302 --
3303 /* Commented by rnahata since its not being used
3304 PROCEDURE get_qty_By_Resource
3305 (
3306 p_resource_id IN NUMBER,
3307 p_aso_bom_type IN VARCHAR2,
3308 p_x_tp_dtls_tbl IN OUT NOCOPY Time_Period_Details_Tbl_Type
3309 ) IS
3310 CURSOR l_aso_rsrc_csr(p_aso_rsrc_id IN NUMBER,
3311 p_object_id IN NUMBER,
3312 p_object_type IN VARCHAR2) IS
3313 SELECT QUANTITY FROM ahl_rt_oper_resources WHERE
3314 ASO_RESOURCE_ID = p_aso_rsrc_id AND
3315 OBJECT_ID = p_object_id AND
3316 ASSOCIATION_TYPE_CODE = p_object_type;
3317
3318
3319 l_temp_table Time_Period_Details_Tbl_Type;
3320 l_table_index NUMBER := 1;
3321 l_quantity NUMBER;
3322
3323 BEGIN
3324 IF (p_resource_id IS NULL) THEN
3325 -- Error
3326 Fnd_Message.Set_Name('AHL','AHL_LTP_RSRC_ID_NULL');
3327 Fnd_Msg_Pub.ADD;
3328 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3329 END IF;
3330
3331 IF (p_aso_bom_type = G_ASO_RESOURCE) THEN
3332 -- ASO Resource
3333 IF (p_x_tp_dtls_tbl.COUNT > 0) THEN
3334 FOR i IN p_x_tp_dtls_tbl.FIRST .. p_x_tp_dtls_tbl.LAST LOOP
3335 l_quantity := 0;
3336 IF (p_x_tp_dtls_tbl(i).OPERATION_ID IS NULL) THEN
3337 -- Route
3338 OPEN l_aso_rsrc_csr(p_resource_id, p_x_tp_dtls_tbl(i).ROUTE_ID, G_RT_ASSOC_TYPE);
3339 ELSE
3340 -- Operation
3341 OPEN l_aso_rsrc_csr(p_resource_id, p_x_tp_dtls_tbl(i).OPERATION_ID, G_OPER_ASSOC_TYPE);
3342 END IF;
3343 FETCH l_aso_rsrc_csr INTO l_quantity;
3344 IF (l_aso_rsrc_csr%FOUND AND l_quantity > 0) THEN
3345 -- Resource used by this Route/Operation: Add to output table
3346 l_temp_table(l_table_index) := p_x_tp_dtls_tbl(i);
3347 l_temp_table(l_table_index).QUANTITY := l_quantity;
3348 l_table_index := l_table_index + 1;
3349 END IF;
3350 CLOSE l_aso_rsrc_csr;
3351 END LOOP;
3352 END IF;
3353 ELSE
3354 -- Error
3355 Fnd_Message.Set_Name('AHL','AHL_LTP_RSRC_TYPE_INVALID');
3356 Fnd_Message.Set_Token('RSRC_TYPE', p_aso_bom_type);
3357 Fnd_Msg_Pub.ADD;
3358 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3359 END IF;
3360 -- Assign Local table to Output Parameter
3361 p_x_tp_dtls_tbl := l_temp_table;
3362 END Get_qty_By_Resource;
3363 */
3364
3365 ----------------------------------------
3366 -- Creates Timeperiods from the start time and end time based on the UOM
3367 -- and populates the table
3368 -- //@@@@@ Formatting hardcoding to be removed
3369 ----------------------------------------
3370 PROCEDURE Create_Time_Periods
3371 (
3372 p_start_time IN DATE,
3373 p_end_time IN DATE,
3374 p_UOM_code IN VARCHAR2,
3375 p_org_id IN NUMBER,
3376 p_dept_id IN NUMBER,
3377 x_per_rsrc_tbl OUT NOCOPY Period_Rsrc_Req_Tbl_Type
3378 ) IS
3379
3380 CURSOR l_dept_shift_csr(p_department_id IN NUMBER) IS
3381 SELECT calendar_code, shift_num FROM ahl_department_shifts
3382 WHERE department_id = p_department_id;
3383
3384 CURSOR l_shift_times_csr(p_calendar_code IN VARCHAR2, p_shift_num IN NUMBER) IS
3385 SELECT FROM_TIME, TO_TIME FROM bom_shift_times
3386 WHERE CALENDAR_CODE = p_calendar_code AND
3387 SHIFT_NUM = p_shift_num;
3388
3389 CURSOR l_workday_pattern_csr(p_calendar_code IN VARCHAR2, p_shift_num IN NUMBER) IS
3390 SELECT DAYS_ON, DAYS_OFF FROM bom_workday_patterns
3391 WHERE CALENDAR_CODE = p_calendar_code AND
3392 SHIFT_NUM = p_shift_num;
3393
3394 CURSOR l_calendar_csr(p_calendar_code IN VARCHAR2) IS
3395 SELECT CALENDAR_START_DATE, CALENDAR_END_DATE FROM bom_calendars
3396 WHERE CALENDAR_CODE = p_calendar_code;
3397
3398 CURSOR l_dept_name_csr (c_org_id IN NUMBER,
3399 c_dept_id IN NUMBER)
3400 IS
3401 SELECT description FROM bom_departments
3402 WHERE organization_id = c_org_id
3403 AND department_id = c_dept_id;
3404
3405 l_api_name CONSTANT VARCHAR2(30) := 'Create_Time_Periods';
3406
3407 l_temp_start DATE;
3408 l_temp_end DATE;
3409 l_temp_index NUMBER := 1;
3410 l_temp_num NUMBER;
3411 l_check_date BOOLEAN := TRUE;
3412 l_working_day BOOLEAN;
3413 l_dept_name VARCHAR2(80);
3414
3415 L_CALENDAR_CODE VARCHAR2(10);
3416 L_SHIFT_NUM NUMBER;
3417 L_CAL_START DATE;
3418 L_CAL_END DATE;
3419 L_SHIFT_START NUMBER;
3420 L_SHIFT_END NUMBER;
3421 L_DAYS_ON NUMBER;
3422 L_DAYS_OFF NUMBER;
3423
3424 BEGIN
3425
3426 IF (l_log_statement >= l_log_current_level) THEN
3427 fnd_log.string
3428 (
3429 fnd_log.level_statement,
3430 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3431 'p_start_time:'||TO_CHAR( p_start_time, 'DD-MON-YYYY hh24:mi')
3432 );
3433 fnd_log.string
3434 (
3435 fnd_log.level_statement,
3436 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3437 'p_end_time:'||TO_CHAR( p_end_time, 'DD-MON-YYYY hh24:mi')
3438 );
3439 END IF;
3440
3441 -- First, Get all Calendar/Shift Values
3442 OPEN l_dept_name_csr(p_org_id,p_dept_id);
3443 FETCH l_dept_name_csr INTO l_dept_name;
3444 CLOSE l_dept_name_csr;
3445 --
3446 -- Get the Calendar code and Shift Num for the department
3447 OPEN l_dept_shift_csr(p_dept_id);
3448 FETCH l_dept_shift_csr INTO L_CALENDAR_CODE, L_SHIFT_NUM;
3449 IF (l_dept_shift_csr%NOTFOUND) THEN
3450 Fnd_Message.Set_Name('AHL','AHL_LTP_NO_SHIFT_FOR_DEPT');
3451 Fnd_Message.Set_Token('DEPT_ID', l_dept_name);
3452 Fnd_Msg_Pub.ADD;
3453 CLOSE l_dept_shift_csr;
3454 Ahl_Debug_Pub.debug('No shift/calendar code for department: ' || p_dept_id, 'LTP: Create_Time_Periods');
3455 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3456 END IF;
3457 CLOSE l_dept_shift_csr;
3458 -- dbms_output.put_line('L_CALENDAR_CODE := ' || L_CALENDAR_CODE);
3459 -- dbms_output.put_line('L_SHIFT_NUM := ' || L_SHIFT_NUM);
3460
3461 -- Get the calendar start date and the calendar end date
3462 OPEN l_calendar_csr(L_CALENDAR_CODE);
3463 FETCH l_calendar_csr INTO L_CAL_START, L_CAL_END;
3464 IF (l_calendar_csr%NOTFOUND) THEN
3465 Fnd_Message.Set_Name('AHL','AHL_LTP_CAL_CODE_INVALID');
3466 Fnd_Message.Set_Token('CAL_CODE', L_CALENDAR_CODE);
3467 Fnd_Msg_Pub.ADD;
3468 CLOSE l_calendar_csr;
3469 Ahl_Debug_Pub.debug('No BOM_CALENDARS entry for calendar code: ' || L_CALENDAR_CODE, 'LTP: Create_Time_Periods');
3470 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3471 END IF;
3472 CLOSE l_calendar_csr;
3473 -- Truncate the date values
3474 L_CAL_START := TRUNC(L_CAL_START);
3475 L_CAL_END := TRUNC(L_CAL_END);
3476 -- dbms_output.put_line('L_CAL_START := ' || L_CAL_START);
3477 -- dbms_output.put_line('L_CAL_END := ' || L_CAL_END);
3478
3479 -- Get Days On and Days Off
3480 OPEN l_workday_pattern_csr(L_CALENDAR_CODE, L_SHIFT_NUM);
3481 FETCH l_workday_pattern_csr INTO L_DAYS_ON, L_DAYS_OFF;
3482 IF (l_workday_pattern_csr%NOTFOUND) THEN
3483 Fnd_Message.Set_Name('AHL','AHL_LTP_NO_PTRN_FOR_DEPT');
3484 Fnd_Message.Set_Token('DEPT_ID', l_dept_name);
3485 Fnd_Msg_Pub.ADD;
3486 CLOSE l_workday_pattern_csr;
3487 Ahl_Debug_Pub.debug('No Work Day Pattern for department: ' || p_dept_id, 'LTP: Create_Time_Periods');
3488 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3489 END IF;
3490 CLOSE l_workday_pattern_csr;
3491
3492 -- Start processing based on UOM code
3493 IF (p_UOM_code = G_UOM_HOUR) THEN
3494 -- dbms_output.put_line('Hour UOM');
3495 -- Get the shift start and shift end times
3496 OPEN l_shift_times_csr(L_CALENDAR_CODE, L_SHIFT_NUM);
3497 FETCH l_shift_times_csr INTO L_SHIFT_START, L_SHIFT_END;
3498 IF (l_shift_times_csr%NOTFOUND) THEN
3499 Fnd_Message.Set_Name('AHL','AHL_LTP_NO_SHIFT_FOR_DEPT');
3500 Fnd_Message.Set_Token('DEPT_ID', l_dept_name);
3501 Fnd_Msg_Pub.ADD;
3502 CLOSE l_shift_times_csr;
3503 Ahl_Debug_Pub.debug('No shift start and end times for department: ' || p_dept_id, 'LTP: Create_Time_Periods');
3504 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3505 END IF;
3506 CLOSE l_shift_times_csr;
3507 -- dbms_output.put_line('L_SHIFT_START := ' || L_SHIFT_START);
3508 -- dbms_output.put_line('L_SHIFT_END := ' || L_SHIFT_END);
3509
3510 -- Break up into hours
3511 l_temp_start := p_start_time;
3512 l_temp_end := p_end_time;
3513 WHILE (l_temp_start < l_temp_end) LOOP
3514 -- dbms_output.put_line('l_temp_start = ' || l_temp_start);
3515 IF (l_check_date) THEN
3516 l_working_day := FALSE;
3517 l_temp_num := MOD((TRUNC(l_temp_start) - L_CAL_START), (L_DAYS_ON + L_DAYS_OFF)) + 1;
3518 IF (l_temp_num <= L_DAYS_ON) THEN
3519 -- dbms_output.put_line('Not Day Off');
3520 -- Not Day Off: Check if Holiday
3521 IF(Not_A_Holiday(l_temp_start, p_dept_id)) THEN
3522 -- dbms_output.put_line('Not a holiday');
3523 -- Working Day: Check if day is in calendar range
3524 IF(l_temp_start > L_CAL_END) THEN
3525 Fnd_Message.Set_Name('AHL','AHL_LTP_INSUFFICIENT_CAL_RANGE');
3526 Fnd_Msg_Pub.ADD;
3527 Ahl_Debug_Pub.debug('Computed date (' || l_temp_start|| ') is outside calendar range', 'LTP: Init_Time_Vars');
3528 RAISE Fnd_Api.G_EXC_ERROR;
3529 END IF;
3530 l_working_day := TRUE;
3531 END IF; -- Not a holiday
3532 END IF; -- Not Day Off
3533 END IF;
3534 IF (l_working_day) THEN
3535 -- Check if hour is valid
3536 l_temp_num := (l_temp_start - TRUNC(l_temp_start)) * G_SECS_IN_DAY;
3537 IF ((L_SHIFT_START < L_SHIFT_END AND l_temp_num >= L_SHIFT_START AND l_temp_num < L_SHIFT_END) OR
3538 (L_SHIFT_START > L_SHIFT_END AND (l_temp_num >= L_SHIFT_START OR l_temp_num < L_SHIFT_END))) THEN
3539 -- dbms_output.put_line('In Shift');
3540 x_per_rsrc_tbl(l_temp_index).PERIOD_START := l_temp_start;
3541 -- //@@@@@ Remove Hardcoding
3542 x_per_rsrc_tbl(l_temp_index).PERIOD_STRING := TO_CHAR(l_temp_start, 'DD-MM-YYYY HH:MI:SS AM');
3543 x_per_rsrc_tbl(l_temp_index).PERIOD_END := l_temp_start + (1/24); -- 1 Hour Long
3544 IF ((l_temp_num + G_SECS_IN_HOUR) = L_SHIFT_END) THEN
3545 l_check_date := TRUE; -- Last Hour of Shift: Check if next day is a working Day
3546 ELSE
3547 l_check_date := FALSE; -- Not Last Hour of Shift: No need to Check again if this is a working Day
3548 END IF;
3549 l_temp_index := l_temp_index + 1;
3550 END IF; -- Hour Valid
3551 l_temp_start := l_temp_start + (1/24); -- Next Hour
3552 ELSE
3553 -- Not a working day: Go the start of shift of next day
3554 l_temp_start := TRUNC(l_temp_start) + 1 + (L_SHIFT_START/G_SECS_IN_DAY);
3555 END IF; -- Working Day
3556 END LOOP;
3557 ELSIF (p_UOM_code = G_UOM_DAY) THEN
3558 -- dbms_output.put_line('Day UOM');
3559 -- Break up into days
3560 l_temp_start := p_start_time;
3561 l_temp_end := p_end_time;
3562 WHILE (l_temp_start < l_temp_end) LOOP
3563 -- dbms_output.put_line('l_temp_start = ' || l_temp_start);
3564 l_temp_num := MOD((TRUNC(l_temp_start) - L_CAL_START), (L_DAYS_ON + L_DAYS_OFF)) + 1;
3565 IF (l_temp_num <= L_DAYS_ON) THEN
3566 -- dbms_output.put_line('Not Day Off');
3567 -- Not Day Off: Check if Holiday
3568 IF(Not_A_Holiday(l_temp_start, p_dept_id)) THEN
3569 -- dbms_output.put_line('Not a holiday');
3570 -- Working Day: Check if day is in calendar range
3571 IF(l_temp_start > L_CAL_END) THEN
3572 Fnd_Message.Set_Name('AHL','AHL_LTP_INSUFFICIENT_CAL_RANGE');
3573 Fnd_Msg_Pub.ADD;
3574 Ahl_Debug_Pub.debug('Computed date (' || l_temp_start || ') is outside calendar range', 'LTP: Init_Time_Vars');
3575 RAISE Fnd_Api.G_EXC_ERROR;
3576 END IF;
3577 x_per_rsrc_tbl(l_temp_index).PERIOD_START := trunc(l_temp_start);
3578 -- //@@@@@ Remove Hardcoding
3579 x_per_rsrc_tbl(l_temp_index).PERIOD_STRING := TO_CHAR(l_temp_start, 'DD-MM-YYYY');
3580
3581 Ahl_Debug_Pub.debug('before create time day:'||p_UOM_code||x_per_rsrc_tbl(l_temp_index).PERIOD_STRING);
3582
3583 -- yazhou 24Aug2005 starts
3584 -- Resource Leveling Re-design
3585
3586 x_per_rsrc_tbl(l_temp_index).PERIOD_END := l_temp_start + 1; -- 1 Day long
3587 -- x_per_rsrc_tbl(l_temp_index).PERIOD_END := trunc(l_temp_start); -- 1 Day long
3588
3589 -- yazhou 24Aug2005 ends
3590
3591 l_temp_index := l_temp_index + 1;
3592
3593 END IF; -- Not a holiday
3594 END IF; -- Not Day Off
3595 l_temp_start := l_temp_start + 1; -- Next Day
3596 END LOOP;
3597 ELSIF (p_UOM_code = G_UOM_WEEK) THEN
3598 -- dbms_output.put_line('Week UOM');
3599 -- Break up into weeks
3600 l_temp_start := p_start_time;
3601 l_temp_end := p_end_time;
3602 WHILE (l_temp_start < l_temp_end) LOOP
3603 x_per_rsrc_tbl(l_temp_index).PERIOD_START := l_temp_start;
3604 -- //@@@@@ Remove Hardcoding
3605 x_per_rsrc_tbl(l_temp_index).PERIOD_STRING := TO_CHAR(l_temp_start, 'DD-MM-YYYY');
3606 -- l_temp_start := l_temp_start + 7; -- 7 Days
3607 l_temp_start := l_temp_start + 6; -- 7 Days
3608 x_per_rsrc_tbl(l_temp_index).PERIOD_END := l_temp_start;
3609 l_temp_start := l_temp_start + 1; -- 7 Days
3610 l_temp_index := l_temp_index + 1;
3611 END LOOP;
3612 ELSIF (p_UOM_code = G_UOM_MONTH) THEN
3613 -- dbms_output.put_line('Month UOM');
3614 -- Break up into months
3615 l_temp_start := p_start_time;
3616 l_temp_end := p_end_time;
3617 WHILE (l_temp_start < l_temp_end) LOOP
3618 x_per_rsrc_tbl(l_temp_index).PERIOD_START := l_temp_start;
3619 IF (l_temp_index = 1) THEN
3620 -- //@@@@@ Remove Hardcoding
3621 x_per_rsrc_tbl(l_temp_index).PERIOD_STRING := TO_CHAR(l_temp_start, 'DD-MM-YYYY');
3622
3623
3624 ELSE
3625 -- //@@@@@ Remove Hardcoding
3626 x_per_rsrc_tbl(l_temp_index).PERIOD_STRING := TO_CHAR(l_temp_start, 'fmMONTH YYYY');
3627 END IF;
3628 SELECT LAST_DAY(l_temp_start) INTO l_temp_start FROM DUAL;
3629 l_temp_start := l_temp_start + 1; -- First day of next month
3630 x_per_rsrc_tbl(l_temp_index).PERIOD_END := l_temp_start;
3631 l_temp_index := l_temp_index + 1;
3632
3633 END LOOP;
3634 ELSE
3635 -- Invalid UOM Code
3636 Fnd_Message.Set_Name('AHL','AHL_LTP_UOM_CODE_INVALID');
3637 Fnd_Message.Set_Token('UOM_CODE', p_UOM_code);
3638 Fnd_Msg_Pub.ADD;
3639 RAISE Fnd_Api.G_EXC_ERROR;
3640 END IF;
3641 -- Ensure that the end time of the last period does not exceed user given end time
3642 IF (l_temp_index > 1 AND (x_per_rsrc_tbl(l_temp_index - 1).PERIOD_END > l_temp_end)) THEN
3643 x_per_rsrc_tbl(l_temp_index - 1).PERIOD_END := l_temp_end;
3644 END IF;
3645
3646 --For debug
3647 IF x_per_rsrc_tbl.COUNT > 0 THEN
3648 FOR i in x_per_rsrc_tbl.FIRST..x_per_rsrc_tbl.LAST LOOP
3649
3650 IF (l_log_statement >= l_log_current_level) THEN
3651 fnd_log.string
3652 (
3653 fnd_log.level_statement,
3654 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3655 'x_per_rsrc_tbl('||i||') START:'||TO_CHAR( x_per_rsrc_tbl(i).PERIOD_START, 'DD-MON-YYYY hh24:mi')
3656 );
3657 fnd_log.string
3658 (
3659 fnd_log.level_statement,
3660 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3661 'x_per_rsrc_tbl('||i||') END:'||TO_CHAR( x_per_rsrc_tbl(i).PERIOD_END, 'DD-MON-YYYY hh24:mi')
3662 );
3663 fnd_log.string
3664 (
3665 fnd_log.level_statement,
3666 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
3667 'x_per_rsrc_tbl('||i||') STRING:'||x_per_rsrc_tbl(i).PERIOD_STRING
3668 );
3669
3670 END IF;
3671 END LOOP;
3672 END IF;
3673 --
3674 END Create_Time_Periods;
3675
3676 ----------------------------------------
3677 -- Sorts the Quantity Change table based on event time
3678 ----------------------------------------
3679 PROCEDURE Sort_Qty_Change_Table
3680 (
3681 p_x_qty_change_tbl IN OUT NOCOPY Qty_Change_Tbl_Type
3682 ) IS
3683
3684 l_temp_qty_chng_rec Qty_Change_Rec_Type;
3685
3686 BEGIN
3687 IF (p_x_qty_change_tbl.COUNT < 2) THEN
3688 RETURN;
3689 END IF;
3690 FOR i IN p_x_qty_change_tbl.FIRST .. (p_x_qty_change_tbl.LAST - 1) LOOP
3691 FOR j IN (i + 1) .. p_x_qty_change_tbl.LAST LOOP
3692 IF (p_x_qty_change_tbl(i).EVENT_TIME > p_x_qty_change_tbl(j).EVENT_TIME) THEN
3693 -- Swap
3694 l_temp_qty_chng_rec := p_x_qty_change_tbl(i);
3695 p_x_qty_change_tbl(i) := p_x_qty_change_tbl(j);
3696 p_x_qty_change_tbl(j) := l_temp_qty_chng_rec;
3697 END IF;
3698 END LOOP;
3699 END LOOP;
3700 END Sort_Qty_Change_Table;
3701
3702 ----------------------------------------
3703 -- Sorts the Time_Period_Details_Tbl By Visit/Task
3704 ----------------------------------------
3705 PROCEDURE Sort_By_Visit_Task
3706 (
3707 p_x_tp_tbl IN OUT NOCOPY Time_Period_Details_Tbl_Type
3708 ) IS
3709
3710 l_temp_tp_rec Time_Period_Details_Rec_Type;
3711
3712 BEGIN
3713 IF (p_x_tp_tbl.COUNT < 2) THEN
3714 RETURN;
3715 END IF;
3716 FOR i IN p_x_tp_tbl.FIRST .. (p_x_tp_tbl.LAST - 1) LOOP
3717 FOR j IN (i + 1) .. p_x_tp_tbl.LAST LOOP
3718 IF (Compare_Visit_Tasks(p_x_tp_tbl(i).VISIT_ID, p_x_tp_tbl(i).TASK_ID , p_x_tp_tbl(j).VISIT_ID, p_x_tp_tbl(j).TASK_ID) > 0) THEN
3719 -- Swap
3720 l_temp_tp_rec := p_x_tp_tbl(i);
3721 p_x_tp_tbl(i) := p_x_tp_tbl(j);
3722 p_x_tp_tbl(j) := l_temp_tp_rec;
3723 END IF;
3724 END LOOP;
3725 END LOOP;
3726 END Sort_By_Visit_Task;
3727
3728 ----------------------------------------
3729 -- Aggregates Task Quantities and gets Task and Visit Names
3730 ----------------------------------------
3731 PROCEDURE Aggregate_Task_Quantities
3732 ( P_resource_id IN NUMBER,
3733 P_org_name IN VARCHAR2,
3734 P_dept_name IN VARCHAR2,
3735 p_tp_dtls_table IN Time_Period_Details_Tbl_Type,
3736 x_task_req_tbl OUT NOCOPY Task_Requirement_Tbl_Type
3737 ) IS
3738
3739 CURSOR l_get_visit_name_csr(p_visit_id IN NUMBER) IS
3740 SELECT VISIT_NAME, VISIT_NUMBER FROM ahl_visits_vl WHERE
3741 VISIT_ID = p_visit_id;
3742
3743 CURSOR l_get_task_name_csr(p_task_id IN NUMBER) IS
3744 SELECT VISIT_TASK_NAME,TASK_TYPE_CODE FROM ahl_visit_tasks_vl WHERE
3745 VISIT_TASK_ID = p_task_id;
3746 --
3747 CURSOR l_get_dept_cur (c_dept_name IN VARCHAR2,
3748 c_org_name IN VARCHAR2)
3749 IS
3750 SELECT B.department_id,A.organization_id FROM
3751 HR_ALL_ORGANIZATION_UNITS A, BOM_DEPARTMENTS B
3752 WHERE A.organization_id = B.organization_id
3753 AND a.name = c_org_name
3754 AND b.description = c_dept_name;
3755 --
3756 l_visit_id NUMBER;
3757 l_task_id NUMBER;
3758 l_qty NUMBER;
3759 l_required_qty NUMBER;
3760 l_available_qty NUMBER;
3761 l_new_index NUMBER := 1;
3762 l_task_name VARCHAR2(80);
3763 l_prev_visit_id NUMBER := -1;
3764 l_prev_visit_name VARCHAR2(80);
3765 l_prev_visit_number NUMBER;
3766 l_task_type_code VARCHAR2(30);
3767 l_dept_id NUMBER;
3768 l_org_id NUMBER;
3769 BEGIN
3770 IF (p_tp_dtls_table.COUNT = 0) THEN
3771 RETURN;
3772 END IF;
3773 --
3774 Ahl_Debug_Pub.debug('enter aggtegate O'||p_org_name);
3775 Ahl_Debug_Pub.debug('enter aggtegate D'||p_dept_name);
3776
3777 --Get dept id
3778 OPEN l_get_dept_cur(p_dept_name,p_org_name);
3779 FETCH l_get_dept_cur INTO l_dept_id,l_org_id;
3780 CLOSE l_get_dept_cur;
3781 --
3782 Ahl_Debug_Pub.debug('AFTER CURSOR aggtegate'||l_org_id);
3783 Ahl_Debug_Pub.debug('AFTER CURSOR aggtegate'||l_dept_id);
3784
3785 --
3786 l_visit_id := p_tp_dtls_table(p_tp_dtls_table.FIRST).VISIT_ID;
3787 l_task_id := p_tp_dtls_table(p_tp_dtls_table.FIRST).TASK_ID;
3788 l_qty := p_tp_dtls_table(p_tp_dtls_table.FIRST).QUANTITY;
3789 l_required_qty := p_tp_dtls_table(p_tp_dtls_table.FIRST).REQUIRED_UNITS;
3790 l_available_qty := Get_Available_Units(p_resource_id, l_dept_id);
3791
3792
3793 Ahl_Debug_Pub.debug('l_qty aggregate visit:'||l_qty);
3794 Ahl_Debug_Pub.debug('l_visit_id aggregate visit:'||l_visit_id);
3795 Ahl_Debug_Pub.debug('l_task_id aggregate visit:'||l_task_id);
3796
3797 FOR i IN p_tp_dtls_table.FIRST .. p_tp_dtls_table.LAST LOOP
3798 IF (p_tp_dtls_table(i).VISIT_ID <> l_visit_id OR p_tp_dtls_table(i).TASK_ID <> l_task_id) THEN
3799 -- New Visit Task: Update x_task_req_tbl
3800 x_task_req_tbl(l_new_index).VISIT_ID := l_visit_id;
3801 x_task_req_tbl(l_new_index).TASK_ID := l_task_id;
3802 x_task_req_tbl(l_new_index).REQUIRED_UNITS := l_qty;
3803 x_task_req_tbl(l_new_index).AVAILABLE_UNITS := l_available_qty;
3804
3805 IF (l_visit_id <> l_prev_visit_id) THEN
3806 -- Get Visit Name, Visit Number
3807 OPEN l_get_visit_name_csr(l_visit_id);
3808 FETCH l_get_visit_name_csr INTO l_prev_visit_name, l_prev_visit_number;
3809 IF (l_get_visit_name_csr%NOTFOUND) THEN
3810 Fnd_Message.Set_Name('AHL','AHL_LTP_VISIT_ID_INVALID');
3811 Fnd_Message.Set_Token('VISIT_ID', l_visit_id);
3812 Fnd_Msg_Pub.ADD;
3813 CLOSE l_get_visit_name_csr;
3814 RAISE Fnd_Api.G_EXC_ERROR;
3815 END IF;
3816 CLOSE l_get_visit_name_csr;
3817 l_prev_visit_id := l_visit_id;
3818 END IF;
3819 x_task_req_tbl(l_new_index).VISIT_NAME := l_prev_visit_name;
3820 x_task_req_tbl(l_new_index).VISIT_NUMBER := l_prev_visit_number;
3821 x_task_req_tbl(l_new_index).REQUIRED_UNITS := l_qty;
3822 x_task_req_tbl(l_new_index).AVAILABLE_UNITS := l_available_qty;
3823 x_task_req_tbl(l_new_index).DEPT_NAME := P_dept_name;
3824
3825 -- Get Task Name
3826 OPEN l_get_task_name_csr(l_task_id);
3827 FETCH l_get_task_name_csr INTO l_task_name,l_task_type_code;
3828 IF (l_get_task_name_csr%NOTFOUND) THEN
3829 Fnd_Message.Set_Name('AHL','AHL_LTP_TASK_ID_INVALID');
3830 Fnd_Message.Set_Token('TASK_ID', l_task_id);
3831 Fnd_Msg_Pub.ADD;
3832 CLOSE l_get_task_name_csr;
3833 RAISE Fnd_Api.G_EXC_ERROR;
3834 END IF;
3835 CLOSE l_get_task_name_csr;
3836 x_task_req_tbl(l_new_index).VISIT_TASK_NAME := l_task_name;
3837 x_task_req_tbl(l_new_index).TASK_TYPE_CODE := l_task_type_code;
3838 x_task_req_tbl(l_new_index).REQUIRED_UNITS := l_qty;
3839 x_task_req_tbl(l_new_index).AVAILABLE_UNITS := l_available_qty;
3840 x_task_req_tbl(l_new_index).DEPT_NAME := P_dept_name;
3841
3842 l_new_index := l_new_index + 1;
3843 -- Next, update l_visit_id, l_task_id and l_qty
3844 l_visit_id := p_tp_dtls_table(i).VISIT_ID;
3845 l_task_id := p_tp_dtls_table(i).TASK_ID;
3846 l_qty := p_tp_dtls_table(i).QUANTITY;
3847 l_required_qty := p_tp_dtls_table(i).REQUIRED_UNITS;
3848 -- l_available_qty := p_tp_dtls_table(i).AVAILABLE_UNITS;
3849 ELSE
3850 -- Same Visit/Task: Update l_qty if required
3851 IF (p_tp_dtls_table(i).QUANTITY > l_qty) THEN
3852 l_qty := p_tp_dtls_table(i).QUANTITY;
3853 END IF;
3854 END IF;
3855 END LOOP;
3856
3857 -- Add for last visit/task
3858 x_task_req_tbl(l_new_index).VISIT_ID := l_visit_id;
3859 x_task_req_tbl(l_new_index).TASK_ID := l_task_id;
3860 x_task_req_tbl(l_new_index).REQUIRED_UNITS := l_qty;
3861 x_task_req_tbl(l_new_index).TASK_TYPE_CODE := l_task_type_code;
3862 x_task_req_tbl(l_new_index).AVAILABLE_UNITS := l_available_qty;
3863 x_task_req_tbl(l_new_index).DEPT_NAME := P_dept_name;
3864
3865 IF (l_visit_id <> l_prev_visit_id) THEN
3866 -- Get Visit Name
3867 OPEN l_get_visit_name_csr(l_visit_id);
3868 FETCH l_get_visit_name_csr INTO l_prev_visit_name, l_prev_visit_number;
3869 IF (l_get_visit_name_csr%NOTFOUND) THEN
3870 Fnd_Message.Set_Name('AHL','AHL_LTP_VISIT_ID_INVALID');
3871 Fnd_Message.Set_Token('VISIT_ID', l_visit_id);
3872 Fnd_Msg_Pub.ADD;
3873 CLOSE l_get_visit_name_csr;
3874 RAISE Fnd_Api.G_EXC_ERROR;
3875 END IF;
3876 CLOSE l_get_visit_name_csr;
3877 END IF;
3878 x_task_req_tbl(l_new_index).VISIT_NAME := l_prev_visit_name;
3879 x_task_req_tbl(l_new_index).VISIT_NUMBER := l_prev_visit_number;
3880 -- Get Task Name
3881 OPEN l_get_task_name_csr(l_task_id);
3882 FETCH l_get_task_name_csr INTO l_task_name,l_task_type_code;
3883 IF (l_get_task_name_csr%NOTFOUND) THEN
3884 Fnd_Message.Set_Name('AHL','AHL_LTP_TASK_ID_INVALID');
3885 Fnd_Message.Set_Token('TASK_ID', l_task_id);
3886 Fnd_Msg_Pub.ADD;
3887 CLOSE l_get_task_name_csr;
3888 RAISE Fnd_Api.G_EXC_ERROR;
3889 END IF;
3890 CLOSE l_get_task_name_csr;
3891 x_task_req_tbl(l_new_index).VISIT_TASK_NAME := l_task_name;
3892 x_task_req_tbl(l_new_index).TASK_TYPE_CODE := l_task_type_code;
3893 x_task_req_tbl(l_new_index).DEPT_NAME := P_dept_name;
3894 x_task_req_tbl(l_new_index).REQUIRED_UNITS := l_qty;
3895 x_task_req_tbl(l_new_index).AVAILABLE_UNITS := l_available_qty;
3896 x_task_req_tbl(l_new_index).DEPT_NAME := P_dept_name;
3897 -- For debugging
3898 IF x_task_req_tbl.COUNT > 0 THEN
3899 FOR i in x_task_req_tbl.FIRST..x_task_req_tbl.LAST
3900 LOOP
3901 --
3902 Ahl_Debug_Pub.debug('END Task quantites VISIT TASK:'||x_task_req_tbl(i).visit_task_name);
3903 Ahl_Debug_Pub.debug('END Task quantites REQUNITS:'||x_task_req_tbl(i).required_units);
3904 Ahl_Debug_Pub.debug('END Task quantites AUNITS:'||x_task_req_tbl(i).available_units);
3905 --
3906 END LOOP;
3907 END IF;
3908 --
3909 END Aggregate_Task_Quantities;
3910 ----------------------------------------
3911 -- Gets the duration of a route from the timespan column
3912 -- of ahl_routes_b table
3913 ----------------------------------------
3914 PROCEDURE Get_Route_Duration
3915 (
3916 p_route_id IN NUMBER,
3917 x_duration OUT NOCOPY NUMBER
3918 ) IS
3919
3920 CURSOR l_route_csr (p_route_id IN NUMBER) IS
3921 SELECT route_id FROM ahl_mr_routes_app_v
3922 WHERE mr_route_id = p_route_id;
3923
3924 CURSOR l_route_time_span_csr (p_route_id IN NUMBER) IS
3925 SELECT NVL(time_span, 0) FROM ahl_routes_b
3926 WHERE route_id = p_route_id;
3927 x_route_id NUMBER;
3928 BEGIN
3929
3930 OPEN l_route_csr(p_route_id);
3931 FETCH l_route_csr INTO x_route_id;
3932 IF (l_route_csr%FOUND) THEN
3933
3934 OPEN l_route_time_span_csr(x_route_id);
3935 FETCH l_route_time_span_csr INTO x_duration;
3936 IF (l_route_time_span_csr%NOTFOUND) THEN
3937 Fnd_Message.Set_Name('AHL', 'AHL_LTP_ROUTE_ID_INVALID');
3938 Fnd_Message.Set_Token('ROUTE_ID', p_route_id);
3939 Fnd_Msg_Pub.ADD;
3940 CLOSE l_route_time_span_csr;
3941 Ahl_Debug_Pub.debug('Invalid route Id: ' || x_duration, 'LTP: Get_Route_Duration');
3942 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3943 END IF;
3944 CLOSE l_route_time_span_csr;
3945
3946 CLOSE l_route_csr;
3947 END IF;
3948
3949 END Get_Route_Duration;
3950
3951 ----------------------------------------
3952 -- Gets the duration of a route based on the resources either directly or through
3953 -- the operations constituting the route
3954 ----------------------------------------
3955 PROCEDURE Get_Rt_Ops_Duration
3956 (
3957 p_route_id IN NUMBER,
3958 x_duration OUT NOCOPY NUMBER
3959 ) IS
3960
3961 CURSOR l_route_operations_csr(p_route_id IN NUMBER) IS
3962 SELECT OPERATION_ID FROM ahl_route_operations
3963 WHERE route_id = p_route_id;
3964
3965 l_operation_id NUMBER;
3966 l_temp_duration NUMBER := 0;
3967
3968 BEGIN
3969 x_duration := 0;
3970 Get_Rt_Max_Duration(p_route_id, l_temp_duration);
3971 IF (l_temp_duration <> 0) THEN
3972 -- Defined at Route Level itself. Not necessary to go to operations
3973 x_duration := l_temp_duration;
3974 RETURN;
3975 ELSE
3976 -- Not defined at route level. Go to operation level
3977 OPEN l_route_operations_csr(p_route_id);
3978 LOOP
3979 FETCH l_route_operations_csr INTO l_operation_id;
3980 EXIT WHEN l_route_operations_csr%NOTFOUND;
3981 Get_Oper_Max_Duration(l_operation_id, l_temp_duration);
3982 x_duration := x_duration + l_temp_duration;
3983 END LOOP;
3984 END IF;
3985 CLOSE l_route_operations_csr;
3986 END Get_Rt_Ops_Duration;
3987
3988 ----------------------------------------
3989 -- Gets the time take for a given operation: Gets the maximum of all machines
3990 -- and labor required by this operation
3991 ----------------------------------------
3992 PROCEDURE Get_Oper_Max_Duration
3993 (
3994 p_operation_id IN NUMBER,
3995 x_duration OUT NOCOPY NUMBER
3996 ) IS
3997
3998 CURSOR l_oper_rsrc_time_csr(p_operation_id IN NUMBER) IS
3999 SELECT NVL(MAX(duration), 0) FROM ahl_rt_oper_resources
4000 WHERE OBJECT_ID = p_operation_id AND
4001 ASSOCIATION_TYPE_CODE = G_OPER_ASSOC_TYPE;
4002
4003 l_rsrc_max NUMBER := 0;
4004
4005 BEGIN
4006 x_duration := 0;
4007 OPEN l_oper_rsrc_time_csr(p_operation_id);
4008 FETCH l_oper_rsrc_time_csr INTO l_rsrc_max;
4009 CLOSE l_oper_rsrc_time_csr;
4010
4011 x_duration := l_rsrc_max;
4012
4013 END Get_Oper_Max_Duration;
4014
4015 ----------------------------------------
4016 -- Gets the time take for a given Route that has no operations: Gets the maximum
4017 -- of all machines and labor required by this Route
4018 ----------------------------------------
4019 PROCEDURE Get_Rt_Max_Duration
4020 (
4021 p_route_id IN NUMBER,
4022 x_duration OUT NOCOPY NUMBER
4023 ) IS
4024
4025 CURSOR l_rt_rsrc_time_csr(p_route_id IN NUMBER) IS
4026 SELECT NVL(MAX(duration), 0) FROM ahl_rt_oper_resources
4027 WHERE OBJECT_ID = p_route_id AND
4028 ASSOCIATION_TYPE_CODE = G_RT_ASSOC_TYPE;
4029
4030 l_rsrc_max NUMBER := 0;
4031
4032 BEGIN
4033 x_duration := 0;
4034 OPEN l_rt_rsrc_time_csr(p_route_id);
4035 FETCH l_rt_rsrc_time_csr INTO l_rsrc_max;
4036 CLOSE l_rt_rsrc_time_csr;
4037
4038 x_duration := l_rsrc_max;
4039
4040 END Get_Rt_Max_Duration;
4041
4042 ----------------------------------------
4043 -- Calculate and Initialize the global variables used for
4044 -- calculating task dates
4045 ----------------------------------------
4046 PROCEDURE Init_Time_Vars
4047 (
4048 p_visit_start_date IN DATE,
4049 p_department_id IN NUMBER
4050 ) IS
4051
4052 CURSOR l_dept_shift_csr(p_department_id IN NUMBER) IS
4053 SELECT calendar_code, shift_num FROM ahl_department_shifts
4054 WHERE department_id = p_department_id;
4055
4056 CURSOR l_shift_times_csr(p_calendar_code IN VARCHAR2, p_shift_num IN NUMBER) IS
4057 SELECT FROM_TIME, TO_TIME FROM bom_shift_times
4058 WHERE CALENDAR_CODE = p_calendar_code AND
4059 SHIFT_NUM = p_shift_num;
4060
4061 CURSOR l_workday_pattern_csr(p_calendar_code IN VARCHAR2, p_shift_num IN NUMBER) IS
4062 SELECT DAYS_ON, DAYS_OFF FROM bom_workday_patterns
4063 WHERE CALENDAR_CODE = p_calendar_code AND
4064 SHIFT_NUM = p_shift_num;
4065
4066 CURSOR l_calendar_csr(p_calendar_code IN VARCHAR2) IS
4067 SELECT CALENDAR_START_DATE, CALENDAR_END_DATE FROM bom_calendars
4068 WHERE CALENDAR_CODE = p_calendar_code;
4069
4070 CURSOR l_exceptions_csr(p_calendar_code IN VARCHAR2) IS
4071 SELECT EXCEPTION_DATE FROM bom_calendar_exceptions
4072 WHERE CALENDAR_CODE = p_calendar_code AND
4073 EXCEPTION_TYPE = G_HOLIDAY_TYPE
4074 ORDER BY EXCEPTION_DATE;
4075 --
4076 CURSOR l_dept_name_csr(c_dept_id IN NUMBER)
4077 IS
4078 SELECT description FROM bom_departments
4079 WHERE department_id = c_dept_id;
4080
4081 l_visit_start_date DATE := TRUNC(p_visit_start_date);
4082 l_temp_date DATE;
4083 l_temp_index NUMBER := 1;
4084 l_curr_wday_index NUMBER;
4085 l_dept_name VARCHAR2(240);
4086 BEGIN
4087 -- Clean-up any existing records
4088 G_WORKING_DATES_TBL.DELETE;
4089 --Get department name
4090 OPEN l_dept_name_csr(p_department_id);
4091 FETCH l_dept_name_csr INTO l_dept_name;
4092 CLOSE l_dept_name_csr;
4093 --
4094 -- Get the Calendar code and Shift Num for the department
4095 OPEN l_dept_shift_csr(p_department_id);
4096 FETCH l_dept_shift_csr INTO G_CALENDAR_CODE, G_SHIFT_NUM;
4097 IF (l_dept_shift_csr%NOTFOUND) THEN
4098 Fnd_Message.Set_Name('AHL','AHL_LTP_NO_SHIFT_FOR_DEPT');
4099 Fnd_Message.Set_Token('DEPT_ID', l_dept_name);
4100 Fnd_Msg_Pub.ADD;
4101 CLOSE l_dept_shift_csr;
4102 Ahl_Debug_Pub.debug('No shift/calendar code for department: ' || p_department_id, 'LTP: Init_Time_Vars');
4103 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4104 END IF;
4105 CLOSE l_dept_shift_csr;
4106 -- dbms_output.put_line('G_CALENDAR_CODE := ' || G_CALENDAR_CODE);
4107 -- dbms_output.put_line('G_SHIFT_NUM := ' || G_SHIFT_NUM);
4108
4109 -- Get the calendar start date and the calendar end date
4110 OPEN l_calendar_csr(G_CALENDAR_CODE);
4111 FETCH l_calendar_csr INTO G_CAL_START, G_CAL_END;
4112 IF (l_calendar_csr%NOTFOUND) THEN
4113 Fnd_Message.Set_Name('AHL','AHL_LTP_CAL_CODE_INVALID');
4114 Fnd_Message.Set_Token('CAL_CODE', G_CALENDAR_CODE);
4115 Fnd_Msg_Pub.ADD;
4116 CLOSE l_calendar_csr;
4117 Ahl_Debug_Pub.debug('No BOM_CALENDARS entry for calendar code: ' || G_CALENDAR_CODE, 'LTP: Init_Time_Vars');
4118 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4119 END IF;
4120 CLOSE l_calendar_csr;
4121 -- Truncate the date values
4122 G_CAL_START := TRUNC(G_CAL_START);
4123 G_CAL_END := TRUNC(G_CAL_END);
4124 -- dbms_output.put_line('G_CAL_START := ' || G_CAL_START);
4125 -- dbms_output.put_line('G_CAL_END := ' || G_CAL_END);
4126
4127 -- Get the shift start and shift end times
4128 OPEN l_shift_times_csr(G_CALENDAR_CODE, G_SHIFT_NUM);
4129 FETCH l_shift_times_csr INTO G_SHIFT_START, G_SHIFT_END;
4130 IF (l_shift_times_csr%NOTFOUND) THEN
4131 Fnd_Message.Set_Name('AHL','AHL_LTP_NO_SHIFT_FOR_DEPT');
4132 Fnd_Message.Set_Token('DEPT_ID', l_dept_name);
4133 Fnd_Msg_Pub.ADD;
4134 CLOSE l_shift_times_csr;
4135 Ahl_Debug_Pub.debug('No shift start and end times for department: ' || p_department_id, 'LTP: Init_Time_Vars');
4136 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4137 END IF;
4138 CLOSE l_shift_times_csr;
4139 -- dbms_output.put_line('G_SHIFT_START := ' || G_SHIFT_START);
4140 -- dbms_output.put_line('G_SHIFT_END := ' || G_SHIFT_END);
4141 IF(G_SHIFT_END < G_SHIFT_START) THEN
4142 G_SHIFT_DURATION_HRS := (G_SHIFT_END + G_SECS_IN_DAY - G_SHIFT_START)/G_SECS_IN_HOUR;
4143 ELSE
4144 G_SHIFT_DURATION_HRS := (G_SHIFT_END - G_SHIFT_START)/G_SECS_IN_HOUR;
4145 END IF;
4146 -- dbms_output.put_line('G_SHIFT_DURATION_HRS := ' || G_SHIFT_DURATION_HRS);
4147
4148 -- Get Days On and Days Off
4149 OPEN l_workday_pattern_csr(G_CALENDAR_CODE, G_SHIFT_NUM);
4150 FETCH l_workday_pattern_csr INTO G_DAYS_ON, G_DAYS_OFF;
4151 IF (l_workday_pattern_csr%NOTFOUND) THEN
4152 Fnd_Message.Set_Name('AHL','AHL_LTP_NO_PTRN_FOR_DEPT');
4153 Fnd_Message.Set_Token('DEPT_ID', l_dept_name);
4154 Fnd_Msg_Pub.ADD;
4155 CLOSE l_workday_pattern_csr;
4156 Ahl_Debug_Pub.debug('No Work Day Pattern for department: ' || p_department_id, 'LTP: Init_Time_Vars');
4157 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4158 END IF;
4159 CLOSE l_workday_pattern_csr;
4160
4161 -- Get the Exception (Holidays) days
4162 OPEN l_exceptions_csr(G_CALENDAR_CODE);
4163 l_temp_index := 1;
4164 LOOP
4165 FETCH l_exceptions_csr INTO l_temp_date;
4166 EXIT WHEN l_exceptions_csr%NOTFOUND;
4167 G_EXCEPTION_DATES_TBL(l_temp_index) := TRUNC(l_temp_date);
4168 l_temp_index := l_temp_index + 1;
4169 END LOOP;
4170 CLOSE l_exceptions_csr;
4171
4172 IF (p_visit_start_date IS NULL) THEN
4173 Fnd_Message.Set_Name('AHL','AHL_LTP_VISIT_ST_DATE_NULL');
4174 Fnd_Msg_Pub.ADD;
4175 Ahl_Debug_Pub.debug('Visit start date is null', 'LTP: Init_Time_Vars');
4176 RAISE Fnd_Api.G_EXC_ERROR;
4177 ELSIF (p_visit_start_date < G_CAL_START OR p_visit_start_date > G_CAL_END) THEN
4178 Fnd_Message.Set_Name('AHL','AHL_LTP_VISIT_ST_DATE_INVALID');
4179 Fnd_Message.Set_Token('VISIT_ST_DATE', p_visit_start_date);
4180 Fnd_Msg_Pub.ADD;
4181 Ahl_Debug_Pub.debug('Visit start date (' || p_visit_start_date || ') is outside calendar range', 'LTP: Init_Time_Vars');
4182 RAISE Fnd_Api.G_EXC_ERROR;
4183 END IF;
4184
4185 --Code fixed by shbhanda on 21st Oct'02
4186 -- Ensure that the visit start date falls on a working day
4187 l_curr_wday_index := MOD((l_visit_start_date - G_CAL_START), (G_DAYS_ON + G_DAYS_OFF)) + 1;
4188 WHILE (l_curr_wday_index > G_DAYS_ON) LOOP
4189 -- Day Off
4190 Ahl_Debug_Pub.debug('Visit Start Date = ' ||l_visit_start_date );
4191 l_visit_start_date := l_visit_start_date + 1;
4192 l_curr_wday_index := MOD((l_visit_start_date - G_CAL_START), (G_DAYS_ON + G_DAYS_OFF)) + 1;
4193 Ahl_Debug_Pub.debug('Inside first while loop');
4194 END LOOP;
4195
4196 -- Not Day Off: Check if Holiday
4197 WHILE (IS_DEPT_Holiday(l_visit_start_date)) LOOP
4198 Ahl_Debug_Pub.debug('Visit Start Date = ' ||l_visit_start_date );
4199 l_visit_start_date := l_visit_start_date + 1;
4200 Ahl_Debug_Pub.debug('Inside second while loop');
4201 -- Holiday
4202 END LOOP;
4203
4204 -- Commented by shbhanda 21 Oct '02
4205 /* -- Ensure that the visit start date falls on a working day
4206 l_curr_wday_index := MOD((l_visit_start_date - G_CAL_START), (G_DAYS_ON + G_DAYS_OFF)) + 1;
4207 IF (l_curr_wday_index > G_DAYS_ON) THEN
4208 -- Day Off
4209 Fnd_Message.Set_Name('AHL','AHL_LTP_VISIT_ST_WDATE_INVALID');
4210 Fnd_Message.Set_Token('VISIT_ST_DATE', p_visit_start_date);
4211 Fnd_Msg_Pub.ADD;
4212 Ahl_Debug_Pub.debug('Visit start date (' || p_visit_start_date || ') is not a working day', 'LTP: Init_Time_Vars');
4213 RAISE Fnd_Api.G_EXC_ERROR;
4214 ELSE
4215 -- Not Day Off: Check if Holiday
4216 IF(IS_DEPT_Holiday(l_visit_start_date)) THEN
4217 -- Holiday
4218 Fnd_Message.Set_Name('AHL','AHL_LTP_VISIT_ST_WDATE_INVALID');
4219 Fnd_Message.Set_Token('VISIT_ST_DATE', p_visit_start_date);
4220 Fnd_Msg_Pub.ADD;
4221 Ahl_Debug_Pub.debug('Visit start date (' || p_visit_start_date || ') is not a working day', 'LTP: Init_Time_Vars');
4222 RAISE Fnd_Api.G_EXC_ERROR;
4223 END IF;
4224 END IF;*/
4225
4226
4227 -- Add shift start to l_visit_start_date and set it to G_ZERO_TIME and G_WORKING_DATES_TBL(0)
4228 l_visit_start_date := l_visit_start_date + (G_SHIFT_START/G_SECS_IN_DAY);
4229 G_ZERO_TIME := l_visit_start_date;
4230 -- dbms_output.put_line('G_ZERO_TIME := ' || TO_CHAR(G_ZERO_TIME, 'MM/DD/YY HH24:MI:SS'));
4231 G_WORKING_DATES_TBL(0) := G_ZERO_TIME;
4232 G_MAX_CAL_DAY := 0;
4233
4234 END Init_Time_Vars;
4235
4236 ----------------------------------------
4237 -- Determine the Nth working date (including shift start time)
4238 -- based on the applicable calendar
4239 ----------------------------------------
4240 FUNCTION Get_Nth_Day(p_day_index NUMBER) RETURN DATE
4241 IS
4242
4243 l_temp_index NUMBER;
4244 l_curr_day DATE;
4245 l_curr_wday_index NUMBER;
4246 l_temp_flag BOOLEAN;
4247
4248 BEGIN
4249 IF (p_day_index <= G_MAX_CAL_DAY) THEN
4250 RETURN G_WORKING_DATES_TBL(p_day_index);
4251 ELSE
4252 FOR l_temp_index IN G_MAX_CAL_DAY + 1 .. p_day_index LOOP
4253 l_curr_day := G_WORKING_DATES_TBL(l_temp_index - 1) + 1;
4254 l_temp_flag := FALSE;
4255 WHILE NOT l_temp_flag LOOP
4256 -- Check if l_curr_day is a working day
4257 l_curr_wday_index := MOD((TRUNC(l_curr_day) - G_CAL_START), (G_DAYS_ON + G_DAYS_OFF)) + 1;
4258 IF (l_curr_wday_index > G_DAYS_ON) THEN
4259 -- Day Off
4260 l_temp_flag := FALSE;
4261 l_curr_day := l_curr_day + 1;
4262 ELSE
4263 -- Not Day Off: Check if Holiday
4264 IF(IS_DEPT_Holiday(l_curr_day)) THEN
4265 -- Holiday
4266 l_temp_flag := FALSE;
4267 l_curr_day := l_curr_day + 1;
4268 ELSE
4269 -- yazhou 24Aug2005 starts
4270 -- Resource Leveling Re-design
4271 -- Remove calendar range validation to sync with time calculation in VWP
4272 /*
4273 -- Working Day: Check if day is in calendar range
4274 IF(l_curr_day > G_CAL_END) THEN
4275 Fnd_Message.Set_Name('AHL','AHL_LTP_INSUFFICIENT_CAL_RANGE');
4276 Fnd_Msg_Pub.ADD;
4277 Ahl_Debug_Pub.debug('Computed date (' || l_curr_day || ') is outside calendar range', 'LTP: Init_Time_Vars');
4278 RAISE Fnd_Api.G_EXC_ERROR;
4279 END IF;
4280 */
4281 -- yazhou 24Aug2005 ends
4282
4283 -- Add this day to the table
4284 G_WORKING_DATES_TBL(l_temp_index) := l_curr_day;
4285 G_MAX_CAL_DAY := l_temp_index;
4286 l_temp_flag := TRUE;
4287 END IF;
4288 END IF;
4289 END LOOP;
4290 END LOOP;
4291 RETURN G_WORKING_DATES_TBL(p_day_index);
4292 END IF;
4293 END Get_Nth_Day;
4294
4295 ----------------------------------------
4296 -- Function to determine if a specific date is a holiday
4297 ----------------------------------------
4298 FUNCTION IS_DEPT_Holiday(l_curr_date DATE) RETURN BOOLEAN
4299 IS
4300 l_temp_date DATE := TRUNC(l_curr_date);
4301 BEGIN
4302 IF (G_EXCEPTION_DATES_TBL.COUNT = 0) THEN
4303 RETURN FALSE;
4304 END IF;
4305 FOR i IN G_EXCEPTION_DATES_TBL.FIRST .. G_EXCEPTION_DATES_TBL.LAST LOOP
4306 IF (l_temp_date = G_EXCEPTION_DATES_TBL(i)) THEN
4307 RETURN TRUE;
4308 ELSIF (l_temp_date < G_EXCEPTION_DATES_TBL(i)) THEN
4309 RETURN FALSE;
4310 END IF;
4311 END LOOP;
4312 RETURN FALSE;
4313 END IS_DEPT_Holiday;
4314
4315 ----------------------------------------
4316 -- Function to determine if a specific date is not
4317 -- a holiday in the given department
4318 ----------------------------------------
4319 FUNCTION Not_A_Holiday(p_curr_date DATE, p_dept_id NUMBER) RETURN BOOLEAN
4320 IS
4321 l_junk VARCHAR2(1);
4322 BEGIN
4323 SELECT 'x' INTO l_junk FROM AHL_DEPARTMENT_SHIFTS ADS, BOM_CALENDAR_EXCEPTIONS BCE
4324 WHERE ADS.DEPARTMENT_ID = p_dept_id AND
4325 BCE.CALENDAR_CODE = ADS.CALENDAR_CODE AND
4326 BCE.EXCEPTION_TYPE = G_HOLIDAY_TYPE AND
4327 TRUNC(BCE.EXCEPTION_DATE) = TRUNC(p_curr_date);
4328
4329 IF (SQL%NOTFOUND) THEN
4330 RETURN TRUE;
4331 ELSE
4332 RETURN FALSE;
4333 END IF;
4334
4335 EXCEPTION
4336 WHEN NO_DATA_FOUND THEN
4337 RETURN TRUE;
4338
4339 END Not_A_Holiday;
4340
4341 ----------------------------------------
4342 -- Function to determine if a specific resource is available
4343 -- (working in a shift) at a specific time
4344 ----------------------------------------
4345 FUNCTION Resource_In_Duty(p_resource_id NUMBER, p_start_date_time DATE) RETURN BOOLEAN
4346 IS
4347
4348 CURSOR l_shift_details_csr(p_resource_id NUMBER) IS
4349 SELECT from_time, to_time
4350 FROM bom_shift_times st, bom_resource_shifts rs
4351 WHERE st.shift_num = rs.shift_num AND
4352 rs.resource_id = p_resource_id;
4353
4354 l_shift_start NUMBER := 0;
4355 l_shift_end NUMBER := 0;
4356 l_start_second NUMBER := 0;
4357
4358 BEGIN
4359
4360 -- Get Days On and Days Off
4361 OPEN l_shift_details_csr(p_resource_id);
4362 FETCH l_shift_details_csr INTO l_shift_start, l_shift_end;
4363 IF (l_shift_details_csr%NOTFOUND) THEN
4364 Fnd_Message.Set_Name('AHL','AHL_LTP_NO_SHIFT_FOR_RSRC');
4365 Fnd_Message.Set_Token('RSRC_ID', p_resource_id);
4366 Fnd_Msg_Pub.ADD;
4367 CLOSE l_shift_details_csr;
4368 Ahl_Debug_Pub.debug('No Shift for Resource: ' || p_resource_id, 'LTP: Resource_In_Duty');
4369 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
4370 END IF;
4371 CLOSE l_shift_details_csr;
4372
4373 l_start_second := (p_start_date_time - TRUNC(p_start_date_time)) * G_SECS_IN_DAY;
4374 IF (l_shift_start > l_shift_end) THEN
4375 RETURN (l_start_second >= l_shift_start OR l_start_second <= l_shift_end);
4376 ELSE
4377 RETURN (l_start_second >= l_shift_start AND l_start_second <= l_shift_end);
4378 END IF;
4379 END Resource_In_Duty;
4380
4381 ----------------------------------------
4382 -- Function to determine if the Resource is already present in the given table
4383 ----------------------------------------
4384 FUNCTION Is_Resource_Present(p_aso_resource_id NUMBER,
4385 p_task_rsrc_tbl Plan_Rsrc_Tbl_Type) RETURN BOOLEAN
4386 IS
4387 BEGIN
4388 IF (p_task_rsrc_tbl.COUNT = 0) THEN
4389 RETURN FALSE;
4390 ELSE
4391 IF (p_aso_resource_id IS NOT NULL) THEN
4392 FOR i IN p_task_rsrc_tbl.FIRST .. p_task_rsrc_tbl.LAST LOOP
4393 IF (p_task_rsrc_tbl(i).ASO_BOM_TYPE = G_ASO_RESOURCE AND
4394 p_task_rsrc_tbl(i).RESOURCE_ID = p_aso_resource_id) THEN
4395 RETURN TRUE;
4396 END IF;
4397 END LOOP;
4398 END IF;
4399 END IF;
4400 END Is_Resource_Present;
4401 ----------------------------------------
4402 -- Function to determine if two time periods overlap
4403 -- Note that If the end time of one period coincides with the
4404 -- start time of the other time period, the timeperiods DON'T overlap
4405 ----------------------------------------
4406 FUNCTION Periods_Overlap(p1_start_time DATE, p1_end_time DATE, p2_start_time DATE, p2_end_time DATE)
4407 RETURN BOOLEAN IS
4408 l1s DATE := p1_start_time;
4409 l1e DATE := p1_end_time;
4410 l2s DATE := p2_start_time;
4411 l2e DATE := p2_end_time;
4412
4413 l_temp_time DATE;
4414 BEGIN
4415 IF (p1_start_time IS NULL OR p2_start_time IS NULL) THEN
4416 -- Invalid: Return false
4417 RETURN FALSE;
4418 END IF;
4419
4420 IF(l1s > l2s) THEN
4421 -- Swap so that l1 starts first
4422 l_temp_time := l1s;
4423 l1s := l2s;
4424 l2s := l_temp_time;
4425 l_temp_time := l1e;
4426 l1e := l2e;
4427 l2e := l_temp_time;
4428 END IF;
4429 RETURN (l1e > l2s);
4430 END Periods_Overlap;
4431
4432 ----------------------------------------
4433 -- Calculates the maximum required quantity during a given period
4434 ----------------------------------------
4435 FUNCTION Get_Required_Quantity(
4436 p_start_time DATE,
4437 p_end_time DATE,
4438 p_tp_dtls_tbl Time_Period_Details_Tbl_Type)
4439 RETURN NUMBER IS
4440
4441 l_qty_change_tbl Qty_Change_Tbl_Type;
4442 l_temp_index NUMBER := 1;
4443 l_initial_req NUMBER := 0;
4444 l_max_demand NUMBER := 0;
4445 l_cur_demand NUMBER := 0;
4446 l_peak_instant DATE;
4447
4448 l_api_name CONSTANT VARCHAR2(30) := 'Get_Required_Quantity';
4449
4450 BEGIN
4451
4452 IF (l_log_statement >= l_log_current_level) THEN
4453 fnd_log.string
4454 (
4455 fnd_log.level_statement,
4456 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
4457 'inside Get_Required_Quantity. p_tp_dtls_tbl.count: '||p_tp_dtls_tbl.count
4458 );
4459
4460 END IF;
4461
4462 -- Fill the Quantity Change table with operations/tasks that are in
4463 -- progress during the time period, Getting the Initial demand along the way
4464 FOR i IN p_tp_dtls_tbl.FIRST .. p_tp_dtls_tbl.LAST LOOP
4465
4466 IF (l_log_statement >= l_log_current_level) THEN
4467 fnd_log.string
4468 (
4469 fnd_log.level_statement,
4470 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
4471 ' i: '||i
4472 );
4473 END IF;
4474
4475 IF (Periods_Overlap(p_start_time, p_end_time, p_tp_dtls_tbl(i).START_TIME, p_tp_dtls_tbl(i).END_TIME)) THEN
4476
4477 IF (l_log_statement >= l_log_current_level) THEN
4478 fnd_log.string
4479 (
4480 fnd_log.level_statement,
4481 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
4482 ' Periods_Overlap'
4483 );
4484 END IF;
4485
4486 l_qty_change_tbl(l_temp_index).EVENT_TIME := p_tp_dtls_tbl(i).START_TIME;
4487 l_qty_change_tbl(l_temp_index).QTY_CHANGE := p_tp_dtls_tbl(i).QUANTITY;
4488
4489 IF (l_log_statement >= l_log_current_level) THEN
4490 fnd_log.string
4491 (
4492 fnd_log.level_statement,
4493 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
4494 ' l_qty_change_tbl('||l_temp_index||').QTY_CHANGE: '||l_qty_change_tbl(l_temp_index).QTY_CHANGE
4495 );
4496
4497 END IF;
4498
4499 l_temp_index := l_temp_index + 1;
4500 l_qty_change_tbl(l_temp_index).EVENT_TIME := p_tp_dtls_tbl(i).END_TIME;
4501 l_qty_change_tbl(l_temp_index).QTY_CHANGE := -(p_tp_dtls_tbl(i).QUANTITY);
4502
4503 IF (l_log_statement >= l_log_current_level) THEN
4504 fnd_log.string
4505 (
4506 fnd_log.level_statement,
4507 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
4508 ' l_qty_change_tbl('||l_temp_index||').QTY_CHANGE: '||l_qty_change_tbl(l_temp_index).QTY_CHANGE
4509 );
4510
4511 END IF;
4512
4513 l_temp_index := l_temp_index + 1;
4514 IF (p_tp_dtls_tbl(i).START_TIME < p_start_time) THEN
4515 l_initial_req := l_initial_req + p_tp_dtls_tbl(i).QUANTITY;
4516 END IF;
4517
4518 IF (l_log_statement >= l_log_current_level) THEN
4519 fnd_log.string
4520 (
4521 fnd_log.level_statement,
4522 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
4523 ' l_temp_index: '||l_temp_index
4524 );
4525
4526 END IF;
4527 END IF;
4528 END LOOP;
4529
4530 IF (l_log_statement >= l_log_current_level) THEN
4531 fnd_log.string
4532 (
4533 fnd_log.level_statement,
4534 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
4535 'After loop l_qty_change_tbl.count: '||l_qty_change_tbl.count
4536 );
4537
4538 END IF;
4539
4540
4541 IF (l_qty_change_tbl.COUNT = 0) THEN
4542 -- No Task/Operation during this period
4543 RETURN 0;
4544 ELSIF (l_qty_change_tbl.COUNT = 2) THEN
4545 -- Only one task
4546 RETURN l_qty_change_tbl(l_qty_change_tbl.FIRST).QTY_CHANGE;
4547 END IF;
4548
4549 -- Sort Quantity Change Table on Event time
4550 Sort_Qty_Change_Table(l_qty_change_tbl);
4551
4552 -- Get the maximum demand
4553 l_cur_demand := l_initial_req;
4554 l_max_demand := l_initial_req;
4555 l_peak_instant := l_qty_change_tbl(l_qty_change_tbl.FIRST).EVENT_TIME;
4556 FOR j IN l_qty_change_tbl.FIRST .. l_qty_change_tbl.LAST LOOP
4557 IF (l_qty_change_tbl(j).EVENT_TIME >= p_start_time) THEN
4558 l_cur_demand := l_cur_demand + l_qty_change_tbl(j).QTY_CHANGE;
4559 IF (l_cur_demand > l_max_demand) THEN
4560 l_max_demand := l_cur_demand;
4561 l_peak_instant := l_qty_change_tbl(j).EVENT_TIME;
4562 END IF;
4563 END IF;
4564 END LOOP;
4565
4566 IF (l_log_statement >= l_log_current_level) THEN
4567 fnd_log.string
4568 (
4569 fnd_log.level_statement,
4570 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
4571 'Complete Get_Required_Quantity. l_max_demand: '||l_max_demand
4572 );
4573
4574 END IF;
4575 -- dbms_output.put_line('Max demand = ' || l_max_demand || ' at ' || TO_CHAR(l_peak_instant, 'MM/DD/YY HH24:MI:SS'));
4576 -- Return the maximum requirement
4577 RETURN l_max_demand;
4578 END;
4579 ----------------------------------------
4580 -- Compares a visit/task combination with another
4581 -- Returns +1 If first visit task > second visit task
4582 -- Returns -1 If first visit task < second visit task
4583 -- Returns 0 If first visit task = second visit task
4584 ----------------------------------------
4585 FUNCTION Compare_Visit_Tasks(p_visit_1 NUMBER, p_task_1 NUMBER, p_visit_2 NUMBER, p_task_2 NUMBER)
4586 RETURN NUMBER IS
4587 BEGIN
4588 IF (p_visit_1 > p_visit_2) THEN
4589 RETURN 1;
4590 ELSIF (p_visit_1 < p_visit_2) THEN
4591 RETURN -1;
4592 ELSE
4593 -- Visit Ids are equal: Compare by task id
4594 IF (p_task_1 > p_task_2) THEN
4595 RETURN 1;
4596 ELSIF (p_task_1 < p_task_2) THEN
4597 RETURN -1;
4598 ELSE
4599 RETURN 0;
4600 END IF;
4601 END IF;
4602 END Compare_Visit_Tasks;
4603 /*
4604 ----------------------------------------
4605 -- Diagnostic procedure to test work day calculations
4606 ----------------------------------------
4607 PROCEDURE Dump_Working_dates_tbl
4608 (
4609 -- p_start_date IN DATE := TO_DATE('15-May-2002'),
4610 p_dept_id IN NUMBER := 1,
4611 p_num_days IN NUMBER := 100
4612 ) IS
4613
4614 l_index NUMBER;
4615 l_temp_date DATE;
4616 BEGIN
4617 -- dbms_output.put_line('Beginning Dump_Working_dates_tbl');
4618 -- dbms_output.put_line('p_start_date = ' || p_start_date);
4619
4620 Init_Time_Vars(p_start_date, p_dept_id);
4621
4622 IF G_EXCEPTION_DATES_TBL.COUNT > 0 THEN
4623 l_index := G_EXCEPTION_DATES_TBL.FIRST;
4624 WHILE l_index IS NOT NULL LOOP
4625 -- dbms_output.put_line('Holiday: ' || l_index || ' Date: ' || TO_CHAR(G_EXCEPTION_DATES_TBL(l_index), 'MM/DD/YY HH24:MI:SS'));
4626 l_index := G_EXCEPTION_DATES_TBL.NEXT(l_index);
4627 END LOOP;
4628 END IF;
4629
4630 l_temp_date := Get_Nth_Day(p_num_days);
4631 -- dbms_output.put_line('G_WORKING_DATES_TBL COUNT = ' || G_WORKING_DATES_TBL.COUNT);
4632 IF G_WORKING_DATES_TBL.COUNT > 0 THEN
4633 l_index := G_WORKING_DATES_TBL.FIRST;
4634 WHILE l_index IS NOT NULL LOOP
4635 -- dbms_output.put_line('Day: ' || l_index || ' Date: ' || TO_CHAR(G_WORKING_DATES_TBL(l_index), 'MM/DD/YY HH24:MI:SS'));
4636 l_index := G_WORKING_DATES_TBL.NEXT(l_index);
4637 END LOOP;
4638 END IF;
4639 -- dbms_output.put_line('Ending Dump_Working_dates_tbl');
4640 END;
4641 */
4642 -------------------------------
4643 -- End Local Procedures --
4644 -------------------------------
4645
4646 ---------------------------------------
4647 -- Calculates the available units of
4648 -- a resource during a given period
4649 ----------------------------------------
4650 -- yazhou 24Aug2005 starts
4651 -- Resource Leveling Re-design
4652 -- Get the shift number for the given department
4653 -- And then retrieve shift capacity for that shift only
4654 -- Also removed p_org_id as input parameter since it's not used
4655
4656 FUNCTION Get_Available_Units(p_resource_id NUMBER,
4657 p_dept_id NUMBER)
4658 RETURN NUMBER IS
4659 --
4660 CURSOR l_get_bom_rsrc_dtls_csr(p_bom_resource_id IN NUMBER,
4661 p_bom_department_id IN NUMBER) IS
4662 SELECT B.CAPACITY_UNITS,a.description
4663 FROM bom_resources A,
4664 bom_department_resources B
4665 WHERE a. resource_id = B.resource_id
4666 AND B.resource_id = p_bom_resource_id
4667 AND B.department_id = p_bom_department_id;
4668
4669 -- Gets shift capacity
4670 CURSOR l_get_shift_dtls_csr(p_bom_resource_id IN NUMBER,
4671 p_bom_department_id IN NUMBER) IS
4672 SELECT CAPACITY_UNITS SHIFT_CAPACITY
4673 FROM bom_resource_shifts
4674 WHERE resource_id = p_bom_resource_id
4675 AND department_id = p_bom_department_id
4676 AND SHIFT_NUM = ( select shift_num
4677 FROM AHL_DEPARTMENT_SHIFTS
4678 WHERE department_id = p_bom_department_id);
4679
4680 -- l_res_type NUMBER;
4681 -- l_bom_org_id NUMBER;
4682 l_total_quantity NUMBER := 0;
4683 -- l_bom_resource_id NUMBER;
4684 l_capacity_units NUMBER;
4685 -- l_shift_num NUMBER;
4686 l_shift_capacity NUMBER;
4687 l_description bom_resources.description%type;
4688
4689 -- yazhou 24Aug2005 ends
4690
4691 BEGIN
4692 Ahl_Debug_Pub.debug('enter get available p_resource_id:'||p_resource_id);
4693 Ahl_Debug_Pub.debug('enter get available p_dept_id:'||p_dept_id);
4694
4695 --Get available units
4696 OPEN l_get_bom_rsrc_dtls_csr(p_resource_id,p_dept_id);
4697 FETCH l_get_bom_rsrc_dtls_csr INTO l_capacity_units,l_description;
4698 IF l_get_bom_rsrc_dtls_csr%NOTFOUND THEN
4699 Fnd_Message.Set_Name('AHL','AHL_LTP_RES_ID_INVALID');
4700 Fnd_Message.Set_Token('RES_ID', l_description);
4701 Fnd_Msg_Pub.ADD;
4702 END IF;
4703 CLOSE l_get_bom_rsrc_dtls_csr;
4704 Ahl_Debug_Pub.debug('Inside get available l_capacity_units:'||l_capacity_units);
4705
4706 -- Get shift capacity
4707 OPEN l_get_shift_dtls_csr(p_resource_id,p_dept_id);
4708 FETCH l_get_shift_dtls_csr INTO l_shift_capacity;
4709 CLOSE l_get_shift_dtls_csr;
4710
4711 Ahl_Debug_Pub.debug('Inside get available l_shift_capacity:'||l_shift_capacity);
4712
4713 IF l_shift_capacity IS NOT NULL THEN
4714 l_total_quantity := l_shift_capacity;
4715 ELSE
4716 l_total_quantity := l_capacity_units;
4717 END IF;
4718
4719 Ahl_Debug_Pub.debug('Inside get available l_total_quantity:'||l_total_quantity);
4720
4721 RETURN l_total_quantity;
4722
4723 END Get_Available_Units;
4724 --
4725 -- AnRaj: Obsoleted Procedure
4726 /*
4727 PROCEDURE GET_WIP_DISC_REQ_UNITS(
4728 p_org_id IN NUMBER,
4729 p_bom_dept_id IN NUMBER,
4730 p_bom_resource_id IN NUMBER,
4731 p_start_date IN DATE,
4732 p_end_date IN DATE,
4733 x_assigned_units OUT NOCOPY NUMBER)
4734 IS
4735
4736 CURSOR wip_disc_cur (c_org_id IN NUMBER)
4737 IS
4738 SELECT wip.wip_entity_id
4739 FROM wip_discrete_jobs wip
4740 WHERE wip.organization_id = c_org_id
4741 AND (p_start_date BETWEEN wip.scheduled_start_date
4742 AND wip.scheduled_completion_date
4743 OR
4744 p_end_date BETWEEN wip.scheduled_start_date
4745 AND wip.scheduled_completion_date)
4746 AND not exists (select wip_entity_id
4747 from ahl_workorders wo
4748 where wo.wip_entity_id = wip.wip_entity_id
4749 and wo.status_code = '17');
4750
4751 --
4752 CURSOR wip_oper_cur (c_wip_entity_id IN NUMBER,
4753 c_dept_id IN NUMBER)
4754 IS
4755 SELECT wip_entity_id
4756 FROM wip_operations
4757 WHERE wip_entity_id = c_wip_entity_id
4758 AND department_id = c_dept_id;
4759 --
4760 CURSOR wip_res_cur (c_wip_entity_id IN NUMBER,
4761 c_resource_id IN NUMBER)
4762 IS
4763 SELECT SUM(assigned_units)
4764 FROM wip_operation_resources
4765 WHERE wip_entity_id = c_wip_entity_id
4766 AND resource_id = c_resource_id;
4767 l_wip_entity_id NUMBER;
4768 l_wip_op_entity_id NUMBER;
4769 l_assigned_units NUMBER;
4770 BEGIN
4771 --
4772 OPEN wip_disc_cur(p_org_id);
4773 LOOP
4774 FETCH wip_disc_cur INTO l_wip_entity_id;
4775 EXIT WHEN wip_disc_cur%NOTFOUND;
4776 IF l_wip_entity_id IS NOT NULL THEN
4777 OPEN wip_oper_cur(l_wip_entity_id,p_bom_dept_id);
4778 LOOP
4779 FETCH wip_oper_cur INTO l_wip_op_entity_id;
4780 EXIT WHEN wip_oper_cur%NOTFOUND;
4781 IF wip_oper_cur%FOUND THEN
4782 OPEN wip_res_cur(l_wip_op_entity_id,p_bom_resource_id);
4783 FETCH wip_res_cur INTO x_assigned_units;
4784 CLOSE wip_res_cur;
4785 END IF;
4786 END LOOP;
4787 CLOSE wip_oper_cur;
4788 END IF;
4789 END LOOP;
4790 CLOSE wip_disc_cur;
4791 END;
4792 */
4793 --
4794 PROCEDURE GET_WIP_DISC_ASSIGN_UNITS(
4795 p_bom_resource_id IN NUMBER,
4796 p_start_date IN DATE,
4797 p_end_date IN DATE,
4798 x_assigned_persons OUT NOCOPY NUMBER)
4799 IS
4800 CURSOR get_per_ins_cur(c_resource_id IN NUMBER)
4801 IS
4802 SELECT person_id,
4803 instance_id,
4804 effective_start_date,effective_end_date
4805 FROM bom_resource_employees
4806 WHERE resource_id = c_resource_id;
4807 --
4808 CURSOR get_assign_per_cur(c_instance_id IN NUMBER,
4809 c_start_date IN DATE,
4810 c_end_date IN DATE)
4811 IS
4812 SELECT COUNT(*)
4813 FROM wip_op_resource_instances
4814 WHERE instance_id = c_instance_id
4815 AND (c_start_date BETWEEN start_date AND
4816 completion_date) OR
4817 (c_end_date BETWEEN start_date AND
4818 completion_date);
4819
4820 l_get_per_ins_rec get_per_ins_cur%ROWTYPE;
4821 l_dummy NUMBER;
4822 --
4823 BEGIN
4824 OPEN get_per_ins_cur(p_bom_resource_id);
4825 LOOP
4826 FETCH get_per_ins_cur INTO l_get_per_ins_rec;
4827 EXIT WHEN get_per_ins_cur%NOTFOUND;
4828 IF l_get_per_ins_rec.instance_id IS NOT NULL
4829 THEN
4830 OPEN get_assign_per_cur(l_get_per_ins_rec.instance_id,
4831 p_start_date,p_end_date);
4832 FETCH get_assign_per_cur INTO l_dummy;
4833 CLOSE get_assign_per_cur;
4834 END IF;
4835 x_assigned_persons := l_dummy;
4836 END LOOP;
4837 CLOSE get_per_ins_cur;
4838 END;
4839
4840 -- JARAMANA 24Aug2005 starts
4841 -- Resource Leveling Re-design
4842
4843 -- Gets the (unique) ids of all the resources used in the
4844 -- given department and in the given time range by the given resource requirements.
4845 PROCEDURE Get_Used_Resources
4846 (
4847 p_dept_id IN NUMBER,
4848 p_start_date IN DATE,
4849 p_end_date IN DATE,
4850 p_tp_dtls_tbl IN Time_Period_Details_Tbl_Type,
4851 x_resources_tbl OUT NOCOPY Resource_Tbl_Type
4852 ) IS
4853 l_temp_index NUMBER := 1;
4854 l_temp_index2 NUMBER;
4855 l_temp_num_tbl Resource_Tbl_Type;
4856 BEGIN
4857 IF (p_tp_dtls_tbl.COUNT > 0) THEN
4858 FOR i IN p_tp_dtls_tbl.FIRST..p_tp_dtls_tbl.LAST LOOP
4859 IF (p_tp_dtls_tbl(i).DEPARTMENT_ID = p_dept_id AND
4860 Periods_Overlap(p_tp_dtls_tbl(i).START_TIME, p_tp_dtls_tbl(i).END_TIME,
4861 p_start_date, p_end_date)) THEN
4862 -- This reseource is required in the given dept during the given time range
4863 l_temp_num_tbl(p_tp_dtls_tbl(i).BOM_RESOURCE_ID) := p_tp_dtls_tbl(i).BOM_RESOURCE_ID;
4864 END IF;
4865 END LOOP;
4866
4867 -- Transfer from the associative array to the 1 based output table
4868 IF (l_temp_num_tbl.COUNT > 0) THEN
4869 l_temp_index2 := l_temp_num_tbl.FIRST;
4870 WHILE (l_temp_index2 IS NOT NULL) LOOP
4871 x_resources_tbl(l_temp_index) := l_temp_index2;
4872 l_temp_index := l_temp_index + 1;
4873 l_temp_index2 := l_temp_num_tbl.NEXT(l_temp_index2);
4874 END LOOP;
4875 END IF;
4876 END IF;
4877 END Get_Used_Resources;
4878
4879 PROCEDURE Append_WIP_Requirements
4880 (
4881 p_org_id IN NUMBER,
4882 p_dept_id IN NUMBER,
4883 p_start_date IN DATE,
4884 p_end_date IN DATE,
4885 p_resource_id IN NUMBER,
4886 p_x_tp_dtls_tbl IN OUT NOCOPY Time_Period_Details_Tbl_Type
4887 ) IS
4888 CURSOR get_wip_req_dtls_csr IS
4889 SELECT wor.start_date, wor.completion_date, wor.assigned_units, wor.wip_entity_id
4890 FROM wip_operation_resources wor, wip_operations wo, wip_discrete_jobs wdj,
4891 AHL_WORKORDERS aw
4892 WHERE wor.resource_id = p_resource_id
4893 AND wo.department_id = p_dept_id
4894 AND wo.wip_entity_id = wor.wip_entity_id
4895 AND wdj.WIP_ENTITY_ID = aw.WIP_ENTITY_ID
4896 AND aw.STATUS_CODE not in ('17','22','7','12','18','4','5')
4897 --(17-Draft, 22-Deleted, 7-Cancelled, 12-Closed, 18-Deferrred, 4-Complete, 5-Complete No-charge)
4898 AND wdj.organization_id = p_org_id
4899 AND wdj.wip_entity_id = wo.wip_entity_id
4900 AND ((wor.start_date BETWEEN p_start_date and p_end_date) OR
4901 (wor.completion_date BETWEEN p_start_date and p_end_date) OR
4902 (wor.start_date < p_start_date AND wor.completion_date > p_end_date));
4903
4904 l_temp_index NUMBER := p_x_tp_dtls_tbl.LAST + 1;
4905 l_api_name CONSTANT VARCHAR2(30) := 'Append_WIP_Requirements';
4906
4907 BEGIN
4908 FOR wip_req_dtls IN get_wip_req_dtls_csr LOOP
4909 p_x_tp_dtls_tbl(l_temp_index).START_TIME := wip_req_dtls.start_date;
4910 p_x_tp_dtls_tbl(l_temp_index).END_TIME := wip_req_dtls.completion_date;
4911 p_x_tp_dtls_tbl(l_temp_index).QUANTITY := wip_req_dtls.assigned_units;
4912 -- None of the other fields in the p_x_tp_dtls_tbl(l_temp_index) record
4913 -- are populated since they are not relevant.
4914
4915 IF (l_log_statement >= l_log_current_level) THEN
4916 fnd_log.string
4917 (
4918 fnd_log.level_statement,
4919 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
4920 'wip_entity_id: '|| wip_req_dtls.wip_entity_id
4921 );
4922 fnd_log.string
4923 (
4924 fnd_log.level_statement,
4925 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
4926 'p_x_tp_dtls_tbl('||l_temp_index||').start_time: '|| TO_CHAR(p_x_tp_dtls_tbl(l_temp_index).start_time, 'DD-MON-YYYY hh24:mi')
4927 );
4928 fnd_log.string
4929 (
4930 fnd_log.level_statement,
4931 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
4932 'p_x_tp_dtls_tbl('||l_temp_index||').end_time: '|| TO_CHAR( p_x_tp_dtls_tbl(l_temp_index).end_time, 'DD-MON-YYYY hh24:mi')
4933 );
4934 fnd_log.string
4935 (
4936 fnd_log.level_statement,
4937 'ahl.plsql.'||g_pkg_name||'.'||l_api_name,
4938 'p_x_tp_dtls_tbl('||l_temp_index||').quantity: '|| p_x_tp_dtls_tbl(l_temp_index).quantity
4939 );
4940
4941 END IF;
4942
4943 l_temp_index := l_temp_index + 1;
4944 END LOOP;
4945
4946 END Append_WIP_Requirements;
4947
4948 -- JARAMANA 24Aug2005 ends
4949 --
4950 END Ahl_Ltp_Resrc_Levl_Pvt;
[Home] [Help]