[Home] [Help]
PACKAGE BODY: APPS.AHL_PP_MATERIALS_PVT
Source
1 PACKAGE BODY AHL_PP_MATERIALS_PVT AS
2 /* $Header: AHLVPPMB.pls 120.15.12010000.5 2009/01/09 00:41:02 sikumar ship $*/
3 --
4 -----------------------
5 -- Declare Constants --
6 -----------------------
7 G_PKG_NAME VARCHAR2(30) := 'AHL_PP_MATERIALS_PVT';
8 G_DEBUG VARCHAR2(1) := AHL_DEBUG_PUB.is_log_enabled;
9
10 -------------------------------------------------
11 -- Declare Locally used Record and Table Types --
12 -------------------------------------------------
13
14 ------------------------------
15 -- Declare Local Procedures --
16 ------------------------------
17
18 -- Procedure to get organization ID
19 PROCEDURE Check_org_name_Or_Id
20 (p_organization_id IN NUMBER,
21 p_org_name IN VARCHAR2,
22 x_organization_id OUT NOCOPY NUMBER,
23 x_return_status OUT NOCOPY VARCHAR2,
24 x_error_msg_code OUT NOCOPY VARCHAR2
25 );
26 -- Procedure to get department ID
27 PROCEDURE Check_dept_desc_Or_Id
28 (p_organization_id IN NUMBER,
29 p_org_name IN VARCHAR2,
30 p_department_id IN NUMBER,
31 p_dept_description IN VARCHAR2,
32 x_department_id OUT NOCOPY NUMBER,
33 x_return_status OUT NOCOPY VARCHAR2,
34 x_error_msg_code OUT NOCOPY VARCHAR2
35 );
36
37 -- Procedure to get visit task ID
38 PROCEDURE Get_visit_task_Id
39 (p_workorder_id IN NUMBER,
40 x_visit_task_id OUT NOCOPY NUMBER,
41 x_return_status OUT NOCOPY VARCHAR2,
42 x_error_msg_code OUT NOCOPY VARCHAR2
43 );
44
45 -- Procedure to get inventory item ID
46 PROCEDURE Get_inventory_item_Id
47 (p_inventory_item_id IN NUMBER,
48 p_concatenated_segments IN VARCHAR2,
49 p_organization_id IN NUMBER,
50 x_inventory_item_id OUT NOCOPY NUMBER,
51 x_return_status OUT NOCOPY VARCHAR2,
52 x_error_msg_code OUT NOCOPY VARCHAR2
53 );
54
55 -- Procedure to get visit task details
56 PROCEDURE Get_visit_task_details
57 ( p_visit_task_id IN NUMBER,
58 x_visit_id OUT NOCOPY NUMBER,
59 x_organization_id OUT NOCOPY NUMBER,
60 x_department_id OUT NOCOPY NUMBER,
61 x_project_task_id OUT NOCOPY NUMBER,
62 x_project_id OUT NOCOPY NUMBER
63 );
64
65 PROCEDURE Get_workorder_Id
66 (p_workorder_id IN NUMBER,
67 p_job_number IN VARCHAR2,
68 x_workorder_id OUT NOCOPY NUMBER,
69 x_return_status OUT NOCOPY VARCHAR2,
70 x_error_msg_code OUT NOCOPY VARCHAR2
71 );
72
73 PROCEDURE Get_workorder_operation_Id
74 (p_workorder_id IN NUMBER,
75 p_operation_sequence IN NUMBER,
76 x_workorder_operation_id OUT NOCOPY NUMBER,
77 x_return_status OUT NOCOPY VARCHAR2,
78 x_error_msg_code OUT NOCOPY VARCHAR2
79 );
80
81 TYPE dff_default_values_type IS RECORD
82 (
83 ATTRIBUTE_CATEGORY VARCHAR2(30),
84 ATTRIBUTE1 VARCHAR2(150),
85 ATTRIBUTE2 VARCHAR2(150),
86 ATTRIBUTE3 VARCHAR2(150),
87 ATTRIBUTE4 VARCHAR2(150),
88 ATTRIBUTE5 VARCHAR2(150),
89 ATTRIBUTE6 VARCHAR2(150),
90 ATTRIBUTE7 VARCHAR2(150),
91 ATTRIBUTE8 VARCHAR2(150),
92 ATTRIBUTE9 VARCHAR2(150),
93 ATTRIBUTE10 VARCHAR2(150),
94 ATTRIBUTE11 VARCHAR2(150),
95 ATTRIBUTE12 VARCHAR2(150),
96 ATTRIBUTE13 VARCHAR2(150),
97 ATTRIBUTE14 VARCHAR2(150),
98 ATTRIBUTE15 VARCHAR2(150)
99 );
100
101 PROCEDURE get_dff_default_values
102 (
103 p_req_material_rec IN REQ_MATERIAL_REC_TYPE,
104 flex_fields_defaults OUT NOCOPY dff_default_values_type
105 );
106 -------------------------------------
107 -- End Local Procedures Declaration--
108 -------------------------------------
109
110 PROCEDURE Check_org_name_Or_Id
111 (p_organization_id IN NUMBER,
112 p_org_name IN VARCHAR2,
113 x_organization_id OUT NOCOPY NUMBER,
114 x_return_status OUT NOCOPY VARCHAR2,
115 x_error_msg_code OUT NOCOPY VARCHAR2
116 )
117 IS
118 BEGIN
119 IF (p_organization_id IS NOT NULL AND
120 p_organization_id <> FND_API.G_MISS_NUM)
121 THEN
122 SELECT organization_id
123 INTO x_organization_id
124 FROM HR_ALL_ORGANIZATION_UNITS
125 WHERE organization_id = p_organization_id;
126 ELSE
127 SELECT organization_id
128 INTO x_organization_id
129 FROM HR_ALL_ORGANIZATION_UNITS
130 WHERE NAME = p_org_name;
131 END IF;
132 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
133 EXCEPTION
134 WHEN NO_DATA_FOUND THEN
135 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
136 x_error_msg_code:= 'AHL_PP_ORG_ID_NOT_EXISTS';
137 WHEN TOO_MANY_ROWS THEN
138 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
139 x_error_msg_code:= 'AHL_PP_ORG_ID_NOT_EXISTS';
140 WHEN OTHERS THEN
141 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
142 RAISE;
143 END Check_org_name_Or_Id;
144 --
145 PROCEDURE Check_dept_desc_Or_Id
146 (p_organization_id IN NUMBER,
147 p_org_name IN VARCHAR2,
148 p_department_id IN NUMBER,
149 p_dept_description IN VARCHAR2,
150 x_department_id OUT NOCOPY NUMBER,
151 x_return_status OUT NOCOPY VARCHAR2,
152 x_error_msg_code OUT NOCOPY VARCHAR2
153 )
154 IS
155 BEGIN
156
157 IF (p_department_id IS NOT NULL AND
158 p_department_id <> FND_API.G_MISS_NUM)
159 THEN
160 SELECT department_id
161 INTO x_department_id
162 FROM BOM_DEPARTMENTS
163 WHERE organization_id = p_organization_id
164 AND department_id = p_department_id;
165 ELSE
166 --
167 SELECT department_id
168 INTO x_department_id
169 FROM BOM_DEPARTMENTS
170 WHERE organization_id = p_organization_id
171 AND description = p_dept_description;
172 END IF;
173 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
174 EXCEPTION
175 WHEN NO_DATA_FOUND THEN
176 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
177 x_error_msg_code:= 'AHL_PP_DEPT_ID_NOT_EXIST';
178 WHEN TOO_MANY_ROWS THEN
179 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
180 x_error_msg_code:= 'AHL_PP_DEPT_ID_NOT_EXIST';
181 WHEN OTHERS THEN
182 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
183 RAISE;
184 END Check_dept_desc_Or_Id;
185 --
186 PROCEDURE Get_visit_task_Id
187 (p_workorder_id IN NUMBER,
188 x_visit_task_id OUT NOCOPY NUMBER,
189 x_return_status OUT NOCOPY VARCHAR2,
190 x_error_msg_code OUT NOCOPY VARCHAR2
191 ) IS
192
193 BEGIN
194 IF (p_workorder_id IS NOT NULL AND
195 p_workorder_id <> FND_API.G_MISS_NUM) THEN
196
197 SELECT visit_task_id INTO x_visit_task_id
198 FROM AHL_WORKORDERS
199 WHERE workorder_id = p_workorder_id;
200 END IF;
201 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
202 IF G_DEBUG='Y' THEN
203 AHL_DEBUG_PUB.debug( 'XVISITTASK:'|| x_visit_task_id);
204 END IF;
205
206 EXCEPTION
207 WHEN NO_DATA_FOUND THEN
208 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
209 x_error_msg_code:= 'AHL_PP_TASK_ID_NOT_EXIST';
210 WHEN TOO_MANY_ROWS THEN
211 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
212 x_error_msg_code:= 'AHL_PP_TASK_ID_NOT_EXIST';
213 WHEN OTHERS THEN
214 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
215 RAISE;
216 END Get_visit_task_Id;
217 --
218 PROCEDURE Get_workorder_Id
219 (p_workorder_id IN NUMBER,
220 p_job_number IN VARCHAR2,
221 x_workorder_id OUT NOCOPY NUMBER,
222 x_return_status OUT NOCOPY VARCHAR2,
223 x_error_msg_code OUT NOCOPY VARCHAR2
224 ) IS
225 --
226 BEGIN
227 --
228 IF p_workorder_id IS NOT NULL THEN
229 --
230 SELECT workorder_id INTO x_workorder_id
231 FROM AHL_WORKORDERS
232 WHERE workorder_id = p_workorder_id;
233 ELSE
234 --
235 SELECT workorder_id INTO x_workorder_id
236 FROM AHL_WORKORDERS
237 WHERE workorder_name = p_job_number;
238 END IF;
239
240 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
241 IF G_DEBUG='Y' THEN
242 AHL_DEBUG_PUB.debug( 'XWOID:'|| x_workorder_id);
243 END IF;
244
245 EXCEPTION
246 WHEN NO_DATA_FOUND THEN
247 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
248 x_error_msg_code:= 'AHL_PP_WO_ID_NOT_EXIST';
249 WHEN TOO_MANY_ROWS THEN
250 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
251 x_error_msg_code:= 'AHL_PP_WO_ID_NOT_EXIST';
252 WHEN OTHERS THEN
253 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
254 RAISE;
255 END Get_workorder_Id;
256 --
257 PROCEDURE Get_workorder_operation_Id
258 (p_workorder_id IN NUMBER,
259 p_operation_sequence IN NUMBER,
260 x_workorder_operation_id OUT NOCOPY NUMBER,
261 x_return_status OUT NOCOPY VARCHAR2,
262 x_error_msg_code OUT NOCOPY VARCHAR2
263 ) IS
264
265 BEGIN
266 IF (p_workorder_id IS NOT NULL AND
267 p_operation_sequence IS NOT NULL) THEN
268 --
269 SELECT workorder_operation_id INTO x_workorder_operation_id
270 FROM AHL_WORKORDER_OPERATIONS
271 WHERE workorder_id = p_workorder_id
272 AND operation_sequence_num = p_operation_sequence;
273 --
274 END IF;
275 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
276
277 EXCEPTION
278 WHEN NO_DATA_FOUND THEN
279 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
280 x_error_msg_code:= 'AHL_PP_WO_OP_ID_NOT_EXIST';
281 WHEN TOO_MANY_ROWS THEN
282 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
283 x_error_msg_code:= 'AHL_PP_WO_OP_ID_NOT_EXIST';
284 WHEN OTHERS THEN
285 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
286 RAISE;
287 END Get_workorder_operation_Id;
288 --
289 PROCEDURE Get_inventory_item_Id
290 (p_inventory_item_id IN NUMBER,
291 p_concatenated_segments IN VARCHAR2,
292 p_organization_id IN NUMBER,
293 x_inventory_item_id OUT NOCOPY NUMBER,
294 x_return_status OUT NOCOPY VARCHAR2,
295 x_error_msg_code OUT NOCOPY VARCHAR2
296 ) IS
297
298 BEGIN
299 IF (p_inventory_item_id IS NOT NULL AND
300 p_inventory_item_id <> FND_API.G_MISS_NUM) THEN
301 --
302 SELECT inventory_item_id INTO x_inventory_item_id
303 FROM MTL_SYSTEM_ITEMS_KFV
304 WHERE inventory_item_id = p_inventory_item_id
305 AND organization_id = p_organization_id;
306 ELSE
307 --
308 SELECT inventory_item_id INTO x_inventory_item_id
309 FROM MTL_SYSTEM_ITEMS_KFV
310 WHERE concatenated_segments = p_concatenated_segments
311 AND organization_id = p_organization_id;
312 END IF;
313 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
314
315 EXCEPTION
316 WHEN NO_DATA_FOUND THEN
317 x_return_status:= Fnd_Api.G_RET_STS_ERROR;
318 x_error_msg_code:= 'AHL_PP_INV_ID_NOT_EXIST';
319 WHEN OTHERS THEN
320 x_return_status:= Fnd_Api.G_RET_STS_UNEXP_ERROR;
321 RAISE;
322 END Get_inventory_item_Id;
323 --
324 PROCEDURE Get_visit_task_details
325 ( p_visit_task_id IN NUMBER,
326 x_visit_id OUT NOCOPY NUMBER,
327 x_organization_id OUT NOCOPY NUMBER,
328 x_department_id OUT NOCOPY NUMBER,
329 x_project_task_id OUT NOCOPY NUMBER,
330 x_project_id OUT NOCOPY NUMBER
331 )
332 IS
333 CURSOR get_visit_task_cur (c_visit_task_id IN NUMBER)
334 IS
335 SELECT visit_id,
336 department_id,project_task_id
337 FROM ahl_visit_tasks_b
338 WHERE visit_task_id = c_visit_task_id;
339 --
340 CURSOR get_org_details_cur(c_visit_id IN NUMBER)
341 IS
342 SELECT organization_id,department_id,
343 project_id
344 FROM ahl_visits_b
345 WHERE visit_id = c_visit_id;
346 --
347 l_visit_id NUMBER;
348 l_department_id NUMBER;
349 l_vdepartment_id NUMBER;
350 l_project_task_id NUMBER;
351 l_project_id NUMBER;
352 l_organization_id NUMBER;
353 l_schedule_designator VARCHAR2(10);
354 --
355 BEGIN
356 OPEN get_visit_task_cur(p_visit_task_id);
357 FETCH get_visit_task_cur INTO l_visit_id,l_department_id,l_project_task_id;
358 CLOSE get_visit_task_cur;
359 IF l_visit_id IS NOT NULL THEN
360 OPEN get_org_details_cur (l_visit_id);
361 FETCH get_org_details_cur INTO l_organization_id, l_vdepartment_id,
362 l_project_id;
363 CLOSE get_org_details_cur;
364 END IF;
365 --Assign
366 x_organization_id := l_organization_id;
367 x_department_id := nvl(l_department_id,l_vdepartment_id);
368 x_visit_id := l_visit_id;
369 x_project_task_id := l_project_task_id;
370 x_project_id := l_project_id;
371 END Get_visit_task_details;
372 -- Insert procedure to create record in to schedule materials
373 PROCEDURE Insert_Row (
374 X_SCHEDULED_MATERIAL_ID IN NUMBER,
375 X_OBJECT_VERSION_NUMBER IN NUMBER,
376 X_INVENTORY_ITEM_ID IN VARCHAR2,
377 X_SCHEDULE_DESIGNATOR IN VARCHAR2,
378 X_VISIT_ID IN NUMBER,
379 X_VISIT_START_DATE IN DATE,
380 X_VISIT_TASK_ID IN NUMBER,
381 X_ORGANIZATION_ID IN NUMBER,
382 X_SCHEDULED_DATE IN DATE,
383 X_REQUEST_ID IN NUMBER,
384 X_REQUESTED_DATE IN DATE,
385 X_SCHEDULED_QUANTITY IN NUMBER,
386 X_PROCESS_STATUS IN NUMBER,
387 X_ERROR_MESSAGE IN VARCHAR2,
388 X_TRANSACTION_ID IN NUMBER,
389 X_UOM IN VARCHAR2,
390 X_RT_OPER_MATERIAL_ID IN NUMBER,
391 X_OPERATION_CODE IN VARCHAR2,
392 X_OPERATION_SEQUENCE IN NUMBER,
393 X_ITEM_GROUP_ID IN NUMBER,
394 X_REQUESTED_QUANTITY IN NUMBER,
395 X_PROGRAM_ID IN NUMBER,
396 X_PROGRAM_UPDATE_DATE IN DATE,
397 X_LAST_UPDATED_DATE IN DATE,
398 X_WORKORDER_OPERATION_ID IN NUMBER,
399 X_MATERIAL_REQUEST_TYPE IN VARCHAR2,
400 X_STATUS IN VARCHAR2,
401 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
402 X_ATTRIBUTE1 IN VARCHAR2,
403 X_ATTRIBUTE2 IN VARCHAR2,
404 X_ATTRIBUTE3 IN VARCHAR2,
405 X_ATTRIBUTE4 IN VARCHAR2,
406 X_ATTRIBUTE5 IN VARCHAR2,
407 X_ATTRIBUTE6 IN VARCHAR2,
408 X_ATTRIBUTE7 IN VARCHAR2,
409 X_ATTRIBUTE8 IN VARCHAR2,
410 X_ATTRIBUTE9 IN VARCHAR2,
411 X_ATTRIBUTE10 IN VARCHAR2,
412 X_ATTRIBUTE11 IN VARCHAR2,
413 X_ATTRIBUTE12 IN VARCHAR2,
414 X_ATTRIBUTE13 IN VARCHAR2,
415 X_ATTRIBUTE14 IN VARCHAR2,
416 X_ATTRIBUTE15 IN VARCHAR2,
417 X_CREATION_DATE IN DATE,
418 X_CREATED_BY IN NUMBER,
419 X_LAST_UPDATE_DATE IN DATE,
420 X_LAST_UPDATED_BY IN NUMBER,
421 X_LAST_UPDATE_LOGIN IN NUMBER
422 ) IS
423 BEGIN
424 INSERT INTO AHL_SCHEDULE_MATERIALS (
425 SCHEDULED_MATERIAL_ID,
426 OBJECT_VERSION_NUMBER,
427 LAST_UPDATE_DATE,
428 LAST_UPDATED_BY,
429 CREATION_DATE,
430 CREATED_BY,
431 LAST_UPDATE_LOGIN,
432 INVENTORY_ITEM_ID,
433 SCHEDULE_DESIGNATOR,
434 VISIT_ID,
435 VISIT_START_DATE,
436 VISIT_TASK_ID,
437 ORGANIZATION_ID,
438 SCHEDULED_DATE,
439 REQUEST_ID,
440 REQUESTED_DATE,
441 SCHEDULED_QUANTITY,
442 PROCESS_STATUS,
443 ERROR_MESSAGE,
444 TRANSACTION_ID,
445 UOM,
446 RT_OPER_MATERIAL_ID,
447 OPERATION_CODE,
448 OPERATION_SEQUENCE,
449 ITEM_GROUP_ID,
450 REQUESTED_QUANTITY,
451 PROGRAM_ID,
452 PROGRAM_UPDATE_DATE,
453 LAST_UPDATED_DATE,
454 WORKORDER_OPERATION_ID,
455 MATERIAL_REQUEST_TYPE,
456 STATUS,
457 ATTRIBUTE_CATEGORY,
458 ATTRIBUTE1,
459 ATTRIBUTE2,
460 ATTRIBUTE3,
461 ATTRIBUTE4,
462 ATTRIBUTE5,
463 ATTRIBUTE6,
464 ATTRIBUTE7,
465 ATTRIBUTE8,
466 ATTRIBUTE9,
467 ATTRIBUTE10,
468 ATTRIBUTE11,
469 ATTRIBUTE12,
470 ATTRIBUTE13,
471 ATTRIBUTE14,
472 ATTRIBUTE15
473 )
474 VALUES(
475 X_SCHEDULED_MATERIAL_ID,
476 X_OBJECT_VERSION_NUMBER,
477 X_LAST_UPDATE_DATE,
478 X_LAST_UPDATED_BY,
479 X_CREATION_DATE,
480 X_CREATED_BY,
481 X_LAST_UPDATE_LOGIN,
482 X_INVENTORY_ITEM_ID,
483 X_SCHEDULE_DESIGNATOR,
484 X_VISIT_ID,
485 X_VISIT_START_DATE,
486 X_VISIT_TASK_ID,
487 X_ORGANIZATION_ID,
488 X_SCHEDULED_DATE,
489 X_REQUEST_ID,
490 -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
491 trunc(X_REQUESTED_DATE),
492 X_SCHEDULED_QUANTITY,
493 X_PROCESS_STATUS,
494 X_ERROR_MESSAGE,
495 X_TRANSACTION_ID,
496 X_UOM,
497 X_RT_OPER_MATERIAL_ID,
498 X_OPERATION_CODE,
499 X_OPERATION_SEQUENCE,
500 X_ITEM_GROUP_ID,
501 X_REQUESTED_QUANTITY,
502 X_PROGRAM_ID,
503 X_PROGRAM_UPDATE_DATE,
504 X_LAST_UPDATED_DATE,
505 X_WORKORDER_OPERATION_ID,
506 X_MATERIAL_REQUEST_TYPE,
507 X_STATUS,
508 X_ATTRIBUTE_CATEGORY,
509 X_ATTRIBUTE1,
510 X_ATTRIBUTE2,
511 X_ATTRIBUTE3,
512 X_ATTRIBUTE4,
513 X_ATTRIBUTE5,
514 X_ATTRIBUTE6,
515 X_ATTRIBUTE7,
516 X_ATTRIBUTE8,
517 X_ATTRIBUTE9,
518 X_ATTRIBUTE10,
519 X_ATTRIBUTE11,
520 X_ATTRIBUTE12,
521 X_ATTRIBUTE13,
522 X_ATTRIBUTE14,
523 X_ATTRIBUTE15);
524 --
525 END Insert_Row;
526 --Update procedure to update record in schedule materials entity
527 PROCEDURE UPDATE_ROW (
528 X_SCHEDULED_MATERIAL_ID IN NUMBER,
529 X_OBJECT_VERSION_NUMBER IN NUMBER,
530 X_INVENTORY_ITEM_ID IN VARCHAR2,
531 X_SCHEDULE_DESIGNATOR IN VARCHAR2,
532 X_VISIT_ID IN NUMBER,
533 X_VISIT_START_DATE IN DATE,
534 X_VISIT_TASK_ID IN NUMBER,
535 X_ORGANIZATION_ID IN NUMBER,
536 X_SCHEDULED_DATE IN DATE,
537 X_REQUEST_ID IN NUMBER,
538 X_REQUESTED_DATE IN DATE,
539 X_SCHEDULED_QUANTITY IN NUMBER,
540 X_PROCESS_STATUS IN NUMBER,
541 X_ERROR_MESSAGE IN VARCHAR2,
542 X_TRANSACTION_ID IN NUMBER,
543 X_UOM IN VARCHAR2,
544 X_RT_OPER_MATERIAL_ID IN NUMBER,
545 X_OPERATION_CODE IN VARCHAR2,
546 X_OPERATION_SEQUENCE IN NUMBER,
547 X_ITEM_GROUP_ID IN NUMBER,
548 X_REQUESTED_QUANTITY IN NUMBER,
549 X_PROGRAM_ID IN NUMBER,
550 X_PROGRAM_UPDATE_DATE IN DATE,
551 X_LAST_UPDATED_DATE IN DATE,
552 X_ATTRIBUTE_CATEGORY IN VARCHAR2,
553 X_ATTRIBUTE1 IN VARCHAR2,
554 X_ATTRIBUTE2 IN VARCHAR2,
555 X_ATTRIBUTE3 IN VARCHAR2,
556 X_ATTRIBUTE4 IN VARCHAR2,
557 X_ATTRIBUTE5 IN VARCHAR2,
558 X_ATTRIBUTE6 IN VARCHAR2,
559 X_ATTRIBUTE7 IN VARCHAR2,
560 X_ATTRIBUTE8 IN VARCHAR2,
561 X_ATTRIBUTE9 IN VARCHAR2,
562 X_ATTRIBUTE10 IN VARCHAR2,
563 X_ATTRIBUTE11 IN VARCHAR2,
564 X_ATTRIBUTE12 IN VARCHAR2,
565 X_ATTRIBUTE13 IN VARCHAR2,
566 X_ATTRIBUTE14 IN VARCHAR2,
567 X_ATTRIBUTE15 IN VARCHAR2,
568 X_CREATION_DATE IN DATE,
569 X_CREATED_BY IN NUMBER,
570 X_LAST_UPDATE_DATE IN DATE,
571 X_LAST_UPDATED_BY IN NUMBER,
572 X_LAST_UPDATE_LOGIN IN NUMBER
573 ) IS
574 BEGIN
575 UPDATE AHL_SCHEDULE_MATERIALS SET
576 OBJECT_VERSION_NUMBER = X_OBJECT_VERSION_NUMBER + 1,
577 INVENTORY_ITEM_ID = X_INVENTORY_ITEM_ID,
578 SCHEDULE_DESIGNATOR = X_SCHEDULE_DESIGNATOR,
579 VISIT_ID = X_VISIT_ID,
580 VISIT_START_DATE = X_VISIT_START_DATE,
581 VISIT_TASK_ID = X_VISIT_TASK_ID,
582 ORGANIZATION_ID = X_ORGANIZATION_ID,
583 SCHEDULED_DATE = X_SCHEDULED_DATE,
584 REQUEST_ID = X_REQUEST_ID,
585 -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
586 REQUESTED_DATE = trunc(X_REQUESTED_DATE),
587 SCHEDULED_QUANTITY = X_SCHEDULED_QUANTITY,
588 PROCESS_STATUS = X_PROCESS_STATUS,
589 ERROR_MESSAGE = X_ERROR_MESSAGE,
590 TRANSACTION_ID = X_TRANSACTION_ID,
591 UOM = X_UOM,
592 RT_OPER_MATERIAL_ID = X_RT_OPER_MATERIAL_ID,
593 OPERATION_CODE = X_OPERATION_CODE,
594 OPERATION_SEQUENCE = X_OPERATION_SEQUENCE,
595 ITEM_GROUP_ID = X_ITEM_GROUP_ID,
596 REQUESTED_QUANTITY = X_REQUESTED_QUANTITY,
597 PROGRAM_ID = X_PROGRAM_ID,
598 PROGRAM_UPDATE_DATE = X_PROGRAM_UPDATE_DATE,
599 LAST_UPDATED_DATE = X_LAST_UPDATED_DATE,
600 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
601 ATTRIBUTE1 = X_ATTRIBUTE1,
602 ATTRIBUTE2 = X_ATTRIBUTE2,
603 ATTRIBUTE3 = X_ATTRIBUTE3,
604 ATTRIBUTE4 = X_ATTRIBUTE4,
605 ATTRIBUTE5 = X_ATTRIBUTE5,
606 ATTRIBUTE6 = X_ATTRIBUTE6,
607 ATTRIBUTE7 = X_ATTRIBUTE7,
608 ATTRIBUTE8 = X_ATTRIBUTE8,
609 ATTRIBUTE9 = X_ATTRIBUTE9,
610 ATTRIBUTE10 = X_ATTRIBUTE10,
611 ATTRIBUTE11 = X_ATTRIBUTE11,
612 ATTRIBUTE12 = X_ATTRIBUTE12,
613 ATTRIBUTE13 = X_ATTRIBUTE13,
614 ATTRIBUTE14 = X_ATTRIBUTE14,
615 ATTRIBUTE15 = X_ATTRIBUTE15,
616 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
617 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
618 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
619 WHERE SCHEDULED_MATERIAL_ID = X_SCHEDULED_MATERIAL_ID
620 AND OBJECT_VERSION_NUMBER=X_OBJECT_VERSION_NUMBER;
621 IF SQL%rowcount=0 THEN
622 Fnd_Message.SET_NAME('AHL','AHL_COM_RECORD_CHANGED');
623 Fnd_Msg_Pub.ADD;
624 END IF;
625 END UPDATE_ROW;
626 -- Delete procedure to remove record from schedule materials
627 PROCEDURE DELETE_ROW (
628 X_SCHEDULED_MATERIAL_ID IN NUMBER
629 ) IS
630 BEGIN
631 DELETE FROM AHL_SCHEDULE_MATERIALS
632 WHERE SCHEDULED_MATERIAL_ID = X_SCHEDULED_MATERIAL_ID;
633 END DELETE_ROW;
634 --
635 -- Start of Comments --
636 -- Procedure name : Create_Material_Reqst
637 -- Type : Private
638 -- Function : Validates Material Information and inserts records into
639 -- Schedule Material table for non routine jobs Calls AHL_WIP_JOB_PVT.
640 -- update_wip_job api
641 -- Pre-reqs :
642 -- Parameters :
643 --
644 -- Standard IN Parameters :
645 -- p_api_version IN NUMBER Required
646 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
647 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
648 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
649 -- p_interface_flag IN VARCHAR2,
650 --
651 -- Standard OUT Parameters :
652 -- x_return_status OUT VARCHAR2 Required
653 -- x_msg_count OUT NUMBER Required
654 -- x_msg_data OUT VARCHAR2 Required
655 --
656 -- Create Material Request Parameters:
657 -- p_x_req_material_tbl IN OUT NOCOPY Req_Material_Tbl_Type,
658 -- Contains material information to perform material reservation
659 --
660 -- Version :
661 -- Initial Version 1.0
662 --
663 -- End of Comments.
664
665 PROCEDURE Create_Material_Reqst (
666 p_api_version IN NUMBER,
667 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
668 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
669 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
670 p_interface_flag IN VARCHAR2 ,
671 p_x_req_material_tbl IN OUT NOCOPY Req_Material_Tbl_Type,
672 x_job_return_status OUT NOCOPY VARCHAR2,
673 x_return_status OUT NOCOPY VARCHAR2,
674 x_msg_count OUT NOCOPY NUMBER,
675 x_msg_data OUT NOCOPY VARCHAR2
676 )
677
678 IS
679 --Check for unique constraint
680 CURSOR Check_unique_cur(c_item_id IN NUMBER,
681 c_operation_id IN NUMBER,
682 c_org_id IN NUMBER,
683 c_sequence_id IN NUMBER)
684 IS
685 SELECT 1
686 FROM AHL_SCHEDULE_MATERIALS
687 WHERE inventory_item_id = c_item_id
688 AND workorder_operation_id = c_operation_id
689 AND organization_id = c_org_id
690 AND operation_sequence = c_sequence_id
691 AND requested_quantity <> 0
692 AND status IN ('ACTIVE','IN-SERVICE');
693 -- Get job number details
694 CURSOR Get_job_number(c_workorder_id IN NUMBER)
695 IS
696 SELECT workorder_name job_number,
697 wip_entity_id
698 FROM ahl_workorders
699 WHERE workorder_id = c_workorder_id;
700 -- Fix for bug# 6594189. Allow for statuses all statuses other than closed,
701 -- cancelled, parts hold etc.
702 --Check for status
703 CURSOR Check_wo_status_cur(c_workorder_id IN NUMBER)
704 IS
705 SELECT 1 FROM ahl_workorders
706 WHERE workorder_id = c_workorder_id
707 /*
708 AND (status_code = 3 or
709 status_code = 1);
710 */
711 AND status_code NOT IN ('12','5','6','7','17','22','19');
712
713 --Check for Route item
714 CURSOR Get_rt_mat_cur (c_visit_task_id IN NUMBER,
715 c_rt_oper_mat_id IN NUMBER)
716 IS
717 SELECT *
718 FROM ahl_schedule_materials
719 WHERE rt_oper_material_id = c_rt_oper_mat_id
720 AND visit_task_id = c_visit_task_id
721 AND requested_quantity <> 0
722 AND status IN ('ACTIVE','IN-SERVICE');
723 --Check to calidate for dates
724 CURSOR Get_sch_dates_cur(c_wo_operation_id IN NUMBER,
725 c_req_date IN DATE)
726 IS
727 SELECT 1
728 FROM ahl_workorder_operations_v
729 WHERE workorder_operation_id = c_wo_operation_id
730 AND c_req_date between trunc(scheduled_start_date)
731 and trunc(scheduled_end_date) ;
732 -- TO Get uom code for meaning
733 CURSOR Uom_cur (uom_mean IN VARCHAR2) IS
734 SELECT UOM_CODE
735 FROM MTL_UNITS_OF_MEASURE
736 WHERE UNIT_OF_MEASURE = uom_mean;
737 -- Get Primary Uom Code
738 CURSOR Primary_Uom_cur (c_item_id IN NUMBER,
739 c_org_id IN NUMBER) IS
740 SELECT primary_uom_code
741 FROM MTL_SYSTEM_ITEMS_VL
742 WHERE inventory_item_id = c_item_id
743 AND organization_id = c_org_id;
744 --Get operation sequnece
745 CURSOR Get_Operation_Seq_cur(c_operation_id IN NUMBER)
746 IS
747 SELECT operation_sequence_num
748 FROM ahl_workorder_operations
749 WHERE workorder_operation_id = c_operation_id;
750
751 -- sracha: added for bug# 6802777.
752 -- derive dept. from wip-operations.
753 CURSOR get_oper_dept(c_wip_entity_id IN NUMBER,
754 c_oper_seq_num IN NUMBER)
755 IS
756 SELECT wo.department_id
757 FROM WIP_OPERATIONS WO
758 WHERE wo.wip_entity_id = c_wip_entity_id
759 AND wo.operation_seq_num = c_oper_seq_num;
760
761 l_api_name CONSTANT VARCHAR2(30) := 'CREATE_MATERIAL_REQST';
762 l_api_version CONSTANT NUMBER := 1.0;
763 l_msg_count NUMBER;
764 l_return_status VARCHAR2(1);
765 l_msg_data VARCHAR2(2000);
766 l_dummy NUMBER;
767 l_junk NUMBER;
768 l_return_staus VARCHAR2(1);
769 --
770 l_visit_task_id NUMBER;
771 l_inventory_item_id NUMBER;
772 l_visit_id NUMBER;
773 l_organization_id NUMBER;
774 l_department_id NUMBER;
775 l_project_task_id NUMBER;
776 l_project_id NUMBER;
777 l_schedule_material_id NUMBER;
778 l_schedule_designator VARCHAR2(10);
779 l_workorder_id NUMBER;
780 l_workorder_name VARCHAR2(80);
781 l_wip_entity_id NUMBER;
782 l_workorder_operation_id NUMBER;
783 l_wo_organization_id NUMBER;
784 l_object_version_number NUMBER;
785 l_init_msg_list VARCHAR2(1) := FND_API.G_TRUE;
786 l_Req_Material_Tbl Req_Material_Tbl_Type;
787 l_default VARCHAR2(30);
788 l_wo_operation_txn_id NUMBER;
789 l_schedule_start_date DATE;
790 l_schedule_end_date DATE;
791 --
792 l_record_loaded VARCHAR2(1);
793 l_transaction_id NUMBER;
794 l_module_type VARCHAR2(10);
795 l_material_rec Get_rt_mat_cur%ROWTYPE;
796 j NUMBER;
797 l_mrp_net_flag NUMBER;
798
799 dff_default_values dff_default_values_type;
800
801 BEGIN
802 --------------------Initialize ----------------------------------
803 -- Standard Start of API savepoint
804 SAVEPOINT create_material_reqst;
805 -- Check if API is called in debug mode. If yes, enable debug.
806 IF G_DEBUG='Y' THEN
807 AHL_DEBUG_PUB.enable_debug;
808 END IF;
809 -- Debug info.
810 IF G_DEBUG='Y' THEN
811 AHL_DEBUG_PUB.debug( 'enter ahl_pp_materias_pvt. create material reqst','+PPMRP+');
812 AHL_DEBUG_PUB.debug( 'INTERAFCE FALG'||p_interface_flag);
813 AHL_DEBUG_PUB.debug( 'Total Number Records:'||p_x_req_material_tbl.COUNT);
814 END IF;
815 -- Standard call to check for call compatibility.
816 IF FND_API.to_boolean(l_init_msg_list)
817 THEN
818 FND_MSG_PUB.initialize;
819 END IF;
820 -- Initialize API return status to success
821 x_return_status := FND_API.G_RET_STS_SUCCESS;
822 -- Initialize message list if p_init_msg_list is set to TRUE.
823 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
824 p_api_version,
825 l_api_name,G_PKG_NAME)
826 THEN
827 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
828 END IF;
829
830 --------------------Start of API Body-----------------------------------
831 IF p_x_req_material_tbl.COUNT > 0 THEN
832 FOR i IN p_x_req_material_tbl.FIRST..p_x_req_material_tbl.LAST
833 LOOP
834 -- Value to ID Conversion
835 IF G_DEBUG='Y' THEN
836 AHL_DEBUG_PUB.debug( 'WOID 1:'||p_x_req_material_tbl(i).workorder_id);
837 AHL_DEBUG_PUB.debug( 'OPFLAG 1:'||p_x_req_material_tbl(i).operation_flag);
838 AHL_DEBUG_PUB.debug( 'ITEM:'||i||'-'||p_x_req_material_tbl(i).concatenated_segments);
839
840 END IF;
841
842 IF ( ( p_x_req_material_tbl(i).workorder_id IS NOT NULL AND
843 p_x_req_material_tbl(i).workorder_id <> FND_API.G_MISS_NUM ) OR
844 ( p_x_req_material_tbl(i).job_number IS NOT NULL AND
845 p_x_req_material_tbl(i).job_number <> FND_API.G_MISS_CHAR ) )
846 THEN
847 --
848 IF G_DEBUG='Y' THEN
849 AHL_DEBUG_PUB.debug( 'WOID 2:'||p_x_req_material_tbl(i).workorder_id);
850 END IF;
851 --
852 Get_workorder_id
853 (p_workorder_id => p_x_req_material_tbl(i).workorder_id,
854 p_job_number => p_x_req_material_tbl(i).job_number,
855 x_workorder_id => l_workorder_id,
856 x_return_status => l_return_status,
857 x_error_msg_code => l_msg_data);
858
859 IF NVL(l_return_status,'x') <> 'S'
860 THEN
861 Fnd_Message.SET_NAME('AHL','AHL_PP_WO_ORD_NOT_EXISTS');
862 Fnd_Msg_Pub.ADD;
863 END IF;
864 --
865 END IF;
866
867 -- rroy
868 -- ACL Changes
869 --Get Job Number
870 OPEN Get_job_number(p_x_req_material_tbl(i).workorder_id);
871 FETCH Get_job_number INTO l_workorder_name,l_wip_entity_id;
872 CLOSE Get_job_number;
873
874 l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked
875 (
876 p_workorder_id => nvl(p_x_req_material_tbl(i).workorder_id,l_workorder_id),
877 p_ue_id => NULL,
878 p_visit_id => NULL,
879 p_item_instance_id => NULL
880 );
881 IF l_return_status = FND_API.G_TRUE THEN
882 FND_MESSAGE.Set_Name('AHL', 'AHL_PP_CRT_MTL_UNTLCKD');
883 FND_MESSAGE.Set_Token('WO_NAME', l_workorder_name);
884 FND_MSG_PUB.ADD;
885 RAISE FND_API.G_EXC_ERROR;
886 END IF;
887
888 -- rroy
889 -- ACL Changes
890 --
891 p_x_req_material_tbl(i).workorder_id := nvl(p_x_req_material_tbl(i).workorder_id,l_workorder_id);
892 --Get visit task id
893 IF (p_x_req_material_tbl(i).workorder_id IS NOT NULL AND
894 p_x_req_material_tbl(i).workorder_id <> Fnd_Api.G_MISS_NUM )
895 THEN
896
897 Get_visit_task_Id
898 (p_workorder_id => p_x_req_material_tbl(i).workorder_id,
899 x_visit_task_id => l_visit_task_id,
900 x_return_status => l_return_status,
901 x_error_msg_code => l_msg_data);
902
903 IF NVL(l_return_status,'x') <> 'S'
904 THEN
905 Fnd_Message.SET_NAME('AHL','AHL_PP_VISIT_TASK_NOT_EXIST');
906 Fnd_Msg_Pub.ADD;
907
908 END IF;
909 END IF;
910 -- Assign
911 p_x_req_material_tbl(i).visit_task_id := l_visit_task_id;
912 IF G_DEBUG='Y' THEN
913 AHL_DEBUG_PUB.debug( 'VTID'||p_x_req_material_tbl(i).visit_task_id);
914 AHL_DEBUG_PUB.debug( 'Job Number:'||p_x_req_material_tbl(i).job_number);
915
916 END IF;
917 -- Validate for organization,department,project_task_id
918 Get_visit_task_details
919 ( p_visit_task_id => l_visit_task_id,
920 x_visit_id => l_visit_id,
921 x_organization_id => l_organization_id,
922 x_department_id => l_department_id,
923 x_project_task_id => l_project_task_id,
924 x_project_id => l_project_id
925 );
926 -- Validate for organization
927 IF l_organization_id IS NULL THEN
928 Fnd_Message.SET_NAME('AHL','AHL_PP_ORG_ID_NOT_EXISTS');
929 Fnd_Msg_Pub.ADD;
930 END IF;
931
932 -- rroy
933 -- ACL Changes
934 /*--Get Job Number
935 OPEN Get_job_number(p_x_req_material_tbl(i).workorder_id);
936 FETCH Get_job_number INTO l_workorder_name,l_wip_entity_id;
937 CLOSE Get_job_number;
938 */
939 -- rroy
940 -- ACL Changes
941
942 --Assign
943 p_x_req_material_tbl(i).job_number := l_workorder_name;
944
945 -- Validate for project task
946 IF (p_interface_flag = 'Y'OR p_interface_flag IS NULL) THEN
947 --Check for workorder status
948 --
949 OPEN Check_wo_status_cur(p_x_req_material_tbl(i).workorder_id);
950 FETCH Check_wo_status_cur INTO l_dummy;
951 IF Check_wo_status_cur%NOTFOUND THEN
952 --
953 Fnd_Message.SET_NAME('AHL','AHL_PP_WO_STATUS_INVALID');
954 Fnd_Msg_Pub.ADD;
955 END IF;
956 --
957 CLOSE Check_wo_status_cur;
958 --
959 END IF;--Condition for interface flag
960 --
961 p_x_req_material_tbl(i).organization_id := l_organization_id;
962 p_x_req_material_tbl(i).department_id := l_department_id;
963 p_x_req_material_tbl(i).visit_id := l_visit_id;
964 p_x_req_material_tbl(i).project_task_id := l_project_task_id;
965 p_x_req_material_tbl(i).project_id := l_project_id;
966 --
967
968 IF G_DEBUG='Y' THEN
969 AHL_DEBUG_PUB.debug( 'ORID'||p_x_req_material_tbl(i).organization_id);
970 AHL_DEBUG_PUB.debug( 'DEID'||p_x_req_material_tbl(i).department_id);
971 AHL_DEBUG_PUB.debug( 'PTID'||p_x_req_material_tbl(i).project_task_id);
972 AHL_DEBUG_PUB.debug( 'PJID'||p_x_req_material_tbl(i).project_id);
973 AHL_DEBUG_PUB.debug( 'CITEM:'||p_x_req_material_tbl(i).concatenated_segments);
974 END IF;
975 -- Convert concatenated segments to Item ID
976 IF (p_x_req_material_tbl(i).concatenated_segments IS NOT NULL AND
977 p_x_req_material_tbl(i).concatenated_segments <> Fnd_Api.G_MISS_CHAR ) OR
978 (p_x_req_material_tbl(i).inventory_item_id IS NOT NULL AND
979 p_x_req_material_tbl(i).inventory_item_id <> Fnd_Api.G_MISS_NUM) THEN
980
981 Get_inventory_item_Id
982 (p_inventory_item_id => p_x_req_material_tbl(i).inventory_item_id,
983 p_concatenated_segments => p_x_req_material_tbl(i).concatenated_segments,
984 p_organization_id => l_organization_id,
985 x_inventory_item_id => l_inventory_item_id,
986 x_return_status => l_return_status,
987 x_error_msg_code => l_msg_data);
988
989 IF NVL(l_return_status,'x') <> 'S'
990 THEN
991 Fnd_Message.SET_NAME('AHL','AHL_PP_INV_ORG_NOT_EXIST');
992 Fnd_Msg_Pub.ADD;
993 END IF;
994 ELSE
995 Fnd_Message.SET_NAME('AHL','AHL_PP_INV_ID_REQUIRED');
996 Fnd_Msg_Pub.ADD;
997
998 END IF;
999
1000 --Assign the returned value
1001 p_x_req_material_tbl(i).inventory_item_id := l_inventory_item_id;
1002 IF G_DEBUG='Y' THEN
1003 AHL_DEBUG_PUB.debug( 'IVID'||p_x_req_material_tbl(i).requested_quantity);
1004 END IF;
1005
1006 -- Validate for Requested Quantity
1007 IF (p_x_req_material_tbl(i).requested_quantity IS NULL OR
1008 p_x_req_material_tbl(i).requested_quantity = FND_API.G_MISS_NUM ) THEN
1009 Fnd_Message.SET_NAME('AHL','AHL_PP_REQ_QUANTITY_REQUIRED');
1010 Fnd_Msg_Pub.ADD;
1011 ELSIF (p_x_req_material_tbl(i).requested_quantity IS NOT NULL AND
1012 p_x_req_material_tbl(i).requested_quantity <> FND_API.G_MISS_NUM) THEN
1013 -- Fix for FP bug# 6642084. -- Allow 0 quantity.
1014 IF p_x_req_material_tbl(i).requested_quantity < 0 THEN
1015 Fnd_Message.SET_NAME('AHL','AHL_PP_REQ_QUANTITY_INVALID');
1016 Fnd_Msg_Pub.ADD;
1017 END IF;
1018 END IF;
1019 --
1020
1021 IF G_DEBUG='Y' THEN
1022 AHL_DEBUG_PUB.debug( 'RDATE1:'||p_x_req_material_tbl(i).requested_date);
1023 END IF;
1024 --
1025 IF (p_interface_flag = 'Y' OR p_interface_flag is null )THEN
1026
1027 -- Validate for Requested Date
1028 IF (p_x_req_material_tbl(i).requested_date IS NULL OR
1029 p_x_req_material_tbl(i).requested_date = FND_API.G_MISS_DATE ) THEN
1030 Fnd_Message.SET_NAME('AHL','AHL_PP_REQ_DATE_REQUIRED');
1031 Fnd_Msg_Pub.ADD;
1032 ELSIF (p_x_req_material_tbl(i).requested_date IS NOT NULL AND
1033 p_x_req_material_tbl(i).requested_date <> FND_API.G_MISS_DATE) THEN
1034 IF p_x_req_material_tbl(i).requested_date < trunc(SYSDATE) THEN
1035 Fnd_Message.SET_NAME('AHL','AHL_PP_REQ_DATE_GT_SYSD');
1036 Fnd_Msg_Pub.ADD;
1037 END IF;
1038 END IF;
1039 END IF;
1040 --
1041 IF G_DEBUG='Y' THEN
1042 AHL_DEBUG_PUB.debug( 'RDATE'||p_x_req_material_tbl(i).requested_date);
1043 AHL_DEBUG_PUB.debug( 'OSID'||p_x_req_material_tbl(i).operation_sequence);
1044 END IF;
1045 --Check for operation sequence
1046 IF( p_x_req_material_tbl(i).operation_sequence IS NULL OR
1047 p_x_req_material_tbl(i).operation_sequence = FND_API.G_MISS_NUM)
1048 THEN
1049 IF (p_x_req_material_tbl(i).workorder_operation_id IS NOT NULL AND
1050 p_x_req_material_tbl(i).workorder_operation_id <> FND_API.G_MISS_NUM)
1051 THEN
1052 --
1053 OPEN Get_Operation_Seq_cur(p_x_req_material_tbl(i).workorder_operation_id);
1054 FETCH Get_Operation_Seq_cur INTO p_x_req_material_tbl(i).operation_sequence;
1055 IF Get_Operation_Seq_cur%NOTFOUND THEN
1056 Fnd_Message.SET_NAME('AHL','AHL_PP_OPER_SEQ_REQD');
1057 Fnd_Msg_Pub.ADD;
1058 END IF;
1059 CLOSE Get_Operation_Seq_cur;
1060 END IF;
1061 END IF;
1062 --
1063 -- dbms_output.put_line( 'after fetch:'||p_x_req_material_tbl(i).operation_sequence);
1064 -- dbms_output.put_line( 'after fetch:'||p_x_req_material_tbl(i).workorder_operation_id);
1065 -- dbms_output.put_line( 'interface flag:'||p_interface_flag);
1066
1067 -- Check for workorder operation ID
1068 IF G_DEBUG='Y' THEN
1069 AHL_DEBUG_PUB.debug('OPID'||p_x_req_material_tbl(i).workorder_operation_id);
1070 END IF;
1071
1072 --
1073 IF (p_x_req_material_tbl(i).workorder_operation_id IS NULL OR
1074 p_x_req_material_tbl(i).workorder_operation_id = FND_API.G_MISS_NUM)
1075 THEN
1076 -- Validate for workorder operation
1077 IF (p_x_req_material_tbl(i).operation_sequence IS NOT NULL AND
1078 p_x_req_material_tbl(i).operation_sequence <> FND_API.G_MISS_NUM) THEN
1079 --
1080 Get_workorder_operation_Id
1081 (p_workorder_id => p_x_req_material_tbl(i).workorder_id,
1082 p_operation_sequence => p_x_req_material_tbl(i).operation_sequence,
1083 x_workorder_operation_id => l_workorder_operation_id,
1084 x_return_status => l_return_status,
1085 x_error_msg_code => l_msg_data);
1086
1087 IF NVL(l_return_status,'x') <> 'S'
1088 THEN
1089 Fnd_Message.SET_NAME('AHL','AHL_PP_WO_OP_ID_NOT_EXIST');
1090 Fnd_Msg_Pub.ADD;
1091 --
1092 END IF;
1093 END IF;
1094 END IF;
1095 -- Assigns operation id when called from UI
1096 IF (p_x_req_material_tbl(i).workorder_operation_id <> FND_API.G_MISS_NUM OR
1097 p_x_req_material_tbl(i).workorder_operation_id IS NULL )
1098 THEN
1099 --
1100 p_x_req_material_tbl(i).workorder_operation_id := NVL(p_x_req_material_tbl(i).workorder_operation_id,l_workorder_operation_id);
1101 ELSE
1102 p_x_req_material_tbl(i).workorder_operation_id := l_workorder_operation_id;
1103 END IF;
1104 --
1105 /* sracha: Fix bug#6594189.
1106 * commented out date validation against workorder scheduled dates.
1107 * we should allow creation of material requirements irrespective of
1108 * workorder
1109 * scheduled dates. Note: This validation is triggered only when creating
1110 * material requirement.
1111 IF (
1112 (
1113 p_interface_flag = 'Y' or p_interface_flag IS NULL
1114 )
1115 AND
1116 (
1117 -- Check added by balaji for bug # 4093650
1118 -- When workorder_operation_id is null or g_miss then date check should not
1119 -- be performed.
1120 p_x_req_material_tbl(i).workorder_operation_id IS NOT NULL AND
1121 p_x_req_material_tbl(i).workorder_operation_id <> FND_API.G_MISS_NUM
1122 )
1123 )
1124 THEN
1125 --Check for requested date should be in schedule start date schedule end date
1126 OPEN Get_sch_dates_cur(p_x_req_material_tbl(i).workorder_operation_id,
1127 trunc(p_x_req_material_tbl(i).requested_date)) ;
1128 FETCH Get_sch_dates_cur INTO l_dummy;
1129 --
1130 IF Get_sch_dates_cur%NOTFOUND THEN
1131 Fnd_Message.SET_NAME('AHL','AHL_PP_RE_DATE_SCH_DATE');
1132 Fnd_Msg_Pub.ADD;
1133 END IF;
1134 --
1135 CLOSE Get_sch_dates_cur;
1136 --
1137 END IF;
1138 */
1139
1140 IF G_DEBUG='Y' THEN
1141 AHL_DEBUG_PUB.debug('Local OPID:'||l_workorder_operation_id);
1142 AHL_DEBUG_PUB.debug('OPID2 :'||p_x_req_material_tbl(i).workorder_operation_id);
1143 END IF;
1144 --Check for operation sequence
1145 -- dbms_output.put_line( 'before uinque check:'||p_x_req_material_tbl(i).inventory_item_id);
1146 -- dbms_output.put_line( 'before unique check:'||p_x_req_material_tbl(i).organization_id);
1147 -- dbms_output.put_line( 'before uinque check:'||p_x_req_material_tbl(i).workorder_operation_id);
1148 -- dbms_output.put_line( 'before unique check:'||p_x_req_material_tbl(i).operation_sequence);
1149 -- dbms_output.put_line( 'before unique check:'||p_x_req_material_tbl(i).requested_date);
1150
1151 --Check for record exists in schedule materials entity
1152 OPEN Check_unique_cur(p_x_req_material_tbl(i).inventory_item_id,
1153 p_x_req_material_tbl(i).workorder_operation_id,
1154 p_x_req_material_tbl(i).organization_id,
1155 p_x_req_material_tbl(i).operation_sequence);
1156 FETCH Check_unique_cur INTO l_dummy;
1157 --
1158 IF Check_unique_cur%FOUND THEN
1159 Fnd_Message.SET_NAME('AHL','AHL_MAT_RECORD_EXIST');
1160 FND_MESSAGE.SET_TOKEN('ITEM',p_x_req_material_tbl(i).concatenated_segments,false);
1161 Fnd_Msg_Pub.ADD;
1162
1163 END IF;
1164 --
1165 CLOSE Check_unique_cur;
1166 --
1167 --dbms_output.put_line( 'before uom conversion:'||p_x_req_material_tbl(i).inventory_item_id);
1168 -- dbms_output.put_line( 'before uom conversion:'||p_x_req_material_tbl(i).organization_id);
1169 -- dbms_output.put_line( 'before uom WO:'||p_x_req_material_tbl(i).workorder_id);
1170
1171 -- Convert Uom code
1172 IF (p_x_req_material_tbl(i).UOM_MEANING IS NOT NULL AND p_x_req_material_tbl(i).UOM_MEANING <> FND_API.G_MISS_CHAR)
1173 THEN
1174 --
1175 OPEN Uom_cur(p_x_req_material_tbl(i).UOM_MEANING);
1176 FETCH Uom_cur INTO p_x_req_material_tbl(i).UOM_CODE;
1177 CLOSE Uom_cur;
1178 -- Get the primary UOM
1179 ELSE
1180 OPEN Primary_Uom_cur(p_x_req_material_tbl(i).inventory_item_id,
1181 p_x_req_material_tbl(i).organization_id);
1182 FETCH Primary_Uom_cur INTO p_x_req_material_tbl(i).uom_code;
1183 CLOSE Primary_Uom_cur;
1184
1185 END IF;
1186
1187 -- OGMA issue # 105 - begin
1188 IF (
1189 p_x_req_material_tbl(i).REPAIR_ITEM IS NOT NULL AND
1190 p_x_req_material_tbl(i).REPAIR_ITEM = 'Y'
1191 )
1192 THEN
1193 p_x_req_material_tbl(i).STATUS := 'IN-SERVICE';
1194 END IF;
1195 -- OGMA issue # 105 - end
1196
1197 -- dbms_output.put_line( 'after uom conversion UOM:'||p_x_req_material_tbl(i).uom_code);
1198
1199 -- Standard call to get message count and if count is get message info.
1200 l_msg_count := FND_MSG_PUB.count_msg;
1201 IF l_msg_count > 0 THEN
1202 X_msg_count := l_msg_count;
1203 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1204 RAISE FND_API.G_EXC_ERROR;
1205 END IF;
1206 END LOOP;
1207 END IF;
1208
1209 -- dbms_output.put_line( 'before wip jobs:'||p_x_req_material_tbl.COUNT);
1210 -- dbms_output.put_line( 'before wip jobs flag:'||p_interface_flag);
1211
1212 -- Calling Wip job api
1213 IF (p_interface_flag = 'Y' OR p_interface_flag IS NULL )THEN
1214 --
1215 -- dbms_output.put_line( 'inside:'||p_interface_flag);
1216
1217 -- IF G_DEBUG='Y' THEN
1218 -- AHL_DEBUG_PUB.debug('after interface flag yes or null call:'||p_x_req_material_tbl(1).workorder_id);
1219 -- END IF;
1220 --
1221 -- dbms_output.put_line( 'inside:'||p_interface_flag);
1222
1223 IF p_x_req_material_tbl.COUNT >0
1224 THEN
1225 j := 1;
1226 FOR i in p_x_req_material_tbl.FIRST..p_x_req_material_tbl.LAST
1227 LOOP
1228 --
1229 OPEN Get_job_number(p_x_req_material_tbl(i).workorder_id);
1230 FETCH Get_job_number INTO l_workorder_name,l_wip_entity_id;
1231 CLOSE Get_job_number;
1232 --
1233 -- sracha: Fix for FP bug# 6802777.
1234 -- derive dept. ID for the operation.
1235 OPEN get_oper_dept(l_wip_entity_id, p_x_req_material_tbl(i).operation_sequence);
1236 FETCH get_oper_dept INTO l_department_id;
1237 IF (get_oper_dept%FOUND) THEN
1238 p_x_req_material_tbl(i).department_id := l_department_id;
1239 END IF;
1240 CLOSE get_oper_dept;
1241 --
1242 l_req_material_tbl(j).JOB_NUMBER := l_workorder_name;
1243 l_req_material_tbl(j).WIP_ENTITY_ID := l_wip_entity_id;
1244 l_req_material_tbl(j).WORKORDER_ID := p_x_req_material_tbl(i).workorder_id;
1245 l_req_material_tbl(j).OPERATION_SEQUENCE :=p_x_req_material_tbl(i).operation_sequence;
1246 l_req_material_tbl(j).INVENTORY_ITEM_ID :=p_x_req_material_tbl(i).inventory_item_id;
1247 l_req_material_tbl(j).UOM_CODE :=p_x_req_material_tbl(i).uom_code;
1248 l_req_material_tbl(j).ORGANIZATION_ID :=p_x_req_material_tbl(i).organization_id;
1249 -- fix for bug# 5549135.
1250 --l_req_material_tbl(j).MRP_NET_FLAG :=1;
1251 l_req_material_tbl(j).MRP_NET_FLAG :=2;
1252 l_req_material_tbl(j).QUANTITY_PER_ASSEMBLY :=p_x_req_material_tbl(i).requested_quantity;
1253 l_req_material_tbl(j).REQUESTED_QUANTITY :=p_x_req_material_tbl(i).requested_quantity;
1254 l_req_material_tbl(j).SUPPLY_TYPE :=NULL;
1255 l_req_material_tbl(j).LOCATION :=NULL;
1256 l_req_material_tbl(j).SUB_INVENTORY :=NULL;
1257 l_req_material_tbl(j).REQUESTED_DATE :=p_x_req_material_tbl(i).requested_date;
1258 l_req_material_tbl(j).OPERATION_FLAG :='C';
1259 -- sracha: Fix for FP bug# 6802777.
1260 l_req_material_tbl(j).DEPARTMENT_ID := p_x_req_material_tbl(i).department_id;
1261 --
1262 IF G_DEBUG='Y' THEN
1263 AHL_DEBUG_PUB.debug('Before Eam job pvt.InentoryItemID:'||l_req_material_tbl(j).INVENTORY_ITEM_ID);
1264 AHL_DEBUG_PUB.debug('Before Eam job pvt.Quantity:'||l_req_material_tbl(j).REQUESTED_QUANTITY);
1265 AHL_DEBUG_PUB.debug('Before Eam job pvt.Uom:'||l_req_material_tbl(j).UOM_CODE);
1266 AHL_DEBUG_PUB.debug('Before Eam job pvt.WorkorderID:'||l_req_material_tbl(j).WORKORDER_ID);
1267 AHL_DEBUG_PUB.debug('Before Eam job pvt.WipentityID:'||l_req_material_tbl(j).WIP_ENTITY_ID);
1268 AHL_DEBUG_PUB.debug('Before Eam job pvt.OrganizationID:'||l_req_material_tbl(j).ORGANIZATION_ID);
1269 AHL_DEBUG_PUB.debug('Before Eam job pvt.Jobmumber:'||l_req_material_tbl(j).JOB_NUMBER);
1270 AHL_DEBUG_PUB.debug('Before Eam job pvt.OperationSequence:'||l_req_material_tbl(j).OPERATION_SEQUENCE);
1271 END IF;
1272
1273 -- dbms_output.put_line( 'Before Eam job pvt.InentoryItemID:'||l_req_material_tbl(j).INVENTORY_ITEM_ID);
1274 -- dbms_output.put_line( 'Before Eam job pvt.quantity:'||l_req_material_tbl(j).REQUESTED_QUANTITY);
1275 -- dbms_output.put_line( 'Before Eam job pvt.uom:'||l_req_material_tbl(j).UOM_CODE);
1276 -- dbms_output.put_line( 'Before Eam job pvt.workorderID:'||l_req_material_tbl(j).WORKORDER_ID);
1277 -- dbms_output.put_line( 'Before Eam job pvt.wip entity:'||l_req_material_tbl(j).WIP_ENTITY_ID);
1278 -- dbms_output.put_line( 'Before Eam job pvt.OPERATION SEQ:'||l_req_material_tbl(j).OPERATION_SEQUENCE);
1279 -- dbms_output.put_line( 'Before Eam job pvt.date:'||l_req_material_tbl(j).REQUESTED_DATE);
1280
1281 j := j+1;
1282 --
1283 END LOOP;
1284 END IF; --Material tbl
1285 --
1286 -- dbms_output.put_line( 'before wip jobs:');
1287
1288 IF G_DEBUG='Y' THEN
1289 AHL_DEBUG_PUB.debug('before wip job call');
1290 END IF;
1291 -- Before Ahl Eam job Call
1292
1293 AHL_EAM_JOB_PVT.process_material_req
1294 (
1295 p_api_version => l_api_version,
1296 p_init_msg_list => l_init_msg_list,
1297 p_commit => p_commit,
1298 p_validation_level => p_validation_level,
1299 p_default => l_default,
1300 p_module_type => l_module_type,
1301 x_return_status => l_return_status,
1302 x_msg_count => l_msg_count,
1303 x_msg_data => l_msg_data,
1304 p_material_req_tbl => l_req_material_tbl);
1305
1306
1307 -- dbms_output.put_line( 'after wip jobs:'||l_return_status);
1308
1309 IF G_DEBUG='Y' THEN
1310 AHL_DEBUG_PUB.debug('after wip job call:'||l_return_status);
1311 AHL_DEBUG_PUB.debug('after wip job call:'||l_msg_count);
1312 AHL_DEBUG_PUB.debug('after wip job call:'||l_msg_data);
1313 END IF;
1314 --
1315 l_msg_count := FND_MSG_PUB.count_msg;
1316 --
1317 IF l_msg_count > 0 THEN
1318 X_msg_count := l_msg_count;
1319 X_msg_data := l_msg_data;
1320 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1321 RAISE FND_API.G_EXC_ERROR;
1322 END IF;
1323
1324 END IF;
1325 --
1326 IF G_DEBUG='Y' THEN
1327 AHL_DEBUG_PUB.debug('Before insert status call');
1328 END IF;
1329 -- dbms_output.put_line( 'after wip jobs:');
1330
1331 --
1332 IF l_return_status ='S' THEN
1333 --Change made on Nov 17, 2005 by jeli due to bug 4742895.
1334 --Ignore messages in stack if return status is S after calls to EAM APIs.
1335 FND_MSG_PUB.initialize;
1336
1337 -- dbms_output.put_line( 'inside return status success:');
1338
1339 IF p_x_req_material_tbl.COUNT > 0 THEN
1340 --
1341 FOR i IN p_x_req_material_tbl.FIRST..p_x_req_material_tbl.LAST
1342 LOOP
1343 --
1344 IF (p_x_req_material_tbl(i).schedule_material_id = FND_API.G_MISS_NUM OR
1345 p_x_req_material_tbl(i).schedule_material_id IS NULL )
1346 THEN
1347 -- Thease conditions are required for optional fields
1348 IF p_x_req_material_tbl(i).visit_start_date = FND_API.G_MISS_DATE
1349 THEN
1350 l_Req_Material_Tbl(i).visit_start_date := NULL;
1351 ELSE
1352 l_Req_Material_Tbl(i).visit_start_date := p_x_req_material_tbl(i).visit_start_date;
1353 END IF;
1354 -- Scheduled Date
1355 IF p_x_req_material_tbl(i).scheduled_date = FND_API.G_MISS_DATE
1356 THEN
1357 l_Req_Material_Tbl(i).scheduled_date := NULL;
1358 ELSE
1359 l_Req_Material_Tbl(i).scheduled_date := p_x_req_material_tbl(i).scheduled_date;
1360 END IF;
1361 -- Request ID
1362 IF p_x_req_material_tbl(i).request_id = FND_API.G_MISS_NUM
1363 THEN
1364 l_Req_Material_Tbl(i).request_id := NULL;
1365 ELSE
1366 l_Req_Material_Tbl(i).request_id := p_x_req_material_tbl(i).request_id;
1367 END IF;
1368 --Scheduled quantity
1369 IF p_x_req_material_tbl(i).scheduled_quantity = FND_API.G_MISS_NUM
1370 THEN
1371 l_Req_Material_Tbl(i).scheduled_quantity := NULL;
1372 ELSE
1373 l_Req_Material_Tbl(i).scheduled_quantity := p_x_req_material_tbl(i).scheduled_quantity;
1374 END IF;
1375 -- Operation Sequence
1376 IF p_x_req_material_tbl(i).operation_sequence = FND_API.G_MISS_NUM
1377 THEN
1378 l_Req_Material_Tbl(i).operation_sequence := NULL;
1379 ELSE
1380 l_Req_Material_Tbl(i).operation_sequence := p_x_req_material_tbl(i).operation_sequence;
1381 END IF;
1382 -- UOM
1383 IF p_x_req_material_tbl(i).uom_code = FND_API.G_MISS_CHAR
1384 THEN
1385 l_Req_Material_Tbl(i).uom_code := NULL;
1386 ELSE
1387 l_Req_Material_Tbl(i).uom_code := p_x_req_material_tbl(i).uom_code;
1388 END IF;
1389 -- Status
1390 IF p_x_req_material_tbl(i).status = FND_API.G_MISS_CHAR
1391 THEN
1392 l_Req_Material_Tbl(i).status := NULL;
1393 ELSE
1394 l_Req_Material_Tbl(i).status := p_x_req_material_tbl(i).status;
1395 END IF;
1396 -- Operation code
1397 IF p_x_req_material_tbl(i).operation_code = FND_API.G_MISS_CHAR
1398 THEN
1399 l_Req_Material_Tbl(i).operation_code := NULL;
1400 ELSE
1401 l_Req_Material_Tbl(i).operation_code := p_x_req_material_tbl(i).operation_code;
1402 END IF;
1403 -- Transaction ID
1404 IF p_x_req_material_tbl(i).transaction_id = FND_API.G_MISS_NUM
1405 THEN
1406 l_Req_Material_Tbl(i).transaction_id := NULL;
1407 ELSE
1408 l_Req_Material_Tbl(i).transaction_id := p_x_req_material_tbl(i).transaction_id;
1409 END IF;
1410 -- Rt Oper Material ID
1411 IF p_x_req_material_tbl(i).rt_oper_material_id = FND_API.G_MISS_NUM
1412 THEN
1413 l_Req_Material_Tbl(i).rt_oper_material_id := NULL;
1414 ELSE
1415 l_Req_Material_Tbl(i).rt_oper_material_id := p_x_req_material_tbl(i).rt_oper_material_id;
1416 END IF;
1417 -- Program ID
1418 IF p_x_req_material_tbl(i).program_id = FND_API.G_MISS_NUM
1419 THEN
1420 l_Req_Material_Tbl(i).program_id := NULL;
1421 ELSE
1422 l_Req_Material_Tbl(i).program_id := p_x_req_material_tbl(i).program_id;
1423 END IF;
1424 -- Item group ID
1425 IF p_x_req_material_tbl(i).item_group_id = FND_API.G_MISS_NUM
1426 THEN
1427 l_Req_Material_Tbl(i).item_group_id := NULL;
1428 ELSE
1429 l_Req_Material_Tbl(i).item_group_id := p_x_req_material_tbl(i).item_group_id;
1430 END IF;
1431 -- Program Update Date
1432 IF p_x_req_material_tbl(i).program_update_date = FND_API.G_MISS_DATE
1433 THEN
1434 l_Req_Material_Tbl(i).program_update_date := NULL;
1435 ELSE
1436 l_Req_Material_Tbl(i).program_update_date := p_x_req_material_tbl(i).program_update_date;
1437 END IF;
1438 -- Last Updated Date
1439 IF p_x_req_material_tbl(i).last_updated_date = FND_API.G_MISS_DATE
1440 THEN
1441 l_Req_Material_Tbl(i).last_updated_date := NULL;
1442 ELSE
1443 l_Req_Material_Tbl(i).last_updated_date := p_x_req_material_tbl(i).last_updated_date;
1444 END IF;
1445
1446 IF G_DEBUG='Y' THEN
1447 AHL_DEBUG_PUB.debug('fetching dff_default_values');
1448 END IF;
1449
1450 get_dff_default_values
1451 (
1452 p_req_material_rec => p_x_req_material_tbl(i),
1453 flex_fields_defaults => dff_default_values
1454 );
1455 IF G_DEBUG='Y' THEN
1456 AHL_DEBUG_PUB.debug('dff_default_values have been fetched');
1457 END IF;
1458 -- Attribte Category
1459 IF p_x_req_material_tbl(i).attribute_category = FND_API.G_MISS_CHAR
1460 THEN
1461 l_Req_Material_Tbl(i).attribute_category := NULL;
1462 ELSIF p_x_req_material_tbl(i).attribute_category IS NULL THEN
1463 l_Req_Material_Tbl(i).attribute_category := dff_default_values.attribute_category;
1464 ELSE
1465 l_Req_Material_Tbl(i).attribute_category := p_x_req_material_tbl(i).attribute_category;
1466 END IF;
1467 -- Attribte1
1468 IF p_x_req_material_tbl(i).attribute1 = FND_API.G_MISS_CHAR
1469 THEN
1470 l_Req_Material_Tbl(i).attribute1 := NULL;
1471 ELSIF p_x_req_material_tbl(i).attribute1 IS NULL THEN
1472 l_Req_Material_Tbl(i).attribute1 := dff_default_values.attribute1;
1473 ELSE
1474 l_Req_Material_Tbl(i).attribute1 := p_x_req_material_tbl(i).attribute1;
1475 END IF;
1476 -- Attribte2
1477 IF p_x_req_material_tbl(i).attribute2 = FND_API.G_MISS_CHAR
1478 THEN
1479 l_Req_Material_Tbl(i).attribute2 := NULL;
1480 ELSIF p_x_req_material_tbl(i).attribute2 IS NULL THEN
1481 l_Req_Material_Tbl(i).attribute1 := dff_default_values.attribute2;
1482 ELSE
1483 l_Req_Material_Tbl(i).attribute2 := p_x_req_material_tbl(i).attribute2;
1484 END IF;
1485 -- Attribte3
1486 IF p_x_req_material_tbl(i).attribute3 = FND_API.G_MISS_CHAR
1487 THEN
1488 l_Req_Material_Tbl(i).attribute3 := NULL;
1489 ELSIF p_x_req_material_tbl(i).attribute3 IS NULL THEN
1490 l_Req_Material_Tbl(i).attribute1 := dff_default_values.attribute3;
1491 ELSE
1492 l_Req_Material_Tbl(i).attribute3 := p_x_req_material_tbl(i).attribute3;
1493 END IF;
1494 -- Attribte4
1495 IF p_x_req_material_tbl(i).attribute4 = FND_API.G_MISS_CHAR
1496 THEN
1497 l_Req_Material_Tbl(i).attribute4 := NULL;
1498 ELSIF p_x_req_material_tbl(i).attribute4 IS NULL THEN
1499 l_Req_Material_Tbl(i).attribute1 := dff_default_values.attribute4;
1500 ELSE
1501 l_Req_Material_Tbl(i).attribute4 := p_x_req_material_tbl(i).attribute4;
1502 END IF;
1503 -- Attribte5
1504 IF p_x_req_material_tbl(i).attribute5 = FND_API.G_MISS_CHAR
1505 THEN
1506 l_Req_Material_Tbl(i).attribute5 := NULL;
1507 ELSIF p_x_req_material_tbl(i).attribute5 IS NULL THEN
1508 l_Req_Material_Tbl(i).attribute1 := dff_default_values.attribute5;
1509 ELSE
1510 l_Req_Material_Tbl(i).attribute5 := p_x_req_material_tbl(i).attribute5;
1511 END IF;
1512 -- Attribte6
1513 IF p_x_req_material_tbl(i).attribute6 = FND_API.G_MISS_CHAR
1514 THEN
1515 l_Req_Material_Tbl(i).attribute6 := NULL;
1516 ELSIF p_x_req_material_tbl(i).attribute6 IS NULL THEN
1517 l_Req_Material_Tbl(i).attribute1 := dff_default_values.attribute6;
1518 ELSE
1519 l_Req_Material_Tbl(i).attribute6 := p_x_req_material_tbl(i).attribute6;
1520 END IF;
1521 -- Attribte7
1522 IF p_x_req_material_tbl(i).attribute7 = FND_API.G_MISS_CHAR
1523 THEN
1524 l_Req_Material_Tbl(i).attribute7 := NULL;
1525 ELSIF p_x_req_material_tbl(i).attribute7 IS NULL THEN
1526 l_Req_Material_Tbl(i).attribute1 := dff_default_values.attribute7;
1527 ELSE
1528 l_Req_Material_Tbl(i).attribute7 := p_x_req_material_tbl(i).attribute7;
1529 END IF;
1530 -- Attribte8
1531 IF p_x_req_material_tbl(i).attribute8 = FND_API.G_MISS_CHAR
1532 THEN
1533 l_Req_Material_Tbl(i).attribute8 := NULL;
1534 ELSIF p_x_req_material_tbl(i).attribute8 IS NULL THEN
1535 l_Req_Material_Tbl(i).attribute1 := dff_default_values.attribute8;
1536 ELSE
1537 l_Req_Material_Tbl(i).attribute8 := p_x_req_material_tbl(i).attribute8;
1538 END IF;
1539 -- Attribte9
1540 IF p_x_req_material_tbl(i).attribute9 = FND_API.G_MISS_CHAR
1541 THEN
1542 l_Req_Material_Tbl(i).attribute9 := NULL;
1543 ELSIF p_x_req_material_tbl(i).attribute9 IS NULL THEN
1544 l_Req_Material_Tbl(i).attribute1 := dff_default_values.attribute9;
1545 ELSE
1546 l_Req_Material_Tbl(i).attribute9 := p_x_req_material_tbl(i).attribute9;
1547 END IF;
1548 -- Attribte10
1549 IF p_x_req_material_tbl(i).attribute10 = FND_API.G_MISS_CHAR
1550 THEN
1551 l_Req_Material_Tbl(i).attribute10 := NULL;
1552 ELSIF p_x_req_material_tbl(i).attribute10 IS NULL THEN
1553 l_Req_Material_Tbl(i).attribute1 := dff_default_values.attribute10;
1554 ELSE
1555 l_Req_Material_Tbl(i).attribute10 := p_x_req_material_tbl(i).attribute10;
1556 END IF;
1557 -- Attribte11
1558 IF p_x_req_material_tbl(i).attribute11 = FND_API.G_MISS_CHAR
1559 THEN
1560 l_Req_Material_Tbl(i).attribute11 := NULL;
1561 ELSIF p_x_req_material_tbl(i).attribute11 IS NULL THEN
1562 l_Req_Material_Tbl(i).attribute1 := dff_default_values.attribute11;
1563 ELSE
1564 l_Req_Material_Tbl(i).attribute11 := p_x_req_material_tbl(i).attribute11;
1565 END IF;
1566 -- Attribte12
1567 IF p_x_req_material_tbl(i).attribute12 = FND_API.G_MISS_CHAR
1568 THEN
1569 l_Req_Material_Tbl(i).attribute12 := NULL;
1570 ELSIF p_x_req_material_tbl(i).attribute12 IS NULL THEN
1571 l_Req_Material_Tbl(i).attribute1 := dff_default_values.attribute12;
1572 ELSE
1573 l_Req_Material_Tbl(i).attribute12 := p_x_req_material_tbl(i).attribute12;
1574 END IF;
1575 -- Attribte13
1576 IF p_x_req_material_tbl(i).attribute13 = FND_API.G_MISS_CHAR
1577 THEN
1578 l_Req_Material_Tbl(i).attribute13 := NULL;
1579 ELSIF p_x_req_material_tbl(i).attribute13 IS NULL THEN
1580 l_Req_Material_Tbl(i).attribute1 := dff_default_values.attribute13;
1581 ELSE
1582 l_Req_Material_Tbl(i).attribute13 := p_x_req_material_tbl(i).attribute13;
1583 END IF;
1584 -- Attribte14
1585 IF p_x_req_material_tbl(i).attribute14 = FND_API.G_MISS_CHAR
1586 THEN
1587 l_Req_Material_Tbl(i).attribute14 := NULL;
1588 ELSIF p_x_req_material_tbl(i).attribute14 IS NULL THEN
1589 l_Req_Material_Tbl(i).attribute1 := dff_default_values.attribute14;
1590 ELSE
1591 l_Req_Material_Tbl(i).attribute14 := p_x_req_material_tbl(i).attribute14;
1592 END IF;
1593 -- Attribte15
1594 IF p_x_req_material_tbl(i).attribute15 = FND_API.G_MISS_CHAR
1595 THEN
1596 l_Req_Material_Tbl(i).attribute15 := NULL;
1597 ELSIF p_x_req_material_tbl(i).attribute15 IS NULL THEN
1598 l_Req_Material_Tbl(i).attribute1 := dff_default_values.attribute15;
1599 ELSE
1600 l_Req_Material_Tbl(i).attribute15 := p_x_req_material_tbl(i).attribute15;
1601 END IF;
1602 --
1603 -- Get Sequence Number for schedule material ID
1604 SELECT ahl_schedule_materials_s.NEXTVAL
1605 INTO l_schedule_material_id FROM DUAL;
1606 --
1607 --
1608 IF G_DEBUG='Y' THEN
1609 AHL_DEBUG_PUB.debug( 'before interface flag:'||p_x_req_material_tbl(i).rt_oper_material_id);
1610 AHL_DEBUG_PUB.debug( 'before interface flag:'||p_interface_flag);
1611
1612 END IF;
1613 --Check for materials added from route.Already in scheudle material entity
1614 -- were schedule during LTP process
1615 IF (p_interface_flag = 'N' or p_interface_flag = 'n') THEN
1616 --
1617 IF G_DEBUG='Y' THEN
1618 AHL_DEBUG_PUB.debug( 'FLAG=N RTID:'||p_x_req_material_tbl(i).rt_oper_material_id);
1619 AHL_DEBUG_PUB.debug( 'FLAG=N VTID:'||p_x_req_material_tbl(i).visit_task_id);
1620 AHL_DEBUG_PUB.debug( 'FLAG=N ITID:'||p_x_req_material_tbl(i).inventory_item_id);
1621 END IF;
1622 IF (p_x_req_material_tbl(i).rt_oper_material_id IS NOT NULL AND
1623 p_x_req_material_tbl(i).rt_oper_material_id <> FND_API.G_MISS_NUM)
1624 THEN
1625 --
1626 OPEN Get_rt_mat_cur (p_x_req_material_tbl(i).visit_task_id,
1627 p_x_req_material_tbl(i).rt_oper_material_id);
1628 FETCH Get_rt_mat_cur INTO l_material_rec;
1629 CLOSE Get_rt_mat_cur;
1630 --
1631 IF G_DEBUG='Y' THEN
1632 AHL_DEBUG_PUB.debug( 'Inside MATID:'||l_material_rec.scheduled_material_id);
1633 AHL_DEBUG_PUB.debug( 'inside VTID:'||l_material_rec.visit_task_id);
1634 AHL_DEBUG_PUB.debug( 'inside ITID:'||l_material_rec.inventory_item_id);
1635 AHL_DEBUG_PUB.debug( 'inside DES:'||l_schedule_designator);
1636 END IF;
1637 --
1638 IF ( l_material_rec.scheduled_material_id IS NOT NULL
1639 --Adithya added for FP Bug# 6366740
1640 AND p_x_req_material_tbl(i).workorder_operation_id = l_material_rec.workorder_operation_id )
1641 THEN
1642 -- UPDATE ahl schedule materials table with operation id, operation sequence
1643 UPDATE ahl_schedule_materials
1644 SET workorder_operation_id = p_x_req_material_tbl(i).workorder_operation_id,
1645 operation_code = p_x_req_material_tbl(i).operation_code,
1646 operation_sequence = p_x_req_material_tbl(i).operation_sequence,
1647 object_version_number =l_material_rec.object_version_number +1
1648 WHERE scheduled_material_id = l_material_rec.scheduled_material_id;
1649 --Assign out parameter
1650 p_x_req_material_tbl(i).schedule_material_id := l_material_rec.scheduled_material_id;
1651 p_x_req_material_tbl(i).requested_quantity := l_material_rec.requested_quantity;
1652 p_x_req_material_tbl(i).requested_date := l_material_rec.requested_date;
1653 p_x_req_material_tbl(i).uom_code := l_material_rec.uom;
1654 -- fix for bug# 5549135.
1655 --p_x_req_material_tbl(i).mrp_net_flag := 1;
1656 p_x_req_material_tbl(i).mrp_net_flag := 2;
1657
1658 IF G_DEBUG='Y' THEN
1659 AHL_DEBUG_PUB.debug( 'p_x_req_material_tbl(i).mrp_net_flag:'||p_x_req_material_tbl(i).mrp_net_flag);
1660 END IF;
1661
1662 ELSE
1663 IF G_DEBUG='Y' THEN
1664 AHL_DEBUG_PUB.debug( 'else flag Mat id:'||l_schedule_material_id);
1665 END IF;
1666 -- Create Record in schedule materials
1667 Insert_Row (
1668 X_SCHEDULED_MATERIAL_ID => l_schedule_material_id,
1669 X_OBJECT_VERSION_NUMBER => 1,
1670 X_INVENTORY_ITEM_ID => p_x_req_material_tbl(i).inventory_item_id,
1671 X_SCHEDULE_DESIGNATOR => l_schedule_designator,
1672 X_VISIT_ID => l_visit_id,
1673 X_VISIT_START_DATE => l_Req_Material_Tbl(i).visit_start_date,
1674 X_VISIT_TASK_ID => p_x_req_material_tbl(i).visit_task_id,
1675 X_ORGANIZATION_ID => p_x_req_material_tbl(i).organization_id,
1676 X_SCHEDULED_DATE => l_Req_Material_Tbl(i).scheduled_date,
1677 X_REQUEST_ID => l_Req_Material_Tbl(i).request_id,
1678 X_REQUESTED_DATE => p_x_req_material_tbl(i).requested_date,
1679 X_SCHEDULED_QUANTITY => l_Req_Material_Tbl(i).scheduled_quantity,
1680 X_PROCESS_STATUS => null,
1681 X_ERROR_MESSAGE => null,
1682 X_TRANSACTION_ID => l_Req_Material_Tbl(i).transaction_id,
1683 X_UOM => l_Req_Material_Tbl(i).uom_code,
1684 X_RT_OPER_MATERIAL_ID => l_Req_Material_Tbl(i).rt_oper_material_id,
1685 X_OPERATION_CODE => l_Req_Material_Tbl(i).operation_code,
1686 X_OPERATION_SEQUENCE => l_Req_Material_Tbl(i).operation_sequence,
1687 X_ITEM_GROUP_ID => l_Req_Material_Tbl(i).item_group_id,
1688 X_REQUESTED_QUANTITY => p_x_req_material_tbl(i).requested_quantity,
1689 X_PROGRAM_ID => l_Req_Material_Tbl(i).program_id,
1690 X_PROGRAM_UPDATE_DATE => l_Req_Material_Tbl(i).program_update_date,
1691 X_LAST_UPDATED_DATE => l_Req_Material_Tbl(i).last_updated_date,
1692 X_WORKORDER_OPERATION_ID => p_x_req_material_tbl(i).workorder_operation_id,
1693 X_MATERIAL_REQUEST_TYPE => 'UNPLANNED',
1694 X_STATUS => nvl(l_Req_Material_Tbl(i).status, 'ACTIVE'),
1695 X_ATTRIBUTE_CATEGORY => l_Req_Material_Tbl(i).attribute_category,
1696 X_ATTRIBUTE1 => l_Req_Material_Tbl(i).attribute1,
1697 X_ATTRIBUTE2 => l_Req_Material_Tbl(i).attribute2,
1698 X_ATTRIBUTE3 => l_Req_Material_Tbl(i).attribute3,
1699 X_ATTRIBUTE4 => l_Req_Material_Tbl(i).attribute4,
1700 X_ATTRIBUTE5 => l_Req_Material_Tbl(i).attribute5,
1701 X_ATTRIBUTE6 => l_Req_Material_Tbl(i).attribute6,
1702 X_ATTRIBUTE7 => l_Req_Material_Tbl(i).attribute7,
1703 X_ATTRIBUTE8 => l_Req_Material_Tbl(i).attribute8,
1704 X_ATTRIBUTE9 => l_Req_Material_Tbl(i).attribute9,
1705 X_ATTRIBUTE10 => l_Req_Material_Tbl(i).attribute10,
1706 X_ATTRIBUTE11 => l_Req_Material_Tbl(i).attribute11,
1707 X_ATTRIBUTE12 => l_Req_Material_Tbl(i).attribute12,
1708 X_ATTRIBUTE13 => l_Req_Material_Tbl(i).attribute13,
1709 X_ATTRIBUTE14 => l_Req_Material_Tbl(i).attribute14,
1710 X_ATTRIBUTE15 => l_Req_Material_Tbl(i).attribute15,
1711 X_CREATION_DATE => SYSDATE,
1712 X_CREATED_BY => fnd_global.user_id,
1713 X_LAST_UPDATE_DATE => SYSDATE,
1714 X_LAST_UPDATED_BY => fnd_global.user_id,
1715 X_LAST_UPDATE_LOGIN => fnd_global.login_id
1716 );
1717
1718 --Assign out parameter
1719 p_x_req_material_tbl(i).schedule_material_id := l_schedule_material_id;
1720 -- fix for bug# 5549135.
1721 --p_x_req_material_tbl(i).mrp_net_flag := 1;
1722 p_x_req_material_tbl(i).mrp_net_flag := 2;
1723 --
1724 END IF; --Get_rt_mat_cur
1725
1726 -- Get Project and Task id
1727 IF G_DEBUG='Y' THEN
1728 AHL_DEBUG_PUB.debug( 'schedule material id 5:'||l_schedule_material_id);
1729 END IF;
1730 ELSE
1731 IF G_DEBUG='Y' THEN
1732 AHL_DEBUG_PUB.debug( 'schedule material id 6:'||l_schedule_material_id);
1733 END IF;
1734
1735 -- Create Record in schedule materials
1736 Insert_Row (
1737 X_SCHEDULED_MATERIAL_ID => l_schedule_material_id,
1738 X_OBJECT_VERSION_NUMBER => 1,
1739 X_INVENTORY_ITEM_ID => p_x_req_material_tbl(i).inventory_item_id,
1740 X_SCHEDULE_DESIGNATOR => l_schedule_designator,
1741 X_VISIT_ID => l_visit_id,
1742 X_VISIT_START_DATE => l_Req_Material_Tbl(i).visit_start_date,
1743 X_VISIT_TASK_ID => p_x_req_material_tbl(i).visit_task_id,
1744 X_ORGANIZATION_ID => p_x_req_material_tbl(i).organization_id,
1745 X_SCHEDULED_DATE => l_Req_Material_Tbl(i).scheduled_date,
1746 X_REQUEST_ID => l_Req_Material_Tbl(i).request_id,
1747 X_REQUESTED_DATE => p_x_req_material_tbl(i).requested_date,
1748 X_SCHEDULED_QUANTITY => l_Req_Material_Tbl(i).scheduled_quantity,
1749 X_PROCESS_STATUS => null,
1750 X_ERROR_MESSAGE => null,
1751 X_TRANSACTION_ID => l_Req_Material_Tbl(i).transaction_id,
1752 X_UOM => l_Req_Material_Tbl(i).uom_code,
1753 X_RT_OPER_MATERIAL_ID => l_Req_Material_Tbl(i).rt_oper_material_id,
1754 X_OPERATION_CODE => l_Req_Material_Tbl(i).operation_code,
1755 X_OPERATION_SEQUENCE => l_Req_Material_Tbl(i).operation_sequence,
1756 X_ITEM_GROUP_ID => l_Req_Material_Tbl(i).item_group_id,
1757 X_REQUESTED_QUANTITY => p_x_req_material_tbl(i).requested_quantity,
1758 X_PROGRAM_ID => l_Req_Material_Tbl(i).program_id,
1759 X_PROGRAM_UPDATE_DATE => l_Req_Material_Tbl(i).program_update_date,
1760 X_LAST_UPDATED_DATE => l_Req_Material_Tbl(i).last_updated_date,
1761 X_WORKORDER_OPERATION_ID => p_x_req_material_tbl(i).workorder_operation_id,
1762 X_MATERIAL_REQUEST_TYPE => 'UNPLANNED',
1763 X_STATUS => 'ACTIVE',
1764 X_ATTRIBUTE_CATEGORY => l_Req_Material_Tbl(i).attribute_category,
1765 X_ATTRIBUTE1 => l_Req_Material_Tbl(i).attribute1,
1766 X_ATTRIBUTE2 => l_Req_Material_Tbl(i).attribute2,
1767 X_ATTRIBUTE3 => l_Req_Material_Tbl(i).attribute3,
1768 X_ATTRIBUTE4 => l_Req_Material_Tbl(i).attribute4,
1769 X_ATTRIBUTE5 => l_Req_Material_Tbl(i).attribute5,
1770 X_ATTRIBUTE6 => l_Req_Material_Tbl(i).attribute6,
1771 X_ATTRIBUTE7 => l_Req_Material_Tbl(i).attribute7,
1772 X_ATTRIBUTE8 => l_Req_Material_Tbl(i).attribute8,
1773 X_ATTRIBUTE9 => l_Req_Material_Tbl(i).attribute9,
1774 X_ATTRIBUTE10 => l_Req_Material_Tbl(i).attribute10,
1775 X_ATTRIBUTE11 => l_Req_Material_Tbl(i).attribute11,
1776 X_ATTRIBUTE12 => l_Req_Material_Tbl(i).attribute12,
1777 X_ATTRIBUTE13 => l_Req_Material_Tbl(i).attribute13,
1778 X_ATTRIBUTE14 => l_Req_Material_Tbl(i).attribute14,
1779 X_ATTRIBUTE15 => l_Req_Material_Tbl(i).attribute15,
1780 X_CREATION_DATE => SYSDATE,
1781 X_CREATED_BY => fnd_global.user_id,
1782 X_LAST_UPDATE_DATE => SYSDATE,
1783 X_LAST_UPDATED_BY => fnd_global.user_id,
1784 X_LAST_UPDATE_LOGIN => fnd_global.login_id
1785 );
1786
1787 --Assign out parameter
1788 --
1789 p_x_req_material_tbl(i).schedule_material_id := l_schedule_material_id;
1790 -- fix for bug# 5549135.
1791 --p_x_req_material_tbl(i).mrp_net_flag := 1;
1792 p_x_req_material_tbl(i).mrp_net_flag := 2;
1793
1794 --
1795 END IF; -- --rt oper id not null
1796 IF G_DEBUG='Y' THEN
1797 AHL_DEBUG_PUB.debug( 'after rt oper material id:'||l_schedule_material_id);
1798 END IF;
1799 --
1800 END IF; -- --Interface flag
1801 --
1802 IF G_DEBUG='Y' THEN
1803 AHL_DEBUG_PUB.debug( 'schedule material id:'||l_schedule_material_id);
1804 END IF;
1805 --
1806 END IF; --Material id g_miss_num
1807 --
1808 SELECT AHL_WO_OPERATIONS_TXNS_S.NEXTVAL INTO l_wo_operation_txn_id
1809 FROM DUAL;
1810 --
1811 IF G_DEBUG='Y' THEN
1812 AHL_DEBUG_PUB.debug( 'before calling log record l_wo_operation_txn_id:'||l_wo_operation_txn_id);
1813 END IF;
1814 --Create Record in transactions table
1815 Log_Transaction_Record
1816 ( p_wo_operation_txn_id => l_wo_operation_txn_id,
1817 p_object_version_number => 1,
1818 p_last_update_date => sysdate,
1819 p_last_updated_by => fnd_global.user_id,
1820 p_creation_date => sysdate,
1821 p_created_by => fnd_global.user_id,
1822 p_last_update_login => fnd_global.login_id,
1823 p_load_type_code => 2,
1824 p_transaction_type_code => 1,
1825 p_workorder_operation_id => p_x_req_material_tbl(i).workorder_operation_id,
1826 p_schedule_material_id => p_x_req_material_tbl(i).schedule_material_id,
1827 p_inventory_item_id => p_x_req_material_tbl(i).inventory_item_id,
1828 p_required_quantity => p_x_req_material_tbl(i).requested_quantity,
1829 p_date_required => p_x_req_material_tbl(i).requested_date
1830 );
1831
1832 --Call MRP Process
1833 --
1834 IF G_DEBUG='Y' THEN
1835 AHL_DEBUG_PUB.debug( 'before calling MRP l_schedule_designator:'||l_schedule_designator);
1836 END IF;
1837 IF (p_interface_flag IS NULL OR p_interface_flag = 'Y') THEN
1838
1839 -- Create Record in schedule materials
1840 Insert_Row (
1841 X_SCHEDULED_MATERIAL_ID => l_schedule_material_id,
1842 X_OBJECT_VERSION_NUMBER => 1,
1843 X_INVENTORY_ITEM_ID => p_x_req_material_tbl(i).inventory_item_id,
1844 X_SCHEDULE_DESIGNATOR => l_schedule_designator,
1845 X_VISIT_ID => l_visit_id,
1846 X_VISIT_START_DATE => l_Req_Material_Tbl(i).visit_start_date,
1847 X_VISIT_TASK_ID => p_x_req_material_tbl(i).visit_task_id,
1848 X_ORGANIZATION_ID => p_x_req_material_tbl(i).organization_id,
1849 X_SCHEDULED_DATE => l_Req_Material_Tbl(i).scheduled_date,
1850 X_REQUEST_ID => l_Req_Material_Tbl(i).request_id,
1851 X_REQUESTED_DATE => p_x_req_material_tbl(i).requested_date,
1852 X_SCHEDULED_QUANTITY => l_Req_Material_Tbl(i).scheduled_quantity,
1853 X_PROCESS_STATUS => null,
1854 X_ERROR_MESSAGE => null,
1855 X_TRANSACTION_ID => l_Req_Material_Tbl(i).transaction_id,
1856 X_UOM => l_Req_Material_Tbl(i).uom_code,
1857 X_RT_OPER_MATERIAL_ID => l_Req_Material_Tbl(i).rt_oper_material_id,
1858 X_OPERATION_CODE => l_Req_Material_Tbl(i).operation_code,
1859 X_OPERATION_SEQUENCE => l_Req_Material_Tbl(i).operation_sequence,
1860 X_ITEM_GROUP_ID => l_Req_Material_Tbl(i).item_group_id,
1861 X_REQUESTED_QUANTITY => p_x_req_material_tbl(i).requested_quantity,
1862 X_PROGRAM_ID => l_Req_Material_Tbl(i).program_id,
1863 X_PROGRAM_UPDATE_DATE => l_Req_Material_Tbl(i).program_update_date,
1864 X_LAST_UPDATED_DATE => l_Req_Material_Tbl(i).last_updated_date,
1865 X_WORKORDER_OPERATION_ID => p_x_req_material_tbl(i).workorder_operation_id,
1866 X_MATERIAL_REQUEST_TYPE => 'UNPLANNED',
1867 X_STATUS => nvl(l_Req_Material_Tbl(i).status,'ACTIVE'),
1868 X_ATTRIBUTE_CATEGORY => l_Req_Material_Tbl(i).attribute_category,
1869 X_ATTRIBUTE1 => l_Req_Material_Tbl(i).attribute1,
1870 X_ATTRIBUTE2 => l_Req_Material_Tbl(i).attribute2,
1871 X_ATTRIBUTE3 => l_Req_Material_Tbl(i).attribute3,
1872 X_ATTRIBUTE4 => l_Req_Material_Tbl(i).attribute4,
1873 X_ATTRIBUTE5 => l_Req_Material_Tbl(i).attribute5,
1874 X_ATTRIBUTE6 => l_Req_Material_Tbl(i).attribute6,
1875 X_ATTRIBUTE7 => l_Req_Material_Tbl(i).attribute7,
1876 X_ATTRIBUTE8 => l_Req_Material_Tbl(i).attribute8,
1877 X_ATTRIBUTE9 => l_Req_Material_Tbl(i).attribute9,
1878 X_ATTRIBUTE10 => l_Req_Material_Tbl(i).attribute10,
1879 X_ATTRIBUTE11 => l_Req_Material_Tbl(i).attribute11,
1880 X_ATTRIBUTE12 => l_Req_Material_Tbl(i).attribute12,
1881 X_ATTRIBUTE13 => l_Req_Material_Tbl(i).attribute13,
1882 X_ATTRIBUTE14 => l_Req_Material_Tbl(i).attribute14,
1883 X_ATTRIBUTE15 => l_Req_Material_Tbl(i).attribute15,
1884 X_CREATION_DATE => SYSDATE,
1885 X_CREATED_BY => fnd_global.user_id,
1886 X_LAST_UPDATE_DATE => SYSDATE,
1887 X_LAST_UPDATED_BY => fnd_global.user_id,
1888 X_LAST_UPDATE_LOGIN => fnd_global.login_id
1889 );
1890
1891 --Assign out parameter
1892 p_x_req_material_tbl(i).schedule_material_id := l_schedule_material_id;
1893 --
1894 END IF; -- Interface flag Is null condiiton
1895 --
1896
1897 END LOOP;
1898 END IF; --Count
1899 --
1900 X_return_status := 'S';
1901 x_job_return_status := 'S';
1902 ELSE
1903 x_job_return_status := 'E';
1904 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1905 RAISE Fnd_Api.G_EXC_ERROR;
1906 END IF; --status condition
1907 --Call notification API
1908 --Send Material Notification
1909 IF X_return_status = 'S' THEN
1910 --Check for Profile Option value If 'Y' Call
1911 IF FND_PROFILE.value( 'AHL_MTL_REQ_NOTIFICATION_ENABLED') = 'Y' THEN
1912 --
1913 MATERIAL_NOTIFICATION
1914 (
1915 p_api_version => p_api_version,
1916 p_init_msg_list => p_init_msg_list,
1917 p_commit => p_commit,
1918 p_validation_level => p_validation_level,
1919 p_Req_Material_Tbl => p_x_req_material_tbl,
1920 x_return_status => l_return_status,
1921 x_msg_count => l_msg_count,
1922 x_msg_data => l_msg_data);
1923 END IF;
1924 END IF;
1925 -- dbms_output.put_line( 'end of API:');
1926
1927 ------------------------End of Body---------------------------------------
1928 --Standard check to count messages
1929 l_msg_count := Fnd_Msg_Pub.count_msg;
1930
1931 IF l_msg_count > 0 THEN
1932 X_msg_count := l_msg_count;
1933 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
1934 RAISE Fnd_Api.G_EXC_ERROR;
1935 END IF;
1936
1937 --Standard check for commit
1938 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
1939 COMMIT;
1940 END IF;
1941 -- Debug info
1942 IF G_DEBUG='Y' THEN
1943 Ahl_Debug_Pub.debug( 'End of public api Create Material Reqst','+PPMRP+');
1944 -- Check if API is called in debug mode. If yes, disable debug.
1945 Ahl_Debug_Pub.disable_debug;
1946 END IF;
1947
1948 EXCEPTION
1949 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1950 ROLLBACK TO create_material_reqst;
1951 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1952 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1953 p_count => x_msg_count,
1954 p_data => x_msg_data);
1955 IF G_DEBUG='Y' THEN
1956 AHL_DEBUG_PUB.log_app_messages (
1957 x_msg_count, x_msg_data, 'ERROR' );
1958 AHL_DEBUG_PUB.debug( 'ahl_pp_materials_pvt. Create Material Reqst','+PPMRP+');
1959 -- Check if API is called in debug mode. If yes, disable debug.
1960 AHL_DEBUG_PUB.disable_debug;
1961 END IF;
1962
1963 WHEN FND_API.G_EXC_ERROR THEN
1964 ROLLBACK TO create_material_reqst;
1965 X_return_status := FND_API.G_RET_STS_ERROR;
1966 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1967 p_count => x_msg_count,
1968 p_data => X_msg_data);
1969 IF G_DEBUG='Y' THEN
1970 -- Debug info.
1971 AHL_DEBUG_PUB.log_app_messages (
1972 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
1973 AHL_DEBUG_PUB.debug( 'ahl_pp_materials_pvt. Create Material Reqst','+PPMRP+');
1974 -- Check if API is called in debug mode. If yes, disable debug.
1975 AHL_DEBUG_PUB.disable_debug;
1976 END IF;
1977 WHEN OTHERS THEN
1978 ROLLBACK TO create_material_reqst;
1979 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1980 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1981 THEN
1982 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PP_MATERIALS_PVT',
1983 p_procedure_name => 'CREATE_MATERIAL_REQST',
1984 p_error_text => SUBSTR(SQLERRM,1,240));
1985 END IF;
1986 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
1987 p_count => x_msg_count,
1988 p_data => X_msg_data);
1989 IF G_DEBUG='Y' THEN
1990 -- Debug info.
1991 AHL_DEBUG_PUB.log_app_messages (
1992 x_msg_count, x_msg_data, 'SQL ERROR' );
1993 AHL_DEBUG_PUB.debug( 'ahl_pp_materials_pvt. Create Material Reqst','+PPMRP+');
1994 -- Check if API is called in debug mode. If yes, disable debug.
1995 AHL_DEBUG_PUB.disable_debug;
1996 END IF;
1997 END Create_Material_Reqst;
1998 --
1999 -- Start of Comments --
2000 -- Procedure name : Update_Material_Reqst
2001 -- Type : Private
2002 -- Function : Updates schedule material table with requested fields, before
2003 -- it calls Eam Api
2004 -- Pre-reqs :
2005 -- Parameters :
2006 --
2007 -- Standard IN Parameters :
2008 -- p_api_version IN NUMBER Required
2009 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2010 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2011 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2012 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
2013 -- p_module_type IN VARCHAR2 Default NULL.
2014 --
2015 -- Standard OUT Parameters :
2016 -- x_return_status OUT VARCHAR2 Required
2017 -- x_msg_count OUT NUMBER Required
2018 -- x_msg_data OUT VARCHAR2 Required
2019 --
2020 -- Update Material Request Parameters:
2021 -- p_x_req_material_tbl IN OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type,
2022 -- Contains material information to perform material reservation
2023 --
2024 -- Version :
2025 -- Initial Version 1.0
2026 --
2027 -- End of Comments.
2028
2029 PROCEDURE Update_Material_Reqst (
2030 p_api_version IN NUMBER,
2031 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
2032 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
2033 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
2034 p_module_type IN VARCHAR2 := NULL,
2035 p_x_req_material_tbl IN OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type,
2036 x_return_status OUT NOCOPY VARCHAR2,
2037 x_msg_count OUT NOCOPY NUMBER,
2038 x_msg_data OUT NOCOPY VARCHAR2
2039 )
2040 IS
2041 --
2042 CURSOR Get_Req_Matrl_cur (c_schedule_material_id IN NUMBER)
2043 IS
2044 SELECT B.scheduled_material_id,
2045 B.inventory_item_id,
2046 B.object_version_number,
2047 B.requested_date,
2048 B.organization_id,
2049 B.visit_id,
2050 B.visit_task_id,
2051 B.requested_quantity,
2052 B.workorder_operation_id,
2053 B.operation_sequence,
2054 B.item_group_id,
2055 B.uom,
2056 B.rt_oper_material_id,
2057 -- modified for FP bug# 6802777
2058 --B.department_id,
2059 WO.department_id,
2060 B.workorder_name,
2061 B.wip_entity_id,
2062 A.attribute_category,
2063 A.attribute1,
2064 A.attribute2,
2065 A.attribute3,
2066 A.attribute4,
2067 A.attribute5,
2068 A.attribute6,
2069 A.attribute7,
2070 A.attribute8,
2071 A.attribute9,
2072 A.attribute10,
2073 A.attribute11,
2074 A.attribute12,
2075 A.attribute13,
2076 A.attribute14,
2077 A.attribute15,
2078 A.completed_quantity,
2079 A.requested_date old_requested_date -- added to fix bug# 5182334.
2080 FROM AHL_SCHEDULE_MATERIALS A,
2081 AHL_JOB_OPER_MATERIALS_V B, WIP_OPERATIONS WO
2082 WHERE A.SCHEDULED_MATERIAL_ID = B.SCHEDULED_MATERIAL_ID
2083 AND B.WIP_ENTITY_ID = WO.WIP_ENTITY_ID
2084 AND B.OPERATION_SEQUENCE = WO.OPERATION_SEQ_NUM
2085 AND A.SCHEDULED_MATERIAL_ID = c_schedule_material_id
2086 FOR UPDATE OF A.OBJECT_VERSION_NUMBER;
2087
2088 -- Get transaction log
2089 CURSOR Get_trans_log_cur(c_wo_trans_id IN NUMBER)
2090 IS
2091 SELECT inventory_item_id,
2092 schedule_material_id,
2093 date_required,
2094 required_quantity
2095 FROM ahl_wo_operations_txns
2096 WHERE wo_operation_txn_id = c_wo_trans_id;
2097 --
2098 --Check for status Released or Unreleased
2099 CURSOR Check_wo_status_cur(c_workorder_id IN NUMBER)
2100 IS
2101 SELECT 1
2102 FROM ahl_workorders
2103 WHERE workorder_id = c_workorder_id
2104 AND (status_code = 3 or
2105 status_code = 1);
2106 --Get wo transaction id
2107 CURSOR Get_wo_transaction_id(c_sch_material_id IN NUMBER)
2108 IS
2109 SELECT max(wo_operation_txn_id)
2110 FROM ahl_wo_operations_txns
2111 WHERE schedule_material_id = c_sch_material_id;
2112
2113 -- rroy
2114 -- ACL Changes
2115
2116 -- Get job number details
2117 CURSOR Get_job_number(c_workorder_id IN NUMBER)
2118 IS
2119 SELECT workorder_name
2120 FROM ahl_workorders
2121 WHERE workorder_id = c_workorder_id;
2122 -- rroy
2123 -- ACL Changes
2124
2125 -- R12: Serial Reservation changes.
2126 -- get count on existing reservations.
2127 CURSOR get_count_resrv_cur (c_item_id IN NUMBER,
2128 c_org_id IN NUMBER,
2129 c_wip_entity_id IN NUMBER,
2130 c_oper_seq_num IN NUMBER) IS
2131 SELECT nvl(SUM(mrv.primary_reservation_quantity), 0) reserved_quantity
2132 FROM mtl_reservations MRV
2133 WHERE MRV.INVENTORY_ITEM_ID = c_item_id
2134 AND MRV.EXTERNAL_SOURCE_CODE = 'AHL'
2135 AND MRV.DEMAND_SOURCE_HEADER_ID = c_wip_entity_id
2136 AND MRV.DEMAND_SOURCE_LINE_ID = c_oper_seq_num;
2137
2138 --
2139 l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_MATERIAL_REQST';
2140 l_api_version CONSTANT NUMBER := 1.0;
2141 l_msg_count NUMBER;
2142 l_return_status VARCHAR2(1);
2143 l_msg_data VARCHAR2(2000);
2144 l_dummy NUMBER;
2145 l_scheduled VARCHAR2(1);
2146 --
2147 l_get_trans_log_rec get_trans_log_cur%ROWTYPE;
2148 --
2149 l_workorder_id NUMBER;
2150 l_new_inventory_id NUMBER;
2151 l_default VARCHAR2(30);
2152 --
2153 l_req_material_tbl Req_Material_Tbl_Type;
2154 l_object_version_number NUMBER;
2155 l_req_material_rec Get_Req_Matrl_cur%ROWTYPE;
2156 --
2157 l_workorder_name VARCHAR2(80);
2158 l_wo_organization_id NUMBER;
2159 l_wo_transaction_id NUMBER;
2160 l_reserved_quantity NUMBER;
2161
2162 --
2163 -- Variables required for wip jobs call
2164 l_wo_operation_txn_id NUMBER;
2165 l_inventory_item_old NUMBER;
2166 j NUMBER;
2167 --
2168 BEGIN
2169 --------------------Initialize ----------------------------------
2170 -- Standard Start of API savepoint
2171 SAVEPOINT update_material_reqst;
2172 -- Check if API is called in debug mode. If yes, enable debug.
2173 IF G_DEBUG='Y' THEN
2174 AHL_DEBUG_PUB.enable_debug;
2175 -- Debug info.
2176 AHL_DEBUG_PUB.debug( 'enter ahl_pp_materias_pvt. update material reqst','+PPMRP+');
2177 END IF;
2178 -- Standard call to check for call compatibility.
2179 IF FND_API.to_boolean(p_init_msg_list)
2180 THEN
2181 FND_MSG_PUB.initialize;
2182 END IF;
2183 -- Initialize API return status to success
2184 x_return_status := FND_API.G_RET_STS_SUCCESS;
2185 -- Initialize message list if p_init_msg_list is set to TRUE.
2186 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
2187 p_api_version,
2188 l_api_name,G_PKG_NAME)
2189 THEN
2190 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2191 END IF;
2192 --------------------Start of API Body-----------------------------------
2193 IF p_x_req_material_tbl.COUNT > 0 THEN
2194 FOR i IN p_x_req_material_tbl.FIRST..p_x_req_material_tbl.LAST
2195 LOOP
2196 IF p_module_type <> 'API' THEN
2197 -- Value to ID Conversion
2198 IF ( ( p_x_req_material_tbl(i).workorder_id IS NOT NULL AND
2199 p_x_req_material_tbl(i).workorder_id <> FND_API.G_MISS_NUM ) OR
2200 ( p_x_req_material_tbl(i).job_number IS NOT NULL AND
2201 p_x_req_material_tbl(i).job_number <> FND_API.G_MISS_CHAR ) )
2202 THEN
2203 --
2204 IF G_DEBUG='Y' THEN
2205 AHL_DEBUG_PUB.debug( 'WOID :'||p_x_req_material_tbl(i).workorder_id);
2206 END IF;
2207 --
2208 Get_workorder_id
2209 (p_workorder_id => p_x_req_material_tbl(i).workorder_id,
2210 p_job_number => p_x_req_material_tbl(i).job_number,
2211 x_workorder_id => l_workorder_id,
2212 x_return_status => l_return_status,
2213 x_error_msg_code => l_msg_data);
2214
2215 IF NVL(l_return_status,'x') <> 'S'
2216 THEN
2217 Fnd_Message.SET_NAME('AHL','AHL_PP_WO_ORD_NOT_EXISTS');
2218 Fnd_Msg_Pub.ADD;
2219 END IF;
2220 --
2221 ELSE
2222 Fnd_Message.SET_NAME('AHL','AHL_PP_WO_ORD_REQUIRED');
2223 Fnd_Msg_Pub.ADD;
2224 END IF;
2225 --
2226 p_x_req_material_tbl(i).workorder_id := l_workorder_id;
2227
2228 -- rroy
2229 -- ACL Changes
2230 l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked(
2231 p_workorder_id => p_x_req_material_tbl(i).workorder_id,
2232 p_ue_id => NULL,
2233 p_visit_id => NULL,
2234 p_item_instance_id => NULL);
2235 IF l_return_status = FND_API.G_TRUE THEN
2236 OPEN get_job_number(p_x_req_material_tbl(i).workorder_id);
2237 FETCH get_job_number INTO l_workorder_name;
2238 CLOSE get_job_number;
2239 FND_MESSAGE.Set_Name('AHL', 'AHL_PP_UPD_MTL_UNTLCKD');
2240 FND_MESSAGE.Set_Token('WO_NAME', l_workorder_name);
2241 FND_MSG_PUB.ADD;
2242 RAISE FND_API.G_EXC_ERROR;
2243 END IF;
2244 -- rroy
2245 -- ACL Changes
2246
2247 --
2248 IF G_DEBUG='Y' THEN
2249 AHL_DEBUG_PUB.debug( 'WOID 2:'||p_x_req_material_tbl(i).workorder_id);
2250 END IF;
2251 END IF; --Module type
2252
2253 --Get Requirement operation details
2254 OPEN Get_Req_Matrl_cur(p_x_req_material_tbl(i).schedule_material_id);
2255 FETCH Get_Req_Matrl_cur INTO l_req_material_rec;
2256 CLOSE Get_Req_Matrl_cur;
2257 -- Assign workorder operation id if null
2258 p_x_req_material_tbl(i).workorder_operation_id := l_req_material_rec.workorder_operation_id;
2259 p_x_req_material_tbl(i).operation_sequence := l_req_material_rec.operation_sequence;
2260 p_x_req_material_tbl(i).job_number := l_req_material_rec.workorder_name;
2261 p_x_req_material_tbl(i).wip_entity_id := l_req_material_rec.wip_entity_id;
2262 p_x_req_material_tbl(i).organization_id := l_req_material_rec.organization_id;
2263 p_x_req_material_tbl(i).department_id := l_req_material_rec.department_id;
2264 p_x_req_material_tbl(i).inventory_item_id := l_req_material_rec.inventory_item_id;
2265 p_x_req_material_tbl(i).visit_id := l_req_material_rec.visit_id;
2266 p_x_req_material_tbl(i).visit_task_id := l_req_material_rec.visit_task_id;
2267 --
2268 IF G_DEBUG='Y' THEN
2269 AHL_DEBUG_PUB.debug( 'INVID :'||p_x_req_material_tbl(i).inventory_item_id);
2270 END IF;
2271
2272 -- Validate for Requested Quantity
2273 IF (p_x_req_material_tbl(i).requested_quantity IS NULL OR
2274 p_x_req_material_tbl(i).requested_quantity = FND_API.G_MISS_NUM) THEN
2275 Fnd_Message.SET_NAME('AHL','AHL_PP_REQ_QUANTITY_REQUIRED');
2276 Fnd_Msg_Pub.ADD;
2277 ELSIF (p_x_req_material_tbl(i).requested_quantity IS NOT NULL AND
2278 p_x_req_material_tbl(i).requested_quantity <> FND_API.G_MISS_NUM) THEN
2279 IF p_x_req_material_tbl(i).requested_quantity < 0 THEN
2280 Fnd_Message.SET_NAME('AHL','AHL_PP_REQ_QUANTITY_INVALID');
2281 Fnd_Msg_Pub.ADD;
2282 END IF;
2283 -- added in R12: Serial Reservation project.
2284 -- If requested quantity changed -
2285 IF (p_x_req_material_tbl(i).requested_quantity <>
2286 l_req_material_rec.requested_quantity)
2287 THEN
2288 -- check for reservations, if any.
2289 OPEN get_count_resrv_cur(l_req_material_rec.inventory_item_id,
2290 l_req_material_rec.organization_id,
2291 l_req_material_rec.wip_entity_id,
2292 l_req_material_rec.operation_sequence);
2293 FETCH get_count_resrv_cur INTO l_reserved_quantity;
2294 CLOSE get_count_resrv_cur;
2295
2296 IF (p_x_req_material_tbl(i).requested_quantity < l_reserved_quantity)
2297 THEN
2298 Fnd_Message.SET_NAME('AHL','AHL_PP_REQ_RESRV_QTY');
2299 Fnd_Message.SET_TOKEN('REQ_QTY',p_x_req_material_tbl(i).requested_quantity);
2300 Fnd_Message.SET_TOKEN('RRV_QTY',l_reserved_quantity);
2301 Fnd_Msg_Pub.ADD;
2302 END IF;-- p_x_req_material_tbl(i).requested_quantity <
2303 END IF; --p_x_req_material_tbl(i).requested_quantity <>
2304 END IF;
2305 IF G_DEBUG='Y' THEN
2306 AHL_DEBUG_PUB.debug( 'QTY :'||p_x_req_material_tbl(i).requested_quantity);
2307 END IF;
2308 IF p_module_type <> 'API' THEN
2309 -- Validate for Requested Date
2310 IF (p_x_req_material_tbl(i).requested_date IS NULL OR
2311 p_x_req_material_tbl(i).requested_date = FND_API.G_MISS_DATE) THEN
2312 Fnd_Message.SET_NAME('AHL','AHL_PP_REQ_DATE_REQUIRED');
2313 Fnd_Msg_Pub.ADD;
2314 ELSIF (p_x_req_material_tbl(i).requested_date IS NOT NULL AND
2315 p_x_req_material_tbl(i).requested_date <> FND_API.G_MISS_DATE) THEN
2316 IF p_x_req_material_tbl(i).requested_date < trunc(SYSDATE) THEN
2317 Fnd_Message.SET_NAME('AHL','AHL_PP_REQ_DATE_GT_EQ_SYSD');
2318 Fnd_Msg_Pub.ADD;
2319 END IF;
2320 END IF;
2321 IF G_DEBUG='Y' THEN
2322 AHL_DEBUG_PUB.debug( 'DATE :'||p_x_req_material_tbl(i).requested_date);
2323 END IF;
2324
2325 -- Validate for Schedule Material ID
2326 IF (p_x_req_material_tbl(i).schedule_material_id IS NULL AND
2327 p_x_req_material_tbl(i).schedule_material_id = FND_API.G_MISS_NUM) THEN
2328 Fnd_Message.SET_NAME('AHL','AHL_PP_SCH_MATRL_REQUIRED');
2329 Fnd_Msg_Pub.ADD;
2330 END IF;
2331 IF G_DEBUG='Y' THEN
2332 AHL_DEBUG_PUB.debug( 'SCHID :'||p_x_req_material_tbl(i).schedule_material_id);
2333 AHL_DEBUG_PUB.debug( 'OSID'||p_x_req_material_tbl(i).operation_sequence);
2334 END IF;
2335 --Check for workorder status
2336 OPEN Check_wo_status_cur(p_x_req_material_tbl(i).workorder_id);
2337 FETCH Check_wo_status_cur INTO l_dummy;
2338 IF Check_wo_status_cur%NOTFOUND THEN
2339 --
2340 Fnd_Message.SET_NAME('AHL','AHL_PP_WO_STATUS_INVALID');
2341 Fnd_Msg_Pub.ADD;
2342 END IF;
2343 --
2344 CLOSE Check_wo_status_cur;
2345 --
2346 ELSE
2347 p_x_req_material_tbl(i).requested_date := l_req_material_rec.requested_date;
2348
2349 END IF; --Module type
2350
2351 --Standard check to count messages
2352 l_msg_count := Fnd_Msg_Pub.count_msg;
2353
2354 IF l_msg_count > 0 THEN
2355 X_msg_count := l_msg_count;
2356 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2357 RAISE Fnd_Api.G_EXC_ERROR;
2358 END IF;
2359
2360 END LOOP; --for loop
2361 END IF;
2362
2363 -- Calling Wip job api
2364 --
2365 IF G_DEBUG='Y' THEN
2366 AHL_DEBUG_PUB.debug( 'End of validations');
2367 AHL_DEBUG_PUB.debug('Before processing updates');
2368 END IF;
2369 --
2370
2371 --IF p_module_type <> 'API' THEN
2372
2373 IF p_x_req_material_tbl.COUNT >0
2374 THEN
2375 j := 1;
2376 FOR i in p_x_req_material_tbl.FIRST..p_x_req_material_tbl.LAST
2377 LOOP
2378 --Get the latest record
2379 OPEN Get_wo_transaction_id(p_x_req_material_tbl(i).schedule_material_id);
2380 FETCH get_wo_transaction_id INTO l_wo_transaction_id;
2381 CLOSE get_wo_transaction_id;
2382 --Get the transaction log record from ahl_wo_operation_txns table
2383 OPEN Get_trans_log_cur(l_wo_transaction_id);
2384 FETCH Get_trans_log_cur INTO l_get_trans_log_rec;
2385 CLOSE Get_trans_log_cur;
2386 --
2387 OPEN Get_Req_Matrl_cur(p_x_req_material_tbl(i).schedule_material_id);
2388 FETCH Get_Req_Matrl_cur INTO l_req_material_rec;
2389 CLOSE Get_Req_Matrl_cur;
2390 --Check for item id
2391 IF l_get_trans_log_rec.inventory_item_id <> p_x_req_material_tbl(i).inventory_item_id
2392 THEN
2393 l_inventory_item_old := l_get_trans_log_rec.inventory_item_id;
2394 ELSE
2395 l_inventory_item_old := p_x_req_material_tbl(i).inventory_item_id;
2396 END IF;
2397 -- Assign workorder operation id if null
2398 p_x_req_material_tbl(i).workorder_operation_id := l_req_material_rec.workorder_operation_id;
2399 --
2400 --Call transaction log to create record ahl_wo_operations_txns
2401 SELECT AHL_WO_OPERATIONS_TXNS_S.NEXTVAL INTO l_wo_operation_txn_id
2402 FROM DUAL;
2403 --
2404 IF G_DEBUG='Y' THEN
2405 AHL_DEBUG_PUB.debug( 'before calling log record l_wo_operation_txn_id:'||l_wo_operation_txn_id);
2406 END IF;
2407 Log_Transaction_Record
2408 ( p_wo_operation_txn_id => l_wo_operation_txn_id,
2409 p_object_version_number => 1,
2410 p_last_update_date => sysdate,
2411 p_last_updated_by => fnd_global.user_id,
2412 p_creation_date => sysdate,
2413 p_created_by => fnd_global.user_id,
2414 p_last_update_login => fnd_global.login_id,
2415 p_load_type_code => 2,
2416 p_transaction_type_code => 1,
2417 p_workorder_operation_id => p_x_req_material_tbl(i).workorder_operation_id,
2418 p_schedule_material_id => p_x_req_material_tbl(i).schedule_material_id,
2419 p_inventory_item_id => p_x_req_material_tbl(i).inventory_item_id,
2420 p_required_quantity => p_x_req_material_tbl(i).requested_quantity,
2421 p_date_required => p_x_req_material_tbl(i).requested_date
2422 );
2423 --Assign to output
2424 l_req_material_tbl(j).JOB_NUMBER := p_x_req_material_tbl(i).job_number;
2425 l_req_material_tbl(j).WIP_ENTITY_ID := p_x_req_material_tbl(i).wip_entity_id;
2426 l_req_material_tbl(j).WORKORDER_ID := p_x_req_material_tbl(i).workorder_id;
2427 l_req_material_tbl(j).OPERATION_SEQUENCE := l_req_material_rec.operation_sequence;
2428 l_req_material_tbl(j).UOM_CODE := l_req_material_rec.uom;
2429 l_req_material_tbl(j).INVENTORY_ITEM_ID := p_x_req_material_tbl(i).inventory_item_id;
2430 l_req_material_tbl(j).ORGANIZATION_ID := p_x_req_material_tbl(i).organization_id;
2431 l_req_material_tbl(j).DEPARTMENT_ID := p_x_req_material_tbl(i).department_id;
2432 -- fix for bug# 5549135
2433 --l_req_material_tbl(j).MRP_NET_FLAG := 1;
2434 l_req_material_tbl(j).MRP_NET_FLAG := 2;
2435 l_req_material_tbl(j).QUANTITY_PER_ASSEMBLY := p_x_req_material_tbl(i).requested_quantity;
2436 l_req_material_tbl(j).REQUESTED_QUANTITY := p_x_req_material_tbl(i).requested_quantity;
2437 l_req_material_tbl(j).SUPPLY_TYPE := NULL;
2438 l_req_material_tbl(j).LOCATION := NULL;
2439 l_req_material_tbl(j).SUB_INVENTORY := NULL;
2440 l_req_material_tbl(j).REQUESTED_DATE := p_x_req_material_tbl(i).requested_date;
2441 l_req_material_tbl(j).OPERATION_FLAG := 'U';
2442
2443 j := j + 1;
2444 IF G_DEBUG='Y' THEN
2445 AHL_DEBUG_PUB.debug('Request Date from DB for sch. Mat ID:1:' || l_req_material_rec.old_requested_date || ':' || p_x_req_material_tbl(i).schedule_material_id);
2446 AHL_DEBUG_PUB.debug('Changed Request Date :' || p_x_req_material_tbl(i).requested_date );
2447 END IF;
2448 --
2449 END LOOP;
2450 END IF; --Material tbl
2451
2452 IF G_DEBUG='Y' THEN
2453 AHL_DEBUG_PUB.debug('beforer wip job record assign');
2454 END IF;
2455
2456 IF l_req_material_tbl.COUNT > 0 THEN
2457 --
2458 IF G_DEBUG='Y' THEN
2459 AHL_DEBUG_PUB.debug('before Eam Workorder job call');
2460 AHL_DEBUG_PUB.debug('before Eam Api jobs call''count :'||l_req_material_tbl.count);
2461
2462 END IF;
2463 -- Call wip job api
2464 --
2465
2466 AHL_EAM_JOB_PVT.process_material_req
2467 (
2468 p_api_version => l_api_version,
2469 p_init_msg_list => p_init_msg_list,
2470 p_commit => p_commit,
2471 p_validation_level => p_validation_level,
2472 p_default => l_default,
2473 p_module_type => p_module_type,
2474 x_return_status => l_return_status,
2475 x_msg_count => l_msg_count,
2476 x_msg_data => l_msg_data,
2477 p_material_req_tbl => l_req_material_tbl
2478 );
2479 --
2480 END IF; --Eam table count > 0
2481 IF G_DEBUG='Y' THEN
2482 AHL_DEBUG_PUB.debug('AHLVPPMB: after wip job call'||l_return_status);
2483 END IF;
2484 --END IF; --Module type null
2485
2486 IF l_return_status ='S' THEN
2487 --
2488 IF p_x_req_material_tbl.COUNT > 0
2489 THEN
2490 FOR i IN p_x_req_material_tbl.FIRST..p_x_req_material_tbl.LAST
2491 LOOP
2492 OPEN Get_Req_Matrl_cur(p_x_req_material_tbl(i).schedule_material_id);
2493 FETCH Get_Req_Matrl_cur INTO l_req_material_rec;
2494 CLOSE Get_Req_Matrl_cur;
2495
2496 IF p_x_req_material_tbl(i).schedule_material_id IS NOT NULL
2497 THEN
2498 --
2499 -- Added for R12 serial reservations enhancements - ER# 4295982.
2500 -- If requested date is changed, then call reservations api to change the
2501 -- requested date in WMS.
2502 IF G_DEBUG='Y' THEN
2503 AHL_DEBUG_PUB.debug('Request Date from DB for sch. Mat ID:2:' || l_req_material_rec.old_requested_date
2504 || ':' || p_x_req_material_tbl(i).schedule_material_id);
2505 AHL_DEBUG_PUB.debug('Changed Request Date :' || p_x_req_material_tbl(i).requested_date );
2506 END IF;
2507
2508 IF (trunc(p_x_req_material_tbl(i).requested_date) <> trunc(l_req_material_rec.old_requested_date))
2509 THEN
2510 IF G_DEBUG='Y' THEN
2511 AHL_DEBUG_PUB.debug('Before Call to Upd RSV ') ;
2512 END IF;
2513
2514 -- call update reservations api.
2515 AHL_RSV_RESERVATIONS_PVT.Update_Reservation(
2516 p_api_version => 1.0,
2517 p_init_msg_list => FND_API.G_FALSE,
2518 p_commit => FND_API.G_FALSE,
2519 p_module_type => NULL,
2520 x_return_status => x_return_status,
2521 x_msg_count => x_msg_count,
2522 x_msg_data => x_msg_data,
2523 p_scheduled_material_id => p_x_req_material_tbl(i).schedule_material_id,
2524 p_requested_date => p_x_req_material_tbl(i).requested_date);
2525
2526 IF G_DEBUG='Y' THEN
2527 AHL_DEBUG_PUB.debug('After Call to Upd RSV- Return Status:' || x_return_status);
2528 END IF;
2529
2530 -- Raise error if exceptions occur
2531 IF (x_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2532 RAISE FND_API.G_EXC_ERROR;
2533 END IF;
2534
2535 END IF; -- p_x_req_material_tbl(i).requested_date <>
2536 --
2537
2538 --
2539 -- The following conditions compare the new record value with old record
2540 -- value, if its different then assign the new value else continue
2541
2542 IF NVL(p_x_req_material_tbl(i).inventory_item_id, 0) <> FND_API.G_MISS_NUM
2543 THEN
2544 l_req_material_rec.inventory_item_id := p_x_req_material_tbl(i).inventory_item_id;
2545 END IF;
2546 --
2547 IF NVL(p_x_req_material_tbl(i).requested_date,sysdate) <> FND_API.G_MISS_DATE
2548 THEN
2549 l_req_material_rec.requested_date := p_x_req_material_tbl(i).requested_date;
2550 END IF;
2551 --
2552 IF NVL(p_x_req_material_tbl(i).requested_quantity, 0) <> FND_API.G_MISS_NUM
2553 THEN
2554 l_req_material_rec.requested_quantity := p_x_req_material_tbl(i).requested_quantity;
2555 END IF;
2556 --
2557 IF NVL(p_x_req_material_tbl(i).organization_id, 0) <> FND_API.G_MISS_NUM
2558 THEN
2559 l_req_material_rec.organization_id := p_x_req_material_tbl(i).organization_id;
2560 END IF;
2561 --
2562 IF NVL(p_x_req_material_tbl(i).visit_id, 0) <> FND_API.G_MISS_NUM
2563 THEN
2564 l_req_material_rec.visit_id := p_x_req_material_tbl(i).visit_id;
2565 END IF;
2566 --
2567 IF NVL(p_x_req_material_tbl(i).visit_task_id, 0) <> FND_API.G_MISS_NUM
2568 THEN
2569 l_req_material_rec.visit_task_id := p_x_req_material_tbl(i).visit_task_id;
2570 END IF;
2571 --
2572 IF NVL(p_x_req_material_tbl(i).item_group_id, 0) <> FND_API.G_MISS_NUM
2573 THEN
2574 l_req_material_rec.item_group_id := p_x_req_material_tbl(i).item_group_id;
2575 END IF;
2576 --
2577 IF NVL(p_x_req_material_tbl(i).rt_oper_material_id, 0) <> FND_API.G_MISS_NUM
2578 THEN
2579 l_req_material_rec.rt_oper_material_id := p_x_req_material_tbl(i).rt_oper_material_id;
2580 END IF;
2581
2582 --
2583 IF p_x_req_material_tbl(i).attribute_category IS NOT NULL AND
2584 p_x_req_material_tbl(i).attribute_category <> FND_API.G_MISS_CHAR
2585 THEN
2586 l_req_material_rec.attribute_category := p_x_req_material_tbl(i).attribute_category;
2587 ELSIF p_x_req_material_tbl(i).attribute_category = FND_API.G_MISS_CHAR THEN
2588 l_req_material_rec.attribute_category := NULL;
2589 END IF;
2590 --
2591 IF p_x_req_material_tbl(i).attribute1 IS NOT NULL AND
2592 p_x_req_material_tbl(i).attribute1 <> FND_API.G_MISS_CHAR
2593 THEN
2594 l_req_material_rec.attribute1 := p_x_req_material_tbl(i).attribute1;
2595 ELSIF p_x_req_material_tbl(i).attribute1 = FND_API.G_MISS_CHAR THEN
2596 l_req_material_rec.attribute1 := NULL;
2597 END IF;
2598
2599 --
2600 IF p_x_req_material_tbl(i).attribute2 IS NOT NULL AND
2601 p_x_req_material_tbl(i).attribute2 <> FND_API.G_MISS_CHAR
2602 THEN
2603 l_req_material_rec.attribute2 := p_x_req_material_tbl(i).attribute2;
2604 ELSIF p_x_req_material_tbl(i).attribute2 = FND_API.G_MISS_CHAR THEN
2605 l_req_material_rec.attribute2 := NULL;
2606 END IF;
2607 --
2608 IF p_x_req_material_tbl(i).attribute3 IS NOT NULL AND
2609 p_x_req_material_tbl(i).attribute3 <> FND_API.G_MISS_CHAR
2610 THEN
2611 l_req_material_rec.attribute3 := p_x_req_material_tbl(i).attribute3;
2612 ELSIF p_x_req_material_tbl(i).attribute3 = FND_API.G_MISS_CHAR THEN
2613 l_req_material_rec.attribute3 := NULL;
2614 END IF;
2615
2616 --
2617 IF p_x_req_material_tbl(i).attribute4 IS NOT NULL AND
2618 p_x_req_material_tbl(i).attribute4 <> FND_API.G_MISS_CHAR
2619 THEN
2620 l_req_material_rec.attribute4 := p_x_req_material_tbl(i).attribute4;
2621 ELSIF p_x_req_material_tbl(i).attribute4 = FND_API.G_MISS_CHAR THEN
2622 l_req_material_rec.attribute4 := NULL;
2623 END IF;
2624
2625 --
2626 IF p_x_req_material_tbl(i).attribute5 IS NOT NULL AND
2627 p_x_req_material_tbl(i).attribute5 <> FND_API.G_MISS_CHAR
2628 THEN
2629 l_req_material_rec.attribute5 := p_x_req_material_tbl(i).attribute5;
2630 ELSIF p_x_req_material_tbl(i).attribute5 = FND_API.G_MISS_CHAR THEN
2631 l_req_material_rec.attribute5 := NULL;
2632 END IF;
2633
2634 --
2635 IF p_x_req_material_tbl(i).attribute6 IS NOT NULL AND
2636 p_x_req_material_tbl(i).attribute6 <> FND_API.G_MISS_CHAR
2637 THEN
2638 l_req_material_rec.attribute6 := p_x_req_material_tbl(i).attribute6;
2639 ELSIF p_x_req_material_tbl(i).attribute6 = FND_API.G_MISS_CHAR THEN
2640 l_req_material_rec.attribute6 := NULL;
2641 END IF;
2642
2643 --
2644 IF p_x_req_material_tbl(i).attribute7 IS NOT NULL AND
2645 p_x_req_material_tbl(i).attribute7 <> FND_API.G_MISS_CHAR
2646 THEN
2647 l_req_material_rec.attribute7 := p_x_req_material_tbl(i).attribute7;
2648 ELSIF p_x_req_material_tbl(i).attribute7 = FND_API.G_MISS_CHAR THEN
2649 l_req_material_rec.attribute7 := NULL;
2650 END IF;
2651
2652 --
2653 IF p_x_req_material_tbl(i).attribute8 IS NOT NULL AND
2654 p_x_req_material_tbl(i).attribute8 <> FND_API.G_MISS_CHAR
2655 THEN
2656 l_req_material_rec.attribute8 := p_x_req_material_tbl(i).attribute8;
2657 ELSIF p_x_req_material_tbl(i).attribute8 = FND_API.G_MISS_CHAR THEN
2658 l_req_material_rec.attribute8 := NULL;
2659 END IF;
2660
2661 --
2662 IF p_x_req_material_tbl(i).attribute9 IS NOT NULL AND
2663 p_x_req_material_tbl(i).attribute9 <> FND_API.G_MISS_CHAR
2664 THEN
2665 l_req_material_rec.attribute9 := p_x_req_material_tbl(i).attribute9;
2666 ELSIF p_x_req_material_tbl(i).attribute9 = FND_API.G_MISS_CHAR THEN
2667 l_req_material_rec.attribute9 := NULL;
2668 END IF;
2669
2670 --
2671 IF p_x_req_material_tbl(i).attribute10 IS NOT NULL AND
2672 p_x_req_material_tbl(i).attribute10 <> FND_API.G_MISS_CHAR
2673 THEN
2674 l_req_material_rec.attribute10 := p_x_req_material_tbl(i).attribute10;
2675 ELSIF p_x_req_material_tbl(i).attribute10 = FND_API.G_MISS_CHAR THEN
2676 l_req_material_rec.attribute10 := NULL;
2677 END IF;
2678
2679 --
2680 IF p_x_req_material_tbl(i).attribute11 IS NOT NULL AND
2681 p_x_req_material_tbl(i).attribute11 <> FND_API.G_MISS_CHAR
2682 THEN
2683 l_req_material_rec.attribute11 := p_x_req_material_tbl(i).attribute11;
2684 ELSIF p_x_req_material_tbl(i).attribute11 = FND_API.G_MISS_CHAR THEN
2685 l_req_material_rec.attribute11 := NULL;
2686 END IF;
2687
2688 --
2689 IF p_x_req_material_tbl(i).attribute12 IS NOT NULL AND
2690 p_x_req_material_tbl(i).attribute12 <> FND_API.G_MISS_CHAR
2691 THEN
2692 l_req_material_rec.attribute12 := p_x_req_material_tbl(i).attribute12;
2693 ELSIF p_x_req_material_tbl(i).attribute12 = FND_API.G_MISS_CHAR THEN
2694 l_req_material_rec.attribute12 := NULL;
2695 END IF;
2696
2697 --
2698 IF p_x_req_material_tbl(i).attribute13 IS NOT NULL AND
2699 p_x_req_material_tbl(i).attribute13 <> FND_API.G_MISS_CHAR
2700 THEN
2701 l_req_material_rec.attribute13 := p_x_req_material_tbl(i).attribute13;
2702 ELSIF p_x_req_material_tbl(i).attribute13 = FND_API.G_MISS_CHAR THEN
2703 l_req_material_rec.attribute13 := NULL;
2704 END IF;
2705
2706 --
2707 IF p_x_req_material_tbl(i).attribute14 IS NOT NULL AND
2708 p_x_req_material_tbl(i).attribute14 <> FND_API.G_MISS_CHAR
2709 THEN
2710 l_req_material_rec.attribute14 := p_x_req_material_tbl(i).attribute14;
2711 ELSIF p_x_req_material_tbl(i).attribute14 = FND_API.G_MISS_CHAR THEN
2712 l_req_material_rec.attribute14 := NULL;
2713 END IF;
2714
2715 --
2716 IF p_x_req_material_tbl(i).attribute15 IS NOT NULL AND
2717 p_x_req_material_tbl(i).attribute15 <> FND_API.G_MISS_CHAR
2718 THEN
2719 l_req_material_rec.attribute15 := p_x_req_material_tbl(i).attribute15;
2720 ELSIF p_x_req_material_tbl(i).attribute15 = FND_API.G_MISS_CHAR THEN
2721 l_req_material_rec.attribute15 := NULL;
2722 END IF;
2723 --
2724 --Update schedule material table
2725 UPDATE AHL_SCHEDULE_MATERIALS
2726 SET inventory_item_id = l_req_material_rec.inventory_item_id,
2727 -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
2728 requested_date = trunc(l_req_material_rec.requested_date),
2729 requested_quantity = l_req_material_rec.requested_quantity,
2730 object_version_number = l_req_material_rec.object_version_number+1,
2731 visit_id = l_req_material_rec.visit_id,
2732 visit_task_id = l_req_material_rec.visit_task_id,
2733 organization_id = l_req_material_rec.organization_id,
2734 item_group_id = l_req_material_rec.item_group_id,
2735 rt_oper_material_id = l_req_material_rec.rt_oper_material_id,
2736 workorder_operation_id = l_req_material_rec.workorder_operation_id,
2737 attribute_category = l_req_material_rec.attribute_category,
2738 attribute1 = l_req_material_rec.attribute1,
2739 attribute2 = l_req_material_rec.attribute2,
2740 attribute3 = l_req_material_rec.attribute3,
2741 attribute4 = l_req_material_rec.attribute4,
2742 attribute5 = l_req_material_rec.attribute5,
2743 attribute6 = l_req_material_rec.attribute6,
2744 attribute7 = l_req_material_rec.attribute7,
2745 attribute8 = l_req_material_rec.attribute8,
2746 attribute9 = l_req_material_rec.attribute9,
2747 attribute10 = l_req_material_rec.attribute10,
2748 attribute11 = l_req_material_rec.attribute11,
2749 attribute12 = l_req_material_rec.attribute12,
2750 attribute13 = l_req_material_rec.attribute13,
2751 attribute14 = l_req_material_rec.attribute14,
2752 attribute15 = l_req_material_rec.attribute15,
2753 last_update_date = sysdate,
2754 last_updated_by = fnd_global.user_id,
2755 last_update_login = fnd_global.login_id
2756 WHERE scheduled_material_id = p_x_req_material_tbl(i).schedule_material_id;
2757 --
2758
2759 END IF; -- p_x_req_material_tbl(i).schedule_material_id
2760 END LOOP;
2761 END IF; -- p_x_req_material_tbl.COUNT
2762 --
2763 ELSE
2764 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2765 RAISE Fnd_Api.G_EXC_ERROR;
2766 END IF;-- Return status
2767
2768 IF X_return_status = 'S' THEN
2769 --Check for Profile Option value If 'Y' Call
2770 IF FND_PROFILE.value( 'AHL_MTL_REQ_NOTIFICATION_ENABLED') = 'Y' THEN
2771
2772 --Send Materil Notification
2773 MATERIAL_NOTIFICATION
2774 (
2775 p_api_version => p_api_version,
2776 p_init_msg_list => p_init_msg_list,
2777 p_commit => p_commit,
2778 p_validation_level => p_validation_level,
2779 p_Req_Material_Tbl => p_x_req_material_tbl,
2780 x_return_status => l_return_status,
2781 x_msg_count => l_msg_count,
2782 x_msg_data => l_msg_data);
2783 END IF;
2784 END IF;
2785
2786 IF G_DEBUG='Y' THEN
2787 AHL_DEBUG_PUB.debug( 'END OF UPDATE PROCESS');
2788 END IF;
2789 --
2790 ------------------------End of Body---------------------------------------
2791 --Standard check to count messages
2792 l_msg_count := Fnd_Msg_Pub.count_msg;
2793
2794 --Change made on Nov 17, 2005 by jeli due to bug 4742895.
2795 --Ignore messages in stack if return status is S after calls to EAM APIs.
2796 /*
2797 IF l_msg_count > 0 THEN
2798 X_msg_count := l_msg_count;
2799 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
2800 RAISE Fnd_Api.G_EXC_ERROR;
2801 END IF;
2802 */
2803
2804 --Standard check for commit
2805 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
2806 COMMIT;
2807 END IF;
2808 -- Debug info
2809 IF G_DEBUG='Y' THEN
2810 Ahl_Debug_Pub.debug( 'End of public api Update Material Reqst','+PPMRP+');
2811 -- Check if API is called in debug mode. If yes, disable debug.
2812 Ahl_Debug_Pub.disable_debug;
2813 END IF;
2814
2815 EXCEPTION
2816 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2817 ROLLBACK TO update_material_reqst;
2818 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2819 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2820 p_count => x_msg_count,
2821 p_data => x_msg_data);
2822 IF G_DEBUG='Y' THEN
2823 AHL_DEBUG_PUB.log_app_messages (
2824 x_msg_count, x_msg_data, 'ERROR' );
2825 AHL_DEBUG_PUB.debug( 'ahl_pp_materials_pvt. Update Material Reqst','+PPMRP+');
2826 -- Check if API is called in debug mode. If yes, disable debug.
2827 AHL_DEBUG_PUB.disable_debug;
2828 END IF;
2829 WHEN FND_API.G_EXC_ERROR THEN
2830 ROLLBACK TO update_material_reqst;
2831 X_return_status := FND_API.G_RET_STS_ERROR;
2832 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2833 p_count => x_msg_count,
2834 p_data => X_msg_data);
2835 IF G_DEBUG='Y' THEN
2836 -- Debug info.
2837 AHL_DEBUG_PUB.log_app_messages (
2838 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
2839 AHL_DEBUG_PUB.debug( 'ahl_pp_materials_pvt. Update Material Reqst','+PPMRP+');
2840 -- Check if API is called in debug mode. If yes, disable debug.
2841 AHL_DEBUG_PUB.disable_debug;
2842 END IF;
2843 WHEN OTHERS THEN
2844 ROLLBACK TO update_material_reqst;
2845 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2846 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2847 THEN
2848 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PP_MATERIALS_PVT',
2849 p_procedure_name => 'UPDATE_MATERIAL_REQST',
2850 p_error_text => SUBSTR(SQLERRM,1,240));
2851 END IF;
2852 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
2853 p_count => x_msg_count,
2854 p_data => X_msg_data);
2855 IF G_DEBUG='Y' THEN
2856 -- Debug info.
2857 AHL_DEBUG_PUB.log_app_messages (
2858 x_msg_count, x_msg_data, 'SQL ERROR' );
2859 AHL_DEBUG_PUB.debug( 'ahl_pp_materials_pvt. Update Material Reqst','+PPMRP+');
2860 -- Check if API is called in debug mode. If yes, disable debug.
2861 AHL_DEBUG_PUB.disable_debug;
2862 END IF;
2863 END Update_Material_Reqst;
2864 -- Start of Comments --
2865 -- Procedure name : Remove_Material_Reqst
2866 -- Type : Private
2867 -- Function : Updates schedule material table with request quantity to zero,
2868 -- Calls Eam APi to remove material request
2869 -- Pre-reqs :
2870 -- Parameters :
2871 --
2872 -- Standard IN Parameters :
2873 -- p_api_version IN NUMBER Required
2874 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
2875 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
2876 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
2877 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
2878 -- p_module_type IN VARCHAR2 Default NULL.
2879 --
2880 -- Standard OUT Parameters :
2881 -- x_return_status OUT VARCHAR2 Required
2882 -- x_msg_count OUT NUMBER Required
2883 -- x_msg_data OUT VARCHAR2 Required
2884 --
2885 -- Remove Material Request Parameters:
2886 -- p_x_req_material_tbl IN OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type,
2887 -- Contains material information to perform material reservation
2888 --
2889 -- Version :
2890 -- Initial Version 1.0
2891 --
2892 -- End of Comments.
2893 PROCEDURE Remove_Material_Request (
2894 p_api_version IN NUMBER,
2895 p_init_msg_list IN VARCHAR2 := Fnd_Api.g_false,
2896 p_commit IN VARCHAR2 := Fnd_Api.g_false,
2897 p_validation_level IN NUMBER := Fnd_Api.g_valid_level_full,
2898 p_module_type IN VARCHAR2 := 'JSP',
2899 p_x_req_material_tbl IN OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type,
2900 x_return_status OUT NOCOPY VARCHAR2,
2901 x_msg_count OUT NOCOPY NUMBER,
2902 x_msg_data OUT NOCOPY VARCHAR2
2903 )
2904 IS
2905 --Get job number
2906 CURSOR Get_job_details(c_workorder_id IN NUMBER)
2907 IS
2908 SELECT workorder_name
2909 FROM ahl_workorders
2910 WHERE workorder_id = c_workorder_id;
2911 -- Bug # 6680137 - begin.
2912 CURSOR c_get_wo_status(c_workorder_id IN NUMBER)
2913 IS
2914 SELECT
2915 AWO.status_code
2916 FROM
2917 AHL_WORKORDERS AWO
2918 WHERE
2919 workorder_id = c_workorder_id;
2920 -- Bug # 6680137 - end
2921 -- Get schedule material details
2922 CURSOR Get_Req_Matrl_cur (c_schedule_material_id IN NUMBER)
2923 IS
2924 /*
2925 SELECT B.scheduled_material_id,
2926 B.inventory_item_id,
2927 B.object_version_number,
2928 B.requested_date,
2929 B.organization_id,
2930 B.visit_id,
2931 B.visit_task_id,
2932 B.requested_quantity,
2933 B.workorder_operation_id,
2934 B.operation_sequence,
2935 B.item_group_id,
2936 B.uom,
2937 B.rt_oper_material_id,
2938 B.department_id,
2939 B.workorder_name,
2940 B.wip_entity_id
2941 FROM AHL_SCHEDULE_MATERIALS A,
2942 AHL_JOB_OPER_MATERIALS_V B
2943 WHERE A.SCHEDULED_MATERIAL_ID = B.SCHEDULED_MATERIAL_ID
2944 AND A.SCHEDULED_MATERIAL_ID = c_schedule_material_id;
2945 */
2946 /*
2947 * R12 Perf Tuning
2948 * Balaji modified the query to use only base tables
2949 * instead of AHL_JOB_OPER_MATERIALS_V. Bug # 4919273
2950 */
2951 SELECT
2952 ASML.scheduled_material_id,
2953 ASML.inventory_item_id,
2954 ASML.object_version_number,
2955 wipr.date_required requested_date,
2956 AVST.organization_id,
2957 AVST.visit_id,
2958 ASML.visit_task_id,
2959 wipr.REQUIRED_QUANTITY requested_quantity,
2960 ASML.workorder_operation_id,
2961 wipr.operation_seq_num operation_sequence,
2962 ASML.item_group_id,
2963 MSIV.PRIMARY_UNIT_OF_MEASURE uom,
2964 ASML.rt_oper_material_id,
2965 AVST.department_id,
2966 AWOS.workorder_name,
2967 AWOS.wip_entity_id
2968 FROM
2969 AHL_WORKORDERS AWOS,
2970 AHL_SCHEDULE_MATERIALS ASML,
2971 wip_requirement_operations wipr,
2972 MTL_SYSTEM_ITEMS_VL MSIV,
2973 AHL_VISITS_VL AVST,
2974 AHL_WORKORDER_OPERATIONS AWOP,
2975 -- added for FP bug# 6802777
2976 WIP_OPERATIONS WOP
2977 WHERE
2978 AWOP.WORKORDER_OPERATION_ID = ASML.WORKORDER_OPERATION_ID AND
2979 AWOS.VISIT_TASK_ID = ASML.VISIT_TASK_ID AND
2980 ASML.VISIT_ID = AVST.VISIT_ID AND
2981 awos.wip_entity_id = wipr.wip_entity_id AND
2982 asml.operation_sequence = wipr.operation_seq_num AND
2983 asml.inventory_item_id = wipr.inventory_item_id AND
2984 asml.organization_id = wipr.organization_id AND
2985 asml.INVENTORY_ITEM_ID = MSIV.INVENTORY_ITEM_ID AND
2986 ASML.ORGANIZATION_ID = MSIV.ORGANIZATION_ID AND
2987 wop.wip_entity_id = wipr.wip_entity_id AND
2988 wop.operation_seq_num = wipr.operation_seq_num AND
2989 asml.status IN ('ACTIVE', 'IN-SERVICE') AND
2990 ASML.SCHEDULED_MATERIAL_ID = c_schedule_material_id;
2991
2992 -- R12: Serial Reservation changes.
2993 -- get count on existing reservations.
2994 CURSOR get_count_resrv_cur (c_item_id IN NUMBER,
2995 c_org_id IN NUMBER,
2996 c_wip_entity_id IN NUMBER,
2997 c_oper_seq_num IN NUMBER) IS
2998 SELECT nvl(SUM(mrv.primary_reservation_quantity), 0) reserved_quantity
2999 FROM mtl_reservations MRV
3000 WHERE MRV.INVENTORY_ITEM_ID = c_item_id
3001 AND MRV.EXTERNAL_SOURCE_CODE = 'AHL'
3002 AND MRV.DEMAND_SOURCE_HEADER_ID = c_wip_entity_id
3003 AND MRV.DEMAND_SOURCE_LINE_ID = c_oper_seq_num;
3004
3005 -- Standard local variable
3006 l_api_name CONSTANT VARCHAR2(30) := 'REMOVE_MATERIAL_REQUEST';
3007 l_api_version CONSTANT NUMBER := 1.0;
3008 l_return_status VARCHAR2(1);
3009 l_msg_data VARCHAR2(200);
3010 l_msg_count NUMBER;
3011 l_workorder_name VARCHAR2(80);
3012 --
3013 l_object_version_number NUMBER;
3014 --
3015 l_req_material_rec Req_Material_Rec_Type;
3016 l_req_material_tbl Req_Material_Tbl_Type;
3017 l_material_rec Get_Req_Matrl_cur%ROWTYPE;
3018 -- Variables required for wip jobs call
3019 l_ahl_wip_work_rec AHL_WIP_JOB_PVT.ahl_wo_rec_type;
3020 l_ahl_wip_oper_tbl AHL_WIP_JOB_PVT.ahl_wo_op_tbl_type ;
3021 l_ahl_wip_rsrc_tbl AHL_WIP_JOB_PVT.ahl_wo_res_tbl_type;
3022 l_ahl_wip_mtrl_tbl AHL_WIP_JOB_PVT.ahl_wo_mtl_tbl_type;
3023 l_default VARCHAR2(30);
3024 j NUMBER;
3025
3026 l_reserved_quantity NUMBER;
3027
3028 -- Bug # 6680137 - begin
3029 l_wo_status VARCHAR2(30);
3030 -- Bug # 6680137 - end
3031 BEGIN
3032 --------------------Initialize ----------------------------------
3033 -- Standard Start of API savepoint
3034 SAVEPOINT remove_material_request;
3035 -- Check if API is called in debug mode. If yes, enable debug.
3036 IF G_DEBUG='Y' THEN
3037 Ahl_Debug_Pub.enable_debug;
3038 -- Debug info.
3039 Ahl_Debug_Pub.debug( 'enter ahl_pp_materials_pvt Remove Material Request ','+MAATP+');
3040 --
3041 END IF;
3042 -- Standard call to check for call compatibility.
3043 IF Fnd_Api.to_boolean(p_init_msg_list)
3044 THEN
3045 Fnd_Msg_Pub.initialize;
3046 END IF;
3047 -- Initialize API return status to success
3048 x_return_status := Fnd_Api.G_RET_STS_SUCCESS;
3049 -- Initialize message list if p_init_msg_list is set to TRUE.
3050 IF NOT Fnd_Api.COMPATIBLE_API_CALL(l_api_version,
3051 p_api_version,
3052 l_api_name,G_PKG_NAME)
3053 THEN
3054 RAISE Fnd_Api.G_EXC_UNEXPECTED_ERROR;
3055 END IF;
3056
3057 ------------------------Start API Body ---------------------------------
3058 IF p_x_req_material_tbl.COUNT > 0 THEN
3059 FOR i IN p_x_req_material_tbl.FIRST..p_x_req_material_tbl.LAST
3060 LOOP
3061 -- Value to ID Conversion
3062 --Get visit task id
3063 IF (p_x_req_material_tbl(i).workorder_id IS NOT NULL AND
3064 p_x_req_material_tbl(i).workorder_id <> Fnd_Api.G_MISS_NUM )
3065 THEN
3066 --
3067 -- Bug # 6680137 - start
3068 OPEN Get_job_details(p_x_req_material_tbl(i).workorder_id);
3069 FETCH Get_job_details INTO l_workorder_name;
3070 IF Get_job_details%NOTFOUND THEN
3071 Fnd_Message.SET_NAME('AHL','AHL_PP_WO_ORD_NOT_EXISTS');
3072 Fnd_Msg_Pub.ADD;
3073 CLOSE Get_job_details;
3074 RAISE FND_API.G_EXC_ERROR;
3075 END IF;
3076 CLOSE Get_job_details;
3077 -- Bug # 6680137 - end
3078 -- Balaji added this validation for Bug # 6680137 - begin.
3079 -- When work order is in status cancelled, complete no-charge or closed
3080 -- material deletion should be disallowed.
3081 OPEN c_get_wo_status(p_x_req_material_tbl(i).workorder_id);
3082 FETCH c_get_wo_status INTO l_wo_status;
3083 CLOSE c_get_wo_status;
3084
3085 IF l_wo_status IN ('7', '5', '12')
3086 THEN
3087 Fnd_Message.SET_NAME('AHL','AHL_PP_WO_STATUS_INVALID');
3088 Fnd_Msg_Pub.ADD;
3089 RAISE FND_API.G_EXC_ERROR;
3090 END IF;
3091 -- Bug # 6680137 - end
3092 END IF;
3093
3094 -- rroy
3095 -- ACL Changes
3096 l_return_status := AHL_PRD_UTIL_PKG.Is_Unit_Locked
3097 (
3098 p_workorder_id => p_x_req_material_tbl(i).workorder_id,
3099 p_ue_id => NULL,
3100 p_visit_id => NULL,
3101 p_item_instance_id => NULL);
3102 IF l_return_status = FND_API.G_TRUE THEN
3103 FND_MESSAGE.Set_Name('AHL', 'AHL_PP_DEL_MTL_UNTLCKD');
3104 FND_MESSAGE.Set_Token('WO_NAME', l_workorder_name);
3105 FND_MSG_PUB.ADD;
3106 RAISE FND_API.G_EXC_ERROR;
3107 END IF;
3108 -- rroy
3109 -- ACL Changes
3110
3111 -- Check for Schedule Material ID
3112 IF (p_x_req_material_tbl(i).schedule_material_id IS NULL OR
3113 p_x_req_material_tbl(i).schedule_material_id = FND_API.G_MISS_NUM) THEN
3114 Fnd_Message.SET_NAME('AHL','AHL_PP_SCH_MATRL_REQUIRED');
3115 Fnd_Msg_Pub.ADD;
3116 END IF;
3117 --
3118 IF G_DEBUG='Y' THEN
3119 Ahl_Debug_Pub.debug( 'Obj Number:'||p_x_req_material_tbl(i).object_version_number);
3120 Ahl_Debug_Pub.debug( 'Sch mat Id:'||p_x_req_material_tbl(i).schedule_material_id);
3121 END IF;
3122 -- Check for object version number
3123 IF (p_x_req_material_tbl(i).object_version_number IS NOT NULL AND
3124 p_x_req_material_tbl(i).object_version_number <> FND_API.G_MISS_NUM) THEN
3125 --
3126 SELECT object_version_number,requested_quantity INTO l_object_version_number,
3127 p_x_req_material_tbl(i).requested_quantity
3128 FROM ahl_schedule_materials
3129 WHERE scheduled_material_id = p_x_req_material_tbl(i).schedule_material_id
3130 FOR UPDATE OF STATUS NOWAIT;
3131 --
3132 IF p_x_req_material_tbl(i).object_version_number <> l_object_version_number THEN
3133 Fnd_Message.SET_NAME('AHL','AHL_PP_RECORD_CHANGED');
3134 Fnd_Msg_Pub.ADD;
3135 RAISE Fnd_Api.G_EXC_ERROR;
3136 END IF;
3137 --
3138 END IF;
3139
3140 --Standard check to count messages
3141 l_msg_count := Fnd_Msg_Pub.count_msg;
3142
3143 IF l_msg_count > 0 THEN
3144 X_msg_count := l_msg_count;
3145 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3146 RAISE Fnd_Api.G_EXC_ERROR;
3147 END IF;
3148
3149 END LOOP;
3150 END IF;
3151 --
3152 IF G_DEBUG='Y' THEN
3153 Ahl_Debug_Pub.debug( 'before wip jobs call:');
3154 END IF;
3155 --
3156 IF p_x_req_material_tbl.COUNT >0 THEN
3157 j := 1;
3158 FOR i in p_x_req_material_tbl.FIRST..p_x_req_material_tbl.LAST
3159 LOOP
3160 --
3161 OPEN Get_Req_Matrl_cur(p_x_req_material_tbl(i).schedule_material_id);
3162 FETCH Get_Req_Matrl_cur INTO l_material_rec;
3163 CLOSE Get_Req_matrl_cur;
3164 --
3165 --Assign to output
3166 l_req_material_tbl(j).JOB_NUMBER := l_material_rec.workorder_name;
3167 l_req_material_tbl(j).WIP_ENTITY_ID := l_material_rec.wip_entity_id;
3168 l_req_material_tbl(j).WORKORDER_ID := p_x_req_material_tbl(i).workorder_id;
3169 l_req_material_tbl(j).OPERATION_SEQUENCE := l_material_rec.operation_sequence;
3170 l_req_material_tbl(j).INVENTORY_ITEM_ID := l_material_rec.inventory_item_id;
3171 l_req_material_tbl(j).ORGANIZATION_ID := l_material_rec.organization_id;
3172 l_req_material_tbl(j).DEPARTMENT_ID := l_material_rec.department_id;
3173 -- fix for bug# 5549135
3174 --l_req_material_tbl(j).MRP_NET_FLAG := 1;
3175 l_req_material_tbl(j).MRP_NET_FLAG := 2;
3176 l_req_material_tbl(j).QUANTITY_PER_ASSEMBLY := l_material_rec.requested_quantity;
3177 l_req_material_tbl(j).REQUESTED_QUANTITY := l_material_rec.requested_quantity;
3178 l_req_material_tbl(j).SUPPLY_TYPE := NULL;
3179 l_req_material_tbl(j).LOCATION := NULL;
3180 l_req_material_tbl(j).SUB_INVENTORY := NULL;
3181 l_req_material_tbl(j).REQUESTED_DATE := l_material_rec.requested_date;
3182 l_req_material_tbl(j).OPERATION_FLAG := 'D';
3183 --
3184 j := j+1;
3185
3186 -- Added for R12: Serial Reservation.
3187 -- check for reservations, if any.
3188 OPEN get_count_resrv_cur(l_material_rec.inventory_item_id,
3189 l_material_rec.organization_id,
3190 l_material_rec.wip_entity_id,
3191 l_material_rec.operation_sequence);
3192 FETCH get_count_resrv_cur INTO l_reserved_quantity;
3193 CLOSE get_count_resrv_cur;
3194
3195 IF (l_reserved_quantity > 0) THEN
3196 IF G_DEBUG='Y' THEN
3197 AHL_DEBUG_PUB.debug('Reserved quantity for sch. material ID:' || p_x_req_material_tbl(i).schedule_material_id || ' is: ' || l_reserved_quantity || 'for INV ID: ' || l_material_rec.inventory_item_id);
3198
3199 AHL_DEBUG_PUB.debug('Before calling delete reservation api');
3200 END IF;
3201
3202 -- delete reservations.
3203 AHL_RSV_RESERVATIONS_PVT.DELETE_RESERVATION(
3204 p_api_version => 1.0,
3205 p_init_msg_list => FND_API.G_FALSE,
3206 p_commit => FND_API.G_FALSE,
3207 p_module_type => NULL,
3208 x_return_status => x_return_status,
3209 x_msg_count => x_msg_count,
3210 x_msg_data => x_msg_data,
3211 p_scheduled_material_id => p_x_req_material_tbl(i).schedule_material_id);
3212
3213 IF G_DEBUG='Y' THEN
3214 AHL_DEBUG_PUB.debug('After calling delete reservation api. Return status:' || x_return_status);
3215 END IF;
3216
3217 -- check return status.
3218 IF (l_return_status = FND_API.G_RET_STS_UNEXP_ERROR) THEN
3219 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3220 ELSIF (l_return_status = FND_API.G_RET_STS_ERROR) THEN
3221 RAISE FND_API.G_EXC_ERROR;
3222 END IF;
3223
3224 END IF;
3225
3226 END LOOP;
3227
3228 END IF; --Material tbl
3229 IF G_DEBUG='Y' THEN
3230 AHL_DEBUG_PUB.debug('beforer Eam wip job call');
3231 END IF;
3232 -- Call wip job api
3233 AHL_EAM_JOB_PVT.process_material_req
3234 (
3235 p_api_version => l_api_version,
3236 p_init_msg_list => p_init_msg_list,
3237 p_commit => p_commit,
3238 p_validation_level => p_validation_level,
3239 p_default => l_default,
3240 p_module_type => p_module_type,
3241 x_return_status => l_return_status,
3242 x_msg_count => l_msg_count,
3243 x_msg_data => l_msg_data,
3244 p_material_req_tbl => l_req_material_tbl
3245 );
3246 --
3247 IF G_DEBUG='Y' THEN
3248 AHL_DEBUG_PUB.debug('after wip job call');
3249 END IF;
3250 IF l_return_Status = 'S' THEN
3251
3252 --Remove the records
3253 IF p_x_req_material_tbl.COUNT > 0 THEN
3254 FOR i IN p_x_req_material_tbl.FIRST..p_x_req_material_tbl.LAST
3255 LOOP
3256 -- If schedule date is not null then update to zero because collection has been done
3257 IF p_x_req_material_tbl(i).schedule_material_id IS NOT NULL THEN
3258 -- Update schedule materials table requested quantity to zero
3259 UPDATE AHL_SCHEDULE_MATERIALS
3260 SET requested_quantity = 0,
3261 status = 'DELETED',
3262 object_version_number = p_x_req_material_tbl(i).object_version_number + 1
3263 WHERE SCHEDULED_MATERIAL_ID = p_x_req_material_tbl(i).schedule_material_id;
3264 END IF;
3265 --
3266 IF G_DEBUG='Y' THEN
3267 AHL_DEBUG_PUB.debug('after set request quantity to zero');
3268 END IF;
3269 --
3270 END LOOP;
3271 END IF;
3272 --
3273 ELSE
3274 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3275 RAISE Fnd_Api.G_EXC_ERROR;
3276 --
3277 END IF; --Status
3278 ---------------------------End of Body---------------------------------------
3279 --Standard check to count messages
3280 l_msg_count := Fnd_Msg_Pub.count_msg;
3281
3282 --Change made on Nov 17, 2005 by jeli due to bug 4742895.
3283 --Ignore messages in stack if return status is S after calls to EAM APIs.
3284 /*
3285 IF l_msg_count > 0 THEN
3286 X_msg_count := l_msg_count;
3287 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3288 RAISE Fnd_Api.G_EXC_ERROR;
3289 END IF;
3290 */
3291 --Standard check for commit
3292 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
3293 COMMIT;
3294 END IF;
3295 -- Debug info
3296 IF G_DEBUG='Y' THEN
3297 Ahl_Debug_Pub.debug( 'End of private api Remove Material Request ','+MAMRP+');
3298 -- Check if API is called in debug mode. If yes, disable debug.
3299 Ahl_Debug_Pub.disable_debug;
3300 END IF;
3301
3302 EXCEPTION
3303 WHEN Fnd_Api.G_EXC_UNEXPECTED_ERROR THEN
3304 ROLLBACK TO remove_material_request;
3305 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3306 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
3307 p_count => x_msg_count,
3308 p_data => x_msg_data);
3309
3310 IF G_DEBUG='Y' THEN
3311 Ahl_Debug_Pub.log_app_messages (
3312 x_msg_count, x_msg_data, 'ERROR' );
3313 Ahl_Debug_Pub.debug( 'ahl_pp_materials_pvt. Remove Material Request ','+MAMRP+');
3314 -- Check if API is called in debug mode. If yes, disable debug.
3315 Ahl_Debug_Pub.disable_debug;
3316 END IF;
3317 WHEN Fnd_Api.G_EXC_ERROR THEN
3318 ROLLBACK TO remove_material_request;
3319 X_return_status := Fnd_Api.G_RET_STS_ERROR;
3320 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
3321 p_count => x_msg_count,
3322 p_data => X_msg_data);
3323 IF G_DEBUG='Y' THEN
3324 -- Debug info.
3325 Ahl_Debug_Pub.log_app_messages (
3326 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
3327 Ahl_Debug_Pub.debug( 'ahl_pp_materials_pvt. Remove Material Request ','+MAMRP+');
3328 -- Check if API is called in debug mode. If yes, disable debug.
3329 Ahl_Debug_Pub.disable_debug;
3330 END IF;
3331 WHEN OTHERS THEN
3332 ROLLBACK TO remove_material_request;
3333 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3334 IF Fnd_Msg_Pub.check_msg_level(Fnd_Msg_Pub.G_MSG_LVL_UNEXP_ERROR)
3335 THEN
3336 Fnd_Msg_Pub.add_exc_msg(p_pkg_name => 'AHL_PP_MATERIALS_PVT',
3337 p_procedure_name => 'REMOVE_MATERIAL_REQUEST',
3338 p_error_text => SUBSTR(SQLERRM,1,240));
3339 END IF;
3340 Fnd_Msg_Pub.count_and_get( p_encoded => Fnd_Api.G_FALSE,
3341 p_count => x_msg_count,
3342 p_data => X_msg_data);
3343
3344 IF G_DEBUG='Y' THEN
3345 -- Debug info.
3346 Ahl_Debug_Pub.log_app_messages (
3347 x_msg_count, x_msg_data, 'SQL ERROR' );
3348 Ahl_Debug_Pub.debug( 'ahl_pp_materials_pvt. Remove Material Request ','+MTMRP+');
3349 -- Check if API is called in debug mode. If yes, disable debug.
3350 Ahl_Debug_Pub.disable_debug;
3351 END IF;
3352 END Remove_Material_Request;
3353
3354
3355 -- Public Procedure Definitions follow --
3356 -----------------------------------------
3357 -- Start of Comments --
3358 -- Procedure name : Process_Material_Request
3359 -- Type : Private
3360 -- Function : Process material reservations through MRP for Routine and Non
3361 -- Routine jobs based on operation flag
3362 -- Pre-reqs :
3363 -- Parameters :
3364 --
3365 -- Standard IN Parameters :
3366 -- p_api_version IN NUMBER Required
3367 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
3368 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
3369 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
3370 -- p_default IN VARCHAR2 Default FND_API.G_TRUE
3371 -- p_module_type IN VARCHAR2 Default NULL.
3372 --
3373 -- Standard OUT Parameters :
3374 -- x_return_status OUT VARCHAR2 Required
3375 -- x_msg_count OUT NUMBER Required
3376 -- x_msg_data OUT VARCHAR2 Required
3377 --
3378 -- Process Material Request Parameters:
3379 -- p_x_req_material_tbl IN OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type,
3380 -- Contains material information to perform material reservation depending
3381 -- on operation flag
3382 --
3383 -- Version :
3384 -- Initial Version 1.0
3385 --
3386 -- End of Comments.
3387
3388 PROCEDURE Process_Material_Request (
3389 p_api_version IN NUMBER,
3390 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
3391 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
3392 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
3393 p_module_type IN VARCHAR2 := NULL,
3394 p_x_req_material_tbl IN OUT NOCOPY AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type,
3395 x_return_status OUT NOCOPY VARCHAR2,
3396 x_msg_count OUT NOCOPY NUMBER,
3397 x_msg_data OUT NOCOPY VARCHAR2
3398 )
3399 IS
3400 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_MATERIAL_REQUEST';
3401 l_api_version CONSTANT NUMBER := 1.0;
3402 l_msg_count NUMBER;
3403 l_return_status VARCHAR2(1);
3404 l_job_return_status VARCHAR2(1);
3405 l_msg_data VARCHAR2(2000);
3406 l_interface_flag VARCHAR2(1) := NULL;
3407 l_called_module VARCHAR2(10) := 'UI';
3408 l_req_material_tbl AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type;
3409 l_req_cr_material_tbl AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type;
3410 l_req_up_material_tbl AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type;
3411 l_req_re_material_tbl AHL_PP_MATERIALS_PVT.Req_Material_Tbl_Type;
3412 l_commit VARCHAR2(30) := Fnd_Api.G_FALSE;
3413
3414 BEGIN
3415 --------------------Initialize ----------------------------------
3416 -- Standard Start of API savepoint
3417 SAVEPOINT process_material_request;
3418 -- Check if API is called in debug mode. If yes, enable debug.
3419 IF G_DEBUG='Y' THEN
3420 AHL_DEBUG_PUB.enable_debug;
3421 -- Debug info.
3422 AHL_DEBUG_PUB.debug( 'enter ahl_pp_materias_pvt. process material request','+PPMRP+');
3423 --
3424 END IF;
3425 -- Standard call to check for call compatibility.
3426 IF FND_API.to_boolean(p_init_msg_list)
3427 THEN
3428 FND_MSG_PUB.initialize;
3429 END IF;
3430 -- Initialize API return status to success
3431 x_return_status := FND_API.G_RET_STS_SUCCESS;
3432 -- Initialize message list if p_init_msg_list is set to TRUE.
3433 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
3434 p_api_version,
3435 l_api_name,G_PKG_NAME)
3436 THEN
3437 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
3438 END IF;
3439 --------------------Start of API Body-----------------------------------
3440 IF p_x_req_material_tbl.COUNT > 0 THEN
3441 FOR i IN p_x_req_material_tbl.FIRST..p_x_req_material_tbl.LAST
3442 LOOP
3443 IF p_x_req_material_tbl(i).operation_flag = 'C'
3444 THEN
3445 --
3446 l_req_cr_material_tbl(i) := p_x_req_material_tbl(i);
3447 --
3448 ELSIF p_x_req_material_tbl(i).operation_flag = 'U'
3449 THEN
3450
3451 l_req_up_material_tbl(i) := p_x_req_material_tbl(i);
3452
3453 ELSIF p_x_req_material_tbl(i).operation_flag = 'D'
3454 THEN
3455 --
3456 l_req_re_material_tbl(i) := p_x_req_material_tbl(i);
3457 END IF;
3458 END LOOP;
3459 END IF;
3460 --Call Private API to process
3461 IF l_req_cr_material_tbl.COUNT > 0 THEN
3462 -- Call create material request
3463 Create_Material_Reqst
3464 (
3465 p_api_version => p_api_version,
3466 p_init_msg_list => p_init_msg_list,
3467 p_commit => l_commit,
3468 p_validation_level => p_validation_level,
3469 p_interface_flag => l_interface_flag,
3470 p_x_req_material_tbl => l_req_cr_material_tbl,
3471 x_job_return_status => l_job_return_status,
3472 x_return_status => l_return_status,
3473 x_msg_count => l_msg_count,
3474 x_msg_data => l_msg_data
3475 ) ;
3476 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
3477 l_msg_count := FND_MSG_PUB.count_msg;
3478 IF l_msg_count > 0 THEN
3479 RAISE FND_API.G_EXC_ERROR;
3480 END IF;
3481 END IF;
3482 FOR i IN l_req_cr_material_tbl.First..l_req_cr_material_tbl.LAST LOOP
3483 p_x_req_material_tbl(i).SCHEDULE_MATERIAL_ID := l_req_cr_material_tbl(i).SCHEDULE_MATERIAL_ID;
3484 END LOOP;
3485 END IF;
3486 IF l_req_up_material_tbl.COUNT > 0 THEN
3487 -- Call Update material request
3488 Update_Material_Reqst
3489 (
3490 p_api_version => p_api_version,
3491 p_init_msg_list => p_init_msg_list,
3492 p_commit => l_commit,
3493 p_validation_level => p_validation_level,
3494 p_module_type => p_module_type,
3495 p_x_req_material_tbl => l_req_up_material_tbl,
3496 x_return_status => l_return_status,
3497 x_msg_count => l_msg_count,
3498 x_msg_data => l_msg_data
3499 );
3500
3501 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
3502 l_msg_count := FND_MSG_PUB.count_msg;
3503 IF l_msg_count > 0 THEN
3504 RAISE FND_API.G_EXC_ERROR;
3505 END IF;
3506 END IF;
3507 END IF;
3508 IF l_req_re_material_tbl.COUNT > 0 THEN
3509 -- Call Remove material request
3510 Remove_Material_Request
3511 (
3512 p_api_version => p_api_version,
3513 p_init_msg_list => p_init_msg_list,
3514 p_commit => l_commit,
3515 p_validation_level => p_validation_level,
3516 p_module_type => p_module_type,
3517 p_x_req_material_tbl => l_req_re_material_tbl,
3518 x_return_status => l_return_status,
3519 x_msg_count => l_msg_count,
3520 x_msg_data => l_msg_data
3521 );
3522
3523 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
3524 l_msg_count := FND_MSG_PUB.count_msg;
3525 IF l_msg_count > 0 THEN
3526 RAISE FND_API.G_EXC_ERROR;
3527 END IF;
3528 END IF;
3529
3530 END IF;
3531 ------------------------End of Body---------------------------------------
3532 --Standard check to count messages
3533 l_msg_count := Fnd_Msg_Pub.count_msg;
3534
3535 IF l_msg_count > 0 THEN
3536 X_msg_count := l_msg_count;
3537 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
3538 RAISE Fnd_Api.G_EXC_ERROR;
3539 END IF;
3540
3541 --Standard check for commit
3542 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
3543 COMMIT;
3544 END IF;
3545 -- Debug info
3546 IF G_DEBUG='Y' THEN
3547 Ahl_Debug_Pub.debug( 'End of public api Process Material Request','+PPMRP+');
3548 -- Check if API is called in debug mode. If yes, disable debug.
3549 Ahl_Debug_Pub.disable_debug;
3550 --
3551 END IF;
3552 EXCEPTION
3553 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
3554 ROLLBACK TO process_material_request;
3555 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3556 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3557 p_count => x_msg_count,
3558 p_data => x_msg_data);
3559 IF G_DEBUG='Y' THEN
3560 AHL_DEBUG_PUB.log_app_messages (
3561 x_msg_count, x_msg_data, 'ERROR' );
3562 AHL_DEBUG_PUB.debug( 'ahl_pp_materials_pvt. Process Material Request','+PPMRP+');
3563 -- Check if API is called in debug mode. If yes, disable debug.
3564 AHL_DEBUG_PUB.disable_debug;
3565 END IF;
3566 WHEN FND_API.G_EXC_ERROR THEN
3567 ROLLBACK TO process_material_request;
3568 X_return_status := FND_API.G_RET_STS_ERROR;
3569 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3570 p_count => x_msg_count,
3571 p_data => X_msg_data);
3572 IF G_DEBUG='Y' THEN
3573 -- Debug info.
3574 AHL_DEBUG_PUB.log_app_messages (
3575 x_msg_count, x_msg_data, 'UNEXPECTED ERROR' );
3576 AHL_DEBUG_PUB.debug( 'ahl_pp_materials_pvt. Process Material Request','+PPMRP+');
3577 -- Check if API is called in debug mode. If yes, disable debug.
3578 AHL_DEBUG_PUB.disable_debug;
3579 --
3580 END IF;
3581 WHEN OTHERS THEN
3582 ROLLBACK TO process_material_request;
3583 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3584 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
3585 THEN
3586 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PP_MATERIALS_PVT',
3587 p_procedure_name => 'PROCESS_MATERIAL_REQUEST',
3588 p_error_text => SUBSTR(SQLERRM,1,240));
3589 END IF;
3590 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
3591 p_count => x_msg_count,
3592 p_data => X_msg_data);
3593 IF G_DEBUG='Y' THEN
3594 -- Debug info.
3595 AHL_DEBUG_PUB.log_app_messages (
3596 x_msg_count, x_msg_data, 'SQL ERROR' );
3597 AHL_DEBUG_PUB.debug( 'ahl_pp_materials_pvt. Process Material Request','+PPMRP+');
3598 -- Check if API is called in debug mode. If yes, disable debug.
3599 AHL_DEBUG_PUB.disable_debug;
3600 END IF;
3601 END Process_Material_Request;
3602 --
3603 -- aps obsoleted
3604 FUNCTION Get_Mrp_Net
3605 (p_schedule_material_id IN NUMBER,
3606 p_item_desc IN VARCHAR2)
3607 RETURN VARCHAR2 IS
3608 --
3609 CURSOR Check_material_cur (c_schedule_material_id IN NUMBER)
3610 IS
3611 SELECT scheduled_material_id,
3612 rt_oper_material_id
3613 FROM ahl_schedule_materials
3614 WHERE scheduled_material_id = c_schedule_material_id;
3615 --
3616 CURSOR Get_item_cur(c_segments IN VARCHAR2)
3617 IS
3618 SELECT distinct(inventory_item_id)
3619 FROM mtl_system_items_kfv
3620 WHERE concatenated_segments = c_segments;
3621 --
3622 l_return VARCHAR2(1);
3623 --
3624 l_inventory_item_id NUMBER;
3625 l_rt_oper_material_id NUMBER;
3626 l_schedule_material_id NUMBER;
3627 BEGIN
3628 --Check for schedule material id
3629 OPEN Check_material_cur(p_schedule_material_id);
3630 FETCH Check_material_cur INTO l_schedule_material_id,l_rt_oper_material_id;
3631 CLOSE Check_material_cur;
3632 --
3633 -- Get inventory item
3634 OPEN Get_item_cur(p_item_desc);
3635 FETCH Get_item_cur INTO l_inventory_item_id;
3636 CLOSE Get_item_cur;
3637 --
3638 IF l_rt_oper_material_id IS NOT NULL THEN
3639 l_return := 'N';
3640 ELSE
3641 l_return := 'Y';
3642 END IF;
3643 RETURN l_return;
3644 EXCEPTION
3645 WHEN OTHERS THEN
3646 RETURN NULL;
3647
3648 END Get_Mrp_Net;
3649 -- aps obsoleted
3650
3651 --
3652 FUNCTION GET_QTY_PER_ASBLY
3653 (p_schedule_material_id IN NUMBER,
3654 p_item_desc IN VARCHAR2 )
3655 RETURN NUMBER IS
3656 CURSOR Check_material_cur (c_schedule_material_id IN NUMBER)
3657 IS
3658 SELECT scheduled_material_id,
3659 rt_oper_material_id,requested_quantity
3660 FROM ahl_schedule_materials
3661 WHERE scheduled_material_id = c_schedule_material_id;
3662 --
3663 CURSOR Get_item_cur(c_segments IN VARCHAR2)
3664 IS
3665 SELECT distinct(inventory_item_id)
3666 FROM mtl_system_items_kfv
3667 WHERE concatenated_segments = c_segments;
3668 --
3669 l_inventory_item_id NUMBER;
3670 l_rt_oper_material_id NUMBER;
3671 l_schedule_material_id NUMBER;
3672 l_requested_quantity NUMBER;
3673 BEGIN
3674 --Check for schedule material id
3675 OPEN Check_material_cur(p_schedule_material_id);
3676 FETCH Check_material_cur INTO l_schedule_material_id,l_rt_oper_material_id,
3677 l_requested_quantity;
3678 CLOSE Check_material_cur;
3679 --
3680 -- Get inventory item
3681 OPEN Get_item_cur(p_item_desc);
3682 FETCH Get_item_cur INTO l_inventory_item_id;
3683 CLOSE Get_item_cur;
3684 --
3685 IF l_schedule_material_id IS NOT NULL THEN
3686 RETURN l_requested_quantity;
3687 END IF;
3688
3689 EXCEPTION
3690 WHEN OTHERS THEN
3691 RETURN NULL;
3692
3693 END Get_Qty_Per_Asbly;
3694 --
3695 -- Start of Comments --
3696 -- Procedure name : Log_Transaction_Record
3697 -- Type : Private
3698 -- Function : Writes the details about a transaction in the Log Table
3699 -- AHL_WO_OPERATION_TXNS
3700 -- Pre-reqs :
3701 -- Parameters :
3702 --
3703 -- Log_Transaction Parameters:
3704 -- p_trans_type_code IN VARCHAR2 Required
3705 -- p_load_type_code IN NUMBER Required
3706 -- p_transaction_type_code IN NUMBER Required
3707 -- p_workorder_operation_id IN NUMBER Default NULL,
3708 -- p_operation_resource_id IN NUMBER Default NULL,
3709 -- p_schedule_material_id IN NUMBER Default NULL,
3710 -- p_bom_resource_id IN NUMBER Default NULL,
3711 -- p_cost_basis_code IN NUMBER Default NULL,
3712 -- p_total_required IN NUMBER Default NULL,
3713 -- p_assigned_units IN NUMBER Default NULL,
3714 -- p_autocharge_type_code IN NUMBER Default NULL,
3715 -- p_standard_rate_flag_code IN NUMBER Default NULL,
3716 -- p_applied_resource_units IN NUMBER Default NULL,
3717 -- p_applied_resource_value IN NUMBER Default NULL,
3718 -- p_inventory_item_id IN NUMBER Default NULL,
3719 -- p_scheduled_quantity IN NUMBER Default NULL,
3720 -- p_scheduled_date IN DATE Default NULL,
3721 -- p_mrp_net_flag IN NUMBER Default NULL,
3722 -- p_quantity_per_assembly IN NUMBER Default NULL,
3723 -- p_required_quantity IN NUMBER Default NULL,
3724 -- p_supply_locator_id IN NUMBER Default NULL,
3725 -- p_supply_subinventory IN NUMBER Default NULL,
3726 -- p_date_required IN DATE Default NULL,
3727 -- p_operation_type_code IN VARCHAR2 Default NULL,
3728 -- p_sched_start_date IN DATE Default NULL,
3729 -- p_res_sched_end_date IN DATE Default NULL,
3730 -- p_op_scheduled_start_date IN DATE Default NULL,
3731 -- p_op_scheduled_end_date IN DATE Default NULL,
3732 -- p_op_actual_start_date IN DATE Default NULL,
3733 -- p_op_actual_end_date IN DATE Default NULL,
3734 -- p_attribute_category IN VARCHAR2 Default NULL,
3735 -- p_attribute1 IN VARCHAR2 Default NULL
3736 -- p_attribute2 IN VARCHAR2 Default NULL
3737 -- p_attribute3 IN VARCHAR2 Default NULL
3738 -- p_attribute4 IN VARCHAR2 Default NULL
3739 -- p_attribute5 IN VARCHAR2 Default NULL
3740 -- p_attribute6 IN VARCHAR2 Default NULL
3741 -- p_attribute7 IN VARCHAR2 Default NULL
3742 -- p_attribute8 IN VARCHAR2 Default NULL
3743 -- p_attribute9 IN VARCHAR2 Default NULL
3744 -- p_attribute10 IN VARCHAR2 Default NULL
3745 -- p_attribute11 IN VARCHAR2 Default NULL
3746 -- p_attribute12 IN VARCHAR2 Default NULL
3747 -- p_attribute13 IN VARCHAR2 Default NULL
3748 -- p_attribute14 IN VARCHAR2 Default NULL
3749 -- p_attribute15 IN VARCHAR2 Default NULL
3750 --
3751 -- Version :
3752 -- Initial Version 1.0
3753 --
3754 -- End of Comments.
3755 --
3756 PROCEDURE Log_Transaction_Record
3757 ( p_wo_operation_txn_id IN NUMBER,
3758 p_object_version_number IN NUMBER,
3759 p_last_update_date IN DATE,
3760 p_last_updated_by IN NUMBER,
3761 p_creation_date IN DATE,
3762 p_created_by IN NUMBER,
3763 p_last_update_login IN NUMBER,
3764 p_load_type_code IN NUMBER,
3765 p_transaction_type_code IN NUMBER,
3766 p_workorder_operation_id IN NUMBER := NULL,
3767 p_operation_resource_id IN NUMBER := NULL,
3768 p_schedule_material_id IN NUMBER := NULL,
3769 p_bom_resource_id IN NUMBER := NULL,
3770 p_cost_basis_code IN NUMBER := NULL,
3771 p_total_required IN NUMBER := NULL,
3772 p_assigned_units IN NUMBER := NULL,
3773 p_autocharge_type_code IN NUMBER := NULL,
3774 p_standard_rate_flag_code IN NUMBER := NULL,
3775 p_applied_resource_units IN NUMBER := NULL,
3776 p_applied_resource_value IN NUMBER := NULL,
3777 p_inventory_item_id IN NUMBER := NULL,
3778 p_scheduled_quantity IN NUMBER := NULL,
3779 p_scheduled_date IN DATE := NULL,
3780 p_mrp_net_flag IN NUMBER := NULL,
3781 p_quantity_per_assembly IN NUMBER := NULL,
3782 p_required_quantity IN NUMBER := NULL,
3783 p_supply_locator_id IN NUMBER := NULL,
3784 p_supply_subinventory IN NUMBER := NULL,
3785 p_date_required IN DATE := NULL,
3786 p_operation_type_code IN VARCHAR2 := NULL,
3787 p_res_sched_start_date IN DATE := NULL,
3788 p_res_sched_end_date IN DATE := NULL,
3789 p_op_scheduled_start_date IN DATE := NULL,
3790 p_op_scheduled_end_date IN DATE := NULL,
3791 p_op_actual_start_date IN DATE := NULL,
3792 p_op_actual_end_date IN DATE := NULL,
3793 p_attribute_category IN VARCHAR2 := NULL,
3794 p_attribute1 IN VARCHAR2 := NULL,
3795 p_attribute2 IN VARCHAR2 := NULL,
3796 p_attribute3 IN VARCHAR2 := NULL,
3797 p_attribute4 IN VARCHAR2 := NULL,
3798 p_attribute5 IN VARCHAR2 := NULL,
3799 p_attribute6 IN VARCHAR2 := NULL,
3800 p_attribute7 IN VARCHAR2 := NULL,
3801 p_attribute8 IN VARCHAR2 := NULL,
3802 p_attribute9 IN VARCHAR2 := NULL,
3803 p_attribute10 IN VARCHAR2 := NULL,
3804 p_attribute11 IN VARCHAR2 := NULL,
3805 p_attribute12 IN VARCHAR2 := NULL,
3806 p_attribute13 IN VARCHAR2 := NULL,
3807 p_attribute14 IN VARCHAR2 := NULL,
3808 p_attribute15 IN VARCHAR2 := NULL)
3809 IS
3810 BEGIN
3811 --
3812 INSERT INTO AHL_WO_OPERATIONS_TXNS
3813 ( wo_operation_txn_id ,
3814 object_version_number ,
3815 last_update_date ,
3816 last_updated_by ,
3817 creation_date ,
3818 created_by ,
3819 last_update_login ,
3820 load_type_code ,
3821 transaction_type_code ,
3822 workorder_operation_id ,
3823 operation_resource_id ,
3824 schedule_material_id ,
3825 bom_resource_id ,
3826 cost_basis_code ,
3827 total_required ,
3828 assigned_units ,
3829 autocharge_type_code ,
3830 standard_rate_flag_code ,
3831 applied_resource_units ,
3832 applied_resource_value ,
3833 inventory_item_id ,
3834 scheduled_quantity ,
3835 scheduled_date ,
3836 mrp_net_flag ,
3837 quantity_per_assembly ,
3838 required_quantity ,
3839 supply_locator_id ,
3840 supply_subinventory ,
3841 date_required ,
3842 operation_type_code ,
3843 res_sched_start_date ,
3844 res_sched_end_date ,
3845 op_scheduled_start_date ,
3846 op_scheduled_end_date ,
3847 op_actual_start_date ,
3848 op_actual_end_date ,
3849 attribute_category ,
3850 attribute1 ,
3851 attribute2 ,
3852 attribute3 ,
3853 attribute4 ,
3854 attribute5 ,
3855 attribute6 ,
3856 attribute7 ,
3857 attribute8 ,
3858 attribute9 ,
3859 attribute10 ,
3860 attribute11 ,
3861 attribute12 ,
3862 attribute13 ,
3863 attribute14 ,
3864 attribute15
3865 )
3866 VALUES
3867 (
3868 p_wo_operation_txn_id ,
3869 p_object_version_number ,
3870 p_last_update_date ,
3871 p_last_updated_by ,
3872 p_creation_date ,
3873 p_created_by ,
3874 p_last_update_login ,
3875 p_load_type_code ,
3876 p_transaction_type_code ,
3877 p_workorder_operation_id ,
3878 p_operation_resource_id ,
3879 p_schedule_material_id ,
3880 p_bom_resource_id ,
3881 p_cost_basis_code ,
3882 p_total_required ,
3883 p_assigned_units ,
3884 p_autocharge_type_code ,
3885 p_standard_rate_flag_code ,
3886 p_applied_resource_units ,
3887 p_applied_resource_value ,
3888 p_inventory_item_id ,
3889 p_scheduled_quantity ,
3890 p_scheduled_date ,
3891 p_mrp_net_flag ,
3892 p_quantity_per_assembly ,
3893 p_required_quantity ,
3894 p_supply_locator_id ,
3895 p_supply_subinventory ,
3896 p_date_required ,
3897 p_operation_type_code ,
3898 p_res_sched_start_date ,
3899 p_res_sched_end_date ,
3900 p_op_scheduled_start_date ,
3901 p_op_scheduled_end_date ,
3902 p_op_actual_start_date ,
3903 p_op_actual_end_date ,
3904 p_attribute_category ,
3905 p_attribute1 ,
3906 p_attribute2 ,
3907 p_attribute3 ,
3908 p_attribute4 ,
3909 p_attribute5 ,
3910 p_attribute6 ,
3911 p_attribute7 ,
3912 p_attribute8 ,
3913 p_attribute9 ,
3914 p_attribute10 ,
3915 p_attribute11 ,
3916 p_attribute12 ,
3917 p_attribute13 ,
3918 p_attribute14 ,
3919 p_attribute15
3920
3921 );
3922
3923 END log_transaction_record;
3924 --
3925 function GET_ISSUED_QTY(P_ORG_ID IN NUMBER, P_ITEM_ID IN NUMBER, P_WORKORDER_OP_ID IN NUMBER) RETURN NUMBER
3926 IS
3927 issued NUMBER;
3928 CURSOR Q1(p_org_id NUMBER, p_itme_Id NUMBER,p_wo_op_id in NUMBER) IS
3929 SELECT SUM(QUANTITY) FROM AHL_WORKORDER_MTL_TXNS
3930 WHERE ORGANIZATION_ID = p_org_id
3931 AND INVENTORY_ITEM_ID = p_item_id
3932 AND WORKORDER_OPERATION_ID = p_wo_op_id
3933 AND TRANSACTION_TYPE_ID = 35;
3934 BEGIN
3935
3936
3937 OPEN Q1(P_ORG_ID,P_ITEM_ID, P_WORKORDER_OP_ID);
3938 FETCH Q1 INTO issued;
3939 IF(Q1%NOTFOUND) THEN
3940 issued := 0;
3941 END IF;
3942 CLOSE Q1;
3943
3944 return issued;
3945 END GET_ISSUED_QTY;
3946
3947 ---JKJAIN FP ER # 6436303
3948
3949 -------------------------------------------------------------------------------------
3950 -- Function for returning net quantity of material available with
3951 -- a workorder.
3952 -- Net Total Quantity = Total Quantity Issued - Total quantity returned
3953 -- Balaji added this function for OGMA ER # 5948868.
3954 --------------------------------------------------------------------------------------
3955 FUNCTION GET_NET_QTY(
3956 P_ORG_ID IN NUMBER,
3957 P_ITEM_ID IN NUMBER,
3958 P_WORKORDER_OP_ID IN NUMBER
3959 )
3960 RETURN NUMBER
3961 IS
3962
3963 -- Local variables
3964 l_issue_qty NUMBER;
3965 l_rtn_qty NUMBER;
3966 l_net_qty NUMBER;
3967
3968 -- Cursors
3969 -- cursor for getting total issued quantity
3970 CURSOR c_get_issue_qty(c_org_id NUMBER, c_itme_Id NUMBER,c_wo_op_id in NUMBER)
3971 IS
3972 SELECT SUM(QUANTITY)
3973 FROM AHL_WORKORDER_MTL_TXNS
3974 WHERE ORGANIZATION_ID = c_org_id
3975 AND INVENTORY_ITEM_ID = c_itme_Id
3976 AND WORKORDER_OPERATION_ID = c_wo_op_id
3977 AND TRANSACTION_TYPE_ID = 35; -- Mtl Issue Txn
3978
3979 -- cursor for getting total returned quantity
3980 CURSOR c_get_rtn_qty(c_org_id NUMBER, c_itme_Id NUMBER,c_wo_op_id in NUMBER)
3981 IS
3982 SELECT SUM(QUANTITY)
3983 FROM AHL_WORKORDER_MTL_TXNS
3984 WHERE ORGANIZATION_ID = c_org_id
3985 AND INVENTORY_ITEM_ID = c_itme_Id
3986 AND WORKORDER_OPERATION_ID = c_wo_op_id
3987 AND TRANSACTION_TYPE_ID = 43; -- Mtl Rtn Txn
3988
3989 BEGIN
3990
3991 OPEN c_get_issue_qty(p_org_id, p_item_id, p_workorder_op_id);
3992 FETCH c_get_issue_qty INTO l_issue_qty;
3993 CLOSE c_get_issue_qty;
3994
3995 IF l_issue_qty IS NULL
3996 THEN
3997 l_issue_qty := 0;
3998 END IF;
3999
4000 OPEN c_get_rtn_qty(p_org_id, p_item_id, p_workorder_op_id);
4001 FETCH c_get_rtn_qty INTO l_rtn_qty;
4002 CLOSE c_get_rtn_qty;
4003
4004 IF l_rtn_qty IS NULL
4005 THEN
4006 l_rtn_qty := 0;
4007 END IF;
4008
4009 l_net_qty := l_issue_qty - l_rtn_qty;
4010
4011 -- JKJAIN BUG # 7587902
4012 -- IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4013 -- fnd_log.string
4014 -- (
4015 -- fnd_log.level_statement,
4016 -- 'ahl.plsql.AHL_PP_MATERIALS_PVT.GET_NET_QTY',
4017 -- 'l_net_qty -> ' || l_net_qty
4018 -- );
4019 -- END IF;
4020
4021 return l_net_qty;
4022
4023 END GET_NET_QTY;
4024
4025 --
4026 -- Start of Comments --
4027 -- Procedure name : Process_Wo_Op_Materials
4028 -- Type : Private
4029 -- Function : Procedure to Process Requested materials defined at Route/Operation/Dispostion
4030 --
4031 -- Pre-reqs :
4032 -- Parameters :
4033 --
4034 -- Standard IN Parameters :
4035 -- p_api_version IN NUMBER Required
4036 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
4037 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
4038 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
4039 -- Based on this flag, the API will set the default attributes.
4040 -- Standard OUT Parameters :
4041 -- x_return_status OUT VARCHAR2 Required
4042 -- x_msg_count OUT NUMBER Required
4043 -- x_msg_data OUT VARCHAR2 Required
4044 --
4045 -- Process_Material Parameters :
4046 -- p_prd_wooperation_tbl IN AHL_PRD_WORKORDER_PVT.Prd_Workoper_Tbl,
4047 -- x_req_material_tbl OUT Ahl_Pp_Material_Pvt.Req_Material_Tbl_Type,Required
4048 -- List of Required materials for a job
4049 --
4050
4051 PROCEDURE Process_Wo_Op_Materials (
4052 p_api_version IN NUMBER,
4053 p_init_msg_list IN VARCHAR2 := Fnd_Api.G_FALSE,
4054 p_commit IN VARCHAR2 := Fnd_Api.G_FALSE,
4055 p_validation_level IN NUMBER := Fnd_Api.G_VALID_LEVEL_FULL,
4056 p_operation_flag IN VARCHAR2,
4057 p_prd_wooperation_tbl IN AHL_PRD_OPERATIONS_PVT.Prd_Operation_Tbl,
4058 x_req_material_tbl OUT NOCOPY Req_Material_Tbl_Type,
4059 x_return_status OUT NOCOPY VARCHAR2,
4060 x_msg_count OUT NOCOPY NUMBER,
4061 x_msg_data OUT NOCOPY VARCHAR2)
4062 IS
4063
4064 CURSOR Sche_Mat_Cur(c_visit_task_id IN NUMBER)
4065 IS
4066 SELECT ASM.scheduled_material_id,
4067 ASM.visit_id,visit_task_id,
4068 ASM.inventory_item_id,
4069 ASM.organization_id,
4070 ASM.requested_date,uom,
4071 ASM.rt_oper_material_id,
4072 ASM.operation_code,
4073 ASM.operation_sequence,
4074 ASM.requested_quantity,
4075 ASM.workorder_operation_id,
4076 ASM.position_path_id,
4077 ASM.relationship_id,
4078 ASM.mr_route_id,
4079 ASM.material_request_type,
4080 ASM.status
4081 FROM AHL_SCHEDULE_MATERIALS ASM,
4082 AHL_RT_OPER_MATERIALS ARM
4083 WHERE ASM.rt_oper_material_id = ARM.RT_OPER_MATERIAL_ID
4084 AND ASM.visit_task_id = C_VISIT_TASK_ID
4085 AND ASM.requested_quantity > 0
4086 AND ASM.STATUS IN ('ACTIVE','IN-SERVICE');
4087 --
4088 CURSOR Visit_Task_Cur(c_workorder_id IN NUMBER)
4089 IS
4090 SELECT a.visit_id,
4091 visit_task_id,
4092 organization_id
4093 FROM ahl_workorders A,
4094 ahl_visits_b b
4095 WHERE workorder_id = c_workorder_id
4096 AND a.visit_id = b.visit_id;
4097
4098 CURSOR Material_Detail_Cur (c_operation_id IN NUMBER,
4099 c_operation_sequence IN NUMBER)
4100 IS
4101 SELECT Scheduled_material_id
4102 FROM AHL_SCHEDULE_MATERIALS
4103 WHERE WORKORDER_OPERATION_ID = c_operation_id
4104 AND OPERATION_SEQUENCE = c_operation_sequence;
4105
4106 l_api_name CONSTANT VARCHAR2(30) := 'PROCESS_WO_OP_MATERIALS';
4107 l_api_version CONSTANT NUMBER := 1.0;
4108 l_msg_count NUMBER;
4109 l_return_status VARCHAR2(1);
4110 l_msg_data VARCHAR2(2000);
4111 --
4112 l_prd_wooperation_tbl AHL_PRD_OPERATIONS_PVT.Prd_Operation_Tbl := p_prd_wooperation_tbl;
4113 l_Sche_Mat_Rec Sche_Mat_Cur%ROWTYPE;
4114 l_Visit_Task_Rec Visit_Task_Cur%ROWTYPE;
4115 l_req_material_tbl Req_Material_Tbl_Type;
4116 l_scheduled_material_id NUMBER;
4117 l_idx NUMBER;
4118
4119 dff_default_values dff_default_values_type;
4120
4121 BEGIN
4122
4123 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
4124 fnd_log.string
4125 (
4126 fnd_log.level_procedure,
4127 'ahl.plsql.AHL_PP_MATERIALS_PVT.Process_Wo_Op_Materials',
4128 'At the start of PLSQL procedure'
4129 );
4130 END IF;
4131 --------------------Initialize ----------------------------------
4132 -- Standard Start of API savepoint
4133 SAVEPOINT Process_Wo_Op_Materials;
4134 -- Standard call to check for call compatibility.
4135 IF FND_API.to_boolean(p_init_msg_list)
4136 THEN
4137 FND_MSG_PUB.initialize;
4138 END IF;
4139 -- Initialize API return status to success
4140 x_return_status := FND_API.G_RET_STS_SUCCESS;
4141 -- Initialize message list if p_init_msg_list is set to TRUE.
4142 IF NOT FND_API.COMPATIBLE_API_CALL(p_api_version,
4143 p_api_version,
4144 l_api_name,G_PKG_NAME)
4145 THEN
4146 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4147 END IF;
4148
4149 --
4150 IF l_prd_wooperation_tbl.COUNT > 0 THEN
4151 FOR i IN l_prd_wooperation_tbl.FIRST..l_prd_wooperation_tbl.LAST
4152 LOOP
4153 --
4154
4155 IF (p_operation_flag = 'C' AND
4156 l_prd_wooperation_tbl(i).workorder_operation_id IS NOT NULL AND
4157 l_prd_wooperation_tbl(i).workorder_operation_id <> FND_API.G_MISS_NUM )
4158 THEN
4159
4160 --Get visit id, visit task id
4161 OPEN Visit_Task_Cur(l_prd_wooperation_tbl(i).workorder_id);
4162 FETCH Visit_Task_Cur INTO l_Visit_Task_Rec;
4163 CLOSE Visit_Task_Cur;
4164
4165
4166 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4167 fnd_log.string
4168 (
4169 fnd_log.level_statement,
4170 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
4171 'Material Requirement for workorder id: ' || l_prd_wooperation_tbl(i).workorder_id
4172 );
4173 fnd_log.string
4174 (
4175 fnd_log.level_statement,
4176 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
4177 'Material Requirement for workorder operation id: ' || l_prd_wooperation_tbl(i).workorder_operation_id
4178 );
4179 fnd_log.string
4180 (
4181 fnd_log.level_statement,
4182 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
4183 'Material Requirement for workorder operation seq: ' || l_prd_wooperation_tbl(i).operation_sequence_num
4184 );
4185
4186 END IF;
4187 --Check for one operation sequence exists means materials exist at route only
4188 IF (l_prd_wooperation_tbl(i).operation_sequence_num IS NOT NULL AND
4189 l_prd_wooperation_tbl(i).operation_sequence_num <> FND_API.G_MISS_NUM)
4190 THEN
4191 --
4192 l_idx := 0;
4193 FOR l_Sche_Mat_Rec IN Sche_Mat_Cur(l_Visit_Task_Rec.visit_task_id)
4194 LOOP
4195 IF (l_Sche_Mat_Rec.operation_sequence IS NULL AND
4196 l_Sche_Mat_Rec.workorder_operation_id IS NULL )THEN
4197
4198 l_req_material_tbl(l_idx).workorder_id := l_prd_wooperation_tbl(i).workorder_id;
4199 l_req_material_tbl(l_idx).organization_id := l_Visit_Task_Rec.organization_id;
4200 l_req_material_tbl(l_idx).workorder_operation_id := l_prd_wooperation_tbl(i).workorder_operation_id;
4201 l_req_material_tbl(l_idx).operation_sequence := l_prd_wooperation_tbl(i).operation_sequence_num;
4202 l_req_material_tbl(l_idx).inventory_item_id := l_Sche_Mat_Rec.inventory_item_id;
4203 l_req_material_tbl(l_idx).schedule_material_id := l_Sche_Mat_Rec.scheduled_material_id;
4204 l_req_material_tbl(l_idx).requested_date := l_prd_wooperation_tbl(i).scheduled_start_date;
4205 l_req_material_tbl(l_idx).rt_oper_material_id := l_Sche_Mat_Rec.rt_oper_material_id;
4206 l_req_material_tbl(l_idx).requested_quantity := l_Sche_Mat_Rec.requested_quantity;
4207 l_req_material_tbl(l_idx).uom_code := l_Sche_Mat_Rec.uom;
4208
4209 get_dff_default_values
4210 (
4211 p_req_material_rec => l_req_material_tbl(l_idx),
4212 flex_fields_defaults => dff_default_values
4213 );
4214
4215 l_req_material_tbl(l_idx).attribute_category := dff_default_values.attribute_category;
4216 l_req_material_tbl(l_idx).attribute1 := dff_default_values.attribute1;
4217 l_req_material_tbl(l_idx).attribute2 := dff_default_values.attribute2;
4218 l_req_material_tbl(l_idx).attribute3 := dff_default_values.attribute3;
4219 l_req_material_tbl(l_idx).attribute4 := dff_default_values.attribute4;
4220 l_req_material_tbl(l_idx).attribute5 := dff_default_values.attribute5;
4221 l_req_material_tbl(l_idx).attribute6 := dff_default_values.attribute6;
4222 l_req_material_tbl(l_idx).attribute7 := dff_default_values.attribute7;
4223 l_req_material_tbl(l_idx).attribute8 := dff_default_values.attribute8;
4224 l_req_material_tbl(l_idx).attribute9 := dff_default_values.attribute9;
4225 l_req_material_tbl(l_idx).attribute10 := dff_default_values.attribute10;
4226 l_req_material_tbl(l_idx).attribute11 := dff_default_values.attribute11;
4227 l_req_material_tbl(l_idx).attribute12 := dff_default_values.attribute12;
4228 l_req_material_tbl(l_idx).attribute13 := dff_default_values.attribute13;
4229 l_req_material_tbl(l_idx).attribute14 := dff_default_values.attribute14;
4230 l_req_material_tbl(l_idx).attribute15 := dff_default_values.attribute15;
4231 -- fix for bug# 5549135.
4232 --l_req_material_tbl(l_idx).mrp_net_flag := 1;
4233 l_req_material_tbl(l_idx).mrp_net_flag := 2;
4234
4235 -- Update with workorder operation details
4236 UPDATE ahl_schedule_materials
4237 SET workorder_operation_id = l_prd_wooperation_tbl(i).workorder_operation_id,
4238 operation_sequence = l_prd_wooperation_tbl(i).operation_sequence_num,
4239 -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
4240 requested_date = trunc(l_prd_wooperation_tbl(i).scheduled_start_date),
4241 organization_id = l_Visit_Task_Rec.organization_id,
4242 object_version_number = object_version_number + 1,
4243 last_update_date = sysdate,
4244 last_updated_by = fnd_global.user_id,
4245 last_update_login = fnd_global.login_id,
4246 ATTRIBUTE_CATEGORY = l_req_material_tbl(l_idx).attribute_category,
4247 ATTRIBUTE1 = l_req_material_tbl(l_idx).attribute1,
4248 ATTRIBUTE2 = l_req_material_tbl(l_idx).attribute2,
4249 ATTRIBUTE3 = l_req_material_tbl(l_idx).attribute3,
4250 ATTRIBUTE4 = l_req_material_tbl(l_idx).attribute4,
4251 ATTRIBUTE5 = l_req_material_tbl(l_idx).attribute5,
4252 ATTRIBUTE6 = l_req_material_tbl(l_idx).attribute6,
4253 ATTRIBUTE7 = l_req_material_tbl(l_idx).attribute7,
4254 ATTRIBUTE8 = l_req_material_tbl(l_idx).attribute8,
4255 ATTRIBUTE9 = l_req_material_tbl(l_idx).attribute9,
4256 ATTRIBUTE10 = l_req_material_tbl(l_idx).attribute10,
4257 ATTRIBUTE11 = l_req_material_tbl(l_idx).attribute11,
4258 ATTRIBUTE12 = l_req_material_tbl(l_idx).attribute12,
4259 ATTRIBUTE13 = l_req_material_tbl(l_idx).attribute13,
4260 ATTRIBUTE14 = l_req_material_tbl(l_idx).attribute14,
4261 ATTRIBUTE15 = l_req_material_tbl(l_idx).attribute15
4262 WHERE scheduled_material_id = l_Sche_Mat_Rec.scheduled_material_id;
4263
4264 ELSIF (l_prd_wooperation_tbl(i).operation_sequence_num = l_Sche_Mat_Rec.operation_sequence
4265 AND l_Sche_Mat_Rec.workorder_operation_id IS NULL) THEN
4266
4267 l_req_material_tbl(l_idx).workorder_id := l_prd_wooperation_tbl(i).workorder_id;
4268 l_req_material_tbl(l_idx).organization_id := l_Visit_Task_Rec.organization_id;
4269 l_req_material_tbl(l_idx).workorder_operation_id := l_prd_wooperation_tbl(i).workorder_operation_id;
4270 l_req_material_tbl(l_idx).operation_sequence := l_prd_wooperation_tbl(i).operation_sequence_num;
4271 l_req_material_tbl(l_idx).inventory_item_id := l_Sche_Mat_Rec.inventory_item_id;
4272 l_req_material_tbl(l_idx).schedule_material_id := l_Sche_Mat_Rec.scheduled_material_id;
4273 l_req_material_tbl(l_idx).requested_date := l_prd_wooperation_tbl(i).scheduled_start_date;
4274 l_req_material_tbl(l_idx).rt_oper_material_id := l_Sche_Mat_Rec.rt_oper_material_id;
4275 l_req_material_tbl(l_idx).requested_quantity := l_Sche_Mat_Rec.requested_quantity;
4276 l_req_material_tbl(l_idx).uom_code := l_Sche_Mat_Rec.uom;
4277 -- fix for bug# 5549135
4278 --l_req_material_tbl(l_idx).mrp_net_flag := 1;
4279 l_req_material_tbl(l_idx).mrp_net_flag := 2;
4280
4281 get_dff_default_values
4282 (
4283 p_req_material_rec => l_req_material_tbl(l_idx),
4284 flex_fields_defaults => dff_default_values
4285 );
4286
4287 l_req_material_tbl(l_idx).attribute_category := dff_default_values.attribute_category;
4288 l_req_material_tbl(l_idx).attribute1 := dff_default_values.attribute1;
4289 l_req_material_tbl(l_idx).attribute2 := dff_default_values.attribute2;
4290 l_req_material_tbl(l_idx).attribute3 := dff_default_values.attribute3;
4291 l_req_material_tbl(l_idx).attribute4 := dff_default_values.attribute4;
4292 l_req_material_tbl(l_idx).attribute5 := dff_default_values.attribute5;
4293 l_req_material_tbl(l_idx).attribute6 := dff_default_values.attribute6;
4294 l_req_material_tbl(l_idx).attribute7 := dff_default_values.attribute7;
4295 l_req_material_tbl(l_idx).attribute8 := dff_default_values.attribute8;
4296 l_req_material_tbl(l_idx).attribute9 := dff_default_values.attribute9;
4297 l_req_material_tbl(l_idx).attribute10 := dff_default_values.attribute10;
4298 l_req_material_tbl(l_idx).attribute11 := dff_default_values.attribute11;
4299 l_req_material_tbl(l_idx).attribute12 := dff_default_values.attribute12;
4300 l_req_material_tbl(l_idx).attribute13 := dff_default_values.attribute13;
4301 l_req_material_tbl(l_idx).attribute14 := dff_default_values.attribute14;
4302 l_req_material_tbl(l_idx).attribute15 := dff_default_values.attribute15;
4303 --Update with operation details
4304 UPDATE ahl_schedule_materials
4305 SET workorder_operation_id = l_prd_wooperation_tbl(i).workorder_operation_id,
4306 object_version_number = object_version_number + 1,
4307 -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
4308 requested_date = trunc(l_prd_wooperation_tbl(i).scheduled_start_date),
4309 organization_id = l_Visit_Task_Rec.organization_id,
4310 last_update_date = sysdate,
4311 last_updated_by = fnd_global.user_id,
4312 last_update_login = fnd_global.login_id,
4313 ATTRIBUTE_CATEGORY = l_req_material_tbl(l_idx).attribute_category,
4314 ATTRIBUTE1 = l_req_material_tbl(l_idx).attribute1,
4315 ATTRIBUTE2 = l_req_material_tbl(l_idx).attribute2,
4316 ATTRIBUTE3 = l_req_material_tbl(l_idx).attribute3,
4317 ATTRIBUTE4 = l_req_material_tbl(l_idx).attribute4,
4318 ATTRIBUTE5 = l_req_material_tbl(l_idx).attribute5,
4319 ATTRIBUTE6 = l_req_material_tbl(l_idx).attribute6,
4320 ATTRIBUTE7 = l_req_material_tbl(l_idx).attribute7,
4321 ATTRIBUTE8 = l_req_material_tbl(l_idx).attribute8,
4322 ATTRIBUTE9 = l_req_material_tbl(l_idx).attribute9,
4323 ATTRIBUTE10 = l_req_material_tbl(l_idx).attribute10,
4324 ATTRIBUTE11 = l_req_material_tbl(l_idx).attribute11,
4325 ATTRIBUTE12 = l_req_material_tbl(l_idx).attribute12,
4326 ATTRIBUTE13 = l_req_material_tbl(l_idx).attribute13,
4327 ATTRIBUTE14 = l_req_material_tbl(l_idx).attribute14,
4328 ATTRIBUTE15 = l_req_material_tbl(l_idx).attribute15
4329 WHERE scheduled_material_id = l_Sche_Mat_Rec.scheduled_material_id;
4330
4331
4332 END IF;
4333 l_idx := l_idx + 1;
4334 END LOOP;
4335
4336 END IF; --COUNT
4337 END IF; --dml operation
4338 END LOOP;
4339 END IF;
4340
4341 --Modified by srin to remove the replave percent check for Bug #4007076
4342 --Assign the derived values
4343 IF l_req_material_tbl.COUNT > 0 THEN
4344 FOR j IN l_req_material_tbl.FIRST..l_req_material_tbl.LAST
4345 LOOP
4346 x_req_material_tbl(j).workorder_id := l_req_material_tbl(j).workorder_id;
4347 x_req_material_tbl(j).organization_id := l_req_material_tbl(j).organization_id;
4348 x_req_material_tbl(j).workorder_operation_id := l_req_material_tbl(j).workorder_operation_id;
4349 x_req_material_tbl(j).operation_sequence := l_req_material_tbl(j).operation_sequence;
4350 x_req_material_tbl(j).inventory_item_id := l_req_material_tbl(j).inventory_item_id;
4351 x_req_material_tbl(j).schedule_material_id := l_req_material_tbl(j).schedule_material_id;
4352 x_req_material_tbl(j).requested_date := l_req_material_tbl(j).requested_date;
4353 x_req_material_tbl(j).rt_oper_material_id := l_req_material_tbl(j).rt_oper_material_id;
4354 x_req_material_tbl(j).requested_quantity := l_req_material_tbl(j).requested_quantity;
4355 x_req_material_tbl(j).uom_code := l_req_material_tbl(j).uom_code;
4356 x_req_material_tbl(j).mrp_net_flag := l_req_material_tbl(j).mrp_net_flag;
4357 x_req_material_tbl(j).operation_flag := 'C';
4358
4359 x_req_material_tbl(j).ATTRIBUTE_CATEGORY := l_req_material_tbl(j).attribute_category;
4360 x_req_material_tbl(j).ATTRIBUTE1 := l_req_material_tbl(j).attribute1;
4361 x_req_material_tbl(j).ATTRIBUTE2 := l_req_material_tbl(j).attribute2;
4362 x_req_material_tbl(j).ATTRIBUTE3 := l_req_material_tbl(j).attribute3;
4363 x_req_material_tbl(j).ATTRIBUTE4 := l_req_material_tbl(j).attribute4;
4364 x_req_material_tbl(j).ATTRIBUTE5 := l_req_material_tbl(j).attribute5;
4365 x_req_material_tbl(j).ATTRIBUTE6 := l_req_material_tbl(j).attribute6;
4366 x_req_material_tbl(j).ATTRIBUTE7 := l_req_material_tbl(j).attribute7;
4367 x_req_material_tbl(j).ATTRIBUTE8 := l_req_material_tbl(j).attribute8;
4368 x_req_material_tbl(j).ATTRIBUTE9 := l_req_material_tbl(j).attribute9;
4369 x_req_material_tbl(j).ATTRIBUTE10 := l_req_material_tbl(j).attribute10;
4370 x_req_material_tbl(j).ATTRIBUTE11 := l_req_material_tbl(j).attribute11;
4371 x_req_material_tbl(j).ATTRIBUTE12 := l_req_material_tbl(j).attribute12;
4372 x_req_material_tbl(j).ATTRIBUTE13 := l_req_material_tbl(j).attribute13;
4373 x_req_material_tbl(j).ATTRIBUTE14 := l_req_material_tbl(j).attribute14;
4374 x_req_material_tbl(j).ATTRIBUTE15 := l_req_material_tbl(j).attribute15;
4375
4376 END LOOP;
4377 END IF;
4378 --Sync up process to update requested date if changed from original date
4379 IF l_prd_wooperation_tbl.COUNT > 0 THEN
4380 FOR i IN l_prd_wooperation_tbl.FIRST..l_prd_wooperation_tbl.LAST
4381 LOOP
4382 --
4383 IF (p_operation_flag = 'S' AND
4384 l_prd_wooperation_tbl(i).workorder_operation_id IS NOT NULL AND
4385 l_prd_wooperation_tbl(i).workorder_operation_id <> FND_API.G_MISS_NUM AND
4386 l_prd_wooperation_tbl(i).operation_sequence_num IS NOT NULL AND
4387 l_prd_wooperation_tbl(i).operation_sequence_num <> FND_API.G_MISS_NUM)
4388 THEN
4389 --
4390 OPEN Material_Detail_Cur(l_prd_wooperation_tbl(i).workorder_operation_id,
4391 l_prd_wooperation_tbl(i).operation_sequence_num);
4392 LOOP
4393 FETCH Material_Detail_Cur INTO l_scheduled_material_id;
4394 EXIT WHEN Material_Detail_Cur%NOTFOUND;
4395 IF l_scheduled_material_id IS NOT NULL THEN
4396 --
4397 UPDATE ahl_schedule_materials
4398 -- AnRaj: truncating Requested Date for Material Requirement based on discussions with PM
4399 SET requested_date = trunc(l_prd_wooperation_tbl(i).actual_start_date),
4400 object_version_number = object_version_number + 1,
4401 last_update_date = sysdate,
4402 last_updated_by = fnd_global.user_id,
4403 last_update_login = fnd_global.login_id
4404 WHERE scheduled_material_id = l_scheduled_material_id;
4405
4406 END IF;
4407 END LOOP;
4408 CLOSE Material_Detail_Cur;
4409 END IF;
4410 END LOOP;
4411 END IF;
4412 --Debug Info
4413 IF x_req_material_tbl.count > 0 THEN
4414 FOR i IN x_req_material_tbl.FIRST..x_req_material_tbl.LAST
4415 LOOP
4416
4417 IF G_DEBUG='Y' THEN
4418 AHL_DEBUG_PUB.debug( 'x_req_material_tbl(i).workorder_id'||x_req_material_tbl(i).workorder_id);
4419 AHL_DEBUG_PUB.debug( 'x_req_material_tbl(i).organization_id'||x_req_material_tbl(i).organization_id);
4420 AHL_DEBUG_PUB.debug( 'x_req_material_tbl(i).workorder_operation_id'||x_req_material_tbl(i).workorder_operation_id);
4421 AHL_DEBUG_PUB.debug( 'x_req_material_tbl(i).operation_sequence'||x_req_material_tbl(i).operation_sequence);
4422 AHL_DEBUG_PUB.debug( 'x_req_material_tbl(i).inventory_item_id'||x_req_material_tbl(i).inventory_item_id);
4423 AHL_DEBUG_PUB.debug( 'x_req_material_tbl(i).requested_date'||x_req_material_tbl(i).requested_date);
4424
4425 END IF;
4426
4427 END LOOP;
4428 END IF;
4429 IF G_DEBUG='Y' THEN
4430 AHL_DEBUG_PUB.debug( 'x_req_material_tbl.count'||x_req_material_tbl.count);
4431 END IF;
4432
4433 ------------------------End of Body---------------------------------------
4434 --Standard check to count messages
4435 l_msg_count := Fnd_Msg_Pub.count_msg;
4436
4437 IF l_msg_count > 0 THEN
4438 X_msg_count := l_msg_count;
4439 X_return_status := Fnd_Api.G_RET_STS_UNEXP_ERROR;
4440 RAISE Fnd_Api.G_EXC_ERROR;
4441 END IF;
4442
4443 --Standard check for commit
4444 IF Fnd_Api.TO_BOOLEAN(p_commit) THEN
4445 COMMIT;
4446 END IF;
4447 -- Debug info
4448 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
4449 fnd_log.string
4450 (
4451 fnd_log.level_procedure,
4452 'ahl.plsql.AHL_PP_MATERIALS_PVT.Process_Wo_Op_Materials.end',
4453 'At the end of PLSQL procedure'
4454 );
4455 END IF;
4456
4457 EXCEPTION
4458 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4459 ROLLBACK TO Process_Wo_Op_Materials;
4460 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4461 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4462 p_count => x_msg_count,
4463 p_data => x_msg_data);
4464
4465 WHEN FND_API.G_EXC_ERROR THEN
4466 ROLLBACK TO Process_Wo_Op_Materials;
4467 X_return_status := FND_API.G_RET_STS_ERROR;
4468 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4469 p_count => x_msg_count,
4470 p_data => X_msg_data);
4471 WHEN OTHERS THEN
4472 ROLLBACK TO Process_Wo_Op_Materials;
4473 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4474 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4475 THEN
4476 fnd_msg_pub.add_exc_msg(p_pkg_name => 'AHL_PP_MATERIALS_PVT',
4477 p_procedure_name => 'PROCESS_WO_OP_MATERIALS',
4478 p_error_text => SUBSTR(SQLERRM,1,240));
4479 END IF;
4480 FND_MSG_PUB.count_and_get( p_encoded => FND_API.G_FALSE,
4481 p_count => x_msg_count,
4482 p_data => X_msg_data);
4483
4484 END Process_Wo_Op_Materials;
4485
4486 --
4487 -- Start of Comments --
4488 -- Procedure name : Material_Notification
4489 -- Type : Private
4490 -- Function : Procedure to send material Notification when new item has been added
4491 -- or quantity has been changed.
4492 --
4493 -- Pre-reqs :
4494 -- Parameters :
4495 --
4496 -- Standard IN Parameters :
4497 -- p_api_version IN NUMBER Required
4498 -- p_init_msg_list IN VARCHAR2 Default FND_API.G_FALSE
4499 -- p_commit IN VARCHAR2 Default FND_API.G_FALSE
4500 -- p_validation_level IN NUMBER Default FND_API.G_VALID_LEVEL_FULL
4501 -- Based on this flag, the API will set the default attributes.
4502 -- Standard OUT Parameters :
4503 -- x_return_status OUT VARCHAR2 Required
4504 -- x_msg_count OUT NUMBER Required
4505 -- x_msg_data OUT VARCHAR2 Required
4506 --
4507 -- Process_Material Notification Parameters :
4508 -- p_Req_Material_Tbl IN Req_Material_Tbl_Type,
4509 --
4510
4511 PROCEDURE MATERIAL_NOTIFICATION
4512 (
4513 p_api_version IN NUMBER:= 1.0,
4514 p_init_msg_list IN VARCHAR2,
4515 p_commit IN VARCHAR2,
4516 p_validation_level IN NUMBER,
4517 p_Req_Material_Tbl IN Req_Material_Tbl_Type,
4518 x_return_status OUT NOCOPY VARCHAR2,
4519 x_msg_count OUT NOCOPY NUMBER,
4520 x_msg_data OUT NOCOPY VARCHAR2
4521 )
4522 IS
4523
4524
4525 CURSOR CursorNotify(c_object_type IN VARCHAR2)
4526 IS
4527 /*
4528 SELECT A.APPROVAL_RULE_ID,
4529 A.APPROVAL_OBJECT_CODE,
4530 A.STATUS_CODE,
4531 B.APPROVER_NAME,
4532 B.APPROVER_SEQUENCE
4533 FROM AHL_APPROVAL_RULES_B A,AHL_APPROVERS_V B
4534 WHERE A.APPROVAL_RULE_ID=B.APPROVAL_RULE_ID
4535 AND A.STATUS_CODE='ACTIVE'
4536 AND A.APPROVAL_OBJECT_CODE=c_object_type
4537 ORDER BY B.APPROVER_SEQUENCE;
4538 */
4539 /*
4540 * R12 Perf Tuning
4541 * Balaji blown open AHL_APPROVERS_V since it introduces NMV
4542 * due to Unions in the query. Reference bug # 4919273 and 4919045
4543 */
4544 SELECT DISTINCT
4545 JRREV.USER_NAME APPROVER_NAME
4546 FROM
4547 AHL_APPROVERS AA,
4548 FND_LOOKUP_VALUES_VL FNDA,
4549 AHL_JTF_RS_EMP_V JRREV,
4550 AHL_APPROVAL_RULES_B APR
4551 WHERE
4552 FNDA.LOOKUP_TYPE = 'AHL_APPROVER_TYPE'
4553 AND FNDA.LOOKUP_CODE = AA.APPROVER_TYPE_CODE
4554 AND AA.APPROVER_TYPE_CODE = 'USER'
4555 AND AA.APPROVER_ID = JRREV.RESOURCE_ID
4556 AND APR.APPROVAL_RULE_ID = AA.APPROVAL_RULE_ID
4557 AND APR.APPROVAL_OBJECT_CODE = c_object_type
4558 UNION
4559 SELECT DISTINCT
4560 JRRV.ROLE_NAME APPROVER_NAME
4561 FROM
4562 AHL_APPROVERS AA,
4563 FND_LOOKUP_VALUES_VL FNDA,
4564 JTF_RS_ROLE_RELATIONS_VL JRRV,
4565 AHL_APPROVAL_RULES_B APR
4566 WHERE
4567 FNDA.LOOKUP_TYPE = 'AHL_APPROVER_TYPE'
4568 AND FNDA.LOOKUP_CODE = AA.APPROVER_TYPE_CODE
4569 AND AA.APPROVER_TYPE_CODE = 'ROLE'
4570 AND AA.APPROVER_ID = JRRV.ROLE_ID
4571 AND APR.APPROVAL_RULE_ID = AA.APPROVAL_RULE_ID
4572 AND APR.APPROVAL_OBJECT_CODE = c_object_type
4573 UNION
4574 SELECT DISTINCT
4575 '' APPROVER_NAME
4576 FROM
4577 AHL_APPROVERS AA,
4578 FND_LOOKUP_VALUES_VL FNDA,
4579 AHL_APPROVAL_RULES_B APR
4580 WHERE
4581 FNDA.LOOKUP_TYPE = 'AHL_APPROVER_TYPE'
4582 AND FNDA.LOOKUP_CODE = AA.APPROVER_TYPE_CODE
4583 AND AA.APPROVER_TYPE_CODE = 'ROLE'
4584 AND AA.APPROVER_ID IS NULL
4585 AND APR.APPROVAL_RULE_ID = AA.APPROVAL_RULE_ID
4586 AND APR.APPROVAL_OBJECT_CODE = c_object_type;
4587
4588 l_rec CursorNotify%rowtype;
4589
4590 l_api_name CONSTANT VARCHAR2(30) := 'MATERIAL_NOTIFICATION';
4591 l_api_version CONSTANT NUMBER := 1.0;
4592 l_msg_count NUMBER;
4593 l_return_status VARCHAR2(1);
4594 l_msg_data VARCHAR2(2000);
4595 --
4596 l_object VARCHAR2(30):='PRD_MTL_NTF';
4597 l_active VARCHAR2(50) := 'N';
4598 l_process_name VARCHAR2(50);
4599
4600 l_item_type VARCHAR2(30) := 'AHLGAPP';
4601 l_message_name VARCHAR2(200) := 'GEN_STDLN_MESG';
4602 l_subject VARCHAR2(3000);
4603 l_body VARCHAR2(3000) := NULL;
4604 l_text VARCHAR2(3000) := NULL;
4605 l_send_to_role_name VARCHAR2(30):= NULL;
4606 l_send_to_res_id NUMBER:= NULL;
4607 l_notif_id NUMBER;
4608 l_notif_id1 NUMBER;
4609 l_role_name VARCHAR2(100);
4610 l_display_role_name VARCHAR2(240);
4611 l_object_notes VARCHAR2(400);
4612
4613 l_Req_Material_Tbl Req_Material_Tbl_Type := p_Req_Material_Tbl;
4614
4615 BEGIN
4616
4617 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
4618 fnd_log.string
4619 (
4620 fnd_log.level_procedure,
4621 'ahl.plsql.AHL_PP_MATERIALS_PVT.Material_Notification',
4622 'At the start of PLSQL procedure'
4623 );
4624 END IF;
4625
4626 -- Standard Start of API savepoint
4627 SAVEPOINT Material_Notification;
4628 -- Initialize message list if p_init_msg_list is set to TRUE.
4629 IF FND_API.to_boolean(p_init_msg_list)
4630 THEN
4631 FND_MSG_PUB.initialize;
4632 END IF;
4633 -- Initialize API return status to success
4634 x_return_status := FND_API.G_RET_STS_SUCCESS;
4635 -- Standard call to check for call compatibility.
4636 IF NOT FND_API.COMPATIBLE_API_CALL(l_api_version,
4637 l_api_version,
4638 l_api_name,G_PKG_NAME)
4639 THEN
4640 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4641 END IF;
4642
4643 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4644 fnd_log.string
4645 (
4646 fnd_log.level_statement,
4647 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
4648 'Request for Material Notification for Number of Records : ' || l_Req_Material_Tbl.COUNT
4649 );
4650
4651 END IF;
4652
4653 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4654 fnd_log.string
4655 (
4656 fnd_log.level_procedure,
4657 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
4658 'Before calling Ahl_Utility_Pvt.Get_Wf_Process_Name'
4659 );
4660
4661
4662 END IF;
4663
4664 --Get workflow status active or not
4665 Ahl_Utility_Pvt.Get_Wf_Process_Name
4666 (
4667 p_object =>l_object,
4668 x_active =>l_active,
4669 x_process_name =>l_process_name ,
4670 x_item_type =>l_item_type,
4671 x_return_status=>l_return_status,
4672 x_msg_count =>l_msg_count,
4673 x_msg_data =>l_msg_data);
4674
4675
4676 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level) THEN
4677 fnd_log.string
4678 (
4679 fnd_log.level_procedure,
4680 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||': End API',
4681 'After calling Ahl_Utility_Pvt.Get_Wf_Process_Name, Return Status : '|| l_return_status
4682 );
4683 END IF;
4684
4685 -- Check Error Message stack.
4686 IF (l_return_Status <> FND_API.G_RET_STS_SUCCESS) THEN
4687 RAISE FND_API.G_EXC_ERROR;
4688 END IF;
4689
4690 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4691 fnd_log.string
4692 (
4693 fnd_log.level_statement,
4694 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
4695 'Active flag : '||l_active
4696 );
4697 END IF;
4698
4699 IF l_active = 'Y' THEN
4700 FOR i IN l_Req_Material_Tbl.FIRST..l_Req_Material_Tbl.LAST
4701 LOOP
4702 IF l_Req_Material_Tbl(i).operation_flag = 'C' THEN
4703 IF l_Req_Material_Tbl(i).concatenated_segments IS NOT NULL THEN
4704
4705 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_MAT_REQ_ADDED_NTF');
4706 FND_MESSAGE.set_token('ITEM',l_Req_Material_Tbl(i).concatenated_segments,false);
4707 l_text := fnd_message.get;
4708 --Include quantity and date
4709 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_MAT_REQ_QTY_NTF');
4710 FND_MESSAGE.set_token('QTY',l_Req_Material_Tbl(i).requested_quantity,false);
4711 l_body := fnd_message.get;
4712 l_Req_Material_Tbl(i).notify_text := l_text ||''||l_body
4713 ||'; For Workorder:'||l_Req_Material_Tbl(i).job_number
4714 || '; Required date:'||l_Req_Material_Tbl(i).requested_date;
4715
4716 END IF;
4717
4718 ELSE
4719 --Update
4720 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_MAT_REQ_NTF_UPDATE');
4721 FND_MESSAGE.set_token('ITEM',l_Req_Material_Tbl(i).concatenated_segments,false);
4722 l_text := fnd_message.get;
4723 --Include quantity and date
4724 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_MAT_QTY_NTF_CHG');
4725 FND_MESSAGE.set_token('QTY',l_Req_Material_Tbl(i).requested_quantity,false);
4726 l_body := fnd_message.get;
4727 l_Req_Material_Tbl(i).notify_text := l_text ||''||l_body
4728 || ';For Workorder:'||l_Req_Material_Tbl(i).job_number
4729 || ';Required date:'||l_Req_Material_Tbl(i).requested_date;
4730 END IF;
4731 END LOOP;
4732 --
4733 l_body := null;
4734
4735 FOR i IN l_Req_Material_Tbl.FIRST..l_Req_Material_Tbl.LAST
4736 LOOP
4737 IF l_Req_Material_Tbl(i).notify_text IS NOT NULL THEN
4738 IF l_body is null then
4739 l_body := l_Req_Material_Tbl(i).notify_text;
4740 ELSE
4741 l_body := l_body ||':' ||l_Req_Material_Tbl(i).notify_text;
4742 END IF;
4743 END IF;
4744
4745 END LOOP;
4746
4747 IF (fnd_log.level_statement >= fnd_log.g_current_runtime_level)THEN
4748 fnd_log.string
4749 (
4750 fnd_log.level_statement,
4751 'ahl.plsql.'||g_pkg_name||'.'||l_api_name||':',
4752 'Number of records : '||l_Req_Material_Tbl.count
4753 );
4754 END IF;
4755
4756 OPEN CursorNotify(l_object);
4757 FETCH CursorNotify INTO l_rec;
4758 CLOSE CursorNotify;
4759 FND_MESSAGE.SET_NAME('AHL','AHL_PRD_MAT_REQ_NTF_CONTENT');
4760 l_subject := fnd_message.get;
4761
4762 l_role_name:=l_rec.approver_name;
4763
4764 l_return_status := FND_API.G_RET_STS_SUCCESS;
4765
4766 l_notif_id := WF_NOTIFICATION.Send
4767 ( role => l_role_name
4768 , msg_type => l_item_type
4769 , msg_name => l_message_name
4770 );
4771
4772 WF_NOTIFICATION.SetAttrText(l_notif_id,
4773 'GEN_MSG_SUBJECT',
4774 l_subject);
4775
4776 WF_NOTIFICATION.SetAttrText(l_notif_id,
4777 'GEN_MSG_BODY',
4778 l_body);
4779
4780 WF_NOTIFICATION.SetAttrText(l_notif_id,
4781 'GEN_MSG_SEND_TO',
4782 l_role_name);
4783
4784 WF_NOTIFICATION.Denormalize_Notification(l_notif_id);
4785 END IF;
4786
4787 IF (fnd_log.level_procedure >= fnd_log.g_current_runtime_level)THEN
4788 fnd_log.string
4789 (
4790 fnd_log.level_procedure,
4791 'ahl.plsql.AHL_PP_MATERIALS_PVT.Material_Notification.end',
4792 'At the end of PLSQL procedure'
4793 );
4794 END IF;
4795
4796
4797 EXCEPTION
4798 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
4799 ROLLBACK TO Material_Notification;
4800 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4801 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
4802 p_count => x_msg_count,
4803 p_data => x_msg_data);
4804
4805 WHEN FND_API.G_EXC_ERROR THEN
4806 ROLLBACK TO MATERIAL_NOTIFICATION;
4807 X_return_status := FND_API.G_RET_STS_ERROR;
4808 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
4809 p_count => x_msg_count,
4810 p_data => X_msg_data);
4811 WHEN OTHERS THEN
4812 ROLLBACK TO Material_Notification;
4813 X_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
4814 IF FND_MSG_PUB.check_msg_level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
4815 THEN
4816 fnd_msg_pub.add_exc_msg(p_pkg_name => G_PKG_NAME,
4817 p_procedure_name => 'MATERIAL_NOTIFICATION',
4818 p_error_text => SUBSTR(SQLERRM,1,240));
4819 END IF;
4820 FND_MSG_PUB.count_and_get( p_encoded =>FND_API.G_FALSE,
4821 p_count => x_msg_count,
4822 p_data => X_msg_data);
4823
4824 END Material_Notification;
4825
4826 PROCEDURE get_dff_default_values
4827 (
4828 p_req_material_rec IN REQ_MATERIAL_REC_TYPE,
4829 flex_fields_defaults OUT NOCOPY dff_default_values_type
4830 ) IS
4831
4832 flexfield fnd_dflex.dflex_r;
4833 flexinfo fnd_dflex.dflex_dr;
4834 contexts fnd_dflex.contexts_dr;
4835 i BINARY_INTEGER;
4836 j BINARY_INTEGER;
4837 segments fnd_dflex.segments_dr;
4838
4839
4840 BEGIN
4841 fnd_dflex.get_flexfield('AHL', 'Material Reqmt Flex Field', flexfield, flexinfo);
4842 IF(p_req_material_rec.ATTRIBUTE_CATEGORY IS NULL)THEN
4843 flex_fields_defaults.ATTRIBUTE_CATEGORY := flexinfo.default_context_value;
4844 ELSIF (p_req_material_rec.ATTRIBUTE_CATEGORY = FND_API.G_MISS_CHAR)THEN
4845 flex_fields_defaults.ATTRIBUTE_CATEGORY := NULL;
4846 ELSE
4847 flex_fields_defaults.ATTRIBUTE_CATEGORY := p_req_material_rec.ATTRIBUTE_CATEGORY;
4848 END IF;
4849 IF G_DEBUG='Y' THEN
4850 AHL_DEBUG_PUB.debug('flex_fields_defaults.ATTRIBUTE_CATEGORY : ' || flex_fields_defaults.ATTRIBUTE_CATEGORY);
4851 END IF;
4852 IF(flex_fields_defaults.ATTRIBUTE_CATEGORY IS NOT NULL)THEN
4853 fnd_dflex.get_contexts(flexfield, contexts);
4854 FOR j IN 1 .. contexts.ncontexts LOOP
4855 IF(contexts.is_enabled(j) AND
4856 (flex_fields_defaults.ATTRIBUTE_CATEGORY = contexts.context_code(j)
4857 OR contexts.is_global(j))
4858 ) THEN
4859 fnd_dflex.get_segments
4860 ( fnd_dflex.make_context(flexfield,
4861 contexts.context_code(j)),
4862 segments,
4863 TRUE
4864 );
4865 FOR i IN 1 .. segments.nsegments LOOP
4866 IF(segments.is_enabled(i)) THEN
4867 IF(segments.application_column_name(i) = 'ATTRIBUTE1')THEN
4868 flex_fields_defaults.ATTRIBUTE1 := to_char(segments.default_value(i));
4869 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE2')THEN
4870 flex_fields_defaults.ATTRIBUTE2 := to_char(segments.default_value(i));
4871 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE3')THEN
4872 flex_fields_defaults.ATTRIBUTE3 := to_char(segments.default_value(i));
4873 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE4')THEN
4874 flex_fields_defaults.ATTRIBUTE4 := to_char(segments.default_value(i));
4875 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE5')THEN
4876 flex_fields_defaults.ATTRIBUTE5 := to_char(segments.default_value(i));
4877 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE6')THEN
4878 flex_fields_defaults.ATTRIBUTE6 := to_char(segments.default_value(i));
4879 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE7')THEN
4880 flex_fields_defaults.ATTRIBUTE7 := to_char(segments.default_value(i));
4881 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE8')THEN
4882 flex_fields_defaults.ATTRIBUTE8 := to_char(segments.default_value(i));
4883 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE9')THEN
4884 flex_fields_defaults.ATTRIBUTE9 := to_char(segments.default_value(i));
4885 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE10')THEN
4886 flex_fields_defaults.ATTRIBUTE10 := to_char(segments.default_value(i));
4887 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE11')THEN
4888 flex_fields_defaults.ATTRIBUTE11 := to_char(segments.default_value(i));
4889 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE12')THEN
4890 flex_fields_defaults.ATTRIBUTE12 := to_char(segments.default_value(i));
4891 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE13')THEN
4892 flex_fields_defaults.ATTRIBUTE13 := to_char(segments.default_value(i));
4893 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE14')THEN
4894 flex_fields_defaults.ATTRIBUTE14 := to_char(segments.default_value(i));
4895 ELSIF(segments.application_column_name(i) = 'ATTRIBUTE15')THEN
4896 flex_fields_defaults.ATTRIBUTE15 := to_char(segments.default_value(i));
4897 END IF;
4898 END IF;
4899 END LOOP;
4900 END IF;
4901 END LOOP;
4902 END IF;
4903
4904 END get_dff_default_values;
4905
4906 END;