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