[Home] [Help]
PACKAGE BODY: APPS.AHL_PP_RESRC_ASSIGN_PVT
Source
1 PACKAGE BODY AHL_PP_RESRC_ASSIGN_PVT AS
2 /* $Header: AHLVASGB.pls 120.11.12020000.2 2012/12/11 03:17:00 prakkum ship $*/
3
4 -- Declare Constants --
5 -----------------------
6 G_PKG_NAME VARCHAR2(30) := 'AHL_PP_RESRC_ASSIGN_PVT';
7 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
8 -------------------------------------------------
9 -- Declare Locally used Record and Table Types --
10 -------------------------------------------------
11
12 -------------------------------------------------
13 -- Declare Local Procedures --
14 -------------------------------------------------
15
16 --------------------------------------------------------------------
17 -- PROCEDURE
18 -- Check_Lookup_Name_Or_Id
19 --
20 -- PURPOSE
21 -- Converts Lookup Name/Code to ID/Value or Vice versa
22 --------------------------------------------------------------------
23 PROCEDURE Check_Lookup_Name_Or_Id
24 ( p_lookup_type IN MFG_LOOKUPS.lookup_type%TYPE,
25 p_lookup_code IN MFG_LOOKUPS.lookup_code%TYPE,
26 p_meaning IN MFG_LOOKUPS.meaning%TYPE,
27 p_check_id_flag IN VARCHAR2,
28
29 x_lookup_code OUT NOCOPY NUMBER,
30 x_return_status OUT NOCOPY VARCHAR2)
31 IS
32 BEGIN
33 IF (p_lookup_code IS NOT NULL) THEN
34 IF (p_check_id_flag = 'Y') THEN
35 SELECT lookup_code INTO x_lookup_code
36 FROM MFG_LOOKUPS
37 WHERE lookup_type = p_lookup_type
38 AND lookup_code = p_lookup_code
39 AND SYSDATE BETWEEN start_date_active
40 AND NVL(end_date_active,SYSDATE);
41 ELSE
42 x_lookup_code := p_lookup_code;
43 END IF;
44 ELSE
45 SELECT lookup_code INTO x_lookup_code
46 FROM MFG_LOOKUPS
47 WHERE lookup_type = p_lookup_type
48 AND meaning = p_meaning
49 AND SYSDATE BETWEEN start_date_active
50 AND NVL(end_date_active,SYSDATE);
51 END IF;
52
53 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
54 EXCEPTION
55 WHEN NO_DATA_FOUND THEN
56 x_return_status := Fnd_Api.G_RET_STS_ERROR;
57 WHEN TOO_MANY_ROWS THEN
58 x_return_status := Fnd_Api.G_RET_STS_ERROR;
59 WHEN OTHERS THEN
60 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
61 RAISE;
62 END;
63
64 --------------------------------------------------------------------
65 -- PROCEDURE
66 -- Check_Serial_Name_Or_Id
67 --
68 -- PURPOSE
69 -- Converts Serial Name to ID or Vice versa
70 --------------------------------------------------------------------
71 PROCEDURE Check_Serial_Name_Or_Id
72 (p_serial_id IN NUMBER,
73 p_serial_number IN VARCHAR2,
74 p_workorder_id IN NUMBER,
75 p_resource_id IN NUMBER,
76 p_dept_id IN NUMBER,
77 p_organization_id IN NUMBER,
78 x_instance_id OUT NOCOPY NUMBER,
79 x_return_status OUT NOCOPY VARCHAR2,
80 x_error_msg_code OUT NOCOPY VARCHAR2
81 )
82 IS
83 BEGIN
84 IF G_DEBUG='Y' THEN
85 Ahl_Debug_Pub.debug( ': Inside Check Serial Number= ' || p_serial_number);
86 END IF;
87
88 IF (p_serial_number IS NOT NULL) THEN
89 /*SELECT DISTINCT(instance_id)
90 INTO x_serial_id
91 FROM BOM_DEPT_RES_INSTANCES BDRI, AHL_OPERATION_RESOURCES AOR, AHL_WORKORDERS_V AWO
92 WHERE BDRI.resource_id = AOR.resource_id
93 AND BDRI.department_id = AWO.department_id
94 AND AWO.workorder_id = p_workorder_id
95 AND AOR.operation_resource_id = p_oper_resrc_id
96 AND BDRI.serial_number = p_serial_number;*/
97
98 SELECT INSTANCE_ID INTO x_instance_id
99 FROM BOM_DEPT_RES_INSTANCES BDRI
100 WHERE BDRI.resource_id = p_resource_id
101 AND BDRI.serial_number = p_serial_number
102 AND BDRI.department_id in (
103 SELECT
104 nvl(bdr.SHARE_FROM_DEPT_ID,
105 bdr.department_id)
106 FROM
107 bom_departments bd,
108 bom_department_resources bdr
109 WHERE
110 bdr.resource_id = p_resource_id and
111 bdr.department_id = bd.department_id and
112 bd.organization_id = p_organization_id
113 );
114 END IF;
115
116 IF G_DEBUG='Y' THEN
117 Ahl_Debug_Pub.debug(': Inside Check Serial Number = ' || x_instance_id);
118 END IF;
119
120 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
121 EXCEPTION
122 WHEN NO_DATA_FOUND THEN
123 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
124 x_error_msg_code:= 'AHL_PP_SERIAL_NOT_EXISTS';
125 WHEN TOO_MANY_ROWS THEN
126 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
127 x_error_msg_code:= 'AHL_PP_SERIAL_NOT_EXISTS';
128 WHEN OTHERS THEN
129 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
130 RAISE;
131 END Check_Serial_Name_Or_Id;
132
133 --------------------------------------------------------------------
134 -- PROCEDURE
135 -- Check_Employee_Number_Or_Id
136 --
137 -- PURPOSE
138 -- Converts Employee Number to ID or Vice versa
139 --------------------------------------------------------------------
140 PROCEDURE Check_Employee_Number_Or_Id
141 (p_employee_id IN NUMBER,
142 p_employee_number IN VARCHAR,
143 p_workorder_id IN NUMBER,
144 p_oper_resrc_id IN NUMBER,
145 p_resource_id IN NUMBER,
146 p_organization_id IN NUMBER,
147 x_employee_id OUT NOCOPY NUMBER,
148 x_return_status OUT NOCOPY VARCHAR2,
149 x_error_msg_code OUT NOCOPY VARCHAR2
150 )
151 IS
152 BEGIN
153 IF (p_employee_number IS NOT NULL) THEN
154 /*SELECT DISTINCT(PPF.PERSON_ID)
155 INTO x_employee_id
156 FROM PER_PEOPLE_F PPF, BOM_RESOURCE_EMPLOYEES BRE, AHL_OPERATION_RESOURCES AOR,
157 PER_PERSON_TYPES PEPT, AHL_WORKORDERS AWV, AHL_VISITS_B VTB
158 WHERE PPF.PERSON_ID = BRE.PERSON_ID AND BRE.RESOURCE_ID = AOR.RESOURCE_ID
159 AND AWV.VISIT_ID = VTB.VISIT_ID
160 AND BRE.ORGANIZATION_ID = VTB.ORGANIZATION_ID
161 AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
162 AND NVL(PPF.CURRENT_EMPLOYEE_FLAG, 'X') = 'Y' AND PEPT.PERSON_TYPE_ID = PPF.PERSON_TYPE_ID
163 AND PEPT.SYSTEM_PERSON_TYPE ='EMP' AND AOR.operation_resource_id = p_oper_resrc_id
164 AND PPF.EMPLOYEE_NUMBER = p_employee_number AND AWV.WORKORDER_ID = p_workorder_id;*/
165
166 -- bug# 4553747.
167 -- assignments are only allowed for employees in the WOs dept.
168 SELECT DISTINCT(PF.employee_id)
169 INTO x_employee_id
170 FROM
171 bom_dept_res_instances bdri,
172 wip_operation_resources wor,
173 wip_operations wo
174 ,ahl_workorders awo, ahl_operation_resources aor
175 , mtl_employees_current_view pf
176 ,bom_resource_employees bre
177 where awo.wip_entity_id = wor.wip_entity_id
178 and awo.workorder_id = p_workorder_id
179 and aor.operation_resource_id = p_oper_resrc_id
180 and wor.resource_seq_num = aor.resource_sequence_num
181 and wor.resource_id = aor.resource_id
182 and bdri.department_id in (
183 SELECT
184 nvl(bdr.SHARE_FROM_DEPT_ID,
185 bdr.department_id)
186 FROM
187 bom_departments bd,
188 bom_department_resources bdr
189 --ahl_pp_requirement_v aprv
190 --Removed since The view is not being used.
191 --for bug #9031320
192 WHERE
193 bdr.resource_id = p_resource_id and
194 bdr.department_id = bd.department_id and
195 bd.organization_id = p_organization_id
196 )
197 and bdri.resource_id = wor.resource_id
198 and wo.wip_entity_id = wor.wip_entity_id
199 and wo.organization_id = wor.organization_id
200 and wo.operation_seq_num = wor.operation_seq_num
201 and pf.employee_id = bre.person_id
202 and pf.organization_id = bre.organization_id
203 and bre.instance_id = bdri.instance_id
204 and pf.employee_num = p_employee_number;
205
206 END IF;
207
208 IF G_DEBUG='Y' THEN
209 Ahl_Debug_Pub.debug(': Inside Check Employee Id= ' || x_Employee_id);
210 END IF;
211
212 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
213 EXCEPTION
214 WHEN NO_DATA_FOUND THEN
215 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
216 x_error_msg_code:= 'AHL_VWP_EMPLOYEE_NOT_EXISTS';
217 WHEN TOO_MANY_ROWS THEN
218 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
219 x_error_msg_code:= 'AHL_VWP_EMPLOYEE_NOT_EXISTS';
220 WHEN OTHERS THEN
221 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
222 RAISE;
223 END Check_Employee_Number_Or_Id;
224
225 --------------------------------------------------------------------
226 -- PROCEDURE
227 -- Check_Employee_Name_Or_Id
228 --
229 -- PURPOSE
230 -- Converts Employee Name to ID or Vice versa
231 --------------------------------------------------------------------
232 PROCEDURE Check_Employee_Name_Or_Id
233 (p_Employee_Id IN NUMBER,
234 p_employee_number IN VARCHAR2,
235 p_workorder_id IN NUMBER,
236 p_oper_resrc_id IN NUMBER,
237 p_resource_id IN NUMBER,
238 p_organization_id IN NUMBER,
239 x_employee_name OUT NOCOPY VARCHAR2,
240 x_employee_id OUT NOCOPY NUMBER,
241 x_return_status OUT NOCOPY VARCHAR2,
242 x_error_msg_code OUT NOCOPY VARCHAR2
243 )
244 IS
245 BEGIN
246
247 IF (p_Employee_Number IS NOT NULL) THEN
248 /*SELECT PPF.PERSON_ID, PPF.FULL_NAME
249 INTO x_employee_id, x_employee_name
250 FROM PER_PEOPLE_F PPF,
251 BOM_RESOURCE_EMPLOYEES BRE,
252 AHL_OPERATION_RESOURCES AOR,
253 PER_PERSON_TYPES PEPT,
254 AHL_WORKORDERS AWV,
255 AHL_VISITS_B VTB
256 WHERE PPF.PERSON_ID = BRE.PERSON_ID
257 AND BRE.resource_id = AOR.resource_id
258 AND TRUNC(SYSDATE) BETWEEN PPF.EFFECTIVE_START_DATE AND PPF.EFFECTIVE_END_DATE
259 AND AWV.VISIT_ID = VTB.VISIT_ID
260 AND BRE.ORGANIZATION_ID = VTB.ORGANIZATION_ID
261 AND NVL(PPF.CURRENT_EMPLOYEE_FLAG, 'X') = 'Y'
262 AND PEPT.PERSON_TYPE_ID = PPF.PERSON_TYPE_ID
263 AND PEPT.SYSTEM_PERSON_TYPE ='EMP'
264 AND AOR.operation_resource_id = p_oper_resrc_id
265 AND PPF.EMPLOYEE_NUMBER = p_employee_number
266 AND AWV.WORKORDER_ID = p_workorder_id;*/
267
268 -- bug# 4553747.
269 -- assignments are only allowed for employees in the WOs dept.
270 select distinct pf.employee_id, pf.full_name
271 INTO x_employee_id, x_employee_name
272 from
273 bom_dept_res_instances bdri,
274 wip_operation_resources wor,
275 wip_operations wo
276 ,ahl_workorders awo, ahl_operation_resources aor
277 , mtl_employees_current_view pf
278 ,bom_resource_employees bre
279 where awo.wip_entity_id = wor.wip_entity_id
280 and awo.workorder_id = p_workorder_id
281 and aor.operation_resource_id = p_oper_resrc_id
282 and wor.resource_seq_num = aor.resource_sequence_num
283 and wor.resource_id = aor.resource_id
284 and bdri.department_id in (
285 SELECT
286 nvl(bdr.SHARE_FROM_DEPT_ID,
287 bdr.department_id)
288 FROM
289 bom_departments bd,
290 bom_department_resources bdr
291 WHERE
292 bdr.resource_id = p_resource_id and
293 bdr.department_id = bd.department_id and
294 bd.organization_id = p_organization_id
295 )
296 and bdri.resource_id = wor.resource_id
297 and wo.wip_entity_id = wor.wip_entity_id
298 and wo.organization_id = wor.organization_id
299 and wo.operation_seq_num = wor.operation_seq_num
300 and pf.employee_id = bre.person_id
301 and pf.organization_id = bre.organization_id
302 and bre.instance_id = bdri.instance_id
303 and pf.employee_num = p_employee_number;
304
305 END IF;
306 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
307
308 EXCEPTION
309 WHEN NO_DATA_FOUND THEN
310 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
311 x_error_msg_code:= 'AHL_VWP_EMPLOYEE_NOT_EXISTS';
312 WHEN TOO_MANY_ROWS THEN
313 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
314 x_error_msg_code:= 'AHL_VWP_EMPLOYEE_NOT_EXISTS';
315 WHEN OTHERS THEN
316 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
317 RAISE;
318 END Check_Employee_Name_Or_Id;
319
320 ---------------------------------------------------------------------
321 -- PROCEDURE
322 -- Insert_Row
323 ---------------------------------------------------------------------
324 PROCEDURE Insert_Row (
325 X_ASSIGNMENT_ID IN NUMBER,
326 X_OBJECT_VERSION_NUMBER IN NUMBER,
327 X_LAST_UPDATE_DATE IN DATE,
328 X_LAST_UPDATED_BY IN NUMBER,
329 X_CREATION_DATE IN DATE,
330 X_CREATED_BY IN NUMBER,
331 X_LAST_UPDATE_LOGIN IN NUMBER,
332 X_OPERATION_RESOURCE_ID IN NUMBER,
333 X_EMPLOYEE_ID IN NUMBER,
334 X_SERIAL_NUMBER IN VARCHAR2,
335 X_INSTANCE_ID IN NUMBER,
336 X_ASSIGN_START_DATE IN DATE,
337 X_ASSIGN_END_DATE IN DATE,
338 X_SELF_ASSIGNED_FLAG IN VARCHAR2,
339 --X_LOGIN_DATE IN DATE,
340 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
341 X_ATTRIBUTE1 IN VARCHAR2,
342 X_ATTRIBUTE2 IN VARCHAR2,
343 X_ATTRIBUTE3 IN VARCHAR2,
344 X_ATTRIBUTE4 IN VARCHAR2,
345 X_ATTRIBUTE5 IN VARCHAR2,
346 X_ATTRIBUTE6 IN VARCHAR2,
347 X_ATTRIBUTE7 IN VARCHAR2,
348 X_ATTRIBUTE8 IN VARCHAR2,
349 X_ATTRIBUTE9 IN VARCHAR2,
350 X_ATTRIBUTE10 IN VARCHAR2,
351 X_ATTRIBUTE11 IN VARCHAR2,
352 X_ATTRIBUTE12 IN VARCHAR2,
353 X_ATTRIBUTE13 IN VARCHAR2,
354 X_ATTRIBUTE14 IN VARCHAR2,
355 X_ATTRIBUTE15 IN VARCHAR2
356 )
357 IS
358 BEGIN
359 INSERT INTO AHL_WORK_ASSIGNMENTS (
360 ASSIGNMENT_ID,
361 OBJECT_VERSION_NUMBER,
362 LAST_UPDATE_DATE,
363 LAST_UPDATED_BY,
364 CREATION_DATE,
365 CREATED_BY,
366 LAST_UPDATE_LOGIN,
367 OPERATION_RESOURCE_ID,
368 EMPLOYEE_ID,
369 SERIAL_NUMBER,
370 INSTANCE_ID,
371 ASSIGN_START_DATE,
372 ASSIGN_END_DATE,
373 --LOGIN_DATE,
374 SELF_ASSIGNED_FLAG,
375 ATTRIBUTE_CATEGORY,
376 ATTRIBUTE1,
377 ATTRIBUTE2,
378 ATTRIBUTE3,
379 ATTRIBUTE4,
380 ATTRIBUTE5,
381 ATTRIBUTE6,
382 ATTRIBUTE7,
383 ATTRIBUTE8,
384 ATTRIBUTE9,
385 ATTRIBUTE10,
386 ATTRIBUTE11,
387 ATTRIBUTE12,
388 ATTRIBUTE13,
389 ATTRIBUTE14,
390 ATTRIBUTE15
391 )
392 VALUES(
393 X_ASSIGNMENT_ID,
394 X_OBJECT_VERSION_NUMBER,
395 X_LAST_UPDATE_DATE,
396 X_LAST_UPDATED_BY,
397 X_CREATION_DATE,
398 X_CREATED_BY,
399 X_LAST_UPDATE_LOGIN,
400 X_OPERATION_RESOURCE_ID,
401 X_EMPLOYEE_ID,
402 X_SERIAL_NUMBER,
403 X_INSTANCE_ID,
404 X_ASSIGN_START_DATE,
405 X_ASSIGN_END_DATE,
406 X_SELF_ASSIGNED_FLAG,
407 --X_LOGIN_DATE,
408 X_ATTRIBUTE_CATEGORY,
409 X_ATTRIBUTE1,
410 X_ATTRIBUTE2,
411 X_ATTRIBUTE3,
412 X_ATTRIBUTE4,
413 X_ATTRIBUTE5,
414 X_ATTRIBUTE6,
415 X_ATTRIBUTE7,
416 X_ATTRIBUTE8,
417 X_ATTRIBUTE9,
418 X_ATTRIBUTE10,
419 X_ATTRIBUTE11,
420 X_ATTRIBUTE12,
421 X_ATTRIBUTE13,
422 X_ATTRIBUTE14,
423 X_ATTRIBUTE15);
424
425 END Insert_Row;
426
427 ---------------------------------------------------------------------
428 -- PROCEDURE
429 -- Update_Row
430 ---------------------------------------------------------------------
431 PROCEDURE UPDATE_ROW (
432 X_ASSIGNMENT_ID IN NUMBER,
433 X_OBJECT_VERSION_NUMBER IN NUMBER,
434 X_OPERATION_RESOURCE_ID IN NUMBER,
435 X_EMPLOYEE_ID IN NUMBER,
436 X_SERIAL_NUMBER IN VARCHAR2,
437 X_INSTANCE_ID IN NUMBER,
438 X_ASSIGN_START_DATE IN DATE,
439 X_ASSIGN_END_DATE IN DATE,
440 X_SELF_ASSIGNED_FLAG IN VARCHAR2,
441 --X_LOGIN_DATE IN DATE,
442 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
443 X_ATTRIBUTE1 IN VARCHAR2,
444 X_ATTRIBUTE2 IN VARCHAR2,
445 X_ATTRIBUTE3 IN VARCHAR2,
446 X_ATTRIBUTE4 IN VARCHAR2,
447 X_ATTRIBUTE5 IN VARCHAR2,
448 X_ATTRIBUTE6 IN VARCHAR2,
449 X_ATTRIBUTE7 IN VARCHAR2,
450 X_ATTRIBUTE8 IN VARCHAR2,
451 X_ATTRIBUTE9 IN VARCHAR2,
452 X_ATTRIBUTE10 IN VARCHAR2,
453 X_ATTRIBUTE11 IN VARCHAR2,
454 X_ATTRIBUTE12 IN VARCHAR2,
455 X_ATTRIBUTE13 IN VARCHAR2,
456 X_ATTRIBUTE14 IN VARCHAR2,
457 X_ATTRIBUTE15 IN VARCHAR2,
458 X_LAST_UPDATE_DATE IN DATE,
459 X_LAST_UPDATED_BY IN NUMBER,
460 X_LAST_UPDATE_LOGIN IN NUMBER
461 )
462 IS
463
464 BEGIN
465 UPDATE AHL_WORK_ASSIGNMENTS SET
466 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER + 1,
467 ASSIGNMENT_ID = X_ASSIGNMENT_ID,
468 OPERATION_RESOURCE_ID = X_OPERATION_RESOURCE_ID,
469 EMPLOYEE_ID = X_EMPLOYEE_ID,
470 SERIAL_NUMBER = X_SERIAL_NUMBER,
471 INSTANCE_ID = X_INSTANCE_ID,
472 ASSIGN_START_DATE = X_ASSIGN_START_DATE,
473 ASSIGN_END_DATE = X_ASSIGN_END_DATE,
474 SELF_ASSIGNED_FLAG = X_SELF_ASSIGNED_FLAG,
475 --LOGIN_DATE = X_LOGIN_DATE,
476 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
477 ATTRIBUTE1 = X_ATTRIBUTE1,
478 ATTRIBUTE2 = X_ATTRIBUTE2,
479 ATTRIBUTE3 = X_ATTRIBUTE3,
480 ATTRIBUTE4 = X_ATTRIBUTE4,
481 ATTRIBUTE5 = X_ATTRIBUTE5,
482 ATTRIBUTE6 = X_ATTRIBUTE6,
483 ATTRIBUTE7 = X_ATTRIBUTE7,
484 ATTRIBUTE8 = X_ATTRIBUTE8,
485 ATTRIBUTE9 = X_ATTRIBUTE9,
486 ATTRIBUTE10 = X_ATTRIBUTE10,
487 ATTRIBUTE11 = X_ATTRIBUTE11,
488 ATTRIBUTE12 = X_ATTRIBUTE12,
489 ATTRIBUTE13 = X_ATTRIBUTE13,
490 ATTRIBUTE14 = X_ATTRIBUTE14,
491 ATTRIBUTE15 = X_ATTRIBUTE15,
492 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
493 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
494 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
495 WHERE ASSIGNMENT_ID = X_ASSIGNMENT_ID
496 AND OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER;
497
498 IF G_DEBUG='Y' THEN
499 AHL_DEBUG_PUB.debug(' Inside Update Row procedure');
500 AHL_DEBUG_PUB.debug(' Assign ID = ' || X_ASSIGNMENT_ID);
501 AHL_DEBUG_PUB.debug(' Object version = ' || X_OBJECT_VERSION_NUMBER);
502 AHL_DEBUG_PUB.debug(' Assign Start Date = ' || x_assign_start_date);
503 AHL_DEBUG_PUB.debug(' Assign End Date = ' || x_assign_end_date);
504 END IF;
505
506 /*IF SQL%rowcount = 0 THEN
507 Fnd_Message.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
508 Fnd_Msg_Pub.ADD;
509 END IF;*/
510 END UPDATE_ROW;
511
512 ---------------------------------------------------------------------
513 -- PROCEDURE
514 -- Delete_Row
515 ---------------------------------------------------------------------
516 PROCEDURE DELETE_ROW (
517 X_ASSIGNMENT_ID IN NUMBER
518 ) IS
519 BEGIN
520 DELETE FROM AHL_WORK_ASSIGNMENTS
521 WHERE ASSIGNMENT_ID = X_ASSIGNMENT_ID;
522 END DELETE_ROW;
523
524 ---------------------------------------------------------------------
525 -- PROCEDURE
526 -- Check_Resrc_Assign_Req_Items
527 ---------------------------------------------------------------------
528 PROCEDURE Check_Resrc_Assign_Req_Items (
529 p_resrc_assign_rec IN Resrc_Assign_Rec_Type,
530 x_return_status OUT NOCOPY VARCHAR2
531 )
532 IS
533 BEGIN
534 IF G_DEBUG='Y' THEN
535 Ahl_Debug_Pub.debug( ': ASSIGNMENT_ID = ' || p_resrc_assign_rec.ASSIGNMENT_ID);
536 Ahl_Debug_Pub.debug( ': OPERATION_SEQ_NUMBER = ' || p_resrc_assign_rec.OPERATION_SEQ_NUMBER);
537 Ahl_Debug_Pub.debug( ': RESOURCE_SEQ_NUMBER = ' || p_resrc_assign_rec.RESOURCE_SEQ_NUMBER);
538 END IF;
539
540 IF (p_resrc_assign_rec.ASSIGNMENT_ID IS NULL OR p_resrc_assign_rec.ASSIGNMENT_ID = Fnd_Api.G_MISS_NUM) THEN
541 -- OPERATION_SEQ_NUMBER
542 IF (p_resrc_assign_rec.OPERATION_SEQ_NUMBER IS NULL OR p_resrc_assign_rec.OPERATION_SEQ_NUMBER = Fnd_Api.G_MISS_NUM) THEN
543 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
544 Fnd_Message.set_name ('AHL', 'AHL_PP_OPER_SEQ_MISSING');
545 Fnd_Msg_Pub.ADD;
546 END IF;
547 x_return_status := Fnd_Api.g_ret_sts_error;
548 RETURN;
549 END IF;
550
551 -- OPERATION_SEQ_NUMBER - Positive
552 IF (p_resrc_assign_rec.OPERATION_SEQ_NUMBER IS NOT NULL AND p_resrc_assign_rec.OPERATION_SEQ_NUMBER <> Fnd_Api.G_MISS_NUM) THEN
553 IF p_resrc_assign_rec.OPERATION_SEQ_NUMBER < 0 THEN
554 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
555 Fnd_Message.set_name ('AHL', 'AHL_PP_ONLY_POSITIVE_VALUE');
556 Fnd_Msg_Pub.ADD;
557 END IF;
558 x_return_status := Fnd_Api.g_ret_sts_error;
559 RETURN;
560 END IF;
561 END IF;
562
563 -- RESOURCE_SEQ_NUMBER
564 IF (p_resrc_assign_rec.RESOURCE_SEQ_NUMBER IS NULL OR p_resrc_assign_rec.RESOURCE_SEQ_NUMBER = Fnd_Api.G_MISS_NUM) THEN
565 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
566 Fnd_Message.set_name ('AHL', 'AHL_PP_RESRC_SEQ_MISSING');
567 Fnd_Msg_Pub.ADD;
568 RAISE FND_API.G_EXC_ERROR;
569 END IF;
570 x_return_status := Fnd_Api.g_ret_sts_error;
571 RETURN;
572 END IF;
573
574 -- RESOURCE_SEQ_NUMBER - Positive
575 IF (p_resrc_assign_rec.RESOURCE_SEQ_NUMBER IS NOT NULL AND p_resrc_assign_rec.RESOURCE_SEQ_NUMBER <> Fnd_Api.G_MISS_NUM) THEN
576 IF p_resrc_assign_rec.RESOURCE_SEQ_NUMBER < 0 THEN
577 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
578 Fnd_Message.set_name ('AHL', 'AHL_PP_ONLY_POSITIVE_VALUE');
579 Fnd_Msg_Pub.ADD;
580 END IF;
581 x_return_status := Fnd_Api.g_ret_sts_error;
582 RETURN;
583 END IF;
584 END IF;
585 END IF;
586 IF G_DEBUG='Y' THEN
587 Ahl_Debug_Pub.debug( ': Resource Type = ' || p_resrc_assign_rec.RESOURCE_TYPE_CODE);
588 Ahl_Debug_Pub.debug( ': Employee Name = ' || p_resrc_assign_rec.employee_name);
589 Ahl_Debug_Pub.debug( ': EMployee Number = ' || p_resrc_assign_rec.employee_number);
590 END IF;
591
592 IF p_resrc_assign_rec.RESOURCE_TYPE_CODE = 2 THEN
593 -- EMPLOYEE_NUMBER
594 IF G_DEBUG='Y' THEN
595 Ahl_Debug_Pub.debug( ': Resource Type 11111= ' || p_resrc_assign_rec.RESOURCE_TYPE_CODE);
596 END IF;
597
598 --IF (p_resrc_assign_rec.employee_id IS NULL) THEN
599 /*
600 IF (p_resrc_assign_rec.EMPLOYEE_NUMBER IS NULL OR p_resrc_assign_rec.EMPLOYEE_NUMBER = Fnd_Api.G_MISS_CHAR) THEN
601 IF G_DEBUG='Y' THEN
602 Ahl_Debug_Pub.debug( ': Resource Type 22222= ' || p_resrc_assign_rec.RESOURCE_TYPE_CODE);
603 END IF;
604
605 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
606 Fnd_Message.set_name ('AHL', 'AHL_PP_EMP_NUM_MISSING');
607 Fnd_Msg_Pub.ADD;
608 END IF;
609 x_return_status := Fnd_Api.g_ret_sts_error;
610 RETURN;
611 END IF; */
612 --END IF; -- p_resrc_assign_rec.employee_id IS NULL
613 IF (p_resrc_assign_rec.EMPLOYEE_ID IS NULL OR p_resrc_assign_rec.EMPLOYEE_ID = Fnd_Api.G_MISS_NUM) THEN
614 IF G_DEBUG='Y' THEN
615 Ahl_Debug_Pub.debug( ': Resource Type 22222= ' || p_resrc_assign_rec.RESOURCE_TYPE_CODE);
616 END IF;
617
618 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
619 Fnd_Message.set_name ('AHL', 'AHL_PP_EMP_NUM_MISSING');
620 Fnd_Msg_Pub.ADD;
621 END IF;
622 x_return_status := Fnd_Api.g_ret_sts_error;
623 RETURN;
624 END IF;
625 END IF; -- p_resrc_assign_rec.RESOURCE_TYPE_CODE
626
627 IF p_resrc_assign_rec.RESOURCE_TYPE_CODE <> 2 THEN
628 -- SERIAL NUMBER
629 IF (p_resrc_assign_rec.SERIAL_NUMBER IS NULL OR p_resrc_assign_rec.SERIAL_NUMBER = Fnd_Api.G_MISS_CHAR) THEN
630 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
631 Fnd_Message.set_name ('AHL', 'AHL_PP_SERIAL_MISSING');
632 Fnd_Msg_Pub.ADD;
633 END IF;
634 x_return_status := Fnd_Api.g_ret_sts_error;
635 RETURN;
636 END IF;
637 END IF;
638
639 -- ASSIGN_START_DATE
640 IF (p_resrc_assign_rec.ASSIGN_START_DATE IS NULL OR p_resrc_assign_rec.ASSIGN_START_DATE = Fnd_Api.G_MISS_DATE)THEN
641 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
642 Fnd_Message.set_name ('AHL', 'AHL_PP_ASSIGN_ST_DT_MISSING');
643 Fnd_Msg_Pub.ADD;
644 END IF;
645 x_return_status := Fnd_Api.g_ret_sts_error;
646 RETURN;
647 END IF;
648
649 -- ASSIGN_END_DATE
650 IF (p_resrc_assign_rec.ASSIGN_END_DATE IS NULL OR p_resrc_assign_rec.ASSIGN_END_DATE = Fnd_Api.G_MISS_DATE)THEN
651 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
652 Fnd_Message.set_name ('AHL', 'AHL_PP_ASSIGN_END_DT_MISSING');
653 Fnd_Msg_Pub.ADD;
654 END IF;
655 x_return_status := Fnd_Api.g_ret_sts_error;
656 RETURN;
657 END IF;
658 END Check_Resrc_Assign_Req_Items;
659
660 -- Check_Resrc_Assign_UK_Items
661 PROCEDURE Check_Resrc_Assign_UK_Items (
662 p_resrc_assign_rec IN Resrc_Assign_Rec_Type,
663 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
664 x_return_status OUT NOCOPY VARCHAR2
665 )
666 IS
667 l_valid_flag VARCHAR2(1);
668 BEGIN
669 x_return_status := Fnd_Api.g_ret_sts_success;
670 --
671 -- For Create_Visit, when ID is passed in, we need to
672 -- check if this ID is unique.
673 RETURN;
674 END Check_Resrc_Assign_UK_Items;
675
676 ---------------------------------------------------------------------
677 -- PROCEDURE
678 -- Check_Resrc_Assign_Items
679 --
680 ---------------------------------------------------------------------
681 PROCEDURE Check_Resrc_Assign_Items (
682 p_resrc_assign_rec IN Resrc_Assign_Rec_Type,
683 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
684 x_return_status OUT NOCOPY VARCHAR2
685 )
686 IS
687 BEGIN
688 --
689 -- Validate required items.
690 Check_Resrc_Assign_Req_Items (
691 p_resrc_assign_rec => p_resrc_assign_rec,
692 x_return_status => x_return_status
693 );
694
695 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
696 RETURN;
697 END IF;
698
699 --
700 -- Validate uniqueness.
701 Check_Resrc_Assign_UK_Items (
702 p_resrc_assign_rec => p_resrc_assign_rec,
703 p_validation_mode => p_validation_mode,
704 x_return_status => x_return_status
705 );
706
707 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
708 RETURN;
709 END IF;
710
711 END Check_Resrc_Assign_Items;
712
713 --------------------------------------------------------------------
714 -- PROCEDURE
715 -- Validate_Resrc_Assign
716 --
717 --------------------------------------------------------------------
718 PROCEDURE Validate_Resrc_Assign (
719 p_api_version IN NUMBER,
720 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
721 p_commit IN VARCHAR2 := Fnd_Api.g_false,
722 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
723 p_resrc_assign_rec IN Resrc_Assign_Rec_Type,
724
725 x_return_status OUT NOCOPY VARCHAR2,
726 x_msg_count OUT NOCOPY NUMBER,
727 x_msg_data OUT NOCOPY VARCHAR2
728 )
729 IS
730 L_API_VERSION CONSTANT NUMBER := 1.0;
731 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Resrc_Assign';
732 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
733 l_return_status VARCHAR2(1);
734 l_assign_start_date DATE;
735 l_assign_end_date DATE;
736 l_eff_st_date DATE;
737 l_eff_end_date DATE;
738 l_actual_start_date DATE;
739 l_actual_end_date DATE;
740 l_scheduled_start_date DATE;
741 l_scheduled_end_date DATE;
742
743 CURSOR c_emp_date(employee_id IN NUMBER) IS
744 SELECT EFFECTIVE_START_DATE, EFFECTIVE_END_DATE FROM
745 BOM_RESOURCE_EMPLOYEES
746 WHERE PERSON_ID = employee_id;
747 /*
748 CURSOR c_job_date(job_id IN NUMBER) IS
749 SELECT TO_DATE(SCHEDULED_START_DATE,'DD-MM-YYYY'), TO_DATE(SCHEDULED_END_DATE,'DD-MM-YYYY'),
750 TO_DATE(ACTUAL_START_DATE,'DD-MM-YYYY'), TO_DATE(ACTUAL_END_DATE,'DD-MM-YYYY')
751 FROM AHL_WORKORDERS_V
752 WHERE workorder_id = job_id; */
753 --
754 /* CURSOR c_job_date(job_id IN NUMBER) IS
755 SELECT TRUNC(SCHEDULED_START_DATE), TRUNC(SCHEDULED_END_DATE),
756 TRUNC(ACTUAL_START_DATE), TRUNC(ACTUAL_END_DATE)
757 FROM AHL_WORKORDERS_V
758 WHERE workorder_id = job_id;
759 */
760 --Modified by srini for performance reasons
761 CURSOR c_job_date(job_id IN NUMBER) IS
762 SELECT TRUNC(WIP.SCHEDULED_START_DATE),
763 TRUNC(WIP.SCHEDULED_COMPLETION_DATE) SCHEDULED_END_DATE,
764 TRUNC(WO.ACTUAL_START_DATE), TRUNC(WO.ACTUAL_END_DATE)
765 FROM AHL_WORKORDERS WO, WIP_DISCRETE_JOBS WIP
766 WHERE WO.wip_entity_id = WIP.wip_entity_id
767 AND WO.workorder_id = job_id;
768
769
770
771 BEGIN
772 --------------------- initialize -----------------------
773 -- Check if API is called in debug mode. If yes, enable debug.
774 IF G_DEBUG='Y' THEN
775 Ahl_Debug_Pub.enable_debug;
776 END IF;
777
778 -- Debug info.
779 IF G_DEBUG='Y' THEN
780 Ahl_Debug_Pub.debug( l_full_name ||':Start');
781 END IF;
782
783 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
784 Fnd_Msg_Pub.initialize;
785 END IF;
786
787 IF NOT Fnd_Api.compatible_api_call (
788 l_api_version,
789 p_api_version,
790 l_api_name,
791 G_PKG_NAME
792 ) THEN
793 RAISE Fnd_Api.g_exc_unexpected_error;
794 END IF;
795 x_return_status := Fnd_Api.g_ret_sts_success;
796
797 ---------------------- validate ------------------------
798 IF G_DEBUG='Y' THEN
799 Ahl_Debug_Pub.debug( l_full_name ||':Check items');
800 END IF;
801
802 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item THEN
803 Check_Resrc_Assign_Items (
804 p_resrc_assign_rec => p_resrc_assign_rec,
805 p_validation_mode => Jtf_Plsql_Api.g_create,
806 x_return_status => l_return_status
807 );
808
809 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
810 RAISE Fnd_Api.g_exc_unexpected_error;
811 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
812 RAISE Fnd_Api.g_exc_error;
813 END IF;
814 END IF;
815
816 --
817 -- Use local vars to reduce amount of typing.
818 IF p_resrc_assign_rec.assign_start_date IS NOT NULL AND p_resrc_assign_rec.assign_start_date <> Fnd_Api.g_miss_date THEN
819 l_assign_start_date := p_resrc_assign_rec.assign_start_date;
820 END IF;
821
822 IF p_resrc_assign_rec.assign_end_date IS NOT NULL AND p_resrc_assign_rec.assign_end_date <> Fnd_Api.g_miss_date THEN
823 l_assign_end_date := p_resrc_assign_rec.assign_end_date;
824 END IF;
825
826 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
827 --
828 -- Validate the active dates.
829 IF l_assign_start_date IS NOT NULL AND l_assign_end_date IS NOT NULL THEN
830
831 ---End date must be greater than or equal to Start Date
832
833 IF l_assign_start_date > l_assign_end_date THEN
834 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
835 Fnd_Message.set_name ('AHL', 'AHL_PP_ASG_FROMDT_GTR_TODT');
836 Fnd_Msg_Pub.ADD;
837 END IF;
838 x_return_status := Fnd_Api.g_ret_sts_error;
839 RETURN;
840 END IF;
841
842 -----Validate Assignment Dates against Job start-end dates
843
844 OPEN c_job_date(p_resrc_assign_rec.WORKORDER_ID);
845 FETCH c_job_date INTO l_scheduled_start_date,l_scheduled_end_date,l_actual_start_date, l_actual_end_date;
846 CLOSE c_job_date;
847
848 l_assign_start_date := TRUNC(l_assign_start_date);
849 l_assign_end_date := TRUNC(l_assign_end_date);
850
851 IF l_actual_start_date is not null or l_actual_end_date is not null THEN
852
853 IF l_actual_start_date is not null and l_assign_start_date < l_actual_start_date THEN
854
855 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
856 Fnd_Message.set_name ('AHL', 'AHL_PP_ASG_EARLY_ACTUAL');
857 Fnd_Msg_Pub.ADD;
858 END IF;
859 x_return_status := Fnd_Api.g_ret_sts_error;
860 RETURN;
861 END IF;
862
863 IF l_actual_end_date is not null and l_assign_end_date > l_actual_end_date THEN
864
865 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
866 Fnd_Message.set_name ('AHL', 'AHL_PP_ASG_LATER_ACTUAL');
867 Fnd_Msg_Pub.ADD;
868 END IF;
869 x_return_status := Fnd_Api.g_ret_sts_error;
870 RETURN;
871 END IF;
872
873 ELSE
874
875 IF l_scheduled_start_date is not null and l_assign_start_date < l_scheduled_start_date THEN
876
877 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
878 Fnd_Message.set_name ('AHL', 'AHL_PP_ASG_EARLY_SCHEDULE');
879 Fnd_Msg_Pub.ADD;
880 END IF;
881 x_return_status := Fnd_Api.g_ret_sts_error;
882 RETURN;
883 END IF;
884
885 IF l_scheduled_end_date is not null and l_assign_end_date > l_scheduled_end_date THEN
886
887 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
888 Fnd_Message.set_name ('AHL', 'AHL_PP_ASG_LATER_SCHEDULE');
889 Fnd_Msg_Pub.ADD;
890 END IF;
891 x_return_status := Fnd_Api.g_ret_sts_error;
892 RETURN;
893 END IF;
894
895 END IF;
896
897
898 END IF;
899
900 IF p_resrc_assign_rec.employee_id IS NOT NULL THEN
901 OPEN c_emp_date(p_resrc_assign_rec.employee_id);
902 FETCH c_emp_date INTO l_eff_st_date, l_eff_end_date;
903 CLOSE c_emp_date;
904
905 IF G_DEBUG='Y' THEN
906 Ahl_Debug_Pub.debug( 'l_assign_start_date'||l_assign_start_date);
907 Ahl_Debug_Pub.debug( 'l_eff_st_date'||l_eff_st_date);
908
909 END IF;
910 /*
911 IF trunc(l_assign_start_date) < trunc(l_eff_st_date) THEN
912 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
913 Fnd_Message.set_name ('AHL', 'AHL_PP_ASG_STDT_GTR_EMP_STDT');
914 Fnd_Msg_Pub.ADD;
915 END IF;
916 x_return_status := Fnd_Api.g_ret_sts_error;
917 RETURN;
918 END IF;
919 */
920 IF trunc(l_assign_end_date) > trunc(l_eff_end_date) THEN
921 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
922 Fnd_Message.set_name ('AHL', 'AHL_PP_EMP_ENDDT_GTR_ASG_ENDDT');
923 Fnd_Msg_Pub.ADD;
924 END IF;
925 x_return_status := Fnd_Api.g_ret_sts_error;
926 RETURN;
927 END IF;
928 END IF;
929
930 -------------------- finish --------------------------
931 Fnd_Msg_Pub.count_and_get (
932 p_encoded => Fnd_Api.g_false,
933 p_count => x_msg_count,
934 p_data => x_msg_data
935 );
936
937 IF G_DEBUG='Y' THEN
938 Ahl_Debug_Pub.debug( l_full_name ||':End');
939 END IF;
940
941 -- Check if API is called in debug mode. If yes, disable debug.
942 IF G_DEBUG='Y' THEN
943 Ahl_Debug_Pub.disable_debug;
944 END IF;
945
946 EXCEPTION
947 WHEN Fnd_Api.g_exc_error THEN
948 x_return_status := Fnd_Api.g_ret_sts_error;
949 Fnd_Msg_Pub.count_and_get (
950 p_encoded => Fnd_Api.g_false,
951 p_count => x_msg_count,
952 p_data => x_msg_data
953 );
954 WHEN Fnd_Api.g_exc_unexpected_error THEN
955 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
956 Fnd_Msg_Pub.count_and_get (
957 p_encoded => Fnd_Api.g_false,
958 p_count => x_msg_count,
959 p_data => x_msg_data
960 );
961 WHEN OTHERS THEN
962 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
963 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
964 THEN
965 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
966 END IF;
967
968 Fnd_Msg_Pub.count_and_get (
969 p_encoded => Fnd_Api.g_false,
970 p_count => x_msg_count,
971 p_data => x_msg_data
972 );
973 END Validate_Resrc_Assign;
974
975 ----------------------------------------------------------------------------------------
976 -- Start of Comments --
977 -- Procedure name : Create_Resrc_Assign
978 -- Type : Private
979 -- Function : Validates Resource Information and inserts records into
980 -- Schedule Resource table for non routine jobs and loads record
981 -- into MRP_SCHEDULE_INTERFACE table Launches Concurrent Program to
982 -- initiate Resource reservation
983 -- Updates schedule Resource table with Assignment id
984 -- Pre-reqs :
985 -- Parameters :
986 --
987 -- Standard IN Parameters :
988 -- p_api_version IN NUMBER Required
989 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
990 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
991 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
992 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
993 -- p_module_type IN VARCHAR2 Default NULL.
994 --
995 -- Standard OUT Parameters :
996 -- x_return_status OUT VARCHAR2 Required
997 -- x_msg_count OUT NUMBER Required
998 -- x_msg_data OUT VARCHAR2 Required
999 --
1000 -- Create Resource Assignment Parameters:
1001 -- p_x_resrc_assign_tbl IN OUT NOCOPY AHL_PP_RESRC_ASSIGN_PVT.Resrc_Assign_Tbl_Type,
1002 -- Contains Resource information to perform Resource reservation
1003 --
1004 -- Version :
1005 -- Initial Version 1.0
1006 --
1007 -- End of Comments.
1008
1009 PROCEDURE Create_Resrc_Assign (
1010 p_api_version IN NUMBER,
1011 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
1012 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
1013 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
1014 p_module_type IN VARCHAR2 := NULL,
1015 p_x_resrc_assign_tbl IN OUT NOCOPY Resrc_Assign_Tbl_Type,
1016 x_return_status OUT NOCOPY VARCHAR2,
1017 x_msg_count OUT NOCOPY NUMBER,
1018 x_msg_data OUT NOCOPY VARCHAR2
1019 )
1020 IS
1021 -- Check to see schedule Resource id exists
1022 CURSOR Sch_id_exists (x_id IN NUMBER) IS
1023 SELECT 1 FROM dual
1024 WHERE EXISTS (SELECT 1
1025 FROM AHL_WORK_ASSIGNMENTS
1026 WHERE ASSIGNMENT_ID = x_id);
1027
1028 /*CURSOR c_oper_resrc (x_id IN NUMBER, x_oper IN NUMBER, x_resrc IN NUMBER) IS
1029 SELECT requirement_id, operation_id, resource_id FROM AHL_PP_REQUIREMENT_V
1030 WHERE job_id = x_id
1031 AND resource_sequence = x_resrc
1032 AND operation_sequence = x_oper;
1033 */
1034 --Modified by Srini for performance fix
1035
1036 CURSOR c_oper_resrc (x_id IN NUMBER, x_oper IN NUMBER, x_resrc IN NUMBER) IS
1037 SELECT OPR.operation_resource_id requirement_id ,
1038 WOP.workorder_operation_id operation_id ,
1039 OPR.resource_id
1040 FROM ahl_operation_resources OPR,
1041 ahl_workorder_operations WOP
1042 WHERE OPR.workorder_operation_id = WOP.workorder_operation_id
1043 AND WOP.operation_sequence_num = x_oper
1044 AND OPR.resource_sequence_num = x_resrc
1045 AND WOP.workorder_id = x_id;
1046
1047
1048 /*
1049 CURSOR c_resource (x_oper IN NUMBER, x_res IN NUMBER, x_id IN NUMBER) IS
1050 SELECT RESOURCE_TYPE_CODE FROM
1051 AHL_PP_REQUIREMENT_V
1052 WHERE OPERATION_SEQUENCE = x_oper AND RESOURCE_SEQUENCE = x_res
1053 AND JOB_ID = x_id;
1054 */
1055 --Modified by Srini for performance fix
1056 CURSOR c_resource (x_oper IN NUMBER, x_res IN NUMBER, x_id IN NUMBER) IS
1057 SELECT resource_type resource_type_code
1058 FROM ahl_operation_resources OPR,
1059 ahl_workorder_operations WOP,
1060 bom_resources BOM
1061 WHERE OPR.workorder_operation_id = WOP.workorder_operation_id
1062 AND OPR.resource_id = BOM.resource_id
1063 AND WOP.operation_sequence_num = x_oper
1064 AND OPR.resource_sequence_num = x_res
1065 AND WOP.workorder_id = x_id;
1066
1067 /*
1068 CURSOR c_assign (x_id IN NUMBER, x_oper IN NUMBER, x_resrc IN NUMBER) IS
1069 SELECT resource_id FROM AHL_PP_REQUIREMENT_V
1070 WHERE job_id = x_id
1071 AND resource_sequence = x_resrc
1072 AND operation_sequence = x_oper;
1073 */
1074 --Modified by Srini for performance fix
1075 CURSOR c_assign (x_id IN NUMBER, x_oper IN NUMBER, x_resrc IN NUMBER) IS
1076 SELECT OPR.resource_id
1077 FROM ahl_workorder_operations WOP,
1078 ahl_operation_resources OPR
1079 WHERE WOP.workorder_operation_id = OPR.workorder_operation_id
1080 AND WOP.operation_sequence_num = x_oper
1081 AND OPR.resource_sequence_num = x_resrc
1082 AND WOP.workorder_id = x_id;
1083
1084 /*
1085 CURSOR c_work (x_id IN NUMBER) IS
1086 SELECT wip_entity_id, organization_id,
1087 department_id FROM AHL_Workorders_V
1088 WHERE workorder_id = x_id;
1089 */
1090 -- fix bug# 6452479. Dept. should default from Operation dept.
1091 CURSOR c_work (x_id IN NUMBER,
1092 x_operation_seq_num IN NUMBER) IS
1093 SELECT a.wip_entity_id, wo.organization_id,
1094 wo.department_id
1095 FROM AHL_Workorders a, wip_operations wo
1096 WHERE a.wip_entity_id = wo.wip_entity_id
1097 AND wo.operation_seq_num = x_operation_seq_num
1098 AND a.workorder_id = x_id;
1099 /*
1100 --Modified by Srini for performance fix
1101 CURSOR c_work (x_id IN NUMBER) IS
1102 SELECT wip_entity_id, organization_id,
1103 department_id
1104 FROM AHL_Workorders a, ahl_visits_b b
1105 WHERE a.visit_id = b.visit_id
1106 AND workorder_id = x_id;
1107 */
1108 --
1109 CURSOR c_instance_cur (c_person_id IN NUMBER,
1110 c_resource_id IN NUMBER,
1111 c_dept_id IN NUMBER)
1112 IS
1113 SELECT a.instance_id
1114 FROM BOM_DEPT_RES_INSTANCES A, BOM_RESOURCE_EMPLOYEES B
1115 WHERE A.INSTANCE_ID = B.INSTANCE_ID
1116 AND B.PERSON_ID = c_person_id
1117 AND A.RESOURCE_ID = c_resource_id
1118 AND A.DEPARTMENT_ID in (
1119 SELECT
1120 nvl(bdr.SHARE_FROM_DEPT_ID,
1121 bdr.department_id)
1122 FROM
1123 bom_department_resources bdr
1124 WHERE
1125 bdr.resource_id = c_resource_id and
1126 bdr.department_id = c_dept_id
1127 );
1128
1129 -- cursor to get the resource req dates
1130 /* CURSOR resrc_req_dates(x_id IN NUMBER, x_oper IN NUMBER, x_resrc IN NUMBER) IS
1131 SELECT scheduled_start_date, scheduled_end_date
1132 FROM ahl_workorder_operations WOP,
1133 ahl_operation_resources OPR
1134 WHERE WOP.workorder_operation_id = OPR.workorder_operation_id
1135 AND WOP.operation_sequence_num = x_oper
1136 AND OPR.resource_sequence_num = x_resrc
1137 AND WOP.workorder_id = x_id;
1138 */
1139
1140 -- Cursor added by Balaji for Bug # 6728602
1141 -- Cursor fetches the resource requirement start and end date seconds.
1142 -- This value is passed to EAM to avoid scheduling hierarchy error.
1143 -- Bug # 6728602 -- start
1144 CURSOR c_get_res_sec(p_wo_id IN NUMBER, p_op_seq IN NUMBER)
1145 IS
1146 SELECT
1147 TO_CHAR(WOP.FIRST_UNIT_START_DATE, 'ss'),
1148 TO_CHAR(WOP.LAST_UNIT_COMPLETION_DATE, 'ss')
1149 FROM
1150 wip_operations WOP,
1151 ahl_workorders AWO
1152 WHERE
1153 WOP.OPERATION_SEQ_NUM = p_op_seq
1154 AND WOP.wip_entity_id = AWO.wip_entity_id
1155 AND AWO.workorder_id = p_wo_id;
1156
1157 l_st_date_sec VARCHAR2(30);
1158 l_end_date_sec VARCHAR2(30);
1159 l_sec VARCHAR2(30);
1160 -- Bug # 6728602 -- end
1161 --
1162 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_Resrc_Assign';
1163 l_api_version CONSTANT NUMBER := 1.0;
1164 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1165
1166 l_msg_count NUMBER;
1167 l_wo_operation_id NUMBER;
1168 l_dummy NUMBER;
1169 l_assignment_id NUMBER;
1170 l_serial_id NUMBER;
1171 l_oper_resrc_id NUMBER;
1172 l_resrc_seq_num NUMBER;
1173 l_object_version_number NUMBER;
1174 l_process_status NUMBER;
1175 l_employee_id NUMBER;
1176 l_resource_type NUMBER;
1177 l_dept_id NUMBER;
1178 l_resource_id NUMBER;
1179 l_instance_id NUMBER;
1180 l_wip_entity_id NUMBER;
1181 l_organization_id NUMBER;
1182 l_return_status VARCHAR2(1);
1183 l_msg_data VARCHAR2(2000);
1184 l_error_message VARCHAR2(120);
1185 l_employee_name VARCHAR2(240);
1186
1187 /*l_res_start_date DATE;
1188 l_res_end_date DATE;
1189 */
1190
1191 l_Resrc_Assign_Tbl Resrc_Assign_Tbl_Type;
1192 l_Resrc_Assign_Rec Resrc_Assign_Rec_Type;
1193 j NUMBER;
1194 l_default VARCHAR2(10);
1195
1196 l_hour VARCHAR2(30);
1197 l_min VARCHAR2(30);
1198 l_date_time VARCHAR2(30);
1199
1200 BEGIN
1201 --------------------Initialize ----------------------------------
1202 -- Standard Start of API savepoint
1203 SAVEPOINT Create_Resrc_Assign;
1204
1205 -- Check if API is called in debug mode. If yes, enable debug.
1206 IF G_DEBUG='Y' THEN
1207 AHL_DEBUG_PUB.enable_debug;
1208 END IF;
1209
1210 -- Debug info.
1211 IF G_DEBUG='Y' THEN
1212 AHL_DEBUG_PUB.debug( 'Enter AHL_PP_RESRC_ASSIGN_PVT. Create_Resrc_Assign','+PPResrc_Assign_Pvt+');
1213 END IF;
1214
1215 -- Standard call to check for call compatibility.
1216 IF FND_API.to_boolean(p_init_msg_list)
1217 THEN
1218 FND_MSG_PUB.initialize;
1219 END IF;
1220
1221 -- Initialize API return status to success
1222 x_return_status := FND_API.G_RET_STS_SUCCESS;
1223
1224 -- Initialize message list if p_init_msg_list is set to TRUE.
1225 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1226 p_api_version,
1227 l_api_name,G_PKG_NAME)
1228 THEN
1229 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1230 END IF;
1231
1232 --------------------Start of API Body-----------------------------------
1233 --------------------Value OR ID conversion------------------------------
1234 --Start API Body
1235
1236 IF p_x_resrc_assign_tbl.COUNT > 0 THEN
1237 FOR i IN p_x_resrc_assign_tbl.FIRST..p_x_resrc_assign_tbl.LAST LOOP
1238 --
1239 IF p_module_type = 'JSP' THEN
1240 p_x_resrc_assign_tbl(i).instance_id := NULL;
1241 p_x_resrc_assign_tbl(i).employee_id := NULL;
1242 END IF;
1243
1244 IF G_DEBUG='Y' THEN
1245 AHL_DEBUG_PUB.debug( 'Workorder ID = ' || p_x_resrc_assign_tbl(i).workorder_id);
1246 AHL_DEBUG_PUB.debug( 'Oper Seq Num = ' || p_x_resrc_assign_tbl(i).operation_seq_number);
1247 AHL_DEBUG_PUB.debug( 'Resrc Seq Num = ' || p_x_resrc_assign_tbl(i).resource_seq_number);
1248 END IF;
1249 --
1250 IF p_x_resrc_assign_tbl(i).workorder_id IS NOT NULL THEN
1251 IF p_x_resrc_assign_tbl(i).operation_seq_number IS NOT NULL AND
1252 p_x_resrc_assign_tbl(i).operation_seq_number <> FND_API.G_MISS_NUM AND
1253 p_x_resrc_assign_tbl(i).resource_seq_number IS NOT NULL AND
1254 p_x_resrc_assign_tbl(i).resource_seq_number <> FND_API.G_MISS_NUM THEN
1255 OPEN c_oper_resrc(p_x_resrc_assign_tbl(i).workorder_id, p_x_resrc_assign_tbl(i).operation_seq_number,
1256 p_x_resrc_assign_tbl(i).resource_seq_number);
1257 FETCH c_oper_resrc INTO l_oper_resrc_id, l_wo_operation_id,l_resource_id;
1258 IF c_oper_resrc%NOTFOUND THEN
1259 AHL_DEBUG_PUB.debug(l_full_name || 'c_oper_resrc i.e Cursor not found');
1260 Fnd_Message.SET_NAME('AHL','AHL_PP_RESRC_REQ_NOT_EXISTS');
1261 Fnd_Msg_Pub.ADD;
1262 RAISE Fnd_Api.G_EXC_ERROR;
1263 END IF;
1264 CLOSE c_oper_resrc;
1265
1266 END IF; -- Check resrc sequence number
1267 ELSE
1268 Fnd_Message.SET_NAME('AHL','AHL_PP_JOB_NOT_EXISTS');
1269 Fnd_Msg_Pub.ADD;
1270 END IF; -- Check of work order id
1271
1272 -- rroy
1273 -- ACL Changes
1274 l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked(p_workorder_id => p_x_resrc_assign_tbl(i).workorder_id,
1275 p_ue_id => NULL,
1276 p_visit_id => NULL,
1277 p_item_instance_id => NULL);
1278 IF l_return_status = FND_API.G_TRUE THEN
1279 FND_MESSAGE.Set_Name('AHL', 'AHL_PP_CRT_RESASG_UNTLCKD');
1280 FND_MSG_PUB.ADD;
1281 RAISE FND_API.G_EXC_ERROR;
1282 END IF;
1283 -- rroy
1284 -- ACL Changes
1285
1286 IF G_DEBUG='Y' THEN
1287 Ahl_Debug_Pub.debug( 'l_oper_resrc_id = ' || l_oper_resrc_id);
1288 Ahl_Debug_Pub.debug( 'l_wo_operation_id = ' || l_wo_operation_id);
1289 END IF;
1290
1291 -- For Resource Type
1292 OPEN c_resource(p_x_resrc_assign_tbl(i).operation_seq_number, p_x_resrc_assign_tbl(i).resource_seq_number,
1293 p_x_resrc_assign_tbl(i).workorder_id);
1294 FETCH c_resource INTO l_resource_type;
1295 IF c_resource%FOUND THEN
1296 IF G_DEBUG='Y' THEN
1297 AHL_DEBUG_PUB.debug(l_full_name || 'c_resource i.e Cursor found');
1298 END IF;
1299
1300 CLOSE c_resource;
1301 p_x_resrc_assign_tbl(i).resource_type_code := l_resource_type;
1302 ELSE
1303 IF G_DEBUG='Y' THEN
1304 AHL_DEBUG_PUB.debug(l_full_name || 'c_resource i.e Cursor not found');
1305 END IF;
1306
1307 CLOSE c_resource;
1308 Fnd_Message.SET_NAME('AHL','AHL_PP_NO_RES_TYPE_FOUND');
1309 Fnd_Msg_Pub.ADD;
1310 RAISE Fnd_Api.G_EXC_ERROR;
1311 END IF;
1312
1313 Ahl_Debug_Pub.debug( 'l_resource_type = ' || l_resource_type);
1314 Ahl_Debug_Pub.debug( 'p_x_resrc_assign_tbl(i).instance_id = ' || p_x_resrc_assign_tbl(i).instance_id);
1315 Ahl_Debug_Pub.debug( 'p_x_resrc_assign_tbl(i).serial_number = ' || p_x_resrc_assign_tbl(i).serial_number);
1316
1317 -- R12
1318 -- Alignment to resource requirement dates is no longer required since
1319 -- assignment times can now be entered by the user
1320
1321 -- to align the assignment dates to the resource req dates
1322 --Required to check the operation start dates and resource start and end date are same
1323 /*OPEN resrc_req_dates(p_x_resrc_assign_tbl(i).workorder_id,
1324 p_x_resrc_assign_tbl(i).operation_seq_number,
1325 p_x_resrc_assign_tbl(i).resource_seq_number);
1326
1327 FETCH resrc_req_dates INTO l_res_start_date,l_res_end_date;
1328 CLOSE resrc_req_dates;
1329 --Validation is required to include resource timestamp for Requested start date
1330 -- requested end date
1331 IF (TRUNC(p_x_resrc_assign_tbl(i).assign_start_date) = TRUNC(l_res_start_date )
1332 AND
1333 TRUNC(p_x_resrc_assign_tbl(i).assign_end_date) = TRUNC(l_res_start_date ))
1334 THEN
1335
1336 p_x_resrc_assign_tbl(i).assign_start_date := l_res_start_date;
1337 p_x_resrc_assign_tbl(i).assign_end_date := l_res_start_date;
1338
1339 ELSIF (TRUNC(p_x_resrc_assign_tbl(i).assign_start_date) = TRUNC(l_res_end_date)
1340 AND
1341 TRUNC(p_x_resrc_assign_tbl(i).assign_end_date) = TRUNC(l_res_end_date )) THEN
1342
1343 p_x_resrc_assign_tbl(i).assign_start_date := l_res_end_date;
1344 p_x_resrc_assign_tbl(i).assign_end_date := l_res_end_date;
1345
1346 ELSIF (TRUNC(p_x_resrc_assign_tbl(i).assign_start_date) = TRUNC(l_res_start_date )
1347 AND
1348 TRUNC(p_x_resrc_assign_tbl(i).assign_end_date) <> TRUNC(l_res_start_date )) THEN
1349
1350 p_x_resrc_assign_tbl(i).assign_start_date := l_res_start_date;
1351
1352 ELSIF (TRUNC(p_x_resrc_assign_tbl(i).assign_start_date) <> TRUNC(l_res_start_date )
1353 AND
1354 TRUNC(p_x_resrc_assign_tbl(i).assign_end_date) = TRUNC(l_res_end_date )) THEN
1355
1356 p_x_resrc_assign_tbl(i).assign_end_date := l_res_end_date;
1357
1358 END IF;
1359 */
1360 -- Bug # 6728602 -- start
1361 OPEN c_get_res_sec(
1362 p_x_resrc_assign_tbl(i).workorder_id,
1363 p_x_resrc_assign_tbl(i).operation_seq_number
1364 );
1365 FETCH c_get_res_sec INTO l_st_date_sec,l_end_date_sec;
1366 CLOSE c_get_res_sec;
1367 -- Bug # 6728602 -- end
1368 -- R12
1369 -- Take into account the start and end times
1370 IF p_x_resrc_assign_tbl(i).assign_start_date IS NOT NULL THEN
1371 IF p_x_resrc_assign_tbl(i).assign_start_hour IS NULL THEN
1372 l_hour := ':00';
1373 ELSE
1374 l_hour := ':' || p_x_resrc_assign_tbl(i).assign_start_hour;
1375 END IF;
1376
1377 IF p_x_resrc_assign_tbl(i).assign_start_min IS NULL THEN
1378 l_min := ':00';
1379 ELSE
1380 l_min := ':' || p_x_resrc_assign_tbl(i).assign_start_min;
1381 END IF;
1382
1383 -- Bug # 6728602 -- start
1384 l_sec := TO_CHAR(p_x_resrc_assign_tbl(i).assign_start_date, 'ss');
1385
1386 IF(l_sec = '00') THEN
1387 l_sec := ':' ||l_st_date_sec;
1388 END IF;
1389
1390 l_date_time := TO_CHAR(p_x_resrc_assign_tbl(i).assign_start_date, 'DD-MM-YYYY')||' '|| l_hour || l_min || l_sec;
1391 p_x_resrc_assign_tbl(i).assign_start_date := TO_DATE(l_date_time , 'DD-MM-YYYY :HH24:MI:SS');
1392 -- Bug # 6728602 -- end
1393 END IF;
1394
1395 IF p_x_resrc_assign_tbl(i).assign_end_date IS NOT NULL THEN
1396 IF p_x_resrc_assign_tbl(i).assign_end_hour IS NULL THEN
1397 l_hour := ':00';
1398 ELSE
1399 l_hour := ':' || p_x_resrc_assign_tbl(i).assign_end_hour;
1400 END IF;
1401
1402 IF p_x_resrc_assign_tbl(i).assign_end_min IS NULL THEN
1403 l_min := ':00';
1404 ELSE
1405 l_min := ':' || p_x_resrc_assign_tbl(i).assign_end_min;
1406 END IF;
1407
1408 -- Bug # 6728602 -- start
1409 l_sec := TO_CHAR(p_x_resrc_assign_tbl(i).assign_end_date, 'ss');
1410
1411 IF(l_sec = '00') THEN
1412 l_sec := ':' ||l_end_date_sec;
1413 END IF;
1414
1415 l_date_time := TO_CHAR(p_x_resrc_assign_tbl(i).assign_end_date, 'DD-MM-YYYY')||' '|| l_hour || l_min || l_sec;
1416 p_x_resrc_assign_tbl(i).assign_end_date := TO_DATE(l_date_time , 'DD-MM-YYYY :HH24:MI:SS');
1417 -- Bug # 6728602 -- end
1418 END IF;
1419
1420
1421 --Get org,dept,wip entity id
1422 OPEN c_work (p_x_resrc_assign_tbl(i).workorder_id,
1423 p_x_resrc_assign_tbl(i).operation_seq_number);
1424 FETCH c_work INTO l_wip_entity_id,l_organization_id,l_dept_id;
1425 CLOSE c_work;
1426 --Assign
1427 p_x_resrc_assign_tbl(i).wip_entity_id := l_wip_entity_id;
1428 p_x_resrc_assign_tbl(i).organization_id := l_organization_id;
1429 p_x_resrc_assign_tbl(i).department_id := l_dept_id;
1430 p_x_resrc_assign_tbl(i).oper_resource_id := l_oper_resrc_id;
1431
1432 IF (G_DEBUG = 'Y') THEN
1433 AHL_DEBUG_PUB.debug(l_full_name || 'Organization ID:' || p_x_resrc_assign_tbl(i).organization_id);
1434 AHL_DEBUG_PUB.debug(l_full_name || 'Dept ID:' || p_x_resrc_assign_tbl(i).department_id);
1435 AHL_DEBUG_PUB.debug(l_full_name || 'Serial:' || p_x_resrc_assign_tbl(i).serial_number);
1436 END IF;
1437
1438 IF l_resource_type <> 2 THEN
1439 /*IF ((l_Resrc_Assign_Rec.employee_number IS NOT NULL AND l_Resrc_Assign_Rec.employee_number <> Fnd_Api.G_MISS_CHAR)
1440 Ot
1441 (l_Resrc_Assign_Rec.employee_name IS NOT NULL AND l_Resrc_Assign_Rec.employee_name <> Fnd_Api.G_MISS_CHAR))
1442 THEN
1443 Fnd_Message.SET_NAME('AHL','AHL_PP_EMPLOYEE_NOT_REQ');
1444 Fnd_Msg_Pub.ADD;
1445 RAISE Fnd_Api.G_EXC_ERROR;
1446 END IF;*/
1447
1448 -- Convert serial number to instance/ serial id
1449 IF (p_x_resrc_assign_tbl(i).serial_number IS NOT NULL AND
1450 p_x_resrc_assign_tbl(i).serial_number <> Fnd_Api.G_MISS_CHAR ) THEN
1451
1452 OPEN c_assign (p_x_resrc_assign_tbl(i).workorder_id, p_x_resrc_assign_tbl(i).operation_seq_number,
1453 p_x_resrc_assign_tbl(i).resource_seq_number);
1454 FETCH c_assign INTO l_resource_id;
1455 CLOSE c_assign;
1456 --
1457 Check_Serial_Name_Or_Id
1458 (p_serial_id => p_x_resrc_assign_tbl(i).instance_id,
1459 p_serial_number => p_x_resrc_assign_tbl(i).serial_number,
1460 p_workorder_id => p_x_resrc_assign_tbl(i).workorder_id,
1461 p_resource_id => l_resource_id,
1462 p_dept_id => l_dept_id,
1463 p_organization_id => l_organization_id,
1464 x_instance_id => l_instance_id,
1465 x_return_status => l_return_status,
1466 x_error_msg_code => l_msg_data);
1467
1468 IF G_DEBUG='Y' THEN
1469 Ahl_Debug_Pub.debug( l_full_name ||'Status Serial' || l_return_status );
1470 END IF;
1471
1472 IF NVL(l_return_status,'x') <> 'S'
1473 THEN
1474 Fnd_Message.SET_NAME('AHL','AHL_PP_SERIAL_NOT_EXISTS');
1475 Fnd_Msg_Pub.ADD;
1476 RAISE Fnd_Api.G_EXC_ERROR;
1477 END IF;
1478 END IF;
1479
1480 --Assign the returned value
1481 p_x_resrc_assign_tbl(i).instance_id := l_instance_id;
1482 p_x_resrc_assign_tbl(i).oper_resource_id := l_oper_resrc_id;
1483
1484 IF G_DEBUG='Y' THEN
1485 Ahl_Debug_Pub.debug(': Serial Id After= ' || p_x_resrc_assign_tbl(i).instance_id);
1486 END IF;
1487
1488 END IF;
1489 IF G_DEBUG='Y' THEN
1490 Ahl_Debug_Pub.debug( ': Employee Name = ' || p_x_resrc_assign_tbl(i).employee_name);
1491 Ahl_Debug_Pub.debug( ': EMployee Number = ' || p_x_resrc_assign_tbl(i).employee_number);
1492 END IF;
1493
1494 IF l_resource_type = 2 THEN
1495 IF G_DEBUG='Y' THEN
1496 AHL_DEBUG_PUB.debug(l_full_name || 'RESOURCE TYPE in Cursor' || l_resource_type);
1497 END IF;
1498
1499 /*IF (l_Resrc_Assign_Rec.serial_number IS NOT NULL AND l_Resrc_Assign_Rec.serial_number <> Fnd_Api.G_MISS_CHAR)
1500 THEN
1501 Fnd_Message.SET_NAME('AHL','AHL_PP_EMPLOYEE_NOT_REQ');
1502 Fnd_Msg_Pub.ADD;
1503 RAISE Fnd_Api.G_EXC_ERROR;
1504 END IF;*/
1505 IF G_DEBUG='Y' THEN
1506 AHL_DEBUG_PUB.debug(l_full_name || 'RESOURCE TYPE in record -- ' || p_x_resrc_assign_tbl(i).resource_type_code);
1507 END IF;
1508
1509 -- For Employee Number
1510 IF (p_x_resrc_assign_tbl(i).employee_number IS NOT NULL AND p_x_resrc_assign_tbl(i).employee_number <> Fnd_Api.G_MISS_CHAR)
1511 THEN
1512 IF G_DEBUG='Y' THEN
1513 Ahl_Debug_Pub.debug(': Inside EMployee Number = ' || p_x_resrc_assign_tbl(i).employee_number);
1514 END IF;
1515
1516 Check_Employee_Number_Or_Id
1517 (p_employee_id => p_x_resrc_assign_tbl(i).employee_id,
1518 p_employee_number => p_x_resrc_assign_tbl(i).employee_number,
1519 p_workorder_id => p_x_resrc_assign_tbl(i).workorder_id,
1520 p_oper_resrc_id => l_oper_resrc_id,
1521 p_resource_id => l_resource_id,
1522 p_organization_id => l_organization_id,
1523 x_employee_id => p_x_resrc_assign_tbl(i).employee_id,
1524 x_return_status => l_return_status,
1525 x_error_msg_code => l_msg_data
1526 );
1527
1528 IF NVL(l_return_status, 'X') <> 'S'
1529 THEN
1530 Fnd_Message.SET_NAME('AHL','AHL_PP_EMP_NUM_NOT_EXISTS');
1531 Fnd_Msg_Pub.ADD;
1532 RAISE Fnd_Api.G_EXC_ERROR;
1533 END IF;
1534
1535 END IF;
1536
1537 -- For Employee Name
1538 IF (p_x_resrc_assign_tbl(i).employee_name IS NOT NULL AND p_x_resrc_assign_tbl(i).employee_name <> Fnd_Api.G_MISS_CHAR)
1539 THEN
1540 IF G_DEBUG='Y' THEN
1541 Ahl_Debug_Pub.debug( l_full_name ||': Inside Employee Name = ' || p_x_resrc_assign_tbl(i).employee_name);
1542 END IF;
1543 Check_Employee_Name_Or_Id
1544 (p_employee_id => l_employee_id,
1545 p_employee_number => p_x_resrc_assign_tbl(i).employee_number,
1546 p_workorder_id => p_x_resrc_assign_tbl(i).workorder_id,
1547 p_oper_resrc_id => l_oper_resrc_id,
1548 p_resource_id => l_resource_id,
1549 p_organization_id => l_organization_id,
1550 x_employee_name => l_employee_name,
1551 x_employee_id => l_employee_id,
1552 x_return_status => l_return_status,
1553 x_error_msg_code => l_msg_data
1554 );
1555
1556 IF NVL(l_return_status, 'X') <> 'S'
1557 THEN
1558 Fnd_Message.SET_NAME('AHL','AHL_PP_EMP_NAME_NOT_EXISTS');
1559 Fnd_Msg_Pub.ADD;
1560 RAISE Fnd_Api.G_EXC_ERROR;
1561 END IF;
1562
1563 IF p_x_resrc_assign_tbl(i).employee_id <> l_employee_id THEN
1564 Fnd_Message.SET_NAME('AHL','AHL_PP_USE_EMP_NAME_LOV');
1565 Fnd_Msg_Pub.ADD;
1566 END IF;
1567
1568 IF p_x_resrc_assign_tbl(i).employee_name <> l_employee_name THEN
1569 Fnd_Message.SET_NAME('AHL','AHL_PP_EMP_NAME_NOT_EXISTS');
1570 Fnd_Msg_Pub.ADD;
1571 END IF;
1572
1573 --
1574 p_x_resrc_assign_tbl(i).employee_id := l_employee_id;
1575 END IF;
1576
1577 -- Get instance id
1578 IF (p_x_resrc_assign_tbl(i).employee_id IS NOT NULL AND p_x_resrc_assign_tbl(i).employee_id <> Fnd_Api.G_MISS_NUM)
1579 THEN
1580 --
1581 OPEN c_instance_cur (p_x_resrc_assign_tbl(i).employee_id,
1582 l_resource_id, p_x_resrc_assign_tbl(i).department_id);
1583 FETCH c_instance_cur INTO p_x_resrc_assign_tbl(i).instance_id;
1584 CLOSE c_instance_cur;
1585 --
1586 END IF;
1587 END IF;
1588
1589 IF G_DEBUG='Y' THEN
1590 Ahl_Debug_Pub.debug(l_full_name || ': Instance Id After= ' || p_x_resrc_assign_tbl(i).instance_id);
1591 Ahl_Debug_Pub.debug(l_full_name || ': Employee Id= ' || p_x_resrc_assign_tbl(i).employee_id);
1592 END IF;
1593
1594 -------------------------------- Validate -----------------------------------------
1595 IF G_DEBUG='Y' THEN
1596 Ahl_Debug_Pub.debug( l_full_name ||': Before Validate Assignment');
1597 END IF;
1598
1599 Validate_Resrc_Assign (
1600 p_api_version => l_api_version,
1601 p_init_msg_list => p_init_msg_list,
1602 p_commit => p_commit,
1603 p_validation_level => p_validation_level,
1604 p_resrc_assign_rec => p_x_resrc_assign_tbl(i),
1605 x_return_status => l_return_status,
1606 x_msg_count => x_msg_count,
1607 x_msg_data => x_msg_data
1608 );
1609
1610 END LOOP;
1611 END IF;
1612 --Standard check to count messages
1613 l_msg_count := Fnd_Msg_Pub.count_msg;
1614
1615 IF l_msg_count > 0 THEN
1616 X_msg_count := l_msg_count;
1617 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1618 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1619 END IF;
1620 -- Call Eam Api to create resource assignment in WIP
1621 IF p_x_resrc_assign_tbl.COUNT > 0 THEN
1622 j := 1;
1623 FOR i IN p_x_resrc_assign_tbl.FIRST..p_x_resrc_assign_tbl.LAST
1624 LOOP
1625 --
1626 IF G_DEBUG='Y' THEN
1627 Ahl_Debug_Pub.debug ('p_x_resrc_assign_tbl(i).wip_entity_id' ||p_x_resrc_assign_tbl(i).wip_entity_id );
1628 Ahl_Debug_Pub.debug ('p_x_resrc_assign_tbl(i).organization_id' ||p_x_resrc_assign_tbl(i).organization_id );
1629 Ahl_Debug_Pub.debug ('p_x_resrc_assign_tbl(i).operation_seq_number' ||p_x_resrc_assign_tbl(i).operation_seq_number );
1630 Ahl_Debug_Pub.debug ('p_x_resrc_assign_tbl(i).resource_seq_number' ||p_x_resrc_assign_tbl(i).resource_seq_number );
1631 Ahl_Debug_Pub.debug ('p_x_resrc_assign_tbl(i).instance_id' ||p_x_resrc_assign_tbl(i).instance_id );
1632 Ahl_Debug_Pub.debug ('p_x_resrc_assign_tbl(i).serial_number' ||p_x_resrc_assign_tbl(i).serial_number );
1633
1634 END IF;
1635
1636 --
1637 l_resrc_assign_tbl(j).WIP_ENTITY_ID := p_x_resrc_assign_tbl(i).wip_entity_id;
1638 l_resrc_assign_tbl(j).ORGANIZATION_ID := p_x_resrc_assign_tbl(i).organization_id;
1639 l_resrc_assign_tbl(j).WORKORDER_ID := p_x_resrc_assign_tbl(i).workorder_id;
1640 l_resrc_assign_tbl(j).OPERATION_SEQ_NUMBER := p_x_resrc_assign_tbl(i).operation_seq_number;
1641 l_resrc_assign_tbl(j).RESOURCE_SEQ_NUMBER := p_x_resrc_assign_tbl(i).resource_seq_number;
1642 l_resrc_assign_tbl(j).INSTANCE_ID := p_x_resrc_assign_tbl(i).instance_id;
1643 l_resrc_assign_tbl(j).SERIAL_NUMBER := p_x_resrc_assign_tbl(i).serial_number;
1644 l_resrc_assign_tbl(j).ASSIGN_START_DATE := p_x_resrc_assign_tbl(i).assign_start_date;
1645 l_resrc_assign_tbl(j).ASSIGN_END_DATE := p_x_resrc_assign_tbl(i).assign_end_date;
1646 l_resrc_assign_tbl(j).OPERATION_FLAG := 'C';
1647
1648 j := j + 1;
1649 --
1650 END LOOP;
1651 --
1652 END IF;
1653 --Call AHL Eam Job Pvt
1654
1655 AHL_EAM_JOB_PVT.process_resource_assign
1656 (
1657 p_api_version => l_api_version,
1658 p_init_msg_list => p_init_msg_list,
1659 p_commit => p_commit,
1660 p_validation_level => p_validation_level,
1661 p_default => l_default,
1662 p_module_type => p_module_type,
1663 x_return_status => l_return_status,
1664 x_msg_count => l_msg_count,
1665 x_msg_data => l_msg_data,
1666 p_resource_assign_tbl => l_resrc_assign_tbl);
1667
1668 --
1669 IF l_return_status = 'S' THEN
1670 IF p_x_resrc_assign_tbl.COUNT > 0 THEN
1671 FOR i IN p_x_resrc_assign_tbl.FIRST..p_x_resrc_assign_tbl.LAST LOOP
1672 IF p_x_resrc_assign_tbl(i).Assignment_id is null or p_x_resrc_assign_tbl(i).Assignment_id = FND_API.G_MISS_NUM
1673 THEN
1674 -- These conditions are required for optional fields
1675 -- Employee Id
1676 IF p_x_resrc_assign_tbl(i).Employee_id = FND_API.G_MISS_NUM
1677 THEN
1678 l_resrc_assign_tbl(i).Employee_id := NULL;
1679 ELSE
1680 l_resrc_assign_tbl(i).Employee_id := p_x_resrc_assign_tbl(i).Employee_id;
1681 END IF;
1682
1683 -- Serial Number
1684 IF p_x_resrc_assign_tbl(i).Serial_Number = FND_API.G_MISS_CHAR
1685 THEN
1686 l_resrc_assign_tbl(i).Serial_Number := NULL;
1687 ELSE
1688 l_resrc_assign_tbl(i).Serial_Number := p_x_resrc_assign_tbl(i).Serial_Number;
1689 END IF;
1690 -- Instance Id
1691 IF p_x_resrc_assign_tbl(i).Instance_id = FND_API.G_MISS_NUM
1692 THEN
1693 l_resrc_assign_tbl(i).Instance_id := NULL;
1694 ELSE
1695 l_resrc_assign_tbl(i).Instance_id := p_x_resrc_assign_tbl(i).Instance_id;
1696 END IF;
1697 -- Last Updated Date
1698 IF p_x_resrc_assign_tbl(i).last_update_login = FND_API.G_MISS_NUM
1699 THEN
1700 l_resrc_assign_tbl(i).last_update_login := NULL;
1701 ELSE
1702 l_resrc_assign_tbl(i).last_update_login := p_x_resrc_assign_tbl(i).last_update_login;
1703 END IF;
1704 -- Attribute Category
1705 IF p_x_resrc_assign_tbl(i).attribute_category = FND_API.G_MISS_CHAR
1706 THEN
1707 l_resrc_assign_tbl(i).attribute_category := NULL;
1708 ELSE
1709 l_resrc_assign_tbl(i).attribute_category := p_x_resrc_assign_tbl(i).attribute_category;
1710 END IF;
1711 -- Attribute1
1712 IF p_x_resrc_assign_tbl(i).attribute1 = FND_API.G_MISS_CHAR
1713 THEN
1714 l_resrc_assign_tbl(i).attribute1 := NULL;
1715 ELSE
1716 l_Resrc_Assign_tbl(i).attribute1 := p_x_Resrc_Assign_tbl(i).attribute1;
1717 END IF;
1718 -- Attribute2
1719 IF p_x_resrc_assign_tbl(i).attribute2 = FND_API.G_MISS_CHAR
1720 THEN
1721 l_Resrc_Assign_tbl(i).attribute2 := NULL;
1722 ELSE
1723 l_Resrc_Assign_tbl(i).attribute2 := p_x_resrc_assign_tbl(i).attribute2;
1724 END IF;
1725 -- Attribute3
1726 IF p_x_resrc_assign_tbl(i).attribute3 = FND_API.G_MISS_CHAR
1727 THEN
1728 l_Resrc_Assign_tbl(i).attribute3 := NULL;
1729 ELSE
1730 l_Resrc_Assign_tbl(i).attribute3 := p_x_resrc_assign_tbl(i).attribute3;
1731 END IF;
1732 -- Attribute4
1733 IF p_x_resrc_assign_tbl(i).attribute4 = FND_API.G_MISS_CHAR
1734 THEN
1735 l_Resrc_Assign_tbl(i).attribute4 := NULL;
1736 ELSE
1737 l_Resrc_Assign_tbl(i).attribute4 := p_x_resrc_assign_tbl(i).attribute4;
1738 END IF;
1739 -- Attribute5
1740 IF p_x_resrc_assign_tbl(i).attribute5 = FND_API.G_MISS_CHAR
1741 THEN
1742 l_Resrc_Assign_tbl(i).attribute5 := NULL;
1743 ELSE
1744 l_Resrc_Assign_tbl(i).attribute5 := p_x_resrc_assign_tbl(i).attribute5;
1745 END IF;
1746 -- Attribute6
1747 IF p_x_resrc_assign_tbl(i).attribute6 = FND_API.G_MISS_CHAR
1748 THEN
1749 l_Resrc_Assign_tbl(i).attribute6 := NULL;
1750 ELSE
1751 l_Resrc_Assign_tbl(i).attribute6 := p_x_resrc_assign_tbl(i).attribute6;
1752 END IF;
1753 -- Attribute7
1754 IF p_x_resrc_assign_tbl(i).attribute7 = FND_API.G_MISS_CHAR
1755 THEN
1756 l_Resrc_Assign_tbl(i).attribute7 := NULL;
1757 ELSE
1758 l_Resrc_Assign_tbl(i).attribute7 := p_x_resrc_assign_tbl(i).attribute7;
1759 END IF;
1760 -- Attribute8
1761 IF p_x_resrc_assign_tbl(i).attribute8 = FND_API.G_MISS_CHAR
1762 THEN
1763 l_Resrc_Assign_tbl(i).attribute8 := NULL;
1764 ELSE
1765 l_Resrc_Assign_tbl(i).attribute8 := p_x_resrc_assign_tbl(i).attribute8;
1766 END IF;
1767 -- Attribute9
1768 IF p_x_resrc_assign_tbl(i).attribute9 = FND_API.G_MISS_CHAR
1769 THEN
1770 l_Resrc_Assign_tbl(i).attribute9 := NULL;
1771 ELSE
1772 l_Resrc_Assign_tbl(i).attribute9 := p_x_resrc_assign_tbl(i).attribute9;
1773 END IF;
1774 -- Attribute10
1775 IF p_x_resrc_assign_tbl(i).attribute10 = FND_API.G_MISS_CHAR
1776 THEN
1777 l_Resrc_Assign_tbl(i).attribute10 := NULL;
1778 ELSE
1779 l_Resrc_Assign_tbl(i).attribute10 := p_x_resrc_assign_tbl(i).attribute10;
1780 END IF;
1781 -- Attribute11
1782 IF p_x_resrc_assign_tbl(i).attribute11 = FND_API.G_MISS_CHAR
1783 THEN
1784 l_Resrc_Assign_tbl(i).attribute11 := NULL;
1785 ELSE
1786 l_Resrc_Assign_tbl(i).attribute11 := p_x_resrc_assign_tbl(i).attribute11;
1787 END IF;
1788 -- Attribute12
1789 IF p_x_resrc_assign_tbl(i).attribute12 = FND_API.G_MISS_CHAR
1790 THEN
1791 l_Resrc_Assign_tbl(i).attribute12 := NULL;
1792 ELSE
1793 l_Resrc_Assign_tbl(i).attribute12 := p_x_resrc_assign_tbl(i).attribute12;
1794 END IF;
1795 -- Attribute13
1796 IF p_x_resrc_assign_tbl(i).attribute13 = FND_API.G_MISS_CHAR
1797 THEN
1798 l_Resrc_Assign_tbl(i).attribute13 := NULL;
1799 ELSE
1800 l_Resrc_Assign_tbl(i).attribute13 := p_x_resrc_assign_tbl(i).attribute13;
1801 END IF;
1802 -- Attribute14
1803 IF p_x_resrc_assign_tbl(i).attribute14 = FND_API.G_MISS_CHAR
1804 THEN
1805 l_Resrc_Assign_tbl(i).attribute14 := NULL;
1806 ELSE
1807 l_Resrc_Assign_tbl(i).attribute14 := p_x_resrc_assign_tbl(i).attribute14;
1808 END IF;
1809 -- Attribute15
1810 IF p_x_resrc_assign_tbl(i).attribute15 = FND_API.G_MISS_CHAR
1811 THEN
1812 l_Resrc_Assign_tbl(i).attribute15 := NULL;
1813 ELSE
1814 l_Resrc_Assign_tbl(i).attribute15 := p_x_resrc_assign_tbl(i).attribute15;
1815 END IF;
1816
1817 --Standard check to count messages
1818 l_msg_count := Fnd_Msg_Pub.count_msg;
1819
1820 IF l_msg_count > 0 THEN
1821 X_msg_count := l_msg_count;
1822 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1823 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1824 END IF;
1825
1826 --
1827 -- Get Sequence Number for Resource Assignment ID
1828 SELECT AHL_WORK_ASSIGNMENTS_S.NEXTVAL
1829 INTO l_assignment_id FROM DUAL;
1830
1831 --Check for Record Exists
1832 OPEN Sch_id_exists(l_assignment_id);
1833 FETCH Sch_id_exists INTO l_dummy;
1834 CLOSE Sch_id_exists;
1835 --
1836 IF l_dummy IS NOT NULL THEN
1837 Fnd_Message.SET_NAME('AHL','AHL_PP_SEQUENCE_NO_EXISTS');
1838 Fnd_Msg_Pub.ADD;
1839 RAISE FND_API.G_EXC_ERROR;
1840 END IF;
1841
1842
1843 -- Create Record in schedule Resources
1844 Insert_Row (
1845 X_ASSIGNMENT_ID => l_assignment_id,
1846 X_OBJECT_VERSION_NUMBER => 1,
1847 X_LAST_UPDATE_DATE => SYSDATE,
1848 X_LAST_UPDATED_BY => fnd_global.user_id,
1849 X_CREATION_DATE => SYSDATE,
1850 X_CREATED_BY => fnd_global.user_id,
1851 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
1852 X_OPERATION_RESOURCE_ID => p_x_resrc_assign_tbl(i).oper_resource_id,
1853 X_EMPLOYEE_ID => l_Resrc_Assign_tbl(i).employee_id,
1854 X_SERIAL_NUMBER => l_Resrc_Assign_tbl(i).serial_number,
1855 X_INSTANCE_ID => l_Resrc_Assign_tbl(i).instance_id,
1856 X_ASSIGN_START_DATE => p_x_resrc_assign_tbl(i).assign_start_date,
1857 X_ASSIGN_END_DATE => p_x_resrc_assign_tbl(i).assign_end_date,
1858 X_SELF_ASSIGNED_FLAG => p_x_resrc_assign_tbl(i).self_assigned_flag,
1859 -- X_LOGIN_DATE => p_x_resrc_assign_tbl(i).login_date,
1860 X_ATTRIBUTE_CATEGORY => l_Resrc_Assign_tbl(i).attribute_category,
1861 X_ATTRIBUTE1 => l_Resrc_Assign_tbl(i).attribute1,
1862 X_ATTRIBUTE2 => l_Resrc_Assign_tbl(i).attribute2,
1863 X_ATTRIBUTE3 => l_Resrc_Assign_tbl(i).attribute3,
1864 X_ATTRIBUTE4 => l_Resrc_Assign_tbl(i).attribute4,
1865 X_ATTRIBUTE5 => l_Resrc_Assign_tbl(i).attribute5,
1866 X_ATTRIBUTE6 => l_Resrc_Assign_tbl(i).attribute6,
1867 X_ATTRIBUTE7 => l_Resrc_Assign_tbl(i).attribute7,
1868 X_ATTRIBUTE8 => l_Resrc_Assign_tbl(i).attribute8,
1869 X_ATTRIBUTE9 => l_Resrc_Assign_tbl(i).attribute9,
1870 X_ATTRIBUTE10 => l_Resrc_Assign_tbl(i).attribute10,
1871 X_ATTRIBUTE11 => l_Resrc_Assign_tbl(i).attribute11,
1872 X_ATTRIBUTE12 => l_Resrc_Assign_tbl(i).attribute12,
1873 X_ATTRIBUTE13 => l_Resrc_Assign_tbl(i).attribute13,
1874 X_ATTRIBUTE14 => l_Resrc_Assign_tbl(i).attribute14,
1875 X_ATTRIBUTE15 => l_Resrc_Assign_tbl(i).attribute15
1876 );
1877
1878 p_x_resrc_assign_tbl(i).ASSIGNMENT_ID := l_assignment_id;
1879 p_x_resrc_assign_tbl(i) := l_Resrc_Assign_tbl(i);
1880 END IF;
1881
1882 END LOOP;
1883 END IF; -- Count > 0
1884 END IF; -- Return status from Eam Api
1885 ------------------------End of Body---------------------------------------
1886 --Standard check to count messages
1887 l_msg_count := Fnd_Msg_Pub.count_msg;
1888
1889 IF l_msg_count > 0 THEN
1890 X_msg_count := l_msg_count;
1891 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1892 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1893 END IF;
1894
1895 --Standard check for commit
1896 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1897 COMMIT;
1898 END IF;
1899 -- Debug info
1900 IF G_DEBUG='Y' THEN
1901 Ahl_Debug_Pub.debug( 'End of public api Create Resource Reqst','+PPResrc_Assign_Pvt+');
1902 -- Check if API is called in debug mode. If yes, disable debug.
1903 Ahl_Debug_Pub.disable_debug;
1904 END IF;
1905
1906 EXCEPTION
1907 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1908 ROLLBACK TO Create_Resrc_Assign;
1909 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1910 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1911 p_count => x_msg_count,
1912 p_data => x_msg_data);
1913
1914 IF G_DEBUG='Y' THEN
1915 AHL_DEBUG_PUB.log_app_messages (
1916 x_msg_count, x_msg_data, 'ERROR' );
1917 --AHL_DEBUG_PUB.debug( 'ahl_ltp_pp_Resources_pvt. Create Resource Reqst','+PPResrc_Assign_Pvt+');
1918 -- Check if API is called in debug mode. If yes, disable debug.
1919 AHL_DEBUG_PUB.disable_debug;
1920 END IF;
1921
1922 WHEN FND_API.G_EXC_ERROR THEN
1923 ROLLBACK TO Create_Resrc_Assign;
1924 X_return_status := FND_API.G_RET_STS_ERROR;
1925 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1926 p_count => x_msg_count,
1927 p_data => X_msg_data);
1928 IF G_DEBUG='Y' THEN
1929 -- Debug info.
1930 AHL_DEBUG_PUB.log_app_messages (
1931 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1932 AHL_DEBUG_PUB.debug( 'ahl_ltp_pp_Resources_pvt. Create Resource Reqst','+PPResrc_Assign_Pvt+');
1933 -- Check if API is called in debug mode. If yes, disable debug.
1934 AHL_DEBUG_PUB.disable_debug;
1935 END IF;
1936
1937 WHEN OTHERS THEN
1938 ROLLBACK TO Create_Resrc_Assign;
1939 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1940 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1941 THEN
1942 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PP_RESRC_ASSIGN_PVT',
1943 p_procedure_name => 'CREATE_Resrc_Assign',
1944 p_error_text => SUBSTR(SQLERRM,1,240));
1945 END IF;
1946 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1947 p_count => x_msg_count,
1948 p_data => X_msg_data);
1949
1950 IF G_DEBUG='Y' THEN
1951 -- Debug info.
1952 AHL_DEBUG_PUB.log_app_messages (
1953 x_msg_count, x_msg_data, 'SQL ERROR' );
1954 --AHL_DEBUG_PUB.debug( 'ahl_ltp_pp_Resources_pvt. Create Resource Reqst','+PPResrc_Assign_Pvt+');
1955 -- Check if API is called in debug mode. If yes, disable debug.
1956 AHL_DEBUG_PUB.disable_debug;
1957 END IF;
1958
1959 END Create_Resrc_Assign;
1960
1961 --------------------------------------------------------------------------------------
1962 --
1963 -- Start of Comments --
1964 -- Procedure name : Update_Resrc_Assign
1965 -- Type : Private
1966 -- Function : Validates Resource Information and modify records into
1967 -- Schedule Resource table for non routine jobs and loads record
1968 -- into MRP_SCHEDULE_INTERFACE table Launches Concurrent Program to
1969 -- initiate Resource reservation
1970 -- Updates Resource table with Assignment Id
1971 -- Pre-reqs :
1972 -- Parameters :
1973 --
1974 -- Standard IN Parameters :
1975 -- p_api_version IN NUMBER Required
1976 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1977 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1978 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1979 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
1980 -- p_module_type IN VARCHAR2 Default NULL.
1981 --
1982 -- Standard OUT Parameters :
1983 -- x_return_status OUT VARCHAR2 Required
1984 -- x_msg_count OUT NUMBER Required
1985 -- x_msg_data OUT VARCHAR2 Required
1986 --
1987 -- Update Resource Assignment Parameters:
1988 -- p_x_resrc_assign_tbl IN OUT NOCOPY AHL_PP_RESRC_ASSIGN_PVT.Resrc_Assign_Tbl_Type,
1989 -- Contains Resource information to perform Resource reservation
1990 --
1991 -- Version :
1992 -- Initial Version 1.0
1993 --
1994 -- End of Comments.
1995
1996 PROCEDURE Update_Resrc_Assign (
1997 p_api_version IN NUMBER,
1998 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
1999 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
2000 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
2001 p_module_type IN VARCHAR2 := NULL,
2002 p_x_resrc_assign_tbl IN OUT NOCOPY Resrc_Assign_Tbl_Type,
2003 x_return_status OUT NOCOPY VARCHAR2,
2004 x_msg_count OUT NOCOPY NUMBER,
2005 x_msg_data OUT NOCOPY VARCHAR2
2006 )
2007 IS
2008 -- To find all information from AHL_OPERATION_RESOURCES view
2009 CURSOR c_assign (x_id IN NUMBER) IS
2010 SELECT * FROM AHL_WORK_ASSIGNMENTS
2011 WHERE ASSIGNMENT_ID = x_id;
2012 c_assign_rec c_assign%ROWTYPE;
2013
2014 /*CURSOR c_resource (x_id IN NUMBER) IS
2015 SELECT * FROM AHL_PP_ASSIGNMENT_V
2016 WHERE ASSIGNMENT_ID = x_id;
2017 */
2018 --Modified by Srini for Performance fix
2019 CURSOR c_resource (x_id IN NUMBER) IS
2020 SELECT WOA.operation_resource_id requirement_id,
2021 WOP.workorder_id job_id,
2022 BOM.resource_type resource_type_code,
2023 OPR.resource_id
2024 FROM ahl_operation_resources OPR,
2025 ahl_work_assignments WOA,
2026 ahl_workorder_operations WOP,
2027 bom_resources BOM
2028 WHERE OPR.operation_resource_id = WOA.operation_resource_id
2029 AND OPR.workorder_operation_id = WOP.workorder_operation_id
2030 AND OPR.resource_id = BOM.resource_id
2031 AND WOA.assignment_id = x_id;
2032
2033 c_resource_rec c_resource%ROWTYPE;
2034
2035 /*CURSOR c_assign1 (x_id IN NUMBER, x_oper IN NUMBER, x_resrc IN NUMBER) IS
2036 SELECT resource_id FROM AHL_PP_REQUIREMENT_V
2037 WHERE job_id = x_id
2038 AND resource_sequence = x_resrc
2039 AND operation_sequence = x_oper;
2040 */
2041 --Modified by Srini for Performance fix
2042 CURSOR c_assign1 (x_id IN NUMBER, x_oper IN NUMBER, x_resrc IN NUMBER) IS
2043 SELECT OPR.resource_id
2044 FROM ahl_workorder_operations WOP,
2045 ahl_operation_resources OPR
2046 WHERE WOP.workorder_operation_id = OPR.workorder_operation_id
2047 AND WOP.operation_sequence_num = x_oper
2048 AND OPR.resource_sequence_num = x_resrc
2049 AND WOP.workorder_id = x_id;
2050
2051
2052 /*CURSOR c_work (x_id IN NUMBER) IS
2053 SELECT wip_entity_id,organization_id,
2054 department_id FROM AHL_Workorders_V
2055 WHERE workorder_id = x_id;
2056 */
2057 -- Fix for bug# 6452479.
2058 -- Get Dept ID from WIP Operations.
2059 CURSOR c_work (x_id IN NUMBER,
2060 x_operation_seq_num IN NUMBER) IS
2061 SELECT a.wip_entity_id, wo.organization_id,
2062 wo.department_id
2063 FROM AHL_Workorders a, wip_operations wo
2064 WHERE a.wip_entity_id = wo.wip_entity_id
2065 AND wo.operation_seq_num = x_operation_seq_num
2066 AND a.workorder_id = x_id;
2067 /*
2068 --Modified by Srini for Performance fix
2069 CURSOR c_work (x_id IN NUMBER) IS
2070 SELECT wip_entity_id,organization_id,
2071 department_id
2072 FROM AHL_Workorders a, ahl_visits_b b
2073 WHERE a.visit_id = b.visit_id
2074 AND workorder_id = x_id;
2075 */
2076
2077 CURSOR c_instance_cur (c_person_id IN NUMBER,
2078 c_resource_id IN NUMBER,
2079 c_dept_id IN NUMBER)
2080 IS
2081 SELECT a.instance_id
2082 FROM BOM_DEPT_RES_INSTANCES A, BOM_RESOURCE_EMPLOYEES B
2083 WHERE A.INSTANCE_ID = B.INSTANCE_ID
2084 AND B.PERSON_ID = c_person_id
2085 AND A.RESOURCE_ID = c_resource_id
2086 AND A.DEPARTMENT_ID in (
2087 SELECT
2088 nvl(bdr.SHARE_FROM_DEPT_ID,
2089 bdr.department_id)
2090 FROM
2091 bom_department_resources bdr
2092 WHERE
2093 bdr.resource_id = c_resource_id and
2094 bdr.department_id = c_dept_id
2095 );
2096
2097 -- cursor to get the resource req dates
2098 CURSOR resrc_req_dates(x_id IN NUMBER, x_oper IN NUMBER, x_resrc IN NUMBER) IS
2099 SELECT scheduled_start_date, scheduled_end_date
2100 FROM ahl_workorder_operations WOP,
2101 ahl_operation_resources OPR
2102 WHERE WOP.workorder_operation_id = OPR.workorder_operation_id
2103 AND WOP.operation_sequence_num = x_oper
2104 AND OPR.resource_sequence_num = x_resrc
2105 AND WOP.workorder_id = x_id;
2106
2107 -- Cursor added by Balaji for Bug # 6728602
2108 -- Cursor fetches the resource requirement start and end date seconds.
2109 -- This value is passed to EAM to avoid scheduling hierarchy error.
2110 -- Bug # 6728602 -- start
2111 CURSOR c_get_res_sec(p_wo_id IN NUMBER, p_op_seq IN NUMBER)
2112 IS
2113 SELECT
2114 TO_CHAR(WOP.FIRST_UNIT_START_DATE, 'ss'),
2115 TO_CHAR(WOP.LAST_UNIT_COMPLETION_DATE, 'ss')
2116 FROM
2117 wip_operations WOP,
2118 ahl_workorders AWO
2119 WHERE
2120 WOP.OPERATION_SEQ_NUM = p_op_seq
2121 AND WOP.wip_entity_id = AWO.wip_entity_id
2122 AND AWO.workorder_id = p_wo_id;
2123
2124 l_st_date_sec VARCHAR2(30);
2125 l_end_date_sec VARCHAR2(30);
2126 l_sec VARCHAR2(30);
2127 -- Bug # 6728602 -- end
2128
2129 l_api_name CONSTANT VARCHAR2(30) := 'Update_Resrc_Assign';
2130 l_api_version CONSTANT NUMBER := 1.0;
2131 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
2132
2133 l_msg_count NUMBER;
2134 l_wo_operation_id NUMBER;
2135 l_assignment_id NUMBER;
2136 l_serial_id NUMBER;
2137 l_resrc_seq_num NUMBER;
2138 l_object_version_number NUMBER;
2139 l_oper_resrc_id NUMBER;
2140 l_process_status NUMBER;
2141 l_employee_id NUMBER;
2142 l_resource_type NUMBER;
2143 l_dept_id NUMBER;
2144 l_resource_id NUMBER;
2145 l_instance_id NUMBER;
2146 l_wip_entity_id NUMBER;
2147 l_organization_id NUMBER;
2148
2149 l_return_status VARCHAR2(1);
2150 l_msg_data VARCHAR2(2000);
2151 l_error_message VARCHAR2(120);
2152 l_employee_name VARCHAR2(240);
2153
2154 --l_res_start_date DATE;
2155 --l_res_end_date DATE;
2156
2157
2158 l_Resrc_Assign_Tbl Resrc_Assign_Tbl_Type;
2159 l_default VARCHAR2(10);
2160 j NUMBER;
2161
2162 l_hour VARCHAR2(30);
2163 l_min VARCHAR2(30);
2164 l_date_time VARCHAR2(30);
2165
2166 BEGIN
2167 --------------------Initialize ----------------------------------
2168 -- Standard Start of API savepoint
2169 SAVEPOINT Update_Resrc_Assign;
2170
2171 -- Check if API is called in debug mode. If yes, enable debug.
2172 IF G_DEBUG='Y' THEN
2173 AHL_DEBUG_PUB.enable_debug;
2174 END IF;
2175 -- Debug info.
2176 IF G_DEBUG='Y' THEN
2177 AHL_DEBUG_PUB.debug( 'Enter ahl_pp_assign_pvt. Update Resource reqst','+PPResrc_Assign_Pvt+');
2178 END IF;
2179
2180 -- Standard call to check for call compatibility.
2181 IF FND_API.to_boolean(p_init_msg_list)
2182 THEN
2183 FND_MSG_PUB.initialize;
2184 END IF;
2185
2186 -- Initialize API return status to success
2187 x_return_status := FND_API.G_RET_STS_SUCCESS;
2188
2189 -- Initialize message list if p_init_msg_list is set to TRUE.
2190 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2191 p_api_version,
2192 l_api_name,G_PKG_NAME)
2193 THEN
2194 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2195 END IF;
2196
2197 --------------------Start of API Body-----------------------------------
2198 --Start API Body
2199 --
2200 IF p_x_resrc_assign_tbl.COUNT > 0 THEN
2201 --
2202 FOR i IN p_x_resrc_assign_tbl.FIRST..p_x_resrc_assign_tbl.LAST
2203 LOOP
2204 --
2205 IF p_module_type = 'JSP'
2206 THEN
2207 p_x_resrc_assign_tbl(i).instance_id := NULL;
2208 p_x_resrc_assign_tbl(i).employee_id := NULL;
2209 END IF;
2210 --
2211 IF G_DEBUG='Y' THEN
2212 AHL_DEBUG_PUB.debug( 'Assignment_id' || p_x_resrc_assign_tbl(i).ASSIGNMENT_ID);
2213 AHL_DEBUG_PUB.debug( 'Workorder ID = ' || p_x_resrc_assign_tbl(i).workorder_id);
2214 AHL_DEBUG_PUB.debug( 'Oper Seq Num = ' || p_x_resrc_assign_tbl(i).operation_seq_number);
2215 AHL_DEBUG_PUB.debug( 'Resrc Seq Num = ' || p_x_resrc_assign_tbl(i).resource_seq_number);
2216 END IF;
2217 -- For Resource Type
2218 OPEN c_resource(p_x_resrc_assign_tbl(i).ASSIGNMENT_ID);
2219 FETCH c_resource INTO c_resource_rec;
2220 CLOSE c_resource;
2221 --Assign values
2222 p_x_resrc_assign_tbl(i).resource_type_code := c_resource_rec.resource_type_code;
2223 l_resource_type := c_resource_rec.resource_type_code;
2224 p_x_resrc_assign_tbl(i).oper_resource_id := c_resource_rec.requirement_id;
2225 p_x_resrc_assign_tbl(i).workorder_id := c_resource_rec.job_id;
2226
2227 IF G_DEBUG='Y' THEN
2228 AHL_DEBUG_PUB.debug(l_full_name || 'Operation Resource Id = ' || p_x_resrc_assign_tbl(i).oper_resource_id);
2229 AHL_DEBUG_PUB.debug(l_full_name || 'Resource Type = ' || l_resource_type);
2230 END IF;
2231
2232 OPEN c_work (p_x_resrc_assign_tbl(i).workorder_id,
2233 p_x_resrc_assign_tbl(i).operation_seq_number);
2234 FETCH c_work INTO l_wip_entity_id,l_organization_id,l_dept_id;
2235 CLOSE c_work;
2236
2237 -- rroy
2238 -- ACL Changes
2239
2240 l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked(p_workorder_id => p_x_resrc_assign_tbl(i).workorder_id,
2241 p_ue_id => NULL,
2242 p_visit_id => NULL,
2243 p_item_instance_id => NULL);
2244 IF l_return_status = FND_API.G_TRUE THEN
2245 FND_MESSAGE.Set_Name('AHL', 'AHL_PP_UPD_RESASG_UNTLCKD');
2246 FND_MSG_PUB.ADD;
2247 RAISE FND_API.G_EXC_ERROR;
2248 END IF;
2249
2250 -- rroy
2251 -- ACL Changes
2252
2253 --Assign wip entity id
2254 p_x_resrc_assign_tbl(i).wip_entity_id := l_wip_entity_id;
2255 p_x_resrc_assign_tbl(i).organization_id := l_organization_id;
2256 p_x_resrc_assign_tbl(i).department_id := l_dept_id;
2257
2258 -- Get Resource id
2259 OPEN c_assign1 (p_x_resrc_assign_tbl(i).workorder_id,
2260 p_x_resrc_assign_tbl(i).operation_seq_number,
2261 p_x_resrc_assign_tbl(i).resource_seq_number);
2262 FETCH c_assign1 INTO l_resource_id;
2263 CLOSE c_assign1;
2264 --
2265 -- to align the assignment dates to the resource req dates
2266 --Required to check the operation start dates and resource start and end date are same
2267 /*OPEN resrc_req_dates(p_x_resrc_assign_tbl(i).workorder_id,
2268 p_x_resrc_assign_tbl(i).operation_seq_number,
2269 p_x_resrc_assign_tbl(i).resource_seq_number);
2270
2271 FETCH resrc_req_dates INTO l_res_start_date,l_res_end_date;
2272 CLOSE resrc_req_dates;
2273 --Validation is required to include resource timestamp for Requested start date
2274 -- requested end date
2275 IF (TRUNC(p_x_resrc_assign_tbl(i).assign_start_date) = TRUNC(l_res_start_date )
2276 AND
2277 TRUNC(p_x_resrc_assign_tbl(i).assign_end_date) = TRUNC(l_res_start_date ))
2278 THEN
2279
2280 p_x_resrc_assign_tbl(i).assign_start_date := l_res_start_date;
2281 p_x_resrc_assign_tbl(i).assign_end_date := l_res_start_date;
2282
2283 ELSIF (TRUNC(p_x_resrc_assign_tbl(i).assign_start_date) = TRUNC(l_res_end_date)
2284 AND
2285 TRUNC(p_x_resrc_assign_tbl(i).assign_end_date) = TRUNC(l_res_end_date )) THEN
2286
2287 p_x_resrc_assign_tbl(i).assign_start_date := l_res_end_date;
2288 p_x_resrc_assign_tbl(i).assign_end_date := l_res_end_date;
2289
2290 ELSIF (TRUNC(p_x_resrc_assign_tbl(i).assign_start_date) = TRUNC(l_res_start_date )
2291 AND
2292 TRUNC(p_x_resrc_assign_tbl(i).assign_end_date) <> TRUNC(l_res_start_date )) THEN
2293
2294 p_x_resrc_assign_tbl(i).assign_start_date := l_res_start_date;
2295
2296 ELSIF (TRUNC(p_x_resrc_assign_tbl(i).assign_start_date) <> TRUNC(l_res_start_date )
2297 AND
2298 TRUNC(p_x_resrc_assign_tbl(i).assign_end_date) = TRUNC(l_res_end_date )) THEN
2299
2300 p_x_resrc_assign_tbl(i).assign_end_date := l_res_end_date;
2301
2302 END IF;
2303 */
2304 -- Bug # 6728602 -- start
2305 OPEN c_get_res_sec(
2306 p_x_resrc_assign_tbl(i).workorder_id,
2307 p_x_resrc_assign_tbl(i).operation_seq_number
2308 );
2309 FETCH c_get_res_sec INTO l_st_date_sec,l_end_date_sec;
2310 CLOSE c_get_res_sec;
2311 -- Bug # 6728602 -- end;
2312
2313 -- R12
2314 -- Take into account the start and end times
2315 IF p_x_resrc_assign_tbl(i).assign_start_date IS NOT NULL THEN
2316 IF p_x_resrc_assign_tbl(i).assign_start_hour IS NULL THEN
2317 l_hour := ':00';
2318 ELSE
2319 l_hour := ':' || p_x_resrc_assign_tbl(i).assign_start_hour;
2320 END IF;
2321
2322 IF p_x_resrc_assign_tbl(i).assign_start_min IS NULL THEN
2323 l_min := ':00';
2324 ELSE
2325 l_min := ':' || p_x_resrc_assign_tbl(i).assign_start_min;
2326 END IF;
2327
2328 -- Bug # 6728602 -- start
2329 l_sec := TO_CHAR(p_x_resrc_assign_tbl(i).assign_start_date, 'ss');
2330
2331 IF(l_sec = '00') THEN
2332 l_sec := ':' ||l_st_date_sec;
2333 END IF;
2334
2335 l_date_time := TO_CHAR(p_x_resrc_assign_tbl(i).assign_start_date, 'DD-MM-YYYY')||' '|| l_hour || l_min || l_sec;
2336 p_x_resrc_assign_tbl(i).assign_start_date := TO_DATE(l_date_time , 'DD-MM-YYYY :HH24:MI:SS');
2337 -- Bug # 6728602 -- end
2338 END IF;
2339
2340 IF p_x_resrc_assign_tbl(i).assign_end_date IS NOT NULL THEN
2341 IF p_x_resrc_assign_tbl(i).assign_end_hour IS NULL THEN
2342 l_hour := ':00';
2343 ELSE
2344 l_hour := ':' || p_x_resrc_assign_tbl(i).assign_end_hour;
2345 END IF;
2346
2347 IF p_x_resrc_assign_tbl(i).assign_end_min IS NULL THEN
2348 l_min := ':00';
2349 ELSE
2350 l_min := ':' || p_x_resrc_assign_tbl(i).assign_end_min;
2351 END IF;
2352 -- Bug # 6728602 -- start
2353 l_sec := TO_CHAR(p_x_resrc_assign_tbl(i).assign_end_date, 'ss');
2354
2355 IF(l_sec = '00') THEN
2356 l_sec := ':' ||l_end_date_sec;
2357 END IF;
2358
2359 l_date_time := TO_CHAR(p_x_resrc_assign_tbl(i).assign_end_date, 'DD-MM-YYYY')||' '|| l_hour || l_min || l_sec;
2360 p_x_resrc_assign_tbl(i).assign_end_date := TO_DATE(l_date_time , 'DD-MM-YYYY :HH24:MI:SS');
2361 -- Bug # 6728602 -- end
2362 END IF;
2363
2364
2365
2366 IF l_resource_type <> 2 THEN
2367 -- Convert serial number to instance/ serial id
2368 IF (p_x_resrc_assign_tbl(i).serial_number IS NOT NULL AND
2369 p_x_resrc_assign_tbl(i).serial_number <> Fnd_Api.G_MISS_CHAR ) THEN
2370
2371 OPEN c_assign1 (p_x_resrc_assign_tbl(i).workorder_id,
2372 p_x_resrc_assign_tbl(i).operation_seq_number,
2373 p_x_resrc_assign_tbl(i).resource_seq_number);
2374 FETCH c_assign1 INTO l_resource_id;
2375 CLOSE c_assign1;
2376 --
2377 Check_Serial_Name_Or_Id
2378 (p_serial_id => p_x_resrc_assign_tbl(i).instance_id,
2379 p_serial_number => p_x_resrc_assign_tbl(i).serial_number,
2380 p_workorder_id => p_x_resrc_assign_tbl(i).workorder_id,
2381 p_resource_id => l_resource_id,
2382 p_dept_id => l_dept_id,
2383 p_organization_id => l_organization_id,
2384 x_instance_id => l_instance_id,
2385 x_return_status => l_return_status,
2386 x_error_msg_code => l_msg_data);
2387
2388 IF G_DEBUG='Y' THEN
2389 Ahl_Debug_Pub.debug( l_full_name ||'Status Serial' || l_return_status );
2390 END IF;
2391
2392 IF NVL(l_return_status,'x') <> 'S'
2393 THEN
2394 Fnd_Message.SET_NAME('AHL','AHL_PP_SERIAL_NOT_EXISTS');
2395 Fnd_Msg_Pub.ADD;
2396 RAISE Fnd_Api.G_EXC_ERROR;
2397 END IF;
2398 END IF;
2399
2400 --Assign the returned value
2401 p_x_resrc_assign_tbl(i).instance_id := l_instance_id;
2402
2403 IF G_DEBUG='Y' THEN
2404 Ahl_Debug_Pub.debug( l_full_name ||': Serial Id After= ' || p_x_resrc_assign_tbl(i).instance_id);
2405 END IF;
2406
2407 END IF;
2408 IF G_DEBUG='Y' THEN
2409 AHL_DEBUG_PUB.debug('Resource Type' || p_x_resrc_assign_tbl(i).resource_type_name);
2410 AHL_DEBUG_PUB.debug('Employee Number' || p_x_resrc_assign_tbl(i).employee_number);
2411 AHL_DEBUG_PUB.debug('Employee id' || p_x_resrc_assign_tbl(i).employee_id);
2412 END IF;
2413
2414 IF l_resource_type = 2 THEN
2415
2416 -- For Employee Number
2417 IF p_x_resrc_assign_tbl(i).employee_number IS NOT NULL AND
2418 p_x_resrc_assign_tbl(i).employee_number <> Fnd_Api.G_MISS_CHAR
2419 THEN
2420 Check_Employee_Number_Or_Id
2421 (p_employee_id => p_x_resrc_assign_tbl(i).employee_id,
2422 p_employee_number => p_x_resrc_assign_tbl(i).employee_number,
2423 p_workorder_id => p_x_resrc_assign_tbl(i).workorder_id,
2424 p_oper_resrc_id => p_x_resrc_assign_tbl(i).oper_resource_id,
2425 p_resource_id => l_resource_id,
2426 p_organization_id => l_organization_id,
2427 x_employee_id => p_x_resrc_assign_tbl(i).employee_id,
2428 x_return_status => l_return_status,
2429 x_error_msg_code => l_msg_data
2430 );
2431
2432 IF NVL(l_return_status, 'X') <> 'S'
2433 THEN
2434 Fnd_Message.SET_NAME('AHL','AHL_PP_EMP_NUM_NOT_EXISTS');
2435 Fnd_Msg_Pub.ADD;
2436 RAISE Fnd_Api.G_EXC_ERROR;
2437 END IF;
2438
2439 -- For Employee Name
2440 IF p_x_resrc_assign_tbl(i).employee_name IS NOT NULL AND
2441 p_x_resrc_assign_tbl(i).employee_name <> Fnd_Api.G_MISS_CHAR
2442 THEN
2443 Check_Employee_Name_Or_Id
2444 (p_employee_id => l_employee_id,
2445 p_employee_number => p_x_resrc_assign_tbl(i).employee_number,
2446 p_workorder_id => p_x_resrc_assign_tbl(i).workorder_id,
2447 p_oper_resrc_id => p_x_resrc_assign_tbl(i).oper_resource_id,
2448 p_resource_id => l_resource_id,
2449 p_organization_id => l_organization_id,
2450 x_employee_name => l_employee_name,
2451 x_employee_id => l_employee_id,
2452 x_return_status => l_return_status,
2453 x_error_msg_code => l_msg_data
2454 );
2455
2456 IF NVL(l_return_status, 'X') <> 'S'
2457 THEN
2458 Fnd_Message.SET_NAME('AHL','AHL_PP_EMP_NAME_NOT_EXISTS');
2459 Fnd_Msg_Pub.ADD;
2460 RAISE Fnd_Api.G_EXC_ERROR;
2461 END IF;
2462
2463 IF p_x_resrc_assign_tbl(i).employee_id <> l_employee_id THEN
2464 Fnd_Message.SET_NAME('AHL','AHL_PP_USE_EMP_NAME_LOV');
2465 Fnd_Msg_Pub.ADD;
2466 END IF;
2467
2468 IF p_x_resrc_assign_tbl(i).employee_name <> l_employee_name THEN
2469 Fnd_Message.SET_NAME('AHL','AHL_PP_EMP_NAME_NOT_EXISTS');
2470 Fnd_Msg_Pub.ADD;
2471 END IF;
2472 END IF;
2473 END IF;
2474 --Assign
2475 p_x_resrc_assign_tbl(i).employee_id := l_employee_id;
2476 --
2477 IF G_DEBUG='Y' THEN
2478 AHL_DEBUG_PUB.debug('Resource id' || l_resource_id);
2479 AHL_DEBUG_PUB.debug('Department id' || p_x_resrc_assign_tbl(i).department_id);
2480 AHL_DEBUG_PUB.debug('Employee id' || p_x_resrc_assign_tbl(i).employee_id);
2481 END IF;
2482
2483 -- Get instance id
2484 IF (p_x_resrc_assign_tbl(i).employee_id IS NOT NULL AND p_x_resrc_assign_tbl(i).employee_id <> Fnd_Api.G_MISS_NUM)
2485 THEN
2486 --
2487 OPEN c_instance_cur (p_x_resrc_assign_tbl(i).employee_id,
2488 l_resource_id,
2489 p_x_resrc_assign_tbl(i).department_id);
2490 FETCH c_instance_cur INTO p_x_resrc_assign_tbl(i).instance_id;
2491 CLOSE c_instance_cur;
2492 --
2493 END IF;
2494
2495 END IF;
2496 -------------------------------- Validate -----------------------------------------
2497
2498 Validate_Resrc_Assign (
2499 p_api_version => l_api_version,
2500 p_init_msg_list => p_init_msg_list,
2501 p_commit => p_commit,
2502 p_validation_level => p_validation_level,
2503 p_resrc_assign_rec => p_x_resrc_assign_tbl(i),
2504 x_return_status => l_return_status,
2505 x_msg_count => x_msg_count,
2506 x_msg_data => x_msg_data
2507 );
2508
2509 END LOOP;
2510 END IF;
2511
2512 --Standard check to count messages
2513 l_msg_count := Fnd_Msg_Pub.count_msg;
2514
2515 IF l_msg_count > 0 THEN
2516 X_msg_count := l_msg_count;
2517 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2518 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2519 END IF;
2520
2521 --
2522
2523 -- Call Eam Api to create resource assignment in WIP
2524 IF p_x_resrc_assign_tbl.COUNT > 0 THEN
2525 j := 1;
2526 FOR i IN p_x_resrc_assign_tbl.FIRST..p_x_resrc_assign_tbl.LAST
2527 LOOP
2528
2529 IF G_DEBUG='Y' THEN
2530 Ahl_Debug_Pub.debug ('p_x_resrc_assign_tbl(i).wip_entity_id' ||p_x_resrc_assign_tbl(i).wip_entity_id );
2531 Ahl_Debug_Pub.debug ('p_x_resrc_assign_tbl(i).organization_id' ||p_x_resrc_assign_tbl(i).organization_id );
2532 Ahl_Debug_Pub.debug ('p_x_resrc_assign_tbl(i).operation_seq_number' ||p_x_resrc_assign_tbl(i).operation_seq_number );
2533 Ahl_Debug_Pub.debug ('p_x_resrc_assign_tbl(i).resource_seq_number' ||p_x_resrc_assign_tbl(i).resource_seq_number );
2534 Ahl_Debug_Pub.debug ('p_x_resrc_assign_tbl(i).instance_id' ||p_x_resrc_assign_tbl(i).instance_id );
2535 Ahl_Debug_Pub.debug ('p_x_resrc_assign_tbl(i).serial_number' ||p_x_resrc_assign_tbl(i).serial_number );
2536
2537 END IF;
2538
2539 --
2540 l_resrc_assign_tbl(j).WIP_ENTITY_ID := p_x_resrc_assign_tbl(i).wip_entity_id;
2541 l_resrc_assign_tbl(j).ORGANIZATION_ID := p_x_resrc_assign_tbl(i).organization_id;
2542 l_resrc_assign_tbl(j).OPERATION_SEQ_NUMBER := p_x_resrc_assign_tbl(i).operation_seq_number;
2543 l_resrc_assign_tbl(j).WORKORDER_ID := p_x_resrc_assign_tbl(i).workorder_id;
2544 l_resrc_assign_tbl(j).RESOURCE_SEQ_NUMBER := p_x_resrc_assign_tbl(i).resource_seq_number;
2545 l_resrc_assign_tbl(j).INSTANCE_ID := p_x_resrc_assign_tbl(i).instance_id;
2546 l_resrc_assign_tbl(j).SERIAL_NUMBER := p_x_resrc_assign_tbl(i).serial_number;
2547 l_resrc_assign_tbl(j).ASSIGN_START_DATE := p_x_resrc_assign_tbl(i).assign_start_date;
2548 l_resrc_assign_tbl(j).ASSIGN_END_DATE := p_x_resrc_assign_tbl(i).assign_end_date;
2549 l_resrc_assign_tbl(j).OPERATION_FLAG := 'U';
2550
2551 j := j + 1;
2552 --
2553 END LOOP;
2554 --
2555 END IF;
2556 --Call AHL Eam Job Pvt
2557
2558 AHL_EAM_JOB_PVT.process_resource_assign
2559 (
2560 p_api_version => l_api_version,
2561 p_init_msg_list => p_init_msg_list,
2562 p_commit => p_commit,
2563 p_validation_level => p_validation_level,
2564 p_default => l_default,
2565 p_module_type => p_module_type,
2566 x_return_status => l_return_status,
2567 x_msg_count => l_msg_count,
2568 x_msg_data => l_msg_data,
2569 p_resource_assign_tbl => l_resrc_assign_tbl);
2570
2571 IF l_return_status = 'S' THEN
2572 --
2573 IF p_x_resrc_assign_tbl.COUNT > 0 THEN
2574 --
2575 FOR i IN p_x_resrc_assign_tbl.FIRST..p_x_resrc_assign_tbl.LAST LOOP
2576 --
2577 --Get Assignment details
2578 OPEN c_assign(p_x_resrc_assign_tbl(i).ASSIGNMENT_ID);
2579 FETCH c_assign INTO c_assign_rec;
2580 CLOSE c_assign;
2581
2582 IF G_DEBUG='Y' THEN
2583 AHL_DEBUG_PUB.debug(' Record OVN = ' || p_x_resrc_assign_tbl(i).object_version_number);
2584 AHL_DEBUG_PUB.debug(' Cursor OVN = ' || c_assign_rec.object_version_number);
2585 END IF;
2586
2587 -- Check Object version number.
2588 IF (p_x_resrc_assign_tbl(i).object_version_number <> c_assign_rec.object_version_number) THEN
2589 AHL_DEBUG_PUB.debug(l_full_name || 'Inside OVN comparison');
2590 Fnd_Message.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
2591 Fnd_Msg_Pub.ADD;
2592 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2593 END IF;
2594
2595 IF p_x_resrc_assign_tbl(i).Assignment_id <> FND_API.G_MISS_NUM THEN
2596 -- These conditions are required for optional fields
2597 /*
2598 -- Operation Resource Id
2599 IF p_x_resrc_assign_tbl(i).Operation_resource_id = FND_API.G_MISS_NUM
2600 THEN
2601 p_x_resrc_assign_tbl(i).Operation_resource_id := NULL;
2602 ELSIF p_x_resrc_assign_tbl(i).Operation_resource_id IS NULL THEN
2603 p_x_resrc_assign_tbl(i).Operation_resource_id := c_assign_rec.Operation_resource_id;
2604 END IF;
2605 */
2606
2607 -- Employee Id
2608 IF p_x_resrc_assign_tbl(i).Employee_id = FND_API.G_MISS_NUM
2609 THEN
2610 p_x_resrc_assign_tbl(i).Employee_id := NULL;
2611 ELSIF p_x_resrc_assign_tbl(i).Employee_id IS NULL THEN
2612 p_x_resrc_assign_tbl(i).Employee_id := c_assign_rec.Employee_id;
2613 END IF;
2614
2615 -- Serial Number
2616 IF p_x_resrc_assign_tbl(i).serial_number = FND_API.G_MISS_CHAR
2617 THEN
2618 p_x_resrc_assign_tbl(i).serial_number := NULL;
2619 ELSIF p_x_resrc_assign_tbl(i).serial_number IS NULL THEN
2620 p_x_resrc_assign_tbl(i).serial_number := c_assign_rec.serial_number;
2621 END IF;
2622 -- Instance Id
2623 IF p_x_resrc_assign_tbl(i).Instance_id = FND_API.G_MISS_NUM
2624 THEN
2625 p_x_resrc_assign_tbl(i).Instance_id := NULL;
2626 ELSIF p_x_resrc_assign_tbl(i).instance_id IS NULL THEN
2627 p_x_resrc_assign_tbl(i).Instance_id := c_assign_rec.Instance_id;
2628 END IF;
2629 -- Assign start date
2630 IF p_x_resrc_assign_tbl(i).Assign_start_date = FND_API.G_MISS_DATE
2631 THEN
2632 p_x_resrc_assign_tbl(i).Assign_start_date := NULL;
2633 ELSIF p_x_resrc_assign_tbl(i).assign_start_date IS NULL THEN
2634 p_x_resrc_assign_tbl(i).Assign_start_date := c_assign_rec.Assign_start_date;
2635 END IF;
2636
2637 -- Assign end date
2638 IF p_x_resrc_assign_tbl(i).Assign_end_date = FND_API.G_MISS_DATE
2639 THEN
2640 p_x_resrc_assign_tbl(i).Assign_end_date := NULL;
2641 ELSIF p_x_resrc_assign_tbl(i).assign_end_date IS NULL THEN
2642 p_x_resrc_assign_tbl(i).Assign_end_date := c_assign_rec.Assign_end_date;
2643 END IF;
2644 -- Attribute Category
2645 IF p_x_resrc_assign_tbl(i).attribute_category = FND_API.G_MISS_CHAR
2646 THEN
2647 p_x_resrc_assign_tbl(i).attribute_category := NULL;
2648 ELSIF p_x_resrc_assign_tbl(i).attribute_category IS NULL THEN
2649 p_x_resrc_assign_tbl(i).attribute_category := c_assign_rec.attribute_category;
2650 END IF;
2651 -- Attribute1
2652 IF p_x_resrc_assign_tbl(i).attribute1 = FND_API.G_MISS_CHAR
2653 THEN
2654 p_x_resrc_assign_tbl(i).attribute1 := NULL;
2655 ELSIF p_x_resrc_assign_tbl(i).attribute1 IS NULL THEN
2656 p_x_resrc_assign_tbl(i).attribute1 := c_assign_rec.attribute1;
2657 END IF;
2658 -- Attribute2
2659 IF p_x_resrc_assign_tbl(i).attribute2 = FND_API.G_MISS_CHAR
2660 THEN
2661 p_x_resrc_assign_tbl(i).attribute2 := NULL;
2662 ELSIF p_x_resrc_assign_tbl(i).attribute2 IS NULL THEN
2663 p_x_resrc_assign_tbl(i).attribute2 := c_assign_rec.attribute2;
2664 END IF;
2665 -- Attribute3
2666 IF p_x_resrc_assign_tbl(i).attribute3 = FND_API.G_MISS_CHAR
2667 THEN
2668 p_x_resrc_assign_tbl(i).attribute3 := NULL;
2669 ELSIF p_x_resrc_assign_tbl(i).attribute3 IS NULL THEN
2670 p_x_resrc_assign_tbl(i).attribute3 := c_assign_rec.attribute3;
2671 END IF;
2672 -- Attribute4
2673 IF p_x_resrc_assign_tbl(i).attribute4 = FND_API.G_MISS_CHAR
2674 THEN
2675 p_x_resrc_assign_tbl(i).attribute4 := NULL;
2676 ELSIF p_x_resrc_assign_tbl(i).attribute4 IS NULL THEN
2677 p_x_resrc_assign_tbl(i).attribute4 := c_assign_rec.attribute4;
2678 END IF;
2679 -- Attribute5
2680 IF p_x_resrc_assign_tbl(i).attribute5 = FND_API.G_MISS_CHAR
2681 THEN
2682 p_x_resrc_assign_tbl(i).attribute5 := NULL;
2683 ELSIF p_x_resrc_assign_tbl(i).attribute5 IS NULL THEN
2684 p_x_resrc_assign_tbl(i).attribute5 := c_assign_rec.attribute5;
2685 END IF;
2686 -- Attribute6
2687 IF p_x_resrc_assign_tbl(i).attribute6 = FND_API.G_MISS_CHAR
2688 THEN
2689 p_x_resrc_assign_tbl(i).attribute6 := NULL;
2690 ELSIF p_x_resrc_assign_tbl(i).attribute6 IS NULL THEN
2691 p_x_resrc_assign_tbl(i).attribute6 := c_assign_rec.attribute6;
2692 END IF;
2693 -- Attribute7
2694 IF p_x_resrc_assign_tbl(i).attribute7 = FND_API.G_MISS_CHAR
2695 THEN
2696 p_x_resrc_assign_tbl(i).attribute7 := NULL;
2697 ELSIF p_x_resrc_assign_tbl(i).attribute7 IS NULL THEN
2698 p_x_resrc_assign_tbl(i).attribute7 := c_assign_rec.attribute7;
2699 END IF;
2700 -- Attribute8
2701 IF p_x_resrc_assign_tbl(i).attribute8 = FND_API.G_MISS_CHAR
2702 THEN
2703 p_x_resrc_assign_tbl(i).attribute8 := NULL;
2704 ELSIF p_x_resrc_assign_tbl(i).attribute8 IS NULL THEN
2705 p_x_resrc_assign_tbl(i).attribute8 := c_assign_rec.attribute8;
2706 END IF;
2707 -- Attribute9
2708 IF p_x_resrc_assign_tbl(i).attribute9 = FND_API.G_MISS_CHAR
2709 THEN
2710 p_x_resrc_assign_tbl(i).attribute9 := NULL;
2711 ELSIF p_x_resrc_assign_tbl(i).attribute9 IS NULL THEN
2712 p_x_resrc_assign_tbl(i).attribute9 := c_assign_rec.attribute9;
2713 END IF;
2714 -- Attribute10
2715 IF p_x_resrc_assign_tbl(i).attribute10 = FND_API.G_MISS_CHAR
2716 THEN
2717 p_x_resrc_assign_tbl(i).attribute10 := NULL;
2718 ELSIF p_x_resrc_assign_tbl(i).attribute10 IS NULL THEN
2719 p_x_resrc_assign_tbl(i).attribute10 := c_assign_rec.attribute10;
2720 END IF;
2721 -- Attribute11
2722 IF p_x_resrc_assign_tbl(i).attribute11 = FND_API.G_MISS_CHAR
2723 THEN
2724 p_x_resrc_assign_tbl(i).attribute11 := NULL;
2725 ELSIF p_x_resrc_assign_tbl(i).attribute11 IS NULL THEN
2726 p_x_resrc_assign_tbl(i).attribute11 := c_assign_rec.attribute11;
2727 END IF;
2728 -- Attribute12
2729 IF p_x_resrc_assign_tbl(i).attribute12 = FND_API.G_MISS_CHAR
2730 THEN
2731 p_x_resrc_assign_tbl(i).attribute12 := NULL;
2732 ELSIF p_x_resrc_assign_tbl(i).attribute12 IS NULL THEN
2733 p_x_resrc_assign_tbl(i).attribute12 := c_assign_rec.attribute12;
2734 END IF;
2735 -- Attribute13
2736 IF p_x_resrc_assign_tbl(i).attribute13 = FND_API.G_MISS_CHAR
2737 THEN
2738 p_x_resrc_assign_tbl(i).attribute13 := NULL;
2739 ELSIF p_x_resrc_assign_tbl(i).attribute13 IS NULL THEN
2740 p_x_resrc_assign_tbl(i).attribute13 := c_assign_rec.attribute13;
2741 END IF;
2742 -- Attribute14
2743 IF p_x_resrc_assign_tbl(i).attribute14 = FND_API.G_MISS_CHAR
2744 THEN
2745 p_x_resrc_assign_tbl(i).attribute14 := NULL;
2746 ELSIF p_x_resrc_assign_tbl(i).attribute14 IS NULL THEN
2747 p_x_resrc_assign_tbl(i).attribute14 := c_assign_rec.attribute14;
2748 END IF;
2749 -- Attribute15
2750 IF p_x_resrc_assign_tbl(i).attribute15 = FND_API.G_MISS_CHAR
2751 THEN
2752 p_x_resrc_assign_tbl(i).attribute15 := NULL;
2753 ELSIF p_x_resrc_assign_tbl(i).attribute15 IS NULL THEN
2754 p_x_resrc_assign_tbl(i).attribute15 := c_assign_rec.attribute15;
2755 END IF;
2756 -- Self Assigned Flag
2757 IF p_x_resrc_assign_tbl(i).self_assigned_flag = FND_API.G_MISS_NUM
2758 THEN
2759 p_x_resrc_assign_tbl(i).self_assigned_flag := NULL;
2760 ELSIF p_x_resrc_assign_tbl(i).self_assigned_flag IS NULL THEN
2761 p_x_resrc_assign_tbl(i).self_assigned_flag := c_assign_rec.self_assigned_flag;
2762 END IF;
2763 /*
2764 -- Login Date
2765 IF p_x_resrc_assign_tbl(i).login_date = FND_API.G_MISS_DATE
2766 THEN
2767 p_x_resrc_assign_tbl(i).login_date := NULL;
2768 ELSIF p_x_resrc_assign_tbl(i).login_date IS NULL THEN
2769 p_x_resrc_assign_tbl(i).login_date := c_assign_rec.login_date;
2770 END IF;
2771 */
2772
2773 --Standard check to count messages
2774 l_msg_count := Fnd_Msg_Pub.count_msg;
2775
2776 IF l_msg_count > 0 THEN
2777 x_msg_count := l_msg_count;
2778 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2779 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2780 END IF;
2781
2782 IF G_DEBUG='Y' THEN
2783 AHL_DEBUG_PUB.debug(l_full_name || ' Before calling Update Row procedure');
2784 END IF;
2785
2786 -- Create Record in schedule Resources
2787 Update_Row (
2788 X_ASSIGNMENT_ID => p_x_resrc_assign_tbl(i).Assignment_id,
2789 X_OBJECT_VERSION_NUMBER => p_x_resrc_assign_tbl(i).object_version_number,
2790 X_OPERATION_RESOURCE_ID => c_assign_rec.Operation_resource_id,
2791 X_EMPLOYEE_ID => p_x_resrc_assign_tbl(i).employee_id,
2792 X_SERIAL_NUMBER => p_x_resrc_assign_tbl(i).serial_number,
2793 X_INSTANCE_ID => p_x_resrc_assign_tbl(i).instance_id,
2794 X_ASSIGN_START_DATE => p_x_resrc_assign_tbl(i).assign_start_date,
2795 X_ASSIGN_END_DATE => p_x_resrc_assign_tbl(i).assign_end_date,
2796 X_SELF_ASSIGNED_FLAG => p_x_resrc_assign_tbl(i).self_assigned_flag,
2797 --X_LOGIN_DATE => p_x_resrc_assign_tbl(i).login_date,
2798 X_ATTRIBUTE_CATEGORY => p_x_resrc_assign_tbl(i).attribute_category,
2799 X_ATTRIBUTE1 => p_x_resrc_assign_tbl(i).attribute1,
2800 X_ATTRIBUTE2 => p_x_resrc_assign_tbl(i).attribute2,
2801 X_ATTRIBUTE3 => p_x_resrc_assign_tbl(i).attribute3,
2802 X_ATTRIBUTE4 => p_x_resrc_assign_tbl(i).attribute4,
2803 X_ATTRIBUTE5 => p_x_resrc_assign_tbl(i).attribute5,
2804 X_ATTRIBUTE6 => p_x_resrc_assign_tbl(i).attribute6,
2805 X_ATTRIBUTE7 => p_x_resrc_assign_tbl(i).attribute7,
2806 X_ATTRIBUTE8 => p_x_resrc_assign_tbl(i).attribute8,
2807 X_ATTRIBUTE9 => p_x_resrc_assign_tbl(i).attribute9,
2808 X_ATTRIBUTE10 => p_x_resrc_assign_tbl(i).attribute10,
2809 X_ATTRIBUTE11 => p_x_resrc_assign_tbl(i).attribute11,
2810 X_ATTRIBUTE12 => p_x_resrc_assign_tbl(i).attribute12,
2811 X_ATTRIBUTE13 => p_x_resrc_assign_tbl(i).attribute13,
2812 X_ATTRIBUTE14 => p_x_resrc_assign_tbl(i).attribute14,
2813 X_ATTRIBUTE15 => p_x_resrc_assign_tbl(i).attribute15,
2814 X_LAST_UPDATE_DATE => SYSDATE,
2815 X_LAST_UPDATED_BY => fnd_global.user_id,
2816 X_LAST_UPDATE_LOGIN => fnd_global.login_id
2817 );
2818 END IF;
2819
2820 IF G_DEBUG='Y' THEN
2821 AHL_DEBUG_PUB.debug(l_full_name || ' After calling Update Row procedure');
2822 AHL_DEBUG_PUB.debug(l_full_name || ' Assign Start Date = ' || p_x_resrc_assign_tbl(i).assign_start_date);
2823 AHL_DEBUG_PUB.debug(l_full_name || ' Assign End Date = ' || p_x_resrc_assign_tbl(i).assign_end_date);
2824 END IF;
2825 END LOOP;
2826 END IF;
2827
2828 END IF; -- Return status from Ahl Eam Api
2829 ------------------------End of Body---------------------------------------
2830 --Standard check to count messages
2831 l_msg_count := Fnd_Msg_Pub.count_msg;
2832
2833 IF l_msg_count > 0 THEN
2834 X_msg_count := l_msg_count;
2835 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2836 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2837 END IF;
2838
2839 --Standard check for commit
2840 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2841 COMMIT;
2842 END IF;
2843 -- Debug info
2844 IF G_DEBUG='Y' THEN
2845 Ahl_Debug_Pub.debug( 'End of public api Update Resource Reqst','+PPResrc_Assign_Pvt+');
2846 -- Check if API is called in debug mode. If yes, disable debug.
2847 Ahl_Debug_Pub.disable_debug;
2848 --
2849 END IF;
2850 EXCEPTION
2851 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2852 ROLLBACK TO update_Resrc_Assign;
2853 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2854 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2855 p_count => x_msg_count,
2856 p_data => x_msg_data);
2857 IF G_DEBUG='Y' THEN
2858 AHL_DEBUG_PUB.log_app_messages(x_msg_count, x_msg_data, 'ERROR' );
2859
2860 -- Check if API is called in debug mode. If yes, disable debug.
2861 AHL_DEBUG_PUB.disable_debug;
2862 --
2863 END IF;
2864 WHEN FND_API.G_EXC_ERROR THEN
2865 ROLLBACK TO update_Resrc_Assign;
2866 X_return_status := FND_API.G_RET_STS_ERROR;
2867 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2868 p_count => x_msg_count,
2869 p_data => X_msg_data);
2870 IF G_DEBUG='Y' THEN
2871 -- Debug info.
2872 AHL_DEBUG_PUB.log_app_messages (
2873 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
2874 --AHL_DEBUG_PUB.debug( 'ahl_ltp_pp_Resources_pvt. Update Resource Reqst','+PPResrc_Assign_Pvt+');
2875 -- Check if API is called in debug mode. If yes, disable debug.
2876 AHL_DEBUG_PUB.disable_debug;
2877 END IF;
2878 WHEN OTHERS THEN
2879 ROLLBACK TO update_Resrc_Assign;
2880 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2881 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2882 THEN
2883 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PP_RESRC_ASSIGN_PVT',
2884 p_procedure_name => 'UPDATE_Resrc_Assign',
2885 p_error_text => SUBSTR(SQLERRM,1,240));
2886 END IF;
2887 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2888 p_count => x_msg_count,
2889 p_data => X_msg_data);
2890
2891 IF G_DEBUG='Y' THEN
2892 -- Debug info.
2893 AHL_DEBUG_PUB.log_app_messages (
2894 x_msg_count, x_msg_data, 'SQL ERROR' );
2895 -- Check if API is called in debug mode. If yes, disable debug.
2896 AHL_DEBUG_PUB.disable_debug;
2897 END IF;
2898 END Update_Resrc_Assign;
2899 --
2900 PROCEDURE Remove_Resource_Assignment (
2901 p_api_version IN NUMBER,
2902 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
2903 p_commit IN VARCHAR2 := Fnd_Api.g_false,
2904 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
2905 p_module_type IN VARCHAR2 := 'JSP',
2906 p_x_resrc_assign_tbl IN OUT NOCOPY Resrc_Assign_tbl_Type,
2907 x_return_status OUT NOCOPY VARCHAR2,
2908 x_msg_count OUT NOCOPY NUMBER,
2909 x_msg_data OUT NOCOPY VARCHAR2
2910 )
2911 IS
2912 -- Get the current record
2913 CURSOR get_resrc_assign_cur(c_assign_id IN NUMBER)
2914 IS
2915 SELECT * FROM AHL_WORK_ASSIGNMENTS
2916 WHERE assignment_id = c_assign_id;
2917
2918 -- Get the workorder details
2919 /*CURSOR get_resource_details (c_oper_resource_id IN NUMBER)
2920 IS
2921 SELECT operation_resource_id,resource_sequence_num,
2922 operation_sequence_num,resource_id,b.workorder_operation_id,
2923 c.workorder_id,wip_entity_id,organization_id,department_id
2924 FROM ahl_operation_resources a, ahl_workorder_operations b, ahl_workorders_v c
2925 WHERE a.WORKORDER_OPERATION_id = b.workorder_operation_id
2926 AND b.workorder_id = c.workorder_id
2927 AND a.operation_resource_id = c_oper_resource_id;
2928 */
2929 --Modified by srini for performance fix
2930 CURSOR get_resource_details (c_oper_resource_id IN NUMBER)
2931 IS
2932 SELECT operation_resource_id,resource_sequence_num,
2933 operation_sequence_num,resource_id,b.workorder_operation_id,
2934 c.workorder_id,c.wip_entity_id, d.organization_id,department_id
2935 FROM ahl_operation_resources a, ahl_workorder_operations b,
2936 ahl_workorders c , wip_discrete_jobs d, wip_operations e
2937 WHERE a.WORKORDER_OPERATION_id = b.workorder_operation_id
2938 AND b.workorder_id = c.workorder_id
2939 AND c.wip_entity_id = d.wip_entity_id
2940 AND c.wip_entity_id = e.wip_entity_id
2941 AND b.operation_sequence_num = e.operation_seq_num
2942 AND a.operation_resource_id = c_oper_resource_id;
2943
2944
2945 l_api_name CONSTANT VARCHAR2(30) := 'REMOVE_Resource_Assignment';
2946 l_api_version CONSTANT NUMBER := 1.0;
2947 l_return_status VARCHAR2(1);
2948 l_msg_data VARCHAR2(200);
2949 l_msg_count NUMBER;
2950 l_dummy NUMBER;
2951 l_error_message VARCHAR2(30);
2952 l_resrc_assign_tbl Resrc_Assign_tbl_Type;
2953 l_resrc_assign_rec get_resrc_assign_cur%ROWTYPE;
2954 l_resource_details get_resource_details%ROWTYPE;
2955 l_default VARCHAR2(10);
2956 j NUMBER;
2957 --
2958 BEGIN
2959 --------------------Initialize ----------------------------------
2960 -- Standard Start of API savepoint
2961 SAVEPOINT Remove_Resource_Assignment;
2962
2963 -- Check if API is called in debug mode. If yes, enable debug.
2964 IF G_DEBUG='Y' THEN
2965 Ahl_Debug_Pub.enable_debug;
2966 END IF;
2967 -- Debug info.
2968 IF G_DEBUG='Y' THEN
2969 Ahl_Debug_Pub.debug( 'enter ahl_ltp_reqst_matrl_pvt Remove Resource Assignment ','+MAATP+');
2970 END IF;
2971 -- Standard call to check for call compatibility.
2972 IF Fnd_Api.to_boolean(p_init_msg_list)
2973 THEN
2974 Fnd_Msg_Pub.initialize;
2975 END IF;
2976
2977 -- Initialize API return status to success
2978 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
2979
2980 -- Initialize message list if p_init_msg_list is set to TRUE.
2981 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
2982 p_api_version,
2983 l_api_name,G_PKG_NAME)
2984 THEN
2985 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2986 END IF;
2987
2988 ------------------------Start API Body ---------------------------------
2989
2990 IF p_x_resrc_assign_tbl.COUNT > 0 THEN
2991 --
2992 FOR i IN p_x_resrc_assign_tbl.FIRST..p_x_resrc_assign_tbl.LAST
2993 LOOP
2994 --
2995 IF (p_x_resrc_assign_tbl(i).assignment_id IS NOT NULL AND
2996 p_x_resrc_assign_tbl(i).assignment_id <> FND_API.G_MISS_NUM) THEN
2997 --Get the exisitng record
2998 OPEN get_resrc_assign_cur(p_x_resrc_assign_tbl(i).assignment_id);
2999 FETCH get_resrc_assign_cur INTO l_resrc_assign_rec;
3000 IF get_resrc_assign_cur%NOTFOUND THEN
3001 Fnd_Message.Set_Name('AHL','AHL_COM_INVALID_RECORD');
3002 Fnd_Msg_Pub.ADD;
3003 CLOSE get_resrc_assign_cur;
3004 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3005 END IF;
3006 --
3007 CLOSE get_resrc_assign_cur;
3008 END IF;
3009 --Check for object version number
3010 IF p_x_resrc_assign_tbl(i).object_version_number <> l_resrc_assign_rec.object_version_number
3011 THEN
3012 Fnd_Message.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
3013 Fnd_Msg_Pub.ADD;
3014 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3015 END IF;
3016 --
3017 -- Get Wip entity ,operation resource details
3018 OPEN get_resource_details(l_resrc_assign_rec.operation_resource_id);
3019 FETCH get_resource_details INTO l_resource_details;
3020 CLOSE get_resource_details;
3021 --
3022 -- Assign to
3023 p_x_resrc_assign_tbl(i).operation_seq_number := l_resource_details.operation_sequence_num;
3024 p_x_resrc_assign_tbl(i).resource_seq_number := l_resource_details.resource_sequence_num;
3025 p_x_resrc_assign_tbl(i).workorder_operation_id := l_resource_details.workorder_operation_id;
3026 p_x_resrc_assign_tbl(i).workorder_id := l_resource_details.workorder_id;
3027 p_x_resrc_assign_tbl(i).wip_entity_id := l_resource_details.wip_entity_id;
3028 p_x_resrc_assign_tbl(i).organization_id := l_resource_details.organization_id;
3029 p_x_resrc_assign_tbl(i).instance_id := l_resrc_assign_rec.instance_id;
3030 -- p_x_resrc_assign_tbl(i).serial_number := l_resrc_assign_rec.serial_number;
3031 p_x_resrc_assign_tbl(i).assign_start_date := l_resrc_assign_rec.assign_start_date;
3032 p_x_resrc_assign_tbl(i).assign_end_date := l_resrc_assign_rec.assign_end_date;
3033
3034 -- rroy
3035 -- ACL Changes
3036 l_return_status := AHL_PRD_UTIL_PKG.IsDelAsg_Enabled(p_assignment_id => p_x_resrc_assign_tbl(i).assignment_id,
3037 p_workorder_id => p_x_resrc_assign_tbl(i).workorder_id);
3038 IF l_return_status = FND_API.G_FALSE THEN
3039 RAISE FND_API.G_EXC_ERROR;
3040 END IF;
3041
3042
3043 -- rroy
3044 -- ACL Changes
3045 END LOOP;
3046 END IF;
3047
3048 --Standard check to count messages
3049 l_msg_count := Fnd_Msg_Pub.count_msg;
3050
3051 IF l_msg_count > 0 THEN
3052 x_msg_count := l_msg_count;
3053 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3054 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3055 END IF;
3056
3057
3058 -- Call Eam Api to create resource assignment in WIP
3059 IF p_x_resrc_assign_tbl.COUNT > 0 THEN
3060 j := 1;
3061 FOR i IN p_x_resrc_assign_tbl.FIRST..p_x_resrc_assign_tbl.LAST
3062 LOOP
3063 --
3064 l_resrc_assign_tbl(j).WIP_ENTITY_ID := p_x_resrc_assign_tbl(i).wip_entity_id;
3065 l_resrc_assign_tbl(j).ORGANIZATION_ID := p_x_resrc_assign_tbl(i).organization_id;
3066 l_resrc_assign_tbl(j).WORKORDER_ID := p_x_resrc_assign_tbl(i).workorder_id;
3067 l_resrc_assign_tbl(j).OPERATION_SEQ_NUMBER := p_x_resrc_assign_tbl(i).operation_seq_number;
3068 l_resrc_assign_tbl(j).RESOURCE_SEQ_NUMBER := p_x_resrc_assign_tbl(i).resource_seq_number;
3069 l_resrc_assign_tbl(j).INSTANCE_ID := p_x_resrc_assign_tbl(i).instance_id;
3070 l_resrc_assign_tbl(j).SERIAL_NUMBER := p_x_resrc_assign_tbl(i).serial_number;
3071 l_resrc_assign_tbl(j).ASSIGN_START_DATE := p_x_resrc_assign_tbl(i).assign_start_date;
3072 l_resrc_assign_tbl(j).ASSIGN_END_DATE := p_x_resrc_assign_tbl(i).assign_end_date;
3073 l_resrc_assign_tbl(j).OPERATION_FLAG := 'D';
3074
3075 j := j + 1;
3076 --
3077 END LOOP;
3078 --
3079 END IF;
3080 --Call AHL Eam Job Pvt
3081
3082 AHL_EAM_JOB_PVT.process_resource_assign
3083 (
3084 p_api_version => l_api_version,
3085 p_init_msg_list => p_init_msg_list,
3086 p_commit => p_commit,
3087 p_validation_level => p_validation_level,
3088 p_default => l_default,
3089 p_module_type => p_module_type,
3090 x_return_status => l_return_status,
3091 x_msg_count => l_msg_count,
3092 x_msg_data => l_msg_data,
3093 p_resource_assign_tbl => l_resrc_assign_tbl);
3094
3095 IF l_return_status = 'S' THEN
3096 --
3097 IF p_x_resrc_assign_tbl.count > 0 THEN
3098 FOR i IN p_x_resrc_assign_tbl.FIRST..p_x_resrc_assign_tbl.LAST
3099 LOOP
3100 --
3101 DELETE FROM AHL_WORK_ASSIGNMENTS
3102 WHERE ASSIGNMENT_ID = p_x_resrc_assign_tbl(i).assignment_id;
3103 --
3104 END LOOP;
3105 END IF;
3106
3107 END IF;
3108 ---------------------------End of Body---------------------------------------
3109 --Standard check to count messages
3110 l_msg_count := Fnd_Msg_Pub.count_msg;
3111
3112 IF l_msg_count > 0 THEN
3113 X_msg_count := l_msg_count;
3114 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3115 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3116 END IF;
3117
3118 --Standard check for commit
3119 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
3120 COMMIT;
3121 END IF;
3122 -- Debug info
3123 IF G_DEBUG='Y' THEN
3124 Ahl_Debug_Pub.debug( 'End of private api Remove Resource Assignment ','+MAMRP+');
3125 -- Check if API is called in debug mode. If yes, disable debug.
3126 Ahl_Debug_Pub.disable_debug;
3127 --
3128 END IF;
3129 EXCEPTION
3130 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
3131 ROLLBACK TO Remove_Resource_Assignment;
3132 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3133 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
3134 p_count => x_msg_count,
3135 p_data => x_msg_data);
3136 IF G_DEBUG='Y' THEN
3137 Ahl_Debug_Pub.log_app_messages (
3138 x_msg_count, x_msg_data, 'ERROR' );
3139 --Ahl_Debug_Pub.debug( 'ahl_ltp_reqst_matrl_pvt. Remove Resource Assignment ','+MAMRP+');
3140 -- Check if API is called in debug mode. If yes, disable debug.
3141 Ahl_Debug_Pub.disable_debug;
3142 END IF;
3143
3144 WHEN Fnd_Api.G_EXC_ERROR THEN
3145 ROLLBACK TO remove_Resource_Assignment;
3146 X_return_status := Fnd_Api.G_RET_STS_ERROR;
3147 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
3148 p_count => x_msg_count,
3149 p_data => X_msg_data);
3150 IF G_DEBUG='Y' THEN
3151 -- Debug info.
3152 Ahl_Debug_Pub.log_app_messages (
3153 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
3154 --Ahl_Debug_Pub.debug( 'ahl_ltp_reqst_matrl_pvt. Remove Resource Assignment ','+MAMRP+');
3155 -- Check if API is called in debug mode. If yes, disable debug.
3156 Ahl_Debug_Pub.disable_debug;
3157 END IF;
3158
3159 WHEN OTHERS THEN
3160 ROLLBACK TO remove_Resource_Assignment;
3161 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3162 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
3163 THEN
3164 Fnd_Msg_Pub.add_exc_msg(p_pkg_name => 'AHL_PP_RESRC_ASSIGN_PVT',
3165 p_procedure_name => 'REMOVE_Resource_Assignment',
3166 p_error_text => SUBSTR(SQLERRM,1,240));
3167 END IF;
3168 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
3169 p_count => x_msg_count,
3170 p_data => X_msg_data);
3171 IF G_DEBUG='Y' THEN
3172 -- Debug info.
3173 Ahl_Debug_Pub.log_app_messages (
3174 x_msg_count, x_msg_data, 'SQL ERROR' );
3175 --Ahl_Debug_Pub.debug( 'ahl_ltp_reqst_matrl_pvt. Remove Resource Assignment ','+MTMRP+');
3176 -- Check if API is called in debug mode. If yes, disable debug.
3177 Ahl_Debug_Pub.disable_debug;
3178 END IF;
3179 END Remove_Resource_Assignment;
3180
3181 -----------------------------------------------------------------------------------
3182 -- Public Procedure Definitions follow --
3183 -----------------------------------------
3184 -- Start of Comments --
3185 -- Procedure name : Process_Resrc_Assign
3186 -- Type : Private
3187 -- Function : Process ............................based on operation flag
3188 -- Pre-reqs :
3189 -- Parameters :
3190 --
3191 -- Standard IN Parameters :
3192 -- p_api_version IN NUMBER Required
3193 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
3194 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
3195 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
3196 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
3197 -- p_module_type IN VARCHAR2 Default NULL.
3198 --
3199 -- Standard OUT Parameters :
3200 -- x_return_status OUT VARCHAR2 Required
3201 -- x_msg_count OUT NUMBER Required
3202 -- x_msg_data OUT VARCHAR2 Required
3203 --
3204 -- Process Resource Assignment Parameters:
3205 -- p_x_resrc_assign_tbl IN OUT NOCOPY AHL_PP_RESRC_ASSIGN_PVT.Resrc_Assign_Tbl_Type,
3206 -- Contains........................ on operation flag
3207 --
3208 -- Version :
3209 -- Initial Version 1.0
3210 --
3211 -- End of Comments.
3212
3213 PROCEDURE Process_Resrc_Assign (
3214 p_api_version IN NUMBER,
3215 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
3216 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
3217 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
3218 p_module_type IN VARCHAR2 := NULL,
3219 p_operation_flag IN VARCHAR2,
3220 p_x_resrc_assign_tbl IN OUT NOCOPY AHL_PP_RESRC_ASSIGN_PVT.Resrc_Assign_Tbl_Type,
3221 x_return_status OUT NOCOPY VARCHAR2,
3222 x_msg_count OUT NOCOPY NUMBER,
3223 x_msg_data OUT NOCOPY VARCHAR2
3224 )
3225 IS
3226 l_api_name CONSTANT VARCHAR2(30) := 'Process_Resrc_Assign';
3227 l_api_version CONSTANT NUMBER := 1.0;
3228 l_msg_count NUMBER;
3229 l_return_status VARCHAR2(1);
3230 l_msg_data VARCHAR2(2000);
3231 l_resrc_Assign_rec AHL_PP_RESRC_ASSIGN_PVT.Resrc_Assign_Rec_Type;
3232
3233 BEGIN
3234 --------------------Initialize ----------------------------------
3235 -- Standard Start of API savepoint
3236 SAVEPOINT Process_Resrc_Assign;
3237
3238 -- Check if API is called in debug mode. If yes, enable debug.
3239 IF G_DEBUG='Y' THEN
3240 AHL_DEBUG_PUB.enable_debug;
3241 END IF;
3242 -- Debug info.
3243 IF G_DEBUG='Y' THEN
3244 AHL_DEBUG_PUB.debug( 'Enter AHL_PP_RESRC_ASSIGN.process_resrc_assign','+PPResrc_Assign_Pvt+');
3245 END IF;
3246 -- Standard call to check for call compatibility.
3247 IF FND_API.to_boolean(p_init_msg_list)
3248 THEN
3249 FND_MSG_PUB.initialize;
3250 END IF;
3251
3252 -- Initialize API return status to success
3253 x_return_status := FND_API.G_RET_STS_SUCCESS;
3254
3255 -- Initialize message list if p_init_msg_list is set to TRUE.
3256 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
3257 p_api_version,
3258 l_api_name,G_PKG_NAME)
3259 THEN
3260 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3261 END IF;
3262
3263 --------------------Start of API Body-----------------------------------
3264 IF p_x_resrc_assign_tbl.COUNT > 0 THEN
3265 --
3266 IF p_operation_flag = 'C' THEN
3267 --
3268 -- Call create Resource Assignment
3269 IF G_DEBUG='Y' THEN
3270 Ahl_Debug_Pub.debug( 'Start of pvt api for create Resource Assignment','+PPResrc_Assign_Pvt+');
3271 END IF;
3272 Create_Resrc_Assign (
3273 p_api_version => p_api_version,
3274 p_init_msg_list => p_init_msg_list,
3275 p_commit => p_commit,
3276 p_validation_level => p_validation_level,
3277 p_module_type => p_module_type,
3278 p_x_resrc_assign_tbl => p_x_resrc_assign_tbl,
3279 x_return_status => l_return_status,
3280 x_msg_count => l_msg_count,
3281 x_msg_data => l_msg_data
3282 ) ;
3283
3284 IF G_DEBUG='Y' THEN
3285 Ahl_Debug_Pub.debug( 'End of pvt api for create Resource Assignment','+PPResrc_Assign_Pvt+');
3286 END IF;
3287
3288 ELSIF p_operation_flag = 'U' THEN
3289 IF G_DEBUG='Y' THEN
3290 AHL_DEBUG_PUB.debug( 'after update'||p_operation_flag);
3291 END IF;
3292 -- Call Update Resource Assignment
3293 Update_Resrc_Assign (
3294 p_api_version => p_api_version,
3295 p_init_msg_list => p_init_msg_list,
3296 p_commit => p_commit,
3297 p_validation_level => p_validation_level,
3298 p_module_type => p_module_type,
3299 p_x_resrc_assign_tbl => p_x_resrc_assign_tbl,
3300 x_return_status => l_return_status,
3301 x_msg_count => l_msg_count,
3302 x_msg_data => l_msg_data
3303 );
3304
3305 ELSIF p_operation_flag = 'D' THEN
3306 -- Call Remove Resource Assignment
3307 Remove_Resource_Assignment (
3308 p_api_version => p_api_version,
3309 p_init_msg_list => p_init_msg_list,
3310 p_commit => p_commit,
3311 p_validation_level => p_validation_level,
3312 p_module_type => p_module_type,
3313 p_x_resrc_assign_tbl => p_x_resrc_assign_tbl,
3314 x_return_status => l_return_status,
3315 x_msg_count => l_msg_count,
3316 x_msg_data => l_msg_data
3317 );
3318
3319 END IF;
3320 END IF;
3321 ------------------------End of Body---------------------------------------
3322 --Standard check to count messages
3323 l_msg_count := Fnd_Msg_Pub.count_msg;
3324
3325 IF l_msg_count > 0 THEN
3326 x_msg_count := l_msg_count;
3327 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3328 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3329 END IF;
3330
3331 --Standard check for commit
3332 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
3333 COMMIT;
3334 END IF;
3335
3336 -- Debug info
3337 IF G_DEBUG='Y' THEN
3338 Ahl_Debug_Pub.debug( 'End of public api Process Resource Assignment','+PPResrc_Assign_Pvt+');
3339 END IF;
3340 -- Check if API is called in debug mode. If yes, disable debug.
3341 IF G_DEBUG='Y' THEN
3342 Ahl_Debug_Pub.disable_debug;
3343 END IF;
3344
3345 EXCEPTION
3346 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3347 ROLLBACK TO Process_Resrc_Assign;
3348 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3349 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3350 p_count => x_msg_count,
3351 p_data => x_msg_data);
3352 IF G_DEBUG='Y' THEN
3353 AHL_DEBUG_PUB.log_app_messages (
3354 x_msg_count, x_msg_data, 'ERROR' );
3355 -- Check if API is called in debug mode. If yes, disable debug.
3356 AHL_DEBUG_PUB.disable_debug;
3357 END IF;
3358
3359 WHEN FND_API.G_EXC_ERROR THEN
3360 ROLLBACK TO Process_Resrc_Assign;
3361 X_return_status := FND_API.G_RET_STS_ERROR;
3362 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3363 p_count => x_msg_count,
3364 p_data => X_msg_data);
3365 IF G_DEBUG='Y' THEN
3366 -- Debug info.
3367 AHL_DEBUG_PUB.log_app_messages (
3368 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
3369 -- Check if API is called in debug mode. If yes, disable debug.
3370 AHL_DEBUG_PUB.disable_debug;
3371 END IF;
3372 WHEN OTHERS THEN
3373 ROLLBACK TO Process_Resrc_Assign;
3374 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3375 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3376 THEN
3377 FND_MSG_PUB.add_exc_msg(p_pkg_name => 'AHL_PP_RESRC_ASSIGN_PVT',
3378 p_procedure_name => 'Process_Resrc_Assign',
3379 p_error_text => SUBSTR(SQLERRM,1,240));
3380 END IF;
3381 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3382 p_count => x_msg_count,
3383 p_data => X_msg_data);
3384 IF G_DEBUG='Y' THEN
3385 -- Debug info.
3386 AHL_DEBUG_PUB.log_app_messages (
3387 x_msg_count, x_msg_data, 'SQL ERROR' );
3388 -- Check if API is called in debug mode. If yes, disable debug.
3389 AHL_DEBUG_PUB.disable_debug;
3390 END IF;
3391 END Process_Resrc_Assign;
3392
3393 END AHL_PP_RESRC_ASSIGN_PVT;