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