[Home] [Help]
PACKAGE BODY: APPS.AHL_PP_RESRC_REQUIRE_PVT
Source
1 PACKAGE BODY AHL_PP_RESRC_REQUIRE_PVT AS
2 /* $Header: AHLVREQB.pls 120.12.12010000.4 2008/12/28 02:31:31 sracha ship $*/
3
4 ----------------------------------------------
5 -- Declare Constants --
6 -----------------------
7 G_PKG_NAME VARCHAR2(30):= 'AHL_PP_RESRC_REQUIRE_PVT';
8 G_MODULE_TYPE VARCHAR2(30);
9 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
10
11 -------------------------------------------------
12 -- Declare Locally used Record and Table Types --
13 -------------------------------------------------
14
15 -------------------------------------------------
16 -- Declare Local Procedures --
17 -------------------------------------------------
18 -- Process_Resrc_Require -- Remove_Resource_Requirement
19 -- Get_Resource_Requirement -- Update_Resrc_Require
20 -- Create_Resrc_Require
21
22
23 --------------------------------------------------------------------
24 -- PROCEDURE
25 -- Check_Lookup_Name_Or_Id
26 --
27 -- PURPOSE
28 -- Converts Lookup Name/Code to ID/Value or Vice versa
29 --------------------------------------------------------------------
30 PROCEDURE Check_Lookup_Name_Or_Id
31 ( p_lookup_type IN MFG_LOOKUPS.lookup_type%TYPE,
32 p_lookup_code IN MFG_LOOKUPS.lookup_code%TYPE,
33 p_meaning IN MFG_LOOKUPS.meaning%TYPE,
34 p_check_id_flag IN VARCHAR2,
35
36 x_lookup_code OUT NOCOPY NUMBER,
37 x_return_status OUT NOCOPY VARCHAR2)
38 IS
39 BEGIN
40 IF (p_lookup_code IS NOT NULL) THEN
41 IF (p_check_id_flag = 'Y') THEN
42 SELECT lookup_code INTO x_lookup_code
43 FROM MFG_LOOKUPS
44 WHERE lookup_type = p_lookup_type
45 AND lookup_code = p_lookup_code
46 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
47 AND TRUNC(NVL(end_date_active,SYSDATE));
48 ELSE
49 x_lookup_code := p_lookup_code;
50 END IF;
51 ELSE
52 SELECT lookup_code INTO x_lookup_code
53 FROM MFG_LOOKUPS
54 WHERE lookup_type = p_lookup_type
55 AND meaning = p_meaning
56 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
57 AND TRUNC(NVL(end_date_active,SYSDATE));
58 END IF;
59
60 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
61 EXCEPTION
62 WHEN NO_DATA_FOUND THEN
63 x_return_status := Fnd_Api.G_RET_STS_ERROR;
64 WHEN TOO_MANY_ROWS THEN
65 x_return_status := Fnd_Api.G_RET_STS_ERROR;
66 WHEN OTHERS THEN
67 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
68 RAISE;
69 END;
70
71 --------------------------------------------------------------------
72 -- PROCEDURE
73 -- Check_Serial_Name_Or_Id
74 --
75 -- PURPOSE
76 -- Converts Serial Name to ID or Vice versa
77 --------------------------------------------------------------------
78 PROCEDURE Check_Serial_Name_Or_Id
79 (p_serial_id IN NUMBER,
80 p_serial_number IN VARCHAR2,
81
82 x_serial_id OUT NOCOPY NUMBER,
83 x_return_status OUT NOCOPY VARCHAR2,
84 x_error_msg_code OUT NOCOPY VARCHAR2
85 )
86 IS
87 BEGIN
88 IF G_DEBUG='Y' THEN
89 Ahl_Debug_Pub.debug( ': Inside Check Serial Number= ' || p_serial_number);
90 END IF;
91
92 IF (p_serial_number IS NOT NULL) THEN
93 SELECT instance_id
94 INTO x_serial_id
95 FROM BOM_DEPT_RES_INSTANCES
96 WHERE SERIAL_NUMBER = p_serial_number;
97 END IF;
98 IF G_DEBUG='Y' THEN
99 Ahl_Debug_Pub.debug(': Inside Check Serial Id= ' || x_serial_id);
100 END IF;
101
102 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
103 EXCEPTION
104 WHEN NO_DATA_FOUND THEN
105 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
106 x_error_msg_code:= 'AHL_PP_SERIAL_NOT_EXISTS';
107 WHEN TOO_MANY_ROWS THEN
108 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
109 x_error_msg_code:= 'AHL_PP_SERIAL_NOT_EXISTS';
110 WHEN OTHERS THEN
111 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
112 RAISE;
113 END Check_Serial_Name_Or_Id;
114
115 --------------------------------------------------------------------
116 -- PROCEDURE
117 -- Check_Resource_Name_Or_Id
118 --
119 -- PURPOSE
120 -- Converts Resource Name to ID or Vice versa
121 --------------------------------------------------------------------
122 PROCEDURE Check_Resource_Name_Or_Id
123 (p_resource_id IN NUMBER,
124 p_resource_code IN VARCHAR2,
125 p_workorder_id IN NUMBER,
126
127 x_resource_id OUT NOCOPY NUMBER,
128 x_return_status OUT NOCOPY VARCHAR2,
129 x_error_msg_code OUT NOCOPY VARCHAR2
130 )
131 IS
132 BEGIN
133 IF (p_resource_code IS NOT NULL) THEN
134 SELECT DISTINCT(BR.RESOURCE_ID)
135 INTO x_resource_id
136 FROM BOM_RESOURCES BR, BOM_DEPARTMENT_RESOURCES BDR, AHL_WORKORDER_OPERATIONS_V AWV
137 WHERE BR.RESOURCE_ID = BDR.RESOURCE_ID AND BDR.DEPARTMENT_ID = AWV.DEPARTMENT_ID
138 AND AWV.WORKORDER_ID = p_workorder_id AND BR.RESOURCE_CODE = p_resource_code;
139 END IF;
140
141 IF G_DEBUG='Y' THEN
142 Ahl_Debug_Pub.debug(': Inside Check Resource Id= ' || x_Resource_id);
143 END IF;
144
145 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
146 EXCEPTION
147 WHEN NO_DATA_FOUND THEN
148 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
149 x_error_msg_code:= 'AHL_PP_RESOURCE_NOT_EXISTS';
150 WHEN TOO_MANY_ROWS THEN
151 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
152 x_error_msg_code:= 'AHL_PP_RESOURCE_NOT_EXISTS';
153 WHEN OTHERS THEN
154 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
155 RAISE;
156 END Check_Resource_Name_Or_Id;
157
158 --------------------------------------------------------------------
159 -- PROCEDURE
160 -- Insert_Row
161 ---------------------------------------------------------------------
162 PROCEDURE Insert_Row (
163 X_OPERATION_RESOURCE_ID IN NUMBER,
164 X_OBJECT_VERSION_NUMBER IN NUMBER,
165 X_LAST_UPDATE_DATE IN DATE,
166 X_LAST_UPDATED_BY IN NUMBER,
167 X_CREATION_DATE IN DATE,
168 X_CREATED_BY IN NUMBER,
169 X_LAST_UPDATE_LOGIN IN NUMBER,
170 X_RESOURCE_ID IN NUMBER,
171 X_WORKORDER_OPERATION_ID IN NUMBER,
172 X_RESOURCE_SEQ_NUMBER IN NUMBER,
173 X_UOM_CODE IN VARCHAR2,
174 X_QUANTITY IN NUMBER,
175 X_DURATION IN NUMBER,
176 X_SCHEDULED_START_DATE IN DATE,
177 X_SCHEDULED_END_DATE IN DATE,
178 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
179 X_ATTRIBUTE1 IN VARCHAR2,
180 X_ATTRIBUTE2 IN VARCHAR2,
181 X_ATTRIBUTE3 IN VARCHAR2,
182 X_ATTRIBUTE4 IN VARCHAR2,
183 X_ATTRIBUTE5 IN VARCHAR2,
184 X_ATTRIBUTE6 IN VARCHAR2,
185 X_ATTRIBUTE7 IN VARCHAR2,
186 X_ATTRIBUTE8 IN VARCHAR2,
187 X_ATTRIBUTE9 IN VARCHAR2,
188 X_ATTRIBUTE10 IN VARCHAR2,
189 X_ATTRIBUTE11 IN VARCHAR2,
190 X_ATTRIBUTE12 IN VARCHAR2,
191 X_ATTRIBUTE13 IN VARCHAR2,
192 X_ATTRIBUTE14 IN VARCHAR2,
193 X_ATTRIBUTE15 IN VARCHAR2
194 ) IS
195 BEGIN
196 INSERT INTO AHL_OPERATION_RESOURCES (
197 OPERATION_RESOURCE_ID,
198 OBJECT_VERSION_NUMBER,
199 LAST_UPDATE_DATE,
200 LAST_UPDATED_BY,
201 CREATION_DATE,
202 CREATED_BY,
203 LAST_UPDATE_LOGIN,
204 RESOURCE_ID ,
205 WORKORDER_OPERATION_ID ,
206 RESOURCE_SEQUENCE_NUM ,
207 --UOM,
208 QUANTITY ,
209 DURATION ,
210 SCHEDULED_START_DATE,
211 SCHEDULED_END_DATE,
212 ATTRIBUTE_CATEGORY,
213 ATTRIBUTE1,
214 ATTRIBUTE2,
215 ATTRIBUTE3,
216 ATTRIBUTE4,
217 ATTRIBUTE5,
218 ATTRIBUTE6,
219 ATTRIBUTE7,
220 ATTRIBUTE8,
221 ATTRIBUTE9,
222 ATTRIBUTE10,
223 ATTRIBUTE11,
224 ATTRIBUTE12,
225 ATTRIBUTE13,
226 ATTRIBUTE14,
227 ATTRIBUTE15 )
228 VALUES(
229 X_OPERATION_RESOURCE_ID,
230 X_OBJECT_VERSION_NUMBER,
231 X_LAST_UPDATE_DATE,
232 X_LAST_UPDATED_BY,
233 X_CREATION_DATE,
234 X_CREATED_BY,
235 X_LAST_UPDATE_LOGIN,
236 X_RESOURCE_ID ,
237 X_WORKORDER_OPERATION_ID ,
238 X_RESOURCE_SEQ_NUMBER ,
239 --X_UOM_CODE ,
240 X_QUANTITY ,
241 X_DURATION ,
242 X_SCHEDULED_START_DATE ,
243 X_SCHEDULED_END_DATE ,
244 X_ATTRIBUTE_CATEGORY,
245 X_ATTRIBUTE1,
246 X_ATTRIBUTE2,
247 X_ATTRIBUTE3,
248 X_ATTRIBUTE4,
249 X_ATTRIBUTE5,
250 X_ATTRIBUTE6,
251 X_ATTRIBUTE7,
252 X_ATTRIBUTE8,
253 X_ATTRIBUTE9,
254 X_ATTRIBUTE10,
255 X_ATTRIBUTE11,
256 X_ATTRIBUTE12,
257 X_ATTRIBUTE13,
258 X_ATTRIBUTE14,
259 X_ATTRIBUTE15);
260
261 END Insert_Row;
262
263 ---------------------------------------------------------------------
264 -- PROCEDURE
265 -- Update_Row
266 ---------------------------------------------------------------------
267 PROCEDURE UPDATE_ROW (
268 X_OPERATION_RESOURCE_ID IN NUMBER,
269 X_OBJECT_VERSION_NUMBER IN NUMBER,
270 X_RESOURCE_ID IN NUMBER,
271 X_WORKORDER_OPERATION_ID IN NUMBER,
272 X_RESOURCE_SEQ_NUMBER IN NUMBER,
273 X_UOM_CODE IN VARCHAR2,
274 X_QUANTITY IN NUMBER,
275 X_DURATION IN NUMBER,
276 X_SCHEDULED_START_DATE IN DATE,
277 X_SCHEDULED_END_DATE IN DATE,
278 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
279 X_ATTRIBUTE1 IN VARCHAR2,
280 X_ATTRIBUTE2 IN VARCHAR2,
281 X_ATTRIBUTE3 IN VARCHAR2,
282 X_ATTRIBUTE4 IN VARCHAR2,
283 X_ATTRIBUTE5 IN VARCHAR2,
284 X_ATTRIBUTE6 IN VARCHAR2,
285 X_ATTRIBUTE7 IN VARCHAR2,
286 X_ATTRIBUTE8 IN VARCHAR2,
287 X_ATTRIBUTE9 IN VARCHAR2,
288 X_ATTRIBUTE10 IN VARCHAR2,
289 X_ATTRIBUTE11 IN VARCHAR2,
290 X_ATTRIBUTE12 IN VARCHAR2,
291 X_ATTRIBUTE13 IN VARCHAR2,
292 X_ATTRIBUTE14 IN VARCHAR2,
293 X_ATTRIBUTE15 IN VARCHAR2,
294 X_LAST_UPDATE_DATE IN DATE,
295 X_LAST_UPDATED_BY IN NUMBER,
296 X_LAST_UPDATE_LOGIN IN NUMBER
297 )
298 IS
299
300 BEGIN
301 UPDATE AHL_OPERATION_RESOURCES SET
302 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER + 1,
303 RESOURCE_ID = X_RESOURCE_ID ,
304 WORKORDER_OPERATION_ID = X_WORKORDER_OPERATION_ID ,
305 RESOURCE_SEQUENCE_NUM = X_RESOURCE_SEQ_NUMBER ,
306 --UOM = X_UOM_CODE ,
307 QUANTITY = X_QUANTITY ,
308 DURATION = X_DURATION ,
309 SCHEDULED_START_DATE = X_SCHEDULED_START_DATE ,
310 SCHEDULED_END_DATE = X_SCHEDULED_END_DATE ,
311 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
312 ATTRIBUTE1 = X_ATTRIBUTE1,
313 ATTRIBUTE2 = X_ATTRIBUTE2,
314 ATTRIBUTE3 = X_ATTRIBUTE3,
315 ATTRIBUTE4 = X_ATTRIBUTE4,
316 ATTRIBUTE5 = X_ATTRIBUTE5,
317 ATTRIBUTE6 = X_ATTRIBUTE6,
318 ATTRIBUTE7 = X_ATTRIBUTE7,
319 ATTRIBUTE8 = X_ATTRIBUTE8,
320 ATTRIBUTE9 = X_ATTRIBUTE9,
321 ATTRIBUTE10 = X_ATTRIBUTE10,
322 ATTRIBUTE11 = X_ATTRIBUTE11,
323 ATTRIBUTE12 = X_ATTRIBUTE12,
324 ATTRIBUTE13 = X_ATTRIBUTE13,
325 ATTRIBUTE14 = X_ATTRIBUTE14,
326 ATTRIBUTE15 = X_ATTRIBUTE15,
327 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
328 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
329 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
330 WHERE OPERATION_RESOURCE_ID = X_OPERATION_RESOURCE_ID
331 AND OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER;
332
333 END UPDATE_ROW;
334
335 ---------------------------------------------------------------------
336 -- PROCEDURE
337 -- Delete_Row
338 ---------------------------------------------------------------------
339 PROCEDURE DELETE_ROW (
340 X_OPERATION_RESOURCE_ID IN NUMBER
341 ) IS
342 BEGIN
343 DELETE FROM AHL_OPERATION_RESOURCES
344 WHERE OPERATION_RESOURCE_ID = X_OPERATION_RESOURCE_ID;
345 END DELETE_ROW;
346
347 ---------------------------------------------------------------------
348 -- PROCEDURE
349 -- Check_Resrc_Require_Req_Items
350 ---------------------------------------------------------------------
351 PROCEDURE Check_Resrc_Require_Req_Items (
352 p_resrc_Require_rec IN Resrc_Require_Rec_Type,
353 x_return_status OUT NOCOPY VARCHAR2
354 )
355 IS
356 l_Require_start_date DATE;
357 l_Require_end_date DATE;
358 l_eff_st_date DATE;
359 l_eff_end_date DATE;
360 l_sch_st_date DATE;
361 l_sch_end_date DATE;
362
363 -- To find all information from AHL_OPERATION_RESOURCES view
364 CURSOR c_oper_req (x_id IN NUMBER) IS
365 SELECT * FROM AHL_OPERATION_RESOURCES
366 WHERE OPERATION_RESOURCE_ID = x_id;
367 c_oper_req_rec c_oper_req%ROWTYPE;
368
369 -- Cursor to check
370 /*CURSOR c_wo_oper (x_id IN NUMBER) IS
371 SELECT TO_DATE(ACTUAL_START_DATE,'DD-MM-YYYY'), TO_DATE(ACTUAL_END_DATE,'DD-MM-YYYY'),
372 TO_DATE(SCHEDULED_START_DATE,'DD-MM-YYYY'), TO_DATE(SCHEDULED_END_DATE,'DD-MM-YYYY')
373 FROM AHL_WORKORDER_OPERATIONS_V WHERE WORKORDER_OPERATION_ID = x_id;*/
374 -- bug 4092197
375 -- no point in doing a date conversion on a date value
376 -- if the intent was to lose the time portion, then the trunc function can be used
377 -- but do not need to use trunc function
378 -- since the date comparisons between resource start/end dates and operation start/end dates
379 -- are already taken care of in the calling APIs (create and update)
380 -- by assigning the operation time stamp to the resource dates
381 -- of the trunc(resource_date) = trunc(operation_date)
382 CURSOR c_wo_oper (x_id IN NUMBER) IS
383 SELECT ACTUAL_START_DATE, ACTUAL_END_DATE,
384 SCHEDULED_START_DATE, SCHEDULED_END_DATE
385 FROM AHL_WORKORDER_OPERATIONS_V WHERE WORKORDER_OPERATION_ID = x_id;
386
387 BEGIN
388 IF G_DEBUG='Y' THEN
389 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '--Operation ID =' || p_resrc_Require_rec.OPERATION_RESOURCE_ID);
390 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || 'WORKORDER_OPERATION_ID = ' || p_resrc_Require_rec.WORKORDER_OPERATION_ID);
394 IF p_resrc_Require_rec.OPERATION_RESOURCE_ID = Fnd_Api.G_MISS_NUM OR p_resrc_Require_rec.OPERATION_RESOURCE_ID IS NULL THEN
391 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || 'Resource ID = ' || p_resrc_Require_rec.Resource_Id);
392 END IF;
393
395 IF G_DEBUG='Y' THEN
396 Ahl_Debug_Pub.debug('Inside Check_Resrc_Require_Req_Items check while adding');
397 END IF;
398 -- OPERATION_SEQ_NUMBER
399 IF (p_resrc_Require_rec.OPERATION_SEQ_NUMBER IS NULL OR p_resrc_Require_rec.OPERATION_SEQ_NUMBER = Fnd_Api.G_MISS_NUM) THEN
400 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
401 IF G_DEBUG='Y' THEN
402 Ahl_Debug_Pub.debug ( 'OPERATION_SEQUENCE NUMBER PROB');
403 END IF;
404 Fnd_Message.set_name ('AHL', 'AHL_PP_OPER_SEQ_MISSING');
405 Fnd_Msg_Pub.ADD;
406 END IF;
407 x_return_status := Fnd_Api.g_ret_sts_error;
408 RETURN;
409 END IF;
410
411 -- OPERATION_SEQ_NUMBER - Positive
412 IF (p_resrc_Require_rec.OPERATION_SEQ_NUMBER IS NOT NULL AND p_resrc_Require_rec.OPERATION_SEQ_NUMBER <> Fnd_Api.G_MISS_NUM) THEN
413 IF p_resrc_Require_rec.OPERATION_SEQ_NUMBER < 0 THEN
414 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
415 IF G_DEBUG='Y' THEN
416 Ahl_Debug_Pub.debug ( 'ONLY POSITIVE');
417 END IF;
418 Fnd_Message.set_name ('AHL', 'AHL_PP_ONLY_POSITIVE_VALUE');
419 Fnd_Msg_Pub.ADD;
420 END IF;
421 x_return_status := Fnd_Api.g_ret_sts_error;
422 RETURN;
423 END IF;
424 END IF;
425
426 -- RESOURCE_SEQ_NUMBER
427 IF (p_resrc_Require_rec.RESOURCE_SEQ_NUMBER IS NULL OR p_resrc_Require_rec.RESOURCE_SEQ_NUMBER = Fnd_Api.G_MISS_NUM) THEN
428 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
429 Fnd_Message.set_name ('AHL', 'AHL_PP_RESRC_SEQ_MISSING');
430 Fnd_Msg_Pub.ADD;
431 END IF;
432 x_return_status := Fnd_Api.g_ret_sts_error;
433 RETURN;
434 END IF;
435
436 -- RESOURCE_SEQ_NUMBER -- Positive / Multiples of 10
437 IF (p_resrc_Require_rec.RESOURCE_SEQ_NUMBER IS NOT NULL AND p_resrc_Require_rec.RESOURCE_SEQ_NUMBER <> Fnd_Api.G_MISS_NUM) THEN
438 IF p_resrc_Require_rec.RESOURCE_SEQ_NUMBER < 0 THEN
439 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
440 Fnd_Message.set_name ('AHL', 'AHL_PP_ONLY_POSITIVE_VALUE');
441 Fnd_Msg_Pub.ADD;
442 END IF;
443 x_return_status := Fnd_Api.g_ret_sts_error;
444 RETURN;
445 END IF;
446
447 IF (p_resrc_Require_rec.RESOURCE_SEQ_NUMBER mod 10) <> 0 THEN
448 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
449 Fnd_Message.set_name ('AHL', 'AHL_PP_RESRC_SEQ_MULTI_OF_TEN');
450 Fnd_Msg_Pub.ADD;
451 END IF;
452 x_return_status := Fnd_Api.g_ret_sts_error;
453 RETURN;
454 END IF;
455 END IF;
456 END IF;
457
458 IF G_DEBUG='Y' THEN
459 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || 'Check other valid fileds');
460 END IF;
461 -- Schedule seq number validation
462 -- JKJAIN US space FP for ER # 6998882-- start
463 IF (
464 p_resrc_Require_rec.schedule_seq_num IS NOT NULL AND
465 p_resrc_Require_rec.schedule_seq_num <> Fnd_Api.G_MISS_NUM
466 )
467 THEN
468
469 IF (
470 p_resrc_Require_rec.schedule_seq_num < 0 OR
471 TRUNC(p_resrc_Require_rec.schedule_seq_num) <> p_resrc_Require_rec.schedule_seq_num
472 )
473 THEN
474
475 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
476 Fnd_Message.set_name ('AHL', 'AHL_COM_SCHED_SEQ_INV');
477 Fnd_Msg_Pub.ADD;
478 END IF;
479 x_return_status := Fnd_Api.g_ret_sts_error;
480 RETURN;
481
482 END IF;
483
484 END IF;
485 -- JKJAIN US space FP for ER # 6998882 end
486
487 IF g_MODULE_TYPE='JSP' THEN
488
489 IF p_Resrc_Require_Rec.RESOURCE_NAME IS NULL OR p_Resrc_Require_Rec.RESOURCE_NAME = Fnd_Api.G_MISS_CHAR THEN
490 IF G_DEBUG='Y' THEN
491 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '-- RESOURCE_NAME ='|| p_resrc_Require_rec.RESOURCE_NAME);
492 END IF;
493
494 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
495 Fnd_Message.set_name ('AHL', 'AHL_PP_RESOURCE_MISSING');
496 Fnd_Msg_Pub.ADD;
497 END IF;
498 x_return_status := Fnd_Api.g_ret_sts_error;
499 RETURN;
500 END IF;
501 END IF;
502
503 IF G_DEBUG='Y' THEN
504 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '-- Duration =' || p_resrc_Require_rec.Duration);
505 END IF;
506 -- DURATION
507 IF (p_resrc_Require_rec.Duration IS NULL OR p_resrc_Require_rec.Duration = Fnd_Api.G_MISS_NUM) THEN
508 IF G_DEBUG='Y' THEN
509 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '-- Duration =' || p_resrc_Require_rec.Duration);
510 END IF;
511 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
512 Fnd_Message.set_name ('AHL', 'AHL_PP_DURATION_MISSING');
513 Fnd_Msg_Pub.ADD;
514 END IF;
518 -- DURATION - Positive
515 x_return_status := Fnd_Api.g_ret_sts_error;
516 RETURN;
517 END IF;
519 IF (p_resrc_Require_rec.Duration IS NOT NULL AND p_resrc_Require_rec.Duration <> Fnd_Api.G_MISS_NUM) THEN
520 IF p_resrc_Require_rec.Duration < 0 THEN
521 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
522 Fnd_Message.set_name ('AHL', 'AHL_PP_ONLY_POSITIVE_VALUE');
523 Fnd_Msg_Pub.ADD;
524 END IF;
525 x_return_status := Fnd_Api.g_ret_sts_error;
526 RETURN;
527 END IF;
528 END IF;
529
530 -- QUANTITY
531 IF G_DEBUG='Y' THEN
532 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '-- Quantity =' || p_resrc_Require_rec.quantity);
533 END IF;
534
535 IF (p_resrc_Require_rec.QUANTITY IS NULL OR p_resrc_Require_rec.QUANTITY = Fnd_Api.G_MISS_NUM) THEN
536 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '-- Quantity =' || p_resrc_Require_rec.quantity);
537 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
538 Fnd_Message.set_name ('AHL', 'AHL_PP_QUANTITY_MISSING');
539 Fnd_Msg_Pub.ADD;
540 END IF;
541 x_return_status := Fnd_Api.g_ret_sts_error;
542 RETURN;
543 END IF;
544
545 -- QUANTITY - Positive
546 IF (p_resrc_Require_rec.QUANTITY IS NOT NULL AND p_resrc_Require_rec.QUANTITY <> Fnd_Api.G_MISS_NUM) THEN
547 IF p_resrc_Require_rec.QUANTITY < 0 THEN
548 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
549 Fnd_Message.set_name ('AHL', 'AHL_PP_ONLY_POSITIVE_VALUE');
550 Fnd_Msg_Pub.ADD;
551 END IF;
552 x_return_status := Fnd_Api.g_ret_sts_error;
553 RETURN;
554 END IF;
555 END IF;
556
557 -- REQ_START_DATE
558 IF G_DEBUG='Y' THEN
559 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '-- REQ_START_DATE =' || p_resrc_Require_rec.REQ_START_DATE);
560 END IF;
561
562 IF (p_resrc_Require_rec.REQ_START_DATE IS NULL OR p_resrc_Require_rec.REQ_START_DATE = Fnd_Api.G_MISS_DATE) 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_REQUIRE_ST_DT_MISSING');
565 Fnd_Msg_Pub.ADD;
566 END IF;
567 x_return_status := Fnd_Api.g_ret_sts_error;
568 RETURN;
569 END IF;
570
571 -- REQ_END_DATE
572 IF G_DEBUG='Y' THEN
573 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '-- REQ_START_DATE =' || p_resrc_Require_rec.REQ_START_DATE);
574 END IF;
575
576 IF (p_resrc_Require_rec.REQ_END_DATE IS NULL OR p_resrc_Require_rec.REQ_END_DATE = Fnd_Api.G_MISS_DATE) THEN
577 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
578 Fnd_Message.set_name ('AHL', 'AHL_PP_REQUIRE_END_DT_MISSING');
579 Fnd_Msg_Pub.ADD;
580 END IF;
581 x_return_status := Fnd_Api.g_ret_sts_error;
582 RETURN;
583 END IF;
584 --
585 -- Use local vars to reduce amount of typing.
586 IF p_resrc_Require_rec.Req_start_date IS NOT NULL OR p_resrc_Require_rec.Req_start_date <> Fnd_Api.g_miss_date THEN
587 l_Require_start_date := p_resrc_Require_rec.Req_start_date;
588 END IF;
589
590 IF p_resrc_Require_rec.Req_end_date IS NOT NULL OR p_resrc_Require_rec.Req_end_date <> Fnd_Api.g_miss_date THEN
591 l_Require_end_date := p_resrc_Require_rec.Req_end_date;
592 END IF;
593
594 IF G_DEBUG='Y' THEN
595 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '-- Start Date =' || l_Require_start_date);
596 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '-- End Date =' || l_Require_end_date);
597 END IF;
598
599 --
600 -- Validate the active dates.
601 IF l_Require_start_date IS NOT NULL AND l_Require_end_date IS NOT NULL THEN
602 IF l_Require_start_date > l_Require_end_date THEN
603 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
604 Fnd_Message.set_name ('AHL', 'AHL_PP_REQ_FROMDT_GTR_TODT');
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;
611
612 ------------------- Start Uncommented on 27 Jan 2003 as bug#2771573 -----------------
613 IF g_MODULE_TYPE='JSP' THEN
614 IF p_resrc_Require_rec.OPERATION_RESOURCE_ID = Fnd_Api.G_MISS_NUM OR p_resrc_Require_rec.OPERATION_RESOURCE_ID IS NULL THEN
615 OPEN c_wo_oper(p_resrc_Require_rec.WORKORDER_OPERATION_ID);
616 FETCH c_wo_oper INTO l_eff_st_date, l_eff_end_date, l_sch_st_date, l_sch_end_date;
617 IF c_wo_oper%NOTFOUND THEN
618 CLOSE c_wo_oper;
619 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '--If condition--');
620 Fnd_Message.SET_NAME('AHL','AHL_PP_WORKORDER_NOT_EXISTS');
621 Fnd_Msg_Pub.ADD;
622 ELSE
623 CLOSE c_wo_oper;
624 END IF;
625 ELSE
626 OPEN c_oper_req(p_resrc_Require_rec.OPERATION_RESOURCE_ID);
627 FETCH c_oper_req INTO c_oper_req_rec;
628 IF c_oper_req%NOTFOUND THEN
629 CLOSE c_oper_req;
630 Fnd_Message.SET_NAME('AHL','AHL_PP_WORKORDER_OPER_NOT_EXISTS');
631 Fnd_Msg_Pub.ADD;
632 ELSE
633 CLOSE c_oper_req;
637 CLOSE c_wo_oper;
634 OPEN c_wo_oper(c_oper_req_rec.WORKORDER_OPERATION_ID);
635 FETCH c_wo_oper INTO l_eff_st_date, l_eff_end_date, l_sch_st_date, l_sch_end_date;
636 IF c_wo_oper%NOTFOUND THEN
638 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '--Else If condition--');
639 Fnd_Message.SET_NAME('AHL','AHL_PP_WORKORDER_NOT_EXISTS');
640 Fnd_Msg_Pub.ADD;
641 ELSE
642 CLOSE c_wo_oper;
643 END IF;
644 END IF;
645 END IF;
646
647
648
649 IF G_DEBUG='Y' THEN
650 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '-- Actual Start Date =' || l_eff_st_date);
651 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '-- Actual End Date =' || l_eff_end_date);
652 Ahl_Debug_Pub.debug('**************************************************************************');
653 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '-- Scheduled Start Date =' || l_sch_st_date);
654 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '-- Scheduled End Date =' || l_sch_end_date);
655 Ahl_Debug_Pub.debug('**************************************************************************');
656 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '-- Require Start Date =' || l_Require_start_date);
657 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '-- Require End Date =' || l_Require_end_date);
658 Ahl_Debug_Pub.debug('**************************************************************************');
659 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '-- Operation ID =' || p_resrc_Require_rec.OPERATION_RESOURCE_ID);
660 END IF;
661 -- bug 4092197
662 --l_Require_start_date := TO_DATE(l_Require_start_date,'DD-MON-YYYY');
663 --l_Require_end_date := TO_DATE(l_Require_end_date,'DD-MON-YYYY');
664 IF l_Require_start_date IS NOT NULL THEN
665 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items AAA' || '-- Start Date =' || l_Require_start_date);
666 IF l_eff_st_date IS NOT NULL THEN
667 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items AAA' || '-- Actual Start Date =' || l_eff_st_date);
668 IF l_Require_start_date < l_eff_st_date THEN
669 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
670 Fnd_Message.set_name ('AHL', 'AHL_PP_REQ_STDT_LESS_ACT_STDT');
671 Fnd_Msg_Pub.ADD;
672 END IF;
673 x_return_status := Fnd_Api.g_ret_sts_error;
674 RETURN;
675 END IF;
676 ELSIF l_sch_st_date IS NOT NULL THEN
677 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items AAA' || '-- Scheduled Start Date =' || l_sch_st_date);
678 IF l_Require_start_date < l_sch_st_date THEN
679 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
680 Fnd_Message.set_name ('AHL', 'AHL_PP_REQ_STDT_LESS_SCH_STDT');
681 Fnd_Msg_Pub.ADD;
682 END IF;
683 x_return_status := Fnd_Api.g_ret_sts_error;
684 RETURN;
685 END IF;
686 END IF;
687
688 IF l_eff_end_date IS NOT NULL THEN
689 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items AAA' || '-- Actual Start Date =' || l_eff_st_date);
690 IF l_Require_start_date > l_eff_end_date THEN
691 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
692 Fnd_Message.set_name ('AHL', 'AHL_PP_REQ_STDT_MORE_ACT_EDDT');
693 Fnd_Msg_Pub.ADD;
694 END IF;
695 x_return_status := Fnd_Api.g_ret_sts_error;
696 RETURN;
697 END IF;
698 ELSIF l_sch_end_date IS NOT NULL THEN
699 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items AAA' || '-- Scheduled End Date =' || l_sch_end_date);
700 IF l_Require_start_date > l_sch_end_date THEN
701 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
702 Fnd_Message.set_name ('AHL', 'AHL_PP_REQ_STDT_MORE_SCH_EDDT');
703 Fnd_Msg_Pub.ADD;
704 END IF;
705 x_return_status := Fnd_Api.g_ret_sts_error;
706 RETURN;
707 END IF;
708 END IF;
709 END IF;
710
711 IF l_Require_end_date IS NOT NULL THEN
712 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items BBB' || '-- End Date =' || l_Require_end_date);
713 IF l_eff_end_date IS NOT NULL THEN
714 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items BBB' || '-- Actual End Date =' || l_eff_end_date);
715 IF l_Require_end_date > l_eff_end_date THEN
716 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
717 Fnd_Message.set_name ('AHL', 'AHL_PP_REQ_EDDT_MORE_ACT_EDDT');
718 Fnd_Msg_Pub.ADD;
719 END IF;
720 x_return_status := Fnd_Api.g_ret_sts_error;
721 RETURN;
722 END IF;
723 ELSIF l_sch_end_date IS NOT NULL THEN
724 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items BBB' || '-- Scheduled End Date =' || l_sch_end_date);
725 IF l_Require_end_date > l_sch_end_date THEN
726 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
727 Fnd_Message.set_name ('AHL', 'AHL_PP_REQ_EDDT_MORE_SCH_EDDT');
728 Fnd_Msg_Pub.ADD;
729 END IF;
730 x_return_status := Fnd_Api.g_ret_sts_error;
731 RETURN;
732 END IF;
733 END IF;
734
735 IF l_eff_st_date IS NOT NULL THEN
736 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items BBB' || '-- Actual Start Date =' || l_eff_st_date);
737 IF l_Require_end_date < l_eff_st_date THEN
741 END IF;
738 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
739 Fnd_Message.set_name ('AHL', 'AHL_PP_REQ_EDDT_LESS_ACT_STDT');
740 Fnd_Msg_Pub.ADD;
742 x_return_status := Fnd_Api.g_ret_sts_error;
743 RETURN;
744 END IF;
745 ELSIF l_sch_st_date IS NOT NULL THEN
746 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items BBB' || '-- Scheduled Start Date =' || l_sch_st_date);
747 IF l_Require_end_date < l_sch_st_date THEN
748 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
749 Fnd_Message.set_name ('AHL', 'AHL_PP_REQ_EDDT_LESS_SCH_STDT');
750 Fnd_Msg_Pub.ADD;
751 END IF;
752 x_return_status := Fnd_Api.g_ret_sts_error;
753 RETURN;
754 END IF;
755 END IF;
756 END IF;
757
758 END IF; -- CHECK FOR g_MODULE_TYPE='JSP' THEN
759
760 END Check_Resrc_Require_Req_Items;
761
762 -- Check_Resrc_Require_UK_Items
763 PROCEDURE Check_Resrc_Require_UK_Items (
764 p_resrc_Require_rec IN Resrc_Require_Rec_Type,
765 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
766 x_return_status OUT NOCOPY VARCHAR2
767 )
768 IS
769 l_valid_flag VARCHAR2(1);
770 l_ctr NUMBER:=0;
771 BEGIN
772 x_return_status := Fnd_Api.g_ret_sts_success;
773
774 --
775 -- For when ID is passed in, we need to check if this ID is unique.
776 IF G_DEBUG='Y' THEN
777 Ahl_Debug_Pub.enable_debug;
778 END IF;
779 IF G_DEBUG='Y' THEN
780 Ahl_Debug_Pub.debug( ' RESOURCE SEQ NUMBER -->'||p_resrc_Require_rec.RESOURCE_SEQ_NUMBER);
781 Ahl_Debug_Pub.debug( ' OPERATION_RESOURCE_ID -->' ||p_resrc_Require_rec.OPERATION_RESOURCE_ID);
782 Ahl_Debug_Pub.debug( ' WORKORDER_OPERATION_ID -->' ||p_resrc_Require_rec.WORKORDER_OPERATION_ID);
783 END IF;
784
785 IF p_validation_mode = Jtf_Plsql_Api.g_create AND (p_resrc_Require_rec.OPERATION_RESOURCE_ID IS NULL OR p_resrc_Require_rec.OPERATION_RESOURCE_ID = FND_API.g_miss_num) THEN
786 Ahl_Debug_Pub.debug( 'For create l_valid_flag -->' || l_valid_flag);
787 Ahl_Debug_Pub.debug( 'QUERY -->' || 'RESOURCE_SEQUENCE_NUM = ' || p_resrc_Require_rec.RESOURCE_SEQ_NUMBER ||
788 ' AND WORKORDER_OPERATION_ID = ' || p_resrc_Require_rec.WORKORDER_OPERATION_ID);
789
790 l_valid_flag := Ahl_Utility_Pvt.check_uniqueness (
791 'AHL_OPERATION_RESOURCES',
792 'RESOURCE_SEQUENCE_NUM = ' || p_resrc_Require_rec.RESOURCE_SEQ_NUMBER ||
793 ' AND WORKORDER_OPERATION_ID = ' || p_resrc_Require_rec.WORKORDER_OPERATION_ID
794 );
795 ELSE
796 Ahl_Debug_Pub.debug( 'QUERY -->' || 'RESOURCE_SEQUENCE_NUM = ' || p_resrc_Require_rec.RESOURCE_SEQ_NUMBER ||
797
798 ' AND WORKORDER_OPERATION_ID = ' || p_resrc_Require_rec.WORKORDER_OPERATION_ID ||
799 ' AND OPERATION_RESOURCE_ID <> ' || p_resrc_Require_rec.OPERATION_RESOURCE_ID);
800
801 l_valid_flag := Ahl_Utility_Pvt.check_uniqueness (
802 'AHL_OPERATION_RESOURCES',
803 'RESOURCE_SEQUENCE_NUM = ' || p_resrc_Require_rec.RESOURCE_SEQ_NUMBER ||
804 ' AND WORKORDER_OPERATION_ID = ' || p_resrc_Require_rec.WORKORDER_OPERATION_ID ||
805 ' AND OPERATION_RESOURCE_ID <> ' || p_resrc_Require_rec.OPERATION_RESOURCE_ID
806 );
807
808 Ahl_Debug_Pub.debug( 'l_valid_flag cccc -->' || l_valid_flag);
809 Ahl_Debug_Pub.debug( 'QUERY -->' || 'RESOURCE_SEQUENCE_NUM = ' || p_resrc_Require_rec.RESOURCE_SEQ_NUMBER ||
810 ' AND WORKORDER_OPERATION_ID = ' || p_resrc_Require_rec.WORKORDER_OPERATION_ID ||
811 ' AND OPERATION_RESOURCE_ID <> ' || p_resrc_Require_rec.OPERATION_RESOURCE_ID);
812 END IF;
813
814 IF l_valid_flag = Fnd_Api.g_false
815 THEN
816
817 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
818 Fnd_Message.set_name ('AHL', 'AHL_PP_REQ_NOT_UNIQUE');
819 Fnd_Msg_Pub.ADD;
820 END IF;
821 x_return_status := Fnd_Api.g_ret_sts_error;
822 RETURN;
823 END IF;
824
825 END Check_Resrc_Require_UK_Items;
826
827 ---------------------------------------------------------------------
828 -- PROCEDURE
829 -- Check_Resrc_Require_Items
830 --
831 ---------------------------------------------------------------------
832 PROCEDURE Check_Resrc_Require_Items (
833 p_resrc_Require_rec IN Resrc_Require_Rec_Type,
834 p_validation_mode IN VARCHAR2 := Jtf_Plsql_Api.g_create,
835 x_return_status OUT NOCOPY VARCHAR2
836 )
837 IS
838 BEGIN
839 --
840 -- Validate Required items.
841 IF G_DEBUG='Y' THEN
842 Ahl_Debug_Pub.debug('BEFORE ..... Check_Resrc_Require_Req_Items');
843 END IF;
844 Check_Resrc_Require_Req_Items (
845 p_resrc_Require_rec => p_resrc_Require_rec,
846 x_return_status => x_return_status
847 );
848
849 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
850 RETURN;
851 END IF;
852 --
853 -- Validate uniqueness.
854 Check_Resrc_Require_UK_Items (
855 p_resrc_Require_rec => p_resrc_Require_rec,
856 p_validation_mode => p_validation_mode,
857 x_return_status => x_return_status
858 );
859
860 IF x_return_status <> Fnd_Api.g_ret_sts_success THEN
861 RETURN;
862 END IF;
863
864 END Check_Resrc_Require_Items;
865
869 --
866 --------------------------------------------------------------------
867 -- PROCEDURE
868 -- Validate_Resrc_Require
870 --------------------------------------------------------------------
871 PROCEDURE Validate_Resrc_Require (
872 p_api_version IN NUMBER,
873 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
874 p_commit IN VARCHAR2 := Fnd_Api.g_false,
875 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
876 p_resrc_Require_rec IN Resrc_Require_Rec_Type,
877
878 x_return_status OUT NOCOPY VARCHAR2,
879 x_msg_count OUT NOCOPY NUMBER,
880 x_msg_data OUT NOCOPY VARCHAR2
881 )
882 IS
883 L_API_VERSION CONSTANT NUMBER := 1.0;
884 L_API_NAME CONSTANT VARCHAR2(30) := 'Validate_Resrc_Require';
885 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
886 l_return_status VARCHAR2(1);
887
888 -- Added to fix bug# 6512803.
889 CURSOR get_res_type(p_resource_id IN NUMBER) IS
890 SELECT resource_type
891 FROM BOM_RESOURCES
892 WHERE resource_id = p_resource_id;
893
894 l_resource_type_code BOM_RESOURCES.resource_type%TYPE;
895
896 BEGIN
897 --------------------- initialize -----------------------
898 -- Check if API is called in debug mode. If yes, enable debug.
899 IF G_DEBUG='Y' THEN
900 Ahl_Debug_Pub.enable_debug;
901 END IF;
902 -- Debug info.
903 IF G_DEBUG='Y' THEN
904 Ahl_Debug_Pub.debug( l_full_name ||':Start');
905 END IF;
906
907 IF Fnd_Api.to_boolean (p_init_msg_list) THEN
908 Fnd_Msg_Pub.initialize;
909 END IF;
910
911 IF NOT Fnd_Api.compatible_api_call (
912 l_api_version,
913 p_api_version,
914 l_api_name,
915 G_PKG_NAME
916 ) THEN
917 RAISE Fnd_Api.g_exc_unexpected_error;
918 END IF;
919 x_return_status := Fnd_Api.g_ret_sts_success;
920
921 ---------------------- validate ------------------------
922 IF G_DEBUG='Y' THEN
923 Ahl_Debug_Pub.debug( l_full_name ||':Check items');
924 END IF;
925
926 IF p_validation_level >= Jtf_Plsql_Api.g_valid_level_item THEN
927 Check_Resrc_Require_Items (
928 p_resrc_Require_rec => p_resrc_Require_rec,
929 p_validation_mode => Jtf_Plsql_Api.g_create,
930 x_return_status => l_return_status
931 );
932
933 IF l_return_status = Fnd_Api.g_ret_sts_unexp_error THEN
934 RAISE Fnd_Api.g_exc_unexpected_error;
935 ELSIF l_return_status = Fnd_Api.g_ret_sts_error THEN
936 RAISE Fnd_Api.g_exc_error;
937 END IF;
938 END IF;
939 --FP for Bug 6625880. AMSRINIV. Doing away with below validation as misc resources can be scheduled.
940 -- Fix for bug# 6512803. Validate scheduled type with resource type.
941 /*
942 IF (p_resrc_Require_rec.scheduled_type_code IS NOT NULL AND
943 p_resrc_Require_rec.scheduled_type_code <> FND_API.G_MISS_NUM) THEN
944 IF (p_resrc_Require_rec.resource_type_code IS NULL AND
945 p_resrc_Require_rec.resource_type_code = FND_API.G_MISS_NUM) THEN
946 OPEN get_res_type(p_resrc_Require_rec.resource_id);
947 FETCH get_res_type INTO l_resource_type_code;
948 CLOSE get_res_type;
949 ELSE
950 l_resource_type_code := p_resrc_Require_rec.resource_type_code;
951 END IF;
952 IF (l_resource_type_code NOT IN (1,2)) AND p_resrc_Require_rec.scheduled_type_code = 1 THEN
953 FND_MESSAGE.set_name( 'AHL', 'AHL_PRD_INVALID_SCHEDULE_TYPE' );
954 Fnd_Msg_Pub.ADD;
955 RAISE Fnd_Api.g_exc_error;
956 END IF;
957 END IF;
958 */
959 -------------------- finish --------------------------
960 Fnd_Msg_Pub.count_and_get (
961 p_encoded => Fnd_Api.g_false,
962 p_count => x_msg_count,
963 p_data => x_msg_data
964 );
965 IF G_DEBUG='Y' THEN
966 Ahl_Debug_Pub.debug( l_full_name ||':End');
967 END IF;
968
969 -- Check if API is called in debug mode. If yes, disable debug.
970 IF G_DEBUG='Y' THEN
971 Ahl_Debug_Pub.disable_debug;
972 END IF;
973
974 EXCEPTION
975 WHEN Fnd_Api.g_exc_error THEN
976 x_return_status := Fnd_Api.g_ret_sts_error;
977 Fnd_Msg_Pub.count_and_get (
978 p_encoded => Fnd_Api.g_false,
979 p_count => x_msg_count,
980 p_data => x_msg_data
981 );
982 WHEN Fnd_Api.g_exc_unexpected_error THEN
983 x_return_status := Fnd_Api.g_ret_sts_unexp_error ;
984 Fnd_Msg_Pub.count_and_get (
985 p_encoded => Fnd_Api.g_false,
986 p_count => x_msg_count,
987 p_data => x_msg_data
988 );
989 WHEN OTHERS THEN
990 x_return_status := Fnd_Api.g_ret_sts_unexp_error;
991 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_unexp_error)
992 THEN
993 Fnd_Msg_Pub.add_exc_msg (G_PKG_NAME, l_api_name);
994 END IF;
995
996 Fnd_Msg_Pub.count_and_get (
997 p_encoded => Fnd_Api.g_false,
998 p_count => x_msg_count,
999 p_data => x_msg_data
1000 );
1001 END Validate_Resrc_Require;
1002
1006 -- Type : Private
1003 --------------------------------------------------------------------------------------------------
1004 -- Start of Comments --
1005 -- Procedure name : Create_Resrc_Require
1007 -- Pre-reqs :
1008 -- Parameters :
1009 --
1010 -- Standard IN Parameters :
1011 -- p_api_version IN NUMBER Required
1012 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
1013 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
1014 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
1015 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
1016 -- p_module_type IN VARCHAR2 Default NULL.
1017 --
1018 -- Standard OUT Parameters :
1019 -- x_return_status OUT VARCHAR2 Required
1020 -- x_msg_count OUT NUMBER Required
1021 -- x_msg_data OUT VARCHAR2 Required
1022 --
1023 -- Create Resource Requirement Parameters:
1024 -- p_x_resrc_Require_tbl IN OUT NOCOPY AHL_PP_RESRC_Require_PVT.Resrc_Require_Tbl_Type,
1025 -- Contains Resource Reqirement information to create
1026 --
1027 -- Version :
1028 -- Initial Version 1.0
1029 --
1030 -- End of Comments.
1031
1032 PROCEDURE Create_Resrc_Require (
1033 p_api_version IN NUMBER,
1034 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
1035 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
1036 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
1037 p_module_type IN VARCHAR2 := NULL,
1038 p_interface_flag IN VARCHAR2,
1039 p_x_resrc_Require_Tbl IN OUT NOCOPY AHL_PP_RESRC_Require_PVT.Resrc_Require_Tbl_Type,
1040 x_return_status OUT NOCOPY VARCHAR2,
1041 x_msg_count OUT NOCOPY NUMBER,
1042 x_msg_data OUT NOCOPY VARCHAR2
1043 )
1044 IS
1045 -- Check to see Operation Resource Id exists
1046 CURSOR Sch_id_exists (x_id IN NUMBER) IS
1047 SELECT 1 FROM dual
1048 WHERE EXISTS (SELECT 1
1049 FROM AHL_OPERATION_RESOURCES
1050 WHERE OPERATION_RESOURCE_ID = x_id);
1051
1052 -- To find workorder_operation_id from ahl_workorder_operation_v view
1053 CURSOR c_wo_oper (x_id IN NUMBER, x_seq IN NUMBER) IS
1054 SELECT WORKORDER_OPERATION_ID FROM
1055 AHL_WORKORDER_OPERATIONS
1056 --AHL_WORKORDER_OPERATIONS_V
1057 WHERE WORKORDER_ID = x_id AND OPERATION_SEQUENCE_NUM = x_seq;
1058
1059 -- To find the resource sequence nubmer from ahl_operation_resources
1060 CURSOR c_resrc_seq (x_id IN NUMBER, x_oper_seq IN NUMBER, x_resrc_seq IN NUMBER) IS
1061 SELECT COUNT(*) FROM
1062 AHL_WORKORDER_OPERATIONS AWOV, AHL_OPERATION_RESOURCES AOR
1063 WHERE AWOV.WORKORDER_OPERATION_ID = AOR.WORKORDER_OPERATION_ID AND
1064 AWOV.WORKORDER_ID = x_id AND AWOV.OPERATION_SEQUENCE_NUM = x_oper_seq AND
1065 AOR.RESOURCE_SEQUENCE_NUM = x_resrc_seq;
1066
1067 -- To find the resource sequence nubmer from ahl_operation_resources
1068 CURSOR c_workorder (x_id IN NUMBER) IS
1069 SELECT * FROM AHL_WORKORDERS
1070 WHERE WORKORDER_ID = x_id;
1071 c_workorder_rec c_workorder%ROWTYPE;
1072
1073 -- To find the resource sequence nubmer from ahl_operation_resources
1074 CURSOR c_resources (x_id IN NUMBER) IS
1075 SELECT DEPARTMENT_ID FROM
1076 BOM_DEPARTMENT_RESOURCES
1077 WHERE RESOURCE_ID = x_id;
1078
1079 -- To find the resource sequence nubmer from ahl_operation_resources
1080 CURSOR c_wo_dept (x_id IN NUMBER) IS
1081 SELECT --V.DEPARTMENT_ID, -- department should be from wip_operations
1082 V.ORGANIZATION_ID,
1083 WORKORDER_NAME, WIP_ENTITY_ID FROM
1084 AHL_VISITS_B V, AHL_VISIT_TASKS_B T, AHL_WORKORDERS W
1085 WHERE W.VISIT_TASK_ID = T.VISIT_TASK_ID AND T.VISIT_ID = V.VISIT_ID
1086 AND W.VISIT_TASK_ID = x_id;
1087
1088 -- To find the UOM_CODE from MTL_UNITS_OF_MEASURE table
1089 CURSOR c_UOM (x_name IN VARCHAR2) IS
1090 SELECT UOM_CODE
1091 FROM MTL_UNITS_OF_MEASURE
1092 WHERE UNIT_OF_MEASURE = x_name;
1093 -- Get uom from bom resources
1094 CURSOR c_uom_code (x_id IN NUMBER)
1095 IS
1096 SELECT unit_of_measure
1097 FROM bom_resources
1098 WHERE resource_id = x_id;
1099
1100 --Modified by srini to fix timestamp
1101 --Check to get the timestamp for operation
1102 /*CURSOR wip_operation_dates (c_workorder_id IN NUMBER,
1103 c_op_seq_num IN NUMBER)
1104 IS
1105 SELECT first_unit_start_date,
1106 last_unit_completion_date
1107 FROM wip_operations a, ahl_workorders b
1108 WHERE a.wip_entity_id = b.wip_entity_id
1109 AND workorder_id = c_workorder_id
1110 AND operation_seq_num = c_op_seq_num;*/
1111 --fix for bug number 6211089
1112 CURSOR wip_operation_dates (c_workorder_operation_id IN NUMBER)
1113 IS
1114 SELECT first_unit_start_date,
1115 last_unit_completion_date
1116 FROM wip_operations a, ahl_workorders b,ahl_workorder_operations c
1117 WHERE a.wip_entity_id = b.wip_entity_id
1118 AND b.workorder_id = c.workorder_id
1122
1119 AND a.operation_seq_num = c.OPERATION_SEQUENCE_NUM
1120 AND c.workorder_operation_id = c_workorder_operation_id;
1121
1123 -- Added resource_type for bug# 6512803.
1124 CURSOR c_get_std_rate_flag(p_resource_id NUMBER)
1125 IS
1126 SELECT
1127 STANDARD_RATE_FLAG, resource_type
1128 FROM
1129 BOM_RESOURCES
1130 WHERE
1131 resource_id = p_resource_id;
1132
1133 -- Schedule seq number validation
1134 -- JKJAIN US space FP for ER # 6998882-- start
1135 CURSOR get_def_sched_seq(c_wo_oper_id IN NUMBER)
1136 IS
1137 SELECT
1138 MIN(wipor.schedule_seq_num)
1139 FROM
1140 ahl_workorder_operations awop,
1141 ahl_workorders awo,
1142 wip_operation_resources wipor
1143 WHERE
1144 awop.operation_sequence_num = wipor.operation_seq_num
1145 AND awo.wip_entity_id = wipor.wip_entity_id
1146 AND awop.workorder_id = awo.workorder_id
1147 AND awop.workorder_operation_id = c_wo_oper_id;
1148
1149 l_def_sched_seq NUMBER;
1150 -- JKJAIN US space FP for ER # 6998882-- end
1151
1152 l_api_name CONSTANT VARCHAR2(30) := 'Create_Resrc_Require';
1153 l_api_version CONSTANT NUMBER := 1.0;
1154 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
1155
1156 l_wo_operation_id NUMBER;
1157 l_dummy NUMBER;
1158 l_requirement_id NUMBER;
1159 l_serial_id NUMBER;
1160 l_resrc_seq_num NUMBER;
1161 l_object_version_number NUMBER;
1162 l_wo_operation_txn_id NUMBER;
1163 l_process_status NUMBER;
1164 l_employee_id NUMBER;
1165 l_msg_count NUMBER;
1166 l_resrc_dept_id NUMBER;
1167 l_dept_id NUMBER;
1168 l_wo_dept NUMBER;
1169 l_count NUMBER;
1170 j NUMBER;
1171 l_std_rate_flag VARCHAR2(30);
1172
1173 l_return_status VARCHAR2(1);
1174 l_msg_data VARCHAR2(2000);
1175 l_error_message VARCHAR2(120);
1176 l_employee_name VARCHAR2(240);
1177 l_wo_organization_id NUMBER;
1178 l_department_id NUMBER;
1179 l_wip_entity_id NUMBER;
1180 --
1181 l_Resrc_Require_Rec Resrc_Require_Rec_Type;
1182 l_Resrc_Require_Tbl Resrc_Require_Tbl_Type;
1183 l_op_start_date DATE;
1184 l_op_end_date DATE;
1185 --
1186 l_workorder_name VARCHAR2(80);
1187 l_default VARCHAR2(10);
1188
1189 -- Added for bug# 6444617.
1190 l_resource_type bom_resources.resource_type%TYPE;
1191
1192 BEGIN
1193 --------------------Initialize ----------------------------------
1194 -- Standard Start of API savepoint
1195 SAVEPOINT Create_Resrc_Require;
1196 -- Check if API is called in debug mode. If yes, enable debug.
1197 IF G_DEBUG='Y' THEN
1198 AHL_DEBUG_PUB.enable_debug;
1199 END IF;
1200 IF G_DEBUG='Y' THEN
1201 Ahl_Debug_Pub.debug ('start p_interface_flag:'||p_interface_flag);
1202 Ahl_Debug_Pub.debug ('p_x_resrc_Require_tbl.COUNT:'||p_x_resrc_Require_tbl.COUNT);
1203 END IF;
1204 -- Debug info.
1205 -- Dbms_Output.Enable(50000);
1206 IF G_DEBUG='Y' THEN
1207 Ahl_Debug_Pub.debug( 'Enter AHL_PP_RESRC_Require_PVT. Create_Resrc_Require +PPResrc_Require_Pvt+');
1208 END IF;
1209 G_MODULE_TYPE:=P_MODULE_TYPE;
1210
1211 -- Standard call to check for call compatibility.
1212 IF FND_API.to_boolean(p_init_msg_list)
1213 THEN
1214 FND_MSG_PUB.initialize;
1215 END IF;
1216
1217 -- Initialize API return status to success
1218 x_return_status := FND_API.G_RET_STS_SUCCESS;
1219
1220 -- Initialize message list if p_init_msg_list is set to TRUE.
1221 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
1222 p_api_version,
1223 l_api_name,G_PKG_NAME)
1224 THEN
1225 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1226 END IF;
1227 --------------------Start of API Body-----------------------------------
1228 IF p_x_resrc_Require_tbl.COUNT > 0 THEN
1229 FOR i IN p_x_resrc_Require_tbl.FIRST..p_x_resrc_Require_tbl.LAST
1230 LOOP
1231 l_Resrc_Require_Rec := p_x_resrc_Require_tbl(i);
1232 --------------------Value OR ID conversion---------------------------
1233 --Start API Body
1234 IF p_module_type = 'JSP'
1235 THEN
1236 l_Resrc_Require_Rec.resource_id := NULL;
1237 END IF;
1238 IF G_DEBUG='Y' THEN
1239 Ahl_Debug_Pub.debug ( ' Workorder Id = ' || l_Resrc_Require_Rec.workorder_id);
1240 Ahl_Debug_Pub.debug ( ' Operation Sequence = ' || l_Resrc_Require_Rec.operation_seq_number);
1241 Ahl_Debug_Pub.debug ( ' Resource Sequence = ' || l_Resrc_Require_Rec.resource_seq_number);
1242 Ahl_Debug_Pub.debug ( 'UOM NAME: ' || l_Resrc_Require_Rec.uom_name);
1243 Ahl_Debug_Pub.debug ( 'UOM CODE: ' || l_Resrc_Require_Rec.uom_code);
1244 Ahl_Debug_Pub.debug ( 'OPER SDATE: ' || l_Resrc_Require_Rec.oper_start_date);
1245 Ahl_Debug_Pub.debug ( 'OPER EDATE: ' || l_Resrc_Require_Rec.oper_end_date);
1246 Ahl_Debug_Pub.debug ( 'REQSDATE: ' || l_Resrc_Require_Rec.req_start_date);
1247 Ahl_Debug_Pub.debug ( 'REQEDATE: ' || l_Resrc_Require_Rec.req_end_date);
1248
1249 END IF;
1250
1254 OPEN c_wo_oper(l_Resrc_Require_Rec.workorder_id, l_Resrc_Require_Rec.operation_seq_number);
1251 IF l_Resrc_Require_Rec.workorder_id IS NOT NULL THEN
1252 IF l_Resrc_Require_Rec.operation_seq_number IS NOT NULL AND l_Resrc_Require_Rec.operation_seq_number <> FND_API.G_MISS_NUM THEN
1253
1255 FETCH c_wo_oper INTO l_wo_operation_id;
1256 IF c_wo_oper%NOTFOUND THEN
1257 CLOSE c_wo_oper;
1258 Ahl_Debug_Pub.debug('NO SEQ');
1259 Fnd_Message.SET_NAME('AHL','AHL_PP_OPER_SEQ_NOT_EXISTS');
1260 Fnd_Msg_Pub.ADD;
1261 ELSE
1262 CLOSE c_wo_oper;
1263 l_Resrc_Require_Rec.workorder_operation_id := l_wo_operation_id;
1264
1265 IF l_Resrc_Require_Rec.resource_seq_number IS NOT NULL AND
1266 l_Resrc_Require_Rec.resource_seq_number <> FND_API.G_MISS_NUM THEN
1267 OPEN c_resrc_seq(l_Resrc_Require_Rec.workorder_id, l_Resrc_Require_Rec.operation_seq_number,l_Resrc_Require_Rec.resource_seq_number);
1268 FETCH c_resrc_seq INTO l_count;
1269 CLOSE c_resrc_seq;
1270
1271 IF l_count > 0 THEN
1272 IF G_DEBUG='Y' THEN
1273 Ahl_Debug_Pub.debug('UNIQ 1');
1274 END IF;
1275 Fnd_Message.SET_NAME('AHL','AHL_PP_RESRC_SEQ_NOT_UNIQUE');
1276 Fnd_Msg_Pub.ADD;
1277 END IF;
1278 END IF; -- Check resrc sequence number
1279 END IF; -- Check c_wo_oper%NOTFOUND
1280
1281 END IF; -- Check of Oper sequence number
1282 ELSE
1283 Fnd_Message.SET_NAME('AHL','AHL_PP_JOB_NOT_EXISTS');
1284 Fnd_Msg_Pub.ADD;
1285 END IF; -- Check of work order id
1286
1287 --rroy
1288 -- ACL Changes
1289 IF p_module_type = 'JSP' THEN
1290
1291 OPEN c_workorder(l_Resrc_Require_Rec.workorder_id);
1292 FETCH c_workorder INTO c_workorder_rec;
1293 IF c_workorder%NOTFOUND THEN
1294 Fnd_Message.SET_NAME('AHL','AHL_PP_WORKORDER_NOT_EXISTS');
1295 Fnd_Msg_Pub.ADD;
1296 END IF;
1297 CLOSE c_workorder;
1298 END IF;
1299
1300 l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked(
1301 p_workorder_id => l_resrc_require_rec.workorder_id,
1302 p_ue_id => NULL,
1303 p_visit_id => NULL,
1304 p_item_instance_id => NULL);
1305 IF l_return_status = FND_API.G_TRUE THEN
1306 FND_MESSAGE.Set_Name('AHL', 'AHL_PP_CRT_RESREQ_UNTLCKD');
1307 FND_MSG_PUB.ADD;
1308 RAISE FND_API.G_EXC_ERROR;
1309 END IF;
1310 --rroy
1311 -- ACL Changes
1312
1313 --Required to check the operation start dates and resource start and end date are same
1314 /*OPEN wip_operation_dates(l_Resrc_Require_Rec.workorder_id,
1315 l_Resrc_Require_Rec.operation_seq_number);*/
1316 -- fix for bug number 6211089
1317 OPEN wip_operation_dates(l_Resrc_Require_Rec.WORKORDER_OPERATION_ID);
1318 FETCH wip_operation_dates INTO l_op_start_date,l_op_end_date;
1319 CLOSE wip_operation_dates;
1320 --Validation is required to include operation timestamp for Requested start date
1321 -- requested end date
1322 -- Bug # 6728602 -- start
1323 /*IF (TRUNC(l_Resrc_Require_Rec.req_start_date) = TRUNC(l_op_end_date)
1324 AND TRUNC(l_Resrc_Require_Rec.req_end_date) = TRUNC(l_op_end_date )) THEN
1325
1326 l_Resrc_Require_Rec.req_start_date := l_op_end_date;
1327 l_Resrc_Require_Rec.req_end_date := l_op_end_date;
1328
1329 ELSIF (TRUNC(l_Resrc_Require_Rec.req_start_date) = TRUNC(l_op_start_date )
1330 AND TRUNC(l_Resrc_Require_Rec.req_end_date) = TRUNC(l_op_start_date )) THEN
1331
1332 l_Resrc_Require_Rec.req_start_date := l_op_start_date;
1333 l_Resrc_Require_Rec.req_end_date := l_op_start_date;
1334
1335 ELSIF (TRUNC(l_Resrc_Require_Rec.req_start_date) = TRUNC(l_op_start_date )
1336 AND TRUNC(l_Resrc_Require_Rec.req_end_date) = TRUNC(l_op_end_date )) THEN
1337
1338 l_Resrc_Require_Rec.req_start_date := l_op_start_date;
1339 l_Resrc_Require_Rec.req_end_date := l_op_end_date;
1340
1341 ELSIF (TRUNC(l_Resrc_Require_Rec.req_start_date) = TRUNC(l_op_start_date )
1342 AND TRUNC(l_Resrc_Require_Rec.req_end_date) <> TRUNC(l_op_start_date )) THEN
1343
1344 l_Resrc_Require_Rec.req_start_date := l_op_start_date;
1345
1346 ELSIF (TRUNC(l_Resrc_Require_Rec.req_start_date) <> TRUNC(l_op_end_date )
1347 AND TRUNC(l_Resrc_Require_Rec.req_end_date) = TRUNC(l_op_end_date )) THEN
1348
1349 l_Resrc_Require_Rec.req_end_date := l_op_end_date;
1350
1351 END IF; */
1352 IF(l_Resrc_Require_Rec.req_start_date < l_op_start_date OR l_Resrc_Require_Rec.req_start_date > l_op_end_date)THEN
1353 l_Resrc_Require_Rec.req_start_date := l_op_start_date;
1354 END IF;
1355 IF(l_Resrc_Require_Rec.req_end_date > l_op_end_date OR l_Resrc_Require_Rec.req_end_date < l_op_start_date)THEN
1356 l_Resrc_Require_Rec.req_end_date := l_op_end_date;
1357 END IF;
1358 -- Bug # 6728602 -- end
1359
1360 IF G_DEBUG='Y' THEN
1364 Ahl_Debug_Pub.debug ( ' Requested Start Date = ' || TO_CHAR(l_Resrc_Require_Rec.req_start_date, 'DD-MM-YYYY HH24:MI:SS'));
1361 Ahl_Debug_Pub.debug ( ' Workorder Operation Id = ' || l_wo_operation_id);
1362 Ahl_Debug_Pub.debug ( ' Resource Type Name = ' || l_Resrc_Require_Rec.resource_type_name);
1363 Ahl_Debug_Pub.debug ( ' Resource Type Code = ' || l_Resrc_Require_Rec.resource_type_code);
1365 Ahl_Debug_Pub.debug ( ' Requested End Date = ' || TO_CHAR(l_Resrc_Require_Rec.req_end_date, 'DD-MM-YYYY HH24:MI:SS'));
1366 END IF;
1367 --
1368 -- For Resource Type
1369 IF ( l_Resrc_Require_Rec.resource_type_name IS NOT NULL AND
1370 l_Resrc_Require_Rec.resource_type_name <> Fnd_Api.G_MISS_CHAR )
1371 THEN
1372 Check_Lookup_Name_Or_Id (
1373 p_lookup_type => 'BOM_RESOURCE_TYPE',
1374 p_lookup_code => NULL,
1375 p_meaning => l_Resrc_Require_Rec.resource_type_name,
1376 p_check_id_flag => 'Y',
1377 x_lookup_code => l_Resrc_Require_Rec.resource_type_code,
1378 x_return_status => l_return_status);
1379
1380 IF NVL(l_return_status, 'X') <> 'S'
1381 THEN
1382 Fnd_Message.SET_NAME('AHL','AHL_PP_RESRC_TYPE_NOT_EXISTS');
1383 Fnd_Msg_Pub.ADD;
1384 RAISE Fnd_Api.G_EXC_ERROR;
1385 END IF;
1386 END IF;
1387 IF G_DEBUG='Y' THEN
1388 Ahl_Debug_Pub.debug ( ' Resource Type Code = ' || l_Resrc_Require_Rec.resource_type_code);
1389 Ahl_Debug_Pub.debug ( ' Resource Name = ' || l_Resrc_Require_Rec.RESOURCE_NAME);
1390 Ahl_Debug_Pub.debug ( ' Resource ID = ' || l_Resrc_Require_Rec.Resource_Id);
1391
1392 Ahl_Debug_Pub.debug ( l_full_name || '*******************BEFORE RESOURCE NAME CHECK');
1393 END IF;
1394 -- For Resource
1395 IF l_Resrc_Require_Rec.RESOURCE_NAME IS NOT NULL AND
1396 l_Resrc_Require_Rec.RESOURCE_NAME <> Fnd_Api.G_MISS_CHAR
1397 THEN
1398 Check_Resource_Name_Or_Id
1399 (p_Resource_Id => l_Resrc_Require_Rec.Resource_Id,
1400 p_Resource_code => l_Resrc_Require_Rec.Resource_Name,
1401 p_workorder_id => l_Resrc_Require_Rec.workorder_id,
1402
1403 x_Resource_Id => l_Resrc_Require_Rec.Resource_Id,
1404 x_return_status => l_return_status,
1405 x_error_msg_code => l_msg_data
1406 );
1407
1408 IF NVL(l_return_status, 'X') <> 'S'
1409 THEN
1410 Fnd_Message.SET_NAME('AHL','AHL_PP_RESOURCE_NOT_EXISTS');
1411 Fnd_Msg_Pub.ADD;
1412 RAISE Fnd_Api.G_EXC_ERROR;
1413 END IF;
1414 END IF;
1415 IF G_DEBUG='Y' THEN
1416 Ahl_Debug_Pub.debug ( l_full_name || '*******************AFTER RESOURCE NAME CHECK');
1417 END IF;
1418
1419 IF p_module_type = 'JSP'
1420 THEN
1421 /*-- Get department id -- commented out as dept is retrieved along with
1422 -- operation start dates.
1423 OPEN c_wo_dept (c_workorder_rec.visit_task_id);
1424 FETCH c_wo_dept INTO --l_department_id,
1425 l_wo_organization_id,
1426 l_workorder_name, l_wip_entity_id;
1427 CLOSE c_wo_dept; */
1428 --
1429 OPEN c_resources(l_Resrc_Require_Rec.Resource_Id);
1430 FETCH c_resources INTO l_resrc_dept_id;
1431 IF c_resources%NOTFOUND THEN
1432 Fnd_Message.SET_NAME('AHL','AHL_PP_RESRC_DEPT_NOT_EXISTS');
1433 Fnd_Msg_Pub.ADD;
1434 END IF;
1435 CLOSE c_resources;
1436
1437 -- check resource id dept matches the operation dept.
1438 IF (l_resrc_dept_id <> l_department_id) THEN
1439 Fnd_Message.SET_NAME('AHL','AHL_PP_RESRC_DEPT_NOT_EXISTS');
1440 Fnd_Msg_Pub.ADD;
1441 END IF;
1442
1443 --Convert Uom code
1444 OPEN c_uom_code(l_Resrc_Require_Rec.Resource_Id);
1445 FETCH c_uom_code INTO l_Resrc_Require_Rec.UOM_CODE;
1446 CLOSE c_uom_code;
1447 --
1448 p_x_resrc_Require_tbl(i).uom_code := l_Resrc_Require_Rec.UOM_CODE;
1449 -- For Units of Measure
1450 IF l_Resrc_Require_Rec.UOM_NAME IS NOT NULL AND
1451 l_Resrc_Require_Rec.UOM_NAME <> Fnd_Api.G_MISS_CHAR
1452 THEN
1453 OPEN c_UOM(l_Resrc_Require_Rec.UOM_NAME);
1454 FETCH c_UOM INTO l_Resrc_Require_Rec.UOM_CODE;
1455 IF c_UOM%NOTFOUND THEN
1456 CLOSE c_UOM;
1457 Fnd_Message.SET_NAME('AHL','AHL_PP_UOM_NOT_EXISTS');
1458 Fnd_Msg_Pub.ADD;
1459 ELSE
1460 CLOSE c_UOM;
1461 END IF;
1462 END IF;
1463
1464 -- For AutoCharge Type
1465 IF ( l_Resrc_Require_Rec.CHARGE_TYPE_NAME IS NOT NULL AND
1466 l_Resrc_Require_Rec.CHARGE_TYPE_NAME <> Fnd_Api.G_MISS_CHAR )
1467 THEN
1468 Check_Lookup_Name_Or_Id (
1469 p_lookup_type => 'BOM_AUTOCHARGE_TYPE',
1470 p_lookup_code => NULL,
1471 p_meaning => l_Resrc_Require_Rec.CHARGE_TYPE_NAME,
1475
1472 p_check_id_flag => 'Y',
1473 x_lookup_code => l_Resrc_Require_Rec.CHARGE_TYPE_CODE,
1474 x_return_status => l_return_status);
1476 IF NVL(l_return_status, 'X') <> 'S'
1477 THEN
1478 Fnd_Message.SET_NAME('AHL','AHL_PP_CHARGE_TYPE_NOT_EXISTS');
1479 Fnd_Msg_Pub.ADD;
1480 RAISE Fnd_Api.G_EXC_ERROR;
1481 END IF;
1482 END IF;
1483 IF G_DEBUG='Y' THEN
1484 Ahl_Debug_Pub.debug ( l_full_name || ' AutoCharge Code = ' || l_Resrc_Require_Rec.CHARGE_TYPE_CODE);
1485 Ahl_Debug_Pub.debug ( l_full_name || ' COST BASIS NAME = ' || l_Resrc_Require_Rec.COST_BASIS_NAME);
1486 Ahl_Debug_Pub.debug ( l_full_name || ' COST BASIS CODE = ' || l_Resrc_Require_Rec.COST_BASIS_CODE);
1487 END IF;
1488 -- For Cost Basis
1489 IF ( l_Resrc_Require_Rec.COST_BASIS_NAME IS NOT NULL AND
1490 l_Resrc_Require_Rec.COST_BASIS_NAME <> Fnd_Api.G_MISS_CHAR )
1491 THEN
1492 Check_Lookup_Name_Or_Id (
1493 p_lookup_type => 'CST_BASIS',
1494 p_lookup_code => NULL,
1495 p_meaning => l_Resrc_Require_Rec.COST_BASIS_NAME,
1496 p_check_id_flag => 'Y',
1497 x_lookup_code => l_Resrc_Require_Rec.COST_BASIS_CODE,
1498 x_return_status => l_return_status);
1499
1500 IF NVL(l_return_status, 'X') <> 'S'
1501 THEN
1502 Fnd_Message.SET_NAME('AHL','AHL_PP_COST_BASIS_NOT_EXISTS');
1503 Fnd_Msg_Pub.ADD;
1504 RAISE Fnd_Api.G_EXC_ERROR;
1505 END IF;
1506 END IF;
1507 IF G_DEBUG='Y' THEN
1508 Ahl_Debug_Pub.debug ( l_full_name || ' COST BASIS CODE = ' || l_Resrc_Require_Rec.COST_BASIS_CODE);
1509 Ahl_Debug_Pub.debug ( l_full_name || ' SCHEDULED TYPE NAME = ' || l_Resrc_Require_Rec.SCHEDULED_TYPE_NAME);
1510 Ahl_Debug_Pub.debug ( l_full_name || ' SCHEDULED TYPE CODE = ' || l_Resrc_Require_Rec.SCHEDULED_TYPE_CODE);
1511 END IF;
1512 -- For Scheduled Type
1513 IF ( l_Resrc_Require_Rec.SCHEDULED_TYPE_NAME IS NOT NULL AND
1514 l_Resrc_Require_Rec.SCHEDULED_TYPE_NAME <> Fnd_Api.G_MISS_CHAR )
1515 THEN
1516 Check_Lookup_Name_Or_Id (
1517 p_lookup_type => 'BOM_RESOURCE_SCHEDULE_TYPE',
1518 p_lookup_code => NULL,
1519 p_meaning => l_Resrc_Require_Rec.SCHEDULED_TYPE_NAME,
1520 p_check_id_flag => 'Y',
1521 x_lookup_code => l_Resrc_Require_Rec.SCHEDULED_TYPE_CODE,
1522 x_return_status => l_return_status);
1523
1524 IF NVL(l_return_status, 'X') <> 'S'
1525 THEN
1526 Fnd_Message.SET_NAME('AHL','AHL_PP_SCHED_TYPE_NOT_EXISTS');
1527 Fnd_Msg_Pub.ADD;
1528 RAISE Fnd_Api.G_EXC_ERROR;
1529 END IF;
1530 END IF;
1531 IF G_DEBUG='Y' THEN
1532 Ahl_Debug_Pub.debug ( l_full_name || ' SCHEDULED TYPE CODE = ' || l_Resrc_Require_Rec.SCHEDULED_TYPE_CODE);
1533 Ahl_Debug_Pub.debug ( l_full_name || ' STANDARD RATE FLAG = ' || l_Resrc_Require_Rec.STD_RATE_FLAG_CODE);
1534 END IF;
1535 -- To find meaning for fnd_lookups code
1536 IF (l_Resrc_Require_Rec.STD_RATE_FLAG_CODE IS NOT NULL AND
1537 l_Resrc_Require_Rec.STD_RATE_FLAG_CODE <> Fnd_Api.G_MISS_NUM) THEN
1538 SELECT meaning
1539 INTO l_std_rate_flag
1540 FROM MFG_LOOKUPS
1541 WHERE lookup_code = l_Resrc_Require_Rec.STD_RATE_FLAG_CODE
1542 AND LOOKUP_TYPE = 'BOM_NO_YES';
1543 END IF;
1544
1545 END IF;
1546
1547 -------------------------------- Validate -----------------------------------------
1548 IF G_DEBUG='Y' THEN
1549 Ahl_Debug_Pub.debug ( l_full_name || ' ******Before calling Validate_Resrc_Require****');
1550 END IF;
1551
1552 -- Schedule seq number validation
1553 -- JKJAIN US space FP for ER # 6998882-- start
1554 IF (
1555 l_Resrc_Require_Rec.schedule_seq_num IS NULL
1556 )
1557 THEN
1558
1559 OPEN get_def_sched_seq(l_Resrc_Require_Rec.workorder_operation_id);
1560 FETCH get_def_sched_seq INTO l_def_sched_seq;
1561 CLOSE get_def_sched_seq;
1562
1563 l_Resrc_Require_Rec.schedule_seq_num := NVL(l_def_sched_seq, 10);
1564
1565 END IF;
1566 -- JKJAIN US space FP for ER # 6998882 -- end
1567
1568 IF p_interface_flag is null or p_interface_flag <> 'N' THEN
1569
1570 Validate_Resrc_Require (
1571 p_api_version => l_api_version,
1572 p_init_msg_list => p_init_msg_list,
1573 p_commit => p_commit,
1574 p_validation_level => p_validation_level,
1575 p_resrc_Require_rec => l_Resrc_Require_Rec,
1576 x_return_status => l_return_status,
1577 x_msg_count => x_msg_count,
1578 x_msg_data => x_msg_data
1579 );
1580 END IF;
1581 IF G_DEBUG='Y' THEN
1582 Ahl_Debug_Pub.debug ( l_full_name || ' ******After calling Validate_Resrc_Require****');
1583 END IF;
1584 --Standard check to count messages
1585 l_msg_count := Fnd_Msg_Pub.count_msg;
1586
1590 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1587 IF l_msg_count > 0 THEN
1588 x_msg_count := l_msg_count;
1589 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1591 END IF;
1592
1593 --
1594 IF l_Resrc_Require_Rec.Operation_Resource_Id = FND_API.G_MISS_NUM OR l_Resrc_Require_Rec.Operation_Resource_Id IS NULL
1595 THEN
1596 IF G_DEBUG='Y' THEN
1597 Ahl_Debug_Pub.debug ( l_full_name || ' ******INSIDE DEFAULT VALUES****');
1598 END IF;
1599
1600 -- These conditions are Required for optional fields
1601 IF (l_Resrc_Require_Rec.CHARGE_TYPE_CODE IS NULL OR
1602 l_Resrc_Require_Rec.CHARGE_TYPE_CODE = Fnd_Api.G_MISS_NUM) THEN
1603 l_Resrc_Require_Rec.charge_type_code := 2;
1604 END IF;
1605
1606 IF (l_Resrc_Require_Rec.COST_BASIS_CODE IS NULL OR
1607 l_Resrc_Require_Rec.COST_BASIS_CODE = Fnd_Api.G_MISS_NUM) THEN
1608 l_Resrc_Require_Rec.cost_basis_code := 1;
1609 END IF;
1610
1611 IF (l_Resrc_Require_Rec.SCHEDULED_TYPE_CODE IS NULL OR
1612 l_Resrc_Require_Rec.SCHEDULED_TYPE_CODE = Fnd_Api.G_MISS_NUM) THEN
1613 l_Resrc_Require_Rec.scheduled_type_code := 1;
1614 END IF;
1615
1616 -- As part of fix for bug# 6512803, merged validation of STD_RATE_FLAG_CODE and
1617 -- SCHEDULED_TYPE_CODE under one IF block.
1618 IF (l_Resrc_Require_Rec.STD_RATE_FLAG_CODE IS NULL OR
1619 l_Resrc_Require_Rec.STD_RATE_FLAG_CODE = Fnd_Api.G_MISS_NUM) OR
1620 (l_Resrc_Require_Rec.SCHEDULED_TYPE_CODE IS NULL OR
1621 l_Resrc_Require_Rec.SCHEDULED_TYPE_CODE = Fnd_Api.G_MISS_NUM) THEN
1622
1623 -- Balaji modified the code for Bug # 5951435-- Begin
1624
1625 OPEN c_get_std_rate_flag(l_Resrc_Require_Rec.Resource_Id);
1626 FETCH c_get_std_rate_flag INTO l_Resrc_Require_Rec.std_rate_flag_code, l_resource_type;
1627 CLOSE c_get_std_rate_flag;
1628 --l_Resrc_Require_Rec.std_rate_flag_code := 1;
1629
1630 -- Balaji modified the code for Bug # 5951435-- End
1631 -- Added to fix bug# 6512803.
1632 IF (l_Resrc_Require_Rec.SCHEDULED_TYPE_CODE IS NULL OR
1633 l_Resrc_Require_Rec.SCHEDULED_TYPE_CODE = Fnd_Api.G_MISS_NUM) THEN
1634 IF (l_resource_type IN (1,2)) THEN
1635 l_Resrc_Require_Rec.scheduled_type_code := 1;
1636 ELSE
1637 l_Resrc_Require_Rec.scheduled_type_code := 2;
1638 END IF;
1639 END IF;
1640
1641 END IF;
1642
1643 -- Last Updated Date
1644 IF l_Resrc_Require_Rec.last_update_login = FND_API.G_MISS_NUM
1645 THEN
1646 l_Resrc_Require_Rec.last_update_login := NULL;
1647 ELSE
1648 l_Resrc_Require_Rec.last_update_login := l_Resrc_Require_Rec.last_update_login;
1649 END IF;
1650 -- Attribute Category
1651 IF l_Resrc_Require_Rec.attribute_category = FND_API.G_MISS_CHAR
1652 THEN
1653 l_Resrc_Require_Rec.attribute_category := NULL;
1654 ELSE
1655 l_Resrc_Require_Rec.attribute_category := l_Resrc_Require_Rec.attribute_category;
1656 END IF;
1657 -- Attribute1
1658 IF l_Resrc_Require_Rec.attribute1 = FND_API.G_MISS_CHAR
1659 THEN
1660 l_Resrc_Require_Rec.attribute1 := NULL;
1661 ELSE
1662 l_Resrc_Require_Rec.attribute1 := l_Resrc_Require_Rec.attribute1;
1663 END IF;
1664 -- Attribute2
1665 IF l_Resrc_Require_Rec.attribute2 = FND_API.G_MISS_CHAR
1666 THEN
1667 l_Resrc_Require_Rec.attribute2 := NULL;
1668 ELSE
1669 l_Resrc_Require_Rec.attribute2 := l_Resrc_Require_Rec.attribute2;
1670 END IF;
1671 -- Attribute3
1672 IF l_Resrc_Require_Rec.attribute3 = FND_API.G_MISS_CHAR
1673 THEN
1674 l_Resrc_Require_Rec.attribute3 := NULL;
1675 ELSE
1676 l_Resrc_Require_Rec.attribute3 := l_Resrc_Require_Rec.attribute3;
1677 END IF;
1678 -- Attribute4
1679 IF l_Resrc_Require_Rec.attribute4 = FND_API.G_MISS_CHAR
1680 THEN
1681 l_Resrc_Require_Rec.attribute4 := NULL;
1682 ELSE
1683 l_Resrc_Require_Rec.attribute4 := l_Resrc_Require_Rec.attribute4;
1684 END IF;
1685 -- Attribute5
1686 IF l_Resrc_Require_Rec.attribute5 = FND_API.G_MISS_CHAR
1687 THEN
1688 l_Resrc_Require_Rec.attribute5 := NULL;
1689 ELSE
1690 l_Resrc_Require_Rec.attribute5 := l_Resrc_Require_Rec.attribute5;
1691 END IF;
1692 -- Attribute6
1693 IF l_Resrc_Require_Rec.attribute6 = FND_API.G_MISS_CHAR
1694 THEN
1695 l_Resrc_Require_Rec.attribute6 := NULL;
1696 ELSE
1697 l_Resrc_Require_Rec.attribute6 := l_Resrc_Require_Rec.attribute6;
1698 END IF;
1699 -- Attribute7
1700 IF l_Resrc_Require_Rec.attribute7 = FND_API.G_MISS_CHAR
1701 THEN
1702 l_Resrc_Require_Rec.attribute7 := NULL;
1703 ELSE
1704 l_Resrc_Require_Rec.attribute7 := l_Resrc_Require_Rec.attribute7;
1705 END IF;
1706 -- Attribute8
1707 IF l_Resrc_Require_Rec.attribute8 = FND_API.G_MISS_CHAR
1708 THEN
1712 END IF;
1709 l_Resrc_Require_Rec.attribute8 := NULL;
1710 ELSE
1711 l_Resrc_Require_Rec.attribute8 := l_Resrc_Require_Rec.attribute8;
1713 -- Attribute9
1714 IF l_Resrc_Require_Rec.attribute9 = FND_API.G_MISS_CHAR
1715 THEN
1716 l_Resrc_Require_Rec.attribute9 := NULL;
1717 ELSE
1718 l_Resrc_Require_Rec.attribute9 := l_Resrc_Require_Rec.attribute9;
1719 END IF;
1720 -- Attribute10
1721 IF l_Resrc_Require_Rec.attribute10 = FND_API.G_MISS_CHAR
1722 THEN
1723 l_Resrc_Require_Rec.attribute10 := NULL;
1724 ELSE
1725 l_Resrc_Require_Rec.attribute10 := l_Resrc_Require_Rec.attribute10;
1726 END IF;
1727 -- Attribute11
1728 IF l_Resrc_Require_Rec.attribute11 = FND_API.G_MISS_CHAR
1729 THEN
1730 l_Resrc_Require_Rec.attribute11 := NULL;
1731 ELSE
1732 l_Resrc_Require_Rec.attribute11 := l_Resrc_Require_Rec.attribute11;
1733 END IF;
1734 -- Attribute12
1735 IF l_Resrc_Require_Rec.attribute12 = FND_API.G_MISS_CHAR
1736 THEN
1737 l_Resrc_Require_Rec.attribute12 := NULL;
1738 ELSE
1739 l_Resrc_Require_Rec.attribute12 := l_Resrc_Require_Rec.attribute12;
1740 END IF;
1741 -- Attribute13
1742 IF l_Resrc_Require_Rec.attribute13 = FND_API.G_MISS_CHAR
1743 THEN
1744 l_Resrc_Require_Rec.attribute13 := NULL;
1745 ELSE
1746 l_Resrc_Require_Rec.attribute13 := l_Resrc_Require_Rec.attribute13;
1747 END IF;
1748 -- Attribute14
1749 IF l_Resrc_Require_Rec.attribute14 = FND_API.G_MISS_CHAR
1750 THEN
1751 l_Resrc_Require_Rec.attribute14 := NULL;
1752 ELSE
1753 l_Resrc_Require_Rec.attribute14 := l_Resrc_Require_Rec.attribute14;
1754 END IF;
1755 -- Attribute15
1756 IF l_Resrc_Require_Rec.attribute15 = FND_API.G_MISS_CHAR
1757 THEN
1758 l_Resrc_Require_Rec.attribute15 := NULL;
1759 ELSE
1760 l_Resrc_Require_Rec.attribute15 := l_Resrc_Require_Rec.attribute15;
1761 END IF;
1762
1763 p_x_resrc_Require_tbl(i) := l_Resrc_Require_Rec;
1764 END IF;
1765 IF G_DEBUG='Y' THEN
1766 Ahl_Debug_Pub.debug ( l_full_name || ' ******OUTSIDE DEFAULT VALUES****');
1767 END IF;
1768
1769 END LOOP;
1770 END IF;
1771 IF G_DEBUG='Y' THEN
1772 Ahl_Debug_Pub.debug ('p_interface_flag'||p_interface_flag);
1773 END IF;
1774 IF nvl(p_interface_flag,'Y')= 'Y' THEN
1775 -- CALL Load_WIP_Jobs API
1776 -- If not sucess then not allowed to insert in our entity
1777 OPEN c_workorder(l_Resrc_Require_Rec.workorder_id);
1778 FETCH c_workorder INTO c_workorder_rec;
1779 CLOSE c_workorder;
1780 --Get organization id
1781 OPEN c_wo_dept (c_workorder_rec.visit_task_id);
1782 FETCH c_wo_dept INTO --l_department_id,
1783 l_wo_organization_id,l_workorder_name,
1784 l_wip_entity_id;
1785 CLOSE c_wo_dept;
1786 --
1787 j:=1;
1788 FOR i IN p_x_resrc_Require_tbl.FIRST..p_x_resrc_Require_tbl.LAST
1789 LOOP
1790 IF G_DEBUG='Y' THEN
1791 Ahl_Debug_Pub.debug ( l_full_name || 'CALL FOR WIP JOBS');
1792 Ahl_Debug_Pub.debug ('p_x_resrc_Require_tbl(i).OPERATION_SEQ_NUMBER' ||p_x_resrc_Require_tbl(i).OPERATION_SEQ_NUMBER );
1793 Ahl_Debug_Pub.debug ('p_x_resrc_Require_tbl(i).RESOURCE_SEQ_NUMBER' ||p_x_resrc_Require_tbl(i).RESOURCE_SEQ_NUMBER );
1794 Ahl_Debug_Pub.debug ('p_x_resrc_Require_tbl(i).RESOURCE_ID' ||p_x_resrc_Require_tbl(i).RESOURCE_ID );
1795 Ahl_Debug_Pub.debug ('p_x_resrc_Require_tbl(i).QUANTITY' ||p_x_resrc_Require_tbl(i).QUANTITY );
1796 END IF;
1797 l_Resrc_Require_Tbl(j).organization_id := l_wo_organization_id;
1798 l_Resrc_Require_Tbl(j).wip_entity_id := l_wip_entity_id;
1799 l_Resrc_Require_Tbl(j).job_number := l_workorder_name;
1800 l_Resrc_Require_Tbl(j).workorder_id := p_x_resrc_Require_tbl(i).WORKORDER_ID;
1801 l_Resrc_Require_Tbl(j).operation_seq_number := p_x_resrc_Require_tbl(i).OPERATION_SEQ_NUMBER;
1802 l_Resrc_Require_Tbl(j).uom_code := p_x_resrc_Require_tbl(i).UOM_CODE;
1803 l_Resrc_Require_Tbl(j).resource_seq_number := p_x_resrc_Require_tbl(i).RESOURCE_SEQ_NUMBER;
1804 -- JKJAIN US space FP for ER # 6998882-- start
1805 l_Resrc_Require_Tbl(j).schedule_seq_num := p_x_resrc_Require_tbl(i).schedule_seq_num;
1806 --JKJAIN US space FP for ER # 6998882 -- end
1807 l_Resrc_Require_Tbl(j).resource_id := p_x_resrc_Require_tbl(i).RESOURCE_ID;
1808 l_Resrc_Require_Tbl(j).duration := p_x_resrc_Require_tbl(i).DURATION;
1809 l_Resrc_Require_Tbl(j).req_start_date := p_x_resrc_Require_tbl(i).REQ_START_DATE;
1810 l_Resrc_Require_Tbl(j).req_end_date := p_x_resrc_Require_tbl(i).REQ_END_DATE;
1811 l_Resrc_Require_Tbl(j).quantity := p_x_resrc_Require_tbl(i).QUANTITY;
1812 l_Resrc_Require_Tbl(j).applied_num := p_x_resrc_Require_tbl(i).QUANTITY;
1813 l_Resrc_Require_Tbl(j).open_num := p_x_resrc_Require_tbl(i).QUANTITY;
1814 l_Resrc_Require_Tbl(j).cost_basis_code := p_x_resrc_Require_tbl(i).COST_BASIS_CODE;
1815 l_Resrc_Require_Tbl(j).charge_type_code := p_x_resrc_Require_tbl(i).CHARGE_TYPE_CODE;
1819
1816 l_Resrc_Require_Tbl(j).std_rate_flag_code := p_x_resrc_Require_tbl(i).STD_RATE_FLAG_CODE;
1817 l_Resrc_Require_Tbl(j).scheduled_type_code := p_x_resrc_Require_tbl(i).SCHEDULED_TYPE_CODE;
1818 l_Resrc_Require_Tbl(j).operation_flag := 'C';
1820 IF G_DEBUG='Y' THEN
1821 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).WIP_ENTITY_ID: ' ||l_Resrc_Require_Tbl(j).WIP_ENTITY_ID );
1822 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).JOB_NUMBER: ' ||l_Resrc_Require_Tbl(j).JOB_NUMBER );
1823 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).UOM_CODE: ' ||l_Resrc_Require_Tbl(j).UOM_CODE );
1824 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).WORKORDER_ID: ' ||l_Resrc_Require_Tbl(j).WORKORDER_ID );
1825 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).ORGANIZATION_ID: ' ||l_Resrc_Require_Tbl(j).ORGANIZATION_ID );
1826 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).OPERATION_SEQ_NUM: ' ||l_Resrc_Require_Tbl(j).OPERATION_SEQ_NUMBER );
1827 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).RESOURCE_SEQ_NUM: ' ||l_Resrc_Require_Tbl(j).RESOURCE_SEQ_NUMBER );
1828 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).RESOURCE_ID: ' ||l_Resrc_Require_Tbl(j).RESOURCE_ID );
1829 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).QUANTITY: ' ||l_Resrc_Require_Tbl(j).QUANTITY );
1830 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).DURATION: ' ||l_Resrc_Require_Tbl(j).DURATION );
1831 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).REQ_START_DATE: ' ||l_Resrc_Require_Tbl(j).REQ_START_DATE );
1832 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).REQ_END_DATE: ' ||l_Resrc_Require_Tbl(j).REQ_END_DATE );
1833 END IF;
1834
1835 j := j + 1;
1836 END LOOP;
1837
1838 -- Call AHL_WIP_JOB_PVT.load_wip_job API If the status is success then process
1839 IF P_MODULE_TYPE='JSP'
1840 THEN
1841
1842
1843 AHL_EAM_JOB_PVT.process_resource_req
1844 (
1845 p_api_version => p_api_version,
1846 p_init_msg_list => p_init_msg_list,
1847 p_commit => p_commit,
1848 p_validation_level => p_validation_level,
1849 p_default => l_default,
1850 p_module_type => p_module_type,
1851 x_return_status => l_return_status,
1852 x_msg_count => l_msg_count,
1853 x_msg_data => l_msg_data,
1854 p_resource_req_tbl => l_Resrc_Require_Tbl);
1855
1856 -- possible that EAM api returns error status but no error messages.
1857 -- not chking return status causes data corruption - see bug# 7632674
1858 -- Raise errors if exceptions occur
1859 IF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
1860 IF G_DEBUG='Y' THEN
1861 Ahl_Debug_Pub.debug ('Error returned from AHL_EAM_JOB_PVT.process_resource_req:'||l_return_status);
1862 END IF;
1863 RAISE FND_API.G_EXC_ERROR;
1864 ELSIF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
1865 IF G_DEBUG='Y' THEN
1866 Ahl_Debug_Pub.debug ('Error returned from AHL_EAM_JOB_PVT.process_resource_req:'||l_return_status);
1867 END IF;
1868 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1869 END IF;
1870 END IF;
1871
1872 END IF; -- interface flag
1873
1874 IF G_DEBUG='Y' THEN
1875 Ahl_Debug_Pub.debug ('after wip load p_interface_flag'||l_return_status);
1876 Ahl_Debug_Pub.debug ('after wip load p_x_resrc_Require_tbl.COUNT'||p_x_resrc_Require_tbl.COUNT);
1877
1878 END IF;
1879
1880 --Standard check to count messages
1881 l_msg_count := Fnd_Msg_Pub.count_msg;
1882 IF l_msg_count > 0 THEN
1883 X_msg_count := l_msg_count;
1884 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1885 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
1886 END IF;
1887
1888 -- IF l_return_status ='S' THEN
1889 --
1890 IF p_x_resrc_Require_tbl.COUNT > 0 THEN
1891 FOR i IN p_x_resrc_Require_tbl.FIRST..p_x_resrc_Require_tbl.LAST
1892 LOOP
1893 IF G_DEBUG='Y' THEN
1894 Ahl_Debug_Pub.debug ( l_full_name || ' ******INSIDE REQUIRE LOOP****');
1895 END IF;
1896
1897 l_Resrc_Require_Rec := p_x_resrc_Require_tbl(i);
1898 IF l_Resrc_Require_Rec.Operation_Resource_id = FND_API.G_MISS_NUM OR l_Resrc_Require_Rec.Operation_Resource_Id IS NULL
1899 THEN
1900 IF G_DEBUG='Y' THEN
1901 Ahl_Debug_Pub.debug ( l_full_name || ' ******INSIDE REQUIRE ID IF CASE****');
1902 END IF;
1903
1904 --
1905 -- Get Sequence Number for Resource Requirement ID
1906 SELECT AHL_OPERATION_RESOURCES_S.NEXTVAL
1907 INTO l_Resrc_Require_Rec.Operation_Resource_id
1908 FROM DUAL;
1909
1910 --Check for Record Exists
1911 OPEN Sch_id_exists(l_Requirement_id);
1912 FETCH Sch_id_exists INTO l_dummy;
1913 CLOSE Sch_id_exists;
1914 --
1915 IF l_dummy IS NOT NULL THEN
1916 Fnd_Message.SET_NAME('AHL','AHL_PP_SEQUENCE_NO_EXISTS');
1917 Fnd_Msg_Pub.ADD;
1918 RAISE FND_API.G_EXC_ERROR;
1919 END IF;
1920 IF G_DEBUG='Y' THEN
1921 Ahl_Debug_Pub.debug ( l_full_name || ' ******Before calling Insert_Row****');
1922 Ahl_Debug_Pub.debug ( 'l_Resrc_Require_Rec.workorder_operation_id'||l_Resrc_Require_Rec.workorder_operation_id);
1923 Ahl_Debug_Pub.debug ( 'pworkorder_operation_id'||p_x_resrc_Require_tbl(i).workorder_operation_id);
1924 END IF;
1928 X_OPERATION_RESOURCE_ID => l_Resrc_Require_Rec.Operation_Resource_id,
1925
1926 -- Create Record in schedule Resources
1927 Insert_Row (
1929 X_OBJECT_VERSION_NUMBER => 1,
1930 X_LAST_UPDATE_DATE => SYSDATE,
1931 X_LAST_UPDATED_BY => fnd_global.user_id,
1932 X_CREATION_DATE => SYSDATE,
1933 X_CREATED_BY => fnd_global.user_id,
1934 X_LAST_UPDATE_LOGIN => fnd_global.login_id,
1935 X_RESOURCE_ID => l_Resrc_Require_Rec.RESOURCE_ID,
1936 X_WORKORDER_OPERATION_ID => l_Resrc_Require_Rec.workorder_operation_id,
1937 X_RESOURCE_SEQ_NUMBER => l_Resrc_Require_Rec.RESOURCE_SEQ_NUMBER,
1938 X_UOM_CODE => l_Resrc_Require_Rec.UOM_CODE,
1939 X_QUANTITY => l_Resrc_Require_Rec.QUANTITY,
1940 X_DURATION => l_Resrc_Require_Rec.DURATION,
1941 X_SCHEDULED_START_DATE => NVL(l_Resrc_Require_Rec.REQ_START_DATE,l_Resrc_Require_Rec.OPER_START_DATE),
1942 X_SCHEDULED_END_DATE => NVL(l_Resrc_Require_Rec.REQ_END_DATE,l_Resrc_Require_Rec.OPER_END_DATE),
1943 X_ATTRIBUTE_CATEGORY => l_Resrc_Require_Rec.attribute_category,
1944 X_ATTRIBUTE1 => l_Resrc_Require_Rec.attribute1,
1945 X_ATTRIBUTE2 => l_Resrc_Require_Rec.attribute2,
1946 X_ATTRIBUTE3 => l_Resrc_Require_Rec.attribute3,
1947 X_ATTRIBUTE4 => l_Resrc_Require_Rec.attribute4,
1948 X_ATTRIBUTE5 => l_Resrc_Require_Rec.attribute5,
1949 X_ATTRIBUTE6 => l_Resrc_Require_Rec.attribute6,
1950 X_ATTRIBUTE7 => l_Resrc_Require_Rec.attribute7,
1951 X_ATTRIBUTE8 => l_Resrc_Require_Rec.attribute8,
1952 X_ATTRIBUTE9 => l_Resrc_Require_Rec.attribute9,
1953 X_ATTRIBUTE10 => l_Resrc_Require_Rec.attribute10,
1954 X_ATTRIBUTE11 => l_Resrc_Require_Rec.attribute11,
1955 X_ATTRIBUTE12 => l_Resrc_Require_Rec.attribute12,
1956 X_ATTRIBUTE13 => l_Resrc_Require_Rec.attribute13,
1957 X_ATTRIBUTE14 => l_Resrc_Require_Rec.attribute14,
1958 X_ATTRIBUTE15 => l_Resrc_Require_Rec.attribute15
1959 );
1960 IF G_DEBUG='Y' THEN
1961 Ahl_Debug_Pub.debug ( l_full_name || ' ******After calling Insert_Row****');
1962 END IF;
1963
1964 p_x_resrc_Require_tbl(i) := l_Resrc_Require_Rec;
1965 END IF;
1966
1967 SELECT AHL_WO_OPERATIONS_TXNS_S.NEXTVAL
1968 INTO l_wo_operation_txn_id
1969 FROM DUAL;
1970 IF G_DEBUG='Y' THEN
1971 AHL_DEBUG_PUB.debug( 'before calling log record l_wo_operation_txn_id:'||l_wo_operation_txn_id);
1972 END IF;
1973
1974 -- Create Record in transactions table
1975 AHL_PP_MATERIALS_PVT.Log_Transaction_Record
1976 ( p_wo_operation_txn_id => l_wo_operation_txn_id,
1977 p_object_version_number => 1,
1978 p_last_update_date => sysdate,
1979 p_last_updated_by => fnd_global.user_id,
1980 p_creation_date => sysdate,
1981 p_created_by => fnd_global.user_id,
1982 p_last_update_login => fnd_global.login_id,
1983 p_load_type_code => 1,
1984 p_transaction_type_code => 2,
1985 p_workorder_operation_id => p_x_resrc_Require_tbl(i).workorder_operation_id,
1986 p_bom_resource_id => p_x_resrc_Require_tbl(i).Resource_id,
1987 p_operation_resource_id => p_x_resrc_Require_tbl(i).Operation_Resource_id,
1988 p_res_sched_start_date => p_x_resrc_Require_tbl(i).REQ_START_DATE,
1989 p_res_sched_end_date => p_x_resrc_Require_tbl(i).REQ_START_DATE
1990 );
1991 IF G_DEBUG='Y' THEN
1992 Ahl_Debug_Pub.debug ( l_full_name || ' ******OUTSIDE REQUIRE IF CASE****');
1993 END IF;
1994 END LOOP;
1995 IF G_DEBUG='Y' THEN
1996 Ahl_Debug_Pub.debug ( l_full_name || ' ******OUTSIDE REQUIRE LOOP****');
1997 END IF;
1998
1999 END IF;
2000
2001 --END IF;
2002 ------------------------End of Body---------------------------------------
2003 --Standard check to count messages
2004 l_msg_count := Fnd_Msg_Pub.count_msg;
2005 IF l_msg_count > 0 THEN
2006 X_msg_count := l_msg_count;
2007 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2008 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2009 END IF;
2010
2011 --Standard check for commit
2012 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2013 COMMIT;
2014 END IF;
2015 IF G_DEBUG='Y' THEN
2016 -- Debug info
2017 Ahl_Debug_Pub.debug( 'End of public api Create Resource Reqst +PPResrc_Require_Pvt+');
2018 -- Check if API is called in debug mode. If yes, disable debug.
2019 Ahl_Debug_Pub.disable_debug;
2020 --
2021 END IF;
2022
2023 EXCEPTION
2024 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2025 ROLLBACK TO Create_Resrc_Require;
2029 p_data => x_msg_data);
2026 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2027 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2028 p_count => x_msg_count,
2030 IF G_DEBUG='Y' THEN
2031 -- Debug info.
2032 AHL_DEBUG_PUB.log_app_messages (x_msg_count, x_msg_data, 'ERROR' );
2033
2034 -- Check if API is called in debug mode. If yes, disable debug.
2035 AHL_DEBUG_PUB.disable_debug;
2036 END IF;
2037
2038 WHEN FND_API.G_EXC_ERROR THEN
2039 ROLLBACK TO Create_Resrc_Require;
2040 X_return_status := FND_API.G_RET_STS_ERROR;
2041 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2042 p_count => x_msg_count,
2043 p_data => X_msg_data);
2044 IF G_DEBUG='Y' THEN
2045
2046 Ahl_Debug_Pub.debug('Inside Exception' || '**UNEXPECTED ERRORS');
2047 -- Debug info.
2048 AHL_DEBUG_PUB.log_app_messages (
2049 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
2050
2051 -- Check if API is called in debug mode. If yes, disable debug.
2052 AHL_DEBUG_PUB.disable_debug;
2053 END IF;
2054
2055 WHEN OTHERS THEN
2056 ROLLBACK TO Create_Resrc_Require;
2057 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2058
2059 Ahl_Debug_Pub.debug('Inside Exception' || '**SQL ERRORS');
2060 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2061 THEN
2062 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PP_RESRC_Require_PVT',
2063 p_procedure_name => 'Create_Resrc_Require',
2064 p_error_text => SUBSTR(SQLERRM,1,240));
2065 END IF;
2066 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2067 p_count => x_msg_count,
2068 p_data => X_msg_data);
2069 IF G_DEBUG='Y' THEN
2070
2071 -- Debug info.
2072 AHL_DEBUG_PUB.log_app_messages (
2073 x_msg_count, x_msg_data, 'SQL ERROR' );
2074
2075 -- Check if API is called in debug mode. If yes, disable debug.
2076 AHL_DEBUG_PUB.disable_debug;
2077 END IF;
2078
2079 END Create_Resrc_Require;
2080
2081 -------------------------------------------------------------------------------------------
2082 --
2083 -- Start of Comments --
2084 -- Procedure name : Update_Resrc_Require
2085 -- Type : Private
2086 -- Pre-reqs :
2087 -- Parameters :
2088 --
2089 -- Standard IN Parameters :
2090 -- p_api_version IN NUMBER Required
2091 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2092 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2093 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2094 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
2095 -- p_module_type IN VARCHAR2 Default NULL.
2096 --
2097 -- Standard OUT Parameters :
2098 -- x_return_status OUT VARCHAR2 Required
2099 -- x_msg_count OUT NUMBER Required
2100 -- x_msg_data OUT VARCHAR2 Required
2101 --
2102 -- Update Resource Requirement Parameters:
2103 -- p_x_resrc_Require_tbl IN OUT NOCOPY AHL_PP_RESRC_Require_PVT.Resrc_Require_Tbl_Type,
2104 -- Contains Resource Requirement information to perform Updation
2105 --
2106 -- Version :
2107 -- Initial Version 1.0
2108 --
2109 -- End of Comments.
2110
2111 PROCEDURE Update_Resrc_Require (
2112 p_api_version IN NUMBER,
2113 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
2114 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
2115 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
2116 p_module_type IN VARCHAR2 := Null,
2117 p_interface_flag IN VARCHAR2,
2118
2119 p_x_resrc_Require_Tbl IN OUT NOCOPY Resrc_Require_Tbl_Type,
2120 x_return_status OUT NOCOPY VARCHAR2,
2121 x_msg_count OUT NOCOPY NUMBER,
2122 x_msg_data OUT NOCOPY VARCHAR2
2123 )
2124 IS
2125
2126 -- To find the WORKORDER_ID from AHL_WORKORDER_OPERATIONS_V view
2127 CURSOR c_wo_oper (x_id IN NUMBER) IS
2128 SELECT WORKORDER_ID,OPERATION_SEQUENCE_NUM FROM
2129 AHL_WORKORDER_OPERATIONS_V
2130 WHERE WORKORDER_OPERATION_ID = x_id;
2131
2132 -- To Get Wip Entity Id and Org Id
2133 CURSOR c_work_orders (x_id IN NUMBER) IS
2134 SELECT WIP_ENTITY_ID,WORKORDER_NAME,
2135 ORGANIZATION_ID FROM
2136 AHL_WORKORDERS A, AHL_VISIT_TASKS_B B,
2137 AHL_VISITS_B C
2138 WHERE WORKORDER_ID = x_id
2139 AND A.VISIT_TASK_ID = B.VISIT_TASK_ID
2140 AND B.VISIT_ID = C.VISIT_ID;
2141
2142 -- To find the RESOURCE_SEQUENCE_NUM from AHL_OPERATION_RESOURCES view
2143 CURSOR c_oper_resrc (x_id IN NUMBER) IS
2144 SELECT RESOURCE_SEQUENCE_NUM FROM
2145 AHL_OPERATION_RESOURCES
2146 WHERE WORKORDER_OPERATION_ID = x_id;
2147
2148 -- To find all information from AHL_OPERATION_RESOURCES view
2149 CURSOR c_oper_req (x_id IN NUMBER) IS
2150 SELECT * FROM AHL_OPERATION_RESOURCES
2151 WHERE OPERATION_RESOURCE_ID = x_id;
2155 /*
2152 c_oper_req_rec c_oper_req%ROWTYPE;
2153
2154 -- To find the resource sequence nubmer from ahl_operation_resources
2156 * R12 Perf Tuning
2157 * Balaji modified the query to use only base tables
2158 * instead of AHL_WORKORDERS_V
2159 */
2160 CURSOR c_workorder (x_id IN NUMBER) IS
2161 --SELECT * FROM AHL_WORKORDERS_V
2162 --WHERE WORKORDER_ID = x_id;
2163 SELECT
2164 WO.visit_task_id,
2165 WDJ.owning_department department_id
2166 FROM
2167 AHL_WORKORDERS WO,
2168 WIP_DISCRETE_JOBS WDJ
2169 WHERE
2170 WO.workorder_id = x_id AND
2171 WDJ.wip_entity_id = wo.wip_entity_id;
2172
2173 c_workorder_rec c_workorder%ROWTYPE;
2174
2175 -- To find the resource sequence nubmer from ahl_operation_resources
2176 CURSOR c_resources (x_id IN NUMBER) IS
2177 SELECT DEPARTMENT_ID FROM
2178 BOM_DEPARTMENT_RESOURCES
2179 WHERE RESOURCE_ID = x_id;
2180
2181 -- To find the UOM_CODE from MTL_UNITS_OF_MEASURE table
2182 CURSOR c_UOM (x_name IN VARCHAR2) IS
2183 SELECT UOM_CODE
2184 FROM MTL_UNITS_OF_MEASURE
2185 WHERE UNIT_OF_MEASURE = x_name;
2186
2187 -- Get uom from bom resources
2188 CURSOR c_uom_code (x_id IN NUMBER)
2189 IS
2190 SELECT unit_of_measure
2191 FROM bom_resources
2192 WHERE resource_id = x_id;
2193
2194 --Modified by srini to fix timestamp
2195 --Check to get the timestamp for operation
2196 /*CURSOR wip_operation_dates (c_workorder_id IN NUMBER,
2197 c_op_seq_num IN NUMBER)
2198 IS
2199 SELECT first_unit_start_date,
2200 last_unit_completion_date, a.department_id
2201 FROM wip_operations a, ahl_workorders b
2202 WHERE a.wip_entity_id = b.wip_entity_id
2203 AND workorder_id = c_workorder_id
2204 AND operation_seq_num = c_op_seq_num;*/
2205
2206 -- Fix for bug# 6053137 added by Adithya
2207 CURSOR wip_operation_dates (c_workorder_operation_id IN NUMBER)
2208 IS
2209 SELECT first_unit_start_date,
2210 last_unit_completion_date
2211 FROM wip_operations a, ahl_workorders b,ahl_workorder_operations c
2212 WHERE a.wip_entity_id = b.wip_entity_id
2213 AND b.workorder_id = c.workorder_id
2214 AND a.operation_seq_num = c.OPERATION_SEQUENCE_NUM
2215 AND c.workorder_operation_id = c_workorder_operation_id;
2216
2217 l_api_name CONSTANT VARCHAR2(30) := 'Update_Resrc_Require';
2218 l_full_name CONSTANT VARCHAR2(80) := G_PKG_NAME || '.' || L_API_NAME;
2219 l_api_version CONSTANT NUMBER := 1.0;
2220
2221 l_msg_count NUMBER;
2222 l_object_version_number NUMBER;
2223 l_resrc_seq_num NUMBER;
2224 l_workorder_id NUMBER;
2225 l_wo_operation_id NUMBER;
2226 l_wo_operation_txn_id NUMBER;
2227 l_resrc_dept_id NUMBER;
2228 l_dept_id NUMBER;
2229 j NUMBER;
2230
2231 l_return_status VARCHAR2(1);
2232 l_std_rate_flag VARCHAR2(30);
2233 l_uom_code VARCHAR2(3);
2234 l_msg_data VARCHAR2(2000);
2235
2236 l_Resrc_Require_Rec Resrc_Require_Rec_Type;
2237 l_Resrc_Require_Tbl Resrc_Require_Tbl_Type;
2238 l_default VARCHAR2(10);
2239 l_work_order_rec c_work_orders%ROWTYPE;
2240 l_wip_entity_id NUMBER;
2241 l_op_start_date DATE;
2242 l_op_end_date DATE;
2243 l_department_id NUMBER;
2244
2245 BEGIN
2246 --------------------Initialize ----------------------------------
2247 -- Standard Start of API savepoint
2248 SAVEPOINT Update_Resrc_Require;
2249
2250 -- Check if API is called in debug mode. If yes, enable debug.
2251 IF G_DEBUG='Y' THEN
2252
2253 AHL_DEBUG_PUB.enable_debug;
2254
2255 -- Debug info.
2256 Ahl_Debug_Pub.debug( 'Enter ahl_pp_resrc_require_pvt. Update Resource Requirement +PPResrc_Require_Pvt+');
2257 END IF;
2258
2259 G_MODULE_TYPE:=P_MODULE_TYPE;
2260 -- Standard call to check for call compatibility.
2261 IF FND_API.to_boolean(p_init_msg_list)
2262 THEN
2263 FND_MSG_PUB.initialize;
2264 END IF;
2265
2266 -- Initialize API return status to success
2267 x_return_status := FND_API.G_RET_STS_SUCCESS;
2268
2269 -- Initialize message list if p_init_msg_list is set to TRUE.
2270 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2271 p_api_version,
2272 l_api_name,G_PKG_NAME)
2273 THEN
2274 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2275 END IF;
2276 --------------------Start of API Body-----------------------------------
2277 IF p_x_resrc_Require_tbl.COUNT > 0 THEN
2278 FOR i IN p_x_resrc_Require_tbl.FIRST..p_x_resrc_Require_tbl.LAST
2279 LOOP
2280 l_Resrc_Require_Rec := p_x_resrc_Require_tbl(i);
2281 IF G_DEBUG='Y' THEN
2282 Ahl_Debug_Pub.debug ( l_full_name || 'OPERATION_RESOURCE_ID = ' || l_Resrc_Require_Rec.OPERATION_RESOURCE_ID);
2283 END IF;
2284 --------------------Value OR ID conversion---------------------------
2285 --Start API Body
2286 IF p_module_type = 'JSP'
2287 THEN
2288 l_Resrc_Require_Rec.resource_id := NULL;
2289 END IF;
2290
2291 OPEN c_oper_req(l_Resrc_Require_Rec.OPERATION_RESOURCE_ID);
2292 FETCH c_oper_req INTO c_oper_req_rec;
2296 l_Resrc_Require_Rec.RESOURCE_SEQ_NUMBER := c_oper_req_rec.RESOURCE_SEQUENCE_NUM;
2293 CLOSE c_oper_req;
2294
2295 l_Resrc_Require_Rec.WORKORDER_OPERATION_ID := c_oper_req_rec.WORKORDER_OPERATION_ID;
2297
2298 -- rroy
2299 -- ACL changes
2300 OPEN c_WO_oper (l_Resrc_Require_Rec.WORKORDER_OPERATION_ID);
2301 FETCH c_WO_oper INTO l_workorder_id,l_Resrc_Require_Rec.operation_seq_number;
2302 CLOSE c_WO_oper;
2303
2304 l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked(
2305 p_workorder_id => l_workorder_id,
2306 p_ue_id => NULL,
2307 p_visit_id => NULL,
2308 p_item_instance_id => NULL);
2309 IF l_return_status = FND_API.G_TRUE THEN
2310 FND_MESSAGE.Set_Name('AHL', 'AHL_PP_UPD_RESREQ_UNTLCKD');
2311 FND_MSG_PUB.ADD;
2312 RAISE FND_API.G_EXC_ERROR;
2313 END IF;
2314
2315 -- rroy
2316 -- ACL changes
2317 -- Added as part of fix for bug 4092197
2318 -- Since this check is there in Create API
2319 -- it should be there in update, otherwise
2320 -- creation is allowed but updation of the same record is not allowed
2321 --Required to check the operation start dates and resource start and end date are same
2322 /*OPEN wip_operation_dates(l_Resrc_Require_Rec.workorder_id,
2323 l_Resrc_Require_Rec.operation_seq_number);
2324 FETCH wip_operation_dates INTO l_op_start_date,l_op_end_date,l_department_id;
2325 CLOSE wip_operation_dates;*/
2326 -- Fix for bug# 6053137 added by Adithya
2327 OPEN wip_operation_dates(l_Resrc_Require_Rec.WORKORDER_OPERATION_ID);
2328 FETCH wip_operation_dates INTO l_op_start_date,l_op_end_date;
2329 CLOSE wip_operation_dates;
2330 --Validation is required to include operation timestamp for Requested start date
2331 -- requested end date
2332 -- Bug # 6728602 -- start
2333
2334 IF G_DEBUG='Y' THEN
2335 Ahl_Debug_Pub.debug ( ' l_Resrc_Require_Rec.req_start_date = ' || to_char(l_Resrc_Require_Rec.req_start_date,'DD-MON-YYYY HH24:MI:SS'));
2336 Ahl_Debug_Pub.debug ( ' l_Resrc_Require_Rec.req_end_date = ' || to_char(l_Resrc_Require_Rec.req_end_date,'DD-MON-YYYY HH24:MI:SS'));
2337 END IF;
2338 IF(l_Resrc_Require_Rec.req_start_date < l_op_start_date OR l_Resrc_Require_Rec.req_start_date > l_op_end_date)THEN
2339 l_Resrc_Require_Rec.req_start_date := l_op_start_date;
2340 END IF;
2341 IF(l_Resrc_Require_Rec.req_end_date > l_op_end_date OR l_Resrc_Require_Rec.req_end_date < l_op_start_date)THEN
2342 l_Resrc_Require_Rec.req_end_date := l_op_end_date;
2343 END IF;
2344
2345 /*IF (TRUNC(l_Resrc_Require_Rec.req_start_date) = TRUNC(l_op_start_date )
2346 AND TRUNC(l_Resrc_Require_Rec.req_end_date) = TRUNC(l_op_start_date )) THEN
2347
2348 l_Resrc_Require_Rec.req_start_date := l_op_start_date;
2349 l_Resrc_Require_Rec.req_end_date := l_op_start_date;
2350
2351 ELSIF (TRUNC(l_Resrc_Require_Rec.req_start_date) = TRUNC(l_op_end_date)
2352 AND TRUNC(l_Resrc_Require_Rec.req_end_date) = TRUNC(l_op_end_date )) THEN
2353
2354 l_Resrc_Require_Rec.req_start_date := l_op_end_date;
2355 l_Resrc_Require_Rec.req_end_date := l_op_end_date;
2356
2357 ELSIF (TRUNC(l_Resrc_Require_Rec.req_start_date) = TRUNC(l_op_start_date )
2358 AND TRUNC(l_Resrc_Require_Rec.req_end_date) = TRUNC(l_op_end_date )) THEN
2359
2360 l_Resrc_Require_Rec.req_start_date := l_op_start_date;
2361 l_Resrc_Require_Rec.req_end_date := l_op_end_date;
2362
2363 ELSIF (TRUNC(l_Resrc_Require_Rec.req_start_date) = TRUNC(l_op_start_date )
2364 AND TRUNC(l_Resrc_Require_Rec.req_end_date) <> TRUNC(l_op_start_date )) THEN
2365
2366 l_Resrc_Require_Rec.req_start_date := l_op_start_date;
2367
2368 ELSIF (TRUNC(l_Resrc_Require_Rec.req_start_date) <> TRUNC(l_op_end_date )
2369 AND TRUNC(l_Resrc_Require_Rec.req_end_date) = TRUNC(l_op_end_date )) THEN
2370
2371 l_Resrc_Require_Rec.req_end_date := l_op_end_date;
2372
2373
2374 END IF;*/
2375 -- Bug # 6728602 -- end
2376 -- end of changes for bug 4092197
2377 IF G_DEBUG='Y' THEN
2378 Ahl_Debug_Pub.debug ( l_full_name || ' WORKORDER_OPERATION_ID = ' || l_Resrc_Require_Rec.WORKORDER_OPERATION_ID);
2379 Ahl_Debug_Pub.debug ( l_full_name || ' Resource ID = ' || l_Resrc_Require_Rec.Resource_Id);
2380 END IF;
2381 --Ahl_Debug_Pub.debug ( l_full_name || ' RESOURCE_SEQUENCE_NUM = ' || l_Resrc_Require_Rec.RESOURCE_SEQ_NUMBER);
2382
2383 /*Ahl_Debug_Pub.debug ( l_full_name || ' Object Version Nubmer = ' || l_Resrc_Require_Rec.object_version_number);
2384 Ahl_Debug_Pub.debug ( l_full_name || ' Resource Type Name = ' || l_Resrc_Require_Rec.resource_type_name);
2385 Ahl_Debug_Pub.debug ( l_full_name || ' Resource Type Code = ' || l_Resrc_Require_Rec.resource_type_code);*/
2386
2387 -- For Resource Type
2388 IF ( l_Resrc_Require_Rec.resource_type_name IS NOT NULL AND
2389 l_Resrc_Require_Rec.resource_type_name <> Fnd_Api.G_MISS_CHAR )
2390 THEN
2391 Check_Lookup_Name_Or_Id (
2392 p_lookup_type => 'BOM_RESOURCE_TYPE',
2393 p_lookup_code => NULL,
2394 p_meaning => l_Resrc_Require_Rec.resource_type_name,
2395 p_check_id_flag => 'Y',
2399 IF NVL(l_return_status, 'X') <> 'S'
2396 x_lookup_code => l_Resrc_Require_Rec.resource_type_code,
2397 x_return_status => l_return_status);
2398
2400 THEN
2401 Fnd_Message.SET_NAME('AHL','AHL_PP_RESRC_TYPE_NOT_EXISTS');
2402 Fnd_Msg_Pub.ADD;
2403 RAISE Fnd_Api.G_EXC_ERROR;
2404 END IF;
2405 END IF;
2406
2407 --Ahl_Debug_Pub.debug ( l_full_name || ' Resource Type Code = ' || l_Resrc_Require_Rec.resource_type_code);
2408 --Ahl_Debug_Pub.debug ( l_full_name || ' Resource Name = ' || l_Resrc_Require_Rec.RESOURCE_NAME);
2409 -- For Resource
2410 IF l_Resrc_Require_Rec.RESOURCE_NAME IS NOT NULL AND
2411 l_Resrc_Require_Rec.RESOURCE_NAME <> Fnd_Api.G_MISS_CHAR
2412 THEN
2413 Check_Resource_Name_Or_Id
2414 (p_Resource_id => l_Resrc_Require_Rec.Resource_Id,
2415 p_Resource_code => l_Resrc_Require_Rec.Resource_Name,
2416 p_workorder_id => l_workorder_id,
2417
2418 x_Resource_Id => l_Resrc_Require_Rec.Resource_Id,
2419 x_return_status => l_return_status,
2420 x_error_msg_code => l_msg_data
2421 );
2422
2423 IF NVL(l_return_status, 'X') <> 'S'
2424 THEN
2425 Fnd_Message.SET_NAME('AHL','AHL_PP_RESOURCE_NOT_EXISTS');
2426 Fnd_Msg_Pub.ADD;
2427 RAISE Fnd_Api.G_EXC_ERROR;
2428 END IF;
2429 END IF;
2430 IF G_DEBUG='Y' THEN
2431 Ahl_Debug_Pub.debug ( l_full_name || 'After Resource ID = ' || l_Resrc_Require_Rec.Resource_Id);
2432 END IF;
2433 --Assign workorder
2434 l_Resrc_Require_Rec.workorder_id := l_workorder_id;
2435
2436 -- RESOURCE_TYPE_NAME
2437 IF (l_Resrc_Require_Rec.RESOURCE_TYPE_NAME IS NULL OR l_Resrc_Require_Rec.RESOURCE_TYPE_NAME = Fnd_Api.G_MISS_CHAR) THEN
2438 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '-- Resource Type Name =' || l_Resrc_Require_Rec.RESOURCE_TYPE_NAME);
2439 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
2440 Fnd_Message.set_name ('AHL', 'AHL_PP_RESRC_TYPE_MISSING');
2441 Fnd_Msg_Pub.ADD;
2442 END IF;
2443 END IF;
2444
2445 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '-- Resource Name =' || l_Resrc_Require_Rec.RESOURCE_NAME);
2446 -- RESOURCE_NAME
2447 IF (l_Resrc_Require_Rec.RESOURCE_NAME IS NULL OR l_Resrc_Require_Rec.RESOURCE_NAME = Fnd_Api.G_MISS_CHAR) THEN
2448 Ahl_Debug_Pub.debug('Check_Resrc_Require_Req_Items' || '-- Resource Name =' || l_Resrc_Require_Rec.RESOURCE_NAME);
2449 IF Fnd_Msg_Pub.check_msg_level (Fnd_Msg_Pub.g_msg_lvl_error) THEN
2450 Fnd_Message.set_name ('AHL', 'AHL_PP_RESRC_NAME_MISSING');
2451 Fnd_Msg_Pub.ADD;
2452 END IF;
2453 END IF;
2454
2455 Ahl_Debug_Pub.debug ( l_full_name || 'After WORKORDER ID = ' || l_Resrc_Require_Rec.workorder_id);
2456 /*OPEN c_workorder(l_Resrc_Require_Rec.workorder_id);
2457 FETCH c_workorder INTO c_workorder_rec;
2458 IF c_workorder%NOTFOUND THEN
2459 CLOSE c_workorder;
2460 Fnd_Message.SET_NAME('AHL','AHL_PP_WORKORDER_NOT_EXISTS');
2461 Fnd_Msg_Pub.ADD;
2462 ELSE
2463 CLOSE c_workorder;*/
2464
2465 OPEN c_resources(l_Resrc_Require_Rec.Resource_Id);
2466 FETCH c_resources INTO l_resrc_dept_id;
2467 IF c_resources%NOTFOUND THEN
2468 CLOSE c_resources;
2469 Fnd_Message.SET_NAME('AHL','AHL_PP_RESRC_DEPT_NOT_EXISTS');
2470 Fnd_Msg_Pub.ADD;
2471 ELSE
2472 CLOSE c_resources;
2473 --IF l_resrc_dept_id = c_workorder_rec.department_id THEN
2474 IF l_resrc_dept_id <> l_department_id THEN
2475 Fnd_Message.SET_NAME('AHL','AHL_PP_RESRC_DEPT_NOT_EXISTS');
2476 Fnd_Msg_Pub.ADD;
2477 END IF;
2478 END IF;
2479
2480 --END IF;
2481
2482 IF G_DEBUG='Y' THEN
2483 Ahl_Debug_Pub.debug ( l_full_name || ' Unit of Measure = ' || l_Resrc_Require_Rec.UOM_Name);
2484 Ahl_Debug_Pub.debug ( l_full_name || ' UOM Code = ' || l_Resrc_Require_Rec.UOM_CODE);
2485 END IF;
2486 -- For Units of Measure
2487 IF l_Resrc_Require_Rec.UOM_NAME IS NOT NULL AND
2488 l_Resrc_Require_Rec.UOM_NAME <> Fnd_Api.G_MISS_CHAR
2489 THEN
2490 OPEN c_UOM(l_Resrc_Require_Rec.UOM_NAME);
2491 FETCH c_UOM INTO l_Resrc_Require_Rec.UOM_CODE;
2492 IF c_UOM%NOTFOUND THEN
2493 CLOSE c_UOM;
2494 Fnd_Message.SET_NAME('AHL','AHL_PP_UOM_NOT_EXISTS');
2495 Fnd_Msg_Pub.ADD;
2496 ELSE
2497 CLOSE c_UOM;
2498 END IF;
2499 END IF;
2500 --Convert Uom code
2501 OPEN c_uom_code(l_Resrc_Require_Rec.Resource_Id);
2502 FETCH c_uom_code INTO l_Resrc_Require_Rec.UOM_CODE;
2503 CLOSE c_uom_code;
2504 --
2505 p_x_resrc_Require_tbl(i).uom_code := l_Resrc_Require_Rec.UOM_CODE;
2506
2507 IF G_DEBUG='Y' THEN
2508 Ahl_Debug_Pub.debug ( l_full_name || ' UOM Code = ' || l_Resrc_Require_Rec.UOM_CODE);
2512 -- For AutoCharge Type
2509 Ahl_Debug_Pub.debug ( l_full_name || ' AutoCharge Type = ' || l_Resrc_Require_Rec.CHARGE_TYPE_NAME);
2510 Ahl_Debug_Pub.debug ( l_full_name || ' AutoCharge Code = ' || l_Resrc_Require_Rec.CHARGE_TYPE_CODE);
2511 END IF;
2513 IF ( l_Resrc_Require_Rec.CHARGE_TYPE_NAME IS NOT NULL AND
2514 l_Resrc_Require_Rec.CHARGE_TYPE_NAME <> Fnd_Api.G_MISS_CHAR )
2515 THEN
2516 Check_Lookup_Name_Or_Id (
2517 p_lookup_type => 'BOM_AUTOCHARGE_TYPE',
2518 p_lookup_code => NULL,
2519 p_meaning => l_Resrc_Require_Rec.CHARGE_TYPE_NAME,
2520 p_check_id_flag => 'Y',
2521 x_lookup_code => l_Resrc_Require_Rec.CHARGE_TYPE_CODE,
2522 x_return_status => l_return_status);
2523
2524 IF NVL(l_return_status, 'X') <> 'S'
2525 THEN
2526 Fnd_Message.SET_NAME('AHL','AHL_PP_CHARGE_TYPE_NOT_EXISTS');
2527 Fnd_Msg_Pub.ADD;
2528 RAISE Fnd_Api.G_EXC_ERROR;
2529 END IF;
2530 END IF;
2531 IF G_DEBUG='Y' THEN
2532 Ahl_Debug_Pub.debug ( l_full_name || ' AutoCharge Code = ' || l_Resrc_Require_Rec.CHARGE_TYPE_CODE);
2533 Ahl_Debug_Pub.debug ( l_full_name || ' COST BASIS NAME = ' || l_Resrc_Require_Rec.COST_BASIS_NAME);
2534 Ahl_Debug_Pub.debug ( l_full_name || ' COST BASIS CODE = ' || l_Resrc_Require_Rec.COST_BASIS_CODE);
2535 END IF;
2536 -- For Cost Basis
2537 IF ( l_Resrc_Require_Rec.COST_BASIS_NAME IS NOT NULL AND
2538 l_Resrc_Require_Rec.COST_BASIS_NAME <> Fnd_Api.G_MISS_CHAR )
2539 THEN
2540 Check_Lookup_Name_Or_Id (
2541 p_lookup_type => 'CST_BASIS',
2542 p_lookup_code => NULL,
2543 p_meaning => l_Resrc_Require_Rec.COST_BASIS_NAME,
2544 p_check_id_flag => 'Y',
2545 x_lookup_code => l_Resrc_Require_Rec.COST_BASIS_CODE,
2546 x_return_status => l_return_status);
2547
2548 IF NVL(l_return_status, 'X') <> 'S'
2549 THEN
2550 Fnd_Message.SET_NAME('AHL','AHL_PP_COST_BASIS_NOT_EXISTS');
2551 Fnd_Msg_Pub.ADD;
2552 RAISE Fnd_Api.G_EXC_ERROR;
2553 END IF;
2554 END IF;
2555 IF G_DEBUG='Y' THEN
2556 Ahl_Debug_Pub.debug ( l_full_name || ' COST BASIS CODE = ' || l_Resrc_Require_Rec.COST_BASIS_CODE);
2557 Ahl_Debug_Pub.debug ( l_full_name || ' SCHEDULED TYPE NAME = ' || l_Resrc_Require_Rec.SCHEDULED_TYPE_NAME);
2558 Ahl_Debug_Pub.debug ( l_full_name || ' SCHEDULED TYPE CODE = ' || l_Resrc_Require_Rec.SCHEDULED_TYPE_CODE);
2559 END IF;
2560 -- For Scheduled Type
2561 IF ( l_Resrc_Require_Rec.SCHEDULED_TYPE_NAME IS NOT NULL AND
2562 l_Resrc_Require_Rec.SCHEDULED_TYPE_NAME <> Fnd_Api.G_MISS_CHAR )
2563 THEN
2564 Check_Lookup_Name_Or_Id (
2565 p_lookup_type => 'BOM_RESOURCE_SCHEDULE_TYPE',
2566 p_lookup_code => NULL,
2567 p_meaning => l_Resrc_Require_Rec.SCHEDULED_TYPE_NAME,
2568 p_check_id_flag => 'Y',
2569 x_lookup_code => l_Resrc_Require_Rec.SCHEDULED_TYPE_CODE,
2570 x_return_status => l_return_status);
2571
2572 IF NVL(l_return_status, 'X') <> 'S'
2573 THEN
2574 Fnd_Message.SET_NAME('AHL','AHL_PP_SCHED_TYPE_NOT_EXISTS');
2575 Fnd_Msg_Pub.ADD;
2576 RAISE Fnd_Api.G_EXC_ERROR;
2577 END IF;
2578 END IF;
2579 IF G_DEBUG='Y' THEN
2580 Ahl_Debug_Pub.debug ( l_full_name || ' SCHEDULED TYPE CODE = ' || l_Resrc_Require_Rec.SCHEDULED_TYPE_CODE);
2581 Ahl_Debug_Pub.debug ( l_full_name || ' STANDARD RATE FLAG = ' || l_Resrc_Require_Rec.STD_RATE_FLAG_CODE);
2582 END IF;
2583 -- To find meaning for fnd_lookups code
2584 IF (l_Resrc_Require_Rec.STD_RATE_FLAG_CODE IS NOT NULL AND
2585 l_Resrc_Require_Rec.STD_RATE_FLAG_CODE <> Fnd_Api.G_MISS_NUM) THEN
2586 SELECT meaning
2587 INTO l_std_rate_flag
2588 FROM MFG_LOOKUPS
2589 WHERE lookup_code = l_Resrc_Require_Rec.STD_RATE_FLAG_CODE
2590 AND LOOKUP_TYPE = 'BOM_NO_YES';
2591 END IF;
2592
2593 -------------------------------- Validate -----------------------------------------
2594
2595 Validate_Resrc_Require (
2596 p_api_version => l_api_version,
2597 p_init_msg_list => p_init_msg_list,
2598 p_commit => p_commit,
2599 p_validation_level => p_validation_level,
2600 p_resrc_Require_rec => l_Resrc_Require_Rec,
2601 x_return_status => l_return_status,
2602 x_msg_count => x_msg_count,
2603 x_msg_data => x_msg_data
2604 );
2605
2606 --Standard check to count messages
2607 l_msg_count := Fnd_Msg_Pub.count_msg;
2608
2609 IF l_msg_count > 0 THEN
2610 x_msg_count := l_msg_count;
2611 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2612 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2613 END IF;
2614
2615 --
2619
2616 IF l_Resrc_Require_Rec.Operation_Resource_id <> FND_API.G_MISS_NUM OR l_Resrc_Require_Rec.Operation_Resource_Id IS NOT NULL
2617 THEN
2618 -- These conditions are Required for optional fields
2620 -- Last Updated Date
2621 IF l_Resrc_Require_Rec.last_update_login = FND_API.G_MISS_NUM
2622 THEN
2623 l_Resrc_Require_Rec.last_update_login := NULL;
2624 ELSE
2625 l_Resrc_Require_Rec.last_update_login := l_Resrc_Require_Rec.last_update_login;
2626 END IF;
2627 -- Attribute Category
2628 IF l_Resrc_Require_Rec.attribute_category = FND_API.G_MISS_CHAR
2629 THEN
2630 l_Resrc_Require_Rec.attribute_category := NULL;
2631 ELSE
2632 l_Resrc_Require_Rec.attribute_category := l_Resrc_Require_Rec.attribute_category;
2633 END IF;
2634 -- Attribute1
2635 IF l_Resrc_Require_Rec.attribute1 = FND_API.G_MISS_CHAR
2636 THEN
2637 l_Resrc_Require_Rec.attribute1 := NULL;
2638 ELSE
2639 l_Resrc_Require_Rec.attribute1 := l_Resrc_Require_Rec.attribute1;
2640 END IF;
2641 -- Attribute2
2642 IF l_Resrc_Require_Rec.attribute2 = FND_API.G_MISS_CHAR
2643 THEN
2644 l_Resrc_Require_Rec.attribute2 := NULL;
2645 ELSE
2646 l_Resrc_Require_Rec.attribute2 := l_Resrc_Require_Rec.attribute2;
2647 END IF;
2648 -- Attribute3
2649 IF l_Resrc_Require_Rec.attribute3 = FND_API.G_MISS_CHAR
2650 THEN
2651 l_Resrc_Require_Rec.attribute3 := NULL;
2652 ELSE
2653 l_Resrc_Require_Rec.attribute3 := l_Resrc_Require_Rec.attribute3;
2654 END IF;
2655 -- Attribute4
2656 IF l_Resrc_Require_Rec.attribute4 = FND_API.G_MISS_CHAR
2657 THEN
2658 l_Resrc_Require_Rec.attribute4 := NULL;
2659 ELSE
2660 l_Resrc_Require_Rec.attribute4 := l_Resrc_Require_Rec.attribute4;
2661 END IF;
2662 -- Attribute5
2663 IF l_Resrc_Require_Rec.attribute5 = FND_API.G_MISS_CHAR
2664 THEN
2665 l_Resrc_Require_Rec.attribute5 := NULL;
2666 ELSE
2667 l_Resrc_Require_Rec.attribute5 := l_Resrc_Require_Rec.attribute5;
2668 END IF;
2669 -- Attribute6
2670 IF l_Resrc_Require_Rec.attribute6 = FND_API.G_MISS_CHAR
2671 THEN
2672 l_Resrc_Require_Rec.attribute6 := NULL;
2673 ELSE
2674 l_Resrc_Require_Rec.attribute6 := l_Resrc_Require_Rec.attribute6;
2675 END IF;
2676 -- Attribute7
2677 IF l_Resrc_Require_Rec.attribute7 = FND_API.G_MISS_CHAR
2678 THEN
2679 l_Resrc_Require_Rec.attribute7 := NULL;
2680 ELSE
2681 l_Resrc_Require_Rec.attribute7 := l_Resrc_Require_Rec.attribute7;
2682 END IF;
2683 -- Attribute8
2684 IF l_Resrc_Require_Rec.attribute8 = FND_API.G_MISS_CHAR
2685 THEN
2686 l_Resrc_Require_Rec.attribute8 := NULL;
2687 ELSE
2688 l_Resrc_Require_Rec.attribute8 := l_Resrc_Require_Rec.attribute8;
2689 END IF;
2690 -- Attribute9
2691 IF l_Resrc_Require_Rec.attribute9 = FND_API.G_MISS_CHAR
2692 THEN
2693 l_Resrc_Require_Rec.attribute9 := NULL;
2694 ELSE
2695 l_Resrc_Require_Rec.attribute9 := l_Resrc_Require_Rec.attribute9;
2696 END IF;
2697 -- Attribute10
2698 IF l_Resrc_Require_Rec.attribute10 = FND_API.G_MISS_CHAR
2699 THEN
2700 l_Resrc_Require_Rec.attribute10 := NULL;
2701 ELSE
2702 l_Resrc_Require_Rec.attribute10 := l_Resrc_Require_Rec.attribute10;
2703 END IF;
2704 -- Attribute11
2705 IF l_Resrc_Require_Rec.attribute11 = FND_API.G_MISS_CHAR
2706 THEN
2707 l_Resrc_Require_Rec.attribute11 := NULL;
2708 ELSE
2709 l_Resrc_Require_Rec.attribute11 := l_Resrc_Require_Rec.attribute11;
2710 END IF;
2711 -- Attribute12
2712 IF l_Resrc_Require_Rec.attribute12 = FND_API.G_MISS_CHAR
2713 THEN
2714 l_Resrc_Require_Rec.attribute12 := NULL;
2715 ELSE
2716 l_Resrc_Require_Rec.attribute12 := l_Resrc_Require_Rec.attribute12;
2717 END IF;
2718 -- Attribute13
2719 IF l_Resrc_Require_Rec.attribute13 = FND_API.G_MISS_CHAR
2720 THEN
2721 l_Resrc_Require_Rec.attribute13 := NULL;
2722 ELSE
2723 l_Resrc_Require_Rec.attribute13 := l_Resrc_Require_Rec.attribute13;
2724 END IF;
2725 -- Attribute14
2726 IF l_Resrc_Require_Rec.attribute14 = FND_API.G_MISS_CHAR
2727 THEN
2728 l_Resrc_Require_Rec.attribute14 := NULL;
2729 ELSE
2730 l_Resrc_Require_Rec.attribute14 := l_Resrc_Require_Rec.attribute14;
2731 END IF;
2732 -- Attribute15
2733 IF l_Resrc_Require_Rec.attribute15 = FND_API.G_MISS_CHAR
2734 THEN
2735 l_Resrc_Require_Rec.attribute15 := NULL;
2736 ELSE
2737 l_Resrc_Require_Rec.attribute15 := l_Resrc_Require_Rec.attribute15;
2738 END IF;
2739
2740 p_x_resrc_Require_tbl(i) := l_Resrc_Require_Rec;
2741 -- Check Object version number.
2745 END IF;
2742 IF G_DEBUG='Y' THEN
2743 Ahl_Debug_Pub.debug ( l_full_name || ' Record Object Version Nubmer = ' || l_resrc_Require_Rec.object_version_number);
2744 Ahl_Debug_Pub.debug ( l_full_name || ' Cursor Object Version Nubmer = ' || c_oper_req_rec.object_version_number);
2746 IF (c_oper_req_rec.object_version_number <> l_Resrc_Require_Rec.object_version_number)
2747 THEN
2748 Fnd_Message.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
2749 Fnd_Msg_Pub.ADD;
2750 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2751 --
2752 END IF;
2753
2754 END IF;
2755 END LOOP;
2756 END IF;
2757
2758 IF (p_interface_flag IS NULL) OR (p_interface_flag IS NOT NULL AND p_interface_flag = 'Y') THEN
2759 -- CALL Load_WIP_Jobs API
2760 -- If not sucess then not allowed to insert in our entity
2761 OPEN c_workorder(l_Resrc_Require_Rec.workorder_id);
2762 FETCH c_workorder INTO c_workorder_rec;
2763 CLOSE c_workorder;
2764 --
2765
2766 j:=1;
2767 FOR i IN p_x_resrc_Require_tbl.FIRST..p_x_resrc_Require_tbl.LAST
2768 LOOP
2769 --
2770 OPEN c_work_orders(l_Resrc_Require_Rec.workorder_id);
2771 FETCH c_work_orders INTO l_work_order_rec;
2772 CLOSE c_work_orders;
2773 --
2774 l_Resrc_Require_Tbl(j).organization_id := l_work_order_rec.organization_id;
2775 l_Resrc_Require_Tbl(j).wip_entity_id := l_work_order_rec.wip_entity_id;
2776 l_Resrc_Require_Tbl(j).job_number := l_work_order_rec.workorder_name;
2777 l_Resrc_Require_Tbl(j).workorder_id := p_x_resrc_Require_tbl(i).WORKORDER_ID;
2778 l_Resrc_Require_Tbl(j).operation_seq_number := p_x_resrc_Require_tbl(i).OPERATION_SEQ_NUMBER;
2779 l_Resrc_Require_Tbl(j).uom_code := p_x_resrc_Require_tbl(i).UOM_CODE;
2780 l_Resrc_Require_Tbl(j).resource_seq_number := p_x_resrc_Require_tbl(i).RESOURCE_SEQ_NUMBER;
2781 l_Resrc_Require_Tbl(j).resource_id := p_x_resrc_Require_tbl(i).RESOURCE_ID;
2782 l_Resrc_Require_Tbl(j).duration := p_x_resrc_Require_tbl(i).DURATION;
2783 l_Resrc_Require_Tbl(j).req_start_date := p_x_resrc_Require_tbl(i).REQ_START_DATE;
2784 l_Resrc_Require_Tbl(j).req_end_date := p_x_resrc_Require_tbl(i).REQ_END_DATE;
2785 l_Resrc_Require_Tbl(j).quantity := p_x_resrc_Require_tbl(i).QUANTITY;
2786 l_Resrc_Require_Tbl(j).applied_num := p_x_resrc_Require_tbl(i).QUANTITY;
2787 l_Resrc_Require_Tbl(j).open_num := p_x_resrc_Require_tbl(i).QUANTITY;
2788 l_Resrc_Require_Tbl(j).cost_basis_code := p_x_resrc_Require_tbl(i).COST_BASIS_CODE;
2789 l_Resrc_Require_Tbl(j).charge_type_code := p_x_resrc_Require_tbl(i).CHARGE_TYPE_CODE;
2790 l_Resrc_Require_Tbl(j).std_rate_flag_code := p_x_resrc_Require_tbl(i).STD_RATE_FLAG_CODE;
2791 l_Resrc_Require_Tbl(j).scheduled_type_code := p_x_resrc_Require_tbl(i).SCHEDULED_TYPE_CODE;
2792 -- JKJAIN US space FP for ER # 6998882-- start
2793 l_Resrc_Require_Tbl(j).schedule_seq_num := p_x_resrc_Require_tbl(i).schedule_seq_num;
2794 -- JKJAIN US space FP for ER # 6998882-- end
2795 l_Resrc_Require_Tbl(j).operation_flag := 'U';
2796 --
2797 IF G_DEBUG='Y' THEN
2798 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).WIP_ENTITY_ID: ' ||l_Resrc_Require_Tbl(j).WIP_ENTITY_ID );
2799 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).JOB_NUMBER: ' ||l_Resrc_Require_Tbl(j).JOB_NUMBER );
2800 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).UOM_CODE: ' ||l_Resrc_Require_Tbl(j).UOM_CODE );
2801 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).WORKORDER_ID: ' ||l_Resrc_Require_Tbl(j).WORKORDER_ID );
2802 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).ORGANIZATION_ID: ' ||l_Resrc_Require_Tbl(j).ORGANIZATION_ID );
2803 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).OPERATION_SEQ_NUM: ' ||l_Resrc_Require_Tbl(j).OPERATION_SEQ_NUMBER );
2804 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).RESOURCE_SEQ_NUM: ' ||l_Resrc_Require_Tbl(j).RESOURCE_SEQ_NUMBER );
2805 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).RESOURCE_ID: ' ||l_Resrc_Require_Tbl(j).RESOURCE_ID );
2806 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).QUANTITY: ' ||l_Resrc_Require_Tbl(j).QUANTITY );
2807 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).DURATION: ' ||l_Resrc_Require_Tbl(j).DURATION );
2808 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).REQ_START_DATE: ' ||l_Resrc_Require_Tbl(j).REQ_START_DATE );
2809 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).REQ_END_DATE: ' ||l_Resrc_Require_Tbl(j).REQ_END_DATE );
2810 END IF;
2811
2812 j := j + 1;
2813 END LOOP;
2814
2815 -- Call AHL_EAN_JOB_PVT If the status is success then process
2816 AHL_EAM_JOB_PVT.process_resource_req
2817 (
2818 p_api_version => p_api_version,
2819 p_init_msg_list => p_init_msg_list,
2820 p_commit => p_commit,
2821 p_validation_level => p_validation_level,
2822 p_default => l_default,
2823 p_module_type => p_module_type,
2824 x_return_status => l_return_status,
2825 x_msg_count => l_msg_count,
2826 x_msg_data => l_msg_data,
2827 p_resource_req_tbl => l_Resrc_Require_Tbl);
2828
2829 END IF;
2830
2831 IF l_return_status ='S' THEN
2832 IF p_x_resrc_Require_tbl.COUNT > 0 THEN
2833 FOR i IN p_x_resrc_Require_tbl.FIRST..p_x_resrc_Require_tbl.LAST
2834 LOOP
2835 l_Resrc_Require_Rec := p_x_resrc_Require_tbl(i);
2839 Ahl_Debug_Pub.debug ( l_full_name || 'after WORKORDER_OPERATION_ID = ' || l_Resrc_Require_Rec.WORKORDER_OPERATION_ID);
2836 IF l_Resrc_Require_Rec.Operation_Resource_id <> FND_API.G_MISS_NUM
2837 THEN
2838 IF G_DEBUG='Y' THEN
2840 Ahl_Debug_Pub.debug ( l_full_name || 'after RESOURCE_ID = ' || l_Resrc_Require_Rec.RESOURCE_ID);
2841 Ahl_Debug_Pub.debug ( l_full_name || 'after OBJECT_VERSION_NUBMER = ' || l_Resrc_Require_Rec.OBJECT_VERSION_NUMBER);
2842 END IF;
2843
2844 -- Create Record in schedule Resources
2845 Update_Row (
2846 X_OPERATION_RESOURCE_ID => l_Resrc_Require_Rec.OPERATION_RESOURCE_ID,
2847 X_OBJECT_VERSION_NUMBER => l_Resrc_Require_Rec.OBJECT_VERSION_NUMBER,
2848 X_RESOURCE_ID => l_Resrc_Require_Rec.RESOURCE_ID,
2849 X_WORKORDER_OPERATION_ID=> l_Resrc_Require_Rec.WORKORDER_OPERATION_ID,
2850 X_RESOURCE_SEQ_NUMBER => l_Resrc_Require_Rec.RESOURCE_SEQ_NUMBER,
2851 X_UOM_CODE => l_Resrc_Require_Rec.UOM_CODE,
2852 X_QUANTITY => l_Resrc_Require_Rec.QUANTITY,
2853 X_DURATION => l_Resrc_Require_Rec.DURATION,
2854 X_SCHEDULED_START_DATE => l_Resrc_Require_Rec.REQ_START_DATE,
2855 X_SCHEDULED_END_DATE => l_Resrc_Require_Rec.REQ_END_DATE,
2856 X_ATTRIBUTE_CATEGORY => l_Resrc_Require_Rec.attribute_category,
2857 X_ATTRIBUTE1 => l_Resrc_Require_Rec.attribute1,
2858 X_ATTRIBUTE2 => l_Resrc_Require_Rec.attribute2,
2859 X_ATTRIBUTE3 => l_Resrc_Require_Rec.attribute3,
2860 X_ATTRIBUTE4 => l_Resrc_Require_Rec.attribute4,
2861 X_ATTRIBUTE5 => l_Resrc_Require_Rec.attribute5,
2862 X_ATTRIBUTE6 => l_Resrc_Require_Rec.attribute6,
2863 X_ATTRIBUTE7 => l_Resrc_Require_Rec.attribute7,
2864 X_ATTRIBUTE8 => l_Resrc_Require_Rec.attribute8,
2865 X_ATTRIBUTE9 => l_Resrc_Require_Rec.attribute9,
2866 X_ATTRIBUTE10 => l_Resrc_Require_Rec.attribute10,
2867 X_ATTRIBUTE11 => l_Resrc_Require_Rec.attribute11,
2868 X_ATTRIBUTE12 => l_Resrc_Require_Rec.attribute12,
2869 X_ATTRIBUTE13 => l_Resrc_Require_Rec.attribute13,
2870 X_ATTRIBUTE14 => l_Resrc_Require_Rec.attribute14,
2871 X_ATTRIBUTE15 => l_Resrc_Require_Rec.attribute15,
2872 X_LAST_UPDATE_DATE => SYSDATE,
2873 X_LAST_UPDATED_BY => fnd_global.user_id,
2874 X_LAST_UPDATE_LOGIN => fnd_global.login_id
2875 );
2876
2877
2878 IF G_DEBUG='Y' THEN
2879 Ahl_Debug_Pub.debug ( 'l_Resrc_Require_Rec.CHARGE_TYPE_CODE:'||l_Resrc_Require_Rec.CHARGE_TYPE_CODE);
2880 END IF;
2881 p_x_resrc_Require_tbl(i) := l_Resrc_Require_Rec;
2882 --Get the value from sequence
2883 SELECT AHL_WO_OPERATIONS_TXNS_S.NEXTVAL
2884 INTO l_wo_operation_txn_id
2885 FROM DUAL;
2886 IF G_DEBUG='Y' THEN
2887 AHL_DEBUG_PUB.debug( 'before calling log record l_wo_operation_txn_id:'||l_wo_operation_txn_id);
2888 END IF;
2889 -- Create Record in transactions table
2890 AHL_PP_MATERIALS_PVT.Log_Transaction_Record
2891 ( p_wo_operation_txn_id => l_wo_operation_txn_id,
2892 p_object_version_number => 1,
2893 p_last_update_date => sysdate,
2894 p_last_updated_by => fnd_global.user_id,
2895 p_creation_date => sysdate,
2896 p_created_by => fnd_global.user_id,
2897 p_last_update_login => fnd_global.login_id,
2898 p_load_type_code => 1,
2899 p_transaction_type_code => 3,
2900 p_workorder_operation_id => p_x_resrc_Require_tbl(i).workorder_operation_id,
2901 p_bom_resource_id => p_x_resrc_Require_tbl(i).Resource_id,
2902 p_operation_resource_id => p_x_resrc_Require_tbl(i).Operation_Resource_id,
2903 p_res_sched_start_date => p_x_resrc_Require_tbl(i).REQ_START_DATE,
2904 p_res_sched_end_date => p_x_resrc_Require_tbl(i).REQ_START_DATE
2905 );
2906
2907 END IF;
2908 END LOOP;
2909 END IF;
2910 END IF;
2911 --
2912 ------------------------End of Body---------------------------------------
2913 -- Standard check to count messages
2914 l_msg_count := Fnd_Msg_Pub.count_msg;
2915
2916 IF l_msg_count > 0 THEN
2917 X_msg_count := l_msg_count;
2918 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2919 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
2920 END IF;
2921
2922 --Standard check for commit
2923 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2924 COMMIT;
2925 END IF;
2926
2927 -- Debug info
2928 IF G_DEBUG='Y' THEN
2929 Ahl_Debug_Pub.debug( 'End of Update Resource Reqst +PPResrc_Require_Pvt+');
2930
2931 -- Check if API is called in debug mode. If yes, disable debug.
2935 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2932 Ahl_Debug_Pub.disable_debug;
2933 END IF;
2934 EXCEPTION
2936 ROLLBACK TO Update_Resrc_Require;
2937 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2938 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2939 p_count => x_msg_count,
2940 p_data => x_msg_data);
2941 IF G_DEBUG='Y' THEN
2942 -- Debug info.
2943 AHL_DEBUG_PUB.log_app_messages (
2944 x_msg_count, x_msg_data, 'ERROR' );
2945
2946 -- Check if API is called in debug mode. If yes, disable debug.
2947 AHL_DEBUG_PUB.disable_debug;
2948 END IF;
2949 WHEN FND_API.G_EXC_ERROR THEN
2950 ROLLBACK TO Update_Resrc_Require;
2951 X_return_status := FND_API.G_RET_STS_ERROR;
2952 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2953 p_count => x_msg_count,
2954 p_data => X_msg_data);
2955 IF G_DEBUG='Y' THEN
2956 -- Debug info.
2957 AHL_DEBUG_PUB.log_app_messages (
2958 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
2959
2960 -- Check if API is called in debug mode. If yes, disable debug.
2961 AHL_DEBUG_PUB.disable_debug;
2962 --
2963 END IF;
2964 WHEN OTHERS THEN
2965 ROLLBACK TO Update_Resrc_Require;
2966 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2967 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2968 THEN
2969 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PP_RESRC_Require_PVT',
2970 p_procedure_name => 'UPDATE_Resrc_Require',
2971 p_error_text => SUBSTR(SQLERRM,1,240));
2972 END IF;
2973 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2974 p_count => x_msg_count,
2975 p_data => X_msg_data);
2976 IF G_DEBUG='Y' THEN
2977 -- Debug info.
2978 AHL_DEBUG_PUB.log_app_messages (
2979 x_msg_count, x_msg_data, 'SQL ERROR' );
2980
2981 -- Check if API is called in debug mode. If yes, disable debug.
2982 AHL_DEBUG_PUB.disable_debug;
2983 END IF;
2984 END Update_Resrc_Require;
2985
2986 --------------------------------------------------------------------
2987 -- PROCEDURE
2988 -- Get_Resource_Requirement
2989 --
2990 -- PURPOSE
2991 -- Get a particular Resource Requirement with all details
2992 --------------------------------------------------------------------
2993 PROCEDURE Get_Resource_Requirement (
2994 p_api_version IN NUMBER,
2995 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
2996 p_commit IN VARCHAR2 := Fnd_Api.g_false,
2997 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
2998 p_module_type IN VARCHAR2 := 'JSP',
2999 p_x_resrc_Require_Tbl IN OUT NOCOPY AHL_PP_RESRC_Require_PVT.Resrc_Require_Tbl_Type,
3000 x_return_status OUT NOCOPY VARCHAR2,
3001 x_msg_count OUT NOCOPY NUMBER,
3002 x_msg_data OUT NOCOPY VARCHAR2
3003 )
3004 IS
3005 L_API_VERSION CONSTANT NUMBER := 1.0;
3006 L_API_NAME CONSTANT VARCHAR2(30) := 'Get_Resource_Requirement';
3007 L_FULL_NAME CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || L_API_NAME;
3008
3009 l_Resrc_Require_Rec AHL_PP_RESRC_Require_PVT.Resrc_Require_Rec_Type;
3010
3011 l_resrc_type_name VARCHAR2(80);
3012 l_resrc_type_code VARCHAR2(30);
3013 l_unit_of_measure VARCHAR2(30);
3014 l_resrc_name VARCHAR2(10);
3015 l_std_rate_flag VARCHAR2(10);
3016 l_charge_type VARCHAR2(80);
3017 l_cost_basis VARCHAR2(80);
3018 l_scheduled_type VARCHAR2(80);
3019
3020 l_applied NUMBER;
3021 l_wo_oper_id NUMBER;
3022 l_oper_seq_num NUMBER;
3023
3024 oper_start_date DATE;
3025 oper_end_date DATE;
3026
3027 CURSOR c_res_req (x_id IN NUMBER) IS
3028 SELECT * FROM AHL_OPERATION_RESOURCES
3029 WHERE OPERATION_RESOURCE_ID = x_id;
3030 c_resrc_req c_res_req%ROWTYPE;
3031
3032 CURSOR c_resource (x_id IN NUMBER) IS
3033 SELECT ML.MEANING, BR.RESOURCE_TYPE, BR.RESOURCE_CODE
3034 FROM BOM_RESOURCES BR, MFG_LOOKUPS ML, AHL_OPERATION_RESOURCES AOR
3035 WHERE BR.RESOURCE_TYPE = ML.LOOKUP_CODE
3036 AND ML.LOOKUP_TYPE= 'BOM_RESOURCE_TYPE'
3037 AND AOR.RESOURCE_ID = BR.RESOURCE_ID
3038 AND AOR.OPERATION_RESOURCE_ID = x_id;
3039
3040 CURSOR c_WIP_oper (x_id IN NUMBER) IS
3041 SELECT WORV.* FROM
3042 AHL_OPERATION_RESOURCES AOR,
3043 AHL_WORKORDER_OPERATIONS AWO,
3044 AHL_WORKORDERS AW,
3045 WIP_OPERATION_RESOURCES_V WORV
3046 WHERE WORV.OPERATION_SEQ_NUM = AWO.OPERATION_SEQUENCE_NUM
3047 AND WORV.RESOURCE_SEQ_NUM = AOR.RESOURCE_SEQUENCE_NUM
3048 AND WORV.WIP_ENTITY_ID = AW.WIP_ENTITY_ID
3049 AND AW.WORKORDER_ID = AWO.WORKORDER_ID
3050 AND AWO.WORKORDER_OPERATION_ID = AOR.WORKORDER_OPERATION_ID
3051 AND AOR.OPERATION_RESOURCE_ID = x_id;
3052 c_WIP_oper_rec c_WIP_oper%ROWTYPE;
3053
3054 CURSOR c_WO_oper(x_id IN NUMBER) IS
3055 SELECT OPERATION_SEQUENCE_NUM, SCHEDULED_START_DATE, SCHEDULED_END_DATE
3056 FROM AHL_WORKORDER_OPERATIONS_V
3060 * Balaji modified the query to use only base tables
3057 WHERE WORKORDER_OPERATION_ID = x_id;
3058
3059 /* R12 Perf Tuning
3061 * instead of ahl_pp_requirement_v
3062 */
3063 CURSOR c_require (x_id IN NUMBER) IS
3064 /*
3065 SELECT *
3066 FROM ahl_pp_requirement_v
3067 WHERE REQUIREMENT_ID = x_id;
3068 */
3069 SELECT
3070 BOM.UNIT_OF_MEASURE uom_code,
3071 MUOM.UNIT_OF_MEASURE UOM_NAME,
3072 AWO.workorder_id job_id
3073 FROM
3074 BOM_RESOURCES BOM,
3075 MTL_UNITS_OF_MEASURE MUOM,
3076 AHL_OPERATION_RESOURCES AOR,
3077 AHL_WORKORDER_OPERATIONS AWO
3078 WHERE
3079 AOR.OPERATION_RESOURCE_ID = x_id AND
3080 AOR.RESOURCE_ID = BOM.RESOURCE_ID AND
3081 BOM.UNIT_OF_MEASURE = MUOM.UOM_CODE AND
3082 AOR.WORKORDER_OPERATION_ID = AWO.WORKORDER_OPERATION_ID;
3083
3084 c_require_rec c_require%ROWTYPE;
3085
3086 CURSOR c_lookups (x_lookup_type IN VARCHAR2, x_lookup_code IN VARCHAR2) IS
3087 SELECT meaning
3088 FROM MFG_LOOKUPS
3089 WHERE lookup_type = x_lookup_type
3090 AND lookup_code = x_lookup_code
3091 AND TRUNC(SYSDATE) BETWEEN TRUNC(NVL(start_date_active, SYSDATE))
3092 AND TRUNC(NVL(end_date_active,SYSDATE));
3093
3094 BEGIN
3095 -------------------------------- Initialize -----------------------
3096 -- Standard start of API savepoint
3097 SAVEPOINT Get_Resource_Requirement;
3098 IF G_DEBUG='Y' THEN
3099 -- Check if API is called in debug mode. If yes, enable debug.
3100 Ahl_Debug_Pub.enable_debug;
3101 --
3102 END IF;
3103 -- Debug info.
3104 IF G_DEBUG='Y' THEN
3105 Ahl_Debug_Pub.debug ( l_full_name || ' Start ');
3106 END IF;
3107
3108 -- Standard call to check for call compatibility.
3109 IF Fnd_Api.to_boolean(p_init_msg_list)
3110 THEN
3111 Fnd_Msg_Pub.initialize;
3112 END IF;
3113
3114 -- Initialize API return status to success
3115 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3116
3117 -- Initialize message list if p_init_msg_list is set to TRUE.
3118 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
3119 p_api_version,
3120 l_api_name,G_PKG_NAME)
3121 THEN
3122 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3123 END IF;
3124
3125 IF p_x_resrc_Require_tbl.COUNT > 0 THEN
3126 FOR i IN p_x_resrc_Require_tbl.FIRST..p_x_resrc_Require_tbl.LAST
3127 LOOP
3128 l_Resrc_Require_Rec := p_x_resrc_Require_Tbl(i);
3129 ----------------------------------------- Cursor ----------------------------------
3130 IF G_DEBUG='Y' THEN
3131 Ahl_Debug_Pub.debug ( l_full_name || ' Operation Resource Id = ' || l_Resrc_Require_Rec.operation_resource_id);
3132 END IF;
3133 OPEN c_res_req (l_Resrc_Require_Rec.operation_resource_id);
3134 FETCH c_res_req INTO c_resrc_req;
3135 CLOSE c_res_req;
3136
3137 OPEN c_resource (l_Resrc_Require_Rec.operation_resource_id);
3138 FETCH c_resource INTO l_resrc_type_name, l_resrc_type_code, l_resrc_name;
3139 CLOSE c_resource;
3140 IF G_DEBUG='Y' THEN
3141 Ahl_Debug_Pub.debug ( l_full_name || ' Resource Type Name = ' || l_resrc_type_name);
3142 Ahl_Debug_Pub.debug ( l_full_name || ' Resource Type Code = ' || l_resrc_type_code);
3143 Ahl_Debug_Pub.debug ( l_full_name || ' Resource Name = ' || l_resrc_name);
3144 END IF;
3145 OPEN c_WO_oper (c_resrc_req.workorder_operation_id);
3146 FETCH c_WO_oper INTO l_oper_seq_num, oper_start_date, oper_end_date;
3147 CLOSE c_WO_oper;
3148 IF G_DEBUG='Y' THEN
3149 Ahl_Debug_Pub.debug ( l_full_name || ' Operation Sequence Number = ' || l_oper_seq_num);
3150 Ahl_Debug_Pub.debug ( l_full_name || ' Operation Start Date = ' || oper_start_date);
3151 Ahl_Debug_Pub.debug ( l_full_name || ' Operation End Date = ' || oper_end_date);
3152 Ahl_Debug_Pub.debug ( l_full_name || ' Resource Id = ' || c_resrc_req.resource_id);
3153 END IF;
3154
3155 OPEN c_WIP_oper ( l_Resrc_Require_Rec.operation_resource_id);
3156 FETCH c_WIP_oper INTO c_WIP_oper_rec;
3157 CLOSE c_WIP_oper;
3158
3159 OPEN c_require (l_Resrc_Require_Rec.operation_resource_id);
3160 FETCH c_require INTO c_require_rec;
3161 CLOSE c_require;
3162
3163 ------------------------------------------ Start -----------------------------------
3164 -- Debug info.
3165
3166 -- To find meaning for fnd_lookups code
3167 IF (c_WIP_oper_rec.STANDARD_RATE_FLAG IS NOT NULL) THEN
3168 SELECT meaning
3169 INTO l_std_rate_flag
3170 FROM MFG_LOOKUPS
3171 WHERE lookup_code = c_WIP_oper_rec.STANDARD_RATE_FLAG
3172 AND LOOKUP_TYPE = 'BOM_NO_YES';
3173 END IF;
3174
3175 OPEN c_lookups ('CST_BASIS', c_WIP_oper_rec.basis_type);
3176 FETCH c_lookups INTO l_cost_basis;
3177 CLOSE c_lookups;
3178
3179 OPEN c_lookups ('BOM_RESOURCE_SCHEDULE_TYPE', c_WIP_oper_rec.scheduled_flag);
3180 FETCH c_lookups INTO l_scheduled_type;
3181 CLOSE c_lookups;
3182
3183 -- Assigning all visits field to visit record attributes meant for display
3184
3185 l_Resrc_Require_Rec.OPERATION_RESOURCE_ID := c_resrc_req.OPERATION_RESOURCE_ID ;
3189 l_Resrc_Require_Rec.REQ_END_DATE := c_resrc_req.SCHEDULED_END_DATE ;
3186 l_Resrc_Require_Rec.OBJECT_VERSION_NUMBER := c_resrc_req.object_version_number ;
3187
3188 l_Resrc_Require_Rec.REQ_START_DATE := c_resrc_req.SCHEDULED_START_DATE ;
3190 -- change for ER 3974014
3191 -- the duration entered into AHL_OPERATION_RESOURCES is now the total_duration of all
3192 -- the resources
3193 -- so total_required := c_resrc_rec.duration
3194 -- duration := c_resrc_rec.duration/quantity
3195
3196 l_Resrc_Require_Rec.TOTAL_REQUIRED := c_resrc_req.DURATION; -- (c_resrc_req.DURATION * c_resrc_req.QUANTITY);
3197 -- Balaji changed APPLIED_NUM to be c_WIP_oper_rec.APPLIED_RESOURCE_UNITS instead of
3198 -- c_WIP_oper_rec.APPLIED_RESOURCE_VALUE b'cos VALUE = UNITS * COST.
3199 l_Resrc_Require_Rec.APPLIED_NUM := c_WIP_oper_rec.APPLIED_RESOURCE_UNITS;
3200 --l_Resrc_Require_Rec.APPLIED_NUM := c_WIP_oper_rec.APPLIED_RESOURCE_VALUE;
3201 l_Resrc_Require_Rec.OPEN_NUM := (l_Resrc_Require_Rec.TOTAL_REQUIRED - l_Resrc_Require_Rec.APPLIED_NUM);
3202
3203 l_Resrc_Require_Rec.STD_RATE_FLAG_NAME := l_std_rate_flag;
3204 l_Resrc_Require_Rec.STD_RATE_FLAG_CODE := c_WIP_oper_rec.Standard_Rate_Flag ;
3205
3206 l_Resrc_Require_Rec.RESOURCE_SEQ_NUMBER := c_resrc_req.RESOURCE_SEQUENCE_NUM;
3207 l_Resrc_Require_Rec.OPERATION_SEQ_NUMBER := l_oper_seq_num;
3208 -- JKJAIN US space FP for ER # 6998882
3209 l_Resrc_Require_Rec.SCHEDULE_SEQ_NUM := c_WIP_oper_rec.SCHEDULE_SEQ_NUM;
3210
3211 l_Resrc_Require_Rec.RESOURCE_TYPE_CODE := l_resrc_type_code;
3212 l_Resrc_Require_Rec.RESOURCE_TYPE_NAME := l_resrc_type_name;
3213
3214 l_Resrc_Require_Rec.RESOURCE_ID := c_resrc_req.RESOURCE_ID;
3215 l_Resrc_Require_Rec.RESOURCE_NAME := l_resrc_name;
3216
3217 l_Resrc_Require_Rec.OPER_START_DATE := oper_start_date;
3218 l_Resrc_Require_Rec.OPER_END_DATE := oper_end_date;
3219 -- ER 3974014
3220 IF c_resrc_req.QUANTITY = 0 THEN
3221 l_Resrc_Require_Rec.DURATION := 0;
3222 ELSE
3223 l_resrc_require_rec.DURATION := c_resrc_req.DURATION/c_resrc_req.QUANTITY;
3224 END IF;
3225 l_Resrc_Require_Rec.QUANTITY := c_resrc_req.QUANTITY;
3226 l_Resrc_Require_Rec.SET_UP := c_WIP_oper_rec.SETUP_ID;
3227
3228 l_Resrc_Require_Rec.UOM_CODE := c_require_rec.UOM_CODE;
3229 l_Resrc_Require_Rec.UOM_NAME := c_require_rec.UOM_NAME;
3230
3231 l_Resrc_Require_Rec.COST_BASIS_CODE := c_WIP_oper_rec.BASIS_TYPE;
3232 l_Resrc_Require_Rec.COST_BASIS_NAME := l_cost_basis;
3233
3234 l_Resrc_Require_Rec.SCHEDULED_TYPE_CODE := c_WIP_oper_rec.SCHEDULED_FLAG;
3235 l_Resrc_Require_Rec.SCHEDULED_TYPE_NAME := l_scheduled_type;
3236
3237 l_Resrc_Require_Rec.CHARGE_TYPE_CODE := c_WIP_oper_rec.AUTOCHARGE_TYPE;
3238 l_Resrc_Require_Rec.CHARGE_TYPE_NAME := c_WIP_oper_rec.AUTOCHARGE_CODE;
3239
3240 -- ACL Changes
3241
3242 l_Resrc_Require_Rec.IS_UNIT_LOCKED := AHL_PRD_UTIL_PKG.Is_Unit_Locked(p_workorder_id => c_require_rec.job_id,
3243 p_ue_id => NULL,
3244 p_visit_id => NULL,
3245 p_item_instance_id => NULL);
3246
3247
3248 p_x_resrc_Require_Tbl(i) := l_Resrc_Require_Rec;
3249 /*Ahl_Debug_Pub.debug ( l_full_name || ' *********************values assing to output parameter***********************');
3250 Ahl_Debug_Pub.debug ( l_full_name || ' Work Order Id = ' || p_x_resrc_Require_tbl(i).WORKORDER_ID);
3251 Ahl_Debug_Pub.debug ( l_full_name || ' Operation Resource Id = ' || p_x_resrc_Require_tbl(i).operation_resource_id);
3252 Ahl_Debug_Pub.debug ( l_full_name || ' Resource Seq Num = ' || p_x_resrc_Require_tbl(i).RESOURCE_SEQ_NUMBER);
3253 Ahl_Debug_Pub.debug ( l_full_name || ' Operation Seq Num = ' || p_x_resrc_Require_tbl(i).OPERATION_SEQ_NUMBER);
3254 Ahl_Debug_Pub.debug ( l_full_name || ' Operation Start = ' || p_x_resrc_Require_tbl(i).OPER_START_DATE);
3255 Ahl_Debug_Pub.debug ( l_full_name || ' Operation End = ' || p_x_resrc_Require_tbl(i).OPER_END_DATE);*/
3256
3257 END LOOP;
3258 END IF;
3259 -- Standard call to get message count and if count is 1, get message info
3260 Fnd_Msg_Pub.Count_And_Get
3261 ( p_count => x_msg_count,
3262 p_data => x_msg_data,
3263 p_encoded => Fnd_Api.g_false);
3264
3265 -- Check if API is called in debug mode. If yes, enable debug.
3266 IF G_DEBUG='Y' THEN
3267 Ahl_Debug_Pub.enable_debug;
3268 END IF;
3269 -- Debug info.
3270 IF Ahl_Debug_Pub.G_FILE_DEBUG THEN
3271 Ahl_Debug_Pub.debug( L_FULL_NAME || '- End');
3272 END IF;
3273
3274 -- Check if API is called in debug mode. If yes, disable debug.
3275 Ahl_Debug_Pub.disable_debug;
3276 RETURN;
3277
3278 EXCEPTION
3279 WHEN Fnd_Api.G_EXC_ERROR THEN
3280 x_return_status := Fnd_Api.G_RET_STS_ERROR;
3281 ROLLBACK TO Get_Resource_Requirement;
3282 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
3283 p_data => x_msg_data,
3284 p_encoded => Fnd_Api.g_false);
3285
3286
3287 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
3288 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3292 p_encoded => Fnd_Api.g_false);
3289 ROLLBACK TO Get_Resource_Requirement;
3290 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
3291 p_data => x_msg_data,
3293
3294 WHEN OTHERS THEN
3295 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3296 ROLLBACK TO Get_Resource_Requirement;
3297 Fnd_Msg_Pub.add_exc_msg( p_pkg_name => G_PKG_NAME,
3298 p_procedure_name => 'Get_Resource_Requirement',
3299 p_error_text => SQLERRM);
3300
3301 Fnd_Msg_Pub.count_and_get( p_count => x_msg_count,
3302 p_data => x_msg_data,
3303 p_encoded => Fnd_Api.g_false);
3304 END Get_Resource_Requirement;
3305 --
3306 PROCEDURE Remove_Resource_Requirement (
3307 p_api_version IN NUMBER,
3308 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
3309 p_commit IN VARCHAR2 := Fnd_Api.g_false,
3310 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
3311 p_module_type IN VARCHAR2 := 'JSP',
3312 p_interface_flag IN VARCHAR2,
3313 p_x_resrc_Require_Tbl IN OUT NOCOPY AHL_PP_RESRC_Require_PVT.Resrc_Require_Tbl_Type,
3314 x_return_status OUT NOCOPY VARCHAR2,
3315 x_msg_count OUT NOCOPY NUMBER,
3316 x_msg_data OUT NOCOPY VARCHAR2
3317 )
3318 IS
3319 --
3320 CURSOR Get_resource_cur (c_op_resource_id IN NUMBER)
3321 IS
3322 SELECT * FROM AHL_OPERATION_RESOURCES
3323 WHERE operation_resource_id = c_op_resource_id;
3324 --
3325 /* R12 Perf Tuning
3326 * Balaji modified the query to use only base tables
3327 * instead of AHL_WORKORDERS_V
3328 */
3329 CURSOR Get_job_number(c_workorder_id IN NUMBER)
3330 IS
3331 SELECT
3332 wo.workorder_name,
3333 wdj.organization_id,
3334 wo.wip_entity_id
3335 FROM
3336 ahl_workorders wo,
3337 wip_discrete_jobs wdj
3338 WHERE
3339 wo.workorder_id = c_workorder_id AND
3340 wdj.wip_entity_id = wo.wip_entity_id;
3341
3342 --
3343 CURSOR Get_wo_oper_cur (c_wo_operation_id IN NUMBER)
3344 IS
3345 SELECT * FROM ahl_workorder_operations_v
3346 WHERE workorder_operation_id = c_wo_operation_id;
3347
3348 --
3349 CURSOR c_chk_assgn (oper_resrc_id IN NUMBER)
3350 IS
3351 SELECT count(*) FROM AHL_WORK_ASSIGNMENTS
3352 WHERE OPERATION_RESOURCE_ID = oper_resrc_id;
3353 -- Get uom from bom resources
3354 CURSOR c_uom_code (x_id IN NUMBER)
3355 IS
3356 SELECT unit_of_measure
3357 FROM bom_resources
3358 WHERE resource_id = x_id;
3359 -- Get cost basis, std rate flag,charge type code
3360 CURSOR c_wip_oper_res (c_wip_entity_id IN NUMBER,
3361 c_oper_seq IN NUMBER,
3362 c_res_seq_num IN NUMBER)
3363 IS
3364 SELECT * FROM WIP_OPERATION_RESOURCES
3365 WHERE WIP_ENTITY_ID = c_wip_entity_id
3366 AND OPERATION_SEQ_NUM = c_oper_seq
3367 AND RESOURCE_SEQ_NUM = c_res_seq_num;
3368
3369
3370 -- check resource txns.
3371 CURSOR check_resrc_txn (p_wip_entity_id IN NUMBER,
3372 p_organization_id IN NUMBER,
3373 p_op_seq IN NUMBER,
3374 p_res_seq IN NUMBER)
3375 IS
3376 SELECT 'x' FROM DUAL
3377 WHERE EXISTS ( SELECT 'x'
3378 FROM WIP_TRANSACTIONS
3379 WHERE wip_entity_id = p_wip_entity_id
3380 AND organization_id = p_organization_id
3381 AND operation_seq_num = p_op_seq
3382 AND resource_seq_num = p_res_seq )
3383 OR EXISTS (SELECT 'x'
3384 FROM WIP_COST_TXN_INTERFACE
3385 WHERE wip_entity_id = p_wip_entity_id
3386 AND organization_id = p_organization_id
3387 AND operation_seq_num = p_op_seq
3388 AND resource_seq_num = p_res_seq);
3389
3390 l_api_name CONSTANT VARCHAR2(30) := 'Remove_Resource_Requirement';
3391 l_api_version CONSTANT NUMBER := 1.0;
3392
3393 l_msg_count NUMBER;
3394 l_msg_data VARCHAR2(200);
3395 l_return_status VARCHAR2(1);
3396 l_error_message VARCHAR2(30);
3397 l_job_number VARCHAR2(80);
3398 l_organization_id NUMBER;
3399 l_wo_operation_txn_id NUMBER;
3400 l_count NUMBER;
3401 l_std_rate_flag VARCHAR2(10);
3402 l_uom_code VARCHAR2(10);
3403 l_resource_rec Get_resource_cur%ROWTYPE;
3404 l_wo_oper_rec Get_wo_oper_cur%ROWTYPE;
3405 l_resrc_Require_Tbl Resrc_Require_Tbl_Type;
3406 l_Resrc_Require_Rec Resrc_Require_Rec_Type;
3407 --
3408 l_wip_oper_res_rec c_wip_oper_res%ROWTYPE;
3409 l_wip_entity_id NUMBER;
3410 j NUMBER;
3411 l_default VARCHAR2(10);
3412 l_junk VARCHAR2(1);
3413
3414
3415 BEGIN
3416 --------------------Initialize ----------------------------------
3417 -- Standard Start of API savepoint
3418 SAVEPOINT Remove_Resource_Requirement;
3419
3420 -- Check if API is called in debug mode. If yes, enable debug.
3421 IF G_DEBUG='Y' THEN
3422 Ahl_Debug_Pub.enable_debug;
3423
3424 -- Debug info.
3428 IF Fnd_Api.to_boolean(p_init_msg_list)
3425 Ahl_Debug_Pub.debug( 'Enter ahl_pp_resrc_require_pvt Remove Resource Requirement +APRRP+');
3426 END IF;
3427 -- Standard call to check for call compatibility.
3429 THEN
3430 Fnd_Msg_Pub.initialize;
3431 END IF;
3432
3433 -- Initialize API return status to success
3434 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3435
3436 -- Initialize message list if p_init_msg_list is set to TRUE.
3437 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
3438 p_api_version,
3439 l_api_name,G_PKG_NAME)
3440 THEN
3441 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3442 END IF;
3443
3444 ------------------------Start API Body ---------------------------------
3445
3446 IF p_x_resrc_Require_tbl.COUNT > 0 THEN
3447 FOR i IN p_x_resrc_Require_tbl.FIRST..p_x_resrc_Require_tbl.LAST
3448 LOOP
3449 --
3450 IF G_DEBUG='Y' THEN
3451 Ahl_Debug_Pub.debug ( ' p_x_resrc_Require_tbl(i).operation_resource_id = ' || p_x_resrc_Require_tbl(i).operation_resource_id);
3452 END IF;
3453 --
3454 IF (p_x_resrc_Require_tbl(i).operation_resource_id IS NOT NULL AND
3455 p_x_resrc_Require_tbl(i).operation_resource_id <> FND_API.G_MISS_NUM)
3456 THEN
3457 --
3458 OPEN Get_resource_cur (p_x_resrc_Require_tbl(i).operation_resource_id);
3459 FETCH Get_resource_cur INTO l_resource_rec;
3460 IF Get_resource_cur%NOTFOUND THEN
3461 Fnd_Message.Set_Name('AHL','AHL_PP_RECORD_INVALID');
3462 Fnd_Msg_Pub.ADD;
3463 CLOSE Get_resource_cur;
3464 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3465 END IF;
3466 CLOSE Get_resource_cur;
3467 END IF;
3468 --
3469 IF G_DEBUG='Y' THEN
3470 Ahl_Debug_Pub.debug ( ' l_resource_rec.resource_id = ' || l_resource_rec.resource_id);
3471 END IF;
3472 --
3473 --Check for object version number
3474 IF (p_x_resrc_Require_tbl(i).object_version_number IS NOT NULL AND
3475 p_x_resrc_Require_tbl(i).object_version_number <> FND_API.G_MISS_NUM)
3476 THEN
3477 IF(p_x_resrc_Require_tbl(i).object_version_number <> l_resource_rec.object_version_number )
3478 THEN
3479 --
3480 Fnd_Message.Set_Name('AHL','AHL_COM_RECORD_CHANGED');
3481 Fnd_Msg_Pub.ADD;
3482 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3483 END IF;
3484 END IF;
3485 --
3486 --Get workorder id, operation sequence number
3487 OPEN Get_wo_oper_cur (l_resource_rec.workorder_operation_id);
3488 FETCH Get_wo_oper_cur INTO l_wo_oper_rec;
3489 CLOSE Get_wo_oper_cur;
3490 --
3491 --Assign the values
3492 p_x_resrc_Require_tbl(i).RESOURCE_SEQ_NUMBER := l_resource_rec.resource_sequence_num;
3493 p_x_resrc_Require_tbl(i).RESOURCE_ID := l_resource_rec.resource_id;
3494 p_x_resrc_Require_tbl(i).OPERATION_SEQ_NUMBER := l_wo_oper_rec.operation_sequence_num;
3495 p_x_resrc_Require_tbl(i).WORKORDER_ID := l_wo_oper_rec.workorder_id;
3496 p_x_resrc_Require_tbl(i).DURATION := l_resource_rec.duration;
3497 p_x_resrc_Require_tbl(i).REQ_START_DATE := l_resource_rec.scheduled_start_date;
3498 p_x_resrc_Require_tbl(i).REQ_END_DATE := l_resource_rec.scheduled_end_date;
3499 p_x_resrc_Require_tbl(i).QUANTITY := l_resource_rec.quantity;
3500 -- Get workorder details
3501 --
3502 OPEN Get_job_number(p_x_resrc_require_tbl(i).workorder_id);
3503 FETCH Get_job_number INTO l_job_number, l_organization_id,l_wip_entity_id;
3504 CLOSE Get_job_number;
3505
3506 -- rroy
3507 -- ACL changes
3508
3509 l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked(
3510 p_workorder_id => p_x_resrc_require_tbl(i).workorder_id,
3511 p_ue_id => NULL,
3512 p_visit_id => NULL,
3513 p_item_instance_id => NULL);
3514 IF l_return_status = FND_API.G_TRUE THEN
3515 FND_MESSAGE.Set_Name('AHL', 'AHL_PP_DEL_RESREQ_UNTLCKD');
3516 FND_MSG_PUB.ADD;
3517 RAISE FND_API.G_EXC_ERROR;
3518 END IF;
3519
3520 -- rroy
3521 -- ACL changes
3522
3523 -- validate if there are any resource txns.
3524 OPEN check_resrc_txn(l_wip_entity_id, l_organization_id,
3525 l_wo_oper_rec.operation_sequence_num,
3526 l_resource_rec.resource_sequence_num);
3527 FETCH check_resrc_txn INTO l_junk;
3528 IF (check_resrc_txn%FOUND) THEN
3529 FND_MESSAGE.Set_Name('AHL', 'AHL_PP_DEL_RESREQ_RESTXN');
3530 FND_MESSAGE.Set_Token('OPER_RES', l_wo_oper_rec.operation_sequence_num || '-' || l_resource_rec.resource_sequence_num);
3531 FND_MSG_PUB.ADD;
3532 CLOSE check_resrc_txn;
3533 RAISE FND_API.G_EXC_ERROR;
3534 END IF;
3535 CLOSE check_resrc_txn;
3536
3537 -- Get operation Resource details
3538 OPEN c_wip_oper_res (l_wip_entity_id,
3539 l_wo_oper_rec.operation_sequence_num,
3540 l_resource_rec.resource_sequence_num);
3544 IF G_DEBUG='Y' THEN
3541 FETCH c_wip_oper_res INTO l_wip_oper_res_rec;
3542 CLOSE c_wip_oper_res;
3543 --
3545 Ahl_Debug_Pub.debug ( ' AutoCharge Code = ' || l_wip_oper_res_rec.AUTOCHARGE_TYPE);
3546 Ahl_Debug_Pub.debug ( ' COST BASIS = ' || l_wip_oper_res_rec.BASIS_TYPE);
3547 Ahl_Debug_Pub.debug ( ' STANDARD_RATE_FLAG = ' || l_wip_oper_res_rec.STANDARD_RATE_FLAG);
3548 Ahl_Debug_Pub.debug ( ' l_Resrc_Require_Rec.Resource_Id = ' || l_Resrc_Require_Rec.Resource_Id);
3549 END IF;
3550 --Assign Org,Wip entity details
3551 p_x_resrc_Require_tbl(i).ORGANIZATION_ID := l_organization_id;
3552 p_x_resrc_Require_tbl(i).WIP_ENTITY_ID := l_wip_entity_id;
3553 p_x_resrc_Require_tbl(i).CHARGE_TYPE_CODE := l_wip_oper_res_rec.AUTOCHARGE_TYPE;
3554 p_x_resrc_Require_tbl(i).COST_BASIS_CODE := l_wip_oper_res_rec.BASIS_TYPE;
3555 p_x_resrc_Require_tbl(i).std_rate_flag_code := l_wip_oper_res_rec.STANDARD_RATE_FLAG;
3556 p_x_resrc_Require_tbl(i).scheduled_type_code := l_wip_oper_res_rec.SCHEDULED_FLAG;
3557 --Check for Eam code
3558 OPEN c_uom_code(l_Resource_Rec.Resource_Id);
3559 FETCH c_uom_code INTO l_uom_code;
3560 CLOSE c_uom_code;
3561 --
3562 IF G_DEBUG='Y' THEN
3563 Ahl_Debug_Pub.debug ('l_UOM_CODE: ' ||l_uom_code );
3564 END IF;
3565 --
3566 p_x_resrc_Require_tbl(i).UOM_CODE := l_uom_code;
3567
3568 IF G_DEBUG='Y' THEN
3569 Ahl_Debug_Pub.debug ('Inside validation RESEQ'||p_x_resrc_Require_tbl(i).RESOURCE_SEQ_NUMBER);
3570 Ahl_Debug_Pub.debug ('Inside validationRESOURCES'||p_x_resrc_Require_tbl(i).RESOURCE_ID);
3571 Ahl_Debug_Pub.debug ('Inside OPERATIONSEQ:'||p_x_resrc_Require_tbl(i).OPERATION_SEQ_NUMBER);
3572 Ahl_Debug_Pub.debug ('Inside woid:'||p_x_resrc_Require_tbl(i).workorder_id);
3573 Ahl_Debug_Pub.debug ('Inside operation resource:'||p_x_resrc_Require_tbl(i).operation_resource_id);
3574 Ahl_Debug_Pub.debug ('Inside OVN:'||p_x_resrc_Require_tbl(i).object_version_number);
3575 Ahl_Debug_Pub.debug ('l_Resrc_Require_Rec.UOM_CODE: ' ||p_x_resrc_Require_tbl(i).uom_code );
3576
3577 END IF;
3578 l_msg_count := Fnd_Msg_Pub.count_msg;
3579 IF G_DEBUG='Y' THEN
3580 Ahl_Debug_Pub.debug ( 'msg count:'||l_msg_count);
3581 END IF;
3582
3583 IF l_msg_count > 0 THEN
3584 X_msg_count := l_msg_count;
3585 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3586 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3587 END IF;
3588
3589 END LOOP;
3590 END IF; --Count
3591 --
3592 IF x_return_status = 'S' THEN
3593 -- Assign before calling Ahl Eam Job API
3594 j:=1;
3595 FOR i IN p_x_resrc_Require_tbl.FIRST..p_x_resrc_Require_tbl.LAST
3596 LOOP
3597 Ahl_Debug_Pub.debug ( 'CALL FOR WIP JOBS');
3598 l_resrc_Require_Tbl(j).organization_id := p_x_resrc_Require_tbl(i).ORGANIZATION_ID;
3599 l_resrc_Require_Tbl(j).wip_entity_id := p_x_resrc_Require_tbl(i).WIP_ENTITY_ID;
3600 l_Resrc_Require_Tbl(j).workorder_id := p_x_resrc_Require_tbl(i).WORKORDER_ID;
3601 l_resrc_Require_Tbl(j).operation_seq_number := p_x_resrc_Require_tbl(i).OPERATION_SEQ_NUMBER;
3602 l_resrc_Require_Tbl(j).resource_seq_number := p_x_resrc_Require_tbl(i).RESOURCE_SEQ_NUMBER;
3603 l_resrc_Require_Tbl(j).resource_id := p_x_resrc_Require_tbl(i).RESOURCE_ID;
3604 l_Resrc_Require_Tbl(j).uom_code := p_x_resrc_Require_tbl(i).UOM_CODE;
3605 l_Resrc_Require_Tbl(j).duration := p_x_resrc_Require_tbl(i).DURATION;
3606 l_Resrc_Require_Tbl(j).req_start_date := p_x_resrc_Require_tbl(i).REQ_START_DATE;
3607 l_Resrc_Require_Tbl(j).req_end_date := p_x_resrc_Require_tbl(i).REQ_END_DATE;
3608 l_Resrc_Require_Tbl(j).quantity := p_x_resrc_Require_tbl(i).QUANTITY;
3609 l_Resrc_Require_Tbl(j).cost_basis_code := p_x_resrc_Require_tbl(i).COST_BASIS_CODE;
3610 l_Resrc_Require_Tbl(j).charge_type_code := p_x_resrc_Require_tbl(i).CHARGE_TYPE_CODE;
3611 l_Resrc_Require_Tbl(j).std_rate_flag_code := p_x_resrc_Require_tbl(i).STD_RATE_FLAG_CODE;
3612 l_Resrc_Require_Tbl(j).scheduled_type_code := p_x_resrc_Require_tbl(i).SCHEDULED_TYPE_CODE;
3613 l_resrc_Require_Tbl(j).operation_flag := 'D';
3614 --
3615 IF G_DEBUG='Y' THEN
3616 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).WIP_ENTITY_ID: ' ||l_Resrc_Require_Tbl(j).WIP_ENTITY_ID );
3617 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).WORKORDER_ID: ' ||l_Resrc_Require_Tbl(j).WORKORDER_ID );
3618 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).ORGANIZATION_ID: ' ||l_Resrc_Require_Tbl(j).ORGANIZATION_ID );
3619 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).OPERATION_SEQ_NUM: ' ||l_Resrc_Require_Tbl(j).OPERATION_SEQ_NUMBER );
3620 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).RESOURCE_SEQ_NUM: ' ||l_Resrc_Require_Tbl(j).RESOURCE_SEQ_NUMBER );
3621 Ahl_Debug_Pub.debug ('l_Resrc_Require_Tbl(j).RESOURCE_ID: ' ||l_Resrc_Require_Tbl(j).RESOURCE_ID );
3622
3623 END IF;
3624
3625 j := j + 1;
3626 END LOOP;
3627
3628 -- Call AHL_EAN_JOB_PVT If the status is success then process
3629 AHL_EAM_JOB_PVT.process_resource_req
3630 (
3631 p_api_version => p_api_version,
3632 p_init_msg_list => p_init_msg_list,
3633 p_commit => p_commit,
3634 p_validation_level => p_validation_level,
3635 p_default => l_default,
3636 p_module_type => p_module_type,
3640 p_resource_req_tbl => l_Resrc_Require_Tbl);
3637 x_return_status => l_return_status,
3638 x_msg_count => l_msg_count,
3639 x_msg_data => l_msg_data,
3641
3642 END IF; -- X STATUS
3643
3644 IF l_return_status = 'S' THEN
3645 IF p_x_resrc_Require_tbl.COUNT > 0 THEN
3646 FOR i IN p_x_resrc_Require_tbl.FIRST..p_x_resrc_Require_tbl.LAST
3647 LOOP
3648 OPEN c_chk_assgn (p_x_resrc_Require_tbl(i).Operation_Resource_Id);
3649 FETCH c_chk_assgn INTO l_count;
3650 CLOSE c_chk_assgn;
3651
3652 IF l_count > 0 THEN
3653 Ahl_Debug_Pub.debug ('Count in Assignments table' || l_count);
3654 Fnd_Message.SET_NAME('AHL','AHL_PP_RESRC_ASSIGN_EXITS');
3655 FND_MESSAGE.SET_TOKEN('RECORD',p_x_resrc_Require_tbl(i).RESOURCE_SEQ_NUMBER,FALSE);
3656 Fnd_Msg_Pub.ADD;
3657 ELSE
3658 Ahl_Debug_Pub.debug ('Count in Assignments table' || l_count);
3659 Ahl_Debug_Pub.debug ('BEFORE DELETE RESOURCES' || p_x_resrc_Require_tbl(i).Operation_Resource_Id);
3660 DELETE FROM AHL_OPERATION_RESOURCES
3661 WHERE OPERATION_RESOURCE_ID = p_x_resrc_Require_tbl(i).operation_resource_id;
3662 END IF;
3663 END LOOP;
3664 END IF;--Count
3665 END IF; --Return status
3666
3667 ---------------------------End of Body---------------------------------------
3668 --Standard check to count messages
3669 l_msg_count := Fnd_Msg_Pub.count_msg;
3670
3671 IF l_msg_count > 0 THEN
3672 X_msg_count := l_msg_count;
3673 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3674 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3675 END IF;
3676
3677 --Standard check for commit
3678 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
3679 COMMIT;
3680 END IF;
3681
3682 -- Debug info
3683 IF G_DEBUG='Y' THEN
3684 Ahl_Debug_Pub.debug( 'End of private api Remove Resource Requirement +MAMRP+');
3685
3686 -- Check if API is called in debug mode. If yes, disable debug.
3687 Ahl_Debug_Pub.disable_debug;
3688 END IF;
3689 EXCEPTION
3690 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
3691 ROLLBACK TO Remove_Resource_Requirement;
3692 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3693 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
3694 p_count => x_msg_count,
3695 p_data => x_msg_data);
3696 IF G_DEBUG='Y' THEN
3697 Ahl_Debug_Pub.log_app_messages (
3698 x_msg_count, x_msg_data, 'ERROR' );
3699
3700 -- Check if API is called in debug mode. If yes, disable debug.
3701 Ahl_Debug_Pub.disable_debug;
3702 END IF;
3703 WHEN Fnd_Api.G_EXC_ERROR THEN
3704 ROLLBACK TO Remove_Resource_Requirement;
3705 X_return_status := Fnd_Api.G_RET_STS_ERROR;
3706 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
3707 p_count => x_msg_count,
3708 p_data => X_msg_data);
3709 IF G_DEBUG='Y' THEN
3710 -- Debug info.
3711 Ahl_Debug_Pub.log_app_messages (
3712 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
3713
3714 -- Check if API is called in debug mode. If yes, disable debug.
3715 Ahl_Debug_Pub.disable_debug;
3716 END IF;
3717
3718 WHEN OTHERS THEN
3719 ROLLBACK TO Remove_Resource_Requirement;
3720 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3721 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
3722 THEN
3723 Fnd_Msg_Pub.add_exc_msg(p_pkg_name => 'AHL_PP_RESRC_Require_PVT',
3724 p_procedure_name => 'Remove_Resource_Requirement',
3725 p_error_text => SUBSTR(SQLERRM,1,240));
3726 END IF;
3727 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
3728 p_count => x_msg_count,
3729 p_data => x_msg_data);
3730 IF G_DEBUG='Y' THEN
3731 -- Debug info.
3732 Ahl_Debug_Pub.log_app_messages (
3733 x_msg_count, x_msg_data, 'SQL ERROR' );
3734
3735 -- Check if API is called in debug mode. If yes, disable debug.
3736 Ahl_Debug_Pub.disable_debug;
3737 END IF;
3738 END Remove_Resource_Requirement;
3739
3740 ----------------------------------------------------------------------------------
3741 -- Public Procedure Definitions follow --
3742 ----------------------------------------------------------------------------------
3743 -- Start of Comments --
3744 -- Procedure name : Process_Resrc_Require
3745 -- Type : Private
3746 -- Function : Process ............................based on operation flag
3747 -- Pre-reqs :
3748 -- Parameters :
3749 --
3750 -- Standard IN Parameters :
3751 -- p_api_version IN NUMBER Required
3752 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
3753 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
3754 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
3758 -- Standard OUT Parameters :
3755 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
3756 -- p_module_type IN VARCHAR2 Default NULL.
3757 --
3759 -- x_return_status OUT VARCHAR2 Required
3760 -- x_msg_count OUT NUMBER Required
3761 -- x_msg_data OUT VARCHAR2 Required
3762 --
3763 -- Process Resource Requirement Parameters:
3764 -- p_x_resrc_Require_tbl IN OUT NOCOPY AHL_PP_RESRC_Require_PVT.Resrc_Require_Tbl_Type,
3765 -- Contains........................ on operation flag
3766 --
3767 -- Version :
3768 -- Initial Version 1.0
3769 --
3770 -- End of Comments.
3771
3772 PROCEDURE Process_Resrc_Require (
3773 p_api_version IN NUMBER,
3774 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
3775 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
3776 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
3777 p_module_type IN VARCHAR2 := NULL,
3778 p_operation_flag IN VARCHAR2,
3779 p_interface_flag IN VARCHAR2,
3780 p_x_resrc_Require_tbl IN OUT NOCOPY AHL_PP_RESRC_Require_PVT.Resrc_Require_Tbl_Type,
3781 x_return_status OUT NOCOPY VARCHAR2,
3782 x_msg_count OUT NOCOPY NUMBER,
3783 x_msg_data OUT NOCOPY VARCHAR2
3784 )
3785 IS
3786 l_api_name CONSTANT VARCHAR2(30) := 'Process_Resrc_Require';
3787 l_api_version CONSTANT NUMBER := 1.0;
3788 l_msg_count NUMBER;
3789 l_return_status VARCHAR2(1);
3790 l_msg_data VARCHAR2(2000);
3791 l_resrc_Require_rec AHL_PP_RESRC_Require_PVT.Resrc_Require_Rec_Type;
3792 l_up_workorder_rec AHL_PRD_WORKORDER_PVT.prd_workorder_rec;
3793 l_up_workoper_tbl AHL_PRD_WORKORDER_PVT.prd_workoper_tbl;
3794 l_plan_flag NUMBER;
3795
3796 CURSOR c_check_planned_wo(c_workorder_id IN NUMBER)
3797 IS
3798 SELECT
3799 WDJ.firm_planned_flag
3800 FROM
3801 WIP_DISCRETE_JOBS WDJ,
3802 AHL_WORKORDERS AWO
3803 WHERE
3804 AWO.wip_entity_id = WDJ.wip_entity_id AND
3805 AWO.workorder_id = c_workorder_id;
3806
3807 BEGIN
3808 --------------------Initialize ----------------------------------
3809 -- Standard Start of API savepoint
3810 SAVEPOINT Process_Resrc_Require;
3811 -- Check if API is called in debug mode. If yes, enable debug.
3812 IF G_DEBUG='Y' THEN
3813 AHL_DEBUG_PUB.enable_debug;
3814 -- Debug info.
3815 Ahl_Debug_Pub.debug( 'Enter AHL_PP_RESRC_Require.process_resrc_Require +PPResrc_Require_Pvt+');
3816 END IF;
3817 -- Standard call to check for call compatibility.
3818 IF FND_API.to_boolean(p_init_msg_list)
3819 THEN
3820 FND_MSG_PUB.initialize;
3821 END IF;
3822 -- Initialize API return status to success
3823 x_return_status := FND_API.G_RET_STS_SUCCESS;
3824 -- Initialize message list if p_init_msg_list is set to TRUE.
3825 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
3826 p_api_version,
3827 l_api_name,G_PKG_NAME)
3828 THEN
3829 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3830 END IF;
3831 --------------------Start of API Body-----------------------------------
3832 IF p_operation_flag = 'C' THEN
3833 --
3834 -- Call create Resource Requirement
3835 Create_Resrc_Require (
3836 p_api_version => p_api_version,
3837 p_init_msg_list => p_init_msg_list,
3838 p_commit => p_commit,
3839 p_validation_level => p_validation_level,
3840 p_module_type => p_module_type,
3841 p_interface_flag => p_interface_flag,
3842 p_x_resrc_Require_tbl => p_x_resrc_Require_tbl,
3843 x_return_status => l_return_status,
3844 x_msg_count => l_msg_count,
3845 x_msg_data => l_msg_data
3846 ) ;
3847 IF G_DEBUG='Y' THEN
3848 Ahl_Debug_Pub.debug('AFTER CREATE_RESRC_REQUIRE');
3849 END IF;
3850 ELSIF p_operation_flag = 'U' THEN
3851 IF G_DEBUG='Y' THEN
3852 Ahl_Debug_Pub.debug( 'after update'||p_operation_flag);
3853 END IF;
3854 -- Call Update Resource Requirement
3855 Update_Resrc_Require (
3856 p_api_version => p_api_version,
3857 p_init_msg_list => p_init_msg_list,
3858 p_commit => p_commit,
3859 p_validation_level => p_validation_level,
3860 p_module_type => p_module_type,
3861 p_interface_flag => Null,
3862 p_x_resrc_Require_tbl => p_x_resrc_Require_tbl,
3863 x_return_status => l_return_status,
3864 x_msg_count => l_msg_count,
3865 x_msg_data => l_msg_data
3866 );
3867
3868 ELSIF p_operation_flag = 'D' THEN
3869
3870 -- Call Remove Resource Requirement
3871 Remove_Resource_Requirement (
3872 p_api_version => p_api_version,
3873 p_init_msg_list => p_init_msg_list,
3874 p_commit => p_commit,
3875 p_validation_level => p_validation_level,
3876 p_module_type => p_module_type,
3877 p_interface_flag => NULL,
3878 p_x_resrc_Require_tbl => p_x_resrc_Require_tbl,
3879 x_return_status => l_return_status,
3880 x_msg_count => l_msg_count,
3881 x_msg_data => l_msg_data
3882 );
3883
3884 ELSIF p_operation_flag = 'L' THEN
3885
3886 -- Call to Get Resource Requirement
3887 Get_Resource_Requirement (
3888 p_api_version => p_api_version,
3889 p_init_msg_list => p_init_msg_list,
3890 p_commit => p_commit,
3891 p_validation_level => p_validation_level,
3892 p_module_type => p_module_type,
3893 p_x_resrc_Require_tbl => p_x_resrc_Require_tbl,
3894 x_return_status => l_return_status,
3895 x_msg_count => l_msg_count,
3896 x_msg_data => l_msg_data
3897 );
3898 END IF;
3899 ------------------------End of Body---------------------------------------
3900 --Standard check to count messages
3901 l_msg_count := Fnd_Msg_Pub.count_msg;
3902
3903 -- Balaji added following piece of code for bug # 5099536
3904 -- Update_Job API is called to recalculate Master workorder actual dates
3905 -- after altering resource requirement for a child workorder.
3906 -- Child workorder scheduled dates are re-calculated correctly by EAM
3907 -- when resource requirements are altered(Added/Removed/Updated) provided
3908 -- the Firm_Planned_Flag is set to "Planned" where as
3909 -- master workorder dates are not recalculated by EAM properly.
3910
3911 IF (
3912 ( p_operation_flag = 'C' OR p_operation_flag = 'U' OR p_operation_flag = 'D')
3913 AND
3914 l_msg_count = 0
3915 )
3916 THEN
3917
3918 FOR i IN p_x_resrc_Require_tbl.FIRST..p_x_resrc_Require_tbl.LAST LOOP
3919 OPEN c_check_planned_wo(p_x_resrc_Require_tbl(i).workorder_id);
3920 FETCH c_check_planned_wo INTO l_plan_flag;
3921 CLOSE c_check_planned_wo;
3922
3923 IF l_plan_flag = 2 THEN
3924
3925 l_up_workorder_rec.WORKORDER_ID := p_x_resrc_Require_tbl(i).workorder_id;
3926
3927 AHL_PRD_WORKORDER_PVT.update_job
3928 (
3929 p_api_version => 1.0 ,
3930 p_init_msg_list => FND_API.G_FALSE ,
3931 p_commit => FND_API.G_FALSE ,
3932 p_validation_level => FND_API.G_VALID_LEVEL_FULL ,
3933 p_default => FND_API.G_TRUE ,
3934 p_module_type => NULL ,
3935 x_return_status => l_return_status ,
3936 x_msg_count => l_msg_count ,
3937 x_msg_data => l_msg_data ,
3938 p_wip_load_flag => 'Y' ,
3939 p_x_prd_workorder_rec => l_up_workorder_rec ,
3940 p_x_prd_workoper_tbl => l_up_workoper_tbl
3941 );
3942
3943 IF ( l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
3944 RAISE FND_API.G_EXC_ERROR;
3945 END IF;
3946
3947 END IF;
3948 END LOOP;
3949 END IF;
3950
3951 IF l_msg_count > 0 THEN
3952 x_msg_count := l_msg_count;
3953 x_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3954 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3955 END IF;
3956
3957 --Standard check for commit
3958 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
3959 COMMIT;
3960 END IF;
3961 IF G_DEBUG='Y' THEN
3962 -- Debug info
3963 Ahl_Debug_Pub.debug( 'End of public api Process Resource Requirement + PPResrc_Require_Pvt+');
3964
3965 -- Check if API is called in debug mode. If yes, disable debug.
3966 Ahl_Debug_Pub.disable_debug;
3967 END IF;
3968 EXCEPTION
3969 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3970 ROLLBACK TO Process_Resrc_Require;
3971 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3972 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3973 p_count => x_msg_count,
3974 p_data => x_msg_data);
3975 IF G_DEBUG='Y' THEN
3976 AHL_DEBUG_PUB.log_app_messages (
3977 x_msg_count, x_msg_data, 'ERROR' );
3978 -- Check if API is called in debug mode. If yes, disable debug.
3979 AHL_DEBUG_PUB.disable_debug;
3980 END IF;
3981 WHEN FND_API.G_EXC_ERROR THEN
3982 ROLLBACK TO Process_Resrc_Require;
3983 X_return_status := FND_API.G_RET_STS_ERROR;
3984 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3985 p_count => x_msg_count,
3986 p_data => X_msg_data);
3987 IF G_DEBUG='Y' THEN
3988 -- Debug info.
3989 AHL_DEBUG_PUB.log_app_messages (
3990 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
3991 -- Check if API is called in debug mode. If yes, disable debug.
3992 AHL_DEBUG_PUB.disable_debug;
3993 END IF;
3994 WHEN OTHERS THEN
3995 ROLLBACK TO Process_Resrc_Require;
3996 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3997 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3998 THEN
3999 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PP_RESRC_Require_PVT',
4000 p_procedure_name => 'Process_Resrc_Require',
4001 p_error_text => SUBSTR(SQLERRM,1,240));
4002 END IF;
4003 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4004 p_count => x_msg_count,
4005 p_data => x_msg_data);
4006 IF G_DEBUG='Y' THEN
4007 -- Debug info.
4008 AHL_DEBUG_PUB.log_app_messages (
4009 x_msg_count, x_msg_data, 'SQL ERROR' );
4010 -- Check if API is called in debug mode. If yes, disable debug.
4011 AHL_DEBUG_PUB.disable_debug;
4012 END IF;
4013 END Process_Resrc_Require;
4014
4015 END AHL_PP_RESRC_Require_PVT;